Monday, August 14, 2017

Purchasing Standard Queries

Oracle Purchasing ( PO )SQL Scripts , Queries


Click here to find Useful SQL Scripts, Queries ( All Modules)




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

  1. ---used to list all Internal Requisitions that do not have an  associated Internal Sales order  
  2. Select RQH.SEGMENT1 REQ_NUM,  
  3. RQL.LINE_NUM,  
  4. RQL.REQUISITION_HEADER_ID ,  
  5. RQL.REQUISITION_LINE_ID,  
  6. RQL.ITEM_ID ,  
  7. RQL.UNIT_MEAS_LOOKUP_CODE ,  
  8. RQL.UNIT_PRICE ,  
  9. RQL.QUANTITY ,  
  10. RQL.QUANTITY_CANCELLED,  
  11. RQL.QUANTITY_DELIVERED ,  
  12. RQL.CANCEL_FLAG ,  
  13. RQL.SOURCE_TYPE_CODE ,  
  14. RQL.SOURCE_ORGANIZATION_ID ,  
  15. RQL.DESTINATION_ORGANIZATION_ID,  
  16. RQH.TRANSFERRED_TO_OE_FLAG  
  17. from  
  18. PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH  
  19. where  
  20. RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID  
  21. and RQL.SOURCE_TYPE_CODE = 'INVENTORY'  
  22. and RQL.SOURCE_ORGANIZATION_ID is not null  
  23. and not exists (select 'existing internal order'  
  24. from OE_ORDER_LINES_ALL LIN  
  25. where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID  
  26. and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)  
  27. ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;  
2. You want to display what requisition and PO are linked(Relation with Requisition and PO )


  1. -----Relation with Requistion and PO  
  2. select r.segment1 "Req Num",  
  3.        p.segment1 "PO Num"  
  4. from po_headers_all p,   
  5. po_distributions_all d,  
  6. po_req_distributions_all rd,   
  7. po_requisition_lines_all rl,  
  8. po_requisition_headers_all r   
  9. where p.po_header_id = d.po_header_id   
  10. and d.req_distribution_id = rd.distribution_id   
  11. and rd.requisition_line_id = rl.requisition_line_id   
  12. and rl.requisition_header_id = r.requisition_header_id   


3. You need to list out all cancel Requisitions

  1. -----list My cancel Requistion  
  2. select prh.REQUISITION_HEADER_ID,  
  3.       prh.PREPARER_ID ,  
  4.       prh.SEGMENT1 "REQ NUM",  
  5.       trunc(prh.CREATION_DATE),  
  6.       prh.DESCRIPTION,  
  7.       prh.NOTE_TO_AUTHORIZER  
  8. from apps.Po_Requisition_headers_all prh,  
  9.      apps.po_action_history pah   
  10. where Action_code='CANCEL'   
  11. and pah.object_type_code='REQUISITION'   
  12. and pah.object_id=prh.REQUISITION_HEADER_ID   
4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)


  1. -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  
  2.   select   
  3.   prh.segment1 "PR NUM",   
  4.   trunc(prh.creation_date) "CREATED ON",   
  5.   trunc(prl.creation_date) "Line Creation Date" ,  
  6.   prl.line_num "Seq #",   
  7.   msi.segment1 "Item Num",   
  8.   prl.item_description "Description",   
  9.   prl.quantity "Qty",   
  10.   trunc(prl.need_by_date) "Required By",   
  11.   ppf1.full_name "REQUESTOR",   
  12.   ppf2.agent_name "BUYER"   
  13.   from   
  14.   po.po_requisition_headers_all prh,   
  15.   po.po_requisition_lines_all prl,   
  16.   apps.per_people_f ppf1,   
  17.   (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,   
  18.   po.po_req_distributions_all prd,   
  19.   inv.mtl_system_items_b msi,   
  20.   po.po_line_locations_all pll,   
  21.   po.po_lines_all pl,   
  22.   po.po_headers_all ph   
  23.   WHERE   
  24.   prh.requisition_header_id = prl.requisition_header_id   
  25.   and prl.requisition_line_id = prd.requisition_line_id   
  26.   and ppf1.person_id = prh.preparer_id   
  27.   and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date   
  28.   and ppf2.agent_id(+) = msi.buyer_id   
  29.   and msi.inventory_item_id = prl.item_id   
  30.   and msi.organization_id = prl.destination_organization_id   
  31.   and pll.line_location_id(+) = prl.line_location_id   
  32.   and pll.po_header_id = ph.po_header_id(+)   
  33.   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)   
  34.   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'   
  35.   AND PLL.LINE_LOCATION_ID IS NULL   
  36.   AND PRL.CLOSED_CODE IS NULL   
  37.   AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'  
  38.   ORDER BY 1,2  
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.


  1. ----- List and all data entry from PR till PO  
  2.   
  3. select distinct u.description "Requestor",   
  4. porh.segment1 as "Req Number",   
  5. trunc(porh.Creation_Date) "Created On",   
  6. pord.LAST_UPDATED_BY,   
  7. porh.Authorization_Status "Status",   
  8. porh.Description "Description",   
  9. poh.segment1 "PO Number",   
  10. trunc(poh.Creation_date) "PO Creation Date",   
  11. poh.AUTHORIZATION_STATUS "PO Status",   
  12. trunc(poh.Approved_Date) "Approved Date"  
  13. from apps.po_headers_all poh,   
  14. apps.po_distributions_all pod,   
  15. apps.po_req_distributions_all pord,   
  16. apps.po_requisition_lines_all porl,   
  17. apps.po_requisition_headers_all porh,   
  18. apps.fnd_user u   
  19. where porh.requisition_header_id = porl.requisition_header_id   
  20. and porl.requisition_line_id = pord.requisition_line_id   
  21. and pord.distribution_id = pod.req_distribution_id(+)   
  22. and pod.po_header_id = poh.po_header_id(+)   
  23. and porh.created_by = u.user_id  
  24. order by 2   
