[Home] [Help]
106: cursor get_hierarchy_csr
107: ( v_user_sequence number,
108: v_intf_type varchar2) IS
109: SELECT TERR_NAME, trim(HIERARCHY) hierarchy, ORG_ID, org_name
110: FROM JTY_WEBADI_OTH_TERR_INTF
111: where user_sequence = v_user_sequence
112: and interface_type = v_intf_type
113: and status is null;
114:
125: BEGIN
126:
127: x_return_status := FND_API.G_RET_STS_SUCCESS;
128: /*
129: update JTY_WEBADI_OTH_TERR_INTF
130: set parent_terr_id = null
131: where user_sequence = p_user_sequence
132: and interface_type = p_intf_type
133: and hierarchy is null;
146: if l_hierarchy_tbl(c).hierarchy is null then
147: -- no hierarchy found. Root node
148: l_parent := l_hierarchy_tbl(c).org_name;
149:
150: update JTY_WEBADI_OTH_TERR_INTF
151: set parent_terr_id = l_parent_id
152: , parent_terr_name = l_parent
153: where terr_name = l_hierarchy_tbl(c).terr_name
154: and user_sequence = p_user_sequence
172: and org_id = l_hierarchy_tbl(c).org_id;
173:
174: case l_count
175: when 0 then
176: update JTY_WEBADI_OTH_TERR_INTF
177: set parent_terr_id = null
178: , parent_terr_name = l_parent
179: where terr_name = l_hierarchy_tbl(c).terr_name
180: and user_sequence = p_user_sequence
186: where org_id = l_hierarchy_tbl(c).org_id
187: and name = l_parent
188: and parent_territory_id = l_anc_id;
189:
190: update JTY_WEBADI_OTH_TERR_INTF
191: set parent_terr_id = l_parent_id
192: , parent_terr_name = l_parent
193: where terr_name = l_hierarchy_tbl(c).terr_name
194: and user_sequence = p_user_sequence
198: fnd_message.clear;
199: FND_MESSAGE.set_name ('JTF', 'JTY_OTH_TERR_NON_UNIQUE_TERR');
200: FND_MESSAGE.set_token ('POSITION', l_parent);
201: X_Msg_Data := fnd_message.get();
202: update JTY_WEBADI_OTH_TERR_INTF
203: set status = x_return_status
204: , error_msg = X_Msg_Data
205: where terr_name = l_hierarchy_tbl(c).terr_name
206: and user_sequence = p_user_sequence
221: when 0 then
222: l_parent := substr(l_terr,instr(l_terr,'->',-1)+2);
223: --dbms_output.put_line('case 0, l_parent: '||l_parent);
224:
225: update JTY_WEBADI_OTH_TERR_INTF
226: set parent_terr_id = null
227: , parent_terr_name = l_parent
228: where terr_name = l_hierarchy_tbl(c).terr_name
229: and user_sequence = p_user_sequence
241: FND_MESSAGE.set_name ('JTF', 'JTY_OTH_TERR_NON_UNIQUE_TERR');
242: FND_MESSAGE.set_token ('POSITION', l_parent);
243: X_Msg_Data := fnd_message.get();
244:
245: update JTY_WEBADI_OTH_TERR_INTF
246: set status = x_return_status
247: , error_msg = X_Msg_Data
248: where terr_name = l_hierarchy_tbl(c).terr_name
249: and user_sequence = p_user_sequence
287: from JTY_WEBADI_QUAL_HEADER jqh,
288: (
289: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
290: 1 qual_num
291: FROM JTY_WEBADI_OTH_TERR_INTF jut
292: where jut.USER_SEQUENCE = v_user_sequence
293: and jut.status is null
294: and jut.qual1_value1 is not null
295: and jut.INTERFACE_TYPE = v_intf_type
296: and jut.header = v_header
297: union all
298: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
299: 2 qual_num
300: FROM JTY_WEBADI_OTH_TERR_INTF jut
301: where jut.USER_SEQUENCE = v_user_sequence
302: and jut.status is null
303: and jut.qual2_value1 is not null
304: and jut.INTERFACE_TYPE = v_intf_type
305: and jut.header = v_header
306: union all
307: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
308: 3 qual_num
309: FROM JTY_WEBADI_OTH_TERR_INTF jut
310: where jut.USER_SEQUENCE = v_user_sequence
311: and jut.status is null
312: and jut.qual3_value1 is not null
313: and jut.INTERFACE_TYPE = v_intf_type
314: and jut.header = v_header
315: union all
316: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
317: 4 qual_num
318: FROM JTY_WEBADI_OTH_TERR_INTF jut
319: where jut.USER_SEQUENCE = v_user_sequence
320: and jut.status is null
321: and jut.qual4_value1 is not null
322: and jut.INTERFACE_TYPE = v_intf_type
323: and jut.header = v_header
324: union all
325: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
326: 5 qual_num
327: FROM JTY_WEBADI_OTH_TERR_INTF jut
328: where jut.USER_SEQUENCE = v_user_sequence
329: and jut.status is null
330: and jut.qual5_value1 is not null
331: and jut.INTERFACE_TYPE = v_intf_type
332: and jut.header = v_header
333: union all
334: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
335: 6 qual_num
336: FROM JTY_WEBADI_OTH_TERR_INTF jut
337: where jut.USER_SEQUENCE = v_user_sequence
338: and jut.status is null
339: and jut.qual6_value1 is not null
340: and jut.INTERFACE_TYPE = v_intf_type
341: and jut.header = v_header
342: union all
343: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
344: 7 qual_num
345: FROM JTY_WEBADI_OTH_TERR_INTF jut
346: where jut.USER_SEQUENCE = v_user_sequence
347: and jut.status is null
348: and jut.qual7_value1 is not null
349: and jut.INTERFACE_TYPE = v_intf_type
350: and jut.header = v_header
351: union all
352: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
353: 8 qual_num
354: FROM JTY_WEBADI_OTH_TERR_INTF jut
355: where jut.USER_SEQUENCE = v_user_sequence
356: and jut.status is null
357: and jut.qual8_value1 is not null
358: and jut.INTERFACE_TYPE = v_intf_type
359: and jut.header = v_header
360: union all
361: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
362: 9 qual_num
363: FROM JTY_WEBADI_OTH_TERR_INTF jut
364: where jut.USER_SEQUENCE = v_user_sequence
365: and jut.status is null
366: and jut.qual9_value1 is not null
367: and jut.INTERFACE_TYPE = v_intf_type
368: and jut.header = v_header
369: union all
370: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
371: 10 qual_num
372: FROM JTY_WEBADI_OTH_TERR_INTF jut
373: where jut.USER_SEQUENCE = v_user_sequence
374: and jut.status is null
375: and jut.qual10_value1 is not null
376: and jut.INTERFACE_TYPE = v_intf_type
377: and jut.header = v_header
378: union all
379: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
380: 11 qual_num
381: FROM JTY_WEBADI_OTH_TERR_INTF jut
382: where jut.USER_SEQUENCE = v_user_sequence
383: and jut.status is null
384: and jut.qual11_value1 is not null
385: and jut.INTERFACE_TYPE = v_intf_type
386: and jut.header = v_header
387: union all
388: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
389: 12 qual_num
390: FROM JTY_WEBADI_OTH_TERR_INTF jut
391: where jut.USER_SEQUENCE = v_user_sequence
392: and jut.status is null
393: and jut.qual12_value1 is not null
394: and jut.INTERFACE_TYPE = v_intf_type
395: and jut.header = v_header
396: union all
397: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
398: 13 qual_num
399: FROM JTY_WEBADI_OTH_TERR_INTF jut
400: where jut.USER_SEQUENCE = v_user_sequence
401: and jut.status is null
402: and jut.qual13_value1 is not null
403: and jut.INTERFACE_TYPE = v_intf_type
404: and jut.header = v_header
405: union all
406: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
407: 14 qual_num
408: FROM JTY_WEBADI_OTH_TERR_INTF jut
409: where jut.USER_SEQUENCE = v_user_sequence
410: and jut.status is null
411: and jut.qual14_value1 is not null
412: and jut.INTERFACE_TYPE = v_intf_type
413: and jut.header = v_header
414: union all
415: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
416: 15 qual_num
417: FROM JTY_WEBADI_OTH_TERR_INTF jut
418: where jut.USER_SEQUENCE = v_user_sequence
419: and jut.status is null
420: and jut.qual15_value1 is not null
421: and jut.INTERFACE_TYPE = v_intf_type
422: and jut.header = v_header
423: union all
424: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
425: 16 qual_num
426: FROM JTY_WEBADI_OTH_TERR_INTF jut
427: where jut.USER_SEQUENCE = v_user_sequence
428: and jut.status is null
429: and jut.qual16_value1 is not null
430: and jut.INTERFACE_TYPE = v_intf_type
431: and jut.header = v_header
432: union all
433: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
434: 17 qual_num
435: FROM JTY_WEBADI_OTH_TERR_INTF jut
436: where jut.USER_SEQUENCE = v_user_sequence
437: and jut.status is null
438: and jut.qual17_value1 is not null
439: and jut.INTERFACE_TYPE = v_intf_type
440: and jut.header = v_header
441: union all
442: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
443: 18 qual_num
444: FROM JTY_WEBADI_OTH_TERR_INTF jut
445: where jut.USER_SEQUENCE = v_user_sequence
446: and jut.status is null
447: and jut.qual18_value1 is not null
448: and jut.INTERFACE_TYPE = v_intf_type
449: and jut.header = v_header
450: union all
451: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
452: 19 qual_num
453: FROM JTY_WEBADI_OTH_TERR_INTF jut
454: where jut.USER_SEQUENCE = v_user_sequence
455: and jut.status is null
456: and jut.qual19_value1 is not null
457: and jut.INTERFACE_TYPE = v_intf_type
458: and jut.header = v_header
459: union all
460: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
461: 20 qual_num
462: FROM JTY_WEBADI_OTH_TERR_INTF jut
463: where jut.USER_SEQUENCE = v_user_sequence
464: and jut.status is null
465: and jut.qual20_value1 is not null
466: and jut.INTERFACE_TYPE = v_intf_type
467: and jut.header = v_header
468: union all
469: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
470: 21 qual_num
471: FROM JTY_WEBADI_OTH_TERR_INTF jut
472: where jut.USER_SEQUENCE = v_user_sequence
473: and jut.status is null
474: and jut.qual21_value1 is not null
475: and jut.INTERFACE_TYPE = v_intf_type
476: and jut.header = v_header
477: union all
478: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
479: 22 qual_num
480: FROM JTY_WEBADI_OTH_TERR_INTF jut
481: where jut.USER_SEQUENCE = v_user_sequence
482: and jut.status is null
483: and jut.qual22_value1 is not null
484: and jut.INTERFACE_TYPE = v_intf_type
485: and jut.header = v_header
486: union all
487: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
488: 23 qual_num
489: FROM JTY_WEBADI_OTH_TERR_INTF jut
490: where jut.USER_SEQUENCE = v_user_sequence
491: and jut.status is null
492: and jut.qual23_value1 is not null
493: and jut.INTERFACE_TYPE = v_intf_type
494: and jut.header = v_header
495: union all
496: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
497: 24 qual_num
498: FROM JTY_WEBADI_OTH_TERR_INTF jut
499: where jut.USER_SEQUENCE = v_user_sequence
500: and jut.status is null
501: and jut.qual24_value1 is not null
502: and jut.INTERFACE_TYPE = v_intf_type
503: and jut.header = v_header
504: union all
505: select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
506: 25 qual_num
507: FROM JTY_WEBADI_OTH_TERR_INTF jut
508: where jut.USER_SEQUENCE = v_user_sequence
509: and jut.status is null
510: and jut.qual25_value1 is not null
511: and jut.INTERFACE_TYPE = v_intf_type
527: fnd_message.clear;
528: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_NAME');
529: X_Msg_Data := fnd_message.get();
530:
531: update JTY_WEBADI_OTH_TERR_INTF jwot
532: set status = x_return_status,
533: error_msg = X_Msg_Data
534: where jwot.terr_name is null
535: and jwot.USER_SEQUENCE = p_USER_SEQUENCE
543: fnd_message.clear;
544: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ID');
545: X_Msg_Data := fnd_message.get();
546:
547: update JTY_WEBADI_OTH_TERR_INTF jwot
548: set status = x_return_status,
549: error_msg = X_Msg_Data
550: where ( (jwot.TERR_ID IS NULL and jwot.header = 'TERR')
551: OR (jwot.header = 'QUAL' and jwot.TERR_QUAL_ID1 IS NULL
577: fnd_message.clear;
578: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_TEMPLATE');
579: X_Msg_Data := fnd_message.get();
580:
581: update JTY_WEBADI_OTH_TERR_INTF jwot
582: set jwot.status = x_return_status,
583: jwot.error_msg = X_Msg_Data
584: where not exists ( SELECT 1
585: FROM jtf_terr_all jt
597: fnd_message.clear;
598: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_NO_TEMPLATE');
599: X_Msg_Data := fnd_message.get();
600:
601: update JTY_WEBADI_OTH_TERR_INTF jwot
602: set status = x_return_status,
603: error_msg = X_Msg_Data
604: where jwot.TERR_TYPE_ID is null
605: AND jwot.user_sequence = p_user_sequence
613: fnd_message.clear;
614: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ACCESS');
615: X_Msg_Data := fnd_message.get();
616:
617: update JTY_WEBADI_OTH_TERR_INTF jwot
618: set jwot.status = x_return_status,
619: jwot.error_msg = X_Msg_Data
620: where exists
621: (select 1 from JTY_WEBADI_RESOURCES jwr
666: x_return_status := FND_API.G_RET_STS_ERROR;
667: fnd_message.clear;
668: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ACCESS');
669: X_Msg_Data := fnd_message.get();
670: update JTY_WEBADI_OTH_TERR_INTF jwot
671: set jwot.status = x_return_status,
672: jwot.error_msg = X_Msg_Data
673: where exists
674: (select 1 from JTY_WEBADI_RESOURCES jwr
699: fnd_message.clear;
700: fnd_message.set_name ('JTF', 'JTF_TTY_DUPLICATE_RESOURCE');
701: X_Msg_Data := fnd_message.get();
702:
703: update JTY_WEBADI_OTH_TERR_INTF jwot
704: set status = x_return_status
705: , error_msg = X_Msg_Data
706: where jwot.USER_SEQUENCE = p_USER_SEQUENCE
707: and jwot.INTERFACE_TYPE = p_intf_type
726: fnd_message.clear;
727: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_RSC_START_DATE');
728: X_Msg_Data := fnd_message.get();
729:
730: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
731: set jwot.status = x_return_status,
732: jwot.error_msg = X_Msg_Data
733: where exists
734: (select 1 from JTY_WEBADI_RESOURCES jwr
748: fnd_message.clear;
749: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_RSC_END_DATE');
750: X_Msg_Data := fnd_message.get();
751:
752: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
753: set jwot.status = x_return_status,
754: jwot.error_msg = X_Msg_Data
755: where exists
756: (select 1 from JTY_WEBADI_RESOURCES jwr
778: close get_qual_csr;
779:
780: if qual_rec.lay_seq_num.count > 0 then
781: forall i in qual_rec.lay_seq_num.first..qual_rec.lay_seq_num.last
782: update JTY_WEBADI_OTH_TERR_INTF jwot
783: set jwot.status = x_return_status,
784: jwot.error_msg = decode(jwot.error_msg,null,X_Msg_Data||': '||qual_rec.qualifier_name(i),
785: jwot.error_msg ||', ' || qual_rec.qualifier_name(i))
786: where jwot.lay_seq_num = qual_rec.lay_seq_num(i)
795: fnd_message.clear;
796: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_START_DATE');
797: X_Msg_Data := fnd_message.get();
798:
799: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
800: set status = x_return_status,
801: error_msg = X_Msg_Data
802: where jwot.interface_type = p_intf_type
803: AND jwot.HEADER= l_header
805: AND jwot.hierarchy is not null
806: and jwot.TERR_START_DATE is not null
807: and jwot.status is null
808: and ( exists ( select 1
809: from JTY_WEBADI_OTH_TERR_INTF jwot2
810: where jwot.parent_terr_id is null
811: and NOT(jwot.TERR_START_DATE between NVL(jwot2.terr_start_date,SYSDATE)
812: and NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,SYSDATE), 12)))
813: and jwot.parent_terr_name = jwot2.terr_name)
821: fnd_message.clear;
822: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_END_DATE');
823: X_Msg_Data := fnd_message.get();
824:
825: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
826: set status = x_return_status,
827: error_msg = X_Msg_Data
828: where jwot.interface_type = p_intf_type
829: AND jwot.HEADER= l_header
831: AND jwot.hierarchy is not null
832: AND jwot.TERR_END_DATE is not null
833: and jwot.status is null
834: and (exists ( select 1
835: from JTY_WEBADI_OTH_TERR_INTF jwot2
836: where jwot.parent_terr_id is null
837: and NOT (jwot.TERR_END_DATE between NVL(jwot2.terr_start_date,SYSDATE)
838: and NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,SYSDATE), 12)))
839: and jwot.parent_terr_name = jwot2.terr_name)
847: fnd_message.clear;
848: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_NO_ACCESS');
849: X_Msg_Data := fnd_message.get();
850:
851: update JTY_WEBADI_OTH_TERR_INTF jwot
852: set status = x_return_status,
853: error_msg = X_Msg_Data
854: where (jwot.org_id is null
855: or mo_global.check_access(jwot.org_id) <> 'Y')
856: and jwot.USER_SEQUENCE = p_USER_SEQUENCE
857: and jwot.INTERFACE_TYPE = p_intf_type
858: and jwot.status is null;
859:
860: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
861: set (status,error_msg) = (SELECT jwot2.STATUS, jwot2.error_msg
862: FROM JTY_WEBADI_OTH_TERR_INTF jwot2
863: where jwot.terr_name = jwot2.terr_name
864: and jwot2.header = l_header
858: and jwot.status is null;
859:
860: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
861: set (status,error_msg) = (SELECT jwot2.STATUS, jwot2.error_msg
862: FROM JTY_WEBADI_OTH_TERR_INTF jwot2
863: where jwot.terr_name = jwot2.terr_name
864: and jwot2.header = l_header
865: and jwot.USER_SEQUENCE = jwot2.USER_SEQUENCE
866: and jwot.INTERFACE_TYPE = jwot2.INTERFACE_TYPE)
871:
872: x_return_status := FND_API.G_RET_STS_SUCCESS;
873:
874: select count(*) into l_count
875: from JTY_WEBADI_OTH_TERR_INTF jwot
876: where status is not null
877: and jwot.USER_SEQUENCE = p_USER_SEQUENCE
878: and jwot.INTERFACE_TYPE = p_intf_type;
879:
879:
880: --dbms_output.put_line ('l_count: '||l_count);
881:
882: if l_count > 0 then
883: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
884: set status = x_return_status
885: where status is null
886: and jwot.USER_SEQUENCE = p_USER_SEQUENCE
887: and jwot.INTERFACE_TYPE = p_intf_type;
907:
908: l_login_id := fnd_global.login_id;
909: l_intf_type := p_intf_type;
910:
911: update JTY_WEBADI_OTH_TERR_INTF jwot
912: set LAST_UPDATE_LOGIN = l_login_id,
913: (terr_start_date,TERR_END_DATE) =
914: (SELECT NVL(jwot2.TERR_START_DATE,TRUNC(SYSDATE)),
915: NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,TRUNC(SYSDATE)), 12) )
912: set LAST_UPDATE_LOGIN = l_login_id,
913: (terr_start_date,TERR_END_DATE) =
914: (SELECT NVL(jwot2.TERR_START_DATE,TRUNC(SYSDATE)),
915: NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,TRUNC(SYSDATE)), 12) )
916: FROM JTY_WEBADI_OTH_TERR_INTF jwot2
917: WHERE jwot.terr_name = jwot2.terr_name
918: and jwot.user_sequence = jwot2.user_sequence
919: and jwot2.header = 'TERR'
920: and rownum = 1),
918: and jwot.user_sequence = jwot2.user_sequence
919: and jwot2.header = 'TERR'
920: and rownum = 1),
921: usage_id = (select jwot2.usage_id from
922: JTY_WEBADI_OTH_TERR_INTF jwot2
923: where jwot.user_sequence = jwot2.user_sequence
924: and interface_type = 'D'
925: and jwot2.usage_id is not null
926: and rownum = 1)
928: and jwot.interface_type = l_intf_type
929: and jwot.action_flag = l_action_flag
930: and jwot.status is null;
931:
932: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
933: SET (TERR_ID, TERR_START_DATE, TERR_END_DATE) =
934: (select jta.terr_id, NVL(jwot.TERR_START_DATE,jta.START_DATE_ACTIVE)
935: , NVL(jwot.TERR_END_DATE,jta.END_DATE_ACTIVE)
936: from jtf_terr_all jta
946: --and jta.parent_territory_id = jwot.parent_terr_id
947: ) ;
948:
949: --dbms_output.put_line(' # records processed for intf table: '||SQL%ROWCOUNT);
950: update JTY_WEBADI_OTH_TERR_INTF
951: set QUAL1_VALUE_ID = NULL, QUAL2_VALUE_ID = NULL
952: , QUAL3_VALUE_ID = NULL, QUAL4_VALUE_ID = NULL
953: , QUAL5_VALUE_ID = NULL, QUAL6_VALUE_ID = NULL
954: , QUAL7_VALUE_ID = NULL, QUAL8_VALUE_ID = NULL
966: and user_sequence = p_user_sequence
967: and action_flag = l_action_flag
968: and header = 'QUAL';
969:
970: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
971: set jwot.terr_id = null
972: where jwot.status is null
973: and jwot.interface_type = l_intf_type
974: and jwot.user_sequence = p_user_sequence
972: where jwot.status is null
973: and jwot.interface_type = l_intf_type
974: and jwot.user_sequence = p_user_sequence
975: and jwot.action_flag = l_action_flag
976: and exists (select 1 from JTY_WEBADI_OTH_TERR_INTF jwot2
977: where jwot.terr_name = jwot2.terr_name
978: and jwot.action_flag = jwot2.action_flag
979: and jwot.user_sequence = jwot2.user_sequence
980: and jwot.interface_type = jwot2.interface_type
981: and jwot2.header = 'TERR');
982:
983: UPDATE JTY_WEBADI_RESOURCES jwr
984: SET TERR_ID = (select jwot.terr_id
985: from JTY_WEBADI_OTH_TERR_INTF jwot
986: where jwr.lay_seq_num = jwot.lay_seq_num
987: and rownum = 1) ,
988: TERR_RSC_ID = NULL,
989: TERR_RSC_ACCESS_ID1 = NULL, TERR_RSC_ACCESS_ID2 = NULL,
991: TERR_RSC_ACCESS_ID5 = NULL, TERR_RSC_ACCESS_ID6 = NULL,
992: TERR_RSC_ACCESS_ID7 = NULL, TERR_RSC_ACCESS_ID8 = NULL,
993: TERR_RSC_ACCESS_ID9 = NULL, TERR_RSC_ACCESS_ID10 = NULL
994: where exists
995: ( select 1 from JTY_WEBADI_OTH_TERR_INTF jwot
996: where jwot.interface_type = l_intf_type
997: and jwot.user_sequence = p_user_sequence
998: and jwot.status is null
999: and jwot.action_flag = l_action_flag
1015: v_intf_type varchar2,
1016: v_qual_num number
1017: ) IS
1018: select jtq.terr_qual_id, jwot.lay_seq_num
1019: from JTY_WEBADI_QUAL_HEADER qgt , jtf_terr_qual_all jtq, JTY_WEBADI_OTH_TERR_INTF jwot
1020: WHERE qgt.qual_usg_id = jtq.qual_usg_id
1021: and jtq.org_id = jwot.org_id
1022: AND qgt.user_sequence = jwot.user_sequence
1023: AND jtq.terr_id = jwot.terr_id
1047: --dbms_output.put_line ('i: '||i);
1048: case i
1049: when 1 then
1050: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1051: update JTY_WEBADI_OTH_TERR_INTF jwot
1052: set jwot.terr_qual_id1 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1053: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1054: and jwot.header = p_header
1055: and jwot.user_sequence = p_user_sequence
1057: and jwot.status is null;
1058: --dbms_output.put_line('1 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1059: when 2 then
1060: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1061: update JTY_WEBADI_OTH_TERR_INTF jwot
1062: set jwot.terr_qual_id2 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1063: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1064: and jwot.header = p_header
1065: and jwot.user_sequence = p_user_sequence
1067: and jwot.status is null;
1068: --dbms_output.put_line('2 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1069: when 3 then
1070: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1071: update JTY_WEBADI_OTH_TERR_INTF jwot
1072: set jwot.terr_qual_id3 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1073: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1074: and jwot.header = p_header
1075: and jwot.user_sequence = p_user_sequence
1077: and jwot.status is null;
1078: --dbms_output.put_line('3 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1079: when 4 then
1080: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1081: update JTY_WEBADI_OTH_TERR_INTF jwot
1082: set jwot.terr_qual_id4 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1083: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1084: and jwot.header = p_header
1085: and jwot.user_sequence = p_user_sequence
1087: and jwot.status is null;
1088: --dbms_output.put_line('4 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1089: when 5 then
1090: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1091: update JTY_WEBADI_OTH_TERR_INTF jwot
1092: set jwot.terr_qual_id5 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1093: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1094: and jwot.header = p_header
1095: and jwot.user_sequence = p_user_sequence
1097: and jwot.status is null;
1098: --dbms_output.put_line('5 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1099: when 6 then
1100: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1101: update JTY_WEBADI_OTH_TERR_INTF jwot
1102: set jwot.terr_qual_id6 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1103: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1104: and jwot.header = p_header
1105: and jwot.user_sequence = p_user_sequence
1107: and jwot.status is null;
1108: --dbms_output.put_line('6 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1109: when 7 then
1110: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1111: update JTY_WEBADI_OTH_TERR_INTF jwot
1112: set jwot.terr_qual_id7 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1113: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1114: and jwot.header = p_header
1115: and jwot.user_sequence = p_user_sequence
1117: and jwot.status is null;
1118: --dbms_output.put_line('7 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1119: when 8 then
1120: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1121: update JTY_WEBADI_OTH_TERR_INTF jwot
1122: set jwot.terr_qual_id8 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1123: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1124: and jwot.header = p_header
1125: and jwot.user_sequence = p_user_sequence
1127: and jwot.status is null;
1128: --dbms_output.put_line('8 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1129: when 9 then
1130: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1131: update JTY_WEBADI_OTH_TERR_INTF jwot
1132: set jwot.terr_qual_id9 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1133: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1134: and jwot.header = p_header
1135: and jwot.user_sequence = p_user_sequence
1137: and jwot.status is null;
1138: --dbms_output.put_line('9 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1139: when 10 then
1140: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1141: update JTY_WEBADI_OTH_TERR_INTF jwot
1142: set jwot.terr_qual_id10 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1143: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1144: and jwot.header = p_header
1145: and jwot.user_sequence = p_user_sequence
1147: and jwot.status is null;
1148: --dbms_output.put_line('10 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1149: when 11 then
1150: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1151: update JTY_WEBADI_OTH_TERR_INTF jwot
1152: set jwot.terr_qual_id11 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1153: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1154: and jwot.header = p_header
1155: and jwot.user_sequence = p_user_sequence
1157: and jwot.status is null;
1158: --dbms_output.put_line('11 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1159: when 12 then
1160: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1161: update JTY_WEBADI_OTH_TERR_INTF jwot
1162: set jwot.terr_qual_id12 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1163: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1164: and jwot.header = p_header
1165: and jwot.user_sequence = p_user_sequence
1167: and jwot.status is null;
1168: --dbms_output.put_line('12 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1169: when 13 then
1170: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1171: update JTY_WEBADI_OTH_TERR_INTF jwot
1172: set jwot.terr_qual_id13 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1173: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1174: and jwot.header = p_header
1175: and jwot.user_sequence = p_user_sequence
1177: and jwot.status is null;
1178: --dbms_output.put_line('13 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1179: when 14 then
1180: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1181: update JTY_WEBADI_OTH_TERR_INTF jwot
1182: set jwot.terr_qual_id14 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1183: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1184: and jwot.header = p_header
1185: and jwot.user_sequence = p_user_sequence
1187: and jwot.status is null;
1188: --dbms_output.put_line('14 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1189: when 15 then
1190: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1191: update JTY_WEBADI_OTH_TERR_INTF jwot
1192: set jwot.terr_qual_id15 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1193: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1194: and jwot.header = p_header
1195: and jwot.user_sequence = p_user_sequence
1197: and jwot.status is null;
1198: --dbms_output.put_line('15 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1199: when 16 then
1200: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1201: update JTY_WEBADI_OTH_TERR_INTF jwot
1202: set jwot.terr_qual_id16 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1203: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1204: and jwot.header = p_header
1205: and jwot.user_sequence = p_user_sequence
1207: and jwot.status is null;
1208: --dbms_output.put_line('16 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1209: when 17 then
1210: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1211: update JTY_WEBADI_OTH_TERR_INTF jwot
1212: set jwot.terr_qual_id17 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1213: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1214: and jwot.header = p_header
1215: and jwot.user_sequence = p_user_sequence
1217: and jwot.status is null;
1218: --dbms_output.put_line('17 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1219: when 18 then
1220: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1221: update JTY_WEBADI_OTH_TERR_INTF jwot
1222: set jwot.terr_qual_id18 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1223: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1224: and jwot.header = p_header
1225: and jwot.user_sequence = p_user_sequence
1227: and jwot.status is null;
1228: --dbms_output.put_line('18 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1229: when 19 then
1230: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1231: update JTY_WEBADI_OTH_TERR_INTF jwot
1232: set jwot.terr_qual_id19 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1233: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1234: and jwot.header = p_header
1235: and jwot.user_sequence = p_user_sequence
1237: and jwot.status is null;
1238: --dbms_output.put_line('19 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1239: when 20 then
1240: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1241: update JTY_WEBADI_OTH_TERR_INTF jwot
1242: set jwot.terr_qual_id20 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1243: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1244: and jwot.header = p_header
1245: and jwot.user_sequence = p_user_sequence
1247: and jwot.status is null;
1248: --dbms_output.put_line('20 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1249: when 21 then
1250: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1251: update JTY_WEBADI_OTH_TERR_INTF jwot
1252: set jwot.terr_qual_id21 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1253: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1254: and jwot.header = p_header
1255: and jwot.user_sequence = p_user_sequence
1257: and jwot.status is null;
1258: --dbms_output.put_line('21 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1259: when 22 then
1260: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1261: update JTY_WEBADI_OTH_TERR_INTF jwot
1262: set jwot.terr_qual_id22 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1263: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1264: and jwot.header = p_header
1265: and jwot.user_sequence = p_user_sequence
1267: and jwot.status is null;
1268: --dbms_output.put_line('22 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1269: when 23 then
1270: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1271: update JTY_WEBADI_OTH_TERR_INTF jwot
1272: set jwot.terr_qual_id23 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1273: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1274: and jwot.header = p_header
1275: and jwot.user_sequence = p_user_sequence
1277: and jwot.status is null;
1278: --dbms_output.put_line('23 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1279: when 24 then
1280: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1281: update JTY_WEBADI_OTH_TERR_INTF jwot
1282: set jwot.terr_qual_id24 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1283: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1284: and jwot.header = p_header
1285: and jwot.user_sequence = p_user_sequence
1287: and jwot.status is null;
1288: --dbms_output.put_line('24 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
1289: when 25 then
1290: forall j in l_terr_qual_rec.lay_seq_num.first..l_terr_qual_rec.lay_seq_num.last
1291: update JTY_WEBADI_OTH_TERR_INTF jwot
1292: set jwot.terr_qual_id25 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
1293: where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
1294: and jwot.header = p_header
1295: and jwot.user_sequence = p_user_sequence
1300: end case;
1301: end if;
1302: end loop;
1303:
1304: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
1305: SET ACTION_FLAG = 'U'
1306: WHERE LAY_SEQ_NUM not in
1307: ( SELECT MIN(jwot2.LAY_SEQ_NUM)
1308: FROM JTY_WEBADI_OTH_TERR_INTF jwot2
1304: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
1305: SET ACTION_FLAG = 'U'
1306: WHERE LAY_SEQ_NUM not in
1307: ( SELECT MIN(jwot2.LAY_SEQ_NUM)
1308: FROM JTY_WEBADI_OTH_TERR_INTF jwot2
1309: WHERE jwot.ACTION_FLAG = jwot2.ACTION_FLAG
1310: and jwot.header = jwot2.header
1311: AND jwot.USER_SEQUENCE = jwot2.USER_SEQUENCE
1312: AND jwot.interface_type = jwot2.interface_type
1331: v_intf_type varchar2,
1332: v_header varchar2
1333: ) IS
1334: SELECT TERR_ID, lay_seq_num
1335: FROM JTY_WEBADI_OTH_TERR_INTF
1336: WHERE interface_type = v_intf_type
1337: AND action_flag = v_action_flag
1338: AND user_sequence = v_user_sequence
1339: AND header = v_header
1346: v_header varchar2
1347: ) IS
1348: select sub.terr_qual_id, sub.lay_seq_num
1349: from ( select terr_qual_id1 terr_qual_id, lay_seq_num
1350: from JTY_WEBADI_OTH_TERR_INTF jwot
1351: WHERE jwot.user_sequence = v_user_sequence
1352: and jwot.action_flag = v_action_flag
1353: and jwot.interface_type = v_intf_type
1354: and jwot.header = v_header
1355: and jwot.qual1_value_id is not null
1356: and jwot.status is null
1357: union all
1358: select terr_qual_id2 terr_qual_id, lay_seq_num
1359: from JTY_WEBADI_OTH_TERR_INTF jwot
1360: WHERE jwot.user_sequence = v_user_sequence
1361: and jwot.action_flag = v_action_flag
1362: and jwot.interface_type = v_intf_type
1363: and jwot.header = v_header
1364: and jwot.qual2_value_id is not null
1365: and jwot.status is null
1366: union all
1367: select terr_qual_id3 terr_qual_id, lay_seq_num
1368: from JTY_WEBADI_OTH_TERR_INTF jwot
1369: WHERE jwot.user_sequence = v_user_sequence
1370: and jwot.action_flag = v_action_flag
1371: and jwot.interface_type = v_intf_type
1372: and jwot.header = v_header
1373: and jwot.qual3_value_id is not null
1374: and jwot.status is null
1375: union all
1376: select terr_qual_id4 terr_qual_id, lay_seq_num
1377: from JTY_WEBADI_OTH_TERR_INTF jwot
1378: WHERE jwot.user_sequence = v_user_sequence
1379: and jwot.action_flag = v_action_flag
1380: and jwot.interface_type = v_intf_type
1381: and jwot.header = v_header
1382: and jwot.qual4_value_id is not null
1383: and jwot.status is null
1384: union all
1385: select terr_qual_id5 terr_qual_id, lay_seq_num
1386: from JTY_WEBADI_OTH_TERR_INTF jwot
1387: WHERE jwot.user_sequence = v_user_sequence
1388: and jwot.action_flag = v_action_flag
1389: and jwot.interface_type = v_intf_type
1390: and jwot.header = v_header
1391: and jwot.qual5_value_id is not null
1392: and jwot.status is null
1393: union all
1394: select terr_qual_id6 terr_qual_id, lay_seq_num
1395: from JTY_WEBADI_OTH_TERR_INTF jwot
1396: WHERE jwot.user_sequence = v_user_sequence
1397: and jwot.action_flag = v_action_flag
1398: and jwot.interface_type = v_intf_type
1399: and jwot.header = v_header
1400: and jwot.qual6_value_id is not null
1401: and jwot.status is null
1402: union all
1403: select terr_qual_id7 terr_qual_id, lay_seq_num
1404: from JTY_WEBADI_OTH_TERR_INTF jwot
1405: WHERE jwot.user_sequence = v_user_sequence
1406: and jwot.action_flag = v_action_flag
1407: and jwot.interface_type = v_intf_type
1408: and jwot.header = v_header
1409: and jwot.qual7_value_id is not null
1410: and jwot.status is null
1411: union all
1412: select terr_qual_id8 terr_qual_id, lay_seq_num
1413: from JTY_WEBADI_OTH_TERR_INTF jwot
1414: WHERE jwot.user_sequence = v_user_sequence
1415: and jwot.action_flag = v_action_flag
1416: and jwot.interface_type = v_intf_type
1417: and jwot.header = v_header
1418: and jwot.qual8_value_id is not null
1419: and jwot.status is null
1420: union all
1421: select terr_qual_id9 terr_qual_id, lay_seq_num
1422: from JTY_WEBADI_OTH_TERR_INTF jwot
1423: WHERE jwot.user_sequence = v_user_sequence
1424: and jwot.action_flag = v_action_flag
1425: and jwot.interface_type = v_intf_type
1426: and jwot.header = v_header
1427: and jwot.qual9_value_id is not null
1428: and jwot.status is null
1429: union all
1430: select terr_qual_id10 terr_qual_id, lay_seq_num
1431: from JTY_WEBADI_OTH_TERR_INTF jwot
1432: WHERE jwot.user_sequence = v_user_sequence
1433: and jwot.action_flag = v_action_flag
1434: and jwot.interface_type = v_intf_type
1435: and jwot.header = v_header
1436: and jwot.qual10_value_id is not null
1437: and jwot.status is null
1438: union all
1439: select terr_qual_id11 terr_qual_id, lay_seq_num
1440: from JTY_WEBADI_OTH_TERR_INTF jwot
1441: WHERE jwot.user_sequence = v_user_sequence
1442: and jwot.action_flag = v_action_flag
1443: and jwot.interface_type = v_intf_type
1444: and jwot.header = v_header
1445: and jwot.qual11_value_id is not null
1446: and jwot.status is null
1447: union all
1448: select terr_qual_id12 terr_qual_id, lay_seq_num
1449: from JTY_WEBADI_OTH_TERR_INTF jwot
1450: WHERE jwot.user_sequence = v_user_sequence
1451: and jwot.action_flag = v_action_flag
1452: and jwot.interface_type = v_intf_type
1453: and jwot.header = v_header
1454: and jwot.qual12_value_id is not null
1455: and jwot.status is null
1456: union all
1457: select terr_qual_id13 terr_qual_id, lay_seq_num
1458: from JTY_WEBADI_OTH_TERR_INTF jwot
1459: WHERE jwot.user_sequence = v_user_sequence
1460: and jwot.action_flag = v_action_flag
1461: and jwot.interface_type = v_intf_type
1462: and jwot.header = v_header
1463: and jwot.qual13_value_id is not null
1464: and jwot.status is null
1465: union all
1466: select terr_qual_id14 terr_qual_id, lay_seq_num
1467: from JTY_WEBADI_OTH_TERR_INTF jwot
1468: WHERE jwot.user_sequence = v_user_sequence
1469: and jwot.action_flag = v_action_flag
1470: and jwot.interface_type = v_intf_type
1471: and jwot.header = v_header
1472: and jwot.qual14_value_id is not null
1473: and jwot.status is null
1474: union all
1475: select terr_qual_id15 terr_qual_id, lay_seq_num
1476: from JTY_WEBADI_OTH_TERR_INTF jwot
1477: WHERE jwot.user_sequence = v_user_sequence
1478: and jwot.action_flag = v_action_flag
1479: and jwot.interface_type = v_intf_type
1480: and jwot.header = v_header
1481: and jwot.qual15_value_id is not null
1482: and jwot.status is null
1483: union all
1484: select terr_qual_id16 terr_qual_id, lay_seq_num
1485: from JTY_WEBADI_OTH_TERR_INTF jwot
1486: WHERE jwot.user_sequence = v_user_sequence
1487: and jwot.action_flag = v_action_flag
1488: and jwot.interface_type = v_intf_type
1489: and jwot.header = v_header
1490: and jwot.qual16_value_id is not null
1491: and jwot.status is null
1492: union all
1493: select terr_qual_id17 terr_qual_id, lay_seq_num
1494: from JTY_WEBADI_OTH_TERR_INTF jwot
1495: WHERE jwot.user_sequence = v_user_sequence
1496: and jwot.action_flag = v_action_flag
1497: and jwot.interface_type = v_intf_type
1498: and jwot.header = v_header
1499: and jwot.qual17_value_id is not null
1500: and jwot.status is null
1501: union all
1502: select terr_qual_id18 terr_qual_id, lay_seq_num
1503: from JTY_WEBADI_OTH_TERR_INTF jwot
1504: WHERE jwot.user_sequence = v_user_sequence
1505: and jwot.action_flag = v_action_flag
1506: and jwot.interface_type = v_intf_type
1507: and jwot.header = v_header
1508: and jwot.qual18_value_id is not null
1509: and jwot.status is null
1510: union all
1511: select terr_qual_id19 terr_qual_id, lay_seq_num
1512: from JTY_WEBADI_OTH_TERR_INTF jwot
1513: WHERE jwot.user_sequence = v_user_sequence
1514: and jwot.action_flag = v_action_flag
1515: and jwot.interface_type = v_intf_type
1516: and jwot.header = v_header
1517: and jwot.qual19_value_id is not null
1518: and jwot.status is null
1519: union all
1520: select terr_qual_id20 terr_qual_id, lay_seq_num
1521: from JTY_WEBADI_OTH_TERR_INTF jwot
1522: WHERE jwot.user_sequence = v_user_sequence
1523: and jwot.action_flag = v_action_flag
1524: and jwot.interface_type = v_intf_type
1525: and jwot.header = v_header
1526: and jwot.qual20_value_id is not null
1527: and jwot.status is null
1528: union all
1529: select terr_qual_id21 terr_qual_id, lay_seq_num
1530: from JTY_WEBADI_OTH_TERR_INTF jwot
1531: WHERE jwot.user_sequence = v_user_sequence
1532: and jwot.action_flag = v_action_flag
1533: and jwot.interface_type = v_intf_type
1534: and jwot.header = v_header
1535: and jwot.qual21_value_id is not null
1536: and jwot.status is null
1537: union all
1538: select terr_qual_id22 terr_qual_id, lay_seq_num
1539: from JTY_WEBADI_OTH_TERR_INTF jwot
1540: WHERE jwot.user_sequence = v_user_sequence
1541: and jwot.action_flag = v_action_flag
1542: and jwot.interface_type = v_intf_type
1543: and jwot.header = v_header
1544: and jwot.qual22_value_id is not null
1545: and jwot.status is null
1546: union all
1547: select terr_qual_id23 terr_qual_id, lay_seq_num
1548: from JTY_WEBADI_OTH_TERR_INTF jwot
1549: WHERE jwot.user_sequence = v_user_sequence
1550: and jwot.action_flag = v_action_flag
1551: and jwot.interface_type = v_intf_type
1552: and jwot.header = v_header
1553: and jwot.qual23_value_id is not null
1554: and jwot.status is null
1555: union all
1556: select terr_qual_id24 terr_qual_id, lay_seq_num
1557: from JTY_WEBADI_OTH_TERR_INTF jwot
1558: WHERE jwot.user_sequence = v_user_sequence
1559: and jwot.action_flag = v_action_flag
1560: and jwot.interface_type = v_intf_type
1561: and jwot.header = v_header
1562: and jwot.qual24_value_id is not null
1563: and jwot.status is null
1564: union all
1565: select terr_qual_id25 terr_qual_id, lay_seq_num
1566: from JTY_WEBADI_OTH_TERR_INTF jwot
1567: WHERE jwot.user_sequence = v_user_sequence
1568: and jwot.action_flag = v_action_flag
1569: and jwot.interface_type = v_intf_type
1570: and jwot.header = v_header
1579: v_header varchar2
1580: ) IS
1581: select sub.qual_value_id, sub.lay_seq_num
1582: from ( select qual1_value_id qual_value_id, lay_seq_num
1583: from JTY_WEBADI_OTH_TERR_INTF jwot
1584: WHERE jwot.user_sequence = v_user_sequence
1585: and jwot.action_flag = v_action_flag
1586: and jwot.interface_type = v_intf_type
1587: and jwot.header = v_header
1588: and jwot.qual1_value_id is not null
1589: and jwot.status is null
1590: union all
1591: select qual2_value_id qual_value_id, lay_seq_num
1592: from JTY_WEBADI_OTH_TERR_INTF jwot
1593: WHERE jwot.user_sequence = v_user_sequence
1594: and jwot.action_flag = v_action_flag
1595: and jwot.interface_type = v_intf_type
1596: and jwot.header = v_header
1597: and jwot.qual2_value_id is not null
1598: and jwot.status is null
1599: union all
1600: select qual3_value_id qual_value_id, lay_seq_num
1601: from JTY_WEBADI_OTH_TERR_INTF jwot
1602: WHERE jwot.user_sequence = v_user_sequence
1603: and jwot.action_flag = v_action_flag
1604: and jwot.interface_type = v_intf_type
1605: and jwot.header = v_header
1606: and jwot.qual3_value_id is not null
1607: and jwot.status is null
1608: union all
1609: select qual4_value_id qual_value_id, lay_seq_num
1610: from JTY_WEBADI_OTH_TERR_INTF jwot
1611: WHERE jwot.user_sequence = v_user_sequence
1612: and jwot.action_flag = v_action_flag
1613: and jwot.interface_type = v_intf_type
1614: and jwot.header = v_header
1615: and jwot.qual4_value_id is not null
1616: and jwot.status is null
1617: union all
1618: select qual5_value_id qual_value_id, lay_seq_num
1619: from JTY_WEBADI_OTH_TERR_INTF jwot
1620: WHERE jwot.user_sequence = v_user_sequence
1621: and jwot.action_flag = v_action_flag
1622: and jwot.interface_type = v_intf_type
1623: and jwot.header = v_header
1624: and jwot.qual5_value_id is not null
1625: and jwot.status is null
1626: union all
1627: select qual6_value_id qual_value_id, lay_seq_num
1628: from JTY_WEBADI_OTH_TERR_INTF jwot
1629: WHERE jwot.user_sequence = v_user_sequence
1630: and jwot.action_flag = v_action_flag
1631: and jwot.interface_type = v_intf_type
1632: and jwot.header = v_header
1633: and jwot.qual6_value_id is not null
1634: and jwot.status is null
1635: union all
1636: select qual7_value_id qual_value_id, lay_seq_num
1637: from JTY_WEBADI_OTH_TERR_INTF jwot
1638: WHERE jwot.user_sequence = v_user_sequence
1639: and jwot.action_flag = v_action_flag
1640: and jwot.interface_type = v_intf_type
1641: and jwot.header = v_header
1642: and jwot.qual7_value_id is not null
1643: and jwot.status is null
1644: union all
1645: select qual8_value_id qual_value_id, lay_seq_num
1646: from JTY_WEBADI_OTH_TERR_INTF jwot
1647: WHERE jwot.user_sequence = v_user_sequence
1648: and jwot.action_flag = v_action_flag
1649: and jwot.interface_type = v_intf_type
1650: and jwot.header = v_header
1651: and jwot.qual8_value_id is not null
1652: and jwot.status is null
1653: union all
1654: select qual9_value_id qual_value_id, lay_seq_num
1655: from JTY_WEBADI_OTH_TERR_INTF jwot
1656: WHERE jwot.user_sequence = v_user_sequence
1657: and jwot.action_flag = v_action_flag
1658: and jwot.interface_type = v_intf_type
1659: and jwot.header = v_header
1660: and jwot.qual9_value_id is not null
1661: and jwot.status is null
1662: union all
1663: select qual10_value_id qual_value_id, lay_seq_num
1664: from JTY_WEBADI_OTH_TERR_INTF jwot
1665: WHERE jwot.user_sequence = v_user_sequence
1666: and jwot.action_flag = v_action_flag
1667: and jwot.interface_type = v_intf_type
1668: and jwot.header = v_header
1669: and jwot.qual10_value_id is not null
1670: and jwot.status is null
1671: union all
1672: select qual11_value_id qual_value_id, lay_seq_num
1673: from JTY_WEBADI_OTH_TERR_INTF jwot
1674: WHERE jwot.user_sequence = v_user_sequence
1675: and jwot.action_flag = v_action_flag
1676: and jwot.interface_type = v_intf_type
1677: and jwot.header = v_header
1678: and jwot.qual11_value_id is not null
1679: and jwot.status is null
1680: union all
1681: select qual12_value_id qual_value_id, lay_seq_num
1682: from JTY_WEBADI_OTH_TERR_INTF jwot
1683: WHERE jwot.user_sequence = v_user_sequence
1684: and jwot.action_flag = v_action_flag
1685: and jwot.interface_type = v_intf_type
1686: and jwot.header = v_header
1687: and jwot.qual12_value_id is not null
1688: and jwot.status is null
1689: union all
1690: select qual13_value_id qual_value_id, lay_seq_num
1691: from JTY_WEBADI_OTH_TERR_INTF jwot
1692: WHERE jwot.user_sequence = v_user_sequence
1693: and jwot.action_flag = v_action_flag
1694: and jwot.interface_type = v_intf_type
1695: and jwot.header = v_header
1696: and jwot.qual13_value_id is not null
1697: and jwot.status is null
1698: union all
1699: select qual14_value_id qual_value_id, lay_seq_num
1700: from JTY_WEBADI_OTH_TERR_INTF jwot
1701: WHERE jwot.user_sequence = v_user_sequence
1702: and jwot.action_flag = v_action_flag
1703: and jwot.interface_type = v_intf_type
1704: and jwot.header = v_header
1705: and jwot.qual14_value_id is not null
1706: and jwot.status is null
1707: union all
1708: select qual15_value_id qual_value_id, lay_seq_num
1709: from JTY_WEBADI_OTH_TERR_INTF jwot
1710: WHERE jwot.user_sequence = v_user_sequence
1711: and jwot.action_flag = v_action_flag
1712: and jwot.interface_type = v_intf_type
1713: and jwot.header = v_header
1714: and jwot.qual15_value_id is not null
1715: and jwot.status is null
1716: union all
1717: select qual16_value_id qual_value_id, lay_seq_num
1718: from JTY_WEBADI_OTH_TERR_INTF jwot
1719: WHERE jwot.user_sequence = v_user_sequence
1720: and jwot.action_flag = v_action_flag
1721: and jwot.interface_type = v_intf_type
1722: and jwot.header = v_header
1723: and jwot.qual16_value_id is not null
1724: and jwot.status is null
1725: union all
1726: select qual17_value_id qual_value_id, lay_seq_num
1727: from JTY_WEBADI_OTH_TERR_INTF jwot
1728: WHERE jwot.user_sequence = v_user_sequence
1729: and jwot.action_flag = v_action_flag
1730: and jwot.interface_type = v_intf_type
1731: and jwot.header = v_header
1732: and jwot.qual17_value_id is not null
1733: and jwot.status is null
1734: union all
1735: select qual18_value_id qual_value_id, lay_seq_num
1736: from JTY_WEBADI_OTH_TERR_INTF jwot
1737: WHERE jwot.user_sequence = v_user_sequence
1738: and jwot.action_flag = v_action_flag
1739: and jwot.interface_type = v_intf_type
1740: and jwot.header = v_header
1741: and jwot.qual18_value_id is not null
1742: and jwot.status is null
1743: union all
1744: select qual19_value_id qual_value_id, lay_seq_num
1745: from JTY_WEBADI_OTH_TERR_INTF jwot
1746: WHERE jwot.user_sequence = v_user_sequence
1747: and jwot.action_flag = v_action_flag
1748: and jwot.interface_type = v_intf_type
1749: and jwot.header = v_header
1750: and jwot.qual19_value_id is not null
1751: and jwot.status is null
1752: union all
1753: select qual20_value_id qual_value_id, lay_seq_num
1754: from JTY_WEBADI_OTH_TERR_INTF jwot
1755: WHERE jwot.user_sequence = v_user_sequence
1756: and jwot.action_flag = v_action_flag
1757: and jwot.interface_type = v_intf_type
1758: and jwot.header = v_header
1759: and jwot.qual20_value_id is not null
1760: and jwot.status is null
1761: union all
1762: select qual21_value_id qual_value_id, lay_seq_num
1763: from JTY_WEBADI_OTH_TERR_INTF jwot
1764: WHERE jwot.user_sequence = v_user_sequence
1765: and jwot.action_flag = v_action_flag
1766: and jwot.interface_type = v_intf_type
1767: and jwot.header = v_header
1768: and jwot.qual21_value_id is not null
1769: and jwot.status is null
1770: union all
1771: select qual22_value_id qual_value_id, lay_seq_num
1772: from JTY_WEBADI_OTH_TERR_INTF jwot
1773: WHERE jwot.user_sequence = v_user_sequence
1774: and jwot.action_flag = v_action_flag
1775: and jwot.interface_type = v_intf_type
1776: and jwot.header = v_header
1777: and jwot.qual22_value_id is not null
1778: and jwot.status is null
1779: union all
1780: select qual23_value_id qual_value_id, lay_seq_num
1781: from JTY_WEBADI_OTH_TERR_INTF jwot
1782: WHERE jwot.user_sequence = v_user_sequence
1783: and jwot.action_flag = v_action_flag
1784: and jwot.interface_type = v_intf_type
1785: and jwot.header = v_header
1786: and jwot.qual23_value_id is not null
1787: and jwot.status is null
1788: union all
1789: select qual24_value_id qual_value_id, lay_seq_num
1790: from JTY_WEBADI_OTH_TERR_INTF jwot
1791: WHERE jwot.user_sequence = v_user_sequence
1792: and jwot.action_flag = v_action_flag
1793: and jwot.interface_type = v_intf_type
1794: and jwot.header = v_header
1795: and jwot.qual24_value_id is not null
1796: and jwot.status is null
1797: union all
1798: select qual25_value_id qual_value_id, lay_seq_num
1799: from JTY_WEBADI_OTH_TERR_INTF jwot
1800: WHERE jwot.user_sequence = v_user_sequence
1801: and jwot.action_flag = v_action_flag
1802: and jwot.interface_type = v_intf_type
1803: and jwot.header = v_header
1811: v_intf_type varchar2,
1812: v_header varchar2
1813: ) IS
1814: SELECT jwr.TERR_RSC_ID, jwot.lay_seq_num
1815: FROM JTY_WEBADI_OTH_TERR_INTF jwot,
1816: JTY_WEBADI_RESOURCES jwr
1817: WHERE jwot.lay_seq_num = jwr.lay_seq_num
1818: and jwot.header = jwr.header
1819: and jwot.user_sequence = jwr.user_sequence
1899: DELETE from JTF_TERR_ALL WHERE TERR_ID = l_del_terr_rec.terr_id(i);
1900:
1901: -- update all records including the qual and rsc which will be deleted
1902: forall i in l_del_terr_rec.terr_id.first..l_del_terr_rec.terr_id.last
1903: update JTY_WEBADI_OTH_TERR_INTF
1904: set status = x_return_status
1905: where terr_id = l_del_terr_rec.terr_id(i)
1906: and user_sequence = p_user_sequence
1907: and interface_type = p_intf_type;
1913: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_TERR');
1914: X_Msg_Data := fnd_message.get();
1915:
1916: forall i in l_del_terr_rec.lay_seq_num.first..l_del_terr_rec.lay_seq_num.last
1917: update JTY_WEBADI_OTH_TERR_INTF
1918: set status = x_return_status,
1919: error_msg = x_msg_data
1920: where lay_seq_num = l_del_terr_rec.lay_seq_num(i)
1921: and user_sequence = p_user_sequence
1946: END IF;
1947: END Loop;
1948:
1949: forall i in l_del_qual_val_rec.lay_seq_num.first..l_del_qual_val_rec.lay_seq_num.last
1950: update JTY_WEBADI_OTH_TERR_INTF
1951: set status = x_return_status
1952: where lay_seq_num = l_del_qual_val_rec.lay_seq_num(i)
1953: and user_sequence = p_user_sequence
1954: and action_flag = p_action_flag
1962: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_QUAL');
1963: X_Msg_Data := fnd_message.get();
1964:
1965: forall i in l_del_qual_val_rec.lay_seq_num.first..l_del_qual_val_rec.lay_seq_num.last
1966: update JTY_WEBADI_OTH_TERR_INTF
1967: set status = x_return_status,
1968: error_msg = x_msg_data
1969: where lay_seq_num = l_del_qual_val_rec.lay_seq_num(i)
1970: and user_sequence = p_user_sequence
1993: DELETE FROM JTF_TERR_RSC_ALL
1994: WHERE TERR_RSC_ID = l_del_rsc_rec.terr_rsc_id(i);
1995:
1996: forall i in l_del_rsc_rec.lay_seq_num.first..l_del_rsc_rec.lay_seq_num.last
1997: update JTY_WEBADI_OTH_TERR_INTF
1998: set status = x_return_status
1999: where lay_seq_num = l_del_rsc_rec.lay_seq_num(i)
2000: and user_sequence = p_user_sequence
2001: and action_flag = p_action_flag
2009: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_RSC');
2010: X_Msg_Data := fnd_message.get();
2011:
2012: forall i in l_del_rsc_rec.lay_seq_num.first..l_del_rsc_rec.lay_seq_num.last
2013: update JTY_WEBADI_OTH_TERR_INTF
2014: set status = x_return_status,
2015: error_msg = x_msg_data
2016: where lay_seq_num = l_del_rsc_rec.lay_seq_num(i)
2017: and user_sequence = p_user_sequence
2321: ORG_ID,
2322: NUM_WINNERS,
2323: 0 NUM_QUAL,
2324: lay_seq_num
2325: from JTY_WEBADI_OTH_TERR_INTF
2326: where header = v_header
2327: and status is null
2328: and USER_SEQUENCE = v_USER_SEQUENCE
2329: and INTERFACE_TYPE = v_intf_type
2431: l_terr_all_rec.ATTRIBUTE13(i),l_terr_all_rec.ATTRIBUTE14(i),l_terr_all_rec.ATTRIBUTE15(i),
2432: l_terr_all_rec.ORG_ID(i), l_terr_all_rec.NUM_WINNERS(i), l_terr_all_rec.NUM_QUAL(i));
2433:
2434: forall i in l_terr_all_rec.TERR_ID.first..l_terr_all_rec.TERR_ID.last
2435: update JTY_WEBADI_OTH_TERR_INTF
2436: set parent_terr_id = l_terr_all_rec.terr_id(i)
2437: where parent_terr_name = l_terr_all_rec.name(i)
2438: and USER_SEQUENCE = P_USER_SEQUENCE
2439: and interface_type = l_intf_type
2439: and interface_type = l_intf_type
2440: and parent_terr_id is null;
2441:
2442: forall i in l_terr_all_rec.TERR_ID.first..l_terr_all_rec.TERR_ID.last
2443: update JTY_WEBADI_OTH_TERR_INTF
2444: set terr_id = l_terr_all_rec.terr_id(i)
2445: where terr_name = l_terr_all_rec.name(i)
2446: and USER_SEQUENCE = P_USER_SEQUENCE
2447: and interface_type = l_intf_type
2467: LAST_UPDATE_LOGIN,
2468: TERR_ID,
2469: usage_id SOURCE_ID,
2470: ORG_ID
2471: from JTY_WEBADI_OTH_TERR_INTF
2472: where header = l_header
2473: and status is null
2474: and USER_SEQUENCE = p_USER_SEQUENCE
2475: and INTERFACE_TYPE = l_intf_type
2496: TERR_ID,
2497: QUAL_TYPE_ID QUAL_TYPE_USG_ID,
2498: ORG_ID
2499: from JTY_WEBADI_QUAL_TYPE_HEADER gt,
2500: JTY_WEBADI_OTH_TERR_INTF jut
2501: where jut.header = L_header
2502: and jut.status is null
2503: and jut.USER_SEQUENCE = P_USER_SEQUENCE
2504: and jut.INTERFACE_TYPE = l_intf_type
2505: and jut.action_flag = p_action_flag
2506: and gt.user_sequence = jut.user_sequence;
2507:
2508: forall i in l_terr_all_rec.lay_seq_num.first..l_terr_all_rec.lay_seq_num.last
2509: update JTY_WEBADI_OTH_TERR_INTF
2510: set status = x_return_status
2511: , error_msg = X_Msg_Data
2512: where lay_seq_num = l_terr_all_rec.lay_seq_num(i)
2513: and interface_type = l_intf_type
2553: NUM_WINNERS = l_terr_all_rec.NUM_WINNERS(i)
2554: where terr_id = l_terr_all_rec.Terr_Id(i);
2555:
2556: forall i in l_terr_all_rec.lay_seq_num.first..l_terr_all_rec.lay_seq_num.last
2557: update JTY_WEBADI_OTH_TERR_INTF
2558: set status = x_return_status
2559: , error_msg = X_Msg_Data
2560: where lay_seq_num = l_terr_all_rec.lay_seq_num(i)
2561: and interface_type = l_intf_type
2601: QUAL1_VALUE2 qual_VALUE2,
2602: QUAL1_VALUE3 qual_VALUE3, ORG_ID,
2603: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2604: CREATED_BY, CREATION_DATE
2605: FROM JTY_WEBADI_OTH_TERR_INTF jut
2606: where jut.USER_SEQUENCE = v_user_sequence
2607: and jut.status is null
2608: and jut.terr_qual_id1 is null
2609: and jut.qual1_value1 is not null
2617: QUAL2_VALUE2 qual_VALUE2,
2618: QUAL2_VALUE3 qual_VALUE3, ORG_ID,
2619: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2620: CREATED_BY, CREATION_DATE
2621: FROM JTY_WEBADI_OTH_TERR_INTF jut
2622: where jut.USER_SEQUENCE = v_user_sequence
2623: and jut.status is null
2624: and jut.terr_qual_id2 is null
2625: and jut.qual2_value1 is not null
2633: QUAL3_VALUE2 qual_VALUE2,
2634: QUAL3_VALUE3 qual_VALUE3, ORG_ID,
2635: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2636: CREATED_BY, CREATION_DATE
2637: FROM JTY_WEBADI_OTH_TERR_INTF jut
2638: where jut.USER_SEQUENCE = v_user_sequence
2639: and jut.status is null
2640: and jut.terr_qual_id3 is null
2641: and jut.qual3_value1 is not null
2649: QUAL4_VALUE2 qual_VALUE2,
2650: QUAL4_VALUE3 qual_VALUE3, ORG_ID,
2651: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2652: CREATED_BY, CREATION_DATE
2653: FROM JTY_WEBADI_OTH_TERR_INTF jut
2654: where jut.USER_SEQUENCE = v_user_sequence
2655: and jut.status is null
2656: and jut.terr_qual_id4 is null
2657: and jut.qual4_value1 is not null
2665: QUAL5_VALUE2 qual_VALUE2,
2666: QUAL5_VALUE3 qual_VALUE3, ORG_ID,
2667: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2668: CREATED_BY, CREATION_DATE
2669: FROM JTY_WEBADI_OTH_TERR_INTF jut
2670: where jut.USER_SEQUENCE = v_user_sequence
2671: and jut.status is null
2672: and jut.terr_qual_id5 is null
2673: and jut.qual5_value1 is not null
2681: QUAL6_VALUE2 qual_VALUE2,
2682: QUAL6_VALUE3 qual_VALUE3, ORG_ID,
2683: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2684: CREATED_BY, CREATION_DATE
2685: FROM JTY_WEBADI_OTH_TERR_INTF jut
2686: where jut.USER_SEQUENCE = v_user_sequence
2687: and jut.status is null
2688: and jut.terr_qual_id6 is null
2689: and jut.qual6_value1 is not null
2697: QUAL7_VALUE2 qual_VALUE2,
2698: QUAL7_VALUE3 qual_VALUE3, ORG_ID,
2699: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2700: CREATED_BY, CREATION_DATE
2701: FROM JTY_WEBADI_OTH_TERR_INTF jut
2702: where jut.USER_SEQUENCE = v_user_sequence
2703: and jut.status is null
2704: and jut.terr_qual_id7 is null
2705: and jut.qual7_value1 is not null
2713: QUAL8_VALUE2 qual_VALUE2,
2714: QUAL8_VALUE3 qual_VALUE3, ORG_ID,
2715: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2716: CREATED_BY, CREATION_DATE
2717: FROM JTY_WEBADI_OTH_TERR_INTF jut
2718: where jut.USER_SEQUENCE = v_user_sequence
2719: and jut.status is null
2720: and jut.terr_qual_id8 is null
2721: and jut.qual8_value1 is not null
2729: QUAL9_VALUE2 qual_VALUE2,
2730: QUAL9_VALUE3 qual_VALUE3, ORG_ID,
2731: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2732: CREATED_BY, CREATION_DATE
2733: FROM JTY_WEBADI_OTH_TERR_INTF jut
2734: where jut.USER_SEQUENCE = v_user_sequence
2735: and jut.status is null
2736: and jut.terr_qual_id9 is null
2737: and jut.qual9_value1 is not null
2745: QUAL10_VALUE2 qual_VALUE2,
2746: QUAL10_VALUE3 qual_VALUE3, ORG_ID,
2747: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2748: CREATED_BY, CREATION_DATE
2749: FROM JTY_WEBADI_OTH_TERR_INTF jut
2750: where jut.USER_SEQUENCE = v_user_sequence
2751: and jut.status is null
2752: and jut.terr_qual_id10 is null
2753: and jut.qual10_value1 is not null
2761: QUAL11_VALUE2 qual_VALUE2,
2762: QUAL11_VALUE3 qual_VALUE3, ORG_ID,
2763: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2764: CREATED_BY, CREATION_DATE
2765: FROM JTY_WEBADI_OTH_TERR_INTF jut
2766: where jut.USER_SEQUENCE = v_user_sequence
2767: and jut.status is null
2768: and jut.terr_qual_id11 is null
2769: and jut.qual11_value1 is not null
2777: QUAL12_VALUE2 qual_VALUE2,
2778: QUAL12_VALUE3 qual_VALUE3, ORG_ID,
2779: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2780: CREATED_BY, CREATION_DATE
2781: FROM JTY_WEBADI_OTH_TERR_INTF jut
2782: where jut.USER_SEQUENCE = v_user_sequence
2783: and jut.status is null
2784: and jut.terr_qual_id12 is null
2785: and jut.qual12_value1 is not null
2793: QUAL13_VALUE2 qual_VALUE2,
2794: QUAL13_VALUE3 qual_VALUE3, ORG_ID,
2795: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2796: CREATED_BY, CREATION_DATE
2797: FROM JTY_WEBADI_OTH_TERR_INTF jut
2798: where jut.USER_SEQUENCE = v_user_sequence
2799: and jut.status is null
2800: and jut.terr_qual_id13 is null
2801: and jut.qual13_value1 is not null
2809: QUAL14_VALUE2 qual_VALUE2,
2810: QUAL14_VALUE3 qual_VALUE3, ORG_ID,
2811: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2812: CREATED_BY, CREATION_DATE
2813: FROM JTY_WEBADI_OTH_TERR_INTF jut
2814: where jut.USER_SEQUENCE = v_user_sequence
2815: and jut.status is null
2816: and jut.terr_qual_id14 is null
2817: and jut.qual14_value1 is not null
2825: QUAL15_VALUE2 qual_VALUE2,
2826: QUAL15_VALUE3 qual_VALUE3, ORG_ID,
2827: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2828: CREATED_BY, CREATION_DATE
2829: FROM JTY_WEBADI_OTH_TERR_INTF jut
2830: where jut.USER_SEQUENCE = v_user_sequence
2831: and jut.status is null
2832: and jut.terr_qual_id15 is null
2833: and jut.qual15_value1 is not null
2841: QUAL16_VALUE2 qual_VALUE2,
2842: QUAL16_VALUE3 qual_VALUE3, ORG_ID,
2843: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2844: CREATED_BY, CREATION_DATE
2845: FROM JTY_WEBADI_OTH_TERR_INTF jut
2846: where jut.USER_SEQUENCE = v_user_sequence
2847: and jut.status is null
2848: and jut.terr_qual_id16 is null
2849: and jut.qual16_value1 is not null
2857: QUAL17_VALUE2 qual_VALUE2,
2858: QUAL17_VALUE3 qual_VALUE3, ORG_ID,
2859: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2860: CREATED_BY, CREATION_DATE
2861: FROM JTY_WEBADI_OTH_TERR_INTF jut
2862: where jut.USER_SEQUENCE = v_user_sequence
2863: and jut.status is null
2864: and jut.terr_qual_id17 is null
2865: and jut.qual17_value1 is not null
2873: QUAL18_VALUE2 qual_VALUE2,
2874: QUAL18_VALUE3 qual_VALUE3, ORG_ID,
2875: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2876: CREATED_BY, CREATION_DATE
2877: FROM JTY_WEBADI_OTH_TERR_INTF jut
2878: where jut.USER_SEQUENCE = v_user_sequence
2879: and jut.status is null
2880: and jut.terr_qual_id18 is null
2881: and jut.qual18_value1 is not null
2889: QUAL19_VALUE2 qual_VALUE2,
2890: QUAL19_VALUE3 qual_VALUE3, ORG_ID,
2891: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2892: CREATED_BY, CREATION_DATE
2893: FROM JTY_WEBADI_OTH_TERR_INTF jut
2894: where jut.USER_SEQUENCE = v_user_sequence
2895: and jut.status is null
2896: and jut.terr_qual_id19 is null
2897: and jut.qual19_value1 is not null
2905: QUAL20_VALUE2 qual_VALUE2,
2906: QUAL20_VALUE3 qual_VALUE3, ORG_ID,
2907: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2908: CREATED_BY, CREATION_DATE
2909: FROM JTY_WEBADI_OTH_TERR_INTF jut
2910: where jut.USER_SEQUENCE = v_user_sequence
2911: and jut.status is null
2912: and jut.terr_qual_id20 is null
2913: and jut.qual20_value1 is not null
2921: QUAL21_VALUE2 qual_VALUE2,
2922: QUAL21_VALUE3 qual_VALUE3, ORG_ID,
2923: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2924: CREATED_BY, CREATION_DATE
2925: FROM JTY_WEBADI_OTH_TERR_INTF jut
2926: where jut.USER_SEQUENCE = v_user_sequence
2927: and jut.status is null
2928: and jut.terr_qual_id21 is null
2929: and jut.qual21_value1 is not null
2937: QUAL22_VALUE2 qual_VALUE2,
2938: QUAL22_VALUE3 qual_VALUE3, ORG_ID,
2939: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2940: CREATED_BY, CREATION_DATE
2941: FROM JTY_WEBADI_OTH_TERR_INTF jut
2942: where jut.USER_SEQUENCE = v_user_sequence
2943: and jut.status is null
2944: and jut.terr_qual_id22 is null
2945: and jut.qual22_value1 is not null
2953: QUAL23_VALUE2 qual_VALUE2,
2954: QUAL23_VALUE3 qual_VALUE3, ORG_ID,
2955: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2956: CREATED_BY, CREATION_DATE
2957: FROM JTY_WEBADI_OTH_TERR_INTF jut
2958: where jut.USER_SEQUENCE = v_user_sequence
2959: and jut.status is null
2960: and jut.terr_qual_id23 is null
2961: and jut.qual23_value1 is not null
2969: QUAL24_VALUE2 qual_VALUE2,
2970: QUAL24_VALUE3 qual_VALUE3, ORG_ID,
2971: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2972: CREATED_BY, CREATION_DATE
2973: FROM JTY_WEBADI_OTH_TERR_INTF jut
2974: where jut.USER_SEQUENCE = v_user_sequence
2975: and jut.status is null
2976: and jut.terr_qual_id24 is null
2977: and jut.qual24_value1 is not null
2985: QUAL25_VALUE2 qual_valUE2,
2986: QUAL25_VALUE3 qual_valUE3, ORG_ID,
2987: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2988: CREATED_BY, CREATION_DATE
2989: FROM JTY_WEBADI_OTH_TERR_INTF jut
2990: where jut.USER_SEQUENCE = v_user_sequence
2991: and jut.status is null
2992: and jut.terr_qual_id25 is null
2993: and jut.qual25_value1 is not null
3022: QUAL1_VALUE2 qual_VALUE2,
3023: QUAL1_VALUE3 qual_VALUE3, ORG_ID,
3024: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3025: CREATED_BY, CREATION_DATE
3026: FROM JTY_WEBADI_OTH_TERR_INTF jut
3027: where jut.USER_SEQUENCE = v_user_sequence
3028: and jut.status is null
3029: and jut.terr_qual_id1 is not null
3030: and jut.QUAL1_VALUE_ID is null
3039: QUAL2_VALUE2 qual_VALUE2,
3040: QUAL2_VALUE3 qual_VALUE3, ORG_ID,
3041: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3042: CREATED_BY, CREATION_DATE
3043: FROM JTY_WEBADI_OTH_TERR_INTF jut
3044: where jut.USER_SEQUENCE = v_user_sequence
3045: and jut.status is null
3046: and jut.terr_qual_id2 is not null
3047: and jut.QUAL2_VALUE_ID is null
3056: QUAL3_VALUE2 qual_VALUE2,
3057: QUAL3_VALUE3 qual_VALUE3, ORG_ID,
3058: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3059: CREATED_BY, CREATION_DATE
3060: FROM JTY_WEBADI_OTH_TERR_INTF jut
3061: where jut.USER_SEQUENCE = v_user_sequence
3062: and jut.status is null
3063: and jut.terr_qual_id3 is not null
3064: and jut.QUAL3_VALUE_ID is null
3073: QUAL4_VALUE2 qual_VALUE2,
3074: QUAL4_VALUE3 qual_VALUE3, ORG_ID,
3075: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3076: CREATED_BY, CREATION_DATE
3077: FROM JTY_WEBADI_OTH_TERR_INTF jut
3078: where jut.USER_SEQUENCE = v_user_sequence
3079: and jut.status is null
3080: and jut.terr_qual_id4 is not null
3081: and jut.QUAL4_VALUE_ID is null
3090: QUAL5_VALUE2 qual_VALUE2,
3091: QUAL5_VALUE3 qual_VALUE3, ORG_ID,
3092: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3093: CREATED_BY, CREATION_DATE
3094: FROM JTY_WEBADI_OTH_TERR_INTF jut
3095: where jut.USER_SEQUENCE = v_user_sequence
3096: and jut.status is null
3097: and jut.terr_qual_id5 is not null
3098: and jut.QUAL5_VALUE_ID is null
3107: QUAL6_VALUE2 qual_VALUE2,
3108: QUAL6_VALUE3 qual_VALUE3, ORG_ID,
3109: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3110: CREATED_BY, CREATION_DATE
3111: FROM JTY_WEBADI_OTH_TERR_INTF jut
3112: where jut.USER_SEQUENCE = v_user_sequence
3113: and jut.status is null
3114: and jut.terr_qual_id6 is not null
3115: and jut.QUAL6_VALUE_ID is null
3124: QUAL7_VALUE2 qual_VALUE2,
3125: QUAL7_VALUE3 qual_VALUE3, ORG_ID,
3126: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3127: CREATED_BY, CREATION_DATE
3128: FROM JTY_WEBADI_OTH_TERR_INTF jut
3129: where jut.USER_SEQUENCE = v_user_sequence
3130: and jut.status is null
3131: and jut.terr_qual_id7 is not null
3132: and jut.QUAL7_VALUE_ID is null
3141: QUAL8_VALUE2 qual_VALUE2,
3142: QUAL8_VALUE3 qual_VALUE3, ORG_ID,
3143: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3144: CREATED_BY, CREATION_DATE
3145: FROM JTY_WEBADI_OTH_TERR_INTF jut
3146: where jut.USER_SEQUENCE = v_user_sequence
3147: and jut.status is null
3148: and jut.terr_qual_id8 is not null
3149: and jut.QUAL8_VALUE_ID is null
3158: QUAL9_VALUE2 qual_VALUE2,
3159: QUAL9_VALUE3 qual_VALUE3, ORG_ID,
3160: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3161: CREATED_BY, CREATION_DATE
3162: FROM JTY_WEBADI_OTH_TERR_INTF jut
3163: where jut.USER_SEQUENCE = v_user_sequence
3164: and jut.status is null
3165: and jut.terr_qual_id9 is not null
3166: and jut.QUAL9_VALUE_ID is null
3175: QUAL10_VALUE2 qual_VALUE2,
3176: QUAL10_VALUE3 qual_VALUE3, ORG_ID,
3177: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3178: CREATED_BY, CREATION_DATE
3179: FROM JTY_WEBADI_OTH_TERR_INTF jut
3180: where jut.USER_SEQUENCE = v_user_sequence
3181: and jut.status is null
3182: and jut.terr_qual_id10 is not null
3183: and jut.QUAL10_VALUE_ID is null
3192: QUAL11_VALUE2 qual_VALUE2,
3193: QUAL11_VALUE3 qual_VALUE3, ORG_ID,
3194: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3195: CREATED_BY, CREATION_DATE
3196: FROM JTY_WEBADI_OTH_TERR_INTF jut
3197: where jut.USER_SEQUENCE = v_user_sequence
3198: and jut.status is null
3199: and jut.terr_qual_id11 is not null
3200: and jut.QUAL11_VALUE_ID is null
3209: QUAL12_VALUE2 qual_VALUE2,
3210: QUAL12_VALUE3 qual_VALUE3, ORG_ID,
3211: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3212: CREATED_BY, CREATION_DATE
3213: FROM JTY_WEBADI_OTH_TERR_INTF jut
3214: where jut.USER_SEQUENCE = v_user_sequence
3215: and jut.status is null
3216: and jut.terr_qual_id12 is not null
3217: and jut.QUAL12_VALUE_ID is null
3226: QUAL13_VALUE2 qual_VALUE2,
3227: QUAL13_VALUE3 qual_VALUE3, ORG_ID,
3228: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3229: CREATED_BY, CREATION_DATE
3230: FROM JTY_WEBADI_OTH_TERR_INTF jut
3231: where jut.USER_SEQUENCE = v_user_sequence
3232: and jut.status is null
3233: and jut.terr_qual_id13 is not null
3234: and jut.QUAL13_VALUE_ID is null
3243: QUAL14_VALUE2 qual_VALUE2,
3244: QUAL14_VALUE3 qual_VALUE3, ORG_ID,
3245: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3246: CREATED_BY, CREATION_DATE
3247: FROM JTY_WEBADI_OTH_TERR_INTF jut
3248: where jut.USER_SEQUENCE = v_user_sequence
3249: and jut.status is null
3250: and jut.terr_qual_id14 is not null
3251: and jut.QUAL14_VALUE_ID is null
3260: QUAL15_VALUE2 qual_VALUE2,
3261: QUAL15_VALUE3 qual_VALUE3, ORG_ID,
3262: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3263: CREATED_BY, CREATION_DATE
3264: FROM JTY_WEBADI_OTH_TERR_INTF jut
3265: where jut.USER_SEQUENCE = v_user_sequence
3266: and jut.status is null
3267: and jut.terr_qual_id15 is not null
3268: and jut.QUAL15_VALUE_ID is null
3277: QUAL16_VALUE2 qual_VALUE2,
3278: QUAL16_VALUE3 qual_VALUE3, ORG_ID,
3279: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3280: CREATED_BY, CREATION_DATE
3281: FROM JTY_WEBADI_OTH_TERR_INTF jut
3282: where jut.USER_SEQUENCE = v_user_sequence
3283: and jut.status is null
3284: and jut.terr_qual_id16 is not null
3285: and jut.QUAL16_VALUE_ID is null
3294: QUAL17_VALUE2 qual_VALUE2,
3295: QUAL17_VALUE3 qual_VALUE3, ORG_ID,
3296: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3297: CREATED_BY, CREATION_DATE
3298: FROM JTY_WEBADI_OTH_TERR_INTF jut
3299: where jut.USER_SEQUENCE = v_user_sequence
3300: and jut.status is null
3301: and jut.terr_qual_id17 is not null
3302: and jut.QUAL17_VALUE_ID is null
3311: QUAL18_VALUE2 qual_VALUE2,
3312: QUAL18_VALUE3 qual_VALUE3, ORG_ID,
3313: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3314: CREATED_BY, CREATION_DATE
3315: FROM JTY_WEBADI_OTH_TERR_INTF jut
3316: where jut.USER_SEQUENCE = v_user_sequence
3317: and jut.status is null
3318: and jut.terr_qual_id18 is not null
3319: and jut.QUAL18_VALUE_ID is null
3328: QUAL19_VALUE2 qual_VALUE2,
3329: QUAL19_VALUE3 qual_VALUE3, ORG_ID,
3330: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3331: CREATED_BY, CREATION_DATE
3332: FROM JTY_WEBADI_OTH_TERR_INTF jut
3333: where jut.USER_SEQUENCE = v_user_sequence
3334: and jut.status is null
3335: and jut.terr_qual_id19 is not null
3336: and jut.QUAL19_VALUE_ID is null
3345: QUAL20_VALUE2 qual_VALUE2,
3346: QUAL20_VALUE3 qual_VALUE3, ORG_ID,
3347: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3348: CREATED_BY, CREATION_DATE
3349: FROM JTY_WEBADI_OTH_TERR_INTF jut
3350: where jut.USER_SEQUENCE = v_user_sequence
3351: and jut.status is null
3352: and jut.terr_qual_id20 is not null
3353: and jut.QUAL20_VALUE_ID is null
3362: QUAL21_VALUE2 qual_VALUE2,
3363: QUAL21_VALUE3 qual_VALUE3, ORG_ID,
3364: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3365: CREATED_BY, CREATION_DATE
3366: FROM JTY_WEBADI_OTH_TERR_INTF jut
3367: where jut.USER_SEQUENCE = v_user_sequence
3368: and jut.status is null
3369: and jut.terr_qual_id21 is not null
3370: and jut.QUAL21_VALUE_ID is null
3379: QUAL22_VALUE2 qual_VALUE2,
3380: QUAL22_VALUE3 qual_VALUE3, ORG_ID,
3381: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3382: CREATED_BY, CREATION_DATE
3383: FROM JTY_WEBADI_OTH_TERR_INTF jut
3384: where jut.USER_SEQUENCE = v_user_sequence
3385: and jut.status is null
3386: and jut.terr_qual_id22 is not null
3387: and jut.QUAL22_VALUE_ID is null
3396: QUAL23_VALUE2 qual_VALUE2,
3397: QUAL23_VALUE3 qual_VALUE3, ORG_ID,
3398: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3399: CREATED_BY, CREATION_DATE
3400: FROM JTY_WEBADI_OTH_TERR_INTF jut
3401: where jut.USER_SEQUENCE = v_user_sequence
3402: and jut.status is null
3403: and jut.terr_qual_id23 is not null
3404: and jut.QUAL23_VALUE_ID is null
3413: QUAL24_VALUE2 qual_VALUE2,
3414: QUAL24_VALUE3 qual_VALUE3, ORG_ID,
3415: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3416: CREATED_BY, CREATION_DATE
3417: FROM JTY_WEBADI_OTH_TERR_INTF jut
3418: where jut.USER_SEQUENCE = v_user_sequence
3419: and jut.status is null
3420: and jut.terr_qual_id24 is not null
3421: and jut.QUAL24_VALUE_ID is null
3430: QUAL25_VALUE2 qual_valUE2,
3431: QUAL25_VALUE3 qual_valUE3, ORG_ID,
3432: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3433: CREATED_BY, CREATION_DATE
3434: FROM JTY_WEBADI_OTH_TERR_INTF jut
3435: where jut.USER_SEQUENCE = v_user_sequence
3436: and jut.status is null
3437: and jut.terr_qual_id25 is not null
3438: and jut.QUAL25_VALUE_ID is null
3468: QUAL1_VALUE2 qual_VALUE2,
3469: QUAL1_VALUE3 qual_VALUE3, ORG_ID,
3470: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3471: CREATED_BY, CREATION_DATE
3472: FROM JTY_WEBADI_OTH_TERR_INTF jut
3473: where jut.USER_SEQUENCE = v_user_sequence
3474: and jut.status is null
3475: and jut.terr_qual_id1 is not null
3476: and jut.QUAL1_VALUE_ID is not null
3486: QUAL2_VALUE2 qual_VALUE2,
3487: QUAL2_VALUE3 qual_VALUE3, ORG_ID,
3488: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3489: CREATED_BY, CREATION_DATE
3490: FROM JTY_WEBADI_OTH_TERR_INTF jut
3491: where jut.USER_SEQUENCE = v_user_sequence
3492: and jut.status is null
3493: and jut.terr_qual_id2 is not null
3494: and jut.QUAL2_VALUE_ID is not null
3504: QUAL3_VALUE2 qual_VALUE2,
3505: QUAL3_VALUE3 qual_VALUE3, ORG_ID,
3506: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3507: CREATED_BY, CREATION_DATE
3508: FROM JTY_WEBADI_OTH_TERR_INTF jut
3509: where jut.USER_SEQUENCE = v_user_sequence
3510: and jut.status is null
3511: and jut.terr_qual_id3 is not null
3512: and jut.QUAL3_VALUE_ID is not null
3522: QUAL4_VALUE2 qual_VALUE2,
3523: QUAL4_VALUE3 qual_VALUE3, ORG_ID,
3524: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3525: CREATED_BY, CREATION_DATE
3526: FROM JTY_WEBADI_OTH_TERR_INTF jut
3527: where jut.USER_SEQUENCE = v_user_sequence
3528: and jut.status is null
3529: and jut.terr_qual_id4 is not null
3530: and jut.QUAL4_VALUE_ID is not null
3540: QUAL5_VALUE2 qual_VALUE2,
3541: QUAL5_VALUE3 qual_VALUE3, ORG_ID,
3542: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3543: CREATED_BY, CREATION_DATE
3544: FROM JTY_WEBADI_OTH_TERR_INTF jut
3545: where jut.USER_SEQUENCE = v_user_sequence
3546: and jut.status is null
3547: and jut.terr_qual_id5 is not null
3548: and jut.QUAL5_VALUE_ID is not null
3558: QUAL6_VALUE2 qual_VALUE2,
3559: QUAL6_VALUE3 qual_VALUE3, ORG_ID,
3560: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3561: CREATED_BY, CREATION_DATE
3562: FROM JTY_WEBADI_OTH_TERR_INTF jut
3563: where jut.USER_SEQUENCE = v_user_sequence
3564: and jut.status is null
3565: and jut.terr_qual_id6 is not null
3566: and jut.QUAL6_VALUE_ID is not null
3576: QUAL7_VALUE2 qual_VALUE2,
3577: QUAL7_VALUE3 qual_VALUE3, ORG_ID,
3578: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3579: CREATED_BY, CREATION_DATE
3580: FROM JTY_WEBADI_OTH_TERR_INTF jut
3581: where jut.USER_SEQUENCE = v_user_sequence
3582: and jut.status is null
3583: and jut.terr_qual_id7 is not null
3584: and jut.QUAL7_VALUE_ID is not null
3594: QUAL8_VALUE2 qual_VALUE2,
3595: QUAL8_VALUE3 qual_VALUE3, ORG_ID,
3596: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3597: CREATED_BY, CREATION_DATE
3598: FROM JTY_WEBADI_OTH_TERR_INTF jut
3599: where jut.USER_SEQUENCE = v_user_sequence
3600: and jut.status is null
3601: and jut.terr_qual_id8 is not null
3602: and jut.QUAL8_VALUE_ID is not null
3612: QUAL9_VALUE2 qual_VALUE2,
3613: QUAL9_VALUE3 qual_VALUE3, ORG_ID,
3614: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3615: CREATED_BY, CREATION_DATE
3616: FROM JTY_WEBADI_OTH_TERR_INTF jut
3617: where jut.USER_SEQUENCE = v_user_sequence
3618: and jut.status is null
3619: and jut.terr_qual_id9 is not null
3620: and jut.QUAL9_VALUE_ID is not null
3630: QUAL10_VALUE2 qual_VALUE2,
3631: QUAL10_VALUE3 qual_VALUE3, ORG_ID,
3632: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3633: CREATED_BY, CREATION_DATE
3634: FROM JTY_WEBADI_OTH_TERR_INTF jut
3635: where jut.USER_SEQUENCE = v_user_sequence
3636: and jut.status is null
3637: and jut.terr_qual_id10 is not null
3638: and jut.QUAL10_VALUE_ID is not null
3648: QUAL11_VALUE2 qual_VALUE2,
3649: QUAL11_VALUE3 qual_VALUE3, ORG_ID,
3650: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3651: CREATED_BY, CREATION_DATE
3652: FROM JTY_WEBADI_OTH_TERR_INTF jut
3653: where jut.USER_SEQUENCE = v_user_sequence
3654: and jut.status is null
3655: and jut.terr_qual_id11 is not null
3656: and jut.QUAL11_VALUE_ID is not null
3666: QUAL12_VALUE2 qual_VALUE2,
3667: QUAL12_VALUE3 qual_VALUE3, ORG_ID,
3668: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3669: CREATED_BY, CREATION_DATE
3670: FROM JTY_WEBADI_OTH_TERR_INTF jut
3671: where jut.USER_SEQUENCE = v_user_sequence
3672: and jut.status is null
3673: and jut.terr_qual_id12 is not null
3674: and jut.QUAL12_VALUE_ID is not null
3684: QUAL13_VALUE2 qual_VALUE2,
3685: QUAL13_VALUE3 qual_VALUE3, ORG_ID,
3686: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3687: CREATED_BY, CREATION_DATE
3688: FROM JTY_WEBADI_OTH_TERR_INTF jut
3689: where jut.USER_SEQUENCE = v_user_sequence
3690: and jut.status is null
3691: and jut.terr_qual_id13 is not null
3692: and jut.QUAL13_VALUE_ID is not null
3702: QUAL14_VALUE2 qual_VALUE2,
3703: QUAL14_VALUE3 qual_VALUE3, ORG_ID,
3704: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3705: CREATED_BY, CREATION_DATE
3706: FROM JTY_WEBADI_OTH_TERR_INTF jut
3707: where jut.USER_SEQUENCE = v_user_sequence
3708: and jut.status is null
3709: and jut.terr_qual_id14 is not null
3710: and jut.QUAL14_VALUE_ID is not null
3720: QUAL15_VALUE2 qual_VALUE2,
3721: QUAL15_VALUE3 qual_VALUE3, ORG_ID,
3722: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3723: CREATED_BY, CREATION_DATE
3724: FROM JTY_WEBADI_OTH_TERR_INTF jut
3725: where jut.USER_SEQUENCE = v_user_sequence
3726: and jut.status is null
3727: and jut.terr_qual_id15 is not null
3728: and jut.QUAL15_VALUE_ID is not null
3738: QUAL16_VALUE2 qual_VALUE2,
3739: QUAL16_VALUE3 qual_VALUE3, ORG_ID,
3740: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3741: CREATED_BY, CREATION_DATE
3742: FROM JTY_WEBADI_OTH_TERR_INTF jut
3743: where jut.USER_SEQUENCE = v_user_sequence
3744: and jut.status is null
3745: and jut.terr_qual_id16 is not null
3746: and jut.QUAL16_VALUE_ID is not null
3756: QUAL17_VALUE2 qual_VALUE2,
3757: QUAL17_VALUE3 qual_VALUE3, ORG_ID,
3758: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3759: CREATED_BY, CREATION_DATE
3760: FROM JTY_WEBADI_OTH_TERR_INTF jut
3761: where jut.USER_SEQUENCE = v_user_sequence
3762: and jut.status is null
3763: and jut.terr_qual_id17 is not null
3764: and jut.QUAL17_VALUE_ID is not null
3774: QUAL18_VALUE2 qual_VALUE2,
3775: QUAL18_VALUE3 qual_VALUE3, ORG_ID,
3776: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3777: CREATED_BY, CREATION_DATE
3778: FROM JTY_WEBADI_OTH_TERR_INTF jut
3779: where jut.USER_SEQUENCE = v_user_sequence
3780: and jut.status is null
3781: and jut.terr_qual_id18 is not null
3782: and jut.QUAL18_VALUE_ID is not null
3792: QUAL19_VALUE2 qual_VALUE2,
3793: QUAL19_VALUE3 qual_VALUE3, ORG_ID,
3794: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3795: CREATED_BY, CREATION_DATE
3796: FROM JTY_WEBADI_OTH_TERR_INTF jut
3797: where jut.USER_SEQUENCE = v_user_sequence
3798: and jut.status is null
3799: and jut.terr_qual_id19 is not null
3800: and jut.QUAL19_VALUE_ID is not null
3810: QUAL20_VALUE2 qual_VALUE2,
3811: QUAL20_VALUE3 qual_VALUE3, ORG_ID,
3812: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3813: CREATED_BY, CREATION_DATE
3814: FROM JTY_WEBADI_OTH_TERR_INTF jut
3815: where jut.USER_SEQUENCE = v_user_sequence
3816: and jut.status is null
3817: and jut.terr_qual_id20 is not null
3818: and jut.QUAL20_VALUE_ID is not null
3828: QUAL21_VALUE2 qual_VALUE2,
3829: QUAL21_VALUE3 qual_VALUE3, ORG_ID,
3830: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3831: CREATED_BY, CREATION_DATE
3832: FROM JTY_WEBADI_OTH_TERR_INTF jut
3833: where jut.USER_SEQUENCE = v_user_sequence
3834: and jut.status is null
3835: and jut.terr_qual_id21 is not null
3836: and jut.QUAL21_VALUE_ID is not null
3846: QUAL22_VALUE2 qual_VALUE2,
3847: QUAL22_VALUE3 qual_VALUE3, ORG_ID,
3848: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3849: CREATED_BY, CREATION_DATE
3850: FROM JTY_WEBADI_OTH_TERR_INTF jut
3851: where jut.USER_SEQUENCE = v_user_sequence
3852: and jut.status is null
3853: and jut.terr_qual_id22 is not null
3854: and jut.QUAL22_VALUE_ID is not null
3864: QUAL23_VALUE2 qual_VALUE2,
3865: QUAL23_VALUE3 qual_VALUE3, ORG_ID,
3866: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3867: CREATED_BY, CREATION_DATE
3868: FROM JTY_WEBADI_OTH_TERR_INTF jut
3869: where jut.USER_SEQUENCE = v_user_sequence
3870: and jut.status is null
3871: and jut.terr_qual_id23 is not null
3872: and jut.QUAL23_VALUE_ID is not null
3882: QUAL24_VALUE2 qual_VALUE2,
3883: QUAL24_VALUE3 qual_VALUE3, ORG_ID,
3884: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3885: CREATED_BY, CREATION_DATE
3886: FROM JTY_WEBADI_OTH_TERR_INTF jut
3887: where jut.USER_SEQUENCE = v_user_sequence
3888: and jut.status is null
3889: and jut.terr_qual_id24 is not null
3890: and jut.QUAL24_VALUE_ID is not null
3900: QUAL25_VALUE2 qual_valUE2,
3901: QUAL25_VALUE3 qual_valUE3, ORG_ID,
3902: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3903: CREATED_BY, CREATION_DATE
3904: FROM JTY_WEBADI_OTH_TERR_INTF jut
3905: where jut.USER_SEQUENCE = v_user_sequence
3906: and jut.status is null
3907: and jut.terr_qual_id25 is not null
3908: and jut.QUAL25_VALUE_ID is not null
3923: from (
3924: select terr_id, user_sequence,
3925: terr_qual_id1 terr_qual_id,
3926: 1 qual_num, QUAL1_VALUE_ID qual_value_id
3927: FROM JTY_WEBADI_OTH_TERR_INTF jut
3928: where jut.USER_SEQUENCE = v_user_sequence
3929: and jut.status is null
3930: and jut.terr_qual_id1 is not null
3931: and jut.QUAL1_VALUE_ID is not null
3936: union all
3937: select terr_id, user_sequence,
3938: terr_qual_id2 terr_qual_id,
3939: 2 qual_num, QUAL2_VALUE_ID qual_value_id
3940: FROM JTY_WEBADI_OTH_TERR_INTF jut
3941: where jut.USER_SEQUENCE = v_user_sequence
3942: and jut.status is null
3943: and jut.terr_qual_id2 is not null
3944: and jut.QUAL2_VALUE_ID is not null
3949: union all
3950: select terr_id, user_sequence,
3951: terr_qual_id3 terr_qual_id,
3952: 3 qual_num, QUAL3_VALUE_ID qual_value_id
3953: FROM JTY_WEBADI_OTH_TERR_INTF jut
3954: where jut.USER_SEQUENCE = v_user_sequence
3955: and jut.status is null
3956: and jut.terr_qual_id3 is not null
3957: and jut.QUAL3_VALUE_ID is not null
3962: union all
3963: select terr_id, user_sequence,
3964: terr_qual_id4 terr_qual_id,
3965: 4 qual_num, QUAL4_VALUE_ID qual_value_id
3966: FROM JTY_WEBADI_OTH_TERR_INTF jut
3967: where jut.USER_SEQUENCE = v_user_sequence
3968: and jut.status is null
3969: and jut.terr_qual_id4 is not null
3970: and jut.QUAL4_VALUE_ID is not null
3975: union all
3976: select terr_id, user_sequence,
3977: terr_qual_id5 terr_qual_id,
3978: 5 qual_num, QUAL5_VALUE_ID qual_value_id
3979: FROM JTY_WEBADI_OTH_TERR_INTF jut
3980: where jut.USER_SEQUENCE = v_user_sequence
3981: and jut.status is null
3982: and jut.terr_qual_id5 is not null
3983: and jut.QUAL5_VALUE_ID is not null
3988: union all
3989: select terr_id, user_sequence,
3990: terr_qual_id6 terr_qual_id,
3991: 6 qual_num, QUAL6_VALUE_ID qual_value_id
3992: FROM JTY_WEBADI_OTH_TERR_INTF jut
3993: where jut.USER_SEQUENCE = v_user_sequence
3994: and jut.status is null
3995: and jut.terr_qual_id6 is not null
3996: and jut.QUAL6_VALUE_ID is not null
4001: union all
4002: select terr_id, user_sequence,
4003: terr_qual_id7 terr_qual_id,
4004: 7 qual_num, QUAL7_VALUE_ID qual_value_id
4005: FROM JTY_WEBADI_OTH_TERR_INTF jut
4006: where jut.USER_SEQUENCE = v_user_sequence
4007: and jut.status is null
4008: and jut.terr_qual_id7 is not null
4009: and jut.QUAL7_VALUE_ID is not null
4014: union all
4015: select terr_id, user_sequence,
4016: terr_qual_id8 terr_qual_id,
4017: 8 qual_num, QUAL8_VALUE_ID qual_value_id
4018: FROM JTY_WEBADI_OTH_TERR_INTF jut
4019: where jut.USER_SEQUENCE = v_user_sequence
4020: and jut.status is null
4021: and jut.terr_qual_id8 is not null
4022: and jut.QUAL8_VALUE_ID is not null
4027: union all
4028: select terr_id, user_sequence,
4029: terr_qual_id9 terr_qual_id,
4030: 9 qual_num, QUAL9_VALUE_ID qual_value_id
4031: FROM JTY_WEBADI_OTH_TERR_INTF jut
4032: where jut.USER_SEQUENCE = v_user_sequence
4033: and jut.status is null
4034: and jut.terr_qual_id9 is not null
4035: and jut.QUAL9_VALUE_ID is not null
4040: union all
4041: select terr_id, user_sequence,
4042: terr_qual_id10 terr_qual_id,
4043: 10 qual_num, QUAL10_VALUE_ID qual_value_id
4044: FROM JTY_WEBADI_OTH_TERR_INTF jut
4045: where jut.USER_SEQUENCE = v_user_sequence
4046: and jut.status is null
4047: and jut.terr_qual_id10 is not null
4048: and jut.QUAL10_VALUE_ID is not null
4053: union all
4054: select terr_id, user_sequence,
4055: terr_qual_id11 terr_qual_id,
4056: 11 qual_num, QUAL11_VALUE_ID qual_value_id
4057: FROM JTY_WEBADI_OTH_TERR_INTF jut
4058: where jut.USER_SEQUENCE = v_user_sequence
4059: and jut.status is null
4060: and jut.terr_qual_id11 is not null
4061: and jut.QUAL11_VALUE_ID is not null
4066: union all
4067: select terr_id, user_sequence,
4068: terr_qual_id12 terr_qual_id,
4069: 12 qual_num, QUAL12_VALUE_ID qual_value_id
4070: FROM JTY_WEBADI_OTH_TERR_INTF jut
4071: where jut.USER_SEQUENCE = v_user_sequence
4072: and jut.status is null
4073: and jut.terr_qual_id12 is not null
4074: and jut.QUAL12_VALUE_ID is not null
4079: union all
4080: select terr_id, user_sequence,
4081: terr_qual_id13 terr_qual_id,
4082: 13 qual_num, QUAL13_VALUE_ID qual_value_id
4083: FROM JTY_WEBADI_OTH_TERR_INTF jut
4084: where jut.USER_SEQUENCE = v_user_sequence
4085: and jut.status is null
4086: and jut.terr_qual_id13 is not null
4087: and jut.QUAL13_VALUE_ID is not null
4092: union all
4093: select terr_id, user_sequence,
4094: terr_qual_id14 terr_qual_id,
4095: 14 qual_num, QUAL14_VALUE_ID qual_value_id
4096: FROM JTY_WEBADI_OTH_TERR_INTF jut
4097: where jut.USER_SEQUENCE = v_user_sequence
4098: and jut.status is null
4099: and jut.terr_qual_id14 is not null
4100: and jut.QUAL14_VALUE_ID is not null
4105: union all
4106: select terr_id, user_sequence,
4107: terr_qual_id15 terr_qual_id,
4108: 15 qual_num, QUAL15_VALUE_ID qual_value_id
4109: FROM JTY_WEBADI_OTH_TERR_INTF jut
4110: where jut.USER_SEQUENCE = v_user_sequence
4111: and jut.status is null
4112: and jut.terr_qual_id15 is not null
4113: and jut.QUAL15_VALUE_ID is not null
4118: union all
4119: select terr_id, user_sequence,
4120: terr_qual_id16 terr_qual_id,
4121: 16 qual_num, QUAL16_VALUE_ID qual_value_id
4122: FROM JTY_WEBADI_OTH_TERR_INTF jut
4123: where jut.USER_SEQUENCE = v_user_sequence
4124: and jut.status is null
4125: and jut.terr_qual_id16 is not null
4126: and jut.QUAL16_VALUE_ID is not null
4131: union all
4132: select terr_id, user_sequence,
4133: terr_qual_id17 terr_qual_id,
4134: 17 qual_num, QUAL17_VALUE_ID qual_value_id
4135: FROM JTY_WEBADI_OTH_TERR_INTF jut
4136: where jut.USER_SEQUENCE = v_user_sequence
4137: and jut.status is null
4138: and jut.terr_qual_id17 is not null
4139: and jut.QUAL17_VALUE_ID is not null
4144: union all
4145: select terr_id, user_sequence,
4146: terr_qual_id18 terr_qual_id,
4147: 18 qual_num, QUAL18_VALUE_ID qual_value_id
4148: FROM JTY_WEBADI_OTH_TERR_INTF jut
4149: where jut.USER_SEQUENCE = v_user_sequence
4150: and jut.status is null
4151: and jut.terr_qual_id18 is not null
4152: and jut.QUAL18_VALUE_ID is not null
4157: union all
4158: select terr_id, user_sequence,
4159: terr_qual_id19 terr_qual_id,
4160: 19 qual_num, QUAL19_VALUE_ID qual_value_id
4161: FROM JTY_WEBADI_OTH_TERR_INTF jut
4162: where jut.USER_SEQUENCE = v_user_sequence
4163: and jut.status is null
4164: and jut.terr_qual_id19 is not null
4165: and jut.QUAL19_VALUE_ID is not null
4170: union all
4171: select terr_id, user_sequence,
4172: terr_qual_id20 terr_qual_id,
4173: 20 qual_num, QUAL20_VALUE_ID qual_value_id
4174: FROM JTY_WEBADI_OTH_TERR_INTF jut
4175: where jut.USER_SEQUENCE = v_user_sequence
4176: and jut.status is null
4177: and jut.terr_qual_id20 is not null
4178: and jut.QUAL20_VALUE_ID is not null
4183: union all
4184: select terr_id, user_sequence,
4185: terr_qual_id21 terr_qual_id,
4186: 21 qual_num, QUAL21_VALUE_ID qual_value_id
4187: FROM JTY_WEBADI_OTH_TERR_INTF jut
4188: where jut.USER_SEQUENCE = v_user_sequence
4189: and jut.status is null
4190: and jut.terr_qual_id21 is not null
4191: and jut.QUAL21_VALUE_ID is not null
4196: union all
4197: select terr_id, user_sequence,
4198: terr_qual_id22 terr_qual_id,
4199: 22 qual_num, QUAL22_VALUE_ID qual_value_id
4200: FROM JTY_WEBADI_OTH_TERR_INTF jut
4201: where jut.USER_SEQUENCE = v_user_sequence
4202: and jut.status is null
4203: and jut.terr_qual_id22 is not null
4204: and jut.QUAL22_VALUE_ID is not null
4209: union all
4210: select terr_id, user_sequence,
4211: terr_qual_id23 terr_qual_id,
4212: 23 qual_num, QUAL23_VALUE_ID qual_value_id
4213: FROM JTY_WEBADI_OTH_TERR_INTF jut
4214: where jut.USER_SEQUENCE = v_user_sequence
4215: and jut.status is null
4216: and jut.terr_qual_id23 is not null
4217: and jut.QUAL23_VALUE_ID is not null
4222: union all
4223: select terr_id, user_sequence,
4224: terr_qual_id24 terr_qual_id,
4225: 24 qual_num, QUAL24_VALUE_ID qual_value_id
4226: FROM JTY_WEBADI_OTH_TERR_INTF jut
4227: where jut.USER_SEQUENCE = v_user_sequence
4228: and jut.status is null
4229: and jut.terr_qual_id24 is not null
4230: and jut.QUAL24_VALUE_ID is not null
4235: union all
4236: select terr_id, user_sequence,
4237: terr_qual_id25 terr_qual_id,
4238: 25 qual_num, QUAL25_VALUE_ID qual_value_id
4239: FROM JTY_WEBADI_OTH_TERR_INTF jut
4240: where jut.USER_SEQUENCE = v_user_sequence
4241: and jut.status is null
4242: and jut.terr_qual_id25 is not null
4243: and jut.QUAL25_VALUE_ID is not null
4288: x_msg_data => x_msg_data);
4289: end if;
4290:
4291: forall i in l_Terr_Qual_Rec.TERR_ID.first..l_Terr_Qual_Rec.TERR_ID.last
4292: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4293: set
4294: jwot.terr_qual_id1 = decode(l_Terr_Qual_Rec.qualifier_num(i),1,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id1),
4295: jwot.terr_qual_id2 = decode(l_Terr_Qual_Rec.qualifier_num(i),2,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id2),
4296: jwot.terr_qual_id3 = decode(l_Terr_Qual_Rec.qualifier_num(i),3,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id3),
4322: and interface_type = l_intf_type;
4323:
4324: if x_return_status = FND_API.G_RET_STS_ERROR then
4325: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
4326: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4327: SET STATUS = x_return_status,
4328: ERROR_MSG = x_msg_data
4329: WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
4330: ( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
4373: x_msg_data => x_msg_data);
4374: end if;
4375:
4376: forall i in l_Terr_Qual_Rec.TERR_ID.first..l_Terr_Qual_Rec.TERR_ID.last
4377: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4378: set
4379: jwot.terr_qual_id1 = decode(l_Terr_Qual_Rec.qualifier_num(i),1,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id1),
4380: jwot.terr_qual_id2 = decode(l_Terr_Qual_Rec.qualifier_num(i),2,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id2),
4381: jwot.terr_qual_id3 = decode(l_Terr_Qual_Rec.qualifier_num(i),3,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id3),
4407: and interface_type = l_intf_type;
4408:
4409: if x_return_status = FND_API.G_RET_STS_ERROR then
4410: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
4411: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4412: SET STATUS = x_return_status,
4413: ERROR_MSG = x_msg_data
4414: WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
4415: ( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
4449: x_msg_data => x_msg_data);
4450:
4451: if x_return_status = FND_API.G_RET_STS_ERROR then
4452: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
4453: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4454: SET STATUS = x_return_status,
4455: ERROR_MSG = x_msg_data
4456: WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
4457: ( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
4608: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_QUAL_VAL');
4609: X_Msg_Data := fnd_message.get();
4610:
4611: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
4612: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4613: SET STATUS = x_return_status,
4614: error_msg = 'U: ' || x_msg_data
4615: WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
4616: ( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
4652: and jtq.terr_qual_id = l_Terr_Qual_Rec.TERR_QUAL_ID(i);
4653: --dbms_output.put_line(' U: get_d_terr_value_csr:TV, actual row processed: '||SQL%ROWCOUNT);
4654:
4655: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
4656: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4657: SET STATUS = x_return_status
4658: WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
4659: ( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
4660: jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
4677: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_QUAL_VAL');
4678: X_Msg_Data := fnd_message.get();
4679:
4680: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
4681: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
4682: SET STATUS = x_return_status,
4683: error_msg = 'U: ' || x_msg_data
4684: WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
4685: ( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
4697: and user_sequence = p_user_sequence;
4698: END;
4699: end if; --get_d_terr_value_csr
4700:
4701: update JTY_WEBADI_OTH_TERR_INTF
4702: set status = x_return_status
4703: where interface_type = l_intf_type
4704: and header = l_header
4705: and status is null
4738: jwr.ATTRIBUTE9, jwr.ATTRIBUTE10, jwr.ATTRIBUTE11,
4739: jwr.ATTRIBUTE12, jwr.ATTRIBUTE13, jwr.ATTRIBUTE14,
4740: jwr.ATTRIBUTE15
4741: from
4742: JTY_WEBADI_OTH_TERR_INTF jwot,
4743: JTY_WEBADI_RESOURCES jwr,
4744: jtf_rs_resource_extns res
4745: where jwr.RESOURCE_ID = res.resource_id(+)
4746: and jwr.user_sequence = jwot.user_sequence
4762: jwot.CREATION_DATE, jwot.CREATED_BY, jwot.LAST_UPDATE_LOGIN,
4763: sub.TERR_RSC_ID, jq.QUAL_TYPE_NAME ACCESS_TYPE,
4764: jwot.ORG_ID, sub.TRANS_ACCESS_CODE
4765: from JTY_WEBADI_QUAL_TYPE_HEADER jq,
4766: JTY_WEBADI_OTH_TERR_INTF jwot,
4767: (
4768: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
4769: 1 qual_type_num, TERR_RSC_ACCESS_ID1 TERR_RSC_ACCESS_ID,
4770: TRANS_ACCESS_CODE1 TRANS_ACCESS_CODE
4881: jwot.CREATION_DATE, jwot.CREATED_BY, jwot.LAST_UPDATE_LOGIN,
4882: sub.TERR_RSC_ID, jq.QUAL_TYPE_NAME ACCESS_TYPE,
4883: jwot.ORG_ID, sub.TRANS_ACCESS_CODE
4884: from JTY_WEBADI_QUAL_TYPE_HEADER jq,
4885: JTY_WEBADI_OTH_TERR_INTF jwot,
4886: (
4887: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
4888: 1 qual_type_num, TERR_RSC_ACCESS_ID1 TERR_RSC_ACCESS_ID,
4889: TRANS_ACCESS_CODE1 TRANS_ACCESS_CODE
5223: l_rsc_access_rec.ORG_ID(i)
5224: );
5225:
5226: forall i in l_rsc_access_rec.TERR_RSC_ID.first..l_rsc_access_rec.TERR_RSC_ID.last
5227: update JTY_WEBADI_OTH_TERR_INTF jwot
5228: set status = x_return_status
5229: where exists
5230: (select 1 from JTY_WEBADI_RESOURCES jwr
5231: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
5246: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_CREATE_ACCESS');
5247: X_Msg_Data := fnd_message.get();
5248:
5249: forall i in l_rsc_access_rec.TERR_RSC_ID.first..l_rsc_access_rec.TERR_RSC_ID.last
5250: update JTY_WEBADI_OTH_TERR_INTF jwot
5251: set status = x_return_status,
5252: error_msg = x_msg_data
5253: where exists
5254: (select 1 from JTY_WEBADI_RESOURCES jwr
5288: ORG_ID = l_rsc_access_rec.ORG_ID(i)
5289: where TERR_RSC_ACCESS_ID = l_rsc_access_rec.TERR_RSC_ACCESS_ID(i);
5290:
5291: forall i in l_rsc_access_rec.TERR_RSC_ID.first..l_rsc_access_rec.TERR_RSC_ID.last
5292: update JTY_WEBADI_OTH_TERR_INTF jwot
5293: set status = x_return_status
5294: where exists
5295: (select 1 from JTY_WEBADI_RESOURCES jwr
5296: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
5311: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_ACCESS');
5312: X_Msg_Data := fnd_message.get();
5313:
5314: forall i in l_rsc_access_rec.TERR_RSC_ID.first..l_rsc_access_rec.TERR_RSC_ID.last
5315: update JTY_WEBADI_OTH_TERR_INTF jwot
5316: set status = x_return_status,
5317: error_msg = x_msg_data
5318: where exists
5319: (select 1 from JTY_WEBADI_RESOURCES jwr
5334: fnd_message.clear;
5335: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_RSC');
5336: X_Msg_Data := fnd_message.get();
5337:
5338: update JTY_WEBADI_OTH_TERR_INTF jwot
5339: set status = x_return_status,
5340: error_msg = X_Msg_Data
5341: where exists
5342: (select 1 from JTY_WEBADI_RESOURCES jwr
5387:
5388: begin
5389: -- remove old inserted values from the interface table
5390: -- added 05/26/2006, bug 5249085
5391: delete from JTY_WEBADI_OTH_TERR_INTF
5392: where interface_type = l_intf_type
5393: and status is not null;
5394: --dbms_output.put_line('removed old data, row count: '||SQL%ROWCOUNT);
5395: commit;
5480: x_msg_data => x_msg_data);
5481: else
5482: select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
5483: bulk collect into l_status, l_error_msg, l_lay_seq_num
5484: from JTY_WEBADI_OTH_TERR_INTF
5485: where interface_type = l_intf_type
5486: and user_sequence = p_user_sequence;
5487:
5488: -- rollback all updated info
5488: -- rollback all updated info
5489: rollback;
5490:
5491: forall i in l_lay_seq_num.first..l_lay_seq_num.last
5492: update JTY_WEBADI_OTH_TERR_INTF
5493: set status = l_status(i),
5494: error_msg = l_error_msg(i)
5495: where lay_seq_num = l_lay_seq_num(i)
5496: and interface_type = l_intf_type
5498: end if;-- qualifier
5499: else
5500: select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
5501: bulk collect into l_status, l_error_msg, l_lay_seq_num
5502: from JTY_WEBADI_OTH_TERR_INTF
5503: where interface_type = l_intf_type
5504: and user_sequence = p_user_sequence;
5505:
5506: -- rollback all updated info
5506: -- rollback all updated info
5507: rollback;
5508:
5509: forall i in l_lay_seq_num.first..l_lay_seq_num.last
5510: update JTY_WEBADI_OTH_TERR_INTF
5511: set status = l_status(i),
5512: error_msg = l_error_msg(i)
5513: where lay_seq_num = l_lay_seq_num(i)
5514: and interface_type = l_intf_type
5516: end if; -- resource
5517: else
5518: select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
5519: bulk collect into l_status, l_error_msg, l_lay_seq_num
5520: from JTY_WEBADI_OTH_TERR_INTF
5521: where interface_type = l_intf_type
5522: and user_sequence = p_user_sequence;
5523:
5524: -- rollback all updated info
5524: -- rollback all updated info
5525: rollback;
5526:
5527: forall i in l_lay_seq_num.first..l_lay_seq_num.last
5528: update JTY_WEBADI_OTH_TERR_INTF
5529: set status = l_status(i),
5530: error_msg = l_error_msg(i)
5531: where lay_seq_num = l_lay_seq_num(i)
5532: and interface_type = l_intf_type
5535:
5536: else
5537: select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
5538: bulk collect into l_status, l_error_msg, l_lay_seq_num
5539: from JTY_WEBADI_OTH_TERR_INTF
5540: where interface_type = l_intf_type
5541: and user_sequence = p_user_sequence;
5542:
5543: -- rollback all updated info
5543: -- rollback all updated info
5544: rollback;
5545:
5546: forall i in l_lay_seq_num.first..l_lay_seq_num.last
5547: update JTY_WEBADI_OTH_TERR_INTF
5548: set status = l_status(i),
5549: error_msg = l_error_msg(i)
5550: where lay_seq_num = l_lay_seq_num(i)
5551: and interface_type = l_intf_type
5553: end if; -- territory
5554: else
5555: select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
5556: bulk collect into l_status, l_error_msg, l_lay_seq_num
5557: from JTY_WEBADI_OTH_TERR_INTF
5558: where interface_type = l_intf_type
5559: and user_sequence = p_user_sequence;
5560:
5561: -- rollback all updated info
5561: -- rollback all updated info
5562: rollback;
5563:
5564: forall i in l_lay_seq_num.first..l_lay_seq_num.last
5565: update JTY_WEBADI_OTH_TERR_INTF
5566: set status = l_status(i),
5567: error_msg = l_error_msg(i)
5568: where lay_seq_num = l_lay_seq_num(i)
5569: and interface_type = l_intf_type