VIEW: APPS.MRP_ATP_ITEMS_RESOURCE_V

Source

View Text - Preformatted

SELECT DISTINCT V.ATP_GROUP_ID ATP_GROUP_ID , V.ORGANIZATION_ID ORGANIZATION_ID , RTG.ASSEMBLY_ITEM_ID INVENTORY_ITEM_ID , V.OWNING_DEPARTMENT_ID DEPARTMENT_ID , V.RESOURCE_ID RESOURCE_ID , V.LINE_ID LINE_ID , V.ENTITY_ID ENTITY_ID , V.ATP_RULE_ID ATP_RULE_ID , V.AVAILABLE_TO_ATP AVAILABLE_TO_ATP , V.ATP_CALENDAR_ORGANIZATION_ID ATP_CALENDAR_ORGANIZATION_ID , V.N_COLUMN1 N_COLUMN1 , V.DEMAND_CLASS DEMAND_CLASS , V.RESOURCE_BASIS_TYPE RESOURCE_BASIS_TYPE , NVL(BR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE /MUC1.CONVERSION_RATE, 0) RESOURCE_USAGE_RATE_OR_AMOUNT , BR.RESOURCE_OFFSET_PERCENT/100 , V.AVAILABLE_24_HOURS_FLAG AVAILABLE_24_HOURS_FLAG , SEQ.OPERATION_SEQ_NUM OPERATION_SEQ_NUM , SEQ.OPTION_DEPENDENT_FLAG OPTION_DEPENDENT_FLAG FROM BOM_DEPARTMENT_RESOURCES DR , BOM_OPERATIONAL_ROUTINGS RTG , BOM_OPERATION_SEQUENCES SEQ , BOM_OPERATION_RESOURCES BR , BOM_CALENDAR_DATES C1 , BOM_CALENDAR_DATES C , MTL_PARAMETERS MP , MTL_UOM_CONVERSIONS MUC2 , MTL_UOM_CONVERSIONS MUC1 , MRP_ATP_BASE_V V WHERE BR.RESOURCE_ID = V.RESOURCE_ID AND BR.BASIS_TYPE IN (1,2) AND SEQ.OPERATION_SEQUENCE_ID = BR.OPERATION_SEQUENCE_ID AND DR.RESOURCE_ID = V.RESOURCE_ID AND NVL(DR.SHARE_FROM_DEPT_ID, DR.DEPARTMENT_ID) = V.OWNING_DEPARTMENT_ID AND SEQ.DEPARTMENT_ID = DR.DEPARTMENT_ID AND TRUNC(NVL(SEQ.DISABLE_DATE, C1.CALENDAR_DATE+1)) > TRUNC(C1.CALENDAR_DATE) AND TRUNC(SEQ.EFFECTIVITY_DATE) <= TRUNC(C1.CALENDAR_DATE) AND RTG.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID AND NVL(RTG.CTP_FLAG, 2) = 1 AND MUC1.UOM_CODE = V.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 = V.RES_UOM AND MP.ORGANIZATION_ID = V.ORGANIZATION_ID AND C.CALENDAR_DATE = TRUNC(V.REQUEST_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 - NVL(V.ATP_LEAD_TIME, 0) AND C1.CALENDAR_CODE = MP.CALENDAR_CODE AND C1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID

View Text - HTML Formatted

SELECT DISTINCT V.ATP_GROUP_ID ATP_GROUP_ID
, V.ORGANIZATION_ID ORGANIZATION_ID
, RTG.ASSEMBLY_ITEM_ID INVENTORY_ITEM_ID
, V.OWNING_DEPARTMENT_ID DEPARTMENT_ID
, V.RESOURCE_ID RESOURCE_ID
, V.LINE_ID LINE_ID
, V.ENTITY_ID ENTITY_ID
, V.ATP_RULE_ID ATP_RULE_ID
, V.AVAILABLE_TO_ATP AVAILABLE_TO_ATP
, V.ATP_CALENDAR_ORGANIZATION_ID ATP_CALENDAR_ORGANIZATION_ID
, V.N_COLUMN1 N_COLUMN1
, V.DEMAND_CLASS DEMAND_CLASS
, V.RESOURCE_BASIS_TYPE RESOURCE_BASIS_TYPE
, NVL(BR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE /MUC1.CONVERSION_RATE
, 0) RESOURCE_USAGE_RATE_OR_AMOUNT
, BR.RESOURCE_OFFSET_PERCENT/100
, V.AVAILABLE_24_HOURS_FLAG AVAILABLE_24_HOURS_FLAG
, SEQ.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
, SEQ.OPTION_DEPENDENT_FLAG OPTION_DEPENDENT_FLAG
FROM BOM_DEPARTMENT_RESOURCES DR
, BOM_OPERATIONAL_ROUTINGS RTG
, BOM_OPERATION_SEQUENCES SEQ
, BOM_OPERATION_RESOURCES BR
, BOM_CALENDAR_DATES C1
, BOM_CALENDAR_DATES C
, MTL_PARAMETERS MP
, MTL_UOM_CONVERSIONS MUC2
, MTL_UOM_CONVERSIONS MUC1
, MRP_ATP_BASE_V V
WHERE BR.RESOURCE_ID = V.RESOURCE_ID
AND BR.BASIS_TYPE IN (1
, 2)
AND SEQ.OPERATION_SEQUENCE_ID = BR.OPERATION_SEQUENCE_ID
AND DR.RESOURCE_ID = V.RESOURCE_ID
AND NVL(DR.SHARE_FROM_DEPT_ID
, DR.DEPARTMENT_ID) = V.OWNING_DEPARTMENT_ID
AND SEQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND TRUNC(NVL(SEQ.DISABLE_DATE
, C1.CALENDAR_DATE+1)) > TRUNC(C1.CALENDAR_DATE)
AND TRUNC(SEQ.EFFECTIVITY_DATE) <= TRUNC(C1.CALENDAR_DATE)
AND RTG.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND NVL(RTG.CTP_FLAG
, 2) = 1
AND MUC1.UOM_CODE = V.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 = V.RES_UOM
AND MP.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C.CALENDAR_DATE = TRUNC(V.REQUEST_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 - NVL(V.ATP_LEAD_TIME
, 0)
AND C1.CALENDAR_CODE = MP.CALENDAR_CODE
AND C1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID