WIp Job Updation Interface

Job Updation Custom Table

Import  Job  Updation Details Through Procedure  and Validate with Open Interface

         SELECT wip_job_schedule_interface_s.NEXTVAL

           INTO v_group_id

           FROM DUAL;;

         SELECT wip_job_number_s.NEXTVAL

           INTO v_job

           FROM DUAL;

CREATE OR REPLACE Procedure APPS.XXMBS_JOB_STATUS_UPDATE(P_Segment1 Varchar2,P_Segment2 Varchar2,P_Segment3 Varchar2,P_Job_Status Number)

Is

v_interface_request_id Number;

 v_group_id Number;

   Cursor U_JOB

   Is

  

            Select Distinct Segment1,PRIMARY_ITEM_ID,WIP_ENTITY_ID,NET_QUANTITY,START_QUANTITY,MSI.Organization_Id,SCHEDULED_START_DATE From WIP_DISCRETE_JOBS WDJ ,Apps.Mtl_System_Items_B MSI

            Where WDJ.PRIMARY_ITEM_ID=MSI.Inventory_item_ID

            And WDJ.Organization_Id=MSI.Organization_Id

            --And WDJ.WIP_ENTITY_ID=1239732

            AND Segment1 Like Substr(P_Segment1,1,7)||'%'||Substr(P_Segment2,9,2)||'%'||Substr(P_Segment3,12,3)||'%'

            And MSI.Organization_Id=123

            Order By WIP_ENTITY_ID;

Begin

    For I In U_JOB

    Loop

   

        Begin

           SELECT wip_job_schedule_interface_s.NEXTVAL

           INTO v_group_id

           FROM DUAL;

        End;

   

            INSERT INTO wip_job_schedule_interface

            (

            organization_id

            --, primary_item_id

            --, job_name

            , wip_entity_id

            , group_id

            , header_id

            , load_type

            , process_phase

            , process_status

            , created_by

            , creation_date

            , last_updated_by

            , last_update_date

            ,START_QUANTITY

            ,NET_QUANTITY

            , first_unit_start_date

            , status_type

            )

            values

            (

            I.Organization_Id -- organization;_id

            --,2058906 -- primary_item_id

            --,176516--wip_entity_name

            ,I.wip_entity_id -- wip_entity_id

            , v_group_id -- max(t.group_id)+1

            ,64798 -- max(t.header_id)+1

            ,3 -- load_type

            ,2 -- process_phase

            ,1 -- process_status

            ,7272 -- created_by

            ,SYSDATE -- creation_date

            ,7272 -- last_updated_by

            ,SYSDATE -- last_update_date

            ,I.START_QUANTITY

            ,I.NET_QUANTITY

            ,to_date(I.SCHEDULED_START_DATE,'DD-MON-RRRR') --first_unit_start_date

            ,P_Job_Status

            );

    

     Commit;

Apps.Fnd_Global.Apps_Initialize(7272,20560, 706);

         v_interface_request_id :=

            fnd_request.submit_request ('WIP',

                                        'WICMLP',

                                        'WIP Mass Load',

                                        NULL,

                                        FALSE,

                                         v_group_id,

                                        0,

                                        1

                                       );

                                       

                                       Commit;

--        Dbms_Output.Put_Line(I.Assembly_Item_Id);

    End Loop;

        

    

End;

/