SELECT t.ou_name,
t.org_id,
t.TYPE,
t.Org_Type,
msi.segment1 item,
msi.inventory_item_id item_id,
mc.SEGMENT1,
mc.SEGMENT2,
secom_sotopo_pkg.get_item_desc(msi.inventory_item_id,1) desc1,
(SELECT NVL(SUM(PDA.QUANTITY_ORDERED - PDA.QUANTITY_CANCELLED - PDA.QUANTITY_DELIVERED),0) QUANTITY
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA,
HR_ORGANIZATION_UNITS HOU
WHERE PLL.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = HOU.organization_id
AND PLA.PO_LINE_ID = PLL.PO_LINE_ID
AND nvl(pla.closed_code,'x') NOT IN ('CLOSED FOR RECEIVING','CLOSED','FINALLY CLOSED')
AND nvl(plL.closed_code,'x') NOT IN ('CLOSED FOR RECEIVING','CLOSED','FINALLY CLOSED')
AND PLL.APPROVED_FLAG != 'Y'
-- Re-approved 20060328
AND PLL.APPROVED_FLAG != 'R'
AND PLA.ITEM_ID = msi.inventory_item_id
AND hou.organization_id IN ((SELECT ood.ORGANIZATION_ID
FROM org_organization_definitions ood
WHERE ood.OPERATING_UNIT = t.org_id))
AND HOU.TYPE = t.type) unapproved_po,
(select nvl(sum(prl.quantity - nvl(prl.quantity_cancelled,0) - nvl(prl.quantity_delivered,0)
- nvl(prl.quantity_received,0)),0)
from po_requisition_lines_all prl,
po_requisition_headers_all prh,
hr_organization_units hou
where prh.requisition_header_id = prl.requisition_header_id
and prh.authorization_status = 'APPROVED'
and prl.item_id = msi.inventory_item_id
and (prl.closed_code != 'FINALLY CLOSED'
or prl.closed_code is null)
and (prl.cancel_flag != 'Y'
or prl.cancel_flag is null)
and prl.org_id = t.org_id
and hou.organization_id = prl.destination_organization_id
AND hou.TYPE IS NOT NULL
and hou.type = t.type
and not exists(select 'X'
from secom_prtopo_lists spl
where prl.requisition_line_id = spl.requisition_line_id)) pr_quantity
FROM (SELECT DISTINCT
ou.NAME ou_name,
ou.organization_id org_id,
v.meaning org_type,
hou.TYPE type
FROM hr_organization_units hou,
org_organization_definitions ood,
hr_operating_units ou,
fnd_lookup_values_vl v
WHERE hou.organization_id = ood.ORGANIZATION_ID
AND hou.TYPE IS NOT NULL
AND ou.organization_id = ood.OPERATING_UNIT
AND v.lookup_type = 'ORG_TYPE'
AND v.lookup_code = hou.type) t,
mtl_system_items_b msi,
MTL_CATEGORIES_B_KFV MC,
MTL_ITEM_CATEGORIES MIC
--MTL_ITEM_CATEGORIES_V mic
WHERE msi.organization_id||'' = 84
AND msi.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
AND mic.ORGANIZATION_ID = msi.ORGANIZATION_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND mic.CATEGORY_SET_ID = 1
