[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
upper(pv.vendor_name) between upper(nvl(p_supplier_name_from,'A'))
and upper(nvl(p_supplier_name_to,'Z'))
and pvs.vendor_id = pv.vendor_id
and nvl(pvs.language,p_base_language)=p_session_language;
SELECT gp.start_date
FROM gl_period_types gpt,gl_periods gp,
gl_period_sets gps,gl_sets_of_books sob
WHERE
sob.set_of_books_id=p_set_of_books_id
and sob.period_set_name=gps.period_set_name
and sob.accounted_period_type=gpt.period_type
and gp.period_set_name=gps.period_set_name
and gp.period_type=gpt.period_type
and p_as_of_date BETWEEN gp.start_date and gp.end_date;*/
SELECT
ai.invoice_id,
ai.payment_cross_rate,
-- 3641604 aps.payment_num,
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,
sum(nvl(aid1.amount,0)) dist_amount --bug 3338086
FROM
/*bug 5264865*/
ap_invoices_all ai,
ap_invoice_distributions_all aid1,
fnd_lookup_types_vl lkp,
fnd_lookup_values_vl lkv
-- 3641604 ap_payment_schedules aps
WHERE
ai.invoice_id = aid1.invoice_id
-- and ai.payment_status_flag<>'P'
-- Fix for 2545297 commented above line and wrote below one
-- and ai.payment_status_flag in ('N','P') 2901541
-- and aid1.line_type_lookup_code = 'ITEM' Commented this line for bug: 3338086
and lkp.lookup_type = 'INVOICE TYPE'
and lkp.application_id = 200
and lkv.lookup_code = ai.invoice_type_lookup_code
and lkv.lookup_type=lkp.lookup_type
and ai.invoice_type_lookup_code <> 'PREPAYMENT'
and ai.invoice_date <= 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)
-- 3641604 and ai.invoice_id=aps.invoice_id
GROUP BY
ai.invoice_id,
lkv.meaning ,
invoice_num ,
invoice_date,
ai.invoice_currency_code ,
ai.invoice_amount,
ai.amount_paid,
ai.discount_amount_taken,
ai.invoice_id,
-- 3641604 aps.payment_num,
ai.payment_cross_rate;
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;
/*Commented the following code fnd added the next two SELECT stmts for bug#1721165 TMANDA
select substr(userenv('LANG'),1,4) into l_session_language from dual;
select language_code into l_base_language from fnd_languages where installed_flag='B';
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 currency_code
INTO l_functional_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
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 ,
l_functional_currency_code ,
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_tot_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
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)
SELECT
p_request_id,
p_as_of_date,
l_organization_name ,
l_functional_currency_code,
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,
lkv.meaning,
ai.invoice_num,
ai.invoice_date ,
ai.invoice_currency_code,
SUM(nvl(AID1.AMOUNT,0)),
SUM(nvl(AID1.PREPAY_AMOUNT_REMAINING,AID1.AMOUNT) ),
(SUM(nvl(AID1.AMOUNT,0)) -
SUM(nvl(AID1.PREPAY_AMOUNT_REMAINING,AID1.AMOUNT)))
FROM
ap_invoices ai,
ap_invoice_distributions aid1,
fnd_lookup_types_vl lkp,
fnd_lookup_values_vl lkv
WHERE
ai.invoice_id=aid1.invoice_id
and ((aid1.line_type_lookup_code = 'ITEM') or
/* BUG 3935997: 'or' condition added */
(aid1.line_type_lookup_code = 'TAX' and
aid1.tax_calculated_flag = 'Y'))
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.lookup_code = ai.invoice_type_lookup_code
and lkp.lookup_type=lkv.lookup_type
and ai.vendor_id = vendor_rec.vendor_id
and ai.vendor_site_id = vendor_rec.vendor_site_id
and ai.invoice_date <= p_as_of_date
and ai.invoice_currency_code = nvl(currency_rec.invoice_currency_code,
ai.invoice_currency_code) --bug6800315
and NVL(aid1.reversal_flag,'N') <> 'Y' --bug6500253/6800315
and nvl(aid1.prepay_amount_remaining,aid1.amount)> 0
HAVING SUM(nvl(AID1.AMOUNT,0)) >= NVL(p_min_invoice_balance,0)--bug6800315
GROUP BY
lkv.meaning,
ai.invoice_num,
ai.invoice_date,
ai.invoice_currency_code;
SELECT NVL(SUM(NVL(amount, 0)), 0)
INTO v_payment_amount
FROM ap_invoice_payments
WHERE invoice_id = p_invoice_id
AND accounting_date <= p_as_of_date;
SELECT AI.payment_cross_rate, AI.invoice_currency_code, NVL(SUM(NVL(AID.amount, 0)), 0)
INTO v_payment_cross_rate, v_invoice_currency_code, v_invoice_amount
FROM ap_invoice_distributions AID, ap_invoices AI
WHERE AI.invoice_id = AID.invoice_id
AND AI.invoice_id = p_invoice_id
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 v_payment_amount, v_discount_taken
FROM ap_invoice_payments AIP, ap_invoices AI
WHERE AIP.invoice_id = AI.invoice_id
AND AI.invoice_id = p_invoice_id
AND accounting_date <= p_as_of_date;