Showing posts with label PO. Show all posts

AP : Reconciling A/P Accrual Accounts Balance

At any given time, the balance of the A/P accrual accounts can account for the following transactions:

Uninvoiced Receipts
Over-invoiced Receipts
Invoice Price Variance (for transactions created using Release 9)
Errors (Invoices or inventory transactions charged to this Account by mistake)

You need to analyze the balance of the A/P accrual accounts, distinguish accrued receipts from invoice price variances, and identify errors.

AP: Master Query for PO , Invoice, Payment Details

SELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag,
'Y', 'Approved'
)
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid, h.check_id, i.check_number,
h.invoice_payment_id,
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAME

Which table stores the Value of Receipt Routing..

If you want to know from Backend if the Receipt Routing is "Standard Receipt",'Inspection Required" or "Direct Delivery", then you should look for Table : po_line_locations_all and Column : RECEIVING_ROUTING_ID

values for the column,

0 - None
1 - Standard Receipt
2 - Inspection Required
3 - Direct Delivery

PO : Different Purchasing modes in Receiving?

There are three modes:

Online: Receipts are processed online. If there are any errors, they are shown on the FORM itself, and don’t let you IGNORE and PROCEED.

This is the most commonly used processing mode.
When the user saves a transaction, no other application activity can be performed until the process completes.
Either the number of records saved will be indicated for a successful transaction or an error message will be displayed.
Uses Receiving Transaction Manager (executable RCVOLTM, located in $PO_TOP/bin).

Immediate: Receipts are processed immediately, but no errors are shown. Errors are recorded in REC_TRANSACTION_INTERFACE table.

When the user saves a transaction, the Receiving Transaction Processor concurrent program will automatically start and process the data in the RCV_TRANSACTIONS_INTERFACE table that was created for the Receipt.
Control of the form is returned to the User who may then perform other application activity.
Transaction errors that occur are written to the PO_INTERFACE_ERRORS table and can be viewed from the Transaction Status Summary.
Additional information may be available in the log file for the Receiving Transaction Processor concurrent request (View/Requests).
Uses Receiving Transaction Processor (executable RVCTP, located in $PO_TOP/bin).

Batch: Receipts are processed in batch, but no errors are shown. Errors are recorded in REC_TRANSACTION_INTERFACE table.

In Batch processing mode, the receiving forms insert transaction information into the receiving interface tables.
These transactions remain in the interface table until you run the Receiving Transaction Processor.
The receiving forms take into account all pending transactions, but Purchasing does not update the transaction history, source documents, and supply information until the transactions are processed.

PO :Receive Complex Purchase Orders PO?

Complex PO is used for services procurement.

For example in construction business , the builder receives payment at completion of different deliverables - Foundation , Flooring , Painting and etc...

Each of these deliverables is a milestone.
Each milestone can be further sub divided if required.

So you can create Complex PO with one service line - Construction and 3 pay items or 3 service lines and each line with one pay item.

Once the builder completes Foundation , he will create Work Confirmation for completing the Foundation and receive payment. ( This work confirmation is the receiving process)

If you thinking , On what basis this will generate a receipt i.e ( How system knows that receiving is completed unless until manually run some process or complete the receiving process)

Work confirmation can be generated to the extent of work completed and this is amount based. So this cycle will complete once the ordered amount is reached.

Once the builder completes Flooring, he will create Work Confirmation for Flooring and receive payment.

Like this after completing each stage in the project the supplier will confirm the completion and receive payments.

You can relate this Milestone based billing or Progressive billing.

Okay , Lets have a scenario popup with Complex PO:

When trying to retrieve the complex PO in the core applications receiving form (Nav > Receipt > Receipts) the error "APP-PO-14094: No records meet your search criteria" is encountered when searching on the Complex PO number.

Take a note , complex PO's are not received via the core application forms.

Complex POs are transacted using the Buyers Work Center and are processed by Receiving Transaction Processor (which runs automatically).

The receiving forms are not used for Complex PO receipts.

When you create and approve the complex PO and then Create the Work Confirmation this step populates the complex po receipt data into the receiving interface (rcv_transactions_interface table) and then Buyers Work Center/ iSupplier calls the Receiving Transaction Processor (RVCTP) to process the receipt.

You need to check the Transaction Status Summary form (complex PO uses Immediate receiving mode) in the core application to see if the record has failed in the receiving interface (Nav > Receiving > Transaction Status Summary).

If the record is not there, then please check to see if it already has been received.

Work Confirmations can be created from iSupplier Portal, iProcurement, and the Professional Buyers Work Center in Purchasing (the most frequent place where this is done).

AP: Analyzing A/P Accrual Account Balance

