[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
cst_pac_periods.pac_period_id
FROM
cst_pac_periods,
cst_pac_item_costs
WHERE cst_pac_periods.pac_period_id = cst_pac_item_costs.pac_period_id
AND cst_pac_item_costs.inventory_item_id = p_item_id
AND cst_pac_item_costs.cost_group_id = p_cost_group_id
AND cst_pac_periods.cost_type_id = p_cost_type_id
ORDER BY cst_pac_periods.period_year, cst_pac_periods.period_num;
SELECT
cpql.begin_layer_quantity
,cpic.market_value
INTO
l_begin_quantity
,l_market_value
FROM
cst_pac_item_costs cpic
, cst_pac_quantity_layers cpql
WHERE cpic.pac_period_id = l_current_period
AND cpic.inventory_item_id = p_item_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.cost_layer_id = cpql.cost_layer_id;
SELECT
nvl(buy_quantity,0)
,nvl(make_quantity,0)
,nvl(issue_quantity,0)
,market_value
FROM
cst_pac_item_costs
WHERE pac_period_id = c_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id;
SELECT open_flag
FROM CST_PAC_PERIODS
WHERE pac_period_id = c_period_id;
SELECT
total_layer_quantity
, market_value
INTO
l_current_total
, l_market_value
FROM
cst_pac_item_costs
WHERE pac_period_id = l_delta_period
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
UPDATE cst_pac_quantity_layers
SET
last_updated_by = p_user_id
, last_update_date = sysdate
, last_update_login = p_login_id
, request_id = p_req_id
, program_application_id = p_prg_appl_id
, program_id = p_prg_id
, program_update_date = sysdate
, layer_quantity = l_delta_quantity
WHERE pac_period_id = l_delta_period
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
UPDATE cst_pac_item_costs
SET
last_updated_by = p_user_id
,last_update_date = sysdate
,last_update_login = p_login_id
,request_id = p_req_id
,program_application_id = p_prg_appl_id
,program_id = p_prg_id
,program_update_date = sysdate
,total_layer_quantity = l_current_total
WHERE pac_period_id = l_delta_period
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
UPDATE cst_pac_quantity_layers
SET
last_updated_by = p_user_id
, last_update_date = sysdate
, last_update_login = p_login_id
, request_id = p_req_id
, program_application_id = p_prg_appl_id
, program_id = p_prg_id
, program_update_date = sysdate
, layer_quantity = l_delta_quantity
WHERE pac_period_id = l_delta_period
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
SELECT
cpic.buy_quantity
, cpic.item_buy_cost
, cpic.make_quantity
, cpic.item_make_cost
, cpic.inventory_item_id
, cpic.market_value
INTO
buy_quantity
, item_buy_cost
, make_quantity
, item_make_cost
, inventory_item_id
, l_market_value
FROM
cst_pac_item_costs cpic
WHERE cpic.pac_period_id = l_current_period
AND cpic.inventory_item_id = p_item_id
AND cpic.cost_group_id = p_cost_group_id;
SELECT
cpql.layer_quantity
, l_market_value
INTO
l_quantity_layers
, l_wac_cost
FROM
cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
WHERE cpic.pac_period_id = cpql.pac_period_id
AND cpic.cost_group_id = cpql.cost_group_id
AND cpic.inventory_item_id = cpql.inventory_item_id
AND cpic.pac_period_id = l_current_period
AND cpic.inventory_item_id = p_item_id
AND cpic.cost_group_id = p_cost_group_id;
SELECT
cpql.layer_quantity
, DECODE((cpic.buy_quantity + cpic.make_quantity)
, 0, 0
, (cpic.buy_quantity * cpic.item_buy_cost +
cpic.make_quantity * cpic.item_make_cost)/
(cpic.buy_quantity + cpic.make_quantity)
)
INTO
l_quantity_layers
, l_wac_cost
FROM
cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
WHERE cpic.pac_period_id = cpql.pac_period_id
AND cpic.cost_group_id = cpql.cost_group_id
AND cpic.inventory_item_id = cpql.inventory_item_id
AND cpic.pac_period_id = l_current_period
AND cpic.inventory_item_id = p_item_id
AND cpic.cost_group_id = p_cost_group_id;
SELECT
cpql.layer_quantity
, DECODE((cpic.buy_quantity + cpic.make_quantity)
, 0, 0
, (cpic.buy_quantity * cpic.item_buy_cost +
cpic.make_quantity * cpic.item_make_cost)/
(cpic.buy_quantity + cpic.make_quantity))
, cpic.market_value
INTO
l_y_quant
, l_wac_cost
, j_market_value
FROM cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
WHERE cpic.pac_period_id = cpql.pac_period_id
AND cpic.cost_group_id = cpql.cost_group_id
AND cpic.inventory_item_id = cpql.inventory_item_id
AND cpic.pac_period_id = l_delta_period
AND cpic.inventory_item_id = p_item_id
AND cpic.cost_group_id = p_cost_group_id;
SELECT
total_layer_quantity
INTO
l_total_quantity
FROM cst_pac_item_costs
WHERE pac_period_id = l_current_period
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
SELECT rowid
,market_value
,justification
INTO l_rowid
,l_market_value
,l_justification
FROM cst_pac_item_costs
WHERE pac_period_id = l_current_period
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
CST_PAC_ITEM_COSTS_PKG.update_row( l_rowid_char
, l_lifo_cost
, l_market_value
, l_justification
, sysdate
, p_user_id
);
SELECT
inventory_item_id
FROM
cst_pac_item_costs
WHERE pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id;
SELECT
inventory_item_id
FROM
cst_pac_item_costs
WHERE pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id BETWEEN p_item_from AND p_item_to;
SELECT
market_value
INTO
l_market_value
FROM
cst_pac_item_costs
WHERE pac_period_id = p_pac_period_id
AND inventory_item_id = l_current_item
AND cost_group_id = p_cost_group_id;
INSERT into CSTGILEV_TEMP(
item_id
, item_desc
, period_id
, period_name
, wac
, lifo_cost
, layer_quantity
, total_layer_quantity
, item_code
, uom_code
, inventory_value)
SELECT
pic.inventory_item_id
, msi.description
, pp.pac_period_id
, pp.period_name
, DECODE((pic.make_quantity+pic.buy_quantity)
, 0, 0
,(pic.item_make_cost*pic.make_quantity +
pic.item_buy_cost*pic.buy_quantity)/
( pic.make_quantity+pic.buy_quantity))
, pic.item_cost
, pql.layer_quantity
, pic.total_layer_quantity
, kfv.concatenated_segments
, msi.primary_uom_code
, (pic.item_cost * pic.total_layer_quantity)
FROM
cst_cost_groups cg
, cst_le_cost_types clt
, cst_pac_periods pp
, cst_pac_item_costs pic
, cst_pac_quantity_layers pql
, mtl_system_items msi
, mtl_system_items_kfv kfv
WHERE clt.legal_entity = p_legal_entity_id
AND pp.legal_entity = clt.legal_entity
AND cg.legal_entity = clt.legal_entity
AND cg.cost_group_id = p_cost_group_id
AND pp.pac_period_id = p_pac_period_id
AND clt.cost_type_id = pp.cost_type_id
AND clt.cost_type_id = p_cost_type_id
AND pic.cost_group_id = cg.cost_group_id
AND pic.pac_period_id = p_pac_period_id
AND pql.pac_period_id = pic.pac_period_id
AND pql.cost_group_id = pic.cost_group_id
AND pql.inventory_item_id = l_current_item
AND msi.inventory_item_id = l_current_item
AND msi.organization_id = cg.organization_id
AND kfv.inventory_item_id = l_current_item
AND pic.inventory_item_id = kfv.inventory_item_id
AND kfv.organization_id = p_master_org
AND pic.cost_layer_id = pql.cost_layer_id;
INSERT into CSTGILEV_TEMP(
item_id
, item_desc
, period_id
, period_name
, wac
, lifo_cost
, market_value
, layer_quantity
, total_layer_quantity
, item_code
, uom_code
, inventory_value)
SELECT
pic.inventory_item_id
, msi.description
, pp.pac_period_id
, pp.period_name
, DECODE((pic.make_quantity+pic.buy_quantity)
, 0, 0
,(pic.item_make_cost*pic.make_quantity +
pic.item_buy_cost*pic.buy_quantity)/
( pic.make_quantity+pic.buy_quantity))
, pic.item_cost
, pic.market_value
, pql.layer_quantity
, pic.total_layer_quantity
, kfv.concatenated_segments
, msi.primary_uom_code
, (pic.market_value * pic.total_layer_quantity)
FROM
cst_cost_groups cg
, cst_le_cost_types clt
, cst_pac_periods pp
, cst_pac_item_costs pic
, cst_pac_quantity_layers pql
, mtl_system_items msi
, mtl_system_items_kfv kfv
WHERE clt.legal_entity = p_legal_entity_id
AND pp.legal_entity = clt.legal_entity
AND cg.legal_entity = clt.legal_entity
AND cg.cost_group_id = p_cost_group_id
AND pp.pac_period_id = p_pac_period_id
AND clt.cost_type_id = pp.cost_type_id
AND clt.cost_type_id = p_cost_type_id
AND pic.cost_group_id = cg.cost_group_id
AND pic.pac_period_id = p_pac_period_id
AND pql.pac_period_id = pic.pac_period_id
AND pql.cost_group_id = pic.cost_group_id
AND pql.inventory_item_id = l_current_item
AND msi.inventory_item_id = l_current_item
AND msi.organization_id = cg.organization_id
AND kfv.inventory_item_id = l_current_item
AND pic.inventory_item_id = kfv.inventory_item_id
AND kfv.organization_id = p_master_org
AND pic.cost_layer_id = pql.cost_layer_id;
UPDATE CSTGILEV_TEMP
SET total_inventory_value =
(SELECT SUM(inventory_value)
FROM cstgilev_temp
WHERE period_id = p_pac_period_id)
WHERE period_id = p_pac_period_id
AND item_id = l_current_item;
SELECT
inventory_item_id
FROM
cst_pac_item_costs
WHERE pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id
AND inventory_item_id BETWEEN p_item_from AND p_item_to;
SELECT
market_value
INTO
l_market_value
FROM
cst_pac_item_costs
WHERE pac_period_id = g_period_tab(g_current_period_index)
AND inventory_item_id = l_current_item
AND cost_group_id = p_cost_group_id;
INSERT into CSTGILEV_TEMP(
item_id
, item_desc
, period_id
, period_name
, wac
, layer_quantity
, total_layer_quantity
, item_code
, uom_code
, inventory_value)
SELECT
pic.inventory_item_id
, msi.description
, pp.pac_period_id
, pp.period_name
, DECODE((pic.make_quantity+pic.buy_quantity)
, 0, 0
,(pic.item_make_cost*pic.make_quantity +
pic.item_buy_cost*pic.buy_quantity)/
( pic.make_quantity+pic.buy_quantity))
, pql.layer_quantity
, pic.total_layer_quantity
, kfv.concatenated_segments
, msi.primary_uom_code
, (pic.item_cost * pic.total_layer_quantity)
FROM
cst_cost_groups cg
, cst_le_cost_types clt
, cst_pac_periods pp
, cst_pac_item_costs pic
, cst_pac_quantity_layers pql
, mtl_system_items msi
, mtl_system_items_kfv kfv
WHERE clt.legal_entity = p_legal_entity_id
AND pp.legal_entity = clt.legal_entity
AND cg.legal_entity = clt.legal_entity
AND cg.cost_group_id = p_cost_group_id
AND pp.pac_period_id = g_period_tab(g_current_period_index)
AND clt.cost_type_id = pp.cost_type_id
AND clt.cost_type_id = p_cost_type_id
AND pic.cost_group_id = cg.cost_group_id
AND pic.pac_period_id = g_period_tab(g_current_period_index)
AND pql.pac_period_id = g_period_tab(g_current_period_index)
AND pql.cost_group_id = pic.cost_group_id
AND pql.inventory_item_id = l_current_item
AND msi.inventory_item_id = pql.inventory_item_id
AND msi.organization_id = cg.organization_id
AND kfv.inventory_item_id = msi.inventory_item_id
AND pic.inventory_item_id = kfv.inventory_item_id
AND kfv.organization_id = p_master_org
AND pic.cost_layer_id = pql.cost_layer_id;
INSERT into CSTGILEV_TEMP(
item_id
, item_desc
, period_id
, period_name
, wac
, market_value
, justification
, layer_quantity
, total_layer_quantity
, item_code
, uom_code
, inventory_value)
SELECT
pic.inventory_item_id
, msi.description
, pp.pac_period_id
, pp.period_name
, pic.market_value
, pic.market_value
, pic.justification
, pql.layer_quantity
, pic.total_layer_quantity
, kfv.concatenated_segments
, msi.primary_uom_code
, (pic.market_value * pic.total_layer_quantity)
FROM
cst_cost_groups cg
, cst_le_cost_types clt
, cst_pac_periods pp
, cst_pac_item_costs pic
, cst_pac_quantity_layers pql
, mtl_system_items msi
, mtl_system_items_kfv kfv
WHERE clt.legal_entity = p_legal_entity_id
AND pp.legal_entity = clt.legal_entity
AND cg.legal_entity = clt.legal_entity
AND cg.cost_group_id = p_cost_group_id
AND pp.pac_period_id = g_period_tab(g_current_period_index)
AND clt.cost_type_id = pp.cost_type_id
AND clt.cost_type_id = p_cost_type_id
AND pic.cost_group_id = cg.cost_group_id
AND pic.pac_period_id = g_period_tab(g_current_period_index)
AND pql.pac_period_id = g_period_tab(g_current_period_index)
AND pql.cost_group_id = pic.cost_group_id
AND pql.inventory_item_id = l_current_item
AND msi.inventory_item_id = pql.inventory_item_id
AND msi.organization_id = cg.organization_id
AND kfv.inventory_item_id = msi.inventory_item_id
AND pic.inventory_item_id = kfv.inventory_item_id
AND kfv.organization_id = p_master_org
AND pic.cost_layer_id = pql.cost_layer_id;
INSERT into CSTGILEV_TEMP(
item_id
, item_desc
, period_id
, period_name
, wac
, lifo_cost
, justification
, layer_quantity
, total_layer_quantity
, item_code
, uom_code
, inventory_value)
SELECT
pic.inventory_item_id
, msi.description
, pp.pac_period_id
, pp.period_name
, DECODE((pic.make_quantity+pic.buy_quantity)
, 0, 0
,(pic.item_make_cost*pic.make_quantity +
pic.item_buy_cost*pic.buy_quantity)/
( pic.make_quantity+pic.buy_quantity))
, pic.item_cost lifo_cost
, pic.justification
, pql.layer_quantity
, pic.total_layer_quantity
, kfv.concatenated_segments
, msi.primary_uom_code
, (pic.item_cost * pic.total_layer_quantity)
FROM
cst_cost_groups cg
, cst_le_cost_types clt
, cst_pac_periods pp
, cst_pac_item_costs pic
, cst_pac_quantity_layers pql
, mtl_system_items msi
, mtl_system_items_kfv kfv
WHERE clt.legal_entity = p_legal_entity_id
AND pp.legal_entity = clt.legal_entity
AND cg.legal_entity = clt.legal_entity
AND cg.cost_group_id = p_cost_group_id
AND pp.pac_period_id = p_pac_period_id
AND clt.cost_type_id = pp.cost_type_id
AND clt.cost_type_id = p_cost_type_id
AND pic.cost_group_id = cg.cost_group_id
AND pic.pac_period_id = p_pac_period_id
AND pql.pac_period_id = pic.pac_period_id
AND pql.cost_group_id = pic.cost_group_id
AND pql.inventory_item_id = l_current_item
AND msi.inventory_item_id = pql.inventory_item_id
AND msi.organization_id = cg.organization_id
AND kfv.inventory_item_id = msi.inventory_item_id
AND pic.inventory_item_id = kfv.inventory_item_id
AND kfv.organization_id = p_master_org
AND pic.cost_layer_id = pql.cost_layer_id;
INSERT into CSTGILEV_TEMP(
item_id
, item_desc
, period_id
, period_name
, wac
, lifo_cost
, market_value
, justification
, layer_quantity
, total_layer_quantity
, item_code
, uom_code
, inventory_value)
SELECT
pic.inventory_item_id
, msi.description
, pp.pac_period_id
, pp.period_name
, DECODE((pic.make_quantity+pic.buy_quantity)
, 0, 0
,(pic.item_make_cost*pic.make_quantity +
pic.item_buy_cost*pic.buy_quantity)/
( pic.make_quantity+pic.buy_quantity))
, pic.item_cost lifo_cost
, pic.market_value
, pic.justification
, pql.layer_quantity
, pic.total_layer_quantity
, kfv.concatenated_segments
, msi.primary_uom_code
, (pic.market_value * pic.total_layer_quantity)
FROM
cst_cost_groups cg
, cst_le_cost_types clt
, cst_pac_periods pp
, cst_pac_item_costs pic
, cst_pac_quantity_layers pql
, mtl_system_items msi
, mtl_system_items_kfv kfv
WHERE clt.legal_entity = p_legal_entity_id
AND pp.legal_entity = clt.legal_entity
AND cg.legal_entity = clt.legal_entity
AND cg.cost_group_id = p_cost_group_id
AND pp.pac_period_id = p_pac_period_id
AND clt.cost_type_id = pp.cost_type_id
AND clt.cost_type_id = p_cost_type_id
AND pic.cost_group_id = cg.cost_group_id
AND pic.pac_period_id = p_pac_period_id
AND pql.pac_period_id = pic.pac_period_id
AND pql.cost_group_id = pic.cost_group_id
AND pql.inventory_item_id = l_current_item
AND msi.inventory_item_id = pql.inventory_item_id
AND msi.organization_id = cg.organization_id
AND kfv.inventory_item_id = msi.inventory_item_id
AND pic.inventory_item_id = kfv.inventory_item_id
AND kfv.organization_id = p_master_org
AND pic.cost_layer_id = pql.cost_layer_id;
UPDATE CSTGILEV_TEMP
SET total_inventory_value =
(SELECT
inventory_value
FROM cstgilev_temp
WHERE period_id = p_pac_period_id
AND item_id = l_current_item)
WHERE period_id = p_pac_period_id
AND item_id = l_current_item;
SELECT organization_id
INTO l_master_org
FROM cst_cost_groups
WHERE cost_group_id = p_cost_group_id;
SELECT
min(inventory_item_id)
, max(inventory_item_id)
INTO
l_item_from
, l_item_to
FROM
cst_pac_item_costs
WHERE cost_group_id = p_cost_group_id
AND pac_period_id = p_pac_period_id;
SELECT
inventory_item_id
INTO
l_item_from
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_code_from
AND organization_id = l_master_org;
SELECT
inventory_item_id
INTO
l_item_to
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_code_to
AND organization_id = l_master_org;
SELECT cpp.pac_period_id
INTO p_pac_period_id
FROM cst_pac_periods cpp
WHERE (cpp.period_name, cpp.cost_type_id) =
( SELECT cpici.period_name,
cct.cost_type_id
FROM cst_pc_item_cost_interface cpici,
cst_cost_types cct
WHERE cpici.interface_header_id = p_interface_header_id
AND cpici.cost_type = cct.cost_type
)
AND cpp.open_flag = 'N';
UPDATE cst_pc_item_cost_interface cpici
SET cpici.pac_period_id = p_pac_period_id
WHERE cpici.interface_header_id = p_interface_header_id;
SELECT count(*)
INTO l_count
FROM cst_pc_item_cost_interface cpici
WHERE cpici.pac_period_id = p_pac_period_id
AND (cpici.inventory_item_id,cpici.cost_group,cpici.cost_type) IN
(
SELECT inventory_item_id, cost_group , cost_type
FROM cst_pc_item_cost_interface
WHERE interface_header_id = p_interface_header_id
);
SELECT interface_header_id,
cost_group,
cost_type,
inventory_item_id,
begin_layer_quantity,
layer_quantity
FROM cst_pc_item_cost_interface
WHERE interface_group_id = a_interface_group_id
ORDER BY cost_group, cost_type, inventory_item_id, pac_period_id;
SELECT clct.primary_cost_method
INTO l_primary_cost_method
FROM cst_cost_types cct,
cst_le_cost_types clct
WHERE cct.cost_type_id = clct.cost_type_id
AND clct.legal_entity =
(SELECT DISTINCT ccg.legal_entity
FROM cst_cost_groups ccg,
cst_cost_group_assignments ccga
WHERE ccg.cost_group_id = ccga.cost_group_id
AND ccg.cost_group_type = 2
AND ccg.cost_group IN
(SELECT cpici.cost_group
FROM cst_pc_item_cost_interface cpici
WHERE cpici.interface_header_id =
RECINTERFACE.interface_header_id
)
)
AND cct.cost_type IN
(SELECT cpici.cost_type
FROM cst_pc_item_cost_interface cpici
WHERE cpici.interface_header_id =
RECINTERFACE.interface_header_id
);
SELECT cpici.begin_layer_quantity
INTO l_initial_quantity
FROM cst_pc_item_cost_interface cpici
WHERE cpici.interface_header_id =
RECINTERFACE.interface_header_id;
UPDATE cst_pc_item_cost_interface
SET process_flag = 3,
error_flag = 22,
error_explanation = 'Begin layer quantity not correct for LIFO record'
WHERE interface_header_id = RECINTERFACE.interface_header_id;
SELECT pac_period_id,
cost_group_id,
cost_type,
inventory_item_id,
interface_header_id
FROM cst_pc_item_cost_interface
WHERE interface_group_id = a_interface_group_id
ORDER BY cost_group, cost_type, inventory_item_id, pac_period_id;
SELECT clct.primary_cost_method
INTO l_primary_cost_method
FROM cst_cost_types cct,
cst_le_cost_types clct
WHERE cct.cost_type_id = clct.cost_type_id
AND clct.legal_entity =
(SELECT DISTINCT ccg.legal_entity
FROM cst_cost_groups ccg,
cst_cost_group_assignments ccga
WHERE ccg.cost_group_id = ccga.cost_group_id
AND ccg.cost_group_type = 2
AND ccg.cost_group IN
(SELECT cpici.cost_group
FROM cst_pc_item_cost_interface cpici
WHERE cpici.interface_header_id =
RECINTERFACE.interface_header_id
)
)
AND cct.cost_type IN
(SELECT cpici.cost_type
FROM cst_pc_item_cost_interface cpici
WHERE cpici.interface_header_id =
RECINTERFACE.interface_header_id
);
SELECT cost_type_id
INTO l_cost_type_id
FROM cst_cost_types
WHERE cost_type = RECINTERFACE.cost_type;
PROCEDURE selective_purge
( p_legal_entity_id IN NUMBER
, p_pac_period_id IN NUMBER
, p_first_period_id IN NUMBER
, p_cost_group_id IN NUMBER
, p_cost_type_id IN NUMBER
, p_item_id IN NUMBER
)
IS
--=================
-- CURSORS
--=================
-- cursor to get market value
CURSOR get_market_value_cur(c_period_id NUMBER
,c_cost_group_id NUMBER
,c_item_id NUMBER)
IS
SELECT
market_value
FROM CST_PAC_ITEM_COSTS
WHERE pac_period_id = c_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_item_id;
SELECT
begin_layer_quantity
FROM CST_PAC_QUANTITY_LAYERS
WHERE pac_period_id = c_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_item_id;
SELECT
layer_quantity
FROM CST_PAC_QUANTITY_LAYERS
WHERE pac_period_id = c_period_id
AND inventory_item_id = c_item_id
AND cost_group_id = c_cost_group_id;
SELECT
period_end_date
FROM CST_PAC_PERIODS
WHERE pac_period_id = c_period_id;
SELECT
COUNT(*)
FROM CST_PAC_ITEM_COSTS
WHERE cost_group_id = c_cost_group_id
AND inventory_item_id = c_item_id
AND pac_period_id IN (SELECT pac_period_id
FROM CST_PAC_PERIODS
WHERE period_end_date
< c_first_period_end_date);
,'Start of Proc:Selective Purge'
);
DELETE CST_PAC_ITEM_COSTS
WHERE cost_group_id = p_cost_group_id
AND inventory_item_id = p_item_id
AND pac_period_id IN (SELECT pac_period_id
FROM CST_PAC_PERIODS
WHERE period_end_date
< l_first_period_end_date);
DELETE CST_PAC_QUANTITY_LAYERS
WHERE cost_group_id = p_cost_group_id
AND inventory_item_id = p_item_id
AND pac_period_id IN (SELECT pac_period_id
FROM CST_PAC_PERIODS
WHERE period_end_date
< l_first_period_end_date);
COMMIT; -- Deleted all the prior item cost layers with commit size
DELETE CST_PAC_ITEM_COSTS
WHERE pac_period_id = l_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
DELETE CST_PAC_QUANTITY_LAYERS
WHERE pac_period_id = l_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
UPDATE CST_PAC_ITEM_COSTS
SET total_layer_quantity = total_layer_quantity + l_layer_quantity
WHERE pac_period_id = l_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
UPDATE CST_PAC_QUANTITY_LAYERS
SET layer_quantity = layer_quantity + l_layer_quantity
WHERE pac_period_id = l_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
DELETE CST_PAC_ITEM_COSTS
WHERE pac_period_id = p_first_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
DELETE CST_PAC_QUANTITY_LAYERS
WHERE pac_period_id = p_first_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
UPDATE CST_PAC_QUANTITY_LAYERS
SET begin_layer_quantity = 0,
layer_quantity = (SELECT total_layer_quantity
FROM CST_PAC_ITEM_COSTS
WHERE pac_period_id = p_first_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id)
WHERE pac_period_id = p_first_period_id
AND inventory_item_id = p_item_id
AND cost_group_id = p_cost_group_id;
COMMIT; -- deleted all the item cost layers
,'End of Proc:Selective Purge'
);
,'selective_purge'
);
END selective_purge;
SELECT
ccg.organization_id,
HOU.name
FROM CST_COST_GROUPS ccg,
HR_ORGANIZATION_UNITS HOU,
HR_ORGANIZATION_INFORMATION HOI
WHERE HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION1 = 'INV'
AND HOI.ORG_INFORMATION2 = 'Y'
AND ( HOI.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
AND ccg.cost_group_id = c_cost_group_id
AND ccg.cost_group_type = 2
AND ccg.organization_id = HOU.organization_id;
SELECT
msi.concatenated_segments item_number,
msi.inventory_item_id
FROM
CST_PAC_PERIODS pp
,CST_PAC_ITEM_COSTS pic
,MTL_SYSTEM_ITEMS_KFV msi
,MTL_ITEM_CATEGORIES mic
,MTL_CATEGORIES_KFV mc
WHERE pp.legal_entity = c_legal_entity_id
AND pic.cost_group_id = c_cost_group_id
AND pp.cost_type_id = c_cost_type_id
AND pp.pac_period_id = c_pac_period_id
AND pic.pac_period_id = pp.pac_period_id
AND pic.inventory_item_id = msi.inventory_item_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND mic.category_id = mc.category_id
AND mc.structure_id = c_category_struct
AND mc.concatenated_segments
BETWEEN nvl(c_category_from,mc.concatenated_segments)
AND nvl(c_category_to,mc.concatenated_segments)
AND msi.concatenated_segments
BETWEEN nvl(c_item_number_from,msi.concatenated_segments)
AND nvl(c_item_number_to,msi.concatenated_segments)
AND msi.organization_id = c_master_org_id
ORDER BY msi.concatenated_segments;
SELECT
total_layer_quantity
INTO
l_total_quantity
FROM
cst_pac_item_costs
WHERE pac_period_id = l_first_period_id
AND inventory_item_id = l_inventory_item_id
AND cost_group_id = p_cost_group_id;
CST_MGD_LIFO_COST_PROCESSOR.selective_purge(p_legal_entity_id
,p_pac_period_id
,l_first_period_id
,p_cost_group_id
,p_cost_type_id
,l_inventory_item_id);
,'Selective LIFO Purge Successful'
);
,'Selective LIFO Purge Failed'
);