Query :Requisition Number from PO Number

Query :Requisition Number from PO Number:

 

SELECT segment1
FROM po_requisition_headers_all
WHERE requisition_header_id IN
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_req_distributions_all
WHERE distribution_id IN
(SELECT req_distribution_id
FROM po_distributions_all
WHERE po_header_id IN
(SELECT po_header_id
FROM po_headers_all
WHERE segment1 = Give Po Number)))); 

Query : To find an PO details

Query : To find an PO details:

SELECT
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number_Release
, pol.line_num Line
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered) Quantity_Amount_Ordered
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed) Quantity_Amount_Billed
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered) Qty_Amount_Delivered
, DECODE (POL.order_type_lookup_code,
'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /
DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions pod
, mtl_system_items msi
, po_line_locations pll
, po_lines pol
, po_releases por
, po_headers poh
, po_vendors pov
, financials_system_parameters fsp
, po_line_types plt
WHERE poh.segment1=
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(por.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
ORDER BY pll.line_location_id; 

Query : Purchase Order and Requisition

Query : Purchase Order and Requisition

SELECT prh.segment1 req_number
,prh.authorization_status
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,pd.req_header_reference_num
,pd.req_line_reference_num
,pl.line_num
,pl.item_description
,pl.quantity
,pl.amount
,ph.segment1 po_number
,prd.distribution_id
,pd.req_distribution_id
FROM po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_distributions_all pd
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.org_id = prd.org_id
and prd.distribution_id = pd.req_distribution_id(+)
and prd.org_id = pd.org_id(+)
and pd.line_location_id = pll.line_location_id(+)
and pd.org_id = pll.org_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pll.org_id = pl.org_id(+)
and pl.po_header_id = ph.po_header_id(+)
and pl.org_id = ph.org_id(+);

Query : To find PO returns

Query : To find PO returns:

execute fnd_client_info.set_org_context('org_id');

SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum ( (nvl(rct.source_doc_quantity,0)) ) Qty_returned
from rcv_transactions rct
, po_lines pol
, po_line_locations pll
where rct.transaction_type = 'RETURN TO VENDOR'
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
union all
SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum ( (nvl(rct.amount,0)) ) Qty_returned
from rcv_transactions rct
, po_lines pol
, po_line_locations pll
where rct.transaction_type = 'RETURN TO VENDOR'
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id 

Query : INVENTORY & ORDER MANAGEMENT

Query : INVENTORY & ORDER MANAGEMENT:

Just replace xxx in the query with your organization_id

SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1

Query : Get Inventory Accounting Entries linked to GL

Query : Get Inventory Accounting Entries linked to GL

select mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1

Query : Supplier Invoice Details

Query : Supplier Invoice Details

selectap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount Invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name--,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account
from ap_invoices_all ap,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
ap_terms_tl at,
gl_code_combinations gcc
where alc.lookup_type='INVOICE TYPE'
and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE
and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'
and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE
and alc2.lookup_type='PAYMENT METHOD'
and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE
and ap.invoice_id=aid.invoice_id
and ap.vendor_id = pv.vendor_id
and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=pvs.vendor_id
and ap.vendor_site_id=pvs.vendor_site_id
--and ap.terms_id=at.term_id(+)
and ap.invoice_num=