If you are Analyzing the A/P Accrual Account Balance , then you need to monitor potential problems with purchasing and receiving activities that can affect the accuracy of your A/P accrual accounts.

You can use the Accrual Reconciliation Report to identify the following problems in receiving, purchasing, inventory, work in process, or accounts payable transactions:

Quantities differ between receipts and invoices
Incorrect purchase order or invoice unit prices (previous releases only)
Discrepancies in supplier billing
Invoice matched to wrong purchase order or wrong purchase order line
Received against the wrong purchase order or order line
Miscellaneous inventory or work in process transactions that do not belong to the accrual accounts
Payables entries for sales tax and freight that do not belong to the accrual accounts

Using the Accrual Reconciliation Report

You should use the Accrual Reconciliation Report to analyze the balance of the Accounts Payable (A/P) accrual accounts.

To submit this report, you must have Purchasing and Payables installed.

You can accrue both expense and inventory purchases as you receive them. When this happens, you temporarily record an accounts payable liability to your Expense or Inventory A/P accrual accounts.

When Payables matches and approves the invoice, Payables clears the A/P accrual accounts and records the liability from the supplier site. See: Accrual Reconciliation Report.

Typically, you run this report at month end. After you have entered your receipt transactions and matched your invoices, you can run the Accrual Reconciliation Report for any transaction date range and identify any differences between your PO Receipts and A/P Invoices.

This report also displays any miscellaneous transactions recorded in error to your accrual accounts. These miscellaneous transactions or transactions unrelated to purchase order receipts may be from Payables, Inventory, or Work in Process (depending on your installation).

After you have researched the reported accrual balances, you can use the Accrual Write-Off form to indicate which entries you wish to remove and write off from this report.

And, after you have written off these entries, you can use the Accrual Write-Off Report as supporting detail for your manual journal entry.

AP : Resolving Quantity Differences in Accrual Reconciliation Report

For resolving Quantity Differences issue , the best way is to use Accrual Reconciliation Report.

The Accrual Reconciliation Report lets you easily identify quantity differences (i.e., when the quantity received for a purchase order shipment is smaller than the quantity invoiced).

Such differences leave residual balances that never clear from the A/P accrual accounts. You should investigate the cause of these differences and take corrective actions before closing your period.

Common causes of quantity differences include late inventory receipts, incorrect receipt quantities, and supplier overbilling.

To correct late receipts, ensure that receivers enter all receipts into inventory. To correct receipt quantities, enter receipt corrections.

To correct overbilling errors, follow your standard procedure for supplier debit memos to clear the difference.

PO : Purchasing Standard Reports

1.   Active Employee Listing
2.   New Vendor Site List
3.   Payment Terms Listing
4.   Vendor Audit Report
5.   Vendors Report
6.   Accrual Reconciliation Report
7.   Accrual Write-Off Report
8.   Backordered Internal Requisition Report
9.   Blanket and Planned PO Status Report
10.   Buyer Listing
11.   Buyer Requisition Action Required Report
12.   Cancelled Purchase Order Report
13.   Cancelled Requisition Report
14.   Contract Status Report
15.   Encumbrance Details Report
16.   Expected Receipts Report
17.   Financials/Purchasing Options Listing
18.   Internal Requisition Status Report
19.   Internal Requisition/Deliveries Discrepancy Report
20.   Invoice Price Variance Report
21.   Item Detail Listing
22.   Item Summary Listing
23.   Location Listing
24.   Matching Holds by Buyer Report
25.   New Vendor Letter
26.   Open Purchase Orders by Buyer
27.   Open Purchase Orders by Cost Center Report
28.   Overdue Vendor Shipments Report
29.   Overshipments Report
30.   Printed Change Orders Report (Landscape)
31.   Printed Change Orders Report (Portrait)
32.   Printed Purchase Order Report (Landscape)
33.   Printed Purchase Order Report (Portrait)
34.   Printed Requisitions Report
35.   Printed RFQ Report (Landscape)
36.   Printed RFQ Report (Portrait)
37.   Purchase Agreement Audit Report
38.   Purchase Order Commitment by Period Report
39.   Purchase Order Detail Report
40.   Purchase Order Distribution Detail Report
41.   Purchase Order and Releases Detail Report
42.   Purchase Price Variance Report
43.   Purchase Requisition Status Report
44.   Purchase Summary Report by Category
45.   Purchase Activity Register
46.   Quality Code Listing
47.   Quotation Action Required Report
48.   RFQ Action Required Report
49.   Receipt Adjustments Report
50.   Receipt Traveler
51.   Receiving Account Distribution Report
52.   Receiving Exceptions Report
53.   Receiving Transactions Register
54.   Receiving Value Report by Destination Account
55.   ReqExpress Templates Listing
56.   Requisition Activity Register
57.   Requisition Distribution Detail Report
58.   Requisition Import Exceptions Report
59.   Requisitions on Cancelled Sales Order Report
60.   Savings Analysis Report (By Buyer)
61.   Savings Analysis Report (By Category)
62.   Standard Notes Listing
63.   Substitute Receipts Report
64.   Tax Code Listing
65.   Uninvoiced Receipts Report
66.   Unit of Measure Class Listing
67.   Unit of Measure Listing
68.   Unordered Receipts Report
69.   Vendor Affiliated Structure Listing
70.   Vendor Price Performance Analysis Report
71.   Vendor Purchase Summary Report
72.   Vendor Quality Performance Analysis Report
73.   Vendor Service Performance Analysis Report
74.   Vendor Volume Analysis Report
75.   Vendors on Hold Report

