LIST OF USEFUL QUERIES IN ORACLE APPLICATIONS

To find PO details:

SELECT

decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number_Release

, pol.line_num Line

, pov.vendor_name Vendor

, pol.item_revision Rev

, pol.item_description Description

, pll.shipment_num

, pod.distribution_num Distribution

, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price

, pll.promised_date Promised_Date

, pol.unit_meas_lookup_code Unit

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_ordered,

'FIXED PRICE', POD.amount_ordered,

POD.quantity_ordered) Quantity_Amount_Ordered

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_billed,

'FIXED PRICE', POD.amount_billed,

POD.quantity_billed) Quantity_Amount_Billed

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_delivered,

'FIXED PRICE', POD.amount_delivered,

POD.quantity_delivered) Qty_Amount_Delivered

, DECODE (POL.order_type_lookup_code,

'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /

DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),

'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /

DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),

(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /

DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),

'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),

(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV

, poh.po_header_id

, pol.po_line_id

, por.release_num

, poh.currency_code C_CURRENCY

, nvl(por.po_release_id,-1) release_id

FROM po_distributions pod

, mtl_system_items msi

, po_line_locations pll

, po_lines pol

, po_releases por

, po_headers poh

, po_vendors pov

, financials_system_parameters fsp

, po_line_types plt

WHERE poh.segment1=

AND poh.po_header_id = pol.po_header_id

AND pol.po_line_id = pll.po_line_id

AND pll.line_location_id = pod.line_location_id

AND pol.item_id = msi.inventory_item_id (+)

AND msi.organization_id = fsp.inventory_organization_id

AND poh.vendor_id = pov.vendor_id (+)

AND pll.po_release_id = por.po_release_id (+)

AND pol.line_type_id = plt.line_type_id

AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')

AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(poh.cancel_flag,'N') = 'N'

AND nvl(por.cancel_flag,'N') = 'N'

AND nvl(pol.cancel_flag,'N') = 'N'

AND nvl(pll.cancel_flag,'N') = 'N'

ORDER BY pll.line_location_id;

Purchase order and Requisition:

SELECT prh.segment1 req_number

,prh.authorization_status

,prl.line_num req_line_num

,prl.item_description req_item_description

,prl.unit_price req_unit_price

,prl.quantity req_quantity

,pd.req_header_reference_num

,pd.req_line_reference_num

,pl.line_num

,pl.item_description

,pl.quantity

,pl.amount

,ph.segment1 po_number

,prd.distribution_id

,pd.req_distribution_id

FROM po_requisition_headers_all prh

,po_requisition_lines_all prl

,po_req_distributions_all prd

,po_distributions_all pd

,po_line_locations_all pll

,po_lines_all pl

,po_headers_all ph

WHERE prh.requisition_header_id = prl.requisition_header_id

and prh.org_id = prl.org_id

and prl.requisition_line_id = prd.requisition_line_id

and prl.org_id = prd.org_id

and prd.distribution_id = pd.req_distribution_id(+)

and prd.org_id = pd.org_id(+)

and pd.line_location_id = pll.line_location_id(+)

and pd.org_id = pll.org_id(+)

and pll.po_line_id = pl.po_line_id(+)

and pll.org_id = pl.org_id(+)

and pl.po_header_id = ph.po_header_id(+)

and pl.org_id = ph.org_id(+);

 

To find PO returns:

execute fnd_client_info.set_org_context('org_id');

SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id

, sum ( (nvl(rct.source_doc_quantity,0)) ) Qty_returned

from rcv_transactions rct

, po_lines pol

, po_line_locations pll

where rct.transaction_type = 'RETURN TO VENDOR'

and rct.po_line_location_id = pll.line_location_id

and rct.po_line_id = pol.po_line_id

and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')

group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

union all

SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id

, sum ( (nvl(rct.amount,0)) ) Qty_returned

from rcv_transactions rct

, po_lines pol

, po_line_locations pll

where rct.transaction_type = 'RETURN TO VENDOR'

and rct.po_line_location_id = pll.line_location_id

and rct.po_line_id = pol.po_line_id

and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE')

group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id