[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'EI' ei_stage,
ei.expenditure_item_id,
ei.quantity,
ei.attribute6,
ei.attribute7,
ei.attribute8,
ei.attribute9,
ei.attribute10,
ei.orig_transaction_reference,
substr(orig_transaction_reference, 1,(instr(orig_transaction_reference, '-', 1, 2)-1)) instance_id,
ei.transaction_source
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei
WHERE cdl.project_id = p_project_id
AND cdl.task_id = p_task_id
AND cdl.org_id = p_org_id
AND cdl.project_id = ei.project_id
AND cdl.task_id = ei.task_id
AND cdl.org_id = ei.org_id
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND ei.transaction_source IN ( 'CSE_PO_RECEIPT', 'CSE_IPV_ADJUSTMENT')
AND (ei.transaction_source, cdl.system_reference3) IN (
SELECT 'CSE_PO_RECEIPT' txn_source , p_po_distribution_id sys_reference3
FROM dual
UNION ALL
SELECT 'CSE_IPV_ADJUSTMENT' txn_source , distribution_line_number
sys_reference3
FROM ap_invoice_distributions_all aida
WHERE po_distribution_id = p_po_distribution_id
and aida.invoice_id = cdl.system_reference2)
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
UNION ALL
SELECT 'TI' ei_stage,
null expenditure_item_id,
ti.quantity,
ti.attribute6,
ti.attribute7,
ti.attribute8,
ti.attribute9,
ti.attribute10,
ti.orig_transaction_reference,
substr(orig_transaction_reference, 1,(instr(orig_transaction_reference, '-', 1, 2)-1)) instance_id,
ti.transaction_source
FROM pa_transaction_interface_all ti
WHERE ti.transaction_source IN ( 'CSE_PO_RECEIPT', 'CSE_IPV_ADJUSTMENT')
AND (ti.transaction_source, cdl_system_reference3) IN ( SELECT
'CSE_PO_RECEIPT' txn_source , p_po_distribution_id sys_reference3
FROM dual
UNION ALL
SELECT 'CSE_IPV_ADJUSTMENT' txn_source , distribution_line_number
sys_reference3
FROM ap_invoice_distributions_all aida
WHERE po_distribution_id = p_po_distribution_id
and aida.invoice_id = ti.cdl_system_reference2)
ORDER BY instance_id, transaction_source desc;
l_nl_pa_tbl(l_ind).last_update_date := sysdate;
l_nl_pa_tbl(l_ind).last_updated_by := fnd_global.user_id;
SELECT aia.invoice_type_lookup_code invoice_type,
aida.line_type_lookup_code invoice_distribution_type,
aida.invoice_distribution_id,
pda.po_header_id,
pda.po_line_id,
pda.po_distribution_id,
pda.project_id,
pda.task_id,
pda.expenditure_item_date,
pda.expenditure_type,
pda.expenditure_organization_id exp_org_id,
pda.destination_organization_id dest_org_id,
aida.org_id,
aida.accounting_date,
aida.invoice_id,
aida.distribution_line_number,
nvl(aida.amount,0) base_amount,
aila.line_number,
aila.quantity_invoiced,
aida.unit_price,
aida.price_var_code_combination_id inv_dist_ccid
FROM po_distributions_all pda,
ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
WHERE EXISTS (
SELECT '1' FROM csi_transactions ct
WHERE ct.transaction_type_id = 105
AND ct.transaction_status_code = 'COMPLETE'
AND ct.source_dist_ref_id1 = pda.po_distribution_id)
AND pda.project_id = nvl(p_project_id, pda.project_id)
AND pda.task_id = nvl(p_task_id, pda.task_id)
AND aida.po_distribution_id = pda.po_distribution_id
and aida.line_type_lookup_code = 'IPV'
AND aida.posted_flag = 'Y'
AND aida.pa_addition_flag = 'N'
AND nvl(aida.reversal_flag, 'N') <> 'Y'
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.invoice_id = aida.invoice_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')
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;
l_ap_pa_tbl.delete;
SELECT accts_pay_code_combination_id
INTO l_ap_pa_tbl(1).acct_pay_ccid
FROM ap_system_parameters_all
WHERE org_id = ap_inv_rec.org_id;
SELECT item_id
INTO l_ap_pa_tbl(1).inventory_item_id
FROM po_lines_all
WHERE po_line_id = ap_inv_rec.po_line_id;
SELECT concatenated_segments
INTO l_ap_pa_tbl(1).item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_ap_pa_tbl(1).inventory_item_id
AND organization_id = ap_inv_rec.dest_org_id;
SELECT name
INTO l_ap_pa_tbl(1).exp_org_name
FROM hr_all_organization_units
WHERE organization_id = ap_inv_rec.exp_org_id;
SELECT segment1
INTO l_ap_pa_tbl(1).project_num
FROM pa_projects_all
WHERE project_id = ap_inv_rec.project_id;
SELECT task_number
INTO l_ap_pa_tbl(1).task_num
FROM pa_tasks
WHERE project_id = ap_inv_rec.project_id
AND task_id = ap_inv_rec.task_id;
SELECT vendor_id
INTO l_ap_pa_tbl(1).vendor_id
FROM ap_invoices_all
WHERE invoice_id = ap_inv_rec.invoice_id;
SELECT segment1
INTO l_ap_pa_tbl(1).vendor_num
FROM po_vendors
WHERE vendor_id = l_ap_pa_tbl(1).vendor_id;
UPDATE ap_invoice_distributions_all
SET pa_addition_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id
WHERE invoice_distribution_id = ap_inv_rec.invoice_distribution_id;