[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pv.vendor_id,
pv.vendor_name supplier_name,
pv.segment1 supplier_number,
pv.num_1099 tax_payer_id,
pv.vat_code vat_registration_number,
pvs.vendor_site_code supplier_site_code,
pvs.vendor_site_id,
pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.city,
pvs.state,
pvs.zip postal_code,
pvs.province,
pvs.country
FROM ap_suppliers pv ,
ap_supplier_sites_all pvs
WHERE
--bug 12927165
--upper(pv.vendor_name) between upper(nvl(p_supplier_name_from,'A'))
--and upper(nvl(p_supplier_name_to,'Z'))
upper(pv.vendor_name) between upper(nvl(p_supplier_name_from,pv.vendor_name))
and upper(nvl(p_supplier_name_to,pv.vendor_name))
AND pvs.vendor_id = pv.vendor_id
AND nvl(pvs.language,p_base_language)=p_session_language;
SELECT DISTINCT ai.invoice_id,
ai.payment_cross_rate,
lkv.meaning trans_type,
ai.invoice_num trans_num,
ai.invoice_date trans_date,
ai.invoice_currency_code,
ai.invoice_amount invoice_amount,
nvl(ai.amount_paid,0) payment_amount,
nvl(ai.discount_amount_taken,0) discount_taken,
gl.currency_code --bug9050332
FROM ap_invoices_all ai,
fnd_lookup_types_vl lkp,
fnd_lookup_values_vl lkv,
gl_ledgers gl --bug9050332
WHERE ai.set_of_books_id = gl.ledger_id --bug9050332
AND lkp.lookup_type = 'INVOICE TYPE'
AND lkp.application_id = 200
AND lkv.view_application_id = 200 --bug13716228
AND lkv.lookup_code = ai.invoice_type_lookup_code
AND lkv.lookup_type=lkp.lookup_type
AND ai.invoice_type_lookup_code <> 'PREPAYMENT'
AND trunc(ai.invoice_date) <= trunc(p_as_of_date)
AND ai.vendor_id = p_vendor_id
AND ai.vendor_site_id = p_vendor_site_id
AND ai.invoice_currency_code = nvl(p_currency,ai.invoice_currency_code)
AND AP_INVOICES_UTILITY_PKG.get_approval_status(AI.INVOICE_ID,
AI.INVOICE_AMOUNT, AI.PAYMENT_STATUS_FLAG,
AI.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED'
;
SELECT DISTINCT(invoice_currency_code) invoice_currency_code
FROM ap_invoices_all ai
WHERE ai.vendor_id=p_supplier_id
AND ai.invoice_currency_code=nvl(p_currency,ai.invoice_currency_code);
SELECT payment_num
FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id;
SELECT substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
INTO l_session_language
FROM dual;
SELECT nls_language
INTO l_base_language
FROM fnd_languages
WHERE installed_flag = 'B';
Selecting the organization from gl_sets_of_books
in case of single org since the org_id will be
null in hr_organization_units*/
BEGIN
SELECT name
INTO l_organization_name
FROM hr_organization_units
WHERE organization_id = FND_PROFILE.VALUE('ORG_ID');
SELECT name
INTO l_organization_name
FROM gl_sets_of_books
WHERE set_of_books_id = P_Set_of_books_id;
SELECT AI.payment_cross_rate, AI.invoice_currency_code, NVL(SUM(NVL(AID.amount, 0)), 0)
INTO l_payment_cross_rate, l_invoice_currency_code, l_invoice_amount
FROM ap_invoice_distributions AID, ap_invoices AI
WHERE AI.invoice_id = AID.invoice_id
AND AI.invoice_id = inv_rec.invoice_id
AND (AID.prepay_distribution_id IS NULL OR
trunc(AID.accounting_date) <= trunc(p_as_of_date))
GROUP BY AI.payment_cross_rate, AI.invoice_currency_code;
SELECT NVL(SUM(NVL(AIP.amount, 0)), 0), NVL(SUM(NVL(AIP.discount_taken, 0)), 0)
INTO l_payment_amount, l_discount_taken
FROM ap_invoice_payments AIP, ap_invoices AI
WHERE AIP.invoice_id = AI.invoice_id
AND AI.invoice_id = inv_rec.invoice_id
AND trunc(AIP.accounting_date) <= trunc(p_as_of_date);
INSERT INTO AP_SUPPLIER_BALANCE_ITF(
Request_id ,
as_of_date ,
organization_name ,
functional_currency_code ,
supplier_name ,
supplier_number,
vat_registration_number,
supplier_site_code ,
address_line1 ,
address_line2 ,
address_line3 ,
city ,
state ,
zip ,
country ,
invoice_type,
invoice_num,
invoice_date,
invoice_currency_code,
invoice_amount ,
amount_remaining ,
payment_amount ,
discount_taken ,
discount_amount_available )
VALUES(p_request_id,
p_as_of_date,
l_organization_name ,
inv_rec.currency_code, --bug9050332 fucntional currency
vendor_rec.supplier_name,
vendor_rec.supplier_number,
vendor_rec.vat_registration_number,
vendor_rec.supplier_site_code,
vendor_rec.address_line1,
vendor_rec.address_line2,
vendor_rec.address_line3,
vendor_rec.city,
vendor_rec.state,
vendor_rec.postal_code,
vendor_rec.country,
inv_rec.trans_type,
inv_rec.trans_num,
inv_rec.trans_date,
inv_rec.invoice_currency_code,
inv_rec.invoice_amount,
l_amount_remaining,
inv_rec.payment_amount,
inv_rec.discount_taken,
l_discount_avail
);
should be selected.
3. Was not restricting by minimun invoice balance. According to
doc, the minimum should restrict the transactions in the report.
4. SQL was selecting from vendor tables. Since we are within
the vendor loop, we can get values from cursor instead of from
the tables, so the SQL is simpler.*/
IF (NVL(p_include_prepayments,'N') = 'Y') THEN --bug6800315
--bug13716228
--Issue 1 - prepay dist total is multipled by no.of applications
--Issue 2 - fnd_lookup_values results in multiple rows as when the same value
-- defined for multiple applications
INSERT INTO ap_supplier_balance_itf(
Request_id ,
as_of_date,
organization_name ,
functional_currency_code ,
supplier_name ,
supplier_number,
vat_registration_number,
supplier_site_code ,
address_line1 ,
address_line2 ,
address_line3 ,
city ,
state ,
zip ,
country ,
invoice_type,
invoice_num,
invoice_date,
invoice_currency_code,
prepay_amount_original,
prepay_amount_remaining,
prepay_amount_applied,
invoice_amount , -- 8217987 3 Cols added
amount_remaining ,
payment_amount)
SELECT DISTINCT p_request_id,
p_as_of_date,
l_organization_name ,
pp_inv.currency_code, --bug9050332
vendor_rec.supplier_name,
vendor_rec.supplier_number,
vendor_rec.vat_registration_number,
vendor_rec.supplier_site_code,
vendor_rec.address_line1,
vendor_rec.address_line2,
vendor_rec.address_line3,
vendor_rec.city,
vendor_rec.state,
vendor_rec.postal_code,
vendor_rec.country,
pp_inv.meaning,
pp_inv.invoice_num,
pp_inv.invoice_date,
pp_inv.invoice_currency_code,
pp_inv.dist_total,
pp_inv.dist_total+pp_inv.appl_dist_total,
-pp_inv.appl_dist_total,
NULL,
NULL,
pp_inv.amount_paid
FROM (SELECT DISTINCT
ai.invoice_id,
aid1.invoice_distribution_id,
gl.currency_code,
lkv.meaning,
ai.invoice_num,
ai.invoice_date ,
ai.invoice_currency_code,
ai.amount_paid,
sum(NVL(aid1.amount, 0)) over (partition by ai.invoice_id) dist_total,
sum(sum(CASE WHEN trunc(pd.accounting_date) <= trunc(p_as_of_date)
THEN pd.amount
ELSE 0
END)) over (partition by ai.invoice_id) appl_dist_total
FROM ap_invoices ai,
ap_invoice_distributions aid1,
ap_invoice_distributions pd, --bug13411905
fnd_lookup_types_vl lkp,
fnd_lookup_values_vl lkv,
gl_ledgers gl --bug9050332
WHERE 1=1
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.payment_status_flag = 'Y'
AND lkp.lookup_type = 'INVOICE TYPE'
AND lkp.application_id = 200
AND lkv.view_application_id = 200
AND lkp.lookup_type=lkv.lookup_type
AND lkv.lookup_code = ai.invoice_type_lookup_code
AND ai.vendor_id = vendor_rec.vendor_id
AND ai.vendor_site_id = vendor_rec.vendor_site_id
AND trunc(ai.invoice_date) <= trunc(p_as_of_date)
AND ai.invoice_currency_code = nvl(currency_rec.invoice_currency_code,
ai.invoice_currency_code) --bug6800315
AND ai.set_of_books_id = gl.ledger_id --bug9050332
AND ai.invoice_id=aid1.invoice_id
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'ERV',
'TIPV', 'TERV', 'TRV',
'REC_TAX', 'NONREC_TAX', 'TAX') -- bug13411905
AND NVL(aid1.reversal_flag,'N') <> 'Y' --bug6500253/6800315
AND pd.prepay_distribution_id (+) = aid1.invoice_distribution_id
GROUP BY ai.invoice_id,
ai.invoice_num,
ai.invoice_date ,
ai.invoice_currency_code,
ai.amount_paid,
aid1.invoice_distribution_id,
nvl(aid1.amount, 0),
gl.currency_code,
lkv.meaning
) pp_inv
WHERE pp_inv.dist_total >= NVL(p_min_invoice_balance,0)--bug6800315
AND pp_inv.dist_total+pp_inv.appl_dist_total > 0 -- added bug 13411905
;