[Home] [Help]
PACKAGE BODY: APPS.OKS_ENT_UTIL_PVT
Source
1 PACKAGE BODY OKS_ENT_UTIL_PVT AS
2 /* $Header: OKSREUTB.pls 120.0 2005/05/25 17:56:14 appldev noship $ */
3
4 FUNCTION get_duration_period(p_start_date IN Date, p_end_date IN Date, inp_type Varchar2) RETURN Varchar2
5 IS
6 x_duration Number;
7 x_timeunit Varchar2(25);
8 x_return_status Varchar2(1);
9 BEGIN
10
11 OKC_TIME_UTIL_PUB.get_duration(p_start_date => p_start_date,
12 p_end_date => p_end_date,
13 x_duration => x_duration,
14 x_timeunit => x_timeunit,
15 x_return_status => x_return_status);
16 IF inp_type = 'D'
17 THEN
18 return(x_duration);
19 ELSIF inp_type = 'P'
20 THEN
21 return(x_timeunit);
22 END IF;
23 END get_duration_period;
24
25 FUNCTION get_contract_amount(p_hdr_id IN Number) RETURN Number
26 IS
27 ---Except Usage.
28 CURSOR l_amt_csr IS
29 SELECT SUM(L.price_negotiated)
30 FROM OKC_K_LINES_B L,
31 OKC_LINE_STYLES_B S
32 WHERE S.ID = L.LSE_ID
33 AND S.ID IN(7, 8, 9, 10, 11, 18, 25, 35)
34 AND L.DNZ_CHR_ID = NVL(p_hdr_id, L.DNZ_CHR_ID);
35
36 l_amt Number;
37 BEGIN
38 OPEN l_amt_csr;
39 FETCH l_amt_csr INTO l_amt;
40 CLOSE l_amt_csr;
41 RETURN(l_amt);
42 END get_contract_amount;
43
44 FUNCTION get_party(p_hdr_id IN Number) RETURN Varchar2
45 IS
46 CURSOR l_party_csr IS
47 SELECT P.OBJECT1_ID1
48 FROM OKC_K_HEADERS_B H
49 ,OKC_K_PARTY_ROLES_B P
50 WHERE H.ID = P.DNZ_CHR_ID
51 AND P.JTOT_OBJECT1_CODE = 'OKX_PARTY'
52 AND H.ID = p_hdr_id;
53
54 l_party_id Varchar2(40);
55
56 BEGIN
57 OPEN l_party_csr;
58 FETCH l_party_csr INTO l_party_id;
59 CLOSE l_party_csr;
60
61 RETURN(l_party_id);
62 END get_party;
63
64 FUNCTION get_billtoshipto(p_hdr_id IN Number --DEFAULT NULL
65 ,p_line_id IN Number --DEFAULT NULL
66 ,p_inp_type IN Varchar2) RETURN Varchar2
67 IS
68
69 /*
70 CURSOR l_bth_csr IS
71 SELECT RL.OBJECT1_ID1
72 FROM OKC_RULE_GROUPS_B RG,
73 OKC_RULES_B RL
74 WHERE RG.dnz_CHR_ID = p_hdr_id
75 AND RG.cle_id Is Null
76 AND RG.ID = RL.RGP_ID
77 AND RL.JTOT_OBJECT1_CODE = p_inp_type;
78
79 CURSOR l_btl_csr IS
80 SELECT RL.OBJECT1_ID1
81 FROM OKC_RULE_GROUPS_B RG,
82 OKC_RULES_B RL
83 WHERE RG.CLE_ID = p_line_id
84 AND RG.ID = RL.RGP_ID
85 AND RL.JTOT_OBJECT1_CODE = p_inp_type;
86 */
87
88 CURSOR l_bth_csr IS
89 SELECT CHR.BILL_TO_SITE_USE_ID,CHR.SHIP_TO_SITE_USE_ID
90 FROM OKC_K_HEADERS_B CHR
91 WHERE CHR.ID = p_hdr_id;
92
93 CURSOR l_btl_csr IS
94 SELECT CLE.BILL_TO_SITE_USE_ID,CLE.SHIP_TO_SITE_USE_ID
95 FROM OKC_K_LINES_B CLE
96 WHERE CLE.ID = p_line_id;
97
98
99 l_bt_id number ;
100 l_st_id number ;
101
102 BEGIN
103
104 l_bt_id := Null;
105 l_st_id := null;
106
107 IF p_line_id IS NOT NULL
108 THEN
109 OPEN l_btl_csr;
110 FETCH l_btl_csr INTO l_bt_id,l_st_id;
111 CLOSE l_btl_csr;
112 ELSIF p_hdr_id IS NOT NULL
113 THEN
114 OPEN l_bth_csr;
115 FETCH l_bth_csr INTO l_bt_id,l_st_id;
116 CLOSE l_bth_csr;
117 END IF;
118 IF p_inp_type = 'OKX_BILLTO' then
119 RETURN l_bt_id;
120 elsif p_inp_type = 'OKX_SHIPTO' then
121 RETURN l_st_id;
122 end if;
123 END get_billtoshipto;
124
125 FUNCTION get_pricelist(p_hdr_id IN Number --DEFAULT NULL
126 ,p_line_id IN Number --DEFAULT NULL
127 ,p_inp_type IN Varchar2) RETURN Varchar2
128 IS
129 /*
130 CURSOR l_plh_csr IS
131 SELECT RL.OBJECT1_ID1, PL.CURRENCY_CODE
132 FROM
133 OKC_RULE_GROUPS_B RG,
134 OKC_RULES_B RL,
135 OKX_LIST_HEADERS_V PL
136 WHERE
137 RG.dnz_CHR_ID = p_hdr_id
138 AND RG.cle_id Is Null
139 AND RG.ID = RL.RGP_ID
140 AND RL.OBJECT1_ID1 = PL.ID1
141 AND RL.OBJECT1_ID2 = PL.ID2
142 AND RL.JTOT_OBJECT1_CODE = 'OKX_PRICE'
143 AND PL.LIST_TYPE_CODE = 'PRL';
144
145 CURSOR l_pll_csr IS
146 SELECT PL.ID1, PL.CURRENCY_CODE
147 FROM
148 OKC_RULE_GROUPS_B RG,
149 OKC_RULES_B RL,
150 OKX_LIST_HEADERS_V PL
151 WHERE
152 RG.CLE_ID = p_line_id
153 AND RG.ID = RL.RGP_ID
154 AND RL.OBJECT1_ID1 = PL.ID1
155 AND RL.OBJECT1_ID2 = PL.ID2
156 AND RL.JTOT_OBJECT1_CODE = 'OKX_PRICE'
157 AND PL.LIST_TYPE_CODE = 'PRL';
158 */
159
160 CURSOR l_plh_csr IS
161 SELECT CHR.PRICE_LIST_ID,CHR.CURRENCY_CODE
162 FROM OKC_K_HEADERS_B CHR
163 WHERE CHR.ID = p_hdr_id;
164
165 CURSOR l_pll_csr IS
166 SELECT CLE.PRICE_LIST_ID,CLE.CURRENCY_CODE
167 FROM OKC_K_LINES_B CLE
168 WHERE CLE.ID = p_line_id;
169
170 l_pl_id Varchar2(40);
171 l_curr_code Varchar2(15);
172
173 BEGIN
174
175 l_pl_id := Null;
176 l_curr_code := Null;
177
178 IF p_hdr_id IS NOT NULL
179 THEN
180 OPEN l_plh_csr;
181 FETCH l_plh_csr INTO l_pl_id, l_curr_code;
182 CLOSE l_plh_csr;
183 ELSIF p_line_id IS NOT NULL
184 THEN
185 OPEN l_pll_csr;
186 FETCH l_pll_csr INTO l_pl_id, l_curr_code;
187 CLOSE l_pll_csr;
188 END IF;
189
190 IF p_inp_type = 'P'
191 THEN
192 return(l_pl_id);
193 ELSIF p_inp_type = 'C'
194 THEN
195 return(l_curr_code);
196 END IF;
197 END get_pricelist;
198
199 FUNCTION get_discount(p_hdr_id IN Number --DEFAULT NULL
200 ,p_line_id IN Number --DEFAULT NULL
201 ) RETURN Varchar2
202 IS
203 /*
204 CURSOR l_dish_csr IS
205 SELECT DI.ID1
206 FROM
207 OKC_RULE_GROUPS_B RG,
208 OKC_RULES_B RL,
209 OKX_LIST_HEADERS_V DI
210 WHERE
211 RG.dnz_CHR_ID = p_hdr_id
212 AND RG.cle_id Is Null
213 AND RG.ID = RL.RGP_ID
214 AND RL.OBJECT1_ID1 = DI.ID1
215 AND RL.OBJECT1_ID2 = DI.ID2
216 AND RL.JTOT_OBJECT1_CODE = 'OKX_DISCOUNT'
217 AND DI.LIST_TYPE_CODE = 'DTL';
218
219 CURSOR l_disl_csr IS
220 SELECT DI.ID1
221 FROM
222 OKC_RULE_GROUPS_B RG,
223 OKC_RULES_B RL,
224 OKX_LIST_HEADERS_V DI
225 WHERE
226 RG.CLE_ID = p_line_id
227 AND RG.ID = RL.RGP_ID
228 AND RL.OBJECT1_ID1 = DI.ID1
229 AND RL.OBJECT1_ID2 = DI.ID2
230 AND RL.JTOT_OBJECT1_CODE = 'OKX_DISCOUNT'
231 AND DI.LIST_TYPE_CODE = 'DTL';
232 */
233
234
235 CURSOR l_disl_csr IS
236 SELECT OKSCLE.DISCOUNT_LIST
237 FROM OKS_K_LINES_B OKSCLE
238 WHERE OKSCLE.CLE_ID = p_line_id;
239
240
241 l_dis_id Varchar2(40); -- := Null;
242
243 BEGIN
244
245 l_dis_id := Null;
246
247 IF p_hdr_id IS NOT NULL
248 THEN
249 /*
250 OPEN l_dish_csr;
251 FETCH l_dish_csr INTO l_dis_id;
252 CLOSE l_dish_csr;
253 */
254 l_dis_id := '';
255 return(l_dis_id);
256 ELSIF p_line_id IS NOT NULL
257 THEN
258 OPEN l_disl_csr;
259 FETCH l_disl_csr INTO l_dis_id;
260 CLOSE l_disl_csr;
261 return(l_dis_id);
262 END IF;
263 END get_discount;
264
265 FUNCTION get_acc_rule(p_hdr_id IN Number
266 ,p_line_id IN Number) RETURN Varchar2
267 IS
268 /*
269 CURSOR l_acch_csr IS
270 SELECT RLS.OBJECT1_ID1
271 FROM OKC_RULE_GROUPS_B RGP,
272 OKC_RULES_B RLS
273 WHERE RGP.dnz_CHR_ID = p_hdr_id
274 AND RGP.cle_id Is Null
275 AND RGP.ID = RLS.RGP_ID
276 AND RLS.JTOT_OBJECT1_CODE = 'OKX_ACCTRULE';
277
278 CURSOR l_accl_csr IS
279 SELECT RLS.OBJECT1_ID1
280 FROM OKC_RULE_GROUPS_B RGP,
281 OKC_RULES_B RLS
282 WHERE RGP.CLE_ID = p_line_id
283 AND RGP.ID = RLS.RGP_ID
284 AND RLS.JTOT_OBJECT1_CODE = 'OKX_ACCTRULE';
285 */
286 CURSOR l_acch_csr IS
287 SELECT OKSCHR.ACCT_RULE_ID
288 FROM OKS_K_HEADERS_B OKSCHR
289 WHERE OKSCHR.CHR_ID = p_hdr_id;
290
291 CURSOR l_accl_csr IS
292 SELECT OKSCLE.ACCT_RULE_ID
293 FROM OKS_K_LINES_B OKSCLE
294 WHERE OKSCLE.CLE_ID = p_line_id;
295
296 l_acc_id Varchar2(40);
297
298 BEGIN
299
300 l_acc_id := Null;
301
302 IF p_hdr_id IS NOT NULL
303 THEN
304 OPEN l_acch_csr;
305 FETCH l_acch_csr INTO l_acc_id;
306 CLOSE l_acch_csr;
307 ELSIF p_line_id IS NOT NULL
308 THEN
309 OPEN l_accl_csr;
310 FETCH l_accl_csr INTO l_acc_id;
311 CLOSE l_accl_csr;
312 END IF;
313
314 return(l_acc_id);
315
316 END get_acc_rule;
317
318 FUNCTION get_inv_rule(p_hdr_id IN Number
319 ,p_line_id IN Number) RETURN Varchar2
320 IS
321 /*
322 CURSOR l_invh_csr IS
323 SELECT RLS.OBJECT1_ID1
324 FROM OKC_RULE_GROUPS_B RGP,
325 OKC_RULES_B RLS
326 WHERE RGP.dnz_CHR_ID = p_hdr_id
327 AND RGP.cle_id Is Null
328 AND RGP.ID = RLS.RGP_ID
329 AND RLS.JTOT_OBJECT1_CODE = 'OKX_INVRULE';
330
331 CURSOR l_invl_csr IS
332 SELECT RLS.OBJECT1_ID1
333 FROM OKC_RULE_GROUPS_B RGP,
334 OKC_RULES_B RLS
335 WHERE RGP.CLE_ID = p_line_id
336 AND RGP.ID = RLS.RGP_ID
337 AND RLS.JTOT_OBJECT1_CODE = 'OKX_INVRULE';
338 */
339
340 CURSOR l_invh_csr IS
341 SELECT CHR.INV_RULE_ID
342 FROM OKC_K_HEADERS_B CHR
343 WHERE CHR.ID = p_hdr_id;
344
345 CURSOR l_invl_csr IS
346 SELECT CLE.INV_RULE_ID
347 FROM OKC_K_LINES_B CLE
348 WHERE CLE.ID = p_line_id;
349
350 l_inv_id Varchar2(40);
351
352 BEGIN
353
354 l_inv_id := Null;
355
356 IF p_hdr_id IS NOT NULL
357 THEN
358 OPEN l_invh_csr;
359 FETCH l_invh_csr INTO l_inv_id;
360 CLOSE l_invh_csr;
361 ELSIF p_line_id IS NOT NULL
362 THEN
363 OPEN l_invl_csr;
364 FETCH l_invl_csr INTO l_inv_id;
365 CLOSE l_invl_csr;
366 END IF;
367
368 return(l_inv_id);
369
370 END get_inv_rule;
371
372 FUNCTION get_billingprofile(p_hdr_id IN Number
373 ,p_line_id IN Number) RETURN Varchar2
374 IS
375 /*
376 CURSOR l_bph_csr IS
377 SELECT RL.RULE_INFORMATION1
378 FROM OKC_RULE_GROUPS_B RG,
379 OKC_RULES_B RL
380 WHERE RG.dnz_ChR_ID = p_hdr_id
381 AND RG.cle_id Is Null
382 AND RG.ID = RL.RGP_ID
383 AND RL.RULE_INFORMATION_CATEGORY = 'BPF';
384
385 CURSOR l_bpl_csr IS
386 SELECT RL.RULE_INFORMATION1
387 FROM OKC_RULE_GROUPS_B RG,
388 OKC_RULES_B RL
389 WHERE RG.Cle_id = p_line_id
390 AND RG.ID = RL.RGP_ID
391 AND RL.RULE_INFORMATION_CATEGORY = 'BPF';
392 */
393 CURSOR l_bph_csr IS
394 SELECT OKSCHR.BILLING_PROFILE_ID
395 FROM OKS_K_HEADERS_B OKSCHR
396 WHERE OKSCHR.CHR_ID = p_hdr_id;
397
398 l_bp_id Varchar2(40);
399
400 BEGIN
401
402 l_bp_id := Null;
403
404 IF p_hdr_id IS NOT NULL
405 THEN
406 OPEN l_bph_csr;
407 FETCH l_bph_csr INTO l_bp_id;
408 CLOSE l_bph_csr;
409
410 ELSIF p_line_id IS NOT NULL
411 THEN
412 /*
413 OPEN l_bpl_csr;
414 FETCH l_bpl_csr INTO l_bp_id;
415 CLOSE l_bpl_csr;
416 */
417 l_bp_id := '';
418 END IF;
419
420 return(l_bp_id);
421
422 END get_billingprofile;
423
424 FUNCTION get_billingschedule(p_hdr_id IN Number
425 ,p_line_id IN Number
426 ,p_inp_type IN Varchar2) RETURN Varchar2
427 IS
428 /*
429 CURSOR l_bsh_csr IS
430 SELECT SUBSTR(RL.RULE_INFORMATION2,1,3) BF,
431 DECODE(RL.RULE_INFORMATION5, 0, 'ADV', 'ARR') BM,
432 TO_NUMBER(RL.RULE_INFORMATION5) ROD,
433 RL.RULE_INFORMATION3 FBT,
434 RL.RULE_INFORMATION4 FBO
435 FROM OKC_RULE_GROUPS_B RG,
436 OKC_RULES_B RL
437 WHERE RG.dnz_CHR_ID = p_hdr_id
438 AND RG.cle_id Is Null
439 AND RG.ID = RL.RGP_ID
440 AND RL.RULE_INFORMATION_CATEGORY = 'SBG';
441
442 CURSOR l_bsl_csr IS
443 SELECT SUBSTR(RL.RULE_INFORMATION2,1,3) BF,
444 DECODE(RL.RULE_INFORMATION5, 0, 'ADV', 'ARR') BM,
445 TO_NUMBER(RL.RULE_INFORMATION5) ROD,
446 RL.RULE_INFORMATION3 FBT,
447 RL.RULE_INFORMATION4 FBO
448 FROM OKC_RULE_GROUPS_B RG,
449 OKC_RULES_B RL
450 WHERE RG.Cle_ID = p_line_id
451 AND RG.ID = RL.RGP_ID
452 AND RL.RULE_INFORMATION_CATEGORY = 'SBG';
453 */
454
455 l_bfrq Varchar2(40);
456 l_bmth Varchar2(3);
457 l_brod Number;
458 l_bfbt Varchar2(450);
459 l_bfbo Varchar2(450);
460
461
462 BEGIN
463
464 l_bfrq := Null;
465 l_bmth := Null;
466 l_brod := Null;
467 l_bfbt := Null;
468 l_bfbo := Null;
469
470 -- this information is not valid anymore..this private function
471 -- should not be used for getting billing schedule information.
472 /*
473 IF p_hdr_id IS NOT NULL
474 THEN
475 OPEN l_bsh_csr;
476 FETCH l_bsh_csr INTO l_bfrq, l_bmth, l_brod, l_bfbt, l_bfbo;
477 CLOSE l_bsh_csr;
478 ELSIF p_line_id IS NOT NULL
479 THEN
480 OPEN l_bsl_csr;
481 FETCH l_bsl_csr INTO l_bfrq, l_bmth, l_brod, l_bfbt, l_bfbo;
482 CLOSE l_bsl_csr;
483 END IF;
484 */
485
486 IF p_inp_type = 'F'
487 THEN
488 return(l_bfrq);
489 ELSIF p_inp_type = 'M'
490 THEN
491 return(l_bmth);
492 ELSIF p_inp_type = 'R'
493 THEN
494 return(l_brod);
495 ELSIF p_inp_type = 'T'
496 THEN
497 return(l_bfbt);
498 ELSIF p_inp_type = 'O'
499 THEN
500 return(l_bfbo);
501 END IF;
502
503 END get_billingschedule;
504
505 FUNCTION get_renternotes(p_hdr_id IN Number
506 ,p_inp_type IN Varchar2) RETURN CLOB
507 IS
508 /*
509 CURSOR l_notes_csr IS
510 SELECT REN.TEXT
511 FROM OKC_K_ARTICLES_B KAR,
512 OKC_K_ARTICLES_TL REN,
513 FND_LOOKUPS SBR
514 WHERE KAR.ID = REN.ID
515 AND REN.SOURCE_LANG = USERENV('LANG')
516 AND KAR.CHR_ID = p_hdr_id
517 AND KAR.CAT_TYPE = 'NSD'
518 AND SBR.LOOKUP_CODE = KAR.SBT_CODE
519 AND SBR.LOOKUP_TYPE = 'OKC_OPERATION'
520 AND SBR.LOOKUP_CODE = p_inp_type;
521 */
522 l_notes CLOB;
523
524 BEGIN
525 -- due to a major change in articles and Terms and conditions datamodel...
526 -- this private function is not valid anymore.
527 /*
528 OPEN l_notes_csr;
529 FETCH l_notes_csr into l_notes;
530 CLOSE l_notes_csr;
531 */
532 return(l_notes);
533
534 END get_renternotes;
535
536 FUNCTION get_terms(p_hdr_id IN Number
537 ,p_line_id IN Number) RETURN Varchar2
538 IS
539 /*
540 CURSOR l_termsh_csr IS
541 SELECT RLS.OBJECT1_ID1
542 FROM OKC_RULE_GROUPS_B RGP,
543 OKC_RULES_B RLS
544 WHERE RGP.dnz_CHR_ID = p_hdr_id
545 AND RGP.cle_id Is Null
546 AND RGP.ID = RLS.RGP_ID
547 AND RLS.JTOT_OBJECT1_CODE = 'OKX_RPAYTERM';
548
549 CURSOR l_termsl_csr IS
550 SELECT RLS.OBJECT1_ID1
551 FROM OKC_RULE_GROUPS_B RGP,
552 OKC_RULES_B RLS
553 WHERE RGP.Cle_ID = p_line_id
554 AND RGP.ID = RLS.RGP_ID
555 AND RLS.JTOT_OBJECT1_CODE = 'OKX_RPAYTERM';
556 */
557
558 CURSOR l_termsh_csr IS
559 SELECT CHR.PAYMENT_TERM_ID
560 FROM OKC_K_HEADERS_B CHR
561 WHERE CHR.ID = p_hdr_id;
562
563 CURSOR l_termsl_csr IS
564 SELECT CLE.PAYMENT_TERM_ID
565 FROM OKC_K_LINES_B CLE
566 WHERE CLE.ID = p_line_id;
567
568 l_terms_id Varchar2(40);
569
570 BEGIN
571
572 l_terms_id := Null;
573
574 IF p_hdr_id IS NOT NULL
575 THEN
576 OPEN l_termsh_csr;
577 FETCH l_termsh_csr INTO l_terms_id;
578 CLOSE l_termsh_csr;
579 ELSIF p_line_id IS NOT NULL
580 THEN
581 OPEN l_termsl_csr;
582 FETCH l_termsl_csr INTO l_terms_id;
583 CLOSE l_termsl_csr;
584 END IF;
585
586 return(l_terms_id);
587 END get_terms;
588
589 FUNCTION get_product(p_line_id IN Number) RETURN l_pdt_rec
590 IS
591
592 CURSOR l_pdt_csr IS
593 SELECT PDT.ID1,
594 PDT.QUANTITY
595 FROM OKC_K_LINES_B LIN,
596 OKC_K_ITEMS ITM,
597 OKX_CUSTOMER_PRODUCTS_V PDT
598 WHERE LIN.ID = ITM.CLE_ID
599 AND ITM.OBJECT1_ID1 = PDT.ID1
600 AND ITM.OBJECT1_ID2 = PDT.ID2
601 AND ITM.JTOT_OBJECT1_CODE = 'OKX_CUSTPROD'
602 AND LIN.ID = p_line_id;
603
604 l_rec_type l_pdt_rec;
605
606 BEGIN
607
608 OPEN l_pdt_csr;
609 FETCH l_pdt_csr INTO l_rec_type;
610 CLOSE l_pdt_csr;
611
612 RETURN(l_rec_type);
613
614 END get_product;
615
616 FUNCTION get_product(p_line_id IN Number, p_inp_type IN Varchar2) RETURN Varchar2
617 IS
618
619 CURSOR l_pdt_csr IS
620 /*
621 SELECT PDT.ID1,
622 PDT.CUSTOMER_ID,
623 CUS.NAME,
624 PDT.INSTALL_SITE_USE_ID,
625 SIT.NAME PARTY_SITE_NAME
626 FROM OKC_K_LINES_B LIN,
627 OKC_K_ITEMS ITM,
628 OKX_CUSTOMER_PRODUCTS_V PDT,
629 OKX_CUSTOMER_ACCOUNTS_V CUS,
630 OKX_PARTIES_V PTY,
631 OKX_PARTY_SITES_V SIT --OKX_PARTY_SITE_USES_V SIT
632 WHERE LIN.ID = ITM.CLE_ID
633 AND ITM.OBJECT1_ID1 = PDT.ID1
634 AND ITM.OBJECT1_ID2 = PDT.ID2
635 AND PDT.CUSTOMER_ID = CUS.ID1
636 AND PTY.ID1 = CUS.PARTY_ID
637 AND PDT.INSTALL_SITE_USE_ID = SIT.PARTY_SITE_ID
638 AND LIN.ID = p_line_id;
639 */
640 SELECT PDT.instance_id ID1, --PDT.ID1,
641 PDT.owner_party_account_id customer_id, --PDT.CUSTOMER_ID,
642 CUS.NAME cusname,
643 PDT.install_location_id install_location_id, --PDT.INSTALL_SITE_USE_ID,
644 SIT.NAME PARTY_SITE_NAME
645 FROM OKC_K_LINES_B LIN,
646 OKC_K_ITEMS ITM,
647 CSI_ITEM_INSTANCES PDT, --OKX_CUSTOMER_PRODUCTS_V PDT,
648 OKX_CUSTOMER_ACCOUNTS_V CUS,
649 OKX_PARTIES_V PTY,
650 OKX_PARTY_SITES_V SIT --OKX_PARTY_SITE_USES_V
651 WHERE LIN.ID = ITM.CLE_ID
652 AND ITM.OBJECT1_ID1 = PDT.Inventory_item_id --PDT.ID1
653 AND ITM.OBJECT1_ID2 = PDT.Inv_Master_Organization_Id --PDT.ID2
654 and PDT.owner_party_account_id = CUS.ID1 -- AND PDT.CUSTOMER_ID = CUS.ID1
655 AND PTY.ID1 = CUS.PARTY_ID
656 AND PDT.install_location_id = SIT.ID1 --PDT.INSTALL_SITE_USE_ID = SIT.PARTY_SITE_ID
657 AND LIN.ID = p_line_id;
658
659 l_pdt_id Varchar2(40);
660 l_cust_id Varchar2(40);
661 l_cust_name Varchar2(255);
662 l_site_id Varchar2(40);
663 l_site_name Varchar2(40);
664 BEGIN
665
666 OPEN l_pdt_csr;
667 FETCH l_pdt_csr INTO l_pdt_id, l_cust_id, l_cust_name, l_site_id, l_site_name;
668 CLOSE l_pdt_csr;
669
670 IF p_inp_type = 'PRDT'
671 THEN
672 RETURN(l_pdt_id);
673 ELSIF p_inp_type = 'CUST'
674 THEN
675 RETURN(l_cust_id);
676 ELSIF p_inp_type = 'NAME'
677 THEN
678 RETURN(l_cust_name);
679 ELSIF p_inp_type = 'SITE'
680 THEN
681 RETURN(l_site_id);
682 ELSIF p_inp_type = 'LOC'
683 THEN
684 RETURN(l_site_name);
685 ELSE
686 RETURN(Null);
687 END IF;
688 END get_product;
689
690 FUNCTION get_system(p_line_id IN Number, p_org_id IN Number) RETURN l_sys_rec
691 IS
692
693 CURSOR l_sys_csr IS
694 SELECT SY.ID1,
695 SY.NAME
696 FROM OKC_K_HEADERS_B HD,
697 OKC_K_LINES_B KL,
698 OKC_K_ITEMS IT,
699 OKX_SYSTEMS_V SY
700 WHERE HD.ID = KL.DNZ_CHR_ID
701 AND KL.ID = IT.CLE_ID
702 AND IT.OBJECT1_ID1 = SY.ID1
703 AND IT.OBJECT1_ID2 = SY.ID2
704 AND IT.JTOT_OBJECT1_CODE = 'OKX_COVSYST'
705 AND KL.ID = p_line_id
706 AND HD.AUTHORING_ORG_ID = p_org_id;
707
708 l_rec_type l_sys_rec;
709
710 BEGIN
711
712 OPEN l_sys_csr;
713 FETCH l_sys_csr INTO l_rec_type;
714 CLOSE l_sys_csr;
715
716 RETURN(l_rec_type);
717 END get_system;
718
719 FUNCTION get_system(p_line_id IN Number, p_inp_type IN Varchar2, p_org_id IN Number) RETURN Varchar2
720 IS
721
722 -- commented as part of fix for bug 3597850
723 /*
724 CURSOR l_sys_csr IS
725 SELECT SY.ID1,
726 SY.NAME
727 FROM OKC_K_HEADERS_B HD,
728 OKC_K_LINES_B KL,
729 OKC_K_ITEMS IT,
730 -- OKC_RULE_GROUPS_B RG,
731 -- OKC_RULES_B RL,
732 OKX_SYSTEMS_V SY
733 WHERE HD.ID = KL.DNZ_CHR_ID
734 -- AND KL.ID = RG.CLE_ID
735 -- AND RG.ID = RL.RGP_ID
736 AND IT.OBJECT1_ID1 = SY.ID1
737 AND IT.OBJECT1_ID2 = SY.ID2
738 and IT.jtot_object1_code = 'OKX_COVSYS'
739 AND HD.AUTHORING_ORG_ID = p_org_id;
740 */
741
742 l_sys_id Varchar2(40);
743 l_sys_name Varchar2(50);
744
745 BEGIN
746
747 l_sys_id := '-99';
748 l_sys_name := ' ';
749
750 -- commented as part of fix for bug 3597850
751 -- this function not used anywhere, so stubbed out.
752 /*
753 OPEN l_sys_csr;
754 FETCH l_sys_csr INTO l_sys_id, l_sys_name;
755 CLOSE l_sys_csr;
756 */
757 IF p_inp_type = 'SYSID'
758 THEN
759 RETURN(l_sys_id);
760 ELSIF p_inp_type = 'SYSNAME'
761 THEN
762 RETURN(l_sys_name);
763 ELSE
764 RETURN(Null);
765 END IF;
766
767 END get_system;
768
769 FUNCTION get_invitem(p_line_id IN Number, p_organization_id IN Number) RETURN l_inv_rec
770 IS
771
772 CURSOR l_inv_csr IS
773 SELECT V.ID1,
774 V.DESCRIPTION
775 FROM OKC_K_LINES_B L,
776 OKC_K_ITEMS I,
777 OKX_SYSTEM_ITEMS_V V,
778 OKC_K_HEADERS_B H
779 WHERE L.ID = I.CLE_ID
780 AND I.OBJECT1_ID1 = V.ID1
781 AND I.OBJECT1_ID2 = V.ID2
782 AND I.JTOT_OBJECT1_CODE = 'OKX_COVITEM'
783 AND L.ID = p_line_id
784 AND H.ID = L.DNZ_CHR_ID
785 AND H.INV_ORGANIZATION_ID = p_organization_id;
786
787 l_rec_type l_inv_rec;
788
789 BEGIN
790
791 OPEN l_inv_csr;
792 FETCH l_inv_csr INTO l_rec_type;
793 CLOSE l_inv_csr;
794
795 RETURN(l_rec_type);
796
797 END get_invitem;
798
799 FUNCTION get_invitem(p_line_id IN Number, p_inp_type Varchar2, p_organization_id IN Number) RETURN Varchar2
800 IS
801
802 CURSOR l_inv_csr IS
803 SELECT V.ID1,
804 V.DESCRIPTION
805 FROM OKC_K_LINES_B L,
806 OKC_K_ITEMS I,
807 OKX_SYSTEM_ITEMS_V V,
808 OKC_K_HEADERS_B H
809 WHERE L.ID = I.CLE_ID
810 AND I.OBJECT1_ID1 = V.ID1
811 AND I.OBJECT1_ID2 = V.ID2
812 AND L.ID = p_line_id
813 AND H.ID = L.DNZ_CHR_ID
814 AND H.inv_organization_id = p_organization_id;
815
816 l_item_id Varchar2(40);
817 l_item_name Varchar2(240);
818
819 BEGIN
820
821 OPEN l_inv_csr;
822 FETCH l_inv_csr INTO l_item_id, l_item_name;
823 CLOSE l_inv_csr;
824
825 IF p_inp_type = 'ITEMID'
826 THEN
827 RETURN(l_item_id);
828 ELSIF p_inp_type = 'ITEMNAME'
829 THEN
830 RETURN(l_item_name);
831 ELSE
832 RETURN(Null);
833 END IF;
834 END get_invitem;
835
836 FUNCTION get_qtyrate_rule(p_line_id IN Number) RETURN l_qtyrate_rec
837 IS
838 /*
839 CURSOR l_qrr_csr IS
840 SELECT RQR.RULE_INFORMATION6 DEFAULT_AMCV_FLAG
841 ,TO_NUMBER(RQR.RULE_INFORMATION5) DEFAULT_QTY
842 ,RQR.RULE_INFORMATION11 DEFAULT_UOM
843 ,TO_NUMBER(RQR.RULE_INFORMATION8) DEFAULT_DURATION
844 ,RQR.RULE_INFORMATION2 DEFAULT_PERIOD
845 ,TO_NUMBER(RQR.RULE_INFORMATION4) MINIMUM_QTY
846 ,RQR.RULE_INFORMATION11 MINIMUM_UOM
847 ,TO_NUMBER(RQR.RULE_INFORMATION8) MINIMUM_DURATION
848 ,RQR.RULE_INFORMATION2 MINIMUM_PERIOD
849 ,TO_NUMBER(RQR.RULE_INFORMATION7) FIXED_QTY
850 ,RQR.RULE_INFORMATION11 FIXED_UOM
851 ,TO_NUMBER(RQR.RULE_INFORMATION8) FIXED_DURATION
852 ,RQR.RULE_INFORMATION2 FIXED_PERIOD
853 ,RQR.RULE_INFORMATION9 LEVEL_FLAG
854 FROM OKC_K_LINES_B LIN,
855 OKC_RULE_GROUPS_B RGP,
856 OKC_RULES_B RQR
857 WHERE LIN.ID = RGP.CLE_ID
858 AND RGP.ID = RQR.RGP_ID
859 AND RQR.RULE_INFORMATION_CATEGORY = 'QRE'
860 AND LIN.ID = p_line_id;
861 */
862
863 l_rec_type l_qtyrate_rec;
864 BEGIN
865 -- not valid anymore
866
867 /*
868 OPEN l_qrr_csr;
869 FETCH l_qrr_csr INTO l_rec_type;
870 CLOSE l_qrr_csr;
871 */
872
873 RETURN(l_rec_type);
874
875 END get_qtyrate_rule;
876
877 FUNCTION get_taxrule(p_hdr_id IN Number, p_inp_type IN Varchar2) RETURN Varchar2
878 IS
879
880 /*
881 CURSOR l_tax_csr IS
882 SELECT object1_id1 TAX_EXEMPTION, object2_id1 TAX_STATUS
883 FROM OKC_K_HEADERS_B HD,
884 OKC_RULE_GROUPS_B RG,
885 OKC_RULES_B RL
886 WHERE HD.ID = RG.DNZ_CHR_ID
887 AND RG.ID = RL.RGP_ID
888 AND RL.RULE_INFORMATION_CATEGORY = 'TAX'
889 AND HD.ID = p_hdr_id;
890 */
891
892 CURSOR l_tax_csr IS
893 SELECT to_char(OKSCHR.TAX_EXEMPTION_ID),OKSCHR.TAX_STATUS
894 FROM OKS_K_HEADERS_B OKSCHR
895 WHERE OKSCHR.CHR_ID = p_hdr_id;
896
897 l_object1_id1 varchar2(40); --OKC_RULES_B.OBJECT1_ID1%TYPE;
898 l_object2_id1 varchar2(200); --OKC_RULES_B.OBJECT1_ID2%TYPE;
899
900 BEGIN
901 IF p_inp_type = 'TE' THEN
902 OPEN l_tax_csr;
903 FETCH l_tax_csr INTO l_object1_id1, l_object2_id1;
904 CLOSE l_tax_csr;
905
906 RETURN(l_object1_id1);
907 ELSIF p_inp_type = 'TS' THEN
908 OPEN l_tax_csr;
909 FETCH l_tax_csr INTO l_object1_id1, l_object2_id1;
910 CLOSE l_tax_csr;
911
912 RETURN(l_object2_id1);
913 END IF;
914
915 END get_taxrule;
916
917 FUNCTION get_convrule(p_hdr_id IN Number) RETURN Varchar2
918 IS
919 /*
920 CURSOR l_cvn_csr IS
921 SELECT object1_id1
922 FROM OKC_K_HEADERS_B HD,
923 OKC_RULE_GROUPS_B RG,
924 OKC_RULES_B RL
925 WHERE HD.ID = RG.DNZ_CHR_ID
926 AND RG.ID = RL.RGP_ID
927 AND RL.RULE_INFORMATION_CATEGORY = 'CVN'
928 AND RL.JTOT_OBJECT1_CODE = 'OKX_CONVTYPE'
929 AND HD.ID = p_hdr_id;
930 */
931
932 CURSOR l_cvn_csr IS
933 SELECT CHR.CONVERSION_TYPE
934 FROM OKC_K_HEADERS_B CHR
935 WHERE CHR.ID = p_hdr_id;
936
937 l_object1_id1 varchar2(40); --OKC_RULES_B.OBJECT1_ID1%TYPE;
938
939 BEGIN
940
941 OPEN l_cvn_csr;
942 FETCH l_cvn_csr INTO l_object1_id1;
943 CLOSE l_cvn_csr;
944
945 RETURN(l_object1_id1);
946
947 END get_convrule;
948
949 FUNCTION get_agreement(p_hdr_id IN Number) RETURN Number
950 IS
951 CURSOR l_agr_csr IS
952 SELECT isa_agreement_id
953 FROM OKC_K_HEADERS_B HD,
954 OKC_GOVERNANCES GV
955 WHERE HD.ID = GV.DNZ_CHR_ID
956 AND HD.ID = p_hdr_id;
957
958 l_agreement_id Number;
959
960 BEGIN
961
962 OPEN l_agr_csr;
963 FETCH l_agr_csr INTO l_agreement_id;
964 CLOSE l_agr_csr;
965
966 RETURN(l_agreement_id);
967
968 END get_agreement;
969
970 FUNCTION get_clvl_party(p_line_id IN Number) Return l_party_rec
971 IS
972 CURSOR l_pty_csr IS
973 SELECT IT.OBJECT1_ID1, PY.NAME
974 FROM OKC_K_LINES_B KL,
975 OKC_K_ITEMS IT,
976 OKX_PARTIES_V PY
977 WHERE KL.ID = IT.CLE_ID
978 AND IT.OBJECT1_ID1 = PY.ID1
979 AND IT.OBJECT1_ID2 = PY.ID2
980 AND IT.JTOT_OBJECT1_CODE = 'OKX_PARTY'
981 AND KL.ID = p_line_id;
982
983 l_rec_type l_party_rec;
984
985 BEGIN
986
987 OPEN l_pty_csr;
988 FETCH l_pty_csr INTO l_rec_type;
989 CLOSE l_pty_Csr;
990
991 RETURN(l_rec_type);
992
993 END get_clvl_party;
994
995
996 FUNCTION get_clvl_customer(p_line_id IN Number) Return l_cust_rec
997 IS
998 CURSOR l_cust_csr IS
999 SELECT IT.OBJECT1_ID1, CT.NAME
1000 FROM OKC_K_LINES_B KL,
1001 OKC_K_ITEMS IT,
1002 OKX_CUSTOMER_ACCOUNTS_V CT
1003 WHERE KL.ID = IT.CLE_ID
1004 AND IT.OBJECT1_ID1 = CT.ID1
1005 AND IT.OBJECT1_ID2 = CT.ID2
1006 AND IT.JTOT_OBJECT1_CODE = 'OKX_CUSTACCT'
1007 AND KL.ID = p_line_id;
1008
1009 l_rec_type l_cust_rec;
1010
1011 BEGIN
1012
1013 OPEN l_cust_csr;
1014 FETCH l_cust_csr INTO l_rec_type;
1015 CLOSE l_cust_Csr;
1016
1017 RETURN(l_rec_type);
1018
1019 END get_clvl_customer;
1020
1021 FUNCTION get_clvl_site(p_line_id IN Number, p_org_id IN Number) Return l_site_rec
1022 IS
1023 CURSOR l_site_csr IS
1024 SELECT IT.OBJECT1_ID1, SI.NAME LOCATION
1025 FROM OKC_K_LINES_B KL,
1026 OKC_K_ITEMS IT,
1027 OKX_CUST_SITE_USES_V SI
1028 WHERE KL.ID = IT.CLE_ID
1029 AND IT.OBJECT1_ID1 = SI.ID1
1030 AND IT.OBJECT1_ID2 = SI.ID2
1031 AND IT.JTOT_OBJECT1_CODE = 'OKX_COVSITE'
1032 AND KL.ID = p_line_id
1033 AND SI.org_id = p_org_id;
1034
1035 l_rec_type l_site_rec;
1036
1037 BEGIN
1038
1039 OPEN l_site_csr;
1040 FETCH l_site_csr INTO l_rec_type;
1041 CLOSE l_site_Csr;
1042
1043 RETURN(l_rec_type);
1044
1045 END get_clvl_site;
1046
1047 FUNCTION get_coverage_type(p_line_id IN Number) Return Varchar2
1048 IS
1049 /*
1050 CURSOR l_cov_csr IS
1051 Select fd.meaning
1052 from okc_k_lines_b kl,
1053 okc_rule_groups_b rg,
1054 okc_rules_b rl,
1055 fnd_lookups fd
1056 where kl.id = rg.cle_id
1057 and kl.lse_id in(2,15,20)
1058 and rg.id = rl.rgp_id
1059 and rl.rule_information1 = fd.lookup_code
1060 and rl.rule_information_category = 'CVE'
1061 and fd.lookup_type = 'OKSCVETYPE'
1062 and kl.id = p_line_id;
1063 */
1064
1065 CURSOR l_cov_csr IS
1066 Select covtyp.meaning
1067 from oks_k_lines_b okscle,
1068 oks_cov_types_v covtyp
1069 where okscle.coverage_type = covtyp.code
1070 and okscle.cle_id = p_line_id;
1071
1072 l_cov_name FND_LOOKUPS.MEANING%TYPE;
1073
1074 BEGIN
1075
1076 OPEN l_cov_csr;
1077 FETCH l_cov_csr INTO l_cov_name;
1078 CLOSE l_cov_csr;
1079
1080 return(l_cov_name);
1081
1082 END get_coverage_type;
1083
1084 FUNCTION get_billrate(p_rate_code IN VARCHAR2) Return Varchar2
1085 IS
1086 CURSOR l_billratemng_csr IS
1087 SELECT MEANING
1088 FROM FND_LOOKUPS
1089 WHERE LOOKUP_TYPE='OKS_BILLING_RATE'
1090 AND LOOKUP_CODE = p_rate_code;
1091
1092 l_rate_mng varchar2(30);
1093
1094 BEGIN
1095
1096 OPEN l_billratemng_csr;
1097 FETCH l_billratemng_csr INTO l_rate_mng;
1098 CLOSE l_billratemng_csr;
1099
1100 RETURN(l_rate_mng);
1101
1102 END get_billrate;
1103
1104 END OKS_ENT_UTIL_PVT;