[Home] [Help]
PACKAGE BODY: APPS.OZF_SD_UTIL_PVT
Source
1 package body OZF_SD_UTIL_PVT as
2 /* $Header: ozfvsdub.pls 120.49.12020000.2 2012/07/24 16:53:40 sariff ship $ */
3
4 -- Start of Comments
5 -- Package name : OZF_SD_UTIL_PVT
6 -- Purpose :
7 -- History : 28-OCT-2009 - ANNSRINI - Fix for bug 9057734 - l_conv_adj_amount added in procedure create_adjustment
8 -- : 07-DEC-2009 - ANNSRINI - changes w.r.t multicurrency
9 -- NOTE :
10 -- End of Comments
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SD_UTIL_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvsdub.pls';
14
15
16 --l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
17
18 function SD_CONVERT_CURRENCY(p_batch_line_id number,p_amount number ) return number
19 is
20
21 l_from_currency VARCHAR2(15):=NULL;
22 l_to_currency VARCHAR2(15):=NULL;
23 l_conv_date DATE;
24 l_from_amount NUMBER;
25 x_to_amount NUMBER;
26 l_util_id NUMBER;
27 x_return_status VARCHAR2(200);
28 begin
29
30 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;
31
32 -- select currency_code into l_from_currency from OZF_FUNDS_UTILIZED_ALL_B where utilization_id=l_util_id;
33
34 select currency_code into l_to_currency from ozf_sd_batch_headers_all
35 where batch_id=(select batch_id from ozf_sd_batch_lines_all
36 where batch_line_id = p_batch_line_id);
37
38 -- select exchange_rate_date into l_conv_date from OZF_FUNDS_UTILIZED_ALL_B where utilization_id=l_util_id;
39
40 OZF_UTILITY_PVT.Convert_Currency (
41 x_return_status ,
42 l_from_currency ,
43 l_to_currency ,
44 sysdate ,
45 p_amount ,
46 x_to_amount
47 );
48
49 return x_to_amount;
50
51
52 end;
53
54 -----------------------------------------------------------------------------------------------------------
55 -- FUNCTION
56 -- GET_CONVERTED_CURRENCY
57 -- PURPOSE
58 -- This API does currency conversion in 1 or 2 steps
59 -- based on the difference in the plan,batch and functional currencies
60 --
61 -- PARAMETERS
62 -- p_plan_currency
63 -- p_batch_currency
64 -- p_functional_curr
65 -- p_exchange_rate_type - Exchange Rate Type
66 -- p_date - Exchange Rate Date of Accrual
67 -- p_amount - Amount to be converted
68 --
69 -- NOTES
70 -- if (Plan Currency != Functional Currency AND Functional Currency == Batch Currency)
71 -- Convert Claim_Amount (in plan currency) to Functional currency on Exchange Rate Date of Accrual
72 -- else if (Plan Currency!= Functional Currency AND Functional Currency!= Batch Currency)
73 -- 1. Convert Claim Amount(in plan currency) to functional currency on Exchange Rate Date of Accrual
74 -- 2. Convert computed Function Amount to batch currency on sysdate or creation Date
75 -- else if (Plan Currency == Functional Currency AND Functional Currency!=batch Currency)
76 -- Convert Claim Amount(in plan currency) to batch currency on sysdate
77 --
78 --------------------------------------------------------------------------------------------------------------
79
80 function GET_CONVERTED_CURRENCY(p_plan_currency varchar2,p_batch_currency varchar2,p_functional_curr varchar2,p_exchange_rate_type varchar2,p_conv_rate number,p_date DATE,p_amount number) return number
81 is
82
83 x_to_amount_func NUMBER;
84 x_to_amount_batch NUMBER;
85 x_return_status VARCHAR2(200);
86 x_rate NUMBER;
87
88 begin
89
90 IF((p_plan_currency <> p_functional_curr) AND (p_functional_curr=p_batch_currency)) THEN
91 OZF_UTILITY_PVT.Convert_Currency (
92 p_from_currency => p_plan_currency,
93 p_to_currency => p_batch_currency,
94 p_conv_type => p_exchange_rate_type,
95 p_conv_rate => FND_API.G_MISS_NUM,
96 p_conv_date => p_date,
97 p_from_amount => p_amount,
98 x_return_status => x_return_status,
99 x_to_amount => x_to_amount_batch,
100 x_rate => x_rate
101 );
102 ELSIF((p_plan_currency <> p_functional_curr) AND (p_functional_curr <> p_batch_currency)) THEN
103
104 OZF_UTILITY_PVT.Convert_Currency (
105 p_from_currency => p_plan_currency,
106 p_to_currency => p_functional_curr,
107 p_conv_type => p_exchange_rate_type,
108 p_conv_rate => FND_API.G_MISS_NUM,
109 p_conv_date => p_date,
110 p_from_amount => p_amount,
111 x_return_status => x_return_status,
112 x_to_amount => x_to_amount_func,
113 x_rate => x_rate
114 );
115
116 OZF_UTILITY_PVT.Convert_Currency (
117 p_from_currency => p_functional_curr,
118 p_to_currency => p_batch_currency,
119 p_conv_type => p_exchange_rate_type,
120 p_conv_rate => FND_API.G_MISS_NUM,
121 p_conv_date => SYSDATE,
122 p_from_amount => x_to_amount_func,
123 x_return_status => x_return_status,
124 x_to_amount => x_to_amount_batch,
125 x_rate => x_rate
126 );
127
128 ELSIF((p_plan_currency = p_functional_curr) AND (p_functional_curr <> p_batch_currency)) THEN
129
130 OZF_UTILITY_PVT.Convert_Currency (
131 p_from_currency => p_plan_currency,
132 p_to_currency => p_batch_currency,
133 p_conv_type => p_exchange_rate_type,
134 p_conv_rate => FND_API.G_MISS_NUM,
135 p_conv_date => SYSDATE,
136 p_from_amount => p_amount,
137 x_return_status => x_return_status,
138 x_to_amount => x_to_amount_batch,
139 x_rate => x_rate
140 );
141
142 END IF;
143 return x_to_amount_batch;
144 end;
145
146
147 procedure SD_AMOUNT_POSTBACK(p_batch_line_id number, x_return_status OUT NOCOPY VARCHAR2,
148 x_meaning OUT NOCOPY VARCHAR2)
149 is
150 l_claim_amount number;
151 l_acctd_amount_remaining number;
152 l_univ_curr_amount_remaining number;
153 l_fund_req_amount_remaining number;
154 l_util_id number;
155 l_amount_remaining number;
156
157 begin
158
159 select UTILIZATION_ID ,claim_amount,acctd_amount_remaining,univ_curr_amount_remaining,fund_request_amount_remaining,amount_remaining
160 into l_util_id, l_claim_amount ,l_acctd_amount_remaining,l_univ_curr_amount_remaining,l_fund_req_amount_remaining,l_amount_remaining
161 from OZF_SD_BATCH_LINES_ALL
162 where batch_line_id=p_batch_line_id;
163
164
165
166 UPDATE OZF_FUNDS_UTILIZED_ALL_B
167 SET amount_remaining=amount_remaining+l_amount_remaining,
168 PLAN_CURR_AMOUNT_REMAINING=PLAN_CURR_AMOUNT_REMAINING+l_claim_amount,
169 acctd_amount_remaining=acctd_amount_remaining+l_acctd_amount_remaining,
170 univ_curr_amount_remaining=univ_curr_amount_remaining+l_amount_remaining,
171 fund_request_amount_remaining=fund_request_amount_remaining+l_fund_req_amount_remaining
172
173 WHERE UTILIZATION_ID=l_util_id;
174
175 DELETE FROM OZF_SD_BATCH_LINES_ALL WHERE batch_line_id=p_batch_line_id;
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177 x_meaning := NULL;
178
179
180 EXCEPTION
181
182 --error code will be returned to java layer where commit or rollback can happen
183 WHEN OTHERS then
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 x_meaning := NULL;
186 RETURN;
187
188 end;
189
190 PROCEDURE CONVERT_TO_RN_DATE(
191 p_server_date IN DATE,
192 x_rn_date OUT NOCOPY VARCHAR2)
193 IS
194 l_utc_date DATE;
195 l_milliseconds VARCHAR2(5);
196 l_server_timezone VARCHAR2(50);
197 l_error_code NUMBER;
198 l_error_msg VARCHAR2(255);
199 l_msg_data VARCHAR2(255);
200 BEGIN
201
202 IF(p_server_date is null) THEN
203 x_rn_date := null;
204 RETURN;
205 END IF;
206 x_rn_date := TO_CHAR(p_server_date,'YYYYMMDD')||'Z';
207
208
209 -- Exception Handling
210 EXCEPTION
211 WHEN OTHERS THEN
212 l_error_code := SQLCODE;
213 l_error_msg := SQLERRM;
214 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
215
216 END CONVERT_TO_RN_DATE;
217
218 PROCEDURE CONVERT_TO_DB_DATE(
219 p_rn_date IN VARCHAR2,
220 x_db_date OUT NOCOPY DATE)
221 IS
222 l_server_date DATE;
223 l_utc_datetime DATE;
224 l_count_t_appearanace NUMBER;
225 l_error_code NUMBER;
226 l_rn_frmt_date VARCHAR2(30);
227 l_rn_timezone VARCHAR2(30);
228 l_db_timezone VARCHAR2(30);
229 l_error_msg VARCHAR2(255);
230 l_msg_data VARCHAR2(255);
231 BEGIN
232
233
234 IF(p_rn_date is null) THEN
235 x_db_date := null;
236
237 RETURN;
238 END IF;
239 l_count_t_appearanace := instr(p_rn_date,'T');
240 IF (l_count_t_appearanace > 0) THEN
241 --Datetime Format: YYYYMMDDThhmmss.SSSZ
242 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
243
244 -- get the timezone of the db server
245 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
246
247
248 l_rn_frmt_date := substr(p_rn_date,1,8)||substr(p_rn_date,10,6);
249
250 l_utc_datetime := TO_DATE(l_rn_frmt_date,'YYYYMMDDHH24MISS');
251
252 -- this function converts the datetime from the user entered/db timezone to UTC
253 x_db_date := FND_TIMEZONES_PVT.adjust_datetime(l_utc_datetime,l_rn_timezone,l_db_timezone);
254
255 ELSE
256 --Date Format : YYYYMMDDZ
257
258 l_rn_frmt_date := substr(p_rn_date,1,8);
259
260 x_db_date := TO_DATE(l_rn_frmt_date,'YYYYMMDD');
261
262 END IF;
263
264 -- Exception Handling
265 EXCEPTION
266 WHEN OTHERS THEN
267 l_error_code := SQLCODE;
268 l_error_msg := SQLERRM;
269 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
270
271 END CONVERT_TO_DB_DATE;
272
273
274
275 ---------------------------------------------------------------------
276 -- PROCEDURE
277 -- UPDATE_SD_REQ_PRICES
278 --
279 -- PURPOSE
280 -- Updates the Ship and Debit price interface table with correct request line iD
281 -- PARAMETERS
282 -- a) p_request_number : The SD Request Number for transaction
283 -- b) p_request_line_id : Correct request line Id
284 --
285 -- NOTES
286 --
287 ---------------------------------------------------------------------------
288
289 PROCEDURE UPDATE_SD_REQ_PRICES(p_request_number IN VARCHAR2,p_request_line_id IN NUMBER)
290 IS
291 l_req_header_id NUMBER ;
292 l_req_line_id NUMBER ;
293
294 BEGIN
295
296 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET REQUEST_LINE_ID=p_request_line_id
297 WHERE REQUEST_NUMBER=p_request_number
298 AND REQUEST_LINE_ID IS NULL;
299
300 COMMIT;
301
302 END UPDATE_SD_REQ_PRICES;
303
304 ---------------------------------------------------------------------
305 -- PROCEDURE
306 -- PROCESS_SD_RESPONSE
307 --
308 -- PURPOSE
309 -- Updates the Ship and Debit header and base tables for the inbound data
310 -- only when the business validation(s) are passed
311 -- The business validation involved are
312 -- a)
313 -- PARAMETERS
314 -- a) p_request_number : The SD Request Number
315 -- b) x_return_status : Return status for the processing
316 -- c) x_msg_data : Error message if the validation errored out
317 -- NOTES
318 --
319 ----------------------------------------------------------------------
320
321 PROCEDURE PROCESS_SD_RESPONSE(p_request_number IN VARCHAR2
322 , x_return_status OUT nocopy VARCHAR2
323 , x_msg_data OUT nocopy VARCHAR2 )
324 IS
325
326 l_return_status VARCHAR2(30);
327 l_msg_data VARCHAR2(2000):='Data submitted is not valid :';
328 l_req_id NUMBER :=0 ;
329 l_req_number_count NUMBER ;
330 l_req_status AMS_USER_STATUSES_B.SYSTEM_STATUS_CODE%TYPE;
331 l_request_status OZF_SD_REQUEST_HEADERS_ALL_B.USER_STATUS_ID%TYPE;
332 l_curr_prod_context OZF_SD_REQUEST_LINES_ALL.PRODUCT_CONTEXT%TYPE ;
333 l_curr_code OZF_SD_REQUEST_HEADERS_ALL_B.REQUEST_CURRENCY_CODE%TYPE;
334 l_approved_lines NUMBER :=0;
335 l_error_count NUMBER := 0 ;
336 l_currency_count NUMBER :=0 ;
337 l_line_status VARCHAR2(1) :='S' ;
338 l_authorization_number OZF_SD_RES_HEADER_INTF.AUTH_NUMBER%TYPE ;
339 l_error_message FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE ;
340
341 CURSOR FETCH_PROD_INTF_REC IS
342 SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
343 PRODINTF.PROD_TYPE INF_PROD_TYPE,
344 PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
345 PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
346 PROD.CONCATENATED_SEGMENTS LINE_PROD_CODE,
347 PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
348 PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
349 PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
350 PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
351 PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
352 NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
353 NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
354 FROM OZF_SD_RES_PROD_INTF PRODINTF,
355 OZF_SD_REQUEST_LINES_ALL PRODLINES,
356 mtl_system_items_b_kfv PROD
357 where NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
358 PRODINTF.REQUEST_NUMBER=p_request_number
359 AND PRODLINES.REQUEST_HEADER_ID=request_header_id
360 AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
361 AND PRODLINES.PRODUCT_CONTEXT = 'PRODUCT'
362 AND PRODLINES.ORG_ID=PROD.ORGANIZATION_ID
363 AND PRODLINES.INVENTORY_ITEM_ID=PROD.INVENTORY_ITEM_ID
364
365 UNION
366 SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
367 PRODINTF.PROD_TYPE INF_PROD_TYPE,
368 PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
369 PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
370 NVL(D.CATEGORY_DESC, 'NA') PROD_CODE,
371 PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
372 PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
373 PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
374 PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
375 PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
376 NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
377 NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
378 FROM OZF_SD_RES_PROD_INTF PRODINTF,
379 OZF_SD_REQUEST_LINES_ALL PRODLINES,
380 ENI_PROD_DEN_HRCHY_PARENTS_V D
381 WHERE NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
382 PRODINTF.REQUEST_NUMBER=p_request_number
383 AND PRODLINES.REQUEST_HEADER_ID=request_header_id
384 AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
385 AND PRODLINES.PRODUCT_CONTEXT ='PRODUCT_CATEGORY'
386 AND PRODLINES.PROD_CATG_ID=D.CATEGORY_ID
387 AND PRODLINES.PRODUCT_CAT_SET_ID = D.CATEGORY_SET_ID
388
389 UNION
390 SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
391 PRODINTF.PROD_TYPE INF_PROD_TYPE,
392 PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
393 PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
394 'ALL' PROD_CODE,
395 PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
396 PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
397 PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
398 PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
399 PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
400 NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
401 NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
402 FROM OZF_SD_RES_PROD_INTF PRODINTF,
403 OZF_SD_REQUEST_LINES_ALL PRODLINES
404 WHERE NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
405 PRODINTF.REQUEST_NUMBER=p_request_number
406 AND PRODLINES.REQUEST_HEADER_ID=request_header_id
407 AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
408 AND PRODLINES.PRODUCT_CONTEXT ='ALL_ITEMS';
409
410
411
412
413 BEGIN
414
415 x_msg_data := '';
416 x_return_status := FND_API.G_RET_STS_SUCCESS ;
417 l_error_message := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR') ;
418
419 -- Validate the SDR Request Number
420 Select count(*) into l_req_number_count from OZF_SD_REQUEST_HEADERS_ALL_B
421 where REQUEST_NUMBER = p_request_number ;
422 IF l_req_number_count = 0 THEN
423
424 l_msg_data := l_msg_data ||','|| 'The request number '||p_request_number ||' is not valid.' ;
425 x_msg_data := 'No Ship and Debit Request exists for : '||p_request_number;
426 x_return_status := FND_API.G_RET_STS_ERROR ;
427
428 -- DO THE MASS UPDATE FOR THE INTERFACE TABLES
429 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) ;
430
431 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
432
433 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
434
435 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
436
437 COMMIT ;
438 return ;
439
440 END IF ;
441
442
443 Select REQ.request_header_id,STATUS.SYSTEM_STATUS_CODE,REQ.request_currency_code into l_req_id,l_req_status,l_curr_code
444 from OZF_SD_REQUEST_HEADERS_ALL_B REQ,AMS_USER_STATUSES_VL STATUS
445 where REQ.REQUEST_NUMBER = p_request_number
446 AND REQ.USER_STATUS_ID=STATUS.USER_STATUS_ID ;
447
448
449 -- If the request status is "PENDING_SUPPLIER_APPROVAL" then process the product lines in loop
450 IF l_req_status='PENDING_SUPPLIER_APPROVAL' THEN
451
452 -- Update the non responded lines as 'rejected' and rejection code as ' No response from vendor'
453 UPDATE OZF_SD_REQUEST_LINES_ALL SET VENDOR_APPROVED_FLAG='N',REJECTION_CODE='OZF_SD_NO_RESPONSE'
454 WHERE REQUEST_HEADER_ID=request_header_id
455 AND REQUEST_LINE_ID IN ( SELECT REQUEST_LINE_ID FROM OZF_SD_REQUEST_LINES_ALL
456 WHERE REQUEST_HEADER_ID=request_header_id
457 MINUS
458 SELECT REQUEST_LINE_ID FROM OZF_SD_RES_PROD_INTF
459 WHERE REQUEST_NUMBER=p_request_number
460 AND NVL(PROCESSED_FLAG,'N') <>'Y') ;
461
462
463 FOR PROD_REC IN FETCH_PROD_INTF_REC
464 LOOP
465 l_msg_data := '';
466 -- Set the line status as valid
467 l_line_status := 'S' ;
468
469 -- Validate the product type
470 IF PROD_REC.INF_PROD_TYPE IS NOT NULL AND
471 PROD_REC.LINE_PROD_CONTEXT <> UPPER(PROD_REC.INF_PROD_TYPE) THEN
472
473
474 l_msg_data := l_msg_data ||','|| 'Product context mismatch' ;
475
476 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
477 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
478 AND NVL(PROCESSED_FLAG,'N') <>'Y';
479
480 l_error_count := l_error_count +1 ;
481 x_msg_data := l_error_message;
482 x_return_status := FND_API.G_RET_STS_ERROR ;
483 l_line_status := 'E' ;
484 END IF;
485
486 -- Validate the product code
487 IF PROD_REC.LINE_PROD_CONTEXT = 'PRODUCT' AND
488 PROD_REC.LINE_PROD_CODE<>PROD_REC.INF_PROD_CODE THEN
489
490
491 l_msg_data := l_msg_data ||','|| 'Product code mismatch' ;
492
493 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
494 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
495 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
496
497 l_error_count := l_error_count +1 ;
498 x_msg_data := l_error_message;
499 x_return_status := FND_API.G_RET_STS_ERROR ;
500 l_line_status := 'E' ;
501 END IF;
502
503 -- Validate the discount type
504 IF PROD_REC.INTF_DISCOUNT_TYE IS NOT NULL AND
505 PROD_REC.INTF_DISCOUNT_TYE <> PROD_REC.LINE_DISCOUNT_TYPE THEN
506
507
508 l_msg_data := l_msg_data ||','|| 'Discount type mismatch' ;
509
510 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Discount type mismatch'
511 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
512 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
513
514 l_error_count := l_error_count +1 ;
515 x_msg_data := l_error_message;
516 x_return_status := FND_API.G_RET_STS_ERROR ;
517 l_line_status := 'E' ;
518 END IF;
519
520 -- Validate the currency if not null
521
522 l_curr_code := PROD_REC.INTF_CURR_CODE ;
523 IF PROD_REC.INTF_CURR_CODE IS NULL OR trim(PROD_REC.INTF_CURR_CODE)='' THEN
524 l_curr_code := PROD_REC.LINE_DISCOUNT_CUR ;
525 END IF ;
526
527
528 IF PROD_REC.LINE_DISCOUNT_TYPE <>'%' THEN
529
530
531 SELECT count(*) INTO l_currency_count
532 FROM FND_CURRENCIES
533 WHERE currency_code = l_curr_code;
534 IF l_currency_count=0 THEN
535
536
537 l_msg_data := l_msg_data ||','|| 'Currency code mismatch' ;
538
539 UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Currency code mismatch'
540 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
541 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
542
543 l_error_count := l_error_count +1 ;
544 x_msg_data := l_error_message;
545 x_return_status := FND_API.G_RET_STS_ERROR ;
546 l_line_status := 'E' ;
547 END IF;
548 END IF ;
549
550 -- Check the validation status of line : If an valid line then update the base table data
551 IF l_line_status <> 'E' THEN
552 -- Update the status for product line as 'Rejected'
553 IF PROD_REC.INF_REJ_CODE IS NOT NULL THEN
554 UPDATE OZF_SD_REQUEST_LINES_ALL SET REJECTION_CODE=PROD_REC.INF_REJ_CODE,VENDOR_APPROVED_FLAG='N'
555 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
556
557 END IF;
558 -- Update the approved amount,approved currency and type in the base line table
559 UPDATE OZF_SD_REQUEST_LINES_ALL SET APPROVED_DISCOUNT_TYPE=PROD_REC.LINE_DISCOUNT_TYPE,
560 APPROVED_DISCOUNT_VALUE=PROD_REC.INTF_APPROVED_DISCOUNT_VALUE,
561 APPROVED_MAX_QTY=PROD_REC.INTF_APPROVED_QUANTITY,
562 APPROVED_DISCOUNT_CURRENCY=l_curr_code
563 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
564
565
566 END IF;
567 END LOOP;
568
569
570 -- UPDATE THE REQUEST STATUS IN HEADER TABLE : OZF_SD_REQUEST_HEADERS_ALL_B
571 SELECT COUNT(*) INTO l_approved_lines FROM OZF_SD_REQUEST_LINES_ALL
572 WHERE REQUEST_HEADER_ID=l_req_id
573 AND VENDOR_APPROVED_FLAG = 'Y' ;
574
575
576 IF l_approved_lines > 0 THEN
577 SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
578 where system_status_TYPE='OZF_SD_REQUEST_STATUS'
579 and SYSTEM_STATUS_CODE='SUPPLIER_APPROVED'
580 and default_flag='Y'
581 and enabled_flag='Y' ;
582 ELSE
583 SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
584 where system_status_TYPE='OZF_SD_REQUEST_STATUS'
585 and SYSTEM_STATUS_CODE='SUPPLIER_REJECTED'
586 and default_flag='Y'
587 and enabled_flag='Y' ;
588 END IF ;
589
590 SELECT AUTH_NUMBER INTO l_authorization_number FROM OZF_SD_RES_HEADER_INTF WHERE request_number=p_request_number
591 AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
592
593 UPDATE OZF_SD_REQUEST_HEADERS_ALL_B SET user_status_id= l_request_status,AUTHORIZATION_NUMBER=l_authorization_number
594 WHERE REQUEST_HEADER_ID=l_req_id ;
595
596
597 ELSE
598 l_msg_data := l_msg_data ||','|| 'The ststus is not pending suppler approval' ;
599 x_msg_data := 'Currently the request is not pending for supplier approval';
600 x_return_status := FND_API.G_RET_STS_ERROR;
601
602 UPDATE OZF_SD_RES_HEADER_INTF SET ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number
603 and NVL(PROCESSED_FLAG,'N') <>'Y';
604
605
606 END IF ;
607
608
609 -- DO THE MASS UPDATE FOR THE INTERFACE TABLES
610
611 UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
612 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
613 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
614 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
615 COMMIT ;
616 EXCEPTION
617
618 WHEN OTHERS then
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 -- DO THE MASS UPDATE FOR THE INTERFACE TABLES
621 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';
622 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';
623 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';
624 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';
625
626 COMMIT ;
627 RETURN;
628
629 END PROCESS_SD_RESPONSE;
630
631
632 PROCEDURE CONVERT_TO_RN_DATETIME(
633 p_server_date IN DATE,
634 x_rn_datetime OUT NOCOPY VARCHAR2)
635 IS
636 l_error_code NUMBER;
637 l_utc_date DATE;
638 l_milliseconds VARCHAR2(5);
639 l_server_timezone VARCHAR2(30);
640 l_error_msg VARCHAR2(255);
641 l_msg_data VARCHAR2(255);
642 BEGIN
643
644 IF(p_server_date is null) THEN
645 x_rn_datetime := null;
646
647 RETURN;
648 END IF;
649
650 CONVERT_TO_RN_TIMEZONE(
651 p_input_date => p_server_date,
652 x_utc_date => l_utc_date );
653
654 l_milliseconds := '000'; --We wont get milliseconds
655
656 x_rn_datetime := TO_CHAR(l_utc_date,'YYYYMMDD')||'T'||TO_CHAR(l_utc_date,'hh24miss')||'.'||l_milliseconds||'Z';
657
658
659 -- Exception Handling
660 EXCEPTION
661 WHEN OTHERS THEN
662 l_error_code := SQLCODE;
663 l_error_msg := SQLERRM;
664 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
665
666 END CONVERT_TO_RN_DATETIME;
667
668
669
670 PROCEDURE CONVERT_TO_RN_TIMEZONE(
671 p_input_date IN DATE,
672 x_utc_date OUT NOCOPY DATE )
673 IS
674 l_error_code NUMBER;
675 l_db_timezone VARCHAR2(30);
676 l_rn_timezone VARCHAR2(30);
677 l_error_msg VARCHAR2(255);
678 l_msg_data VARCHAR2(255);
679 BEGIN
680
681 -- get the timezone of the db server
682 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
683
684 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
685
686
687 -- this function converts the datetime from the user entered/db timezone to UTC
688 x_utc_date := FND_TIMEZONES_PVT.adjust_datetime(p_input_date,l_db_timezone,l_rn_timezone);
689
690
691 -- Exception Handling
692 EXCEPTION
693 WHEN OTHERS THEN
694 l_error_code := SQLCODE;
695 l_error_msg := SQLERRM;
696 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
697
698 END CONVERT_TO_RN_TIMEZONE;
699
700 ---------------------------------------------------------------------
701 -- PROCEDURE
702 -- UPDATE_SD_REQ_STALE_DATA
703 --
704 -- PURPOSE
705 -- Updates the Ship and Debit interface tables for any stale data
706 --
707 -- PARAMETERS
708 -- a) p_request_number : The SD Request Number
709 -- NOTES
710 --
711 ----------------------------------------------------------------------
712
713 PROCEDURE UPDATE_SD_REQ_STALE_DATA(p_request_number IN VARCHAR2)
714 IS
715 l_req_number_count NUMBER :=0;
716 BEGIN
717
718 UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
719 UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
720 UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
721 UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
722 COMMIT ;
723
724 END UPDATE_SD_REQ_STALE_DATA;
725
726 ---------------------------------------------------------------------
727 -- PROCEDURE
728 -- SD_RAISE_EVENT
729 --
730 -- PURPOSE
731 -- This procedure raises a Business Event based on batch action.
732 --
733 -- PARAMETERS
734 -- a) Batch ID
735 -- b) Batch Action - can be EXPORT,CREATE, RESPONSE and CLAIM.
736 -- NOTES
737 --
738 ----------------------------------------------------------------------
739
740
741 PROCEDURE SD_RAISE_EVENT(P_BATCH_ID IN NUMBER,
742 P_BATCH_ACTION IN VARCHAR2,
743 x_return_status OUT NOCOPY VARCHAR2) IS
744
745 p_event_name VARCHAR2(100) := 'oracle.apps.ozf.sd.batch.lifecycle';
746 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
747 evtkey VARCHAR2(100);
748
749
750 BEGIN
751 evtkey := 'SDB' || P_BATCH_ID || dbms_utility.get_time();
752 wf_event.addparametertolist(p_name => 'BATCH_ID', p_value => P_BATCH_ID, p_parameterlist => l_parameter_list);
753 wf_event.addparametertolist(p_name => 'ACTION_NAME', p_value => P_BATCH_ACTION, p_parameterlist => l_parameter_list);
754 wf_event.RAISE(p_event_name, evtkey, NULL, l_parameter_list, sysdate);
755
756 EXCEPTION
757
758 WHEN OTHERS then
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 RETURN;
761
762 END SD_RAISE_EVENT;
763
764
765 ---------------------------------------------------------------------
766 -- PROCEDURE
767 -- PROCESS_BATCH_ADJUST_CLAIM
768 --
769 -- PURPOSE
770 -- This procedure is to process the submitted batch for claim and adjustment
771 -- This will be called from the batch UI in Close and Export
772 --
773 -- PARAMETERS
774 -- a) p_batch_header_id - Batch header Id
775 --
776 -- NOTES
777 --
778 ----------------------------------------------------------------------
779 PROCEDURE PROCESS_BATCH_ADJUST_CLAIM( p_batch_header_id NUMBER,
780 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
781 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
782 x_return_status OUT NOCOPY VARCHAR2,
783 x_msg_count OUT NOCOPY NUMBER,
784 x_msg_data OUT NOCOPY VARCHAR2) IS
785
786 l_procedure_name VARCHAR2(30) := 'PROCESS_BATCH_ADJUST_CLAIM' ;
787 l_unapproved_line_count NUMBER ;
788 l_approved_line_count NUMBER;
789 l_total_line_count NUMBER;
790 l_full_write_off VARCHAR2(1) := fnd_api.g_false ;
791 l_status_code VARCHAR2(30) ;
792 l_claim_id number ;
793 l_orig_app_line_count NUMBER ;
794 l_orig_total_line_count NUMBER ;
795 l_create_claim VARCHAR2(1) := fnd_api.g_false ;
796
797 l_total_app_claim_amt NUMBER ;
798 l_tot_non_rma_lines NUMBER ;
799 l_imd_claim_flag VARCHAR2(1);
800
801
802
803
804 BEGIN
805
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submitted batch is '||p_batch_header_id);
809
810 SAVEPOINT process_batch_adjust_claim_sv;
811
812
813 SELECT status_code,imd_claim_flag into l_status_code,l_imd_claim_flag
814 FROM OZF_SD_BATCH_HEADERS_ALL
815 WHERE BATCH_ID=p_batch_header_id ;
816
817 FND_FILE.PUT_LINE(FND_FILE.LOG, '- imd_claim_flag for batch is '||l_imd_claim_flag );
818 FND_FILE.PUT_LINE(FND_FILE.LOG, '- for batch id '||p_batch_header_id||' status code is'||l_status_code);
819
820 --If partial approved is allowed for a batch then on batch closure we just do adjustments
821 IF l_imd_claim_flag ='Y' THEN
822
823
824 UPDATE OZF_SD_BATCH_LINES_ALL
825 SET status_code ='APPROVED'
826 WHERE BATCH_ID = p_batch_header_id
827 AND COMPLETE_FLAG ='Y'
828 AND PURGE_FLAG <>'Y'
829 AND ORIGINAL_CLAIM_AMOUNT > 0
830 AND TOTAL_APPROVED_AMT IS NOT NULL
831 AND status_code NOT IN ('APPROVED','COMPLETED');
832
833
834 l_full_write_off := fnd_api.g_true;
835
836 FND_FILE.PUT_LINE(FND_FILE.LOG, '- l_full_write_off '||l_full_write_off);
837
838 create_adjustment(p_batch_header_id =>p_batch_header_id,
839 p_comp_wrt_off=> l_full_write_off,
840 x_return_status=> x_return_status,
841 x_msg_count=> x_msg_count,
842 x_msg_data=> x_msg_data);
843
844
845 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Status of Adjustment API call '||x_return_status);
846
847 IF x_return_status <> FND_API.G_RET_STS_SUCCESS AND l_full_write_off = fnd_api.g_FALSE THEN
848
849 ROLLBACK TO SAVEPOINT process_batch_adjust_claim_sv;
850
851 FOR I IN 1..x_msg_count LOOP
852
853 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
854 ' Msg from Claim API while invoking claim for batch '
855 || SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
856
857 END LOOP;
858
859 RETURN;
860 END IF;
861
862 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Adjustment completed');
863
864 ELSE
865
866 -- Check if the sum is negative
867
868 select sum(approved_unit_claim_amount) INTO l_total_app_claim_amt from (
869 select
870 CASE
871 WHEN((approved_amount is null and QUANTITY_APPROVED is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR QUANTITY_APPROVED < 0) then null
872 WHEN (approved_amount is null and QUANTITY_APPROVED IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) )
873 WHEN (approved_amount is null and QUANTITY_APPROVED IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN discount_value
874 WHEN (QUANTITY_APPROVED is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) )
875 WHEN (QUANTITY_APPROVED is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN approved_amount
876 WHEN (QUANTITY_APPROVED is not null and quantity_approved <> 0 and approved_amount is not null and approved_amount <> 0 AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) )
877 WHEN (QUANTITY_APPROVED is not null and quantity_approved <> 0 and approved_amount is not null and approved_amount <> 0 AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount)
878 END approved_unit_claim_amount
879
880 from OZF_SD_BATCH_LINES_ALL line
881 WHERE line.complete_flag ='Y'
882 AND line.batch_id = p_batch_header_id
883 AND line.purge_flag <>'Y'
884 AND line.ORIGINAL_CLAIM_AMOUNT >0
885 AND (line.ORIGINAL_CLAIM_AMOUNT-line.CLAIM_AMOUNT)<>0
886 AND line.status_code NOT IN ('APPROVED','COMPLETED') ) ;
887
888
889
890
891
892 SELECT app_lines.app_count,
893 all_lines.total_count
894 INTO l_orig_app_line_count, l_orig_total_line_count
895 FROM
896 (SELECT COUNT(1) total_count
897 FROM OZF_SD_BATCH_LINES_ALL
898 WHERE batch_id = p_batch_header_id
899 ) all_lines,
900 (SELECT COUNT(1) app_count
901 FROM OZF_SD_BATCH_LINES_ALL
902 WHERE batch_id = p_batch_header_id
903 AND status_code IN ('APPROVED')
904 ) app_lines ;
905
906
907
908 IF (l_total_app_claim_amt>0) THEN
909
910 UPDATE OZF_SD_BATCH_LINES_ALL
911 SET status_code ='APPROVED'
912 WHERE batch_id = p_batch_header_id
913 AND batch_line_id IN
914 (
915 select line.batch_line_id
916 from OZF_SD_BATCH_LINES_ALL line
917 WHERE line.complete_flag ='Y'
918 AND line.batch_id = p_batch_header_id
919 AND line.purge_flag <>'Y'
920 AND line.ORIGINAL_CLAIM_AMOUNT >0
921 AND (line.ORIGINAL_CLAIM_AMOUNT-line.CLAIM_AMOUNT)<>0
922 AND line.status_code NOT IN ('APPROVED','COMPLETED')
923
924 minus
925
926 select batch_line_id
927 from ozf_sd_batch_line_disputes
928 where dispute_code in('OZF_SD_CURR_CODE_MISMATCH', 'OZF_SD_VENDOR_AUTH_AMT_NGTVE' ,'OZF_SD_VENDOR_AUTH_QTY_NGTVE','OZF_SD_AUTH_AMT_QTY_NULL','OZF_SD_NO_RESPONSE')
929 and batch_id = p_batch_header_id
930 group by batch_line_id,dispute_code
931 having count(dispute_code)>0
932 ) ;
933
934 END IF ;
935
936
937
938
939 SELECT (all_lines.total_count - app_lines.app_count),
940 app_lines.app_count,
941 all_lines.total_count
942 INTO l_unapproved_line_count, l_approved_line_count, l_total_line_count
943 FROM
944 (SELECT COUNT(1) total_count
945 FROM OZF_SD_BATCH_LINES_ALL
946 WHERE batch_id = p_batch_header_id
947 ) all_lines,
948 (SELECT COUNT(1) app_count
949 FROM OZF_SD_BATCH_LINES_ALL
950 WHERE batch_id = p_batch_header_id
951 AND status_code IN ('APPROVED')
952 ) app_lines ;
953
954
955
956 -- Check if all lines are approved and batch header is not APPROVED.
957 IF(l_approved_line_count = l_total_line_count) THEN
958
959 l_full_write_off := fnd_api.g_true;
960
961 FND_FILE.PUT_LINE(FND_FILE.LOG, '- All the lines are approved');
962 FND_FILE.PUT_LINE(FND_FILE.LOG, '- l_approved_line_count '||l_approved_line_count);
963 FND_FILE.PUT_LINE(FND_FILE.LOG, '- l_total_line_count '||l_total_line_count);
964 FND_FILE.PUT_LINE(FND_FILE.LOG, '- l_orig_app_line_count '||l_orig_app_line_count);
965 FND_FILE.PUT_LINE(FND_FILE.LOG, '- l_orig_total_line_count '||l_orig_total_line_count);
966
967
968 -- Only for WIP batch create claim
969 IF (l_orig_app_line_count <> l_orig_total_line_count) THEN
970
971 l_full_write_off := fnd_api.g_FALSE ;
972 l_create_claim := fnd_api.g_true;
973
974 FND_FILE.PUT_LINE(FND_FILE.LOG, '- All the lines are approved and processing the claim');
975
976 OZF_SD_BATCH_FEED_PVT.process_claim(p_batch_header_id,
977 x_return_status,
978 x_msg_data,l_claim_id);
979
980 FND_FILE.PUT_LINE(FND_FILE.LOG, ' - Status of claim API call '||x_return_status);
981
982 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
983 ROLLBACK TO SAVEPOINT process_batch_adjust_claim_sv;
984
985 FND_FILE.PUT_LINE(FND_FILE.LOG, x_msg_data);
986
987 RETURN ;
988 END IF ;
989
990 FND_FILE.PUT_LINE(FND_FILE.LOG, ' -Claim created successfully ');
991
992 end if ;
993
994 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claiing the create Adjustment API ');
995 FND_FILE.PUT_LINE(FND_FILE.LOG, '- l_full_write_off '||l_full_write_off);
996
997 create_adjustment(p_batch_header_id =>p_batch_header_id,
998 p_comp_wrt_off=> l_full_write_off,
999 x_return_status=> x_return_status,
1000 x_msg_count=> x_msg_count,
1001 x_msg_data=> x_msg_data);
1002
1003
1004 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Status of Adjustment API call '||x_return_status);
1005
1006 IF x_return_status <> FND_API.G_RET_STS_SUCCESS AND l_full_write_off = fnd_api.g_FALSE THEN
1007
1008 ROLLBACK TO SAVEPOINT process_batch_adjust_claim_sv;
1009
1010 FOR I IN 1..x_msg_count LOOP
1011
1012 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1013 ' Msg from Claim API while invoking claim for batch '
1014 || SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
1015
1016 END LOOP;
1017
1018 RETURN;
1019 END IF;
1020
1021 IF (l_create_claim=fnd_api.g_true) THEN
1022
1023 sd_raise_event (p_batch_header_id, 'CLAIM', x_return_status);
1024
1025 FND_FILE.PUT_LINE(FND_FILE.LOG, ' -Raised life cycle business event ');
1026
1027
1028 END IF ;
1029
1030
1031 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Adjustment completed');
1032
1033
1034
1035 else
1036
1037
1038 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Few or all lines are not ');
1039
1040 if (l_approved_line_count > 0) THEN
1041
1042 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Few lines are approved - Calling create child batch');
1043
1044
1045 OZF_SD_BATCH_FEED_PVT.PROCESS_CHILD_BATCH(p_batch_header_id,x_return_status,x_msg_data) ;
1046
1047 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Process child batch returned status '||x_return_status);
1048 End if;
1049
1050 -- The unapproved lines, with complete flag true, the complete write off would happen
1051 l_full_write_off := fnd_api.g_true ;
1052
1053 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Adjusting the lines of the current batch');
1054
1055
1056 create_adjustment(p_batch_header_id =>p_batch_header_id,
1057 p_comp_wrt_off=> l_full_write_off,
1058 x_return_status=> x_return_status,
1059 x_msg_count=> x_msg_count,
1060 x_msg_data=> x_msg_data);
1061
1062
1063 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Adjusted the batch '||x_return_status);
1064
1065 end if ;
1066
1067 END IF;
1068
1069
1070 -- Update the Batch as closed based on the all lines status
1071
1072 SELECT (all_lines.total_count - (app_lines.app_count + com_lines.com_count + rma_lines.rma_count))
1073 INTO l_unapproved_line_count
1074 FROM
1075 (SELECT COUNT(1) total_count
1076 FROM OZF_SD_BATCH_LINES_ALL
1077 WHERE batch_id = p_batch_header_id
1078 ) all_lines,
1079 (SELECT COUNT(1) app_count
1080 FROM OZF_SD_BATCH_LINES_ALL
1081 WHERE batch_id = p_batch_header_id
1082 AND status_code = 'APPROVED'
1083 ) app_lines,
1084 (SELECT COUNT(1) com_count
1085 FROM OZF_SD_BATCH_LINES_ALL
1086 WHERE batch_id = p_batch_header_id
1087 AND status_code = 'COMPLETED'
1088 ) com_lines,
1089 (SELECT COUNT(1) rma_count
1090 FROM OZF_SD_BATCH_LINES_ALL
1091 WHERE batch_id = p_batch_header_id
1092 AND ORIGINAL_CLAIM_AMOUNT < 0
1093 ) rma_lines ;
1094
1095 IF (l_unapproved_line_count = 0 ) THEN
1096
1097 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Changing status to Closed');
1098
1099
1100 UPDATE ozf_sd_batch_headers_all
1101 SET status_code = 'CLOSED'
1102 WHERE BATCH_ID = p_batch_header_id ;
1103
1104 END IF ;
1105
1106 COMMIT;
1107
1108 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1109
1110 EXCEPTION
1111
1112 WHEN OTHERS then
1113 x_return_status := FND_API.G_RET_STS_ERROR;
1114
1115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected exception is :' || sqlerrm);
1116
1117 ROLLBACK TO SAVEPOINT process_batch_adjust_claim_sv;
1118 RETURN;
1119
1120 END PROCESS_BATCH_ADJUST_CLAIM;
1121
1122
1123 ---------------------------------------------------------------------
1124 -- PROCEDURE
1125 -- CREATE_ADJUSTMENT
1126 --
1127 -- PURPOSE
1128 -- This procedure is to create adjustment for submitted batch
1129 --
1130 -- PARAMETERS
1131 -- a) p_batch_header_id - Batch header Id
1132 --
1133 -- NOTES
1134 --
1135 ----------------------------------------------------------------------
1136 PROCEDURE CREATE_ADJUSTMENT( p_batch_header_id NUMBER,
1137 p_comp_wrt_off IN VARCHAR2 := fnd_api.g_false,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2) IS
1141
1142
1143 l_procedure_name VARCHAR2(30) := 'CREATE_ADJUSTMENT' ;
1144 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1145 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1146 x_act_budget_id NUMBER;
1147 l_new_util_id NUMBER ;
1148 l_fail_count NUMBER := 0;
1149 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1150 l_init_msg_list VARCHAR2(1) := fnd_api.g_false ;
1151 l_conv_adj_amount NUMBER; -- added to fix bug 9057734
1152
1153
1154 CURSOR c_batch_details IS
1155 SELECT lines.batch_line_id,
1156 lines.utilization_id ,
1157 lines.ADJUSTMENT_TYPE_ID,
1158 CASE WHEN (lines.status_code IN ('APPROVED','PARTIALLY_APPROVED') AND TOTAL_APPROVED_AMT IS NULL) THEN (lines.ORIGINAL_CLAIM_AMOUNT - lines.CLAIM_AMOUNT)
1159 WHEN (lines.status_code IN ('APPROVED','PARTIALLY_APPROVED') AND TOTAL_APPROVED_AMT IS NOT NULL ) THEN (lines.ORIGINAL_CLAIM_AMOUNT - lines.TOTAL_APPROVED_AMT)
1160 ELSE lines.ORIGINAL_CLAIM_AMOUNT
1161 END adj_amount ,
1162 lines.agreement_currency_code line_curr_code,
1163 adj.adjustment_type adj_type_name,
1164 util.cust_account_id,
1165 util.billto_cust_account_id,
1166 util.bill_to_site_use_id,
1167 util.product_level_type,
1168 util.product_id,
1169 util.object_type,
1170 util.object_id,
1171 util.order_line_id,
1172 util.org_id,
1173 util.fund_id,
1174 util.currency_code,
1175 util.plan_currency_code,
1176 util.plan_type,
1177 util.plan_id,
1178 util.exchange_rate_date
1179 FROM OZF_SD_BATCH_LINES_ALL lines ,
1180 ozf_funds_utilized_all_b util,
1181 ozf_claim_types_all_vl adj
1182
1183 WHERE lines.batch_id = p_batch_header_id
1184 AND (lines.status_code = 'APPROVED'
1185 OR
1186 lines.COMPLETE_FLAG='Y')
1187 and lines.ADJUSTMENT_TYPE_ID = adj.claim_type_id
1188 AND lines.utilization_id = util.utilization_id
1189 AND lines.ORIGINAL_CLAIM_AMOUNT >0
1190 AND lines.ADJ_UTILIZATION_ID is null;
1191
1192 BEGIN
1193
1194 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1195
1196
1197 FND_FILE.PUT_LINE(FND_FILE.LOG, p_batch_header_id||' - Submitted for adjustment');
1198
1199
1200 FOR adj_rec IN c_batch_details
1201 LOOP
1202
1203 IF(adj_rec.adj_amount<0) THEN
1204
1205 adj_rec.adj_amount := adj_rec.adj_amount * (-1) ;
1206 END IF ;
1207
1208 IF(adj_rec.adj_amount<>0) THEN
1209
1210
1211
1212 FND_FILE.PUT_LINE(FND_FILE.LOG, p_batch_header_id||' - Line for adjustment is '||adj_rec.batch_line_id);
1213
1214
1215 l_act_util_rec.adjustment_type := adj_rec.adj_type_name; --'DECREASE_EARNED';
1216 l_act_util_rec.adjustment_type_id := adj_rec.ADJUSTMENT_TYPE_ID;
1217 l_act_util_rec.utilization_type := 'ADJUSTMENT';
1218 l_act_util_rec.orig_utilization_id := adj_rec.utilization_id;
1219 l_act_util_rec.adjustment_date := SYSDATE;
1220 l_act_util_rec.gl_date := SYSDATE;
1221 l_act_util_rec.cust_account_id := adj_rec.cust_account_id;
1222 l_act_util_rec.billto_cust_account_id := adj_rec.billto_cust_account_id;
1223 l_act_util_rec.bill_to_site_use_id := adj_rec.bill_to_site_use_id;
1224 l_act_util_rec.product_level_type := adj_rec.product_level_type;
1225 l_act_util_rec.product_id := adj_rec.product_id;
1226 l_act_util_rec.object_type := adj_rec.object_type;
1227 l_act_util_rec.object_id := adj_rec.object_id;
1228 l_act_util_rec.order_line_id := adj_rec.order_line_id;
1229 l_act_util_rec.org_id := adj_rec.org_id;
1230
1231
1232 l_act_budgets_rec.request_amount := adj_rec.adj_amount;
1233 l_act_budgets_rec.status_code := 'APPROVED';
1234 l_act_budgets_rec.parent_source_id := adj_rec.fund_id;
1235 l_act_budgets_rec.parent_src_curr := adj_rec.currency_code;
1236 l_act_budgets_rec.request_currency := adj_rec.plan_currency_code;
1237 l_act_budgets_rec.budget_source_type := adj_rec.plan_type;
1238 l_act_budgets_rec.budget_source_id := adj_rec.plan_id;
1239 l_act_budgets_rec.arc_act_budget_used_by := adj_rec.plan_type;
1240 l_act_budgets_rec.act_budget_used_by_id := adj_rec.plan_id;
1241 l_act_budgets_rec.exchange_rate_date := adj_rec.exchange_rate_date;
1242
1243
1244 l_act_budgets_rec.transfer_type := 'UTILIZED';
1245 l_act_budgets_rec.transaction_type := 'DEBIT';-- All the utilization for DECREADED OR INCREASED Earned is of type DEBIT
1246
1247 ozf_fund_utilized_pvt.create_act_utilization(p_api_version => 1.0
1248 ,p_init_msg_list => l_init_msg_list
1249 ,p_validation_level => l_validation_level
1250 ,x_return_status => x_return_status
1251 ,x_msg_count => x_msg_count
1252 ,x_msg_data => x_msg_data
1253 ,p_act_budgets_rec => l_act_budgets_rec
1254 ,p_act_util_rec => l_act_util_rec
1255 ,x_act_budget_id => x_act_budget_id
1256 ,x_utilization_id => l_new_util_id
1257 );
1258
1259
1260 FND_FILE.PUT_LINE(FND_FILE.LOG,p_batch_header_id||'-'||adj_rec.batch_line_id||' Return staus for ASJ private call '||x_return_status );
1261
1262
1263
1264 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1265
1266
1267 fnd_msg_pub.count_and_get (
1268 p_encoded=> fnd_api.g_false
1269 ,p_count=> x_msg_count
1270 ,p_data=> x_msg_data);
1271
1272
1273 IF (p_comp_wrt_off = fnd_api.g_false) THEN
1274 RETURN;
1275 END IF;
1276
1277
1278 ELSE
1279
1280 UPDATE ozf_funds_utilized_all_b
1281 SET amount_remaining = 0,
1282 acctd_amount_remaining = 0,
1283 plan_curr_amount_remaining = 0,
1284 univ_curr_amount_remaining = 0
1285 WHERE utilization_id = l_new_util_id;
1286
1287 END IF;
1288
1289
1290 -- If complete write off, then change the line status to COMPLETE and update the new utlization iD
1291 -- Else update the utilization id but the line status
1292 IF (p_comp_wrt_off = fnd_api.g_true) THEN
1293
1294 UPDATE OZF_SD_BATCH_LINES_ALL
1295 SET ADJ_UTILIZATION_ID = l_new_util_id,
1296 status_code = 'COMPLETED'
1297 WHERE batch_line_id = adj_rec.batch_line_id ;
1298
1299 ELSE
1300 UPDATE OZF_SD_BATCH_LINES_ALL
1301 SET ADJ_UTILIZATION_ID = l_new_util_id
1302 WHERE batch_line_id = adj_rec.batch_line_id;
1303
1304 END IF;
1305
1306
1307 FND_FILE.PUT_LINE(FND_FILE.LOG,p_batch_header_id||' -Utilization created for line '||adj_rec.batch_line_id );
1308
1309
1310 END IF ;
1311 END LOOP;
1312
1313
1314
1315 FND_FILE.PUT_LINE(FND_FILE.LOG,p_batch_header_id||' -Adjustment completed '||x_return_status );
1316 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1317
1318 EXCEPTION
1319
1320 WHEN OTHERS then
1321 x_return_status := FND_API.G_RET_STS_ERROR;
1322
1323 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected exception is :' || sqlerrm);
1324
1325
1326 RETURN;
1327
1328 END CREATE_ADJUSTMENT;
1329
1330
1331 -- Start of comments
1332 -- API name : PROCESS_SD_PEN_CLOSE_BATCHES
1333 -- Type : Private
1334 -- Pre-reqs : Batch Status should be Pending Close
1335 -- Function : Executable target for concurrent program
1336 -- : Executable Name "OZFSDPBPEX"
1337 -- : Processes the batch for Adjustment and Claim
1338 -- Parameters :
1339 -- IN : p_batch_id IN NUMBER
1340
1341 -- End of comments
1342
1343 PROCEDURE PROCESS_SD_PEN_CLOSE_BATCHES(errbuf OUT nocopy VARCHAR2,
1344 retcode OUT nocopy NUMBER,
1345 p_batch_id NUMBER) IS
1346
1347
1348 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
1349 l_msg_count NUMBER ;
1350 l_msg_data VARCHAR2(2000) ;
1351 l_curr_batch_status VARCHAR2(30);
1352
1353 l_status_code VARCHAR2(30) := NULL;
1354 l_request_id NUMBER := NULL;
1355 l_phase_code VARCHAR2(1) := NULL;
1356
1357 Cursor c_pen_close_batches (p_batch_id number) is
1358 select bh.status_code, cr.request_id, cr.phase_code
1359 from OZF_SD_BATCH_HEADERS_ALL bh, FND_CONCURRENT_PROGRAMS cp , FND_CONCURRENT_REQUESTS cr
1360 where cp.concurrent_program_name = 'OZFSDPBPPRG'
1361 and cp.concurrent_program_id = bh.program_id
1362 and cr.request_id(+) = bh.request_id
1363 and bh.batch_id = p_batch_id;
1364
1365 BEGIN
1366
1367 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Processing for batch '|| p_batch_id || ' for closure.');
1368
1369
1370 OPEN c_pen_close_batches (p_batch_id);
1371 FETCH c_pen_close_batches into l_status_code, l_request_id, l_phase_code;
1372 CLOSE c_pen_close_batches;
1373
1374
1375 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current Status of batch is '|| l_status_code);
1376
1377 -- True when the request in BH not found in FND_Concurrent_Reqeusts / The current request is the request tagged in BH / Last request has finshed processing.
1378 IF ( l_status_code ='PENDING_CLOSE'
1379 AND ( l_request_id is null OR l_request_id = FND_GLOBAL.CONC_REQUEST_ID OR l_phase_code = 'C')) THEN
1380
1381 update ozf_sd_batch_headers_all
1382 set request_id = FND_GLOBAL.CONC_REQUEST_ID,
1383 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
1384 last_update_date = sysdate,
1385 last_updated_by = FND_GLOBAL.USER_ID,
1386 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1387 object_version_number = object_version_number + 1
1388 where batch_id = p_batch_id;
1389
1390 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoking api for processing Claim / Adjustment.');
1391
1392 PROCESS_BATCH_ADJUST_CLAIM( p_batch_header_id => p_batch_id,
1393 p_validation_level=> fnd_api.g_valid_level_full,
1394 p_init_msg_list=> fnd_api.g_false,
1395 x_return_status=> l_return_status ,
1396 x_msg_count=> l_msg_count ,
1397 x_msg_data=> l_msg_data ) ;
1398
1399 FND_FILE.PUT_LINE(FND_FILE.LOG, 'The processing for the batch is completed '|| l_return_status);
1400
1401 ELSE
1402
1403 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch could not be processed by this request.');
1404 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Either the batch is not in Pending Close state or it is currently being processed by another request.');
1405
1406 END IF ;
1407
1408 END PROCESS_SD_PEN_CLOSE_BATCHES ;
1409
1410
1411
1412 -- Start of comments
1413 -- API name : SET_COMPLETE_FLAGS
1414 -- Type : Private
1415 -- Purpose : Mark the line status to complete
1416 -- IN : p_batch_id IN NUMBER
1417
1418 -- End of comments
1419
1420
1421 PROCEDURE SET_COMPLETE_FLAGS( p_batch_header_id NUMBER,
1422 x_return_status OUT NOCOPY VARCHAR2) IS
1423
1424 l_incomplete_count NUMBER ;
1425
1426 BEGIN
1427
1428 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1429
1430 UPDATE OZF_SD_BATCH_LINES_ALL
1431 SET complete_flag='Y'
1432 WHERE batch_id=p_batch_header_id ;
1433
1434 COMMIT;
1435
1436 EXCEPTION
1437
1438 WHEN OTHERS then
1439 x_return_status := FND_API.G_RET_STS_ERROR;
1440
1441 FND_FILE.PUT_LINE(FND_FILE.LOG,p_batch_header_id||' Unexpected exception occured ' );
1442
1443 RETURN;
1444
1445
1446 END SET_COMPLETE_FLAGS ;
1447
1448
1449 -- Start of comments
1450 -- API name : PROCESS_SD_PEN_CLOSE_BATCHES
1451 -- Type : Private
1452 -- Purpose : Check if few lines have complete not set
1453 -- Parameters :
1454 -- IN : p_batch_id IN NUMBER
1455
1456 -- End of comments
1457
1458
1459 PROCEDURE CHECK_COMPLETE_FLAGS( p_batch_header_id NUMBER,
1460 x_return_status OUT NOCOPY VARCHAR2) IS
1461
1462
1463 l_incomplete_count NUMBER ;
1464
1465
1466 BEGIN
1467 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1468
1469 SELECT COUNT(1) INTO l_incomplete_count
1470 FROM OZF_SD_BATCH_LINES_ALL
1471 WHERE status_code IN( 'REJECTED','SUBMITTED')
1472 AND NVL(complete_flag,'N')<>'Y'
1473 AND batch_id=p_batch_header_id ;
1474
1475
1476 IF (l_incomplete_count>0) THEN
1477
1478 x_return_status := FND_API.G_RET_STS_ERROR ;
1479 END IF ;
1480
1481
1482 END CHECK_COMPLETE_FLAGS ;
1483
1484 end OZF_SD_UTIL_PVT;