AR Reports

1. Debtor Ledger Report Query........

SELECT DISTINCT B.GL_DATE,
              c.type  type1, 
              --d.CUSTOMER_CATEGORY_CODE ,
             -- d.customer_name,
             party
             .PARTY_NAME customer_name,
                          -- c.NAME Vch_Type,
                           decode(c.type,'INV','Invoice',
                                          'CM','Credit Memo',
                                          'DM','Debit Memo') Vch_Type,
              a.trx_number doc_no,
              a.trx_date  doc_dt,
              --c.DESCRIPTION particular,
              d.description particular,
              b.amount *  nvl(a.EXCHANGE_RATE,1) amount,
                            --a.customer_trx_id,
             -- a.INTERFACE_HEADER_ATTRIBUTE1 ref_no,
              --d.customer_id,
              cust.CUST_ACCOUNT_ID,
             null,          
               null,
               null,
               null
    FROM RA_CUSTOMER_TRX_ALL  a,
         RA_CUST_TRX_LINE_GL_DIST_ALL b,
         RA_CUST_TRX_TYPES_ALL   C ,
               hz_cust_accounts cust,
        hz_parties party,
                hz_cust_acct_sites_all hcasa,
         hz_cust_site_uses_all hcsua  ,
         (SELECT distinct gcc.CODE_COMBINATION_ID, gcc.segment4, ffvl.description,lgd.CUSTOMER_TRX_ID
  FROM RA_CUSTOMER_TRX_ALL a,
  ra_cust_trx_line_gl_dist_all lgd,
       gl_code_combinations gcc,
       fnd_flex_values_vl ffvl
 WHERE a.CUSTOMER_TRX_ID=lgd.CUSTOMER_TRX_ID
  and lgd.code_combination_id = gcc.code_combination_id
   AND gcc.segment4 = ffvl.flex_value
   AND lgd.account_class = 'REV'
  -- and gcc.segment4=31111
   --and a.trx_number=10130000002
   ) d
    WHERE cust.party_id = party.party_id
          and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
           and a.CUSTOMER_TRX_ID=d.CUSTOMER_TRX_ID
    and  hcasa.cust_acct_site_id = hcsua.cust_acct_site_id  
    AND a.customer_trx_id=b.customer_trx_id
    AND a.cust_trx_type_id  = c.cust_trx_type_id
    AND a.bill_to_customer_id  =cust.CUST_ACCOUNT_ID
   AND A.COMPLETE_FLAG = 'Y'
    AND b.org_id=nvl(:P_ORG_ID,b.org_id)
    AND b.latest_rec_flag ='Y'
       AND c.type in ('INV','CM','DM')
    AND B.GL_DATE  is not  null
    AND trunc(b.gl_date) between  nvl(:p_from_date,b.gl_date) AND nvl(:p_to_date,b.gl_date)
      AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
union
SELECT DISTINCT (G.GL_DATE),  
             'REC'   type1,
             party.PARTY_NAME customer_name,
              --e.TYPE vch_type,
               'Receipt' vch_type,
              E.RECEIPT_NUMBER doc_no,
              e.RECEIPT_DATE doc_dt ,
              ABCA.BANK_ACCOUNT_NAME particular,
              e.AMOUNT  AMOUNT ,
              cust.CUST_ACCOUNT_ID,
             --  e.cash_receipt_id,  
             null,          
               null,
               null,
               null
  FROM ar_cash_receipts_all e,
       ar_cash_receipt_history_all g,
       ce_bank_accounts abca,
       ce_bank_acct_uses_all abcau,
       hz_cust_accounts cust,
       HZ_PARTIES party,
       AR_RECEIVABLE_APPLICATIONS_ALL f,
       AR_PAYMENT_SCHEDULES_all        d
 WHERE e.cash_receipt_id = g.cash_receipt_id
   AND abcau.bank_account_id = abca.bank_account_id
   AND e.remit_bank_acct_use_id = abcau.bank_acct_use_id
   AND e.pay_from_customer = cust.cust_account_id
   and party.PARTY_ID=cust.PARTY_ID
   --and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID
 --and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
-- and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and e.CASH_RECEIPT_ID=f.RECEIVABLE_APPLICATION_ID
and d.PAYMENT_SCHEDULE_ID=f.PAYMENT_SCHEDULE_ID
  -- AND g.status = 'CLEARED'
   AND G.gl_date between nvl(:p_from_date,g.gl_date) AND nvl(:p_to_date,g.gl_date)
    AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
