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

AP:Query fetching both Prepayment Data and Invoice Data of a Payable Invoice

This Query will find a query which fetches both invoice and prepayment information of a payable invoice ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

SELECT pv.vendor_name "Vendor Name",

       ai.invoice_num "Invoice Num",

       ai.invoice_id "Invoice Id",

       ai.invoice_amount "Invoice amount",

       ail.line_number "Invoice Line Num",

       ai2.invoice_id "Prepay Invoice Id",

       ai2.invoice_num "Prepay Invoice Num",

       ail.prepay_line_number "Prepay Invoice Line Num",

       (-1)*(ail.amount - NVL(ail.included_tax_amount,0))

       "Prepay Amount Applied",

       NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) +      

       NVL(ail.total_nrec_tax_amount, 0)), 0)

       "Tax amount Applied"

FROM   AP_INVOICES_ALL ai,

       AP_INVOICES_ALL ai2,

       AP_INVOICE_LINES_ALL ail,

       AP_SUPPLIERS pv

WHERE ai.invoice_id                  = ail.invoice_id

AND   ai2.invoice_id                   = ail.prepay_invoice_id

AND   ail.amount                       < 0

AND   NVL(ail.discarded_flag,'N')     <> 'Y'

AND   ail.line_type_lookup_code        = 'PREPAY'

AND   ai.vendor_id                     = pv.vendor_id

AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')

AND   ai.invoice_id                    = '166014';

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

SysAdmin :SQL Query to display Current Statement of Active Session/Program

     SELECT s.sid,
       s.client_info,
       s.machine,
       s.program,
       s.type,
       s.logon_time,
       s.osuser,
       sq.sorts,
       sq.disk_reads,
       sq.buffer_gets,
       sq.rows_processed,
       sq.sqltype,
       sq.sql_text
  FROM gv$session s,
               gv$sql sq
 WHERE s.sql_hash_value = sq.hash_value
   AND s.inst_id = :p_inst_id           -- replace with ID from above
   AND s.sid = :p_sid                   -- replace with instID from above
   AND sq.inst_id = s.inst_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