Sales Invoice Report,Sales Order Details

Sales Invoice Report

select distinct
ooha.order_number, ooha.created_by,ooha.ATTRIBUTE1 Payment_Mode,ooha.BLANKET_NUMBER "Booking Reference",loc.CITY Destination,
rcta.CT_REFERENCE,cust_acct.account_number cust_code,party.party_name cust_name,
ooha.ORDERED_DATE,ooha.ATTRIBUTE7 Truck_NO, ooha.ATTRIBUTE8 Driver_Name,rcta.Attribute5 c_Date ,
rcta.Attribute4 Cheque,rcta.attribute3 Bank , rcta.attribute1 CASH,ooha.TRANSACTIONAL_CURR_CODE Currency,
CASE
WHEN ot.NAME LIKE '%OPC%'
THEN 'OPC'
WHEN ot.NAME LIKE '%SRC%'
THEN 'SRC'
WHEN ot.NAME LIKE '%Clinker%OPC%'
THEN 'Clinker-OPC'
WHEN ot.NAME LIKE '%Clinker%SRC%'
THEN 'Clinker-SRC'
ELSE ot.NAME
END AS PRODUCT_TYPE,
/* CASE
WHEN ot.NAME LIKE '%Bag%'
THEN wsd.requested_quantity
ELSE NULL
END AS sold_bag_quantity,
CASE
WHEN ot.NAME LIKE '%TONS%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_ton_quantity,*/
CASE
WHEN oola.ORDER_QUANTITY_UOM = 'TON'
THEN oola.ORDERED_QUANTITY
WHEN oola.ORDER_QUANTITY_UOM = 'BAG'
THEN (oola.ORDERED_QUANTITY*50)
ELSE NULL
END AS sold_ton_quantity,
CASE
WHEN oola.ORDER_QUANTITY_UOM = 'BAG'
THEN oola.ORDERED_QUANTITY
ELSE NULL
END AS sold_bag_quantity,
CASE
WHEN ot.NAME LIKE '%BULK%TONS%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_bilk_ton_quantity,
CASE
WHEN ooha.attribute1 LIKE 'Cash'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cash,
CASE
WHEN ooha.attribute1 LIKE 'Check'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cheque,
CASE
WHEN ooha.attribute1 LIKE 'Cash-Transfer'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cash_Transfer,
CASE
WHEN ooha.attribute1 LIKE 'Cash-Deposits'
THEN (oola.unit_selling_price * oola.ordered_quantity)
ELSE NULL
END AS Pay_Cash_Deposit,
(oola.unit_selling_price * oola.ordered_quantity)Total,
ship_su.location||' '||loc.ADDRESS1||' '||loc.ADDRESS2||' '||loc.ADDRESS3||' '||loc.POSTAL_CODE ||' '||loc. CITY ||' '||NVL(loc.STATE , loc.PROVINCE) ||' '||loc.COUNTRY Location,
ship_su.SITE_USE_CODE,
/*----------------Dispatch Details----------*/
TO_CHAR (mtrh.date_required, 'HH12:MI AM') time_in,
TO_CHAR (wnd.ultimate_dropoff_date, 'HH12:MI AM') time_out,
wpbv.attribute1 "Empty Weight",
wpbv.attribute2 "empty Weigh Bridge Ref",
wsd.shipped_quantity net_weight,
mmt.ATTRIBUTE1 "Gross weigh M Tons", mmt.ATTRIBUTE4 "Weigh Brid Ref.",
/* wpbv.attribute2 Wt_Bridge_Ref, wpbv.attribute1 Gross_wt_M_Tons,wpbv.TO_SCHEDULED_SHIP_DATE,*/
/*----------------Invoice Details----------------*/
CASE
WHEN oola.ORDER_QUANTITY_UOM LIKE 'TON'
THEN (oola.unit_selling_price)
WHEN oola.ORDER_QUANTITY_UOM LIKE 'BAG'
THEN (oola.unit_selling_price)*(1000/50)
ELSE NULL
END AS "Price Per M Ton (SR)",
rcta.trx_number invoice_number,
(oola.unit_selling_price * oola.ordered_quantity) "Total Amount (SR)" ,
to_char(to_date((oola.ordered_quantity * oola.unit_selling_price),'J'), 'JSP')||' RIYAL ONLY' "Amount in Words" ,
ooha.PAYMENT_TYPE_CODE
from ra_customer_trx_all rcta,
oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all acct,
hz_party_sites party_site ,
hz_locations loc,
oe_transaction_types_tl ot,
wsh_shipping_details_v wsd,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_picking_batches wpb,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
WSH_PICKING_BATCHES_V wpbv,
mtl_material_transactions mmt,
WSH_PICK_SLIP_V WPSV
where
ooha.header_id=oola.header_id
AND ooha.ATTRIBUTE1 in ('Cash','Cash-Transfer','Cash-Deposit','Check')
AND to_char(ooha.order_number)=rcta.CT_REFERENCE(+)
AND ooha.sold_to_org_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
--AND ooha.ship_to_org_id = ship_su.site_use_id(+)
AND ooha.ship_to_org_id = ship_su.site_use_id
AND acct.cust_acct_site_id = ship_su.cust_acct_site_id
and acct.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and cust_acct.party_id = party.party_id
--AND ship_su.SITE_USE_CODE = 'BILL_TO'
AND ooha.order_type_id = ot.transaction_type_id
AND ot.LANGUAGE = USERENV ('LANG')
AND ooha.header_id=wsd.order_header_id
AND oola.line_id=wsd.order_line_id
AND wdd.source_header_id=ooha.header_id
AND wdd.source_line_id=oola.line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id= wnd.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
and ooha.header_id = wpb.order_header_id(+)
AND ooha.ORDER_NUMBER=wpbv.ORDER_NUMBER(+)
AND oola.LINE_ID=mmt.TRX_SOURCE_LINE_ID
AND wpsv.move_order_line_id =wdd.move_order_line_id
AND wpsv.TRANSACTION_ID=mmt.TRANSACTION_ID
AND ooha.order_number = NVL (:p_order_number, ooha.order_number)
AND party.party_name=nvl(:p_party_name,party.party_name)
AND cust_acct.account_number=nvl(:p_account_number,cust_acct.account_number)
AND trunc(ooha.ORDERED_DATE) between nvl(:p_inv_date_from,ooha.ORDERED_DATE) and nvl(:p_inv_date_to,ooha.ORDERED_DATE)ORDER BY ooha.order_number;
/

Sales Order Details

SELECT ooh.order_number,
trunc(ooh.ordered_date),
cust.ACCOUNT_NUMBER customer_number
,ott.NAME order_type
, hp_bill.party_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
||hl_ship.address2||Decode(hl_ship.address3,NULL,' ',chr(10))
||hl_ship.address3||Decode(hl_ship.address4,NULL,' ',chr(10))
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',')
||hl_ship.state ||Decode(hl_ship.postal_code,'',',')
||hl_ship.postal_code ship_to_address
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
||hl_bill.address2||Decode(hl_bill.address3,NULL,' ',chr(10))
||hl_bill.address3||Decode(hl_bill.address4,NULL,' ',chr(10))
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',')
||hl_bill.state ||Decode(hl_bill.postal_code,'',',')
||hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code,
CASE
WHEN ot.NAME LIKE '%Bag%'
THEN wsd.requested_quantity
ELSE NULL
END AS sold_bag_quantity,
CASE
WHEN ot.NAME LIKE '%Tons%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_ton_quantity,
CASE
WHEN ot.NAME LIKE '%Bulk%Tons%'
THEN wsd.shipped_quantity
ELSE NULL
END AS sold_bilk_ton_quantity,
CASE
WHEN ooh.PAYMENT_TYPE_CODE LIKE 'Cash'
THEN nvl(ool.ordered_quantity,0) * nvl(ool.unit_selling_price,0)
ELSE NULL
END AS Pay_Cash,
CASE
WHEN ooh.PAYMENT_TYPE_CODE LIKE 'Check'
THEN nvl(ool.ordered_quantity,0) * nvl(ool.unit_selling_price,0)
ELSE NULL
END AS Pay_Check,
ooh.CHECK_NUMBER,
ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
FROM oe_order_headers_all ooh
, oe_order_lines_all ool
, oe_transaction_types_tl ott
, oe_transaction_types_tl ot
, wsh_shipping_details_v wsd
, hz_cust_site_uses_all hcs_ship
, hz_cust_accounts cust
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
--AND header_id = :p_header_id
AND ooh.header_id = ool.header_id
AND ooh.order_type_id = ott.transaction_type_id
ANd ooh.order_type_id = ot.transaction_type_id
AND wsd.order_header_id = ooh.header_id
AND wsd.order_line_id = ool.line_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND cust.cust_account_id = hca_ship.cust_account_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id;

Technical Queries related to Oracle Purchasing

] TO LIST OUT ALL CANCEL REQUISITIONS:->> list My cancel Requistionselect prh.REQUISITION_HEADER_ID, prh.PREPARER_ID , prh.SEGMENT1 "REQ NUM", trunc(prh.CREATION_DATE), prh.DESCRIPTION, prh.NOTE_TO_AUTHORIZERfrom 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

2] TO LIST 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_FLAGfromPO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select 'existing internal order'from OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

3] 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
4] List all Purchase Requisition without a Purchase Order 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.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

5] 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.>> LIST AND ALL DATA ENTRY FROM PR TILL POselect 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
6] Identifying all PO’s which does not have any PR’s>>LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO. 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

7] Relation between Requisition and PO tables>>Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_IDPO_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.
--You need to find table which hold PO Approval path…
These two table keeps the data:
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES
8] List all the PO’s with there approval ,invoice and Payment Details>>LIST AND PO WITH THERE APPROVAL , INVOICE AND PAYMENT DETAILSselect 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'
10] 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) isPurchasing.
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 orpo_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id orpo_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 orpo_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 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')
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] 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 =;
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 : 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_transactionswhere 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 GLIWHERE user_je_source_name =’Purchasing’AND gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND 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 GLIRWHERE reference_1=’PO’AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))