1 PACKAGE BODY fii_ar_unapp_rct_trend_pkg AS
2 /* $Header: FIIARDBIURTB.pls 120.4.12000000.2 2007/04/09 20:16:44 vkazhipu ship $ */
3
4 -- This package will provide SQL statements to retrieve data for Unapplied Receipts Trend report
5
6 PROCEDURE get_unapp_rct_trend ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 ,p_unapp_rct_trend_sql OUT NOCOPY VARCHAR2
8 ,p_unapp_rct_trend_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 ) IS
10
11 l_sqlstmt VARCHAR2(30000);
12
13 -- Variables for where clauses
14 l_child_party_where VARCHAR2(300);
15 l_collector_where VARCHAR2(300);
16
17 -- Variables related to TIME table
18 l_curr_per_sequence NUMBER;
19 l_curr_end_date DATE;
20
21 -- Variables used for forming PMV SQL when report date <> end date of the month
22 l_curr_period_unapp_rec_sql VARCHAR2(10000);
23 l_curr_period_total_rec_sql VARCHAR2(10000);
24
25
26 BEGIN
27 -- Call to reset the parameter variables
28 fii_ar_util_pkg.reset_globals;
29
30 -- Call to get all the parameters in the report
31 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
32
33 -- Call to populate fii_ar_summary_gt table
34 fii_ar_util_pkg.populate_summary_gt_tables;
35
36 -- Customer Dimension WHERE clause based on the report parameter
37 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
38 l_child_party_where := ' AND f.party_id = inner_time.party_id ';
39 END IF;
40
41 -- Collector Dimension WHERE clause based on the report parameter
42
43 IF (fii_ar_util_pkg.g_collector_id <> '-111') THEN
44 l_collector_where := 'AND f.collector_id = inner_time.collector_id';
45 END IF;
46
47 -- Getting the sequence and the last day of the month corresponding to the REPORT DATE
48 -- Putting NVL to ensure that SELECT clause doesn't fail when AsOfDate is not available in TIME table
49
50 SELECT NVL(MAX(sequence),0),NVL(MAX(end_date),SYSDATE)
51 INTO l_curr_per_sequence,l_curr_end_date
52 FROM fii_time_ent_period
53 WHERE fii_ar_util_pkg.g_as_of_date BETWEEN start_date AND end_date;
54
55 -- Framing SQL statement for the current month for as of date <> end date of the month
56
57 IF fii_ar_util_pkg.g_as_of_date <> l_curr_end_date THEN
58
59 -- Following variable stores the SQL for Unapplied Receipt Amount
60
61 l_curr_period_unapp_rec_sql :=
62 ' UNION ALL
63 SELECT /*+ INDEX(f FII_AR_RCT_AGING_BASE_MV_N1)*/ '||l_curr_per_sequence||' period_sequence
64 ,f.total_unapplied_amount FII_AR_UNAPP_REC_AMT
65 ,f.total_unapplied_count FII_AR_UNAPP_REC_COUNT
66 ,NULL FII_AR_PRIOR_UNAPP_REC_AMT
67 ,NULL FII_AR_PRIOR_UNAPP_REC_COUNT
68 ,NULL FII_AR_TOTAL_REC_AMT
69 ,NULL FII_AR_TOTAL_REC_COUNT
70 FROM fii_ar_rct_aging_base_mv'||fii_ar_util_pkg.g_curr_suffix||' f
71 ,(SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ cal.time_id time_id
72 ,cal.period_type_id period_type_id
73 ,gt.*
74 FROM fii_time_structures cal
75 ,fii_ar_summary_gt gt
76 WHERE cal.report_date = :ASOF_DATE
77 AND BITAND(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
78 ) inner_time
79 WHERE inner_time.time_id = f.time_id
80 AND inner_time.period_type_id = f.period_type_id
81 AND f.org_id = inner_time.org_id
82 AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '||l_child_party_where||' '||l_collector_where;
83
84 -- Following variable stores the SQL for Total Receipt Amount
85
86 l_curr_period_total_rec_sql :=
87 ' UNION ALL
88 SELECT /*+ INDEX(f FII_AR_NET_REC_BASE_MV_N1)*/ '||l_curr_per_sequence||' period_sequence
89 ,NULL FII_AR_UNAPP_REC_AMT
90 ,NULL FII_AR_UNAPP_REC_COUNT
91 ,NULL FII_AR_PRIOR_UNAPP_REC_AMT
92 ,NULL FII_AR_PRIOR_UNAPP_REC_COUNT
93 ,CASE WHEN f.header_filter_date > LAST_DAY(ADD_MONTHS(:ASOF_DATE,-1))
94 THEN f.total_receipt_amount
95 ELSE NULL
96 END FII_AR_TOTAL_REC_AMT
97 ,CASE WHEN f.header_filter_date > LAST_DAY(ADD_MONTHS(:ASOF_DATE,-1))
98 THEN f.total_receipt_count
99 ELSE NULL
100 END FII_AR_TOTAL_REC_COUNT
101 FROM fii_ar_net_rec_base_mv'||fii_ar_util_pkg.g_curr_suffix||' f
102 ,(SELECT /*+no_merge leading(gt) cardinality(gt 1)*/ cal.time_id time_id
103 ,cal.period_type_id period_type_id
104 ,gt.*
105 FROM fii_time_structures cal
106 ,fii_ar_summary_gt gt
107 WHERE cal.report_date = :ASOF_DATE
108 AND BITAND(cal.record_type_id, :BITAND) = :BITAND
109 ) inner_time
110 WHERE inner_time.time_id = f.time_id
111 AND inner_time.period_type_id = f.period_type_id
112 AND f.org_id = inner_time.org_id
113 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_child_party_where||' '||l_collector_where;
114
115 END IF;
116
117 -- PMV SQL to display data on Unapplied Receipts Trend Report
118
119 l_sqlstmt :=
120 'SELECT time.name VIEWBY
121 ,TO_CHAR(time.end_date,''DD/MM/YYYY'') FII_AR_MONTH_END_DATE
122 ,FII_AR_UNAPP_REC_AMT
123 ,FII_AR_UNAPP_REC_COUNT
124 ,FII_AR_TOTAL_REC_AMT
125 ,FII_AR_TOTAL_REC_COUNT
126 -- Drill on Unapplied Receipts Amount column should go to Unapplied Receipts Summary report,VIEWBY = OU
127 ,CASE WHEN FII_AR_UNAPP_REC_AMT = 0 OR FII_AR_UNAPP_REC_AMT IS NULL THEN NULL
128 WHEN time.end_date < :ASOF_DATE
129 THEN ''AS_OF_DATE=FII_AR_MONTH_END_DATE&pFunctionName=FII_AR_UNAPP_RCT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y''
130 ELSE ''pFunctionName=FII_AR_UNAPP_RCT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y''
131 END FII_AR_UNAPP_REC_AMT_DRILL
132 -- Drill on Total Receipts Amount column should go to Receipts Activity report,VIEWBY = OU
133 ,CASE WHEN FII_AR_TOTAL_REC_AMT = 0 OR FII_AR_TOTAL_REC_AMT IS NULL THEN NULL
134 WHEN time.end_date < :ASOF_DATE
135 THEN ''AS_OF_DATE=FII_AR_MONTH_END_DATE&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y''
136 ELSE ''pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y''
137 END FII_AR_TOTAL_REC_AMT_DRILL
138 ,FII_AR_PRIOR_UNAPP_REC_AMT
139 ,FII_AR_PRIOR_UNAPP_REC_COUNT
140 FROM
141 ( SELECT inner_inline_view.period_sequence period_sequence
142 ,SUM(FII_AR_UNAPP_REC_AMT) FII_AR_UNAPP_REC_AMT
143 ,SUM(FII_AR_UNAPP_REC_COUNT) FII_AR_UNAPP_REC_COUNT
144 ,SUM(FII_AR_TOTAL_REC_AMT) FII_AR_TOTAL_REC_AMT
145 ,SUM(FII_AR_TOTAL_REC_COUNT) FII_AR_TOTAL_REC_COUNT
146 ,NULL FII_AR_UNAPP_REC_AMT_DRILL
147 ,NULL FII_AR_TOTAL_REC_AMT_DRILL
148 ,SUM(FII_AR_PRIOR_UNAPP_REC_AMT) FII_AR_PRIOR_UNAPP_REC_AMT
149 ,SUM(FII_AR_PRIOR_UNAPP_REC_COUNT) FII_AR_PRIOR_UNAPP_REC_COUNT
150 FROM
151 (SELECT /*+ INDEX(f FII_AR_RCT_AGING_BASE_mv_N1)*/ time.sequence period_sequence
152 ,CASE WHEN inner_time.report_date >= :SD_SDATE
153 THEN f.total_unapplied_amount
154 ELSE NULL
155 END FII_AR_UNAPP_REC_AMT
156 ,CASE WHEN inner_time.report_date >= :SD_SDATE
157 THEN f.total_unapplied_count
158 ELSE NULL
159 END FII_AR_UNAPP_REC_COUNT
160 ,CASE WHEN inner_time.report_date < :SD_SDATE
161 THEN f.total_unapplied_amount
162 ELSE NULL
163 END FII_AR_PRIOR_UNAPP_REC_AMT
164 ,CASE WHEN inner_time.report_date < :SD_SDATE
165 THEN f.total_unapplied_count
166 ELSE NULL
167 END FII_AR_PRIOR_UNAPP_REC_COUNT
168 ,NULL FII_AR_TOTAL_REC_AMT
169 ,NULL FII_AR_TOTAL_REC_COUNT
170 -- Since VIEWBY is always MONTH, base MV would be used
171 FROM fii_ar_rct_aging_base_mv'||fii_ar_util_pkg.g_curr_suffix||' f
172 ,fii_time_ent_period time
173 ,(SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ cal.time_id time_id
174 ,cal.period_type_id period_type_id
175 ,cal.report_date report_date
176 ,gt.*
177 FROM fii_time_structures cal
178 ,fii_ar_summary_gt gt
179 WHERE report_date IN (SELECT end_date
180 FROM fii_time_ent_period
181 WHERE start_date >= :SD_PRIOR_PRIOR
182 AND end_date <= :ASOF_DATE
183 )
184 AND BITAND(cal.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
185 ) inner_time
186 WHERE inner_time.time_id = f.time_id
187 AND f.period_type_id = inner_time.period_type_id
188 AND f.org_id = inner_time.org_id
189 AND time.end_date = inner_time.report_date
190 AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '||l_child_party_where||'
191 '||l_collector_where||'
192 UNION ALL
193 SELECT /*+ INDEX(f FII_AR_NET_REC_BASE_mv_N1)*/
194 inner_time.sequence period_sequence
195 ,NULL FII_AR_UNAPP_REC_AMT
196 ,NULL FII_AR_UNAPP_REC_COUNT
197 ,NULL FII_AR_PRIOR_UNAPP_REC_AMT
198 ,NULL FII_AR_PRIOR_UNAPP_REC_COUNT
199 ,CASE WHEN f.header_filter_date >= inner_time.start_date
200 THEN total_receipt_amount
201 ELSE NULL
202 END FII_AR_TOTAL_REC_AMT
203 ,CASE WHEN f.header_filter_date >= inner_time.start_date
204 THEN total_receipt_count
205 ELSE NULL
206 END FII_AR_TOTAL_REC_COUNT
207 FROM fii_ar_net_rec_base_mv'||fii_ar_util_pkg.g_curr_suffix||' f
208 ,(SELECT /*+no_merge leading(gt) cardinality(gt 1)*/ time.ent_period_id ent_period_id
209 ,time.sequence sequence
210 ,time.start_date start_date
211 ,gt.*
212 FROM fii_time_ent_period time
213 ,fii_ar_summary_gt gt
214 WHERE time.start_date > :SD_PRIOR -- Need to Pick only one year data
215 AND time.end_date <= :ASOF_DATE
216 ) inner_time
217 WHERE inner_time.ent_period_id = f.time_id
218 AND f.period_type_id = 32
219 AND f.org_id = inner_time.org_id
220 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_child_party_where||'
221 '||l_collector_where||''||l_curr_period_unapp_rec_sql||''||l_curr_period_total_rec_sql||'
222 ) inner_inline_view
223 GROUP BY inner_inline_view.period_sequence
224 ) inner_view
225 ,fii_time_ent_period time
226 WHERE time.start_date <= :ASOF_DATE
227 AND time.start_date > :SD_PRIOR
228 AND time.sequence = inner_view.period_sequence (+)
229 -- Outer join to display all the 12 months irrespective of whether data is available or not for those months
230 ORDER BY time.start_date
231 ';
232
233 -- Call to UTIL package to bind the variables
234
235 fii_ar_util_pkg.bind_variable(l_sqlstmt
236 ,p_page_parameter_tbl
237 ,p_unapp_rct_trend_sql
238 ,p_unapp_rct_trend_output
239 );
240
241 END get_unapp_rct_trend;
242
243 END fii_ar_unapp_rct_trend_pkg;
[Home] [Help]