[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 */
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_amount_int_id , /* 27 */
created_by , /* 28 */
creation_date , /* 29 */
last_updated_by , /* 30 */
last_update_date , /* 31 */
last_update_login , /* 32 */
request_id , /* 33 */
program_id , /* 34 */
program_application_id , /* 35 */
program_update_date /* 36 */
)
VALUES (
p_matches_int_tbl(i).match_int_id , /* 01 */
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 */
l_match_amount_int_id , /* 27 */
fnd_global.user_id , /* 28 */
SYSDATE , /* 29 */
fnd_global.user_id , /* 30 */
SYSDATE , /* 31 */
fnd_global.login_id , /* 32 */
fnd_global.conc_request_id , /* 33 */
fnd_global.conc_program_id , /* 34 */
fnd_global.prog_appl_id , /* 35 */
SYSDATE /* 36 */
);
SELECT SUM(d.amount) amount,
MInt.match_type_code match_type_code,
NULL tax_code,
NULL tax_amt_included_flag,
l.cost_factor_id cost_factor_id,
i.invoice_currency_code curr_code,
i.exchange_rate curr_rate,
i.exchange_rate_type curr_type,
i.exchange_date curr_date,
NULL tax_cost_factor_id, --Tax Prorate should be the same basis of its charge
'CHARGE' amount_type
FROM inl_matches_int MInt,
ap_invoices i,
ap_invoice_lines l,
ap_invoice_distributions d
WHERE MInt.from_parent_table_name = 'AP_INVOICE_DISTRIBUTIONS'
AND MInt.from_parent_table_id = d.invoice_distribution_id
AND MInt.processing_status_code = 'PENDING'
AND d.invoice_id = p_invoice_id
AND d.invoice_id = i.invoice_id
AND d.invoice_id = l.invoice_id
AND l.line_number = d.invoice_line_number
AND l.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
GROUP BY MInt.match_type_code,
l.cost_factor_id,
i.invoice_currency_code,
i.exchange_rate,
i.exchange_rate_type,
i.exchange_date
UNION ALL
SELECT SUM(d.amount) amount,
MInt.match_type_code,
MInt.tax_code,
MInt.tax_amt_included_flag,
l.cost_factor_id cost_factor_id,
i.invoice_currency_code curr_code,
i.exchange_rate curr_rate,
i.exchange_rate_type curr_type,
i.exchange_date curr_date,
l_prorat.cost_factor_id tax_cost_factor_id, --Tax Prorate should be the same basis of its charge
'TAX' amount_type
FROM inl_matches_int MInt,
ap_invoices i,
ap_invoice_lines l,
ap_invoice_distributions d,
ap_invoice_distributions d_prorat,
ap_invoice_lines l_prorat
WHERE MInt.from_parent_table_name = 'AP_INVOICE_DISTRIBUTIONS'
AND MInt.from_parent_table_id = d.invoice_distribution_id
AND MInt.processing_status_code = 'PENDING'
AND d.invoice_id = p_invoice_id
AND d.invoice_id = i.invoice_id
AND d.invoice_id = l.invoice_id
AND l.line_number = d.invoice_line_number
AND l.line_type_lookup_code = 'TAX'
AND d_prorat.invoice_distribution_id = d.charge_applicable_to_dist_id
AND d_prorat.invoice_id = l_prorat.invoice_id
AND l_prorat.line_number = d_prorat.invoice_line_number
AND l_prorat.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
GROUP BY MInt.match_type_code,
MInt.tax_code,
MInt.tax_amt_included_flag,
l.cost_factor_id,
i.invoice_currency_code,
i.exchange_rate,
i.exchange_rate_type,
i.exchange_date,
l_prorat.cost_factor_id;
l_debug_info := 'Insert in inl_match_amounts.';
SELECT inl_match_amounts_int_s.nextval
INTO l_match_amount_int_id
FROM dual;
INSERT INTO inl_match_amounts_int(
match_amount_int_id , /* 01 */
group_id , /* 02 */
processing_status_code , /* 03 */
transaction_type , /* 04 */
matched_amt , /* 05 */
matched_curr_code , /* 06 */
matched_curr_conversion_type, /* 07 */
matched_curr_conversion_date, /* 08 */
matched_curr_conversion_rate, /* 09 */
program_id , /* 10 */
program_update_date , /* 11 */
program_application_id , /* 12 */
request_id , /* 13 */
created_by , /* 14 */
creation_date , /* 15 */
last_updated_by , /* 16 */
last_update_date , /* 17 */
last_update_login /* 18 */
)
VALUES (
l_match_amount_int_id , /* 01 */
l_group_id , /* 02 */
'PENDING' , /* 03 */
'CREATE' , /* 04 */
c_amounts_rec.amount , /* 05 */
c_amounts_rec.curr_code , /* 06 */
c_amounts_rec.curr_type , /* 07 */
c_amounts_rec.curr_date , /* 08 */
c_amounts_rec.curr_rate , /* 09 */
fnd_global.conc_program_id , /* 10 */
SYSDATE , /* 11 */
fnd_global.prog_appl_id , /* 12 */
fnd_global.conc_request_id , /* 13 */
fnd_global.user_id , /* 14 */
SYSDATE , /* 15 */
fnd_global.user_id , /* 16 */
SYSDATE , /* 17 */
fnd_global.login_id); /* 18 */
UPDATE inl_matches_int MInt
SET match_amount_int_id = l_match_amount_int_id
WHERE MInt.from_parent_table_name = 'AP_INVOICE_DISTRIBUTIONS'
AND MInt.match_type_code = c_amounts_rec.match_type_code
AND MInt.processing_status_code = 'PENDING'
AND ( (c_amounts_rec.amount_type = 'TAX'
AND MInt.tax_code = c_amounts_rec.tax_code
AND MInt.from_parent_table_id
IN (SELECT d.invoice_distribution_id
FROM ap_invoice_lines l,
ap_invoice_distributions d,
ap_invoice_distributions d_prorat,
ap_invoice_lines l_prorat
WHERE d.invoice_id = p_invoice_id
AND d.invoice_id = l.invoice_id
AND l.line_number = d.invoice_line_number
AND l.line_type_lookup_code = 'TAX'
AND d_prorat.invoice_distribution_id = d.charge_applicable_to_dist_id
AND d_prorat.invoice_id = l_prorat.invoice_id
AND l_prorat.line_number = d_prorat.invoice_line_number
AND l_prorat.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
AND MInt.charge_line_type_id = c_amounts_rec.tax_cost_factor_id
)
)OR(c_amounts_rec.amount_type = 'CHARGE'
AND MInt.charge_line_type_id = c_amounts_rec.cost_factor_id
AND MInt.from_parent_table_id
IN (SELECT d.invoice_distribution_id
FROM ap_invoice_lines l,
ap_invoice_distributions d
WHERE d.invoice_id = p_invoice_id
AND d.invoice_id = l.invoice_id
AND l.line_number = d.invoice_line_number
AND l.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT')
)
))
;
SELECT SUM(MInt.matched_amt)
INTO l_match_amount_validation
FROM inl_matches_int MInt
WHERE MInt.match_amount_int_id = l_match_amount_int_id;
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,
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
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 = 'S'
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 = l.rcv_transaction_id
AND l.invoice_id = i.invoice_id
AND d.invoice_id = p_invoice_id
ORDER BY invoice_distribution_id;
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;