[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete
from isc_dr_inc
where fact_name = 'ISC_DR_MTTR_F';
bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
insert /*+ append parallel(isc_dr_mttr_f) */
into isc_dr_mttr_f
( repair_line_id
, repair_start_date
, repair_end_date
, time_to_repair
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select /*+ ordered use_hash(crt, bdr, cpt, ced, pdr, cpt1, ced1, ibs)
parallel(crt) parallel(cpt) parallel(ced) parallel(pdr)
parallel(cpt1) parallel(ced1) parallel(ibs) parallel(bdr) */
bdr.repair_line_id repair_line_id
, min(pdr.transaction_date) repair_start_date
, max(ibs.actual_shipment_date) repair_end_date
, max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
, 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_repair_types_b crt
, isc_dr_repair_orders_f bdr
, csd_product_transactions cpt
, cs_estimate_details ced
, poa_dbi_rtx_f pdr
, csd_product_transactions cpt1
, cs_estimate_details ced1
, isc_book_sum2_f ibs
where
bdr.repair_line_id = cpt.repair_line_id
and bdr.repair_type_id = crt.repair_type_id
and crt.repair_type_ref <> 'RF'
and cpt.action_type in ('RMA','WALK_IN_RECEIPT')
and cpt.action_code = 'CUST_PROD'
and cpt.estimate_detail_id = ced.estimate_detail_id
and ced.order_line_id = pdr.oe_order_line_id
and pdr.transaction_type = 'DELIVER'
and bdr.repair_line_id = cpt1.repair_line_id
and cpt1.action_type in ('SHIP','WALK_IN_ISSUE')
and cpt1.action_code = 'CUST_PROD'
and cpt1.estimate_detail_id = ced1.estimate_detail_id
and ced1.order_line_id = ibs.line_id
and ibs.actual_shipment_date is not null
and bdr.status = 'C'
and bdr.date_closed is not null
and bdr.ro_creation_date >= g_global_start_date
group by
bdr.repair_line_id;
bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
insert /*+ append parallel(isc_dr_mttr_f) */
into isc_dr_mttr_f
( repair_line_id
, repair_start_date
, repair_end_date
, time_to_repair
, 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(crt, bdr) parallel(bdr) */
bdr.repair_line_id repair_line_id
, min(pdr.transaction_date) repair_start_date
, max(ibs.actual_shipment_date) repair_end_date
, max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
, 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
isc_dr_repair_orders_f bdr
, csd_repair_types_b crt
, csd_product_transactions cpt
, csd_product_transactions cpt1
, poa_dbi_rtx_f pdr
, isc_book_sum2_f ibs
where
bdr.repair_line_id = cpt.repair_line_id
and bdr.serial_number is null
and bdr.repair_type_id = crt.repair_type_id
and crt.repair_type_ref = 'RF'
and cpt.action_type = 'MOVE_IN'
-- and cpt.action_code = 'DEFECTIVES'
and cpt.req_line_id = pdr.requisition_line_id
and pdr.transaction_type = 'DELIVER'
and bdr.repair_line_id = cpt1.repair_line_id
and cpt1.action_type = 'MOVE_OUT'
-- and cpt1.action_code = 'USABLES'
and cpt1.order_line_id = ibs.line_id
and ibs.actual_shipment_date is not null
and bdr.status = 'C'
and bdr.date_closed is not null
and bdr.ro_creation_date >= g_global_start_date
group by
bdr.repair_line_id;
bis_collection_utilities.log( 'Inserted ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
insert /*+ append parallel(isc_dr_mttr_f) */
into isc_dr_mttr_f
( repair_line_id
, repair_start_date
, repair_end_date
, time_to_repair
, 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(crt, bdr) parallel(bdr) */
bdr.repair_line_id repair_line_id
, pdr.transaction_date repair_start_date
, ibs.actual_shipment_date repair_end_date
, (ibs.actual_shipment_date - pdr.transaction_date) time_to_repair
, 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
isc_dr_repair_orders_f bdr
, csd_repair_types_b crt
, csd_product_transactions cpt
, csd_product_transactions cpt1
, poa_dbi_rtx_f pdr
, mtl_unit_transactions mut
, isc_book_sum2_f ibs
where
bdr.repair_line_id = cpt.repair_line_id
and bdr.serial_number is not null
and bdr.repair_type_id = crt.repair_type_id
and crt.repair_type_ref = 'RF'
and cpt.action_type = 'MOVE_IN'
-- and cpt.action_code = 'DEFECTIVES'
and cpt.req_line_id = pdr.requisition_line_id
and pdr.transaction_type = 'DELIVER'
and pdr.inv_transaction_id = mut.transaction_id
and cpt.source_serial_number = mut.serial_number
and bdr.repair_line_id = cpt1.repair_line_id
and cpt1.action_type = 'MOVE_OUT'
-- and cpt1.action_code = 'USABLES'
and cpt1.order_line_id = ibs.line_id
and ibs.actual_shipment_date is not null
and cpt1.prod_txn_status = 'SHIPPED'
and bdr.status = 'C'
and bdr.date_closed is not null
and bdr.ro_creation_date >= g_global_start_date;
bis_collection_utilities.log( 'Inserted ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_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_MTTR_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
bdr.repair_line_id repair_line_id
, min(pdr.transaction_date) repair_start_date
, max(ibs.actual_shipment_date) repair_end_date
, max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
from
isc_dr_repair_orders_f bdr
, csd_repair_types_b crt
, csd_product_transactions cpt
, csd_product_transactions cpt1
, cs_estimate_details ced
, cs_estimate_details ced1
, poa_dbi_rtx_f pdr
, isc_book_sum2_f ibs
where
bdr.repair_line_id = cpt.repair_line_id
and bdr.repair_type_id = crt.repair_type_id
and crt.repair_type_ref <> 'RF'
and cpt.action_type in ('RMA','WALK_IN_RECEIPT')
and cpt.action_code = 'CUST_PROD'
and cpt.estimate_detail_id = ced.estimate_detail_id
and ced.order_line_id = pdr.oe_order_line_id
and pdr.transaction_type = 'DELIVER'
and bdr.repair_line_id = cpt1.repair_line_id
and cpt1.action_type in ('SHIP','WALK_IN_ISSUE')
and cpt1.action_code = 'CUST_PROD'
and cpt1.estimate_detail_id = ced1.estimate_detail_id
and ced1.order_line_id = ibs.line_id
and ibs.actual_shipment_date is not null
and bdr.status = 'C'
and bdr.ro_creation_date >= g_global_start_date
and bdr.date_closed > l_run_date
group by
bdr.repair_line_id
) oltp
on
( fact.repair_line_id = oltp.repair_line_id )
when matched then
update
set fact.repair_start_date = oltp.repair_start_date
, fact.repair_end_date = oltp.repair_end_date
, fact.time_to_repair = oltp.time_to_repair
, 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_start_date
, repair_end_date
, time_to_repair
, 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_start_date
, oltp.repair_end_date
, oltp.time_to_repair
, 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
);
( select
bdr.repair_line_id repair_line_id
, min(pdr.transaction_date) repair_start_date
, max(ibs.actual_shipment_date) repair_end_date
, max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
from
isc_dr_repair_orders_f bdr
, csd_repair_types_b crt
, csd_product_transactions cpt
, csd_product_transactions cpt1
, poa_dbi_rtx_f pdr
, isc_book_sum2_f ibs
where
bdr.repair_line_id = cpt.repair_line_id
and bdr.serial_number is null
and bdr.repair_type_id = crt.repair_type_id
and crt.repair_type_ref = 'RF'
and cpt.action_type = 'MOVE_IN'
-- and cpt.action_code = 'DEFECTIVES'
and cpt.req_line_id = pdr.requisition_line_id
and pdr.transaction_type = 'DELIVER'
and bdr.repair_line_id = cpt1.repair_line_id
and cpt1.action_type = 'MOVE_OUT'
-- and cpt1.action_code = 'USABLES'
and cpt1.order_line_id = ibs.line_id
and ibs.actual_shipment_date is not null
and bdr.status = 'C'
and bdr.ro_creation_date >= g_global_start_date
and bdr.date_closed > l_run_date
group by
bdr.repair_line_id
) oltp
on
( fact.repair_line_id = oltp.repair_line_id )
when matched then
update
set fact.repair_start_date = oltp.repair_start_date
, fact.repair_end_date = oltp.repair_end_date
, fact.time_to_repair = oltp.time_to_repair
, 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_start_date
, repair_end_date
, time_to_repair
, 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_start_date
, oltp.repair_end_date
, oltp.time_to_repair
, 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
);
( select
bdr.repair_line_id repair_line_id
, pdr.transaction_date repair_start_date
, ibs.actual_shipment_date repair_end_date
, ibs.actual_shipment_date - pdr.transaction_date time_to_repair
from
isc_dr_repair_orders_f bdr
, csd_repair_types_b crt
, csd_product_transactions cpt
, csd_product_transactions cpt1
, poa_dbi_rtx_f pdr
, mtl_unit_transactions mut
, isc_book_sum2_f ibs
where
bdr.repair_line_id = cpt.repair_line_id
and bdr.serial_number is not null
and bdr.repair_type_id = crt.repair_type_id
and crt.repair_type_ref = 'RF'
and cpt.action_type = 'MOVE_IN'
-- and cpt.action_code = 'DEFECTIVES'
and cpt.req_line_id = pdr.requisition_line_id
and pdr.transaction_type = 'DELIVER'
and pdr.inv_transaction_id = mut.transaction_id
and cpt.source_serial_number = mut.serial_number
and bdr.repair_line_id = cpt1.repair_line_id
and cpt1.action_type = 'MOVE_OUT'
-- and cpt1.action_code = 'USABLES'
and cpt1.order_line_id = ibs.line_id
and ibs.actual_shipment_date is not null
and cpt1.prod_txn_status = 'SHIPPED'
and bdr.status = 'C'
and bdr.ro_creation_date >= g_global_start_date
and bdr.date_closed > l_run_date
) oltp
on
( fact.repair_line_id = oltp.repair_line_id )
when matched then
update
set fact.repair_start_date = oltp.repair_start_date
, fact.repair_end_date = oltp.repair_end_date
, fact.time_to_repair = oltp.time_to_repair
, 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_start_date
, repair_end_date
, time_to_repair
, 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_start_date
, oltp.repair_end_date
, oltp.time_to_repair
, 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_last_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_MTTR_F' ;
bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );