[Script] Outsource Processing WIP & PO

 範例 : 

WITH job AS

 (SELECT wdj.wip_entity_name,

         wdj.wip_entity_id,

         wo.operation_seq_num,

         wdj.status_type_disp

  FROM   wip_discrete_jobs_v wdj,

         wip_operations_v    wo

  WHERE  wdj.organization_id = 00

  AND    wdj.class_code = 'XX'

  AND    wdj.wip_entity_id = wo.wip_entity_id),

po AS

 (SELECT pd.wip_entity_id,

         pd.wip_operation_seq_num,

         poh.segment1,

         poh.terms_id,

         poh.vendor_id,

         poh.vendor_site_id,

         poh.currency_code,

         poh.po_header_id,

         poh.type_lookup_code,

         poh.approved_flag,

         poh.closed_code,

         pol.closed_code          line_closed_code,

         pll.cancel_flag          line_cancel_flag,

         poh.frozen_flag,

         poh.user_hold_flag,

         pol.expiration_date,

         pol.cancel_flag

  FROM   po_distributions_all  pd,

         po_line_locations_all pll,

         po_lines_all          pol,

         po_headers_all        poh

  WHERE  pd.line_location_id = pll.line_location_id

  AND    pll.po_line_id = pol.po_line_id

  AND    pol.po_header_id = poh.po_header_id)

SELECT job.wip_entity_name "Job Number",

       job.status_type_disp "Job Status",

       po.currency_code "Currency",

       po_totals_po_sv.get_po_total(X_header_id => po.po_header_id) "Total Amout",

       wip_osp.is_order_open(approved_flag => po.approved_flag,

                             closed_code => po.closed_code,

                             line_closed_status => po.closed_code,

                             cancel_flag => po.cancel_flag,

                             frozen_flag => po.frozen_flag,

                             user_hold_flag => po.user_hold_flag,

                             line_expiration_date => po.expiration_date,

                             line_cancel_flag => po.cancel_flag) "Purchase is opened?"

FROM   job,

       po

WHERE  job.wip_entity_id = po.wip_entity_id(+)

AND    job.operation_seq_num = po.wip_operation_seq_num(+)

[Check] WIP Loop Issue

檢查範例 : 

[mrp_net_flag = 1 表示 MRP / ASCP模組使用到,定義為供給或需求

通常生產主料與用料相同時,可能造成無限迴圈的異常]

[一般維修工單最可能發生, 將用料 MRP NET 取消即可]

DECLARE

  CURSOR curJob IS

    SELECT wdj.organization_id,

           wdj.primary_item_id,

           wdj.wip_entity_id,

           wro.inventory_item_id,

           wdj.wip_entity_name

    FROM   wip_discrete_jobs_v          wdj,

           wip_requirement_operations_v wro

    WHERE  wdj.wip_entity_id = wro.wip_entity_id

    AND    wro.mrp_net_flag = 1

    --AND    wro.organization_id = 00

    --AND    wdj.status_type IN (1,3);

    AND    wdj.creation_date >= SYSDATE - 90;

  CURSOR curImpl

  (

    pOrganizationId  IN NUMBER,

    pComponentItemId IN NUMBER,

    pWipAssemblyId   IN NUMBER

  ) IS

    SELECT 1

    FROM   bom_small_impl_temp bom

    WHERE  bom.lowest_item_id = pWipAssemblyId

    AND    bom.organization_id = pOrganizationId

    AND    bom.parent_item_id = pComponentItemId

    AND    bom.alternate_designator IS NULL;

  vComponentItem mtl_item_flexfields.item_number%TYPE;

  vAssemblyItem  mtl_item_flexfields.item_number%TYPE;

  vSequenceId    NUMBER := -99;

  xErrorMessage  VARCHAR2(2000);

  xErrorCode     NUMBER;

BEGIN

  dbms_output.enable(2000000);

  FOR i IN curJob

  LOOP

    DELETE FROM bom_small_impl_temp

    WHERE  sequence_id = vSequenceId;

    bompiinq.imploder_userexit(sequence_id => vSequenceId,

                               eng_mfg_flag => 1, -- bom

                               org_id => i.organization_id, impl_flag => 2,-- both impl and unimpl

                               display_option => 3,-- 1 = all, 2 = curr, 3=curr,future

                               levels_to_implode => 10,

                               item_id => i.primary_item_id,

                               impl_date => to_char(SYSDATE,'YYYY/MM/DD HH24:MI'),

                               unit_number_from => '', unit_number_to => '',

                               err_msg => xErrorMessage,

                               err_code => xErrorCode);

    FOR j IN curImpl(i.organization_id, i.inventory_item_id,

                     i.primary_item_id)

    LOOP

      IF curImpl%FOUND THEN

        vComponentItem := bom_globals.Get_Item_Name(p_item_id => i.inventory_item_id,

                                                    p_org_id => i.organization_id);

        vAssemblyItem  := bom_globals.Get_Item_Name(p_item_id => i.primary_item_id,

                                                    p_org_id => i.organization_id);

        IF vAssemblyItem <> vComponentItem THEN

          dbms_output.put_line('Org Id: ' || to_char(i.organization_id) || ', ' ||

                               'Job: ' || i.wip_entity_name || ', ' ||

                               'Assembly: ' || vAssemblyItem || '(id=' ||

                               to_char(i.primary_item_id) || ')' || ', ' ||

                               'Component: ' || vComponentItem || '(id=' ||

                               to_char(i.inventory_item_id) || ')');

        END IF;

      

        EXIT;

      END IF;

    END LOOP;

  END LOOP;

