R12.2 AP/AR Detail Level Queries

 -- AR transactions data
select 
  a.customer_trx_id erp_id ,
 decode( d.type,'INV', 'Invoice','DM','Debit Note','CM','Credit Note') Transaction_type ,
  d.name Transaction_type_Name,
  a.trx_number,
  a.trx_date,
  c.gl_date ,
  apsa.due_date,
  hp.party_id customer_id,
  hp.party_name customer_name,
  hca.account_number ,
  hca.account_name ,
  apsa.amount_due_original total_amount ,
  apsa.amount_due_original - apsa.amount_due_remaining amount_received,
  apsa.amount_due_remaining remaining_amount,
  decode(apsa.amount_due_remaining,0,'Closed','Open') status,
--  row_number() over (partition by a.customer_trx_id  order by b.link_to_cust_trx_line_id asc nulls first ) line_number,
--  Decode(b.line_type, 'LINE','Item','Tax' ) Line_type ,
 row_number() over (partition by a.customer_trx_id  order by c.account_class asc ) line_number,
  c.account_class ,
  b.description line_description,
--  b.extended_amount line_amount,  
  gcc.concatenated_segments account_code ,
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT1'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      and ffv.flex_value = gcc.segment1 
  )  ||'.'||
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT2'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment2 
  )  ||'.'||
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT3'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment3 
  )  ||'.'|| 
(SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT4'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment4 
  )  ||'.'|| 
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT5'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment5 
  )  ||'.'||  
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT6'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment6 
  )  ||'.'|| 
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT7'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      AND ffv.flex_value = gcc.segment7 
  )  ||'.'||  
  (SELECT ffv.description
  FROM apps.fnd_id_flex_structures_vl fif ,
           apps.fnd_id_flex_segments_vl fifs,
          apps.fnd_flex_values_vl ffv
  WHERE fif.id_flex_num = gcc.chart_of_accounts_id
      AND fif.application_id = 101 
      AND fif.id_flex_code = 'GL#'
      AND fif.id_flex_code         = fifs.id_flex_code
      AND fif.id_flex_num          = fifs.id_flex_num
      AND fifs.application_column_name = 'SEGMENT8'
      AND fifs.flex_value_set_id   = ffv.flex_value_set_id
      and ffv.flex_value = gcc.segment8 
  )  account_description,
  c.acctd_amount 
FROM apps.ra_customer_trx_all a,
  apps.ra_customer_trx_lines_all b ,
  apps.ra_cust_trx_line_gl_dist_all c,
  apps.ra_cust_trx_types_all d ,
  apps.ar_payment_schedules_all apsa,
  apps.gl_code_combinations_kfv gcc ,
  apps.hz_cust_accounts hca,
  apps.hz_parties hp
where a.org_id             = 4668
--and a.customer_trx_id = 8778052
and a.customer_trx_id      = b.customer_trx_id(+)
and a.customer_trx_id = c.customer_trx_id
and b.customer_trx_line_id(+) = c.customer_trx_line_id
AND c.code_combination_id  = gcc.code_combination_id
AND a.cust_trx_type_id     = d.cust_trx_type_id
AND a.org_id               = d.org_id
AND a.bill_to_customer_id  = hca.cust_account_id
AND hca.party_id           = hp.party_id
AND a.customer_trx_id      = apsa.customer_trx_id
  --     and apsa.amount_due_remaining > 0
 Order by 1
  ;
  
  
-- AR Receipts
  SELECT 
  hp.party_id customer_id,
  hp.party_name customer_name,
  hca.account_number ,
  cr.cash_receipt_id  erp_id ,  
  cr.receipt_number ,
  cr.receipt_date ,
  apsa.due_date maturity_date,
  (SELECT crh.trx_date 
    FROM apps.ar_cash_receipt_history_all crh 
    WHERE crh.cash_receipt_id = cr.cash_receipt_id 
     AND crh.status = 'CLEARED' ) cleared_date,
  (SELECT crh.status  
    FROM apps.ar_cash_receipt_history_all crh 
    WHERE crh.cash_receipt_id = cr.cash_receipt_id 
     AND crh.current_record_flag = 'Y' 
  )  receipt_status,
  cr.comments description , 
    cr.amount receipt_amount ,
  abs(apsa.amount_applied) amount_applied,
  cr.amount + apsa.amount_applied unapplied_amount,
  (SELECT  cba.bank_account_num 
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba 
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id )  remit_bank_account_num , 
  (SELECT  cba.iban_number 
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba 
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id )  iban_number ,
  (SELECT  cbv.bank_name
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba ,
          apps.ce_banks_v cbv
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id
    AND cba.bank_id  = cbv.bank_party_id )  bank_name    ,
  (SELECT  cbbv.bank_branch_name
    FROM apps.ce_bank_acct_uses_all cbu ,
          apps.ce_bank_accounts cba ,
          apps.ce_bank_branches_v cbbv
  WHERE cbu.bank_acct_use_id = cr.remit_bank_acct_use_id
    AND cbu.bank_account_id = cba.bank_account_id
    AND cba.BANK_BRANCH_ID  = cbbv.branch_party_id )  branch_name          
FROM apps.ar_cash_receipts_all cr,
   apps.ar_payment_schedules_all apsa,
  apps.hz_cust_accounts hca,
  apps.hz_parties hp
WHERE cr.org_id             = 4668
AND cr.pay_from_customer  = hca.cust_account_id
AND hca.party_id           = hp.party_id
AND cr.cash_receipt_id      = apsa.cash_receipt_id
  --     and apsa.amount_due_remaining > 0
 ORDER BY 1, 4
  ;
  
-- AP Transactions  (Invoice,DM,CM)
  
  SELECT 
    (SELECT name 
       FROM apps.hr_operating_units
       WHERE organization_id = aia.org_id) OU_name ,  
    aia.vendor_id supplier_id,
    (SELECT vendor_name 
       FROM apps.ap_suppliers pv
       WHERE pv.vendor_id = aia.vendor_id) Supplier_name ,
    (SELECT vendor_site_code NAME 
       FROM apps.ap_supplier_Sites_all  pv
       WHERE pv.vendor_site_id = aia.vendor_site_id) Supplier_site_name ,
    aia.invoice_id erp_invoice_id,
    decode(aia.invoice_type_lookup_code,'STANDARD','Invoice', initcap(aia.invoice_type_lookup_code)||' Memo') invoice_type ,
    aia.invoice_num , 
    aia.invoice_Date,    
    aida.accounting_Date gl_date ,
    aia.invoice_amount ,
    aia.amount_paid ,
    apsa.amount_remaining ,
    Case when apsa.amount_remaining > 0 then 'Open' else 'Closed' end Status ,
    aila.line_number , 
    aila.line_type_lookup_code line_type ,
    aila.description line_description ,
    aida.distribution_line_number  , 
     gcc.concatenated_segments account_code ,              
     (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT1'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment1 
              )  ||'.'||
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT2'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment2 
              )  ||'.'||
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT3'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment3 
              )  ||'.'|| 
            (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT4'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment4 
              )  ||'.'|| 
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT5'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment5 
              )  ||'.'||  
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT6'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment6 
              )  ||'.'|| 
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT7'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment7 
              )  ||'.'||  
              (SELECT ffv.description
              FROM apps.fnd_id_flex_structures_vl fif ,
                       apps.fnd_id_flex_segments_vl fifs,
                      apps.fnd_flex_values_vl ffv
              WHERE fif.id_flex_num = gcc.chart_of_accounts_id
                  AND fif.application_id = 101 
                  AND fif.id_flex_code = 'GL#'
                  AND fif.id_flex_code         = fifs.id_flex_code
                  AND fif.id_flex_num          = fifs.id_flex_num
                  AND fifs.application_column_name = 'SEGMENT8'
                  AND fifs.flex_value_set_id   = ffv.flex_value_set_id
                  AND ffv.flex_value = gcc.segment8 
              )  account_description ,
      nvl ( (aida.amount), 0)  amount
  FROM apps.ap_invoice_distributions_all aida,
           apps.ap_invoice_lines_all aila,
           apps.ap_invoices_all aia, 
           ap.ap_payment_schedules_all apsa,
           apps.gl_code_combinations_kfv gcc
 WHERE  1 = 1
     AND aia.org_id = 4668
     AND aia.invoice_id = apsa.invoice_id
     AND aida.invoice_id = aia.invoice_id
     AND aila.invoice_id = aida.invoice_id
     AND aila.line_number = aida.invoice_line_number
     AND aia.cancelled_date is null
     AND nvl(aila.discarded_flag,'N') != 'Y'
   --    AND aida.parent_reversal_id is null 
     AND gcc.code_combination_id = aida.dist_code_combination_id
 ORDER BY aia.invoice_id , aila.line_number, aida.distribution_line_number       
       ;

-- AP Payments

SELECT (SELECT name 
             FROM apps.hr_operating_units
             WHERE organization_id = aipa.org_id) OU_name , 
             aca.check_id erp_payment_id,    
             aca.check_number cheq_number,
             aca.check_date cheq_date,
             Initcap(aca.STATUS_LOOKUP_CODE) Cheq_Status,
             aca.cleared_date ,
             nvl(aca.cleared_Amount,aipa.amount) cheq_amount,             
             aca.bank_account_name,
             aca.bank_account_num,
          (SELECT  cbv.bank_name
            FROM apps.ce_bank_acct_uses_all cbu ,
                  apps.ce_bank_accounts cba ,
                  apps.ce_banks_v cbv
          WHERE cbu.bank_acct_use_id =  aca.ce_bank_acct_use_id  
            AND cbu.bank_account_id = cba.bank_account_id
            AND cba.bank_id  = cbv.bank_party_id )  bank_name    ,
          (SELECT  cbbv.bank_branch_name
            FROM apps.ce_bank_acct_uses_all cbu ,
                  apps.ce_bank_accounts cba ,
                  apps.ce_bank_branches_v cbbv
          WHERE cbu.bank_acct_use_id =              aca.ce_bank_acct_use_id  
            AND cbu.bank_account_id = cba.bank_account_id
            AND cba.BANK_BRANCH_ID  = cbbv.branch_party_id )  branch_name       ,   
             pv.vendor_id supplier_id,
             pv.vendor_name supplier_name,
             pvsa.vendor_site_code Supplier_Site,           
             aia.invoice_num supplier_invoice_num ,
             aipa.amount paid_amount,
             aia.invoice_date invoice_date
   FROM  apps.ap_suppliers pv 
          ,   apps.ap_supplier_sites_all pvsa 
          ,   apps.ap_checks_All aca
          ,   apps.ap_invoice_payments_All aipa
          ,   apps.ap_invoices_all aia 
WHERE  aipa.set_of_books_id = 2403
 --  AND aca.check_date >= '01-JAN-2020'
  AND aca.STATUS_LOOKUP_CODE != 'VOIDED'
  AND  aca.vendor_id = pv.vendor_id
  AND  aca.vendor_site_id = pvsa.vendor_site_id
  AND aca.check_id =  aipa.check_id
  AND aipa.invoice_id = aia.invoice_id
ORDER BY 2 ASC ;