[Home] [Help]
PACKAGE BODY: APPS.PON_CLM_UTIL_PKG
Source
1 PACKAGE BODY PON_CLM_UTIL_PKG as
2 /* $Header: PONCLMUTILB.pls 120.15.12020000.2 2013/02/09 09:28:21 hvutukur ship $ */
3 --g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_AUCTION_APPROVAL_PKG.';
4
5 FUNCTION IS_DOCUMENT_FEDERAL(p_doc_type_id IN NUMBER) RETURN NUMBER IS
6 l_is_doc_federal number := 0;
7 l_rule_count number;
8 BEGIN
9 SELECT count(*)
10 INTO l_rule_count
11 FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules,
12 PON_AUC_BIZRULES pon_auc_bizrules
13 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
14 AND pon_auc_doctype_rules.DOCTYPE_ID = p_doc_type_id
15 AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION'
16 AND pon_auc_doctype_rules.default_value = 'Y';
17
18 if l_rule_count > 0 then
19 l_is_doc_federal := 1;
20 end if;
21 return l_is_doc_federal;
22
23 END IS_DOCUMENT_FEDERAL;
24
25 FUNCTION IS_AMENDMENT(p_auction_header_id IN NUMBER) RETURN VARCHAR2 IS
26 l_is_amendment varchar2(1);
27 BEGIN
28 select nvl(amendment_flag,'N') into l_is_amendment from pon_auction_headers_all
29 where auction_header_id = p_auction_header_id;
30 return l_is_amendment;
31 END IS_AMENDMENT;
32
33 FUNCTION IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id IN NUMBER) RETURN NUMBER IS
34 l_is_doc_federal number := 0;
35 l_doctype_id number;
36 BEGIN
37
38 select auh.doctype_id
39 into l_doctype_id
40 from pon_auction_headers_all auh
41 where auh.auction_header_id = p_auction_header_id;
42 l_is_doc_federal := is_document_federal(l_doctype_id);
43
44
45 return l_is_doc_federal;
46
47 END IS_NEG_DOCUMENT_FEDERAL;
48
49
50
51 FUNCTION GET_COPY_ERR_MSGS(p_msg_count IN NUMBER, p_is_unexp_error OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
52 l_err_msg varchar2(2000);
53 l_loop_error varchar2(200);
54 l_amend_new_round_err varchar2(200);
55 BEGIN
56 fnd_message.set_name('PON', 'PON_MULTI_NEWRND_OR_AMND_ERR');
57 l_amend_new_round_err := fnd_message.get;
58 p_is_unexp_error := 'N';
59 IF ( p_msg_count > 0) THEN
60 FOR i IN 1..p_msg_count LOOP
61 l_loop_error := FND_MSG_PUB.Get(p_msg_index => i,p_encoded => 'F');
62 if l_amend_new_round_err = l_loop_error then
63 p_is_unexp_error := 'Y';
64 return l_amend_new_round_err;
65 end if;
66 l_err_msg := l_err_msg || fnd_global.local_chr(10) || FND_MSG_PUB.Get(p_msg_index => i,p_encoded => 'F');
67 END LOOP;
68 END IF;
69 return l_err_msg;
70 END GET_COPY_ERR_MSGS;
71
72 FUNCTION IS_UDA_ENABLED(p_doc_type_id IN NUMBER) RETURN NUMBER IS
73
74 BEGIN
75 return IS_DOCUMENT_FEDERAL(p_doc_type_id);
76 END IS_UDA_ENABLED;
77
78
79 PROCEDURE UPDATE_HEADER_STYLE_ID(p_auction_header_id IN NUMBER,
80 p_style_id IN NUMBER) IS
81
82 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HEADER_STYLE_ID';
83 l_api_version CONSTANT NUMBER := 1.0;
84
85 BEGIN
86 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
87 FND_LOG.string(log_level => FND_LOG.level_statement,
88 module => l_api_name,
89 message => 'BEGIN ::' || p_auction_header_id || ':::'
90 || p_style_id);
91 END IF;
92
93 update pon_auction_headers_all
94 set po_style_id = p_style_id
95 where
96 auction_header_id = p_auction_header_id;
97
98 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
99 FND_LOG.string(log_level => FND_LOG.level_statement,
100 module => l_api_name,
101 message => 'Updated style id successfully.');
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
107 FND_LOG.string(log_level => FND_LOG.level_statement,
108 module => l_api_name,
109 message => 'Error Occured while updating style id.');
110 END IF;
111
112 END UPDATE_HEADER_STYLE_ID;
113
114 FUNCTION getCLMStatus ( user_id NUMBER, resp_id NUMBER, appl_id NUMBER ) RETURN VARCHAR2
115 IS
116 Profile_Value_Site_Level VARCHAR2(1);
117 Profile_Value_Resp_Level VARCHAR2(1);
118 BEGIN
119 Profile_Value_Site_Level := NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N');
120 Profile_Value_Resp_Level := NVL(FND_PROFILE.VALUE_SPECIFIC( NAME => 'PO_CLM_ENABLED',
121 RESPONSIBILITY_ID => resp_id),'N');
122
123 IF Profile_Value_Site_Level = 'N' THEN
124 RETURN Profile_Value_Site_Level;
125 ELSE
126 RETURN Profile_Value_Resp_Level;
127 END IF;
128
129 EXCEPTION WHEN OTHERS THEN
130 RETURN 'N';
131 END getCLMStatus;
132
133
134
135 FUNCTION Isclminstalled RETURN BOOLEAN
136 IS
137 BEGIN
138 if getCLMStatus (fnd_global.user_id, fnd_global.resp_id,fnd_global.resp_appl_id) ='Y' THEN
139 RETURN TRUE;
140 ELSE
141 RETURN FALSE;
142 END IF;
143 EXCEPTION WHEN OTHERS THEN
144 RETURN FALSE;
145 END isclminstalled;
146
147
148 FUNCTION ISCLMRESP RETURN VARCHAR2
149 IS
150 BEGIN
151 if getCLMStatus (fnd_global.user_id, fnd_global.resp_id,fnd_global.resp_appl_id) ='Y' THEN
152 RETURN 'Y';
153 ELSE
154 RETURN 'N';
155 END IF;
156 EXCEPTION WHEN OTHERS THEN
157 RETURN 'N';
158 END ISCLMRESP;
159
160 FUNCTION clm_pon_auc_hdr_vpd_fun (object_schema in varchar2, object_name varchar2) return varchar2 AS
161 ProfileValue VARCHAR2(1);
162 p_user_id NUMBER;
163 p_resp_id NUMBER;
164 p_appl_id NUMBER;
165 p_emp_id NUMBER;
166 p_proc_name VARCHAR2(100) := 'PON.PLSQL.CLM_PON_AUC_HDR_VPD_FUN';
167 BEGIN
168 p_user_id := fnd_global.user_id;
169 p_resp_id := fnd_global.resp_id;
170 p_appl_id := fnd_global.resp_appl_id;
171 SELECT Nvl(employee_id,-999) INTO p_emp_id FROM fnd_user WHERE user_id = p_user_id;
172
173 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
174 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Enter clm_pon_auc_hdr_vpd_fun Procedure');
175 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_user_id : ' || p_user_id);
176 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_resp_id : ' || p_resp_id);
177 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_appl_id : ' || p_appl_id);
178 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_emp_id : ' || p_emp_id);
179 END IF;
180
181 IF p_emp_id <> -999 THEN
182 ProfileValue := PON_CLM_UTIL_PKG.getCLMStatus(p_user_id, p_resp_id, p_appl_id);
183
184 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
185 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'ProfileValue : '||ProfileValue);
186 END IF;
187
188 IF ProfileValue <> 'N' THEN
189 return ' exists ( SELECT PADR.DEFAULT_VALUE FROM PON_AUC_DOCTYPE_RULES PADR, PON_AUC_BIZRULES PAB WHERE
190 PADR.BIZRULE_ID = PAB.BIZRULE_ID AND PADR.DOCTYPE_ID = PON_AUCTION_HEADERS.DOCTYPE_ID AND
191 PAB.NAME = ''FEDERAL_NEGOTIATION'' AND PADR.DEFAULT_VALUE = ''Y'') ';
192 ELSE
193 return ' not exists ( SELECT PADR.DEFAULT_VALUE FROM PON_AUC_DOCTYPE_RULES PADR, PON_AUC_BIZRULES PAB WHERE
194 PADR.BIZRULE_ID = PAB.BIZRULE_ID AND PADR.DOCTYPE_ID = PON_AUCTION_HEADERS.DOCTYPE_ID AND
195 PAB.NAME = ''FEDERAL_NEGOTIATION'' AND PADR.DEFAULT_VALUE = ''Y'') ';
196 END IF;
197 ELSE
198 return ' 1=1 ';
199 END IF;
200 EXCEPTION WHEN OTHERS THEN
201 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
202 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Error : '||SQLERRM);
203 END IF;
204 END clm_pon_auc_hdr_vpd_fun;
205
206
207
208 FUNCTION IS_LINE_CLIN(p_auction_header_id IN Number, p_line_number IN Number) return boolean IS
209 l_clin_line Boolean := FALSE;
210 l_group_line_id Number;
211 BEGIN
212 select group_line_id into l_group_line_id
213 from pon_auction_item_prices_all
214 where auction_header_id = p_auction_header_id
215 and line_number = p_line_number;
216
217 if ( l_group_line_id IS Null ) THEN
218 Return TRUE;
219 Else
220 Return False;
221 End IF;
222 End IS_LINE_CLIN;
223
224 FUNCTION IS_LINE_PRICED(p_auction_header_id IN Number, p_line_number IN Number) return boolean
225 IS
226 l_info_flag varchar2(1);
227 Begin
228 select nvl(clm_info_flag,'N') into l_info_flag
229 from pon_auction_item_prices_all
230 where auction_header_id = p_auction_header_id
231 and line_number = p_line_number;
232
233 If ( l_info_flag = 'Y' ) Then
234 Return False;
235 Else
236 Return True;
237 End If;
238 End IS_LINE_PRICED;
239
240 Function DOES_CLIN_HAVE_CHILD(p_auction_header_id IN Number, p_line_number IN Number) return boolean
241 IS
242 l_child_count Number;
243 BEGIN
244 select count(*) into l_child_count
245 from pon_auction_item_prices_all
246 where auction_header_id = p_auction_header_id
247 and group_line_id = p_line_number;
248
249 if ( l_child_count = 0 ) then
250 return False;
251 else
252 return True;
253 end if;
254 End Does_Clin_Have_Child;
255
256 Function DOES_LINE_HAVE_OPTION(p_auction_header_id IN Number, p_line_number IN Number) return boolean
257 IS
258 l_child_count Number;
259 BEGIN
260 select count(*) into l_child_count
261 from pon_auction_item_prices_all
262 where auction_header_id = p_auction_header_id
263 and CLM_BASE_LINE_NUM = p_line_number;
264
265 if ( l_child_count = 0 ) then
266 return False;
267 else
268 return True;
269 end if;
270 End Does_LINE_Have_OPTION;
271
272 /* Bug 10056890 - Added the following function to return document number in CLM Format. */
273
274 FUNCTION GET_DOCUMENT_NUMBER(p_auction_header_id IN Number) return varchar2 AS
275 docNumber PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
276 BEGIN
277 SELECT document_number INTO docNumber FROM pon_auction_headers_all
278 WHERE auction_header_id = p_auction_header_id;
279
280 IF docNumber IS NULL THEN
281 RETURN p_auction_header_id;
282 END IF;
283
284 RETURN docNumber;
285
286 EXCEPTION WHEN OTHERS THEN
287 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
288 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'get_document_number.invoked', 'Error : '||SQLERRM);
289 END IF;
290 RETURN p_auction_header_id;
291 END GET_DOCUMENT_NUMBER;
292 -- Bug:9954903-start
293 function get_clm_pdf_name(p_docNumber in varchar2,
294 p_revisionNum in Number,
295 p_language in varchar2,
296 p_userViewType in varchar2) return varchar2 as
297 fileName varchar2(300);
298 begin
299 fileName := p_docNumber;
300
301 if p_revisionNum is not null then
302 fileName := fileName || '_';
303 fileName := fileName || p_revisionNum;
304 end if;
305
306 fileName := fileName || '_';
307
308 if p_language is null then
309 fileName := fileName || 'US';
310 else
311 fileName := fileName || p_language;
312 end if;
313
314 return(fileName);
315 exception
316 when others then
317 return('');
318 end get_clm_pdf_name;
319 --Bug:9954903-end
320
321 FUNCTION GET_CLM_COMMERCIAL_DOC_NUMBER(P_AUCTION_HEADER_ID IN NUMBER) RETURN VARCHAR2
322 IS
323 L_IS_DOCUMENT_FEDERAL NUMBER:=0;
324 L_DOCUMENT_NUMBER PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
325 BEGIN
326
327 /* Offer Enhancements Changes */
328 L_IS_DOCUMENT_FEDERAL:= IS_NEG_DOCUMENT_FEDERAL(P_AUCTION_HEADER_ID);
329
330 IF L_IS_DOCUMENT_FEDERAL=1
331 THEN
332 SELECT DOCUMENT_NUMBER INTO L_DOCUMENT_NUMBER
333 FROM
334 PON_AUCTION_HEADERS
335 WHERE
336 AUCTION_HEADER_ID=(SELECT AUCTION_HEADER_ID_ORIG_ROUND
337 FROM
338 PON_AUCTION_HEADERS PAH2
339 WHERE
340 PAH2.AUCTION_HEADER_ID=P_AUCTION_HEADER_ID
341 );
342 ELSE
343 SELECT DOCUMENT_NUMBER INTO L_DOCUMENT_NUMBER
344 FROM
345 PON_AUCTION_HEADERS
346 WHERE
347 AUCTION_HEADER_ID=P_AUCTION_HEADER_ID;
348 END IF;
349
350 RETURN L_DOCUMENT_NUMBER;
351
352 END;
353
354 PROCEDURE UPDATE_UMBRELLA_PROG(p_auction_header_id IN NUMBER,
355 p_program_id IN NUMBER ) IS
356
357 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_UMBRELLA_PROG';
358 l_api_version CONSTANT NUMBER := 1.0;
359
360 BEGIN
361 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
362 FND_LOG.string(log_level => FND_LOG.level_statement,
363 module => l_api_name,
364 message => 'BEGIN ::' || p_auction_header_id || ':::'
365 || p_program_id);
366 END IF;
367 update pon_auction_headers_all
368 set umbrella_program_id = p_program_id
369 where
370 auction_header_id = p_auction_header_id;
371 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
372 FND_LOG.string(log_level => FND_LOG.level_statement,
373 module => l_api_name,
374 message => 'Updated program id successfully.');
375 END IF;
376 EXCEPTION
377 WHEN OTHERS THEN
378 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
379 FND_LOG.string(log_level => FND_LOG.level_statement,
380 module => l_api_name,
381 message => 'Error Occured while updating program id.');
382 END IF;
383 END UPDATE_UMBRELLA_PROG;
384
385 /*Function to check if Line Structure changes is enabled in Sourcing*/
386 FUNCTION get_line_structure_enabled(p_auction_header_id IN NUMBER) RETURN VARCHAR2
387 IS
388
389 l_encumberence_flag VARCHAR2(1);
390 l_outcome_document_type VARCHAR2(100);
391
392 l_api_name VARCHAR2(30) := 'get_line_structure_enabled';
393
394 BEGIN
395
396 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
397 FND_LOG.string(log_level => FND_LOG.level_statement,
398 module => l_api_name,
399 message => 'BEGIN ::' || p_auction_header_id );
400 END IF;
401
402
403 SELECT nvl(fsp.purch_encumbrance_flag, 'N'), pah.contract_type INTO l_encumberence_flag,l_outcome_document_type
404 FROM financials_system_params_all fsp, pon_auction_headers_all pah
405 where pah.auction_header_id = p_auction_header_id
406 and fsp.org_id = pah.org_id ;
407
408 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
409 FND_LOG.string(log_level => FND_LOG.level_statement,
410 module => l_api_name,
411 message => 'l_encumberence_flag: '||l_encumberence_flag ||
412 'l_outcome_document_type: '||l_outcome_document_type);
413 END IF;
414
415 IF(IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id => p_auction_header_id) = 1
416 AND l_encumberence_flag = 'Y' AND l_outcome_document_type = 'STANDARD' ) THEN
417
418 RETURN 'Y';
419 END IF;
420
421 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
422 FND_LOG.string(log_level => FND_LOG.level_statement,
423 module => l_api_name,
424 message => 'END');
425 END IF;
426 RETURN 'N';
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
431 FND_LOG.string(log_level => FND_LOG.level_statement,
432 module => l_api_name,
433 message => 'Error Occured in IF statement');
434 END IF;
435 RETURN('N');
436
437 END get_line_structure_enabled;
438
439 end PON_CLM_UTIL_PKG;