[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT F.INVENTORY_ITEM_ID,
SUM(F.ORIGINAL_FORECAST_QUANTITY),
SUM(F.ORIGINAL_FORECAST_QUANTITY *
MTL_ABC_COMPILE_PKG.get_item_cost(F.ORGANIZATION_ID,
F.INVENTORY_ITEM_ID,
F.PROJECT_ID,
x_org_cost_group_id))
BULK COLLECT INTO l_item_id,l_qty_demand_total,l_value_demand_total
FROM
BOM_CALENDAR_DATES C1,
MRP_FORECAST_DESIGNATORS D1,
MRP_FORECAST_DESIGNATORS D2,
MRP_FORECAST_DATES F,
MTL_ABC_COMPILES ABC
WHERE D2.FORECAST_DESIGNATOR = x_forc_name
AND D1.ORGANIZATION_ID = x_organization_id
AND D2.ORGANIZATION_ID = x_organization_id
AND D1.FORECAST_SET = NVL(D2.FORECAST_SET, x_forc_name)
AND D1.FORECAST_DESIGNATOR = DECODE(D2.FORECAST_SET, NULL,
D1.FORECAST_DESIGNATOR, x_forc_name)
AND ABC.ORGANIZATION_ID = F.ORGANIZATION_ID
AND ABC.COMPILE_ID = x_compile_id
AND F.INVENTORY_ITEM_ID = ABC.INVENTORY_ITEM_ID
AND F.ORGANIZATION_ID = x_organization_id
AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
AND F.BUCKET_TYPE = 1
AND C1.CALENDAR_CODE = x_cal_code
AND C1.EXCEPTION_SET_ID = x_except_id
AND ( C1.CALENDAR_DATE >= F.FORECAST_DATE
--Added format mask while using to_date function to comply with
--GSCC File.Date.5 standard. Bug:4410902
-- Bug# 6819570, replaced the to_date function used earlier with
-- FND_DATE.canonical_to_date which does not require format mask,
-- hence avoiding ORA-01861 'literal does not match format string' error.
AND C1.CALENDAR_DATE >= FND_DATE.canonical_to_date(x_start_date)
AND C1.CALENDAR_DATE <= FND_DATE.canonical_to_date(x_cutoff_date)
AND C1.CALENDAR_DATE = C1.NEXT_DATE
AND C1.CALENDAR_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE ))
group by F.INVENTORY_ITEM_ID
UNION ALL
SELECT F.INVENTORY_ITEM_ID,
SUM(F.ORIGINAL_FORECAST_QUANTITY/(C2.NEXT_SEQ_NUM-C3.NEXT_SEQ_NUM)),
SUM((F.ORIGINAL_FORECAST_QUANTITY/(C2.NEXT_SEQ_NUM-C3.NEXT_SEQ_NUM)) *
MTL_ABC_COMPILE_PKG.get_item_cost(F.ORGANIZATION_ID,
F.INVENTORY_ITEM_ID,
F.PROJECT_ID,
x_org_cost_group_id))
FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
BOM_CALENDAR_DATES C3,
BOM_CAL_WEEK_START_DATES W1, MRP_FORECAST_DATES F,
MRP_FORECAST_DESIGNATORS D1, MRP_FORECAST_DESIGNATORS D2,
MTL_ABC_COMPILES ABC
WHERE D2.FORECAST_DESIGNATOR = x_forc_name
AND D1.ORGANIZATION_ID = x_organization_id
AND D2.ORGANIZATION_ID = x_organization_id
AND D1.FORECAST_SET = NVL(D2.FORECAST_SET, x_forc_name)
AND D1.FORECAST_DESIGNATOR = DECODE(D2.FORECAST_SET, NULL,
D1.FORECAST_DESIGNATOR, x_forc_name)
AND ABC.ORGANIZATION_ID = F.ORGANIZATION_ID
AND ABC.COMPILE_ID = x_compile_id
AND F.INVENTORY_ITEM_ID = ABC.INVENTORY_ITEM_ID
AND F.ORGANIZATION_ID = x_organization_id
AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
AND F.BUCKET_TYPE = 2
AND W1.CALENDAR_CODE = x_cal_code
AND W1.EXCEPTION_SET_ID = x_except_id
AND (W1.WEEK_START_DATE >= F.FORECAST_DATE
AND W1.WEEK_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
--Added format mask while using to_date function to comply with
--GSCC File.Date.5 standard. Bug:4410902
-- Bug# 6819570, replaced the to_date function used earlier with
-- FND_DATE.canonical_to_date which does not require format mask,
-- hence avoiding ORA-01861 'literal does not match format string' error.
AND W1.NEXT_DATE > FND_DATE.canonical_to_date(x_start_date)
AND C1.CALENDAR_CODE = x_cal_code
AND C2.CALENDAR_CODE = x_cal_code
AND C3.CALENDAR_CODE = x_cal_code
AND C1.EXCEPTION_SET_ID = x_except_id
AND C2.EXCEPTION_SET_ID = x_except_id
AND C3.EXCEPTION_SET_ID = x_except_id
AND C3.CALENDAR_DATE= W1.WEEK_START_DATE
AND C2.CALENDAR_DATE = W1.NEXT_DATE
AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
--Added format mask while using to_date function to comply with
--GSCC File.Date.5 standard. Bug:4410902
-- Bug# 6819570, replaced the to_date function used earlier with
-- FND_DATE.canonical_to_date which does not require format mask,
-- hence avoiding ORA-01861 'literal does not match format string' error.
AND C1.CALENDAR_DATE >= FND_DATE.canonical_to_date(x_start_date)
AND C1.CALENDAR_DATE <= FND_DATE.canonical_to_date(x_cutoff_date)
AND C1.CALENDAR_DATE = C1.NEXT_DATE
AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
group by F.INVENTORY_ITEM_ID
UNION ALL
SELECT F.INVENTORY_ITEM_ID,
SUM(F.ORIGINAL_FORECAST_QUANTITY/
(C2.NEXT_SEQ_NUM - C3.NEXT_SEQ_NUM)),
SUM(F.ORIGINAL_FORECAST_QUANTITY/
(C2.NEXT_SEQ_NUM - C3.NEXT_SEQ_NUM) *
MTL_ABC_COMPILE_PKG.get_item_cost(F.ORGANIZATION_ID,
F.INVENTORY_ITEM_ID,
F.PROJECT_ID,
x_org_cost_group_id))
FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
BOM_CALENDAR_DATES C3,
BOM_PERIOD_START_DATES W1, MRP_FORECAST_DATES F,
MRP_FORECAST_DESIGNATORS D1, MRP_FORECAST_DESIGNATORS D2,
MTL_ABC_COMPILES ABC
WHERE D2.FORECAST_DESIGNATOR = x_forc_name
AND D1.ORGANIZATION_ID = x_organization_id
AND D2.ORGANIZATION_ID = x_organization_id
AND D1.FORECAST_SET = NVL(D2.FORECAST_SET, x_forc_name)
AND D1.FORECAST_DESIGNATOR = DECODE(D2.FORECAST_SET, NULL,
D1.FORECAST_DESIGNATOR, x_forc_name)
AND ABC.ORGANIZATION_ID = F.ORGANIZATION_ID
AND ABC.COMPILE_ID = x_compile_id
AND F.INVENTORY_ITEM_ID = ABC.INVENTORY_ITEM_ID
AND F.ORGANIZATION_ID = x_organization_id
AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
AND F.BUCKET_TYPE = 3
AND W1.CALENDAR_CODE = x_cal_code
AND W1.EXCEPTION_SET_ID = x_except_id
AND (W1.PERIOD_START_DATE >= F.FORECAST_DATE
AND W1.PERIOD_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
--Added format mask while using to_date function to comply with
--GSCC File.Date.5 standard. Bug:4410902
-- Bug# 6819570, replaced the to_date function used earlier with
-- FND_DATE.canonical_to_date which does not require format mask,
-- hence avoiding ORA-01861 'literal does not match format string' error.
AND W1.NEXT_DATE > FND_DATE.canonical_to_date(x_start_date)
AND C1.CALENDAR_CODE = x_cal_code
AND C2.CALENDAR_CODE = x_cal_code
AND C3.CALENDAR_CODE = x_cal_code
AND C1.EXCEPTION_SET_ID = x_except_id
AND C2.EXCEPTION_SET_ID = x_except_id
AND C3.EXCEPTION_SET_ID = x_except_id
AND C3.CALENDAR_DATE= W1.PERIOD_START_DATE
AND C2.CALENDAR_DATE = W1.NEXT_DATE
AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
--Added format mask while using to_date function to comply with
--GSCC File.Date.5 standard. Bug:4410902
-- Bug# 6819570, replaced the to_date function used earlier with
-- FND_DATE.canonical_to_date which does not require format mask,
-- hence avoiding ORA-01861 'literal does not match format string' error.
AND C1.CALENDAR_DATE >= FND_DATE.canonical_to_date(x_start_date)
AND C1.CALENDAR_DATE = C1.NEXT_DATE
--Added format mask while using to_date function to comply with
--GSCC File.Date.5 standard. Bug:4410902
-- Bug# 6819570, replaced the to_date function used earlier with
-- FND_DATE.canonical_to_date which does not require format mask,
-- hence avoiding ORA-01861 'literal does not match format string' error.
AND C1.CALENDAR_DATE <= FND_DATE.canonical_to_date(x_cutoff_date)
AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
group by F.INVENTORY_ITEM_ID;
UPDATE MTL_ABC_COMPILES
SET COMPILE_QUANTITY = COMPILE_QUANTITY + l_qty_demand_total(i),
COMPILE_VALUE = COMPILE_VALUE + l_value_demand_total(i)
WHERE ORGANIZATION_ID = x_organization_id
AND inventory_item_id = l_item_id(i)
AND compile_id = x_compile_id;
SELECT NVL(CCICV.ITEM_COST,0) into l_item_cost
FROM CST_CG_ITEM_COSTS_VIEW CCICV,
MRP_PROJECT_PARAMETERS MPP
WHERE CCICV.ORGANIZATION_ID = x_organization_id
AND CCICV.INVENTORY_ITEM_ID = x_inventory_item_id
AND CCICV.COST_GROUP_ID = MPP.COSTING_GROUP_ID
AND MPP.PROJECT_ID = x_project_id
AND MPP.ORGANIZATION_ID = x_organization_id;
SELECT NVL(CCICV.ITEM_COST,0) into l_item_cost
FROM CST_CG_ITEM_COSTS_VIEW CCICV
WHERE CCICV.ORGANIZATION_ID = x_organization_id
AND CCICV.INVENTORY_ITEM_ID = x_inventory_item_id
AND CCICV.COST_GROUP_ID = x_cost_group_id;
SELECT organization_id
, inventory_item_id
, compile_id
FROM mtl_abc_compiles
WHERE compile_id = p_compile_id
FOR UPDATE;
SELECT cost_mthd_code
INTO l_cost_mthd
FROM cm_mthd_mst
WHERE cost_type_id = p_cost_type_id;
UPDATE mtl_abc_compiles
SET compile_value = compile_quantity * l_item_cost
WHERE CURRENT OF cur_get_abc_compiles;