VIEW: APPS.MRPBV_PLAN_INVENTORY_LOTS

Source

View Text - Preformatted

SELECT para.ORGANIZATION_CODE ,org.NAME ,lot.COMPILE_DESIGNATOR ,'_KF:INV:MSTK:SYSITEM' ,lot.LOT_SUB_INVENTORY_CODE ,lot.INVENTORY_LOT_NUMBER ,lot.LOT_QUANTITY ,lot.LOT_EXPIRATION_DATE ,lot.EXPIRED_LOT_QUANTITY ,lot.END_ITEM_UNIT_NUMBER ,lot.ORGANIZATION_ID ,lot.INVENTORY_ITEM_ID ,lot.PROJECT_ID ,lot.TASK_ID ,lot.LAST_UPDATE_DATE ,lot.LAST_UPDATED_BY ,lot.CREATION_DATE ,lot.CREATED_BY FROM MTL_PARAMETERS para, HR_ALL_ORGANIZATION_UNITS org, MRP_SYSTEM_ITEMS mrpitem, MTL_SYSTEM_ITEMS sysitem, MTL_LOT_NUMBERS lotnum, MRP_SUB_INVENTORIES mrpsub, MRP_PLAN_ORGANIZATIONS planorg, MRP_PLANS plan, MRP_INVENTORY_LOTS lot WHERE lot.COMPILE_DESIGNATOR = plan.COMPILE_DESIGNATOR AND planorg.PLAN_LEVEL(+) = 2 AND planorg.COMPILE_DESIGNATOR(+) = plan.COMPILE_DESIGNATOR AND planorg.ORGANIZATION_ID(+) = plan.ORGANIZATION_ID AND NVL(planorg.PLANNED_ORGANIZATION,plan.ORGANIZATION_ID) = lot.ORGANIZATION_ID AND lot.ORGANIZATION_ID = mrpsub.ORGANIZATION_ID AND lot.COMPILE_DESIGNATOR = mrpsub.COMPILE_DESIGNATOR AND lot.LOT_SUB_INVENTORY_CODE = mrpsub.SUB_INVENTORY_CODE AND lot.ORGANIZATION_ID = lotnum.ORGANIZATION_ID AND lot.INVENTORY_ITEM_ID = lotnum.INVENTORY_ITEM_ID AND lot.INVENTORY_LOT_NUMBER = lotnum.LOT_NUMBER AND lot.ORGANIZATION_ID = mrpitem.ORGANIZATION_ID AND lot.COMPILE_DESIGNATOR = mrpitem.COMPILE_DESIGNATOR AND lot.INVENTORY_ITEM_ID = mrpitem.INVENTORY_ITEM_ID AND lot.ORGANIZATION_ID = sysitem.ORGANIZATION_ID AND lot.INVENTORY_ITEM_ID = sysitem.INVENTORY_ITEM_ID AND lot.ORGANIZATION_ID = para.ORGANIZATION_ID AND lot.ORGANIZATION_ID = org.ORGANIZATION_ID AND '_SEC:LOT.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

View Text - HTML Formatted

SELECT PARA.ORGANIZATION_CODE
, ORG.NAME
, LOT.COMPILE_DESIGNATOR
, '_KF:INV:MSTK:SYSITEM'
, LOT.LOT_SUB_INVENTORY_CODE
, LOT.INVENTORY_LOT_NUMBER
, LOT.LOT_QUANTITY
, LOT.LOT_EXPIRATION_DATE
, LOT.EXPIRED_LOT_QUANTITY
, LOT.END_ITEM_UNIT_NUMBER
, LOT.ORGANIZATION_ID
, LOT.INVENTORY_ITEM_ID
, LOT.PROJECT_ID
, LOT.TASK_ID
, LOT.LAST_UPDATE_DATE
, LOT.LAST_UPDATED_BY
, LOT.CREATION_DATE
, LOT.CREATED_BY
FROM MTL_PARAMETERS PARA
, HR_ALL_ORGANIZATION_UNITS ORG
, MRP_SYSTEM_ITEMS MRPITEM
, MTL_SYSTEM_ITEMS SYSITEM
, MTL_LOT_NUMBERS LOTNUM
, MRP_SUB_INVENTORIES MRPSUB
, MRP_PLAN_ORGANIZATIONS PLANORG
, MRP_PLANS PLAN
, MRP_INVENTORY_LOTS LOT
WHERE LOT.COMPILE_DESIGNATOR = PLAN.COMPILE_DESIGNATOR
AND PLANORG.PLAN_LEVEL(+) = 2
AND PLANORG.COMPILE_DESIGNATOR(+) = PLAN.COMPILE_DESIGNATOR
AND PLANORG.ORGANIZATION_ID(+) = PLAN.ORGANIZATION_ID
AND NVL(PLANORG.PLANNED_ORGANIZATION
, PLAN.ORGANIZATION_ID) = LOT.ORGANIZATION_ID
AND LOT.ORGANIZATION_ID = MRPSUB.ORGANIZATION_ID
AND LOT.COMPILE_DESIGNATOR = MRPSUB.COMPILE_DESIGNATOR
AND LOT.LOT_SUB_INVENTORY_CODE = MRPSUB.SUB_INVENTORY_CODE
AND LOT.ORGANIZATION_ID = LOTNUM.ORGANIZATION_ID
AND LOT.INVENTORY_ITEM_ID = LOTNUM.INVENTORY_ITEM_ID
AND LOT.INVENTORY_LOT_NUMBER = LOTNUM.LOT_NUMBER
AND LOT.ORGANIZATION_ID = MRPITEM.ORGANIZATION_ID
AND LOT.COMPILE_DESIGNATOR = MRPITEM.COMPILE_DESIGNATOR
AND LOT.INVENTORY_ITEM_ID = MRPITEM.INVENTORY_ITEM_ID
AND LOT.ORGANIZATION_ID = SYSITEM.ORGANIZATION_ID
AND LOT.INVENTORY_ITEM_ID = SYSITEM.INVENTORY_ITEM_ID
AND LOT.ORGANIZATION_ID = PARA.ORGANIZATION_ID
AND LOT.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND '_SEC:LOT.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY