Quries on Oracle Purchasing

CANCEL REQUISITIONS
SELECT
,prh.REQUISITION_HEADER_ID
,prh.PREPARER_ID
,prh.SEGMENT1 "REQ NUM"
,trunc(prh.CREATION_DATE)
,prh.DESCRIPTION
,prh.NOTE_TO_AUTHORIZER
FROM
,apps.Po_Requisition_headers_all prh
,apps.po_action_history pah
WHERE
action_code='CANCEL' and
pah.object_type_code='REQUISITION' and
pah.object_id=prh.REQUISITION_HEADER_ID
------------------------------------------------------------------------------------------------------------
INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER
SELECT
,RQH.SEGMENT1
,RQL.LINE_NUM
,RQL.REQUISITION_HEADER_ID
,RQL.REQUISITION_LINE_ID
,RQL.ITEM_ID
,RQL.UNIT_MEAS_LOOKUP_CODE
,RQL.UNIT_PRICE
,RQL.QUANTITY
,RQL.QUANTITY_CANCELLED
,RQL.QUANTITY_DELIVERED
,RQL.CANCEL_FLAG
,RQL.SOURCE_TYPE_CODE
,RQL.SOURCE_ORGANIZATION_ID
,RQL.DESTINATION_ORGANIZATION_ID
,RQH.TRANSFERRED_TO_OE_FLAG
FROM
,PO_REQUISITION_LINES_ALL RQL
,PO_REQUISITION_HEADERS_ALL RQH
WHERE
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID and
RQL.SOURCE_TYPE_CODE = 'INVENTORY' and
RQL.SOURCE_ORGANIZATION_ID is not null and not exists
(select 'existing internal order'from OE_ORDER_LINES_ALL LIN where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM
------------------------------------------------------------------------------------------------------------
DISPLAY WHAT REQUISITION AND PO ARE LINKED
(Relation with Requisition and PO )
SELECT
,r.segment1 "Req Num"
,p.segment1 "PO Num"
from
,po_headers_all p
,po_distributions_all d
,po_req_distributions_all rd
,po_requisition_lines_all rl
,po_requisition_headers_all r
WHERE
p.po_header_id = d.po_header_id and
d.req_distribution_id = rd.distribution_id and
rd.requisition_line_id = rl.requisition_line_id and
rl.requisition_header_id = r.requisition_header_id
------------------------------------------------------------------------------------------------------------
PURCHASE REQUISITION WITHOUT PO THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO
(Purchase Requisition without a Purchase Order)
SELECT
,prh.segment1 "PR NUM"
,trunc(prh.creation_date) "CREATED ON"
,trunc(prl.creation_date) "Line Creation Date"
,prl.line_num "Seq #"
,msi.segment1 "Item Num"
,prl.item_description "Description"
,prl.quantity "Qty"
,trunc(prl.need_by_date) "Required By"
,ppf1.full_name "REQUESTOR"
,ppf2.agent_name "BUYER"
FROM
,po.po_requisition_headers_all prh
,po.po_requisition_lines_all prl
,apps.per_people_f ppf1
,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2
,po.po_req_distributions_all prd
,inv.mtl_system_items_b msi
,po.po_line_locations_all pll
,po.po_lines_all pl
,po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id and
prl.requisition_line_id = prd.requisition_line_id and
ppf1.person_id = prh.preparer_id and
prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and
ppf2.agent_id(+) = msi.buyer_id and
msi.inventory_item_id = prl.item_id and
msi.organization_id = prl.destination_organization_id and
pll.line_location_id(+) = prl.line_location_id and
pll.po_header_id = ph.po_header_id(+) and
pll.pl_line_id = pl.po_line_id(+) and
prh.authorization_status = 'APPROVED' and
pll.line_location_id is NULL and
prl.closed_code is NULL and
nvl(prl.cancel_flag,'N') <> 'Y'
ORDER BY 1,2
------------------------------------------------------------------------------------------------------------
INFORMATION FROM PR to PO
(Requisition moved from different stages till converting into PR)
SELECT DISTINCT
,u.description "Requestor"
,porh.segment1 as "Req Number"
,trunc(porh.Creation_Date) "Created On"
,pord.LAST_UPDATED_BY
,porh.Authorization_Status "Status"
,porh.Description "Description"
,poh.segment1 "PO Number"
,trunc(poh.Creation_date) "PO Creation Date"
,poh.AUTHORIZATION_STATUS "PO Status"
,trunc(poh.Approved_Date) "Approved Date"
FROM
,apps.po_headers_all poh
,apps.po_distributions_all pod
,apps.po_req_distributions_all pord
,apps.po_requisition_lines_all porl
,apps.po_requisition_headers_all porh
,apps.fnd_user u
WHERE
porh.requisition_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and
pord.distribution_id = pod.req_distribution_id(+) and
pod.po_header_id = poh.po_header_id(+) and
porh.created_by = u.user_id
order by 2
------------------------------------------------------------------------------------------------------------
PO’s WHICH DOES NOT HAVE ANY PR’s
SELECT
,prh.segment1 "PR NUM"
,trunc(prh.creation_date) "CREATED ON"
,trunc(prl.creation_date) "Line Creation Date"
,prl.line_num "Seq #"
,msi.segment1 "Item Num"
,prl.item_description "Description"
,prl.quantity "Qty"
,trunc(prl.need_by_date) "Required By"
,ppf1.full_name "REQUESTOR"
,ppf2.agent_name "BUYER"
FROM
,po.po_requisition_headers_all prh
,po.po_requisition_lines_all prl
,apps.per_people_f ppf1
,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2
,po.po_req_distributions_all prd
,inv.mtl_system_items_b msi
,po.po_line_locations_all pll
,po.po_lines_all pl
,po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id and
prl.requisition_line_id = prd.requisition_line_id and
ppf1.person_id = prh.preparer_id and
prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and
ppf2.agent_id(+) = msi.buyer_id and
msi.inventory_item_id = prl.item_id and
msi.organization_id = prl.destination_organization_id and
pll.line_location_id(+) = prl.line_location_id and
pll.po_header_id = ph.po_header_id(+) and
pll.po_line_id = pl.po_line_id(+) and
prh.authorization_status = 'APPROVED' and
pll.line_location_id is NULL and
prl.closed_code is NULL and
nvl(prl.cancel_flag,'N') <> 'Y'
ORDER BY 1,2
------------------------------------------------------------------------------------------------------------
ALL PO’s WITH APPROVAL, INVOICE, & PAYMENT DETAILS
SELECT
,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"
,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
,h.amount
,h.check_id
,h.invoice_payment_id "Payment Id"
,i.check_number "Cheque Number"
,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
,PO.PO_VENDORS E
,PO.PO_VENDOR_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
f.vendor_site_id = i.vendor_site_id and
c.PO_HEADER_ID is not null and
a.payment_status_flag = 'Y' and
d.type_lookup_code != 'BLANKET'
------------------------------------------------------------------------------------------------------------
ALL OPEN PO'S
SELECT
,h.segment1 "PO NUM"
,h.authorization_status "STATUS"
,l.line_num "SEQ NUM"
,ll.line_location_id
,d.po_distribution_id
,h.type_lookup_code "TYPE"
FROM
,po.po_headers_all h
,po.po_lines_all l
,po.po_line_locations_all ll
,po.po_distributions_all d
WHERE
h.po_header_id = l.po_header_id and
ll.po_line_id = l.po_Line_id and
ll.line_location_id = d.line_location_id and
h.closed_date is NULL and
h.type_lookup_code not in ('QUOTATION')
------------------------------------------------------------------------------------------------------------
STEPS TO DEBUG A PURCHASE ORDER
Get po_header_id first and run each query and then analyze the data.
For better understanding this is splited into 5 major stages.
Stage 1: PO Creation
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;
PO_LINES_ALL
select * from po_lines_all where po_header_id =;
PO_LINE_LOCATIONS_ALL
select * from po_line_locations_all where po_header_id =;
PO_DISTRIBUTIONS_ALL
select * from po_distributions_all where po_header_id =;
PO_RELEASES_ALL
SELECT * FROM po_releases_all WHERE po_header_id =;

Stage 2: Once PO is received, data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);
RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =;
RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =;
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactionswhere po_header_id =);
RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id IN
(select transaction_id from rcv_transactions where po_header_id =);
RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id IN
(select transaction_id from rcv_transactions where po_header_id =);
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =;
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id IN
(select transaction_id from mtl_material_transactions where transaction_source_id =);

Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =);
AP_INVOICES_ALL
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 =));

Stage 4 : Mostly there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference IN
(select to_char(transaction_id) from mtl_material_transactions where transaction_source_id = );

Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);
GL_INTERFACE
select * from gl_interface gli where user_je_source_name =’Purchasing’
and gl_sl_link_table =’RSL’ and reference21=’PO’ and exists
(select 1 from rcv_receiving_sub_ledger rrsl where gli.reference22 =RRSL.reference2 and GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));
GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIR WHERE reference_1=’PO’ AND gl_sl_link_table =’RSL’AND EXISTS
( SELECT 1 FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3 AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions where po_header_id =))