[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT
JE_IL_TAX_PKG.IS_NUMBER(max(decode(person_last_name, ''TN'', person_first_name)),''DFN'') Deduction_File_Number,
JE_IL_TAX_PKG.IS_NUMBER(max(decode(person_last_name, ''TM'', person_first_name)),''CTPID'') Tax_Payer_ID,
max(decode(q1.le_role ,''Legal Contact'',email_address,null)) Email,
''96'' Type_Code,
:P_Manual_Rpt_Exist P_Manual,
:P_Comp_Rpt_Exist P_Complimentary_Rpt,
:P_Payer_Position P_Payer_Pos,
max(decode(person_last_name, ''TN'',substr(hp.primary_phone_area_code|| hp.primary_phone_number,1,10))) Phone_No
FROM hz_parties hp,
(SELECT subject_id ,XLE_CONTACT_GRP.concat_contact_roles (subject_id,object_id) le_role
FROM hz_relationships
WHERE object_id = (
SELECT party_id
FROM xle_firstparty_information_v
WHERE legal_entity_id = :P_Legal_Entity_ID
)
AND relationship_code = ''CONTACT_OF''
AND directional_flag = ''F'' ) q1
WHERE hp.party_id = q1.subject_id';
'SELECT NULL Deduction_File_Number, NULL Tax_Payer_ID,
NULL Email, NULL Type_Code, NULL P_Manual,
NULL P_Complimentary_Rpt, NULL P_Payer_Pos
FROM DUAL';
'SELECT aag.name awt_group_name, ''A'' awt_flag
FROM ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_awt_groups aag,
ap_checks_all ac
WHERE ac.check_id = :check_id
AND ac.check_id = aip.check_id
AND aip.invoice_id = ai.invoice_id
AND aag.group_id = nvl(ai.awt_group_id, ai.pay_awt_group_id)
AND rownum = 1';
'SELECT NULL awt_group_name, NULL awt_flag FROM DUAL';
'SELECT (SUM(acctd_rounded_cr) - SUM(acctd_rounded_dr)) vendor_balance,
party_id vendor_id3,
party_site_id vendor_site_id3
FROM xla_trial_balances
WHERE party_id = :vendor_id2
AND party_site_id = :vendor_site_id2
AND ledger_id = :p_ledger_id
AND definition_code IN
(SELECT definition_code
FROM xla_tb_definitions_b
WHERE ledger_id = :p_ledger_id)
--AND gl_date BETWEEN :p_start_date AND :p_end_date -- Bug 14162763
AND trunc(gl_date) between trunc(to_date(''01-01-1950'',''DD-MM-YYYY'')) and trunc(to_date(:p_end_date,''DD-MM-YYYY'')) -- Bug 14162763
GROUP BY party_site_id, party_id
HAVING(SUM(acctd_rounded_cr) - SUM(acctd_rounded_dr)) > 0';
'SELECT NULL vendor_balance, NULL vendor_id3, NULL vendor_site_id3 FROM DUAL';
'SELECT vendor_site_id rate_site_id,
tax_name tax_name,
tax_rate tax_rate,
to_char(start_date, ''DD-MON-YYYY'') start_date1,
to_char(end_date, ''DD-MON-YYYY'') end_date1,
comments comments
FROM ap_awt_tax_rates_all
WHERE vendor_site_id = :vendor_site_id2 ';
'SELECT NULL rate_site_id, NULL tax_name,
NULL tax_rate, NULL start_date1,
NULL end_date1, NULL comments
FROM DUAL';
'SELECT NULL count_lines FROM DUAL';
'SELECT NULL Count_Vendors FROM DUAL';
'SELECT (SUM(nvl(acctd_rounded_cr,0)) - SUM(nvl(acctd_rounded_dr,0))) vendor_balance,
party_id vendor_id3,
party_site_id vendor_site_id3
FROM xla_trial_balances
WHERE party_id = :vendor_id2
AND party_site_id = :vendor_site_id2
AND ledger_id = :p_ledger_id
AND definition_code = nvl(:p_definition_code,definition_code)
-- AND gl_date BETWEEN :p_start_date AND :p_end_date -- Bug 14162763
AND trunc(gl_date) between trunc(to_date(''01-01-1950'',''DD-MM-YYYY'')) and trunc(to_date(:p_end_date,''DD-MM-YYYY'')) -- Bug 14162763
GROUP BY party_site_id, party_id ';
SELECT primary_ledger_id
INTO l_primary_ledger_id
FROM gl_ledger_relationships
WHERE target_ledger_id = p_ledger_id
AND (target_ledger_category_code = 'PRIMARY' OR source_ledger_id <> target_ledger_id)
AND ROWNUM = 1;
SELECT currency_code
INTO l_currency_code
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
p_vat_reg_no := ' (SELECT zx.rep_registration_number
FROM zx_party_tax_profile zx
WHERE pvs.party_site_id = zx.party_id
AND zx.party_type_code = ''THIRD_PARTY_SITE''
AND ROWNUM = 1) vat_reg_no, ';
' SELECT SUM(countv) count_lines
FROM
(SELECT DISTINCT ac.vendor_site_id vendor_site_id2, (
CASE
WHEN SUM(nvl(nvl(aid.base_amount, aid.amount) *-1, 0)) < 0
OR(SUM(nvl(aip.payment_base_amount, aip.amount)) +
SUM(nvl(nvl(aid.base_amount, aid.amount) *-1, 0))) < 0
THEN 0
ELSE 1
END) countv
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_checks_all ac,
ap_invoice_payments_all aip,
po_vendors pvend,
po_vendor_sites_all pvs,
(SELECT distinct person_id,
national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE ai.invoice_id = aid.invoice_id
AND ac.check_id = aip.check_id
AND aip.invoice_id = ai.invoice_id
AND ai.set_of_books_id = ' || l_primary_ledger_id || '
AND aid.set_of_books_id = ' || l_primary_ledger_id || '
AND ai.legal_entity_id = :p_legal_entity_id
AND aid.line_type_lookup_code = ''AWT''
AND aid.awt_flag = ''A''
AND aid.awt_invoice_payment_id IS NOT NULL
AND(aid.accounting_date >= :p_start_date
AND aid.accounting_date <= :p_end_date)
AND pvend.vendor_id = pvs.vendor_id
AND nvl(pvend.employee_id, -99) = papf.person_id (+)
AND pvs.vendor_id = ai.vendor_id'
|| p_vendor_type_cond ||
' GROUP BY ac.vendor_site_id) ';
'SELECT SUM(DECODE(SUM(countv), SUM(countt), 1, 0)) Count_Vendors
FROM po_vendors pvend,
po_vendor_sites_all pvs,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf,
(SELECT distinct ac.vendor_site_id Vendor_Site_IDs,
( CASE
WHEN SUM(NVL(NVL(aid.base_amount,aid.amount)*-1,0)) < 0
OR (SUM(NVL(aip.payment_base_amount,aip.amount)) +
SUM(NVL(NVL(aid.base_amount,aid.amount)*-1,0))) < 0
THEN 0
ELSE 1
END ) countv,
COUNT(distinct(ac.vendor_site_id)) countt
FROM ap_invoices_all ai
,ap_invoice_distributions_all aid
,ap_checks_all ac
,ap_invoice_payments_all aip
WHERE ai.invoice_id = aid.invoice_id
AND ac.check_id=aip.check_id
AND aip.invoice_id=ai.invoice_id
AND ai.set_of_books_id = ' || l_primary_ledger_id || '
AND aid.set_of_books_id = ' || l_primary_ledger_id || '
AND ai.legal_entity_id = :P_Legal_Entity_ID
AND aid.line_type_lookup_code = ''AWT''
AND ((aid.awt_flag = ''A''
AND aid.awt_invoice_payment_id = aip.invoice_payment_id))
AND (aid.accounting_date >= :P_START_DATE
AND aid.accounting_date <= :P_END_DATE)
GROUP BY ac.vendor_site_id
UNION
SELECT distinct ac1.vendor_site_id Vendor_Site_IDs,
( CASE
WHEN SUM(NVL(aip1.payment_base_amount,aip1.amount)) < 0
THEN 0
ELSE 1
END ) countv,
COUNT(distinct(ac1.vendor_site_id)) countt
FROM AP_INVOICE_PAYMENTS_ALL aip1
,AP_CHECKS_ALL ac1
,PO_VENDORS pvend1
,PO_VENDOR_SITES_ALL pvs1
WHERE aip1.check_id = ac1.check_id
AND aip1.set_of_books_id= ' || l_primary_ledger_id || '
AND ac1.global_attribute_category=''JE.IL.APXPAWKB.CHECKS''
AND NVL(ac1.global_attribute1,0) > 0
AND aip1.accounting_date >=:P_START_DATE
AND aip1.accounting_date <=:P_END_DATE
AND ac1.vendor_id= pvend1.vendor_id
AND ac1.vendor_site_id = pvs1.vendor_site_id
AND pvend1.vendor_id = pvs1.vendor_id
AND :P_REPORT_NAME = ''JEILWHTT''
'|| l_currency_check
|| l_foreign_suppliers_check|| '
group by ac1.vendor_site_id) q1
WHERE pvend.vendor_id = pvs.vendor_id
AND nvl(pvend.employee_id, -99) = papf.person_id (+)
AND pvs.vendor_site_id = q1.Vendor_Site_IDs'
|| p_vendor_type_cond ||
'GROUP BY ' || p_tax_payerid_cond;
SELECT period_set_name
INTO l_period_set_name
FROM gl_ledgers
WHERE ledger_id = l_primary_ledger_id;
SELECT xlah.event_id pay_event_id ,
aip.invoice_id invoice_id,
SUM(nvl(xdln.unrounded_accounted_dr,0)) - SUM(nvl(xdln.unrounded_accounted_cr,0)) gross_amount
FROM (select distinct a.invoice_id,a.accounting_event_id from ap_invoice_payments_all a
WHERE a.invoice_id = pn_invoice_id
AND a.check_id = pn_check_id
AND a.accounting_date >= pd_start_date
AND a.accounting_date <= pd_end_date
AND a.reversal_inv_pmt_id IS NULL) aip,
xla_ae_lines xdln ,
xla_ae_headers xlah
WHERE aip.accounting_event_id = xlah.event_id
AND xlah.application_id = xdln.application_id
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.ledger_id = p_ledger_id
AND xlah.accounting_entry_status_code = 'F'
AND (xdln.accounting_class_code IN ('LIABILITY' , 'EXCHANGE_GAIN_LOSS') or xdln.gain_or_loss_flag = 'Y') --bug14152662
GROUP BY xlah.event_id,
aip.invoice_id;
SELECT xlah.event_id pay_event_id ,
aip.invoice_id invoice_id,
SUM(nvl(xdln.unrounded_accounted_cr,0)) - SUM(nvl(xdln.unrounded_accounted_dr,0)) gross_amount
FROM (select distinct a.invoice_id,a.accounting_event_id from ap_invoice_payments_all a
WHERE a.invoice_id = pn_invoice_id
AND a.check_id = pn_check_id
AND a.accounting_date >= pd_start_date
AND a.accounting_date <= pd_end_date
AND a.reversal_inv_pmt_id IS NOT NULL) aip,
xla_ae_lines xdln ,
xla_ae_headers xlah
WHERE aip.accounting_event_id = xlah.event_id
AND xlah.application_id = xdln.application_id
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.ledger_id = p_ledger_id
AND xlah.accounting_entry_status_code = 'F'
AND (xdln.accounting_class_code IN ('LIABILITY' , 'EXCHANGE_GAIN_LOSS') or xdln.gain_or_loss_flag = 'Y') --bug14152662
GROUP BY xlah.event_id ,
aip.invoice_id;
SELECT xlah.event_id event,
SUM(nvl(xdln.unrounded_accounted_cr,0)) - SUM(nvl(xdln.unrounded_accounted_dr,0)) pay_amount --modified for bug10262743
FROM xla_ae_lines xdln,
xla_ae_headers xlah ,
ap_invoice_payments_all aip
WHERE aip.invoice_id = cn_invoice_id
AND aip.check_id = pn_check_id
AND aip.accounting_event_id = cn_event_id
AND (xdln.unrounded_accounted_dr IS NOT NULL
OR
xdln.unrounded_accounted_cr IS NOT NULL)
AND xlah.application_id = xdln.application_id
AND xlah.ae_header_id = xdln.ae_header_id
AND xdln.accounting_class_code ='AWT'
AND xlah.event_id = aip.accounting_event_id
AND xlah.ledger_id = p_ledger_id
AND xlah.accounting_entry_status_code = 'F'
GROUP BY
xlah.event_id;
SELECT ai.invoice_id invoice_id
FROM ap_invoice_payments_all aip,
ap_invoice_distributions_all aid,
ap_invoices_all ai
WHERE ai.invoice_id = cn_invoice_id
AND ai.invoice_id = aip.invoice_id
AND aip.invoice_id = aid.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aip.accounting_event_id = aid.accounting_event_id
AND ROWNUM =1;
SELECT xdln.event_id event,
-- SUM(xdln.unrounded_accounted_dr) pay_amount Commented for bug 10279386
SUM(nvl(xdln.unrounded_accounted_dr,0)) - SUM(nvl(xdln.unrounded_accounted_cr,0)) pay_amount --modified for bug10279386
FROM ap_invoice_distributions_all aid ,
xla_distribution_links xdln,
xla_ae_headers xlah
WHERE aid.invoice_id = cn_invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND xlah.application_id = xdln.application_id
AND xdln.event_id = aid.accounting_event_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
AND xlah.accounting_entry_status_code = 'F'
AND xdln.source_distribution_type='AP_INV_DIST'
GROUP BY xdln.event_id;
SELECT TO_NUMBER(ac.global_attribute1) bank_wht_amount
FROM AP_CHECKS_ALL ac
,PO_VENDORS pv
,PO_VENDOR_SITES_ALL pvs
WHERE ac.check_id =pn_check_id
AND ac.vendor_id = pv.vendor_id
AND ac.vendor_site_id = pvs.vendor_site_id
AND pv.vendor_id = pvs.vendor_id
AND ac.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
AND ( ( p_information_level = 'V'
AND pv.global_attribute17 = '08'
AND pv.global_attribute15 in ('9','92','93'))
OR
( p_information_level = 'S'
AND pvs.global_attribute17 = '08'
AND pvs.global_attribute15 in ('9','92','93'))
);
SELECT SUM(xdln.unrounded_accounted_dr) INTO ln_invoice_amount
FROM ap_invoice_distributions_all aid ,
xla_distribution_links xdln,
xla_ae_headers xlah
WHERE aid.invoice_id = ln_invoice_id
AND aid.line_type_lookup_code <> 'AWT'
AND xdln.event_id = aid.accounting_event_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.application_id = xdln.application_id
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
AND xlah.accounting_entry_status_code = 'F'
AND xdln.source_distribution_type = 'AP_INV_DIST';
SELECT SIGN(SUM(NVL(aip.payment_base_amount,aip.amount))) INTO ln_sign
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = ln_event_id;
SELECT SIGN(NVL(aip.payment_base_amount,aip.amount)) INTO ln_sign
FROM ap_invoice_payments_all aip
WHERE aip.invoice_id = ln_invoice_id
AND aip.accounting_event_id = ln_event_id;
SELECT invoice_type_lookup_code INTO lv_invoice_type
FROM ap_invoices_all WHERE invoice_id = ln_invoice_id;
SELECT ai.invoice_id invoice_id
FROM ap_invoice_payments_all aip,
ap_invoice_distributions_all aid,
ap_invoices_all ai
WHERE ai.invoice_id = cn_invoice_id
AND ai.invoice_id = aip.invoice_id
AND aip.invoice_id = aid.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aip.accounting_event_id = aid.accounting_event_id
AND ROWNUM =1;
SELECT count(1) INTO l_bank_awt_check
FROM AP_CHECKS_ALL ac
WHERE ac.check_id= pn_check_id
AND ac.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
AND NVL(ac.global_attribute1,0) > 0;
SELECT count(1)
INTO l_inv_with_awt
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = pn_invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aid.awt_flag ='A'
AND aid.awt_invoice_payment_id IS NOT NULL
AND aid.accounting_date >= pd_start_date
AND aid.accounting_date <= pd_end_date;
select count(1) into v_tot_cnt from ap_invoices_all where invoice_id in (
select invoice_id from ap_invoice_payments_all where check_id = pn_check_id);
select count(1) into v_cre_cnt from ap_invoices_all where invoice_id in (
select invoice_id from ap_invoice_payments_all where check_id = pn_check_id)
and INVOICE_TYPE_LOOKUP_CODE = 'CREDIT';
select 'N' into v_temp from ap_invoices_all where invoice_id = pn_invoice_id
and INVOICE_TYPE_LOOKUP_CODE = 'CREDIT';
REM it fetches the AWT data and updates the Global Varieble
REM gn_awt_amount. This function fetches data from this GT Varieble
REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
REM +======================================================================+
*/
FUNCTION get_awt_amount
RETURN NUMBER
IS
vn_ret_awt NUMBER;
SELECT TO_NUMBER(ac.global_attribute1) bank_wht_amount
FROM AP_CHECKS_ALL ac
,PO_VENDORS pv
,PO_VENDOR_SITES_ALL pvs
WHERE ac.check_id =pn_check_id
AND ac.vendor_id = pv.vendor_id
AND ac.vendor_site_id = pvs.vendor_site_id
AND pv.vendor_id = pvs.vendor_id
AND ac.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
AND ( ( p_information_level = 'V'
AND pv.global_attribute17 = '08'
AND pv.global_attribute15 in ('9','92','93'))
OR
( p_information_level = 'S'
AND pvs.global_attribute17 = '08'
AND pvs.global_attribute15 in ('9','92','93'))
);