[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_RPT
Source
1 PACKAGE BODY JTF_TERR_RPT AS
2 /* $Header: jtftrtrb.pls 120.7 2011/09/22 13:00:25 swpoddar 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 -- 09/22/11 SWPODDAR changed dml queries to access table via
23 -- synonym instead
24 -- End of Comments
25 ----
26 /* Description: inserts qualifier data from staging table to */
27 /* jtf_tae_rpt_objs */
28 /* Usage: public procedure , should be invoked when the staging table */ /* is populated */
29
30
31 PROCEDURE cleanup
32 as
33 v_statement varchar2(8000);
34 l_ora_schemaname varchar2(100);
35
36 begin
37
38 select owner
39 into l_ora_schemaname
40 from all_synonyms
41 where table_owner=
42 (select owner from sys.all_objects where object_name='JTF_TAE_RPT_STAGING_OUT' and object_type='TABLE')
43 and synonym_name='JTF_TAE_RPT_STAGING_OUT';
44
45 /*delete from APPS.jtf_tae_rpt_staging_out
46 where session_id in (select session_id from l_ora_schemaname ||'.icx_sessions
47 where disabled_flag <> 'N'); */
48
49 v_statement:='delete from '||l_ora_schemaname||'.jtf_tae_rpt_staging_out where session_id
50 not in (select session_id from '||l_ora_schemaname||'.icx_sessions)';
51
52 EXECUTE IMMEDIATE v_statement;
53
54 commit;
55
56 end cleanup;
57
58 PROCEDURE get_keyword_parties(p_session_id number,
59 p_terr_id number)
60 as
61
62 l_dyn_str VARCHAR2(8000);
63 l_ora_schemaname varchar2(100);
64 v_statement varchar2(8000);
65
66 begin
67
68 select owner
69 into l_ora_schemaname
70 from all_synonyms
71 where table_owner=
72 (select owner from sys.all_objects where object_name='JTF_TAE_RPT_STAGING_OUT' and object_type='TABLE')
73 and synonym_name='JTF_TAE_RPT_STAGING_OUT';
74
75 -- delete previous data for the older dead sessions
76 cleanup;
77
78 /*delete from jtf_tae_rpt_staging_out
79 where session_id = p_session_id */
80
81 v_statement:='delete from '||l_ora_schemaname||'.jtf_tae_rpt_staging_out where session_id = p_session_id';
82 EXECUTE IMMEDIATE v_statement;
83
84 l_dyn_str :=
85 ' insert into '||l_ora_schemaname||'.jtf_tae_rpt_staging_out ' ||
86 ' (TRANS_OBJECT_ID, ' ||
87 ' TRANS_DETAIL_OBJECT_ID, ' ||
88 ' SOURCE_ID,' ||
89 ' TRANS_OBJECT_TYPE_ID, ' ||
90 ' TERR_ID,' ||
91 ' session_id, ' ||
92 ' at_char01, ' ||
93 ' at_char02, ' ||
94 ' low_value_char ' ||
95 ' ) ' ||
96 'select ' ||
97 ' ROW_NUMBER() OVER (ORDER BY ilv.low_value_char, ilv.org_name, ilv.org_address) ' ||
98 ' AS PSEUDO_ROWNUM, ' ||
99 ' -999, ' ||
100 ' -999, ' ||
101 ' -999, ' ||
102 ' :p_terr_id1, ' ||
103 ' :p_session_id, ' ||
104 ' ilv.org_name, ' ||
105 ' ilv.org_address, ' ||
106 ' ilv.low_value_char ' ||
107 ' FROM ( ' ||
108 ' SELECT ' ||
109 ' Q1012.low_value_char, Q1012.high_value_char ' ||
110 ' , hzp.party_name org_name ' ||
111 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
112 ' hzl.city || '', '' || hzl.state ' ||
113 ' || '', '' || hzl.postal_code org_address ' ||
114 ' FROM ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' ||' , '||
115 l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1012 ' || ' , '||
116 l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
117 l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
118 l_ora_schemaname ||'.hz_parties hzp ' ||' , '||
119 l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
120 l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
121 ' WHERE ' ||
122 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
123 ' Q1007R1.comparison_operator = ''='' ) ' ||
124 ' OR ' ||
125 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
126 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
127 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
128 ' ) ' ||
129 ' AND Q1007R1.qual_usg_id = -1007 ' ||
130 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
131 ' AND UPPER(hzp.party_name) = Q1012.low_value_char ' ||
132 ' AND Q1012.COMPARISON_OPERATOR = ''='' ' ||
133 ' AND Q1012.qual_usg_id = -1012 ' ||
134 ' AND Q1012.terr_id = jtr.terr_id ' ||
135 ' AND hzl.location_id = hzps.location_id ' ||
136 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
137 ' AND hzps.party_id = hzp.party_id ' ||
138 ' AND hzp.status = ''A'' ' ||
139 ' AND hzp.party_id = aaa.customer_id ' ||
140 ' AND aaa.salesforce_id = jtr.resource_id ' ||
141 ' AND jtr.terr_id = :p_terr_id2 ' ||
142
143 ' UNION ' ||
144
145 ' SELECT ' ||
146 ' Q1012.low_value_char, NULL ' ||
147 ' , hzp.party_name org_name ' ||
148 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
149 ' hzl.city || '', '' || hzl.state ' ||
150 ' || '', '' || hzl.postal_code org_address ' ||
151 ' FROM ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' || ' , '||
152 l_ora_schemaname ||'.jtf_terr_cnr_qual_like_mv Q1012 ' || ' , ' ||
153 l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
154 l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
155 l_ora_schemaname ||'.hz_parties hzp ' || ' , '||
156 l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
157 l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
158 ' WHERE ' ||
159 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
160 ' Q1007R1.comparison_operator = ''='' ) ' ||
161 ' OR ' ||
162 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
163 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
164 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
165 ' ) ' ||
166 ' AND Q1007R1.qual_usg_id = -1007 ' ||
167 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
168 ' AND UPPER(hzp.party_name) LIKE Q1012.low_value_char ' ||
169 ' AND UPPER(SUBSTR(hzp.party_name, 1, 1)) = Q1012.first_char ' ||
170 ' AND Q1012.qual_usg_id = -1012 ' ||
171 ' AND Q1012.terr_id = jtr.terr_id ' ||
172 ' AND hzl.location_id = hzps.location_id ' ||
173 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
174 ' AND hzps.party_id = hzp.party_id ' ||
175 ' AND hzp.status = ''A'' ' ||
176 ' AND hzp.party_id = aaa.customer_id ' ||
177 ' AND aaa.salesforce_id = jtr.resource_id ' ||
178 ' AND jtr.terr_id = :p_terr_id3 ' ||
179
180 ' UNION ' ||
181
182 ' SELECT ' ||
183 ' Q1012.low_value_char, NULL ' ||
184 ' , hzp.party_name org_name ' ||
185 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
186 ' hzl.city || '', '' || hzl.state ' ||
187 ' || '', '' || hzl.postal_code org_address ' ||
188 ' FROM ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' || ' , '||
189 l_ora_schemaname ||'.jtf_terr_cnr_qual_like_mv Q1012 ' || ' , '||
190 l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
191 l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
192 l_ora_schemaname ||'.hz_parties hzp ' || ' , '||
193 l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
194 l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
195 ' WHERE ' ||
196 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
197 ' Q1007R1.comparison_operator = ''='' ) ' ||
198 ' OR ' ||
199 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
200 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
201 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
202 ' ) ' ||
203 ' AND Q1007R1.qual_usg_id = -1007 ' ||
204 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
205 ' AND UPPER(hzp.party_name) LIKE Q1012.low_value_char ' ||
206 ' AND ''%'' = Q1012.first_char ' ||
207 ' AND Q1012.qual_usg_id = -1012 ' ||
208 ' AND Q1012.terr_id = jtr.terr_id ' ||
209 ' AND hzl.location_id = hzps.location_id ' ||
210 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
211 ' AND hzps.party_id = hzp.party_id ' ||
212 ' AND hzp.status = ''A'' ' ||
213 ' AND hzp.party_id = aaa.customer_id ' ||
214 ' AND aaa.salesforce_id = jtr.resource_id ' ||
215 ' AND jtr.terr_id = :p_terr_id4 ' ||
216
217 ' UNION ' ||
218
219 ' SELECT ' ||
220 ' Q1012.low_value_char, Q1012.high_value_char ' ||
221 ' , hzp.party_name org_name ' ||
222 ' , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
223 ' hzl.city || '', '' || hzl.state ' ||
224 ' || '', '' || hzl.postal_code org_address ' ||
225 ' FROM ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' || ' , '||
226 l_ora_schemaname ||'.jtf_terr_cnr_qual_btwn_mv Q1012 ' || ' , '||
227 l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
228 l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
229 l_ora_schemaname ||'.hz_parties hzp ' || ' , '||
230 l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
231 l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
232 ' WHERE ' ||
233 ' ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
234 ' Q1007R1.comparison_operator = ''='' ) ' ||
235 ' OR ' ||
236 ' ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
237 ' hzl.postal_code >= Q1007R1.low_value_char AND ' ||
238 ' Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
239 ' ) ' ||
240 ' AND Q1007R1.qual_usg_id = -1007 ' ||
241 ' AND Q1007R1.terr_id = jtr.terr_id ' ||
242 ' AND UPPER(hzp.party_name) BETWEEN Q1012.low_value_char AND Q1012.high_value_char ' ||
243 ' AND Q1012.qual_usg_id = -1012 ' ||
244 ' AND Q1012.terr_id = jtr.terr_id ' ||
245 ' AND hzl.location_id = hzps.location_id ' ||
246 ' AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
247 ' AND hzps.party_id = hzp.party_id ' ||
248 ' AND hzp.status = ''A'' ' ||
249 ' AND hzp.party_id = aaa.customer_id ' ||
250 ' AND aaa.salesforce_id = jtr.resource_id ' ||
251 ' AND jtr.terr_id = :p_terr_id5 ' ||
252 ' ) ilv ';
253
254
255
256
257 EXECUTE IMMEDIATE l_dyn_str USING
258 p_terr_id,
259 p_session_id,
260 p_terr_id,
261 p_terr_id,
262 p_terr_id,
263 p_terr_id;
264
265 commit;
266
267 END get_keyword_parties;
268
269 PROCEDURE get_results(p_session_id number,
270 p_resource_id number,
271 p_group_id number,
272 p_active_date varchar2
273 )
274 as
275 lp_active_date DATE := SYSDATE;
276 lp_sysdate DATE := SYSDATE;
277 v_statement varchar2(8000);
278 l_ora_schemaname varchar2(100);
279 l_dyn_str VARCHAR2(8000);
280
281 BEGIN
282
283 select owner
284 into l_ora_schemaname
285 from all_synonyms
286 where table_owner=
287 (select owner from sys.all_objects where object_name='JTF_TAE_RPT_STAGING_OUT' and object_type='TABLE')
288 and synonym_name='JTF_TAE_RPT_STAGING_OUT';
289
290 /*delete from APPS.jtf_tae_rpt_staging_out
291 where session_id = p_session_id; */
292
293 v_statement:='delete from '||l_ora_schemaname||'.jtf_tae_rpt_staging_out where session_id = p_session_id';
294 EXECUTE IMMEDIATE v_statement;
295
296 /*
297 insert into l_ora_schemaname ||'.jtf_tae_rpt_staging_out
298 (TRANS_OBJECT_ID,
299 TRANS_DETAIL_OBJECT_ID,
300 SOURCE_ID,
301 TRANS_OBJECT_TYPE_ID,
302 TERR_ID,
303 terr_name,
304 terr_rank,
305 session_id)
306 values(-999,
307 -999,
308 -999,
309 -999,
310 -999,
311 p_resource_id,
312 p_group_id,
313 p_session_id);
314 */
315
316 lp_active_date := to_date(p_active_date, 'YYYY/MM/DD');
317
318 l_dyn_str:='insert into '||l_ora_schemaname||'.jtf_tae_rpt_staging_out
319 (TRANS_OBJECT_ID,
320 TRANS_DETAIL_OBJECT_ID,
321 SOURCE_ID,
322 TRANS_OBJECT_TYPE_ID,
323 TERR_ID,
324 terr_name,
325 terr_rank,
326 session_id)
327 select distinct -999,
328 -999,
329 -999,
330 -999,
331 jta.terr_id,
332 jta.name,
333 jta.rank,
334 p_session_id
335 from jtf_terr_all jta,
336 jtf_terr_rsc_all jtra
337 where jta.terr_id = jtra.terr_id
338 and (jtra.resource_id = p_resource_id or p_resource_id = -999)
339 and (jtra.group_id = p_group_id or p_group_id =-999)
340 AND NOT EXISTS (
341 SELECT jt.terr_id
342 FROM jtf_terr_all jt
343 WHERE ( ( NVL(jt.end_date_active, ' || lp_sysdate ||') <= NVL(' ||lp_active_date ||', '|| lp_sysdate ||'
344 ) ) OR ( NVL(jt.start_date_active, ' ||lp_sysdate ||') > NVL(' ||lp_active_date ||', '|| lp_sysdate ||' ) ) )
345 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
346 START WITH jt.terr_id = jta.terr_id )';
347
348 EXECUTE IMMEDIATE l_dyn_str;
349
350 commit;
351 END get_results;
352
353 end JTF_TERR_RPT;