[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct
mp.organization_code
, decode( least( r.conversion_rate1, r.conversion_rate2 )
, l_euro_missing_at_start, l_euro_start_date
, r.transaction_date) transaction_date
, r.base_currency_code
, nvl(r.conversion_rate1, -999) primary_rate
, nvl(r.conversion_rate2, -999) secondary_rate
from
isc_maint_wo_cst_conv_rates r
, mtl_parameters mp
, ( select /*+ index_ffs(isc_maint_wo_cst_sum_stg) */ distinct
organization_id
, completion_date
from isc_maint_wo_cst_sum_stg
) s
where ( nvl(r.conversion_rate1, -999) < 0 or
nvl(r.conversion_rate2, -999) < 0 )
and mp.organization_id = s.organization_id
and r.transaction_date (+) = s.completion_date
and r.organization_id (+) = s.organization_id;
insert /*+ append */
into isc_maint_wo_cst_conv_rates
( organization_id
, transaction_date
, base_currency_code
, conversion_rate1
, conversion_rate2
, creation_date
, last_update_date
, created_by
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select
s.organization_id
, s.completion_date
, c.currency_code
, decode( s.completed_flag -- only attempt conversion for real completion dates
, 'Y', fii_currency.get_global_rate_primary
( c.currency_code
, s.completion_date )
, 0
) conversion_rate1
, decode( l_global_currency_code2
, null, 0 -- only attempt conversion if secondary currency defined
, decode( s.completed_flag -- only attempt conversion for real completion dates
, 'Y', fii_currency.get_global_rate_secondary
( c.currency_code
, s.completion_date )
, 0
)
) conversion_rate2
, sysdate
, sysdate
, g_user_id
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
( select /*+ index_ffs(isc_maint_wo_cst_sum_stg)
parallel_index(isc_maint_wo_cst_sum_stg) */ distinct
organization_id
, completion_date
, completed_flag
from
isc_maint_wo_cst_sum_stg
) s
, ( select distinct
hoi.organization_id
, gsob.currency_code
from
hr_organization_information hoi
, gl_sets_of_books gsob
where hoi.org_information_context = 'Accounting Information'
and hoi.org_information1 = to_char(gsob.set_of_books_id)
) c
where c.organization_id = s.organization_id;
bis_collection_utilities.log( l_rowcount || ' rows inserted into currency conversion table', 2 );
insert /*+ append parallel(s) */
into isc_maint_wo_cst_sum_stg s
( organization_id
, work_order_id
, department_id
, maint_cost_category
, estimated_flag
, completion_date
, completed_flag
, actual_mat_cost_b
, actual_lab_cost_b
, actual_eqp_cost_b
, estimated_mat_cost_b
, estimated_lab_cost_b
, estimated_eqp_cost_b
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select /*+ parallel(wo) parallel(pb) */
wo.organization_id
, wo.work_order_id
, nvl(pb.operations_dept_id,-1) -- nvl'd so merge stmt join is successful
, nvl(pb.maint_cost_category,-1)-- nvl'd so merge stmt join is successful
, decode( sum( sum( abs(pb.system_estimated_mat_cost)
+ abs(pb.system_estimated_lab_cost)
+ abs(pb.system_estimated_eqp_cost)
)
) over( partition by wo.organization_id, wo.work_order_id )
, 0, 'N'
, 'Y') estimated_flag
, nvl(wo.completion_date,wo.closed_date)
, 'Y'
, sum(pb.actual_mat_cost)
, sum(pb.actual_lab_cost)
, sum(pb.actual_eqp_cost)
, sum(pb.system_estimated_mat_cost)
, sum(pb.system_estimated_lab_cost)
, sum(pb.system_estimated_eqp_cost)
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
isc_maint_work_orders_f wo
, wip_eam_period_balances pb
where
wo.work_order_id = pb.wip_entity_id
and wo.organization_id = pb.organization_id
and nvl(wo.completion_date, wo.closed_date) >= g_global_start_date
and wo.status_type in (4, 5, 12)
group by
wo.organization_id
, wo.work_order_id
, nvl(pb.operations_dept_id,-1)
, nvl(pb.maint_cost_category,-1)
, nvl(wo.completion_date,wo.closed_date);
bis_collection_utilities.log( l_rowcount || ' rows inserted into staging table', 1 );
insert /*+ append parallel(f) */
into isc_maint_wo_cst_sum_f f
( organization_id
, work_order_id
, department_id
, maint_cost_category
, estimated_flag
, completion_date
, conversion_rate1
, conversion_rate2
, actual_mat_cost_b
, actual_lab_cost_b
, actual_eqp_cost_b
, estimated_mat_cost_b
, estimated_lab_cost_b
, estimated_eqp_cost_b
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select /*+ parallel(s) parallel(c) */
s.organization_id
, s.work_order_id
, s.department_id
, s.maint_cost_category
, s.estimated_flag
, s.completion_date
, c.conversion_rate1
, decode( c.conversion_rate2
, 0, null
, c.conversion_rate2 )
, s.actual_mat_cost_b
, s.actual_lab_cost_b
, s.actual_eqp_cost_b
, s.estimated_mat_cost_b
, s.estimated_lab_cost_b
, s.estimated_eqp_cost_b
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
isc_maint_wo_cst_sum_stg s
, isc_maint_wo_cst_conv_rates c
where
c.organization_id = s.organization_id
and c.transaction_date = s.completion_date;
bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
insert /*+ append */
into isc_maint_wo_cst_sum_stg
( organization_id
, work_order_id
, department_id
, maint_cost_category
, estimated_flag
, completion_date
, completed_flag
, actual_mat_cost_b
, actual_lab_cost_b
, actual_eqp_cost_b
, estimated_mat_cost_b
, estimated_lab_cost_b
, estimated_eqp_cost_b
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select
organization_id
, work_order_id
, nvl(operations_dept_id,-1) -- nvl'd so merge stmt join is successful
, nvl(maint_cost_category,-1)-- nvl'd so merge stmt join is successful
, decode( sum( sum( abs(system_estimated_mat_cost)
+ abs(system_estimated_lab_cost)
+ abs(system_estimated_eqp_cost)
)
) over( partition by organization_id, work_order_id )
, 0, 'N'
, 'Y') estimated_flag
, decode( status_type
, 4, completion_date -- complete
, 5, completion_date -- complete - no charges
, 12, nvl(completion_date,closed_date) -- closed
, g_max_date )
, decode( status_type
, 4, 'Y'
, 5, 'Y'
, 12, 'Y'
, 'N' )
, sum(actual_mat_cost)
, sum(actual_lab_cost)
, sum(actual_eqp_cost)
, sum(system_estimated_mat_cost)
, sum(system_estimated_lab_cost)
, sum(system_estimated_eqp_cost)
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
( select
wo.organization_id
, wo.work_order_id
, wo.status_type
, wo.completion_date
, wo.closed_date
, pb.operations_dept_id
, pb.maint_cost_category
, pb.actual_mat_cost
, pb.actual_lab_cost
, pb.actual_eqp_cost
, pb.system_estimated_mat_cost
, pb.system_estimated_lab_cost
, pb.system_estimated_eqp_cost
from
wip_eam_period_balances pb
, (
-- identifies all completed/closed work orders that
-- have at least on cost balance row updated since
-- last collection
select
wo.organization_id
, wo.work_order_id
, wo.status_type
, wo.completion_date
, wo.closed_date
from
isc_maint_work_orders_f wo
, wip_eam_period_balances pb
where
pb.last_update_date >= l_collect_from_date
and wo.work_order_id = pb.wip_entity_id
and wo.organization_id = pb.organization_id
and wo.status_type in (4, 5, 12)
--
union
--
-- identifies all work orders that have been updated since last
-- collection whos completion_date differs from the previously
-- recorded completion_date (this allows us to verify the
-- currency conversion for all changed completion_dates also
-- allows us to clear completion_date for "un-completed" work
-- orders)
-- and identifies all completed or closed work orders that have
-- been re-estimated since last collection (this allows us to
-- catch work orders that have had a resource estimated deleted
-- that resulted in a row being deleted from period balances)
select
wo.organization_id
, wo.work_order_id
, wo.status_type
, wo.completion_date
, wo.closed_date
from
isc_maint_work_orders_f wo
, wip_eam_period_balances pb
, isc_maint_wo_cst_sum_f f
where
wo.last_update_date >= l_collect_from_date
and ( nvl(nvl(wo.completion_date,closed_date),g_max_date) <> nvl(f.completion_date,g_max_date) or
-- this is necessary to pick up cost for any work order where the estimated
-- resource was deleted this resulted in a
( wo.last_estimation_date >= l_collect_from_date and
nvl(wo.completion_date,closed_date) is not null )
)
and wo.work_order_id = pb.wip_entity_id
and wo.organization_id = pb.organization_id
-- need to outer join here to ensure we pick up completed work order that
-- may not already exist in the wo cst base summary
and wo.work_order_id = f.work_order_id(+)
and wo.organization_id = f.organization_id(+)
) wo
where
wo.work_order_id = pb.wip_entity_id
and wo.organization_id = pb.organization_id
--
union all
--
-- returns an all "zero" cost row from isc_maint_wo_cst_sum_f
-- for all completed/closed work orders have been updated since
-- last collection and have been re-estimated since last collection
-- (this allows us to catch work orders that have had a resource
-- estimated deleted that resulted in a row being deleted from
-- period balances and zero out the isc_maint_wo_cst_sum_f row)
select
wo.organization_id
, wo.work_order_id
, wo.status_type
, wo.completion_date
, wo.closed_date
, f.department_id
, f.maint_cost_category
, 0 actual_mat_cost
, 0 actual_lab_cost
, 0 actual_eqp_cost
, 0 system_estimated_mat_cost
, 0 system_estimated_lab_cost
, 0 system_estimated_eqp_cost
from
isc_maint_work_orders_f wo
, isc_maint_wo_cst_sum_f f
where
wo.status_type in (4, 5, 12)
and wo.last_update_date >= l_collect_from_date
and wo.last_estimation_date >= l_collect_from_date
and wo.work_order_id = f.work_order_id
and wo.organization_id = f.organization_id
)
group by
organization_id
, work_order_id
, nvl(operations_dept_id,-1) -- nvl'd so merge stmt join is successful
, nvl(maint_cost_category,-1)-- nvl'd so merge stmt join is successful
, decode( status_type
, 4, completion_date
, 5, completion_date
, 12, nvl(completion_date,closed_date)
, g_max_date )
, decode( status_type
, 4, 'Y'
, 5, 'Y'
, 12, 'Y'
, 'N' );
bis_collection_utilities.log( l_rowcount || ' rows inserted into staging table', 1 );
( select
s.organization_id
, s.work_order_id
, s.department_id
, s.maint_cost_category
, s.estimated_flag
, decode( s.completed_flag
, 'Y', s.completion_date
, null ) completion_date
, decode( s.completed_flag
, 'Y', c.conversion_rate1
, null ) conversion_rate1
, decode( s.completed_flag
, 'Y', decode( c.conversion_rate2
, 0, null
, c.conversion_rate2
)
, null ) conversion_rate2
, decode( s.completed_flag
, 'Y', s.actual_mat_cost_b
, null ) actual_mat_cost_b
, decode( s.completed_flag
, 'Y', s.actual_lab_cost_b
, null ) actual_lab_cost_b
, decode( s.completed_flag
, 'Y', s.actual_eqp_cost_b
, null ) actual_eqp_cost_b
, decode( s.completed_flag
, 'Y', s.estimated_mat_cost_b
, null ) estimated_mat_cost_b
, decode( s.completed_flag
, 'Y', s.estimated_lab_cost_b
, null ) estimated_lab_cost_b
, decode( s.completed_flag
, 'Y', s.estimated_eqp_cost_b
, null ) estimated_eqp_cost_b
, sysdate creation_date
, g_user_id created_by
, 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_maint_wo_cst_sum_stg s
, isc_maint_wo_cst_conv_rates c
where
c.organization_id = s.organization_id
and c.transaction_date = s.completion_date
) s
on
( f.organization_id = s.organization_id
and f.work_order_id = s.work_order_id
and f.department_id = s.department_id
and f.maint_cost_category = s.maint_cost_category
)
when matched then update
set
f.estimated_flag = s.estimated_flag
, f.completion_date = s.completion_date
, f.conversion_rate1 = s.conversion_rate1
, f.conversion_rate2 = s.conversion_rate2
, f.actual_mat_cost_b = s.actual_mat_cost_b
, f.actual_lab_cost_b = s.actual_lab_cost_b
, f.actual_eqp_cost_b = s.actual_eqp_cost_b
, f.estimated_mat_cost_b = s.estimated_mat_cost_b
, f.estimated_lab_cost_b = s.estimated_lab_cost_b
, f.estimated_eqp_cost_b = s.estimated_eqp_cost_b
, f.last_update_date = s.last_update_date
, f.last_updated_by = s.last_updated_by
, f.last_update_login = s.last_update_login
, f.program_id = s.program_id
, f.program_login_id = s.program_login_id
, f.program_application_id = s.program_application_id
, f.request_id = s.request_id
when not matched then insert
( organization_id
, work_order_id
, department_id
, maint_cost_category
, estimated_flag
, completion_date
, conversion_rate1
, conversion_rate2
, actual_mat_cost_b
, actual_lab_cost_b
, actual_eqp_cost_b
, estimated_mat_cost_b
, estimated_lab_cost_b
, estimated_eqp_cost_b
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( s.organization_id
, s.work_order_id
, s.department_id
, s.maint_cost_category
, s.estimated_flag
, s.completion_date
, s.conversion_rate1
, s.conversion_rate2
, s.actual_mat_cost_b
, s.actual_lab_cost_b
, s.actual_eqp_cost_b
, s.estimated_mat_cost_b
, s.estimated_lab_cost_b
, s.estimated_eqp_cost_b
, s.creation_date
, s.created_by
, s.last_update_date
, s.last_updated_by
, s.last_update_login
, s.program_id
, s.program_login_id
, s.program_application_id
, s.request_id
);