[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO eni_currency_conv_rates_stg
(currency_code,
effective_date,
primary_rate,
secondary_rate)
SELECT currency_code,
effective_date,
decode(l_prim_currency_code, NULL, TO_NUMBER(NULL),
fii_currency.get_rate(currency_code, l_prim_currency_code,
effective_date, l_prim_rate_type)) primary_rate,
decode(l_sec_currency_code, NULL, TO_NUMBER(NULL),
fii_currency.get_rate(currency_code, l_sec_currency_code,
effective_date, l_sec_rate_type)) secondary_rate
FROM (SELECT /*+ PARALLEL(tmp) */ DISTINCT currency_code ,
effective_date FROM eni_dbi_item_cost_stg tmp);
INSERT INTO eni_currency_conv_rates_stg
(currency_code,
effective_date,
primary_rate,
secondary_rate)
SELECT currency_code,
effective_date,
decode(l_prim_currency_code, NULL, TO_NUMBER(NULL),
fii_currency.get_rate(currency_code, l_prim_currency_code,
effective_date, l_prim_rate_type)) primary_rate,
decode(l_sec_currency_code, NULL, TO_NUMBER(NULL),
fii_currency.get_rate(currency_code, l_sec_currency_code,
effective_date, l_sec_rate_type)) secondary_rate
FROM (SELECT DISTINCT currency_code ,
effective_date FROM eni_dbi_item_cost_stg);
eni_dbi_util_pkg.log('Inserted ' ||sql%ROWCOUNT || ' currency rates into rates table');
select nvl(max(1),0)
into l_exists_sc_orgs
from sys.dual
where exists (
select 'There are standard costing orgs'
from mtl_parameters
where primary_cost_method = 1
);
select nvl(max(1),0)
into l_exists_ac_orgs
from sys.dual
where exists (
select 'There are Avg/LIFO/FIFO costing orgs'
from mtl_parameters
where primary_cost_method <> 1
);
select FND_GLOBAL.USER_ID
into l_application_user_id
from sys.dual;
insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
(effective_date,
inventory_item_id,
organization_id,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
currency_code,
conversion_rate)
select effective_date,
inventory_item_id,
organization_id,
sum(standard_cost) item_cost,
nvl(sum(decode(cost_element_id, 1, standard_cost)), 0) material_cost,
nvl(sum(decode(cost_element_id, 2, standard_cost)), 0) material_overhead_cost,
nvl(sum(decode(cost_element_id, 3, standard_cost)), 0) resource_cost,
nvl(sum(decode(cost_element_id, 4, standard_cost)), 0) outside_processing_cost,
nvl(sum(decode(cost_element_id, 5, standard_cost)), 0) overhead_cost,
sysdate last_update_date,
l_application_user_id last_updated_by,
sysdate creation_date,
l_application_user_id created_by,
l_application_user_id last_update_login,
currency_code,
null --fii_currency.get_global_rate_primary(currency_code, effective_date) conversion_rate
from (
select /*+ parallel(cec) parallel(hoi) */
cec.inventory_item_id,
cec.organization_id,
trunc(cec.last_update_date) effective_date,
gsob.currency_code,
cec.cost_element_id,
cec.standard_cost,
rank() over (partition by cec.inventory_item_id, cec.organization_id, trunc(cec.last_update_date),
gsob.currency_code order by cec.cost_update_id desc) r
from cst_elemental_costs cec,
hr_organization_information hoi,
gl_sets_of_books gsob
where cec.organization_id = hoi.organization_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char (gsob.set_of_books_id)
and cec.last_update_date >= l_start_date
and cec.last_update_date - 0 <= l_end_date + 0.99999
)
where r = 1
group by effective_date, inventory_item_id, organization_id, currency_code;
insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
(effective_date,
inventory_item_id, organization_id, item_cost, material_cost,
material_overhead_cost, resource_cost, outside_processing_cost,
overhead_cost, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, currency_code, conversion_rate)
select /*+ parallel (x) parallel (mcacd) use_hash (mcacd, hoi, gsob)
swap_join_inputs (gsob) pq_distribute (mcacd, hash, hash)
pq_distribute
(gsob, none, broadcast) */
trunc(x.asofdate),
mcacd.inventory_item_id,
mcacd.organization_id,
sum (mcacd.new_cost),
nvl(sum(decode(mcacd.cost_element_id, 1, mcacd.new_cost)), 0) mtl,
nvl(sum(decode(mcacd.cost_element_id, 2, mcacd.new_cost)), 0) mtl_ovh,
nvl(sum(decode(mcacd.cost_element_id, 3, mcacd.new_cost)), 0) res,
nvl(sum(decode(mcacd.cost_element_id, 4, mcacd.new_cost)), 0) osp,
nvl(sum(decode(mcacd.cost_element_id, 5, mcacd.new_cost)), 0) ovhd,
sysdate,
1,
sysdate,
1,
1,
gsob.currency_code,
null --fii_currency.get_global_rate_primary (gsob.currency_code,trunc(x.asofdate))
from (
select /*+ no_merge parallel(mmt) parallel(cql) parallel(mp) full(mmt)
swap_join_inputs(cql) */ mmt.inventory_item_id,
mmt.organization_id,
cql.layer_id, max(mmt.transaction_id) transaction_id,
trunc (mmt.transaction_date) asofdate
from mtl_material_transactions mmt,
cst_quantity_layers cql,
mtl_parameters mp
where mp.primary_cost_method <> 1
and mp.default_cost_group_id = mmt.cost_group_id
and mp.organization_id = mmt.organization_id
and mmt.transaction_date >= l_start_date
and mmt.transaction_date - 0 <= l_end_date + 0.99999
and mmt.inventory_item_id = cql.inventory_item_id
and mmt.organization_id = cql.organization_id
and mmt.cost_group_id = cql.cost_group_id
group by mmt.inventory_item_id, mmt.organization_id,
cql.layer_id,
trunc (mmt.transaction_date)) x,
mtl_cst_actual_cost_details mcacd,
hr_organization_information hoi,
gl_sets_of_books gsob
where mcacd.transaction_id = x.transaction_id
and mcacd.organization_id = x.organization_id
and mcacd.layer_id = x.layer_id
and x.organization_id = hoi.organization_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char (gsob.set_of_books_id)
group by trunc (x.asofdate), mcacd.inventory_item_id,
mcacd.organization_id,
gsob.currency_code;
insert /*+ append parallel(a) */ into eni_dbi_item_cost_f a
(effective_date,
inventory_item_id,
organization_id,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
primary_currency_rate,
secondary_currency_rate,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
select /*+ parallel(edicstg) parallel(eccrstg) */
edicstg.effective_date,
edicstg.inventory_item_id,
edicstg.organization_id,
edicstg.item_cost,
edicstg.material_cost,
edicstg.material_overhead_cost,
edicstg.resource_cost,
edicstg.outside_processing_cost,
edicstg.overhead_cost,
eccrstg.primary_rate,
eccrstg.secondary_rate,
edicstg.last_update_date,
edicstg.last_updated_by,
edicstg.creation_date,
edicstg.created_by,
edicstg.last_update_login
from eni_dbi_item_cost_stg edicstg, eni_currency_conv_rates_stg eccrstg
where edicstg.currency_code = eccrstg.currency_code
and edicstg.effective_date = eccrstg.effective_date;
to SYSDATE now and the mmt table rows are not updated once inserted.
*/
SELECT Max(TRANSACTION_ID)
INTO l_processed_txn_id
FROM MTL_MATERIAL_TRANSACTIONS mmt;
Store the max cost_update_id from cec into bis_refresh_log table
We are simply picking the max cost update id as the end date is defaulted
to SYSDATE now and the cec table rows are not updated once inserted.
*/
SELECT Max(COST_UPDATE_ID)
INTO l_processed_cost_id
FROM cst_elemental_costs cec;
SELECT effective_date,
currency_code,
primary_rate conversion_rate,
secondary_rate conversion_rate_s--,
-- decode(primary_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') PRIMARY_STATUS,
-- decode(secondary_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') SECONDARY_STATUS
FROM eni_currency_conv_rates_stg
WHERE (nvl(primary_rate,99) < 0 OR nvl(secondary_rate,99) < 0)
AND effective_date IS NOT NULL;
select distinct currency_code, effective_date, conversion_rate, conversion_rate_s
from eni_dbi_item_cost_stg
where (NVL(conversion_rate,-99) < 0
OR NVL(conversion_rate_s,-99) < 0)
AND effective_date IS NOT NULL;*/
Fetch the last Processed transaction id and cost update id from bis refresh log table.
**/
SELECT MAX(decode(brl.attribute1,'mtl_material_transactions',attribute2,NULL))
,MAX(decode(brl.attribute3,'cst_elemental_costs' ,attribute4,NULL))
INTO l_processed_txn_id, l_processed_cost_id
FROM bis_refresh_log brl
WHERE brl.object_name = 'eni_dbi_item_cost_f';
eni_dbi_util_pkg.log('Last processed cost update_id from cst_elemental_costs table as stored in bis_refresh_log table is:' || l_processed_cost_id);
select FND_GLOBAL.USER_ID
into l_application_user_id
from sys.dual;
select FND_GLOBAL.LOGiN_ID
into l_login_id
from sys.dual;
select NVL(max(1),0)
into rows_in_stage
from eni_dbi_item_cost_stg;
insert into /*+ append parallel(a) +*/ eni_dbi_item_cost_f
(effective_date,
inventory_item_id,
organization_id,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
primary_currency_rate,
secondary_currency_rate,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
select /*+ parallel(edicstg) parallel(eccrstg) */
edicstg.effective_date,
edicstg.inventory_item_id,
edicstg.organization_id,
edicstg.item_cost,
edicstg.material_cost,
edicstg.material_overhead_cost,
edicstg.resource_cost,
edicstg.outside_processing_cost,
edicstg.overhead_cost,
eccrstg.primary_rate,
eccrstg.secondary_rate,
edicstg.last_update_date,
edicstg.last_updated_by,
edicstg.creation_date,
edicstg.created_by,
edicstg.last_update_login
from eni_dbi_item_cost_stg edicstg, eni_currency_conv_rates_stg eccrstg
where edicstg.currency_code = eccrstg.currency_code
and edicstg.effective_date = eccrstg.effective_date;
select nvl(max(1),0)
into l_exists_sc_orgs
from sys.dual
where exists (
select 'There are standard costing orgs'
from mtl_parameters
where primary_cost_method = 1
);
select nvl(max(1),0)
into l_exists_ac_orgs
from sys.dual
where exists (
select 'There are Avg/LIFO/FIFO costing orgs'
from mtl_parameters
where primary_cost_method <> 1
);
SELECT NVL( MAX( cost_update_id), 0)
INTO l_processed_cost_id
FROM cst_elemental_costs cec
WHERE cec.last_update_date < l_last_run_to_date;
eni_dbi_util_pkg.log('Processing cst_elemental_costs.cost_update_id greater than ' || l_processed_cost_id);
insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
(effective_date,
inventory_item_id,
organization_id,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
currency_code,
conversion_rate)
select effective_date,
inventory_item_id,
organization_id,
sum(standard_cost) item_cost,
nvl(sum(decode(cost_element_id, 1, standard_cost)), 0) material_cost,
nvl(sum(decode(cost_element_id, 2, standard_cost)), 0) material_overhead_cost,
nvl(sum(decode(cost_element_id, 3, standard_cost)), 0) resource_cost,
nvl(sum(decode(cost_element_id, 4, standard_cost)), 0) outside_processing_cost,
nvl(sum(decode(cost_element_id, 5, standard_cost)), 0) overhead_cost,
sysdate last_update_date,
l_application_user_id last_updated_by,
sysdate creation_date,
l_application_user_id created_by,
l_application_user_id last_update_login,
currency_code,
null --fii_currency.get_global_rate_primary(currency_code, effective_date) conversion_rate
from (
select
cec.inventory_item_id,
cec.organization_id,
trunc(cec.last_update_date) effective_date,
gsob.currency_code,
cec.cost_element_id,
cec.standard_cost,
rank() over (partition by cec.inventory_item_id, cec.organization_id, trunc(cec.last_update_date),
gsob.currency_code order by cec.cost_update_id desc) r
from cst_elemental_costs cec,
hr_organization_information hoi,
gl_sets_of_books gsob
where cec.organization_id = hoi.organization_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char (gsob.set_of_books_id)
and cec.cost_update_id >= l_processed_cost_id
)
where r = 1
group by effective_date, inventory_item_id, organization_id, currency_code;
SELECT NVL( MAX( transaction_id), 0)
INTO l_processed_txn_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_date < l_last_run_to_date;
insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
(effective_date,
inventory_item_id, organization_id, item_cost, material_cost,
material_overhead_cost, resource_cost, outside_processing_cost,
overhead_cost, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, currency_code, conversion_rate)
select /*+ parallel (x) parallel (mcacd) use_hash (mcacd, hoi, gsob)
swap_join_inputs (gsob) pq_distribute (mcacd, hash, hash)
pq_distribute
(gsob, none, broadcast) */
trunc(x.asofdate),
mcacd.inventory_item_id,
mcacd.organization_id,
sum (mcacd.new_cost),
nvl(sum(decode(mcacd.cost_element_id, 1, mcacd.new_cost)), 0) mtl,
nvl(sum(decode(mcacd.cost_element_id, 2, mcacd.new_cost)), 0) mtl_ovh,
nvl(sum(decode(mcacd.cost_element_id, 3, mcacd.new_cost)), 0) res,
nvl(sum(decode(mcacd.cost_element_id, 4, mcacd.new_cost)), 0) osp,
nvl(sum(decode(mcacd.cost_element_id, 5, mcacd.new_cost)), 0) ovhd,
sysdate,
1,
sysdate,
1,
1,
gsob.currency_code,
null --fii_currency.get_global_rate_primary (gsob.currency_code,trunc(x.asofdate))
from (
select /*+ no_merge parallel(mmt) parallel(cql) parallel(mp)
swap_join_inputs(cql) */ mmt.inventory_item_id,
mmt.organization_id,
cql.layer_id, max(mmt.transaction_id) transaction_id,
trunc (mmt.transaction_date) asofdate
from mtl_material_transactions mmt,
cst_quantity_layers cql,
mtl_parameters mp
where mp.primary_cost_method <> 1
and mp.default_cost_group_id = mmt.cost_group_id
and mp.organization_id = mmt.organization_id
and mmt.transaction_id > l_processed_txn_id
and mmt.inventory_item_id = cql.inventory_item_id
and mmt.organization_id = cql.organization_id
and mmt.cost_group_id = cql.cost_group_id
group by mmt.inventory_item_id, mmt.organization_id,
cql.layer_id,
trunc (mmt.transaction_date)) x,
mtl_cst_actual_cost_details mcacd,
hr_organization_information hoi,
gl_sets_of_books gsob
where mcacd.transaction_id = x.transaction_id
and mcacd.organization_id = x.organization_id
and mcacd.layer_id = x.layer_id
and x.organization_id = hoi.organization_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char (gsob.set_of_books_id)
group by trunc (x.asofdate), mcacd.inventory_item_id,
mcacd.organization_id,
gsob.currency_code;
(select
edicstg.effective_date,
edicstg.inventory_item_id,
edicstg.organization_id,
edicstg.item_cost item_cost,
edicstg.material_cost material_cost,
edicstg.material_overhead_cost material_overhead_cost,
edicstg.resource_cost resource_cost,
edicstg.outside_processing_cost outside_processing_cost,
edicstg.overhead_cost overhead_cost,
eccrstg.primary_rate primary_rate,
eccrstg.secondary_rate secondary_rate,
edicstg.last_update_date last_update_date,
edicstg.last_updated_by last_updated_by,
edicstg.creation_date creation_date,
edicstg.created_by created_by,
edicstg.last_update_login last_update_login
from eni_dbi_item_cost_stg edicstg, eni_currency_conv_rates_stg eccrstg
where edicstg.currency_code = eccrstg.currency_code
and edicstg.effective_date = eccrstg.effective_date
-- group by
-- effective_date,
-- inventory_item_id,
-- organization_id
) new_costs
on
(old_costs.inventory_item_id = new_costs.inventory_item_id and
old_costs.organization_id = new_costs.organization_id and
old_costs.effective_date = new_costs.effective_date)
when matched then
update set old_costs.item_cost = new_costs.item_cost,
old_costs.material_cost = new_costs.material_cost,
old_costs.material_overhead_cost = new_costs.material_overhead_cost,
old_costs.resource_cost = new_costs.resource_cost,
old_costs.outside_processing_cost = new_costs.outside_processing_cost,
old_costs.overhead_cost = new_costs.overhead_cost,
old_costs.primary_currency_rate = new_costs.primary_rate,
old_costs.secondary_currency_rate = new_costs.secondary_rate,
old_costs.last_update_date = sysdate,
old_costs.last_updated_by = l_application_user_id,
old_costs.last_update_login = l_application_user_id
when not matched then
insert (old_costs.effective_date
, old_costs.inventory_item_id
, old_costs.organization_id
, old_costs.item_cost
, old_costs.material_cost
, old_costs.material_overhead_cost
, old_costs.resource_cost
, old_costs.outside_processing_cost
, old_costs.overhead_cost
, old_costs.primary_currency_rate
, old_costs.secondary_currency_rate
, old_costs.last_update_date
, old_costs.last_updated_by
, old_costs.creation_date
, old_costs.created_by
, old_costs.last_update_login)
values ( new_costs.effective_date
, new_costs.inventory_item_id
, new_costs.organization_id
, new_costs.item_cost
, new_costs.material_cost
, new_costs.material_overhead_cost
, new_costs.resource_cost
, new_costs.outside_processing_cost
, new_costs.overhead_cost
, new_costs.primary_rate
, new_costs.secondary_rate
, sysdate
, l_application_user_id
, sysdate
, l_application_user_id
, l_application_user_id
);
SELECT Max(TRANSACTION_ID)
INTO l_processed_txn_id
FROM MTL_MATERIAL_TRANSACTIONS;
SELECT Max(COST_UPDATE_ID)
INTO l_processed_cost_id
FROM cst_elemental_costs;