eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MRP_ATP_V

Source

View Text - Preformatted


SELECT /* RESOURCES */ D.ROWID , D.ATP_GROUP_ID , D.ORGANIZATION_ID , I3.INVENTORY_ITEM_ID , D.INVENTORY_ITEM_ID , DR.DEPARTMENT_ID , DEPT.DEPARTMENT_CODE , DEPT.DESCRIPTION , DR2.DEPARTMENT_ID , DR.RESOURCE_ID , R.RESOURCE_CODE , R.DESCRIPTION , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , 0-( DR.RESOURCE_ID * POWER(10, LENGTH(DR2.DEPARTMENT_ID) + 1) + DR2.DEPARTMENT_ID * POWER (10, 1) + LENGTH(DR2.DEPARTMENT_ID)) , D.LAST_UPDATE_DATE , D.LAST_UPDATED_BY , D.CREATION_DATE , D.CREATED_BY , D.LAST_UPDATE_LOGIN , NVL(DR2.ATP_RULE_ID, NVL(MP.DEFAULT_ATP_RULE_ID, 0)) ,NVL( DECODE(BR.BASIS_TYPE, 1, (NVL(D.PRIMARY_UOM_QUANTITY, D.LINE_ITEM_QUANTITY)*BR.USAGE_RATE_OR_AMOUNT), 2, BR.USAGE_RATE_OR_AMOUNT)*MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0) , 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 , MUC1.UOM_CODE , 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 , C1.CALENDAR_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 ,NVL( DECODE(BR.BASIS_TYPE, 1, (NVL(D.PRIMARY_UOM_QUANTITY, D.LINE_ITEM_QUANTITY) *BR.USAGE_RATE_OR_AMOUNT), 2, BR.USAGE_RATE_OR_AMOUNT) *MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0) , D.SESSION_ID , D.SCHEDULE_GROUP_ID , BR.BASIS_TYPE , NVL(BR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0) , DR2.AVAILABLE_24_HOURS_FLAG , D.SUPPLY_HEADER_ID , R.UNIT_OF_MEASURE , D.SHIP_METHOD , D.INTRANSIT_LEAD_TIME , D.VENDOR_ID , D.VENDOR_SITE_ID , D.CUSTOMER_ID , D.LOCK_FLAG , D.ACTION_CODE , D.TRANSACTION_MODE , D.PROCESS_FLAG FROM BOM_RESOURCES R , BOM_DEPARTMENTS DEPT , BOM_DEPARTMENT_RESOURCES DR2 /* this is the owning dept */ , BOM_DEPARTMENT_RESOURCES DR /* this is the sharing dept */ , BOM_CALENDAR_DATES C2 , BOM_CALENDAR_DATES C1 , BOM_CALENDAR_DATES C , MTL_UOM_CONVERSIONS MUC2 , MTL_UOM_CONVERSIONS MUC1 , BOM_OPERATION_RESOURCES BR , BOM_OPERATION_SEQUENCES SEQ , BOM_OPERATIONAL_ROUTINGS RTG , MTL_PARAMETERS MP , MTL_SYSTEM_ITEMS I3 , MTL_SYSTEM_ITEMS I2 , MTL_SYSTEM_ITEMS I1 , MTL_DEMAND_INTERFACE D WHERE 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 I3.ATP_COMPONENTS_FLAG IN ('R', 'C') AND MP.ORGANIZATION_ID = D.ORGANIZATION_ID AND RTG.ASSEMBLY_ITEM_ID = I3.INVENTORY_ITEM_ID AND RTG.ORGANIZATION_ID = I3.ORGANIZATION_ID AND NVL(RTG.CTP_FLAG, 2) = 1 AND SEQ.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID AND TRUNC(NVL(SEQ.DISABLE_DATE, C2.CALENDAR_DATE+1)) > TRUNC(C2.CALENDAR_DATE) AND TRUNC(SEQ.EFFECTIVITY_DATE) <= TRUNC(C2.CALENDAR_DATE) AND ((SEQ.OPTION_DEPENDENT_FLAG = 2) OR (SEQ.OPTION_DEPENDENT_FLAG = 1 AND EXISTS( SELECT 'OPTION FOR THIS OPERATION EXISTS' FROM BOM_BILL_OF_MATERIALS BOM, BOM_INVENTORY_COMPONENTS BIC, MTL_DEMAND_INTERFACE MDI2 WHERE MDI2.ATP_GROUP_ID = D.ATP_GROUP_ID AND BIC.COMPONENT_ITEM_ID = MDI2.INVENTORY_ITEM_ID AND TRUNC(BIC.EFFECTIVITY_DATE) <= TRUNC(C2.CALENDAR_DATE) AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID AND BOM.ASSEMBLY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID AND BOM.ORGANIZATION_ID = RTG.ORGANIZATION_ID AND BIC.OPERATION_SEQ_NUM = SEQ.OPERATION_SEQ_NUM))) AND BR.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID AND BR.BASIS_TYPE IN (1,2) AND DR.RESOURCE_ID = BR.RESOURCE_ID AND R.RESOURCE_ID = DR.RESOURCE_ID AND MUC1.UOM_CODE = FND_PROFILE.VALUE_SPECIFIC('BOM:HOUR_UOM_CODE') AND MUC1.INVENTORY_ITEM_ID = 0 AND MUC2.UOM_CLASS = MUC1.UOM_CLASS AND MUC2.INVENTORY_ITEM_ID = 0 AND MUC2.UOM_CODE = R.UNIT_OF_MEASURE AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE) AND C.CALENDAR_CODE = MP.CALENDAR_CODE AND C.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID AND C1.SEQ_NUM = C.PRIOR_SEQ_NUM - CEIL((NVL(I3.FIXED_LEAD_TIME,0)+ NVL(I3.VARIABLE_LEAD_TIME, 0)* NVL(D.PRIMARY_UOM_QUANTITY,D.LINE_ITEM_QUANTITY))* (100-NVL(BR.RESOURCE_OFFSET_PERCENT, 0))/100) AND C1.CALENDAR_CODE = MP.CALENDAR_CODE AND C1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID AND ((I3.BOM_ITEM_TYPE = 1 AND C2.SEQ_NUM = C.PRIOR_SEQ_NUM - CEIL(NVL(I3.FIXED_LEAD_TIME,0) + NVL(I3.VARIABLE_LEAD_TIME,0)* NVL(D.PRIMARY_UOM_QUANTITY,D.LINE_ITEM_QUANTITY))) OR (I3.BOM_ITEM_TYPE !=1 AND C2.SEQ_NUM = C.PRIOR_SEQ_NUM -NVL(D.ATP_LEAD_TIME, 0))) AND C2.CALENDAR_CODE = MP.CALENDAR_CODE AND C2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID AND DR.DEPARTMENT_ID = SEQ.DEPARTMENT_ID AND DR2.DEPARTMENT_ID = NVL(DR.SHARE_FROM_DEPT_ID, DR.DEPARTMENT_ID) AND DR2.RESOURCE_ID = DR.RESOURCE_ID AND DEPT.DEPARTMENT_ID = DR.DEPARTMENT_ID AND NVL(DR2.CTP_FLAG, 2) = 1 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.SHIP_METHOD , D.INTRANSIT_LEAD_TIME , D.VENDOR_ID , D.VENDOR_SITE_ID , D.CUSTOMER_ID , D.LOCK_FLAG , D.ACTION_CODE , D.TRANSACTION_MODE , D.PROCESS_FLAG FROM MTL_PARAMETERS MP , MTL_SYSTEM_ITEMS I3 , MTL_SYSTEM_ITEMS I2 , MTL_SYSTEM_ITEMS I1 , MTL_DEMAND_INTERFACE D WHERE 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 UNION ALL SELECT /* Line */ D.ROWID , 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) , WL.LINE_ID , WL.LINE_CODE , WL.DESCRIPTION , 0- WL.LINE_ID*10 , D.LAST_UPDATE_DATE , D.LAST_UPDATED_BY , D.CREATION_DATE , D.CREATED_BY , D.LAST_UPDATE_LOGIN , NVL(WL.ATP_RULE_ID, NVL(MP.DEFAULT_ATP_RULE_ID, 0)) , NVL(D.PRIMARY_UOM_QUANTITY, 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 , I3.PRIMARY_UOM_CODE , 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 , C1.CALENDAR_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 , NVL(D.PRIMARY_UOM_QUANTITY, D.LINE_ITEM_QUANTITY) , D.SESSION_ID , D.SCHEDULE_GROUP_ID , 1 , 1 , to_number(null) , D.SUPPLY_HEADER_ID , I3.PRIMARY_UOM_CODE , D.SHIP_METHOD , D.INTRANSIT_LEAD_TIME , D.VENDOR_ID , D.VENDOR_SITE_ID , D.CUSTOMER_ID , D.LOCK_FLAG , D.ACTION_CODE , D.TRANSACTION_MODE , D.PROCESS_FLAG FROM WIP_LINES WL , BOM_OPERATIONAL_ROUTINGS RTG , BOM_CALENDAR_DATES C1 , BOM_CALENDAR_DATES C , MTL_PARAMETERS MP , MTL_SYSTEM_ITEMS I3 , MTL_SYSTEM_ITEMS I2 , MTL_SYSTEM_ITEMS I1 , MTL_DEMAND_INTERFACE D WHERE 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 I3.ATP_COMPONENTS_FLAG IN ('R', 'C') AND MP.ORGANIZATION_ID = D.ORGANIZATION_ID AND RTG.ASSEMBLY_ITEM_ID = I3.INVENTORY_ITEM_ID AND RTG.ORGANIZATION_ID = I3.ORGANIZATION_ID AND NVL(RTG.CTP_FLAG,2) = 1 AND WL.LINE_ID = RTG.LINE_ID AND WL.ORGANIZATION_ID = RTG.ORGANIZATION_ID AND TRUNC(NVL(WL.DISABLE_DATE, C1.CALENDAR_DATE+1)) > TRUNC(C1.CALENDAR_DATE) AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE) AND C.CALENDAR_CODE = MP.CALENDAR_CODE AND C.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID AND C1.SEQ_NUM = C.PRIOR_SEQ_NUM -CEIL(NVL(I3.FIXED_LEAD_TIME, 0)+ NVL(I3.VARIABLE_LEAD_TIME, 0)* NVL(D.PRIMARY_UOM_QUANTITY, D.LINE_ITEM_QUANTITY)) AND C1.CALENDAR_CODE = MP.CALENDAR_CODE AND C1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID

View Text - HTML Formatted

SELECT /* RESOURCES */ D.ROWID
, D.ATP_GROUP_ID
, D.ORGANIZATION_ID
, I3.INVENTORY_ITEM_ID
, D.INVENTORY_ITEM_ID
, DR.DEPARTMENT_ID
, DEPT.DEPARTMENT_CODE
, DEPT.DESCRIPTION
, DR2.DEPARTMENT_ID
, DR.RESOURCE_ID
, R.RESOURCE_CODE
, R.DESCRIPTION
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, 0-( DR.RESOURCE_ID * POWER(10
, LENGTH(DR2.DEPARTMENT_ID) + 1) + DR2.DEPARTMENT_ID * POWER (10
, 1) + LENGTH(DR2.DEPARTMENT_ID))
, D.LAST_UPDATE_DATE
, D.LAST_UPDATED_BY
, D.CREATION_DATE
, D.CREATED_BY
, D.LAST_UPDATE_LOGIN
, NVL(DR2.ATP_RULE_ID
, NVL(MP.DEFAULT_ATP_RULE_ID
, 0))
, NVL( DECODE(BR.BASIS_TYPE
, 1
, (NVL(D.PRIMARY_UOM_QUANTITY
, D.LINE_ITEM_QUANTITY)*BR.USAGE_RATE_OR_AMOUNT)
, 2
, BR.USAGE_RATE_OR_AMOUNT)*MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE
, 0)
, 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
, MUC1.UOM_CODE
, 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
, C1.CALENDAR_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
, NVL( DECODE(BR.BASIS_TYPE
, 1
, (NVL(D.PRIMARY_UOM_QUANTITY
, D.LINE_ITEM_QUANTITY) *BR.USAGE_RATE_OR_AMOUNT)
, 2
, BR.USAGE_RATE_OR_AMOUNT) *MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE
, 0)
, D.SESSION_ID
, D.SCHEDULE_GROUP_ID
, BR.BASIS_TYPE
, NVL(BR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE
, 0)
, DR2.AVAILABLE_24_HOURS_FLAG
, D.SUPPLY_HEADER_ID
, R.UNIT_OF_MEASURE
, D.SHIP_METHOD
, D.INTRANSIT_LEAD_TIME
, D.VENDOR_ID
, D.VENDOR_SITE_ID
, D.CUSTOMER_ID
, D.LOCK_FLAG
, D.ACTION_CODE
, D.TRANSACTION_MODE
, D.PROCESS_FLAG
FROM BOM_RESOURCES R
, BOM_DEPARTMENTS DEPT
, BOM_DEPARTMENT_RESOURCES DR2 /* THIS IS THE OWNING DEPT */
, BOM_DEPARTMENT_RESOURCES DR /* THIS IS THE SHARING DEPT */
, BOM_CALENDAR_DATES C2
, BOM_CALENDAR_DATES C1
, BOM_CALENDAR_DATES C
, MTL_UOM_CONVERSIONS MUC2
, MTL_UOM_CONVERSIONS MUC1
, BOM_OPERATION_RESOURCES BR
, BOM_OPERATION_SEQUENCES SEQ
, BOM_OPERATIONAL_ROUTINGS RTG
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS I3
, MTL_SYSTEM_ITEMS I2
, MTL_SYSTEM_ITEMS I1
, MTL_DEMAND_INTERFACE D
WHERE 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 I3.ATP_COMPONENTS_FLAG IN ('R'
, 'C')
AND MP.ORGANIZATION_ID = D.ORGANIZATION_ID
AND RTG.ASSEMBLY_ITEM_ID = I3.INVENTORY_ITEM_ID
AND RTG.ORGANIZATION_ID = I3.ORGANIZATION_ID
AND NVL(RTG.CTP_FLAG
, 2) = 1
AND SEQ.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
AND TRUNC(NVL(SEQ.DISABLE_DATE
, C2.CALENDAR_DATE+1)) > TRUNC(C2.CALENDAR_DATE)
AND TRUNC(SEQ.EFFECTIVITY_DATE) <= TRUNC(C2.CALENDAR_DATE)
AND ((SEQ.OPTION_DEPENDENT_FLAG = 2) OR (SEQ.OPTION_DEPENDENT_FLAG = 1
AND EXISTS( SELECT 'OPTION FOR THIS OPERATION EXISTS'
FROM BOM_BILL_OF_MATERIALS BOM
, BOM_INVENTORY_COMPONENTS BIC
, MTL_DEMAND_INTERFACE MDI2
WHERE MDI2.ATP_GROUP_ID = D.ATP_GROUP_ID
AND BIC.COMPONENT_ITEM_ID = MDI2.INVENTORY_ITEM_ID
AND TRUNC(BIC.EFFECTIVITY_DATE) <= TRUNC(C2.CALENDAR_DATE)
AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BOM.ASSEMBLY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
AND BOM.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND BIC.OPERATION_SEQ_NUM = SEQ.OPERATION_SEQ_NUM)))
AND BR.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND BR.BASIS_TYPE IN (1
, 2)
AND DR.RESOURCE_ID = BR.RESOURCE_ID
AND R.RESOURCE_ID = DR.RESOURCE_ID
AND MUC1.UOM_CODE = FND_PROFILE.VALUE_SPECIFIC('BOM:HOUR_UOM_CODE')
AND MUC1.INVENTORY_ITEM_ID = 0
AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
AND MUC2.INVENTORY_ITEM_ID = 0
AND MUC2.UOM_CODE = R.UNIT_OF_MEASURE
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
AND C.CALENDAR_CODE = MP.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND C1.SEQ_NUM = C.PRIOR_SEQ_NUM - CEIL((NVL(I3.FIXED_LEAD_TIME
, 0)+ NVL(I3.VARIABLE_LEAD_TIME
, 0)* NVL(D.PRIMARY_UOM_QUANTITY
, D.LINE_ITEM_QUANTITY))* (100-NVL(BR.RESOURCE_OFFSET_PERCENT
, 0))/100)
AND C1.CALENDAR_CODE = MP.CALENDAR_CODE
AND C1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND ((I3.BOM_ITEM_TYPE = 1
AND C2.SEQ_NUM = C.PRIOR_SEQ_NUM - CEIL(NVL(I3.FIXED_LEAD_TIME
, 0) + NVL(I3.VARIABLE_LEAD_TIME
, 0)* NVL(D.PRIMARY_UOM_QUANTITY
, D.LINE_ITEM_QUANTITY))) OR (I3.BOM_ITEM_TYPE !=1
AND C2.SEQ_NUM = C.PRIOR_SEQ_NUM -NVL(D.ATP_LEAD_TIME
, 0)))
AND C2.CALENDAR_CODE = MP.CALENDAR_CODE
AND C2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND DR.DEPARTMENT_ID = SEQ.DEPARTMENT_ID
AND DR2.DEPARTMENT_ID = NVL(DR.SHARE_FROM_DEPT_ID
, DR.DEPARTMENT_ID)
AND DR2.RESOURCE_ID = DR.RESOURCE_ID
AND DEPT.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND NVL(DR2.CTP_FLAG
, 2) = 1 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.SHIP_METHOD
, D.INTRANSIT_LEAD_TIME
, D.VENDOR_ID
, D.VENDOR_SITE_ID
, D.CUSTOMER_ID
, D.LOCK_FLAG
, D.ACTION_CODE
, D.TRANSACTION_MODE
, D.PROCESS_FLAG
FROM MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS I3
, MTL_SYSTEM_ITEMS I2
, MTL_SYSTEM_ITEMS I1
, MTL_DEMAND_INTERFACE D
WHERE 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 UNION ALL SELECT /* LINE */ D.ROWID
, 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)
, WL.LINE_ID
, WL.LINE_CODE
, WL.DESCRIPTION
, 0- WL.LINE_ID*10
, D.LAST_UPDATE_DATE
, D.LAST_UPDATED_BY
, D.CREATION_DATE
, D.CREATED_BY
, D.LAST_UPDATE_LOGIN
, NVL(WL.ATP_RULE_ID
, NVL(MP.DEFAULT_ATP_RULE_ID
, 0))
, NVL(D.PRIMARY_UOM_QUANTITY
, 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
, I3.PRIMARY_UOM_CODE
, 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
, C1.CALENDAR_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
, NVL(D.PRIMARY_UOM_QUANTITY
, D.LINE_ITEM_QUANTITY)
, D.SESSION_ID
, D.SCHEDULE_GROUP_ID
, 1
, 1
, TO_NUMBER(NULL)
, D.SUPPLY_HEADER_ID
, I3.PRIMARY_UOM_CODE
, D.SHIP_METHOD
, D.INTRANSIT_LEAD_TIME
, D.VENDOR_ID
, D.VENDOR_SITE_ID
, D.CUSTOMER_ID
, D.LOCK_FLAG
, D.ACTION_CODE
, D.TRANSACTION_MODE
, D.PROCESS_FLAG
FROM WIP_LINES WL
, BOM_OPERATIONAL_ROUTINGS RTG
, BOM_CALENDAR_DATES C1
, BOM_CALENDAR_DATES C
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS I3
, MTL_SYSTEM_ITEMS I2
, MTL_SYSTEM_ITEMS I1
, MTL_DEMAND_INTERFACE D
WHERE 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 I3.ATP_COMPONENTS_FLAG IN ('R'
, 'C')
AND MP.ORGANIZATION_ID = D.ORGANIZATION_ID
AND RTG.ASSEMBLY_ITEM_ID = I3.INVENTORY_ITEM_ID
AND RTG.ORGANIZATION_ID = I3.ORGANIZATION_ID
AND NVL(RTG.CTP_FLAG
, 2) = 1
AND WL.LINE_ID = RTG.LINE_ID
AND WL.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND TRUNC(NVL(WL.DISABLE_DATE
, C1.CALENDAR_DATE+1)) > TRUNC(C1.CALENDAR_DATE)
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
AND C.CALENDAR_CODE = MP.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND C1.SEQ_NUM = C.PRIOR_SEQ_NUM -CEIL(NVL(I3.FIXED_LEAD_TIME
, 0)+ NVL(I3.VARIABLE_LEAD_TIME
, 0)* NVL(D.PRIMARY_UOM_QUANTITY
, D.LINE_ITEM_QUANTITY))
AND C1.CALENDAR_CODE = MP.CALENDAR_CODE
AND C1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID