[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_POP_TERR_ADMIN_BIN_PVT
Source
1 PACKAGE BODY JTF_TTY_POP_TERR_ADMIN_BIN_PVT AS
2 /* $Header: jtfvuabb.pls 120.2 2006/03/23 15:11:13 chchandr ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TTY_POP_TERR_ADMIN_BIN_PVT
6 -- PURPOSE
7 --
8 -- Procedures:
9 -- (see below for specification)
10 --
11 --
12 --
13 --
14 -- NOTES
15 --
16 --
17 --
18 --
19 -- HISTORY
20 -- 09/15/02 JRADHAKR CREATED
21 --
22 --
23 -- End of Comments
24 --
25
26 Procedure populate_catch_all_bin_info
27 ( x_return_status OUT NOCOPY VARCHAR2
28 , x_error_message OUT NOCOPY VARCHAR2
29 )
30 IS
31
32 CURSOR c_terr_list
33 IS select terr_id
34 , terr_group_id
35 from jtf_terr_all
36 WHERE CATCH_ALL_FLAG = 'Y';
37
38 L_USER_ID NUMBER := FND_GLOBAL.USER_ID();
39 L_SYSDATE DATE;
40 L_LEADS NUMBER;
41 L_OPPORTUNITIES NUMBER;
42 L_ACCOUNTS NUMBER;
43
44 BEGIN
45 L_SYSDATE := SYSDATE;
46 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('In populate_catch_all_bin_info Procedure ');
47
48 Update jtf_tty_admin_bin_summ jtabs
49 set LEADS = 0
50 , OPPORTUNITIES = 0
51 , ACCOUNTS = 0
52 , CATCH_ALL_UPDATE_DATE = L_SYSDATE
53 , LAST_UPDATED_BY = L_USER_ID
54 , LAST_UPDATE_DATE = L_SYSDATE
55 ;
56
57 for l_list in c_terr_list
58 loop
59
60 --
61 --Accounts
62 SELECT /*+ INDEX (AAA as_accesses_u1) */ COUNT(*)
63 INTO L_ACCOUNTS
64 FROM
65 as_accesses_all AAA
66 , as_territory_accesses ATA
67 WHERE AAA.ACCESS_ID = ATA.ACCESS_ID
68 AND ATA.TERRITORY_ID = l_list.terr_id
69 AND LEAD_ID IS NULL
70 AND SALES_LEAD_ID IS NULL
71 AND CUSTOMER_ID IS NOT NULL;
72
73 -- Leads
74 SELECT /*+ INDEX (AAA as_accesses_u1) */ COUNT(*)
75 INTO L_LEADS
76 FROM
77 as_accesses_all AAA
78 , as_territory_accesses ATA
79 WHERE AAA.ACCESS_ID = ATA.ACCESS_ID
80 AND ATA.TERRITORY_ID = l_list.terr_id
81 AND LEAD_ID IS NULL
82 AND SALES_LEAD_ID IS NOT NULL;
83
84
85 -- Opportunities
86 SELECT /*+ INDEX (AAA as_accesses_u1) */ COUNT(*)
87 INTO L_OPPORTUNITIES
88 FROM
89 as_accesses_all AAA
90 , as_territory_accesses ATA
91 WHERE AAA.ACCESS_ID = ATA.ACCESS_ID
92 AND ATA.TERRITORY_ID = l_list.terr_id
93 AND LEAD_ID IS NOT NULL
94 AND SALES_LEAD_ID IS NULL;
95
96
97 Update jtf_tty_admin_bin_summ jtabs
98 set LEADS = L_LEADS
99 , OPPORTUNITIES = L_OPPORTUNITIES
100 , ACCOUNTS = L_ACCOUNTS
101 where terr_group_id = l_list.terr_group_id;
102
103 end loop;
104
105
106
107 EXCEPTION
108 when FND_API.G_EXC_ERROR then
109 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('Exception others in populate_catch_all_bin_information '||SQLERRM);
110 RETURN;
111 when FND_API.G_EXC_UNEXPECTED_ERROR then
112 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log ('Exception others in populate_catch_all_bin_information '||SQLERRM);
113 RETURN;
114 when others then
115 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log ('Exception others in populate_catch_all_bin_information '||SQLERRM);
116 RETURN;
117
118 END populate_catch_all_bin_info;
119
120
121 Procedure populate_kpi_bin_info
122 ( x_return_status OUT NOCOPY VARCHAR2
123 , x_error_message OUT NOCOPY VARCHAR2
124 )
125 IS
126
127 L_USER_ID NUMBER := FND_GLOBAL.USER_ID();
128 L_SYSDATE DATE;
129
130 BEGIN
131 L_SYSDATE := SYSDATE;
132
133 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('In populate_kpi_bin_info Procedure ');
134
135 Update jtf_tty_admin_bin_summ jtabs
136 set TOTAL_NAMED_ACCOUNT = '0.0%'
137 , MAPPED_NAMED_ACC_PER = '0.0%'
138 , ASSIGNED_NAMED_ACC_PER = '0.0%'
139 , KPI_UPDATE_DATE = L_SYSDATE
140 , LAST_UPDATED_BY = L_USER_ID
141 , LAST_UPDATE_DATE = L_SYSDATE
142 ;
143
144 Update jtf_tty_admin_bin_summ jtabs
145 set (TOTAL_NAMED_ACCOUNT
146 , MAPPED_NAMED_ACC_PER
147 , ASSIGNED_NAMED_ACC_PER
148 )
149 = (select
150 to_char(tot.named_accounts) total
151 , nvl(to_char(map.mapped/tot.named_accounts * 100,'9999.9'),'0.0') || '%' mapPer
152 , nvl(to_char(ass.assigned/tot.named_accounts * 100 ,'9999.9'),'0.0') || '%' assPer
153 from ( select jga.terr_group_id
154 , count(*) assigned
155 from jtf_tty_terr_grp_accts jga
156 where jga.DN_JNR_ASSIGNED_FLAG = 'Y'
157 group by jga.terr_group_id) ASS,
158 ( select jga.terr_group_id, jtg.terr_group_name
159 , count(*) named_accounts
160 from jtf_tty_terr_grp_accts jga
161 , jtf_tty_terr_groups jtg
162 where jga.terr_group_id = jtg.terr_group_id
163 group by jga.terr_group_id, jtg.terr_group_name ) tot,
164 ( select
165 jga.terr_group_id
166 , count(*) mapped
167 from jtf_tty_terr_grp_accts jga
168 where jga.dn_jna_mapping_complete_flag = 'Y'
169 group by jga.terr_group_id ) map
170 where ass.terr_group_id (+) = tot.terr_group_id
171 and map.terr_group_id (+) = tot.terr_group_id
172 and jtabs.terr_group_id = tot.terr_group_id );
173
174
175 EXCEPTION
176 when FND_API.G_EXC_ERROR then
177 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('Exception others in populate_kpi_bin_information '||SQLERRM);
178 RETURN;
179 when FND_API.G_EXC_UNEXPECTED_ERROR then
180 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log ('Exception others in populate_kpi_bin_information '||SQLERRM);
181 RETURN;
182 when others then
183 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log ('Exception others in populate_kpi_bin_information '||SQLERRM);
184 RETURN;
185
186 END populate_kpi_bin_info;
187
188 Procedure Sync_terr_group
189 ( x_return_status OUT NOCOPY VARCHAR2
190 , x_error_message OUT NOCOPY VARCHAR2
191 )
192 IS
193
194 L_USER_ID NUMBER := FND_GLOBAL.USER_ID();
195 L_SYSDATE DATE;
196
197
198 BEGIN
199 L_SYSDATE := SYSDATE;
200
201 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('Synchronizing the SUMM table with TERR_GROUPS table');
202 /* take care of the logic moved from web adi admin excel */
203
204 UPDATE JTF_TTY_TERR_GRP_ACCTS
205 SET DN_JNR_ASSIGNED_FLAG = 'Y'
206 WHERE TERR_GROUP_ACCOUNT_ID IN
207 (select /*+ INDEX_FFS(NARSC JTF_TTY_NAMED_ACCT_RSC_N8 )*/ narsc.terr_group_account_id
208 from jtf_tty_named_acct_rsc narsc
209 where assigned_flag = 'Y');
210
211 UPDATE JTF_TTY_TERR_GRP_ACCTS
212 SET DN_JNR_ASSIGNED_FLAG = 'N'
213 WHERE TERR_GROUP_ACCOUNT_ID NOT IN
214 (select /*+ INDEX_FFS(NARSC JTF_TTY_NAMED_ACCT_RSC_N8 )*/ narsc.terr_group_account_id
215 from jtf_tty_named_acct_rsc narsc
216 where assigned_flag = 'Y');
217
218
219 delete from jtf_tty_admin_bin_summ
220 where TERR_GROUP_ID not in (
221 select TERR_GROUP_ID from jtf_tty_terr_groups
222 Where TRUNC(active_from_date) <= TRUNC(SYSDATE)
223 AND TRUNC(NVL(active_to_date, SYSDATE)) >= TRUNC(SYSDATE)
224 );
225
226 update jtf_tty_admin_bin_summ jtabs
227 set jtabs.TERR_GROUP_NAME = (select jtg.TERR_GROUP_NAME
228 from jtf_tty_terr_groups jtg
229 where jtg.TERR_GROUP_ID = jtabs.TERR_GROUP_ID);
230
231
232 insert into jtf_tty_admin_bin_summ jtabs
233 ( ADMIN_BIN_TERR_GRP_ID
234 , OBJECT_VERSION_NUMBER
235 , TERR_GROUP_ID
236 , TERR_GROUP_NAME
237 , CREATED_BY
238 , CREATION_DATE
239 , LAST_UPDATED_BY
240 , LAST_UPDATE_DATE )
241 select TERR_GROUP_ID
242 , 1
243 , TERR_GROUP_ID
244 , TERR_GROUP_NAME
245 , L_USER_ID
246 , L_SYSDATE
247 , L_USER_ID
248 , L_SYSDATE
249 from jtf_tty_terr_groups
250 where TERR_GROUP_ID not in
251 (select TERR_GROUP_ID
252 from jtf_tty_admin_bin_summ)
253 AND self_service_type = 'NAMED_ACCOUNT'
254 AND TRUNC(active_from_date) <= TRUNC(SYSDATE)
255 AND TRUNC(NVL(active_to_date, SYSDATE)) >= TRUNC(SYSDATE) ;
256
257
258 COMMIT;
259
260 EXCEPTION
261 when FND_API.G_EXC_ERROR then
262 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log (' Error in Synchronizing the SUMM table' || SQLERRM );
263 RETURN;
264 when FND_API.G_EXC_UNEXPECTED_ERROR then
265 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log (' Error in Synchronizing the SUMM table' || SQLERRM );
266 RETURN;
267 when others then
268 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log (' Error in Synchronizing the SUMM table' || SQLERRM );
269 RETURN;
270
271 END Sync_terr_group;
272
273
274 END JTF_TTY_POP_TERR_ADMIN_BIN_PVT;