[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_ACTIVITY
Source
1 PACKAGE BODY FII_AP_INV_ACTIVITY AS
2 /* $Header: FIIAPS4B.pls 120.7 2006/03/24 23:11:16 vkazhipu noship $ */
3
4 --vkazhipu added for performance repository tuning
5 --bug 4997442
6
7 g_date_string DATE;
8
9 PROCEDURE get_inv_activity (
10 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
11 inv_act_anal_sql OUT NOCOPY VARCHAR2,
12 inv_act_anal_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
13 IS
14 sqlstmt varchar2(14000);
15 l_viewby_dim VARCHAR2(240); -- what is the viewby
16 l_as_of_date DATE;
17 l_organization VARCHAR2(240);
18 l_supplier VARCHAR2(240);
19 l_currency VARCHAR2(240); -- random size, possibly high
20 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
21 l_record_type_id NUMBER; --
22 l_gid NUMBER; -- 0,4 or 8
23 l_viewby_string VARCHAR2(240);
24 inv_act_rec BIS_QUERY_ATTRIBUTES;
25 l_period_type VARCHAR2(240);
26 l_invoice_number VARCHAR2(240);
27 l_column_name VARCHAR2(240);
28 l_table_name VARCHAR2(240);
29 l_org_WHERE VARCHAR2(240);
30 l_supplier_WHERE VARCHAR2(240);
31 l_url_1 VARCHAR2(1000);
32 l_url_2 VARCHAR2(1000);
33 l_url_3 VARCHAR2(1000);
34 BEGIN
35
36 /*getting the parameters values by calling the util package*/
37 FII_PMV_Util.Get_Parameters(
38 p_page_parameter_tbl,
39 l_as_of_date,
40 l_organization,
41 l_supplier,
42 l_invoice_number,
43 l_period_type,
44 l_record_type_id,
45 l_viewby_dim,
46 l_currency,
47 l_viewby_id,
48 l_viewby_string,
49 l_gid,
50 l_org_WHERE,
51 l_supplier_WHERE
52 );
53
54 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
55 l_url_1 := 'pFunctionName=FII_AP_INV_ACTIVITY&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
56 l_url_2 := 'pFunctionName=FII_AP_INV_ACTIVITY&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
57 l_url_3 := 'pFunctionName=FII_AP_INV_ACTIVITY&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
58 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
59 l_url_1 := 'pFunctionName=FII_AP_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&FII_CURRENCIES=FII_CURRENCIES&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ENT_DTL';
60 l_url_2 := 'pFunctionName=FII_AP_E_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&FII_CURRENCIES=FII_CURRENCIES&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
61 l_url_3 := 'pFunctionName=FII_AP_MANUAL_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&FII_CURRENCIES=FII_CURRENCIES&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_MANUAL_INV_ENT_DTL';
62 END IF;
63
64 /*------------------------------------------------------------------------------------------------
65 | VIEWBY -Either Operating Unit or Supplier
66 | VIEWBY_ID -Either org_id/supplier_id
67 | FII_ATTRIBUTE1 Graph Title
68 | FII_ATTRIBUTE2 Graph Title
69 | FII_ATTRIBUTE7 Column Spanning
70 | FII_MEASURE1 Invoice_amt_entered
71 | FII_MEASURE2 Prior Invoice_amt_entered
72 | FII_MEASURE3 Change
73 | FII_MEASURE4 Invoice_count_entered
74 | FII_MEASURE5 Prior Invoice_count_entered
75 | FII_MEASURE6 Change
76 | FII_MEASURE7 Distribution_count
77 | FII_MEASURE8 Prior Distribution_count
78 | FII_MEASURE9 Change
79 | FII_ATTRIBUTE8 COLUMN SPANNING
80 | FII_MEASURE10 Electronic Invoice Amount
81 | FII_MEASURE11 Prior Electronic Invoice Amount
82 | FII_MEASURE12 Change in electronic Invoice Amount
83 | FII_MEASURE14 Prior Electronic Invoice Count
84 | FII_MEASURE23 Prior Manual Invoice Count
85 | FII_MEASURE13 Electronic Invoice Count
86 | FII_CAL1 For legend
87 | FII_MEASURE15 Change in Invoice Count
88 | FII_MEASURE16 Number of Distributions
89 | FII_MEASURE17 Prior Number of Distributions
90 | FII_MEASURE18 Change
91 | FII_ATTRIBUTE10 Column Spanning
92 | FII_MEASURE19 Manual Invoice Amount
93 | FII_MEASURE20 Prior Manual Invoice Amount
94 | FII_MEASURE21 Change
95 | FII_MEASURE22 Manual Number of Invoices
96 | FII_CAL2 Dummy variable for Legend
97 | FII_MEASURE24 Change in Manual Invoices
98 | FII_MEASURE25 Manual Distribution Count
99 | FII_MEASURE26 Prior Manual Distribution Count
100 | FII_MEASURE27 Change in Manual Distribution Count
101 | FII_MEASURE28 Grand Total(invoice amount entered)
102 | FII_MEASURE29 Grand Total(prior invoice amount)
103 | FII_MEASURE30 Grand Total(invoice count entered)
104 | FII_DIM1 Grand Total(prior invoice count entered)
105 | FII_DIM2 Grand Total(distribution count)
106 | FII_DIM3 Grand Total(prior distribution count)
107 | FII_DIM4 Grand Total(electronic invoice entered)
108 | FII_DIM5 Grand Total(prior electronic invoice amount entered)
109 | FII_DIM7 Grand Total(electronic invoice count)
110 | FII_DIM8 Grand Total(prior electronic invoice count)
111 | FII_DIM9 Grand Total(electronic distribution count)
112 | FII_DIM10 Grand Total(prior electronic distribution count)
113 | FII_FSG_COL1 Grand Total(manual amount entered)
114 | FII_FSG_COL2 Grand Total(prior manual amount entered)
115 | FII_FSG_COL3 Grand Total(manual invoice count)
116 | FII_FSG_COL4 Grand Total(prior manual invoice count)
117 | FII_FSG_COL5 Grand Total(manual distribution count)
118 | FII_FSG_COL6 Grand Total(prior distribution count)
119 | FII_FSG_COL7 Grand Total(Change Total Invoice Amt.)
120 | FII_FSG_COL8 Grand Total(Change No. of Invoices Entered)
121 | FII_FSG_COL9 Grand Total(Change no. of Distributions)
122 | FII_FSG_COL10 Grand Total(Change Electronic Invoice Amount)
123 | FII_FSG_COL11 Grand Total(Change No. of Elect. invoices Entered)
124 | FII_FSG_COL12 Grand Total(Change No. of Distributions)
125 | FII_FSG_COL13 Grand Total(Change Manual Invoice Amount)
126 | FII_FSG_COL14 Grand Total(Change No. of Manual Invoices)
127 | FII_ATTRIBUTE3 Grand Total(Change No. of Distributions)
128 | FII_ATTRIBUTE11 Drill across(FII_MEASURE4)
129 | FII_ATTRIBUTE12 Drill Dynamic(FII_MEASURE13)
130 | FII_ATTRIBUTE13 Drill Dynamic(FII_MEASURE22)
131 ------------------------------------------------------------------------------------------------------------------*/
132
133
134 ----constructing the sql statement
135
136
137 sqlstmt:= '
138 SELECT viewby_dim.value VIEWBY,
139 viewby_dim.id VIEWBYID,
140 f.FII_MEASURE1 FII_MEASURE1,
141 f.FII_MEASURE2 FII_MEASURE2,
142 f.FII_MEASURE4 FII_MEASURE4,
143 f.FII_MEASURE5 FII_MEASURE5,
144 f.FII_MEASURE7 FII_MEASURE7,
145 f.FII_MEASURE8 FII_MEASURE8,
146 f.FII_MEASURE10 FII_MEASURE10,
147 f.FII_MEASURE11 FII_MEASURE11,
148 f.FII_MEASURE13 FII_MEASURE13,
149 f.FII_MEASURE14 FII_MEASURE14,
150 f.FII_MEASURE16 FII_MEASURE16,
151 f.FII_MEASURE17 FII_MEASURE17,
152 f.FII_MEASURE19 FII_MEASURE19,
153 f.FII_MEASURE20 FII_MEASURE20,
154 f.FII_MEASURE22 FII_MEASURE22,
155 f.FII_MEASURE23 FII_MEASURE23,
156 f.FII_MEASURE25 FII_MEASURE25,
157 f.FII_MEASURE26 FII_MEASURE26,
158 f.FII_MEASURE28 FII_MEASURE28,
159 f.FII_MEASURE29 FII_MEASURE29,
160 f.FII_MEASURE30 FII_MEASURE30,
161 f.FII_DIM1 FII_DIM1,
162 f.FII_DIM2 FII_DIM2,
163 f.FII_DIM3 FII_DIM3,
164 f.FII_DIM4 FII_DIM4,
165 f.FII_DIM5 FII_DIM5,
166 f.FII_DIM7 FII_DIM7,
167 f.FII_DIM8 FII_DIM8,
168 f.FII_DIM9 FII_DIM9,
169 f.FII_DIM10 FII_DIM10,
170 f.FII_FSG_COL1 FII_FSG_COL1,
171 f.FII_FSG_COL2 FII_FSG_COL2,
172 f.FII_FSG_COL3 FII_FSG_COL3,
173 f.FII_FSG_COL4 FII_FSG_COL4,
174 f.FII_FSG_COL5 FII_FSG_COL5,
175 f.FII_FSG_COL6 FII_FSG_COL6,
176 '''||l_url_1||''' FII_ATTRIBUTE11,
177 '''||l_url_2||''' FII_ATTRIBUTE12,
178 '''||l_url_3||''' FII_ATTRIBUTE13
179 FROM
180 (SELECT
181 id,
182 FII_MEASURE1,
183 FII_MEASURE2,
184 FII_MEASURE4,
185 FII_MEASURE5,
186 FII_MEASURE7,
187 FII_MEASURE8,
188 FII_MEASURE10,
189 FII_MEASURE11,
190 FII_MEASURE13,
191 FII_MEASURE14,
192 FII_MEASURE16,
193 FII_MEASURE17,
194 FII_MEASURE19,
195 FII_MEASURE20,
196 FII_MEASURE22,
197 FII_MEASURE23,
198 FII_MEASURE25,
199 FII_MEASURE26,
200 SUM(FII_MEASURE1) OVER() FII_MEASURE28,
201 SUM(FII_MEASURE2) OVER() FII_MEASURE29,
202 SUM(FII_MEASURE4) OVER() FII_MEASURE30,
203 SUM(FII_MEASURE5) OVER() FII_DIM1,
204 SUM(FII_MEASURE7) OVER() FII_DIM2,
205 SUM(FII_MEASURE8) OVER() FII_DIM3,
206 SUM(FII_MEASURE10) OVER() FII_DIM4,
207 SUM(FII_MEASURE11) OVER() FII_DIM5,
208 SUM(FII_MEASURE13) OVER() FII_DIM7,
209 SUM(FII_MEASURE14) OVER() FII_DIM8,
210 SUM(FII_MEASURE16) OVER() FII_DIM9,
211 SUM(FII_MEASURE17) OVER() FII_DIM10,
212 SUM(FII_MEASURE19) OVER() FII_FSG_COL1,
213 SUM(FII_MEASURE20) OVER() FII_FSG_COL2,
214 SUM(FII_MEASURE22) OVER() FII_FSG_COL3,
215 SUM(FII_MEASURE23) OVER() FII_FSG_COL4,
216 SUM(FII_MEASURE25) OVER() FII_FSG_COL5,
217 SUM(FII_MEASURE26) OVER() FII_FSG_COL6,
218 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
219 FROM
220 (
221 SELECT f.'||l_viewby_id||' id,
222 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
223 THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE1,
224 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
225 THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE2,
226 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
227 THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE4,
228 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
229 THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE5,
230 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
231 THEN f.e_invoice_count ELSE 0 END) FII_MEASURE13,
232 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
233 THEN f.e_invoice_count ELSE 0 END) FII_MEASURE14,
234 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
235 THEN f.distribution_count ELSE 0 END) FII_MEASURE7,
236 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
237 THEN f.distribution_count ELSE 0 END) FII_MEASURE8,
238 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
239 THEN f.e_distribution_count ELSE 0 END) FII_MEASURE16,
240 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
241 THEN f.e_distribution_count ELSE 0 END) FII_MEASURE17,
242 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
243 THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE10,
244 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
245 THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE11,
246 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
247 THEN f.invoice_amt_entered'||l_currency||' - f.e_invoice_amt'||l_currency||'
248 ELSE 0 END) FII_MEASURE19,
249 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
250 THEN f.invoice_amt_entered'||l_currency||' - f.e_invoice_amt'||l_currency||'
251 ELSE 0 END) FII_MEASURE20,
252 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
253 THEN f.invoice_count_entered - f.e_invoice_count ELSE 0 END) FII_MEASURE22,
254 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
255 THEN f.invoice_count_entered - f.e_invoice_count ELSE 0 END) FII_MEASURE23,
256 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
257 THEN f.distribution_count - f.e_distribution_count ELSE 0 END) FII_MEASURE25,
258 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
259 THEN f.distribution_count - f.e_distribution_count ELSE 0 END) FII_MEASURE26
260 FROM FII_AP_IVATY_XB_MV f,
261 fii_time_structures cal
262 WHERE f.time_id = cal.time_id
263 AND f.period_type_id = cal.period_type_id
264 '||l_org_WHERE||l_supplier_WHERE||'
265 AND bitAND(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
266 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
267 AND f.gid = :GID
268 GROUP BY f.'||l_viewby_id||')) f,
269 ('||l_viewby_string||') viewby_dim
270 WHERE f.id = viewby_dim.id
271 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
272 &ORDER_BY_CLAUSE';
273
274 /*Section for binding the variables*/
275
276 FII_PMV_Util.bind_variable(
277 p_sqlstmt=>sqlstmt,
278 p_page_parameter_tbl=>p_page_parameter_tbl,
279 p_sql_output=>inv_act_anal_sql,
280 p_bind_output_table=>inv_act_anal_output,
281 p_record_type_id=>l_record_type_id,
282 p_gid=>l_gid
283 );
284
285
286 END get_inv_activity;
287
288
289
290
291
292
293 PROCEDURE get_inv_type (
294 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
295 inv_type_sql OUT NOCOPY VARCHAR2,
296 inv_type_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
297 IS
298 sqlstmt varchar2(14000);
299 l_num NUMBER;
300 l_viewby_dim VARCHAR2(240); -- what is the viewby
301 l_as_of_date DATE;
302 l_organization VARCHAR2(240);
303 l_supplier VARCHAR2(240);
304 l_currency VARCHAR2(240); -- rANDom size, possibly high
305 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
306 l_record_type_id NUMBER; --
307 l_gid NUMBER; -- 0,4 or 8
308 l_viewby_string VARCHAR2(240);
309 inv_type_rec BIS_QUERY_ATTRIBUTES;
310 l_param_join VARCHAR2(240);
311 l_param_join_ou VARCHAR2(240);
312 l_curr_info VARCHAR2(240);
313 l_curr_suffix VARCHAR2(240);
314 l_prim_curr VARCHAR2(240);
315 l_sec_curr VARCHAR2(240);
316 l_period_type VARCHAR2(240);
317 l_invoice_number VARCHAR2(240);
318 l_column_name VARCHAR2(240);
319 l_table_name VARCHAR2(240);
320 l_org_WHERE VARCHAR2(240);
321 l_supplier_WHERE VARCHAR2(240);
322 l_url_1 VARCHAR2(1000);
323 l_url_2 VARCHAR2(1000);
324 l_url_3 VARCHAR2(1000);
325 l_url_4 VARCHAR2(1000);
326 l_url_5 VARCHAR2(1000);
327 l_url_6 VARCHAR2(1000);
328 l_url_7 VARCHAR2(1000);
329 l_url_8 VARCHAR2(1000);
330
331
332 BEGIN
333
334 /*getting the parameters values FROM the page parameter table*/
335 FII_PMV_Util.Get_Parameters(
336 p_page_parameter_tbl,
337 l_as_of_date,
338 l_organization,
339 l_supplier,
340 l_invoice_number,
341 l_period_type,
342 l_record_type_id,
343 l_viewby_dim,
344 l_currency,
345 l_viewby_id,
346 l_viewby_string,
347 l_gid,
348 l_org_WHERE,
349 l_supplier_WHERE
350 );
351 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
352 l_url_1 := 'pFunctionName=FII_AP_INV_TYPE&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
353 l_url_2 := '';
354 l_url_3 := '';
355 l_url_4 := '';
356 l_url_5 := '';
357 l_url_6 := '';
358 l_url_7 := '';
359 l_url_8 := '';
360 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
361 l_url_1 := 'pFunctionName=FII_AP_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ENT_DTL';
362 l_url_2 := 'pFunctionName=FII_AP_STANDARD_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_STANDARD_INV_ENT_DTL';
363 l_url_3 := 'pFunctionName=FII_AP_WITHHOLDING_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_WITHHOLDING_INV_ENT_DTL';
364 l_url_4 := 'pFunctionName=FII_AP_PREPAYMENT_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_PREPAYMENT_INV_ENT_DTL';
365 l_url_5 := 'pFunctionName=FII_AP_CREDIT_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_CREDIT_INV_ENT_DTL';
366 l_url_6 := 'pFunctionName=FII_AP_DEBIT_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_DEBIT_INV_ENT_DTL';
367 l_url_7 := 'pFunctionName=FII_AP_MIXED_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_MIXED_INV_ENT_DTL';
368 l_url_8 := 'pFunctionName=FII_AP_INTEREST_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INTEREST_INV_ENT_DTL';
369 END IF;
370
371
372 /*--------------------------------------------------------------
373 | VIEWBY Either Supplier or Operating Unit
374 | VIEWBY_ID Either supplier_id/org_id
375 | FII_MEASURE1 Invoice Amount
376 | FII_MEASURE2 Prior Invoice Amount
377 | FII_MEASURE3 Change
378 | FII_MEASURE4 Invoices Entered
379 | FII_MEASURE5 Prior Invoices Entered
380 | FII_MEASURE6 Change
381 | FII_MEASURE7 Standard
382 | FII_MEASURE8 Withholding
383 | FII_MEASURE9 Prepayment
384 | FII_MEASURE10 Credit
385 | FII_MEASURE11 Debit
386 | FII_MEASURE12 Mixed
387 | FII_MEASURE13 Interest
388 | FII_MEASURE15 Grand Total(invoice amount entered)
389 | FII_MEASURE16 Grand Total(prior invoice amount entered)
390 | FII_MEASURE17 Grand Total(invoice count entered)
391 | FII_MEASURE18 Grand Total(prior invoice count entered)
392 | FII_MEASURE19 Grand Total(standard)
393 | FII_MEASURE20 Grand Total(withholding)
394 | FII_MEASURE21 Grand Total(prepayment)
395 | FII_MEASURE22 Grand Total(credit)
396 | FII_MEASURE23 Grand Total(debit)
397 | FII_MEASURE24 Grand Total(mixed)
398 | FII_MEASURE25 Grand Total(interest)
399 | FII_DIM1 Grand Total(Invoice Amount Change)
400 | FII_DIM2 Grand Total(Invoice Count change)
401 | FII_ATTRIBUTE5 Dynamic Drill (FII_MEASURE4)
402 | FII_ATTRIBUTE 6 Dynamic Drill (FII_MEASURE7)
403 | FII_ATTRIBUTE 7 Dynamic Drill (FII_MEASURE8)
404 | FII_ATTRIBUTE 8 Dynamic Drill (FII_MEASURE9)
405 | FII_ATTRIBUTE 10 Dynamic Drill (FII_MEASURE10)
406 | FII_ATTRIBUTE11 Dynamic Drill (FII_MEASURE11)
407 | FII_ATTRIBUTE 12 Dynamic Drill (FII_MEASURE12)
408 | FII_ATTRIBUTE 13 Dynamic Drill (FII_MEASURE13)
409 ---------------------------------------------------------------------------*/
410
411
412 ----constructing the sql statement
413
414 sqlstmt:= '
415 select viewby_dim.value VIEWBY,
416 viewby_dim.id VIEWBYID,
417 f.FII_MEASURE1 FII_MEASURE1,
418 f.FII_MEASURE2 FII_MEASURE2,
419 f.FII_MEASURE4 FII_MEASURE4,
420 f.FII_MEASURE5 FII_MEASURE5,
421 f.FII_MEASURE7 FII_MEASURE7,
422 f.FII_MEASURE8 FII_MEASURE8,
423 f.FII_MEASURE9 FII_MEASURE9,
424 f.FII_MEASURE10 FII_MEASURE10,
425 f.FII_MEASURE11 FII_MEASURE11,
426 f.FII_MEASURE12 FII_MEASURE12,
427 f.FII_MEASURE13 FII_MEASURE13,
428 f.FII_MEASURE15 FII_MEASURE15,
429 f.FII_MEASURE16 FII_MEASURE16,
430 f.FII_MEASURE17 FII_MEASURE17,
431 f.FII_MEASURE18 FII_MEASURE18,
432 f.FII_MEASURE19 FII_MEASURE19,
433 f.FII_MEASURE20 FII_MEASURE20,
434 f.FII_MEASURE21 FII_MEASURE21,
435 f.FII_MEASURE22 FII_MEASURE22,
436 f.FII_MEASURE23 FII_MEASURE23,
437 f.FII_MEASURE24 FII_MEASURE24,
438 f.FII_MEASURE25 FII_MEASURE25,
439 '''||l_url_1||''' FII_ATTRIBUTE5,
440 '''||l_url_2||''' FII_ATTRIBUTE6,
441 '''||l_url_3||''' FII_ATTRIBUTE7,
442 '''||l_url_4||''' FII_ATTRIBUTE8,
443 '''||l_url_5||''' FII_ATTRIBUTE10,
444 '''||l_url_6||''' FII_ATTRIBUTE11,
445 '''||l_url_7||''' FII_ATTRIBUTE12,
446 '''||l_url_8||''' FII_ATTRIBUTE13
447
448 FROM
449 (select
450 id,
451 FII_MEASURE1,
452 FII_MEASURE2,
453 FII_MEASURE4,
454 FII_MEASURE5,
455 FII_MEASURE7,
456 FII_MEASURE8,
457 FII_MEASURE9,
458 FII_MEASURE10,
459 FII_MEASURE11,
460 FII_MEASURE12,
461 FII_MEASURE13,
462 SUM(FII_MEASURE1) over() FII_MEASURE15,
463 SUM(FII_MEASURE2) over() FII_MEASURE16,
464 SUM(FII_MEASURE4) over() FII_MEASURE17,
465 SUM(FII_MEASURE5) over() FII_MEASURE18,
466 SUM(FII_MEASURE7) over() FII_MEASURE19,
467 SUM(FII_MEASURE8) over() FII_MEASURE20,
468 SUM(FII_MEASURE9) over() FII_MEASURE21,
469 SUM(FII_MEASURE10) over() FII_MEASURE22,
470 SUM(FII_MEASURE11) over() FII_MEASURE23,
471 SUM(FII_MEASURE12) over() FII_MEASURE24,
472 SUM(FII_MEASURE13) over() FII_MEASURE25,
473 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
474 FROM
475 (SELECT f.'||l_viewby_id||' id,
476 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
477 THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE1,
478 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
479 THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE2,
480 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
481 THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE4,
482 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
483 THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE5,
484 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
485 THEN f.stANDard_count ELSE 0 END) FII_MEASURE7,
486 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
487 THEN f.withholding_count ELSE 0 END) FII_MEASURE8,
488 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
489 THEN f.prepayment_count ELSE 0 END) FII_MEASURE9,
490 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
491 THEN f.credit_count ELSE 0 END) FII_MEASURE10,
492 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
493 THEN f.debit_count ELSE 0 END) FII_MEASURE11,
494 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
495 THEN f.mixed_count ELSE 0 END) FII_MEASURE12,
496 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
497 THEN f.interest_count ELSE 0 END) FII_MEASURE13
498 FROM FII_AP_IVATY_XB_MV f,
499 fii_time_structures cal
500 WHERE f.time_id = cal.time_id
501 AND f.period_type_id = cal.period_type_id
502 '||l_org_WHERE||l_supplier_WHERE||'
503 AND bitAND(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
504 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
505 AND f.gid = :GID
506 GROUP BY f.'||l_viewby_id||')) f,
507 ('||l_viewby_string||') viewby_dim
508 WHERE f.id = viewby_dim.id
509 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
510 &ORDER_BY_CLAUSE';
511
512 /*Section for binding the variables*/
513
514 FII_PMV_Util.bind_variable(
515 p_sqlstmt=>sqlstmt,
516 p_page_parameter_tbl=>p_page_parameter_tbl,
517 p_sql_output=>inv_type_sql,
518 p_bind_output_table=>inv_type_output,
519 p_record_type_id=>l_record_type_id,
520 p_gid=>l_gid
521 );
522
523 END get_inv_type;
524
525
526
527 /* - Electronic Invoice Analysis
528 - Procedure get_electronic_inv */
529
530
531 PROCEDURE get_electronic_inv (
532 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
533 elec_inv_sql OUT NOCOPY VARCHAR2,
534 elec_inv_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
535 IS
536 sqlstmt varchar2(14000);
537 l_num NUMBER;
538 l_viewby_dim VARCHAR2(240); -- what is the viewby
539 l_as_of_date DATE;
540 l_organization VARCHAR2(240);
541 l_supplier VARCHAR2(240);
542 l_currency VARCHAR2(240); -- random size, possibly high
543 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
544 l_record_type_id NUMBER; --
545 l_gid NUMBER; -- 0,4 or 8
546 l_viewby_string VARCHAR2(240);
547 elec_inv_rec BIS_QUERY_ATTRIBUTES;
548 l_param_join VARCHAR2(240);
549 l_param_join_ou VARCHAR2(240);
550 l_curr_info VARCHAR2(240);
551 l_curr_suffix VARCHAR2(240);
552 l_prim_curr VARCHAR2(240);
553 l_sec_curr VARCHAR2(240);
554 l_period_type VARCHAR2(240);
555 l_invoice_number VARCHAR2(240);
556 l_column_name VARCHAR2(240);
557 l_table_name VARCHAR2(240);
558 l_org_where VARCHAR2(240);
559 l_supplier_where VARCHAR2(240);
560 l_url_1 VARCHAR2(1000);
561 l_url_2 VARCHAR2(1000);
562 l_url_3 VARCHAR2(1000);
563 l_url_4 VARCHAR2(1000);
564 l_url_5 VARCHAR2(1000);
565 l_url_6 VARCHAR2(1000);
566 l_url_7 VARCHAR2(1000);
567 l_url_8 VARCHAR2(1000);
568
569
570 BEGIN
571
572 /*getting the parameters values FROM the page parameter table*/
573 FII_PMV_Util.Get_Parameters(
574 p_page_parameter_tbl,
575 l_as_of_date,
576 l_organization,
577 l_supplier,
578 l_invoice_number,
579 l_period_type,
580 l_record_type_id,
581 l_viewby_dim,
582 l_currency,
583 l_viewby_id,
584 l_viewby_string,
585 l_gid,
586 l_org_where,
587 l_supplier_where
588 );
589
590 /* Bug:3036059- Added a URL to l_url_1 for drill on 'Invoices Entered' column when viewed by Operating Unit. */
591
592 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
593 l_url_1 := 'pFunctionName=FII_AP_ELECTRONIC_INV&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
594 l_url_2 := '';
595 l_url_3 := '';
596 l_url_4 := '';
597 l_url_5 := '';
598 l_url_6 := '';
599 l_url_7 := '';
600 l_url_8 := '';
601
602 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
603 l_url_1 :='pFunctionName=FII_AP_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ENT_DTL';
604 l_url_2 := 'pFunctionName=FII_AP_XML_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_XML_INV_ENT_DTL';
605 l_url_3:= 'pFunctionName=FII_AP_EDI_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_EDI_INV_ENT_DTL';
606 l_url_4 :='pFunctionName=FII_AP_ERS_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_ERS_INV_ENT_DTL';
607 l_url_5:='pFunctionName=FII_AP_ISP_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_ISP_INV_ENT_DTL';
608 l_url_6:= 'pFunctionName=FII_AP_ASBN_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_ASBN_INV_ENT_DTL';
609 l_url_7:= 'pFunctionName=FII_AP_OTHER_SRC_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_OTHER_SRC_INV_ENT_DTL';
610 l_url_8:= 'pFunctionName=FII_AP_E_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
611
612 END IF;
613
614
615 /*-------------------------------------------------------------------------------------------------
616 | VIEWBY Either Operating Unit or Supplier
617 | VIEWBY_ID Either Org_id/supplier_id
618 | FII_ATTRIBUTE1 Graph title
619 | FII_ATTRIBUTE2 Graph title
620 | FII_ATTRIBUTE6 Column Spanning
621 | FII_MEASURE1 Amount
622 | FII_MEASURE2 Invoices
623 | FII_MEASURE3 Prior Total Invoices Entered
624 | FII_MEASURE4 Percent Electronic
625 | FII_MEASURE5 Prior % Electronic Invoices
626 | FII_MEASURE6 Change (fiimeasure4-fii_measure5)
627 | FII_MEASURE7 Electronic Invoices Amount
628 | FII_MEASURE8 Prior Electronic Invoices Amount
629 | FII_MEASURE9 Change in electronic invoice amount
630 | FII_MEASURE11 Prior Electronic Invoice Count
631 | FII_MEASURE14 Prior Manual Invoice Count
632 | FII_MEASURE10 Electronic Invoices Entered
633 | FII_CAL1 Dummy column for Legend
634 | FII_MEASURE13 Manual Invoice Count
635 | FII_MEASURE12 Change In electronic invoices entered
636 | FII_MEASURE15 XML
637 | FII_MEASURE16 EDI
638 | FII_MEASURE17 ERS
639 | FII_MEASURE18 ISP
640 | FII_MEASURE19 ASBN
641 | FII_MEASURE20 Other Integrated
642 | FII_MEASURE23 Grand Total(invoice amt entered)
643 | FII_MEASURE24 Grand Total(invoice count entered)
644 | FII_MEASURE25 Grand Total(prior invoice entered count)
645 | FII_MEASURE26 Grand Total(elec invoice amt entered)
646 | FII_MEASURE27 Grand Total(elec prior invoice amt entered)
647 | FII_MEASURE28 Grand Total(e_invoice _count)
648 | FII_MEASURE29 Grand Total(prior e_invoice_count)
649 | FII_MEASURE30 Grand Total(xml)
650 | FII_DIM1 Grand Total(edi)
651 | FII_DIM2 Grand Total(ers)
652 | FII_DIM3 Grand Total(isp)
653 | FII_DIM4 Grand Total(asbn)
654 | FII_DIM5 Grand Total(others)
655 | FII_DIM7 Grand Total(% electronic invoices entered)
656 | FII_DIM8 Grand Total(% prior electronic invoices entered)
657 | FII_DIM9 Change(electronic invoice amt)
658 | FII_DIM10 Change (electroninic invoice entered)
659 | FII_ATTRIBUTE3 Drill (Total Invoices Entered)
660 | FII_ATTRIBUTE12 Drill(Electronic Invoices Entered)
661 | FII_ATTRIBUTE4 Drill (XML)
662 | FII_ATTRIBUTE5 Drill (EDI)
663 | FII_ATTRIBUTE7 Drill (ERS)
664 | FII_ATTRIBUTE8 Drill (ISP)
665 | FII_ATTRIBUTE10 Drill (ASBN)
666 | FII_ATTRIBUTE11 Drill (Other Integrated)
667 | FII_ATTRIBUTE13 Change % for Number of Invoices(For Custom View)
668 | FII_ATTRIBUTE14 Grand Total for Change(For Custom View)
669 | FII_KPI1 Invoices Entered
670 | FII_KPI2 Hidden column Electronic Invoices
671 | FII_CV1 Amount (For Custom View Implementation)
672 | FII_CV2 Entered(For Custom View Implementation)
673 | FII_CV3 Change(For Custom View Implementation)
674 | FII_CV4 Electronic(For Custom View Implementation)
675 ------------------------------------------------------------------------------------------------------------*/
676
677
678 ----constructing the sql statement
679
680 sqlstmt:= '
681 SELECT viewby_dim.value VIEWBY,
682 viewby_dim.id VIEWBYID,
683 SUM(f.FII_MEASURE1) FII_MEASURE1,
684 SUM(f.FII_MEASURE2) FII_MEASURE2,
685 SUM(f.FII_MEASURE3) FII_MEASURE3,
686 SUM(f.FII_MEASURE7) FII_MEASURE7,
687 SUM(f.FII_MEASURE8) FII_MEASURE8,
688 SUM(f.FII_MEASURE10) FII_MEASURE10,
689 SUM(f.FII_MEASURE11) FII_MEASURE11,
690 SUM(f.FII_MEASURE15) FII_MEASURE15,
691 SUM(f.FII_MEASURE16) FII_MEASURE16,
692 SUM(f.FII_MEASURE17) FII_MEASURE17,
693 SUM(f.FII_MEASURE18) FII_MEASURE18,
694 SUM(f.FII_MEASURE19) FII_MEASURE19,
695 SUM(f.FII_MEASURE20) FII_MEASURE20,
696 SUM(f.FII_MEASURE23) FII_MEASURE23,
697 SUM(f.FII_MEASURE24) FII_MEASURE24,
698 SUM(f.FII_MEASURE25) FII_MEASURE25,
699 SUM(f.FII_MEASURE26) FII_MEASURE26,
700 SUM(f.FII_MEASURE27) FII_MEASURE27,
701 SUM(f.FII_MEASURE28) FII_MEASURE28,
702 SUM(f.FII_MEASURE29) FII_MEASURE29,
703 SUM(f.FII_MEASURE30) FII_MEASURE30,
704 SUM(f.FII_DIM1) FII_DIM1,
705 SUM(f.FII_DIM2) FII_DIM2,
706 SUM(f.FII_DIM3) FII_DIM3,
707 SUM(f.FII_DIM4) FII_DIM4,
708 SUM(f.FII_DIM5) FII_DIM5,
709 '''||l_url_1||''' FII_ATTRIBUTE3,
710 '''||l_url_2||''' FII_ATTRIBUTE4,
711 '''||l_url_3||''' FII_ATTRIBUTE5,
712 '''||l_url_4||''' FII_ATTRIBUTE7,
713 '''||l_url_5||''' FII_ATTRIBUTE8,
714 '''||l_url_6||''' FII_ATTRIBUTE10,
715 '''||l_url_7||''' FII_ATTRIBUTE11,
716 '''||l_url_8||''' FII_ATTRIBUTE12,
717 SUM(f.FII_CV5) FII_CV5
718 FROM
719 (SELECT id ID,
720 FII_MEASURE1 FII_MEASURE1,
721 FII_MEASURE2 FII_MEASURE2,
722 FII_MEASURE3 FII_MEASURE3,
723 FII_MEASURE7 FII_MEASURE7,
724 FII_MEASURE8 FII_MEASURE8,
725 FII_MEASURE10 FII_MEASURE10,
726 FII_MEASURE11 FII_MEASURE11,
727 FII_MEASURE15 FII_MEASURE15,
728 FII_MEASURE16 FII_MEASURE16,
729 FII_MEASURE17 FII_MEASURE17,
730 FII_MEASURE18 FII_MEASURE18,
731 FII_MEASURE19 FII_MEASURE19,
732 FII_MEASURE20 FII_MEASURE20,
733 SUM(FII_MEASURE1) OVER() FII_MEASURE23,
734 SUM(FII_MEASURE2) OVER() FII_MEASURE24,
735 SUM(FII_MEASURE3) OVER() FII_MEASURE25,
736 SUM(FII_MEASURE7) OVER() FII_MEASURE26,
737 SUM(FII_MEASURE8) OVER() FII_MEASURE27,
738 SUM(FII_MEASURE10) OVER() FII_MEASURE28,
739 SUM(FII_MEASURE11) OVER() FII_MEASURE29,
740 SUM(FII_MEASURE15) OVER() FII_MEASURE30,
741 SUM(FII_MEASURE16) OVER() FII_DIM1,
742 SUM(FII_MEASURE17) OVER() FII_DIM2,
743 SUM(FII_MEASURE18) OVER() FII_DIM3,
744 SUM(FII_MEASURE19) OVER() FII_DIM4,
745 SUM(FII_MEASURE20) OVER() FII_DIM5,
746 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk,
747 (DECODE(SUM(nvl(FII_MEASURE2,0)) over(),0,null,
748 DECODE(SUM(nvl(FII_MEASURE10,0)) over(),0,0,
749 SUM(nvl(FII_MEASURE10,0)) over() / SUM(nvl(FII_MEASURE2,0)) over()))*100) -
750
751 (DECODE(SUM(nvl(FII_MEASURE3,0)) over(),0,null,
752 DECODE(SUM(nvl(FII_MEASURE11,0)) over(),0,0,
753 SUM(nvl(FII_MEASURE11,0)) over()/ SUM(nvl(FII_MEASURE3,0)) over()))*100) FII_CV5 /* Changes made for Bug 3110651 */
754 FROM
755 (SELECT f.'||l_viewby_id||' id,
756 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
757 THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE1,
758 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
759 THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE2,
760 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
761 THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE3,
762 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
763 THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE7,
764 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
765 THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE8,
766 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
767 THEN f.e_invoice_count ELSE 0 END) FII_MEASURE10,
768 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
769 THEN f.e_invoice_count ELSE 0 END) FII_MEASURE11,
770 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
771 THEN f.xml_count ELSE 0 END) FII_MEASURE15,
772 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
773 THEN f.edi_count ELSE 0 END) FII_MEASURE16,
774 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
775 THEN f.ers_count ELSE 0 END) FII_MEASURE17,
776 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
777 THEN f.isp_count ELSE 0 END) FII_MEASURE18,
778 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
779 THEN f.asbn_count ELSE 0 END) FII_MEASURE19,
780 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
781 THEN f.other_integrated_count ELSE 0 END) FII_MEASURE20
782 FROM FII_AP_IVATY_XB_MV f,
783 fii_time_structures cal
784 WHERE f.time_id = cal.time_id
785 AND f.period_type_id = cal.period_type_id
786 '||l_org_where||l_supplier_where||'
787 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
788 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
789 AND f.gid = :GID
790 GROUP by f.'||l_viewby_id||')) f,
791 ('||l_viewby_string||') viewby_dim
792 WHERE f.id = viewby_dim.id
793 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
794 GROUP BY viewby_dim.value,viewby_dim.id
795 &ORDER_BY_CLAUSE';
796
797 /*Section for binding the variables*/
798
799 FII_PMV_Util.bind_variable(
800 p_sqlstmt=>sqlstmt,
801 p_page_parameter_tbl=>p_page_parameter_tbl,
802 p_sql_output=>elec_inv_sql,
803 p_bind_output_table=>elec_inv_output,
804 p_record_type_id=>l_record_type_id,
805 p_gid=>l_gid
806 );
807
808 END get_electronic_inv;
809
810
811
812 /* Holds Activity
813 Procedure get_hold_activity */
814
815
816
817 PROCEDURE get_hold_activity (
818 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
819 get_hold_sql OUT NOCOPY VARCHAR2,
820 get_hold_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
821 IS
822 sqlstmt varchar2(14000);
823 l_num NUMBER;
824 l_viewby_dim VARCHAR2(240); -- what is the viewby
825 l_as_of_date DATE;
826 l_organization VARCHAR2(240);
827 l_supplier VARCHAR2(240);
828 l_currency VARCHAR2(240); -- rANDom size, possibly high
829 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
830 l_record_type_id NUMBER; --
831 l_gid NUMBER; -- 0,4 or 8
832 l_viewby_string VARCHAR2(240);
833 get_hold_rec BIS_QUERY_ATTRIBUTES;
834 l_param_join VARCHAR2(240);
835 l_param_join_ou VARCHAR2(240);
836 l_curr_info VARCHAR2(240);
837 l_curr_suffix VARCHAR2(240);
838 l_prim_curr VARCHAR2(240);
839 l_sec_curr VARCHAR2(240);
840 l_period_type VARCHAR2(240);
841 l_invoice_number VARCHAR2(240);
842 l_column_name VARCHAR2(240);
843 l_table_name VARCHAR2(240);
844 l_org_where VARCHAR2(240);
845 l_supplier_where VARCHAR2(240);
846 l_period_suffix VARCHAR2(240);
847 l_url_1 VARCHAR2(1000);
848
849 BEGIN
850
851 /*getting the parameters values FROM the page parameter table*/
852 FII_PMV_Util.Get_Parameters(
853 p_page_parameter_tbl,
854 l_as_of_date,
855 l_organization,
856 l_supplier,
857 l_invoice_number,
858 l_period_type,
859 l_record_type_id,
860 l_viewby_dim,
861 l_currency,
862 l_viewby_id,
863 l_viewby_string,
864 l_gid,
865 l_org_where,
866 l_supplier_where
867 );
868
869 l_period_suffix:=FII_PMV_UTIL.get_period_type_suffix(l_period_type);
870
871 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
872 l_url_1 := 'pFunctionName=FII_AP_HOLD_ACTIVITY&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
873
874 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
875 l_url_1 := 'pFunctionName=FII_AP_INV_HOLD_ACTIVITY_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_HOLD_ACTIVITY_DTL';
876
877 END IF;
878
879
880
881 /*------------------------------------------------------------------------
882 | VIEWBY -Either supplier or operating unit
883 | VIEWBY_ID -Either supplier_id or org_id
884 | FII_ATTRIBUTE6 Graph Title
885 | FII_MEASURE1 Invoices Placed on Hold Amount
886 | FII_MEASURE2 Prior Invoices Placed on Hold Amount
887 | FII_MEASURE3 Change
888 | FII_MEASURE4 Number of Invoices
889 | FII_MEASURE5 Prior Invoices Placed on Hold
890 | FII_MEASURE6 Change
891 | FII_MEASURE7 Total Number of Days on Hold
892 | FII_MEASURE8 Prior Total Number of Days on Hold
893 | FII_MEASURE9 Average Days on Hold
894 | FII_MEASURE10 Prior Average Days on Hold
895 | FII_MEASURE11 Change
896 | FII_MEASURE12 Number of Holds Placed
897 | FII_MEASURE13 Prior Number of Holds Placed
898 | FII_MEASURE14 Change
899 | FII_MEASURE15 Variance Holds
900 | FII_MEASURE16 % Variance
901 | FII_MEASURE17 PO Matching Holds
902 | FII_MEASURE18 % PO Matching
903 | FII_MEASURE19 Invoice Holds
904 | FII_MEASURE20 % Invoice
905 | FII_MEASURE21 User Defined Holds
906 | FII_MEASURE22 % User Defined
907 | FII_MEASURE23 Other Holds
908 | FII_MEASURE24 % Other
909 | FII_MEASURE25 Grand Total(invoice on hold amt)
910 | FII_MEASURE26 Grand Total(prior invoice on hold amt)
911 | FII_MEASURE27 Grand Total(prior invoice on hold amt)
912 | FII_MEASURE28 Grand Total(prior invoice on hold count)
913 | FII_MEASURE29 Grand Total(invoice days on hold)
914 | FII_MEASURE30 Grand Total(prior invoice days on hold)
915 | FII_DIM1 Grand Total(no of holds placed)
916 | FII_DIM2 Grand Total(prior no of holds placed)
917 | FII_DIM3 Grand Total(variance hold count)
918 | FII_DIM4 Grand Total(PO Matching Holds)
919 | FII_DIM5 Grand Total(User Defined Holds)
920 | FII_DIM7 Grand Total(Other Holds)
921 | FII_DIM8 Grand Total(Invoice Holds)
922 | FII_DIM9 Grand Total(Change Inv placed on Hold amt)
923 | FII_DIM10 Grand Total(Change Inv Placed on Hold)
924 | FII_FSG_COL1 Grand Total(Average days on Hold)
925 | FII_FSG_COL2 Grand Total(Prior Average days on Hold)
926 | FII_FSG_COL3 Grand Total(Change Days on Hold)
927 | FII_FSG_COL4 Grand Total(Change no. of Holds Placed)
928 | FII_FSG_COL5 Grand Total(% Invoice)
929 | FII_FSG_COL6 Grand Total(% User Defined)
930 | FII_FSG_COL7 Grand Total(% Other Holds)
931 | FII_FSG_COL8 Grand Total(% PO Matching)
932 | FII_FSG_COL9 Grand Total(% Variance)
933 | FII_ATTRIBUTE14 Drill (No. of Invoices)
934 | FII_CV1 Amount(Custom View Implementation)
935 | FII_CV2 Number of Invoices(Custom View Implementation)
936 | FII_CV3 Change(Custom View Implementation)
937 | FII_CV4 PO Matching Holds(Custom View Implementation)
938 | FII_CV5 Days on Hold (Custom View Implementation)
939 ----------------------------------------------------------------------------------*/
940
941
942 ----constructing the sql statement
943
944 sqlstmt:= '
945 SELECT viewby_dim.value VIEWBY,
946 viewby_dim.id VIEWBYID,
947 SUM(FII_MEASURE1) FII_MEASURE1,
948 SUM(FII_MEASURE2) FII_MEASURE2,
949 SUM(FII_MEASURE4) FII_MEASURE4,
950 SUM(FII_MEASURE5) FII_MEASURE5,
951 SUM(FII_MEASURE7) FII_MEASURE7,
952 SUM(FII_MEASURE8) FII_MEASURE8,
953 SUM(FII_MEASURE12) FII_MEASURE12,
954 SUM(FII_MEASURE13) FII_MEASURE13,
955 SUM(FII_MEASURE15) FII_MEASURE15,
956 SUM(FII_MEASURE17) FII_MEASURE17,
957 SUM(FII_MEASURE19) FII_MEASURE19,
958 SUM(FII_MEASURE21) FII_MEASURE21,
959 SUM(FII_MEASURE23) FII_MEASURE23,
960 SUM(FII_MEASURE25) FII_MEASURE25,
961 SUM(FII_MEASURE26) FII_MEASURE26,
962 SUM(FII_MEASURE27) FII_MEASURE27,
963 SUM(FII_MEASURE28) FII_MEASURE28,
964 SUM(FII_MEASURE29) FII_MEASURE29,
965 SUM(FII_MEASURE30) FII_MEASURE30,
966 SUM(FII_DIM1) FII_DIM1,
967 SUM(FII_DIM2) FII_DIM2,
968 SUM(FII_DIM3) FII_DIM3,
969 SUM(FII_DIM4) FII_DIM4,
970 SUM(FII_DIM5) FII_DIM5,
971 SUM(FII_DIM7) FII_DIM7,
972 SUM(FII_DIM8) FII_DIM8,
973 '''||l_url_1||''' FII_ATTRIBUTE14
974 FROM
975 (select id,
976 FII_MEASURE1 FII_MEASURE1,
977 FII_MEASURE2 FII_MEASURE2,
978 FII_MEASURE4 FII_MEASURE4,
979 FII_MEASURE5 FII_MEASURE5,
980 FII_MEASURE7 FII_MEASURE7,
981 FII_MEASURE8 FII_MEASURE8,
982 FII_MEASURE12 FII_MEASURE12,
983 FII_MEASURE13 FII_MEASURE13,
984 FII_MEASURE15 FII_MEASURE15,
985 FII_MEASURE17 FII_MEASURE17,
986 FII_MEASURE19 FII_MEASURE19,
987 FII_MEASURE21 FII_MEASURE21,
988 FII_MEASURE23 FII_MEASURE23,
989 SUM(FII_MEASURE1) OVER() FII_MEASURE25,
990 SUM(FII_MEASURE2) OVER() FII_MEASURE26,
991 SUM(FII_MEASURE4) OVER() FII_MEASURE27,
992 SUM(FII_MEASURE5) OVER() FII_MEASURE28,
993 SUM(FII_MEASURE7) OVER() FII_MEASURE29,
994 SUM(FII_MEASURE8) OVER() FII_MEASURE30,
995 SUM(FII_MEASURE12) OVER() FII_DIM1,
996 SUM(FII_MEASURE13) OVER() FII_DIM2,
997 SUM(FII_MEASURE15) OVER() FII_DIM3,
998 SUM(FII_MEASURE17) OVER() FII_DIM4,
999 SUM(FII_MEASURE21) OVER() FII_DIM5,
1000 SUM(FII_MEASURE23) OVER() FII_DIM7,
1001 SUM(FII_MEASURE19) OVER() FII_DIM8,
1002 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
1003 from
1004 (select id,
1005 SUM(FII_MEASURE1) FII_MEASURE1,
1006 SUM(FII_MEASURE2) FII_MEASURE2,
1007 SUM(FII_MEASURE4) FII_MEASURE4,
1008 SUM(FII_MEASURE5) FII_MEASURE5,
1009 SUM(FII_MEASURE7) FII_MEASURE7,
1010 SUM(FII_MEASURE8) FII_MEASURE8,
1011 SUM(FII_MEASURE12) FII_MEASURE12,
1012 SUM(FII_MEASURE13) FII_MEASURE13,
1013 SUM(FII_MEASURE15) FII_MEASURE15,
1014 SUM(FII_MEASURE17) FII_MEASURE17,
1015 SUM(FII_MEASURE19) FII_MEASURE19,
1016 SUM(FII_MEASURE21) FII_MEASURE21,
1017 SUM(FII_MEASURE23) FII_MEASURE23
1018 from
1019 (SELECT f.'||l_viewby_id||' id,
1020 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1021 THEN f.inv_on_hold_amt'||l_period_suffix||l_currency||' ELSE 0 END) FII_MEASURE1,
1022 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1023 THEN f.inv_on_hold_amt'||l_period_suffix||l_currency||' ELSE 0 END) FII_MEASURE2,
1024 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1025 THEN f.inv_on_hold_count'||l_period_suffix||' ELSE 0 END) FII_MEASURE4,
1026 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1027 THEN f.inv_on_hold_count'||l_period_suffix||' ELSE 0 END) FII_MEASURE5,
1028 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1029 THEN f.days_on_hold'||l_period_suffix||' ELSE 0 END) FII_MEASURE7,
1030 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1031 THEN f.days_on_hold'||l_period_suffix||' ELSE 0 END) FII_MEASURE8,
1032 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1033 THEN f.no_of_holds_placed ELSE 0 END) FII_MEASURE12,
1034 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1035 THEN f.no_of_holds_placed ELSE 0 END) FII_MEASURE13,
1036 0 FII_MEASURE15,
1037 0 FII_MEASURE17,
1038 0 FII_MEASURE19,
1039 0 FII_MEASURE21,
1040 0 FII_MEASURE23
1041 FROM FII_AP_HATY_XB_MV f,
1042 fii_time_structures cal
1043 WHERE f.time_id = cal.time_id
1044 AND f.period_type_id = cal.period_type_id
1045 '||l_org_where||l_supplier_where||'
1046 AND bitAND(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
1047 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
1048 AND f.gid = :GID
1049 GROUP BY f.'||l_viewby_id||'
1050 UNION ALL
1051 SELECT f.'||l_viewby_id||' id,
1052 0 FII_MEASURE1,
1053 0 FII_MEASURE2,
1054 0 FII_MEASURE4,
1055 0 FII_MEASURE5,
1056 0 FII_MEASURE7,
1057 0 FII_MEASURE8,
1058 0 FII_MEASURE12,
1059 0 FII_MEASURE13,
1060 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1061 THEN f.variance_hold_count ELSE 0 END) FII_MEASURE15,
1062 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1063 THEN f.po_matching_hold_count ELSE 0 END) FII_MEASURE17,
1064 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1065 THEN f.invoice_hold_count ELSE 0 END) FII_MEASURE19,
1066 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1067 THEN f.user_defined_hold_count ELSE 0 END) FII_MEASURE21,
1068 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
1069 THEN f.other_hold_count ELSE 0 END) FII_MEASURE23
1070 FROM FII_AP_HCAT_IB_MV f,
1071 fii_time_structures cal
1072 WHERE f.time_id = cal.time_id
1073 AND f.period_type_id = cal.period_type_id
1074 '||l_org_where||l_supplier_where||'
1075 AND bitAND(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
1076 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
1077 AND f.hold_release_flag=''H'' /*added for bug no.3096078*/
1078 AND f.gid = :GID
1079 GROUP BY f.'||l_viewby_id||')
1080 group by ID)) f,
1081 ('||l_viewby_string||') viewby_dim
1082 WHERE f.id = viewby_dim.id
1083 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
1084 GROUP BY viewby_dim.value,viewby_dim.id
1085 &ORDER_BY_CLAUSE';
1086
1087
1088 /*Section for binding the variables*/
1089
1090
1091 FII_PMV_Util.bind_variable(
1092 p_sqlstmt=>sqlstmt,
1093 p_page_parameter_tbl=>p_page_parameter_tbl,
1094 p_sql_output=>get_hold_sql,
1095 p_bind_output_table=>get_hold_output,
1096 p_record_type_id=>l_record_type_id,
1097 p_gid=>l_gid
1098 );
1099
1100
1101 END get_hold_activity;
1102
1103 /* Creating a Procedure for the Electronic Invoice trend report */
1104
1105 PROCEDURE Local_Bind_Variable
1106 (p_sqlstmt IN Varchar2,
1107 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1108 p_sql_output OUT NOCOPY Varchar2,
1109 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
1110 p_record_type_id IN Number Default Null,
1111 p_view_by IN Varchar2 Default Null,
1112 p_gid IN Number Default Null,
1113 p_period_start IN Date Default null,
1114 p_report_start IN Date Default null,
1115 p_cur_effective_num IN Number Default Null,
1116 p_period_id IN Number Default Null
1117 ) IS
1118 l_bind_rec BIS_QUERY_ATTRIBUTES;
1119
1120 BEGIN
1121 p_bind_output_table := BIS_QUERY_ATTRIBUTES_TBL();
1122 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1123 p_sql_output := p_sqlstmt;
1124 p_bind_output_table.EXTEND;
1125 l_bind_rec.attribute_name := ':VIEW_BY';
1126 l_bind_rec.attribute_value := to_char(p_view_by);
1127 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1128 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1129 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1130 p_bind_output_table.EXTEND;
1131 l_bind_rec.attribute_name := ':RECORD_TYPE_ID';
1132 l_bind_rec.attribute_value := to_char(p_record_type_id);
1133 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1134 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1135 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1136 p_bind_output_table.EXTEND;
1137 l_bind_rec.attribute_name := ':GID';
1138 l_bind_rec.attribute_value := to_char(p_gid);
1139 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1140 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1141 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1142 p_bind_output_table.EXTEND;
1143 l_bind_rec.attribute_name := ':SEC_ID';
1144 l_bind_rec.attribute_value := fii_pmv_util.get_sec_profile;
1145 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1146 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1147 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1148 p_bind_output_table.EXTEND;
1149 l_bind_rec.attribute_name := ':PERIOD_START';
1150 l_bind_rec.attribute_value := to_char(p_period_start, 'DD-MM-YYYY');
1151 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1152 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1153 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1154 p_bind_output_table.EXTEND;
1155 l_bind_rec.attribute_name := ':P_REPORT_START';
1156 l_bind_rec.attribute_value := to_char(p_report_start, 'DD-MM-YYYY');
1157 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1158 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1159 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1160 p_bind_output_table.EXTEND;
1161 l_bind_rec.attribute_name := ':P_CUR_EFFECTIVE_NUM';
1162 l_bind_rec.attribute_value := TO_CHAR(p_cur_effective_num);
1163 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1164 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1165 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1166 p_bind_output_table.EXTEND;
1167 l_bind_rec.attribute_name := ':P_PERIOD_ID';
1168 l_bind_rec.attribute_value := to_char(p_period_id);
1169 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1170 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1171 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1172 p_bind_output_table.EXTEND;
1173 --vkazhipu added for performance
1174 --bug 4997442
1175 l_bind_rec.attribute_name := ':P_DATE_STRING';
1176 l_bind_rec.attribute_value := to_char(g_date_string,'DD-MM-YYYY');
1177 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1178 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1179 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1180 p_bind_output_table.EXTEND;
1181
1182 END Local_Bind_Variable;
1183
1184 /* Creating Procedure that fetches the period details for the Electronic Invoice Trend reports */
1185
1186 PROCEDURE get_period_details(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1187 p_period_start OUT NOCOPY Date,
1188 p_cur_period OUT NOCOPY Number,
1189 p_id_column OUT NOCOPY Varchar2,
1190 p_report_start OUT NOCOPY DATE,
1191 p_cur_effective_num OUT NOCOPY number,
1192 p_period_id OUT NOCOPY number)
1193 IS
1194 l_as_of_date DATE;
1195 i NUMBER;
1196 l_period_type VARCHAR2(2000);
1197 l_p_as_of_date DATE;
1198 l_start_date DATE;
1199 BEGIN
1200 IF (p_page_parameter_tbl.count > 0) THEN
1201 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1202 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
1203 l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
1204 END IF;
1205 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
1206 l_period_type := p_page_parameter_tbl(i).parameter_value;
1207 END IF;
1208 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_WEEK_FROM' THEN
1209 p_cur_period := p_page_parameter_tbl(i).parameter_id;
1210 p_id_column := 'week_id';
1211 END IF;
1212 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
1213 p_cur_period := p_page_parameter_tbl(i).parameter_id;
1214 p_id_column := 'ent_period_id';
1215 END IF;
1216 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_ENT_QTR_FROM' THEN
1217 p_cur_period := p_page_parameter_tbl(i).parameter_id;
1218 p_id_column := 'ent_qtr_id';
1219 END IF;
1220 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
1221 p_cur_period := p_page_parameter_tbl(i).parameter_id;
1222 p_id_column := 'ent_year_id';
1223 END IF;
1224 END LOOP;
1225 END IF;
1226
1227 select nvl(min(start_date), trunc(sysdate)) into l_start_date from fii_time_ent_year;
1228
1229 CASE l_period_type
1230 WHEN 'FII_TIME_WEEK' THEN
1231 p_period_id := 16;
1232 select nvl(fii_time_api.pwk_end(l_as_of_date-91) +1, l_start_date) into p_report_start from dual;
1233 select nvl(fii_time_api.pwk_end(l_as_of_date) +1, l_start_date-1) into p_period_start from dual;
1234 select sequence into p_cur_effective_num
1235 from fii_time_week
1236 where l_as_of_date between start_date and end_date;
1237 WHEN 'FII_TIME_ENT_PERIOD' THEN
1238 p_period_id := 32;
1239 select nvl(fii_time_api.ent_lysper_end(l_as_of_date), l_start_date-1) into p_report_start from dual;
1240 select nvl(fii_time_api.ent_cper_start(l_as_of_date), l_start_date) into p_period_start from dual;
1241 select sequence into p_cur_effective_num
1242 from fii_time_ent_period
1243 where l_as_of_date between start_date and end_date;
1244 WHEN 'FII_TIME_ENT_QTR' THEN
1245 p_period_id := 64;
1246 select nvl(fii_time_api.ent_lysqtr_end(l_as_of_date), l_start_date-1) into p_report_start from dual;
1247 select nvl(fii_time_api.ent_cqtr_start(l_as_of_date), l_start_date) into p_period_start from dual;
1248
1249 select sequence into p_cur_effective_num
1250 from fii_time_ent_qtr
1251 where l_as_of_date between start_date and end_date;
1252 WHEN 'FII_TIME_ENT_YEAR' THEN
1253 p_period_id := 128;
1254 --p_report_start := fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_as_of_date))));
1255
1256 select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_as_of_date)))),l_start_date-1)
1257 into p_report_start from dual; /* Bug 3325387 */
1258
1259 select nvl(fii_time_api.ent_cyr_start(l_as_of_date), l_start_date) into p_period_start from dual;
1260 select sequence into p_cur_effective_num
1261 from fii_time_ent_year
1262 where l_as_of_date between start_date and end_date;
1263 END CASE;
1264
1265 END get_period_details;
1266
1267 /* Electronic Invoice trend Report */
1268
1269 PROCEDURE get_electronic_inv_trend (
1270 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1271 electronic_inv_trend_sql OUT NOCOPY VARCHAR2,
1272 electronic_inv_trend_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1273 IS
1274 l_viewby_dim VARCHAR2(240); -- what is the viewby
1275 l_as_of_date DATE;
1276 l_organization VARCHAR2(240);
1277 l_supplier VARCHAR2(240);
1278 l_currency VARCHAR2(240); -- random size, possibly high
1279 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
1280 l_record_type_id NUMBER; --
1281 l_gid NUMBER; -- 0,4 or 8
1282 l_viewby_string VARCHAR2(240);
1283 electronic_inv_trend_rec BIS_QUERY_ATTRIBUTES;
1284 l_param_join VARCHAR2(240);
1285 l_cur_period NUMBER;
1286 l_id_column VARCHAR2(100);
1287 sqlstmt VARCHAR2(14000);
1288 l_invoice_number VARCHAR2(240);
1289 l_org_where VARCHAR2(240);
1290 l_supplier_where VARCHAR2(240);
1291 l_period_type VARCHAR2(1000);
1292 l_period_start DATE;
1293 l_report_start DATE;
1294 l_cur_effective_num NUMBER;
1295 l_period_id NUMBER;
1296 l_url_1 VARCHAR2(1000);
1297 l_url_2 VARCHAR2(1000);
1298 -- l_date VARCHAR2(1000);
1299 l_date_mask VARCHAR2(240);
1300 --l_count NUMBER;
1301 l_status VARCHAR2(30);
1302 l_industry VARCHAR2(30);
1303 l_fii_schema VARCHAR2(30);
1304 l_as_of_date_2 VARCHAR2(50);
1305 l_as_of_date_3 DATE;
1306
1307
1308 BEGIN
1309 FII_PMV_Util.Get_Parameters(
1310 p_page_parameter_tbl,
1311 l_as_of_date,
1312 l_organization,
1313 l_supplier,
1314 l_invoice_number,
1315 l_period_type,
1316 l_record_type_id,
1317 l_viewby_dim,
1318 l_currency,
1319 l_viewby_id,
1320 l_viewby_string,
1321 l_gid,
1322 l_org_where,
1323 l_supplier_where
1324 );
1325
1326 get_period_details(p_page_parameter_tbl,
1327 l_period_start,
1328 l_cur_period,
1329 l_id_column,
1330 l_report_start,
1331 l_cur_effective_num,
1332 l_period_id );
1333
1334 FII_PMV_Util.get_format_mask(l_date_mask);
1335
1336 l_as_of_date_2 := to_char(l_as_of_date,'DD/MM/YYYY');
1337 l_as_of_date_3 := to_date(l_as_of_date_2,'DD/MM/YYYY');
1338
1339 /* As part of bug 3497818 we check if the table FII_AR_SALES_CREDITS is present. If it is present then we need
1340 to use the new logic of populating the urls which is available in 11.5.10 env so as to avoid the security concern
1341 arising due to using of Drill across package.
1342 For 11.5.9 environments we will be using the same old logic of using the drill across package for passing the dates
1343 In 11.5.9 environments the FII_AR_SALES_CREDITS table will not exist and hence this test will suffice.*/
1344
1345 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema))
1346 THEN NULL;
1347 END IF;
1348
1349 /* Commented out by VKAZHIPU, since FII_AP_DRILL_ACROSS Package is not used for drill down */
1350 /* bug 4568962 */
1351
1352 /*select count(*) into l_count from all_tables where table_name = 'FII_AR_SALES_CREDITS' and
1353 rownum = 1 and owner = l_fii_schema; */
1354
1355 /* Commented out by VKAZHIPU, since FII_AP_DRILL_ACROSS Package is not used for drill down */
1356 /* bug 4568962 */
1357
1358 --IF l_count = 0 THEN
1359
1360
1361 /* changed code below to implement drill as per bug no.3044393*/
1362 -- IF l_organization <> 'All' and l_supplier <> 'All' then
1363 -- l_url_1 := 'pFunctionName=FII_AP_E_INV_ENT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1364 -- CASE l_period_type
1365
1366 -- WHEN 'FII_TIME_ENT_PERIOD' THEN
1367 -- l_url_1 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||tcur.end_date||''&pPeriod=FII_TIME_ENT_PERIOD&pParamIds=Y';
1368 -- l_url_2 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||&BIS_CURRENT_ASOF_DATE||''&pPeriod=FII_TIME_ENT_PERIOD&pParamIds=Y';
1369 -- l_date := 'fii_time_api.ent_cper_end(&BIS_CURRENT_ASOF_DATE)';
1370 -- WHEN 'FII_TIME_ENT_YEAR' THEN
1371 -- l_url_1 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||tcur.end_date||''&pPeriod=FII_TIME_ENT_YEAR&pParamIds=Y';
1372 -- l_url_2 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||&BIS_CURRENT_ASOF_DATE||''&pPeriod=FII_TIME_ENT_YEAR&pParamIds=Y';
1373 -- l_date :='fii_time_api.ent_cyr_end(&BIS_CURRENT_ASOF_DATE)';
1374 -- WHEN 'FII_TIME_ENT_QTR' THEN
1375 -- l_url_1 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||tcur.end_date||''&pPeriod=FII_TIME_ENT_QTR&pParamIds=Y';
1376 -- l_url_2 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||&BIS_CURRENT_ASOF_DATE||''&pPeriod=FII_TIME_ENT_QTR&pParamIds=Y';
1377 -- l_date :='fii_time_api.ent_cqtr_end(&BIS_CURRENT_ASOF_DATE)';
1378 -- WHEN 'FII_TIME_WEEK' THEN
1379 -- l_url_1 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||enddate||''&pPeriod=FII_TIME_WEEK&pParamIds=Y';
1380 -- l_url_2 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_E_INV_ENT_DTL&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=''||&BIS_CURRENT_ASOF_DATE||''&pPeriod=FII_TIME_WEEK&pParamIds=Y';
1381 -- END CASE;
1382 -- ELSE
1383 -- l_url_1 := '';
1384 -- l_url_2 := '';
1385 g_date_string := fii_time_api.ent_cper_end(l_as_of_date_3);
1386 -- END IF;
1387
1388 -- ELSE
1389
1390
1391 /*changed code below to implement drill as per bug no.3044393*/
1392 IF l_organization <> 'All' and l_supplier <> 'All' then
1393
1394 CASE l_period_type
1395
1396 WHEN 'FII_TIME_ENT_PERIOD' THEN
1397 l_url_1 := 'AS_OF_DATE=''||drill_date||''&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1398 l_url_2 := 'AS_OF_DATE='||l_as_of_date_2||'&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1399 g_date_string := fii_time_api.ent_cper_end(l_as_of_date_3);
1400
1401
1402 WHEN 'FII_TIME_ENT_YEAR' THEN
1403 l_url_1 := 'AS_OF_DATE=''||drill_date||''&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1404 l_url_2 := 'AS_OF_DATE='||l_as_of_date_2||'&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1405 g_date_string :=fii_time_api.ent_cyr_end(l_as_of_date_3);
1406
1407
1408 WHEN 'FII_TIME_ENT_QTR' THEN
1409 l_url_1 := 'AS_OF_DATE=''||drill_date||''&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1410 l_url_2 := 'AS_OF_DATE='||l_as_of_date_2||'&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1411 g_date_string :=fii_time_api.ent_cqtr_end(l_as_of_date_3);
1412
1413
1414 WHEN 'FII_TIME_WEEK' THEN
1415
1416 l_url_1 := 'AS_OF_DATE=''||drill_date||''&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1417 l_url_2 := 'AS_OF_DATE='||l_as_of_date_2||'&pFunctionName=FII_AP_E_INV_ENT_DTL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_E_INV_ENT_DTL';
1418 g_date_string := fii_time_api.cwk_end(l_as_of_date_3);
1419
1420 END CASE;
1421 ELSE
1422 l_url_1 := '';
1423 l_url_2 := '';
1424 g_date_string := fii_time_api.ent_cper_end(l_as_of_date_3);
1425 END IF;
1426
1427
1428 --END IF;
1429
1430 /*--------------------------------------------------------+
1431 | VIEWBY - Either Operating Unit / Supplier |
1432 | VIEWBYID - Either org_id / supplier_id |
1433 | FII_MEASURE1 - Period to Date |
1434 | FII_MEASURE2 - Total Invoices Entered |
1435 | FII_MEASURE3 - Electronic Invoices Entered |
1436 | FII_MEASURE4 - % Electronic Invoices |
1437 | FII_MEASURE5 - Electronic Invoice Amount |
1438 +-------------------------------------------------------*/
1439
1440 ----constructing the sql statement
1441 /* changed code below to implement drill as per bug no.3044393.Passing the END DATE in case of WEEK explicitly which will be last date displayed
1442 in the trend report so we have divided the sql in 2 parts One for the WEEK and second to handle all others.*/
1443 CASE l_period_type
1444 WHEN 'FII_TIME_WEEK' then
1445
1446 sqlstmt := '
1447 SELECT
1448 (case when FII_MEASURE1 = fii_time_api.cwk_end(&BIS_CURRENT_ASOF_DATE) then to_char(&BIS_CURRENT_ASOF_DATE) else FII_MEASURE1 end) FII_MEASURE1,
1449 FII_MEASURE2,
1450 FII_MEASURE3,
1451 FII_MEASURE5,
1452 (CASE WHEN (enddate-fii_time_api.cwk_end(&BIS_CURRENT_ASOF_DATE)) = 0 then '''||l_url_2||''' else '''||l_url_1||''' END ) FII_ATTRIBUTE1
1453
1454 FROM(
1455 SELECT
1456 tcur.end_date enddate,
1457 name FII_MEASURE1,
1458 inline_view.invoice_entered FII_MEASURE2,
1459 inline_view.invoice_count FII_MEASURE3,
1460 inline_view.invoice_amt FII_MEASURE5,
1461 to_char(tcur.end_date,''DD/MM/YYYY'') drill_date
1462
1463 FROM
1464 (
1465
1466 SELECT
1467 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
1468 SUM(invoice_count_entered) invoice_entered ,
1469 SUM(e_invoice_count) invoice_count,
1470 SUM(e_invoice_amt) invoice_amt
1471
1472 FROM
1473 (
1474 SELECT
1475 t.sequence FII_SEQUENCE,
1476 f.invoice_count_entered invoice_count_entered,
1477 f.e_invoice_count e_invoice_count,
1478 f.e_invoice_amt'||l_currency||' e_invoice_amt
1479
1480 FROM FII_AP_IVATY_XB_MV f,
1481 '||l_period_type||' t
1482 WHERE
1483 f.gid = :GID
1484 AND f.time_id = t.'||l_id_column||'
1485 AND f.period_type_id = :P_PERIOD_ID
1486 AND t.end_date between to_date(:P_REPORT_START,''DD-MM-YYYY'') AND to_date(:PERIOD_START,''DD-MM-YYYY'')
1487 '||l_org_where||l_supplier_where||'
1488 UNION ALL
1489
1490 SELECT
1491 :P_CUR_EFFECTIVE_NUM FII_SEQUENCE,
1492 f.invoice_count_entered invoice_count_entered,
1493 f.e_invoice_count e_invoice_count,
1494 f.e_invoice_amt'||l_currency||' e_invoice_amt
1495
1496 FROM FII_AP_IVATY_XB_MV f,
1497 fii_time_structures cal
1498
1499 WHERE
1500 f.gid = :GID
1501 AND f.period_type_id = cal.period_type_id
1502 AND f.time_id = cal.time_id
1503 AND bitand(cal.record_type_id,:RECORD_TYPE_ID)= :RECORD_TYPE_ID
1504 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
1505 '||l_org_where||l_supplier_where||'
1506
1507 ) inner_inline_view
1508 GROUP BY inner_inline_view.FII_SEQUENCE
1509 ) inline_view,
1510 '||l_period_type||' tcur
1511 WHERE inline_view.fii_effective_num (+)= tcur.sequence
1512 AND tcur.start_date > to_date(:P_REPORT_START,''DD-MM-YYYY'')
1513 AND tcur.start_date <= &BIS_CURRENT_ASOF_DATE
1514 ORDER BY tcur.start_date)';
1515
1516 ELSE
1517
1518 sqlstmt := '
1519 SELECT
1520 name FII_MEASURE1,
1521 inline_view.invoice_entered FII_MEASURE2,
1522 inline_view.invoice_count FII_MEASURE3,
1523 inline_view.invoice_amt FII_MEASURE5,
1524 (CASE WHEN (tcur.end_date-:P_DATE_STRING) = 0 then '''||l_url_2||''' else '''||l_url_1||''' END ) FII_ATTRIBUTE1
1525
1526 FROM
1527 (
1528 SELECT
1529 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
1530 SUM(invoice_count_entered) invoice_entered ,
1531 SUM(e_invoice_count) invoice_count,
1532 SUM(e_invoice_amt) invoice_amt,
1533 drill_date drill_date
1534 FROM
1535 (
1536 SELECT
1537 t.sequence FII_SEQUENCE,
1538 f.invoice_count_entered invoice_count_entered,
1539 f.e_invoice_count e_invoice_count,
1540 f.e_invoice_amt'||l_currency||' e_invoice_amt,
1541 to_char(t.end_date,''DD/MM/YYYY'') drill_date
1542
1543 FROM FII_AP_IVATY_XB_MV f,
1544 '||l_period_type||' t
1545 WHERE
1546 f.gid = :GID
1547 AND f.time_id = t.'||l_id_column||'
1548 AND f.period_type_id = :P_PERIOD_ID
1549 AND t.end_date > to_date(:P_REPORT_START,''DD-MM-YYYY'') /*Changed for bug no.3069214*/
1550 AND t.end_date < to_date(:PERIOD_START,''DD-MM-YYYY'')
1551 '||l_org_where||l_supplier_where||'
1552
1553 UNION ALL
1554
1555 SELECT
1556 :P_CUR_EFFECTIVE_NUM FII_SEQUENCE,
1557 f.invoice_count_entered invoice_count_entered,
1558 f.e_invoice_count e_invoice_count,
1559 f.e_invoice_amt'||l_currency||' e_invoice_amt,
1560 to_char(cal.report_date,''DD/MM/YYYY'') drill_date
1561 FROM FII_AP_IVATY_XB_MV f,
1562 fii_time_structures cal
1563
1564 WHERE
1565 f.gid = :GID
1566 AND f.period_type_id = cal.period_type_id
1567 AND f.time_id = cal.time_id
1568 AND bitand(cal.record_type_id,:RECORD_TYPE_ID)= :RECORD_TYPE_ID
1569 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
1570 '||l_org_where||l_supplier_where||'
1571
1572 ) inner_inline_view
1573 GROUP BY inner_inline_view.FII_SEQUENCE, drill_date
1574 ) inline_view,
1575 '||l_period_type||' tcur
1576 WHERE inline_view.fii_effective_num (+)= tcur.sequence
1577 AND tcur.start_date > to_date(:P_REPORT_START,''DD-MM-YYYY'')
1578 AND tcur.start_date <= &BIS_CURRENT_ASOF_DATE
1579 ORDER BY tcur.start_date';
1580 END CASE;
1581
1582 Local_Bind_Variable(
1583 p_sqlstmt=>sqlstmt,
1584 p_page_parameter_tbl=>p_page_parameter_tbl,
1585 p_sql_output=>electronic_inv_trend_sql,
1586 p_bind_output_table=>electronic_inv_trend_output,
1587 p_record_type_id=>l_record_type_id,
1588 p_gid=>l_gid,
1589 p_period_start=>l_period_start,
1590 p_report_start => l_report_start,
1591 p_cur_effective_num => l_cur_effective_num,
1592 p_period_id => l_period_id
1593 );
1594 END get_electronic_inv_trend;
1595
1596 END fii_ap_inv_activity;