[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_CUSTOM_PACKAGE
Source
1 PACKAGE BODY OKC_XPRT_CUSTOM_PACKAGE AS
2 /* $Header: okcxprtudvtestprocb.pls 120.8.12010000.2 2008/12/17 10:14:28 kkolukul noship $ */
3
4 -- Define GLOBAL CONSTANTS
5 -- Always use the package name and procedure name in error messages
6 -- for easy debug
7
8 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_CUSTOM_PACKAGE';
9 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
10 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
11
12
13 -- The following lines define true, false and product constants
14
15
16 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
17 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
18 G_OKC CONSTANT VARCHAR2(3) := 'OKC';
19
20 --
21 -- The following lines define The return status from the procedure
22 -- The procedure must return on of these statuses in X_RETURN_STATUS
23 --
24
25 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
26 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
27 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
28
29 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
30 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
31 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
32
33
34 PROCEDURE GET_OE_HEADER_VALUES (
35 P_DOC_TYPE IN VARCHAR2,
36 P_DOC_ID IN NUMBER,
37 P_VARIABLE_CODE IN VARCHAR2,
38 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
39 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
40 X_MSG_COUNT OUT NOCOPY NUMBER,
41 X_MSG_DATA OUT NOCOPY VARCHAR2
42 )
43 IS
44
45
46 -- TO CUSTOMIZE: Change the l_api_name value to this custom procedure
47 -- Define local variables for variables addressed in this API
48 -- Create appropriate cursor statements (use p_doc_id parameter)
49
50 l_api_name CONSTANT VARCHAR2(30) := 'GET_OE_HEADER_VALUES';
51
52 l_blanket_number OE_ORDER_HEADERS_ALL.BLANKET_NUMBER%TYPE;
53 l_user_status_code OE_ORDER_HEADERS_ALL.USER_STATUS_CODE%TYPE;
54 l_context OE_ORDER_HEADERS_ALL.CONTEXT%TYPE;
55
56
57 --
58 -- Define Cursor to read the variable value for the document
59 -- If you are reading data from multiple tables with multiple SELECT statements,
60 -- define all cursors here with appropriate names.
61 --
62 -- The following cursor is defined to retrieve values for the user defined variables
63 -- for a sales order (DOCUMENT_TYPE = 'O')
64 --
65 Cursor l_oe_header_csr Is
66 SELECT BLANKET_NUMBER,
67 USER_STATUS_CODE,
68 CONTEXT
69 FROM OE_ORDER_HEADERS_ALL
70 WHERE HEADER_ID = p_doc_id;
71
72 BEGIN
73
74
75 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
76 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
77 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
78 END IF;
79
80 x_return_status := G_RET_STS_SUCCESS;
81
82
83
84 -- TO CUSTOMIZE: Check for appropriate P_DOC_TYPE
85 -- Change cursor names and INTO variables with local variables defined above
86 -- Modify IF...ELSE statements appropriately to assign correct value to the our parameter
87
88
89 IF P_DOC_TYPE = 'O' THEN
90 OPEN l_oe_header_csr;
91 FETCH l_oe_header_csr INTO l_blanket_number, l_user_status_code, l_context;
92 CLOSE l_oe_header_csr;
93
94 IF P_VARIABLE_CODE = 'OE$BLANKET_NUMBER' THEN
95 X_VARIABLE_VALUE_ID := l_blanket_number;
96
97 ELSIF P_VARIABLE_CODE = 'OE$USER_STATUS_CODE' THEN
98 X_VARIABLE_VALUE_ID := l_user_status_code;
99
100 ELSIF P_VARIABLE_CODE = 'OE$CONTEXT' THEN
101 X_VARIABLE_VALUE_ID := l_context;
102
103 END IF;
104 END IF;
105
106
107 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
108
109 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
110 '1000: Variable Codes along with values:');
111
112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
113 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
114 ' = ' || X_VARIABLE_VALUE_ID );
115
116 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
117 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
118 END IF;
119
120 EXCEPTION
121 --
122 -- retain all error handling below as it is. Do not change
123 --
124
125 -- TO CUSTOMIZE: Close all cursors
126 -- DO NOT delete debug statements.
127 -- Add more debug statements if required. Follow the same structure for debug statements
128
129
130 WHEN FND_API.G_EXC_ERROR THEN
131 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
132 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
133 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
134 END IF;
135
136 IF l_oe_header_csr%ISOPEN THEN
137 CLOSE l_oe_header_csr;
138 END IF;
139
140 --
141 -- if you have more cursors, add cursor closing statements here as shown above
142 --
143
144 x_return_status := G_RET_STS_ERROR ;
145 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
146 p_count => x_msg_count,
147 p_data => x_msg_data );
148
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
152 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
153 END IF;
154
155 IF l_oe_header_csr%ISOPEN THEN
156 CLOSE l_oe_header_csr;
157 END IF;
158
159 --
160 -- if you have more cursors, add close cursor statements here as shown above
161 --
162
163 x_return_status := G_RET_STS_UNEXP_ERROR ;
164 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
165
166 WHEN OTHERS THEN
167 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
168 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
169 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
170 END IF;
171
172 IF l_oe_header_csr%ISOPEN THEN
173 CLOSE l_oe_header_csr;
174 END IF;
175
176 --
177 -- if you have more cursors, add close cursor statements here as shown above
178 --
179
180 x_return_status := G_RET_STS_UNEXP_ERROR ;
181 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
182
183 END GET_OE_HEADER_VALUES;
184
185
186 PROCEDURE GET_PRICE_UPDATE_TOLERANCE (
187 P_DOC_TYPE IN VARCHAR2,
188 P_DOC_ID IN NUMBER,
189 P_VARIABLE_CODE IN VARCHAR2,
190 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
191 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
192 X_MSG_COUNT OUT NOCOPY NUMBER,
193 X_MSG_DATA OUT NOCOPY VARCHAR2
194 )
195 IS
196
197 l_api_name CONSTANT VARCHAR2(30) := 'GET_PRICE_UPDATE_TOLERANCE';
198
199 l_price_update_tolerance PO_HEADERS_ALL.PRICE_UPDATE_TOLERANCE%TYPE;
200
201
202 Cursor l_price_update_csr Is
203 select PRICE_UPDATE_TOLERANCE from PO_HEADERS_ALL
204 WHERE po_header_id = P_DOC_ID ;
205
206 --AND TYPE_LOOKUP_CODE = P_DOC_TYPE
207
208 BEGIN
209
210 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
212 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
213 END IF;
214
215 x_return_status := G_RET_STS_SUCCESS;
216
217
218 OPEN l_price_update_csr;
219 FETCH l_price_update_csr INTO l_price_update_tolerance;
220 dbms_output.put_line('l_price_update_tolerance IS '||l_price_update_tolerance);
221 l_price_update_tolerance := l_price_update_tolerance+10;
222 CLOSE l_price_update_csr;
223
224 IF ( P_VARIABLE_CODE = 'PO$PRICE_TOLERANCE1' OR P_VARIABLE_CODE = 'PO$PRICE_TOLERANCE' ) AND P_DOC_TYPE = 'PA_BLANKET' THEN
225 X_VARIABLE_VALUE_ID := l_price_update_tolerance;
226 END IF;
227
228
229 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
230
231 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
232 '1000: Variable Codes along with values:');
233
234 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
235 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
236 ' = ' || X_VARIABLE_VALUE_ID );
237
238 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
239 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
240 END IF;
241
242 EXCEPTION
243
244 WHEN FND_API.G_EXC_ERROR THEN
245 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
246 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
247 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
248 END IF;
249
250 IF l_price_update_csr%ISOPEN THEN
251 CLOSE l_price_update_csr;
252 END IF;
253
254
255 x_return_status := G_RET_STS_ERROR ;
256 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
257 p_count => x_msg_count,
258 p_data => x_msg_data );
259
260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
262 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
263 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
264 END IF;
265
266 IF l_price_update_csr%ISOPEN THEN
267 CLOSE l_price_update_csr;
268 END IF;
269
270 x_return_status := G_RET_STS_UNEXP_ERROR ;
271 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
272
273 WHEN OTHERS THEN
274 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
275 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
276 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
277 END IF;
278
279 IF l_price_update_csr%ISOPEN THEN
280 CLOSE l_price_update_csr;
281 END IF;
282
283 x_return_status := G_RET_STS_UNEXP_ERROR ;
284 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
285
286 END GET_PRICE_UPDATE_TOLERANCE;
287
288 PROCEDURE GET_QUOTE_SALES_SUPPLEMENT (
289 P_DOC_TYPE IN VARCHAR2,
290 P_DOC_ID IN NUMBER,
291 P_VARIABLE_CODE IN VARCHAR2,
292 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
293 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
294 X_MSG_COUNT OUT NOCOPY NUMBER,
295 X_MSG_DATA OUT NOCOPY VARCHAR2
296 )
297 IS
298
299 l_api_name CONSTANT VARCHAR2(30) := 'GET_QUOTE_SALES_SUPPLEMENT';
300 l_tmp_instance_id ASO_SUP_tmpl_INSTANCE.TEMPLATE_INSTANCE_ID%TYPE;
301 l_tmp_value ASO_SUP_INSTANCE_VALUE.value%TYPE;
302 l_tmp1_value ASO_SUP_INSTANCE_VALUE.value%TYPE;
303 l_tmp2_value ASO_SUP_response_tl.response_name%TYPE;
304
305
306
307 Cursor l_tmp_instance_csr is
308 SELECT TEMPLATE_INSTANCE_ID FROM ASO_SUP_tmpl_INSTANCE where owner_table_id = P_DOC_ID;
309
310 --cursor to get text values
311 Cursor l_sup_value_csr(p_tmp_instace_id number,p_comp_name varchar2) is
312 SELECT value FROM ASO_SUP_INSTANCE_VALUE
313 WHERE template_instance_id = p_tmp_instace_id
314 AND value is not null;
315 -- AND sect_comp_map_id =
316 -- (select component_id from ASO_SUP_component_tl where component_name = p_comp_name and language = 'US');
317
318
319 --cursor to get LOV values
320 -- Modified on 03232007 to return Value id instead of Value name from fnd_flex_values_vl
321
322 Cursor l_sup_lov_value_csr(p_tmp_instace_id number,p_comp_name varchar2) is
323 SELECT flex_value_id
324 FROM fnd_flex_values_vl
325 WHERE flex_value_set_id = (SELECT flex_value_set_id
326 FROM fnd_flex_value_sets
327 -- Added the Flex vbalue set where condition again on 04162007
328 WHERE flex_value_set_name = 'Industry Type') --1022577 -- Need to be known from setup for 'Industry Type'
329 AND flex_value = (SELECT response_name
330 FROM ASO_SUP_response_tl
331 WHERE response_id IN (SELECT response_id
332 FROM ASO_SUP_INSTANCE_VALUE
333 WHERE template_instance_id = p_tmp_instace_id)
334 AND language = USERENV('LANG'));
335
336 --Cursor l_sup_lov_value_csr(p_tmp_instace_id number,p_comp_name varchar2) is
337 --select response_name from ASO_SUP_response_tl where response_id in
338 --(SELECT response_id FROM ASO_SUP_INSTANCE_VALUE WHERE template_instance_id = p_tmp_instace_id)
339 --and language = 'US';
340
341
342 BEGIN
343
344 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
345 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
346 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
347 END IF;
348
349
350 x_return_status := G_RET_STS_SUCCESS;
351
352 dbms_output.put_line('start of proc ');
353 OPEN l_tmp_instance_csr;
354 FETCH l_tmp_instance_csr INTO l_tmp_instance_id;
355 CLOSE l_tmp_instance_csr;
356 dbms_output.put_line('l_tmp_instance_id IS '||l_tmp_instance_id);
357
358 OPEN l_sup_value_csr(l_tmp_instance_id,'Benefit');
359 FETCH l_sup_value_csr INTO l_tmp_value;
360 CLOSE l_sup_value_csr;
361 dbms_output.put_line('l_tmp_value IS '||l_tmp_value);
362
363 OPEN l_sup_value_csr(l_tmp_instance_id,'Name of VAD');
364 FETCH l_sup_value_csr INTO l_tmp1_value;
365 CLOSE l_sup_value_csr;
366 dbms_output.put_line('l_tmp1_value IS '||l_tmp1_value);
367
368 --getting lov value
369 OPEN l_sup_lov_value_csr(l_tmp_instance_id,'Industry-Type');
370 FETCH l_sup_lov_value_csr INTO l_tmp2_value;
371 CLOSE l_sup_lov_value_csr;
372 dbms_output.put_line('l_tmp2_value IS '||l_tmp2_value);
373
374
375 IF P_VARIABLE_CODE = 'QUOTE$SUP_VAD' THEN
376 X_VARIABLE_VALUE_ID := l_tmp_value;
377 ELSIF P_VARIABLE_CODE = 'QUOTE$BENEFIT' THEN
378 X_VARIABLE_VALUE_ID := l_tmp1_value;
379 ELSIF P_VARIABLE_CODE = 'QUOTE$INDL' THEN
380 X_VARIABLE_VALUE_ID := l_tmp2_value;
381 END IF;
382
383
384 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
385
386 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
387 '1000: Variable Codes along with values:');
388
389 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
390 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
391 ' = ' || X_VARIABLE_VALUE_ID );
392
393 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
394 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
395 END IF;
396
397 EXCEPTION
398
399 WHEN FND_API.G_EXC_ERROR THEN
400 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
402 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
403 END IF;
404
405
406 x_return_status := G_RET_STS_ERROR ;
407 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
408 p_count => x_msg_count,
409 p_data => x_msg_data );
410
411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
412 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
413 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
414 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
415 END IF;
416
417 x_return_status := G_RET_STS_UNEXP_ERROR ;
418 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
419
420 WHEN OTHERS THEN
421
422 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
424 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
425 END IF;
426
427
428 x_return_status := G_RET_STS_UNEXP_ERROR ;
429 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
430
431 END GET_QUOTE_SALES_SUPPLEMENT;
432
433 PROCEDURE GET_SHIP_WAREHOUSE (
434 P_DOC_TYPE IN VARCHAR2,
435 P_DOC_ID IN NUMBER,
436 P_VARIABLE_CODE IN VARCHAR2,
437 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
438 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
439 X_MSG_COUNT OUT NOCOPY NUMBER,
440 X_MSG_DATA OUT NOCOPY VARCHAR2
441 )
442 IS
443
444 l_api_name CONSTANT VARCHAR2(30) := 'GET_SHIP_WAREHOUSE';
445
446 l_ship_warehouse MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
447
448
449 Cursor l_ship_warehouse_csr Is
450 SELECT SHIP_FROM_ORG_ID
451 FROM OE_BLANKET_HEADERS_ALL
452 WHERE HEADER_ID = P_DOC_ID;
453
454 BEGIN
455
456 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
457 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
458 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
459 END IF;
460
461 x_return_status := G_RET_STS_SUCCESS;
462
463
464 OPEN l_ship_warehouse_csr;
465 FETCH l_ship_warehouse_csr INTO l_ship_warehouse;
466 CLOSE l_ship_warehouse_csr;
467
468 IF P_VARIABLE_CODE = 'SA$WHS' AND P_DOC_TYPE = 'B' THEN
469 X_VARIABLE_VALUE_ID := l_ship_warehouse;
470 END IF;
471
472
473 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474
475 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
476 '1000: Variable Codes along with values:');
477
478 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
479 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
480 ' = ' || X_VARIABLE_VALUE_ID );
481
482 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
483 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
484 END IF;
485
486 EXCEPTION
487
488 WHEN FND_API.G_EXC_ERROR THEN
489 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
491 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
492 END IF;
493
494 IF l_ship_warehouse_csr%ISOPEN THEN
495 CLOSE l_ship_warehouse_csr;
496 END IF;
497
498
499 x_return_status := G_RET_STS_ERROR ;
500 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
501 p_count => x_msg_count,
502 p_data => x_msg_data );
503
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
507 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
508 END IF;
509
510 IF l_ship_warehouse_csr%ISOPEN THEN
511 CLOSE l_ship_warehouse_csr;
512 END IF;
513
514 x_return_status := G_RET_STS_UNEXP_ERROR ;
515 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
516
517 WHEN OTHERS THEN
518 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
520 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
521 END IF;
522
523 IF l_ship_warehouse_csr%ISOPEN THEN
524 CLOSE l_ship_warehouse_csr;
525 END IF;
526
527 x_return_status := G_RET_STS_UNEXP_ERROR ;
528 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
529
530 END GET_SHIP_WAREHOUSE;
531
532 PROCEDURE GET_SOURCING_UOM (
533 P_DOC_TYPE IN VARCHAR2,
534 P_DOC_ID IN NUMBER,
535 P_VARIABLE_CODE IN VARCHAR2,
536 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
537 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
538 X_MSG_COUNT OUT NOCOPY NUMBER,
539 X_MSG_DATA OUT NOCOPY VARCHAR2
540 )
541 IS
542
543 l_api_name CONSTANT VARCHAR2(30) := 'GET_SOURCING_UOM';
544
545 l_uom_code PON_AUCTION_ITEM_PRICES_ALL.UOM_CODE%TYPE;
546
547
548 Cursor l_rfq_uom_code_csr Is
549 SELECT UOM
550 FROM PON_BID_ITEM_PRICES
551 WHERE BID_NUMBER = P_DOC_ID;
552
553 Cursor l_quote_uom_code_csr Is
554 SELECT UOM_CODE
555 FROM PON_AUCTION_ITEM_PRICES_ALL
556 WHERE BEST_BID_NUMBER = P_DOC_ID;
557
558 BEGIN
559
560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
561 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
562 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
563 END IF;
564
565 x_return_status := G_RET_STS_SUCCESS;
566
567
568 IF (P_VARIABLE_CODE = 'RF$UOM' OR P_VARIABLE_CODE = 'RFQ$UOM') THEN
569 IF P_DOC_TYPE = 'RFQ' THEN
570 OPEN l_rfq_uom_code_csr;
571 FETCH l_rfq_uom_code_csr INTO l_uom_code;
572 CLOSE l_rfq_uom_code_csr;
573 END IF;
574
575 IF P_DOC_TYPE = 'RFQ_RESPONSE' THEN
576 OPEN l_quote_uom_code_csr;
577 FETCH l_quote_uom_code_csr INTO l_uom_code;
578 CLOSE l_quote_uom_code_csr;
579 END IF;
580
581 X_VARIABLE_VALUE_ID := l_uom_code;
582 END IF;
583
584
585 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
586
587 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
588 '1000: Variable Codes along with values:');
589
590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
591 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
592 ' = ' || X_VARIABLE_VALUE_ID );
593
594 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
595 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
596 END IF;
597
598 EXCEPTION
599
600 WHEN FND_API.G_EXC_ERROR THEN
601 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
602 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
603 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
604 END IF;
605
606 IF l_rfq_uom_code_csr%ISOPEN THEN
607 CLOSE l_rfq_uom_code_csr;
608 END IF;
609
610 IF l_quote_uom_code_csr%ISOPEN THEN
611 CLOSE l_quote_uom_code_csr;
612 END IF;
613
614
615 x_return_status := G_RET_STS_ERROR ;
616 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
617 p_count => x_msg_count,
618 p_data => x_msg_data );
619
620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
621 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
622 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
623 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
624 END IF;
625
626 IF l_rfq_uom_code_csr%ISOPEN THEN
627 CLOSE l_rfq_uom_code_csr;
628 END IF;
629
630 IF l_quote_uom_code_csr%ISOPEN THEN
631 CLOSE l_quote_uom_code_csr;
632 END IF;
633
634
635 x_return_status := G_RET_STS_UNEXP_ERROR ;
636 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
637
638 WHEN OTHERS THEN
639 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
640 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
641 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
642 END IF;
643
644 IF l_rfq_uom_code_csr%ISOPEN THEN
645 CLOSE l_rfq_uom_code_csr;
646 END IF;
647
648 IF l_quote_uom_code_csr%ISOPEN THEN
649 CLOSE l_quote_uom_code_csr;
650 END IF;
651
652 x_return_status := G_RET_STS_UNEXP_ERROR ;
653 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
654
655 END GET_SOURCING_UOM;
656
657 PROCEDURE GET_PO_UOM (
658 P_DOC_TYPE IN VARCHAR2,
659 P_DOC_ID IN NUMBER,
660 P_VARIABLE_CODE IN VARCHAR2,
661 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
662 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
663 X_MSG_COUNT OUT NOCOPY NUMBER,
664 X_MSG_DATA OUT NOCOPY VARCHAR2
665 )
666 IS
667
668 l_api_name CONSTANT VARCHAR2(30) := 'GET_PO_UOM';
669
670 l_uom_code PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE%TYPE;
671
672
673 -- Modified the cursor for bug 6010684
674 Cursor l_uom_code_csr Is
675 SELECT UOM_CODE
676 FROM MTL_UNITS_OF_MEASURE
677 WHERE UNIT_OF_MEASURE = (SELECT UNIT_MEAS_LOOKUP_CODE
678 FROM PO_LINES_ALL
679 WHERE PO_HEADER_ID = P_DOC_ID);
680 --Cursor l_uom_code_csr Is
681 --SELECT UNIT_MEAS_LOOKUP_CODE
682 --FROM PO_LINES_ALL
683 --WHERE PO_HEADER_ID = P_DOC_ID;
684
685 BEGIN
686
687 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
688 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
689 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
690 END IF;
691
692 x_return_status := G_RET_STS_SUCCESS;
693
694
695 OPEN l_uom_code_csr;
696 FETCH l_uom_code_csr INTO l_uom_code;
697 CLOSE l_uom_code_csr;
698
699 IF (P_VARIABLE_CODE = 'PO$UOM' OR P_VARIABLE_CODE = 'POS$UOM') AND P_DOC_TYPE = 'PO_STANDARD' THEN
700 X_VARIABLE_VALUE_ID := l_uom_code;
701 END IF;
702
703
704 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
705
706 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
707 '1000: Variable Codes along with values:');
708
709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
710 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
711 ' = ' || X_VARIABLE_VALUE_ID );
712
713 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
714 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
715 END IF;
716
717 EXCEPTION
718
719 WHEN FND_API.G_EXC_ERROR THEN
720 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
722 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
723 END IF;
724
725 IF l_uom_code_csr%ISOPEN THEN
726 CLOSE l_uom_code_csr;
727 END IF;
728
729
730 x_return_status := G_RET_STS_ERROR ;
731 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
732 p_count => x_msg_count,
733 p_data => x_msg_data );
734
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
736 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
737 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
738 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
739 END IF;
740
741 IF l_uom_code_csr%ISOPEN THEN
742 CLOSE l_uom_code_csr;
743 END IF;
744
745 x_return_status := G_RET_STS_UNEXP_ERROR ;
746 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
747
748 WHEN OTHERS THEN
749 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
751 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
752 END IF;
753
754 IF l_uom_code_csr%ISOPEN THEN
755 CLOSE l_uom_code_csr;
756 END IF;
757
758 x_return_status := G_RET_STS_UNEXP_ERROR ;
759 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
760
761 END GET_PO_UOM;
762
763 PROCEDURE GET_RELEASE_RATIO (
764 P_DOC_TYPE IN VARCHAR2,
765 P_DOC_ID IN NUMBER,
766 P_VARIABLE_CODE IN VARCHAR2,
767 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
768 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
769 X_MSG_COUNT OUT NOCOPY NUMBER,
770 X_MSG_DATA OUT NOCOPY VARCHAR2
771 )
772 IS
773
774 l_api_name CONSTANT VARCHAR2(30) := 'GET_RELEASE_RATIO';
775
776 l_release_ratio NUMBER;
777
778
779 Cursor l_release_ratio_csr Is
780 SELECT NVL(MIN_RELEASE_AMOUNT,0)/NVL(AMOUNT_LIMIT, 1)
781 FROM PO_HEADERS_ALL
782 WHERE PO_HEADER_ID = P_DOC_ID;
783
784 BEGIN
785
786 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
787 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
788 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
789 END IF;
790
791 x_return_status := G_RET_STS_SUCCESS;
792
793
794 OPEN l_release_ratio_csr;
795 FETCH l_release_ratio_csr INTO l_release_ratio;
796 CLOSE l_release_ratio_csr;
797
798 IF P_VARIABLE_CODE = 'PO$RELRAT' AND P_DOC_TYPE = 'PA_BLANKET' THEN
799 X_VARIABLE_VALUE_ID := l_release_ratio;
800 END IF;
801
802
803 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
804
805 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
806 '1000: Variable Codes along with values:');
807
808 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
809 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
810 ' = ' || X_VARIABLE_VALUE_ID );
811
812 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
813 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
814 END IF;
815
816 EXCEPTION
817
818 WHEN FND_API.G_EXC_ERROR THEN
819 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
820 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
821 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
822 END IF;
823
824 IF l_release_ratio_csr%ISOPEN THEN
825 CLOSE l_release_ratio_csr;
826 END IF;
827
828
829 x_return_status := G_RET_STS_ERROR ;
830 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
831 p_count => x_msg_count,
832 p_data => x_msg_data );
833
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
836 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
837 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
838 END IF;
839
840 IF l_release_ratio_csr%ISOPEN THEN
841 CLOSE l_release_ratio_csr;
842 END IF;
843
844 x_return_status := G_RET_STS_UNEXP_ERROR ;
845 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
846
847 WHEN OTHERS THEN
848 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
850 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
851 END IF;
852
853 IF l_release_ratio_csr%ISOPEN THEN
854 CLOSE l_release_ratio_csr;
855 END IF;
856
857 x_return_status := G_RET_STS_UNEXP_ERROR ;
858 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
859
860 END GET_RELEASE_RATIO;
861
862 PROCEDURE GET_BLANKET_AMOUNT_RANGE (
863 P_DOC_TYPE IN VARCHAR2,
864 P_DOC_ID IN NUMBER,
865 P_VARIABLE_CODE IN VARCHAR2,
866 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
867 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
868 X_MSG_COUNT OUT NOCOPY NUMBER,
869 X_MSG_DATA OUT NOCOPY VARCHAR2
870 )
871 IS
872
873 l_api_name CONSTANT VARCHAR2(30) := 'GET_BLANKET_AMOUNT_RANGE';
874
875 l_blanket_amount_range NUMBER;
876
877
878 Cursor l_blanket_amount_range_csr Is
879 SELECT BLANKET_MAX_AMOUNT - BLANKET_MIN_AMOUNT
880 FROM OE_BLANKET_HEADERS_EXT
881 WHERE ORDER_NUMBER = (SELECT ORDER_NUMBER
882 FROM OE_BLANKET_HEADERS_ALL
883 WHERE HEADER_ID = P_DOC_ID);
884
885 BEGIN
886
887 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
888 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
889 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
890 END IF;
891
892 x_return_status := G_RET_STS_SUCCESS;
893
894
895 OPEN l_blanket_amount_range_csr;
896 FETCH l_blanket_amount_range_csr INTO l_blanket_amount_range;
897 CLOSE l_blanket_amount_range_csr;
898
899 IF P_VARIABLE_CODE = 'SA$BLRAN' AND P_DOC_TYPE = 'B' THEN
900 X_VARIABLE_VALUE_ID := l_blanket_amount_range;
901 END IF;
902
903
904 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
905
906 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
907 '1000: Variable Codes along with values:');
908
909 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
910 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
911 ' = ' || X_VARIABLE_VALUE_ID );
912
913 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
914 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
915 END IF;
916
917 EXCEPTION
918
919 WHEN FND_API.G_EXC_ERROR THEN
920 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
921 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
922 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
923 END IF;
924
925 IF l_blanket_amount_range_csr%ISOPEN THEN
926 CLOSE l_blanket_amount_range_csr;
927 END IF;
928
929
930 x_return_status := G_RET_STS_ERROR ;
931 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
932 p_count => x_msg_count,
933 p_data => x_msg_data );
934
935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
936 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
937 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
938 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
939 END IF;
940
941 IF l_blanket_amount_range_csr%ISOPEN THEN
942 CLOSE l_blanket_amount_range_csr;
943 END IF;
944
945 x_return_status := G_RET_STS_UNEXP_ERROR ;
946 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
947
948 WHEN OTHERS THEN
949 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
950 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
951 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
952 END IF;
953
954 IF l_blanket_amount_range_csr%ISOPEN THEN
955 CLOSE l_blanket_amount_range_csr;
956 END IF;
957
958 x_return_status := G_RET_STS_UNEXP_ERROR ;
959 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
960
961 END GET_BLANKET_AMOUNT_RANGE;
962
963 PROCEDURE GET_DFF_PO (
964 P_DOC_TYPE IN VARCHAR2,
965 P_DOC_ID IN NUMBER,
966 P_VARIABLE_CODE IN VARCHAR2,
967 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
968 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
969 X_MSG_COUNT OUT NOCOPY NUMBER,
970 X_MSG_DATA OUT NOCOPY VARCHAR2
971 )
972 IS
973
974 l_api_name CONSTANT VARCHAR2(30) := 'GET_DFF_PO';
975
976 l_attribute4_val PO_HEADERS_ALL.ATTRIBUTE4%TYPE;
977
978
979 Cursor l_attribute4_val_csr Is
980 SELECT ATTRIBUTE4
981 FROM PO_HEADERS_ALL
982 WHERE PO_HEADER_ID = P_DOC_ID;
983
984 BEGIN
985
986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
988 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
989 END IF;
990
991 x_return_status := G_RET_STS_SUCCESS;
992
993
994 OPEN l_attribute4_val_csr;
995 FETCH l_attribute4_val_csr INTO l_attribute4_val;
996 CLOSE l_attribute4_val_csr;
997
998 IF P_VARIABLE_CODE = 'PO$DFF' AND P_DOC_TYPE IN ( 'PA_BLANKET', 'PO_STANDARD') THEN
999 X_VARIABLE_VALUE_ID := l_attribute4_val;
1000 END IF;
1001
1002
1003 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1004
1005 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1006 '1000: Variable Codes along with values:');
1007
1008 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1009 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
1010 ' = ' || X_VARIABLE_VALUE_ID );
1011
1012 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1013 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1014 END IF;
1015
1016 EXCEPTION
1017
1018 WHEN FND_API.G_EXC_ERROR THEN
1019 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1020 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1021 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1022 END IF;
1023
1024 IF l_attribute4_val_csr%ISOPEN THEN
1025 CLOSE l_attribute4_val_csr;
1026 END IF;
1027
1028
1029 x_return_status := G_RET_STS_ERROR ;
1030 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
1031 p_count => x_msg_count,
1032 p_data => x_msg_data );
1033
1034 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1035 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1037 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1038 END IF;
1039
1040 IF l_attribute4_val_csr%ISOPEN THEN
1041 CLOSE l_attribute4_val_csr;
1042 END IF;
1043
1044 x_return_status := G_RET_STS_UNEXP_ERROR ;
1045 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1046
1047 WHEN OTHERS THEN
1048 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1049 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1050 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1051 END IF;
1052
1053 IF l_attribute4_val_csr%ISOPEN THEN
1054 CLOSE l_attribute4_val_csr;
1055 END IF;
1056
1057 x_return_status := G_RET_STS_UNEXP_ERROR ;
1058 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1059
1060 END GET_DFF_PO;
1061
1062 PROCEDURE GET_DFF_SA (
1063 P_DOC_TYPE IN VARCHAR2,
1064 P_DOC_ID IN NUMBER,
1065 P_VARIABLE_CODE IN VARCHAR2,
1066 X_VARIABLE_VALUE_ID IN OUT NOCOPY VARCHAR2,
1067 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1068 X_MSG_COUNT OUT NOCOPY NUMBER,
1069 X_MSG_DATA OUT NOCOPY VARCHAR2
1070 )
1071 IS
1072
1073 l_api_name CONSTANT VARCHAR2(30) := 'GET_DFF_SA';
1074
1075 l_attribute4_val OE_BLANKET_HEADERS_ALL.ATTRIBUTE4%TYPE := NULL;
1076
1077
1078 Cursor l_attribute4_val_csr Is
1079 SELECT ATTRIBUTE4
1080 FROM OE_BLANKET_HEADERS_ALL
1081 WHERE HEADER_ID = P_DOC_ID;
1082
1083 Cursor l_flex_value_id_csr(p_flex_value varchar2) is
1084 SELECT flex_value_id
1085 FROM fnd_flex_values_vl ffvv,okc_bus_variables_b obvb
1086 WHERE ffvv.flex_value_set_id = obvb.VALUE_SET_ID and
1087 obvb.VARIABLE_CODE = p_variable_code and
1088 ffvv.FLEX_VALUE = p_flex_value;
1089
1090
1091 BEGIN
1092
1093 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1094 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1095 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1096 END IF;
1097
1098 x_return_status := G_RET_STS_SUCCESS;
1099
1100
1101 OPEN l_attribute4_val_csr;
1102 FETCH l_attribute4_val_csr INTO l_attribute4_val;
1103 CLOSE l_attribute4_val_csr;
1104
1105 IF P_VARIABLE_CODE = 'SA$DFF' AND P_DOC_TYPE = 'B' AND l_attribute4_val IS NOT NULL THEN
1106 OPEN l_flex_value_id_csr(l_attribute4_val);
1107 FETCH l_flex_value_id_csr INTO X_VARIABLE_VALUE_ID;
1108 CLOSE l_flex_value_id_csr;
1109 ELSE
1110 X_VARIABLE_VALUE_ID := NULL;
1111 END IF;
1112
1113
1114 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1115
1116 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1117 '1000: Variable Codes along with values:');
1118
1119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1120 'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
1121 ' = ' || X_VARIABLE_VALUE_ID );
1122
1123 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1124 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1125 END IF;
1126
1127 EXCEPTION
1128
1129 WHEN FND_API.G_EXC_ERROR THEN
1130 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1131 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1132 '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1133 END IF;
1134
1135 IF l_attribute4_val_csr%ISOPEN THEN
1136 CLOSE l_attribute4_val_csr;
1137 END IF;
1138
1139
1140 x_return_status := G_RET_STS_ERROR ;
1141 FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
1142 p_count => x_msg_count,
1143 p_data => x_msg_data );
1144
1145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1146 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1147 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1148 '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1149 END IF;
1150
1151 IF l_attribute4_val_csr%ISOPEN THEN
1152 CLOSE l_attribute4_val_csr;
1153 END IF;
1154
1155 x_return_status := G_RET_STS_UNEXP_ERROR ;
1156 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1157
1158 WHEN OTHERS THEN
1159 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1160 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1161 '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1162 END IF;
1163
1164 IF l_attribute4_val_csr%ISOPEN THEN
1165 CLOSE l_attribute4_val_csr;
1166 END IF;
1167
1168 x_return_status := G_RET_STS_UNEXP_ERROR ;
1169 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1170
1171 END GET_DFF_SA;
1172
1173 END OKC_XPRT_CUSTOM_PACKAGE;
1174