eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FII_AR_REC_DETAIL_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 43


  l_unapp_select_sql  VARCHAR2(2000);

Line: 44


  l_unapp_end_select_sql   VARCHAR2(2000);

Line: 155


     l_unapp_select_sql :=
     'SELECT
		FII_AR_RCT_ACCT_NUM		FII_AR_RCT_ACCT_NUM,
		FII_AR_RCT_NUM			FII_AR_RCT_NUM,
		FII_AR_RCT_DATE			FII_AR_RCT_DATE,
		FII_AR_RCT_GL_DATE		FII_AR_RCT_GL_DATE,
		FII_AR_RCT_PAY_METHOD		FII_AR_RCT_PAY_METHOD,
		FII_AR_RCT_STATUS		FII_AR_RCT_STATUS,
		FII_AR_RCT_AMT_RCURR		FII_AR_RCT_AMT_RCURR,
		SUM(FII_AR_RCT_AMT)			FII_AR_RCT_AMT,
		SUM(FII_AR_RCT_APP_AMT)		FII_AR_RCT_APP_AMT,
		SUM(FII_AR_RCT_UNAPP_AMT)		FII_AR_RCT_UNAPP_AMT,
		SUM(FII_AR_RCT_EARNED_DCT)		FII_AR_RCT_EARNED_DCT,
		SUM(FII_AR_RCT_UNEARNED_DCT)		FII_AR_RCT_UNEARNED_DCT,
		SUM(SUM(FII_AR_RCT_AMT)) over()		FII_AR_GT_RCT_AMT,
		SUM(SUM(FII_AR_RCT_APP_AMT)) over()		FII_AR_GT_RCT_APP_AMT,
		SUM(SUM(FII_AR_RCT_UNAPP_AMT)) over()		FII_AR_GT_RCT_UNAPP_AMT,
		SUM(SUM(FII_AR_RCT_EARNED_DCT)) over()	FII_AR_GT_RCT_EARNED_DCT,
		SUM(SUM(FII_AR_RCT_UNEARNED_DCT)) over()	FII_AR_GT_RCT_UNEARNED_DCT,
		FII_AR_RCT_NUM_DRILL		FII_AR_RCT_NUM_DRILL,
		FII_AR_RCT_AMT_DRILL		FII_AR_RCT_AMT_DRILL,
		FII_AR_RCT_APP_AMT_DRILL	FII_AR_RCT_APP_AMT_DRILL
	FROM (';

Line: 179


     l_unapp_end_select_sql := ' AND FII_AR_RCT_UNAPP_AMT <>0';

Line: 226


     l_unapp_select_sql :=
     'SELECT
		FII_AR_RCT_ACCT_NUM		FII_AR_RCT_ACCT_NUM,
		FII_AR_RCT_NUM			FII_AR_RCT_NUM,
		FII_AR_RCT_DATE			FII_AR_RCT_DATE,
		FII_AR_RCT_GL_DATE		FII_AR_RCT_GL_DATE,
		FII_AR_RCT_PAY_METHOD		FII_AR_RCT_PAY_METHOD,
		FII_AR_RCT_STATUS		FII_AR_RCT_STATUS,
		FII_AR_RCT_AMT_RCURR		FII_AR_RCT_AMT_RCURR,
		SUM(FII_AR_RCT_AMT)			FII_AR_RCT_AMT,
		SUM(FII_AR_RCT_APP_AMT)		FII_AR_RCT_APP_AMT,
		SUM(FII_AR_RCT_UNAPP_AMT)		FII_AR_RCT_UNAPP_AMT,
		SUM(FII_AR_RCT_EARNED_DCT)		FII_AR_RCT_EARNED_DCT,
		SUM(FII_AR_RCT_UNEARNED_DCT)		FII_AR_RCT_UNEARNED_DCT,
		SUM(SUM(FII_AR_RCT_AMT)) over()		FII_AR_GT_RCT_AMT,
		SUM(SUM(FII_AR_RCT_APP_AMT)) over()		FII_AR_GT_RCT_APP_AMT,
		SUM(SUM(FII_AR_RCT_UNAPP_AMT)) over()		FII_AR_GT_RCT_UNAPP_AMT,
		SUM(SUM(FII_AR_RCT_EARNED_DCT)) over()	FII_AR_GT_RCT_EARNED_DCT,
		SUM(SUM(FII_AR_RCT_UNEARNED_DCT)) over()	FII_AR_GT_RCT_UNEARNED_DCT,
		FII_AR_RCT_NUM_DRILL		FII_AR_RCT_NUM_DRILL,
		FII_AR_RCT_AMT_DRILL		FII_AR_RCT_AMT_DRILL,
		FII_AR_RCT_APP_AMT_DRILL	FII_AR_RCT_APP_AMT_DRILL
	FROM (';

Line: 250


     l_unapp_end_select_sql := 'AND FII_AR_RCT_UNAPP_AMT <>0';

Line: 366


  l_sqlstmt :='SELECT  FII_AR_RCT_ACCT_NUM FII_AR_RCT_ACCT_NUM,
             FII_AR_RCT_NUM,FII_AR_RCT_DATE,
	     FII_AR_RCT_GL_DATE,
	     m.name FII_AR_RCT_PAY_METHOD ,
             DECODE(l.lookup_code,''NSF'',l.meaning, ''REV'',l.meaning ,''STOP'', l.meaning, ''-'') FII_AR_RCT_STATUS,
	     FII_AR_RCT_AMT_RCURR,
	     SUM(FII_AR_RCT_AMT) FII_AR_RCT_AMT,
	     SUM(FII_AR_RCT_APP_AMT) FII_AR_RCT_APP_AMT,
	     SUM(FII_AR_RCT_UNAPP_AMT) FII_AR_RCT_UNAPP_AMT,
	     SUM(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
             SUM(FII_AR_RCT_UNEARNED_DCT) FII_AR_RCT_UNEARNED_DCT,
	     SUM(SUM(FII_AR_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
             SUM(SUM(FII_AR_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
	     SUM(SUM(FII_AR_RCT_UNAPP_AMT)) over() FII_AR_GT_RCT_UNAPP_AMT,
	     SUM(SUM(FII_AR_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
             SUM(SUM(FII_AR_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
	     FII_AR_RCT_NUM_DRILL,
             FII_AR_RCT_AMT_DRILL,
	     FII_AR_RCT_APP_AMT_DRILL
    FROM(
    SELECT /*+ no_merge leading(v) cardinality(v 1) */ NVL(acct.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM, --acct.account_number account_number,
       f.receipt_number    FII_AR_RCT_NUM,
       f.receipt_date     FII_AR_RCT_DATE,
       f.gl_date          FII_AR_RCT_GL_DATE,
       f.header_status  header_status,
       to_char(SUM(f.amount_applied_rct), ''999,999,999'')
                    || '' '' || f.currency_code FII_AR_RCT_AMT_RCURR,
       sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_AMT,
       sum(decode(f.application_status,
                    ''APP'',   f.amount_applied_rct' || l_currency || ',
                    0)) FII_AR_RCT_APP_AMT,
       sum(decode(f.application_status,
                    ''UNAPP'',   f.amount_applied_rct'|| l_currency || ',
                    ''UNID'',    f.amount_applied_rct'|| l_currency || ',
                     0)) FII_AR_RCT_UNAPP_AMT,
       sum(f.earned_discount_amount' || l_currency || ') FII_AR_RCT_EARNED_DCT,
       sum(f.unearned_discount_amount'||l_currency||') FII_AR_RCT_UNEARNED_DCT,
       sum(sum(f.amount_applied_rct' || l_currency || ')) over() FII_AR_GT_RCT_AMT,
       sum(sum(decode(f.application_status,
                    ''APP'',   f.amount_applied_rct' || l_currency || ',
                    0))) over() FII_AR_GT_RCT_APP_AMT,
       sum(sum(decode(f.application_status,
                    ''UNAPP'',   f.amount_applied_rct'|| l_currency || ',
                    ''UNID'',    f.amount_applied_rct'|| l_currency || ',
                     0))) over() FII_AR_GT_RCT_UNAPP_AMT,
       sum(sum(f.earned_discount_amount' || l_currency || ')) over()   FII_AR_GT_RCT_EARNED_DCT,
       sum(sum(f.unearned_discount_amount'||l_currency||')) over() FII_AR_GT_RCT_UNEARNED_DCT,
       decode(f.receipt_number, NULL, NULL, ''' ||

                   l_rct_num_url || ''') FII_AR_RCT_NUM_DRILL,

       decode(sum(f.amount_applied_rct' || l_currency || '), 0, NULL, NULL, NULL, ''' ||
                   l_rct_amt_url || ''')       FII_AR_RCT_AMT_DRILL,

       decode(sum(f.amount_applied_trx' || l_currency || '), 0, NULL, NULL, NULL,

                       ''' || l_rct_app_amt_url || ''')  FII_AR_RCT_APP_AMT_DRILL,
		       f.bill_to_customer_id, f.receipt_method_id
     FROM fii_ar_receipts_f        f, fii_cust_accounts acct,
	  '||l_from_table || '
            FII_AR_SUMMARY_GT v
     WHERE  f.org_id  = v.org_id
     AND f.bill_to_customer_id = acct.cust_account_id '
     || l_where_clause ||'
     GROUP BY NVL(acct.account_number,'''||l_unid_message||''') , f.receipt_number, f.receipt_date, f.gl_date, f.cash_receipt_id,
               f.currency_code, f.bill_to_customer_id, f.header_status, f.receipt_method_id) fact , ar_lookups  l,
	      ar_receipt_methods    m
	  WHERE fact.receipt_method_id   = m.receipt_method_id
	  AND l.lookup_type = ''CHECK_STATUS''
          AND l.lookup_code = fact.header_status    ' || l_unapp_end_select_sql ||'
	  GROUP BY 		FII_AR_RCT_ACCT_NUM,
				FII_AR_RCT_NUM,
				FII_AR_RCT_DATE,
				FII_AR_RCT_GL_DATE,
				m.name,
				l.lookup_code, l.meaning ,
				FII_AR_RCT_AMT_RCURR,
				FII_AR_RCT_NUM_DRILL,
				FII_AR_RCT_AMT_DRILL,
				FII_AR_RCT_APP_AMT_DRILL ' || l_order_by;

Line: 534


    'SELECT
       lv.meaning                  FII_AR_RCT_BALANCE,
       nvl(sum(v2.amount), 0)      FII_AR_RCT_AMT,
       decode(lv.lookup_code, ''APP'',
         decode(nvl(sum(v2.amount), 0),
                0, NULL,
                ''' || l_rct_amt_url || '''),
                NULL) FII_AR_RCT_AMT_DRILL
     FROM (
       SELECT /*+ leading(gt) cardinality(gt 1) */ f.application_status           status,
              sum(f.amount_applied_rct) amount,
	      f.cash_receipt_id,
	      :CUST_ACCOUNT_ID    cust_account_id
       FROM fii_ar_receipts_f f,  '||l_from_table || ' fii_ar_summary_gt gt
       WHERE  f.org_id         = gt.org_id
       AND   f.cash_receipt_id = :CASH_RECEIPT_ID
       AND   f.currency_code  = '''|| l_rct_curr || '''
       AND   (f.applied_payment_schedule_id > 0 OR f.applied_payment_schedule_id is NULL)
       ' || l_where_clause || '
       GROUP BY f.application_status,
	      f.cash_receipt_id,
	      :CUST_ACCOUNT_ID
       UNION ALL
       SELECT  /*+ leading(gt) cardinality(gt 1) */ decode(applied_payment_schedule_id,
                     -2, ''OTHER'', -3, ''OTHER'', -5, ''OTHER'',
                     -6, ''OTHER'', -8, ''OTHER'', -9, ''OTHER'',
                     -1, ''ONACC'',
                     -4, ''CASH'',
                     -7, ''PREPAY'') status,
              sum(f.amount_applied_rct) amount,
	      f.cash_receipt_id,
	      :CUST_ACCOUNT_ID    cust_account_id
       FROM fii_ar_receipts_f f,  '||l_from_table || ' fii_ar_summary_gt gt
       WHERE  f.org_id         = gt.org_id
       AND   f.cash_receipt_id = :CASH_RECEIPT_ID
       AND   f.currency_code  = '''|| l_rct_curr || '''
       AND   f.applied_payment_schedule_id < 0
       ' || l_where_clause || '
       GROUP BY decode(applied_payment_schedule_id,
                     -2, ''OTHER'', -3, ''OTHER'', -5, ''OTHER'',
                     -6, ''OTHER'', -8, ''OTHER'', -9, ''OTHER'',
                     -1, ''ONACC'',
                     -4, ''CASH'',
                     -7, ''PREPAY''),
	      f.cash_receipt_id,
	      :CUST_ACCOUNT_ID
       ) v2,
       fnd_lookup_values lv
     WHERE lv.lookup_type       = ''FII_AR_RCT_BAL_DETAIL_TYPE''
     AND   lv.view_application_id = 450
     AND   lv.language = userenv(''LANG'')
     AND   v2.status (+)= lv.lookup_code
     GROUP BY lv.meaning, lv.lookup_code, cash_receipt_id,
	      cust_account_id
     ORDER BY decode(lv.lookup_code,
                ''UNID'',  1, ''APP'',  2, ''ONACC'',  3,
                ''UNAPP'', 4, ''CASH'', 5, ''PREPAY'', 6,
                ''OTHER'', 7 )';