[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mmt.transaction_source_id entity_id,
we.entity_type entity_type,
mmt.repetitive_line_id line_id,
mmt.primary_quantity pri_qty,
mmt.inventory_item_id item_id,
mmt.operation_seq_num op_seq,
mmt.organization_id,
mmt.subinventory_code subinv,
mmt.transaction_action_id
INTO l_entity_id,
l_entity_type,
l_line_id,
l_pri_qty,
l_item_id,
l_op_seq,
l_org_id,
l_subinv,
l_txn_action_id
FROM mtl_material_transactions mmt,
wip_entities we
WHERE mmt.transaction_id = p_txn_id
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (1,27, 33, 34)
AND we.wip_entity_id = mmt.transaction_source_id;
SELECT DECODE(l_zero_cost_flag,
0, nvl(cpic.item_cost,0), 0) * (-1 * l_pri_qty )
INTO l_applied_value
FROM wip_discrete_jobs wdj,
cst_pac_item_costs cpic
WHERE cpic.pac_period_id = p_pac_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = l_item_id
AND wdj.wip_entity_id = l_entity_id;
UPDATE wip_pac_period_balances wppb
SET
(pl_material_in,
pl_material_overhead_in,
pl_resource_in,
pl_outside_processing_in,
pl_overhead_in,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date) =
(
SELECT -- Checking p_zero_cost_flag for rebuild items as part of eAM support in PAC
NVL(wppb.pl_material_in,0) +
(DECODE(p_zero_cost_flag,0,NVL(cpic.material_cost,0),0) * (-1 * p_pri_qty)),
NVL(wppb.pl_material_overhead_in,0) +
(DECODE(p_zero_cost_flag,0,NVL(cpic.material_overhead_cost,0),0) * (-1 * p_pri_qty)),
NVL(wppb.pl_resource_in,0) +
(DECODE(p_zero_cost_flag,0,NVL(cpic.resource_cost,0),0) * (-1 * p_pri_qty)),
NVL(wppb.pl_outside_processing_in,0)+
(DECODE(p_zero_cost_flag,0,NVL(cpic.outside_processing_cost,0),0) * (-1 * p_pri_qty)),
NVL(wppb.pl_overhead_in,0) +
(DECODE(p_zero_cost_flag,0,NVL(cpic.overhead_cost,0),0) * (-1 * p_pri_qty)),
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM cst_pac_item_costs cpic
WHERE cpic.pac_period_id = p_pac_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = p_item_id
)
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = p_entity_id
AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
AND wppb.operation_seq_num = p_op_seq
AND EXISTS
( SELECT 'X'
FROM cst_pac_item_costs cpic2
WHERE cpic2.pac_period_id = p_pac_period_id
AND cpic2.cost_group_id = p_cost_group_id
AND cpic2.inventory_item_id = p_item_id);
UPDATE wip_pac_period_balances wppb
SET
(pl_material_in_apull,
pl_material_overhead_in_apull,
pl_resource_in_apull,
pl_outside_processing_in_apull,
pl_overhead_in_apull,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date) =
(
SELECT
NVL(wppb.pl_material_in_apull,0) +
(NVL(cpic.material_cost,0) * (-1 * p_pri_qty)),
NVL(wppb.pl_material_overhead_in_apull,0) +
(NVL(cpic.material_overhead_cost,0) * (-1 * p_pri_qty)),
NVL(wppb.pl_resource_in_apull,0) +
(NVL(cpic.resource_cost,0) * (-1 * p_pri_qty)),
NVL(wppb.pl_outside_processing_in_apull,0)+
(NVL(cpic.outside_processing_cost,0)* (-1 * p_pri_qty)),
NVL(wppb.pl_overhead_in_apull,0) +
(NVL(cpic.overhead_cost,0) * (-1 * p_pri_qty)),
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM cst_pac_item_costs cpic
WHERE cpic.pac_period_id = p_pac_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = p_item_id
)
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = p_entity_id
AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
AND wppb.operation_seq_num = p_op_seq
AND EXISTS
( SELECT 'X'
FROM cst_pac_item_costs cpic2
WHERE cpic2.pac_period_id = p_pac_period_id
AND cpic2.cost_group_id = p_cost_group_id
AND cpic2.inventory_item_id = p_item_id)
AND EXISTS
( SELECT 'ASSY PULL'
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = p_entity_id
AND wro.wip_supply_type = 2
AND wro.inventory_item_id = p_item_id
AND wro.operation_seq_num = p_op_seq);
USING (SELECT sum(NVL(cpicd.ITEM_COST,0) * -1 * p_pri_qty) cost,
(-1 * p_pri_qty) qty,
cpicd.cost_element_id cost_element_id
FROM CST_PAC_ITEM_COSTS cpic,
CST_PAC_ITEM_COST_DETAILS cpicd
WHERE cpic.pac_period_id = p_pac_period_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.inventory_item_id = p_item_id
AND cpic.cost_layer_id = cpicd.cost_layer_id
GROUP BY cpicd.cost_element_id ) s
ON ( cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id,-99) = nvl(p_line_id, -99)
AND cprocd.inventory_item_id = p_item_id
AND cprocd.operation_seq_num = p_op_seq
AND cprocd.pac_period_id = p_pac_period_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.cost_element_id = s.cost_element_id)
WHEN MATCHED THEN UPDATE SET cprocd.applied_value = nvl(cprocd.applied_value,0) + nvl(s.cost,0),
cprocd.applied_quantity = nvl( cprocd.applied_quantity,0) + nvl(s.qty,0),
cprocd.last_update_date = SYSDATE,
cprocd.last_updated_by = p_user_id,
cprocd.last_update_login = p_login_id,
cprocd.request_id = p_request_id,
cprocd.program_application_id = p_prog_app_id,
cprocd.program_id = p_prog_id,
cprocd.program_update_date = SYSDATE
WHEN NOT MATCHED THEN INSERT ( pac_period_id,
cost_group_id,
wip_entity_id,
line_id,
inventory_item_id,
cost_element_id,
operation_seq_num,
applied_value,
applied_quantity,
relieved_value,
relieved_quantity,
comp_variance,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (p_pac_period_id,
p_cost_group_id,
p_entity_id,
p_line_id,
p_item_id,
s.cost_element_id,
p_op_seq,
s.cost,
s.qty,
0,
0,
0,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE);