[Home] [Help]
433: WHERE terr_group_id = p_terr_gp_id;
434:
435: /* finally delete the terr gp itself */
436:
437: DELETE FROM jtf_tty_terr_groups
438: WHERE terr_group_id = p_terr_gp_id;
439:
440: /* GSST decom. by SHLI */
441: -- log_event(p_terr_gp_id, 'DELETE', 'Delete Territory Group', 'TG', fnd_global.user_id);
1733: jtf_tty_my_resources_v repdn,
1734: jtf_tty_named_acct_rsc narsc,
1735: jtf_rs_resource_extns rs,
1736: jtf_rs_group_members mem,
1737: jtf_tty_terr_groups ttygrp
1738: WHERE na.named_account_id = ga.named_account_id
1739: AND ga.terr_group_account_id = narsc.terr_group_account_id
1740: AND narsc.resource_id = repdn.resource_id
1741: AND narsc.rsc_group_id = repdn.group_id
1757: jtf_tty_my_resources_v repdn,
1758: jtf_tty_named_acct_rsc narsc,
1759: jtf_rs_resource_extns rs,
1760: jtf_rs_group_members mem,
1761: jtf_tty_terr_groups ttygrp
1762: WHERE na.named_account_id = ga.named_account_id
1763: AND ga.terr_group_account_id = narsc.terr_group_account_id
1764: AND narsc.resource_id = repdn.resource_id
1765: AND narsc.rsc_group_id = repdn.group_id
1782: AND a.group_id = p_group_id;
1783:
1784: CURSOR num_geos_c IS
1785: SELECT COUNT(gt.geo_territory_id) geo
1786: FROM jtf_tty_geo_terr_rsc gt, jtf_tty_geo_terr gterr, jtf_tty_terr_groups tg
1787: WHERE gt.geo_territory_id = gterr.geo_territory_id
1788: AND tg.terr_group_id = gterr.terr_group_id
1789: AND TRUNC(tg.active_from_date) <= TRUNC(SYSDATE)
1790: AND (tg.active_to_date IS NULL OR TRUNC(tg.active_to_date) >= TRUNC(SYSDATE))
1798:
1799: CURSOR salesrep_named_accounts_c
1800: IS SELECT COUNT(na.NAMED_ACCOUNT_ID) num_accounts
1801: FROM jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
1802: jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
1803: WHERE na.named_account_id = tga.named_account_id
1804: AND tga.terr_group_id = tg.terr_group_id
1805: AND (tg.active_from_date <= SYSDATE OR tg.active_from_date IS NULL)
1806: AND (tg.active_to_date >= SYSDATE OR tg.active_to_date IS NULL)
1812:
1813: CURSOR salesrep_named_accounts_all_c
1814: IS SELECT COUNT(na.NAMED_ACCOUNT_ID) num_accounts
1815: FROM jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
1816: jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
1817: WHERE na.named_account_id = tga.named_account_id
1818: AND tga.terr_group_id = tg.terr_group_id
1819: AND (tg.active_from_date <= SYSDATE OR tg.active_from_date IS NULL)
1820: AND (tg.active_to_date >= SYSDATE OR tg.active_to_date IS NULL)
2123: 'RS_EMPLOYEE',
2124: na.site_type_code,
2125: count(na.NAMED_ACCOUNT_ID) num_accts
2126: from jtf_tty_named_accts na, jtf_tty_named_acct_rsc narsc,
2127: jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
2128: where na.named_account_id = tga.named_account_id
2129: and tga.terr_group_id = tg.terr_group_id
2130: and (tg.active_from_date <= sysdate or tg.active_from_date is null)
2131: and (tg.active_to_date >= sysdate or tg.active_to_date is null)
2187: 'RS_EMPLOYEE',
2188: na.site_type_code,
2189: count(na.NAMED_ACCOUNT_ID) num_accts
2190: from jtf_tty_named_accts na, jtf_tty_acct_rsc_dn narsc,
2191: jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
2192: where na.named_account_id = tga.named_account_id
2193: and narsc.TERR_GROUP_ACCOUNT_ID = tga.TERR_GROUP_ACCOUNT_ID
2194: and tga.terr_group_id = tg.terr_group_id
2195: and (tg.active_from_date <= sysdate or tg.active_from_date is null)
2278: AND ROWNUM < 2 ;
2279: END IF;
2280: END IF;
2281: IF (p_action_type = 'INSERT') THEN
2282: INSERT INTO JTF_TTY_TERR_GROUPS(
2283: TERR_GROUP_ID,
2284: OBJECT_VERSION_NUMBER,
2285: TERR_GROUP_NAME,
2286: DESCRIPTION,
2324: p_generate_na_flag,
2325: p_group_type);
2326: ELSE
2327:
2328: UPDATE JTF_TTY_TERR_GROUPS
2329: SET TERR_GROUP_NAME = p_terr_gp_name,
2330: DESCRIPTION = p_description,
2331: RANK = p_rank,
2332: ACTIVE_FROM_DATE = p_active_from_date,