[Home] [Help]
PACKAGE BODY: APPS.AS_FORECAST_ACTUAL_PKG
Source
1 PACKAGE BODY AS_FORECAST_ACTUAL_PKG AS
2 /* $Header: asxtfab.pls 115.32 2003/01/13 21:55:19 geliu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_FORECAST_ACTUAL_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtfab.pls';
6 g_line_error NUMBER := 0;
7 g_next_line VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);/*for chr='\n'*/
8 g_temp_blob BLOB;
9
10 PROCEDURE Insert_Row(
11 p_SALESFORCE_ID in NUMBER,
12 p_SALES_GROUP_ID in NUMBER,
13 p_PERIOD_NAME in VARCHAR2,
14 p_CURRENCY_CODE in VARCHAR2,
15 p_ALLOCATED_BUDGET_AMOUNT in NUMBER,
16 p_ACTUAL_REVENUE_AMOUNT in NUMBER,
17 p_CREATED_BY in NUMBER,
18 p_CREATION_DATE in DATE,
19 p_LAST_UPDATED_BY in NUMBER,
20 p_LAST_UPDATE_DATE in DATE,
21 p_LAST_UPDATE_LOGIN in NUMBER,
22 p_REQUEST_ID in NUMBER,
23 p_PROGRAM_APPLICATION_ID in NUMBER,
24 p_PROGRAM_ID in NUMBER,
25 p_PROGRAM_UPDATE_DATE in DATE,
26 p_SECURITY_GROUP_ID in NUMBER,
27 p_forecast_category_id in NUMBER,
28 p_credit_type_id in NUMBER)
29 IS
30 BEGIN
31 INSERT INTO AS_FORECAST_ACTUALS(
32 FORECAST_ACTUAL_ID,
33 SALESFORCE_ID,
34 SALES_GROUP_ID,
35 PERIOD_NAME,
36 CURRENCY_CODE,
37 ALLOCATED_BUDGET_AMOUNT,
38 ACTUAL_REVENUE_AMOUNT,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATE_LOGIN,
44 REQUEST_ID,
45 PROGRAM_APPLICATION_ID,
46 PROGRAM_ID,
47 PROGRAM_UPDATE_DATE,
48 FORECAST_CATEGORY_ID,
49 FORECAST_CREDIT_TYPE_ID
50 ) VALUES (
51 AS_FORECAST_ACTUALS_S.NEXTVAL,
52 decode( p_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL, 0, NULL, p_SALESFORCE_ID),
53 decode( p_SALES_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SALES_GROUP_ID),
54 decode( p_PERIOD_NAME, FND_API.G_MISS_CHAR, NULL, p_PERIOD_NAME),
55 decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE),
56 round(decode( p_ALLOCATED_BUDGET_AMOUNT, FND_API.G_MISS_NUM, 0, NULL, 0, p_ALLOCATED_BUDGET_AMOUNT),4),
57 round(decode( p_ACTUAL_REVENUE_AMOUNT, FND_API.G_MISS_NUM, 0, NULL, 0, p_ACTUAL_REVENUE_AMOUNT),4),
58 decode( p_CREATED_BY, FND_API.G_MISS_NUM, -1, p_CREATED_BY),
59 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, SYSDATE, p_CREATION_DATE),
60 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, -1, p_LAST_UPDATED_BY),
61 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, SYSDATE, p_LAST_UPDATE_DATE),
62 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, -1, p_LAST_UPDATE_LOGIN),
63 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
64 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
65 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
66 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE),
67 decode( p_forecast_category_id, FND_API.G_MISS_NUM, NULL,p_forecast_category_id),
68 decode( p_credit_type_id, FND_API.G_MISS_NUM, NULL,p_credit_type_id));
69 /* decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SECURITY_GROUP_ID));--bug#1799322*/
70 End Insert_Row;
71
72 PROCEDURE Update_Row(
73 p_FORECAST_ACTUAL_ID in NUMBER,
74 p_CURRENCY_CODE in VARCHAR2,
75 p_ALLOCATED_BUDGET_AMOUNT in NUMBER,
76 p_ACTUAL_REVENUE_AMOUNT in NUMBER,
77 p_LAST_UPDATED_BY in NUMBER,
78 p_LAST_UPDATE_DATE in DATE,
79 p_LAST_UPDATE_LOGIN in NUMBER,
80 p_REQUEST_ID in NUMBER,
81 p_PROGRAM_APPLICATION_ID in NUMBER,
82 p_PROGRAM_ID in NUMBER,
83 p_PROGRAM_UPDATE_DATE in DATE,
84 p_SECURITY_GROUP_ID in NUMBER
85 )
86 IS
87 BEGIN
88 Update AS_FORECAST_ACTUALS
89 SET
90 CURRENCY_CODE = decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, CURRENCY_CODE, p_CURRENCY_CODE),
91 ALLOCATED_BUDGET_AMOUNT = round(decode( p_ALLOCATED_BUDGET_AMOUNT, FND_API.G_MISS_NUM, ALLOCATED_BUDGET_AMOUNT, NULL, 0, p_ALLOCATED_BUDGET_AMOUNT),4),
92 ACTUAL_REVENUE_AMOUNT = round(decode( p_ACTUAL_REVENUE_AMOUNT, FND_API.G_MISS_NUM, ACTUAL_REVENUE_AMOUNT, NULL, 0, p_ACTUAL_REVENUE_AMOUNT),4),
93 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
94 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, SYSDATE, p_LAST_UPDATE_DATE),
95 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
96 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID)
97 where FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID;
98
99 /* SECURITY_GROUP_ID = decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, SECURITY_GROUP_ID, p_SECURITY_GROUP_ID)--bug#1799322*/
100
101 If (SQL%NOTFOUND) then
102 RAISE NO_DATA_FOUND;
103 End If;
104 END Update_Row;
105
106 PROCEDURE Delete_Row(
107 p_FORECAST_ACTUAL_ID in NUMBER)
108 IS
109 BEGIN
110 DELETE FROM AS_FORECAST_ACTUALS
111 WHERE FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID;
112 If (SQL%NOTFOUND) then
113 RAISE NO_DATA_FOUND;
114 End If;
115 END Delete_Row;
116
117 PROCEDURE Lock_Row(
118 p_FORECAST_ACTUAL_ID in NUMBER,
119 p_SALESFORCE_ID in NUMBER,
120 p_SALES_GROUP_ID in NUMBER,
121 p_PERIOD_NAME in VARCHAR2,
122 p_CURRENCY_CODE in VARCHAR2,
123 p_ALLOCATED_BUDGET_AMOUNT in NUMBER,
124 p_ACTUAL_REVENUE_AMOUNT in NUMBER,
125 p_CREATED_BY in NUMBER,
126 p_CREATION_DATE in DATE,
127 p_LAST_UPDATED_BY in NUMBER,
128 p_LAST_UPDATE_DATE in DATE,
129 p_LAST_UPDATE_LOGIN in NUMBER,
130 p_REQUEST_ID in NUMBER,
131 p_PROGRAM_APPLICATION_ID in NUMBER,
132 p_PROGRAM_ID in NUMBER,
133 p_PROGRAM_UPDATE_DATE in DATE,
134 p_SECURITY_GROUP_ID in NUMBER)
135 IS
136 CURSOR C IS
137 SELECT *
138 FROM AS_FORECAST_ACTUALS
139 WHERE FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID
140 FOR UPDATE of FORECAST_ACTUAL_ID NOWAIT;
141 Recinfo C%ROWTYPE;
142 BEGIN
143 OPEN C;
144 FETCH C INTO Recinfo;
145 If (C%NOTFOUND) then
146 CLOSE C;
147 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 End If;
150 CLOSE C;
151 if (
152 ( Recinfo.FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID)
153 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
154 OR ( ( Recinfo.CREATED_BY IS NULL )
155 AND ( p_CREATED_BY IS NULL )))
156 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
157 OR ( ( Recinfo.CREATION_DATE IS NULL )
158 AND ( p_CREATION_DATE IS NULL )))
159 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
160 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
161 AND ( p_LAST_UPDATED_BY IS NULL )))
162 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
163 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
164 AND ( p_LAST_UPDATE_DATE IS NULL )))
165 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
166 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
167 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
168 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
169 OR ( ( Recinfo.REQUEST_ID IS NULL )
170 AND ( p_REQUEST_ID IS NULL )))
171 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
172 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
173 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
174 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
175 OR ( ( Recinfo.PROGRAM_ID IS NULL )
176 AND ( p_PROGRAM_ID IS NULL )))
177 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
178 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
179 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
180 AND ( ( Recinfo.SALESFORCE_ID = p_SALESFORCE_ID)
181 OR ( ( Recinfo.SALESFORCE_ID IS NULL )
182 AND ( p_SALESFORCE_ID IS NULL )))
183 AND ( ( Recinfo.SALES_GROUP_ID = p_SALES_GROUP_ID)
184 OR ( ( Recinfo.SALES_GROUP_ID IS NULL )
185 AND ( p_SALES_GROUP_ID IS NULL )))
186 AND ( ( Recinfo.PERIOD_NAME = p_PERIOD_NAME)
187 OR ( ( Recinfo.PERIOD_NAME IS NULL )
188 AND ( p_PERIOD_NAME IS NULL )))
189 AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
190 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
191 AND ( p_CURRENCY_CODE IS NULL )))
192 AND ( ( Recinfo.ALLOCATED_BUDGET_AMOUNT = p_ALLOCATED_BUDGET_AMOUNT)
193 OR ( ( Recinfo.ALLOCATED_BUDGET_AMOUNT IS NULL )
194 AND ( p_ALLOCATED_BUDGET_AMOUNT IS NULL )))
195 AND ( ( Recinfo.ACTUAL_REVENUE_AMOUNT = p_ACTUAL_REVENUE_AMOUNT)
196 OR ( ( Recinfo.ACTUAL_REVENUE_AMOUNT IS NULL )
197 AND ( p_ACTUAL_REVENUE_AMOUNT IS NULL )))
198 AND ( ( Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
199 OR ( ( Recinfo.SECURITY_GROUP_ID IS NULL )
200 AND ( p_SECURITY_GROUP_ID IS NULL )))
201 ) then
202 return;
203 else
204 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
205 APP_EXCEPTION.RAISE_EXCEPTION;
206 End If;
207 END Lock_Row;
208
209 PROCEDURE Upload_Data(
210 p_period_set_name IN VARCHAR2,
211 p_line_number IN NUMBER,
212 p_SALESFORCE_NUMBER IN NUMBER,
213 p_SALES_GROUP_NUMBER IN NUMBER,
214 p_PERIOD_NAME IN VARCHAR2,
215 p_CURRENCY_CODE IN VARCHAR2,
216 p_ALLOCATED_BUDGET_AMOUNT IN NUMBER,
217 p_ACTUAL_REVENUE_AMOUNT IN NUMBER,
218 p_CREATED_BY IN NUMBER,
219 p_CREATION_DATE IN DATE,
220 p_LAST_UPDATED_BY IN NUMBER,
221 p_LAST_UPDATE_DATE IN DATE,
222 p_LAST_UPDATE_LOGIN IN NUMBER,
223 p_REQUEST_ID IN NUMBER,
224 p_PROGRAM_APPLICATION_ID IN NUMBER,
225 p_PROGRAM_ID IN NUMBER,
226 p_PROGRAM_UPDATE_DATE IN DATE,
227 p_SECURITY_GROUP_ID IN NUMBER,
228 p_filehandle IN UTL_FILE.FILE_TYPE,
229 p_forecast_category_name IN VARCHAR2,
230 p_credit_type_name IN VARCHAR2)
231
232 IS
233 -- Define the local variables
234 l_forecast_actual_id NUMBER := 0;
235 l_errcnt NUMBER := 0; -- number of errors in this row
236 l_errflag BOOLEAN := FALSE;
237 l_salesforce_id NUMBER;
238 l_sales_group_id NUMBER;
239 l_forecast_category_id NUMBER;
240 l_credit_type_id NUMBER;
241 l_log_file VARCHAR2(60);
242 l_filepath VARCHAR2(60) := ''; -- check utl_file dir
243 l_log_msg VARCHAR2(255):= '';
244 l_header VARCHAR2(100):='';
245 l_period_start_date DATE;
246 l_period_end_date DATE;
247
248 BEGIN
249 -- Validate period name
250 BEGIN
251 Chk_Valid_PeriodName(
252 p_period_name => p_period_name
253 , p_period_set_name => p_period_set_name
254 , p_filehandle => p_filehandle
255 , x_period_flag => l_errflag
256 , x_start_date => l_period_start_date
257 , x_end_date => l_period_end_date );
258
259 IF NOT l_errflag THEN
260 l_errcnt := l_errcnt + 1;
261 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_PERIOD');
262 fnd_message.set_token('PERIOD_NAME',p_period_name);
263 l_log_msg := l_log_msg || fnd_message.get ;
264 END IF;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 APP_EXCEPTION.RAISE_EXCEPTION;
269 END;
270
271 -- Validate Forecast Category
272 BEGIN
273 Get_ForecastCategoryId (
274 p_name => UPPER(p_forecast_category_name)
275 , p_filehandle => p_filehandle
276 , p_start_date => l_period_start_date
277 , p_end_date => l_period_end_date
278 , x_forecast_category_id => l_forecast_category_id ) ;
279
280 IF NVL(l_forecast_category_id,0) = 0 THEN
281 l_errcnt := l_errcnt + 1;
282 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_FRCSTCAT');
283 fnd_message.set_token('FORECASTCATEGORY',p_forecast_category_name);
284 l_log_msg := l_log_msg || fnd_message.get ;
285 END IF;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 APP_EXCEPTION.RAISE_EXCEPTION;
290 END;
291
292 -- Validate Credit Type
293 BEGIN
294 Get_CreditTypeId(
295 p_name => UPPER(p_credit_type_name)
296 , p_filehandle => p_filehandle
297 , x_credit_type_id => l_credit_type_id );
298
299 IF NVL(l_credit_type_id,0) = 0 THEN
300 l_errcnt := l_errcnt + 1;
301 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_CREDITTYPE');
302 fnd_message.set_token('CREDITTYPE',p_credit_type_name);
303 l_log_msg := l_log_msg || fnd_message.get ;
304 END IF;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 APP_EXCEPTION.RAISE_EXCEPTION;
309 END;
310
311 -- Validate Sales group
312 BEGIN
313 Get_SalesGroupId (
314 p_sales_group_number => p_sales_group_number
315 , p_filehandle => p_filehandle
316 , p_start_date => l_period_start_date
317 , p_end_date => l_period_end_date
318 , x_sales_group_id => l_sales_group_id
319 ) ;
320
321 IF NVL(l_sales_group_id,0) = 0 THEN
322 l_errcnt := l_errcnt + 1;
323 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_SLSGRP');
324 fnd_message.set_token('SALESGROUP',p_sales_group_number);
325 l_log_msg := l_log_msg || fnd_message.get ;
326 END IF;
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 APP_EXCEPTION.RAISE_EXCEPTION;
331 END; -- sales group id
332
333 -- Validate Salesforce
334 BEGIN
335 IF NVL(p_salesforce_number, 0) = 0 THEN -- valid
336 l_salesforce_id := NULL;
337 ELSE
338 IF NVL(l_sales_group_id,0) <> 0 THEN -- cannot validate without sales group
339 Get_SalesForceId (
340 p_salesforce_number => p_salesforce_number
341 , p_filehandle => p_filehandle
342 , p_start_date => l_period_start_date
343 , p_end_date => l_period_end_date
344 , p_sales_group_id => l_sales_group_id
345 , x_salesforce_id => l_salesforce_id
346 ) ;
347
348 IF NVL(l_salesforce_id,0) = 0 THEN
349 l_errcnt := l_errcnt + 1;
350 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_SLSPERSON');
351 fnd_message.set_token('SALESPERSON',p_salesforce_number);
352 l_log_msg := l_log_msg || fnd_message.get ;
353 END IF;
354 END IF; -- sales group
355 END IF;
356 EXCEPTION
357 WHEN OTHERS THEN
358 APP_EXCEPTION.RAISE_EXCEPTION;
359 END; -- sales force
360
361 -- Validate currency
362 BEGIN
363 Chk_Valid_Currency(
364 p_currency_code => p_currency_code
365 , p_filehandle => p_filehandle
366 , x_currency_flag => l_errflag);
367
368 IF NOT l_errflag THEN
369 l_errcnt := l_errcnt + 1;
370 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_CURR');
371 fnd_message.set_token('CURRENCY',p_currency_code);
375 EXCEPTION
372 l_log_msg := l_log_msg || fnd_message.get ;
373 END IF;
374
376 WHEN OTHERS THEN
377 APP_EXCEPTION.RAISE_EXCEPTION;
378 END;
379
380
381 -- All validations done. No errors in this row, all ID's valid. Process row.
382 IF l_errcnt=0 THEN
383 -- Retrieve forecast_actual_id for a unique combination of this record
384 BEGIN
385 IF l_salesforce_id IS NOT NULL THEN
386 SELECT forecast_actual_id
387 INTO l_forecast_actual_id
388 FROM AS_FORECAST_ACTUALS
389 WHERE SALESFORCE_ID = l_salesforce_id
390 AND SALES_GROUP_ID = l_sales_group_id
391 AND FORECAST_CATEGORY_ID = l_forecast_category_id
392 AND FORECAST_CREDIT_TYPE_ID = l_credit_type_id
393 AND PERIOD_NAME = p_period_name;
394 ELSE
395 SELECT forecast_actual_id
396 INTO l_forecast_actual_id
397 FROM AS_FORECAST_ACTUALS
398 WHERE SALES_GROUP_ID = l_sales_group_id
399 AND FORECAST_CATEGORY_ID = l_forecast_category_id
400 AND FORECAST_CREDIT_TYPE_ID = l_credit_type_id
401 AND PERIOD_NAME = p_period_name
402 AND SALESFORCE_ID IS NULL;
403 END IF;
404
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 NULL;
408 WHEN OTHERS THEN
409 APP_EXCEPTION.RAISE_EXCEPTION;
410 END;
411
412 IF NVL(l_forecast_actual_id,0) = 0 THEN
413 INSERT_ROW(
414 l_salesforce_id
415 , l_sales_group_id
416 , p_period_name
417 , p_currency_code
418 , p_allocated_budget_amount
419 , p_actual_revenue_amount
420 , p_created_by
421 , SYSDATE
422 , p_last_updated_by
423 , SYSDATE
424 , p_last_update_login
425 , p_request_id
426 , p_program_application_id
427 , p_program_id
428 , SYSDATE
429 , p_security_group_id
430 , l_forecast_category_id
431 , l_credit_type_id);
432
433 ELSE -- update existing row
434 UPDATE_ROW(
435 l_forecast_actual_id
436 , p_currency_code
437 , p_allocated_budget_amount
438 , p_actual_revenue_amount
439 , p_last_updated_by
440 , SYSDATE
441 , p_last_update_login
442 , p_request_id
443 , p_program_application_id
444 , p_program_id
445 , SYSDATE
446 , p_security_group_id);
447
448 END IF; -- if forecast actual ID is obtained
449
450 ELSE -- error in validating IDs
451 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_LINE');
452 fnd_message.set_token('LINE',p_line_number);
453 l_log_msg := fnd_message.get||l_log_msg;
454 UTL_FILE.PUT_LINE(p_filehandle, l_log_msg) ;
455 l_log_msg := l_log_msg;
456 create_loglob(l_log_msg
457 ,null
458 ,'W'
459 ,FALSE);
460 g_line_error := g_line_error + 1;
461 END IF; -- no errors in this row, all ID's valid
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 APP_EXCEPTION.RAISE_EXCEPTION;
466 END Upload_Data;
467
468 FUNCTION Get_LogDir(p_data_file IN VARCHAR2) RETURN VARCHAR2 IS
469 l_token VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(44);
470 l_slash VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(47);
471 l_logdir VARCHAR2(2000) := '';
472 l_pos number := 0;
473 BEGIN
474 -- use first entry of utl_file_dir as the DIR
475 -- if there is no entry then do not even construct file names
476 select trim(value)
477 into l_logdir
478 from v$parameter
479 where name = 'utl_file_dir';
480
481 l_pos := instr(l_logdir, l_token);
482 if ( l_logdir is null ) then
483 raise no_data_found;
484 return l_logdir;
485 elsif (l_pos > 0) then
486 return trim(substr(l_logdir,1,l_pos-1));
487 else
488 return l_logdir;
489 end if;
490 END Get_LogDir ;
491
492
493 FUNCTION Get_LogFileName (p_data_file IN VARCHAR2)
494 RETURN VARCHAR2
495 IS
496 l_log_date VARCHAR2(10);
497 l_decimal VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(46);
498 l_underscore VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(95);
499 l_filename VARCHAR2(100):= '';
500 BEGIN
501 -- Form the log filename using data filename and current date/time.
502 l_log_date := TO_CHAR(SYSDATE,'MMDDYY');
503 if (instr(p_data_file,l_decimal) > 0) then
504 l_filename := substr(p_data_file, 1, instr(p_data_file,l_decimal)-1) ||l_underscore|| l_log_date || '.log' ;
505 else
506 l_filename := p_data_file||l_underscore|| l_log_date || '.log' ;
507 end if;
508 return l_filename;
509 END Get_LogFileName ;
510
511 PROCEDURE Chk_Valid_PeriodName (
512 p_period_name IN VARCHAR2
513 , p_period_set_name IN VARCHAR2
514 , p_filehandle IN UTL_FILE.FILE_TYPE
515 , x_period_flag OUT NOCOPY BOOLEAN
516 , x_start_date OUT NOCOPY DATE
517 , x_end_date OUT NOCOPY DATE )
518 IS
519 l_period_name VARCHAR2(50);
520 l_exception VARCHAR2(500);
521 BEGIN
522 x_period_flag := FALSE;
526 FROM gl_periods
523
524 SELECT period_name, start_date, end_date
525 INTO l_period_name, x_start_date, x_end_date
527 WHERE period_name = p_period_name
528 AND period_set_name = p_period_set_name;
529 IF l_period_name = p_period_name THEN
530 x_period_flag := TRUE;
531 END IF;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 l_period_name := '';
535 x_period_flag := FALSE;
536 WHEN OTHERS THEN
537 IF (UTL_FILE.IS_OPEN(p_filehandle)) THEN
538 UTL_FILE.PUT_LINE(p_filehandle, 'Oracle error while checking period name: '||sqlerrm);
539 END IF;
540 l_exception := 'Oracle error while checking period name: '||sqlerrm;
541 create_loglob(l_exception
542 ,null
543 ,'W'
544 ,FALSE);
545 END Chk_Valid_PeriodName;
546
547 PROCEDURE Chk_Valid_Currency (
548 p_currency_code IN VARCHAR2
549 , p_filehandle IN UTL_FILE.FILE_TYPE
550 , x_currency_flag OUT NOCOPY BOOLEAN )
551 IS
552 l_currency_code VARCHAR2(30);
553 l_exception VARCHAR2(500);
554 BEGIN
555 x_currency_flag := FALSE;
556
557 SELECT currency_code
558 INTO l_currency_code
559 FROM fnd_currencies
560 WHERE currency_code = p_currency_code;
561 IF l_currency_code = p_currency_code THEN
562 x_currency_flag := TRUE;
563 END IF;
564 EXCEPTION
565 WHEN NO_DATA_FOUND THEN
566 l_currency_code := '';
567 WHEN OTHERS THEN
568 IF (UTL_FILE.IS_OPEN(p_filehandle)) THEN
569 UTL_FILE.PUT_LINE(p_filehandle, 'Oracle error while checking Currency: '||sqlerrm);
570 END IF;
571 l_exception := 'Oracle error while checking Currency: '||sqlerrm;
572 create_loglob(l_exception
573 ,null
574 ,'W'
575 ,FALSE);
576 END Chk_Valid_Currency;
577
578 PROCEDURE Get_CreditTypeId (
579 p_name IN VARCHAR2
580 , p_filehandle IN UTL_FILE.FILE_TYPE
581 , x_credit_type_id OUT NOCOPY NUMBER ) IS
582 l_exception VARCHAR2(500);
583 BEGIN
584 SELECT sales_credit_type_id
585 INTO x_credit_type_id
586 FROM aso_i_sales_credit_types_v
587 WHERE
588 enabled_flag = 'Y'
589 AND UPPER(name) = p_name
590 AND rownum = 1;
591 EXCEPTION
592 WHEN NO_DATA_FOUND THEN
593 x_credit_type_id := 0;
594 WHEN OTHERS THEN
595 IF (UTL_FILE.IS_OPEN(p_filehandle)) THEN
596 UTL_FILE.PUT_LINE(p_filehandle, 'Oracle error while validating Credit Type: '||sqlerrm);
597 END IF;
598 l_exception := 'Oracle error while validating Credit Type: '||sqlerrm;
599 create_loglob(l_exception
600 ,null
601 ,'W'
602 ,FALSE);
603 END Get_CreditTypeId;
604
605 PROCEDURE Get_ForecastCategoryId (
606 p_name IN VARCHAR2
607 , p_filehandle IN UTL_FILE.FILE_TYPE
608 , p_start_date IN DATE
609 , p_end_date IN DATE
610 , x_forecast_category_id OUT NOCOPY NUMBER ) IS
611 l_exception VARCHAR2(500);
612 BEGIN
613
614 SELECT forecast_category_id
615 INTO x_forecast_category_id
616 FROM as_forecast_categories
617 WHERE
618 UPPER(forecast_category_name) = p_name
619 AND ((start_date_active <= p_end_date) OR (start_date_active IS NULL))
620 AND ((end_date_active >= p_start_date) OR (end_date_active IS NULL))
621 AND rownum = 1 ;
622
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 x_forecast_category_id := 0;
626 WHEN OTHERS THEN
627 IF (UTL_FILE.IS_OPEN(p_filehandle)) THEN
628 UTL_FILE.PUT_LINE(p_filehandle, 'Oracle error while validating Forecast Category: '||sqlerrm);
629 END IF;
630 l_exception := 'Oracle error while validating Forecast Category: '||sqlerrm;
631 create_loglob(l_exception
632 ,null
633 ,'W'
634 ,FALSE);
635 END Get_ForecastCategoryId;
636
637 PROCEDURE Get_SalesGroupId (
638 p_sales_group_number IN NUMBER
639 , p_filehandle IN UTL_FILE.FILE_TYPE
640 , p_start_date IN DATE
641 , p_end_date IN DATE
642 , x_sales_group_id OUT NOCOPY NUMBER ) IS
643 l_exception VARCHAR2(500);
644 BEGIN
645 SELECT jgb.group_id
646 INTO x_sales_group_id
647 FROM
648 jtf_rs_groups_b jgb
649 , jtf_rs_group_usages jgu
650 WHERE
651 jgb.group_number = p_sales_group_number
652 AND ((jgb.start_date_active <= p_end_date) OR (jgb.start_date_active IS NULL))
653 AND ((jgb.end_date_active >= p_start_date) OR (jgb.end_date_active IS NULL))
654 AND jgu.usage = 'SALES'
655 AND jgu.group_id = jgb.group_id
656 AND rownum = 1;
657
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 x_sales_group_id := 0;
661 WHEN OTHERS THEN
662 IF (UTL_FILE.IS_OPEN(p_filehandle)) THEN
663 UTL_FILE.PUT_LINE(p_filehandle, 'Oracle error while checking Sales Group ID: '||sqlerrm);
664 END IF;
665 l_exception := 'Oracle error while checking Sales Group ID: '||sqlerrm;
666 create_loglob(l_exception
670 END Get_SalesGroupId;
667 ,null
668 ,'W'
669 ,FALSE);
671
672 PROCEDURE Get_SalesForceId (
673 p_salesforce_number IN NUMBER
674 , p_filehandle IN UTL_FILE.FILE_TYPE
675 , p_start_date IN DATE
676 , p_end_date IN DATE
677 , p_sales_group_id IN NUMBER
678 , x_salesforce_id OUT NOCOPY NUMBER
679 ) IS
680 l_file_handle UTL_FILE.FILE_TYPE;
681 l_exception VARCHAR2(500);
682 BEGIN
683
684 SELECT res.resource_id
685 INTO x_salesforce_id
686 FROM
687 jtf_rs_group_members mem
688 , jtf_rs_resource_extns res
689 , jtf_rs_role_relations rrel
690 , jtf_rs_roles_b roleb
691 WHERE
692 res.resource_number = p_salesforce_number
693 AND roleb.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
694 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
695 AND rrel.role_id = roleb.role_id
696 AND (rrel.start_date_active <= p_end_date
697 OR rrel.start_date_active IS NULL)
698 AND (rrel.end_date_active >= p_start_date
699 OR rrel.end_date_active IS NULL)
700 AND rrel.delete_flag <> 'Y'
701 AND (roleb.member_flag = 'Y'
702 OR (NVL(roleb.member_flag,'N') ='N' and roleb.manager_flag='Y'))
703 AND mem.group_id = p_sales_group_id
704 AND mem.resource_id = res.resource_id
705 AND mem.group_member_id = rrel.role_resource_id
706 AND mem.delete_flag <> 'Y'
707 AND rownum = 1 ;
708
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 x_salesforce_id := 0;
712 WHEN OTHERS THEN
713 IF (UTL_FILE.IS_OPEN(p_filehandle)) THEN
714 UTL_FILE.PUT_LINE(p_filehandle, 'Oracle error while checking Sales ForceId: '||sqlerrm);
715 UTL_FILE.PUT_LINE(p_filehandle, 'SalespersonNumber:'||p_salesforce_number||'SalesGroupId: '||p_sales_group_id||' StartDate: '||p_start_date||' EndDate:'||p_end_date);
716 END IF;
717 l_exception := 'Oracle error while checking Sales ForceId: '||sqlerrm||g_next_line||'SalespersonNumber:'||p_salesforce_number||'SalesGroupId: '||p_sales_group_id||' StartDate: '||p_start_date||' EndDate:'||p_end_date;
718 create_loglob(l_exception
719 ,null
720 ,'W'
721 ,FALSE);
722
723 END Get_SalesForceId;
724
725 Procedure Read_Lob(p_file_id IN NUMBER
726 , p_CREATED_BY IN NUMBER
727 , p_LAST_UPDATED_BY IN NUMBER
728 , p_LAST_UPDATE_LOGIN IN NUMBER
729 , p_PROGRAM_APPLICATION_ID IN NUMBER)
730 IS
731 l_lob_loc BLOB;
732 l_lob_len NUMBER;
733 l_file_handle UTL_FILE.FILE_TYPE; /*File Handle given to the Utl_file call*/
734 l_lob_data VARCHAR2(1000);/*this variable stores blob data*/
735 l_filepath VARCHAR2(200) := '';/*has the dir path of log file*/
736 l_file_name VARCHAR2(200) := '';/*has the client file name*/
737 l_log_file VARCHAR2(200) := '';/*has the log file name*/
738 l_amount_var Integer := 200;/*Number of bytes to be read from blob*/
739 l_offset_var Integer := 1; /*Offset given to the dbms_lob.read call*/
740 l_string_param VARCHAR2(200) := '';
741 l_period_name VARCHAR2(25) := '';
742 l_salesgrp_id VARCHAR2(25) := '';
743 l_salesforce_id VARCHAR2(25) := '';
744 l_budget_amt NUMBER := 0;
745 l_revenue_amt NUMBER := 0;
746 lv_budget_amt VARCHAR2(25) := '';
747 lv_revenue_amt VARCHAR2(25) := '';
748 l_currency_code VARCHAR2(25) := '';
749 l_line_data VARCHAR2(100) := '';
750 l_decimal VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(46);/*for chr='.'*/
751 l_token VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(34);/*for chr='"'*/
752 l_comma VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(44); /*for chr=','*/
753 l_period_set_name VARCHAR2(50) := FND_PROFILE.VALUE('AS_FORECAST_CALENDAR');
754 lv_amount_var NUMBER := 0;
755 l_temp_string VARCHAR2(100) := '';
756 l_header VARCHAR2(100):='';
757 l_footer VARCHAR2(100):='';
758 l_string VARCHAR2(100) := '';
759 l_counter NUMBER;
760 l_line_number NUMBER := 0;
761 l_line_counter NUMBER := 0;
762 l_forecast_category_name VARCHAR2(100);
763 l_credit_type_name VARCHAR2(300);
764 l_valid BOOLEAN := TRUE;
765 l_exception VARCHAR2(500) := '';
766 BEGIN
767 SELECT DBMS_LOB.GETLENGTH(file_data), file_data, file_name
768 INTO l_lob_len, l_lob_loc, l_file_name
769 FROM FND_LOBS
770 WHERE file_id = p_file_id;
771
772 IF SQL%NOTFOUND THEN
773 raise NO_DATA_FOUND;
774 END IF;
775 l_filepath := '';
776 l_filepath := Get_LogDir(l_filepath);
777 l_log_file := Get_LogFileName(l_file_name);
778
779 create_loglob(null
780 ,null
781 ,'W'
782 ,TRUE);
783 if (UTL_FILE.IS_OPEN(l_file_handle)) then
784 UTL_FILE.FCLOSE(l_file_handle);
785 end if;
786
787 begin
788 l_file_handle := UTL_FILE.FOPEN(l_filepath, l_log_file, 'w');
789 exception
790 when others then
794 ,FALSE);
791 create_loglob(sqlerrm
792 ,p_file_id
793 ,'W'
795 end;
796 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_HDR');
797 fnd_message.set_token('DATETIME',to_Char(sysdate, 'DD-MON-RR HH24:MI:SS'));
798 fnd_message.set_token('USER',p_created_by);
799 fnd_message.set_token('FILENAME', l_file_name);
800 l_header := fnd_message.get;
801 UTL_FILE.PUT_LINE(l_file_handle, l_header);
802 UTL_FILE.NEW_LINE(l_file_handle, 1);
803
804 create_loglob(l_header
805 ,p_file_id
806 ,'W'
807 ,FALSE);
808 if (l_lob_len > l_amount_var) then
809 lv_amount_var := l_amount_var;
810 else
811 lv_amount_var := l_lob_len;
812 End if;
813
814 while(l_lob_len > 0)
815 LOOP
816 DBMS_LOB.READ(l_lob_loc, lv_amount_var, l_offset_var, l_lob_data);
817 l_string_param := utl_raw.cast_to_varchar2(l_lob_data);
818 while(length(l_string_param) > 0 )
819 Loop
820 l_line_data := '';
821 if (instr(l_string_param, g_next_line) >0) then
822 l_line_data := substr(l_string_param, 1, instr(l_string_param, g_next_line)-1);
823 l_line_data := rtrim(l_line_data, g_next_line);
824 elsif ((instr(l_string_param, l_token, 1, 16) > 0) and (instr(l_string_param, l_token, 1, 17) = 0)) then
825 l_line_data := substr(l_string_param, 1, instr(l_string_param, l_token, 1, 16));
826 l_string_param := l_string_param ||g_next_line;
827 end if;
828 l_valid := true;
829
830 if ( length(trim(l_line_data)) = length(g_next_line)) then
831 l_valid := false;
832 l_line_number := l_line_number + 1;
833 end if;
834
835 /*The following If condition is for parsing the last line of the file and/or concatinating the truncated lines.*/
836 if (nvl(length(l_line_data),0) <= 0) then
837 if (length(l_temp_string) > 0) then
838 l_string := concat(l_temp_string, l_string_param);
839 if (instr(l_string, l_token, 1, 16) > 0) then
840 l_line_data := l_string;
841 l_temp_string := '';
842 end if;
843 else
844 l_temp_string := '';
845 l_temp_string := l_string_param;
846 l_string_param := '';
847 end if;
848 end if;
849
850 if ((length(l_line_data) > 0) and (l_valid))then
851 if (length(l_temp_string) > 0) then
852 l_line_data := concat(l_temp_string, l_line_data);
853 l_temp_string := '';
854 end if;
855
856 for l_counter in 1..8
857 loop
858 begin
859 if (l_counter = 1) then
860 begin
861 l_period_name := trim(substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2));
862 exception
863 when others then
864 l_period_name := null;
865 end;
866 elsif (l_counter = 2) then
867 begin
868 l_forecast_category_name := trim(substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2));
869 exception
870 when others then
871 l_forecast_category_name := null;
872 end;
873 elsif (l_counter = 3) then
874 begin
875 l_credit_type_name := trim(substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2));
876 exception
877 when others then
878 l_credit_type_name := null;
879 end;
880 elsif (l_counter = 4) then
881 begin
882 l_salesgrp_id := to_number(substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2));
883 exception
884 when others then
885 l_salesgrp_id := null;
886 end;
887 elsif (l_counter = 5) then
888 begin
889 l_salesforce_id := to_number(substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2));
890 exception
891 when others then
892 l_salesforce_id := null;
893 end;
894 elsif (l_counter = 6) then
895 begin
896 lv_budget_amt := substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2);
897 while(instr(lv_budget_amt,l_comma) > 0)
898 loop
899 lv_budget_amt := concat(substr(lv_budget_amt,1,instr(lv_budget_amt,l_comma)-1), substr(lv_budget_amt,instr(lv_budget_amt,l_comma)+1));
900 end loop;
901 l_budget_amt := to_number(lv_budget_amt);
902 exception
903 when others then
904 l_budget_amt := null;
905 end;
906 elsif (l_counter = 7) then
907 begin
908 lv_revenue_amt := substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2);
912 end loop;
909 while(instr(lv_revenue_amt,l_comma) > 0)
910 loop
911 lv_revenue_amt := concat(substr(lv_revenue_amt,1,instr(lv_revenue_amt,l_comma)-1), substr(lv_revenue_amt,instr(lv_revenue_amt,l_comma)+1));
913 l_revenue_amt := to_number(lv_revenue_amt);
914 exception
915 when others then
916 l_revenue_amt := null;
917 end;
918 elsif (l_counter = 8) then
919 begin
920 l_currency_code:= trim(substr(l_line_data, 2,instr(l_line_data, l_token, 2)-2));
921 exception
922 when others then
923 l_currency_code := null;
924 end;
925
926 end if;
927 exception
928 when others then
929 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_LINE');
930 fnd_message.set_token('LINE',l_line_number);
931 UTL_FILE.PUT_LINE(l_file_handle, fnd_message.get||sqlerrm) ;
932 l_exception := fnd_message.get||sqlerrm;
933 create_loglob(l_exception
934 ,null
935 ,'W'
936 ,FALSE);
937
938 g_line_error := g_line_error + 1;
939 end;
940 l_line_data := substr(l_line_data, instr(l_line_data, l_token,2)+2);
941 end loop;
942 l_line_number := l_line_number + 1;
943 l_line_counter:= l_line_counter+ 1;
944 begin
945 Upload_Data(l_period_set_name
946 ,l_line_number
947 ,l_salesforce_id
948 ,l_salesgrp_id
949 ,l_period_name
950 ,l_currency_code
951 ,l_budget_amt
952 ,l_revenue_amt
953 ,p_CREATED_By
954 ,sysdate
955 ,p_LAST_UPDATED_BY
956 ,sysdate
957 ,p_LAST_UPDATE_LOGIN
958 ,null
959 ,p_PROGRAM_APPLICATION_ID
960 ,null
961 ,sysdate
962 ,null
963 ,l_file_handle
964 ,l_forecast_category_name
965 ,l_credit_type_name);
966 exception
967 when others then
968 UTL_FILE.PUT_LINE(l_file_handle, 'Oracle error(s) occured at '|| l_line_number ||' while Uploading : '||sqlerrm) ;
969 l_exception := 'Oracle error(s) occured at '|| l_line_number ||' while Uploading : '||sqlerrm;
970 create_loglob(l_exception
971 ,null
972 ,'W'
973 ,FALSE);
974 end;
975 if (l_line_counter >= 50) then
976 commit;
977 l_line_counter := 0;
978 end if;
979 end if;
980 l_string_param := substr(l_string_param, instr(l_string_param, g_next_line)+1);
981 End loop;
982 l_lob_len := l_lob_len - lv_amount_var; --to get the remaining part of the blob.
983 l_offset_var := l_offset_var + lv_amount_var; --to set a pointer to determine till what point the blob is read.
984
985 if ((l_lob_len > 0) and (l_lob_len < lv_amount_var)) Then
986 lv_amount_var := l_lob_len;
987 End if;
988 l_lob_data := '';
989 END LOOP;
990 fnd_message.set_name('ASF','ASF_FRCSTACT_LOG_FOOTER');
991 fnd_message.set_token('DATETIME',to_Char(sysdate, 'DD-MON-RR HH24:MI:SS'));
992 fnd_message.set_token('LINE',l_line_number);
993 fnd_message.set_token('ERROR',g_line_error);
994 l_footer := fnd_message.get;
995 UTL_FILE.NEW_LINE(l_file_handle, 1);
996 UTL_FILE.PUT_LINE(l_file_handle, l_footer ) ;
997 l_footer := l_footer;
998 create_loglob(l_footer
999 ,null
1000 ,'W'
1001 ,FALSE);
1002
1003 create_loglob(null
1004 ,p_file_id
1005 ,'C'
1006 ,FALSE);
1007 --delete_lob(p_file_id, l_file_handle);/*Commented because the internal lob is rewritten with log content.*/
1008 commit;
1009 UTL_FILE.FCLOSE(l_file_handle); -- close data file
1010 l_line_number := 0;
1011 g_line_error := 0;
1012 Exception
1013 When others then
1014 IF (UTL_FILE.IS_OPEN(l_file_handle) = false) THEN
1015 l_file_handle := UTL_FILE.FOPEN(l_filepath, l_log_file, 'a');
1016 END IF;
1017 UTL_FILE.PUT_LINE(l_file_handle, 'Oracle error(s) occured at '|| l_line_number ||' while processing : '||sqlerrm);
1018 UTL_FILE.FCLOSE(l_file_handle); -- close data file
1019 l_exception := 'Oracle error(s) occured at '|| l_line_number ||' while processing : '||sqlerrm;
1020 create_loglob(l_exception
1021 ,null
1022 ,'W'
1023 ,FALSE);
1024 create_loglob(null
1025 ,p_file_id
1026 ,'C'
1027 ,FALSE);
1028 End Read_lob;
1029
1030 /*Deleting the blob*/
1031 PROCEDURE Delete_lob(p_file_id IN NUMBER
1032 ,p_filehandle IN UTL_FILE.FILE_TYPE) IS
1033 l_doc_id NUMBER := 0;
1034 l_datatype NUMBER := 6;
1035 CURSOR doc_id_cur IS
1036 SELECT document_id
1040 BEGIN
1037 FROM Fnd_Documents_tl
1038 WHERE Media_Id = p_file_id;
1039
1041 FOR i IN doc_id_cur
1042 LOOP
1043 EXIT when doc_id_cur%NOTFOUND;
1044 if (l_doc_id is null) then
1045 raise NO_DATA_FOUND;
1046 end if;
1047 l_doc_id := i.document_id;
1048 END LOOP;
1049 begin
1050 FND_DOCUMENTS_PKG.DELETE_ROW( l_doc_id, l_datatype, NULL);
1051 exception
1052 when others then
1053 UTL_FILE.PUT_LINE(p_filehandle,'Error while deleting Fnd_documents: '||sqlerrm);
1054 End ;
1055 begin
1056 DELETE FND_LOBS WHERE FILE_ID = p_file_id;
1057 exception
1058 when others then
1059 UTL_FILE.PUT_LINE(p_filehandle,'Error while deleting lob: '||sqlerrm);
1060 End ;
1061 end;
1062 /*Lob is deleted*/
1063 /*Creating the log lob by accumlating the temporary lob
1064 p_log_string- String that needs to go in blob.
1065 p_file_id-Blob ID to which the temp log needs to be copied at the end.
1066 p_op_type-takes in 'C' or 'W'. C- to copy the temp blob to internal blob.
1067 W- to write to the temp blob
1068 p_exists- boolean value determines whether to create a new temporary blob.(True creates a new one)
1069 */
1070 PROCEDURE Create_Loglob( p_log_string IN VARCHAR2
1071 ,p_file_id IN NUMBER
1072 ,p_op_type IN VARCHAR2
1073 ,p_exists IN BOOLEAN)
1074 IS
1075 /*The following variables are for temporary blob */
1076 dest_lob_loc BLOB;
1077 l_temp_amt_var Integer := 200;
1078 l_temp_offset Integer := 1;
1079 l_temp_amt_var1 Integer := 200;
1080 l_temp_offset1 Integer := 1;
1081 l_temp_logdata VARCHAR2(1000);
1082 l_temp_param VARCHAR2(1000);
1083 l_strlen NUMBER := 0;
1084 l_log_string VARCHAR2(1000);
1085 BEGIN
1086 /*Creating a temporary blob*/
1087 IF (p_exists) THEN
1088 DBMS_LOB.CREATETEMPORARY(g_temp_blob, true);
1089 END IF;
1090
1091 IF (p_op_type = 'W') THEN
1092 /*Write to the temp lob*/
1093 l_log_string := p_log_string||g_next_line;
1094 l_strlen := length(l_log_string);
1095 IF(l_temp_amt_var > l_strlen) THEN
1096 DBMS_LOB.WRITEAPPEND(g_temp_blob, l_strlen,utl_raw.cast_to_raw(l_log_string));
1097
1098 l_temp_offset := l_temp_offset + l_strlen;
1099 ELSE
1100 WHILE(l_strlen > 0)
1101 LOOP
1102 DBMS_LOB.WRITEAPPEND(g_temp_blob, l_temp_amt_var, utl_raw.cast_to_raw(l_log_string));
1103 l_temp_offset := l_temp_offset + l_temp_amt_var;
1104 l_strlen := l_strlen - l_temp_amt_var;
1105 if ((l_strlen > 0) and (l_strlen < l_temp_amt_var)) Then
1106 l_temp_amt_var := l_strlen;
1107 End if;
1108 END LOOP;
1109 l_temp_offset := 1;
1110 l_temp_amt_var := 200;
1111 END IF;
1112 ELSIF (p_op_type = 'C') THEN
1113 SELECT file_data, dbms_lob.getlength(file_data)
1114 INTO dest_lob_loc, l_temp_amt_var1
1115 FROM FND_LOBS
1116 WHERE file_id = p_file_id
1117 FOR Update;
1118 dbms_lob.erase(dest_lob_loc, l_temp_amt_var1);
1119 l_temp_amt_var1 := dbms_lob.getlength(g_temp_blob);
1120 dbms_lob.copy(dest_lob_loc, g_temp_blob, l_temp_amt_var1);
1121 commit;
1122 dbms_lob.freetemporary(g_temp_blob);
1123 END IF;
1124 exception
1125 when others then
1126 dbms_lob.freetemporary(g_temp_blob);
1127 End Create_Loglob;
1128
1129 End AS_FORECAST_ACTUAL_PKG;