Procurement Interface Management

Frequently used tables for purchase receipt

RCV_TRANSACTION_INTERFACE RCV_LOTS_INTERFACE PO_INTERFACE_ERRORS MTL_TRANSACTION_LOTS_TEMP MTL_TRANSACTION_LOTS_INTERFACE MTL_SUPPLY role table 1. MTL_SUPPLY changes after the purchase order is created and approved. when the purchase order is created, MTL_SUPPLY is empty. after approval, a piece of data is generated in MTL_SUPPLY, SUPPLY_TYPE_CODE = REQc. after the requisition is approved, the requisition header and the requisition row are stored in MS. REQ_HEADER_ID, MS. REQ_LINE_ID, in MS. SUPPLY_TYPE_CODE = REQ 2. MTL_SUPPLY changes after PO creation and approval. when the requisition is converted to po, MTL_SUPPLY is blank B. after approval, the original SUPPLY_TYPE_CODE = REQ in MTL_SUPPLY is changed to SUPPLY_TYPE_CODE = POc. generally, when a purchase order is automatically created as a purchase order, its SUPPLY_TYPE_CODE = REQ is changed to SUPPLY_TYPE_CODE = POd. when the approved po is canceled, SUPPLY_TYPE_CODE = PO in MTL_SUPPLY is changed to SUPPLY_TYPE_CODE = REQe. -- if the application for automatic purchase order opening by the requisition is called, if the application is not approved. PO_HEADER_ID, MS. PO_LINE_ID, -- MS. PO_RELEASE_ID, MS. PO_LINE_LOCATION_ID, MS. PO_DISTRIBUTION_ID, null, if approved -- MS. REQ_HEADER_ID, MS. REQ_LINE_ID is cleared, MS. SUPPLY_TYPE_CODE = PO. For a po ticket that modifies the quantity or adds a new purchase order line -- but is not approved, the data of the original approved purchase order line remains unchanged, however, the newly added row is not entered into this table. When the approved po is canceled, SUPPLY_TYPE_CODE = PO in MTL_SUPPLY is changed to SUPPLY_TYPE_CODE = REQ, -- MS. REQ_HEADER_ID, MS. REQ_LINE_ID is filled in with the header and row value of the original requisition. At the same time, 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, cleared. 3. MTL_SUPPLY changes after the PO is received.. when the po is fully received, the original SUPPLY_TYPE_CODE = PO in MTL_SUPPLY is changed to SUPPLY_TYPE_CODE = processing B. -- after RECEIVING the purchase order, SUPPLY_TYPE_CODE = PO in MTL_SUPPLY is changed to SUPPLY_TYPE_CODE = signing ing, -- at the same time, MS. SHIPMENT_HEADER_ID, MS. SHIPMENT_LINE_ID, MS. RCV_TRANSACTION_ID: the header information and row information of the shipment, and -- transaction_id4 of rcv_transaction. When a certain row of the PO is received, MTL_SUPPLY changes. after receiving a certain row of the PO, the original SUPPLY_TYPE_CODE = PO in the receiving row is changed to SUPPLY_TYPE_CODE = inging5. What is the MTL_SUPPLY change after the PO test?. after the PO test, the original SUPPLY_TYPE_CODE = processing ing in the row will remain unchanged. 6. What changes does MTL_SUPPLY change after the PO is loaded into the database?. after the PO is partially imported into the database, the row records in MTL_SUPPLY are deleted, and all the rows in the PO are deleted. The table involved is deleted-the approved requisition SELECT prh. requisition_header_id, prolactin. requisition_line_id, prh. segment1 from PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRLwhere prh. requisition_header_id = prolactin. requisition_header_id and prh. requisition_header_id = 662 and prh. authorization_status = 'apache' -- SELECT ph of the APPROVED purchase order. po_header_id, pl. PO_LINE_ID, ph. segment1, ph. * from Po_Lines_all pl, Po_Headers_All phwhere pl. PO_HEADER_ID = ph. po_header_id/* and ph. po_header_id = 41526 */and ph. authorization_status = 'apache' and NVL (ph. cancel_flag, 'n') <> 'y' and ph. creation_date> = trunc (sysdate) -- receives select * from RCV_SHIPMENT_HEADERS rsh, rcv_shipment_lines rslwhere rsh. shipment_header_id = rsl. shipment_header_idand rsh. creation_date> = trunc (sysdate) and rsh. receipt_num = '000000' select * from rcv_transactions rtwhere rt. the explanation on transaction_id = 870339trm is as follows: 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 Requisitions2) Approved Purchase orders3) Shipments from suppliers4) intransit shipments from another organization Types 3 and 4 cocould be distinguished by the presence of data in theINTRANSIT_OWNING_ORGANIZATION_ID column, which identifies theownership of the items in intransit. if this column is null then itmeans that the shipment supply is from a Vendor .. this information is used by the available to promise routine to derivethe ATP information as appropiate. quantities of items in intransitare also kept track of in the table .. records in MTL_SUPPLY are created every time you approve a requisitionor a PO or create an intransit shipment. one record of REQ type willbe created for one requisition line when the requisition is approved. one record of PO type will be created per PO distribution when a PO isapproved and one record per shipment line will be created when ashipment is created .. records in MTL_SUPPLY will be recreated whenever there aretransactions such as return to processing ing, return to vendor orcancellation of purchase orders .. records in MTL_SUPPLY are deleted every time you change the documentstatus to not approved. for example, a PO wowould require approval ifyou change the line and shipment quantity. if such a PO is re-approved then the PO supply will be recreated forthe new quantity .. the supply type code of a REQ supply record is changed to PO everytime a requisition is autocreated. similarly the supply type code ischanged from PO to processing ing when a PO is fully received. if a PO ispartially encoded ed then a supply with the supply type code ofRECEIVING will be created for the quantity encoded ed. when the receiptis delivered the processing ing supply is deleted. SHIPMENT supply works inthe 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: processing mode this option specifies the processing mode used after saving the work done to receive transaction processing: Online-directly call the "receive transaction processing program ". Instant-transfers the transaction processing to the interface table and calls the "receive transaction processing program" for the transaction processing group entered after the previous SAVE operation ". Batch-send transaction processing to the interface table ("receive transaction processing program" will select these transactions from the interface table at the next runtime) Update interface table data update interface table data so that error data can be resubmitted

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 rrps. HEADER_INTERFACE_ID FROM RCV_TRANSACTIONS_INTERFACE RRTI); 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