6.Identifying all PO's which does not have any PR's


  1. -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  
  2.   select   
  3.   prh.segment1 "PR NUM",   
  4.   trunc(prh.creation_date) "CREATED ON",   
  5.   trunc(prl.creation_date) "Line Creation Date" ,  
  6.   prl.line_num "Seq #",   
  7.   msi.segment1 "Item Num",   
  8.   prl.item_description "Description",   
  9.   prl.quantity "Qty",   
  10.   trunc(prl.need_by_date) "Required By",   
  11.   ppf1.full_name "REQUESTOR",   
  12.   ppf2.agent_name "BUYER"   
  13.   from   
  14.   po.po_requisition_headers_all prh,   
  15.   po.po_requisition_lines_all prl,   
  16.   apps.per_people_f ppf1,   
  17.   (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,   
  18.   po.po_req_distributions_all prd,   
  19.   inv.mtl_system_items_b msi,   
  20.   po.po_line_locations_all pll,   
  21.   po.po_lines_all pl,   
  22.   po.po_headers_all ph   
  23.   WHERE   
  24.   prh.requisition_header_id = prl.requisition_header_id   
  25.   and prl.requisition_line_id = prd.requisition_line_id   
  26.   and ppf1.person_id = prh.preparer_id   
  27.   and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date   
  28.   and ppf2.agent_id(+) = msi.buyer_id   
  29.   and msi.inventory_item_id = prl.item_id   
  30.   and msi.organization_id = prl.destination_organization_id   
  31.   and pll.line_location_id(+) = prl.line_location_id   
  32.   and pll.po_header_id = ph.po_header_id(+)   
  33.   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)   
  34.   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'   
  35.   AND PLL.LINE_LOCATION_ID IS NULL   
  36.   AND PRL.CLOSED_CODE IS NULL   
  37.   AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'  
  38.   ORDER BY 1,2  
7. List all the PO's with there approval ,invoice and Payment Details


  1. ----- List and PO With there approval , invoice and payment details  
  2. select   
  3. a.org_id "ORG ID",   
  4. E.SEGMENT1 "VENDOR NUM",  
  5. e.vendor_name "SUPPLIER NAME",  
  6. UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",   
  7. f.vendor_site_code "VENDOR SITE CODE",  
  8. f.ADDRESS_LINE1 "ADDRESS",  
  9. f.city "CITY",  
  10. f.country "COUNTRY",   
  11. to_char(trunc(d.CREATION_DATE)) "PO Date",   
  12. d.segment1 "PO NUM",  
  13. d.type_lookup_code "PO Type",   
  14. c.quantity_ordered "QTY ORDERED",   
  15. c.quantity_cancelled "QTY CANCELLED",   
  16. g.item_id "ITEM ID" ,   
  17. g.item_description "ITEM DESCRIPTION",  
  18. g.unit_price "UNIT PRICE",   
  19. (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",   
  20. (select   
  21. decode(ph.approved_FLAG, 'Y''Approved')   
  22. from po.po_headers_all ph   
  23. where ph.po_header_ID = d.po_header_id)"PO Approved?",   
  24. a.invoice_type_lookup_code "INVOICE TYPE",  
  25. a.invoice_amount "INVOICE AMOUNT",   
  26. to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",   
  27. a.invoice_num "INVOICE NUMBER",   
  28. (select   
  29. decode(x.MATCH_STATUS_FLAG, 'A''Approved')   
  30. from ap.ap_invoice_distributions_all x   
  31. where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?",   
  32. a.amount_paid,  
  33. h.amount,   
  34. h.check_id,   
  35. h.invoice_payment_id "Payment Id",   
  36. i.check_number "Cheque Number",   
  37. to_char(trunc(i.check_DATE)) "PAYMENT DATE"   
  38.    
  39. FROM AP.AP_INVOICES_ALL A,   
  40. AP.AP_INVOICE_DISTRIBUTIONS_ALL B,   
  41. PO.PO_DISTRIBUTIONS_ALL C,   
  42. PO.PO_HEADERS_ALL D,   
  43. PO.PO_VENDORS E,   
  44. PO.PO_VENDOR_SITES_ALL F,   
  45. PO.PO_LINES_ALL G,   
  46. AP.AP_INVOICE_PAYMENTS_ALL H,   
  47. AP.AP_CHECKS_ALL I   
  48. where a.invoice_id = b.invoice_id   
  49. and b.po_distribution_id = c. po_distribution_id (+)   
  50. and c.po_header_id = d.po_header_id (+)   
  51. and e.vendor_id (+) = d.VENDOR_ID   
  52. and f.vendor_site_id (+) = d.vendor_site_id   
  53. and d.po_header_id = g.po_header_id   
  54. and c.po_line_id = g.po_line_id   
  55. and a.invoice_id = h.invoice_id   
  56. and h.check_id = i.check_id   
  57. and f.vendor_site_id = i.vendor_site_id   
  58. and c.PO_HEADER_ID is not null   
  59. and a.payment_status_flag = 'Y'   
  60. and d.type_lookup_code != 'BLANKET'   


8.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.

9. List me all open PO's

  1. ----- List all open PO'S  
  2. select   
  3. h.segment1 "PO NUM",   
  4. h.authorization_status "STATUS",   
  5. l.line_num "SEQ NUM",   
  6. ll.line_location_id,   
  7. d.po_distribution_id ,   
  8. h.type_lookup_code "TYPE"   
  9. from   
  10. po.po_headers_all h,   
  11. po.po_lines_all l,   
  12. po.po_line_locations_all ll,   
  13. po.po_distributions_all d   
  14. where h.po_header_id = l.po_header_id   
  15. and ll.po_line_id = l.po_Line_id   
  16. and ll.line_location_id = d.line_location_id   
  17. and h.closed_date is null   
  18. and h.type_lookup_code not in ('QUOTATION')  
10.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'.



Below are the some of useful queries/scripts…..


List Purchase Requisitions with out PO


  1. -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  
  2.   select   
  3.   prh.segment1 "PR NUM",   
  4.   trunc(prh.creation_date) "CREATED ON",   
  5.   trunc(prl.creation_date) "Line Creation Date" ,  
  6.   prl.line_num "Seq #",   
  7.   msi.segment1 "Item Num",   
  8.   prl.item_description "Description",   
  9.   prl.quantity "Qty",   
  10.   trunc(prl.need_by_date) "Required By",   
  11.   ppf1.full_name "REQUESTOR",   
  12.   ppf2.agent_name "BUYER"   
  13.   from   
  14.   po.po_requisition_headers_all prh,   
  15.   po.po_requisition_lines_all prl,   
  16.   apps.per_people_f ppf1,   
  17.   (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,   
  18.   po.po_req_distributions_all prd,   
  19.   inv.mtl_system_items_b msi,   
  20.   po.po_line_locations_all pll,   
  21.   po.po_lines_all pl,   
  22.   po.po_headers_all ph   
  23.   WHERE   
  24.   prh.requisition_header_id = prl.requisition_header_id   
  25.   and prl.requisition_line_id = prd.requisition_line_id   
  26.   and ppf1.person_id = prh.preparer_id   
  27.   and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date   
  28.   and ppf2.agent_id(+) = msi.buyer_id   
  29.   and msi.inventory_item_id = prl.item_id   
  30.   and msi.organization_id = prl.destination_organization_id   
  31.   and pll.line_location_id(+) = prl.line_location_id   
  32.   and pll.po_header_id = ph.po_header_id(+)   
  33.   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)   
  34.   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'   
  35.   AND PLL.LINE_LOCATION_ID IS NULL   
  36.   AND PRL.CLOSED_CODE IS NULL   
  37.   AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'  
  38.   ORDER BY 1,2  

List all information form PR to PO


  1. ----- List and all data entry from PR till PO  
  2.   
  3. select distinct u.description "Requestor",   
  4. porh.segment1 as "Req Number",   
  5. trunc(porh.Creation_Date) "Created On",   
  6. pord.LAST_UPDATED_BY,   
  7. porh.Authorization_Status "Status",   
  8. porh.Description "Description",   
  9. poh.segment1 "PO Number",   
  10. trunc(poh.Creation_date) "PO Creation Date",   
  11. poh.AUTHORIZATION_STATUS "PO Status",   
  12. trunc(poh.Approved_Date) "Approved Date"  
  13. from apps.po_headers_all poh,   
  14. apps.po_distributions_all pod,   
  15. apps.po_req_distributions_all pord,   
  16. apps.po_requisition_lines_all porl,   
  17. apps.po_requisition_headers_all porh,   
  18. apps.fnd_user u   
  19. where porh.requisition_header_id = porl.requisition_header_id   
  20. and porl.requisition_line_id = pord.requisition_line_id   
  21. and pord.distribution_id = pod.req_distribution_id(+)   
  22. and pod.po_header_id = poh.po_header_id(+)   
  23. and porh.created_by = u.user_id  
  24. order by 2   

List out all cancel Requisitions


  1. -----list My cancel Requistion  
  2. select prh.REQUISITION_HEADER_ID,  
  3.       prh.PREPARER_ID ,  
  4.       prh.SEGMENT1 "REQ NUM",  
  5.       trunc(prh.CREATION_DATE),  
  6.       prh.DESCRIPTION,  
  7.       prh.NOTE_TO_AUTHORIZER  
  8. from apps.Po_Requisition_headers_all prh,  
  9.      apps.po_action_history pah   
  10. where Action_code='CANCEL'   
  11. and pah.object_type_code='REQUISITION'   
  12. and pah.object_id=prh.REQUISITION_HEADER_ID

Oracle Procure to Pay Cycle (P2P) Useful queries

Following queries can be very helpful if you are working in Purchasing module and need to know the details of a P2P transaction.Stage 1: SQL Queries To See The Details Of  a PO After It's 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>))

A single Query covering P2P life Cycle

A single Query covering P2P life Cycle

In real business world, many time when system is running external/internal auditor are more interested in scrutiny of:
  • Goods received / invoices received
  • Inaccurate or duplicate vendor & material master records
  • Discrepancies in payment terms
  • Delays / long processing times
  • Detect duplicate vendor
  • Unusually large or small payments
  • Unauthorized changes made to invoices
  • Detect Duplicate invoice
  • Detect Duplicate payment
  • Approval status
· Therefore thought to share this query, hope this would be great help who have such kind of adhoc requirement from daily life.
· Here is query:
· SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
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 STATUS",
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,
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';

P2P(Procure-to-Pay) Cycle Tables with Joins


1.Item Creation:

2.Supplier Creation:

3.Buyer Creation:

-------------------------------------------------

1.Item Creation:

1)Attach the Responsibility called "Inventory Vision Operations (USA)"
2)Open the Items form
  Items=>Master Item
3)Select the Organization name - Vision Operations
4)Enter the Item Name , Item Description 
  goto Inventory tab check the checkbox called Inventory 
  goto purchasing tab check the check box called Purchasing
5)Save
6)Goto  Tools Menu => Organization Assignment option to assign for the multiple
  organizations.
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'  --INVENTORY_ITEM_ID=20817

2.Supplier Creation:

1)Attach the Responsibility called "Purchasing Vision Operations (USA)"
2)Goto the Supplier Form
  Supply Base=>Suppliers
3)Enter the Supplier Name . Save supplier number will be created automatically.
4)Select the Sites button enter the supplier site address and other details
5)Goto the Contacts tab enter the Contact details 
    Name
    Phno
    Postion and so on.....
SELECT * FROM PO_VENDORS WHERE SEGMENT1='20067' --VENDOR_ID=7930
SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID=7930 --VENDOR_SITE_ID IN (4638,4639)
SELECT * FROM PO_VENDOR_CONTACTS WHERE VENDOR_SITE_ID IN (4638,4639)

3.Buyer Creation:

Buyer Creation:
==================
1)attach the Responsibility called "US HRMS Manager"
2)Create Employee  
  People => Enter and Maintain=>Select New button
3)Enter Emp name 
  select action option select "create Employement" select the optiona s "Buyer"
4)enter Data of Birth 
  save => Ok  = > Empoyee number will be generated.
5)Goto  System Administrator open the User form create or query user
  select the Person field attach the emp name (Which we have created)
6)Save.
7)Goto Purchasing Responsibility 
  open the Buyers form 
  enter the employee name and save the transactions.
 Setup => Personnal =>Buyers
SELECT * FROM PER_ALL_PEOPLE_F --WHERE --PERSON_ID='25'--EMPLOYEE_NUMBER='1289' --PERSON_ID=13496

Purchase Order Flow:

1.Requisition
2.RFQ(Request for Quatation)

1.REQUISITION:
Requisition: is one of the purchasing document will be prepared by the employee when
   ever he required the materials or Services or Training and so on.
we have two types of Requisitions 1)Internal
                    2)Purchase
Internal requisition will be created if materials are receiving from another Inventory
inside of the organization.
Purchase requisition will be created while purchasing the materials from the Suppliers.
Requisitions=>Requisitions
We will enter the Requisition at three level 1)Header
                        2)Line
                        3)Distributions.
Open the Requisition form enter the Reqno and select the type at Header level
 Enter the Items information at line level like Item name,qty,unitprice,tax and so on
select Distributions button enter the Distributions details.
Save
Select the Button called Approve button to go for approving the Requisition Document
Open the Requisition summary form.
Enter the Reqno select find button we can find the Requisition status wether it is
approved or not.
select Tools menu => View Action History to find the history details
Select Tools Menu  =>Control option to Cancel the requisition.
SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5716' --REQUISITION_HEADER_ID=56885
SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=56885  --REQUISITION_LINE_ID=60797
SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=60797
2.RFQ(REQUEST FOR QUOTATION)
Once the Requisition is Approved Buyer will prepare thre RFQ document which will be
delivered to the supplier. Supplier will respond for that with quotation.
we have Three types of RFQ documents
BID RFQ:This will be prepared for the secific fixed quantity and there won't be any
        PriceBraeaks(Discounts).
catalog RFQ: This will be create for te materials which we will purchase from the
        suppliers regularley , and large number of quantity. Here we can specify the
        Price Breaks.
Standard RFQ:  This will be prepared for the Items which we will purchase only once
        not very often,Here we can include the Discounts information at different
        auantity levels.
RFQ Information will be entered at 3 Level
  1)Headers
  2)Lines
  3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)
Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and we will enter
the terms abd condition details.
Payment Terms: When Organization is going to make the payment and Interest rates
Fright  Terms: Who is going to Bear the Tansportation chargers wether Buyer or Supplier
FOB(FreeOnBoard): If any materials damage or any missing quantity is there then the
             the responsiboility of those materials.
Carrier     : In which Transportation Company Organization Required Materials
              Transportation company Name.
Open the RFQ Form
RFQ and Quotations=>RFQ's
select TYpe and Dates and so on
enter the Items details at line level
select terms button enter the Terms and Condition Details
Select the Price Braks button enter the Price break details
Save
Select the suppliers button enter the suplier details (Who are receiving this Document)
Select the Button called Add from List to Include the supplier list automatically.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='347' AND TYPE_LOOKUP_CODE='RFQ' --PO_HEADER_ID=32876
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=32876 -- PO_LINE_ID=38063
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38063 --LINE_LOCATION_ID=72425
3.QUOTATION:
Quotation is another purchasing document we will receive from the Supplier which
contains the supplier quote details , Price, Payment terms and so on.
Whatever the quotations we have received from the supplier we will enter in the system
through form.
We have three types of Quotations 1)Bid 2)Catalog 3)Standard
For Bid RFQ      we will  receive Bid      quotation from the Supplier
For Catalog RFQ  we will  receive Catalog  quotation from the Supplier
For Standard RFQ we will  receive Standard quotation from the Supplier.
After enter all the quotations in the system management will do quote analysis as per
that one best quotation will be elected as Purchase Order.
Quotation Report
Item Name   (Table Value set MTL_SYSTEM_ITEMS_B   Segment1)
QuoteNo Type  Cdate Supplier Site ContactPerson Buyer   Created(UserName)
4.PURCHASE ORDER:
PO is one of the Main document which will be prepared and approved by the buyer and
send it to the supplier. which contains the following information
terms and Conditions
Items deails
Qty,Price
Distiribution and Shipment Details and so on.
We have four types of Purchase Order       1)STANDARD
     2)PLANNED
     3)BLANKET
     4)CONTRACT
Purchase Orders=> Purchase Orders
Open the PO form enter the Inforamtion at header level select line level inforamtion
enter the items and quantity,price details
select shippments button enter the shippment details select the Distributions button
enter the Distribution Detauils.
Save
Select the Button called Approve (Uncheck Email Check Box) , Document will be submitted
for approval.
open the Purchase Order summary form  enter PO number Select Find button we can find
the status of the Purchase order.
Goto Tools menu
Action History => We can find who hs submitted for Approve /Reject /Cancel details
Copy Document  => To Create Another PO based on this PO
Control        => To Close the Purchase Order or to cancel the Purchase Order.

Types of Purchase Order: -
Standard P.OPlanned P.OBlanket P.OContractP.O
Terms and Conditions
Goods or Service Known
Pricing Known
Quantity known
Account Distributions Known
Delivery Schedule Known
Can be Encumbered
Can Encumber releases
Yes
Yes
Yes
Yes
Yes
Yes
Yes
N/A
Yes
Yes
Yes
Yes
Yes
May be
Yes
Yes
Yes
Yes
May be
No
No
No
No
Yes
Yes
No
No
No
No
No
No
N/A
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='4514' AND TYPE_LOOKUP_CODE='STANDARD' --PO_HEADER_ID =32878
 --TYPE_LOOKUP_CODE

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =32879 --PO_LINE_ID=38065
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38066 --LINE_LOCATION_ID=72427
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=72428
SELECT * FROM PO_LOOKUP_CODES --LOOKUP_CODE
AUTO CREATE:
It is one of the Purchasing feature to create the RFQ and  PO documents automatically
by using requisition lines.
1)Create Requisition and approve
2)Open the AutoCreate form
3)Select Clear button enter the RequisitionNO
4)Select find button which will shows all the requisition lines
  select the lines whatever we want to include into the RFQ
5)select Action = Create to create new RFQ
  AddTo  to add lines to exisiting to RFQ
6)Select DocumentType = RFQ
7)select Automatic button which will create RFQ document automatically .
5.RECEIPTS:

           Receipts are one of the documents it will be used to find out how much quantity Supplier has supplied. We will find out Purchase Order status if it is successfully approved then we will create the Receipt. We will give the PO Number select Findbutton check the PO lines right mark and save. It will create the Receipt number select Header button it will shows the receipt number and date.           
                                                                                                                                                               SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=7472 --SHIPMENT_HEADER_ID=61421
SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=61421 -- SHIPMENT_LINE_ID=68368
SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_HEADER_ID=61421
Once Receipt will created go Inventory module and check whether the requested items are received or not by using these tables.
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'
SELECT * FROM MTL_ONHAND_QUANTITIES WHERE INVENTORY_ITEM_ID=20817 AND ORGANIZATION_ID=204
SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=32881 AND TYPE_LOOKUP_CODE='STANDARD'

Match Approval Level :- While creating the Purchase Order we will mention the Match Approval Level at Shipments we will have 3 types they are
2-way:- Purchase Order and Invoice Quantities must match within tolerance before the corresponding invoice can be paid.
3-way:- Purchase Order, Receipts and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.
4-way:- Purchase Order, Receipts, Inspection and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

Account Payables (A.P) Module:-
           Account payables will be used to do the payment transactions. A.P Module is integrated with both P.O and G.L Modules. In Account Payables we will create the invoices and we will approve once invoice is approved successfully we will make the payment. Once payment is over we will move the transactions from A.P to G.l.
1.  Without supplier we cannot create Invoice.
2.  Without invoice we cannot make Payment.                                      
From the company point of view a person or Organization who is going to receive amount we will call as Supplier.
Types of Invoices:-
1.     Standard
2.     Credit Memo
3.     Debit Memo
4.     With Holding Tax                                                                                   
5.     Po Default
6.     Mixed
7.     Pre Payment
8.     Expense Report
9.     Recurring Invoices
10.  Quick Match                                                                                
Standard Invoice:-    We will create the Standard Invoice to particular Supplier and Supplier site we will enter the invoice amount, invoice date and soon……..
Credit Memo & Debit Memo Invoices:- Both Invoices has got negative (-ve) amount and adjusted against Standard Invoice. Credit Memo will be created whenever Supplier is giving discount. Debit Memo will be created if buyer is going to deduct the amount.
With Holding Tax Invoice:-      If supplier is not registered supplier then buyer will make the Income Tax to the government on behalf of supplier.
Po Default Invoice:-    Here we will create the Invoice as per Purchase Order amount. We will give the Po number system will retrieve PO amount and Invoice will be created as per PO details.
Prepayment Invoice:-    When ever we want make payment to supplier in advance that tome we will create this Prepayment Invoice and we make the Payment.
Expense Reports Invoice:-     It will be created for employee expenses as per the employee grade, position this Invoices will be calculated.
Recurring Invoice:-      For some of the Invoices we will not be having supplier invoice that time we will create Recurring Invoices.
Ex:-  For rent account we will be creating Invoice which has got fixed amount and fixed rate (duration).
 Quick Match Invoice:- While creating Purchase Order we will be giving the match approval option as per that match approval we will create the Invoice and the Invoice type is Quick Match Invoice.
Mixed Invoice:- Mixed Invoices will be created for miscellaneous expenses. Once we create the invoice you have to do following 3 activities.
1.     Validate Invoice
2.     Approve the Invoice
3.     Create Accounting entries for Invoice 
 INVOICES
Here we will select the Invoice type and we will give the Supplier number, name, site invoice date, invoice number, invoice currencies, and amount. Select Distributions button to distribute the Invoice amount into different accounts.
1.     Invoice total should be equal to the distributions total then we will call it as Invoice validated successfully.
2.     Select Actions…1 button chooses approve check box press OK then system will approve the Invoice.
3.     Select Actions…1 button choose create accounting check box press OK button it will create the accounting entries we can see all this accounting transactions from tools view accounting option.
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV4516'  --INVOICE_ID=63379 ,--VENDOR_ID(LINK B/WAP INVOICE AND PO_VENDORS
)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=63379


Invoice Holds:-    If invoice is not approved then that invoice will be keeping under hold status. By selecting holds button in invoice form we can see the holds details.
For view Invoice holds details:
           Select * from ap_holds_all
For view release the Invoice holds names:
           Select * from ap_holds_release_name_v;
PAYMENTS:
Payments:-     Once the Invoice is approved then we can go for payments. The Payments are or 3 types. They were
1.     Manual
2.     Quick
3.     Refund
Manual:-    Here we will issue the checks manually to the supplier and we will capture that information in the payment scheme by using manual payment option.
Quick:-     Through the Quick Payment type we can generate checks through the system and we can have the transactions directly in the system.
Refund:-   When ever company is going to give advance back to the customer that time we will select payment type as Refund.
Navigation steps for Payments:-
                 payments  ==> payments
For view list of payments:
           Select * from ap_invoice_payments_all;
           Select * from ap_payment_schedules_all;
For check’s information:
           Select * from ap_checks_all;
For check format:
           Select * from ap_check_formats;
           Select * from ap_checkrun_conc_processes_all;

Distribution Set:-     It is one of the option is available in Invoices Screen. While creating the Invoice we will attach distribution set. System will automatically create the transactions in distributions forms as per the distribution set.
 Navigation:
 set-up =>invoice=> distribution set

To view Distribution sets at header level:
           Select * from ap_distribution_sets_all;
To view Distribution sets at lines level:
           Select * from ap_distribution_set_lines_all;
Transferring Transactions from AP to GL:-
           We will execute the concurrent program from SRS Window. This program will transfer all the payment transactions into the G.L Module. It will take following parameters.
Program Name:-   Payables Transfer to General Ledger
Parameters:-
           Set of Books Name
           Transfer Reporting Book(s)
           From Date
           To Date
           Journal Category
Validate Accounts
Transfer To GL Interface
Submit Journal Import :  yes  (It should be always YES)
To view from AP to GL:
           Select * from gl_interface;
To view journal import details:
           Select * from gl_je_headers  Ã        for Headers
           Select * from gl_je_lines      Ã        for Lines
           Select * from gl_je_batches  Ã        for Batches
To view posting:
          
           Select * from gl_balances;
          
           After submitting the request select viewà output button. It will shows number of transactions has been transferred to G.L. then select G.L Module (General Ledger, Vision Operations (USA)).
SELECT * FROM GL_JE_HEADERS
SELECT * FROM GL_JE_LINES
SELECT * FROM GL_JE_BATCHES
SELECT * FROM GL_BALANCES

PO Approval History Query in Oracle apps


Action History of PO 

