VIEW: APPS.MRP_ATP_BASE_V

Source

View Text - Preformatted

SELECT /* RESOURCES */ D.ROWID , D.ATP_GROUP_ID , D.ORGANIZATION_ID , TO_NUMBER(D.C_COLUMN6) , D.INVENTORY_ITEM_ID , D.DEPARTMENT_ID , DEPT.DEPARTMENT_CODE , DEPT.DESCRIPTION , TO_NUMBER(D.C_COLUMN8) , D.RESOURCE_ID , R.RESOURCE_CODE , R.DESCRIPTION , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , 0-( D.RESOURCE_ID * POWER(10, LENGTH(TO_NUMBER(D.C_COLUMN8)) + 1) + TO_NUMBER(D.C_COLUMN8) * POWER (10, 1) + LENGTH(TO_NUMBER(D.C_COLUMN8))) , D.LAST_UPDATE_DATE , D.LAST_UPDATED_BY , D.CREATION_DATE , D.CREATED_BY , D.LAST_UPDATE_LOGIN , D.ATP_RULE_ID , D.LINE_ITEM_QUANTITY , D.REQUEST_DATE_ATP_QUANTITY , D.EARLIEST_ATP_DATE , D.EARLIEST_ATP_DATE_QUANTITY , D.REQUEST_ATP_DATE , D.REQUEST_ATP_DATE_QUANTITY , D.GROUP_AVAILABLE_DATE , D.ACCUMULATION_WINDOW , D.ATP_LEAD_TIME , D.ATP_CHECK , D.INFINITE_TIME_FENCE_DATE , D.LINE_ITEM_UOM , D.GROUP_ATP_CHECK , D.USER_LINE_NUM , D.ATTRIBUTE_CATEGORY , D.ATTRIBUTE1 , D.ATTRIBUTE2 , D.ATTRIBUTE3 , D.ATTRIBUTE4 , D.ATTRIBUTE5 , D.ATTRIBUTE6 , D.ATTRIBUTE7 , D.ATTRIBUTE8 , D.ATTRIBUTE9 , D.ATTRIBUTE10 , D.ATTRIBUTE11 , D.ATTRIBUTE12 , D.ATTRIBUTE13 , D.ATTRIBUTE14 , D.ATTRIBUTE15 , D.C_COLUMN1 , D.C_COLUMN2 , D.C_COLUMN3 , D.C_COLUMN4 , D.C_COLUMN5 , D.C_COLUMN6 , D.N_COLUMN1 , D.N_COLUMN2 , D.N_COLUMN3 , D.N_COLUMN4 , D.N_COLUMN5 , D.D_COLUMN1 , D.D_COLUMN2 , D.D_COLUMN3 , D.D_COLUMN4 , D.D_COLUMN5 , D.REQUIREMENT_DATE , D.ERROR_CODE , D.ERR_EXPLANATION , D.DEMAND_CLASS , D.DEMAND_SOURCE_TYPE , D.DEMAND_SOURCE_HEADER_ID , D.DEMAND_SOURCE_LINE , D.DEMAND_SOURCE_DELIVERY , D.DEMAND_SOURCE_NAME , D.LATEST_ACCEPTABLE_DATE , D.ATP_CALENDAR_ORGANIZATION_ID , D.PRIMARY_UOM_QUANTITY , D.SESSION_ID , D.SCHEDULE_GROUP_ID , TO_NUMBER(D.C_COLUMN4) , D.N_COLUMN3 , TO_NUMBER(D.C_COLUMN5) , D.SUPPLY_HEADER_ID , SUBSTR(D.C_COLUMN7, 1, 3) , D.CUSTOMER_ID , D.ACTION_CODE FROM BOM_RESOURCES R , BOM_DEPARTMENTS DEPT , MTL_DEMAND_INTERFACE D WHERE D.RESOURCE_ID IS NOT NULL /* ENSURE IT IS A RESOURCE */ AND R.RESOURCE_ID = D.RESOURCE_ID AND DEPT.DEPARTMENT_ID = D.DEPARTMENT_ID UNION ALL SELECT /* LINES */ D.ROWID , D.ATP_GROUP_ID , D.ORGANIZATION_ID , TO_NUMBER(D.C_COLUMN6) , D.INVENTORY_ITEM_ID , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , D.DEPARTMENT_ID , WL.LINE_CODE , WL.DESCRIPTION , 0-( D.DEPARTMENT_ID * 10) , D.LAST_UPDATE_DATE , D.LAST_UPDATED_BY , D.CREATION_DATE , D.CREATED_BY , D.LAST_UPDATE_LOGIN , D.ATP_RULE_ID , D.LINE_ITEM_QUANTITY , D.REQUEST_DATE_ATP_QUANTITY , D.EARLIEST_ATP_DATE , D.EARLIEST_ATP_DATE_QUANTITY , D.REQUEST_ATP_DATE , D.REQUEST_ATP_DATE_QUANTITY , D.GROUP_AVAILABLE_DATE , D.ACCUMULATION_WINDOW , D.ATP_LEAD_TIME , D.ATP_CHECK , D.INFINITE_TIME_FENCE_DATE , D.LINE_ITEM_UOM , D.GROUP_ATP_CHECK , D.USER_LINE_NUM , D.ATTRIBUTE_CATEGORY , D.ATTRIBUTE1 , D.ATTRIBUTE2 , D.ATTRIBUTE3 , D.ATTRIBUTE4 , D.ATTRIBUTE5 , D.ATTRIBUTE6 , D.ATTRIBUTE7 , D.ATTRIBUTE8 , D.ATTRIBUTE9 , D.ATTRIBUTE10 , D.ATTRIBUTE11 , D.ATTRIBUTE12 , D.ATTRIBUTE13 , D.ATTRIBUTE14 , D.ATTRIBUTE15 , D.C_COLUMN1 , D.C_COLUMN2 , D.C_COLUMN3 , D.C_COLUMN4 , D.C_COLUMN5 , D.C_COLUMN6 , D.N_COLUMN1 , D.N_COLUMN2 , D.N_COLUMN3 , D.N_COLUMN4 , D.N_COLUMN5 , D.D_COLUMN1 , D.D_COLUMN2 , D.D_COLUMN3 , D.D_COLUMN4 , D.D_COLUMN5 , D.REQUIREMENT_DATE , D.ERROR_CODE , D.ERR_EXPLANATION , D.DEMAND_CLASS , D.DEMAND_SOURCE_TYPE , D.DEMAND_SOURCE_HEADER_ID , D.DEMAND_SOURCE_LINE , D.DEMAND_SOURCE_DELIVERY , D.DEMAND_SOURCE_NAME , D.LATEST_ACCEPTABLE_DATE , D.ATP_CALENDAR_ORGANIZATION_ID , D.PRIMARY_UOM_QUANTITY , D.SESSION_ID , D.SCHEDULE_GROUP_ID , TO_NUMBER(D.C_COLUMN4) , D.N_COLUMN3 , TO_NUMBER(D.C_COLUMN5) , D.SUPPLY_HEADER_ID , SUBSTR(D.C_COLUMN7, 1, 3) , D.CUSTOMER_ID , D.ACTION_CODE FROM WIP_LINES WL , MTL_DEMAND_INTERFACE D WHERE D.RESOURCE_ID IS NULL /* ENSURE IT IS A LINE */ AND WL.ORGANIZATION_ID = D.ORGANIZATION_ID AND WL.LINE_ID = D.DEPARTMENT_ID UNION ALL SELECT /* MATERIALS */ D.ROWID ROW_ID , D.ATP_GROUP_ID , D.ORGANIZATION_ID , I3.INVENTORY_ITEM_ID , D.INVENTORY_ITEM_ID , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , I3.INVENTORY_ITEM_ID , D.LAST_UPDATE_DATE , D.LAST_UPDATED_BY , D.CREATION_DATE , D.CREATED_BY , D.LAST_UPDATE_LOGIN , DECODE(I2.BOM_ITEM_TYPE, 5, DECODE(I2.ATP_FLAG, 'N', DECODE(I2.ATP_COMPONENTS_FLAG, 'N', D.ATP_RULE_ID, 'Y', D.ATP_RULE_ID, NVL(I2.ATP_RULE_ID, NVL(MP.DEFAULT_ATP_RULE_ID, 0))), NVL(I2.ATP_RULE_ID, NVL(MP.DEFAULT_ATP_RULE_ID, 0)) ), D.ATP_RULE_ID) , D.LINE_ITEM_QUANTITY , D.REQUEST_DATE_ATP_QUANTITY , D.EARLIEST_ATP_DATE , D.EARLIEST_ATP_DATE_QUANTITY , D.REQUEST_ATP_DATE , D.REQUEST_ATP_DATE_QUANTITY , D.GROUP_AVAILABLE_DATE , D.ACCUMULATION_WINDOW , D.ATP_LEAD_TIME , D.ATP_CHECK , D.INFINITE_TIME_FENCE_DATE , D.LINE_ITEM_UOM , D.GROUP_ATP_CHECK , D.USER_LINE_NUM , D.ATTRIBUTE_CATEGORY , D.ATTRIBUTE1 , D.ATTRIBUTE2 , D.ATTRIBUTE3 , D.ATTRIBUTE4 , D.ATTRIBUTE5 , D.ATTRIBUTE6 , D.ATTRIBUTE7 , D.ATTRIBUTE8 , D.ATTRIBUTE9 , D.ATTRIBUTE10 , D.ATTRIBUTE11 , D.ATTRIBUTE12 , D.ATTRIBUTE13 , D.ATTRIBUTE14 , D.ATTRIBUTE15 , D.C_COLUMN1 , D.C_COLUMN2 , D.C_COLUMN3 , D.C_COLUMN4 , D.C_COLUMN5 , D.C_COLUMN6 , D.N_COLUMN1 , D.N_COLUMN2 , D.N_COLUMN3 , D.N_COLUMN4 , D.N_COLUMN5 , D.D_COLUMN1 , D.D_COLUMN2 , D.D_COLUMN3 , D.D_COLUMN4 , D.D_COLUMN5 , D.REQUIREMENT_DATE REQUEST_DATE , D.ERROR_CODE , D.ERR_EXPLANATION , D.DEMAND_CLASS , D.DEMAND_SOURCE_TYPE , D.DEMAND_SOURCE_HEADER_ID , D.DEMAND_SOURCE_LINE , D.DEMAND_SOURCE_DELIVERY , D.DEMAND_SOURCE_NAME , D.LATEST_ACCEPTABLE_DATE , D.ATP_CALENDAR_ORGANIZATION_ID , D.PRIMARY_UOM_QUANTITY , D.SESSION_ID , D.SCHEDULE_GROUP_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , D.SUPPLY_HEADER_ID , NULL , D.CUSTOMER_ID , D.ACTION_CODE FROM MTL_PARAMETERS MP , MTL_SYSTEM_ITEMS I3 , MTL_SYSTEM_ITEMS I2 , MTL_SYSTEM_ITEMS I1 , MTL_DEMAND_INTERFACE D WHERE D.DEPARTMENT_ID IS NULL AND I1.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID AND I1.ORGANIZATION_ID = D.ORGANIZATION_ID AND I2.INVENTORY_ITEM_ID = DECODE(I1.PRODUCT_FAMILY_ITEM_ID, NULL, I1.INVENTORY_ITEM_ID, I1.PRODUCT_FAMILY_ITEM_ID) AND I2.ORGANIZATION_ID = I1.ORGANIZATION_ID AND I3.INVENTORY_ITEM_ID = DECODE(I2.BOM_ITEM_TYPE, 5, DECODE(I2.ATP_FLAG, 'N', DECODE(I2.ATP_COMPONENTS_FLAG, 'N', D.INVENTORY_ITEM_ID, 'Y', D.INVENTORY_ITEM_ID, I2.INVENTORY_ITEM_ID), I2.INVENTORY_ITEM_ID), D.INVENTORY_ITEM_ID) AND I3.ORGANIZATION_ID = I2.ORGANIZATION_ID AND (NOT (I3.ATP_FLAG = 'N' AND I3.ATP_COMPONENTS_FLAG IN ('C', 'R'))) AND MP.ORGANIZATION_ID = D.ORGANIZATION_ID

View Text - HTML Formatted

SELECT /* RESOURCES */ D.ROWID
, D.ATP_GROUP_ID
, D.ORGANIZATION_ID
, TO_NUMBER(D.C_COLUMN6)
, D.INVENTORY_ITEM_ID
, D.DEPARTMENT_ID
, DEPT.DEPARTMENT_CODE
, DEPT.DESCRIPTION
, TO_NUMBER(D.C_COLUMN8)
, D.RESOURCE_ID
, R.RESOURCE_CODE
, R.DESCRIPTION
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, 0-( D.RESOURCE_ID * POWER(10
, LENGTH(TO_NUMBER(D.C_COLUMN8)) + 1) + TO_NUMBER(D.C_COLUMN8) * POWER (10
, 1) + LENGTH(TO_NUMBER(D.C_COLUMN8)))
, D.LAST_UPDATE_DATE
, D.LAST_UPDATED_BY
, D.CREATION_DATE
, D.CREATED_BY
, D.LAST_UPDATE_LOGIN
, D.ATP_RULE_ID
, D.LINE_ITEM_QUANTITY
, D.REQUEST_DATE_ATP_QUANTITY
, D.EARLIEST_ATP_DATE
, D.EARLIEST_ATP_DATE_QUANTITY
, D.REQUEST_ATP_DATE
, D.REQUEST_ATP_DATE_QUANTITY
, D.GROUP_AVAILABLE_DATE
, D.ACCUMULATION_WINDOW
, D.ATP_LEAD_TIME
, D.ATP_CHECK
, D.INFINITE_TIME_FENCE_DATE
, D.LINE_ITEM_UOM
, D.GROUP_ATP_CHECK
, D.USER_LINE_NUM
, D.ATTRIBUTE_CATEGORY
, D.ATTRIBUTE1
, D.ATTRIBUTE2
, D.ATTRIBUTE3
, D.ATTRIBUTE4
, D.ATTRIBUTE5
, D.ATTRIBUTE6
, D.ATTRIBUTE7
, D.ATTRIBUTE8
, D.ATTRIBUTE9
, D.ATTRIBUTE10
, D.ATTRIBUTE11
, D.ATTRIBUTE12
, D.ATTRIBUTE13
, D.ATTRIBUTE14
, D.ATTRIBUTE15
, D.C_COLUMN1
, D.C_COLUMN2
, D.C_COLUMN3
, D.C_COLUMN4
, D.C_COLUMN5
, D.C_COLUMN6
, D.N_COLUMN1
, D.N_COLUMN2
, D.N_COLUMN3
, D.N_COLUMN4
, D.N_COLUMN5
, D.D_COLUMN1
, D.D_COLUMN2
, D.D_COLUMN3
, D.D_COLUMN4
, D.D_COLUMN5
, D.REQUIREMENT_DATE
, D.ERROR_CODE
, D.ERR_EXPLANATION
, D.DEMAND_CLASS
, D.DEMAND_SOURCE_TYPE
, D.DEMAND_SOURCE_HEADER_ID
, D.DEMAND_SOURCE_LINE
, D.DEMAND_SOURCE_DELIVERY
, D.DEMAND_SOURCE_NAME
, D.LATEST_ACCEPTABLE_DATE
, D.ATP_CALENDAR_ORGANIZATION_ID
, D.PRIMARY_UOM_QUANTITY
, D.SESSION_ID
, D.SCHEDULE_GROUP_ID
, TO_NUMBER(D.C_COLUMN4)
, D.N_COLUMN3
, TO_NUMBER(D.C_COLUMN5)
, D.SUPPLY_HEADER_ID
, SUBSTR(D.C_COLUMN7
, 1
, 3)
, D.CUSTOMER_ID
, D.ACTION_CODE
FROM BOM_RESOURCES R
, BOM_DEPARTMENTS DEPT
, MTL_DEMAND_INTERFACE D
WHERE D.RESOURCE_ID IS NOT NULL /* ENSURE IT IS A RESOURCE */
AND R.RESOURCE_ID = D.RESOURCE_ID
AND DEPT.DEPARTMENT_ID = D.DEPARTMENT_ID UNION ALL SELECT /* LINES */ D.ROWID
, D.ATP_GROUP_ID
, D.ORGANIZATION_ID
, TO_NUMBER(D.C_COLUMN6)
, D.INVENTORY_ITEM_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, D.DEPARTMENT_ID
, WL.LINE_CODE
, WL.DESCRIPTION
, 0-( D.DEPARTMENT_ID * 10)
, D.LAST_UPDATE_DATE
, D.LAST_UPDATED_BY
, D.CREATION_DATE
, D.CREATED_BY
, D.LAST_UPDATE_LOGIN
, D.ATP_RULE_ID
, D.LINE_ITEM_QUANTITY
, D.REQUEST_DATE_ATP_QUANTITY
, D.EARLIEST_ATP_DATE
, D.EARLIEST_ATP_DATE_QUANTITY
, D.REQUEST_ATP_DATE
, D.REQUEST_ATP_DATE_QUANTITY
, D.GROUP_AVAILABLE_DATE
, D.ACCUMULATION_WINDOW
, D.ATP_LEAD_TIME
, D.ATP_CHECK
, D.INFINITE_TIME_FENCE_DATE
, D.LINE_ITEM_UOM
, D.GROUP_ATP_CHECK
, D.USER_LINE_NUM
, D.ATTRIBUTE_CATEGORY
, D.ATTRIBUTE1
, D.ATTRIBUTE2
, D.ATTRIBUTE3
, D.ATTRIBUTE4
, D.ATTRIBUTE5
, D.ATTRIBUTE6
, D.ATTRIBUTE7
, D.ATTRIBUTE8
, D.ATTRIBUTE9
, D.ATTRIBUTE10
, D.ATTRIBUTE11
, D.ATTRIBUTE12
, D.ATTRIBUTE13
, D.ATTRIBUTE14
, D.ATTRIBUTE15
, D.C_COLUMN1
, D.C_COLUMN2
, D.C_COLUMN3
, D.C_COLUMN4
, D.C_COLUMN5
, D.C_COLUMN6
, D.N_COLUMN1
, D.N_COLUMN2
, D.N_COLUMN3
, D.N_COLUMN4
, D.N_COLUMN5
, D.D_COLUMN1
, D.D_COLUMN2
, D.D_COLUMN3
, D.D_COLUMN4
, D.D_COLUMN5
, D.REQUIREMENT_DATE
, D.ERROR_CODE
, D.ERR_EXPLANATION
, D.DEMAND_CLASS
, D.DEMAND_SOURCE_TYPE
, D.DEMAND_SOURCE_HEADER_ID
, D.DEMAND_SOURCE_LINE
, D.DEMAND_SOURCE_DELIVERY
, D.DEMAND_SOURCE_NAME
, D.LATEST_ACCEPTABLE_DATE
, D.ATP_CALENDAR_ORGANIZATION_ID
, D.PRIMARY_UOM_QUANTITY
, D.SESSION_ID
, D.SCHEDULE_GROUP_ID
, TO_NUMBER(D.C_COLUMN4)
, D.N_COLUMN3
, TO_NUMBER(D.C_COLUMN5)
, D.SUPPLY_HEADER_ID
, SUBSTR(D.C_COLUMN7
, 1
, 3)
, D.CUSTOMER_ID
, D.ACTION_CODE
FROM WIP_LINES WL
, MTL_DEMAND_INTERFACE D
WHERE D.RESOURCE_ID IS NULL /* ENSURE IT IS A LINE */
AND WL.ORGANIZATION_ID = D.ORGANIZATION_ID
AND WL.LINE_ID = D.DEPARTMENT_ID UNION ALL SELECT /* MATERIALS */ D.ROWID ROW_ID
, D.ATP_GROUP_ID
, D.ORGANIZATION_ID
, I3.INVENTORY_ITEM_ID
, D.INVENTORY_ITEM_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, I3.INVENTORY_ITEM_ID
, D.LAST_UPDATE_DATE
, D.LAST_UPDATED_BY
, D.CREATION_DATE
, D.CREATED_BY
, D.LAST_UPDATE_LOGIN
, DECODE(I2.BOM_ITEM_TYPE
, 5
, DECODE(I2.ATP_FLAG
, 'N'
, DECODE(I2.ATP_COMPONENTS_FLAG
, 'N'
, D.ATP_RULE_ID
, 'Y'
, D.ATP_RULE_ID
, NVL(I2.ATP_RULE_ID
, NVL(MP.DEFAULT_ATP_RULE_ID
, 0)))
, NVL(I2.ATP_RULE_ID
, NVL(MP.DEFAULT_ATP_RULE_ID
, 0)) )
, D.ATP_RULE_ID)
, D.LINE_ITEM_QUANTITY
, D.REQUEST_DATE_ATP_QUANTITY
, D.EARLIEST_ATP_DATE
, D.EARLIEST_ATP_DATE_QUANTITY
, D.REQUEST_ATP_DATE
, D.REQUEST_ATP_DATE_QUANTITY
, D.GROUP_AVAILABLE_DATE
, D.ACCUMULATION_WINDOW
, D.ATP_LEAD_TIME
, D.ATP_CHECK
, D.INFINITE_TIME_FENCE_DATE
, D.LINE_ITEM_UOM
, D.GROUP_ATP_CHECK
, D.USER_LINE_NUM
, D.ATTRIBUTE_CATEGORY
, D.ATTRIBUTE1
, D.ATTRIBUTE2
, D.ATTRIBUTE3
, D.ATTRIBUTE4
, D.ATTRIBUTE5
, D.ATTRIBUTE6
, D.ATTRIBUTE7
, D.ATTRIBUTE8
, D.ATTRIBUTE9
, D.ATTRIBUTE10
, D.ATTRIBUTE11
, D.ATTRIBUTE12
, D.ATTRIBUTE13
, D.ATTRIBUTE14
, D.ATTRIBUTE15
, D.C_COLUMN1
, D.C_COLUMN2
, D.C_COLUMN3
, D.C_COLUMN4
, D.C_COLUMN5
, D.C_COLUMN6
, D.N_COLUMN1
, D.N_COLUMN2
, D.N_COLUMN3
, D.N_COLUMN4
, D.N_COLUMN5
, D.D_COLUMN1
, D.D_COLUMN2
, D.D_COLUMN3
, D.D_COLUMN4
, D.D_COLUMN5
, D.REQUIREMENT_DATE REQUEST_DATE
, D.ERROR_CODE
, D.ERR_EXPLANATION
, D.DEMAND_CLASS
, D.DEMAND_SOURCE_TYPE
, D.DEMAND_SOURCE_HEADER_ID
, D.DEMAND_SOURCE_LINE
, D.DEMAND_SOURCE_DELIVERY
, D.DEMAND_SOURCE_NAME
, D.LATEST_ACCEPTABLE_DATE
, D.ATP_CALENDAR_ORGANIZATION_ID
, D.PRIMARY_UOM_QUANTITY
, D.SESSION_ID
, D.SCHEDULE_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, D.SUPPLY_HEADER_ID
, NULL
, D.CUSTOMER_ID
, D.ACTION_CODE
FROM MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS I3
, MTL_SYSTEM_ITEMS I2
, MTL_SYSTEM_ITEMS I1
, MTL_DEMAND_INTERFACE D
WHERE D.DEPARTMENT_ID IS NULL
AND I1.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID
AND I1.ORGANIZATION_ID = D.ORGANIZATION_ID
AND I2.INVENTORY_ITEM_ID = DECODE(I1.PRODUCT_FAMILY_ITEM_ID
, NULL
, I1.INVENTORY_ITEM_ID
, I1.PRODUCT_FAMILY_ITEM_ID)
AND I2.ORGANIZATION_ID = I1.ORGANIZATION_ID
AND I3.INVENTORY_ITEM_ID = DECODE(I2.BOM_ITEM_TYPE
, 5
, DECODE(I2.ATP_FLAG
, 'N'
, DECODE(I2.ATP_COMPONENTS_FLAG
, 'N'
, D.INVENTORY_ITEM_ID
, 'Y'
, D.INVENTORY_ITEM_ID
, I2.INVENTORY_ITEM_ID)
, I2.INVENTORY_ITEM_ID)
, D.INVENTORY_ITEM_ID)
AND I3.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND (NOT (I3.ATP_FLAG = 'N'
AND I3.ATP_COMPONENTS_FLAG IN ('C'
, 'R')))
AND MP.ORGANIZATION_ID = D.ORGANIZATION_ID