[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert /*+ append parallel(isc_dr_costs_stg) */
into isc_dr_costs_stg
( repair_line_id
, work_order_id
, func_currency_code
, date_closed
, material_cost_b
, labor_cost_b
, expense_cost_b
)
select /*+ ordered use_hash( crjx, rof,wpb, hoi, gsob)
parallel (crjx) parallel(rof) parallel(wpb) parallel(hoi) */
rof.repair_line_id repair_line_id
, crjx.wip_entity_id work_order_id
, gsob.currency_code func_currency_code
, rof.dbi_date_closed date_closed
, sum( nvl(wpb.pl_material_in,0) ) material_cost_b
, sum( nvl(wpb.tl_resource_in,0) + nvl(wpb.pl_resource_in,0) ) labor_cost_b
, sum( nvl(pl_material_overhead_in,0)
+ nvl(tl_overhead_in,0)
+ nvl(pl_overhead_in,0)
+ nvl(tl_outside_processing_in,0)
+ nvl(pl_outside_processing_in,0) ) expense_cost_b
from
isc_dr_repair_orders_f rof
, csd_repair_job_xref crjx
, wip_period_balances wpb
, ( select wip_entity_id
from csd_repair_job_xref xref
group by xref.wip_entity_id
having count(1) = 1
) crjx1
, hr_organization_information hoi
, gl_sets_of_books gsob
where
rof.repair_line_id = crjx.repair_line_id
and crjx.wip_entity_id = wpb.wip_entity_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char(gsob.set_of_books_id)
and hoi.organization_id = wpb.organization_id
and rof.status = 'C'
and crjx1.wip_entity_id = wpb.wip_entity_id
and rof.ro_creation_date >= p_run_date
group by
crjx.wip_entity_id
, rof.repair_line_id
, gsob.currency_code
, rof.dbi_date_closed;
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' insert staging table', 2 );
insert /*+ append parallel(isc_dr_costs_stg) */
into isc_dr_costs_stg
( repair_line_id
, work_order_id
, func_currency_code
, date_closed
, material_cost_b
, labor_cost_b
, expense_cost_b
)
select
rof.repair_line_id repair_line_id
, crjx.wip_entity_id work_order_id
, gsob.currency_code func_currency_code
, rof.dbi_date_closed date_closed
, sum( nvl(wpb.pl_material_in,0) ) material_cost_b
, sum( nvl(wpb.tl_resource_in,0) + nvl(wpb.pl_resource_in,0) ) labor_cost_b
, sum( nvl(pl_material_overhead_in,0)
+ nvl(tl_overhead_in,0)
+ nvl(pl_overhead_in,0)
+ nvl(tl_outside_processing_in,0)
+ nvl(pl_outside_processing_in,0)
) expense_cost_b
from
isc_dr_repair_orders_f rof
, csd_repair_job_xref crjx
, wip_period_balances wpb
, ( select wip_entity_id
from csd_repair_job_xref xref
group by xref.wip_entity_id
having count (1) = 1
) crjx1
, hr_organization_information hoi
, gl_sets_of_books gsob
where
rof.repair_line_id = crjx.repair_line_id
and crjx.wip_entity_id = wpb.wip_entity_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char(gsob.set_of_books_id)
and hoi.organization_id = wpb.organization_id
and rof.status = 'C'
and crjx1.wip_entity_id = wpb.wip_entity_id
and rof.date_closed >= p_run_date
and rof.ro_creation_date >= g_global_start_date
group by
crjx.wip_entity_id
, rof.repair_line_id
, gsob.currency_code
, rof.dbi_date_closed;
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' insert staging table', 2 );
select
func_currency_code
, date_closed
, g_conv_rate
, sg_conv_rate
from
isc_dr_costs_conv_tmp
where
g_conv_rate < 0
or ( sg_conv_rate < 0 and g_global_sec_curr_code is not null );
insert
into isc_dr_costs_conv_tmp
( func_currency_code
, date_closed
, g_conv_rate
, sg_conv_rate
)
select
costs.func_currency_code
, costs.date_closed
, decode( costs.func_currency_code
, g_global_curr_code, 1
, fii_currency.get_rate( costs.func_currency_code
, g_global_curr_code
, costs.date_closed
, g_global_rate_type
)
) g_conv_rate
, decode( g_global_sec_curr_code
, null, null
, costs.func_currency_code, 1
, fii_currency.get_rate( costs.func_currency_code
, g_global_sec_curr_code
, costs.date_closed
, g_global_sec_rate_type
)
) sg_conv_rate
from
( select distinct
func_currency_code
, date_closed
from isc_dr_costs_stg
order by func_currency_code, date_closed
) costs;
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into currency conversion table', 2 );
delete
from isc_dr_inc
where fact_name = 'ISC_DR_CHARGES_F';
bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
insert /*+ append parallel(isc_dr_charges_f) */
into isc_dr_charges_f
( repair_line_id
, material_charges_g
, labor_charges_g
, expense_charges_g
, material_charges_sg
, labor_charges_sg
, expense_charges_sg
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select /*+ use_hash(cra, rof, cral, ced, ibsf, ctbt)
parallel(cra) parallel(rof) parallel(cral) parallel(ced) parallel(ibsf) parallel(ctbt) */
rof.repair_line_id repair_line_id
, sum( case
when cbtc.billing_category = 'M' then
ibsf.fulfilled_amt_g
else 0
end
) material_charges_g
, sum( case
when cbtc.billing_category = 'L' then
ibsf.fulfilled_amt_g
else 0
end
) labor_charges_g
, sum( case
when cbtc.billing_category = 'E' then
ibsf.fulfilled_amt_g
else 0
end
) expense_charges_g
, sum( case
when cbtc.billing_category = 'M' then
ibsf.fulfilled_amt_g1
else 0
end
) material_charges_sg
, sum( case
when cbtc.billing_category = 'L' then
ibsf.fulfilled_amt_g1
else 0
end
) labor_charges_sg
, sum( case
when cbtc.billing_category = 'E' then
ibsf.fulfilled_amt_g1
else 0
end
) expense_charges_sg
, g_user_id created_by
, sysdate creation_date
, sysdate last_update_date
, g_user_id last_updated_by
, g_login_id last_update_login
, g_program_id program_id
, g_program_login_id program_login_id
, g_program_application_id program_application_id
, g_request_id request_id
from
csd_repair_actuals cra
, isc_dr_repair_orders_f rof
, csd_repair_actual_lines cral
, cs_estimate_details ced
, isc_book_sum2_f ibsf
, cs_txn_billing_types ctbt
, cs_billing_type_categories cbtc
where
rof.repair_line_id = cra.repair_line_id
and cra.repair_actual_id = cral.repair_actual_id
and ced.estimate_detail_id = cral.estimate_detail_id
and ced.order_line_id = ibsf.line_id
and ced.txn_billing_type_id = ctbt.txn_billing_type_id
and ctbt.billing_type = cbtc.billing_type
and rof.repair_mode = 'WIP'
and rof.status = 'C'
and rof.ro_creation_date >= g_global_start_date
group by rof.repair_line_id;
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_CHARGES_F' , 1 );
insert into isc_dr_inc
( fact_name
, last_run_date
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( 'ISC_DR_CHARGES_F'
, l_ro_last_run_date
, g_user_id
, sysdate
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
);
bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
delete
from isc_dr_inc
where fact_name = 'ISC_DR_COSTS_F';
bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
insert /*+ append parallel(isc_dr_costs_f) */
into isc_dr_costs_f
( repair_line_id
, material_cost_g
, labor_cost_g
, expense_cost_g
, material_cost_sg
, labor_cost_sg
, expense_cost_sg
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select
stg.repair_line_id repair_line_id
, nvl(sum( stg.material_cost_b * rates.g_conv_rate ), 0) material_cost_g
, nvl(sum( stg.labor_cost_b * rates.g_conv_rate ), 0) labor_cost_g
, nvl(sum( stg.expense_cost_b * rates.g_conv_rate ), 0) expense_cost_g
, nvl(sum( stg.material_cost_b * rates.sg_conv_rate ), 0) material_cost_sg
, nvl(sum( stg.labor_cost_b * rates.sg_conv_rate ), 0) labor_cost_sg
, nvl(sum( stg.expense_cost_b * rates.sg_conv_rate ), 0) expense_cost_sg
, g_user_id created_by
, sysdate creation_date
, sysdate last_update_date
, g_user_id last_updated_by
, g_login_id last_update_login
, g_program_id program_id
, g_program_login_id program_login_id
, g_program_application_id program_application_id
, g_request_id request_id
from
isc_dr_costs_stg stg
, isc_dr_costs_conv_tmp rates
where
stg.func_currency_code = rates.func_currency_code
and stg.date_closed = rates.date_closed
group by stg.repair_line_id;
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_COSTS_F' , 1 );
insert into
isc_dr_inc
( fact_name
, last_run_date
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( 'ISC_DR_COSTS_F'
, l_ro_last_run_date
, g_user_id
, sysdate
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
);
bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
( select rof.repair_line_id repair_line_id
, sum( case
when cbtc.billing_category = 'M' then
ibsf.fulfilled_amt_g
else 0
end
) material_charges_g
, sum( case
when cbtc.billing_category = 'L' then
ibsf.fulfilled_amt_g
else 0
end
) labor_charges_g
, sum( case
when cbtc.billing_category = 'E' then
ibsf.fulfilled_amt_g
else 0
end
) expense_charges_g
, sum( case
when cbtc.billing_category = 'M' then
ibsf.fulfilled_amt_g1
else 0
end
) material_charges_sg
, sum( case
when cbtc.billing_category = 'L' then
ibsf.fulfilled_amt_g1
else 0
end
) labor_charges_sg
, sum( case
when cbtc.billing_category = 'E' then
ibsf.fulfilled_amt_g1
else 0
end
) expense_charges_sg
, sysdate last_update_date
, g_user_id last_updated_by
, g_login_id last_update_login
, g_program_id program_id
, g_program_login_id program_login_id
, g_program_application_id program_application_id
, g_request_id request_id
from
isc_dr_repair_orders_f rof
, cs_estimate_details ced
, csd_repair_actuals cra
, csd_repair_actual_lines cral
, cs_txn_billing_types ctbt
, cs_billing_type_categories cbtc
, isc_book_sum2_f ibsf
where
rof.repair_line_id = cra.repair_line_id
and cra.repair_actual_id = cral.repair_actual_id
and ced.estimate_detail_id = cral.estimate_detail_id
and ced.order_line_id = ibsf.line_id
and ced.txn_billing_type_id = ctbt.txn_billing_type_id
and ctbt.billing_type = cbtc.billing_type
and rof.repair_mode = 'WIP'
and rof.status = 'C'
and rof.date_closed >= l_charges_last_run_date
and rof.ro_creation_date >= g_global_start_date
group by
rof.repair_line_id
) charges
on
( fact.repair_line_id = charges.repair_line_id )
when matched then
update
set fact.material_charges_g = charges.material_charges_g
, fact.labor_charges_g = charges.labor_charges_g
, fact.expense_charges_g = charges.expense_charges_g
, fact.material_charges_sg = charges.material_charges_sg
, fact.labor_charges_sg = charges.labor_charges_sg
, fact.expense_charges_sg = charges.expense_charges_sg
, fact.last_update_date = charges.last_update_date
, fact.last_updated_by = charges.last_updated_by
, fact.last_update_login = charges.last_update_login
, fact.program_id = charges.program_id
, fact.program_login_id = charges.program_login_id
, fact.program_application_id = charges.program_application_id
, fact.request_id = charges.request_id
when not matched then
insert
( fact.repair_line_id
, fact.material_charges_g
, fact.labor_charges_g
, fact.expense_charges_g
, fact.material_charges_sg
, fact.labor_charges_sg
, fact.expense_charges_sg
, fact.created_by
, fact.creation_date
, fact.last_update_date
, fact.last_updated_by
, fact.last_update_login
, fact.program_id
, fact.program_login_id
, fact.program_application_id
, fact.request_id
)
values
( charges.repair_line_id
, charges.material_charges_g
, charges.labor_charges_g
, charges.expense_charges_g
, charges.material_charges_sg
, charges.labor_charges_sg
, charges.expense_charges_sg
, g_user_id
, sysdate
, charges.last_update_date
, charges.last_updated_by
, charges.last_update_login
, charges.program_id
, charges.program_login_id
, charges.program_application_id
, charges.request_id
);
update isc_dr_inc
set
last_run_date = l_ro_last_run_date
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
where fact_name = 'ISC_DR_CHARGES_F';
bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );
( select
stg.repair_line_id repair_line_id
, nvl(sum( stg.material_cost_b * rates.g_conv_rate ), 0) material_cost_g
, nvl(sum( stg.labor_cost_b * rates.g_conv_rate ), 0) labor_cost_g
, nvl(sum( stg.expense_cost_b * rates.g_conv_rate ), 0) expense_cost_g
, nvl(sum( stg.material_cost_b * rates.sg_conv_rate ), 0) material_cost_sg
, nvl(sum( stg.labor_cost_b * rates.sg_conv_rate ), 0) labor_cost_sg
, nvl(sum( stg.expense_cost_b * rates.sg_conv_rate ), 0) expense_cost_sg
, sysdate last_update_date
, g_user_id last_updated_by
, g_login_id last_update_login
, g_program_id program_id
, g_program_login_id program_login_id
, g_program_application_id program_application_id
, g_request_id request_id
from
isc_dr_costs_stg stg
, isc_dr_costs_conv_tmp rates
where
stg.func_currency_code = rates.func_currency_code
and stg.date_closed = rates.date_closed
group by stg.repair_line_id
) costs
on
( fact.repair_line_id = costs.repair_line_id )
when matched then
update
set fact.material_cost_g = costs.material_cost_g
, fact.labor_cost_g = costs.labor_cost_g
, fact.expense_cost_g = costs.expense_cost_g
, fact.material_cost_sg = costs.material_cost_sg
, fact.labor_cost_sg = costs.labor_cost_sg
, fact.expense_cost_sg = costs.expense_cost_sg
, fact.last_update_date = costs.last_update_date
, fact.last_updated_by = costs.last_updated_by
, fact.last_update_login = costs.last_update_login
, fact.program_id = costs.program_id
, fact.program_login_id = costs.program_login_id
, fact.program_application_id = costs.program_application_id
, fact.request_id = costs.request_id
when not matched then
insert
( fact.repair_line_id
, fact.material_cost_g
, fact.labor_cost_g
, fact.expense_cost_g
, fact.material_cost_sg
, fact.labor_cost_sg
, fact.expense_cost_sg
, fact.created_by
, fact.creation_date
, fact.last_update_date
, fact.last_updated_by
, fact.last_update_login
, fact.program_id
, fact.program_login_id
, fact.program_application_id
, fact.request_id
)
values
( costs.repair_line_id
, costs.material_cost_g
, costs.labor_cost_g
, costs.expense_cost_g
, costs.material_cost_sg
, costs.labor_cost_sg
, costs.expense_cost_sg
, g_user_id
, sysdate
, costs.last_update_date
, costs.last_updated_by
, costs.last_update_login
, costs.program_id
, costs.program_login_id
, costs.program_application_id
, costs.request_id
);
update isc_dr_inc
set last_run_date = l_ro_last_run_date
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
where fact_name = 'ISC_DR_COSTS_F';
bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );