[Home] [Help]
PACKAGE BODY: APPS.AHL_WARRANTY_UTILS_PVT
Source
1 PACKAGE BODY AHL_WARRANTY_UTILS_PVT AS
2 /* $Header: AHLVSWUB.pls 120.7.12020000.2 2012/12/07 14:55:41 sareepar ship $ */
3
4 -- Global CONSTANTS
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_WARRANTY_UTILS_PVT';
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7
8 ------------------------------------
9 -- Common constants and variables --
10 ------------------------------------
11 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
12 l_log_statement NUMBER := fnd_log.level_statement;
13 l_log_procedure NUMBER := fnd_log.level_procedure;
14 l_log_error NUMBER := fnd_log.level_error;
15 l_log_unexpected NUMBER := fnd_log.level_unexpected;
16 -----------------------------------------------------------------
17
18 -------------------------------------------------------------------
19 -- Procedure name : Calc_Contract_Exp_Date
20 -- Type : Private
21 --
22 --
23 -- Function :To Calculate Warranty Expiration
24 --
25 --
26 -- Pre-reqs :
27 -- Parameters :
28 --
29 -- Standard IN Parameters :
30 -- p_api_version IN NUMBER Required
31 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
32 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
33 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
34 --
35 -- Standard OUT Parameters :
36 -- x_return_status OUT VARCHAR2 Required
37 -- x_msg_count OUT NUMBER Required
38 -- x_msg_data OUT VARCHAR2 Required
39 --
40 -- Warranty Expiration Calculation Parameters:
41 -- p_warranty_contract_id_tbl IN NUMBER Required if p_module_type is not 'CP'
42 -- cont = contract
43 -- ctl = counter
44 -- Version :
45 -- 16/08/2010 JKJain Initial Creation
46 -------------------------------------------------------------------
47 PROCEDURE Calc_Contract_Exp_Date (
48 p_api_version IN NUMBER,
49 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
50 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
51 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
52 p_module_type IN VARCHAR2 := NULL,
53 p_warranty_contract_id_tbl IN warranty_contract_id_tbl_type,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2)
57 IS
58 --Standard local variables
59 L_API_NAME CONSTANT VARCHAR2(30) := 'Calc_Contract_Exp_Date';
60 L_API_VERSION CONSTANT NUMBER := 1.0;
61 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
62 l_msg_data VARCHAR2(2000);
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER;
65 l_warranty_contract_id NUMBER;
66 l_warranty_contract_id_tbl warranty_contract_id_tbl_type;
67
68 L_EXP_TYPE_DATE CONSTANT VARCHAR2(4) := 'DATE';
69 L_EXP_TYPE_PERIOD CONSTANT VARCHAR2(6) := 'PERIOD';
70 L_EXP_TYPE_FORECAST CONSTANT VARCHAR2(8) := 'FORECAST';
71
72
73 l_cont_end_date DATE;
74 l_cont_start_date DATE;
75 l_cont_ovn NUMBER;
76 l_wrrnty_period NUMBER;
77 l_period_uom VARCHAR2(30);
78 l_cont_exp_type VARCHAR2(30);
79 l_exp_date DATE;
80 l_item_instance_id NUMBER;
81
82 l_user_id NUMBER;
83 l_login_id NUMBER;
84
85 l_least_ctr_exp_date DATE;
86 l_cntr_pk NUMBER;
87 l_cntr_ovn NUMBER;
88 l_cntr_id NUMBER;
89 l_cntr_threshold NUMBER;
90 l_cntr_start NUMBER;
91
92 rec_i NUMBER;
93
94 l_warranty_counter_tbl AHL_UMP_ProcessUnit_PVT.warranty_counter_tbl_type;
95
96 --Cursors
97
98 --fetch Start and End date, OVN, item_instance_id and expiry type for the Contract
99 CURSOR get_cont_details (c_warranty_contract_id IN NUMBER)
100 IS
101 SELECT active_start_date, active_end_date, contract_expiry_type,object_version_number,item_instance_id
102 FROM ahl_warranty_contracts_b
103 WHERE warranty_contract_id = c_warranty_contract_id
104 FOR UPDATE of expiration_date ;
105
106
107 --fetch counters associated with the contract
108 CURSOR get_counter_details (c_warranty_contract_id IN NUMBER)
109 IS
110 SELECT cntrt.warranty_cntrt_counter_id,cntrt.counter_id,cntrt.object_version_number,ctr.uom_code,cntrt.threshold,cntrt.start_value
111 FROM ahl_warranty_cont_ctr_b cntrt, cs_counters ctr
112 WHERE cntrt.warranty_contract_id = c_warranty_contract_id
113 AND cntrt.counter_id = ctr.counter_id
114 FOR UPDATE of expiration_date ;
115
116 BEGIN
117
118 IF (l_log_procedure >= l_log_current_level) THEN
119 fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure');
120 END IF;
121
122 -- initialize return status.
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 -- Standard start of API savepoint
126 SAVEPOINT Calc_Contract_Exp_Date_Pvt;
127
128 -- Initialize message list if p_init_msg_list is set to TRUE
129 IF FND_API.To_Boolean(p_init_msg_list) THEN
130 FND_MSG_PUB.Initialize;
131 END IF;
132
133 -- Initialize API return status to success
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 -- Standard call to check for call compatibility.
137 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
138 p_api_version,
139 l_api_name,G_PKG_NAME) THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END IF;
142
143 -- Basic Validation
144
145 -- Check for Required Parameters
146 IF(p_warranty_contract_id_tbl.COUNT < 1) THEN
147 FND_MESSAGE.Set_Name('AHL','AHL_WARRANTY_CNTRCT_ID_MISS');
148 FND_MSG_PUB.ADD;
149 IF (l_log_unexpected >= l_log_current_level)THEN
150 fnd_log.string
151 (
152 l_log_unexpected,
153 'ahl.plsql.Calc_Contract_Exp_Date',
154 'For this API flow, Warranty Contract Id is mandatory but found null in input '
155 );
156 END IF;
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158 END IF;
159
160 IF (l_log_statement >= l_log_current_level) THEN
161 fnd_log.string(l_log_statement,L_DEBUG_KEY,'p_warranty_contract_id_tbl.COUNT : '||p_warranty_contract_id_tbl.COUNT);
162 END IF;
163
164 -- Logic
165 l_warranty_contract_id_tbl := p_warranty_contract_id_tbl;
166
167
168 FOR i IN 1..l_warranty_contract_id_tbl.COUNT LOOP
169
170 l_warranty_contract_id := l_warranty_contract_id_tbl(i);
171
172 OPEN get_cont_details (l_warranty_contract_id);
173 FETCH get_cont_details INTO l_cont_start_date, l_cont_end_date,l_cont_exp_type,l_cont_ovn,l_item_instance_id ;
174 IF (get_cont_details%NOTFOUND) THEN
175 FND_MESSAGE.Set_Name('AHL','AHL_WARRANTY_CNTRCT_ID_INVALID');
176 FND_MSG_PUB.ADD;
177 CLOSE get_cont_details;
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180 CLOSE get_cont_details;
181
182 l_user_id := to_number(fnd_global.USER_ID);
183 l_login_id := to_number(fnd_global.LOGIN_ID);
184
185
186 --Calculate Expiration Dates for individual Counters associated with this contract.
187 rec_i := 0;
188 l_warranty_counter_tbl.DELETE;
189
190 FOR counter_rec in get_counter_details(l_warranty_contract_id)
191 LOOP
192 l_warranty_counter_tbl(rec_i).warranty_cntrt_counter_id := counter_rec.warranty_cntrt_counter_id;
193 l_warranty_counter_tbl(rec_i).counter_id := counter_rec.counter_id;
194 l_warranty_counter_tbl(rec_i).object_version_number := counter_rec.object_version_number;
195 l_warranty_counter_tbl(rec_i).uom_code := counter_rec.uom_code;
196 l_warranty_counter_tbl(rec_i).max_counter_value := (counter_rec.threshold + counter_rec.start_value);
197 rec_i := rec_i+1;
198 END LOOP;
199
200 l_least_ctr_exp_date :=null;
201
202 IF(l_warranty_counter_tbl.COUNT > 0) THEN
203 --Calling Procedure stored in UMP which will return Least Expiration Date from Counters.
204 AHL_UMP_ProcessUnit_PVT.Get_Cont_Ctr_Expiration_Date (p_item_instance_id => l_item_instance_id,
205 p_x_warranty_counter_tbl => l_warranty_counter_tbl,
206 x_expiration_date => l_least_ctr_exp_date,
207 x_return_status => l_return_status,
208 x_msg_data => l_msg_data,
209 x_msg_count => l_msg_count);
210
211 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
214 RAISE FND_API.G_EXC_ERROR;
215 END IF;
216
217 --Update Contract Counter table with new expiration date
218 FORALL i IN l_warranty_counter_tbl.FIRST..l_warranty_counter_tbl.LAST
219 UPDATE AHL_WARRANTY_CONT_CTR_B SET EXPIRATION_DATE = l_warranty_counter_tbl(i).counter_expiration_date,
220 OBJECT_VERSION_NUMBER = l_warranty_counter_tbl(i).object_version_number+1,
221 LAST_UPDATE_DATE = sysdate,
222 LAST_UPDATED_BY = l_user_id,
223 LAST_UPDATE_LOGIN = l_login_id,
224 SECURITY_GROUP_ID = NULL
225 WHERE WARRANTY_CNTRT_COUNTER_ID = l_warranty_counter_tbl(i).warranty_cntrt_counter_id;
226
227 END IF; -- l_warranty_counter_tbl.COUNT > 0
228
229 IF (l_log_statement >= l_log_current_level)THEN
230 fnd_log.string(
231 l_log_statement,
232 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
233 ' Contract End Date = '|| l_cont_end_date ||' and end date from counter = '|| l_least_ctr_exp_date
234 );
235 END IF;
236
237 l_exp_date := null;
238 --In case of Forecast, variable l_cont_end_date will have NULL value.
239 IF(l_least_ctr_exp_date IS NULL) THEN
240 l_exp_date := l_cont_end_date;
241 ELSIF (l_cont_end_date IS NOT NULL)THEN
242 IF(l_cont_end_date > l_least_ctr_exp_date) THEN
243 l_exp_date := l_least_ctr_exp_date;
244 ELSE
245 l_exp_date := l_cont_end_date;
246 END IF;
247 ELSE l_exp_date := l_least_ctr_exp_date;
248 END IF;
249
250
251 IF (l_log_statement >= l_log_current_level)THEN
252 fnd_log.string(
253 l_log_statement,
254 'ahl.plsql.'||g_pkg_name||'.'||l_api_name ,
255 'Before updating a row in AHL_WARRANTY_CONTRACTS_B. where contract id = '|| l_warranty_contract_id ||' and new expiration date = '|| l_exp_date
256 );
257 END IF;
258
259 UPDATE AHL_WARRANTY_CONTRACTS_B SET EXPIRATION_DATE = l_exp_date,
260 OBJECT_VERSION_NUMBER = l_cont_ovn+1,
261 LAST_UPDATE_DATE = sysdate,
262 LAST_UPDATED_BY = l_user_id,
263 LAST_UPDATE_LOGIN = l_login_id,
264 SECURITY_GROUP_ID = NULL
265 WHERE WARRANTY_CONTRACT_ID = l_warranty_contract_id ;
266
267 END LOOP;
268
269 -- Standard check of p_commit
270 IF FND_API.TO_BOOLEAN(p_commit) THEN
271 COMMIT WORK;
272 END IF;
273
274 IF (l_log_procedure >= l_log_current_level)THEN
275 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure');
276 END IF;
277
278
279
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 ROLLBACK TO Calc_Contract_Exp_Date_Pvt;
284 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
285 p_data => x_msg_data,
286 p_encoded => fnd_api.g_false);
287
288
289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291 ROLLBACK TO Calc_Contract_Exp_Date_Pvt;
292 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
293 p_data => x_msg_data,
294 p_encoded => fnd_api.g_false);
295
296
297 WHEN OTHERS THEN
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 ROLLBACK TO Calc_Contract_Exp_Date_Pvt;
300 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
301 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
302 p_procedure_name => 'Calc_Contract_Exp_Date',
303 p_error_text => SUBSTR(SQLERRM,1,500));
304 END IF;
305 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
306 p_data => x_msg_data,
307 p_encoded => fnd_api.g_false);
308
309 END Calc_Contract_Exp_Date;
310
311 --------------------------------------------------------------------
312 -- PROCEDURE
313 -- Calc_Contract_Exp_Date_CP
314 --
315 -- PURPOSE
316 -- Made as an executable for the Warranty Expiration Calculation Concurrent Program
317 -- Calc_Contract_Exp_Date_CP Parameters :
318 --
319 -- errbuf OUT VARCHAR2 Required
320 -- Defines in pl/sql to store procedure to get error messages into log file
321 -- retcode OUT NUMBER Required
322 -- To get the status of the concurrent program
323
324 -- Version :
325 -- 24 Sep, 2010 JKJain, Supplier Warranty ER, Initial Version - 1.0
326 --------------------------------------------------------------------
327 PROCEDURE Calc_Contract_Exp_Date_CP(
328 errbuf OUT NOCOPY VARCHAR2,
329 retcode OUT NOCOPY NUMBER,
330 p_api_version IN NUMBER :=1.0
331 )
332 IS
333
334 -- Local variables section
335 l_msg_count NUMBER;
336 l_msg_data VARCHAR2(2000);
337 l_return_status VARCHAR2(1);
338 l_api_version NUMBER := 1.0;
339 l_api_name VARCHAR2(30) := 'Calc_Contract_Exp_Date_CP';
340 l_err_msg VARCHAR2(2000);
341 l_msg_index_out NUMBER;
342 l_warranty_contract_id_tbl warranty_contract_id_tbl_type;
343 l_count NUMBER :=0;
344
345 --fetch all the Contracts in Active Status.
346 CURSOR get_active_cont_ids
347 IS
348 SELECT warranty_contract_id
349 FROM ahl_warranty_contracts_b;
350 --WHERE contract_status_code = 'ACTIVE';
351
352 BEGIN
353
354 -- Standard start of API savepoint
355 SAVEPOINT Calc_Contract_Exp_Date_CP;
356
357 -- 1. Initialize error message stack by default
358 FND_MSG_PUB.Initialize;
359
360 -- initialize return status to success
361 retcode := 0;
362
363 -- Standard call to check for call compatibility
364 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
365 retcode := 2;
366 errbuf := FND_MSG_PUB.Get;
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369
370 -- 2. Dump all input parameters
371 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
372 fnd_file.put_line(fnd_file.log, 'fnd_global.USER_ID -> '|| fnd_global.USER_ID);
373 fnd_file.put_line(fnd_file.log, 'fnd_global.RESP_ID -> '||fnd_global.RESP_ID);
374 fnd_file.put_line(fnd_file.log, 'fnd_global.PROG_APPL_ID -> '|| fnd_global.PROG_APPL_ID);
375 fnd_file.put_line(fnd_file.log, 'mo_global.get_current_org_id -> '|| mo_global.get_current_org_id());
376
377 OPEN get_active_cont_ids;
378 FETCH get_active_cont_ids BULK COLLECT INTO l_warranty_contract_id_tbl;
379 CLOSE get_active_cont_ids;
380
381 l_count := l_warranty_contract_id_tbl.COUNT;
382
383 fnd_file.put_line(fnd_file.log, 'before calling Calc_Contract_Exp_Date, number of records = '||l_count);
384
385 Calc_Contract_Exp_Date(
386 p_api_version => p_api_version,
387 p_module_type => 'CP', --passing p_module_type as Concurrent Program
388 p_warranty_contract_id_tbl => l_warranty_contract_id_tbl,
389 x_return_status => l_return_status,
390 x_msg_count => l_msg_count,
391 x_msg_data => l_msg_data);
392
393
394 l_msg_count := FND_MSG_PUB.Count_Msg;
395 IF (l_msg_count > 0) THEN
396 fnd_file.put_line(fnd_file.log, 'Following error occured while calculating Warranty Expiration...');
397 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
398 RAISE FND_API.G_EXC_ERROR;
399 ELSE
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402 END IF;
403
404 fnd_file.put_line(fnd_file.log, 'After calling Calc_Contract_Exp_Date successfully');
405
406 -- set the output
407 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Successfully calculated and updated expiration date for '|| l_count ||' warranty contracts. ');
408
409
410 EXCEPTION
411 WHEN FND_API.G_EXC_ERROR THEN
412 ROLLBACK TO Calc_Contract_Exp_Date_CP;
413 retcode := 2;
414 errbuf := l_api_name || ':ERR_EXP:' || substrb(sqlerrm,1,60);
415 FOR i IN 1..l_msg_count
416 LOOP
417 fnd_msg_pub.get( p_msg_index => i,
418 p_encoded => FND_API.G_FALSE,
419 p_data => l_err_msg,
420 p_msg_index_out => l_msg_index_out);
421
422 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
423 END LOOP;
424
425
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 ROLLBACK TO Calc_Contract_Exp_Date_CP;
428 retcode := 2;
429 errbuf := l_api_name || ':ERR_UNEXP:' || substrb(sqlerrm,1,60);
430 l_msg_count := Fnd_Msg_Pub.count_msg;
431 FOR i IN 1..l_msg_count
432 LOOP
433 fnd_msg_pub.get( p_msg_index => i,
434 p_encoded => FND_API.G_FALSE,
435 p_data => l_err_msg,
436 p_msg_index_out => l_msg_index_out);
437
438 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
439 END LOOP;
440
441
442 WHEN OTHERS THEN
443 ROLLBACK TO Calc_Contract_Exp_Date_CP;
444 retcode := 2;
445 errbuf := l_api_name || ':EXP_OTH:' || substrb(sqlerrm,1,60);
446 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
447 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
448 p_procedure_name => 'Calc_Contract_Exp_Date_CP',
449 p_error_text => SUBSTR(SQLERRM,1,500));
450 END IF;
451 l_msg_count := Fnd_Msg_Pub.count_msg;
452 FOR i IN 1..l_msg_count
453 LOOP
454 fnd_msg_pub.get( p_msg_index => i,
455 p_encoded => FND_API.G_FALSE,
456 p_data => l_err_msg,
457 p_msg_index_out => l_msg_index_out);
458
459 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
460 END LOOP;
461
462
463 END Calc_Contract_Exp_Date_CP;
464
465
466 --------------------------------------------------------------------
467 -- PROCEDURE
468 -- Disable_Templates_CP
469 --
470 -- PURPOSE
471 -- Made as an executable for the Disabling Templates Concurrent Program
472 -- Disable_Templates_CP Parameters :
473 --
474 -- errbuf OUT VARCHAR2 Required
475 -- Defines in pl/sql to store procedure to get error messages into log file
476 -- retcode OUT NUMBER Required
477 -- To get the status of the concurrent program
478
479 -- Version :
480 -- 04 Oct, 2010 JKJain, Supplier Warranty ER, Initial Version - 1.0
481 --------------------------------------------------------------------
482 PROCEDURE Disable_Templates_CP(
483 errbuf OUT NOCOPY VARCHAR2,
484 retcode OUT NOCOPY NUMBER,
485 p_api_version IN NUMBER :=1.0
486 )
487 IS
488
489 -- Local variables section
490
491 l_api_version NUMBER := 1.0;
492 l_api_name VARCHAR2(30) := 'Disable_Templates_CP';
493 l_err_msg VARCHAR2(2000);
494 l_msg_index_out NUMBER;
495 l_msg_count NUMBER;
496 l_user_id NUMBER;
497 l_login_id NUMBER;
498 --Cursors
499
500 --fetch Enabled Templates from template table
501 CURSOR disable_templates_csr
502 IS
503 SELECT warranty_template_id,object_version_number,enabled_flag
504 FROM ahl_warranty_templates_b
505 WHERE SYSDATE > nvl(active_end_date,SYSDATE)
506 AND enabled_flag = 'Y'
507 FOR UPDATE of enabled_flag ;
508
509 type disable_templates_csr_type is table of disable_templates_csr%rowtype;
510 csrTbl disable_templates_csr_type;
511
512 BEGIN
513
514 -- Standard start of API savepoint
515 SAVEPOINT Disable_Templates_CP_PVT;
516
517 -- 1. Initialize error message stack by default
518 FND_MSG_PUB.Initialize;
519
520 -- initialize return status to success
521 retcode := 0;
522
523 -- Standard call to check for call compatibility
524 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
525 retcode := 2;
526 errbuf := FND_MSG_PUB.Get;
527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528 END IF;
529
530 -- 2. Dump all input parameters
531 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
532 fnd_file.put_line(fnd_file.log, 'fnd_global.USER_ID -> '|| fnd_global.USER_ID);
533 fnd_file.put_line(fnd_file.log, 'fnd_global.RESP_ID -> '||fnd_global.RESP_ID);
534 fnd_file.put_line(fnd_file.log, 'fnd_global.PROG_APPL_ID -> '|| fnd_global.PROG_APPL_ID);
535 fnd_file.put_line(fnd_file.log, 'mo_global.get_current_org_id -> '|| mo_global.get_current_org_id());
536
537 -- Bulk collect
538 OPEN disable_templates_csr;
539 FETCH disable_templates_csr BULK COLLECT INTO csrTbl;
540 CLOSE disable_templates_csr;
541
542 l_user_id := to_number(fnd_global.USER_ID);
543 l_login_id := to_number(fnd_global.LOGIN_ID);
544
545 fnd_file.put_line(fnd_file.log, 'before updating table AHL_WARRANTY_TEMPLATES_B, rows = ' || csrTbl.COUNT );
546
547 FOR i IN 1..csrTbl.COUNT
548 LOOP
549 UPDATE AHL_WARRANTY_TEMPLATES_B SET ENABLED_FLAG = 'N',
550 OBJECT_VERSION_NUMBER = csrTbl(i).object_version_number+1,
551 LAST_UPDATE_DATE = SYSDATE,
552 LAST_UPDATED_BY = l_user_id,
553 LAST_UPDATE_LOGIN = l_login_id,
554 SECURITY_GROUP_ID = NULL
555 WHERE WARRANTY_TEMPLATE_ID = csrTbl(i).warranty_template_id;
556
557 END LOOP;
558
559 fnd_file.put_line(fnd_file.log, 'After updating table AHL_WARRANTY_TEMPLATES_B successfully');
560
561 -- set the output
562 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Successfully disables '|| csrTbl.COUNT ||' Warranty Templates having end date less than '||sysdate);
563
564 EXCEPTION
565
566 WHEN OTHERS THEN
567 ROLLBACK TO Disable_Templates_CP_PVT;
568 retcode := 2;
569 errbuf := l_api_name || ':EXP_OTH:' || substrb(sqlerrm,1,60);
570 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
571 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
572 p_procedure_name => 'Disable_Templates_CP',
573 p_error_text => SUBSTR(SQLERRM,1,500));
574 END IF;
575 l_msg_count := Fnd_Msg_Pub.count_msg;
576 FOR i IN 1..l_msg_count
577 LOOP
578 fnd_msg_pub.get( p_msg_index => i,
579 p_encoded => FND_API.G_FALSE,
580 p_data => l_err_msg,
581 p_msg_index_out => l_msg_index_out);
582
583 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
584 END LOOP;
585
586
587 END Disable_Templates_CP;
588
589 -------------------------------------------------------------------
590 -- Procedure name : Disable_Templates_For_Vendor
591 -- Type : Private
592 --
593 --
594 -- Function :To Change template's Enabled_Flag to N, given a vendor id.
595 --
596 --
597 -- Pre-reqs :
598 -- Parameters :
599 --
600 -- Standard IN Parameters :
601 -- p_api_version IN NUMBER Required
602 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
603 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
604 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
605 --
606 -- Standard OUT Parameters :
607 -- x_return_status OUT VARCHAR2 Required
608 -- x_msg_count OUT NUMBER Required
609 -- x_msg_data OUT VARCHAR2 Required
610 --
611 -- Warranty Expiration Calculation Parameters:
612 -- p_warranty_vendor_id IN NUMBER Required
613 --
614 -- Version :
615 -- 24-Sep-2010 JKJain Initial Creation
616 -- 30-NOV-2010 JKJain Modified for the new requirements
617 -------------------------------------------------------------------
618 PROCEDURE Disable_Templates_For_Vendor (
619 p_api_version IN NUMBER,
620 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
621 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
622 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
623 p_module_type IN VARCHAR2 := NULL,
624 p_warranty_vendor_id_tbl IN warranty_vendor_id_tbl_type,
625 x_return_status OUT NOCOPY VARCHAR2,
626 x_msg_count OUT NOCOPY NUMBER,
627 x_msg_data OUT NOCOPY VARCHAR2
628 )
629
630 IS
631 --Standard local variables
632 L_API_NAME CONSTANT VARCHAR2(30) := 'Disable_Templates_For_Vendor';
633 L_API_VERSION CONSTANT NUMBER := 1.0;
634 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
635
636 l_user_id NUMBER := fnd_global.user_id;
637 l_login_id NUMBER := fnd_global.login_id;
638
639
640 --Cursors
641 --JKJain, 30 Nov 2010
642 /*
643 --fetch Vendor ID record from the template table
644 CURSOR update_enabled_flag_csr (c_vendor_id IN NUMBER)
645 IS
646 SELECT warranty_template_id,warranty_vendor_id,object_version_number,enabled_flag
647 FROM ahl_warranty_templates_b
648 WHERE warranty_vendor_id = c_vendor_id
649 FOR UPDATE of enabled_flag ;
650 */
651
652
653 BEGIN
654
655 IF (l_log_procedure >= l_log_current_level) THEN
656 fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure, count = '||p_warranty_vendor_id_tbl.COUNT);
657 END IF;
658
659 -- Standard start of API savepoint
660 SAVEPOINT Disable_Tmpls_For_Vendor_Pvt;
661
662 -- Initialize message list if p_init_msg_list is set to TRUE
663 IF FND_API.To_Boolean(p_init_msg_list) THEN
664 FND_MSG_PUB.Initialize;
665 END IF;
666
667 -- Initialize API return status to success
668 x_return_status := FND_API.G_RET_STS_SUCCESS;
669
670 -- Standard call to check for call compatibility.
671 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
672 p_api_version,
673 l_api_name,G_PKG_NAME) THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676
677 -- Basic Validation
678
679 -- Check for Required Parameters
680 IF(p_warranty_vendor_id_tbl.COUNT < 1) THEN
681 FND_MESSAGE.Set_Name('AHL','AHL_SW_VENDOR_ID_NULL');
682 FND_MSG_PUB.ADD;
683 IF (l_log_unexpected >= l_log_current_level)THEN
684 fnd_log.string
685 (
686 l_log_unexpected,
687 'ahl.plsql.Disable_Templates_For_Vendor',
688 'For this API flow, Vendor Id in table is mandatory but found null in input '
689 );
690 END IF;
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END IF;
693
694 IF (l_log_statement >= l_log_current_level) THEN
695 fnd_log.string(l_log_statement,L_DEBUG_KEY,'p_warranty_vendor_id count : '||p_warranty_vendor_id_tbl.COUNT);
696 END IF;
697
698 -- Logic
699 FORALL i in p_warranty_vendor_id_tbl.first .. p_warranty_vendor_id_tbl.last
700 UPDATE AHL_WARRANTY_TEMPLATES_B SET ENABLED_FLAG = 'N',
701 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
702 LAST_UPDATE_DATE = sysdate,
703 LAST_UPDATED_BY = l_user_id,
704 LAST_UPDATE_LOGIN = l_login_id
705 WHERE warranty_vendor_id = p_warranty_vendor_id_tbl(i)
706 AND ENABLED_FLAG = 'Y' ;
707 --JKJain, 30 Nov 2010
708 -- Instead of vendor_id, now changed the API to take table of vendor_id, as per the new request.
709 /*
710 --Cursor For LOOP
711 FOR csr_index in update_enabled_flag_csr(p_warranty_vendor_id)
712 LOOP
713
714
715 UPDATE AHL_WARRANTY_TEMPLATES_B SET ENABLED_FLAG = 'N',
716 OBJECT_VERSION_NUMBER = csr_index.object_version_number+1,
717 LAST_UPDATE_DATE = sysdate,
718 LAST_UPDATED_BY = l_user_id,
719 LAST_UPDATE_LOGIN = l_login_id,
720 SECURITY_GROUP_ID = NULL
721 WHERE WARRANTY_TEMPLATE_ID = csr_index.warranty_template_id;
722
723 END LOOP;
724 */
725 -- Standard check of p_commit
726 IF FND_API.TO_BOOLEAN(p_commit) THEN
727 COMMIT WORK;
728 END IF;
729
730 IF (l_log_procedure >= l_log_current_level)THEN
731 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure');
732 END IF;
733
734
735 EXCEPTION
736 WHEN FND_API.G_EXC_ERROR THEN
737 x_return_status := FND_API.G_RET_STS_ERROR;
738 ROLLBACK TO Disable_Tmpls_For_Vendor_Pvt;
739 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
740 p_data => x_msg_data,
741 p_encoded => fnd_api.g_false);
742
743
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 ROLLBACK TO Disable_Tmpls_For_Vendor_Pvt;
747 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
748 p_data => x_msg_data,
749 p_encoded => fnd_api.g_false);
750
751
752 WHEN OTHERS THEN
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 ROLLBACK TO Disable_Tmpls_For_Vendor_Pvt;
755 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
756 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
757 p_procedure_name => 'Disable_Templates_For_Vendor',
758 p_error_text => SUBSTR(SQLERRM,1,500));
759 END IF;
760 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
761 p_data => x_msg_data,
762 p_encoded => fnd_api.g_false);
763
764 END Disable_Templates_For_Vendor;
765
766 -------------------------------------------------------------------
767 -- Procedure name : Process_Attachments
768 -- Type : Private
769 --
770 --
771 -- Function :To Copy Attachments and Delete Attachements.
772 --
773 --
774 -- Pre-reqs :
775 -- Parameters :
776 --
777 -- Standard IN Parameters :
778 -- p_api_version IN NUMBER Required
779 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
780 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
781 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
782 --
783 -- Standard OUT Parameters :
784 -- x_return_status OUT VARCHAR2 Required
785 -- x_msg_count OUT NUMBER Required
786 -- x_msg_data OUT VARCHAR2 Required
787 --
788 -- Warranty Expiration Calculation Parameters:
789 -- p_entity_name IN VARCHAR2 Required
790 -- p_pk1_value IN NUMBER Required
791 -- p_copy_from_pk IN NUMBER, Required in case of Copy Attachments
792 -- p_copy_from_entity IN VARCHAR2,Required in case of Copy Attachments
793 -- p_dml_operation IN VARCHAR2, D for Delete, C for Copy
794 -- Version :
795 -- 15-Oct-2010 JKJain Initial Creation
796 -------------------------------------------------------------------
797 PROCEDURE Process_Attachments (
798 p_api_version IN NUMBER,
799 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
800 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
801 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
802 p_module_type IN VARCHAR2 := NULL,
803 p_entity_name IN VARCHAR2,
804 p_pk1_value IN NUMBER,
805 p_copy_from_entity IN VARCHAR2,
806 p_copy_from_pk IN NUMBER,
807 p_dml_operation IN VARCHAR2,
808 x_return_status OUT NOCOPY VARCHAR2,
809 x_msg_count OUT NOCOPY NUMBER,
810 x_msg_data OUT NOCOPY VARCHAR2
811 )
812 IS
813 --Standard local variables
814 L_API_NAME CONSTANT VARCHAR2(30) := 'Process_Attachments';
815 L_API_VERSION CONSTANT NUMBER := 1.0;
816 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
817 l_msg_data VARCHAR2(2000);
818 l_return_status VARCHAR2(1);
819 l_msg_count NUMBER;
820
821 l_user_id NUMBER := fnd_global.user_id;
822 l_login_id NUMBER := fnd_global.login_id;
823
824 l_row_char VARCHAR2(30);
825 l_attch_doc_id NUMBER;
826 l_doc_id NUMBER := null;
827 l_data_type NUMBER;
828 l_media_id NUMBER;
829 --Cursors
830
831 --fetch attachments details
832 CURSOR attachment_csr (c_entity_name IN VARCHAR2, c_pk1_value IN NUMBER)
833 IS
834 SELECT attach.*,docs.datatype_id
835 FROM fnd_attached_documents attach, fnd_documents docs
836 WHERE entity_name = c_entity_name
837 AND pk1_value = c_pk1_value
838 AND docs.document_id = attach.document_id;
839
840 --fetch documents details
841 CURSOR document_csr (c_document_id IN NUMBER)
842 IS
843 SELECT *
844 FROM fnd_documents_vl docs
845 WHERE document_id = c_document_id;
846
847 document_rec document_csr%rowtype;
848
849 --fetch File details
850 CURSOR file_csr (c_file_id IN NUMBER)
851 IS
852 SELECT *
853 FROM fnd_lobs
854 WHERE file_id = c_file_id;
855
856 file_rec file_csr%rowtype;
857
858 BEGIN
859
860 IF (l_log_procedure >= l_log_current_level) THEN
861 fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure');
862 END IF;
863
864 -- Standard start of API savepoint
865 SAVEPOINT Process_Attachments_Pvt;
866
867 -- Initialize message list if p_init_msg_list is set to TRUE
868 IF FND_API.To_Boolean(p_init_msg_list) THEN
869 FND_MSG_PUB.Initialize;
870 END IF;
871
872 -- Initialize API return status to success
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 -- Standard call to check for call compatibility.
876 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
877 p_api_version,
878 l_api_name,G_PKG_NAME) THEN
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881
882 -- Basic Validation
883
884 -- Check for Required Parameters
885 IF(p_dml_operation IS NULL OR p_entity_name IS NULL OR p_pk1_value IS NULL) THEN
886 FND_MESSAGE.Set_Name('AHL','AHL_SW_ATTCH_INPUT_INV');
887 FND_MSG_PUB.ADD;
888 IF (l_log_unexpected >= l_log_current_level)THEN
889 fnd_log.string
890 (
891 l_log_unexpected,
892 'ahl.plsql.Process_Attachments',
893 'For this API flow, mandatory input are p_entity_name = ' || p_entity_name || ' p_dml_operation = '||p_dml_operation||' p_pk1_value = '||p_pk1_value
894 );
895 END IF;
896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
897 END IF;
898
899 IF (l_log_statement >= l_log_current_level) THEN
900 fnd_log.string(l_log_statement,L_DEBUG_KEY,'mandatory input are p_entity_name = ' || p_entity_name ||
901 ' p_dml_operation = '||p_dml_operation||' p_pk1_value = '||p_pk1_value || ' p_copy_from_pk = ' || p_copy_from_pk ||
902 ' p_copy_from_entity = '||p_copy_from_entity);
903 END IF;
904
905 -- Logic
906
907 IF('D' = p_dml_operation)THEN
908
909 --Cursor For LOOP
910 FOR csr_index in attachment_csr(p_entity_name,p_pk1_value)
911 LOOP
912
913 fnd_documents_pkg.Delete_Row(csr_index.document_id,
914 csr_index.datatype_id,
915 'Y');
916
917 END LOOP;
918
919 ELSIF('C' = p_dml_operation) THEN
920
921 -- Copy from p_copy_from_pk
922 IF(p_copy_from_pk IS NULL OR p_copy_from_entity IS NULL)THEN
923 FND_MESSAGE.Set_Name('AHL','AHL_SW_ATTCH_INPUT_INV');
924 FND_MSG_PUB.ADD;
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 END IF;
927
928 FOR csr_index in attachment_csr(p_copy_from_entity,p_copy_from_pk)
929 LOOP
930
931 document_rec := null;
932 l_row_char :=null;
933 l_doc_id := null;
934 l_attch_doc_id := null;
935 l_data_type := null;
936 l_media_id :=null;
937
938 OPEN document_csr(csr_index.document_id);
939 FETCH document_csr into document_rec;
940
941 IF (document_csr%NOTFOUND) THEN
942 CLOSE document_csr;
943 CONTINUE;
944 END IF;
945
946 CLOSE document_csr;
947
948 SELECT fnd_attached_documents_s.NEXTVAL into l_attch_doc_id from dual;
949
950 -- if datatype_id = 6 X_Media_ID will be returned after inserting into fnd_documents only if we pass X_MEdia_ID as NULL,
951 --And we will need to explicitly insert data in fnd_lobs
952 l_data_type := document_rec.datatype_id;
953 l_media_id := document_rec.media_id;
954 IF( l_data_type = 6)THEN
955 l_media_id := null;
956 END IF;
957
958 IF (l_log_statement >= l_log_current_level) THEN
959 fnd_log.string(l_log_statement,L_DEBUG_KEY,'JKJ, fetched values are : csr_index.document_id = ' || csr_index.document_id ||
960 ' document_rec.media_id = '||document_rec.media_id||' document_rec.url = '||document_rec.url || ' document_rec.title = ' || document_rec.title ||
961 ' csr_index.datatype_id = '||csr_index.datatype_id);
962 END IF;
963
964 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
965 X_Rowid =>l_row_char,
966 X_attached_document_id => l_attch_doc_id,
967 X_document_id => l_doc_id,
968 X_creation_date => sysdate,
969 X_created_by => l_user_id,
970 X_last_update_date => sysdate,
971 X_last_updated_by => l_user_id,
972 X_last_update_login => l_login_id ,
973 X_seq_num => csr_index.seq_num,
974 X_entity_name =>p_entity_name,
975 X_column1 =>null,
976 X_pk1_value =>p_pk1_value,
977 X_pk2_value =>null,
978 X_pk3_value =>null,
979 X_pk4_value =>null,
980 X_pk5_value =>null,
981 X_automatically_added_flag => 'N',
982 X_request_id =>null,
983 X_program_application_id =>null,
984 X_program_id =>null,
985 X_program_update_date =>null,
986 X_Attribute_Category =>csr_index.Attribute_Category,
987 X_Attribute1 =>csr_index.Attribute1,
988 X_Attribute2 =>csr_index.Attribute2,
989 X_Attribute3 =>csr_index.Attribute3,
990 X_Attribute4 =>csr_index.Attribute4,
991 X_Attribute5 =>csr_index.Attribute5,
992 X_Attribute6 =>csr_index.Attribute6,
993 X_Attribute7 =>csr_index.Attribute7,
994 X_Attribute8 =>csr_index.Attribute8,
995 X_Attribute9 =>csr_index.Attribute9,
996 X_Attribute10 =>csr_index.Attribute10,
997 X_Attribute11 =>csr_index.Attribute11,
998 X_Attribute12 =>csr_index.Attribute12,
999 X_Attribute13 =>csr_index.Attribute13,
1000 X_Attribute14 =>csr_index.Attribute14,
1001 X_Attribute15 =>csr_index.Attribute15,
1002 /* columns necessary for creating a document on the fly */
1003 X_datatype_id =>l_data_type,
1004 X_category_id =>csr_index.category_id,
1005 X_security_type =>document_rec.security_type, --csr_index.security_type,
1006 X_security_id =>document_rec.security_id,
1007 X_publish_flag =>document_rec.publish_flag,
1008 X_image_type =>document_rec.image_type,
1009 X_storage_type =>document_rec.storage_type,
1010 X_usage_type => 'O',
1011 X_language =>userenv('LANG'),
1012 X_description =>document_rec.description,
1013 X_file_name =>document_rec.file_name,
1014 X_media_id =>l_media_id,
1015 X_doc_Attribute_Category =>document_rec.doc_Attribute_Category,
1016 X_doc_Attribute1 =>document_rec.doc_Attribute1,
1017 X_doc_Attribute2 =>document_rec.doc_Attribute2,
1018 X_doc_Attribute3 =>document_rec.doc_Attribute3,
1019 X_doc_Attribute4 =>document_rec.doc_Attribute4,
1020 X_doc_Attribute5 =>document_rec.doc_Attribute5,
1021 X_doc_Attribute6 =>document_rec.doc_Attribute6,
1022 X_doc_Attribute7 =>document_rec.doc_Attribute7,
1023 X_doc_Attribute8 =>document_rec.doc_Attribute8,
1024 X_doc_Attribute9 =>document_rec.doc_Attribute9,
1025 X_doc_Attribute10 =>document_rec.doc_Attribute10,
1026 X_doc_Attribute11 =>document_rec.doc_Attribute11,
1027 X_doc_Attribute12 =>document_rec.doc_Attribute12,
1028 X_doc_Attribute13 =>document_rec.doc_Attribute13,
1029 X_doc_Attribute14 =>document_rec.doc_Attribute14,
1030 X_doc_Attribute15 =>document_rec.doc_Attribute15,
1031 X_create_doc =>'Y',
1032 X_url =>document_rec.url,
1033 X_title =>document_rec.title);
1034
1035 IF (l_log_statement >= l_log_current_level) THEN
1036 fnd_log.string(l_log_statement,L_DEBUG_KEY,'JKJ, After insersion : l_doc_id = ' || l_doc_id || ' l_attch_doc_id = '||l_attch_doc_id||' document_rec.image_type = '||document_rec.image_type );
1037 END IF;
1038
1039 -- datatype_id = 1 for Short Text (Taken care in FND_DOCUMENTS_PKG.Insert_Row if Media_Id is passed)
1040 -- datatype_id = 5 for url (Taken care directly if url is passed)
1041 -- datatype_id = 6 for file (X_Media_ID will be returned after inserting into fnd_documents, But need to inserddata in fnd_lobs)
1042 IF(l_data_type = 6)THEN
1043
1044 file_rec := null;
1045
1046 OPEN file_csr(document_rec.media_id);
1047 FETCH file_csr into file_rec;
1048 CLOSE file_csr;
1049
1050 insert into fnd_lobs
1051 ( file_id,
1052 file_name,
1053 file_content_type,
1054 upload_date,
1055 expiration_date,
1056 program_name,
1057 program_tag,
1058 file_data,
1059 language,
1060 oracle_charset,
1061 file_format
1062 )
1063 values
1064 ( l_media_id,
1065 file_rec.file_name,
1066 file_rec.file_content_type,
1067 sysdate,
1068 null,
1069 null,
1070 null,
1071 file_rec.file_data,
1072 userenv('LANG'),
1073 file_rec.oracle_charset,
1074 file_rec.file_format
1075 );
1076
1077
1078 END IF;
1079
1080 END LOOP;
1081
1082 ELSIF ('U' = p_dml_operation) THEN
1083
1084 --Code not required
1085 null;
1086
1087 END IF;
1088
1089
1090
1091 -- Standard check of p_commit
1092 IF FND_API.TO_BOOLEAN(p_commit) THEN
1093 COMMIT WORK;
1094 END IF;
1095
1096 IF (l_log_procedure >= l_log_current_level)THEN
1097 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure');
1098 END IF;
1099
1100
1101 EXCEPTION
1102 WHEN FND_API.G_EXC_ERROR THEN
1103 x_return_status := FND_API.G_RET_STS_ERROR;
1104 ROLLBACK TO Process_Attachments_Pvt;
1105 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1106 p_data => x_msg_data,
1107 p_encoded => fnd_api.g_false);
1108
1109
1110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1112 ROLLBACK TO Process_Attachments_Pvt;
1113 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1114 p_data => x_msg_data,
1115 p_encoded => fnd_api.g_false);
1116
1117
1118 WHEN OTHERS THEN
1119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1120 ROLLBACK TO Process_Attachments_Pvt;
1121 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1122 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1123 p_procedure_name => 'Process_Attachments',
1124 p_error_text => SUBSTR(SQLERRM,1,500));
1125 END IF;
1126 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1127 p_data => x_msg_data,
1128 p_encoded => fnd_api.g_false);
1129
1130 END Process_Attachments;
1131
1132 -------------------------------------------------------------------
1133 -- Procedure name : Insert_Turnover_Notes
1134 -- Type : Private
1135 --
1136 --
1137 -- Function :To Insert Turnover Notes into JTF Notes.
1138 --
1139 --
1140 -- Pre-reqs :
1141 -- Parameters :
1142 --
1143 -- Standard IN Parameters :
1144 -- p_api_version IN NUMBER Required
1145 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
1146 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1147 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1148 -- p_default IN VARCHAR2 Default FND_API.G_FALSE
1149 -- p_module_type IN VARCHAR2 Default Null
1150 -- Standard OUT Parameters :
1151 -- x_return_status OUT VARCHAR2 Required
1152 -- x_msg_count OUT NUMBER Required
1153 -- x_msg_data OUT VARCHAR2 Required
1154 --
1155 -- Insert Turnover Notes Parameters:
1156 -- p_trunover_notes_tbl IN OUT AHL_WARRANTY_UTILS_PVT.Turnover_Notes_Tbl_Type Required
1157 --
1158 -- Version :
1159 -- 06-Oct-2010 SUKHWSIN Initial Creation
1160 -------------------------------------------------------------------
1161
1162 PROCEDURE Insert_Turnover_Notes (
1163 p_api_version IN NUMBER := 1.0 ,
1164 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1165 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1166 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1167 p_default IN VARCHAR2 := FND_API.G_FALSE,
1168 p_module_type IN VARCHAR2 := Null,
1169 x_return_status OUT NOCOPY VARCHAR2,
1170 x_msg_count OUT NOCOPY NUMBER,
1171 x_msg_data OUT NOCOPY VARCHAR2,
1172 p_trunover_notes_tbl IN OUT NOCOPY AHL_WARRANTY_UTILS_PVT.Turnover_Notes_Tbl_Type
1173 )
1174 IS
1175 l_api_version CONSTANT NUMBER := 1.0;
1176 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Turnover_Notes';
1177
1178 BEGIN
1179
1180 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1181 fnd_log.string (fnd_log.level_procedure,
1182 'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes.begin',
1183 'At the start of PLSQL procedure'
1184 );
1185 END IF;
1186
1187 -- Standard start of API savepoint
1188 SAVEPOINT Insert_Turnover_Notes;
1189
1190 -- Standard call to check for call compatibility
1191 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
1192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1193 END IF;
1194
1195 -- Initialize message list if p_init_msg_list is set to TRUE
1196 IF FND_API.To_Boolean( p_init_msg_list) THEN
1197 FND_MSG_PUB.Initialize;
1198 END IF;
1199
1200 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1201 fnd_log.string(
1202 fnd_log.level_statement,
1203 'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
1204 'p_init_message_list : ' || p_init_msg_list
1205 );
1206
1207 fnd_log.string(
1208 fnd_log.level_statement,
1209 'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
1210 'p_commit : ' || p_commit
1211 );
1212
1213 END IF;
1214
1215 -- Initialize API return status to success
1216 x_return_status := FND_API.G_RET_STS_SUCCESS;
1217
1218 -- Insert notes
1219 FOR i IN p_trunover_notes_tbl.FIRST..p_trunover_notes_tbl.LAST LOOP
1220 -- validate entered data
1221 IF(trunc(p_trunover_notes_tbl(i).entered_date) > trunc(SYSDATE))THEN
1222 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_NOTES_INV_ENT_DT');
1223 FND_MESSAGE.Set_Token('ENTERED_DATE',p_trunover_notes_tbl(i).entered_date);
1224 FND_MSG_PUB.ADD;
1225 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1226 fnd_log.string(
1227 fnd_log.level_error,
1228 'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
1229 'Invalid enterded date' || to_char(p_trunover_notes_tbl(i).entered_date)
1230 );
1231 END IF;
1232 END IF;
1233
1234 -- validate that notes cant not be null
1235 IF(p_trunover_notes_tbl(i).notes IS NULL)THEN
1236 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_NOTES_INV_NOTES_NLL');
1237 FND_MSG_PUB.ADD;
1238
1239 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1240 fnd_log.string(
1241 fnd_log.level_error,
1242 'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
1243 'Invalid enterded date' || to_char(p_trunover_notes_tbl(i).entered_date)
1244 );
1245 END IF;
1246 END IF;
1247
1248 IF p_trunover_notes_tbl(i).employee_name IS NULL THEN
1249 FND_MESSAGE.set_name('AHL','AHL_PRD_EMP_NULL_TRNTS');
1250 FND_MSG_PUB.ADD;
1251 END IF;
1252
1253 -- add notes if no messages
1254 IF(FND_MSG_PUB.count_msg = 0)THEN
1255 JTF_NOTES_PUB.Create_note(
1256 p_api_version => 1.0,
1257 p_init_msg_list => FND_API.G_FALSE,
1258 p_commit => FND_API.G_FALSE,
1259 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1260 x_return_status => x_return_status,
1261 x_msg_count => x_msg_count,
1262 x_msg_data => x_msg_data,
1263 p_source_object_id => p_trunover_notes_tbl(i).source_object_id,
1264 p_source_object_code => p_trunover_notes_tbl(i).source_object_code,
1265 p_notes => p_trunover_notes_tbl(i).notes,
1266 p_entered_by => p_trunover_notes_tbl(i).user_id,
1267 p_entered_date => p_trunover_notes_tbl(i).entered_date,
1268 x_jtf_note_id => p_trunover_notes_tbl(i).jtf_note_id
1269 );
1270 END IF;
1271
1272 END LOOP;
1273
1274 -- Check Error Message stack.
1275 x_msg_count := FND_MSG_PUB.count_msg;
1276 IF x_msg_count > 0 THEN
1277 RAISE FND_API.G_EXC_ERROR;
1278 END IF;
1279
1280 -- Standard check of p_commit
1281 IF FND_API.TO_BOOLEAN(p_commit) THEN
1282 COMMIT WORK;
1283 END IF;
1284
1285 -- Standard call to get message count and if count is 1, get message info
1286 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1287 p_data => x_msg_data,
1288 p_encoded => fnd_api.g_false
1289 );
1290
1291 IF(x_msg_count > 0 )THEN
1292 RAISE FND_API.G_EXC_ERROR;
1293 END IF;
1294
1295 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1296 fnd_log.string(
1297 fnd_log.level_procedure,
1298 'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes.end',
1299 'At the end of PLSQL procedure'
1300 );
1301
1302 END IF;
1303
1304 EXCEPTION
1305
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 Rollback to Insert_Turnover_Notes;
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1310 p_data => x_msg_data,
1311 p_encoded => fnd_api.g_false);
1312
1313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314 Rollback to Insert_Turnover_Notes;
1315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1316 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1317 p_data => x_msg_data,
1318 p_encoded => fnd_api.g_false);
1319
1320 WHEN OTHERS THEN
1321 Rollback to Insert_Turnover_Notes;
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1323 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1324 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1325 p_procedure_name => l_api_name,
1326 p_error_text => SUBSTRB(SQLERRM,1,500));
1327 END IF;
1328 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1329 p_data => x_msg_data,
1330 p_encoded => fnd_api.g_false);
1331 END Insert_Turnover_Notes;
1332
1333 ------------------------------------------------------------------------------------
1334 -- Start of Comments
1335 -- Function name : Is_Warranty_Edit_Allowed
1336 -- Type : Public
1337 -- Function : Checks if the edit warranty security function is allowed for the
1338 -- logged in user or not.
1339 -- Returns 'Y' is allowed, 'N' otherwsie.
1340 --
1341 -- Pre-reqs :
1342 -- Parameters :
1343 --
1344 --
1345 -- End of Comments
1346
1347 FUNCTION Is_Warranty_Edit_Allowed
1348 RETURN VARCHAR2 IS
1349 --
1350 l_ret_val VARCHAR2(1) := 'N';
1351 --
1352 BEGIN
1353 -- check the security context of the function
1354 IF (FND_FUNCTION.test('AHL_WARRANTY_EDIT_ALLOWED')) THEN
1355 l_ret_val := 'Y';
1356 END IF;
1357
1358 -- return the value
1359 RETURN l_ret_val;
1360 END Is_Warranty_Edit_Allowed;
1361
1362 END AHL_WARRANTY_UTILS_PVT;