Advanced ETL Processor Version: 5.8.1.7
Machine:
Oracle DB version: 11.1.0.7.0
Oracle APPS version: 12.1.2
Query: select * from xxcm.xxcm_econ_ap_v
Screen showing the transformation:
The transformers all of them have trim for all fields and that is all.
The object that actually causes the freezing is the reader that says AR.
The freezing is temporary but can last up to a couple of hours (the time it takes for the query to run on SQL Developer)
The freezing occurs under any of the following conditions:
- When opening the transformation
- When opening the reader object
- When running the package
The issue here is I would like to continue working on other stuff while the query loads, even if it takes a lot to get the info.
Contents of the view (please note that I do not intend to have this view optimized, I just need you to help me with Advanced ETL processor so it does not freeze when I am working with this object):
WITH XXCM_TRANSACTION_GL_XLA
AS (SELECT GLB.je_batch_id,
GLB.name gl_bath_name, --1
glh.je_header_id,
glh.posted_date, --2
glh.name gl_header_name, --3
glh.period_name,
gll.je_line_num,
gll.descriptiongl_line_desc, --4
gll.code_combination_id,
gcc.account_type,
gcc.segment1,
gcc.segment5,
gcc.segment6, ---
xah.event_id,
xal.code_combination_idxla_code_combination_id,
xdl.source_distribution_type,
xdl.source_distribution_id_num_1,
xdl.unrounded_accounted_drunrounded_entered_dr,
xdl.unrounded_accounted_crunrounded_entered_cr,
(SELECT a.NATURAL_SIDE_CODE
FROM apps.XLA_ACCT_LINE_TYPES_B a
WHERE 1 = 1 --
AND a.ACCOUNTING_CLASS_CODE =
xal.accounting_class_code
AND a.APPLICATION_ID = '200'
AND a.ENTITY_CODE = xte.entity_code
AND a.EVENT_CLASS_CODE = xdl.event_class_code
AND a.AMB_CONTEXT_CODE = 'DEFAULT'
AND a.ACCOUNTING_LINE_CODE =
xdl.accounting_line_code
AND a.ACCOUNTING_LINE_TYPE_CODE = 'S')
natural_side_code
FROM gl.gl_je_headersglh,
gl.gl_je_linesgll,
gl.gl_je_batches GLB,
gl.gl_import_referencesgir,
gl.gl_code_combinationsgcc,
xla.xla_ae_headersxah,
xla.xla_ae_linesxal,
xla.xla_transaction_entitiesxte,
xla.xla_distribution_linksxdl
WHERE 1 = 1
AND GLL.JE_HEADER_ID = GLH.JE_HEADER_ID
AND GLB.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gir.je_header_id
AND GLL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND gll.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gcc.CODE_COMBINATION_ID = gll.CODE_COMBINATION_ID
AND glh.STATUS = 'P'
AND glh.Actual_flag = 'A'
AND GLH.JE_SOURCE = 'Payables'
AND glh.PERIOD_NAME =
(SELECT meaning
FROM apps.FND_LOOKUP_VALUES
WHERE 1 = 1
AND lookup_type =
'XXCM_ECONT_PERIOD_EXTRACT'
AND lookup_code = 'PERIOD_NAME'
AND (VIEW_APPLICATION_ID = 3)
AND (SECURITY_GROUP_ID = 0)
AND LANGUAGE = 'ESA'
AND ROWNUM = 1)
AND gll.PERIOD_NAME = glh.PERIOD_NAME
AND xdl.application_id = 200 --Payables
AND xah.application_id = xal.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.event_id = xah.event_id
AND XDL.APPLICATION_ID = XAL.APPLICATION_ID
AND xdl.ae_line_num = xal.ae_line_num
AND XAH.APPLICATION_ID = XTE.APPLICATION_ID
AND xah.entity_id = xte.entity_id)
SELECT xtgx.gl_bath_name, --1
xtgx.posted_date, --2
xtgx.gl_header_name, --3
aila.descriptiongl_line_desc, --xtgx.gl_line_desc, --4
aia.description, --5 ---> terminapoliza
aba.batch_name, --6
xtgx.segment5, --7
xtgx.segment6, --8
NULL nom_subcuenta, --9
xtgx.unrounded_entered_dr, --10
xtgx.unrounded_entered_cr, --11
aia.invoice_currency_code, --12
aia.exchange_ratetipo_cambio, --13 ---> terminatransaccion
NULL folio_interno, --14
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.END_EXPENSE_DATE
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.END_EXPENSE_DATE
ELSE
aia.invoice_date
END
Fecha_factura,
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_document_number
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_document_number
ELSE
aia.invoice_num
END
folio_fiscal, --15 folio_fiscal - num_factura
aia.invoice_amount amount, --16 importefactura
CASE
WHEN xtgx.period_name = pag.check_date
THEN
CASE
WHEN pag.payment_method_code IS NULL
THEN
aia.payment_method_code
ELSE
pag.payment_method_code
END
ELSE
NULL
END
payment_method_code, --17 ---> terminacomprobantes
sup.segment1, --18
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_name
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_name
WHEN (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE = 'XXCM_ECON_AP_SITE_CODE_SUP'
AND (VIEW_APPLICATION_ID = 200)
AND lookup_code = sup_site.VENDOR_SITE_CODE
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
UNION
SELECT 'FISCAL'
FROM DUAL
WHERE regexp_like (sup_site.VENDOR_SITE_CODE,'FIS[0-9]{12}$')
)
IS NULL
THEN
(SELECT HPS.attribute1
FROM APPS.HZ_PARTY_SITES HPS
WHERE HPS.PARTY_SITE_ID = aia.PARTY_SITE_ID)
ELSE
sup.vendor_name
END
nom_beneficiario, --19 nom_beneficiario
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.check_number
ELSE NULL
END
check_number, --20
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_name
ELSE NULL
END
bank_name, --21 BANCO_EMISOR
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_number
ELSE NULL
END
bank_number,
CASE
WHEN xtgx.period_name = pag.check_date
THEN
pag.bank_account_name
ELSE
NULL
END
bank_account_name, --22 NUM_CUENTA_ORI
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.cleared_date
ELSE NULL
END
cleared_date, --23 FECHA_CONT_CHEQUE
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.cleared_amount
ELSE NULL
END
cleared_amount, --24 MONTO_CHEQUE
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.exchange_rate
ELSE NULL
END
exchange_rate,
-- pag.check_number, --20
-- pag.bank_name, --21 BANCO_EMISOR
-- pag.bank_account_name, --22 NUM_CUENTA_ORI
-- pag.cleared_date, --23 FECHA_CONT_CHEQUE
-- pag.cleared_amount, --24 MONTO_CHEQUE
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_taxpayer_id
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_taxpayer_id
WHEN (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE = 'XXCM_ECON_AP_SITE_CODE_SUP'
AND (VIEW_APPLICATION_ID = 200)
AND lookup_code = sup_site.VENDOR_SITE_CODE
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
UNION
SELECT 'FISCAL'
FROM DUAL
WHERE regexp_like (sup_site.VENDOR_SITE_CODE,'FIS[0-9]{12}$')
)
IS NULL
THEN
(SELECT HPS.PARTY_SITE_NAME
FROM APPS.HZ_PARTY_SITES HPS
WHERE HPS.PARTY_SITE_ID = aia.PARTY_SITE_ID)
ELSE
sup.standard_industry_class
END
rfc_acreedor, --25 rfc_acreedor
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_account_num
ELSE NULL
END
bank_account_num, --26 cuenta_destino
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_name_r
ELSE NULL
END
bank_name_r, --27 BANCO_RECEPTOR
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_number_r
ELSE NULL
END
bank_number_r, --27 BANCO_RECEPTOR
NULL fecha_timbrado, --28
CASE
WHEN xtgx.period_name = pag.check_date
THEN
CASE
WHEN aia.payment_status_flag = 'Y' THEN 'PAGADO'
WHEN aia.payment_status_flag = 'N' THEN 'NO PAGADO'
WHEN aia.payment_status_flag = 'P' THEN 'PAGO PARCIAL'
END
ELSE
'NO PAGADO'
END
ap_status_pag,
-- pag.bank_account_num, --26 cuenta_destino
-- pag.bank_name_r, --27 BANCO_RECEPTOR
-- NULL fecha_timbrado, --28
-- CASE
-- WHEN aia.payment_status_flag = 'Y' THEN 'PAGADO'
-- WHEN aia.payment_status_flag = 'N' THEN 'NO PAGADO'
-- WHEN aia.payment_status_flag = 'P' THEN 'PAGO PARCIAL'
-- END ap_status_pag,
xtgx.source_distribution_type,
xtgx.source_distribution_id_num_1,
xtgx.SEGMENT1cia,
xtgx.period_name,
aia.gl_date,
aia.invoice_id,
aia.source,
xtgx.account_type,
xtgx.natural_side_code,
aia.invoice_type_lookup_code,
TO_CHAR (NULL) CFD_CBB_NUMFOL,
aida.LINE_TYPE_LOOKUP_CODE,
CASE
WHEN aia.payment_status_flag = 'Y'
THEN
XXCM.get_amount_withheld (aia.invoice_id)
ELSE
XXCM.XXCM_CALC_RETENCION (aia.invoice_id)
END
RETENCION,
xtgx.je_batch_id,
xtgx.je_header_id
FROM xxcm_transaction_gl_xlaxtgx,
ap.ap_invoice_distributions_allaida,
ap.ap_invoice_lines_allaila,
ap.ap_invoices_allaia,
ap.ap_batches_all aba,
ap.ap_suppliers sup,
ap.ap_supplier_sites_allsup_site,
(SELECT aip.invoice_id,
aca.check_number, --NUM_CHEC_DEP
aca.payment_method_code,
br.bank_name, --BANCO_EMISOR----
br.bank_number, --NUM BANCO
ba.bank_account_name, --NUM_CUENTA_ORI---
TO_CHAR (AIP.ACCOUNTING_DATE, 'MON-YYYY') check_date,
-- AIP.ACCOUNTING_DATE ,
-- AIP.AMOUNT ,
AIP.ACCOUNTING_DATE cleared_date, --FECHA_CONT_CHEQUE
aca.amountcleared_amount, --MONTO_CHEQUE
aca.exchange_rate,
ieba.bank_account_num, --cuenta_destino
br2.bank_name bank_name_r, --BANCO_RECEPTOR----
br2.bank_number bank_number_r --NUM BANCO
FROM apps.ap_invoice_payments_allaip,
apps.ap_checks_allaca,
apps.ce_bank_accountsba,
apps.ce_bank_acct_uses_allcbau,
apps.ce_bank_branches_vbr,
apps.ce_bank_branches_v br2,
apps.iby_ext_bank_accountsieba --,
-- apps.iby_ext_banks_vieb,
-- apps.iby_ext_bank_branches_viebb
WHERE 1 = 1
AND aip.check_id = aca.check_id
AND aca.ce_bank_acct_use_id = cbau.bank_acct_use_id
AND cbau.bank_account_id = ba.bank_account_id
AND ba.bank_branch_id = br.branch_party_id
AND aca.external_bank_account_id =
ieba.ext_bank_account_id(+)
-- AND ieb.bank_party_id = iebb.bank_party_id
-- AND ieba.branch_id = iebb.branch_party_id
-- AND ieba.bank_id = ieb.bank_party_id
AND ieba.branch_id = br2.branch_party_id(+)
AND aca.status_lookup_code != 'VOIDED'
AND ba.bank_branch_id != 104045) pag
WHERE xtgx.source_distribution_type = 'AP_INV_DIST'
AND xtgx.source_distribution_id_num_1 =
aida.invoice_distribution_id
AND aida.INVOICE_LINE_NUMBER = aila.LINE_NUMBER
AND aida.invoice_id = aia.invoice_id
AND aila.invoice_id = aia.invoice_id
AND aba.batch_id = aia.batch_id
AND aia.vendor_id = sup.vendor_id
AND aia.vendor_site_id = sup_site.vendor_site_id
AND aia.invoice_id = pag.invoice_id(+)
UNION ALL
SELECT xtgx.gl_bath_name, --1
xtgx.posted_date, --2
xtgx.gl_header_name, --3
aila.descriptiongl_line_desc, --xtgx.gl_line_desc, --4
aia.description, --5 ---> terminapoliza
aba.batch_name, --6
xtgx.segment5, --7
xtgx.segment6, --8
NULL nom_subcuenta, --9
xtgx.unrounded_entered_dr, --10
xtgx.unrounded_entered_cr, --11
aia.invoice_currency_code, --12
aia.exchange_ratetipo_cambio, --13 ---> terminatransaccion
NULL folio_interno, --14
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.END_EXPENSE_DATE
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.END_EXPENSE_DATE
ELSE
aia.invoice_date
END
Fecha_factura,
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_document_number
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_document_number
ELSE
aia.invoice_num
END
folio_fiscal, --15 folio_fiscal - num_factura
aia.invoice_amount amount, --16 importefactura
pag.payment_method_code, --17 ---> terminacomprobantes
sup.segment1, --18
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_name
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_name
WHEN (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE = 'XXCM_ECON_AP_SITE_CODE_SUP'
AND (VIEW_APPLICATION_ID = 200)
AND lookup_code = sup_site.VENDOR_SITE_CODE
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
UNION
SELECT 'FISCAL'
FROM DUAL
WHERE regexp_like (sup_site.VENDOR_SITE_CODE,'FIS[0-9]{12}$')
)
IS NULL
THEN
(SELECT HPS.attribute1
FROM APPS.HZ_PARTY_SITES HPS
WHERE HPS.PARTY_SITE_ID = aia.PARTY_SITE_ID)
ELSE
sup.vendor_name
END
nom_beneficiario, --19 nom_beneficiario
pag.check_number, --20
pag.bank_name, --21 BANCO_EMISOR
pag.bank_number,
pag.bank_account_name, --22 NUM_CUENTA_ORI
pag.cleared_date, --23 FECHA_CONT_CHEQUE
pag.cleared_amount, --24 MONTO_CHEQUE
pag.exchange_rate,
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_taxpayer_id
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_taxpayer_id
WHEN (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE = 'XXCM_ECON_AP_SITE_CODE_SUP'
AND (VIEW_APPLICATION_ID = 200)
AND lookup_code = sup_site.VENDOR_SITE_CODE
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
UNION
SELECT 'FISCAL'
FROM DUAL
WHERE regexp_like (sup_site.VENDOR_SITE_CODE,'FIS[0-9]{12}$')
)
IS NULL
THEN
(SELECT HPS.PARTY_SITE_NAME
FROM APPS.HZ_PARTY_SITES HPS
WHERE HPS.PARTY_SITE_ID = aia.PARTY_SITE_ID)
ELSE
sup.standard_industry_class
END
rfc_acreedor, --25 rfc_acreedor
pag.bank_account_num, --26 cuenta_destino
pag.bank_name_r, --27 BANCO_RECEPTOR----
pag.bank_number_r,
NULL fecha_timbrado, --28
CASE
WHEN aia.payment_status_flag = 'Y' THEN 'PAGADO'
WHEN aia.payment_status_flag = 'N' THEN 'NO PAGADO'
WHEN aia.payment_status_flag = 'P' THEN 'PAGO PARCIAL'
END
ap_status_pag,
xtgx.source_distribution_type,
xtgx.source_distribution_id_num_1,
xtgx.SEGMENT1cia,
xtgx.period_name,
AIA.GL_DATE,
aia.invoice_id,
aia.source,
xtgx.account_type,
XTGX.NATURAL_SIDE_CODE,
aia.invoice_type_lookup_code,
TO_CHAR (NULL) CFD_CBB_NUMFOL,
aida.LINE_TYPE_LOOKUP_CODE,
XXCM.get_amount_withheld (aia.invoice_id) RETENCION,
xtgx.je_batch_id,
xtgx.je_header_id
FROM XXCM_TRANSACTION_GL_XLA xtgx,
ap.ap_payment_hist_distsaphd,
ap.ap_payment_history_allaph,
ap.ap_invoice_distributions_allaida,
ap.ap_invoice_lines_allaila,
ap.ap_invoices_allaia,
ap.ap_batches_all aba,
ap.ap_suppliers sup,
ap.ap_supplier_sites_allsup_site,
(SELECT aca.check_id,
aca.check_number, --NUM_CHEC_DEP
aca.payment_method_code,
br.bank_name, --BANCO_EMISOR
br.bank_number, --NUM BANCO
ba.bank_account_name, --NUM_CUENTA_ORI
aca.check_datecleared_date, --FECHA_CONT_CHEQUE
aca.amountcleared_amount, --MONTO_CHEQUE
aca.exchange_rate,
ieba.bank_account_num, --cuenta_destino
br2.bank_name bank_name_r, --BANCO_RECEPTOR
br2.bank_number bank_number_r --NUM BANCO
FROM apps.ap_checks_allaca,
apps.ce_bank_accountsba,
apps.ce_bank_acct_uses_allcbau,
apps.ce_bank_branches_vbr,
apps.ce_bank_branches_v br2,
apps.iby_ext_bank_accountsieba
-- ,apps.iby_ext_banks_vieb,
-- apps.iby_ext_bank_branches_viebb
WHERE 1 = 1
AND aca.ce_bank_acct_use_id = cbau.bank_acct_use_id
AND cbau.bank_account_id = ba.bank_account_id
AND ba.bank_branch_id = br.branch_party_id
AND aca.external_bank_account_id =
ieba.ext_bank_account_id(+)
-- AND ieb.bank_party_id = iebb.bank_party_id
-- AND ieba.branch_id = iebb.branch_party_id
-- AND ieba.bank_id = ieb.bank_party_id
AND ieba.branch_id = br2.branch_party_id(+)) pag
WHERE xtgx.source_distribution_type = 'AP_PMT_DIST'
AND xtgx.source_distribution_id_num_1 = aphd.payment_hist_dist_id
AND aida.invoice_distribution_id = aphd.invoice_distribution_id
AND aph.payment_history_id = aphd.payment_history_id
AND aida.invoice_line_number = aila.line_number
AND aida.invoice_id = aia.invoice_id
AND aila.invoice_id = aia.invoice_id
AND aba.batch_id = aia.batch_id
AND aia.vendor_id = sup.vendor_id
AND aia.vendor_site_id = sup_site.vendor_site_id
AND aph.check_id = pag.check_id
UNION ALL
SELECT xtgx.gl_bath_name, --1
xtgx.posted_date, --2
xtgx.gl_header_name, --3
aila.descriptiongl_line_desc, --xtgx.gl_line_desc, --4
aia.description, --5 ---> terminapoliza
aba.batch_name, --6
xtgx.segment5, --7
xtgx.segment6, --8
NULL nom_subcuenta, --9
xtgx.unrounded_entered_dr, --10
xtgx.unrounded_entered_cr, --11
aia.invoice_currency_code, --12
aia.exchange_ratetipo_cambio, --13 -> terminatransaccion
NULL folio_interno, --14
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.END_EXPENSE_DATE
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.END_EXPENSE_DATE
ELSE
aia.invoice_date
END
Fecha_factura,
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_document_number
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_document_number
ELSE
aia.invoice_num
END
folio_fiscal, --15 folio_fiscal - num_factura
aia.invoice_amount amount, --16 importefactura
CASE
WHEN xtgx.period_name = pag.check_date
THEN
CASE
WHEN pag.payment_method_code IS NULL
THEN
aia.payment_method_code
ELSE
pag.payment_method_code
END
ELSE
NULL
END
payment_method_code, --17 -> terminacomprobantes
sup.segment1, --18
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_name
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_name
WHEN (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE = 'XXCM_ECON_AP_SITE_CODE_SUP'
AND (VIEW_APPLICATION_ID = 200)
AND lookup_code = sup_site.VENDOR_SITE_CODE
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
UNION
SELECT 'FISCAL'
FROM DUAL
WHERE regexp_like (sup_site.VENDOR_SITE_CODE,'FIS[0-9]{12}$')
)
IS NULL
THEN
(SELECT HPS.attribute1
FROM APPS.HZ_PARTY_SITES HPS
WHERE HPS.PARTY_SITE_ID = aia.PARTY_SITE_ID)
ELSE
sup.vendor_name
END
nom_beneficiario, --19 nom_beneficiario
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.check_number
ELSE NULL
END
check_number, --20
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_name
ELSE NULL
END
bank_name, --21 BANCO_EMISOR
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_number
ELSE NULL
END
bank_number,
CASE
WHEN xtgx.period_name = pag.check_date
THEN
pag.bank_account_name
ELSE
NULL
END
bank_account_name, --22 NUM_CUENTA_ORI
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.cleared_date
ELSE NULL
END
cleared_date, --23 FECHA_CONT_CHEQUE
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.cleared_amount
ELSE NULL
END
cleared_amount, --24 MONTO_CHEQUE
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.exchange_rate
ELSE NULL
END
exchange_rate,
-- pag.check_number, --20
-- pag.bank_name, --21 BANCO_EMISOR
-- pag.bank_account_name, --22 NUM_CUENTA_ORI
-- pag.cleared_date, --23 FECHA_CONT_CHEQUE
-- pag.cleared_amount, --24 MONTO_CHEQUE
CASE
WHEN aia.invoice_type_lookup_code = 'EXPENSE REPORT'
AND sup.vendor_type_lookup_code = 'EMPLOYEE'
THEN
aila.merchant_taxpayer_id
WHEN ( aia.invoice_type_lookup_code = 'EXPENSE REPORT'
OR aia.invoice_type_lookup_code = 'STANDARD')
AND (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE =
'XXCM_FE_EXEPC_ACREEDOR_INF_GST'
AND lookup_code = sup.segment1
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y')
IS NOT NULL
THEN
aila.merchant_taxpayer_id
WHEN (SELECT MEANING
FROM APPS.fnd_lookup_values
WHERE LOOKUP_TYPE = 'XXCM_ECON_AP_SITE_CODE_SUP'
AND lookup_code = sup_site.VENDOR_SITE_CODE
AND (VIEW_APPLICATION_ID = 200)
AND LANGUAGE = 'ESA'
AND ENABLED_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
UNION
SELECT 'FISCAL'
FROM DUAL
WHERE regexp_like (sup_site.VENDOR_SITE_CODE,'FIS[0-9]{12}$')
)
IS NULL
THEN
(SELECT HPS.PARTY_SITE_NAME
FROM APPS.HZ_PARTY_SITES HPS
WHERE HPS.PARTY_SITE_ID = aia.PARTY_SITE_ID)
ELSE
sup.standard_industry_class
END
rfc_acreedor, --25 rfc_acreedor
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_account_num
ELSE NULL
END
bank_account_num, --26 cuenta_destino
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_name_r
ELSE NULL
END
bank_name_r, --27 BANCO_RECEPTOR
CASE
WHEN xtgx.period_name = pag.check_date THEN pag.bank_number_r
ELSE NULL
END
bank_number_r,
NULL fecha_timbrado, --28
CASE
WHEN xtgx.period_name = pag.check_date
THEN
CASE
WHEN aia.payment_status_flag = 'Y' THEN 'PAGADO'
WHEN aia.payment_status_flag = 'N' THEN 'NO PAGADO'
WHEN aia.payment_status_flag = 'P' THEN 'PAGO PARCIAL'
END
ELSE
'NO PAGADO'
END
ap_status_pag,
-- pag.bank_account_num, --26 cuenta_destino
-- pag.bank_name_r, --27 BANCO_RECEPTOR
-- NULL fecha_timbrado, --28
-- CASE
-- WHEN aia.payment_status_flag = 'Y' THEN 'PAGADO'
-- WHEN aia.payment_status_flag = 'N' THEN 'NO PAGADO'
-- WHEN aia.payment_status_flag = 'P' THEN 'PAGO PARCIAL'
-- END ap_status_pag,
xtgx.source_distribution_type,
xtgx.source_distribution_id_num_1,
xtgx.segment1cia,
xtgx.period_name,
aia.gl_date,
aia.invoice_id,
aia.source,
xtgx.account_type,
xtgx.natural_side_code,
aia.invoice_type_lookup_code,
TO_CHAR (NULL) CFD_CBB_NUMFOL,
aida.LINE_TYPE_LOOKUP_CODE,
CASE
WHEN aia.payment_status_flag = 'Y'
THEN
XXCM.get_amount_withheld (aia.invoice_id)
ELSE
XXCM.XXCM_CALC_RETENCION (aia.invoice_id)
END
RETENCION,
xtgx.je_batch_id,
xtgx.je_header_id
FROM XXCM_TRANSACTION_GL_XLA xtgx,
ap.ap_prepay_app_distsapad,
ap.ap_invoice_distributions_allaida,
ap.ap_invoice_lines_allaila,
ap.ap_invoices_allaia,
ap.ap_batches_all aba,
ap.ap_suppliers sup,
ap.ap_supplier_sites_allsup_site,
(SELECT aip.invoice_id,
aca.check_number, --NUM_CHEC_DEP
aca.payment_method_code, --
br.bank_name, --BANCO_EMISOR
br.bank_number, --NUM BANCO
ba.BANK_ACCOUNT_NAME, --NUM_CUENTA_ORI
TO_CHAR (AIP.ACCOUNTING_DATE, 'MON-YYYY') check_date,
-- AIP.ACCOUNTING_DATE ,
-- AIP.AMOUNT ,
AIP.ACCOUNTING_DATE cleared_date, --FECHA_CONT_CHEQUE
aca.amountcleared_amount, --MONTO_CHEQUE
aca.exchange_rate,
ieba.bank_account_num, --cuenta_destino
br2.bank_name bank_name_r, --BANCO_RECEPTOR
br2.bank_number bank_number_r --NUM BANCO
FROM apps.ap_invoice_payments_allaip,
apps.ap_checks_allaca,
apps.ce_bank_accountsba,
apps.ce_bank_acct_uses_allcbau,
apps.ce_bank_branches_vbr,
apps.ce_bank_branches_v br2,
apps.iby_ext_bank_accountsieba --,
-- apps.iby_ext_banks_vieb,
-- apps.iby_ext_bank_branches_viebb
WHERE 1 = 1
AND aip.check_id = aca.check_id
AND aca.ce_bank_acct_use_id = cbau.bank_acct_use_id
AND cbau.bank_account_id = ba.bank_account_id
AND ba.bank_branch_id = br.branch_party_id
AND aca.external_bank_account_id =
ieba.ext_bank_account_id(+)
-- AND ieb.bank_party_id = iebb.bank_party_id
-- AND ieba.branch_id = iebb.branch_party_id
-- AND ieba.bank_id = ieb.bank_party_id
AND ieba.branch_id = br2.branch_party_id(+)
AND aca.status_lookup_code != 'VOIDED') pag
WHERE xtgx.source_distribution_type = 'AP_PREPAY'
AND xtgx.source_distribution_id_num_1 = apad.prepay_app_dist_id
-- AND apad.prepay_app_distribution_id = aida.invoice_distribution_id
AND apad.invoice_distribution_id = aida.invoice_distribution_id
AND aida.invoice_line_number = aila.line_number
AND aida.invoice_id = aia.invoice_id
AND aila.invoice_id = aia.invoice_id
AND aba.batch_id = aia.batch_id
AND aia.vendor_id = sup.vendor_id
AND aia.vendor_site_id = sup_site.vendor_site_id
AND AIA.INVOICE_ID = PAG.INVOICE_ID(+)