AR Open Balances Revaluation

Description
Categories: BI Publisher
Application: Receivables
Source: AR Open Balances Revaluation Report
Short Name: AROBRR
DB package: ar_obalrev_pkg
SELECT hou.name                     ou_name
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')             accounting_flexfield
      ,rcta.invoice_currency_code   currency_code
      ,hp.party_name                customer_name	  
      ,rcta.trx_number              invoice_number
	  , ''                          lookup_code 
	  , rcta.trx_date               invoice_date
	  ,rcta.doc_sequence_value      internal_invoice_no
	  ,rctt.type                    transaction_type
	  ,rcta.customer_trx_id         cust_trx_id
	   , DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(rcta.invoice_currency_code),0)) 
				                    exchange_rate	  
	  ,SUM(aps.amount_due_original) invoice_amt_entered
	  ,SUM(aps.amount_due_original) test
      ,0                            invoice_amt_accounted                                 
      ,SUM(aps.amount_due_original) -   NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
                                              AND    ara.applied_customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) +
										NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
											  AND    ara.application_type       != 'CASH'
											  AND    rctt.type                   = 'CM'
                                              AND    ara.customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) + 
											  NVL(
                                                  (
                                                  SELECT SUM(ara.amount)
                                                    FROM ar_adjustments ara
                                                   WHERE ara.customer_trx_id = rcta.customer_trx_id
                                                   AND   ara.gl_date <= &gd_date_to),0
                                                   ) inv_amt_due
      ,  NVL(APS.exchange_rate,1) * (SUM(aps.amount_due_original) -   NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
                                              AND    ara.applied_customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) +
										NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
											  AND    ara.application_type       != 'CASH'
											  AND    rctt.type                   = 'CM'
                                              AND    ara.customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) + 
											  NVL(
                                                  (
                                                  SELECT SUM(ara.amount)
                                                    FROM ar_adjustments ara
                                                   WHERE ara.customer_trx_id = rcta.customer_trx_id
                                                   AND   ara.gl_date <= &gd_date_to),0
                                                   )    )  inv_amt_due_er
      ,(SUM(APS.amount_due_original) -  NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
                                              AND    ara.applied_customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) +
										NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
											  AND    ara.application_type       != 'CASH'
											  AND    rctt.type                   = 'CM'
                                              AND    ara.customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) +
										  NVL(
                                                   (
                                                     SELECT SUM(ARA.amount)
                                                       FROM ar_adjustments ARA
                                                      WHERE ARA.customer_trx_id = RCtA.customer_trx_id
                                                      AND   ara.gl_date <= &gd_date_to
													),0
                                                    )
		 ) * NVL(DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(rcta.invoice_currency_code),0)),0) inv_amt_due_reval                            
  FROM  hr_operating_units           hou
       ,ra_customer_trx              rcta
       ,ar_payment_schedules         aps
       ,ra_cust_trx_types            rctt
       ,ra_cust_trx_line_gl_dist     rctlgd
       ,gl_code_combinations         gcc
       ,xla_distribution_links       xdl
       ,xla_ae_lines                 xal
       ,gl_import_references         gir
       ,gl_je_headers                gjh
       ,hz_cust_accounts             hca
       ,hz_parties                   hp
       ,ar_system_parameters         sp
  WHERE HOU.set_of_books_id          = sp.set_of_books_id
  AND   RCtA.customer_trx_id        = APS.customer_trx_id
  AND   RCtA.org_id                 = HOU.organization_id
  AND   APS.org_id                  = RCtA.org_id
  AND   RCtA.cust_trx_type_id       = RCTT.cust_trx_type_id
  AND   RCtA.org_id                 = RCTT.org_id
  AND   RCTLGD.org_id               = RCTT.org_id
  AND   APS.customer_trx_id         = RCTLGD.customer_trx_id
--  AND   gcc.code_combination_id     = RCTLGD.code_combination_id 
  AND   aps.gl_date                <= &gd_date_to
  AND   rctlgd.account_class        ='REC'
  AND   RCTLGD.latest_rec_flag      ='Y'
  AND   xdl.source_distribution_id_num_1 =rctlgd.cust_trx_line_gl_dist_id
  AND   xdl.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
  AND   xdl.application_id =222
  AND   xal.ae_header_id =xdl.ae_header_id
  AND   xal.ae_line_num =xdl.ae_line_num
  AND   xal.application_id =222
  AND   xal.accounting_class_code ='RECEIVABLE'
  AND   gcc.code_combination_id =xal.code_combination_id
  AND   gir.gl_sl_link_id =xal.gl_sl_link_id
  AND   gir.gl_sl_link_table =xal.gl_sl_link_table
  AND   gjh.je_header_id =gir.je_header_id 
  AND   gjh.status ='P'
  AND   gjh.ledger_id = sp.set_of_books_id
  AND   sp.org_id = :P_ORG_ID
  AND   rcta.bill_to_customer_id   = hca.cust_account_id
  AND   hp.party_id                = hca.party_id
  --AND   HCA.account_number = :P_CUSTOMER
  AND   &gc_trx_date_where1
  AND   &gc_ou_where1
  AND   &gc_customer_where1       
  AND   &gc_currency_where1
  AND   &gc_incl_domestic_inv_where1  
 GROUP BY  hou.name
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
      ,rcta.invoice_currency_code
      ,hp.party_name
      ,rcta.trx_number
	  , ''
	  , rcta.trx_date
	  ,rcta.doc_sequence_value
	  ,rctt.type
	  ,hca.cust_account_id
	   , DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(rcta.invoice_currency_code),0))
	,rcta.customer_trx_id
	,   NVL(APS.exchange_rate,1)  -- added for bug 18327012
HAVING  NVL((SUM(APS.amount_due_original) -  NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
                                              AND    ara.applied_customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) +
										NVL(
                                             (
                                              SELECT SUM(ara.amount_applied)
                                              FROM   ar_receivable_applications ara
                                              WHERE  ara.status                  = 'APP'
											  AND    ara.application_type       != 'CASH'
											  AND    rctt.type                   = 'CM'
                                              AND    ara.customer_trx_id = rcta.customer_trx_id
                                              AND    ara.gl_date                 <= &gd_date_to),0
                                              ) +
											  NVL(
                                                           (
                                                             SELECT SUM(ARA.amount)
                                                               FROM ar_adjustments ARA
                                                              WHERE ARA.customer_trx_id = RCtA.customer_trx_id
                                                              AND   ara.gl_date <= &gd_date_to),0)),0
            ) <> 0
UNION
SELECT abc.op_name                         ou_name,
  abc.accounting_flexfield                 accounting_flexfield,
  abc.currency_code                        currency_code,
  hp.party_name                            customer_name,
  abc.invoice_number                       invoice_number,
  abc.lookup_code   	                   lookup_code,
  abc.receipt_date                         invoice_date,
 abc.int_invoice_number                    internal_invoice_no,
  abc.trans_type                           transaction_type,
  abc.custid                               cust_trx_id,  
  abc.exchange_rate                        exchange_rate,  
  abc.orig_amt                        invoice_amt_entered,
  NVL(ar_obalrev_pkg.test(abc.invoice_number,abc.orig_amt),0) test,
  0                                        invoice_amt_accounted,
  0-abc.original_amount                      inv_amt_due,  
  0-abc.historic_amount                      inv_amt_due_er,
  0-abc.closing_amount                       inv_amt_due_reval
