[Home] [Help]
PACKAGE BODY: APPS.OZF_QP_QUAL_PVT
Source
1 PACKAGE BODY OZF_QP_QUAL_PVT as
2 /* $Header: ozfvqpqb.pls 120.6 2006/06/08 01:15:03 julou noship $ */
3 --
4 -- NAME
5 -- OZF_QP_QUAL_PVT
6 --
7 -- HISTORY
8 -- 11/19/1999 ptendulk Created
9 -- 05-DEC-2002 julou 1. sql performance fix
10 -- 11-Feb-2003 RSSHARMA Fixed bug # 2794205.
11 -- IN Function get_buying_group call procedure get_all_parents only
12 -- if the passed in aso_party_id has a parent
13 -- 03-MAR-2003 julou changed p_use_type from 'TERRITORY' to 'RESOURCE'
14 -- 18-Apr-2003 julou bug 2906198 - Find_TM_Territories, Find_SA_Territories
15 -- 1. moved return statement out of 'IF' condition
16 -- 2. replaced first..last with 1..count in loop
17 -- 03-Apr-2006 gramanat moved OZF_PARTY_RELATIONS_TYPE to global space
18 ------------------------------------------------------------------------------
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):='OZF_QP_QUAL_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12):='ozfvqpqb.pls';
22 g_bg_tbl qp_attr_mapping_pub.t_multirecord;
23 g_total NUMBER := 1 ;
24 g_rel_type VARCHAR2(30) := FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE');
25
26
27 FUNCTION check_exists(p_party_id NUMBER)
28 RETURN boolean IS
29 dupfound boolean := false;
30
31 BEGIN
32 FOR i IN 1.. g_bg_tbl.COUNT LOOP
33 if g_bg_tbl(i) = p_party_id then
34 dupfound := true;
35 exit;
36 end if;
37 END LOOP;
38 return dupfound;
39 END;
40
41
42 --------------- start of comments --------------------------
43 -- NAME
44 -- get_all_parents
45 --
46 -- USAGE
47 -- Procedure will do recursive job to find all parents for each party
48 -- NOTES
49 --
50 -- HISTORY
51 -- 11/07/2001 jieli created
52 -- End of Comments
53 --
54 --------------- end of comments ----------------------------
55
56 PROCEDURE get_all_parents(aso_party_id IN NUMBER,
57 om_sold_to_org IN NUMBER,
58 px_bg_tbl IN OUT NOCOPY qp_attr_mapping_pub.t_multirecord)
59 IS
60
61 p_party_id NUMBER;
62 l_party_id NUMBER;
63
64 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
65 SELECT party_id
66 FROM hz_cust_accounts
67 WHERE cust_account_id = p_sold_to_org;
68
69 /*
70 CURSOR c_bg IS
71 select object_id
72 from hz_party_relationships
73 where party_relationship_type = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
74 and subject_id=p_party_id;
75 */
76
77 CURSOR c_bg IS
78 SELECT r1.object_id
79 FROM hz_relationships r1
80 WHERE r1.relationship_code = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
81 AND r1.subject_type = 'ORGANIZATION'
82 AND r1.subject_table_name = 'HZ_PARTIES'
83 AND r1.object_type = 'ORGANIZATION'
84 AND r1.object_table_name = 'HZ_PARTIES'
85 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
86 AND r1.status = 'A'
87 AND r1.subject_id=p_party_id;
88
89 BEGIN
90
91 IF aso_party_id IS NULL OR aso_party_id = FND_API.g_miss_num THEN
92 OPEN cur_get_party_id(om_sold_to_org);
93 FETCH cur_get_party_id into p_party_id;
94 CLOSE cur_get_party_id;
95 ELSE
96 p_party_id := aso_party_id;
97 END IF;
98
99 open c_bg;
100 LOOP
101 fetch c_bg into l_party_id;
102 IF c_bg%notfound then
103 if check_exists(p_party_id) = false then
104 g_bg_tbl(g_total) := p_party_id;
105 g_total := g_total + 1;
106 px_bg_tbl := g_bg_tbl;
107 end if;
108 close c_bg;
109 exit;
110 else
111 get_all_parents(l_party_id,'',g_bg_tbl);
112 end if;
113 END LOOP;
114
115 END get_all_parents;
116
117 --------------- start of comments --------------------------
118 -- NAME
119 -- get_buying_groups
120 --
121 -- USAGE
122 -- Function will return all the buying groups
123 -- to which the Customer belongs
124 -- NOTES
125 --
126 -- HISTORY
127 -- 11/07/2001 jieli created
128 -- End of Comments
129 --
130 --------------- end of comments ----------------------------
131
132 FUNCTION get_buying_groups(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER)
133 RETURN qp_attr_mapping_pub.t_MultiRecord IS
134 l_bg_tbl qp_attr_mapping_pub.t_multirecord ;
135
136 p_party_id NUMBER;
137 l_party_id NUMBER;
138
139 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
140 SELECT party_id
141 FROM hz_cust_accounts
142 WHERE cust_account_id = p_sold_to_org;
143
144 /*
145 CURSOR c_bg IS
146 select object_id
147 from hz_party_relationships
148 where party_relationship_type = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
149 and subject_id=p_party_id;
150 */
151
152 CURSOR c_bg IS
153 SELECT r1.object_id
154 FROM hz_relationships r1
155 WHERE r1.relationship_code = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
156 AND r1.subject_type = 'ORGANIZATION'
157 AND r1.subject_table_name = 'HZ_PARTIES'
158 AND r1.object_type = 'ORGANIZATION'
159 AND r1.object_table_name = 'HZ_PARTIES'
160 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
161 AND r1.status = 'A'
162 AND r1.subject_id=p_party_id;
163
164 BEGIN
165 g_bg_tbl.delete;
166 g_total := 1;
167
168 IF aso_party_id IS NULL OR aso_party_id = FND_API.g_miss_num THEN
169 OPEN cur_get_party_id(om_sold_to_org);
170 FETCH cur_get_party_id into p_party_id;
171 CLOSE cur_get_party_id;
172 ELSE
173 p_party_id := aso_party_id;
174 END IF;
175
176 open c_bg;
177 fetch c_bg into l_party_id;
178 IF c_bg%notfound then
179 close c_bg;
180 -- l_bg_tbl(g_total) := aso_party_id;
181 l_bg_tbl(g_total) := p_party_id;
182 -- return l_bg_tbl;
183 ELSE
184 close c_bg;
185 get_all_parents(p_party_id,om_sold_to_org, l_bg_tbl);
186 END IF;
187
188 --get_all_parents(aso_party_id,om_sold_to_org, l_bg_tbl);
189 return l_bg_tbl;
190 END;
191
192 --------------- start of comments --------------------------
193 -- NAME
194 -- get_buying_groups
195 --
196 -- USAGE
197 -- overload function to handle buying group in indirect sales
198 -- NOTES
199 --
200 -- HISTORY
201 -- 26-FEB-2004 julou created.
202 -- End of Comments
203 --
204 --------------- end of comments ----------------------------
205 FUNCTION get_buying_groups(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER,ic_party_id IN NUMBER)
206 RETURN qp_attr_mapping_pub.t_MultiRecord IS
207 l_bg_tbl qp_attr_mapping_pub.t_multirecord ;
208
209 p_party_id NUMBER;
210 l_party_id NUMBER;
211
212 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
213 SELECT party_id
214 FROM hz_cust_accounts
215 WHERE cust_account_id = p_sold_to_org;
216
217 CURSOR c_bg IS
218 SELECT r1.object_id
219 FROM hz_relationships r1
220 WHERE r1.relationship_code = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
221 AND r1.subject_type = 'ORGANIZATION'
222 AND r1.subject_table_name = 'HZ_PARTIES'
223 AND r1.object_type = 'ORGANIZATION'
224 AND r1.object_table_name = 'HZ_PARTIES'
225 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
226 AND r1.status = 'A'
227 AND r1.subject_id=p_party_id;
228
229 BEGIN
230 g_bg_tbl.DELETE;
231 g_total := 1;
232
233 IF aso_party_id IS NULL OR aso_party_id = FND_API.g_miss_num THEN
234 IF om_sold_to_org IS NULL OR om_sold_to_org = FND_API.g_miss_num THEN
235 p_party_id := ic_party_id;
236 ELSE
237 OPEN cur_get_party_id(om_sold_to_org);
238 FETCH cur_get_party_id into p_party_id;
239 CLOSE cur_get_party_id;
240 END IF;
241 ELSE
242 p_party_id := aso_party_id;
243 END IF;
244
245 OPEN c_bg;
246 FETCH c_bg INTO l_party_id;
247
248 IF c_bg%NOTFOUND THEN
249 CLOSE c_bg;
250 l_bg_tbl(g_total) := p_party_id;
251 ELSE
252 CLOSE c_bg;
253 get_all_parents(p_party_id,om_sold_to_org, l_bg_tbl);
254 END IF;
255
256 RETURN l_bg_tbl;
257 END;
258
259 --------------- start of comments --------------------------
260 -- NAME
261 -- get_market_segment
262 --
263 -- USAGE
264 -- Function will return all the Market Segments
265 -- to which the Customer belongs
266 -- NOTES
267 --
268 -- HISTORY
269 -- 01/12/2000 ptendulk created
270 -- 06/12/2000 skarumur modified
271 -- Changed the return types for the functions
272 -- should return segment names instead of ID's
273 -- Using qp's return structure
274 -- End of Comments
275 --
276 --------------- end of comments ----------------------------
277 FUNCTION get_segments(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER)
278 RETURN qp_attr_mapping_pub.t_MultiRecord IS
279
280 l_mks_tbl qp_attr_mapping_pub.t_multirecord ;
281
282 p_party_id number;
283
284 CURSOR c_mks IS
285 SELECT pms.market_segment_id
286 FROM ams_party_market_segments pms
287 WHERE pms.party_id = p_party_id
288 AND pms.market_qualifier_type IS NULL;
289
290 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
291 SELECT party_id
292 FROM hz_cust_accounts
293 WHERE cust_account_id = p_sold_to_org;
294
295 l_total NUMBER := 1 ;
296
297 BEGIN
298
299 IF aso_party_id = FND_API.g_miss_num THEN
300 OPEN cur_get_party_id(om_sold_to_org);
301 FETCH cur_get_party_id into p_party_id;
302 CLOSE cur_get_party_id;
303 ELSE
304 p_party_id := aso_party_id;
305 END IF;
306
307 FOR mks_rec in c_mks LOOP
308 l_mks_tbl(l_total) := mks_rec.market_segment_id;
309 l_total := l_total + 1 ;
310 END LOOP;
311 return l_mks_tbl ;
312 END get_segments ;
313
314 --------------- start of comments --------------------------
315 -- NAME
316 -- get_lists
317
318 -- USAGE
319 -- Function will return all the Target Segments
320 -- to which the Customer belongs
321 -- NOTES
322 --
323 -- HISTORY
324 --- julou Created
325 --
326 --------------- end of comments ----------------------------
327 FUNCTION get_lists(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER)
328 RETURN qp_attr_mapping_pub.t_MultiRecord IS
329
330 l_tgt_tbl qp_attr_mapping_pub.t_multirecord ;
331
332 p_party_id number;
333
334 CURSOR c_mks IS
335 SELECT list_header_id
336 FROM ams_list_entries
337 WHERE enabled_flag = 'Y'
338 AND party_id = p_party_id;
339
340 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
341 SELECT party_id
342 FROM hz_cust_accounts
343 WHERE cust_account_id = p_sold_to_org;
344
345 l_total NUMBER := 1 ;
346
347 BEGIN
348
349 IF aso_party_id = FND_API.g_miss_num THEN
350 OPEN cur_get_party_id(om_sold_to_org);
351 FETCH cur_get_party_id into p_party_id;
352 CLOSE cur_get_party_id;
353 ELSE
354 p_party_id := aso_party_id;
355 END IF;
356
357 FOR mks_rec in c_mks LOOP
358 l_tgt_tbl(l_total) := mks_rec.list_header_id;
359 l_total := l_total + 1;
360 END LOOP;
361 return l_tgt_tbl ;
362
363 END get_lists ;
364
365
366 --------------- start of comments --------------------------
367 -- NAME
368 -- Find_TM_Territories
369 --
370 -- USAGE
371 -- Function will return the winning territories ID
372 -- for trade management
373 -- NOTES
374 --
375 -- HISTORY
376 -- 28-OCT-2001 julou created
377 -- End of Comments
378 --
379 --------------- end of comments ----------------------------
380 FUNCTION Find_TM_Territories
381 (
382 p_party_id IN NUMBER
383 ,p_sold_to_org IN NUMBER
384 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
385 IS
386
387 l_api_version CONSTANT NUMBER := 1.0;
388 l_api_name CONSTANT VARCHAR2(30) := 'Find_TM_Territories';
389 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
390
391 CURSOR cur_get_party_info(p_party_id NUMBER) IS
392 SELECT city,postal_code,state,county,country,party_id
393 FROM hz_parties
394 WHERE party_id = p_party_id;
395
396 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
397 SELECT party_id
398 FROM hz_cust_accounts
399 WHERE cust_account_id = p_sold_to_org;
400
401 CURSOR cur_is_terr_setup IS
402 SELECT /*+ ORDERED */ -- julou sql performance fix
403 count(*)
404 FROM jtf_terr_all jt, jtf_terr_qtype_usgs_all jtqu, jtf_qual_type_usgs jqtu
405 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
406 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
407 jt.end_date_active IS NULL ))
408 AND jt.terr_id = jtqu.terr_id
409 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
410 AND jqtu.source_id = -1003
411 AND jqtu.qual_type_id = -1007;
412
413 l_party_id NUMBER;
414 l_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
415 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
416 l_terr_name VARCHAR2(120);
417 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
418
419 l_return_status VARCHAR2(1);
420 l_msg_data VARCHAR2(2000);
421 l_msg_count NUMBER;
422 l_count NUMBER;
423
424 BEGIN
425 -- initializing
426 --apps.FND_MSG_PUB.initialize;
427 l_return_status := Fnd_Api.g_ret_sts_success;
428
429 -- territory rec
430 l_trans_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
431 l_trans_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
432
433 -- extend qualifier elements
434 l_trans_rec.SQUAL_NUM01.EXTEND;
435 l_trans_rec.SQUAL_NUM02.EXTEND;
436 l_trans_rec.SQUAL_NUM03.EXTEND;
437 l_trans_rec.SQUAL_NUM04.EXTEND;
438 l_trans_rec.SQUAL_NUM05.EXTEND;
439 l_trans_rec.SQUAL_NUM06.EXTEND;
440 l_trans_rec.SQUAL_NUM07.EXTEND;
441 l_trans_rec.SQUAL_NUM08.EXTEND;
442 l_trans_rec.SQUAL_NUM09.EXTEND;
443 l_trans_rec.SQUAL_NUM10.EXTEND;
444 l_trans_rec.SQUAL_NUM11.EXTEND;
445 l_trans_rec.SQUAL_NUM12.EXTEND;
446 l_trans_rec.SQUAL_NUM13.EXTEND;
447 l_trans_rec.SQUAL_NUM14.EXTEND;
448 l_trans_rec.SQUAL_NUM15.EXTEND;
449 l_trans_rec.SQUAL_NUM16.EXTEND;
450 l_trans_rec.SQUAL_NUM17.EXTEND;
451 l_trans_rec.SQUAL_NUM18.EXTEND;
452 l_trans_rec.SQUAL_NUM19.EXTEND;
453 l_trans_rec.SQUAL_NUM20.EXTEND;
454 l_trans_rec.SQUAL_NUM21.EXTEND;
455 l_trans_rec.SQUAL_NUM22.EXTEND;
456 l_trans_rec.SQUAL_NUM23.EXTEND;
457 l_trans_rec.SQUAL_NUM24.EXTEND;
458 l_trans_rec.SQUAL_NUM25.EXTEND;
459 l_trans_rec.SQUAL_NUM26.EXTEND;
460 l_trans_rec.SQUAL_NUM27.EXTEND;
461 l_trans_rec.SQUAL_NUM28.EXTEND;
462 l_trans_rec.SQUAL_NUM29.EXTEND;
463 l_trans_rec.SQUAL_NUM30.EXTEND;
464 l_trans_rec.SQUAL_NUM31.EXTEND;
465 l_trans_rec.SQUAL_NUM32.EXTEND;
466 l_trans_rec.SQUAL_NUM33.EXTEND;
467 l_trans_rec.SQUAL_NUM34.EXTEND;
468 l_trans_rec.SQUAL_NUM35.EXTEND;
469 l_trans_rec.SQUAL_NUM36.EXTEND;
470 l_trans_rec.SQUAL_NUM37.EXTEND;
471 l_trans_rec.SQUAL_NUM38.EXTEND;
472 l_trans_rec.SQUAL_NUM39.EXTEND;
473 l_trans_rec.SQUAL_NUM40.EXTEND;
474 l_trans_rec.SQUAL_NUM41.EXTEND;
475 l_trans_rec.SQUAL_NUM42.EXTEND;
476 l_trans_rec.SQUAL_NUM43.EXTEND;
477 l_trans_rec.SQUAL_NUM44.EXTEND;
478 l_trans_rec.SQUAL_NUM45.EXTEND;
479 l_trans_rec.SQUAL_NUM46.EXTEND;
480 l_trans_rec.SQUAL_NUM47.EXTEND;
481 l_trans_rec.SQUAL_NUM48.EXTEND;
482 l_trans_rec.SQUAL_NUM49.EXTEND;
483 l_trans_rec.SQUAL_NUM50.EXTEND;
484
485 l_trans_rec.SQUAL_CHAR01.EXTEND;
486 l_trans_rec.SQUAL_CHAR02.EXTEND;
487 l_trans_rec.SQUAL_CHAR03.EXTEND;
488 l_trans_rec.SQUAL_CHAR04.EXTEND;
489 l_trans_rec.SQUAL_CHAR05.EXTEND;
490 l_trans_rec.SQUAL_CHAR06.EXTEND;
491 l_trans_rec.SQUAL_CHAR07.EXTEND;
492 l_trans_rec.SQUAL_CHAR08.EXTEND;
493 l_trans_rec.SQUAL_CHAR09.EXTEND;
494 l_trans_rec.SQUAL_CHAR10.EXTEND;
495 l_trans_rec.SQUAL_CHAR11.EXTEND;
496 l_trans_rec.SQUAL_CHAR12.EXTEND;
497 l_trans_rec.SQUAL_CHAR13.EXTEND;
498 l_trans_rec.SQUAL_CHAR14.EXTEND;
499 l_trans_rec.SQUAL_CHAR15.EXTEND;
500 l_trans_rec.SQUAL_CHAR16.EXTEND;
501 l_trans_rec.SQUAL_CHAR17.EXTEND;
502 l_trans_rec.SQUAL_CHAR18.EXTEND;
503 l_trans_rec.SQUAL_CHAR19.EXTEND;
504 l_trans_rec.SQUAL_CHAR20.EXTEND;
505 l_trans_rec.SQUAL_CHAR21.EXTEND;
506 l_trans_rec.SQUAL_CHAR22.EXTEND;
507 l_trans_rec.SQUAL_CHAR23.EXTEND;
508 l_trans_rec.SQUAL_CHAR24.EXTEND;
509 l_trans_rec.SQUAL_CHAR25.EXTEND;
510 l_trans_rec.SQUAL_CHAR26.EXTEND;
511 l_trans_rec.SQUAL_CHAR27.EXTEND;
512 l_trans_rec.SQUAL_CHAR28.EXTEND;
513 l_trans_rec.SQUAL_CHAR29.EXTEND;
514 l_trans_rec.SQUAL_CHAR30.EXTEND;
515 l_trans_rec.SQUAL_CHAR31.EXTEND;
516 l_trans_rec.SQUAL_CHAR32.EXTEND;
517 l_trans_rec.SQUAL_CHAR33.EXTEND;
518 l_trans_rec.SQUAL_CHAR34.EXTEND;
519 l_trans_rec.SQUAL_CHAR35.EXTEND;
520 l_trans_rec.SQUAL_CHAR36.EXTEND;
521 l_trans_rec.SQUAL_CHAR37.EXTEND;
522 l_trans_rec.SQUAL_CHAR38.EXTEND;
523 l_trans_rec.SQUAL_CHAR39.EXTEND;
524 l_trans_rec.SQUAL_CHAR40.EXTEND;
525 l_trans_rec.SQUAL_CHAR41.EXTEND;
526 l_trans_rec.SQUAL_CHAR42.EXTEND;
527 l_trans_rec.SQUAL_CHAR43.EXTEND;
528 l_trans_rec.SQUAL_CHAR44.EXTEND;
529 l_trans_rec.SQUAL_CHAR45.EXTEND;
530 l_trans_rec.SQUAL_CHAR46.EXTEND;
531 l_trans_rec.SQUAL_CHAR47.EXTEND;
532 l_trans_rec.SQUAL_CHAR48.EXTEND;
533 l_trans_rec.SQUAL_CHAR49.EXTEND;
534 l_trans_rec.SQUAL_CHAR50.EXTEND;
535
536 -- transaction qualifier values
537 l_trans_rec.SQUAL_NUM01(1) := null;
538 l_trans_rec.SQUAL_NUM02(1) := null;
539 l_trans_rec.SQUAL_NUM03(1) := null;
540 l_trans_rec.SQUAL_NUM04(1) := null;
541 l_trans_rec.SQUAL_NUM05(1) := null;
542 l_trans_rec.SQUAL_NUM06(1) := null;
543 l_trans_rec.SQUAL_NUM07(1) := null;
544 l_trans_rec.SQUAL_NUM08(1) := null;
545 l_trans_rec.SQUAL_NUM09(1) := null;
546 l_trans_rec.SQUAL_NUM10(1) := null;
547 l_trans_rec.SQUAL_NUM11(1) := null;
548 l_trans_rec.SQUAL_NUM12(1) := null;
549 l_trans_rec.SQUAL_NUM13(1) := null;
550 l_trans_rec.SQUAL_NUM14(1) := null;
551 l_trans_rec.SQUAL_NUM15(1) := null;
552 l_trans_rec.SQUAL_NUM16(1) := null;
553 l_trans_rec.SQUAL_NUM17(1) := null;
554 l_trans_rec.SQUAL_NUM18(1) := null;
555 l_trans_rec.SQUAL_NUM19(1) := null;
556 l_trans_rec.SQUAL_NUM20(1) := null;
557 l_trans_rec.SQUAL_NUM21(1) := null;
558 l_trans_rec.SQUAL_NUM22(1) := null;
559 l_trans_rec.SQUAL_NUM23(1) := null;
560 l_trans_rec.SQUAL_NUM24(1) := null;
561 l_trans_rec.SQUAL_NUM25(1) := null;
562 l_trans_rec.SQUAL_NUM26(1) := null;
563 l_trans_rec.SQUAL_NUM27(1) := null;
564 l_trans_rec.SQUAL_NUM28(1) := null;
565 l_trans_rec.SQUAL_NUM29(1) := null;
566 l_trans_rec.SQUAL_NUM30(1) := null;
567 l_trans_rec.SQUAL_NUM31(1) := null;
568 l_trans_rec.SQUAL_NUM32(1) := null;
569 l_trans_rec.SQUAL_NUM33(1) := null;
570 l_trans_rec.SQUAL_NUM34(1) := null;
571 l_trans_rec.SQUAL_NUM35(1) := null;
572 l_trans_rec.SQUAL_NUM36(1) := null;
573 l_trans_rec.SQUAL_NUM37(1) := null;
574 l_trans_rec.SQUAL_NUM38(1) := null;
575 l_trans_rec.SQUAL_NUM39(1) := null;
576 l_trans_rec.SQUAL_NUM40(1) := null;
577 l_trans_rec.SQUAL_NUM41(1) := null;
578 l_trans_rec.SQUAL_NUM42(1) := null;
579 l_trans_rec.SQUAL_NUM43(1) := null;
580 l_trans_rec.SQUAL_NUM44(1) := null;
581 l_trans_rec.SQUAL_NUM45(1) := null;
582 l_trans_rec.SQUAL_NUM46(1) := null;
583 l_trans_rec.SQUAL_NUM47(1) := null;
584 l_trans_rec.SQUAL_NUM48(1) := null;
585 l_trans_rec.SQUAL_NUM49(1) := null;
586 l_trans_rec.SQUAL_NUM50(1) := null;
587
588 l_trans_rec.SQUAL_CHAR01(1) := null;
589 l_trans_rec.SQUAL_CHAR02(1) := null;
590 l_trans_rec.SQUAL_CHAR03(1) := null;
591 l_trans_rec.SQUAL_CHAR04(1) := null;
592 l_trans_rec.SQUAL_CHAR05(1) := null;
593 l_trans_rec.SQUAL_CHAR06(1) := null;
594 l_trans_rec.SQUAL_CHAR07(1) := null;
595 l_trans_rec.SQUAL_CHAR08(1) := null;
596 l_trans_rec.SQUAL_CHAR09(1) := null;
597 l_trans_rec.SQUAL_CHAR10(1) := null;
598 l_trans_rec.SQUAL_CHAR11(1) := null;
599 l_trans_rec.SQUAL_CHAR12(1) := null;
600 l_trans_rec.SQUAL_CHAR13(1) := null;
601 l_trans_rec.SQUAL_CHAR14(1) := null;
602 l_trans_rec.SQUAL_CHAR15(1) := null;
603 l_trans_rec.SQUAL_CHAR16(1) := null;
604 l_trans_rec.SQUAL_CHAR17(1) := null;
605 l_trans_rec.SQUAL_CHAR18(1) := null;
606 l_trans_rec.SQUAL_CHAR19(1) := null;
607 l_trans_rec.SQUAL_CHAR20(1) := null;
608 l_trans_rec.SQUAL_CHAR21(1) := null;
609 l_trans_rec.SQUAL_CHAR22(1) := null;
610 l_trans_rec.SQUAL_CHAR23(1) := null;
611 l_trans_rec.SQUAL_CHAR24(1) := null;
612 l_trans_rec.SQUAL_CHAR25(1) := null;
613 l_trans_rec.SQUAL_CHAR26(1) := null;
614 l_trans_rec.SQUAL_CHAR27(1) := null;
615 l_trans_rec.SQUAL_CHAR28(1) := null;
616 l_trans_rec.SQUAL_CHAR29(1) := null;
617 l_trans_rec.SQUAL_CHAR30(1) := null;
618 l_trans_rec.SQUAL_CHAR31(1) := null;
619 l_trans_rec.SQUAL_CHAR32(1) := null;
620 l_trans_rec.SQUAL_CHAR33(1) := null;
621 l_trans_rec.SQUAL_CHAR34(1) := null;
622 l_trans_rec.SQUAL_CHAR35(1) := null;
623 l_trans_rec.SQUAL_CHAR36(1) := null;
624 l_trans_rec.SQUAL_CHAR37(1) := null;
625 l_trans_rec.SQUAL_CHAR38(1) := null;
626 l_trans_rec.SQUAL_CHAR39(1) := null;
627 l_trans_rec.SQUAL_CHAR40(1) := null;
628 l_trans_rec.SQUAL_CHAR41(1) := null;
629 l_trans_rec.SQUAL_CHAR42(1) := null;
630 l_trans_rec.SQUAL_CHAR43(1) := null;
631 l_trans_rec.SQUAL_CHAR44(1) := null;
632 l_trans_rec.SQUAL_CHAR45(1) := null;
633 l_trans_rec.SQUAL_CHAR46(1) := null;
634 l_trans_rec.SQUAL_CHAR47(1) := null;
635 l_trans_rec.SQUAL_CHAR48(1) := null;
636 l_trans_rec.SQUAL_CHAR49(1) := null;
637 l_trans_rec.SQUAL_CHAR50(1) := null;
638
639 OPEN cur_is_terr_setup;
640 FETCH cur_is_terr_setup INTO l_count;
641 CLOSE cur_is_terr_setup;
642
643 IF l_count > 0 THEN
644
645 IF p_party_id = Fnd_Api.g_miss_num
646 OR p_party_id IS NULL THEN
647 OPEN cur_get_party_id(p_sold_to_org);
648 FETCH cur_get_party_id INTO l_party_id;
649 CLOSE cur_get_party_id;
650 ELSE
651 l_party_id := p_party_id;
652 END IF;
653
654 OPEN cur_get_party_info(l_party_id);
655 FETCH cur_get_party_info INTO l_trans_rec.SQUAL_CHAR02(1),l_trans_rec.SQUAL_CHAR06(1),l_trans_rec.SQUAL_CHAR04(1),l_trans_rec.SQUAL_CHAR03(1),l_trans_rec.SQUAL_CHAR07(1),l_trans_rec.SQUAL_NUM01(1);
656 CLOSE cur_get_party_info;
657
658 oe_debug_pub.add('Trade MGR: before calling get_winners: city ' || l_trans_rec.SQUAL_CHAR02(1));
659 oe_debug_pub.add('Trade MGR: before calling get_winners: zipcode ' || l_trans_rec.SQUAL_CHAR06(1));
660 oe_debug_pub.add('Trade MGR: before calling get_winners: state ' || l_trans_rec.SQUAL_CHAR04(1));
661 oe_debug_pub.add('Trade MGR: before calling get_winners: county ' || l_trans_rec.SQUAL_CHAR03(1));
662 oe_debug_pub.add('Trade MGR: before calling get_winners: country ' || l_trans_rec.SQUAL_CHAR07(1));
663 oe_debug_pub.add('Trade MGR: before calling get_winners: party_id ' || l_trans_rec.SQUAL_NUM01(1));
664
665 JTF_TERR_ASSIGN_PUB.get_winners
666 ( p_api_version_number => l_api_version,
667 p_init_msg_list => FND_API.G_TRUE,
668 p_use_type => 'RESOURCE',
669 p_source_id => -1003,
670 p_trans_id => -1007,
671 p_trans_rec => l_trans_rec,
672 p_resource_type => FND_API.G_MISS_CHAR,
673 p_role => FND_API.G_MISS_CHAR,
674 p_top_level_terr_id => FND_API.G_MISS_NUM,
675 p_num_winners => FND_API.G_MISS_NUM,
676 x_return_status => l_return_status,
677 x_msg_count => l_msg_count,
678 x_msg_data => l_msg_data,
679 x_winners_rec => l_winner_rec
680 );
681 oe_debug_pub.add('Trade MGR: after calling get_winners: status ' || l_return_status);
682 oe_debug_pub.add('Trade MGR: terr count ' || l_winner_rec.terr_id.COUNT);
683 END IF;
684
685 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
686 FOR i IN NVL(l_winner_rec.terr_id.FIRST, 1)..NVL(l_winner_rec.terr_id.LAST, 0) LOOP
687 l_multirecord(i) := l_winner_rec.terr_id(i);
688 oe_debug_pub.add('Trade MGR: terr_id(' || i || ') ' || l_multirecord(i));
689 END LOOP;
690 END IF;
691 RETURN l_multirecord;
692
693 END Find_TM_Territories;
694
695
696 --------------- start of comments --------------------------
697 -- NAME
698 -- Find_TM_Territories
699 --
700 -- USAGE
701 -- Overload function will return the winning territories ID
702 -- for trade management
703 -- NOTES
704 --
705 -- HISTORY
706 -- 28-OCT-2001 julou created
707 -- End of Comments
708 --
709 --------------- end of comments ----------------------------
710 FUNCTION Find_TM_Territories
711 (
712 p_party_id IN NUMBER
713 ,p_sold_to_org IN NUMBER
714 ,p_ship_to_org IN NUMBER
715 ,p_bill_to_org IN NUMBER
716 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
717 IS
718
719 l_api_version CONSTANT NUMBER := 1.0;
720 l_api_name CONSTANT VARCHAR2(30) := 'Find_TM_Territories';
721 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
722
723 CURSOR cur_get_party_info(p_party_id NUMBER) IS
724 SELECT city,postal_code,state,county,country,party_id,province,category_code
725 FROM hz_parties
726 WHERE party_id = p_party_id;
727
728 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
729 SELECT party_id
730 FROM hz_cust_accounts
731 WHERE cust_account_id = p_sold_to_org
732 AND status = 'A';
733
734 CURSOR c_sales_channel(p_cust_acct_id NUMBER) IS
735 SELECT sales_channel_code
736 FROM hz_cust_accounts
737 WHERE cust_account_id = p_cust_acct_id
738 and status = 'A';
739
740 CURSOR c_cust_profile(p_sold_to NUMBER, p_ship_to NUMBER, p_bill_to NUMBER) IS
741 SELECT profile_class_id
742 FROM hz_customer_profiles
743 WHERE cust_account_id = p_sold_to
744 AND status = 'A'
745 AND site_use_id IN (p_ship_to, p_bill_to);
746 /*
747 CURSOR c_site_use_code(p_site_use_id NUMBER) IS
748 SELECT site_use_code
749 FROM hz_cust_site_uses_all
750 WHERE site_use_id = p_site_use_id;
751
752 l_index NUMBER := 0;
753 */
754 CURSOR cur_is_terr_setup IS
755 SELECT /*+ ORDERED */ -- julou sql performance fix
756 count(*)
757 FROM jtf_terr jt, jtf_terr_qtype_usgs jtqu, jtf_qual_type_usgs jqtu
758 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
759 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
760 jt.end_date_active IS NULL ))
761 AND jt.terr_id = jtqu.terr_id
762 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
763 AND jqtu.source_id = -1003
764 AND jqtu.qual_type_id = -1007;
765
766 CURSOR c_party_site_id(p_ship_to_org_id NUMBER) IS
767 SELECT hzcasa.party_site_id
768 FROM hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa
769 WHERE hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id
770 AND hzcsua.site_use_id = p_ship_to_org_id;
771
772 l_party_id NUMBER;
773 l_trx_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
774 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
775 l_terr_name VARCHAR2(120);
776 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
777
778 l_return_status VARCHAR2(1);
779 l_msg_data VARCHAR2(2000);
780 l_msg_count NUMBER;
781 l_count NUMBER;
782 BEGIN
783 -- initializing
784 --apps.FND_MSG_PUB.initialize;
785 l_return_status := Fnd_Api.g_ret_sts_success;
786
787 -- territory rec
788 l_trx_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
789 l_trx_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
790
791 -- extend qualifier elements
792 l_trx_rec.SQUAL_NUM01.EXTEND;
793 l_trx_rec.SQUAL_NUM02.EXTEND;
794 l_trx_rec.SQUAL_NUM03.EXTEND;
795 l_trx_rec.SQUAL_NUM04.EXTEND;
796 l_trx_rec.SQUAL_NUM05.EXTEND;
797 l_trx_rec.SQUAL_NUM06.EXTEND;
798 l_trx_rec.SQUAL_NUM07.EXTEND;
799 l_trx_rec.SQUAL_NUM08.EXTEND;
800 l_trx_rec.SQUAL_NUM09.EXTEND;
801 l_trx_rec.SQUAL_NUM10.EXTEND;
802 l_trx_rec.SQUAL_NUM11.EXTEND;
803 l_trx_rec.SQUAL_NUM12.EXTEND;
804 l_trx_rec.SQUAL_NUM13.EXTEND;
805 l_trx_rec.SQUAL_NUM14.EXTEND;
806 l_trx_rec.SQUAL_NUM15.EXTEND;
807 l_trx_rec.SQUAL_NUM16.EXTEND;
808 l_trx_rec.SQUAL_NUM17.EXTEND;
809 l_trx_rec.SQUAL_NUM18.EXTEND;
810 l_trx_rec.SQUAL_NUM19.EXTEND;
811 l_trx_rec.SQUAL_NUM20.EXTEND;
812 l_trx_rec.SQUAL_NUM21.EXTEND;
813 l_trx_rec.SQUAL_NUM22.EXTEND;
814 l_trx_rec.SQUAL_NUM23.EXTEND;
815 l_trx_rec.SQUAL_NUM24.EXTEND;
816 l_trx_rec.SQUAL_NUM25.EXTEND;
817 l_trx_rec.SQUAL_NUM26.EXTEND;
818 l_trx_rec.SQUAL_NUM27.EXTEND;
819 l_trx_rec.SQUAL_NUM28.EXTEND;
820 l_trx_rec.SQUAL_NUM29.EXTEND;
821 l_trx_rec.SQUAL_NUM30.EXTEND;
822 l_trx_rec.SQUAL_NUM31.EXTEND;
823 l_trx_rec.SQUAL_NUM32.EXTEND;
824 l_trx_rec.SQUAL_NUM33.EXTEND;
825 l_trx_rec.SQUAL_NUM34.EXTEND;
826 l_trx_rec.SQUAL_NUM35.EXTEND;
827 l_trx_rec.SQUAL_NUM36.EXTEND;
828 l_trx_rec.SQUAL_NUM37.EXTEND;
829 l_trx_rec.SQUAL_NUM38.EXTEND;
830 l_trx_rec.SQUAL_NUM39.EXTEND;
831 l_trx_rec.SQUAL_NUM40.EXTEND;
832 l_trx_rec.SQUAL_NUM41.EXTEND;
833 l_trx_rec.SQUAL_NUM42.EXTEND;
834 l_trx_rec.SQUAL_NUM43.EXTEND;
835 l_trx_rec.SQUAL_NUM44.EXTEND;
836 l_trx_rec.SQUAL_NUM45.EXTEND;
837 l_trx_rec.SQUAL_NUM46.EXTEND;
838 l_trx_rec.SQUAL_NUM47.EXTEND;
839 l_trx_rec.SQUAL_NUM48.EXTEND;
840 l_trx_rec.SQUAL_NUM49.EXTEND;
841 l_trx_rec.SQUAL_NUM50.EXTEND;
842
843 l_trx_rec.SQUAL_CHAR01.EXTEND;
844 l_trx_rec.SQUAL_CHAR02.EXTEND;
845 l_trx_rec.SQUAL_CHAR03.EXTEND;
846 l_trx_rec.SQUAL_CHAR04.EXTEND;
847 l_trx_rec.SQUAL_CHAR05.EXTEND;
848 l_trx_rec.SQUAL_CHAR06.EXTEND;
849 l_trx_rec.SQUAL_CHAR07.EXTEND;
850 l_trx_rec.SQUAL_CHAR08.EXTEND;
851 l_trx_rec.SQUAL_CHAR09.EXTEND;
852 l_trx_rec.SQUAL_CHAR10.EXTEND;
853 l_trx_rec.SQUAL_CHAR11.EXTEND;
854 l_trx_rec.SQUAL_CHAR12.EXTEND;
855 l_trx_rec.SQUAL_CHAR13.EXTEND;
856 l_trx_rec.SQUAL_CHAR14.EXTEND;
857 l_trx_rec.SQUAL_CHAR15.EXTEND;
858 l_trx_rec.SQUAL_CHAR16.EXTEND;
859 l_trx_rec.SQUAL_CHAR17.EXTEND;
860 l_trx_rec.SQUAL_CHAR18.EXTEND;
861 l_trx_rec.SQUAL_CHAR19.EXTEND;
862 l_trx_rec.SQUAL_CHAR20.EXTEND;
863 l_trx_rec.SQUAL_CHAR21.EXTEND;
864 l_trx_rec.SQUAL_CHAR22.EXTEND;
865 l_trx_rec.SQUAL_CHAR23.EXTEND;
866 l_trx_rec.SQUAL_CHAR24.EXTEND;
867 l_trx_rec.SQUAL_CHAR25.EXTEND;
868 l_trx_rec.SQUAL_CHAR26.EXTEND;
869 l_trx_rec.SQUAL_CHAR27.EXTEND;
870 l_trx_rec.SQUAL_CHAR28.EXTEND;
871 l_trx_rec.SQUAL_CHAR29.EXTEND;
872 l_trx_rec.SQUAL_CHAR30.EXTEND;
873 l_trx_rec.SQUAL_CHAR31.EXTEND;
874 l_trx_rec.SQUAL_CHAR32.EXTEND;
875 l_trx_rec.SQUAL_CHAR33.EXTEND;
876 l_trx_rec.SQUAL_CHAR34.EXTEND;
877 l_trx_rec.SQUAL_CHAR35.EXTEND;
878 l_trx_rec.SQUAL_CHAR36.EXTEND;
879 l_trx_rec.SQUAL_CHAR37.EXTEND;
880 l_trx_rec.SQUAL_CHAR38.EXTEND;
881 l_trx_rec.SQUAL_CHAR39.EXTEND;
882 l_trx_rec.SQUAL_CHAR40.EXTEND;
883 l_trx_rec.SQUAL_CHAR41.EXTEND;
884 l_trx_rec.SQUAL_CHAR42.EXTEND;
885 l_trx_rec.SQUAL_CHAR43.EXTEND;
886 l_trx_rec.SQUAL_CHAR44.EXTEND;
887 l_trx_rec.SQUAL_CHAR45.EXTEND;
888 l_trx_rec.SQUAL_CHAR46.EXTEND;
889 l_trx_rec.SQUAL_CHAR47.EXTEND;
890 l_trx_rec.SQUAL_CHAR48.EXTEND;
891 l_trx_rec.SQUAL_CHAR49.EXTEND;
892 l_trx_rec.SQUAL_CHAR50.EXTEND;
893
894 -- transaction qualifier values
895 l_trx_rec.SQUAL_NUM01(1) := null;
896 l_trx_rec.SQUAL_NUM02(1) := null;
897 l_trx_rec.SQUAL_NUM03(1) := null;
898 l_trx_rec.SQUAL_NUM04(1) := null;
899 l_trx_rec.SQUAL_NUM05(1) := null;
900 l_trx_rec.SQUAL_NUM06(1) := null;
901 l_trx_rec.SQUAL_NUM07(1) := null;
902 l_trx_rec.SQUAL_NUM08(1) := null;
903 l_trx_rec.SQUAL_NUM09(1) := null;
904 l_trx_rec.SQUAL_NUM10(1) := null;
905 l_trx_rec.SQUAL_NUM11(1) := null;
906 l_trx_rec.SQUAL_NUM12(1) := null;
907 l_trx_rec.SQUAL_NUM13(1) := null;
908 l_trx_rec.SQUAL_NUM14(1) := null;
909 l_trx_rec.SQUAL_NUM15(1) := null;
910 l_trx_rec.SQUAL_NUM16(1) := null;
911 l_trx_rec.SQUAL_NUM17(1) := null;
912 l_trx_rec.SQUAL_NUM18(1) := null;
913 l_trx_rec.SQUAL_NUM19(1) := null;
914 l_trx_rec.SQUAL_NUM20(1) := null;
915 l_trx_rec.SQUAL_NUM21(1) := null;
916 l_trx_rec.SQUAL_NUM22(1) := null;
917 l_trx_rec.SQUAL_NUM23(1) := null;
918 l_trx_rec.SQUAL_NUM24(1) := null;
919 l_trx_rec.SQUAL_NUM25(1) := null;
920 l_trx_rec.SQUAL_NUM26(1) := null;
921 l_trx_rec.SQUAL_NUM27(1) := null;
922 l_trx_rec.SQUAL_NUM28(1) := null;
923 l_trx_rec.SQUAL_NUM29(1) := null;
924 l_trx_rec.SQUAL_NUM30(1) := null;
925 l_trx_rec.SQUAL_NUM31(1) := null;
926 l_trx_rec.SQUAL_NUM32(1) := null;
927 l_trx_rec.SQUAL_NUM33(1) := null;
928 l_trx_rec.SQUAL_NUM34(1) := null;
929 l_trx_rec.SQUAL_NUM35(1) := null;
930 l_trx_rec.SQUAL_NUM36(1) := null;
931 l_trx_rec.SQUAL_NUM37(1) := null;
932 l_trx_rec.SQUAL_NUM38(1) := null;
933 l_trx_rec.SQUAL_NUM39(1) := null;
934 l_trx_rec.SQUAL_NUM40(1) := null;
935 l_trx_rec.SQUAL_NUM41(1) := null;
936 l_trx_rec.SQUAL_NUM42(1) := null;
937 l_trx_rec.SQUAL_NUM43(1) := null;
938 l_trx_rec.SQUAL_NUM44(1) := null;
939 l_trx_rec.SQUAL_NUM45(1) := null;
940 l_trx_rec.SQUAL_NUM46(1) := null;
941 l_trx_rec.SQUAL_NUM47(1) := null;
942 l_trx_rec.SQUAL_NUM48(1) := null;
943 l_trx_rec.SQUAL_NUM49(1) := null;
944 l_trx_rec.SQUAL_NUM50(1) := null;
945
946 l_trx_rec.SQUAL_CHAR01(1) := null;
947 l_trx_rec.SQUAL_CHAR02(1) := null;
948 l_trx_rec.SQUAL_CHAR03(1) := null;
949 l_trx_rec.SQUAL_CHAR04(1) := null;
950 l_trx_rec.SQUAL_CHAR05(1) := null;
951 l_trx_rec.SQUAL_CHAR06(1) := null;
952 l_trx_rec.SQUAL_CHAR07(1) := null;
953 l_trx_rec.SQUAL_CHAR08(1) := null;
954 l_trx_rec.SQUAL_CHAR09(1) := null;
955 l_trx_rec.SQUAL_CHAR10(1) := null;
956 l_trx_rec.SQUAL_CHAR11(1) := null;
957 l_trx_rec.SQUAL_CHAR12(1) := null;
958 l_trx_rec.SQUAL_CHAR13(1) := null;
959 l_trx_rec.SQUAL_CHAR14(1) := null;
960 l_trx_rec.SQUAL_CHAR15(1) := null;
961 l_trx_rec.SQUAL_CHAR16(1) := null;
962 l_trx_rec.SQUAL_CHAR17(1) := null;
963 l_trx_rec.SQUAL_CHAR18(1) := null;
964 l_trx_rec.SQUAL_CHAR19(1) := null;
965 l_trx_rec.SQUAL_CHAR20(1) := null;
966 l_trx_rec.SQUAL_CHAR21(1) := null;
967 l_trx_rec.SQUAL_CHAR22(1) := null;
968 l_trx_rec.SQUAL_CHAR23(1) := null;
969 l_trx_rec.SQUAL_CHAR24(1) := null;
970 l_trx_rec.SQUAL_CHAR25(1) := null;
971 l_trx_rec.SQUAL_CHAR26(1) := null;
972 l_trx_rec.SQUAL_CHAR27(1) := null;
973 l_trx_rec.SQUAL_CHAR28(1) := null;
974 l_trx_rec.SQUAL_CHAR29(1) := null;
975 l_trx_rec.SQUAL_CHAR30(1) := null;
976 l_trx_rec.SQUAL_CHAR31(1) := null;
977 l_trx_rec.SQUAL_CHAR32(1) := null;
978 l_trx_rec.SQUAL_CHAR33(1) := null;
979 l_trx_rec.SQUAL_CHAR34(1) := null;
980 l_trx_rec.SQUAL_CHAR35(1) := null;
981 l_trx_rec.SQUAL_CHAR36(1) := null;
982 l_trx_rec.SQUAL_CHAR37(1) := null;
983 l_trx_rec.SQUAL_CHAR38(1) := null;
984 l_trx_rec.SQUAL_CHAR39(1) := null;
985 l_trx_rec.SQUAL_CHAR40(1) := null;
986 l_trx_rec.SQUAL_CHAR41(1) := null;
987 l_trx_rec.SQUAL_CHAR42(1) := null;
988 l_trx_rec.SQUAL_CHAR43(1) := null;
989 l_trx_rec.SQUAL_CHAR44(1) := null;
990 l_trx_rec.SQUAL_CHAR45(1) := null;
991 l_trx_rec.SQUAL_CHAR46(1) := null;
992 l_trx_rec.SQUAL_CHAR47(1) := null;
993 l_trx_rec.SQUAL_CHAR48(1) := null;
994 l_trx_rec.SQUAL_CHAR49(1) := null;
995 l_trx_rec.SQUAL_CHAR50(1) := null;
996
997 OPEN cur_is_terr_setup;
998 FETCH cur_is_terr_setup INTO l_count;
999 CLOSE cur_is_terr_setup;
1000
1001 IF l_count > 0 THEN
1002
1003 IF p_party_id = Fnd_Api.g_miss_num
1004 OR p_party_id IS NULL THEN
1005 OPEN cur_get_party_id(p_sold_to_org);
1006 FETCH cur_get_party_id INTO l_party_id;
1007 CLOSE cur_get_party_id;
1008 ELSE
1009 l_party_id := p_party_id;
1010 END IF;
1011
1012 OPEN cur_get_party_info(l_party_id);
1013 FETCH cur_get_party_info INTO l_trx_rec.SQUAL_CHAR02(1),l_trx_rec.SQUAL_CHAR06(1),l_trx_rec.SQUAL_CHAR04(1),l_trx_rec.SQUAL_CHAR03(1),l_trx_rec.SQUAL_CHAR07(1),l_trx_rec.SQUAL_NUM01(1),l_trx_rec.squal_char05(1),l_trx_rec.squal_char09(1);
1014 CLOSE cur_get_party_info;
1015
1016 OPEN c_party_site_id(p_ship_to_org);
1017 FETCH c_party_site_id INTO l_trx_rec.SQUAL_NUM02(1);
1018 CLOSE c_party_site_id;
1019
1020 OPEN c_sales_channel(p_sold_to_org);
1021 FETCH c_sales_channel INTO l_trx_rec.squal_char16(1);
1022 CLOSE c_sales_channel;
1023
1024 OPEN c_cust_profile(p_sold_to_org, p_ship_to_org, p_bill_to_org);
1025 FETCH c_cust_profile INTO l_trx_rec.squal_num15(1);
1026 CLOSE c_cust_profile;
1027 /*
1028 IF p_bill_to_org IS NOT NULL AND p_bill_to_org <> fnd_api.g_miss_num THEN
1029 l_index := l_index + 1;
1030 l_trx_rec.squal_char17.EXTEND;
1031 OPEN c_site_use_code(p_bill_to_org);
1032 FETCH c_site_use_code INTO l_trx_rec.squal_char17(l_index);
1033 CLOSE c_site_use_code;
1034 END IF;
1035
1036 IF p_ship_to_org IS NOT NULL AND p_ship_to_org <> fnd_api.g_miss_num THEN
1037 l_index := l_index + 1;
1038 l_trx_rec.squal_char17.EXTEND;
1039 OPEN c_site_use_code(p_ship_to_org);
1040 FETCH c_site_use_code INTO l_trx_rec.squal_char17(l_index);
1041 CLOSE c_site_use_code;
1042 END IF;
1043 */
1044 JTF_TERR_ASSIGN_PUB.get_winners
1045 ( p_api_version_number => l_api_version,
1046 p_init_msg_list => FND_API.G_TRUE,
1047 p_use_type => 'RESOURCE',
1048 p_source_id => -1003,
1049 p_trans_id => -1007,
1050 p_trans_rec => l_trx_rec,
1051 p_resource_type => FND_API.G_MISS_CHAR,
1052 p_role => FND_API.G_MISS_CHAR,
1053 p_top_level_terr_id => FND_API.G_MISS_NUM,
1054 p_num_winners => FND_API.G_MISS_NUM,
1055 x_return_status => l_return_status,
1056 x_msg_count => l_msg_count,
1057 x_msg_data => l_msg_data,
1058 x_winners_rec => l_winner_rec
1059 );
1060 END IF;
1061
1062 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
1063 FOR i IN 1..l_winner_rec.terr_id.COUNT LOOP
1064 l_multirecord(i) := l_winner_rec.terr_id(i);
1065 END LOOP;
1066 END IF;
1067 RETURN l_multirecord;
1068
1069 END Find_TM_Territories;
1070
1071
1072 --------------- start of comments --------------------------
1073 -- NAME
1074 -- Find_SA_Territories
1075 --
1076 -- USAGE
1077 -- Function will return the winning territories ID
1078 -- for sales account
1079 -- NOTES
1080 --
1081 -- HISTORY
1082 -- 28-OCT-2001 julou created
1083 -- End of Comments
1084 --
1085 --------------- end of comments ----------------------------
1086 FUNCTION Find_SA_Territories
1087 (
1088 p_party_id IN NUMBER
1089 ,p_sold_to_org IN NUMBER
1090 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
1091 IS
1092
1093 l_api_version CONSTANT NUMBER := 1.0;
1094 l_api_name CONSTANT VARCHAR2(30) := 'Find_SA_Territories';
1095 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1096
1097 CURSOR cur_get_party_info(p_party_id NUMBER) IS
1098 SELECT city,postal_code,state,county,country,party_id
1099 FROM hz_parties
1100 WHERE party_id = p_party_id;
1101
1102 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
1103 SELECT party_id
1104 FROM hz_cust_accounts
1105 WHERE cust_account_id = p_sold_to_org;
1106
1107 CURSOR cur_is_terr_setup IS
1108 SELECT /*+ ORDERED */ -- julou sql performance fix
1109 count(*)
1110 FROM jtf_terr_all jt, jtf_terr_qtype_usgs_all jtqu,jtf_qual_type_usgs jqtu
1111 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
1112 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
1113 jt.end_date_active IS NULL ))
1114 AND jt.terr_id = jtqu.terr_id
1115 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
1116 AND jqtu.source_id = -1001
1117 AND jqtu.qual_type_id = -1002;
1118
1119 l_party_id NUMBER;
1120 l_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
1121 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
1122 l_terr_name VARCHAR2(120);
1123 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1124
1125 l_return_status VARCHAR2(1);
1126 l_msg_data VARCHAR2(2000);
1127 l_msg_count NUMBER;
1128 l_count NUMBER;
1129
1130 BEGIN
1131 -- initializing
1132 --apps.FND_MSG_PUB.initialize;
1133 l_return_status := Fnd_Api.g_ret_sts_success;
1134
1135 -- territory rec
1136 l_trans_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
1137 l_trans_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
1138
1139 -- extend qualifier elements
1140 l_trans_rec.SQUAL_NUM01.EXTEND;
1141 l_trans_rec.SQUAL_NUM02.EXTEND;
1142 l_trans_rec.SQUAL_NUM03.EXTEND;
1143 l_trans_rec.SQUAL_NUM04.EXTEND;
1144 l_trans_rec.SQUAL_NUM05.EXTEND;
1145 l_trans_rec.SQUAL_NUM06.EXTEND;
1146 l_trans_rec.SQUAL_NUM07.EXTEND;
1147 l_trans_rec.SQUAL_NUM08.EXTEND;
1148 l_trans_rec.SQUAL_NUM09.EXTEND;
1149 l_trans_rec.SQUAL_NUM10.EXTEND;
1150 l_trans_rec.SQUAL_NUM11.EXTEND;
1151 l_trans_rec.SQUAL_NUM12.EXTEND;
1152 l_trans_rec.SQUAL_NUM13.EXTEND;
1153 l_trans_rec.SQUAL_NUM14.EXTEND;
1154 l_trans_rec.SQUAL_NUM15.EXTEND;
1155 l_trans_rec.SQUAL_NUM16.EXTEND;
1156 l_trans_rec.SQUAL_NUM17.EXTEND;
1157 l_trans_rec.SQUAL_NUM18.EXTEND;
1158 l_trans_rec.SQUAL_NUM19.EXTEND;
1159 l_trans_rec.SQUAL_NUM20.EXTEND;
1160 l_trans_rec.SQUAL_NUM21.EXTEND;
1161 l_trans_rec.SQUAL_NUM22.EXTEND;
1162 l_trans_rec.SQUAL_NUM23.EXTEND;
1163 l_trans_rec.SQUAL_NUM24.EXTEND;
1164 l_trans_rec.SQUAL_NUM25.EXTEND;
1165 l_trans_rec.SQUAL_NUM26.EXTEND;
1166 l_trans_rec.SQUAL_NUM27.EXTEND;
1167 l_trans_rec.SQUAL_NUM28.EXTEND;
1168 l_trans_rec.SQUAL_NUM29.EXTEND;
1169 l_trans_rec.SQUAL_NUM30.EXTEND;
1170 l_trans_rec.SQUAL_NUM31.EXTEND;
1171 l_trans_rec.SQUAL_NUM32.EXTEND;
1172 l_trans_rec.SQUAL_NUM33.EXTEND;
1173 l_trans_rec.SQUAL_NUM34.EXTEND;
1174 l_trans_rec.SQUAL_NUM35.EXTEND;
1175 l_trans_rec.SQUAL_NUM36.EXTEND;
1176 l_trans_rec.SQUAL_NUM37.EXTEND;
1177 l_trans_rec.SQUAL_NUM38.EXTEND;
1178 l_trans_rec.SQUAL_NUM39.EXTEND;
1179 l_trans_rec.SQUAL_NUM40.EXTEND;
1180 l_trans_rec.SQUAL_NUM41.EXTEND;
1181 l_trans_rec.SQUAL_NUM42.EXTEND;
1182 l_trans_rec.SQUAL_NUM43.EXTEND;
1183 l_trans_rec.SQUAL_NUM44.EXTEND;
1184 l_trans_rec.SQUAL_NUM45.EXTEND;
1185 l_trans_rec.SQUAL_NUM46.EXTEND;
1186 l_trans_rec.SQUAL_NUM47.EXTEND;
1187 l_trans_rec.SQUAL_NUM48.EXTEND;
1188 l_trans_rec.SQUAL_NUM49.EXTEND;
1189 l_trans_rec.SQUAL_NUM50.EXTEND;
1190
1191 l_trans_rec.SQUAL_CHAR01.EXTEND;
1192 l_trans_rec.SQUAL_CHAR02.EXTEND;
1193 l_trans_rec.SQUAL_CHAR03.EXTEND;
1194 l_trans_rec.SQUAL_CHAR04.EXTEND;
1195 l_trans_rec.SQUAL_CHAR05.EXTEND;
1196 l_trans_rec.SQUAL_CHAR06.EXTEND;
1197 l_trans_rec.SQUAL_CHAR07.EXTEND;
1198 l_trans_rec.SQUAL_CHAR08.EXTEND;
1199 l_trans_rec.SQUAL_CHAR09.EXTEND;
1200 l_trans_rec.SQUAL_CHAR10.EXTEND;
1201 l_trans_rec.SQUAL_CHAR11.EXTEND;
1202 l_trans_rec.SQUAL_CHAR12.EXTEND;
1203 l_trans_rec.SQUAL_CHAR13.EXTEND;
1204 l_trans_rec.SQUAL_CHAR14.EXTEND;
1205 l_trans_rec.SQUAL_CHAR15.EXTEND;
1206 l_trans_rec.SQUAL_CHAR16.EXTEND;
1207 l_trans_rec.SQUAL_CHAR17.EXTEND;
1208 l_trans_rec.SQUAL_CHAR18.EXTEND;
1209 l_trans_rec.SQUAL_CHAR19.EXTEND;
1210 l_trans_rec.SQUAL_CHAR20.EXTEND;
1211 l_trans_rec.SQUAL_CHAR21.EXTEND;
1212 l_trans_rec.SQUAL_CHAR22.EXTEND;
1213 l_trans_rec.SQUAL_CHAR23.EXTEND;
1214 l_trans_rec.SQUAL_CHAR24.EXTEND;
1215 l_trans_rec.SQUAL_CHAR25.EXTEND;
1216 l_trans_rec.SQUAL_CHAR26.EXTEND;
1217 l_trans_rec.SQUAL_CHAR27.EXTEND;
1218 l_trans_rec.SQUAL_CHAR28.EXTEND;
1219 l_trans_rec.SQUAL_CHAR29.EXTEND;
1220 l_trans_rec.SQUAL_CHAR30.EXTEND;
1221 l_trans_rec.SQUAL_CHAR31.EXTEND;
1222 l_trans_rec.SQUAL_CHAR32.EXTEND;
1223 l_trans_rec.SQUAL_CHAR33.EXTEND;
1224 l_trans_rec.SQUAL_CHAR34.EXTEND;
1225 l_trans_rec.SQUAL_CHAR35.EXTEND;
1226 l_trans_rec.SQUAL_CHAR36.EXTEND;
1227 l_trans_rec.SQUAL_CHAR37.EXTEND;
1228 l_trans_rec.SQUAL_CHAR38.EXTEND;
1229 l_trans_rec.SQUAL_CHAR39.EXTEND;
1230 l_trans_rec.SQUAL_CHAR40.EXTEND;
1231 l_trans_rec.SQUAL_CHAR41.EXTEND;
1232 l_trans_rec.SQUAL_CHAR42.EXTEND;
1233 l_trans_rec.SQUAL_CHAR43.EXTEND;
1234 l_trans_rec.SQUAL_CHAR44.EXTEND;
1235 l_trans_rec.SQUAL_CHAR45.EXTEND;
1236 l_trans_rec.SQUAL_CHAR46.EXTEND;
1237 l_trans_rec.SQUAL_CHAR47.EXTEND;
1238 l_trans_rec.SQUAL_CHAR48.EXTEND;
1239 l_trans_rec.SQUAL_CHAR49.EXTEND;
1240 l_trans_rec.SQUAL_CHAR50.EXTEND;
1241
1242 -- transaction qualifier values
1243 l_trans_rec.SQUAL_NUM01(1) := null;
1244 l_trans_rec.SQUAL_NUM02(1) := null;
1245 l_trans_rec.SQUAL_NUM03(1) := null;
1246 l_trans_rec.SQUAL_NUM04(1) := null;
1247 l_trans_rec.SQUAL_NUM05(1) := null;
1248 l_trans_rec.SQUAL_NUM06(1) := null;
1249 l_trans_rec.SQUAL_NUM07(1) := null;
1250 l_trans_rec.SQUAL_NUM08(1) := null;
1251 l_trans_rec.SQUAL_NUM09(1) := null;
1252 l_trans_rec.SQUAL_NUM10(1) := null;
1253 l_trans_rec.SQUAL_NUM11(1) := null;
1254 l_trans_rec.SQUAL_NUM12(1) := null;
1255 l_trans_rec.SQUAL_NUM13(1) := null;
1256 l_trans_rec.SQUAL_NUM14(1) := null;
1257 l_trans_rec.SQUAL_NUM15(1) := null;
1258 l_trans_rec.SQUAL_NUM16(1) := null;
1259 l_trans_rec.SQUAL_NUM17(1) := null;
1260 l_trans_rec.SQUAL_NUM18(1) := null;
1261 l_trans_rec.SQUAL_NUM19(1) := null;
1262 l_trans_rec.SQUAL_NUM20(1) := null;
1263 l_trans_rec.SQUAL_NUM21(1) := null;
1264 l_trans_rec.SQUAL_NUM22(1) := null;
1265 l_trans_rec.SQUAL_NUM23(1) := null;
1266 l_trans_rec.SQUAL_NUM24(1) := null;
1267 l_trans_rec.SQUAL_NUM25(1) := null;
1268 l_trans_rec.SQUAL_NUM26(1) := null;
1269 l_trans_rec.SQUAL_NUM27(1) := null;
1270 l_trans_rec.SQUAL_NUM28(1) := null;
1271 l_trans_rec.SQUAL_NUM29(1) := null;
1272 l_trans_rec.SQUAL_NUM30(1) := null;
1273 l_trans_rec.SQUAL_NUM31(1) := null;
1274 l_trans_rec.SQUAL_NUM32(1) := null;
1275 l_trans_rec.SQUAL_NUM33(1) := null;
1276 l_trans_rec.SQUAL_NUM34(1) := null;
1277 l_trans_rec.SQUAL_NUM35(1) := null;
1278 l_trans_rec.SQUAL_NUM36(1) := null;
1279 l_trans_rec.SQUAL_NUM37(1) := null;
1280 l_trans_rec.SQUAL_NUM38(1) := null;
1281 l_trans_rec.SQUAL_NUM39(1) := null;
1282 l_trans_rec.SQUAL_NUM40(1) := null;
1283 l_trans_rec.SQUAL_NUM41(1) := null;
1284 l_trans_rec.SQUAL_NUM42(1) := null;
1285 l_trans_rec.SQUAL_NUM43(1) := null;
1286 l_trans_rec.SQUAL_NUM44(1) := null;
1287 l_trans_rec.SQUAL_NUM45(1) := null;
1288 l_trans_rec.SQUAL_NUM46(1) := null;
1289 l_trans_rec.SQUAL_NUM47(1) := null;
1290 l_trans_rec.SQUAL_NUM48(1) := null;
1291 l_trans_rec.SQUAL_NUM49(1) := null;
1292 l_trans_rec.SQUAL_NUM50(1) := null;
1293
1294 l_trans_rec.SQUAL_CHAR01(1) := null;
1295 l_trans_rec.SQUAL_CHAR02(1) := null;
1296 l_trans_rec.SQUAL_CHAR03(1) := null;
1297 l_trans_rec.SQUAL_CHAR04(1) := null;
1298 l_trans_rec.SQUAL_CHAR05(1) := null;
1299 l_trans_rec.SQUAL_CHAR06(1) := null;
1300 l_trans_rec.SQUAL_CHAR07(1) := null;
1301 l_trans_rec.SQUAL_CHAR08(1) := null;
1302 l_trans_rec.SQUAL_CHAR09(1) := null;
1303 l_trans_rec.SQUAL_CHAR10(1) := null;
1304 l_trans_rec.SQUAL_CHAR11(1) := null;
1305 l_trans_rec.SQUAL_CHAR12(1) := null;
1306 l_trans_rec.SQUAL_CHAR13(1) := null;
1307 l_trans_rec.SQUAL_CHAR14(1) := null;
1308 l_trans_rec.SQUAL_CHAR15(1) := null;
1309 l_trans_rec.SQUAL_CHAR16(1) := null;
1310 l_trans_rec.SQUAL_CHAR17(1) := null;
1311 l_trans_rec.SQUAL_CHAR18(1) := null;
1312 l_trans_rec.SQUAL_CHAR19(1) := null;
1313 l_trans_rec.SQUAL_CHAR20(1) := null;
1314 l_trans_rec.SQUAL_CHAR21(1) := null;
1315 l_trans_rec.SQUAL_CHAR22(1) := null;
1316 l_trans_rec.SQUAL_CHAR23(1) := null;
1317 l_trans_rec.SQUAL_CHAR24(1) := null;
1318 l_trans_rec.SQUAL_CHAR25(1) := null;
1319 l_trans_rec.SQUAL_CHAR26(1) := null;
1320 l_trans_rec.SQUAL_CHAR27(1) := null;
1321 l_trans_rec.SQUAL_CHAR28(1) := null;
1322 l_trans_rec.SQUAL_CHAR29(1) := null;
1323 l_trans_rec.SQUAL_CHAR30(1) := null;
1324 l_trans_rec.SQUAL_CHAR31(1) := null;
1325 l_trans_rec.SQUAL_CHAR32(1) := null;
1326 l_trans_rec.SQUAL_CHAR33(1) := null;
1327 l_trans_rec.SQUAL_CHAR34(1) := null;
1328 l_trans_rec.SQUAL_CHAR35(1) := null;
1329 l_trans_rec.SQUAL_CHAR36(1) := null;
1330 l_trans_rec.SQUAL_CHAR37(1) := null;
1331 l_trans_rec.SQUAL_CHAR38(1) := null;
1332 l_trans_rec.SQUAL_CHAR39(1) := null;
1333 l_trans_rec.SQUAL_CHAR40(1) := null;
1334 l_trans_rec.SQUAL_CHAR41(1) := null;
1335 l_trans_rec.SQUAL_CHAR42(1) := null;
1336 l_trans_rec.SQUAL_CHAR43(1) := null;
1337 l_trans_rec.SQUAL_CHAR44(1) := null;
1338 l_trans_rec.SQUAL_CHAR45(1) := null;
1339 l_trans_rec.SQUAL_CHAR46(1) := null;
1340 l_trans_rec.SQUAL_CHAR47(1) := null;
1341 l_trans_rec.SQUAL_CHAR48(1) := null;
1342 l_trans_rec.SQUAL_CHAR49(1) := null;
1343 l_trans_rec.SQUAL_CHAR50(1) := null;
1344
1345 OPEN cur_is_terr_setup;
1346 FETCH cur_is_terr_setup INTO l_count;
1347 CLOSE cur_is_terr_setup;
1348
1349 IF l_count > 0 THEN
1350
1351 IF p_party_id = Fnd_Api.g_miss_num
1352 OR p_party_id IS NULL THEN
1353 OPEN cur_get_party_id(p_sold_to_org);
1354 FETCH cur_get_party_id INTO l_party_id;
1355 CLOSE cur_get_party_id;
1356 ELSE
1357 l_party_id := p_party_id;
1358 END IF;
1359
1360 OPEN cur_get_party_info(l_party_id);
1361 FETCH cur_get_party_info INTO l_trans_rec.SQUAL_CHAR02(1),l_trans_rec.SQUAL_CHAR06(1),l_trans_rec.SQUAL_CHAR04(1),l_trans_rec.SQUAL_CHAR03(1),l_trans_rec.SQUAL_CHAR07(1),l_trans_rec.SQUAL_NUM01(1);
1362 CLOSE cur_get_party_info;
1363 oe_debug_pub.add('Trade MGR SA: before calling get_winners: city ' || l_trans_rec.SQUAL_CHAR02(1));
1364 oe_debug_pub.add('Trade MGR SA: before calling get_winners: zipcode ' || l_trans_rec.SQUAL_CHAR06(1));
1365 oe_debug_pub.add('Trade MGR SA: before calling get_winners: state ' || l_trans_rec.SQUAL_CHAR04(1));
1366 oe_debug_pub.add('Trade MGR SA: before calling get_winners: county ' || l_trans_rec.SQUAL_CHAR03(1));
1367 oe_debug_pub.add('Trade MGR SA: before calling get_winners: country ' || l_trans_rec.SQUAL_CHAR07(1));
1368 oe_debug_pub.add('Trade MGR SA: before calling get_winners: party_id ' || l_trans_rec.SQUAL_NUM01(1));
1369
1370 JTF_TERR_ASSIGN_PUB.get_winners
1371 ( p_api_version_number => l_api_version,
1372 p_init_msg_list => FND_API.G_TRUE,
1373 p_use_type => 'RESOURCE',
1374 p_source_id => -1001,
1375 p_trans_id => -1002,
1376 p_trans_rec => l_trans_rec,
1377 p_resource_type => FND_API.G_MISS_CHAR,
1378 p_role => FND_API.G_MISS_CHAR,
1379 p_top_level_terr_id => FND_API.G_MISS_NUM,
1380 p_num_winners => FND_API.G_MISS_NUM,
1381 x_return_status => l_return_status,
1382 x_msg_count => l_msg_count,
1383 x_msg_data => l_msg_data,
1384 x_winners_rec => l_winner_rec
1385 );
1386
1387 END IF;
1388 oe_debug_pub.add('Trade MGR SA: after calling get_winners: status ' || l_return_status);
1389 oe_debug_pub.add('Trade MGR SA: terr count ' || l_winner_rec.terr_id.COUNT);
1390
1391 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
1392 FOR i IN NVL(l_winner_rec.terr_id.FIRST, 1)..NVL(l_winner_rec.terr_id.LAST, 0) LOOP
1393 l_multirecord(i) := l_winner_rec.terr_id(i);
1394 oe_debug_pub.add('Trade MGR SA: terr_id(' || i || ') ' || l_multirecord(i));
1395 END LOOP;
1396 END IF;
1397 RETURN l_multirecord;
1398
1399 END Find_SA_Territories;
1400
1401
1402 --------------- start of comments --------------------------
1403 -- NAME
1404 -- Find_SA_Territories
1405 --
1406 -- USAGE
1407 -- Overload function will return the winning territories ID
1408 -- for sales account
1409 -- NOTES
1410 --
1411 -- HISTORY
1412 -- 28-OCT-2001 julou created
1413 -- End of Comments
1414 --
1415 --------------- end of comments ----------------------------
1416 FUNCTION Find_SA_Territories
1417 (
1418 p_party_id IN NUMBER
1419 ,p_sold_to_org IN NUMBER
1420 ,p_ship_to_org IN NUMBER
1421 ,p_bill_to_org IN NUMBER
1422 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
1423 IS
1424
1425 l_api_version CONSTANT NUMBER := 1.0;
1426 l_api_name CONSTANT VARCHAR2(30) := 'Find_SA_Territories';
1427 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1428
1429 CURSOR cur_get_party_info(p_party_id NUMBER) IS
1430 SELECT city,postal_code,state,county,country,party_id
1431 FROM hz_parties
1432 WHERE party_id = p_party_id;
1433
1434 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
1435 SELECT party_id
1436 FROM hz_cust_accounts
1437 WHERE cust_account_id = p_sold_to_org;
1438
1439 CURSOR cur_is_terr_setup IS
1440 SELECT /*+ ORDERED */ -- julou sql performance fix
1441 count(*)
1442 FROM jtf_terr jt, jtf_terr_qtype_usgs jtqu,jtf_qual_type_usgs jqtu
1443 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
1444 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
1445 jt.end_date_active IS NULL ))
1446 AND jt.terr_id = jtqu.terr_id
1447 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
1448 AND jqtu.source_id = -1001
1449 AND jqtu.qual_type_id = -1002;
1450
1451 CURSOR c_party_site_id(p_ship_to_org_id NUMBER) IS
1452 SELECT hzcasa.party_site_id
1453 FROM hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa
1454 WHERE hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id
1455 AND hzcsua.site_use_id = p_ship_to_org_id;
1456
1457 l_party_id NUMBER;
1458 l_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
1459 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
1460 l_terr_name VARCHAR2(120);
1461 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1462
1463 l_return_status VARCHAR2(1);
1464 l_msg_data VARCHAR2(2000);
1465 l_msg_count NUMBER;
1466 l_count NUMBER;
1467
1468 BEGIN
1469 -- initializing
1470 --apps.FND_MSG_PUB.initialize;
1471 l_return_status := Fnd_Api.g_ret_sts_success;
1472
1473 -- territory rec
1474 l_trans_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
1475 l_trans_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
1476
1477 -- extend qualifier elements
1478 l_trans_rec.SQUAL_NUM01.EXTEND;
1479 l_trans_rec.SQUAL_NUM02.EXTEND;
1480 l_trans_rec.SQUAL_NUM03.EXTEND;
1481 l_trans_rec.SQUAL_NUM04.EXTEND;
1482 l_trans_rec.SQUAL_NUM05.EXTEND;
1483 l_trans_rec.SQUAL_NUM06.EXTEND;
1484 l_trans_rec.SQUAL_NUM07.EXTEND;
1485 l_trans_rec.SQUAL_NUM08.EXTEND;
1486 l_trans_rec.SQUAL_NUM09.EXTEND;
1487 l_trans_rec.SQUAL_NUM10.EXTEND;
1488 l_trans_rec.SQUAL_NUM11.EXTEND;
1489 l_trans_rec.SQUAL_NUM12.EXTEND;
1490 l_trans_rec.SQUAL_NUM13.EXTEND;
1491 l_trans_rec.SQUAL_NUM14.EXTEND;
1492 l_trans_rec.SQUAL_NUM15.EXTEND;
1493 l_trans_rec.SQUAL_NUM16.EXTEND;
1494 l_trans_rec.SQUAL_NUM17.EXTEND;
1495 l_trans_rec.SQUAL_NUM18.EXTEND;
1496 l_trans_rec.SQUAL_NUM19.EXTEND;
1497 l_trans_rec.SQUAL_NUM20.EXTEND;
1498 l_trans_rec.SQUAL_NUM21.EXTEND;
1499 l_trans_rec.SQUAL_NUM22.EXTEND;
1500 l_trans_rec.SQUAL_NUM23.EXTEND;
1501 l_trans_rec.SQUAL_NUM24.EXTEND;
1502 l_trans_rec.SQUAL_NUM25.EXTEND;
1503 l_trans_rec.SQUAL_NUM26.EXTEND;
1504 l_trans_rec.SQUAL_NUM27.EXTEND;
1505 l_trans_rec.SQUAL_NUM28.EXTEND;
1506 l_trans_rec.SQUAL_NUM29.EXTEND;
1507 l_trans_rec.SQUAL_NUM30.EXTEND;
1508 l_trans_rec.SQUAL_NUM31.EXTEND;
1509 l_trans_rec.SQUAL_NUM32.EXTEND;
1510 l_trans_rec.SQUAL_NUM33.EXTEND;
1511 l_trans_rec.SQUAL_NUM34.EXTEND;
1512 l_trans_rec.SQUAL_NUM35.EXTEND;
1513 l_trans_rec.SQUAL_NUM36.EXTEND;
1514 l_trans_rec.SQUAL_NUM37.EXTEND;
1515 l_trans_rec.SQUAL_NUM38.EXTEND;
1516 l_trans_rec.SQUAL_NUM39.EXTEND;
1517 l_trans_rec.SQUAL_NUM40.EXTEND;
1518 l_trans_rec.SQUAL_NUM41.EXTEND;
1519 l_trans_rec.SQUAL_NUM42.EXTEND;
1520 l_trans_rec.SQUAL_NUM43.EXTEND;
1521 l_trans_rec.SQUAL_NUM44.EXTEND;
1522 l_trans_rec.SQUAL_NUM45.EXTEND;
1523 l_trans_rec.SQUAL_NUM46.EXTEND;
1524 l_trans_rec.SQUAL_NUM47.EXTEND;
1525 l_trans_rec.SQUAL_NUM48.EXTEND;
1526 l_trans_rec.SQUAL_NUM49.EXTEND;
1527 l_trans_rec.SQUAL_NUM50.EXTEND;
1528
1529 l_trans_rec.SQUAL_CHAR01.EXTEND;
1530 l_trans_rec.SQUAL_CHAR02.EXTEND;
1531 l_trans_rec.SQUAL_CHAR03.EXTEND;
1532 l_trans_rec.SQUAL_CHAR04.EXTEND;
1533 l_trans_rec.SQUAL_CHAR05.EXTEND;
1534 l_trans_rec.SQUAL_CHAR06.EXTEND;
1535 l_trans_rec.SQUAL_CHAR07.EXTEND;
1536 l_trans_rec.SQUAL_CHAR08.EXTEND;
1537 l_trans_rec.SQUAL_CHAR09.EXTEND;
1538 l_trans_rec.SQUAL_CHAR10.EXTEND;
1539 l_trans_rec.SQUAL_CHAR11.EXTEND;
1540 l_trans_rec.SQUAL_CHAR12.EXTEND;
1541 l_trans_rec.SQUAL_CHAR13.EXTEND;
1542 l_trans_rec.SQUAL_CHAR14.EXTEND;
1543 l_trans_rec.SQUAL_CHAR15.EXTEND;
1544 l_trans_rec.SQUAL_CHAR16.EXTEND;
1545 l_trans_rec.SQUAL_CHAR17.EXTEND;
1546 l_trans_rec.SQUAL_CHAR18.EXTEND;
1547 l_trans_rec.SQUAL_CHAR19.EXTEND;
1548 l_trans_rec.SQUAL_CHAR20.EXTEND;
1549 l_trans_rec.SQUAL_CHAR21.EXTEND;
1550 l_trans_rec.SQUAL_CHAR22.EXTEND;
1551 l_trans_rec.SQUAL_CHAR23.EXTEND;
1552 l_trans_rec.SQUAL_CHAR24.EXTEND;
1553 l_trans_rec.SQUAL_CHAR25.EXTEND;
1554 l_trans_rec.SQUAL_CHAR26.EXTEND;
1555 l_trans_rec.SQUAL_CHAR27.EXTEND;
1556 l_trans_rec.SQUAL_CHAR28.EXTEND;
1557 l_trans_rec.SQUAL_CHAR29.EXTEND;
1558 l_trans_rec.SQUAL_CHAR30.EXTEND;
1559 l_trans_rec.SQUAL_CHAR31.EXTEND;
1560 l_trans_rec.SQUAL_CHAR32.EXTEND;
1561 l_trans_rec.SQUAL_CHAR33.EXTEND;
1562 l_trans_rec.SQUAL_CHAR34.EXTEND;
1563 l_trans_rec.SQUAL_CHAR35.EXTEND;
1564 l_trans_rec.SQUAL_CHAR36.EXTEND;
1565 l_trans_rec.SQUAL_CHAR37.EXTEND;
1566 l_trans_rec.SQUAL_CHAR38.EXTEND;
1567 l_trans_rec.SQUAL_CHAR39.EXTEND;
1568 l_trans_rec.SQUAL_CHAR40.EXTEND;
1569 l_trans_rec.SQUAL_CHAR41.EXTEND;
1570 l_trans_rec.SQUAL_CHAR42.EXTEND;
1571 l_trans_rec.SQUAL_CHAR43.EXTEND;
1572 l_trans_rec.SQUAL_CHAR44.EXTEND;
1573 l_trans_rec.SQUAL_CHAR45.EXTEND;
1574 l_trans_rec.SQUAL_CHAR46.EXTEND;
1575 l_trans_rec.SQUAL_CHAR47.EXTEND;
1576 l_trans_rec.SQUAL_CHAR48.EXTEND;
1577 l_trans_rec.SQUAL_CHAR49.EXTEND;
1578 l_trans_rec.SQUAL_CHAR50.EXTEND;
1579
1580 -- transaction qualifier values
1581 l_trans_rec.SQUAL_NUM01(1) := null;
1582 l_trans_rec.SQUAL_NUM02(1) := null;
1583 l_trans_rec.SQUAL_NUM03(1) := null;
1584 l_trans_rec.SQUAL_NUM04(1) := null;
1585 l_trans_rec.SQUAL_NUM05(1) := null;
1586 l_trans_rec.SQUAL_NUM06(1) := null;
1587 l_trans_rec.SQUAL_NUM07(1) := null;
1588 l_trans_rec.SQUAL_NUM08(1) := null;
1589 l_trans_rec.SQUAL_NUM09(1) := null;
1590 l_trans_rec.SQUAL_NUM10(1) := null;
1591 l_trans_rec.SQUAL_NUM11(1) := null;
1592 l_trans_rec.SQUAL_NUM12(1) := null;
1593 l_trans_rec.SQUAL_NUM13(1) := null;
1594 l_trans_rec.SQUAL_NUM14(1) := null;
1595 l_trans_rec.SQUAL_NUM15(1) := null;
1596 l_trans_rec.SQUAL_NUM16(1) := null;
1597 l_trans_rec.SQUAL_NUM17(1) := null;
1598 l_trans_rec.SQUAL_NUM18(1) := null;
1599 l_trans_rec.SQUAL_NUM19(1) := null;
1600 l_trans_rec.SQUAL_NUM20(1) := null;
1601 l_trans_rec.SQUAL_NUM21(1) := null;
1602 l_trans_rec.SQUAL_NUM22(1) := null;
1603 l_trans_rec.SQUAL_NUM23(1) := null;
1604 l_trans_rec.SQUAL_NUM24(1) := null;
1605 l_trans_rec.SQUAL_NUM25(1) := null;
1606 l_trans_rec.SQUAL_NUM26(1) := null;
1607 l_trans_rec.SQUAL_NUM27(1) := null;
1608 l_trans_rec.SQUAL_NUM28(1) := null;
1609 l_trans_rec.SQUAL_NUM29(1) := null;
1610 l_trans_rec.SQUAL_NUM30(1) := null;
1611 l_trans_rec.SQUAL_NUM31(1) := null;
1612 l_trans_rec.SQUAL_NUM32(1) := null;
1613 l_trans_rec.SQUAL_NUM33(1) := null;
1614 l_trans_rec.SQUAL_NUM34(1) := null;
1615 l_trans_rec.SQUAL_NUM35(1) := null;
1616 l_trans_rec.SQUAL_NUM36(1) := null;
1617 l_trans_rec.SQUAL_NUM37(1) := null;
1618 l_trans_rec.SQUAL_NUM38(1) := null;
1619 l_trans_rec.SQUAL_NUM39(1) := null;
1620 l_trans_rec.SQUAL_NUM40(1) := null;
1621 l_trans_rec.SQUAL_NUM41(1) := null;
1622 l_trans_rec.SQUAL_NUM42(1) := null;
1623 l_trans_rec.SQUAL_NUM43(1) := null;
1624 l_trans_rec.SQUAL_NUM44(1) := null;
1625 l_trans_rec.SQUAL_NUM45(1) := null;
1626 l_trans_rec.SQUAL_NUM46(1) := null;
1627 l_trans_rec.SQUAL_NUM47(1) := null;
1628 l_trans_rec.SQUAL_NUM48(1) := null;
1629 l_trans_rec.SQUAL_NUM49(1) := null;
1630 l_trans_rec.SQUAL_NUM50(1) := null;
1631
1632 l_trans_rec.SQUAL_CHAR01(1) := null;
1633 l_trans_rec.SQUAL_CHAR02(1) := null;
1634 l_trans_rec.SQUAL_CHAR03(1) := null;
1635 l_trans_rec.SQUAL_CHAR04(1) := null;
1636 l_trans_rec.SQUAL_CHAR05(1) := null;
1637 l_trans_rec.SQUAL_CHAR06(1) := null;
1638 l_trans_rec.SQUAL_CHAR07(1) := null;
1639 l_trans_rec.SQUAL_CHAR08(1) := null;
1640 l_trans_rec.SQUAL_CHAR09(1) := null;
1641 l_trans_rec.SQUAL_CHAR10(1) := null;
1642 l_trans_rec.SQUAL_CHAR11(1) := null;
1643 l_trans_rec.SQUAL_CHAR12(1) := null;
1644 l_trans_rec.SQUAL_CHAR13(1) := null;
1645 l_trans_rec.SQUAL_CHAR14(1) := null;
1646 l_trans_rec.SQUAL_CHAR15(1) := null;
1647 l_trans_rec.SQUAL_CHAR16(1) := null;
1648 l_trans_rec.SQUAL_CHAR17(1) := null;
1649 l_trans_rec.SQUAL_CHAR18(1) := null;
1650 l_trans_rec.SQUAL_CHAR19(1) := null;
1651 l_trans_rec.SQUAL_CHAR20(1) := null;
1652 l_trans_rec.SQUAL_CHAR21(1) := null;
1653 l_trans_rec.SQUAL_CHAR22(1) := null;
1654 l_trans_rec.SQUAL_CHAR23(1) := null;
1655 l_trans_rec.SQUAL_CHAR24(1) := null;
1656 l_trans_rec.SQUAL_CHAR25(1) := null;
1657 l_trans_rec.SQUAL_CHAR26(1) := null;
1658 l_trans_rec.SQUAL_CHAR27(1) := null;
1659 l_trans_rec.SQUAL_CHAR28(1) := null;
1660 l_trans_rec.SQUAL_CHAR29(1) := null;
1661 l_trans_rec.SQUAL_CHAR30(1) := null;
1662 l_trans_rec.SQUAL_CHAR31(1) := null;
1663 l_trans_rec.SQUAL_CHAR32(1) := null;
1664 l_trans_rec.SQUAL_CHAR33(1) := null;
1665 l_trans_rec.SQUAL_CHAR34(1) := null;
1666 l_trans_rec.SQUAL_CHAR35(1) := null;
1667 l_trans_rec.SQUAL_CHAR36(1) := null;
1668 l_trans_rec.SQUAL_CHAR37(1) := null;
1669 l_trans_rec.SQUAL_CHAR38(1) := null;
1670 l_trans_rec.SQUAL_CHAR39(1) := null;
1671 l_trans_rec.SQUAL_CHAR40(1) := null;
1672 l_trans_rec.SQUAL_CHAR41(1) := null;
1673 l_trans_rec.SQUAL_CHAR42(1) := null;
1674 l_trans_rec.SQUAL_CHAR43(1) := null;
1675 l_trans_rec.SQUAL_CHAR44(1) := null;
1676 l_trans_rec.SQUAL_CHAR45(1) := null;
1677 l_trans_rec.SQUAL_CHAR46(1) := null;
1678 l_trans_rec.SQUAL_CHAR47(1) := null;
1679 l_trans_rec.SQUAL_CHAR48(1) := null;
1680 l_trans_rec.SQUAL_CHAR49(1) := null;
1681 l_trans_rec.SQUAL_CHAR50(1) := null;
1682
1683 OPEN cur_is_terr_setup;
1684 FETCH cur_is_terr_setup INTO l_count;
1685 CLOSE cur_is_terr_setup;
1686
1687 IF l_count > 0 THEN
1688
1689 IF p_party_id = Fnd_Api.g_miss_num
1690 OR p_party_id IS NULL THEN
1691 OPEN cur_get_party_id(p_sold_to_org);
1692 FETCH cur_get_party_id INTO l_party_id;
1693 CLOSE cur_get_party_id;
1694 ELSE
1695 l_party_id := p_party_id;
1696 END IF;
1697
1698 OPEN cur_get_party_info(l_party_id);
1699 FETCH cur_get_party_info INTO l_trans_rec.SQUAL_CHAR02(1),l_trans_rec.SQUAL_CHAR06(1),l_trans_rec.SQUAL_CHAR04(1),l_trans_rec.SQUAL_CHAR03(1),l_trans_rec.SQUAL_CHAR07(1),l_trans_rec.SQUAL_NUM01(1);
1700 CLOSE cur_get_party_info;
1701
1702 OPEN c_party_site_id(p_ship_to_org);
1703 FETCH c_party_site_id INTO l_trans_rec.SQUAL_NUM02(1);
1704 CLOSE c_party_site_id;
1705
1706 JTF_TERR_ASSIGN_PUB.get_winners
1707 ( p_api_version_number => l_api_version,
1708 p_init_msg_list => FND_API.G_TRUE,
1709 p_use_type => 'RESOURCE',
1710 p_source_id => -1001,
1711 p_trans_id => -1002,
1712 p_trans_rec => l_trans_rec,
1713 p_resource_type => FND_API.G_MISS_CHAR,
1714 p_role => FND_API.G_MISS_CHAR,
1715 p_top_level_terr_id => FND_API.G_MISS_NUM,
1716 p_num_winners => FND_API.G_MISS_NUM,
1717 x_return_status => l_return_status,
1718 x_msg_count => l_msg_count,
1719 x_msg_data => l_msg_data,
1720 x_winners_rec => l_winner_rec
1721 );
1722
1723 END IF;
1724
1725 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
1726 FOR i IN 1..l_winner_rec.terr_id.COUNT LOOP
1727 l_multirecord(i) := l_winner_rec.terr_id(i);
1728 END LOOP;
1729 END IF;
1730 RETURN l_multirecord;
1731
1732 END Find_SA_Territories;
1733
1734
1735 -- Sourcing rules for SOLD_BY contxt
1736 FUNCTION get_sales_method
1737 (
1738 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1739 )
1740 RETURN VARCHAR2
1741 IS
1742 l_sales_method VARCHAR2(1);
1743 l_distributor_acct_id NUMBER;
1744 BEGIN
1745 IF p_resale_line_tbl.COUNT > 0 THEN
1746 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1747 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1748 l_sales_method := 'I';
1749 ELSE
1750 l_sales_method := 'D';
1751 END IF;
1752 ELSE
1753 l_sales_method := 'D';
1754 END IF;
1755
1756 RETURN l_sales_method;
1757 END get_sales_method;
1758
1759
1760 FUNCTION get_distributor_acct_id
1761 (
1762 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1763 )
1764 RETURN NUMBER
1765 IS
1766 l_distributor_acct_id NUMBER;
1767 BEGIN
1768 IF p_resale_line_tbl.COUNT > 0 THEN
1769 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1770 ELSE
1771 l_distributor_acct_id := NULL;
1772 END IF;
1773
1774 RETURN l_distributor_acct_id;
1775 END get_distributor_acct_id;
1776
1777
1778 FUNCTION get_distributor_lists
1779 (
1780 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1781 )
1782 RETURN Qp_Attr_Mapping_Pub.t_multirecord
1783 IS
1784 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1785 l_distributor_acct_id NUMBER;
1786 BEGIN
1787 IF p_resale_line_tbl.COUNT > 0 THEN
1788 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1789 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1790 l_multirecord := get_lists(fnd_api.g_miss_num, l_distributor_acct_id);
1791 END IF;
1792 END IF;
1793
1794 RETURN l_multirecord;
1795 END get_distributor_lists;
1796
1797
1798 FUNCTION get_distributor_segments
1799 (
1800 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1801 )
1802 RETURN Qp_Attr_Mapping_Pub.t_multirecord
1803 IS
1804 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1805 l_distributor_acct_id NUMBER;
1806 BEGIN
1807 IF p_resale_line_tbl.COUNT > 0 THEN
1808 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1809 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1810 l_multirecord := get_segments(fnd_api.g_miss_num, l_distributor_acct_id);
1811 END IF;
1812 END IF;
1813
1814 RETURN l_multirecord;
1815 END get_distributor_segments;
1816
1817
1818 FUNCTION get_distributor_territories
1819 (
1820 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1821 )
1822 RETURN Qp_Attr_Mapping_Pub.t_multirecord
1823 IS
1824 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1825 l_distributor_acct_id NUMBER;
1826 l_ship_to_org_id NUMBER;
1827 BEGIN
1828 IF p_resale_line_tbl.COUNT > 0 THEN
1829 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1830 l_ship_to_org_id := p_resale_line_tbl(1).sold_from_site_id;
1831 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1832 l_multirecord := find_tm_territories(fnd_api.g_miss_num, l_distributor_acct_id, l_ship_to_org_id, fnd_api.g_miss_num);
1833 END IF;
1834 END IF;
1835
1836 RETURN l_multirecord;
1837 END get_distributor_territories;
1838 /*
1839 Seeding instruction:
1840 get_sales_method:
1841 User Source Type: PL/SQL API
1842 User Value String: OZF_QP_QUAL_PVT.get_sales_method(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1843
1844 get_distributor_acct_id:
1845 User Source Type: PL/SQL API
1846 User Value String: OZF_QP_QUAL_PVT.get_distributor_acct_id(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1847
1848 get_distributor_lists:
1849 User Source Type: PL/SQL API Multi-Record
1850 User Value String: OZF_QP_QUAL_PVT.get_distributor_lists(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1851
1852 get_distributor_segments:
1853 User Source Type: PL/SQL API Multi-Record
1854 User Value String: OZF_QP_QUAL_PVT.get_distributor_segments(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1855
1856 get_distributor_territories:
1857 User Source Type: PL/SQL API Multi-Record
1858 User Value String: OZF_QP_QUAL_PVT.get_distributor_territories(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1859 */
1860
1861 END OZF_QP_QUAL_PVT ;