import receipt transaction,Receiving Transaction Processor

CREATE OR REPLACE PROCEDURE INVIMPRCV_BG_1(ERRBUF              OUT VARCHAR2,
                                           RETCODE             OUT VARCHAR2,
                                           P_SHIPPING_ORG_ID   IN NUMBER,
                                           P_RECEIVING_ORG_ID  IN NUMBER,
                                           p_receiving_sub_inv IN varchar2,
                                           p_receipt_date      IN VARCHAR2,
                                           P_SHIPMENT_NUMBER   IN  VARCHAR2,
                                           P_SHIPPED_DATE      IN  VARCHAR2,
                                           P_PACKING_SLIP      IN  VARCHAR2,
                                           P_WAYBILL           IN  VARCHAR2,
                                           P_FREIGHT_CARRIER   IN  VARCHAR2,
                                           P_BILL_LADING       IN  VARCHAR2,
                                           P_CONTAINER         IN  number,
                                           P_RECEIVER         IN  VARCHAR2,
                                           P_COMMENTS         IN  VARCHAR2
                                           ) IS

  CURSOR receipt(req_id number) IS
    select r.receipt_num
      from rcv_shipment_headers r
     where r.request_id = req_id;
  CURSOR so_header IS
    SELECT distinct mt.TRANSACTION_REFERENCE so_id
      FROM MTL_MATERIAL_TRANSACTIONS mt, mtl_system_items_b msi
     WHERE mt.TRANSACTION_ACTION_ID NOT IN (24, 30)
       and mt.TRANSACTION_TYPE_ID = '33'
       and mt.TRANSACTION_ACTION_ID = 1
       and mt.TRANSACTION_SOURCE_TYPE_ID = 2
       and mt.inventory_item_id = msi.inventory_item_id
       and mt.organization_id = msi.organization_id
       and mt.organization_id = P_Shipping_ORG_ID -- 215 -- shipping org id --
       and mt.SHIPMENT_NUMBER =nvl(P_SHIPMENT_NUMBER,mt.SHIPMENT_NUMBER) /*414095*/
       and mt.WAYBILL_AIRBILL=nvl(P_WAYBILL,mt.WAYBILL_AIRBILL);
      -- and mt.FREIGHT_CODE=nvl(P_FREIGHT_CARRIER,mt.FREIGHT_CODE);

  CURSOR MMT_LINE IS
    SELECT oe.cust_po_number,
           msi.segment1 item_num,
           msi.inventory_item_id item_id,
           mt.TRANSACTION_QUANTITY,
           mt.TRANSACTION_UOM,
           mt.SHIPMENT_NUMBER,
           mt.WAYBILL_AIRBILL,
           mt.FREIGHT_CODE,
           mt.NUMBER_OF_CONTAINERS,
           mt.TRANSACTION_REFERENCE,
           oe.order_number,
           oe.order_number || '.' || ot.name || '.' || mt.source_code source_refer,
           mt.SOURCE_CODE,
           mt.SOURCE_LINE_ID,
           mt.TRANSFER_TRANSACTION_ID,
           mt.TRANSACTION_SET_ID,
           mt.RCV_TRANSACTION_ID,
           mt.MOVE_TRANSACTION_ID,
           mt.COMPLETION_TRANSACTION_ID,
           mt.transaction_source_name,
           mt.TRANSACTION_SOURCE_TYPE_ID,
           mt.TRANSACTION_SOURCE_ID,
           mt.subinventory_code,
           ol.orig_sys_document_ref po_header_id,
           decode(ol.split_from_line_id,
                  '',
                  substr(ol.orig_sys_line_ref,
                         1,
                         instr(ol.orig_sys_line_ref, '.') - 1),
                  (select substr(l.orig_sys_line_ref,
                                 1,
                                 instr(l.orig_sys_line_ref, '.') - 1)
                     from oe_order_lines_all l
                    where l.line_id = ol.split_from_line_id)) po_line_id,
           ol.orig_sys_shipment_ref po_location_id
      FROM MTL_MATERIAL_TRANSACTIONS mt,
           mtl_system_items_b        msi,
           oe_order_headers_all      oe,
           oe_order_lines_all        ol,
           OE_TRANSACTION_TYPES_TL   ot
     WHERE mt.TRANSACTION_ACTION_ID NOT IN (24, 30)
       and mt.TRANSACTION_TYPE_ID = '33'
       and mt.TRANSACTION_ACTION_ID = 1
       and mt.TRANSACTION_SOURCE_TYPE_ID = 2
       and mt.inventory_item_id = msi.inventory_item_id
       and mt.organization_id = msi.organization_id
       and mt.TRANSACTION_REFERENCE = oe.header_id(+)
       and oe.org_id = P_Shipping_ORG_ID --215 -- shipping org id --
       and ot.transaction_type_id = oe.order_type_id
       and mt.organization_id = P_Shipping_ORG_ID -- 215 -- shipping org id --
       and oe.cust_po_number is not null
       and ot.language = 'US'
          -- and oe.order_number = '101003869'
       and oe.header_id = ol.header_id
       and ol.line_id = mt.SOURCE_LINE_ID
       and mt.SHIPMENT_NUMBER = P_SHIPMENT_NUMBER --414095
     group by oe.cust_po_number,
              msi.segment1,
              msi.inventory_item_id,
              mt.TRANSACTION_QUANTITY,
              mt.TRANSACTION_UOM,
              mt.SHIPMENT_NUMBER,
              mt.WAYBILL_AIRBILL,
              mt.FREIGHT_CODE,
              mt.NUMBER_OF_CONTAINERS,
              mt.TRANSACTION_REFERENCE,
              oe.order_number,
              oe.order_number || '.' || ot.name || '.' || mt.source_code,
              mt.SOURCE_CODE,
              mt.SOURCE_LINE_ID,
              mt.TRANSFER_TRANSACTION_ID,
              mt.TRANSACTION_SET_ID,
              mt.RCV_TRANSACTION_ID,
              mt.MOVE_TRANSACTION_ID,
              mt.COMPLETION_TRANSACTION_ID,
              mt.transaction_source_name,
              mt.TRANSACTION_SOURCE_TYPE_ID,
              mt.TRANSACTION_SOURCE_ID,
              mt.subinventory_code,
              ol.orig_sys_document_ref,
              ol.orig_sys_shipment_ref,
              ol.split_from_line_id,
              ol.orig_sys_line_ref;

  CURSOR PO_LINE(header_id number, line_id number, location_id number) IS
    SELECT pl.org_Id,
           pl.po_header_id,
           pl.item_id,
           pl.po_line_id,
           pl.line_num,
           pll.quantity,
           pl.unit_meas_lookup_code,
           mp.organization_code,
           pll.line_location_id,
           pll.closed_code,
           pll.quantity_received,
           pll.cancel_flag,
           pll.shipment_num,
           pda.destination_type_code,
           pda.deliver_to_person_id,
           pll.ship_to_location_id,
           pda.destination_subinventory,
           pda.destination_organization_id
      FROM po_lines_all              pl,
           po_line_locations_all     pll,
           mtl_parameters            mp,
           apps.po_distributions_all pda
     WHERE pl.po_header_id = header_id --338882
       and pl.po_line_id = line_id
       and pll.line_location_id = location_id
       AND pl.po_line_id = pll.po_line_id
       AND pll.line_location_id = pda.line_location_id
       AND pll.ship_to_organization_id = mp.organization_id;

  l_req_id        NUMBER;
  l_complete_flag BOOLEAN;
  l_phase         VARCHAR2(100);
  l_status        VARCHAR2(100);
  l_dev_phase     VARCHAR2(100);
  l_dev_status    VARCHAR2(100);
  l_message       VARCHAR2(1000);

  X_USER_ID        NUMBER :=fnd_profile.value('USER_ID');
  X_PO_HEADER_ID   NUMBER;
  X_VENDOR_ID      NUMBER;
  X_SEGMENT1       VARCHAR2(20);
  X_ORG_ID         NUMBER;
  X_LINE_NUM       NUMBER;
  x_group_id       number;
  X_vendor_site_id NUMBER;
  v_count  number :=0;
  --Receipt header--
  x_receiving_subinv VARCHAR2(30) := p_receiving_sub_inv; -- 'ME RM'; --  Receiving Subinventory:
  v_receipt_date     RCV_SHIPMENT_HEADERS.CREATION_DATE%TYPE :=to_date(P_receipt_date,'YYYY/MM/DD HH24:MI:SS');
  v_shipment_num     RCV_SHIPMENT_HEADERS.Shipment_Num%TYPE :=P_SHIPMENT_NUMBER||'121'; --4140954;
  v_SHIPPED_DATE     RCV_SHIPMENT_HEADERS.SHIPPED_DATE%TYPE := to_date(p_SHIPPED_DATE,'YYYY/MM/DD HH24:MI:SS');
  v_packing_slip     RCV_SHIPMENT_HEADERS.Packing_Slip%TYPE := p_packing_slip;
  v_waybill          RCV_SHIPMENT_HEADERS.Waybill_Airbill_Num%TYPE:= p_waybill;
  V_Freight_Carrier  RCV_SHIPMENT_HEADERS.Freight_Carrier_Code%TYPE := p_Freight_Carrier;
  V_Bill_Lading      RCV_SHIPMENT_HEADERS.Bill_Of_Lading%TYPE := p_Bill_Lading;
  V_Container        RCV_SHIPMENT_HEADERS.Num_Of_Containers%TYPE := p_Container;
  v_receiver         HR_EMPLOYEES.full_name%TYPE:=p_receiver;--Macau, Inventory User --PO_INQ_SV.GET_PERSON_NAME(RSH.EMPLOYEE_ID)
  v_comments         RCV_SHIPMENT_HEADERS.Comments%TYPE:= p_comments;
  --Receipt header--
 v_shipping_org VARCHAR2(30);
 v_receiving_org VARCHAR2(30);
 v_Receiver_name VARCHAR2(100);
BEGIN
select name into v_shipping_org
 from hr_organization_units o where o.organization_id =P_SHIPPING_ORG_ID;
select name into v_receiving_org
 from hr_organization_units o where o.organization_id =P_RECEIVING_ORG_ID;

/**********User Input Parameters*****************/
      fnd_file.put_line(fnd_file.output, 'Shipping Org:' ||v_shipping_org||';');
      fnd_file.put_line(fnd_file.output, 'Receiving Org:' ||v_receiving_org||';');
      fnd_file.put_line(fnd_file.output, 'Receiving Subinventory:'||p_receiving_sub_inv||';');
      fnd_file.put_line(fnd_file.output, 'Receipt Date:'||P_receipt_date||';');
      fnd_file.put_line(fnd_file.output, 'Shipment Number:' ||P_SHIPMENT_NUMBER||';');
      fnd_file.put_line(fnd_file.output, 'Shipped Date:'||p_SHIPPED_DATE||';');
      fnd_file.put_line(fnd_file.output, 'Packing Slip:'||p_packing_slip||';'||chr(13)||
                           'WayBill/AirBill:'||p_waybill||';'||chr(13)||
                           'Freight Carrier:'||p_Freight_Carrier||';'||chr(13)||
                           'Bill of Lading:'||p_Freight_Carrier||';'||chr(13)||
                           'Container:'||p_Container||';'||chr(13)||
                           'Received By:'||v_Receiver_name||';'||chr(13)||
                           'Comments:'||p_comments||';'||chr(13)
                          );
                    

  fnd_file.put_line(fnd_file.output, '***ROI RCV API Insert Script***');

  FOR CURSOR0 IN so_header LOOP
    fnd_file.put_line(fnd_file.output, CURSOR0.so_id);
    SELECT PO_HEADER_ID, VENDOR_ID, vendor_site_id, SEGMENT1, ORG_ID
      INTO X_PO_HEADER_ID,
           X_VENDOR_ID,
           X_vendor_site_id,
           X_SEGMENT1,
           X_ORG_ID
      FROM PO_HEADERS_ALL
     WHERE SEGMENT1 = (select cust_po_number
                         from oe_order_headers_all oe
                        where oe.header_id = CURSOR0.so_id)
       AND ORG_ID = p_receiving_org_id; --216; -- receiving org_id 
    
    fnd_file.put_line(fnd_file.output, 'PO number :'||X_SEGMENT1);
    ----insert header --- 
    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, --receipt date--
       created_by,
       vendor_id,
       vendor_site_id,
       expected_receipt_date,
       validation_flag,
       SHIPMENT_NUM,
       SHIPPED_DATE,
       PACKING_SLIP,
       WAYBILL_AIRBILL_NUM,
       FREIGHT_CARRIER_CODE,
       BILL_OF_LADING,
       NUM_OF_CONTAINERS,
       EMPLOYEE_NAME ,--EMPLOYEE_ID,
       COMMENTS)
      SELECT rcv_headers_interface_s.NEXTVAL,
             rcv_interface_groups_s.NEXTVAL,
             'PENDING',
             'VENDOR',
             'NEW',
             'RECEIVE',
             SYSDATE,
             x_user_id,
             0,
             v_receipt_date,
             x_user_id,
             x_vendor_id,
             X_vendor_site_id,
             SYSDATE,
             'Y',
             v_shipment_num,
             v_SHIPPED_DATE,
             v_packing_slip,
             v_waybill,
             V_Freight_Carrier,
             V_Bill_Lading,
             V_Container,
             v_receiver,
             v_comments
        FROM DUAL;
/****** Finding source from Material transaction  *****/
--Print import contents for checking --
    FOR CURSOR2 IN MMT_LINE LOOP
 
      ----insert line ---
      FOR CURSOR1 IN PO_LINE(to_number(CURSOR2.po_header_id),
                             to_number(CURSOR2.po_line_id),
                             to_number(CURSOR2.po_location_id)) LOOP
        IF CURSOR1.CLOSED_CODE IN ('APPROVED', 'OPEN') AND
           CURSOR1.QUANTITY_RECEIVED < CURSOR1.QUANTITY/* AND
          (CURSOR1.QUANTITY- CURSOR1.QUANTITY_RECEIVED)             and NVL(CURSOR1.CANCEL_FLAG, 'N') = 'N' THEN
          
          v_count:=v_count+1;
          INSERT INTO rcv_transactions_interface
            (interface_transaction_id,
             GROUP_ID,
             last_update_date,
             last_updated_by,
             creation_date,
             created_by,
             last_update_login,
             transaction_type,
             transaction_date,
             processing_status_code,
             processing_mode_code,
             transaction_status_code,
             po_header_id, --****
             po_line_id,--
             item_id,--
             quantity,--
             unit_of_measure,--
             po_line_location_id,--
             auto_transact_code, --
             receipt_source_code,
             to_organization_code,
             to_organization_id,
             ship_to_location_id, --
             source_document_code,
             destination_type_code,
             deliver_to_person_id,
             deliver_to_location_id,
             subinventory,
             header_interface_id,
             validation_flag)
            SELECT rcv_transactions_interface_s.NEXTVAL,
                   rcv_interface_groups_s.CURRVAL,
                   SYSDATE,
                   x_user_id,
                   SYSDATE,
                   x_user_id,
                   0,
                   'RECEIVE', --transaction_type,
                   SYSDATE,
                   'PENDING',
                   'BATCH',
                   'PENDING',
                   CURSOR1.po_header_id,
                   CURSOR1.PO_LINE_ID,
                   CURSOR1.ITEM_ID,
                   --(CURSOR1.QUANTITY- CURSOR1.QUANTITY_RECEIVED),
                   -CURSOR2.TRANSACTION_QUANTITY,-- CURSOR1.QUANTITY,
                   CURSOR2.TRANSACTION_UOM, -- CURSOR1.UNIT_MEAS_LOOKUP_CODE,
                   CURSOR1.LINE_LOCATION_ID,
                   'DELIVER', --auto_transact_code
                   'VENDOR', --receipt_source_code,
                   CURSOR1.ORGANIZATION_CODE,
                   CURSOR1.org_Id,--to_organization_id,
                   CURSOR1.ship_to_location_id,
                   'PO',
                   CURSOR1.destination_type_code,
                   CURSOR1.deliver_to_person_id,
                   CURSOR1.ship_to_location_id,
                   nvl(CURSOR1.destination_subinventory, x_receiving_subinv),
                   rcv_headers_interface_s.CURRVAL,
                   'Y'
              FROM DUAL;
       
          fnd_file.put_line(fnd_file.output, 'PO line: ' || CURSOR1.LINE_NUM ||
                               ' Shipment: ' || CURSOR1.SHIPMENT_NUM || 'Outstanding Quantity: ' || (CURSOR1.QUANTITY- CURSOR1.QUANTITY_RECEIVED)||
                               ' has been inserted into ROI.');
        ELSE
          fnd_file.put_line(fnd_file.output, 'PO line ' || CURSOR1.LINE_NUM ||' Outstanding Quantity: ' || (CURSOR1.QUANTITY- CURSOR1.QUANTITY_RECEIVED)||
                               ' is either closed, cancelled, received; then this line not import');
        END IF;
       
      END LOOP;
--Print import Receipt line contents for checking --
/*****Shipment Number ;
      Item number;
      Transaction Qty;
      Transaction Uom;
      WayBill/Air Bill;
      Freight code;
      Container;
    ***********/
      fnd_file.put_line(fnd_file.output, 'Shipment Number:' ||CURSOR2.SHIPMENT_NUMBER||';');
      fnd_file.put_line(fnd_file.output, 'Item number:'||CURSOR2.item_num||';'||chr(13)||
                           'Transaction Qty:'||-CURSOR2.TRANSACTION_QUANTITY||';'||chr(13)||
                           'Transaction Uom:'||CURSOR2.TRANSACTION_UOM||';'||chr(13)||
                           'WayBill/Air Bill:'||CURSOR2.WAYBILL_AIRBILL||';'||chr(13)||
                           'Freight code:'||CURSOR2.FREIGHT_CODE||';'||chr(13)||
                           'Container:'||CURSOR2.NUMBER_OF_CONTAINERS||';'||chr(13)||
                           'Sales number:'||CURSOR2.order_number||';'||chr(13)||
                           'Source refer:'||CURSOR2.source_refer||';'||chr(13)
                          );
                   
    end loop;
 
  end loop;
  fnd_file.put_line(fnd_file.output, 'Insert line Count:'||v_count||';');
  fnd_file.put_line(fnd_file.output, '*** ROI COMPLETE - End ***');

  COMMIT;
 
  fnd_file.put_line(fnd_file.output, '*** Call Receiving Transaction Processor Reoport ***');
  ---*************************--
  fnd_global.apps_initialize(user_id           => fnd_profile.value('USER_ID'),
                             resp_id           => fnd_profile.value('RESP_ID'),
                             resp_appl_id      => fnd_profile.value('RESP_APPL_ID'), --GL
                             security_group_id => 0);

  l_req_id := fnd_request.submit_request('PO',
                                         'RVCTP',
                                         null,
                                         sysdate,
                                         false,
                                         'BATCH',
                                         null);
  commit;

  IF l_req_id <= 0 THEN
      
  
    fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor runing exception ***');
  ELSIF l_req_id > 0 THEN
 
    fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor Request Submit successful ***');
    COMMIT;
    l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
                                                       1,
                                                       3600,
                                                       l_phase,
                                                       l_status,
                                                       l_dev_phase,
                                                       l_dev_status,
                                                       l_message);
  
                                                      
  END IF;
 
  if l_complete_flag  then
    FOR CURSOR3 IN receipt(l_req_id) LOOP
    if CURSOR3.receipt_num is null then
    --** check the po_interface_errors*--
    select count(*)into v_count
     from po_interface_errors a where a.request_id=l_req_id;
      fnd_file.put_line(fnd_file.output,'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
    --** check the po_interface_errors*--
    else
     fnd_file.put_line(fnd_file.output,'Receipt Number:' || CURSOR3.receipt_num);
    end if;

    end loop;
  end if;
  --***********************************--
END;
 

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

转载于:http://blog.itpub.net/9182041/viewspace-682152/