[Home] [Help]
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;
68: TYPE g_geo_territory_id_tab IS TABLE OF jtf_tty_geo_terr.geo_territory_id%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;
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;
594: /* Territory Group Accounts that are deleted */
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
604: /* no need to process the deleted TGA if the corresponding TG has been updated */
605: AND NOT EXISTS (
606: SELECT 1
607: FROM jtf_tty_named_acct_changes F
608: WHERE F.object_type = 'TG'
609: AND F.object_id = B.terr_group_id
610: AND F.creation_date <= l_date);
611:
809: /* Territory Groups that are deleted */
810: CURSOR terr_grp_delete(l_date DATE) IS
811: /* Get the territory groups that have been physically deleted */
812: SELECT A.object_id
813: FROM jtf_tty_named_acct_changes A
814: WHERE A.creation_date <= l_date
815: AND A.change_type = 'DELETE'
816: AND A.object_type = 'TG'
817: UNION
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;
825:
826: l_object_id l_object_id_tab;
827: l_no_of_records NUMBER;
828: BEGIN
10837: FROM (
10838: /* Get the territory group account for which the sales team has been updated */
10839: SELECT A.OBJECT_ID terr_group_account_id
10840: ,'SALES_TEAM_UPDATE' change_type
10841: FROM JTF_TTY_NAMED_ACCT_CHANGES A
10842: WHERE A.creation_date <= l_date
10843: AND A.change_type = 'UPDATE'
10844: AND A.object_type = 'TGA'
10845: AND NOT EXISTS
10844: AND A.object_type = 'TGA'
10845: AND NOT EXISTS
10846: /* if the territory group account is already deleted , no need to process the insert/update */
10847: ( SELECT 1
10848: FROM jtf_tty_named_acct_changes B
10849: WHERE A.object_id = B.object_id
10850: AND B.object_type = 'TGA'
10851: AND B.change_type = 'DELETE')
10852: UNION
10853: /* Get the territory group account for which the mapping **
10854: ** of the corresponding named account has been updated */
10855: SELECT B.terr_group_account_id terr_group_account_id
10856: ,'MAPPING_UPDATE' change_type
10857: FROM jtf_tty_named_acct_changes A
10858: ,jtf_tty_terr_grp_accts B
10859: WHERE A.creation_date <= l_date
10860: AND A.change_type = 'UPDATE'
10861: AND A.object_type = 'NA'
10877: AND NVL(F.named_account_flag ,'N') <> 'Y'
10878: /* no need to process the TGA if it is part of the TG that has been updated */
10879: AND NOT EXISTS (
10880: SELECT 1
10881: FROM jtf_tty_named_acct_changes F
10882: WHERE F.object_type = 'TG'
10883: AND F.object_id = C.terr_group_id
10884: AND F.creation_date <= l_date)
10885: GROUP BY
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'
11147: AND C.object_id = A.terr_group_id
11149: AND A.self_service_type = 'NAMED_ACCOUNT'
11150: AND NOT EXISTS
11151: /* if the territory group is already deleted , no need to process the insert/update */
11152: ( SELECT 1
11153: FROM jtf_tty_named_acct_changes D
11154: WHERE D.object_id = C.object_id
11155: AND D.object_type = 'TG'
11156: AND D.change_type = 'DELETE')
11157: GROUP BY
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'
11199: AND C.object_id = A.terr_group_id
11201: AND A.self_service_type = 'GEOGRAPHY'
11202: AND NOT EXISTS
11203: /* if the territory group is already deleted , no need to process the insert/update */
11204: ( SELECT 1
11205: FROM jtf_tty_named_acct_changes D
11206: WHERE D.object_id = C.object_id
11207: AND D.object_type = 'TG'
11208: AND D.change_type = 'DELETE')
11209: GROUP BY
11464:
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
11474: /* no need to process the deleted GT if the corresponding TG has been updated */
11475: AND NOT EXISTS (
11476: SELECT 1
11477: FROM jtf_tty_named_acct_changes F
11478: WHERE F.object_type = 'TG'
11479: AND F.object_id = B.terr_group_id
11480: AND F.creation_date <= l_date);
11481:
11481:
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
11491: /* no need to process the GT if the corresponding TG has been updated */
11492: AND NOT EXISTS (
11493: SELECT 1
11494: FROM jtf_tty_named_acct_changes F
11495: WHERE F.object_type = 'TG'
11496: AND F.object_id = B.terr_group_id
11497: AND F.creation_date <= l_date);
11498:
11694:
11695: -- enable all the triggers
11696: alter_triggers(p_status => 'ENABLE');
11697:
11698: -- delete the records from jtf_tty_named_acct_changes which have been processed
11699: DELETE jtf_tty_named_acct_changes
11700: WHERE creation_date <= g_cutoff_time;
11701:
11702: EXCEPTION
11695: -- enable all the triggers
11696: alter_triggers(p_status => 'ENABLE');
11697:
11698: -- delete the records from jtf_tty_named_acct_changes which have been processed
11699: DELETE jtf_tty_named_acct_changes
11700: WHERE creation_date <= g_cutoff_time;
11701:
11702: EXCEPTION
11703: WHEN OTHERS THEN
11994: -- enable all the triggers
11995: alter_triggers(p_status => 'ENABLE');
11996:
11997: -- truncate the table jtf_tty_named_accts so that next incremental runs donot process the rows processed in total mode
11998: l_stmt := 'truncate table '||g_jtf_schema||'.'|| 'jtf_tty_named_acct_changes';
11999: EXECUTE IMMEDIATE l_stmt;
12000:
12001: EXCEPTION
12002: WHEN OTHERS THEN