[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insert_wip_costs |
*----------------------------------------------------------------------------*/
PROCEDURE insert_wip_costs (
p_pac_period_id IN NUMBER,
p_prior_period_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_cost_type_id IN NUMBER,
p_item_id IN NUMBER,
p_entity_id IN NUMBER,
p_line_id IN NUMBER,
p_txn_id IN NUMBER,
p_net_qty IN NUMBER,
p_completed_assembly_qty IN NUMBER,
p_user_id IN NUMBER,
p_final_completion_flag IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER DEFAULT -1,
p_prog_app_id IN NUMBER DEFAULT -1,
x_err_num OUT NOCOPY NUMBER,
x_err_code OUT NOCOPY VARCHAR2,
x_err_msg OUT NOCOPY VARCHAR2)
IS
l_stmt_num NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'insert_wip_costs';
SELECT mmt.transaction_action_id,
mmt.transaction_source_type_id ,
mmt.primary_quantity
INTO l_transaction_action_id,
l_transaction_source_type_id,
l_primary_quantity
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_txn_id;
SELECT COUNT(wppb.PAC_PERIOD_ID), Max(Nvl(relieved_assembly_units,0))
INTO l_job, l_prior_completed_units
FROM WIP_PAC_PERIOD_BALANCES wppb
WHERE wppb.WIP_ENTITY_ID = p_entity_id
AND wppb.PAC_PERIOD_ID = p_prior_period_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
INSERT ALL
WHEN pp_pl_material_out <> 0 THEN
-- Previous Level and Material cost element
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
1,
2,
pp_pl_material_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- Previous Level and Material Overhead cost element
WHEN pp_pl_material_overhead_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
2,
2,
pp_pl_material_overhead_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- Previous Level and Resource cost element
WHEN pp_pl_resource_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
3,
2,
pp_pl_resource_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- Previous Level and Outsideprocessing cost element
WHEN pp_pl_outside_processing_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
4,
2,
pp_pl_outside_processing_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- Previous Level and Overhead cost element
WHEN pp_pl_overhead_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
5,
2,
pp_pl_overhead_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- This Level and Resource cost element
WHEN pp_tl_resource_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
3,
1,
pp_tl_resource_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- This Level and Overhead cost element
WHEN pp_tl_overhead_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
5,
1,
pp_tl_overhead_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- This Level and Outsideprocessing cost element
WHEN pp_tl_outside_processing_out <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
4,
1,
pp_tl_outside_processing_out,
0, -- New column value
SYSDATE,
p_user,
SYSDATE,
p_user,
p_request,
p_prog_app,
p_prog,
SYSDATE,
p_login)
-- Create 0 TL Material instead of 0 PL Material when there is no non-zero cost details
WHEN (pp_pl_material_out = 0 AND pp_pl_material_overhead_out = 0 AND pp_pl_resource_out = 0 AND
pp_pl_outside_processing_out = 0 AND pp_pl_overhead_out = 0 AND pp_tl_resource_out = 0 AND
pp_tl_outside_processing_out = 0 AND pp_tl_overhead_out = 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
1, -- Material Cost Element
1, -- This Level
0,-- Zero Cost
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
SELECT p_pac_period_id p_pac_period,
p_cost_group_id p_cost_group,
p_cost_type_id p_cost_type,
p_item_id p_item,
p_txn_id p_txn,
SUM(nvl(wppb.pl_material_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_material_out,
SUM(nvl(wppb.pl_material_overhead_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_material_overhead_out,
SUM(nvl(wppb.pl_resource_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_resource_out,
SUM(nvl(wppb.pl_outside_processing_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_outside_processing_out,
SUM(nvl(wppb.pl_overhead_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_pl_overhead_out,
SUM(nvl(wppb.tl_resource_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_tl_resource_out,
SUM(nvl(wppb.tl_outside_processing_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_tl_outside_processing_out,
SUM(nvl(wppb.tl_overhead_out/
decode(nvl(relieved_assembly_units,0),0,1,relieved_assembly_units),0)) pp_tl_overhead_out,
p_user_id p_user,
p_login_id p_login,
p_request_id p_request,
p_prog_app_id p_prog_app,
p_prog_id p_prog
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_prior_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) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
/* INSERT INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period_id,
p_cost_group_id,
p_cost_type_id,
p_item_id,
p_txn_id,
1, -- Material Cost Element
1, -- This Level
0,-- Zero Cost
sysdate,
p_user_id,
sysdate,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
p_login_id);*/
INSERT ALL
-- Previous Level and Material cost element
WHEN (pl_material_temp <> 0 OR pl_material_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
1,
2,
pl_material_temp,
pl_material_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Material Overhead cost element
WHEN (pl_material_overhead_temp <> 0 OR pl_material_overhead_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
2,
2,
pl_material_overhead_temp,
pl_material_overhead_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Resource cost element
WHEN (pl_resource_temp <> 0 OR pl_resource_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
3,
2,
pl_resource_temp,
pl_resource_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Outside Processing cost element
WHEN (pl_outside_processing_temp <>0 OR pl_outside_processing_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
4,
2,
pl_outside_processing_temp,
pl_outside_processing_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Overhead cost element
WHEN (pl_overhead_temp <>0 OR pl_overhead_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
5,
2,
pl_overhead_temp,
pl_overhead_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- This level and Resource Cost Element
WHEN (tl_resource_temp <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
3,
1,
tl_resource_temp,
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
-- This Level and Outside Processing cost element
WHEN tl_outside_processing_temp <>0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
4,
1,
tl_outside_processing_temp,
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
-- This Level and Overhead cost element
WHEN tl_overhead_temp <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
5,
1,
tl_overhead_temp,
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
-- Create 0 TL Material instead of 0 PL Material when there is no non-zero cost details
WHEN (pl_material_temp = 0 AND pl_material_overhead_temp = 0 AND pl_resource_temp = 0 AND
pl_outside_processing_temp = 0 AND pl_overhead_temp = 0 AND tl_resource_temp = 0 AND
tl_outside_processing_temp = 0 AND tl_overhead_temp = 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
1, -- Material Cost Element
1, -- This Level
0,-- Zero Cost
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
SELECT p_pac_period_id p_pac_period,
p_cost_group_id p_cost_group,
p_cost_type_id p_cost_type,
p_item_id p_item,
p_txn_id p_txn,
NVL(SUM(wppb.pl_material_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_temp,
NVL(SUM(wppb.pl_material_overhead_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_overhead_temp,
NVL(SUM(wppb.pl_resource_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_resource_temp,
NVL(SUM(wppb.pl_outside_processing_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_outside_processing_temp,
NVL(SUM(wppb.pl_overhead_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_overhead_temp,
NVL(SUM(wppb.tl_resource_temp)/
decode(l_net_qty,0,1,l_net_qty),0) tl_resource_temp,
NVL(SUM(wppb.tl_outside_processing_temp)/
decode(l_net_qty,0,1,l_net_qty),0) tl_outside_processing_temp,
NVL(SUM(wppb.tl_overhead_temp)/
decode(l_net_qty,0,1,l_net_qty),0) tl_overhead_temp,
-- All temp Variance Columns
NVL(SUM(wppb.pl_material_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_temp_var,
NVL(SUM(wppb.pl_material_overhead_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_overhead_temp_var,
NVL(SUM(wppb.pl_resource_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_resource_temp_var,
NVL(SUM(wppb.pl_outside_processing_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_outside_processing_temp_var,
NVL(SUM(wppb.pl_overhead_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_overhead_temp_var,
p_user_id p_user,
p_login_id p_login,
p_request_id p_request,
p_prog_app_id p_prog_app,
p_prog_id p_prog
FROM wip_pac_period_balances wppb
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) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
INSERT ALL
-- Previous Level and Material cost element
WHEN (pl_material_temp <> 0 OR pl_material_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
1,
2,
pl_material_temp,
pl_material_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Material Overhead cost element
WHEN (pl_material_overhead_temp <> 0 OR pl_material_overhead_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
2,
2,
pl_material_overhead_temp,
pl_material_overhead_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Resource cost element
WHEN (pl_resource_temp <> 0 OR pl_resource_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
3,
2,
pl_resource_temp,
pl_resource_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Outside Processing cost element
WHEN (pl_outside_processing_temp <>0 OR pl_outside_processing_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
4,
2,
pl_outside_processing_temp,
pl_outside_processing_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- Previous Level and Overhead cost element
WHEN (pl_overhead_temp <>0 OR pl_overhead_temp_var <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
wip_variance, -- New Column
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
5,
2,
pl_overhead_temp,
pl_overhead_temp_var,-- New column value
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login )
-- This level and Resource Cost Element
WHEN (tl_resource_temp <> 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
3,
1,
tl_resource_temp,
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
-- This Level and Outside Processing cost element
WHEN tl_outside_processing_temp <>0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
4,
1,
tl_outside_processing_temp,
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
-- This Level and Overhead cost element
WHEN tl_overhead_temp <> 0 THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
5,
1,
tl_overhead_temp,
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
-- Create 0 TL Material instead of 0 PL Material when there is no non-zero cost details
WHEN (pl_material_temp = 0 AND pl_material_overhead_temp = 0 AND pl_resource_temp = 0 AND
pl_outside_processing_temp = 0 AND pl_overhead_temp = 0 AND tl_resource_temp = 0 AND
tl_outside_processing_temp = 0 AND tl_overhead_temp = 0) THEN
INTO mtl_pac_txn_cost_details
(pac_period_id,
cost_group_id,
cost_type_id ,
inventory_item_id,
transaction_id,
cost_element_id,
level_type,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(p_pac_period,
p_cost_group,
p_cost_type,
p_item,
p_txn,
1, -- Material Cost Element
1, -- This Level
0,-- Zero Cost
sysdate,
p_user,
sysdate,
p_user,
p_request,
p_prog_app,
p_prog,
sysdate,
p_login)
SELECT p_pac_period_id p_pac_period,
p_cost_group_id p_cost_group,
p_cost_type_id p_cost_type,
p_item_id p_item,
p_txn_id p_txn,
NVL(SUM(wppb.pl_material_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_temp,
NVL(SUM(wppb.pl_material_overhead_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_overhead_temp,
NVL(SUM(wppb.pl_resource_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_resource_temp,
NVL(SUM(wppb.pl_outside_processing_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_outside_processing_temp,
NVL(SUM(wppb.pl_overhead_temp)/
decode(l_net_qty,0,1,l_net_qty),0) pl_overhead_temp,
NVL(SUM(wppb.tl_resource_temp)/
decode(l_net_qty,0,1,l_net_qty),0) tl_resource_temp,
NVL(SUM(wppb.tl_outside_processing_temp)/
decode(l_net_qty,0,1,l_net_qty),0) tl_outside_processing_temp,
NVL(SUM(wppb.tl_overhead_temp)/
decode(l_net_qty,0,1,l_net_qty),0) tl_overhead_temp,
-- All temp Variance Columns
NVL(SUM(wppb.pl_material_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_temp_var,
NVL(SUM(wppb.pl_material_overhead_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_material_overhead_temp_var,
NVL(SUM(wppb.pl_resource_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_resource_temp_var,
NVL(SUM(wppb.pl_outside_processing_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_outside_processing_temp_var,
NVL(SUM(wppb.pl_overhead_temp_var)*l_primary_quantity/
decode(l_net_qty,0,1,l_net_qty),0) pl_overhead_temp_var,
p_user_id p_user,
p_login_id p_login,
p_request_id p_request,
p_prog_app_id p_prog_app,
p_prog_id p_prog
FROM wip_pac_period_balances wppb
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) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
fnd_file.put_line(fnd_file.log,' Exception in Insert_wip_costs');
x_err_msg := SUBSTR('CSTPPWAS.insert_wip_costs('
|| to_char(l_stmt_num)
|| '): '
||SQLERRM,1,240);
END insert_wip_costs;
| UPDATE table WIP_PAC_PERIOD_BALANCES (wppb) |
| Check the job balance in this period for each cost element. |
| If the value is negative then put these values in VAR columns |
| END; |
SELECT wppb.operation_seq_num operation_seq_num
FROM WIP_PAC_PERIOD_BALANCES wppb
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 <= decode(p_scrap,1,p_op_seq,
wppb.operation_seq_num);
SELECT wro.inventory_item_id component,
Decode(wro.basis_type,
2, (wro.quantity_per_assembly / l_lot_size),
wro.quantity_per_assembly) / decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1) quantity_per_assembly
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE wro.wip_entity_id = p_entity_id
AND nvl(wro.repetitive_schedule_id ,-99) = nvl(l_repetitive_schedule_id,-99)
AND wro.operation_seq_num = c_op_sequence
AND wro.wip_supply_type NOT IN (4,5,6);
SELECT cost_element_id cst_ele_id
FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
WHERE cprocd.pac_period_id = p_pac_period_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_sequence_num
AND cprocd.inventory_item_id = component;
SELECT entity_type,
organization_id
INTO l_entity_type,
l_org_id
FROM wip_entities
WHERE wip_entity_id = p_entity_id;
SELECT nvl(start_quantity,1) -- to avoid divide by zero error
INTO l_lot_size
FROM wip_discrete_jobs
WHERE wip_entity_id = p_entity_id;
SELECT wrs.repetitive_schedule_id
INTO l_repetitive_schedule_id
FROM wip_repetitive_schedules wrs
WHERE wrs.wip_entity_id = p_entity_id
AND wrs.line_id = p_line_id;
SELECT nvl(include_component_yield, 1)
INTO l_include_comp_yield
FROM wip_parameters
WHERE organization_id = l_org_id;
SELECT COUNT(*)
INTO l_record_exists
FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
WHERE cprocd.pac_period_id = p_pac_period_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
AND cprocd.inventory_item_id = comp_rec.component
AND ROWNUM < 2;
SELECT SUM(NVL(cpicd.item_cost,0))
INTO l_current_period_cost
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 = comp_rec.component
AND cpic.cost_layer_id = cpicd.cost_layer_id
AND cpicd.cost_element_id = i
GROUP BY cpicd.cost_element_id;
INSERT INTO CST_PAC_REQ_OPER_COST_DETAILS
(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,
Temp_Relieved_value,
-- who Columns
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
VALUES (p_pac_period_id,
p_cost_group_id,
p_entity_id,
p_line_id,
comp_rec.component,
i,
op_seq_rec.operation_seq_num,
0,
0,
l_current_period_cost * l_avl_relieve_qty,
l_avl_relieve_qty,
0,
l_current_period_cost * l_avl_relieve_qty,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
sysdate,
p_login_id);
SELECT nvl(relieved_value,0),
decode(nvl(relieved_quantity, 0),
0,1,
nvl(relieved_quantity, 0))
INTO l_avl_relieve_value,
l_avl_relieve_qty
FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
WHERE cprocd.pac_period_id = p_prior_period_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
AND cprocd.inventory_item_id = comp_rec.component
AND cprocd.cost_element_id = cstelement_rec.cst_ele_id;
SELECT sum(primary_quantity)
INTO l_assembly_return_cnt
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = p_entity_id
AND mmt.transaction_action_id = 32
AND mmt.transaction_source_type_id = 5
AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999);
SELECT Max(Nvl(relieved_assembly_units,0))
INTO l_prior_completed_units
FROM wip_pac_period_balances wppb
WHERE wppb.wip_entity_id = p_entity_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99)
AND wppb.cost_group_id = p_cost_group_id;
SELECT nvl(Relieved_Value,0),
decode(sign(nvl(Relieved_quantity,0)),
0,1,
Relieved_quantity)
INTO l_prior_relieved_value,
l_prior_relieved_qty
FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
WHERE cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
AND cprocd.inventory_item_id = comp_rec.component
AND cprocd.cost_element_id = cstelement_rec.cst_ele_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_prior_period_id;
SELECT nvl(relieved_value,0),
decode(nvl(relieved_quantity, 0),
0,1,
nvl(relieved_quantity, 0))
INTO l_avl_relieve_value,
l_avl_relieve_qty
FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
WHERE cprocd.pac_period_id = p_prior_period_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
AND cprocd.inventory_item_id = comp_rec.component
AND cprocd.cost_element_id = cstelement_rec.cst_ele_id;
SELECT (nvl(applied_value,0) - nvl(relieved_value,0) - nvl(comp_variance,0)),
nvl(applied_quantity, 0),
(nvl(applied_quantity, 0) - nvl(relieved_quantity, 0)),
nvl(relieved_quantity, 0)
INTO l_avl_relieve_value,
l_applied_qty,
l_avl_relieve_qty,
l_relieved_qty
FROM CST_PAC_REQ_OPER_COST_DETAILS cprocd
WHERE cprocd.pac_period_id = p_pac_period_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
AND cprocd.inventory_item_id = comp_rec.component
AND cprocd.cost_element_id = cstelement_rec.cst_ele_id;
SELECT SUM(NVL(cpicd.item_cost,0))
INTO l_current_period_cost
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 = comp_rec.component
AND cpic.cost_layer_id = cpicd.cost_layer_id
AND cpicd.cost_element_id = cstelement_rec.cst_ele_id;
SELECT SUM(NVL(cpicd.item_cost,0))
INTO l_current_period_cost
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 = comp_rec.component
AND cpic.cost_layer_id = cpicd.cost_layer_id
AND cpicd.cost_element_id = cstelement_rec.cst_ele_id;
SELECT SUM(NVL(cpicd.item_cost,0))
INTO l_current_period_cost
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 = comp_rec.component
AND cpic.cost_layer_id = cpicd.cost_layer_id
AND cpicd.cost_element_id = cstelement_rec.cst_ele_id;
UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
SET cprocd.Relieved_value = nvl(cprocd.Relieved_Value,0) +
decode(p_net_qty,
0,decode(p_final_completion_flag,
0, 0,
NULL,0,
Decode(l_prior_completed_units, 0, 0,
l_avl_relieve_value)),
l_avl_relieve_value),
cprocd.Temp_Relieved_value = l_avl_relieve_value + (-1) * l_prior_relieved_value,
-- Same as TEMP column in WPPB table. This will be used if Total Job value is -ve then
-- Update the Comp_variance = Comp_variance + Temp_Relieved_value at the end
cprocd.Relieved_quantity = nvl(cprocd.Relieved_quantity,0) + l_avl_relieve_qty,
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
WHERE cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.operation_seq_num = op_seq_rec.operation_seq_num
AND cprocd.inventory_item_id = comp_rec.component
AND cprocd.cost_element_id = cstelement_rec.cst_ele_id
AND cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_pac_period_id;
UPDATE wip_pac_period_balances wppb
SET wppb.pl_material_out = wppb.pl_material_out + decode(p_net_qty,0,
decode(p_final_completion_flag,
0,0,
NULL,0,
Decode(l_prior_completed_units, 0, 0,
l_op_relieved_comp_cost(1))),
l_op_relieved_comp_cost(1)),
wppb.pl_material_temp = wppb.pl_material_temp + l_op_relieved_comp_cost(1)
+ decode(p_net_qty,0,
decode(p_final_completion_flag,
1, l_prior_relieved_comp_cost(1),
0),
0),
wppb.pl_material_overhead_out = wppb.pl_material_overhead_out + decode(p_net_qty,
0,decode(p_final_completion_flag,
0,0,
NULL,0,
Decode(l_prior_completed_units, 0, 0,
l_op_relieved_comp_cost(2))),
l_op_relieved_comp_cost(2)),
wppb.pl_material_overhead_temp = wppb.pl_material_overhead_temp + l_op_relieved_comp_cost(2)
+ decode(p_net_qty,0,
decode(p_final_completion_flag,
1, l_prior_relieved_comp_cost(2),
0),0),
wppb.pl_resource_out = wppb.pl_resource_out + decode(p_net_qty,
0,decode(p_final_completion_flag,
0,0,
NULL,0,
Decode(l_prior_completed_units, 0, 0,
l_op_relieved_comp_cost(3))),
l_op_relieved_comp_cost(3)),
wppb.pl_resource_temp = wppb.pl_resource_temp + l_op_relieved_comp_cost(3)
+ decode(p_net_qty,0,
decode(p_final_completion_flag,
1, l_prior_relieved_comp_cost(3),
0), 0),
wppb.pl_outside_processing_out = wppb.pl_outside_processing_out + decode(p_net_qty,
0,decode(p_final_completion_flag,
0,0,
NULL,0,
Decode(l_prior_completed_units, 0, 0,
l_op_relieved_comp_cost(4))),
l_op_relieved_comp_cost(4)),
wppb.pl_outside_processing_temp = wppb.pl_outside_processing_temp + l_op_relieved_comp_cost(4)
+ decode(p_net_qty,0,
decode(p_final_completion_flag,
1, l_prior_relieved_comp_cost(4),
0), 0),
wppb.pl_overhead_out = wppb.pl_overhead_out + decode(p_net_qty,
0,decode(p_final_completion_flag,
0,0,
NULL,0,
Decode(l_prior_completed_units, 0, 0,
l_op_relieved_comp_cost(5))),
l_op_relieved_comp_cost(5)),
wppb.pl_overhead_temp = wppb.pl_overhead_temp + l_op_relieved_comp_cost(5)
+ decode(p_net_qty,0,
decode(p_final_completion_flag,
1, l_prior_relieved_comp_cost(5),
0),0),
wppb.last_update_date = SYSDATE,
wppb.last_updated_by = p_user_id,
wppb.last_update_login = p_login_id,
wppb.request_id = p_request_id,
wppb.program_application_id = p_prog_app_id,
wppb.program_id = p_prog_id,
wppb.program_update_date = SYSDATE
WHERE wppb.wip_entity_id = p_entity_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99)
AND wppb.cost_group_id = p_cost_group_id
AND wppb.operation_seq_num = op_seq_rec.operation_seq_num;
UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + nvl(cprocd.Temp_Relieved_value,0),
cprocd.Relieved_value = nvl(cprocd.Relieved_value,0) - nvl(cprocd.Temp_Relieved_value,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
WHERE cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_pac_period_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.cost_element_id = 1; -- Material Cost Element
UPDATE WIP_PAC_PERIOD_BALANCES wppb
-- New column to store the variance in case if total job balance is negative
-- This column is not storing accumlated value
SET wppb.pl_material_temp_var = wppb.pl_material_temp,
-- This column is storing accumlated values of variance
wppb.pl_material_var = nvl(wppb.pl_material_var,0) + nvl(wppb.pl_material_temp,0),
--Subtract the variance column value from OUT column
wppb.pl_material_out = wppb.pl_material_out - wppb.pl_material_temp,
-- Make TEMP value to Zero
wppb.pl_material_temp = 0,
wppb.last_update_date = SYSDATE,
wppb.last_updated_by = p_user_id,
wppb.last_update_login = p_login_id,
wppb.request_id = p_request_id,
wppb.program_application_id = p_prog_app_id,
wppb.program_id = p_prog_id,
wppb.program_update_date = SYSDATE
WHERE wppb.cost_group_id = p_cost_group_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND wppb.wip_entity_id = p_entity_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
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
WHERE cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_pac_period_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.cost_element_id = 2; -- Material Overhead Cost Element
UPDATE wip_pac_period_balances wppb
-- New column to store the variance in case total job balance is negative
SET wppb.pl_material_overhead_temp_var = wppb.pl_material_overhead_temp,
-- This column is storing accumlated values of variance
wppb.pl_material_overhead_var = nvl(wppb.pl_material_overhead_var,0) + nvl(wppb.pl_material_overhead_temp,0),
--Subtract the variance column value from OUT column
wppb.pl_material_overhead_out = wppb.pl_material_overhead_out - wppb.pl_material_overhead_temp,
-- Make TEMP value to Zero
wppb.pl_material_overhead_temp = 0,
wppb.last_update_date = SYSDATE,
wppb.last_updated_by = p_user_id,
wppb.last_update_login = p_login_id,
wppb.request_id = p_request_id,
wppb.program_application_id = p_prog_app_id,
wppb.program_id = p_prog_id,
wppb.program_update_date = SYSDATE
WHERE wppb.cost_group_id = p_cost_group_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND wppb.wip_entity_id = p_entity_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
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
WHERE cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_pac_period_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.cost_element_id = 3; -- Resource Cost Element
UPDATE wip_pac_period_balances wppb
-- New column to store the variance in case if total job balance is negative
SET wppb.pl_resource_temp_var = wppb.pl_resource_temp,
-- This column is storing accumlated values of variance
wppb.pl_resource_var = nvl(wppb.pl_resource_var,0) + nvl(wppb.pl_resource_temp,0),
--Subtract the variance column value from OUT column
wppb.pl_resource_out = wppb.pl_resource_out - wppb.pl_resource_temp,
-- Make TEMP value to Zero
wppb.pl_resource_temp = 0,
wppb.last_update_date = SYSDATE,
wppb.last_updated_by = p_user_id,
wppb.last_update_login = p_login_id,
wppb.request_id = p_request_id,
wppb.program_application_id = p_prog_app_id,
wppb.program_id = p_prog_id,
wppb.program_update_date = SYSDATE
WHERE wppb.cost_group_id = p_cost_group_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND wppb.wip_entity_id = p_entity_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
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
WHERE cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_pac_period_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.cost_element_id = 4; -- Outside Processing Cost Element
UPDATE wip_pac_period_balances wppb
-- New column to store the varaince in case total job balance is negative
SET wppb.pl_outside_processing_temp_var = wppb.pl_outside_processing_temp,
-- This column is storing accumlated values of variance
wppb.pl_outside_processing_var = nvl(wppb.pl_outside_processing_var,0) + nvl(wppb.pl_outside_processing_temp,0),
--Subtract the variance column value from OUT column
wppb.pl_outside_processing_out = wppb.pl_outside_processing_out - wppb.pl_outside_processing_temp,
-- Make TEMP value to Zero
wppb.pl_outside_processing_temp = 0,
wppb.last_update_date = SYSDATE,
wppb.last_updated_by = p_user_id,
wppb.last_update_login = p_login_id,
wppb.request_id = p_request_id,
wppb.program_application_id = p_prog_app_id,
wppb.program_id = p_prog_id,
wppb.program_update_date = SYSDATE
WHERE wppb.cost_group_id = p_cost_group_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND wppb.wip_entity_id = p_entity_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
UPDATE CST_PAC_REQ_OPER_COST_DETAILS cprocd
SET cprocd.Comp_variance = nvl(cprocd.Comp_variance,0) + cprocd.Temp_Relieved_value,
cprocd.Relieved_value = cprocd.Relieved_value - cprocd.Temp_Relieved_value,
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
WHERE cprocd.cost_group_id = p_cost_group_id
AND cprocd.pac_period_id = p_pac_period_id
AND cprocd.wip_entity_id = p_entity_id
AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
AND cprocd.cost_element_id = 5; -- Overhead Processing Cost Element
UPDATE wip_pac_period_balances wppb
-- New column to store the varaince in case total job balance is negative
SET wppb.pl_overhead_temp_var = wppb.pl_overhead_temp,
-- This column is storing accumlated values of variance
wppb.pl_overhead_var = nvl(wppb.pl_overhead_var,0) + nvl(wppb.pl_overhead_temp,0),
--Subtract the variance column value from OUT column
wppb.pl_overhead_out = wppb.pl_overhead_out - wppb.pl_overhead_temp,
-- Make TEMP value to Zero
wppb.pl_overhead_temp = 0,
wppb.last_update_date = SYSDATE,
wppb.last_updated_by = p_user_id,
wppb.last_update_login = p_login_id,
wppb.request_id = p_request_id,
wppb.program_application_id = p_prog_app_id,
wppb.program_id = p_prog_id,
wppb.program_update_date = SYSDATE
WHERE wppb.cost_group_id = p_cost_group_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND wppb.wip_entity_id = p_entity_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99);
SELECT mmt.transaction_id txn_id,
mmt.inventory_item_id item_id,
mmt.primary_quantity pri_qty,
mmt.organization_id org_id,
mmt.subinventory_code subinv,
mmt.transaction_action_id txn_action_id,
mmt.transaction_source_type_id txn_src_type_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_source_id = p_entity_id
AND NVL(mmt.repetitive_line_id,-99) = NVL(p_line_id,-99)
AND mmt.transaction_action_id = 30
AND NVL(mmt.operation_seq_num, -1) = NVL(p_op_seq,-1)
ORDER BY mmt.primary_quantity DESC, mmt.transaction_id; -- minimize the occurences of negative periodic inventory quantity
SELECT mmt.transaction_id txn_id,
mmt.inventory_item_id item_id,
mmt.primary_quantity pri_qty,
mmt.organization_id org_id,
mmt.subinventory_code subinv,
mmt.transaction_action_id txn_action_id,
mmt.transaction_source_type_id txn_src_type_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_source_id = p_entity_id
AND NVL(mmt.repetitive_line_id,-99) = NVL(p_line_id,-99)
AND mmt.transaction_action_id IN (31,32)
ORDER BY mmt.transaction_action_id, mmt.transaction_id; -- minimize the occurences of negative periodic inventory quantity
SELECT SUM(mmt.primary_quantity)
INTO l_net_completion
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = p_entity_id
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (31,32);
UPDATE wip_pac_period_balances wppb
SET tl_resource_temp = 0,
tl_overhead_temp = 0,
tl_outside_processing_temp = 0,
pl_material_temp = 0,
pl_material_overhead_temp = 0,
pl_resource_temp = 0,
pl_outside_processing_temp = 0,
pl_overhead_temp = 0
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) = decode(p_entity_type, 4, -99, NVL(p_line_id,-99));
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp,
pl_material_out,
pl_material_temp,
pl_material_overhead_out,
pl_material_overhead_temp,
pl_resource_out,
pl_resource_temp,
pl_outside_processing_out,
pl_outside_processing_temp,
pl_overhead_out,
pl_overhead_temp
) =
(SELECT
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
-NVL(wppb.tl_resource_var,0)),
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
-NVL(wppb.tl_resource_var,0)),
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
- NVL(wppb.pl_material_var,0)),
(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)
- NVL(wppb.pl_material_var,0)),
(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0)),
(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0)),
(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
- NVL(wppb.pl_resource_var,0)),
(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)
- NVL(wppb.pl_resource_var,0)),
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0)),
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0)),
(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
- NVL(wppb.pl_overhead_var,0)),
(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
- NVL(wppb.pl_overhead_var,0))
FROM wip_pac_period_balances wppb2,
wip_flow_schedules wfs
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND wppb2.operation_seq_num = wppb.operation_seq_num
AND wfs.wip_entity_id = p_entity_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 wppb.operation_seq_num <=
decode(p_scrap,1,nvl(p_op_seq,wppb.operation_seq_num),wppb.operation_seq_num);
SELECT NVL(SUM(NVL(wppb.tl_resource_in,0)-
NVL(wppb.tl_resource_out,0)
- NVL(wppb.tl_resource_var,0)),0) net_tl_resource_value,
NVL(SUM(NVL(wppb.tl_overhead_in,0)-
NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),0) net_tl_overhead_value,
NVL(SUM(NVL(wppb.tl_outside_processing_in,0)-
NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),0) net_tl_osp_value
INTO
l_net_tl_resource_value,
l_net_tl_overhead_value,
l_net_tl_osp_value
FROM wip_pac_period_balances wppb
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0)),
0
),
NVL(tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
-NVL(wppb.tl_outside_processing_out,0)
-NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
-NVL(wppb.tl_outside_processing_out,0)
-NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
0
),
NVL(tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
0
)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id =p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <= wppb.operation_seq_num;
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) - nvl(unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) -
nvl(unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
NVL(tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
-NVL(wppb.tl_outside_processing_out,0)
-NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) -
NVL(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
-NVL(wppb.tl_outside_processing_out,0)
-NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
NVL(tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT NVL(SUM(NVL(wppb.tl_resource_out,0)),0) net_tl_resource_value,
NVL(SUM(NVL(wppb.tl_overhead_out,0)),0) net_tl_overhead_value,
NVL(SUM(NVL(wppb.tl_outside_processing_out,0)),0) net_tl_osp_value
INTO
l_net_tl_resource_value,
l_net_tl_overhead_value,
l_net_tl_osp_value
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_prior_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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(wppb2.tl_resource_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(wppb2.tl_resource_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_prior_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT NVL(SUM(NVL(wppb.tl_resource_out,0)),0) net_tl_resource_value,
NVL(SUM(NVL(wppb.tl_overhead_out,0)),0) net_tl_overhead_value,
NVL(SUM(NVL(wppb.tl_outside_processing_out,0)),0) net_tl_osp_value
INTO
l_net_tl_resource_value,
l_net_tl_overhead_value,
l_net_tl_osp_value
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_prior_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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT sum(primary_quantity)
INTO l_assembly_return_cnt
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = p_entity_id
AND mmt.transaction_action_id = 32
AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999);
SELECT Max(Nvl(relieved_assembly_units,0))
INTO l_prior_completed_units
FROM wip_pac_period_balances wppb
WHERE wppb.wip_entity_id = p_entity_id
AND wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND nvl(wppb.line_id, -99) = nvl(p_line_id,-99)
AND wppb.cost_group_id = p_cost_group_id;
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(wppb2.tl_resource_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1, NVL(wppb2.tl_resource_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_prior_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_resource_in,0)
-NVL(wppb2.tl_resource_out,0)
-NVL(wppb2.tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
- NVL(wppb2.tl_resource_var,0))),
0
),
decode(SIGN(SIGN(NVL(wppb2.tl_resource_in,0)
-NVL(wppb2.tl_resource_out,0)
-NVL(wppb2.tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
- NVL(wppb2.tl_resource_var,0))
,0
),
NVL(wppb.tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0)))
,0
),
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0)),
0
),
NVL(tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_in,0)
-NVL(wppb2.tl_overhead_out,0)
-NVL(wppb2.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
- NVL(wppb2.tl_overhead_var,0))),
0
),
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_in,0)
-NVL(wppb2.tl_overhead_out,0)
-NVL(wppb2.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
- NVL(wppb2.tl_overhead_var,0)),
0
)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (
tl_resource_temp,
tl_outside_processing_temp,
tl_overhead_temp
)
=
( SELECT DECODE(
SIGN(SIGN(NVL(tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(tl_resource_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(tl_outside_processing_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(tl_overhead_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
)
FROM wip_pac_period_balances
WHERE pac_period_id = p_prior_period_id
AND cost_group_id = p_cost_group_id
AND wip_entity_id = p_entity_id
AND NVL(line_id,-99) = NVL(p_line_id,-99)
AND operation_seq_num = wppb.operation_seq_num
)
WHERE pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id
AND wip_entity_id = p_entity_id
AND NVL(line_id,-99) = NVL(p_line_id,-99)
AND operation_seq_num <= decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT NVL(SUM(NVL(wppb.tl_resource_in,0)-
NVL(wppb.tl_resource_out,0)
- NVL(wppb.tl_resource_var,0)),0) net_tl_resource_value,
NVL(SUM(NVL(wppb.tl_overhead_in,0)-
NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),0) net_tl_overhead_value,
NVL(SUM(NVL(wppb.tl_outside_processing_in,0)-
NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),0) net_tl_osp_value,
NVL(SUM(NVL(wppb.pl_material_in,0)-
NVL(wppb.pl_material_out,0)
- NVL(wppb.pl_material_var,0)),0) net_pl_material_value,
NVL(SUM(NVL(wppb.pl_material_overhead_in,0)-
NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0)),0) net_pl_moh_value,
NVL(SUM(NVL(wppb.pl_resource_in,0)-
NVL(wppb.pl_resource_out,0)
- NVL(wppb.pl_resource_var,0)),0) net_pl_resource_value,
NVL(SUM(NVL(wppb.pl_outside_processing_in,0)-
NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0)),0) net_pl_osp_value,
NVL(SUM(NVL(wppb.pl_overhead_in,0)-
NVL(wppb.pl_overhead_out,0)
- NVL(wppb.pl_overhead_var,0)),0) net_pl_overhead_value
INTO
l_net_tl_resource_value,
l_net_tl_overhead_value,
l_net_tl_osp_value,
l_net_pl_material_value,
l_net_pl_moh_value,
l_net_pl_resource_value,
l_net_pl_osp_value,
l_net_pl_overhead_value
FROM wip_pac_period_balances wppb
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp,
pl_material_out,
pl_material_temp,
pl_material_overhead_out,
pl_material_overhead_temp,
pl_resource_out,
pl_resource_temp,
pl_outside_processing_out,
pl_outside_processing_temp,
pl_overhead_out,
pl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0)),
0
),
NVL(tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
0
),
NVL(tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
0
),
NVL(pl_material_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_material_in,0)
-NVL(wppb.pl_material_out,0)
-NVL(wppb.pl_material_var,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
(NVL(wppb.pl_material_in,0) - NVL(wppb.pl_material_out,0) - nvl(wppb.pl_material_in_apull,0)
- NVL(wppb.pl_material_var,0)) + nvl(wppb.pl_material_in_apull,0),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_material_in,0)
-NVL(wppb.pl_material_out,0)
-NVL(wppb.pl_material_var,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)-nvl(wppb.pl_material_in_apull,0)
- NVL(wppb.pl_material_var,0)) + nvl(wppb.pl_material_in_apull,0),
0
),
NVL(pl_material_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0) - nvl(wppb.pl_material_overhead_in_apull,0)
- NVL(wppb.pl_material_overhead_var,0)) + nvl(wppb.pl_material_overhead_in_apull,0),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0) - nvl(wppb.pl_material_overhead_in_apull,0)
- NVL(wppb.pl_material_overhead_var,0)) + nvl(wppb.pl_material_overhead_in_apull,0),
0
),
NVL(pl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_resource_in,0)
-NVL(wppb.pl_resource_out,0)
-NVL(wppb.pl_resource_var,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0) - nvl(wppb.pl_resource_in_apull,0)
- NVL(wppb.pl_resource_var,0)) + nvl(wppb.pl_resource_in_apull,0),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_resource_in,0)
-NVL(wppb.pl_resource_out,0)
-NVL(wppb.pl_resource_var,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0) - nvl(wppb.pl_resource_in_apull,0)
- NVL(wppb.pl_resource_var,0)) + nvl(wppb.pl_resource_in_apull,0),
0
),
NVL(pl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
- NVL(wppb.pl_outside_processing_var,0)) + nvl(wppb.pl_outside_processing_in_apull,0),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
- NVL(wppb.pl_outside_processing_var,0)) + nvl(wppb.pl_outside_processing_in_apull,0),
0
),
NVL(pl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_overhead_in,0)
-NVL(wppb.pl_overhead_out,0)
-NVL(wppb.pl_overhead_var,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
- NVL(wppb.pl_overhead_var,0)) + nvl(wppb.pl_overhead_in_apull,0),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_overhead_in,0)
-NVL(wppb.pl_overhead_out,0)
-NVL(wppb.pl_overhead_var,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
- NVL(wppb.pl_overhead_var,0)) + nvl(wppb.pl_overhead_in_apull,0),
0
)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <= wppb.operation_seq_num;
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp,
pl_material_out,
pl_material_temp,
scrap_pull_material, -- Added by Bug#4717026
pl_material_overhead_out,
pl_material_overhead_temp,
scrap_pull_material_overhead, -- Added by Bug#4717026
pl_resource_out,
pl_resource_temp,
scrap_pull_resource, -- Added by Bug#4717026
pl_outside_processing_out,
pl_outside_processing_temp,
scrap_pull_outside_processing, -- Added by Bug#4717026
pl_overhead_out,
pl_overhead_temp,
scrap_pull_overhead -- Added by Bug#4717026
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_resource_in,0)
-NVL(wppb2.tl_resource_out,0)
-NVL(wppb2.tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
- NVL(wppb2.tl_resource_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
decode(SIGN(SIGN(NVL(wppb2.tl_resource_in,0)
-NVL(wppb2.tl_resource_out,0)
-NVL(wppb2.tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb2.tl_resource_in,0)-NVL(wppb2.tl_resource_out,0)
- NVL(wppb2.tl_resource_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
NVL(tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb2.tl_outside_processing_in,0)
- NVL(wppb2.tl_outside_processing_out,0)
- NVL(wppb2.tl_outside_processing_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
NVL(tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_in,0)
-NVL(wppb2.tl_overhead_out,0)
-NVL(wppb2.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
- NVL(wppb2.tl_overhead_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_in,0)
-NVL(wppb2.tl_overhead_out,0)
-NVL(wppb2.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
- NVL(wppb2.tl_overhead_var,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
),
0
),
NVL(pl_material_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_material_in,0)
-NVL(wppb2.pl_material_out,0)
-NVL(wppb2.pl_material_var,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
(NVL(wppb2.pl_material_in,0)-NVL(wppb2.pl_material_out,0)- nvl(wppb2.pl_material_in_apull,0)
- NVL(wppb2.pl_material_var,0)+ nvl(wppb.scrap_pull_material,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty /(nvl(wppb.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb.pl_material_in_apull,0), --bug 14537919
nvl(wppb.pl_material_in_apull,0) - nvl(wppb.scrap_pull_material,0)),
0
),
decode(SIGN(SIGN(NVL(wppb2.pl_material_in,0)
-NVL(wppb2.pl_material_out,0)
-NVL(wppb2.pl_material_var,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
(NVL(wppb2.pl_material_in,0)- NVL(wppb2.pl_material_out,0)- nvl(wppb2.pl_material_in_apull,0)
- NVL(wppb2.pl_material_var,0)+ nvl(wppb2.scrap_pull_material,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_material_in_apull,0),
nvl(wppb2.pl_material_in_apull,0) - nvl(wppb2.scrap_pull_material,0)),
0
),
NVL(wppb.scrap_pull_material,0) +
decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_material_in_apull,0),
0),
NVL(pl_material_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_material_overhead_in,0)
- NVL(wppb2.pl_material_overhead_out,0)
- NVL(wppb2.pl_material_overhead_var,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
(NVL(wppb2.pl_material_overhead_in,0)
- NVL(wppb2.pl_material_overhead_out,0)- nvl(wppb2.pl_material_overhead_in_apull,0)
- NVL(wppb2.pl_material_overhead_var,0)+ nvl(wppb2.scrap_pull_material_overhead,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb.pl_material_overhead_in_apull,0),
nvl(wppb.pl_material_overhead_in_apull,0) - nvl(wppb2.scrap_pull_material_overhead,0)),
0
),
decode(SIGN(SIGN(NVL(wppb2.pl_material_overhead_in,0)
- NVL(wppb2.pl_material_overhead_out,0)
- NVL(wppb2.pl_material_overhead_var,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
(NVL(wppb2.pl_material_overhead_in,0)
- NVL(wppb2.pl_material_overhead_out,0)-nvl(wppb2.pl_material_overhead_in_apull,0)
- NVL(wppb2.pl_material_overhead_var,0)+ nvl(wppb2.scrap_pull_material_overhead,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_material_overhead_in_apull,0),
nvl(wppb2.pl_material_overhead_in_apull,0) - nvl(wppb2.scrap_pull_material_overhead,0)),
0
),
NVL(wppb.scrap_pull_material_overhead,0) +
decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_material_overhead_in_apull,0),
0),
NVL(pl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_resource_in,0)
-NVL(wppb2.pl_resource_out,0)
- NVL(wppb2.pl_resource_var,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
(NVL(wppb2.pl_resource_in,0)- NVL(wppb2.pl_resource_out,0)- nvl(wppb2.pl_resource_in_apull,0)
- NVL(wppb2.pl_resource_var,0) + NVL(wppb2.scrap_pull_resource,0) )*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_resource_in_apull,0),
nvl(wppb2.pl_resource_in_apull,0) - NVL(wppb2.scrap_pull_resource,0)),
0
),
decode(SIGN(SIGN(NVL(wppb2.pl_resource_in,0)
-NVL(wppb2.pl_resource_out,0)
- NVL(wppb2.pl_resource_var,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
(NVL(wppb2.pl_resource_in,0)- NVL(wppb2.pl_resource_out,0)- nvl(wppb2.pl_resource_in_apull,0)
- NVL(wppb2.pl_resource_var,0) + NVL(wppb2.scrap_pull_resource,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_resource_in_apull,0),
nvl(wppb2.pl_resource_in_apull,0) - NVL(wppb2.scrap_pull_resource,0)),
0
),
NVL(wppb.scrap_pull_resource,0) +
decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_resource_in_apull,0),
0),
NVL(pl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_outside_processing_in,0)
- NVL(wppb2.pl_outside_processing_out,0)
- NVL(wppb2.pl_outside_processing_var,0)
+ NVL(wppb.scrap_pull_outside_processing,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
(NVL(wppb2.pl_outside_processing_in,0)
- NVL(wppb2.pl_outside_processing_out,0)- nvl(wppb2.pl_outside_processing_in_apull,0)
- NVL(wppb2.pl_outside_processing_var,0) + NVL(wppb2.scrap_pull_resource,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_outside_processing_in_apull,0),
nvl(wppb2.pl_outside_processing_in_apull,0) - NVL(wppb2.scrap_pull_outside_processing,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
- NVL(wppb.pl_outside_processing_var,0) + NVL(wppb.scrap_pull_outside_processing,0))*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_outside_processing_in_apull,0),
nvl(wppb2.pl_outside_processing_in_apull,0) - NVL(wppb2.scrap_pull_outside_processing,0)),
0
),
NVL(scrap_pull_outside_processing,0) +
decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_outside_processing_in_apull,0),
0),
NVL(pl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_overhead_in,0)
-NVL(wppb.pl_overhead_out,0)
-NVL(wppb.pl_overhead_var,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
(NVL(wppb2.pl_overhead_in,0)-NVL(wppb2.pl_overhead_out,0)-nvl(wppb2.pl_overhead_in_apull,0)
- NVL(wppb2.pl_overhead_var,0) + NVL(wppb2.scrap_pull_overhead,0) )*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_overhead_in_apull,0),
nvl(wppb2.pl_overhead_in_apull,0) - NVL(wppb2.scrap_pull_overhead,0)),
0
),
decode(SIGN(SIGN(NVL(wppb2.pl_overhead_in,0)
-NVL(wppb2.pl_overhead_out,0)
-NVL(wppb2.pl_overhead_var,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
(NVL(wppb2.pl_overhead_in,0)-NVL(wppb2.pl_overhead_out,0)-nvl(wppb2.pl_overhead_in_apull,0)
- NVL(wppb2.pl_overhead_var,0) + NVL(wppb2.scrap_pull_overhead,0) )*
decode(SIGN(NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0)),
0, 1,
-1, 1,
p_net_qty/ (NVL(wppb2.operation_completed_units,0) -
NVL(wppb2.relieved_assembly_units,0)- nvl(wppb2.unrelieved_scrap_quantity,0))
) + decode(p_scrap,1,
decode(SIGN(nvl(relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_overhead_in_apull,0),
nvl(wppb2.pl_overhead_in_apull,0) - NVL(wppb2.scrap_pull_overhead,0)),
0
),
NVL(wppb.scrap_pull_overhead,0) +
decode(p_scrap,1,
decode(SIGN(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0)),
0,1,
-1,1,
p_net_qty/(nvl(wppb2.relieved_scrap_qty,0) + nvl(l_net_completion,0))) * nvl(wppb2.pl_overhead_in_apull,0),
0)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT NVL(SUM(NVL(wppb.tl_resource_out,0)),0) net_tl_resource_value,
NVL(SUM(NVL(wppb.tl_overhead_out,0)),0) net_tl_overhead_value,
NVL(SUM(NVL(wppb.tl_outside_processing_out,0)),0) net_tl_osp_value,
NVL(SUM(NVL(wppb.pl_material_out,0)),0) net_pl_material_value,
NVL(SUM(NVL(wppb.pl_material_overhead_out,0)),0) net_pl_moh_value,
NVL(SUM(NVL(wppb.pl_resource_out,0)),0) net_pl_resource_value,
NVL(SUM(NVL(wppb.pl_outside_processing_out,0)),0) net_pl_osp_value,
NVL(SUM(NVL(wppb.pl_overhead_out,0)),0) net_pl_overhead_value
INTO
l_net_tl_resource_value,
l_net_tl_overhead_value,
l_net_tl_osp_value,
l_net_pl_material_value,
l_net_pl_moh_value,
l_net_pl_resource_value,
l_net_pl_osp_value,
l_net_pl_overhead_value
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_prior_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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp,
pl_material_out,
pl_material_temp,
pl_material_overhead_out,
pl_material_overhead_temp,
pl_resource_out,
pl_resource_temp,
pl_outside_processing_out,
pl_outside_processing_temp,
pl_overhead_out,
pl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(wppb2.tl_resource_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(wppb2.tl_resource_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_material_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_material_out,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
NVL(wppb2.pl_material_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.pl_material_out,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
NVL(wppb2.pl_material_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_material_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_material_overhead_out,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
NVL(wppb2.pl_material_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.pl_material_overhead_out,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
NVL(wppb2.pl_material_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_resource_out,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
NVL(wppb2.pl_resource_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.pl_resource_out,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
NVL(wppb2.pl_resource_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_outside_processing_out,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
NVL(wppb2.pl_outside_processing_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.pl_outside_processing_out,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
NVL(wppb2.pl_outside_processing_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_overhead_out,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
NVL(wppb2.pl_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
decode(SIGN(SIGN(NVL(wppb2.pl_overhead_out,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
NVL(wppb2.pl_overhead_out,0) *
p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_prior_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT NVL(SUM(NVL(wppb.tl_resource_out,0)),0) net_tl_resource_value,
NVL(SUM(NVL(wppb.tl_overhead_out,0)),0) net_tl_overhead_value,
NVL(SUM(NVL(wppb.tl_outside_processing_out,0)),0) net_tl_osp_value,
NVL(SUM(NVL(wppb.pl_material_out,0)),0) net_pl_material_value,
NVL(SUM(NVL(wppb.pl_material_overhead_out,0)),0) net_pl_moh_value,
NVL(SUM(NVL(wppb.pl_resource_out,0)),0) net_pl_resource_value,
NVL(SUM(NVL(wppb.pl_outside_processing_out,0)),0) net_pl_osp_value,
NVL(SUM(NVL(wppb.pl_overhead_out,0)),0) net_pl_overhead_value,
NVL(MAX(relieved_assembly_units),0)
INTO
l_net_tl_resource_value,
l_net_tl_overhead_value,
l_net_tl_osp_value,
l_net_pl_material_value,
l_net_pl_moh_value,
l_net_pl_resource_value,
l_net_pl_osp_value,
l_net_pl_overhead_value,
l_prior_completed_units
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_prior_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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
SELECT sum(primary_quantity)
INTO l_assembly_return_cnt
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = p_entity_id
AND mmt.transaction_action_id = 32
AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999);
UPDATE wip_pac_period_balances wppb
SET ( tl_resource_out,
tl_outside_processing_out,
tl_overhead_out,
pl_material_out,
pl_material_overhead_out,
pl_resource_out,
pl_outside_processing_out,
pl_overhead_out
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(wppb2.tl_resource_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(wppb2.tl_outside_processing_out,0)*
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(wppb2.tl_overhead_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_material_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_material_out,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
NVL(wppb2.pl_material_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_material_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_material_overhead_out,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
NVL(wppb2.pl_material_overhead_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_resource_out,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
NVL(wppb2.pl_resource_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_outside_processing_out,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
NVL(wppb2.pl_outside_processing_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0),
NVL(wppb.pl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb2.pl_overhead_out,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
NVL(wppb2.pl_overhead_out,0) *
l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
0,1,wppb2.relieved_assembly_units),
0)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_prior_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET ( tl_resource_out,
tl_resource_temp,
tl_outside_processing_out,
tl_outside_processing_temp,
tl_overhead_out,
tl_overhead_temp,
pl_material_out,
pl_material_temp,
pl_material_overhead_out,
pl_material_overhead_temp,
pl_resource_out,
pl_resource_temp,
pl_outside_processing_out,
pl_outside_processing_temp,
pl_overhead_out,
pl_overhead_temp
) =
(SELECT
NVL(wppb.tl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0))),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_resource_in,0)
-NVL(wppb.tl_resource_out,0)
-NVL(tl_resource_var,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
(NVL(wppb.tl_resource_in,0)-NVL(wppb.tl_resource_out,0)
- NVL(tl_resource_var,0))
,0
),
NVL(tl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)))
,0
),
decode(SIGN(SIGN(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
(NVL(wppb.tl_outside_processing_in,0)
- NVL(wppb.tl_outside_processing_out,0)
- NVL(wppb.tl_outside_processing_var,0)),
0
),
NVL(tl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0))),
0
),
decode(SIGN(SIGN(NVL(wppb.tl_overhead_in,0)
-NVL(wppb.tl_overhead_out,0)
-NVL(wppb.tl_overhead_var,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
(NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
- NVL(wppb.tl_overhead_var,0)),
0
),
NVL(pl_material_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_material_in,0)
-NVL(wppb.pl_material_out,0)
-NVL(wppb.pl_material_var,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)- nvl(wppb.pl_material_in_apull,0)
- NVL(wppb.pl_material_var,0))
+ nvl(wppb.pl_material_in_apull,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_material_in,0)
-NVL(wppb.pl_material_out,0)
-NVL(wppb.pl_material_var,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
(NVL(wppb.pl_material_in,0)-NVL(wppb.pl_material_out,0)-nvl(wppb.pl_material_in_apull,0)
- NVL(wppb.pl_material_var,0)) + nvl(wppb.pl_material_in_apull,0),
0
),
NVL(pl_material_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)-nvl(wppb.pl_material_overhead_in_apull,0)
- NVL(wppb.pl_material_overhead_var,0))
+ nvl(wppb.pl_material_overhead_in_apull,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)
- NVL(wppb.pl_material_overhead_var,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
(NVL(wppb.pl_material_overhead_in,0)
- NVL(wppb.pl_material_overhead_out,0)-nvl(wppb.pl_material_overhead_in_apull,0)
- NVL(wppb.pl_material_overhead_var,0))
+ nvl(wppb.pl_material_overhead_in_apull,0),
0
),
NVL(pl_resource_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_resource_in,0)
-NVL(wppb.pl_resource_out,0)
-NVL(wppb.pl_resource_var,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)-nvl(wppb.pl_resource_in_apull,0)
- NVL(wppb.pl_resource_var,0))
+ nvl(wppb.pl_resource_in_apull,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_resource_in,0)
-NVL(wppb.pl_resource_out,0)
-NVL(wppb.pl_resource_var,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
(NVL(wppb.pl_resource_in,0)-NVL(wppb.pl_resource_out,0)-nvl(wppb.pl_resource_in_apull,0)
- NVL(wppb.pl_resource_var,0)) + nvl(wppb.pl_resource_in_apull,0),
0
),
NVL(pl_outside_processing_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
- NVL(wppb.pl_outside_processing_var,0))
+ nvl(wppb.pl_outside_processing_in_apull,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)
- NVL(wppb.pl_outside_processing_var,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
(NVL(wppb.pl_outside_processing_in,0)
- NVL(wppb.pl_outside_processing_out,0)-nvl(wppb.pl_outside_processing_in_apull,0)
- NVL(wppb.pl_outside_processing_var,0)) +
nvl(wppb.pl_outside_processing_in_apull,0),
0
),
NVL(pl_overhead_out,0) +
decode(SIGN(SIGN(NVL(wppb.pl_overhead_in,0)
-NVL(wppb.pl_overhead_out,0)
-NVL(wppb.pl_overhead_var,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
Decode(l_prior_completed_units, 0, 0,
(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
- NVL(wppb.pl_overhead_var,0))
+ nvl(wppb.pl_overhead_in_apull,0)),
0
),
decode(SIGN(SIGN(NVL(wppb.pl_overhead_in,0)
-NVL(wppb.pl_overhead_out,0)
-NVL(wppb.pl_overhead_var,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
(NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
- NVL(wppb.pl_overhead_var,0))
+ nvl(wppb.pl_overhead_in_apull,0),
0
)
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
AND wppb2.operation_seq_num = wppb.operation_seq_num
)
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 <=
decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET (
tl_resource_temp,
tl_outside_processing_temp,
tl_overhead_temp,
pl_material_temp,
pl_material_overhead_temp,
pl_resource_temp,
pl_outside_processing_temp,
pl_overhead_temp
)
= (
SELECT DECODE(
SIGN(SIGN(NVL(tl_resource_out,0))
+SIGN(l_net_tl_resource_value)
+2
),
1,
NVL(tl_resource_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(tl_outside_processing_out,0))
+SIGN(l_net_tl_osp_value)
+2
),
1,
NVL(tl_outside_processing_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(tl_overhead_out,0))
+SIGN(l_net_tl_overhead_value)
+2
),
1,
NVL(tl_overhead_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(pl_material_out,0))
+SIGN(l_net_pl_material_value)
+2
),
1,
NVL(pl_material_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(pl_material_overhead_out,0))
+SIGN(l_net_pl_moh_value)
+2
),
1,
NVL(pl_material_overhead_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(pl_resource_out,0))
+SIGN(l_net_pl_resource_value)
+2
),
1,
NVL(pl_resource_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(pl_outside_processing_out,0))
+SIGN(l_net_pl_osp_value)
+2
),
1,
NVL(pl_outside_processing_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
),
DECODE(
SIGN(SIGN(NVL(pl_overhead_out,0))
+SIGN(l_net_pl_overhead_value)
+2
),
1,
NVL(pl_overhead_out,0) /
DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
0
)
FROM wip_pac_period_balances
WHERE pac_period_id = p_prior_period_id
AND cost_group_id = p_cost_group_id
AND wip_entity_id = p_entity_id
AND NVL(line_id,-99) = NVL(p_line_id,-99)
AND operation_seq_num = wppb.operation_seq_num
)
WHERE pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id
AND wip_entity_id = p_entity_id
AND NVL(line_id,-99) = NVL(p_line_id,-99)
AND operation_seq_num <= decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
UPDATE wip_pac_period_balances wppb
SET wppb.relieved_assembly_units =
NVL(wppb.relieved_assembly_units,0) + p_net_qty
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 <= decode(p_scrap,1,p_op_seq,
wppb.operation_seq_num);
SELECT NVL(we.primary_item_id,-1),
we.organization_id
INTO l_item_id,
l_org_id
FROM wip_entities we
WHERE we.wip_entity_id = p_entity_id;
SELECT sum(primary_quantity)
INTO l_completed_assembly_qty
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = p_entity_id
AND nvl(mmt.repetitive_line_id,-99) = nvl(p_line_id,-99)
AND mmt.transaction_action_id = 31
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999);
insert_wip_costs
(p_pac_period_id => p_pac_period_id,
p_prior_period_id => p_prior_period_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_item_id => c_txn_rec.item_id,
p_entity_id => p_entity_id,
p_line_id => p_line_id,
p_txn_id => c_txn_rec.txn_id,
p_net_qty => l_conv_net_qty,
p_completed_assembly_qty => NULL,
p_final_completion_flag => NULL,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
x_err_num => l_err_num,
x_err_code => l_err_code,
x_err_msg => l_err_msg);
UPDATE MTL_MATERIAL_TRANSACTIONS
SET PERIODIC_PRIMARY_QUANTITY = c_txn_rec.pri_qty * l_uom_conv_rate
WHERE TRANSACTION_ID = c_txn_rec.txn_id;
insert_wip_costs
(p_pac_period_id => p_pac_period_id,
p_prior_period_id => p_prior_period_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_item_id => c_txn_rec.item_id,
p_entity_id => p_entity_id,
p_line_id => p_line_id,
p_txn_id => c_txn_rec.txn_id,
p_net_qty => l_conv_net_qty,
p_completed_assembly_qty => l_completed_assembly_qty,
p_final_completion_flag => p_final_completion_flag,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
x_err_num => l_err_num,
x_err_code => l_err_code,
x_err_msg => l_err_msg);
UPDATE MTL_MATERIAL_TRANSACTIONS
SET PERIODIC_PRIMARY_QUANTITY = c_txn_rec.pri_qty * l_uom_conv_rate
WHERE TRANSACTION_ID = c_txn_rec.txn_id;
SELECT mmt.transaction_source_id entity_id,
mmt.organization_id org_id,
we.entity_type entity_type,
mmt.repetitive_line_id line_id,
mmt.operation_seq_num op_seq,
SUM(mmt.primary_quantity) net_scrap
FROM mtl_material_transactions mmt,
cst_cost_group_assignments ccga,
wip_entities we
WHERE mmt.inventory_item_id = p_item_id
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id = 30
AND mmt.organization_id = ccga.organization_id
AND ccga.cost_group_id = p_cost_group_id
AND we.wip_entity_id = mmt.transaction_source_id
AND we.organization_id = mmt.organization_id
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.inventory_item_id = we.primary_item_id
AND mmt1.transaction_source_id = we.wip_entity_id
AND mmt1.organization_id = we.organization_id
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id in (1,27,33,34)
AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC (p_end_date) + 0.99999))
GROUP BY
mmt.transaction_source_id,
mmt.organization_id,
we.entity_type,
mmt.repetitive_line_id,
mmt.operation_seq_num
ORDER BY
SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
mmt.transaction_source_id; -- a consistent tie breaker
SELECT mmt.transaction_source_id entity_id,
mmt.organization_id org_id,
we.entity_type entity_type,
mmt.repetitive_line_id line_id,
SUM(mmt.primary_quantity) net_completion
FROM mtl_material_transactions mmt,
cst_cost_group_assignments ccga,
wip_entities we
WHERE mmt.inventory_item_id = p_item_id
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (31,32)
AND mmt.organization_id = ccga.organization_id
AND ccga.cost_group_id = p_cost_group_id
AND we.wip_entity_id = mmt.transaction_source_id
AND we.organization_id = mmt.organization_id
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.inventory_item_id = we.primary_item_id
AND mmt1.transaction_source_id = we.wip_entity_id
AND mmt1.organization_id = we.organization_id
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id in (1,27,33,34)
AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC (p_end_date) + 0.99999))
/* R12 PAC Enhancements for China and Taiwan: Exclude eAM entities as rebuildable jobs can be
completed only in expense subinventories at 0 cost so they should not be costed and no
distributions created */
AND we.entity_type not in (6,7)
GROUP BY
mmt.transaction_source_id,
mmt.organization_id,
we.entity_type,
mmt.repetitive_line_id
ORDER BY
SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
mmt.transaction_source_id; -- a consistent tie breaker
fnd_file.put_line(fnd_file.log,' Scrap_Qty Update << ');
UPDATE WIP_PAC_PERIOD_BALANCES
SET relieved_scrap_qty = nvl(relieved_scrap_qty,0) + c_ent_rec.net_scrap
WHERE wip_entity_id = c_ent_rec.entity_id
AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_ent_rec.line_id,-99))
AND operation_seq_num <= c_ent_rec.op_seq
AND cost_type_id = p_cost_type_id
AND pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 4, /* Non-rework completions */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
SELECT count(*)
INTO l_final_completion_count
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = c_ent_rec.entity_id
AND nvl(mmt.repetitive_line_id,-99) = nvl( c_ent_rec.line_id,-99)
/* Bug 12703830 */
AND mmt.transaction_action_id = 31
AND mmt.final_completion_flag = 'Y'
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND ROWNUM < 2;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 4, /* Non-rework completions */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 4, /* Non-rework completions */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/* Bug 11834257: Use update_item_cppb instead of update_cppb */
CSTPPWAC.update_item_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 4, /* Non-rework Completions */
i_item_id => p_item_id,
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/*CSTPPWAC.update_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 4, /* Non-rework Completions */
/*i_low_level_code => p_low_level_code,
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);*/
SELECT mmt.transaction_id txn_id,
mmt.transaction_source_id entity_id,
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 org_id
FROM mtl_material_transactions mmt,
wip_entities we,
cst_cost_group_assignments ccga /* bug3930450 - Added ccga join to process txns for a particular cost group */
WHERE mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND ccga.cost_group_id = p_cost_group_id /* bug3930450 */
AND ccga.organization_id = mmt.organization_id /* bug3930450 */
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (1,27,33,34)
AND mmt.inventory_item_id = p_item_id
AND we.wip_entity_id = mmt.transaction_source_id
AND we.organization_id = mmt.organization_id
AND NVL(we.primary_item_id,-1) = mmt.inventory_item_id;
SELECT mmt.transaction_source_id entity_id,
mmt.organization_id org_id,
we.entity_type entity_type,
mmt.repetitive_line_id line_id,
mmt.operation_seq_num op_seq,
SUM(mmt.primary_quantity) net_scrap
FROM mtl_material_transactions mmt,
cst_cost_group_assignments ccga,
wip_entities we
WHERE mmt.inventory_item_id = p_item_id
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id = 30
AND mmt.organization_id = ccga.organization_id
AND ccga.cost_group_id = p_cost_group_id
AND we.wip_entity_id = mmt.transaction_source_id
AND we.organization_id = mmt.organization_id
AND EXISTS (SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.inventory_item_id = we.primary_item_id
AND mmt1.transaction_source_id = we.wip_entity_id
AND mmt1.organization_id = we.organization_id
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id in (1,27,33,34)
AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC (p_end_date) + 0.99999))
GROUP BY
mmt.transaction_source_id,
mmt.organization_id,
we.entity_type,
mmt.repetitive_line_id,
mmt.operation_seq_num
ORDER BY
SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
mmt.transaction_source_id; -- a consistent tie breaker
SELECT mmt.transaction_source_id entity_id,
mmt.organization_id org_id,
we.entity_type entity_type,
mmt.repetitive_line_id line_id,
SUM(mmt.primary_quantity) net_completion
FROM mtl_material_transactions mmt,
cst_cost_group_assignments ccga,
wip_entities we
WHERE mmt.inventory_item_id = p_item_id
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (31,32)
AND mmt.organization_id = ccga.organization_id
AND ccga.cost_group_id = p_cost_group_id
AND we.wip_entity_id = mmt.transaction_source_id
AND we.organization_id = mmt.organization_id
AND EXISTS(SELECT 1
FROM mtl_material_transactions mmt1
WHERE mmt1.inventory_item_id = we.primary_item_id
AND mmt1.transaction_source_id = we.wip_entity_id
AND mmt1.organization_id = we.organization_id
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id in (1,27,33,34)
AND mmt1.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC (p_end_date) + 0.99999))
/* Exclude eAM entities as only rebuildable jobs can be completed only
in expense subinventories at 0 cost so they should not be costed and no
distributions created */
AND we.entity_type not in (6,7)
GROUP BY
mmt.transaction_source_id,
mmt.organization_id,
we.entity_type,
mmt.repetitive_line_id
ORDER BY
SUM(mmt.primary_quantity) DESC, -- minimize the occurences of negative periodic inventory quantity
mmt.transaction_source_id; -- a consistent tie breaker
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 6, /* Rework Issues */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 6, /* Rework Issues */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/* Bug 11834257: Use update_item_cppb instead of update_cppb */
CSTPPWAC.update_item_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 6, /* Rework Issues */
i_item_id => p_item_id,
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/*CSTPPWAC.update_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 6, /* Rework Issues */
/*i_low_level_code => p_low_level_code,
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);*/
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 7, /* Rework Completions */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
SELECT count(*)
INTO l_final_completion_count
FROM mtl_material_transactions mmt
WHERE mmt.transaction_source_id = c_ent_rec.entity_id
AND nvl(mmt.repetitive_line_id,-99) = nvl( c_ent_rec.line_id,-99)
/* Bug 12703830 */
AND mmt.transaction_action_id = 31
AND mmt.final_completion_flag = 'Y'
AND mmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND ROWNUM < 2;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 7, /* Rework completions */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/* Insert into cppb */
l_stmt_num := 60;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 7, /* Rework completions */
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/* Update cppb */
l_stmt_num := 70;
/* Bug 11834257: Use update_item_cppb instead of update_cppb */
CSTPPWAC.update_item_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 7, /* Rework Completions */
i_item_id => p_item_id,
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);
/*CSTPPWAC.update_cppb(i_pac_period_id => p_pac_period_id,
i_cost_group_id => p_cost_group_id,
i_txn_category => 7, /* Rework Completions */
/*i_low_level_code => p_low_level_code,
i_user_id => p_user_id,
i_login_id => p_login_id,
i_request_id => p_request_id,
i_prog_id => p_prog_id,
i_prog_appl_id => p_prog_app_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg);*/
SELECT decode(inventory_asset_flag,'Y',0,1)
INTO l_exp_item
FROM mtl_system_items msi
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT decode(l_exp_item,1,1,decode(asset_inventory,1,0,1))
INTO l_exp_flag
FROM mtl_secondary_inventories msi
WHERE secondary_inventory_name = p_subinv
AND organization_id = p_org_id;