Oracle R12采购接收流程(PR-PO-RCV-AP-Payment)

版权声明：转载请以链接形式注明出处

目录(?)[+]

1. Requisitions
2. AutoCreate
3. PO
4. ReceiptDelivery
5. Invoice
6. Create Accounting
7. Payment

1.Requisitions

路径：Purchasing > Requisitions > Requisitions

请购单的类型只有两种，一种类型是Internal Requisition，用于内部组织间的物料申请；另外一种类型是Purchase Requisition，用于产生PO，对外部供应商的采购申请。

Tables

采购申请主要涉及以下三张表PO_REQUISITION_HEADERS_ALL,PO_REQUISITION_LINES_ALL,PO_REQ_DISTRIBUTIONS_ALL.

header表和lines表通过REQUISITION_HEADER_ID关联，header <-1...*-> lines

lines表和distributions表通过REQUISITION_LINE_ID相互关联，lines <-1...*-> distributions

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description.
.
REQUISITION_HEADER_ID is the unique system-generated requisition number. REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you use to identify the requisition
in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate requisition numbers for you.
.
PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition information. PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window.
.
SEGMENT1 provides unique values for each row in the table in addition to REQUISITION_HEADER_ID.

PO_REQUISITION_LINES

PO_REQUISITION_LINES stores information about requisition lines.You need one row for each requisition line you create. Each row contains the line number, item number, item category, item description, need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.
.
LINE_LOCATION_ID identifies the purchase order shipment line on which you placed the requisition. LINE_LOCATION_ID is null if you have not placed the requisition line on a purchase order.
BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for the requisition line.
PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multisourced this requisition line.
.
PO_REQUISITION_LINES is one of three tables storing requisition information. This table corresponds to the Lines region of the Requisitions window

PO_REQ_DISTRIBUTIONS_ALL

PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution.
You need one row for each requisition distribution you create. Each row includes the Accounting Flexfield ID and requisition line quantity.
.
PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information. This table corresponds to the requisition Distributions window, accessible through the Requisitions window

Distribution里的三个Account信息所对应的PO_REQ_DISTRIBUTIONS_ALL表里的字段

Debit(DR):CODE_COMBINATION_ID,

Credit(CR):ACCRUAL_ACCOUNT_ID,

Variance:VARIANCE_ACCOUNT_ID

这三个Account信息都可以在GL_CODE_COMBINATIONS_KFV中查找到。

Queries

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE segment1 = '14337';

SELECT line.* FROM PO_REQUISITION_HEADERS_ALL header,PO_REQUISITION_LINES_ALL line
WHERE header.REQUISITION_HEADER_ID = line.REQUISITION_HEADER_ID
AND header.segment1 = '14337';

SELECT * FROM PO_REQUISITION_LINES_ALL line,PO_REQ_DISTRIBUTIONS_ALL dis
WHERE line.REQUISITION_LINE_ID = dis.REQUISITION_LINE_ID
AND line.REQUISITION_HEADER_ID IN (SELECT REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE segment1 = '14337');

2.AutoCreate

AutoCreate用于把PR转换成PO

路径：Purchasing > AutoCreate

注：如果查不到PR，看看Buyer或者Ship-To是否输入正确，可以清掉这两个字段再查

这里使用Automatic模式来生成PO，接下来会弹出窗口，输入必要的采购订单的必要信息，比如供货方，币别...

3.PO

AutoCreate之后，就生成了PO，这里还需要输入必要的Ship-To,Bill-To信息...

Shipment信息

Distribution信息

保存PO，并审批。

Tables

和PO相关的表有：PO_HEADERS_ALL,PO_LINES_ALL,PO_LINE_LOCATIONS_ALL,PO_DISTRIBUTIONS_ALL.

header表和lines表通过PO_HEADER_ID关联，header <-1...*-> lines

lines表和locations表通过PO_LINE_ID关联，lines<-1...*-> locations

locations表和distributions表通过LINE_LOCATION_ID相互关联，location<-1...*-> distributions

PO_HEADERS_ALL