FROM
  (
SELECT hou.name op_name,
     fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') accounting_flexfield,
     hca.cust_account_id custid,     
     decode(ara.status,    'ACC',    '*' || al.meaning) trans_type,
     acr.receipt_number invoice_number,
     acr.doc_sequence_value int_invoice_number,     
     acr.receipt_date,
	 al.lookup_code,   	
     acr.amount orig_amt,
     nvl(acr.exchange_rate,    1) orig_invoice_rate,
     SUBSTR(acr.currency_code,    1,    3) currency_code,
    (SUM(nvl(ara.amount_applied,0))) original_amount,
    (SUM(nvl(acr.exchange_rate,    1) *nvl(ara.amount_applied,    0))) historic_amount,
    ((SUM(NVL(ara.amount_applied,0))) *(DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(acr.currency_code),0)))) closing_amount,
	DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(acr.currency_code),0)) exchange_rate
   FROM hr_operating_units hou,
     ar_receivable_applications ara,
     ar_lookups al,
     ar_cash_receipts acr,
     ar_cash_receipt_history acrh,
     hz_cust_accounts hca,
     gl_code_combinations gcc,
	 ar_distributions ads,
	 xla_distribution_links xdl,
	 xla_ae_lines xal,
	 gl_import_references  gir,
     gl_je_headers gjh,
     ar_system_parameters sp
   WHERE hou.set_of_books_id = sp.set_of_books_id
   AND acr.org_id = hou.organization_id
   AND acr.org_id = ara.org_id
   AND hca.cust_account_id = acr.pay_from_customer
   AND acr.cash_receipt_id = ara.cash_receipt_id
   AND acrh.cash_receipt_id = ara.cash_receipt_id
   AND ara.cash_receipt_history_id = acrh.cash_receipt_history_id
   AND al.lookup_type = 'PAYMENT_TYPE'
   AND ara.status = al.lookup_code
--   AND gcc.code_combination_id = ara.code_combination_id 
   --AND trate.tran_curr_code(+) = acr.currency_code
   AND ara.status = 'ACC'
   AND not exists(
   select 'X' from ar_cash_receipt_history crhin
   where crhin.cash_receipt_id = acr.cash_receipt_id
   AND crhin.status = 'REVERSED'
   )   
   AND nvl(ara.confirmed_flag,    'Y') = 'Y'
   AND nvl(acr.confirmed_flag,    'Y') = 'Y'   
   AND ads.source_id = acrh.CASH_RECEIPT_HISTORY_ID
   AND xdl.source_distribution_id_num_1 = ads.line_id
   AND xdl.application_id =222
   AND xal.ae_header_id =xdl.ae_header_id
   AND 	xal.ae_line_num =xdl.ae_line_num
   AND 	xal.application_id =222
   AND  xal.accounting_class_code ='ACC'
   AND 	gcc.code_combination_id =xal.code_combination_id
   AND  gir.gl_sl_link_id =xal.gl_sl_link_id
   AND 	gir.gl_sl_link_table =xal.gl_sl_link_table
   AND 	gjh.je_header_id =gir.je_header_id
   AND  gjh.ledger_id = sp.set_of_books_id
   AND sp.org_id = :P_ORG_ID
   AND 	gjh.status ='P'
   AND &gc_trx_date_where
   AND &gc_incl_domestic_inv_where
   AND &gc_customer_where
   AND &gc_currency_where
   AND &gc_ou_where
  GROUP BY hou.name,
     fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
     hca.cust_account_id,
     decode(ara.status,    'ACC',    '*' || al.meaning),
     acr.receipt_number,
     acr.doc_sequence_value,     
     acr.receipt_date,al.lookup_code,
	 acr.amount,
     nvl(acr.exchange_rate,    1),
     SUBSTR(acr.currency_code,    1,    3),
     acr.currency_code HAVING SUM(ara.amount_applied) <> 0
   UNION
   SELECT hou.name op_name,
     fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') accounting_flexfield,
     hca.cust_account_id custid,
     decode(ara.status,    'UNAPP',    '*' || al.meaning) trans_type,
     acr.receipt_number invoice_number,
     acr.doc_sequence_value int_invoice_number,     
     acr.receipt_date,
	 al.lookup_code ,  	
     acr.amount orig_amt,
     nvl(acr.exchange_rate,    1) orig_invoice_rate,
     SUBSTR(acr.currency_code,    1,    3) currency_code,
    (SUM(ara.amount_applied)) original_amount,
    (SUM(nvl(acr.exchange_rate,    1) *nvl(ara.amount_applied,    0))) historic_amount,
    (SUM(ara.amount_applied)) *(DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(acr.currency_code),0))) closing_amount,
    DECODE(:p_exchange_rate_type,'User',REPLACE(:p_exchange_rate,',','.')
		        ,NVL(ar_obalrev_pkg.get_rate(acr.currency_code),0)) exchange_rate
   FROM hr_operating_units hou,
     ar_receivable_applications ara,
     ar_lookups al,
     ar_cash_receipts acr,
     ar_cash_receipt_history acrh,
     hz_cust_accounts hca,
     gl_code_combinations gcc,	 
	 ar_distributions ads,
	 xla_distribution_links xdl,
	 xla_ae_lines xal,
     gl_import_references  gir,
     gl_je_headers gjh,
     ar_system_parameters sp
   WHERE hou.set_of_books_id = sp.set_of_books_id
   AND acr.org_id = hou.organization_id
   AND acr.org_id = ara.org_id
   AND hca.cust_account_id = acr.pay_from_customer
   AND acr.cash_receipt_id = ara.cash_receipt_id
   AND acrh.cash_receipt_id = ara.cash_receipt_id
   AND ara.cash_receipt_history_id = acrh.cash_receipt_history_id
   AND al.lookup_type = 'PAYMENT_TYPE'
   AND ara.status = al.lookup_code
