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_DISCOUNT_SUMMARY_PKG dependencies on FII_AR_UTIL_PKG

Line 41: fii_ar_util_pkg.reset_globals;

37: l_order_column VARCHAR2(500);
38: l_gt_hint varchar2(500);
39: BEGIN
40: -- Call to reset the parameter variables
41: fii_ar_util_pkg.reset_globals;
42:
43: -- Call to get all the parameters in the report
44: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
45: l_gt_hint := ' leading(gt) cardinality(gt 1) ';

Line 44: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

40: -- Call to reset the parameter variables
41: fii_ar_util_pkg.reset_globals;
42:
43: -- Call to get all the parameters in the report
44: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
45: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
46: -- Frame the order by clause for the report sql
47: IF(INSTR(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
48:

Line 47: IF(INSTR(fii_ar_util_pkg.g_order_by,',') <> 0) THEN

43: -- Call to get all the parameters in the report
44: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
45: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
46: -- Frame the order by clause for the report sql
47: IF(INSTR(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
48:
49: -- Above means that more than one column is sortable on the report
50: -- So, sort on the default column, FII_AR_DISCOUNT_AMT in descending order
51: -- NVL is added to make sure the null values appear last

Line 55: ELSIF(INSTR(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN

51: -- NVL is added to make sure the null values appear last
52:
53: l_order_by := 'ORDER BY NVL(FII_AR_DISCOUNT_AMT, -999999999) DESC';
54:
55: ELSIF(INSTR(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
56:
57: -- Above means that a particular sort column is clicked to have descending order
58: -- Here, all the null values should appear last on the report
59:

Line 60: l_order_column := SUBSTR(fii_ar_util_pkg.g_order_by,1,INSTR(fii_ar_util_pkg.g_order_by, ' DESC'));

56:
57: -- Above means that a particular sort column is clicked to have descending order
58: -- Here, all the null values should appear last on the report
59:
60: l_order_column := SUBSTR(fii_ar_util_pkg.g_order_by,1,INSTR(fii_ar_util_pkg.g_order_by, ' DESC'));
61: l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
62:
63: ELSE
64:

Line 74: fii_ar_util_pkg.populate_summary_gt_tables;

70: END IF;
71:
72: -- Call to populate fii_ar_summary_gt table
73:
74: fii_ar_util_pkg.populate_summary_gt_tables;
75:
76: -- Assigning VIEWBY drill to NULL
77:
78: l_viewby_drill := '''''';

Line 81: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR

77:
78: l_viewby_drill := '''''';
79:
80: -- Defining industry where clause for specific industry or when viewby is Industry
81: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
82: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
83:
84: l_industry_where := ' AND time.class_code = f.class_code AND time.class_category = f.class_category';
85: END IF;

Line 82: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN

78: l_viewby_drill := '''''';
79:
80: -- Defining industry where clause for specific industry or when viewby is Industry
81: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
82: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
83:
84: l_industry_where := ' AND time.class_code = f.class_code AND time.class_category = f.class_category';
85: END IF;
86:

Line 88: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

84: l_industry_where := ' AND time.class_code = f.class_code AND time.class_category = f.class_category';
85: END IF;
86:
87: -- Customer Dimension where clause
88: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
89: l_child_party_where := ' AND f.party_id = time.party_id ';
90: END IF;
91:
92: -- Defining drills for Discount Amount, Applied Receipt Amount and Days Paid column shown on the report

Line 101: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

97: l_app_rec_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
98: l_days_paid_drill := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
99:
100: -- Done for drill to detailed reports
101: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
102: l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
103: l_discount_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
104: l_app_rec_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
105: END IF;

Line 108: IF((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')

104: l_app_rec_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
105: END IF;
106:
107: -- Select, where, group by clauses based on viewby
108: IF((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
109: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
110: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
111: l_discount_amt_drill_1 := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
112: l_app_rec_amt_drill_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';

Line 109: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN

105: END IF;
106:
107: -- Select, where, group by clauses based on viewby
108: IF((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
109: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
110: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
111: l_discount_amt_drill_1 := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
112: l_app_rec_amt_drill_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
113: l_days_paid_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';

Line 110: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN

106:
107: -- Select, where, group by clauses based on viewby
108: IF((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
109: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
110: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
111: l_discount_amt_drill_1 := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
112: l_app_rec_amt_drill_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
113: l_days_paid_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
114: ELSE

Line 123: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN

119:
120: l_discount_amt_final_drill := ''''||l_discount_amt_drill_1||'''';
121: l_app_rec_amt_final_drill := ''''||l_app_rec_amt_drill_1||'''';
122: l_days_paid_drill := l_days_paid_drill_1;
123: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
124: l_discount_amt_final_drill := ''''||l_discount_amt_detail_drill||'''';
125:
126: l_app_rec_amt_final_drill := ''''||l_app_rec_amt_detail_drill||'''';
127:

Line 133: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

129:
130: -- WHERE clause for Customer Account
131: l_cust_acct_where := 'AND f.cust_account_id = time.cust_account_id';
132:
133: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
134: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
135: l_discount_amt_final_drill :=
136: 'CASE WHEN is_self_flag = ''Y'' OR is_leaf_flag = ''Y''
137: THEN '''||l_discount_amt_detail_drill||'''

Line 134: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN

130: -- WHERE clause for Customer Account
131: l_cust_acct_where := 'AND f.cust_account_id = time.cust_account_id';
132:
133: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
134: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
135: l_discount_amt_final_drill :=
136: 'CASE WHEN is_self_flag = ''Y'' OR is_leaf_flag = ''Y''
137: THEN '''||l_discount_amt_detail_drill||'''
138: ELSE '''||l_discount_amt_drill||'''

Line 170: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

166: END IF;
167:
168: -- Included party_id which is passed on the DRILL to detailed reports
169:
170: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
171: l_inner_cust_columns := l_inner_cust_columns || ' ,time.party_id';
172: END IF;
173:
174: -- PMV SQL to display data on Discount Summary Report

Line 232: (SELECT /+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/ VIEWBY

228: ELSE '''||l_days_paid_drill||'''
229: END FII_AR_DAYS_PAID_DRILL
230: ,'||l_viewby_drill||' FII_AR_VIEW_BY_DRILL
231: FROM
232: (SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY
233: ,time.viewby_code viewby_id
234: '||l_inner_cust_columns||'
235: ,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
236: ELSE NULL END) + SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount

Line 268: FROM fii_ar_net_rec'||fii_ar_util_pkg.g_cust_suffix||'_mv'||fii_ar_util_pkg.g_curr_suffix||' f

264: ,(SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
265: + SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN earned_discount_amount ELSE NULL END))*100
266: /NULLIF(SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN app_amount ELSE NULL END),0)
267: FII_AR_PRIOR_APP_REC_PERCENT
268: FROM fii_ar_net_rec'||fii_ar_util_pkg.g_cust_suffix||'_mv'||fii_ar_util_pkg.g_curr_suffix||' f
269: ,(SELECT /*+ no_merge '||l_gt_hint|| ' */ cal.time_id time_id
270: ,cal.period_type_id period_type_id
271: ,cal.report_date report_date
272: ,gt.* -- Picking all the columns from Security table -- parent_party_id,party_id,org_id,

Line 275: ,'||fii_ar_util_pkg.get_from_statement||' gt -- Security table

271: ,cal.report_date report_date
272: ,gt.* -- Picking all the columns from Security table -- parent_party_id,party_id,org_id,
273: -- collector_id, is_leaf_flag,class_code,class_category,viewby, viewby_code
274: FROM fii_time_structures cal
275: ,'||fii_ar_util_pkg.get_from_statement||' gt -- Security table
276: WHERE report_date IN (:ASOF_DATE,:PREVIOUS_ASOF_DATE)
277: AND BITAND(cal.record_type_id, :BITAND) = :BITAND -- Bitand value changes with PeriodType
278: AND '||fii_ar_util_pkg.get_where_statement||'
279: ) time

Line 278: AND '||fii_ar_util_pkg.get_where_statement||'

274: FROM fii_time_structures cal
275: ,'||fii_ar_util_pkg.get_from_statement||' gt -- Security table
276: WHERE report_date IN (:ASOF_DATE,:PREVIOUS_ASOF_DATE)
277: AND BITAND(cal.record_type_id, :BITAND) = :BITAND -- Bitand value changes with PeriodType
278: AND '||fii_ar_util_pkg.get_where_statement||'
279: ) time
280: WHERE f.time_id = time.time_id
281: AND f.period_type_id = time.period_type_id
282: AND f.org_id = time.org_id

Line 283: AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_child_party_where||'

279: ) time
280: WHERE f.time_id = time.time_id
281: AND f.period_type_id = time.period_type_id
282: AND f.org_id = time.org_id
283: AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_child_party_where||'
284: '||l_cust_acct_where||'
285: '||l_party_where||'
286: '||l_industry_where||'
287: GROUP BY time.viewby_code '||l_inner_cust_columns||', VIEWBY

Line 293: fii_ar_util_pkg.bind_variable(l_sqlstmt

289: '||l_order_by;
290:
291: -- Call to UTIL package to bind the variables
292:
293: fii_ar_util_pkg.bind_variable(l_sqlstmt
294: ,p_page_parameter_tbl
295: ,p_discount_summary_sql
296: ,p_discount_summary_output
297: );