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.
PACKAGE BODY: APPS.FII_AR_BA_TRX_CLASS_PKG

Source


1 PACKAGE BODY FII_AR_BA_TRX_CLASS_PKG AS
2 /*  $Header: FIIARDBIBTCB.pls 120.11 2007/05/15 20:47:01 vkazhipu ship $ */
3 
4 --   This package will provide sql statements to retrieve data for Billing Activity
5 
6 -- --------------------------------------------------------------------------
7 -- Name : get_bill_act_trx_class
8 -- Type : Procedure
9 -- Description : This procedure passes SQL to PMV for Billing Activity Transaction
10 --               Class Report
11 -----------------------------------------------------------------------------
12 
13 
14 PROCEDURE get_bill_act_trx_class (
15   p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, ba_trx_class_sum_sql out NOCOPY VARCHAR2,
16   ba_trx_class_sum_out out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
17 
18   l_sql_stmt			VARCHAR2(30000);
19   l_view_by_flag                NUMBER(1);
20   l_view_by                     VARCHAR2(120);
21   l_customer_where              VARCHAR2(60);
22   l_cust_acct_where             VARCHAR2(60);
23   l_org_where                   VARCHAR2(30);
24   l_industry_where              VARCHAR2(240);
25   l_where_clause                VARCHAR2(1000);
26   l_inner_from_clause           VARCHAR2(60);
27   l_inner_where_clause          VARCHAR2(60);
28   l_child_party_where           VARCHAR2(60);
29 
30   l_cust_acct_or_leaf_amt_drill VARCHAR2(240);
31 
32   l_cust_acct_or_leaf_inv_drill VARCHAR2(240);
33   l_cust_acct_or_leaf_dm_drill  VARCHAR2(240);
34   l_cust_acct_or_leaf_cb_drill  VARCHAR2(240);
35   l_customer_drill              VARCHAR2(120);
36   l_amount_drill                VARCHAR2(120);
37 
38   l_cust_suffix                 VARCHAR2(5);
39   l_curr_suffix                 VARCHAR2(4);
40   l_viewby_id                   VARCHAR2(30);
41 
42 
43   l_order_by			varchar2(500);
44   l_order_column		varchar2(100);
45   l_gt_hint varchar2(500);
46 BEGIN
47 
48     /* Reset Global Variables */
49     fii_ar_util_pkg.reset_globals;
50 
51 
52 /* Get the parameters that the user has selected */
53 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
54 
55 
56 /* Populate the dimension combination(s) that the user has access to */
57 fii_ar_util_pkg.populate_summary_gt_tables;
58 
59 l_gt_hint := ' leading(t) cardinality(t 1) ';
60 
61 -- Get the view by
62 l_view_by := fii_ar_util_pkg.g_view_by;
63 
64 /* Check whether join on party is is reqd. or not */
65 IF (fii_ar_util_pkg.g_party_id <> '-111' OR l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
66     l_child_party_where := ' AND f.party_id   = gt.party_id ';
67 END IF;
68 
69 -- Check whether we need a filter for parent_party_id. This is applicable only if the Ct. dimension is hierarchial and view by
70 -- is customer.
71 IF fii_ar_util_pkg.g_is_hierarchical_flag = 'Y' and l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
72        l_customer_where := ' and f.parent_party_id = gt.parent_party_id';
73        l_view_by_flag :=2; -- for Customer
74 END IF;
75 
76 
77 
78 -- Checks whether filter for Ct. acct. is reqd or not
79 IF l_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
80        l_cust_acct_where := ' and f.cust_account_id = gt.cust_account_id';
81        l_view_by_flag:=3;
82        l_gt_hint := ' leading(t.gt) cardinality(t.gt 1) ';
83 END IF;
84 
85 /* Org filter will be there in all cases */
86       l_org_where := ' and f.org_id=gt.org_id';
87 
88 /* Check whether industry filter is reqd or not */
89 IF  l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
90        l_industry_where := NULL;
91 ELSIF fii_ar_util_pkg.g_industry_id <> '-111' OR l_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
92       l_industry_where := ' and f.class_code=gt.class_code and f.class_category=gt.class_category';
93 END IF;
94 
95 
96 -- The below mentioned variable will make the code easy to understand.
97   l_where_clause := l_child_party_where||l_customer_where || l_cust_acct_where || l_org_where||l_industry_where;
98 
99 
100 -- Drills
101 
102  IF (l_view_by_flag = 2 ) /* View by is ct. and implementation is heirarchial */ THEN
103 
104 	 l_amount_drill := 'pFunctionName=FII_AR_BILL_ACT_TRX_CLASS&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
105    l_customer_drill:= 'pFunctionName=FII_AR_BILL_ACT_TRX_CLASS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
106 
107 END IF;
108 
109 --vkazhipu added for bug 5960517
110  IF l_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' OR
111     l_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS'  THEN
112     l_view_by_flag:=1; -- for ou,industry
113     IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
114      l_amount_drill := 'pFunctionName=FII_AR_BILL_ACT_TRX_CLASS&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
115     ELSE
116       l_amount_drill := 'pFunctionName=FII_AR_BILL_ACT_TRX_CLASS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
117     END IF;
118 END IF;
119 
120 -- Drill on amount and view by is ct acct or ct is leaf. Drilll to Billing Activity Detail
121 IF l_view_by_flag = 3 THEN
122   l_cust_acct_or_leaf_amt_drill := 'pFunctionName=FII_AR_BILLING_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_CUST_ACCOUNT=VIEWBYID';
123 ELSE
124   l_cust_acct_or_leaf_amt_drill := 'pFunctionName=FII_AR_BILLING_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
125 END IF;
126 
127 -- Drill on Invoice amt and view by is ct acct or ct is leaf. Drill to Invoice Activity Detail
128 IF l_view_by_flag = 3 THEN
129   l_cust_acct_or_leaf_inv_drill:= 'pFunctionName=FII_AR_INV_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_CUST_ACCOUNT=VIEWBYID';
130 ELSE
131   l_cust_acct_or_leaf_inv_drill:= 'pFunctionName=FII_AR_INV_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
132 END IF;
133 
134 -- Drill on DM amt and view by is ct acct or ct is leaf. Drill to Debit Memo Detail
135 IF l_view_by_flag = 3 THEN
136   l_cust_acct_or_leaf_dm_drill:= 'pFunctionName=FII_AR_DM_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_CUST_ACCOUNT=VIEWBYID';
137 ELSE
138   l_cust_acct_or_leaf_dm_drill:= 'pFunctionName=FII_AR_DM_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
139 END IF;
140 
141 -- Drill on CB amt and view by is ct acct or ct is leaf. Drill to Chargeback Activity Detail
142 IF l_view_by_flag = 3 THEN
143   l_cust_acct_or_leaf_cb_drill:= 'pFunctionName=FII_AR_CB_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_CUST_ACCOUNT=VIEWBYID';
144 ELSE
145    l_cust_acct_or_leaf_cb_drill:= 'pFunctionName=FII_AR_CB_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
146 END IF;
147 
148 -- Construct the order by
149 IF(instr(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
150 
151    /*This means no particular sort column is selected in the report
152    So sort on the default column in descending order
153    NVL is added to make sure the NULL values appear last*/
154 
155    l_order_by := 'ORDER BY NVL(FII_AR_BILL_ACT_AMT, -999999999) DESC';
156 
157   ELSIF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
158 
159    /*This means a particular sort column is clicked to have descending order
160    in which case we would want all the NULL values to appear last in the
161    report so add an NVL to that column*/
162 
163    l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
164    l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
165 
166   ELSE
167 
168    /*This is the case when user has asked for an ascending order sort.
169    Use PMV's order by clause*/
170 
171    l_order_by := '&ORDER_BY_CLAUSE';
172 
173   END IF;
174 
175   IF l_view_by_flag=3 THEN /* Viewby is Ct. acct */
176     l_inner_from_clause := fii_ar_util_pkg.get_from_statement;
177     l_inner_where_clause := ' and '||fii_ar_util_pkg.get_where_statement;
178   ELSE
179     l_inner_from_clause := 'fii_ar_summary_gt ';
180     l_inner_where_clause := NULL;
181   END IF;
182 
183   l_cust_suffix := fii_ar_util_pkg.g_cust_suffix;
184   l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
185 
186 -- Now the variable initialization is done, Start to build the PMV Query
187 
188 l_sql_stmt := l_sql_stmt || 'SELECT  VIEWBY,
189                                      VIEW_BY_ID VIEWBYID,
190 		                     NULLIF(SUM(FII_AR_BILL_ACT_AMT),0) FII_AR_BILL_ACT_AMT,
191                                      NULLIF(SUM(FII_AR_BILL_ACT_COUNT),0) FII_AR_BILL_ACT_COUNT,
192                                      NULLIF(SUM(FII_AR_BA_INV_AMT),0) FII_AR_BA_INV_AMT,
193 				     NULLIF(SUM(FII_AR_BA_INV_COUNT),0) FII_AR_BA_INV_COUNT,
194 				     NULLIF(SUM(FII_AR_BA_DM_AMT),0) FII_AR_BA_DM_AMT,
195 				     NULLIF(SUM(FII_AR_BA_DM_COUNT),0) FII_AR_BA_DM_COUNT,
196 				     NULLIF(SUM(FII_AR_BA_CB_AMT),0) FII_AR_BA_CB_AMT,
197 				     NULLIF(SUM(FII_AR_BA_CB_COUNT),0) FII_AR_BA_CB_COUNT,
198 				     NULLIF(SUM(FII_AR_BA_BR_AMT),0) FII_AR_BA_BR_AMT,
199                                      NULLIF(SUM(FII_AR_BA_BR_COUNT),0) FII_AR_BA_BR_COUNT,
200 				     NULLIF(SUM(FII_AR_BA_DEP_AMT),0) FII_AR_BA_DEP_AMT,
201 				     NULLIF(SUM(FII_AR_BA_DEP_COUNT),0) FII_AR_BA_DEP_COUNT,
202 				     NULLIF(SUM(FII_AR_BA_CM_AMT),0) FII_AR_BA_CM_AMT,
203 				     NULLIF(SUM(FII_AR_BA_CM_COUNT),0) FII_AR_BA_CM_COUNT,
204                                      NULLIF(SUM(SUM(FII_AR_BILL_ACT_AMT)) over(),0) FII_AR_GT_BILL_ACT_AMT ,
205 				     NULLIF(SUM(SUM(FII_AR_BILL_ACT_COUNT)) over(),0) FII_AR_GT_BILL_ACT_COUNT ,
206 				     NULLIF(SUM(SUM(FII_AR_BA_INV_AMT)) over(),0) FII_AR_GT_BA_INV_AMT,
207 				     NULLIF(SUM(SUM(FII_AR_BA_INV_COUNT)) over(),0) FII_AR_GT_BA_INV_COUNT,
208 				     NULLIF(SUM(SUM(FII_AR_BA_DM_AMT)) over(),0) FII_AR_GT_BA_DM_AMT,
209 				     NULLIF(SUM(SUM(FII_AR_BA_DM_COUNT)) over(),0) FII_AR_GT_BA_DM_COUNT,
210 				     NULLIF(SUM(SUM(FII_AR_BA_CB_AMT)) over(),0) FII_AR_GT_BA_CB_AMT,
211 				     NULLIF(SUM(SUM(FII_AR_BA_CB_COUNT)) over(),0) FII_AR_GT_BA_CB_COUNT,
212 				     NULLIF(SUM(SUM(FII_AR_BA_BR_AMT)) over(),0) FII_AR_GT_BA_BR_AMT,
213                                      NULLIF(SUM(SUM(FII_AR_BA_BR_COUNT)) over(),0) FII_AR_GT_BA_BR_COUNT,
214 				     NULLIF(SUM(SUM(FII_AR_BA_DEP_AMT)) over(),0) FII_AR_GT_BA_DEP_AMT,
215 				     NULLIF(SUM(SUM(FII_AR_BA_DEP_COUNT)) over(),0) FII_AR_GT_BA_DEP_COUNT,
216 				     NULLIF(SUM(SUM(FII_AR_BA_CM_AMT)) over(),0) FII_AR_GT_BA_CM_AMT,
217 				     NULLIF(SUM(SUM(FII_AR_BA_CM_COUNT)) over(),0) FII_AR_GT_BA_CM_COUNT,
218 				     NULLIF(SUM(FII_AR_BA_INV_AMT),0) FII_AR_G_BA_INV_AMT,
219 				     NULLIF(SUM(FII_AR_BA_DM_AMT),0) FII_AR_G_BA_DM_AMT,
220 				     NULLIF(SUM(FII_AR_BA_CB_AMT),0) FII_AR_G_BA_CB_AMT,
221 				     NULLIF(SUM(FII_AR_BA_BR_AMT),0) FII_AR_G_BA_BR_AMT,
222 				     NULLIF(SUM(FII_AR_BA_DEP_AMT),0) FII_AR_G_BA_DEP_AMT,
223 				     NULLIF(SUM(FII_AR_BA_CM_AMT),0) FII_AR_G_BA_CM_AMT,
224 				     NULLIF(SUM(FII_AR_BA_INV_COUNT),0) FII_AR_G_BA_INV_COUNT,
225 				     NULLIF(SUM(FII_AR_BA_DM_COUNT),0) FII_AR_G_BA_DM_COUNT,
226 				     NULLIF(SUM(FII_AR_BA_CB_COUNT),0) FII_AR_G_BA_CB_COUNT,
227 				     NULLIF(SUM(FII_AR_BA_BR_COUNT),0) FII_AR_G_BA_BR_COUNT,
228 				     NULLIF(SUM(FII_AR_BA_DEP_COUNT),0) FII_AR_G_BA_DEP_COUNT,
229 				     NULLIF(SUM(FII_AR_BA_CM_COUNT),0) FII_AR_G_BA_CM_COUNT, ';
230 
231 
232 IF l_view_by_flag = 1 THEN /* It means that the view by is either ou or Industry */
233    l_sql_stmt := l_sql_stmt ||'DECODE(NVL(SUM(FII_AR_BILL_ACT_AMT),0),0,'''','''|| l_amount_drill ||''') FII_AR_BILL_ACT_AMT_DRILL ,
234 	                       DECODE(NVL(SUM(FII_AR_BA_INV_AMT),0),0,'''','''|| l_amount_drill ||''') FII_AR_BA_INV_AMT_DRILL ,
235                                DECODE(NVL(SUM(FII_AR_BA_DM_AMT),0),0,'''','''|| l_amount_drill ||''') FII_AR_BA_DM_AMT_DRILL ,
236 			       DECODE(NVL(SUM(FII_AR_BA_CB_AMT),0),0,'''','''|| l_amount_drill ||''') FII_AR_BA_CB_AMT_DRILL ,
237 			       NULL FII_AR_VIEW_BY_DRILL';
238 ELSIF /*( l_view_by_flag = 3 )
239      or */ (l_view_by = 'CUSTOMER+FII_CUSTOMERS' and fii_ar_util_pkg.g_is_hierarchical_flag = 'N' ) THEN
240 
241    l_sql_stmt := l_sql_stmt ||'DECODE(NVL(SUM(FII_AR_BILL_ACT_AMT),0),0,'''','''|| l_cust_acct_or_leaf_amt_drill || ''') FII_AR_BILL_ACT_AMT_DRILL ,
242 	                       DECODE(NVL(SUM(FII_AR_BA_INV_AMT),0),0,'''','''||l_cust_acct_or_leaf_inv_drill || ''') FII_AR_BA_INV_AMT_DRILL ,
243 			       DECODE(NVL(SUM(FII_AR_BA_DM_AMT),0),0,'''','''||l_cust_acct_or_leaf_dm_drill || ''') FII_AR_BA_DM_AMT_DRILL ,
244 			       DECODE(NVL(SUM(FII_AR_BA_CB_AMT),0),0,'''','''||l_cust_acct_or_leaf_cb_drill || ''') FII_AR_BA_CB_AMT_DRILL ,
245                                NULL FII_AR_VIEW_BY_DRILL ';
246 ELSIF l_view_by_flag =2 or l_view_by_flag=3 THEN /* It means that the View by Is ct and implementation is hierarchial */
247 
248     l_sql_stmt := l_sql_stmt || ' DECODE(NVL(SUM(FII_AR_BILL_ACT_AMT),0),0,'''',FII_AR_BILL_ACT_AMT_DRILL) FII_AR_BILL_ACT_AMT_DRILL,
249                                   DECODE(NVL(SUM(FII_AR_BA_INV_AMT),0),0,'''',FII_AR_BA_INV_AMT_DRILL) FII_AR_BA_INV_AMT_DRILL,
250                                   DECODE(NVL(SUM(FII_AR_BA_DM_AMT),0),0,'''',FII_AR_BA_DM_AMT_DRILL) FII_AR_BA_DM_AMT_DRILL,
251 				  DECODE(NVL(SUM(FII_AR_BA_CB_AMT),0),0,'''',FII_AR_BA_CB_AMT_DRILL) FII_AR_BA_CB_AMT_DRILL,
252                                   FII_AR_VIEW_BY_DRILL ';
253 END IF;
254 
255 l_sql_stmt := l_sql_stmt || ' FROM ( SELECT f.inv_ba_amount+f.dm_ba_amount+f.cb_ba_amount
256                                    +f.br_ba_amount+f.dep_ba_amount + f.cm_ba_amount FII_AR_BILL_ACT_AMT,
257                                   f.inv_ba_count+f.dm_ba_count+f.cb_ba_count
258                                    +f.br_ba_count+f.dep_ba_count + f.cm_ba_count FII_AR_BILL_ACT_COUNT,
259                                    f.inv_ba_amount FII_AR_BA_INV_AMT,
260 		                   f.inv_ba_count FII_AR_BA_INV_COUNT,
261 		                   f.dm_ba_amount FII_AR_BA_DM_AMT,
262 		                   f.dm_ba_count FII_AR_BA_DM_COUNT,
263 		                   f.cb_ba_amount FII_AR_BA_CB_AMT,
264 		                   f.cb_ba_count FII_AR_BA_CB_COUNT,
265 		                   f.br_ba_amount FII_AR_BA_BR_AMT,
266 		                   f.br_ba_count FII_AR_BA_BR_COUNT,
267 		                   f.dep_ba_amount FII_AR_BA_DEP_AMT,
268 		                   f.dep_ba_count FII_AR_BA_DEP_COUNT,
269 		                   f.cm_ba_amount FII_AR_BA_CM_AMT,
270 		                   f.cm_ba_count FII_AR_BA_CM_COUNT,
271                                    gt.viewby viewby,
272                                    gt.viewby_code VIEW_BY_ID';
273 
274 
275 IF l_view_by_flag = 3 THEN
276            l_cust_acct_or_leaf_amt_drill := l_cust_acct_or_leaf_amt_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
277 	   l_cust_acct_or_leaf_inv_drill := l_cust_acct_or_leaf_inv_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
278 	   l_cust_acct_or_leaf_dm_drill := l_cust_acct_or_leaf_dm_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
279 	   l_cust_acct_or_leaf_cb_drill := l_cust_acct_or_leaf_cb_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
280            l_sql_stmt:=l_sql_stmt || ','''||l_cust_acct_or_leaf_amt_drill||'''||gt.party_id||'''' FII_AR_BILL_ACT_AMT_DRILL,';
281            l_sql_stmt:=l_sql_stmt || ''''||l_cust_acct_or_leaf_inv_drill||'''||gt.party_id||'''' FII_AR_BA_INV_AMT_DRILL,';
282 	   l_sql_stmt:=l_sql_stmt || ''''||l_cust_acct_or_leaf_dm_drill||'''||gt.party_id||'''' FII_AR_BA_DM_AMT_DRILL,';
283 	   l_sql_stmt:=l_sql_stmt || ''''||l_cust_acct_or_leaf_cb_drill||'''||gt.party_id||'''' FII_AR_BA_CB_AMT_DRILL,';
284            l_sql_stmt:=l_sql_stmt ||' NULL FII_AR_VIEW_BY_DRILL ';
285 END IF;
286 
287 IF l_view_by_flag = 2 THEN /* The view by is ct. and the implementation is heirarchial */
288    l_sql_stmt := l_sql_stmt || ', gt.is_leaf_flag IS_LEAF_FLAG ,';
289    l_sql_Stmt :=l_sql_stmt || ' CASE WHEN gt.is_leaf_flag = ''Y'' OR gt.is_self_flag = ''Y'' THEN '''||
290                                   l_cust_acct_or_leaf_amt_drill||'''
291 				ELSE '''||
292 				  l_amount_drill||'''
293 				END FII_AR_BILL_ACT_AMT_DRILL ,
294                                 CASE WHEN gt.is_leaf_flag = ''Y'' OR gt.is_self_flag = ''Y'' THEN '''||
295                                   l_cust_acct_or_leaf_inv_drill||'''
296 				ELSE '''||
297 				  l_amount_drill||'''
298 				END FII_AR_BA_INV_AMT_DRILL ,
299 				 CASE WHEN gt.is_leaf_flag = ''Y'' OR gt.is_self_flag = ''Y'' THEN '''||
300                                   l_cust_acct_or_leaf_dm_drill||'''
301 				ELSE '''||
302 				  l_amount_drill||'''
303 				END FII_AR_BA_DM_AMT_DRILL ,
304 				 CASE WHEN gt.is_leaf_flag = ''Y'' OR gt.is_self_flag = ''Y'' THEN '''||
305                                   l_cust_acct_or_leaf_cb_drill||'''
306 				ELSE '''||
307 				  l_amount_drill||'''
308 				END FII_AR_BA_CB_AMT_DRILL ,
309 				DECODE(gt.is_self_flag,''Y'','''',DECODE(gt.is_leaf_flag,''Y'','''','''||l_customer_drill||''')) FII_AR_VIEW_BY_DRILL ';
310 
311 END IF;
312 
313 
314 l_sql_stmt:=l_sql_stmt||' FROM fii_ar_billing_act'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
315                                 (select /*+ no_merge '||l_gt_hint|| ' */ * from fii_time_structures cal, '||l_inner_from_clause||' t
316 	                         where cal.report_date = :ASOF_DATE
317 				 and BITAND(cal.record_type_id,:BITAND)= :BITAND '||l_inner_where_clause ||' ) gt
318                         where f.time_id = gt.time_id
319 			and gt.period_type_id=f.period_type_id
320                          '|| l_where_clause||') GROUP BY viewby,view_by_id ';
321 
322 IF l_view_by_flag = 2 or l_view_by_flag= 3 THEN
323    l_sql_stmt := l_sql_stmt || ',FII_AR_BILL_ACT_AMT_DRILL,FII_AR_BA_INV_AMT_DRILL,FII_AR_BA_DM_AMT_DRILL,FII_AR_BA_CB_AMT_DRILL, FII_AR_VIEW_BY_DRILL ';
324 END IF;
325 
326 l_sql_stmt := l_sql_stmt || l_order_by;
327 
328 
329     /* Pass back the pmv sql along with bind variables to PMV */
330     fii_ar_util_pkg.bind_variable(l_sql_stmt, p_page_parameter_tbl, ba_trx_class_sum_sql, ba_trx_class_sum_out);
331 
332 END get_bill_act_trx_class;
333 
334 
335 
336 END ;
337