[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 2006/07/21 13:11:19 akrangan noship $ */
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 BEGIN
1140
1141 -- ********
1142 -- Get the Item_Type and Item_Key
1143 -- ********
1144
1145 l_colon := INSTR(document_id, ':');
1146 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1147 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1148
1149 -- ********
1150 -- Get the Attribute values
1151 -- ********
1152
1153 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1154 itemtype => l_item_type,
1155 itemkey => l_item_key,
1156 aname => 'OKL_CONTRACT_NUMBER');
1157
1158 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1159 itemtype => l_item_type,
1160 itemkey => l_item_key,
1161 aname => 'OKS_CONTRACT_NUMBER');
1162
1163 l_dispose_date := WF_ENGINE.GetItemAttrDate(
1164 itemtype => l_item_type,
1165 itemkey => l_item_key,
1166 aname => 'DISPOSAL_DATE');
1167
1168 l_dispose_asset_id := WF_ENGINE.GetItemAttrText(
1169 itemtype => l_item_type,
1170 itemkey => l_item_key,
1171 aname => 'ASSET_ID');
1172
1173 -- ********
1174 -- Get Assets Details
1175 -- ********
1176
1177 get_assets_schedule (
1178 p_kle_id => l_dispose_asset_id,
1179 x_asset_schedule_tbl => l_asset_schedule_tbl);
1180
1181
1182
1183 -- ********
1184 -- Get Notification Body texts
1185 -- ********
1186
1187 get_set_noti_dtls (
1188 p_itemtype => l_item_type,
1189 p_itemkey => l_item_key,
1190 x_noti_rec => l_noti_rec);
1191
1192 -- ********
1193 -- Check if SCRAPPED
1194 -- ********
1195
1196 l_scrapped_yn := check_asset_scrapped(l_dispose_asset_id);
1197
1198 IF l_scrapped_yn = 'Y' THEN
1199
1200 l_msg_body := l_noti_rec.scrap_main_body;
1201
1202 ELSE
1203
1204 l_msg_body := l_noti_rec.dispose_main_body;
1205
1206 END IF;
1207
1208 -- ********
1209 -- Set the message body
1210 -- ********
1211
1212 l_msgbody :=
1213 '<html>
1214 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1215 .tableDataCell { font-family: Arial; font-size: 9pt; }
1216 </style>
1217 <body>
1218 <p></p>
1219 <p class="tableDataCell"> ' || l_msg_body || ' </p>
1220 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1221 <table border="1">
1222 <tr>
1223 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1224 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1225 <td class="tableHeaderCell"> ' || l_noti_rec.asset_disp_date || ' </td>
1226 </tr>
1227 <tr>
1228 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1229 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1230 <td class="tableDataCell"> ' || l_dispose_date || ' </td>
1231 </tr>
1232 </table>
1233 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1234 <table border="1">
1235 <tr>
1236 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1237 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1238 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1239 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1240 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1241 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1242 </tr>';
1243 IF l_asset_schedule_tbl.COUNT > 0 THEN
1244 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1245
1246 l_msgbody := l_msgbody ||
1247 '<tr>
1248 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1249 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1250 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1251 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1252 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1253 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1254 </tr>';
1255 END LOOP;
1256 END IF;
1257
1258 l_msgbody := l_msgbody ||
1259 '</table>
1260 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1261 </body>
1262 </html>';
1263
1264 -- ********
1265 -- Set OUT variables
1266 -- ********
1267
1268 document := l_msgbody;
1269 document_type := display_type;
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 NULL;
1274
1275 END pop_dispose_noti_dtls;
1276
1277 -- Start of comments
1278 --
1279 -- Procedure Name : pop_return_noti_dtls
1280 -- Desciption : Populates the Linked Lease Asset Return Notification details
1281 -- When Source is 'RETURN'
1282 -- Business Rules :
1283 -- Parameters :
1284 -- Version : 1.0
1285 -- History : RMUNJULU created
1286 --
1287 -- End of comments
1288 PROCEDURE pop_return_noti_dtls(
1289 document_id IN VARCHAR2,
1290 display_type IN VARCHAR2,
1291 document IN OUT NOCOPY VARCHAR2,
1292 document_type IN OUT NOCOPY VARCHAR2) IS
1293
1294 l_item_type WF_ITEMS.item_type%TYPE;
1295 l_item_key WF_ITEMS.item_key%TYPE;
1296 l_colon NUMBER;
1297 l_msgbody VARCHAR2(32000);
1298 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1299 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1300 l_asset_schedule_tbl kle_tbl_type;
1301 l_noti_rec noti_rec_type;
1302 i NUMBER := 1;
1303
1304 l_return_date DATE;
1305 l_return_asset_id NUMBER;
1306
1307 BEGIN
1308
1309 -- ********
1310 -- Get the Item_Type and Item_Key
1311 -- ********
1312
1313 l_colon := INSTR(document_id, ':');
1314 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1315 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1316
1317 -- ********
1318 -- Get the Attribute values
1319 -- ********
1320
1321 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1322 itemtype => l_item_type,
1323 itemkey => l_item_key,
1324 aname => 'OKL_CONTRACT_NUMBER');
1325
1326 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1327 itemtype => l_item_type,
1328 itemkey => l_item_key,
1329 aname => 'OKS_CONTRACT_NUMBER');
1330
1331 l_return_date := WF_ENGINE.GetItemAttrDate(
1332 itemtype => l_item_type,
1333 itemkey => l_item_key,
1334 aname => 'RETURN_DATE');
1335
1336 l_return_asset_id := WF_ENGINE.GetItemAttrText(
1337 itemtype => l_item_type,
1338 itemkey => l_item_key,
1339 aname => 'ASSET_ID');
1340
1341 -- ********
1342 -- Get Assets Details
1343 -- ********
1344
1345 get_assets_schedule (
1346 p_kle_id => l_return_asset_id,
1347 x_asset_schedule_tbl => l_asset_schedule_tbl);
1348
1349 -- ********
1350 -- Get Notification Body texts
1351 -- ********
1352
1353 get_set_noti_dtls (
1354 p_itemtype => l_item_type,
1355 p_itemkey => l_item_key,
1356 x_noti_rec => l_noti_rec);
1357
1358 -- ********
1359 -- Set the message body
1360 -- ********
1361
1362 l_msgbody :=
1363 '<html>
1364 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1365 .tableDataCell { font-family: Arial; font-size: 9pt; }
1366 </style>
1367 <body>
1368 <p></p>
1369 <p class="tableDataCell"> ' || l_noti_rec.return_main_body || ' </p>
1370 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1371 <table border="1">
1372 <tr>
1373 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1374 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1375 <td class="tableHeaderCell"> ' || l_noti_rec.asset_ret_date || ' </td>
1376 </tr>
1377 <tr>
1378 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1379 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1380 <td class="tableDataCell"> ' || l_return_date || ' </td>
1381 </tr>
1382 </table>
1383 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1384 <table border="1">
1385 <tr>
1386 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1387 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1388 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1389 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1390 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1391 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1392 </tr>';
1393 IF l_asset_schedule_tbl.COUNT > 0 THEN
1394 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1395
1396 l_msgbody := l_msgbody ||
1397 '<tr>
1398 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1399 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1400 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1401 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1402 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1403 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1404 </tr>';
1405 END LOOP;
1406 END IF;
1407
1408 l_msgbody := l_msgbody ||
1409 '</table>
1410 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1411 </body>
1412 </html>';
1413
1414 -- ********
1415 -- Set OUT variables
1416 -- ********
1417
1418 document := l_msgbody;
1419 document_type := display_type;
1420
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 NULL;
1424
1425 END pop_return_noti_dtls;
1426
1427 -- Start of comments
1428 --
1429 -- Procedure Name : pop_delink_err_noti_dtls
1430 -- Desciption : Populates the Linked Lease De-link Error and Termination Notification details
1431 -- When Source is 'ERROR'
1432 -- Business Rules :
1433 -- Parameters :
1434 -- Version : 1.0
1435 -- History : RMUNJULU created
1436 --
1437 -- End of comments
1438 PROCEDURE pop_delink_err_noti_dtls(
1439 document_id IN VARCHAR2,
1440 display_type IN VARCHAR2,
1441 document IN OUT NOCOPY VARCHAR2,
1442 document_type IN OUT NOCOPY VARCHAR2) IS
1443
1444 l_item_type WF_ITEMS.item_type%TYPE;
1445 l_item_key WF_ITEMS.item_key%TYPE;
1446 l_colon NUMBER;
1447 l_msgbody VARCHAR2(32000);
1448 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1449 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1450 l_quote_id VARCHAR2(200);
1451 l_asset_schedule_tbl kle_tbl_type;
1452 l_noti_rec noti_rec_type;
1453 i NUMBER := 1;
1454
1455 l_termination_date DATE;
1456 l_okl_chr_id NUMBER;
1457
1458 BEGIN
1459
1460 -- LIMITATIONS:
1461 -- For now Partial quotes are not allowed if linked service contract exists.
1462 -- So its always full termination. So get all linked assets
1463 -- for the lease contract, no need to check if all exists in quote,
1464 -- since all will exist.
1465
1466
1467 -- ********
1468 -- Get the Item_Type and Item_Key
1469 -- ********
1470
1471 l_colon := INSTR(document_id, ':');
1472 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1473 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1474
1475 -- ********
1476 -- Get the Attribute values
1477 -- ********
1478
1479 l_okl_chr_id := WF_ENGINE.GetItemAttrText(
1480 itemtype => l_item_type,
1481 itemkey => l_item_key,
1482 aname => 'OKL_CONTRACT_ID');
1483
1484 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1485 itemtype => l_item_type,
1486 itemkey => l_item_key,
1487 aname => 'OKL_CONTRACT_NUMBER');
1488
1489 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1490 itemtype => l_item_type,
1491 itemkey => l_item_key,
1492 aname => 'OKS_CONTRACT_NUMBER');
1493
1494 l_termination_date := WF_ENGINE.GetItemAttrDate(
1495 itemtype => l_item_type,
1496 itemkey => l_item_key,
1497 aname => 'TERMINATION_DATE');
1498
1499 l_quote_id := WF_ENGINE.GetItemAttrText(
1500 itemtype => l_item_type,
1501 itemkey => l_item_key,
1502 aname => 'QUOTE_ID');
1503
1504 -- ********
1505 -- Get Assets Details
1506 -- ********
1507
1508 get_assets_schedule (
1509 p_khr_id => l_okl_chr_id,
1510 p_quote_id => TO_NUMBER(l_quote_id),
1511 x_asset_schedule_tbl => l_asset_schedule_tbl);
1512
1513 -- ********
1514 -- Get Notification Body texts
1515 -- ********
1516
1517 get_set_noti_dtls (
1518 p_itemtype => l_item_type,
1519 p_itemkey => l_item_key,
1520 x_noti_rec => l_noti_rec);
1521
1522 -- ********
1523 -- Set the message body
1524 -- ********
1525
1526 l_msgbody :=
1527 '<html>
1528 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1529 .tableDataCell { font-family: Arial; font-size: 9pt; }
1530 </style>
1531 <body>
1532 <p></p>
1533 <p class="tableDataCell"> ' || l_noti_rec.error_main_body || ' </p>
1534 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1535 <table border="1">
1536 <tr>
1537 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1538 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1539 <td class="tableHeaderCell"> ' || l_noti_rec.termination_date || ' </td>
1540 </tr>
1541 <tr>
1542 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1543 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1544 <td class="tableDataCell"> ' || l_termination_date || ' </td>
1545 </tr>
1546 </table>
1547 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1548 <table border="1">
1549 <tr>
1550 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1551 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1552 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1553 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1554 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1555 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1556 </tr>';
1557 IF l_asset_schedule_tbl.COUNT > 0 THEN
1558 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1559
1560 l_msgbody := l_msgbody ||
1561 '<tr>
1562 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1563 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1564 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1565 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1566 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1567 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1568 </tr>';
1569 END LOOP;
1570 END IF;
1571
1572 l_msgbody := l_msgbody ||
1573 '</table>
1574 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1575 </body>
1576 </html>';
1577
1578 -- ********
1579 -- Set OUT variables
1580 -- ********
1581
1582 document := l_msgbody;
1583 document_type := display_type;
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 NULL;
1588
1589 END pop_delink_err_noti_dtls;
1590
1591 -- Start of comments
1592 --
1593 -- Procedure Name : pop_delink_noti_dtls
1594 -- Desciption : Populates the Linked Lease De-link and Termination Notification details
1595 -- When Source is 'SUCCESS'
1596 -- Business Rules :
1597 -- Parameters :
1598 -- Version : 1.0
1599 -- History : RMUNJULU created
1600 -- : RMUNJULU 23-DEC-03 SERVICE K UPDATES
1601 --
1602 -- End of comments
1603 PROCEDURE pop_delink_noti_dtls(
1604 document_id IN VARCHAR2,
1605 display_type IN VARCHAR2,
1606 document IN OUT NOCOPY VARCHAR2,
1607 document_type IN OUT NOCOPY VARCHAR2) IS
1608
1609 l_item_type WF_ITEMS.item_type%TYPE;
1610 l_item_key WF_ITEMS.item_key%TYPE;
1611 l_colon NUMBER;
1612 l_msgbody VARCHAR2(32000);
1613 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1614 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1615 l_quote_id VARCHAR2(200);
1616 l_asset_schedule_tbl kle_tbl_type;
1617 l_noti_rec noti_rec_type;
1618 i NUMBER := 1;
1619
1620 l_termination_date DATE;
1621 l_okl_chr_id NUMBER;
1622
1623
1624 BEGIN
1625
1626 -- LIMITATIONS:
1627 -- For now Partial quotes are not allowed if linked service contract exists.
1628 -- So its always full termination. So get all linked assets
1629 -- for the lease contract, no need to check if all exists in quote,
1630 -- since all will exist.
1631
1632 -- ********
1633 -- Get the Item_Type and Item_Key
1634 -- ********
1635
1636 l_colon := INSTR(document_id, ':');
1637 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1638 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1639
1640 -- ********
1641 -- Get the Attribute values
1642 -- ********
1643
1644 l_okl_chr_id := WF_ENGINE.GetItemAttrText(
1645 itemtype => l_item_type,
1646 itemkey => l_item_key,
1647 aname => 'OKL_CONTRACT_ID');
1648
1649 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1650 itemtype => l_item_type,
1651 itemkey => l_item_key,
1652 aname => 'OKL_CONTRACT_NUMBER');
1653
1654 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1655 itemtype => l_item_type,
1656 itemkey => l_item_key,
1657 aname => 'OKS_CONTRACT_NUMBER');
1658
1659 l_termination_date := WF_ENGINE.GetItemAttrDate(
1660 itemtype => l_item_type,
1661 itemkey => l_item_key,
1662 aname => 'TERMINATION_DATE');
1663
1664 l_quote_id := WF_ENGINE.GetItemAttrText(
1665 itemtype => l_item_type,
1666 itemkey => l_item_key,
1667 aname => 'QUOTE_ID');
1668
1669 -- ********
1670 -- Get Assets Details
1671 -- ********
1672
1673 get_assets_schedule (
1674 p_khr_id => l_okl_chr_id,
1675 p_quote_id => TO_NUMBER(l_quote_id),
1676 x_asset_schedule_tbl => l_asset_schedule_tbl);
1677
1678 -- ********
1679 -- Get Notification Body texts
1680 -- ********
1681
1682 get_set_noti_dtls (
1683 p_itemtype => l_item_type,
1684 p_itemkey => l_item_key,
1685 x_noti_rec => l_noti_rec);
1686
1687 -- ********
1688 -- Set the message body
1689 -- ********
1690 -- RMUNJULU 23-DEC-03 SERVICE K UPDATES
1691 -- Changed the message body removed the asset details
1692 l_msgbody :=
1693 '<html>
1694 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1695 .tableDataCell { font-family: Arial; font-size: 9pt; }
1696 </style>
1697 <body>
1698 <p></p>
1699 <p class="tableDataCell"> ' || l_noti_rec.success_main_body || ' </p>
1700 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1701 <table border="1">
1702 <tr>
1703 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1704 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1705 <td class="tableHeaderCell"> ' || l_noti_rec.termination_date || ' </td>
1706 </tr>
1707
1708 <tr>
1709 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1710 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1711 <td class="tableDataCell"> ' || l_termination_date || ' </td>
1712 </tr>
1713 </table>
1714 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1715 </body>
1716 </html>';
1717
1718 -- ********
1719 -- Set OUT variables
1720 -- ********
1721
1722 document := l_msgbody;
1723 document_type := display_type;
1724
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 NULL;
1728
1729 END pop_delink_noti_dtls;
1730
1731 -- Start of comments
1732 --
1733 -- Procedure Name : pop_term_noti_dtls
1734 -- Desciption : Populates the Linked Lease Termination Notification details
1735 -- When Source is 'TERMINATION'
1736 -- Business Rules :
1737 -- Parameters :
1738 -- Version : 1.0
1739 -- History : RMUNJULU created
1740 --
1741 -- End of comments
1742 PROCEDURE pop_term_noti_dtls(
1743 document_id IN VARCHAR2,
1744 display_type IN VARCHAR2,
1745 document IN OUT NOCOPY VARCHAR2,
1746 document_type IN OUT NOCOPY VARCHAR2) IS
1747
1748 l_item_type WF_ITEMS.item_type%TYPE;
1749 l_item_key WF_ITEMS.item_key%TYPE;
1750 l_colon NUMBER;
1751 l_msgbody VARCHAR2(32000);
1752 l_okl_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1753 l_oks_contract_number OKC_K_HEADERS_B.contract_number%TYPE;
1754 l_quote_id VARCHAR2(200);
1755 l_asset_schedule_tbl kle_tbl_type;
1756 l_noti_rec noti_rec_type;
1757 i NUMBER := 1;
1758
1759 l_termination_date DATE;
1760 l_okl_chr_id NUMBER;
1761
1762 BEGIN
1763
1764 -- LIMITATIONS:
1765 -- For now Partial quotes are not allowed if linked service contract exists.
1766 -- So its always full termination. So get all linked assets
1767 -- for the lease contract, no need to check if all exists in quote,
1768 -- since all will exist.
1769
1770 -- ********
1771 -- Get the Item_Type and Item_Key
1772 -- ********
1773
1774 l_colon := INSTR(document_id, ':');
1775 l_item_type := SUBSTR(document_id, 1, l_colon - 1);
1776 l_item_key := SUBSTR(document_id, l_colon + 1, LENGTH(document_id) - l_colon);
1777
1778 -- ********
1779 -- Get the Attribute values
1780 -- ********
1781
1782 l_okl_chr_id := WF_ENGINE.GetItemAttrText(
1783 itemtype => l_item_type,
1784 itemkey => l_item_key,
1785 aname => 'OKL_CONTRACT_ID');
1786
1787 l_okl_contract_number := WF_ENGINE.GetItemAttrText(
1788 itemtype => l_item_type,
1789 itemkey => l_item_key,
1790 aname => 'OKL_CONTRACT_NUMBER');
1791
1792 l_oks_contract_number := WF_ENGINE.GetItemAttrText(
1793 itemtype => l_item_type,
1794 itemkey => l_item_key,
1795 aname => 'OKS_CONTRACT_NUMBER');
1796
1797 l_termination_date := WF_ENGINE.GetItemAttrDate(
1798 itemtype => l_item_type,
1799 itemkey => l_item_key,
1800 aname => 'TERMINATION_DATE');
1801
1802 l_quote_id := WF_ENGINE.GetItemAttrText(
1803 itemtype => l_item_type,
1804 itemkey => l_item_key,
1805 aname => 'QUOTE_ID');
1806
1807 -- ********
1808 -- Get Assets Details
1809 -- ********
1810
1811 get_assets_schedule (
1812 p_khr_id => l_okl_chr_id,
1813 p_quote_id => TO_NUMBER(l_quote_id),
1814 x_asset_schedule_tbl => l_asset_schedule_tbl);
1815
1816 -- ********
1817 -- Get Notification Body texts
1818 -- ********
1819
1820 get_set_noti_dtls (
1821 p_itemtype => l_item_type,
1822 p_itemkey => l_item_key,
1823 x_noti_rec => l_noti_rec);
1824
1825 -- ********
1826 -- Set the message body
1827 -- ********
1828
1829 l_msgbody :=
1830 '<html>
1831 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt; font-weight: bold;}
1832 .tableDataCell { font-family: Arial; font-size: 9pt; }
1833 </style>
1834 <body>
1835 <p></p>
1836 <p class="tableDataCell"> ' || l_noti_rec.termination_main_body || ' </p>
1837 <p class="tableHeaderCell"> ' || l_noti_rec.contract_dtls || ' </p>
1838 <table border="1">
1839 <tr>
1840 <td class="tableHeaderCell"> ' || l_noti_rec.lease_contract || ' </td>
1841 <td class="tableHeaderCell"> ' || l_noti_rec.service_contract || ' </td>
1842 <td class="tableHeaderCell"> ' || l_noti_rec.termination_date || ' </td>
1843 </tr>
1844 <tr>
1845 <td class="tableDataCell"> ' || l_okl_contract_number || ' </td>
1846 <td class="tableDataCell"> ' || l_oks_contract_number || ' </td>
1847 <td class="tableDataCell"> ' || l_termination_date || ' </td>
1848 </tr>
1849 </table>
1850 <p class="tableHeaderCell"> ' || l_noti_rec.schedule_assets || ' </p>
1851 <table border="1">
1852 <tr>
1853 <td class="tableHeaderCell"> ' || l_noti_rec.asset_num || ' </td>
1854 <td class="tableHeaderCell"> ' || l_noti_rec.item || ' </td>
1855 <td class="tableHeaderCell"> ' || l_noti_rec.item_description || ' </td>
1856 <td class="tableHeaderCell"> ' || l_noti_rec.reference_num || ' </td>
1857 <td class="tableHeaderCell"> ' || l_noti_rec.serial_num || ' </td>
1858 <td class="tableHeaderCell"> ' || l_noti_rec.quantity || ' </td>
1859 </tr>';
1860 IF l_asset_schedule_tbl.COUNT > 0 THEN
1861 FOR i IN l_asset_schedule_tbl.FIRST..l_asset_schedule_tbl.LAST LOOP
1862
1863 l_msgbody := l_msgbody ||
1864 '<tr>
1865 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_number||'</td>
1866 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_number||'</td>
1867 <td class="tableDataCell">'||l_asset_schedule_tbl(i).item_description||'</td>
1868 <td class="tableDataCell">'||l_asset_schedule_tbl(i).install_base_number||'</td>
1869 <td class="tableDataCell">'||l_asset_schedule_tbl(i).serial_number||'</td>
1870 <td class="tableDataCell">'||l_asset_schedule_tbl(i).asset_quantity||'</td>
1871 </tr>';
1872 END LOOP;
1873 END IF;
1874
1875 l_msgbody := l_msgbody ||
1876 '</table>
1877 <p class="tableDataCell"> ' || l_noti_rec.body_end || ' </p>
1878 </body>
1879 </html>';
1880
1881 -- ********
1882 -- Set OUT variables
1883 -- ********
1884
1885 document := l_msgbody;
1886 document_type := display_type;
1887
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 NULL;
1891
1892 END pop_term_noti_dtls;
1893
1894 -- Start of comments
1895 --
1896 -- Procedure Name : check_source
1897 -- Desciption : Checks the source of the WF call, this will decide which
1898 -- path to take, which will send a noti
1899 -- Business Rules :
1900 -- Parameters :
1901 -- Version : 1.0
1902 -- History : RMUNJULU created
1903 --
1904 -- End of comments
1905 PROCEDURE check_source(
1906 itemtype IN VARCHAR2,
1907 itemkey IN VARCHAR2,
1908 actid IN NUMBER,
1909 funcmode IN VARCHAR2,
1910 resultout OUT NOCOPY VARCHAR2) IS
1911
1912
1913 l_source VARCHAR2(100);
1914
1915
1916 BEGIN
1917
1918 --
1919 -- RUN mode
1920 --
1921 IF (funcmode = 'RUN') THEN
1922
1923 -- ********
1924 -- Get the values
1925 -- ********
1926
1927 l_source := WF_ENGINE.GetItemAttrText(
1928 itemtype => itemtype,
1929 itemkey => itemkey,
1930 aname => 'SOURCE');
1931
1932 -- ********
1933 -- Set the Notification Body and Output of this function based on source
1934 -- ********
1935
1936 IF l_source = 'SUCCESS' THEN -- From termination, de-link was successful
1937
1938 WF_ENGINE.SetItemAttrText(
1939 itemtype => itemtype,
1940 itemkey => itemkey,
1941 aname => 'MESSAGE_DOC',
1942 avalue =>
1943 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_delink_noti_dtls /'||itemtype||':'||itemkey);
1944
1945 resultout := 'COMPLETE:SOURCE_SUCCESS';
1946
1947 ELSIF l_source = 'ERROR' THEN -- From termination, de-link failed
1948
1949 WF_ENGINE.SetItemAttrText(
1950 itemtype => itemtype,
1951 itemkey => itemkey,
1952 aname => 'MESSAGE_DOC',
1953 avalue =>
1954 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_delink_err_noti_dtls /'||itemtype||':'||itemkey);
1955
1956 resultout := 'COMPLETE:SOURCE_ERROR';
1957
1958 ELSIF l_source = 'TERMINATION' THEN -- From termination, de-link not needed
1959
1960 WF_ENGINE.SetItemAttrText(
1961 itemtype => itemtype,
1962 itemkey => itemkey,
1963 aname => 'MESSAGE_DOC',
1964 avalue =>
1965 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_term_noti_dtls /'||itemtype||':'||itemkey);
1966
1967 resultout := 'COMPLETE:SOURCE_TERMINATION';
1968
1969 ELSIF l_source = 'DISPOSE' THEN -- From Asset Dispose
1970
1971 WF_ENGINE.SetItemAttrText(
1972 itemtype => itemtype,
1973 itemkey => itemkey,
1974 aname => 'MESSAGE_DOC',
1975 avalue =>
1976 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_dispose_noti_dtls /'||itemtype||':'||itemkey);
1977
1978 resultout := 'COMPLETE:SOURCE_DISPOSE';
1979
1980 ELSIF l_source = 'RETURN' THEN -- From Asset Return
1981
1982 WF_ENGINE.SetItemAttrText(
1983 itemtype => itemtype,
1984 itemkey => itemkey,
1985 aname => 'MESSAGE_DOC',
1986 avalue =>
1987 'PLSQL:OKL_AM_SERVICE_K_INT_WF.pop_return_noti_dtls /'||itemtype||':'||itemkey);
1988
1989 resultout := 'COMPLETE:SOURCE_RETURN';
1990
1991 END IF;
1992
1993 RETURN ;
1994 END IF;
1995
1996 --
1997 -- CANCEL mode
1998 --
1999 IF (funcmode = 'CANCEL') THEN
2000
2001 resultout := 'COMPLETE:';
2002 RETURN;
2003
2004 END IF;
2005
2006 --
2007 -- TIMEOUT mode
2008 --
2009 IF (funcmode = 'TIMEOUT') THEN
2010
2011 resultout := 'COMPLETE:';
2012 RETURN;
2013
2014 END IF;
2015
2016
2017 EXCEPTION
2018
2019 WHEN OTHERS THEN
2020 WF_CORE.context('OKL_AM_SERVICE_K_INT_WF' , 'check_source',
2021 itemtype, itemkey, actid, funcmode);
2022 RAISE;
2023
2024 END check_source;
2025
2026
2027 END OKL_AM_SERVICE_K_INT_WF;