PO_HEADERS_ALL contains header information for your purchasing documents.You need one row for each document you create. There are six types of documents that use PO_HEADERS_ALL:
RFQs,

Quotations,

Standard purchase orders,

Planned purchase orders,

Blanket purchase orders,

Contracts
.
Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information, and the status of the document. .
Oracle Purchasing uses this information to record information that is related to a complete document.
.
PO_HEADER_ID is the unique system-generated primary key and is invisible to the user. SEGMENT1 is the system-assigned number you use to identify the document in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate document numbers for you.SEGMENT1 is not unique for the entire table. Different document types can share the same numbers. You can uniquely identify a row in PO_HEADERS_ALL using ORG_ID, SEGMENT1, and TYPE_LOOKUP_CODE, or using PO_HEADER_ID.
.
If APPROVED_FLAG is 'Y', the purchase order is approved. If your document type is a blanket purchase order, contract purchase order, RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE to store the valid date range for the document. Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders.
.
If you autocreate a quotation from an RFQ using the Copy Document window, Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID. Oracle Purchasing also uses FROM_TYPE_LOOKUP_CODE to indicate that you copied the quotation from an RFQ.
.
Oracle Purchasing does not use SUMMARY_FLAG and ENABLED_FLAG. Because future versions of Oracle Purchasing will use them, SUMMARY_FLAG and ENABLED_FLAG should always be 'N' and 'Y' respectively.
.
You enter document header information in the Header region of the Purchase Orders, RFQs, and Quotations windows.

PO_LINES_ALL

PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. There are five document types that use lines:
RFQs
Quotations
Standard purchase orders
Blanket purchase orders
Planned purchase orders
Each row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered for the line. Oracle Purchasing uses this information
to record and update item and price information for purchase orders,quotations, and RFQs.
.
PO_LINE_ID is the unique system-generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order. Oracle Purchasing uses CONTRACT_ID to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned purchase order lines.
.
The QUANTITY field stores the total quantity of all purchase order shipment lines (found in PO_LINE_LOCATIONS_ALL).

PO_LINE_LOCATIONS_ALL

这个表记录了Shipment的相关信息

PO_LINE_LOCATIONS_ALL contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:
RFQs .
Quotations
Standard purchase orders
Planned purchase orders
Planned purchase order releases
Blanket purchase orders
Blanket purchase order releases
.
Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and price
break information for blanket purchase orders, quotations and RFQs.
.
PO_RELEASE_ID applies only to blanket purchase order release shipments. PO_RELEASE_ID identifies the release on which you placed this shipment.
.
SOURCE_SHIPMENT_ID applies only to planned purchase order release shipments. It identifies the planned purchase order shipment you chose to release from.
.
PRICE_OVERRIDE always equals the purchase order line price for standard purchase order shipments. For blanket and planned purchase orders, PRICE_OVERRIDE depends on the values of the
ALLOW_PRICE_OVERRIDE_FLAG and NOT_TO_EXCEED_PRICE in the corresponding row in PO_LINES_ALL:
.
If ALLOW_PRICE_OVERRIDE_FLAG is 'N', then PRICE_OVERRIDE equals UNIT_PRICE in PO_LINES_ALL.

.
If ALLOW_PRICE_OVERRIDE_FLAG is 'Y', the PRICE_OVERRIDE can take any value that is smaller than NOT_TO_EXCEED_PRICE in PO_LINES_ALL.
.
The QUANTITY field corresponds to the total quantity ordered on all purchase order distribution lines (found in PO_DISTRIBUTIONS_ALL).
.
Oracle Purchasing automatically updates QUANTITY_RECEIVED, QUANTITY_ACCEPTED, and QUANTITY_REJECTED when you receive, return, or inspect goods or services. Oracle Payables automatically updates QUANTITY_BILLED when you match an invoice with a purchase order shipment. Oracle Purchasing automatically updates QUANTITY_CANCELLED when you cancel a purchase order shipment.
.
Oracle Purchasing sets APPROVED_FLAG to 'Y' when you approve the corresponding purchase order if there are no problems associated with the shipment and its related distributions.
.
Oracle Purchasing sets ENCUMBERED_FLAG to 'Y' and enters the ENCUMBERED_DATE when you approve a purchase order if you use encumbrance.

