[Home] [Help]
PACKAGE BODY: APPS.OM_SETUP_VALID_PKG
Source
1 PACKAGE BODY OM_SETUP_VALID_PKG AS
2 -- $Header: OEXRSTVB.pls 115.27 2004/05/06 05:34:02 rmoharan ship $
3
4 -- Added for bug 3310908 - Start
5 RESP_LIST SARRAY := SARRAY();
6 USER_LIST SARRAY := SARRAY();
7 APPS_LIST SARRAY := SARRAY();
8 -- Added for bug 3310908 - End
9
10 PROCEDURE VALIDATE_MAIN (
11 X_RETCODE OUT NOCOPY BOOLEAN,
12 P_LEVEL IN VARCHAR2,
13 P_VALUE IN VARCHAR2
14 ) IS
15 flags CHECK_FLAG;
16 value VARCHAR2(5):='FALSE';
17 errLogged BOOLEAN := FALSE;
18 entity VARCHAR2(100) := 'System';
19 error_type VARCHAR2(15) := 'ERROR';
20 mesg_name VARCHAR2(255):= 'ONT_SETVAL_INVALID_INPUT';
21 ou NUMBER;
22 BEGIN
23 OE_DEBUG_PUB.ADD('Inside OEXRSTVB : Validate_Main');
24 RESP_LIST := GENERATE_LIST_RESP( P_LEVEL, P_VALUE );
25 IF ( P_LEVEL = 'RESP' AND (RESP_LIST(1) is null or RESP_LIST(1) = 0) ) THEN
26 ou := getOperatingUnit( P_VALUE );
27 errLogged := writeError( entity, P_LEVEL, P_VALUE, error_type, mesg_name ,
28 null, null, null, null, null, null, null, null,
29 null, null, null);
30 X_RETCODE := TRUE;
31 RETURN;
32 END IF;
33 OE_DEBUG_PUB.ADD('Validate_Main : Retrieved_Resp_List');
34 OE_DEBUG_PUB.ADD('Resp_List count :'||RESP_lIST.COUNT);
35 APPS_LIST := GENERATE_LIST_APPS;
36 OE_DEBUG_PUB.ADD('Validate_Main : Retrieved_Apps_List');
37 OE_DEBUG_PUB.ADD('Apps_List count :'||APPS_lIST.COUNT);
38 USER_LIST := GENERATE_LIST_USER( P_LEVEL, P_VALUE );
39 OE_DEBUG_PUB.ADD('Validate_Main : Retrieved_Use_List');
40 OE_DEBUG_PUB.ADD('User_List count :'||USER_lIST.COUNT);
41 flags := CHECK_FLAG (
42 value, value, value, value, value,
43 value, value, value, value, value,
44 value, value, value, value, value,
45 value, value
46 );
47 IF ( P_LEVEL = 'USER' ) THEN
48
49 flags(1) := 'TRUE';
50 flags(2) := 'TRUE';
51 flags(17) := 'TRUE';
52 ELSIF ( (P_LEVEL = 'RESP' OR P_LEVEL = 'OU') ) THEN
53 flags(1) := 'TRUE';
54 flags(2) := 'TRUE';
55 flags(3) := 'TRUE';
56 flags(4) := 'TRUE';
57 flags(7) := 'TRUE';
58 flags(8) := 'TRUE';
59 flags(9) := 'TRUE';
60 flags(10) := 'TRUE';
61 flags(11) := 'TRUE';
62 flags(12) := 'TRUE';
63 flags(13) := 'TRUE';
64 flags(14) := 'TRUE';
65 flags(15) := 'TRUE';
66 flags(16) := 'TRUE';
67 ELSIF ( P_LEVEL = 'INST') THEN
68 value := 'TRUE';
69 flags := CHECK_FLAG (
70 value, value, value, value, value,
71 value, value, value, value, value,
72 value, value, value, value, value,
73 value, value, value
74 );
75
76 END IF;
77 IF ( flags(1) = 'TRUE' ) THEN
78 VALIDATE_PROFILE_OPTIONS( p_level, p_value );
79 END IF;
80 IF ( flags(2) = 'TRUE' ) THEN
81 VALIDATE_USER_PROFILE_OPTIONS( p_level, p_value );
82 END IF;
83 IF ( flags(3) = 'TRUE' ) THEN
84 VALIDATE_SET_OF_BOOKS_SETUP( p_level, p_value );
85 END IF;
86 IF ( flags(4) = 'TRUE' ) THEN
87 VALIDATE_ITEM_VALID_ORG( p_level, p_value );
88 END IF;
89 IF ( flags(5) = 'TRUE' ) THEN
90 VALIDATE_SALES_ORDER_KEYFLEX( p_level, p_value );
91 END IF;
92 IF ( flags(6) = 'TRUE' ) THEN
93 VALIDATE_ITEM_CATALOGS_FLEX( p_level, p_value );
94 END IF;
95 IF ( flags(7) = 'TRUE' ) THEN
96 VALIDATE_TRANSACTION_TYPES( p_level, p_value );
97 END IF;
98 IF ( flags(8) = 'TRUE' ) THEN
99 VALIDATE_DOC_SEQ_SALES_ORDERS( p_level, p_value );
100 END IF;
101 IF ( flags(9) = 'TRUE' ) THEN
102 VALIDATE_CREDIT_CHECKING( p_level, p_value );
103 END IF;
104 IF ( flags(10) = 'TRUE' ) THEN
105 VALIDATE_ITEM_DEFINITION( p_level, p_value );
106 END IF;
107 IF ( flags(11) = 'TRUE' ) THEN
108 VALIDATE_PRICE_LIST_DEFINITION( p_level, p_value );
109 END IF;
110 IF ( flags(12) = 'TRUE' ) THEN
111 VALIDATE_SALES_CRDT_DEFINITION( p_level, p_value );
112 END IF;
113 IF ( flags(13) = 'TRUE' ) THEN
114 VALIDATE_SHIPPING_ORGS( p_level, p_value );
115 END IF;
116 IF ( flags(14) = 'TRUE' ) THEN
117 VALIDATE_PERIOD_STATUS( p_level, p_value );
118 END IF;
119 IF ( flags(15) = 'TRUE' ) THEN
120 VALIDATE_FREIGHT_CARRIER( p_level, p_value );
121 END IF;
122 IF ( flags(16) = 'TRUE' ) THEN
123 VALIDATE_DOC_SEQ_SHIPPING( p_level, p_value );
124 END IF;
125 IF ( flags(17) = 'TRUE' ) THEN
126 VALIDATE_SHIPPING_GRANTS_ROLES( p_level, p_value );
127 END IF;
128 X_RETCODE := TRUE;
129 EXCEPTION
130 WHEN OTHERS THEN
131 X_RETCODE := FALSE;
132 RETURN;
133 END VALIDATE_MAIN;
134 /***********************************************************************************************/
135 /** getOperatingUnit ***************************************************************************/
136 /***********************************************************************************************/
137 FUNCTION getOperatingUnit ( resp_id NUMBER ) RETURN NUMBER AS
138 oper_unit NUMBER;
139 prof_opt_id NUMBER;
140
141 prof_opt_val VARCHAR2(255);
142 BEGIN
143 prof_opt_id := getProfileOptionId('ORG_ID');
144 IF ( prof_opt_id = null ) THEN
145 RETURN -1;
146 ELSE
147 prof_opt_val := getProfileOptionValue( 10003, resp_id, prof_opt_id );
148
149 END IF;
150 IF ( prof_opt_val = null ) THEN
151 RETURN -1;
152 ELSE
153 oper_unit := to_number(prof_opt_val);
154 END IF;
155
156 RETURN oper_unit;
157 END getOperatingUnit;
158 /***********************************************************************************************/
159 /** getProfileOptionId *************************************************************************/
160 /***********************************************************************************************/
161 FUNCTION getProfileOptionId ( prof_opt_name VARCHAR2 ) RETURN NUMBER AS
162 prof_opt_id NUMBER;
163 BEGIN
164 SELECT FPO.PROFILE_OPTION_ID
165 INTO prof_opt_id
166 FROM FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTIONS_TL FPOT
167 WHERE FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
168 AND FPOT.LANGUAGE=USERENV('LANG')
169 AND FPOT.PROFILE_OPTION_NAME LIKE prof_opt_name;
170 RETURN prof_opt_id;
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 RETURN -1;
174 END getProfileOptionId;
175 /***********************************************************************************************/
176 /** getProfOptName *************************************************************************/
177 /***********************************************************************************************/
178 FUNCTION getProfOptName ( prof_opt_name VARCHAR2 ) RETURN VARCHAR2 AS
179 user_prof_opt_name VARCHAR2(255);
180 BEGIN
181 SELECT FPOT.USER_PROFILE_OPTION_NAME
182 INTO user_prof_opt_name
183 FROM FND_PROFILE_OPTIONS_TL FPOT
184 WHERE FPOT.LANGUAGE=USERENV('LANG')
185 AND FPOT.PROFILE_OPTION_NAME LIKE prof_opt_name;
186 RETURN user_prof_opt_name;
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 RETURN -1;
190 END getProfOptName;
191 /***********************************************************************************************/
192 /** getProfileOptionValue **********************************************************************/
193 /***********************************************************************************************/
194 FUNCTION getProfileOptionValue (
195 lvl_id VARCHAR2,
196 lvl_value VARCHAR2,
197 prof_opt_id NUMBER
198 ) RETURN VARCHAR2 AS
199 prof_opt_val VARCHAR2(255);
200 BEGIN
201 IF (lvl_id = '10001') THEN
202 SELECT PROFILE_OPTION_VALUE
203 INTO prof_opt_val
204 FROM FND_PROFILE_OPTION_VALUES
205 WHERE PROFILE_OPTION_ID = prof_opt_id
206 AND LEVEL_ID = lvl_id
207 AND ROWNUM < 2;
208 ELSE
209 SELECT PROFILE_OPTION_VALUE
210 INTO prof_opt_val
211 FROM FND_PROFILE_OPTION_VALUES
212 WHERE PROFILE_OPTION_ID = prof_opt_id
213 AND LEVEL_ID = lvl_id
214 AND LEVEL_VALUE = lvl_value
215 AND ROWNUM < 2;
216 END IF;
217 RETURN prof_opt_val;
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 RETURN -1;
221 WHEN OTHERS THEN
222 RETURN -1;
223 END getProfileOptionValue;
224 /***********************************************************************************************/
225 /** getRespForUser *****************************************************************************/
226 /***********************************************************************************************/
227 FUNCTION getRespForUser ( user_id NUMBER ) RETURN SARRAY AS
228 resp_id NUMBER;
229 resps SARRAY:= SARRAY();
230 CURSOR resp_cursor IS
231 SELECT USER_ID, RESPONSIBILITY_ID
232 FROM FND_USER_RESP_GROUPS;
233 resp resp_cursor%ROWTYPE;
234 indx NUMBER;
235 BEGIN
236 indx := 1;
237 resps.extend(1000);
238 OPEN resp_cursor;
239 LOOP
240 FETCH resp_cursor INTO resp;
241 EXIT WHEN resp_cursor%NOTFOUND;
242 IF resp.USER_ID = user_id THEN
243 resps(indx) := resp.RESPONSIBILITY_ID;
244 indx := indx + 1;
245 IF ( mod(indx,1000) = 999 ) THEN
246 resps.extend(1000);
247 END IF;
248 END IF;
249 END LOOP;
250 resps(indx) := -1;
251 RETURN resps;
252 END getRespForUser;
253 /***********************************************************************************************/
254 /** getResps ***********************************************************************************/
255 /***********************************************************************************************/
256 FUNCTION getResps RETURN NUMARRAY AS
257 prof_opt_val VARCHAR2(255);
258 resps NUMARRAY:= NUMARRAY();
259 CURSOR resp_cursor IS
260 SELECT RESPONSIBILITY_ID
261 FROM FND_RESPONSIBILITY;
262 resp resp_cursor%ROWTYPE;
263 indx NUMBER;
264 BEGIN
265 indx := 1;
266 resps.extend(1000);
267 OPEN resp_cursor;
268 LOOP
269 FETCH resp_cursor INTO resp;
270 EXIT WHEN resp_cursor%NOTFOUND;
271 resps(indx) := resp.RESPONSIBILITY_ID;
272 indx := indx + 1;
273 IF ( mod(indx,1000) = 999 ) THEN
274 resps.extend(1000);
275 END IF;
276 END LOOP;
277 resps(indx) := -1;
278 RETURN resps;
279 END getResps;
280 /***********************************************************************************************/
281 /** getUsers ***********************************************************************************/
282 /***********************************************************************************************/
283 --NOT Being Used anymore
284 /* FUNCTION getUsers RETURN NUMARRAY AS
285 users NUMARRAY:= NUMARRAY();
286 CURSOR usr_cursor IS
287 SELECT USER_ID
288 FROM FND_USER;
289 usr usr_cursor%ROWTYPE;
290 indx NUMBER;
291 BEGIN
292 indx := 1;
293 users.extend(1000);
294 OPEN usr_cursor;
295 LOOP
296 FETCH usr_cursor INTO usr;
297 EXIT WHEN usr_cursor%NOTFOUND;
298 users(indx) := usr.USER_ID;
299 indx := indx + 1;
300 IF ( mod(indx,1000) = 999 ) THEN
301 users.extend(1000);
302 END IF;
303 END LOOP;
304 users(indx) := -1;
305 RETURN users;
306 END getUsers;
307 */
308
309
310 /***********************************************************************************************/
311 /** getUserName ********************************************************************************/
312 /***********************************************************************************************/
313 FUNCTION getUserName ( userid NUMBER ) RETURN VARCHAR2 AS
314 username VARCHAR2(255);
315 BEGIN
316 SELECT USER_NAME
317 INTO username
318 FROM FND_USER
319 WHERE USER_ID = userid;
320 RETURN username;
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 RETURN -1;
324 END;
325 /***********************************************************************************************/
326 /** getUserName ********************************************************************************/
327 /***********************************************************************************************/
328 FUNCTION getOuName ( ouid NUMBER ) RETURN VARCHAR2 AS
329 ouname VARCHAR2(255);
330 BEGIN
331 SELECT NAME
332 INTO ouname
333 FROM HR_ALL_ORGANIZATION_UNITS_TL
334 WHERE ORGANIZATION_ID = ouid
335 AND LANGUAGE = USERENV('LANG');
336 RETURN ouname;
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 RETURN -1;
340 END;
341
342 /***********************************************************************************************/
343 /** getTtypeName *******************************************************************************/
344 /***********************************************************************************************/
345 FUNCTION getTtypeName ( ttypeid NUMBER ) RETURN VARCHAR2 AS
346 ttypename VARCHAR2(255);
347 BEGIN
348 SELECT NAME
349 INTO ttypename
350 FROM OE_TRANSACTION_TYPES_TL
351 WHERE TRANSACTION_TYPE_ID = ttypeid
352 AND LANGUAGE=USERENV('LANG');
353 RETURN ttypename;
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 RETURN -1;
357 END;
358 /***********************************************************************************************/
359 /** getRespName ********************************************************************************/
360 /***********************************************************************************************/
361 FUNCTION getRespName ( resp_id NUMBER ) RETURN VARCHAR2 AS
362 resp_name VARCHAR2(255);
363 BEGIN
364 BEGIN
365 SELECT RESPONSIBILITY_NAME
366 INTO resp_name
367 FROM FND_RESPONSIBILITY_TL
368 WHERE RESPONSIBILITY_ID = resp_id
369 AND LANGUAGE=USERENV('LANG');
370 RETURN resp_name;
371 EXCEPTION
372 WHEN OTHERS THEN
373 RETURN -1;
374 END;
375 END getRespName;
376 /***********************************************************************************************/
377 /** getAppsName ********************************************************************************/
378 /***********************************************************************************************/
379 FUNCTION getAppsName ( appl_id NUMBER ) RETURN VARCHAR2 AS
380 appl_name VARCHAR2(255);
381 BEGIN
382 BEGIN
383 SELECT APPLICATION_NAME
384 INTO appl_name
385 FROM FND_APPLICATION_TL
386 WHERE APPLICATION_ID = appl_id
387 AND LANGUAGE=USERENV('LANG');
388 RETURN appl_name;
389 EXCEPTION
390 WHEN OTHERS THEN
391 RETURN -1;
392 END;
393 END getAppsName;
394 /***********************************************************************************************/
395 /** getShippingOrgsForOu ***********************************************************************/
396 /***********************************************************************************************/
397 FUNCTION getShippingOrgsForOu(ou NUMBER) RETURN SARRAY AS
398 /* Array Variables */
399 shiporgs SARRAY := SARRAY();
400 /* Counter Variables */
401 ind NUMBER := 1;
402 /* Cursor Variables */
403 CURSOR shorgs(ou NUMBER) IS
404 SELECT ORGANIZATION_ID
405 FROM WSH_SHIPPING_PARAMETERS
406 WHERE ORGANIZATION_ID IN (
407 SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID
408 FROM HR_ORGANIZATION_UNITS HOU,
409 HR_ORGANIZATION_INFORMATION HOI,
410 FND_PRODUCT_GROUPS FPG
411 WHERE HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
412 AND DECODE ( FPG.MULTI_ORG_FLAG, 'Y',
413 DECODE (HOI.ORG_INFORMATION_CONTEXT, 'Accounting Information',
414 TO_NUMBER(HOI.ORG_INFORMATION3), TO_NUMBER(NULL)
415 ), TO_NUMBER(NULL)
416 ) = ou
417 );
418 shorg shorgs%ROWTYPE;
419 BEGIN
420 shiporgs.extend(100000);
421 OPEN shorgs(ou);
422 FETCH shorgs INTO shorg;
423 LOOP
424 shiporgs(ind) := shorg.ORGANIZATION_ID;
425 ind := ind + 1;
426 IF ( mod(ind,1000) = 999 ) THEN
427 shiporgs.extend(1000);
428 END IF;
429 FETCH shorgs INTO shorg;
430 EXIT WHEN shorgs%NOTFOUND;
431
432 END LOOP;
433 RETURN trimArray(shiporgs);
434 END getShippingOrgsForOu;
435 /***********************************************************************************************/
436 /** getRespListForForm ************************************************************************/
437 /***********************************************************************************************/
438 FUNCTION getRespListForForm
439 (
440 p_form_name VARCHAR2
441 , p_description VARCHAR2
442 , p_application VARCHAR2
443 )
444 RETURN SARRAY
445 AS
446 l_function_list SARRAY := SARRAY();
447 l_function_id NUMBER;
448 l_menu_list SARRAY := SARRAY();
449 l_resp_list SARRAY := SARRAY();
450 l_temp_list SARRAY := SARRAY();
451 l_ctr_j NUMBER := 1;
452 l_index NUMBER := 1;
453 l_form_name VARCHAR2(100);
454 l_description VARCHAR2(100);
455 l_application VARCHAR2(10);
456 l_isdouble BOOLEAN;
457 BEGIN
458 l_form_name := p_form_name;
459 l_description := p_description;
460 l_application := p_application;
461 l_function_list := getFormFunctionId(l_form_name,l_application,l_description);
462 OE_DEBUG_PUB.ADD('Inside Get_Resp_List_For_Form : fn list count :');
463 OE_DEBUG_PUB.ADD(l_function_list.COUNT);
464 FOR k IN 1..l_function_list.COUNT
465 LOOP
466 l_function_id := l_function_list(k);
467 l_menu_list := getAllMenus(l_function_id);
468 l_temp_list := getRespListFromMenus(l_menu_list);
469 l_resp_list.extend(l_temp_list.COUNT);
470 FOR i IN 1..l_temp_list.COUNT
471 LOOP
472 l_isdouble:=FALSE;
473 l_ctr_j:=1;
474 WHILE (l_ctr_j < i)
475 LOOP
476 IF (l_temp_list(i) = l_temp_list(l_ctr_j)) THEN
477 l_isdouble:=TRUE;
478 EXIT;
479 END IF;
480 l_ctr_j:=l_ctr_j+1;
481 END LOOP;
482 IF (l_isdouble=FALSE) THEN
483 l_resp_list(l_index):=l_temp_list(i);
484 l_index:=l_index+1;
485 END IF;
486 END LOOP;
487 END LOOP;
488 RETURN l_resp_list;
489 END getRespListForForm;
490 /***********************************************************************************************/
491 /** getRespListFromMenus **********************************************************************/
492 /***********************************************************************************************/
493 FUNCTION getRespListFromMenus ( menus SARRAY ) RETURN SARRAY AS
494 CURSOR resp_get IS
495 SELECT MENU_ID, RESPONSIBILITY_ID
496 FROM FND_RESPONSIBILITY;
497 rget resp_get%ROWTYPE;
498 ind NUMBER := 1;
499 counter NUMBER := 1;
500 ctr NUMBER := 1;
501 resps SARRAY := SARRAY();
502 menu_id NUMBER := 0;
503 BEGIN
504 resps.extend(1000);
505 FOR i IN 1..menus.COUNT
506 LOOP
507 IF ( menus(i) IS NULL ) THEN
508 EXIT;
509 END IF;
510 ctr := ctr + 1;
511 END LOOP;
512 FOR i IN 1..ctr
513 LOOP
514 SELECT to_number(menus(i))
515 INTO menu_id
516 FROM DUAL;
517 OPEN resp_get;
518 FETCH resp_get INTO rget;
519 LOOP
520 IF ( rget.MENU_ID = menu_id ) THEN
521 resps(ind) := rget.RESPONSIBILITY_ID;
522 ind := ind + 1;
523 IF ( MOD(ind,1000) = 999 ) THEN
524 resps.extend(1000);
525 END IF;
526 END IF;
527 FETCH resp_get INTO rget;
528 EXIT WHEN resp_get%NOTFOUND;
529 END LOOP;
530 CLOSE resp_get;
531 END LOOP;
532 FOR i IN 1..resps.COUNT
533 LOOP
534 IF resps(i) IS NULL THEN
535 EXIT;
536 END IF;
537 counter := counter + 1;
538 END LOOP;
539 RETURN( trimArray( resps ) );
540 END getRespListFromMenus;
541 /***********************************************************************************************/
542 /** getSubMenus *******************************************************************************/
543 /***********************************************************************************************/
544 FUNCTION getSubMenus( menu_id NUMBER ) RETURN SARRAY AS
545 CURSOR sub_menus( submenu_id IN NUMBER ) IS
546 SELECT menu_id
547 FROM FND_MENU_ENTRIES
548 WHERE SUB_MENU_ID = submenu_id;
549 smenus sub_menus%ROWTYPE;
550 menus SARRAY := SARRAY();
551 ind NUMBER:=1;
552 BEGIN
553 OPEN sub_menus( menu_id );
554 FETCH sub_menus INTO smenus;
555 menus.extend(1000);
556 LOOP
557 menus(ind) := smenus.menu_id;
558 ind := ind + 1;
559 IF ( MOD(ind,1000) = 999 ) THEN
560 menus.extend(1000);
561 END IF;
562 FETCH sub_menus INTO smenus;
563 EXIT WHEN sub_menus%NOTFOUND;
564 END LOOP;
565 menus := trimArray( menus );
566 RETURN menus;
567 END getSubMenus;
568 /***********************************************************************************************/
569 /** getUniqueList *****************************************************************************/
570 /***********************************************************************************************/
571 FUNCTION getUniqueList( list SARRAY ) RETURN SARRAY AS
572 unique_list SARRAY := SARRAY();
573 uctr NUMBER := 1;
574 isdouble BOOLEAN := FALSE;
575 BEGIN
576 unique_list.extend(list.COUNT);
577 FOR j IN 1..list.COUNT
578 LOOP
579 FOR i IN 1..j
580 LOOP
581 IF ( list(j) = unique_list(i) ) THEN
582 isdouble := TRUE;
583 END IF;
584 END LOOP;
585 IF isdouble = FALSE THEN
586 unique_list(uctr) := list(j);
587 uctr := uctr + 1;
588 END IF;
589 isdouble := FALSE;
590 END LOOP;
591 RETURN( trimArray(unique_list) );
592 END getUniqueList;
593 /***********************************************************************************************/
594 /** isNumeric *********************************************************************************/
595 /***********************************************************************************************/
596 FUNCTION isNumeric ( myStr VARCHAR2 ) RETURN BOOLEAN AS
597 myNbr NUMBER;
598 BEGIN
599 myNbr := to_number(myStr);
600 RETURN TRUE;
601 EXCEPTION
602 WHEN OTHERS THEN
603 IF ( SQLCODE = '-6502' ) THEN
604 RETURN FALSE;
605 END IF;
606 END isNumeric;
607 /***********************************************************************************************/
608 /** trimArray *********************************************************************************/
609 /***********************************************************************************************/
610 FUNCTION trimArray (
611 in_array SARRAY
612 ) RETURN SARRAY AS
613 out_array SARRAY := SARRAY();
614 out_ctr NUMBER := 0;
615 out_idx NUMBER := 1;
616 BEGIN
617 FOR i IN 1..in_array.COUNT
618 LOOP
619 IF ( in_array(i) IS NOT NULL ) THEN
620 out_ctr := out_ctr + 1;
621 END IF;
622 END LOOP;
623 out_array.extend(out_ctr);
624 FOR i IN 1..in_array.COUNT
625 LOOP
626 IF ( in_array(i) IS NOT NULL ) THEN
627 out_array(out_idx) := in_array(i);
628 out_idx := out_idx + 1;
629 END IF;
630 END LOOP;
631 RETURN out_array;
632 END trimArray;
633 /***********************************************************************************************/
634 /** writeError ********************************************************************************/
635 /***********************************************************************************************/
636 FUNCTION writeError (
637 ENTITY VARCHAR2,
638 P_LEVEL VARCHAR2,
639 P_VALUE VARCHAR2,
640 ERROR_TYPE VARCHAR2,
641 MESG_NAME VARCHAR2,
642 TYPE VARCHAR2,
643 USER_NAME VARCHAR2,
644 OPERATING_UNIT VARCHAR2,
645 ORGANIZATION_ID VARCHAR2,
646 SET_OF_BOOKS_AR VARCHAR2,
647 SET_OF_BOOKS VARCHAR2,
648 SET_OF_BOOKS_PF VARCHAR2,
649 PROFILE_OPTION VARCHAR2,
650 TRANSACT_TYPE VARCHAR2,
651 DOC_CATEGORY VARCHAR2,
652 PAYMENT_TERM VARCHAR2
653 ) RETURN BOOLEAN AS
654 TEMP VARCHAR2(100);
655 P_F_LEVEL VARCHAR2(100);
656 P_F_VALUE VARCHAR2(100);
657 BEGIN
658 IF ( P_LEVEL = 'OU' ) THEN
659 P_F_LEVEL := 'Operating Unit';
660 P_F_VALUE := getOuName(to_number(P_VALUE));
661 ELSIF ( P_LEVEL = 'RESP' ) THEN
662 P_F_LEVEL := 'Responsibility';
663 P_F_VALUE := getRespName(to_number(P_VALUE));
664 ELSIF ( P_LEVEL = 'USER' ) THEN
665 P_F_LEVEL := 'User';
666 P_F_VALUE := getUserName(to_number(P_VALUE));
667 ELSIF ( P_LEVEL = 'INST' ) THEN
668 P_F_LEVEL := 'Instance';
669 END IF;
670 INSERT INTO OM_SETUP_VALID_REP
671 ( ENTITY, P_LEVEL, P_VALUE, ERROR_TYPE,
672 MESG_NAME, TYPE, USER_NAME, OPERATING_UNIT,
673 ORGANIZATION, SET_OF_BOOKS_AR, SET_OF_BOOKS,
674 SET_OF_BOOKS_PF, PROFILE_OPTION, TRANSACT_TYPE,
675 DOC_CATEGORY, PAYMENT_TERM
676 )
677 VALUES
678 ( ENTITY, P_F_LEVEL, P_F_VALUE, ERROR_TYPE,
679 MESG_NAME, TYPE, USER_NAME, OPERATING_UNIT,
680 ORGANIZATION_ID, SET_OF_BOOKS_AR, SET_OF_BOOKS,
681 SET_OF_BOOKS_PF, PROFILE_OPTION, TRANSACT_TYPE,
682 DOC_CATEGORY, PAYMENT_TERM
683 );
684 RETURN TRUE;
685 EXCEPTION
686 WHEN OTHERS THEN
687 RETURN FALSE;
688 END writeError;
689 /***********************************************************************************************/
690 /** addArrayToArr *****************************************************************************/
691 /***********************************************************************************************/
692 FUNCTION addArrayToArr (
693 IN_ARRAY SARRAY,
694 AD_ARRAY SARRAY
695 )RETURN SARRAY AS
696 OUT_ARRAY SARRAY := SARRAY();
697 ARRAY_IN SARRAY := SARRAY();
698 ARRAY_AD SARRAY := SARRAY();
699 COUNT_TOT NUMBER := 0;
700 COUNT_IN NUMBER := 0;
701 COUNT_AD NUMBER := 0;
702 k NUMBER := 0;
703 BEGIN
704 ARRAY_IN := trimArray( IN_ARRAY );
705 ARRAY_AD := trimArray( AD_ARRAY );
706 COUNT_IN := ARRAY_IN.COUNT;
707 COUNT_AD := ARRAY_AD.COUNT;
708 COUNT_TOT := COUNT_IN + COUNT_AD;
709 OUT_ARRAY.extend(COUNT_TOT);
710 FOR i IN 1..COUNT_IN
711 LOOP
712 OUT_ARRAY(i) := IN_ARRAY(i);
713 END LOOP;
714 FOR i IN COUNT_IN+1..COUNT_TOT
715 LOOP
716 k := i-COUNT_IN;
717 OUT_ARRAY(i) := AD_ARRAY(k);
718 END LOOP;
719 RETURN( OUT_ARRAY );
720 END addArrayToArr;
721 /***********************************************************************************************/
722 /** checkForOMResp ****************************************************************************/
723 /***********************************************************************************************/
724 FUNCTION checkForOMResp( user_id NUMBER ) RETURN BOOLEAN AS
725 resp_id NUMBER;
726 BEGIN
727 SELECT RESPONSIBILITY_ID
728 INTO resp_id
729 FROM FND_USER_RESP_GROUPS
730 WHERE USER_ID = user_id
731 AND RESPONSIBILITY_APPLICATION_ID = 660
732 AND ROWNUM < 2;
733 RETURN TRUE;
734 EXCEPTION
735 WHEN NO_DATA_FOUND THEN
736 RETURN FALSE;
737 END checkForOMResp;
738 /***********************************************************************************************/
739 /** checkForOMResp ****************************************************************************/
740 /***********************************************************************************************/
741 FUNCTION checkForOmOu( oper_unit NUMBER ) RETURN BOOLEAN AS
742 orgcount NUMBER;
743 BEGIN
744 -- Sys Param Change
745 -- Table OE_SYSTEM_PARAMETERS_ALL is replace by OE_SYS_PARAMETERS_ALL
746 SELECT COUNT(ORG_ID)
747 INTO orgcount
748 FROM OE_SYS_PARAMETERS_ALL
749 WHERE ORG_ID = oper_unit;
750 IF orgcount = 0 THEN
751 RETURN FALSE;
752 ELSE
753 RETURN TRUE;
754 END IF;
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 RETURN FALSE;
758 END checkForOmOu;
759 /***********************************************************************************************/
760 /** getAllMenus *******************************************************************************/
761 /***********************************************************************************************/
762 FUNCTION getAllMenus( func_id NUMBER ) RETURN SARRAY AS
763 menu_tmp_arr SARRAY := SARRAY();
764 menu_fin_arr SARRAY := SARRAY();
765 menu_sub_arr SARRAY := SARRAY();
766 menu_rec_arr SARRAY := SARRAY();
767 ctr NUMBER := 1;
768 i NUMBER := 1;
769 fin_idx NUMBER := 1;
770 BEGIN
771 menu_tmp_arr.extend(1000);
772 --menu_fin_arr.extend(1000);
773 menu_rec_arr := getMenus(func_id);
774 FOR j IN 1..menu_rec_arr.COUNT
775 LOOP
776 menu_tmp_arr(j) := menu_rec_arr(j);
777 END LOOP;
778 WHILE ( menu_tmp_arr(i) IS NOT NULL )
779 LOOP
780 menu_sub_arr := getSubMenus(menu_tmp_arr(i));
781 IF ( menu_sub_arr.COUNT > 0 ) THEN
782 menu_rec_arr := addArrayToArr( menu_tmp_arr, menu_sub_arr );
783 FOR j IN 1..menu_rec_arr.COUNT
784 LOOP
785 menu_tmp_arr(j) := menu_rec_arr(j);
786 IF ( MOD(j,1000)=999 ) THEN
787 menu_tmp_arr.extend(1000);
788 END IF;
789 END LOOP;
790 /**********************************************************/
791 /* -- Commented for Set of Books Validation Bug.----------
792 ELSE
793 menu_fin_arr(fin_idx) := menu_tmp_arr(i);
794 fin_idx := fin_idx + 1;
795 ---------------------------------------------------------*/
796 /**********************************************************/
797 END IF;
798 i := i + 1;
799 END LOOP;
800 RETURN menu_tmp_arr;
801 END getAllMenus;
802 /***********************************************************************************************/
803 /** GENERATE_LIST_APPS ************************************************************************/
804 /***********************************************************************************************/
805 FUNCTION GENERATE_LIST_APPS RETURN SARRAY AS
806 CURSOR ALL_APPS(resp_id IN NUMBER) IS
807 SELECT APPLICATION_ID
808 FROM FND_RESPONSIBILITY
809 WHERE RESPONSIBILITY_ID=resp_id;
810 ctr NUMBER := 1;
811 j NUMBER := 1;
812 apps_all ALL_APPS%ROWTYPE;
813 --
814 -- Bug 3537496 : Modified by NKILLEDA
815 -- Changed the variable name from 'apps' to l_apps for GSCC
816 -- compliance. the specific rule is file.sql.6.
817 --
818 l_apps SARRAY := SARRAY();
819 isdouble BOOLEAN := FALSE;
820 isFound BOOLEAN := FALSE;
821 TEMP_LIST SARRAY := SARRAY();
822 BEGIN
823 l_apps.extend(1000);
824 FOR i IN 1..RESP_LIST.COUNT
825 LOOP
826 OPEN ALL_APPS(RESP_LIST(i));
827 FETCH ALL_APPS INTO apps_all;
828 LOOP
829 l_apps(ctr) := apps_all.APPLICATION_ID;
830 ctr := ctr + 1;
831 IF ( MOD(ctr,1000)=999 ) THEN
832 l_apps.extend(1000);
833 END IF;
834 FETCH ALL_APPS INTO apps_all;
835 EXIT WHEN ALL_APPS%NOTFOUND;
836 END LOOP;
837 CLOSE ALL_APPS ;
838 END LOOP;
839 RETURN getUniqueList( l_apps );
840 END GENERATE_LIST_APPS;
841 /***********************************************************************************************/
845 P_LEVEL VARCHAR2,
842 /** GENERATE_LIST_RESP ************************************************************************/
843 /***********************************************************************************************/
844 FUNCTION GENERATE_LIST_RESP (
846 P_VALUE VARCHAR2
847 ) RETURN SARRAY AS
848 RET_LIST SARRAY := SARRAY();
849 list_so SARRAY := SARRAY();
850 list_sh SARRAY := SARRAY();
851 list_qp SARRAY := SARRAY();
852 list_ru SARRAY := SARRAY();
853 list_all SARRAY := SARRAY();
854 lvl_id NUMBER ;
855 lvl_value NUMBER ;
856 prof_opt_id NUMBER := 1991;
857 oper_unit VARCHAR2(30);
858 ctr NUMBER := 1;
859 BEGIN
860 OE_DEBUG_PUB.ADD('In OEXRSTVB : Generate_List_Resp function');
861 list_so := getRespListForForm('OEXOEORD', 'Sales Orders', 'ONT');
862 OE_DEBUG_PUB.ADD('Retrieved responsibilities accessing Sales Order form');
863 list_sh := getRespListForForm('WSHFSTRX', null, 'WSH' );
864 OE_DEBUG_PUB.ADD('Retrieved responsibilities accessing Shipping Transactions form');
865 list_qp := getRespListForForm('QPXPRLST', null, 'QP' );
866 OE_DEBUG_PUB.ADD('Retrieved responsibilities accessing Price List form');
867 list_all := addArrayToArr(list_qp, addArrayToArr(list_so, list_sh));
868 IF P_LEVEL = 'INST' THEN
869 RETURN getUniqueList(list_all);
870 END IF;
871 IF P_LEVEL = 'OU' THEN
872 RET_LIST.extend(list_all.COUNT);
873 FOR i IN 1..list_all.COUNT
874 LOOP
875 lvl_id := 10003;
876 lvl_value := list_all(i);
877 oper_unit := getProfileOptionValue( lvl_id, lvl_value,
878 prof_opt_id );
879 IF oper_unit = p_value THEN
880 RET_LIST(ctr) := list_all(i);
881 ctr := ctr + 1;
882 END IF;
883 END LOOP;
884 RETURN getUniqueList(RET_LIST);
885 END IF;
886 IF P_LEVEL = 'RESP' THEN
887 RET_LIST.extend(1);
888 FOR i IN 1..list_all.COUNT
889 LOOP
890 IF list_all(i) = P_VALUE THEN
891 RET_LIST(1) := P_VALUE;
892 END IF;
893 END LOOP;
894 RETURN RET_LIST;
895 END IF;
896 IF P_LEVEL = 'USER' THEN
897 list_ru := getRespForUser( to_number(p_value) );
898 RET_LIST.extend(list_all.COUNT);
899 FOR i IN 1..list_all.COUNT
900 LOOP
901 FOR j IN 1..list_ru.COUNT
902 LOOP
903 IF ( list_ru(j) = list_all(i) ) THEN
904 RET_LIST(ctr) := list_all(i);
905 ctr := ctr + 1;
906 EXIT;
907 END IF;
908 END LOOP;
909 END LOOP;
910 RETURN getUniqueList(RET_LIST);
911 END IF;
912 END GENERATE_LIST_RESP;
913 /***********************************************************************************************/
914 /** GENERATE_LIST_USER ************************************************************************/
915 /***********************************************************************************************/
916 FUNCTION GENERATE_LIST_USER(
917 P_LEVEL VARCHAR2,
918 P_VALUE VARCHAR2
919 ) RETURN SARRAY AS
920 CURSOR ALL_USER(resp_id IN NUMBER) IS
921 SELECT USER_ID
922 FROM FND_USER_RESP_GROUPS
923 WHERE RESPONSIBILITY_ID=resp_id;
924 ctr NUMBER := 1;
925 j NUMBER := 1;
926 user_all ALL_USER%ROWTYPE;
927 users SARRAY := SARRAY();
928 isdouble BOOLEAN := FALSE;
929 isFound BOOLEAN := FALSE;
930 TEMP_LIST SARRAY := SARRAY();
931 BEGIN
932 IF P_LEVEL = 'USER' THEN
933 users.extend(1);
934 users(1) := P_VALUE;
935 RETURN users;
936 END IF;
937 users.extend(1000);
938 FOR i IN 1..RESP_LIST.COUNT
939 LOOP
940 OPEN ALL_USER(RESP_LIST(i));
941 FETCH ALL_USER INTO user_all;
942 LOOP
943 users(ctr) := user_all.USER_ID;
944 ctr := ctr + 1;
945 IF ( MOD(ctr,1000) = 999 ) THEN
946 users.extend(1000);
947 END IF;
948 FETCH ALL_USER INTO user_all;
949 EXIT WHEN ALL_USER%NOTFOUND;
950 END LOOP;
951 CLOSE ALL_USER ;
952 END LOOP;
953 RETURN getUniqueList( users );
954 END GENERATE_LIST_USER;
955 /***********************************************************************************************/
956 /** Initialize Temp Tables ********************************************************************/
957 /***********************************************************************************************/
958 FUNCTION initializeTemp RETURN BOOLEAN IS
959 BEGIN
960 DECLARE
961 BEGIN
962 /*------------------------------------------------------------------------------
963 Following PL/SQL block inserts rows into the om_setup_valid_entities table.
964 ------------------------------------------------------------------------------*/
965
966 BEGIN
967 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
968 VALUES ('Key Flex Field Setup', 'VALIDATE_SALES_ORDER_KEYFLEX', 'N', 'N', 'N', 'Y', 1);
969 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
970 VALUES ('Key Flex Field Setup', 'VALIDATE_ITEM_CATALOGS_FLEX', 'N', 'N', 'N', 'Y', 2);
971 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
972 VALUES ('Shipping Organizations', 'VALIDATE_SHIPPING_ORGS(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 3);
973 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
974 VALUES ('Period Status', 'VALIDATE_PERIOD_STATUS(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 4);
975 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
976 VALUES ('Profile Options', 'VALIDATE_PROFILE_OPTIONS(:lvl, :val)', 'Y', 'Y', 'Y', 'Y', 5);
977 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
978 VALUES ('User Level Profile Options', 'VALIDATE_USER_PROFILE_OPTIONS(:lvl, :val)', 'Y', 'Y', 'Y', 'Y', 6);
979 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
980 VALUES ('Item Validation Organization', 'VALIDATE_ITEM_VALID_ORG(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 7);
981 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
982 VALUES ('Document Sequences For Sales Orders', 'VALIDATE_DOC_SEQ_SALES_ORDERS(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 8);
983 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
984 VALUES ('Document Sequences for Shipping Documents', 'VALIDATE_DOC_SEQ_SHIPPING(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 9);
985 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
986 VALUES ('Item Definition', 'VALIDATE_ITEM_DEFINITION(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 10);
987 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
988 VALUES ('Price List Definition', 'VALIDATE_PRICE_LIST_DEFINITION(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 11);
989 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
990 VALUES ('Transaction Type', 'VALIDATE_TRANSACTION_TYPES(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 12);
991 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
992 VALUES ('Credit Checking Setup', 'VALIDATE_CREDIT_CHECKING(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 13);
993 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
994 VALUES ('Sales Credit', 'VALIDATE_SALES_CRDT_DEFINITION(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 14);
995 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
996 VALUES ('Freight Carrier', 'VALIDATE_FREIGHT_CARRIER(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 15);
997 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
998 VALUES ('Set of Books', 'VALIDATE_SET_OF_BOOKS_SETUP(:lvl, :val)', 'N', 'Y', 'Y', 'Y', 16);
999 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
1000 VALUES ('Shipping Grants and Roles', 'VALIDATE_SHIPPING_GRANTS_ROLES(:lvl, :val)', 'Y', 'N', 'N', 'Y', 17);
1001 INSERT INTO OM_SETUP_VALID_ENTITIES(ENTITY_NAME, PROCEDURE_NAME, USER_FLAG, RESPONSIBILITY_FLAG, OPERATING_UNIT_FLAG, INSTANCE_FLAG, SEQUENCE_NUM)
1002 VALUES ('System', 'None', 'N', 'Y', 'N', 'N', 18);
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 begin
1006 RETURN(FALSE);
1007 end;
1008 END;
1009 /*------------------------------------------------------------------------------
1010 Following PL/SQL block inserts rows into the om_setup_valid_errm table.
1011 ------------------------------------------------------------------------------*/
1012 BEGIN
1013 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1014 VALUES('ONT_SETVAL_INVALID_PROF_OPT', 'The following profile options have not been defined at any level.');
1015 /*
1016 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1017 VALUES('ONT_SETVAL_INVALID_USER_PROF_OPT', 'The following profile options should not have a value for any user that has access to an Order Management responsibility.');
1018 */
1019 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1020 VALUES('ONT_SETVAL_INVALID_USR_PRF_OPT', 'The following profile options should not have a value for any user that has access to an Order Management responsibility.');
1021 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1022 VALUES('ONT_SETVAL_INVALID_SOB', 'Set of books do not match between AR system option and GL Set of Books name profile option.');
1023 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1024 VALUES('ONT_SETVAL_INVALID_MASTER_ORG', 'Item Validation Organizations do not match between OM Parameters and QP: Item Validation Organization profile option.');
1025 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1026 VALUES('ONT_SETVAL_INVALID_SO_FLEX', 'Sales Order Key Flex Field is not included required 3 segments or not enabled or not allowed dynamic insert.');
1027 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1028 VALUES('ONT_SETVAL_INVALID_ITMCAT_FLEX', 'Item Catalogs Key Flex Field is not enabled or not frozen.');
1029 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1030 VALUES('ONT_SETVAL_NOTFOUND_DOC_SEQ ', 'There are no document sequences defined for Oracle Order Management.');
1031 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1032 VALUES('ONT_SETVAL_NOTFOUND_DOC_CAT', 'A document category with the same name as the transaction type does not exist.');
1033 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1034 VALUES('ONT_SETVAL_INVALID_DOC_SEQ', 'There is not a document sequence assignment in the set of book corresponding to each operating unit with OM parameters defined for Oracle Order Management.');
1035 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1036 VALUES('ONT_SETVAL_INACTIVE_DOC_SEQ', 'The following document category should be actively assigned, in the set of books corresponding to the operating unit of the transaction type, to an active sequence.');
1037 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1038 VALUES('ONT_SETVAL_CC_NOTFOUND', 'No credit check rules exist.');
1039 /*
1040 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1041 VALUES('ONT_SETVAL_CC_INVALID_TRAN_TYPE', 'Credit check rules are not enabled in the following transaction type.');
1042 */
1043 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1044 VALUES('ONT_SETVAL_CC_INVALID_TRAN_TYP', 'Credit check rules are not enabled in the following transaction type.');
1045 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1046 VALUES('ONT_SETVAL_CC_INVALID_PAY_TERM', 'There is no payment term that is enabled for credit checking.');
1047 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1048 VALUES('ONT_SETVAL_CC_INVALID_CUST', 'There is no customer or the customer site that is enabled for credit checking.');
1049 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1050 VALUES('ONT_SETVAL_CC_PRE_CALC_EXPO', 'The Credit Checking Initialize process is not scheduled and there is at least one credit check rule using pre-calculated Exposure.');
1051 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1052 VALUES('ONT_SETVAL_INVALID_ITEM', 'No item exists in the following inventory organizations with OE Transactable, Customer Ordered, Customer Order Enabled, Returnable, Internal Ordered, Internal Orders Enabled, OE Transactable enabled');
1053 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1054 VALUES('ONT_SETVAL_INVALID_PRC_LST', 'No active price list exists with items assigned to it.');
1055 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1056 VALUES('ONT_SETVAL_INVALID_SLS_CRDT', 'No sales credit type exists for both quota and non-quota types.');
1057 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1058 VALUES('ONT_SETVAL_INVALID_SHIP_ORG', 'Shipping parameters have not been defined in any inventory organizations in the following shipping operating units.');
1059 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1060 VALUES('ONT_SETVAL_INVALID_DFLT_SUBINV', 'A default staging subinventory has not been defined in the following shipping inventory organizations.');
1061 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1062 VALUES('ONT_SETVAL_INVALID_SUBINV_ORGN', 'Subinventories are not defined in the following shipping inventory organizations.');
1063 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1064 VALUES('ONT_SETVAL_INVALID_SHIP_OMORG', 'Shipping parameters have not been defined in the following inventory organizations.');
1065 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1066 VALUES('ONT_SETVAL_INVALID_PICK_RULES', 'No picking rule exists in the following shipping inventory organizations.');
1067 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1068 VALUES('ONT_SETVAL_NO_OPEN_PERIOD', 'No open period exists in the following inventory organizations.');
1069 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1070 VALUES('ONT_SETVAL_INVALID_CURR_PERIOD', 'The current period is not opened in the following inventory organizations.');
1071 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1072 VALUES('ONT_SETVAL_FC_INACTIVE_ALL', 'No active carrier exists.');
1073 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1074 VALUES('ONT_SETVAL_FC_INACTIVE_ORG', 'No active carrier exists in any inventory organizations under the following Operating Unit.');
1075 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1076 VALUES('ONT_SETVAL_FC_RELATIONS', 'No carrier / ship method relationships exist in any inventory organizations under the following Operating Unit.');
1077 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1078 VALUES('ONT_SETVAL_SHIP_DOC_SEQ', 'There are no document sequences defined for Oracle Shipping.');
1079 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1080 VALUES('ONT_SETVAL_SHIP_DOC_BOL', 'There are no document categories defined for BOL.');
1081 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1082 VALUES('ONT_SETVAL_SHIP_DOC_PKSLP', 'There are no document sequence categories defined for Pack Slip.');
1083 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1084 VALUES('ONT_SETVAL_SHIP_DOC_SEQ_ASSGN', 'There is not a document sequence assignment in the set of book corresponding to each operating unit with OM parameters defined for both BOL and Pack Slip.');
1085 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1086 VALUES('ONT_SETVAL_NOTFOUND_ROLES', 'No shipping role are defined. ');
1087 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1088 VALUES('ONT_SETVAL_NOTFOUND_ROLES_USER', 'No shipping role is assigned to this user. ');
1089 /*
1090 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1091 VALUES('ONT_SETVAL_NOTFOUND_ROLES_USERS', 'No shipping role is assigned to any existing user. ');
1092 */
1093 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1094 VALUES('ONT_SETVAL_NOTFOUND_ROLE_USERS', 'No shipping role is assigned to any existing user. ');
1095 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1096 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_1', 'No transaction type is defined in the following operating units.');
1097 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1098 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_2', 'No Order workflow assignment exists for any the following transaction types in the following operating units.');
1099 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1100 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_3', 'No line workflow exists for any the following transaction types in the following operating units.');
1101 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1102 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_4', 'No credit checking for booking or shipping either Ordering, Packing, Picking or Shipping for any the following transaction types exists in the following operating units.');
1103 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1104 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_5', 'Schduling level is not set for the following transaction types in the following operating units.');
1105 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1106 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_6', 'COGS account is not set for the following transaction types in the following operating units.');
1107 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1108 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_7', 'Invoice source and Non-Delivery invoice source are not set for the following transaction types in the following operating units.');
1109 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1110 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_8', 'Receivables Transaction Type is not set in the following operating units.');
1111 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1112 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_9', 'No default order line type exists for the following transaction types in the following operating units.');
1113 /*
1114 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1115 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE_10', 'No default return line type exists for the following transaction types in the following operating units.');
1116 */
1117 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1118 VALUES('ONT_SETVAL_INVALID_TRAN_TYPE10', 'No default return line type exists for the following transaction types in the following operating units.');
1119 INSERT INTO OM_SETUP_VALID_ERRM(MESG_NAME, DESCRIPTION)
1120 VALUES('ONT_SETVAL_INVALID_INPUT', 'The selected responsibility cannot be validated for OM Setup as it cannot access the Sales Orders, Shipping Transactions and Price Lists forms.');
1121
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 begin
1125 RETURN(FALSE);
1126 end;
1127 END;
1128 /*------------------------------------------------------------------------------
1129 Following PL/SQL block inserts rows into the OM_SETUP_VALID_PROF_OPT table.
1130 ------------------------------------------------------------------------------*/
1131 BEGIN
1132 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1133 VALUES('AR_USE_INV_ACCT_FOR_CM_FLAG', 'INST', 660 );
1134 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1135 VALUES('CZ_UIMGR_URL', 'INST', 660 );
1136 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1137 VALUES('ONT_ADMINISTER_PUBLIC_QUERIES', 'INST', 660 );
1138 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1139 VALUES('OE_CREDIT_TRANSACTION_TYPE_ID', 'INST', 660 );
1140 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1141 VALUES('WSH_CR_SREP_FOR_FREIGHT', 'INST', 660 );
1142 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1143 VALUES('OE_CUST_ITEM_SHOW_MATCHES', 'INST', 660 );
1144 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1145 VALUES('ONT_EST_AUTH_VALID_DAYS', 'INST', 660 );
1146 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1147 VALUES('ONT_INCLUDED_ITEM_FREEZE_METHOD', 'INST', 660 );
1148 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1149 VALUES('WSH_INVOICE_NUMBERING_METHOD', 'INST', 660 );
1150 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1151 VALUES('OE_INVOICE_SOURCE', 'INST', 660 );
1152 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1153 VALUES('OE_INVOICE_TRANSACTION_TYPE_ID', 'INST', 660 );
1154 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1155 VALUES('OE_ID_FLEX_CODE', 'INST', 660 );
1156 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1157 VALUES('OE_NON_DELIVERY_INVOICE_SOURCE', 'INST', 660 );
1158 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1159 VALUES('OM_OVER_RETURN_TOLERANCE', 'INST', 660 );
1160 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1161 VALUES('OE_OVERSHIP_INVOICE_BASIS', 'INST', 660 );
1162 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1163 VALUES('OM_OVER_SHIPMENT_TOLERANCE', 'INST', 660 );
1164 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1165 VALUES('ONT_RETURN_ITEM_MISMATCH_ACTION', 'INST', 660 );
1166 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1167 VALUES('ONT_RETURN_FULFILLED_LINE_ACTION', 'INST', 660 );
1168 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1169 VALUES('OE_DISCOUNT_DETAILS_ON_INVOICE', 'INST', 660 );
1170 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1171 VALUES('ONT_SOURCE_CODE', 'INST', 660 );
1172 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1173 VALUES('OM_UNDER_RETURN_TOLERANCE', 'INST', 660 );
1174 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1175 VALUES('OM_UNDER_SHIPMENT_TOLERANCE', 'INST', 660 );
1176 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1177 VALUES('QP_BLIND_DISCOUNT', 'INST', 660 );
1178 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1179 VALUES('QP_ORGANIZATION_ID', 'INST', 660 );
1180 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1181 VALUES('QP_NEGATIVE_PRICING', 'INST', 660 );
1182 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1183 VALUES('QP_SOURCE_SYSTEM_CODE', 'INST', 660 );
1184 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1185 VALUES('QP_UNIT_PRICE_PRECISION_TYPE', 'INST', 660 );
1186 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1187 VALUES('QP_VERIFY_GSA', 'INST', 660 );
1188 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1189 VALUES('UNIQUE:SEQ_NUMBERS', 'INST', 660 );
1190 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1191 VALUES('AR_ALLOW_TAX_UPDATE', 'INST', 660 );
1192 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1193 VALUES('AR_ALLOW_TAX_CODE_OVERRIDE', 'INST', 660 );
1194 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1195 VALUES('AR_TAX_USE_VENDOR', 'INST', 660 );
1196 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1197 VALUES('AR_USE_INV_ACCT_FOR_CM_FLAG', 'USER', 660 );
1198 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1199 VALUES('CZ_UIMGR_URL', 'USER', 660 );
1200 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1201 VALUES('BOM:ITEM_SEQUENCE_INCREMENT', 'USER', 660 );
1202 --INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1203 --VALUES('BOM:DEFAULT_BOM_LEVELS', 'USER', 660 );
1204 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1205 VALUES('ONT_ADMINISTER_PUBLIC_QUERIES', 'USER', 660 );
1206 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1207 VALUES('OE_APPLY_AUTOMATIC_ATCHMT', 'USER', 660 );
1208 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1209 VALUES('ONT_AUTOSCHEDULE', 'USER', 660 );
1210 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1211 VALUES('ONT_CREDIT_CARD_PRIVILEGES', 'USER', 660 );
1212 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1213 VALUES('OE_CREDIT_TRANSACTION_TYPE_ID', 'USER', 660 );
1214 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1215 VALUES('ONT_CUSTOMER_RELATIONSHIPS', 'USER', 660 );
1216 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1217 VALUES('ONT_EST_AUTH_VALID_DAYS', 'USER', 660 );
1218 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1219 VALUES('ONT_GSA_VIOLATION_ACTION', 'USER', 660 );
1220 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1221 VALUES('ONT_INCLUDED_ITEM_FREEZE_METHOD', 'USER', 660 );
1222 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1223 VALUES('WSH_INVOICE_NUMBERING_METHOD', 'USER', 660 );
1224 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1225 VALUES('OE_INVOICE_SOURCE', 'USER', 660 );
1226 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1227 VALUES('OE_INVOICE_TRANSACTION_TYPE_ID', 'USER', 660 );
1228 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1229 VALUES('OE_ID_FLEX_CODE', 'USER', 660 );
1230 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1231 VALUES('OE_NON_DELIVERY_INVOICE_SOURCE', 'USER', 660 );
1232 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1233 VALUES('OM_OVER_RETURN_TOLERANCE', 'USER', 660 );
1234 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1235 VALUES('OM_OVER_SHIPMENT_TOLERANCE', 'USER', 660 );
1236 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1237 VALUES('OE_OVERSHIP_INVOICE_BASIS', 'USER', 660 );
1238 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1239 VALUES('ONT_RECEIPT_METHOD_ID', 'USER', 660 );
1240 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1241 VALUES('ONT_RESERVATION_TIME_FENCE', 'USER', 660 );
1242 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1243 VALUES('ONT_RETURN_ITEM_MISMATCH_ACTION', 'USER', 660 );
1244 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1245 VALUES('ONT_RETURN_FULFILLED_LINE_ACTION', 'USER', 660 );
1246 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1247 VALUES('ONT_RISK_FAC_THRESHOLD', 'USER', 660 );
1248 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1249 VALUES('ONT_SCHEDULE_LINE_ON_HOLD', 'USER', 660 );
1250 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1251 VALUES('OE_DISCOUNT_DETAILS_ON_INVOICE', 'USER', 660 );
1252 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1253 VALUES('ONT_SOURCE_CODE', 'USER', 660 );
1254 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1255 VALUES('OM_UNDER_RETURN_TOLERANCE', 'USER', 660 );
1256 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1257 VALUES('OM_UNDER_SHIPMENT_TOLERANCE', 'USER', 660 );
1258 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1259 VALUES('QP_ACCRUAL_UOM_CLASS', 'USER', 660 );
1260 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1261 VALUES('QP_BLIND_DISCOUNT', 'USER', 660 );
1262 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1263 VALUES('QP_ORGANIZATION_ID', 'USER', 660 );
1264 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1265 VALUES('QP_LINE_VOLUME_UOM_CODE', 'USER', 660 );
1266 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1267 VALUES('QP_LINE_WEIGHT_UOM_CODE', 'USER', 660 );
1268 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1269 VALUES('QP_NEGATIVE_PRICING', 'USER', 660 );
1270 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1271 VALUES('QP_SOURCE_SYSTEM_CODE', 'USER', 660 );
1272 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1273 VALUES('QP_UNIT_PRICE_PRECISION_TYPE', 'USER', 660 );
1274 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1275 VALUES('QP_VERIFY_GSA', 'USER', 660 );
1276 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1277 VALUES('UNIQUE:SEQ_NUMBERS', 'USER', 660 );
1278 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1279 VALUES('AR_ALLOW_TAX_UPDATE', 'USER', 660 );
1280 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1281 VALUES('AR_ALLOW_MANUAL_TAX_LINES', 'USER', 660 );
1282 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1283 VALUES('AR_ALLOW_TAX_CODE_OVERRIDE', 'USER', 660 );
1284 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1285 VALUES('AR_TAX_USE_VENDOR', 'USER', 660 );
1286 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1287 VALUES('SO_INVOICE_FREIGHT_AS_LINE', 'USER', 660 );
1288 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1289 VALUES('OE_INVENTORY_ITEM_FOR_FREIGHT', 'USER', 660 );
1290 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1291 VALUES('AR_CALCULATE_TAX_ON_CM', 'USER', 660 );
1292 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1293 VALUES('WSH_CR_SREP_FOR_FREIGHT', 'USER', 660 );
1294 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1295 VALUES('ORG_ID', 'USER', 660 );
1296 INSERT INTO OM_SETUP_VALID_PROF_OPT(PROFILE_OPTION_NAME, VAL_LEVEL, APPLICATION_ID)
1297 VALUES('GL_SET_OF_BKS_NAME', 'USER', 660 );
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 begin
1301 RETURN(FALSE);
1302 end;
1303 END;
1304 END;
1305 RETURN(TRUE);
1306 END initializeTemp;
1307 /***********************************************************************************************/
1308 /** getFormFunctionId *************************************************************************/
1309 /***********************************************************************************************/
1310 FUNCTION getFormFunctionId
1311 (
1312 p_form_name VARCHAR2
1313 , p_application VARCHAR2
1314 , p_description VARCHAR2
1315 )
1316 RETURN sarray
1317 as
1318 ---------------------------------------------------------
1319 --------- Input/Output/Inter Variables ------------------
1320 ---------------------------------------------------------
1321 l_form_name VARCHAR2(100);
1322 l_application VARCHAR2(10);
1323 l_description VARCHAR2(100);
1324 l_form_id NUMBER;
1325 l_application_id NUMBER;
1326 l_function_id NUMBER;
1327 l_function_list SARRAY:=SARRAY();
1328 l_empty_cursor BOOLEAN:=FALSE;
1329 l_csr_app_id NUMBER;
1330 l_sarray_init_lgt NUMBER:=1000;
1331 l_sarray_ext_cnt NUMBER:=999;
1332 ---------------------------------------------------------
1333 --------- Cursor Variables ------------------------------
1334 ---------------------------------------------------------
1335 CURSOR c_function_cursor(c_form_id NUMBER) IS
1336 select application_id, function_id
1337 from fnd_form_functions
1338 where form_id = c_form_id;
1339 ---------------------------------------------------------
1340 --------- Cursor Row Type variables ---------------------
1341 ---------------------------------------------------------
1342 l_function_csr c_function_cursor%ROWTYPE;
1343 l_list_index NUMBER:=1;
1344 ---------------------------------------------------------
1345 --------- Other variables -------------------------------
1346 ---------------------------------------------------------
1347 begin
1348 OE_DEBUG_PUB.ADD('IN OEXRSTVB: Get_Form_Function_Id');
1349 OE_DEBUG_PUB.ADD('Input Paramters: ');
1350 OE_DEBUG_PUB.ADD(' l_form_name: '||l_form_name);
1351 OE_DEBUG_PUB.ADD(' l_application: '||l_application);
1352 OE_DEBUG_PUB.ADD(' l_description: '||l_description);
1353 l_form_name := p_form_name;
1354 l_application := p_application;
1355 l_description := p_description;
1356 l_function_list.extend(l_sarray_init_lgt);
1357 ---------------------------------------------------------
1358 ---------- Get the Form Id from Form Name ---------------
1359 ---------------------------------------------------------
1360 select form_id
1361 into l_form_id
1362 from fnd_form
1363 where form_name = l_form_name;
1364 OE_DEBUG_PUB.ADD('l_form_id: '||l_form_id);
1365 ---------------------------------------------------------
1366 ---------- Application Id from Application Short Name --
1367 ---------------------------------------------------------
1368 select application_id
1369 into l_application_id
1370 from fnd_application
1371 where application_short_name = l_application;
1372 OE_DEBUG_PUB.ADD('l_application_id: '||l_application_id);
1373 ---------------------------------------------------------
1374 ---------- Process cursor to get function list ----------
1375 ---------------------------------------------------------
1376 open c_function_cursor(l_form_id);
1377 OE_DEBUG_PUB.ADD('Opened Cursor c_function_cursor');
1378 fetch c_function_cursor into l_function_csr;
1379 OE_DEBUG_PUB.ADD('Fetched cursor into l_function_csr');
1380 if ( c_function_cursor%NOTFOUND ) then
1381 OE_DEBUG_PUB.ADD('No records in cursor');
1382 l_empty_cursor := true;
1383 else
1384 OE_DEBUG_PUB.ADD('records found in cursor');
1385 l_csr_app_id:=l_function_csr.application_id;
1386 OE_DEBUG_PUB.ADD('l_csr_app_id :'||l_csr_app_id);
1387 if (l_csr_app_id=l_application_id) then
1388 loop
1389 l_function_list(l_list_index):=l_function_csr.function_id;
1390 l_list_index:=l_list_index+1;
1391 if(MOD(l_list_index,l_sarray_init_lgt)=l_sarray_ext_cnt) then
1392 l_function_list.extend(l_sarray_init_lgt);
1393 end if;
1394 fetch c_function_cursor into l_function_csr;
1395 exit when c_function_cursor%NOTFOUND;
1396 end loop;
1397 end if;
1398 end if;
1399 ---------------------------------------------------------
1400 ------------ Trim function name list -------------------
1401 ---------------------------------------------------------
1402 l_function_list:=trimArray(l_function_list);
1403 return l_function_list;
1404 end getFormFunctionId;
1405 /***********************************************************************************************/
1406 /** getMenus *********************************************************************************/
1407 /***********************************************************************************************/
1408 FUNCTION getMenus( func_id NUMBER ) RETURN SARRAY AS
1409 menus SARRAY := SARRAY();
1410 CURSOR func_menus( func_id IN NUMBER ) IS
1411 SELECT menu_id
1412 FROM FND_MENU_ENTRIES
1413 WHERE FUNCTION_ID = func_id;
1414 fmenus func_menus%ROWTYPE;
1415 ind NUMBER:=1;
1416 BEGIN
1417 OPEN func_menus(func_id);
1418 FETCH func_menus INTO fmenus;
1419 menus.extend(1000);
1420 LOOP
1421 menus(ind) := fmenus.menu_id;
1422 ind := ind + 1;
1423 IF ( MOD(ind,1000) = 999 ) THEN
1424 menus.extend(1000);
1425 END IF;
1426 FETCH func_menus INTO fmenus;
1427 EXIT WHEN func_menus%NOTFOUND;
1428
1429 END LOOP;
1430 RETURN(trimArray( menus ) );
1431 END;
1432 /***********************************************************************************************/
1433 /** Procedures**********************************************************************************/
1434 /***********************************************************************************************/
1435 PROCEDURE VALIDATE_PROFILE_OPTIONS(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1436 IS
1437 /* Procedure variables */
1438 prof_opts SARRAY := SARRAY();
1439 prof_opt_id NUMBER;
1440 prof_opt_val VARCHAR2(100);
1441 prof_opt_valn NUMBER;
1442 resps SARRAY := SARRAY();
1443 numVal BOOLEAN;
1444 /* Report table variables */
1445 entity VARCHAR2(100);
1446 error_type VARCHAR2(15);
1447 mesg_name VARCHAR2(255);
1448 /* Flags variables */
1449
1450 isDefined BOOLEAN;
1451 errLogged BOOLEAN;
1452 /* Cursors */
1453 CURSOR profile_options IS
1454 SELECT PROFILE_OPTION_NAME
1455 FROM OM_SETUP_VALID_PROF_OPT
1456 WHERE VAL_LEVEL = 'INST'
1457 AND APPLICATION_ID = 660;
1458 --
1459 -- Bug 3537496 : Modified by NKILLEDA
1460 -- Changed the variable name from 'po' to l_po_rec for GSCC
1461 -- compliance. the specific rule is file.sql.6.
1462 --
1463 l_po_rec profile_options%ROWTYPE;
1464 /* Counters */
1465 ind NUMBER := 1;
1466 BEGIN
1467 prof_opts.extend(100);
1468 OPEN profile_options;
1469 FETCH profile_options INTO l_po_rec;
1470 LOOP
1471 prof_opts(ind) := l_po_rec.PROFILE_OPTION_NAME;
1472 ind := ind + 1;
1473 FETCH profile_options INTO l_po_rec;
1474 EXIT WHEN profile_options%NOTFOUND;
1475 END LOOP;
1476 prof_opts := trimArray(prof_opts);
1477 FOR i IN 1..prof_opts.COUNT
1478 LOOP
1479 isDefined := FALSE;
1480 prof_opt_id := getProfileOptionId( prof_opts(i) );
1481 prof_opt_val := getProfileOptionValue(10001, p_value, prof_opt_id);
1482 numVal := isNumeric( prof_opt_val );
1483 IF ( (numVal=FALSE) OR
1484 ((numVal=TRUE) AND NOT ((prof_opt_val=-1) OR (prof_opt_val is null))
1485 )
1486 ) THEN
1487 isDefined := TRUE;
1488 END IF;
1489 IF ( isDefined = FALSE ) THEN
1490 FOR j IN 1..APPS_LIST.COUNT
1491 LOOP
1492 prof_opt_val := getProfileOptionValue(10002, APPS_LIST(j), prof_opt_id);
1493 numVal := isNumeric( prof_opt_val );
1494 IF ((numVal=FALSE)OR((numVal=TRUE)
1495
1496 AND NOT( (prof_opt_val=-1)
1497 OR (prof_opt_val is null)))) THEN
1498 isDefined := TRUE;
1499 END IF;
1500 END LOOP;
1501 END IF;
1502 IF ( isDefined = FALSE ) THEN
1503 FOR j IN 1..RESP_LIST.COUNT
1504 LOOP
1505 prof_opt_val := getProfileOptionValue(10003, RESP_LIST(j), prof_opt_id);
1506 numVal := isNumeric( prof_opt_val );
1507 IF ((numVal=FALSE)OR((numVal=TRUE)
1508 AND NOT( (prof_opt_val=-1)
1509
1510 OR (prof_opt_val is null)))) THEN
1511 isDefined := TRUE;
1512 END IF;
1513 END LOOP;
1514 END IF;
1515 IF ( isDefined = FALSE ) THEN
1516 FOR j IN 1..USER_LIST.COUNT
1517 LOOP
1518 prof_opt_val := getProfileOptionValue(10004, USER_LIST(j), prof_opt_id);
1519 numVal := isNumeric( prof_opt_val );
1520 IF ((numVal=FALSE)OR((numVal=TRUE)
1521 AND NOT( (prof_opt_val=-1)
1522 OR (prof_opt_val is null)))) THEN
1523 isDefined := TRUE;
1524 END IF;
1525 END LOOP;
1526 END IF;
1527 IF isDefined = FALSE THEN
1528 entity := 'Profile Options';
1529 error_type := 'ERROR';
1530 mesg_name := 'ONT_SETVAL_INVALID_PROF_OPT';
1531 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name ,
1532 null, null, null, null, null, null, null, getProfOptName(prof_opts(i)),
1533 null, null, null);
1534 END IF;
1535 END LOOP;
1536 END VALIDATE_PROFILE_OPTIONS;
1537 PROCEDURE VALIDATE_USER_PROFILE_OPTIONS(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1538 IS
1539 /* Procedure variables */
1540 prof_opts SARRAY := SARRAY();
1541 prof_opt_id NUMBER;
1542 prof_opt_val VARCHAR2(100);
1543 prof_opt_valn NUMBER;
1544 numVal BOOLEAN;
1545 user_name VARCHAR2(255);
1546 /* Report table variables */
1547 entity VARCHAR2(100);
1548 error_type VARCHAR2(15);
1549 mesg_name VARCHAR2(255);
1550 /* Cursors */
1551 CURSOR profile_options IS
1552 SELECT PROFILE_OPTION_NAME
1553 FROM OM_SETUP_VALID_PROF_OPT
1554 WHERE VAL_LEVEL = 'USER'
1555 AND APPLICATION_ID = 660;
1556 upo profile_options%ROWTYPE;
1557 /* Counters */
1558 ind NUMBER := 1;
1559 omresp BOOLEAN;
1560 errLogged BOOLEAN;
1561 BEGIN
1562 prof_opts.extend(100);
1563 entity := 'User Level Profile Options';
1564 error_type := 'ERROR';
1565 mesg_name := 'ONT_SETVAL_INVALID_USR_PRF_OPT';
1566 OPEN profile_options;
1567 FETCH profile_options INTO upo;
1568 LOOP
1569 prof_opts(ind) := upo.PROFILE_OPTION_NAME;
1570 ind := ind + 1;
1571 FETCH profile_options INTO upo;
1572 EXIT WHEN profile_options%NOTFOUND;
1573 END LOOP;
1574 prof_opts := trimArray(prof_opts);
1575 FOR i IN 1..prof_opts.COUNT
1576 LOOP
1577 prof_opt_id := getProfileOptionId( prof_opts(i) );
1578 FOR j IN 1..USER_LIST.COUNT
1579 LOOP
1580 omresp := checkForOMResp(USER_LIST(j));
1581 IF ( omresp = TRUE ) THEN
1582 prof_opt_val := getProfileOptionValue(10004, USER_LIST(j), prof_opt_id);
1583 numVal := isNumeric( prof_opt_val );
1584 user_name := getUserName(USER_LIST(j));
1585 IF ((numVal=FALSE)OR((numVal=TRUE) AND NOT( (prof_opt_val=-1)
1586 OR (prof_opt_val is null)))) THEN
1587 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1588 null, user_name, null, null, null, null, null,
1589 getProfOptName(prof_opts(i)), null, null, null );
1590 END IF;
1591 END IF;
1592 END LOOP;
1593 END LOOP;
1594 END VALIDATE_USER_PROFILE_OPTIONS;
1595 PROCEDURE VALIDATE_SET_OF_BOOKS_SETUP(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1596 IS
1597 /* Report table variables */
1598 entity VARCHAR2(100) := 'Set of Books';
1599 error_type VARCHAR2(15) := 'ERROR';
1600 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_SOB';
1601 /* Indicator variables */
1602 isError BOOLEAN := FALSE;
1603 errLogged BOOLEAN;
1604 /* Local variables */
1605 lvl_id VARCHAR2(10);
1606 lvl_value VARCHAR2(10);
1607 prof_opt_id NUMBER:=1991;
1608 oper_unit NUMBER;
1609 ou NUMBER;
1610 ou_cur NUMBER;
1611 sobar VARCHAR2(255);
1612 sobhr VARCHAR2(255);
1613 sobgl VARCHAR2(255);
1614 /* Array Variables */
1615 oulist SARRAY:=SARRAY();
1616 /* Counter Variables */
1617 indx NUMBER:=1;
1618 appcount NUMBER:=0;
1619 /* Ar system parameters */
1620 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
1621 l_sob_id NUMBER;
1622
1623 BEGIN
1624
1625 IF ( P_LEVEL = 'OU' ) THEN
1626 oulist.extend(1);
1627 oulist(1) := P_VALUE;
1628 ELSE
1629 oulist.extend(4000);
1630 FOR i IN 1..RESP_LIST.COUNT
1631 LOOP
1632 ou := getOperatingUnit( RESP_LIST(i) );
1633 IF ( ou <> -1 ) THEN
1634 oulist(indx) := ou;
1635 indx := indx + 1;
1636 END IF;
1637 END LOOP;
1638 oulist := getUniqueList(trimArray( oulist ));
1639 END IF;
1640 FOR i IN 1..oulist.COUNT
1641 LOOP
1642 BEGIN
1643 IF oe_code_control.code_release_level < '110510' THEN
1644 SELECT gsob.NAME
1645 INTO sobar
1646 FROM AR_SYSTEM_PARAMETERS_ALL aspa, GL_SETS_OF_BOOKS gsob
1647 WHERE aspa.ORG_ID = oulist(i)
1648 AND gsob.SET_OF_BOOKS_ID = aspa.SET_OF_BOOKS_ID;
1649 ELSE
1650 l_AR_Sys_Param_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params(oulist(i));
1651 l_sob_id := l_AR_Sys_Param_Rec.set_of_books_id;
1652 SELECT gsob.NAME
1653 INTO sobar
1654 FROM GL_SETS_OF_BOOKS gsob
1655 WHERE gsob.SET_OF_BOOKS_ID = l_sob_id;
1656 END IF;
1657
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 sobar := -1;
1661 END;
1662 BEGIN
1663 SELECT gsob.NAME
1664 INTO sobhr
1665 FROM HR_OPERATING_UNITS hou, GL_SETS_OF_BOOKS gsob
1666 WHERE hou.ORGANIZATION_ID = oulist(i)
1667 AND gsob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID;
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 sobhr := -1;
1671 END;
1672 FOR j IN 1..RESP_LIST.COUNT
1673 LOOP
1674 ou_cur := getOperatingUnit( RESP_LIST(j) );
1675 IF ( ou_cur = oulist(i) ) THEN
1676 sobgl:=getProfileOptionValue(10003, RESP_LIST(j), 1202);
1677 IF ( sobgl<>to_char(-1) AND sobar<>to_char(-1) AND sobhr<>to_char(-1)
1678 AND (sobgl<>sobar OR sobar<>sobhr OR sobgl<>sobhr) ) THEN
1679 errLogged := writeError( entity, p_level, p_value, error_type,
1680 mesg_name, 'Resp', getRespName(RESP_LIST(j)),
1681 null, null, sobar, sobhr, sobgl, null, null, null, null );
1682 END IF;
1683 FOR k IN 1..APPS_LIST.COUNT
1684 LOOP
1685 SELECT COUNT(APPLICATION_ID)
1686 INTO appcount
1687 FROM FND_RESPONSIBILITY
1688 WHERE RESPONSIBILITY_ID = RESP_LIST(j)
1689 AND APPLICATION_ID = APPS_LIST(k);
1690 IF ( appcount>0 ) THEN
1691 sobgl:=getProfileOptionValue(10002, APPS_LIST(k), 1202);
1692 IF ( sobgl<>to_char(-1) AND sobar<>to_char(-1) AND sobhr<>to_char(-1)
1693 AND ( sobgl<>sobar OR sobar<>sobhr OR sobgl<>sobhr ) ) THEN
1694 errLogged := writeError( entity, p_level, p_value, error_type,
1695 mesg_name, 'Appl', getAppsName(APPS_LIST(k)),
1696 null, null, sobar, sobhr, sobgl, null, null, null, null );
1697 END IF;
1698 END IF;
1699 END LOOP;
1700 END IF;
1701 END LOOP;
1702 END LOOP;
1703 END VALIDATE_SET_OF_BOOKS_SETUP;
1704 PROCEDURE VALIDATE_ITEM_VALID_ORG(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1705 IS
1706 /* Report table variables */
1707 entity VARCHAR2(100) := 'Item Validation Organization';
1708 error_type VARCHAR2(15) := 'ERROR';
1709 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_MASTER_ORG';
1710 oulist SARRAY := SARRAY();
1711 ou NUMBER;
1712 indx NUMBER:=1;
1713 /* Indicator variables */
1714 isError BOOLEAN := FALSE;
1715 errLogged BOOLEAN;
1716 /* Local variables */
1717 lvl_id VARCHAR2(10);
1718 lvl_value VARCHAR2(10);
1719 prof_opt_id NUMBER:=1991;
1720 oper_unit NUMBER;
1721 mstrorgrb NUMBER;
1722 mstrorgib NUMBER;
1723 mstrorg NUMBER;
1724 BEGIN
1725 IF ( P_LEVEL = 'OU' ) THEN
1726 oulist.extend(1);
1727 oulist(1) := P_VALUE;
1728 ELSE
1729 oulist.extend(4000);
1730 FOR i IN 1..RESP_LIST.COUNT
1731 LOOP
1732 ou := getOperatingUnit( RESP_LIST(i) );
1733 IF ( ou <> -1 ) THEN
1734 oulist(indx) := ou;
1735 indx := indx + 1;
1736 END IF;
1737 END LOOP;
1738 oulist := getUniqueList(trimArray( oulist ));
1739 END IF;
1740 FOR j IN 1..oulist.COUNT
1741 LOOP
1742 -- Start Sys Param Change
1743 /*
1744 BEGIN
1745 SELECT MASTER_ORGANIZATION_ID
1746 INTO mstrorg
1747 FROM OE_SYSTEM_PARAMETERS_ALL
1748 WHERE ORG_ID = oulist(j);
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 mstrorg := -1;
1752 END;
1753 */
1754
1755 mstrorg:=
1756 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', oulist(j));
1757 -- END Sys Param Change
1758 /*
1759 -- Commented out for bug 2888807
1760 mstrorgib := getProfileOptionValue( 10001, null, 1000227 );
1761 IF ( ( mstrorg <> -1 AND mstrorgib <> -1 AND mstrorg <> mstrorgib )
1762 OR ( mstrorg = -1 AND mstrorgib <> -1 ) )
1763 THEN
1764 IF ( mstrorg = -1 ) THEN
1765 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1766 'Not Defined', null, getOuName(oulist(j)), getOuName(mstrorgib), null,
1767 null, null, null, null, null, null );
1768 ELSE
1769 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1770 getOuName(mstrorg), null, getOuName(oulist(j)), getOuName(mstrorgib), null,
1771 null, null, null, null, null, null );
1772 END IF;
1773 END IF;
1774 -- Commented out for bug 2888807
1775 */
1776 FOR i IN 1..RESP_LIST.COUNT
1777 LOOP
1778 lvl_id := 10003;
1779 lvl_value := RESP_LIST(i);
1780 oper_unit := getProfileOptionValue( lvl_id, lvl_value, prof_opt_id );
1781 IF ( oper_unit = oulist(j) ) THEN
1782 mstrorgrb := getProfileOptionValue( lvl_id, lvl_value, 1000227 );
1783 IF ( mstrorgrb = -1 ) THEN
1784 lvl_id := 10001;
1785 mstrorgrb := getProfileOptionValue( lvl_id, lvl_value, 1000227 );
1786 END IF;
1787 IF ( ( mstrorg <> -1 AND mstrorgrb <> -1 AND mstrorg <> mstrorgrb )
1788 OR ( mstrorg = -1 AND mstrorgrb <> -1 ) )
1789 THEN
1790 IF ( mstrorg = -1 ) THEN
1791 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1792 'Not Defined', null, getOuName(oper_unit), getOuName(mstrorgrb), null,
1793 null, null, null, null, null, null );
1794 ELSE
1795 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1796 getOuName(mstrorg), null, getOuName(oper_unit), getOuName(mstrorgrb), null,
1797 null, null, null, null, null, null );
1798 END IF;
1799 END IF;
1800 END IF;
1801 END LOOP;
1802 END LOOP;
1803 END VALIDATE_ITEM_VALID_ORG;
1804 PROCEDURE VALIDATE_SALES_ORDER_KEYFLEX(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1805 IS
1806 /* Report table variables */
1807 entity VARCHAR2(100) := 'Key Flex Field Setup';
1808 error_type VARCHAR2(15) := 'ERROR';
1809 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_SO_FLEX';
1810 /* Cursor Variables */
1811 CURSOR flex_defs IS
1812 SELECT DYNAMIC_INSERTS_ALLOWED_FLAG,
1813 FFST.ENABLED_FLAG
1814 FROM FND_ID_FLEX_STRUCTURES_VL FFST,
1815 FND_ID_FLEX_SEGMENTS_VL FFSG,
1816 FND_FLEX_VALUE_SETS FVS
1817 WHERE FFST.APPLICATION_ID = 401
1818 AND FFST.ID_FLEX_CODE = 'MKTS'
1819 AND FFST.APPLICATION_ID = FFSG.APPLICATION_ID
1820 AND FFST.ID_FLEX_CODE = FFSG.ID_FLEX_CODE
1821 AND FFST.ID_FLEX_NUM = FFSG.ID_FLEX_NUM
1822 AND FVS.FLEX_VALUE_SET_ID(+) = FFSG.FLEX_VALUE_SET_ID;
1823 flex flex_defs%ROWTYPE;
1824
1825 CURSOR flex_segs IS
1826 SELECT SEGMENT_NUM,
1827 SEGMENT_NAME,
1828 FFST.ENABLED_FLAG
1829 FROM FND_ID_FLEX_STRUCTURES_VL FFST,
1830 FND_ID_FLEX_SEGMENTS_VL FFSG,
1831 FND_FLEX_VALUE_SETS FVS
1832 WHERE FFST.APPLICATION_ID = 401
1833 AND FFST.ID_FLEX_CODE = 'MKTS'
1834 AND FFST.APPLICATION_ID = FFSG.APPLICATION_ID
1835 AND FFST.ID_FLEX_CODE = FFSG.ID_FLEX_CODE
1836 AND FFST.ID_FLEX_NUM = FFSG.ID_FLEX_NUM
1837 AND FVS.FLEX_VALUE_SET_ID(+) = FFSG.FLEX_VALUE_SET_ID;
1838 flexs flex_segs%ROWTYPE;
1839
1840 /* Counters */
1841 counter NUMBER := 0;
1842 /* Indicator variables */
1843 isError BOOLEAN := FALSE;
1844 errLogged BOOLEAN;
1845 enabled VARCHAR2(1);
1846 senable VARCHAR2(1);
1847 dyninsa VARCHAR2(1);
1848 BEGIN
1849 OPEN flex_defs;
1850 FETCH flex_defs INTO flex;
1851 LOOP
1852 enabled := flex.ENABLED_FLAG;
1853 dyninsa := flex.DYNAMIC_INSERTS_ALLOWED_FLAG;
1854 IF ( enabled = 'N' OR dyninsa = 'N') THEN
1855 isError := TRUE;
1856 EXIT;
1857 END IF;
1858 counter := counter + 1;
1859
1860 FETCH flex_defs INTO flex;
1861 EXIT WHEN flex_defs%NOTFOUND;
1862 END LOOP;
1863 IF (isError=FALSE) AND (counter<3) THEN
1864 isError := TRUE;
1865 END IF;
1866 CLOSE flex_defs;
1867 OPEN flex_segs;
1868 FETCH flex_segs INTO flexs;
1869 LOOP
1870 senable := flexs.ENABLED_FLAG;
1871 IF ( senable='N') THEN
1872 isError := TRUE;
1873 EXIT;
1874 END IF;
1875 counter := counter + 1;
1876
1877 FETCH flex_segs INTO flexs;
1878 EXIT WHEN flex_segs%NOTFOUND;
1879 END LOOP;
1880 CLOSE flex_segs;
1881 IF isError = TRUE THEN
1882 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1883 null, null, null, null, null, null, null,
1884 null, null, null, null );
1885 END IF;
1886 END VALIDATE_SALES_ORDER_KEYFLEX;
1887 PROCEDURE VALIDATE_ITEM_CATALOGS_FLEX(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1888 IS
1889 /* Report table variables */
1890 entity VARCHAR2(100) := 'Key Flex Field Setup';
1891 error_type VARCHAR2(15) := 'ERROR';
1892 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_ITMCAT_FLEX';
1893 /* Cursor Variables */
1894 CURSOR flex_defs IS
1895 SELECT FREEZE_FLEX_DEFINITION_FLAG,
1896 SEGMENT_NUM,
1897 SEGMENT_NAME,
1898 FFSG.ENABLED_FLAG
1899 FROM FND_ID_FLEX_STRUCTURES_VL FFST,
1900 FND_ID_FLEX_SEGMENTS_VL FFSG,
1901 FND_FLEX_VALUE_SETS FVS
1902 WHERE FFST.APPLICATION_ID = 401
1903 AND FFST.ID_FLEX_CODE = 'MICG'
1904 AND FFST.APPLICATION_ID = FFSG.APPLICATION_ID
1905 AND FFST.ID_FLEX_CODE = FFSG.ID_FLEX_CODE
1906 AND FFST.ID_FLEX_NUM = FFSG.ID_FLEX_NUM
1907 AND FVS.FLEX_VALUE_SET_ID(+) = FFSG.FLEX_VALUE_SET_ID;
1908 flex flex_defs%ROWTYPE;
1909 /* Counters */
1910 counter NUMBER := 0;
1911 /* Indicator variables */
1912 isError BOOLEAN := FALSE;
1913 errLogged BOOLEAN;
1914 freezed VARCHAR2(1);
1915 enabled VARCHAR2(1);
1916 BEGIN
1917 OPEN flex_defs;
1918 FETCH flex_defs INTO flex;
1919 LOOP
1920 freezed := flex.FREEZE_FLEX_DEFINITION_FLAG;
1921 enabled := flex.ENABLED_FLAG;
1922 IF ( freezed = 'N' OR enabled = 'N') THEN
1923 isError := TRUE;
1924 EXIT;
1925 END IF;
1926 counter := counter + 1;
1927 FETCH flex_defs INTO flex;
1928 EXIT WHEN flex_defs%NOTFOUND;
1929 END LOOP;
1930 IF (isError=FALSE) AND (counter=0) THEN
1931 isError := TRUE;
1932 END IF;
1933 IF isError = TRUE THEN
1934 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
1935 null, null, null, null, null, null, null,
1936 null, null, null, null );
1937 END IF;
1938 END VALIDATE_ITEM_CATALOGS_FLEX;
1939 PROCEDURE VALIDATE_TRANSACTION_TYPES(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
1940 IS
1941 /* Report table variables */
1942 entity VARCHAR2(100) := 'Transaction Type';
1943 error_type VARCHAR2(15);
1944 mesg_name VARCHAR2(255);
1945 /* Indicator variables */
1946 errLogged BOOLEAN;
1947 /* OU Array variables */
1948 ou NUMBER;
1949 oulist SARRAY := SARRAY();
1950 oulist2 SARRAY := SARRAY();
1951 oulist3 SARRAY := SARRAY();
1952 oulist4 SARRAY := SARRAY();
1953 oulist5 SARRAY := SARRAY();
1954 oulist6 SARRAY := SARRAY();
1955 oulist7 SARRAY := SARRAY();
1956 oulist8 SARRAY := SARRAY();
1957 oulist9 SARRAY := SARRAY();
1958 /* Ttypes Array variables */
1959 ttypes2 SARRAY := SARRAY();
1960 ttypes3 SARRAY := SARRAY();
1961 ttypes4 SARRAY := SARRAY();
1962 ttypes5 SARRAY := SARRAY();
1963 ttypes6 SARRAY := SARRAY();
1964 ttypes7 SARRAY := SARRAY();
1965 ttypes8 SARRAY := SARRAY();
1966 ttypes9 SARRAY := SARRAY();
1967 /* Counter variable */
1968 indx NUMBER := 1;
1969 cnt1 NUMBER := 1;
1970 cnt2 NUMBER := 1;
1971 cnt3 NUMBER := 1;
1972 /* Cursor Variables */
1973 CURSOR csr2 IS
1974 SELECT T.TRANSACTION_TYPE_ID, T.ORG_ID
1975 FROM OE_TRANSACTION_TYPES_ALL T
1976 WHERE NOT T.TRANSACTION_TYPE_ID IN ( SELECT UNIQUE ORDER_TYPE_ID
1977 FROM OE_WORKFLOW_ASSIGNMENTS
1978 WHERE LINE_TYPE_ID IS NULL );
1979 csrvar2 csr2%ROWTYPE;
1980 CURSOR csr3 IS
1981 SELECT T.TRANSACTION_TYPE_ID, T.ORG_ID
1982 FROM OE_TRANSACTION_TYPES_ALL T
1983 WHERE NOT T.TRANSACTION_TYPE_ID IN ( SELECT UNIQUE ORDER_TYPE_ID
1984 FROM OE_WORKFLOW_ASSIGNMENTS
1985 WHERE LINE_TYPE_ID IS NOT NULL );
1986 csrvar3 csr3%ROWTYPE;
1987 CURSOR csr4 IS
1988 SELECT TRANSACTION_TYPE_ID, ORG_ID
1989 FROM OE_TRANSACTION_TYPES_ALL
1990 WHERE TRANSACTION_TYPE_CODE = 'ORDER'
1991 AND ORDER_CATEGORY_CODE <> 'RETURN'
1992 AND DEFAULT_OUTBOUND_LINE_TYPE_ID IS NULL;
1993
1994 csrvar4 csr4%ROWTYPE;
1995 CURSOR csr5 IS
1996 SELECT TRANSACTION_TYPE_ID, ORG_ID
1997 FROM OE_TRANSACTION_TYPES_ALL
1998 WHERE TRANSACTION_TYPE_CODE = 'ORDER'
1999 AND DEFAULT_INBOUND_LINE_TYPE_ID IS NULL
2000 AND ORDER_CATEGORY_CODE <> 'ORDER';
2001 csrvar5 csr5%ROWTYPE;
2002 CURSOR csr6 IS
2003 SELECT TRANSACTION_TYPE_ID, ORG_ID
2004 FROM OE_TRANSACTION_TYPES_ALL
2005 WHERE ENTRY_CREDIT_CHECK_RULE_ID IS NULL
2006 AND SHIPPING_CREDIT_CHECK_RULE_ID IS NULL;
2007 csrvar6 csr6%ROWTYPE;
2008 CURSOR csr7 IS
2009 SELECT TRANSACTION_TYPE_ID, ORG_ID
2010 FROM OE_TRANSACTION_TYPES_ALL
2011 WHERE SCHEDULING_LEVEL_CODE IS NULL;
2012 csrvar7 csr6%ROWTYPE;
2013 CURSOR csr8 IS
2014 SELECT TRANSACTION_TYPE_ID, ORG_ID
2015 FROM OE_TRANSACTION_TYPES_ALL
2016 WHERE COST_OF_GOODS_SOLD_ACCOUNT IS NULL;
2017 csrvar8 csr8%ROWTYPE;
2018 CURSOR csr9 IS
2019 SELECT TRANSACTION_TYPE_ID, ORG_ID
2020 FROM OE_TRANSACTION_TYPES_ALL
2021 WHERE NON_DELIVERY_INVOICE_SOURCE_ID IS NULL
2022 AND INVOICE_SOURCE_ID IS NULL;
2023 csrvar9 csr9%ROWTYPE;
2024 CURSOR csr10 IS
2025 SELECT UNIQUE ORG_ID
2026 FROM OE_TRANSACTION_TYPES_ALL
2027 WHERE CUST_TRX_TYPE_ID IS NULL;
2028 csrvar10 csr10%ROWTYPE;
2029 BEGIN
2030 IF ( P_LEVEL = 'OU' ) THEN
2031 oulist.extend(1);
2032 oulist(1) := P_VALUE;
2033 ELSE
2034 oulist.extend(4000);
2035 FOR i IN 1..RESP_LIST.COUNT
2036 LOOP
2037 ou := getOperatingUnit( RESP_LIST(i) );
2038 IF ( ou <> -1 ) THEN
2039 oulist(indx) := ou;
2040 indx := indx + 1;
2041 END IF;
2042 END LOOP;
2043 oulist := getUniqueList(trimArray( oulist ));
2044 END IF;
2045 FOR i IN 1..oulist.COUNT
2046 LOOP
2047 SELECT COUNT(TRANSACTION_TYPE_ID)
2048 INTO cnt1
2049 FROM OE_TRANSACTION_TYPES_ALL
2050 WHERE ORG_ID = oulist(i);
2051 IF ( cnt1 = 0 ) THEN
2052 error_type := 'ERROR';
2053 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_1';
2054 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2055 null, null, getOuName(oulist(i)), null, null, null, null,
2056 null, null, null, null );
2057 END IF;
2058 OPEN csr2;
2059 FETCH csr2 INTO csrvar2;
2060 LOOP
2061 error_type := 'ERROR';
2062 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_2';
2063 IF csrvar2.ORG_ID = oulist(i) THEN
2064 errLogged:= writeError( entity, p_level, p_value, error_type, mesg_name,
2065 null, null, getOuName(csrvar2.ORG_ID), null, null, null, null,
2066 null, getTtypeName(csrvar2.TRANSACTION_TYPE_ID), null, null );
2067 END IF;
2068 FETCH csr2 INTO csrvar2;
2069 EXIT WHEN csr2%NOTFOUND;
2070 END LOOP;
2071 CLOSE csr2;
2072 OPEN csr3;
2073 FETCH csr3 INTO csrvar3;
2074 LOOP
2075 error_type := 'ERROR';
2076 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_3';
2077 IF csrvar3.ORG_ID = oulist(i) THEN
2078 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2079 null, null, getOuName(csrvar3.ORG_ID), null, null, null, null,
2080 null, getTtypeName(csrvar3.TRANSACTION_TYPE_ID), null, null );
2081 END IF;
2082 FETCH csr3 INTO csrvar3;
2083 EXIT WHEN csr3%NOTFOUND;
2084 END LOOP;
2085 CLOSE csr3;
2086 OPEN csr4;
2087 FETCH csr4 INTO csrvar4;
2088 LOOP
2089 error_type := 'WARNING';
2090 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_9';
2091 IF csrvar4.ORG_ID = oulist(i) THEN
2092 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2093 null, null, getOuName(csrvar4.ORG_ID), null, null, null, null,
2094 null, getTtypeName(csrvar4.TRANSACTION_TYPE_ID), null, null );
2095 END IF;
2096 FETCH csr4 INTO csrvar4;
2097 EXIT WHEN csr4%NOTFOUND;
2098 END LOOP;
2099 CLOSE csr4;
2100 OPEN csr5;
2101 FETCH csr5 INTO csrvar5;
2102 LOOP
2103 error_type := 'WARNING';
2104 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE10';
2105 IF csrvar5.ORG_ID = oulist(i) THEN
2106 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2107 null, null, getOuName(csrvar5.ORG_ID), null, null, null, null,
2108 null, getTtypeName(csrvar5.TRANSACTION_TYPE_ID), null, null );
2109 END IF;
2110 FETCH csr5 INTO csrvar5;
2111 EXIT WHEN csr5%NOTFOUND;
2112 END LOOP;
2113 CLOSE csr5;
2114 /*
2115 OPEN csr6;
2116 FETCH csr6 INTO csrvar6;
2117 LOOP
2118 error_type := 'WARNING';
2119 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_4';
2120 IF csrvar6.ORG_ID = oulist(i) THEN
2121 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2122 null, null, getOuName(csrvar6.ORG_ID), null, null, null, null,
2123 null, getTtypeName(csrvar6.TRANSACTION_TYPE_ID), null, null );
2124 END IF;
2125 FETCH csr6 INTO csrvar6;
2126 EXIT WHEN csr6%NOTFOUND;
2127 END LOOP;
2128 CLOSE csr6;
2129 */
2130 OPEN csr7;
2131 FETCH csr7 INTO csrvar7;
2132 LOOP
2133 error_type := 'WARNING';
2134 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_5';
2135 IF csrvar7.ORG_ID = oulist(i) THEN
2136 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2137 null, null, getOuName(csrvar7.ORG_ID), null, null, null, null,
2138 null, getTtypeName(csrvar7.TRANSACTION_TYPE_ID), null, null );
2139 END IF;
2140 FETCH csr7 INTO csrvar7;
2141 EXIT WHEN csr7%NOTFOUND;
2142 END LOOP;
2143 CLOSE csr7;
2144 OPEN csr8;
2145 FETCH csr8 INTO csrvar8;
2146 LOOP
2147 error_type := 'WARNING';
2148 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_6';
2149 IF csrvar8.ORG_ID = oulist(i) THEN
2150 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2151 null, null, getOuName(csrvar8.ORG_ID), null, null, null, null,
2152 null, getTtypeName(csrvar8.TRANSACTION_TYPE_ID), null, null );
2153 END IF;
2154 FETCH csr8 INTO csrvar8;
2155 EXIT WHEN csr8%NOTFOUND;
2156 END LOOP;
2157 CLOSE csr8;
2158 OPEN csr9;
2159 FETCH csr9 INTO csrvar9;
2160 LOOP
2161 error_type := 'WARNING';
2162 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_7';
2163 IF csrvar9.ORG_ID = oulist(i) THEN
2164 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2165 null, null, getOuName(csrvar9.ORG_ID), null, null, null, null,
2166 null, getTtypeName(csrvar9.TRANSACTION_TYPE_ID), null, null );
2167 END IF;
2168 FETCH csr9 INTO csrvar9;
2169 EXIT WHEN csr9%NOTFOUND;
2170 END LOOP;
2171 CLOSE csr9;
2172 OPEN csr10;
2173 FETCH csr10 INTO csrvar10;
2174 LOOP
2175 error_type := 'WARNING';
2176 mesg_name := 'ONT_SETVAL_INVALID_TRAN_TYPE_8';
2177 IF csrvar10.ORG_ID = oulist(i) THEN
2178 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2179 null, null, getOuName(csrvar10.ORG_ID), null, null, null, null,
2180 null, null, null, null );
2181 END IF;
2182 FETCH csr10 INTO csrvar10;
2183 EXIT WHEN csr10%NOTFOUND;
2184 END LOOP;
2185 CLOSE csr10;
2186 END LOOP;
2187 END VALIDATE_TRANSACTION_TYPES;
2188 PROCEDURE VALIDATE_DOC_SEQ_SALES_ORDERS(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2189 IS
2190 /* Report table variables */
2191 entity VARCHAR2(100) := 'Document Sequences For Sales Orders';
2192 error_type VARCHAR2(15) ;
2193 mesg_name VARCHAR2(255) ;
2194 /* Indicator variables */
2195 errLogged BOOLEAN;
2196 isError BOOLEAN:=FALSE;
2197 omdef BOOLEAN:=FALSE;
2198 /* Counter Variables */
2199 indx NUMBER:=1;
2200 docseqcnt NUMBER:=0;
2201 dscatcnt NUMBER:=0;
2202 actcount NUMBER:=0;
2203 sobcount NUMBER:=0;
2204 /* Array Variables */
2205 oulist SARRAY:=SARRAY();
2206 /* Local Variables */
2207 ou NUMBER;
2208 sobcat NUMBER;
2209 sobhr NUMBER;
2210 /* Cursor Variables */
2211 CURSOR ttypes IS
2212 SELECT tl.NAME, b.ORG_ID
2213 FROM OE_TRANSACTION_TYPES_TL tl, OE_TRANSACTION_TYPES_ALL b
2214 WHERE tl.TRANSACTION_TYPE_ID = b.TRANSACTION_TYPE_ID
2215 AND tl.LANGUAGE=USERENV('LANG')
2216 ORDER BY b.ORG_ID;
2217 ttype ttypes%ROWTYPE;
2218 CURSOR doccats IS
2219 SELECT tta.ORG_ID, fdsc.CODE, fdsc.NAME
2220 FROM FND_DOC_SEQUENCE_CATEGORIES fdsc
2221 , OE_TRANSACTION_TYPES_ALL tta
2222 , OE_TRANSACTION_TYPES_TL ttl
2223 WHERE ttl.NAME = fdsc.NAME
2224 AND tta.TRANSACTION_TYPE_ID = ttl.TRANSACTION_TYPE_ID
2225 AND ttl.LANGUAGE=USERENV('LANG')
2226 ORDER BY tta.ORG_ID;
2227 doccat doccats%ROWTYPE;
2228 BEGIN
2229 SELECT COUNT(DOC_SEQUENCE_ID)
2230 INTO docseqcnt
2231 FROM FND_DOCUMENT_SEQUENCES
2232 WHERE APPLICATION_ID = 660;
2233 IF docseqcnt = 0 THEN
2234 error_type := 'ERROR';
2235 mesg_name := 'ONT_SETVAL_NOTFOUND_DOC_SEQ';
2236 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2237 null, null, null, null, null, null, null, null, null, null, null );
2238 END IF;
2239 IF ( P_LEVEL = 'OU' ) THEN
2240 oulist.extend(1);
2241 oulist(1) := P_VALUE;
2242 ELSE
2243 oulist.extend(4000);
2244 FOR i IN 1..RESP_LIST.COUNT
2245 LOOP
2246 ou := getOperatingUnit( RESP_LIST(i) );
2247 IF ( ou <> -1 ) THEN
2248 oulist(indx) := ou;
2249 indx := indx + 1;
2250 END IF;
2251 END LOOP;
2252 oulist := getUniqueList(trimArray( oulist ));
2253 END IF;
2254 FOR i IN 1..oulist.COUNT
2255 LOOP
2256 OPEN ttypes;
2257 FETCH ttypes INTO ttype;
2258 LOOP
2259 IF ttype.ORG_ID = oulist(i) THEN
2260 SELECT COUNT(NAME)
2261 INTO dscatcnt
2262 FROM FND_DOC_SEQUENCE_CATEGORIES
2263 WHERE NAME = ttype.NAME
2264 AND TABLE_NAME = 'OE_TRANSACTION_TYPES_ALL';
2265 IF dscatcnt = 0 THEN
2266 error_type := 'ERROR';
2267 mesg_name := 'ONT_SETVAL_NOTFOUND_DOC_CAT';
2268 errLogged := writeError( entity, p_level, p_value, error_type,
2269 mesg_name, null, null, getOuName(oulist(i)),
2270 null, null, null, null, null, ttype.NAME,
2271 null, null );
2272 END IF;
2273 END IF;
2274 FETCH ttypes INTO ttype;
2275 EXIT WHEN ttypes%NOTFOUND;
2276 END LOOP;
2277 CLOSE ttypes;
2278 OPEN doccats;
2279 FETCH doccats INTO doccat;
2280 LOOP
2281 IF (doccat.ORG_ID = oulist(i) ) THEN
2282 BEGIN
2283 SELECT UNIQUE SET_OF_BOOKS_ID
2284 INTO sobhr
2285 FROM HR_OPERATING_UNITS
2286 WHERE ORGANIZATION_ID = doccat.ORG_ID;
2287 EXCEPTION
2288 WHEN OTHERS THEN
2289 sobhr := -1;
2290 isError := TRUE;
2291 END;
2292 IF ( sobhr <> -1 ) THEN
2293 SELECT COUNT(DOC_SEQUENCE_ASSIGNMENT_ID)
2294 INTO actcount
2295 FROM FND_DOC_SEQUENCE_ASSIGNMENTS
2296 WHERE CATEGORY_CODE = doccat.CODE
2297 AND START_DATE <= SYSDATE
2298 AND SYSDATE <= NVL ( END_DATE, SYSDATE)
2299 AND SET_OF_BOOKS_ID = sobhr;
2300 END IF;
2301 IF actcount = 0 THEN
2302 error_type := 'ERROR';
2303 mesg_name := 'ONT_SETVAL_INACTIVE_DOC_SEQ';
2304 errLogged := writeError( entity, p_level, p_value, error_type,
2305 mesg_name, null, null, null, null, null, null, null,
2306 null, null, doccat.NAME, null );
2307 END IF;
2308 END IF;
2309 FETCH doccats INTO doccat;
2310 EXIT WHEN doccats%NOTFOUND;
2311 END LOOP;
2312 CLOSE doccats;
2313 omdef := checkForOmOu(oulist(i));
2314 IF omdef = TRUE THEN
2315 BEGIN
2316 SELECT UNIQUE SET_OF_BOOKS_ID
2317 INTO sobhr
2318 FROM HR_OPERATING_UNITS
2319 WHERE ORGANIZATION_ID = doccat.ORG_ID;
2320 EXCEPTION
2321 WHEN OTHERS THEN
2322 sobhr := -1;
2323 isError := TRUE;
2324 END;
2325 IF ( sobhr <> -1 ) THEN
2326 SELECT COUNT(SET_OF_BOOKS_ID)
2327 into sobcount
2328 FROM FND_DOC_SEQUENCE_ASSIGNMENTS
2329 WHERE SET_OF_BOOKS_ID = sobhr;
2330 END IF;
2331 IF ( sobcount = 0 ) THEN
2332 error_type := 'ERROR';
2333 mesg_name := 'ONT_SETVAL_INVALID_DOC_SEQ';
2334 errLogged := writeError( entity, p_level, p_value, error_type,
2335 mesg_name, null, null, null, null, null, null,
2336 null, null, null, null, null );
2337 END IF;
2338 END IF;
2339 END LOOP;
2340 END VALIDATE_DOC_SEQ_SALES_ORDERS;
2341 PROCEDURE VALIDATE_CREDIT_CHECKING(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2342 IS
2343 /* Report table variables */
2344 entity VARCHAR2(100) := 'Credit Checking Setup';
2345 error_type VARCHAR2(15) ;
2346 mesg_name VARCHAR2(255) ;
2347 /* Counter Variables */
2348 ccrcnt NUMBER:=0;
2349 custcnt NUMBER:=0;
2350 creqcnt NUMBER:=0;
2351 ccexcnt NUMBER:=0;
2352 ptcount NUMBER:=0;
2353 indx NUMBER:=1;
2354 /* Array Variables */
2355 oulist SARRAY:=SARRAY();
2356 /* Local Variables */
2357 ou NUMBER;
2358 sob VARCHAR2(50);
2359 /* Cursor Variables */
2360 CURSOR ttdisabled IS
2361 SELECT tl.NAME, b.ORG_ID
2362 FROM OE_TRANSACTION_TYPES_ALL b
2363 , OE_TRANSACTION_TYPES_TL tl
2364 WHERE ENTRY_CREDIT_CHECK_RULE_ID IS NULL
2365 AND SHIPPING_CREDIT_CHECK_RULE_ID IS NULL
2366 AND PICKING_CREDIT_CHECK_RULE_ID IS NULL
2367 AND PACKING_CREDIT_CHECK_RULE_ID IS NULL
2368 AND tl.LANGUAGE = USERENV('LANG')
2369 AND tl.TRANSACTION_TYPE_ID = b.TRANSACTION_TYPE_ID;
2370 ttypes ttdisabled%ROWTYPE;
2371 /* Indicator Variables */
2372 errLogged BOOLEAN;
2373 BEGIN
2374 SELECT COUNT(CREDIT_CHECK_RULE_ID)
2375 INTO ccrcnt
2376 FROM OE_CREDIT_CHECK_RULES;
2377 IF ( ccrcnt = 0 ) THEN
2378 error_type := 'WARNING';
2379 mesg_name := 'ONT_SETVAL_CC_NOTFOUND';
2380 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2381 null, null, null, null, null, null, null, null, null, null, null );
2382 END IF;
2383 IF ( P_LEVEL = 'OU' ) THEN
2384 oulist.extend(1);
2385 oulist(1) := P_VALUE;
2386 ELSE
2387 oulist.extend(4000);
2388 FOR i IN 1..RESP_LIST.COUNT
2389 LOOP
2390 ou := getOperatingUnit( RESP_LIST(i) );
2391 IF ( ou <> -1 ) THEN
2392 oulist(indx) := ou;
2393 indx := indx + 1;
2394 END IF;
2395 END LOOP;
2396 oulist := getUniqueList(trimArray(oulist));
2397 END IF;
2398 SELECT COUNT(TERM_ID)
2399 INTO ptcount
2400 FROM RA_TERMS_B
2401 WHERE CREDIT_CHECK_FLAG = 'Y';
2402 FOR i IN 1..oulist.COUNT
2403 LOOP
2404 error_type := 'WARNING';
2405 mesg_name := 'ONT_SETVAL_CC_INVALID_PAY_TERM';
2406 IF ( ptcount = 0 ) THEN
2407 SELECT gl.name
2408 INTO sob
2409 FROM GL_SETS_OF_BOOKS gl
2410 , HR_OPERATING_UNITS hou
2411 WHERE hou.organization_id = oulist(i)
2412 AND gl.set_of_books_id = hou.set_of_books_id;
2413 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2414 null, null, getOuName(oulist(i)), null, null, sob, null, null,
2415 null, null, null );
2416 END IF;
2417 error_type := 'WARNING';
2418 mesg_name := 'ONT_SETVAL_CC_INVALID_TRAN_TYP';
2419 OPEN ttdisabled;
2420 FETCH ttdisabled INTO ttypes;
2421 LOOP
2422 IF ( ttypes.ORG_ID = oulist(i) ) THEN
2423 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2424 null, null, getOuName(oulist(i)), null, null, null, null, null,
2425 ttypes.NAME, null, null );
2426 END IF;
2427 FETCH ttdisabled INTO ttypes;
2428 EXIT WHEN ttdisabled%NOTFOUND;
2429 END LOOP;
2430 CLOSE ttdisabled;
2431 error_type := 'WARNING';
2432 mesg_name := 'ONT_SETVAL_CC_INVALID_CUST';
2433 SELECT COUNT(CUST_ACCOUNT_PROFILE_ID)
2434 INTO custcnt
2435 FROM HZ_CUSTOMER_PROFILES
2436 WHERE CREDIT_CHECKING = 'Y';
2437 IF custcnt = 0 THEN
2438 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2439 null, null, getOuName(oulist(i)), null, null, null, null, null,
2440 null, null, null );
2441 END IF;
2442 error_type := 'WARNING';
2443 mesg_name := 'ONT_SETVAL_CC_PRE_CALC_EXPO';
2444 SELECT COUNT(CREDIT_CHECK_RULE_ID)
2445 INTO ccexcnt
2446 FROM OE_CREDIT_CHECK_RULES
2447 WHERE QUICK_CR_CHECK_FLAG = 'Y';
2448 SELECT COUNT(fcr.REQUEST_ID)
2449 INTO creqcnt
2450 FROM FND_CONCURRENT_REQUESTS fcr, FND_CONCURRENT_PROGRAMS_TL fcp
2451 WHERE fcr.CONCURRENT_PROGRAM_ID=fcp.concurrent_program_id
2452 AND (( fcr.PHASE_CODE='P' AND fcr.STATUS_CODE='Q'
2453 OR fcr.PHASE_CODE='P' AND fcr.STATUS_CODE='R' )
2454 OR ( fcr.PHASE_CODE='R' AND fcr.STATUS_CODE='R' ))
2455 AND fcp.USER_CONCURRENT_PROGRAM_NAME like 'Initialize Credit Summaries Table'
2456 AND fcp.LANGUAGE = USERENV('LANG');
2457 IF ccexcnt > 0 and creqcnt = 0 THEN
2458 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name,
2459 null, null, getOuName(oulist(i)), null, null, null, null, null,
2460 null, null, null );
2461 END IF;
2462 END LOOP;
2463 END VALIDATE_CREDIT_CHECKING;
2464 PROCEDURE VALIDATE_ITEM_DEFINITION(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2465 IS
2466 /* Report table variables */
2467 entity VARCHAR2(100) := 'Item Definition';
2468 error_type VARCHAR2(15) := 'ERROR';
2469 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_ITEM';
2470 /* Counter Variables */
2471 indx NUMBER:=1;
2472 itmcnt NUMBER:=0;
2473 /* Array Variables */
2474 oulist SARRAY:=SARRAY();
2475 /* Local Variables */
2476 ou NUMBER;
2477 mstrorg NUMBER;
2478 arstatus VARCHAR2(1);
2479 /* Indicator Variables */
2480 errLogged BOOLEAN;
2481 BEGIN
2482 BEGIN
2483 SELECT STATUS
2484 INTO arstatus
2485 FROM FND_PRODUCT_INSTALLATIONS
2486 WHERE APPLICATION_ID = 222;
2487 EXCEPTION
2488 WHEN NO_DATA_FOUND THEN
2489 arstatus := 'N';
2490 END;
2491 IF ( P_LEVEL = 'OU' ) THEN
2492 oulist.extend(1);
2493 oulist(1) := P_VALUE;
2494 ELSE
2495 oulist.extend(4000);
2496 FOR i IN 1..RESP_LIST.COUNT
2497 LOOP
2498 ou := getOperatingUnit( RESP_LIST(i) );
2499 IF ( ou <> -1 ) THEN
2500 oulist(indx) := ou;
2501 indx := indx + 1;
2502 END IF;
2503 END LOOP;
2504 oulist := getUniqueList(trimArray(oulist));
2505 END IF;
2506 FOR i IN 1..oulist.COUNT
2507 LOOP
2508 -- Start Sys Param Change
2509 /*
2510 BEGIN
2511 SELECT MASTER_ORGANIZATION_ID
2512 INTO mstrorg
2513 FROM OE_SYSTEM_PARAMETERS_ALL
2514 WHERE ORG_ID = oulist(i);
2515 EXCEPTION
2516 WHEN NO_DATA_FOUND THEN
2517 mstrorg := null;
2518 END;
2519 */
2520 mstrorg :=
2521 oe_sys_parameters.value('MASTER_ORGANIZATION_ID',oulist(i));
2522 -- End Sys Param Change
2523 IF mstrorg IS NOT NULL THEN
2524 IF arstatus = 'I' THEN
2525 SELECT COUNT(INVENTORY_ITEM_ID)
2526 INTO itmcnt
2527 FROM MTL_SYSTEM_ITEMS_B
2528 WHERE ORGANIZATION_ID = mstrorg
2529 AND MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'
2530 AND CUSTOMER_ORDER_FLAG = 'Y'
2531 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
2532 AND RETURNABLE_FLAG = 'Y'
2533 AND INTERNAL_ORDER_FLAG = 'Y'
2534 AND INTERNAL_ORDER_ENABLED_FLAG = 'Y'
2535 AND SHIPPABLE_ITEM_FLAG = 'Y'
2536 AND INVOICEABLE_ITEM_FLAG = 'Y'
2537 AND RESERVABLE_TYPE = 1
2538 AND INVOICE_ENABLED_FLAG = 'Y';
2539 ELSE
2540 SELECT COUNT(INVENTORY_ITEM_ID)
2541 INTO itmcnt
2542 FROM MTL_SYSTEM_ITEMS_B
2543 WHERE ORGANIZATION_ID = mstrorg
2544 AND MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'
2545 AND CUSTOMER_ORDER_FLAG = 'Y'
2546 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
2547 AND RETURNABLE_FLAG = 'Y'
2548 AND INTERNAL_ORDER_FLAG = 'Y'
2549 AND INTERNAL_ORDER_ENABLED_FLAG = 'Y'
2550 AND SHIPPABLE_ITEM_FLAG = 'Y'
2551 AND RESERVABLE_TYPE = 1
2552 AND INVOICEABLE_ITEM_FLAG = 'Y';
2553 END IF;
2554 END IF;
2555 IF itmcnt = 0 THEN
2556 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
2557 null, getOuName(mstrorg), getOuName(mstrorg), null, null, null, null, null, null,
2558 null );
2559 END IF;
2560 END LOOP;
2561 END VALIDATE_ITEM_DEFINITION;
2562 PROCEDURE VALIDATE_PRICE_LIST_DEFINITION(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2563 IS
2564 /* Report table variables */
2565 entity VARCHAR2(100) := 'Price List Definition';
2566 error_type VARCHAR2(15) := 'ERROR';
2567 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_PRC_LST';
2568 /* Counter Variables */
2569 prclstct NUMBER := 1;
2570 /* Indicator Variables */
2571 errLogged BOOLEAN;
2572 BEGIN
2573 SELECT COUNT(QLLV.LIST_HEADER_ID)
2574 INTO prclstct
2575 FROM QP_LIST_LINES_V QLLV, QP_LIST_HEADERS_B QLHB
2576 WHERE QLLV.LIST_HEADER_ID = QLHB.LIST_HEADER_ID
2577 /* Commented as it is not required.
2578 AND SYSDATE >= NVL(QLHB.START_DATE_ACTIVE, TO_DATE('01-JAN-1000','DD-MON-YYYY'))
2579 AND SYSDATE < NVL(QLHB.END_DATE_ACTIVE, TO_DATE('31-DEC-2999'))
2580 */
2581 AND QLLV.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
2582 AND QLHB.LIST_HEADER_ID IN (SELECT LIST_HEADER_ID
2583 FROM QP_LIST_HEADERS_B);
2584 IF prclstct = 0 THEN
2585 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
2586 null, null, null, null, null, null, null, null, null, null );
2587 END IF;
2588 END VALIDATE_PRICE_LIST_DEFINITION;
2589 PROCEDURE VALIDATE_SALES_CRDT_DEFINITION(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2590 IS
2591 /* Report table variables */
2592 entity VARCHAR2(100) := 'Sales Credit';
2593 error_type VARCHAR2(15) := 'WARNING';
2594 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_SLS_CRDT';
2595 /* Counter variables */
2596 qcnt NUMBER := 1;
2597 nqcnt NUMBER := 1;
2598 qtype VARCHAR2(10);
2599 /* Indicator Variables */
2600 isError BOOLEAN := TRUE;
2601 errLogged BOOLEAN;
2602 BEGIN
2603 SELECT COUNT(QUOTA_FLAG)
2604 INTO qcnt
2605 FROM OE_SALES_CREDIT_TYPES
2606 WHERE QUOTA_FLAG = 'Y';
2607 SELECT COUNT(QUOTA_FLAG)
2608 INTO nqcnt
2609 FROM OE_SALES_CREDIT_TYPES
2610 WHERE QUOTA_FLAG IS NULL
2611 OR QUOTA_FLAG = 'N';
2612 IF qcnt=0 AND nqcnt=0 THEN
2613 qtype := 'BOTH';
2614 ELSIF qcnt=0 THEN
2615 qtype := 'QUOTA';
2616 ELSIF nqcnt = 0 THEN
2617 qtype := 'NON QUOTA';
2618 ELSE
2619 isError := FALSE;
2620 END IF;
2621 IF isError = TRUE THEN
2622 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, qtype,
2623 null, null, null, null, null, null, null, null, null, null );
2624 END IF;
2625 END VALIDATE_SALES_CRDT_DEFINITION;
2626 PROCEDURE VALIDATE_PERIOD_STATUS(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2627 IS
2628 /* Report table variables */
2629 entity VARCHAR2(100) := 'Period Status';
2630 error_type VARCHAR2(15) := 'WARNING';
2631 mesg_name VARCHAR2(255) := 'ONT_SETVAL_INVALID_SLS_CRDT';
2632 /* Counter Variables */
2633 opencnt NUMBER:=0;
2634 currcnt NUMBER:=0;
2635 /* Array Variables */
2636 oulist SARRAY:=SARRAY();
2637 shiporgs SARRAY:=SARRAY();
2638 /* Local Variables */
2639 ou NUMBER;
2640 status VARCHAR2(10);
2641 /* Counter Variables */
2642 indx NUMBER:=1;
2643 /* Indicator Variables */
2644 errLogged BOOLEAN;
2645 BEGIN
2646 IF ( P_LEVEL = 'OU' ) THEN
2647 oulist.extend(1);
2648 oulist(1) := P_VALUE;
2649 ELSE
2650 oulist.extend(4000);
2651 FOR i IN 1..RESP_LIST.COUNT
2652 LOOP
2653 ou := getOperatingUnit( RESP_LIST(i) );
2654 IF ( ou <> -1 ) THEN
2655 oulist(indx) := ou;
2656 indx := indx + 1;
2657 END IF;
2658 END LOOP;
2659 oulist := getUniqueList(trimArray(oulist));
2660 END IF;
2661 FOR i IN 1..oulist.COUNT
2662 LOOP
2663 shiporgs := getUniqueList(getShippingOrgsForOu(oulist(i)));
2664 FOR j IN 1..shiporgs.COUNT
2665 LOOP
2666 SELECT COUNT(ORGANIZATION_ID)
2667 INTO opencnt
2668 FROM ORG_ACCT_PERIODS_V
2669 WHERE ORGANIZATION_ID = shiporgs(j)
2670 AND STATUS = 'Open';
2671 SELECT COUNT(ORGANIZATION_ID)
2672 INTO currcnt
2673 FROM ORG_ACCT_PERIODS_V
2674 WHERE ORGANIZATION_ID = shiporgs(j)
2675 AND SYSDATE >= START_DATE
2676 AND SYSDATE < END_DATE
2677 AND STATUS = 'Open';
2678 IF opencnt = 0 THEN
2679 error_type := 'WARNING';
2680 mesg_name := 'ONT_SETVAL_NO_OPEN_PERIOD';
2681 errLogged := writeError( entity, p_level, p_value, error_type,
2682 mesg_name, null, null, getOuName(oulist(i)), getOuName(shiporgs(j)),
2683 null, null, null, null, null, null, null );
2684 END IF;
2685 IF currcnt = 0 THEN
2686 error_type := 'WARNING';
2687 mesg_name := 'ONT_SETVAL_INVALID_CURR_PERIOD';
2688 errLogged := writeError( entity, p_level, p_value, error_type,
2689 mesg_name, null, null, getOuName(oulist(i)), getOuName(shiporgs(j)),
2690 null, null, null, null, null, null, null );
2691 END IF;
2692 END LOOP;
2693 END LOOP;
2694 END VALIDATE_PERIOD_STATUS;
2695 PROCEDURE VALIDATE_SHIPPING_ORGS(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2696 IS
2697 /* Report table variables */
2698 entity VARCHAR2(100) := 'Shipping Organizations';
2699 error_type VARCHAR2(15) ;
2700 mesg_name VARCHAR2(255) ;
2701 /* Array Variables */
2702 oulist SARRAY:=SARRAY();
2703 shiporgs SARRAY:=SARRAY();
2704 /* Local Variables */
2705 ou NUMBER;
2706 dftstgsub VARCHAR2(100);
2707 /* Counter Variables */
2708 indx NUMBER:=1;
2709 shsecnt NUMBER:=0;
2710 shipcnt NUMBER:=0;
2711 shorgscnt NUMBER:=0;
2712 secinvcnt NUMBER:=0;
2713 pickcnt NUMBER:=0;
2714 /* Indicator Variables */
2715 errLogged BOOLEAN;
2716 BEGIN
2717 IF ( P_LEVEL = 'OU' ) THEN
2718 oulist.extend(1);
2719 oulist(1) := P_VALUE;
2720 ELSE
2721 oulist.extend(4000);
2722 FOR i IN 1..RESP_LIST.COUNT
2723 LOOP
2724 ou := getOperatingUnit( RESP_LIST(i) );
2725 IF ( ou <> -1 ) THEN
2726 oulist(indx) := ou;
2727 indx := indx + 1;
2728 END IF;
2729 END LOOP;
2730 oulist := getUniqueList(trimArray(oulist));
2731 END IF;
2732 FOR i IN 1..oulist.COUNT
2733 LOOP
2734 shiporgs := getUniqueList(getShippingOrgsForOu(oulist(i)));
2735 SELECT COUNT(FREIGHT_CODE_COMBINATION_ID)
2736 INTO shsecnt
2737 FROM MTL_INTERCOMPANY_PARAMETERS
2738 WHERE SHIP_ORGANIZATION_ID = oulist(i)
2739 OR SELL_ORGANIZATION_ID = oulist(i);
2740 IF shsecnt <> 0 THEN
2741 SELECT COUNT(FREIGHT_CODE_COMBINATION_ID)
2742 INTO shipcnt
2743 FROM MTL_INTERCOMPANY_PARAMETERS
2744 WHERE SHIP_ORGANIZATION_ID = oulist(i);
2745 END IF;
2746 IF ( shsecnt = 0 ) OR ( shsecnt <> 0 AND shipcnt <> 0 ) THEN
2747 FOR j IN 1..shiporgs.COUNT
2748 LOOP
2749 SELECT COUNT(ORGANIZATION_ID)
2750 INTO shorgscnt
2751 FROM WSH_SHIPPING_PARAMETERS
2752 WHERE ORGANIZATION_ID = shiporgs(j);
2753 IF shorgscnt > 0 THEN
2754 EXIT;
2755 END IF;
2756 END LOOP;
2757 IF ( shorgscnt = 0 ) THEN
2758 error_type := 'ERROR';
2759 mesg_name := 'ONT_SETVAL_INVALID_SHIP_ORG';
2760 errLogged := writeError( entity, p_level, p_value, error_type,
2761 mesg_name, null, null, getOuName(oulist(i)), null, null, null,
2762 null, null, null, null, null );
2763 END IF;
2764 FOR j IN 1..shiporgs.COUNT
2765 LOOP
2766 SELECT COUNT(ORGANIZATION_ID)
2767 INTO shorgscnt
2768 FROM WSH_SHIPPING_PARAMETERS
2769 WHERE ORGANIZATION_ID = shiporgs(j);
2770 IF ( shorgscnt = 0 AND checkForOmOu(oulist(i)) = TRUE) THEN
2771 error_type := 'WARNING';
2772 mesg_name := 'ONT_SETVAL_INVALID_SHIP_OMORG';
2773 errLogged := writeError( entity, p_level, p_value, error_type,
2774 mesg_name, null, null, getOuName(oulist(i)), getOuName(shiporgs(j)),
2775 null, null, null, null, null, null, null );
2776 END IF;
2777 END LOOP;
2778 END IF;
2779 FOR j IN 1..shiporgs.COUNT
2780 LOOP
2781 BEGIN
2782 SELECT DEFAULT_STAGE_SUBINVENTORY
2783 INTO dftstgsub
2784 FROM WSH_SHIPPING_PARAMETERS
2785 WHERE ORGANIZATION_ID = shiporgs(j);
2786 EXCEPTION
2787 WHEN NO_DATA_FOUND THEN
2788 dftstgsub := null;
2789 WHEN OTHERS THEN
2790 dftstgsub := null;
2791 END;
2792 IF dftstgsub IS NULL THEN
2793 error_type := 'ERROR';
2794 mesg_name := 'ONT_SETVAL_INVALID_DFLT_SUBINV';
2795 errLogged := writeError( entity, p_level, p_value, error_type,
2796 mesg_name, null, null, getOuName(oulist(i)),
2797 getOuName(shiporgs(j)), null, null, null, null, null,
2798 null, null );
2799 END IF;
2800 END LOOP;
2801 FOR j IN 1..shiporgs.COUNT
2802 LOOP
2803 SELECT COUNT(SECONDARY_INVENTORY_NAME)
2804 INTO secinvcnt
2805 FROM MTL_SECONDARY_INVENTORIES_FK_V
2806 WHERE ORGANIZATION_ID = shiporgs(j);
2807 IF secinvcnt = 0 THEN
2808 error_type := 'ERROR';
2809 mesg_name := 'ONT_SETVAL_INVALID_SUBINV_ORGN';
2810 errLogged := writeError( entity, p_level, p_value, error_type,
2811 mesg_name, null, null, getOuName(oulist(i)),
2812 getOuName(shiporgs(j)), null, null, null, null, null,
2813 null, null );
2814 END IF;
2815 END LOOP;
2816 FOR j IN 1..shiporgs.COUNT
2817 LOOP
2818 SELECT COUNT(ORGANIZATION_ID)
2819 INTO pickcnt
2820 FROM WSH_SHIPPING_PARAMETERS
2821 WHERE ORGANIZATION_ID = shiporgs(j)
2822 AND PICK_SEQUENCE_RULE_ID IS NOT NULL
2823 AND PICK_GROUPING_RULE_ID IS NOT NULL;
2824 IF pickcnt = 0 THEN
2825 error_type := 'ERROR';
2826 mesg_name := 'ONT_SETVAL_INVALID_PICK_RULES';
2827 errLogged := writeError( entity, p_level, p_value, error_type,
2828 mesg_name, null, null, getOuName(oulist(i)),
2829 getOuName(shiporgs(j)), null, null, null, null, null,
2830 null, null );
2831 END IF;
2832 END LOOP;
2833 END LOOP;
2834 RETURN;
2835 END VALIDATE_SHIPPING_ORGS;
2836 PROCEDURE VALIDATE_FREIGHT_CARRIER(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2837 IS
2838 /* Report table variables */
2839 entity VARCHAR2(100) := 'Freight Carrier';
2840 error_type VARCHAR2(15) ;
2841 mesg_name VARCHAR2(255) ;
2842 /* Array Variables */
2843 oulist SARRAY:=SARRAY();
2844 shiporgs SARRAY:=SARRAY();
2845 /* Local Variables */
2846 ou NUMBER;
2847 /* Counter Variables */
2848 indx NUMBER:=1;
2849 activect NUMBER:=0;
2850 actorgct NUMBER:=0;
2851 cashmect NUMBER:=0;
2852 /* Indicator Variables */
2853 errLogged BOOLEAN;
2854 dummy_v VARCHAR2(10);
2855 BEGIN
2856 BEGIN
2857 SELECT 'x'
2858 INTO dummy_v
2859 FROM org_freight_vl
2860 WHERE rownum = 1;
2861 EXCEPTION
2862 WHEN NO_DATA_FOUND THEN
2863 error_type := 'ERROR';
2864 mesg_name := 'ONT_SETVAL_FC_INACTIVE_ALL';
2865 errLogged := writeError( entity, p_level, p_value, error_type,
2866 mesg_name, null, null, null, null, null, null,
2867 null, null, null, null, null );
2868 END;
2869 /*
2870 SELECT COUNT(FREIGHT_CODE)
2871 INTO activect
2872 FROM ORG_FREIGHT_VL;
2873 */
2874 /*WHERE SYSDATE <= NVL(DISABLE_DATE, TO_DATE('31-DEC-2999'));*/
2875 /*
2876 IF activect = 0 THEN
2877 error_type := 'ERROR';
2878 mesg_name := 'ONT_SETVAL_FC_INACTIVE_ALL';
2879 errLogged := writeError( entity, p_level, p_value, error_type,
2880 mesg_name, null, null, null, null, null, null,
2881 null, null, null, null, null );
2882 END IF;
2883 */
2884
2885 IF ( P_LEVEL = 'OU' ) THEN
2886 oulist.extend(1);
2887 oulist(1) := P_VALUE;
2888 ELSE
2889 oulist.extend(4000);
2890 FOR i IN 1..RESP_LIST.COUNT
2891 LOOP
2892 ou := getOperatingUnit( RESP_LIST(i) );
2893 IF ( ou <> -1 ) THEN
2894 oulist(indx) := ou;
2895 indx := indx + 1;
2896 END IF;
2897 END LOOP;
2898 oulist := getUniqueList(trimArray(oulist));
2899 END IF;
2900 FOR i IN 1..oulist.COUNT
2901 LOOP
2902 shiporgs := getUniqueList(getShippingOrgsForOu(oulist(i)));
2903 FOR j IN 1..shiporgs.COUNT
2904 LOOP
2905 SELECT COUNT(ORG_CARRIER_SERVICE_ID)
2906 INTO actorgct
2907 FROM WSH_ORG_CARRIER_SERVICES_V
2908 WHERE ENABLED_FLAG = 'Y'
2909 AND ORGANIZATION_ID = shiporgs(j);
2910 SELECT COUNT(FREIGHT_CODE)
2911 INTO cashmect
2912 FROM WSH_CARRIER_SHIP_METHODS_V
2913 WHERE ENABLED_FLAG = 'Y'
2914 AND ORGANIZATION_ID = shiporgs(j);
2915 END LOOP;
2916 IF actorgct=0 THEN
2917 error_type := 'ERROR';
2918 mesg_name := 'ONT_SETVAL_FC_INACTIVE_ORG';
2919 errLogged := writeError( entity, p_level, p_value, error_type,
2920 mesg_name, null, null, getOuName(oulist(i)), null, null, null,
2921 null, null, null, null, null );
2922 END IF;
2923 IF cashmect=0 THEN
2924 error_type := 'ERROR';
2925 mesg_name := 'ONT_SETVAL_FC_RELATIONS';
2926 errLogged := writeError( entity, p_level, p_value, error_type,
2927 mesg_name, null, null, getOuName(oulist(i)), null, null, null,
2928 null, null, null, null, null );
2929 END IF;
2930 END LOOP;
2931 END VALIDATE_FREIGHT_CARRIER;
2932 PROCEDURE VALIDATE_DOC_SEQ_SHIPPING(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
2933 IS
2934 /* Report table variables */
2935 entity VARCHAR2(100) := 'Document Sequences for Shipping Documents';
2936 error_type VARCHAR2(15) ;
2937 mesg_name VARCHAR2(255) ;
2938 /* Counter Variables */
2939 doccount NUMBER := 0;
2940 bolcount NUMBER := 0;
2941 pkslpcnt NUMBER := 0;
2942 indx NUMBER := 1;
2943 omcnt NUMBER := 0;
2944 bolacnt NUMBER := 0;
2945 pkslpacnt NUMBER := 0;
2946 /* Array Variables */
2947 oulist SARRAY := SARRAY();
2948 /* Local Variables */
2949 ou NUMBER;
2950 sob NUMBER;
2951 /* Indicator Variables */
2952 errLogged BOOLEAN;
2953 BEGIN
2954 SELECT COUNT(DOC_SEQUENCE_ID)
2955 INTO doccount
2956 FROM FND_DOCUMENT_SEQUENCES
2957 WHERE APPLICATION_ID = 665;
2958 IF ( doccount = 0 ) THEN
2959 mesg_name := 'ONT_SETVAL_SHIP_DOC_SEQ';
2960 error_type := 'ERROR';
2961 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
2962 null, null, null, null, null, null, null, null, null, null );
2963 END IF;
2964 SELECT COUNT(wdsc.DOC_SEQUENCE_CATEGORY_ID)
2965 INTO bolcount
2966 FROM WSH_DOC_SEQUENCE_CATEGORIES wdsc
2967 ,FND_DOC_SEQUENCE_CATEGORIES fdsc
2968 WHERE wdsc.CATEGORY_CODE = fdsc.CODE
2969 AND fdsc.NAME = 'BOL';
2970 IF ( bolcount = 0 ) THEN
2971 mesg_name := 'ONT_SETVAL_SHIP_DOC_BOL';
2972 error_type := 'WARNING';
2973 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
2974 null, null, null, null, null, null, null, null, null, null );
2975 END IF;
2976 SELECT COUNT(wdsc.DOC_SEQUENCE_CATEGORY_ID)
2977 INTO pkslpcnt
2978 FROM WSH_DOC_SEQUENCE_CATEGORIES wdsc
2979 ,FND_DOC_SEQUENCE_CATEGORIES fdsc
2980 WHERE wdsc.CATEGORY_CODE = fdsc.CODE
2981 AND fdsc.NAME = 'BOL';
2982 IF ( pkslpcnt = 0 ) THEN
2983 mesg_name := 'ONT_SETVAL_SHIP_DOC_PKSLP';
2984 error_type := 'WARNING';
2985 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
2986 null, null, null, null, null, null, null, null, null, null );
2987 END IF;
2988 IF ( P_LEVEL = 'OU' ) THEN
2989 oulist.extend(1);
2990 oulist(1) := P_VALUE;
2991 ELSE
2992 oulist.extend(4000);
2993 FOR i IN 1..RESP_LIST.COUNT
2994 LOOP
2995 ou := getOperatingUnit( RESP_LIST(i) );
2996 IF ( ou <> -1 ) THEN
2997 oulist(indx) := ou;
2998 indx := indx + 1;
2999 END IF;
3000 END LOOP;
3001 oulist := getUniqueList(trimArray(oulist));
3002 END IF;
3003 FOR i IN 1..oulist.COUNT
3004 LOOP
3005 -- Sys param Change
3006 -- Table oe_system_parameters_all is replaced by oe_sys_parameters_all
3007 SELECT COUNT(*)
3008 INTO omcnt
3009 FROM OE_SYS_PARAMETERS_ALL
3010 WHERE ORG_ID = oulist(i);
3011 IF omcnt > 0 THEN
3012 BEGIN
3013 SELECT SET_OF_BOOKS_ID
3014 INTO sob
3015 FROM HR_OPERATING_UNITS
3016 WHERE ORGANIZATION_ID = oulist(i);
3017 EXCEPTION
3018 WHEN NO_DATA_FOUND THEN
3019 sob := -1;
3020 END;
3021 SELECT COUNT(fdsa.DOC_SEQUENCE_ASSIGNMENT_ID)
3022 INTO bolacnt
3023 FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdsa
3024 ,FND_DOC_SEQUENCE_CATEGORIES fdsc
3025 WHERE SET_OF_BOOKS_ID = sob
3026 AND CATEGORY_CODE = fdsc.CODE
3027 AND fdsc.NAME = 'BOL';
3028 SELECT COUNT(fdsa.DOC_SEQUENCE_ASSIGNMENT_ID)
3029 INTO pkslpacnt
3030 FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdsa
3031 ,FND_DOC_SEQUENCE_CATEGORIES fdsc
3032 WHERE SET_OF_BOOKS_ID = sob
3033 AND CATEGORY_CODE = fdsc.CODE
3034 AND fdsc.NAME = 'PKSLP';
3035 IF ( bolacnt > 0 AND pkslpacnt > 0 ) THEN
3036 mesg_name := 'ONT_SETVAL_SHIP_DOC_NO_ASSGN';
3037 ELSE
3038 mesg_name := 'ONT_SETVAL_SHIP_DOC_SEQ_ASSGN';
3039 error_type := 'WARNING';
3040 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
3041 null, null, null, null, null, null, null, null, null, null );
3042 EXIT;
3043 END IF;
3044 END IF;
3045 END LOOP;
3046 END VALIDATE_DOC_SEQ_SHIPPING;
3047 PROCEDURE VALIDATE_SHIPPING_GRANTS_ROLES(P_LEVEL IN VARCHAR2, P_VALUE IN VARCHAR2)
3048 IS
3049 /* Report table variables */
3050 entity VARCHAR2(100) := 'Shipping Grants and Roles';
3051 error_type VARCHAR2(15) ;
3052 mesg_name VARCHAR2(255) ;
3053 /* Counter variables */
3054 rolecnt NUMBER := 1;
3055 /* Temporary Variables */
3056 roleid NUMBER;
3057 /* Indicator Variables */
3058 isError BOOLEAN := FALSE;
3059 errLogged BOOLEAN;
3060 BEGIN
3061 SELECT COUNT(ROLE_ID)
3062 INTO rolecnt
3063 FROM WSH_ROLES;
3064 IF rolecnt = 0 THEN
3065 error_type := 'ERROR';
3066 mesg_name := 'ONT_SETVAL_NOTFOUND_ROLES';
3067 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
3068 null, null, null, null, null, null, null, null, null, null );
3069 END IF;
3070 FOR i IN 1..USER_LIST.COUNT
3071 LOOP
3072 BEGIN
3073 SELECT ROLE_ID
3074 INTO roleid
3075 FROM WSH_GRANTS
3076 WHERE USER_ID = USER_LIST(i)
3077 AND ROWNUM < 2;
3078 isError := FALSE;
3079 EXIT;
3080 EXCEPTION
3081 WHEN NO_DATA_FOUND THEN
3082 isError := TRUE;
3083 END;
3084 END LOOP;
3085 IF isError = TRUE THEN
3086 error_type := 'ERROR';
3087 IF ( USER_LIST.COUNT = 1 ) THEN
3088 mesg_name := 'ONT_SETVAL_NOTFOUND_ROLES_USER';
3089 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
3090 USER_LIST(1), null, null, null, null, null, null, null, null, null );
3091 ELSE
3092 mesg_name := 'ONT_SETVAL_NOTFOUND_ROLE_USERS';
3093 errLogged := writeError( entity, p_level, p_value, error_type, mesg_name, null,
3094 null, null, null, null, null, null, null, null, null, null );
3095
3096 END IF;
3097 END IF;
3098 END VALIDATE_SHIPPING_GRANTS_ROLES;
3099 END OM_SETUP_VALID_PKG;
3100