[Home] [Help]
PACKAGE BODY: APPS.OKS_UTIL_PUB
Source
1 Package Body OKS_UTIL_PUB AS
2 /* $Header: OKSUTPRB.pls 120.6.12010000.2 2009/07/06 12:19:24 cgopinee ship $ */
3
4 PROCEDURE GET_VALUESET_ID(P_FLEXFIELD_NAME IN VARCHAR2,
5 P_CONTEXT IN VARCHAR2 ,
6 P_SEG IN VARCHAR2 ,
7 X_VSID OUT NOCOPY NUMBER,
8 X_FORMAT_TYPE OUT NOCOPY VARCHAR2,
9 X_VALIDATION_TYPE OUT NOCOPY VARCHAR2)IS
10 FLEXFIELD FND_DFLEX.DFLEX_R;
11 FLEXINFO FND_DFLEX.DFLEX_DR;
12 TEST_REC FND_VSET.VALUESET_R;
13 X_VALUESETID NUMBER;
14 TEST_FREC FND_VSET.VALUESET_DR;
15 CONTEXTS FND_DFLEX.CONTEXTS_DR;
16 I BINARY_INTEGER;
17 J BINARY_INTEGER;
18 SEGMENTS FND_DFLEX.SEGMENTS_DR;
19 BEGIN
20 FND_DFLEX.GET_FLEXFIELD('QP',P_FLEXFIELD_NAME,FLEXFIELD,FLEXINFO);
21 FND_DFLEX.GET_CONTEXTS(FLEXFIELD,CONTEXTS);
22 FND_DFLEX.GET_SEGMENTS(FND_DFLEX.MAKE_CONTEXT(FLEXFIELD,P_CONTEXT),SEGMENTS,TRUE);
23 FOR J IN 1..SEGMENTS.NSEGMENTS LOOP
24 IF SEGMENTS.SEGMENT_NAME(J) = P_SEG THEN
25 X_VALUESETID := SEGMENTS.VALUE_SET(J);
26 END IF;
27 END LOOP;
28 IF X_VALUESETID IS NOT NULL THEN
29 FND_VSET.GET_VALUESET(X_VALUESETID,TEST_REC,TEST_FREC);
30 X_VSID :=X_VALUESETID;
31 X_FORMAT_TYPE :=TEST_FREC.FORMAT_TYPE;
32 X_VALIDATION_TYPE :=TEST_REC.VALIDATION_TYPE;
33 ELSE
34 X_VSID :=NULL;
35 X_FORMAT_TYPE :='C';
36 X_VALIDATION_TYPE :=NULL;
37
38 END IF;
39 END GET_VALUESET_ID;
40
41 FUNCTION context_exists(p_context VARCHAR2,
42 p_context_dr fnd_dflex.contexts_dr,
43 p_context_r OUT NOCOPY fnd_dflex.context_r ) RETURN BOOLEAN IS
44 BEGIN
45 IF (p_context_dr.ncontexts > 0) THEN
46 FOR i IN 1..p_context_dr.ncontexts LOOP
47 IF (p_context = p_context_dr.context_code(i)
48 AND p_context_dr.is_enabled(i) = TRUE) THEN
49 p_context_r.context_code := p_context_dr.context_code(i);
50 RETURN TRUE;
51 END IF;
52 END LOOP;
53 RETURN FALSE;
54 ELSE
55 RETURN FALSE;
56 END IF;
57 END Context_exists;
58
59 FUNCTION segment_exists(p_segment_name IN VARCHAR2,
60 p_segments_dr IN fnd_dflex.segments_dr,
61 p_check_enabled IN BOOLEAN := TRUE,
62 p_value_set_id OUT NOCOPY NUMBER,
63 p_precedence OUT NOCOPY NUMBER) RETURN BOOLEAN IS
64 BEGIN
65 IF (p_segments_dr.nsegments > 0) THEN
66 FOR i IN 1..p_segments_dr.nsegments LOOP
67 IF p_check_enabled then
68 IF (p_segments_dr.application_column_name(i) = p_segment_name) and
69 p_segments_dr.is_enabled(i) THEN ---added bu svdeshmu as per renga/jay's request.
70 p_value_set_id := p_segments_dr.value_set(i);
71 p_precedence := p_segments_dr.sequence(i);
72 RETURN TRUE;
73 END IF;
74 ELSE
75 IF p_segments_dr.application_column_name(i) = p_segment_name THEN
76 p_value_set_id := p_segments_dr.value_set(i);
77 p_precedence := p_segments_dr.sequence(i);
78 RETURN TRUE;
79 END IF;
80 END IF;
81
82 END LOOP;
83 RETURN FALSE;
84 ELSE
85 RETURN FALSE;
86 END IF;
87 END segment_exists;
88
89 -- =======================================================================
90 -- Function value_exists
91 -- funtion type Private
92 -- Returns BOOLEAN
93 -- out parameters : None
94 -- DESCRIPTION
95 -- Searches for value if it exists in the value set list populated by
96 -- get_valueset call.
97 -- =======================================================================
98
99
100 FUNCTION value_exists(p_vsid IN NUMBER,p_value IN VARCHAR2) RETURN BOOLEAN IS
101 v_vset fnd_vset.valueset_r;
102 v_fmt fnd_vset.valueset_dr;
103 v_found BOOLEAN;
104 v_row NUMBER;
105 v_value fnd_vset.value_dr;
106 BEGIN
107 fnd_vset.get_valueset(p_vsid, v_vset, v_fmt);
108 fnd_vset.get_value_init(v_vset, TRUE);
109 fnd_vset.get_value(v_vset, v_row, v_found, v_value);
110
111 WHILE(v_found) LOOP
112 IF (v_value.value = p_value) THEN
113 fnd_vset.get_value_end(v_vset);
114 RETURN TRUE;
115 END IF;
116 fnd_vset.get_value(v_vset, v_row, v_found, v_value);
117 END LOOP;
118 fnd_vset.get_value_end(v_vset);
119 RETURN FALSE;
120 END value_exists;
121
122
123 -- =======================================================================
124 -- Function validate_num_date
125 -- funtion type public
126 -- Returns number
127 -- out parameters :
128 -- DESCRIPTION
129 --
130 --
131 -- =======================================================================
132
133 Function validate_num_date(p_datatype in varchar2
134 ,p_value in varchar2
135 )return number IS
136
137 x_error_code NUMBER:= 0;
138 l_date DATE;
139 l_number NUMBER;
140 BEGIN
141 IF p_datatype = 'N' THEN
142 l_number := fnd_number.canonical_to_number(p_value);
143 ELSIF p_datatype IN ('X', 'Y') THEN
144 --l_date := fnd_date.canonical_to_date(p_value);
145 l_date := to_date(p_value,'FXYYYY/MM/DD HH24:MI:SS');
146 END IF;
147 RETURN x_error_code;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 x_error_code := 9;
152 RETURN x_error_code;
153 END validate_num_date;
154
155
156 /*********** Procedure to validate Flexfield **************/
157 PROCEDURE validate_oks_flexfield(flexfield_name IN VARCHAR2,
158 context IN VARCHAR2,
159 attribute IN VARCHAR2,
160 value IN VARCHAR2,
161 application_short_name IN VARCHAR2,
162 context_flag OUT NOCOPY VARCHAR2,
163 attribute_flag OUT NOCOPY VARCHAR2,
164 value_flag OUT NOCOPY VARCHAR2,
165 datatype OUT NOCOPY VARCHAR2,
166 precedence OUT NOCOPY VARCHAR2,
167 error_code OUT NOCOPY NUMBER ,
168 check_enabled IN BOOLEAN := TRUE) IS
169
170 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
171 SELECT application_id
172 FROM fnd_application
173 WHERE application_short_name = app_short_name;
174
175 v_context_dr fnd_dflex.contexts_dr;
176 v_dflex_r fnd_dflex.dflex_r;
177 v_context_r fnd_dflex.context_r;
178 v_segments_dr fnd_dflex.segments_dr;
179 v_value_set_id NUMBER;
180 v_precedence NUMBER;
181 v_valueset_r fnd_vset.valueset_r;
182 v_format_dr fnd_vset.valueset_dr;
183 v_valueset_dr fnd_vset.valueset_dr;
184 v_dflex_dr fnd_dflex.dflex_dr;
185 v_flexfield_val_ind NUMBER DEFAULT 0;
186 l_value VARCHAR2(150);
187 l_id VARCHAR2(150);
188 BEGIN
189 context_flag := 'N';
190 attribute_flag := 'N';
191 value_flag := 'N';
192 error_code := 0;
193 IF (flexfield_name IS NULL) THEN
194 error_code := 1; -- flexfield_name is not passed.
195 RETURN;
196 END IF;
197 IF (context IS NULL) THEN
198 error_code := 2;
199 RETURN; -- context value is not passed
200 END IF;
201 IF (attribute IS NULL) THEN
202 error_code := 3;
203 RETURN; -- attribute value is not passed.
204 END IF;
205 IF (value IS NULL) THEN
206 error_code := 4; -- value is not passed
207 RETURN;
208 END IF;
209 IF (application_short_name IS NULL) THEN
210 error_code := 5; -- application short name is not passed
211 RETURN;
212 END IF;
213
214 -- Get the application_id
215
216 OPEN Cur_get_application_id(application_short_name);
217 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
218 IF (Cur_get_application_id%NOTFOUND) THEN
219 CLOSE Cur_get_application_id;
220 error_code := 6; -- Invalid application short name.
221 RETURN;
222 END IF;
223 CLOSE Cur_get_application_id;
224
225 -- check if flexfield name passed is a valid one or not.
226 v_flexfield_val_ind:= 1;
227 fnd_dflex.get_flexfield(application_short_name,flexfield_name,v_dflex_r,v_dflex_dr);
228
229 -- Get the context listing for the flexfield
230 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
231
232 IF (context_exists(context,v_context_dr,v_context_r) = TRUE) THEN
233 context_flag := 'Y';
234 ELSE
235 context_flag := 'N';
236 error_code := 7; -- Invalid context passed
237 RETURN;
238 END IF;
239
240 v_context_r.flexfield := v_dflex_r;
241
242 -- Get the enabled segments for the context selected.
243
244 --fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
245 fnd_dflex.get_segments(v_context_r,v_segments_dr,FALSE);
246
247 IF (segment_exists(attribute,v_segments_dr,check_enabled,v_value_set_id,v_precedence) = TRUE) THEN
248 IF (v_precedence IS NOT NULL) THEN
249 precedence := v_precedence;
250 END IF;
251 attribute_flag := 'Y';
252 IF (v_value_set_id IS NULL) THEN
253 datatype := 'C';
254 value_flag := 'Y'; -- If there is no valueset attached then just pass the validation.
255 error_code := 0;
256 RETURN;
257 END IF;
258 ELSE
259 attribute_flag :='N';
260 error_code := 8; -- Invalid segment passed
261 RETURN;
262 END IF;
263
264 -- Get value set information and validate the value passed.
265 fnd_vset.get_valueset(v_value_set_id,v_valueset_r,v_valueset_dr);
266
267 datatype := v_valueset_dr.format_type;
268
269 -- check if there is any value set attached to the segment
270 IF (v_value_set_id IS NULL or not g_validate_flag) THEN
271 error_code := 0;
272 value_flag := 'Y';
273 RETURN;
274 END IF;
275 -- If Validation type is independent
276
277 IF (v_valueset_r.validation_type = 'I') THEN
278 IF (value_exists(v_value_set_id,value) = TRUE) THEN
279 value_flag := 'Y';
280 error_code := 0; -- successfull
281 RETURN;
282 ELSE
283 value_flag := 'N';
284 error_code := 9; -- Value does not exist.
285 END IF;
286 ELSIF (v_valueset_r.validation_type = 'F') THEN
287 IF (value_exists_in_table(v_valueset_r.table_info,value,l_id,l_value) = TRUE) THEN
288 value_flag := 'Y';
289 error_code := 0; -- Successfull
290 ELSE
291 value_flag := 'N';
292 error_code := 9; -- Value does not exist.
293 RETURN;
294 END IF;
295 ELSIF (v_valueset_r.validation_type = 'N') or datatype in( 'N','X','Y') THEN
296 --value_flag := 'Y';
297 --error_code := 0;
298
299 ---added for proper handling of dates/number in multilingual envs.
300 ---uncomment whenever needed(svdeshmu)
301 error_code := validate_num_date(datatype,value);
302 If error_code = 0 then
303 value_flag := 'Y';
304 else
305 value_flag := 'N';
306 End if;
307 END IF;
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 IF (v_flexfield_val_ind = 1) THEN
311 error_code := 10;
312 RETURN;
313 END IF;
314 END validate_oks_flexfield;
315
316 PROCEDURE GET_PROD_FLEX_PROPERTIES( PRIC_ATTRIBUTE_CONTEXT IN VARCHAR2,
317 PRIC_ATTRIBUTE IN VARCHAR2,
318 PRIC_ATTR_VALUE IN VARCHAR2,
319 X_DATATYPE OUT NOCOPY VARCHAR2,
320 X_PRECEDENCE OUT NOCOPY NUMBER,
321 X_ERROR_CODE OUT NOCOPY NUMBER)
322 IS
323
324 L_CONTEXT_FLAG VARCHAR2(1);
325 L_ATTRIBUTE_FLAG VARCHAR2(1);
326 L_VALUE_FLAG VARCHAR2(1);
327 L_DATATYPE VARCHAR2(1);
328 L_PRECEDENCE NUMBER;
329 L_ERROR_CODE NUMBER := 0;
330
331 BEGIN
332
333 OKS_UTIL_PUB.VALIDATE_OKS_FLEXFIELD(FLEXFIELD_NAME =>'QP_ATTR_DEFNS_PRICING'
334 ,CONTEXT =>PRIC_ATTRIBUTE_CONTEXT
335 ,ATTRIBUTE =>PRIC_ATTRIBUTE
336 ,VALUE =>PRIC_ATTR_VALUE
337 ,APPLICATION_SHORT_NAME => 'QP'
338 ,CHECK_ENABLED =>FALSE
339 ,CONTEXT_FLAG =>L_CONTEXT_FLAG
340 ,ATTRIBUTE_FLAG =>L_ATTRIBUTE_FLAG
341 ,VALUE_FLAG =>L_VALUE_FLAG
342 ,DATATYPE =>L_DATATYPE
343 ,PRECEDENCE =>L_PRECEDENCE
344 ,ERROR_CODE =>L_ERROR_CODE
345 );
346
347 X_DATATYPE := NVL(L_DATATYPE,'C');
348 X_PRECEDENCE := NVL(L_PRECEDENCE,5000);
349
350 END GET_PROD_FLEX_PROPERTIES;
351
352
353 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
354 p_value VARCHAR2,
355 x_id OUT NOCOPY VARCHAR2,
356 x_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
357
358 v_selectstmt VARCHAR2(500) ;
359 v_cursor_id INTEGER;
360 v_value VARCHAR2(150);
361 v_meaning VARCHAR2(240);
362 v_id VARCHAR2(150);
363 v_retval INTEGER;
364 v_where_clause fnd_flex_validation_tables.additional_where_clause%type;
365 v_cols VARCHAR2(1000);
366
367 BEGIN
368 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
369
370 if instr(upper(p_table_r.where_clause),'WHERE ') > 0 then
371 --to include the id column name in the query
372
373 --included extra quotes for comparing varchar and num values in select
374 v_where_clause := replace(UPPER(p_table_r.where_clause)
375 ,'WHERE '
376 ,'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND ');
377
378 else
379
380 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
381
382 end if;
383
384 v_cols := p_table_r.value_column_name;
385
386 -------------------
387 --changes made by spgopal for performance problem
388 --added out parameters to pass back id and value for given valueset id
389 -------------------
390
391 IF (p_table_r.id_column_name IS NOT NULL) THEN
392
393 --
394 -- to_char() conversion function is defined only for
395 -- DATE and NUMBER datatypes.
396 --
397 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
398 v_cols := v_cols || ' , To_char(' || p_table_r.id_column_name || ')';
399 ELSE
400 v_cols := v_cols || ' , ' || p_table_r.id_column_name;
401 END IF;
402 ELSE
403 v_cols := v_cols || ', NULL ';
404 END IF;
405
406
407
408 v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
409
410 oe_debug_pub.add('select stmt'||v_selectstmt);
411
412 ------------------
413
414 /*
415 IF p_table_r.id_column_name is not null then
416
417 v_selectstmt := 'SELECT '||p_table_r.id_column_name||' FROM '||p_table_r.table_name||' '||v_where_clause;
418
419 ELSE
420
421 v_selectstmt := 'SELECT '||p_table_r.value_column_name||' FROM '||p_table_r.table_name||' '||p_table_r.where_clause;
422
423 END IF;
424 */
425
426 -- parse the query
427
428 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
429 oe_debug_pub.add('after parse');
430 -- Bind the input variables
431 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_value,150);
432 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_id,150);
433 v_retval := DBMS_SQL.EXECUTE(v_cursor_id);
434 LOOP
435 -- Fetch rows in to buffer and check the exit condition from the loop
436 IF( DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0) THEN
437 EXIT;
438 END IF;
439 -- Retrieve the rows from buffer into PLSQL variables
440 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_value);
441 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_id);
442
443
444 IF v_id IS NULL AND (p_value = v_value) THEN
445 oe_debug_pub.add('id null, passing value'||p_value||','||v_value);
446 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
447 x_id := v_id;
448 x_value := v_value;
449 RETURN TRUE;
450 ELSIF (p_value = v_id) THEN
451 oe_debug_pub.add('id exists, passing id'||p_value||','||v_id);
452 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
453 x_id := v_id;
454 x_value := v_value;
455 RETURN TRUE;
456 ELSE
457 Null;
458 oe_debug_pub.add('value does notmatch, continue search'||p_value||','||v_id);
459 END IF;
460 END LOOP;
461 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
462 RETURN FALSE;
463 EXCEPTION
464 WHEN OTHERS THEN
465
466 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
467 RETURN FALSE;
468 END value_exists_in_table;
469
470 FUNCTION Get_OKS_Status RETURN VARCHAR2 IS
471
472 l_status VARCHAR2(1);
473 l_industry VARCHAR2(1);
474 l_application_id NUMBER := 661;
475 l_retval BOOLEAN;
476 BEGIN
477
478
479 IF G_PRODUCT_STATUS = FND_API.G_MISS_CHAR THEN
480
481 l_retval := fnd_installation.get(l_application_id,l_application_id,
482 l_status,l_industry);
483
484 -- if l_status = 'I', OKS is fully installed. Advanced pricing functionalities
485 -- should be available.
486 --if l_status = 'S', OKS is shared ie Basic OKS is Installed.Only basic
487 --pricing functionality should be available.
488 --if l_status = 'N', -- OKS not installled
489
490 G_PRODUCT_STATUS := l_status;
491
492 END IF;
493
494 return G_PRODUCT_STATUS;
495
496 END Get_OKS_Status;
497
498 FUNCTION Resp_Org_id RETURN NUMBER IS
499 Begin
500 If fnd_profile.value('OKC_VIEW_K_BY_ORG') = 'Y' then
501 return fnd_profile.value('ORG_ID');
502 Else
503 return null;
504 End If;
505 End Resp_Org_id;
506
507 PROCEDURE UPDATE_CONTACTS_SALESGROUP
508 ( ERRBUF OUT NOCOPY VARCHAR2,
509 RETCODE OUT NOCOPY NUMBER,
510 P_CONTRACT_ID IN NUMBER,
511 P_GROUP_ID IN NUMBER)
512 IS
513 --
514
515 ---------------------------
516 -- cursors to select contracts for given contract number
517 -- and group
518 ---------------------------
519 --CP_CONTRACT_ID is not null
520 --cp_group_id is not null
521 CURSOR CONTRACT_HDR_1(cp_contract_id NUMBER
522 ,cp_group_id NUMBER)
523 IS
524 SELECT hdr.id hdr_id, authoring_org_id org_id
525 FROM okc_k_headers_b hdr
526 WHERE Hdr.scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION')
527 AND Hdr.Template_yn = 'N'
528 AND exists (Select 'x' from OKC_K_GRPINGS okg
529 Where okg.included_chr_id = hdr.id
530 And okg.cgp_parent_id= cp_group_id)
531 AND Hdr.id = CP_CONTRACT_ID;
532
533
534 --CP_CONTRACT_ID is not null
535 --cp_group_id is null
536 CURSOR CONTRACT_HDR_2(cp_contract_id NUMBER)
537 IS
538 SELECT hdr.id hdr_id, authoring_org_id org_id
539 FROM okc_k_headers_b hdr
540 WHERE Hdr.scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION')
541 AND Hdr.Template_yn = 'N'
542 AND Hdr.id = CP_CONTRACT_ID;
543
544
545 --CP_CONTRACT_ID is null
546 --cp_group_id is not null
547 CURSOR CONTRACT_HDR_3(cp_group_id NUMBER)
548 IS
549 SELECT hdr.id hdr_id, authoring_org_id org_id
550 FROM okc_k_headers_b hdr
551 WHERE Hdr.scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION')
552 AND Hdr.Template_yn = 'N'
553 AND exists (Select 'x' from OKC_K_GRPINGS okg
554 Where okg.included_chr_id = hdr.id
555 And okg.cgp_parent_id= cp_group_id);
556
557 --CP_CONTRACT_ID is null
558 --cp_group_id is null
559
560 CURSOR CONTRACT_HDR_4
561 IS
562 SELECT /*+ PARALLEL(HDR) */ hdr.id hdr_id, authoring_org_id org_id
563 FROM okc_k_headers_b HDR
564 WHERE Hdr.scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION')
565 AND Hdr.Template_yn = 'N';
566
567 ----------------------------
568 -- cursor to select vendor contacts salesrep
569 ----------------------------
570 CURSOR contacts_cur (cp_contract_id IN NUMBER)
571 IS
572 SELECT id , object1_id1 ,object1_id2, cpl_id, cro_code , start_date, last_update_date
573 FROM OKC_CONTACTS
574 WHERE dnz_chr_id = cp_contract_id
575 AND jtot_object1_code = 'OKX_SALEPERS'
576 -- and cro_code = 'SALESPERSON' -- changed for bug#3564073
577 AND sales_group_id IS NULL;
578
579 ---------------------------
580 l_api_version NUMBER := 1.0;
581 l_init_msg_list VARCHAR2(1) := OKC_API.G_FALSE;
582 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
583 l_msg_count NUMBER;
584 l_msg_data VARCHAR2(2000);
585 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
586 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
587
588 l_hdr_rec CONTRACT_HDR_1%rowtype;
589
590 l_salesgrp_id Number;
591 l_org_id NUmber;
592 l_index NUmber;
593
594
595 BEGIN
596 OKC_CONTEXT.SET_OKC_ORG_CONTEXT;
597 l_org_id := OKC_CONTEXT.GET_OKC_ORG_ID;
598
599 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Upadte contacts salesgroup ='||to_char(p_contract_id));
600
601 IF (p_contract_id is NOT NULL) AND (p_group_id is NOT NULL) THEN
602 OPEN contract_hdr_1(p_contract_id,p_group_id);
603
604 ELSIF (p_contract_id is NULL) AND (p_group_id is NULL) THEN
605 OPEN contract_hdr_4;
606
607 ELSIF (p_contract_id is NOT NULL) AND (p_group_id is NULL) THEN
608 OPEN contract_hdr_2(p_contract_id);
609
610
611 ELSIF (p_contract_id is NULL) AND (p_group_id is NOT NULL) THEN
612 OPEN contract_hdr_3(p_group_id);
613
614 END IF;
615
616 LOOP
617 IF contract_hdr_1%ISOPEN then
618 FETCH contract_hdr_1 INTO l_hdr_rec ;
619 EXIT WHEN contract_hdr_1%NOTFOUND ;
620 ELSIF contract_hdr_2%ISOPEN then
621 FETCH contract_hdr_2 INTO l_hdr_rec ;
622 EXIT WHEN contract_hdr_2%NOTFOUND ;
623 ELSIF contract_hdr_3%ISOPEN then
624 FETCH contract_hdr_3 INTO l_hdr_rec ;
625 EXIT WHEN contract_hdr_3%NOTFOUND ;
626 ELSIF contract_hdr_4%ISOPEN then
627 FETCH contract_hdr_4 INTO l_hdr_rec ;
628 EXIT WHEN contract_hdr_4%NOTFOUND ;
629 END IF ;
630
631 -- set okc contex
632 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(p_chr_id => l_hdr_rec.hdr_id);
633
634 for contacts_rec in contacts_cur(cp_contract_id => l_hdr_rec.hdr_id)
635 loop
636 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Contract ID ='||l_hdr_rec.hdr_id);
637 FND_FILE.PUT_LINE(FND_FILE.LOG, 'contact ='||contacts_rec.object1_id1);
638 l_salesgrp_id := jtf_rs_integration_pub.get_default_sales_group
639 (p_salesrep_id => contacts_rec.object1_id1,
640 p_org_id => l_hdr_rec.org_id,
641 p_date => nvl(contacts_rec.start_date,contacts_rec.last_update_date)); -- sysdate replaced for DBI change request.
642
643 FND_FILE.PUT_LINE(FND_FILE.LOG, 'defaultsalesgroup ='||to_char(l_salesgrp_id));
644
645 l_ctcv_tbl_in(1).id := contacts_rec.id;
646 l_ctcv_tbl_in(1).dnz_chr_id := l_hdr_rec.hdr_id;
647 l_ctcv_tbl_in(1).object_version_number := okc_api.g_miss_NUM;
648 l_ctcv_tbl_in(1).cpl_id := contacts_rec.cpl_id;
649 l_ctcv_tbl_in(1).cro_code := contacts_rec.cro_code;
650 l_ctcv_tbl_in(1).object1_id1 := contacts_rec.object1_id1;
651 l_ctcv_tbl_in(1).object1_id2 := '#';
652 l_ctcv_tbl_in(1).sales_group_id := l_salesgrp_id;
653
654 okc_contract_party_pub.update_contact (
655 p_api_version => l_api_version,
656 p_init_msg_list => l_init_msg_list,
657 x_return_status => l_return_status,
658 x_msg_count => l_msg_count,
659 x_msg_data => l_msg_data,
660 p_ctcv_tbl => l_ctcv_tbl_in,
661 x_ctcv_tbl => l_ctcv_tbl_out );
662
663
664 FND_FILE.PUT_LINE(FND_FILE.LOG, 'return status ='||l_return_status);
665
666 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
667 FOR i in 1..fnd_msg_pub.count_msg
668 Loop
669 fnd_msg_pub.get
670 (p_msg_index => i,
671 p_encoded => 'F',
672 p_data => l_msg_data,
673 p_msg_index_out => l_index
674 );
675 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error from okc_API ='||l_msg_data);
676 End Loop;
677 l_return_status := l_return_status;
678 -- commented out since program should continue even there is error.05/25/04
679 -- RAISE G_EXCEPTION_HALT_VALIDATION;
680 END IF;
681 end loop;
682 end loop;
683
684 IF contract_hdr_1%ISOPEN THEN
685 CLOSE contract_hdr_1;
686 ELSIF contract_hdr_2%ISOPEN THEN
687 CLOSE contract_hdr_2;
688 ELSIF contract_hdr_3%ISOPEN THEN
689 CLOSE contract_hdr_3;
690 ELSIF contract_hdr_4%ISOPEN THEN
691 CLOSE contract_hdr_4;
692 END IF;
693
694 EXCEPTION
695 WHEN G_EXCEPTION_HALT_VALIDATION THEN
696 NULL;
697 WHEN OTHERS THEN
698 -- store SQL error message on message stack for caller
699 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
700 -- notify caller of an UNEXPECTED error
701 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
702 END update_contacts_salesgroup; -- Procedure
703
704 -- ===========================================================================
705 -- Function get_line_name
706 -- Input Parameters:
707 -- p_lty_code : Line Type Code (lty_code from okc_line_styles_b)
708 -- p_object1_id1 : Object Id 1 (object1_id1 from okc_k_items)
709 -- p_object1_id2 : Object Id 2 (object1_id2 from okc_k_items)
710 -- Return Value:
711 -- Name of the line item (VARCHAR2)
712 -- This function returns the name of a covered level
713 -- with one of the line types:
714 -- COVER_CUST, COVER_ITEM, COVER_PROD, COVER_PTY,
715 -- COVER_SITE, COVER_SYS, INST_CTR
716 -- The Function doesn't validate the input.
717 -- For invalid inputs the function returns NULL.
718 -- The parameter p_object1_id2 is ignored for all line types except COVER_ITEM
719 -- This function can be extended to accept other line types
720 -- including top lines, in the future
721 -- Created: JAKURUVI 08/12/2005
722 -- Modified: JAKURUVI 03/13/2006: Changed parameter p_lse_id to p_lty_code
723 -- ===========================================================================
724 FUNCTION get_line_name( p_lty_code IN VARCHAR2,
725 p_object1_id1 IN VARCHAR2,
726 p_object1_id2 IN VARCHAR2 ) RETURN VARCHAR2 IS
727 -- Covered Items
728 CURSOR itemcur IS
729 SELECT concatenated_segments name
730 FROM mtl_system_items_b_kfv
731 WHERE inventory_item_id = to_number(p_object1_id1)
732 and organization_id = to_number(p_object1_id2);
733
734 -- Covered Products
735 CURSOR prodcur IS
736 SELECT concatenated_segments name
737 FROM mtl_system_items_b_kfv mtl,
738 csi_item_instances csi
739 WHERE mtl.inventory_item_id = csi.inventory_item_id
740 AND mtl.organization_id = csi.inv_master_organization_id
741 AND csi.instance_id = to_number(p_object1_id1);
742
743 -- Covered Parties
744 CURSOR partycur IS
745 SELECT party_name name
746 FROM hz_parties
747 WHERE party_id = to_number(p_object1_id1);
748
749 -- Covered Sites
750 CURSOR sitecur IS
751 SELECT party_site_number||'-'||party_site_name name
752 FROM hz_party_sites p
753 WHERE party_site_id = to_number(p_object1_id1);
754
755 -- Covered Systems
756 CURSOR systemcur IS
757 SELECT name
758 FROM csi_systems_tl csi
759 WHERE csi.system_id = to_number(p_object1_id1)
760 AND csi.language = USERENV('LANG');
761
762 -- Covered Customer Accounts
763 CURSOR customercur IS
764 SELECT NVL(ca.account_name, p.party_name) name
765 FROM hz_cust_accounts ca,
766 hz_parties p
767 WHERE ca.party_id = p.party_id
768 AND ca.cust_account_id = to_number(p_object1_id1);
769
770 -- Usage Items(Counters)
771 CURSOR ctrtypecur IS
772 SELECT source_object_code, source_object_id
773 FROM csi_counter_associations csi
774 WHERE csi.counter_id = to_number(p_object1_id1);
775 -- Covered Product Counters
776 CURSOR cpctrcur(p_instance_id IN NUMBER) IS
777 SELECT concatenated_segments name
778 FROM mtl_system_items_b_kfv mtl,
779 csi_item_instances csi
780 WHERE mtl.inventory_item_id = csi.inventory_item_id
781 AND mtl.organization_id = csi.inv_master_organization_id
782 AND csi.instance_id = p_instance_id;
783 -- Service Counters
784 CURSOR svcctrcur(p_line_id IN NUMBER) IS
785 SELECT concatenated_segments name
786 FROM mtl_system_items_b_kfv mtl,
787 okc_k_items item
788 WHERE mtl.inventory_item_id = to_number(item.object1_id1)
789 AND mtl.organization_id = to_number(item.object1_id2)
790 AND item.cle_id = p_line_id;
791
792 BEGIN
793 IF p_lty_code = 'COVER_ITEM' THEN -- Item
794 FOR itemrec IN itemcur LOOP
795 RETURN itemrec.name;
796 END LOOP;
797 ELSIF p_lty_code = 'COVER_PTY' THEN -- Party
798 FOR partyrec IN partycur LOOP
799 RETURN partyrec.name;
800 END LOOP;
801 ELSIF p_lty_code = 'COVER_PROD' THEN -- Product
802 FOR prodrec IN prodcur LOOP
803 RETURN prodrec.name;
804 END LOOP;
805 ELSIF p_lty_code = 'COVER_SITE' THEN -- Site
806 FOR siterec IN sitecur LOOP
807 RETURN siterec.name;
808 END LOOP;
809 ELSIF p_lty_code = 'COVER_SYS' THEN -- System
810 FOR systemrec IN systemcur LOOP
811 RETURN systemrec.name;
812 END LOOP;
813 ELSIF p_lty_code = 'COVER_CUST' THEN -- Customer
814 FOR customerrec IN customercur LOOP
815 RETURN customerrec.name;
816 END LOOP;
817 ELSIF p_lty_code = 'INST_CTR' THEN -- Usage Item(Counter)
818 FOR ctrtyperec IN ctrtypecur LOOP
819 IF ctrtyperec.source_object_code = 'CP' THEN -- Covered Product Counter
820 FOR cpctrrec IN cpctrcur(ctrtyperec.source_object_id) LOOP
821 RETURN cpctrrec.name;
822 END LOOP;
823 ELSIF ctrtyperec.source_object_code = 'CONTRACT_LINE' THEN -- Service Counter
824 FOR svcctrrec IN svcctrcur(ctrtyperec.source_object_id) LOOP
825 RETURN svcctrrec.name;
826 END LOOP;
827 END IF;
828 END LOOP;
829 END IF;
830 RETURN NULL;
831 EXCEPTION when OTHERS THEN
832 RETURN NULL;
833 END get_line_name;
834
835 -- This function is an overloaded wrapper to the above function
836 -- To be used if only the line id is known
837 FUNCTION get_line_name( p_subline_id IN NUMBER ) RETURN VARCHAR2 IS
838 CURSOR sublinecur IS
839 SELECT lse.lty_code, item.object1_id1, item.object1_id2
840 FROM okc_k_lines_b sle, okc_k_items item, okc_line_styles_b lse
841 WHERE item.cle_id = sle.id
842 AND lse.id = sle.lse_id
843 AND sle.id = p_subline_id;
844 l_name VARCHAR2(200);
845 BEGIN
846 FOR sublinerec IN sublinecur LOOP
847 l_name := get_line_name
848 ( p_lty_code => sublinerec.lty_code,
849 p_object1_id1 => sublinerec.object1_id1,
850 p_object1_id2 => sublinerec.object1_id2
851 );
852 EXIT;
853 END LOOP;
854 RETURN l_name;
855 EXCEPTION when OTHERS THEN
856 RETURN NULL;
857 END get_line_name;
858
859 -------
860 Procedure create_transaction_extension(P_Api_Version IN NUMBER
861 ,P_Init_Msg_List IN VARCHAR2
862 ,P_Header_ID IN NUMBER
863 ,P_Line_ID IN NUMBER
864 ,P_Source_Trx_Ext_ID IN NUMBER
865 ,P_Cust_Acct_ID IN NUMBER
866 ,P_Bill_To_Site_Use_ID IN NUMBER
867 ,x_entity_id OUT NOCOPY NUMBER
868 ,x_msg_data OUT NOCOPY VARCHAR2
869 ,x_msg_count OUT NOCOPY NUMBER
870 ,x_return_status OUT NOCOPY VARCHAR2) IS
871
872 l_api_name CONSTANT VARCHAR2(30) := 'create_transaction_extension';
873 l_module_name VARCHAR2(256) := G_APP_NAME || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
874
875 --Input parameters--
876 l_PayerContext_Rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
877 l_Payer_Equivalency VARCHAR2(20);
878 l_Pmt_channel VARCHAR2(20);
879 l_instr_assignment NUMBER;
880 l_TrxnExtension_rec IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
881 l_ext_entity_tab IBY_FNDCPT_COMMON_PUB.Id_tbl_type; --Used by copy_transaction_extension
882
883
884 --Output parameters--
885 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
886 l_entity_id NUMBER;
887
888 --Local Variables--
889 l_Authoring_Org_ID NUMBER;
890
891 l_Cust_Account_Site_ID NUMBER;
892 l_Cust_Account_ID NUMBER;
893 l_Party_ID NUMBER;
894
895 ---Cursor to retrieve Account_Site_ID, Cust_Account_ID and Party_ID for Header
896 CURSOR GetAcctInfo IS
897 select
898 cas.cust_account_id Cust_Account_ID
899 ,ca.party_id Party_ID
900 from
901 hz_cust_site_uses_all csu
902 ,hz_cust_acct_sites_all cas
903 ,hz_cust_accounts_all ca
904 where
905 csu.site_use_id = P_Bill_To_Site_Use_ID
906 and cas.cust_acct_site_id = csu.cust_acct_site_id
907 and ca.cust_account_id = cas.cust_account_id;
908
909 CURSOR GetCustAcctParty IS
910 select
911 ca.party_id Party_ID
912 from
913 hz_cust_accounts_all ca
914 where ca.cust_account_id = P_Cust_Acct_ID;
915
916 --Cursor to get Instrument_Assignment_ID
917 CURSOR GetInstrAssgnID IS
918 select
919 instr_assignment_ID
920 /* Modified by cgopinee for PA DSS Enhancement */
921 /*,CARD_EXPIRYDATE */
922 , card_expired_flag
923 from
924 IBY_TRXN_EXTENSIONS_V
925 where
926 trxn_extension_ID = P_Source_Trx_Ext_ID;
927
928 l_Instrument_Assignment_ID NUMBER;
929 /* Modified by cgopinee for PA DSS Enhancement */
930 /* l_CC_Expiry_Date DATE; */
931 l_CC_Expiry_Flag VARCHAR2(10);
932
933 Begin
934 l_ext_entity_tab.DELETE;
935 l_ext_entity_tab(0) := P_Source_Trx_Ext_ID;
936
937 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
938 IF (FND_LOG.test(FND_LOG.level_procedure, l_module_name)) THEN
939 FND_LOG.string(FND_LOG.level_procedure
940 ,l_module_name||'.Begin'
941 ,'P_Source_Trx_Ext_ID='||P_Source_Trx_Ext_ID
942 ||',P_Bill_To_Site_Use_ID='||P_Bill_To_Site_Use_ID
943 ||',P_Header_ID='||P_Header_ID
944 ||',P_Line_ID='||P_Line_ID
945 );
946 END IF;
947 END IF;
948
949 OPEN GetInstrAssgnID;
950 Fetch GetInstrAssgnID INTO l_Instrument_Assignment_ID,l_CC_Expiry_Flag;
951 CLOSE GetInstrAssgnID;
952
953 /* Modified by cgopinee for PA DSS Enhancement */
954 /*
955 IF ( to_number(to_char(l_CC_Expiry_Date,'YYYYMM')) <
956 to_number(to_char(SYSDATE,'YYYYMM'))
957 ) then*/
958
959 IF NVL(l_CC_Expiry_Flag,'N') = 'Y'
960 THEN
961 x_entity_id := NULL;
962 x_return_status := FND_API.G_RET_STS_SUCCESS;
963 ELSE
964
965 l_Authoring_Org_ID := okc_context.get_okc_org_id;
966 l_Cust_Account_Site_ID := P_Bill_To_Site_Use_ID;
967
968 IF (P_Cust_Acct_ID IS NULL) THEN
969 OPEN GetAcctInfo;
970 FETCH GetAcctInfo INTO
971 l_Cust_Account_ID
972 ,l_Party_ID;
973 CLOSE GetAcctInfo;
974
975 ELSIF (P_Cust_Acct_ID IS NOT NULL) THEN
976 l_Cust_Account_ID := P_Cust_Acct_ID;
977
978 OPEN GetCustAcctParty;
979 FETCH GetCustAcctParty INTO l_Party_ID;
980 CLOSE GetCustAcctParty;
981
982 END IF;
983
984
985
986
987 --Setting values for l_PayerContext_Rec--
988 -- l_PayerContext_Rec.Org_Type := 'OPERATING_UNIT';
989 -- l_PayerContext_Rec.Org_Id := l_Authoring_Org_ID;
990 -- l_PayerContext_Rec.Account_Site_Id := l_Cust_Account_Site_ID;
991 l_PayerContext_Rec.Payment_Function := 'CUSTOMER_PAYMENT';
992 l_PayerContext_Rec.Party_Id := l_Party_ID;
993 l_PayerContext_Rec.Cust_Account_Id := l_Cust_Account_ID;
994
995 --Setting values for l_TrxnExtension_rec--
996 If (P_Header_ID IS NOT NULL and P_Line_ID IS NULL) then
997 l_TrxnExtension_rec.order_id := P_Header_ID;
998 Elsif (P_Header_ID IS NULL and P_Line_ID IS NOT NULL) then
999 l_TrxnExtension_rec.order_id := P_Line_ID;
1000 End If;
1001
1002 l_TrxnExtension_rec.originating_application_id := G_APP_ID;
1003 l_TrxnExtension_rec.Trxn_Ref_Number1 := to_char(SYSDATE,'ddmmyyyyhhmmssss');
1004 -- hkamdar 17-Mar-2006 Commented for bug # 5095244. Equivalency of FULL needs to be passed when copying an
1005 -- EXT WARR CONTRACT, CREATED FROM OM WITH CC INFO.
1006 -- l_Payer_Equivalency := IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD;
1007 l_Payer_Equivalency := IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL;
1008 -- End hkamdar for bug # 5095244
1009 l_Pmt_Channel := 'CREDIT_CARD';
1010 l_instr_assignment := l_Instrument_Assignment_ID;
1011
1012 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1013 FND_LOG.string(FND_LOG.level_statement
1014 ,l_module_name
1015 ,'Org ID='||l_Authoring_Org_ID
1016 ||'Party ID='||l_Party_ID
1017 ||'Acct_Id ='||l_Cust_Account_ID
1018 ||'Site_Id='||l_Cust_Account_Site_ID
1019 );
1020 END IF;
1021
1022 Begin
1023
1024 IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension
1025 (
1026 p_api_version => P_api_version
1027 ,p_init_msg_list => p_init_msg_list
1028 ,p_commit => FND_API.G_FALSE
1029 ,x_return_status => x_return_status
1030 ,x_msg_count => x_msg_count
1031 ,x_msg_data => x_msg_data
1032 ,p_payer => l_PayerContext_Rec
1033 ,p_payer_equivalency => l_Payer_Equivalency
1034 ,p_pmt_channel => l_Pmt_channel
1035 ,p_instr_assignment => l_instr_assignment
1036 ,p_trxn_attribs => l_TrxnExtension_rec
1037 ,x_entity_id => x_entity_id
1038 ,x_response => l_response
1039 );
1040
1041 /****
1042 IBY_FNDCPT_TRXN_PUB.Copy_Transaction_Extension
1043 (
1044 p_api_version => p_api_version
1045 ,p_init_msg_list => p_init_msg_list
1046 ,p_commit => FND_API.G_FALSE
1047 ,x_return_status => x_return_status
1048 ,x_msg_count => x_msg_count
1049 ,x_msg_data => x_msg_data
1050 ,p_payer => l_PayerContext_Rec
1051 ,p_payer_equivalency => l_Payer_Equivalency
1052 ,p_entities => l_ext_entity_tab
1053 ,p_trxn_attribs => l_TrxnExtension_rec
1054 ,x_entity_id => x_entity_id
1055 ,x_response => l_response
1056 );
1057 ****/
1058
1059
1060
1061 IF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1062 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
1063 fnd_message.set_token
1064 ('IBY_API_NAME',
1065 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension : '||l_response.result_code);
1066 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
1067 fnd_msg_pub.ADD;
1068 RAISE fnd_api.g_exc_unexpected_error;
1069 ELSIF (x_return_status = FND_API.g_ret_sts_error) THEN
1070 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
1071 fnd_message.set_token
1072 ('IBY_API_NAME',
1073 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension :'||l_response.result_code);
1074 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
1075 fnd_msg_pub.ADD;
1076 RAISE fnd_api.g_exc_error;
1077 END IF;
1078
1079
1080
1081
1082 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1083 FND_LOG.string(FND_LOG.level_statement
1084 ,l_module_name
1085 ,'After call to IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension'
1086 ||',x_return_status='||x_return_status
1087 ||',Result Code ='||l_response.result_code
1088 ||',Result Category='||l_response.result_category
1089 ||',Result Message='||l_response.result_message
1090 );
1091 END IF;
1092
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1097 FND_LOG.string(FND_LOG.level_unexpected
1098 ,l_module_name||'.EXCEPTION'
1099 ,'Exception in call to IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension, x_return_status ='||x_return_status
1100 ||'SQLERRM ='||SQLERRM
1101 );
1102 END IF;
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 FND_MSG_PUB.add_exc_msg(g_pkg_name, 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension', substr(SQLERRM,1,240));
1105 RAISE;
1106
1107 End;
1108
1109 END IF; --End of IF Check for l_CC_Expiry_Date--------------------------------------
1110
1111 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1112 IF (FND_LOG.test(FND_LOG.level_procedure, l_module_name)) THEN
1113 FND_LOG.string(FND_LOG.level_procedure
1114 ,l_module_name||'.End'
1115 ,'x_return_status ='||x_return_status
1116 );
1117 END IF;
1118 END IF;
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1123 FND_LOG.string(FND_LOG.level_unexpected
1124 ,l_module_name||'.EXCEPTION'
1125 ,'General Exception in Create_Transaction_Exception, x_return_status ='||x_return_status
1126 ||'SQLERRM ='||SQLERRM
1127 );
1128 END IF;
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1130 IF GetInstrAssgnID%ISOPEN then
1131 CLOSE GetInstrAssgnID;
1132 END IF;
1133 IF GetAcctInfo%ISOPEN then
1134 CLOSE GetAcctInfo;
1135 END IF;
1136 IF GetCustAcctParty%ISOPEN then
1137 CLOSE GetCustAcctParty;
1138 END IF;
1139 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, substr(SQLERRM,1,240));
1140 RAISE;
1141
1142 End create_transaction_extension;
1143
1144
1145
1146 -------
1147 END; -- Package Body OKS_UTIL_PUB