1 PACKAGE BODY AMS_TCOP_UTIL_PKG AS
2 /* $Header: amsvtcub.pls 120.0 2005/05/31 22:19:38 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_TCOP_UTIL_PKG
7 -- Purpose
8 --
9 -- This package contains all the traffic cop related utilities
10 --
11 -- History
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17 -- Start of Comments
18 -- Name
19 -- Is_Fatigue_Rule_Applicable
20 --
21 -- Purpose
22 -- This function verifies if Fatigue Rule is applicable for
23 -- this schedule or not
24 -- Return Value
25 -- It returns 'Y' if Fatigue Rule is applicable for this schedule
26 -- It returns 'N' if Fatigue Rule is no applicable for this schedule
27 --
28 function Is_Fatigue_Rule_Applicable (
29 p_schedule_id number
30 )
31 return varchar2
32 is
33
34 cursor c_get_fatigue_flag(p_schedule_id number)
35 is
36 select list_header.apply_traffic_cop,
37 list_header.list_header_id
38 from ams_list_headers_all list_header
39 ,ams_act_lists act_list
40 where act_list.LIST_ACT_TYPE = 'TARGET'
41 and act_list.LIST_USED_BY = 'CSCH'
42 and act_list.LIST_USED_BY_ID = p_schedule_id
43 and act_list.LIST_HEADER_ID = list_header.LIST_HEADER_ID;
44
45 cursor c_get_rule(p_schedule_id number)
46 is
47 select rule_id
48 from ams_tcop_fr_rules_setup rule,
49 ams_campaign_schedules_b schedule
50 where rule.ENABLED_FLAG = 'Y'
51 and (rule.CHANNEL_ID is null
52 or (rule.CHANNEL_ID = schedule.ACTIVITY_ID) )
53 and rule.RULE_TYPE in ('GLOBAL' , 'CHANNEL_BASED')
54 and schedule.SCHEDULE_ID = p_schedule_id;
55
56 cursor c_get_null_party_map(p_list_header_id number)
57 is
58 select list1.list_entry_id
59 from ams_list_entries list1
60 where list1.list_header_id = p_list_header_id
61 and exists
62 (
63 select list2.list_entry_id
64 from ams_list_entries list2
65 where list1.list_entry_id=list2.list_entry_id
66 and list2.list_header_id = p_list_header_id
67 and list2.party_id is null
68 );
69
70 cursor c_get_non_tca_map(p_list_header_id number)
71 is
72 select list1.list_entry_id
73 from ams_list_entries list1
74 where list1.list_header_id=p_list_header_id
75 and not exists
76 (
77 select list2.party_id
78 from ams_list_entries list2
79 ,hz_parties hz
80 where list2.party_id=hz.party_id
81 and list2.list_header_id = p_list_header_id
82 and list2.list_entry_id=list1.list_entry_id
83 );
84
85
86 l_fatigue_flag varchar2(1);
87 l_rule_id number;
88 l_list_header_id number;
89 l_list_entry_id number;
90
91 begin
92
93 -- Check if the Apply Traffic Cop flag is set at Target Group List Header
94 -- level;
95 open c_get_fatigue_flag (p_schedule_id);
96 fetch c_get_fatigue_flag into l_fatigue_flag,l_list_header_id;
97 close c_get_fatigue_flag;
98
99 if (l_fatigue_flag = 'Y') then
100
101 -- Check if there are any Fatigue Rules relevant for this Schedule
102 -- If not,Fatigue Rule will not be applied
103
104 open c_get_rule (p_schedule_id);
105 fetch c_get_rule into l_rule_id;
106 close c_get_rule;
107
108 if (l_rule_id is not null) then
109
110 -- Check whether party_id column in AMS_LIST_ENTRIES is null
111 -- if party_id column is null then Fatigue Rule will not be applied
112 open c_get_null_party_map(l_list_header_id);
113 fetch c_get_null_party_map
114 into l_list_entry_id;
115 close c_get_null_party_map;
116
117 if (l_list_entry_id is not null) then
118 return 'N';
119 else
120 -- Check whether party_id mapped in AMS_LIST_ENTRIES
121 -- refer to HZ_PARTIES.PARTY_ID
122 -- if party_id columns is mapped to anything other than TCA Party Id
123 -- then fatigue rule will not be applicable.
124 open c_get_non_tca_map(l_list_header_id);
125 fetch c_get_non_tca_map
126 into l_list_entry_id;
127 close c_get_non_tca_map;
128
129 if (l_list_entry_id is not null) then
130 return 'N';
131 else
132 return 'Y';
133 end if;
134
135 end if;
136
137 else
138 return 'N';
139 end if;
140 else
141 return 'N';
142 end if;
143
144 end Is_Fatigue_Rule_Applicable;
145
146 END AMS_TCOP_UTIL_PKG;
[Home] [Help]