[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inl_matches_int_s.NEXTVAL
INTO p_matches_int_tbl(i).match_int_id
FROM dual;
INSERT INTO inl_matches_int (
match_int_id , /* 01 */
adj_group_date , /* 01A*/ -- OPM Integration
group_id , /* 02 */
processing_status_code , /* 03 */
transaction_type , /* 04 */
match_type_code , /* 05 */
from_parent_table_name , /* 07 */
from_parent_table_id , /* 08 */
to_parent_table_name , /* 08 */
to_parent_table_id , /* 09 */
matched_qty , /* 11 */
matched_uom_code , /* 12 */
matched_amt , /* 13 */
matched_curr_code , /* 14 */
matched_curr_conversion_type , /* 15 */
matched_curr_conversion_date , /* 16 */
matched_curr_conversion_rate , /* 17 */
replace_estim_qty_flag , /* 18 */
charge_line_type_id , /* 19 */
party_id , /* 20 */
party_number , /* 21 */
party_site_id , /* 22 */
party_site_number , /* 23 */
tax_code , /* 24 */
nrec_tax_amt , /* 25 */
tax_amt_included_flag , /* 26 */
match_amounts_flag , /* 28 */
created_by , /* 29 */
creation_date , /* 30 */
last_updated_by , /* 31 */
last_update_date , /* 32 */
last_update_login , /* 33 */
request_id , /* 34 */
program_id , /* 35 */
program_application_id , /* 36 */
program_update_date /* 37 */
)
VALUES (
p_matches_int_tbl(i).match_int_id , /* 01 */
p_matches_int_tbl(i).adj_group_date , /* 01A*/ -- OPM Integration
l_group_id , /* 02 */
'PENDING' , /* 03 */
'CREATE' , /* 04 */
p_matches_int_tbl(i).match_type_code , /* 05 */
p_matches_int_tbl(i).from_parent_table_name , /* 07 */
p_matches_int_tbl(i).from_parent_table_id , /* 08 */
p_matches_int_tbl(i).to_parent_table_name , /* 08 */
p_matches_int_tbl(i).to_parent_table_id , /* 09 */
p_matches_int_tbl(i).matched_qty , /* 11 */
p_matches_int_tbl(i).matched_uom_code , /* 12 */
p_matches_int_tbl(i).matched_amt , /* 13 */
p_matches_int_tbl(i).matched_curr_code , /* 14 */
p_matches_int_tbl(i).matched_curr_conversion_type , /* 15 */
p_matches_int_tbl(i).matched_curr_conversion_date , /* 16 */
p_matches_int_tbl(i).matched_curr_conversion_rate , /* 17 */
p_matches_int_tbl(i).replace_estim_qty_flag , /* 18 */
p_matches_int_tbl(i).charge_line_type_id , /* 19 */
p_matches_int_tbl(i).party_id , /* 20 */
NULL , /* 21 */
p_matches_int_tbl(i).party_site_id , /* 22 */
NULL , /* 23 */
p_matches_int_tbl(i).tax_code , /* 24 */
p_matches_int_tbl(i).nrec_tax_amt , /* 25 */
p_matches_int_tbl(i).tax_amt_included_flag , /* 26 */
p_matches_int_tbl(i).match_amounts_flag , /* 28 */
fnd_global.user_id , /* 29 */
SYSDATE , /* 30 */
fnd_global.user_id , /* 31 */
SYSDATE , /* 32 */
fnd_global.login_id , /* 33 */
fnd_global.conc_request_id , /* 34 */
fnd_global.conc_program_id , /* 35 */
fnd_global.prog_appl_id , /* 36 */
SYSDATE /* 37 */
);
SELECT decode(NVL(l.corrected_inv_id, 0), 0, decode(l.line_type_lookup_code,'ITEM','ITEM', 'TAX', 'TAX', 'CHARGE'), 'CORRECTION') line_type,
decode(NVL(l.corrected_inv_id, 0), 0, NULL, decode(l.line_type_lookup_code,'ITEM','ITEM', 'TAX', 'TAX', 'CHARGE')) correction_type,
d.amount distr_amount,
d.corrected_invoice_dist_id corrected_invoice_dist_id,
d.invoice_distribution_id invoice_distribution_id,
d.invoice_id invoice_id,
d.line_type_lookup_code line_type_lookup_code,
d.parent_reversal_id parent_reversal_id,
d.dist_match_type dist_match_type,
d.charge_applicable_to_dist_id,
NVL(l.rcv_transaction_id,d.rcv_transaction_id) rcv_transaction_id, --BUG#8485279
-- l.rcv_transaction_id rcv_transaction_id,
muom.uom_code uom_code,
i.invoice_currency_code curr_code,
i.exchange_rate curr_rate,
i.exchange_rate_type curr_type,
i.exchange_date curr_date,
i.party_id party_id,
i.party_site_id party_site_id,
decode(l.line_type_lookup_code, 'TAX', l.quantity_invoiced, d.quantity_invoiced) quantity_invoiced,
l.cost_factor_id cost_factor_id,
l.tax tax_code,
decode(l.line_type_lookup_code, 'TAX', decode(d.tax_recoverable_flag,'Y',0,d.amount), NULL) nrec_tax_amt,
decode(l.line_type_lookup_code, 'TAX', 'N', NULL) tax_amt_included_flag,
d.accounting_date accounting_date, -- LCM-OPM Integration Send to OPM as transaction date
NVL(d.reversal_flag,'N') reversal_flag, --Bug#14604983
count(*) over () count_recs --Bug#14604983
FROM rcv_transactions rt,
ap_invoice_distributions d,
ap_invoices i,
ap_invoice_lines l,
mtl_units_of_measure muom
WHERE l.line_type_lookup_code IN ('ITEM', 'MISCELLANEOUS', 'FREIGHT', 'TAX')
AND d.match_status_flag = p_match_status_flag --Bug#14604983
AND rt.lcm_shipment_line_id IS NOT NULL
AND muom.unit_of_measure (+) = d.matched_uom_lookup_code
AND d.invoice_id = l.invoice_id
AND d.invoice_line_number = l.line_number
AND rt.transaction_id = NVL(l.rcv_transaction_id,d.rcv_transaction_id) --BUG#8485279
AND l.invoice_id = i.invoice_id
AND d.invoice_id = p_invoice_id
ORDER BY NVL(d.reversal_flag,'N'), invoice_distribution_id; --Bug#14604983
SELECT count(*)
INTO l_count_to_match_amt
FROM ap_invoice_lines_all l
WHERE l.invoice_id = p_invoice_id
AND l.line_type_lookup_code <> 'ITEM';
SELECT ail.cost_factor_id,
ail.line_type_lookup_code
INTO l_par_cost_factor_id,
l_par_line_type_lookup_code
FROM ap_invoice_distributions aid,
ap_invoice_lines ail
WHERE aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.invoice_distribution_id = c_distr_rec.charge_applicable_to_dist_id
AND aid.invoice_id = c_distr_rec.invoice_id;