[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_SERVICE_K_INT_WF
Source
1 PACKAGE BODY OKL_AM_SERVICE_K_INT_WF AS
2 /* $Header: OKLRKWFB.pls 120.2.12010000.3 2009/10/01 21:56:14 rmunjulu ship $ */
3
4 -- Rec Type to get and set notification body details
5 TYPE noti_rec_type IS RECORD (
6 success_main_body FND_NEW_MESSAGES.message_text%TYPE,
7 error_main_body FND_NEW_MESSAGES.message_text%TYPE,
8 termination_main_body FND_NEW_MESSAGES.message_text%TYPE,
9 return_main_body FND_NEW_MESSAGES.message_text%TYPE,
10 dispose_main_body FND_NEW_MESSAGES.message_text%TYPE,
11 scrap_main_body FND_NEW_MESSAGES.message_text%TYPE,
12 contract_dtls FND_NEW_MESSAGES.message_text%TYPE,
13 lease_contract FND_NEW_MESSAGES.message_text%TYPE,
14 service_contract FND_NEW_MESSAGES.message_text%TYPE,
15 termination_date FND_NEW_MESSAGES.message_text%TYPE,
16 asset_ret_date FND_NEW_MESSAGES.message_text%TYPE,
17 asset_disp_date FND_NEW_MESSAGES.message_text%TYPE,
18 schedule_assets FND_NEW_MESSAGES.message_text%TYPE,
19 asset_num FND_NEW_MESSAGES.message_text%TYPE,
20 item FND_NEW_MESSAGES.message_text%TYPE,
21 item_description FND_NEW_MESSAGES.message_text%TYPE,
22 reference_num FND_NEW_MESSAGES.message_text%TYPE,
23 serial_num FND_NEW_MESSAGES.message_text%TYPE,
24 quantity FND_NEW_MESSAGES.message_text%TYPE,
25 body_end FND_NEW_MESSAGES.message_text%TYPE);
26
27
28 -- Start of comments
29 --
30 -- Procedure Name : set_message
31 -- Desciption : Sets the message with tokens
32 -- Does NOT put the message on the message stack
33 -- This set_message is used instead of the standard OKL_API.set_message
34 -- because the OKL_API.set_message puts the message on message stack after
35 -- which it cannot be retrieved using FND_MESSAGE.get
36 -- Business Rules :
37 -- Parameters :
38 -- Version : 1.0
39 -- History : RMUNJULU created
40 --
41 -- End of comments
42 PROCEDURE set_message (
43 p_app_name IN VARCHAR2 DEFAULT G_APP_NAME,
44 p_msg_name IN VARCHAR2,
45 p_token1 IN VARCHAR2 DEFAULT NULL,
46 p_token1_value IN VARCHAR2 DEFAULT NULL,
47 p_token2 IN VARCHAR2 DEFAULT NULL,
48 p_token2_value IN VARCHAR2 DEFAULT NULL,
49 p_token3 IN VARCHAR2 DEFAULT NULL,
50 p_token3_value IN VARCHAR2 DEFAULT NULL,
51 p_token4 IN VARCHAR2 DEFAULT NULL,
52 p_token4_value IN VARCHAR2 DEFAULT NULL,
53 p_token5 IN VARCHAR2 DEFAULT NULL,
54 p_token5_value IN VARCHAR2 DEFAULT NULL,
55 p_token6 IN VARCHAR2 DEFAULT NULL,
56 p_token6_value IN VARCHAR2 DEFAULT NULL,
57 p_token7 IN VARCHAR2 DEFAULT NULL,
58 p_token7_value IN VARCHAR2 DEFAULT NULL,
59 p_token8 IN VARCHAR2 DEFAULT NULL,
60 p_token8_value IN VARCHAR2 DEFAULT NULL,
61 p_token9 IN VARCHAR2 DEFAULT NULL,
62 p_token9_value IN VARCHAR2 DEFAULT NULL,
63 p_token10 IN VARCHAR2 DEFAULT NULL,
64 p_token10_value IN VARCHAR2 DEFAULT NULL ) IS
65
66 BEGIN
67
68 FND_MESSAGE.set_name( P_APP_NAME, P_MSG_NAME);
69
70 IF (p_token1 IS NOT NULL) AND (p_token1_value IS NOT NULL) THEN
71 FND_MESSAGE.set_token( TOKEN => p_token1,
72 VALUE => p_token1_value);
73 END IF;
74
75 IF (p_token2 IS NOT NULL) AND (p_token2_value IS NOT NULL) THEN
76 FND_MESSAGE.set_token( TOKEN => p_token2,
77 VALUE => p_token2_value);
78 END IF;
79
80 IF (p_token3 IS NOT NULL) AND (p_token3_value IS NOT NULL) THEN
81 FND_MESSAGE.set_token( TOKEN => p_token3,
82 VALUE => p_token3_value);
83 END IF;
84
85 IF (p_token4 IS NOT NULL) AND (p_token4_value IS NOT NULL) THEN
86 FND_MESSAGE.set_token( TOKEN => p_token4,
87 VALUE => p_token4_value);
88 END IF;
89
90 IF (p_token5 IS NOT NULL) AND (p_token5_value IS NOT NULL) THEN
91 FND_MESSAGE.set_token( TOKEN => p_token5,
92 VALUE => p_token5_value);
93 END IF;
94
95 IF (p_token6 IS NOT NULL) AND (p_token6_value IS NOT NULL) THEN
96 FND_MESSAGE.set_token( TOKEN => p_token6,
97 VALUE => p_token6_value);
98 END IF;
99 IF (p_token7 IS NOT NULL) AND (p_token7_value IS NOT NULL) THEN
100 FND_MESSAGE.set_token( TOKEN => p_token7,
101 VALUE => p_token7_value);
102 END IF;
103
104 IF (p_token8 IS NOT NULL) AND (p_token8_value IS NOT NULL) THEN
105 FND_MESSAGE.set_token( TOKEN => p_token8,
106 VALUE => p_token8_value);
107 END IF;
108 IF (p_token9 IS NOT NULL) AND (p_token9_value IS NOT NULL) THEN
109 FND_MESSAGE.set_token( TOKEN => p_token9,
110 VALUE => p_token9_value);
111 END IF;
112
113 IF (p_token10 IS NOT NULL) AND (p_token10_value IS NOT NULL) THEN
114 FND_MESSAGE.set_token( TOKEN => p_token10,
115 VALUE => p_token10_value);
116 END IF;
117
118 --FND_MSG_PUB.add;
119
120 END set_message;
121
122
123 -- Start of comments
124 --
125 -- Procedure Name : check_asset_scrapped
126 -- Desciption : Checks if there is a RETURN with type SCRAPPED
127 -- Business Rules :
128 -- Parameters :
129 -- Version : 1.0
130 -- History : RMUNJULU created
131 --
132 -- End of comments
133 FUNCTION check_asset_scrapped (p_asset_id IN NUMBER) RETURN VARCHAR2 IS
134
135 -- Get Asset Return Status Code for kle_id
136 CURSOR get_return_dtls_csr(p_kle_id IN NUMBER) IS
137 SELECT ART.ARS_CODE
138 FROM OKL_ASSET_RETURNS_B ART
139 WHERE ART.kle_id = p_kle_id
140 AND ART.ARS_CODE <> 'CANCELLED';
141
142 l_scrapped_yn VARCHAR2(1) := 'N';
143
144 BEGIN
145
146 FOR get_return_dtls_rec IN get_return_dtls_csr(p_asset_id) LOOP
147
148 IF get_return_dtls_rec.ars_code = 'SCRAPPED' THEN
149
150 l_scrapped_yn := 'Y';
151
152 END IF;
153
154 END LOOP;
155
156 RETURN l_scrapped_yn;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN NULL;
161 END check_asset_scrapped;
162
163
164 -- Start of comments
165 --
166 -- Procedure Name : get_set_noti_dtls
167 -- Desciption : Gets the notification attributes and Sets the notification
168 -- messages. Need this procedure to make the message translatable
169 -- Business Rules :
170 -- Parameters :
171 -- Version : 1.0
172 -- History : RMUNJULU created
173 --
174 -- End of comments
175 PROCEDURE get_set_noti_dtls (
176 p_itemtype IN VARCHAR2,
177 p_itemkey IN VARCHAR2,
178 x_noti_rec OUT NOCOPY noti_rec_type) IS
179
180
181 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
182 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
183 l_dispose_date DATE;
184 l_return_date DATE;
185 l_term_date DATE;
186
187 l_noti_rec noti_rec_type;
188
189 BEGIN
190
191 -- ********
192 -- Get the Attribute values
193 -- ********
194
195 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
196 itemtype => p_itemtype,
197 itemkey => p_itemkey,
198 aname => 'OKL_CONTRACT_NUMBER');
199
200 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
201 itemtype => p_itemtype,
202 itemkey => p_itemkey,
203 aname => 'OKS_CONTRACT_NUMBER');
204
205 l_dispose_date := WF_ENGINE.GetItemAttrDate(
206 itemtype => p_itemtype,
207 itemkey => p_itemkey,
208 aname => 'DISPOSAL_DATE');
209
210 l_return_date := WF_ENGINE.GetItemAttrDate(
211 itemtype => p_itemtype,
212 itemkey => p_itemkey,
213 aname => 'RETURN_DATE');
214
215
216 l_term_date := WF_ENGINE.GetItemAttrDate(
217 itemtype => p_itemtype,
218 itemkey => p_itemkey,
219 aname => 'TERMINATION_DATE');
220
221 -- ********
222 -- Set the Notification body texts
223 -- ********
224
225 -- Success Notifications main body
226 set_message(
227 p_app_name => G_APP_NAME,
228 p_msg_name => 'OKL_AM_SRV_SUCCESS_MAIN_BODY',
229 p_token1 => 'LEASE_CONTRACT',
230 p_token1_value => l_okl_contract_number,
231 p_token2 => 'TERMINATION_DATE',
232 p_token2_value => l_term_date,
233 p_token3 => 'SERVICE_CONTRACT',
234 p_token3_value => l_oks_contract_number);
235
236 l_noti_rec.success_main_body := FND_MESSAGE.get;
237
238 -- Error Notifications main body
239 set_message(
240 p_app_name => G_APP_NAME,
241 p_msg_name => 'OKL_AM_SRV_ERROR_MAIN_BODY',
242 p_token1 => 'LEASE_CONTRACT',
243 p_token1_value => l_okl_contract_number,
244 p_token2 => 'TERMINATION_DATE',
245 p_token2_value => l_term_date,
246 p_token3 => 'SERVICE_CONTRACT',
247 p_token3_value => l_oks_contract_number);
248
249 l_noti_rec.error_main_body := FND_MESSAGE.get;
250
251 -- Termination Notifications main body
252 set_message(
253 p_app_name => G_APP_NAME,
254 p_msg_name => 'OKL_AM_SRV_TERM_MAIN_BODY',
255 p_token1 => 'LEASE_CONTRACT',
256 p_token1_value => l_okl_contract_number,
257 p_token2 => 'TERMINATION_DATE',
258 p_token2_value => l_term_date,
259 p_token3 => 'SERVICE_CONTRACT',
260 p_token3_value => l_oks_contract_number);
261
262 l_noti_rec.termination_main_body := FND_MESSAGE.get;
263
264 -- Return Notifications main body
265 set_message(
266 p_app_name => G_APP_NAME,
267 p_msg_name => 'OKL_AM_SRV_RETURN_MAIN_BODY',
268 p_token1 => 'SERVICE_CONTRACT',
269 p_token1_value => l_oks_contract_number,
270 p_token2 => 'RETURN_DATE',
271 p_token2_value => l_return_date);
272
273 l_noti_rec.return_main_body := FND_MESSAGE.get;
274
275 -- Dispose Notifications main body
276 set_message(
277 p_app_name => G_APP_NAME,
278 p_msg_name => 'OKL_AM_SRV_DISPOSE_MAIN_BODY',
279 p_token1 => 'SERVICE_CONTRACT',
280 p_token1_value => l_oks_contract_number,
281 p_token2 => 'DISPOSE_DATE',
282 p_token2_value => l_dispose_date);
283
284 l_noti_rec.dispose_main_body := FND_MESSAGE.get;
285
286 -- Dispose Notifications main body -- If Scrapped
287 set_message(
288 p_app_name => G_APP_NAME,
289 p_msg_name => 'OKL_AM_SRV_SCRAP_MAIN_BODY',
290 p_token1 => 'SERVICE_CONTRACT',
291 p_token1_value => l_oks_contract_number,
292 p_token2 => 'DISPOSE_DATE',
293 p_token2_value => l_dispose_date);
294
295 l_noti_rec.scrap_main_body := FND_MESSAGE.get;
296
297 -- Contract Details
298 set_message(
299 p_app_name => G_APP_NAME,
300 p_msg_name => 'OKL_AM_SRV_CONTRACT_DTLS');
301
302 l_noti_rec.contract_dtls := FND_MESSAGE.get;
303
304 -- Lease Contract
305 set_message(
306 p_app_name => G_APP_NAME,
307 p_msg_name => 'OKL_AM_SRV_LEASE_CONTRACT');
308
309 l_noti_rec.lease_contract := FND_MESSAGE.get;
310
311 -- Service Contract
312 set_message(
313 p_app_name => G_APP_NAME,
314 p_msg_name => 'OKL_AM_SRV_SERVICE_CONTRACT');
315
316 l_noti_rec.service_contract := FND_MESSAGE.get;
317
318 -- Termination Date
319 set_message(
320 p_app_name => G_APP_NAME,
321 p_msg_name => 'OKL_AM_SRV_TERMINATION_DATE');
322
323 l_noti_rec.termination_date := FND_MESSAGE.get;
324
325 -- Asset Return Date
326 set_message(
327 p_app_name => G_APP_NAME,
328 p_msg_name => 'OKL_AM_SRV_ASSET_RET_DATE');
329
330 l_noti_rec.asset_ret_date := FND_MESSAGE.get;
331
332 -- Asset Sale Date
333 set_message(
334 p_app_name => G_APP_NAME,
335 p_msg_name => 'OKL_AM_SRV_ASSET_DISP_DATE');
336
337 l_noti_rec.asset_disp_date := FND_MESSAGE.get;
338
339 -- Schedule of Assets
340 set_message(
341 p_app_name => G_APP_NAME,
342 p_msg_name => 'OKL_AM_SRV_SCHEDULE_ASSETS');
343
344 l_noti_rec.schedule_assets := FND_MESSAGE.get;
345
346 -- Asset Number
347 set_message(
348 p_app_name => G_APP_NAME,
349 p_msg_name => 'OKL_AM_SRV_ASSET_NUM');
350
351 l_noti_rec.asset_num := FND_MESSAGE.get;
352
353 -- Item
354 set_message(
355 p_app_name => G_APP_NAME,
356 p_msg_name => 'OKL_AM_SRV_ITEM');
357
358 l_noti_rec.item := FND_MESSAGE.get;
359
360 -- Description
361 set_message(
362 p_app_name => G_APP_NAME,
363 p_msg_name => 'OKL_AM_SRV_ITEM_DESCRIPTION');
364
365 l_noti_rec.item_description := FND_MESSAGE.get;
366
367 -- Reference Number
368 set_message(
369 p_app_name => G_APP_NAME,
370 p_msg_name => 'OKL_AM_SRV_REFERENCE_NUM');
371
372 l_noti_rec.reference_num := FND_MESSAGE.get;
373
374 -- Serial Number
375 set_message(
376 p_app_name => G_APP_NAME,
377 p_msg_name => 'OKL_AM_SRV_SERIAL_NUM');
378
379 l_noti_rec.serial_num := FND_MESSAGE.get;
380
381 -- Quantity
382 set_message(
383 p_app_name => G_APP_NAME,
384 p_msg_name => 'OKL_AM_SRV_QUANTITY');
385
386 l_noti_rec.quantity := FND_MESSAGE.get;
387
388 -- Body End
389 set_message(
390 p_app_name => G_APP_NAME,
391 p_msg_name => 'OKL_AM_SRV_BODY_END');
392
393 l_noti_rec.body_end := FND_MESSAGE.get;
394
395 -- ********
396 -- Set the out parameter
397 -- ********
398
399 x_noti_rec := l_noti_rec;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 NULL;
404 END get_set_noti_dtls;
405
406
407 -- Start of comments
408 --
409 -- Procedure Name : get_assets_schedule
410 -- Desciption : Get the Asset Details when financial kle_id is supplied,
411 -- can return multiple records if serialized asset
412 -- Business Rules :
413 -- Parameters :
414 -- Version : 1.0
415 -- History : RMUNJULU created
416 --
417 -- End of comments
418 PROCEDURE get_assets_schedule (
419 p_kle_id IN NUMBER,
420 x_asset_schedule_tbl OUT NOCOPY kle_tbl_type) IS
421
422
423 -- Get the asset details, if serialized asset then will have multiple
424 -- rows returned for all serial numbers
425 -- p_kle_id : is serviced financial asset line with service contract link
426 CURSOR get_asset_dtls_csr (p_kle_id IN NUMBER) IS
427 SELECT CLET_FIN.name asset_number,
428 MTL.description item_number,
429 CLET_FIN.item_description item_description ,
430 CSI.instance_number install_base_number,
431 CSI.serial_number serial_number,
432 CSI.quantity asset_quantity
433 FROM CSI_ITEM_INSTANCES CSI,
434 OKC_K_ITEMS CIM_IB,
435 OKC_LINE_STYLES_B LSE_IB,
436 OKC_K_LINES_B CLE_IB,
437 OKC_LINE_STYLES_B LSE_INST,
438 OKC_K_LINES_B CLE_INST,
439 OKC_LINE_STYLES_B LSE_MODEL,
440 OKC_K_LINES_B CLE_MODEL,
441 OKC_K_ITEMS CIM_MODEL,
442 MTL_SYSTEM_ITEMS MTL,
443 OKC_LINE_STYLES_B LSE_FIN,
444 OKC_K_LINES_TL CLET_FIN,
445 OKC_K_LINES_B CLE_FIN
446 WHERE CLE_FIN.id = CLET_FIN.id
447 AND CLET_FIN.LANGUAGE = USERENV('LANG')
448 AND LSE_FIN.id = CLE_FIN.lse_id
449 AND LSE_FIN.lty_code = 'FREE_FORM1'
450 AND CLE_INST.cle_id = CLE_FIN.id
451 AND CLE_INST.dnz_chr_id = CLE_FIN.dnz_chr_id
452 AND CLE_INST.lse_id = LSE_INST.id
453 AND LSE_INST.lty_code = 'FREE_FORM2'
454 AND CLE_IB.cle_id = CLE_INST.id
455 AND CLE_IB.dnz_chr_id = CLE_FIN.dnz_chr_id
456 AND CLE_IB.lse_id = LSE_IB.id
457 AND LSE_IB.lty_code = 'INST_ITEM'
458 AND CIM_IB.cle_id = CLE_IB.id
459 AND CIM_IB.dnz_chr_id = CLE_IB.dnz_chr_id
460 AND CIM_IB.object1_id1 = CSI.instance_id (+)
461 -- AND CIM_IB.object1_id2 = '#'
462 AND CIM_IB.jtot_object1_code = 'OKX_IB_ITEM'
463 AND CLE_FIN.id = CLE_MODEL.cle_id
464 AND CLE_MODEL.lse_id = LSE_MODEL.id
465 AND LSE_MODEL.lty_code = 'ITEM'
466 AND CLE_MODEL.id = CIM_MODEL.cle_id
467 AND CIM_MODEL.object1_id1 = MTL.inventory_item_id
468 AND CIM_MODEL.object1_id2 = TO_NUMBER(MTL.organization_id)
469 AND CLE_FIN.id = p_kle_id;
470
471 l_asset_schedule_tbl kle_tbl_type;
472 i NUMBER := 1;
473
474 BEGIN
475
476 -- Get the asset details for the financial line passed and set kle_tbl
477 FOR get_asset_dtls_rec IN get_asset_dtls_csr(p_kle_id) LOOP
478
479 l_asset_schedule_tbl(i).asset_number := get_asset_dtls_rec.asset_number;
480 l_asset_schedule_tbl(i).item_number := get_asset_dtls_rec.item_number;
481 l_asset_schedule_tbl(i).item_description := get_asset_dtls_rec.item_description;
482 l_asset_schedule_tbl(i).install_base_number := get_asset_dtls_rec.install_base_number;
483 l_asset_schedule_tbl(i).serial_number := get_asset_dtls_rec.serial_number;
484 l_asset_schedule_tbl(i).asset_quantity := get_asset_dtls_rec.asset_quantity;
485
486 i := i + 1;
487 END LOOP;
488
489 -- Set the out tbl
490 x_asset_schedule_tbl := l_asset_schedule_tbl;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 NULL;
495 END get_assets_schedule;
496
497 -- Start of comments
498 --
499 -- Procedure Name : get_assets_schedule
500 -- Desciption : Get the Serviced Assets with Details when okl contract id is supplied,
501 -- Business Rules :
502 -- Parameters :
503 -- Version : 1.0
504 -- History : RMUNJULU created
505 -- : RMUNJULU 05-JAN-04 SERVICE K UPDATES, changed cursors
506 -- get_k_serviced_assets_csr and get_q_serviced_assets_csr -- added DISTINCT
507 -- since same asset can exist as multiple covered products in OKS if it is
508 -- serialized and then in that case duplicate records are shown which we want
509 -- to avoid
510 --
511 -- End of comments
512 PROCEDURE get_assets_schedule (
513 p_khr_id IN NUMBER,
514 p_quote_id IN NUMBER,
515 x_asset_schedule_tbl OUT NOCOPY kle_tbl_type) IS
516
517 -- Get all serviced assets for the contract
518 CURSOR get_k_serviced_assets_csr (p_khr_id IN NUMBER) IS
519 SELECT DISTINCT LNK_SRV_CIM.object1_id1 asset_id
520 FROM OKC_K_HEADERS_B OKS_CHRB,
521 OKC_LINE_STYLES_B OKS_COV_PD_LSE,
522 OKC_K_LINES_B OKS_COV_PD_CLEB,
523 OKC_K_REL_OBJS KREL,
524 OKC_LINE_STYLES_B LNK_SRV_LSE,
525 OKC_STATUSES_B LNK_SRV_STS,
526 OKC_K_LINES_B LNK_SRV_CLEB,
527 OKC_K_ITEMS LNK_SRV_CIM,
528 OKC_K_LINES_B FIN_LINE
529 WHERE OKS_CHRB.scs_code = 'SERVICE'
530 AND OKS_CHRB.id = OKS_COV_PD_CLEB.dnz_chr_id
531 AND OKS_COV_PD_CLEB.lse_id = OKS_COV_PD_LSE.id
532 AND OKS_COV_PD_LSE.lty_code = 'COVER_PROD'
533 AND '#' = KREL.object1_id2
534 AND OKS_COV_PD_CLEB.id = KREL.object1_id1
535 AND KREL.rty_code = 'OKLSRV'
536 AND KREL.cle_id = LNK_SRV_CLEB.id
537 AND LNK_SRV_CLEB.lse_id = LNK_SRV_LSE.id
538 AND LNK_SRV_LSE.lty_code = 'LINK_SERV_ASSET'
539 AND LNK_SRV_CLEB.sts_code = LNK_SRV_STS.code
540 AND LNK_SRV_CLEB.id = LNK_SRV_CIM.cle_id
541 AND LNK_SRV_CIM.jtot_object1_code = 'OKX_COVASST'
542 AND LNK_SRV_CIM.object1_id2 = '#'
543 AND LNK_SRV_CIM.object1_id1 = FIN_LINE.id
544 AND FIN_LINE.dnz_chr_id = p_khr_id;
545
546
547 -- Get all serviced assets for the quote
548 CURSOR get_q_serviced_assets_csr (p_qte_id IN NUMBER) IS
549 SELECT DISTINCT LNK_SRV_CIM.object1_id1 asset_id
550 FROM OKC_K_HEADERS_B OKS_CHRB,
551 OKC_LINE_STYLES_B OKS_COV_PD_LSE,
552 OKC_K_LINES_B OKS_COV_PD_CLEB,
553 OKC_K_REL_OBJS KREL,
554 OKC_LINE_STYLES_B LNK_SRV_LSE,
555 OKC_STATUSES_B LNK_SRV_STS,
556 OKC_K_LINES_B LNK_SRV_CLEB,
557 OKC_K_ITEMS LNK_SRV_CIM,
558 OKC_K_LINES_B FIN_LINE,
559 OKL_TXL_QUOTE_LINES_B TQL
560 WHERE OKS_CHRB.scs_code = 'SERVICE'
561 AND OKS_CHRB.id = OKS_COV_PD_CLEB.dnz_chr_id
562 AND OKS_COV_PD_CLEB.lse_id = OKS_COV_PD_LSE.id
563 AND OKS_COV_PD_LSE.lty_code = 'COVER_PROD'
564 AND '#' = KREL.object1_id2
565 AND OKS_COV_PD_CLEB.id = KREL.object1_id1
566 AND KREL.rty_code = 'OKLSRV'
567 AND KREL.cle_id = LNK_SRV_CLEB.id
568 AND LNK_SRV_CLEB.lse_id = LNK_SRV_LSE.id
569 AND LNK_SRV_LSE.lty_code = 'LINK_SERV_ASSET'
570 AND LNK_SRV_CLEB.sts_code = LNK_SRV_STS.code
571 AND LNK_SRV_CLEB.id = LNK_SRV_CIM.cle_id
572 AND LNK_SRV_CIM.jtot_object1_code = 'OKX_COVASST'
573 AND LNK_SRV_CIM.object1_id2 = '#'
574 AND LNK_SRV_CIM.object1_id1 = FIN_LINE.id
575 AND FIN_LINE.id = TQL.kle_id
576 AND TQL.qlt_code = 'AMCFIA'
577 AND TQL.qte_id = p_qte_id;
578
579
580 l_asset_id NUMBER;
581 l_temp_kle_tbl kle_tbl_type;
582 l_asset_schedule_tbl kle_tbl_type;
583 j NUMBER := 1;
584 k NUMBER := 1;
585
586 BEGIN
587
588 -- IF no Quote ID passed then Full Termination
589 IF p_quote_id IS NULL
590 OR p_quote_id = OKL_API.G_MISS_NUM THEN
591
592 -- Get the serviced assets for the contract
593 FOR get_k_serviced_assets_rec IN get_k_serviced_assets_csr(p_khr_id) LOOP
594
595 l_asset_id := get_k_serviced_assets_rec.asset_id;
596
597 -- Get the assets schedule for financial asset id
598 get_assets_schedule (
599 p_kle_id => l_asset_id,
600 x_asset_schedule_tbl => l_temp_kle_tbl);
601
602 IF l_temp_kle_tbl.COUNT > 0 THEN
603
604 -- loop thru asset details
605 FOR j IN l_temp_kle_tbl.FIRST..l_temp_kle_tbl.LAST LOOP
606
607 -- fill up the asset schedule table
608 l_asset_schedule_tbl(k).asset_number := l_temp_kle_tbl(j).asset_number;
609 l_asset_schedule_tbl(k).item_number := l_temp_kle_tbl(j).item_number;
610 l_asset_schedule_tbl(k).item_description := l_temp_kle_tbl(j).item_description;
611 l_asset_schedule_tbl(k).install_base_number := l_temp_kle_tbl(j).install_base_number;
612 l_asset_schedule_tbl(k).serial_number := l_temp_kle_tbl(j).serial_number;
613 l_asset_schedule_tbl(k).asset_quantity := l_temp_kle_tbl(j).asset_quantity;
614
615 k := k + 1;
616 END LOOP;
617 END IF;
618 END LOOP;
619 ELSE -- Quote Id passed - Get the Serviced Assets for the Quote Assets
620
621 -- Get the serviced assets for the contract
622 FOR get_q_serviced_assets_rec IN get_q_serviced_assets_csr(p_quote_id) LOOP
623
624 l_asset_id := get_q_serviced_assets_rec.asset_id;
625
626 -- Get the assets schedule for financial asset id
627 get_assets_schedule (
628 p_kle_id => l_asset_id,
629 x_asset_schedule_tbl => l_temp_kle_tbl);
630
631 IF l_temp_kle_tbl.COUNT > 0 THEN
632
633 -- loop thru asset details
634 FOR j IN l_temp_kle_tbl.FIRST..l_temp_kle_tbl.LAST LOOP
635
636 -- fill up the asset schedule table
637 l_asset_schedule_tbl(k).asset_number := l_temp_kle_tbl(j).asset_number;
638 l_asset_schedule_tbl(k).item_number := l_temp_kle_tbl(j).item_number;
639 l_asset_schedule_tbl(k).item_description := l_temp_kle_tbl(j).item_description;
640 l_asset_schedule_tbl(k).install_base_number := l_temp_kle_tbl(j).install_base_number;
641 l_asset_schedule_tbl(k).serial_number := l_temp_kle_tbl(j).serial_number;
642 l_asset_schedule_tbl(k).asset_quantity := l_temp_kle_tbl(j).asset_quantity;
643
644 k := k + 1;
645 END LOOP;
646 END IF;
647 END LOOP;
648 END IF;
649
650 -- set the out asset_schedule_tbl
651 x_asset_schedule_tbl := l_asset_schedule_tbl;
652
653 EXCEPTION
654 WHEN OTHERS THEN
655 NULL;
656 END get_assets_schedule;
657
658 -- Start of comments
659 --
660 -- Procedure Name : raise_service_k_int_event
661 -- Desciption : Raise the Service K Integration WF Process Event
662 -- Business Rules :
663 -- Parameters :
664 -- Version : 1.0
665 -- History : RMUNJULU created
666 -- : RMUNJULU 23-DEC-03 SERVICE K UPDATES
667 --
668 -- End of comments
669 PROCEDURE raise_service_k_int_event (
670 p_transaction_id IN VARCHAR2,
671 p_source IN VARCHAR2,
672 p_quote_id IN VARCHAR2 DEFAULT NULL,
673 p_oks_contract IN VARCHAR2 DEFAULT NULL, --RMUNJULU 23-DEC-03 SERVICE K UPDATES
674 p_transaction_date IN DATE) IS
675
676
677 l_parameter_list WF_PARAMETER_LIST_T;
678 l_key WF_ITEMS.item_key%TYPE;
679 l_event_name WF_EVENTS.NAME%TYPE := 'oracle.apps.okl.am.servicekintegration';
680 l_seq NUMBER;
681
682 -- Cursor to get the value of the sequence
683 CURSOR okl_key_csr IS
684 SELECT okl_wf_item_s.nextval
685 FROM DUAL;
686
687 BEGIN
688
689 SAVEPOINT raise_service_k_int_event;
690
691 OPEN okl_key_csr;
692 FETCH okl_key_csr INTO l_seq;
693 CLOSE okl_key_csr;
694
695 l_key := l_event_name ||l_seq ;
696
697 -- *******
698 -- Set the parameter list
699 -- *******
700
701 WF_EVENT.AddParameterToList('TRANSACTION_ID',
702 p_transaction_id,
703 l_parameter_list);
704
705 WF_EVENT.AddParameterToList('SOURCE',
706 p_source,
707 l_parameter_list);
708
709 IF p_quote_id IS NOT NULL THEN
710
711 WF_EVENT.AddParameterToList('QUOTE_ID',
712 p_quote_id,
713 l_parameter_list);
714
715 END IF;
716
717 --RMUNJULU 23-DEC-03 SERVICE K UPDATES Added code to set OKS contract ID
718 IF p_oks_contract IS NOT NULL THEN
719
720 WF_EVENT.AddParameterToList('OKS_CONTRACT_ID',
721 p_oks_contract,
722 l_parameter_list);
723
724 END IF;
725
726 WF_EVENT.AddParameterToList('TRANSACTION_DATE',
727 p_transaction_date,
728 l_parameter_list);
729 --added by akrangan
730 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
731
732 -- Raise Business Event
733 WF_EVENT.raise(
734 p_event_name => l_event_name,
735 p_event_key => l_key,
736 p_parameters => l_parameter_list);
737
738 l_parameter_list.DELETE;
739
740 EXCEPTION
741 WHEN OTHERS THEN
742 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
743 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
744 FND_MSG_PUB.ADD;
745 IF okl_key_csr%ISOPEN THEN
746 CLOSE okl_key_csr;
747 END IF;
748 ROLLBACK TO raise_service_k_int_event;
749
750 END raise_service_k_int_event;
751
752 -- Start of comments
753 --
754 -- Procedure Name : populate_attributes
755 -- Desciption : Populates the attributes for this WF
756 -- Business Rules :
757 -- Parameters :
758 -- Version : 1.0
759 -- History : RMUNJULU created
760 -- : RMUNJULU 3061751 Got the right OKL and OKS managers
761 -- : RMUNJULU 3061751 Set OKC APPROVER AS OKL and OKS managers
762 -- : RMUNJULU 3061751 Changed Date Format Mask
763 -- : RMUNJULU 23-DEC-03 SERVICE K UPDATES
764 --
765 -- End of comments
766 PROCEDURE populate_attributes(
767 itemtype IN VARCHAR2,
768 itemkey IN VARCHAR2,
769 actid IN NUMBER,
770 funcmode IN VARCHAR2,
771 resultout OUT NOCOPY VARCHAR2) IS
772
773
774 -- Get the OKL contract details
775 CURSOR okl_k_dtls_csr ( p_okl_khr_id IN NUMBER) IS
776 SELECT CHR.contract_number
777 FROM OKC_K_HEADERS_B CHR
778 WHERE CHR.id = p_okl_khr_id;
779
780 -- Get the OKS contract details
781 CURSOR oks_k_dtls_csr ( p_oks_khr_id IN NUMBER) IS
782 SELECT CHR.contract_number
783 FROM OKC_K_HEADERS_B CHR
784 WHERE CHR.id = p_oks_khr_id;
785
786 -- Get the asset details
787 CURSOR asset_dtls_csr ( p_id IN NUMBER) IS
788 SELECT CLE.dnz_chr_id
789 FROM OKC_K_LINES_B CLE
790 WHERE CLE.id = p_id;
791
792 l_source VARCHAR2(100);
793 l_transaction_id VARCHAR2(100);
794 l_quote_id VARCHAR2(100);
795 l_message VARCHAR2(2000);
796 l_okl_chr_id NUMBER;
797 l_oks_chr_id NUMBER;
798 l_okl_contract_number VARCHAR2(300);
799 l_oks_contract_number VARCHAR2(300);
800 l_asset_id NUMBER;
801 l_return_status VARCHAR2(3);
802 l_asset_return_date DATE;
803 l_asset_disposal_date DATE;
804 l_api_version CONSTANT NUMBER := G_API_VERSION;
805 l_msg_count NUMBER := G_MISS_NUM;
806 l_msg_data VARCHAR2(2000);
807 l_oks_agent VARCHAR2(200);
808 l_okl_agent VARCHAR2(200);
809 l_termination_date DATE;
810 l_transaction_date VARCHAR2(200);
811 l_message_subject VARCHAR2(300);
812 l_scrapped_yn VARCHAR2(1) := 'N';
813
814 BEGIN
815
816 --
817 -- THE DIFFERENT SOURCES AND CORRESPONDING NOTIFICATION DETAILS
818 --
819 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
820 -- SOURCE NOTIFICATION NAME TO TRANSACTION_ID
821 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
822 -- SUCCESS Service Contract de-linked from Lease Contract OKS Manager KHR_ID
823 -- ERROR Service Contract could not be de-linked from Lease Contract OKS + OKL Manager KHR_ID
824 -- TERMINATION Lease Contract terminated but Service Contract has not been de-linked OKS Manager KHR_ID
825 -- RETURN Serviced asset(s) returned by lessee OKS Manager ASSET_ID
826 -- DISPOSE Serviced asset(s) sold OKS Manager ASSET_ID
827 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
828
829 -- Get/Set the proper date format from contract/fnd profile
830
831 --
832 -- RUN mode
833 --
834 IF (funcmode = 'RUN') THEN
835
836 -- ********
837 -- Get the attribute values which are set
838 -- ********
839
840 l_source := WF_ENGINE.GetItemAttrText(
841 itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'SOURCE');
844
845
846 l_transaction_id := WF_ENGINE.GetItemAttrText(
847 itemtype => itemtype,
848 itemkey => itemkey,
849 aname => 'TRANSACTION_ID');
850
851 l_quote_id := WF_ENGINE.GetItemAttrText(
852 itemtype => itemtype,
853 itemkey => itemkey,
854 aname => 'QUOTE_ID');
855
856 l_transaction_date := WF_ENGINE.GetItemAttrText(
857 itemtype => itemtype,
858 itemkey => itemkey,
859 aname => 'TRANSACTION_DATE');
860
861 -- RMUNJULU 23-DEC-03 SERVICE K UPDATES
862 l_oks_chr_id := WF_ENGINE.GetItemAttrText(
863 itemtype => itemtype,
864 itemkey => itemkey,
865 aname => 'OKS_CONTRACT_ID');
866
867 -- *********
868 -- Get/Set the OKS AND OKL performing agents :
869 -- *********
870
871 -- RMUNJULU 3061751 Set OKC APPROVER AS OKL and OKS managers
872 l_oks_agent := FND_PROFILE.value('OKC_K_APPROVER'); -- OKS: Notify Contract Approver
873
874 IF l_oks_agent IS NULL THEN
875 l_oks_agent := 'SYSADMIN';
876 END IF;
877
878 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
879 itemkey => itemkey,
880 aname => 'OKS_PERFORMING_AGENT',
881 avalue => l_oks_agent);
882
883
884 l_okl_agent := FND_PROFILE.value('OKC_K_APPROVER'); -- OKL: Notify Contract Approver
885
886 IF l_okl_agent IS NULL THEN
887 l_okl_agent := 'SYSADMIN';
888 END IF;
889
890 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
891 itemkey => itemkey,
892 aname => 'OKL_PERFORMING_AGENT',
893 avalue => l_okl_agent);
894
895 -- ********
896 -- Set the variables
897 -- ********
898
899 IF l_source IN ('SUCCESS',
900 'ERROR',
901 'TERMINATION') THEN
902
903 -- Trn ID is LEASE KHR_ID
904 l_okl_chr_id := TO_NUMBER(l_transaction_id);
905
906 -- Trn_Date passed is Termination_Date
907 l_termination_date := TO_DATE(l_transaction_date,'DD/MM/RRRR');
908
909 IF l_source = 'SUCCESS' THEN
910
911 -- Success Message Subject
912 set_message(
913 p_app_name => G_APP_NAME,
914 p_msg_name => 'OKL_AM_SRV_SUCCESS_MSG_SUB');
915
916 l_message_subject := FND_MESSAGE.get;
917
918 ELSIF l_source = 'ERROR' THEN
919
920 -- Error Message Subject
921 set_message(
922 p_app_name => G_APP_NAME,
923 p_msg_name => 'OKL_AM_SRV_ERROR_MSG_SUB');
924
925 l_message_subject := FND_MESSAGE.get;
926
927 ELSIF l_source = 'TERMINATION' THEN
928
929 -- Termination Message Subject
930 set_message(
931 p_app_name => G_APP_NAME,
932 p_msg_name => 'OKL_AM_SRV_TERM_MSG_SUB');
933
934 l_message_subject := FND_MESSAGE.get;
935
936 END IF;
937
938 ELSIF l_source = 'DISPOSE' THEN
939
940 -- Trn_Id is Asset_Id
941 l_asset_id := TO_NUMBER(l_transaction_id);
942
943 -- Trn_Date passed is Disposal_Date
944 l_asset_disposal_date := TO_DATE(l_transaction_date,'DD/MM/RRRR');
945
946 -- Get the Asset Details
947 OPEN asset_dtls_csr(l_asset_id);
948 FETCH asset_dtls_csr INTO l_okl_chr_id;
949 CLOSE asset_dtls_csr;
950
951 l_scrapped_yn := check_asset_scrapped(l_asset_id);
952
953 IF l_scrapped_yn = 'Y' THEN
954
955 -- Scrap Message Subject
956 set_message(
957 p_app_name => G_APP_NAME,
958 p_msg_name => 'OKL_AM_SRV_SCRAP_MSG_SUB');
959
960 l_message_subject := FND_MESSAGE.get;
961
962 ELSE
963
964 -- Dispose Message Subject
965 set_message(
966 p_app_name => G_APP_NAME,
967 p_msg_name => 'OKL_AM_SRV_DISPOSE_MSG_SUB');
968
969 l_message_subject := FND_MESSAGE.get;
970 END IF;
971 ELSIF l_source = 'RETURN' THEN
972
973 -- Trn_Id is Asset_Id
974 l_asset_id := TO_NUMBER(l_transaction_id);
975
976 -- Trn_Date passed is Return_Date
977 l_asset_return_date := TO_DATE(l_transaction_date,'DD/MM/RRRR');
978
979 -- Get the Return Details
980 OPEN asset_dtls_csr(l_asset_id);
981 FETCH asset_dtls_csr INTO l_okl_chr_id;
982 CLOSE asset_dtls_csr;
983
984 -- Return Message Subject
985 set_message(
986 p_app_name => G_APP_NAME,
987 p_msg_name => 'OKL_AM_SRV_RETURN_MSG_SUB');
988
989 l_message_subject := FND_MESSAGE.get;
990
991 END IF;
992
993 -- get okl contract dtls
994 OPEN okl_k_dtls_csr(l_okl_chr_id);
995 FETCH okl_k_dtls_csr INTO l_okl_contract_number;
996 CLOSE okl_k_dtls_csr;
997
998 -- RMUNJULU 23-DEC-03 SERVICE K UPDATES Added condition
999 -- If No OKS Contract ID was set to the WF then get it now
1000 IF l_oks_chr_id IS NULL
1001 OR l_oks_chr_id = OKL_API.G_MISS_NUM THEN
1002
1003 -- Get the linked lease details
1004 OKL_SERVICE_INTEGRATION_PVT.check_service_link (
1005 p_api_version => l_api_version,
1006 p_init_msg_list => G_FALSE,
1007 x_return_status => l_return_status,
1008 x_msg_count => l_msg_count,
1009 x_msg_data => l_msg_data,
1010 p_lease_contract_id => l_okl_chr_id ,
1011 x_service_contract_id => l_oks_chr_id);
1012
1013 END IF;
1014
1015 -- get oks contract dtls
1016 OPEN oks_k_dtls_csr(l_oks_chr_id);
1017 FETCH oks_k_dtls_csr INTO l_oks_contract_number;
1018 CLOSE oks_k_dtls_csr;
1019
1020 -- *********
1021 -- Set Attributes needed
1022 -- *********
1023
1024 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1025 itemkey => itemkey,
1026 aname => 'OKL_CONTRACT_ID',
1027 avalue => l_okl_chr_id);
1028
1029 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1030 itemkey => itemkey,
1031 aname => 'OKL_CONTRACT_NUMBER',
1032 avalue => l_okl_contract_number);
1033
1034 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1035 itemkey => itemkey,
1036 aname => 'OKS_CONTRACT_ID',
1037 avalue => l_oks_chr_id);
1038
1039 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1040 itemkey => itemkey,
1041 aname => 'OKS_CONTRACT_NUMBER',
1042 avalue => l_oks_contract_number);
1043
1044 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
1045 itemkey => itemkey,
1046 aname => 'TERMINATION_DATE',
1047 avalue => TO_DATE(l_termination_date,'DD/MM/RRRR'));
1048
1049 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
1050 itemkey => itemkey,
1051 aname => 'NOTIFICATION_DATE',
1052 avalue => TO_DATE(SYSDATE,'DD/MM/RRRR'));
1053
1054 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1055 itemkey => itemkey,
1056 aname => 'ASSET_ID',
1057 avalue => l_asset_id);
1058
1059 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
1060 itemkey => itemkey,
1061 aname => 'RETURN_DATE',
1062 avalue => TO_DATE(l_asset_return_date,'DD/MM/RRRR'));
1063
1064 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
1065 itemkey => itemkey,
1066 aname => 'DISPOSAL_DATE',
1067 avalue => TO_DATE(l_asset_disposal_date,'DD/MM/RRRR'));
1068
1069 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1070 itemkey => itemkey,
1071 aname => 'MESSAGE_SUBJECT',
1072 avalue => l_message_subject);
1073
1074 resultout := 'COMPLETE:Y';
1075 RETURN ;
1076 END IF;
1077
1078 --
1079 -- CANCEL mode
1080 --
1081 IF (funcmode = 'CANCEL') THEN
1082
1083 resultout := 'COMPLETE:';
1084 RETURN;
1085
1086 END IF;
1087
1088 --
1089 -- TIMEOUT mode
1090 --
1091 IF (funcmode = 'TIMEOUT') THEN
1092
1093 resultout := 'COMPLETE:';
1094 RETURN;
1095
1096 END IF;
1097
1098 EXCEPTION
1099
1100 WHEN OTHERS THEN
1101 WF_CORE.context('OKL_AM_SERVICE_K_INT_WF' , 'populate_attributes',
1102 itemtype, itemkey, actid, funcmode);
1103 RAISE;
1104
1105 END populate_attributes;
1106
1107 -- Start of comments
1108 --
1109 -- Procedure Name : pop_dispose_noti_dtls
1110 -- Desciption : Populates the Linked Lease Asset Dispose Notification details
1111 -- Business Rules :
1112 -- Parameters :
1113 -- Version : 1.0
1114 -- History : RMUNJULU created
1115 --
1116 -- End of comments
1117 PROCEDURE pop_dispose_noti_dtls(
1118 document_id IN VARCHAR2,
1119 display_type IN VARCHAR2,
1120 document IN OUT NOCOPY VARCHAR2,
1121 document_type IN OUT NOCOPY VARCHAR2) IS
1122
1123 l_item_type WF_ITEMS.item_type%TYPE;
1124 l_item_key WF_ITEMS.item_key%TYPE;
1125 l_colon NUMBER;
1126 l_msgbody VARCHAR2(32000);
1127 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1128 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1129 l_asset_schedule_tbl kle_tbl_type;
1130 l_noti_rec noti_rec_type;
1131 i NUMBER := 1;
1132
1133 l_dispose_date DATE;
1134 l_dispose_asset_id NUMBER;
1135
1136 l_scrapped_yn VARCHAR2(1) := 'N';
1137 l_msg_body FND_NEW_MESSAGES.message_text%TYPE;
1138
1139 l_dispose_date_text VARCHAR2(60); -- added for bug 7538658
1140
1141 l_user_id NUMBER; -- added for bug 7538658
1142
1143 -- added for bug 7538658
1144 CURSOR get_user_id_csr IS
1145 SELECT user_id
1146 FROM FND_USER
1147 WHERE User_Name = FND_GLOBAL.user_name;
1148
1149 disptype VARCHAR2(30); -- Bug 8974552
1150
1151 BEGIN
1152
1153 -- ********
1154 -- Get the Item_Type and Item_Key
1155 -- ********
1156
1157 l_colon := INSTR(document_id, ':');
1158 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1159 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1160
1161 -- ********
1162 -- Get the Attribute values
1163 -- ********
1164
1165 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1166 itemtype => l_item_type,
1167 itemkey => l_item_key,
1168 aname => 'OKL_CONTRACT_NUMBER');
1169
1170 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1171 itemtype => l_item_type,
1172 itemkey => l_item_key,
1173 aname => 'OKS_CONTRACT_NUMBER');
1174
1175 l_dispose_date := WF_ENGINE.GetItemAttrDate(
1176 itemtype => l_item_type,
1177 itemkey => l_item_key,
1178 aname => 'DISPOSAL_DATE');
1179
1180 l_dispose_asset_id := WF_ENGINE.GetItemAttrText(
1181 itemtype => l_item_type,
1182 itemkey => l_item_key,
1183 aname => 'ASSET_ID');
1184
1185 -- ********
1186 -- Get Assets Details
1187 -- ********
1188
1189 get_assets_schedule (
1190 p_kle_id => l_dispose_asset_id,
1191 x_asset_schedule_tbl => l_asset_schedule_tbl);
1192
1193
1194
1195 -- ********
1196 -- Get Notification Body texts
1197 -- ********
1198
1199 get_set_noti_dtls (
1200 p_itemtype => l_item_type,
1201 p_itemkey => l_item_key,
1202 x_noti_rec => l_noti_rec);
1203
1204 -- ********
1205 -- Check if SCRAPPED
1206 -- ********
1207
1208 l_scrapped_yn := check_asset_scrapped(l_dispose_asset_id);
1209
1210 IF l_scrapped_yn = 'Y' THEN
1211
1212 l_msg_body := l_noti_rec.scrap_main_body;
1213
1214 ELSE
1215
1216 l_msg_body := l_noti_rec.dispose_main_body;
1217
1218 END IF;
1219
1220
1221 -- added for Bug 7538658 start
1222 IF (FND_RELEASE.MAJOR_VERSION = 12 AND FND_RELEASE.minor_version >= 1 AND FND_RELEASE.POINT_VERSION >= 1 )
1223 OR (FND_RELEASE.MAJOR_VERSION > 12) THEN
1224
1225 OPEN get_user_id_csr;
1226 FETCH get_user_id_csr INTO l_user_id;
1227 CLOSE get_user_id_csr;
1228
1229 IF l_user_id IS NULL THEN
1230 l_user_id := to_number(null);
1231 END IF;
1232
1233 if (disptype=wf_notification.doc_html) then -- bug 8974552
1234 -- For html notification in Hijrah calendar, the MMM date format would be displayed correctly only when <BDO> tag is used.
1235 -- Use NVL for NLS_CALENDAR
1236 l_dispose_date_text := '<BDO DIR="LTR">' ||
1237 to_char(l_dispose_date,
1238 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1239 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''')
1240 || '</BDO>';
1241
1242 else
1243 -- Use NVL for NLS_CALENDAR
1244 l_dispose_date_text := to_char(l_dispose_date,
1245 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1246 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''');
1247
1248 end if;
1249
1250 ELSE
1251
1252 l_dispose_date_text := to_char(l_dispose_date);
1253
1254 END IF;
1255 -- added for Bug 7538658 End
1256
1257 -- ********
1258 -- Set the message body
1259 -- ********
1260
1261 l_msgbody :=
1262 '<html>
1263 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1264 .tableDataCell { font-family: Arial; font-size: 9pt; }
1265 </style>
1266 <body>
1267 <p></p>
1268 <p class="tableDataCell"> ' || l_msg_body || ' </p>
1269 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1270 <table border="1">
1271 <tr>
1272 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1273 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1274 <td class="tableHeaderCell"> ' || l_noti_rec.asset_disp_date || ' </td>
1275 </tr>
1276 <tr>
1277 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1278 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1279 <td class="tableDataCell"> ' || l_dispose_date_text || ' </td>
1280 </tr>
1281 </table>
1282 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1283 <table border="1">
1284 <tr>
1285 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1286 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1287 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1288 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1289 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1290 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1291 </tr>';
1292 IF l_asset_schedule_tbl.COUNT > 0 THEN
1293 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1294
1295 l_msgbody := l_msgbody ||
1296 '<tr>
1297 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1298 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1299 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1300 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1301 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1302 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1303 </tr>';
1304 END LOOP;
1305 END IF;
1306
1307 l_msgbody := l_msgbody ||
1308 '</table>
1309 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1310 </body>
1311 </html>';
1312
1313 -- ********
1314 -- Set OUT variables
1315 -- ********
1316
1317 document := l_msgbody;
1318 document_type := display_type;
1319
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 NULL;
1323
1324 END pop_dispose_noti_dtls;
1325
1326 -- Start of comments
1327 --
1328 -- Procedure Name : pop_return_noti_dtls
1329 -- Desciption : Populates the Linked Lease Asset Return Notification details
1330 -- When Source is 'RETURN'
1331 -- Business Rules :
1332 -- Parameters :
1333 -- Version : 1.0
1334 -- History : RMUNJULU created
1335 --
1336 -- End of comments
1337 PROCEDURE pop_return_noti_dtls(
1338 document_id IN VARCHAR2,
1339 display_type IN VARCHAR2,
1340 document IN OUT NOCOPY VARCHAR2,
1341 document_type IN OUT NOCOPY VARCHAR2) IS
1342
1343 l_item_type WF_ITEMS.item_type%TYPE;
1344 l_item_key WF_ITEMS.item_key%TYPE;
1345 l_colon NUMBER;
1346 l_msgbody VARCHAR2(32000);
1347 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1348 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1349 l_asset_schedule_tbl kle_tbl_type;
1350 l_noti_rec noti_rec_type;
1351 i NUMBER := 1;
1352
1353 l_return_date DATE;
1354 l_return_asset_id NUMBER;
1355
1356 l_return_date_text VARCHAR2(60); -- added for bug 7538658
1357
1358 l_user_id NUMBER; -- added for bug 7538658
1359
1360 -- added for bug 7538658
1361 CURSOR get_user_id_csr IS
1362 SELECT user_id
1363 FROM FND_USER
1364 WHERE User_Name = FND_GLOBAL.user_name;
1365
1366 disptype VARCHAR2(30); -- Bug 8974552
1367
1368 BEGIN
1369
1370 -- ********
1371 -- Get the Item_Type and Item_Key
1372 -- ********
1373
1374 l_colon := INSTR(document_id, ':');
1375 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1376 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1377
1378 -- ********
1379 -- Get the Attribute values
1380 -- ********
1381
1382 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1383 itemtype => l_item_type,
1384 itemkey => l_item_key,
1385 aname => 'OKL_CONTRACT_NUMBER');
1386
1387 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1388 itemtype => l_item_type,
1389 itemkey => l_item_key,
1390 aname => 'OKS_CONTRACT_NUMBER');
1391
1392 l_return_date := WF_ENGINE.GetItemAttrDate(
1393 itemtype => l_item_type,
1394 itemkey => l_item_key,
1395 aname => 'RETURN_DATE');
1396
1397 l_return_asset_id := WF_ENGINE.GetItemAttrText(
1398 itemtype => l_item_type,
1399 itemkey => l_item_key,
1400 aname => 'ASSET_ID');
1401
1402 -- ********
1403 -- Get Assets Details
1404 -- ********
1405
1406 get_assets_schedule (
1407 p_kle_id => l_return_asset_id,
1408 x_asset_schedule_tbl => l_asset_schedule_tbl);
1409
1410 -- ********
1411 -- Get Notification Body texts
1412 -- ********
1413
1414 get_set_noti_dtls (
1415 p_itemtype => l_item_type,
1416 p_itemkey => l_item_key,
1417 x_noti_rec => l_noti_rec);
1418
1419
1420 -- added for Bug 7538658 start
1421 IF (FND_RELEASE.MAJOR_VERSION = 12 AND FND_RELEASE.minor_version >= 1 AND FND_RELEASE.POINT_VERSION >= 1 )
1422 OR (FND_RELEASE.MAJOR_VERSION > 12) THEN
1423
1424 OPEN get_user_id_csr ;
1425 FETCH get_user_id_csr INTO l_user_id;
1426 CLOSE get_user_id_csr ;
1427
1428 IF l_user_id IS NULL THEN
1429 l_user_id := to_number(null);
1430 END IF;
1431
1432
1433 if (disptype=wf_notification.doc_html) then -- bug 8974552
1434 -- For html notification in Hijrah calendar, the MMM date format would be displayed correctly only when <BDO> tag is used.
1435 -- Use NVL for NLS_CALENDAR
1436 l_return_date_text := '<BDO DIR="LTR">' ||
1437 to_char(l_return_date,
1438 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1439 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''')
1440 || '</BDO>';
1441
1442 else
1443 -- Use NVL for NLS_CALENDAR
1444 l_return_date_text := to_char(l_return_date,
1445 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1446 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''');
1447
1448 end if;
1449
1450 ELSE
1451
1452 l_return_date_text := to_char(l_return_date);
1453
1454 END IF;
1455 -- added for Bug 7538658 End
1456
1457 -- ********
1458 -- Set the message body
1459 -- ********
1460
1461 l_msgbody :=
1462 '<html>
1463 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1464 .tableDataCell { font-family: Arial; font-size: 9pt; }
1465 </style>
1466 <body>
1467 <p></p>
1468 <p class="tableDataCell"> ' || l_noti_rec.return_main_body || ' </p>
1469 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1470 <table border="1">
1471 <tr>
1472 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1473 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1474 <td class="tableHeaderCell"> ' || l_noti_rec.asset_ret_date || ' </td>
1475 </tr>
1476 <tr>
1477 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1478 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1479 <td class="tableDataCell"> ' || l_return_date_text || ' </td>
1480 </tr>
1481 </table>
1482 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1483 <table border="1">
1484 <tr>
1485 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1486 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1487 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1488 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1489 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1490 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1491 </tr>';
1492 IF l_asset_schedule_tbl.COUNT > 0 THEN
1493 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1494
1495 l_msgbody := l_msgbody ||
1496 '<tr>
1497 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1498 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1499 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1500 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1501 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1502 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1503 </tr>';
1504 END LOOP;
1505 END IF;
1506
1507 l_msgbody := l_msgbody ||
1508 '</table>
1509 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1510 </body>
1511 </html>';
1512
1513 -- ********
1514 -- Set OUT variables
1515 -- ********
1516
1517 document := l_msgbody;
1518 document_type := display_type;
1519
1520 EXCEPTION
1521 WHEN OTHERS THEN
1522 NULL;
1523
1524 END pop_return_noti_dtls;
1525
1526 -- Start of comments
1527 --
1528 -- Procedure Name : pop_delink_err_noti_dtls
1529 -- Desciption : Populates the Linked Lease De-link Error and Termination Notification details
1530 -- When Source is 'ERROR'
1531 -- Business Rules :
1532 -- Parameters :
1533 -- Version : 1.0
1534 -- History : RMUNJULU created
1535 --
1536 -- End of comments
1537 PROCEDURE pop_delink_err_noti_dtls(
1538 document_id IN VARCHAR2,
1539 display_type IN VARCHAR2,
1540 document IN OUT NOCOPY VARCHAR2,
1541 document_type IN OUT NOCOPY VARCHAR2) IS
1542
1543 l_item_type WF_ITEMS.item_type%TYPE;
1544 l_item_key WF_ITEMS.item_key%TYPE;
1545 l_colon NUMBER;
1546 l_msgbody VARCHAR2(32000);
1547 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1548 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1549 l_quote_id VARCHAR2(200);
1550 l_asset_schedule_tbl kle_tbl_type;
1551 l_noti_rec noti_rec_type;
1552 i NUMBER := 1;
1553
1554 l_termination_date DATE;
1555 l_okl_chr_id NUMBER;
1556
1557
1558 l_termination_date_text VARCHAR2(60); -- added for bug 7538658
1559 l_user_id NUMBER; -- added for bug 7538658
1560
1561 -- added for bug 7538658
1562 CURSOR get_user_id_csr IS
1563 SELECT user_id
1564 FROM FND_USER
1565 WHERE User_Name = FND_GLOBAL.user_name;
1566
1567 disptype VARCHAR2(30); -- Bug 8974552
1568
1569 BEGIN
1570
1571 -- LIMITATIONS:
1572 -- For now Partial quotes are not allowed if linked service contract exists.
1573 -- So its always full termination. So get all linked assets
1574 -- for the lease contract, no need to check if all exists in quote,
1575 -- since all will exist.
1576
1577
1578 -- ********
1579 -- Get the Item_Type and Item_Key
1580 -- ********
1581
1582 l_colon := INSTR(document_id, ':');
1583 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1584 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1585
1586 -- ********
1587 -- Get the Attribute values
1588 -- ********
1589
1590 l_okl_chr_id := WF_ENGINE.GetItemAttrText(
1591 itemtype => l_item_type,
1592 itemkey => l_item_key,
1593 aname => 'OKL_CONTRACT_ID');
1594
1595 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1596 itemtype => l_item_type,
1597 itemkey => l_item_key,
1598 aname => 'OKL_CONTRACT_NUMBER');
1599
1600 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1601 itemtype => l_item_type,
1602 itemkey => l_item_key,
1603 aname => 'OKS_CONTRACT_NUMBER');
1604
1605 l_termination_date := WF_ENGINE.GetItemAttrDate(
1606 itemtype => l_item_type,
1607 itemkey => l_item_key,
1608 aname => 'TERMINATION_DATE');
1609
1610 l_quote_id := WF_ENGINE.GetItemAttrText(
1611 itemtype => l_item_type,
1612 itemkey => l_item_key,
1613 aname => 'QUOTE_ID');
1614
1615 -- ********
1616 -- Get Assets Details
1617 -- ********
1618
1619 get_assets_schedule (
1620 p_khr_id => l_okl_chr_id,
1621 p_quote_id => TO_NUMBER(l_quote_id),
1622 x_asset_schedule_tbl => l_asset_schedule_tbl);
1623
1624 -- ********
1625 -- Get Notification Body texts
1626 -- ********
1627
1628 get_set_noti_dtls (
1629 p_itemtype => l_item_type,
1630 p_itemkey => l_item_key,
1631 x_noti_rec => l_noti_rec);
1632
1633 -- added for Bug 7538658 start
1634 IF (FND_RELEASE.MAJOR_VERSION = 12 AND FND_RELEASE.minor_version >= 1 AND FND_RELEASE.POINT_VERSION >= 1 )
1635 OR (FND_RELEASE.MAJOR_VERSION > 12) THEN
1636
1637 OPEN get_user_id_csr;
1638 FETCH get_user_id_csr INTO l_user_id;
1639 CLOSE get_user_id_csr;
1640
1641 IF l_user_id IS NULL THEN
1642 l_user_id := to_number(null);
1643 END IF;
1644
1645 if (disptype=wf_notification.doc_html) then -- bug 8974552
1646 -- For html notification in Hijrah calendar, the MMM date format would be displayed correctly only when <BDO> tag is used.
1647 -- Use NVL for NLS_CALENDAR
1648 l_termination_date_text := '<BDO DIR="LTR">' ||
1649 to_char(l_termination_date,
1650 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1651 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''')
1652 || '</BDO>';
1653
1654 else
1655 -- Use NVL for NLS_CALENDAR
1656 l_termination_date_text := to_char(l_termination_date,
1657 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1658 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''');
1659
1660 end if;
1661
1662 ELSE
1663
1664 l_termination_date_text := to_char(l_termination_date);
1665
1666 END IF;
1667 -- added for Bug 7538658 End
1668
1669 -- ********
1670 -- Set the message body
1671 -- ********
1672
1673 l_msgbody :=
1674 '<html>
1675 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1676 .tableDataCell { font-family: Arial; font-size: 9pt; }
1677 </style>
1678 <body>
1679 <p></p>
1680 <p class="tableDataCell"> ' || l_noti_rec.error_main_body || ' </p>
1681 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1682 <table border="1">
1683 <tr>
1684 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1685 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1686 <td class="tableHeaderCell"> ' || l_noti_rec.termination_date || ' </td>
1687 </tr>
1688 <tr>
1689 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1690 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1691 <td class="tableDataCell"> ' || l_termination_date_text || ' </td>
1692 </tr>
1693 </table>
1694 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1695 <table border="1">
1696 <tr>
1697 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1698 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1699 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1700 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1701 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1702 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1703 </tr>';
1704 IF l_asset_schedule_tbl.COUNT > 0 THEN
1705 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1706
1707 l_msgbody := l_msgbody ||
1708 '<tr>
1709 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1710 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1711 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1712 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1713 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1714 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1715 </tr>';
1716 END LOOP;
1717 END IF;
1718
1719 l_msgbody := l_msgbody ||
1720 '</table>
1721 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1722 </body>
1723 </html>';
1724
1725 -- ********
1726 -- Set OUT variables
1727 -- ********
1728
1729 document := l_msgbody;
1730 document_type := display_type;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 NULL;
1735
1736 END pop_delink_err_noti_dtls;
1737
1738 -- Start of comments
1739 --
1740 -- Procedure Name : pop_delink_noti_dtls
1741 -- Desciption : Populates the Linked Lease De-link and Termination Notification details
1742 -- When Source is 'SUCCESS'
1743 -- Business Rules :
1744 -- Parameters :
1745 -- Version : 1.0
1746 -- History : RMUNJULU created
1747 -- : RMUNJULU 23-DEC-03 SERVICE K UPDATES
1748 --
1749 -- End of comments
1750 PROCEDURE pop_delink_noti_dtls(
1751 document_id IN VARCHAR2,
1752 display_type IN VARCHAR2,
1753 document IN OUT NOCOPY VARCHAR2,
1754 document_type IN OUT NOCOPY VARCHAR2) IS
1755
1756 l_item_type WF_ITEMS.item_type%TYPE;
1757 l_item_key WF_ITEMS.item_key%TYPE;
1758 l_colon NUMBER;
1759 l_msgbody VARCHAR2(32000);
1760 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1761 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1762 l_quote_id VARCHAR2(200);
1763 l_asset_schedule_tbl kle_tbl_type;
1764 l_noti_rec noti_rec_type;
1765 i NUMBER := 1;
1766
1767 l_termination_date DATE;
1768 l_okl_chr_id NUMBER;
1769
1770 l_termination_date_text VARCHAR2(60); -- added for bug 7538658
1771
1772 l_user_id NUMBER; -- added for bug 7538658
1773
1774 -- added for bug 7538658
1775 CURSOR get_user_id_csr IS
1776 SELECT user_id
1777 FROM FND_USER
1778 WHERE User_Name = FND_GLOBAL.user_name;
1779
1780 disptype VARCHAR2(30); -- Bug 8974552
1781
1782
1783 BEGIN
1784
1785 -- LIMITATIONS:
1786 -- For now Partial quotes are not allowed if linked service contract exists.
1787 -- So its always full termination. So get all linked assets
1788 -- for the lease contract, no need to check if all exists in quote,
1789 -- since all will exist.
1790
1791 -- ********
1792 -- Get the Item_Type and Item_Key
1793 -- ********
1794
1795 l_colon := INSTR(document_id, ':');
1796 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1797 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1798
1799 -- ********
1800 -- Get the Attribute values
1801 -- ********
1802
1803 l_okl_chr_id := WF_ENGINE.GetItemAttrText(
1804 itemtype => l_item_type,
1805 itemkey => l_item_key,
1806 aname => 'OKL_CONTRACT_ID');
1807
1808 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1809 itemtype => l_item_type,
1810 itemkey => l_item_key,
1811 aname => 'OKL_CONTRACT_NUMBER');
1812
1813 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1814 itemtype => l_item_type,
1815 itemkey => l_item_key,
1816 aname => 'OKS_CONTRACT_NUMBER');
1817
1818 l_termination_date := WF_ENGINE.GetItemAttrDate(
1819 itemtype => l_item_type,
1820 itemkey => l_item_key,
1821 aname => 'TERMINATION_DATE');
1822
1823 l_quote_id := WF_ENGINE.GetItemAttrText(
1824 itemtype => l_item_type,
1825 itemkey => l_item_key,
1826 aname => 'QUOTE_ID');
1827
1828 -- ********
1829 -- Get Assets Details
1830 -- ********
1831
1832 get_assets_schedule (
1833 p_khr_id => l_okl_chr_id,
1834 p_quote_id => TO_NUMBER(l_quote_id),
1835 x_asset_schedule_tbl => l_asset_schedule_tbl);
1836
1837 -- ********
1838 -- Get Notification Body texts
1839 -- ********
1840
1841 get_set_noti_dtls (
1842 p_itemtype => l_item_type,
1843 p_itemkey => l_item_key,
1844 x_noti_rec => l_noti_rec);
1845
1846
1847 -- added for Bug 7538658 start
1848 IF (FND_RELEASE.MAJOR_VERSION = 12 AND FND_RELEASE.minor_version >= 1 AND FND_RELEASE.POINT_VERSION >= 1 )
1849 OR (FND_RELEASE.MAJOR_VERSION > 12) THEN
1850
1851 OPEN get_user_id_csr;
1852 FETCH get_user_id_csr INTO l_user_id;
1853 CLOSE get_user_id_csr;
1854
1855 IF l_user_id IS NULL THEN
1856 l_user_id := to_number(null);
1857 END IF;
1858
1859 if (disptype=wf_notification.doc_html) then -- bug 8974552
1860 -- For html notification in Hijrah calendar, the MMM date format would be displayed correctly only when <BDO> tag is used.
1861 -- Use NVL for NLS_CALENDAR
1862 l_termination_date_text := '<BDO DIR="LTR">' ||
1863 to_char(l_termination_date,
1864 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1865 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''')
1866 || '</BDO>';
1867
1868 else
1869 -- Use NVL for NLS_CALENDAR
1870 l_termination_date_text := to_char(l_termination_date,
1871 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
1872 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''');
1873
1874 end if;
1875
1876 ELSE
1877
1878 l_termination_date_text := to_char(l_termination_date);
1879
1880 END IF;
1881 -- added for Bug 7538658 End
1882
1883 -- ********
1884 -- Set the message body
1885 -- ********
1886 -- RMUNJULU 23-DEC-03 SERVICE K UPDATES
1887 -- Changed the message body removed the asset details
1888 l_msgbody :=
1889 '<html>
1890 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1891 .tableDataCell { font-family: Arial; font-size: 9pt; }
1892 </style>
1893 <body>
1894 <p></p>
1895 <p class="tableDataCell"> ' || l_noti_rec.success_main_body || ' </p>
1896 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1897 <table border="1">
1898 <tr>
1899 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1900 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1901 <td class="tableHeaderCell"> ' || l_noti_rec.termination_date || ' </td>
1902 </tr>
1903
1904 <tr>
1905 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1906 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1907 <td class="tableDataCell"> ' || l_termination_date_text || ' </td>
1908 </tr>
1909 </table>
1910 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1911 </body>
1912 </html>';
1913
1914 -- ********
1915 -- Set OUT variables
1916 -- ********
1917
1918 document := l_msgbody;
1919 document_type := display_type;
1920
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923 NULL;
1924
1925 END pop_delink_noti_dtls;
1926
1927 -- Start of comments
1928 --
1929 -- Procedure Name : pop_term_noti_dtls
1930 -- Desciption : Populates the Linked Lease Termination Notification details
1931 -- When Source is 'TERMINATION'
1932 -- Business Rules :
1933 -- Parameters :
1934 -- Version : 1.0
1935 -- History : RMUNJULU created
1936 --
1937 -- End of comments
1938 PROCEDURE pop_term_noti_dtls(
1939 document_id IN VARCHAR2,
1940 display_type IN VARCHAR2,
1941 document IN OUT NOCOPY VARCHAR2,
1942 document_type IN OUT NOCOPY VARCHAR2) IS
1943
1944 l_item_type WF_ITEMS.item_type%TYPE;
1945 l_item_key WF_ITEMS.item_key%TYPE;
1946 l_colon NUMBER;
1947 l_msgbody VARCHAR2(32000);
1948 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1949 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1950 l_quote_id VARCHAR2(200);
1951 l_asset_schedule_tbl kle_tbl_type;
1952 l_noti_rec noti_rec_type;
1953 i NUMBER := 1;
1954
1955 l_termination_date DATE;
1956 l_okl_chr_id NUMBER;
1957
1958 l_termination_date_text VARCHAR2(60); -- added for bug 7538658
1959 l_user_id NUMBER; -- added for bug 7538658
1960
1961 -- added for bug 7538658
1962 CURSOR get_user_id_csr IS
1963 SELECT user_id
1964 FROM FND_USER
1965 WHERE User_Name = FND_GLOBAL.user_name;
1966
1967 disptype VARCHAR2(30); -- Bug 8974552
1968
1969 BEGIN
1970
1971 -- LIMITATIONS:
1972 -- For now Partial quotes are not allowed if linked service contract exists.
1973 -- So its always full termination. So get all linked assets
1974 -- for the lease contract, no need to check if all exists in quote,
1975 -- since all will exist.
1976
1977 -- ********
1978 -- Get the Item_Type and Item_Key
1979 -- ********
1980
1981 l_colon := INSTR(document_id, ':');
1982 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1983 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1984
1985 -- ********
1986 -- Get the Attribute values
1987 -- ********
1988
1989 l_okl_chr_id := WF_ENGINE.GetItemAttrText(
1990 itemtype => l_item_type,
1991 itemkey => l_item_key,
1992 aname => 'OKL_CONTRACT_ID');
1993
1994 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1995 itemtype => l_item_type,
1996 itemkey => l_item_key,
1997 aname => 'OKL_CONTRACT_NUMBER');
1998
1999 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
2000 itemtype => l_item_type,
2001 itemkey => l_item_key,
2002 aname => 'OKS_CONTRACT_NUMBER');
2003
2004 l_termination_date := WF_ENGINE.GetItemAttrDate(
2005 itemtype => l_item_type,
2006 itemkey => l_item_key,
2007 aname => 'TERMINATION_DATE');
2008
2009 l_quote_id := WF_ENGINE.GetItemAttrText(
2010 itemtype => l_item_type,
2011 itemkey => l_item_key,
2012 aname => 'QUOTE_ID');
2013
2014 -- ********
2015 -- Get Assets Details
2016 -- ********
2017
2018 get_assets_schedule (
2019 p_khr_id => l_okl_chr_id,
2020 p_quote_id => TO_NUMBER(l_quote_id),
2021 x_asset_schedule_tbl => l_asset_schedule_tbl);
2022
2023 -- ********
2024 -- Get Notification Body texts
2025 -- ********
2026
2027 get_set_noti_dtls (
2028 p_itemtype => l_item_type,
2029 p_itemkey => l_item_key,
2030 x_noti_rec => l_noti_rec);
2031
2032
2033 -- added for Bug 7538658 start
2034 IF (FND_RELEASE.MAJOR_VERSION = 12 AND FND_RELEASE.minor_version >= 1 AND FND_RELEASE.POINT_VERSION >= 1 )
2035 OR (FND_RELEASE.MAJOR_VERSION > 12) THEN
2036
2037 OPEN get_user_id_csr;
2038 FETCH get_user_id_csr INTO l_user_id;
2039 CLOSE get_user_id_csr;
2040
2041 IF l_user_id IS NULL THEN
2042 l_user_id := to_number(null);
2043 END IF;
2044
2045 if (disptype=wf_notification.doc_html) then -- bug 8974552
2046 -- For html notification in Hijrah calendar, the MMM date format would be displayed correctly only when <BDO> tag is used.
2047 -- Use NVL for NLS_CALENDAR
2048 l_termination_date_text := '<BDO DIR="LTR">' ||
2049 to_char(l_termination_date,
2050 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
2051 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''')
2052 || '</BDO>';
2053
2054 else
2055 -- Use NVL for NLS_CALENDAR
2056 l_termination_date_text := to_char(l_termination_date,
2057 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),
2058 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || '''');
2059
2060 end if;
2061
2062 ELSE
2063
2064 l_termination_date_text := to_char(l_termination_date);
2065
2066 END IF;
2067 -- added for Bug 7538658 End
2068
2069 -- ********
2070 -- Set the message body
2071 -- ********
2072
2073 l_msgbody :=
2074 '<html>
2075 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
2076 .tableDataCell { font-family: Arial; font-size: 9pt; }
2077 </style>
2078 <body>
2079 <p></p>
2080 <p class="tableDataCell"> ' || l_noti_rec.termination_main_body || ' </p>
2081 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
2082 <table border="1">
2083 <tr>
2084 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
2085 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
2086 <td class="tableHeaderCell"> ' || l_noti_rec.termination_date || ' </td>
2087 </tr>
2088 <tr>
2089 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
2090 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
2091 <td class="tableDataCell"> ' || l_termination_date_text || ' </td>
2092 </tr>
2093 </table>
2094 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
2095 <table border="1">
2096 <tr>
2097 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
2098 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
2099 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
2100 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
2101 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
2102 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
2103 </tr>';
2104 IF l_asset_schedule_tbl.COUNT > 0 THEN
2105 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
2106
2107 l_msgbody := l_msgbody ||
2108 '<tr>
2109 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
2110 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
2111 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
2112 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
2113 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
2114 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
2115 </tr>';
2116 END LOOP;
2117 END IF;
2118
2119 l_msgbody := l_msgbody ||
2120 '</table>
2121 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
2122 </body>
2123 </html>';
2124
2125 -- ********
2126 -- Set OUT variables
2127 -- ********
2128
2129 document := l_msgbody;
2130 document_type := display_type;
2131
2132 EXCEPTION
2133 WHEN OTHERS THEN
2134 NULL;
2135
2136 END pop_term_noti_dtls;
2137
2138 -- Start of comments
2139 --
2140 -- Procedure Name : check_source
2141 -- Desciption : Checks the source of the WF call, this will decide which
2142 -- path to take, which will send a noti
2143 -- Business Rules :
2144 -- Parameters :
2145 -- Version : 1.0
2146 -- History : RMUNJULU created
2147 --
2148 -- End of comments
2149 PROCEDURE check_source(
2150 itemtype IN VARCHAR2,
2151 itemkey IN VARCHAR2,
2152 actid IN NUMBER,
2153 funcmode IN VARCHAR2,
2154 resultout OUT NOCOPY VARCHAR2) IS
2155
2156
2157 l_source VARCHAR2(100);
2158
2159
2160 BEGIN
2161
2162 --
2163 -- RUN mode
2164 --
2165 IF (funcmode = 'RUN') THEN
2166
2167 -- ********
2168 -- Get the values
2169 -- ********
2170
2171 l_source := WF_ENGINE.GetItemAttrText(
2172 itemtype => itemtype,
2173 itemkey => itemkey,
2174 aname => 'SOURCE');
2175
2176 -- ********
2177 -- Set the Notification Body and Output of this function based on source
2178 -- ********
2179
2180 IF l_source = 'SUCCESS' THEN -- From termination, de-link was successful
2181
2182 WF_ENGINE.SetItemAttrText(
2183 itemtype => itemtype,
2184 itemkey => itemkey,
2185 aname => 'MESSAGE_DOC',
2186 avalue =>
2187 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_delink_noti_dtls /'||itemtype||':'||itemkey);
2188
2189 resultout := 'COMPLETE:SOURCE_SUCCESS';
2190
2191 ELSIF l_source = 'ERROR' THEN -- From termination, de-link failed
2192
2193 WF_ENGINE.SetItemAttrText(
2194 itemtype => itemtype,
2195 itemkey => itemkey,
2196 aname => 'MESSAGE_DOC',
2197 avalue =>
2198 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_delink_err_noti_dtls /'||itemtype||':'||itemkey);
2199
2200 resultout := 'COMPLETE:SOURCE_ERROR';
2201
2202 ELSIF l_source = 'TERMINATION' THEN -- From termination, de-link not needed
2203
2204 WF_ENGINE.SetItemAttrText(
2205 itemtype => itemtype,
2206 itemkey => itemkey,
2207 aname => 'MESSAGE_DOC',
2208 avalue =>
2209 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_term_noti_dtls /'||itemtype||':'||itemkey);
2210
2211 resultout := 'COMPLETE:SOURCE_TERMINATION';
2212
2213 ELSIF l_source = 'DISPOSE' THEN -- From Asset Dispose
2214
2215 WF_ENGINE.SetItemAttrText(
2216 itemtype => itemtype,
2217 itemkey => itemkey,
2218 aname => 'MESSAGE_DOC',
2219 avalue =>
2220 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_dispose_noti_dtls /'||itemtype||':'||itemkey);
2221
2222 resultout := 'COMPLETE:SOURCE_DISPOSE';
2223
2224 ELSIF l_source = 'RETURN' THEN -- From Asset Return
2225
2226 WF_ENGINE.SetItemAttrText(
2227 itemtype => itemtype,
2228 itemkey => itemkey,
2229 aname => 'MESSAGE_DOC',
2230 avalue =>
2231 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_return_noti_dtls /'||itemtype||':'||itemkey);
2232
2233 resultout := 'COMPLETE:SOURCE_RETURN';
2234
2235 END IF;
2236
2237 RETURN ;
2238 END IF;
2239
2240 --
2241 -- CANCEL mode
2242 --
2243 IF (funcmode = 'CANCEL') THEN
2244
2245 resultout := 'COMPLETE:';
2246 RETURN;
2247
2248 END IF;
2249
2250 --
2251 -- TIMEOUT mode
2252 --
2253 IF (funcmode = 'TIMEOUT') THEN
2254
2255 resultout := 'COMPLETE:';
2256 RETURN;
2257
2258 END IF;
2259
2260
2261 EXCEPTION
2262
2263 WHEN OTHERS THEN
2264 WF_CORE.context('OKL_AM_SERVICE_K_INT_WF' , 'check_source',
2265 itemtype, itemkey, actid, funcmode);
2266 RAISE;
2267
2268 END check_source;
2269
2270
2271 END OKL_AM_SERVICE_K_INT_WF;