1 PACKAGE BODY OE_GSA_UTIL AS
2 /* $Header: OEXUGSAB.pls 120.1 2005/06/13 18:31:52 appldev $ */
3
4 -- Global constants holding the package name.
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_GSA_UTIL';
7
8 g_header_rec OE_Order_PUB.Header_Rec_Type;
9
10
11 FUNCTION Check_GSA_Main
12 (p_line_rec IN OE_Order_Pub.Line_Rec_Type,
13 x_resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) RETURN VARCHAR2
14 IS
15 l_gsa_enabled_flag VARCHAR2(1):= 'N';
16 l_gsa_indicator_flag VARCHAR2(1):= 'N';
17 l_gsa_passed_flag VARCHAR2(1):= 'Y';
18 --
19 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
20 --
21 BEGIN
22
23 IF l_debug_level > 0 THEN
24 oe_debug_pub.add( 'INSIDE CHECK_GSA_MAIN' ) ;
25 END IF;
26
27 /* -----------------------------------------------------------------------
28 Function Check_GSA_Enabled checks if GSA verification is enabled or
29 not by checking the profile options
30 ------------------------------------------------------------------------*/
31 l_gsa_enabled_flag := Check_GSA_Enabled (p_line_rec);
32
33 IF l_gsa_enabled_flag = 'Y' THEN
34 BEGIN
35 IF l_debug_level > 0 THEN
36 oe_debug_pub.add( 'GSA_ENABLED = Y' ) ;
37 END IF;
38 l_gsa_indicator_flag := 'N';
39
40 /* -----------------------------------------------------------------------
41 Function Check_GSA_Indicator checks if the customer or its bill-to-site
42 is GSA to accordingly check further.
43 ------------------------------------------------------------------------*/
44 l_gsa_indicator_flag := Check_GSA_Indicator (p_line_rec);
45 IF l_gsa_indicator_flag = 'Y' THEN
46 IF l_debug_level > 0 THEN
47 oe_debug_pub.add( 'GSA_INDICATOR = Y' ) ;
48 END IF;
49
50 /* -----------------------------------------------------------------------
51 For now, we have commented out nocopy the part in the following line where
52
53 we check for the price offered to a Non-GSA customer against the price
54 entered in the order for a GSA customer.
55 This is due to the performance issues. We may enable it in the future
56 releases.
57 ------------------------------------------------------------------------*/
58 /* l_gsa_passed_flag := Check_GSA_Customer (p_line_rec); */
59 null;
60 ELSE
61 IF l_debug_level > 0 THEN
62 oe_debug_pub.add( 'GSA_INDICATOR = N' ) ;
63 END IF;
64 /* -----------------------------------------------------------------------
65 Function Check_NonGSA_Customer checks for the price offered to a
66 GSA customer against the price entered in the order for a NonGSA customer
67 and returns whether the check passed or no.
68 ------------------------------------------------------------------------*/
69 l_gsa_passed_flag := Check_NonGSA_Customer (p_line_rec);
70 END IF;
71 END;
72 END IF;
73
74 IF l_gsa_passed_flag = 'Y' THEN
75 IF l_debug_level > 0 THEN
76 oe_debug_pub.add( 'GSA_PASSED = Y' ) ;
77 END IF;
78 x_resultout := 'COMPLETE:AP_PASS';
79 ELSE
80 IF l_debug_level > 0 THEN
81 oe_debug_pub.add( 'GSA_PASSED = N' ) ;
82 END IF;
83 x_resultout := 'COMPLETE:AP_FAIL';
84 END IF;
85
86 RETURN x_resultout;
87
88 end Check_GSA_Main;
89
90
91 FUNCTION Check_GSA_Enabled
92 (p_line_rec IN OE_Order_Pub.Line_Rec_Type) RETURN VARCHAR2
93 IS
94 l_profile_option_value VARCHAR2(1):= 'N';
95 --
96 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
97 --
98 BEGIN
99
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'INSIDE GSA_ENABLED' ) ;
102 END IF;
103
104 l_profile_option_value := 'N';
105 l_profile_option_value := FND_PROFILE.value ('QP_VERIFY_GSA');
106
107 RETURN l_profile_option_value;
108
109
110 EXCEPTION
111
112 WHEN NO_DATA_FOUND THEN
113 IF l_debug_level > 0 THEN
114 oe_debug_pub.add( 'INSIDE NO-DATA-FOUND EXCEPTION' ) ;
115 END IF;
116 l_profile_option_value := 'N';
117
118 WHEN OTHERS THEN
119 -- Unexpected error
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add( 'INSIDE OTHERS EXCEPTION' ) ;
122 END IF;
123 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
124 THEN
125 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
126 'Check_GSA_Violation. GSA Violation Price ');
127 END IF;
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129
130 END Check_GSA_Enabled;
131
132 FUNCTION Check_GSA_Indicator
133 (p_line_rec IN OE_Order_Pub.Line_Rec_Type) RETURN VARCHAR2
134 IS
135 l_invoice_to_org_id NUMBER;
136 l_site_use_id NUMBER;
137 l_customer_id NUMBER;
138 l_gsa_flag VARCHAR2(1):= 'N';
139 --
140 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
141 --
142 BEGIN
143
144
145 BEGIN
146 IF l_debug_level > 0 THEN
147 oe_debug_pub.add( 'INSIDE GSA_INDICATOR_CUSTOMER' , 5 ) ;
148 END IF;
149
150 /* SELECT NVL(GSA_INDICATOR,'N')
151 INTO l_gsa_flag
152 FROM OE_SOLD_TO_ORGS_V STO, RA_CUSTOMERS C
153 WHERE STO.ORGANIZATION_ID = l_invoice_to_org_id
154 AND STO.CUSTOMER_ID (+)= C.CUSTOMER_ID; */
155 --added the following select and assignment statement to fix bug 1738379 Begin
156 l_invoice_to_org_id := p_line_rec.invoice_to_org_id;
157 select /* MOAC_SQL_CHANGE */ nvl(gsa_indicator,'N')
158 into l_gsa_flag
159 from hz_cust_site_uses_all hsu
160 where site_use_id = l_invoice_to_org_id ;
161 /* AND NVL(hsu.org_id,
162 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1), ' ',NULL,
163 SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),-99)) =
164 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,
165 SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99); */
166 IF l_debug_level > 0 THEN
167 oe_debug_pub.add( 'GSA_FLAG VALUE1:'||L_GSA_FLAG , 5 ) ;
168 END IF;
169 --added the above select and assignment statement to fix bug 1738379 End
170
171 EXCEPTION
172
173 WHEN NO_DATA_FOUND THEN
174 IF l_debug_level > 0 THEN
175 oe_debug_pub.add( 'INSIDE NO-DATA-FOUND EXCEPTION 1' , 5 ) ;
176 END IF;
177 l_gsa_flag := 'N';
178
179 WHEN OTHERS THEN
180 -- Unexpected error
181 IF l_debug_level > 0 THEN
182 oe_debug_pub.add( 'INSIDE OTHERS EXCEPTION' ) ;
183 END IF;
184 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
185 THEN
186 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
187 'Check_GSA_Violation. GSA Violation Price ');
188 END IF;
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END;
191
192 IF l_gsa_flag = 'N' THEN
193 BEGIN
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( 'INSIDE GSA_INDICATOR_SITE' , 5 ) ;
196 END IF;
197 --commented to fix bug 1738379
198 /* SELECT NVL(GSA_INDICATOR,'N')
199 INTO l_gsa_flag
200 FROM OE_INVOICE_TO_ORGS_V ITO, RA_SITE_USES SU
201 WHERE ITO.ORGANIZATION_ID = l_invoice_to_org_id
202 AND ITO.SITE_USE_ID = SU.SITE_USE_ID; */
203 --added the following select and assignment statement to fix bug 1738379 Begin
204 l_customer_id := p_line_rec.sold_to_org_id;
205 select nvl(gsa_indicator_flag,'N')
206 into l_gsa_flag
207 from hz_parties hp,hz_cust_accounts hca
208 where hp.party_id = hca.party_id
209 and hca.cust_account_id = l_customer_id ;
210 IF l_debug_level > 0 THEN
211 oe_debug_pub.add( 'GSA_FLAG VALUE2:'||L_GSA_FLAG , 5 ) ;
212 END IF;
213 --added the above select and assignment statement to fix bug 1738379 End
214
215 EXCEPTION
216
217 WHEN NO_DATA_FOUND THEN
218 IF l_debug_level > 0 THEN
219 oe_debug_pub.add( 'INSIDE NO-DATA-FOUND EXCEPTION 2' , 5 ) ;
220 END IF;
221 l_gsa_flag := 'N';
222
223 WHEN OTHERS THEN
224 l_gsa_flag := 'N';
225 -- Unexpected error
226 IF l_debug_level > 0 THEN
227 oe_debug_pub.add( 'INSIDE OTHERS EXCEPTION' ) ;
228 END IF;
229 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
230 THEN
231 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
232 'Check_GSA_Violation. GSA Violation Price ');
233 END IF;
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235 END;
236 END IF;
237 IF l_debug_level > 0 THEN
238 oe_debug_pub.add( 'GSA_FLAG VALUE 3:'||L_GSA_FLAG , 5 ) ;
239 END IF;
240
241 RETURN l_gsa_flag;
242
243 END Check_GSA_Indicator;
244
245
246 -- Function which checks if any GSA customer has got more price for
247 -- the item as compared to the NonGSA customer's price .
248
249 FUNCTION Check_NonGSA_Customer
250 (p_line_rec IN OE_Order_Pub.Line_Rec_Type) RETURN VARCHAR2
251 IS
252 l_gsa_count NUMBER := 0;
253 l_gsa_passed_flag VARCHAR2(1) := 'Y';
254
255 --
256 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
257 --
258 BEGIN
259 -- l_gsa_count stores the number of GSA prices less than the
260 -- unit selling price of the NonGSA customer.
261
262 IF l_debug_level > 0 THEN
263 oe_debug_pub.add( 'INSIDE CHECK_NONGSA_CUSTOMER' ) ;
264 END IF;
265 l_gsa_count := 0;
266 l_gsa_count := Get_GSA_Count (p_line_rec);
267 IF l_gsa_count > 0 THEN
268 l_gsa_passed_flag := 'N';
269 IF l_debug_level > 0 THEN
270 oe_debug_pub.add( 'GSA_COUNT >0' ) ;
271 END IF;
272 ELSE
273 l_gsa_passed_flag := 'Y';
274 IF l_debug_level > 0 THEN
275 oe_debug_pub.add( 'GSA_COUNT = 0' ) ;
276 END IF;
277 END IF;
278
279 RETURN l_gsa_passed_flag;
280
281 END Check_NONGSA_CUSTOMER;
282
283
284 FUNCTION Get_GSA_Count
285 (p_line_rec IN OE_Order_Pub.Line_Rec_Type) RETURN NUMBER
286 IS
287
288 --PERFORMANCE no use
289
290 l_gsa_count NUMBER := 0;
291 l_unit_selling_price NUMBER := 0;
292 l_price_list_id NUMBER;
293 l_pricing_quantity NUMBER;
294 l_customer_item_id NUMBER;
295 l_inventory_item_id NUMBER;
296 l_pricing_date DATE;
297
298 --
299 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
300 --
301 BEGIN
302 l_unit_selling_price := p_line_rec.unit_selling_price;
303 l_pricing_quantity := p_line_rec.pricing_quantity;
304 l_inventory_item_id := p_line_rec.inventory_item_id;
305 l_price_list_id := p_line_rec.price_list_id;
306 l_pricing_date := p_line_rec.pricing_date;
307
308
309 -- The select statement checks to see the maximum GSA discount price for the
310 -- inventory item that is being priced from the Sales Order line.
311 --
312 IF l_debug_level > 0 THEN
313 oe_debug_pub.add( 'INSIDE GSA_COUNT' ) ;
314 END IF;
315
316 SELECT count(*)
317 INTO l_gsa_count
318 FROM OE_DISCOUNTS OEDIS,
319 OE_DISCOUNT_CUSTOMERS OEDCU,
320 OE_DISCOUNT_LINES OEDLN,
321 OE_PRICE_BREAK_LINES OEPBL
322 WHERE OEDIS.GSA_INDICATOR = 'Y'
323 AND OEDIS.PRICE_LIST_ID = l_price_list_id
324 AND l_pricing_date
325 BETWEEN NVL(OEDIS.START_DATE_ACTIVE,l_pricing_date)
326 AND NVL(OEDIS.END_DATE_ACTIVE,l_pricing_date)
327 AND OEDIS.DISCOUNT_ID = OEDLN.DISCOUNT_ID
328 AND OEDLN.ENTITY_VALUE = l_inventory_item_id
329 AND l_pricing_date
330 BETWEEN NVL(OEDLN.START_DATE_ACTIVE,l_pricing_date)
331 AND NVL(OEDLN.END_DATE_ACTIVE,l_pricing_date)
332 AND OEDLN.DISCOUNT_LINE_ID = OEPBL.DISCOUNT_LINE_ID (+)
333 AND l_pricing_quantity
334 BETWEEN NVL(OEPBL.PRICE_BREAK_LINES_LOW_RANGE,1)
335 AND NVL(OEPBL.PRICE_BREAK_LINES_HIGH_RANGE,l_pricing_quantity)
336 AND l_pricing_date
337 BETWEEN NVL(OEPBL.START_DATE_ACTIVE,l_pricing_date)
338 AND NVL(OEPBL.END_DATE_ACTIVE,l_pricing_date)
339 AND NVL(OEPBL.PRICE, OEDLN.PRICE) >= l_unit_selling_price
340 AND OEDCU.DISCOUNT_ID (+) = OEDIS.DISCOUNT_ID
341 AND l_pricing_date
342 BETWEEN NVL(OEDCU.START_DATE_ACTIVE,l_pricing_date)
343 AND NVL(OEDCU.END_DATE_ACTIVE,l_pricing_date);
344
345 RETURN l_gsa_count;
346
347 EXCEPTION
348
349 WHEN NO_DATA_FOUND THEN
350 l_gsa_count := 0;
351 IF l_debug_level > 0 THEN
352 oe_debug_pub.add( 'INSIDE NO-DATA-FOUND EXCEPTION' ) ;
353 END IF;
354
355 WHEN OTHERS THEN
356 -- Unexpected error
357 IF l_debug_level > 0 THEN
358 oe_debug_pub.add( 'INSIDE OTHERS EXCEPTION' ) ;
359 END IF;
360 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
361 THEN
362 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
363 'Check_GSA_Violation. GSA Violation Price ');
364 END IF;
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366
367 END Get_GSA_Count;
368
369
370 --Function which checks if any NonGSA customer has got less price for
371 -- the item as compared to the GSA customer's price .
372
373 FUNCTION Check_GSA_CUSTOMER
374 (p_line_rec IN OE_Order_Pub.Line_Rec_Type) RETURN VARCHAR2
375 IS
376 l_nongsa_count NUMBER := 0;
377 l_gsa_passed_flag VARCHAR2(1) := 'Y';
378
379 --
380 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
381 --
382 BEGIN
383 -- l_gsa_count stores the number of NonGSA prices less than the
384 -- unit selling price of the GSA customer.
385
386 l_nongsa_count := 0;
387 l_nongsa_count := Get_NonGSA_Count (p_line_rec);
388 IF l_nongsa_count > 0 THEN
389 l_gsa_passed_flag := 'N';
390 ELSE
391 l_gsa_passed_flag := 'Y';
392 END IF;
393
394 END Check_GSA_Customer;
395
396
397 FUNCTION Get_NonGSA_Count
398 (p_line_rec IN OE_Order_Pub.Line_Rec_Type) RETURN NUMBER
399 IS
400 l_nongsa_count NUMBER := 0;
401 l_header_id NUMBER := 0;
402 l_inventory_item_id NUMBER;
403 l_unit_selling_price NUMBER := 0;
404 l_pricing_quantity NUMBER;
405 l_price_list_id NUMBER;
406 l_pricing_date DATE;
407 --
408 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
409 --
410 BEGIN
411
412 l_unit_selling_price := p_line_rec.unit_selling_price;
413 l_inventory_item_id := p_line_rec.inventory_item_id;
414 l_pricing_quantity := p_line_rec.pricing_quantity;
415 l_price_list_id := p_line_rec.price_list_id;
416 l_pricing_date := p_line_rec.pricing_date;
417
418 SELECT count(*)
419 INTO l_nongsa_count
420 FROM OE_DISCOUNTS OEDIS,
421 OE_DISCOUNT_CUSTOMERS OEDCU,
422 OE_DISCOUNT_LINES OEDLN,
423 OE_PRICE_BREAK_LINES OEPBL
424 WHERE OEDIS.GSA_INDICATOR = 'N'
425 AND OEDIS.PRICE_LIST_ID = l_price_list_id
426 AND l_pricing_date
427 BETWEEN NVL(OEDIS.START_DATE_ACTIVE,l_pricing_date)
428 AND NVL(OEDIS.END_DATE_ACTIVE,l_pricing_date)
429 AND OEDIS.DISCOUNT_ID = OEDLN.DISCOUNT_ID
430 AND OEDLN.ENTITY_VALUE = l_inventory_item_id
431 AND l_pricing_date
432 BETWEEN NVL(OEDLN.START_DATE_ACTIVE,l_pricing_date)
433 AND NVL(OEDLN.END_DATE_ACTIVE,l_pricing_date)
434 AND OEDLN.DISCOUNT_LINE_ID = OEPBL.DISCOUNT_LINE_ID (+)
435 AND l_pricing_quantity
436 BETWEEN NVL(OEPBL.PRICE_BREAK_LINES_LOW_RANGE,1)
437 AND NVL(OEPBL.PRICE_BREAK_LINES_HIGH_RANGE,l_pricing_quantity)
438 AND l_pricing_date
439 BETWEEN NVL(OEPBL.START_DATE_ACTIVE,l_pricing_date)
440 AND NVL(OEPBL.END_DATE_ACTIVE,l_pricing_date)
441 AND NVL(OEPBL.PRICE, OEDLN.PRICE) <= l_unit_selling_price
442 AND OEDIS.DISCOUNT_ID = OEDCU.DISCOUNT_ID (+)
443 AND l_pricing_date
444 BETWEEN NVL(OEDCU.START_DATE_ACTIVE,l_pricing_date)
445 AND NVL(OEDCU.END_DATE_ACTIVE,l_pricing_date);
446
447 RETURN l_nongsa_count;
448
449 EXCEPTION
450
451 WHEN NO_DATA_FOUND THEN
452 l_nongsa_count := 0;
453
454 WHEN OTHERS THEN
455 -- Unexpected error
456 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
457 THEN
458 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
459 'Check_GSA_Violation. GSA Violation Price ');
460 END IF;
461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
462
463 END Get_NonGSA_Count;
464
465
466 FUNCTION Get_Hold_id(hold NUMBER) RETURN NUMBER
467 IS
468 x_hold_id NUMBER :=0;
469 --
470 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
471 --
472 BEGIN
473
474 SELECT OE_HOLD_definitions.HOLD_ID
475 INTO x_hold_id
476 FROM OE_HOLD_definitions WHERE TYPE_CODE = 'GSA';
477
478 RETURN x_hold_id;
479
480 EXCEPTION
481 WHEN NO_DATA_FOUND THEN RETURN 0;
482 WHEN OTHERS THEN RETURN 0;
483
484 END Get_Hold_id;
485
486
487 FUNCTION Get_Source_id(header_id NUMBER) RETURN NUMBER
488 IS
489 x_source_id NUMBER :=0;
490 l_header_id NUMBER;
491 --
492 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
493 --
494 BEGIN
495 l_header_id := header_id;
496
497 SELECT MAX(S.HOLD_SOURCE_ID)
498 INTO x_source_id
499 FROM OE_HOLD_SOURCES S
500 WHERE S.HOLD_ENTITY_ID = l_header_id
501 AND S.HOLD_ENTITY_CODE = 'O'
502 AND NVL(RELEASED_FLAG,'N') ='N';
503
504 RETURN x_source_id;
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 IF l_debug_level > 0 THEN
509 oe_debug_pub.add( 'FAILED IN GET_SOURCE_ID' ) ;
510 END IF;
511 RETURN 0;
512 END Get_Source_id;
513
514
515 FUNCTION Release_Hold
516 (p_line_rec IN OE_Order_Pub.Line_Rec_Type,
517 x_resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) RETURN VARCHAR2
518 IS
519 l_header_id NUMBER := 0;
520 l_hold_release_id NUMBER := 0;
521 l_hold_source_id NUMBER := 0;
522 l_request_id NUMBER :=0;
523 l_line_id NUMBER:=0;
524 l_program_application_id NUMBER :=0;
525 l_program_id NUMBER :=0;
526 --
527 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
528 --
529 BEGIN
530 l_header_id := p_line_rec.header_id;
531 l_line_id := p_line_rec.line_id;
532 l_request_id := p_line_rec.request_id;
533 l_hold_source_id := Get_Source_id(l_line_id);
534 l_program_application_id := p_line_rec.program_application_id;
535 l_program_id := p_line_rec.program_id;
536
537
538 SELECT OE_HOLD_RELEASES_S.NEXTVAL
539 INTO l_hold_release_id
540 FROM DUAL;
541
542 BEGIN
543 INSERT INTO OE_HOLD_RELEASES
544 (HOLD_RELEASE_ID,
545 CREATION_DATE,
546 CREATED_BY,
547 LAST_UPDATE_DATE,
548 LAST_UPDATED_BY,
549 LAST_UPDATE_LOGIN,
550 REQUEST_ID,
551 PROGRAM_APPLICATION_ID,
552 PROGRAM_ID,
553 PROGRAM_UPDATE_DATE,
554 HOLD_SOURCE_ID,
555 -- HOLD_ENTITY_CODE,
556 -- HOLD_ENTITY_ID,
557 RELEASE_REASON_CODE )
558 SELECT l_hold_release_id,
559 SYSDATE,
560 FND_GLOBAL.user_id,
561 SYSDATE,
562 FND_GLOBAL.user_id,
563 FND_GLOBAL.login_id,
564 l_request_id,
565 l_program_application_id,
566 l_program_id,
567 DECODE(l_request_id, NULL, NULL, SYSDATE ),
568 l_hold_source_id,
569 --'O',
570 --l_header_id,
571 'PASS_GSA'
572 FROM DUAL
573 WHERE l_hold_source_id <> 0;
574
575 If l_hold_source_id <> 0 then
576 FND_MESSAGE.SET_NAME('OE', 'GSA Hold Removed');
577 OE_MSG_PUB.Add;
578 End If;
579
580 EXCEPTION
581 WHEN OTHERS THEN null;
582 -- put real handling here
583 END;
584
585 BEGIN
586 UPDATE OE_ORDER_HOLDS OEHLD
587 SET HOLD_RELEASE_ID = l_hold_release_id,
588 RELEASED_FLAG='Y',
589 LAST_UPDATE_DATE = SYSDATE,
590 LAST_UPDATED_BY = FND_GLOBAL.user_id,
591 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
592 REQUEST_ID = l_request_id,
593 PROGRAM_APPLICATION_ID = l_program_application_id,
594 PROGRAM_ID = l_program_id,
595 PROGRAM_UPDATE_DATE = DECODE( l_request_id, NULL, NULL, SYSDATE )
596 WHERE HOLD_SOURCE_ID = l_hold_source_id
597 AND HEADER_ID = l_header_id
598 AND LINE_ID = l_line_id;
599
600 RETURN 'COMPLETE:AP_PASS';
601
602 EXCEPTION
603 WHEN OTHERS THEN null;
604 --need more thinking here
605 END;
606
607 EXCEPTION
608 WHEN OTHERS THEN RETURN 'COMPLETE:AP_FAIL';
609
610 END Release_Hold;
611
612 END OE_GSA_UTIL;
[Home] [Help]