[Home] [Help]
246: /* TOTAL mode => re-generate all NA and GEO territories */
247: IF (p_mode = 'TOTAL') THEN
248:
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
330: Write_Log(2, 'Start deleting the territories corresponding to the self-service geography territory : ' || p_geo_territory_id);
331: END IF;
332:
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
393: Write_Log(2, 'Start deleting the territories corresponding to the territory group account : ' || p_terr_grp_acct_id);
394: END IF;
395:
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
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 */
434: /* but we do not need to do this if only sales team has been updated */
435: IF (p_change_type <> 'SALES_TEAM_UPDATE') THEN
432: /* corresponding to the catch-all territory which are not present */
433: /* in jtf_tty_acct_qual_maps for the territory group */
434: /* but we do not need to do this if only sales team has been updated */
435: IF (p_change_type <> 'SALES_TEAM_UPDATE') THEN
436: DELETE FROM jtf_terr_values_all jtv
437: WHERE NOT EXISTS
438: (SELECT 1
439: FROM jtf_tty_terr_grp_accts A
440: ,jtf_tty_acct_qual_maps B
492: END IF;
493:
494: --Delete Territory Values
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)
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 */
539: /* in jtf_tty_acct_qual_maps for the territory group */
540: /* but we do not need to do this if only sales team has been updated */
541:
540: /* but we do not need to do this if only sales team has been updated */
541:
542: IF (p_change_type <> 'SALES_TEAM_UPDATE') THEN
543: forall idx in p_terrGrpId_tbl.FIRST .. p_terrGrpId_tbl.LAST
544: DELETE from jtf_terr_values_all jtv
545: WHERE NOT EXISTS
546: (SELECT 1
547: FROM jtf_tty_terr_grp_accts A
548: ,jtf_tty_acct_qual_maps B
698: END IF;
699:
700: IF p_terr_creation_flag IS NULL THEN
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
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
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
1216: , jtv.CURRENCY_CODE
1217: , jtv.ORG_ID
1218: , jtv.ID_USED_FLAG
1219: , jtv.LOW_VALUE_CHAR_ID
1220: FROM jtf_terr_values_all jtv
1221: WHERE jtv.terr_qual_id = lp_terr_qual_id;
1222:
1223: /* get the geographies
1224: ** used for OVERLAY territory creation */
2717: , jtv.CURRENCY_CODE
2718: , jtv.ORG_ID
2719: , jtv.ID_USED_FLAG
2720: , jtv.LOW_VALUE_CHAR_ID
2721: FROM jtf_terr_values_all jtv
2722: WHERE jtv.terr_qual_id = lp_terr_qual_id;
2723:
2724: /* get the geographies
2725: ** used for OVERLAY territory creation */
4769: IF (l_na_count > 0) THEN
4770:
4771: /***************************************************************/
4772: /* (4) START: CREATE CUSTOMER KEY NAME VALUES FOR CATCH_ALL */
4773: /* TERRITORY IN TABLE JTF_TERR_VALUES_ALL */
4774: /***************************************************************/
4775:
4776: IF ( p_matching_rule_code(x) IN ('1', '2') AND
4777: p_generate_catchall_flag(x) = 'Y' ) THEN
4788: FOR catchall IN catchall_cust(p_terr_grp_acct_id(x)) LOOP
4789: -- check to see if the customer key name and comparision operator exist for the catchall territory
4790: SELECT COUNT(*)
4791: INTO l_cust_count
4792: FROM jtf_terr_values_all
4793: WHERE comparison_operator = catchall.comparison_operator
4794: AND low_value_char = catchall.value1_char
4795: AND terr_qual_id = l_terr_qual_id;
4796:
4793: WHERE comparison_operator = catchall.comparison_operator
4794: AND low_value_char = catchall.value1_char
4795: AND terr_qual_id = l_terr_qual_id;
4796:
4797: -- if the record does not exist , insert a record in jtf_terr_values_all
4798: IF (l_cust_count = 0) THEN
4799:
4800: k := k + 1;
4801: l_terr_values_tbl(k).TERR_VALUE_ID := NULL;
4846: END IF;
4847:
4848: /***************************************************************/
4849: /* (4) END: CREATE CUSTOMER KEY NAME VALUES FOR CATCH_ALL */
4850: /* TERRITORY IN TABLE JTF_TERR_VALUES_ALL */
4851: /***************************************************************/
4852:
4853: /***************************************************************/
4854: /* (5) START: CREATE NA TERRITORIES FOR NAs IN TERRITORY GROUP */
7448: , jtv.CURRENCY_CODE
7449: , jtv.ORG_ID
7450: , jtv.ID_USED_FLAG
7451: , jtv.LOW_VALUE_CHAR_ID
7452: FROM jtf_terr_values_all jtv
7453: WHERE jtv.terr_qual_id = lp_terr_qual_id;
7454:
7455: /* get those roles for a territory Group that
7456: ** do not have Product Interest defined */