[Home] [Help]
PACKAGE BODY: APPS.OZF_SD_UTIL_PVT
Source
1 package body OZF_SD_UTIL_PVT as
2 /* $Header: ozfvsdub.pls 120.17 2008/05/02 10:11:55 amlal noship $ */
3
4 -- Start of Comments
5 -- Package name : OZF_SD_UTIL_PVT
6 -- Purpose :
7 -- History :
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SD_UTIL_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvsdub.pls';
13
14
15 --l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
16
17 function SD_CONVERT_CURRENCY(p_batch_line_id number,p_amount number ) return number
18 is
19
20 l_from_currency VARCHAR2(15):=NULL;
21 l_to_currency VARCHAR2(15):=NULL;
22 l_conv_date DATE;
23 l_from_amount NUMBER;
24 x_to_amount NUMBER;
25 l_util_id NUMBER;
26 x_return_status VARCHAR2(200);
27 begin
28
29 select UTILIZATION_ID,CLAIM_AMOUNT_CURRENCY_CODE into l_util_id,l_from_currency from OZF_SD_BATCH_LINES_ALL where batch_line_id=p_batch_line_id;
30
31 -- select currency_code into l_from_currency from OZF_FUNDS_UTILIZED_ALL_B where utilization_id=l_util_id;
32
33 select currency_code into l_to_currency from ozf_sd_batch_headers_all
34 where batch_id=(select batch_id from ozf_sd_batch_lines_all
35 where batch_line_id = p_batch_line_id);
36
37 select exchange_rate_date into l_conv_date from OZF_FUNDS_UTILIZED_ALL_B where utilization_id=l_util_id;
38
39 OZF_UTILITY_PVT.Convert_Currency (
40 x_return_status ,
41 l_from_currency ,
42 l_to_currency ,
43 l_conv_date ,
44 p_amount ,
45 x_to_amount
46 );
47
48 return x_to_amount;
49
50
51 end;
52
53 procedure SD_AMOUNT_POSTBACK(p_batch_line_id number, x_return_status OUT NOCOPY VARCHAR2,
54 x_meaning OUT NOCOPY VARCHAR2)
55 is
56 l_claim_amount number;
57 l_acctd_amount_remaining number;
58 l_univ_curr_amount_remaining number;
59 l_util_id number;
60 l_amount_remaining number;
61
62 begin
63
64 select UTILIZATION_ID ,claim_amount,acctd_amount_remaining,univ_curr_amount_remaining,amount_remaining
65 into l_util_id, l_claim_amount ,l_acctd_amount_remaining,l_univ_curr_amount_remaining,l_amount_remaining
66 from OZF_SD_BATCH_LINES_ALL
67 where batch_line_id=p_batch_line_id;
68
69
70
71 UPDATE OZF_FUNDS_UTILIZED_ALL_B
72 SET amount_remaining=amount_remaining+l_amount_remaining,
73 PLAN_CURR_AMOUNT_REMAINING=PLAN_CURR_AMOUNT_REMAINING+l_claim_amount,
74 acctd_amount_remaining=acctd_amount_remaining+l_acctd_amount_remaining,
75 univ_curr_amount_remaining=univ_curr_amount_remaining+l_amount_remaining
76
77 WHERE UTILIZATION_ID=l_util_id;
78
79 DELETE FROM OZF_SD_BATCH_LINES_ALL WHERE batch_line_id=p_batch_line_id;
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81 x_meaning := NULL;
82
83
84 EXCEPTION
85
86 --confirm with joshua abt action to be taken
87 --error code will be returned to java layer where commit or rollback can happen
88 WHEN OTHERS then
89 x_return_status := FND_API.G_RET_STS_ERROR;
90 x_meaning := NULL;
91 RETURN;
92
93 end;
94
95 PROCEDURE CONVERT_TO_RN_DATE(
96 p_server_date IN DATE,
97 x_rn_date OUT NOCOPY VARCHAR2)
98 IS
99 l_utc_date DATE;
100 l_milliseconds VARCHAR2(5);
101 l_server_timezone VARCHAR2(50);
102 l_error_code NUMBER;
103 l_error_msg VARCHAR2(255);
104 l_msg_data VARCHAR2(255);
105 BEGIN
106
107 IF(p_server_date is null) THEN
108 x_rn_date := null;
109 RETURN;
110 END IF;
111 x_rn_date := TO_CHAR(p_server_date,'YYYYMMDD')||'Z';
112
113
114 -- Exception Handling
115 EXCEPTION
116 WHEN OTHERS THEN
117 l_error_code := SQLCODE;
118 l_error_msg := SQLERRM;
119 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
120
121 END CONVERT_TO_RN_DATE;
122
123 PROCEDURE CONVERT_TO_DB_DATE(
124 p_rn_date IN VARCHAR2,
125 x_db_date OUT NOCOPY DATE)
126 IS
127 l_server_date DATE;
128 l_utc_datetime DATE;
129 l_count_t_appearanace NUMBER;
130 l_error_code NUMBER;
131 l_rn_frmt_date VARCHAR2(30);
132 l_rn_timezone VARCHAR2(30);
133 l_db_timezone VARCHAR2(30);
134 l_error_msg VARCHAR2(255);
135 l_msg_data VARCHAR2(255);
136 BEGIN
137
138
139 IF(p_rn_date is null) THEN
140 x_db_date := null;
141
142 RETURN;
143 END IF;
144 l_count_t_appearanace := instr(p_rn_date,'T');
145 IF (l_count_t_appearanace > 0) THEN
146 --Datetime Format: YYYYMMDDThhmmss.SSSZ
147 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
148
149 -- get the timezone of the db server
150 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
151
152
153 l_rn_frmt_date := substr(p_rn_date,1,8)||substr(p_rn_date,10,6);
154
155 l_utc_datetime := TO_DATE(l_rn_frmt_date,'YYYYMMDDHH24MISS');
156
157 -- this function converts the datetime from the user entered/db timezone to UTC
158 x_db_date := FND_TIMEZONES_PVT.adjust_datetime(l_utc_datetime,l_rn_timezone,l_db_timezone);
159
160 ELSE
161 --Date Format : YYYYMMDDZ
162
163 l_rn_frmt_date := substr(p_rn_date,1,8);
164
165 x_db_date := TO_DATE(l_rn_frmt_date,'YYYYMMDD');
166
167 END IF;
168
169 -- Exception Handling
170 EXCEPTION
171 WHEN OTHERS THEN
172 l_error_code := SQLCODE;
173 l_error_msg := SQLERRM;
174 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
175
176 END CONVERT_TO_DB_DATE;
177
178
179
180 ---------------------------------------------------------------------
181 -- PROCEDURE
182 -- UPDATE_SD_REQ_PRICES
183 --
184 -- PURPOSE
185 -- Updates the Ship and Debit price interface table with correct request line iD
186 -- PARAMETERS
187 -- a) p_request_number : The SD Request Number for transaction
188 -- b) p_request_line_id : Correct request line Id
189 --
190 -- NOTES
191 --
192 ---------------------------------------------------------------------------
193
194 PROCEDURE UPDATE_SD_REQ_PRICES(p_request_number IN VARCHAR2,p_request_line_id IN NUMBER)
195 IS
196 l_req_header_id NUMBER ;
197 l_req_line_id NUMBER ;
198
199 BEGIN
200
201 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET REQUEST_LINE_ID=p_request_line_id
202 WHERE REQUEST_NUMBER=p_request_number
203 AND REQUEST_LINE_ID IS NULL;
204
205 COMMIT;
206
207 END UPDATE_SD_REQ_PRICES;
208
209 ---------------------------------------------------------------------
210 -- PROCEDURE
211 -- PROCESS_SD_RESPONSE
212 --
213 -- PURPOSE
214 -- Updates the Ship and Debit header and base tables for the inbound data
215 -- only when the business validation(s) are passed
216 -- The business validation involved are
217 -- a)
218 -- PARAMETERS
219 -- a) p_request_number : The SD Request Number
220 -- b) x_return_status : Return status for the processing
221 -- c) x_msg_data : Error message if the validation errored out
222 -- NOTES
223 --
224 ----------------------------------------------------------------------
225
226 PROCEDURE PROCESS_SD_RESPONSE(p_request_number IN VARCHAR2
227 , x_return_status OUT nocopy VARCHAR2
228 , x_msg_data OUT nocopy VARCHAR2 )
229 IS
230
231 l_return_status VARCHAR2(30);
232 l_msg_data VARCHAR2(2000):='Data submitted is not valid :';
233 l_req_id NUMBER :=0 ;
234 l_req_number_count NUMBER ;
235 l_req_status AMS_USER_STATUSES_B.SYSTEM_STATUS_CODE%TYPE;
236 l_request_status OZF_SD_REQUEST_HEADERS_ALL_B.USER_STATUS_ID%TYPE;
237 l_curr_prod_context OZF_SD_REQUEST_LINES_ALL.PRODUCT_CONTEXT%TYPE ;
238 l_curr_code OZF_SD_REQUEST_HEADERS_ALL_B.REQUEST_CURRENCY_CODE%TYPE;
239 l_approved_lines NUMBER :=0;
240 l_error_count NUMBER := 0 ;
241 l_currency_count NUMBER :=0 ;
242 l_line_status VARCHAR2(1) :='S' ;
243 l_authorization_number OZF_SD_RES_HEADER_INTF.AUTH_NUMBER%TYPE ;
244 l_error_message FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE ;
245
246 CURSOR FETCH_PROD_INTF_REC IS
247 SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
248 PRODINTF.PROD_TYPE INF_PROD_TYPE,
249 PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
250 PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
251 PROD.CONCATENATED_SEGMENTS LINE_PROD_CODE,
252 PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
253 PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
254 PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
255 PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
256 PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
257 NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
258 NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
259 FROM OZF_SD_RES_PROD_INTF PRODINTF,
260 OZF_SD_REQUEST_LINES_ALL PRODLINES,
261 mtl_system_items_b_kfv PROD
262 where NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
263 PRODINTF.REQUEST_NUMBER=p_request_number
264 AND PRODLINES.REQUEST_HEADER_ID=request_header_id
265 AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
266 AND PRODLINES.PRODUCT_CONTEXT = 'PRODUCT'
267 AND PRODLINES.ORG_ID=PROD.ORGANIZATION_ID
268 AND PRODLINES.INVENTORY_ITEM_ID=PROD.INVENTORY_ITEM_ID
269
270 UNION
271 SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
272 PRODINTF.PROD_TYPE INF_PROD_TYPE,
273 PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
274 PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
275 NVL(D.CATEGORY_DESC, 'NA') PROD_CODE,
276 PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
277 PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
278 PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
279 PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
280 PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
281 NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
282 NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
283 FROM OZF_SD_RES_PROD_INTF PRODINTF,
284 OZF_SD_REQUEST_LINES_ALL PRODLINES,
285 ENI_PROD_DEN_HRCHY_PARENTS_V D
286 WHERE NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
287 PRODINTF.REQUEST_NUMBER=p_request_number
288 AND PRODLINES.REQUEST_HEADER_ID=request_header_id
289 AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
290 AND PRODLINES.PRODUCT_CONTEXT ='PRODUCT_CATEGORY'
291 AND PRODLINES.PROD_CATG_ID=D.CATEGORY_ID
292 AND PRODLINES.PRODUCT_CAT_SET_ID = D.CATEGORY_SET_ID ;
293
294 BEGIN
295
296 x_msg_data := '';
297 x_return_status := FND_API.G_RET_STS_SUCCESS ;
298 l_error_message := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR') ;
299
300 -- Validate the SDR Request Number
301 Select count(*) into l_req_number_count from OZF_SD_REQUEST_HEADERS_ALL_B
302 where REQUEST_NUMBER = p_request_number ;
303 IF l_req_number_count = 0 THEN
304
305 l_msg_data := l_msg_data ||','|| 'The request number '||p_request_number ||' is not valid.' ;
306 x_msg_data := 'No Ship and Debit Request exists for : '||p_request_number;
307 x_return_status := FND_API.G_RET_STS_ERROR ;
308
309 -- DO THE MASS UPDATE FOR THE INTERFACE TABLES
310 UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y',ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
311
312 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
313
314 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
315
316 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
317
318 COMMIT ;
319 return ;
320
321 END IF ;
322
323
324 Select REQ.request_header_id,STATUS.SYSTEM_STATUS_CODE,REQ.request_currency_code into l_req_id,l_req_status,l_curr_code
325 from OZF_SD_REQUEST_HEADERS_ALL_B REQ,AMS_USER_STATUSES_VL STATUS
326 where REQ.REQUEST_NUMBER = p_request_number
327 AND REQ.USER_STATUS_ID=STATUS.USER_STATUS_ID ;
328
329
330 -- If the request status is "PENDING_SUPPLIER_APPROVAL" then process the product lines in loop
331 IF l_req_status='PENDING_SUPPLIER_APPROVAL' THEN
332
333 -- Update the non responded lines as 'rejected' and rejection code as ' No response from vendor'
334 UPDATE OZF_SD_REQUEST_LINES_ALL SET VENDOR_APPROVED_FLAG='N',REJECTION_CODE='OZF_SD_NO_RESPONSE'
335 WHERE REQUEST_HEADER_ID=request_header_id
339 SELECT REQUEST_LINE_ID FROM OZF_SD_RES_PROD_INTF
336 AND REQUEST_LINE_ID IN ( SELECT REQUEST_LINE_ID FROM OZF_SD_REQUEST_LINES_ALL
337 WHERE REQUEST_HEADER_ID=request_header_id
338 MINUS
340 WHERE REQUEST_NUMBER=p_request_number
341 AND NVL(PROCESSED_FLAG,'N') <>'Y') ;
342
343
344 FOR PROD_REC IN FETCH_PROD_INTF_REC
345 LOOP
346 l_msg_data := '';
347 -- Set the line status as valid
348 l_line_status := 'S' ;
349
350 -- Validate the product type
351 IF PROD_REC.INF_PROD_TYPE IS NOT NULL AND
352 PROD_REC.LINE_PROD_CONTEXT <> UPPER(PROD_REC.INF_PROD_TYPE) THEN
353
354
355 l_msg_data := l_msg_data ||','|| 'Product context mismatch' ;
356
357 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
358 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
359 AND NVL(PROCESSED_FLAG,'N') <>'Y';
360
361 l_error_count := l_error_count +1 ;
362 x_msg_data := l_error_message;
363 x_return_status := FND_API.G_RET_STS_ERROR ;
364 l_line_status := 'E' ;
365 END IF;
366
367 -- Validate the product code
368 IF PROD_REC.LINE_PROD_CONTEXT<>'PRODUCT_CATEGORY' AND
369 PROD_REC.LINE_PROD_CODE<>PROD_REC.INF_PROD_CODE THEN
370
371
372 l_msg_data := l_msg_data ||','|| 'Product code mismatch' ;
373
374 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
375 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
376 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
377
378 l_error_count := l_error_count +1 ;
379 x_msg_data := l_error_message;
380 x_return_status := FND_API.G_RET_STS_ERROR ;
381 l_line_status := 'E' ;
382 END IF;
383
384 -- Validate the discount type
385 IF PROD_REC.INTF_DISCOUNT_TYE IS NOT NULL AND
386 PROD_REC.INTF_DISCOUNT_TYE <> PROD_REC.LINE_DISCOUNT_TYPE THEN
387
388
389 l_msg_data := l_msg_data ||','|| 'Discount type mismatch' ;
390
391 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Discount type mismatch'
392 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
393 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
394
395 l_error_count := l_error_count +1 ;
396 x_msg_data := l_error_message;
397 x_return_status := FND_API.G_RET_STS_ERROR ;
398 l_line_status := 'E' ;
399 END IF;
400
401 -- Validate the currency if not null
402
403 l_curr_code := PROD_REC.INTF_CURR_CODE ;
404 IF PROD_REC.INTF_CURR_CODE IS NULL OR trim(PROD_REC.INTF_CURR_CODE)='' THEN
405 l_curr_code := PROD_REC.LINE_DISCOUNT_CUR ;
406 END IF ;
407
408
409 IF PROD_REC.LINE_DISCOUNT_TYPE <>'%' THEN
410
411
412 SELECT count(*) INTO l_currency_count
413 FROM FND_CURRENCIES
414 WHERE currency_code = l_curr_code;
415 IF l_currency_count=0 THEN
416
417
418 l_msg_data := l_msg_data ||','|| 'Currency code mismatch' ;
419
420 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Currency code mismatch'
421 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
422 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
423
424 l_error_count := l_error_count +1 ;
425 x_msg_data := l_error_message;
426 x_return_status := FND_API.G_RET_STS_ERROR ;
427 l_line_status := 'E' ;
428 END IF;
429 END IF ;
430
431 -- Check the validation status of line : If an valid line then update the base table data
432 IF l_line_status <> 'E' THEN
433 -- Update the status for product line as 'Rejected'
434 IF PROD_REC.INF_REJ_CODE IS NOT NULL THEN
435 UPDATE OZF_SD_REQUEST_LINES_ALL SET REJECTION_CODE=PROD_REC.INF_REJ_CODE,VENDOR_APPROVED_FLAG='N'
436 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
437
438 END IF;
439 -- Update the approved amount,approved currency and type in the base line table
440 UPDATE OZF_SD_REQUEST_LINES_ALL SET APPROVED_DISCOUNT_TYPE=PROD_REC.LINE_DISCOUNT_TYPE,
441 APPROVED_DISCOUNT_VALUE=PROD_REC.INTF_APPROVED_DISCOUNT_VALUE,
442 APPROVED_MAX_QTY=PROD_REC.INTF_APPROVED_QUANTITY,
443 APPROVED_DISCOUNT_CURRENCY=l_curr_code
444 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
445
446
447 END IF;
448 END LOOP;
449
450
451 -- UPDATE THE REQUEST STATUS IN HEADER TABLE : OZF_SD_REQUEST_HEADERS_ALL_B
452 SELECT COUNT(*) INTO l_approved_lines FROM OZF_SD_REQUEST_LINES_ALL
453 WHERE REQUEST_HEADER_ID=l_req_id
454 AND VENDOR_APPROVED_FLAG = 'Y' ;
455
456
457 IF l_approved_lines > 0 THEN
458 SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
459 where system_status_TYPE='OZF_SD_REQUEST_STATUS'
460 and SYSTEM_STATUS_CODE='SUPPLIER_APPROVED'
461 and default_flag='Y'
462 and enabled_flag='Y' ;
463 ELSE
464 SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
465 where system_status_TYPE='OZF_SD_REQUEST_STATUS'
466 and SYSTEM_STATUS_CODE='SUPPLIER_REJECTED'
467 and default_flag='Y'
468 and enabled_flag='Y' ;
469 END IF ;
470
471 SELECT AUTH_NUMBER INTO l_authorization_number FROM OZF_SD_RES_HEADER_INTF WHERE request_number=p_request_number
475 WHERE REQUEST_HEADER_ID=l_req_id ;
472 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
473
474 UPDATE OZF_SD_REQUEST_HEADERS_ALL_B SET user_status_id= l_request_status,AUTHORIZATION_NUMBER=l_authorization_number
476
477
478 ELSE
479 l_msg_data := l_msg_data ||','|| 'The ststus is not pending suppler approval' ;
480 x_msg_data := 'Currently the request is not pending for supplier approval';
481 x_return_status := FND_API.G_RET_STS_ERROR;
482
483 UPDATE OZF_SD_RES_HEADER_INTF SET ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number
484 and NVL(PROCESSED_FLAG,'N') <>'Y';
485
486
487 END IF ;
488
489
490 -- DO THE MASS UPDATE FOR THE INTERFACE TABLES
491
492 UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
493 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
494 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
495 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
496 COMMIT ;
497 EXCEPTION
498
499 WHEN OTHERS then
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
501 -- DO THE MASS UPDATE FOR THE INTERFACE TABLES
502 UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
503 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
504 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
505 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
506
507 COMMIT ;
508 RETURN;
509
510 END PROCESS_SD_RESPONSE;
511
512
513 PROCEDURE CONVERT_TO_RN_DATETIME(
514 p_server_date IN DATE,
515 x_rn_datetime OUT NOCOPY VARCHAR2)
516 IS
517 l_error_code NUMBER;
518 l_utc_date DATE;
519 l_milliseconds VARCHAR2(5);
520 l_server_timezone VARCHAR2(30);
521 l_error_msg VARCHAR2(255);
522 l_msg_data VARCHAR2(255);
523 BEGIN
524
525 IF(p_server_date is null) THEN
526 x_rn_datetime := null;
527
528 RETURN;
529 END IF;
530
531 CONVERT_TO_RN_TIMEZONE(
532 p_input_date => p_server_date,
533 x_utc_date => l_utc_date );
534
535 l_milliseconds := '000'; --We wont get milliseconds
536
537 x_rn_datetime := TO_CHAR(l_utc_date,'YYYYMMDD')||'T'||TO_CHAR(l_utc_date,'hh24miss')||'.'||l_milliseconds||'Z';
538
539
540 -- Exception Handling
541 EXCEPTION
542 WHEN OTHERS THEN
543 l_error_code := SQLCODE;
544 l_error_msg := SQLERRM;
545 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
546
547 END CONVERT_TO_RN_DATETIME;
548
549
550
551 PROCEDURE CONVERT_TO_RN_TIMEZONE(
552 p_input_date IN DATE,
553 x_utc_date OUT NOCOPY DATE )
554 IS
555 l_error_code NUMBER;
556 l_db_timezone VARCHAR2(30);
557 l_rn_timezone VARCHAR2(30);
558 l_error_msg VARCHAR2(255);
559 l_msg_data VARCHAR2(255);
560 BEGIN
561
562 -- get the timezone of the db server
563 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
564
565 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
566
567
568 -- this function converts the datetime from the user entered/db timezone to UTC
569 x_utc_date := FND_TIMEZONES_PVT.adjust_datetime(p_input_date,l_db_timezone,l_rn_timezone);
570
571
572 -- Exception Handling
573 EXCEPTION
574 WHEN OTHERS THEN
575 l_error_code := SQLCODE;
576 l_error_msg := SQLERRM;
577 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
578
579 END CONVERT_TO_RN_TIMEZONE;
580
581 ---------------------------------------------------------------------
582 -- PROCEDURE
583 -- UPDATE_SD_REQ_STALE_DATA
584 --
585 -- PURPOSE
586 -- Updates the Ship and Debit interface tables for any stale data
587 --
588 -- PARAMETERS
589 -- a) p_request_number : The SD Request Number
590 -- NOTES
591 --
592 ----------------------------------------------------------------------
593
594 PROCEDURE UPDATE_SD_REQ_STALE_DATA(p_request_number IN VARCHAR2)
595 IS
596 l_req_number_count NUMBER :=0;
597 BEGIN
598
599 UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
600 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
601 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
602 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
603 COMMIT ;
604
605 END UPDATE_SD_REQ_STALE_DATA;
606
607 ---------------------------------------------------------------------
608 -- PROCEDURE
609 -- SD_RAISE_EVENT
610 --
611 -- PURPOSE
612 -- This procedure raises a Business Event based on batch action.
613 --
614 -- PARAMETERS
615 -- a) Batch ID
616 -- b) Batch Action - can be EXPORT,CREATE, RESPONSE and CLAIM.
617 -- NOTES
618 --
619 ----------------------------------------------------------------------
620
621
622 PROCEDURE SD_RAISE_EVENT(P_BATCH_ID IN NUMBER,
623 P_BATCH_ACTION IN VARCHAR2,
624 x_return_status OUT NOCOPY VARCHAR2) IS
625
626 p_event_name VARCHAR2(100) := 'oracle.apps.ozf.sd.batch.lifecycle';
627 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
628 evtkey VARCHAR2(100);
629
630
631 BEGIN
632 evtkey := 'SDB' || P_BATCH_ID || dbms_utility.get_time();
633 wf_event.addparametertolist(p_name => 'BATCH_ID', p_value => P_BATCH_ID, p_parameterlist => l_parameter_list);
634 wf_event.addparametertolist(p_name => 'ACTION_NAME', p_value => P_BATCH_ACTION, p_parameterlist => l_parameter_list);
635 wf_event.RAISE(p_event_name, evtkey, NULL, l_parameter_list, sysdate);
636
637 EXCEPTION
638
639 WHEN OTHERS then
640 x_return_status := FND_API.G_RET_STS_ERROR;
641 RETURN;
642
643 END SD_RAISE_EVENT;
644
645 end OZF_SD_UTIL_PVT;