END;

[API] bill_exists & routing_exists & revision_exists

wip_common.bill_exists

wip_common.routing_exists

wip_common.revision_exists

[API] 工單用料替換

範例 : 

DECLARE

  vOrganizationId wip_discrete_jobs_v.organization_id%TYPE := 00;

  vWipEntityName  wip_discrete_jobs_v.wip_entity_name%TYPE := 123456;

  vOlditem        mtl_system_items_vl.concatenated_segments%TYPE := 'A';

  vNewitem        mtl_system_items_vl.concatenated_segments%TYPE := 'B';

  vGroupId     wip_job_schedule_interface.group_id%TYPE;

  vJob         wip.wip_job_schedule_interface%ROWTYPE;

  vRequirement wip.wip_job_dtls_interface%ROWTYPE;

  vWipEntity      wip_discrete_jobs_v%ROWTYPE;

  vWipRequirement wip_requirement_operations_v%ROWTYPE;

  vReturnStatus VARCHAR2(1); 

  vErrorMessage VARCHAR2(2000);

BEGIN

  SELECT *

  INTO   vWipEntity

  FROM   wip_discrete_jobs_v wdj

  WHERE  wdj.organization_id = vOrganizationId

  AND    wdj.wip_entity_name = vWipEntityName;

  SELECT *

  INTO   vWipRequirement

  FROM   wip_requirement_operations_v

  WHERE  organization_id = vWipEntity.organization_id

  AND    wip_entity_id = vWipEntity.wip_entity_id

  AND    concatenated_segments = vOlditem;

  --app init 

  fnd_global.apps_initialize(user_id => 00, resp_id => 00, resp_appl_id => 00);

  /*================================================

   WIP_LOAD_TYPE        MFG_LOOKUPS

   ------------        --------------

       1              Create Standard Job

       2              Create Repetitive Schedule

       3              Update Discrete Job

       4              Create Non–standard Job

  ================================================*/

  vGroupId              := wip.wip_job_schedule_interface_s.nextval;

  vJob.load_type        := 3;

  vJob.last_update_date := SYSDATE;

  vJob.last_updated_by  := fnd_global.user_id;

  vJob.creation_date    := SYSDATE;

  vJob.created_by       := fnd_global.user_id;

  vJob.process_phase    := '2';

  vJob.process_status   := '1';

  vJob.job_name         := vWipEntity.wip_entity_name;

  vJob.wip_entity_id    := vWipEntity.wip_entity_id;

  vJob.status_type      := vWipEntity.status_type;

  vJob.organization_id  := vWipEntity.organization_id;

  vJob.group_id         := vGroupId;

  vJob.header_id        := vGroupId;

  vJob.source_code      := 'TEST';

  INSERT INTO wip.wip_job_schedule_interface

  VALUES vJob;

  vRequirement.load_type             := 2;

  vRequirement.operation_seq_num     := vWipRequirement.operation_seq_num;

  vRequirement.department_id         := vWipRequirement.department_id;

  vRequirement.inventory_item_id_new := inv_value_to_id.Inventory_Item(p_organization_id => vOrganizationId,

                                                                       p_inventory_item => vNewitem);

  vRequirement.inventory_item_id_old := inv_value_to_id.Inventory_Item(p_organization_id => vOrganizationId,

                                                                       p_inventory_item => vOlditem);

  vRequirement.wip_supply_type       := vWipRequirement.wip_supply_type;

  vRequirement.date_required         := vWipRequirement.date_required;

  vRequirement.quantity_per_assembly := vWipRequirement.quantity_per_assembly;

  vRequirement.required_quantity     := vWipRequirement.required_quantity;

  vRequirement.group_id              := vGroupId;

  vRequirement.parent_header_id      := vGroupId;

  vRequirement.substitution_type     := 3;

  vRequirement.process_phase         := 2; --validation

  vRequirement.process_status        := 1; --pending

  vRequirement.last_update_date      := SYSDATE;

  vRequirement.last_updated_by       := fnd_global.user_id;

  vRequirement.creation_date         := SYSDATE;

  vRequirement.created_by            := fnd_global.user_id;

  vRequirement.last_update_login     := fnd_global.login_id;

  INSERT INTO wip.wip_job_dtls_interface

  VALUES vRequirement;

  wip_massload_pub.massloadjobs(p_groupid => vGroupId,

                                p_validationlevel => 2,

                                p_commitflag => 0, -- Commit 1 =Yes , 0 ='No'

                                x_returnstatus => vReturnStatus,

                                x_errormsg => vErrorMessage);

  dbms_output.put_line(vReturnStatus);

  dbms_output.put_line(vErrorMessage);

EXCEPTION 

  WHEN OTHERS THEN 

   dbms_output.put_line(dbms_utility.format_error_stack);

END;