KDM DIOT V2

➜ Back to Library
Description
Imported from BI Publisher (as a concurrent program)
Application: EMR XXAP
Source: EMR DPIVA Export LSNA
Short Name: XXAP_DPIVA_LSNA
DB package:

XXAP EMR DPIVA Export LSNA
select
t.id,
t.tipoprov,
t.tipooperac,
t.rfc,
t.numidfiscal,
t.invoice_id,
t.party_id,
t.party_site_id,
t.vendor_name,
t.country,
t.nationality,
t.invoice_org,
t.invoice_type,
t.amount_paid,
t.item_amount,
t.import_tax,
t.currency_code,
t.exchange_rate,
t.usehdrdff,
--
-- IVALOC
--
(select
 ltrim(to_char(round(
   decode(t.currency_code,'USD',
          xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_16%AP_VAT', decode(t.usehdrdff,'N',t.rfc, null)) * t.exchange_rate,
          xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_16%AP_VAT', decode(t.usehdrdff, 'N',t.rfc, null))
         ),0),'99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.invoice_id = zl.trx_id(+) and
 ail.line_number = zl.trx_line_number (+) and
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 nvl(ail.attribute13, ail.tax_classification_code) = 'LSRM_16%AP_VAT' and
 nvl(ail.discarded_flag, 'N') = 'N' and
 nvl(ail.cancelled_flag, 'N') = 'N' and
 t.import_tax = 'NO' and
 decode (t.usehdrdff,'N',ail.attribute10, 0) = decode (t.usehdrdff,'N',nvl(t.rfc,ail.attribute10), 0)
 group by
 ail.invoice_id,
 ail.prepay_invoice_id,
 ail.org_id
 union
 select
 t.amount_paid
 from
 ar_misc_cash_distributions_all mcd,
 gl_code_combinations gcc
 where
 mcd.code_combination_id = gcc.code_combination_id and
 mcd.org_id = :p_org_id and
 mcd.cash_receipt_id = t.invoice_id and
 t.import_tax = 'NO'
 group by
 mcd.cash_receipt_id
) "VALACTPAGTAS15O16IVA",
(select
 ltrim(to_char(round(
   decode(t.currency_code, 'USD',
          (xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_16%AP_VAT', decode(t.usehdrdff,'N',t.rfc, null)) + sum(zl.taxable_amt)) * t.exchange_rate,
          (xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_16%AP_VAT', decode(t.usehdrdff,'N',t.rfc,null)) + sum(zl.taxable_amt))
         ),0),'99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.invoice_id = zl.trx_id(+) and
 ail.line_number = zl.trx_line_number (+) and
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 nvl(ail.attribute13, ail.tax_classification_code) = 'LSRM_16%AP_VAT' and
 nvl(ail.discarded_flag, 'N') = 'N' and
 nvl(ail.cancelled_flag, 'N') = 'N' and
 t.import_tax = 'NO' and
 decode (t.usehdrdff,'N',ail.attribute10, 0) = decode (t.usehdrdff,'N',nvl(t.rfc,ail.attribute10), 0)
 group by
 ail.invoice_id,
 ail.prepay_invoice_id,
 ail.org_id
 union
 select
 ltrim(to_char(round(sum(abs(mcd.amount)),0) , '99999999999990'))
 from
 ar_misc_cash_distributions_all mcd,
 gl_code_combinations gcc
 where
 mcd.code_combination_id = gcc.code_combination_id and
 mcd.org_id = :p_org_id and
 mcd.cash_receipt_id = t.invoice_id and
 t.import_tax = 'NO'
 group by
 mcd.cash_receipt_id
) "IVALOC_TOTAL",
--
-- 'IVA8'
--
(select
 ltrim(to_char(round(
   decode(t.currency_code,'USD',
          xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_8%AP_VAT', decode(t.usehdrdff,'N',t.rfc,null)) * t.exchange_rate,
          xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_8%AP_VAT', decode(t.usehdrdff,'N',t.rfc,null))
         ),0),'99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.invoice_id = zl.trx_id(+) and
 ail.line_number = zl.trx_line_number (+) and
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 nvl(ail.attribute13, ail.tax_classification_code) = 'LSRM_8%AP_VAT' and
 nvl(ail.discarded_flag, 'N') = 'N' and
 nvl(ail.cancelled_flag, 'N') = 'N' and
 t.import_tax = 'NO' and
 decode (t.usehdrdff, 'N', ail.attribute10, 0) = decode (t.usehdrdff, 'N', nvl(t.rfc, ail.attribute10), 0)
 group by
 ail.invoice_id,
 ail.prepay_invoice_id,
 ail.org_id
) "VALACTPAGTAS8IVA",
(select
 ltrim(to_char(round(
   decode(t.currency_code,'USD',
          (xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_8%AP_VAT', decode(t.usehdrdff,'N',t.rfc,null)) + sum(zl.taxable_amt)) * t.exchange_rate,
          (xxap_invoicerfc_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_8%AP_VAT', decode(t.usehdrdff,'N',t.rfc, null)) + sum(zl.taxable_amt))
         ),0),'99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.invoice_id = zl.trx_id(+) and
 ail.line_number = zl.trx_line_number (+) and
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 nvl(ail.attribute13, ail.tax_classification_code) = 'LSRM_8%AP_VAT' and
 nvl(ail.discarded_flag, 'N') = 'N' and
 nvl(ail.cancelled_flag, 'N') = 'N' and
 t.import_tax = 'NO' and
 decode (t.usehdrdff, 'N', ail.attribute10, 0) = decode (t.usehdrdff, 'N', nvl(t.rfc, ail.attribute10), 0)
 group by
 ail.invoice_id,
 ail.prepay_invoice_id,
 ail.org_id
) "IVA8_TOTAL",
--
-- IVAIMP
--
(select
 ltrim(to_char(round(
   decode (t.currency_code,'USD',
           xxap_invoice_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_IMPORT_TAX') * t.exchange_rate,
           xxap_invoice_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_IMPORT_TAX')
          ),0),'99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 ail.line_type_lookup_code = 'TAX' and
 nvl (ail.discarded_flag, 'N') = 'N' and
 nvl (ail.cancelled_flag, 'N') = 'N' and
 ail.invoice_id = zl.trx_id and
 ail.summary_tax_line_id = zl.summary_tax_line_id and
 t.import_tax = 'YES' and
 exists
  (select
   1
   from
   ap_invoice_lines_all ail2
   where
   ail2.invoice_id = ail.invoice_id and
   ail2.line_number = zl.trx_line_number and
   nvl(ail2.attribute13, ail.tax_rate_code) = 'LSRM_IMPORT_TAX'
  )
) "MONIVAPAGNOACRIMPTAS15O16",
(select
 ltrim(to_char(round(decode(t.currency_code,'USD',zl.taxable_amt * t.exchange_rate, zl.taxable_amt), 0), '99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 ail.line_type_lookup_code = 'TAX' and
 nvl (ail.discarded_flag, 'N') = 'N' and
 nvl (ail.cancelled_flag, 'N') = 'N' and
 ail.invoice_id = zl.trx_id and
 ail.summary_tax_line_id = zl.summary_tax_line_id and
 t.import_tax = 'YES' and
 exists
  (select
   1
   from
   ap_invoice_lines_all ail2
   where
   ail2.invoice_id = ail.invoice_id and
   ail2.line_number = zl.trx_line_number and
   nvl(ail2.attribute13, ail.tax_rate_code) = 'LSRM_IMPORT_TAX'
  )
) "VALACTPAGIMPBYSTAS15O16IVA",
(select
 ltrim(to_char(round(
   decode(t.currency_code,'USD',
          (xxap_invoice_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_IMPORT_TAX') + zl.taxable_amt) * t.exchange_rate,
          (xxap_invoice_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, 'LSRM_IMPORT_TAX') + zl.taxable_amt)
         ),0),'99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 ail.line_type_lookup_code = 'TAX' and
 nvl (ail.discarded_flag, 'N') = 'N' and
 nvl (ail.cancelled_flag, 'N') = 'N' and
 ail.invoice_id = zl.trx_id and
 ail.summary_tax_line_id = zl.summary_tax_line_id and
 t.import_tax = 'YES' and
 exists
  (select
   1
   from
   ap_invoice_lines_all ail2
   where
   ail2.invoice_id = ail.invoice_id and
   ail2.line_number = zl.trx_line_number and
   nvl(ail2.attribute13, ail.tax_rate_code) = 'LSRM_IMPORT_TAX'
  )
) "IVAIMP_TOTAL",
--
-- IVAOTR
--
(select
 ltrim(to_char(round(abs(decode (t.currency_code,'USD',sum(nvl(ail.attribute14, zl.taxable_amt)) * t.exchange_rate, sum(nvl(ail.attribute14, zl.taxable_amt)))), 0), '99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.invoice_id = zl.trx_id(+) and
 ail.line_number = zl.trx_line_number(+) and
 ail.org_id = zl.internal_organization_id and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 nvl (ail.attribute13, ail.tax_classification_code) = 'LSRM_EXEMPT_VAT' and
 nvl (ail.discarded_flag, 'N') = 'N' and
 nvl (ail.cancelled_flag, 'N') = 'N' and
 t.import_tax = 'NO' and
 decode(t.usehdrdff,'N',ail.attribute10, 0) = decode(t.usehdrdff,'N',t.rfc, 0)
) "ValActPagNoIVA",
(select
 ltrim(to_char(round(abs(decode(t.currency_code,'USD',sum(ail.amount) * t.exchange_rate, sum(ail.amount))), 0), '99999999999990'))
 from
 ap_invoice_lines_all ail
 where
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 ail.line_type_lookup_code = 'AWT' and
 nvl (ail.discarded_flag, 'N') = 'N' and
 nvl (ail.cancelled_flag, 'N') = 'N' and
 decode(t.usehdrdff,'N',ail.attribute10,0) = decode(t.usehdrdff,'N',t.rfc,0) and
 t.import_tax = 'NO'
) "IVARetCont",
(select
 ltrim(to_char(round(abs(decode(t.currency_code,'USD',sum(ail.amount) * t.exchange_rate,sum(ail.amount))), 0), '99999999999990'))
 from
 ap_invoice_lines_all ail
 where
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 t.import_tax = 'NO' and
 ail.line_type_lookup_code = 'AWT' and
 nvl (ail.discarded_flag, 'N') = 'N' and
 nvl (ail.cancelled_flag, 'N') = 'N' and
 decode(t.usehdrdff,'N',ail.attribute10, 0) = decode(t.usehdrdff,'N',t.rfc, 0) and
 exists
  (select
   1
   from
   gl_code_combinations gcc,
   fnd_lookup_values fl
   where
   gcc.segment4 = fl.meaning and
   fl.lookup_type = 'EMR_WT_ACCTS_LSRM' and
   fl.language = userenv ('LANG') and
   fl.enabled_flag = 'Y' and
   fl.description like 'IVA%' and
   gcc.code_combination_id = ail.default_dist_ccid
  )
) "IVARet",
(select
 ltrim(to_char(round(abs(decode(t.currency_code,'USD',sum(ail.amount) * t.exchange_rate,sum(ail.amount))), 0), '99999999999990'))
 from
 ap_invoice_lines_all ail
 where
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 t.import_tax = 'NO' and
 ail.line_type_lookup_code = 'AWT' and
 nvl(ail.discarded_flag, 'N') = 'N' and
 nvl(ail.cancelled_flag, 'N') = 'N' and
 --Check line Level DFF - Send to SAT DFF
 nvl(ail.attribute5, 'Y') = 'Y' and
 decode(t.usehdrdff,'N',ail.attribute10,0) = decode(t.usehdrdff,'N',t.rfc,0) and
 exists
  (select
   1
   from
   gl_code_combinations gcc,
   fnd_lookup_values fl
   where
   gcc.segment4 = fl.meaning and
   fl.lookup_type = 'EMR_WT_ACCTS_LSRM' and
   fl.language = userenv ('LANG') and
   fl.enabled_flag = 'Y' and
   fl.description like 'ISR%' and
   gcc.code_combination_id = ail.default_dist_ccid
  )
) "ISRRet",
(select
 ltrim(to_char(round(abs(decode(t.currency_code,'USD',sum(nvl(ail.attribute14, zl.taxable_amt)) * t.exchange_rate, sum(nvl(ail.attribute14, zl.taxable_amt)))), 0), '99999999999990'))
 from
 ap_invoice_lines_all ail,
 zx_lines zl
 where
 ail.invoice_id = zl.trx_id(+) and
 ail.line_number = zl.trx_line_number(+) and
 ail.org_id = zl.internal_organization_id and
 ail.org_id = zl.internal_organization_id(+) and
 zl.entity_code(+) = 'AP_INVOICES' and
 ail.org_id = :p_org_id and
 ail.invoice_id = t.invoice_id and
 nvl(ail.attribute13, ail.tax_classification_code) = 'LSRM_0%AP_VAT' and
 nvl(ail.discarded_flag, 'N') = 'N' and
 nvl(ail.cancelled_flag, 'N') = 'N' and
 t.import_tax = 'NO' and
 decode(t.usehdrdff,'N',ail.attribute10,0) = decode(t.usehdrdff,'N',t.rfc,0)
) "ValActPagTas0IVA"
--
--
from
--
--
(
select distinct
'CAB' id,
'04' tipoprov,
nvl(pvs.attribute12,'85') tipooperac,
upper(decode(
 nvl(aia.attribute10,'Y'),'N',
 ail.attribute10,
 (select
  nvl(trim(rep_registration_number),'XAXX010101000')
  from
  zx_party_tax_profile
  where
  party_id = pv.party_id and
  party_type_code = 'THIRD_PARTY'
 )
)) rfc,
aia.invoice_num numidfiscal,
aia.invoice_id,
aia.party_id,
pvs.party_site_id,
upper(decode(nvl(aia.attribute10,'Y'), 'N', ail.attribute7, pv.vendor_name)) vendor_name,
upper(decode(nvl(aia.attribute10,'Y'), 'N', ail.attribute8, pvs.country)) country,
upper(decode(nvl(aia.attribute10,'Y'),'N', ail.attribute9, (select territory_short_name from fnd_territories_vl where territory_code = pvs.country))) nationality,
aia.org_id invoice_org,
aia.invoice_type_lookup_code invoice_type,
decode(nvl(aia.attribute10,'Y'), 'N', ltrim (to_char(round(decode( aia.invoice_currency_code, 'USD', xxap_rfc_amtpaid(aia.invoice_id, ail.org_id, ail.attribute10) * ip.exchange_rate, xxap_rfc_amtpaid(aia.invoice_id, ail.org_id, ail.attribute10)), 0), '99999999999990')), ltrim(to_char(round(decode(aia.invoice_currency_code, 'USD', aia.amount_paid * ip.exchange_rate, aia.amount_paid), 0), '99999999999990'))) amount_paid,
decode(nvl(aia.attribute10,'Y'), 'N', ltrim (to_char(round(decode( aia.invoice_currency_code, 'USD', xxap_invbyrfc_item_price(aia.invoice_id, ail.org_id, null, ail.attribute10) * ip.exchange_rate, xxap_invbyrfc_item_price(aia.invoice_id, ail.org_id, null, ail.attribute10)), 0), '99999999999990')), ltrim(to_char(round(decode(aia.invoice_currency_code, 'USD', xxap_invoice_item_price( aia.invoice_id, ail.prepay_invoice_id, ail.org_id) * ip.exchange_rate, xxap_invoice_item_price(aia.invoice_id, ail.prepay_invoice_id, ail.org_id)), 0), '99999999999990'))) item_amount,
'NO' import_tax,
aia.invoice_currency_code currency_code,
ip.exchange_rate,
nvl (aia.attribute10, 'Y') usehdrdff,
'I' src
from
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_transaction_entities ent,
xla_events xe,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_distribution_links dl,
gl_import_references gir,
gl_je_lines jel,
gl_je_headers jeh,
gl_je_batches glb,
gl_code_combinations glc,
ap_suppliers pv,
ap_supplier_sites_all pvs,
ap_invoice_payments_all ip,
ap_checks_all ac
where
ail.invoice_id = aia.invoice_id and
aia.org_id = ail.org_id and
aia.invoice_type_lookup_code != 'PREPAYMENT' and
--Check Header Level DFF - Send to SAT DFF
nvl (aia.attribute13, 'Y') = 'Y' and
--Check line Level DFF - Send to SAT DFF
nvl (ail.attribute5, 'Y') = 'Y' and
aid.invoice_id = aia.invoice_id and
aid.invoice_id = ail.invoice_id and
aid.invoice_line_number = ail.line_number and
dl.source_distribution_id_num_1 = aid.invoice_distribution_id and
dl.applied_to_source_id_num_1 = ail.invoice_id and
dl.source_distribution_type = 'AP_INV_DIST' and
ent.source_id_int_1 = aia.invoice_id and
ent.legal_entity_id = aia.legal_entity_id and
ent.security_id_int_1 = aia.org_id and
ent.entity_code = 'AP_INVOICES' and
ent.application_id = xe.application_id and
ent.entity_id = xe.entity_id and
aeh.application_id = ent.application_id and
aeh.entity_id = ent.entity_id and
aeh.ledger_id = ent.ledger_id and
aeh.accounting_entry_status_code = 'F' and
aeh.gl_transfer_status_code = 'Y' and
ael.ae_header_id = aeh.ae_header_id and
ael.application_id = aeh.application_id and
aeh.ae_header_id = dl.ae_header_id and
ael.ae_line_num = dl.ae_line_num and
xe.event_id = dl.event_id and
gir.gl_sl_link_id = ael.gl_sl_link_id and
gir.gl_sl_link_table = ael.gl_sl_link_table and
gir.reference_7 = ael.ae_header_id and
jel.je_header_id = gir.je_header_id and
jel.je_line_num = gir.je_line_num and
jel.code_combination_id = glc.code_combination_id and
aia.vendor_id = pv.vendor_id and
aia.vendor_site_id = pvs.vendor_site_id and
pvs.vendor_id = pv.vendor_id and
jeh.status = 'P' and
jeh.actual_flag = 'A' and
jeh.je_header_id = jel.je_header_id and
jeh.period_name = jel.period_name and
glb.je_batch_id = jeh.je_batch_id and
glb.je_batch_id = gir.je_batch_id and
pvs.org_id = aid.org_id and
pvs.org_id = aia.org_id and
ip.invoice_id = ail.invoice_id and
ip.check_id = ac.check_id and
nvl (ip.reversal_flag, 'N') = 'N' and
nvl (ip.posted_flag, 'N') = 'Y' and
---- PARAMETERS ----
ip.period_name = :p_period_name and
jeh.ledger_id = :p_ledger_id and
glc.segment1 = :p_business_unit and
aia.org_id = :p_org_id and
---- PARAMETERS ----
ail.line_type_lookup_code = 'ITEM' and
nvl (ail.discarded_flag, 'N') = 'N' and
nvl (ail.cancelled_flag, 'N') = 'N' and
--if line was incorrectly classified and the line has a tax code of mport tax -- do not include
nvl (ail.attribute13, '1') != 'LSRM_IMPORT_TAX' and
nvl (aid.reversal_flag, 'N') = 'N' and
nvl (aid.cancellation_flag, 'N') = 'N' and
exists
 (select
  1
  from
  ap_invoice_lines_all aill
  where
  aill.invoice_id = ail.invoice_id and
  aill.line_type_lookup_code in ('TAX', 'AWT') and
  aill.tax_rate_code != 'LSRM_IMPORT_TAX'
 )
union
--invoices for prepayments
select distinct
'CAB' id,
'04' tipoprov,
nvl (pvs.attribute12,'85') tipooperac,
upper(decode(
 nvl(aia.attribute10,'Y'),'N',
 ail.attribute10,
 (select
  nvl(trim(rep_registration_number), 'XAXX010101000')
  from
  zx_party_tax_profile
  where
  party_id = pv.party_id and
  party_type_code = 'THIRD_PARTY'
 )
)) rfc,
aia.invoice_num numidfiscal,
aia.invoice_id,
aia.party_id,
pvs.party_site_id,
upper(decode(nvl(aia.attribute10,'Y'),'N',ail.attribute7, pv.vendor_name)) vendor_name,
upper(decode(nvl(aia.attribute10,'Y'),'N',ail.attribute8, pvs.country)) country,
upper(decode(
 nvl(aia.attribute10,'Y'),'N',
 ail.attribute9,
 (select
  territory_short_name
  from
  fnd_territories_vl
  where
  territory_code = pvs.country
 )
)) nationality,
aia.org_id invoice_org,
aia.invoice_type_lookup_code invoice_type,
decode(nvl(aia.attribute10,'Y'),'N', ltrim(to_char(round(decode(aia.invoice_currency_code, 'USD', xxap_rfc_amtpaid(aia.invoice_id, ail.org_id, ail.attribute10) * ip1.exchange_rate, xxap_rfc_amtpaid(aia.invoice_id, ail.org_id, ail.attribute10)), 0), '99999999999990')), ltrim(to_char(round(decode (aia.invoice_currency_code, 'USD', aia.amount_paid * ip1.exchange_rate, aia.amount_paid), 0), '99999999999990'))) amount_paid,
decode(nvl(aia.attribute10,'Y'),'N', ltrim(to_char(round(decode(aia.invoice_currency_code, 'USD', xxap_invbyrfc_item_price(aia.invoice_id, ail.org_id, null, ail.attribute10) * ip1.exchange_rate, xxap_invbyrfc_item_price(aia.invoice_id, ail.org_id, null, ail.attribute10)), 0), '99999999999990')), ltrim(to_char(round(decode(aia.invoice_currency_code, 'USD', xxap_invoice_item_price(aia.invoice_id, ail1.prepay_invoice_id, ail.org_id) * ip1.exchange_rate, xxap_invoice_item_price(aia.invoice_id, ail1.prepay_invoice_id, ail.org_id)), 0), '99999999999990'))) item_amount,
'NO' import_tax,
aia.invoice_currency_code currency_code,
ip1.exchange_rate,
nvl (aia.attribute10, 'Y') usehdrdff,
'I' src
from
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_suppliers pv,
ap_supplier_sites_all pvs,
ap_invoice_payments_all ip1,
ap_invoice_lines_all ail1
where
ail.invoice_id = aia.invoice_id  and
aia.org_id = ail.org_id and
aia.org_id = :p_org_id and
--Check Header Level DFF - Send to SAT DFF
nvl (aia.attribute13, 'Y') = 'Y' and
--Check line Level DFF - Send to SAT DFF
nvl (ail.attribute5, 'Y') = 'Y' and
--PREPAYMENT invoices do not pay taxes
aia.invoice_type_lookup_code != 'PREPAYMENT' and
ail.line_type_lookup_code = 'ITEM' and
nvl (ail.discarded_flag, 'N') = 'N' and
nvl (ail.cancelled_flag, 'N') = 'N' and
--if line was incorrectly classified and the line has a tax code of mport tax -- do not include
nvl (ail.attribute13, '1') != 'LSRM_IMPORT_TAX' and
aia.vendor_id = pv.vendor_id and
aia.org_id = pvs.org_id and
aia.vendor_site_id = pvs.vendor_site_id and
pvs.vendor_id = pv.vendor_id and
ail.invoice_id = ail1.invoice_id and
ail1.line_type_lookup_code = 'PREPAY' and
ip1.invoice_id = nvl (ail1.prepay_invoice_id, ail1.invoice_id) and
nvl (ip1.reversal_flag, 'N') = 'N' and
nvl (ip1.posted_flag, 'N') = 'Y' and
exists
 (select
  1
  from
  ap_invoice_lines_all ail2,
  ap_invoice_distributions_all aid,
  xla_transaction_entities ent,
  xla_events xe,
  xla_ae_headers aeh,
  xla_ae_lines ael,
  xla_distribution_links dl,
  gl_import_references gir,
  gl_je_lines jel,
  gl_je_headers jeh,
  gl_je_batches glb,
  gl_code_combinations glc,
  ap_invoice_payments_all ip,
  ap_checks_all ac
  where
  1=1 and
  aid.invoice_id = aia.invoice_id and
  aid.invoice_id = ail2.invoice_id and
  aid.invoice_line_number = ail2.line_number and
  dl.source_distribution_id_num_1 in (aid.prepay_distribution_id, aid.invoice_distribution_id) and
  dl.applied_to_source_id_num_1 in (ail1.prepay_invoice_id, ail1.invoice_id) and
  dl.source_distribution_type = 'AP_INV_DIST' and
  ent.source_id_int_1 in (ail2.prepay_invoice_id, ail2.invoice_id) and
  ent.legal_entity_id = aia.legal_entity_id and
  ent.security_id_int_1 = aid.org_id and
  ent.entity_code = 'AP_INVOICES' and
  ent.application_id = xe.application_id and
  ent.entity_id = xe.entity_id and
  aeh.application_id = ent.application_id and
  aeh.entity_id = ent.entity_id and
  aeh.ledger_id = ent.ledger_id and
  aeh.accounting_entry_status_code = 'F' and
  aeh.gl_transfer_status_code = 'Y' and
  ael.ae_header_id = aeh.ae_header_id and
  ael.application_id = aeh.application_id and
  aeh.ae_header_id = dl.ae_header_id and
  ael.ae_line_num = dl.ae_line_num and
  xe.event_id = dl.event_id and
  gir.gl_sl_link_id = ael.gl_sl_link_id and
  gir.gl_sl_link_table = ael.gl_sl_link_table and
  gir.reference_7 = ael.ae_header_id and
  jel.je_header_id = gir.je_header_id and
  jel.je_line_num = gir.je_line_num and
  jel.code_combination_id = glc.code_combination_id and
  jeh.status = 'P' and
  jeh.actual_flag = 'A' and
  jeh.je_header_id = jel.je_header_id and
  jeh.period_name = jel.period_name and
  glb.je_batch_id = jeh.je_batch_id and
  glb.je_batch_id = gir.je_batch_id and
  --if invoice was prepaid get payment information from prepayment invoice
  ip.invoice_id = nvl(ail1.prepay_invoice_id, ail1.invoice_id) and
  ip.check_id = ac.check_id and
  nvl(ip.reversal_flag, 'N') = 'N' and
  nvl(ip.posted_flag, 'N') = 'Y' and
  ---- PARAMETERS ----
  aid.period_name = :p_period_name and
  jeh.ledger_id = :p_ledger_id and
  glc.segment1 = :p_business_unit and
  ail1.org_id = :p_org_id and
  ---- PARAMETERS ----
  ail2.line_type_lookup_code = 'PREPAY' and
  nvl(ail2.discarded_flag, 'N') = 'N' and
  nvl(ail2.cancelled_flag, 'N') = 'N' and
  --if line was incorrectly classified and the line has a tax code of mport tax -- do not include
  nvl(ail2.attribute13, '1') != 'LSRM_IMPORT_TAX' and
  nvl(aid.reversal_flag, 'N') = 'N' and
  nvl(aid.cancellation_flag, 'N') = 'N'
 ) and
exists
 (select
  1
  from
  ap_invoice_lines_all aill
  where
  aill.invoice_id = ail.invoice_id and
  aill.line_type_lookup_code in ('TAX', 'AWT') and
  aill.tax_rate_code != 'LSRM_IMPORT_TAX'
 )
union
select distinct
'CAB' id,
'05' tipoprov,
nvl (pvs.attribute12, '85') tipooperac,
nvl (ail.attribute10, 'XEX010101000') rfc,
aia.invoice_num numidfiscal,
aia.invoice_id,
0,
0,
upper (ail.attribute7) vendor_name,
ail.attribute8 country,
ail.attribute9 nationality,
aia.org_id invoice_org,
aia.invoice_type_lookup_code invoice_type,
ltrim(to_char(round(decode(aia.invoice_currency_code, 'USD', xxap_invoice_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, nvl(ail.attribute13,ail.tax_classification_code)) * ip.exchange_rate, (xxap_invoice_tax_paid(ail.invoice_id, ail.prepay_invoice_id, ail.org_id, nvl (ail.attribute13, ail.tax_classification_code)))), 0), '99999999999990')) amount_paid,
ltrim(to_char(round(decode(
 aia.invoice_currency_code,'USD',
 (select
  to_char(sum(taxable_amt))
  from
  ap_invoice_lines_all ail2,
  zx_lines zl
  where
  trx_id = ail.invoice_id and
  ail2.invoice_id = zl.trx_id and
  ail2.line_number = zl.trx_line_number and
  ail2.org_id = zl.internal_organization_id and
  zl.entity_code = 'AP_INVOICES' and
  nvl(ail2.attribute13, ail2.tax_classification_code) = nvl(ail.attribute13, ail.tax_classification_code)
 ) * ip.exchange_rate,
 (select
  to_char(sum(taxable_amt))
  from
  ap_invoice_lines_all ail2,
  zx_lines zl
  where
  trx_id = ail.invoice_id and
  ail2.invoice_id = zl.trx_id and
  ail2.line_number = zl.trx_line_number and
  ail2.org_id = zl.internal_organization_id and
  zl.entity_code = 'AP_INVOICES' and
  nvl (ail2.attribute13, ail2.tax_classification_code) = nvl (ail.attribute13, ail.tax_classification_code)
 )
), 0), '99999999999990')) taxable_amount,
'YES' import_tax,
aia.invoice_currency_code currency_code,
ip.exchange_rate,
'N' usehdrdff,
'I' src
from
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_transaction_entities ent,
xla_events xe,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_distribution_links dl,
gl_import_references gir,
gl_je_lines jel,
gl_je_headers jeh,
gl_je_batches glb,
gl_code_combinations glc,
ap_suppliers pv,
ap_supplier_sites_all pvs,
ap_invoice_payments_all ip,
ap_checks_all ac
where
ail.invoice_id = aia.invoice_id and
aia.org_id = ail.org_id and
--PREPAYMENT invoices do not pay taxes
aia.invoice_type_lookup_code != 'PREPAYMENT' and
aid.invoice_id = aia.invoice_id and
aid.invoice_id = ail.invoice_id and
aid.invoice_line_number = ail.line_number and
ent.source_id_int_1 = aia.invoice_id and
ent.legal_entity_id = aia.legal_entity_id and
ent.security_id_int_1 = aia.org_id and
ent.entity_code = 'AP_INVOICES' and
ent.application_id = xe.application_id and
ent.entity_id = xe.entity_id and
aeh.application_id = ent.application_id and
aeh.entity_id = ent.entity_id and
aeh.ledger_id = ent.ledger_id and
aeh.accounting_entry_status_code = 'F' and
aeh.gl_transfer_status_code = 'Y' and
ael.ae_header_id = aeh.ae_header_id and
ael.application_id = aeh.application_id and
aeh.ae_header_id = dl.ae_header_id and
ael.ae_line_num = dl.ae_line_num and
xe.event_id = dl.event_id and
aid.invoice_distribution_id = dl.source_distribution_id_num_1 and
gir.gl_sl_link_id = ael.gl_sl_link_id and
gir.gl_sl_link_table = ael.gl_sl_link_table and
gir.reference_7 = ael.ae_header_id and
jel.je_header_id = gir.je_header_id and
jel.je_line_num = gir.je_line_num and
jel.code_combination_id = glc.code_combination_id and
aia.vendor_id = pv.vendor_id and
aia.vendor_site_id = pvs.vendor_site_id and
pvs.vendor_id = pv.vendor_id and
jeh.status = 'P' and
jeh.actual_flag = 'A' and
jeh.je_header_id = jel.je_header_id and
jeh.period_name = jel.period_name and
glb.je_batch_id = jeh.je_batch_id and
glb.je_batch_id = gir.je_batch_id and
pvs.org_id = aid.org_id and
pvs.org_id = aia.org_id and
ail.invoice_id = ip.invoice_id and
ip.check_id = ac.check_id and
nvl (ip.reversal_flag, 'N') = 'N' and
---- PARAMETERS ----
ip.period_name = :p_period_name and
jeh.ledger_id = :p_ledger_id and
glc.segment1 = :p_business_unit and
aia.org_id = :p_org_id and
---- PARAMETERS ----
nvl (ail.discarded_flag, 'N') = 'N' and
nvl (ail.cancelled_flag, 'N') = 'N' and
nvl (aid.reversal_flag, 'N') = 'N' and
nvl (aid.cancellation_flag, 'N') = 'N' and
ip.posted_flag = 'Y' and
ail.line_type_lookup_code = 'ITEM' and
nvl (ail.attribute13, ail.tax_classification_code) = 'LSRM_IMPORT_TAX'
union
select distinct
'CAB' id,
'04' tipoprov,
'85' tipooperac,
nvl(acr.attribute10,registration_number) rfc,
acr.receipt_number numidfiscal,
acr.cash_receipt_id,
0 party_id,
0 party_site_id,
nvl(acr.attribute7,ob.bank_name) supplier,
nvl(acr.attribute8,ob.country) country,
nvl(acr.attribute9,(select territory_short_name from fnd_territories_vl where territory_code = ob.country)) nationality,
acr.org_id receipt_org,
art.name,
ltrim(to_char(round(abs(mcd.amount),0), '99999999999990')) amount_paid,
ltrim(to_char(round((ard.amount_cr - abs (mcd.amount)), 0))) taxable_amount,
'NO' import_tax,
acr.currency_code,
acr.exchange_rate,
'N' usehdrdff,
'R' src
from
  ar_cash_receipts_all acr,
  ar_cash_receipt_history_all acrh,
  ar_distributions_all ard,
  ar_misc_cash_distributions_all mcd,
  ar_receivables_trx_all art,
  xla_ae_headers xah,
  xla_ae_lines xal,
  xla_distribution_links xdl,
  gl_import_references glimp,
  gl_je_batches glb,
  gl_je_headers glh,
  gl_je_lines gll,
  gl_code_combinations gcc,
  fnd_lookup_values_vl lv,
  xle_fp_registrations_v xfr,
  (select
   cba.bank_id,
   bb.bank_name,
   cba.bank_account_name,
   cba.bank_account_num,
   cba.multi_currency_allowed_flag,
   cba.zero_amount_allowed,
   cba.account_classification,
   bb.bank_branch_type,
   bb.bank_branch_name,
   bb.bank_branch_number,
   bb.eft_swift_code,
   bau.bank_acct_use_id,
   ou.name,
   gcf.concatenated_segments,
   bb.country,
   bb.bank_number
   from
   ce_bank_accounts cba,
   ce_bank_acct_uses_all bau,
   cefv_bank_branches bb,
   hr_operating_units ou,
   gl_code_combinations_kfv gcf
   where
   cba.bank_account_id = bau.bank_account_id and
   cba.bank_branch_id = bb.bank_branch_id and
   ou.organization_id = bau.org_id and
   cba.asset_code_combination_id = gcf.code_combination_id and
   ou.name = 'LSRM OU' and
   (cba.end_date is null or cba.end_date > trunc(sysdate))
  ) ob
where
acr.cash_receipt_id = acrh.cash_receipt_id and
acr.org_id = acrh.org_id and
acrh.cash_receipt_history_id = ard.source_id and
acrh.org_id = ard.org_id and
acr.cash_receipt_id = mcd.cash_receipt_id and
xfr.legal_entity_id = acr.legal_entity_id and
ard.source_table = 'CRH' and
acr.type = 'MISC' and
xal.application_id = xah.application_id and
xah.ae_header_id = xal.ae_header_id and
xah.ae_header_id = xdl.ae_header_id and
xal.ae_line_num = xdl.ae_line_num and
xdl.application_id = xah.application_id and
xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' and
xdl.source_distribution_id_num_1 = ard.line_id and
glimp.je_header_id = glh.je_header_id and
glimp.je_line_num = gll.je_line_num and
glimp.je_batch_id = glb.je_batch_id and
glh.je_header_id = gll.je_header_id and
glh.je_batch_id = glb.je_batch_id and
glimp.gl_sl_link_table = xal.gl_sl_link_table and
glimp.gl_sl_link_id = xal.gl_sl_link_id and
glimp.reference_5 = xah.entity_id and
glimp.reference_6 = xah.event_id and
glimp.reference_7 = xah.ae_header_id and
glh.je_source = 'Receivables' and
acr.status = 'APP' and --Applied excludes reversed
mcd.code_combination_id = gcc.code_combination_id and
gcc.segment4 = lv.meaning and --Acount Number
lv.lookup_type = 'EMR_RECEIPT_ACCTS_LSRM' and
lv.enabled_flag = 'Y' and
lv.end_date_active is null and
art.name = lv.description and -- Activity Name
acr.receivables_trx_id = art.receivables_trx_id and
ob.bank_acct_use_id = acr.remit_bank_acct_use_id and
---- PARAMETERS ----
gll.period_name = :p_period_name and
gll.ledger_id = :p_ledger_id and
gcc.segment1 = :p_business_unit and
acr.org_id = :p_org_id
---- PARAMETERS ----
) t
order by
t.tipoprov,
t.rfc,
t.invoice_id
Parameter NameSQL textValidation
Operating Unit
 
LOV
Period Name
 
LOV
Ledger Id
 
LOV Oracle
Business Unit
 
LOV
Download
Blitz Report™