PO_DISTRIBUTIONS_ALL

这个表记录了distributions的相关信息

PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment. There are four types of documents using distributions in Oracle Purchasing:
Standard Purchase Orders
Planned Purchase Orders
Planned Purchase Order Releases
Blanket Purchase Order Releases

Each row includes the destination type, requestor ID, quantity ordered and deliver-to location for the distribution.
.
Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases.
.
PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order and release information.
.
Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:
.
If you autocreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution you copy on the purchase order..

If you use a foreign currency on your purchase order,Oracle Purchasing stores currency conversion information in RATE and RATE_DATE.
.
If you use encumbrance, GL_ENCUMBERED_DATE and GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create journal entries in Oracle General Ledger. .

If you do not autocreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table.
.
If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release.
.
If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release.
.
If you cancel the distribution, Oracle Purchasing automatically updates QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also enters UNENCUMBERED_AMOUNT if you use encumbrance..

You enter distribution information in the Distributions window, accessible through the Purchase Orders and Releases windows.

Distribution里的三个Account信息所对应的PO_DISTRIBUTIONS_ALL表里的字段

Debit(DR):CODE_COMBINATION_ID,

Credit(CR):ACCRUAL_ACCOUNT_ID,

Variance:VARIANCE_ACCOUNT_ID

这三个Account信息都可以在GL_CODE_COMBINATIONS_KFV中查找到。

Queries

SELECT * FROM PO_HEADERS_ALL WHERE segment1 = '6151';

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE segment1 = '6151');

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE segment1 = '6151');

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE segment1 = '6151');

PR-PO整体TABLE关系图

PO-PR通过SQL如何互查

[sql] view plain copy print ?

1. --Query PO information base on PR number 
2. SELECT POH.SEGMENT1,PLA.* 
3. FROM   PO_HEADERS_ALL POH, 
4.        PO_LINES_ALL PLA, 
5.        PO_LINE_LOCATIONS_ALL PLL 
6. WHERE  POH.PO_HEADER_ID = PLL.PO_HEADER_ID 
7. AND    POH.PO_HEADER_ID = PLA.PO_HEADER_ID 
8. AND    PLL.LINE_LOCATION_ID IN 
9.                                 (SELECT DISTINCT LINE_LOCATION_ID 
10.                                 FROM             PO_REQUISITION_LINES_ALL 
11.                                 WHERE            REQUISITION_HEADER_ID = 
12.                                                  (SELECT DISTINCT REQUISITION_HEADER_ID 
13.                                                  FROM             PO_REQUISITION_HEADERS_ALL 
14.                                                  WHERE            SEGMENT1 = '&PR_NUMBER' 
15.                                                  ) 
16.                                 ); 
17.                                  
18. --Query PR information base on PO number/PO_HEADER_ID                               
19. SELECT * 
20. FROM   PO_REQUISITION_HEADERS_ALL PRHA, 
21.        PO_REQUISITION_LINES_ALL PRLA  , 
22.        PO_REQ_DISTRIBUTIONS_ALL PRDA 
23. WHERE  PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID 
24. AND    PRDA.REQUISITION_LINE_ID   = PRLA.REQUISITION_LINE_ID 
25. AND    PRDA.DISTRIBUTION_ID IN 
26.        ( SELECT PDA.REQ_DISTRIBUTION_ID 
27.        FROM    PO_HEADERS_ALL PHA, 
28.                PO_DISTRIBUTIONS_ALL PDA 
29.        WHERE   PHA.PO_HEADER_ID = PDA.PO_HEADER_ID 
30.        AND     PHA.SEGMENT1     = '887' 
31.        ) --最好用PO_HEADER_ID来查PR，因为用PO Number来查，可能会出现多个结果                        

