[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1) success_count
from ap_invoice_distributions_all
where request_id = cp_conc_request_id ;
select count(1 ) failed_count
from csi_txn_errors
where source_group_ref_id=cp_conc_request_id ;
select aia.invoice_num invoice_number,
aida.distribution_line_number distribution_line_number,
aida.quantity_invoiced quantity_invoiced,
aida.base_invoice_price_variance base_invoice_price_variance,
cte.error_text error_text
from ap_invoices_all aia,
ap_invoice_distributions_all aida,
csi_txn_errors cte
where aida.invoice_id = aia.invoice_id
and cte.source_id = aida.invoice_distribution_id
and cte.source_group_ref_id = cp_conc_request_id ;
SELECT transaction_id,
inv_material_transaction_id,
transaction_quantity,
transaction_uom_code
FROM csi_transactions
WHERE source_dist_ref_id1 = p_po_dist_id
AND transaction_type_id IN (105, 112) -- po rcpt in to proj/inv
AND transaction_status_code = 'COMPLETE';
SELECT cia.fa_asset_id fa_id,
cia.fa_book_type_code fa_book_type_code,
'FA' fa_state
FROM csi_i_assets cia,
csi_item_instances_h ciih
WHERE ciih.transaction_id = p_csi_txn_id
AND cia.instance_id = ciih.instance_id
AND cia.fa_asset_id is not null
AND EXISTS (
SELECT 1 FROM fa_asset_invoices fai
WHERE fai.asset_id = cia.fa_asset_id
AND fai.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
AND fai.po_distribution_id = p_po_dist_id)
UNION
SELECT cia.fa_mass_addition_id fa_id,
cia.fa_book_type_code fa_book_type_code,
'FMA' fa_state
FROM csi_i_assets cia,
csi_item_instances_h ciih
WHERE ciih.transaction_id = p_csi_txn_id
AND cia.instance_id = ciih.instance_id
AND cia.fa_asset_id is null
AND EXISTS (
SELECT 1 FROM fa_mass_additions fma
WHERE fma.mass_addition_id = cia.fa_mass_addition_id
AND fma.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
AND fma.po_distribution_id = p_po_dist_id);
SELECT fma.mass_addition_id,
fma.description,
fma.asset_category_id,
fma.book_type_code,
fma.date_placed_in_service,
fmd.units
FROM csi_item_instances_h ciih,
fa_mass_additions fma,
fa_massadd_distributions fmd
WHERE ciih.transaction_id = p_csi_txn_id
AND fma.reviewer_comments = to_char(ciih.instance_id)
AND fma.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
AND fma.posting_status <> 'POSTED'
AND fma.book_type_code = p_book_type_code
AND fma.add_to_asset_id IS null
AND fma.split_merged_code = 'MP'
AND fmd.mass_addition_id = fma.mass_addition_id;
SELECT fnd_profile.value('CSE_FA_BOOK_TYPE_CODE')
INTO l_dflt_book_type_code
FROM sys.dual;
SELECT current_units,
description,
asset_category_id
INTO l_units,
l_asset_description,
l_asset_category_id
FROM fa_additions
WHERE asset_id = asset_rec.fa_id;
SELECT date_placed_in_service
INTO l_date_placed_in_service
FROM fa_books
WHERE asset_id = asset_rec.fa_id
AND book_type_code = asset_rec.fa_book_type_code
AND date_ineffective is NULL;
SELECT description,
asset_category_id,
book_type_code,
date_placed_in_service
INTO l_asset_description,
l_asset_category_id,
l_book_type_code,
l_date_placed_in_service
FROM fa_mass_additions
WHERE mass_addition_id = asset_rec.fa_id;
SELECT units
INTO l_units
FROM fa_massadd_distributions
WHERE mass_addition_id = asset_rec.fa_id;
l_mass_add_rec.last_update_date := sysdate;
l_mass_add_rec.last_updated_by := fnd_global.user_id;
l_mass_add_rec.last_update_login := fnd_global.login_id;
cse_asset_util_pkg.insert_mass_add(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_mass_add_rec => l_mass_add_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT aida.invoice_id,
aida.distribution_line_number,
aida.unit_price,
decode(aida.line_type_lookup_code,'NONREC_TAX', (SELECT aida1.quantity_invoiced
FROM ap_invoice_distributions_all aida1
WHERE aida1.invoice_distribution_id = aida.charge_applicable_to_dist_id),'TRV', (SELECT aida2.quantity_invoiced
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),'TIPV', (SELECT aida2.quantity_invoiced
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),'ERV', (SELECT aida2.quantity_invoiced
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced, --Modified for bug 12991094 and 13770784
aida.invoice_distribution_id,
aida.line_type_lookup_code invoice_distribution_type, --Added for bug 12991094
aida.po_distribution_id,
aia.invoice_num,
--nvl(aida.amount, 0) price_variance,
NVL(aida.base_amount,nvl(aida.amount, 0)) price_variance,
aila.inventory_item_id,
aila.org_id,
'N' as self_assessed_flag
FROM ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
WHERE aida.line_type_lookup_code IN ('IPV','FREIGHT', 'NONREC_TAX', 'TRV','TIPV','ERV') --Modified for bug 12991094,13770784 and 13647752
AND aida.posted_flag = 'Y'
AND aida.assets_addition_flag IN ('U','I','N')
--AND NVL(aida.assets_tracking_flag,'x') = 'Y'
AND aida.po_distribution_id is not null
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
-- AND aila.inventory_item_id = nvl(p_inventory_item_id, aila.inventory_item_id)--Commented for bug 12991094
AND aia.invoice_id = aida.invoice_id
AND EXISTS ( --Added for bug 12991094
SELECT '1'
FROM ap_invoice_lines_all aila2
WHERE aila2.invoice_id = aila.invoice_id
AND aila2.inventory_item_id = nvl(p_inventory_item_id, aila2.inventory_item_id))
AND EXISTS (
SELECT '1'
FROM csi_transactions ct
WHERE ct.transaction_type_id in (105, 112)
AND ct.transaction_status_code = 'COMPLETE'
AND ct.source_dist_ref_id1 = aida.po_distribution_id)
AND EXISTS (
SELECT '1'
FROM po_distributions_all pod
WHERE pod.po_distribution_id = aida.po_distribution_id
AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id))
UNION -- Added for bug 13770784
SELECT aida.invoice_id,
aida.distribution_line_number,
aida.unit_price,
decode(aida.line_type_lookup_code,'FREIGHT', (SELECT aida1.quantity_invoiced
FROM ap_invoice_distributions_all aida1
WHERE aida1.invoice_distribution_id = aida.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced,
aida.invoice_distribution_id,
aida.line_type_lookup_code invoice_distribution_type,
decode(aida.line_type_lookup_code,'FREIGHT', (SELECT aida2.po_distribution_id
FROM ap_invoice_distributions_all aida2
WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),aida.po_distribution_id) po_distribution_id,
aia.invoice_num,
--nvl(aida.amount, 0) price_variance,
NVL(aida.base_amount,nvl(aida.amount, 0)) price_variance,
aila.inventory_item_id,
aila.org_id,
'N' as self_assessed_flag
FROM ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
WHERE aida.line_type_lookup_code IN ('FREIGHT')
AND aida.posted_flag = 'Y'
AND aida.assets_addition_flag IN ('U','I','N')
AND aida.po_distribution_id is null
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.invoice_id = aida.invoice_id
AND EXISTS (
SELECT '1'
FROM ap_invoice_lines_all aila2
WHERE aila2.invoice_id = aila.invoice_id
AND aila2.inventory_item_id = nvl(p_inventory_item_id, aila2.inventory_item_id))
AND EXISTS (
SELECT '1'
FROM ap_invoice_distributions_all aida4
WHERE aida4.invoice_distribution_id = aida.charge_applicable_to_dist_id
AND EXISTS (
SELECT '1'
FROM csi_transactions ct
WHERE ct.transaction_type_id in (105, 112)
AND ct.transaction_status_code = 'COMPLETE'
AND ct.source_dist_ref_id1 = aida4.po_distribution_id)
AND EXISTS (
SELECT '1'
FROM po_distributions_all pod
WHERE pod.po_distribution_id = aida4.po_distribution_id
AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id)))
-- Cursor to get all the NONREC_TAX line types which has po_distribution_id as NULL -- Added for bug 13770784
UNION
SELECT aida.invoice_id,
aida.distribution_line_number,
aida.unit_price,
decode(aida.line_type_lookup_code,'NONREC_TAX', (SELECT aida1.quantity_invoiced
FROM ap_invoice_distributions_all aida1
WHERE aida1.invoice_distribution_id = aida.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced, --Modified for bug 12991094
aida.invoice_distribution_id,
aida.line_type_lookup_code invoice_distribution_type, --Added for bug 12991094
aida.po_distribution_id,
aia.invoice_num,
--nvl(aida.amount, 0) price_variance,
NVL(aida.base_amount,nvl(aida.amount, 0)) price_variance,
aila.inventory_item_id,
aila.org_id,
'N' as self_assessed_flag
FROM ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
WHERE aida.line_type_lookup_code IN ('NONREC_TAX')
--WHERE aida.line_type_lookup_code IN ('NONREC_TAX', 'TRV','TIPV','ERV')
AND aida.posted_flag = 'Y'
AND aida.assets_addition_flag IN ('U','I','N')
AND aida.po_distribution_id is null
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.invoice_id = aida.invoice_id
AND EXISTS ( --Added for bug 12991094
SELECT '1'
FROM ap_invoice_lines_all aila2
WHERE aila2.invoice_id = aila.invoice_id
AND aila2.inventory_item_id = nvl(p_inventory_item_id, aila2.inventory_item_id))
/*
AND EXISTS (
SELECT '1'
FROM csi_transactions ct
WHERE ct.transaction_type_id in (105, 112)
AND ct.transaction_status_code = 'COMPLETE'
AND ct.source_dist_ref_id1 = aida.po_distribution_id)
AND EXISTS (
SELECT '1'
FROM po_distributions_all pod
WHERE pod.po_distribution_id = aida.po_distribution_id
AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id));*/
SELECT asstda.invoice_id, asstda.distribution_line_number, asstda.unit_price,
decode(asstda.line_type_lookup_code,'NONREC_TAX', (SELECT asstda.quantity_invoiced
FROM AP_SELF_ASSESSED_TAX_DIST_all asstda1
WHERE asstda1.invoice_distribution_id = asstda.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced,
asstda.invoice_distribution_id,
asstda.line_type_lookup_code invoice_distribution_type,
asstda.po_distribution_id po_distribution_id,
aia.invoice_num,
--nvl(asstda.amount, 0) price_variance,
NVL(asstda.base_amount,nvl(asstda.amount, 0)) price_variance,
aila.inventory_item_id,
aila.org_id,
asstda.SELF_ASSESSED_FLAG as self_assessed_flag
FROM AP_SELF_ASSESSED_TAX_DIST_all asstda,
--ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
WHERE asstda.line_type_lookup_code IN ('NONREC_TAX')
AND asstda.posted_flag = 'Y'
AND asstda.assets_addition_flag IN ('U','I','N')
--AND asstda.po_distribution_id is null
AND aila.invoice_id = asstda.invoice_id
AND aila.line_number = asstda.invoice_line_number
AND aia.invoice_id = asstda.invoice_id
AND EXISTS
(SELECT '1'
FROM ap_invoice_lines_all aila2
WHERE aila2.invoice_id = aila.invoice_id
AND aila2.inventory_item_id = NVL(p_inventory_item_id, aila2.inventory_item_id)
);
SELECT aida.line_type_lookup_code invoice_distribution_type,
aida.invoice_distribution_id,
aida.invoice_line_number,
aia.invoice_type_lookup_code invoice_type,
nvl(aida.amount,0) base_amount,
aida.accounting_date,
aida.dist_code_combination_id inv_dist_ccid
FROM ap_invoice_distributions_all aida,
ap_invoices_all aia
WHERE aida.invoice_id = p_invoice_id
AND aida.project_id = p_project_id
AND aida.task_id = p_task_id
AND aida.line_type_lookup_code IN ('FREIGHT', 'TAX', 'NONREC_TAX','TRV','TIPV','ERV')
AND aida.posted_flag = 'Y'
AND aida.pa_addition_flag = 'N'
AND nvl(aida.reversal_flag, 'N') <> 'Y'
AND nvl(aida.tax_recoverable_flag, 'N') = 'N'
AND aia.invoice_id = aida.invoice_id
AND exists (
SELECT 'x' FROM ap_chrg_allocations_all
WHERE item_dist_id = p_item_dist_id
AND charge_dist_id = aida.invoice_distribution_id);
SELECT allocated_amount
FROM ap_chrg_allocations_all
WHERE item_dist_id = pf_item_dist_id
AND charge_dist_id = pf_charge_dist_id;
SELECT pol.item_id,
pod.destination_organization_id,
pod.variance_account_id,
poh.segment1,
poh.vendor_id
INTO l_invoice_rec.inventory_item_id,
l_invoice_rec.organization_id,
l_invoice_rec.payables_ccid,
l_invoice_rec.po_num,
l_invoice_rec.po_vendor_id
FROM po_distributions_all pod,
po_lines_all pol,
po_headers_all poh
WHERE pod.po_distribution_id = invoice_rec.po_distribution_id
AND pol.po_line_id = pod.po_line_id
AND poh.po_header_id = pol.po_header_id;
SELECT nvl(comms_nl_trackable_flag, 'N'),
nvl(asset_creation_code, '0')
INTO l_ib_trackable_flag,
l_asset_creation_code
FROM mtl_system_items
WHERE inventory_item_id = l_invoice_rec.inventory_item_id
AND organization_id = l_invoice_rec.organization_id;
UPDATE ap_invoice_distributions_all
SET assets_addition_flag = 'Y',
request_id = l_conc_request_id
WHERE invoice_distribution_id = invoice_rec.invoice_distribution_id
AND assets_addition_flag <>'Y';
UPDATE ap_self_assessed_tax_dist_all
SET assets_addition_flag = 'Y',
request_id = l_conc_request_id
WHERE invoice_distribution_id = invoice_rec.invoice_distribution_id
AND assets_addition_flag <>'Y';