[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT inner_view.viewby VIEWBY
,viewby_id VIEWBYID
,FII_AR_DISCOUNT_AMT
,(FII_AR_DISCOUNT_AMT - (FII_AR_PRIOR_UNEARNED_DISC_AMT + FII_AR_PRIOR_EARNED_DISC_AMT))*100
/NULLIF((FII_AR_PRIOR_UNEARNED_DISC_AMT + FII_AR_PRIOR_EARNED_DISC_AMT),0)
FII_AR_DISCOUNT_CHANGE
,FII_AR_APP_REC_PERCENT
,FII_AR_EARNED_DISC_PERCENT
,FII_AR_EARNED_DISC_AMT
,(FII_AR_EARNED_DISC_AMT - FII_AR_PRIOR_EARNED_DISC_AMT)*100
/NULLIF(FII_AR_PRIOR_EARNED_DISC_AMT,0) FII_AR_EARNED_DISC_CHANGE
,FII_AR_UNEARNED_DISC_PERCENT
,FII_AR_UNEARNED_DISC_AMT
,(FII_AR_UNEARNED_DISC_AMT - FII_AR_PRIOR_UNEARNED_DISC_AMT)*100
/NULLIF(FII_AR_PRIOR_UNEARNED_DISC_AMT,0) FII_AR_UNEARNED_DISC_CHANGE
,FII_AR_APP_REC_AMT
,FII_AR_WTD_DAYS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) FII_AR_DAYS_PAID
,FII_AR_WTD_TERMS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) FII_AR_TERMS_PAID
,SUM (FII_AR_DISCOUNT_AMT) OVER () FII_AR_GT_DISCOUNT_AMT
,(SUM(FII_AR_DISCOUNT_AMT) OVER () - (SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER ()
+ SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER ()))*100
/NULLIF((SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER ()),0)
FII_AR_GT_DISCOUNT_CHANGE
,(SUM(FII_AR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_EARNED_DISC_AMT) OVER ())*100
/NULLIF(SUM(FII_AR_APP_REC_AMT) OVER (),0) FII_AR_GT_APP_REC_PERCENT
,SUM(FII_AR_EARNED_DISC_AMT) OVER ()*100
/NULLIF((SUM(FII_AR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_EARNED_DISC_AMT) OVER ()),0)
FII_AR_GT_EARNED_DISC_PERCENT
,SUM(FII_AR_EARNED_DISC_AMT) OVER () FII_AR_GT_EARNED_DISC_AMT
,(SUM(FII_AR_EARNED_DISC_AMT) OVER () - SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER ())*100
/NULLIF(SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER (),0) FII_AR_GT_EARNED_DISC_CHANGE
,SUM(FII_AR_UNEARNED_DISC_AMT) OVER ()*100
/NULLIF((SUM(FII_AR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_EARNED_DISC_AMT) OVER ()),0)
FII_AR_GT_UNEARN_DISC_PERCENT
,SUM(FII_AR_UNEARNED_DISC_AMT) OVER () FII_AR_GT_UNEARN_DISC_AMT
,(SUM(FII_AR_UNEARNED_DISC_AMT) OVER () - SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER ())*100
/NULLIF(SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER (),0) FII_AR_GT_UNEARN_DISC_CHANGE
,SUM(FII_AR_APP_REC_AMT) OVER () FII_AR_GT_APP_REC_AMT
,SUM(FII_AR_WTD_DAYS_PAID) OVER ()/NULLIF(SUM(FII_AR_APP_REC_AMT) OVER (),0) FII_AR_GT_DAYS_PAID
,SUM(FII_AR_WTD_TERMS_PAID) OVER ()/NULLIF(SUM(FII_AR_APP_REC_AMT) OVER (),0) FII_AR_GT_TERMS_PAID
,FII_AR_PRIOR_UNEARNED_DISC_AMT
,FII_AR_PRIOR_EARNED_DISC_AMT
,FII_AR_PRIOR_APP_REC_PERCENT
,CASE WHEN FII_AR_DISCOUNT_AMT = 0 OR FII_AR_DISCOUNT_AMT IS NULL THEN NULL
ELSE '||l_discount_amt_final_drill||'
END FII_AR_DISCOUNT_AMT_DRILL
,CASE WHEN FII_AR_APP_REC_AMT = 0 OR FII_AR_APP_REC_AMT IS NULL THEN NULL
ELSE '||l_app_rec_amt_final_drill||'
END FII_AR_APP_REC_AMT_DRILL
,CASE WHEN FII_AR_WTD_DAYS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) = 0
OR FII_AR_WTD_DAYS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) IS NULL THEN NULL
ELSE '''||l_days_paid_drill||'''
END FII_AR_DAYS_PAID_DRILL
,'||l_viewby_drill||' FII_AR_VIEW_BY_DRILL
FROM
(SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY
,time.viewby_code viewby_id
'||l_inner_cust_columns||'
,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
ELSE NULL END) + SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount
ELSE NULL END) FII_AR_DISCOUNT_AMT
,SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE
THEN unearned_discount_amount ELSE NULL END) FII_AR_PRIOR_UNEARNED_DISC_AMT
,SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN earned_discount_amount
ELSE NULL END) FII_AR_PRIOR_EARNED_DISC_AMT
,(SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
+ SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount ELSE NULL END))*100
/NULLIF(SUM(CASE WHEN report_date = :ASOF_DATE THEN app_amount ELSE NULL END),0)
FII_AR_APP_REC_PERCENT
,SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount ELSE NULL END)*100
/NULLIF((SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
ELSE NULL END) + SUM(CASE WHEN report_date = :ASOF_DATE
THEN earned_discount_amount ELSE NULL END)),0)
FII_AR_EARNED_DISC_PERCENT
,SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount
ELSE NULL END) FII_AR_EARNED_DISC_AMT
,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount ELSE NULL END)*100
/NULLIF((SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
+ SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount ELSE NULL END)),0)
FII_AR_UNEARNED_DISC_PERCENT
,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
ELSE NULL END) FII_AR_UNEARNED_DISC_AMT
,SUM(CASE WHEN report_date = :ASOF_DATE THEN app_amount ELSE NULL END) FII_AR_APP_REC_AMT
,SUM(CASE WHEN report_date = :ASOF_DATE THEN wtd_days_paid_num
ELSE NULL END) FII_AR_WTD_DAYS_PAID
,SUM(CASE WHEN report_date = :ASOF_DATE THEN wtd_terms_paid_num
ELSE NULL END) FII_AR_WTD_TERMS_PAID
,(SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
+ SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN earned_discount_amount ELSE NULL END))*100
/NULLIF(SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN app_amount ELSE NULL END),0)
FII_AR_PRIOR_APP_REC_PERCENT
FROM fii_ar_net_rec'||fii_ar_util_pkg.g_cust_suffix||'_mv'||fii_ar_util_pkg.g_curr_suffix||' f
,(SELECT /*+ no_merge '||l_gt_hint|| ' */ cal.time_id time_id
,cal.period_type_id period_type_id
,cal.report_date report_date
,gt.* -- Picking all the columns from Security table -- parent_party_id,party_id,org_id,
-- collector_id, is_leaf_flag,class_code,class_category,viewby, viewby_code
FROM fii_time_structures cal
,'||fii_ar_util_pkg.get_from_statement||' gt -- Security table
WHERE report_date IN (:ASOF_DATE,:PREVIOUS_ASOF_DATE)
AND BITAND(cal.record_type_id, :BITAND) = :BITAND -- Bitand value changes with PeriodType
AND '||fii_ar_util_pkg.get_where_statement||'
) time
WHERE f.time_id = time.time_id
AND f.period_type_id = time.period_type_id
AND f.org_id = time.org_id
AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_child_party_where||'
'||l_cust_acct_where||'
'||l_party_where||'
'||l_industry_where||'
GROUP BY time.viewby_code '||l_inner_cust_columns||', VIEWBY
) inner_view
'||l_order_by;