=================================P2P==================================== 1.

Requisition =============== SELECT*FROM TAB WHERE TNAME LIKE 'PO_%' SELECT*FROM po_requisition_headers_all WHERE REQUISITION_HEADER_ID=71252 --(segment1 ==========> requisition_number) SELECT*FROM po_requisition_lines_all WHERE REQUISITION_HEADER_ID=71252 --(requisition_header_id FROM po_requisition_headers_all ) SELECT*FROM po_req_distributions_all WHERE REQUISITION_LINE_ID=79119 --(Note: requisition_header_id is the link between po_requisition_headers_all and po_requisition_lines_all --(requisition_line_id is the link between po_requisition_lines_all and po_req_distributions_all) **REQ_HEADER_REFERENCE_NUM IS THE REFERENCE FOR PO IS AUTO CREATED FROM requisition TO PO USING AUTO_CREATE** 2.Purchase order ================ SELECT*FROM po_headers_all WHERE SEGMENT1='13130004' ORDER BY CREATION_DATE DESC --(SEGMENT1====>PO_NUMBER) SELECT*FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID=1040 SELECT*FROM po_lines_all WHERE PO_HEADER_ID=148766 --(PO_HEADER_ID FROM POP_HEADERS_ALL) SELECT*FROM JAI_PO_TAXES WHERE PO_HEADER_ID=148766 --(LOCALIZATION TABLE TO FIND TAXES APPLIED FOR PO.PO_HEADER_ID FROM POP_HEADERS_ALL) SELECT*FROM JAI_CMN_TAXES_ALL WHERE TAX_ID IN (SELECT JPT.TAX_ID fROM JAI_PO_TAXES JPT WHERE JPT.TAX_ID=TAX_ID AND PO_HEADER_ID=148766) --(TAX_ID FROM JAI_PO_TAXES) SELECT*FROM JAI_CMN_TAX_CTGS_ALL WHERE TAX_CATEGORY_ID=10033 --(TAX_CATEGORY_ID FROM JAI_PO_TAXES) SELECT*FROM po_distributions_all WHERE PO_HEADER_ID=148766 --(PO_HEADER_ID FROM POP_HEADERS_ALL) SELECT*FROM po_line_locations_all WHERE PO_HEADER_ID=148766 --(Note: po_header_id is the link between all these tables) SELECT*FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID=1027 --(CODE_COMBINATION_ID FROM PO_DISTRBUTIONS_ALL) SELECT*FROM PO_VENDORS WHERE VENDOR_ID=1255 --(VENDOR_ID FROM PO_HEADERS_ALL) SELECT*FROM PO_VENDOR_SITES_ALL WHERE VENDOR_SITE_ID=240 --(VENDOR_SITE_ID FROM PO_HEADERS_ALL)

SELECT*FROM PO_VENDOR_CONTACTS WHERE VENDOR_CONTACT_ID=239 AND VENDOR_SITE_ID=240 --(VENDOR_ID,VENDOR_SITE_ID FROM PO_HEADERS_ALL) SELECT*FROM FND_USER WHERE USER_ID=2393 --(CREATED_BY=USER_ID PO_HEADERS) SELECT*fROM PER_ALL_PEOPLE_F WHERE PERSON_ID=6871 --(PERSON_ID=PREPARE_ID FROM po_requisition_headers_all) SELECT*FROM PO_AGENTS WHERE AGENT_ID=6871 --(AGENT_ID=AGENT_ID FROM PO_HEADERS_ALL) 3.Receipt =========== SELECT*FROM rcv_shipment_headers WHERE SHIPMENT_HEADER_ID=19016646 --(shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_lines) SELECT*FROM rcv_shipment_lines WHERE PO_HEADER_ID=148766 --(po_header_id is the link between rcv_shipment_headers and rcv_transaction) SELECT*FROM rcv_shipment_lines WHERE SHIPMENT_LINE_ID=19346283 --(SHIPMENT_LINE_ID=RCV_SHIPMENT_LINE_ID FROM AP_INVOICE_LINES_ALL) SELECT*FROM rcv_transactions WHERE SHIPMENT_HEADER_ID=19016646 --(shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_lines) SELECT*FROM rcv_transactions WHERE TRANSACTION_ID=26763160 --(TRANSACTION_ID=RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL SELECT*FROM qa_results WHERE RECEIPT_NUM='147201300002' and TRANSACTION_ID IN (26763160,26763161,26763162) --(quality result stored in qa_results table) 4.Invoices =========== SELECT*FROM ap_invoices_all WHERE INVOICE_ID=477297 --(INVOICE_ID FROM AP_INVOICE_LINES_ALL) SELECT*FROM AP_INVOICE_LINES_ALL WHERE PO_HEADER_ID=148766 --(PO_HEADER_ID FROM PO_HEADERS_ALL IF PO==>INVOICE CREATED PO_HEADER_ID POUPLATED) --(PO_DISTRBUTION_ID FROM po_distributions_all) SELECT*FROM ap_invoice_distributions_all WHERE INVOICE_ID=477297 --(INVOICE_ID FROM AP_INVOICES_ALL) --Note: invoice_id is the link between ap_invoices_all and ap_invoice_distributions_all ===========Payments============= SELECT*FROM ap_checks_all WHERE CHECK_ID=10366 --CHECK_ID FROM AP_INVOICE_PAYMNENTS_ALL) SELECT*FROM ap_invoice_payments_all WHERE INVOICE_ID=10419 --(INVOICE_ID FROM AP_INVOICES_ALL) SELECT*FROM ap_payment_schedules_all WHERE INVOICE_ID=10419 --(INVOICE_ID FROM AP_INVOICES_ALL)

5.Payment Reconcillation ======================= SELECT*FROM AP_BANK_ACCOUNTS_ALL --WHERE BANK_ACCOUNT_NUM='52052645919' SELECT*FROM ap_bank_branches SELECT*FROM AP_BANK_ACCOUNTS 6.GL Transfer =================== SELECT*FROM gl_periods SELECT*FROM GL_PERIOD_STATUSES SELECT*FROM GL_SETS_OF_BOOKS SELECT*fROM glfv_charts_of_accounts ====>>REQ--->PO(AUTO_CREATED) select DISTINCT prh.SEGMENT1 "Requisition Number", ph.SEGMENT1 "Purchase Order Number" from po_req_distributions_all prd, po_distributions_all pod, po_headers_all ph, po_requisition_lines_all prl, po_requisition_headers_all prh where prd.ORG_ID = pod.ORG_ID and pod.PO_HEADER_ID = ph.PO_HEADER_ID and prd.REQUISITION_LINE_ID = prl.REQUISITION_LINE_ID and prl.REQUISITION_HEADER_ID = prh.REQUISITION_HEADER_ID and ph.PO_HEADER_ID =74 and prl.REQUISITION_LINE_ID IN(321,322) SELECT*FROM po_action_history_v SELECT*FROM PO_PURCHASE_HISTORY_V WHERE PO_HEADER_ID=148766 --(po_header_id from po_headers_all) select*from PO_NOTES select*from PO_LOOKUP_CODES select*from PO_DOCUMENT_TYPES_ALL

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

Internal Requisitions without Sales order 2. You want to display what requisition and PO are linked(Relation with Requisition and PO ) Requisition and PO 3. You need to list out all cancel Requisitions Cancel Requisition 4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order) PR without PO 5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO. PR to PO 6.Identifying all PO's which does not have any PR's PO without Requisition 7. Relation between Requisition and PO tables

