[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select last_run_date
into x_run_date
from isc_dr_inc
where fact_name = p_fact_name;
select distinct master_organization_id
from mtl_parameters;
select user_profile_option_name
into l_profile_name
from fnd_profile_options_vl
where profile_option_name = 'CS_INV_VALIDATION_ORG';
select master_organization_id
into l_master_org
from mtl_parameters
where organization_id = l_org;
delete
from isc_dr_inc
where fact_name = 'ISC_DR_REPAIR_ORDERS_F';
bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
insert /*+ append parallel(isc_dr_repair_orders_f) */
into isc_dr_repair_orders_f
( repair_line_id
, repair_number
, repair_organization_id
, master_organization_id
, inventory_item_id
, item_org_id
, repair_type_id
, incident_id
, incident_number
, customer_id
, ro_creation_date
, dbi_ro_creation_date
, repair_mode
, date_closed
, dbi_date_closed
, promise_date
, dbi_promise_date
, flow_status_id
, status
, serial_number
, quantity
, uom_code
, 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(inc, cr) parallel(inc) parallel(cr)*/
cr.repair_line_id
, cr.repair_number
, nvl(cr.owning_organization_id,-1)
, l_master_org master_organization_id
, cr.inventory_item_id
, cr.inventory_item_id || '-' || l_master_org
, cr.repair_type_id
, inc.incident_id
, inc.incident_number
, inc.customer_id
, trunc(cr.creation_date)
, case
when cr.creation_date < g_global_start_date then
g_global_start_date
else trunc(cr.creation_date)
end
, cr.repair_mode
, cr.date_closed
, trunc(cr.date_closed)
, trunc(cr.promise_date)
, case
when cr.promise_date < g_global_start_date then
g_global_start_date - 1
else trunc(cr.promise_date)
end
, cr.flow_status_id
, cr.status
, cr.serial_number
, cr.quantity
, cr.unit_of_measure
, l_user_id
, sysdate
, sysdate
, l_user_id
, l_login_id
, l_program_id
, l_program_login_id
, l_program_application_id
, l_request_id
from
csd_repairs cr
, cs_incidents_all_b inc
where
cr.incident_id = inc.incident_id
and ( cr.status in ('O','H','D') or
cr.Date_closed >= g_global_start_date
);
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_REPAIR_ORDERS_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_REPAIR_ORDERS_F'
, l_run_date
, l_user_id
, sysdate
, sysdate
, l_user_id
, l_login_id
, l_program_id
, l_program_login_id
, l_program_application_id
, l_request_id
);
bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
( select
cr.repair_line_id repair_line_id
, cr.repair_number repair_number
, nvl(cr.owning_organization_id, -1) organization_id
, cr.inventory_item_id inventory_item_id
, cr.repair_type_id repair_type_id
, inc.incident_id incident_id
, inc.incident_number incident_number
, inc.customer_id customer_id
, trunc(cr.creation_date) ro_creation_date
, case
when cr.creation_date < g_global_start_date then
g_global_start_date
else trunc(cr.creation_date)
end dbi_ro_creation_date
, cr.repair_mode repair_mode
, cr.date_closed date_closed
, trunc(cr.date_closed) dbi_date_closed
, trunc(cr.promise_date) promise_date
, case
when cr.promise_date < g_global_start_date then
g_global_start_date - 1
else
trunc(cr.promise_date)
end dbi_promise_date
, cr.flow_status_id flow_status_id
, cr.status status
, cr.serial_number serial_number
, cr.quantity quantity
, cr.unit_of_measure uom_code
from
csd_repairs cr
, cs_incidents_all_b inc
where
cr.incident_id = inc.incident_id
and cr.last_update_date > l_last_run_date
) oltp
on
( fact.repair_line_id = oltp.repair_line_id )
when matched then
update set
fact.repair_number = oltp.repair_number
, fact.repair_organization_id = oltp.organization_id
, fact.master_organization_id = l_master_org
, fact.inventory_item_id = oltp.inventory_item_id
, fact.item_org_id = oltp.inventory_item_id || '-' || l_master_org
, fact.repair_type_id = oltp.repair_type_id
, fact.incident_id = oltp.incident_id
, fact.incident_number = oltp.incident_number
, fact.customer_id = oltp.customer_id
, fact.ro_creation_date = oltp.ro_creation_date
, fact.dbi_ro_creation_date = oltp.dbi_ro_creation_date
, fact.date_closed = oltp.date_closed
, fact.dbi_date_closed = oltp.dbi_date_closed
, fact.repair_mode = oltp.repair_mode
, fact.promise_date = oltp.promise_date
, fact.dbi_promise_date = oltp.dbi_promise_date
, fact.flow_status_id = oltp.flow_status_id
, fact.status = oltp.status
, fact.serial_number = oltp.serial_number
, fact.quantity = oltp.quantity
, fact.uom_code = oltp.uom_code
, fact.last_update_date = sysdate
, fact.last_updated_by = l_user_id
, fact.last_update_login = l_login_id
, fact.program_id = l_program_id
, fact.program_login_id = l_program_login_id
, fact.program_application_id = l_program_application_id
, fact.request_id = l_request_id
when not matched then
insert
( repair_line_id
, repair_number
, repair_organization_id
, master_organization_id
, inventory_item_id
, item_org_id
, repair_type_id
, incident_id
, incident_number
, customer_id
, ro_creation_date
, dbi_ro_creation_date
, repair_mode
, date_closed
, dbi_date_closed
, promise_date
, dbi_promise_date
, flow_status_id
, status
, serial_number
, quantity
, uom_code
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( oltp.repair_line_id
, oltp.repair_number
, oltp.organization_id
, l_master_org
, oltp.inventory_item_id
, oltp.inventory_item_id || '-' || l_master_org
, oltp.repair_type_id
, oltp.incident_id
, oltp.incident_number
, oltp.customer_id
, oltp.ro_creation_date
, oltp.dbi_ro_creation_date
, oltp.repair_mode
, oltp.date_closed
, oltp.dbi_date_closed
, oltp.promise_date
, oltp.dbi_promise_date
, oltp.flow_status_id
, oltp.status
, oltp.serial_number
, oltp.quantity
, oltp.uom_code
, l_user_id
, sysdate
, sysdate
, l_user_id
, l_login_id
, l_program_id
, l_program_login_id
, l_program_application_id
, l_request_id
);
update isc_dr_inc
set
last_run_date = l_run_date
, last_update_date = sysdate
, last_updated_by = l_user_id
, last_update_login = l_login_id
, program_id = l_program_id
, program_login_id = l_program_login_id
, program_application_id = l_program_application_id
, request_id = l_request_id
where fact_name = 'ISC_DR_REPAIR_ORDERS_F';
bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );