[Home] [Help]
PACKAGE BODY: APPS.ONT_OIP_COMMON
Source
1 package body ONT_OIP_Common as
2 /* $Header: ontcomnb.pls 120.9 2011/08/04 08:59:55 kshashan ship $ */
3
4 procedure getContactId(lContactid in out NOCOPY varchar2) is
5 xContactId number;
6 --
7 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8 --
9 BEGIN
10 if (xContactId is not null) then
11 lContactId:=XContactId;
12 else
13 lContactId:=-99;
14 end if;
15 end getContactId;
16
17 /* Sun OIP changes - Method to get the active holds list */
18 FUNCTION getActiveHolds(
19 pEntityType IN VARCHAR2,
20 pEntityId IN NUMBER) RETURN VARCHAR2
21 IS
22
23 CURSOR HeaderHoldNames
24 IS
25 select distinct def.name hold_name,
26 def.hold_id
27 from oe_order_headers_all h,
28 oe_order_holds_all hold,
29 oe_hold_sources_all src,
30 oe_hold_definitions def
31 where h.header_id=hold.header_id
32 and hold.hold_source_id=src.hold_source_id
33 and hold.line_id IS NULL
34 and src.hold_id=def.hold_id
35 and hold.released_flag='N'
36 and h.header_id = pEntityid;
37
38 CURSOR LineHoldNames
39 IS
40 select distinct def.name hold_name,
41 def.hold_id
42 from oe_order_lines_all l,
43 oe_order_holds_all hold,
44 oe_hold_sources_all src,
45 oe_hold_definitions def
46 where hold.header_id=l.header_id
47 and hold.line_id=l.line_id
48 and hold.hold_source_id=src.hold_source_id
49 and src.hold_id=def.hold_id
50 and hold.released_flag='N'
51 and l.line_id=pEntityId;
52
53 lActiveHolds VARCHAR2(2000);
54
55 --
56 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
57 --
58
59 BEGIN
60
61 IF pEntityType = 'OEOH' THEN
62 FOR hold_rec IN HeaderHoldNames
63 LOOP
64 lActiveHolds := hold_rec.hold_name || ',' || lActiveHolds;
65 END LOOP;
66 ELSIF pEntityType = 'OEOL' THEN
67 FOR hold_rec IN LineHoldNames
68 LOOP
69 lActiveHolds := hold_rec.hold_name || ',' || lActiveHolds;
70 END LOOP;
71 END IF;
72
73 lActiveHolds := SubStr(lActiveHolds, 1, InStr(lActiveHolds,',',-1)-1);
74
75 RETURN lActiveHolds;
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 RETURN '';
80 END getActiveHolds;
81
82 /* Sun OIP changes - Method to get the IB system list */
83 FUNCTION getIbSystems (pLineId IN NUMBER)
84 RETURN VARCHAR2
85 IS
86
87 CURSOR SystemNames
88 IS
89 select DISTINCT csv.name Name -- bug 10632055
90 from csi_t_txn_line_details clid
91 , csi_t_transaction_lines csitl
92 , csi_systems_vl csv
93 where csitl.SOURCE_TRANSACTION_ID = pLineId
94 and csitl.TRANSACTION_LINE_ID = clid.TRANSACTION_LINE_ID
95 and clid.csi_system_id = csv.system_id;
96
97 lSystemNames VARCHAR2(2000);
98
99 --
100 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
101 --
102
103 BEGIN
104
105 FOR system_rec IN SystemNames
106 LOOP
107 lSystemNames := system_rec.Name || ',' || lSystemNames;
108 END LOOP;
109
110 lSystemNames := SubStr(lSystemNames, 1, InStr(lSystemNames,',',-1)-1);
111
112 RETURN lSystemNames;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 RETURN '';
117 END getIbSystems;
118
119 /* Sun OIP changes - Method to get formatted serial numbers */
120 FUNCTION getSerialNumbers(pDeliveryDetailId IN NUMBER)
121 RETURN VARCHAR2
122 IS
123 l_serial_number_list VARCHAR2(2000):=NULL;
124
125 CURSOR SerialNumbers IS
126 SELECT DISTINCT
127 fm_serial_number,
128 to_serial_number
129 FROM wsh_serial_numbers
130 WHERE delivery_detail_id = pDeliveryDetailId;
131
132 BEGIN
133
134 For rec IN SerialNumbers
135 LOOP
136
137 IF (rec.fm_serial_number = rec.to_serial_number) THEN
138 l_serial_number_list := rec.fm_serial_number || ',' || l_serial_number_list;
139 ELSE
140 l_serial_number_list := rec.fm_serial_number||'..'||rec.to_serial_number
141 || ',' || l_serial_number_list;
142 END IF;
143
144 END LOOP;
145
146 l_serial_number_list := SubStr(l_serial_number_list, 1, InStr(l_serial_number_list,',',-1)-1);
147
148 RETURN l_serial_number_list;
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 RETURN '';
153 END getSerialNumbers;
154
155 /* Overloaded version. Use this API now. */
156 /* Sun OIP changes begin */
157
158
159 procedure getContactDetails(
160 pUserId in number,
161 pContactId out nocopy number,
162 pUserFName out nocopy varchar2,
163 pUserLName out nocopy varchar2,
164 pUserEmail out nocopy varchar2,
165 pCustName out nocopy varchar2,
166 pCustomerID out nocopy number,
167 pCustomerAddrID out nocopy number,
168 pStatusCode out nocopy NUMBER,
169 pCustomerIdList OUT NOCOPY varchar2
170 ) is
171
172 CURSOR C_PARTY IS
173 SELECT Role.cust_account_role_id ContactID,
174 Party.person_first_name UserFName,
175 Party.person_last_name UserLName,
176 Party.email_address UserEmail,
177 Party.party_name CustName,
178 Role.cust_account_id CustomerId,
179 nvl(Role.cust_acct_site_id, 0) CustomerAddrID
180 FROM fnd_user fnd,
181 hz_parties Party,
182 hz_cust_account_roles ROLE,
183 hz_relationships Rel
184 WHERE Party.party_id = fnd.customer_id
185 AND Party.party_type = 'PERSON'
186 AND Role.current_role_state = 'A'
187 AND Role.role_type = 'CONTACT'
188 -- AND Role.cust_acct_site_id IS NULL -- bug 12616799
189 AND (Role.end_date IS NULL OR Role.end_date > SYSDATE)
190 AND Rel.subject_id = fnd.customer_id
191 AND Rel.party_id = Role.party_id
192 AND Rel.subject_type = 'PERSON'
193 AND (Rel.end_date IS NULL OR Rel.end_date > SYSDATE)
194 AND Rel.status = 'A'
195 AND Rel.subject_table_name = 'HZ_PARTIES'
196 AND fnd.user_id = pUserId
197 UNION
198 SELECT roles.cust_account_role_id,
199 party.person_first_name,
200 party.person_last_name,
201 party.email_address,
202 party.party_name,
203 roles.cust_account_id,
204 nvl(roles.cust_acct_site_id,0)
205 FROM fnd_user fnd,
206 hz_parties party,
207 hz_cust_account_roles roles
208 WHERE fnd.customer_id = party.party_id
209 AND party.party_type='PARTY_RELATIONSHIP'
210 AND party.party_id = roles.party_id
211 AND roles.status ='A'
212 AND party.status='A'
213 AND fnd.user_id=pUserID
214 UNION
215 SELECT 0 ContactID,
216 party.person_first_name UserFName,
217 party.person_last_name UserLName,
218 party.email_address UserEmail,
219 party.party_name CustName,
220 cust.cust_account_id CustomerId,
221 0 CustomerAddrID
222 FROM fnd_user fnd,
223 hz_parties party,
224 hz_cust_accounts cust
225 WHERE fnd.customer_id = party.party_id
226 AND party.party_type = 'PERSON'
227 AND party.status = 'A'
228 AND cust.party_id = party.party_id
229 AND cust.status = 'A'
230 AND fnd.user_id = pUserId;
231
232
233 lContactId NUMBER;
234 lUserFName VARCHAR2(500);
235 lUserLName VARCHAR2(500);
236 lUserEmail VARCHAR2(512);
237 lCustName VARCHAR2(500);
238 lCustomerID NUMBER;
239 lCustomerAddrID NUMBER;
240 lCustomerIdList VARCHAR2(1000) := '';
241 lRecCount NUMBER := 0;
242
243 --
244 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
245 --
246 BEGIN
247 IF (C_Party%ISOPEN) THEN
248 CLOSE C_Party;
249 END IF;
250
251 FOR rec IN C_Party
252 LOOP
253 lRecCount := lRecCount + 1;
254 lContactId := rec.ContactID;
255 lUserFName := rec.UserFName;
256 lUserLName := rec.UserLName;
257 lUserEmail := rec.UserEmail;
258 lCustName := rec.CustName;
259 lCustomerID := rec.CustomerID;
260 lCustomerAddrID := rec.CustomerAddrID;
261 lCustomerIdList := rec.CustomerId || ',' || lCustomerIdList;
262 END LOOP;
263
264 -- If user has access to multiple customers, return the customer ID
265 -- field as -99 to flag that CustomerIdList should be used.
266
267 IF lRecCount > 1 THEN
268 lCustomerId := -99;
269 END IF;
270
271 -- remove an extra comma at the end
272 lCustomerIdList := SubStr(lCustomerIdList, 1, InStr(lCustomerIdList,',',-1)-1);
273
274 pContactId := lContactId;
275 pUserFName := lUserFName;
276 pUserLName := lUserLName;
277 pUserEmail := lUserEmail;
278 pCustName := lCustName;
279 pCustomerID := lCustomerID;
280 pCustomerAddrID := lCustomerAddrID;
281 pCustomerIdList := lCustomerIdList;
282
283 EXCEPTION
284 when no_data_found then
285 pStatusCode:=-99;
286 END getContactDetails;
287
288 /*OIP changes end */
289
290
291 procedure getContactDetails(lUserId in number,
292 pContactId out nocopy number,
293
294 pUserFName out nocopy varchar2,
295
296 pUserLName out nocopy varchar2,
297
298 pUserEmail out nocopy varchar2,
299
300 pCustName out nocopy varchar2,
301
302 pCustomerID out nocopy number,
303
304 pCustomerAddrID out nocopy number,
305
306 pStatusCode out nocopy number) is
307
308 CURSOR C_PARTY IS
309 select roles.cust_account_role_id,
310 party.person_first_name,
311 party.person_last_name,
312 party.email_address,
313 party.party_name,
314 roles.cust_account_id,
315 nvl(roles.cust_acct_site_id,0)
316 from fnd_user fnd,
317 hz_parties party,
318 hz_cust_account_roles roles
319 where fnd.customer_id = party.party_id
320 and party.party_type='PARTY_RELATIONSHIP'
321 and party.party_id = roles.party_id
322 and roles.status ='A'
323 and party.status='A'
324 and fnd.user_id=lUserID
325 UNION -----added the below query for bug# 7456410 ,8467122
326 SELECT
327 Nvl(NULL,0) ,
328 party.person_first_name,
329 party.person_last_name,
330 party.email_address,
331 party.party_name,
332 cust.cust_account_id ,
333 Nvl(NULL,0)
334 FROM
335 fnd_user fnd,
336 hz_parties party,
337 hz_cust_accounts cust
338 WHERE fnd.customer_id = party.party_id
339 AND party.party_type='PERSON'
340 AND party.status = 'A'
341 AND cust.party_id=party.party_id
342 AND fnd.user_id=lUserID;
343
344 --
345 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
346 --
347 BEGIN
348 IF (C_Party%ISOPEN) THEN
349 CLOSE C_Party;
350 END IF;
351
352 OPEN C_Party;
353 FETCH C_Party into pContactId,
354 pUserFName,
355 pUserLName,
356 pUserEmail,
357 pCustName ,
358 pCustomerID,
359 pCustomerAddrID;
360 CLOSE C_Party;
361 EXCEPTION
362 when no_data_found then
363 pStatusCode:=-99;
364 END getContactDetails;
365
366 procedure initialize is
367
368 lvContactID varchar2(80);
369
370 --
371 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
372 --
373 Begin
374
375
376 lvContactID := icx_sec.getID(icx_sec.PV_CUST_CONTACT_ID);
377
378 -- Get the Customer contact information
379 gCustFContact := fnd_profile.value_wnps('ONT_FEEDBACK_PROFILE');
380
381 ----------------------------------------------------
382 -- Set the Global message variables here for caching
383 ----------------------------------------------------
384 gHelp :=getMessage('ONT_HELP');
385 gReload :=getMessage('ONT_RELOAD');
386 gMenu :=getMessage('ONT_MENU');
387 gSave :=getMessage('ONT_SAVE');
388 gExit :=getMessage('ONT_EXIT');
389
390
391 if lvContactID is not NULL then
392 gContactID := to_number(lvContactID);
393
394 Begin
395 select party.person_first_name,
396 party.person_last_name,
397 rel_party.email_address,
398 cust_party.party_name,
399 cst.cust_account_id,
400 nvl(con.cust_acct_site_id,0)
401 INTO gUserFName,
402 gUserLName,
403 gUserEmail,
404 gCustName ,
405 gCustomerID,
406 gCustomerAddrID
407 from hz_cust_accounts cst,
408 hz_parties cust_party,
409 hz_cust_account_roles con,
410 hz_parties party,
411 hz_parties rel_party,
412 hz_relationships rel,
413 hz_cust_accounts acct
414 where
415 cst.cust_account_id = con.cust_account_id
416 and con.cust_account_role_id = gContactID
417 and rownum = 1
418 and cst.party_id = cust_party.party_id
419 and con.party_id = rel.party_id
420 and con.role_type = 'CONTACT'
421 and rel.subject_id = party.party_id
422 and rel.subject_table_name = 'HZ_PARTIES'
423 and rel.object_table_name = 'HZ_PARTIES'
424 and rel.object_id = acct.party_id
425 and acct.cust_account_id = con.cust_account_id
426 and rel.party_id = rel_party.party_id;
427
428 exception
429 when no_data_found then
430 null;
431 end;
432
433
434 end if;
435 exception
436
437 when no_data_found then
438 null;
439
440 end initialize;
441
442 function getMessage(pMsgName varchar2,
443 pTokenName1 varchar2 DEFAULT NULL,
444 pTokenValue1 varchar2 DEFAULT NULL,
445 pTokenName2 varchar2 DEFAULT NULL,
446 pTokenValue2 varchar2 DEFAULT NULL,
447 pTokenName3 varchar2 DEFAULT NULL,
448 pTokenValue3 varchar2 DEFAULT NULL,
449 pTokenName4 varchar2 DEFAULT NULL,
450 pTokenValue4 varchar2 DEFAULT NULL,
451 pTokenName5 varchar2 DEFAULT NULL,
452 pTokenValue5 varchar2 DEFAULT NULL) return varchar2 is
453 --
454 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
455 --
456 begin
457
458 FND_MESSAGE.SET_NAME('ONT',pMsgName);
459
460 if (pTokenName1 is NOT NULL) THEN
461 FND_MESSAGE.SET_TOKEN(pTokenName1,pTokenValue1);
462 end if;
463
464 if (pTokenName2 is NOT NULL) THEN
465 FND_MESSAGE.SET_TOKEN(pTokenName2,pTokenValue2);
466 end if;
467
468 if (pTokenName3 is NOT NULL) THEN
469 FND_MESSAGE.SET_TOKEN(pTokenName3,pTokenValue3);
470 end if;
471
472 if (pTokenName4 is NOT NULL) THEN
473 FND_MESSAGE.SET_TOKEN(pTokenName4,pTokenValue4);
474 end if;
475
476 if (pTokenName5 is NOT NULL) THEN
477 FND_MESSAGE.SET_TOKEN(pTokenName5,pTokenValue5);
478 end if;
479
480 return(FND_MESSAGE.GET);
481
482 end getMessage;
483
484 function getRecCount(pCurrent number,
485 pPageTot number,
486 pTotal number) return varchar2 is
487 --
488 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
489 --
490 begin
491 return(getMessage('ONT_RECORD_COUNT','FIRSTREC',pCurrent,'LASTREC',pPageTot,'TOTALREC',pTotal));
492 end getRecCount;
493
494
495 FUNCTION Get_Released_Status_Name(
496 p_source_code IN VARCHAR2,
497 p_released_status IN VARCHAR2,
498 p_oe_interfaced_flag IN VARCHAR2,
499 p_inv_interfaced_flag IN VARCHAR2,
500 p_move_order_line_id IN NUMBER)
501 RETURN VARCHAR2 IS
502
503 l_released_status_name VARCHAR2(50) := null;
504
505 BEGIN
506 IF (p_source_code = 'OE'
507 AND p_released_status = 'C'
508 AND p_oe_interfaced_flag = 'Y'
509 AND p_inv_interfaced_flag IN ('X','Y'))
510 OR
511 (p_source_code <> 'OE'
512 AND p_released_status = 'C'
513 AND p_inv_interfaced_flag = 'Y') THEN
514
515 BEGIN
516
517 SELECT meaning
518 INTO l_released_status_name
519 FROM wsh_lookups
520 WHERE lookup_type = 'PICK_STATUS'
521 AND lookup_code = 'I';
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 NULL;
526 END;
527 ELSE
528 BEGIN
529 -- bug 4267981
530 IF p_released_status = 'S' AND p_move_order_line_id is null THEN
531 SELECT meaning
532 INTO l_released_status_name
533 FROM wsh_lookups
534 WHERE lookup_type = 'PICK_STATUS'
535 AND lookup_code = 'K';
536 ELSE
537 SELECT meaning
538 INTO l_released_status_name
539 FROM wsh_lookups
540 WHERE lookup_type = 'PICK_STATUS'
541 AND lookup_code = p_released_status;
542 END IF;
543 EXCEPTION
544 WHEN OTHERS THEN
545 NULL;
546 END;
547 END IF;
548 RETURN l_released_status_name;
549
550 END Get_Released_Status_Name;
551
552 PROCEDURE Get_Price_formatted(
553 p_transactional_curr_code IN VARCHAR2,
554 p_price IN NUMBER,
555 p_line_category_code IN VARCHAR2,
556 x_price_formatted OUT NOCOPY VARCHAR2
557 )
558 IS
559 l_precision NUMBER;
560 l_ext_precision NUMBER;
561 l_min_acct_unit NUMBER;
562 l_precision_type VARCHAR2(30);
563 l_format_mask VARCHAR2(240);
564 --
565 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
566 --
567
568 BEGIN
569
570 IF l_debug_level > 0 THEN
571 oe_debug_pub.add('Entering ONT_OIP_COMMON.get_price_formatted ');
572 END IF ;
573
574 FND_CURRENCY.GET_INFO(Currency_Code => p_transactional_curr_code,
575 precision => l_precision,
576 ext_precision => l_ext_precision,
577 min_acct_unit => l_min_acct_unit );
578
579 IF l_debug_level > 0 THEN
580 oe_debug_pub.add('precision: '|| l_precision );
581 oe_debug_pub.add('ext precision: '|| l_ext_precision );
582 END IF;
583
584 fnd_profile.get('ONT_UNIT_PRICE_PRECISION_TYPE', l_precision_type);
585
586 IF l_debug_level > 0 THEN
587 oe_debug_pub.add('precision_type: '|| l_precision_type);
588 END IF;
589
590 IF (l_precision_type = 'EXTENDED') THEN
591
592 FND_CURRENCY.Build_Format_Mask(
593 format_mask => l_format_mask
594 ,field_length => 60
595 ,precision => l_ext_precision
596 ,min_acct_unit => l_min_acct_unit
597 ,disp_grp_sep => TRUE);
598 ELSE
599
600 FND_CURRENCY.Build_Format_Mask(
601 format_mask => l_format_mask
602 ,field_length => 60
603 ,precision => l_precision
604 ,min_acct_unit => l_min_acct_unit
605 ,disp_grp_sep => TRUE);
606 END IF;
607
608 IF l_debug_level > 0 THEN
609 oe_debug_pub.add('l_format_mask: '|| l_format_mask );
610 END IF;
611
612 BEGIN
613 select To_Char(p_price*decode(p_line_category_code,'RETURN',-1,1),l_format_mask)
614 into x_price_formatted
615 from dual;
616 END;
617
618 IF l_debug_level > 0 THEN
619 oe_debug_pub.add('x_price_formatted '|| x_price_formatted,1 );
620 oe_debug_pub.add('Exiting ONT_OIP_COMMON.get_price_formatted ',1);
621 END IF;
622
623 END Get_Price_formatted;
624
625 END ONT_OIP_Common;