ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 46140955F34CA748E0634C05890A38B5 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 Y XXAP XXAP_DPIVA_LSNA US KDM DIOT V2 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 1=1 :p_business_unit :p_ledger_id :p_org_id :p_period_name 1 10 :p_org_id LOV custom Y select hou.organization_id id, hou.name value, hou.organization_id description from hr_operating_units hou where name in ('LSRM OU','KATO','LEROY SOMER','LSPD') order by value,description coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) Y US Operating Unit Operating Unit 2 20 :p_period_name LOV custom Y select glp.period_name id, glp.period_name value, null description from gl_periods glp, gl_sets_of_books gsob, gl_period_sets gps, gl_period_types gpt, hr_organization_units horu, hr_operating_units hopu where gsob.period_set_name=gps.period_set_name and gsob.accounted_period_type=gpt.period_type and gsob.set_of_books_id=hopu.set_of_books_id and horu.business_group_id=hopu.business_group_id and horu.organization_id=hopu.organization_id and glp.period_set_name=gsob.period_set_name and glp.period_type=gpt.period_type and horu.organization_id=nvl(:$flex$.Operating_Unit,fnd_global.org_id) order by glp.start_date desc Y US Period Name Period Name 3 30 :p_ledger_id LOV Oracle Ledger LOV Y select ledger_id id, name value, null description from gl_ledgers where object_type_code='L' and ledger_id in (select ledger_id from gl_access_set_assignments where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) order by value,description select hou.set_of_books_id from hr_operating_units hou where hou.organization_id = :$flex$.Operating_Unit Y US Ledger Id Ledger Id 4 40 :p_business_unit LOV custom Y select ffvv.flex_value id, ffvv.flex_value value, ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description from fnd_flex_values_vl ffvv where ffvv.flex_value_set_id=1031523 order by ffvv.flex_value Y US Business Unit Business Unit :$flex$.Operating_Unit Operating Unit Ledger Id :$flex$.Operating_Unit Operating Unit Period Name