[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_supply_demand_table.delete;
SELECT
1 reservation_type,
8 supply_demand_source_type,
0 txn_source_type_id,
0 supply_demand_source_id,
2 supply_demand_type,
SUM(Q.TRANSACTION_QUANTITY) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE-1,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_SECONDARY_INVENTORIES S,
BOM_CALENDAR_DATES C,
MTL_PARAMETERS P,
MTL_ONHAND_QUANTITIES Q,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
MTL_GROUP_ITEM_ATPS_VIEW V
WHERE I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND Q.ORGANIZATION_ID = V.ORGANIZATION_ID
AND Q.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND S.SECONDARY_INVENTORY_NAME = Q.SUBINVENTORY_CODE
AND S.ORGANIZATION_ID = Q.ORGANIZATION_ID
AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.AVAILABILITY_TYPE = DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2, -1, V.INVENTORY_ITEM_ID)
AND V.ATP_GROUP_ID = P_GROUP_ID
AND R.DEMAND_CLASS_ATP_FLAG=2
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(SYSDATE)
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
GROUP BY V.INVENTORY_ITEM_ID, V.ORGANIZATION_ID, C.NEXT_DATE, C.NEXT_SEQ_NUM
UNION ALL
SELECT
1 reservation_type,
8 supply_demand_source_type,
0 txn_source_type_id,
0 supply_demand_source_id,
2 supply_demand_type,
SUM(T.PRIMARY_QUANTITY) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM MTL_SECONDARY_INVENTORIES S,
BOM_CALENDAR_DATES C,
MTL_PARAMETERS P,
MTL_MATERIAL_TRANSACTIONS_TEMP T,
MTL_SYSTEM_ITEMS I,
MTL_ATP_RULES R,
MTL_GROUP_ITEM_ATPS_VIEW V
WHERE I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = V.ORGANIZATION_ID
AND T.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND T.POSTING_FLAG = 'Y'
AND S.SECONDARY_INVENTORY_NAME = T.SUBINVENTORY_CODE
AND S.ORGANIZATION_ID = T.ORGANIZATION_ID
AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.AVAILABILITY_TYPE = DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2, -1, V.INVENTORY_ITEM_ID)
AND V.ATP_GROUP_ID = P_GROUP_ID
AND R.DEMAND_CLASS_ATP_FLAG=2
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(SYSDATE)
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
GROUP BY V.INVENTORY_ITEM_ID, V.ORGANIZATION_ID, C.NEXT_DATE, C.NEXT_SEQ_NUM;
SELECT
1 reservation_type,
16 supply_demand_source_type,
U.SOURCE_TYPE_ID txn_source_type_id,
U.SOURCE_ID supply_demand_source_id,
2 supply_demand_type,
U.PRIMARY_UOM_QUANTITY supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
BOM_CALENDAR_DATES C,
MTL_USER_SUPPLY U,
MTL_SYSTEM_ITEMS I,
MTL_PARAMETERS P,
MTL_ATP_RULES R,
MTL_GROUP_ITEM_ATPS_VIEW V
WHERE U.ORGANIZATION_ID = V.ORGANIZATION_ID
AND U.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_USER_DEFINED_SUPPLY, 2, -1, V.INVENTORY_ITEM_ID)
AND V.ATP_GROUP_ID = P_GROUP_ID
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM +(DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(U.EXPECTED_DELIVERY_DATE);
SELECT
1 reservation_type,
DECODE(S.PO_HEADER_ID,
NULL,DECODE(S.SUPPLY_TYPE_CODE,
'REQ',DECODE(S.FROM_ORGANIZATION_ID,
NULL,18,
20),
12),
1) supply_demand_source_type,
DECODE(S.PO_HEADER_ID,
NULL,DECODE(S.SUPPLY_TYPE_CODE,
'REQ',10,
8),
1) txn_source_type_id,
DECODE(S.PO_HEADER_ID,
NULL,DECODE(S.SUPPLY_TYPE_CODE,
'REQ',REQ_HEADER_ID,
SHIPMENT_HEADER_ID),
PO_HEADER_ID) supply_demand_source_id,
2 supply_demand_type,
DECODE(P_MRP_STATUS,
1, DECODE(S.SUPPLY_TYPE_CODE,
'SHIPMENT', S.TO_ORG_PRIMARY_QUANTITY,
DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
1,NVL(S.MRP_PRIMARY_QUANTITY, 0),
S.TO_ORG_PRIMARY_QUANTITY)),
S.TO_ORG_PRIMARY_QUANTITY) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_GROUP_ITEM_ATPS_VIEW V,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
MTL_PARAMETERS P,
BOM_CALENDAR_DATES C,
MTL_SUPPLY S
WHERE V.ATP_GROUP_ID = P_GROUP_ID
AND R.DEMAND_CLASS_ATP_FLAG=2
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND((R.INCLUDE_INTERORG_TRANSFERS = 1
AND S.REQ_HEADER_ID IS NULL
AND S.PO_HEADER_ID IS NULL)
OR (S.REQ_HEADER_ID=DECODE(R.INCLUDE_INTERNAL_REQS,1,S.REQ_HEADER_ID)
AND S.FROM_ORGANIZATION_ID IS NOT NULL)
OR (S.SUPPLY_TYPE_CODE=DECODE(R.INCLUDE_VENDOR_REQS,1,'REQ')
AND S.FROM_ORGANIZATION_ID IS NULL)
OR S.PO_HEADER_ID=DECODE(R.INCLUDE_PURCHASE_ORDERS,1, S.PO_HEADER_ID))
AND S.TO_ORGANIZATION_ID=V.ORGANIZATION_ID
AND S.ITEM_ID = V.INVENTORY_ITEM_ID
AND S.DESTINATION_TYPE_CODE='INVENTORY'
AND (S.TO_SUBINVENTORY IS NULL OR EXISTS (SELECT
'X' FROM MTL_SECONDARY_INVENTORIES S2
WHERE S2.ORGANIZATION_ID=S.TO_ORGANIZATION_ID
AND S.TO_SUBINVENTORY=S2.SECONDARY_INVENTORY_NAME
AND S2.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
1, 1,
NULL, 1,
S2.INVENTORY_ATP_CODE)
AND S2.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2, 1,
S2.AVAILABILITY_TYPE)))
AND I.ORGANIZATION_ID= V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(S.EXPECTED_DELIVERY_DATE);
SELECT
1 reservation_type,
DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
5 txn_source_type_id,
D.WIP_ENTITY_ID supply_demand_source_id,
2 supply_demand_type,
DECODE(P_MRP_STATUS,
1, DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
1, DECODE(D.JOB_TYPE,1,
DECODE(I.MRP_PLANNING_CODE,
4,NVL(D.MPS_NET_QUANTITY,0),
D.START_QUANTITY),
D.START_QUANTITY),
D.START_QUANTITY) - D.QUANTITY_COMPLETED - D.QUANTITY_SCRAPPED,
D.START_QUANTITY - D.QUANTITY_COMPLETED - D.QUANTITY_SCRAPPED) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM WIP_DISCRETE_JOBS D,
BOM_CALENDAR_DATES C,
MTL_PARAMETERS P,
MTL_SYSTEM_ITEMS I,
MTL_ATP_RULES R,
MTL_GROUP_ITEM_ATPS_VIEW V
WHERE D.STATUS_TYPE IN (1,3,4,6)
AND (D.START_QUANTITY-D.QUANTITY_COMPLETED-D.QUANTITY_SCRAPPED) >0
AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
AND D.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,
V.INVENTORY_ITEM_ID, DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
1,V.INVENTORY_ITEM_ID,
-1))
AND (D.JOB_TYPE =DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
OR D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE);
SELECT
1 reservation_type,
DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
5 txn_source_type_id,
D.WIP_ENTITY_ID supply_demand_source_id,
2 supply_demand_type,
-1*O.REQUIRED_QUANTITY supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM MTL_GROUP_ITEM_ATPS_VIEW V,
MTL_PARAMETERS P,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
BOM_CALENDAR_DATES C,
WIP_REQUIREMENT_OPERATIONS O,
WIP_DISCRETE_JOBS D
WHERE O.ORGANIZATION_ID=D.ORGANIZATION_ID
AND O.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
AND O.WIP_ENTITY_ID=D.WIP_ENTITY_ID
AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
AND O.WIP_SUPPLY_TYPE <> 6
AND O.REQUIRED_QUANTITY < 0
AND O.OPERATION_SEQ_NUM > 0
AND (D.JOB_TYPE=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
OR D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
AND D.STATUS_TYPE IN (1,3,4,6)
AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,
V.INVENTORY_ITEM_ID, DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1,
V.INVENTORY_ITEM_ID, -1))
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED);
SELECT
1 reservation_type,
4 supply_demand_source_type,
5 txn_source_type_id,
WRS.REPETITIVE_SCHEDULE_ID supply_demand_source_id,
2 supply_demand_type,
DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM)-WRS.QUANTITY_COMPLETED),
-1,WRS.DAILY_PRODUCTION_RATE*LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,
WRS.PROCESSING_WORK_DAYS)-WRS.QUANTITY_COMPLETED,
LEAST(C1.NEXT_SEQ_NUM+WRS.PROCESSING_WORK_DAYS-C.NEXT_SEQ_NUM,1)*WRS.DAILY_PRODUCTION_RATE) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_GROUP_ATPS_VIEW V,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
MTL_PARAMETERS P,
BOM_CALENDAR_DATES C,
BOM_CALENDAR_DATES C1,
WIP_REPETITIVE_SCHEDULES WRS,
WIP_REPETITIVE_ITEMS WRI
WHERE V.ATP_GROUP_ID = P_GROUP_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND WRI.ORGANIZATION_ID = V.ORGANIZATION_ID
AND WRI.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
AND R.INCLUDE_REP_WIP_RECEIPTS = 1
AND WRI.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND WRI.LINE_ID = WRS.LINE_ID
AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND WRS.STATUS_TYPE IN (1,3,4,6)
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C1.CALENDAR_CODE=P.CALENDAR_CODE
AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_COMPLETION_DATE)
AND C.CALENDAR_CODE=P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
AND C.SEQ_NUM BETWEEN C1.NEXT_SEQ_NUM AND C1.NEXT_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
AND WRS.DAILY_PRODUCTION_RATE*LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS) > WRS.QUANTITY_COMPLETED
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (
DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1);
SELECT
1 reservation_type,
24 supply_demand_source_type,
5 txn_source_type_id,
D.WIP_ENTITY_ID supply_demand_source_id,
2 supply_demand_type,
DECODE(P_MRP_STATUS,
1,DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
1,DECODE(I.MRP_PLANNING_CODE,
4,NVL(D.MPS_NET_QUANTITY,0),
8,NVL(D.MPS_NET_QUANTITY,0),
D.PLANNED_QUANTITY),
D.PLANNED_QUANTITY - D.QUANTITY_COMPLETED), /* I missed something here */
D.PLANNED_QUANTITY - D.QUANTITY_COMPLETED) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
WIP_FLOW_SCHEDULES D,
BOM_CALENDAR_DATES C,
MTL_PARAMETERS P,
MTL_SYSTEM_ITEMS I,
MTL_ATP_RULES R,
MTL_GROUP_ITEM_ATPS_VIEW V
WHERE D.STATUS = 1
AND (D.PLANNED_QUANTITY-D.QUANTITY_COMPLETED) >0
AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
AND D.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
AND D.SCHEDULED_FLAG = 1
AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, V.INVENTORY_ITEM_ID, -1)
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE)
AND C.NEXT_SEQ_NUM >= P_SYS_SEQ_NUM;
SELECT
1 reservation_type,
DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
5 txn_source_type_id,
D.WIP_ENTITY_ID supply_demand_source_id,
1 supply_demand_type,
LEAST(-1*(O.REQUIRED_QUANTITY-O.QUANTITY_ISSUED),0) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_GROUP_ITEM_ATPS_VIEW V,
MTL_PARAMETERS P,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
BOM_CALENDAR_DATES C,
WIP_REQUIREMENT_OPERATIONS O,
WIP_DISCRETE_JOBS D,
BOM_CALENDAR_DATES C1
WHERE O.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
AND O.WIP_SUPPLY_TYPE <> 6
AND O.REQUIRED_QUANTITY > 0
AND O.OPERATION_SEQ_NUM > 0
AND O.WIP_ENTITY_ID=D.WIP_ENTITY_ID
AND O.ORGANIZATION_ID=D.ORGANIZATION_ID
AND O.DATE_REQUIRED >= c1.calendar_date
AND ((D.JOB_TYPE= 1 AND R.INCLUDE_DISCRETE_WIP_DEMAND = 1)
OR (D.JOB_TYPE = 3 AND R.INCLUDE_NONSTD_WIP_DEMAND = 1))
AND D.STATUS_TYPE IN (1,3,4,6)
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C1.SEQ_NUM = greatest(1,P_SYS_SEQ_NUM-Nvl(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND P.CALENDAR_CODE = C1.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED)
AND NOT EXISTS (SELECT 'exists in group?'
FROM MTL_DEMAND_INTERFACE MDI1
WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
AND MDI1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = D.WIP_ENTITY_ID);
SELECT
1 reservation_type,
4 supply_demand_source_type,
5 txn_source_type_id,
WRS.REPETITIVE_SCHEDULE_ID supply_demand_source_id,
1 supply_demand_type,
DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM)-WRO.QUANTITY_ISSUED),
-1,-1*(WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*LEAST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS)-WRO.QUANTITY_ISSUED),
GREATEST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM-WRS.PROCESSING_WORK_DAYS,-1)*WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_GROUP_ITEM_ATPS_VIEW V,
MTL_PARAMETERS P,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
BOM_CALENDAR_DATES C,
BOM_CALENDAR_DATES C1,
WIP_REPETITIVE_SCHEDULES WRS,
WIP_OPERATIONS WO,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE WRO.ORGANIZATION_ID = V.ORGANIZATION_ID
AND WRO.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND R.INCLUDE_REP_WIP_DEMAND = 1
AND WRO.WIP_SUPPLY_TYPE <> 6
AND WRO.REQUIRED_QUANTITY > 0
AND WRO.OPERATION_SEQ_NUM > 0
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+)
AND WRO.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID(+)
AND WRO.ORGANIZATION_ID = WO.ORGANIZATION_ID(+)
AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND WRS.STATUS_TYPE IN (1,3,4,6)
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C1.CALENDAR_CODE=P.CALENDAR_CODE
AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_START_DATE)
AND C.CALENDAR_CODE=P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
AND C.SEQ_NUM BETWEEN C1.PRIOR_SEQ_NUM AND C1.PRIOR_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
AND WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*LEAST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS)>WRO.QUANTITY_ISSUED
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
AND NOT EXISTS (SELECT 'exists in group?'
FROM MTL_DEMAND_INTERFACE MDI1
WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
AND MDI1.ORGANIZATION_ID = V.ORGANIZATION_ID
AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = WRS.WIP_ENTITY_ID);
SELECT
1 reservation_type,
17 supply_demand_source_type,
U.SOURCE_TYPE_ID txn_source_type_id,
U.SOURCE_ID supply_demand_source_id,
1 supply_demand_type,
-1*U.PRIMARY_UOM_QUANTITY supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_GROUP_ITEM_ATPS_VIEW V,
MTL_PARAMETERS P,
MTL_ATP_RULES R,
MTL_SYSTEM_ITEMS I,
MTL_USER_DEMAND U,
BOM_CALENDAR_DATES C
WHERE U.ORGANIZATION_ID = V.ORGANIZATION_ID
AND U.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_USER_DEFINED_DEMAND, 2, -1, V.INVENTORY_ITEM_ID)
AND V.ATP_GROUP_ID = P_GROUP_ID
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(U.REQUIREMENT_DATE);
SELECT
1 reservation_type,
24 supply_demand_source_type,
5 txn_source_type_id,
F.WIP_ENTITY_ID supply_demand_source_id,
1 supply_demand_type,
F.PLANNED_QUANTITY-F.QUANTITY_COMPLETED schedule_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id,
F.primary_item_id assembly_item_id,
F.bom_revision_date bom_revision_date,
F.alternate_bom_designator alternate_bom_designator
FROM WIP_FLOW_SCHEDULES F,
BOM_BILL_OF_MATERIALS BOM ,
BOM_EXPLOSIONS BE ,
BOM_CALENDAR_DATES C,
MTL_PARAMETERS P,
MTL_SYSTEM_ITEMS I,
MTL_ATP_RULES R,
MTL_GROUP_ITEM_ATPS_VIEW V
WHERE V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND, 1,
V.INVENTORY_ITEM_ID, -1)
AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND BE.COMPONENT_ITEM_ID = V.INVENTORY_ITEM_ID
AND BE.ORGANIZATION_ID = V.ORGANIZATION_ID
AND BE.EXPLOSION_TYPE = 'ALL'
AND BE.EXTENDED_QUANTITY > 0
AND BE.COMPONENT_ITEM_ID <> BE.TOP_ITEM_ID
AND BOM.COMMON_BILL_SEQUENCE_ID = BE.TOP_BILL_SEQUENCE_ID
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND TRUNC(BE.EFFECTIVITY_DATE) <= TRUNC(F.SCHEDULED_COMPLETION_DATE)
AND TRUNC(BE.DISABLE_DATE) > TRUNC(F.SCHEDULED_COMPLETION_DATE)
AND F.PRIMARY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND F.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND F.STATUS = 1
AND F.SCHEDULED_FLAG = 1
AND (F.PLANNED_QUANTITY - F.QUANTITY_COMPLETED) >0
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND NVL(F.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
NVL(F.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(F.SCHEDULED_COMPLETION_DATE)
AND C.PRIOR_SEQ_NUM >= P_SYS_SEQ_NUM
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
AND NOT EXISTS (SELECT 'exists in group?'
FROM MTL_DEMAND_INTERFACE MDI1
WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
AND MDI1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = F.WIP_ENTITY_ID);
SELECT D.RESERVATION_TYPE reservation_type,
DECODE(D.DEMAND_SOURCE_TYPE,
2, DECODE(D.RESERVATION_TYPE,1,2,3,23,9),
8, DECODE(D.RESERVATION_TYPE,1,21,22),D.DEMAND_SOURCE_TYPE) supply_demand_source_type,
DECODE(D.DEMAND_SOURCE_TYPE,
8,2,D.DEMAND_SOURCE_TYPE) txn_source_type_id,
D.DEMAND_SOURCE_HEADER_ID supply_demand_source_id,
1 supply_demand_type,
-1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
V.INVENTORY_ITEM_ID inventory_item_id,
V.ORGANIZATION_ID organization_id
FROM
MTL_GROUP_ITEM_ATPS_VIEW V,
MTL_PARAMETERS P,
MTL_SYSTEM_ITEMS I,
MTL_ATP_RULES R,
BOM_CALENDAR_DATES C,
MTL_DEMAND D,
BOM_CALENDAR_DATES C1
WHERE D.ORGANIZATION_ID = V.ORGANIZATION_ID
AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)
AND D.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND D.AVAILABLE_TO_ATP = 1
AND D.RESERVATION_TYPE <> DECODE(NVL(V.N_COLUMN1,R.INCLUDE_ONHAND_AVAILABLE), 2, 2, -1)
AND D.RESERVATION_TYPE <> DECODE(R.DEMAND_CLASS_ATP_FLAG, 1, 2, -1)
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS, 2, 2, -1)
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS, 2, 8, -1)
AND (D.SUBINVENTORY IS NULL
OR D.SUBINVENTORY IN (SELECT S.SECONDARY_INVENTORY_NAME
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)))
AND V.AVAILABLE_TO_ATP = 1
AND V.ATP_RULE_ID = R.RULE_ID
AND V.ATP_GROUP_ID = P_GROUP_ID
AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
AND C1.SEQ_NUM = greatest(1, P_SYS_SEQ_NUM - Nvl(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND D.REQUIREMENT_DATE >= C1.CALENDAR_DATE
AND D.RESERVATION_TYPE <> 2
AND C.PRIOR_SEQ_NUM < DECODE(D.RESERVATION_TYPE,
2,C.PRIOR_SEQ_NUM+1,
NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1, I.CUMULATIVE_TOTAL_LEAD_TIME,
2, I.CUM_MANUFACTURING_LEAD_TIME,
3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1))
AND NOT EXISTS
(SELECT 'exists in group?'
FROM MTL_GROUP_ATPS_VIEW V1
WHERE V1.ATP_GROUP_ID = P_GROUP_ID
AND V1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
AND V1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
AND V1.AVAILABLE_TO_ATP = 1
AND NVL(V1.DEMAND_SOURCE_TYPE, -1) = D.DEMAND_SOURCE_TYPE
AND NVL(V1.DEMAND_SOURCE_HEADER_ID, -1) = D.DEMAND_SOURCE_HEADER_ID
AND NVL(V1.DEMAND_SOURCE_LINE, '@@@') = NVL(D.DEMAND_SOURCE_LINE, '@@@')
AND NVL(V1.DEMAND_SOURCE_DELIVERY, '@@@') = NVL(D.DEMAND_SOURCE_DELIVERY, '@@@')
AND NVL(V1.DEMAND_SOURCE_NAME, '@@@') = NVL(D.DEMAND_SOURCE_NAME, '@@@'))
AND P.CALENDAR_CODE = C.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
AND P.CALENDAR_CODE = C1.CALENDAR_CODE
AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
AND V.INVENTORY_ITEM_ID=DECODE(D.RESERVATION_TYPE,
1,DECODE(D.PARENT_DEMAND_ID, NULL,V.INVENTORY_ITEM_ID,-1),
2,V.INVENTORY_ITEM_ID,
3,DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS,
1,V.INVENTORY_ITEM_ID,
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
1,V.INVENTORY_ITEM_ID, -1)),-1)
AND V.INVENTORY_ITEM_ID=
DECODE(R.INCLUDE_SALES_ORDERS, 2,
DECODE(R.INCLUDE_INTERNAL_ORDERS, 2,
DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2,
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 2,
DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 2, -1,
V.INVENTORY_ITEM_ID),
V.INVENTORY_ITEM_ID),
V.INVENTORY_ITEM_ID),
V.INVENTORY_ITEM_ID),
V.INVENTORY_ITEM_ID);
SELECT
1 reservation_type, -- fake
2 supply_demand_source_type,
2 txn_source_type_id, -- fake
L.LINE_ID supply_demand_source_id,
1 supply_demand_type,
-1*(L.ORDERED_QUANTITY-NVL(SHIPPED_QUANTITY, 0)) supply_demand_quantity,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
I.INVENTORY_ITEM_ID inventory_item_id,
I.ORGANIZATION_ID organization_id
FROM BOM_CALENDAR_DATES C ,
OE_ORDER_LINES L,
MTL_ATP_RULES R ,
/* MTL_GROUP_ATPS_VIEW G , */
MTL_DEMAND_INTERFACE G, /* use the table directly - perf bug 4899603 */
MTL_PARAMETERS P ,
MTL_SYSTEM_ITEMS I
WHERE I.ATP_FLAG in ('C', 'Y')
AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
AND G.ATP_GROUP_ID = p_group_id
AND G.ORGANIZATION_ID = I.ORGANIZATION_ID
AND G.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND G.ATP_RULE_ID = R.RULE_ID
AND R.INCLUDE_SALES_ORDERS = 1
AND L.SHIP_FROM_ORG_ID = I.ORGANIZATION_ID
AND L.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND L.VISIBLE_DEMAND_FLAG = 'Y'
AND L.ORDERED_QUANTITY > NVL(L.SHIPPED_QUANTITY,0)
AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_DATE,
MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID,
1,
SYSDATE,
-NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.CALENDAR_DATE = TRUNC(L.SCHEDULE_SHIP_DATE);
SELECT OE_INSTALL.Get_Active_Product
INTO l_oe_install
FROM DUAL;
SELECT sort_order
FROM bom_explosions be
WHERE be.top_bill_sequence_id = l_top_bill_seq_id
and be.organization_id = p_organization_id
and be.component_item_id = p_component_item_id
and be.explosion_type = 'ALL'
and be.component_item_id <> be.top_item_id
and trunc(be.effectivity_date) <= trunc(nvl(p_date,sysdate))
and trunc(be.disable_date) > trunc(nvl(p_date,sysdate));
select common_bill_sequence_id
into l_top_bill_sequence_id
from bom_bill_of_materials
where assembly_item_id = p_assembly_item_id
and organization_id = p_organization_id
and alternate_bom_designator = p_alternate_bom_designator;
select common_bill_sequence_id
into l_top_bill_sequence_id
from bom_bill_of_materials
where assembly_item_id = p_assembly_item_id
and organization_id = p_organization_id
and alternate_bom_designator is NULL;
select NVL(bic.wip_supply_type,WIP_CONSTANTS.PUSH), NVL(bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),be.component_quantity
into l_wip_supply_type,l_basis_type,l_qty
from bom_explosions be, bom_inventory_components bic
where be.top_bill_sequence_id = l_top_bill_sequence_id
and be.explosion_type = 'ALL'
and be.sort_order = l_sort_order
and be.component_sequence_id = bic.component_sequence_id;
SELECT MTL_ATP_RULES_S.NEXTVAL,MTL_SUPPLY_DEMAND_TEMP_S.NEXTVAL INTO l_rule_id,l_group_id from DUAL;
DELETE FROM MTL_ATP_RULES WHERE RULE_NAME = 'FLM';
SELECT C1.NEXT_SEQ_NUM, (C2.NEXT_SEQ_NUM - C1.NEXT_SEQ_NUM +1)
INTO l_sys_seq_num, l_inf_fence_ind
FROM BOM_CALENDAR_DATES C1, MTL_PARAMETERS P, BOM_CALENDAR_DATES C2
WHERE P.ORGANIZATION_ID= p_org_id
AND P.CALENDAR_CODE = C1.CALENDAR_CODE
AND C1.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C1.CALENDAR_DATE = TRUNC(sysdate)
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
AND C2.CALENDAR_DATE = p_to_date;
INSERT INTO MTL_ATP_RULES (
RULE_ID,
RULE_NAME,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
PAST_DUE_DEMAND_CUTOFF_FENCE,
PAST_DUE_SUPPLY_CUTOFF_FENCE,
INFINITE_SUPPLY_TIME_FENCE,
INFINITE_SUPPLY_FENCE_CODE,
DEMAND_CLASS_ATP_FLAG,
INCLUDE_REP_MPS,
INCLUDE_ONHAND_AVAILABLE,
ACCUMULATE_AVAILABLE_FLAG,
FORWARD_CONSUMPTION_FLAG,
BACKWARD_CONSUMPTION_FLAG,
INCLUDE_SALES_ORDERS,
INCLUDE_INTERNAL_ORDERS,
INCLUDE_DISCRETE_WIP_DEMAND,
INCLUDE_REP_WIP_DEMAND,
INCLUDE_NONSTD_WIP_DEMAND,
INCLUDE_DISCRETE_MPS,
INCLUDE_USER_DEFINED_DEMAND,
INCLUDE_PURCHASE_ORDERS,
INCLUDE_INTERNAL_REQS,
INCLUDE_VENDOR_REQS,
INCLUDE_DISCRETE_WIP_RECEIPTS,
INCLUDE_REP_WIP_RECEIPTS,
INCLUDE_NONSTD_WIP_RECEIPTS,
INCLUDE_INTERORG_TRANSFERS,
INCLUDE_USER_DEFINED_SUPPLY,
INCLUDE_FLOW_SCHEDULE_DEMAND,
INCLUDE_FLOW_SCHEDULE_RECEIPTS,
DEFAULT_ATP_SOURCES)
VALUES (
l_rule_id, -- RULE_ID
'FLM', -- RULE_NAME
0, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
0, -- LAST_UPDATED_BY
SYSDATE, -- CREATION_DATE
0, -- PAST_DUE_DEMAND_CUTOFF_FENCE
0, -- PAST_DUE_SUPPLY_CUTOFF_FENCE
l_inf_fence_ind, -- INFINITE_SUPPLY_TIME_FENCE
4, -- INFINITE_SUPPLY_FENCE_CODE
2, -- DEMAND_CLASS_ATP_FLAG
2, -- INCLUDE_REP_MPS
1, -- INCLUDE_ONHAND_AVAILABLE
1, -- ACCUMULATE_AVAILABLE_FLAG
1, -- FORWARD_CONSUMPTION_FLAG
1, -- BACKWARD_CONSUMPTION_FLAG
1, -- INCLUDE_SALES_ORDERS
1, -- INCLUDE_INTERNAL_ORDERS
1, -- INCLUDE_DISCRETE_WIP_DEMAND
1, -- INCLUDE_REP_WIP_DEMAND
1, -- INCLUDE_NONSTD_WIP_DEMAND
2, -- INCLUDE_DISCRETE_MPS
1, -- INCLUDE_USER_DEFINED_DEMAND
1, -- INCLUDE_PURCHASE_ORDERS
2, -- INCLUDE_INTERNAL_REQS
2, -- INCLUDE_VENDOR_REQS
1, -- INCLUDE_DISCRETE_WIP_RECEIPTS
1, -- INCLUDE_REP_WIP_RECEIPTS
1, -- INCLUDE_NONSTD_WIP_RECEIPTS
1, -- INCLUDE_INTERORG_TRANSFERS
1, -- INCLUDE_USER_DEFINED_SUPPLY
1, -- INCLUDE_FLOW_SCHEDULE_DEMAND
1, -- INCLUDE_FLOW_SCHEDULE_RECEIPTS
3 -- DEFAULT_ATP_SOURCES (all subinventories)
);
INSERT INTO MTL_GROUP_ATPS_VIEW (
ATP_GROUP_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ATP_RULE_ID,
REQUEST_QUANTITY,
UOM_CODE,
AVAILABLE_TO_ATP,
N_COLUMN1)
VALUES (
l_group_id,
p_org_id,
p_item_id_tbl(item_index),
SYSDATE,
0,
SYSDATE,
0,
l_rule_id,
0,
'SD',
1,
-1);
select line_id
into l_line_id
from wip_flow_schedules
where wip_entity_id = l_temp_tbl(i).supply_demand_source_id;
l_temp_tbl.delete;