用接口开发接PO收入库程序

            用接口开发采购入库程序在网上一搜就会有很多，但基本上都是一样的程序，在自己的系统中操作时很有可能不成功，下面就针对这种情况，将自己操作的全过程记录下来，供大家参考:

           SQL代码(网上搜的):

        

/* Formatted on 2014-10-7 15:23:20 (QP5 v5.115.810.9015) */
/*采购接收接口开发
RCV_TRANSACTIONS_INTERFACE;

RCV_HEADERS_INTERFACE;

mtl_transaction_lots_interface; (物料属于批次控制)

**************************************************
*/

DECLARE
   V_INTERFACE_TRANSACTION_ID NUMBER;
   V_GROUP_ID     NUMBER;
   V_SHIPMENT_HEADER_ID NUMBER;
   V_SHIPMENT_LINE_ID NUMBER;
   V_LINE_LOCATION_ID NUMBER;
   V_PO_DISTRIBUTION_ID NUMBER;
   V_HEADER_INTERFACE_ID NUMBER;
   V_DELIVER_TO_PERSON_ID NUMBER;
   L_REQUEST_ID   NUMBER;
   L_REQUEST_ID2  NUMBER;
   V_DESTINATION_TYPE_CODE VARCHAR2(100);
   V_EXPECTED_RECEIPT_DATE DATE;
   V_COUNTRY_OF_ORIGIN_CODE VARCHAR2(100);
   V_LOT_CONTROL  NUMBER;
   V_USE_MTL_SERIAL NUMBER;
   V_RCV_QTY      NUMBER;
   V_TRANS_QTY    NUMBER;
   L_PHASE        VARCHAR2(10);
   L_STATUS       VARCHAR2(10);
   L_DEV_PHASE    VARCHAR2(10);
   L_DEV_STATUS   VARCHAR2(10);
   L_MESSAGE      VARCHAR2(10);
   L_REQUEST_LAUCH1_STATUS BOOLEAN;
   V_INTERFACE_ID NUMBER;

   CURSOR CUR_PO_INFO
   IS
      SELECT PHA.SEGMENT1
            ,PHA.CURRENCY_CODE
            ,PHA.SHIP_TO_LOCATION_ID
            ,PHA.VENDOR_ID
            ,PHA.VENDOR_SITE_ID
            ,PLA.*
        FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA
       WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
         AND PHA.ORG_ID = PLA.ORG_ID
         AND PHA.ORG_ID = 85                                    
         AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
         AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
         AND PHA.WF_ITEM_TYPE = 'POAPPRV'
         AND PHA.SEGMENT1 = '123214060066' ;
BEGIN
   FND_GLOBAL.APPS_INITIALIZE(USER_ID     => 11236  --7137
                             ,RESP_ID     => 50623
                             ,RESP_APPL_ID => 201 --Purchasing Super User , PO
                             );
   MO_GLOBAL.SET_POLICY_CONTEXT('S', 85);
   MO_GLOBAL.SET_ORG_CONTEXT(85, '', 'ONT');
   MO_GLOBAL.INIT('ONT');
   dbms_application_info.SET_CLIENT_INFO(85);

   FOR C_PO_INFO IN CUR_PO_INFO LOOP
      SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
        INTO V_INTERFACE_TRANSACTION_ID
        FROM DUAL;

      SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO V_GROUP_ID FROM DUAL;

      SELECT RCV_SHIPMENT_HEADERS_S.NEXTVAL
        INTO V_SHIPMENT_HEADER_ID
        FROM DUAL;

      SELECT RCV_SHIPMENT_LINES_S.NEXTVAL INTO V_SHIPMENT_LINE_ID FROM DUAL;

      SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
        INTO V_HEADER_INTERFACE_ID
        FROM DUAL;

      SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
        INTO V_INTERFACE_ID
        FROM DUAL;

      BEGIN
         SELECT MSIB.LOT_CONTROL_CODE
           INTO V_LOT_CONTROL
           FROM MTL_SYSTEM_ITEMS_B MSIB
          WHERE MSIB.INVENTORY_ITEM_ID = C_PO_INFO.ITEM_ID
            AND MSIB.ORGANIZATION_ID = 104;                             --HUTS
      EXCEPTION
         WHEN OTHERS THEN
            V_LOT_CONTROL := NULL;
      END;

      IF V_LOT_CONTROL IS NULL THEN
         V_USE_MTL_SERIAL := NULL;
      ELSE
         V_USE_MTL_SERIAL := 1;
      END IF;

      --Get po line location informations
      BEGIN
         SELECT PLLA.LINE_LOCATION_ID
               ,PLLA.NEED_BY_DATE
               ,PLLA.COUNTRY_OF_ORIGIN_CODE
               ,(PLLA.QUANTITY - PLLA.QUANTITY_RECEIVED)
           INTO V_LINE_LOCATION_ID
               ,V_EXPECTED_RECEIPT_DATE
               ,V_COUNTRY_OF_ORIGIN_CODE
               ,V_TRANS_QTY
           FROM PO_LINE_LOCATIONS_ALL PLLA
          WHERE PLLA.PO_HEADER_ID = C_PO_INFO.PO_HEADER_ID
            AND PLLA.PO_LINE_ID = C_PO_INFO.PO_LINE_ID
            AND ROWNUM = 1;
      EXCEPTION
         WHEN OTHERS THEN
            V_LINE_LOCATION_ID := NULL;
            V_EXPECTED_RECEIPT_DATE := NULL;
            V_COUNTRY_OF_ORIGIN_CODE := NULL;
            V_TRANS_QTY := 0;
      END;

      --Get po_distribution_id
      BEGIN
         SELECT PDA.PO_DISTRIBUTION_ID
               ,PDA.DESTINATION_TYPE_CODE
               ,PDA.DELIVER_TO_PERSON_ID
           INTO V_PO_DISTRIBUTION_ID
               ,V_DESTINATION_TYPE_CODE
               ,V_DELIVER_TO_PERSON_ID
           FROM PO_DISTRIBUTIONS_ALL PDA
          WHERE PDA.PO_HEADER_ID = C_PO_INFO.PO_HEADER_ID
            AND PDA.PO_LINE_ID = C_PO_INFO.PO_LINE_ID
            AND ROWNUM = 1;
      EXCEPTION
         WHEN OTHERS THEN
            V_PO_DISTRIBUTION_ID := NULL;
            V_DESTINATION_TYPE_CODE := NULL;
      END;

      IF V_TRANS_QTY > 0 THEN
         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