[Home] [Help]
PACKAGE BODY: APPS.ONT_OIP_COMMON
Source
1 package body ONT_OIP_Common as
2 /* $Header: ontcomnb.pls 120.4 2006/01/19 12:03:52 smwong noship $ */
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
18 procedure getContactDetails(lUserId in number,
19 pContactId out nocopy number,
20
21 pUserFName out nocopy varchar2,
22
23 pUserLName out nocopy varchar2,
24
25 pUserEmail out nocopy varchar2,
26
27 pCustName out nocopy varchar2,
28
29 pCustomerID out nocopy number,
30
31 pCustomerAddrID out nocopy number,
32
33 pStatusCode out nocopy number) is
34
35 CURSOR C_PARTY IS
36 select roles.cust_account_role_id,
37 party.person_first_name,
38 party.person_last_name,
39 party.email_address,
40 party.party_name,
41 roles.cust_account_id,
42 nvl(roles.cust_acct_site_id,0)
43 from fnd_user fnd,
44 hz_parties party,
45 hz_cust_account_roles roles
46 where fnd.customer_id = party.party_id
47 and party.party_type='PARTY_RELATIONSHIP'
48 and party.party_id = roles.party_id
49 and roles.status ='A'
50 and party.status='A'
51 and fnd.user_id=lUserID;
52 --
53 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
54 --
55 BEGIN
56 IF (C_Party%ISOPEN) THEN
57 CLOSE C_Party;
58 END IF;
59
60 OPEN C_Party;
61 FETCH C_Party into pContactId,
62 pUserFName,
63 pUserLName,
64 pUserEmail,
65 pCustName ,
66 pCustomerID,
67 pCustomerAddrID;
68 CLOSE C_Party;
69 EXCEPTION
70 when no_data_found then
71 pStatusCode:=-99;
72 END getContactDetails;
73
74 procedure initialize is
75
76 lvContactID varchar2(80);
77
78 --
79 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
80 --
81 Begin
82
83
84 lvContactID := icx_sec.getID(icx_sec.PV_CUST_CONTACT_ID);
85
86 -- Get the Customer contact information
87 gCustFContact := fnd_profile.value_wnps('ONT_FEEDBACK_PROFILE');
88
89 ----------------------------------------------------
90 -- Set the Global message variables here for caching
91 ----------------------------------------------------
92 gHelp :=getMessage('ONT_HELP');
93 gReload :=getMessage('ONT_RELOAD');
94 gMenu :=getMessage('ONT_MENU');
95 gSave :=getMessage('ONT_SAVE');
96 gExit :=getMessage('ONT_EXIT');
97
98
99 if lvContactID is not NULL then
100 gContactID := to_number(lvContactID);
101
102 Begin
103 select party.person_first_name,
104 party.person_last_name,
105 rel_party.email_address,
106 cust_party.party_name,
107 cst.cust_account_id,
108 nvl(con.cust_acct_site_id,0)
109 INTO gUserFName,
110 gUserLName,
111 gUserEmail,
112 gCustName ,
113 gCustomerID,
114 gCustomerAddrID
115 from hz_cust_accounts cst,
116 hz_parties cust_party,
117 hz_cust_account_roles con,
118 hz_parties party,
119 hz_parties rel_party,
120 hz_relationships rel,
121 hz_cust_accounts acct
122 where
123 cst.cust_account_id = con.cust_account_id
124 and con.cust_account_role_id = gContactID
125 and rownum = 1
126 and cst.party_id = cust_party.party_id
127 and con.party_id = rel.party_id
128 and con.role_type = 'CONTACT'
129 and rel.subject_id = party.party_id
130 and rel.subject_table_name = 'HZ_PARTIES'
131 and rel.object_table_name = 'HZ_PARTIES'
132 and rel.object_id = acct.party_id
133 and acct.cust_account_id = con.cust_account_id
134 and rel.party_id = rel_party.party_id;
135
136 exception
137 when no_data_found then
138 null;
139 end;
140
141
142 end if;
143 exception
144
145 when no_data_found then
146 null;
147
148 end initialize;
149
150 function getMessage(pMsgName varchar2,
151 pTokenName1 varchar2 DEFAULT NULL,
152 pTokenValue1 varchar2 DEFAULT NULL,
153 pTokenName2 varchar2 DEFAULT NULL,
154 pTokenValue2 varchar2 DEFAULT NULL,
155 pTokenName3 varchar2 DEFAULT NULL,
156 pTokenValue3 varchar2 DEFAULT NULL,
157 pTokenName4 varchar2 DEFAULT NULL,
158 pTokenValue4 varchar2 DEFAULT NULL,
159 pTokenName5 varchar2 DEFAULT NULL,
160 pTokenValue5 varchar2 DEFAULT NULL) return varchar2 is
161 --
162 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
163 --
164 begin
165
166 FND_MESSAGE.SET_NAME('ONT',pMsgName);
167
168 if (pTokenName1 is NOT NULL) THEN
169 FND_MESSAGE.SET_TOKEN(pTokenName1,pTokenValue1);
170 end if;
171
172 if (pTokenName2 is NOT NULL) THEN
173 FND_MESSAGE.SET_TOKEN(pTokenName2,pTokenValue2);
174 end if;
175
176 if (pTokenName3 is NOT NULL) THEN
177 FND_MESSAGE.SET_TOKEN(pTokenName3,pTokenValue3);
178 end if;
179
180 if (pTokenName4 is NOT NULL) THEN
181 FND_MESSAGE.SET_TOKEN(pTokenName4,pTokenValue4);
182 end if;
183
184 if (pTokenName5 is NOT NULL) THEN
185 FND_MESSAGE.SET_TOKEN(pTokenName5,pTokenValue5);
186 end if;
187
188 return(FND_MESSAGE.GET);
189
190 end getMessage;
191
192 function getRecCount(pCurrent number,
193 pPageTot number,
194 pTotal number) return varchar2 is
195 --
196 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
197 --
198 begin
199 return(getMessage('ONT_RECORD_COUNT','FIRSTREC',pCurrent,'LASTREC',pPageTot,'TOTALREC',pTotal));
200 end getRecCount;
201
202
203 FUNCTION Get_Released_Status_Name(
204 p_source_code IN VARCHAR2,
205 p_released_status IN VARCHAR2,
206 p_oe_interfaced_flag IN VARCHAR2,
207 p_inv_interfaced_flag IN VARCHAR2,
208 p_move_order_line_id IN NUMBER)
209 RETURN VARCHAR2 IS
210
211 l_released_status_name VARCHAR2(50) := null;
212
213 BEGIN
214 IF (p_source_code = 'OE'
215 AND p_released_status = 'C'
216 AND p_oe_interfaced_flag = 'Y'
217 AND p_inv_interfaced_flag IN ('X','Y'))
218 OR
219 (p_source_code <> 'OE'
220 AND p_released_status = 'C'
221 AND p_inv_interfaced_flag = 'Y') THEN
222
223 BEGIN
224
225 SELECT meaning
226 INTO l_released_status_name
227 FROM wsh_lookups
228 WHERE lookup_type = 'PICK_STATUS'
229 AND lookup_code = 'I';
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 NULL;
234 END;
235 ELSE
236 BEGIN
237 -- bug 4267981
238 IF p_released_status = 'S' AND p_move_order_line_id is null THEN
239 SELECT meaning
240 INTO l_released_status_name
241 FROM wsh_lookups
242 WHERE lookup_type = 'PICK_STATUS'
243 AND lookup_code = 'K';
244 ELSE
245 SELECT meaning
246 INTO l_released_status_name
247 FROM wsh_lookups
248 WHERE lookup_type = 'PICK_STATUS'
249 AND lookup_code = p_released_status;
250 END IF;
251 EXCEPTION
252 WHEN OTHERS THEN
253 NULL;
254 END;
255 END IF;
256 RETURN l_released_status_name;
257
258 END Get_Released_Status_Name;
259
260 PROCEDURE Get_Price_formatted(
261 p_transactional_curr_code IN VARCHAR2,
262 p_price IN NUMBER,
263 p_line_category_code IN VARCHAR2,
264 x_price_formatted OUT NOCOPY VARCHAR2
265 )
266 IS
267 l_precision NUMBER;
268 l_ext_precision NUMBER;
269 l_min_acct_unit NUMBER;
270 l_precision_type VARCHAR2(30);
271 l_format_mask VARCHAR2(240);
272 --
273 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
274 --
275
276 BEGIN
277
278 IF l_debug_level > 0 THEN
279 oe_debug_pub.add('Entering ONT_OIP_COMMON.get_price_formatted ');
280 END IF ;
281
282 FND_CURRENCY.GET_INFO(Currency_Code => p_transactional_curr_code,
283 precision => l_precision,
284 ext_precision => l_ext_precision,
285 min_acct_unit => l_min_acct_unit );
286
287 IF l_debug_level > 0 THEN
288 oe_debug_pub.add('precision: '|| l_precision );
289 oe_debug_pub.add('ext precision: '|| l_ext_precision );
290 END IF;
291
292 fnd_profile.get('ONT_UNIT_PRICE_PRECISION_TYPE', l_precision_type);
293
294 IF l_debug_level > 0 THEN
295 oe_debug_pub.add('precision_type: '|| l_precision_type);
296 END IF;
297
298 IF (l_precision_type = 'EXTENDED') THEN
299
300 FND_CURRENCY.Build_Format_Mask(
301 format_mask => l_format_mask
302 ,field_length => 60
303 ,precision => l_ext_precision
304 ,min_acct_unit => l_min_acct_unit
305 ,disp_grp_sep => TRUE);
306 ELSE
307
308 FND_CURRENCY.Build_Format_Mask(
309 format_mask => l_format_mask
310 ,field_length => 60
311 ,precision => l_precision
312 ,min_acct_unit => l_min_acct_unit
313 ,disp_grp_sep => TRUE);
314 END IF;
315
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add('l_format_mask: '|| l_format_mask );
318 END IF;
319
320 BEGIN
321 select To_Char(p_price*decode(p_line_category_code,'RETURN',-1,1),l_format_mask)
322 into x_price_formatted
323 from dual;
324 END;
325
326 IF l_debug_level > 0 THEN
327 oe_debug_pub.add('x_price_formatted '|| x_price_formatted,1 );
328 oe_debug_pub.add('Exiting ONT_OIP_COMMON.get_price_formatted ',1);
329 END IF;
330
331 END Get_Price_formatted;
332
333 END ONT_OIP_Common;