采购接口管理

采购接收常用表 

RCV_TRANSACTION_INTERFACE
  RCV_LOTS_INTERFACE
  PO_INTERFACE_ERRORS
  MTL_TRANSACTION_LOTS_TEMP
  MTL_TRANSACTION_LOTS_INTERFACE
  MTL_SUPPLY
  RCV_SUPPLY

MTL_SUPPLY表的作用

1、当请购单创建、审批后，MTL_SUPPLY的变化
a.当创建完请购单,MTL_SUPPLY为空
b.当审批后,MTL_SUPPLY中产生一条数据，SUPPLY_TYPE_CODE=REQ
c.当请购单审批后，请购头与请购行存入MS.REQ_HEADER_ID, MS.REQ_LINE_ID，此时MS.SUPPLY_TYPE_CODE=REQ
 
2、当PO创建、审批后，MTL_SUPPLY的变化
a.当请购单转换成po后，在未审批时，MTL_SUPPLY为空
b.当审批后,MTL_SUPPLY中原有的SUPPLY_TYPE_CODE=REQ被更改为SUPPLY_TYPE_CODE=PO
c.一般而言，当请购单自动创建为采购单时，其SUPPLY_TYPE_CODE=REQ被更改为SUPPLY_TYPE_CODE=PO
d.当审批后的po做取消时，MTL_SUPPLY中SUPPLY_TYPE_CODE=PO被更改为SUPPLY_TYPE_CODE=REQ
e.--当调用由请购单自动开采购单的程序时，如果未审批，MS.PO_HEADER_ID    ,MS.PO_LINE_ID,
   --MS.PO_RELEASE_ID, MS.PO_LINE_LOCATION_ID    ,MS.PO_DISTRIBUTION_ID,为空，如果已审批
   --MS.REQ_HEADER_ID, MS.REQ_LINE_ID被清空，MS.SUPPLY_TYPE_CODE=PO,对于在po单中修改数量或添加新的采购订单行
   --而未审批，原有已审批的采购订单行数据不变，但新添加的行未录入该表
   --当审批后的po做取消时，MTL_SUPPLY中SUPPLY_TYPE_CODE=PO被更改为SUPPLY_TYPE_CODE=REQ,
   --MS.REQ_HEADER_ID, MS.REQ_LINE_ID被填入原有的请购单的头和行值，同时， MS.PO_HEADER_ID,MS.PO_LINE_ID,
   --MS.PO_RELEASE_ID, MS.PO_LINE_LOCATION_ID,MS.PO_DISTRIBUTION_ID, MS.NEED_BY_DATE,MS.RECEIPT_DATE,
   --MS.EXPECTED_DELIVERY_DATE,  被清空
3、当PO接收后，MTL_SUPPLY的变化
  a.当po完全接收后，MTL_SUPPLY中原有的SUPPLY_TYPE_CODE=PO被更改为SUPPLY_TYPE_CODE=RECEIVING
  b. --当采购单做完接收后，MTL_SUPPLY中SUPPLY_TYPE_CODE=PO被更改为SUPPLY_TYPE_CODE=RECEIVING,
     --同时 MS.SHIPMENT_HEADER_ID,MS.SHIPMENT_LINE_ID,  MS.RCV_TRANSACTION_ID    ,存入shipment的头信息，行信息，及
     --rcv_transaction的transaction_id
4、当PO某行部分接收后，MTL_SUPPLY的变化
  a.当PO某行部分接收后,接受行中原有的SUPPLY_TYPE_CODE=PO被更改为SUPPLY_TYPE_CODE=RECEIVING
5、当PO检验后，MTL_SUPPLY有什么变化
  a.当PO检验后,接受行中原有的SUPPLY_TYPE_CODE=RECEIVING不变
6、当PO部分入库后，MTL_SUPPLY有什么变化；全部入库后呢
  a.当PO部分入库后,MTL_SUPPLY中该行记录被删除，而全部入库则该采购单所有行记录被删除
涉及的表
 
--已审批的请购单
SELECT prh.requisition_header_id, prl.requisition_line_id,prh.segment1
  from PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
where prh.requisition_header_id = prl.requisition_header_id
   and prh.requisition_header_id = 662
   and prh.authorization_status  = 'APPROVED'
   
--已审批的采购单   
SELECT ph.po_header_id,pl.PO_LINE_ID,ph.segment1,ph.*
  from Po_Lines_all pl,Po_Headers_All ph
where pl.PO_HEADER_ID=ph.po_header_id
/*   and ph.po_header_id = 41526*/
   and ph.authorization_status  = 'APPROVED'
   and NVL(ph.cancel_flag,'N')<>'Y'
   and ph.creation_date>=trunc(sysdate)
--接收
 
select *
from RCV_SHIPMENT_HEADERS rsh,
      rcv_shipment_lines   rsl
where rsh.shipment_header_id=rsl.shipment_header_id
and rsh.creation_date>=trunc(sysdate)
and rsh.receipt_num='185631'
      
select *
from rcv_transactions rt
where  rt.transaction_id=870339
ｔｒｍ上的解释如下
MTL_SUPPLY stores incoming supply information for an organization.
This table forms one of the sources in Inventory's Demand-Supply form.
There are four different types of supply captured in this table:
 
1) Approved Requisitions
2) Approved Purchase orders
3) Shipments from suppliers
4) Intransit shipments from another organization
 
Types 3 and 4 could be distinguished by the presence of data in the
INTRANSIT_OWNING_ORGANIZATION_ID column, which identifies the
ownership of the items in intransit. If this column is null then it
means that the shipment supply is from a Vendor.
.
This information is used by the available to promise routine to derive
the ATP information as appropiate. Quantities of items in intransit
are also kept track of in the table.
.
Records in MTL_SUPPLY are created every time you approve a requisition
or a PO or create an intransit shipment. One record of REQ type will
be created for one requisition line when the requisition is approved.
One record of PO type will be created per PO distribution when a PO is
approved and one record per shipment line will be created when a
shipment is created.
.
Records in MTL_SUPPLY will be recreated whenever there are
transactions such as return to receiving, return to vendor or
cancellation of purchase orders.
.
Records in MTL_SUPPLY are deleted every time you change the document
status to not approved. For example, a PO would require approval if
you change the line and shipment quantity.
If such a PO is re-approved then the PO supply will be recreated for
the new quantity.
.
The supply type code of a REQ supply record is changed to PO every
time a requisition is autocreated. Similarly the supply type code is
changed from PO to RECEIVING when a PO is fully received. If a PO is
partially received then a supply with the supply type code of
RECEIVING will be created for the quantity received. When the receipt
is delivered the RECEIVING supply is deleted. SHIPMENT supply works in
the same way as PO supply.
.
There is a database trigger on MTL_SUPPLY with the name MTL_SUPPLY_T.
This trigger fires on insert, update or deletion of records in

MTL_SUPPLY. It inserts records into MRP_RELIEF_INTERFACE table 

 

RCV：处理模式

此选项指明在保存对接收事务处理所做的工作之后使用的处理模式：
联机—直接调用“接收事务处理程序”。
即期—将事务处理传送至接口表，并为自上一次保存所做的工作之后输入的事务处理组调用“接收事务处理处理程序”。 
批—将事务处理传送至接口表（“接收事务处理处理程序”在下一次运行时将从接口表中挑选这些事务处理）

