AR : Receipt to fetch Total Uncleared Receipt Amount

SELECT   NVL (SUM (ps.amount_due_remaining), 0) total_uncleared_receipts
  FROM   hz_cust_accounts_all cust_acct,
         ar_payment_schedules_all ps,
         ar_receivable_applications_all arr,
         hz_cust_acct_sites_all acct_site,
         hz_party_sites party_site,
         hz_locations loc,
         hz_cust_site_uses_all site_uses,
         ar_cash_receipt_history_all crh,
         ar_cash_receipts_all acr,
         gl_code_combinations cc
 WHERE       TRUNC (ps.gl_date) <= :p_as_of_date
         AND ps.customer_id = cust_acct.cust_account_id
         AND cust_acct.account_number = :p_account_number
         AND ps.customer_id = cust_acct.cust_account_id
         AND acct_site.party_site_id = party_site.party_site_id
         AND loc.location_id = party_site.location_id
         AND ps.cash_receipt_id = acr.cash_receipt_id
         AND acr.cash_receipt_id = crh.cash_receipt_id
         AND crh.account_code_combination_id = cc.code_combination_id
         AND ps.trx_date <= :p_as_of_date
         AND ps.CLASS = 'PMT'
         AND ps.cash_receipt_id = arr.cash_receipt_id
         AND arr.status = 'UNAPP'
         AND ps.status = 'OP'
         AND site_uses.site_use_code = 'BILL_TO'
         AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
         AND NVL (site_uses.status, 'A') = 'A'
         AND cust_acct.cust_account_id = acct_site.cust_account_id
         AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
         AND ps.customer_id = acct_site.cust_account_id
         AND ps.customer_site_use_id = site_uses.site_use_id
         AND ps.cash_receipt_id = crh.cash_receipt_id
         AND crh.status NOT IN ('CLEARED')
HAVING   NVL (SUM (arr.amount_applied), 0) > 0;

Read More

AR : Receipt to fetch Total Un-applied Receipt Amount

SELECT   NVL (SUM (arr.amount_applied), 0) total_unapp_receipts
  FROM   hz_cust_accounts_all cust_acct,
         ar_payment_schedules_all ps,
         ar_receivable_applications_all arr,
         hz_cust_acct_sites_all acct_site,
         hz_party_sites party_site,
         hz_locations loc,
         hz_cust_site_uses_all site_uses,
         ar_cash_receipts_all acr,
         ar_cash_receipt_history_all crh,
         gl_code_combinations cc
 WHERE       TRUNC (ps.gl_date) <= :p_as_of_date
         AND ps.customer_id = cust_acct.cust_account_id
         AND cust_acct.account_number = :p_account_number
         AND ps.customer_id = cust_acct.cust_account_id
         AND acct_site.party_site_id = party_site.party_site_id
         AND loc.location_id = party_site.location_id
         AND ps.cash_receipt_id = acr.cash_receipt_id
         AND acr.cash_receipt_id = crh.cash_receipt_id
         AND crh.account_code_combination_id = cc.code_combination_id
         AND ps.trx_date <= :p_as_of_date
         AND ps.CLASS = 'PMT'
         AND ps.cash_receipt_id = arr.cash_receipt_id
         AND arr.status = 'UNAPP'
         AND ps.status = 'OP'
         AND site_uses.site_use_code = 'BILL_TO'
         AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
         AND NVL (site_uses.status, 'A') = 'A'
         AND cust_acct.cust_account_id = acct_site.cust_account_id
         AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
         AND ps.customer_id = acct_site.cust_account_id
         AND ps.customer_site_use_id = site_uses.site_use_id
HAVING   NVL (SUM (arr.amount_applied), 0) > 0;

Read More

AR :Query for statement with Bill-To & Ship-To address for AR Open Transactions

SELECT sob.name sob_name,
         hou.name ou_name,
         t.trx_number,
         hp.party_name,
         acct.account_number,
         rctt.name Transaction_type,
         t.bill_to_site_use_id,
            hl.address1
         || ','
         || hl.address2
         || ','
         || hl.city
         || ','
         || hl.county
         || ','
         || NVL (hl.state, hl.province)
            bill_to_address,
            hl1.address1
         || ','
         || hl1.address2
         || ','
         || hl1.city
         || ','
         || hl1.county
         || ','
         || NVL (hl1.state, hl.province)
            ship_to_address,
            hl1.address1
         || ','
         || hl1.address2
         || ','
         || hl1.city
         || ','
         || hl1.county
         || ','
         || NVL (hl1.state, hl.province)
            paying_cust_address,
         l.customer_trx_line_id,
         t.customer_trx_id,
         l.line_number,
         l.line_type,
         l.reason_code,
         l.quantity_invoiced,
         l.inventory_item_id,
         l.description,
         l.uom_code,
         l.unit_selling_price,
         (d.amount) extended_amount,
         p.amount_due_remaining balance_due,
         gcc.segment1,
         gcc.segment2,
         gcc.segment3,
         gcc.segment4,
         gcc.segment5,
         gcc.segment6,
         gcc.segment7,
         gcc.segment8,
         d.account_class,
         d.gl_date,
         d.acctd_amount,
         sp.salesrep_number,
         s.revenue_percent_split,
         l.link_to_cust_trx_line_id
