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