1 PACKAGE BODY oks_code_hook AS
2 /* $Header: OKSCCHKB.pls 120.5.12020000.2 2013/02/06 07:07:06 mchandak ship $ */
3
4 /* PROCEDURE
5 tax_trx_date This routine is used to set a Transaction Date to calculate tax.
6
7 INPUT PARAMETERS
8 p_chr_id Contract Header Id
9 p_cle_id Contract Line Id
10 p_hdr_start_date Contract Header Start Date
11 p_lin_start_date Contract Line Start Date
12
13 RETURN VALUE
14 x_hook 1 Hook has been used
15 0 Hook has not been used
16 -1 Error in Hook
17 x_date Transaction Date for calculating tax
18 */
19 PROCEDURE tax_trx_date
20 (p_chr_id IN NUMBER,
21 p_cle_id IN NUMBER,
22 p_hdr_start_date IN DATE,
23 p_lin_start_date IN DATE,
24 x_hook OUT NOCOPY NUMBER,
25 x_date OUT NOCOPY DATE
26 )
27 IS
28
29 BEGIN
30
31 x_hook := 0;
32 x_date := NULL;
33
34 /* When Input Parameter p_chr_id is NOT NULL and p_cle_id is NULL then tax calculation
35 is for contract Header and p_hdr_start_date is contract header start date.
36
37 When Input Parameter p_cle_id is NOT NULL and p_chr_id is NULL then tax calculation
38 is for contract Line and p_lin_start_date is contract Line start date.
39
40 Output parameter x_date should be set to the date on which the tax calculation should be based.
41 If Hook is used then assign x_hook as 1.
42
43 By Default x_hook = 0 and x_date is NULL Which means transaction date considered for tax
44 calculation is contract header/line start date. */
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 x_hook :=-1;
49 END tax_trx_date;
50 /*added for bug7668259*/
51 /*----------------------------------------------------------------------------------------+
52 Procedure
53 billing_interface_st_date This routine is a client extension that lets the user
54 have different approach while running service contracts
55 main billing program.
56
57 PARAMETERS
58
59 RETURN VALUE
60 x_hook 1 Hook has been used
61 0 Hook has not been used
62 -1 Error in Hook
63 x_date Billing Interface Start Date
64 x_hint Type of Parallel Processing
65 Only Allowed Value is "FULL".
66
67 x_hint value is used only for the following case.
68 Service Contracts Main Billing Program is submitted without providing
69 any parameters i.e Main Billing Program Across Operating Units.
70 If the customer wants to process the data in FULL Parallel mode; i.e parallel
71 processing is used on oks_level_elements table and for contracts header
72 and line table.
73 x_hint is used by the Main billing Program for across operating units
74 only if the value for x_hook is 0,x_date is NULL and x_hint is 'FULL'.
75
76 x_hook and x_date is used by the Service Contracts Main Billing Program
77 in following scenarios.
78 1) Submitted for across operating units(i.e without providing any parameters).
79 2) Submitted for a specific operating unit.
80 3) Submitted with a combination of Contract Categry and Org id.
81 4) Submitted with a combination of Contract Group and Ord id.
82 +-----------------------------------------------------------------------------------------*/
83
84 PROCEDURE billing_interface_st_date
85 (x_hook OUT NOCOPY NUMBER,
86 x_date OUT NOCOPY DATE,
87 x_hint OUT NOCOPY VARCHAR2)
88 IS
89 BEGIN
90 x_hook := 0;
91 x_date := NULL;
92 x_hint := 'FULL';
93 /* To customize this hook, Call the Custom Logic to derive the Billing interface start date
94 and pass the start_date to x_date which is passed as an out parameter.If Hook is used
95 assign x_hook as 1. This hook is used as part of Service Contract Main Billing program
96 in order to filter the data based upon date_interface start and end dates
97
98 x_hint value can be either NULL or FULL based upon the requirement by the customer*/
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 x_hook :=-1;
103 END billing_interface_st_date;
104
105 /* Added by sjanakir for Bug#5073827 */
106 /* PROCEDURE
107 calc_header_amt This routine is a client extension that lets the user have
108 different approach for calculatiog header amount and tax.
109
110 PARAMETERS
111 p_contract_id Contract Header Id
112
113 RETURN VALUE
114 x_hook 1 Hook has been used
115 0 Hook has not been used
116 -1 Error in Hook
117 x_tax Header Tax Amount
118 x_total Total Contract Header Amount */
119
120 PROCEDURE calc_header_amt
121 (p_contract_id IN NUMBER,
122 x_hook OUT NOCOPY NUMBER,
123 x_tax OUT NOCOPY NUMBER,
124 x_total OUT NOCOPY NUMBER)
125 IS
126 BEGIN
127 x_hook := 0;
128 x_tax := 0;
129 x_total := 0;
130
131 /* To customize this hook, Assign x_hook as 1 and Call the Custom Logic to derive the total amount and total
132 tax. Output parameters of Custom Logic should be x_total(Total Header Amount) and x_tax (Total Tax).
133 By Default x_hook = 0 which means system would follow standard oracle logic to calculate the header amount
134 and tax.
135 */
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 x_hook :=-1;
140 END calc_header_amt;
141
142 /* added for ER 7687114*/
143 ----------------------------------------------------------------------------------
144 /* FUNCTION
145 custom_function This function will be used to derive custom data specific to a contract
146
147 INPUT PARAMETERS
148 p_chr_id Contract Header Id
149
150 RETURN VALUE
151 l_custom_value Custom data to be derived by the customer.
152 */
153 FUNCTION custom_function
154 (p_chr_id IN NUMBER
155 )
156 Return VARCHAR2 IS
157
158 l_custom_value VARCHAR2(200);
159
160 BEGIN
161
162 l_custom_value := '-99';
163
164 /* The customer has to write their own logic to derive the required information based on
165 their business needs and populate l_custom_value. Only one value should be passed back
166 as return value.
167
168 By default the return value is '-99'.
169 */
170 RETURN l_custom_value;
171 EXCEPTION
172 WHEN OTHERS THEN
173 l_custom_value := '-99';
174 END custom_function;
175
176 /* Added by cgopinee for Bug#9166537*/
177 /* FUNCTION
178 date_asmblr_fn This function is an extension that lets the user have
179 additional conditional calls to api's to filter the contracts
180 upfront during the date assembler program rather than defining
181 them as functions in independent conditions.
182
183 PARAMETERS
184 p_chr_id contract_header_id,
185 p_k_num contract_number,
186 p_k_modifier contract_number_modifier,
187 p_k_end_date contract_end_date,
188 p_class IN VARCHAR2,
189 p_scs_code IN VARCHAR2,
190 p_k_estamount IN NUMBER,
191 p_sts_code IN VARCHAR2,
192 p_auth_orgid IN NUMBER
193
194
195 RETURN VALUE
196 'Y' or 'N' */
197
198 FUNCTION date_asmblr_fn
199 (p_chr_id IN NUMBER,
200 p_k_num IN VARCHAR2,
201 p_k_modifier IN VARCHAR2,
202 p_k_end_date IN DATE,
203 p_class IN VARCHAR2,
204 p_scs_code IN VARCHAR2,
205 p_k_estamount IN NUMBER,
206 p_sts_code IN VARCHAR2,
207 p_auth_orgid IN NUMBER
208 )
209 RETURN VARCHAR2 IS
210 BEGIN
211 /* The customer has to write their own logic to filter the contracts based on
212 their business needs. The customer can make the function calls that they define
213 in FUNCTIONS of independent conditions. This would filter the contracts upfront
214 before being picked for renewal.
215
216 Only one value(either 'Y'/'N') should be passed back as return value.
217
218 By default the return value is 'Y'.
219 */
220
221 RETURN 'Y';
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 RETURN 'Y';
226 END date_asmblr_fn;
227
228 /* Added by skuchima for Bug#9724454 */
229 /*
230 PARAMETERS
231
232 p_oks_cnt Number of contracts picked from interface table
233
234 RETURN VALUE
235 x_hook 0 Hook has not been used
236 x_hook 1 Hook has been used
237 x_hook -1 Error in Hook
238
239 The customer has to write their own logic for post processing after the OKCALERT workflow is launced in Renewal process by the concurrent program
240 Service Contracts Date Assembler Workflow Manager
241 */
242 PROCEDURE custom_da_prc(p_oks_cnt IN NUMBER,
243 x_hook OUT NOCOPY NUMBER
244 ) IS
245
246 l_request_id NUMBER;
247 -- PLSQL Table for storing Request IDs
248 type REQ_TAB is table of number index by binary_integer;
249 L_REQ_TAB REQ_TAB;
250 L_REQ_COUNT number := 0;
251 x_PHASE varchar2(2000);
252 x_STATUS varchar2(2000);
253 x_DEV_PHASE varchar2(2000);
254 x_DEV_STATUS varchar2(2000);
255 x_MESSAGE varchar2(2000);
256 l_RETURN_STATUS boolean;
257 l_spawn_wf NUMBER;
258 l_queue_size NUMBER;
259 l_num_wf_prc NUMBER;
260
261 BEGIN
262
263 x_hook := 0;
264
265 /* To customize this hook assign x_hook:=1
266 They customer can write their own logic for post processing after the launch of OKCLAERT workflow in Renewal Process*/
267
268
269 /* Sample implementation of the hook.
270 The below sample code submits the Workflow Background Processes based on the profile values
271 To implement the below sample code customer should create a custom profile to fetch maximum number of Workflow Background Processes that can be spawned
272 when this custom hook is run*/
273
274 /* Sample code starts here
275 x_hook:=1;
276 ---Total Queue_Size
277 l_queue_size := Nvl(FND_PROFILE.VALUE('OKS_DA_QUEUE_SIZE'),0);
278 --Custom profile can be created by the user to fetch the maximum number of workflow processes that should be spawned
279 l_num_wf_prc := Nvl(FND_PROFILE.VALUE('OKS_DA_NUM_WF_PRC'),1);
280
281 IF(l_queue_size<>0) then
282 l_spawn_wf:=Ceil((p_oks_cnt * l_num_wf_prc)/l_queue_size);
283 ELSE
284 l_spawn_wf:=0;
285 END IF;
286
287
288 FOR i IN 1..l_spawn_wf LOOP
289
290 --spawn the workflow background process
291 L_REQUEST_ID:= FND_REQUEST.SUBMIT_REQUEST(
292 APPLICATION => 'FND',
293 PROGRAM => 'FNDWFBG',
294 ARGUMENT1 => 'OKCALERT',
295 ARGUMENT2 => NULL,
296 ARGUMENT3 => NULL,
297 ARGUMENT4 => 'Y',
298 ARGUMENT5 => 'Y',
299 ARGUMENT6 => NULL);
300
301 COMMIT;
302
303 --capture all the request ids
304 L_REQ_COUNT := L_REQ_COUNT + 1;
305 L_REQ_TAB(L_REQ_COUNT) := L_REQUEST_ID;
306
307
308 END LOOP;
309 IF(L_REQ_COUNT<>0) then
310 --check if any child program is running or not
311 FOR I in L_REQ_TAB.first .. L_REQ_TAB.last LOOP
312
313 L_RETURN_STATUS:=FND_CONCURRENT.WAIT_FOR_REQUEST(L_REQ_TAB(I)
314 ,(20) -- 20 seconds to wait next check
315 ,(1200 * 60) -- Wait for max of 1200 minutes
316 ,X_PHASE
317 ,X_STATUS
318 ,X_DEV_PHASE
319 ,X_DEV_STATUS
320 ,X_MESSAGE);
321
322 if (L_RETURN_STATUS = false) then
323 EXIT;
324 x_hook:=-1;
325 END IF;
326 END LOOP;
327 END IF;
328 End of the Sample code */
329 EXCEPTION
330 WHEN OTHERS THEN
331 x_hook := -1;
332 END custom_da_prc;
333 /*Added by spingali for bug#13024593
334 PARAMETERS
335
336 p_id contract id;
337 p_canc_reason_code cancellation reason code;
338 p_term_cancel_source source of the transaction
339 (Valid values:
340 If comes from customer acceptance page, then its 'CUSTOMER'.
341 For other transactions, the value would be 'MANUAL'.)
342 p_new_sts_code New status code to be refelected in contract.
343
344 RETURN VALUE
345 x_hook 0 Hook has not been used
346 x_hook 1 Hook has been used
347 x_hook -1 Error in Hook
348
349 The customer has to write their own logic to return the contract status to which they want to set when a contract is declined.
350 */
351 PROCEDURE get_custom_status(p_id IN NUMBER,
352 p_canc_reason_code IN varchar2,
353 p_term_cancel_source IN varchar2,
354 p_new_sts_code OUT NOCOPY varchar2 ,
355 x_hook OUT NOCOPY NUMBER
356 ) IS
357
358 BEGIN
359
360 x_hook := 0;
361
362 /* To customize this hook assign x_hook:=1
363 They customer can write their own logic to return the contract status to which they want to set when a contract is declined.*/
364 /*If p_term_cancel_source = 'CUSTOMER', then transaction is coming from customer acceptance page
365 and if p_term_cancel_source = 'MANUAL', then its coming from other flows like manual status change etc*/
366
367 /* Sample implementation of the hook.
368 /* Sample code starts here
369 x_hook:=1;
370 If p_canc_reason_code is not null and p_term_cancel_source = 'CUSTOMER' then
371 p_new_sts_code :=p_canc_reason_code;
372 End If;
373 End of the Sample code */
374 EXCEPTION
375 WHEN OTHERS THEN
376 x_hook := -1;
377 END get_custom_status;
378
379 PROCEDURE Update_Merge_Account(req_id IN NUMBER
380 ,set_number IN NUMBER
381 ,process_mode IN VARCHAR2
382 ,x_hook OUT NOCOPY NUMBER
383 ) IS
384
385 BEGIN
386
387 x_hook := 0;
388
389 /* To customize this hook assign x_hook:=1
390 They customer can write their own logic to update the TAS to update the information which was updated in OKS during customer merge*/
391
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 x_hook := -1;
396 END Update_Merge_Account;
397
398
399 /*
400 Added by spingali for bug#14056524
401 PARAMETERS
402
403 p_entity_name VARCHAR2 - Name of the entity that is being merged
404 p_from_id NUMBER - Id of the record that is being merged
405 p_from_fk_id NUMBER - Id of the Old Parent
406 p_to_fk_id NUMBER - Id of the New Parent
407 p_parent_entity_name VARCHAR2 - Parent entity name
408 p_batch_id NUMBER - Id of the Batch
409 p_batch_party_id NUMBER - Id of the batch and party record
410
411 RETURN VALUE
412 x_hook 0 Hook has not been used
413 x_hook 1 Hook has been used
414 x_hook -1 Error in Hook
415
416 The customer has to write their own logic to update the TAS with the information updated in OKS during Party Merge.
417 */
418
419
420 PROCEDURE Update_Party_Merge(
421 p_entity_name IN VARCHAR2,
422 p_from_id IN NUMBER,
423 p_from_fk_id IN NUMBER,
424 p_to_fk_id IN NUMBER,
425 p_parent_entity_name IN VARCHAR2,
426 p_batch_id IN NUMBER,
427 p_batch_party_id IN NUMBER,
428 x_hook OUT NOCOPY VARCHAR2) IS
429
430 BEGIN
431
432 x_hook := 0;
433
434 /* To customize this hook assign x_hook:=1
435 They customer can write their own logic to update the TAS to update the information which was updated in OKS during Party merge*/
436
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 x_hook := -1;
441 END Update_Party_Merge;
442
443 /*
444 Added by VGUJARAT for bug#14404570
445 PARAMETERS
446
447 p_column - VARCHAR2 - Determines the type of data to be retrieved
448 P_LSE_ID - NUMBER - Line type
449 p_sub_line_id - NUMBER - Contract Subline Id
450 p_chr_id - NUMBER - Contract Header Id
451
452 RETURN VALUE - VARCHAR2 - Source Transaction Information
453
454 Following function is used as part of VRM project to retrieve the source transaction information.
455 */
456
457 FUNCTION get_contract_source_details(p_column IN VARCHAR2,
458 P_LSE_ID IN NUMBER,
459 p_sub_line_id IN NUMBER ,
460 p_chr_id IN number) RETURN VARCHAR2
461 IS
462 -- License Transfer --
463 CURSOR Get_transfer_details(p_cle_id IN NUMBER) IS
464 SELECT Op.meaning Source,
465 cl.opn_code,
466 (Kh.COntract_number||Kh.COntract_number_modifier) Contract,
467 (Tl.line_number||'.'||Kl.Line_Number) line_Number
468 FROM okc_class_operations cl
469 , okc_operation_instances oip
470 , okc_operation_lines ol
471 , okc_operations_v Op
472 , Okc_k_headers_b Kh
473 , Okc_k_lines_b Kl
474 ,Okc_k_lines_b Tl
475 WHERE ol.subject_cle_id = p_cle_id --"Subline ID"
476 AND oip.cop_id = cl.Id
477 AND cl.cls_code = 'SERVICE'
478 AND ol.oie_id = oip.id
479 AND Op.Code = cl.opn_code
480 AND Kh.Id = Kl.dnz_chr_id
481 AND Kl.Id = ol.object_cle_id
482 AND Kl.cle_id = Tl.Id;
483
484 Get_transfer_details_rec Get_transfer_details%rowtype;
485
486 CURSOR get_object_details (p_cle_id IN NUMBER, p_object_code IN VARCHAR2) IS
487 SELECT object1_id1 from
488 OKC_K_REL_OBJS_V
489 WHERE cle_id = p_cle_id
490 AND JTOT_OBJECT1_CODE = p_object_code;
491
492 get_object_details_rec get_object_details%rowtype;
493
494 CURSOR get_transfer_details_sub(p_line_id IN NUMBER) IS
495 SELECT order_number , decode( to_char(nvl(line.option_number,-99)), '-99',
496 decode(to_char(nvl(line.component_number,'-99')) , '-99' ,
497 line.line_number||'.'||line.shipment_number||'...'|| line.service_number
498 ,line.line_number||'.'||line.shipment_number||'..'||
499 line.component_number||'.'||line.service_number )
500 , decode(to_char(nvl(line.component_number,-99)) , '-99',
501 line.line_number||'.'||line.shipment_number||'.'||
502 line.option_number||'..'||line.service_number
503 ,line.line_number||'.'||line.shipment_number||'.'||line.option_number ||'.'||
504 line.component_number||'.'||line.service_number ) ) line_number
505 FROM oe_order_lines_all line, oe_order_headers_all hdr
506 WHERE line.line_id = p_line_id
507 AND hdr.header_id =line.header_id;
508
509 Get_transfer_details_sub_rec Get_transfer_details_sub%rowtype;
510
511 CURSOR get_fnd_csr(p_type VARCHAR2,p_code VARCHAR2) IS
512 SELECT Meaning
513 FROM Fnd_Lookups
514 WHERE Lookup_Type = p_type
515 AND Lookup_Code = p_code;
516
517 CURSOR rel_objs_csr (p_chr_id IN NUMBER) IS
518 SELECT object1_id1
519 FROM okc_k_rel_objs_v
520 WHERE chr_id = p_chr_id
521 AND cle_id IS NULL;
522
523 rel_objs_rec rel_objs_csr%ROWTYPE;
524
525 CURSOR order_header_csr(p_object1_id1 IN NUMBER) IS
526 SELECT order_number
527 FROM okx_order_headers_v
528 WHERE id1 = p_object1_id1;
529
530 order_header_rec order_header_csr%ROWTYPE;
531
532
533
534 get_fnd_rec get_fnd_csr%ROWTYPE;
535 l_source_document VARCHAR2(150);
536 l_source_line VARCHAR2(150);
537 l_source_transaction VARCHAR2(150);
538 l_order_number NUMBER;
539 -- License Transfer --
540 BEGIN
541
542 -- If Service Or Extended Warranty
543 -- 1. Relational Objects
544 -- 2. Operation Lines. Preference for Renewals
545 -- elsif for warranty
546 -- 1. Relational object for header exists, then show as Sales order as the
547 -- Source and
548 -- Header Order number as the document number
549 -- 2. Operation Lines
550 -- 3. Created from Installed base
551 -- elsif for Usages
552 -- 1. Operation lines. Preference for Renewals
553 -- end if;
554
555 IF P_lse_id in (1,19)
556 THEN
557
558 OPEN get_object_details(P_SUB_LINE_ID,'OKX_ORDERLINE');
559 FETCH Get_object_details INTO Get_object_details_rec;
560 IF Get_object_details%FOUND
561 THEN
562
563 OPEN get_transfer_details_sub(Get_object_details_rec.object1_id1);
564 FETCH Get_transfer_details_sub into Get_transfer_details_sub_rec;
565 close get_transfer_details_sub;
566
567 l_source_document:= Get_transfer_details_sub_rec.order_number;
568 l_source_line := Get_transfer_details_sub_rec.line_number ;
569
570 --transaction source code in this case will always be 'SALES_ORDER'
571 OPEN get_fnd_csr('OKS_TRANSACTION_SOURCE','SALES_ORDER');
572 FETCH get_fnd_csr INTO get_fnd_rec;
573 IF get_fnd_csr%FOUND
574 THEN
575 l_source_transaction:= get_fnd_rec.meaning ;
576 END IF;
577 CLOSE get_fnd_csr;
578
579 ELSE
580
581 OPEN Get_transfer_details(P_SUB_LINE_ID);
582 LOOP
583 FETCH Get_transfer_details into Get_transfer_details_rec;
584 EXIT when Get_transfer_details%NOTFOUND;
585
586 IF Get_transfer_details_rec.opn_code in ('RENEWAL','REN_CON')
587 THEN
588 EXIT;
589 END IF;
590 END LOOP;
591 IF Get_transfer_details%ROWCOUNT > 0
592 THEN
593 l_source_document:=Get_transfer_details_rec.contract ;
594 l_source_line:=Get_transfer_details_rec.line_number ;
595 l_source_transaction:=Get_transfer_details_rec.source ;
596 END IF;
597 close Get_transfer_details;
598 END IF; --IF Get_object_details%FOUND
599 CLOSE get_object_details;
600
601 ELSIF P_lse_id =14
602 THEN
603
604 OPEN rel_objs_csr(p_chr_id);
605 FETCH rel_objs_csr INTO rel_objs_rec;
606 CLOSE rel_objs_csr;
607
608 IF rel_objs_rec.object1_id1 IS NOT NULL
609 THEN
610 OPEN order_header_csr(rel_objs_rec.object1_id1);
611 FETCH order_header_csr INTO order_header_rec;
612 CLOSE order_header_csr;
613
614 l_order_number:=order_header_rec.order_number;
615 END IF;
616
617
618 IF l_order_number is NOT NULL
619 THEN
620 OPEN get_fnd_csr('OKS_TRANSACTION_SOURCE','SALES_ORDER');
621 FETCH get_fnd_csr INTO get_fnd_rec;
622 IF get_fnd_csr%FOUND
623 THEN
624 l_source_transaction:=get_fnd_rec.meaning;
625 l_source_document:= l_order_number;
626 END IF;
627 CLOSE get_fnd_csr;
628 END IF;
629
630 --If no record FOUND in above relation objects check in operations lines
631 --if there is no operations lines then transaction source will be updated as
632 --Install base
633 IF l_source_transaction IS NULL
634 THEN
635 OPEN Get_transfer_details(P_SUB_LINE_ID);
636 LOOP
637 FETCH Get_transfer_details into Get_transfer_details_rec;
638 EXIT WHEN Get_transfer_details%NOTFOUND;
639 IF Get_transfer_details_rec.opn_code in ('RENEWAL','REN_CON')
640 THEN
641 EXIT;
642 END IF;
643 END LOOP;
644 IF Get_transfer_details%ROWCOUNT > 0
645 THEN
646 l_source_document:=Get_transfer_details_rec.contract ;
647 l_source_line:=Get_transfer_details_rec.line_number ;
648 l_source_transaction:=Get_transfer_details_rec.source ;
649
650 ELSE
651 OPEN get_fnd_csr('OKS_TRANSACTION_SOURCE','INSTALL_BASE');
652 FETCH get_fnd_csr INTO get_fnd_rec;
653 IF get_fnd_csr%FOUND
654 THEN
655 l_source_transaction:=get_fnd_rec.meaning;
656 END IF;
657 CLOSE get_fnd_csr;
658 END IF;
659 close Get_transfer_details;
660 END IF;
661
662 ELSIF P_lse_id =12
663 THEN
664 OPEN Get_transfer_details(p_sub_line_id);
665 LOOP
666 FETCH Get_transfer_details into Get_transfer_details_rec;
667 EXIT WHEN Get_transfer_details%NOTFOUND;
668 IF Get_transfer_details_rec.opn_code in ('RENEWAL','REN_CON')
669 THEN
670 EXIT;
671 END IF;
672 END LOOP;
673
674 IF Get_transfer_details%ROWCOUNT > 0
675 THEN
676 l_source_document:=Get_transfer_details_rec.contract ;
677 l_source_line:=Get_transfer_details_rec.line_number ;
678 l_source_transaction:=Get_transfer_details_rec.source ;
679 END IF;
680 close Get_Transfer_details;
681
682 END IF;
683
684 IF p_column= 'SOURCE_DOCUMENT' THEN
685 RETURN l_source_document;
686 ELSIF p_column= 'SOURCE_LINE' THEN
687 RETURN l_source_line ;
688 ELSIF p_column= 'SOURCE_TRANSACTION' THEN
689 RETURN l_source_transaction ;
690 END IF;
691
692 END get_contract_source_details;
693
694 /*
695 Added by SKUCHIMA for bug#14576343
696 PARAMETERS
697 p_cle_id - NUMBER - Contract line Id
698 RETURN VALUE - NUMBER
699 Following function is used as part of VRM project to retrieve the quantity for subscription line.
700 */
701
702 function get_subs_qty
703 ( p_cle_id IN NUMBER
704 ) RETURN NUMBER IS
705
706 L_return_status VARCHAR2(50);
707 l_qpprod_quantity NUMBER;
708 l_qpprod_uom_code VARCHAR2(40);
709 Begin
710 oks_subscription_pub.get_subs_qty
711 (p_cle_id => p_cle_id,
712 x_return_status => L_return_status,
713 x_quantity => l_qpprod_quantity,
714 x_uom_code => l_qpprod_uom_code
715 );
716
717 RETURN Nvl(l_qpprod_quantity,0);
718
719 Exception
720 When Others Then
721 NULL;
722 End get_subs_qty;
723
724 /*
725 Added by SKUCHIMA for bug#14576343
726 PARAMETERS
727 p_cle_id - NUMBER - Contract line Id
728 RETURN VALUE - VARCHAR2
729 Following function is used as part of VRM project to retrieve the UOM code for subscription line.
730 */
731
732 function get_subs_uom
733 ( p_cle_id IN NUMBER
734 ) RETURN varchar2 IS
735
736 L_return_status VARCHAR2(50);
737 l_qpprod_quantity NUMBER;
738 l_qpprod_uom_code VARCHAR2(40);
739 Begin
740 oks_subscription_pub.get_subs_qty
741 (p_cle_id => p_cle_id,
742 x_return_status => L_return_status,
743 x_quantity => l_qpprod_quantity,
744 x_uom_code => l_qpprod_uom_code
745 );
746
747 RETURN l_qpprod_uom_code;
748
749 Exception
750 When Others Then
751 NULL;
752 End get_subs_uom;
753
754
755 END oks_code_hook;
[Home] [Help]