PO :Types of Quotations and RFQs

There are three types of quotations and RFQs that come with Purchasing by default:

Catalog: Used for high-volume items or items for which your supplier sends you information regularly. A Catalog quotation or RFQ also includes price breaks at different quantity levels.
Standard: Used for items you'll need only once or not very often, but not necessarily for a specific, fixed quantity, location, and date. For example, you could use a Catalog quotation or RFQ for office supplies, but use a Standard quotation or RFQ for a special type of pen you don't order very often. A Standard quotation or RFQ also includes price breaks at different quantity levels.
Bid: Used for a specific, fixed quantity, location, and date. For example, a Bid would be used for a large or expensive piece of equipment that you've never ordered before, or for an item that incurs transportation or other special costs. You cannot specify price breaks for a Bid quotation or RFQ.

For all three types, you can define effectivity dates at the header level.

For Catalog and Standard quotations, you can also specify effectivity dates for individual price breaks. (For a Bid, you cannot specify effectivity dates at the shipment level.)

Using Quotation Information for a Purchase Order

When you create a purchase order (manually or from requisitions), you can use the Supplier Item Catalog window to retrieve quotation information. (The Supplier Item Catalog window can include quotations sent to you by your supplier through the Purchasing Documents Open Interface. Purchasing provides all your approved quotation shipment information for a specific item or manufacturing category. You can copy this quotation shipment to an existing blanket purchase agreement or standard purchase order when you add this item or purchasing category to a purchase order line. You can sort this quotation information according to your needs, using criteria such as price or quantity. You can easily evaluate the source that is best for an item.

After you select the quotation shipment you want to use, Purchasing copies the item unit price, quantity, unit of measure, supplier product number, inspection required status, receipt required status, quotation number, quotation type, and supplier quotation number on your purchase order.

Purchasing automatically warns you when the terms and conditions of the quotation are different from the terms and conditions of your purchase order. The original purchase order terms and conditions remain unchanged.

PO :Purchase Price Variance

The variance that you record at the time you receive an item in inventory or supplier services into work in process.

This variance is the difference between the standard unit cost for the item or service and the purchase unit price multiplied by the quantity received.

You record purchase price variances in a purchase price variance account for your organization. Since standard cost is a planned cost, you may incur variances between the standard cost and the purchase order price.

AP : 2 WAY MATCHING QUERY

Two–Way: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'

PO :Supplier Master

select
pv.VENDOR_NAME,
pv.segment1 Supplier_number,
pvs.VENDOR_SITE_CODE site_name,
pv.match_option vendor_match_option,
pvs.match_option site_match_option,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY ,
pv.START_DATE_ACTIVE,
pv.end_DATE_ACTIVE
from
po_vendors pv,
po_vendor_sites_all pvs
where pv.vendor_id = pvs.vendor_id
and pvs.org_id =<org_id>

PO : Understanding Receipt Routing

If You want to track items from the moment that they leave a supplier's warehouse until they arrive in stock. Depending on the operation, several stops might exist between the two points, such as the dock, the staging area, inspection, and so forth.

You NEED to use receipt routing to track and move items through a series of operations that make up a receipt route.

You must define the operations that make up each receipt route. You must also determine the logistical and accounting updates that the system performs as you process items through a receipt route. After you create a receipt route, you can assign it to an item based on the supplier who provides the item.

Receipt Routing is of three types:

Direct
Standard
Inspection

In Direct once the goods arrive at the destination, we directly move them to a specific Sub-Inv

In Standard once the goods are at the destination, we receive it at the receiving point first and then move them to the Sub-Inv.

In Inspection once the goods are at the destination, we receive it at the receiving point and then we perform inspection and accordingly we either accept it or reject them.

AP : 3 WAY MATCHING QUERY

Three–Way: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid

Here are the query for 3 way Matching

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'

AP : 4 WAY MATCHING QUERY

Four–Way: Purchase order, receipt, accepted, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Here is query for 4 way matching .

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'

EBS Queryboard