AR Customer Statement

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Customer Statement
Short Name: ARSTMTRPT
DB package: AR_TP_STMT_PKG
SELECT 'T'                       trx_type
      ,rctt.name                 transaction_type
      ,NULL                      adjustment_number
      ,rct.trx_number            transaction_number
      ,rctld.gl_date             GL_Date
      ,rct.trx_date              transaction_date
      ,apsa.due_date             transaction_due_date
      ,rct.invoice_currency_code transaction_currency
      ,rctld.amount              entered_amount
      ,rctld.acctd_amount        accounted_amount
      ,gp.period_name            gp_period_name
      ,gp.period_num             gp_period_num
      ,gp.start_date             gp_start_date
      ,apsa.amount_due_original  due_original
      ,apsa.amount_due_remaining due_remaining
      ,rct.status_trx            trx_status
 FROM  ra_customer_trx          rct
      ,ar_payment_schedules_all apsa
      ,ra_cust_trx_types_all    rctt
      ,ra_cust_trx_line_gl_dist_all rctld
      ,gl_periods               gp
      ,gl_ledgers               gled
 WHERE rct.customer_trx_id        = apsa.customer_trx_id(+)
   AND rct.customer_trx_id        = rctld.customer_trx_id
   AND rct.cust_trx_type_id       = rctt.cust_trx_type_id
   AND rct.org_id                 = rctt.org_id
   AND gled.period_set_name       = gp.period_set_name
   AND gled.accounted_period_type = gp.period_type
   AND gled.ledger_id             = rct.set_of_books_id
   AND rctld.gl_date              BETWEEN gp.start_date AND gp.end_date
   AND rctld.latest_rec_flag      = 'Y'
   AND rctld.account_class        = 'REC'
   AND rctt.post_to_gl            = 'Y' -- Only Postable to GL are picked
   AND rctt.type  IN ('CB','INV','DM','CM','BR','DEP') -- Guarantees are not picked
   AND rct.trx_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
   AND rctld.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
   AND rct.bill_to_customer_id    = :customer_party_id
   AND rct.bill_to_site_use_id    = :customer_party_site_id
   AND gp.adjustment_period_flag  = 'N'
   &gc_org_id
   &gc_currency
   &gc_accounted
   &gc_incomplete_trx
UNION ALL
SELECT 'R'                       trx_type
      ,arm.name                  transaction_type
      ,NULL                      adjustment_number
      ,acr.receipt_number        transaction_number
      ,acrh.gl_date              GL_Date
      ,acr.receipt_date          transaction_date
      ,apsa.due_date             transaction_due_date
      ,acr.currency_code         transaction_currency
      ,acr.amount                entered_amount
      ,acr.amount * NVL(acr.exchange_rate,1) accounted_amount
      ,gp.period_name            gp_period_name
      ,gp.period_num             gp_period_num
      ,gp.start_date             gp_start_date
      ,apsa.amount_due_original  due_original
      ,apsa.amount_due_remaining due_remaining
      ,acrh.status               trx_status
 FROM  ar_cash_receipts            acr
      ,ar_receipt_methods          arm
      ,ar_cash_receipt_history_all acrh
      ,ar_payment_schedules_all    apsa
      ,gl_periods                  gp
      ,gl_ledgers                  gled
 WHERE acr.cash_receipt_id           = apsa.cash_receipt_id(+)
   AND acr.org_id                    = apsa.org_id(+)
   AND acr.receipt_method_id         = arm.receipt_method_id
   AND acr.cash_receipt_id           = acrh.cash_receipt_id
   AND acr.org_id                    = acrh.org_id
   AND gled.period_set_name          = gp.period_set_name
   AND gled.accounted_period_type    = gp.period_type
   AND acr.set_of_books_id           = gled.ledger_id
   AND acrh.gl_date                  BETWEEN gp.start_date AND gp.end_date
   AND acrh.first_posted_record_flag = 'Y'
   AND acrh.trx_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
   AND acrh.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
   AND acr.pay_from_customer         = :customer_party_id
   AND acr.customer_site_use_id      = :customer_party_site_id
   AND gp.adjustment_period_flag  = 'N'
   &gc_rcpt_org_id
   &gc_rcpt_currency
   &gc_rcpt_accounted
UNION ALL
SELECT 'R'                       trx_type
      ,arm.name                  transaction_type
      ,NULL                      adjustment_number
      ,acr.receipt_number        transaction_number
      ,acrh.gl_date              GL_Date
      ,acr.receipt_date          transaction_date
      ,apsa.due_date             transaction_due_date
      ,acr.currency_code         transaction_currency
      ,acr.amount                entered_amount
      ,acr.amount * NVL(acr.exchange_rate,1) accounted_amount
      ,gp.period_name            gp_period_name
      ,gp.period_num             gp_period_num
      ,gp.start_date             gp_start_date
      ,apsa.amount_due_original  due_original
      ,apsa.amount_due_remaining due_remaining
      ,acrh.status               trx_status
 FROM  ar_cash_receipts            acr
      ,ar_receipt_methods          arm
      ,ar_cash_receipt_history_all acrh
      ,ar_payment_schedules_all    apsa
      ,gl_periods                  gp
      ,gl_ledgers                  gled
 WHERE acr.cash_receipt_id        = apsa.cash_receipt_id(+)
   AND acr.org_id                 = apsa.org_id(+)
   AND acr.receipt_method_id      = arm.receipt_method_id
   AND acr.cash_receipt_id        = acrh.cash_receipt_id
   AND acr.org_id                 = acrh.org_id
   AND gled.period_set_name       = gp.period_set_name
   AND gled.accounted_period_type = gp.period_type
   AND acr.set_of_books_id        = gled.ledger_id
   AND acrh.gl_date               BETWEEN gp.start_date AND gp.end_date
   AND acr.reversal_date          IS NOT NULL
   AND acrh.current_record_flag   = 'Y'
   AND acrh.status                = 'REVERSED'
   -- To Consider the first status for Reversed Receipts
   AND acrh.trx_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
   AND acrh.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
   AND acr.pay_from_customer      = :customer_party_id
   AND acr.customer_site_use_id   = :customer_party_site_id
   AND gp.adjustment_period_flag  = 'N'
   &gc_rcpt_org_id
   &gc_rcpt_currency
   &gc_rcpt_accounted
UNION ALL
SELECT 'A'                       trx_type
      ,al.meaning                transaction_type
      ,aa.adjustment_number      adjustment_number
      ,rct.trx_number            transaction_number
      ,aa.gl_date                GL_Date
      ,aa.apply_date             transaction_date
      ,aa.apply_date             transaction_due_date
      ,rct.invoice_currency_code transaction_currency
      ,aa.amount                 entered_amount
      ,aa.acctd_amount           accounted_amount
      ,gp.period_name            gp_period_name
      ,gp.period_num             gp_period_num
      ,gp.start_date             gp_start_date
      ,apsa.amount_due_original  due_original
      ,apsa.amount_due_remaining due_remaining
      ,aa.status                 trx_status
 FROM  ar_adjustments           aa
      ,ar_lookups               al
      ,ra_customer_trx_all      rct
      ,ra_cust_trx_types_all    rctt  
      ,ar_payment_schedules_all apsa
      ,gl_periods               gp
      ,gl_ledgers               gled
 WHERE rct.customer_trx_id        = apsa.customer_trx_id(+)
   AND rct.org_id                 = apsa.org_id(+)
   AND rct.cust_trx_type_id       = rctt.cust_trx_type_id
   AND rct.org_id                 = rctt.org_id
   AND rct.customer_trx_id        = aa.customer_trx_id
   AND rct.org_id                 = aa.org_id
   AND gled.period_set_name       = gp.period_set_name
   AND gled.accounted_period_type = gp.period_type
   AND gled.ledger_id             = aa.set_of_books_id
   AND al.lookup_type             = 'ADJUSTMENT_TYPE'
   AND aa.status                  = 'A' -- For approved Adjustments
   AND aa.type                    = al.lookup_code
   AND aa.gl_date                 BETWEEN gp.start_date AND gp.end_date
   AND aa.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
   AND aa.apply_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
   AND rct.bill_to_customer_id    = :customer_party_id
   AND rct.bill_to_site_use_id    = :customer_party_site_id
   AND gp.adjustment_period_flag  = 'N'
   AND rctt.post_to_gl            = 'Y' -- Only Postable to GL are picked
   AND rctt.type  IN ('CB','INV','DM','CM','BR','DEP') -- Guarantees are not picked
   &gc_org_id
   &gc_currency
   &gc_adj_accounted
   &gc_incomplete_trx
UNION ALL
SELECT 'R'                                trx_type
      ,art.name                           transaction_type
      ,NULL                               adjustment_number
      ,acr.receipt_number                 transaction_number
      ,ara.gl_date                        gl_date
      ,ara.apply_date                     transaction_date
      ,ara.apply_date                     transaction_due_date
      ,acr.currency_code                  transaction_currency
      ,-1*(ara.amount_applied)            entered_amount
      ,-1*(ara.acctd_amount_applied_from) accounted_amount
      ,gp.period_name                     gp_period_name
      ,gp.period_num                      gp_period_num
      ,gp.start_date                      gp_start_date
      ,0                                  due_original
      ,0                                  due_remaining
      ,art.status                         trx_status
FROM  ar_cash_receipts               acr
     ,ar_receivable_applications_all ara
     ,ar_receivables_trx_all         art
     ,gl_periods                     gp
     ,gl_ledgers                     gled
WHERE acr.cash_receipt_id        = ara.cash_receipt_id
  AND acr.org_id                 = ara.org_id
  AND ara.receivables_trx_id     = art.receivables_trx_id
  AND ara.org_id                 = art.org_id
  AND gled.period_set_name       = gp.period_set_name
  AND gled.accounted_period_type = gp.period_type
  AND acr.set_of_books_id        = gled.ledger_id
  AND ara.gl_date BETWEEN gp.start_date AND gp.end_date
  AND ara.apply_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
  AND ara.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
  AND acr.pay_from_customer      = :customer_party_id
  AND acr.customer_site_use_id   = :customer_party_site_id
  AND art.type                   = 'WRITEOFF'
--AND art.status                 = 'A' --Only Active Receipt WriteOffs
  AND gp.adjustment_period_flag  = 'N'
  &gc_rcpt_org_id
  &gc_rcpt_currency
  &gc_app_accounted
ORDER BY 13,12
Parameter Name SQL text Validation
Reporting Context Name
 
Accounted Transactions
 
LOV Oracle
Include Incomplete Transactions
 
LOV Oracle
Customer Class
 
LOV Oracle
Customer Category
 
LOV Oracle
Currency
 
LOV Oracle
Customer Name To
 
LOV Oracle
Customer Name From
 
LOV Oracle
GL Date To
 
Date
GL Date From
 
Date
Document Date To
 
Date
Document Date From
 
Date
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle
Ask a question