更新接口表数据

     更新接口表数据，让错误数据重新提交
UPDATE RCV_HEADERS_INTERFACE T
   SET T.PROCESSING_REQUEST_ID  = NULL,
       T.RECEIPT_HEADER_ID      = NULL,
       T.VALIDATION_FLAG        = 'Y',
       T.PROCESSING_STATUS_CODE = 'PENDING'
 WHERE T.HEADER_INTERFACE_ID IN
       (SELECT RTI.HEADER_INTERFACE_ID FROM RCV_TRANSACTIONS_INTERFACE RTI);
 
UPDATE PO.RCV_TRANSACTIONS_INTERFACE T
   SET T.REQUEST_ID                = NULL,
       T.PROCESSING_REQUEST_ID     = NULL,
       T.ORDER_TRANSACTION_ID      = NULL,
       T.PRIMARY_QUANTITY          = NULL,
       T.PRIMARY_UNIT_OF_MEASURE   = NULL,
       T.INTERFACE_TRANSACTION_QTY = NULL,
       T.VALIDATION_FLAG           = 'Y',
       T.PROCESSING_STATUS_CODE    = 'PENDING',
       T.TRANSACTION_STATUS_CODE   = 'PENDING',
       T.SHIPMENT_HEADER_ID        = NULL

 

接收入库的例子