Here is link: PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1 PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID) PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1 What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req. 8.You need to find table which hold PO Approval path... These two table keeps the data: PO_APPROVAL_LIST_HEADERS PO_APPROVAL_LIST_LINES 9. List all the PO's with there approval ,invoice and Payment Details List PO's with Approval , invoice and Payment info 10.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions.. The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals. The following reference fields are used to capture and keep PO information in the GL_JE_LINES table. These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is Purchasing. Budgetary Records from PO (These include reservations, reversals and cancellations): REFERENCE_1- Source (PO or REQ) REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or po_requisition_headers_all.requisition_header_id) REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or po_req_distributions_all.distribution_id) REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or po_requisition_headers_all.segment1) REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1) Accrual Records from PO: REFERENCE_1- Source (PO) REFERENCE_2- PO Header ID (from po_headers_all.po_header_id) REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id REFERENCE_4- Purchase Order number (from po_headers_all.segment1) REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id) Take a note for Period end accruals, the REFERENCE_5 column is not used. 11. List me all open PO's List me all Open PO'S 12.There are different authorization_status can a requisition have. Approved Cancelled In Process Incomplete Pre-Approved Rejected and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'. 13. A standard Quotations one that you can tie back to a PO. Navigate to RFQ -> Auto create -> enter a PO and reference it back.

14. I want to debug for a PO , where should I start. Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.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 =<po_number>; select * from po_headers_all where po_header_id =<po_header_id>; po_lines_all select * from po_lines_all where po_header_id =<po_header_id>; po_line_locations_all select * from po_line_locations_all where po_header_id =<po_header_id>; po_distributions_all select * from po_distributions_all where po_header_id =<po_header_id>; po_releases_all SELECT * FROM po_releases_all WHERE po_header_id =<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_lines where po_header_id =<po_header_id>); RCV_SHIPMENT_LINES select * from rcv_shipment_lines where po_header_id =<po_header_id>; RCV_TRANSACTIONS select * from rcv_transactions where po_header_id =<po_header_id>; RCV_ACCOUNTING_EVENTS SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN (select transaction_id from rcv_transactions where po_header_id =<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 =<po_header_id>); RCV_SUB_LEDGER_DETAILS select * from rcv_sub_ledger_details where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>); MTL_MATERIAL_TRANSACTIONS select * from mtl_material_transactions where transaction_source_id =<po_header_id>; MTL_TRANSACTION_ACCOUNTS select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =<po_header_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 =<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 =<po_header_id>)); Stage 4 : Many Time 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 = <po_header_id> ); Stage 5 : General Ledger Prompt 17. GL_BC_PACKETS ..This is for encumbrances SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');

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.reference3 AND GLI.reference24 =RRSL.reference4 AND RRSL.rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id <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 RRSL WHERE GLIR.reference_2 =RRSL.reference2 AND GLIR.reference_3 =RRSL.reference3 AND GLIR.reference_4 =RRSL.reference4 AND RRSL.rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>))