[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_RPT
Source
1 PACKAGE BODY JTF_TERR_RPT AS
2 /* $Header: jtftrtrb.pls 115.14 2004/01/09 00:35:24 vxsriniv ship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- Custom Assignment API
6 --
7 -- NOTES
8 -- ORACLE INTERNAL USE ONLY: NOT for customer use
9 --
10 -- HISTORY
11 -- 03/18/02 SGKUMAR Created
12 -- 03/20/02 SGKUMAR added new procedure insert_qualifiers
13 -- 03/20/02 SGKUMAR added new procedure set_winners
14 -- 03/20/02 SGKUMAR added new procedure get_winners
15 -- 04/12/02 SGKUMAR added code to initialize resource name
16 -- territory name, etc
17 -- 04/15/02 SGKUMAR removed resource and group related data
18 -- 05/29/02 JDOCHERT changed sql for parties to dynamic sql
19 -- 05/29/02 SGKUMAR support for shuttle
20 -- 05/30/02 SGKUMAR changed code for getting only active terr
21 -- if parent terr made inactive
22 -- End of Comments
23 ----
24 /* Description: inserts qualifier data from staging table to */
25 /* jtf_tae_rpt_objs */
26 /* Usage: public procedure , should be invoked when the staging table */ /* is populated */
27
28
29 PROCEDURE cleanup
30 as
31 begin
32 delete from jtf.jtf_tae_rpt_staging_out
33 where session_id in (select session_id from APPS.icx_sessions
34 where disabled_flag <> 'N');
35
36 delete from APPS.jtf_tae_rpt_staging_out
37 where session_id not in (select session_id from APPS.icx_sessions);
38
39 commit;
40
41 end cleanup;
42
43 PROCEDURE get_keyword_parties(p_session_id number,
44 p_terr_id number)
45 as
46
47 l_dyn_str VARCHAR2(8000);
48
49 BEGIN
50 -- delete previous data for the older dead sessions
51 cleanup;
52 delete from jtf_tae_rpt_staging_out
53 where session_id = p_session_id;
54
55 l_dyn_str :=
56 ' insert into jtf_tae_rpt_staging_out ' ||
57 ' (TRANS_OBJECT_ID, ' ||
58 ' TRANS_DETAIL_OBJECT_ID, ' ||
59 ' SOURCE_ID,' ||
60 ' TRANS_OBJECT_TYPE_ID, ' ||
61 ' TERR_ID,' ||
62 ' session_id, ' ||
63 ' at_char01, ' ||
64 ' at_char02, ' ||
65 ' low_value_char ' ||
66 ' ) ' ||
67 'select ' ||
68 ' ROW_NUMBER() OVER (ORDER BY ilv.low_value_char, ilv.org_name, ilv.org_address) ' ||
69 ' AS PSEUDO_ROWNUM, ' ||
70 ' -999, ' ||
71 ' -999, ' ||
72 ' -999, ' ||
73 ' :p_terr_id1, ' ||
74 ' :p_session_id, ' ||
75 ' ilv.org_name, ' ||
76 ' ilv.org_address, ' ||
77 ' ilv.low_value_char ' ||
78 ' FROM ( ' ||
79 ' SELECT ' ||
80 ' Q1012.low_value_char, Q1012.high_value_char ' ||
81 ' , hzp.party_name org_name ' ||
82 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
83 ' hzl.city || '', '' || hzl.state ' ||
84 ' || '', '' || hzl.postal_code org_address ' ||
85 ' FROM ' ||
86 ' APPS.jtf_terr_qual_rules_mv Q1007R1 ' ||
87 ' , APPS.jtf_terr_qual_rules_mv Q1012 ' ||
88 ' , APPS.hz_locations hzl ' ||
89 ' , APPS.hz_party_sites hzps ' ||
90 ' , APPS.hz_parties hzp ' ||
91 ' , APPS.as_accesses_all aaa ' ||
92 ' , APPS.jtf_terr_rsc_all jtr ' ||
93 ' WHERE ' ||
94 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
95 ' Q1007R1.comparison_operator = ''='' ) ' ||
96 ' OR ' ||
97 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
98 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
99 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
100 ' ) ' ||
101 ' AND Q1007R1.qual_usg_id = -1007 ' ||
102 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
103 ' AND UPPER(hzp.party_name) = Q1012.low_value_char ' ||
104 ' AND Q1012.COMPARISON_OPERATOR = ''='' ' ||
105 ' AND Q1012.qual_usg_id = -1012 ' ||
106 ' AND Q1012.terr_id = jtr.terr_id ' ||
107 ' AND hzl.location_id = hzps.location_id ' ||
108 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
109 ' AND hzps.party_id = hzp.party_id ' ||
110 ' AND hzp.status = ''A'' ' ||
111 ' AND hzp.party_id = aaa.customer_id ' ||
112 ' AND aaa.salesforce_id = jtr.resource_id ' ||
113 ' AND jtr.terr_id = :p_terr_id2 ' ||
114
115 ' UNION ' ||
116
117 ' SELECT ' ||
118 ' Q1012.low_value_char, NULL ' ||
119 ' , hzp.party_name org_name ' ||
120 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
121 ' hzl.city || '', '' || hzl.state ' ||
122 ' || '', '' || hzl.postal_code org_address ' ||
123 ' FROM ' ||
124 ' APPS.jtf_terr_qual_rules_mv Q1007R1 ' ||
125 ' , APPS.jtf_terr_cnr_qual_like_mv Q1012 ' ||
126 ' , APPS.hz_locations hzl ' ||
127 ' , APPS.hz_party_sites hzps ' ||
128 ' , APPS.hz_parties hzp ' ||
129 ' , APPS.as_accesses_all aaa ' ||
130 ' , APPS.jtf_terr_rsc_all jtr ' ||
131 ' WHERE ' ||
132 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
133 ' Q1007R1.comparison_operator = ''='' ) ' ||
134 ' OR ' ||
135 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
136 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
137 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
138 ' ) ' ||
139 ' AND Q1007R1.qual_usg_id = -1007 ' ||
140 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
141 ' AND UPPER(hzp.party_name) LIKE Q1012.low_value_char ' ||
142 ' AND UPPER(SUBSTR(hzp.party_name, 1, 1)) = Q1012.first_char ' ||
143 ' AND Q1012.qual_usg_id = -1012 ' ||
144 ' AND Q1012.terr_id = jtr.terr_id ' ||
145 ' AND hzl.location_id = hzps.location_id ' ||
146 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
147 ' AND hzps.party_id = hzp.party_id ' ||
148 ' AND hzp.status = ''A'' ' ||
149 ' AND hzp.party_id = aaa.customer_id ' ||
150 ' AND aaa.salesforce_id = jtr.resource_id ' ||
151 ' AND jtr.terr_id = :p_terr_id3 ' ||
152
153 ' UNION ' ||
154
155 ' SELECT ' ||
156 ' Q1012.low_value_char, NULL ' ||
157 ' , hzp.party_name org_name ' ||
158 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
159 ' hzl.city || '', '' || hzl.state ' ||
160 ' || '', '' || hzl.postal_code org_address ' ||
161 ' FROM ' ||
162 ' APPS.jtf_terr_qual_rules_mv Q1007R1 ' ||
163 ' , APPS.jtf_terr_cnr_qual_like_mv Q1012 ' ||
164 ' , APPS.hz_locations hzl ' ||
165 ' , APPS.hz_party_sites hzps ' ||
166 ' , APPS.hz_parties hzp ' ||
167 ' , APPS.as_accesses_all aaa ' ||
168 ' , APPS.jtf_terr_rsc_all jtr ' ||
169 ' WHERE ' ||
170 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
171 ' Q1007R1.comparison_operator = ''='' ) ' ||
172 ' OR ' ||
173 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
174 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
175 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
176 ' ) ' ||
177 ' AND Q1007R1.qual_usg_id = -1007 ' ||
178 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
179 ' AND UPPER(hzp.party_name) LIKE Q1012.low_value_char ' ||
180 ' AND ''%'' = Q1012.first_char ' ||
181 ' AND Q1012.qual_usg_id = -1012 ' ||
182 ' AND Q1012.terr_id = jtr.terr_id ' ||
183 ' AND hzl.location_id = hzps.location_id ' ||
184 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
185 ' AND hzps.party_id = hzp.party_id ' ||
186 ' AND hzp.status = ''A'' ' ||
187 ' AND hzp.party_id = aaa.customer_id ' ||
188 ' AND aaa.salesforce_id = jtr.resource_id ' ||
189 ' AND jtr.terr_id = :p_terr_id4 ' ||
190
191 ' UNION ' ||
192
193 ' SELECT ' ||
194 ' Q1012.low_value_char, Q1012.high_value_char ' ||
195 ' , hzp.party_name org_name ' ||
196 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
197 ' hzl.city || '', '' || hzl.state ' ||
198 ' || '', '' || hzl.postal_code org_address ' ||
199 ' FROM ' ||
200 ' APPS.jtf_terr_qual_rules_mv Q1007R1 ' ||
201 ' , APPS.jtf_terr_cnr_qual_btwn_mv Q1012 ' ||
202 ' , APPS.hz_locations hzl ' ||
203 ' , APPS.hz_party_sites hzps ' ||
204 ' , APPS.hz_parties hzp ' ||
205 ' , APPS.as_accesses_all aaa ' ||
206 ' , APPS.jtf_terr_rsc_all jtr ' ||
207 ' WHERE ' ||
208 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
209 ' Q1007R1.comparison_operator = ''='' ) ' ||
210 ' OR ' ||
211 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
212 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
213 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
214 ' ) ' ||
215 ' AND Q1007R1.qual_usg_id = -1007 ' ||
216 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
217 ' AND UPPER(hzp.party_name) BETWEEN Q1012.low_value_char AND Q1012.high_value_char ' ||
218 ' AND Q1012.qual_usg_id = -1012 ' ||
219 ' AND Q1012.terr_id = jtr.terr_id ' ||
220 ' AND hzl.location_id = hzps.location_id ' ||
221 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
222 ' AND hzps.party_id = hzp.party_id ' ||
223 ' AND hzp.status = ''A'' ' ||
224 ' AND hzp.party_id = aaa.customer_id ' ||
225 ' AND aaa.salesforce_id = jtr.resource_id ' ||
226 ' AND jtr.terr_id = :p_terr_id5 ' ||
227 ' ) ilv ';
228
229
230
231
232 EXECUTE IMMEDIATE l_dyn_str USING
233 p_terr_id,
234 p_session_id,
235 p_terr_id,
236 p_terr_id,
237 p_terr_id,
238 p_terr_id;
239
240 commit;
241
242 END get_keyword_parties;
243
244 PROCEDURE get_results(p_session_id number,
245 p_resource_id number,
246 p_group_id number,
247 p_active_date varchar2
248 )
249 as
250 lp_active_date DATE := SYSDATE;
251 lp_sysdate DATE := SYSDATE;
252
253 BEGIN
254 delete from jtf_tae_rpt_staging_out
255 where session_id = p_session_id;
256 /*
257 insert into jtf_tae_rpt_staging_out
258 (TRANS_OBJECT_ID,
259 TRANS_DETAIL_OBJECT_ID,
260 SOURCE_ID,
261 TRANS_OBJECT_TYPE_ID,
262 TERR_ID,
263 terr_name,
264 terr_rank,
265 session_id)
266 values(-999,
267 -999,
268 -999,
269 -999,
270 -999,
271 p_resource_id,
272 p_group_id,
273 p_session_id);
274 */
275 lp_active_date := to_date(p_active_date, 'YYYY/MM/DD');
276 insert into jtf_tae_rpt_staging_out
277 (TRANS_OBJECT_ID,
278 TRANS_DETAIL_OBJECT_ID,
279 SOURCE_ID,
280 TRANS_OBJECT_TYPE_ID,
281 TERR_ID,
282 terr_name,
283 terr_rank,
284 session_id)
285 select distinct -999,
286 -999,
287 -999,
288 -999,
289 jta.terr_id,
290 jta.name,
291 jta.rank,
292 p_session_id
293 from jtf_terr_all jta,
294 jtf_terr_rsc_all jtra
295 where jta.terr_id = jtra.terr_id
296 and (jtra.resource_id = p_resource_id or p_resource_id = -999)
297 and (jtra.group_id = p_group_id or p_group_id =-999)
298 AND NOT EXISTS (
299 SELECT jt.terr_id
300 FROM jtf_terr_all jt
301 WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(lp_active_date, lp_sysdate) ) OR
302 ( NVL(jt.start_date_active, lp_sysdate) > NVL(lp_active_date, lp_sysdate) )
303 )
304 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
305 START WITH jt.terr_id = jta.terr_id );
306
307 commit;
308 END get_results;
309
310
311
312 end JTF_TERR_RPT;