Receiving warehouse receiving example

CREATE OR REPLACE PACKAGE BODYXXUTS_O2C_PO_RECEIPT_PKGASPROCEDURE MAIN(ERRBUFOUT VARCHAR2, RETCODEOUT NUMBER, P_ORGANIZATION_IDIN NUMBER, P_PO_LAST_TWOIN VARCHAR2)ISV_INTERFACE_TRANSACTION_IDNUMBER; V_GROUP_IDNUMBER; V_SHIPMENT_HEADER_IDNUMBER; V_SHIPMENT_LINE_IDNUMBER; V_LINE_LOCATION_IDNUMBER; V_PO_DISTRIBUTION_IDNUMBER; V_HEADER_INTERFACE_IDNUMBER; V_DELIVER_TO_PERSON_IDNUMBER; L_REQUEST_IDNUMBER; L_REQUEST_ID2NUMBER; V_DESTINATION_TYPE_CODEVARCHAR2(100); V_EXPECTED_RECEIPT_DATEDATE; V_COUNTRY_OF_ORIGIN_CODEVARCHAR2(100); V_LOT_CONTROLNUMBER; V_USE_MTL_SERIALNUMBER; V_RCV_QTYNUMBER; V_TRANS_QTYNUMBER; V_LOCATOR_IDNUMBER; V_LOCATION_IDNUMBER; L_PHASEVARCHAR2(10); L_STATUSVARCHAR2(10); L_DEV_PHASEVARCHAR2(10); L_DEV_STATUSVARCHAR2(10); L_MESSAGEVARCHAR2(10); L_REQUEST_LAUCH1_STATUSBOOLEAN; V_INTERFACE_IDNUMBER; V_USER_IDNUMBER; V_COUNTNUMBER;CURSORCUR_PO_INFOISSELECTPHA. SEGMENT1, PHA. CURRENCY_CODE, PHA. VENDOR_ID, PHA. tags, PHA. SHIP_TO_LOCATION_ID, PDA. tags, PDA. DESTINATION_TYPE_CODE, PDA. tags, PDA. QUANTITY_ORDERED, PDA. LINE_LOCATION_ID,NVL(PDA. QUANTITY_ORDERED, 0 )-NVL(PDA. QUANTITY_DELIVERED, 0) TRANS_QTY, PLA .*FROMPO_HEADERS_ALL PHA, PO_LINES_ALL PLA, PO_DISTRIBUTIONS_ALL PDAWHEREPHA. PO_HEADER_ID = PLA. PO_HEADER_IDANDPHA. ORG_ID = PLA. ORG_IDANDPHA. ORG_ID = 104-- OU: HUTSANDPHA. AUTHORIZATION_STATUS = 'apache'ANDPHA. TYPE_LOOKUP_CODE = 'Standard'ANDPDA. PO_HEADER_ID = PLA. PO_HEADER_IDANDPDA. PO_LINE_ID = PLA. PO_LINE_IDAND EXISTS(SELECT1FROMPO_LINE_LOCATIONS_ALL PLLAWHEREPLLA. PO_HEADER_ID = PLA. PO_HEADER_IDANDPLLA. PO_LINE_ID = PLA. PO_LINE_IDAND(PLLA. QUANTITY-PLLA. QUANTITY_RECEIVED-PLLA. QUANTITY_CANCELLED)> 0ANDPLLA. SHIP_TO_ORGANIZATION_ID = P_ORGANIZATION_ID)ANDPHA. SEGMENT1LIKE'%' | P_PO_LAST_TWO;BEGINV_USER_ID: = FND_GLOBAL.USER_ID;UPDATEPO_HEADERS_ALL PHASETPHA. RATE_TYPE = 'user'WHEREPHA. CURRENCY_CODE <> 'RMB'ANDPHA. ORG_ID = 104-- OU: HUTSANDPHA. AUTHORIZATION_STATUS = 'apache'ANDPHA. TYPE_LOOKUP_CODE = 'Standard'-- And pha. wf_item_type = 'poapprv'ANDPHA. RATE_TYPEIS NULLANDPHA. SEGMENT1LIKE'%' | P_PO_LAST_TWO;COMMIT;BEGINSELECTMIL. INVENTORY_LOCATION_IDINTOV_LOCATOR_IDFROMMTL_ITEM_LOCATIONS MILWHEREMIL. SUBINVENTORY_CODE = 'na01'ANDMIL. ORGANIZATION_ID = P_ORGANIZATION_IDAND ROWNUM= 1;EXCEPTIONWHEN OTHERS THENFND_FILE.PUT_LINE (1,