Oracle Application's Blog: PO Accrual SQL Query in Oracle Fusion

PO Accrual SQL Query in Oracle Fusion

Hi friends, we are going to discuss the PO accrual sql query in oracle fusion. We will share the detail sql query which helps to extract the complete PO accrual information's in oracle fusion. PO accrual report is one of the most important reports in financials but to develop this report is too difficult. To develop the PO accrual report, we need to put lots of efforts in query building as well the reconciliation work too. First of all , let us explain you the purpose of PO accrual report and which type of data it capture in oracle fusion application.

PO Accrual SQL Query in Oracle Fusion

PO Accrual Report Logic :-

The simple cycle of P2P is , We do create the Purchase Orders , Once PO got approved we send the PO to supplier. Supplier send the Material to us. When PO material comes to our organization . we do PO receiving against the purchase order , then after receiving we need to punch the AP invoice in oracle fusion application.

In PO accrual report, It captures those PO receipts against which we have not booked the AP Invoice. In simple language those receipts against which we have not yet punched the AP invoices. PO accrual report helps to find out those receipts which is not yet billed or created invoice against that so that finance department can take the action to punch these missed invoices for these suppliers.

Detail PO Accrual SQL Query to extract the Accrual data in Oracle Fusion Application

Here below is the PO accrual sql query which helps to extract the accrual information's details in oracle fusion application. You can use this report to develop the PO accrual report in oracle fusion application.

select

cmr_po_distribution_id,

received_qty,

RECEIVED_AMOUT,

received_amout_func_currency,

INVOICED_AMT,

invoice_amt_func_curr,

invoice_variance_amt_func_curr,

invoiced_qty,

uninvoiced_qty,

UNINVOICED_AMT,

sold_to_business_unit_id,

business_unit_name,

leger_name,

func_currency,

category_code,

category_name,

inventory_item_id,

item_code,

description,

deliver_to_inventory_org_id,

destination_org,

vendor_id,

receipt_num,

receipt_date,

quantity_shipped,

quantity_received,

quantity_delivered,

quantity_returned,

quantity_accepted,

quantity_rejected,

po_unit_price,

(quantity_shipped * po_unit_price) shipped_amt,

(quantity_received * po_unit_price) received_amt,

(quantity_delivered * po_unit_price) delivered_amt,

(quantity_returned * po_unit_price) returned_amt,

(quantity_accepted * po_unit_price) accepted_amt,

(quantity_rejected * po_unit_price) rejected_amt,

vendor_name,

vendor_site_id,

party_site_name,

category_id,

po_number,

line_number,

shipment_number,

distribution_number,

CURRENCY_CODE,

LINE_TYPE,

po_price,

price_func_currency,

amt_func_curr,

UOM_CODE,

UOM_CODE1,

CURRENCY_CONVERSION_TYPE,

currency_conversion_rate,

currency_conversion_date,

po_quantity,

PO_AMOUNT,

po_amount_func_currency

from (select b.cmr_po_distribution_id,

decode(b.purchase_basis, 'GOODS', sum(nvl(b.source_doc_qty,0)), 0) as received_qty,

DECODE(B.PURCHASE_BASIS, 'GOODS', (SUM(NVL(B.SOURCE_DOC_QTY,0)) * B.PO_PRICE), SUM(NVL(B.TRANSACTION_AMT,0)) ) AS RECEIVED_AMOUT,

(decode(b.purchase_basis, 'GOODS', (sum(nvl(b.source_doc_qty,0)) * b.po_price), SUM(NVL(b.transaction_amt,0)) ))*(nvl(b.currency_conversion_rate, 1)) as received_amout_func_currency,

SUM(NVL(B.INVOICED_AMT, 0)) AS INVOICED_AMT,

SUM(NVL(B.invoice_amt_func_curr, 0)) AS invoice_amt_func_curr,

SUM(NVL(B.invoice_variance_amt_func_curr, 0)) as invoice_variance_amt_func_curr,

decode(b.purchase_basis, 'GOODS', sum(nvl(b.invoiced_qty, 0)), 0) as invoiced_qty,

decode(b.purchase_basis, 'GOODS', sum(nvl(b.source_doc_qty,0)) - sum(nvl(b.invoiced_qty, 0)), 0) as uninvoiced_qty,

DECODE(B.PURCHASE_BASIS, 'GOODS', (SUM(NVL(B.SOURCE_DOC_QTY,0)) * B.PO_PRICE - SUM(NVL(B.INVOICED_AMT, 0))),

(SUM(NVL(B.TRANSACTION_AMT,0)) - SUM(NVL(B.INVOICED_AMT, 0)))) UNINVOICED_AMT,

b.sold_to_business_unit_id,

fabu.bu_name AS business_unit_name,

lgr.name as leger_name,

lgr.currency_code AS func_currency,

ecv.category_code,

ecv.category_name,

b.inventory_item_id,

esi.item_number AS item_code,

nvl(esi.description,b.item_description) AS description,

b.deliver_to_inventory_org_id,

iod.organization_name AS destination_org,

b.vendor_id,

(select receipt_num from rcv_shipment_headers

a1,rcv_shipment_lines a2 where a1.shipment_header_id=a2.shipment_header_id and a2.po_line_id=(select po_line_id from po_headers_all pha,po_lines_all pla