1 Reply Latest reply on May 31, 2018 5:05 AM by Dhanya-Oracle

    Import Standard Purchase Orders program being in running Phase Only.

    San The Explorer

      Hi,

      I populated below PO interface tables to migrate Open Standard PO'S.

      PO_HEADERS_INTERFACE

      PO_LINES_INTERFACE

      PO_LINE_LOCATIONS_INTERFACE

      PO_DISTRIBUTIONS_INTERFACE

       

      Actually the problem is the Import Standard PO'S concurrent program is not moving into further phase from Running.When i checked Process Code in PO_HEADERS_INTERFACE table it is IN PROCESS. So i terminated the Program so many times and my DBA killed the session so many times, but the program is not moving from Running Phase.

      In other scenario i inserted invalid data into interface tables then the program was completed and updated process code rejected in PO_headers_interface table.

       

      below is the script i am using to populate the interface tables.With the same script i migrated few standard PO's in Open and Closed status but i am getting this issue from last 2 days.

      DECLARE

        --Header Information

        -- l_header_id           NUMBER;

        L_BILL_TO_ID NUMBER := 204;

        L_SHIP_TO_ID NUMBER := 204;

         l_buyer_id            NUMBER := 32889;

        L_PO_TYPE      VARCHAR2(20) := 'STANDARD';

        L_PROCESS_CODE VARCHAR2(20) := 'PENDING';

        L_ACTION       VARCHAR2(20) := 'ORIGINAL';

        L_ORG_ID       NUMBER := 204;

        L_SUPPLIER_ID  NUMBER := 1220;

        L_SITE_CODE_ID NUMBER := 2894;

        L_HEADER_COUNT NUMBER := 1;

       

       

        --Line Information 

        L_LINE_COUNT    NUMBER := 2;

        L_LINE_TYPE     VARCHAR2(20) := 'Goods';

        L_INV_ITEM_ID   NUMBER := 156731;

        L_LINE_QUANTITY NUMBER := 4;

        L_UNIT_PRICE    NUMBER := 50;

        -- l_need_by_date        DATE;

       

       

        --Line Location Information

        L_LOCATION_COUNT NUMBER := 2;

        -- l_ship_numb           NUMBER := ;

        L_INV_ORG_ID  NUMBER := 204;

        L_LOC_SHIP_TO NUMBER := 204;

        --l_ship_quantity       NUMBER;

        L_NEED_BY_DATE DATE := '31-MAY-2018';

        L_LOC_QUANTITY NUMBER := 2;

        --Line Distribution Information

        L_DIST_COUNT NUMBER := 2;

        --l_dist_numb           NUMBER;

        L_DIST_QUANTITY NUMBER := 1;

       

       

      BEGIN

        FOR HDR_X IN 1 .. L_HEADER_COUNT LOOP

          DBMS_OUTPUT.PUT_LINE('--Header--');

          INSERT INTO PO.PO_HEADERS_INTERFACE

            (INTERFACE_HEADER_ID,

             --BATCH_ID,  

             PROCESS_CODE,

             ACTION,

             ORG_ID,

             DOCUMENT_TYPE_CODE,

             CURRENCY_CODE,

                AGENT_ID,   

             VENDOR_ID,

             VENDOR_SITE_ID,

             SHIP_TO_LOCATION_ID,

             BILL_TO_LOCATION_ID,

             ATTRIBUTE6,

             approval_status,

             creation_date,

             attribute10

             --,closed_code

            

             -- CREATION_DATE

             )

          VALUES

            (PO_HEADERS_INTERFACE_S.NEXTVAL,

             L_PROCESS_CODE,

             L_ACTION,

             L_ORG_ID,

             L_PO_TYPE,

             'USD',

              l_buyer_id,

             L_SUPPLIER_ID,

             L_SITE_CODE_ID,

             L_SHIP_TO_ID,

             L_BILL_TO_ID,

             'TEST-PO',

             'APPROVED',

             SYSDATE,

             PO_HEADERS_INTERFACE_S.currval

            

             --,'CLOSE'

             );

       

          FOR X_LINE IN 1 .. L_LINE_COUNT LOOP

            DBMS_OUTPUT.PUT_LINE('--Line--');

         

            INSERT INTO PO.PO_LINES_INTERFACE

              (INTERFACE_LINE_ID,

               INTERFACE_HEADER_ID,

             --  ACTION,

               LINE_NUM,

               LINE_TYPE,

               ITEM_ID,

               --UOM_CODE,    

               QUANTITY,

               UNIT_PRICE,

               SHIP_TO_ORGANIZATION_ID,

               SHIP_TO_LOCATION_ID,

               --   NEED_BY_DATE,

               --PROMISED_DATE,       

                CREATION_DATE,   

               LINE_LOC_POPULATED_FLAG)

            VALUES

              (PO_LINES_INTERFACE_S.NEXTVAL,

               PO_HEADERS_INTERFACE_S.CURRVAL,

             --  'ADD',

               X_LINE,

               L_LINE_TYPE,

               L_INV_ITEM_ID,

               L_line_QUANTITY,

               L_UNIT_PRICE,

               L_INV_ORG_ID,

               L_LOC_SHIP_TO,

               SYSDATE,

               'Y');

         

            FOR X_LOC IN 1 .. L_LOCATION_COUNT LOOP

              DBMS_OUTPUT.PUT_LINE('--Location--');

           

              INSERT INTO PO.PO_LINE_LOCATIONS_INTERFACE

                (INTERFACE_LINE_LOCATION_ID,

                 INTERFACE_HEADER_ID,

                 INTERFACE_LINE_ID,

                 SHIPMENT_TYPE,

                 SHIPMENT_NUM,

                 SHIP_TO_ORGANIZATION_ID,

                 SHIP_TO_LOCATION_ID,

                 NEED_BY_DATE,

                 --PROMISED_DATE,       

                 QUANTITY,

                 CREATION_DATE

               -- , INVOICE_CLOSE_TOLERANCE

            --,RECEIVE_CLOSE_TOLERANCE

                 )

              VALUES

                (PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL,

                 PO_HEADERS_INTERFACE_S.CURRVAL,

                 PO_LINES_INTERFACE_S.CURRVAL,

                

                 'STANDARD',

                 X_LOC,

                 L_INV_ORG_ID,

                 L_LOC_SHIP_TO,

                 L_NEED_BY_DATE,

                 L_loc_QUANTITY,

                 SYSDATE

                -- ,100

                 --,100

                

                 );

           

              FOR X_DIST IN 1 .. L_DIST_COUNT LOOP

                DBMS_OUTPUT.PUT_LINE('--Distribution--');

             

                INSERT INTO PO.PO_DISTRIBUTIONS_INTERFACE

                  (INTERFACE_HEADER_ID,

                   INTERFACE_LINE_ID,

                   INTERFACE_LINE_LOCATION_ID,

                   INTERFACE_DISTRIBUTION_ID,

                   DISTRIBUTION_NUM,

                   ORG_ID,

                   QUANTITY_ORDERED,

                   -- CHARGE_ACCOUNT_ID,

                    CREATION_DATE

                   )

                VALUES

                  (PO_HEADERS_INTERFACE_S.CURRVAL, --- INTERFACE_HEADER_ID,

                   PO_LINES_INTERFACE_S.CURRVAL, --- INTERFACE_LINE_ID,

                   PO_LINE_LOCATIONS_INTERFACE_S.CURRVAL, ---  INTERFACE_LINE_LOCATION_ID,

                   PO.PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL, --- INTERFACE_DISTRIBUTION_ID,          

                   X_DIST,

                   L_ORG_ID,

                   L_DIST_QUANTITY, --- QUANTITY_ORDERED,         

                   --l_charge_account_id,  

                   SYSDATE

                   );

             

              END LOOP;

           

            END LOOP;

         

          END LOOP;

       

        END LOOP;

        COMMIT;

       

       

      EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE(SQLERRM);

       

      END;