[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_id,
completion_transaction_id,
transaction_source_id,
repetitive_line_id,
inventory_item_id,
organization_id,
flow_schedule
FROM mtl_material_transactions mmt
WHERE transaction_source_type_id = 5 /* job or schedule */
AND transaction_action_id IN (30, 31, 32) /* scrap,completion,return */
AND transaction_date BETWEEN trunc(c_start_date)
AND (trunc(c_end_date) + 0.99999)
AND EXISTS(
SELECT 'exists'
FROM cst_cost_group_assignments
WHERE cost_group_id = c_cost_group_id
AND organization_id = mmt.organization_id);
The (-1, inventory_item_id) combination inserted will signify that there
has been a completion/scrap/return for the item
------------------------------------------------------------------------*/
INSERT INTO
cst_pac_explosion_temp (
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT i_pac_period_id,
i_cost_group_id,
-1,
comp_rec.inventory_item_id,
'N',
1000
FROM dual
WHERE NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = -1
AND component_item_id = comp_rec.inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id);
INSERT INTO
cst_pac_explosion_temp(
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT DISTINCT
i_pac_period_id,
i_cost_group_id,
comp_rec.inventory_item_id,
wro.inventory_item_id,
'N',
1000
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = comp_rec.transaction_source_id
AND NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = comp_rec.inventory_item_id
AND component_item_id = inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id);
INSERT INTO
cst_pac_explosion_temp(
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT DISTINCT
i_pac_period_id,
i_cost_group_id,
comp_rec.inventory_item_id,
mmt.inventory_item_id,
'N',
1000
FROM mtl_material_transactions mmt
WHERE transaction_source_id = comp_rec.transaction_source_id
AND transaction_source_type_id = 5
AND transaction_action_id IN (1,27,33,34)
AND transaction_date BETWEEN trunc(i_start_date)
AND (trunc(i_end_date) + 0.99999)
AND NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = comp_rec.inventory_item_id
AND component_item_id = inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id)
GROUP BY
mmt.inventory_item_id
HAVING sum(mmt.primary_quantity) <> 0;
INSERT INTO
cst_pac_explosion_temp(
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT DISTINCT
i_pac_period_id,
i_cost_group_id,
comp_rec.inventory_item_id,
wro.inventory_item_id,
'N',
1000
FROM mtl_material_txn_allocations mmta ,
wip_requirement_operations wro
WHERE mmta.transaction_id = comp_rec.transaction_id
AND mmta.repetitive_schedule_id = wro.repetitive_schedule_id
AND wro.wip_entity_id = comp_rec.transaction_source_id
AND NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = comp_rec.inventory_item_id
AND component_item_id = inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id);
INSERT INTO
cst_pac_explosion_temp(
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT DISTINCT
i_pac_period_id,
i_cost_group_id,
comp_rec.inventory_item_id,
mmt.inventory_item_id,
'N',
1000
FROM mtl_material_transactions mmt
WHERE transaction_source_id = comp_rec.transaction_source_id
AND repetitive_line_id = comp_rec.repetitive_line_id
AND transaction_source_type_id = 5
AND transaction_action_id IN (1,27,33,34)
AND transaction_date BETWEEN trunc(i_start_date)
AND (trunc(i_end_date) + 0.99999)
AND NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = comp_rec.inventory_item_id
AND component_item_id = inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id)
GROUP BY
mmt.inventory_item_id
HAVING sum(mmt.primary_quantity) <> 0;
INSERT INTO
cst_pac_explosion_temp(
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT DISTINCT
i_pac_period_id,
i_cost_group_id,
comp_rec.inventory_item_id,
mmt.inventory_item_id,
'N',
1000
FROM mtl_material_transactions mmt
WHERE mmt.completion_transaction_id =
comp_rec.completion_transaction_id
AND transaction_date BETWEEN trunc(i_start_date)
AND (trunc(i_end_date) + 0.99999)
AND mmt.transaction_action_id in (1,27,33,34)
AND NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = comp_rec.inventory_item_id
AND component_item_id = inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id);
INSERT INTO
cst_pac_explosion_temp(
pac_period_id,
cost_group_id,
assembly_item_id,
component_item_id,
deleted,
loop_count)
SELECT DISTINCT
i_pac_period_id,
i_cost_group_id,
comp_rec.inventory_item_id,
mmt.inventory_item_id,
'N',
1000
FROM mtl_material_transactions mmt
WHERE mmt.completion_transaction_id =
comp_rec.completion_transaction_id
AND transaction_date BETWEEN trunc(i_start_date)
AND (trunc(i_end_date) + 0.99999)
AND mmt.transaction_action_id in (1,27,33,34)
AND NOT EXISTS (
SELECT 'exists'
FROM cst_pac_explosion_temp
WHERE assembly_item_id = comp_rec.inventory_item_id
AND component_item_id = inventory_item_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id)
GROUP BY
mmt.inventory_item_id
HAVING sum(primary_quantity) <> 0;
CURSOR C_comp_not_deleted (
c_pac_period_id IN NUMBER,
c_cost_group_id IN NUMBER)
IS
SELECT DISTINCT
component_item_id cii
FROM cst_pac_explosion_temp
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND deleted = 'N';
SELECT DISTINCT
msik1.inventory_item_id assembly_item_id,
msik1.concatenated_segments assembly_item,
msik2.inventory_item_id component_item_id,
msik2.concatenated_segments component_item,
we.wip_entity_id wip_entity_id,
we.wip_entity_name wip_entity
FROM cst_pac_explosion_temp cpet,
mtl_material_transactions mmt1,
mtl_material_transactions mmt2,
mtl_system_items_kfv msik1,
mtl_system_items_kfv msik2,
wip_entities we
WHERE cpet.pac_period_id = c_pac_period_id
AND cpet.cost_group_id = c_cost_group_id
AND cpet.deleted = 'N'
AND mmt1.inventory_item_id = cpet.assembly_item_id
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id IN (30,31,32)
AND mmt1.transaction_date BETWEEN trunc(c_start_date)
AND (trunc(c_end_date) + 0.99999)
AND (
SELECT count('exists')
FROM cst_cost_group_assignments
WHERE cost_group_id = c_cost_group_id
AND organization_id = mmt1.organization_id
and rownum < 2 ) > 0
AND mmt2.inventory_item_id = cpet.component_item_id
AND mmt2.transaction_action_id IN (1,27,33,34)
AND mmt2.transaction_date BETWEEN trunc(c_start_date)
AND (trunc(c_end_date) + 0.99999)
AND ( ( ( NVL(mmt1.flow_schedule,'N') <> 'Y')
AND mmt1.repetitive_line_id IS NULL
AND mmt2.transaction_source_id = mmt1.transaction_source_id
AND mmt2.transaction_source_type_id = 5)
OR ( ( NVL(mmt1.flow_schedule,'N') <> 'Y')
AND mmt1.repetitive_line_id IS NOT NULL
AND mmt2.transaction_source_id = mmt1.transaction_source_id
AND mmt2.repetitive_line_id = mmt1.repetitive_line_id
AND mmt2.transaction_source_type_id = 5)
OR ( ( NVL(mmt1.flow_schedule,'N') = 'Y')
AND mmt2.completion_transaction_id = mmt1.completion_transaction_id))
AND msik1.organization_id = mmt1.organization_id
AND msik1.inventory_item_id = mmt1.inventory_item_id
AND msik2.organization_id = msik2.organization_id
AND msik2.inventory_item_id = mmt2.inventory_item_id
AND we.wip_entity_id = mmt2.transaction_source_id
GROUP BY
msik1.inventory_item_id,
msik1.concatenated_segments,
msik2.inventory_item_id,
msik2.concatenated_segments,
we.wip_entity_id,
we.wip_entity_name
HAVING sum(mmt2.primary_quantity) <> 0;
l_update_flag BOOLEAN DEFAULT FALSE;
UPDATE cst_pac_explosion_temp
SET deleted = 'Y',
loop_count = l_iteration
WHERE component_item_id = assembly_item_id
AND deleted = 'N'
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id;
l_update_flag := FALSE;
OPEN C_comp_not_deleted(i_pac_period_id,i_cost_group_id);
FETCH C_comp_not_deleted INTO l_cii;
EXIT when C_comp_not_deleted%NOTFOUND;
parent. Need to only check with rows that have been updated by
previous iterations since components can occur at different levels
--------------------------------------------------------------------*/
l_stmt_num := 40;
UPDATE /*+ index(cet1 CST_PAC_EXPLOSION_TEMP_N1) */
cst_pac_explosion_temp cet1
SET deleted = 'Y',
loop_count = l_iteration
WHERE component_item_id = l_cii
AND deleted = 'N'
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND NOT EXISTS (
SELECT 'exists as a parent'
FROM cst_pac_explosion_temp cet2
WHERE cet2.assembly_item_id = l_cii
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND loop_count >= l_iteration);
if no rows are updated then it implies that the component exists as a
parent and should not for now be inserted into the CPLLC table
--------------------------------------------------------------------*/
l_stmt_num := 50;
l_update_flag := TRUE;
INSERT INTO
cst_pac_low_level_codes (
pac_period_id,
cost_group_id,
inventory_item_id,
low_level_code,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (
i_pac_period_id,
i_cost_group_id,
l_cii,
l_level_code,
sysdate,
-1,
sysdate,
-1);
CLOSE C_comp_not_deleted;
if "undeleted" rows still exist in cst_pac_explosion_temp and no rows
were updated, then this implies there is a LOOP.
----------------------------------------------------------------------*/
IF (NOT l_update_flag AND l_counter > 0) THEN
IF (i_method = 1) THEN
raise LOOP_WARNING;
completion/return/scrap. So we delete all leaf node items from cpllc. We
make sure that assembly items with completion/scrap/return are not
considered as leaf nodes even if they might appear with LLC of 1000
------------------------------------------------------------------------*/
DELETE FROM
cst_pac_low_level_codes cpllc
WHERE low_level_code = 1000
AND cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id
AND NOT EXISTS (
SELECT component_item_id FROM cst_pac_explosion_temp cpet
WHERE assembly_item_id = -1
AND component_item_id = cpllc.inventory_item_id
AND cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id);
DELETE FROM
cst_pac_low_level_codes
WHERE cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id;
DELETE FROM
cst_pac_explosion_temp
WHERE cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id;
DELETE FROM
cst_pac_low_level_codes
WHERE cost_group_id = i_cost_group_id
AND pac_period_id = i_pac_period_id;