[Home] [Help]
PACKAGE BODY: APPS.OZF_PARTY_MKT_SEG_LOADER_PVT
Source
1 PACKAGE BODY OZF_Party_Mkt_Seg_Loader_PVT AS
2 /* $Header: ozfvldrb.pls 120.15 2012/01/02 09:39:20 amitamku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OZF_Party_Mkt_Seg_Loader_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='ozfvldrb.pls';
6 /* variable to on-off the debug messages of the programe */
7 G_DEBUG_LEVEL BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 -- yzhao: type definition for load_party_market... used internally
9 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 TYPE VARCHAR2_TBL_TYPE IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
11 -- yzhao: 05/07/2003 SQL bind variable compliance
12 TYPE BIND_VAR_TYPE IS RECORD (
13 BIND_INDEX NUMBER,
14 BIND_TYPE VARCHAR2(1),
15 BIND_CHAR VARCHAR2(2000),
16 BIND_NUMBER NUMBER
17 );
18 TYPE BIND_TBL_TYPE IS TABLE OF BIND_VAR_TYPE INDEX BY BINARY_INTEGER;
19 G_BIND_TYPE_NUMBER CONSTANT VARCHAR2(1) := 'N';
20 G_BIND_TYPE_CHAR CONSTANT VARCHAR2(1) := 'C';
21 G_BIND_VAR_STRING CONSTANT VARCHAR2(9) := ':OZF_BIND';
22 PROCEDURE write_conc_log
23 (
24 p_text IN CLOB
25 ) IS
26 l_text CLOB := p_text;
27 BEGIN
28
29 IF G_DEBUG_LEVEL THEN
30 IF length(l_text) > 32767 THEN
31 l_text := substr(l_text,1,32767);
32 END IF;
33 Ozf_Utility_pvt.write_conc_log (l_text);
34 -- Ozf_Utility_pvt.write_conc_log (p_text);
35 END IF;
36 END write_conc_log;
37 /*****************************************************************************
38 * NAME
39 * compose_qualifier_values
40 *
41 * PURPOSE
42 * This procedure is a private procedure used by get_territory_qualifiers
43 * to compose qualifier expression
44 *
45 * NOTES
46 *
47 * HISTORY
48 * 10/14/2001 yzhao created
49 * 05/07/2003 yzhao SQL bind variable project
50 *****************************************************************************/
51 PROCEDURE compose_qualifier_values
52 (
53 p_value_rec IN JTF_TERRITORY_GET_PUB.Terr_Values_Rec_Type,
54 p_bindvar_index IN NUMBER,
55 p_bind_vars IN BIND_TBL_TYPE,
56 x_cond_str OUT NOCOPY VARCHAR2,
57 x_bind_vars OUT NOCOPY BIND_TBL_TYPE
58 ) IS
59 l_temp_index NUMBER;
60 l_index NUMBER;
61 l_value_str CLOB;
62 l_bind_vars BIND_TBL_TYPE;
63 BEGIN
64 l_bind_vars := p_bind_vars;
65 l_index := p_bindvar_index + p_bind_vars.COUNT;
66 write_conc_log('D: compose_qualifier_values: bindvar_index=' || l_index);
67 IF p_value_rec.COMPARISON_OPERATOR = '=' OR
68 p_value_rec.COMPARISON_OPERATOR = '<>' OR
69 p_value_rec.COMPARISON_OPERATOR = '<' OR
70 p_value_rec.COMPARISON_OPERATOR = '>' OR
71 p_value_rec.COMPARISON_OPERATOR = 'LIKE' OR
72 p_value_rec.COMPARISON_OPERATOR = 'NOT LIKE' THEN
73 IF p_value_rec.ID_USED_FLAG = 'Y' THEN
74 -- l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ' || p_value_rec.LOW_VALUE_CHAR_ID;
75 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index;
76 l_bind_vars(l_index).bind_index := l_index;
77 l_bind_vars(l_index).bind_type := G_BIND_TYPE_NUMBER;
78 l_bind_vars(l_index).bind_number := p_value_rec.LOW_VALUE_CHAR_ID;
79 l_index := l_index + 1;
80 ELSE
81 -- l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ''' || p_value_rec.LOW_VALUE_CHAR || '''';
82 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index;
83 l_bind_vars(l_index).bind_index := l_index;
84 l_bind_vars(l_index).bind_type := G_BIND_TYPE_CHAR;
85 l_bind_vars(l_index).bind_char := p_value_rec.LOW_VALUE_CHAR;
86 l_index := l_index + 1;
87 END IF;
88 ELSIF p_value_rec.COMPARISON_OPERATOR = 'BETWEEN' OR
89 p_value_rec.COMPARISON_OPERATOR = 'NOT BETWEEN' THEN
90 IF p_value_rec.ID_USED_FLAG = 'N' THEN
91 -- l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ''' || p_value_rec.LOW_VALUE_CHAR || ''' AND ''' || p_value_rec.HIGH_VALUE_CHAR || '''';
92 -- Bug 3453913
93 l_temp_index := l_index + 1;
94 l_value_str := p_value_rec.COMPARISON_OPERATOR || G_BIND_VAR_STRING || l_index
95 || ' AND ' || G_BIND_VAR_STRING || l_temp_index;
96 l_bind_vars(l_index).bind_index := l_index;
97 l_bind_vars(l_index).bind_type := G_BIND_TYPE_CHAR;
98 l_bind_vars(l_index).bind_char := p_value_rec.LOW_VALUE_CHAR;
99 l_index := l_index + 1;
100 l_bind_vars(l_index).bind_index := l_index ;
101 l_bind_vars(l_index).bind_type := G_BIND_TYPE_CHAR;
102 l_bind_vars(l_index).bind_char := p_value_rec.HIGH_VALUE_CHAR;
103 l_index := l_index + 1;
104 /* yzhao: between numbers is not supported? or use LOW_VALUE_NUMBER, HIGH_VALUE_NUMBER?
105 ELSE
106 l_value_str := p_value_rec.COMPARISON_OPERATOR || ' ' || p_value_rec.LOW_VALUE_CHAR_ID || ' AND ' || p_value_rec.HIGH_VALUE_CHAR_ID;
107 */
108 END IF;
109 END IF;
110 x_cond_str := l_value_str;
111 x_bind_vars := l_bind_vars;
112 write_conc_log('TRAY>>>>>>>>>>>>>>>>>>>>>>l_value_str: '||l_value_str);
113 END compose_qualifier_values;
114 /*****************************************************************************
115 * NAME
116 * get_territory_qualifiers
117 *
118 * PURPOSE
119 * This procedure is a private procedure called by generate_party_for_territory
120 * to get qualifier information of a territory
121 *
122 * NOTES
123 * 1. currently JTF territory has no public api for getting territory detail information
124 * JTF_TERRITORY_GET_PUB.Get_Territory_Details() is not publicly supported.
125 * Change it when api is public
126 * 2. I'm concerned about the sql buffer size. As territory qualifier combination grows,
127 * it may exceed the limit?
128 *
129 * HISTORY
130 * 10/14/2001 yzhao created
131 * 04/09/2003 niprakas Fixed the bug#2833114.
132 *****************************************************************************/
133 PROCEDURE get_territory_qualifiers
134 (
135 p_terr_id IN NUMBER,
136 p_bindvar_index IN NUMBER,
137 x_terr_pid OUT NOCOPY NUMBER,
138 x_terr_child_table OUT NOCOPY NUMBER_TBL_TYPE,
139 x_hzsql_table OUT NOCOPY VARCHAR2_TBL_TYPE,
140 x_bind_vars OUT NOCOPY BIND_TBL_TYPE
141 ) IS
142 l_api_version CONSTANT NUMBER := 1.0;
143 l_return_status VARCHAR2(1);
144 l_msg_count NUMBER;
145 l_msg_data VARCHAR2(2000);
146 l_tmp_str CLOB;
147 J NUMBER;
148 l_hzsql_table VARCHAR2_TBL_TYPE;
149 l_terr_qual_id NUMBER;
150 l_terr_rec JTF_TERRITORY_GET_PUB.Terr_Rec_Type;
151 l_terr_type_rec JTF_TERRITORY_GET_PUB.Terr_Type_Rec_Type;
152 l_terr_child_table JTF_TERRITORY_GET_PUB.Terr_Tbl_Type;
153 l_terr_usgs_table JTF_TERRITORY_GET_PUB.Terr_Usgs_Tbl_Type;
154 l_terr_qtype_usgs_table JTF_TERRITORY_GET_PUB.Terr_QType_Usgs_Tbl_Type;
155 l_terr_qual_table JTF_TERRITORY_GET_PUB.Terr_Qual_Tbl_Type;
156 l_terr_values_table JTF_TERRITORY_GET_PUB.Terr_Values_Tbl_Type;
157 l_terr_rsc_table JTF_TERRITORY_GET_PUB.Terr_Rsc_Tbl_Type;
158 -- This one is required ....
159 l_hzparty_sql CLOB := null;
160 l_hzpartyacc_sql CLOB := null;
161 -- This is required ....
162 l_hzpartyrel_sql CLOB := null;
163 l_hzpartysiteuse_sql CLOB := null;
164 -- This is required ..
165 l_hzcustprof_sql CLOB := null;
166 -- This is new field ...
167 l_hzlocations_sql CLOB := null;
168 /*
169 -- l_hzcustname_sql handles customer name
170 -- l_hzpartysite_sql VARCHAR2(2000) := null;
171 -- l_hzcustname_sql VARCHAR2(2000) := null;
172 -- l_hzcustcat_sql handles the customer category
173 -- l_hzcustcat_sql VARCHAR2(2000) := null;
174 -- l_saleschannel_sql handles the sales channel
175 -- l_hzsaleschannel_sql VARCHAR2(2000) := null;
176 */
177 l_out_child_table NUMBER_TBL_TYPE;
178 l_out_hzsql_table VARCHAR2_TBL_TYPE;
179 l_bind_vars BIND_TBL_TYPE;
180 l_child_bind_vars BIND_TBL_TYPE;
181 l_index NUMBER;
182 BEGIN
183 --
184 JTF_TERRITORY_GET_PUB.Get_Territory_Details(
185 p_Api_Version => l_api_version,
186 p_Init_Msg_List => FND_API.G_FALSE,
187 x_return_status => l_return_status,
188 x_msg_count => l_msg_count,
189 x_msg_data => l_msg_data,
190 p_terr_id => p_terr_id,
191 x_terr_rec => l_terr_rec,
192 x_terr_type_rec => l_terr_type_rec,
193 x_terr_sub_terr_tbl => l_terr_child_table,
194 x_terr_usgs_tbl => l_terr_usgs_table,
195 x_terr_qtype_usgs_tbl => l_terr_qtype_usgs_table,
196 x_terr_qual_tbl => l_terr_qual_table,
197 x_terr_values_tbl => l_terr_values_table,
198 x_terr_rsc_tbl => l_terr_rsc_table);
199 --
200 -- dbms_output.put_line('get_territory_details(terr_id=' || p_terr_id || ') returns ' || l_return_status);
201 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
202 RAISE FND_API.g_exc_error;
203 END IF;
204 J := l_terr_values_table.FIRST;
205 l_index := p_bindvar_index;
206 --
207 write_conc_log('D: territory=' || p_terr_id ||
208 ' qualifier count=' || l_terr_qual_table.COUNT ||
209 ' first=' || NVL(l_terr_qual_table.FIRST, -100) ||
210 ' LAST=' || NVL(l_terr_qual_table.LAST, -200));
211 --
212 FOR I IN NVL(l_terr_qual_table.FIRST, 1) .. NVL(l_terr_qual_table.LAST, 0)
213 LOOP
214 --
215 /* only processing OFFER's qualifiers at this time
216 one qualifier may have multiple values. The relationship is 'OR' between these values
217 it is assumed that qualifier table and qualifier value table are of the same order
218 for example, qualifier table qualifier value table
219 q1 value1 for q1
220 q2 value1 for q2
221 value2 for q2
222 q3 value1 for q3
223 */
224 l_terr_qual_id := l_terr_qual_table(I).TERR_QUAL_ID;
225 IF l_terr_qual_table(I).QUALIFIER_TYPE_NAME = 'OFFER'
226 AND J <= l_terr_values_table.LAST
227 AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id
228 THEN
229 --
230 write_conc_log('D: before compose_qualifier_values(' || I || ') index=' || l_index);
231 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
232 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
233 , p_bindvar_index => l_index
234 , p_bind_vars => l_bind_vars
235 , x_cond_str => l_tmp_str
236 , x_bind_vars => l_child_bind_vars
237 );
238 l_bind_vars := l_child_bind_vars;
239 IF l_terr_qual_table(I).QUAL_USG_ID = -1066
240 THEN
241 --
242 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.CITY ' || l_tmp_str;
243 J := J + 1;
244 write_conc_log('D: In the City ' || l_hzlocations_sql);
245 write_conc_log('D: before compose_qualifier_values(' || I || ') index=' || l_index);
246 WHILE ( J <= l_terr_values_table.LAST AND
247 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
248 LOOP
249 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
250 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
251 , p_bindvar_index => l_index
252 , p_bind_vars => l_bind_vars
253 , x_cond_str => l_tmp_str
254 , x_bind_vars => l_child_bind_vars
255 );
256 l_bind_vars := l_child_bind_vars;
257 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.CITY ' || l_tmp_str;
258 J := J + 1;
259 --
260 END LOOP;
261 write_conc_log('D: After the City ' || l_hzlocations_sql);
262 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
263 --
264 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1065
265 THEN
266 --
267 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.COUNTRY ' || l_tmp_str;
268 J := J + 1;
269 write_conc_log('D: In the country ' || l_hzlocations_sql);
270 WHILE (J <= l_terr_values_table.LAST AND
271 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
272 LOOP
273 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
274 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
275 , p_bindvar_index => l_index
276 , p_bind_vars => l_bind_vars
277 , x_cond_str => l_tmp_str
278 , x_bind_vars => l_child_bind_vars
279 );
280 l_bind_vars := l_child_bind_vars;
281 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.COUNTRY ' || l_tmp_str;
282 J := J + 1;
283 END LOOP;
284 write_conc_log('D: After the country ' || l_hzlocations_sql);
285 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
286 --
287 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1069
288 THEN
289 --
290 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.COUNTY ' || l_tmp_str;
291 J := J + 1;
292 WHILE (J <= l_terr_values_table.LAST AND
293 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
294 LOOP
295 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
296 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
297 , p_bindvar_index => l_index
298 , p_bind_vars => l_bind_vars
299 , x_cond_str => l_tmp_str
300 , x_bind_vars => l_child_bind_vars
301 );
302 l_bind_vars := l_child_bind_vars;
303 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.COUNTY ' || l_tmp_str;
304 J := J + 1;
305 END LOOP;
306 --
307 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
308 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1081
309 THEN
310 --
311 l_hzparty_sql := l_hzparty_sql || '(hzp.CATEGORY_CODE ' || l_tmp_str;
312 J := J + 1;
313 WHILE (J <= l_terr_values_table.LAST AND
314 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
315 LOOP
316 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
317 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
318 , p_bindvar_index => l_index
319 , p_bind_vars => l_bind_vars
320 , x_cond_str => l_tmp_str
321 , x_bind_vars => l_child_bind_vars
322 );
323 l_bind_vars := l_child_bind_vars;
324 l_hzparty_sql := l_hzparty_sql || ' OR hzp.CATEGORY_CODE ' || l_tmp_str;
325 J := J + 1;
326 --
327 END LOOP;
328 l_hzparty_sql := l_hzparty_sql || ') AND ';
329 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1064
330 THEN
331 --
332 write_conc_log('TRAY>>>>>>>>>>>>>>>>>>>>>>> For Qualifier Customer Name');
333 l_hzparty_sql := l_hzparty_sql || '(hzp.PARTY_ID ' || l_tmp_str;
334 J := J + 1;
335 WHILE (J <= l_terr_values_table.LAST AND
336 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
337 LOOP
338 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
339 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
340 , p_bindvar_index => l_index
341 , p_bind_vars => l_bind_vars
342 , x_cond_str => l_tmp_str
343 , x_bind_vars => l_child_bind_vars
344 );
345 l_bind_vars := l_child_bind_vars;
346 l_hzparty_sql := l_hzparty_sql || ' OR hzp.PARTY_ID ' || l_tmp_str;
347 J := J + 1;
348 --
349
350 write_conc_log('TRAY>>>>>>>>>>>>>>>>>>>>>>> l_hzparty_sql'||l_hzparty_sql);
351 write_conc_log('TRAY>>>>>>>>>>>>>>>>>>>>>>> l_tmp_str'||l_tmp_str);
352
353 END LOOP;
354 l_hzparty_sql := l_hzparty_sql || ') AND ';
355 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1067
356 THEN
357 --
358 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.POSTAL_CODE ' || l_tmp_str;
359 J := J + 1;
360 WHILE (J <= l_terr_values_table.LAST AND
361 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
362 LOOP
363 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
364 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
365 , p_bindvar_index => l_index
366 , p_bind_vars => l_bind_vars
367 , x_cond_str => l_tmp_str
368 , x_bind_vars => l_child_bind_vars
369 );
370 l_bind_vars := l_child_bind_vars;
371 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.POSTAL_CODE ' || l_tmp_str;
372 J := J + 1;
373 END LOOP;
374 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
375 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1071
376 THEN
377 --
378 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.PROVINCE ' || l_tmp_str;
379 J := J + 1;
380 WHILE (J <= l_terr_values_table.LAST
381 AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
382 LOOP
383 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
384 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
385 , p_bindvar_index => l_index
386 , p_bind_vars => l_bind_vars
387 , x_cond_str => l_tmp_str
388 , x_bind_vars => l_child_bind_vars
389 );
390 l_bind_vars := l_child_bind_vars;
391 l_hzlocations_sql := l_hzlocations_sql ||
392 ' OR hzloc.PROVINCE ' ||
393 l_terr_values_table(J).COMPARISON_OPERATOR || '''' ||
394 l_tmp_str;
395 J := J + 1;
396 --
397 END LOOP;
398 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
399 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1068
400 THEN
401 l_hzlocations_sql := l_hzlocations_sql || '(hzloc.STATE ' || l_tmp_str;
402 J := J + 1;
403 WHILE (J <= l_terr_values_table.LAST
404 AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
405 LOOP
406 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
407 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
408 , p_bindvar_index => l_index
409 , p_bind_vars => l_bind_vars
410 , x_cond_str => l_tmp_str
411 , x_bind_vars => l_child_bind_vars
412 );
413 l_bind_vars := l_child_bind_vars;
414 l_hzlocations_sql := l_hzlocations_sql || ' OR hzloc.STATE ' || l_tmp_str;
415 J := J + 1;
416 END LOOP;
417 l_hzlocations_sql := l_hzlocations_sql || ') AND ';
418 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1076
419 THEN
420 l_hzpartyacc_sql := l_hzpartyacc_sql || '(hzca.CUSTOMER_CLASS_CODE ' || l_tmp_str;
421 J := J + 1;
422 WHILE (J <= l_terr_values_table.LAST
423 AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
424 LOOP
425 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
426 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
427 , p_bindvar_index => l_index
428 , p_bind_vars => l_bind_vars
429 , x_cond_str => l_tmp_str
430 , x_bind_vars => l_child_bind_vars
431 );
432 l_bind_vars := l_child_bind_vars;
433 l_hzpartyacc_sql := l_hzpartyacc_sql || ' OR hzca.CUSTOMER_CLASS_CODE ' || l_tmp_str;
434 J := J + 1;
435 END LOOP;
436 l_hzpartyacc_sql := l_hzpartyacc_sql || ') AND ';
437 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1079
438 THEN
439 l_hzpartyacc_sql := l_hzpartyacc_sql || '(hzca.SALES_CHANNEL_CODE ' || l_tmp_str;
440 J := J + 1;
441 WHILE (J <= l_terr_values_table.LAST
442 AND l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
443 LOOP
444 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
445 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
446 , p_bindvar_index => l_index
447 , p_bind_vars => l_bind_vars
448 , x_cond_str => l_tmp_str
449 , x_bind_vars => l_child_bind_vars
450 );
451 l_bind_vars := l_child_bind_vars;
452 l_hzpartyacc_sql := l_hzpartyacc_sql || ' OR hzca.SALES_CHANNEL_CODE ' || l_tmp_str;
453 J := J + 1;
454 --
455 END LOOP;
456 l_hzpartyacc_sql := l_hzpartyacc_sql || ') AND ';
457 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1075
458 THEN
459 l_hzpartyrel_sql := l_hzpartyrel_sql || '(hzpr.relationship_code ' || l_tmp_str;
460 J := J + 1;
461 WHILE (J <= l_terr_values_table.LAST AND
462 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
463 LOOP
464 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
465 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
466 , p_bindvar_index => l_index
467 , p_bind_vars => l_bind_vars
468 , x_cond_str => l_tmp_str
469 , x_bind_vars => l_child_bind_vars
470 );
471 l_bind_vars := l_child_bind_vars;
472 l_hzpartyrel_sql := l_hzpartyrel_sql || ' OR hzpr.relationship_code ' || l_tmp_str;
473 J := J + 1;
474 END LOOP;
475 l_hzpartyrel_sql := l_hzpartyrel_sql || ') AND ';
476 -- 10/25 newly added
477 --ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Account Hierarchy'
478 --R12 : Renamed
479 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1063
480 THEN
481 l_hzpartyrel_sql := l_hzpartyrel_sql || '(hzpr.OBJECT_ID ' || l_tmp_str;
482 J := J + 1;
483 WHILE (J <= l_terr_values_table.LAST AND
484 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id) LOOP
485 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
486 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
487 , p_bindvar_index => l_index
488 , p_bind_vars => l_bind_vars
489 , x_cond_str => l_tmp_str
490 , x_bind_vars => l_child_bind_vars
491 );
492 l_bind_vars := l_child_bind_vars;
493 l_hzpartyrel_sql := l_hzpartyrel_sql || ' OR hzpr.OBJECT_ID ' || l_tmp_str;
494 J := J + 1;
495 END LOOP;
496 l_hzpartyrel_sql := l_hzpartyrel_sql || ') AND ';
497 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1073
498 THEN
499 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || '(hzcsua.SITE_USE_CODE ' || l_tmp_str;
500 J := J + 1;
501 WHILE (J <= l_terr_values_table.LAST AND
502 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
503 LOOP
504 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
505 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
506 , p_bindvar_index => l_index
507 , p_bind_vars => l_bind_vars
508 , x_cond_str => l_tmp_str
509 , x_bind_vars => l_child_bind_vars
510 );
511 l_bind_vars := l_child_bind_vars;
512 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || ' OR hzcsua.SITE_USE_CODE ' || l_tmp_str;
513 J := J + 1;
514 END LOOP;
515 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || ') AND ';
516 --ELSIF l_terr_qual_table(I).QUALIFIER_NAME = 'Account Code'
517 --R12 : Renamed
518 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1077
519 THEN
520 l_hzpartyacc_sql := l_hzpartyacc_sql || '(hzps.PARTY_SITE_ID ' || l_tmp_str;
521 J := J + 1;
522 WHILE (J <= l_terr_values_table.LAST AND
523 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
524 LOOP
525 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
526 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
527 , p_bindvar_index => l_index
528 , p_bind_vars => l_bind_vars
529 , x_cond_str => l_tmp_str
530 , x_bind_vars => l_child_bind_vars
531 );
532 l_bind_vars := l_child_bind_vars;
533 l_hzpartyacc_sql := l_hzpartyacc_sql || ' OR hzps.PARTY_SITE_ID ' || l_tmp_str;
534 J := J + 1;
535 END LOOP;
536 l_hzpartyacc_sql := l_hzpartyacc_sql || ') AND ';
537 ELSIF l_terr_qual_table(I).QUAL_USG_ID = -1074
538 THEN
539 l_hzcustprof_sql := l_hzcustprof_sql || '(hzcp.PROFILE_CLASS_ID ' || l_tmp_str;
540 J := J + 1;
541 WHILE (J <= l_terr_values_table.LAST AND
542 l_terr_values_table(J).TERR_QUAL_ID = l_terr_qual_id)
543 LOOP
544 -- compose_qualifier_values(l_terr_values_table(J), l_tmp_str);
545 compose_qualifier_values( p_value_rec => l_terr_values_table(J)
546 , p_bindvar_index => l_index
547 , p_bind_vars => l_bind_vars
548 , x_cond_str => l_tmp_str
549 , x_bind_vars => l_child_bind_vars
550 );
551 l_bind_vars := l_child_bind_vars;
552 l_hzcustprof_sql := l_hzcustprof_sql || ' OR hzcp.PROFILE_CLASS_ID ' || l_tmp_str;
553 J := J + 1;
554 END LOOP;
555 l_hzcustprof_sql := l_hzcustprof_sql || ') AND ';
556 END IF;
557 END IF; -- IF qualifier_type_name='OFFER'
558 /* to do claim qualifiers: 'Claim Type' 'Claim Class' 'Reasons' 'Vendor'
559 add ' AND VENDOR=' to PO_VENDORS, OZF_TRADE_PROFILE sql
560 */
561 END LOOP; -- FOR I IN l_terr_qual_table.FIRST .. (l_terr_qual_table.LAST-1) LOOP
562 /* It's important to maintain the same order as get_territory_qualifiers() returns */
563 J := 1;
564 l_out_hzsql_table(J) := l_hzparty_sql;
565 l_out_hzsql_table(J+1) := l_hzpartyrel_sql;
566 l_out_hzsql_table(J+2) := l_hzcustprof_sql;
567 l_out_hzsql_table(J+3) := l_hzlocations_sql;
568 l_out_hzsql_table(J+4) := l_hzpartyacc_sql;
569 l_out_hzsql_table(J+5) := l_hzpartysiteuse_sql;
570 -- l_out_hzsql_table(J+1) := l_hzpartyacc_sql;
571 -- l_out_hzsql_table(J+3) := l_hzpartysite_sql;
572 -- l_out_hzsql_table(J+4) := l_hzpartysiteuse_sql;
573 -- l_out_hzsql_table(J+6) := l_hzcustname_sql;
574 -- l_out_hzsql_table(J+7) := l_hzcustcat_sql;
575 -- l_out_hzsql_table(J+8) := l_hzsaleschannel_sql;
576 x_hzsql_table := l_out_hzsql_table;
577 -- FOR J IN NVL(l_terr_child_table.FIRST, 1) .. NVL((l_terr_child_table.LAST-1), 0)
578 -- R12: mkothari changed 'LAST-1' to 'LAST'
579 FOR J IN NVL(l_terr_child_table.FIRST, 1) .. NVL((l_terr_child_table.LAST), 0)
580 LOOP
581 --
582 l_out_child_table(J) := l_terr_child_table(J).terr_id;
583 --
584 END LOOP;
585 x_terr_child_table := l_out_child_table;
586 x_terr_pid := l_terr_rec.parent_territory_id;
587 x_bind_vars := l_bind_vars;
588 write_conc_log('get_territory_qualifiers(' || p_terr_id || '): ends binds=' || l_bind_vars.COUNT);
589 END get_territory_qualifiers;
590 /*****************************************************************************
591 * NAME
592 * generate_party_for_territory
593 *
594 * PURPOSE
595 * This procedure is a private procedure used by LOAD_PARTY_MARKET_QUALIFIER
596 * to generate party list for a territory and its children
597 * recusive call
598 *
599 * NOTES
600 *
601 * HISTORY
602 * 10/14/2001 yzhao created
603 * 04/09/2003 niprakas Fix for the bug#2833114. The dynamic SQL are
604 * changed. The insert statement for AMS_PARTY_MARKET_SEGMENTS
605 * is changed. It now inserts cust_account_id,cust_acct_site_id
606 * and cust_site_use_code.
607 ******************************************************************************/
608 PROCEDURE generate_party_for_territory
609 ( p_errbuf OUT NOCOPY VARCHAR2,
610 p_retcode OUT NOCOPY NUMBER,
611 p_terr_id IN NUMBER,
612 p_getparent_flag IN VARCHAR2 := 'N',
613 p_bind_vars IN BIND_TBL_TYPE,
614 p_hzparty_sql IN VARCHAR2 := null,
615 p_hzpartyacc_sql IN VARCHAR2 := null,
616 p_hzpartyrel_sql IN VARCHAR2 := null,
617 -- p_hzpartysite_sql IN VARCHAR2 := null,
618 p_hzpartysiteuse_sql IN VARCHAR2 := null,
619 p_hzcustprof_sql IN VARCHAR2 := null,
620 p_hzlocations_sql IN VARCHAR2 := null
621 -- p_hzcustname_sql IN VARCHAR2 := null,
622 -- p_hzcustcat_sql IN VARCHAR2 := null,
623 -- p_hzsaleschannel_sql IN VARCHAR2 := null
624 )
625 IS
626 l_full_name CONSTANT VARCHAR2(60) := 'GENERATE_PARTY_FOR_TERRITORY';
627 l_err_msg VARCHAR2(2000);
628 /* redefine these buffer sizes so they can fit all qualifier combinations */
629 l_final_sql CLOB;
630 l_party_select_sql CLOB := null;
631 l_party_where_sql CLOB := null;
632 l_party_join_sql CLOB := null;
633 l_hzparty_sql CLOB := p_hzparty_sql;
634 l_hzpartyacc_sql CLOB := p_hzpartyacc_sql;
635 l_hzpartyrel_sql CLOB := p_hzpartyrel_sql;
636 l_hzpartysiteuse_sql CLOB := p_hzpartysiteuse_sql;
637 l_hzcustprof_sql CLOB := p_hzcustprof_sql;
638 l_hzlocations_sql CLOB := p_hzlocations_sql;
639 l_hzsql_table VARCHAR2_TBL_TYPE;
640 l_terr_id NUMBER;
641 l_terr_pid NUMBER;
642 l_terr_child_table NUMBER_TBL_TYPE;
643 l_tmp_child_table NUMBER_TBL_TYPE;
644 l_party_mkt_seg_id NUMBER;
645 l_party_id NUMBER;
646 l_index NUMBER;
647 l_client_info NUMBER;
648 l_cust_account_id NUMBER;
649 l_cust_acct_site_id NUMBER;
650 l_cust_site_use_code VARCHAR2(30);
651 flag VARCHAR2(2) := 'F';
652 l_bindvar_index NUMBER;
653 l_bind_vars BIND_TBL_TYPE;
654 l_final_bind_vars BIND_TBL_TYPE;
655 l_denorm_csr INTEGER;
656
657 --TRAY
658 l_store_select_sql CLOB := null;
659 l_store_where_sql CLOB := null;
660 l_store_final_sql CLOB := null;
661 l_store_insert_sql CLOB := null;
662 lvar VARCHAR2(200);
663 n NUMBER:=0;
664 len NUMBER:=0;
665 isNum NUMBER;
666 l_store_index NUMBER;
667 l_store_csr INTEGER;
668 --TRAY
669
670
671
672 -- l_hzpartysite_sql VARCHAR2(10000) := p_hzpartysite_sql;
673 -- l_hzcustname_sql VARCHAR2(10000) := p_hzcustname_sql;
674 -- l_hzcustcat_sql VARCHAR2(10000) := p_hzcustcat_sql;
675 -- l_hzsaleschannel_sql VARCHAR2(10000) := p_hzsaleschannel_sql;
676 -- TYPE PartyCurTyp IS REF CURSOR; -- define weak REF CURSOR type
677 -- l_party_cv PartyCurTyp; -- declare cursor variable
678 -- CURSOR c_party_mkt_seg_seq IS -- generate an ID for INSERT
679 -- SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
680 -- FROM DUAL;
681
682
683 --R12: mkothari -- added cursor to get territory org
684 CURSOR client_info_csr IS select org_id from jtf_terr_all where terr_id = p_terr_id;
685 l_terr_limited_to_ou VARCHAR2(1) := NVL(FND_PROFILE.VALUE('OZF_TP_TERR_LIMITED_TO_OU'), 'N');
686
687 BEGIN
688 --
689 p_retcode := 0;
690 FND_MSG_PUB.initialize;
691 --
692 Ozf_Utility_pvt.write_conc_log(l_full_name || ': START for territory ' || p_terr_id);
693 --
694 l_terr_id := p_terr_id;
695
696 --R12: mkothari
697 --l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT
698 -- _INFO'), 1, 10));
699 OPEN client_info_csr;
700 FETCH client_info_csr into l_client_info;
701 CLOSE client_info_csr;
702 -- IF l_client_info IS NULL THEN l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT
703 -- _INFO'), 1, 10)); END IF;
704 l_final_bind_vars := p_bind_vars;
705 --
706 LOOP
707 --
708 l_bindvar_index := l_final_bind_vars.COUNT + 1;
709 get_territory_qualifiers
710 (
711 p_terr_id => l_terr_id,
712 p_bindvar_index => l_bindvar_index,
713 x_terr_pid => l_terr_pid,
714 x_terr_child_table => l_tmp_child_table,
715 x_hzsql_table => l_hzsql_table,
716 x_bind_vars => l_bind_vars
717 );
718 write_conc_log(l_full_name ||
719 ' after get_territory_qualifiers(terr_id=' || l_terr_id ||
720 ') bindvar_count=' || l_bind_vars.count);
721 /* it's important to be of exactly the same order as get_territory_qualifiers() returns */
722 l_index := 1;
723 l_hzparty_sql := l_hzparty_sql || l_hzsql_table(l_index);
724 l_hzpartyrel_sql := l_hzpartyrel_sql || l_hzsql_table(l_index+1);
725 l_hzcustprof_sql := l_hzcustprof_sql || l_hzsql_table(l_index+2);
726 l_hzlocations_sql := l_hzlocations_sql || l_hzsql_table(l_index+3);
727 l_hzpartyacc_sql := l_hzpartyacc_sql || l_hzsql_table(l_index+4);
728 l_hzpartysiteuse_sql := l_hzpartysiteuse_sql || l_hzsql_table(l_index+5);
729 -- l_hzpartysite_sql := l_hzpartysite_sql || l_hzsql_table(l_index+3);
730 -- l_hzcustname_sql := l_hzcustname_sql || l_hzsql_table(l_index+6);
731 -- l_hzcustcat_sql := l_hzcustcat_sql || l_hzsql_table(l_index+7) ;
732 -- l_hzsaleschannel_sql := l_hzsaleschannel_sql || l_hzsql_table(l_index+8);
733
734 write_conc_log(' l_hzparty_sql ' || l_hzparty_sql);
735 write_conc_log(' l_hzpartyrel_sql ' || l_hzpartyrel_sql);
736 write_conc_log(' l_hzcustprof_sql ' || l_hzcustprof_sql);
737 write_conc_log(' l_hzlocations_sql ' || l_hzlocations_sql);
738 write_conc_log(' l_hzpartyacc_sql ' ||l_hzpartyacc_sql);
739 write_conc_log(' l_hzpartysiteuse_sql ' || l_hzpartysiteuse_sql);
740 -- write_conc_log(' l_hzpartysite_sql ' || l_hzpartysite_sql);
741 -- write_conc_log(' l_hzcustname_sql ' || l_hzcustname_sql);
742 -- write_conc_log(' l_hzcustcat_sql ' ||l_hzcustcat_sql);
743 -- write_conc_log(' l_hzsaleschannel_sql ' || l_hzsaleschannel_sql);
744 -- yzhao: 05/08/2003 append this node's bind variable
745 l_index := l_final_bind_vars.COUNT + 1;
746 FOR i IN NVL(l_bind_vars.FIRST, 1) .. NVL(l_bind_vars.LAST, 0)
747 LOOP
748 --
749 l_final_bind_vars(l_index) := l_bind_vars(i);
750 l_index := l_index + 1;
751 --
752 END LOOP;
753 l_bindvar_index := l_index;
754 -- remember the current node's children for later recursion
755 IF (p_terr_id = l_terr_id)
756 THEN
757 --
758 l_terr_child_table := l_tmp_child_table;
759 --
760 END IF;
761 -- get the territory ancestors's qualifier information
762 -- if it's required and if it is not root territory
763 IF (p_getparent_flag = 'N' OR l_terr_pid = 1)
764 THEN
765 --
766 EXIT;
767 --
768 END IF;
769 l_terr_id := l_terr_pid;
770 --
771 END LOOP;
772 IF l_hzparty_sql IS NOT NULL
773 THEN
774 --
775 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
776 'hzca.cust_account_id, '||
777 'hzcsua.cust_acct_site_id, '||
778 'hzcsua.site_use_id, '||
779 'hzcsua.bill_to_site_use_id, '||
780 'hzcsua.site_use_code ' ;
781 l_party_select_sql := l_party_select_sql ||
782 'FROM hz_cust_site_uses_all hzcsua, '||
783 'hz_cust_acct_sites_all hzcasa, '||
784 'hz_cust_accounts hzca, ';
785 l_party_select_sql := l_party_select_sql ||
786 'hz_party_sites hzps, '||
787 'hz_locations hzloc, '||
788 'hz_parties hzp ' ;
789 l_party_where_sql := ' WHERE ' ;
790 --R12
791 IF l_terr_limited_to_ou = 'Y'
792 THEN
793 l_party_where_sql := l_party_where_sql ||
794 'hzcsua.org_id = ' || l_client_info || ' AND ';
795 l_party_where_sql := l_party_where_sql ||
796 'hzcsua.org_id = hzcasa.org_id AND ';
797 END IF;
798 l_party_where_sql := l_party_where_sql ||
799 'hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') ';
800 l_party_where_sql := l_party_where_sql ||
801 'AND hzcsua.status = ''A'' ' ||
802 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
803 l_party_where_sql := l_party_where_sql ||
804 'AND hzcasa.cust_account_id = hzca.cust_account_id ' ;
805 l_party_where_sql := l_party_where_sql ||
806 'AND hzcasa.party_site_id = hzps.party_site_id '||
807 'AND hzps.location_id = hzloc.location_id ';
808 l_party_where_sql := l_party_where_sql ||
809 'AND hzcasa.cust_account_id = hzca.cust_account_id ' ;
810 l_party_where_sql := l_party_where_sql ||
811 'AND hzca.party_id = hzp.party_id '||
812 'AND ' || l_hzparty_sql;
813 write_conc_log('l_hzparty_sql ' || l_party_select_sql || l_party_where_sql);
814 flag := 'T';
815 --
816
817 --TRAY
818 write_conc_log('############### Store SQL for PARTY >>> START');
819 l_store_select_sql := 'SELECT DISTINCT hzp.party_id, '||
820 'hzpsu.party_site_use_id site_use_id, '||
821 'hzpsu.site_use_type site_use_code, '||
822 'hzps.LOCATION_ID ';
823
824 l_store_select_sql := l_store_select_sql ||
825 'FROM hz_parties hzp, '||
826 'hz_party_sites hzps, '||
827 'hz_party_site_uses hzpsu ';
828 l_store_where_sql := 'WHERE ' || l_hzparty_sql;
829 l_store_where_sql := l_store_where_sql ||
830 ' hzpsu.site_use_type = (''STORE'') '||
831 'AND hzpsu.status = ''A'' ';
832 l_store_where_sql := l_store_where_sql ||
833 'AND hzps.party_id = hzp.party_id ' ||
834 'AND hzps.party_site_id = hzpsu.party_site_id ';
835
836 l_store_final_sql := l_store_select_sql||l_store_where_sql;
837 write_conc_log('############### Store SQL for PARTY >>> Constructed: '||l_store_final_sql);
838 --TRAY
839 END IF;
840 IF l_hzpartysiteuse_sql IS NOT NULL
841 THEN
842 --
843 IF l_party_select_sql IS NULL
844 THEN
845 --
846 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
847 'hzca.cust_account_id, '||
848 'hzcsua.cust_acct_site_id, '||
849 'hzcsua.site_use_id, '||
850 'hzcsua.bill_to_site_use_id, '||
851 'hzcsua.site_use_code ' ;
852 l_party_select_sql := l_party_select_sql ||
853 'FROM hz_cust_accounts hzca, '||
854 'hz_cust_site_uses_all hzcsua, '||
855 'hz_cust_acct_sites_all hzcasa ';
856 l_party_where_sql := ' WHERE ' ;
857 --R12
858 IF l_terr_limited_to_ou = 'Y'
859 THEN
860 l_party_where_sql := l_party_where_sql ||
861 'hzcsua.org_id = ' || l_client_info || ' AND ';
862 l_party_where_sql := l_party_where_sql ||
863 'hzcsua.org_id = hzcasa.org_id AND ';
864 END IF;
865 l_party_where_sql := l_party_where_sql ||
866 '( hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') OR ';
867 l_party_where_sql := l_party_where_sql ||
868 substr(l_hzpartysiteuse_sql, 1, length(l_hzpartysiteuse_sql)-4)||
869 ') ';
870 l_party_where_sql := l_party_where_sql ||
871 'AND hzcsua.status = ''A'' '||
872 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ' ;
873 l_party_where_sql := l_party_where_sql ||
874 'AND hzcasa.cust_account_id = hzca.cust_account_id '||
875 'AND ';
876 write_conc_log('IF l_hzpartysiteuse_sql ' || l_party_select_sql || l_party_where_sql);
877 --
878 ELSE
879 --
880 l_party_where_sql := null;
881 l_party_where_sql := ' WHERE ' ;
882 --R12
883 IF l_terr_limited_to_ou = 'Y'
884 THEN
885 l_party_where_sql := l_party_where_sql ||
886 'hzcsua.org_id = ' || l_client_info || ' AND ';
887 l_party_where_sql := l_party_where_sql ||
888 'hzcsua.org_id = hzcasa.org_id AND ';
889 END IF;
890 l_party_where_sql := l_party_where_sql ||
891 '( hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') OR ';
892 l_party_where_sql := l_party_where_sql ||
893 substr(l_hzpartysiteuse_sql, 1, length(l_hzpartysiteuse_sql)-4) ||
894 ') ' ;
895 l_party_where_sql := l_party_where_sql ||
896 'AND hzcsua.status = ''A'' '||
897 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
898 l_party_where_sql := l_party_where_sql ||
899 'AND hzcasa.cust_account_id = hzca.cust_account_id ';
900 l_party_where_sql := l_party_where_sql ||
901 'AND hzcasa.party_site_id = hzps.party_site_id '||
902 'AND hzps.location_id = hzloc.location_id ';
903 l_party_where_sql := l_party_where_sql ||
904 'AND hzcasa.cust_account_id = hzca.cust_account_id ' ;
905 l_party_where_sql := l_party_where_sql ||
906 'AND hzca.party_id = hzp.party_id '||
907 'AND ' || l_hzparty_sql;
908 write_conc_log('Else l_hzpartysiteuse_sql ' || l_party_select_sql || l_party_where_sql);
909 --
910 END IF;
911 --
912 END IF;
913 IF l_hzpartyacc_sql IS NOT NULL
914 THEN
915 --
916 IF l_party_select_sql IS NULL
917 THEN
918 --
919 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
920 'hzca.cust_account_id, '||
921 'hzcsua.cust_acct_site_id, '||
922 'hzcsua.site_use_id, '||
923 'hzcsua.bill_to_site_use_id, '||
924 'hzcsua.site_use_code ';
925 l_party_select_sql := l_party_select_sql ||
926 'FROM hz_cust_accounts hzca, '||
927 'hz_cust_site_uses_all hzcsua, '||
928 'hz_cust_acct_sites_all hzcasa, ';
929 l_party_select_sql := l_party_select_sql ||
930 'hz_party_sites hzps ';
931 l_party_where_sql := ' WHERE ' ;
932 --R12
933 IF l_terr_limited_to_ou = 'Y'
934 THEN
935 l_party_where_sql := l_party_where_sql ||
936 'hzcsua.org_id = ' || l_client_info || ' AND ';
937 l_party_where_sql := l_party_where_sql ||
938 'hzcsua.org_id = hzcasa.org_id AND ';
939 END IF;
940 l_party_where_sql := l_party_where_sql ||
941 'hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') ';
942 -- For the Account Classification
943 l_party_where_sql := l_party_where_sql ||
944 'AND hzcsua.status = ''A'' '||
945 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
946 l_party_where_sql := l_party_where_sql ||
947 'AND hzcasa.cust_account_id = hzca.cust_account_id '||
948 'AND ' || l_hzpartyacc_sql;
949 write_conc_log('IF l_hzpartyacc_sql ' || l_party_select_sql || l_party_where_sql);
950 --
951 ELSE
952 --
953 -- l_party_select_sql := l_party_select_sql || ', hz_cust_accounts hzca';
954 -- l_party_where_sql := l_party_where_sql || 'hzca.party_id AND ' || l_hzpartyacc_sql;
955 IF (flag = 'F')
956 THEN
957 --
958 IF INSTR(l_party_select_sql, 'hz_party_sites') = 0 THEN
959 l_party_select_sql := l_party_select_sql || ', hz_party_sites hzps ';
960 END IF;
961 -- l_party_select_sql := l_party_select_sql || ' ,hz_party_sites hzps ';
962 --
963 END IF;
964 l_party_where_sql := l_party_where_sql || l_hzpartyacc_sql;
965 write_conc_log('ELSE l_hzpartyacc_sql ' || l_party_select_sql || l_party_where_sql);
966 --
967 END IF;
968 --
969 END IF;
970 IF l_hzpartyrel_sql IS NOT NULL
971 THEN
972 --
973 IF l_party_select_sql IS NULL
974 THEN
975 --
976 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
977 'hzca.cust_account_id, '||
978 'hzcsua.cust_acct_site_id, '||
979 'hzcsua.site_use_id, '||
980 'hzcsua.bill_to_site_use_id, '||
981 'hzcsua.site_use_code ';
982 l_party_select_sql := l_party_select_sql ||
983 'FROM hz_cust_site_uses_all hzcsua, '||
984 'hz_cust_acct_sites_all hzcasa, '||
985 'hz_cust_accounts hzca, ';
986 l_party_select_sql := l_party_select_sql ||
987 'hz_relationships hzpr ';
988 l_party_where_sql := 'WHERE ' ;
989 --R12
990 IF l_terr_limited_to_ou = 'Y'
991 THEN
992 l_party_where_sql := l_party_where_sql ||
993 'hzcsua.org_id = ' || l_client_info || ' AND ';
994 l_party_where_sql := l_party_where_sql ||
995 'hzcsua.org_id = hzcasa.org_id AND ';
996 END IF;
997 l_party_where_sql := l_party_where_sql ||
998 'hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') ';
999 l_party_where_sql := l_party_where_sql ||
1000 'AND hzcsua.status = ''A'' '||
1001 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
1002 l_party_where_sql := l_party_where_sql ||
1003 'AND hzcasa.cust_account_id = hzca.cust_account_id '||
1004 'AND hzpr.subject_id = hzca.party_id ';
1005 l_party_where_sql := l_party_where_sql ||
1006 'AND hzpr.start_date <= SYSDATE '||
1007 'AND NVL(hzpr.end_date, SYSDATE) >= SYSDATE ';
1008 l_party_where_sql := l_party_where_sql ||
1009 'AND hzpr.relationship_code = ''SUBSIDIARY_OF'' ' ;
1010 l_party_where_sql := l_party_where_sql ||
1011 'AND hzpr.status = ''A'' '||
1012 'AND ' || l_hzpartyrel_sql;
1013 write_conc_log('IF l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
1014 --
1015 ELSE
1016 --
1017 l_party_select_sql := l_party_select_sql ||
1018 ', hz_relationships hzpr ';
1019 l_party_where_sql := l_party_where_sql ||
1020 'hzpr.subject_id = hzca.party_id '||
1021 'AND hzpr.start_date <= SYSDATE ' ;
1022 l_party_where_sql := l_party_where_sql ||
1023 'AND NVL(hzpr.end_date, SYSDATE) >= SYSDATE ' ;
1024 l_party_where_sql := l_party_where_sql ||
1025 'AND hzpr.relationship_code = ''SUBSIDIARY_OFF'' ';
1026 l_party_where_sql := l_party_where_sql ||
1027 'AND hzpr.status = ''A'' '||
1028 'AND ' || l_hzpartyrel_sql;
1029 write_conc_log('Else l_hzpartyrel_sql ' || l_party_select_sql || l_party_where_sql);
1030 --
1031 END IF;
1032 --
1033 END IF;
1034 -- it is important to check l_hzcustprof_sql AFTER l_hzpartyacc_sql
1035 -- so table hz_cust_accounts does not show twice
1036 IF l_hzcustprof_sql IS NOT NULL
1037 THEN
1038 --
1039 IF l_party_select_sql IS NULL
1040 THEN
1041 --
1042 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
1043 'hzca.cust_account_id, '||
1044 'hzcsua.cust_acct_site_id, '||
1045 'hzcsua.site_use_id, '||
1046 'hzcsua.bill_to_site_use_id, ';
1047 l_party_select_sql := l_party_select_sql ||
1048 'hzcsua.site_use_code '||
1049 'FROM hz_cust_accounts hzca, '||
1050 'hz_cust_site_uses_all hzcsua, ';
1051 l_party_select_sql := l_party_select_sql ||
1052 'hz_cust_acct_sites_all hzcasa, '||
1053 'hz_customer_profiles hzcp ';
1054 l_party_where_sql := ' WHERE ' || l_hzcustprof_sql;
1055 --R12
1056 IF l_terr_limited_to_ou = 'Y'
1057 THEN
1058 l_party_where_sql := l_party_where_sql ||
1059 'hzcsua.org_id = ' || l_client_info || ' AND ';
1060 l_party_where_sql := l_party_where_sql ||
1061 'hzcsua.org_id = hzcasa.org_id AND ';
1062 END IF;
1063 l_party_where_sql := l_party_where_sql ||
1064 'hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') ';
1065 l_party_where_sql := l_party_where_sql ||
1066 'AND hzcsua.status = ''A'' '||
1067 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
1068 l_party_where_sql := l_party_where_sql ||
1069 'AND hzcasa.cust_account_id = hzca.cust_account_id ';
1070 l_party_where_sql := l_party_where_sql ||
1071 'AND hzca.cust_account_id = hzcp.cust_account_id '||
1072 'AND ';
1073 write_conc_log(' If l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
1074 --
1075 ELSE
1076 --
1077 IF l_hzpartyacc_sql IS NOT NULL
1078 THEN
1079 --
1080 l_party_select_sql := l_party_select_sql ||
1081 ', hz_customer_profiles hzcp ';
1082 l_party_where_sql := l_party_where_sql ||
1083 'hzca.cust_account_id = hzcp.cust_account_id '||
1084 'AND ' || l_hzcustprof_sql;
1085 write_conc_log(' If Else If l_hzcustprof_sql ' || l_party_select_sql || l_party_where_sql);
1086 --
1087 ELSE
1088 --
1089 -- l_party_where_sql := l_party_where_sql ||
1090 -- l_party_join_sql ||
1091 -- 'hzca.party_id
1092 -- AND hzca.cust_account_id = hzcp.cust_account_id
1093 -- AND ' || l_hzcustprof_sql;
1094 l_party_select_sql := l_party_select_sql ||
1095 ', hz_customer_profiles hzcp ';
1096 l_party_where_sql := l_party_where_sql ||
1097 ' hzca.cust_account_id = hzcp.cust_account_id '||
1098 'AND ' || l_hzcustprof_sql;
1099 write_conc_log(' If Else else l_hzcustprof_sql '|| l_party_select_sql || l_party_where_sql);
1100 --
1101 END IF;
1102 --
1103 END IF;
1104 --
1105 END IF;
1106 IF l_hzlocations_sql IS NOT NULL
1107 THEN
1108 --
1109 IF l_party_select_sql IS NULL
1110 THEN
1111 --
1112 l_party_select_sql := 'SELECT DISTINCT hzca.party_id, '||
1113 'hzca.cust_account_id, '||
1114 'hzcsua.cust_acct_site_id, '||
1115 'hzcsua.site_use_id, '||
1116 'hzcsua.bill_to_site_use_id, '||
1117 'hzcsua.site_use_code ';
1118 l_party_select_sql := l_party_select_sql ||
1119 'FROM hz_cust_site_uses_all hzcsua, '||
1120 'hz_cust_acct_sites_all hzcasa, '||
1121 'hz_cust_accounts hzca, ';
1122 l_party_select_sql := l_party_select_sql ||
1123 --R12: mkothari 'hz_relationships hzpr, '||
1124 'hz_party_sites hzps, '||
1125 'hz_locations hzloc ';
1126 l_party_where_sql := 'WHERE ' || l_hzlocations_sql;
1127 --R12
1128 IF l_terr_limited_to_ou = 'Y'
1129 THEN
1130 l_party_where_sql := l_party_where_sql ||
1131 'hzcsua.org_id = ' || l_client_info || ' AND ';
1132 l_party_where_sql := l_party_where_sql ||
1133 'hzcsua.org_id = hzcasa.org_id AND ';
1134 END IF;
1135 l_party_where_sql := l_party_where_sql ||
1136 'hzcsua.site_use_code in (''BILL_TO'',''SHIP_TO'') '||
1137 'AND hzcsua.status = ''A'' ';
1138 l_party_where_sql := l_party_where_sql ||
1139 'AND hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id ';
1140 l_party_where_sql := l_party_where_sql ||
1141 'AND hzcasa.cust_account_id = hzca.cust_account_id ' ;
1142 l_party_where_sql := l_party_where_sql ||
1143 'AND hzcasa.party_site_id = hzps.party_site_id '||
1144 'AND hzps.location_id = hzloc.location_id '||
1145 'AND ' ;
1146 write_conc_log(' If l_hzloactions_sql ' || l_party_select_sql || l_party_where_sql);
1147 --
1148 ELSE
1149 -- l_party_select_sql := l_party_select_sql || ', hz_party_sites hzps, hz_locations hzloc';
1150 IF (flag = 'F')
1151 THEN
1152 --
1153 IF INSTR(l_party_select_sql, 'hz_locations') = 0 THEN
1154 l_party_select_sql := l_party_select_sql || ', hz_locations hzloc ';
1155 END IF;
1156
1157 IF INSTR(l_party_select_sql, 'hz_party_sites') = 0 THEN
1158 l_party_select_sql := l_party_select_sql || ', hz_party_sites hzps ';
1159 END IF;
1160 /*
1161 l_party_select_sql := l_party_select_sql ||
1162 ', hz_locations hzloc '||
1163 ', hz_party_sites hzps ';
1164 */
1165 --
1166 END IF;
1167 l_party_where_sql := l_party_where_sql ||
1168 ' hzcasa.party_site_id = hzps.party_site_id '||
1169 'AND hzps.location_id = hzloc.location_id '||
1170 'AND ';
1171 l_party_where_sql := l_party_where_sql || l_hzlocations_sql;
1172 write_conc_log(' Else l_hzloactions_sql ' || l_party_select_sql || l_party_where_sql);
1173 --
1174 END IF;
1175 --TRAY
1176 write_conc_log('############### Store SQL for LOCATION >>> START');
1177 l_store_select_sql := 'SELECT DISTINCT hzp.party_id, '||
1178 'hzpsu.party_site_use_id site_use_id, '||
1179 'hzpsu.site_use_type site_use_code,'||
1180 'hzps.location_id ';
1181
1182 l_store_select_sql := l_store_select_sql ||
1183 'FROM hz_parties hzp, '||
1184 'hz_party_sites hzps, '||
1185 'hz_party_site_uses hzpsu, '||
1186 'hz_locations hzloc ';
1187 l_store_where_sql := 'WHERE ' || l_hzparty_sql ||' '|| l_hzlocations_sql;
1188 l_store_where_sql := l_store_where_sql ||
1189 ' hzpsu.site_use_type = (''STORE'') '||
1190 'AND hzpsu.status = ''A'' ';
1191 l_store_where_sql := l_store_where_sql ||
1192 'AND hzps.location_id = hzloc.location_id '||
1193 'AND hzps.party_id = hzp.party_id ' ||
1194 'AND hzps.party_site_id = hzpsu.party_site_id ';
1195
1196 l_store_final_sql := l_store_select_sql||l_store_where_sql;
1197 write_conc_log('############### Store SQL for LOCATION >>> Constructed'||l_store_final_sql);
1198
1199 --TRAY
1200
1201 END IF;
1202 /*
1203 DBMS_OUTPUT.PUT_LINE(' final from sql(' || length(l_party_select_sql) || '): ' || l_party_select_sql);
1204 DBMS_OUTPUT.PUT_LINE(' final where sql length=' || length(l_party_where_sql));
1205 l_index := 1;
1206 WHILE l_index < (length(l_party_where_sql)-4) LOOP
1207 DBMS_OUTPUT.PUT_LINE( substr(l_party_where_sql, l_index, 240));
1208 l_index := l_index + 240;
1209 END LOOP;
1210 */
1211
1212 --TRAY
1213
1214 IF l_store_final_sql IS NOT NULL THEN
1215 write_conc_log('############### Store Insert SQL >>> START');
1216 l_store_insert_sql := 'INSERT INTO OZF_TP_TERRUSG_MAP('||
1217 'OZF_TP_TERRUSG_MAP_ID, '||
1218 'last_update_date, '||
1219 'last_updated_by,';
1220 l_store_insert_sql := l_store_insert_sql ||
1221 'creation_date, '||
1222 'created_by, '||
1223 'last_update_login, ';
1224 l_store_insert_sql := l_store_insert_sql ||
1225 'party_id, '||
1226 'start_date_active, '||
1227 'end_date_active, ' ;
1228 l_store_insert_sql := l_store_insert_sql ||
1229 'market_qualifier_type, '||
1230 'market_qualifier_reference, '||
1231 'cust_account_id, '||
1232 'cust_acct_site_id, '||
1233 'site_use_id, '||
1234 'bill_to_site_use_id, '||
1235 'site_use_code, '||
1236 'user_added, '||
1237 'location_id )';
1238 l_store_insert_sql := l_store_insert_sql ||
1239 ' SELECT OZF_TP_TERRUSG_MAP_S.nextval, '||
1240 'SYSDATE, '||
1241 'FND_GLOBAL.user_id, ';
1242 l_store_insert_sql := l_store_insert_sql ||
1243 'SYSDATE, '||
1244 'FND_GLOBAL.user_id, '||
1245 'FND_GLOBAL.conc_login_id, ';
1246 l_store_insert_sql := l_store_insert_sql ||
1247 'party_id, '||
1248 'SYSDATE, '||
1249 'NULL, ';
1250 l_store_insert_sql := l_store_insert_sql ||
1251 ' ''TERRITORY'', '||
1252 ':terr_id market_qualifier_reference, '||
1253 'NULL, '||
1254 'NULL, '||
1255 'site_use_id, '||
1256 'NULL, '||
1257 'site_use_code, '||
1258 '''N'' user_added, '||
1259 'location_id '||
1260 'FROM (';
1261 l_store_insert_sql := l_store_insert_sql ||
1262 l_store_final_sql ||
1263 ')';
1264 write_conc_log('############### Store Insert SQL >>> Constructed '||l_store_insert_sql);
1265 END IF;
1266 write_conc_log('###############l_store_final_sql: '||l_store_final_sql);
1267 write_conc_log('###############l_store_insert_sql: '||l_store_insert_sql);
1268
1269 IF l_store_insert_sql IS NOT NULL THEN
1270
1271 l_store_csr := DBMS_SQL.open_cursor;
1272 DBMS_SQL.parse(l_store_csr, l_store_insert_sql, DBMS_SQL.native);
1273 DBMS_SQL.BIND_VARIABLE (l_store_csr, ':terr_id', p_terr_id);
1274
1275 select length(l_store_final_sql) into len from dual;
1276 Ozf_Utility_pvt.write_conc_log('############### Timestamp before for loop: '||to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'));
1277 for n in 1..len
1278 LOOP
1279 --select SUBSTR(SUBSTR(l_store_final_sql,instr(l_store_final_sql,':',1,n),11),10,10 )into lvar from dual;
1280
1281 -- IF lvar between '1' and '9999' then
1282 Ozf_Utility_pvt.write_conc_log('############### Counter value inside for loop is: '||n);
1283 /*
1284 amitamku: fix bug 13495579 - PERFORMANCE ISSUE : GENERATE PARTY LIST FOR MARKET QUALIFIERS
1285 Added exit block so that once it finds the last ":" in the string it will come out of the for loop
1286 */
1287 exit when instr(l_store_final_sql,':',1,n) = 0;
1288 IF instr(l_store_final_sql,':',1,n)>0 THEN
1289 lvar:=n;
1290 select instr(translate(lvar,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') into isNum FROM dual;
1291 IF isNum = 0 then
1292
1293 write_conc_log('###############Bind Variable Number: '||lvar);
1294
1295 IF l_final_bind_vars(lvar).bind_type = G_BIND_TYPE_CHAR
1296 THEN
1297 --
1298 write_conc_log('D: bind vars ' || lvar || ' char = ' || l_final_bind_vars(lvar).bind_char);
1299 DBMS_SQL.BIND_VARIABLE (l_store_csr,
1300 G_BIND_VAR_STRING || l_final_bind_vars(lvar).bind_index,
1301 l_final_bind_vars(lvar).bind_char);
1302 --
1303 ELSIF l_final_bind_vars(lvar).bind_type = G_BIND_TYPE_NUMBER
1304 THEN
1305 --
1306 write_conc_log('D: bind vars ' || lvar || ' number=' || l_final_bind_vars(lvar).bind_number);
1307 DBMS_SQL.BIND_VARIABLE (l_store_csr,
1308 G_BIND_VAR_STRING || l_final_bind_vars(lvar).bind_index,
1309 l_final_bind_vars(lvar).bind_number);
1310 --
1311 END IF;
1312 END IF;
1313 END IF;
1314 END LOOP;
1315 Ozf_Utility_pvt.write_conc_log('############### Timestamp after for loop: '||to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'));
1316 DELETE FROM OZF_TP_TERRUSG_MAP
1317 WHERE market_qualifier_type = 'TERRITORY'
1318 AND market_qualifier_reference = p_terr_id
1319 AND user_added = 'N';
1320
1321 l_store_index := dbms_sql.execute(l_store_csr);
1322 dbms_sql.close_cursor(l_store_csr);
1323 END IF;
1324
1325 write_conc_log('############### Stores inserted in ozf mapping: '||l_store_index);
1326 --TRAY
1327
1328 -- mkothari - sep-09-2005 --R12
1329 -- Dump old terr definition into global temp table so that
1330 -- code can identify that account is moved from where to where.
1331 DELETE FROM OZF_PARTY_MARKET_SEGMENTS_T
1332 WHERE market_qualifier_type = 'TERRITORY'
1333 AND market_qualifier_reference = p_terr_id;
1334
1335 /* --this give GSCC error - "select * not allowed"
1336 INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
1337 SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
1338 */
1339 INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
1340 AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1341 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
1342 OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
1343 PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
1344 ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
1345 PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
1346 MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
1347 CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
1348 ROLLUP_PARTY_ID, SITE_USE_ID)
1349 SELECT AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1350 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
1351 OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
1352 PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
1353 ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
1354 PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
1355 MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
1356 CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
1357 ROLLUP_PARTY_ID, SITE_USE_ID
1358 FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
1359 WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
1360 AND OLD_TERR.market_qualifier_reference = p_terr_id
1361 AND OLD_TERR.site_use_code = 'SHIP_TO'
1362 AND OLD_TERR.party_id IS NOT NULL
1363 AND OLD_TERR.site_use_id IS NOT NULL;
1364
1365
1366 DELETE FROM AMS_PARTY_MARKET_SEGMENTS
1367 WHERE market_qualifier_type = 'TERRITORY'
1368 AND market_qualifier_reference = p_terr_id;
1369 -- remove 'AND ' at the end of the where clause
1370 -- write_conc_log('Before opening the cursor ');
1371 write_conc_log('D: The dynamic SQL ' ||
1372 l_party_select_sql ||
1373 substr(l_party_where_sql, 1, length(l_party_where_sql)-4));
1374 IF l_party_select_sql IS NOT NULL
1375 THEN
1376 -- yzhao: 05/08/2003 SQL bind variable project
1377 l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS('||
1378 'ams_party_market_segment_id, '||
1379 'last_update_date, '||
1380 'last_updated_by,';
1381 l_final_sql := l_final_sql ||
1382 'creation_date, '||
1383 'created_by, '||
1384 'last_update_login, '||
1385 'object_version_number, '||
1386 'market_segment_id,';
1387 l_final_sql := l_final_sql ||
1388 'market_segment_flag, '||
1389 'party_id, '||
1390 'start_date_active, '||
1391 'end_date_active, '||
1392 'org_id,';
1393 l_final_sql := l_final_sql ||
1394 'market_qualifier_type, '||
1395 'market_qualifier_reference, '||
1396 'cust_account_id, '||
1397 'cust_acct_site_id, '||
1398 'site_use_id, '||
1399 'bill_to_site_use_id, '||
1400 'site_use_code )';
1401 l_final_sql := l_final_sql ||
1402 ' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, '||
1403 'SYSDATE, '||
1404 'FND_GLOBAL.user_id, ';
1405 l_final_sql := l_final_sql ||
1406 'SYSDATE, '||
1407 'FND_GLOBAL.user_id, '||
1408 'FND_GLOBAL.conc_login_id, '||
1409 '1, '||
1410 '0, ';
1411 l_final_sql := l_final_sql ||
1412 ' ''N'', '||
1413 'party_id, '||
1414 'SYSDATE, '||
1415 'NULL, '||
1416 ':org_id org_id,';
1417 l_final_sql := l_final_sql ||
1418 ' ''TERRITORY'', '||
1419 ':terr_id market_qualifier_reference, '||
1420 'cust_account_id, '||
1421 'cust_acct_site_id, '||
1422 'site_use_id, '||
1423 'bill_to_site_use_id, '||
1424 'site_use_code '||
1425 'FROM (';
1426 l_final_sql := l_final_sql ||
1427 l_party_select_sql ||
1428 substr(l_party_where_sql, 1, length(l_party_where_sql)-4) ||
1429 ')';
1430 write_conc_log('TRAY>>>>>>>>>>>>>>>>>>>>l_final_sql: '||l_final_sql);
1431 l_denorm_csr := DBMS_SQL.open_cursor;
1432 DBMS_SQL.parse(l_denorm_csr, l_final_sql, DBMS_SQL.native);
1433 DBMS_SQL.BIND_VARIABLE (l_denorm_csr, ':org_id', l_client_info);
1434 DBMS_SQL.BIND_VARIABLE (l_denorm_csr, ':terr_id', p_terr_id);
1435 FOR i IN NVL(l_final_bind_vars.FIRST, 1) .. NVL(l_final_bind_vars.LAST, 0)
1436 LOOP
1437 --
1438 write_conc_log('D: bind vars ' || i || ' index=' ||
1439 l_final_bind_vars(i).bind_index ||
1440 ' type = ' ||
1441 l_final_bind_vars(i).bind_type );
1442 IF l_final_bind_vars(i).bind_type = G_BIND_TYPE_CHAR
1443 THEN
1444 --
1445 write_conc_log('D: bind vars ' || i || ' char = ' || l_final_bind_vars(i).bind_char);
1446 DBMS_SQL.BIND_VARIABLE (l_denorm_csr,
1447 G_BIND_VAR_STRING || l_final_bind_vars(i).bind_index,
1448 l_final_bind_vars(i).bind_char);
1449 --
1450 ELSIF l_final_bind_vars(i).bind_type = G_BIND_TYPE_NUMBER
1451 THEN
1452 --
1453 write_conc_log('D: bind vars ' || i || ' number=' || l_final_bind_vars(i).bind_number);
1454 DBMS_SQL.BIND_VARIABLE (l_denorm_csr,
1455 G_BIND_VAR_STRING || l_final_bind_vars(i).bind_index,
1456 l_final_bind_vars(i).bind_number);
1457 --
1458 END IF;
1459 --
1460 END LOOP;
1461 l_index := dbms_sql.execute(l_denorm_csr);
1462 write_conc_log('D: After executing ');
1463 dbms_sql.close_cursor(l_denorm_csr);
1464
1465 Ozf_Utility_pvt.write_conc_log(l_full_name || ': Rows inserted in ams denorm table: '||l_index);
1466
1467 --TRAY
1468 --Needs to be done here so that the copied row is not lost
1469 if l_store_index>0 then
1470
1471 write_conc_log('############### Copy Store from ozf mapping table to ams table >>> START');
1472 INSERT INTO ams_party_market_segments
1473 (
1474 AMS_PARTY_MARKET_SEGMENT_ID,
1475 LAST_UPDATE_DATE,
1476 LAST_UPDATED_BY,
1477 CREATION_DATE,
1478 CREATED_BY,
1479 LAST_UPDATE_LOGIN,
1480 MARKET_SEGMENT_ID,
1481 MARKET_SEGMENT_FLAG,
1482 PARTY_ID,
1483 START_DATE_ACTIVE,
1484 END_DATE_ACTIVE,
1485 MARKET_QUALIFIER_TYPE,
1486 MARKET_QUALIFIER_REFERENCE,
1487 CUST_ACCOUNT_ID,
1488 CUST_ACCT_SITE_ID,
1489 SITE_USE_CODE,
1490 BILL_TO_SITE_USE_ID,
1491 ROLLUP_PARTY_ID,
1492 SITE_USE_ID,
1493 ORG_ID
1494 )
1495 select
1496 AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL,
1497 SYSDATE,
1498 FND_GLOBAL.user_id,
1499 SYSDATE,
1500 FND_GLOBAL.user_id,
1501 FND_GLOBAL.conc_login_id,
1502 0,
1503 'N',
1504 PARTY_ID,
1505 START_DATE_ACTIVE,
1506 END_DATE_ACTIVE,
1507 MARKET_QUALIFIER_TYPE,
1508 MARKET_QUALIFIER_REFERENCE,
1509 CUST_ACCOUNT_ID,
1510 CUST_ACCT_SITE_ID,
1511 SITE_USE_CODE,
1512 BILL_TO_SITE_USE_ID,
1513 ROLLUP_PARTY_ID,
1514 SITE_USE_ID,
1515 l_client_info
1516 FROM ozf_tp_terrusg_map
1517 WHERE MARKET_QUALIFIER_REFERENCE = p_terr_id;
1518 write_conc_log('############### Copy Store from ozf mapping table to ams table >>> DONE');
1519 end if;
1520
1521
1522 --TRAY
1523
1524 /* -- Before the Bind Variable Project
1525 OPEN l_party_cv
1526 FOR l_party_select_sql || substr(l_party_where_sql, 1, length(l_party_where_sql)-4);
1527 LOOP
1528 FETCH l_party_cv INTO l_party_id,
1529 l_cust_account_id,
1530 l_cust_acct_site_id,
1531 l_cust_site_use_code;
1532 write_conc_log('l_party_id ' || l_party_id);
1533 write_conc_log('l_cust_account_id ' || l_cust_account_id);
1534 write_conc_log('l_cust_acct_site_id ' || l_cust_acct_site_id);
1535 write_conc_log('l_cust_site_use_code ' || l_cust_site_use_code);
1536 EXIT WHEN l_party_cv%NOTFOUND;
1537 -- dbms_output.put_line(l_full_name ||
1538 -- ': INSERT: party_id=' || l_party_id ||
1539 -- ' territory_id=' || p_terr_id);
1540 OPEN c_party_mkt_seg_seq;
1541 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
1542 CLOSE c_party_mkt_seg_seq;
1543 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1544 (
1545 ams_party_market_segment_id
1546 , last_update_date
1547 , last_updated_by
1548 , creation_date
1549 , created_by
1550 , last_update_login
1551 , object_version_number
1552 , market_segment_id
1553 , market_segment_flag
1554 , party_id
1555 , start_date_active
1556 , end_date_active
1557 , org_id
1558 , market_qualifier_type
1559 , market_qualifier_reference
1560 , cust_account_id
1561 , cust_acct_site_id
1562 , site_use_code
1563 )
1564 VALUES
1565 (
1566 l_party_mkt_seg_id
1567 , SYSDATE
1568 , FND_GLOBAL.user_id
1569 , SYSDATE
1570 , FND_GLOBAL.user_id
1571 , FND_GLOBAL.conc_login_id
1572 , 1
1573 , 0
1574 , 'N'
1575 , l_party_id
1576 , SYSDATE
1577 , NULL
1578 , l_client_info
1579 , 'TERRITORY'
1580 , p_terr_id
1581 ,l_cust_account_id
1582 ,l_cust_acct_site_id
1583 ,l_cust_site_use_code
1584 );
1585 END LOOP;
1586 CLOSE l_party_cv;
1587 */
1588 END IF;
1589 UPDATE ams_party_market_segments a
1590 SET a.rollup_party_id = ( SELECT acct.party_id
1591 FROM hz_cust_accounts_all acct,
1592 hz_cust_acct_sites_all acct_site,
1593 hz_cust_site_uses_all site_use
1594 WHERE site_use.site_use_id = NVL(a.bill_to_site_use_id,a.site_use_id)
1595 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1596 AND acct_site.cust_account_id = acct.cust_account_id)
1597 WHERE a.market_qualifier_type = 'TERRITORY'
1598 AND a.market_qualifier_reference = p_terr_id;
1599 Ozf_Utility_pvt.write_conc_log(l_full_name || ': Success for territory ' || p_terr_id);
1600
1601 ---R12 ------
1602 Ozf_Utility_pvt.write_conc_log(l_full_name || ': BEGIN - Adjusting Account Targets for Territory = '|| p_terr_id || ';');
1603 OZF_ALLOCATION_ENGINE_PVT.adjust_account_targets(p_retcode, p_errbuf, p_terr_id);
1604 Ozf_Utility_pvt.write_conc_log(l_full_name || ': END - Adjusting Account Targets for Territory = ' || p_terr_id || ';');
1605
1606
1607 /* recursively generate party list for the territory's children
1608 passing in parent's qualifier directly so don't need to calculate again
1609 */
1610 l_index := l_terr_child_table.FIRST;
1611 WHILE l_index IS NOT NULL
1612 LOOP
1613 --
1614 generate_party_for_territory
1615 ( p_errbuf => p_errbuf
1616 , p_retcode => p_retcode
1617 , p_terr_id => l_terr_child_table(l_index)
1618 , p_getparent_flag => 'N'
1619 , p_bind_vars => l_final_bind_vars
1620 , p_hzparty_sql => l_hzparty_sql
1621 , p_hzpartyacc_sql => l_hzpartyacc_sql
1622 , p_hzpartyrel_sql => l_hzpartyrel_sql
1623 -- , p_hzpartysite_sql => l_hzpartysite_sql
1624 , p_hzpartysiteuse_sql => l_hzpartysiteuse_sql
1625 , p_hzcustprof_sql => l_hzcustprof_sql
1626 --, p_hzcustname_sql => l_hzcustname_sql
1627 --, p_hzcustcat_sql => l_hzcustcat_sql
1628 --, p_hzsaleschannel_sql => l_hzsaleschannel_sql
1629 , p_hzlocations_sql => l_hzlocations_sql
1630 );
1631 l_index := l_terr_child_table.NEXT(l_index);
1632 --
1633 END LOOP;
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 /* Let the master procdure handle exception */
1637 Ozf_Utility_pvt.write_conc_log('Exception in get_party_territory ' || sqlerrm);
1638 p_retcode := 1;
1639 l_err_msg := 'Exception while generating parties for territory id=' ||
1640 p_terr_id || ' - ' || sqlerrm;
1641 p_errbuf := l_err_msg;
1642 raise;
1643 END generate_party_for_territory;
1644 /*****************************************************************************
1645 * NAME
1646 * generate_party_for_buyinggroup
1647 *
1648 * PURPOSE
1649 * This procedure is a private procedure used by LOAD_PARTY_MARKET_QUALIFIER
1650 * to generate buying groups information
1651 *
1652 * NOTES
1653 *
1654 * HISTORY
1655 * 11/09/2001 yzhao created
1656 * 02/07/2003 yzhao to handle non-directional relationship like 'PARTNER_OF',
1657 * add directional_flag in c_get_object_ids
1658 ******************************************************************************/
1659 PROCEDURE generate_party_for_buyinggroup
1660 ( p_errbuf OUT NOCOPY VARCHAR2,
1661 p_retcode OUT NOCOPY NUMBER,
1662 p_bg_id IN NUMBER,
1663 p_direction IN VARCHAR2 := NULL,
1664 p_obj_list OUT NOCOPY NUMBER_TBL_TYPE
1665 )
1666 IS
1667 l_full_name CONSTANT VARCHAR2(60) := 'generate_party_for_buyinggroup';
1668 l_err_msg VARCHAR2(2000);
1669 l_obj_list NUMBER_TBL_TYPE;
1670 l_child_obj_list NUMBER_TBL_TYPE;
1671 l_all_obj_list NUMBER_TBL_TYPE;
1672 l_party_mkt_seg_id NUMBER_TBL_TYPE;
1673 l_client_info NUMBER;
1674 l_index NUMBER;
1675 CURSOR c_get_object_ids IS
1676 SELECT subject_id
1677 FROM hz_relationships
1678 WHERE relationship_code = fnd_profile.VALUE('OZF_PARTY_RELATIONS_TYPE')
1679 AND subject_type = 'ORGANIZATION'
1680 AND subject_table_name = 'HZ_PARTIES'
1681 AND object_type = 'ORGANIZATION'
1682 AND object_table_name = 'HZ_PARTIES'
1683 AND start_date <= SYSDATE AND NVL(end_date, SYSDATE) >= SYSDATE
1684 AND status = 'A'
1685 AND object_id = p_bg_id
1686 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI */
1687 AND directional_flag = NVL(p_direction, directional_flag);
1688 CURSOR c_party_mkt_seg_seq IS -- generate an ID
1689 SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
1690 FROM DUAL;
1691 BEGIN
1692 Ozf_Utility_pvt.write_conc_log(l_full_name || ': Start buyinggroup_id=' || p_bg_id);
1693 p_errbuf := null;
1694 p_retcode := 0;
1695 -- delete all buying group records for this subject_id
1696
1697 DELETE FROM AMS_PARTY_MARKET_SEGMENTS
1698 WHERE market_qualifier_type = 'BG'
1699 AND market_qualifier_reference = p_bg_id;
1700
1701 -- l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT
1702 -- _INFO'),1,10));
1703 l_client_info := NULL;
1704
1705 OPEN c_party_mkt_seg_seq;
1706 FETCH c_party_mkt_seg_seq INTO l_index;
1707 CLOSE c_party_mkt_seg_seq;
1708 -- 03/26/2002 always return the party itself as part of the buying group
1709 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1710 (
1711 ams_party_market_segment_id
1712 , last_update_date
1713 , last_updated_by
1714 , creation_date
1715 , created_by
1716 , last_update_login
1717 , object_version_number
1718 , market_segment_id
1719 , market_segment_flag
1720 , party_id
1721 , start_date_active
1722 , end_date_active
1723 , org_id
1724 , market_qualifier_type
1725 , market_qualifier_reference
1726 )
1727 VALUES
1728 (
1729 l_index
1730 , SYSDATE
1731 , FND_GLOBAL.user_id
1732 , SYSDATE
1733 , FND_GLOBAL.user_id
1734 , FND_GLOBAL.conc_login_id
1735 , 1
1736 , 0
1737 , 'N'
1738 , p_bg_id
1739 , SYSDATE
1740 , NULL
1741 , l_client_info
1742 , 'BG'
1743 , p_bg_id
1744 );
1745 OPEN c_get_object_ids;
1746 FETCH c_get_object_ids BULK COLLECT INTO l_obj_list;
1747 CLOSE c_get_object_ids;
1748 -- dbms_output.put_line('buy(' || p_bg_id || '): object count=' || l_obj_list.count);
1749 IF l_obj_list.count = 0 THEN
1750 -- return. Leaf node.
1751 p_obj_list := l_obj_list;
1752 Ozf_Utility_pvt.write_conc_log(l_full_name || ': END buyinggroup_id=' || p_bg_id);
1753 return;
1754 END IF;
1755 FOR I IN NVL(l_obj_list.FIRST, 1) .. NVL(l_obj_list.LAST, 0) LOOP
1756 OPEN c_party_mkt_seg_seq;
1757 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(I);
1758 CLOSE c_party_mkt_seg_seq;
1759 END LOOP;
1760 l_all_obj_list := l_obj_list;
1761 l_index := l_all_obj_list.LAST;
1762 -- get buying groups for all subject_ids of p_bg_id
1763 FOR I IN NVL(l_obj_list.FIRST, 1) .. NVL(l_obj_list.LAST, 0) LOOP
1764 generate_party_for_buyinggroup
1765 ( p_errbuf => p_errbuf,
1766 p_retcode => p_retcode,
1767 p_bg_id => l_obj_list(I),
1768 p_direction => p_direction,
1769 p_obj_list => l_child_obj_list
1770 );
1771 -- append l_child_obj_list to l_all_obj_list
1772 IF l_child_obj_list.COUNT > 0 THEN
1773 FOR J IN NVL(l_child_obj_list.FIRST, 1) .. NVL(l_child_obj_list.LAST, 0) LOOP
1774 l_index := l_index + 1;
1775 l_all_obj_list(l_index) := l_child_obj_list(J);
1776 OPEN c_party_mkt_seg_seq;
1777 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(l_index);
1778 CLOSE c_party_mkt_seg_seq;
1779 END LOOP;
1780 END IF;
1781 END LOOP;
1782 -- DBMS_OUTPUT.PUT_LINE(l_full_name || ': INSERT buying group: buyinggroup_id='
1783 -- || p_bg_id || ' count=' || l_all_obj_list.COUNT);
1784 FORALL I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0)
1785 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1786 (
1787 ams_party_market_segment_id
1788 , last_update_date
1789 , last_updated_by
1790 , creation_date
1791 , created_by
1792 , last_update_login
1793 , object_version_number
1794 , market_segment_id
1795 , market_segment_flag
1796 , party_id
1797 , start_date_active
1798 , end_date_active
1799 , org_id
1800 , market_qualifier_type
1801 , market_qualifier_reference
1802 )
1803 VALUES
1804 (
1805 l_party_mkt_seg_id(I)
1806 , SYSDATE
1807 , FND_GLOBAL.user_id
1808 , SYSDATE
1809 , FND_GLOBAL.user_id
1810 , FND_GLOBAL.conc_login_id
1811 , 1
1812 , 0
1813 , 'N'
1814 , l_all_obj_list(I)
1815 , SYSDATE
1816 , NULL
1817 , l_client_info
1818 , 'BG'
1819 , p_bg_id
1820 );
1821 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
1822 for non-directional records, always insert a row pair of (A, B) and (B, A) */
1823 IF (p_direction IS NOT NULL AND l_all_obj_list.FIRST IS NOT NULL) THEN
1824 FOR I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0) LOOP
1825 OPEN c_party_mkt_seg_seq;
1826 FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(I);
1827 CLOSE c_party_mkt_seg_seq;
1828 END LOOP;
1829 FORALL I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0)
1830 INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1831 (
1832 ams_party_market_segment_id
1833 , last_update_date
1834 , last_updated_by
1835 , creation_date
1836 , created_by
1837 , last_update_login
1838 , object_version_number
1839 , market_segment_id
1840 , market_segment_flag
1841 , party_id
1842 , start_date_active
1843 , end_date_active
1844 , org_id
1845 , market_qualifier_type
1846 , market_qualifier_reference
1847 )
1848 VALUES
1849 (
1850 l_party_mkt_seg_id(I)
1851 , SYSDATE
1852 , FND_GLOBAL.user_id
1853 , SYSDATE
1854 , FND_GLOBAL.user_id
1855 , FND_GLOBAL.conc_login_id
1856 , 1
1857 , 0
1858 , 'N'
1859 , p_bg_id
1860 , SYSDATE
1861 , NULL
1862 , l_client_info
1863 , 'BG'
1864 , l_all_obj_list(I)
1865 );
1866 END IF;
1867 p_obj_list := l_all_obj_list;
1868 Ozf_Utility_pvt.write_conc_log(l_full_name || ': END buyinggroup_id=' || p_bg_id);
1869 EXCEPTION
1870 WHEN OTHERS THEN
1871 /* Let the master procdure handle exception */
1872 p_retcode := 1;
1873 l_err_msg := 'Exception while generating buying group buyinggroup_id=' || p_bg_id || ' - ' || sqlerrm;
1874 p_errbuf := l_err_msg;
1875 -- dbms_output.put_line('Exception: ' || substr(l_err_msg, 1, 220));
1876 RAISE;
1877 END;
1878 /*****************************************************************************
1879 * NAME
1880 * LOAD_PARTY_MARKET_QUALIFIER
1881 *
1882 * PURPOSE
1883 * This procedure is a concurrent program to
1884 * generate buying groups recursively
1885 * generate party list that matches a given territory's qualifiers
1886 * it also recursively generates party list for the territory's children
1887 *
1888 * NOTES
1889 *
1890 * HISTORY
1891 * 10/04/2001 yzhao created
1892 * 11/14/2001 yzhao add buying group
1893 ******************************************************************************/
1894 PROCEDURE LOAD_PARTY_MARKET_QUALIFIER
1895 ( errbuf OUT NOCOPY VARCHAR2,
1896 retcode OUT NOCOPY NUMBER,
1897 p_terr_id IN NUMBER := NULL,
1898 p_bg_id IN NUMBER := NULL
1899 )
1900 IS
1901 l_full_name CONSTANT VARCHAR2(60) := 'LOAD_PARTY_FOR_MARKET_QUALIFIERS';
1902 l_terr_id NUMBER;
1903 l_org_id NUMBER;
1904 l_bg_id NUMBER;
1905 l_rel_profile VARCHAR2(30);
1906 l_rel_type VARCHAR2(30);
1907 l_obj_list NUMBER_TBL_TYPE;
1908 l_direction_code VARCHAR2(1);
1909 l_bind_vars BIND_TBL_TYPE;
1910 CURSOR c_get_all_territories IS -- get all root territories of trade management
1911 /*SELECT distinct terr_id
1912 FROM jtf_terr_overview_v jtov
1913 WHERE jtov.source_id = -1003
1914 AND parent_territory_id = 1;
1915 */
1916 select distinct JTR.terr_id
1917 FROM JTF_TERR_ALL JTR ,
1918 JTF_TERR_USGS_ALL JTU ,
1919 JTF_SOURCES_ALL JSE
1920 WHERE JTU.TERR_ID = JTR.TERR_ID
1921 AND JTU.SOURCE_ID = JSE.SOURCE_ID
1922 AND JTU.SOURCE_ID = -1003
1923 AND JTR.PARENT_TERRITORY_ID = 1
1924 AND NVL(JTR.ORG_ID, -99) = NVL(JTU.ORG_ID, NVL(JTR.ORG_ID, -99))
1925 AND JSE.ORG_ID IS NULL;
1926 /*
1927 -R12: mkothari- Denorm Terr belonging to all orgs if p_terr_id is not passed ----
1928 AND NVL(JTR.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT
1929 _INFO'),1,1),' ' ,
1930 NULL, SUBSTR(USERENV('CLIENT
1931 _INFO'),1,10))),-99)) =
1932 NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT
1933 _INFO'),1,1),' ',
1934 NULL, SUBSTR(USERENV('CLIENT
1935 _INFO'),1,10))),-99);
1936 ---------------------------------------------------------------------------------
1937 */
1938
1939
1940 /*
1941 Fix for bug 3158378: Replaced this where clause in the above sql
1942 AND NVL(JTR.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT
1943 _INFO'),1,1),' ' , NULL,
1944 SUBSTR(USERENV('CLIENT
1945 _INFO'),1,10))),-99)) =
1946 NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT
1947 _INFO'),1,1),' ', NULL, SUBSTR(USERENV('CLIENT
1948 _INFO'),1,10))),-99);
1949 */
1950 CURSOR c_get_relationship_type(p_relationship_code VARCHAR2) IS
1951 SELECT relationship_type, direction_code
1952 FROM hz_relationship_types
1953 WHERE (forward_rel_code = p_relationship_code
1954 OR backward_rel_code = p_relationship_code)
1955 AND subject_type = 'ORGANIZATION'
1956 AND object_type = 'ORGANIZATION'
1957 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
1958 P - Parent C - Child N - non-directional
1959 e.g. 'PARTNER_OF' is non-directional relationship
1960 AND direction_code = 'P'
1961 */
1962 AND direction_code IN ('P', 'N')
1963 AND status = 'A'
1964 /* mgudivak: Bug 3433528 */
1965 AND hierarchical_flag = 'N';
1966 /* yzhao: 08/07/2002 fix performance issue. Use index on relationship_type */
1967 CURSOR c_get_all_bgroots(p_relationship_code VARCHAR2, p_relationship_type VARCHAR2, p_direction_code VARCHAR2) IS
1968 -- get all root object_ids
1969 SELECT distinct r1.object_id
1970 FROM hz_relationships r1
1971 WHERE r1.relationship_type = p_relationship_type
1972 AND r1.relationship_code = p_relationship_code
1973 AND r1.subject_type = 'ORGANIZATION'
1974 AND r1.subject_table_name = 'HZ_PARTIES'
1975 AND r1.object_type = 'ORGANIZATION'
1976 AND r1.object_table_name = 'HZ_PARTIES'
1977 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
1978 AND r1.status = 'A'
1979 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
1980 handle non-directional relationship e.g. PARTNER_OF
1981 */
1982 AND r1.directional_flag = NVL(p_direction_code, r1.directional_flag)
1983 AND NOT EXISTS
1984 (SELECT 1
1985 FROM hz_relationships r2
1986 WHERE r1.object_id = r2.subject_id
1987 AND r2.relationship_type = p_relationship_type
1988 AND r2.relationship_code = p_relationship_code
1989 AND r2.subject_type = 'ORGANIZATION'
1990 AND r2.subject_table_name = 'HZ_PARTIES'
1991 AND r2.object_type = 'ORGANIZATION'
1992 AND r2.object_table_name = 'HZ_PARTIES'
1993 AND r2.start_date <= SYSDATE AND NVL(r2.end_date, SYSDATE) >= SYSDATE
1994 AND r2.status = 'A'
1995 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
1996 handle non-directional relationship e.g. PARTNER_OF
1997 */
1998 AND r2.directional_flag = NVL(p_direction_code, r2.directional_flag)
1999 );
2000
2001 -- R12
2002 CURSOR client_info_csr(l_terr_id NUMBER) IS select org_id from jtf_terr_all where terr_id = l_terr_id;
2003
2004 BEGIN
2005 Ozf_Utility_pvt.write_conc_log(l_full_name || ': Start ');
2006 SAVEPOINT LOAD_PARTY_MARKET_QUALIFIER;
2007 -- mo_global.init('JTF');
2008 -- mo_global.set_policy_context('M',null);
2009 errbuf := null;
2010 retcode := 0;
2011 /* yzhao: 08/07/2002 fix bug 2503141 performance issue. Use index on relationship_type */
2012 l_direction_code := NULL;
2013 l_rel_profile := fnd_profile.VALUE('OZF_PARTY_RELATIONS_TYPE');
2014 OPEN c_get_relationship_type(l_rel_profile);
2015 FETCH c_get_relationship_type INTO l_rel_type, l_direction_code;
2016 CLOSE c_get_relationship_type;
2017 IF p_bg_id IS NOT NULL THEN
2018 IF (l_direction_code = 'N') THEN
2019 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
2020 handle non-directional relationship e.g. PARTNER_OF, so search forward and backward relationship
2021 */
2022 generate_party_for_buyinggroup(p_errbuf => errbuf,
2023 p_retcode => retcode,
2024 p_bg_id => p_bg_id,
2025 p_direction => 'F',
2026 p_obj_list => l_obj_list);
2027 generate_party_for_buyinggroup(p_errbuf => errbuf,
2028 p_retcode => retcode,
2029 p_bg_id => p_bg_id,
2030 p_direction => 'B',
2031 p_obj_list => l_obj_list);
2032 ELSE
2033 generate_party_for_buyinggroup(p_errbuf => errbuf,
2034 p_retcode => retcode,
2035 p_bg_id => p_bg_id,
2036 p_direction => NULL,
2037 p_obj_list => l_obj_list);
2038 END IF;
2039 ELSE
2040 -- no buying group id parameter means generate party pair list for all buying groups
2041
2042 -- Bug 5174046..perf issue. This delete is using lot of time
2043 -- There is a delete from each Buying Group Id inside generate_party_for_buyinggroup
2044
2045 -- DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2046 -- WHERE market_qualifier_type = 'BG';
2047
2048 IF (l_direction_code = 'N') THEN
2049 /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
2050 handle non-directional relationship e.g. PARTNER_OF, so search forward and backward relationship
2051 */
2052 l_direction_code := 'F';
2053 OPEN c_get_all_bgroots(l_rel_profile, l_rel_type, l_direction_code);
2054 LOOP
2055 FETCH c_get_all_bgroots INTO l_bg_id;
2056 EXIT WHEN c_get_all_bgroots%NOTFOUND;
2057 generate_party_for_buyinggroup(p_errbuf => errbuf,
2058 p_retcode => retcode,
2059 p_bg_id => l_bg_id,
2060 p_direction => l_direction_code,
2061 p_obj_list => l_obj_list);
2062 -- dbms_output.put_line('root: id= ' || l_bg_id || ' forward count=' || l_obj_list.count);
2063 END LOOP;
2064 CLOSE c_get_all_bgroots;
2065 l_direction_code := 'B';
2066 OPEN c_get_all_bgroots(l_rel_profile, l_rel_type, l_direction_code);
2067 LOOP
2068 FETCH c_get_all_bgroots INTO l_bg_id;
2069 EXIT WHEN c_get_all_bgroots%NOTFOUND;
2070 generate_party_for_buyinggroup(p_errbuf => errbuf,
2071 p_retcode => retcode,
2072 p_bg_id => l_bg_id,
2073 p_direction => l_direction_code,
2074 p_obj_list => l_obj_list);
2075 -- dbms_output.put_line('root: id= ' || l_bg_id || ' backward count=' || l_obj_list.count);
2076 END LOOP;
2077 CLOSE c_get_all_bgroots;
2078 ELSE
2079 l_direction_code := NULL;
2080 OPEN c_get_all_bgroots(l_rel_profile, l_rel_type, l_direction_code);
2081 LOOP
2082 FETCH c_get_all_bgroots INTO l_bg_id;
2083 EXIT WHEN c_get_all_bgroots%NOTFOUND;
2084 generate_party_for_buyinggroup(p_errbuf => errbuf,
2085 p_retcode => retcode,
2086 p_bg_id => l_bg_id,
2087 p_direction => l_direction_code,
2088 p_obj_list => l_obj_list);
2089 END LOOP;
2090 CLOSE c_get_all_bgroots;
2091 END IF;
2092 END IF;
2093 IF p_terr_id IS NOT NULL THEN
2094 -- R12 - Added
2095 OPEN client_info_csr(p_terr_id);
2096 FETCH client_info_csr into l_org_id;
2097 CLOSE client_info_csr;
2098 Ozf_Utility_pvt.write_conc_log(l_full_name||': Org for territory ' ||p_terr_id||' is => '||l_org_id);
2099 mo_global.init('JTF');
2100 mo_global.set_policy_context('S',l_org_id);
2101 generate_party_for_territory(errbuf, retcode, p_terr_id, 'Y', l_bind_vars);
2102
2103 -- mkothari - sep-09-2005 --R12
2104 -- adjust the account targets if any accounts have moved
2105 ---Ozf_Utility_pvt.write_conc_log(l_full_name || ': Adjusting Account Targets for Territory = '
2106 --- || p_terr_id || ' ... ');
2107 --OZF_ALLOCATION_ENGINE_PVT.adjust_account_targets(retcode, errbuf, p_terr_id);
2108
2109 ELSE
2110 -- mkothari - sep-09-2005 --R12
2111 -- Dump old terr definition into global temp table so that
2112 -- code can identify that account is moved from where to where.
2113 /*
2114 DELETE FROM OZF_PARTY_MARKET_SEGMENTS_T
2115 WHERE market_qualifier_type = 'TERRITORY';
2116 */
2117
2118 /* --this give GSCC error - "select * not allowed"
2119 INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
2120 SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
2121 */
2122
2123 /*
2124 INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
2125 AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2126 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2127 OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
2128 PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
2129 ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
2130 PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
2131 MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
2132 CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
2133 ROLLUP_PARTY_ID, SITE_USE_ID)
2134 SELECT AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2135 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2136 OBJECT_VERSION_NUMBER, MARKET_SEGMENT_ID, MARKET_SEGMENT_FLAG,
2137 PARTY_ID, START_DATE_ACTIVE, END_DATE_ACTIVE,
2138 ORG_ID, SECURITY_GROUP_ID, PROGRAM_APPLICATION_ID,
2139 PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
2140 MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
2141 CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
2142 ROLLUP_PARTY_ID, SITE_USE_ID
2143 FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
2144 WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
2145 AND OLD_TERR.site_use_code = 'SHIP_TO'
2146 AND OLD_TERR.party_id IS NOT NULL
2147 AND OLD_TERR.site_use_id IS NOT NULL;
2148 */
2149
2150 -- no territory id parameter means generate party list for all territories
2151 ---R12: DO NOT DELETE BEFORE COPYING into TEMP Table
2152 --DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2153 --WHERE market_qualifier_type = 'TERRITORY';
2154 OPEN c_get_all_territories;
2155 LOOP
2156 FETCH c_get_all_territories INTO l_terr_id;
2157 EXIT WHEN c_get_all_territories%NOTFOUND;
2158 -- R12 - Added
2159 OPEN client_info_csr(l_terr_id);
2160 FETCH client_info_csr into l_org_id;
2161 CLOSE client_info_csr;
2162 Ozf_Utility_pvt.write_conc_log(l_full_name||': Org for territory ' ||l_terr_id||' is => '||l_org_id);
2163 mo_global.init('JTF');
2164 mo_global.set_policy_context('S',l_org_id);
2165 generate_party_for_territory(errbuf, retcode, l_terr_id, 'N', l_bind_vars);
2166 END LOOP;
2167 CLOSE c_get_all_territories;
2168
2169
2170 -- mkothari - sep-09-2005 --R12
2171 -- adjust the account targets if any accounts have moved
2172 ---Ozf_Utility_pvt.write_conc_log(l_full_name || ': Adjusting Account Targets ... ');
2173 --OZF_ALLOCATION_ENGINE_PVT.adjust_account_targets(retcode, errbuf, NULL);
2174
2175 END IF;
2176 Ozf_Utility_pvt.write_conc_log;
2177 COMMIT;
2178 EXCEPTION
2179 WHEN OTHERS THEN
2180 ROLLBACK TO LOAD_PARTY_MARKET_QUALIFIER;
2181 retcode := 1;
2182 Ozf_Utility_pvt.write_conc_log(l_full_name || ': Exception ' || sqlerrm);
2183 Ozf_Utility_pvt.write_conc_log;
2184 END LOAD_PARTY_MARKET_QUALIFIER;
2185 END OZF_Party_Mkt_Seg_Loader_PVT;