[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oel.line_id
FROM mtl_system_items msi,
oe_order_lines_all oel
WHERE oel.ato_line_id = p_ato_line_id
AND oel.item_type_code = 'OPTION'
AND nvl(oel.cancelled_flag,'N') <> 'Y'
AND msi.inventory_item_id = oel.inventory_item_id
AND msi.organization_id = p_order_line_rec.om_vld_org_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
AND nvl(msi.shippable_item_flag, 'N') = 'Y';
l_c_tld_tbl.delete;
l_c_t_pty_tbl.delete;
l_c_t_pty_acct_tbl.delete;
l_c_t_oa_tbl.delete;
l_c_t_ea_tbl.delete;
l_c_t_sys_tbl.delete;
l_c_t_iir_tbl.delete;
SELECT transaction_line_id
INTO l_c_tl_rec.transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = l_c_tl_rec.source_transaction_table
AND source_transaction_id = l_c_tl_rec.source_transaction_id
AND source_transaction_type_id = l_c_tl_rec.source_transaction_type_id;
SELECT ordered_quantity
INTO l_parent_ord_qty
FROM oe_order_lines_all
WHERE line_id = p_order_line_rec.link_to_line_id;
SELECT instance_party_id
INTO l_instance_party_id
FROM csi_i_parties
WHERE instance_id = l_ship_tbl(s_ind).instance_id
AND relationship_type_code = 'OWNER';
SELECT ip_account_id
INTO l_ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = l_instance_party_id
AND relationship_type_code = 'OWNER';
SELECT transaction_line_id
INTO l_transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
AND source_transaction_id = p_order_line_rec.order_line_id;
SELECT sum(abs(quantity))
INTO l_total_tld_quantity
FROM csi_t_txn_line_details
WHERE transaction_line_id = l_transaction_line_id
AND source_transaction_flag = 'Y';
SELECT src_change_owner,
src_change_owner_to_code
INTO l_src_change_owner,
l_src_change_owner_to_code
FROM csi_ib_txn_types
WHERE sub_type_id = l_c_tld_rec.sub_type_id;
SELECT party_id
INTO l_c_tpd_tbl(1).party_source_id
FROM hz_cust_accounts
WHERE cust_account_id = p_order_line_rec.customer_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 = l_order_line_rec.ib_current_loc_id;
SELECT party_site_id
INTO l_party_install_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 = l_order_line_rec.ib_install_loc_id;
SELECT transaction_line_id
INTO l_trx_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_id = l_copy_txn_line_rec.source_transaction_id
AND source_transaction_table = l_copy_txn_line_rec.source_transaction_table;
SELECT transaction_line_id
INTO l_trx_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_id = l_copy_txn_line_rec.source_transaction_id
AND source_transaction_table = l_copy_txn_line_rec.source_transaction_table;
UPDATE csi_t_txn_line_details
SET processing_status = 'IN_PROCESS'
WHERE transaction_line_id IN (
SELECT transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = l_literal2
AND source_transaction_id = l_order_line_rec.order_line_id)
AND source_transaction_flag = 'N'
AND processing_status <> l_literal1;
debug('Installation details for the final process - Update Install Base.' );
/* pass it to update_install_base */
/*---------------------------------------------------*/
csi_utl_pkg.Get_Pricing_Attribs(
p_line_id => l_order_line_rec.order_line_id,
x_pricing_attb_tbl => x_pricing_attb_tbl,
x_return_status => x_return_status );
/* relationships are build then Update instance in IB */
/*--------------------------------------------------------*/
update_install_base(
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_txn_line_rec => l_transaction_line_rec,
p_txn_line_detail_tbl => l_txn_line_detail_tbl,
p_txn_party_detail_tbl => l_txn_party_detail_tbl,
p_txn_pty_acct_dtl_tbl => l_txn_pty_acct_dtl_tbl,
p_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
p_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_vals_tbl,
p_txn_ii_rltns_tbl => l_txn_ii_rltns_tbl,
p_txn_systems_tbl => l_txn_systems_tbl,
p_pricing_attribs_tbl => x_pricing_attb_tbl,
p_order_line_rec => l_order_line_rec,
p_trx_rec => l_trx_rec,
p_source => 'SHIPMENT',
p_validate_only => 'N',
px_error_rec => l_error_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT txn_relationship_id
FROM csi_t_ii_relationships
WHERE transaction_line_id = p_tl_id
AND (subject_id = p_tld_id
OR
object_id = p_tld_id)
AND relationship_type_code = 'COMPONENT-OF';
SELECT sub_tld.instance_id sub_instance_id,
obj_tld.instance_id obj_instance_id
FROM csi_t_ii_relationships iir,
csi_t_txn_line_details sub_tld,
csi_t_txn_line_details obj_tld
WHERE iir.transaction_line_id = p_tl_id
AND (iir.subject_id = p_tld_id
OR
iir.object_id = p_tld_id)
AND iir.relationship_type_code <> 'COMPONENT-OF'
AND sub_tld.transaction_line_id = iir.transaction_line_id
AND sub_tld.txn_line_detail_id = iir.subject_id
AND obj_tld.transaction_line_id = iir.transaction_line_id
AND obj_tld.txn_line_detail_id = iir.object_id;
SELECT non_src_status_id
INTO l_ns_instance_rec.instance_status_id
FROM csi_txn_sub_types
WHERE transaction_type_id = p_trx_rec.transaction_type_id
AND sub_type_id = p_txn_line_detail_rec.sub_type_id;
SELECT object_version_number,
location_type_code,
serial_number,
active_end_date
INTO l_ns_instance_rec.object_version_number,
l_location_code,
l_serial_number,
l_active_end_date
FROM csi_item_instances
WHERE instance_id = p_txn_line_detail_rec.instance_id;
debug('Instance Status is TERMINABLE so cannot update the instance status');
p_api_name => 'update_item_instance');
/* non source status update call. */
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_ns_instance_rec,
p_ext_attrib_values_tbl => l_ns_ext_attrib_val_tbl,
p_party_tbl => l_ns_party_tbl,
p_account_tbl => l_ns_party_acct_tbl,
p_pricing_attrib_tbl => l_ns_pricing_attribs_tbl,
p_org_assignments_tbl => l_ns_org_units_tbl,
p_txn_rec => l_trx_rec,
p_asset_assignment_tbl => l_ns_inst_asset_tbl,
x_instance_id_lst => l_ns_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT object_version_number
INTO l_exp_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = p_txn_line_detail_rec.instance_id;
SELECT object_version_number
INTO l_trx_rec.object_version_number
FROM csi_transactions
WHERE transaction_id = l_trx_rec.transaction_id;
csi_transactions_pvt.update_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_transaction_rec => l_trx_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT ool.line_id,
ool.header_id,
ool.item_type_code,
ool.cust_po_number,
ool.ato_line_id,
ool.top_model_line_id,
ool.link_to_line_id,
NVL(ool.invoice_to_contact_id ,ooh.invoice_to_contact_id ) invoice_to_contact_id ,
ool.line_type_id,
ool.ordered_quantity,
ool.shipped_quantity ord_line_shipped_qty,
NVL(ool.ship_to_contact_id, ooh.ship_to_contact_id) ship_to_contact_id,
NVL(ool.ship_from_org_id, ooh.ship_from_org_id) ship_from_org_id ,
NVL(ool.sold_to_org_id, ooh.sold_to_org_id) sold_to_org_id ,
NVL(ool.sold_from_org_id, ooh.sold_from_org_id) sold_from_org_id ,
NVL(ool.ship_to_org_id, ooh.ship_to_org_id) ship_to_org_id ,
NVL(ool.invoice_to_org_id, ooh.invoice_to_org_id) invoice_to_org_id ,
NVL(ool.deliver_to_org_id, ooh.deliver_to_org_id) deliver_to_org_id ,
ool.order_quantity_uom,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id inv_organization_id,
mmt.revision revision,
mmt.subinventory_code subinventory,
mmt.locator_id locator_id,
null lot_number,
mut.serial_number serial_number,
abs(mmt.transaction_quantity) shipped_quantity,
mmt.transaction_uom,
mmt.transaction_date,
-- msi.lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_trx_id),lot_control_code) lot_control_code,-- Added for bug#14835893
msi.serial_number_control_code,
msi.reservable_type,
haou.location_id hr_location_id,
msei.location_id subinv_location_id,
to_char(null) ib_owner,
to_number(null) end_customer_id,
to_char(null) ib_install_loc,
to_number(null) ib_install_loc_id,
to_char(null) ib_current_loc,
to_number(null) ib_current_loc_id,
ooh.order_source_id order_source_id -- Added for Siebel Genesis Project
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_system_items msi,
mtl_unit_transactions mut,
mtl_material_transactions mmt,
mtl_secondary_inventories msei,
hr_all_organization_units haou
WHERE mmt.transaction_id = p_trx_id
AND mmt.transaction_id = mut.transaction_id(+)
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.lot_control_code = 1 -- no lot case
AND mmt.organization_id = haou.organization_id(+)
AND mmt.subinventory_code = msei.secondary_inventory_name(+)
AND mmt.organization_id = msei.organization_id(+)
AND ool.line_id = mmt.trx_source_line_id
AND ooh.header_id = ool.header_id
UNION
SELECT ool.line_id,
ool.header_id,
ool.item_type_code,
ool.cust_po_number,
ool.ato_line_id,
ool.top_model_line_id,
ool.link_to_line_id,
NVL(ool.invoice_to_contact_id, ooh.invoice_to_contact_id ) invoice_to_contact_id ,
ool.line_type_id,
ool.ordered_quantity,
ool.shipped_quantity ord_line_shipped_qty,
NVL(ool.ship_to_contact_id, ooh.ship_to_contact_id) ship_to_contact_id,
NVL(ool.ship_from_org_id , ooh.ship_from_org_id) ship_from_org_id ,
NVL(ool.sold_to_org_id , ooh.sold_to_org_id) sold_to_org_id ,
NVL(ool.sold_from_org_id, ooh.sold_from_org_id) sold_from_org_id ,
NVL(ool.ship_to_org_id , ooh.ship_to_org_id) ship_to_org_id ,
NVL(ool.invoice_to_org_id, ooh.invoice_to_org_id) invoice_to_org_id ,
NVL(ool.deliver_to_org_id, ooh.deliver_to_org_id) deliver_to_org_id ,
ool.order_quantity_uom ,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id inv_organization_id,
mmt.revision revision,
mmt.subinventory_code subinventory,
mmt.locator_id locator_id,
mtln.lot_number lot_number,
mut.serial_number serial_number,
abs(mtln.transaction_quantity) shipped_quantity,
mmt.transaction_uom,
mmt.transaction_date,
-- msi.lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_trx_id),lot_control_code) lot_control_code,-- Added for bug#14835893
msi.serial_number_control_code,
msi.reservable_type,
haou.location_id hr_location_id,
msei.location_id subinv_location_id,
to_char(null) ib_owner,
to_number(null) end_customer_id,
to_char(null) ib_install_loc,
to_number(null) ib_install_loc_id,
to_char(null) ib_current_loc,
to_number(null) ib_current_loc_id,
ooh.order_source_id order_source_id -- Added for Siebel Genesis Project
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_system_items msi,
mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_secondary_inventories msei,
hr_all_organization_units haou
WHERE mmt.transaction_id = p_trx_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND mtln.serial_transaction_id = mut.transaction_id(+)
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.lot_control_code = 2 -- lot control case
AND mmt.organization_id = haou.organization_id(+)
AND mmt.subinventory_code = msei.secondary_inventory_name(+)
AND mmt.organization_id = msei.organization_id(+)
AND mmt.trx_source_line_id = ool.line_id
AND ool.header_id = ooh.header_id;
Select object_version_number
Into l_zero_instance_rec.object_version_number
From csi_item_instances
Where instance_id = l_cre_instance_rec.instance_id;
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_zero_instance_rec,
p_party_tbl => l_zero_parties_tbl,
p_account_tbl => l_zero_pty_accts_tbl,
p_org_assignments_tbl => l_zero_org_units_tbl,
p_ext_attrib_values_tbl => l_zero_ea_values_tbl,
p_pricing_attrib_tbl => l_zero_pricing_tbl,
p_asset_assignment_tbl => l_zero_assets_tbl,
p_txn_rec => l_trx_rec,
x_instance_id_lst => l_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
debug('Instance ('||l_zero_instance_rec.instance_id||') created and updated with zero quantity successfully.');
Select object_version_number
Into l_instance_header_tbl(1).object_version_number
From csi_item_instances
Where instance_id = l_zero_instance_rec.instance_id;
x_model_inst_tbl.delete;
SELECT transaction_line_id
INTO l_txn_line_rec.transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_id = l_txn_line_rec.source_transaction_id
AND source_transaction_table = l_txn_line_rec.source_transaction_table;
SELECT instance_usage_code
INTO l_instance_usage_code
FROM csi_item_instances
WHERE instance_id = px_txn_line_detail_tbl(l_ind).instance_id;
/* the txn line details and updates the txn line dtls */
/* with the processing_status as "IN_PROCESS" */
/*----------------------------------------------------------*/
PROCEDURE process_txn_dtl(
p_serial_code IN NUMBER,
p_txn_sub_type_rec IN txn_sub_type_rec,
p_order_line_rec IN order_line_rec,
x_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
x_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
x_order_shipment_tbl IN OUT NOCOPY order_shipment_tbl,
x_return_status OUT NOCOPY varchar2)
IS
x_msg_count number ;
l_upd_txn_line_dtl_tbl.delete;
l_upd_txn_ii_rltns_tbl.delete;
l_upd_txn_party_detail_tbl.delete;
l_upd_txn_pty_acct_dtl_tbl.delete ;
l_upd_txn_org_assgn_tbl.delete;
l_upd_txn_ext_attr_vals_tbl.delete;
/* update the txn_line_detail as ERROR and create new txn_line_dtls*/
l_upd_txn_line_dtl_tbl(l_upd).txn_line_detail_id := x_txn_line_detail_tbl(j).txn_line_detail_id;
/* update the txn_detail with the qty of difference between txn_line_dtls and shipment*/
l_upd_txn_line_dtl_tbl(l_upd).txn_line_detail_id := x_txn_line_detail_tbl(j).txn_line_detail_id;
UPDATE csi_t_txn_line_details
SET processing_status = 'SUBMIT'
WHERE txn_line_detail_id = x_txn_line_detail_tbl(j).txn_line_detail_id;
/* update the txn_detail with the instance in shipping record */
l_upd_txn_line_dtl_tbl(l_upd).txn_line_detail_id :=
x_txn_line_detail_tbl(j).txn_line_detail_id;
/* update the txn_dtls as errored and create txn_dtls from shipping rec */
l_upd_txn_line_dtl_tbl(l_upd).txn_line_detail_id := x_txn_line_detail_tbl(j).txn_line_detail_id;
/*update the txn_detail with the instance in shipping record */
debug('Config does not exists, so processing the txn line dtls ');
/* update txn_detail with the instance from shipping record */
l_upd_txn_line_dtl_tbl(l_upd).txn_line_detail_id := x_txn_line_detail_tbl(j).txn_line_detail_id;
/* update the txn dtls with the processing status */
csi_t_txn_details_grp.update_txn_line_dtls(
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_none,
p_txn_line_rec => l_upd_txn_line_rec,
p_txn_line_detail_tbl => l_upd_txn_line_dtl_tbl,
px_txn_ii_rltns_tbl => l_upd_txn_ii_rltns_tbl,
px_txn_party_detail_tbl => l_upd_txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl => l_upd_txn_pty_acct_dtl_tbl,
px_txn_org_assgn_tbl => l_upd_txn_org_assgn_tbl,
px_txn_ext_attrib_vals_tbl => l_upd_txn_ext_attr_vals_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
g_api_name := 'csi_t_txn_details_grp.update_txn_line_dtls';
debug('update_txn_line_dtls completed successfully');
SELECT last_vld_organization_id
INTO l_last_vld_organization_id
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT object_version_number,
quantity - p_quantity
INTO l_instance_rec.object_version_number,
l_instance_rec.quantity
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
p_api_name => 'update_item_instance',
p_pkg_name => 'csi_item_instance_pub');
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_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_val_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_party_acct_tbl,
p_pricing_attrib_tbl => l_pricing_attribs_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_txn_rec => p_trx_rec,
p_asset_assignment_tbl => l_inst_asset_tbl,
x_instance_id_lst => l_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
g_api_name := 'csi_item_instance_pub.update_item_instance';
l_c_parties_tbl.DELETE;
l_c_pty_accts_tbl.DELETE;
SELECT object_version_number
INTO l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
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_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => px_csi_txn_rec,
x_instance_id_lst => l_u_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT object_version_number,
location_type_code
INTO l_inst_object_ver_num,
l_location_type_code
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT * INTO l_order_line_rec
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT * INTO l_order_header_rec
FROM oe_order_headers_all
WHERE header_id = l_order_line_rec.header_id;
SELECT HCAS.party_site_id
INTO l_party_site_id
FROM hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCAS
WHERE HCSU.site_use_id = l_order_line_rec.ship_to_org_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
SELECT party_id
INTO l_owner_party_id
FROM hz_cust_accounts
WHERE cust_account_id = l_order_line_rec.sold_to_org_id;
SELECT instance_party_id,
object_version_number
INTO l_instance_party_id,
l_pty_object_ver_num
FROM csi_i_parties
WHERE instance_id = p_instance_id;
p_api_name => 'update_item_instance',
p_pkg_name => 'csi_item_instance_pub');
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_u_instance_rec,
p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
p_party_tbl => l_u_party_tbl,
p_account_tbl => l_u_party_acct_tbl,
p_pricing_attrib_tbl => l_u_pricing_attribs_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_txn_rec => p_csi_txn_rec,
p_asset_assignment_tbl => l_u_inst_asset_tbl,
x_instance_id_lst => l_u_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT wip.inventory_item_id,
sum(required_quantity) qty_required,
sum(quantity_issued) qty_issued,
sum(quantity_per_assembly) qty_per_assy
FROM wip_requirement_operations wip,
mtl_system_items msi
WHERE wip_entity_id = p_wip_entity_id
AND wip.organization_id = p_organization_id
AND wip.inventory_item_id = msi.inventory_item_id
AND wip.organization_id = msi.organization_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
AND nvl(quantity_issued,0) > 0
GROUP BY wip.inventory_item_id;
SELECT instance_id,
quantity,
serial_number
FROM csi_item_instances
WHERE inventory_item_id = p_item_id
AND location_type_code = 'WIP'
AND wip_job_id = p_wip_entity_id;
SELECT instance_id,
quantity,
serial_number
FROM csi_item_instances
WHERE inventory_item_id = p_item_id
AND location_type_code = 'WIP'
AND wip_job_id = p_wip_entity_id
AND quantity <= p_qty_per_assy;
SELECT serial_number_control_code
INTO l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = req_rec.inventory_item_id
AND organization_id = p_organization_id;
px_tld_account_tbl.delete(a_ind);
px_tld_party_tbl.DELETE(p_ind);
px_tld_oa_tbl.delete(oa_ind);
px_tld_ea_tbl.delete(ea_ind);
SELECT system_id
INTO l_system_id
FROM csi_systems_vl
WHERE system_type_code = px_txn_systems_tbl(ind).system_type_code
AND name = px_txn_systems_tbl(ind).system_name
AND customer_id = px_txn_systems_tbl(ind).customer_id;
SELECT sum(quantity)
INTO l_total_qty
FROM csi_item_instances
WHERE last_oe_order_line_id = p_order_line_rec.order_line_id
AND inventory_item_id = p_order_line_rec.inv_item_id;
UPDATE csi_t_txn_line_details a
SET error_code = NULL,
error_explanation = NULL ,
processing_status = 'PROCESSED'
WHERE a.processing_status <> l_literal1
AND a.source_transaction_flag = 'Y'
AND a.inventory_item_id = p_order_line_rec.inv_item_id
AND a.transaction_line_id in (SELECT transaction_line_id
FROM csi_t_transaction_lines b
WHERE -- a.transaction_line_id = b.transaction_line_id AND -- Commented for Perf Bug bug 4311676
b.source_transaction_id = p_order_line_rec.order_line_id
AND b.source_transaction_table = l_literal2 );
UPDATE csi_t_transaction_lines
SET processing_status = 'PROCESSED'
WHERE source_transaction_id = p_order_line_rec.order_line_id;
DELETE FROM csi_t_txn_line_details
WHERE transaction_line_id = p_transaction_line_id
AND source_transaction_flag = 'Y'
AND processing_status <> l_literal1;
SELECT oel.line_id,
oel.inventory_item_id,
oel.ship_from_org_id,
oel.ordered_quantity,
oel.split_from_line_id
INTO l_config_rec.line_id,
l_config_rec.item_id,
l_config_rec.ship_organization_id,
l_config_rec.order_quantity,
l_config_rec.split_from_line_id
FROM oe_order_lines_all oel
WHERE oel.header_id = p_order_line_rec.header_id
AND oel.link_to_line_id = p_order_line_rec.ato_line_id
AND oel.item_type_code = 'CONFIG';
SELECT oel.line_id,
oel.inventory_item_id,
oel.ship_from_org_id,
oel.ordered_quantity,
oel.split_from_line_id
INTO l_config_rec.line_id,
l_config_rec.item_id,
l_config_rec.ship_organization_id,
l_config_rec.order_quantity,
l_config_rec.split_from_line_id
FROM oe_order_lines_all oel
WHERE oel.header_id = p_order_line_rec.header_id
AND oel.link_to_line_id = p_order_line_rec.ato_line_id
AND oel.item_type_code = 'CONFIG'
AND oel.split_from_line_id is null;
SELECT serial_number_control_code
INTO l_config_rec.serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_config_rec.item_id
AND organization_id = l_config_rec.ship_organization_id;
PROCEDURE update_install_base(
p_api_version IN number,
p_commit IN varchar2 := fnd_api.g_false,
p_init_msg_list IN varchar2 := fnd_api.g_false,
p_validation_level IN number := fnd_api.g_valid_level_full,
p_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
p_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
p_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
p_txn_pty_acct_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
p_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
p_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
p_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
p_txn_systems_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
p_pricing_attribs_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
p_order_line_rec IN order_line_rec,
p_trx_rec IN csi_datastructures_pub.transaction_rec,
p_source IN varchar2,
p_validate_only IN varchar2,
px_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2)
IS
l_api_name CONSTANT varchar2(30) := 'update_install_base';
SAVEPOINT update_install_base;
api_log('update_install_base');
fnd_message.set_name('CSI', 'CSI_CANNOT_UPDATE');
SELECT nvl(fnd_profile.value('CSI_DEF_INST_DATE'),'N')
INTO l_default_install_date
FROM dual;
l_cps_tbl.delete;
l_cre_party_tbl.delete;
l_upd_party_tbl.delete;
l_cre_party_acct_tbl.delete;
l_upd_party_acct_tbl.delete;
l_cre_org_units_tbl.delete;
l_upd_org_units_tbl.delete;
l_cre_ext_attrib_val_tbl.delete;
l_upd_ext_attrib_val_tbl.delete;
l_cre_pricing_attribs_tbl.delete;
l_cre_party_acct_tbl.delete;
l_cre_party_acct_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_line_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_line_rec.ib_install_loc_id; -- ship_to_org_id;
/* If the instance reference exists then call update instance api else */
/* call create instance api .Also check if the split instance profile */
/* is on then split it into so many number of instances */
IF NVL(l_tld_rec.instance_id,fnd_api.g_miss_num ) <> fnd_api.g_miss_num THEN
l_upd_instance_rec := l_tmp_instance_rec;
/* and update the destination instance to make it a cp */
/*--------------------------------------------------------------------*/
BEGIN
SELECT instance_id,
quantity,
object_version_number
INTO l_so_instance_id,
l_so_qty,
l_so_obj_ver_num
FROM csi_item_instances
WHERE serial_number = l_tld_rec.serial_number
AND inventory_item_id = l_tld_rec.inventory_item_id
AND (instance_usage_code = 'RETURNED' --added the outer braces for bug6310708
--Start of code for 6188180
OR (instance_usage_code = 'IN_TRANSIT'
AND
active_end_date IS NOT NULL)
--End of code for 6188180
-- Bug 10092644
OR (instance_usage_code = 'IN_WIP'
AND
active_end_date IS NOT NULL)
);
SELECT object_version_number
INTO l_inst_obj_ver_num
FROM csi_item_instances
WHERE instance_id = l_tld_rec.changed_instance_id;
/* update the existing instance with the qty equal to (inst_qty - shipped_qty) */
/* and create a new instance with qty equal to txn qty */
l_quantity1 := (l_curr_instance_qty - l_tld_rec.quantity );
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_un_exp_instance_rec,
p_ext_attrib_values_tbl => l_un_exp_ext_attrib_val_tbl,
p_party_tbl => l_un_exp_party_tbl,
p_account_tbl => l_un_exp_party_acct_tbl,
p_pricing_attrib_tbl => l_un_exp_pricing_attribs_tbl,
p_org_assignments_tbl => l_un_exp_org_units_tbl,
p_txn_rec => l_trx_rec,
p_asset_assignment_tbl => l_un_exp_inst_asset_tbl,
x_instance_id_lst => l_chg_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
/* Assign the values for update_instance_rec */
l_upd_instance_rec.instance_id := l_instance_id ;
SELECT HCAS.party_site_id
INTO l_upd_party_site_id
FROM hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCAS
WHERE HCSU.site_use_id = p_order_line_rec.ib_install_loc_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
l_upd_ext_attrib_val_tbl.DELETE;
l_upd_party_tbl.DELETE;
l_upd_party_acct_tbl.DELETE;
l_upd_pricing_attribs_tbl.DELETE;
l_upd_org_units_tbl.DELETE;
l_upd_inst_asset_tbl.DELETE;
SELECT owner_party_id,
object_version_number
INTO l_owner_party_id,
l_chg_instance_rec.object_version_number
FROM CSI_ITEM_INSTANCES
WHERE instance_id = l_upd_party_tbl(1).instance_id;
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_chg_instance_rec,
p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
p_party_tbl => l_chg_party_tbl,
p_account_tbl => l_chg_party_acct_tbl,
p_pricing_attrib_tbl => l_chg_pricing_attribs_tbl,
p_org_assignments_tbl => l_chg_org_units_tbl,
p_txn_rec => l_trx_rec,
p_asset_assignment_tbl => l_chg_inst_asset_tbl,
x_instance_id_lst => l_chg_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT object_version_number
INTO l_upd_instance_rec.object_version_number
FROM CSI_ITEM_INSTANCES
WHERE instance_id = l_upd_instance_rec.instance_id;
SELECT object_version_number
INTO l_upd_party_tbl(1).object_version_number
FROM CSI_I_PARTIES
WHERE instance_party_id = l_upd_party_tbl(1).instance_party_id;
l_upd_party_tbl.delete;
l_upd_party_acct_tbl.delete;
SELECT 'Y' INTO l_cia_found
FROM sys.dual
WHERE exists (
SELECT '1' FROM csi_i_assets
WHERE instance_id = l_upd_instance_rec.instance_id
AND sysdate between nvl(active_start_date, sysdate-1) and nvl(active_end_date, sysdate+1));
p_api_name => 'update_item_instance',
p_pkg_name => 'csi_item_instance_pub');
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_trx_rec,
p_asset_assignment_tbl => l_upd_inst_asset_tbl,
x_instance_id_lst => l_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
g_api_name := 'csi_item_instance_pub.update_item_instance';
l_new_pricing_attribs_tbl.delete;
l_old_pricing_attribs_tbl.delete;
SELECT pricing_attribute_id,
object_version_number
INTO p_pricing_attribs_tbl(i).pricing_attribute_id,
p_pricing_attribs_tbl(i).object_version_number
FROM csi_i_pricing_attribs
WHERE instance_id = p_pricing_attribs_tbl(i).instance_id
AND pricing_context = p_pricing_attribs_tbl(i).pricing_context;
debug('Update Pricing_Attributes Count '||l_old_pricing_attribs_tbl.count);
p_api_name => 'update_pricing_attribs',
p_pkg_name => 'csi_pricing_attribs_pub');
csi_pricing_attribs_pub.update_pricing_attribs(
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_pricing_attribs_tbl => l_old_pricing_attribs_tbl,
p_txn_rec => l_trx_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
g_api_name := 'csi_pricing_attribs_pub.update_pricing_attribs';
debug('Pricing Attributes updated successfully.');
l_old_extended_attribs_tbl.delete;
l_new_extended_attribs_tbl.delete;
SELECT attribute_value_id,
object_version_number
INTO l_cre_ext_attrib_val_tbl(i).attribute_value_id,
l_cre_ext_attrib_val_tbl(i).object_version_number
FROM csi_iea_values
WHERE instance_id = l_cre_ext_attrib_val_tbl(i).instance_id
AND attribute_id = l_cre_ext_attrib_val_tbl(i).attribute_id;
debug('Update Extended_Attributes Count '||l_old_extended_attribs_tbl.count);
p_api_name => 'update_extended_attrib_values',
p_pkg_name => 'csi_item_instance_pub');
csi_item_instance_pub.update_extended_attrib_values(
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_ext_attrib_tbl => l_old_extended_attribs_tbl,
p_txn_rec => l_trx_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
g_api_name := 'csi_item_instance_pub.update_extended_attrib_values';
debug('Extended Attributed updated successfully.');
SELECT relationship_id,
object_version_number
INTO l_relationship_id,
l_ii_rel_obj_ver_num
FROM csi_ii_relationships
WHERE subject_id = l_tld_rec.instance_id
AND (active_end_date is null OR active_end_date >= sysdate);
l_auto_split_instances.delete;
l_cps_tbl.delete;
SELECT HCAS.party_site_id
INTO l_cre_party_site_id
FROM hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCAS
WHERE HCSU.site_use_id = p_order_line_rec.ib_install_loc_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
l_auto_split_instances.delete;
l_cps_tbl.delete;
/* Update the txn_line_detail to processed after updating IB successfully */
l_tld_rec.processing_status := 'PROCESSED';
END IF; -- end if for source_trx_flag = 'Y' -- bug 3692473.The child item instances for a Non-sourced item instance gets updated in a replacement scenario. This need to happen after the get_ii_realtion_tbl routine.
SELECT wip_entity_id
INTO l_wip_job_id
FROM wip_discrete_jobs
WHERE primary_item_id = l_order_line_rec.inv_item_id
AND organization_id = l_order_line_rec.inv_org_id
AND source_line_id = l_order_line_rec.order_line_id
AND status_type <> 7; -- excluding the cancelled wip jobs
l_upd_ii_rltns_tbl.delete;
l_cre_ii_rltns_tbl.delete;
/* update instance relationship in IB */
IF l_upd_ii_rltns_tbl.count > 0 THEN
csi_t_gen_utility_pvt.dump_api_info(
p_api_name => 'update_relationship',
p_pkg_name => 'csi_ii_relationships_pub');
csi_ii_relationships_pub.update_relationship(
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_relationship_tbl => l_upd_ii_rltns_tbl,
p_txn_rec => l_trx_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
debug('update_relationship completed successfully');
DEBUG('Error in csi_order_ship_pub.update_install_base, call to csi_process_txn_pvt.check_and_break_relation');
csi_t_txn_details_grp.update_txn_line_dtls(
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_txn_line_rec => p_txn_line_rec,
p_txn_line_detail_tbl => p_txn_line_detail_tbl,
px_txn_ii_rltns_tbl => l_upd_txn_ii_rltns_tbl,
px_txn_party_detail_tbl => l_upd_txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl => l_upd_txn_pty_acct_dtl_tbl,
px_txn_org_assgn_tbl => l_upd_txn_org_assgn_tbl,
px_txn_ext_attrib_vals_tbl => l_upd_txn_ext_attr_vals_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
g_api_name := 'csi_t_txn_details_grp.update_txn_line_dtls';
UPDATE CSI_T_TRANSACTION_LINES
set PROCESSING_STATUS = 'PROCESSED'
where transaction_line_id in
( select transaction_line_id
from CSI_T_TXN_LINE_DETAILS
where config_inst_hdr_id = p_txn_line_detail_tbl(J).config_inst_hdr_id
and config_inst_rev_num = p_txn_line_detail_tbl(J).config_inst_rev_num
and config_inst_item_id = p_txn_line_detail_tbl(J).config_inst_item_id
)
and processing_status = 'SUBMIT';
UPDATE CSI_T_TXN_LINE_DETAILS
set PROCESSING_STATUS = 'PROCESSED'
where config_inst_hdr_id = p_txn_line_detail_tbl(J).config_inst_hdr_id
and config_inst_rev_num = p_txn_line_detail_tbl(J).config_inst_rev_num
and config_inst_item_id = p_txn_line_detail_tbl(J).config_inst_item_id
and source_transaction_flag = 'Y'
and processing_status = 'SUBMIT';
ROLLBACK TO update_install_base;
ROLLBACK TO update_install_base;
END update_install_base;
SELECT 'X'
INTO l_sold_from_org_found
FROM csi_i_org_assignments
WHERE instance_id = p_txn_line_detail_tbl(i).instance_id
AND operating_unit_id = p_order_line_rec.sold_from_org_id
AND relationship_type_code = 'SOLD_FROM'
AND nvl (active_end_date, sysdate+1) > sysdate;
/* Shipped OWNER party . If it does not match then update*/
/* update the party with the Shipped OWNER Party . */
/* Also create another party rec with the 'SOLD_TO' */
/* relationship */
/*---------------------------------------------------------*/
l_pty := p_txn_party_detail_tbl.count + 1;
SELECT csi_t_party_details_s.nextval
INTO l_txn_party_detail_id
FROM sys.dual;
/* Check if the object id is being updated, if so raise error */
IF l_curr_object_id <> l_object_inst_id THEN
fnd_message.set_name('CSI','CSI_INT_OBJ_ID_NOT_ALLOW_UPD');
x_cre_txn_line_dtls_tbl.delete;
x_cre_txn_party_dtls_tbl.delete;
x_cre_txn_pty_acct_dtls_tbl.delete;
x_cre_txn_org_assgn_tbl.delete;
x_cre_txn_ext_attb_vals_tbl.delete;
x_cre_txn_systems_tbl.delete;
x_cre_txn_ii_rltns_tbl.delete;
SELECT transaction_line_id
INTO l_trx_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_id = x_cre_txn_line_rec.source_transaction_id
and source_transaction_table = x_cre_txn_line_rec.source_transaction_table
and source_transaction_type_id = x_cre_txn_line_rec.source_transaction_type_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 = x_order_shipment_tbl(i).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 = x_order_shipment_tbl(i).ib_install_loc_id; -- ship_to_org_id;
l_order_shipment_tbl.delete;
SELECT inventory_item_id,
organization_id,
picking_line_id,
abs(primary_quantity)
INTO l_inventory_item_id,
l_organization_id,
l_source_line_id,
l_source_quantity
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_txn_id;
SELECT instance_id
INTO l_mtl_txn_tbl(ret_ind).instance_id
FROM csi_item_instances
WHERE inventory_item_id = l_mtl_txn_tbl(ret_ind).inventory_item_id
AND serial_number = l_mtl_txn_tbl(ret_ind).serial_number
AND instance_usage_code = 'RETURNED';