FND Design Data [Home] [Help]

View: MRP_ATP_BASE_V

Product: MRP - Master Scheduling/MRP
Description: ATP/CTP View
Implementation/DBA Data: ViewAPPS.MRP_ATP_BASE_V
View Text

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

Columns

Name
ROW_ID
ATP_GROUP_ID
ORGANIZATION_ID
INVENTORY_ITEM_ID
REQUEST_ITEM_ID
DEPARTMENT_ID
DEPARTMENT_CODE
DEPARTMENT_DESCRIPTION
OWNING_DEPARTMENT_ID
RESOURCE_ID
RESOURCE_CODE
RESOURCE_DESCRIPTION
LINE_ID
LINE_CODE
LINE_DESCRIPTION
ENTITY_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
ATP_RULE_ID
REQUEST_QUANTITY
REQUEST_DATE_ATP_QUANTITY
EARLIEST_ATP_DATE
EARLIEST_ATP_DATE_QUANTITY
REQUEST_ATP_DATE
REQUEST_ATP_DATE_QUANTITY
GROUP_AVAILABLE_DATE
ACCUMULATION_WINDOW
ATP_LEAD_TIME
AVAILABLE_TO_ATP
INFINITE_TIME_FENCE_DATE
UOM_CODE
GROUP_ATP_CHECK
LINE_NUMBER
ATTRIBUTE_CATEGORY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ATTRIBUTE6
ATTRIBUTE7
ATTRIBUTE8
ATTRIBUTE9
ATTRIBUTE10
ATTRIBUTE11
ATTRIBUTE12
ATTRIBUTE13
ATTRIBUTE14
ATTRIBUTE15
C_COLUMN1
C_COLUMN2
C_COLUMN3
C_COLUMN4
C_COLUMN5
C_COLUMN6
N_COLUMN1
N_COLUMN2
N_COLUMN3
N_COLUMN4
N_COLUMN5
D_COLUMN1
D_COLUMN2
D_COLUMN3
D_COLUMN4
D_COLUMN5
REQUEST_DATE
ERROR_CODE
ERR_EXPLANATION
DEMAND_CLASS
DEMAND_SOURCE_TYPE
DEMAND_SOURCE_HEADER_ID
DEMAND_SOURCE_LINE
DEMAND_SOURCE_DELIVERY
DEMAND_SOURCE_NAME
LATEST_ACCEPTABLE_DATE
ATP_CALENDAR_ORGANIZATION_ID
REQUEST_PRIMARY_UOM_QUANTITY
SESSION_ID
SCHEDULE_GROUP_ID
RESOURCE_BASIS_TYPE
RESOURCE_USAGE_RATE_OR_AMOUNT
AVAILABLE_24_HOURS_FLAG
SUPPLY_HEADER_ID
RES_UOM
CUSTOMER_ID
ACTION_CODE