CREATE OR REPLACE PACKAGE BODY XXUTS_O2C_PO_RECEIPT_PKG AS
  PROCEDURE MAIN(ERRBUF            OUT VARCHAR2,
                 RETCODE           OUT NUMBER,
                 P_ORGANIZATION_ID IN NUMBER,
                 P_PO_LAST_TWO     IN VARCHAR2) IS
    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;
    V_LOCATOR_ID               NUMBER;
    V_LOCATION_ID              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;
    V_USER_ID                  NUMBER;
    V_COUNT                    NUMBER;
    CURSOR CUR_PO_INFO IS
      SELECT PHA.SEGMENT1,
             PHA.CURRENCY_CODE,
             PHA.VENDOR_ID,
             PHA.VENDOR_SITE_ID,
             PHA.SHIP_TO_LOCATION_ID,
             PDA.PO_DISTRIBUTION_ID,
             PDA.DESTINATION_TYPE_CODE,
             PDA.DELIVER_TO_PERSON_ID,
             PDA.QUANTITY_ORDERED,
             PDA.LINE_LOCATION_ID,
             NVL(PDA.QUANTITY_ORDERED, 0) - NVL(PDA.QUANTITY_DELIVERED, 0) TRANS_QTY,
             PLA.*
        FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA, PO_DISTRIBUTIONS_ALL PDA
       WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
         AND PHA.ORG_ID = PLA.ORG_ID
         AND PHA.ORG_ID = 104 --OU:HUTS
         AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
         AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
         AND PDA.PO_HEADER_ID = PLA.PO_HEADER_ID
         AND PDA.PO_LINE_ID = PLA.PO_LINE_ID
         AND EXISTS
       (SELECT 1
                FROM PO_LINE_LOCATIONS_ALL PLLA
               WHERE PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
                 AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
                 AND (PLLA.QUANTITY - PLLA.QUANTITY_RECEIVED -
                     PLLA.QUANTITY_CANCELLED) > 0
                 AND PLLA.SHIP_TO_ORGANIZATION_ID = P_ORGANIZATION_ID)
         AND PHA.SEGMENT1 LIKE '%' || P_PO_LAST_TWO;
  BEGIN
    V_USER_ID := FND_GLOBAL.USER_ID;
    UPDATE PO_HEADERS_ALL PHA
       SET PHA.RATE_TYPE = 'User'
     WHERE PHA.CURRENCY_CODE <> 'RMB'
       AND PHA.ORG_ID = 104 --OU:HUTS
       AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
       AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
          --and pha.wf_item_type = 'POAPPRV'
       AND PHA.RATE_TYPE IS NULL
       AND PHA.SEGMENT1 LIKE '%' || P_PO_LAST_TWO;
    COMMIT;
    BEGIN
      SELECT MIL.INVENTORY_LOCATION_ID
        INTO V_LOCATOR_ID
        FROM MTL_ITEM_LOCATIONS MIL
       WHERE MIL.SUBINVENTORY_CODE = 'NA01'
         AND MIL.ORGANIZATION_ID = P_ORGANIZATION_ID
         AND ROWNUM = 1;
    EXCEPTION
      WHEN OTHERS THEN
        FND_FILE.PUT_LINE(1,
                          'Please define subinventory:NA01 and it is locator:NA01......;');
        RETURN;
    END;
    BEGIN
      SELECT COUNT(*)
        INTO V_COUNT
        FROM MTL_SECONDARY_INVENTORIES MSI
       WHERE MSI.SECONDARY_INVENTORY_NAME = 'NA01'
         AND MSI.ORGANIZATION_ID = P_ORGANIZATION_ID;
      IF V_COUNT = 0
      THEN
        FND_FILE.PUT_LINE(1, 'Please define subinventory:NA01;');
        RETURN;
      END IF;
    END;
    IF P_ORGANIZATION_ID = 122
    THEN
      BEGIN
        SELECT LOC.LOCATION_ID
          INTO V_LOCATION_ID
          FROM HR_LOCATIONS LOC
         WHERE LOC.LOCATION_CODE = 'HUTS-HQ Hangzhou'
           AND LOC.INVENTORY_ORGANIZATION_ID = P_ORGANIZATION_ID
           AND ROWNUM = 1;
      EXCEPTION
        WHEN OTHERS THEN
          FND_FILE.PUT_LINE(1, 'Location: HUTS-HQ Hangzhou not found!');
          RETURN;
      END;
    END IF;
    SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO V_GROUP_ID FROM DUAL;
    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 = P_ORGANIZATION_ID; --p_organization_id; --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 PLLA.LINE_LOCATION_ID = C_PO_INFO.LINE_LOCATION_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;
      V_PO_DISTRIBUTION_ID    := C_PO_INFO.PO_DISTRIBUTION_ID;
      V_DESTINATION_TYPE_CODE := C_PO_INFO.DESTINATION_TYPE_CODE;
      V_DELIVER_TO_PERSON_ID  := C_PO_INFO.DELIVER_TO_PERSON_ID;
      V_TRANS_QTY             := NVL(C_PO_INFO.TRANS_QTY, 0);
      IF P_ORGANIZATION_ID <> 122
      THEN
        V_LOCATION_ID := C_PO_INFO.SHIP_TO_LOCATION_ID;
      END IF;
      IF V_TRANS_QTY > 0
      THEN
        --Insert into header interface
        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,
           VENDOR_ID,
           SHIP_TO_ORGANIZATION_ID,
           EXPECTED_RECEIPT_DATE,
           VALIDATION_FLAG)
        VALUES
          (V_HEADER_INTERFACE_ID, --Header_Interface_Id
           V_GROUP_ID, --Group_Id
           'PENDING', --Processing_Status_Code
           'VENDOR', --Receipt_Source_Code
           'NEW', --Transaction_Type
           'DELIVER', --Auto_Transact_Code
           SYSDATE, --Last_Update_Date
           V_USER_ID, --Last_Updated_By
           V_USER_ID, --Last_Update_Login
           SYSDATE, --Creation_Date
           V_USER_ID, --Created_By
           C_PO_INFO.VENDOR_ID, --Vendor_Id
           P_ORGANIZATION_ID, --Ship_To_Organization_Id,
           SYSDATE, --Expected_Receipt_Date
           'Y' --Validation_Flag
           );
        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,
           QUANTITY,
           UNIT_OF_MEASURE,
           ITEM_ID,
           ITEM_DESCRIPTION,
           EMPLOYEE_ID,
           AUTO_TRANSACT_CODE,
           SHIP_TO_LOCATION_ID,
           RECEIPT_SOURCE_CODE,
           VENDOR_ID,
           SOURCE_DOCUMENT_CODE,
           PO_HEADER_ID,
           PO_LINE_ID,
           PO_LINE_LOCATION_ID,
           DESTINATION_TYPE_CODE,
           PO_DISTRIBUTION_ID,
           DELIVER_TO_PERSON_ID,
           LOCATION_ID,
           DELIVER_TO_LOCATION_ID,
           SUBINVENTORY,
           LOCATOR_ID,
           HEADER_INTERFACE_ID,
           DOCUMENT_NUM,
           TO_ORGANIZATION_ID,
           CURRENCY_CONVERSION_TYPE,
           VALIDATION_FLAG)
        VALUES
          (V_INTERFACE_TRANSACTION_ID, --INTERFACE_TRANSACTION_ID,
           V_GROUP_ID, --GROUP_ID,
           SYSDATE, --LAST_UPDATE_DATE,
           V_USER_ID, --LAST_UPDATED_BY,
           SYSDATE, --CREATION_DATE,
           V_USER_ID, --CREATED_BY,
           V_USER_ID, --LAST_UPDATE_LOGIN,
           'RECEIVE', --TRANSACTION_TYPE,
           SYSDATE, --TRANSACTION_DATE,
           'PENDING', --PROCESSING_STATUS_CODE,
           'IMMEDIATE', --PROCESSING_MODE_CODE,
           'PENDING', --TRANSACTION_STATUS_CODE,
           V_TRANS_QTY, --QUANTITY,
           C_PO_INFO.UNIT_MEAS_LOOKUP_CODE, --UNIT_OF_MEASURE,
           C_PO_INFO.ITEM_ID, --ITEM_ID,
           C_PO_INFO.ITEM_DESCRIPTION, --ITEM_DESCRIPTION
           0, --EMPLOYEE_ID,
           'DELIVER', --AUTO_TRANSACT_CODE,
           V_LOCATION_ID, --c_po_info.ship_to_location_id, --SHIP_TO_LOCATION_ID,
           'VENDOR', --RECEIPT_SOURCE_CODE,
           C_PO_INFO.VENDOR_ID, --VENDOR_ID,
           'PO', --SOURCE_DOCUMENT_CODE,
           C_PO_INFO.PO_HEADER_ID, --PO_HEADER_ID,
           C_PO_INFO.PO_LINE_ID, --PO_LINE_ID,
           V_LINE_LOCATION_ID, --PO_LINE_LOCATION_ID,
           V_DESTINATION_TYPE_CODE, --DESTINATION_TYPE_CODE,
           V_PO_DISTRIBUTION_ID, --po_distribution_id
           V_DELIVER_TO_PERSON_ID, --DELIVER_TO_PERSON_ID,
           V_LOCATION_ID, --200,--LOCATION_ID,
           V_LOCATION_ID, --200,--DELIVER_TO_LOCATION_ID,
           'NA01', --SUBINVENTORY,
           V_LOCATOR_ID, --locator_id,
           V_HEADER_INTERFACE_ID, --HEADER_INTERFACE_ID,
           C_PO_INFO.SEGMENT1, --DOCUMENT_NUM,
           P_ORGANIZATION_ID, --TO_ORGANIZATION_ID,
           'Corporate',
           'Y' --VALIDATION_FLAG
           );
        IF V_LOT_CONTROL = 2
        THEN
          INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
            (TRANSACTION_INTERFACE_ID,
             ORIGINATION_TYPE,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             CREATION_DATE,
             CREATED_BY,
             LAST_UPDATE_LOGIN,
             LOT_NUMBER,
             TRANSACTION_QUANTITY,
             PRIMARY_QUANTITY,
             --SERIAL_TRANSACTION_TEMP_ID,
             PRODUCT_CODE,
             PRODUCT_TRANSACTION_ID)
          VALUES
            (V_INTERFACE_ID, --TRANSACTION_INTERFACE_ID
             3, --ORIGINATION_TYPE
             SYSDATE, --LAST_UPDATE_DATE
             V_USER_ID, --LAST_UPDATED_BY
             SYSDATE, --CREATION_DATE
             V_USER_ID, --CREATED_BY
             V_USER_ID, --LAST_UPDATE_LOGIN
             'Un-invoiced-CON', --LOT_NUMBER
             V_TRANS_QTY, --TRANSACTION_QUANTITY
             V_TRANS_QTY, --PRIMARY_QUANTITY
             --0,--MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
             'RCV', --PRODUCT_CODE
             V_INTERFACE_TRANSACTION_ID --PRODUCT_TRANSACTION_ID
             );
        END IF;
      END IF;
    END LOOP;
    L_REQUEST_ID  := FND_REQUEST.SUBMIT_REQUEST('PO',
                                                 --APPLICATIONI SHORT NAME;
                                                'RVCTP',
                                                 --PROGRAME SHORT NAME;
                                                '', '', FALSE, 'IMMEDIATE',
                                                V_GROUP_ID
                                                --104--104,
                                                --CHR(0)
                                                );
    L_REQUEST_ID2 := FND_REQUEST.SUBMIT_REQUEST('PO',
                                                 --APPLICATIONI SHORT NAME;
                                                'RCVDLPDT',
                                                 --PROGRAME SHORT NAME;
                                                '', '', FALSE,
                                                'P_group_id=' || V_GROUP_ID,
                                                'P_receipt_source_type=Supplier',
                                                'P_qty_precision=2',
                                                'P_org_id=' ||
                                                 P_ORGANIZATION_ID);
    FND_FILE.PUT_LINE(2,
                      'Receiving Transaction Processor: ' || L_REQUEST_ID ||
                       ' Receipt Traveller Concurrent program: ' ||
                       L_REQUEST_ID2);
    FND_FILE.PUT_LINE(2, 'Group Id: ' || V_GROUP_ID);
    COMMIT;
  END MAIN;
END XXUTS_O2C_PO_RECEIPT_PKG;
 
 
 
                                                                                    

ROI How to Return Receiving Transaction with Receiving Open Interface

来源于（ID 360340.1）
              
退回至供应商需要2步操作：
A) 退回至接收
TRANSACTION_TYPE = RETURN TO RECEIVING
PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)
B) 退回至供应商：
TRANSACTION_TYPE = RETURN TO VENDOR
PARENT_TRANSACTION_ID = (Transaction Id for RECEIVE transaction)
注意退回到供应商的parentid 是接收的ID
 
In order to be most comprehensive, each script has been tested for a Lot and Serial Controlled Item.
最复杂的接收，如用到批次管理和序列管理，那么需要在如下3个表中插入数据： 
RCV_TRANSACTIONS_INTERFACE,MTL_TRANSACTION_LOTS_INTERFACE  and MTL_SERIAL_NUMBERS_INTERFACE.
如果是标准接收那么只需要在RCV_TRANSACTIONS_INTERFACE 中插入数据
 
For a RETURN TO VENDOR transaction for a direct delivery receipt, only 1 step return is needed,
then following fields need to be changed:
TRANSACTION_TYPE = RETURN TO VENDOR
PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)
 
1) Standard Purchase Order number # 8415 has been entered
for Inventory Item = Lot-Serial-Controlled-1
Shipment Receipt Routing = Standard Receipt (RECEIVING_ROUTING_ID=1)
Destination Type Code = Inventory
 
 
2) Item  Lot-Serial-Controlled-1 is a lot and serial controlled Item
SELECT MP.ORGANIZATION_ID ORG_ID,
       MP.ORGANIZATION_CODE ORG_CODE,
       MSI.INVENTORY_ITEM_ID,
       MSI.SEGMENT1,
       DECODE(TO_CHAR(MSI.LOT_CONTROL_CODE), '2', 'Y', '1', 'N') LOT_CONTROL,
       DECODE(TO_CHAR(MSI.SERIAL_NUMBER_CONTROL_CODE), '1', 'None', '2',
              'Predefined', '5', 'Dynamic at INV receipt', '6',
              'Dynamic at SO issue') SERIAL_CONTROL
  FROM MTL_SYSTEM_ITEMS_B MSI, MTL_PARAMETERS MP
 WHERE MSI.SEGMENT1 LIKE '&item'
   AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID;
 
3) Receipt number  # 7970 for a quantity=2 has been entered (Receive and Deliver Transactions)
The deliver transaction for a quantity=2 on PO #  8415 for
item=Lot-Serial-Controlled-1 is entered
on Lot Number=S336
and Serial Number from SN0020 to SN0021
 
 
4) Use the script below to get details about the receipt transactions. Data from this
query is used in the insert statement below.
SELECT RSH.RECEIPT_NUM,
       PH.SEGMENT1 PO_NUMBER,
       RT.TRANSACTION_ID,
       RT.TRANSACTION_TYPE,
       RT.TRANSACTION_DATE,
       RT.QUANTITY,
       RT.UNIT_OF_MEASURE,
       RT.SHIPMENT_HEADER_ID,
       RT.SHIPMENT_LINE_ID,
       RT.SOURCE_DOCUMENT_CODE,
       RT.DESTINATION_TYPE_CODE,
       RT.EMPLOYEE_ID,
       RT.PARENT_TRANSACTION_ID,
       RT.PO_HEADER_ID,
       RT.PO_LINE_ID,
       PL.LINE_NUM,
       PL.ITEM_ID,
       PL.ITEM_DESCRIPTION,
       PL.UNIT_PRICE,
       RT.PO_LINE_LOCATION_ID,
       RT.PO_DISTRIBUTION_ID,
       RT.ROUTING_HEADER_ID,
       RT.ROUTING_STEP_ID,
       RT.DELIVER_TO_PERSON_ID,
       RT.DELIVER_TO_LOCATION_ID,
       RT.VENDOR_ID,
       RT.VENDOR_SITE_ID,
       RT.ORGANIZATION_ID,
       RT.SUBINVENTORY,
       RT.LOCATOR_ID,
       RT.LOCATION_ID,
       RSH.SHIP_TO_ORG_ID
  FROM RCV_TRANSACTIONS     RT,
       RCV_SHIPMENT_HEADERS RSH,
       PO_HEADERS_ALL       PH,
       PO_LINES_ALL         PL
 WHERE /*RSH.RECEIPT_NUM = '&RECEIPT_NUMBER'
   AND PH.SEGMENT1 LIKE '&PO_NUMBER'
   AND*/ PH.PO_HEADER_ID = PL.PO_HEADER_ID
   AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
   AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID;
 
5)  Run the following scripts to identify the Lot/Serial Information
related to the DELIVER transaction RCV_TRANSACTIONS.TRANSACTION_ID=524018；
 
SELECT LOT_TRANSACTION_TYPE, LOT_NUM, QUANTITY
  FROM RCV_LOT_TRANSACTIONS
 WHERE SOURCE_TRANSACTION_ID = &RTTRANSACTION_ID;
 
This script returns 1 record with LOT_TRANSACTION_TYPE=TRANSACTION
and LOT_NUM=S366
 
SELECT SERIAL_TRANSACTION_TYPE,
       SERIAL_NUM,
       SHIPMENT_LINE_ID,
       TRANSACTION_ID,
       LOT_NUM
  FROM RCV_SERIAL_TRANSACTIONS
 WHERE SOURCE_TRANSACTION_ID = &RTTRANSACTION_ID;
 
This script returns 2 records,one for each serial number between SN0020 and SN0021
For SERIAL_NUM=SN0020 and SERIAL_NUM=SN0021
SERIAL_TRANSACTION_TYPE=TRANSACTION
SHIPMENT_LINE_ID=484510
SOURCE_TRANSACTION_ID=524018
TRANSACTION_ID=524018
LOT_NUM=S366
 
A) RETURN to RECEIVING for PURCHASE ORDER Example
 退回至接收
1) Insert via ROI a RETURN TO RECEIVING  record on the DELIVER to INVENTORY Transaction
(RCV_TRANSACTIONS.TRANSACTION_ID=524018)
so to correct the quantity delivered from 2 to a quantity delivered of 1,
returning SERIAL_NUM=SN0021 for LOT_NUM=S366
 
Insert
. 1 record in RCV_TRANSACTIONS_INTERFACE table with
TRANSACTION_TYPE='RETURN TO RECEIVING'
PARENT_TRANSACTION_ID=524018 (Transaction Id for DELIVER transaction) for a positive quantity =1
and DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='VENDOR' and
SOURCE_DOCUMENT_CODE='PO'
. 1 record in MTL_TRANSACTIONS_LOTS_INTERFACE for a positive quantity=1
on lot number S366
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE, indicating
FM_SERIAL_NUMBER='SN0021'and TO_SERIAL_NUMBER='SN0021'
  
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,
   QUANTITY,
   UNIT_OF_MEASURE,
   ITEM_ID,
   EMPLOYEE_ID,
   SHIPMENT_HEADER_ID,
   SHIPMENT_LINE_ID,
   RECEIPT_SOURCE_CODE,
   VENDOR_ID,
   FROM_ORGANIZATION_ID,
   FROM_SUBINVENTORY,
   FROM_LOCATOR_ID,
   SOURCE_DOCUMENT_CODE,
   PARENT_TRANSACTION_ID,
   PO_HEADER_ID,
   PO_LINE_ID,
   PO_LINE_LOCATION_ID,
   PO_DISTRIBUTION_ID,
   DESTINATION_TYPE_CODE,
   DELIVER_TO_PERSON_ID,
   LOCATION_ID,
   DELIVER_TO_LOCATION_ID,
   VALIDATION_FLAG)
VALUES
  (RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --INTERFACE_TRANSACTION_ID
   RCV_INTERFACE_GROUPS_S.NEXTVAL, --GROUP_ID
   SYSDATE, --LAST_UPDATE_DATE
   0, --LAST_UPDATE_BY
   SYSDATE, --CREATION_DATE
   0, --CREATED_BY
   0, --LAST_UPDATE_LOGIN
   'RETURN TO RECEIVING', --TRANSACTION_TYPE
   SYSDATE, --TRANSACTION_DATE
   'PENDING', --PROCESSING_STATUS_CODE
   'BATCH', --PROCESSING_MODE_CODE
   'PENDING', --TRANSACTION_STATUS_CODE
   1, --QUANTITY
   'Each', --UNIT_OF_MEASURE
   169845, --ITEM_ID
   13706, --EMPLOYEE_ID
   477550, --SHIPMENT_HEADER_ID
   484510, --SHIPMENT_LINE_ID
   'VENDOR', --RECEIPT_SOURCE_CODE
   7927, --VENDOR_ID
   204, --FROM_ORGANIZATION_ID
   'Stores', --FROM_SUBINVENTORY
   NULL, --FROM_LOCATOR_ID
   'PO', --SOURCE_DOCUMENT_CODE
   524018, --PARENT_TRANSACTION_ID
   40924, --PO_HEADER_ID
   46915, --PO_LINE_ID
   81963, --PO_LINE_LOCATION_ID
   84349, --PO_DISTRIBUTION_ID
   'INVENTORY', --DESTINATION_TYPE_CODE
   NULL, --DELIVER_TO_PERSON_ID
   NULL, --LOCATION_ID
   NULL, --DELIVER_TO_LOCATION_ID
   'Y' --VALIDATION_FLAG
   );
 
  
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,
   SERIAL_TRANSACTION_TEMP_ID,
   PRODUCT_CODE,
   PRODUCT_TRANSACTION_ID)
VALUES
  (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --TRANSACTION_INTERFACE_ID
   SYSDATE, --LAST_UPDATE_DATE
   0, --LAST_UPDATED_BY
   SYSDATE, --CREATION_DATE
   0, --CREATED_BY
   0, --LAST_UPDATE_LOGIN
   'S366', --LOT_NUMBER
   1, --TRANSACTION_QUANTITY
   1, --PRIMARY_QUANTITY
   MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
   '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
   0, --LAST_UPDATED_BY
   SYSDATE, --CREATION_DATE
   0, --CREATED_BY
   0, --LAST_UPDATE_LOGIN
   'SN0021', --FM_SERIAL_NUMBER
   'SN0021', --TO_SERIAL_NUMBER
   'RCV', --PRODUCT_CODE
   RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
   );
通过如下SQL检测是否插入成功：
SELECT * FROM RCV_TRANSACTIONS_INTERFACE WHERE PO_HEADER_ID = 40924;
 
SELECT *
  FROM MTL_TRANSACTION_LOTS_INTERFACE
 WHERE PRODUCT_TRANSACTION_ID = &INTERFACE_TXN_ID;
 
SELECT *
  FROM MTL_SERIAL_NUMBERS_INTERFACE
 WHERE PRODUCT_TRANSACTION_ID = &INTERFACE_TXN_ID;
 
 
3) 在请求中运行Receiving Transaction Processor for the specific Group ID (GROUP_ID=29806)
 
4) Navigate to Receiving / Receiving Transactions Summary form
For PO 8415 , Receipt Number 7970 has Receive, Deliver and Return to Receiving transactions.  
到接收事物汇总处查询PO的接收情况
 
 
5) Navigate to Purchase Orders/Purchase Orders Summary
At shipment and distribution level, for the PO Number 8415
Quantity Received=2
Quantity Delivered=1
 
B) RETURN  to VENDOR Transaction for PURCHASE ORDER Example
 
6) Insert via ROI a RETURN TO VENDOR record on the RECEIVE Transaction
(RCV_TRANSACTIONS.TRANSACTION_ID=524017)
so to correct the quantity received from 2 to a quantity received of 1
 
Insert
. 1 record in RCV_TRANSACTIONS_INTERFACE table with
TRANSACTION_TYPE='RETURN TO VENDOR'
PARENT_TRANSACTION_ID=524017 (Transaction Id for RECEIVE transaction)
for a positive quantity = 1 indicating
DESTINATION_TYPE_CODE='RECEIVING'
RECEIPT_SOURCE_CODE='VENDOR' and SOURCE_DOCUMENT_CODE='PO'
. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a positive quantity=1
on lot number S366
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE, indicating
FM_SERIAL_NUMBER='SN0021'and TO_SERIAL_NUMBER='SN0021'
 插入RCV_TRANSACTIONS_INTERFACE表
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,
   QUANTITY,
   UNIT_OF_MEASURE,
   ITEM_ID,
   EMPLOYEE_ID,
   SHIPMENT_HEADER_ID,
   SHIPMENT_LINE_ID,
   RECEIPT_SOURCE_CODE,
   VENDOR_ID,
   FROM_ORGANIZATION_ID,
   FROM_SUBINVENTORY,
   FROM_LOCATOR_ID,
   SOURCE_DOCUMENT_CODE,
   PARENT_TRANSACTION_ID,
   PO_HEADER_ID,
   PO_LINE_ID,
   PO_LINE_LOCATION_ID,
   PO_DISTRIBUTION_ID,
   DESTINATION_TYPE_CODE,
   DELIVER_TO_PERSON_ID,
   LOCATION_ID,
   DELIVER_TO_LOCATION_ID,
   VALIDATION_FLAG)
VALUES
  (RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --INTERFACE_TRANSACTION_ID
   RCV_INTERFACE_GROUPS_S.NEXTVAL, --GROUP_ID
   SYSDATE, --LAST_UPDATE_DATE
   0, --LAST_UPDATE_BY
   SYSDATE, --CREATION_DATE
   0, --CREATED_BY
   0, --LAST_UPDATE_LOGIN
   'RETURN TO VENDOR', --TRANSACTION_TYPE
   SYSDATE, --TRANSACTION_DATE
   'PENDING', --PROCESSING_STATUS_CODE
   'BATCH', --PROCESSING_MODE_CODE
   'PENDING', --TRANSACTION_STATUS_CODE
   1, --QUANTITY
   'Each', --UNIT_OF_MEASURE
   169845, --ITEM_ID
   13706, --EMPLOYEE_ID
   477550, --SHIPMENT_HEADER_ID
   484510, --SHIPMENT_LINE_ID
   'VENDOR', --RECEIPT_SOURCE_CODE
   7927, --VENDOR_ID
   204, --FROM_ORGANIZATION_ID
   'Stores', --FROM_SUBINVENTORY
   NULL, --FROM_LOCATOR_ID
   'PO', --SOURCE_DOCUMENT_CODE
   524017, --PARENT_TRANSACTION_ID
   40924, --PO_HEADER_ID
   46915, --PO_LINE_ID
   81963, --PO_LINE_LOCATION_ID
   84349, --PO_DISTRIBUTION_ID
   'RECEIVING', --DESTINATION_TYPE_CODE
   NULL, --DELIVER_TO_PERSON_ID
   NULL, --LOCATION_ID
   NULL, --DELIVER_TO_LOCATION_ID
   'Y' --VALIDATION_FLAG
   );
 
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,
   SERIAL_TRANSACTION_TEMP_ID,
   PRODUCT_CODE,
   PRODUCT_TRANSACTION_ID)
VALUES
  (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --TRANSACTION_INTERFACE_ID
   SYSDATE, --LAST_UPDATE_DATE
   0, --LAST_UPDATED_BY
   SYSDATE, --CREATION_DATE
   0, --CREATED_BY
   0, --LAST_UPDATE_LOGIN
   'S366', --LOT_NUMBER
   1, --TRANSACTION_QUANTITY
   1, --PRIMARY_QUANTITY
   MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
   '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
   0, --LAST_UPDATED_BY
   SYSDATE, --CREATION_DATE
   0, --CREATED_BY
   0, --LAST_UPDATE_LOGIN
   'SN0021', --FM_SERIAL_NUMBER
   'SN0021', --TO_SERIAL_NUMBER
   'RCV', --PRODUCT_CODE
   RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
   );
 
7) Run the following scripts to check data have been correctly inserted
SELECT * FROM RCV_TRANSACTIONS_INTERFACE WHERE PO_HEADER_ID = 40924;
 
SELECT *
  FROM MTL_TRANSACTION_LOTS_INTERFACE
 WHERE PRODUCT_TRANSACTION_ID = &INTERFACE_TXN_ID;
 
SELECT *
  FROM MTL_SERIAL_NUMBERS_INTERFACE
 WHERE PRODUCT_TRANSACTION_ID = &INTERFACE_TXN_ID;
 
8) In Purchasing Responsibility, Run the Receiving Transaction Processor
for the specific Group ID (GROUP_ID=30053)
 
9) Navigate to Receiving / Receiving Transactions Summary form
For PO 8415 , Receipt Number 7970 has Receive, Deliver , Return to Receiving
and Return to Supplier transactions
 
SQL> Select * from RCV_TRANSACTIONS where PO_HEADER_ID=40924
It returns 4 records
New record has
TRANSACTION_TYPE=RETURN TO VENDOR
TRANSACTION_ID=536017
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=302978
QUANTITY=1
SQL> Select * from MTL_SERIAL_NUMBERS where INVENTORY_ITEM_ID=&Item_id
and lot_number like 'S366';
The script returns 2 records
 
For SERIAL_NUMBER=SN0020
    CURRENT_STATUS=3 (Resides in stores)
    CURRENT_SUBINVENTORY_CODE=Stores
 
For SERIAL_NUMBER=SN0021
    CURRENT_STATUS=4 (serial number issued from stores)
    CURRENT_SUBINVENTORY_CODE=null
10) Navigate to Purchase Orders/Purchase Orders Summary
At shipment and distribution level, for the PO Number 8415
Quantity Received=1
Quantity Delivered=1
 
 
 

Oracle EBS标准错误信息如何追踪 （Debug）

首先，我们需要在admin职责下设置一些Profile:
----------------------------------------------
a. RCV: Debug Mode=Yes
b. FND: Debug Log Enabled=Yes
c. FND: Debug Log Level=Statement
d. FND: Debug Log Module=po%
----------------------------------------------
a. TP:INV Transaction processing mode=On-line
b. INV: Debug Trace=Yes
c. INV: debug Level=11
d. INV: Debug file (Including the complete path)=<valid directory path ----------------------------------------------
a. OM: Debug Level = 5
b. OM: Debug Log Directory = <valid directory path>
然后可以用如下SQL查看Debug信息：
select to_char(timestamp, 'DD-MON-YY HH24:MI:SS'),
message_text,
module,
log_sequence
from fnd_log_messages
WHERE trunc(timestamp) = trunc(sysdate)
AND process_id = (SELECT os_process_id
FROM fnd_concurrent_requests
WHERE request_id = &p_request_id)
AND module not like 'fnd%'
ORDER BY log_sequence;
*********************************************
例如错误：Error: derive_vendor_rcv_line_qty-098的Debug信息如下：
 
Match Option:RProfile value ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE:N
po.plsql.RCV_ROI_TRANSACTION.RCV_ROI_TRANSACTION.4731
No of Distributions:1:: Rate and Rate_date in po distributions7.468200216771979::16-JAN-08
po.plsql.RCV_ROI_TRANSACTION.RCV_ROI_TRANSACTION.4757
Exception in derive_vendor_rcv_line_qty
po.plsql.RCV_ROI_TRANSACTION.RCV_ROI_TRANSACTION.5011
由此，我们可以看到在Package：RCV_ROI_TRANSACTION中，4757行 - 5011行之间发生错误，引发Exception报错：Error: derive_vendor_rcv_line_qty-098，那么此错误就轻易被解决掉了。
 
a. RCV: Debug Mode=Yes
b. FND: Debug Log Enabled=Yes
c. FND: Debug Log Level=Statement
d. FND: Debug Log Module=po%  -->此处看你debug什么模块的
 
记得设置在User级别，如果是site级别的，会很影响系统的Performance
 
然后可以用如下SQL查看Debug信息：
SELECT TO_CHAR(TIMESTAMP, 'DD-MON-YY HH24:MI:SS'),
       MESSAGE_TEXT,
       MODULE,
       LOG_SEQUENCE
  FROM FND_LOG_MESSAGES
 WHERE TRUNC(TIMESTAMP) = TRUNC(SYSDATE)
   AND PROCESS_ID = (SELECT OS_PROCESS_ID
                       FROM FND_CONCURRENT_REQUESTS
                      WHERE REQUEST_ID = &P_REQUEST_ID)
   AND MODULE NOT LIKE 'fnd%'
 ORDER BY LOG_SEQUENCE;
 
 
 
 
 
 
 

处理接收入库中入库类型不是 INVENTORY 而是 RECEIVING 的错误

这个错误导致接收没有事物处理
处理思路是：删除掉错误的入库行，手工重新接收
1、 首先备份错误的数据
CREATE TABLE rz.rz_rcv_transactions_back0828
AS
SELECT *
  FROM RCV_TRANSACTIONS RT
 WHERE RT.TRANSACTION_TYPE = 'DELIVER'
   AND RT.DESTINATION_TYPE_CODE = 'RECEIVING';
2、删除错误的数据行
DELETE FROM RCV_TRANSACTIONS RT
 WHERE RT.TRANSACTION_TYPE = 'DELIVER'
   AND RT.DESTINATION_TYPE_CODE = 'RECEIVING';
 
3、插入到表PO.RCV_SUPPLY
INSERT INTO PO.RCV_SUPPLY
  SELECT 'RECEIVING',
         RT.TRANSACTION_ID,
         RT.LAST_UPDATED_BY,
         RT.LAST_UPDATE_DATE,
         RT.LAST_UPDATE_LOGIN,
         RT.CREATED_BY,
         RT.CREATION_DATE,
         '0',
         RT.PROGRAM_APPLICATION_ID,
         RT.PROGRAM_ID,
         RT.PROGRAM_UPDATE_DATE,
         NULL,
         NULL,
         RT.PO_HEADER_ID,
         RT.PO_RELEASE_ID,
         RT.PO_LINE_ID,
         RT.PO_LINE_LOCATION_ID,
         RT.PO_DISTRIBUTION_ID,
         RT.SHIPMENT_HEADER_ID,
         RT.SHIPMENT_LINE_ID,
         RT.TRANSACTION_ID,
         RSL.ITEM_ID,
         RSL.ITEM_REVISION,
         RT.QUANTITY,
         RT.UNIT_OF_MEASURE,
         RT.QUANTITY,
         RT.UNIT_OF_MEASURE,
         RT.TRANSACTION_DATE,
         ‘SINGLE’,
         RT.LOCATION_ID,
         NULL,
         NULL,
         RSL.TO_ORGANIZATION_ID,
         RSL.TO_SUBINVENTORY,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
    FROM RCV_TRANSACTIONS RT
   INNER JOIN RCV_SHIPMENT_LINES RSL
      ON RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
   WHERE RT.TRANSACTION_ID IN
         (SELECT RTB.PARENT_TRANSACTION_ID
            FROM RZ.RZ_RCV_TRANSACTIONS_BACK0828 RTB);
 
4、插入表MTL_SUPPLIERS
INSERT INTO MTL_SUPPLY
 SELECT 'RECEIVING',
         RT.TRANSACTION_ID,
         RT.LAST_UPDATED_BY,
         RT.LAST_UPDATE_DATE,
         RT.LAST_UPDATE_LOGIN,
         RT.CREATED_BY,
         RT.CREATION_DATE,
         NULL,
         RT.PROGRAM_APPLICATION_ID,
         RT.PROGRAM_ID,
         RT.PROGRAM_UPDATE_DATE,
         NULL,
         NULL,
         RT.PO_HEADER_ID,
         RT.PO_RELEASE_ID,
         RT.PO_LINE_ID,
         RT.PO_LINE_LOCATION_ID,
         RT.PO_DISTRIBUTION_ID,
         RT.SHIPMENT_HEADER_ID,
         RT.SHIPMENT_LINE_ID,
         RT.TRANSACTION_ID,
         RSL.ITEM_ID,
         RSL.ITEM_REVISION,
         NULL,
         RT.QUANTITY,
         RT.UNIT_OF_MEASURE,
         RT.QUANTITY,
         RT.UNIT_OF_MEASURE,
         RT.TRANSACTION_DATE,
         RT.TRANSACTION_DATE,
         RT.TRANSACTION_DATE+3,
         'INVENTORY',
         RT.LOCATION_ID,
         NULL,
         NULL,
         RSL.TO_ORGANIZATION_ID,
         RSL.TO_SUBINVENTORY,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL
    FROM RCV_TRANSACTIONS RT
   INNER JOIN RCV_SHIPMENT_LINES RSL
      ON RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
   WHERE RT.TRANSACTION_ID IN
         (SELECT RTB.PARENT_TRANSACTION_ID
            FROM RZ.RZ_RCV_TRANSACTIONS_BACK0828 RTB);
 
5、执行 /app/PROD/apps/apps_st/appl/inv/12.0.0/patch/115/sql/invinbup.sql
如果不执行这个SQL会出现RVTTH-115f错误
6、到接收事物处理重新接收。
 
 
 

错误： 无法从 RHI 中找到题头记录。必须为事务处理 RECEIVE 提供题头信息

 Receiving Transactions Fail Intermittently:RCV_ROI_HEADER_MISSING
 
When this error occurs, an orphan rcv_shipment_headers record may be created.
After the interface records are reset, the transactions process successfully.

Cause

This error occurs when the Receiving Transaction Processor does not find a PENDING record in RCV_HEADERS_INTERFACE for the GROUP_ID/HEADER_INTERFACE_ID associated with the record being processed in RCV_TRANSACTIONS_INTERFACE.
The rcv_transactions_interface records were not Committed at the same time as the rcv_headers_interface record.  The following fields also were not populated using the appropriate sequencing method:
  1. GROUP_ID:  RCV_INTERFACE_GROUPS_S.NEXTVAL and RCV_INTERFACE_GROUPS_S.CURRVAL
  2. HEADER_INTERFACE_ID: RCV_HEADERS_INTERFACE_S.NEXTVAL and RCV_HEADERS_INTERFACE_S.CURRVAL
  3. INTERFACE_TRANSACTION_ID: RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL

Solution

Populate GROUP_ID and commit after each "set" of data.
The RCV_HEADERS_INTERFACE (RHI) records should not be Committed until after all RCV_TRANSACTIONS_INTERFACE (RTI) records for same HEADER_INTERFACE_ID are inserted.
The Commit must be performed AFTER both RHI and all its associated RTI records are populated.

The RHI record should be populated using:  RCV_INTERFACE_GROUPS_S.NEXTVAL 

The RTI records associated with the parent RHI record should be populated using: RCV_INTERFACE_GROUPS_S.CURRVAL 

Note: It is OK to have multiple RHI with same GROUP_ID. The key here is that all associated RTI 
records must be Committed at same time as its corresponding RHI parent BEFORE the Receiving Transaction Processor is run.
To review a sample script that inserts multiple rcv_headers_interface and rcv_transactions_interface records, download the attached file (sample_roi_insert_script.sql)
/*The sample script below uses sequencing to insert:
1.  one group_id value
2.  two header_interface_id values
3.  six interface_transaction_id values
**three rcv_transactions_interface records for each rcv_headers_interface record*/
 
INSERT INTO RCV_HEADERS_INTERFACE
  (HEADER_INTERFACE_ID,
   GROUP_ID,
   PROCESSING_STATUS_CODE,
   RECEIPT_SOURCE_CODE,
   TRANSACTION_TYPE,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_LOGIN,
   CREATION_DATE,
   CREATED_BY,
   SHIPPED_DATE,
   VENDOR_NAME,
   EMPLOYEE_NAME,
   VALIDATION_FLAG,
   SHIP_TO_ORGANIZATION_CODE,
   EXPECTED_RECEIPT_DATE,
   AUTO_TRANSACT_CODE)
  SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL,
         RCV_INTERFACE_GROUPS_S.NEXTVAL,
         'PENDING',
         'VENDOR',
         'NEW',
         SYSDATE,
         1,
         1,
         SYSDATE,
         1,
         SYSDATE,
         'Advanced Network Devices',
         'lastname, firstname',
         'Y',
         'M1',
         SYSDATE + 3,
         'RECEIVE'
    FROM DUAL;
 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
  (INTERFACE_TRANSACTION_ID,
   HEADER_INTERFACE_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,
   QUANTITY,
   UNIT_OF_MEASURE,
   AUTO_TRANSACT_CODE,
   RECEIPT_SOURCE_CODE,
   SOURCE_DOCUMENT_CODE,
   DOCUMENT_NUM,
   DOCUMENT_LINE_NUM,
   SHIP_TO_LOCATION_CODE,
   NOTICE_UNIT_PRICE,
   VALIDATION_FLAG,
   TO_ORGANIZATION_CODE,
   ITEM_NUM)
  SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
         RCV_HEADERS_INTERFACE_S.CURRVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         SYSDATE,
         1,
         SYSDATE,
         1,
         1,
         'RECEIVE',
         SYSDATE,
         'PENDING',
         'BATCH',
         'PENDING',
         2,
         'Each',
         'DELIVER',
         'VENDOR',
         'PO',
         '11724',
         '1',
         'M1- Seattle', -- 'ship to location',
         1,
         'Y',
         'M1',
         'CM13139'
    FROM DUAL;
 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
  (INTERFACE_TRANSACTION_ID,
   HEADER_INTERFACE_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,
   QUANTITY,
   UNIT_OF_MEASURE,
   AUTO_TRANSACT_CODE,
   RECEIPT_SOURCE_CODE,
   SOURCE_DOCUMENT_CODE,
   DOCUMENT_NUM,
   DOCUMENT_LINE_NUM,
   SHIP_TO_LOCATION_CODE,
   NOTICE_UNIT_PRICE,
   VALIDATION_FLAG,
   TO_ORGANIZATION_CODE,
   ITEM_NUM)
  SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
         RCV_HEADERS_INTERFACE_S.CURRVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         SYSDATE,
         1,
         SYSDATE,
         1,
         1,
         'RECEIVE',
         SYSDATE,
         'PENDING',
         'BATCH',
         'PENDING',
         8,
         'Each',
         'DELIVER',
         'VENDOR',
         'PO',
         '13494',
         '1',
         'M1- Seattle', -- 'ship to location',
         1,
         'Y',
         'M1',
         '81956'
    FROM DUAL;
 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
  (INTERFACE_TRANSACTION_ID,
   HEADER_INTERFACE_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,
   QUANTITY,
   UNIT_OF_MEASURE,
   AUTO_TRANSACT_CODE,
   RECEIPT_SOURCE_CODE,
   SOURCE_DOCUMENT_CODE,
   DOCUMENT_NUM,
   DOCUMENT_LINE_NUM,
   SHIP_TO_LOCATION_CODE,
   NOTICE_UNIT_PRICE,
   VALIDATION_FLAG,
   TO_ORGANIZATION_CODE,
   ITEM_NUM)
  SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
         RCV_HEADERS_INTERFACE_S.CURRVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         SYSDATE,
         1,
         SYSDATE,
         1,
         1,
         'RECEIVE',
         SYSDATE,
         'PENDING',
         'BATCH',
         'PENDING',
         12,
         'Each',
         'DELIVER',
         'VENDOR',
         'PO',
         '11725',
         '1',
         'M1- Seattle', -- 'ship to location',
         1,
         'Y',
         'M1',
         'AG13339'
    FROM DUAL;
 
INSERT INTO RCV_HEADERS_INTERFACE
  (HEADER_INTERFACE_ID,
   GROUP_ID,
   PROCESSING_STATUS_CODE,
   RECEIPT_SOURCE_CODE,
   TRANSACTION_TYPE,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_LOGIN,
   CREATION_DATE,
   CREATED_BY,
   SHIPPED_DATE,
   VENDOR_NAME,
   EMPLOYEE_NAME,
   VALIDATION_FLAG,
   SHIP_TO_ORGANIZATION_CODE,
   EXPECTED_RECEIPT_DATE,
   AUTO_TRANSACT_CODE)
  SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         'PENDING',
         'VENDOR',
         'NEW',
         SYSDATE,
         1,
         1,
         SYSDATE,
         1,
         SYSDATE,
         'Advanced Network Devices',
         'lastname, firstname',
         'Y',
         'M1',
         SYSDATE + 3,
         'RECEIVE'
    FROM DUAL;
 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
  (INTERFACE_TRANSACTION_ID,
   HEADER_INTERFACE_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,
   QUANTITY,
   UNIT_OF_MEASURE,
   AUTO_TRANSACT_CODE,
   RECEIPT_SOURCE_CODE,
   SOURCE_DOCUMENT_CODE,
   DOCUMENT_NUM,
   DOCUMENT_LINE_NUM,
   SHIP_TO_LOCATION_CODE,
   NOTICE_UNIT_PRICE,
   VALIDATION_FLAG,
   TO_ORGANIZATION_CODE,
   ITEM_NUM)
  SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
         RCV_HEADERS_INTERFACE_S.CURRVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         SYSDATE,
         1,
         SYSDATE,
         1,
         1,
         'RECEIVE',
         SYSDATE,
         'PENDING',
         'BATCH',
         'PENDING',
         12,
         'Each',
         'DELIVER',
         'VENDOR',
         'PO',
         '19877',
         '1',
         'M1- Seattle', -- 'ship to location',
         1,
         'Y',
         'M1',
         'JR1002'
    FROM DUAL;
 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
  (INTERFACE_TRANSACTION_ID,
   HEADER_INTERFACE_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,
   QUANTITY,
   UNIT_OF_MEASURE,
   AUTO_TRANSACT_CODE,
   RECEIPT_SOURCE_CODE,
   SOURCE_DOCUMENT_CODE,
   DOCUMENT_NUM,
   DOCUMENT_LINE_NUM,
   SHIP_TO_LOCATION_CODE,
   NOTICE_UNIT_PRICE,
   VALIDATION_FLAG,
   TO_ORGANIZATION_CODE,
   ITEM_NUM)
  SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
         RCV_HEADERS_INTERFACE_S.CURRVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         SYSDATE,
         1,
         SYSDATE,
         1,
         1,
         'RECEIVE',
         SYSDATE,
         'PENDING',
         'BATCH',
         'PENDING',
         18,
         'Each',
         'DELIVER',
         'VENDOR',
         'PO',
         '81922',
         '1',
         'M1- Seattle', -- 'ship to location',
         1,
         'Y',
         'M1',
         'GK1822'
    FROM DUAL;
 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
  (INTERFACE_TRANSACTION_ID,
   HEADER_INTERFACE_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,
   QUANTITY,
   UNIT_OF_MEASURE,
   AUTO_TRANSACT_CODE,
   RECEIPT_SOURCE_CODE,
   SOURCE_DOCUMENT_CODE,
   DOCUMENT_NUM,
   DOCUMENT_LINE_NUM,
   SHIP_TO_LOCATION_CODE,
   NOTICE_UNIT_PRICE,
   VALIDATION_FLAG,
   TO_ORGANIZATION_CODE,
   ITEM_NUM)
  SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
         RCV_HEADERS_INTERFACE_S.CURRVAL,
         RCV_INTERFACE_GROUPS_S.CURRVAL,
         SYSDATE,
         1,
         SYSDATE,
         1,
         1,
         'RECEIVE',
         SYSDATE,
         'PENDING',
         'BATCH',
         'PENDING',
         32,
         'Each',
         'DELIVER',
         'VENDOR',
         'PO',
         '12325',
         '1',
         'M1- Seattle', -- 'ship to location',
         1,
         'Y',
         'M1',
         'BU1239'
    FROM DUAL;
COMMIT;
 
 
posted @ 2012-07-06 23:37  郭振斌  阅读(5417)  评论(0)  编辑  收藏  举报