Types of Invoice Class in AR Transaction

Invoice : A document that you create that lists amounts owed for the purchases of goods or services, any tax, freight charges and payment terms.

Credit Memo : A document that partially or fully reverses an original invoice.

Debit Memo : Debits that you assign to a customer to collect additional charges .

ChargeBack : A new debit item that you assign to your customer when closing an existing, outstanding debit item.

Deposit : A type of commitment whereby a customer agrees to deposit or prepay a sum of money for the future purchase of goods and services.

Guarantee : A contractual obligation to purchase a specified amount of goods or services over a predefined period of time.

Read More

Query to find strategies of a Customer Oracle R12 AR

Strategies of a Customer

SELECT DISTINCT(ist.cust_account_id),
  ist.status_code,
  ist.strategy_id,
  ist.strategy_template_id,
  ist.creation_date,
  hca.account_number,
  hca.account_name,
  istt.strategy_name
FROM iex_strategies ist,
  hz_cust_accounts_all hca,
  iex_strategy_templates_tl istt,
  (SELECT MAX(creation_date) max_date,
    cust_account_id
  FROM iex_strategies
  GROUP BY cust_account_id
  ) da
WHERE hca.cust_account_id    = ist.cust_account_id
AND ist.strategy_template_id = istt.strategy_temp_id
AND ist.object_type          = 'ACCOUNT'
AND ist.status_code         <> 'CANCELLED' --= 'OPEN'
AND TRUNC(ist.creation_date) =TRUNC(da.max_date)
AND ist.cust_account_id      =da.cust_account_id
AND hca.cust_account_id      =da.cust_account_id
AND hca.account_number      IN ('GL-21618645');

Read More

AR Transactions to GL Drill Down

select * from RA_CUSTOMER_TRX_ALL  where trx_number = &trx_num --pk customer_trx_id ; trx_number=100771
select * from RA_CUST_TRX_LINE_GL_DIST_ALL where customer_trx_id=1557 --pk cust_trx_line_gl_dist_id
select * from XLA_DISTRIBUTION_LINKS where source_distribution_id_num_1 in (1471,1331) --(373185,373186,373191) --pk application_id
select * from XLA_AE_LINES where ae_header_id=(14807) -- 193233 --pk ae_header_id
select * from GL_CODE_COMBINATIONS where code_combination_id in (98480,98482,98483)
select * from GL_LEDGERS where ledger_id=2023 --xla_ae_line links with this table using ledger_id

select distinct t.* from (
select gl.name Ledger_Name,
       (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
       gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6) Account,
       rctlgda.gl_date Gl_Date,
       xal.accounting_class_code class,
       xal.accounted_dr,
       xal.accounted_cr
  from GL_LEDGERS                   gl,
       GL_CODE_COMBINATIONS         gcc,
       XLA_AE_LINES                 xal,
       XLA_DISTRIBUTION_LINKS       xdl,
       RA_CUST_TRX_LINE_GL_DIST_ALL rctlgda,
       RA_CUSTOMER_TRX_ALL          rcta
 where gl.ledger_id = xal.ledger_id
   and gcc.code_combination_id = xal.code_combination_id
   and xal.ae_header_id = xdl.ae_header_id
   and xdl.source_distribution_id_num_1 = rctlgda.cust_trx_line_gl_dist_id
   and rctlgda.customer_trx_id = rcta.customer_trx_id
   and rcta.trx_number = &trx_number
   and xdl.source_distribution_type ='RA_CUST_TRX_LINE_GL_DIST_ALL' ) t

Read More

AR Receipts to Transaction Drill Down

 select acra.receipt_number,
           acra.amount,
           rcta.trx_number,
           acra.receipt_date,
           apsa.amount_applied
     from ar_cash_receipts_all acra,
          ar_receivable_applications_all araa,
          ra_customer_trx_all rcta,
          ar_payment_schedules_all apsa
    where acra.receipt_number = 'BACS170211MORETON'
      and  apsa.customer_trx_id=rcta.customer_trx_id
      and araa.cash_receipt_id = acra.cash_Receipt_id
      and rcta.customer_trx_id=araa.applied_customer_trx_id;

Read More

Drill Down from AR Cash Receipts to GL Ledger R12

Query:

select distinct t.* from
(select gl.name Ledger_Name,
       (gcc.segment1||'-'||
       gcc.segment2||'-'||
       gcc.segment3||'-'||
       gcc.segment4||'-'||
       gcc.segment5||'-'||
       gcc.segment6)Account,
       araa.gl_date Gl_Date,
       xal.accounting_class_code Class,
       xal.accounted_dr,
       xal.accounted_cr
from GL_LEDGERS gl,
     XLA_DISTRIBUTION_LINKS  xdl,
     XLA_AE_LINES            xal,
     gl_code_combinations    gcc,
     ar_cash_receipts_all    acra,
     ar_payment_schedules_all apsa,
     AR_RECEIVABLE_APPLICATIONS_ALL araa,
     AR_DISTRIBUTIONS_ALL           ada
where xal.ledger_id = gl.ledger_id
and      acra.cash_receipt_id = apsa.cash_receipt_id
and      apsa.payment_schedule_id = araa.payment_schedule_id
and      araa.payment_schedule_id = apsa.payment_schedule_id
and      ada.source_id = araa.receivable_application_id -----------
--and      ada.source_type = 'CASH'
--and      ada.code_combination_id = gcc.code_combination_id
and      ada.third_party_id = acra.pay_from_customer
and      ada.third_party_sub_id = acra.customer_site_use_id
and      xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =  ada.line_id
and      ada.source_table = 'RA'
and      xdl.event_id = araa.event_id
--and      xdl.event_class_code like 'RECEIPT' ---------
--and      xdl.event_type_code like 'RECEIPT_ALL'------------
and      xdl.source_distribution_type like 'AR_DISTRIBUTIONS_ALL'
--and      xdl.rounding_class_code = 'RECEIVABLE'
--and      xdl.application_id = 222
and      xal.ae_header_id = xdl.ae_header_id
and      xal.code_combination_id = gcc.code_combination_id
and      acra.receipt_number = &Recipt_Number ) t


select * from ar_distributions_all;

select * from XLA_DISTRIBUTION_LINKS where source_distribution_type LIKE 'AR_DISTRIBUTIONS_ALL';

Read More