1 PACKAGE BODY FII_AR_REC_ACTIVITY_TREND_PKG AS
2 /* $Header: FIIARDBIRTB.pls 120.5.12000000.2 2007/04/09 20:20:42 vkazhipu ship $ */
3
4 PROCEDURE get_rec_activity_trend (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 open_rec_sql OUT NOCOPY VARCHAR2,
7 open_rec_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8 IS
9
10 l_viewby_id VARCHAR2(50);
11 l_viewby VARCHAR2(250);
12 l_party_where VARCHAR2(250);
13 l_parent_party_where VARCHAR2(250);
14 l_collector_where VARCHAR2(250);
15 l_industry_where VARCHAR2(500);
16 l_cust_acct_where VARCHAR2(250);
17 l_cust_self_drill VARCHAR2(500);
18 l_past_due_rec_drill VARCHAR2(500);
19 l_open_rec_drill VARCHAR2(500);
20 l_select VARCHAR2(15000);
21 l_col_select VARCHAR2(1000);
22 l_prior_column VARCHAR2(500);
23 l_group_by VARCHAR2(100) := NULL;
24 l_order_by VARCHAR2(250);
25 l_order_column VARCHAR2(250);
26 l_select_curr_end_prd VARCHAR2(5000);
27 l_end_date VARCHAR2(50);
28 l_start_date VARCHAR2(50);
29 l_per_from VARCHAR2(100);
30
31 BEGIN
32
33 fii_ar_util_pkg.reset_globals;
34 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
35
36 /* The call populates the AR global temp table. */
37 fii_ar_util_pkg.populate_summary_gt_tables;
38
39 /* Defining the where clause for PARTY_ID*/
40 IF fii_ar_util_pkg.g_party_id <> '-111' THEN
41 l_party_where := ' AND f.party_id = time.party_id ';
42 END IF;
43
44 /* Defining the where clause for COLLECTOR_ID*/
45 IF fii_ar_util_pkg.g_collector_id <> '-111' THEN
46 l_collector_where := 'AND f.collector_id = time.collector_id ';
47 END IF;
48
49 /* Defining industry where clause for specific industry */
50 IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
51 l_industry_where := ' AND time.class_code = f.class_code AND time.class_category = f.class_category';
52 END IF;
53
54 /* Defining the table to be used based on Period Type chosen.*/
55 CASE fii_ar_util_pkg.g_page_period_type
56 WHEN 'FII_TIME_WEEK' THEN
57 l_per_from:=' fii_time_week ';
58 WHEN 'FII_TIME_ENT_PERIOD' THEN
59 l_per_from:=' fii_time_ent_period ';
60 WHEN 'FII_TIME_ENT_QTR' THEN
61 l_per_from:=' fii_time_ent_qtr ';
62 WHEN 'FII_TIME_ENT_YEAR' THEN
63 l_per_from:=' fii_time_ent_year ';
64 END CASE;
65
66 /* The select statement checks wethere asofdate chosen is end of current week/month/qtr/year.
67 If True then no need to add a new select ELSE need to add a select statement to get amount
68 upto asofdate chosen for the current period. */
69
70 IF fii_ar_util_pkg.g_as_of_date = fii_ar_util_pkg.g_curr_per_end THEN
71 l_select_curr_end_prd :=' ';
72 l_end_date := ' :ASOF_DATE ';
73
74 ELSE
75
76 l_select_curr_end_prd := ' UNION ALL
77 /* The select statment will return data for current week/month/qtr/year upto asofdate,
78 if asofdate<> last day of period*/
79 SELECT
80 per.sequence sequence,
81 CASE WHEN (f.header_filter_date >= per.start_date
82 AND f.header_filter_date <= :ASOF_DATE) THEN
83 sum(f.total_receipt_amount) ELSE NULL END FII_AR_REC_AMT,
84 CASE WHEN (f.header_filter_date >= per.start_date
85 AND f.header_filter_date <= :ASOF_DATE) THEN
86 sum(f.total_receipt_count) ELSE NULL END FII_AR_REC_COUNT,
87 sum(f.app_amount) FII_AR_REC_APP_AMT,
88 sum(f.app_count) FII_AR_REC_APP_COUNT,
89 NULL FII_AR_PRIOR_REC_AMT
90 FROM '||l_per_from||' per,
91 FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
92 ( SELECT * FROM fii_time_structures cal, '||fii_ar_util_pkg.get_from_statement||' gt
93 WHERE report_date = :ASOF_DATE
94 AND bitand(cal.record_type_id, :BITAND) = :BITAND
95 AND '||fii_ar_util_pkg.get_where_statement||') time
96 WHERE f.time_id = time.time_id
97 AND f.period_type_id = time.period_type_id
98 AND f.org_id = time.org_id
99 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||l_collector_where||l_industry_where||'
100 AND per.end_date = :CURR_PERIOD_END
101 GROUP BY report_date, per.sequence, f.header_filter_date, per.start_date
102 ';
103
104 l_end_date := ' :CURR_PERIOD_START ';
105
106 END IF;
107
108 /* This condition handles, wethere parameter compare to chosen is Prior Period and show Prior Data or not. */
109
110 IF fii_ar_util_pkg.g_time_comp = 'SEQUENTIAL' THEN
111 l_prior_column:= ' NULL FII_AR_PRIOR_REC_AMT ';
112 l_start_date := ' :SD_PRIOR ';
113 ELSE
114 IF fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
115 l_prior_column:= ' NULL FII_AR_PRIOR_REC_AMT ';
116 l_start_date := ' :SD_PRIOR ';
117 ELSE
118 l_prior_column:= ' CASE WHEN time.report_date < :SD_SDATE AND f.header_filter_date >= MIN(per.start_date) THEN sum(f.total_receipt_amount) ELSE NULL END FII_AR_PRIOR_REC_AMT ';
119 l_start_date := ' :SD_PRIOR_PRIOR ';
120 END IF;
121 END IF;
122
123
124 IF fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
125
126 l_col_select := ' sum(f.total_receipt_amount) FII_AR_REC_AMT,
127 sum(f.total_receipt_count) FII_AR_REC_COUNT,
128 sum(f.app_amount) FII_AR_REC_APP_AMT,
129 sum(f.app_count) FII_AR_REC_APP_COUNT, ';
130
131 ELSE
132
133 l_col_select := ' CASE WHEN time.report_date >= :SD_SDATE
134 AND (f.header_filter_date >= MIN(per.start_date)
135 AND f.header_filter_date <= time.report_date) THEN
136 sum(f.total_receipt_amount) ELSE NULL END FII_AR_REC_AMT,
137 CASE WHEN time.report_date >= :SD_SDATE
138 AND (f.header_filter_date >= MIN(per.start_date)
139 AND f.header_filter_date <= time.report_date) THEN
140 sum(f.total_receipt_count) ELSE NULL END FII_AR_REC_COUNT,
141 CASE WHEN time.report_date >= :SD_SDATE THEN
142 sum(f.app_amount) ELSE NULL END FII_AR_REC_APP_AMT,
143 CASE WHEN time.report_date >= :SD_SDATE THEN
144 sum(f.app_count) ELSE NULL END FII_AR_REC_APP_COUNT, ';
145 END IF;
146
147 /* Final Select statement */
148
149 l_select :=
150 '
151 SELECT cy_per.name VIEWBY,
152 cy_per.name FII_AR_VIEWBY,
153 to_char(cy_per.end_date,''DD/MM/YYYY'') FII_AR_PERIOD_END_DATE,
154 SUM(FII_AR_REC_AMT) FII_AR_REC_AMT,
155 SUM(FII_AR_REC_COUNT) FII_AR_REC_COUNT,
156 SUM(FII_AR_REC_APP_AMT) FII_AR_REC_APP_AMT,
157 SUM(FII_AR_REC_APP_COUNT) FII_AR_REC_APP_COUNT,
158 SUM(FII_AR_PRIOR_REC_AMT) FII_AR_PRIOR_REC_AMT,
159 SUM(FII_AR_REC_AMT) FII_AR_REC_AMT_G,
160 DECODE(SUM(FII_AR_REC_AMT),0,NULL,NULL,NULL,DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
161 ''&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
162 ''AS_OF_DATE=FII_AR_PERIOD_END_DATE&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'')) FII_AR_REC_AMT_DRILL
163 FROM
164 '||l_per_from||' cy_per,
165 (SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
166 per.sequence sequence,
167 '||l_col_select||l_prior_column||'
168 FROM '||l_per_from||' per,
169 FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
170 ( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ * FROM fii_time_structures cal, '||fii_ar_util_pkg.get_from_statement||' gt
171 WHERE report_date in
172 (SELECT end_date from '||l_per_from||' cy_per WHERE cy_per.start_date < '||l_end_date||'
173 AND cy_per.start_date >= '||l_start_date||' )
174 AND bitand(cal.record_type_id, :BITAND) = :BITAND
175 AND '||fii_ar_util_pkg.get_where_statement||') time
176 WHERE f.time_id = time.time_id
177 AND f.period_type_id = time.period_type_id
178 AND f.org_id = time.org_id
179 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||l_collector_where||l_industry_where||'
180 AND per.end_date = time.report_date
181 GROUP BY report_date, per.sequence, f.header_filter_date
182 '||l_select_curr_end_prd||'
183 ) inline_view
184 WHERE cy_per.start_date <= :ASOF_DATE
185 AND cy_per.start_date > :SD_PRIOR
186 AND cy_per.sequence = inline_view.sequence (+)
187 GROUP BY cy_per.name,cy_per.end_date,cy_per.start_date
188 ORDER BY cy_per.start_date
189 ';
190
191
192 fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, open_rec_sql, open_rec_output);
193
194
195 END get_rec_activity_trend;
196
197 /* This function returns the label for the First column in the report, based on period chosen. */
198 FUNCTION get_label RETURN VARCHAR2 IS
199 stmt VARCHAR2(240);
200 BEGIN
201
202 CASE fii_ar_util_pkg.g_page_period_type
203 WHEN 'FII_TIME_WEEK' THEN
204 stmt:= FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_WEEK');
205 WHEN 'FII_TIME_ENT_PERIOD' THEN
206 stmt:= FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_MONTH');
207 WHEN 'FII_TIME_ENT_QTR' THEN
208 stmt:= FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_QUARTER');
209 WHEN 'FII_TIME_ENT_YEAR' THEN
210 stmt:= FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_YEAR');
211 END CASE;
212
213 Return stmt;
214
215 END get_label;
216
217 END FII_AR_REC_ACTIVITY_TREND_PKG;
218
[Home] [Help]