[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_id
INTO l_inst_pty_id
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = p_rel_type_code
AND ((active_end_date is null ) OR (active_end_date > sysdate));
SELECT object_version_number
INTO l_obj_ver_num
FROM csi_i_org_assignments
WHERE instance_ou_id = p_instance_ou_id;
SELECT object_version_number
INTO l_obj_ver_num
FROM csi_ii_relationships
WHERE relationship_id = p_relationship_id
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number,
attribute_value_id
INTO x_obj_version_number,
x_attribute_value_id
FROM csi_iea_values
WHERE instance_id = p_instance_id
AND attribute_id = p_attribute_id
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number,
instance_ou_id
INTO x_obj_version_number,
x_instance_ou_id
FROM csi_i_org_assignments
WHERE instance_id = p_instance_id
AND operating_unit_id = p_operating_unit_id
AND relationship_type_code = p_rel_type_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number,
ip_account_id
INTO x_obj_version_number,
x_ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = p_instance_pty_id
AND relationship_type_code = p_rel_type_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number,
instance_party_id
INTO x_obj_version_number,
x_inst_pty_qty
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = p_rel_type_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number,
quantity
INTO x_obj_version_number,
x_inst_qty
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT object_version_number
INTO l_obj_ver_num
FROM csi_iea_values
WHERE attribute_value_id = p_attrib_value_id
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number
INTO l_obj_ver_num
FROM csi_i_parties
WHERE instance_party_id = p_inst_pty_id
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT object_version_number
INTO l_obj_ver_num
FROM csi_ip_accounts
WHERE ip_account_id = p_ip_acct_id
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT sub_type_id
INTO x_sub_type_id
FROM csi_source_ib_types
WHERE transaction_type_id = p_transaction_type_id
AND nvl(default_flag, 'N') = 'Y';
SELECT instance_status_id
INTO x_instance_status_id
FROM csi_instance_statuses
WHERE name = l_status_name;
SELECT primary_uom_code
INTO l_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id;
SELECT PRICING_CONTEXT,
PRICING_ATTRIBUTE1,
PRICING_ATTRIBUTE2,
PRICING_ATTRIBUTE3,
PRICING_ATTRIBUTE4,
PRICING_ATTRIBUTE5,
PRICING_ATTRIBUTE6,
PRICING_ATTRIBUTE7,
PRICING_ATTRIBUTE8,
PRICING_ATTRIBUTE9,
PRICING_ATTRIBUTE10,
PRICING_ATTRIBUTE11,
PRICING_ATTRIBUTE12,
PRICING_ATTRIBUTE13,
PRICING_ATTRIBUTE14,
PRICING_ATTRIBUTE15,
PRICING_ATTRIBUTE16,
PRICING_ATTRIBUTE17,
PRICING_ATTRIBUTE18,
PRICING_ATTRIBUTE19,
PRICING_ATTRIBUTE20,
PRICING_ATTRIBUTE21,
PRICING_ATTRIBUTE22,
PRICING_ATTRIBUTE23,
PRICING_ATTRIBUTE24,
PRICING_ATTRIBUTE25,
PRICING_ATTRIBUTE26,
PRICING_ATTRIBUTE27,
PRICING_ATTRIBUTE28,
PRICING_ATTRIBUTE29,
PRICING_ATTRIBUTE30,
PRICING_ATTRIBUTE31,
PRICING_ATTRIBUTE32,
PRICING_ATTRIBUTE33,
PRICING_ATTRIBUTE34,
PRICING_ATTRIBUTE35,
PRICING_ATTRIBUTE36,
PRICING_ATTRIBUTE37,
PRICING_ATTRIBUTE38,
PRICING_ATTRIBUTE39,
PRICING_ATTRIBUTE40,
PRICING_ATTRIBUTE41,
PRICING_ATTRIBUTE42,
PRICING_ATTRIBUTE43,
PRICING_ATTRIBUTE44,
PRICING_ATTRIBUTE45,
PRICING_ATTRIBUTE46,
PRICING_ATTRIBUTE47,
PRICING_ATTRIBUTE48,
PRICING_ATTRIBUTE49,
PRICING_ATTRIBUTE50,
PRICING_ATTRIBUTE51,
PRICING_ATTRIBUTE52,
PRICING_ATTRIBUTE53,
PRICING_ATTRIBUTE54,
PRICING_ATTRIBUTE55,
PRICING_ATTRIBUTE56,
PRICING_ATTRIBUTE57,
PRICING_ATTRIBUTE58,
PRICING_ATTRIBUTE59,
PRICING_ATTRIBUTE60,
PRICING_ATTRIBUTE61,
PRICING_ATTRIBUTE62,
PRICING_ATTRIBUTE63,
PRICING_ATTRIBUTE64,
PRICING_ATTRIBUTE65,
PRICING_ATTRIBUTE66,
PRICING_ATTRIBUTE67,
PRICING_ATTRIBUTE68,
PRICING_ATTRIBUTE69,
PRICING_ATTRIBUTE70,
PRICING_ATTRIBUTE71,
PRICING_ATTRIBUTE72,
PRICING_ATTRIBUTE73,
PRICING_ATTRIBUTE74,
PRICING_ATTRIBUTE75,
PRICING_ATTRIBUTE76,
PRICING_ATTRIBUTE77,
PRICING_ATTRIBUTE78,
PRICING_ATTRIBUTE79,
PRICING_ATTRIBUTE80,
PRICING_ATTRIBUTE81,
PRICING_ATTRIBUTE82,
PRICING_ATTRIBUTE83,
PRICING_ATTRIBUTE84,
PRICING_ATTRIBUTE85,
PRICING_ATTRIBUTE86,
PRICING_ATTRIBUTE87,
PRICING_ATTRIBUTE88,
PRICING_ATTRIBUTE89,
PRICING_ATTRIBUTE90,
PRICING_ATTRIBUTE91,
PRICING_ATTRIBUTE92,
PRICING_ATTRIBUTE93,
PRICING_ATTRIBUTE94,
PRICING_ATTRIBUTE95,
PRICING_ATTRIBUTE96,
PRICING_ATTRIBUTE97,
PRICING_ATTRIBUTE98,
PRICING_ATTRIBUTE99,
PRICING_ATTRIBUTE100
FROM OE_ORDER_PRICE_ATTRIBS
WHERE LINE_ID = p_line_id
AND FLEX_TITLE='QP_ATTR_DEFNS_PRICING'; -- Fix for bug 4151459
x_pricing_attb_tbl.delete;
SELECT party_site_id
INTO l_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = p_order_shipment_rec.ib_current_loc_id; -- ship_to_org_id;
SELECT party_site_id
INTO l_install_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = p_order_shipment_rec.ib_install_loc_id; -- ship_to_org_id;
SELECT party_site_id
INTO l_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = p_order_shipment_rec.ship_to_org_id;
SELECT party_id,
status
INTO l_party_id,
l_account_status
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_acct_id;
SELECT 'x'
INTO l_dummy
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND instance_party_id = p_inst_party_id
AND relationship_type_code = p_pty_rel_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT instance_id
INTO l_inst_id
FROM csi_item_instances
WHERE last_oe_order_line_id = p_order_line_id;
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE object_id = p_model_line_id
AND subject_id = p_line_id
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT instance_party_id
INTO l_inst_party_id
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = 'OWNER'
AND ((active_end_date is null)
OR
(active_end_date >= sysdate));
SELECT source_transaction_type_id
INTO l_trx_type_id
FROM csi_t_transaction_lines
WHERE transaction_line_id = p_trx_line_id;
SELECT transaction_line_id
INTO l_trx_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_id = p_src_trx_id
AND source_transaction_table = p_src_table_name;
SELECT
ip_account_id
INTO l_ip_acct_id
FROM csi_ip_accounts
WHERE instance_party_id = p_instance_party_id
AND relationship_type_code = 'OWNER'
AND ((active_end_date is null)
OR (active_end_date >= sysdate));
SELECT master_organization_id
INTO p_master_organization_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT internal_party_id
INTO x_int_party_id
FROM csi_install_parameters;
SELECT serial_number_control_code
INTO l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id;
SELECT b.header_id,
b.line_id,
mmt.inventory_item_id,
mmt.organization_id,
mmt.transaction_date,
b.ordered_quantity,
b.shipped_quantity,
b.top_model_line_id,
b.ato_line_id,
b.link_to_line_id,
NVL(b.invoice_to_org_id,c.invoice_to_org_id) invoice_to_org_id,
NVL(b.ship_to_org_id,c.ship_to_org_id) ship_to_org_id,
NVL(b.sold_from_org_id,c.sold_from_org_id) sold_from_org_id ,
NVL(b.sold_to_org_id,c.sold_to_org_id) sold_to_org_id,
NVL(b.sold_to_org_id,c.sold_to_org_id) customer_id,
NVL(b.ship_to_contact_id,c.ship_to_contact_id) ship_to_contact_id,
NVL(b.invoice_to_contact_id,c.invoice_to_contact_id) invoice_to_contact_id ,
b.order_quantity_uom order_quantity_uom,
b.item_type_code,
NVL(b.agreement_id, c.agreement_id) agreement_id,
c.order_number,
b.line_number||'.'||b.shipment_number||'.'||option_number,
b.actual_shipment_date actual_shipment_date,
b.fulfillment_date fulfillment_date,
b.org_id,
NVL(b.deliver_to_org_id,c.deliver_to_org_id) deliver_to_org_id,
b.ordered_item,
b.config_header_id,
b.config_rev_nbr,
b.configuration_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
b.unit_selling_price,
c.transactional_curr_code,
NVL(b.model_remnant_flag,'N'), --4344316
decode(c.order_source_Id, 28, 'SIEBEL',29,'SIEBEL',null) source_code
INTO l_order_line_rec.header_id,
l_order_line_rec.order_line_id ,
l_order_line_rec.inv_item_id,
l_order_line_rec.inv_org_id,
l_order_line_rec.transaction_date ,
l_order_line_rec.ordered_quantity,
l_order_line_rec.shipped_quantity ,
l_order_line_rec.top_model_line_id,
l_order_line_rec.ato_line_id,
l_order_line_rec.link_to_line_id ,
l_order_line_rec.invoice_to_org_id ,
l_order_line_rec.ship_to_org_id ,
l_order_line_rec.sold_from_org_id,
l_order_line_rec.sold_to_org_id,
l_order_line_rec.customer_id ,
l_order_line_rec.ship_to_contact_id ,
l_order_line_rec.invoice_to_contact_id ,
l_order_line_rec.order_quantity_uom ,
l_order_line_rec.item_type_code,
l_order_line_rec.agreement_id,
l_order_line_rec.order_number,
l_order_line_rec.line_number,
l_order_line_rec.actual_shipment_date,
l_order_line_rec.fulfillment_date,
l_order_line_rec.org_id,
l_order_line_rec.deliver_to_org_id,
l_order_line_rec.ordered_item,
l_order_line_rec.config_header_id,
l_order_line_rec.config_rev_nbr,
l_order_line_rec.configuration_id,
l_order_line_rec.mtl_action_id,
l_order_line_rec.mtl_src_type_id,
l_order_line_rec.unit_price,
l_order_line_rec.currency_code,
l_order_line_rec.model_remnant_flag, --4344316
l_order_line_rec.source_code
FROM mtl_material_transactions mmt,
oe_order_lines_all b,
oe_order_headers_all c
WHERE mmt.trx_source_line_id = b.line_id
AND b.header_id = c.header_id
AND mmt.transaction_id = p_mtl_transaction_id;
SELECT party_site_id
INTO l_order_line_rec.ship_to_party_site_id
FROM hz_cust_acct_sites_all hzcas,
hz_cust_site_uses_all hzcsu
WHERE hzcsu.site_use_id = l_order_line_rec.ship_to_org_id
AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id;
SELECT line_id
INTO x_split_ord_line_id
FROM oe_order_lines_all
WHERE split_from_line_id = p_order_line_id
AND header_id = p_order_header_id;
SELECT sub_type_id ,
src_change_owner_to_code,
src_status_id,
nvl(src_change_owner,'N'),
transaction_type_id,
non_src_change_owner_to_code,
non_src_status_id,
nvl(non_src_change_owner,'N'),
nvl(src_reference_reqd,'N'),
nvl(non_src_reference_reqd,'N'),
nvl(src_return_reqd,'N'),
non_src_return_reqd
INTO x_trx_sub_type_rec.sub_type_id,
x_trx_sub_type_rec.src_chg_owner_code,
x_trx_sub_type_rec.src_status_id,
x_trx_sub_type_rec.src_change_owner,
x_trx_sub_type_rec.trx_type_id,
x_trx_sub_type_rec.nsrc_chg_owner_code,
x_trx_sub_type_rec.nsrc_status_id,
x_trx_sub_type_rec.nsrc_change_owner,
x_trx_sub_type_rec.src_reference_reqd,
x_trx_sub_type_rec.nsrc_reference_reqd,
x_trx_sub_type_rec.src_return_reqd,
x_trx_sub_type_rec.nsrc_return_reqd
FROM csi_txn_sub_types
WHERE sub_type_id = p_sub_type_id
AND transaction_type_id = p_trx_type_id;
SELECT
object_id,
subject_id
INTO x_object_id,
x_subject_id
FROM csi_ii_relationships
WHERE relationship_id = p_ii_relationship_id
AND ((active_end_date is null) OR
(active_end_date > sysdate));
SELECT cii.instance_id,
'N' process_flag
FROM csi_item_instances cii,
oe_order_lines_all oel
WHERE oel.line_id = p_parent_line_id
AND cii.inventory_item_id = oel.inventory_item_id
AND cii.last_oe_order_line_id = oel.line_id;
select sum(ordered_quantity)
into l_order_line_qty
from oe_order_lines_all
where link_to_line_id = p_link_to_line_id
and inventory_item_id = p_order_item_id
and model_remnant_flag = 'Y';
SELECT ordered_quantity
INTO l_ordered_quantity
FROM oe_order_lines_all
WHERE line_id = p_link_to_line_id;
SELECT a.link_to_line_id,
nvl(msi.comms_nl_trackable_flag,'N')
INTO l_parent_line_id,
l_ib_trackable_flag
FROM oe_order_lines_all a,
mtl_system_items msi,
oe_system_parameters_all osp
WHERE a.line_id = p_link_to_line_id
AND osp.org_id = a.org_id
AND msi.inventory_item_id = a.inventory_item_id
AND msi.organization_id = osp.master_organization_id;
SELECT 'x'
INTO l_dummy
FROM mtl_system_items msi,
oe_order_lines_all orl,
oe_order_headers_all orh
WHERE msi.inventory_item_id = orl.inventory_item_id
AND msi.organization_id = NVL(orl.ship_from_org_id,orh.ship_from_org_id)
AND orl.header_id = orh.header_id
AND msi.comms_nl_trackable_flag = 'Y'
AND orl.line_id = x_link_to_line_id;
SELECT link_to_line_id , line_id
INTO x_link_to_line_id, l_line_id
FROM oe_order_lines_all
WHERE line_id = x_link_to_line_id;
PROCEDURE update_txn_line_dtl(
p_source_trx_id IN NUMBER,
p_source_trx_table IN VARCHAR2,
p_api_name IN VARCHAR2,
p_error_message IN VARCHAR2)
IS
l_literal1 VARCHAR2(30) := 'PROCESSED';
UPDATE csi_t_txn_line_details a
SET error_code = p_api_name,
error_explanation = substr(p_error_message,1,240),
processing_status = 'ERROR'
WHERE a.processing_status <> l_literal1
AND a.source_transaction_flag = 'Y'
AND a.transaction_line_id = (SELECT b.transaction_line_id -- changes for the bug 2851485
FROM csi_t_transaction_lines b
WHERE -- a.transaction_line_id = b.transaction_line_id AND -- Commented for Perf Bug 4311676
b.source_transaction_id = p_source_trx_id
AND b.source_transaction_table = p_source_trx_table);
debug('No of rows updated= '||sql%rowcount);
END update_txn_line_dtl;
SELECT subject_id
FROM csi_ii_relationships
WHERE object_id = pc_instance_id -- parent instance id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date , sysdate+1);
SELECT last_wip_job_id,
location_type_code,
instance_usage_code
INTO l_wip_job_id,
l_location_type_code,
l_instance_usage_code
FROM csi_item_instances
WHERE instance_id = rltns_rec.subject_id;
SELECT 'Y'
INTO l_leaf_node
FROM sys.dual
WHERE exists (SELECT 'X' FROM oe_order_lines_all
WHERE header_id = p_order_line_rec.header_id
AND link_to_line_id = p_order_line_rec.order_line_id);
SELECT 'Y'
INTO l_bom_found
FROM bom_bill_of_materials
WHERE assembly_item_id = p_order_line_rec.inv_item_id
AND organization_id = p_order_line_rec.inv_org_id
AND alternate_bom_designator is NULL;
SELECT wip_entity_id
INTO l_wip_job_id
FROM wip_discrete_jobs
WHERE primary_item_id = p_order_line_rec.inv_item_id
AND organization_id = p_order_line_rec.inv_org_id
AND source_line_id = p_order_line_rec.order_line_id
AND status_type <> 7; -- exclude the cancelled job
SELECT instance_party_id,
object_version_number
INTO x_i_party_rec.instance_party_id,
x_i_party_rec.object_version_number
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND party_id = x_i_party_rec.party_id
AND relationship_type_code = x_i_party_rec.relationship_type_code
AND contact_flag = x_i_party_rec.contact_flag;
SELECT object_version_number
INTO x_i_party_rec.object_version_number
FROM csi_i_parties
WHERE instance_party_id = p_t_party_rec.instance_party_id;
SELECT ip_account_id,
object_version_number
INTO x_i_pa_rec.ip_account_id,
x_i_pa_rec.object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = p_instance_party_id
AND party_account_id = x_i_pa_rec.party_account_id
AND relationship_type_code = x_i_pa_rec.relationship_type_code;
SELECT instance_party_id
INTO l_owner_inst_pty_id
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = 'OWNER';
/* Build party account table for create/update */
IF p_t_pty_acct_tbl.count > 0 THEN
FOR k in p_t_pty_acct_tbl.FIRST..p_t_pty_acct_tbl.LAST
LOOP
IF p_t_pty_acct_tbl(k).txn_party_detail_id = p_t_pty_tbl(pty_ind).txn_party_detail_id
THEN
convert_tpa_to_ipa(
p_instance_party_id => l_pty_rec.instance_party_id,
p_parent_tbl_index => l_pty_ind,
p_order_line_rec => p_order_line_rec,
p_t_pa_rec => p_t_pty_acct_tbl(k),
x_i_pa_rec => l_pa_rec);
SELECT instance_party_id,
object_version_number
INTO l_pty_rec.instance_party_id,
l_pty_rec.object_version_number
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND party_id = l_pty_rec.party_id
AND relationship_type_code = l_pty_rec.relationship_type_code
AND contact_flag = 'Y';
SELECT instance_party_id,
object_version_number
INTO l_pty_rec.instance_party_id,
l_pty_rec.object_version_number
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND party_id = l_pty_rec.party_id
AND relationship_type_code = l_pty_rec.relationship_type_code
AND contact_flag = 'Y';
SELECT ctld.txn_line_detail_id,
ctld.quantity,
ctld.transaction_line_id,
ctld.transaction_system_id,
ctld.csi_system_id
FROM csi_t_txn_line_details ctld,
csi_t_transaction_lines ctl
WHERE ctl.source_transaction_id = p_src_txn_id
AND ctl.source_transaction_table = p_src_txn_table
AND ctld.transaction_line_id = ctl.transaction_line_id
AND ctld.quantity > 1;
UPDATE csi_t_txn_line_details
SET quantity = 1
WHERE txn_line_detail_id = o_line_dtl_tbl(1).txn_line_detail_id;
SELECT cii.quantity alloc_quantity
FROM csi_ii_relationships ciir,
csi_item_instances cii
WHERE ciir.object_id = p_object_id
AND cii.instance_id = ciir.subject_id
AND cii.inventory_item_id = p_item_id
AND sysdate BETWEEN nvl(ciir.active_start_date, sysdate - 1)
AND nvl(ciir.active_end_date, sysdate + 1);
x_txn_ii_rltns_tbl.delete;
x_txn_line_dtls_lst.delete;
SELECT mmt.transaction_id ,
oel.line_id,
oeh.header_id,
oeh.order_number,
oel.line_id,
oel.line_number||'.'||oel.shipment_number,
mmt.transaction_date,
mmt.transaction_id
INTO x_mtl_txn_rec.mtl_transaction_id,
x_mtl_txn_rec.source_line_id,
x_mtl_txn_rec.source_header_ref_id,
x_mtl_txn_rec.source_header_ref,
x_mtl_txn_rec.source_line_ref_id,
x_mtl_txn_rec.source_line_ref,
x_mtl_txn_rec.source_transaction_date,
x_mtl_txn_rec.inv_material_transaction_id
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_transaction_id
AND oel.line_id = mmt.trx_source_line_id
AND oeh.header_id = oel.header_id;
x_txn_line_dtls_lst.delete;
update csi_t_txn_line_details
set quantity = 1
WHERE txn_line_detail_id = split_txn_dtl_id;
/* Build org_assignment table for create/update */
IF p_txn_org_assgn_tbl.count > 0 THEN
FOR j in p_txn_org_assgn_tbl.FIRST..p_txn_org_assgn_tbl.LAST LOOP
IF (p_txn_org_assgn_tbl(j).txn_line_detail_id = p_txn_line_detail_rec.txn_line_detail_id) AND
(( NVL(p_txn_org_assgn_tbl(j).active_end_date,l_date) > sysdate ) OR
(p_txn_org_assgn_tbl(j).active_end_date = FND_API.G_MISS_DATE )) THEN
l_instance_ou_id := p_txn_org_assgn_tbl(j).instance_ou_id;
SELECT instance_ou_id
INTO l_instance_ou_id
FROM csi_i_org_assignments
WHERE instance_id = p_txn_line_detail_rec.instance_id
AND relationship_type_code = p_txn_org_assgn_tbl(j).relationship_type_code;
SELECT instance_ou_id
INTO l_instance_ou_id
FROM csi_i_org_assignments
WHERE instance_id = p_txn_line_detail_rec.instance_id
AND relationship_type_code = p_txn_org_assgn_tbl(j).relationship_type_code
AND operating_unit_id = p_txn_org_assgn_tbl(j).operating_unit_id
AND (sysdate > nvl(active_start_date, sysdate-1)
AND
sysdate < nvl(active_end_date, sysdate + 1));
/* if the instance_ou_id does not exist then update for org_units */
l_obj_ver_num := csi_utl_pkg.get_org_obj_ver_num(
l_instance_ou_id);
/* Build ext attribs table for create/update */
IF p_txn_ext_attrib_vals_tbl.count > 0 THEN
FOR j in p_txn_ext_attrib_vals_tbl.FIRST..p_txn_ext_attrib_vals_tbl.LAST LOOP
IF (p_txn_ext_attrib_vals_tbl(j).txn_line_detail_id = p_txn_line_detail_rec.txn_line_detail_id ) AND
((NVL(p_txn_ext_attrib_vals_tbl(j).active_end_date,l_date) > sysdate ) OR
(p_txn_ext_attrib_vals_tbl(j).active_end_date = FND_API.G_MISS_DATE )) AND
(p_txn_ext_attrib_vals_tbl(j).PROCESS_FLAG = 'Y') THEN
debug('attrib_source_table ='||p_txn_ext_attrib_vals_tbl(j).attrib_source_table);
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = p_trx_rec.transaction_id;
SELECT non_src_change_owner,
non_src_change_owner_to_code
INTO l_non_source_change_owner,
l_non_src_change_owner_code
FROM csi_txn_sub_types
WHERE transaction_type_id = csi_order_ship_pub.g_txn_type_id
AND sub_type_id = p_trx_rec.txn_sub_type_id;
SELECT object_version_number,
location_type_code
INTO l_object_version_number,
l_location_code
FROM csi_item_instances
WHERE instance_id = l_old_instance_id;
Select instance_party_id,
object_version_number
Into l_upd_party_tbl(1).instance_party_id,
l_upd_party_tbl(1).object_version_number
From csi_i_parties
Where instance_id = l_old_instance_id
And relationship_Type_code = 'OWNER';
Select internal_party_id
Into l_upd_party_tbl(1).party_id
From csi_install_parameters;
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_upd_instance_rec,
p_ext_attrib_values_tbl => l_upd_ext_attrib_val_tbl,
p_party_tbl => l_upd_party_tbl,
p_account_tbl => l_upd_party_acct_tbl,
p_pricing_attrib_tbl => l_upd_pricing_attribs_tbl,
p_org_assignments_tbl => l_upd_org_units_tbl,
p_txn_rec => l_upd_txn_rec,
p_asset_assignment_tbl => l_upd_inst_asset_tbl,
x_instance_id_lst => l_upd_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT relationship_id, object_version_number,
subject_id, object_id, relationship_type_code
FROM csi_ii_relationships
WHERE object_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND (active_end_date is null OR active_end_date >= sysdate);
SELECT distinct instance_number
FROM CSI_ITEM_INSTANCES cii, CSI_II_RELATIONSHIPS cir
WHERE owner_party_id NOT IN (SELECT internal_party_id
FROM csi_install_parameters)
AND (cii.instance_id = cir.object_id or cii.instance_id = cir.subject_id)
AND cir.relationship_type_code = 'COMPONENT-OF'
AND (cii.active_end_date is null or cii.active_end_date > sysdate)
AND (cir.active_end_date is null or cir.active_end_date > sysdate)
AND cii.instance_id = p_instance_id;
SELECT instance_history_id, new_location_type_code,
new_instance_status_id
FROM csi_item_instances_h
WHERE instance_id = p_instance_id
ORDER BY instance_history_id desc;
SELECT relationship_id, object_version_number,
subject_id, object_id, relationship_type_code
FROM csi_ii_relationships
WHERE object_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF';
SELECT 'RMA' item_status
FROM csi_ii_relationships
WHERE subject_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND (active_end_date IS NOT NULL OR active_end_date <= sysdate)
ORDER BY RELATIONSHIP_ID DESC;
SELECT txn_relationship_id, object_id, relationship_type_code
FROM csi_t_ii_relationships
WHERE object_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND active_end_date IS NULL;
SELECT 'Y' config_instance, active_end_date
FROM csi_ii_relationships
WHERE subject_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
ORDER BY relationship_id DESC;
SELECT 'x'
INTO l_found
FROM csi_ii_relationships
WHERE subject_id = l_old_instance_id
AND object_id = l_parent_instance_id;
SELECT location_type_code
INTO l_location_type_code
FROM csi_item_instances
WHERE instance_id = l_old_instance_id;
SELECT 'N'
INTO l_transfer_components_flag
FROM csi_ii_relationships
WHERE object_id = l_new_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND active_end_date IS NULL;
SELECT relationship_id, object_version_number,
object_id, relationship_type_code
INTO l_relationship_id, l_ii_rel_obj_ver_num,
l_expire_object_id, l_relationship_type_code
FROM csi_ii_relationships
WHERE subject_id = l_old_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND (active_end_date is null OR active_end_date > sysdate);
SELECT relationship_id, object_version_number,
object_id, relationship_type_code
INTO l_relationship_id, l_object_version_number,
l_object_id, l_relationship_type_code
FROM csi_ii_relationships
WHERE subject_id = l_old_instance_id
AND object_id = l_parent_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND (active_end_date IS NOT NULL OR active_end_date <= sysdate);
SELECT instance_id,
instance_party_id,
object_version_number
INTO x_upd_party_tbl(i).instance_id,
x_upd_party_tbl(i).instance_party_id,
x_upd_party_tbl(i).object_version_number
FROM csi_i_parties
WHERE instance_id = l_instance_id
AND relationship_type_code = 'OWNER'
AND (sysdate > nvl(active_start_date, sysdate -1)
OR
sysdate < nvl(active_end_date, sysdate +1) );
SELECT instance_ou_id ,
object_version_number
INTO x_cre_org_units_tbl(l_cre_org).instance_ou_id,
x_cre_org_units_tbl(l_cre_org).object_version_number
FROM csi_i_org_assignments
WHERE instance_id = l_instance_id
AND relationship_type_code = x_cre_org_units_tbl(l_cre_org).relationship_type_code;
SELECT a.txn_line_detail_id,
a.quantity,
a.transaction_line_id,
a.transaction_system_id,
a.csi_system_id
FROM csi_t_txn_line_details a,
csi_t_transaction_lines b
WHERE a.transaction_line_id = b.transaction_line_id
AND b.source_transaction_id = p_src_trx_id
AND b.source_transaction_table = p_src_trx_table
AND a.source_transaction_flag = 'Y'
AND a.quantity > p_ratio;
SELECT mod(l_line_dtl_tbl(1).quantity,p_ratio)
INTO l_mod_value
FROM dual;
update csi_t_txn_line_details
set quantity = p_ratio
WHERE txn_line_detail_id = C1.txn_line_detail_id;
SELECT ship_rel.subject_id
INTO x_party_id
FROM hz_relationships ship_rel,
hz_cust_account_roles ship_roles
WHERE ship_roles.cust_account_role_id = p_cust_acct_role_id
AND ship_rel.party_id = ship_roles.party_id
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';
x_cre_party_tbl.delete;
x_upd_party_tbl.delete;
x_cre_party_acct_tbl.delete;
x_upd_party_acct_tbl.delete;
SELECT inventory_item_id,
organization_id
INTO l_item_control_rec.inventory_item_id,
l_item_control_rec.organization_id
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_txn_id;
SELECT serial_number_control_code,
lot_control_code,
revision_qty_control_code,
location_control_code,
bom_item_type,
primary_uom_code,
base_item_id,
pick_components_flag,
comms_nl_trackable_flag,
reservable_type,
shippable_item_flag,
mtl_transactions_enabled_flag
INTO l_item_control_rec.serial_control_code,
l_item_control_rec.lot_control_code,
l_item_control_rec.revision_control_code,
l_item_control_rec.locator_control_code,
l_item_control_rec.bom_item_type,
l_item_control_rec.primary_uom_code,
l_item_control_rec.model_item_id,
l_item_control_rec.pick_components_flag,
l_item_control_rec.ib_trackable_flag,
l_item_control_rec.reservable_type,
l_item_control_rec.shippable_flag,
l_item_control_rec.transactable_flag
FROM mtl_system_items
WHERE inventory_item_id = l_item_control_rec.inventory_item_id
AND organization_id = l_item_control_rec.organization_id;
SELECT nvl(negative_inv_receipt_code,1)
INTO l_item_control_rec.negative_balances_code
FROM mtl_parameters
WHERE organization_id = l_item_control_rec.organization_id;
SELECT instance_id
FROM csi_t_txn_line_details
WHERE txn_line_detail_id = c_txn_line_detail_id ;
SELECT source_transaction_table, source_transaction_id
FROM csi_t_txn_line_details a ,
csi_t_transaction_lines b
WHERE a.transaction_line_id = b.transaction_line_id
AND a.txn_line_detail_id = c_txn_line_detail_id ;
SELECT *
FROM csi_t_ii_relationships
WHERE (( subject_type = 'T' AND subject_id = c_orig_oe_tld)
OR ( object_type = 'T' AND object_id = c_orig_oe_tld))
AND NVL(active_end_date ,SYSDATE) >= SYSDATE ;
SELECT a.*
FROM csi_t_txn_line_details a ,
csi_t_transaction_lines b ,
mtl_system_items_b c
WHERE a.transaction_line_id = b.transaction_line_id
AND ((b.source_transaction_table = 'WSH_DELIVERY_DETAILS' AND c.shippable_item_flag = 'Y')
OR (b.source_transaction_table = 'OE_ORDER_LINES_ALL' AND c.shippable_item_flag = 'N'))
AND a.instance_id IS NOT NULL ---meaning it is already processed. or it is Non Source
AND a.source_txn_line_detail_id = c_partner_oe_tld
AND a.inventory_item_id = c.inventory_item_id
AND a.inv_organization_id = c.organization_id;
Select source_transaction_id
Into l_orig_oe_src_txn_id
From csi_t_transaction_lines tl, csi_t_txn_line_details tld
Where tld.transaction_line_id = tl.transaction_line_id
And tld.txn_line_detail_id = l_orig_oe_tld;
SELECT *
FROM csi_t_ii_relationships
WHERE (( subject_type = 'T' AND subject_id = c_txn_line_detail_id )
OR (object_type = 'T' AND object_id = c_txn_line_detail_id ))
AND NVL(active_end_date ,SYSDATE) >= SYSDATE ;
px_instance_tbl.DELETE;
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND link_to_line_id = p_parent_line_id
ORDER BY line_number, shipment_number;
l_line_tbl_nxt_lvl.delete;
SELECT nvl(msi.comms_nl_trackable_flag,'N')
INTO l_ib_trackable_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_line_tbl(l_ind).inventory_item_id
AND msi.organization_id = p_om_vld_org_id;
l_line_tbl.DELETE;
SELECT 'X'
FROM csi_t_transaction_lines ctl,
csi_t_txn_line_details ctld
WHERE ctl.source_transaction_id = p_ord_line_id
AND ctl.source_transaction_table = 'WSH_DELIVERY_DETAILS'
AND ctld.transaction_line_id = ctl.transaction_line_id
AND nvl(ctld.source_transaction_flag,'N') = 'N'
AND ctld.instance_id = p_instance_id
AND ctld.processing_status = 'PROCESSED';
SELECT 'X'
FROM csi_ii_relationships
WHERE subject_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND active_end_date IS NULL;
select sum(ordered_quantity)
into l_order_line_qty
from oe_order_lines_all
where link_to_line_id = l_line_tbl(l_ol_ind).link_to_line_id
and inventory_item_id = l_line_tbl(l_ol_ind).inventory_item_id
and model_remnant_flag = 'Y';
l_temp_instance_hdr_tbl.DELETE;
l_txn_ps_tbl.DELETE;