VIEW: APPS.MRPFV_PLAN_ITEM_CAPACITIES

Source

View Text - Preformatted

SELECT DISTINCT PARA.ORGANIZATION_CODE ,ORG.NAME ,ITEMS.COMPILE_DESIGNATOR ,'_KF:INV:MSTK:SYSITEM' ,SUPPLIER.VENDOR_NAME ,SUPPSITE.VENDOR_SITE_CODE ,CAP.TO_DATE ,CAP.FROM_DATE ,CAP.CAPACITY_PER_DAY ,PLANS.ORGANIZATION_ID ,ITEMS.INVENTORY_ITEM_ID ,PO.VENDOR_ID ,PO.VENDOR_SITE_ID ,PO.LAST_UPDATE_DATE ,PO.LAST_UPDATED_BY ,PO.CREATION_DATE ,PO.CREATED_BY FROM MTL_PARAMETERS para ,HR_ALL_ORGANIZATION_UNITS org ,MTL_SYSTEM_ITEMS sysitem ,PO_VENDORS supplier ,PO_VENDOR_SITES_ALL suppsite ,PO_APPROVED_SUPPLIER_LIST asl ,PO_ASL_ATTRIBUTES po ,PO_SUPPLIER_ITEM_CAPACITY cap ,MRP_SYSTEM_ITEMS ITEMS ,MRP_PLAN_ORGANIZATIONS mpo ,MRP_PLANS plans WHERE asl.item_id = items.inventory_item_id and asl.using_organization_id = -1 and po.asl_id = asl.asl_id and po.using_organization_id = asl.using_organization_id and PO_ASL_SV.check_asl_action('2_SOURCING', po.vendor_id, po.vendor_site_id, items.inventory_item_id, NULL,NULL) = 1 and cap.asl_id = po.asl_id and cap.using_organization_id = po.using_organization_id and trunc(nvl(cap.to_date,sysdate+1)) > trunc(sysdate) and mpo.plan_level(+) = 2 and mpo.compile_designator(+) = plans.compile_designator and mpo.organization_id(+) = plans.organization_id and items.organization_id = NVL(mpo.planned_organization, plans.organization_id) and items.compile_designator = PLANS.COMPILE_DESIGNATOR and plans.organization_id = para.organization_id and plans.organization_id = org.organization_id and items.organization_id = sysitem.organization_id and items.inventory_item_id = sysitem.inventory_item_id and po.vendor_id = supplier.vendor_id(+) and po.vendor_site_id = suppsite.vendor_site_id(+) and '_SEC:PLANS.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

View Text - HTML Formatted

SELECT DISTINCT PARA.ORGANIZATION_CODE
, ORG.NAME
, ITEMS.COMPILE_DESIGNATOR
, '_KF:INV:MSTK:SYSITEM'
, SUPPLIER.VENDOR_NAME
, SUPPSITE.VENDOR_SITE_CODE
, CAP.TO_DATE
, CAP.FROM_DATE
, CAP.CAPACITY_PER_DAY
, PLANS.ORGANIZATION_ID
, ITEMS.INVENTORY_ITEM_ID
, PO.VENDOR_ID
, PO.VENDOR_SITE_ID
, PO.LAST_UPDATE_DATE
, PO.LAST_UPDATED_BY
, PO.CREATION_DATE
, PO.CREATED_BY
FROM MTL_PARAMETERS PARA
, HR_ALL_ORGANIZATION_UNITS ORG
, MTL_SYSTEM_ITEMS SYSITEM
, PO_VENDORS SUPPLIER
, PO_VENDOR_SITES_ALL SUPPSITE
, PO_APPROVED_SUPPLIER_LIST ASL
, PO_ASL_ATTRIBUTES PO
, PO_SUPPLIER_ITEM_CAPACITY CAP
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS MPO
, MRP_PLANS PLANS
WHERE ASL.ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ASL.USING_ORGANIZATION_ID = -1
AND PO.ASL_ID = ASL.ASL_ID
AND PO.USING_ORGANIZATION_ID = ASL.USING_ORGANIZATION_ID
AND PO_ASL_SV.CHECK_ASL_ACTION('2_SOURCING'
, PO.VENDOR_ID
, PO.VENDOR_SITE_ID
, ITEMS.INVENTORY_ITEM_ID
, NULL
, NULL) = 1
AND CAP.ASL_ID = PO.ASL_ID
AND CAP.USING_ORGANIZATION_ID = PO.USING_ORGANIZATION_ID
AND TRUNC(NVL(CAP.TO_DATE
, SYSDATE+1)) > TRUNC(SYSDATE)
AND MPO.PLAN_LEVEL(+) = 2
AND MPO.COMPILE_DESIGNATOR(+) = PLANS.COMPILE_DESIGNATOR
AND MPO.ORGANIZATION_ID(+) = PLANS.ORGANIZATION_ID
AND ITEMS.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND PLANS.ORGANIZATION_ID = PARA.ORGANIZATION_ID
AND PLANS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ITEMS.ORGANIZATION_ID = SYSITEM.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SYSITEM.INVENTORY_ITEM_ID
AND PO.VENDOR_ID = SUPPLIER.VENDOR_ID(+)
AND PO.VENDOR_SITE_ID = SUPPSITE.VENDOR_SITE_ID(+)
AND '_SEC:PLANS.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY