eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_TTY_GEN_TERR_PVT dependencies on JTF_TERR_ALL

Line 63: TYPE g_org_id_tab IS TABLE OF jtf_terr_all.org_id%TYPE;

59: TYPE g_catch_all_resource_id_tab IS TABLE OF jtf_tty_terr_groups.catch_all_resource_id%TYPE;
60: TYPE g_catch_all_resource_type_tab IS TABLE OF jtf_tty_terr_groups.catch_all_resource_type%TYPE;
61: TYPE g_generate_catchall_flag_tab IS TABLE OF jtf_tty_terr_groups.generate_catchall_flag%TYPE;
62: TYPE g_num_winners_tab IS TABLE OF jtf_tty_terr_groups.num_winners%TYPE;
63: TYPE g_org_id_tab IS TABLE OF jtf_terr_all.org_id%TYPE;
64: TYPE g_change_type_tab IS TABLE OF jtf_tty_named_acct_changes.change_type%TYPE;
65: TYPE g_from_where_tab IS TABLE OF jtf_tty_named_acct_changes.from_where%TYPE;
66: TYPE g_terr_group_account_id_tab IS TABLE OF jtf_tty_terr_grp_accts.terr_group_account_id%TYPE;
67: TYPE g_terr_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;

Line 67: TYPE g_terr_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;

63: TYPE g_org_id_tab IS TABLE OF jtf_terr_all.org_id%TYPE;
64: TYPE g_change_type_tab IS TABLE OF jtf_tty_named_acct_changes.change_type%TYPE;
65: TYPE g_from_where_tab IS TABLE OF jtf_tty_named_acct_changes.from_where%TYPE;
66: TYPE g_terr_group_account_id_tab IS TABLE OF jtf_tty_terr_grp_accts.terr_group_account_id%TYPE;
67: TYPE g_terr_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;
68: TYPE g_geo_territory_id_tab IS TABLE OF jtf_tty_geo_terr.geo_territory_id%TYPE;
69: TYPE g_geo_terr_name_tab IS TABLE OF jtf_tty_geo_terr.geo_terr_name%TYPE;
70: TYPE g_terr_attribute_tab IS TABLE OF jtf_tty_terr_grp_accts.attribute1%TYPE;
71: TYPE g_terr_attr_cat_tab IS TABLE OF jtf_terr_all.attribute_category%TYPE;

Line 71: TYPE g_terr_attr_cat_tab IS TABLE OF jtf_terr_all.attribute_category%TYPE;

67: TYPE g_terr_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;
68: TYPE g_geo_territory_id_tab IS TABLE OF jtf_tty_geo_terr.geo_territory_id%TYPE;
69: TYPE g_geo_terr_name_tab IS TABLE OF jtf_tty_geo_terr.geo_terr_name%TYPE;
70: TYPE g_terr_attribute_tab IS TABLE OF jtf_tty_terr_grp_accts.attribute1%TYPE;
71: TYPE g_terr_attr_cat_tab IS TABLE OF jtf_terr_all.attribute_category%TYPE;
72: -- TYPE g_terr_created_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;
73: -- TYPE g_terr_creation_flag_tab IS TABLE OF VARCHAR2(1);
74:
75:

Line 72: -- TYPE g_terr_created_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;

68: TYPE g_geo_territory_id_tab IS TABLE OF jtf_tty_geo_terr.geo_territory_id%TYPE;
69: TYPE g_geo_terr_name_tab IS TABLE OF jtf_tty_geo_terr.geo_terr_name%TYPE;
70: TYPE g_terr_attribute_tab IS TABLE OF jtf_tty_terr_grp_accts.attribute1%TYPE;
71: TYPE g_terr_attr_cat_tab IS TABLE OF jtf_terr_all.attribute_category%TYPE;
72: -- TYPE g_terr_created_id_tab IS TABLE OF jtf_terr_all.terr_id%TYPE;
73: -- TYPE g_terr_creation_flag_tab IS TABLE OF VARCHAR2(1);
74:
75:
76: --------------------------------------------------------------------

Line 253: FROM jtf_terr_qual_all jtq, jtf_terr_all jt

249: --DELETE territory value records
250: DELETE FROM jtf_terr_values_all jtv
251: WHERE jtv.terr_qual_id IN
252: ( SELECT jtq.terr_qual_id
253: FROM jtf_terr_qual_all jtq, jtf_terr_all jt
254: WHERE jtq.terr_id = jt.terr_id
255: AND jt.terr_group_flag = 'Y' );
256:
257: --Delete Territory Qualifer records

Line 261: FROM jtf_terr_all jt

257: --Delete Territory Qualifer records
258: DELETE FROM JTF_TERR_QUAL_ALL jtq
259: WHERE jtq.terr_id IN
260: ( SELECT jt.terr_id
261: FROM jtf_terr_all jt
262: WHERE jt.terr_group_flag = 'Y' );
263:
264:
265: --Delete Territory qual type usgs

Line 269: FROM jtf_terr_all jt

265: --Delete Territory qual type usgs
266: DELETE FROM JTF_TERR_QTYPE_USGS_ALL jtqu
267: WHERE jtqu.terr_id IN
268: ( SELECT jt.terr_id
269: FROM jtf_terr_all jt
270: WHERE jt.terr_group_flag = 'Y' );
271:
272:
273: --Delete Territory usgs

Line 277: FROM jtf_terr_all jt

273: --Delete Territory usgs
274: DELETE FROM JTF_TERR_USGS_ALL jtu
275: WHERE jtu.terr_id IN
276: ( SELECT jt.terr_id
277: FROM jtf_terr_all jt
278: WHERE jt.terr_group_flag = 'Y' );
279:
280:
281: --Delete Territory Resource Access

Line 285: FROM jtf_terr_rsc_all jtr, jtf_terr_all jt

281: --Delete Territory Resource Access
282: DELETE FROM JTF_TERR_RSC_ACCESS_ALL jtra
283: WHERE jtra.terr_rsc_id IN
284: ( SELECT jtr.terr_rsc_id
285: FROM jtf_terr_rsc_all jtr, jtf_terr_all jt
286: WHERE jtr.terr_id = jt.terr_id
287: AND jt.terr_group_flag = 'Y' );
288:
289:

Line 294: FROM jtf_terr_all jt

290: -- Delete the Territory Resource records
291: DELETE FROM JTF_TERR_RSC_ALL jtr
292: WHERE jtr.terr_id IN
293: ( SELECT jt.terr_id
294: FROM jtf_terr_all jt
295: WHERE jt.terr_group_flag = 'Y' );
296:
297:
298: --Delete Territory record

Line 299: DELETE FROM JTF_TERR_ALL jt

295: WHERE jt.terr_group_flag = 'Y' );
296:
297:
298: --Delete Territory record
299: DELETE FROM JTF_TERR_ALL jt
300: WHERE jt.terr_id IN
301: ( SELECT jt.terr_id
302: FROM jtf_terr_all jt
303: WHERE jt.terr_group_flag = 'Y' );

Line 302: FROM jtf_terr_all jt

298: --Delete Territory record
299: DELETE FROM JTF_TERR_ALL jt
300: WHERE jt.terr_id IN
301: ( SELECT jt.terr_id
302: FROM jtf_terr_all jt
303: WHERE jt.terr_group_flag = 'Y' );
304:
305:
306: END IF;

Line 337: ,JTF_TERR_ALL B

333: --Delete Territory Values
334: DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
335: ( SELECT TERR_QUAL_ID
336: FROM JTF_TERR_QUAL_ALL A
337: ,JTF_TERR_ALL B
338: WHERE B.GEO_TERRITORY_ID = p_geo_territory_id
339: AND B.TERR_ID = A.TERR_ID );
340:
341: --Delete Territory Qualifer records

Line 343: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );

339: AND B.TERR_ID = A.TERR_ID );
340:
341: --Delete Territory Qualifer records
342: DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
343: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
344:
345: --Delete Territory qual type usgs
346: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
347: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );

Line 347: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );

343: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
344:
345: --Delete Territory qual type usgs
346: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
347: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
348:
349: --Delete Territory usgs
350: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
351: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );

Line 351: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );

347: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
348:
349: --Delete Territory usgs
350: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
351: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
352:
353: --Delete Territory Resource Access
354: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
355: ( SELECT TERR_RSC_ID

Line 357: ,JTF_TERR_ALL B

353: --Delete Territory Resource Access
354: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
355: ( SELECT TERR_RSC_ID
356: FROM JTF_TERR_RSC_ALL A
357: ,JTF_TERR_ALL B
358: WHERE B.GEO_TERRITORY_ID = p_geo_territory_id
359: AND B.TERR_ID = A.TERR_ID );
360:
361: -- Delete the Territory Resource records

Line 363: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );

359: AND B.TERR_ID = A.TERR_ID );
360:
361: -- Delete the Territory Resource records
362: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
363: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
364:
365: --Delete Territory records corresponding to the territory group
366: DELETE FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id;
367:

Line 366: DELETE FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id;

362: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
363: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id );
364:
365: --Delete Territory records corresponding to the territory group
366: DELETE FROM JTF_TERR_ALL WHERE GEO_TERRITORY_ID = p_geo_territory_id;
367:
368: IF G_Debug THEN
369: Write_Log(2, 'End deleting the territories corresponding to the self-service geography territory : ' || p_geo_territory_id);
370: END IF;

Line 400: ,JTF_TERR_ALL B

396: --Delete Territory Values
397: DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
398: ( SELECT TERR_QUAL_ID
399: FROM JTF_TERR_QUAL_ALL A
400: ,JTF_TERR_ALL B
401: WHERE B.TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id
402: AND B.TERR_ID = A.TERR_ID );
403:
404: --Delete Territory Qualifer records

Line 406: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

402: AND B.TERR_ID = A.TERR_ID );
403:
404: --Delete Territory Qualifer records
405: DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
406: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
407:
408: --Delete Territory qual type usgs
409: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
410: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

Line 410: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

406: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
407:
408: --Delete Territory qual type usgs
409: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
410: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
411:
412: --Delete Territory usgs
413: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
414: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

Line 414: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

410: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
411:
412: --Delete Territory usgs
413: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
414: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
415:
416: --Delete Territory Resource Access
417: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
418: ( SELECT TERR_RSC_ID

Line 420: ,JTF_TERR_ALL B

416: --Delete Territory Resource Access
417: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
418: ( SELECT TERR_RSC_ID
419: FROM JTF_TERR_RSC_ALL A
420: ,JTF_TERR_ALL B
421: WHERE B.TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id
422: AND B.TERR_ID = A.TERR_ID );
423:
424: -- Delete the Territory Resource records

Line 426: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

422: AND B.TERR_ID = A.TERR_ID );
423:
424: -- Delete the Territory Resource records
425: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
426: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
427:
428: --Delete Territory records corresponding to the territory group
429: DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id;
430: /* if the user has deleted the TGA or update the mapping */

Line 429: DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id;

425: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
426: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
427:
428: --Delete Territory records corresponding to the territory group
429: DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id;
430: /* if the user has deleted the TGA or update the mapping */
431: /* then delete the customer key name values from jtf_terr_values_all */
432: /* corresponding to the catch-all territory which are not present */
433: /* in jtf_tty_acct_qual_maps for the territory group */

Line 499: ,JTF_TERR_ALL B

495: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
496: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
497: ( SELECT TERR_QUAL_ID
498: FROM JTF_TERR_QUAL_ALL A
499: ,JTF_TERR_ALL B
500: WHERE B.TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx)
501: AND B.TERR_ID = A.TERR_ID );
502:
503: --Delete Territory Qualifer records

Line 506: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );

502:
503: --Delete Territory Qualifer records
504: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
505: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID IN
506: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
507:
508: --Delete Territory qual type usgs
509: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
510: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN

Line 511: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );

507:
508: --Delete Territory qual type usgs
509: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
510: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
511: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
512:
513: --Delete Territory usgs
514: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
515: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID IN

Line 516: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );

512:
513: --Delete Territory usgs
514: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
515: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID IN
516: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
517:
518: --Delete Territory Resource Access
519: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
520: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN

Line 523: ,JTF_TERR_ALL B

519: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
520: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
521: ( SELECT TERR_RSC_ID
522: FROM JTF_TERR_RSC_ALL A
523: ,JTF_TERR_ALL B
524: WHERE B.TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx)
525: AND B.TERR_ID = A.TERR_ID );
526:
527: -- Delete the Territory Resource records

Line 530: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );

526:
527: -- Delete the Territory Resource records
528: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
529: DELETE from JTF_TERR_RSC_ALL Where TERR_ID IN
530: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
531:
532: --Delete Territory records corresponding to the territory group
533: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
534: DELETE from JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx);

Line 534: DELETE from JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx);

530: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx) );
531:
532: --Delete Territory records corresponding to the territory group
533: forall idx in p_grpAcctId_tbl.FIRST .. p_grpAcctId_tbl.LAST
534: DELETE from JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_grpAcctId_tbl(idx);
535:
536: /* if the user has deleted the TGA or update the mapping */
537: /* then delete the customer key name values from jtf_terr_values_all */
538: /* corresponding to the catch-all territory which are not present */

Line 557: jtf_terr_all ta

553: AND B.VALUE1_CHAR = jtv.LOW_VALUE_CHAR)
554: AND terr_qual_id =
555: ( SELECT terr_qual_id
556: FROM jtf_terr_qual_all tqa,
557: jtf_terr_all ta
558: WHERE tqa.terr_id = ta.terr_id
559: AND ta.catch_all_flag='Y'
560: AND enabled_flag='Y'
561: AND SYSDATE BETWEEN ta.start_date_active AND NVL(ta.end_date_active, SYSDATE+1)

Line 599: ,jtf_terr_all B

595: CURSOR terr_grp_acct_delete(l_date DATE) IS
596: SELECT DISTINCT A.object_id
597: ,B.terr_group_id
598: FROM jtf_tty_named_acct_changes A
599: ,jtf_terr_all B
600: WHERE A.creation_date <= l_date
601: AND A.change_type = 'DELETE'
602: AND A.object_type = 'TGA'
603: AND A.object_id = B.terr_group_account_id

Line 705: ,JTF_TERR_ALL B

701: --Delete Territory Values
702: DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
703: ( SELECT TERR_QUAL_ID
704: FROM JTF_TERR_QUAL_ALL A
705: ,JTF_TERR_ALL B
706: WHERE B.TERR_GROUP_ID = p_terr_grp_id
707: AND B.TERR_ID = A.TERR_ID );
708:
709: --Delete Territory Qualifer records

Line 711: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );

707: AND B.TERR_ID = A.TERR_ID );
708:
709: --Delete Territory Qualifer records
710: DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
711: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
712:
713: --Delete Territory qual type usgs
714: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
715: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );

Line 715: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );

711: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
712:
713: --Delete Territory qual type usgs
714: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
715: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
716:
717: --Delete Territory usgs
718: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
719: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );

Line 719: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );

715: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
716:
717: --Delete Territory usgs
718: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
719: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
720:
721: --Delete Territory Resource Access
722: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
723: ( SELECT TERR_RSC_ID

Line 725: ,JTF_TERR_ALL B

721: --Delete Territory Resource Access
722: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
723: ( SELECT TERR_RSC_ID
724: FROM JTF_TERR_RSC_ALL A
725: ,JTF_TERR_ALL B
726: WHERE B.TERR_GROUP_ID = p_terr_grp_id
727: AND B.TERR_ID = A.TERR_ID );
728:
729: -- Delete the Territory Resource records

Line 731: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );

727: AND B.TERR_ID = A.TERR_ID );
728:
729: -- Delete the Territory Resource records
730: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
731: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
732:
733: --Delete Territory records corresponding to the territory group
734:
735: DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id;

Line 735: DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id;

731: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id );
732:
733: --Delete Territory records corresponding to the territory group
734:
735: DELETE FROM JTF_TERR_ALL WHERE TERR_GROUP_ID = p_terr_grp_id;
736:
737: ELSE
738: --Delete Territory Values
739: DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN

Line 742: ,JTF_TERR_ALL B

738: --Delete Territory Values
739: DELETE FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
740: ( SELECT TERR_QUAL_ID
741: FROM JTF_TERR_QUAL_ALL A
742: ,JTF_TERR_ALL B
743: WHERE B.TERR_GROUP_ID = p_terr_grp_id
744: AND B.TERR_ID = A.TERR_ID
745: AND B.TERR_ID <> p_terr_id );
746:

Line 749: ( SELECT TERR_ID FROM JTF_TERR_ALL

745: AND B.TERR_ID <> p_terr_id );
746:
747: --Delete Territory Qualifer records
748: DELETE FROM JTF_TERR_QUAL_ALL WHERE TERR_ID IN
749: ( SELECT TERR_ID FROM JTF_TERR_ALL
750: WHERE TERR_GROUP_ID = p_terr_grp_id
751: AND TERR_ID <> p_terr_id
752: );
753:

Line 756: ( SELECT TERR_ID FROM JTF_TERR_ALL

752: );
753:
754: --Delete Territory qual type usgs
755: DELETE FROM JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID IN
756: ( SELECT TERR_ID FROM JTF_TERR_ALL
757: WHERE TERR_GROUP_ID = p_terr_grp_id
758: );
759:
760: --Delete Territory usgs

Line 762: ( SELECT TERR_ID FROM JTF_TERR_ALL

758: );
759:
760: --Delete Territory usgs
761: DELETE FROM JTF_TERR_USGS_ALL WHERE TERR_ID IN
762: ( SELECT TERR_ID FROM JTF_TERR_ALL
763: WHERE TERR_GROUP_ID = p_terr_grp_id
764: -- AND TERR_ID <> p_terr_id
765: );
766:

Line 771: ,JTF_TERR_ALL B

767: --Delete Territory Resource Access
768: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
769: ( SELECT TERR_RSC_ID
770: FROM JTF_TERR_RSC_ALL A
771: ,JTF_TERR_ALL B
772: WHERE B.TERR_GROUP_ID = p_terr_grp_id
773: AND B.TERR_ID = A.TERR_ID
774: );
775:

Line 778: ( SELECT TERR_ID FROM JTF_TERR_ALL

774: );
775:
776: -- Delete the Territory Resource records
777: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
778: ( SELECT TERR_ID FROM JTF_TERR_ALL
779: WHERE TERR_GROUP_ID = p_terr_grp_id
780: );
781:
782: --Delete Territory records corresponding to the territory group

Line 784: DELETE FROM JTF_TERR_ALL

780: );
781:
782: --Delete Territory records corresponding to the territory group
783:
784: DELETE FROM JTF_TERR_ALL
785: WHERE TERR_GROUP_ID = p_terr_grp_id
786: AND TERR_ID <> p_terr_id;
787: END IF;
788:

Line 820: FROM jtf_terr_all A

816: AND A.object_type = 'TG'
817: UNION
818: /* Get the territory groups which has expired as end_date_active < sysdate */
819: SELECT DISTINCT A.terr_group_id
820: FROM jtf_terr_all A
821: WHERE A.terr_group_flag = 'Y'
822: AND A.end_date_active < l_date;
823:
824: TYPE l_object_id_tab IS TABLE OF jtf_tty_named_acct_changes.object_id%TYPE;

Line 1193: FROM jtf_terr_all

1189: , description
1190: , rank
1191: , parent_territory_id
1192: , terr_id
1193: FROM jtf_terr_all
1194: WHERE terr_id = l_terr;
1195:
1196: /* get Qualifiers used in a territory */
1197: CURSOR csr_get_qual( lp_terr_id NUMBER) IS

Line 1607: UPDATE JTF_TERR_ALL

1603:
1604: /* BEGIN: Successful Territory creation? */
1605: IF x_return_status = 'S' THEN
1606:
1607: UPDATE JTF_TERR_ALL
1608: SET TERR_GROUP_FLAG = 'Y'
1609: , TERR_GROUP_ID = p_terr_group_id(x)
1610: , CATCH_ALL_FLAG = 'N'
1611: , GEO_TERR_FLAG = 'Y'

Line 1960: UPDATE JTF_TERR_ALL

1956: );
1957:
1958: IF x_return_status = 'S' THEN
1959:
1960: UPDATE JTF_TERR_ALL
1961: SET TERR_GROUP_FLAG = 'Y'
1962: , TERR_GROUP_ID = p_terr_group_id(x)
1963: , CATCH_ALL_FLAG = 'N'
1964: , GEO_TERR_FLAG = 'Y'

Line 2198: UPDATE JTF_TERR_ALL

2194: );
2195:
2196: IF (x_return_status = 'S') THEN
2197:
2198: UPDATE JTF_TERR_ALL
2199: SET TERR_GROUP_FLAG = 'Y'
2200: , TERR_GROUP_ID = p_terr_group_id(x)
2201: , CATCH_ALL_FLAG = 'N'
2202: , GEO_TERR_FLAG = 'Y'

Line 2694: FROM jtf_terr_all

2690: , description
2691: , rank
2692: , parent_territory_id
2693: , terr_id
2694: FROM jtf_terr_all
2695: WHERE terr_id = l_terr;
2696:
2697: /* get Qualifiers used in a territory */
2698: CURSOR csr_get_qual( lp_terr_id NUMBER) IS

Line 3036: UPDATE JTF_TERR_ALL

3032: /* BEGIN: SUCCESSFUL TERRITORY CREATION? */
3033: IF X_RETURN_STATUS = 'S' THEN
3034:
3035: /* JDOCHERT: 01/08/03: ADDED TERR_GROUP_ID */
3036: UPDATE JTF_TERR_ALL
3037: SET TERR_GROUP_FLAG = 'Y'
3038: , CATCH_ALL_FLAG = 'N'
3039: , TERR_GROUP_ID = p_TERR_GROUP_ID(x)
3040: , NUM_WINNERS = p_NUM_WINNERS(x)

Line 3269: UPDATE JTF_TERR_ALL

3265:
3266: -- JDOCHERT: 01/08/03: Added p_terr_group_rec_ID and CATCH_ALL_FLAG
3267: -- and NAMED_ACCOUNT_FLAG and p_terr_group_rec_ACCOUNT_ID
3268:
3269: UPDATE JTF_TERR_ALL
3270: SET TERR_GROUP_FLAG = 'Y'
3271: , TERR_GROUP_ID = p_terr_group_id(x)
3272: , CATCH_ALL_FLAG = 'N'
3273: , GEO_TERR_FLAG = 'Y'

Line 3636: UPDATE JTF_TERR_ALL

3632:
3633: IF x_return_status = 'S' THEN
3634:
3635: -- JDOCHERT: 01/08/03: Added p_terr_group_rec.ID
3636: UPDATE JTF_TERR_ALL
3637: SET terr_group_FLAG = 'Y'
3638: , terr_group_ID = p_TERR_GROUP_ID(x)
3639: WHERE terr_id = x_terr_id;
3640:

Line 3816: UPDATE JTF_TERR_ALL

3812: );
3813:
3814: IF x_return_status = 'S' THEN
3815:
3816: UPDATE JTF_TERR_ALL
3817: SET TERR_GROUP_FLAG = 'Y'
3818: , TERR_GROUP_ID = p_terr_group_id(x)
3819: , CATCH_ALL_FLAG = 'N'
3820: , GEO_TERR_FLAG = 'Y'

Line 4054: UPDATE JTF_TERR_ALL

4050: );
4051:
4052: IF (x_return_status = 'S') THEN
4053:
4054: UPDATE JTF_TERR_ALL
4055: SET TERR_GROUP_FLAG = 'Y'
4056: , TERR_GROUP_ID = p_terr_group_id(x)
4057: , CATCH_ALL_FLAG = 'N'
4058: , GEO_TERR_FLAG = 'Y'

Line 4876: FROM jtf_terr_all jt

4872: l_terr_exists := 0;
4873:
4874: SELECT COUNT(*)
4875: INTO l_terr_exists
4876: FROM jtf_terr_all jt
4877: WHERE jt.terr_id = naterr.terr_group_account_id * -100;
4878:
4879: IF (l_terr_exists = 0) THEN
4880: l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -100;

Line 5119: UPDATE JTF_TERR_ALL

5115: IF x_return_status = 'S' THEN
5116:
5117: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG
5118: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
5119: UPDATE JTF_TERR_ALL
5120: SET TERR_GROUP_FLAG = 'Y'
5121: , TERR_GROUP_ID = p_terr_group_id(x)
5122: , CATCH_ALL_FLAG = 'N'
5123: , NAMED_ACCOUNT_FLAG = 'Y'

Line 5290: FROM jtf_terr_all jt

5286: l_terr_exists := 0;
5287:
5288: SELECT COUNT(*)
5289: INTO l_terr_exists
5290: FROM jtf_terr_all jt
5291: WHERE jt.terr_id = naterr.terr_group_account_id * -10000;
5292:
5293: IF (l_terr_exists = 0) THEN
5294: l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -10000;

Line 5500: UPDATE JTF_TERR_ALL

5496: IF x_return_status = 'S' THEN
5497:
5498: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG
5499: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
5500: UPDATE JTF_TERR_ALL
5501: SET TERR_GROUP_FLAG = 'Y'
5502: , TERR_GROUP_ID = p_terr_group_id(x)
5503: , CATCH_ALL_FLAG = 'N'
5504: , NAMED_ACCOUNT_FLAG = 'Y'

Line 5884: UPDATE JTF_TERR_ALL

5880: IF x_return_status = 'S' THEN
5881:
5882: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
5883: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
5884: UPDATE JTF_TERR_ALL
5885: SET TERR_GROUP_FLAG = 'Y'
5886: , TERR_GROUP_ID = p_TERR_GROUP_ID(x)
5887: , NAMED_ACCOUNT_FLAG = 'Y'
5888: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 6153: UPDATE JTF_TERR_ALL

6149: IF (x_return_status = 'S') THEN
6150:
6151: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
6152: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
6153: UPDATE JTF_TERR_ALL
6154: SET TERR_GROUP_FLAG = 'Y'
6155: , TERR_GROUP_ID = p_terr_group_id(x)
6156: , NAMED_ACCOUNT_FLAG = 'Y'
6157: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 6481: UPDATE JTF_TERR_ALL

6477: IF x_return_status = 'S' THEN
6478:
6479: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
6480: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
6481: UPDATE JTF_TERR_ALL
6482: SET TERR_GROUP_FLAG = 'Y'
6483: , TERR_GROUP_ID = p_terr_group_id(x)
6484: , NAMED_ACCOUNT_FLAG = 'Y'
6485: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 6734: UPDATE JTF_TERR_ALL

6730: IF (x_return_status = 'S') THEN
6731:
6732: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
6733: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
6734: UPDATE JTF_TERR_ALL
6735: SET TERR_GROUP_FLAG = 'Y'
6736: , TERR_GROUP_ID = p_TERR_GROUP_ID(x)
6737: , NAMED_ACCOUNT_FLAG = 'Y'
6738: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 7425: FROM jtf_terr_all

7421: , description
7422: , rank
7423: , parent_territory_id
7424: , terr_id
7425: FROM jtf_terr_all
7426: WHERE terr_id = l_terr;
7427:
7428: /* get Qualifiers used in a territory */
7429: CURSOR csr_get_qual( lp_terr_id NUMBER) IS

Line 7713: UPDATE JTF_TERR_ALL

7709:
7710: /* BEGIN: SUCCESSFUL TERRITORY CREATION? */
7711: IF X_RETURN_STATUS = 'S' THEN
7712: /* JDOCHERT: 01/08/03: ADDED TERR_GROUP_ID */
7713: UPDATE JTF_TERR_ALL
7714: SET TERR_GROUP_FLAG = 'Y'
7715: , CATCH_ALL_FLAG = 'N'
7716: , TERR_GROUP_ID = p_terr_group_id(x)
7717: , NUM_WINNERS = p_num_winners(x)

Line 7930: UPDATE JTF_TERR_ALL

7926: /* BEGIN: Successful Territory creation? */
7927: IF x_return_status = 'S' THEN
7928:
7929: /* JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG */
7930: UPDATE JTF_TERR_ALL
7931: SET TERR_GROUP_FLAG = 'Y'
7932: , TERR_GROUP_ID = p_terr_group_id(x)
7933: , CATCH_ALL_FLAG = 'Y'
7934: WHERE terr_id = x_terr_id;

Line 8093: FROM jtf_terr_all jt

8089: l_terr_exists := 0;
8090:
8091: SELECT COUNT(*)
8092: INTO l_terr_exists
8093: FROM jtf_terr_all jt
8094: WHERE jt.terr_id = naterr.terr_group_account_id * -100;
8095:
8096: IF (l_terr_exists = 0) THEN
8097: l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -100;

Line 8338: UPDATE JTF_TERR_ALL

8334: IF x_return_status = 'S' THEN
8335:
8336: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG
8337: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
8338: UPDATE JTF_TERR_ALL
8339: SET TERR_GROUP_FLAG = 'Y'
8340: , TERR_GROUP_ID = p_terr_group_id(x)
8341: , CATCH_ALL_FLAG = 'N'
8342: , NAMED_ACCOUNT_FLAG = 'Y'

Line 8506: FROM jtf_terr_all jt

8502: l_terr_exists := 0;
8503:
8504: SELECT COUNT(*)
8505: INTO l_terr_exists
8506: FROM jtf_terr_all jt
8507: WHERE jt.terr_id = naterr.terr_group_account_id * -10000;
8508:
8509: IF (l_terr_exists = 0) THEN
8510: l_terr_all_rec.TERR_ID := naterr.terr_group_account_id * -10000;

Line 8716: UPDATE JTF_TERR_ALL

8712: IF x_return_status = 'S' THEN
8713:
8714: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID and CATCH_ALL_FLAG
8715: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
8716: UPDATE JTF_TERR_ALL
8717: SET TERR_GROUP_FLAG = 'Y'
8718: , TERR_GROUP_ID = p_terr_group_id(x)
8719: , CATCH_ALL_FLAG = 'N'
8720: , NAMED_ACCOUNT_FLAG = 'Y'

Line 9086: UPDATE JTF_TERR_ALL

9082:
9083: IF x_return_status = 'S' THEN
9084:
9085: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
9086: UPDATE JTF_TERR_ALL
9087: SET TERR_GROUP_FLAG = 'Y'
9088: , TERR_GROUP_ID = p_TERR_GROUP_ID(x)
9089: WHERE terr_id = x_terr_id;
9090:

Line 9286: UPDATE JTF_TERR_ALL

9282: IF x_return_status = 'S' THEN
9283:
9284: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
9285: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
9286: UPDATE JTF_TERR_ALL
9287: SET TERR_GROUP_FLAG = 'Y'
9288: , TERR_GROUP_ID = p_TERR_GROUP_ID(x)
9289: , NAMED_ACCOUNT_FLAG = 'Y'
9290: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 9551: UPDATE JTF_TERR_ALL

9547: IF (x_return_status = 'S') THEN
9548:
9549: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
9550: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
9551: UPDATE JTF_TERR_ALL
9552: SET TERR_GROUP_FLAG = 'Y'
9553: , TERR_GROUP_ID = p_terr_group_id(x)
9554: , NAMED_ACCOUNT_FLAG = 'Y'
9555: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 9882: UPDATE JTF_TERR_ALL

9878: IF x_return_status = 'S' THEN
9879:
9880: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
9881: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
9882: UPDATE JTF_TERR_ALL
9883: SET TERR_GROUP_FLAG = 'Y'
9884: , TERR_GROUP_ID = p_terr_group_id(x)
9885: , NAMED_ACCOUNT_FLAG = 'Y'
9886: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 10135: UPDATE JTF_TERR_ALL

10131: IF (x_return_status = 'S') THEN
10132:
10133: -- JDOCHERT: 01/08/03: Added TERR_GROUP_ID
10134: -- and NAMED_ACCOUNT_FLAG and TERR_GROUP_ACCOUNT_ID
10135: UPDATE JTF_TERR_ALL
10136: SET TERR_GROUP_FLAG = 'Y'
10137: , TERR_GROUP_ID = p_TERR_GROUP_ID(x)
10138: , NAMED_ACCOUNT_FLAG = 'Y'
10139: , TERR_GROUP_ACCOUNT_ID = overlayterr.terr_group_account_id

Line 10396: ,jtf_terr_all D

10392: ,-1 * B.terr_group_id
10393: ,E.terr_id
10394: FROM jtf_tty_geo_terr B
10395: ,jtf_tty_terr_groups C
10396: ,jtf_terr_all D
10397: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10398: WHERE B.geo_territory_id = l_geo_territory_id
10399: AND B.terr_group_id = C.terr_group_id
10400: AND C.parent_terr_id = D.terr_id

Line 10397: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch

10393: ,E.terr_id
10394: FROM jtf_tty_geo_terr B
10395: ,jtf_tty_terr_groups C
10396: ,jtf_terr_all D
10397: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10398: WHERE B.geo_territory_id = l_geo_territory_id
10399: AND B.terr_group_id = C.terr_group_id
10400: AND C.parent_terr_id = D.terr_id
10401: AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist

Line 10587: ,jtf_terr_all D

10583: ,-1 * B.terr_group_id
10584: ,E.terr_id
10585: FROM jtf_tty_geo_terr B
10586: ,jtf_tty_terr_groups C
10587: ,jtf_terr_all D
10588: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10589: WHERE B.geo_territory_id = l_geo_terr_id
10590: AND B.terr_group_id = C.terr_group_id
10591: AND C.parent_terr_id = D.terr_id

Line 10588: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch

10584: ,E.terr_id
10585: FROM jtf_tty_geo_terr B
10586: ,jtf_tty_terr_groups C
10587: ,jtf_terr_all D
10588: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10589: WHERE B.geo_territory_id = l_geo_terr_id
10590: AND B.terr_group_id = C.terr_group_id
10591: AND C.parent_terr_id = D.terr_id
10592: AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist

Line 10865: ,jtf_terr_all D -- to get the org_id of the parent territory

10861: AND A.object_type = 'NA'
10862: AND A.object_id = B.named_account_id) A
10863: ,jtf_tty_terr_grp_accts B
10864: ,jtf_tty_terr_groups C
10865: ,jtf_terr_all D -- to get the org_id of the parent territory
10866: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10867: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
10868: WHERE A.terr_group_account_id = B.terr_group_account_id
10869: AND B.terr_group_id = C.terr_group_id

Line 10866: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch

10862: AND A.object_id = B.named_account_id) A
10863: ,jtf_tty_terr_grp_accts B
10864: ,jtf_tty_terr_groups C
10865: ,jtf_terr_all D -- to get the org_id of the parent territory
10866: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10867: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
10868: WHERE A.terr_group_account_id = B.terr_group_account_id
10869: AND B.terr_group_id = C.terr_group_id
10870: AND C.parent_terr_id = D.terr_id

Line 10867: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch

10863: ,jtf_tty_terr_grp_accts B
10864: ,jtf_tty_terr_groups C
10865: ,jtf_terr_all D -- to get the org_id of the parent territory
10866: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
10867: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
10868: WHERE A.terr_group_account_id = B.terr_group_account_id
10869: AND B.terr_group_id = C.terr_group_id
10870: AND C.parent_terr_id = D.terr_id
10871: AND E.terr_group_id(+) = C.terr_group_id -- outer-join is necessary as overlay branch may not exist

Line 11142: ,JTF_TERR_ALL B

11138: , B.ORG_ID
11139: , MIN(C.change_type) change_type
11140: -- if there is both insert and update to a territory group , we need to process only the insert
11141: FROM JTF_TTY_TERR_GROUPS A
11142: ,JTF_TERR_ALL B
11143: ,JTF_TTY_NAMED_ACCT_CHANGES C
11144: WHERE C.creation_date <= l_date
11145: AND C.change_type IN ('INSERT', 'UPDATE')
11146: AND C.object_type = 'TG'

Line 11194: ,JTF_TERR_ALL B

11190: , B.ORG_ID
11191: , MIN(C.change_type) change_type
11192: -- if there is both insert and update to a territory group , we need to process only the insert
11193: FROM JTF_TTY_TERR_GROUPS A
11194: ,JTF_TERR_ALL B
11195: ,JTF_TTY_NAMED_ACCT_CHANGES C
11196: WHERE C.creation_date <= l_date
11197: AND C.change_type IN ('INSERT', 'UPDATE')
11198: AND C.object_type = 'TG'

Line 11469: ,jtf_terr_all B

11465: /* Geography territories that are deleted */
11466: CURSOR geo_terr_delete(l_date DATE) IS
11467: SELECT DISTINCT A.object_id
11468: FROM jtf_tty_named_acct_changes A
11469: ,jtf_terr_all B
11470: WHERE A.creation_date <= l_date
11471: AND A.change_type = 'DELETE'
11472: AND A.object_type = 'GT'
11473: AND A.object_id = B.geo_territory_id

Line 11486: ,jtf_terr_all B

11482: /* Parent of the geography territories that are deleted */
11483: CURSOR geo_terr_parent_delete(l_date DATE) IS
11484: SELECT DISTINCT A.object_id
11485: FROM jtf_tty_named_acct_changes A
11486: ,jtf_terr_all B
11487: WHERE A.creation_date <= l_date
11488: AND A.change_type = 'DELETE PARENT'
11489: AND A.object_type = 'GT'
11490: AND A.object_id = B.geo_territory_id

Line 11737: , JTF_TERR_ALL B

11733: , A.NUM_WINNERS
11734: , B.ORG_ID
11735: , 'INSERT'
11736: FROM JTF_TTY_TERR_GROUPS A
11737: , JTF_TERR_ALL B
11738: WHERE a.parent_terr_id = b.terr_id
11739: AND ( a.active_to_date >= SYSDATE OR a.active_to_date IS NULL )
11740: AND a.active_from_date <= SYSDATE
11741: AND a.self_service_type = 'NAMED_ACCOUNT';

Line 11760: , JTF_TERR_ALL B

11756: , A.NUM_WINNERS
11757: , B.ORG_ID
11758: , 'INSERT'
11759: FROM JTF_TTY_TERR_GROUPS A
11760: , JTF_TERR_ALL B
11761: WHERE a.parent_terr_id = b.terr_id
11762: AND ( a.active_to_date >= SYSDATE OR a.active_to_date IS NULL )
11763: AND a.active_from_date <= SYSDATE
11764: AND a.self_service_type = 'GEOGRAPHY';

Line 12176: ,jtf_terr_all D -- to get the org_id of the parent territory

12172: ,B.attribute15
12173: FROM
12174: jtf_tty_terr_grp_accts B
12175: ,jtf_tty_terr_groups C
12176: ,jtf_terr_all D -- to get the org_id of the parent territory
12177: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
12178: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
12179: WHERE B.terr_group_account_id = l_terr_grp_acct_id
12180: AND B.terr_group_id = C.terr_group_id

Line 12177: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch

12173: FROM
12174: jtf_tty_terr_grp_accts B
12175: ,jtf_tty_terr_groups C
12176: ,jtf_terr_all D -- to get the org_id of the parent territory
12177: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
12178: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
12179: WHERE B.terr_group_account_id = l_terr_grp_acct_id
12180: AND B.terr_group_id = C.terr_group_id
12181: AND C.terr_group_id = D.terr_group_id

Line 12178: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch

12174: jtf_tty_terr_grp_accts B
12175: ,jtf_tty_terr_groups C
12176: ,jtf_terr_all D -- to get the org_id of the parent territory
12177: ,jtf_terr_all E -- to get the terr_id of the top level territory of overlay branch
12178: ,jtf_terr_all F -- to get the terr_id for the placeholder territory of non-overlay branch
12179: WHERE B.terr_group_account_id = l_terr_grp_acct_id
12180: AND B.terr_group_id = C.terr_group_id
12181: AND C.terr_group_id = D.terr_group_id
12182: AND D.terr_group_flag = 'Y'

Line 12405: ,JTF_TERR_ALL B

12401: , A.NUM_WINNERS
12402: , B.ORG_ID
12403: , p_change_type change_type
12404: FROM JTF_TTY_TERR_GROUPS A
12405: ,JTF_TERR_ALL B
12406: ,JTF_TERR_ALL C
12407: WHERE A.parent_terr_id = B.terr_id
12408: AND B.terr_id = C.parent_territory_id
12409: AND B.org_id = C.org_id

Line 12406: ,JTF_TERR_ALL C

12402: , B.ORG_ID
12403: , p_change_type change_type
12404: FROM JTF_TTY_TERR_GROUPS A
12405: ,JTF_TERR_ALL B
12406: ,JTF_TERR_ALL C
12407: WHERE A.parent_terr_id = B.terr_id
12408: AND B.terr_id = C.parent_territory_id
12409: AND B.org_id = C.org_id
12410: AND C.terr_id = p_terr_id

Line 12431: ,JTF_TERR_ALL B

12427: , A.NUM_WINNERS
12428: , B.ORG_ID
12429: , p_change_type change_type
12430: FROM JTF_TTY_TERR_GROUPS A
12431: ,JTF_TERR_ALL B
12432: WHERE A.terr_group_id = l_terr_group_id
12433: AND A.parent_terr_id = B.terr_id
12434: AND A.self_service_type = 'GEOGRAPHY';
12435:

Line 12585: FROM JTF_TERR_ALL

12581:
12582: CURSOR c_get_terr_id (l_terr_grp_id NUMBER)
12583: IS
12584: SELECT terr_id
12585: FROM JTF_TERR_ALL
12586: WHERE CATCH_ALL_FLAG='Y'
12587: AND TERR_GROUP_ID = l_terr_grp_id;
12588:
12589: BEGIN

Line 12607: DELETE FROM JTF_TERR_ALL

12603:
12604: DELETE FROM JTF_TERR_RSC_ALL
12605: WHERE TERR_ID = l_catchall_terr_id;
12606:
12607: DELETE FROM JTF_TERR_ALL
12608: WHERE TERR_ID = l_catchall_terr_id;
12609:
12610: COMMIT;
12611: EXCEPTION

Line 12627: FROM JTF_TERR_ALL

12623:
12624: CURSOR c_get_terr_id (l_terr_grp_id NUMBER)
12625: IS
12626: SELECT terr_id
12627: FROM JTF_TERR_ALL
12628: WHERE CATCH_ALL_FLAG='Y'
12629: AND TERR_GROUP_ID = l_terr_grp_id;
12630:
12631: BEGIN

Line 12650: FROM JTF_TERR_ALL

12646:
12647: DELETE FROM JTF_TERR_RSC_ALL
12648: WHERE TERR_ID IN
12649: ( SELECT terr_id
12650: FROM JTF_TERR_ALL
12651: WHERE CATCH_ALL_FLAG='Y'
12652: AND TERR_GROUP_ID = p_terr_group_id
12653: );
12654:

Line 12699: FROM JTF_TERR_ALL

12695: INTO l_terr_rsc_id
12696: FROM DUAL;
12697:
12698: SELECT terr_id INTO l_catchall_terr_id
12699: FROM JTF_TERR_ALL
12700: WHERE CATCH_ALL_FLAG='Y'
12701: AND TERR_GROUP_ID = p_terr_group_id;
12702:
12703: INSERT INTO jtf_terr_rsc_all

Line 12795: FROM jtf_terr_all

12791:
12792: Cursor NATG_TERR IS
12793: SELECT terr_id,
12794: TERR_GROUP_ID
12795: FROM jtf_terr_all
12796: CONNECT BY parent_territory_id = PRIOR terr_id
12797: AND TERR_ID <> 1
12798: AND CATCH_ALL_FLAG <> 'Y'
12799: AND NAMED_ACCOUNT_FLAG <> 'Y'

Line 12916: FROM jtf_terr_all

12912:
12913: CURSOR c_get_terr_dtls ( l_terr_grp_acct_id NUMBER)
12914: IS
12915: SELECT terr_id, org_id, trunc(start_date_active), trunc(end_date_active)
12916: FROM jtf_terr_all
12917: WHERE terr_group_account_id = l_terr_grp_acct_id;
12918:
12919: /* Access Types for a particular Role within a Territory Group */
12920: CURSOR role_access(l_terr_group_id NUMBER,l_role VARCHAR2) IS

Line 12935: ,JTF_TERR_ALL B

12931: --Delete Territory Resource Access
12932: DELETE FROM JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
12933: ( SELECT TERR_RSC_ID
12934: FROM JTF_TERR_RSC_ALL A
12935: ,JTF_TERR_ALL B
12936: WHERE B.TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id
12937: AND B.TERR_ID = A.TERR_ID );
12938:
12939: -- Delete the Territory Resource records

Line 12941: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );

12937: AND B.TERR_ID = A.TERR_ID );
12938:
12939: -- Delete the Territory Resource records
12940: DELETE FROM JTF_TERR_RSC_ALL WHERE TERR_ID IN
12941: ( SELECT TERR_ID FROM JTF_TERR_ALL WHERE TERR_GROUP_ACCOUNT_ID = p_terr_grp_acct_id );
12942:
12943: l_init_msg_list :=FND_API.G_TRUE;
12944: i := 0;
12945: a := 0;

Line 13102: UPDATE jtf_terr_all jta

13098: IS
13099:
13100: BEGIN
13101:
13102: UPDATE jtf_terr_all jta
13103: set (jta.ATTRIBUTE1, jta.ATTRIBUTE2, jta.ATTRIBUTE3,
13104: jta.ATTRIBUTE4, jta.ATTRIBUTE5, jta.ATTRIBUTE6,
13105: jta.ATTRIBUTE7, jta.ATTRIBUTE8, jta.ATTRIBUTE9,
13106: jta.ATTRIBUTE10, jta.ATTRIBUTE11, jta.ATTRIBUTE12,