--Query PO information base on PR number
SELECT POH.SEGMENT1,PLA.*
FROM   PO_HEADERS_ALL POH,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLL
WHERE  POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND    POH.PO_HEADER_ID = PLA.PO_HEADER_ID
AND    PLL.LINE_LOCATION_ID IN
                                (SELECT DISTINCT LINE_LOCATION_ID
                                FROM             PO_REQUISITION_LINES_ALL
                                WHERE            REQUISITION_HEADER_ID =
                                                 (SELECT DISTINCT REQUISITION_HEADER_ID
                                                 FROM             PO_REQUISITION_HEADERS_ALL
                                                 WHERE            SEGMENT1 = '&PR_NUMBER'
                                                 )
                                );
                                
 --Query PR information base on PO number/PO_HEADER_ID                              
SELECT *
FROM   PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLA  ,
       PO_REQ_DISTRIBUTIONS_ALL PRDA
WHERE  PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND    PRDA.REQUISITION_LINE_ID   = PRLA.REQUISITION_LINE_ID
AND    PRDA.DISTRIBUTION_ID IN
       ( SELECT PDA.REQ_DISTRIBUTION_ID
       FROM    PO_HEADERS_ALL PHA,
               PO_DISTRIBUTIONS_ALL PDA
       WHERE   PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
       AND     PHA.SEGMENT1     = '887'
       ) --最好用PO_HEADER_ID来查PR，因为用PO Number来查，可能会出现多个结果                       

PO Approve之后所涉及到的其他表

MTL_SUPPLY

一条供货信息在MTL_SUPPLY中就生成了，SUPPLY_TYPE_CODE=PO，SUPPLY_SOURCE_ID=PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID,此外表里还记录了PO单据相关的信息。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.(SUPPLY_TYPE_CODE=REQ)

One record of PO type will be created per PO distribution(SUPPLY_TYPE_CODE=PO) when a PO is approved and one record per shipment line will be created when a shipment is created.

RCV_SUPPLY，rcv_transactions_interface，rcv_headers_interface这个时候还没有数据

4.Receipt&Delivery

供应商会发货到我门指定组织，接下来我们要做接收和投递入库

Receipt

Receipt的后台逻辑，实际上是插入一条记录到rcv_transactions_interface里，然后调用Receiving Transaction Processor来处理RTI里的数据，如果报错，错误信息会写po_interface_errors表中。

Receipt成功运行之后，可以看到RTI的数据被删除，RCV的相关数据也会生成在rcv_shipment_headers,rcv_shipment_lines,rcv_transactions,rcv_supply表中，rcv_shipment_headers记录了Receipt Header的信息，rcv_shipment_lines记录了Receipt的Line的信息，RT为接收的历史表，RCV_SUPPLY标识了还有多少待接收数量（这个时候SUPPLY_TYPE_CODE为RECEIVING状态）。

RSL的SHIPMENT_LINE_STATUS_CODE为：FULLY RECEIVED

再看MTL_SUPPLY,SUPPLY_TYPE_CODE也由PO更改为了RECEIVING。

RCV_SUPPLY和MTL_SUPPLY的作用类似，都是提供SUPPLY的信息，由于历史原因，才让两个表共存，RCV_SUPPLY的信息更加详细。

Delivery

路径：Inventory > Transactions > Receiving > Receiving Transaction

和Receipt一样，Delivery后台逻辑也会插入一条记录到rcv_transactions_interface里（TRANSACTION_TYPE=DELIVER，PROCESSING_STATUS_CODE:PENDING），然后调用Receiving Transaction Processor来处理RTI里的数据，如果报错，错误信息会写po_interface_errors表中。

RT有两条Transaction，一条是TRANSACTION_TYPE为RECEIVE,另外一条TRANSACTION_TYPE为DELIVER。

RSL的SHIPMENT_LINE_STATUS_CODE仍为：FULLY RECEIVED。

完全Delivery之后，RCV_SUPPLY和MTL_SUPPLY就会被删除。

另外如果查看Material Transaction，会发现有一条PO Receipt的Transaction生成了。

System Flow

