[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| There will be one set of rows for the WIP issue and the update
| to WPB should be done using this row. The comm_iss_flag = 1
| indicates that the txn is a CITW. Hence the layer_id and the
| cost_txn_action_id passed in should be joined to in MCACD to
| obtain the correct set of rows.
|--------------------------------------------------------------*/
stmt_num := 20;
UPDATE WIP_PERIOD_BALANCES WPB
SET
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN,
PL_RESOURCE_IN,
PL_OUTSIDE_PROCESSING_IN,
PL_OVERHEAD_IN) =
(SELECT
SYSDATE,
I_USER_ID,
-1,
I_REQUEST_ID,
-1,
-1,
SYSDATE,
nvl(wpb.pl_material_in,0) +
(round((sum(decode(cost_element_id,1,nvl(actual_cost,0),
2,0,
3,0,
4,0,
5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
nvl(wpb.pl_material_overhead_in,0) +
(round((sum(decode(cost_element_id,2,nvl(actual_cost,0),
1,0,
3,0,
4,0,
5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
nvl(wpb.pl_resource_in,0) +
(round((sum(decode(cost_element_id,3,nvl(actual_cost,0),
1,0,
2,0,
4,0,
5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
nvl(wpb.pl_outside_processing_in,0) +
(round((sum(decode(cost_element_id,4,nvl(actual_cost,0),
1,0,
2,0,
3,0,
5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
nvl(wpb.pl_overhead_in,0) +
(round((sum(decode(cost_element_id,5,nvl(actual_cost,0),
1,0,
2,0,
3,0,
4,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit)
FROM
mtl_cst_actual_cost_details
WHERE
TRANSACTION_ID = I_TRX_ID AND
DECODE(I_COMM_ISS_FLAG,1,I_LAYER_ID,LAYER_ID)
= LAYER_ID AND
nvl(DECODE(I_COMM_ISS_FLAG,1,
I_COST_TXN_ACTION_ID,TRANSACTION_ACTION_ID),-99)
= nvl(TRANSACTION_ACTION_ID,-99))
WHERE
WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
ORGANIZATION_ID = I_ORG_ID AND
ACCT_PERIOD_ID = I_PERIOD_ID;
UPDATE WIP_PERIOD_BALANCES WPB
SET
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PL_MATERIAL_OUT,
PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT,
PL_OUTSIDE_PROCESSING_OUT,
PL_OVERHEAD_OUT,
TL_MATERIAL_OUT,
TL_MATERIAL_OVERHEAD_OUT,
TL_RESOURCE_OUT,
TL_OUTSIDE_PROCESSING_OUT,
TL_OVERHEAD_OUT) =
(SELECT
SYSDATE,
I_USER_ID,
-1,
I_REQUEST_ID,
-1,
-1,
SYSDATE,
nvl(wpb.pl_material_out,0) +
(round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,1,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.pl_material_overhead_out,0) +
(round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,2,
nvl(actual_cost,0), 0), 0)))/ l_round_unit)*l_round_unit),
nvl(wpb.pl_resource_out,0) +
(round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,3,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.pl_outside_processing_out,0) +
(round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,4,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.pl_overhead_out,0) +
(round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,5,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.tl_material_out,0) +
(round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,1,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.tl_material_overhead_out,0) + 0, /* The TL MO never gets Cr to the Job*/
nvl(wpb.tl_resource_out,0) +
(round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,3,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.tl_outside_processing_out,0) +
(round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,4,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
nvl(wpb.tl_overhead_out,0) +
(round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,5,
nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit)
FROM
mtl_cst_actual_cost_details
WHERE
TRANSACTION_ID = I_TRX_ID)
WHERE
WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
ORGANIZATION_ID = I_ORG_ID AND
ACCT_PERIOD_ID = I_PERIOD_ID;