[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_profile.value('csi_debug_level')
INTO l_debug_level
FROM sys.dual;
SELECT fnd_profile.value('csi_logfile_path')
INTO l_debug_path
FROM sys.dual;
SELECT value
INTO l_utl_file_dir
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT substr(l_utl_file_dir, 1,
decode(instr(l_utl_file_dir, ',', 1), 0,
length(l_utl_file_dir), instr(l_utl_file_dir, ',', 1)-1 ))
INTO l_debug_path
FROM sys.dual;
SELECT inventory_item_id,
organization_id,
reservable_type,
nvl(comms_nl_trackable_flag,'N'),
serial_number_control_code,
lot_control_code,
shippable_item_flag,
bom_item_type,
concatenated_segments,
primary_uom_code,
location_control_code,
revision_qty_control_code,
base_item_id,
pick_components_flag,
returnable_flag,
wip_supply_type,
planning_make_buy_code,
inventory_item_flag,
mtl_transactions_enabled_flag
INTO l_item_rec.item_id,
l_item_rec.organization_id,
l_item_rec.reservable_type,
l_item_rec.ib_trackable_flag,
l_item_rec.serial_code,
l_item_rec.lot_code,
l_item_rec.shippable_flag,
l_item_rec.bom_item_type,
l_item_rec.item,
l_item_rec.primary_uom_code,
l_item_rec.locator_code,
l_item_rec.revision_code,
l_item_rec.base_item_id,
l_item_rec.pick_flag,
l_item_rec.returnable_flag,
l_item_rec.wip_supply_type,
l_item_rec.make_buy_code,
l_item_rec.inventory_flag,
l_item_rec.inv_transactable_flag
FROM mtl_system_items_vl
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT decode(p_order_line.ib_trackable_flag, 'Y', '*', ' ')
INTO l_ib_ind
FROM sys.dual;
SELECT transaction_line_id,
txn_line_detail_id,
inventory_item_id,
quantity,
serial_number,
lot_number,
instance_id,
changed_instance_id,
location_type_code,
location_id,
processing_status,
source_transaction_flag,
config_inst_baseline_rev_num,
config_inst_hdr_id,
config_inst_item_id,
config_inst_rev_num
FROM csi_t_txn_line_details
WHERE transaction_line_id = p_transaction_line_id
ORDER BY source_transaction_flag desc, txn_line_detail_id asc;
SELECT txn_party_detail_id,
party_source_table,
party_source_id,
relationship_type_code,
contact_flag,
contact_party_id
FROM csi_t_party_details
WHERE txn_line_detail_id = p_txn_line_detail_id;
SELECT txn_account_detail_id,
ip_account_id,
relationship_type_code,
account_id,
active_start_date,
ship_to_address_id,
bill_to_address_id
FROM csi_t_party_accounts
WHERE txn_party_detail_id = p_txn_party_detail_id;
SELECT txn_relationship_id,
subject_type,
subject_id,
relationship_type_code,
object_type,
object_id,
position_reference,
display_order,
mandatory_flag,
active_end_date,
csi_inst_relationship_id,
migrated_flag,
sub_config_inst_hdr_id,
sub_config_inst_rev_num,
sub_config_inst_item_id,
obj_config_inst_hdr_id,
obj_config_inst_rev_num,
obj_config_inst_item_id
FROM csi_t_ii_relationships
WHERE transaction_line_id = p_txn_line_id;
SELECT txn_relationship_id,
subject_type,
subject_id,
relationship_type_code,
object_type,
object_id,
position_reference,
display_order,
mandatory_flag,
active_end_date,
csi_inst_relationship_id,
migrated_flag,
sub_config_inst_hdr_id,
sub_config_inst_rev_num,
sub_config_inst_item_id,
obj_config_inst_hdr_id,
obj_config_inst_rev_num,
obj_config_inst_item_id
FROM csi_t_ii_relationships
WHERE (sub_config_inst_hdr_id = p_inst_hdr_id
AND
sub_config_inst_item_id = p_inst_item_id
AND
sub_config_inst_rev_num = p_inst_rev_num)
OR (obj_config_inst_hdr_id = p_inst_hdr_id
AND
obj_config_inst_item_id = p_inst_item_id
AND
obj_config_inst_rev_num = p_inst_rev_num);
SELECT *
INTO l_tl_rec
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'CONFIGURATOR'
AND config_session_hdr_id = l_order_lines(l_ind).config_header_id
AND config_session_rev_num = l_order_lines(l_ind).config_rev_nbr
AND config_session_item_id = l_order_lines(l_ind).configuration_id;
SELECT *
INTO l_tl_rec
FROM csi_t_transaction_lines
WHERE source_transaction_table = p_source_table
AND source_transaction_id = l_order_lines(l_ind).line_id;
SELECT transaction_id,
transaction_date
INTO px_mmt_rec.csi_txn_id,
px_mmt_rec.csi_txn_date
FROM csi_transactions
WHERE inv_material_transaction_id = px_mmt_rec.mtl_txn_id
AND rownum = 1;
SELECT transaction_error_id,
error_text
INTO px_mmt_rec.error_id,
px_mmt_rec.error_text
FROM csi_txn_errors
WHERE inv_material_transaction_id = px_mmt_rec.mtl_txn_id
AND processed_flag in ('E', 'R');
SELECT msg_id,
msg_code,
msg_status
INTO px_mmt_rec.message_id,
px_mmt_rec.message_code,
px_mmt_rec.message_status
FROM xnp_msgs
WHERE dbms_lob.instr(body_text, 'MTL_TRANSACTION_ID') > 0
AND dbms_lob.instr(body_text, px_mmt_rec.mtl_txn_id) > 0
AND rownum = 1;
SELECT mmt.transaction_id,
mmt.transaction_date,
mmt.transaction_quantity,
mmt.transaction_uom,
mmt.primary_quantity,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_type_id,
mmt.inventory_item_id,
mmt.organization_id ,
mmt.transaction_source_id,
mmt.trx_source_line_id,
mmt.transfer_transaction_id,
mtt.transaction_type_name,
mtt.type_class,
mtt.user_defined_flag
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mmt.transaction_id = p_txn_id
AND mtt.transaction_type_id = mmt.transaction_type_id;
SELECT mut.serial_number serial_number,
mut.inventory_item_id item_id,
mut.organization_id organization_id,
to_char(null) lot_number
FROM mtl_unit_transactions mut
WHERE mut.transaction_id = p_txn_id
UNION
SELECT mut.serial_number serial_number,
mut.inventory_item_id item_id,
mut.organization_id organization_id,
mtln.lot_number lot_number
FROM mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut
WHERE mtln.transaction_id = p_txn_id
AND mut.transaction_id = mtln.serial_transaction_id
ORDER BY 1;
SELECT instance_id,
location_type_code,
instance_usage_code
INTO l_mut_tbl(l_mut_ind).instance_id ,
l_mut_tbl(l_mut_ind).location_type_code,
l_mut_tbl(l_mut_ind).instance_usage_code
FROM csi_item_instances
WHERE inventory_item_id = srl_rec.item_id
AND serial_number = srl_rec.serial_number;
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mmt.transaction_id = mut.transaction_id
UNION ALL
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mtln.organization_id = mut.organization_id
AND mtln.transaction_date = mut.transaction_date
AND mtln.serial_transaction_id = mut.transaction_id
AND mmt.transaction_id = mtln.transaction_id
ORDER BY 1 desc, 2 desc;
SELECT wip_entity_id,
entity_type,
organization_id
INTO l_job_rec.wip_entity_id,
l_job_rec.wip_entity_type,
l_job_rec.organization_id
FROM wip_entities
WHERE wip_entity_name = p_wip_entity_name
AND organization_id = p_organization_id;
SELECT primary_item_id,
quantity_completed,
quantity_completed,
status
INTO l_job_rec.primary_item_id,
l_job_rec.start_qty, -- wo less case compl qty is job qty
l_job_rec.qty_completed,
l_job_rec.wip_job_status
FROM wip_flow_schedules
WHERE wip_entity_id = l_job_rec.wip_entity_id
AND organization_id = l_job_rec.organization_id;
SELECT primary_item_id,
start_quantity,
quantity_completed,
job_type,
status_type,
nvl(maintenance_object_source, 0),
source_code,
source_line_id
INTO l_job_rec.primary_item_id,
l_job_rec.start_qty,
l_job_rec.qty_completed,
l_job_rec.wip_entity_type,
l_job_rec.wip_job_status,
l_job_rec.maint_obj_source,
l_job_rec.source_code,
l_job_rec.source_line_id
FROM wip_discrete_jobs
WHERE wip_entity_id = l_job_rec.wip_entity_id
AND organization_id = l_job_rec.organization_id;
SELECT segment1,
inventory_item_id,
organization_id,
operation_seq_num,
component_sequence_id,
quantity_per_assembly,
required_quantity,
quantity_issued,
wip_supply_type,
supply_subinventory
FROM wip_requirement_operations
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
ORDER BY operation_seq_num, component_sequence_id;
SELECT decode(l_item_rec.ib_trackable_flag, 'Y', '*', ' ')
INTO l_ib_ind
FROM sys.dual;
SELECT mmt.transaction_id
FROM mtl_material_transactions mmt,
mtl_system_items msi
WHERE mmt.transaction_source_type_id = 5
AND mmt.organization_id = p_organization_id
AND mmt.transaction_source_id = p_wip_entity_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
ORDER BY transaction_date desc;
SELECT msn.serial_number,
msn.inventory_item_id
FROM mtl_object_genealogy mog,
mtl_serial_numbers msn
WHERE mog.object_type = 2 -- serial genealogy
AND mog.object_id = p_gen_object_id
AND mog.parent_object_type = 2 -- serial genealogy
AND msn.gen_object_id = mog.parent_object_id
AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
AND nvl(mog.end_date_active, sysdate+1);
SELECT current_status,
current_organization_id,
gen_object_id
INTO l_current_status,
l_current_org_id,
l_gen_object_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id;
SELECT instance_id,
instance_number
INTO l_instance_id,
l_instance_number
FROM csi_item_instances
WHERE inventory_item_id = p_item_id
AND serial_number = p_serial_number;
SELECT transaction_id,
transaction_type_id,
transaction_date,
source_header_ref_id,
source_header_ref,
source_line_ref
FROM csi_transactions
WHERE source_line_ref_id = p_order_line_id;
SELECT transaction_error_id,
source_type,
source_header_ref,
error_text
FROM csi_txn_errors
WHERE source_id = p_order_line_id
AND processed_flag in ('E', 'R');
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_source_type_id = 2
AND transaction_action_id = 1
AND inventory_item_id = p_item_id
AND trx_source_line_id = p_line_id
ORDER BY transaction_id desc;
SELECT 'Y'
INTO l_sub_model_flag
FROM sys.dual
WHERE exists (
SELECT 'X'
FROM bom_cto_order_lines
WHERE ato_line_id = p_ato_line_id
AND parent_ato_line_id = p_line_id);
SELECT wip_entity_id,
organization_id,
request_id
FROM wip_discrete_jobs
WHERE primary_item_id = p_item_id
AND organization_id = p_organization_id
AND source_line_id = p_line_id
AND status_type <> 7 -- excluding the cancelled wip jobs
ORDER by wip_entity_id desc;
SELECT wip_entity_id,
organization_id,
request_id
FROM wip_discrete_jobs
WHERE primary_item_id = p_item_id
AND organization_id = p_organization_id
AND request_id = p_request_id
AND status_type <> 7 -- excluding the cancelled wip jobs
ORDER by wip_entity_id desc;
SELECT line_id,
inventory_item_id
INTO l_ato_model_tbl(l_ind).config_line_id,
l_ato_model_tbl(l_ind).config_item_id
FROM oe_order_lines_all
WHERE header_id = p_order_lines(p_ind).header_id
AND link_to_line_id = p_order_lines(p_ind).ato_line_id
AND item_type_code = 'CONFIG';
SELECT line_id,
inventory_item_id
INTO l_ato_model_tbl(l_ind).config_line_id,
l_ato_model_tbl(l_ind).config_item_id
FROM oe_order_lines_all
WHERE header_id = p_order_lines(p_ind).header_id
AND link_to_line_id = p_order_lines(p_ind).ato_line_id
AND item_type_code = 'CONFIG'
AND split_from_line_id is null;
SELECT wip_supply_type,
parent_ato_line_id,
config_item_id
INTO l_ato_model_tbl(l_ind).wip_supply_type,
l_ato_model_tbl(l_ind).parent_ato_line_id,
l_config_item_id
FROM bom_cto_order_lines
WHERE line_id = p_order_lines(p_ind).line_id;
SELECT wip_entity_name
INTO l_ato_model_tbl(l_ind).wip_entity_name
FROM wip_entities
WHERE wip_entity_id = l_ato_model_tbl(l_ind).wip_entity_id;
SELECT internal_party_id,
project_location_id,
wip_location_id,
in_transit_location_id,
po_location_id,
category_set_id,
freeze_flag,
freeze_date,
show_all_party_location,
ownership_override_at_txn,
sfm_queue_bypass_flag,
auto_allocate_comp_at_wip,
to_date(null) txn_seq_start_date,
null ownership_cascade_at_txn
FROM csi_install_parameters;
SELECT oeh.header_id,
oel.line_id,
oel.inventory_item_id,
oel.ordered_quantity,
oel.order_quantity_uom,
oel.ordered_item,
oel.item_revision,
oel.line_number||'.'||nvl(oel.option_number,0)||'.'||oel.shipment_number line_number,
nvl(oel.ship_from_org_id,oeh.ship_from_org_id) ship_from_org_id,
nvl(oel.sold_to_org_id, oeh.sold_to_org_id) sold_to_org_id,
nvl(oel.deliver_to_org_id, oeh.deliver_to_org_id) deliver_to_org_id,
nvl(oel.invoice_to_org_id, oeh.invoice_to_org_id) invoice_to_org_id,
nvl(oel.ship_to_org_id, oeh.ship_to_org_id) ship_to_org_id,
oel.fulfilled_quantity,
oel.flow_status_code,
oel.item_type_code,
oel.link_to_line_id,
oel.ato_line_id,
oel.top_model_line_id,
oel.sort_order,
oel.org_id,
oeh.order_type_id,
oel.line_type_id,
oel.ship_to_contact_id,
oel.invoice_to_contact_id,
oel.deliver_to_contact_id,
nvl(oel.price_list_id, oeh.price_list_id) price_list_id,
oel.unit_selling_price,
oel.creation_date,
oel.component_sequence_id,
oel.line_category_code,
oel.cancelled_flag,
oel.source_type_code,
oel.drop_ship_flag,
nvl(oel.fulfilled_flag, 'N') fulfilled_flag,
oel.configuration_id,
oel.config_header_id,
oel.config_rev_nbr,
oel.shippable_flag,
oel.fulfillment_date,
oel.shipping_interfaced_flag,
oel.split_from_line_id,
oel.actual_shipment_date,
oel.shipped_quantity
FROM oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE oeh.order_number = p_order_number
AND oel.header_id = oeh.header_id
ORDER BY oel.line_number, oel.sort_order;
SELECT instance_id,
instance_number,
inventory_item_id,
serial_number,
lot_number,
location_type_code,
location_id,
instance_usage_code,
last_oe_order_line_id,
last_vld_organization_id,
active_end_date
FROM csi_item_instances
WHERE instance_id = p_inst_id;
SELECT iir.relationship_id,
iir.subject_id,
iir.relationship_type_code,
iir.active_end_date rel_end_date,
ii.serial_number,
ii.location_type_code,
ii.instance_usage_code,
ii.active_end_date instance_end_date
FROM csi_ii_relationships iir,
csi_item_instances ii
WHERE iir.object_id = p_inst_id
AND ii.instance_id = iir.subject_id;
SELECT ct.transaction_id,
ct.transaction_type_id,
to_char(ct.transaction_date, 'mm/dd/yy hh:mi:ss') transaction_date,
to_char(ct.source_transaction_date, 'mm/dd/yy hh:mi:ss') source_transaction_date,
ctt.source_transaction_type,
ct.inv_material_transaction_id
FROM csi_item_instances_h ciih,
csi_transactions ct,
csi_txn_types ctt
WHERE ciih.instance_id = p_inst_id
AND ct.transaction_id = ciih.transaction_id
AND ctt.transaction_type_id = ct.transaction_type_id
ORDER BY ct.source_transaction_date desc;
SELECT distinct afv.version, afv.creation_date, ab.bug_number
FROM ad_file_versions afv,
ad_patch_run_bug_actions aprba,
ad_patch_run_bugs aprb,
ad_bugs ab
WHERE afv.file_id = p_file_id
AND aprba.file_id(+) = afv.file_id
AND aprba.patch_file_version_id(+) = afv.file_version_id
AND aprb.patch_run_bug_id(+) = aprba.patch_run_bug_id
AND aprb.success_flag(+) = 'Y'
AND ab.bug_id(+) = aprb.bug_id
ORDER BY afv.creation_date desc;
SELECT version
FROM ad_file_versions
WHERE file_id = p_file_id
ORDER BY file_version_id desc;
SELECT file_id
INTO l_file_id
FROM ad_files
WHERE filename = p_file_name
AND subdir = p_subdir
AND app_short_name = p_prod_code;