[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(freeze_flag, 'N')
INTO l_freeze_flag
FROM csi_install_parameters
WHERE rownum = 1;
SELECT nvl(freeze_flag, 'N')
INTO l_freeze_flag
FROM csi_install_parameters
WHERE rownum = 1;
SELECT config_header_id,
config_rev_nbr,
configuration_id
INTO l_session_key.session_hdr_id,
l_session_key.session_rev_num,
l_session_key.session_item_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT config_valid_status
INTO x_config_valid_status
FROM csi_t_transaction_lines ctl,
csi_t_txn_line_details ctd
WHERE ctd.config_inst_hdr_id = p_instance_key.inst_hdr_id
AND ctd.config_inst_rev_num = p_instance_key.inst_rev_num
AND ctd.config_inst_item_id = p_instance_key.inst_item_id
AND ctl.transaction_line_id = ctd.transaction_line_id;
SELECT config_header_id,
config_rev_nbr,
configuration_id
INTO l_session_key.session_hdr_id,
l_session_key.session_rev_num,
l_session_key.session_item_id
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT master_organization_id
INTO l_master_org_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
select 1
into l_record_found
from csi_txn_errors cte,
mtl_material_transactions mmt
where mmt.transaction_id = cte.inv_material_transaction_id
and mmt.inventory_item_id = p_item_id
and cte.processed_flag in ('R','E','W')
and rownum < 2;
select 1
into l_record_found
from mtl_transactions_interface mti
where mti.inventory_item_id = p_item_id
and mti.organization_id = p_org_id
and rownum < 2;
select 1
into l_record_found
from mtl_material_transactions_temp mmtt
where mmtt.inventory_item_id = p_item_id
and mmtt.organization_id = p_org_id
and rownum < 2;
SELECT msg_id,
msg_code,
msg_status,
body_text,
creation_date,
description
FROM xnp_msgs
WHERE (msg_code like 'CSI%' OR msg_code like 'CSE%')
AND msg_status in ('READY','FAILED')
AND msg_creation_date > pc_freeze_date
-- AND nvl(msg_status, 'READY') <> 'PROCESSED' -- commented for Bug 3987286
AND recipient_name is null;
SELECT freeze_date
INTO l_freeze_date
FROM csi_install_parameters
WHERE rownum = 1;
select inventory_item_id
into l_item_id
from oe_order_lines_all
where line_id = l_source_id;
select inventory_item_id
into l_item_id
from mtl_material_transactions
where transaction_id = l_source_id;
select pla.Item_Id
into l_item_id
from rcv_transactions rt,
po_lines_all pla
where rt.transaction_id = l_source_id
and rt.po_Line_Id = pla.po_Line_Id;
select 1
into l_record_found
from csi_item_instances cii
where cii.inventory_item_id = p_item_id
and cii.active_end_date IS NULL
and rownum < 2;
SELECT 1
INTO l_record_found
FROM csi_item_instances
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND config_inst_hdr_id is NOT NULL
AND config_inst_rev_num is NOT NULL
AND config_inst_item_id is NOT NULL;
SELECT ool.config_header_id config_session_hdr_id,
ool.config_rev_nbr config_session_rev_num,
ool.configuration_id config_session_item_id
INTO l_config_keys.session_hdr_id,
l_config_keys.session_rev_num,
l_config_keys.session_item_id
FROM csi_txn_errors cte,
mtl_material_transactions mmt,
mtl_unit_transactions mut,
oe_order_lines_all ool
WHERE mmt.transaction_id = mut.transaction_id
AND mmt.transaction_action_id = 1
AND mmt.transaction_source_type_id = 2
AND mut.transaction_id = cte.inv_material_transaction_id
AND mut.inventory_item_id = p_inventory_item_id
AND mut.serial_number = p_serial_number
AND cte.processed_flag in ('R','E','W')
AND cte.transaction_type_id = 51
AND mmt.trx_source_line_id = ool.line_id;
SELECT msg_id,
msg_code,
msg_status,
body_text,
creation_date,
description
FROM xnp_msgs
WHERE msg_code = 'CSISOSHP'
AND msg_status in ('READY','FAILED')
AND msg_creation_date > pc_freeze_date
AND recipient_name is null;
SELECT ool.config_header_id config_session_hdr_id,
ool.config_rev_nbr config_session_rev_num,
ool.configuration_id config_session_item_id
FROM mtl_material_transactions mmt,
mtl_unit_transactions mut,
oe_order_lines_all ool
WHERE mmt.transaction_id = mut.transaction_id
AND mmt.transaction_id = pc_transaction_id
AND mmt.transaction_action_id = 1
AND mmt.transaction_source_type_id = 2
AND mut.inventory_item_id = pc_item_id
AND mut.serial_number = pc_serial_number
AND mmt.trx_source_line_id = ool.line_id;
SELECT freeze_date
INTO l_freeze_date
FROM csi_install_parameters
WHERE rownum = 1;
SELECT cil.active_end_date active_end_date
,cil.installation_date installation_date
,cil.txn_line_detail_id txn_line_detail_id
FROM csi_t_txn_line_details cil,
csi_mass_edit_entries_b cmee
WHERE cmee.entry_id = pc_batch_id
AND cmee.txn_line_id = cil.transaction_line_id
AND cil.instance_id IS NULL;
ELSE -- Mass Update
l_txn_oks_rec := p_txn_oks_rec;
debug(' Mass Update Batch ('||l_txn_oks_rec.batch_id||') .. Calling get_instances');
SELECT cil.instance_id instance_id
FROM csi_t_txn_line_details cil,
csi_mass_edit_entries_b cmee
WHERE cmee.entry_id = pc_batch_id
AND cmee.txn_line_id = cil.transaction_line_id
AND cil.instance_id IS NOT NULL;
SELECT cil.active_end_date active_end_date
,cil.installation_date installation_date
,cil.txn_line_detail_id txn_line_detail_id
FROM csi_t_txn_line_details cil,
csi_mass_edit_entries_b cmee
WHERE cmee.entry_id = pc_batch_id
AND cmee.txn_line_id = cil.transaction_line_id
AND cil.instance_id IS NULL;
SELECT install_date
FROM csi_item_instances
WHERE instance_id = pc_instance_id;
SELECT owner_party_id
FROM csi_item_instances
WHERE instance_id = pc_instance_id;
debug(' Start of get_instances ... Mass Update Batch Processing');
SELECT owner_party_id,install_date
INTO l_parent_owner_party_id,l_parent_install_date
FROM csi_item_instances
WHERE instance_id = inst_rec.instance_id;
SELECT owner_party_id
INTO l_owner_party_id
FROM csi_item_instances
WHERE instance_id = l_txn_trf_instances_tbl(trf);
l_txn_trf_instances_tbl.delete(trf);
l_txn_idc_instances_tbl.delete(id);
l_txn_trf_instances_tbl.delete(trf);
l_txn_idc_instances_tbl.delete(id);
SELECT transaction_id,
transaction_date
FROM mtl_material_transactions
WHERE transaction_date between pc_from_date and pc_to_date;
SELECT 1
FROM csi_transactions
WHERE inv_material_transaction_id = pc_transaction_id;
SELECT transaction_id,source_header_ref
FROM csi_transactions
WHERE source_line_ref_id = pc_line_id
AND transaction_type_id = pc_txn_id;
SELECT oeh.header_id,
oel.line_id,
oeh.order_number,
oel.line_number
FROM oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE oeh.header_id = oel.header_id
AND oel.line_id = pc_line_id;
l_mass_update_recs NUMBER;
SELECT txn_line_id
FROM csi_mass_edit_entries_b
WHERE status_code = p_status;
SELECT transaction_line_id
FROM csi_t_transaction_lines
WHERE processing_status = p_status
AND migrated_flag is NULL;
SELECT transaction_line_id,
source_transaction_id
FROM csi_t_transaction_lines
WHERE migrated_flag = 'Y';
SELECT oh.flow_status_code
INTO l_order_status
FROM oe_order_headers_all oh, oe_order_lines_all ol
WHERE ol.line_id = r_migrated_recs.source_transaction_id
AND ol.header_id = oh.header_id;
debug_con_log(' Before csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Detail: '||r_migrated_recs.transaction_line_id);
csi_t_txn_details_grp.delete_transaction_dtls (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_transaction_line_id => r_migrated_recs.transaction_line_id
,p_api_caller_identity => 'PURGE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
debug_con_log(' After csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Details');
debug_con_log(' Before csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Detail: '||r_migrated_recs.transaction_line_id);
csi_t_txn_details_grp.delete_transaction_dtls (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_transaction_line_id => r_migrated_recs.transaction_line_id
,p_api_caller_identity => 'PURGE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
debug_con_log(' After csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Details');
SELECT count(*)
INTO l_processed_recs
FROM csi_t_txn_line_details
WHERE transaction_line_id = r_processed_recs.transaction_line_id
AND source_transaction_flag = 'N';
debug_con_log(' Before csi_t_txn_details_grp.delete_transaction_dtls');
csi_t_txn_details_grp.delete_transaction_dtls (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_transaction_line_id => r_processed_recs.transaction_line_id
,p_api_caller_identity => 'PURGE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
debug_con_log(' After csi_t_txn_details_grp.delete_transaction_dtls');
debug_con_log(' Remove Mass Update Recs with no Txn Details ... ');
l_mass_update_recs := 0;
debug_con_log(' Check to see if the Txn Line in Mass Update has any records in csi_t_transaction_lines: '||r_mu.txn_line_id);
SELECT 1
INTO l_mass_update_recs
FROM csi_t_transaction_lines
WHERE transaction_line_id = r_mu.txn_line_id;
debug_con_log(' No Records exist so remove the Mass Update data from csi_mass_edit_entries_b: '||r_mu.txn_line_id);
DELETE from csi_mass_edit_entries_b
WHERE txn_line_id = r_mu.txn_line_id;
debug_con_log(' Finished Processing Mass Update Recs with no Txn Details ... ');