FROM ra_customer_trx_all t,
         ar_payment_schedules_all p,
         gl_sets_of_books sob,
         hr_operating_units hou,
         ra_customer_trx_lines_all l,
         ra_cust_trx_line_gl_dist_all d,
         gl_code_combinations gcc,
         ra_cust_trx_line_salesreps_all s,
         jtf_rs_salesreps sp,
         ra_cust_trx_types_all rctt,
         ra_terms rt,
         hz_parties hp,
         hz_cust_site_uses_all hcsu,
         hz_cust_acct_sites_all hcas,
         hz_cust_accounts acct,
         hz_party_sites hps,
         hz_locations hl,
         hz_cust_site_uses_all hcsu1,
         hz_cust_acct_sites_all hcas1,
         hz_party_sites hps1,
         hz_locations hl1,
         hz_cust_accounts acct_pay,
         hz_cust_site_uses_all hcsu2,
         hz_cust_acct_sites_all hcas2,
         hz_party_sites hps2,
         hz_locations hl2
   WHERE     t.customer_trx_id = p.customer_trx_id
         AND p.status = 'OP'
         AND rctt.org_id = t.org_id
         AND t.set_of_books_id = sob.set_of_books_id
         AND t.org_id = hou.organization_id
         AND t.customer_trx_id = l.customer_trx_id
         AND t.cust_trx_type_id = rctt.cust_trx_type_id
         AND d.customer_trx_id = l.customer_trx_id
         AND d.customer_trx_line_id = l.customer_trx_line_id
         AND d.code_combination_id = gcc.code_combination_id
         AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
         AND s.customer_trx_id(+) = t.customer_trx_id
         AND s.salesrep_id = sp.salesrep_id(+)
         AND s.org_id = sp.org_id(+)
         AND t.term_id = rt.term_id(+)
         AND acct.party_id = hp.party_id
         AND t.bill_to_customer_id = acct.cust_account_id
         AND t.bill_to_site_use_id = hcsu.site_use_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND acct.cust_account_id = hcas.cust_account_id
         AND hcsu.site_use_code = 'BILL_TO'
         AND hcas.party_site_id = hps.party_site_id
         AND hps.location_id = hl.location_id
         AND t.ship_to_site_use_id = hcsu1.site_use_id(+)
         AND hcas1.cust_acct_site_id(+) = hcsu1.cust_acct_site_id
         AND p.class IN ('INV', 'DM')
         AND hcsu1.site_use_code(+) = 'SHIP_TO'
         AND hcas1.party_site_id = hps1.party_site_id(+)
         AND hps1.location_id = hl1.location_id(+)
         AND t.paying_customer_id = acct_pay.cust_account_id(+)
         AND t.paying_site_use_id = hcsu2.site_use_id(+)
         AND hcas2.cust_acct_site_id(+) = hcsu2.cust_acct_site_id
         AND hcas2.party_site_id = hps2.party_site_id(+)
         AND NVL (hcsu2.site_use_code, 'BILL_TO') = 'BILL_TO'
         AND hps2.location_id = hl2.location_id(+)
         AND EXISTS
                (SELECT 1
                   FROM ra_customer_trx_all t,
                        ar_payment_schedules_all p1,
                        gl_sets_of_books sob,
                        hr_operating_units hou,
                        hz_cust_accounts acct_bill_to,
                        hz_cust_accounts acct_pay,
                        jtf_rs_salesreps sp,
                        ra_terms rt,
                        ra_cust_trx_types_all rctt
                  WHERE     t.customer_trx_id = p1.customer_trx_id
                        AND p1.status = 'OP'
                        AND p1.customer_trx_id = p.customer_trx_id
                        AND t.set_of_books_id = sob.set_of_books_id
                        AND t.org_id = hou.organization_id
                        AND t.bill_to_customer_id =
                               acct_bill_to.cust_account_id
                        AND t.paying_customer_id = acct_pay.cust_account_id(+)
                        AND t.primary_salesrep_id = sp.salesrep_id(+)
                        AND t.org_id = sp.org_id(+)
                        AND t.term_id = rt.term_id(+)
                        AND t.cust_trx_type_id = rctt.cust_trx_type_id
                        AND t.org_id = rctt.org_id)
ORDER BY trx_number, customer_trx_line_id;

Read More

AR: Fetching Aging Bucket line details for a delinquent Customer account

Use this query to Fetch Aging bucket line details for a particular Customer to send dunning letter:-

You need to pass two parameter :

•   Dunning Plan Name
•   Customer Account Number

SELECT l.aging_bucket_line_id,
                l.type Aging_Bucket_Line_Type,
                l.days_start,
                l.days_to
  FROM ar_aging_bucket_lines l,
              ar_aging_buckets b,
              iex_dunning_plans_vl d
 WHERE 1 = 1
   AND d.name = :p_dunning_plan_name   
   AND d.aging_bucket_id = b.aging_bucket_id
   AND b.aging_bucket_id = l.aging_bucket_id
   AND (SELECT MAX (TRUNC (SYSDATE) - TRUNC (ar.due_date)) days
                 FROM iex_delinquencies del,
                             ar_payment_schedules ar,
                             hz_cust_accounts hca
             WHERE 1 = 1
             AND hca.cust_account_id = del.cust_account_id
             AND hca.account_number = :p_account_number
            AND del.payment_schedule_id = ar.payment_schedule_id
            AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
            BETWEEN l.days_start AND l.days_to
            AND EXISTS                    (SELECT 1
                     FROM iex_ag_dn_xref x
                     WHERE 1=1
                      AND d.dunning_plan_id = x.dunning_plan_id
                      AND d.aging_bucket_id = x.aging_bucket_id
                      AND x.aging_bucket_line_id = l.aging_bucket_line_id);

Read More

GL :Fetch Batch Name and perioddname from invoice

SELECT gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE  glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
and rcta.trx_number ='1301117'

Read More

AR : To fetch AR Transactions deatils - including lines and distributions

SELECT rcta.trx_number
     , rcta.customer_trx_id
     , rba.name batch
     , rcta.creation_date
     , rctta.name tx_type
     , hp.party_number party
     , hca.account_number act_no   
     , hp.party_name
     , hps.party_site_number site_num
     , rcta.request_id -- Autoinvoice Import Program
     , rbsa.name source
     , rbsa.description
     , rcta.trx_date
     , rctla.description line_descr
     , rctla.unit_selling_price
     , rctla.line_type
     , rctla.interface_line_context
     , rctla.tax_rate
     , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 cgh_acct
  FROM ar.ra_customer_trx_all rcta
     , ar.ra_customer_trx_lines_all rctla
     , ar.ra_cust_trx_line_gl_dist_all rctlgda
     , ar.ra_batches_all rba
     , ar.ra_cust_trx_types_all rctta
     , apps.hz_cust_accounts hca
     , apps.hz_parties hp
     , ar.hz_party_sites hps
     , apps.hz_cust_acct_sites_all hcasa
     , apps.hz_cust_site_uses_all hcsua
     , ar.hz_locations hl
     , applsys.fnd_user fu1
     , applsys.fnd_user fu2
     , ar.ra_batch_sources_all rbsa
     , gl.gl_code_combinations gcc
 WHERE rcta.bill_to_customer_id = hca.cust_account_id
   AND hp.party_id = hca.party_id
   AND hp.party_id = hps.party_id
   AND hcasa.party_site_id = hps.party_site_id
   AND hca.cust_account_id = hcasa.cust_account_id
   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
   AND rcta.created_by = fu1.user_id
   AND rcta.last_updated_by = fu2.user_id
   AND hcsua.site_use_id = rcta.bill_to_site_use_id
   AND hps.location_id = hl.location_id
   AND rcta.batch_source_id = rbsa.batch_source_id(+)
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.customer_trx_line_id = rctlgda.customer_trx_line_id
   AND rctlgda.code_combination_id = gcc.code_combination_id
   AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
   AND RCTA.BATCH_ID = RBA.BATCH_ID
--   AND RCTA.TRX_NUMBER = '123'
   AND rcta.creation_date >= '01-SEP-2012'
   AND rctla.line_type = 'LINE'
   AND rctla.description = 'Internal Award'
   AND gcc.segment1 = tbl_activity.flex_value
   AND gcc.segment2 = tbl_detail.flex_value
   AND 1 = 1;

Read More

AR : Fetch Customer Bank detail

select DISTINCT ac.customer_name
,ac.customer_number
from iby_pmt_instr_uses_all instr_assign,iby_external_payers_all
payee,iby_ext_bank_accounts bankacct,hz_cust_accounts_all hcal,ar_customers acwhere
instr_assign.instrument_id = bankacct.ext_bank_account_id
and instr_assign.ext_pmt_party_id = payee.ext_payer_id
and instr_assign.instrument_type = 'BANKACCOUNT'
and instr_assign.payment_flow ='FUNDS_CAPTURE'
and instr_assign.payment_function = 'CUSTOMER_PAYMENT'
and hcal.cust_account_id = payee.cust_account_id
and ac.customer_number = hcal.account_number
and bankacct.bank_account_num = <Bank account>

Read More

AR : Query for Fetching Query Transaction details

  SELECT sob.name sob_name,

         hou.name ou_name,

         l.customer_trx_line_id,

         t.customer_trx_id,

         l.line_number,

         l.line_type,

         l.reason_code,

         l.quantity_invoiced,

         l.inventory_item_id,

         l.description,

         l.uom_code,

         l.unit_selling_price,

         (d.amount) extended_amount,

         l.interface_line_context,

         l.interface_line_attribute1,

         l.interface_line_attribute2,

         l.interface_line_attribute3,

         l.interface_line_attribute4,

         l.interface_line_attribute5,

         l.interface_line_attribute6,

         l.interface_line_attribute7,

         l.interface_line_attribute8,

         l.interface_line_attribute9,

         gcc.segment1,

         gcc.segment2,

         gcc.segment3,

         gcc.segment4,

         gcc.segment5,

         gcc.segment6,

         gcc.segment7,

         gcc.segment8,

         d.account_class,

         d.gl_date,

         d.acctd_amount,

         d.attribute1,

         d.attribute2,

         d.attribute3,

         d.attribute4,

         sp.salesrep_number,

         s.revenue_percent_split,

         l.link_to_cust_trx_line_id

    FROM ra_customer_trx_all t,

         ar_payment_schedules_all p,

         gl_sets_of_books sob,

         hr_operating_units hou,

         ra_customer_trx_lines_all l,

         ra_cust_trx_line_gl_dist_all d,

         gl_code_combinations gcc,

         ra_cust_trx_line_salesreps_all s,

         jtf_rs_salesreps sp

   WHERE     t.customer_trx_id = p.customer_trx_id

         AND p.status = 'OP'

         AND t.set_of_books_id = sob.set_of_books_id

         AND t.org_id = hou.organization_id

         AND t.customer_trx_id = l.customer_trx_id

         AND d.customer_trx_id = l.customer_trx_id

         AND d.customer_trx_line_id = l.customer_trx_line_id

         AND d.code_combination_id = gcc.code_combination_id

         AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id

         AND s.customer_trx_id(+) = t.customer_trx_id

         AND s.salesrep_id = sp.salesrep_id(+)

         AND s.org_id = sp.org_id(+)

ORDER BY customer_trx_id,

         line_number,

         line_type,

         customer_trx_line_id;

Read More

AR : Query to fetch Customer Bank Accounts

The following query gives you the links required for matching a Bank Account to its Customer Site Record:


SELECT cust.party_name                   customer_name
,      cust_acct.account_number
,      cust_uses.site_use_code
,      cust_loc.address1
,      cust_loc.address2
,      cust_loc.address3
,      cust_loc.address4
,      cust_loc.city
,      cust_loc.postal_code
,      bank.party_name                   bank_name
,      bank_prof.home_country
,      branch.party_name                 branch_name
,      branch_prof.bank_or_branch_number branch_number
,      account.bank_account_num
,      account.bank_account_name
FROM   hz_parties bank
,      hz_relationships rel
,      hz_parties branch
,      hz_organization_profiles bank_prof
,      hz_organization_profiles branch_prof
,      iby_ext_bank_accounts account
,      iby_account_owners acc_owner
,      iby_external_payers_all ext_payer
,      iby_pmt_instr_uses_all acc_instr
,      hz_parties cust
,      hz_cust_accounts cust_acct
,      hz_cust_acct_sites_all cust_site
,      hz_cust_site_uses_all cust_uses
,      hz_locations cust_loc
WHERE  1=1
AND    bank.party_id = rel.object_id
and    bank.party_type = rel.object_type
AND    rel.object_table_name = 'HZ_PARTIES'
AND    rel.relationship_code = 'BRANCH_OF'
AND    rel.subject_id = branch.party_id
AND    rel.subject_type = branch.party_type
AND    rel.subject_table_name = 'HZ_PARTIES'
AND    bank.party_id = bank_prof.party_id
AND    branch.party_id = branch_prof.party_id
AND    bank.party_id = account.bank_id
AND    branch.party_id = account.branch_id
AND    account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND    acc_owner.account_owner_party_id = cust.party_id
AND    account.ext_bank_account_id = acc_instr.instrument_id
AND    acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND    ext_payer.cust_account_id = cust_acct.cust_account_id
AND    cust_acct.cust_account_id = cust_site.cust_account_id
AND    cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND    cust_uses.site_use_id = ext_payer.acct_site_use_id
AND    cust_uses.location = cust_loc.location_id
AND    cust.party_id = cust_acct.party_id;

Read More