SELECT DISTINCT prha.segment1 req
              , pha.segment1 po
              , gcc.segment3 acct
              , acct_desc.description acct_desc
              , gcc.segment4 ctr
              , ctr_desc.description ctr_desc
              , papf.full_name req_raised_by
              , reqah.full_name req_last_approver
              , poah.full_name po_last_approver
              , pv.vendor_name supplier
              , pvsa.vendor_site_code site
           FROM po.po_requisition_headers_all prha
              , po.po_requisition_lines_all prla
              , po.po_line_locations_all plla
              , po.po_lines_all pla
              , po.po_headers_all pha
              , po.po_distributions_all pda
              , gl.gl_code_combinations gcc
              , apps.po_vendors pv
              , apps.po_vendor_sites_all pvsa
              , apps.fnd_flex_values_vl acct_desc
              , apps.fnd_flex_values_vl ctr_desc
              , hr.per_all_people_f papf
              , (SELECT papf.full_name
                      , pah.action_code
                      , pah.object_id
                   FROM po.po_action_history pah
                      , po.po_requisition_headers_all prha
                      , applsys.fnd_user fu
                      , hr.per_all_people_f papf
                  WHERE object_id = prha.requisition_header_id
                    AND pah.employee_id = fu.employee_id
                    AND fu.employee_id = papf.person_id
                    AND SYSDATE BETWEEN papf.effective_start_date
                                    AND papf.effective_end_date
                    AND pah.object_type_code = 'REQUISITION'
                    AND pah.action_code = 'APPROVE'
                    AND pah.sequence_num =
                           (SELECT MAX(sequence_num)
                              FROM po.po_action_history pah1
                             WHERE pah1.object_id = pah.object_id
                               AND pah1.object_type_code = 'REQUISITION'
                               AND pah1.action_code = 'APPROVE')) reqah
              , (SELECT papf.full_name
                      , pah.action_code
                      , pah.object_id
                   FROM po.po_action_history pah
                      , po.po_headers_all pha
                      , applsys.fnd_user fu
                      , hr.per_all_people_f papf
                  WHERE object_id = pha.po_header_id
                    AND pah.employee_id = fu.employee_id
                    AND fu.employee_id = papf.person_id
                    AND SYSDATE BETWEEN papf.effective_start_date
                                    AND papf.effective_end_date
                    AND pah.object_type_code = 'PO'
                    AND pah.action_code = 'APPROVE'
                    AND pah.sequence_num =
                           (SELECT MAX(sequence_num)
                              FROM po.po_action_history pah1
                             WHERE pah1.object_id = pah.object_id
                               AND pah1.object_type_code = 'PO'
                               AND pah1.action_code = 'APPROVE')) poah
          WHERE prha.requisition_header_id = prla.requisition_header_id
            AND prla.line_location_id = plla.line_location_id
            AND plla.po_header_id = pla.po_header_id
            AND pla.po_header_id = pha.po_header_id
            AND pla.po_line_id = pda.po_line_id
            AND pda.code_combination_id = gcc.code_combination_id
            AND reqah.object_id = prha.requisition_header_id
            AND poah.object_id = pha.po_header_id
            AND prha.preparer_id = papf.person_id
            AND gcc.segment3 = acct_desc.flex_value
            AND gcc.segment4 = ctr_desc.flex_value
            AND pha.vendor_id = pv.vendor_id
            AND pha.vendor_site_id = pvsa.vendor_site_id
            AND pv.vendor_id = pvsa.vendor_id
            AND SYSDATE BETWEEN papf.effective_start_date
                            AND papf.effective_end_date
            AND prha.creation_date >= '01-APR-2009'
            AND prha.creation_date <= '03-APR-2009'
       ORDER BY prha.segment1
              , gcc.segment4

PO to GL query


SELECT prh.segment1 req_number,
       prd.distribution_id,
       poh.segment1 po_num,
       poh.org_id,
       rsh.receipt_num,
       api.invoice_num,
       apl.line_number,
       aps.vendor_name
FROM  -- Purchase Requisition --
     po_requisition_headers_all prh,
     po_requisition_lines_all prl,
     po_req_distributions_all prd,
     -- Purchase Order --
     po_distributions_all pod,
     po_headers_all poh,
     po_lines_all pol,
     -- Receipt --
     rcv_transactions rct,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh,
     -- AP Invoice --
     ap_invoice_distributions_all apd,
     ap_invoice_lines_all apl,
     ap_invoices_all api,
     -- Subledger Accounting-- 
     xla_distribution_links xldl,
     apps.xla_ae_lines al,
     xla_ae_headers ah,
     apps.xla_events e,
     apps.xla_transaction_entities te,
     -- GL --
     gl_import_references gir,
     apps.gl_je_lines jl,
     gl_je_headers glh,
     apps.gl_code_combinations glcc,
    -- Supplier Detail --
     ap_suppliers aps
WHERE  1 = 1
       -- Purchase Requisition --
      AND prh.requisition_header_id = prl.requisition_header_id
      AND prl.requisition_line_id = prd.requisition_line_id
      -- Purchase Requisition TO  Purchase Order --
      AND prd.distribution_id = pod.req_distribution_id
      -- Purchase Order --
      AND pol.po_line_id = pod.po_line_id
      AND poh.po_header_id = pol.po_header_id
      -- Purchase Order  TO Receipt --
      AND pod.po_distribution_id = rct.po_distribution_id
      -- Receipt --
      AND rct.shipment_line_id = rsl.shipment_line_id
      AND rsh.shipment_header_id = rsl.shipment_header_id
      -- Receipt TO Invoice --
      AND apd.po_distribution_id = rct.po_distribution_id
      -- AP Invoice --
      AND api.invoice_id = apd.invoice_id
      AND api.invoice_id = apl.invoice_id
      -- AP Invoice TO Sub ledger Accounting -- 
      AND xldl.applied_to_source_id_num_1 = api.invoice_id
      AND xldl.source_distribution_id_num_1 = apd.invoice_distribution_id
      -- Sub ledger Accounting --
      AND xldl.ae_line_num = al.ae_line_num
      AND xldl.ae_header_id = al.ae_header_id
      AND al.ae_header_id = ah.ae_header_id
      AND al.application_id = ah.application_id
      AND ah.event_id = e.event_id
      AND e.entity_id = te.entity_id(+)
      AND e.application_id = te.application_id(+)
      -- Subledger Accounting  TO   GL  -- 
      AND al.gl_sl_link_id = gir.gl_sl_link_id
      -- GL  ---
      AND gir.je_header_id = jl.je_header_id
      AND gir.je_line_num = jl.je_line_num
      AND jl.je_header_id = glh.je_header_id
     AND jl.code_combination_id = glcc.code_combination_id
      -- Supplier Detail --
      AND api.vendor_id = aps.vendor_id
      AND ah.je_category_name = 'Purchase Invoices'
      AND ah.gl_transfer_status_code= 'Y'
      AND glh.STATUS='P'
      --      AND api.invoice_num = 'BABU_TEST_INVOICE'
      --      AND apl.line_number = 1

