[Home] [Help]
PACKAGE BODY: APPS.IBE_REPORTING_PVT
Source
1 PACKAGE BODY IBE_REPORTING_PVT AS
2 /* $Header: IBEVECRB.pls 120.2 2005/09/14 03:07:57 appldev ship $ */
3 l_debug VARCHAR2(1) := 'N';
4 l_debug_profile VARCHAR2(1) := 'N';
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_REPORTING_PKG';
7
8 g_conversionType FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
9 g_CurrencyCode FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
10 g_periodSetName FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
11 g_ForceRefreshFlag Varchar2(1);
12 g_QuarterBeginFlag Varchar2(1);
13 g_YearlyDataFlag Varchar2(1);
14 g_ParallelFlag Varchar2(1);
15 g_dayoffset Number;
16 g_TruncateFlag Varchar2(1);
17 g_RefreshMV Varchar2(1);
18
19
20 g_inputRefreshMode Varchar2(50) := 'COMPLETE';
21 g_inputBeginDate Varchar2(50);
22 g_inputEndDate Varchar2(50);
23 g_debugFlag Varchar2(2) := 'Y';
24
25
26 g_maxRows Number;
27 g_data_source Varchar2(2000);
28 g_refreshSysDate Date;
29
30 g_idx_tablespace Varchar2(30);
31
32 g_setupErrorCount Number := 0;
33
34 g_maxamount_ceil Number := 15000000;
35
36 g_error_threshhold Number := 25;
37
38 IBE_ECR_SETUP_ERROR Exception;
39 PRAGMA EXCEPTION_INIT(IBE_ECR_SETUP_ERROR,-20200);
40
41 IBE_ECR_CALENDER_ERROR Exception;
42 PRAGMA EXCEPTION_INIT(IBE_ECR_CALENDER_ERROR,-20201);
43
44 IBE_ECR_PROFILE_ERROR Exception;
45 PRAGMA EXCEPTION_INIT(IBE_ECR_PROFILE_ERROR,-20202);
46
47 IBE_ECR_CONVERSION_ERROR Exception;
48 PRAGMA EXCEPTION_INIT(IBE_ECR_CONVERSION_ERROR,-20204);
49
50 IBE_ECR_RATES_MISSING_ERR Exception;
51 PRAGMA EXCEPTION_INIT(IBE_ECR_RATES_MISSING_ERR,-20205);
52
53 Cursor c_lookup(ptype IN Varchar2) IS
54 Select LookUp_Code,Meaning
55 From Fnd_Lookups
56 Where Lookup_Type = ptype;
57
58 Cursor c_currency(ptype IN Varchar2) IS
59 Select LookUp_Code
60 From Fnd_Lookups
61 Where Lookup_Type = ptype;
62
63
64 PROCEDURE printDebugLog(pDebugStmt Varchar2) IS
65 BEGIN
66
67 -- Debug Procedure
68 -- Y : Display Debug in the Conc. Program Log.
69 -- N: No Debug Statement
70
71 If g_debugFlag = 'Y' Then
72 FND_FILE.PUT_LINE(FND_FILE.LOG,pDebugStmt);
73 END IF;
74
75 IF (l_debug_profile = 'Y') THEN
76 IBE_UTIL.debug(pDebugStmt);
77 END IF;
78
79 End printDebugLog;
80
81 PROCEDURE printOutput(pMessage Varchar2) IS
82 l_printTimeStamp Varchar2(25):= NULL;
83 BEGIN
84
85
86 IF Substr(pMessage,1,1) <> '+' Then
87 l_printTimeStamp := to_char(sysdate,'RRRR/MM/DD HH:MI:SS')||' ';
88 End If;
89
90 If FND_GLOBAL.user_id > -1 Then
91 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_printTimeStamp||pMessage);
92 End If;
93
94 END printOutput;
95
96
97
98 function get_item_count(inventory_item_id in number,header_id in number) return number is
99 v_inventory_item_id number;
100 v_result number default 0;
101
102
103 cursor get_count(l_inventory_item_id number,l_header_id number) is
104 select count(distinct nvl(oh.order_number,0))
105 from oe_order_lines_all ol , oe_order_headers_all oh
106 WHERE oh.header_id = l_header_id and
107 oh.header_id = ol.header_id and
108 nvl(OH.cancelled_flag,'N') = 'N' and
109 nvl(oh.booked_flag,'N')= 'Y' and
110 ol.inventory_item_id = l_inventory_item_id and
111 nvl(ol.cancelled_flag,'N')= 'N' and
112 nvl(ol.booked_flag,'N')= 'Y' and
113 link_to_line_id is NULL ;
114
115 BEGIN
116
117 open get_count(inventory_item_id,header_id);
118 fetch get_count into v_result;
119 close get_count;
120 return(TO_NUMBER(v_result));
121 EXCEPTION
122 WHEN OTHERS THEN
123 return(0);
124 END;
125
126
127 FUNCTION getMessage(pOwner IN Varchar2,pName IN Varchar2) Return Varchar2
128
129 IS
130 l_Message Varchar2(2000);
131
132 BEGIN
133 FND_MESSAGE.Set_Name(pOwner,pName);
134 l_Message := FND_MESSAGE.get;
135 Return l_Message;
136 END getMessage;
137
138 FUNCTION getMessage(pOwner IN Varchar2,pName IN Varchar2,ptokenValue IN Varchar2) Return Varchar2
139
140 IS
141
142 l_Message Varchar2(2000);
143
144 BEGIN
145 FND_MESSAGE.Set_Name(pOwner,pName);
146 FND_MESSAGE.Set_Token('NAME',ptokenValue);
147 l_Message := FND_MESSAGE.get;
148 Return l_Message;
149 END getMessage;
150
151
152 PROCEDURE getTableSpace(ptype IN Varchar2,pTabSpace OUT NOCOPY Varchar2) IS
153 Begin
154 IF (l_debug = 'Y') THEN
155 printDebugLog('getTableSpace(+)');
156 printDebugLog(fnd_global.tab||'IN Parameters: '||ptype);
157 END IF;
158 If ptype = 'INDEX' Then
159 select i.index_tablespace Into pTabSpace
160 from fnd_product_installations i, fnd_application a, fnd_oracle_userid u
161 where a.application_short_name = 'IBE'
162 and a.application_id = i.application_id
163 and u.oracle_id = i.oracle_id;
164 End If;
165 IF (l_debug = 'Y') THEN
166 printDebugLog(fnd_global.tab||'OUT Parameters: '||pTabSpace);
167 printDebugLog('getTableSpace(-)');
168 END IF;
169 End getTableSpace;
170
171 PROCEDURE printParameterOut(pMode IN Varchar2,pBeginDate IN Varchar2,pEndDate IN Varchar2,pDayOffset IN Varchar2) IS
172 BEGIN
173
174 printOutput('*** '||getMessage('IBE','IBE_ECR_INPUT_TITLE')||' ***');
175 printOutput(getMessage('IBE','IBE_ECR_REFRESH_MODE')||': '||pMode);
176 printOutput(getMessage('IBE','IBE_ECR_BEGIN_DATE')||': '||pBeginDate);
177 printOutput(getMessage('IBE','IBE_ECR_END_DATE')||': '||pEndDate);
178 -- printOutput(getMessage('IBE','IBE_ECR_DAYOFFSET')||': '||pdayOffset);
179
180 END printParameterOut;
181
182
183 PROCEDURE printProfileOut IS
184
185 BEGIN
186
187 printOutput('*** '||getMessage('IBE','IBE_ECR_PROFILE_TITLE')||' ***');
188 printOutput('IBE_ECR_PERIOD_SET_NAME:' ||nvl(g_PeriodSetName,'ORA-20202: '|| getMessage('IBE','IBE_ECR_PROFILE_VALUE','IBE_ECR_PERIOD_SET_NAME')));
189 printOutput('IBE_GL_CONVERSION_TYPE: ' || nvl(g_conversionType,'ORA-20202: '||getMessage('IBE','IBE_ECR_PROFILE_VALUE','IBE_GL_CONVERSION_TYPE')));
190 printOutput('IBE_CURRENCY_CODE: ' || nvl(g_currencyCode,'ORA-20202: '||getMessage('IBE','IBE_ECR_PROFILE_VALUE','IBE_CURRENCY_CODE')));
191 printOutput('IBE_ECR_FORCE_REFRESH: '||g_ForceRefreshFlag);
192 printOutput('IBE_ENABLE_PARALLEL_PROCESSING: '||g_parallelFlag);
193 printOutput('IBE_ECR_QUARTER_BEGIN_DATA: '||g_QuarterBeginFlag);
194 printOutput('IBE_ECR_YEARLY_DATA: '||g_YearlyDataFlag);
195 printOutput('IBE_TRUNC_RECORDS: '||g_TruncateFlag);
196
197 END printProfileOut;
198
199 PROCEDURE printBinFreqDateOut(pBinRefreshDate IN Date) IS
200
201 l_StartDate Date;
202 l_EndDate Date;
203
204 BEGIN
205 printOutput('*** '||getMessage('IBE','IBE_ECR_BIN_FREQ_DATE')||' ***');
206 For rec_lookup IN c_lookup('IBE_BIN_FREQUENCY') Loop
207 Begin
208 getFrequencyDate(pBinRefreshDate,rec_lookup.lookup_Code,g_PeriodSetName,
209 g_dayoffset,l_StartDate,l_EndDate);
210 printOutput(rec_lookup.meaning||': '||to_char(l_StartDate,'YYYY/MM/DD HH24:MI:SS')||' - '||to_char(l_EndDate,'YYYY/MM/DD HH24:MI:SS'));
211 Exception
212 When IBE_ECR_CALENDER_ERROR Then
213 g_setupErrorCount := g_setupErrorCount + 1;
214 printOutput(rec_lookup.meaning||': ');
215 printOutput(SQLERRM);
216 End;
217 End Loop;
218
219 END printBinFreqDateOut;
220
221
222 PROCEDURE printOrderSourceOut(pOrderSource IN Varchar2) IS
223
224 BEGIN
225 printOutput('*** '||getMessage('IBE','IBE_ECR_ORDER_SOURCES')||' ***');
226 printOutput(nvl(pOrderSource,' '));
227 END printOrderSourceOut;
228
229
230 PROCEDURE updateLog(pMvlogid IN Number,
231 pStatus IN Number,
232 pLogTime IN Date,
233 pCurrencyCode IN Varchar2,
234 pErrorCode IN Varchar2 default null,
235 pErrorMessage IN Varchar2 default null )
236 IS
237 Begin
238
239 IF (l_debug = 'Y') THEN
240 printDebugLog('updateLog(+)');
241 printDebugLog(fnd_global.tab||'IN Parameters: '||to_char(pMvlogid)||', '||pStatus||', '||to_char(pLogTime,'YYYY-MM-DD HH24:MI:SS')||', '||pCurrencyCode||', '||pErrorCode||', '||pErrorMessage);
242 END IF;
243
244 Update IBE_ECR_MVLOG
245 Set Refresh_Status = pStatus,
246 Refresh_Duration = ((pLogTime- Creation_Date)*24*60*60),
247 Currency_Code = pCurrencyCode,
248 Error_Code = pErrorCode,
249 Error_Message = pErrorMessage
250 Where MvLog_id = pMvlogid;
251
252 IF (l_debug = 'Y') THEN
253 printDebugLog('updateLog(-)');
254 END IF;
255
256 End updateLog;
257
258 PROCEDURE updateLog(pName IN Varchar2,
259 pMode IN Varchar2,
260 pFromStatus IN Number,
261 pToStatus IN Number,
262 pErrorCode IN Varchar2 default null,
263 pErrorMessage IN Varchar2 default null )
264 IS
265 BEGIN
266
267 IF (l_debug = 'Y') THEN
268 printDebugLog('updateLog(+)');
269 printDebugLog(fnd_global.tab||'IN Parameters: '||pName||', '||pMode||', '||to_char(pFromStatus)||', '||to_char(pToStatus)||', '||pErrorCode||', '||pErrorMessage );
270 END IF;
271
272 Update IBE_ECR_MVLOG
273 Set Refresh_Status = pToStatus,
274 Error_Code = nvl(pErrorCode,Error_Code),
275 Error_Message = nvl(pErrorMessage,Error_Message)
276 Where Refresh_Status = pFromStatus
277 And Mview_Name = pName
278 And Refresh_Mode = pMode;
279
280 IF (l_debug = 'Y') THEN
281 printDebugLog('updateLog(-)');
282 END IF;
283 End updateLog;
284
285 PROCEDURE makeLogEntry(pMviewName IN Varchar2,
286 pMode IN Varchar2,
287 pStatus IN Number,
288 pBeginDate IN Date,
289 pEndDate IN Date,
290 pLogTime IN Date,
291 pErrorCode IN Varchar2 default null,
292 pErrorMessage IN Varchar2 default null,
293 pLogId OUT NOCOPY Number) IS
294
295 l_refreshStatus Number :=0;
296
297 BEGIN
298 IF (l_debug = 'Y') THEN
299 printDebugLog('makeLogEntry(+)');
300 printDebugLog(fnd_global.tab||'IN Parameters: '||pMviewName||', '||pMode||', '||pStatus||',
301 '||to_char(pBeginDate,'YYYY-MM-DD HH24:MI:SS')||', '||to_char(pEndDate,'YYYY-MM-DD HH24:MI:SS')||',
302 '||to_char(pLogTime,'YYYY-MM-DD HH24:MI:SS')||', '||pErrorCode||', '||pErrorMessage );
303
304 END IF;
305
306 -- Make New Entry.
307 Insert into IBE_ECR_MVLOG
308 (mvlog_id,object_version_number,Created_by,Creation_Date,
309 last_updated_by,last_update_date,last_update_login,
310 Program_application_id,Request_id,Program_id,Program_update_Date,Refresh_Mode,
311 mview_Name, Refresh_Status, Begin_Date, End_Date,Fact_Source,Error_Code, Error_Message,Refresh_Duration,
312 CONVERSION_TYPE, DAY_BIN_OFFSET,FORCE_REFRESH_FLAG, QUARTER_BEGIN_FLAG, PERIOD_SET_NAME)
313 Values (ibe_ecr_mvlog_s1.nextval,0,
314 FND_GLOBAL.user_id,pLogTime,
315 FND_GLOBAL.user_id,sysdate,
316 FND_GLOBAL.Conc_Login_ID,
317 fnd_global.prog_appl_id, fnd_global.conc_request_id,
318 fnd_global.conc_program_id,Sysdate,pmode,
319 pMviewName, pStatus, pBeginDate, pEndDate,
320 g_data_source, pErrorCode, pErrorMessage,0,
321 g_ConversionType, g_dayoffset, g_ForceRefreshFlag, g_QuarterBeginFlag, g_periodSetName)
322 RETURNING mvlog_id into plogid;
323
324 IF (l_debug = 'Y') THEN
325 printDebugLog(fnd_global.tab||'IN Parameters: '||to_char(plogid));
326 printDebugLog('makeLogEntry(-)');
327 END IF;
328 EXCEPTION
329 When Others Then
330 printOutput(getMessage('IBE','IBE_ECR_MVLOG_ERROR',pMviewName));
331 Raise;
332 END makeLogEntry;
333
334
335 PROCEDURE dropIndex( pMode IN Varchar2, pOwner IN Varchar2,pName IN Varchar2) IS
336
337 CURSOR dr_obj(p_owner varchar2,p_name varchar2) is
338 Select owner,index_name
339 From all_indexes
340 Where table_owner = p_owner
341 And table_name = p_name;
342 Begin
343 IF (l_debug = 'Y') THEN
344 printDebugLog('dropIndex(+)');
345 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pOwner||', '||pName);
346 END IF;
347
348 If pMode = 'COMPLETE' Then
349 for chg_tbl in dr_obj(pOwner,pName) loop
350 EXECUTE IMMEDIATE 'drop index '||chg_tbl.owner||'.'||chg_tbl.index_name;
351 end loop;
352 End If;
353
354 IF (l_debug = 'Y') THEN
355 printDebugLog('dropIndex(-)');
356 END IF;
357 END dropIndex;
358
359
360 PROCEDURE createIndex( pMode IN Varchar2, pName IN Varchar2) IS
361
362 l_idx_High_space_clause Varchar2(2000);
363 l_idx_Low_space_clause Varchar2(2000);
364 l_addOption Varchar2(100);
365
366 Begin
367
368 IF (l_debug = 'Y') THEN
369 printDebugLog('createIndex(+)');
370 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pName);
371 END IF;
372
373
374 If pMode = 'COMPLETE' Then
375
376 l_addOption := '';
377
378 If g_ParallelFlag = 'Y' Then
379 l_addOption := ' parallel';
380 End If;
381
382 /*
383 l_idx_High_space_clause := ' storage (initial 10M next 10M pctincrease 0) tablespace '||g_idx_tablespace||' pctfree 0'||l_addOption;
384 l_idx_Low_space_clause := ' storage (initial 50K next 50K pctincrease 0) tablespace '||g_idx_tablespace||' pctfree 0'||l_addOption;
385 */
386
387
388 l_idx_High_space_clause := ' tablespace '||g_idx_tablespace||' '||l_addOption;
389 l_idx_Low_space_clause := ' tablespace '||g_idx_tablespace||' '||l_addOption;
390
391
392 If pName = 'IBE_ECR_ORDER_HEADERS_FACT' Then
393
394 EXECUTE IMMEDIATE 'Create index IBE_ECR_ORDER_HEADERS_FACT_N1 on IBE_ECR_ORDER_HEADERS_FACT(FACT_DATE) '||l_idx_High_space_clause;
395
396 EXECUTE IMMEDIATE 'Create index IBE_ECR_ORDER_HEADERS_FACT_N2 on IBE_ECR_ORDER_HEADERS_FACT(FACT_DATE,INVOICE_TO_ORG_ID)'||l_idx_High_space_clause;
397
398 EXECUTE IMMEDIATE 'Create UNIQUE index IBE_ECR_ORDER_HEADERS_FACT_U1 on IBE_ECR_ORDER_HEADERS_FACT(HEADER_ID) '||l_idx_High_space_clause;
399 ElsIf pName = 'IBE_ECR_ORDERS_FACT' Then
400
401 EXECUTE IMMEDIATE 'Create index IBE_ECR_ORDERS_FACT_N1 on IBE_ECR_ORDERS_FACT(ORGANIZATION_ID,INVENTORY_ITEM_ID) '||l_idx_High_space_clause;
402 EXECUTE IMMEDIATE 'Create index IBE_ECR_ORDERS_FACT_N2 on IBE_ECR_ORDERS_FACT(ORG_ID)'||l_idx_High_space_clause;
403 EXECUTE IMMEDIATE 'Create index IBE_ECR_ORDERS_FACT_N3 on IBE_ECR_ORDERS_FACT(FACT_DATE)'||l_idx_High_space_clause;
404
405 /* **** Bug# 4550680 Begin
406 Removing the code for IBE_ECR_BIN_FACT table.
407 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
408 So, IBE_ECR_BIN_FACT table is not required.
409 **** Bug# 4550680 End ***** */
410
411
412 ElsIf pName = 'IBE_ECR_QUOTES_FACT' Then
413
414 EXECUTE IMMEDIATE 'Create index IBE_ECR_QUOTES_FACT_N1 on IBE_ECR_QUOTES_FACT(ORGANIZATION_ID,INVENTORY_ITEM_ID)'||l_idx_High_space_clause;
415 EXECUTE IMMEDIATE 'Create index IBE_ECR_QUOTES_FACT_N2 on IBE_ECR_QUOTES_FACT(ORG_ID)'||l_idx_High_space_clause;
416 EXECUTE IMMEDIATE 'Create index IBE_ECR_QUOTES_FACT_N3 on IBE_ECR_QUOTES_FACT(CUST_ACCOUNT_ID)'||l_idx_High_space_clause;
417
418 End If;
419
420 End If;
421
422
423 IF (l_debug = 'Y') THEN
424 printDebugLog('createIndex(-)');
425 END IF;
426 END createIndex;
427
428 PROCEDURE setSessionParallel( pFlag IN Varchar2) IS
429 Begin
430 IF (l_debug = 'Y') THEN
431 printDebugLog('setSessionParallel(+)');
432 printDebugLog(fnd_global.tab||'IN Parameters: '||pFlag);
433 END IF;
434
435 If pFlag = 'Y' Then
436
437 EXECUTE IMMEDIATE ' alter session enable parallel dml';
438 EXECUTE IMMEDIATE ' alter session enable parallel query';
439
440 End If;
441
442 IF (l_debug = 'Y') THEN
443 printDebugLog('setSessionParallel(-)');
444 END IF;
445 End setSessionParallel;
446
447 PROCEDURE resetSessionParallel( pFlag IN Varchar2) IS
448 Begin
449 IF (l_debug = 'Y') THEN
450 printDebugLog('resetSessionParallel(+)');
451 printDebugLog(fnd_global.tab||'IN Parameters: '||pFlag);
452 END IF;
453
454 If pFlag = 'Y' Then
455 EXECUTE IMMEDIATE ' alter session disable parallel dml';
456 EXECUTE IMMEDIATE ' alter session disable parallel query';
457 End If;
458
459 IF (l_debug = 'Y') THEN
460 printDebugLog('resetSessionParallel(-)');
461 END IF;
462 End resetSessionParallel;
463
464
465
466 PROCEDURE setTableParallel( pFlag IN Varchar2, pOwner IN Varchar2, pName IN Varchar2) IS
467 l_addOption Varchar2(100) := null;
468 Begin
469
470 IF (l_debug = 'Y') THEN
471 printDebugLog('setTableParallel(+)');
472 printDebugLog(fnd_global.tab||'IN Parameters: '||pFlag||', '||pOwner||', '||pName);
473 END IF;
474
475 If pFlag = 'Y' or pFlag is NULL Then
476 l_addOption := 'Parallel';
477 EXECUTE IMMEDIATE 'Alter table '||pOwner||'.'||pName||' '||l_addOption;
478 End If;
479
480
481 IF (l_debug = 'Y') THEN
482 printDebugLog('setTableParallel(-)');
483 END IF;
484 End setTableParallel;
485
486
487 PROCEDURE resetTableParallel( pFlag IN Varchar2, pOwner IN Varchar2, pName IN Varchar2) IS
488 Begin
489
490 IF (l_debug = 'Y') THEN
491 printDebugLog('resetTableParallel(+)');
492 printDebugLog(fnd_global.tab||'IN Parameters: '||pFlag||', '||pOwner||', '||pName);
493 END IF;
494
495 If pFlag = 'Y' Then
496 EXECUTE IMMEDIATE 'Alter table '||pOwner||'.'||pName||' noparallel ';
497 End If;
498 IF (l_debug = 'Y') THEN
499 printDebugLog('resetTableParallel(-)');
500 END IF;
501
502 End resetTableParallel;
503
504
505 PROCEDURE removeFactData( pMode IN Varchar2, pName IN Varchar2, pFromDate IN Date, pToDate IN Date) IS
506
507 l_application_short_name varchar2(300) ;
508 l_status_AppInfo varchar2(300) ;
509 l_industry_AppInfo varchar2(300) ;
510 l_oracle_schema_AppInfo varchar2(300) ;
511 BEGIN
512 IF (l_debug = 'Y') THEN
513 printDebugLog('removeFactData(+)');
514 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pNAME||', '||to_char(pFromDate,'YYYY-MM-DD HH24:MI:SS')||', '||to_char(pToDate,'YYYY-MM-DD HH24:MI:SS') );
515 END IF;
516
517 If pMode = 'COMPLETE' Then
518
519 If g_TruncateFlag = 'Y' Then
520 select application_short_name
521 into l_application_short_name
522 from fnd_application
523 where application_id = 671;
524 IF (fnd_installation.get_app_info(l_application_short_name,
525 l_status_AppInfo ,
526 l_industry_AppInfo ,
527 l_oracle_schema_AppInfo )) then
528 EXECUTE IMMEDIATE 'TRUNCATE TABLE '|| l_oracle_schema_AppInfo|| '.'||pName;
529 ELSE
530 EXECUTE IMMEDIATE 'Delete From '||pName;
531 END IF;
532
533 Else
534 EXECUTE IMMEDIATE 'Delete From '||pName;
535
536 End If;
537
538 Else
539
540 If pName = 'IBE_ECR_QUOTES_FACT' Then
541
542 Delete from IBE_ECR_QUOTES_FACT where quote_date between pFromDate and pToDate;
543
544 Else
545
546 EXECUTE IMMEDIATE 'Delete From '||pName||' Where fact_date between :1 and :2'
547 USING pFromDate,pToDate;
548
549 End IF;
550
551
552 End If;
553
554 IF (l_debug = 'Y') THEN
555 printDebugLog('removeFactData(-)');
556 END IF;
557 END removeFactData;
558
559 Procedure getFrequencyDate(pDate IN Date, pFrequency IN Varchar2, pPeriodSetName IN varchar2,pdayoffset IN Number, pStartDate OUT NOCOPY Date, pEndDate OUT NOCOPY Date) IS
560
561 l_StartDay Number := 1; -- week starts on Sunday
562
563 Cursor c_PeriodDate(p_periodSetName Varchar2,p_periodType varchar2,p_date date) Is
564 Select Start_Date,End_Date
565 From GL_Periods
566 Where period_set_name = p_periodsetname
567 and period_type = p_periodType
568 and adjustment_period_flag = 'N'
569 and p_Date between Start_date and End_date;
570
571 Begin
572
573 pStartDate := NULL;
574 pEndDate := NULL;
575
576 If pFrequency = 'DAY' Then
577 pStartDate := trunc(pDate - pdayoffset);
578 pEndDate := pDate;
579 ElsIf pFrequency = 'WEEK' Then
580
581 Select trunc(NEXT_DAY(pDate-7,l_StartDay))
582 into pStartDate
583 From Dual;
584
585 -- pStartDate := trunc(NEXT_DAY(pDate-7,l_StartDay));
586 pEndDate := pDate;
587 ElsIf pFrequency = 'ROLLING13WK' Then
588
589 Select trunc(NEXT_DAY(pDate-7*14,l_StartDay))
590 into pStartDate
591 From Dual;
592
593 pEndDate := pDate;
594
595 ElsIf pFrequency = 'MONTH' Then
596 For rec_period In c_periodDate(pPeriodSetName,'Month',Trunc(pDate)) Loop
597 pStartDate := rec_period.start_date;
598 pEndDate := pDate;
599 End Loop;
600 ElsIf pFrequency = 'QUARTER' Then
601 For rec_period In c_periodDate(pPeriodSetName,'Quarter',Trunc(pDate)) Loop
602 pStartDate := rec_period.start_date;
603 pEndDate := pDate;
604 End Loop;
605 ElsIf pFrequency = 'YEAR' Then
606 For rec_period In c_periodDate(pPeriodSetName,'Year',Trunc(pDate)) Loop
607 pStartDate := rec_period.start_date;
608 pEndDate := pDate;
609 End Loop;
610 End If;
611
612
613 If (pStartDate is null and pEndDate is null) Then
614 FND_MESSAGE.Set_Name('IBE','IBE_ECR_CALENDER_ERROR');
615 FND_MESSAGE.Set_Token('NAME',InitCap(pFrequency));
616 FND_MESSAGE.Set_Token('PERIODSETNAME',nvl(pPeriodSetName,'???'));
617 FND_MESSAGE.Set_Token('DATE',to_char(pdate));
618 Raise_application_error(-20201, FND_MESSAGE.get);
619 End If;
620
621 End getFrequencyDate;
622
623
624
625 Procedure getTransactionSource(pSourceName IN Varchar2,pSourceValue OUT NOCOPY Varchar2,pSourceMeaning OUT NOCOPY Varchar2) IS
626 l_data_sources Varchar2(2000);
627 l_Meaning Varchar2(4000);
628
629 Begin
630 IF (l_debug = 'Y') THEN
631 printDebugLog('getTransactionSource(+)');
632 END IF;
633 For rec_lookup IN c_lookup(pSourceName) Loop
634 l_data_sources := l_data_sources||' ,'||''''||InitCap(rec_lookup.lookup_Code)||'''';
635 l_Meaning := l_Meaning||', '||rec_lookup.meaning;
636 End Loop;
637
638 pSourceValue := substr(trim(l_data_sources),2,length(trim(l_data_sources)));
639 pSourceMeaning := substr(trim(l_Meaning),2,length(trim(l_Meaning)));
640 IF (l_debug = 'Y') THEN
641 printDebugLog('getTransactionSource(-)');
642 END IF;
643
644 End getTransactionSource;
645
646
647 Procedure getBinMaxRows(pRows OUT NOCOPY NUMBER) IS
648 l_type Varchar2(30) := 'IBE_BIN_NUM_ROW';
649 Begin
650 IF (l_debug = 'Y') THEN
651 printDebugLog('getBinMaxRows(+)');
652 printDebugLog(fnd_global.tab||'IN Parameters: None');
653 END IF;
654
655 Select nvl(max(to_number(lookup_code)),20) into pRows
656 From fnd_lookups
657 Where lookup_type = l_type;
658
659 IF (l_debug = 'Y') THEN
660 printDebugLog(fnd_global.tab||'OUT Parameters: '||to_char(pRows));
661 printDebugLog('getBinMaxRows(-)');
662 END IF;
663
664 End getBinMaxRows;
665
666
667 PROCEDURE getProfileValues IS
668
669 BEGIN
670
671 IF (l_debug = 'Y') THEN
672 printDebugLog('getProfileValues(+)');
673 printDebugLog(fnd_global.tab||'No IN Parameters');
674 END IF;
675
676 g_conversionType := fnd_profile.Value_Specific('IBE_GL_CONVERSION_TYPE',null,null,671);
677
678 If g_conversionType is null Then
679 g_setupErrorCount := g_setupErrorCount + 1;
680 End If;
681
682 g_currencyCode := fnd_profile.Value_Specific('IBE_CURRENCY_CODE',null,null,671);
683
684
685 If g_currencyCode is null Then
686 g_setupErrorCount := g_setupErrorCount + 1;
687 End If;
688
689 g_PeriodSetName := fnd_profile.Value_Specific('IBE_ECR_PERIOD_SET_NAME',null,null,671);
690
691 If g_PeriodSetName is null Then
692 g_setupErrorCount := g_setupErrorCount + 1;
693 End If;
694
695 g_ForceRefreshFlag := nvl(fnd_profile.value_specific('IBE_ENABLE_FORCE_REFRESH',null,null,671),'Y');
696 g_parallelFlag := nvl(fnd_profile.value_specific('IBE_ENABLE_PARALLEL_PROCESSING',null,null,671),'N');
697 g_QuarterBeginFlag := nvl(fnd_profile.Value_Specific('IBE_ECR_QUARTER_BEGIN_DATA',null,null,671),'Y');
698 g_YearlyDataFlag := nvl(fnd_profile.Value_Specific('IBE_ECR_YEARLY_DATA',null,null,671),'Y');
699 g_TruncateFlag := nvl(fnd_profile.Value_Specific('IBE_TRUNC_RECORDS',null,null,671),'Y');
700
701 IF (l_debug = 'Y') THEN
702 printDebugLog(fnd_global.tab||'No OUT Parameters');
703 printDebugLog('getProfileValues(-)');
704 END IF;
705
706 End getProfileValues;
707
708
709 PROCEDURE getPeriodDate(pMode IN Varchar2,pfactName IN varchar2,pBeginDate IN Varchar2,pEndDate IN Varchar2,
710 pFromDate OUT NOCOPY Date,pToDate OUT NOCOPY Date) IS
711
712 l_endDate Date;
713
714 BEGIN
715
716 IF (l_debug = 'Y') THEN
717 printDebugLog('getPeriodDate(+)');
718 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pfactName||', '||pBeginDate||', '||pEndDate);
719 END IF;
720
721 If pMode = 'COMPLETE' Then
722
723 If g_YearlyDataFlag = 'Y' Then
724
725 getFrequencyDate(sysdate,'YEAR',g_PeriodSetName, g_dayoffset, pFromDate,l_endDate);
726
727 ElsIf g_QuarterBeginFlag = 'Y' Then
728
729 getFrequencyDate(sysdate,'QUARTER',g_PeriodSetName,g_dayoffset,pFromDate,l_endDate);
730
731 Else
732
733 pFromDate := trunc(g_refreshSysDate);
734
735 End If;
736
737 If ((pBeginDate is not null) and (pFromDate > trunc(to_date(pBeginDate,'YYYY/MM/DD HH24:MI:SS')))) Then
738 pFromDate := trunc(to_date(pBeginDate,'YYYY/MM/DD HH24:MI:SS'));
739 End If;
740
741 ElsIf pMode = 'INCREMENT' Then
742
743 Select min(End_Date) Into pFromDate
744 From ibe_ecr_mvlog
745 Where refresh_mode = pMode
746 And mview_name = pfactName
747 And refresh_status = 1;
748
749 If pFromDate is null Then
750
751 Select min(Begin_Date) Into pFromDate
752 From ibe_ecr_mvlog
753 Where refresh_mode = pMode
754 And mview_name = pfactName
755 And refresh_status = -1;
756
757 If pFromDate is null Then
758
759 Begin
760 Select End_Date Into pFromDate
761 From ibe_ecr_mvlog
762 Where refresh_mode = 'COMPLETE'
763 And mview_name = pfactName
764 And refresh_status = 1;
765 Exception
766 When NO_DATA_FOUND Then
767 printOutput(getMessage('IBE','IBE_ECR_COMPLETE_FIRST'));
768 Raise;
769 End;
770
771 End If;
772
773 End If;
774
775 pFromDate := Trunc(pFromDate);
776
777 End If;
778
779
780 If pEndDate is null Then
781
782 pToDate := g_refreshSysDate;
783
784 Else
785 pToDate := to_date(pEndDate, 'YYYY/MM/DD HH24:MI:SS');
786
787 End If;
788
789
790
791 IF (l_debug = 'Y') THEN
792 printDebugLog(fnd_global.tab||'OUT Parameters: '||to_char(pFromDate,'YYYY/MM/DD HH24:MI:SS')||', '||to_char(pToDate,'YYYY/MM/DD HH24:MI:SS'));
793 printDebugLog('getPeriodDate(-)');
794 END IF;
795 END getPeriodDate;
796
797 PROCEDURE CheckRatesAvailable(pMode in Varchar2, pFactName in Varchar2, pFromDate in Varchar2,
798 pToDate in Varchar2, pAmount in Number, missingCount out nocopy number) IS
799 CURSOR RateExistCheckRecs (l_start_date Date, l_end_date Date)
800 is
801 SELECT DISTINCT Transactional_Curr_Code,
802 Ordered_date
803 FROM (
804 SELECT DISTINCT
805 OH.Transactional_Curr_Code,
806 trunc(oh.ordered_date) ordered_date
807 FROM
808 Oe_order_Sources OS,
809 oe_order_headers_all oh,
810 fnd_lookups fndlkp
811 WHERE
812 nvl(oh.booked_flag,'N') = 'Y'
813 AND nvl(oh.cancelled_flag,'N') = 'N'
814 AND fndlkp.lookup_type = 'IBE_ECR_ORDER_SOURCE'
815 AND fndlkp.lookup_code = upper(OS.Name)
816 AND OH.Source_Document_Type_ID = OS.order_source_id
817 AND oh.booked_date BETWEEN l_start_date AND l_end_date
818 UNION ALL
819 SELECT DISTINCT
820 a.currency_code Transactional_Curr_Code,
821 to_date(pToDate,'yyyy/mm/dd hh24:mi:ss') ordered_date
822 FROM
823 aso_quote_headers_all a,
824 fnd_lookups fndlkp
825 WHERE
826 a.quote_header_id = (SELECT max(quote_header_id) quote_header_id
827 FROM aso_quote_headers_all
828 WHERE quote_number = a.quote_number)
829 AND ( (
830 fndlkp.lookup_code = upper(a.quote_source_code)
831 AND a.resource_id IS NULL )
832 OR a.resource_id IS NOT NULL)
833 AND fndlkp.lookup_type = 'IBE_ECR_ORDER_SOURCE'
834 AND a.order_id IS NULL
835 AND a.creation_date BETWEEN l_start_date and l_end_date
836 AND a.total_quote_price <= pAmount
837 );
838
839 l_rateexist Char(1);
840 l_factStartDate Date;
841 l_factEndDate Date;
842
843 begin
844
845 missingCount := 0;
846 getPeriodDate(pMode,pFactName, pFromDate, pToDate,l_factStartDate, l_factEndDate);
847
848 for RateRec in RateExistCheckRecs (l_factStartDate, l_factEndDate )
849 loop
850 l_rateexist := gl_currency_api.rate_exists(
851 RateRec.Transactional_Curr_Code,
852 g_CurrencyCode,
853 RateRec.ordered_date,
854 g_ConversionType);
855
856 if l_rateexist = 'N' then
857 missingCount := missingCount + 1;
858 if missingCount = 1 then
859 FND_FILE.PUT_LINE(FND_FILE.LOG,getMessage('IBE','IBE_ECR_RATES_MISSING_ERR'));
860 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
861 FND_FILE.PUT_LINE(FND_FILE.LOG,getMessage('IBE','IBE_ECR_RATES_MISSING_HEADER'));
862 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
863 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad(getMessage('IBE','IBE_ECR_FROM_CURRENCY'),17,' ')||
864 rpad(getMessage('IBE','IBE_ECR_TO_CURRENCY'),17,' ')||
865 rpad(getMessage('IBE','IBE_PRMT_DATE_G'),17)||
866 getMessage('IBE','IBE_ECR_CONVERSION_TYPE'));
867 end if;
868 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad(RateRec.Transactional_Curr_Code,17,' ')||
869 rpad(g_CurrencyCode,17,' ')||
870 rpad(RateRec.ordered_date,17,' ')||
871 g_ConversionType);
872 if missingCount = g_error_threshhold then
873 exit;
874 end if;
875 end if;
876 end loop ;
877
878 end checkRatesAvailable;
879
880
881 PROCEDURE getFactDataPeriod(pMode IN VARCHAR2,pfactName IN Varchar2,pStatus IN Number,
882 pStartDate Out NOCOPY Date,pEndDate OUT NOCOPY Date) IS
883 BEGIN
884 IF (l_debug = 'Y') THEN
885 printDebugLog('getFactDataPeriod(+)');
886 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pfactName||', '||to_char(pStatus));
887 END IF;
888
889
890 Select Begin_Date, End_Date
891 Into pStartDate, pEndDate
892 From IBE_ECR_MVLOG
893 Where Refresh_Status = pStatus
894 And Mview_Name = pFactname
895 And Refresh_Mode = pMode;
896 IF (l_debug = 'Y') THEN
897 printDebugLog(fnd_global.tab||'OUT Parameters: '||to_char(pStartDate,'YYYY/MM/DD')||', '||to_char(pEndDate,'YYYY/MM/DD'));
898 printDebugLog('getFactDataPeriod(-)');
899 END IF;
900 END getFactDataPeriod;
901
902 PROCEDURE insertOrderHeaderFact( p_currency_code IN Varchar2, pFromDate IN Date, pToDate IN Date) IS
903 l_booked_flag varchar2(1) := 'Y';
904 l_cancelled_flag varchar2(1) := 'N';
905 l_fact_source varchar2(240) := 'IBE_ECR_ORDER_SOURCE';
906 BEGIN
907 IF (l_debug = 'Y') THEN
908 printDebugLog('insertOrderHeaderFact(+)');
909 printDebugLog(fnd_global.tab||'IN Parameters: '||p_currency_code||', '||to_char(pFromDate,'YYYY-MM-DD HH24:MI:SS')||', '||to_char(pToDate,'YYYY-MM-DD HH24:MI:SS') );
910 END IF;
911
912 Insert /*+ append */ Into IBE_ECR_ORDER_HEADERS_FACT (fact_date, object_version_number, created_by,creation_date,
913 last_updated_by, last_update_date, last_update_login, resource_id, org_id, currency_code, header_id, ordered_date,
914 order_number,Transactional_Curr_Code,Conversion_Rate, sold_to_org_id, invoice_to_org_id, agreement_id, salesrep_id,
915 functional_amount, reported_amount, customer_class_code, party_id ) SELECT Trunc(OH.booked_date) Fact_Date,
916 0 Object_Version_Number,
917 FND_GLOBAL.user_id Created_By,
918 sysdate Creation_Date,
919 FND_GLOBAL.user_id Last_Updated_By,
920 sysdate Last_Updation_Date,
921 FND_GLOBAL.user_id Last_update_login,
922 null Resource_id,
923 oh.Org_id,
924 p_currency_code Currency_Code,
925 oh.Header_id Header_id,
926 oh.Ordered_date,
927 oh.Order_number,
928 oh.Transactional_Curr_Code,
929 oh.Conversion_Rate,
930 OH.Sold_To_Org_ID,
931 oh.Invoice_To_Org_ID,
932 OH.Agreement_ID,
933 OH.SalesRep_ID,
934 Sum((decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.Pricing_Quantity,0))*
935 (nvl(OL.Unit_Selling_Price,0)*nvl(Oh.conversion_rate,1))) Functional_Amount,
936 Sum((decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.Pricing_Quantity,0))*
937 gl_currency_api.convert_amount_sql(OH.Transactional_Curr_Code,p_currency_code,trunc(oh.ordered_date),
938 g_conversionType,nvl(OL.Unit_Selling_Price,0))) Reported_Amount,
939 hca.customer_class_code, hca.party_id
940 FROM Oe_order_Sources OS,
941 Oe_order_headers_all OH,
942 Oe_order_lines_all OL,
943 hz_cust_accounts hca,
944 hz_cust_site_uses_all hcsu,
945 hz_cust_acct_sites_all hcas,
946 fnd_lookups fndlkp
947 WHERE fndlkp.lookup_type = l_fact_source
948 AND fndlkp.lookup_code = upper(OS.Name)
949 AND OH.Source_Document_Type_ID = OS.order_source_id
950 AND OH.Header_id = OL.Header_id
951 AND nvl(OH.Booked_flag,'N') = l_booked_flag
952 AND nvl(OH.cancelled_flag,'N') = l_cancelled_flag
953 AND OH.booked_date between pFromDate and pToDate
954 AND oh.invoice_To_org_id = hcsu.site_use_id
955 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
956 AND hcas.cust_account_id = hca.Cust_account_id
957 GROUP BY Trunc(OH.booked_date), OH.Org_ID,
958 oh.header_id, oh.ordered_date,
959 oh.order_number, oh.Transactional_Curr_Code,
960 oh.Conversion_Rate,OH.Invoice_To_Org_ID,
961 OH.Sold_To_Org_ID, OH.Agreement_ID, OH.SalesRep_ID,
962 hca.customer_class_code,hca.party_id;
963
964 IF (l_debug = 'Y') THEN
965 printDebugLog('insertOrderHeaderFact(-)');
966 END IF;
967 End insertOrderHeaderFact;
968
969 PROCEDURE insertOrderLineFact( p_currency_code IN Varchar2, pFromDate IN Date, pToDate IN Date) IS
970 l_booked_flag varchar2(1) := 'Y';
971 l_cancelled_flag varchar2(1) := 'N';
972 l_fact_source varchar2(240) := 'IBE_ECR_ORDER_SOURCE';
973 BEGIN
974
975 IF (l_debug = 'Y') THEN
976 printDebugLog('insertOrderLineFact(+)');
977 printDebugLog(fnd_global.tab||'IN Parameters: '||p_currency_code||', '||to_char(pFromDate,'YYYY-MM-DD HH24:MI:SS')||', '||to_char(pToDate,'YYYY-MM-DD HH24:MI:SS') );
978 END IF;
979 /* The following query returns only recordss for type STANDARD, SERVICE and other parent records. */
980 Insert /*+ append */ into IBE_ECR_ORDERS_FACT (fact_date, object_version_number, created_by, creation_date,
981 last_updated_by, last_update_date, last_update_login, resource_id, org_id, msite_id, currency_code, section_id,
982 organization_id, inventory_item_id, uom_code, sold_to_org_id, invoice_to_org_id, agreement_id, salesrep_id,
983 num_times_ordered, sale_quantity, functional_amount, reported_amount) SELECT ift.fact_date Fact_Date,
984 0 Object_Version_Number,
985 FND_GLOBAL.user_id Created_By,
986 sysdate Creation_Date,
987 FND_GLOBAL.user_id Last_Updated_By,
988 sysdate Last_Update_Date,
989 FND_GLOBAL.user_id Last_update_login,
990 null Resource_id,
991 ift.Org_id,
992 1 MSite_id,
993 p_currency_code Currency_Code,
994 1 Section_id,
995 osp.Master_Organization_id ORGANIZATION_ID,
996 ol.Inventory_Item_ID,
997 ol.Pricing_quantity_uom UOM_Code,
998 ol.Sold_To_Org_ID,
999 ol.Invoice_To_Org_ID,
1000 ift.Agreement_ID,
1001 ift.SalesRep_ID,
1002 Count(ift.order_number) NUM_TIMES_ORDERED,
1003 Sum(decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.Pricing_Quantity,0)) AS Sale_Quantity,
1004 return_functional_amount(ol.inventory_item_id,osp.Master_Organization_id,ift.header_id,ol.item_type_code,OL.line_category_code,
1005 OL.ordered_Quantity,OL.Unit_Selling_Price,ift.conversion_rate) Functional_Amount,
1006 gl_currency_api.convert_amount_sql( ift.Transactional_Curr_Code,g_CurrencyCode ,
1007 trunc(ift.ordered_date),g_ConversionType,
1008 return_functional_amount(ol.inventory_item_id,osp.Master_Organization_id,ift.header_id,
1009 ol.item_type_code,OL.line_category_code,
1010 OL.ordered_Quantity,OL.Unit_Selling_Price,ift.conversion_rate) ) Reported_Amount
1011 FROM IBE_ECR_ORDER_HEADERS_FACT ift,
1012 OE_ORDER_LINES_ALL OL,
1013 OE_SYSTEM_PARAMETERS_ALL OSP
1014 WHERE ift.Header_id = OL.Header_id
1015 AND ift.fact_date between pFromDate and pToDate
1016 AND nvl(OL.cancelled_flag,'N') = l_cancelled_flag
1017 AND OL.link_to_line_id is NULL
1018 AND OL.Org_ID = OSP.Org_ID
1019 GROUP BY ift.fact_date, ift.Org_ID, OSP.Master_Organization_id, OL.Inventory_Item_ID, OL.Pricing_quantity_uom, OL.Invoice_To_Org_ID, OL.Sold_To_Org_ID,ift.Agreement_ID, ift.SalesRep_ID ,ift.header_id
1020 ,ol.item_type_code,OL.line_category_code,OL.ordered_Quantity,OL.Unit_Selling_Price,ift.conversion_rate,
1021 ift.Transactional_Curr_Code,ift.ordered_date;
1022
1023
1024 IF (l_debug = 'Y') THEN
1025 printDebugLog('insertOrderLineFact(-)');
1026 END IF;
1027 End insertOrderLineFact;
1028
1029
1030 PROCEDURE insertQuotesFact(pFromDate IN Date, pToDate IN Date) IS
1031 l_fact_source varchar2(240) := 'IBE_ECR_ORDER_SOURCE';
1032 BEGIN
1033
1034 IF (l_debug = 'Y') THEN
1035 printDebugLog('insertQuotesFact(+)');
1036 printDebugLog(fnd_global.tab||'IN Parameters: '||to_char(pFromDate,'YYYY-MM-DD HH24:MI:SS')||', '||to_char(pToDate,'YYYY-MM-DD HH24:MI:SS') );
1037 END IF;
1038
1039 Insert /*+ append */ into IBE_ECR_QUOTES_FACT (quote_date, object_version_number, created_by, creation_date,
1040 last_updated_by, last_update_date, last_update_login, msite_id, org_id, currency_code, section_id, organization_id,
1041 inventory_item_id, uom_code, cust_account_id, employee_person_id, added_to_cart_frequency, quote_quantity,
1042 item_ordered_frequency, order_quantity)
1043 SELECT
1044 Trunc(QH.Creation_Date) Quote_Date,
1045 0 Object_Version_Number,
1046 FND_GLOBAL.user_id Created_By,
1047 sysdate Creation_Date,
1048 FND_GLOBAL.user_id Last_Updated_By,
1049 sysdate Last_Updation_Date,
1050 FND_GLOBAL.user_id Last_update_login,
1051 1 Msite_ID,
1052 QH.Org_id,
1053 QH.Currency_Code,
1054 1 Section_ID,
1055 QL.ORGANIZATION_ID,
1056 QL.Inventory_item_id,
1057 QL.UOM_Code,
1058 QH.Cust_Account_ID,
1059 QH.Employee_Person_ID,
1060 Count(*) Added_To_Cart_Frequency,
1061 Sum(nvl(QL.Quantity,0)) AS Quote_Quantity,
1062 -- Count(decode(nvl(OH.Booked_Flag,'N'),'N',Null,QH.order_id)) Item_Ordered_Frequency,
1063 SUM(get_item_count(ql.inventory_item_id,nvl(oh.header_id,0))) Item_Ordered_Frequency,
1064 Sum(decode(nvl(OH.Booked_Flag,'N'),'N',0, nvl(QL.Quantity,0))) AS Order_Quantity
1065 FROM Aso_quote_headers_all QH,
1066 Aso_quote_lines_all QL,
1067 OE_Order_Headers_ALL OH,
1068 Fnd_lookups fndlkp
1069 WHERE fndlkp.lookup_type = l_fact_source
1070 AND upper(Qh.Quote_Source_Code) = fndlkp.lookup_code
1071 AND QH.Creation_Date between pFromDate and pToDate
1072 AND Qh.quote_header_id = Ql.quote_header_id
1073 and ql.item_type_code <> 'CFG'
1074 AND Qh.order_id = OH.Header_ID (+)
1075 AND Qh.max_version_flag = 'Y'
1076 GROUP BY Trunc(QH.Creation_Date), QH.Org_id ,QH.Currency_Code,QL.ORGANIZATION_ID, QL.Inventory_item_id, QL.UOM_Code, QH.Currency_Code, QH.Cust_Account_ID,QH.Employee_Person_ID;
1077
1078 IF (l_debug = 'Y') THEN
1079 printDebugLog('insertQuotesFact(-)');
1080 END IF;
1081 End insertQuotesFact;
1082
1083
1084 PROCEDURE insertBinTOPORD(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date,pDataFromOM IN Boolean default FALSE) IS
1085 Begin
1086 null;
1087 /* **** Bug# 4550680 Begin
1088 Removing the code for IBE_ECR_BIN_FACT table.
1089 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1090 So, IBE_ECR_BIN_FACT table is not required.
1091 **** Bug# 4550680 End ***** */
1092 End insertBinTOPORD;
1093
1094
1095 PROCEDURE insertBinTOPORD(pCurrencyCode IN Varchar2, pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
1096
1097 Begin
1098 null;
1099 /* **** Bug# 4550680 Begin
1100 Removing the code for IBE_ECR_BIN_FACT table.
1101 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1102 So, IBE_ECR_BIN_FACT table is not required.
1103 **** Bug# 4550680 End ***** */
1104
1105 End insertBinTOPORD;
1106
1107
1108 PROCEDURE insertBinTOPPRD(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date, pDataFromOM IN Boolean default FALSE) IS
1109 Begin
1110 null;
1111 /* **** Bug# 4550680 Begin
1112 Removing the code for IBE_ECR_BIN_FACT table.
1113 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1114 So, IBE_ECR_BIN_FACT table is not required.
1115 **** Bug# 4550680 End ***** */
1116
1117 End insertBinTOPPRD;
1118
1119 PROCEDURE insertBinTOPPRD(pCurrencyCode IN Varchar2,pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
1120 Begin
1121 null;
1122 /* **** Bug# 4550680 Begin
1123 Removing the code for IBE_ECR_BIN_FACT table.
1124 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1125 So, IBE_ECR_BIN_FACT table is not required.
1126 **** Bug# 4550680 End ***** */
1127 End insertBinTOPPRD;
1128
1129
1130 PROCEDURE insertBinTOPCUST(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date,pDataFromOM IN Boolean default FALSE) IS
1131
1132 Begin
1133 null;
1134 /* **** Bug# 4550680 Begin
1135 Removing the code for IBE_ECR_BIN_FACT table.
1136 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1137 So, IBE_ECR_BIN_FACT table is not required.
1138 **** Bug# 4550680 End ***** */
1139
1140 End insertBinTOPCUST;
1141
1142
1143 PROCEDURE insertBinTOPCUST(pCurrencyCode IN Varchar2,pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
1144
1145 Begin
1146 null;
1147 /* **** Bug# 4550680 Begin
1148 Removing the code for IBE_ECR_BIN_FACT table.
1149 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1150 So, IBE_ECR_BIN_FACT table is not required.
1151 **** Bug# 4550680 End ***** */
1152 End insertBinTOPCUST;
1153
1154 PROCEDURE insertBinSUMM(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date,pDataFromOM IN Boolean default FALSE) IS
1155
1156 Begin
1157 null;
1158 /* **** Bug# 4550680 Begin
1159 Removing the code for IBE_ECR_BIN_FACT table.
1160 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1161 So, IBE_ECR_BIN_FACT table is not required.
1162 **** Bug# 4550680 End ***** */
1163 End insertBinSUMM;
1164
1165
1166 PROCEDURE insertBinSUMM(pCurrencyCode IN Varchar2,pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
1167
1168 Begin
1169 null;
1170 /* **** Bug# 4550680 Begin
1171 Removing the code for IBE_ECR_BIN_FACT table.
1172 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1173 So, IBE_ECR_BIN_FACT table is not required.
1174 **** Bug# 4550680 End ***** */
1175 End insertBinSUMM;
1176
1177
1178
1179 PROCEDURE insertBinSUMMCart(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date)
1180 IS
1181 Begin
1182 null;
1183 /* **** Bug# 4550680 Begin
1184 Removing the code for IBE_ECR_BIN_FACT table.
1185 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1186 So, IBE_ECR_BIN_FACT table is not required.
1187 **** Bug# 4550680 End ***** */
1188 End insertBinSUMMCart;
1189
1190 PROCEDURE forceRefreshData(pForceRefreshFlag IN Varchar2,pMode IN Varchar2, pObjName IN Varchar2,pBeginDate IN Date,pEndDate IN Date,pForceRefreshStatus OUT NOCOPY Varchar2) IS
1191
1192 l_begindate date;
1193 l_enddate date;
1194 l_mvlog_id Number;
1195 l_mvlog_Status Number :=1;
1196
1197 BEGIN
1198 IF (l_debug = 'Y') THEN
1199 printDebugLog('ForceRefreshData(+)');
1200 printDebugLog(fnd_global.tab||'IN Parameters: '||pForceRefreshFlag||', '||pMode||', '||pObjName||', '||pBeginDate||', '||pEndDate );
1201 END IF;
1202
1203 -- Set Force Data Refresh to 'Yes' by default.
1204
1205 pForceRefreshStatus := 'Y';
1206
1207 If pForceRefreshFlag = 'N' Then
1208
1209 Begin
1210
1211 Select Mvlog_id, Begin_Date, End_Date Into l_mvlog_id,l_begindate,l_enddate
1212 From IBE_ECR_MVLOG
1213 Where Refresh_Status = l_mvlog_Status
1214 And Mview_Name = pObjName
1215 And Refresh_Mode = pMode;
1216
1217 If (l_begindate = pBeginDate And l_enddate = pendDate) Then
1218
1219 pForceRefreshStatus := 'N';
1220
1221 Update IBE_ECR_MVLOG
1222 Set Program_ID = fnd_global.conc_program_id,
1223 Request_id = fnd_global.conc_request_id,
1224 Program_update_Date = SysDate,
1225 last_updated_by = FND_GLOBAL.USER_ID,
1226 last_update_date = sysdate,
1227 Program_application_id = fnd_global.prog_appl_id
1228 Where mvlog_id = l_mvlog_id;
1229
1230 End if;
1231
1232 Exception
1233 When Others Then
1234 pForceRefreshStatus := 'Y';
1235 End;
1236 End if;
1237
1238 IF (l_debug = 'Y') THEN
1239 printDebugLog(fnd_global.tab||'OUT Parameters: '||pForceRefreshStatus);
1240 printDebugLog('ForceRefreshData(-)');
1241 END IF;
1242 END ForceRefreshData;
1243
1244
1245
1246
1247 PROCEDURE insertBinFact(pRefreshDate IN Date) IS
1248 Begin
1249 null;
1250 /* **** Bug# 4550680 Begin
1251 Removing the code for IBE_ECR_BIN_FACT table.
1252 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1253 So, IBE_ECR_BIN_FACT table is not required.
1254 **** Bug# 4550680 End ***** */
1255 END insertBinFact;
1256
1257
1258 PROCEDURE insertBinFact(pCurrencyCode IN Varchar2,pRefreshDate IN Date) IS
1259 Begin
1260 null;
1261 /* **** Bug# 4550680 Begin
1262 Removing the code for IBE_ECR_BIN_FACT table.
1263 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1264 So, IBE_ECR_BIN_FACT table is not required.
1265 **** Bug# 4550680 End ***** */
1266 END insertBinFact;
1267
1268
1269 PROCEDURE refreshFact(pMode IN Varchar2,pFactName IN Varchar2,pBeginDate IN Varchar2, pEndDate IN Varchar2) IS
1270
1271 l_factStartDate Date;
1272 l_factEndDate Date;
1273
1274 l_ForceRefreshStatus Varchar2(2);
1275 l_mvLogID Number;
1276 l_factCurrencyCode Varchar2(240);
1277
1278 Begin
1279
1280 IF (l_debug = 'Y') THEN
1281 printDebugLog('refreshFact(+)');
1282 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pBeginDate||', '||pEndDate);
1283 END IF;
1284
1285 printOutput('+-----------------------------------------------------------------------------+');
1286 printOutput(getMessage('IBE','IBE_ECR_REFRESH_START',pfactName));
1287
1288 getPeriodDate(pMode,pfactName, pBeginDate, pEndDate,l_factStartDate, l_factEndDate);
1289
1290 l_factCurrencyCode := g_CurrencyCode;
1291
1292 forceRefreshData(g_ForceRefreshFlag,pMode,pfactName,l_factStartDate,l_factEndDate, l_ForceRefreshStatus);
1293
1294 If l_ForceRefreshStatus = 'Y' Then
1295
1296 makeLogEntry(pfactName,pMode,0,l_factStartDate,l_factEndDate,Sysdate,null,null,l_mvLogID);
1297
1298 Begin
1299
1300 updateLog(pfactName,pMode,1,2,null,null);
1301 updateLog(pfactName,pMode,-1,2,null,null);
1302
1303 If pMode = 'COMPLETE' Then
1304 updateLog(pfactName,'INCREMENT',1,2,null,null);
1305 updateLog(pfactName,'INCREMENT',-1,2,null,null);
1306 End If;
1307
1308 setTableParallel(g_parallelFlag,'IBE',pfactName);
1309
1310 removeFactData(pMode,pfactName,l_factStartDate,l_factEndDate);
1311
1312 dropIndex(pMode,'IBE',pfactName);
1313
1314 If pFactName = 'IBE_ECR_ORDER_HEADERS_FACT' Then
1315
1316 insertOrderHeaderFact(g_CurrencyCode,l_factStartDate,l_factEndDate);
1317 Commit;
1318
1319 Delete From IBE_ECR_ORDER_HEADERS_FACT where functional_amount > g_maxamount_ceil;
1320
1321 ElsIf pFactName = 'IBE_ECR_ORDERS_FACT' Then
1322
1323 insertOrderLineFact(g_CurrencyCode,l_factStartDate, l_factEndDate);
1324
1325 ElsIf pFactName = 'IBE_ECR_QUOTES_FACT' Then
1326
1327 insertQuotesFact(l_factStartDate, l_factEndDate);
1328 /* **** Bug# 4550680 Begin
1329 Removing the code for IBE_ECR_BIN_FACT table.
1330 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1331 So, IBE_ECR_BIN_FACT table is not required.
1332 **** Bug# 4550680 End ***** */
1333 End If;
1334
1335 createIndex(pMode,pfactName);
1336
1337 resetTableParallel(g_parallelFlag,'IBE',pfactName);
1338
1339 updateLog(l_mvLogID,1,sysdate,l_FactCurrencyCode,null,null);
1340
1341 Commit;
1342
1343 Exception
1344 When Others Then
1345 updateLog(l_mvLogID,-1,sysdate,null,SQLCODE,SQLERRM);
1346 Raise;
1347 End;
1348 End if;
1349
1350 printOutput(getMessage('IBE','IBE_ECR_REFRESH_PERIOD',pfactName)||' '||to_char(l_factStartDate,'YYYY/MM/DD HH24:MI:SS')||' - '||to_char(l_factEndDate,'YYYY/MM/DD HH24:MI:SS'));
1351
1352 printOutput(getMessage('IBE','IBE_ECR_REFRESH_SUCCESS',pfactName));
1353 printOutput('+-----------------------------------------------------------------------------+');
1354
1355 IF (l_debug = 'Y') THEN
1356 printDebugLog(fnd_global.tab||'OUT Parameters: None');
1357 printDebugLog('refreshFact(-)');
1358 END IF;
1359 Exception
1360 When Others Then
1361
1362 IF (l_debug = 'Y') THEN
1363 printDebugLog('Exception: refershFact(-):'||SQLCODE);
1364 END IF;
1365 printOutput(getMessage('IBE','IBE_ECR_REFRESH_ERROR',pfactName)||'-'||SQLERRM);
1366 printOutput('+-----------------------------------------------------------------------------+');
1367 Raise;
1368 End refreshFact;
1369
1370
1371
1372 PROCEDURE refreshFactMain(errbuf OUT NOCOPY VARCHAR2,
1373 retcode OUT NOCOPY NUMBER,
1374 pMode IN varchar2,
1375 pBeginDate IN varchar2,
1376 pEndDate IN varchar2,
1377 pDayOffset IN Number,
1378 pDebugFlag IN Varchar2,
1379 pRateCheckFlag IN Varchar2) IS
1380 l_Meaning Varchar2(4000);
1381 l_MissingCount Number;
1382
1383 Begin
1384
1385 l_debug_profile := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
1386 if ( l_debug_profile = 'Y' OR pDebugFlag = 'Y') then
1387 l_debug := 'Y';
1388 else
1389 l_debug := 'N';
1390 end if;
1391
1392 if (l_debug_profile = 'Y') then
1393 IBE_UTIL.ENABLE_DEBUG_NEW('N');
1394 else
1395 IBE_UTIL.DISABLE_DEBUG_NEW();
1396 end if;
1397
1398 g_debugFlag := pDebugFlag;
1399
1400 If pDebugFlag = 'Y' Then
1401 IBE_UTIL.Enable_Debug;
1402 End If;
1403
1404 IF (l_debug = 'Y') THEN
1405 printDebugLog('refreshFactMain(+)');
1406 printDebugLog(fnd_global.tab||'IN Parameters: '||pBeginDate||', '||pEndDate||', '||pMode||', '||pDayoffset);
1407 END IF;
1408
1409 printOutput('+-----------------------------------------------------------------------------+');
1410
1411 -- Setting up Global Variables
1412
1413 g_inputRefreshMode := pMode;
1414
1415 g_setupErrorCount := 0;
1416
1417 g_refreshSysDate := Sysdate;
1418
1419 g_dayoffset := nvl(pDayOffset,0);
1420
1421 getTableSpace('INDEX',g_idx_tablespace);
1422
1423 getProfileValues();
1424
1425 getBinMaxRows(g_maxRows);
1426
1427 getTransactionSource('IBE_ECR_ORDER_SOURCE',g_data_source,l_Meaning);
1428
1429
1430 printOutput('+-----------------------------------------------------------------------------+');
1431 printParameterOut(pMode,pBeginDate,pEndDate,pDayOffset);
1432 printOutput('+-----------------------------------------------------------------------------+');
1433 printProfileOut();
1434 printOutput('+-----------------------------------------------------------------------------+');
1435 printOrderSourceOut(l_Meaning);
1436 printOutput('+-----------------------------------------------------------------------------+');
1437
1438 If pEndDate is null Then
1439 printBinFreqDateOut(g_refreshSysDate);
1440 Else
1441 printBinFreqDateOut(to_date(pEndDate, 'YYYY/MM/DD HH24:MI:SS'));
1442 End If;
1443
1444 printOutput('+-----------------------------------------------------------------------------+');
1445 If (g_setupErrorCount > 0) Then
1446
1447 raise_application_error(-20200,getMessage('IBE','IBE_ECR_SETUP_ERROR'));
1448
1449 End If;
1450
1451 if pRateCheckFlag = 'Y' then
1452 CheckRatesAvailable(pMode,'IBE_ECR_ORDER_HEADERS_FACT',pBeginDate, pEndDate,g_maxamount_ceil, l_MissingCount);
1453
1454 if l_MissingCount > 0 then
1455 raise_application_error(-20205,getMessage('IBE','IBE_ECR_RATES_MISSING_ERR'));
1456 end if;
1457 end if;
1458
1459 setSessionParallel( g_parallelFlag);
1460
1461 Update IBE_ECR_MVLOG Set Refresh_status = -1 Where Refresh_Status = 0;
1462
1463
1464 refreshFact(pMode,'IBE_ECR_ORDER_HEADERS_FACT',pBeginDate, pEndDate);
1465 refreshFact(pMode,'IBE_ECR_ORDERS_FACT',pBeginDate, pEndDate);
1466
1467 /* **** Bug# 4550680 Begin
1468 Removing the code for IBE_ECR_BIN_FACT table.
1469 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1470 So, IBE_ECR_BIN_FACT table is not required.
1471 **** Bug# 4550680 End ***** */
1472 refreshFact(pMode,'IBE_ECR_QUOTES_FACT',pBeginDate, pEndDate);
1473
1474 IF (l_debug = 'Y') THEN
1475 printDebugLog('refreshFactMain(-)');
1476 END IF;
1477 commit;
1478
1479 resetSessionParallel(g_parallelFlag);
1480
1481 Exception
1482 When Others Then
1483 printOutput(SQLCODE||'-'||SQLERRM);
1484 commit;
1485 resetSessionParallel( g_parallelFlag);
1486 retcode := 2;
1487 raise;
1488 End refreshFactMain;
1489
1490
1491 PROCEDURE dropMview( pMode IN Varchar2, pOwner IN Varchar2,pName IN Varchar2) IS
1492
1493 CURSOR dr_obj_mv(pOwner IN Varchar2,pName IN Varchar2) is
1494 Select 'drop materialized view '||owner||'.'||mview_name sqlstmt
1495 From all_mviews
1496 Where owner = pOwner
1497 And mview_name = pName;
1498 ddl_curs integer;
1499 Begin
1500 IF (l_debug = 'Y') THEN
1501 printDebugLog('dropMview(+)');
1502 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pOwner||', '||pName);
1503 END IF;
1504
1505 If pMode = 'COMPLETE' Then
1506 /*
1507 for chg_tbl in dr_obj_mv(pOwner,pName) loop
1508 EXECUTE IMMEDIATE chg_tbl.sqlstmt;
1509 end loop;
1510 */
1511
1512 ddl_curs := dbms_sql.open_cursor;
1513 for chg_tbl in dr_obj_mv(pOwner,pName) loop
1514 /* Parse implicitly executes the DDL statements */
1515 dbms_sql.parse(ddl_curs, chg_tbl.sqlstmt,dbms_sql.native) ;
1516 end loop;
1517 dbms_sql.close_cursor(ddl_curs);
1518
1519 End If;
1520 IF (l_debug = 'Y') THEN
1521 printDebugLog('dropMview(-)');
1522 END IF;
1523 END dropMview;
1524
1525 Procedure registerMview(pOperation IN VARCHAR2, pMviewName IN Varchar2) IS
1526
1527 l_Stmt Varchar2(2000);
1528
1529 Begin
1530 IF (l_debug = 'Y') THEN
1531 printDebugLog('registerMview(+)');
1532 printDebugLog(fnd_global.tab||'IN Parameters: '||pOperation||', '||pMviewName);
1533 END IF;
1534
1535 If pOperation = 'CREATE' Then
1536
1537 l_Stmt := 'Create materialized view '||pMviewName||' on prebuilt table refresh complete on demand ENABLE query rewrite As ';
1538
1539 ElsIf pOperation = 'INSERT' Then
1540 l_Stmt := 'insert /*+append */ into '||pMviewName||' ';
1541 End If;
1542 /* **** Bug# 4550680 Begin
1543 Removing the code for IBE_ECR_BIN_FACT table.
1544 Reason: From R12 release, we are decommissioning the iStore Merchant UI reports.
1545 So, IBE_ECR_BIN_FACT table is not required.
1546 **** Bug# 4550680 End *****/
1547
1548
1549 IF (l_debug = 'Y') THEN
1550 printDebugLog('registerMview(-)');
1551 END IF;
1552 End registerMview;
1553
1554 PROCEDURE IsFactDataAvailable(pMode IN Varchar2,pName IN Varchar2,pStatus OUT NOCOPY Varchar2,pBeginDate OUT NOCOPY Date,pEndDate OUT NOCOPY Date,
1555 pConversionType OUT NOCOPY Varchar2, pCurrencyCode OUT NOCOPY Varchar2, pFactSource OUT NOCOPY Varchar2, pDayOffset OUT NOCOPY Number, pForceRefresh OUT
1556 NOCOPY Varchar2, pQuarterBegin OUT NOCOPY Varchar2, pPeriodSetName OUT NOCOPY Varchar2, pErrorMessage OUT NOCOPY Varchar2) IS
1557 l_mvlogStatus Number;
1558 BEGIN
1559 IF (l_debug = 'Y') THEN
1560 printDebugLog('IsFactDataAvailable(+)');
1561 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pName);
1562 END IF;
1563
1564 l_mvlogStatus := 1; -- '1' indicates Successfull
1565
1566 Select 'Y',Begin_Date,End_Date,Conversion_Type,Currency_code,Fact_Source,
1567 day_bin_offset,Force_Refresh_Flag,Quarter_Begin_Flag,Period_Set_Name
1568 INTO pStatus,pBeginDate,pEndDate,pConversionType,pCurrencyCode,pFactSource,
1569 pDayOffset, pForceRefresh, pQuarterBegin,pPeriodSetName
1570 From IBE_ECR_MVLOG
1571 WHERE MView_Name = pName
1572 ANd Refresh_Status = l_mvlogStatus
1573 And Refresh_Mode = pMode;
1574
1575
1576 IF (l_debug = 'Y') THEN
1577 printDebugLog(fnd_global.tab||'OUT Parameters: '||pStatus||', '||pBeginDate||', '||pEndDate||',
1578 '||pConversionType||', '||pCurrencyCode||', '||pFactSource||', '||to_char(pDayOffset)||', '||pForceRefresh||',
1579 '||pQuarterBegin||', '||pPeriodSetName);
1580
1581 END IF;
1582
1583 IF (l_debug = 'Y') THEN
1584 printDebugLog('IsFactDataAvailable(-)');
1585 END IF;
1586
1587 EXCEPTION
1588
1589 WHEN OTHERS THEN
1590 pStatus := 'N';
1591 pBeginDate := sysdate;
1592 pEndDate := sysdate;
1593 pConversionType := null;
1594 pCurrencyCode := null;
1595 pFactSource := null;
1596 pDayOffset := null;
1597 pForceRefresh := null;
1598 pQuarterBegin := null;
1599 pPeriodSetName := null;
1600
1601 pErrorMessage := getMessage('IBE','IBE_ECR_NO_FACT_DATA',pName);
1602
1603 printOutput(pErrorMessage);
1604
1605 IF (l_debug = 'Y') THEN
1606 printDebugLog(fnd_global.tab||'OUT Parameters: '||pStatus||', '||pBeginDate||', '||pEndDate);
1607 printDebugLog('IsDataAvailable(-):'||SQLCODE);
1608 END IF;
1609
1610 END IsFactDataAvailable;
1611
1612 PROCEDURE refreshMview(pMode IN Varchar2, pMViewName Varchar2,pFactName Varchar2) IS
1613
1614 x_DataAvailable Varchar2(2);
1615
1616 x_BeginDate Date;
1617 x_EndDate Date;
1618
1619 l_mvLogID Number;
1620 x_forceRefresh Varchar2(2) := 'Y';
1621 x_NoFactMessage Varchar2(2000);
1622
1623 l_username Varchar2(100);
1624
1625 IBE_ECR_NO_FACT_DATA Exception;
1626
1627 BEGIN
1628 IF (l_debug = 'Y') THEN
1629 printDebugLog('refershMView(+)');
1630 printDebugLog(fnd_global.tab||'IN Parameters: '||pMode||', '||pMViewName||', '||pFactName);
1631 END IF;
1632
1633 printOutput('+-----------------------------------------------------------------------------+');
1634
1635 printOutput(getMessage('IBE','IBE_ECR_REFRESH_START',pMviewName));
1636
1637 IsFactDataAvailable(pMode, pFactName, x_DataAvailable, x_begindate, x_enddate,g_conversionType, g_currencyCode,
1638 g_data_source,g_dayoffset,g_ForceRefreshFlag,g_QuarterBeginFlag,g_periodSetName,x_NoFactMessage);
1639
1640 If x_DataAvailable = 'Y' Then
1641
1642 forceRefreshData(g_ForceRefreshFlag,'COMPLETE',pMViewName,x_BeginDate,x_EndDate,x_forceRefresh);
1643
1644 If x_forceRefresh = 'Y' Then
1645
1646 makeLogEntry(pMViewName,'COMPLETE',0,x_BeginDate,x_EndDate,Sysdate,null,null,l_mvLogID);
1647
1648 Begin
1649
1650 select user into l_username from dual;
1651
1652 updateLog(pMViewName,'COMPLETE',1,2,null,null);
1653 updateLog(pMViewName,'COMPLETE',-1,2,null,null);
1654
1655 -- Complete Refresh
1656 -- No Rollback Segment specified
1657 -- Continue after errors
1658 -- FALSE, 0,0,0 (only required by warehouse refresh as used by replciation process
1659 -- Atomic Refresh since refreshing just one mv at a time.
1660 -- DBMS_MVIEW.REFRESH(pMViewName, 'A', '', TRUE, FALSE, 0,0,0, TRUE);
1661
1662 dropIndex('COMPLETE',l_username,pMViewName);
1663 dropMview('COMPLETE',l_username,pMViewName);
1664
1665 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||pMViewName||' drop storage';
1666
1667 If g_refreshMV = 'Y' Then
1668
1669 setTableParallel(g_parallelFlag,l_username,pMViewName);
1670 registerMview('INSERT',pMViewName);
1671 Commit;
1672 resetTableParallel(g_parallelFlag,l_username,pMViewName);
1673 createIndex('COMPLETE', pMViewName);
1674 registerMview('CREATE',pMViewName);
1675
1676 End If;
1677 updateLog(l_mvLogID,1,sysdate,g_currencyCode,null,null);
1678
1679 Exception
1680 WHEN OTHERS THEN
1681 updateLog(l_mvLogID,-1,sysdate,null,SQLCODE,SQLERRM);
1682 Raise;
1683 End;
1684 End If;
1685 Else
1686 -- make the log entry for no fact data and raise the exception.
1687 makeLogEntry(pMViewName,'COMPLETE',0,x_BeginDate,x_EndDate,Sysdate,null,null,l_mvLogID);
1688 updateLog(l_mvLogID,-1,sysdate,null,'IBE_ECR_NO_FACT_DATA',x_NoFactMessage);
1689 --Raise;
1690 End If;
1691
1692
1693 IF (l_debug = 'Y') THEN
1694 printDebugLog(fnd_global.tab||'No OUT Parameters');
1695 printDebugLog('refershMView(-)');
1696 END IF;
1697
1698 printOutput(getMessage('IBE','IBE_ECR_REFRESH_SUCCESS',pMviewName));
1699 printOutput('+-----------------------------------------------------------------------------+');
1700
1701 EXCEPTION
1702 WHEN OTHERS THEN
1703 IF (l_debug = 'Y') THEN
1704 printDebugLog('Exception: refershMView(-):'||SQLCODE);
1705 END IF;
1706 printOutput(getMessage('IBE','IBE_ECR_REFRESH_ERROR',pMviewName));
1707 printOutput('+-----------------------------------------------------------------------------+');
1708 Raise;
1709 END refreshMView;
1710
1711
1712
1713 PROCEDURE refreshMviewMain( errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER, pfactRefreshMode IN Varchar2, pDebugFlag IN Varchar2 ) IS
1714 BEGIN
1715
1716 /* ***
1717 batoleti: This procedure is obsoleted.
1718 We are not populating the data into prebuilt tables and to MVs.
1719 Always the report queries are constructed on FACT tables.
1720 So, to avoid issues relating to MVs, we are obsolting this procedure.
1721 Please refer the bug# 3993969 for details.
1722 * ***/
1723 NULL;
1724 End refreshMviewMain;
1725
1726 PROCEDURE purgeMain(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER, pDebugFlag IN Varchar2) IS
1727
1728 l_owner Varchar2(30);
1729 l_tablespace Varchar2(30);
1730 l_mvlogStatus Number;
1731
1732 BEGIN
1733 l_debug_profile := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
1734 if ( l_debug_profile = 'Y' OR pDebugFlag = 'Y') then
1735 l_debug := 'Y';
1736 else
1737 l_debug := 'N';
1738 end if;
1739
1740 if (l_debug_profile = 'Y') then
1741 IBE_UTIL.ENABLE_DEBUG_NEW('N');
1742 else
1743 IBE_UTIL.DISABLE_DEBUG_NEW();
1744 end if;
1745
1746 -- Set Debug Mode
1747 g_debugFlag := pDebugFlag;
1748 If pDebugFlag = 'Y' Then
1749 IBE_UTIL.Enable_Debug;
1750 End If;
1751
1752 IF (l_debug = 'Y') THEN
1753 printDebugLog('purgeMain(+)');
1754 END IF;
1755
1756 l_mvlogStatus := 2;
1757 Delete from IBE_ECR_MVLOG where Refresh_Status = l_mvlogStatus ;
1758 Commit;
1759 printOutput(getMessage('IBE','IBE_ECR_MVLOG_PURGE'));
1760 IF (l_debug = 'Y') THEN
1761 printDebugLog('purgeMain(-)');
1762 END IF;
1763 EXCEPTION
1764 when OTHERS then
1765 printOutput(SQLCODE||'-'||SQLERRM);
1766 retcode := 2;
1767 raise;
1768 END purgeMain;
1769
1770
1771
1772 function return_amount(p_inventory_item_id number , p_organization_id number ,
1773 p_order_header_id number )
1774 RETURN NUMBER IS
1775 l_return_amount number;
1776
1777 begin
1778
1779 select Sum((decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.ordered_Quantity,0))*
1780 (nvl(OL.Unit_Selling_Price,0))) into l_return_amount
1781 from oe_order_lines_all ol
1782 where header_id = p_order_header_id
1783 and exists
1784 ( select line_id from oe_order_lines_all x
1785 where x.inventory_item_id = p_inventory_item_id
1786 and ol.top_model_line_id = x.top_model_line_id
1787 ) ;
1788
1789
1790 return l_return_amount;
1791
1792 end return_amount;
1793
1794
1795
1796
1797 function return_functional_amount(p_inventory_item_id number , p_organization_id number ,
1798 p_order_header_id number , p_item_type_code varchar2,p_line_category_code varchar2,
1799 p_ordered_Quantity number ,p_Unit_Selling_Price number,p_conversion_rate number)
1800 RETURN NUMBER IS
1801 l_return_amount number;
1802
1803 begin
1804 select decode(p_item_type_code,'MODEL',
1805 return_amount(p_inventory_item_id,p_organization_id,p_order_header_id),
1806 Sum((decode(p_line_category_code,'RETURN',-1,1)* nvl(p_ordered_Quantity,0))*
1807 (nvl(p_Unit_Selling_Price,0)*nvl(p_conversion_rate,1))))
1808 into l_return_amount
1809 from dual ;
1810
1811
1812 return l_return_amount;
1813
1814 end return_functional_amount;
1815 function get_section_path(section_id in number,store_id in number) return varchar2 is
1816 l_section_id number;
1817 l_store_id number;
1818 v_root_section_id varchar2(10);
1819 l_string varchar2(2000) DEFAULT NULL;
1820 v_string varchar2(2000) DEFAULT NULL;
1821 v_concat_ids varchar2(1000);
1822 l_iter number;
1823 l_concat_begin_brace number;
1824 l_display_name varchar2(100);
1825 cursor get_concat_ids(section_id number, l_store_id number) is
1826 select a.concat_ids, b.msite_root_section_id
1827 from ibe_dsp_msite_sct_sects a,
1828 ibe_msites_b b
1829 where a.child_section_id = section_id
1830 and a.mini_site_id = 1
1831 and b.msite_id = l_store_id
1832 and b.site_type = 'I'; -- Changed as per the Bug # 4394901
1833 cursor get_section_name(l_concat_section_id number) is
1834 SELECT display_name
1835 FROM ibe_dsp_sections_tl
1836 WHERE section_id = l_concat_section_id
1837 AND language = userenv('LANG');
1838 BEGIN
1839 OPEN get_concat_ids(section_id, store_id);
1840 l_concat_begin_brace := 0;
1841 FETCH get_concat_ids into v_concat_ids,v_root_section_id;
1842 v_concat_ids := v_concat_ids||'.';
1843 l_iter:= 1;
1844 WHILE (length(v_concat_ids) > 1)
1845 LOOP
1846 begin
1847 v_string:= substr(v_concat_ids,1,instr(v_concat_ids,'.')-1);
1848 OPEN get_section_name(TO_NUMBER(v_string));
1849 FETCH get_section_name INTO l_display_name;
1850 CLOSE get_section_name;
1851 IF (TO_NUMBER(v_root_section_id) = TO_NUMBER(v_string)) THEN
1852 IF (l_iter > 1) THEN
1853 l_string := l_string||']'||'> '||ltrim(rtrim(l_display_name));
1854 l_iter := l_iter + 1;
1855 l_concat_begin_brace := 1;
1856 ELSE
1857 l_string := l_string||'> '||ltrim(rtrim(l_display_name));
1858 l_iter := l_iter + 1;
1859 END IF;
1860 ELSE
1861 l_string := l_string||'> '||ltrim(rtrim(l_display_name));
1862 l_iter := l_iter + 1;
1863 END IF;
1864 v_concat_ids := substr(v_concat_ids,instr(v_concat_ids,'.')+1) ;
1865 end;
1866 END LOOP;
1867 l_string := substr(l_string,3,length(l_string));
1868 IF (l_concat_begin_brace = 1) THEN
1869 l_string := '['||l_string;
1870 END IF;
1871 return(l_string);
1872 END;
1873
1874 END IBE_REPORTING_PVT;