AND G.ORG_ID=nvl(:P_ORG_ID,g.org_id)
        union
     Select DISTINCT  (G.GL_DATE),  
             'REV'   type1,
              --rc.CUSTOMER_CATEGORY_CODE,
              party.PARTY_NAME customer_name,
            -- e.TYPE vch_type,
              'Receipt' vch_type,
              E.RECEIPT_NUMBER doc_no,
              e.RECEIPT_DATE doc_dt ,
              ABCA.BANK_ACCOUNT_NAME particular,
              e.amount  AMOUNT ,
             --  null,
           --e.COMMENTS,--a.TRX_NUMBER||' '||ABCA.BANK_ACCOUNT_NAME ref_no ,
            -- hp.PARTY_NAME,
               --e.cash_receipt_id,
               --e.REVERSAL_COMMENTS
               cust.CUST_ACCOUNT_ID,
               null,
               null,
               null,
               null
    from AR_PAYMENT_SCHEDULES_all        d ,
         AR_CASH_RECEIPTS_all            e ,
         AR_RECEIVABLE_APPLICATIONS_ALL f      ,
         ar_cash_receipt_history_all  G  ,
         --RA_CUSTOMER_TRX_ALL  A  ,
                hz_cust_accounts cust,
        hz_parties party,
                hz_cust_acct_sites_all hcasa,
         hz_cust_site_uses_all hcsua,
         CE_BANK_ACCOUNTS ABCA   
    where d.PAYMENT_SCHEDULE_ID     = f.PAYMENT_SCHEDULE_ID
    and f.CASH_RECEIPT_ID           = e.CASH_RECEIPT_ID(+)
   -- AND f.AMOUNT_APPLIED  IS NOT NULL
    and f.CASH_RECEIPT_ID           = G.CASH_RECEIPT_ID
    --and d.CUSTOMER_ID = nvl(:P_FROM_CUSTOMER_ID,d.customer_id)
    and d.CUSTOMER_ID in(select distinct cust_account_id from hz_cust_acct_sites_all
                where cust_acct_site_id in (select distinct cust_acct_site_id from hz_cust_site_uses_all
                --where GL_ID_REC=:P_CODE)
                ))
    AND G.gl_date between nvl(:p_from_date,g.gl_date) AND nvl(:p_to_date,g.gl_date)
    and g.FIRST_POSTED_RECORD_FLAG='N'
    AND G.ORG_ID=nvl(:P_ORG_ID,g.org_id)
   -- and rc.customer_id=d.CUSTOMER_ID
   -- and rc.customer_id=d.CUSTOMER_ID
    --and rc.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID
     and cust.party_id = party.party_id
      -- and D.customer_id = hcasa.cust_account_id
   and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
    and d.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID
     and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID
    and e.receipt_method_id NOT IN (SELECT receipt_method_id FROM  AR_RECEIPT_METHODS 
                WHERE  UPPER(NAME)  LIKE 'ADVANCE%')
    AND E.REVERSAL_DATE IS not NULL
    AND E.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID
    --AND HCSUA.GL_ID_REC=:P_CODE
  and e.STATUS  in ('REV','NSF','STOP')                 --Added by JD 20/04/09
--    and e.STATUS='REV'                                 --Removed by JD 20/04/09
    AND E.CASH_RECEIPT_ID IN (SELECT CASH_RECEIPT_ID FROM AR_CASH_RECEIPT_HISTORY_ALL A
                              WHERE A.STATUS = 'REVERSED')
  AND g.REVERSAL_GL_DATE is null                       --Added by JD 10/01/09
  AND g.REVERSAL_CASH_RECEIPT_HIST_ID is null
  AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
 union
  select distinct
    h.GL_DATE,
    'EXGL' type,
    --rc.CUSTOMER_CATEGORY_CODE,
    party.PARTY_NAME customer_name,
    'Receipt' vch_type,
            c.RECEIPT_NUMBER doc_no,--trx number
    c.RECEIPT_DATE doc_dt,
    ABCA.BANK_ACCOUNT_NAME particular,
   --sum((c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED))
    sum((nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)) EXGAINLOSS,
    --(c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED) AMOUNT,
    --rc.CUSTOMER_ID  ,
   cust.CUST_ACCOUNT_ID,
    null,
     null,
     null,
     null   
    from RA_CUSTOMER_TRX_ALL t,
    AR_RECEIVABLE_APPLICATIONS_ALL r,
            ar_cash_receipts_all c,
            ar_cash_receipt_history_all h,
                        CE_BANK_ACCOUNTS ABCA,
           hz_cust_accounts cust,
        hz_parties party
    where    r.APPLIED_CUSTOMER_TRX_ID = t.CUSTOMER_TRX_ID 
    and c.CASH_RECEIPT_ID = r.CASH_RECEIPT_ID
    and cust.party_id = party.party_id
     and c.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID
      and c.PAY_FROM_CUSTOMER = cust.CUST_ACCOUNT_ID
    -- AND c.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID
    and c.REMIT_BANK_ACCT_USE_ID=ABCA.BANK_ACCOUNT_ID
    and cust.PARTY_ID=party.PARTY_ID 
    and c.PAY_FROM_CUSTOMER = cust.CUST_ACCOUNT_ID
   --and r.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID
    --and c.PAY_FROM_CUSTOMER = 1040
    and h.STATUS = 'CLEARED'
  -- and h.STATUS = 'REMITTED'
    --and h.GL_DATE >'31-MAR-2008'
    and h.gl_date between nvl(:p_from_date,h.gl_date) AND nvl(:p_to_date,h.gl_date)
    and  h.ORG_ID=nvl(:P_ORG_ID,h.org_id)
     AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
    --and c.CURRENCY_CODE <> 'INR'
         --AND RC.CUSTOMER_ID =:P_FROM_CUSTOMER_ID--new
    --and (nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)<>0 --new
    GROUP BY h.GL_DATE,TYPE,c.RECEIPT_NUMBER,c.RECEIPT_DATE,null,
    null,c.CASH_RECEIPT_ID,c.COMMENTS, party.PARTY_NAME,ABCA.BANK_ACCOUNT_NAME
     ,cust.CUST_ACCOUNT_ID
union all
SELECT DISTINCT
    B.GL_DATE,
    'ADJ' type1, 
   -- d.CUSTOMER_CATEGORY_CODE, 
    party.PARTY_NAME,
   c.NAME Vch_Type,
    a.trx_number,
    a.trx_date  ,
    -- c.DESCRIPTION particular,
    d.description particular,
    b.amount *  nvl(a.EXCHANGE_RATE,1) amount,
    a.customer_trx_id,
    a.INTERFACE_HEADER_ATTRIBUTE1 ref_no,
   -- d.customer_id,
    null,
    null,
    null
      FROM RA_CUSTOMER_TRX_ALL  a,
        ar_adjustments_all     b,
        RA_CUST_TRX_TYPES_ALL   C ,
       --AR_CUSTOMERS  d,
       hz_cust_accounts cust,
       hz_parties party,
        hz_cust_acct_sites_all hcasa,
        hz_cust_site_uses_all hcsua,
        (SELECT distinct gcc.CODE_COMBINATION_ID, gcc.segment4, ffvl.description,lgd.CUSTOMER_TRX_ID
  FROM RA_CUSTOMER_TRX_ALL a,
  ra_cust_trx_line_gl_dist_all lgd,
       gl_code_combinations gcc,
       fnd_flex_values_vl ffvl
 WHERE a.CUSTOMER_TRX_ID=lgd.CUSTOMER_TRX_ID
  and lgd.code_combination_id = gcc.code_combination_id
   AND gcc.segment4 = ffvl.flex_value
   AND lgd.account_class = 'REV'
  -- and gcc.segment4=31111
   --and a.trx_number=10130000002
   ) d 
    WHERE  a.customer_trx_id=b.customer_trx_id
     AND a.cust_trx_type_id  = c.cust_trx_type_id
       and  cust.party_id = party.party_id
      AND a.bill_to_customer_id  =cust.CUST_ACCOUNT_ID
       and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
        and
    hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
    and a.CUSTOMER_TRX_ID=d.CUSTOMER_TRX_ID
            --and a.customer_trx_id <> '69735'
    --AND a.sold_to_customer_id  =d.customer_id
    AND  b.org_id=nvl(:P_ORG_ID,b.org_id)
    --AND     c.type in ('INV','CM','DM')
    AND B. GL_DATE  is not  null
    --AND     a.trx_number =:P_TRX
    AND trunc(b.gl_date) between  nvl(:p_from_date,b.gl_date) AND nvl(:p_to_date,b.gl_date)
     AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
    --AND HCSUA.GL_ID_REC=:P_CODE;
order by 1,3