Query to List all Purchase Orders with India Localization Tax attached



SELECT distinct pha.segment1 "Po Number",
       pha.creation_date "Date",
       pha.approved_date "Approved Date",
       (pla.unit_price* pla.quantity) "Amount w/o Tax",
       jpt.tax_amount "Tax Amount",
       jpll.total_amount "Total Amount",
       pla.line_num "Line Num",
       pv.vendor_name"Vendor Name",
       jrt.tax_name"Tax Name",
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) "Po Line Status",
       hou.name "Organization Name"
  FROM po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_vendors pv,
       JAI_PO_LINE_LOCATIONS jpll,
       hr_operating_units hou,
       JAI_RCV_TAX_V jrt,
       jai_po_taxes jpt,
       PO_LOOKUP_CODES POLC1,
       PO_LOOKUP_CODES POLC2
 WHERE pha.po_header_id=pla.po_header_id
   AND plla.po_header_id=pha.po_header_id
   AND pv.vendor_id=pha.vendor_id
   AND plla.po_line_id=pla.po_line_id
   AND pha.segment1=:P_PONUM   --'2012102020109'
   AND jpll.po_header_id=pha.po_header_id
   AND jpll.po_line_id=plla.po_line_id
   AND pha.org_id= hou.organization_id
   AND jrt.vendor_id=pha.vendor_id
   AND jrt.tax_id= jpt.tax_id
   AND jpt.po_header_id=pha.po_header_id
   AND jpt.po_line_id=pla.po_line_id
   AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION')
   AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
   AND POLC1.LOOKUP_CODE(+) = NVL(PLLA.CLOSED_CODE, 'OPEN')
   AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
   AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
   AND pha.po_header_id=plla.po_header_id
--AND jrt.tax_type=jpt.tax_type
--AND jpll.tax_category_id=jpt.tax_category_id
/*GROUP BY pha.segment1,
       pha.creation_date,
       pha.approved_date,
       jpt.tax_amount ,
       jpll.total_amount,
       pla.line_num ,
       pv.vendor_name,
       jrt.tax_name,
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL),
       hou.name*/

Query to fetch Supplier Information



Solution
---------
SELECT asp.vendor_name supplier_name,
       asp.segment1 supplier_number,
       ass.vendor_site_code supplier_site,
       hou.name operating_unit,
       decode(ass.pay_on_code,'RECEIPT',ass.pay_on_code,'') pay_on_receipt_flag,
       ass.pay_on_receipt_summary_code invoice_summary_level,
       ieppm.payment_method_code
  FROM ap_suppliers asp,
       ap_supplier_sites_all ass,
       hr_operating_units hou,
       IBY_EXTERNAL_PAYEES_ALL iepa,
       iby_ext_party_pmt_mthds ieppm
 WHERE asp.vendor_id=ass.vendor_id
   AND ass.org_id=hou.organization_id
   AND iepa.supplier_site_id=ass.vendor_site_id
   AND iepa.org_id=ass.org_id
   AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
   AND ass.org_id in(81,82,83,155);
hope this will help to you.... keep watching this site for more updates or bookmark this site.

Query to fetch requisition and PO are linked(Relation with Requisition and PO )


  1. -----Relation with Requistion and PO  
  2. select r.segment1 "Req Num",  
  3.        p.segment1 "PO Num"  
  4. from po_headers_all p,   
  5. po_distributions_all d,  
  6. po_req_distributions_all rd,   
  7. po_requisition_lines_all rl,  
  8. po_requisition_headers_all r   
  9. where p.po_header_id = d.po_header_id   
  10. and d.req_distribution_id = rd.distribution_id   
  11. and rd.requisition_line_id = rl.requisition_line_id   
  12. and rl.requisition_header_id = r.requisition_header_id 

Query to list out all Internal Requisitions that do not have an associated Internal Sales order.


select RQH.SEGMENT1 REQ_NUM,  
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;  

Relation between Purchasing Requisition and PO tables


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.

Requisition Approval History Query in Oracle apps


Checking Requisition Approval History for a specific member of staff

SELECT   pah.action_code
       , pah.object_id
       , pah.action_date
       , pah.sequence_num step
       , pah.creation_date
       , prha.segment1 req_num
       , prha.wf_item_key
       , prha.authorization_status
       , fu.description
       , papf.full_name hr_full_name
       , papf.employee_number emp_no
       , pj.NAME job
    FROM po.po_action_history pah
       , po.po_requisition_headers_all prha
       , applsys.fnd_user fu
       , hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.per_jobs pj
   WHERE object_id = prha.requisition_header_id
     AND pah.employee_id = fu.employee_id
     AND fu.employee_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.job_id = pj.job_id
     AND paaf.primary_flag = 'Y'
     AND SYSDATE BETWEEN papf.effective_start_date ANDpapf.effective_end_date
     AND SYSDATE BETWEEN paaf.effective_start_date ANDpaaf.effective_end_date
     AND pah.object_type_code = 'REQUISITION'
     AND pah.action_code = 'APPROVE'
     AND papf.full_name = :pn
ORDER BY pah.creation_date desc;

No comments:

Post a Comment