Pre-requisites:At least one approved shipment which has the ship-to-organization same as the active organization. This means that a PO supply exists in mtl_supply.

When you create a standard receipt against a PO:

1.     Records are inserted into RCV_TRANSACTIONS_INTERFACE withprocessing_status_code andtransaction_status_code as‘PENDING’ and transaction_type of‘RECEIVE’.

2.     Shipment header is created in RCV_SHIPMENT_HEADERS.

3.     Receiving Transaction Processor is called.

The ReceivingTransaction Processor does the following:

1.     Unlocks all previous transactions from a failedoperation so that they can be processed now.

2.     Sets the processing_status_code inrcv_transactions_interface to ‘RUNNING’ (only in case of BATCH and IMMEDIATE processing modes).

3.     Calls the actual processing function - rvtptcontrol.

In rvtptcontrolwe initialize the transaction information structure, do the validations and perform the actual actions to complete the transactions. Here according to the transaction type we call different functions to do the actual processing.

1.     Creates Shipment Line in rcv_shipment_lines. Create transaction history i.e. create records inrcv_transactions. In case the item is under lot or serial control then create records inrcv_lot_transactions andrcv_serial_transactions.

2.     Maintain supply information. This means updating or deleting the PO supply, create RECEIVING/DELIVER supply inmtl_supply. Creatercv_supply.In case the item is under serial control then maintainrcv_serials_supply.

3.     Update the RECEIVED/DELIVERED quantities in PO Distributions.

4.     Close the PO for receiving if applicable.

5.     Create the Inventory Interface record i.e. insert a record into mtl_material_transactions_temp and call the inventory function inltpu() which completes the delivery of the item into Inventory. (rvtii.lpc)

6.     Receipt Accruals.

Flow for Receiving Transaction Processor

For Receipt Transactions

1)  CREATESHIPMENT HEADER       ==> rvtshheader

2)CREATE SHIPMENT LINE       ==> rvtshline

3)RECEIVE TRX             ==> rvtthrec

4)DELIVER TRX             ==> rvtthdel

5)MAINTAIN SHIPMENT RECEIPT QTY ==> rvtuqsreceive

6)MAINTAIN PO RECEIPT QTY       ==>rvtuqpreceive

7)MAINTAIN REQ RECEIPT QTY      ==>rvtuqrreceive

8)MAINTAIN PO DELIVERY QTY      ==>rvtuqpdelivered

9)MAINTAIN REQ DELIVERY QTY     ==>rvtuqrdelivered

10)CLOSE PO FOR RECEIVING       ==>rvtclose_for_receiving

11)INV INTERFACE TRX         ==> rvtiinv_trx

12)OUTSIDE OPS DELIVERY          ==> rvtooperation

13)ACCRUE RECEIPT             ==>rvtacar_accrue_receipt

14)PRINT DELIVERY TICKET     ==>rvtpdelivery_ticket

15)PRINT CHARGE NOTICE           ==> rvtpdcharge_notice

For Delivery Transactions

1) DELIVER TRX                ==> rvtthdel

2) MAINTAIN REQ DELIVERY QTY        ==>rvtuqrdelivered

3) MAINTAIN PO DELIVERY QTY     ==>rvtuqpdelivered

4) CLOSE PO FOR RECEIVING       ==>rvtclose_for_receiving

5) INV INTERFACE TRX         ==> rvtiinv_trx

6) OUTSIDE OPS DELIVERY          ==> rvtooperation

7) ACCRUE RECEIPT             ==>rvtacar_accrue_receipt

8) PRINT CHARGE NOTICE           ==> rvtpdcharge_notice

rcv_shipment_lines

RCV_TRANSACTIONS

5.Invoice

路径：Account Payable > Invoices > Entry > Invoices

Match

Find

Match

Actions > Validate

验证之后，Invoice的状态应该变为：Validated

可以通过下边SQL查看发票信息

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =173317));

5.Create Accounting

Invoice > Action > Create Accounting

6.Payment

发票流程走完后就是付款了

路径：Account Payable > Payment