The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ledger_id
INTO l_ledger_id
FROM cst_acct_info_v
WHERE organization_id = p_organization_id;
SELECT nvl(inventory_asset_flag, 'N')
INTO l_inventory_asset_flag
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT abs(primary_quantity)
INTO l_primary_quantity
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_transaction_id;
SELECT sum(nvl(base_transaction_value,0))
INTO l_base_value
FROM mtl_transaction_accounts
WHERE transaction_id = p_mtl_transaction_id
AND primary_quantity > 0;
SELECT substr(msib.description,1,80) asset_description
FROM mtl_system_items_b msib
WHERE msib.organization_id = c_org_id
AND msib.inventory_item_id = c_inv_item_id ;
SELECT ood.operating_unit
INTO l_txn_ou_context
FROM org_organization_definitions ood,
mtl_material_transactions mmt,
csi_inst_txn_details_v citdv
WHERE citdv.transaction_id = p_asset_attrib_rec.transaction_id
AND citdv.inv_material_transaction_id = mmt.transaction_id
AND mmt.organization_id = ood.organization_id
AND ROWNUM = 1;
SELECT pa.ORG_ID
INTO l_txn_ou_context
FROM csi_item_instances_h ciih,
csi_item_instances cii,
csi_transactions ct,
pa_projects_all pa
WHERE ciih.transaction_id = p_asset_attrib_rec.transaction_id
AND ciih.instance_id = p_asset_attrib_rec.instance_id
AND cii.instance_id = p_asset_attrib_rec.instance_id
AND ct.transaction_id = p_asset_attrib_rec.transaction_id
AND (ciih.new_inst_usage_code = 'IN_SERVICE' OR cii.instance_usage_code = 'IN_SERVICE')
AND ct.transaction_type_id = 108 -- PROJECT_ITEM_IN_SERVICE
AND ct.transaction_status_code = 'INTERFACED_TO_PA'
AND ct.source_header_ref_id = nvl(cii.last_pa_project_id, source_header_ref_id)
AND ct.source_line_ref_id = nvl(cii.last_pa_task_id, source_line_ref_id)
AND pa.project_id = cii.last_pa_project_id;
SELECT cod.operating_unit
INTO l_txn_ou_context
FROM rcv_transactions rt,
csi_inst_txn_details_v citdv,
org_organization_definitions cod
WHERE citdv.transaction_id = p_asset_attrib_rec.transaction_id
AND citdv.source_transaction_type = 'PO_RECEIPT_INTO_PROJECT'
AND rt.transaction_id = citdv.source_dist_ref_id2
AND rt.organization_id = cod.organization_id;
SELECT msib.asset_creation_code,
msib.serial_number_control_code,
citdv.source_transaction_date
FROM mtl_system_items_b msib,
csi_inst_txn_details_v citdv
WHERE msib.organization_id = citdv.inv_master_organization_id
AND msib.inventory_item_id = citdv.inventory_item_id
AND citdv.transaction_id = p_csi_txn_id
AND citdv.instance_id = p_inst_id;
SELECT start_date
FROM fa_book_controls fbc,
fa_calendar_periods fcp
WHERE fbc.book_type_code = l_book_type_code
AND fcp.calendar_type = fbc.deprn_calendar
AND trunc(l_transaction_date) BETWEEN fcp.start_date AND fcp.end_date;
SELECT NVL(tl_overhead_in,0)+
NVL(tl_resource_in,0)+
NVL(tl_outside_processing_in,0)+
NVL(pl_overhead_in,0)+
NVL(pl_material_in,0)+
NVL(pl_material_overhead_in,0)+
NVL(pl_resource_in,0)+
NVL(pl_outside_processing_in,0)
FROM wip_period_balances
WHERE wip_entity_id = c_wip_entity_id ;
SELECT citdv.instance_id,
citdv.inventory_item_id,
citdv.inv_organization_id,
mmt.primary_quantity
FROM csi_inst_txn_details_v citdv,
csi_i_assets cia,
mtl_material_transactions mmt
WHERE citdv.source_header_ref_id=l_wip_entity_id
AND cia.instance_id=citdv.instance_id
AND citdv.inv_material_transaction_id=mmt.transaction_id ;
SELECT accounting_flex_structure
FROM fa_book_controls
WHERE book_type_code = c_book_type_code ;
SELECT fifs.segment_num
FROM fnd_id_flex_segments fifs,
fnd_segment_attribute_values fsav
WHERE fifs.application_column_name = fsav.application_column_name
AND fifs.id_flex_num = fsav.id_flex_num
AND fifs.id_flex_code = fsav.id_flex_code
AND fifs.application_id = fsav.application_id
AND fsav.application_id = 101 --GL
AND fsav.id_flex_code = 'GL#'
AND fsav.id_flex_num = l_flex_num
AND fsav.segment_attribute_type = 'GL_ACCOUNT'
AND fsav.attribute_value = 'Y';
SELECT asset_clearing_account_ccid ,
deprn_expense_acct
FROM fa_category_books
WHERE book_type_code = p_book_type_code
AND category_id = p_category_id ;
SELECT application_short_name
FROM fnd_application
WHERE application_id = 101 ; --GL
SELECT concatenated_segments
FROM fa_categories_b_kfv
WHERE category_id = l_category_id ;
SELECT pda.code_combination_id
FROM po_distributions_all pda,
rcv_transactions rt,
csi_transactions ct
WHERE pda.po_distribution_id = rt.po_distribution_id
AND rt.transaction_id = ct.source_dist_ref_id2
AND ct.transaction_id = c_transaction_id
AND ct.transaction_type_id = 105 -- rec in to project
UNION
SELECT pda.variance_account_id
FROM po_distributions_all pda,
ap_invoice_distributions_all aida ,
csi_transactions ct
WHERE pda.po_distribution_id = aida.po_distribution_id
AND aida.invoice_distribution_id = ct.source_dist_ref_id2
AND ct.transaction_id = c_transaction_id
AND ct.transaction_type_id = 102; -- ap ipv
SELECT xal.code_combination_id
FROM mtl_transaction_accounts mta,
xla_distribution_links xdl,
xla_ae_lines xal,
xla_ae_headers xah
WHERE mta.transaction_id = p_mtl_txn_id
AND mta.accounting_line_type = p_acct_line_type
AND nvl(mta.cost_element_id,1) = p_cost_element_id
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xah.ae_header_id = xal.ae_header_id
AND xah.ledger_id = p_ledger_id;
SELECT xlael.code_combination_id
FROM xla_transaction_entities xlte,
xla_ae_headers xlaeh,
xla_ae_lines xlael,
xla_distribution_links xdl,
mtl_transaction_accounts mta
WHERE xlte.application_id = l_application_id
AND xlte.entity_code = l_entity_code
AND xlte.source_id_int_1 = p_mtl_txn_id
AND xlaeh.ledger_id = p_ledger_id
AND xlaeh.application_id = xlte.application_id
AND xlaeh.entity_id = xlte.entity_id
AND xlael.application_id = xlte.application_id
AND xlael.ae_header_id = xlaeh.ae_header_id
AND xlael.accounting_class_code = p_acct_class_code
AND xdl.ae_header_id = xlael.ae_header_id
AND xdl.ae_line_num = xlael.ae_line_num
AND mta.inv_sub_ledger_id = xdl.source_distribution_id_num_1
AND mta.cost_element_id = 1;
SELECT NVL(source_dist_ref_id2,transaction_id)
FROM csi_transactions
WHERE transaction_id = c_txn_id ;
SELECT asset_clearing_account_ccid
FROM fa_category_books
WHERE book_type_code = c_book_type_code
AND category_id = c_category_id ;
SELECT transaction_type_id,
inv_material_transaction_id,
source_dist_ref_id1,
source_line_ref_id
INTO l_csi_txn_type_id,
l_mtl_txn_id,
l_po_distribution_id,
l_line_id
FROM csi_transactions
WHERE transaction_id = p_asset_attrib_rec.transaction_id;
SELECT nvl(msi.inventory_asset_flag, 'N'),
mmt.organization_id,
subinventory_code,
transaction_source_id -- Added for bug#16365883
INTO l_inventory_asset_flag,
l_organization_id,
l_subinventory_code,
l_txn_source_id -- Added for bug#16365883
FROM mtl_material_transactions mmt,
mtl_system_items msi
WHERE mmt.transaction_id = l_mtl_txn_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id;
SELECT decode(asset_inventory,2,'Y','N') --1=Asset Subinventory 2=Expense subinventory
INTO l_exp_subinv_flag
FROM mtl_secondary_inventories
WHERE organization_id = l_organization_id
AND secondary_inventory_name = l_subinventory_code;
SELECT ctld.sub_type_id
INTO l_sub_type_id
FROM csi_t_txn_line_details ctld,csi_t_transaction_lines ctl
WHERE ctld.transaction_line_id = ctl.transaction_line_id
AND ctl. source_transaction_id = l_line_id
AND ROWNUM=1;
SELECT nvl(citt.sub_type_id, -1)
INTO l_sub_type_id
FROM csi_ib_txn_types citt,
csi_source_ib_types csit
WHERE csit.transaction_type_id = 51
AND csit.default_flag = 'Y'
and citt.sub_type_id = csit.sub_type_id;
SELECT transaction_id,'Y' INTO l_cogs_mtl_txn_id,l_cogs_recognized FROM mtl_material_transactions WHERE transaction_type_id = 10008 AND transaction_source_id = l_txn_source_id;
SELECT material_account
INTO l_asset_acct_ccid
FROM mtl_parameters mp
WHERE mp.organization_id = l_organization_id;
SELECT SUM(DECODE(fr.status,'PENDING', NVL(fr.cost_retired,0)*(-1),
NVL(fr.cost_retired,0))) cost,
SUM(DECODE(fr.status,'PENDING', NVL(fr.units,0)*(-1),
NVL(fr.units,0))) units
FROM fa_retirements fr ,
fa_distribution_history fdh
WHERE fr.status IN ('PENDING','REINSTATE')
AND fr.retirement_id = fdh.retirement_id
AND fdh.distribution_id = c_distribution_id ;
SELECT SUM(NVL(cost_retired,0)*(-1)) cost
,SUM(NVL(units,0)*(-1)) units
FROM fa_mass_ext_retirements
WHERE review_status = 'POST'
AND book_type_code = p_asset_query_rec.book_type_code
AND asset_id = p_asset_query_rec.asset_id ;
SELECT SUM(NVL(fma.fixed_assets_cost,0)) cost ,
SUM(fma.fixed_assets_units) total_units ,
fma.mass_addition_id
FROM fa_mass_additions fma
,fa_massadd_distributions fmd
WHERE fmd.mass_addition_id = fma.mass_addition_id
AND fma.posting_status = 'POST'
AND fma.book_type_code = p_asset_query_rec.book_type_code
AND fma.add_to_asset_id = p_asset_query_rec.asset_id
GROUP BY fma.mass_addition_id ;
SELECT units location_units
FROM fa_massadd_distributions
WHERE NVL(deprn_expense_ccid, -1)=
NVL(p_asset_query_rec.deprn_expense_ccid,NVL(deprn_expense_ccid,-1))
AND NVL(employee_id, -1)=
NVL(p_asset_query_rec.employee_id,NVL(employee_id,-1))
AND location_id = NVL(p_asset_query_rec.location_id,NVL(location_id,-1))
AND mass_addition_id = c_mass_addition_id ;
SELECT DECODE(msib.asset_creation_code,'1','N','Y')
FROM mtl_system_items_b msib
,csi_item_instances cii
,csi_i_assets cia
WHERE msib.organization_id = cii.inv_master_organization_id
AND msib.inventory_item_id = cii.inventory_item_id
AND cii.instance_id = cia.instance_id
AND cia.instance_asset_id = c_instance_asset_id;*/
SELECT DECODE(msib.asset_creation_code,'1','N','Y')
FROM mtl_system_items_b msib
,csi_i_assets cia
WHERE msib.organization_id = c_inv_org_id
AND msib.inventory_item_id = c_inv_item_id
AND cia.instance_id = c_inst_id
AND cia.instance_asset_id = c_instance_asset_id;
SELECT NVL(msib.asset_creation_code,'~')
FROM mtl_system_items_b msib
WHERE msib.organization_id = c_inv_org_id
AND msib.inventory_item_id = c_inv_item_id;
SELECT mmt.primary_quantity
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = c_mmt_id
AND mmt.inventory_item_id = c_inv_id ;
SELECT ctst.src_change_owner
FROM csi_t_txn_line_details cttld,
csi_ib_txn_types ctst
WHERE cttld.source_transaction_flag = 'Y'
AND cttld.csi_transaction_id = c_transaction_id
AND cttld.sub_type_id = ctst.sub_type_id;
SELECT source_line_ref_id
INTO l_line_id
FROM csi_transactions
WHERE transaction_id = p_asset_attrib_rec.transaction_id;
SELECT 'Y' INTO l_ship_only FROM oe_order_lines_all WHERE Nvl(shipped_quantity,0) >0 AND Nvl(invoiced_quantity,0) = 0 AND (invoice_interface_status_code IS NULL OR invoice_interface_status_code = 'NOT_ELIGIBLE' ) AND line_id = l_line_id;
SELECT ctld.sub_type_id
INTO l_sub_type_id
FROM csi_t_txn_line_details ctld,csi_t_transaction_lines ctl
WHERE ctld.transaction_line_id = ctl.transaction_line_id
AND ctl. source_transaction_id = l_line_id
AND ROWNUM=1;
SELECT nvl(citt.sub_type_id, -1)
INTO l_sub_type_id
FROM csi_ib_txn_types citt,
csi_source_ib_types csit
WHERE csit.transaction_type_id = 51
AND csit.default_flag = 'Y'
and citt.sub_type_id = csit.sub_type_id;
SELECT 'N' from mtl_secondary_inventories msi , csi_item_instances cii WHERE msi.asset_inventory=1 AND msi.secondary_inventory_name=l_inv_subinventory_name AND msi.organization_id=cii.inv_organization_id AND msi.organization_id= l_inv_organization_id AND cii.instance_id=l_instance_id;
SELECT 'N' from csi_i_assets WHERE instance_id=l_instance_id AND (active_end_date >SYSDATE OR active_end_date IS NULL );
SELECT 'N' from csi_item_instances
WHERE (pa_project_id IS NOT NULL OR
last_pa_project_id IS NOT NULL)
AND instance_id =l_instance_id
AND (active_end_date >SYSDATE OR active_end_date IS NULL );
PROCEDURE insert_mass_add(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_mass_add_rec IN OUT NOCOPY fa_mass_additions%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
x_error_msg VARCHAR2(2000);
l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.insert_mass_add' ;
Select dp.period_name,
bc.last_deprn_run_date,
bc.deprn_calendar
from fa_deprn_periods dp,
fa_deprn_periods dp2,
fa_deprn_periods dp3,
fa_book_controls bc
where dp.book_type_code =c_book_type_code
and dp.period_close_date is null
and dp2.book_type_code(+) = bc.distribution_source_book
and dp2.period_counter(+) = bc.last_mass_copy_period_counter
and dp3.book_type_code(+) = bc.book_type_code
and dp3.period_counter(+) = bc.last_purge_period_counter
and bc.book_type_code = c_book_type_code;
SELECT END_DATE
FROM FA_CALENDAR_PERIODS FAP,
fa_book_controls FAC
WHERE FAP.calendAr_type=c_calendar_type
AND FAC.BOOk_TYPE_CODE =c_book_type_code
AND FAP.PERIOD_NAME=c_period_name;
SELECT fa_mass_additions_s.nextval
INTO p_mass_add_rec.mass_addition_id
FROM SYS.DUAL ;
debug('inside api insert_mass_add');
SELECT ROUND(p_mass_add_rec.fixed_assets_cost,2) ,
ROUND(p_mass_add_rec.payables_cost,2),
ROUND(p_mass_add_rec.unrevalued_cost,2)
INTO l_fixed_assets_cost,
l_payables_cost,
l_unrevalued_cost
FROM SYS.dual ;
INSERT INTO fa_mass_additions(
mass_addition_id,
asset_number,
tag_number,
description,
asset_category_id,
manufacturer_name,
serial_number,
model_number,
book_type_code,
date_placed_in_service,
fixed_assets_cost,
payables_units,
fixed_assets_units,
payables_code_combination_id,
expense_code_combination_id,
location_id,
assigned_to ,
feeder_system_name,
create_batch_date,
create_batch_id,
last_update_date,
last_updated_by,
reviewer_comments,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
posting_status,
queue_name,
invoice_date,
invoice_created_by,
invoice_updated_by ,
payables_cost,
invoice_id,
payables_batch_name,
depreciate_flag,
parent_mass_addition_id ,
parent_asset_id,
split_merged_code,
ap_distribution_line_number,
post_batch_id,
add_to_asset_id,
amortize_flag,
new_master_flag,
asset_key_ccid,
asset_type,
deprn_reserve,
ytd_deprn,
beginning_nbv,
created_by,
creation_date,
last_update_login,
salvage_value,
accounting_date,
unit_of_measure,
unrevalued_cost,
ytd_reval_deprn_expense,
merged_code,
split_code,
merge_parent_mass_additions_id,
split_parent_mass_additions_id,
project_asset_line_id,
project_id,
task_id,
sum_units,
dist_name,
inventorial,
short_fiscal_year_flag,
conversion_date,
original_deprn_start_date,
group_asset_id,
cua_parent_hierarchy_id,
units_to_adjust,
bonus_ytd_deprn,
bonus_deprn_reserve,
amortize_nbv_flag,
amortization_start_date,
attribute14,
TRANSACTION_DATE,
TRANSACTION_TYPE_CODE,
po_distribution_id,
CONTEXT,
ATTRIBUTE_CATEGORY_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
-- ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30)
VALUES(
p_mass_add_rec.mass_addition_id ,
p_mass_add_rec.asset_number,
p_mass_add_rec.tag_number,
p_mass_add_rec.description,
p_mass_add_rec.asset_category_id,
p_mass_add_rec.manufacturer_name,
p_mass_add_rec.serial_number,
p_mass_add_rec.model_number,
p_mass_add_rec.book_type_code,
p_mass_add_rec.date_placed_in_service,
l_fixed_assets_cost,
p_mass_add_rec.payables_units,
p_mass_add_rec.fixed_assets_units,
p_mass_add_rec.payables_code_combination_id,
p_mass_add_rec.expense_code_combination_id,
p_mass_add_rec.location_id,
p_mass_add_rec.assigned_to ,
p_mass_add_rec.feeder_system_name,
p_mass_add_rec.create_batch_date,
p_mass_add_rec.create_batch_id,
p_mass_add_rec.last_update_date,
p_mass_add_rec.last_updated_by,
p_mass_add_rec.reviewer_comments,
p_mass_add_rec.invoice_number,
p_mass_add_rec.vendor_number,
p_mass_add_rec.po_vendor_id,
p_mass_add_rec.po_number,
p_mass_add_rec.posting_status,
p_mass_add_rec.queue_name,
p_mass_add_rec.invoice_date,
p_mass_add_rec.invoice_created_by,
p_mass_add_rec.invoice_updated_by ,
l_payables_cost,
p_mass_add_rec.invoice_id,
p_mass_add_rec.payables_batch_name,
p_mass_add_rec.depreciate_flag,
p_mass_add_rec.parent_mass_addition_id ,
p_mass_add_rec.parent_asset_id,
p_mass_add_rec.split_merged_code,
p_mass_add_rec.ap_distribution_line_number,
p_mass_add_rec.post_batch_id,
p_mass_add_rec.add_to_asset_id,
p_mass_add_rec.amortize_flag,
p_mass_add_rec.new_master_flag,
p_mass_add_rec.asset_key_ccid,
p_mass_add_rec.asset_type,
p_mass_add_rec.deprn_reserve,
p_mass_add_rec.ytd_deprn,
p_mass_add_rec.beginning_nbv,
p_mass_add_rec.created_by,
p_mass_add_rec.creation_date,
p_mass_add_rec.last_update_login,
p_mass_add_rec.salvage_value,
p_mass_add_rec.accounting_date,
p_mass_add_rec.unit_of_measure,
l_unrevalued_cost,
p_mass_add_rec.ytd_reval_deprn_expense,
p_mass_add_rec.merged_code,
p_mass_add_rec.split_code,
p_mass_add_rec.merge_parent_mass_additions_id,
p_mass_add_rec.split_parent_mass_additions_id,
p_mass_add_rec.project_asset_line_id,
p_mass_add_rec.project_id,
p_mass_add_rec.task_id,
p_mass_add_rec.sum_units,
p_mass_add_rec.dist_name,
p_mass_add_rec.inventorial,
p_mass_add_rec.short_fiscal_year_flag,
p_mass_add_rec.conversion_date,
p_mass_add_rec.original_deprn_start_date,
p_mass_add_rec.group_asset_id,
p_mass_add_rec.cua_parent_hierarchy_id,
p_mass_add_rec.units_to_adjust,
p_mass_add_rec.bonus_ytd_deprn,
p_mass_add_rec.bonus_deprn_reserve,
p_mass_add_rec.amortize_nbv_flag,
p_mass_add_rec.amortization_start_date ,
p_mass_add_rec.attribute14,
p_mass_add_rec.TRANSACTION_date,
p_mass_add_rec.TRANSACTION_TYPE_CODE,
p_mass_add_rec.po_distribution_id,
p_mass_add_rec.CONTEXT,
p_mass_add_rec.ATTRIBUTE_CATEGORY_CODE,
p_mass_add_rec.ATTRIBUTE1,
p_mass_add_rec.ATTRIBUTE2,
p_mass_add_rec.ATTRIBUTE3,
p_mass_add_rec.ATTRIBUTE4,
p_mass_add_rec.ATTRIBUTE5,
p_mass_add_rec.ATTRIBUTE6,
p_mass_add_rec.ATTRIBUTE7,
p_mass_add_rec.ATTRIBUTE8,
p_mass_add_rec.ATTRIBUTE9,
p_mass_add_rec.ATTRIBUTE10,
p_mass_add_rec.ATTRIBUTE11,
p_mass_add_rec.ATTRIBUTE12,
p_mass_add_rec.ATTRIBUTE13,
-- p_mass_add_rec.ATTRIBUTE14,
p_mass_add_rec.ATTRIBUTE15,
p_mass_add_rec.ATTRIBUTE16,
p_mass_add_rec.ATTRIBUTE17,
p_mass_add_rec.ATTRIBUTE18,
p_mass_add_rec.ATTRIBUTE19,
p_mass_add_rec.ATTRIBUTE20,
p_mass_add_rec.ATTRIBUTE21,
p_mass_add_rec.ATTRIBUTE22,
p_mass_add_rec.ATTRIBUTE23,
p_mass_add_rec.ATTRIBUTE24,
p_mass_add_rec.ATTRIBUTE25,
p_mass_add_rec.ATTRIBUTE26,
p_mass_add_rec.ATTRIBUTE27,
p_mass_add_rec.ATTRIBUTE28,
p_mass_add_rec.ATTRIBUTE29,
p_mass_add_rec.ATTRIBUTE30
);
END insert_mass_add;
SELECT ct1.transaction_id,
ct1.transaction_date
FROM csi_item_instances_h ciih1
,csi_transactions ct1
,csi_txn_types ctt
WHERE ct1.transaction_id = ciih1.transaction_id
AND ct1.transaction_type_id = ctt.transaction_type_id
AND ct1.transaction_id <> p_asset_attrib_rec.transaction_id
----AS these transactions cannot be processed without the receipts,
---these don't qualify for this validation.
AND ctt.source_transaction_type NOT IN ('INTERORG_TRANS_SHIPMENT',
'ISO_SHIPMENT')
AND ciih1.instance_id IN (
SELECT ciih.instance_id
FROM csi_item_instances_h ciih,
csi_transactions ct
WHERE ct.transaction_id = p_asset_attrib_rec.transaction_id
AND ciih.transaction_id = ct.transaction_id)
AND ct1.transaction_status_code = 'PENDING' ;
SELECT ct.transaction_date FROM csi_transactions ct WHERE ct.transaction_id = p_transaction_id ;*/
SELECT cal.fa_location_id fa_location_id
FROM csi_a_locations cal
WHERE cal.location_id = l_location_id
AND cal.location_table = 'HR_LOCATIONS'
AND l_location_type_code = 'INVENTORY'
AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate
UNION
SELECT cal.fa_location_id fa_location_id
FROM csi_a_locations cal
WHERE location_id = l_location_id
AND l_location_type_code IN ('HZ_LOCATIONS', 'IN_TRANSIT', 'PROJECT') -- Modified for bug 8651868
AND cal.location_table IN ('HZ_LOCATIONS','LOCATION_CODES','HR_LOCATIONS')
AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate
UNION
SELECT cal.fa_location_id fa_location_id
FROM csi_a_locations cal
WHERE location_id = l_location_id
AND l_location_type_code IN ('HR_LOCATIONS','INTERNAL_SITE')
AND cal.location_table IN ('HR_LOCATIONS')
AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate
UNION
SELECT cal.fa_location_id fa_location_id
FROM csi_a_locations cal,
hz_party_sites hzps
WHERE hzps.location_id = cal.location_id
AND hzps.party_site_id = l_location_id -- Modified for bug 4149685
AND l_location_type_code = 'HZ_PARTY_SITES'
AND cal.location_table IN ('HZ_LOCATIONS','LOCATION_CODES')
AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate ;
SELECT creation_date
INTO l_time_stamp
FROM csi_item_instances_h
WHERE transaction_id = p_inst_loc_rec.transaction_id
AND instance_id = p_inst_loc_rec.instance_id;
SELECT pla.unit_price ---Unit Price for ONE UOM
,pla_muom.uom_code pla_uom_code
,rcv_muom.uom_code primary_uom_code
FROM rcv_transactions rt
,po_lines_all pla
,mtl_units_of_measure pla_muom
,mtl_units_of_measure rcv_muom
WHERE rt.transaction_id = p_source_txn_id
AND rt.po_line_id = pla.po_line_id
AND pla.unit_meas_lookup_code = pla_muom.unit_of_measure
AND rt.primary_unit_of_measure = rcv_muom.unit_of_measure ;
SELECT 'N'
FROM fa_transaction_headers th,
fa_book_controls bc,
fa_deprn_periods dp
WHERE th.asset_id = p_asset_id
AND th.book_type_code = p_book_type_code
AND bc.book_type_code = th.book_type_code
AND th.transaction_type_code||''
= DECODE(bc.book_class,'CORPORATE','TRANSFER IN', 'ADDITION')
AND th.date_effective BETWEEN dp.period_open_date
AND nvl(dp.period_close_date,sysdate)
AND dp.book_type_code = th.book_type_code
AND dp.period_close_date is NULL ;
SELECT rcv_sub_ledger_id
INTO l_sub_ledger_id
from rcv_receiving_sub_ledger
WHERE rcv_transaction_id = p_rcv_transaction_id
AND accounting_line_type = 'Charge';
SELECT fcp.period_name
INTO l_period_name
FROM fa_book_controls fbc,
fa_calendar_periods fcp
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fbc.deprn_calendar
AND p_dpis BETWEEN fcp.start_date AND fcp.end_date;
SELECT xlael.code_combination_id
FROM xla_transaction_entities xlte,
xla_ae_headers xlaeh,
xla_ae_lines xlael
WHERE xlte.application_id = l_application_id
AND xlte.entity_code = l_entity_code
AND xlte.source_id_int_1 = p_invoice_id
AND xlaeh.ledger_id = l_ledger_id
AND xlaeh.application_id = xlte.application_id
AND xlaeh.entity_id = xlte.entity_id
and xlael.application_id = xlte.application_id
AND xlael.ae_header_id = xlaeh.ae_header_id
AND xlael.accounting_class_code = l_acct_class_code;
SELECT set_of_books_id
INTO l_ledger_id
FROM ap_system_parameters;
SELECT xlael.code_combination_id
FROM xla_transaction_entities xlte,
xla_ae_headers xlaeh,
xla_ae_lines xlael
WHERE xlte.application_id = l_application_id
AND xlte.entity_code = l_entity_code
AND xlte.source_id_int_1 = p_invoice_id
AND xlaeh.ledger_id = l_ledger_id
AND xlaeh.application_id = xlte.application_id
AND xlaeh.entity_id = xlte.entity_id
and xlael.application_id = xlte.application_id
AND xlael.ae_header_id = xlaeh.ae_header_id
AND xlael.accounting_class_code = l_acct_class_code;*/
SELECT xlael.code_combination_id
FROM xla_transaction_entities xlte,
xla_ae_headers xlaeh,
xla_ae_lines xlael,
xla_distribution_links xdl,
ap_invoice_distributions_all aida
WHERE xlte.application_id = l_application_id
AND xlte.entity_code = l_entity_code
AND xlte.source_id_int_1 = p_invoice_id
AND xlaeh.ledger_id = l_ledger_id
AND aida.INVOICE_DISTRIBUTION_ID = p_invoice_distribution_id
AND aida.invoice_id =p_invoice_id
AND xlaeh.application_id = xlte.application_id
AND xlaeh.entity_id = xlte.entity_id
and xlael.application_id = xlte.application_id
AND xlael.ae_header_id = xlaeh.ae_header_id
AND xlael.accounting_class_code = l_acct_class_code
AND aida.INVOICE_DISTRIBUTION_ID = xdl.source_distribution_id_num_1
AND xdl.ae_header_id = xlael.AE_HEADER_ID
and xdl.ae_line_num = xlael.ae_line_num;
SELECT xlael.code_combination_id
FROM xla_transaction_entities xlte,
xla_ae_headers xlaeh,
xla_ae_lines xlael,
xla_distribution_links xdl,
AP_SELF_ASSESSED_TAX_DIST_all aida
WHERE xlte.application_id = l_application_id
AND xlte.entity_code = l_entity_code
AND xlte.source_id_int_1 = p_invoice_id
AND xlaeh.ledger_id = l_ledger_id
AND aida.INVOICE_DISTRIBUTION_ID = p_invoice_distribution_id
AND aida.invoice_id =p_invoice_id
AND xlaeh.application_id = xlte.application_id
AND xlaeh.entity_id = xlte.entity_id
and xlael.application_id = xlte.application_id
AND xlael.ae_header_id = xlaeh.ae_header_id
AND xlael.accounting_class_code = 'SELF_ASSESSED_TAX'
AND aida.INVOICE_DISTRIBUTION_ID = xdl.source_distribution_id_num_1
AND xdl.ae_header_id = xlael.AE_HEADER_ID
and xdl.ae_line_num = xlael.ae_line_num;
SELECT set_of_books_id
INTO l_ledger_id
FROM ap_system_parameters;
SELECT asset_key_flex_structure
INTO l_asset_key_flex_struct
FROM fa_system_controls;