WITH
entity_sec_hrchy as(
select regexp_substr (val, '[^|]+', 1, 1) level0
,regexp_substr (val, '[^|]+', 1, 2) level1
from (select ltrim(sys_connect_by_path(pk1_start_value, '|'), '|') val
from (select ftn.pk1_start_value
,ftn.parent_pk1_value
from fnd_tree_node ftn
,fnd_tree_version_tl ftvt
where 1 = 1
and ftn.tree_version_id = ftvt.tree_version_id
and ftvt.tree_version_name = 'XX_ENTITY_SECURITY_HIERARCHY'
and ftvt.tree_code = 'XX_ENTITY_SECURITY_HIERARCHY'
and ftvt.tree_structure_code = 'GL_ACCT_FLEX'
and ftvt.language = 'US')
where connect_by_isleaf = 1
start with parent_pk1_value is null
connect by nocycle parent_pk1_value = prior pk1_start_value)
),
data_acc_set as(
select gllv.ledger_id
,gllv.ledger_name
,entity_sec_hrchy.level0
,entity_sec_hrchy.level1 flex_segment_value
,gllv.legal_entity_id
,gllv.legal_entity_name
,null ledger_set_name
from entity_sec_hrchy
,gl_legal_entities_bsvs gleb
,gl_ledger_le_v gllv
,fun_user_role_data_asgnmnts furda
,gl_access_set_norm_assign gasna
where 1 = 1
and gllv.legal_entity_id = gleb.legal_entity_id
and gleb.flex_segment_value = entity_sec_hrchy.level1
and furda.user_guid = FND_GLOBAL.USER_GUID
and gasna.access_set_id = furda.access_set_id
and furda.active_flag = 'Y'
and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))
and gasna.segment_value = entity_sec_hrchy.level0
union all
select distinct gl.ledger_id
,gllv.ledger_name
,null
,gleb.flex_segment_value
,gllv.legal_entity_id
,gllv.legal_entity_name
,null ledger_set_name
from fun_user_role_data_asgnmnts furda
,gl_access_set_norm_assign gasa
,gl_ledgers gl
,gl_ledger_le_v gllv
,gl_legal_entities_bsvs gleb
,gl_access_sets gas
where furda.user_guid = FND_GLOBAL.USER_GUID
and gasa.access_set_id = furda.access_set_id
and furda.active_flag = 'Y'
and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))
and gasa.ledger_id = gl.ledger_id
and gl.object_type_code = 'L'
and gllv.legal_entity_id = gleb.legal_entity_id
and gasa.access_set_id = gas.access_set_id
and gas.security_segment_code = 'F'
and gleb.legal_entity_id = gllv.legal_entity_id
and gllv.ledger_id = gasa.ledger_id
union all
select distinct gls1.ledger_id
,gls1.name
,null
,gleb.flex_segment_value
,gllv.legal_entity_id
,gllv.legal_entity_name
,gl.name ledger_set_name
from fun_user_role_data_asgnmnts furda
,gl_access_set_norm_assign gasa
,gl_ledgers gl
,gl_ledger_set_assignments glsa
,gl_ledgers gls1
,gl_legal_entities_bsvs gleb
,gl_access_sets gas
,gl_ledger_le_v gllv
where furda.user_guid = FND_GLOBAL.USER_GUID
and gasa.access_set_id = furda.access_set_id
and furda.active_flag = 'Y'
and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))
and gasa.ledger_id = gl.ledger_id
and gasa.all_segment_value_flag = 'Y'
and gl.ledger_id = glsa.ledger_set_id
and gls1.ledger_id = glsa.ledger_id
and gasa.access_set_id = gas.access_set_id
and gas.security_segment_code = 'F'
and gleb.legal_entity_id = gllv.legal_entity_id
and gllv.ledger_id = gls1.ledger_id
),
DYN_GL_XX_DATA AS
(SELECT glg.NAME LEDGER,
gjh.period_name PERIOD,
gjh.je_source,
gjh.je_category,
gjh.doc_sequence_value DOC_SEQ,
TO_CHAR(gjh.default_effective_date, 'DD-MON-YYYY','nls_date_language=american') ACCOUNTING_DATE,
TO_CHAR(gjh.posted_date, 'DD-MON-YYYY','nls_date_language=american') POSTED_DATE,
data_acc_set.flex_segment_value accounting_entity,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 1, data_acc_set.flex_segment_value) accounting_entity_desc,
gcc.segment2 cost_centre,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 2, gcc.segment2) cost_centre_desc,
gcc.segment3 nominal_account,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 3, gcc.segment3) nominal_account_desc,
gcc.segment4 sub_analysis,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 4, gcc.segment4) sub_analysis_desc,
gcc.segment5 intercompany,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 5, gcc.segment5) intercompany_desc,
gcc.segment6 location,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 6, gcc.segment6) location_desc,
gcc.segment7 project,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 7, gcc.segment7) project_desc,
gcc.segment8 flow,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 8, gcc.segment8) flow_desc,
gcc.segment9 spare1,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 9, gcc.segment9) spare1_desc,
gcc.segment10 spare2,
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 10, gcc.segment10) spare2_desc,
gcc.code_combination_id,
gjl.currency_code ENT_CUR,
gjl.entered_dr ENT_DR,
gjl.entered_cr ENT_CR,
NVL(gjl.entered_dr,0)-NVL(gjl.entered_cr,0) ENT_NET,
gjl.accounted_dr ACCTD_DR,
gjl.accounted_cr ACCTD_CR,
NVL(gjl.accounted_dr,0)-NVL(gjl.accounted_cr,0) ACCTD_NET,
xal.accounting_class_code ACTCLS_CODE,
xal.entered_dr XENTERED_DR,
xal.entered_cr XENTERED_CR,
xal.accounted_dr XACCOUNTED_DR,
xal.accounted_cr XACCOUNTED_CR,
nvl(xal.currency_conversion_type,gjl.currency_conversion_type) CUR_CONV_TYPE,
TO_CHAR(nvl(xal.currency_conversion_date,gjl.currency_conversion_date), 'DD-MON-YYYY','nls_date_language=american') CUR_CONV_DATE,
nvl(xal.currency_conversion_rate,gjl.currency_conversion_rate) CUR_CONV_RATE,
gjb.name BATCH,
gjb.description BATCH_DESC,
gjh.name JOUR_NAME,
gjh.description JOUR_DESC,
gjl.description JOURLINE_DESC,
gjh.created_by JOUR_PREP,
(SELECT user_id FROM gl_je_action_log WHERE je_batch_id = gjb.je_batch_id AND action_code = 'POSTED') POSTED_BY,
fsv_acc_seq.header_name ACC_SEQUENCE_NAME,
gjh.posting_acct_seq_value ACC_SEQUENCE_NUMBER,
fsv_rep_seq.header_name REP_SEQUENCE_NAME,
gjh.close_acct_seq_value REP_SEQUENCE_NUMBER,
gjl.je_line_num JOURNAL_LINE_NUMBER,
(SELECT display_name FROM per_person_names_f WHERE person_id = gjb.approver_employee_id AND name_type = 'GLOBAL') JOUR_APPR,
--DECODE(gjh.accrual_rev_effective_date, NULL, 'N', 'Y') REV_FLAG,
decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') REV_FLAG,
TO_CHAR(gjh.accrual_rev_effective_date, 'DD-MON-YYYY','nls_date_language=american') REV_DATE,
xev.entity_id,
xah.ae_header_id,
gjl.REFERENCE_1 GL_REF1,
xal.accounting_class_code,
xal.ae_line_num
FROM xla_events xev,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
fun_seq_versions fsv_acc_seq,
fun_seq_versions fsv_rep_seq,
gl_je_lines gjl,
gl_ledgers glg,
gl_je_batches gjb,
gl_code_combinations gcc,
gl_je_sources gjss,
gl_je_categories gjcc,
data_acc_set
WHERE 1 =1
AND xah.event_id = xev.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.gl_transfer_status_code= 'Y'
AND xal.gl_sl_link_id =gir.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gjl.je_header_id =gjh.je_header_id
AND gjh.je_header_id =gir.je_header_id
AND gjl.je_header_id =gir.je_header_id
AND gir.je_line_num =gjl.je_line_num
AND glg.ledger_id = gjh.ledger_id
AND gjh.je_batch_id = gjb.je_batch_id
--AND GCC.SEGMENT3 = '5002002'
--and gjb.je_batch_id = gjal_ct.je_batch_id
AND gcc.code_combination_id =xal.code_combination_id
AND gcc.code_combination_id =gjl.code_combination_id
and gjh.posting_acct_seq_version_id = fsv_acc_seq.seq_version_id(+)
and gjh.close_acct_seq_version_id = fsv_rep_seq.seq_version_id(+)
AND gjss.je_source_name = gjh.je_source
AND gjcc.je_category_name = gjh.je_category
AND gjss.LANGUAGE = USERENV('LANG')
AND gjcc.LANGUAGE = USERENV('LANG')
AND gjh.status ='P'
AND gjh.actual_flag ='A'
AND gjh.je_source IN ('Payables','Receivables','Cash Management','Assets')
--AND glg.NAME = :P_LEDGER
and (data_acc_set.ledger_name = :p_ledger
or data_acc_set.ledger_set_name = :p_ledger)
and glg.ledger_id = data_acc_set.ledger_id
and ((coalesce(null, :p_acct_entity) is null)
or (data_acc_set.flex_segment_value in (:p_acct_entity)))
and ((coalesce(null, :p_cost_centre) is null)
or (gcc.segment2 in (:p_cost_centre)))
and ((coalesce(null, :p_account) is null)
or (gcc.segment3 in (:p_account)))
and ((coalesce(null, :p_sub_analysis) is null)
or (gcc.segment4 in (:p_sub_analysis)))
and ((coalesce(null, :p_intercmpny) is null)
or (gcc.segment5 in (:p_intercmpny)))
and ((coalesce(null, :p_location) is null)
or (gcc.segment6 in (:p_location)))
and ((coalesce(null, :p_project) is null)
or (gcc.segment7 in (:p_project)))
and ((coalesce(null, :p_flow) is null)
or (gcc.segment8 in (:p_flow)))
and ((coalesce(null, :p_spare1) is null)
or (gcc.segment9 in (:p_spare1)))
and ((coalesce(null, :p_spare2) is null)
or (gcc.segment10 in (:p_spare2)))
and ((coalesce(null, :P_JE_SOURCE) is null)
or (gjss.user_je_source_name in (:P_JE_SOURCE)))
and ((coalesce(null, :P_JE_CATEGORY) is null)
or (gjcc.user_je_category_name in (:P_JE_CATEGORY)))
and gjh.period_name in (select period_name
from gl_periods
where start_date >= (select start_date
from gl_periods
where period_name = :P_PERIOD_FROM
and period_set_name = 'XX_GLOBAL_CAL')
and end_date <= (select end_date
from gl_periods
where period_name = :P_PERIOD_TO
and period_set_name = 'XX_GLOBAL_CAL')
and period_set_name = 'XX_GLOBAL_CAL')
),
DYN_INVOICE_TYPE AS
(SELECT MEANING,
LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='INVOICE TYPE'
AND LANGUAGE =USERENV('LANG')
),
DYN_VENDOR_TYPE AS
(SELECT MEANING,
LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='POZ_VENDOR_TYPE'
AND LANGUAGE =USERENV('LANG')
),
DYN_INVOICE_LINE_TYPE AS
(SELECT MEANING,
LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='INVOICE LINE TYPE'
AND LANGUAGE =USERENV('LANG')
)
--Purchase Invoices
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,
aia.invoice_num AP_INV_NUMBER,
(SELECT MEANING
FROM DYN_INVOICE_TYPE
WHERE LOOKUP_CODE = AIA.INVOICE_TYPE_LOOKUP_CODE
) AP_INV_TYPE,
aia.source AP_SOURCE,
TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') AP_INV_DATE,
aia.invoice_amount AP_INVOICE_AMOUNT,
hp.party_name AP_SUP_NAME,
aps.segment1 AP_SUP_NUM,
poss.vendor_site_code AP_SUPSITE,
(SELECT MEANING
FROM DYN_VENDOR_TYPE
WHERE LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE
) AP_SUPTYPE,
(SELECT MAX(PU.USERNAME)
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
PER_USERS PU
WHERE aid.invoice_id = ail.invoice_id
and aid.invoice_distribution_id = xdl.source_distribution_id_num_1
and aia.invoice_id = ail.invoice_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and PU.PERSON_ID=ail.requester_id
) AP_REQUESTOR,
aia.description AP_INVDESC,
(SELECT MAX(ail.description)
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
WHERE aid.invoice_id = ail.invoice_id
and aid.invoice_distribution_id = xdl.source_distribution_id_num_1
and aia.invoice_id = ail.invoice_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVLINEDESC,
(SELECT MAX(dtf.MEANING)
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl,
DYN_INVOICE_LINE_TYPE dtf
WHERE aid.invoice_id = ail.invoice_id
and aid.invoice_distribution_id = xdl.source_distribution_id_num_1
and aia.invoice_id = ail.invoice_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and dtf.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVLINE_TYPE,
(SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=AIA.PO_HEADER_ID
) AP_PO,
(SELECT MAX(prha.requisition_number)
FROM po_distributions_all pda ,
por_req_distributions_all prda ,
por_requisition_lines_all prla ,
por_requisition_headers_all prha
WHERE aia.po_header_id = pda.po_header_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
) AP_POREQ,
(SELECT LISTAGG(rsh.RECEIPT_NUM,'|') WITHIN GROUP (
ORDER BY rsh.RECEIPT_NUM)
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id=rsl.shipment_header_id
AND rsl.po_header_id = aia.po_header_id
) AP_RECEIPT,
(SELECT LISTAGG(check_number,'|') WITHIN GROUP (ORDER BY check_number)
FROM (SELECT aca.check_number,sum(aipa.amount) AMT
FROM ap_checks_all aca,
ap_invoice_payments_all aipa
WHERE aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
GROUP BY aca.check_number)
WHERE AMT!=0 -- To exclude cancelled checks
) AP_PMT,
att.name AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
to_char(aia.terms_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
ap_invoices_all aia,
xla_transaction_entities xte,
poz_suppliers aps,
ap_terms_tl att,
poz_supplier_sites_all_m poss,
hz_parties hp
WHERE 1 =1
AND aia.invoice_id = xte.source_id_int_1
AND aps.party_id = hp.party_id(+)
AND aia.vendor_id =aps.vendor_id(+)
AND aia.vendor_site_id = poss.vendor_site_id(+)
AND aps.vendor_id = poss.vendor_id(+)
and aia.terms_id = att.term_id
and att.language = 'US'
AND DGSD.entity_id = xte.entity_id
AND xte.entity_code = 'AP_INVOICES'
AND DGSD.je_source = 'Payables'
AND DGSD.je_category = 'Purchase Invoices'
UNION ALL
--Payments
SELECT DISTINCT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,
(SELECT max(aia.invoice_num)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INV_NUMBER,
(SELECT max(dff.MEANING)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
DYN_INVOICE_TYPE dff
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and AIA.INVOICE_TYPE_LOOKUP_CODE = dff.LOOKUP_CODE
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INV_TYPE,
(SELECT max(aia.source)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_SOURCE,
(SELECT max(TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') )
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INV_DATE,
(SELECT max(aia.invoice_amount)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVOICE_AMOUNT,
(SELECT max(hp.party_name)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
hz_parties hp,
poz_suppliers aps
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
and aps.party_id = hp.party_id
) AP_SUP_NAME,
(SELECT max(aps.segment1)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
poz_suppliers aps
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
) AP_SUP_NUM,
(SELECT max(poss.vendor_site_code)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
poz_suppliers aps,
poz_supplier_sites_all_m poss
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
and poss.vendor_id = aps.vendor_id
AND poss.vendor_site_id = aia.vendor_site_id
) AP_SUPSITE,
(SELECT max(dff.MEANING)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
poz_suppliers aps,
DYN_VENDOR_TYPE dff
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
and dff.LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE
) AP_SUPTYPE,
(SELECT max(pu.username)
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
PER_USERS pu
where aid.invoice_id = ail.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and PU.PERSON_ID=ail.requester_id
) AP_REQUESTOR,
(SELECT max(aia.description)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVDESC,
(SELECT max(ail.description)
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
where aid.invoice_id = ail.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVLINEDESC,
(SELECT max(dff.MEANING)
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
DYN_INVOICE_LINE_TYPE dff
where aid.invoice_id = ail.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and dff.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE
) AP_INVLINE_TYPE,
(SELECT max(pha.SEGMENT1)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl,
PO_HEADERS_ALL pha
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and pha.PO_HEADER_ID=AIA.PO_HEADER_ID
) AP_PO,
(SELECT max(prha.requisition_number)
FROM po_headers_all poh,
po_distributions_all pda ,
por_req_distributions_all prda ,
por_requisition_lines_all prla ,
por_requisition_headers_all prha ,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
WHERE poh.po_header_id = pda.po_header_id
AND poh.po_header_id = aia.po_header_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
and aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_POREQ,
(SELECT LISTAGG(RECEIPT_NUM,'|') WITHIN GROUP (
ORDER BY RECEIPT_NUM)
FROM
(SELECT rsh.RECEIPT_NUM
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl
WHERE rsh.shipment_header_id=rsl.shipment_header_id
AND rsl.po_header_id = aia.po_header_id
and aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1
and xdl.applied_to_distribution_type = 'AP_INV_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
GROUP BY rsh.RECEIPT_NUM)
) AP_RECEIPT,
TO_CHAR(aca.check_number) AP_PMT,
NULL AP_PAYMENT_TERMS,
--to_char(aps.due_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
to_char(aca.check_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DATE,
flv.meaning AP_PAYMENT_STATUS,
ieba.bank_account_num AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_payment_schedules_all aps,
fnd_lookup_values flv,
iby_ext_bank_accounts ieba,
xla_transaction_entities xte
WHERE 1 =1
AND aca.check_id = xte.source_id_int_1
AND DGSD.entity_id = xte.entity_id
and aipa.check_id = aca.check_id(+)
and aps.invoice_id = aipa.invoice_id
and aca.status_lookup_code = flv.lookup_code(+)
and aca.external_bank_account_id = ieba.ext_bank_account_id(+)
and flv.lookup_type (+)= 'CHECK STATE'
and flv.language (+)= 'US'
AND DGSD.je_source = 'Payables'
AND DGSD.je_category = 'Payments'
AND xte.entity_code = 'AP_PAYMENTS'
UNION ALL
--Reconciled Payments
SELECT DISTINCT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,
(SELECT max(aia.invoice_num)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INV_NUMBER,
(SELECT max(dff.MEANING)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
DYN_INVOICE_TYPE dff,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and AIA.INVOICE_TYPE_LOOKUP_CODE = dff.LOOKUP_CODE
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INV_TYPE,
(SELECT max(aia.source)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_SOURCE,
(SELECT max(TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') )
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INV_DATE,
(SELECT max(aia.invoice_amount)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVOICE_AMOUNT,
(SELECT max(hp.party_name)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
hz_parties hp,
poz_suppliers aps,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
and aps.party_id = hp.party_id
) AP_SUP_NAME,
(SELECT max(aps.segment1)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
poz_suppliers aps,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
) AP_SUP_NUM,
(SELECT max(poss.vendor_site_code)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
poz_suppliers aps,
poz_supplier_sites_all_m poss ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
and poss.vendor_id = aps.vendor_id
AND poss.vendor_site_id = aia.vendor_site_id
) AP_SUPSITE,
(SELECT max(dff.MEANING)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
poz_suppliers aps,
DYN_VENDOR_TYPE dff ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and aps.vendor_id = aia.vendor_id
and dff.LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE
) AP_SUPTYPE,
(SELECT max(pu.username)
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
PER_USERS pu ,
ap_payment_hist_dists aipd
where aid.invoice_id = ail.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and PU.PERSON_ID=ail.requester_id
) AP_REQUESTOR,
(SELECT max(aia.description)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVDESC,
(SELECT max(ail.description)
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
where aid.invoice_id = ail.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_INVLINEDESC,
(SELECT max(dff.MEANING)
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
DYN_INVOICE_LINE_TYPE dff ,
ap_payment_hist_dists aipd
where aid.invoice_id = ail.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and dff.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE
) AP_INVLINE_TYPE,
(SELECT max(pha.SEGMENT1)
from ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl,
PO_HEADERS_ALL pha ,
ap_payment_hist_dists aipd
where aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
and pha.PO_HEADER_ID=AIA.PO_HEADER_ID
) AP_PO,
(SELECT max(prha.requisition_number)
FROM po_headers_all poh,
po_distributions_all pda ,
por_req_distributions_all prda ,
por_requisition_lines_all prla ,
por_requisition_headers_all prha ,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
WHERE poh.po_header_id = pda.po_header_id
AND poh.po_header_id = aia.po_header_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
and aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
) AP_POREQ,
(SELECT LISTAGG(RECEIPT_NUM,'|') WITHIN GROUP (
ORDER BY RECEIPT_NUM)
FROM
(SELECT rsh.RECEIPT_NUM
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla_distribution_links xdl ,
ap_payment_hist_dists aipd
WHERE rsh.shipment_header_id=rsl.shipment_header_id
AND rsl.po_header_id = aia.po_header_id
and aia.invoice_id = ail.invoice_id
and ail.line_number = aid.invoice_line_number
and aid.invoice_id = aia.invoice_id
AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1
AND aipd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'
and xdl.ae_header_id = DGSD.ae_header_id
and xdl.ae_line_num = DGSD.ae_line_num
GROUP BY rsh.RECEIPT_NUM)
) AP_RECEIPT,
TO_CHAR(aca.check_number) AP_PMT,
NULL AP_PAYMENT_TERMS,
--to_char(aps.due_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
to_char(aca.check_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DATE,
flv.meaning AP_PAYMENT_STATUS,
ieba.bank_account_num AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_payment_schedules_all aps,
fnd_lookup_values flv,
iby_ext_bank_accounts ieba,
xla_transaction_entities xte
WHERE 1 =1
AND aca.check_id = xte.source_id_int_1
AND DGSD.entity_id = xte.entity_id
and aipa.check_id = aca.check_id(+)
and aps.invoice_id = aipa.invoice_id
and aca.status_lookup_code = flv.lookup_code(+)
and aca.external_bank_account_id = ieba.ext_bank_account_id(+)
and flv.lookup_type (+)= 'CHECK STATE'
and flv.language (+)= 'US'
AND DGSD.je_source = 'Payables'
AND xte.entity_code = 'AP_PAYMENTS'
AND DGSD.je_category = 'Reconciled Payments'
UNION ALL
--Receivables Invoice Distributions
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,
rcta.trx_number AR_TRXNUM,
(SELECT description
FROM ra_customer_trx_lines_all
WHERE customer_trx_id=rcta.customer_trx_id
AND ROWNUM<2
)AR_INV_LINE_DESC,
TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,
(SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id=rcta.customer_trx_id
) AR_TRXAMT,
hp.party_name AR_CUSTNAME,
hcsu.location AR_CUSTSITE,
hca.account_number AR_CUSTNUM,
rctta.name AR_TRXTYPE,
rabs.name AR_TRXSOURCE,
(SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)
FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARC,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE ARC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS ='APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER
)
WHERE AMT!=0 -- Exclude cancelled receipts
) AR_RECEIPTNUM,
(SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)
FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARC,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE ARC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS ='APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER
)
WHERE AMT!=0 -- Exclude cancelled receipts
) AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
ra_customer_trx_all rcta,
ra_cust_trx_line_gl_dist_all rctda,
xla_distribution_links xdl,
hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
ra_cust_trx_types_all rctta,
ra_batch_sources_all rabs
WHERE 1 =1
AND hca.cust_account_id = hcsa.cust_account_id
AND rcta.bill_to_site_use_id = hcsu.site_use_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_site_id = hcsa.party_site_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rctta.cust_trx_type_seq_id = rcta.cust_trx_type_seq_id
AND rabs.batch_source_seq_id = rcta.batch_source_seq_id
AND rcta.customer_trx_id = xte.source_id_int_1
AND DGSD.entity_id = xte.entity_id
AND rcta.customer_trx_id = rctda.customer_trx_id
AND xdl.ae_header_id = DGSD.ae_header_id
AND xdl.ae_line_num = DGSD.ae_line_num
AND xdl.source_distribution_id_num_1 = rctda.cust_trx_line_gl_dist_id
AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND DGSD.je_source = 'Receivables'
UNION ALL
-- Receivable Applications
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,
rcta.trx_number AR_TRXNUM,
(SELECT description
FROM ra_customer_trx_lines_all
WHERE customer_trx_id=rcta.customer_trx_id
AND ROWNUM<2
)AR_INV_LINE_DESC,
TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,
(SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id=rcta.customer_trx_id
) AR_TRXAMT,
hp.party_name AR_CUSTNAME,
hcsu.location AR_CUSTSITE,
hca.account_number AR_CUSTNUM,
rctta.name AR_TRXTYPE,
rabs.name AR_TRXSOURCE,
(SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)
FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARC
WHERE ARC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS ='APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER
)
WHERE AMT!=0 -- Exclude cancelled receipts
) AR_RECEIPTNUM,
(SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)
FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARC
WHERE ARC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS ='APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER
)
WHERE AMT!=0 -- Exclude cancelled receipts
) AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
ra_customer_trx_all rcta,
ar_distributions_all rctda,
ar_receivable_applications_all araa,
xla_distribution_links xdl,
hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
ra_cust_trx_types_all rctta,
ra_batch_sources_all rabs
WHERE 1 =1
AND hca.cust_account_id = hcsa.cust_account_id
AND rcta.bill_to_site_use_id = hcsu.site_use_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_site_id = hcsa.party_site_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rctta.cust_trx_type_seq_id = rcta.cust_trx_type_seq_id
AND rabs.batch_source_seq_id = rcta.batch_source_seq_id
AND rcta.customer_trx_id = xte.source_id_int_1
AND DGSD.entity_id = xte.entity_id
AND xdl.ae_header_id = DGSD.ae_header_id
AND xdl.ae_line_num = DGSD.ae_line_num
AND xdl.source_distribution_id_num_1 = rctda.line_id
AND araa.receivable_application_id = rctda.source_id
AND rcta.customer_trx_id = araa.customer_trx_id
AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND DGSD.je_source = 'Receivables'
UNION ALL
--Receivables Adjustments
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,
rcta.trx_number AR_TRXNUM,
(SELECT DISTINCT description
FROM ra_customer_trx_lines_all
WHERE customer_trx_id=rcta.customer_trx_id
AND ROWNUM<2
) AR_INV_LINE_DESC,
TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,
(SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id=rcta.customer_trx_id
) AR_TRXAMT,
hp.party_name AR_CUSTNAME,
hcsu.location AR_CUSTSITE,
hca.account_number AR_CUSTNUM,
rctta.name AR_TRXTYPE,
rabs.name AR_TRXSOURCE,
(SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)
FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARC,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE ARC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS ='APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER
)
WHERE AMT!=0 -- Exclude cancelled receipts
) AR_RECEIPTNUM,
(SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)
FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARC,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE ARC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS ='APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER
)
WHERE AMT!=0 -- Exclude cancelled receipts
) AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
ra_customer_trx_all rcta,
ar_adjustments_all ada,
hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
ra_cust_trx_types_all rctta,
ra_batch_sources_all rabs
WHERE 1 =1
AND hca.cust_account_id = hcsa.cust_account_id
AND rcta.bill_to_site_use_id = hcsu.site_use_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_site_id = hcsa.party_site_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rctta.cust_trx_type_seq_id = rcta.cust_trx_type_seq_id
AND rabs.batch_source_seq_id = rcta.batch_source_seq_id
AND rcta.customer_trx_id = ada.customer_trx_id
AND DGSD.entity_id = xte.entity_id
AND xte.source_id_int_1 = ada.adjustment_id
AND ada.customer_trx_id = rcta.customer_trx_id
AND DGSD.je_source = 'Receivables'
AND DGSD.je_category = 'Adjustment'
AND xte.entity_code = 'ADJUSTMENTS'
UNION ALL
--Receipts Balancing
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,
(SELECT LISTAGG(trx_number,'|') WITHIN GROUP (ORDER BY customer_trx_id)
FROM
(SELECT rcta.trx_number ,rcta.customer_trx_id
FROM ra_customer_trx_all rcta,
ar_receivable_applications_all araa
WHERE rcta.customer_trx_id = araa.applied_customer_trx_id
AND araa.cash_receipt_id = acr.cash_receipt_id
AND araa.status='APP'
GROUP BY rcta.trx_number,rcta.customer_trx_id
)) AR_TRXNUM,
(SELECT DISTINCT rctla.description
FROM ra_customer_trx_lines_all rctla
WHERE 1=1
AND ROWNUM<2
AND exists (select 1 from ar_receivable_applications_all araa
where rctla.customer_trx_id=araa.applied_customer_trx_id
AND araa.cash_receipt_id = acr.cash_receipt_id
AND araa.status='APP')
) AR_INV_LINE_DESC,
(SELECT LISTAGG(trx_date,'|') WITHIN GROUP (ORDER BY customer_trx_id)
FROM
(SELECT TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') trx_date,rcta.customer_trx_id
FROM ra_customer_trx_all rcta,
ar_receivable_applications_all araa
WHERE rcta.customer_trx_id = araa.applied_customer_trx_id
AND araa.cash_receipt_id = acr.cash_receipt_id
AND araa.status='APP'
GROUP BY rcta.trx_date,rcta.customer_trx_id
)) AR_TRXDATE,
(SELECT SUM(rctla.extended_amount) extended_amount
FROM ra_customer_trx_lines_all rctla
WHERE 1=1
AND exists (select 1 from ar_receivable_applications_all araa
where rctla.customer_trx_id=araa.applied_customer_trx_id
AND araa.cash_receipt_id = acr.cash_receipt_id
AND araa.status='APP')
) AR_TRXAMT,
hp.party_name AR_CUSTNAME,
(SELECT hcsu.location
FROM hz_party_sites hps,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu
WHERE 1 =1
AND hca.cust_account_id = hcsa.cust_account_id
AND acr.customer_site_use_id = hcsu.site_use_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_site_id = hcsa.party_site_id
) AR_CUSTSITE,
hca.account_number AR_CUSTNUM,
(SELECT LISTAGG(name,'|') WITHIN GROUP (ORDER BY customer_trx_id)
FROM
(SELECT rctta.name ,rcta.customer_trx_id
FROM ra_customer_trx_all rcta,
ar_receivable_applications_all araa,
ra_cust_trx_types_all rctta
WHERE rcta.customer_trx_id = araa.applied_customer_trx_id
AND araa.cash_receipt_id = acr.cash_receipt_id
AND araa.status='APP'
AND rctta.cust_trx_type_seq_id = rcta.cust_trx_type_seq_id
GROUP BY rctta.name,rcta.customer_trx_id
)) AR_TRXTYPE,
(SELECT LISTAGG(name,'|') WITHIN GROUP (ORDER BY customer_trx_id)
FROM
(SELECT rabs.name ,rcta.customer_trx_id
FROM ra_customer_trx_all rcta,
ar_receivable_applications_all araa,
ra_batch_sources_all rabs
WHERE rcta.customer_trx_id = araa.applied_customer_trx_id
AND araa.cash_receipt_id = acr.cash_receipt_id
AND rabs.batch_source_seq_id = rcta.batch_source_seq_id
AND araa.status='APP'
GROUP BY rabs.name,rcta.customer_trx_id
)) AR_TRXSOURCE,
acr.RECEIPT_NUMBER AR_RECEIPTNUM,
TO_CHAR(acr.RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american') AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
ar_cash_receipts_all acr,
hz_cust_accounts hca,
hz_parties hp
WHERE 1 =1
AND acr.cash_receipt_id = xte.source_id_int_1
AND DGSD.entity_id = xte.entity_id
AND DGSD.je_source = 'Receivables'
AND DGSD.je_category = 'Receipts'
AND acr.pay_from_customer = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
UNION ALL
--Cash Management
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
cet.transaction_type CE_TRX_TYPE,
TO_CHAR(cet.transaction_date, 'DD-MON-YYYY','nls_date_language=american') CE_TRX_DATE,
cet.reference_text CE_REF,
cet.description CE_DESC,
to_char(csh.statement_number) CE_STMT_IDENTIFIER,
TO_CHAR(csh.statement_date, 'DD-MON-YYYY','nls_date_language=american') CE_STMT_DATE,
to_char(ieba.bank_account_num) CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
ce_bank_accounts cba,
ce_statement_headers csh,
iby_ext_bank_accounts ieba,
ce_external_transactions cet
WHERE 1 =1
AND xte.source_id_int_1 = cet.transaction_id
AND xte.entity_id = DGSD.entity_id
and cet.bank_account_id = cba.bank_account_id
and csh.bank_account_id = cba.bank_account_id
and CET.bank_account_id = ieba.ext_bank_account_id
AND DGSD.je_source = 'Cash Management'
UNION ALL
--Assets Depreciation
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,
fas.asset_number FA_ASSET_NUM,
DGSD.je_category FA_TRANS_CATE,
fac.segment1 FA_MAJ_CATE,
fac.segment2 FA_MIN_CATE,
fas.asset_type FA_ASSET_TYPE,
fasl.description FA_ASSET_DESC,
TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,
fl.segment1 FA_ASST_LOC_COUNTRY,
fl.segment2 FA_ASST_LOC_STATE,
fl.segment3 FA_ASST_LOC_CITY,
fl.segment4 FA_ASST_LOC_SPARE,
'' FA_ASST_REM_LIFE,
fam.method_code FA_ASST_DEP_MET,
fam.life_in_months FA_USEFUL_LIFE,
fab.book_type_code FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
fa_additions_b fas,
fa_additions_tl fasl,
fa_categories_b fac,
fa_books fab,
fa_distribution_history fdh,
fa_locations fl,
fa_category_book_defaults fcb,
fa_methods fam
WHERE 1 =1
AND fas.asset_id = xte.source_id_int_1
AND fasl.asset_id = fas.asset_id
AND fab.transaction_header_id_out is null
AND fasl.language = USERENV('LANG')
AND fab.book_type_code = xte.source_id_char_1
AND fab.book_type_code = fdh.book_type_code
AND fab.transaction_header_id_out is null
AND fas.asset_category_id = fac.category_id
AND fab.asset_id = fas.asset_id
AND fcb.category_id = fac.category_id
AND fcb.book_type_code = fab.book_type_code
and fdh.distribution_id in (select max(fdh1.distribution_id)
from fa_distribution_history fdh1
where 1=1
and fab.asset_id = fdh1.asset_id
AND fab.book_type_code = fdh1.book_type_code)
--AND DGSD.code_combination_id = fdh.code_combination_id
AND fcb.method_id = fam.method_id
and fab.asset_id = fdh.asset_id
and fdh.location_id = fl.location_id
AND DGSD.entity_id = xte.entity_id
AND DGSD.je_source = 'Assets'
AND DGSD.je_category = 'Depreciation'
AND xte.entity_code ='DEPRECIATION'
UNION ALL
--Assets Addition
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,
fas.asset_number FA_ASSET_NUM,
DGSD.je_category FA_TRANS_CATE,
fac.segment1 FA_MAJ_CATE,
fac.segment2 FA_MIN_CATE,
fas.asset_type FA_ASSET_TYPE,
fasl.description FA_ASSET_DESC,
TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,
fl.segment1 FA_ASST_LOC_COUNTRY,
fl.segment2 FA_ASST_LOC_STATE,
fl.segment3 FA_ASST_LOC_CITY,
fl.segment4 FA_ASST_LOC_SPARE,
'' FA_ASST_REM_LIFE,
fam.method_code FA_ASST_DEP_MET,
fam.life_in_months FA_USEFUL_LIFE,
fab.book_type_code FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
fa_additions_b fas,
fa_additions_tl fasl,
fa_categories_b fac,
fa_books fab,
fa_category_book_defaults fcb,
fa_distribution_history fdh,
fa_locations fl,
fa_transaction_headers fth,
fa_methods fam
WHERE 1 =1
AND fth.transaction_header_id = xte.source_id_int_1
AND fas.asset_id = fth.asset_id
AND fas.asset_id = fab.asset_id
AND fasl.asset_id = fas.asset_id
AND fasl.language = USERENV('LANG')
AND fab.book_type_code = xte.source_id_char_1
AND fab.transaction_header_id_out is null
AND fab.book_type_code = fth.book_type_code
AND fab.book_type_code = fdh.book_type_code
AND fab.transaction_header_id_out is null
AND fas.asset_category_id = fac.category_id
AND fcb.category_id = fac.category_id
and fab.asset_id = fdh.asset_id
and fdh.location_id = fl.location_id
AND fcb.book_type_code = fab.book_type_code
AND fcb.method_id = fam.method_id
AND DGSD.entity_id = xte.entity_id
and fdh.distribution_id in (select max(fdh1.distribution_id)
from fa_distribution_history fdh1
where 1=1
and fab.asset_id = fdh1.asset_id
AND fab.book_type_code = fdh1.book_type_code)
AND DGSD.JE_SOURCE = 'Assets'
AND DGSD.je_category IN ('Transfer', 'Retirement', 'Adjustment', 'CIP Adjustment', 'Addition', 'CIP Addition')
UNION ALL
--FA LEASE
SELECT DGSD.LEDGER,
DGSD.PERIOD,
DGSD.ACCOUNTING_DATE,
DGSD.accounting_entity,
DGSD.cost_centre,
DGSD.nominal_account,
DGSD.sub_analysis,
DGSD.intercompany,
DGSD.location,
DGSD.project,
DGSD.flow,
DGSD.spare1,
DGSD.spare2,
(DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||
DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,
DGSD.JE_SOURCE,
DGSD.JE_CATEGORY,
DGSD.DOC_SEQ,
DGSD.POSTED_DATE,
DGSD.ENT_CUR,
DGSD.XENTERED_DR ENT_DR,
DGSD.XENTERED_CR ENT_CR,
nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,
DGSD.XACCOUNTED_DR ACCTD_DR,
DGSD.XACCOUNTED_CR ACCTD_CR,
nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,
DGSD.CUR_CONV_TYPE,
DGSD.CUR_CONV_DATE,
DGSD.CUR_CONV_RATE,
DGSD.BATCH,
DGSD.BATCH_DESC,
DGSD.JOUR_NAME,
DGSD.JOUR_DESC,
DGSD.JOURLINE_DESC,
DGSD.JOUR_PREP,
DGSD.JOUR_APPR,
DGSD.REV_FLAG,
DGSD.REV_DATE,
DGSD.ACC_SEQUENCE_NAME,
DGSD.ACC_SEQUENCE_NUMBER,
DGSD.REP_SEQUENCE_NAME,
DGSD.REP_SEQUENCE_NUMBER,
DGSD.JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,
fas.asset_number FA_ASSET_NUM,
DGSD.je_category FA_TRANS_CATE,
fac.segment1 FA_MAJ_CATE,
fac.segment2 FA_MIN_CATE,
fas.asset_type FA_ASSET_TYPE,
fasl.description FA_ASSET_DESC,
TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,
fl.segment1 FA_ASST_LOC_COUNTRY,
fl.segment2 FA_ASST_LOC_STATE,
fl.segment3 FA_ASST_LOC_CITY,
fl.segment4 FA_ASST_LOC_SPARE,
'' FA_ASST_REM_LIFE,
fam.method_code FA_ASST_DEP_MET,
fam.life_in_months FA_USEFUL_LIFE,
fab.book_type_code FA_BOOK_TYPE_CODE,
fal.lease_number FA_LEASE_NUMBER,
fal.description FA_LEASE_DESCRIPTION,
to_char(fal.lease_start_date, 'DD-MON-YYYY','nls_date_language=american') FA_LEASE_START_DATE,
to_char(fal.lease_end_date, 'DD-MON-YYYY','nls_date_language=american') FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
DGSD.GL_REF1
FROM DYN_GL_XX_DATA DGSD,
xla_transaction_entities xte,
fa_additions_b fas,
fa_additions_tl fasl,
fa_categories_b fac,
fa_books fab,
fa_distribution_history fdh,
fa_locations fl,
fa_category_book_defaults fcb,
FA_LEASES fal,
fa_methods fam
WHERE 1 =1
AND fas.asset_id = xte.source_id_int_1
AND fal.lease_id = fab.lease_id
AND fasl.asset_id = fas.asset_id
AND fab.transaction_header_id_out is null
AND fasl.language = USERENV('LANG')
AND fab.book_type_code = xte.source_id_char_1
AND fab.transaction_header_id_out is null
AND fas.asset_category_id = fac.category_id
AND fab.asset_id = fas.asset_id
AND fcb.category_id = fac.category_id
AND fcb.book_type_code = fab.book_type_code
AND fab.book_type_code = fdh.book_type_code
AND fcb.method_id = fam.method_id
and fab.asset_id = fdh.asset_id
and fdh.location_id = fl.location_id
AND DGSD.entity_id = xte.entity_id
and fdh.distribution_id in (select max(fdh1.distribution_id)
from fa_distribution_history fdh1
where 1=1
and fab.asset_id = fdh1.asset_id
AND fab.book_type_code = fdh1.book_type_code)
AND DGSD.je_source = 'Assets'
AND DGSD.je_category = 'Lease Expense'
AND xte.entity_code ='LEASE_EXPENSE'
UNION ALL
--All Other Sources
SELECT
glg.NAME LEDGER,
gjh.period_name PERIOD,
TO_CHAR(gjh.default_effective_date, 'DD-MON-YYYY','nls_date_language=american') ACCOUNTING_DATE,
data_acc_set.flex_segment_value accounting_entity,
gcc.segment2 cost_centre,
gcc.segment3 nominal_account,
gcc.segment4 sub_analysis,
gcc.segment5 intercompany,
gcc.segment6 location,
gcc.segment7 project,
gcc.segment8 flow,
gcc.segment9 spare1,
gcc.segment10 spare2,
(gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 1, data_acc_set.flex_segment_value)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 2, gcc.segment2)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 3, gcc.segment3)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 4, gcc.segment4)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 5, gcc.segment5)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 6, gcc.segment6)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 7, gcc.segment7)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 8, gcc.segment8)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 9, gcc.segment9)||'.'||
gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 10, gcc.segment10))CODE_COMBINATION_DESC,
gjs.user_je_source_name JE_SOURCE,
gjc.user_je_category_name JE_CATEGORY,
gjh.doc_sequence_value DOC_SEQ,
TO_CHAR(gjh.posted_date, 'DD-MON-YYYY','nls_date_language=american') POSTED_DATE,
gjl.currency_code ENT_CUR,
nvl(xal.entered_dr, gjl.entered_dr) ENT_DR,
nvl(xal.entered_cr, gjl.entered_cr) ENT_CR,
nvl(nvl(xal.entered_dr, gjl.entered_dr),0)-nvl(nvl(xal.entered_cr, gjl.entered_cr),0) ENT_NET,
nvl(xal.accounted_dr, gjl.accounted_dr) ACCTD_DR,
nvl(xal.accounted_cr, gjl.accounted_cr) ACCTD_CR,
nvl(nvl(xal.accounted_dr, gjl.accounted_dr),0)-nvl(nvl(xal.accounted_cr, gjl.accounted_cr),0) ACCTD_NET,
nvl(xal.currency_conversion_type,gjl.currency_conversion_type) CUR_CONV_TYPE,
TO_CHAR(nvl(xal.currency_conversion_date,gjl.currency_conversion_date), 'DD-MON-YYYY','nls_date_language=american') CUR_CONV_DATE,
nvl(xal.currency_conversion_rate,gjl.currency_conversion_rate) CUR_CONV_RATE,
gjb.name BATCH,
gjb.description BATCH_DESC,
gjh.name JOUR_NAME,
gjh.description JOUR_DESC,
gjl.description JOURLINE_DESC,
gjh.created_by JOUR_PREP,
(SELECT display_name FROM per_person_names_f WHERE person_id = gjb.approver_employee_id AND name_type = 'GLOBAL') JOUR_APPR,
--DECODE(gjh.accrual_rev_effective_date, NULL, 'N', 'Y') REV_FLAG,
decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') REV_FLAG,
TO_CHAR(gjh.accrual_rev_effective_date, 'DD-MON-YYYY','nls_date_language=american') REV_DATE,
fsv_acc_seq.header_name ACC_SEQUENCE_NAME,
gjh.posting_acct_seq_value ACC_SEQUENCE_NUMBER,
fsv_rep_seq.header_name REP_SEQUENCE_NAME,
gjh.close_acct_seq_value REP_SEQUENCE_NUMBER,
gjl.je_line_num JOURNAL_LINE_NUMBER,
NULL AP_ACCOUNTING_TYPE,
NULL AP_INV_NUMBER,
NULL AP_INV_TYPE,
NULL AP_SOURCE,
NULL AP_INV_DATE,
NULL AP_INVOICE_AMOUNT,
NULL AP_SUP_NAME,
NULL AP_SUP_NUM,
NULL AP_SUPSITE,
NULL AP_SUPTYPE,
NULL AP_REQUESTOR,
NULL AP_INVDESC,
NULL AP_INVLINEDESC,
NULL AP_INVLINE_TYPE,
NULL AP_PO,
NULL AP_POREQ,
NULL AP_RECEIPT,
NULL AP_PMT,
NULL AP_PAYMENT_TERMS,
NULL AP_PAYMENT_DUE_DATE,
NULL AP_PAYMENT_TERM_DATE,
NULL AP_PAYMENT_DATE,
NULL AP_PAYMENT_STATUS,
NULL AP_XX_BANK_ACC_NUMB,
NULL FA_ACCOUNTING_TYPE,
NULL FA_ASSET_NUM,
NULL FA_TRANS_CATE,
NULL FA_MAJ_CATE,
NULL FA_MIN_CATE,
NULL FA_ASSET_TYPE,
NULL FA_ASSET_DESC,
NULL FA_DATE_PLACED,
NULL FA_ASST_LOC_COUNTRY,
NULL FA_ASST_LOC_STATE,
NULL FA_ASST_LOC_CITY,
NULL FA_ASST_LOC_SPARE,
NULL FA_ASST_REM_LIFE,
NULL FA_ASST_DEP_MET,
NULL FA_USEFUL_LIFE,
NULL FA_BOOK_TYPE_CODE,
NULL FA_LEASE_NUMBER,
NULL FA_LEASE_DESCRIPTION,
NULL FA_LEASE_START_DATE,
NULL FA_LEASE_END_DATE,
NULL AR_ACCOUNTING_TYPE,
NULL AR_TRXNUM,
NULL AR_INV_LINE_DESC,
NULL AR_TRXDATE,
NULL AR_TRXAMT,
NULL AR_CUSTNAME,
NULL AR_CUSTSITE,
NULL AR_CUSTNUM,
NULL AR_TRXTYPE,
NULL AR_TRXSOURCE,
NULL AR_RECEIPTNUM,
NULL AR_RECEIPTDATE,
NULL CE_TRX_TYPE,
NULL CE_TRX_DATE,
NULL CE_REF,
NULL CE_DESC,
NULL CE_STMT_IDENTIFIER,
NULL CE_STMT_DATE,
NULL CE_XX_BANK_ACC,
gjl.REFERENCE_1 GL_REF1
FROM
gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers glg,
gl_je_batches gjb,
gl_code_combinations gcc,
gl_import_references gir,
fun_seq_versions fsv_acc_seq,
fun_seq_versions fsv_rep_seq,
gl_je_sources gjs,
gl_je_categories gjc,
xla_ae_lines xal,
data_acc_set
WHERE 1=1
AND gjl.je_header_id=gjh.je_header_id
AND glg.ledger_id = gjh.ledger_id
AND gjs.je_source_name = gjh.je_source
AND gjc.je_category_name = gjh.je_category
AND gjs.LANGUAGE = USERENV('LANG')
AND gjc.LANGUAGE = USERENV('LANG')
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.je_header_id = gir.je_header_id(+)
AND gjl.je_line_num = gir.je_line_num(+)
AND gir.gl_sl_link_id = xal.gl_sl_link_id(+)
AND gir.gl_sl_link_table = xal.gl_sl_link_table(+)
AND gcc.code_combination_id=gjl.code_combination_id
and gjh.posting_acct_seq_version_id = fsv_acc_seq.seq_version_id(+)
and gjh.close_acct_seq_version_id = fsv_rep_seq.seq_version_id(+)
AND gjh.status='P'
AND gjh.actual_flag='A'
AND gjh.je_source NOT IN ('Payables','Receivables','Cash Management','Assets')
-- AND glg.NAME = :P_LEDGER
and (data_acc_set.ledger_name = :p_ledger
or data_acc_set.ledger_set_name = :p_ledger)
and glg.ledger_id = data_acc_set.ledger_id
and ((coalesce(null, :p_acct_entity) is null)
or (data_acc_set.flex_segment_value in (:p_acct_entity)))
and ((coalesce(null, :p_cost_centre) is null)
or (gcc.segment2 in (:p_cost_centre)))
and ((coalesce(null, :p_account) is null)
or (gcc.segment3 in (:p_account)))
and ((coalesce(null, :p_sub_analysis) is null)
or (gcc.segment4 in (:p_sub_analysis)))
and ((coalesce(null, :p_intercmpny) is null)
or (gcc.segment5 in (:p_intercmpny)))
and ((coalesce(null, :p_location) is null)
or (gcc.segment6 in (:p_location)))
and ((coalesce(null, :p_project) is null)
or (gcc.segment7 in (:p_project)))
and ((coalesce(null, :p_flow) is null)
or (gcc.segment8 in (:p_flow)))
and ((coalesce(null, :p_spare1) is null)
or (gcc.segment9 in (:p_spare1)))
and ((coalesce(null, :p_spare2) is null)
or (gcc.segment10 in (:p_spare2)))
and ((coalesce(null, :P_JE_SOURCE) is null)
or (gjs.user_je_source_name in (:P_JE_SOURCE)))
and ((coalesce(null, :P_JE_CATEGORY) is null)
or (gjc.user_je_category_name in (:P_JE_CATEGORY)))
and gjh.period_name in (select period_name
from gl_periods
where start_date >= (select start_date
from gl_periods
where period_name = :P_PERIOD_FROM
and period_set_name = 'XX_GLOBAL_CAL')
and end_date <= (select end_date
from gl_periods
where period_name = :P_PERIOD_TO
and period_set_name = 'XX_GLOBAL_CAL')
and period_set_name = 'XX_GLOBAL_CAL')