Oracle Apps PO Line Details Query with Org Restriction

Before Executing the query Execute apps Initialize in your SQL client.

SELECT pha.po_header_id,
pla.po_line_id,
pha.segment1
po_number,
pha.creation_date
po_date,
pha.currency_code
currency,
NVL (pla.CLOSED_CODE, pha.authorization_status)
po_status,
asup.VENDOR_NAME
buyer_name,
pla.line_num
po_line_number,
msib.segment1
item_code,
msib.description
item_description,
msib.primary_unit_of_measure,
pla.unit_price,
pla.quantity
ordered_qty,
(SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
quantity_received,
(SELECT SUM (NVL (quantity_billed, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
quantity_billed,
pla.quantity
– (SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
pending_qty,
( pla.quantity
– (SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id))
* pla.unit_price
pending_value,
pla.quantity
– ( (SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
+ (SELECT SUM (NVL (ALLOCATION_QTY, 0))
FROM xxatc_shipment_dtl_v xsdv
WHERE xsdv.po_header_id = pha.PO_HEADER_ID
AND xsdv.po_line_id = pla.PO_LINE_ID))
available_qty,
(SELECT SUM (NVL (ALLOCATION_QTY, 0))
FROM xxatc_shipment_dtl_v xsdv
WHERE xsdv.po_header_id = pha.PO_HEADER_ID
AND xsdv.po_line_id = pla.PO_LINE_ID)
avl_qty,
mc.segment1
family_,
mc.segment2
sub_family,
pha.vendor_id
FROM po_headers_all pha,
ap_suppliers asup,
po_lines_all pla,
mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories_b_kfv mc
WHERE 1 = 1
AND pha.po_header_id = pla.po_header_id
AND pha.vendor_id = asup.vendor_id
AND pla.item_id = msib.inventory_item_id
AND msib.organization_id = pla.org_id
AND mic.CATEGORY_SET_ID = 1
AND msib.organization_id = mic.organization_id
AND msib.inventory_item_id = mic.inventory_item_id
AND mic.category_id = mc.category_id
AND pha.AUTHORIZATION_STATUS = ‘APPROVED’
AND NVL (pla.CLOSED_CODE, ‘OPEN’) = ‘OPEN’
AND fnd_profile.VALUE (‘ORG_ID’) = 142 –For Org Restriction
ORDER BY PO_NUMBER DESC, PO_LINE_NUMBER ASC