[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TRANSACTION_ID
FROM WIP_COST_TXN_INTERFACE
WHERE GROUP_ID = p_wcti_group_id;
INSERT INTO wip_transaction_accounts
(transaction_id,
reference_account,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
transaction_date,
wip_entity_id,
repetitive_schedule_id,
accounting_line_type,
transaction_value,
base_transaction_value,
contra_set_id,
primary_quantity,
rate_or_amount,
basis_type,
resource_id,
cost_element_id,
activity_id,
currency_code,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT wcti.transaction_id,
DECODE(cce.cost_element_id,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account),
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
wcti.organization_id,
wcti.transaction_date,
wcti.wip_entity_id,
NULL,
7,
NULL,
SUM(DECODE(cce.cost_element_id,
1, (NVL(wpb.pl_material_out,0)
- NVL(wpb.pl_material_in,0)
+ NVL(wpb.pl_material_var,0)
+ NVL(wpb.tl_material_out,0)
- 0
+ NVL(wpb.tl_material_var,0)),
2, (NVL(wpb.pl_material_overhead_out,0)
- NVL(wpb.pl_material_overhead_in,0)
+ NVL(wpb.pl_material_overhead_var,0)
+ NVL(wpb.tl_material_overhead_out,0)
- 0
+ NVL(wpb.tl_material_overhead_var,0)),
3, (NVL(wpb.pl_resource_out,0)
- NVL(wpb.pl_resource_in,0)
+ NVL(wpb.pl_resource_var,0)
+ NVL(wpb.tl_resource_out,0)
- NVL(wpb.tl_resource_in,0)
+ NVL(wpb.tl_resource_var,0)),
4, (NVL(wpb.pl_outside_processing_out,0)
- NVL(wpb.pl_outside_processing_in,0)
+ NVL(wpb.pl_outside_processing_var,0)
+ NVL(wpb.tl_outside_processing_out,0)
- NVL(wpb.tl_outside_processing_in,0)
+ NVL(wpb.tl_outside_processing_var,0)),
5, (NVL(wpb.pl_overhead_out,0)
- NVL(wpb.pl_overhead_in,0)
+ NVL(wpb.pl_overhead_var,0)
+ NVL(wpb.tl_overhead_out,0)
- NVL(wpb.tl_overhead_in,0)
+ NVL(wpb.tl_overhead_var,0)))),
wcti.wip_entity_id,
NULL,
NULL,
NULL,
NULL,
DECODE((MAX(cce.cost_element_id) - MIN(cce.cost_element_id)),
0, MAX(cce.cost_element_id), NULL),
NULL,
NULL,
NULL,
NULL,
NULL,
p_req_id,
p_prg_appl_id,
p_prg_id,
sysdate
FROM wip_cost_txn_interface wcti,
wip_period_balances wpb,
wip_discrete_jobs wdj,
cst_cost_elements cce
--{BUG#13072387
, org_acct_periods ocpf
, org_acct_periods ocpt
--}
WHERE wcti.group_id = p_wcti_group_id
AND wcti.wip_entity_id = wpb.wip_entity_id
AND wcti.wip_entity_id = wdj.wip_entity_id
--{
AND wcti.acct_period_id = ocpt.acct_period_id
AND wcti.organization_id = ocpt.organization_id
AND wpb.acct_period_id = ocpf.acct_period_id
AND wpb.organization_id = ocpf.organization_id
AND ocpt.period_start_date >= ocpf.period_start_date
--AND wcti.acct_period_id >= wpb.acct_period_id
--}
GROUP BY wcti.transaction_id,
wcti.wip_entity_id,
wcti.organization_id,
wcti.transaction_date,
decode(cce.cost_element_id,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account);
INSERT INTO wip_transaction_accounts
(transaction_id,
reference_account,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
transaction_date,
wip_entity_id,
repetitive_schedule_id,
accounting_line_type,
transaction_value,
base_transaction_value,
contra_set_id,
primary_quantity,
rate_or_amount,
basis_type,
resource_id,
cost_element_id,
activity_id,
currency_code,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT wcti.transaction_id,
DECODE(cce.cost_element_id,
1, wdj.material_variance_account,
3, wdj.resource_variance_account,
4, wdj.outside_proc_variance_account,
5, wdj.overhead_variance_account),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
wcti.organization_id,
wcti.transaction_date,
wcti.wip_entity_id,
NULL,
8,
NULL,
SUM(DECODE(cce.cost_element_id,
1, -1 * ( NVL(wpb.pl_material_out,0)
- NVL(wpb.pl_material_in,0)
+ NVL(wpb.pl_material_var,0)
+ NVL(wpb.pl_material_overhead_out,0)
- NVL(wpb.pl_material_overhead_in,0)
+ NVL(wpb.pl_material_overhead_var,0)
+ NVL(wpb.pl_resource_out,0)
- NVL(wpb.pl_resource_in,0)
+ NVL(wpb.pl_resource_var,0)
+ NVL(wpb.pl_overhead_out,0)
- NVL(wpb.pl_overhead_in,0)
+ NVL(wpb.pl_overhead_var,0)
+ NVL(wpb.pl_outside_processing_out,0)
- NVL(wpb.pl_outside_processing_in,0)
+ NVL(wpb.pl_outside_processing_var,0)
+ NVL(wpb.tl_material_out,0)
- 0
+ NVL(wpb.tl_material_var,0)
+ NVL(wpb.tl_material_overhead_out,0)
- 0
+ NVL(wpb.tl_material_overhead_var,0)),
3, -1 * ( NVL(wpb.tl_resource_out,0)
- NVL(wpb.tl_resource_in,0)
+ NVL(wpb.tl_resource_var,0)),
4, -1 * ( NVL(wpb.tl_outside_processing_out,0)
- NVL(wpb.tl_outside_processing_in,0)
+ NVL(wpb.tl_outside_processing_var,0)),
5, -1 * ( NVL(wpb.tl_overhead_out,0)
- NVL(wpb.tl_overhead_in,0)
+ NVL(wpb.tl_overhead_var,0)))),
wcti.wip_entity_id,
NULL,
NULL,
NULL,
NULL,
DECODE((MAX(cce.cost_element_id) - MIN(cce.cost_element_id)),
0, MAX(cce.cost_element_id), NULL),
NULL,
NULL,
NULL,
NULL,
NULL,
p_req_id,
p_prg_appl_id,
p_prg_id,
SYSDATE
FROM wip_cost_txn_interface wcti,
wip_period_balances wpb,
wip_discrete_jobs wdj,
cst_cost_elements cce
--{BUG#13072387
, org_acct_periods ocpf
, org_acct_periods ocpt
--}
WHERE wcti.group_id = p_wcti_group_id
AND cce.cost_element_id <> 2
AND wcti.wip_entity_id = wpb.wip_entity_id
AND wcti.wip_entity_id = wdj.wip_entity_id
--{
AND wcti.acct_period_id = ocpt.acct_period_id
AND wcti.organization_id= ocpt.organization_id
AND wpb.acct_period_id = ocpf.acct_period_id
AND wpb.organization_id = ocpf.organization_id
AND ocpt.period_start_date >= ocpf.period_start_date
--AND wcti.acct_period_id >= wpb.acct_period_id
--}
GROUP BY wcti.transaction_id,
wcti.wip_entity_id,
wcti.organization_id,
wcti.transaction_date,
DECODE(cce.cost_element_id,
1, wdj.material_variance_account,
3, wdj.resource_variance_account,
4, wdj.outside_proc_variance_account,
5, wdj.overhead_variance_account);
UPDATE WIP_TRANSACTION_ACCOUNTS
SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = l_transaction_t(l_index);
| Update variance columns. |
| While summing across wip_period_balance rows to accumulate costs we |
| do not want the var values in the close period to get picked up. So |
| we need them out with the decode. This is needed since wip now lets |
| you re-open a closed job and variance could be posted multiple |
| times in the same period if the job were closed repeatedly. |
+------------------------------------------------------------------------*/
l_stmt_num := 50;
UPDATE wip_period_balances wpb
SET (last_updated_by,
last_update_date,
last_update_login,
pl_material_var,
pl_material_overhead_var,
pl_resource_var,
pl_outside_processing_var,
pl_overhead_var,
tl_material_var,
tl_material_overhead_var,
tl_resource_var,
tl_outside_processing_var,
tl_overhead_var )
=
(SELECT p_user_id,
SYSDATE,
p_login_id,
SUM( NVL(pl_material_in,0)
- NVL(pl_material_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(pl_material_var,0))),
SUM( NVL(pl_material_overhead_in,0)
- NVL(pl_material_overhead_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(pl_material_overhead_var,0))),
SUM( NVL(pl_resource_in,0)
- NVL(pl_resource_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(pl_resource_var,0))),
SUM( NVL(pl_outside_processing_in,0)
- NVL(pl_outside_processing_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(pl_outside_processing_var,0))),
SUM( NVL(pl_overhead_in,0)
- NVL(pl_overhead_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(pl_overhead_var,0))),
SUM( 0
- NVL(tl_material_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(tl_material_var,0))),
SUM( 0
- NVL(tl_material_overhead_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(tl_material_overhead_var,0))),
SUM( NVL(tl_resource_in,0)
- NVL(tl_resource_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(tl_resource_var,0))),
SUM( NVL(tl_outside_processing_in,0)
- NVL(tl_outside_processing_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(tl_outside_processing_var,0))),
SUM( NVL(tl_overhead_in,0)
- NVL(tl_overhead_out,0)
- DECODE(wpb2.acct_period_id,
wpb.acct_period_id,0,
NVL(tl_overhead_var,0)))
FROM wip_period_balances wpb2
--{BUG#13072387
, org_acct_periods ocpf
, org_acct_periods ocpt
--}
WHERE wpb2.wip_entity_id = wpb.wip_entity_id
--{
--AND wpb2.acct_period_id <= wpb.acct_period_id
AND wpb2.acct_period_id = ocpf.acct_period_id
AND wpb2.organization_id= ocpf.organization_id
AND wpb.acct_period_id = ocpt.acct_period_id
AND wpb.organization_id = ocpt.organization_id
AND ocpf.period_start_date <= ocpt.period_start_date
--}
)
WHERE (wpb.acct_period_id,
wpb.wip_entity_id)
IN (SELECT i.acct_period_id,
i.wip_entity_id
FROM wip_cost_txn_interface i
WHERE i.group_id = p_wcti_group_id);
SELECT primary_cost_method
INTO l_costing_method
FROM mtl_parameters
WHERE organization_id = p_org_id;
| If primary_cost_method is average, FIFO or LIFO then update the |
| value of variance relieved |
+------------------------------------------------------------------------*/
l_stmt_num := 70;
UPDATE wip_req_operation_cost_details w
SET (relieved_variance_value)
= (SELECT NVL(applied_matl_value,0)
- NVL(relieved_matl_completion_value,0)
- NVL(relieved_matl_scrap_value,0)
FROM wip_req_operation_cost_details w2
WHERE w.wip_entity_id = w2.wip_entity_id
AND w.organization_id = w2.organization_id
AND w.inventory_item_id = w2.inventory_item_id
AND w.operation_seq_num = w2.operation_seq_num
AND w.cost_element_id = w2.cost_element_id )
WHERE w.wip_entity_id
IN (SELECT wip_entity_id
FROM wip_cost_txn_interface wcti
WHERE wcti.group_id = p_wcti_group_id );
UPDATE wip_operation_resources w
SET (relieved_variance_value)
= (SELECT NVL(applied_resource_value,0)
- NVL(relieved_res_completion_value,0)
- NVL(relieved_res_scrap_value,0)
FROM wip_operation_resources w2
WHERE w.wip_entity_id = w2.wip_entity_id
AND w.organization_id = w2.organization_id
AND w.operation_seq_num = w2.operation_seq_num
AND w.resource_seq_num = w2.resource_seq_num)
WHERE w.wip_entity_id
IN (SELECT wip_entity_id
FROM wip_cost_txn_interface wcti
WHERE wcti.group_id = p_wcti_group_id);
UPDATE wip_operation_overheads w
SET (relieved_variance_value)
= (SELECT NVL(applied_ovhd_value,0)
- NVL(relieved_ovhd_completion_value,0)
- NVL(relieved_ovhd_scrap_value,0)
FROM wip_operation_overheads w2
WHERE w.wip_entity_id = w2.wip_entity_id
AND w.organization_id = w2.organization_id
AND w.operation_seq_num = w2.operation_seq_num
AND w.resource_seq_num = w2.resource_seq_num
AND w.overhead_id = w2.overhead_id
AND w.basis_type = w2.basis_type )
WHERE w.wip_entity_id
IN (SELECT wip_entity_id
FROM wip_cost_txn_interface wcti
WHERE wcti.group_id = p_wcti_group_id);
| Delete any balance rows beyond the job's close date (accounting |
| period starting date > job close date) |
+------------------------------------------------------------------------*/
l_stmt_num := 80;
DELETE FROM WIP_PERIOD_BALANCES wpb
WHERE (wpb.acct_period_id,
wpb.wip_entity_id)
IN
(SELECT a.acct_period_id,
i.wip_entity_id
FROM wip_cost_txn_interface i,
org_acct_periods a
--{BUG#13072387
, org_acct_periods f
--}
WHERE i.group_id = p_wcti_group_id
--{
AND i.acct_period_id = f.acct_period_id
AND i.organization_id = f.organization_id
AND a.period_start_date > f.period_start_date
--AND a.acct_period_id > i.acct_period_id
--}
AND a.organization_id = i.organization_id);
| delete from wip_cost_txn_interface. |
+------------------------------------------------------------------------*/
l_stmt_num := 90;