退货订单接收入库并创建批次api

declare
  v_order_header_id   number := 194108;
  v_req_id            number;
  v_group_id          number;
  v_req_return_status boolean;
  v_line_count        number;
  cursor cur_order_header is
    select h.sold_to_org_id, h.ship_from_org_id
      from oe_order_headers_all h
     where h.header_id = v_order_header_id;
  cursor cur_order_line is
    select l.header_id,
           l.line_id,
           l.sold_to_org_id,
           l.ordered_item_id,
           l.ordered_quantity,
           l.order_quantity_uom
      from oe_order_lines_all l
     where l.header_id = v_order_header_id;
begin
  dbms_output.put_line('begin');
  fnd_global.APPS_INITIALIZE(1115, 1, 1);
  for rec_order_header in cur_order_header loop
    INSERT INTO RCV_HEADERS_INTERFACE
      (HEADER_INTERFACE_ID,
       GROUP_ID,
       PROCESSING_STATUS_CODE,
       RECEIPT_SOURCE_CODE,
       TRANSACTION_tYPE,
       AUTO_TRANSACT_CODE,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       LAsT_UPDATE_LOGIN,
       CREATION_DATE,
       CREATED_BY,
       CUSTOMER_ID,
       SHIP_To_ORGANIZATION_ID,
       EXPECTED_RECEIPT_DATE,
       VALIDATION_FLAG)
    VALUES
      (rcv_headers_interface_s.nextval, --Header_Interface_Id
       rcv_interface_groups_s.nextval, --Group_Id
       'PENDING', --Processing_Status_Code
       'CUSTOMER', --Receipt_Source_Code  --'VENDOR',--
       'NEW', --'RECEIVE', --Transaction_Type  new
       'DELIVER', --Auto_Transact_Code
       SYSDATE, --Last_Update_Date
       1115, --Last_Updated_By
       0, --Last_Update_Login
       SYSDATE, --Creation_Date
       1115, --Created_By
       rec_order_header.sold_to_org_id, --530941, --Customer_Id
       156, --rec_order_header.ship_from_org_id,--156, --Ship_To_Organization_Id,"ZZC"
       SYSDATE, --Expected_Receipt_Date
       'Y' --Validation_Flag
       );
    select max(hi.group_id) into v_group_id from RCV_HEADERS_INTERFACE hi;
    dbms_output.put_line('insert header success:' || v_group_id);
    v_line_count := 0;
    for rec_order_line in cur_order_line loop
      v_line_count := v_line_count + 1;
      INSERT INTO RCV_TRANSACTIONS_INTERFACE
        (INTERFACE_TRANSACTION_ID,
         GROUP_ID,
         HEADER_INTERFACE_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         TRANSACTION_TYPE,
         TRANSACTION_DATE,
         PROCESSING_STATUS_CODE,
         PROCESSING_MODE_CODE,
         TRANSACTION_STATUS_CODE,
         QUANTITY,
         UNIT_OF_MEASURE,
         INTERFACE_SOURCE_CODE,
         ITEM_ID,
         EMPLOYEE_ID,
         AUTO_TRANSACT_CODE,
         RECEIPT_SOURCE_CODE,
         TO_ORGANIZATION_ID,
         SOURCE_DOCUMENT_CODE,
         DESTINATION_TYPE_CODE,
         DELIVER_TO_LOCATION_ID,
         SUBINVENTORY,
         LOCATOR,
         EXPECTED_RECEIPT_DATE,
         OE_ORDER_HEADER_ID,
         OE_ORDER_LINE_ID,
         CUSTOMER_ID,
         --CUSTOMER_SITE_ID,
         VALIDATION_FLAG)
      Values
        (RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --INTERFACE_TRANSACTION_ID
         RCV_INTERFACE_GROUPS_S.CURRVAL, --GROUP_ID
         RCV_HEADERS_INTERFACE_S.CURRVAL, --HEADER_INTERFACE_ID
         SYSDATE, --LAST_UPDATE_DATE
         1115, --LAST_UPDATED_BY
         SYSDATE, --CREATION_DATE
         1115, --CREATED_BY
         'RECEIVE', --TRANSACTION_TYPE
         SYSDATE, --TRANSACTION_DATE
         'PENDING', --PROCESSING_STATUS_CODE
         'BATCH', --PROCESSING_MODE_CODE
         'PENDING', --TRANSACTION_MODE_CODE
         rec_order_line.ordered_quantity, --3, --QUANTITY
         rec_order_line.order_quantity_uom, --'箱', --UNIT_OF_MEASURE--================
         'RCV', --INTERFACE_SOURCE_CODE
         rec_order_line.ordered_item_id, --281, --ITEM_ID
         818, --EMPLOYEE_ID  --default ========
         'DELIVER', --AUTO_TRANSACT_CODE
         'CUSTOMER', --RECEIPT_SOURCE_CODE
         156, --TO_ORGANIZATION_ID--==========ZZC
         'RMA', --SOURCE_DOCUMENT_CODE
         'INVENTORY', --DESTINATION_TYPE_CODE
         142, --DELIVER_TO_LOCATION_ID--==========
         'ZZC', --SUBINVENTORY
         'A', --LOCATOR
         SYSDATE, --EXPECTED_RECEIPT_DATE
         rec_order_line.header_id, --193880, --OE_ORDER_HEADER_ID
         rec_order_line.line_id, --398048, --OE_ORDER_LINE_ID
         rec_order_line.sold_to_org_id, --530941, --CUSTOMER_ID--====================
         --16134, --CUSTOMER_SITE_ID
         'Y');
   
      INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
        (TRANSACTION_INTERFACE_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATE_LOGIN,
         LOT_NUMBER,
         TRANSACTION_QUANTITY,
         PRIMARY_QUANTITY,
         PRODUCT_CODE,
         PRODUCT_TRANSACTION_ID)
      VALUES
        (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --TRANSACTION_INTERFACE_ID
         SYSDATE, --LAST_UPDATE_DATE
         1115, --LAST_UPDATED_BY
         SYSDATE, --CREATION_DATE
         1115, --CREATED_BY
         0, --LAST_UPDATE_LOGIN
         '2008122203', --LOT_NUMBER
         rec_order_line.ordered_quantity, --3, --TRANSACTION_QUANTITY
         rec_order_line.ordered_quantity, --3, --PRIMARY_QUANTITY
         'RCV', --PRODUCT_CODE
         RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
         );

/*INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
    (TRANSACTION_INTERFACE_ID,
     LAST_UPDATE_DATE,
     LAST_UPDATED_BY,
     CREATION_DATE,
     CREATED_BY,
     LAST_UPDATE_LOGIN,
     FM_SERIAL_NUMBER,
     TO_SERIAL_NUMBER,
     PRODUCT_CODE,
     PRODUCT_TRANSACTION_ID)
  VALUES
    (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL, --TRANSACTION_INTERFACE_ID
     SYSDATE, --LAST_UPDATE_DATE
     1115, --LAST_UPDATED_BY
     SYSDATE, --CREATION_DATE
     1115, --CREATED_BY
     0, --LAST_UPDATE_LOGIN
     'S108', --FM_SERIAL_NUMBER
     'S108', --TO_SERIAL_NUMBER
     'RCV', --PRODUCT_CODE
     RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
     );*/
      dbms_output.put_line('insert detail:' || v_line_count);
    end loop;
    dbms_output.put_line('insert detail success');
  end loop;
exception
  when others then
    dbms_output.put_line('exception:' || sqlerrm);
    rollback;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/797362/viewspace-592577/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/797362/viewspace-592577/