Joining AP_CHECKS_ALL with XLA tables

https://aporaclepayables.blogspot.com/2018/11/select-distinct-select-count-gcc.html

SELECT DISTINCT

(

select   count(*) --  gcc.segmentx
from
xla_ae_headers a, xla_ae_lines b, gl_code_combinations c, xla_events d 
,xla.xla_transaction_entities ent
where  ent.source_id_int_1 =  aca.check_id   --Joining here
and  a.AE_HEADER_ID=b.AE_HEADER_ID
and a.LEDGER_ID=b.LEDGER_ID
and a.APPLICATION_ID=b.APPLICATION_ID
and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID
and a.EVENT_ID=d.EVENT_ID
and d.APPLICATION_ID=a.APPLICATION_ID
and d.APPLICATION_ID=b.APPLICATION_ID
and a.ENTITY_ID=ent.entity_id
and d.ENTITY_ID=ent.entity_id
and ent.application_id=a.APPLICATION_ID
and a.APPLICATION_ID=200
and b.APPLICATION_ID=200
and b.ACCOUNTING_CLASS_CODE ='CASH'
and a.je_category_name = '&P_JE_CATEGORY'
)   Payment_Account  ,

AIA.GL_DATE,
                AIA.DOC_SEQUENCE_VALUE "AP Number",
                 aca.DOC_SEQUENCE_VALUE "PV Number",
                 --asa.segment1 "Supplier Number"/*,
                 asa.vendor_name "Supplier Name",
                 aia.invoice_num "Invoice Number",
                 aia.INVOICE_DATE "Invoice Date",
                 aia.invoice_amount INV_AMT,
                 (select sum(ln.AMOUNT)
                    from ap_invoice_lines_all ln, gl_code_combinations 
gcc
                   where ln.INVOICE_ID = aia.INVOICE_ID
                     and gcc.SEGMENT3 like '1049%'
                     and ln.DEFAULT_DIST_CCID = gcc.CODE_COMBINATION_ID) 
Sales_Tax_Charged,
                 null "Total Amount",
                 null "Income Tax Withholding",
                 aia.PAYMENT_REASON_COMMENTS "Account Head",

                 NULL                  "Sales Tax Withholding",
                 AIPA.AMOUNT           "Payment Amount",
                 ASA.NUM_1099          "NTN No",
               --  ppf.NATIONAL_IDENTIFIER NIC_NO,
  (select ppf.NATIONAL_IDENTIFIER from
                    per_people_f          ppf
                    where ppf.PERSON_ID =asa.EMPLOYEE_ID
                    and rownum =1) NIC_NO,
                 asa.ATTRIBUTE1        "Individual / Company",
                 aia.DESCRIPTION       "Description",
                 aca.CHECK_DATE        "Payment Date",
                 aca.CHECK_NUMBER,
                 aca.BANK_ACCOUNT_NAME "Bank" ,
                 aia.invoice_id ,
         '.'||gcc.SEGMENT3||'.'  LIAB_ACC,

   (
    select AAA.DESCRIPTION
       from fnd_flex_values_vl AAA
     where   AAA.FLEX_VALUE = gcc.SEGMENT3
     and    AAA.flex_value_set_id =  1016491
     )   LIAB_ACC_DESC,
     aia.INVOICE_CURRENCY_CODE,
     decode(aia.PAYMENT_STATUS_FLAG,'N','UNPAID','Y','PAID','P','PARTIAL' 
)   PAYMENT_STATUS
   FROM ap_checks_all           aca,
        ap_invoice_payments_all aipa,
        ap_invoices_all         aia,
        ap_suppliers            asa,
          gl_code_combinations  gcc

  WHERE aca.check_id = aipa.check_id
    AND aipa.invoice_id = aia.invoice_id
    AND aia.vendor_id = asa.vendor_id
    and aia.CANCELLED_DATE is null
    and aca.STATUS_LOOKUP_CODE != 'VOIDED'
      and  aia.ACCTS_PAY_CODE_COMBINATION_ID  =  gcc.CODE_COMBINATION_ID
       --AND aipa.ACCRUAL_POSTED_FLAG = 'N'
       --AND POSTED_FLAG = 'N'
       ------------------------
       AND TRUNC (aca.CHECK_DATE) BETWEEN   
nvl(:P_frm_ch_date,aca.CHECK_DATE) AND nvl(:P_to_ch_date,aca.CHECK_DATE)

AND TRUNC (aia.GL_DATE) BETWEEN nvl(:P_frm_INv_date,aia.GL_DATE) AND 
nvl(:P_to_INV_date,aia.GL_DATE)
          and     asa.segment1 = nvl(:P_from_vendr,asa.segment1 )
and  (  :P_PS =  'A'  or  :P_PS = aia.PAYMENT_STATUS_FLAG   )
union all

select
null,
aia.GL_DATE,
aia.DOC_SEQUENCE_VALUE"AP Number",
NVL(null,0) "PV Number",
s.VENDOR_NAME "Supplier Name",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date"
,aia.INVOICE_AMOUNT INV_AMT
,(select sum(ln.AMOUNT)
                    from ap_invoice_lines_all ln, gl_code_combinations 
gcc
                   where ln.INVOICE_ID = aia.INVOICE_ID
                     and gcc.SEGMENT3 like '1049%'
                     and ln.DEFAULT_DIST_CCID = gcc.CODE_COMBINATION_ID) 
Sales_Tax_Charged
                     ,null "Total Amount",
                 null "Income Tax Withholding",
                 aia.PAYMENT_REASON_COMMENTS "Account Head"
                 ,NULL                  "Sales Tax Withholding",
                 NVL(NULL,0)           "Payment Amount",
                 S.NUM_1099          "NTN No",
               --  ppf.NATIONAL_IDENTIFIER NIC_NO,

  (select ppf.NATIONAL_IDENTIFIER from
                    per_people_f          ppf
                    where ppf.PERSON_ID =s.EMPLOYEE_ID
                    and rownum =1) NIC_NO,


               s.ATTRIBUTE1        "Individual / Company",
                 aia.DESCRIPTION       "Description"

                 ,null       "Payment Date",
                nvL( null,0) CHECK_NUMBER,
                 null "Bank" ,
                 aia.invoice_id ,
              '.'||gcc.SEGMENT3||'.'    LIAB_ACC,


   (
    select AAA.DESCRIPTION
       from fnd_flex_values_vl AAA
     where   AAA.FLEX_VALUE = gcc.SEGMENT3
     and    AAA.flex_value_set_id =  1016491
     )   LIAB_ACC_DESC,
     aia.INVOICE_CURRENCY_CODE,
     decode(aia.PAYMENT_STATUS_FLAG,'N','UNPAID','Y','PAID','P','PARTIAL' 
)   PAYMENT_STATUS
from

ap_invoices_all aia
,ap_suppliers s
--,ap_invoice_payments_all aipa
,  gl_code_combinations  gcc

where

s.VENDOR_ID = aia.VENDOR_ID

--and aia.AMOUNT_PAID is null
and NVL(aia.AMOUNT_PAID,0) = 0
   and  aia.ACCTS_PAY_CODE_COMBINATION_ID  =  gcc.CODE_COMBINATION_ID
--and ppf.PERSON_ID = s.EMPLOYEE_ID
AND TRUNC (aia.GL_DATE) BETWEEN nvl(:P_frm_INv_date,aia.GL_DATE) AND 
nvl(:P_to_INV_date,aia.GL_DATE)
          and     s.segment1 = nvl(:P_from_vendr,s.segment1 )
and  (  :P_PS =  'A'  or  :P_PS = aia.PAYMENT_STATUS_FLAG   )