[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO wip_period_balances
(acct_period_id, wip_entity_id,
repetitive_schedule_id, last_update_date,
last_updated_by, creation_date,
created_by, last_update_login,
organization_id, class_type,
tl_resource_in, tl_overhead_in, tl_outside_processing_in,
pl_material_in, pl_material_overhead_in, pl_resource_in, pl_overhead_in, pl_outside_processing_in,
tl_material_out, tl_material_overhead_out, tl_resource_out, tl_overhead_out, tl_outside_processing_out,
pl_material_out, pl_material_overhead_out, pl_resource_out, pl_overhead_out, pl_outside_processing_out,
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
oap.acct_period_id, i_wip_entity_id,
NULL, SYSDATE,
i_user_id, SYSDATE,
i_user_id, i_login_id,
i_org_id, wac.class_type,
0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM wip_flow_schedules wcs,
wip_accounting_classes wac,
org_acct_periods oap
WHERE
wcs.organization_id = i_org_id
AND wcs.wip_entity_id = i_wip_entity_id
AND wac.class_code = wcs.class_code
AND wac.organization_id = i_org_id
AND oap.acct_period_id >= i_acct_period_id
AND oap.organization_id = i_org_id
AND oap.acct_period_id >
(SELECT nvl(max(acct_period_id),0)
FROM wip_period_balances
WHERE organization_id = i_org_id
AND wip_entity_id = i_wip_entity_id)
AND NOT EXISTS
(SELECT 'x' FROM wip_period_balances
WHERE organization_id = i_org_id
AND acct_period_id = i_acct_period_id
AND wip_entity_id = i_wip_entity_id);
INSERT INTO mtl_cst_txn_cost_details
(
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_average_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
1,
decode(cce.cost_element_id,
1,sum(0 - nvl(tl_material_out,0)),
2,sum(0 - nvl(tl_material_overhead_out,0)),
3,sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)),
4,sum(nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)),
5,sum(nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0)))/ABS(i_txn_qty),
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
cst_cost_elements cce,
wip_period_balances wpb
WHERE
wpb.wip_entity_id = i_wip_entity_id AND
wpb.organization_id = i_org_id AND
cce.cost_element_id <> 2
GROUP BY
cce.cost_element_id
HAVING
decode(cce.cost_element_id,
1,sum(0 - nvl(tl_material_out,0)),
2,sum(0 - nvl(tl_material_overhead_out,0)),
3,sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)),
4,sum(nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)),
5,sum(nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0))) > 0;
INSERT INTO mtl_cst_txn_cost_details
(
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_average_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
2,
decode(cce.cost_element_id,
1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)),
2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)),
3,sum(nvl(pl_resource_in,0) - nvl(pl_resource_out,0)),
4,sum(nvl(pl_outside_processing_in,0)- nvl(pl_outside_processing_out,0)),
5,sum(nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0)))/ABS(i_txn_qty),
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
cst_cost_elements cce,
wip_period_balances wpb
WHERE
wpb.wip_entity_id = i_wip_entity_id AND
wpb.organization_id = i_org_id
GROUP BY
cce.cost_element_id
HAVING
decode(cce.cost_element_id,
1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)),
2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)),
3,sum(nvl(pl_resource_in,0) - nvl(pl_resource_out,0)),
4,sum(nvl(pl_outside_processing_in,0)- nvl(pl_outside_processing_out,0)),
5,sum(nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0))) > 0;
INSERT INTO WIP_SCRAP_VALUES
(
transaction_id,
level_type,
cost_element_id,
cost_update_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
cost_element_value,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT
i_trx_id,
level_type,
cost_element_id,
NULL,
SYSDATE,
i_user_id,
i_user_id,
SYSDATE,
i_login_id,
transaction_cost,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
mtl_cst_txn_cost_details
WHERE
transaction_id = i_trx_id;
INSERT INTO mtl_cst_txn_cost_details
(
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_average_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
1,
decode(cce.cost_element_id,
1,sum(nvl(tl_material_out,0) - 0),
2,sum(nvl(tl_material_overhead_out,0) - 0),
3,sum(nvl(tl_resource_out,0) - nvl(tl_resource_in,0)),
4,sum(nvl(tl_outside_processing_out,0)- nvl(tl_outside_processing_in,0)),
5,sum(nvl(tl_overhead_out,0) - nvl(tl_overhead_in,0)))/ABS(i_txn_qty),
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
cst_cost_elements cce,
wip_period_balances wpb
WHERE
wpb.wip_entity_id = i_wip_entity_id AND
wpb.organization_id = i_org_id AND
cce.cost_element_id <> 2
GROUP BY
cce.cost_element_id
HAVING
decode(cce.cost_element_id,
1,sum(nvl(tl_material_out,0) - 0),
2,sum(nvl(tl_material_overhead_out,0) - 0),
3,sum(nvl(tl_resource_out,0) - nvl(tl_resource_in,0)),
4,sum(nvl(tl_outside_processing_out,0)- nvl(tl_outside_processing_in,0)),
5,sum(nvl(tl_overhead_out,0) - nvl(tl_overhead_in,0))) > 0;
INSERT INTO mtl_cst_txn_cost_details
(
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_average_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
2,
decode(cce.cost_element_id,
1,sum(nvl(pl_material_out,0) - nvl(pl_material_in,0)),
2,sum(nvl(pl_material_overhead_out,0) - nvl(pl_material_overhead_in,0)),
3,sum(nvl(pl_resource_out,0) - nvl(pl_resource_in,0)),
4,sum(nvl(pl_outside_processing_out,0)- nvl(pl_outside_processing_in,0)),
5,sum(nvl(pl_overhead_out,0) - nvl(pl_overhead_in,0)))/ABS(i_txn_qty),
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
cst_cost_elements cce,
wip_period_balances wpb
WHERE
wpb.wip_entity_id = i_wip_entity_id AND
wpb.organization_id = i_org_id
GROUP BY
cce.cost_element_id
HAVING
decode(cce.cost_element_id,
1,sum(nvl(pl_material_out,0) - nvl(pl_material_in,0)),
2,sum(nvl(pl_material_overhead_out,0) - nvl(pl_material_overhead_in,0)),
3,sum(nvl(pl_resource_out,0) - nvl(pl_resource_in,0)),
4,sum(nvl(pl_outside_processing_out,0)- nvl(pl_outside_processing_in,0)),
5,sum(nvl(pl_overhead_out,0) - nvl(pl_overhead_in,0))) > 0;
INSERT INTO WIP_SCRAP_VALUES
(
transaction_id,
level_type,
cost_element_id,
cost_update_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
cost_element_value,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT
i_trx_id,
level_type,
cost_element_id,
NULL,
SYSDATE,
i_user_id,
i_user_id,
SYSDATE,
i_login_id,
transaction_cost,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
mtl_cst_txn_cost_details
WHERE
transaction_id = i_trx_id;
select count(*)
into
l_rowcount
from
wip_period_balances
where
wip_entity_id = i_wip_entity_id and
acct_period_id = i_acct_period_id;
select wip_transactions_s.nextval
into
l_txn_id from dual;
INSERT INTO wip_transaction_accounts
(WIP_SUB_LEDGER_ID,
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
CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
l_txn_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,i_user_id,SYSDATE,i_user_id,i_login_id,
i_org_id,i_txn_date,i_wip_entity_id,
NULL,7,NULL,
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))),
i_wip_entity_id,NULL, NULL, NULL, NULL,
cce.cost_element_id,
NULL, NULL, NULL, NULL, NULL,
i_request_id,i_prog_appl_id,i_prog_id,SYSDATE
FROM
wip_period_balances wpb,
wip_flow_schedules wdj,
cst_cost_elements cce
WHERE
wpb.wip_entity_id = wdj.wip_entity_id and
wdj.wip_entity_id = i_wip_entity_id and
wpb.acct_period_id = i_acct_period_id;
INSERT INTO wip_transaction_accounts
( WIP_SUB_LEDGER_ID,
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
CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
l_txn_id,
decode(cce.cost_element_id,
1, wfs.material_variance_account,
3, wfs.resource_variance_account,
4, wfs.outside_proc_variance_account,
5, wfs.overhead_variance_account),
SYSDATE,i_user_id,SYSDATE,i_user_id,i_login_id,
i_org_id,i_txn_date,i_wip_entity_id,
NULL,8,NULL,
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))),
i_wip_entity_id,NULL, NULL, NULL, NULL,
cce.cost_element_id,
NULL, NULL, NULL, NULL, NULL,
i_request_id,i_prog_appl_id,i_prog_id,SYSDATE
FROM
wip_period_balances wpb,
wip_flow_schedules wfs,
cst_cost_elements cce
WHERE
wpb.wip_entity_id = wfs.wip_entity_id and
wpb.acct_period_id = i_acct_period_id and
wfs.wip_entity_id = i_wip_entity_id and
cce.cost_element_id <> 2;
-- Update WPB
stmt_num := 40;
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 i_user_id, SYSDATE, i_login_id,
NVL(PL_MATERIAL_IN,0)
- NVL(PL_MATERIAL_OUT,0),
NVL(PL_MATERIAL_OVERHEAD_IN,0)
- NVL(PL_MATERIAL_OVERHEAD_OUT,0),
NVL(PL_RESOURCE_IN,0)
- NVL(PL_RESOURCE_OUT,0),
NVL(PL_OUTSIDE_PROCESSING_IN,0)
- NVL(PL_OUTSIDE_PROCESSING_OUT,0),
NVL(PL_OVERHEAD_IN,0)
- NVL(PL_OVERHEAD_OUT,0),
0
- NVL(TL_MATERIAL_OUT,0),
0
- NVL(TL_MATERIAL_OVERHEAD_OUT,0),
NVL(TL_RESOURCE_IN,0)
- NVL(TL_RESOURCE_OUT,0),
NVL(TL_OUTSIDE_PROCESSING_IN,0)
- NVL(TL_OUTSIDE_PROCESSING_OUT,0),
NVL(TL_OVERHEAD_IN,0)
- NVL(TL_OVERHEAD_OUT,0)
FROM WIP_PERIOD_BALANCES wpb2
WHERE wpb2.wip_entity_id = wpb.wip_entity_id
AND wpb2.acct_period_id = wpb.acct_period_id)
WHERE
wpb.wip_entity_id = i_wip_entity_id AND
wpb.acct_period_id = i_acct_period_id;
INSERT INTO WIP_TRANSACTIONS
(TRANSACTION_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, WIP_ENTITY_ID,
ACCT_PERIOD_ID, DEPARTMENT_ID,
TRANSACTION_TYPE, TRANSACTION_DATE,
LINE_ID, SOURCE_CODE,
SOURCE_LINE_ID, OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM, EMPLOYEE_ID,
RESOURCE_ID, AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, USAGE_RATE_OR_AMOUNT,
BASIS_TYPE, TRANSACTION_QUANTITY,
TRANSACTION_UOM, PRIMARY_QUANTITY,
PRIMARY_UOM, ACTUAL_RESOURCE_RATE,
STANDARD_RESOURCE_RATE, CURRENCY_CODE,
CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE, CURRENCY_ACTUAL_RESOURCE_RATE,
ACTIVITY_ID, REASON_ID,
REFERENCE, MOVE_TRANSACTION_ID,
PO_HEADER_ID, PO_LINE_ID,
RCV_TRANSACTION_ID, PRIMARY_ITEM_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,
ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
GROUP_ID,
project_id,
task_id,
pm_cost_collected)
SELECT
l_txn_id, SYSDATE,
i_user_id, SYSDATE,
i_user_id, i_login_id,
i_org_id, i_wip_entity_id,
i_acct_period_id, NULL,
6, i_txn_date,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL,
i_request_id, i_prog_appl_id,
i_prog_id, SYSDATE,
NULL, NULL,
NULL, NULL
from dual;