Introduction
This Query will help to Fetch PO Receipt Details Data in Oracle Apps.
SQL Query:
SELECT pv.vendor_name, poh.ship_to_location, poh.bill_to_location,
DECODE (plla.match_option, ‘P’, ‘PO’, ‘R’, ‘Receipt’, ”) line_status,
UPPER (poh.status) authorization_status,
TRUNC (poh.creation_date) purchase_order_creation_date,
NVL (plla.promised_date, plla.need_by_date) promised_or_need_by_date,
poh.segment1 purchase_order_number, pla.line_num purchase_order_line,
mcb.segment1 product_line,
(SELECT concatenated_segments
FROM mtl_item_categories a,
mtl_category_sets_tl b,
mtl_categories_b_kfv c
WHERE a.category_set_id = b.category_set_id
AND b.category_set_name = ‘Royalty’
AND a.category_id = c.category_id
AND a.inventory_item_id = im.inventory_item_id
AND a.organization_id = im.organization_id
AND ROWNUM = 1) royalty_product,
‘=”‘ || im.segment1 || ‘”‘ item_number,
im.description item_description, pla.quantity purchase_order_quantity,
pla.unit_meas_lookup_code purchase_order_uom,
plla.quantity_received quantity_received, plla.quantity_billed,
(pla.quantity – plla.quantity_received) difference,
–MUC.CONVERSION_RATE UOM_Conversion_Rate,
pla.unit_price purchase_order_unit_price,
(plla.quantity_received * pla.unit_price
) extended_purchase_order_price,
(SELECT location_code
FROM hr_locations_all
WHERE location_id =
pvs.ship_to_location_id)
supplier_ship_to_location,
(SELECT location_code
FROM hr_locations_all
WHERE location_id =
pvs.bill_to_location_id)
supplier_bill_to_location,
poh.closed_code po_header_status, pla.closed_code po_line_status,
plla.closed_code po_shipments_status, sh.receipt_num doc_no,
poh.segment1 po_number,
TO_NUMBER (NVL (mt.primary_quantity, 0)) primary_quantity,
TO_NUMBER
(bolinf.th_get_base_uom_qty (mt.inventory_item_id,
mt.organization_id,
NVL (mt.primary_quantity, 0),
im.primary_uom_code
)
) base_qty,
( TO_NUMBER (bolinf.th_get_base_uom_qty (mt.inventory_item_id,
mt.organization_id,
NVL (mt.primary_quantity, 0),
im.primary_uom_code
)
)
* rt.po_unit_price
) extended_amount,
TRUNC (rt.transaction_date) transaction_date, NULL COST, poh.vendor_id,
mt.organization_id, mt.inventory_item_id, pla.po_line_id
FROM inv.mtl_material_transactions mt,
inv.mtl_system_items_b im,
po.rcv_transactions rt,
po.rcv_shipment_headers sh,
po_headers_v poh,
po_line_locations_all plla,
po_lines_all pla,
po_vendors pv,
mtl_item_categories mic,
mtl_categories_b mcb
— ,MTL_UOM_CONVERSIONS MUC
,
po_vendor_sites_all pvs
WHERE mt.transaction_type_id = 18
AND mt.inventory_item_id = im.inventory_item_id
AND mt.organization_id = im.organization_id
AND poh.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND im.organization_id = plla.ship_to_organization_id
AND pla.po_line_id = rt.po_line_id
AND pla.item_id = im.inventory_item_id
AND im.organization_id = mic.organization_id
AND im.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = 1 –‘Inventory’
AND pvs.vendor_site_id = poh.vendor_site_id
AND mt.primary_quantity > 0
AND mt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_header_id = sh.shipment_header_id
AND rt.po_header_id = poh.po_header_id
AND mic.category_id = mcb.category_id
AND mcb.segment1 = NVL (:p_category_id, mcb.segment1)
AND poh.vendor_id = pv.vendor_id
AND pv.vendor_name = NVL (:p_vendor_id, pv.vendor_name)
AND TRUNC (rt.transaction_date) BETWEEN NVL (:p_transaction_from_date,
rt.transaction_date
)
AND NVL (:p_transaction_to_date,
rt.transaction_date
)
AND im.organization_id = (SELECT organization_id
FROM org_organization_definitions
WHERE organization_code = :p_organization_id)
AND rt.po_line_location_id = plla.line_location_id
AND :p_po_or_receipt = ‘Receipt’
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your question