--   AND gcc.code_combination_id = ara.code_combination_id
   AND ara.status = 'UNAPP'
   AND nvl(ara.confirmed_flag,    'Y') = 'Y'
   AND nvl(acr.confirmed_flag,    'Y') = 'Y'   
   AND ads.source_id = acrh.cash_receipt_history_id
   AND xdl.source_distribution_id_num_1 = ads.line_id
   AND xdl.application_id =222
   AND xal.ae_header_id =xdl.ae_header_id
   AND 	xal.ae_line_num =xdl.ae_line_num
   AND 	xal.application_id =222
   AND  xal.accounting_class_code ='UNAPP'
    AND ads.source_type <> 'BANK_CHARGES'     -- added to exclude Bank Charges 18327012
	AND ads.source_table='CRH'               -- added to exclude Bank Charges 18327012
	AND xdl.SOURCE_DISTRIBUTION_TYPE='AR_DISTRIBUTIONS_ALL'  --for bug  18327012
 AND 	gcc.code_combination_id =xal.code_combination_id
   AND  gir.gl_sl_link_id =xal.gl_sl_link_id
   AND 	gir.gl_sl_link_table =xal.gl_sl_link_table
   AND 	gjh.je_header_id =gir.je_header_id
   AND  gjh.ledger_id = sp.set_of_books_id
   AND  sp.org_id = :P_ORG_ID
   AND 	gjh.status ='P'
   AND not exists(
   select 'X' from ar_cash_receipt_history crhin
   where crhin.cash_receipt_id = acr.cash_receipt_id
   AND crhin.status = 'REVERSED'
   )
   AND &gc_trx_date_where
   AND &gc_incl_domestic_inv_where
   AND &gc_customer_where
   AND &gc_currency_where
   AND &gc_ou_where
  GROUP BY hou.name,
     fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
     hca.cust_account_id,
     decode(ara.status,    'UNAPP',    '*' || al.meaning),
     acr.receipt_number,
     acr.doc_sequence_value,     
     acr.receipt_date,al.lookup_code,
	 acr.amount,
     nvl(acr.exchange_rate,    1),
     SUBSTR(acr.currency_code,    1,    3),
     acr.currency_code HAVING SUM(ara.amount_applied) <> 0)
abc,
  hz_cust_accounts hca,
  hz_parties hp
WHERE hca.party_id = hp.party_id
 AND hca.cust_account_id = abc.custid
 AND &gc_customer_where1
ORDER BY invoice_number