[Home] [Help]
PACKAGE BODY: APPS.OZF_QP_QUAL_PVT
Source
1 PACKAGE BODY OZF_QP_QUAL_PVT as
2 /* $Header: ozfvqpqb.pls 120.11 2011/10/16 18:09:06 nirprasa ship $ */
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 -- 21-Oct-2010 nirprasa 10216374/9447673 SSD-IDSM ER - IDSM AND ACCRUAL CHANGES
19 -- 10/16/2011 nirprasa ER10157845 ER: NEED BACKDATING ADJUSTMENT FUNCTIONALITY FOR SUPPLIER SHIP & DEBIT OFFERS
20 -- 10/16/2011 nirprasa ER10157845 : restrict get_item_cost API to be update already populated cost_price
21 ------------------------------------------------------------------------------
22
23 G_PKG_NAME CONSTANT VARCHAR2(30):='OZF_QP_QUAL_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12):='ozfvqpqb.pls';
25 g_bg_tbl qp_attr_mapping_pub.t_multirecord;
26 g_total NUMBER := 1 ;
27 g_rel_type VARCHAR2(30) := FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE');
28
29
30 FUNCTION check_exists(p_party_id NUMBER)
31 RETURN boolean IS
32 dupfound boolean := false;
33
34 BEGIN
35 FOR i IN 1.. g_bg_tbl.COUNT LOOP
36 if g_bg_tbl(i) = p_party_id then
37 dupfound := true;
38 exit;
39 end if;
40 END LOOP;
41 return dupfound;
42 END;
43
44
45 --------------- start of comments --------------------------
46 -- NAME
47 -- get_all_parents
48 --
49 -- USAGE
50 -- Procedure will do recursive job to find all parents for each party
51 -- NOTES
52 --
53 -- HISTORY
54 -- 11/07/2001 jieli created
55 -- End of Comments
56 --
57 --------------- end of comments ----------------------------
58
59 PROCEDURE get_all_parents(aso_party_id IN NUMBER,
60 om_sold_to_org IN NUMBER,
61 px_bg_tbl IN OUT NOCOPY qp_attr_mapping_pub.t_multirecord)
62 IS
63
64 p_party_id NUMBER;
65 l_party_id NUMBER;
66
67 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
68 SELECT party_id
69 FROM hz_cust_accounts
70 WHERE cust_account_id = p_sold_to_org;
71
72 /*
73 CURSOR c_bg IS
74 select object_id
75 from hz_party_relationships
76 where party_relationship_type = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
77 and subject_id=p_party_id;
78 */
79
80 CURSOR c_bg IS
81 SELECT r1.object_id
82 FROM hz_relationships r1
83 WHERE r1.relationship_code = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
84 AND r1.subject_type = 'ORGANIZATION'
85 AND r1.subject_table_name = 'HZ_PARTIES'
86 AND r1.object_type = 'ORGANIZATION'
87 AND r1.object_table_name = 'HZ_PARTIES'
88 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
89 AND r1.status = 'A'
90 AND r1.subject_id=p_party_id;
91
92 BEGIN
93
94 IF aso_party_id IS NULL OR aso_party_id = FND_API.g_miss_num THEN
95 OPEN cur_get_party_id(om_sold_to_org);
96 FETCH cur_get_party_id into p_party_id;
97 CLOSE cur_get_party_id;
98 ELSE
99 p_party_id := aso_party_id;
100 END IF;
101
102 open c_bg;
103 LOOP
104 fetch c_bg into l_party_id;
105 IF c_bg%notfound then
106 if check_exists(p_party_id) = false then
107 g_bg_tbl(g_total) := p_party_id;
108 g_total := g_total + 1;
109 px_bg_tbl := g_bg_tbl;
110 end if;
111 close c_bg;
112 exit;
113 else
114 get_all_parents(l_party_id,'',g_bg_tbl);
115 end if;
116 END LOOP;
117
118 END get_all_parents;
119
120 --------------- start of comments --------------------------
121 -- NAME
122 -- get_buying_groups
123 --
124 -- USAGE
125 -- Function will return all the buying groups
126 -- to which the Customer belongs
127 -- NOTES
128 --
129 -- HISTORY
130 -- 11/07/2001 jieli created
131 -- End of Comments
132 --
133 --------------- end of comments ----------------------------
134
135 FUNCTION get_buying_groups(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER)
136 RETURN qp_attr_mapping_pub.t_MultiRecord IS
137 l_bg_tbl qp_attr_mapping_pub.t_multirecord ;
138
139 p_party_id NUMBER;
140 l_party_id NUMBER;
141
142 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
143 SELECT party_id
144 FROM hz_cust_accounts
145 WHERE cust_account_id = p_sold_to_org;
146
147 /*
148 CURSOR c_bg IS
149 select object_id
150 from hz_party_relationships
151 where party_relationship_type = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
152 and subject_id=p_party_id;
153 */
154
155 CURSOR c_bg IS
156 SELECT r1.object_id
157 FROM hz_relationships r1
158 WHERE r1.relationship_code = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
159 AND r1.subject_type = 'ORGANIZATION'
160 AND r1.subject_table_name = 'HZ_PARTIES'
161 AND r1.object_type = 'ORGANIZATION'
162 AND r1.object_table_name = 'HZ_PARTIES'
163 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
164 AND r1.status = 'A'
165 AND r1.subject_id=p_party_id;
166
167 BEGIN
168 g_bg_tbl.delete;
169 g_total := 1;
170
171 IF aso_party_id IS NULL OR aso_party_id = FND_API.g_miss_num THEN
172 OPEN cur_get_party_id(om_sold_to_org);
173 FETCH cur_get_party_id into p_party_id;
174 CLOSE cur_get_party_id;
175 ELSE
176 p_party_id := aso_party_id;
177 END IF;
178
179 open c_bg;
180 fetch c_bg into l_party_id;
181 IF c_bg%notfound then
182 close c_bg;
183 -- l_bg_tbl(g_total) := aso_party_id;
184 l_bg_tbl(g_total) := p_party_id;
185 -- return l_bg_tbl;
186 ELSE
187 close c_bg;
188 get_all_parents(p_party_id,om_sold_to_org, l_bg_tbl);
189 END IF;
190
191 --get_all_parents(aso_party_id,om_sold_to_org, l_bg_tbl);
192 return l_bg_tbl;
193 END;
194
195 --------------- start of comments --------------------------
196 -- NAME
197 -- get_buying_groups
198 --
199 -- USAGE
200 -- overload function to handle buying group in indirect sales
201 -- NOTES
202 --
203 -- HISTORY
204 -- 26-FEB-2004 julou created.
205 -- End of Comments
206 --
207 --------------- end of comments ----------------------------
208 FUNCTION get_buying_groups(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER,ic_party_id IN NUMBER)
209 RETURN qp_attr_mapping_pub.t_MultiRecord IS
210 l_bg_tbl qp_attr_mapping_pub.t_multirecord ;
211
212 p_party_id NUMBER;
213 l_party_id NUMBER;
214
215 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
216 SELECT party_id
217 FROM hz_cust_accounts
218 WHERE cust_account_id = p_sold_to_org;
219
220 CURSOR c_bg IS
221 SELECT r1.object_id
222 FROM hz_relationships r1
223 WHERE r1.relationship_code = g_rel_type --FND_PROFILE.value ('OZF_PARTY_RELATIONS_TYPE')
224 AND r1.subject_type = 'ORGANIZATION'
225 AND r1.subject_table_name = 'HZ_PARTIES'
226 AND r1.object_type = 'ORGANIZATION'
227 AND r1.object_table_name = 'HZ_PARTIES'
228 AND r1.start_date <= SYSDATE AND NVL(r1.end_date, SYSDATE) >= SYSDATE
229 AND r1.status = 'A'
230 AND r1.subject_id=p_party_id;
231
232 BEGIN
233 g_bg_tbl.DELETE;
234 g_total := 1;
235
236 IF aso_party_id IS NULL OR aso_party_id = FND_API.g_miss_num THEN
237 IF om_sold_to_org IS NULL OR om_sold_to_org = FND_API.g_miss_num THEN
238 p_party_id := ic_party_id;
239 ELSE
240 OPEN cur_get_party_id(om_sold_to_org);
241 FETCH cur_get_party_id into p_party_id;
242 CLOSE cur_get_party_id;
243 END IF;
244 ELSE
245 p_party_id := aso_party_id;
246 END IF;
247
248 OPEN c_bg;
249 FETCH c_bg INTO l_party_id;
250
251 IF c_bg%NOTFOUND THEN
252 CLOSE c_bg;
253 l_bg_tbl(g_total) := p_party_id;
254 ELSE
255 CLOSE c_bg;
256 get_all_parents(p_party_id,om_sold_to_org, l_bg_tbl);
257 END IF;
258
259 RETURN l_bg_tbl;
260 END;
261
262 --------------- start of comments --------------------------
263 -- NAME
264 -- get_market_segment
265 --
266 -- USAGE
267 -- Function will return all the Market Segments
268 -- to which the Customer belongs
269 -- NOTES
270 --
271 -- HISTORY
272 -- 01/12/2000 ptendulk created
273 -- 06/12/2000 skarumur modified
274 -- Changed the return types for the functions
275 -- should return segment names instead of ID's
276 -- Using qp's return structure
277 -- End of Comments
278 --
279 --------------- end of comments ----------------------------
280 FUNCTION get_segments(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER)
281 RETURN qp_attr_mapping_pub.t_MultiRecord IS
282
283 l_mks_tbl qp_attr_mapping_pub.t_multirecord ;
284
285 p_party_id number;
286
287 CURSOR c_mks IS
288 SELECT pms.market_segment_id
289 FROM ams_party_market_segments pms
290 WHERE pms.party_id = p_party_id
291 AND pms.market_qualifier_type IS NULL;
292
293 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
294 SELECT party_id
295 FROM hz_cust_accounts
296 WHERE cust_account_id = p_sold_to_org;
297
298 l_total NUMBER := 1 ;
299
300 BEGIN
301
302 IF aso_party_id = FND_API.g_miss_num THEN
303 OPEN cur_get_party_id(om_sold_to_org);
304 FETCH cur_get_party_id into p_party_id;
305 CLOSE cur_get_party_id;
306 ELSE
307 p_party_id := aso_party_id;
308 END IF;
309
310 FOR mks_rec in c_mks LOOP
311 l_mks_tbl(l_total) := mks_rec.market_segment_id;
312 l_total := l_total + 1 ;
313 END LOOP;
314 return l_mks_tbl ;
315 END get_segments ;
316
317 --------------- start of comments --------------------------
318 -- NAME
319 -- get_lists
320
321 -- USAGE
322 -- Function will return all the Target Segments
323 -- to which the Customer belongs
324 -- NOTES
325 --
326 -- HISTORY
327 --- julou Created
328 --
329 --------------- end of comments ----------------------------
330 FUNCTION get_lists(aso_party_id IN NUMBER,om_sold_to_org IN NUMBER)
331 RETURN qp_attr_mapping_pub.t_MultiRecord IS
332
333 l_tgt_tbl qp_attr_mapping_pub.t_multirecord ;
334
335 p_party_id number;
336
337 CURSOR c_mks IS
338 SELECT list_header_id
339 FROM ams_list_entries
340 WHERE enabled_flag = 'Y'
341 AND party_id = p_party_id;
342
343 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
344 SELECT party_id
345 FROM hz_cust_accounts
346 WHERE cust_account_id = p_sold_to_org;
347
348 l_total NUMBER := 1 ;
349
350 BEGIN
351
352 IF aso_party_id = FND_API.g_miss_num THEN
353 OPEN cur_get_party_id(om_sold_to_org);
354 FETCH cur_get_party_id into p_party_id;
355 CLOSE cur_get_party_id;
356 ELSE
357 p_party_id := aso_party_id;
358 END IF;
359
360 FOR mks_rec in c_mks LOOP
361 l_tgt_tbl(l_total) := mks_rec.list_header_id;
362 l_total := l_total + 1;
363 END LOOP;
364 return l_tgt_tbl ;
365
366 END get_lists ;
367
368
369 --------------- start of comments --------------------------
370 -- NAME
371 -- Find_TM_Territories
372 --
373 -- USAGE
374 -- Function will return the winning territories ID
375 -- for trade management
376 -- NOTES
377 --
378 -- HISTORY
379 -- 28-OCT-2001 julou created
380 -- End of Comments
381 --
382 --------------- end of comments ----------------------------
383 FUNCTION Find_TM_Territories
384 (
385 p_party_id IN NUMBER
386 ,p_sold_to_org IN NUMBER
387 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
388 IS
389
390 l_api_version CONSTANT NUMBER := 1.0;
391 l_api_name CONSTANT VARCHAR2(30) := 'Find_TM_Territories';
392 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
393
394 CURSOR cur_get_party_info(p_party_id NUMBER) IS
395 SELECT city,postal_code,state,county,country,party_id
396 FROM hz_parties
397 WHERE party_id = p_party_id;
398
399 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
400 SELECT party_id
401 FROM hz_cust_accounts
402 WHERE cust_account_id = p_sold_to_org;
403
404 CURSOR cur_is_terr_setup IS
405 SELECT /*+ ORDERED */ -- julou sql performance fix
406 count(*)
407 FROM jtf_terr_all jt, jtf_terr_qtype_usgs_all jtqu, jtf_qual_type_usgs jqtu
408 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
409 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
410 jt.end_date_active IS NULL ))
411 AND jt.terr_id = jtqu.terr_id
412 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
413 AND jqtu.source_id = -1003
414 AND jqtu.qual_type_id = -1007;
415
416 l_party_id NUMBER;
417 l_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
418 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
419 l_terr_name VARCHAR2(120);
420 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
421
422 l_return_status VARCHAR2(1);
423 l_msg_data VARCHAR2(2000);
424 l_msg_count NUMBER;
425 l_count NUMBER;
426
427 BEGIN
428 -- initializing
429 --apps.FND_MSG_PUB.initialize;
430 l_return_status := Fnd_Api.g_ret_sts_success;
431
432 -- territory rec
433 l_trans_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
434 l_trans_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
435
436 -- extend qualifier elements
437 l_trans_rec.SQUAL_NUM01.EXTEND;
438 l_trans_rec.SQUAL_NUM02.EXTEND;
439 l_trans_rec.SQUAL_NUM03.EXTEND;
440 l_trans_rec.SQUAL_NUM04.EXTEND;
441 l_trans_rec.SQUAL_NUM05.EXTEND;
442 l_trans_rec.SQUAL_NUM06.EXTEND;
443 l_trans_rec.SQUAL_NUM07.EXTEND;
444 l_trans_rec.SQUAL_NUM08.EXTEND;
445 l_trans_rec.SQUAL_NUM09.EXTEND;
446 l_trans_rec.SQUAL_NUM10.EXTEND;
447 l_trans_rec.SQUAL_NUM11.EXTEND;
448 l_trans_rec.SQUAL_NUM12.EXTEND;
449 l_trans_rec.SQUAL_NUM13.EXTEND;
450 l_trans_rec.SQUAL_NUM14.EXTEND;
451 l_trans_rec.SQUAL_NUM15.EXTEND;
452 l_trans_rec.SQUAL_NUM16.EXTEND;
453 l_trans_rec.SQUAL_NUM17.EXTEND;
454 l_trans_rec.SQUAL_NUM18.EXTEND;
455 l_trans_rec.SQUAL_NUM19.EXTEND;
456 l_trans_rec.SQUAL_NUM20.EXTEND;
457 l_trans_rec.SQUAL_NUM21.EXTEND;
458 l_trans_rec.SQUAL_NUM22.EXTEND;
459 l_trans_rec.SQUAL_NUM23.EXTEND;
460 l_trans_rec.SQUAL_NUM24.EXTEND;
461 l_trans_rec.SQUAL_NUM25.EXTEND;
462 l_trans_rec.SQUAL_NUM26.EXTEND;
463 l_trans_rec.SQUAL_NUM27.EXTEND;
464 l_trans_rec.SQUAL_NUM28.EXTEND;
465 l_trans_rec.SQUAL_NUM29.EXTEND;
466 l_trans_rec.SQUAL_NUM30.EXTEND;
467 l_trans_rec.SQUAL_NUM31.EXTEND;
468 l_trans_rec.SQUAL_NUM32.EXTEND;
469 l_trans_rec.SQUAL_NUM33.EXTEND;
470 l_trans_rec.SQUAL_NUM34.EXTEND;
471 l_trans_rec.SQUAL_NUM35.EXTEND;
472 l_trans_rec.SQUAL_NUM36.EXTEND;
473 l_trans_rec.SQUAL_NUM37.EXTEND;
474 l_trans_rec.SQUAL_NUM38.EXTEND;
475 l_trans_rec.SQUAL_NUM39.EXTEND;
476 l_trans_rec.SQUAL_NUM40.EXTEND;
477 l_trans_rec.SQUAL_NUM41.EXTEND;
478 l_trans_rec.SQUAL_NUM42.EXTEND;
479 l_trans_rec.SQUAL_NUM43.EXTEND;
480 l_trans_rec.SQUAL_NUM44.EXTEND;
481 l_trans_rec.SQUAL_NUM45.EXTEND;
482 l_trans_rec.SQUAL_NUM46.EXTEND;
483 l_trans_rec.SQUAL_NUM47.EXTEND;
484 l_trans_rec.SQUAL_NUM48.EXTEND;
485 l_trans_rec.SQUAL_NUM49.EXTEND;
486 l_trans_rec.SQUAL_NUM50.EXTEND;
487
488 l_trans_rec.SQUAL_CHAR01.EXTEND;
489 l_trans_rec.SQUAL_CHAR02.EXTEND;
490 l_trans_rec.SQUAL_CHAR03.EXTEND;
491 l_trans_rec.SQUAL_CHAR04.EXTEND;
492 l_trans_rec.SQUAL_CHAR05.EXTEND;
493 l_trans_rec.SQUAL_CHAR06.EXTEND;
494 l_trans_rec.SQUAL_CHAR07.EXTEND;
495 l_trans_rec.SQUAL_CHAR08.EXTEND;
496 l_trans_rec.SQUAL_CHAR09.EXTEND;
497 l_trans_rec.SQUAL_CHAR10.EXTEND;
498 l_trans_rec.SQUAL_CHAR11.EXTEND;
499 l_trans_rec.SQUAL_CHAR12.EXTEND;
500 l_trans_rec.SQUAL_CHAR13.EXTEND;
501 l_trans_rec.SQUAL_CHAR14.EXTEND;
502 l_trans_rec.SQUAL_CHAR15.EXTEND;
503 l_trans_rec.SQUAL_CHAR16.EXTEND;
504 l_trans_rec.SQUAL_CHAR17.EXTEND;
505 l_trans_rec.SQUAL_CHAR18.EXTEND;
506 l_trans_rec.SQUAL_CHAR19.EXTEND;
507 l_trans_rec.SQUAL_CHAR20.EXTEND;
508 l_trans_rec.SQUAL_CHAR21.EXTEND;
509 l_trans_rec.SQUAL_CHAR22.EXTEND;
510 l_trans_rec.SQUAL_CHAR23.EXTEND;
511 l_trans_rec.SQUAL_CHAR24.EXTEND;
512 l_trans_rec.SQUAL_CHAR25.EXTEND;
513 l_trans_rec.SQUAL_CHAR26.EXTEND;
514 l_trans_rec.SQUAL_CHAR27.EXTEND;
515 l_trans_rec.SQUAL_CHAR28.EXTEND;
516 l_trans_rec.SQUAL_CHAR29.EXTEND;
517 l_trans_rec.SQUAL_CHAR30.EXTEND;
518 l_trans_rec.SQUAL_CHAR31.EXTEND;
519 l_trans_rec.SQUAL_CHAR32.EXTEND;
520 l_trans_rec.SQUAL_CHAR33.EXTEND;
521 l_trans_rec.SQUAL_CHAR34.EXTEND;
522 l_trans_rec.SQUAL_CHAR35.EXTEND;
523 l_trans_rec.SQUAL_CHAR36.EXTEND;
524 l_trans_rec.SQUAL_CHAR37.EXTEND;
525 l_trans_rec.SQUAL_CHAR38.EXTEND;
526 l_trans_rec.SQUAL_CHAR39.EXTEND;
527 l_trans_rec.SQUAL_CHAR40.EXTEND;
528 l_trans_rec.SQUAL_CHAR41.EXTEND;
529 l_trans_rec.SQUAL_CHAR42.EXTEND;
530 l_trans_rec.SQUAL_CHAR43.EXTEND;
531 l_trans_rec.SQUAL_CHAR44.EXTEND;
532 l_trans_rec.SQUAL_CHAR45.EXTEND;
533 l_trans_rec.SQUAL_CHAR46.EXTEND;
534 l_trans_rec.SQUAL_CHAR47.EXTEND;
535 l_trans_rec.SQUAL_CHAR48.EXTEND;
536 l_trans_rec.SQUAL_CHAR49.EXTEND;
537 l_trans_rec.SQUAL_CHAR50.EXTEND;
538
539 -- transaction qualifier values
540 l_trans_rec.SQUAL_NUM01(1) := null;
541 l_trans_rec.SQUAL_NUM02(1) := null;
542 l_trans_rec.SQUAL_NUM03(1) := null;
543 l_trans_rec.SQUAL_NUM04(1) := null;
544 l_trans_rec.SQUAL_NUM05(1) := null;
545 l_trans_rec.SQUAL_NUM06(1) := null;
546 l_trans_rec.SQUAL_NUM07(1) := null;
547 l_trans_rec.SQUAL_NUM08(1) := null;
548 l_trans_rec.SQUAL_NUM09(1) := null;
549 l_trans_rec.SQUAL_NUM10(1) := null;
550 l_trans_rec.SQUAL_NUM11(1) := null;
551 l_trans_rec.SQUAL_NUM12(1) := null;
552 l_trans_rec.SQUAL_NUM13(1) := null;
553 l_trans_rec.SQUAL_NUM14(1) := null;
554 l_trans_rec.SQUAL_NUM15(1) := null;
555 l_trans_rec.SQUAL_NUM16(1) := null;
556 l_trans_rec.SQUAL_NUM17(1) := null;
557 l_trans_rec.SQUAL_NUM18(1) := null;
558 l_trans_rec.SQUAL_NUM19(1) := null;
559 l_trans_rec.SQUAL_NUM20(1) := null;
560 l_trans_rec.SQUAL_NUM21(1) := null;
561 l_trans_rec.SQUAL_NUM22(1) := null;
562 l_trans_rec.SQUAL_NUM23(1) := null;
563 l_trans_rec.SQUAL_NUM24(1) := null;
564 l_trans_rec.SQUAL_NUM25(1) := null;
565 l_trans_rec.SQUAL_NUM26(1) := null;
566 l_trans_rec.SQUAL_NUM27(1) := null;
567 l_trans_rec.SQUAL_NUM28(1) := null;
568 l_trans_rec.SQUAL_NUM29(1) := null;
569 l_trans_rec.SQUAL_NUM30(1) := null;
570 l_trans_rec.SQUAL_NUM31(1) := null;
571 l_trans_rec.SQUAL_NUM32(1) := null;
572 l_trans_rec.SQUAL_NUM33(1) := null;
573 l_trans_rec.SQUAL_NUM34(1) := null;
574 l_trans_rec.SQUAL_NUM35(1) := null;
575 l_trans_rec.SQUAL_NUM36(1) := null;
576 l_trans_rec.SQUAL_NUM37(1) := null;
577 l_trans_rec.SQUAL_NUM38(1) := null;
578 l_trans_rec.SQUAL_NUM39(1) := null;
579 l_trans_rec.SQUAL_NUM40(1) := null;
580 l_trans_rec.SQUAL_NUM41(1) := null;
581 l_trans_rec.SQUAL_NUM42(1) := null;
582 l_trans_rec.SQUAL_NUM43(1) := null;
583 l_trans_rec.SQUAL_NUM44(1) := null;
584 l_trans_rec.SQUAL_NUM45(1) := null;
585 l_trans_rec.SQUAL_NUM46(1) := null;
586 l_trans_rec.SQUAL_NUM47(1) := null;
587 l_trans_rec.SQUAL_NUM48(1) := null;
588 l_trans_rec.SQUAL_NUM49(1) := null;
589 l_trans_rec.SQUAL_NUM50(1) := null;
590
591 l_trans_rec.SQUAL_CHAR01(1) := null;
592 l_trans_rec.SQUAL_CHAR02(1) := null;
593 l_trans_rec.SQUAL_CHAR03(1) := null;
594 l_trans_rec.SQUAL_CHAR04(1) := null;
595 l_trans_rec.SQUAL_CHAR05(1) := null;
596 l_trans_rec.SQUAL_CHAR06(1) := null;
597 l_trans_rec.SQUAL_CHAR07(1) := null;
598 l_trans_rec.SQUAL_CHAR08(1) := null;
599 l_trans_rec.SQUAL_CHAR09(1) := null;
600 l_trans_rec.SQUAL_CHAR10(1) := null;
601 l_trans_rec.SQUAL_CHAR11(1) := null;
602 l_trans_rec.SQUAL_CHAR12(1) := null;
603 l_trans_rec.SQUAL_CHAR13(1) := null;
604 l_trans_rec.SQUAL_CHAR14(1) := null;
605 l_trans_rec.SQUAL_CHAR15(1) := null;
606 l_trans_rec.SQUAL_CHAR16(1) := null;
607 l_trans_rec.SQUAL_CHAR17(1) := null;
608 l_trans_rec.SQUAL_CHAR18(1) := null;
609 l_trans_rec.SQUAL_CHAR19(1) := null;
610 l_trans_rec.SQUAL_CHAR20(1) := null;
611 l_trans_rec.SQUAL_CHAR21(1) := null;
612 l_trans_rec.SQUAL_CHAR22(1) := null;
613 l_trans_rec.SQUAL_CHAR23(1) := null;
614 l_trans_rec.SQUAL_CHAR24(1) := null;
615 l_trans_rec.SQUAL_CHAR25(1) := null;
616 l_trans_rec.SQUAL_CHAR26(1) := null;
617 l_trans_rec.SQUAL_CHAR27(1) := null;
618 l_trans_rec.SQUAL_CHAR28(1) := null;
619 l_trans_rec.SQUAL_CHAR29(1) := null;
620 l_trans_rec.SQUAL_CHAR30(1) := null;
621 l_trans_rec.SQUAL_CHAR31(1) := null;
622 l_trans_rec.SQUAL_CHAR32(1) := null;
623 l_trans_rec.SQUAL_CHAR33(1) := null;
624 l_trans_rec.SQUAL_CHAR34(1) := null;
625 l_trans_rec.SQUAL_CHAR35(1) := null;
626 l_trans_rec.SQUAL_CHAR36(1) := null;
627 l_trans_rec.SQUAL_CHAR37(1) := null;
628 l_trans_rec.SQUAL_CHAR38(1) := null;
629 l_trans_rec.SQUAL_CHAR39(1) := null;
630 l_trans_rec.SQUAL_CHAR40(1) := null;
631 l_trans_rec.SQUAL_CHAR41(1) := null;
632 l_trans_rec.SQUAL_CHAR42(1) := null;
633 l_trans_rec.SQUAL_CHAR43(1) := null;
634 l_trans_rec.SQUAL_CHAR44(1) := null;
635 l_trans_rec.SQUAL_CHAR45(1) := null;
636 l_trans_rec.SQUAL_CHAR46(1) := null;
637 l_trans_rec.SQUAL_CHAR47(1) := null;
638 l_trans_rec.SQUAL_CHAR48(1) := null;
639 l_trans_rec.SQUAL_CHAR49(1) := null;
640 l_trans_rec.SQUAL_CHAR50(1) := null;
641
642 OPEN cur_is_terr_setup;
643 FETCH cur_is_terr_setup INTO l_count;
644 CLOSE cur_is_terr_setup;
645
646 IF l_count > 0 THEN
647
648 IF p_party_id = Fnd_Api.g_miss_num
649 OR p_party_id IS NULL THEN
650 OPEN cur_get_party_id(p_sold_to_org);
651 FETCH cur_get_party_id INTO l_party_id;
652 CLOSE cur_get_party_id;
653 ELSE
654 l_party_id := p_party_id;
655 END IF;
656
657 OPEN cur_get_party_info(l_party_id);
658 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);
659 CLOSE cur_get_party_info;
660
661 oe_debug_pub.add('Trade MGR: before calling get_winners: city ' || l_trans_rec.SQUAL_CHAR02(1));
662 oe_debug_pub.add('Trade MGR: before calling get_winners: zipcode ' || l_trans_rec.SQUAL_CHAR06(1));
663 oe_debug_pub.add('Trade MGR: before calling get_winners: state ' || l_trans_rec.SQUAL_CHAR04(1));
664 oe_debug_pub.add('Trade MGR: before calling get_winners: county ' || l_trans_rec.SQUAL_CHAR03(1));
665 oe_debug_pub.add('Trade MGR: before calling get_winners: country ' || l_trans_rec.SQUAL_CHAR07(1));
666 oe_debug_pub.add('Trade MGR: before calling get_winners: party_id ' || l_trans_rec.SQUAL_NUM01(1));
667
668 JTF_TERR_ASSIGN_PUB.get_winners
669 ( p_api_version_number => l_api_version,
670 p_init_msg_list => FND_API.G_TRUE,
671 p_use_type => 'RESOURCE',
672 p_source_id => -1003,
673 p_trans_id => -1007,
674 p_trans_rec => l_trans_rec,
675 p_resource_type => FND_API.G_MISS_CHAR,
676 p_role => FND_API.G_MISS_CHAR,
677 p_top_level_terr_id => FND_API.G_MISS_NUM,
678 p_num_winners => FND_API.G_MISS_NUM,
679 x_return_status => l_return_status,
680 x_msg_count => l_msg_count,
681 x_msg_data => l_msg_data,
682 x_winners_rec => l_winner_rec
683 );
684 oe_debug_pub.add('Trade MGR: after calling get_winners: status ' || l_return_status);
685 oe_debug_pub.add('Trade MGR: terr count ' || l_winner_rec.terr_id.COUNT);
686 END IF;
687
688 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
689 FOR i IN NVL(l_winner_rec.terr_id.FIRST, 1)..NVL(l_winner_rec.terr_id.LAST, 0) LOOP
690 l_multirecord(i) := l_winner_rec.terr_id(i);
691 oe_debug_pub.add('Trade MGR: terr_id(' || i || ') ' || l_multirecord(i));
692 END LOOP;
693 END IF;
694 RETURN l_multirecord;
695
696 END Find_TM_Territories;
697
698
699 --------------- start of comments --------------------------
700 -- NAME
701 -- Find_TM_Territories
702 --
703 -- USAGE
704 -- Overload function will return the winning territories ID
705 -- for trade management
706 -- NOTES
707 --
708 -- HISTORY
709 -- 28-OCT-2001 julou created
710 -- End of Comments
711 --
712 --------------- end of comments ----------------------------
713 FUNCTION Find_TM_Territories
714 (
715 p_party_id IN NUMBER
716 ,p_sold_to_org IN NUMBER
717 ,p_ship_to_org IN NUMBER
718 ,p_bill_to_org IN NUMBER
719 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
720 IS
721
722 l_api_version CONSTANT NUMBER := 1.0;
723 l_api_name CONSTANT VARCHAR2(30) := 'Find_TM_Territories';
724 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
725
726 CURSOR cur_get_party_info(p_party_id NUMBER) IS
727 SELECT city,postal_code,state,county,country,party_id,province,category_code
728 FROM hz_parties
729 WHERE party_id = p_party_id;
730
731 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
732 SELECT party_id
733 FROM hz_cust_accounts
734 WHERE cust_account_id = p_sold_to_org
735 AND status = 'A';
736
737 CURSOR c_sales_channel(p_cust_acct_id NUMBER) IS
738 SELECT sales_channel_code
739 FROM hz_cust_accounts
740 WHERE cust_account_id = p_cust_acct_id
741 and status = 'A';
742
743 CURSOR c_cust_profile(p_sold_to NUMBER, p_ship_to NUMBER, p_bill_to NUMBER) IS
744 SELECT profile_class_id
745 FROM hz_customer_profiles
746 WHERE cust_account_id = p_sold_to
747 AND status = 'A'
748 AND site_use_id IN (p_ship_to, p_bill_to);
749 /*
750 CURSOR c_site_use_code(p_site_use_id NUMBER) IS
751 SELECT site_use_code
752 FROM hz_cust_site_uses_all
753 WHERE site_use_id = p_site_use_id;
754
755 l_index NUMBER := 0;
756 */
757 CURSOR cur_is_terr_setup IS
758 SELECT /*+ ORDERED */ -- julou sql performance fix
759 count(*)
760 FROM jtf_terr jt, jtf_terr_qtype_usgs jtqu, jtf_qual_type_usgs jqtu
761 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
762 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
763 jt.end_date_active IS NULL ))
764 AND jt.terr_id = jtqu.terr_id
765 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
766 AND jqtu.source_id = -1003
767 AND jqtu.qual_type_id = -1007;
768
769 CURSOR c_party_site_id(p_ship_to_org_id NUMBER) IS
770 SELECT hzcasa.party_site_id
771 FROM hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa
772 WHERE hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id
773 AND hzcsua.site_use_id = p_ship_to_org_id;
774
775 l_party_id NUMBER;
776 l_trx_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
777 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
778 l_terr_name VARCHAR2(120);
779 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
780
781 l_return_status VARCHAR2(1);
782 l_msg_data VARCHAR2(2000);
783 l_msg_count NUMBER;
784 l_count NUMBER;
785 BEGIN
786 -- initializing
787 --apps.FND_MSG_PUB.initialize;
788 l_return_status := Fnd_Api.g_ret_sts_success;
789
790 -- territory rec
791 l_trx_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
792 l_trx_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
793
794 -- extend qualifier elements
795 l_trx_rec.SQUAL_NUM01.EXTEND;
796 l_trx_rec.SQUAL_NUM02.EXTEND;
797 l_trx_rec.SQUAL_NUM03.EXTEND;
798 l_trx_rec.SQUAL_NUM04.EXTEND;
799 l_trx_rec.SQUAL_NUM05.EXTEND;
800 l_trx_rec.SQUAL_NUM06.EXTEND;
801 l_trx_rec.SQUAL_NUM07.EXTEND;
802 l_trx_rec.SQUAL_NUM08.EXTEND;
803 l_trx_rec.SQUAL_NUM09.EXTEND;
804 l_trx_rec.SQUAL_NUM10.EXTEND;
805 l_trx_rec.SQUAL_NUM11.EXTEND;
806 l_trx_rec.SQUAL_NUM12.EXTEND;
807 l_trx_rec.SQUAL_NUM13.EXTEND;
808 l_trx_rec.SQUAL_NUM14.EXTEND;
809 l_trx_rec.SQUAL_NUM15.EXTEND;
810 l_trx_rec.SQUAL_NUM16.EXTEND;
811 l_trx_rec.SQUAL_NUM17.EXTEND;
812 l_trx_rec.SQUAL_NUM18.EXTEND;
813 l_trx_rec.SQUAL_NUM19.EXTEND;
814 l_trx_rec.SQUAL_NUM20.EXTEND;
815 l_trx_rec.SQUAL_NUM21.EXTEND;
816 l_trx_rec.SQUAL_NUM22.EXTEND;
817 l_trx_rec.SQUAL_NUM23.EXTEND;
818 l_trx_rec.SQUAL_NUM24.EXTEND;
819 l_trx_rec.SQUAL_NUM25.EXTEND;
820 l_trx_rec.SQUAL_NUM26.EXTEND;
821 l_trx_rec.SQUAL_NUM27.EXTEND;
822 l_trx_rec.SQUAL_NUM28.EXTEND;
823 l_trx_rec.SQUAL_NUM29.EXTEND;
824 l_trx_rec.SQUAL_NUM30.EXTEND;
825 l_trx_rec.SQUAL_NUM31.EXTEND;
826 l_trx_rec.SQUAL_NUM32.EXTEND;
827 l_trx_rec.SQUAL_NUM33.EXTEND;
828 l_trx_rec.SQUAL_NUM34.EXTEND;
829 l_trx_rec.SQUAL_NUM35.EXTEND;
830 l_trx_rec.SQUAL_NUM36.EXTEND;
831 l_trx_rec.SQUAL_NUM37.EXTEND;
832 l_trx_rec.SQUAL_NUM38.EXTEND;
833 l_trx_rec.SQUAL_NUM39.EXTEND;
834 l_trx_rec.SQUAL_NUM40.EXTEND;
835 l_trx_rec.SQUAL_NUM41.EXTEND;
836 l_trx_rec.SQUAL_NUM42.EXTEND;
837 l_trx_rec.SQUAL_NUM43.EXTEND;
838 l_trx_rec.SQUAL_NUM44.EXTEND;
839 l_trx_rec.SQUAL_NUM45.EXTEND;
840 l_trx_rec.SQUAL_NUM46.EXTEND;
841 l_trx_rec.SQUAL_NUM47.EXTEND;
842 l_trx_rec.SQUAL_NUM48.EXTEND;
843 l_trx_rec.SQUAL_NUM49.EXTEND;
844 l_trx_rec.SQUAL_NUM50.EXTEND;
845
846 l_trx_rec.SQUAL_CHAR01.EXTEND;
847 l_trx_rec.SQUAL_CHAR02.EXTEND;
848 l_trx_rec.SQUAL_CHAR03.EXTEND;
849 l_trx_rec.SQUAL_CHAR04.EXTEND;
850 l_trx_rec.SQUAL_CHAR05.EXTEND;
851 l_trx_rec.SQUAL_CHAR06.EXTEND;
852 l_trx_rec.SQUAL_CHAR07.EXTEND;
853 l_trx_rec.SQUAL_CHAR08.EXTEND;
854 l_trx_rec.SQUAL_CHAR09.EXTEND;
855 l_trx_rec.SQUAL_CHAR10.EXTEND;
856 l_trx_rec.SQUAL_CHAR11.EXTEND;
857 l_trx_rec.SQUAL_CHAR12.EXTEND;
858 l_trx_rec.SQUAL_CHAR13.EXTEND;
859 l_trx_rec.SQUAL_CHAR14.EXTEND;
860 l_trx_rec.SQUAL_CHAR15.EXTEND;
861 l_trx_rec.SQUAL_CHAR16.EXTEND;
862 l_trx_rec.SQUAL_CHAR17.EXTEND;
863 l_trx_rec.SQUAL_CHAR18.EXTEND;
864 l_trx_rec.SQUAL_CHAR19.EXTEND;
865 l_trx_rec.SQUAL_CHAR20.EXTEND;
866 l_trx_rec.SQUAL_CHAR21.EXTEND;
867 l_trx_rec.SQUAL_CHAR22.EXTEND;
868 l_trx_rec.SQUAL_CHAR23.EXTEND;
869 l_trx_rec.SQUAL_CHAR24.EXTEND;
870 l_trx_rec.SQUAL_CHAR25.EXTEND;
871 l_trx_rec.SQUAL_CHAR26.EXTEND;
872 l_trx_rec.SQUAL_CHAR27.EXTEND;
873 l_trx_rec.SQUAL_CHAR28.EXTEND;
874 l_trx_rec.SQUAL_CHAR29.EXTEND;
875 l_trx_rec.SQUAL_CHAR30.EXTEND;
876 l_trx_rec.SQUAL_CHAR31.EXTEND;
877 l_trx_rec.SQUAL_CHAR32.EXTEND;
878 l_trx_rec.SQUAL_CHAR33.EXTEND;
879 l_trx_rec.SQUAL_CHAR34.EXTEND;
880 l_trx_rec.SQUAL_CHAR35.EXTEND;
881 l_trx_rec.SQUAL_CHAR36.EXTEND;
882 l_trx_rec.SQUAL_CHAR37.EXTEND;
883 l_trx_rec.SQUAL_CHAR38.EXTEND;
884 l_trx_rec.SQUAL_CHAR39.EXTEND;
885 l_trx_rec.SQUAL_CHAR40.EXTEND;
886 l_trx_rec.SQUAL_CHAR41.EXTEND;
887 l_trx_rec.SQUAL_CHAR42.EXTEND;
888 l_trx_rec.SQUAL_CHAR43.EXTEND;
889 l_trx_rec.SQUAL_CHAR44.EXTEND;
890 l_trx_rec.SQUAL_CHAR45.EXTEND;
891 l_trx_rec.SQUAL_CHAR46.EXTEND;
892 l_trx_rec.SQUAL_CHAR47.EXTEND;
893 l_trx_rec.SQUAL_CHAR48.EXTEND;
894 l_trx_rec.SQUAL_CHAR49.EXTEND;
895 l_trx_rec.SQUAL_CHAR50.EXTEND;
896
897 -- transaction qualifier values
898 l_trx_rec.SQUAL_NUM01(1) := null;
899 l_trx_rec.SQUAL_NUM02(1) := null;
900 l_trx_rec.SQUAL_NUM03(1) := null;
901 l_trx_rec.SQUAL_NUM04(1) := null;
902 l_trx_rec.SQUAL_NUM05(1) := null;
903 l_trx_rec.SQUAL_NUM06(1) := null;
904 l_trx_rec.SQUAL_NUM07(1) := null;
905 l_trx_rec.SQUAL_NUM08(1) := null;
906 l_trx_rec.SQUAL_NUM09(1) := null;
907 l_trx_rec.SQUAL_NUM10(1) := null;
908 l_trx_rec.SQUAL_NUM11(1) := null;
909 l_trx_rec.SQUAL_NUM12(1) := null;
910 l_trx_rec.SQUAL_NUM13(1) := null;
911 l_trx_rec.SQUAL_NUM14(1) := null;
912 l_trx_rec.SQUAL_NUM15(1) := null;
913 l_trx_rec.SQUAL_NUM16(1) := null;
914 l_trx_rec.SQUAL_NUM17(1) := null;
915 l_trx_rec.SQUAL_NUM18(1) := null;
916 l_trx_rec.SQUAL_NUM19(1) := null;
917 l_trx_rec.SQUAL_NUM20(1) := null;
918 l_trx_rec.SQUAL_NUM21(1) := null;
919 l_trx_rec.SQUAL_NUM22(1) := null;
920 l_trx_rec.SQUAL_NUM23(1) := null;
921 l_trx_rec.SQUAL_NUM24(1) := null;
922 l_trx_rec.SQUAL_NUM25(1) := null;
923 l_trx_rec.SQUAL_NUM26(1) := null;
924 l_trx_rec.SQUAL_NUM27(1) := null;
925 l_trx_rec.SQUAL_NUM28(1) := null;
926 l_trx_rec.SQUAL_NUM29(1) := null;
927 l_trx_rec.SQUAL_NUM30(1) := null;
928 l_trx_rec.SQUAL_NUM31(1) := null;
929 l_trx_rec.SQUAL_NUM32(1) := null;
930 l_trx_rec.SQUAL_NUM33(1) := null;
931 l_trx_rec.SQUAL_NUM34(1) := null;
932 l_trx_rec.SQUAL_NUM35(1) := null;
933 l_trx_rec.SQUAL_NUM36(1) := null;
934 l_trx_rec.SQUAL_NUM37(1) := null;
935 l_trx_rec.SQUAL_NUM38(1) := null;
936 l_trx_rec.SQUAL_NUM39(1) := null;
937 l_trx_rec.SQUAL_NUM40(1) := null;
938 l_trx_rec.SQUAL_NUM41(1) := null;
939 l_trx_rec.SQUAL_NUM42(1) := null;
940 l_trx_rec.SQUAL_NUM43(1) := null;
941 l_trx_rec.SQUAL_NUM44(1) := null;
942 l_trx_rec.SQUAL_NUM45(1) := null;
943 l_trx_rec.SQUAL_NUM46(1) := null;
944 l_trx_rec.SQUAL_NUM47(1) := null;
945 l_trx_rec.SQUAL_NUM48(1) := null;
946 l_trx_rec.SQUAL_NUM49(1) := null;
947 l_trx_rec.SQUAL_NUM50(1) := null;
948
949 l_trx_rec.SQUAL_CHAR01(1) := null;
950 l_trx_rec.SQUAL_CHAR02(1) := null;
951 l_trx_rec.SQUAL_CHAR03(1) := null;
952 l_trx_rec.SQUAL_CHAR04(1) := null;
953 l_trx_rec.SQUAL_CHAR05(1) := null;
954 l_trx_rec.SQUAL_CHAR06(1) := null;
955 l_trx_rec.SQUAL_CHAR07(1) := null;
956 l_trx_rec.SQUAL_CHAR08(1) := null;
957 l_trx_rec.SQUAL_CHAR09(1) := null;
958 l_trx_rec.SQUAL_CHAR10(1) := null;
959 l_trx_rec.SQUAL_CHAR11(1) := null;
960 l_trx_rec.SQUAL_CHAR12(1) := null;
961 l_trx_rec.SQUAL_CHAR13(1) := null;
962 l_trx_rec.SQUAL_CHAR14(1) := null;
963 l_trx_rec.SQUAL_CHAR15(1) := null;
964 l_trx_rec.SQUAL_CHAR16(1) := null;
965 l_trx_rec.SQUAL_CHAR17(1) := null;
966 l_trx_rec.SQUAL_CHAR18(1) := null;
967 l_trx_rec.SQUAL_CHAR19(1) := null;
968 l_trx_rec.SQUAL_CHAR20(1) := null;
969 l_trx_rec.SQUAL_CHAR21(1) := null;
970 l_trx_rec.SQUAL_CHAR22(1) := null;
971 l_trx_rec.SQUAL_CHAR23(1) := null;
972 l_trx_rec.SQUAL_CHAR24(1) := null;
973 l_trx_rec.SQUAL_CHAR25(1) := null;
974 l_trx_rec.SQUAL_CHAR26(1) := null;
975 l_trx_rec.SQUAL_CHAR27(1) := null;
976 l_trx_rec.SQUAL_CHAR28(1) := null;
977 l_trx_rec.SQUAL_CHAR29(1) := null;
978 l_trx_rec.SQUAL_CHAR30(1) := null;
979 l_trx_rec.SQUAL_CHAR31(1) := null;
980 l_trx_rec.SQUAL_CHAR32(1) := null;
981 l_trx_rec.SQUAL_CHAR33(1) := null;
982 l_trx_rec.SQUAL_CHAR34(1) := null;
983 l_trx_rec.SQUAL_CHAR35(1) := null;
984 l_trx_rec.SQUAL_CHAR36(1) := null;
985 l_trx_rec.SQUAL_CHAR37(1) := null;
986 l_trx_rec.SQUAL_CHAR38(1) := null;
987 l_trx_rec.SQUAL_CHAR39(1) := null;
988 l_trx_rec.SQUAL_CHAR40(1) := null;
989 l_trx_rec.SQUAL_CHAR41(1) := null;
990 l_trx_rec.SQUAL_CHAR42(1) := null;
991 l_trx_rec.SQUAL_CHAR43(1) := null;
992 l_trx_rec.SQUAL_CHAR44(1) := null;
993 l_trx_rec.SQUAL_CHAR45(1) := null;
994 l_trx_rec.SQUAL_CHAR46(1) := null;
995 l_trx_rec.SQUAL_CHAR47(1) := null;
996 l_trx_rec.SQUAL_CHAR48(1) := null;
997 l_trx_rec.SQUAL_CHAR49(1) := null;
998 l_trx_rec.SQUAL_CHAR50(1) := null;
999
1000 OPEN cur_is_terr_setup;
1001 FETCH cur_is_terr_setup INTO l_count;
1002 CLOSE cur_is_terr_setup;
1003
1004 IF l_count > 0 THEN
1005
1006 IF p_party_id = Fnd_Api.g_miss_num
1007 OR p_party_id IS NULL THEN
1008 OPEN cur_get_party_id(p_sold_to_org);
1009 FETCH cur_get_party_id INTO l_party_id;
1010 CLOSE cur_get_party_id;
1011 ELSE
1012 l_party_id := p_party_id;
1013 END IF;
1014
1015 OPEN cur_get_party_info(l_party_id);
1016 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);
1017 CLOSE cur_get_party_info;
1018
1019 OPEN c_party_site_id(p_ship_to_org);
1020 FETCH c_party_site_id INTO l_trx_rec.SQUAL_NUM02(1);
1021 CLOSE c_party_site_id;
1022
1023 OPEN c_sales_channel(p_sold_to_org);
1024 FETCH c_sales_channel INTO l_trx_rec.squal_char16(1);
1025 CLOSE c_sales_channel;
1026
1027 OPEN c_cust_profile(p_sold_to_org, p_ship_to_org, p_bill_to_org);
1028 FETCH c_cust_profile INTO l_trx_rec.squal_num15(1);
1029 CLOSE c_cust_profile;
1030 /*
1031 IF p_bill_to_org IS NOT NULL AND p_bill_to_org <> fnd_api.g_miss_num THEN
1032 l_index := l_index + 1;
1033 l_trx_rec.squal_char17.EXTEND;
1034 OPEN c_site_use_code(p_bill_to_org);
1035 FETCH c_site_use_code INTO l_trx_rec.squal_char17(l_index);
1036 CLOSE c_site_use_code;
1037 END IF;
1038
1039 IF p_ship_to_org IS NOT NULL AND p_ship_to_org <> fnd_api.g_miss_num THEN
1040 l_index := l_index + 1;
1041 l_trx_rec.squal_char17.EXTEND;
1042 OPEN c_site_use_code(p_ship_to_org);
1043 FETCH c_site_use_code INTO l_trx_rec.squal_char17(l_index);
1044 CLOSE c_site_use_code;
1045 END IF;
1046 */
1047 JTF_TERR_ASSIGN_PUB.get_winners
1048 ( p_api_version_number => l_api_version,
1049 p_init_msg_list => FND_API.G_TRUE,
1050 p_use_type => 'RESOURCE',
1051 p_source_id => -1003,
1052 p_trans_id => -1007,
1053 p_trans_rec => l_trx_rec,
1054 p_resource_type => FND_API.G_MISS_CHAR,
1055 p_role => FND_API.G_MISS_CHAR,
1056 p_top_level_terr_id => FND_API.G_MISS_NUM,
1057 p_num_winners => FND_API.G_MISS_NUM,
1058 x_return_status => l_return_status,
1059 x_msg_count => l_msg_count,
1060 x_msg_data => l_msg_data,
1061 x_winners_rec => l_winner_rec
1062 );
1063 END IF;
1064
1065 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
1066 FOR i IN 1..l_winner_rec.terr_id.COUNT LOOP
1067 l_multirecord(i) := l_winner_rec.terr_id(i);
1068 END LOOP;
1069 END IF;
1070 RETURN l_multirecord;
1071
1072 END Find_TM_Territories;
1073
1074
1075 --------------- start of comments --------------------------
1076 -- NAME
1077 -- Find_SA_Territories
1078 --
1079 -- USAGE
1080 -- Function will return the winning territories ID
1081 -- for sales account
1082 -- NOTES
1083 --
1084 -- HISTORY
1085 -- 28-OCT-2001 julou created
1086 -- End of Comments
1087 --
1088 --------------- end of comments ----------------------------
1089 FUNCTION Find_SA_Territories
1090 (
1091 p_party_id IN NUMBER
1092 ,p_sold_to_org IN NUMBER
1093 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
1094 IS
1095
1096 l_api_version CONSTANT NUMBER := 1.0;
1097 l_api_name CONSTANT VARCHAR2(30) := 'Find_SA_Territories';
1098 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1099
1100 CURSOR cur_get_party_info(p_party_id NUMBER) IS
1101 SELECT city,postal_code,state,county,country,party_id
1102 FROM hz_parties
1103 WHERE party_id = p_party_id;
1104
1105 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
1106 SELECT party_id
1107 FROM hz_cust_accounts
1108 WHERE cust_account_id = p_sold_to_org;
1109
1110 CURSOR cur_is_terr_setup IS
1111 SELECT /*+ ORDERED */ -- julou sql performance fix
1112 count(*)
1113 FROM jtf_terr_all jt, jtf_terr_qtype_usgs_all jtqu,jtf_qual_type_usgs jqtu
1114 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
1115 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
1116 jt.end_date_active IS NULL ))
1117 AND jt.terr_id = jtqu.terr_id
1118 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
1119 AND jqtu.source_id = -1001
1120 AND jqtu.qual_type_id = -1002;
1121
1122 l_party_id NUMBER;
1123 l_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
1124 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
1125 l_terr_name VARCHAR2(120);
1126 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1127
1128 l_return_status VARCHAR2(1);
1129 l_msg_data VARCHAR2(2000);
1130 l_msg_count NUMBER;
1131 l_count NUMBER;
1132
1133 BEGIN
1134 -- initializing
1135 --apps.FND_MSG_PUB.initialize;
1136 l_return_status := Fnd_Api.g_ret_sts_success;
1137
1138 -- territory rec
1139 l_trans_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
1140 l_trans_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
1141
1142 -- extend qualifier elements
1143 l_trans_rec.SQUAL_NUM01.EXTEND;
1144 l_trans_rec.SQUAL_NUM02.EXTEND;
1145 l_trans_rec.SQUAL_NUM03.EXTEND;
1146 l_trans_rec.SQUAL_NUM04.EXTEND;
1147 l_trans_rec.SQUAL_NUM05.EXTEND;
1148 l_trans_rec.SQUAL_NUM06.EXTEND;
1149 l_trans_rec.SQUAL_NUM07.EXTEND;
1150 l_trans_rec.SQUAL_NUM08.EXTEND;
1151 l_trans_rec.SQUAL_NUM09.EXTEND;
1152 l_trans_rec.SQUAL_NUM10.EXTEND;
1153 l_trans_rec.SQUAL_NUM11.EXTEND;
1154 l_trans_rec.SQUAL_NUM12.EXTEND;
1155 l_trans_rec.SQUAL_NUM13.EXTEND;
1156 l_trans_rec.SQUAL_NUM14.EXTEND;
1157 l_trans_rec.SQUAL_NUM15.EXTEND;
1158 l_trans_rec.SQUAL_NUM16.EXTEND;
1159 l_trans_rec.SQUAL_NUM17.EXTEND;
1160 l_trans_rec.SQUAL_NUM18.EXTEND;
1161 l_trans_rec.SQUAL_NUM19.EXTEND;
1162 l_trans_rec.SQUAL_NUM20.EXTEND;
1163 l_trans_rec.SQUAL_NUM21.EXTEND;
1164 l_trans_rec.SQUAL_NUM22.EXTEND;
1165 l_trans_rec.SQUAL_NUM23.EXTEND;
1166 l_trans_rec.SQUAL_NUM24.EXTEND;
1167 l_trans_rec.SQUAL_NUM25.EXTEND;
1168 l_trans_rec.SQUAL_NUM26.EXTEND;
1169 l_trans_rec.SQUAL_NUM27.EXTEND;
1170 l_trans_rec.SQUAL_NUM28.EXTEND;
1171 l_trans_rec.SQUAL_NUM29.EXTEND;
1172 l_trans_rec.SQUAL_NUM30.EXTEND;
1173 l_trans_rec.SQUAL_NUM31.EXTEND;
1174 l_trans_rec.SQUAL_NUM32.EXTEND;
1175 l_trans_rec.SQUAL_NUM33.EXTEND;
1176 l_trans_rec.SQUAL_NUM34.EXTEND;
1177 l_trans_rec.SQUAL_NUM35.EXTEND;
1178 l_trans_rec.SQUAL_NUM36.EXTEND;
1179 l_trans_rec.SQUAL_NUM37.EXTEND;
1180 l_trans_rec.SQUAL_NUM38.EXTEND;
1181 l_trans_rec.SQUAL_NUM39.EXTEND;
1182 l_trans_rec.SQUAL_NUM40.EXTEND;
1183 l_trans_rec.SQUAL_NUM41.EXTEND;
1184 l_trans_rec.SQUAL_NUM42.EXTEND;
1185 l_trans_rec.SQUAL_NUM43.EXTEND;
1186 l_trans_rec.SQUAL_NUM44.EXTEND;
1187 l_trans_rec.SQUAL_NUM45.EXTEND;
1188 l_trans_rec.SQUAL_NUM46.EXTEND;
1189 l_trans_rec.SQUAL_NUM47.EXTEND;
1190 l_trans_rec.SQUAL_NUM48.EXTEND;
1191 l_trans_rec.SQUAL_NUM49.EXTEND;
1192 l_trans_rec.SQUAL_NUM50.EXTEND;
1193
1194 l_trans_rec.SQUAL_CHAR01.EXTEND;
1195 l_trans_rec.SQUAL_CHAR02.EXTEND;
1196 l_trans_rec.SQUAL_CHAR03.EXTEND;
1197 l_trans_rec.SQUAL_CHAR04.EXTEND;
1198 l_trans_rec.SQUAL_CHAR05.EXTEND;
1199 l_trans_rec.SQUAL_CHAR06.EXTEND;
1200 l_trans_rec.SQUAL_CHAR07.EXTEND;
1201 l_trans_rec.SQUAL_CHAR08.EXTEND;
1202 l_trans_rec.SQUAL_CHAR09.EXTEND;
1203 l_trans_rec.SQUAL_CHAR10.EXTEND;
1204 l_trans_rec.SQUAL_CHAR11.EXTEND;
1205 l_trans_rec.SQUAL_CHAR12.EXTEND;
1206 l_trans_rec.SQUAL_CHAR13.EXTEND;
1207 l_trans_rec.SQUAL_CHAR14.EXTEND;
1208 l_trans_rec.SQUAL_CHAR15.EXTEND;
1209 l_trans_rec.SQUAL_CHAR16.EXTEND;
1210 l_trans_rec.SQUAL_CHAR17.EXTEND;
1211 l_trans_rec.SQUAL_CHAR18.EXTEND;
1212 l_trans_rec.SQUAL_CHAR19.EXTEND;
1213 l_trans_rec.SQUAL_CHAR20.EXTEND;
1214 l_trans_rec.SQUAL_CHAR21.EXTEND;
1215 l_trans_rec.SQUAL_CHAR22.EXTEND;
1216 l_trans_rec.SQUAL_CHAR23.EXTEND;
1217 l_trans_rec.SQUAL_CHAR24.EXTEND;
1218 l_trans_rec.SQUAL_CHAR25.EXTEND;
1219 l_trans_rec.SQUAL_CHAR26.EXTEND;
1220 l_trans_rec.SQUAL_CHAR27.EXTEND;
1221 l_trans_rec.SQUAL_CHAR28.EXTEND;
1222 l_trans_rec.SQUAL_CHAR29.EXTEND;
1223 l_trans_rec.SQUAL_CHAR30.EXTEND;
1224 l_trans_rec.SQUAL_CHAR31.EXTEND;
1225 l_trans_rec.SQUAL_CHAR32.EXTEND;
1226 l_trans_rec.SQUAL_CHAR33.EXTEND;
1227 l_trans_rec.SQUAL_CHAR34.EXTEND;
1228 l_trans_rec.SQUAL_CHAR35.EXTEND;
1229 l_trans_rec.SQUAL_CHAR36.EXTEND;
1230 l_trans_rec.SQUAL_CHAR37.EXTEND;
1231 l_trans_rec.SQUAL_CHAR38.EXTEND;
1232 l_trans_rec.SQUAL_CHAR39.EXTEND;
1233 l_trans_rec.SQUAL_CHAR40.EXTEND;
1234 l_trans_rec.SQUAL_CHAR41.EXTEND;
1235 l_trans_rec.SQUAL_CHAR42.EXTEND;
1236 l_trans_rec.SQUAL_CHAR43.EXTEND;
1237 l_trans_rec.SQUAL_CHAR44.EXTEND;
1238 l_trans_rec.SQUAL_CHAR45.EXTEND;
1239 l_trans_rec.SQUAL_CHAR46.EXTEND;
1240 l_trans_rec.SQUAL_CHAR47.EXTEND;
1241 l_trans_rec.SQUAL_CHAR48.EXTEND;
1242 l_trans_rec.SQUAL_CHAR49.EXTEND;
1243 l_trans_rec.SQUAL_CHAR50.EXTEND;
1244
1245 -- transaction qualifier values
1246 l_trans_rec.SQUAL_NUM01(1) := null;
1247 l_trans_rec.SQUAL_NUM02(1) := null;
1248 l_trans_rec.SQUAL_NUM03(1) := null;
1249 l_trans_rec.SQUAL_NUM04(1) := null;
1250 l_trans_rec.SQUAL_NUM05(1) := null;
1251 l_trans_rec.SQUAL_NUM06(1) := null;
1252 l_trans_rec.SQUAL_NUM07(1) := null;
1253 l_trans_rec.SQUAL_NUM08(1) := null;
1254 l_trans_rec.SQUAL_NUM09(1) := null;
1255 l_trans_rec.SQUAL_NUM10(1) := null;
1256 l_trans_rec.SQUAL_NUM11(1) := null;
1257 l_trans_rec.SQUAL_NUM12(1) := null;
1258 l_trans_rec.SQUAL_NUM13(1) := null;
1259 l_trans_rec.SQUAL_NUM14(1) := null;
1260 l_trans_rec.SQUAL_NUM15(1) := null;
1261 l_trans_rec.SQUAL_NUM16(1) := null;
1262 l_trans_rec.SQUAL_NUM17(1) := null;
1263 l_trans_rec.SQUAL_NUM18(1) := null;
1264 l_trans_rec.SQUAL_NUM19(1) := null;
1265 l_trans_rec.SQUAL_NUM20(1) := null;
1266 l_trans_rec.SQUAL_NUM21(1) := null;
1267 l_trans_rec.SQUAL_NUM22(1) := null;
1268 l_trans_rec.SQUAL_NUM23(1) := null;
1269 l_trans_rec.SQUAL_NUM24(1) := null;
1270 l_trans_rec.SQUAL_NUM25(1) := null;
1271 l_trans_rec.SQUAL_NUM26(1) := null;
1272 l_trans_rec.SQUAL_NUM27(1) := null;
1273 l_trans_rec.SQUAL_NUM28(1) := null;
1274 l_trans_rec.SQUAL_NUM29(1) := null;
1275 l_trans_rec.SQUAL_NUM30(1) := null;
1276 l_trans_rec.SQUAL_NUM31(1) := null;
1277 l_trans_rec.SQUAL_NUM32(1) := null;
1278 l_trans_rec.SQUAL_NUM33(1) := null;
1279 l_trans_rec.SQUAL_NUM34(1) := null;
1280 l_trans_rec.SQUAL_NUM35(1) := null;
1281 l_trans_rec.SQUAL_NUM36(1) := null;
1282 l_trans_rec.SQUAL_NUM37(1) := null;
1283 l_trans_rec.SQUAL_NUM38(1) := null;
1284 l_trans_rec.SQUAL_NUM39(1) := null;
1285 l_trans_rec.SQUAL_NUM40(1) := null;
1286 l_trans_rec.SQUAL_NUM41(1) := null;
1287 l_trans_rec.SQUAL_NUM42(1) := null;
1288 l_trans_rec.SQUAL_NUM43(1) := null;
1289 l_trans_rec.SQUAL_NUM44(1) := null;
1290 l_trans_rec.SQUAL_NUM45(1) := null;
1291 l_trans_rec.SQUAL_NUM46(1) := null;
1292 l_trans_rec.SQUAL_NUM47(1) := null;
1293 l_trans_rec.SQUAL_NUM48(1) := null;
1294 l_trans_rec.SQUAL_NUM49(1) := null;
1295 l_trans_rec.SQUAL_NUM50(1) := null;
1296
1297 l_trans_rec.SQUAL_CHAR01(1) := null;
1298 l_trans_rec.SQUAL_CHAR02(1) := null;
1299 l_trans_rec.SQUAL_CHAR03(1) := null;
1300 l_trans_rec.SQUAL_CHAR04(1) := null;
1301 l_trans_rec.SQUAL_CHAR05(1) := null;
1302 l_trans_rec.SQUAL_CHAR06(1) := null;
1303 l_trans_rec.SQUAL_CHAR07(1) := null;
1304 l_trans_rec.SQUAL_CHAR08(1) := null;
1305 l_trans_rec.SQUAL_CHAR09(1) := null;
1306 l_trans_rec.SQUAL_CHAR10(1) := null;
1307 l_trans_rec.SQUAL_CHAR11(1) := null;
1308 l_trans_rec.SQUAL_CHAR12(1) := null;
1309 l_trans_rec.SQUAL_CHAR13(1) := null;
1310 l_trans_rec.SQUAL_CHAR14(1) := null;
1311 l_trans_rec.SQUAL_CHAR15(1) := null;
1312 l_trans_rec.SQUAL_CHAR16(1) := null;
1313 l_trans_rec.SQUAL_CHAR17(1) := null;
1314 l_trans_rec.SQUAL_CHAR18(1) := null;
1315 l_trans_rec.SQUAL_CHAR19(1) := null;
1316 l_trans_rec.SQUAL_CHAR20(1) := null;
1317 l_trans_rec.SQUAL_CHAR21(1) := null;
1318 l_trans_rec.SQUAL_CHAR22(1) := null;
1319 l_trans_rec.SQUAL_CHAR23(1) := null;
1320 l_trans_rec.SQUAL_CHAR24(1) := null;
1321 l_trans_rec.SQUAL_CHAR25(1) := null;
1322 l_trans_rec.SQUAL_CHAR26(1) := null;
1323 l_trans_rec.SQUAL_CHAR27(1) := null;
1324 l_trans_rec.SQUAL_CHAR28(1) := null;
1325 l_trans_rec.SQUAL_CHAR29(1) := null;
1326 l_trans_rec.SQUAL_CHAR30(1) := null;
1327 l_trans_rec.SQUAL_CHAR31(1) := null;
1328 l_trans_rec.SQUAL_CHAR32(1) := null;
1329 l_trans_rec.SQUAL_CHAR33(1) := null;
1330 l_trans_rec.SQUAL_CHAR34(1) := null;
1331 l_trans_rec.SQUAL_CHAR35(1) := null;
1332 l_trans_rec.SQUAL_CHAR36(1) := null;
1333 l_trans_rec.SQUAL_CHAR37(1) := null;
1334 l_trans_rec.SQUAL_CHAR38(1) := null;
1335 l_trans_rec.SQUAL_CHAR39(1) := null;
1336 l_trans_rec.SQUAL_CHAR40(1) := null;
1337 l_trans_rec.SQUAL_CHAR41(1) := null;
1338 l_trans_rec.SQUAL_CHAR42(1) := null;
1339 l_trans_rec.SQUAL_CHAR43(1) := null;
1340 l_trans_rec.SQUAL_CHAR44(1) := null;
1341 l_trans_rec.SQUAL_CHAR45(1) := null;
1342 l_trans_rec.SQUAL_CHAR46(1) := null;
1343 l_trans_rec.SQUAL_CHAR47(1) := null;
1344 l_trans_rec.SQUAL_CHAR48(1) := null;
1345 l_trans_rec.SQUAL_CHAR49(1) := null;
1346 l_trans_rec.SQUAL_CHAR50(1) := null;
1347
1348 OPEN cur_is_terr_setup;
1349 FETCH cur_is_terr_setup INTO l_count;
1350 CLOSE cur_is_terr_setup;
1351
1352 IF l_count > 0 THEN
1353
1354 IF p_party_id = Fnd_Api.g_miss_num
1355 OR p_party_id IS NULL THEN
1356 OPEN cur_get_party_id(p_sold_to_org);
1357 FETCH cur_get_party_id INTO l_party_id;
1358 CLOSE cur_get_party_id;
1359 ELSE
1360 l_party_id := p_party_id;
1361 END IF;
1362
1363 OPEN cur_get_party_info(l_party_id);
1364 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);
1365 CLOSE cur_get_party_info;
1366 oe_debug_pub.add('Trade MGR SA: before calling get_winners: city ' || l_trans_rec.SQUAL_CHAR02(1));
1367 oe_debug_pub.add('Trade MGR SA: before calling get_winners: zipcode ' || l_trans_rec.SQUAL_CHAR06(1));
1368 oe_debug_pub.add('Trade MGR SA: before calling get_winners: state ' || l_trans_rec.SQUAL_CHAR04(1));
1369 oe_debug_pub.add('Trade MGR SA: before calling get_winners: county ' || l_trans_rec.SQUAL_CHAR03(1));
1370 oe_debug_pub.add('Trade MGR SA: before calling get_winners: country ' || l_trans_rec.SQUAL_CHAR07(1));
1371 oe_debug_pub.add('Trade MGR SA: before calling get_winners: party_id ' || l_trans_rec.SQUAL_NUM01(1));
1372
1373 JTF_TERR_ASSIGN_PUB.get_winners
1374 ( p_api_version_number => l_api_version,
1375 p_init_msg_list => FND_API.G_TRUE,
1376 p_use_type => 'RESOURCE',
1377 p_source_id => -1001,
1378 p_trans_id => -1002,
1379 p_trans_rec => l_trans_rec,
1380 p_resource_type => FND_API.G_MISS_CHAR,
1381 p_role => FND_API.G_MISS_CHAR,
1382 p_top_level_terr_id => FND_API.G_MISS_NUM,
1383 p_num_winners => FND_API.G_MISS_NUM,
1384 x_return_status => l_return_status,
1385 x_msg_count => l_msg_count,
1386 x_msg_data => l_msg_data,
1387 x_winners_rec => l_winner_rec
1388 );
1389
1390 END IF;
1391 oe_debug_pub.add('Trade MGR SA: after calling get_winners: status ' || l_return_status);
1392 oe_debug_pub.add('Trade MGR SA: terr count ' || l_winner_rec.terr_id.COUNT);
1393
1394 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
1395 FOR i IN NVL(l_winner_rec.terr_id.FIRST, 1)..NVL(l_winner_rec.terr_id.LAST, 0) LOOP
1396 l_multirecord(i) := l_winner_rec.terr_id(i);
1397 oe_debug_pub.add('Trade MGR SA: terr_id(' || i || ') ' || l_multirecord(i));
1398 END LOOP;
1399 END IF;
1400 RETURN l_multirecord;
1401
1402 END Find_SA_Territories;
1403
1404
1405 --------------- start of comments --------------------------
1406 -- NAME
1407 -- Find_SA_Territories
1408 --
1409 -- USAGE
1410 -- Overload function will return the winning territories ID
1411 -- for sales account
1412 -- NOTES
1413 --
1414 -- HISTORY
1415 -- 28-OCT-2001 julou created
1416 -- End of Comments
1417 --
1418 --------------- end of comments ----------------------------
1419 FUNCTION Find_SA_Territories
1420 (
1421 p_party_id IN NUMBER
1422 ,p_sold_to_org IN NUMBER
1423 ,p_ship_to_org IN NUMBER
1424 ,p_bill_to_org IN NUMBER
1425 ) RETURN Qp_Attr_Mapping_Pub.t_multirecord
1426 IS
1427
1428 l_api_version CONSTANT NUMBER := 1.0;
1429 l_api_name CONSTANT VARCHAR2(30) := 'Find_SA_Territories';
1430 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1431
1432 CURSOR cur_get_party_info(p_party_id NUMBER) IS
1433 SELECT city,postal_code,state,county,country,party_id
1434 FROM hz_parties
1435 WHERE party_id = p_party_id;
1436
1437 CURSOR cur_get_party_id(p_sold_to_org NUMBER) IS
1438 SELECT party_id
1439 FROM hz_cust_accounts
1440 WHERE cust_account_id = p_sold_to_org;
1441
1442 CURSOR cur_is_terr_setup IS
1443 SELECT /*+ ORDERED */ -- julou sql performance fix
1444 count(*)
1445 FROM jtf_terr jt, jtf_terr_qtype_usgs jtqu,jtf_qual_type_usgs jqtu
1446 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE) AND
1447 ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR
1448 jt.end_date_active IS NULL ))
1449 AND jt.terr_id = jtqu.terr_id
1450 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
1451 AND jqtu.source_id = -1001
1452 AND jqtu.qual_type_id = -1002;
1453
1454 CURSOR c_party_site_id(p_ship_to_org_id NUMBER) IS
1455 SELECT hzcasa.party_site_id
1456 FROM hz_cust_site_uses_all hzcsua, hz_cust_acct_sites_all hzcasa
1457 WHERE hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id
1458 AND hzcsua.site_use_id = p_ship_to_org_id;
1459
1460 l_party_id NUMBER;
1461 l_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
1462 l_winner_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
1463 l_terr_name VARCHAR2(120);
1464 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1465
1466 l_return_status VARCHAR2(1);
1467 l_msg_data VARCHAR2(2000);
1468 l_msg_count NUMBER;
1469 l_count NUMBER;
1470
1471 BEGIN
1472 -- initializing
1473 --apps.FND_MSG_PUB.initialize;
1474 l_return_status := Fnd_Api.g_ret_sts_success;
1475
1476 -- territory rec
1477 l_trans_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
1478 l_trans_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
1479
1480 -- extend qualifier elements
1481 l_trans_rec.SQUAL_NUM01.EXTEND;
1482 l_trans_rec.SQUAL_NUM02.EXTEND;
1483 l_trans_rec.SQUAL_NUM03.EXTEND;
1484 l_trans_rec.SQUAL_NUM04.EXTEND;
1485 l_trans_rec.SQUAL_NUM05.EXTEND;
1486 l_trans_rec.SQUAL_NUM06.EXTEND;
1487 l_trans_rec.SQUAL_NUM07.EXTEND;
1488 l_trans_rec.SQUAL_NUM08.EXTEND;
1489 l_trans_rec.SQUAL_NUM09.EXTEND;
1490 l_trans_rec.SQUAL_NUM10.EXTEND;
1491 l_trans_rec.SQUAL_NUM11.EXTEND;
1492 l_trans_rec.SQUAL_NUM12.EXTEND;
1493 l_trans_rec.SQUAL_NUM13.EXTEND;
1494 l_trans_rec.SQUAL_NUM14.EXTEND;
1495 l_trans_rec.SQUAL_NUM15.EXTEND;
1496 l_trans_rec.SQUAL_NUM16.EXTEND;
1497 l_trans_rec.SQUAL_NUM17.EXTEND;
1498 l_trans_rec.SQUAL_NUM18.EXTEND;
1499 l_trans_rec.SQUAL_NUM19.EXTEND;
1500 l_trans_rec.SQUAL_NUM20.EXTEND;
1501 l_trans_rec.SQUAL_NUM21.EXTEND;
1502 l_trans_rec.SQUAL_NUM22.EXTEND;
1503 l_trans_rec.SQUAL_NUM23.EXTEND;
1504 l_trans_rec.SQUAL_NUM24.EXTEND;
1505 l_trans_rec.SQUAL_NUM25.EXTEND;
1506 l_trans_rec.SQUAL_NUM26.EXTEND;
1507 l_trans_rec.SQUAL_NUM27.EXTEND;
1508 l_trans_rec.SQUAL_NUM28.EXTEND;
1509 l_trans_rec.SQUAL_NUM29.EXTEND;
1510 l_trans_rec.SQUAL_NUM30.EXTEND;
1511 l_trans_rec.SQUAL_NUM31.EXTEND;
1512 l_trans_rec.SQUAL_NUM32.EXTEND;
1513 l_trans_rec.SQUAL_NUM33.EXTEND;
1514 l_trans_rec.SQUAL_NUM34.EXTEND;
1515 l_trans_rec.SQUAL_NUM35.EXTEND;
1516 l_trans_rec.SQUAL_NUM36.EXTEND;
1517 l_trans_rec.SQUAL_NUM37.EXTEND;
1518 l_trans_rec.SQUAL_NUM38.EXTEND;
1519 l_trans_rec.SQUAL_NUM39.EXTEND;
1520 l_trans_rec.SQUAL_NUM40.EXTEND;
1521 l_trans_rec.SQUAL_NUM41.EXTEND;
1522 l_trans_rec.SQUAL_NUM42.EXTEND;
1523 l_trans_rec.SQUAL_NUM43.EXTEND;
1524 l_trans_rec.SQUAL_NUM44.EXTEND;
1525 l_trans_rec.SQUAL_NUM45.EXTEND;
1526 l_trans_rec.SQUAL_NUM46.EXTEND;
1527 l_trans_rec.SQUAL_NUM47.EXTEND;
1528 l_trans_rec.SQUAL_NUM48.EXTEND;
1529 l_trans_rec.SQUAL_NUM49.EXTEND;
1530 l_trans_rec.SQUAL_NUM50.EXTEND;
1531
1532 l_trans_rec.SQUAL_CHAR01.EXTEND;
1533 l_trans_rec.SQUAL_CHAR02.EXTEND;
1534 l_trans_rec.SQUAL_CHAR03.EXTEND;
1535 l_trans_rec.SQUAL_CHAR04.EXTEND;
1536 l_trans_rec.SQUAL_CHAR05.EXTEND;
1537 l_trans_rec.SQUAL_CHAR06.EXTEND;
1538 l_trans_rec.SQUAL_CHAR07.EXTEND;
1539 l_trans_rec.SQUAL_CHAR08.EXTEND;
1540 l_trans_rec.SQUAL_CHAR09.EXTEND;
1541 l_trans_rec.SQUAL_CHAR10.EXTEND;
1542 l_trans_rec.SQUAL_CHAR11.EXTEND;
1543 l_trans_rec.SQUAL_CHAR12.EXTEND;
1544 l_trans_rec.SQUAL_CHAR13.EXTEND;
1545 l_trans_rec.SQUAL_CHAR14.EXTEND;
1546 l_trans_rec.SQUAL_CHAR15.EXTEND;
1547 l_trans_rec.SQUAL_CHAR16.EXTEND;
1548 l_trans_rec.SQUAL_CHAR17.EXTEND;
1549 l_trans_rec.SQUAL_CHAR18.EXTEND;
1550 l_trans_rec.SQUAL_CHAR19.EXTEND;
1551 l_trans_rec.SQUAL_CHAR20.EXTEND;
1552 l_trans_rec.SQUAL_CHAR21.EXTEND;
1553 l_trans_rec.SQUAL_CHAR22.EXTEND;
1554 l_trans_rec.SQUAL_CHAR23.EXTEND;
1555 l_trans_rec.SQUAL_CHAR24.EXTEND;
1556 l_trans_rec.SQUAL_CHAR25.EXTEND;
1557 l_trans_rec.SQUAL_CHAR26.EXTEND;
1558 l_trans_rec.SQUAL_CHAR27.EXTEND;
1559 l_trans_rec.SQUAL_CHAR28.EXTEND;
1560 l_trans_rec.SQUAL_CHAR29.EXTEND;
1561 l_trans_rec.SQUAL_CHAR30.EXTEND;
1562 l_trans_rec.SQUAL_CHAR31.EXTEND;
1563 l_trans_rec.SQUAL_CHAR32.EXTEND;
1564 l_trans_rec.SQUAL_CHAR33.EXTEND;
1565 l_trans_rec.SQUAL_CHAR34.EXTEND;
1566 l_trans_rec.SQUAL_CHAR35.EXTEND;
1567 l_trans_rec.SQUAL_CHAR36.EXTEND;
1568 l_trans_rec.SQUAL_CHAR37.EXTEND;
1569 l_trans_rec.SQUAL_CHAR38.EXTEND;
1570 l_trans_rec.SQUAL_CHAR39.EXTEND;
1571 l_trans_rec.SQUAL_CHAR40.EXTEND;
1572 l_trans_rec.SQUAL_CHAR41.EXTEND;
1573 l_trans_rec.SQUAL_CHAR42.EXTEND;
1574 l_trans_rec.SQUAL_CHAR43.EXTEND;
1575 l_trans_rec.SQUAL_CHAR44.EXTEND;
1576 l_trans_rec.SQUAL_CHAR45.EXTEND;
1577 l_trans_rec.SQUAL_CHAR46.EXTEND;
1578 l_trans_rec.SQUAL_CHAR47.EXTEND;
1579 l_trans_rec.SQUAL_CHAR48.EXTEND;
1580 l_trans_rec.SQUAL_CHAR49.EXTEND;
1581 l_trans_rec.SQUAL_CHAR50.EXTEND;
1582
1583 -- transaction qualifier values
1584 l_trans_rec.SQUAL_NUM01(1) := null;
1585 l_trans_rec.SQUAL_NUM02(1) := null;
1586 l_trans_rec.SQUAL_NUM03(1) := null;
1587 l_trans_rec.SQUAL_NUM04(1) := null;
1588 l_trans_rec.SQUAL_NUM05(1) := null;
1589 l_trans_rec.SQUAL_NUM06(1) := null;
1590 l_trans_rec.SQUAL_NUM07(1) := null;
1591 l_trans_rec.SQUAL_NUM08(1) := null;
1592 l_trans_rec.SQUAL_NUM09(1) := null;
1593 l_trans_rec.SQUAL_NUM10(1) := null;
1594 l_trans_rec.SQUAL_NUM11(1) := null;
1595 l_trans_rec.SQUAL_NUM12(1) := null;
1596 l_trans_rec.SQUAL_NUM13(1) := null;
1597 l_trans_rec.SQUAL_NUM14(1) := null;
1598 l_trans_rec.SQUAL_NUM15(1) := null;
1599 l_trans_rec.SQUAL_NUM16(1) := null;
1600 l_trans_rec.SQUAL_NUM17(1) := null;
1601 l_trans_rec.SQUAL_NUM18(1) := null;
1602 l_trans_rec.SQUAL_NUM19(1) := null;
1603 l_trans_rec.SQUAL_NUM20(1) := null;
1604 l_trans_rec.SQUAL_NUM21(1) := null;
1605 l_trans_rec.SQUAL_NUM22(1) := null;
1606 l_trans_rec.SQUAL_NUM23(1) := null;
1607 l_trans_rec.SQUAL_NUM24(1) := null;
1608 l_trans_rec.SQUAL_NUM25(1) := null;
1609 l_trans_rec.SQUAL_NUM26(1) := null;
1610 l_trans_rec.SQUAL_NUM27(1) := null;
1611 l_trans_rec.SQUAL_NUM28(1) := null;
1612 l_trans_rec.SQUAL_NUM29(1) := null;
1613 l_trans_rec.SQUAL_NUM30(1) := null;
1614 l_trans_rec.SQUAL_NUM31(1) := null;
1615 l_trans_rec.SQUAL_NUM32(1) := null;
1616 l_trans_rec.SQUAL_NUM33(1) := null;
1617 l_trans_rec.SQUAL_NUM34(1) := null;
1618 l_trans_rec.SQUAL_NUM35(1) := null;
1619 l_trans_rec.SQUAL_NUM36(1) := null;
1620 l_trans_rec.SQUAL_NUM37(1) := null;
1621 l_trans_rec.SQUAL_NUM38(1) := null;
1622 l_trans_rec.SQUAL_NUM39(1) := null;
1623 l_trans_rec.SQUAL_NUM40(1) := null;
1624 l_trans_rec.SQUAL_NUM41(1) := null;
1625 l_trans_rec.SQUAL_NUM42(1) := null;
1626 l_trans_rec.SQUAL_NUM43(1) := null;
1627 l_trans_rec.SQUAL_NUM44(1) := null;
1628 l_trans_rec.SQUAL_NUM45(1) := null;
1629 l_trans_rec.SQUAL_NUM46(1) := null;
1630 l_trans_rec.SQUAL_NUM47(1) := null;
1631 l_trans_rec.SQUAL_NUM48(1) := null;
1632 l_trans_rec.SQUAL_NUM49(1) := null;
1633 l_trans_rec.SQUAL_NUM50(1) := null;
1634
1635 l_trans_rec.SQUAL_CHAR01(1) := null;
1636 l_trans_rec.SQUAL_CHAR02(1) := null;
1637 l_trans_rec.SQUAL_CHAR03(1) := null;
1638 l_trans_rec.SQUAL_CHAR04(1) := null;
1639 l_trans_rec.SQUAL_CHAR05(1) := null;
1640 l_trans_rec.SQUAL_CHAR06(1) := null;
1641 l_trans_rec.SQUAL_CHAR07(1) := null;
1642 l_trans_rec.SQUAL_CHAR08(1) := null;
1643 l_trans_rec.SQUAL_CHAR09(1) := null;
1644 l_trans_rec.SQUAL_CHAR10(1) := null;
1645 l_trans_rec.SQUAL_CHAR11(1) := null;
1646 l_trans_rec.SQUAL_CHAR12(1) := null;
1647 l_trans_rec.SQUAL_CHAR13(1) := null;
1648 l_trans_rec.SQUAL_CHAR14(1) := null;
1649 l_trans_rec.SQUAL_CHAR15(1) := null;
1650 l_trans_rec.SQUAL_CHAR16(1) := null;
1651 l_trans_rec.SQUAL_CHAR17(1) := null;
1652 l_trans_rec.SQUAL_CHAR18(1) := null;
1653 l_trans_rec.SQUAL_CHAR19(1) := null;
1654 l_trans_rec.SQUAL_CHAR20(1) := null;
1655 l_trans_rec.SQUAL_CHAR21(1) := null;
1656 l_trans_rec.SQUAL_CHAR22(1) := null;
1657 l_trans_rec.SQUAL_CHAR23(1) := null;
1658 l_trans_rec.SQUAL_CHAR24(1) := null;
1659 l_trans_rec.SQUAL_CHAR25(1) := null;
1660 l_trans_rec.SQUAL_CHAR26(1) := null;
1661 l_trans_rec.SQUAL_CHAR27(1) := null;
1662 l_trans_rec.SQUAL_CHAR28(1) := null;
1663 l_trans_rec.SQUAL_CHAR29(1) := null;
1664 l_trans_rec.SQUAL_CHAR30(1) := null;
1665 l_trans_rec.SQUAL_CHAR31(1) := null;
1666 l_trans_rec.SQUAL_CHAR32(1) := null;
1667 l_trans_rec.SQUAL_CHAR33(1) := null;
1668 l_trans_rec.SQUAL_CHAR34(1) := null;
1669 l_trans_rec.SQUAL_CHAR35(1) := null;
1670 l_trans_rec.SQUAL_CHAR36(1) := null;
1671 l_trans_rec.SQUAL_CHAR37(1) := null;
1672 l_trans_rec.SQUAL_CHAR38(1) := null;
1673 l_trans_rec.SQUAL_CHAR39(1) := null;
1674 l_trans_rec.SQUAL_CHAR40(1) := null;
1675 l_trans_rec.SQUAL_CHAR41(1) := null;
1676 l_trans_rec.SQUAL_CHAR42(1) := null;
1677 l_trans_rec.SQUAL_CHAR43(1) := null;
1678 l_trans_rec.SQUAL_CHAR44(1) := null;
1679 l_trans_rec.SQUAL_CHAR45(1) := null;
1680 l_trans_rec.SQUAL_CHAR46(1) := null;
1681 l_trans_rec.SQUAL_CHAR47(1) := null;
1682 l_trans_rec.SQUAL_CHAR48(1) := null;
1683 l_trans_rec.SQUAL_CHAR49(1) := null;
1684 l_trans_rec.SQUAL_CHAR50(1) := null;
1685
1686 OPEN cur_is_terr_setup;
1687 FETCH cur_is_terr_setup INTO l_count;
1688 CLOSE cur_is_terr_setup;
1689
1690 IF l_count > 0 THEN
1691
1692 IF p_party_id = Fnd_Api.g_miss_num
1693 OR p_party_id IS NULL THEN
1694 OPEN cur_get_party_id(p_sold_to_org);
1695 FETCH cur_get_party_id INTO l_party_id;
1696 CLOSE cur_get_party_id;
1697 ELSE
1698 l_party_id := p_party_id;
1699 END IF;
1700
1701 OPEN cur_get_party_info(l_party_id);
1702 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);
1703 CLOSE cur_get_party_info;
1704
1705 OPEN c_party_site_id(p_ship_to_org);
1706 FETCH c_party_site_id INTO l_trans_rec.SQUAL_NUM02(1);
1707 CLOSE c_party_site_id;
1708
1709 JTF_TERR_ASSIGN_PUB.get_winners
1710 ( p_api_version_number => l_api_version,
1711 p_init_msg_list => FND_API.G_TRUE,
1712 p_use_type => 'RESOURCE',
1713 p_source_id => -1001,
1714 p_trans_id => -1002,
1715 p_trans_rec => l_trans_rec,
1716 p_resource_type => FND_API.G_MISS_CHAR,
1717 p_role => FND_API.G_MISS_CHAR,
1718 p_top_level_terr_id => FND_API.G_MISS_NUM,
1719 p_num_winners => FND_API.G_MISS_NUM,
1720 x_return_status => l_return_status,
1721 x_msg_count => l_msg_count,
1722 x_msg_data => l_msg_data,
1723 x_winners_rec => l_winner_rec
1724 );
1725
1726 END IF;
1727
1728 IF l_return_status = Fnd_Api.g_ret_sts_success THEN
1729 FOR i IN 1..l_winner_rec.terr_id.COUNT LOOP
1730 l_multirecord(i) := l_winner_rec.terr_id(i);
1731 END LOOP;
1732 END IF;
1733 RETURN l_multirecord;
1734
1735 END Find_SA_Territories;
1736
1737
1738 -- Sourcing rules for SOLD_BY contxt
1739 FUNCTION get_sales_method
1740 (
1741 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1742 )
1743 RETURN VARCHAR2
1744 IS
1745 l_sales_method VARCHAR2(1);
1746 l_distributor_acct_id NUMBER;
1747 BEGIN
1748 IF p_resale_line_tbl.COUNT > 0 THEN
1749 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1750 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1751 l_sales_method := 'I';
1752 ELSE
1753 l_sales_method := 'D';
1754 END IF;
1755 ELSE
1756 l_sales_method := 'D';
1757 END IF;
1758
1759 RETURN l_sales_method;
1760 END get_sales_method;
1761
1762
1763 FUNCTION get_distributor_acct_id
1764 (
1765 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1766 )
1767 RETURN NUMBER
1768 IS
1769 l_distributor_acct_id NUMBER;
1770 BEGIN
1771 IF p_resale_line_tbl.COUNT > 0 THEN
1772 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1773 ELSE
1774 l_distributor_acct_id := NULL;
1775 END IF;
1776
1777 RETURN l_distributor_acct_id;
1778 END get_distributor_acct_id;
1779
1780
1781 FUNCTION get_distributor_lists
1782 (
1783 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1784 )
1785 RETURN Qp_Attr_Mapping_Pub.t_multirecord
1786 IS
1787 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1788 l_distributor_acct_id NUMBER;
1789 BEGIN
1790 IF p_resale_line_tbl.COUNT > 0 THEN
1791 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1792 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1793 l_multirecord := get_lists(fnd_api.g_miss_num, l_distributor_acct_id);
1794 END IF;
1795 END IF;
1796
1797 RETURN l_multirecord;
1798 END get_distributor_lists;
1799
1800
1801 FUNCTION get_distributor_segments
1802 (
1803 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1804 )
1805 RETURN Qp_Attr_Mapping_Pub.t_multirecord
1806 IS
1807 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1808 l_distributor_acct_id NUMBER;
1809 BEGIN
1810 IF p_resale_line_tbl.COUNT > 0 THEN
1811 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1812 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1813 l_multirecord := get_segments(fnd_api.g_miss_num, l_distributor_acct_id);
1814 END IF;
1815 END IF;
1816
1817 RETURN l_multirecord;
1818 END get_distributor_segments;
1819
1820
1821 FUNCTION get_distributor_territories
1822 (
1823 p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1824 )
1825 RETURN Qp_Attr_Mapping_Pub.t_multirecord
1826 IS
1827 l_multirecord Qp_Attr_Mapping_Pub.t_multirecord;
1828 l_distributor_acct_id NUMBER;
1829 l_ship_to_org_id NUMBER;
1830 BEGIN
1831 IF p_resale_line_tbl.COUNT > 0 THEN
1832 l_distributor_acct_id := p_resale_line_tbl(1).sold_from_cust_account_id;
1833 l_ship_to_org_id := p_resale_line_tbl(1).sold_from_site_id;
1834 IF l_distributor_acct_id IS NOT NULL AND l_distributor_acct_id <> fnd_api.g_miss_num THEN
1835 l_multirecord := find_tm_territories(fnd_api.g_miss_num, l_distributor_acct_id, l_ship_to_org_id, fnd_api.g_miss_num);
1836 END IF;
1837 END IF;
1838
1839 RETURN l_multirecord;
1840 END get_distributor_territories;
1841 /*
1842 Seeding instruction:
1843 get_sales_method:
1844 User Source Type: PL/SQL API
1845 User Value String: OZF_QP_QUAL_PVT.get_sales_method(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1846
1847 get_distributor_acct_id:
1848 User Source Type: PL/SQL API
1849 User Value String: OZF_QP_QUAL_PVT.get_distributor_acct_id(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1850
1851 get_distributor_lists:
1852 User Source Type: PL/SQL API Multi-Record
1853 User Value String: OZF_QP_QUAL_PVT.get_distributor_lists(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1854
1855 get_distributor_segments:
1856 User Source Type: PL/SQL API Multi-Record
1857 User Value String: OZF_QP_QUAL_PVT.get_distributor_segments(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1858
1859 get_distributor_territories:
1860 User Source Type: PL/SQL API Multi-Record
1861 User Value String: OZF_QP_QUAL_PVT.get_distributor_territories(OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL)
1862 */
1863
1864
1865 --nirprasa, 10216374/9447673 SSD-IDSM ER - IDSM AND ACCRUAL CHANGES
1866
1867 FUNCTION get_item_cost
1868 (
1869 p_line_id NUMBER, p_resale_line_tbl ozf_order_price_pvt.resale_line_tbl_type -- OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL
1870 )
1871 RETURN VARCHAR2
1872 IS
1873 l_sdr_flag VARCHAR2(1) := 'Y';
1874 l_cost_price NUMBER;
1875 l_primary_uom_code VARCHAR2(3);
1876 l_order_uom_code VARCHAR2(3);
1877 l_Uom_rate NUMBER;
1878 l_cost_price_currency VARCHAR2(15);
1879 l_order_currency VARCHAR2(15);
1880 l_conversion_type_code VARCHAR2(30);
1881 l_conversion_rate NUMBER;
1882 l_denominator NUMBER;
1883 l_numerator NUMBER;
1884 l_rate NUMBER;
1885 l_inventory_item_id NUMBER;
1886 l_organization_id NUMBER;
1887 l_supplier_item_cost NUMBER;
1888 l_call_costing_api BOOLEAN := false;
1889 l_table_name varchar2(30);
1890 l_col_name varchar2(30);
1891 l_party_id NUMBER;
1892 l_idsm_ord_src_party_id VARCHAR2(30);
1893 l_column_name VARCHAR2(30);
1894 l_exp_qry_str VARCHAR2(500);
1895 l_qry_str VARCHAR2(500);
1896 l_uom_code VARCHAR2(30);
1897 l_inv_org_id NUMBER;
1898 x_msg_count NUMBER;
1899 x_msg_data VARCHAR2(2000);
1900 l_api_name VARCHAR2(30) := 'get_item_cost';
1901 l_resale_table_type VARCHAR2(10);
1902 l_direct_order_flag VARCHAR2(1);
1903
1904 CURSOR c_order_info(p_order_line_id IN NUMBER) IS
1905 SELECT line.inventory_item_id, line.org_id,
1906 line.order_quantity_uom, head.transactional_curr_code,
1907 head.conversion_type_code, line.ship_from_org_id
1908 FROM oe_order_lines_all line, oe_order_headers_all head
1909 WHERE line.line_id = p_order_line_id
1910 AND line.header_id = head.header_id;
1911
1912 CURSOR c_resale_line_info(p_resale_line_id IN NUMBER) IS
1913 SELECT inventory_item_id,org_id, supplier_item_cost,
1914 nvl(supplier_item_uom,uom_code), currency_code
1915 FROM ozf_resale_lines_all
1916 WHERE resale_line_id = p_resale_line_id;
1917
1918 CURSOR c_iface_line_info(p_resale_line_int_id IN NUMBER) IS
1919 SELECT inventory_item_id,org_id, supplier_item_cost,
1920 nvl(supplier_item_uom,uom_code), currency_code
1921 FROM ozf_resale_lines_int_all
1922 WHERE resale_line_int_id = p_resale_line_int_id;
1923
1924 CURSOR c_iface_batch_info(p_resale_line_int_id IN NUMBER) IS
1925 SELECT orb.direct_order_flag
1926 FROM ozf_resale_lines_int_all orli, ozf_resale_batches_all orb
1927 WHERE orli.resale_line_int_id = p_resale_line_int_id
1928 AND orb.resale_batch_id = orli.resale_batch_id;
1929
1930 CURSOR c_resale_batch_info(p_resale_line_id IN NUMBER) IS
1931 SELECT orb.direct_order_flag
1932 FROM ozf_resale_batches_all orb,
1933 ozf_resale_batch_line_maps_all orblm
1934 WHERE orblm.resale_line_id = p_resale_line_id
1935 AND orblm.resale_batch_id = orb.resale_batch_id;
1936
1937 CURSOR c_exchange_rate_type_csr(p_org_id NUMBER)IS
1938 SELECT exchange_rate_type
1939 FROM ozf_sys_parameters_all
1940 WHERE org_id=p_org_id;
1941
1942 CURSOR c_primary_uom_csr(p_inventory_item_id NUMBER, p_inv_org_id NUMBER)IS
1943 SELECT primary_uom_code
1944 FROM mtl_system_items
1945 WHERE inventory_item_id = p_inventory_item_id
1946 AND organization_id = p_inv_org_id;
1947
1948
1949 CURSOR c_cost_price_curr_csr(p_organization_id NUMBER)IS
1950 SELECT ogs.currency_code
1951 FROM org_organization_definitions ood, oe_gl_sets_of_books_v ogs
1952 WHERE ood.organization_id = p_organization_id
1953 AND ood.set_of_books_id = ogs.set_of_books_id;
1954
1955
1956 BEGIN
1957
1958 /*
1959 1) For EBS OM orders
1960 1.1) get cost price
1961 1.2) Store cost price in oe_order_lines_all table. The column will be retreived from profile 'OZF: Store Item Cost'
1962 1.3) Shouldn't we have a default value for this profile?
1963 1.4) If any excpetion happens store -1 in this column
1964
1965 2) If resale_table_type is IFACE or RESALE then compare profile 'OZF: Use Indirect Sales as Order Source for Customer'
1966 with disti of the batch.
1967
1968 2.1) If both are same then check get the value of column 'Supplier Item Cost'.
1969 If not provided during batch upload then get using costing API and store in this column.
1970
1971 2.2) If both are diff then get using costing API and store in column Supplier Item Cost.
1972
1973 3) If its is iface store in ozf_resale_lines_int_all, if its resale store in ozf_resale_lines_all.
1974
1975 4) If exception happens or costing API doesn't return anything then store -1 in respective columns.
1976 */
1977
1978 IF p_resale_line_tbl.COUNT > 0 THEN
1979 l_resale_table_type := p_resale_line_tbl(1).resale_table_type;
1980 END IF;
1981
1982 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
1983 Fnd_Msg_Pub.Add_Exc_Msg( 'l_resale_table_type', l_resale_table_type);
1984 Fnd_Msg_Pub.Add_Exc_Msg( 'p_line_id', p_line_id);
1985 END IF;
1986
1987 CASE
1988 WHEN l_resale_table_type = 'RESALE' THEN
1989 l_qry_str := 'UPDATE ozf_resale_lines_all SET supplier_item_cost = :1 WHERE resale_line_id = :2 AND supplier_item_cost IS NULL' ;
1990
1991 --derive resale order information like organization_id, item_id, uom etc.
1992
1993 OPEN c_resale_line_info(p_line_id);
1994 FETCH c_resale_line_info INTO l_inventory_item_id, l_organization_id, l_supplier_item_cost,
1995 l_uom_code, l_order_currency;
1996 CLOSE c_resale_line_info;
1997
1998 --Based on the resale batch's OU derive exchange_rate_type from System Parameter's page.
1999
2000 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2001 Fnd_Msg_Pub.Add_Exc_Msg( 'l_organization_id', l_organization_id);
2002 Fnd_Msg_Pub.Add_Exc_Msg( 'l_supplier_item_cost', l_supplier_item_cost);
2003 END IF;
2004
2005 OPEN c_exchange_rate_type_csr(l_organization_id);
2006 FETCH c_exchange_rate_type_csr INTO l_conversion_type_code;
2007 CLOSE c_exchange_rate_type_csr;
2008
2009 /*Derive the value of direct_order_flag from ozf_resale_batches_all table
2010 IF yes then it's a direct order hence the purchase price should be provided
2011 during web-adi upload else set flag l_call_costing_api to true, so that
2012 purchase price can be derived later using costing API.*/
2013
2014 OPEN c_resale_batch_info(p_line_id);
2015 FETCH c_resale_batch_info INTO l_direct_order_flag;
2016 CLOSE c_resale_batch_info;
2017
2018 IF l_direct_order_flag = 'Y' THEN
2019 l_cost_price := l_supplier_item_cost;
2020 ELSE
2021 l_call_costing_api := true;
2022 END IF;
2023
2024 l_inv_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
2025
2026 WHEN l_resale_table_type = 'IFACE' THEN
2027
2028 /*If the pricing call is made for a IFACE order through conc. prog
2029 Third Party Accrual from Interface Table, then this code will be executed. */
2030
2031 l_qry_str := 'UPDATE ozf_resale_lines_int_all SET supplier_item_cost = :1 WHERE resale_line_int_id = :2 AND supplier_item_cost IS NULL';
2032
2033 --derive interface order information like organization_id, item_id, uom etc.
2034
2035 OPEN c_iface_line_info(p_line_id);
2036 FETCH c_iface_line_info INTO l_inventory_item_id, l_organization_id, l_supplier_item_cost,
2037 l_uom_code, l_order_currency;
2038 CLOSE c_iface_line_info;
2039
2040 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2041 Fnd_Msg_Pub.Add_Exc_Msg( 'l_organization_id', l_organization_id);
2042 END IF;
2043
2044 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2045 Fnd_Msg_Pub.Add_Exc_Msg( 'l_organization_id', l_organization_id);
2046 Fnd_Msg_Pub.Add_Exc_Msg( 'l_supplier_item_cost', l_supplier_item_cost);
2047 END IF;
2048
2049 OPEN c_exchange_rate_type_csr(l_organization_id);
2050 FETCH c_exchange_rate_type_csr INTO l_conversion_type_code;
2051 CLOSE c_exchange_rate_type_csr;
2052
2053 OPEN c_iface_batch_info(p_line_id);
2054 FETCH c_iface_batch_info INTO l_direct_order_flag;
2055 CLOSE c_iface_batch_info;
2056
2057 IF l_direct_order_flag = 'Y' THEN
2058 l_cost_price := l_supplier_item_cost;
2059 ELSE
2060 l_call_costing_api := true;
2061 END IF;
2062
2063 l_inv_org_id := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
2064
2065 ELSE
2066 /*If the pricing call is made for direct OM order then this code will be executed. */
2067 OPEN c_order_info(p_line_id);
2068 FETCH c_order_info INTO l_inventory_item_id, l_organization_id,
2069 l_uom_code, l_order_currency,
2070 l_conversion_type_code, l_inv_org_id;
2071 CLOSE c_order_info;
2072
2073 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2074 Fnd_Msg_Pub.Add_Exc_Msg( 'l_organization_id', l_organization_id);
2075 END IF;
2076
2077 MO_GLOBAL.set_policy_context('S', l_organization_id);
2078
2079 l_column_name := FND_PROFILE.value('OZF_ITEM_COST_COLUMN');
2080
2081 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2082 Fnd_Msg_Pub.Add_Exc_Msg( 'l_column_name', l_column_name);
2083 END IF;
2084
2085 /*FOR OM orders we have a new seeded profile at site and org level
2086 OZF: Store Item Cost. This profile will store the name of DFF column from
2087 oe_order_lines_all where the calculated purchase price will be stored.
2088 If profile is not set then raise FND warning*/
2089
2090 IF l_column_name IS NOT NULL THEN
2091 l_qry_str := 'UPDATE oe_order_lines_all SET '|| l_column_name ||' = :1 WHERE line_id = :2 AND ' || l_column_name ||' IS NULL';
2092 l_call_costing_api := true;
2093 ELSE
2094 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2095 FND_MESSAGE.set_name('OZF', 'OZF_PROFILE_MISS_WRG');
2096 FND_MESSAGE.Set_Token('TEXT','Profile OZF: Store Item Cost is NULL.');
2097 FND_MSG_PUB.add;
2098 END IF;
2099 END IF;
2100
2101 END CASE;
2102
2103 /*If the processing is happening for resale/iface batch which is either indirect
2104 OR it is a direct batch but supplier_item_cost was not provided during POS data upload
2105 OR it is an EBS OM order then call costing API.*/
2106
2107 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2108 Fnd_Msg_Pub.Add_Exc_Msg( 'l_inv_org_id', l_inv_org_id);
2109 END IF;
2110
2111 IF ((l_call_costing_api OR l_cost_price IS NULL OR l_cost_price = FND_API.G_MISS_NUM)
2112 AND (l_inv_org_id IS NOT NULL AND l_inv_org_id <> FND_API.G_MISS_NUM )) THEN
2113 l_cost_price := cst_cost_api.get_item_cost
2114 (p_api_version => 1
2115 ,p_inventory_item_id => l_inventory_item_id
2116 ,p_organization_id => l_inv_org_id
2117 );
2118
2119 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2120 Fnd_Msg_Pub.Add_Exc_Msg( 'l_cost_price', l_cost_price);
2121 END IF;
2122
2123 OPEN c_primary_uom_csr(l_inventory_item_id,l_inv_org_id);
2124 FETCH c_primary_uom_csr INTO l_primary_uom_code;
2125 CLOSE c_primary_uom_csr;
2126
2127
2128 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2129 Fnd_Msg_Pub.Add_Exc_Msg( 'l_order_uom_code', l_order_uom_code);
2130 Fnd_Msg_Pub.Add_Exc_Msg( 'l_primary_uom_code', l_primary_uom_code);
2131 END IF;
2132
2133 l_order_uom_code := l_uom_code;
2134
2135 IF ((l_primary_uom_code IS NOT NULL AND l_primary_uom_code <> FND_API.G_MISS_CHAR)
2136 AND (l_primary_uom_code <> l_order_uom_code AND l_cost_price is not null
2137 AND l_cost_price <> fnd_api.g_miss_num )) THEN
2138 INV_CONVERT.INV_UM_CONVERSION(From_Unit => l_order_uom_code
2139 ,To_Unit => l_primary_uom_code
2140 ,Item_ID => l_inventory_item_id
2141 ,Uom_Rate => l_uom_rate);
2142
2143 --Cost Price in Order Line UOM & Ledger Currency
2144 l_cost_price := l_cost_price * l_Uom_rate;
2145 END IF;
2146
2147 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2148 Fnd_Msg_Pub.Add_Exc_Msg( 'l_cost_price', l_cost_price);
2149 END IF;
2150
2151 OPEN c_cost_price_curr_csr(l_organization_id);
2152 FETCH c_cost_price_curr_csr INTO l_cost_price_currency;
2153 CLOSE c_cost_price_curr_csr;
2154
2155
2156 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_DEBUG_LOW ) THEN
2157 Fnd_Msg_Pub.Add_Exc_Msg( 'l_cost_price_currency', l_cost_price_currency);
2158 Fnd_Msg_Pub.Add_Exc_Msg( 'l_order_currency', l_order_currency);
2159 END IF;
2160
2161 IF l_cost_price_currency IS NOT NULL AND l_cost_price_currency <> FND_API.G_MISS_CHAR
2162 AND l_cost_price_currency <> l_order_currency THEN
2163
2164 /* If cost price currency is not equal to order currency (which is batch currency for resale orders.)
2165 then convert cost_price to order currency. As already explained the exchange_rate_type is
2166 derived from TM System Paramters for order's OU.
2167 If conversion rate type is not found then we will raise a warning in debugs*/
2168
2169
2170 IF l_conversion_type_code IS NULL OR l_conversion_type_code = FND_API.G_MISS_CHAR THEN
2171 --Conversion type not entered in sales order header, unable to perform cost conversion
2172 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2173 FND_MESSAGE.set_name('OZF', 'OZF_INS_RESALE_LOG_WRG');
2174 FND_MESSAGE.Set_Token('TEXT','Conversion rate type is not defined for OU.'|| l_organization_id);
2175 FND_MSG_PUB.add;
2176 END IF;
2177 RETURN NULL;
2178 END IF;
2179
2180 /* USER Conversion Type is not supported.
2181 IF l_conversion_type_code = 'User' AND l_conversion_rate IS NULL THEN
2182 RETURN NULL;
2183 END IF; */
2184
2185 gl_currency_api.convert_amount
2186 ( x_from_currency => l_cost_price_currency ,
2187 x_to_currency => l_order_currency ,
2188 x_conversion_date => SYSDATE ,
2189 x_conversion_type => l_conversion_type_code ,
2190 x_amount => l_cost_price ,
2191 x_converted_amount => l_cost_price ,
2192 x_denominator => l_denominator ,
2193 x_numerator => l_numerator ,
2194 x_rate => l_rate );
2195
2196 END IF; --IF l_cost_price_currency <> l_order_currency THEN
2197 END IF;
2198
2199 IF l_qry_str IS NOT NULL AND l_cost_price IS NOT NULL AND l_cost_price <> fnd_api.g_miss_num THEN
2200 EXECUTE IMMEDIATE l_qry_str USING l_cost_price, p_line_id;
2201 END IF;
2202
2203 RETURN l_sdr_flag;
2204
2205 EXCEPTION
2206 WHEN Fnd_Api.G_EXC_ERROR THEN
2207 Fnd_Msg_Pub.Count_AND_Get
2208 ( p_count => x_msg_count,
2209 p_data => x_msg_data,
2210 p_encoded => Fnd_Api.G_FALSE
2211 );
2212 EXECUTE IMMEDIATE l_qry_str USING -1, p_line_id;
2213 RETURN l_sdr_flag;
2214 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2215 Fnd_Msg_Pub.Count_AND_Get
2216 ( p_count => x_msg_count,
2217 p_data => x_msg_data,
2218 p_encoded => Fnd_Api.G_FALSE
2219 );
2220 EXECUTE IMMEDIATE l_qry_str USING -1, p_line_id;
2221 RETURN l_sdr_flag;
2222 WHEN OTHERS THEN
2223 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
2224 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2225 END IF;
2226 Fnd_Msg_Pub.Count_AND_Get
2227 ( p_count => x_msg_count,
2228 p_data => x_msg_data,
2229 p_encoded => Fnd_Api.G_FALSE
2230 );
2231 EXECUTE IMMEDIATE l_qry_str USING -1, p_line_id;
2232 RETURN l_sdr_flag;
2233
2234 RETURN l_sdr_flag;
2235 END get_item_cost;
2236
2237 FUNCTION get_additional_qualifiers
2238 (
2239 p_order_header_id NUMBER,
2240 p_order_line_id NUMBER,
2241 p_list_header_id NUMBER,
2242 p_list_line_id NUMBER
2243 )
2244 RETURN VARCHAR2
2245 IS
2246
2247 CURSOR c_qualifier_info(p_qualifier_context IN VARCHAR2,
2248 p_qualifier_attribute IN VARCHAR2,
2249 p_list_header_id IN NUMBER) IS
2250 SELECT qualifier_attr_value
2251 FROM qp_qualifiers
2252 WHERE list_header_id=p_list_header_id
2253 AND qualifier_context = p_qualifier_context
2254 AND qualifier_attribute = p_qualifier_attribute;
2255
2256 CURSOR c_account_type(p_order_line_id IN NUMBER) IS
2257 SELECT attribute3
2258 FROM oe_order_lines_all
2259 WHERE line_id = p_order_line_id;
2260
2261 CURSOR c_item_vendor(p_order_line_id IN NUMBER) IS
2262 SELECT line.attribute4
2263 FROM oe_order_lines_all line, mtl_system_items item
2264 WHERE line.line_id = p_order_line_id
2265 AND line.inventory_item_id = item.inventory_item_id;
2266
2267 l_qualified_flag VARCHAR2(1) := 'Y';
2268 l_acccount_type VARCHAR2(30);
2269 l_vendor VARCHAR2(30);
2270 l_qualifier_info c_qualifier_info%ROWTYPE;
2271
2272 BEGIN
2273
2274 --account type custom qualifier:
2275
2276 /*OPEN c_qualifier_info('DH Custom','QUALIFIER_ATTRIBUTE45',p_list_header_id);
2277 FETCH c_qualifier_info INTO l_qualifier_info;
2278 CLOSE c_qualifier_info;
2279
2280 OPEN c_account_type(p_order_line_id);
2281 FETCH c_account_type INTO l_acccount_type;
2282 CLOSE c_account_type;
2283
2284 IF l_qualifier_info.qualifier_attribute_value = l_acccount_type THEN
2285 l_qualified_flag := 'Y';
2286 ELSE
2287 l_qualified_flag := 'N';
2288 END IF;
2289
2290
2291 -- Vendor custom qualifier:
2292
2293 OPEN c_qualifier_info('DH Custom','QUALIFIER_ATTRIBUTE46',p_list_header_id);
2294 FETCH c_qualifier_info INTO l_qualifier_info;
2295 CLOSE c_qualifier_info;
2296
2297 OPEN c_item_vendor(p_order_line_id);
2298 FETCH c_item_vendor INTO l_vendor;
2299 CLOSE c_item_vendor;
2300
2301 IF l_qualifier_info.qualifier_attribute_value = l_acccount_type THEN
2302 l_qualified_flag := 'Y';
2303 ELSE
2304 l_qualified_flag := 'N';
2305 END IF;*/
2306
2307 RETURN l_qualified_flag;
2308
2309 /*EXCEPTION
2310 WHEN Fnd_Api.G_EXC_ERROR THEN
2311 Fnd_Msg_Pub.Count_AND_Get
2312 ( p_count => x_msg_count,
2313 p_data => x_msg_data,
2314 p_encoded => Fnd_Api.G_FALSE
2315 );
2316 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2317 Fnd_Msg_Pub.Count_AND_Get
2318 ( p_count => x_msg_count,
2319 p_data => x_msg_data,
2320 p_encoded => Fnd_Api.G_FALSE
2321 );
2322
2323 WHEN OTHERS THEN
2324 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
2325 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2326 END IF;
2327 Fnd_Msg_Pub.Count_AND_Get
2328 ( p_count => x_msg_count,
2329 p_data => x_msg_data,
2330 p_encoded => Fnd_Api.G_FALSE
2331 );
2332
2333 RETURN l_qualified_flag;*/
2334 END get_additional_qualifiers;
2335
2336 END OZF_QP_QUAL_PVT ;