1 PACKAGE BODY OZF_SD_BATCH_FEED_PVT as
2 /* $Header: ozfvsdfb.pls 120.71.12020000.3 2012/07/26 10:02:39 sariff ship $ */
3
4 -- Start of Comments
5 -- Package name : OZF_SD_BATCH_FEED_PVT
6 -- Purpose :
7 -- History :
8 -- : 30-JUN-2009 - Annsrini - If claim process is unsuccessful, then update batch header status as PENDING_CLAIM
9 -- : 20-JUL-2009 - Annsrini - Adjustment related changes
10 -- : 27-AUG-2009 - JMAHENDR - change of dispute code for
11 -- invalid / missing response as
12 -- OZF_SD_NO_RESPONSE
13 -- : 27-AUG-2009 - JMAHENDR - removed nvl on
14 -- vendor_auth_quantity with shipped quantity
15 -- : 07-DEC-2009 - ANNSRINI - changes w.r.t multicurrency
16 -- NOTE :
17 -- End of Comments
18
19 g_pkg_name constant VARCHAR2(30) := 'OZF_SD_BATCH_FEED_PVT';
20 g_file_name constant VARCHAR2(12) := 'ozfvsdfb.pls';
21
22 -- Author : MBHATT
23 -- Created : 11/16/2007 2:39:16 PM
24 -- Purpose :
25 -- Public function and procedure declarations
26 -- Private type declarations
27 PROCEDURE update_stale_data_batch_line(p_batch_number IN VARCHAR2,
28 p_batch_line_number IN VARCHAR2) AS
29
30 BEGIN
31 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_stale_data_batch_line', 'Procedure Starts');
32
33 UPDATE ozf_sd_batch_lines_int_all
34 SET processed_flag = 'S',
35 last_update_date = sysdate,
36 last_updated_by = fnd_global.user_id
37 WHERE ship_frm_sd_claim_request_id = p_batch_number
38 AND batch_line_number = p_batch_line_number
39 AND processed_flag = 'N';
40
41 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_stale_data_batch_line', 'Procedure Ends');
42
43 EXCEPTION
44 WHEN others THEN
45 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_stale_data_batch_line', 'Exception: '||sqlerrm);
46
47 END update_stale_data_batch_line;
48
49 PROCEDURE populate_interface(p_batch_number IN VARCHAR2,
50 p_operating_unit IN VARCHAR2,
51 p_frm_cntct1_name IN VARCHAR2,
52 p_frm_cntct1_email IN VARCHAR2,
53 p_frm_cntct1_fax IN VARCHAR2,
54 p_frm_cntct1_phone IN VARCHAR2,
55 p_frm_gbl_ptnr_role_class_cd IN VARCHAR2,
56 p_frm_gbl_business_id IN VARCHAR2,
57 p_frm_gbl_supply_chain_cd IN VARCHAR2,
58 p_frm_business_name IN VARCHAR2,
59 p_frm_prop_business_id1 IN VARCHAR2,
60 p_frm_prop_domain_id1 IN VARCHAR2,
61 p_frm_auth_id1 IN VARCHAR2,
62 p_frm_gbl_ptnr_class_cd IN VARCHAR2,
63 p_frm_cntct2_name IN VARCHAR2,
64 p_frm_cntct2_email IN VARCHAR2,
65 p_frm_cntct2_fax IN VARCHAR2,
66 p_frm_cntct2_phone IN VARCHAR2,
67 p_frm_gbl_loc_id IN VARCHAR2,
68 p_frm_prop_domain_id2 IN VARCHAR2,
69 p_frm_prop_auth_id2 IN VARCHAR2,
70 p_frm_prop_loc_id IN VARCHAR2,
71 p_frm_add_line1 IN VARCHAR2,
72 p_frm_add_line2 IN VARCHAR2,
73 p_frm_add_line3 IN VARCHAR2,
74 p_frm_city IN VARCHAR2,
75 p_frm_country IN VARCHAR2,
76 p_frm_postal_code IN VARCHAR2,
77 p_frm_po_box_id IN VARCHAR2,
78 p_frm_region IN VARCHAR2,
79 p_gbl_doc_func_code IN VARCHAR2,
80 p_ship_from_sd_auth_id IN VARCHAR2,
81 p_dist_by_gbl_business_id IN VARCHAR2,
82 p_dist_by_gbl_supp_chain_cd IN VARCHAR2,
83 p_dist_by_business_name IN VARCHAR2,
84 p_dist_by_prop_business_id IN VARCHAR2,
85 p_dist_by_prop_domain_id1 IN VARCHAR2,
86 p_dist_by_prop_auth_id1 IN VARCHAR2,
87 p_dist_by_gbl_ptnr_class_cd IN VARCHAR2,
88 p_dist_by_cntct_name IN VARCHAR2,
89 p_dist_by_cntct_email IN VARCHAR2,
90 p_dist_by_cntct_fax IN VARCHAR2,
91 p_dist_by_cntct_phone IN VARCHAR2,
92 p_dist_by_gbl_loc_id IN VARCHAR2,
93 p_dist_by_prop_domain_id2 IN VARCHAR2,
94 p_dist_by_prop_auth_id2 IN VARCHAR2,
95 p_dist_by_prop_loc_id IN VARCHAR2,
96 p_dist_by_add_line1 IN VARCHAR2,
97 p_dist_by_add_line2 IN VARCHAR2,
98 p_dist_by_add_line3 IN VARCHAR2,
99 p_dist_by_city IN VARCHAR2,
100 p_dist_by_country IN VARCHAR2,
101 p_dist_by_postal_code IN VARCHAR2,
102 p_dist_by_po_box_id IN VARCHAR2,
103 p_dist_by_region IN VARCHAR2,
104 p_ship_to_business_name IN VARCHAR2,
105 p_ship_to_gbl_business_id IN VARCHAR2,
106 p_ship_to_gbl_supp_chain_cd IN VARCHAR2,
107 p_ship_to_prop_business_id IN VARCHAR2,
108 p_ship_to_prop_domain_id1 IN VARCHAR2,
109 p_ship_to_prop_auth_id1 IN VARCHAR2,
110 p_ship_to_gbl_ptnr_class_cd IN VARCHAR2,
111 p_ship_to_cust_cntct_name IN VARCHAR2,
112 p_ship_to_cust_cntct_email IN VARCHAR2,
113 p_ship_to_cust_cntct_fax IN VARCHAR2,
114 p_ship_to_cust_cntct_phone IN VARCHAR2,
115 p_ship_to_cust_gbl_loc_id IN VARCHAR2,
116 p_ship_to_prop_domain_id2 IN VARCHAR2,
117 p_ship_to_prop_auth_id2 IN VARCHAR2,
118 p_ship_to_cust_prop_loc_id IN VARCHAR2,
119 p_ship_to_cust_add1 IN VARCHAR2,
120 p_ship_to_cust_add2 IN VARCHAR2,
121 p_ship_to_cust_add3 IN VARCHAR2,
122 p_ship_to_cust_city IN VARCHAR2,
123 p_ship_to_cust_country IN VARCHAR2,
124 p_ship_to_cust_postal_code IN VARCHAR2,
125 p_ship_to_cust_po_box_id IN VARCHAR2,
126 p_ship_to_cust_region IN VARCHAR2,
127 p_sold_to_business_name IN VARCHAR2,
128 p_sold_to_gbl_business_id IN VARCHAR2,
129 p_sold_to_gbl_supp_chain_cd IN VARCHAR2,
130 p_sold_to_prop_business_id IN VARCHAR2,
131 p_sold_to_prop_domain_id1 IN VARCHAR2,
132 p_sold_to_prop_auth_id1 IN VARCHAR2,
133 p_sold_to_gbl_ptnr_class_cd IN VARCHAR2,
137 p_sold_to_cust_cntct_phone IN VARCHAR2,
134 p_sold_to_cust_cntct_name IN VARCHAR2,
135 p_sold_to_cust_cntct_email IN VARCHAR2,
136 p_sold_to_cust_cntct_fax IN VARCHAR2,
138 p_sold_to_cust_gbl_loc_id IN VARCHAR2,
139 p_sold_to_prop_domain_id2 IN VARCHAR2,
140 p_sold_to_prop_auth_id2 IN VARCHAR2,
141 p_sold_to_cust_prop_loc_id IN VARCHAR2,
142 p_sold_to_cust_add1 IN VARCHAR2,
143 p_sold_to_cust_add2 IN VARCHAR2,
144 p_sold_to_cust_add3 IN VARCHAR2,
145 p_sold_to_cust_city IN VARCHAR2,
146 p_sold_to_cust_country IN VARCHAR2,
147 p_sold_to_cust_postal_code IN VARCHAR2,
148 p_sold_to_cust_po_box_id IN VARCHAR2,
149 p_sold_to_cust_region IN VARCHAR2,
150 p_end_cust_business_name IN VARCHAR2,
151 p_end_cust_gbl_business_id IN VARCHAR2,
152 p_end_cust_gbl_supp_chain_cd IN VARCHAR2,
153 p_end_cust_prop_business_id IN VARCHAR2,
154 p_end_cust_prop_domain_id1 IN VARCHAR2,
155 p_end_cust_prop_auth_id1 IN VARCHAR2,
156 p_end_cust_gbl_ptnr_class_cd IN VARCHAR2,
157 p_end_cust_cntct_name IN VARCHAR2,
158 p_end_cust_cntct_email IN VARCHAR2,
159 p_end_cust_cntct_fax IN VARCHAR2,
160 p_end_cust_cntct_phone IN VARCHAR2,
161 p_end_cust_gbl_loc_id IN VARCHAR2,
162 p_end_cust_prop_domain_id2 IN VARCHAR2,
163 p_end_cust_prop_auth_id2 IN VARCHAR2,
164 p_end_cust_prop_loc_id IN VARCHAR2,
165 p_end_cust_add1 IN VARCHAR2,
166 p_end_cust_add2 IN VARCHAR2,
167 p_end_cust_add3 IN VARCHAR2,
168 p_end_cust_city IN VARCHAR2,
169 p_end_cust_country IN VARCHAR2,
170 p_end_cust_postal_code IN VARCHAR2,
171 p_end_cust_po_box_id IN VARCHAR2,
172 p_end_cust_region IN VARCHAR2,
173 p_ship_frm_sd_claim_req_date IN DATE,
174 p_ship_frm_sd_claim_req_id IN VARCHAR2,
175 p_credit_ref_id IN VARCHAR2,
176 p_debit_ref_id IN VARCHAR2,
177 p_batch_line_id IN NUMBER,
178 p_batch_line_number IN NUMBER,
179 p_order_date IN DATE,
180 p_order_line_number IN VARCHAR2,
181 p_order_number IN VARCHAR2,
182 p_invoice_date IN DATE,
183 p_invoice_line_number IN VARCHAR2,
184 p_invoice_number IN VARCHAR2,
185 p_discount_type IN VARCHAR2,
186 p_discount_value IN NUMBER,
187 p_discount_currency IN VARCHAR2,
188 p_cost_price IN NUMBER,
189 p_cost_price_curr_code IN VARCHAR2,
190 p_auth_price IN NUMBER,
191 p_auth_price_curr_code IN VARCHAR2,
192 p_resale_price IN NUMBER,
193 p_resale_price_curr_code IN VARCHAR2,
194 p_uom IN VARCHAR2,
195 p_line_status IN VARCHAR2,
196 p_disposition_code1 IN VARCHAR2,
197 p_disposition_code2 IN VARCHAR2,
198 p_disposition_code3 IN VARCHAR2,
199 p_disposition_code4 IN VARCHAR2,
200 p_disposition_code5 IN VARCHAR2,
201 p_disposition_code6 IN VARCHAR2,
202 p_disposition_code7 IN VARCHAR2,
203 p_disposition_code8 IN VARCHAR2,
204 p_disposition_code9 IN VARCHAR2,
205 p_disposition_code10 IN VARCHAR2,
206 p_vendor_part_number IN VARCHAR2,
207 p_dist_part_number IN VARCHAR2,
208 p_date_shipped IN DATE,
209 p_qty_shipped IN NUMBER,
210 p_claim_amt_curr_code IN VARCHAR2,
211 p_last_sub_claim_amt IN NUMBER,
215 p_vendor_apprvd_qty IN NUMBER,
212 p_vendor_auth_line_item_no IN VARCHAR2,
213 p_vendor_apprvd_amt IN NUMBER,
214 p_vendor_apprvd_amt_curr_cd IN VARCHAR2,
216 p_batch_submission_date IN DATE,
217 p_batch_id IN NUMBER,
218 p_to_cntct1_name IN VARCHAR2,
219 p_to_cntct1_email IN VARCHAR2,
220 p_to_cntct1_fax IN VARCHAR2,
221 p_to_cntct1_phone IN VARCHAR2,
222 p_to_gbl_ptnr_role_class_cd IN VARCHAR2,
223 p_to_gbl_business_id IN VARCHAR2,
224 p_to_gbl_supply_chain_cd IN VARCHAR2,
225 p_to_business_name IN VARCHAR2,
226 p_to_prop_business_id1 IN VARCHAR2,
227 p_to_prop_domain_id1 IN VARCHAR2,
228 p_to_auth_id1 IN VARCHAR2,
229 p_to_gbl_ptnr_class_cd IN VARCHAR2,
230 p_to_cntct2_name IN VARCHAR2,
231 p_to_cntct2_email IN VARCHAR2,
232 p_to_cntct2_fax IN VARCHAR2,
233 p_to_cntct2_phone IN VARCHAR2,
234 p_to_gbl_loc_id IN VARCHAR2,
235 p_to_prop_domain_id2 IN VARCHAR2,
236 p_to_prop_auth_id2 IN VARCHAR2,
237 p_to_prop_loc_id IN VARCHAR2,
238 p_to_add_line1 IN VARCHAR2,
239 p_to_add_line2 IN VARCHAR2,
240 p_to_add_line3 IN VARCHAR2,
241 p_to_city IN VARCHAR2,
242 p_to_country IN VARCHAR2,
243 p_to_postal_code IN VARCHAR2,
244 p_to_po_box_id IN VARCHAR2,
245 p_to_region IN VARCHAR2,
246 P_HDR_ATTR_CATG IN VARCHAR2,
247 P_HDR_ATTR1 IN VARCHAR2,
248 P_HDR_ATTR2 IN VARCHAR2,
249 P_HDR_ATTR3 IN VARCHAR2,
250 P_HDR_ATTR4 IN VARCHAR2,
251 P_HDR_ATTR5 IN VARCHAR2,
252 P_HDR_ATTR6 IN VARCHAR2,
253 P_HDR_ATTR7 IN VARCHAR2,
254 P_HDR_ATTR8 IN VARCHAR2,
255 P_HDR_ATTR9 IN VARCHAR2,
256 P_HDR_ATTR10 IN VARCHAR2,
257 P_HDR_ATTR11 IN VARCHAR2,
258 P_HDR_ATTR12 IN VARCHAR2,
259 P_HDR_ATTR13 IN VARCHAR2,
260 P_HDR_ATTR14 IN VARCHAR2,
261 P_HDR_ATTR15 IN VARCHAR2,
262 P_HDR_ATTR16 IN VARCHAR2,
263 P_HDR_ATTR17 IN VARCHAR2,
264 P_HDR_ATTR18 IN VARCHAR2,
265 P_HDR_ATTR19 IN VARCHAR2,
266 P_HDR_ATTR20 IN VARCHAR2,
267 P_HDR_ATTR21 IN VARCHAR2,
268 P_HDR_ATTR22 IN VARCHAR2,
269 P_HDR_ATTR23 IN VARCHAR2,
270 P_HDR_ATTR24 IN VARCHAR2,
271 P_HDR_ATTR25 IN VARCHAR2,
272 P_HDR_ATTR26 IN VARCHAR2,
273 P_HDR_ATTR27 IN VARCHAR2,
274 P_HDR_ATTR28 IN VARCHAR2,
275 P_HDR_ATTR29 IN VARCHAR2,
276 P_HDR_ATTR30 IN VARCHAR2,
277 P_LINE_ATTR_CATG IN VARCHAR2,
278 P_LINE_ATTR1 IN VARCHAR2,
279 P_LINE_ATTR2 IN VARCHAR2,
280 P_LINE_ATTR3 IN VARCHAR2,
281 P_LINE_ATTR4 IN VARCHAR2,
282 P_LINE_ATTR5 IN VARCHAR2,
283 P_LINE_ATTR6 IN VARCHAR2,
284 P_LINE_ATTR7 IN VARCHAR2,
285 P_LINE_ATTR8 IN VARCHAR2,
286 P_LINE_ATTR9 IN VARCHAR2,
287 P_LINE_ATTR10 IN VARCHAR2,
288 P_LINE_ATTR11 IN VARCHAR2,
289 P_LINE_ATTR12 IN VARCHAR2,
290 P_LINE_ATTR13 IN VARCHAR2,
291 P_LINE_ATTR14 IN VARCHAR2,
292 P_LINE_ATTR15 IN VARCHAR2,
293 P_LINE_ATTR16 IN VARCHAR2,
294 P_LINE_ATTR17 IN VARCHAR2,
295 P_LINE_ATTR18 IN VARCHAR2,
296 P_LINE_ATTR19 IN VARCHAR2,
297 P_LINE_ATTR20 IN VARCHAR2,
298 P_LINE_ATTR21 IN VARCHAR2,
299 P_LINE_ATTR22 IN VARCHAR2,
300 P_LINE_ATTR23 IN VARCHAR2,
301 P_LINE_ATTR24 IN VARCHAR2,
302 P_LINE_ATTR25 IN VARCHAR2,
303 P_LINE_ATTR26 IN VARCHAR2,
304 P_LINE_ATTR27 IN VARCHAR2,
305 P_LINE_ATTR28 IN VARCHAR2,
306 P_LINE_ATTR29 IN VARCHAR2,
307 P_LINE_ATTR30 IN VARCHAR2
308 ) AS
309 l_seq NUMBER;
310 l_batch_status VARCHAR2(15);
311
312 BEGIN
313 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface', 'Procedure Starts');
314
315 ozf_sd_batch_feed_pvt.update_stale_data_batch_line(p_batch_number, p_batch_line_number);
316
317 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface',
318 'WebADI Batch - before inserting into interface table');
319
320 BEGIN
321 INSERT INTO ozf_sd_batch_lines_int_all
322 (batch_line_int_id,
323 frm_contact1_name,
324 frm_contact1_email,
325 frm_contact1_fax,
326 frm_contact1_phone,
327 frm_gbl_ptnr_role_class_code,
328 frm_business_name,
329 frm_gbl_business_id,
330 frm_gbl_supply_chain_code,
331 frm_prop_business_id,
332 frm_prop_domain_id1,
333 frm_prop_id_authority1,
334 frm_gbl_partner_class_code,
335 frm_contact2_name,
336 frm_contact2_email,
337 frm_contact2_fax,
338 frm_contact2_phone,
339 frm_gbl_location_id,
340 frm_prop_domain_id2,
341 frm_prop_id_authority2,
342 frm_prop_location_id,
343 frm_address_line1,
344 frm_address_line2,
345 frm_address_line3,
346 frm_city_name,
347 frm_gbl_country_code,
348 frm_national_postal_code,
349 frm_pobox_id,
350 frm_region_name,
351 gbl_doc_function_code,
352 ship_frm_sd_authorization_id,
353 dist_by_business_name,
354 dist_by_gbl_business_id,
355 dist_by_gbl_supply_chain_code,
356 dist_by_prop_business_id,
357 dist_by_prop_domain_id1,
358 dist_by_prop_idauthority1,
359 dist_by_gbl_partner_class_code,
360 dist_by_contact1_name,
361 dist_by_contact1_email,
362 dist_by_contact1_fax,
363 dist_by_contact1_phone,
364 dist_by_gbl_location_id,
365 dist_by_prop_domain_id2,
366 dist_by_prop_idauthority2,
367 dist_by_prop_location_id,
368 dist_by_address_line1,
369 dist_by_address_line2,
370 dist_by_address_line3,
371 dist_by_city_name,
372 dist_by_gbl_country_code,
373 dist_by_national_postal_code,
374 dist_by_pobox_id,
375 dist_by_region_name,
376 ship_to_business_name,
377 ship_to_gbl_business_id,
378 ship_to_gbl_supply_chain_code,
379 ship_to_prop_business_id,
380 ship_to_prop_domain_id1,
381 ship_to_prop_id_authority1,
382 ship_to_gbl_partner_class_code,
383 ship_to_contact1_name,
384 ship_to_contact1_email,
385 ship_to_contact1_fax,
386 ship_to_contact1_phone,
387 ship_to_gbl_location_id,
388 ship_to_prop_domain_id2,
389 ship_to_prop_id_authority2,
390 ship_to_prop_location_id,
391 ship_to_address_line1,
392 ship_to_address_line2,
393 ship_to_address_line3,
394 ship_to_city_name,
395 ship_to_gbl_country_code,
396 ship_to_national_postal_code,
397 ship_to_pobox_id,
398 ship_to_region_name,
399 sold_to_business_name,
400 sold_to_gbl_business_id,
401 sold_to_gbl_supply_chain_code,
402 sold_to_prop_business_id,
403 sold_to_prop_domain_id1,
404 sold_to_prop_id_authority1,
405 sold_to_gbl_partner_class_code,
406 sold_to_contact1_name,
407 sold_to_contact1_email,
408 sold_to_contact1_fax,
409 sold_to_contact1_phone,
410 sold_to_gbl_location_id,
411 sold_to_prop_domain_id2,
412 sold_to_prop_id_authority2,
413 sold_to_prop_location_id,
414 sold_to_address_line1,
415 sold_to_address_line2,
416 sold_to_address_line3,
417 sold_to_city_name,
418 sold_to_gbl_country_code,
419 sold_to_national_postal_code,
420 sold_to_pobox_id,
421 sold_to_region_name,
422 used_by_business_name,
423 used_by_gbl_business_id,
424 used_by_gbl_supply_chain_code,
425 used_by_prop_business_id,
426 used_by_prop_domain_id1,
427 used_by_prop_id_authority1,
428 used_by_gbl_partner_class_code,
429 used_by_contact1_name,
430 used_by_contact1_email,
431 used_by_contact1_fax,
432 used_by_contact1_phone,
433 used_by_gbl_location_id,
434 used_by_prop_domain_id2,
435 used_by_prop_id_authority2,
436 used_by_prop_location_id,
437 used_by_address_line1,
438 used_by_address_line2,
439 used_by_address_line3,
440 used_by_city_name,
441 used_by_gbl_country_code,
442 used_by_national_postal_code,
443 used_by_pobox_id,
444 used_by_region_name,
445 ship_frm_sd_claim_request_date,
446 ship_frm_sd_claim_request_id,
447 credit_reference_id,
448 debit_reference_id,
449 batch_line_number,
450 order_date,
451 order_line_number,
452 order_number,
453 invoice_date,
454 invoice_line_number,
455 invoice_number,
456 cost_currency_code,
457 cost_monetary_amount,
458 auth_cost_currency_code,
459 auth_cost_monetary_amount,
460 resale_currency_code,
461 resale_monetary_amount,
462 gbl_uom,
463 gbl_claim_disposition_code,
464 vendor_product_id,
465 dist_product_id,
466 ship_date,
467 shipped_quantity,
468 vendor_auth_lineitem_line_nbr,
469 vendor_auth_cost_currency_code,
470 vendor_auth_cost_monetary_amt,
471 vendor_auth_quantity,
472 this_doc_generation_date,
473 this_document_id,
474 to_contact1_name,
475 to_contact1_email,
476 to_contact1_fax,
477 to_contact1_phone,
478 to_gbl_partner_role_class_code,
479 to_business_name1,
480 to_gbl_business_id,
481 to_gbl_supply_chain_code,
482 to_prop_business_id,
483 to_prop_domain_id1,
484 to_prop_id_authority1,
485 to_gbl_partner_class_code,
486 to_contact2_name,
487 to_contact2_email,
488 to_contact2_fax,
489 to_contact2_phone,
490 to_gbl_location_id,
491 to_prop_domain_id2,
492 to_prop_id_authority2,
493 to_prop_location_id,
494 to_address_line1,
495 to_address_line2,
496 to_address_line3,
497 to_city_name,
498 to_gbl_country_code,
499 to_national_postal_code,
500 to_pobox_id,
501 to_region_name,
502 creation_date,
503 last_update_date,
504 last_updated_by,
505 created_by,
506 processed_flag,
507 batch_id,
508 batch_line_id,
509 gbl_claim_rej_code1,
510 gbl_claim_rej_code2,
511 gbl_claim_rej_code3,
512 gbl_claim_rej_code4,
513 gbl_claim_rej_code5,
514 gbl_claim_rej_code6,
515 gbl_claim_rej_code7,
516 gbl_claim_rej_code8,
517 gbl_claim_rej_code9,
518 gbl_claim_rej_code10,
519 header_attribute_category,
520 header_attribute1,
524 header_attribute5,
521 header_attribute2,
522 header_attribute3,
523 header_attribute4,
525 header_attribute6,
526 header_attribute7,
527 header_attribute8,
528 header_attribute9,
529 header_attribute10,
530 header_attribute11,
531 header_attribute12,
532 header_attribute13,
533 header_attribute14,
534 header_attribute15,
535 header_attribute16,
536 header_attribute17,
537 header_attribute18,
538 header_attribute19,
539 header_attribute20,
540 header_attribute21,
541 header_attribute22,
542 header_attribute23,
543 header_attribute24,
544 header_attribute25,
545 header_attribute26,
546 header_attribute27,
547 header_attribute28,
548 header_attribute29,
549 header_attribute30,
550 line_attribute_category,
551 line_attribute1,
552 line_attribute2,
553 line_attribute3,
554 line_attribute4,
555 line_attribute5,
556 line_attribute6,
557 line_attribute7,
558 line_attribute8,
559 line_attribute9,
560 line_attribute10,
561 line_attribute11,
562 line_attribute12,
563 line_attribute13,
564 line_attribute14,
565 line_attribute15,
566 line_attribute16,
567 line_attribute17,
568 line_attribute18,
569 line_attribute19,
570 line_attribute20,
571 line_attribute21,
572 line_attribute22,
573 line_attribute23,
574 line_attribute24,
575 line_attribute25,
576 line_attribute26,
577 line_attribute27,
578 line_attribute28,
579 line_attribute29,
580 line_attribute30
581 )
582 VALUES
583 (ozf_sd_batch_lines_int_all_s.nextval,
584 p_to_cntct1_name,
585 p_to_cntct1_email,
586 p_to_cntct1_fax,
587 p_to_cntct1_phone,
588 p_to_gbl_ptnr_role_class_cd,
589 p_to_business_name,
590 p_to_gbl_business_id,
591 p_to_gbl_supply_chain_cd,
592 p_to_prop_business_id1,
593 p_to_prop_domain_id1,
594 p_to_auth_id1,
595 p_to_gbl_ptnr_class_cd,
596 p_to_cntct2_name,
597 p_to_cntct2_email,
598 p_to_cntct2_fax,
599 p_to_cntct2_phone,
600 p_to_gbl_loc_id,
601 p_to_prop_domain_id2,
602 p_to_prop_auth_id2,
603 p_to_prop_loc_id,
604 p_to_add_line1,
605 p_to_add_line2,
606 p_to_add_line3,
607 p_to_city,
608 p_to_country,
609 p_to_postal_code,
610 p_to_po_box_id,
611 p_to_region,
612 'RESPONSE',
613 p_ship_from_sd_auth_id,
614 p_dist_by_business_name,
615 p_dist_by_gbl_business_id,
616 'Electronic Components', --P_DIST_BY_GBL_SUPP_CHAIN_CD ,
617 p_dist_by_prop_business_id,
618 p_dist_by_prop_domain_id1,
619 p_dist_by_prop_auth_id1,
620 'Distributor', --P_DIST_BY_GBL_PTNR_CLASS_CD ,
621 p_dist_by_cntct_name,
622 p_dist_by_cntct_email,
623 p_dist_by_cntct_fax,
624 p_dist_by_cntct_phone,
625 p_dist_by_gbl_loc_id,
626 p_dist_by_prop_domain_id2,
627 p_dist_by_prop_auth_id2,
628 p_dist_by_prop_loc_id,
629 p_dist_by_add_line1,
630 p_dist_by_add_line2,
631 p_dist_by_add_line3,
632 p_dist_by_city,
633 p_dist_by_country,
634 p_dist_by_postal_code,
635 p_dist_by_po_box_id,
636 p_dist_by_region,
637 p_ship_to_business_name,
638 p_ship_to_gbl_business_id,
639 p_ship_to_gbl_supp_chain_cd,
640 p_ship_to_prop_business_id,
641 p_ship_to_prop_domain_id1,
642 p_ship_to_prop_auth_id1,
643 p_ship_to_gbl_ptnr_class_cd,
644 p_ship_to_cust_cntct_name,
645 p_ship_to_cust_cntct_email,
646 p_ship_to_cust_cntct_fax,
647 p_ship_to_cust_cntct_phone,
648 p_ship_to_cust_gbl_loc_id,
649 p_ship_to_prop_domain_id2,
650 p_ship_to_prop_auth_id2,
651 p_ship_to_cust_prop_loc_id,
652 p_ship_to_cust_add1,
653 p_ship_to_cust_add2,
654 p_ship_to_cust_add3,
655 p_ship_to_cust_city,
656 p_ship_to_cust_country,
657 p_ship_to_cust_postal_code,
658 p_ship_to_cust_po_box_id,
659 p_ship_to_cust_region,
660 p_sold_to_business_name,
661 p_sold_to_gbl_business_id,
662 p_sold_to_gbl_supp_chain_cd,
663 p_sold_to_prop_business_id,
664 p_sold_to_prop_domain_id1,
665 p_sold_to_prop_auth_id1,
666 p_sold_to_gbl_ptnr_class_cd,
667 p_sold_to_cust_cntct_name,
668 p_sold_to_cust_cntct_email,
669 p_sold_to_cust_cntct_fax,
670 p_sold_to_cust_cntct_phone,
671 p_sold_to_cust_gbl_loc_id,
672 p_sold_to_prop_domain_id2,
673 p_sold_to_prop_auth_id2,
674 p_sold_to_cust_prop_loc_id,
675 p_sold_to_cust_add1,
676 p_sold_to_cust_add2,
677 p_sold_to_cust_add3,
678 p_sold_to_cust_city,
679 p_sold_to_cust_country,
680 p_sold_to_cust_postal_code,
681 p_sold_to_cust_po_box_id,
682 p_sold_to_cust_region,
683 p_end_cust_business_name,
684 p_end_cust_gbl_business_id,
685 p_end_cust_gbl_supp_chain_cd,
686 p_end_cust_prop_business_id,
687 p_end_cust_prop_domain_id1,
688 p_end_cust_prop_auth_id1,
689 p_end_cust_gbl_ptnr_class_cd,
690 p_end_cust_cntct_name,
691 p_end_cust_cntct_email,
692 p_end_cust_cntct_fax,
693 p_end_cust_cntct_phone,
694 p_end_cust_gbl_loc_id,
695 p_end_cust_prop_domain_id2,
696 p_end_cust_prop_auth_id2,
700 p_end_cust_add3,
697 p_end_cust_prop_loc_id,
698 p_end_cust_add1,
699 p_end_cust_add2,
701 p_end_cust_city,
702 p_end_cust_country,
703 p_end_cust_postal_code,
704 p_end_cust_po_box_id,
705 p_end_cust_region,
706 p_ship_frm_sd_claim_req_date,
707 p_batch_number, --Inserting batch_number for SHIP_FRM_SD_CLAIM_REQ_ID to be in sync with xml
708 p_credit_ref_id,
709 p_debit_ref_id,
710 p_batch_line_number,
711 p_order_date,
712 p_order_line_number,
713 p_order_number,
714 p_invoice_date,
715 p_invoice_line_number,
716 p_invoice_number,
717 p_cost_price_curr_code,
718 p_cost_price,
719 p_auth_price_curr_code,
720 p_auth_price,
721 p_resale_price_curr_code,
722 p_resale_price,
723 p_uom,
724 p_line_status,
725 p_vendor_part_number,
726 p_dist_part_number,
727 p_date_shipped,
728 p_qty_shipped,
729 p_vendor_auth_line_item_no,
730 p_vendor_apprvd_amt_curr_cd,
731 p_vendor_apprvd_amt,
732 p_vendor_apprvd_qty,
733 p_batch_submission_date,
734 p_batch_number,
735 p_frm_cntct1_name,
736 p_frm_cntct1_email,
737 p_frm_cntct1_fax,
738 p_frm_cntct1_phone,
739 p_frm_gbl_ptnr_role_class_cd,
740 p_frm_business_name,
741 p_frm_gbl_business_id,
742 p_frm_gbl_supply_chain_cd,
743 p_frm_prop_business_id1,
744 p_frm_prop_domain_id1,
745 p_frm_auth_id1,
746 p_frm_gbl_ptnr_class_cd,
747 p_frm_cntct2_name,
748 p_frm_cntct2_email,
749 p_frm_cntct2_fax,
750 p_frm_cntct2_phone,
751 p_frm_gbl_loc_id,
752 p_frm_prop_domain_id2,
753 p_frm_prop_auth_id2,
754 p_frm_prop_loc_id,
755 p_frm_add_line1,
756 p_frm_add_line2,
757 p_frm_add_line3,
758 p_frm_city,
759 p_frm_country,
760 p_frm_postal_code,
761 p_frm_po_box_id,
762 p_frm_region,
763 sysdate,
764 sysdate,
765 0,
766 0,
767 'N',
768 p_batch_id,
769 p_batch_line_id,
770 p_disposition_code1,
771 p_disposition_code2,
772 p_disposition_code3,
773 p_disposition_code4,
774 p_disposition_code5,
775 p_disposition_code6,
776 p_disposition_code7,
777 p_disposition_code8,
778 p_disposition_code9,
779 p_disposition_code10,
780 P_HDR_ATTR_CATG,
781 P_HDR_ATTR1,
782 P_HDR_ATTR2,
783 P_HDR_ATTR3,
784 P_HDR_ATTR4,
785 P_HDR_ATTR5,
786 P_HDR_ATTR6,
787 P_HDR_ATTR7,
788 P_HDR_ATTR8,
789 P_HDR_ATTR9,
790 P_HDR_ATTR10,
791 P_HDR_ATTR11,
792 P_HDR_ATTR12,
793 P_HDR_ATTR13,
794 P_HDR_ATTR14,
795 P_HDR_ATTR15,
796 P_HDR_ATTR16,
797 P_HDR_ATTR17,
798 P_HDR_ATTR18,
799 P_HDR_ATTR19,
800 P_HDR_ATTR20,
801 P_HDR_ATTR21,
802 P_HDR_ATTR22,
803 P_HDR_ATTR23,
804 P_HDR_ATTR24,
805 P_HDR_ATTR25,
806 P_HDR_ATTR26,
807 P_HDR_ATTR27,
808 P_HDR_ATTR28,
809 P_HDR_ATTR29,
810 P_HDR_ATTR30,
811 P_LINE_ATTR_CATG,
812 P_LINE_ATTR1,
813 P_LINE_ATTR2,
814 P_LINE_ATTR3,
815 P_LINE_ATTR4,
816 P_LINE_ATTR5,
817 P_LINE_ATTR6,
818 P_LINE_ATTR7,
819 P_LINE_ATTR8,
820 P_LINE_ATTR9,
821 P_LINE_ATTR10,
822 P_LINE_ATTR11,
823 P_LINE_ATTR12,
824 P_LINE_ATTR13,
825 P_LINE_ATTR14,
826 P_LINE_ATTR15,
827 P_LINE_ATTR16,
828 P_LINE_ATTR17,
829 P_LINE_ATTR18,
830 P_LINE_ATTR19,
831 P_LINE_ATTR20,
832 P_LINE_ATTR21,
833 P_LINE_ATTR22,
834 P_LINE_ATTR23,
835 P_LINE_ATTR24,
836 P_LINE_ATTR25,
837 P_LINE_ATTR26,
838 P_LINE_ATTR27,
839 P_LINE_ATTR28,
840 P_LINE_ATTR29,
841 P_LINE_ATTR30);
842
843 EXCEPTION
844 WHEN others THEN
845 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface', 'Exception: '||sqlerrm);
846 END;
847
848 COMMIT;
849 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface', 'Procedure Ends');
850
851 END populate_interface;
852
853 PROCEDURE update_data(p_batch_number IN VARCHAR2,
854 x_return_status OUT nocopy VARCHAR2,
855 x_msg_data OUT nocopy VARCHAR2
856 ) IS
857 l_cnt_hdr NUMBER;
858 l_cnt_line NUMBER;
859 l_cnt_tot_line NUMBER;
860 l_cnt_approved_lines NUMBER;
861 l_cnt_rejected_lines NUMBER;
862 l_message VARCHAR2(500);
863 l_to_amount NUMBER;
864 l_claim_number VARCHAR2(30) := NULL;
865 l_accepted_amount NUMBER;
866 l_batch_id NUMBER;
867 l_batch_status VARCHAR2(15);
868 l_claim_id NUMBER;
869 l_claim_minor_version NUMBER;
870 l_currency_code VARCHAR2(15);
871 l_msg_count NUMBER;
872 l_org_id NUMBER;
873 l_ssd_dec_adj_type_id NUMBER;
874 l_ssd_inc_adj_type_id NUMBER;
875 l_tot_app_claim_amt NUMBER ;
876 l_func_currency VARCHAR2(15);
877 l_batch_currency VARCHAR2(15);
878
879 --Start: Fix for Bug 10628160
880 l_custom_setup_id NUMBER := NULL;
881 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
882 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
883 l_split_claim_id NUMBER := NULL;
884 l_msg_count3 NUMBER := NULL;
885 l_msg_data VARCHAR2(100) := NULL;
886 l_msg_count2 NUMBER := NULL;
887 l_msg_data2 VARCHAR2(100) := NULL;
888 l_return_status2 VARCHAR2(15) := NULL;
889 l_return_status VARCHAR2(15) := NULL;
890 --End: Fix for Bug 10628160
891 --Start: Added for partial approval (ER 13245462)
892 l_batch_comp_appr_lines_tbl number_tbl;
893 l_batch_und_appr_lines_tbl number_tbl;
894 l_batch_over_appr_lines_tbl number_tbl;
895 l_batch_appr_lines_tbl number_tbl;
896 l_batch_comp_rej_lines_tbl number_tbl;
897 l_cnt_over_appr_lines NUMBER :=0;
898 l_cnt_under_appr_lines NUMBER :=0;
899 l_cnt_comp_appr_lines NUMBER :=0;
900 l_cnt_comp_rej_lines NUMBER :=0;
901 l_imd_claim_flag VARCHAR2(1) :='N';
902 l_cnt_appr_lines NUMBER :=0;
903 --End: Added for partial approval (ER 13245462)
904
905
906
907 CURSOR C_MISS_REJ_APP IS
908 SELECT batch_line_number,
909 auth_cost,
910 auth_curr_code,
911 stat_code,
912 credit_reference_id,
913 vendor_auth_quantity,
914 adjustment_type,
915 ozf_utility_pvt.currround(CASE
916 WHEN((auth_cost is null and vendor_auth_quantity is null) OR ( auth_curr_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR auth_cost < 0 OR (original_claim_amount > 0 and vendor_auth_quantity < 0) ) then 0
917
918 WHEN (auth_cost is null and vendor_auth_quantity IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * vendor_auth_quantity)
919
920 WHEN (auth_cost is null and vendor_auth_quantity IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * vendor_auth_quantity)
921
922 WHEN (vendor_auth_quantity is null and auth_cost is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - auth_cost) * QUANTITY_SHIPPED)
923
924 WHEN (vendor_auth_quantity is null and auth_cost is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (auth_cost * QUANTITY_SHIPPED)
925
926 WHEN (vendor_auth_quantity is not null and auth_cost is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - auth_cost) * vendor_auth_quantity)
927 WHEN (vendor_auth_quantity is not null and auth_cost is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((auth_cost) * vendor_auth_quantity)
928 END,claim_amount_currency_code) approved_claim_amount,
929 line_status_code,
930 computed_batch_curr_claim_amt,
931 claim_amount,
932 batch_line_id,
933 original_claim_amount,
934 claim_amount_currency_code
935 FROM(
936 SELECT bint.batch_line_number batch_line_number,
937 DECODE(gbl_claim_disposition_code,'APPROVED', DECODE(discount_type, 'AMT' , discount_value , agreement_price), vendor_auth_cost_monetary_amt) auth_cost,
938 DECODE(gbl_claim_disposition_code,'APPROVED', DECODE(discount_type, 'AMT' , discount_currency_code, Agreement_currency_code), vendor_auth_cost_currency_code) auth_curr_code,
939 DECODE(NVL(gbl_claim_disposition_code,'REJECTED'), 'APPROVED', 'APPROVED', 'REJECTED') stat_code,
940 credit_reference_id,
941 DECODE(gbl_claim_disposition_code,'APPROVED',quantity_shipped , vendor_auth_quantity) vendor_auth_quantity,
942 clm.adjustment_type,
943 discount_type,
944 discount_value,
945 list_price,
946 agreement_price,
947 claim_amount_currency_code,
948 quantity_shipped,
949 gbl_claim_disposition_code line_status_code,
950 lines.claim_amount,
951 original_claim_amount,
952 lines.batch_line_id,
953 CASE
954 WHEN (lines.claim_amount_currency_code = l_batch_currency) THEN lines.BATCH_CURR_CLAIM_AMOUNT
955 WHEN ((lines.claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(lines.claim_amount_currency_code,
956 l_batch_currency,
957 l_func_currency,
958 (SELECT fu.exchange_rate_type
959 FROM ozf_funds_utilized_all_b fu
960 WHERE fu.utilization_id = lines.utilization_id
961 AND lines.batch_id = p_batch_number),
962 NULL,
963 sysdate,
964 lines.claim_amount)
965
966 WHEN (lines.claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(lines.claim_amount_currency_code,
967 l_batch_currency,
968 l_func_currency,
969 (SELECT fu.exchange_rate_type
970 FROM ozf_funds_utilized_all_b fu
971 WHERE fu.utilization_id = lines.utilization_id
972 AND lines.batch_id = p_batch_number),
973 NULL,
974 (SELECT fu.exchange_rate_date
975 FROM ozf_funds_utilized_all_b fu
976 WHERE fu.utilization_id = lines.utilization_id
977 AND lines.batch_id = p_batch_number),
978 lines.claim_amount)
979 END computed_batch_curr_claim_amt
980 FROM ozf_sd_batch_lines_int_all bint, ozf_sd_batch_lines_all lines, ozf_claim_types_all_vl clm
981 WHERE ship_frm_sd_claim_request_id = p_batch_number
982 AND processed_flag = 'N'
983 AND bint.ship_frm_sd_claim_request_id = lines.batch_id
984 AND bint.batch_line_number = lines.batch_line_number
985 AND lines.adjustment_type_id = clm.claim_type_id(+)
986 ) ;
987
988 BEGIN
989
990 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'Procedure Starts');
991
992 x_return_status := fnd_api.g_ret_sts_success;
993
994 --Retreive batch id
995 BEGIN
996 SELECT batch_id, status_code, claim_minor_version, org_id, currency_code,NVL(imd_claim_flag,'N')
997 INTO l_batch_id, l_batch_status, l_claim_minor_version, l_org_id, l_batch_currency,l_imd_claim_flag
998 FROM ozf_sd_batch_headers_all
999 WHERE batch_number = p_batch_number;
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND THEN
1002 UPDATE ozf_sd_batch_lines_int_all
1003 SET validation_txt = 'Batch ID does not exist',
1004 last_update_date = sysdate,
1005 last_updated_by = fnd_global.user_id
1006 WHERE ship_frm_sd_claim_request_id = p_batch_number
1007 AND processed_flag = 'N';
1008 COMMIT;
1009 x_return_status := fnd_api.g_ret_sts_error;
1010 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_BATCH_INVALID');
1011 RETURN;
1012 WHEN OTHERS THEN
1013 x_return_status := fnd_api.g_ret_sts_error;
1014 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_BATCH_INVALID');
1015 RETURN;
1016 END;
1017
1018 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
1019 'Batch Id: '||l_batch_id||' and Batch Status: '||l_batch_status);
1020
1021 --raising business event
1022 ozf_sd_util_pvt.sd_raise_event (l_batch_id, 'RESPONSE', x_return_status);
1023
1024 IF l_batch_status NOT IN ('WIP','SUBMITTED' ) THEN
1025 UPDATE ozf_sd_batch_lines_int_all
1026 SET processed_flag = 'E',
1027 validation_txt = 'Batch not in WIP or SUBMITTED status',
1028 last_update_date = sysdate,
1029 last_updated_by = fnd_global.user_id
1030 WHERE ship_frm_sd_claim_request_id = p_batch_number
1031 AND processed_flag = 'N';
1032 COMMIT;
1033 x_return_status := fnd_api.g_ret_sts_error;
1034 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_BATCH_STATUS_INVALID');
1035 RETURN;
1036 END IF;
1037
1038 --check whether lines exist for given batch id
1039 SELECT COUNT(1)
1040 INTO l_cnt_line
1041 FROM ozf_sd_batch_lines_int_all
1042 WHERE ship_frm_sd_claim_request_id = p_batch_number
1043 AND processed_flag = 'N';
1044
1045 IF l_cnt_line = 0 THEN
1046 UPDATE ozf_sd_batch_lines_int_all
1047 SET validation_txt = 'There are no Lines for this Batch ID',
1048 last_update_date = sysdate,
1049 last_updated_by = fnd_global.user_id
1050 WHERE ship_frm_sd_claim_request_id = p_batch_number
1051 AND processed_flag = 'N';
1055 RETURN;
1052 COMMIT;
1053 x_return_status := fnd_api.g_ret_sts_error;
1054 x_msg_data := 'There are no Lines for this Batch ID';
1056 END IF;
1057
1058 SELECT gs.currency_code
1059 INTO l_func_currency
1060 FROM gl_sets_of_books gs,
1061 ozf_sys_parameters_all org,
1062 ozf_sd_batch_headers_all bh
1063 WHERE org.set_of_books_id = gs.set_of_books_id
1064 AND org.org_id = bh.org_id
1065 AND bh.batch_number = p_batch_number;
1066
1067
1068 BEGIN
1069 DELETE ozf_sd_batch_line_disputes
1070 WHERE batch_id = l_batch_id
1071 AND batch_line_id IN (SELECT bl.batch_line_id
1072 FROM ozf_sd_batch_lines_all bl, ozf_sd_batch_lines_int_all intr
1073 WHERE bl.batch_id = l_batch_id
1074 AND bl.batch_id = intr.ship_frm_sd_claim_request_id
1075 AND bl.batch_line_number = intr.batch_line_number
1076 AND intr.processed_flag = 'N'
1077 AND bl.purge_flag <> 'Y'
1078 );
1079 EXCEPTION
1080 WHEN others THEN
1081 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
1082 'Exception Delete OZF_SD_BATCH_LINE_DISPUTES: '||sqlerrm);
1083 END;
1084
1085 INSERT INTO ozf_sd_batch_line_disputes
1086 (batch_line_dispute_id,
1087 batch_id,
1088 batch_line_id,
1089 object_version_number,
1090 dispute_code,
1091 review_flag,
1092 creation_date,
1093 last_update_date,
1094 last_updated_by,
1095 request_id,
1096 created_by,
1097 created_from,
1098 last_update_login,
1099 program_application_id,
1100 program_update_date,
1101 program_id,
1102 security_group_id)
1103
1104 SELECT ozf_sd_batch_line_disputes_s.nextval,
1105 a.batch_id,
1106 a.batch_line_id,
1107 1,
1108 a.dispute_code,
1109 'N',
1110 sysdate,
1111 sysdate,
1112 fnd_global.user_id,
1113 fnd_global.conc_request_id,
1114 fnd_global.user_id,
1115 NULL, --created from
1116 fnd_global.conc_login_id,
1117 fnd_global.prog_appl_id,
1118 NULL, --l_program_update_date,
1119 fnd_global.conc_program_id,
1120 fnd_global.security_group_id
1121
1122 FROM -- No rejection code for rejected line
1123 ( SELECT intr.batch_id,
1124 intr.batch_line_id,
1125 'OZF_SD_MISSING_REJ_CODE' dispute_code
1126 FROM ozf_sd_batch_lines_int_all intr,
1127 ozf_sd_batch_lines_all bl
1128 WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
1129 AND intr.processed_flag = 'N'
1130 AND bl.purge_flag <> 'Y'
1131 AND intr.gbl_claim_disposition_code = 'REJECTED'
1132 AND intr.ship_frm_sd_claim_request_id = bl.batch_id
1133 AND intr.batch_line_number = bl.batch_line_number
1134 AND intr.gbl_claim_rej_code1 IS NULL
1135 AND intr.gbl_claim_rej_code2 IS NULL
1136 AND intr.gbl_claim_rej_code3 IS NULL
1137 AND intr.gbl_claim_rej_code4 IS NULL
1138 AND intr.gbl_claim_rej_code5 IS NULL
1139 AND intr.gbl_claim_rej_code6 IS NULL
1140 AND intr.gbl_claim_rej_code7 IS NULL
1141 AND intr.gbl_claim_rej_code8 IS NULL
1142 AND intr.gbl_claim_rej_code9 IS NULL
1143 AND intr.gbl_claim_rej_code10 IS NULL
1144
1145 UNION ALL -- missing or invalid status for batch lines
1146
1147 SELECT intr.batch_id,
1148 intr.batch_line_id,
1149 'OZF_SD_NO_RESPONSE' dispute_code
1150 FROM ozf_sd_batch_lines_int_all intr
1151 WHERE ship_frm_sd_claim_request_id = p_batch_number
1152 AND processed_flag = 'N'
1153 AND ( (gbl_claim_disposition_code IS NULL) OR ( gbl_claim_disposition_code NOT IN ('APPROVED', 'REJECTED') ) )
1154
1155
1156 UNION ALL -- Currency code mismatch
1157
1158 SELECT intr.batch_id,
1159 intr.batch_line_id,
1160 'OZF_SD_CURR_CODE_MISMATCH' dispute_code
1161 FROM ozf_sd_batch_lines_int_all intr,
1162 ozf_sd_batch_lines_all bl
1163 WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
1164 AND intr.processed_flag = 'N'
1165 AND bl.purge_flag <> 'Y'
1166 AND intr.gbl_claim_disposition_code = 'REJECTED'
1167 AND intr.ship_frm_sd_claim_request_id = bl.batch_id
1168 AND intr.batch_line_number = bl.batch_line_number
1169 AND bl.claim_amount_currency_code <> intr.vendor_auth_cost_currency_code
1170
1171 UNION ALL -- VENDOR_AUTH_COST_MONETARY_AMT CAN NOT BE NEGATIVE
1172
1173 SELECT intr.batch_id,
1174 intr.batch_line_id,
1175 'OZF_SD_VENDOR_AUTH_AMT_NGTVE' dispute_code
1176 FROM ozf_sd_batch_lines_int_all intr,
1177 ozf_sd_batch_lines_all bl
1178 WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
1179 AND intr.processed_flag = 'N'
1180 AND bl.purge_flag <> 'Y'
1181 AND intr.gbl_claim_disposition_code = 'REJECTED'
1182 AND intr.ship_frm_sd_claim_request_id = bl.batch_id
1183 AND intr.batch_line_number = bl.batch_line_number
1184 AND intr.vendor_auth_cost_monetary_amt < 0
1185
1186 UNION ALL -- VENDOR_AUTH_QTY CAN NOT BE NEGATIVE
1187
1188 SELECT intr.batch_id,
1189 intr.batch_line_id,
1190 'OZF_SD_VENDOR_AUTH_QTY_NGTVE' dispute_code
1191 FROM ozf_sd_batch_lines_int_all intr,
1192 ozf_sd_batch_lines_all bl
1193 WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
1194 AND intr.processed_flag = 'N'
1195 AND bl.purge_flag <> 'Y'
1199 AND bl.original_claim_amount > 0 -- only for non RMA lines
1196 AND intr.gbl_claim_disposition_code = 'REJECTED'
1197 AND intr.ship_frm_sd_claim_request_id = bl.batch_id
1198 AND intr.batch_line_number = bl.batch_line_number
1200 AND intr.vendor_auth_quantity < 0
1201
1202 UNION ALL -- VENDOR_AUTH_COST_MONETARY_AMT is NULL and VENDOR_AUTH_QTY is NULL
1203
1204 SELECT intr.batch_id,
1205 intr.batch_line_id,
1206 'OZF_SD_AUTH_AMT_QTY_NULL' dispute_code
1207 FROM ozf_sd_batch_lines_int_all intr,
1208 ozf_sd_batch_lines_all bl
1209 WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
1210 AND intr.processed_flag = 'N'
1211 AND bl.purge_flag <> 'Y'
1212 AND intr.gbl_claim_disposition_code = 'REJECTED'
1213 AND intr.ship_frm_sd_claim_request_id = bl.batch_id
1214 AND intr.batch_line_number = bl.batch_line_number
1215 AND intr.vendor_auth_cost_monetary_amt is NULL
1216 AND intr.vendor_auth_quantity is NULL
1217
1218 ) a ;
1219
1220 update_dispute_data(p_batch_number,l_batch_id);
1221
1222 SELECT SSD_DEC_ADJ_TYPE_ID, SSD_INC_ADJ_TYPE_ID
1223 INTO l_ssd_dec_adj_type_id, l_ssd_inc_adj_type_id
1224 FROM OZF_SYS_PARAMETERS_ALL
1225 WHERE org_id = l_org_id;
1226
1227 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' SSD Batch batch id'||l_batch_id||' allow immediate claim flag '||l_imd_claim_flag);
1228
1229
1230 IF l_imd_claim_flag ='Y' THEN
1231
1232 FOR V_MISS_REJ_APP_REC IN C_MISS_REJ_APP
1233 LOOP
1234 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.batch_line_number'||V_MISS_REJ_APP_REC.batch_line_number);
1235
1236
1237 UPDATE ozf_sd_batch_lines_all
1238 SET status_code = V_MISS_REJ_APP_REC.stat_code,
1239 approved_amount = V_MISS_REJ_APP_REC.auth_cost,
1240 approved_currency_code = V_MISS_REJ_APP_REC.auth_curr_code,
1241 object_version_number = object_version_number + 1,
1242 last_update_date = sysdate,
1243 last_updated_by = fnd_global.user_id,
1244 vendor_ref_id = V_MISS_REJ_APP_REC.credit_reference_id,
1245 quantity_approved = V_MISS_REJ_APP_REC.vendor_auth_quantity,
1246 total_approved_amt = CASE WHEN V_MISS_REJ_APP_REC.stat_code ='REJECTED' THEN NVL(total_approved_amt,0) + V_MISS_REJ_APP_REC.approved_claim_amount
1247 WHEN V_MISS_REJ_APP_REC.stat_code ='APPROVED' THEN total_approved_amt + V_MISS_REJ_APP_REC.approved_claim_amount
1248 END,
1249 total_approved_qty = CASE WHEN V_MISS_REJ_APP_REC.stat_code ='REJECTED' THEN NVL(total_approved_qty,0) + V_MISS_REJ_APP_REC.vendor_auth_quantity
1250 WHEN V_MISS_REJ_APP_REC.stat_code ='APPROVED' THEN total_approved_qty + V_MISS_REJ_APP_REC.vendor_auth_quantity
1251 END,
1252
1253 batch_curr_claim_amount = decode(V_MISS_REJ_APP_REC.stat_code,'APPROVED',V_MISS_REJ_APP_REC.computed_batch_curr_claim_amt, batch_curr_claim_amount),
1254 adjustment_type_id = (case when ( (original_claim_amount > 0)
1255 AND (V_MISS_REJ_APP_REC.approved_claim_amount > original_claim_amount)
1256 AND V_MISS_REJ_APP_REC.adjustment_type <> 'STANDARD'
1257 AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
1258 THEN l_ssd_inc_adj_type_id
1259 when ( (original_claim_amount > 0)
1260 AND (V_MISS_REJ_APP_REC.approved_claim_amount < original_claim_amount)
1261 AND V_MISS_REJ_APP_REC.adjustment_type <> 'DECREASE_EARNED'
1262 AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
1263 THEN l_ssd_dec_adj_type_id
1264 else
1265 adjustment_type_id
1266 end)
1267 WHERE batch_id = l_batch_id
1268 AND batch_line_number = V_MISS_REJ_APP_REC.batch_line_number
1269 AND purge_flag <> 'Y'
1270 AND status_code = 'SUBMITTED';
1271
1272 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.stat_code'||V_MISS_REJ_APP_REC.stat_code);
1273 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.auth_cost'||V_MISS_REJ_APP_REC.auth_cost);
1274 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.vendor_auth_quantity'||V_MISS_REJ_APP_REC.vendor_auth_quantity);
1275 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.claim_amount_currency_code'||V_MISS_REJ_APP_REC.claim_amount_currency_code);
1276 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.auth_curr_code'||V_MISS_REJ_APP_REC.auth_curr_code);
1277 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.approved_claim_amount'||V_MISS_REJ_APP_REC.approved_claim_amount);
1278 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.claim_amount'||V_MISS_REJ_APP_REC.claim_amount);
1279
1280
1281
1282 --Bucketing under approved,over approved,completely rejected and completely approved lines(Rejected line but completely approved)
1283 IF(V_MISS_REJ_APP_REC.stat_code = 'REJECTED' ) THEN
1284 -- For System disputes code
1285 If(V_MISS_REJ_APP_REC.auth_cost < 0) OR (V_MISS_REJ_APP_REC.original_claim_amount > 0 AND V_MISS_REJ_APP_REC.vendor_auth_quantity < 0)
1286 OR (V_MISS_REJ_APP_REC.vendor_auth_quantity IS NULL AND V_MISS_REJ_APP_REC.auth_cost IS NULL )
1287 OR (V_MISS_REJ_APP_REC.line_status_code IS NULL) OR (V_MISS_REJ_APP_REC.line_status_code NOT IN ('APPROVED', 'REJECTED'))
1288 OR (V_MISS_REJ_APP_REC.claim_amount_currency_code <> V_MISS_REJ_APP_REC.auth_curr_code) THEN
1289 l_batch_comp_rej_lines_tbl(l_cnt_comp_rej_lines) := V_MISS_REJ_APP_REC.batch_line_id;
1290 l_cnt_comp_rej_lines := l_cnt_comp_rej_lines + 1;
1291 ELSE IF( ABS(V_MISS_REJ_APP_REC.approved_claim_amount) > ABS(V_MISS_REJ_APP_REC.claim_amount)) THEN
1292 l_batch_over_appr_lines_tbl(l_cnt_over_appr_lines) := V_MISS_REJ_APP_REC.batch_line_id; -- Over approval scenario
1293 l_cnt_over_appr_lines := l_cnt_over_appr_lines + 1;
1294 ELSE IF ( ABS(V_MISS_REJ_APP_REC.approved_claim_amount) < ABS(V_MISS_REJ_APP_REC.claim_amount)) THEN
1295 l_batch_und_appr_lines_tbl(l_cnt_under_appr_lines) := V_MISS_REJ_APP_REC.batch_line_id; -- under approval scenario
1296 l_cnt_under_appr_lines := l_cnt_under_appr_lines + 1;
1297 ELSE IF (ABS(V_MISS_REJ_APP_REC.approved_claim_amount) = ABS(V_MISS_REJ_APP_REC.claim_amount)) THEN
1298 l_batch_comp_appr_lines_tbl(l_cnt_comp_appr_lines) := V_MISS_REJ_APP_REC.batch_line_id; -- complete approval scenario
1299 l_cnt_comp_appr_lines := l_cnt_comp_appr_lines + 1;
1300 END IF; --Completely approved end if
1301 END IF; -- Under approved end if
1302 END IF; -- Over approved end if
1303 END IF; -- Handling system disputes codes
1304 ELSE IF (V_MISS_REJ_APP_REC.stat_code = 'APPROVED') THEN
1305 l_batch_appr_lines_tbl(l_cnt_appr_lines) := V_MISS_REJ_APP_REC.batch_line_id;
1306 l_cnt_appr_lines := l_cnt_appr_lines +1;
1307 END IF;
1308 END IF; --End If for Line Status is REJECTED
1309
1310 END LOOP ;
1311
1312 --get total number of lines
1313 SELECT COUNT(1)
1314 INTO l_cnt_tot_line
1315 FROM ozf_sd_batch_lines_all
1316 WHERE batch_id = l_batch_id
1317 AND purge_flag <> 'Y';
1318
1319 --get number of approved lines
1320 SELECT COUNT(1)
1321 INTO l_cnt_approved_lines
1322 FROM ozf_sd_batch_lines_all
1323 WHERE batch_id = l_batch_id
1324 AND status_code = 'APPROVED'
1325 AND purge_flag <> 'Y';
1326
1327 --get number of rejected lines
1328 SELECT COUNT(1)
1329 INTO l_cnt_rejected_lines
1330 FROM ozf_sd_batch_lines_all
1331 WHERE batch_id = l_batch_id
1332 AND status_code = 'REJECTED'
1333 AND purge_flag <> 'Y';
1334
1335
1336
1337 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of total lines: '||l_cnt_tot_line);
1338 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of rejected lines: '||l_cnt_rejected_lines);
1339 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of approved lines: '||l_cnt_approved_lines);
1340 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of completely rejected lines: '||l_cnt_comp_rej_lines);
1341 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of completely approved lines: '||l_cnt_comp_appr_lines);
1342 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of under approved lines: '||l_cnt_under_appr_lines);
1343 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of over approved lines: '||l_cnt_over_appr_lines);
1344 -- Logmessage to print partial approval flag for a batch
1345
1346 -- If partial approval is allowed for a batch
1347 --All lines are rejected or if none approved
1348 IF ( ( l_cnt_rejected_lines = l_cnt_tot_line) OR ( l_cnt_approved_lines = 0 ) ) THEN
1349
1350 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if block All lines are rejected or if none approved');
1351 --If all lines are over approved or completely approved
1352 IF ( NVL(l_cnt_comp_appr_lines,0) + NVL(l_cnt_over_appr_lines,0) = l_cnt_tot_line ) THEN
1353
1354 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if block All lines are over approved or completely approved');
1355
1356 UPDATE ozf_sd_batch_headers_all
1357 SET status_code = 'APPROVED',
1358 claim_minor_version = NULL,
1359 object_version_number = object_version_number + 1,
1360 last_update_date = sysdate,
1361 last_updated_by = fnd_global.user_id
1362 WHERE batch_id = l_batch_id;
1363
1364 --Mark all rows as processed
1365 UPDATE ozf_sd_batch_lines_int_all
1366 SET processed_flag = 'Y',
1367 last_update_date = sysdate,
1368 last_updated_by = fnd_global.user_id
1369 WHERE ship_frm_sd_claim_request_id = p_batch_number
1370 AND processed_flag = 'N';
1371
1372 -- Update status_code,claim amount,batch_curr_claim_amount and quantity_shipped for over approved lines
1373 --For batch Currency Claim Amount 2 step Conversion i.e accrual date to sysdate is taken care by OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY API
1374 UPDATE ozf_sd_batch_lines_all
1375 SET status_code = 'APPROVED',
1376 claim_amount = ozf_utility_pvt.currround(CASE
1377 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1378
1379 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1380
1381 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1382
1383 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1384
1385 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1386
1387 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1388 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1389 ELSE 0
1390 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
1391 batch_curr_claim_amount = CASE
1392 WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
1393 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1394
1395 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1396
1397 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1398
1399 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1400
1401 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1402
1403 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1404 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1405 ELSE 0
1406 END,CLAIM_AMOUNT_CURRENCY_CODE)
1407 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1408 l_batch_currency,
1409 l_func_currency,
1410 (SELECT fu.exchange_rate_type
1411 FROM ozf_funds_utilized_all_b fu
1412 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1413 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1414 NULL,
1415 sysdate,
1416 CASE
1417 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1418
1419 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1420
1421 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1422
1423 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1424
1425 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1426
1427 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1428 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1429 END)
1430
1431 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1432 l_batch_currency,
1433 l_func_currency,
1434 (SELECT fu.exchange_rate_type
1435 FROM ozf_funds_utilized_all_b fu
1436 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1437 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1438 NULL,
1439 (SELECT fu.exchange_rate_date
1440 FROM ozf_funds_utilized_all_b fu
1441 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1442 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1443 CASE
1444 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1445
1446 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1447
1448 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1452 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1449
1450 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1451
1453
1454 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1455 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1456 END)
1457 END,
1458 quantity_shipped = NVL(quantity_approved,quantity_shipped),
1459 discount_value = decode(discount_type,
1460 'AMT',NVL(approved_amount,discount_value),
1461 'NEWPRICE',NVL(approved_amount,agreement_price),
1462 '%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
1463 agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
1464 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
1465 ELSE total_approved_qty
1466 END,
1467 object_version_number = object_version_number + 1,
1468 last_update_date = sysdate,
1469 last_updated_by = fnd_global.user_id
1470 WHERE batch_id = l_batch_id
1471 AND purge_flag <> 'Y' ;
1472
1473
1474
1475 PROCESS_COMP_APPR_BATCH(l_batch_id, x_return_status, x_msg_data);
1476
1477
1478 COMMIT;
1479 RETURN;
1480
1481
1482 --If there is atleast over approved line or one under approved line or completely approved line then process_child_batch is invoked
1483 ELSE IF ( (l_cnt_under_appr_lines >= 1) OR (l_cnt_over_appr_lines >= 1 ) OR (l_cnt_comp_appr_lines >=1)) THEN
1484
1485 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if block atleast one approved or under approved line');
1486 -- Invoke Child batch creation API
1487 PROCESS_CHILD_BATCH(l_batch_id,l_batch_appr_lines_tbl,l_batch_und_appr_lines_tbl,l_batch_over_appr_lines_tbl,l_batch_comp_appr_lines_tbl, x_return_status, x_msg_data);
1488
1489 --set batch header as WIP for parent batch
1490 UPDATE ozf_sd_batch_headers_all
1491 SET status_code = 'WIP',
1492 object_version_number = object_version_number + 1,
1493 claim_minor_version = l_claim_minor_version + 1,
1494 last_update_date = sysdate,
1495 last_updated_by = fnd_global.user_id
1496 WHERE batch_id = l_batch_id;
1497
1498 --Mark all rows as processed
1499 UPDATE ozf_sd_batch_lines_int_all
1500 SET processed_flag = 'Y',
1501 last_update_date = sysdate,
1502 last_updated_by = fnd_global.user_id
1503 WHERE ship_frm_sd_claim_request_id = p_batch_number
1504 AND processed_flag = 'N';
1505
1506 --Update the following fields for the under approved batch lines
1507 -- batch_curr_claim_amount,agreement_price(for discount type %,New Price) and discount value for discount type amount,processed_flag
1508 -- Status code of the under approved line has to be set as partially approved
1509 IF((l_batch_und_appr_lines_tbl IS NOT NULL) AND (l_batch_und_appr_lines_tbl.COUNT > 0)) THEN
1510
1511 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if l_batch_und_appr_lines_tbl is not null ');
1512
1513 FOR i IN 0..l_batch_und_appr_lines_tbl.COUNT-1
1514 LOOP
1515
1516 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if l_batch_und_appr_lines_tbl(i) '||l_batch_und_appr_lines_tbl(i));
1517
1518 UPDATE ozf_sd_batch_lines_all
1519 SET status_code = 'PARTIALLY_APPROVED',
1520 claim_amount = claim_amount - ozf_utility_pvt.currround(CASE
1521 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1522
1523 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1524
1525 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1526
1527 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1528
1529 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1530
1531 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1532 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1533 ELSE 0
1534 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
1535 batch_curr_claim_amount = CASE
1536 WHEN (claim_amount_currency_code = l_batch_currency) THEN ( claim_amount - ozf_utility_pvt.currround(CASE
1537 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1541 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1538
1539 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1540
1542
1543 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1544
1545 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1546
1547 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1548 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1549 ELSE 0
1550 END,CLAIM_AMOUNT_CURRENCY_CODE))
1551 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1552 l_batch_currency,
1553 l_func_currency,
1554 (SELECT fu.exchange_rate_type
1555 FROM ozf_funds_utilized_all_b fu
1556 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1557 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1558 NULL,
1559 sysdate,
1560 claim_amount - ozf_utility_pvt.currround(CASE
1561 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1562
1563 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1564
1565 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1566
1567 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1568
1569 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1570
1571 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1572 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1573 ELSE 0
1574 END,CLAIM_AMOUNT_CURRENCY_CODE))
1575
1576 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1577 l_batch_currency,
1578 l_func_currency,
1579 (SELECT fu.exchange_rate_type
1580 FROM ozf_funds_utilized_all_b fu
1581 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1582 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1583 NULL,
1584 (SELECT fu.exchange_rate_date
1585 FROM ozf_funds_utilized_all_b fu
1586 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1587 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1588 claim_amount - ozf_utility_pvt.currround(CASE
1589 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1590
1591 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1592
1593 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1594
1595 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1596
1597 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1598
1599 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1600 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1601 ELSE 0
1602 END,CLAIM_AMOUNT_CURRENCY_CODE))
1603 END,
1604 quantity_shipped = CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
1605 ELSE quantity_shipped - NVL(quantity_approved,0)
1606 END,
1610 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1607 agreement_price = OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC( CASE WHEN ( discount_type ='%' OR discount_type = 'NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
1608 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1609
1611
1612 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1613
1614 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1615
1616 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1617
1618 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1619 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1620 ELSE 0
1621 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
1622 ELSE quantity_shipped - NVL(quantity_approved,0)
1623 END)))
1624 ELSE agreement_price END,AGREEMENT_CURRENCY_CODE),
1625 discount_value = CASE WHEN ( discount_type ='AMT') THEN ( (claim_amount - ozf_utility_pvt.currround(CASE
1626 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1627
1628 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1629
1630 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1631
1632 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1633
1634 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1635
1636 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1637 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1638 ELSE 0
1639 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
1640 ELSE quantity_shipped - NVL(quantity_approved,0)
1641 END))
1642 WHEN ( discount_type ='NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
1643 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1644
1645 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1646
1647 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1648
1649 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1650
1651 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1652
1653 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1654 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1655 ELSE 0
1656 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
1657 ELSE quantity_shipped - NVL(quantity_approved,0)
1658 END)))
1659 WHEN ( discount_type ='%') THEN ROUND((100 - (((LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
1660 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1661
1662 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1663
1664 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1665
1666 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1667
1668 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1669
1670 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1671 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1672 ELSE 0
1673 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
1674 ELSE quantity_shipped - NVL(quantity_approved,0)
1675 END)))/NVL(list_price,1)) *100)),2)
1676 ELSE discount_value
1677 END,
1678 object_version_number = object_version_number + 1,
1679 last_update_date = sysdate,
1680 last_updated_by = fnd_global.user_id,
1681 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
1682 ELSE total_approved_qty
1683 END
1684 WHERE batch_id = l_batch_id
1685 AND batch_line_id = l_batch_und_appr_lines_tbl(i)
1686 AND purge_flag <> 'Y';
1687 END LOOP;
1688 END IF; --Under approved lines end if
1689
1690 COMMIT;
1691 RETURN;
1692
1693 ELSE
1694
1695 UPDATE ozf_sd_batch_headers_all
1696 SET status_code = 'WIP',
1697 object_version_number = object_version_number + 1,
1698 last_update_date = sysdate,
1699 last_updated_by = fnd_global.user_id
1700 WHERE batch_id = l_batch_id;
1701
1702 UPDATE ozf_sd_batch_lines_int_all
1703 SET processed_flag = 'Y',
1704 last_update_date = sysdate,
1705 last_updated_by = fnd_global.user_id
1706 WHERE ship_frm_sd_claim_request_id = p_batch_number
1707 AND processed_flag = 'N';
1708
1709 COMMIT;
1710 RETURN;
1711
1712 END IF; -- End if For Atleast One Over approved or under approved line
1713 END IF ; -- End if for all lines are over approved case
1714 END IF; --All lines are rejected or if none approved
1715
1716 -- All lines APPROVED case
1717 IF l_cnt_approved_lines = l_cnt_tot_line THEN
1718
1719 UPDATE ozf_sd_batch_headers_all
1720 SET status_code = 'APPROVED',
1721 claim_minor_version = NULL,
1722 object_version_number = object_version_number + 1,
1723 last_update_date = sysdate,
1724 last_updated_by = fnd_global.user_id
1725 WHERE batch_id = l_batch_id;
1726
1727 --Mark all rows as processed
1728 UPDATE ozf_sd_batch_lines_int_all
1729 SET processed_flag = 'Y',
1730 last_update_date = sysdate,
1731 last_updated_by = fnd_global.user_id
1732 WHERE ship_frm_sd_claim_request_id = p_batch_number
1733 AND processed_flag = 'N';
1734
1735 PROCESS_COMP_APPR_BATCH(l_batch_id, x_return_status, x_msg_data);
1736
1737
1738 COMMIT;
1739 RETURN;
1740
1741 END IF; -- All lines APPROVED case
1742
1743
1744 -- If the summation of approved lines and completely approved lines (Rejected line but totally approved) is the total no of lines
1745 -- This scenario is equivalent to all lines being approved.
1746 IF ( NVL( l_cnt_approved_lines,0) + NVL(l_cnt_comp_appr_lines,0) + NVL(l_cnt_over_appr_lines,0) = l_cnt_tot_line ) THEN
1747
1748
1749 UPDATE ozf_sd_batch_headers_all
1750 SET status_code = 'APPROVED',
1751 claim_minor_version = NULL,
1752 object_version_number = object_version_number + 1,
1753 last_update_date = sysdate,
1754 last_updated_by = fnd_global.user_id
1755 WHERE batch_id = l_batch_id;
1756
1757 --Mark all rows as processed
1758 UPDATE ozf_sd_batch_lines_int_all
1759 SET processed_flag = 'Y',
1760 last_update_date = sysdate,
1761 last_updated_by = fnd_global.user_id
1762 WHERE ship_frm_sd_claim_request_id = p_batch_number
1763 AND processed_flag = 'N';
1764
1765 --Update completely approved batch lines
1766 IF((l_batch_comp_appr_lines_tbl IS NOT NULL) AND (l_batch_comp_appr_lines_tbl.COUNT > 0)) THEN
1767 FOR i IN 0..l_batch_comp_appr_lines_tbl.COUNT-1
1768 LOOP
1769 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
1770 'In completely approved lines if loop for batch line id '||l_batch_comp_appr_lines_tbl(i));
1771 UPDATE ozf_sd_batch_lines_all
1772 SET status_code = 'APPROVED',
1773 claim_amount = ozf_utility_pvt.currround(CASE
1774 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1775
1776 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1777
1778 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1779
1780 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1781
1782 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1783
1784 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1785 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1786 ELSE 0
1787 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
1788 batch_curr_claim_amount = CASE
1789 WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
1790 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1791
1792 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1793
1794 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1795
1796 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1797
1798 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1799
1800 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1801 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1802 ELSE 0
1803 END,CLAIM_AMOUNT_CURRENCY_CODE)
1804 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1805 l_batch_currency,
1806 l_func_currency,
1807 (SELECT fu.exchange_rate_type
1808 FROM ozf_funds_utilized_all_b fu
1809 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1810 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1811 NULL,
1812 sysdate,
1813 CASE
1814 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1815
1816 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1817
1818 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1819
1820 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1821
1822 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1823
1824 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1825 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1826 END)
1827
1828 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1829 l_batch_currency,
1830 l_func_currency,
1831 (SELECT fu.exchange_rate_type
1832 FROM ozf_funds_utilized_all_b fu
1833 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1834 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1835 NULL,
1836 (SELECT fu.exchange_rate_date
1840 CASE
1837 FROM ozf_funds_utilized_all_b fu
1838 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1839 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1841 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1842
1843 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1844
1845 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1846
1847 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1848
1849 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1850
1851 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1852 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1853 END)
1854 END,
1855 quantity_shipped = NVL(quantity_approved,quantity_shipped),
1856 discount_value = decode(discount_type,
1857 'AMT',NVL(approved_amount,discount_value),
1858 'NEWPRICE',NVL(approved_amount,agreement_price),
1859 '%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
1860 agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
1861 object_version_number = object_version_number + 1,
1862 last_update_date = sysdate,
1863 last_updated_by = fnd_global.user_id,
1864 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
1865 ELSE total_approved_qty
1866 END
1867 WHERE batch_id = l_batch_id
1868 AND batch_line_id = l_batch_comp_appr_lines_tbl(i)
1869 AND purge_flag <> 'Y';
1870
1871 END LOOP;
1872 END IF;--Completely approved lines (Rejected lines but completely approved)
1873
1874 -- If there are over approved lines,update the status of the lines to approved,batch currency claim amount to current approved amount
1875 IF((l_batch_over_appr_lines_tbl IS NOT NULL) AND (l_batch_over_appr_lines_tbl.COUNT > 0)) THEN
1876 FOR i IN 0..l_batch_over_appr_lines_tbl.COUNT-1
1877 LOOP
1878 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
1879 'In over approved lines if loop for batch line id '||l_batch_over_appr_lines_tbl(i));
1880
1881 UPDATE ozf_sd_batch_lines_all
1882 SET status_code = 'APPROVED',
1883 claim_amount = ozf_utility_pvt.currround(CASE
1884 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1885
1886 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1887
1888 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1889
1890 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1891
1892 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1893
1894 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1895 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1896 ELSE 0
1897 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
1898 batch_curr_claim_amount = CASE
1899 WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
1900 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1901
1902 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1903
1904 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1905
1906 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1907
1908 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1909
1910 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1911 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1912 ELSE 0
1913 END,CLAIM_AMOUNT_CURRENCY_CODE)
1914 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1915 l_batch_currency,
1916 l_func_currency,
1917 (SELECT fu.exchange_rate_type
1918 FROM ozf_funds_utilized_all_b fu
1919 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1920 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1921 NULL,
1922 sysdate,
1923 CASE
1924 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1925
1926 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1927
1928 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1929
1930 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1931
1932 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1933
1934 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1935 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1936 END)
1937
1938 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
1939 l_batch_currency,
1940 l_func_currency,
1941 (SELECT fu.exchange_rate_type
1942 FROM ozf_funds_utilized_all_b fu
1943 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1944 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1945 NULL,
1946 (SELECT fu.exchange_rate_date
1947 FROM ozf_funds_utilized_all_b fu
1948 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
1949 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
1950 CASE
1951 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
1952
1953 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
1954
1955 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
1956
1957 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
1958
1959 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
1960
1961 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
1962 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
1963 END)
1964 END,
1965 quantity_shipped = NVL(quantity_approved,quantity_shipped),
1966 discount_value = decode(discount_type,
1967 'AMT',NVL(approved_amount,discount_value),
1968 'NEWPRICE',NVL(approved_amount,agreement_price),
1969 '%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
1970 agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
1971 object_version_number = object_version_number + 1,
1972 last_update_date = sysdate,
1973 last_updated_by = fnd_global.user_id,
1974 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
1975 ELSE total_approved_qty
1976 END
1977 WHERE batch_id = l_batch_id
1978 AND batch_line_id = l_batch_over_appr_lines_tbl(i)
1979 AND purge_flag <> 'Y';
1980
1981 END LOOP;
1982 END IF;--Over Approved Lines
1983
1984
1985
1986 PROCESS_COMP_APPR_BATCH(l_batch_id, x_return_status, x_msg_data);
1987
1988 COMMIT;
1989 RETURN;
1990
1991 --Not all lines Approved and atleast there is one APPROVED line -- Forking required
1992 ELSE IF ( ( l_cnt_approved_lines > 0 ) AND ( l_cnt_approved_lines <> l_cnt_tot_line ) ) THEN
1993
1994 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
1995 'atleast there is one approved line,before child batch creation');
1996
1997 PROCESS_CHILD_BATCH(l_batch_id,l_batch_appr_lines_tbl,l_batch_und_appr_lines_tbl,l_batch_over_appr_lines_tbl,l_batch_comp_appr_lines_tbl, x_return_status, x_msg_data);
1998
1999 --set batch header as WIP for parent batch
2000 UPDATE ozf_sd_batch_headers_all
2001 SET status_code = 'WIP',
2002 object_version_number = object_version_number + 1,
2003 claim_minor_version = l_claim_minor_version + 1,
2004 last_update_date = sysdate,
2005 last_updated_by = fnd_global.user_id
2006 WHERE batch_id = l_batch_id;
2007
2008 --Mark all rows as processed
2009 UPDATE ozf_sd_batch_lines_int_all
2010 SET processed_flag = 'Y',
2011 last_update_date = sysdate,
2012 last_updated_by = fnd_global.user_id
2013 WHERE ship_frm_sd_claim_request_id = p_batch_number
2014 AND processed_flag = 'N';
2015
2016 --Update the following fields for the under approved batch lines
2017 -- batch_curr_claim_amount,agreement_price(for discount type %,New Price) and discount value for discount type amount,processed_flag
2018 -- Status code of the under approved line has to be set as partially approved
2019 IF((l_batch_und_appr_lines_tbl IS NOT NULL) AND (l_batch_und_appr_lines_tbl.COUNT > 0)) THEN
2020 FOR i IN 0..l_batch_und_appr_lines_tbl.COUNT-1
2021 LOOP
2022 UPDATE ozf_sd_batch_lines_all
2023 SET status_code = 'PARTIALLY_APPROVED',
2024 claim_amount = claim_amount - ozf_utility_pvt.currround(CASE
2025 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2026
2027 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2028
2029 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2030
2031 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2032
2033 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2034
2035 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2036 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2037 ELSE 0
2038 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
2039 batch_curr_claim_amount = CASE
2040 WHEN (claim_amount_currency_code = l_batch_currency) THEN ( claim_amount - ozf_utility_pvt.currround(CASE
2041 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2042
2043 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2044
2045 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2046
2047 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2048
2049 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2050
2051 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2052 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2053 ELSE 0
2054 END,CLAIM_AMOUNT_CURRENCY_CODE))
2055 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
2056 l_batch_currency,
2057 l_func_currency,
2058 (SELECT fu.exchange_rate_type
2059 FROM ozf_funds_utilized_all_b fu
2060 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2061 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
2062 NULL,
2063 sysdate,
2064 claim_amount - ozf_utility_pvt.currround(CASE
2065 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2066
2067 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2068
2069 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2070
2074
2071 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2072
2073 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2075 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2076 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2077 ELSE 0
2078 END,CLAIM_AMOUNT_CURRENCY_CODE))
2079
2080 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
2081 l_batch_currency,
2082 l_func_currency,
2083 (SELECT fu.exchange_rate_type
2084 FROM ozf_funds_utilized_all_b fu
2085 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2086 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
2087 NULL,
2088 (SELECT fu.exchange_rate_date
2089 FROM ozf_funds_utilized_all_b fu
2090 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2091 AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
2092 claim_amount - ozf_utility_pvt.currround(CASE
2093 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2094
2095 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2096
2097 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2098
2099 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2100
2101 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2102
2103 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2104 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2105 ELSE 0
2106 END,CLAIM_AMOUNT_CURRENCY_CODE))
2107 END,
2108 quantity_shipped = CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
2109 ELSE quantity_shipped - NVL(quantity_approved,0)
2110 END,
2111 agreement_price =OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC( CASE WHEN ( discount_type ='%' OR discount_type = 'NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
2112 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2113
2114 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2115
2116 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2117
2118 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2119
2120 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2121
2122 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2123 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2124 ELSE 0
2125 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
2126 ELSE quantity_shipped - NVL(quantity_approved,0)
2127 END)))
2128 ELSE agreement_price END ,agreement_currency_code),
2129 discount_value = CASE WHEN ( discount_type ='AMT') THEN ( (claim_amount - ozf_utility_pvt.currround(CASE
2130 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2131
2132 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2133
2134 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2135
2136 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2137
2141 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2138 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2139
2140 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2142 ELSE 0
2143 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
2144 ELSE quantity_shipped - NVL(quantity_approved,0)
2145 END))
2146 WHEN ( discount_type ='NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
2147 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2148
2149 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2150
2151 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2152
2153 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2154
2155 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2156
2157 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2158 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2159 ELSE 0
2160 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
2161 ELSE quantity_shipped - NVL(quantity_approved,0)
2162 END)))
2163 WHEN ( discount_type ='%') THEN ROUND((100 - (((LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
2164 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2165
2166 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2167
2168 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2169
2170 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2171
2172 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2173
2174 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2175 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2176 ELSE 0
2177 END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
2178 ELSE quantity_shipped - NVL(quantity_approved,0)
2179 END)))/NVL(list_price,1)) *100)),2)
2180 ELSE discount_value
2181 END,
2182 object_version_number = object_version_number + 1,
2183 last_update_date = sysdate,
2184 last_updated_by = fnd_global.user_id,
2185 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
2186 ELSE total_approved_qty
2187 END
2188 WHERE batch_id = l_batch_id
2189 AND batch_line_id = l_batch_und_appr_lines_tbl(i)
2190 AND purge_flag <> 'Y';
2191 END LOOP;
2192 END IF; --Under approved lines end if
2193
2194
2195
2196
2197
2198 COMMIT;
2199 RETURN;
2200
2201 END IF;
2202 END IF; -- Not all lines Approved and atleast there is one APPROVED line
2203
2204 --If partial approval is not allowed for a batch
2205 ELSE
2206
2207 FOR V_MISS_REJ_APP_REC IN C_MISS_REJ_APP
2208
2209 LOOP
2210
2211 UPDATE ozf_sd_batch_lines_all
2212 SET status_code = V_MISS_REJ_APP_REC.stat_code,
2213 approved_amount = (case when (original_claim_amount > 0) THEN V_MISS_REJ_APP_REC.auth_cost
2214 when (original_claim_amount < 0 AND V_MISS_REJ_APP_REC.line_status_code = 'APPROVED') then V_MISS_REJ_APP_REC.auth_cost end),
2215
2216 approved_currency_code = (case when (original_claim_amount > 0) THEN V_MISS_REJ_APP_REC.auth_curr_code
2217 when (original_claim_amount < 0 AND V_MISS_REJ_APP_REC.line_status_code = 'APPROVED') THEN V_MISS_REJ_APP_REC.auth_curr_code end),
2218
2219 object_version_number = object_version_number + 1,
2220 last_update_date = sysdate,
2221 last_updated_by = fnd_global.user_id,
2222 vendor_ref_id = V_MISS_REJ_APP_REC.credit_reference_id,
2226 batch_curr_claim_amount = decode(V_MISS_REJ_APP_REC.stat_code,'APPROVED',V_MISS_REJ_APP_REC.computed_batch_curr_claim_amt, batch_curr_claim_amount),
2223 quantity_approved = (case when (original_claim_amount > 0) THEN V_MISS_REJ_APP_REC.vendor_auth_quantity
2224 when (original_claim_amount < 0 AND V_MISS_REJ_APP_REC.line_status_code = 'APPROVED') THEN V_MISS_REJ_APP_REC.vendor_auth_quantity end),
2225
2227 adjustment_type_id = (case when ( (original_claim_amount > 0)
2228 AND (V_MISS_REJ_APP_REC.approved_claim_amount > original_claim_amount)
2229 AND V_MISS_REJ_APP_REC.adjustment_type <> 'STANDARD'
2230 AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
2231 THEN l_ssd_inc_adj_type_id
2232 when ( (original_claim_amount > 0)
2233 AND (V_MISS_REJ_APP_REC.approved_claim_amount < original_claim_amount)
2234 AND V_MISS_REJ_APP_REC.adjustment_type <> 'DECREASE_EARNED'
2235 AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
2236 THEN l_ssd_dec_adj_type_id
2237 else
2238 adjustment_type_id
2239 end)
2240 WHERE batch_id = l_batch_id
2241 AND batch_line_number = V_MISS_REJ_APP_REC.batch_line_number
2242 AND purge_flag <> 'Y'
2243 AND status_code = 'SUBMITTED';
2244
2245 END LOOP ;
2246
2247 --get total number of lines
2248 SELECT COUNT(1)
2249 INTO l_cnt_tot_line
2250 FROM ozf_sd_batch_lines_all
2251 WHERE batch_id = l_batch_id
2252 AND purge_flag <> 'Y';
2253
2254 --get number of approved lines
2255 SELECT COUNT(1)
2256 INTO l_cnt_approved_lines
2257 FROM ozf_sd_batch_lines_all
2258 WHERE batch_id = l_batch_id
2259 AND status_code = 'APPROVED'
2260 AND purge_flag <> 'Y';
2261
2262 --get number of rejected lines
2263 SELECT COUNT(1)
2264 INTO l_cnt_rejected_lines
2265 FROM ozf_sd_batch_lines_all
2266 WHERE batch_id = l_batch_id
2267 AND status_code = 'REJECTED'
2268 AND purge_flag <> 'Y';
2269
2270 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'number of total lines: '||l_cnt_tot_line);
2271 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'number of rejected lines: '||l_cnt_rejected_lines);
2272 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'number of approved lines: '||l_cnt_approved_lines);
2273
2274
2275 --All lines are rejected or if none approved
2276
2277 IF ( ( l_cnt_rejected_lines = l_cnt_tot_line) OR ( l_cnt_approved_lines = 0 ) ) THEN
2278
2279
2280 UPDATE ozf_sd_batch_headers_all
2281 SET status_code = 'WIP',
2282 object_version_number = object_version_number + 1,
2283 last_update_date = sysdate,
2284 last_updated_by = fnd_global.user_id
2285 WHERE batch_id = l_batch_id;
2286
2287 UPDATE ozf_sd_batch_lines_int_all
2288 SET processed_flag = 'Y',
2289 last_update_date = sysdate,
2290 last_updated_by = fnd_global.user_id
2291 WHERE ship_frm_sd_claim_request_id = p_batch_number
2292 AND processed_flag = 'N';
2293
2294 COMMIT;
2295 RETURN;
2296
2297 END IF; --All lines are rejected or if none approved
2298
2299 -- All lines APPROVED case
2300 IF l_cnt_approved_lines = l_cnt_tot_line THEN
2301
2302 UPDATE ozf_sd_batch_headers_all
2303 SET status_code = 'APPROVED',
2304 claim_minor_version = NULL,
2305 object_version_number = object_version_number + 1,
2306 last_update_date = sysdate,
2307 last_updated_by = fnd_global.user_id
2308 WHERE batch_id = l_batch_id;
2309
2310 --Mark all rows as processed
2311 UPDATE ozf_sd_batch_lines_int_all
2312 SET processed_flag = 'Y',
2313 last_update_date = sysdate,
2314 last_updated_by = fnd_global.user_id
2315 WHERE ship_frm_sd_claim_request_id = p_batch_number
2316 AND processed_flag = 'N';
2317
2318 PROCESS_COMP_APPR_BATCH(l_batch_id, x_return_status, x_msg_data);
2319 COMMIT;
2320 RETURN;
2321
2322 END IF; -- All lines APPROVED case
2323
2324
2325 --Not all lines Approved and atleast there is one APPROVED line -- Forking required
2326 IF ( ( l_cnt_approved_lines > 0 ) AND ( l_cnt_approved_lines <> l_cnt_tot_line ) ) THEN
2327
2328 PROCESS_CHILD_BATCH(l_batch_id, x_return_status, x_msg_data);
2329
2330 --set batch header as WIP for parent batch
2331 UPDATE ozf_sd_batch_headers_all
2332 SET status_code = 'WIP',
2333 object_version_number = object_version_number + 1,
2334 claim_minor_version = l_claim_minor_version + 1,
2335 last_update_date = sysdate,
2336 last_updated_by = fnd_global.user_id
2337 WHERE batch_id = l_batch_id;
2338
2339 --Mark all rows as processed
2340 UPDATE ozf_sd_batch_lines_int_all
2341 SET processed_flag = 'Y',
2342 last_update_date = sysdate,
2343 last_updated_by = fnd_global.user_id
2344 WHERE ship_frm_sd_claim_request_id = p_batch_number
2345 AND processed_flag = 'N';
2346
2347 COMMIT;
2351 END IF; -- Partial approval is not allowed
2348 RETURN;
2349
2350 END IF; -- Not all lines Approved and atleast there is one APPROVED line
2352
2353
2354 IF x_return_status = fnd_api.g_ret_sts_error THEN
2355 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
2356 'Entered throwing exception' || x_msg_data);
2357 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
2358 fnd_message.set_token('MESSAGE', x_msg_data);
2359 RAISE fnd_api.g_exc_error;
2360 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2361 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2362 'Entered throwing unexpected exception');
2363 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
2364 fnd_message.set_token('MESSAGE', x_msg_data);
2365 RAISE fnd_api.g_exc_unexpected_error;
2366 END IF;
2367
2368 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'Procedure Ends');
2369
2370 EXCEPTION
2371 WHEN fnd_api.g_exc_error THEN
2372 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2373 'OZF EXCEPTION G_EXC_ERROR: '||x_msg_data);
2374 x_return_status := fnd_api.g_ret_sts_error;
2375 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
2376 fnd_message.set_token('MESSAGE', x_msg_data);
2377 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2378 'OZF done:' || x_msg_data || '::::');
2379
2380 WHEN fnd_api.g_exc_unexpected_error THEN
2381 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2382 'OZF EXCEPTION G_EXC_UNEXPECTED_ERROR');
2383 x_return_status := fnd_api.g_ret_sts_unexp_error;
2384 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
2385 fnd_message.set_token('MESSAGE', x_msg_data);
2386 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2387 'OZF done G_EXC_UNEXPECTED_ERROR:' || x_msg_data || '::::');
2388 WHEN others THEN
2389 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2390 'OZF EXCEPTION OTHERS' || sqlerrm);
2391 x_return_status := fnd_api.g_ret_sts_error;
2392 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
2393 fnd_message.set_token('MESSAGE', x_msg_data);
2394 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
2395 'OZF done OTHERS:' || x_msg_data ||'::::');
2396 END update_data;
2397
2398
2399
2400 PROCEDURE PROCESS_CHILD_BATCH(p_batch_id IN NUMBER,
2401 p_batch_appr_lines_tbl IN number_tbl,
2402 p_batch_undr_appr_lines_tbl IN number_tbl,
2403 p_batch_over_appr_lines_tbl IN number_tbl,
2404 p_batch_comp_appr_lines_tbl IN number_tbl,
2405 x_return_status OUT nocopy VARCHAR2,
2406 x_msg_data OUT nocopy VARCHAR2) IS
2407
2408 l_batch_line_tbl_rec OZF_SD_BATCH_LINES_ALL%ROWTYPE;
2409 l_cnt_total_line NUMBER;
2410 l_cnt_rejected_line NUMBER;
2411 l_new_batch_id NUMBER := NULL;
2412 l_message VARCHAR2(1000);
2413 l_vendor_id NUMBER;
2414 l_vendor_site_id NUMBER;
2415 l_org_id NUMBER;
2416 l_claim_amount NUMBER;
2417 l_dup_dispute NUMBER;
2418 l_batch_threshold NUMBER;
2419 l_line_threshold NUMBER;
2420 l_currency_code VARCHAR2(20);
2421 l_batch_number VARCHAR2(20);
2422 l_par_batch_curr_code VARCHAR2(20);
2423 l_par_batch_owner_id NUMBER;
2424 l_claim_number VARCHAR2(30);
2425 l_claim_id NUMBER;
2426 l_claim_minor_version NUMBER;
2427 l_msg_count NUMBER;
2428
2429 l_tot_app_claim_amt NUMBER ;
2430 l_batch_type VARCHAR2(30);
2431 l_func_currency VARCHAR2(15);
2432 l_batch_currency VARCHAR2(15);
2433 l_line_clm_amt NUMBER;
2434 l_line_batch_curr_clm_amt NUMBER;
2435
2436
2437 --Start: Fix for Bug 10628160
2438 l_custom_setup_id NUMBER := NULL;
2439 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
2440 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
2441 l_split_claim_id NUMBER := NULL;
2442 l_msg_count3 NUMBER := NULL;
2443 l_msg_data VARCHAR2(100) := NULL;
2444 l_msg_count2 NUMBER := NULL;
2445 l_msg_data2 VARCHAR2(100) := NULL;
2446 l_return_status2 VARCHAR2(15) := NULL;
2447 l_return_status VARCHAR2(15) := NULL;
2448
2449 --End: Fix for Bug 10628160
2450
2451
2452 BEGIN
2453
2454 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2455 'Procedure Starts: Batch Id :'||p_batch_id);
2456
2457
2458 SELECT batch_number, vendor_id, vendor_site_id, org_id,
2459 currency_code, batch_amount_threshold, batch_line_amount_threshold, claim_number, claim_minor_version,batch_type,owner_id,currency_code
2460 INTO l_batch_number, l_vendor_id, l_vendor_site_id, l_org_id,
2461 l_par_batch_curr_code, l_batch_threshold, l_line_threshold, l_claim_number, l_claim_minor_version,l_batch_type,l_par_batch_owner_id,l_batch_currency
2462 FROM ozf_sd_batch_headers_all
2463 WHERE batch_id = p_batch_id;
2464
2465 SELECT gs.currency_code
2466 INTO l_func_currency
2467 FROM gl_sets_of_books gs,
2468 ozf_sys_parameters_all org,
2469 ozf_sd_batch_headers_all bh
2473
2470 WHERE org.set_of_books_id = gs.set_of_books_id
2471 AND org.org_id = bh.org_id
2472 AND bh.batch_id = p_batch_id;
2474 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2475 'Functional currency for :'||p_batch_id||' : '||l_func_currency);
2476
2477 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2478 'Batch currency for :'||p_batch_id||' : '||l_batch_currency);
2479
2480 ozf_sd_batch_pvt.create_batch_header(l_vendor_id,
2481 l_vendor_site_id,
2482 l_org_id,
2483 l_batch_threshold,
2484 l_line_threshold,
2485 l_par_batch_curr_code,
2486 'F' ,
2487 'APPROVED',
2488 l_claim_number||'_'||l_claim_minor_version,
2489 l_claim_minor_version,
2490 p_batch_id, -- current batch (i.e) parent batch_id
2491 l_new_batch_id,
2492 l_batch_type,
2493 l_par_batch_owner_id);
2494
2495 -- if there are approved lines update the batch_id of the approved lines to the new batch id
2496 IF((p_batch_appr_lines_tbl IS NOT NULL) AND (p_batch_appr_lines_tbl.COUNT > 0)) THEN
2497 UPDATE ozf_sd_batch_lines_all
2498 SET batch_id = l_new_batch_id,
2499 object_version_number = object_version_number + 1,
2500 last_update_date = sysdate,
2501 last_updated_by = fnd_global.user_id
2502 WHERE batch_id = p_batch_id
2503 AND status_code = 'APPROVED'
2504 AND purge_flag <> 'Y';
2505 END IF; -- Approved lines
2506 -- If there are completely approved lines update the status of the lines to approved,batch currency claim amount to current approved amount
2507 IF((p_batch_comp_appr_lines_tbl IS NOT NULL) AND (p_batch_comp_appr_lines_tbl.COUNT > 0)) THEN
2508 FOR i IN 0..p_batch_comp_appr_lines_tbl.COUNT-1
2509 LOOP
2510 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2511 'In completely approved lines if loop for batch line id '||p_batch_comp_appr_lines_tbl(i));
2512 UPDATE ozf_sd_batch_lines_all
2513 SET batch_id = l_new_batch_id,
2514 status_code = 'APPROVED',
2515 claim_amount = ozf_utility_pvt.currround(CASE
2516 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2517
2518 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2519
2520 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2521
2522 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2523
2524 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2525
2526 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2527 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2528 ELSE 0
2529 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
2530 batch_curr_claim_amount = CASE
2531 WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
2532 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2533
2534 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2535
2536 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2537
2538 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2539
2540 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2541
2542 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2543 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2544 ELSE 0
2545 END,CLAIM_AMOUNT_CURRENCY_CODE)
2546 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
2547 l_batch_currency,
2548 l_func_currency,
2549 (SELECT fu.exchange_rate_type
2550 FROM ozf_funds_utilized_all_b fu
2551 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2552 AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
2553 NULL,
2554 sysdate,
2555 CASE
2559
2556 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2557
2558 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2560 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2561
2562 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2563
2564 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2565
2566 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2567 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2568 END)
2569
2570 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
2571 l_batch_currency,
2572 l_func_currency,
2573 (SELECT fu.exchange_rate_type
2574 FROM ozf_funds_utilized_all_b fu
2575 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2576 AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
2577 NULL,
2578 (SELECT fu.exchange_rate_date
2579 FROM ozf_funds_utilized_all_b fu
2580 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2581 AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
2582 CASE
2583 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
2584
2585 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
2586
2587 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
2588
2589 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
2590
2591 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
2592
2593 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
2594 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
2595 END)
2596 END,
2597 quantity_shipped = NVL(quantity_approved,quantity_shipped),
2598 discount_value = decode(discount_type,
2599 'AMT',NVL(approved_amount,discount_value),
2600 'NEWPRICE',NVL(approved_amount,agreement_price),
2601 '%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
2602 agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
2603 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
2604 ELSE total_approved_qty
2605 END,
2606 object_version_number = object_version_number + 1,
2607 last_update_date = sysdate,
2608 last_updated_by = fnd_global.user_id
2609
2610 WHERE batch_id = p_batch_id
2611 AND batch_line_id = p_batch_comp_appr_lines_tbl(i)
2612 AND purge_flag <> 'Y';
2613
2614 END LOOP;
2615 END IF;--Completely approved lines (Rejected lines but completely approved)
2616
2617 -- If there are under approved lines,create split lines
2618 IF((p_batch_undr_appr_lines_tbl IS NOT NULL) AND (p_batch_undr_appr_lines_tbl.COUNT > 0)) THEN
2619 FOR i IN 0..p_batch_undr_appr_lines_tbl.COUNT-1
2620 LOOP
2621
2622 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2623 'In under approved lines if loop for batch line id '||p_batch_undr_appr_lines_tbl(i));
2624
2625 SELECT * INTO l_batch_line_tbl_rec
2626 FROM OZF_SD_BATCH_LINES_ALL
2627 WHERE batch_id = p_batch_id
2628 AND batch_line_id =p_batch_undr_appr_lines_tbl(i);
2629
2630 l_line_clm_amt := ozf_utility_pvt.currround(CASE
2631 WHEN((l_batch_line_tbl_rec.approved_amount is null and l_batch_line_tbl_rec.quantity_approved is null)
2632 OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
2633 OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
2634 and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
2635
2636 WHEN (l_batch_line_tbl_rec.approved_amount is null and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2637 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2641 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2638 THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
2639
2640 WHEN (l_batch_line_tbl_rec.approved_amount is null
2642 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2643 THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
2644
2645 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2646 and l_batch_line_tbl_rec.approved_amount is not null
2647 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2648 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2649
2650 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2651 and l_batch_line_tbl_rec.approved_amount is not null
2652 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2653 THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2654
2655 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2656 and l_batch_line_tbl_rec.approved_amount is not null
2657 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2658 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2659 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2660 and l_batch_line_tbl_rec.approved_amount is not null
2661 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2662 THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2663 ELSE 0
2664 END,l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE);
2665 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2666 'In under approved lines if loop for batch line id '||p_batch_undr_appr_lines_tbl(i) ||' claim amount in request currency l_line_clm_amt: '||l_line_clm_amt);
2667
2668 SELECT CASE
2669 WHEN (l_batch_line_tbl_rec.claim_amount_currency_code = l_batch_currency)
2670 THEN ozf_utility_pvt.currround(CASE
2671 WHEN((l_batch_line_tbl_rec.approved_amount is null
2672 and l_batch_line_tbl_rec.quantity_approved is null)
2673 OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
2674 OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
2675 and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
2676
2677 WHEN (l_batch_line_tbl_rec.approved_amount is null
2678 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2679 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2680 THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
2681
2682 WHEN (l_batch_line_tbl_rec.approved_amount is null
2683 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2684 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2685 THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
2686
2687 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2688 and l_batch_line_tbl_rec.approved_amount is not null
2689 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2690 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2691
2692 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2693 and l_batch_line_tbl_rec.approved_amount is not null
2694 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2695 THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2696
2697 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2698 and l_batch_line_tbl_rec.approved_amount is not null
2699 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2700 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2701 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2702 and l_batch_line_tbl_rec.approved_amount is not null
2703 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2704 THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2705 ELSE 0
2706 END,l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE)
2707 WHEN ((l_batch_line_tbl_rec.claim_amount_currency_code = l_func_currency)
2708 AND (l_func_currency <> l_batch_currency))
2709 THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(l_batch_line_tbl_rec.claim_amount_currency_code,
2710 l_batch_currency,
2711 l_func_currency,
2712 (SELECT fu.exchange_rate_type
2713 FROM ozf_funds_utilized_all_b fu,
2714 ozf_sd_batch_lines_all
2715 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2716 AND ozf_sd_batch_lines_all.batch_line_id =l_batch_line_tbl_rec.batch_line_id),
2717 NULL,
2718 sysdate,
2719 CASE WHEN((l_batch_line_tbl_rec.approved_amount is null
2720 and l_batch_line_tbl_rec.quantity_approved is null)
2721 OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
2722 OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
2726 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2723 and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
2724
2725 WHEN (l_batch_line_tbl_rec.approved_amount is null
2727 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2728 THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
2729
2730 WHEN (l_batch_line_tbl_rec.approved_amount is null
2731 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2732 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2733 THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
2734
2735 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2736 and l_batch_line_tbl_rec.approved_amount is not null
2737 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2738 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2739
2740 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2741 and l_batch_line_tbl_rec.approved_amount is not null
2742 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2743 THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2744
2745 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2746 and l_batch_line_tbl_rec.approved_amount is not null
2747 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2748 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2749 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2750 and l_batch_line_tbl_rec.approved_amount is not null
2751 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2752 THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2753 ELSE 0
2754 END)
2755
2756 WHEN (l_batch_line_tbl_rec.claim_amount_currency_code <> l_func_currency)
2757 THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(l_batch_line_tbl_rec.claim_amount_currency_code,
2758 l_batch_currency,
2759 l_func_currency,
2760 (SELECT fu.exchange_rate_type
2761 FROM ozf_funds_utilized_all_b fu,
2762 ozf_sd_batch_lines_all
2763 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2764 AND ozf_sd_batch_lines_all.batch_line_id =l_batch_line_tbl_rec.batch_line_id),
2765 NULL,
2766 (SELECT fu.exchange_rate_date
2767 FROM ozf_funds_utilized_all_b fu,
2768 ozf_sd_batch_lines_all
2769 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
2770 AND ozf_sd_batch_lines_all.batch_line_id =l_batch_line_tbl_rec.batch_line_id),
2771 CASE WHEN((l_batch_line_tbl_rec.approved_amount is null
2772 and l_batch_line_tbl_rec.quantity_approved is null)
2773 OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
2774 OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
2775 and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
2776
2777 WHEN (l_batch_line_tbl_rec.approved_amount is null
2778 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2779 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2780 THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
2781
2782 WHEN (l_batch_line_tbl_rec.approved_amount is null
2783 and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
2784 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2785 THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
2786
2787 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2788 and l_batch_line_tbl_rec.approved_amount is not null
2789 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2790 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2791
2792 WHEN (l_batch_line_tbl_rec.quantity_approved is null
2793 and l_batch_line_tbl_rec.approved_amount is not null
2794 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2795 THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
2796
2797 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2798 and l_batch_line_tbl_rec.approved_amount is not null
2799 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
2800 THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2801 WHEN (l_batch_line_tbl_rec.quantity_approved is not null
2802 and l_batch_line_tbl_rec.approved_amount is not null
2803 AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
2804 THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
2805 ELSE 0
2806 END)
2807 END INTO l_line_batch_curr_clm_amt from dual;
2808
2809 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
2810 'In under approved lines if loop for batch line id '||p_batch_undr_appr_lines_tbl(i) ||' claim amount in batch currency l_line_batch_curr_clm_amt: '||l_line_batch_curr_clm_amt);
2811
2812 INSERT INTO OZF_SD_BATCH_LINES_ALL
2816 batch_line_number,
2813 (batch_line_id,
2814 object_version_number,
2815 batch_id,
2817 utilization_id,
2818 agreement_number,
2819 ship_to_org_id,
2820 ship_to_contact_id,
2821 sold_to_customer_id,
2822 sold_to_contact_id,
2823 sold_to_site_use_id,
2824 end_customer_id,
2825 end_customer_contact_id,
2826 order_header_id,
2827 order_line_id,
2828 invoice_number,
2829 invoice_line_number,
2830 resale_price_currency_code,
2831 resales_price,
2832 list_price_currency_code,
2833 list_price,
2834 agreement_currency_code,
2835 agreement_price,
2836 status_code,
2837 claim_amount,
2838 claim_amount_currency_code,
2839 batch_curr_claim_amount,
2840 original_claim_amount,
2841 batch_curr_orig_claim_amount,
2842 item_id,
2843 vendor_item_id,
2844 shipped_quantity_uom,
2845 last_sub_claim_amount,
2846 acctd_amount_remaining,
2847 univ_curr_amount_remaining,
2848 fund_request_amount_remaining,
2849 amount_remaining,
2850 quantity_shipped,
2851 purge_flag,
2852 order_date,
2853 creation_date,
2854 last_update_date,
2855 last_updated_by,
2856 request_id,
2857 created_by,
2858 last_update_login,
2859 program_application_id,
2860 program_update_date,
2861 program_id,
2862 org_id,
2863 transmit_flag,
2864 discount_type,
2865 discount_value,
2866 discount_currency_code,
2867 adjustment_type_id,
2868 order_source,
2869 parent_line_id,
2870 total_approved_amt,
2871 total_approved_qty,
2872 vendor_ref_id,
2873 ATTRIBUTE_CATEGORY,
2874 ATTRIBUTE1,
2875 ATTRIBUTE2,
2876 ATTRIBUTE3,
2877 ATTRIBUTE4,
2878 ATTRIBUTE5,
2879 ATTRIBUTE6,
2880 ATTRIBUTE7,
2881 ATTRIBUTE8,
2882 ATTRIBUTE9,
2883 ATTRIBUTE10,
2884 ATTRIBUTE11,
2885 ATTRIBUTE12,
2886 ATTRIBUTE13,
2887 ATTRIBUTE14,
2888 ATTRIBUTE15,
2889 ATTRIBUTE16,
2890 ATTRIBUTE17,
2891 ATTRIBUTE18,
2892 ATTRIBUTE19,
2893 ATTRIBUTE20,
2894 ATTRIBUTE21,
2895 ATTRIBUTE22,
2896 ATTRIBUTE23,
2897 ATTRIBUTE24,
2898 ATTRIBUTE25,
2899 ATTRIBUTE26,
2900 ATTRIBUTE27,
2901 ATTRIBUTE28,
2902 ATTRIBUTE29,
2903 ATTRIBUTE30,
2904 process_feed_flag
2905
2906 )values
2907 (OZF_SD_BATCH_LINES_ALL_S.NEXTVAL,
2908 1,
2909 l_new_batch_id,
2910 l_batch_line_tbl_rec.batch_line_number,
2911 l_batch_line_tbl_rec.utilization_id,
2912 l_batch_line_tbl_rec.agreement_number,
2913 l_batch_line_tbl_rec.ship_to_org_id,
2914 l_batch_line_tbl_rec.ship_to_contact_id,
2915 l_batch_line_tbl_rec.sold_to_customer_id,
2916 l_batch_line_tbl_rec.sold_to_contact_id,
2917 l_batch_line_tbl_rec.sold_to_site_use_id,
2918 l_batch_line_tbl_rec.end_customer_id,
2919 l_batch_line_tbl_rec.end_customer_contact_id,
2920 l_batch_line_tbl_rec.order_header_id,
2921 l_batch_line_tbl_rec.order_line_id,
2922 l_batch_line_tbl_rec.invoice_number,
2923 l_batch_line_tbl_rec.invoice_line_number,
2924 l_batch_line_tbl_rec.resale_price_currency_code,
2925 l_batch_line_tbl_rec.resales_price,
2926 l_batch_line_tbl_rec.list_price_currency_code,
2927 l_batch_line_tbl_rec.list_price,
2928 l_batch_line_tbl_rec.agreement_currency_code,
2929 decode(l_batch_line_tbl_rec.discount_type,
2930 '%',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price),
2931 'NEWPRICE',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price),
2932 l_batch_line_tbl_rec.agreement_price),
2933 'APPROVED',--Status code
2934 l_line_clm_amt, -- Current approved Claim Amount
2935 l_batch_line_tbl_rec.claim_amount_currency_code,
2936 l_line_batch_curr_clm_amt, -- batch currency claim amount
2937 l_line_clm_amt, -- original claim amount in request currency
2938 l_line_batch_curr_clm_amt, --original claim amount in batch currency
2939 l_batch_line_tbl_rec.item_id,
2940 l_batch_line_tbl_rec.vendor_item_id,
2941 l_batch_line_tbl_rec.shipped_quantity_uom,
2942 l_batch_line_tbl_rec.last_sub_claim_amount,
2943 l_batch_line_tbl_rec.acctd_amount_remaining,
2944 l_batch_line_tbl_rec.univ_curr_amount_remaining,
2945 l_batch_line_tbl_rec.fund_request_amount_remaining,
2946 l_batch_line_tbl_rec.amount_remaining,
2947 NVL(l_batch_line_tbl_rec.quantity_approved,l_batch_line_tbl_rec.quantity_shipped), --shipped quantity
2948 l_batch_line_tbl_rec.purge_flag,
2949 l_batch_line_tbl_rec.order_date,
2950 l_batch_line_tbl_rec.creation_date,
2951 l_batch_line_tbl_rec.last_update_date,
2952 l_batch_line_tbl_rec.last_updated_by,
2953 l_batch_line_tbl_rec.request_id,
2954 l_batch_line_tbl_rec.created_by,
2955 l_batch_line_tbl_rec.last_update_login,
2956 l_batch_line_tbl_rec.program_application_id,
2957 l_batch_line_tbl_rec.program_update_date,
2958 l_batch_line_tbl_rec.program_id,
2959 l_batch_line_tbl_rec.org_id,
2960 l_batch_line_tbl_rec.transmit_flag,
2961 l_batch_line_tbl_rec.discount_type,
2965 '%',ROUND((100 - ((NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price)/NVL(l_batch_line_tbl_rec.list_price,1)) *100)),2)
2962 decode(l_batch_line_tbl_rec.discount_type,
2963 'AMT',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.discount_value),
2964 'NEWPRICE',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price),
2966 ,l_batch_line_tbl_rec.discount_value),
2967 l_batch_line_tbl_rec.discount_currency_code,
2968 l_batch_line_tbl_rec.adjustment_type_id,--need to review
2969 l_batch_line_tbl_rec.order_source,
2970 l_batch_line_tbl_rec.batch_line_id,
2971 l_line_clm_amt, -- Total approved amount
2972 NVL(l_batch_line_tbl_rec.quantity_approved,l_batch_line_tbl_rec.quantity_shipped),-- Total approved quantity
2973 l_batch_line_tbl_rec.vendor_ref_id,
2974 l_batch_line_tbl_rec.ATTRIBUTE_CATEGORY,
2975 l_batch_line_tbl_rec.ATTRIBUTE1,
2976 l_batch_line_tbl_rec.ATTRIBUTE2,
2977 l_batch_line_tbl_rec.ATTRIBUTE3,
2978 l_batch_line_tbl_rec.ATTRIBUTE4,
2979 l_batch_line_tbl_rec.ATTRIBUTE5,
2980 l_batch_line_tbl_rec.ATTRIBUTE6,
2981 l_batch_line_tbl_rec.ATTRIBUTE7,
2982 l_batch_line_tbl_rec.ATTRIBUTE8,
2983 l_batch_line_tbl_rec.ATTRIBUTE9,
2984 l_batch_line_tbl_rec.ATTRIBUTE10,
2985 l_batch_line_tbl_rec.ATTRIBUTE11,
2986 l_batch_line_tbl_rec.ATTRIBUTE12,
2987 l_batch_line_tbl_rec.ATTRIBUTE13,
2988 l_batch_line_tbl_rec.ATTRIBUTE14,
2989 l_batch_line_tbl_rec.ATTRIBUTE15,
2990 l_batch_line_tbl_rec.ATTRIBUTE16,
2991 l_batch_line_tbl_rec.ATTRIBUTE17,
2992 l_batch_line_tbl_rec.ATTRIBUTE18,
2993 l_batch_line_tbl_rec.ATTRIBUTE19,
2994 l_batch_line_tbl_rec.ATTRIBUTE20,
2995 l_batch_line_tbl_rec.ATTRIBUTE21,
2996 l_batch_line_tbl_rec.ATTRIBUTE22,
2997 l_batch_line_tbl_rec.ATTRIBUTE23,
2998 l_batch_line_tbl_rec.ATTRIBUTE24,
2999 l_batch_line_tbl_rec.ATTRIBUTE25,
3000 l_batch_line_tbl_rec.ATTRIBUTE26,
3001 l_batch_line_tbl_rec.ATTRIBUTE27,
3002 l_batch_line_tbl_rec.ATTRIBUTE28,
3003 l_batch_line_tbl_rec.ATTRIBUTE29,
3004 l_batch_line_tbl_rec.ATTRIBUTE30,
3005 l_batch_line_tbl_rec.process_feed_flag
3006 );
3007
3008 END LOOP;
3009 END IF;
3010
3011 -- If there are over approved lines,update the status of the lines to approved,batch currency claim amount to current approved amount
3012 IF((p_batch_over_appr_lines_tbl IS NOT NULL) AND (p_batch_over_appr_lines_tbl.COUNT > 0)) THEN
3013 FOR i IN 0..p_batch_over_appr_lines_tbl.COUNT-1
3014 LOOP
3015 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3016 'In over approved lines if loop for batch line id '||p_batch_over_appr_lines_tbl(i));
3017
3018 UPDATE ozf_sd_batch_lines_all
3019 SET batch_id = l_new_batch_id,
3020 status_code = 'APPROVED',
3021 claim_amount = ozf_utility_pvt.currround(CASE
3022 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
3023
3024 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
3025
3026 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
3027
3028 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
3029
3030 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
3031
3032 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
3033 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
3034 ELSE 0
3035 END,CLAIM_AMOUNT_CURRENCY_CODE) ,
3036 batch_curr_claim_amount = CASE
3037 WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
3038 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
3039
3040 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
3041
3042 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
3043
3044 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
3045
3046 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
3047
3048 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
3049 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
3050 ELSE 0
3054 l_func_currency,
3051 END,CLAIM_AMOUNT_CURRENCY_CODE)
3052 WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
3053 l_batch_currency,
3055 (SELECT fu.exchange_rate_type
3056 FROM ozf_funds_utilized_all_b fu
3057 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
3058 AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
3059 NULL,
3060 sysdate,
3061 CASE
3062 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
3063
3064 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
3065
3066 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
3067
3068 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
3069
3070 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
3071
3072 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
3073 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
3074 END)
3075
3076 WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
3077 l_batch_currency,
3078 l_func_currency,
3079 (SELECT fu.exchange_rate_type
3080 FROM ozf_funds_utilized_all_b fu
3081 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
3082 AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
3083 NULL,
3084 (SELECT fu.exchange_rate_date
3085 FROM ozf_funds_utilized_all_b fu
3086 WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
3087 AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
3088 CASE
3089 WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
3090
3091 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
3092
3093 WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
3094
3095 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
3096
3097 WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
3098
3099 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
3100 WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
3101 END)
3102 END,
3103 quantity_shipped = NVL(quantity_approved,quantity_shipped),
3104 discount_value = decode(discount_type,
3105 'AMT',NVL(approved_amount,discount_value),
3106 'NEWPRICE',NVL(approved_amount,agreement_price),
3107 '%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
3108 agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
3109 object_version_number = object_version_number + 1,
3110 last_update_date = sysdate,
3111 last_updated_by = fnd_global.user_id,
3112 total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(l_batch_line_tbl_rec.quantity_approved,l_batch_line_tbl_rec.quantity_shipped)
3113 ELSE total_approved_qty
3114 END
3115 WHERE batch_id = p_batch_id
3116 AND batch_line_id = p_batch_over_appr_lines_tbl(i)
3117 AND purge_flag <> 'Y';
3118
3119 END LOOP;
3120 END IF;--Over Approved Lines
3121
3122 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::CREATE_CHILD_BATCH',
3123 'Procedure Ends');
3124 --raising business event
3125 ozf_sd_util_pvt.sd_raise_event (l_new_batch_id, 'CREATE', x_return_status);
3126
3127 PROCESS_COMP_APPR_BATCH(l_new_batch_id, x_return_status, x_msg_data);
3128
3129 END PROCESS_CHILD_BATCH;
3130
3131
3132 PROCEDURE PROCESS_CHILD_BATCH(p_batch_id IN NUMBER,
3133 x_return_status OUT nocopy VARCHAR2,
3134 x_msg_data OUT nocopy VARCHAR2) IS
3135
3136 l_cnt_total_line NUMBER;
3137 l_cnt_rejected_line NUMBER;
3138 l_new_batch_id NUMBER := NULL;
3142 l_org_id NUMBER;
3139 l_message VARCHAR2(1000);
3140 l_vendor_id NUMBER;
3141 l_vendor_site_id NUMBER;
3143 l_claim_amount NUMBER;
3144 l_dup_dispute NUMBER;
3145 l_batch_threshold NUMBER;
3146 l_line_threshold NUMBER;
3147 l_currency_code VARCHAR2(20);
3148 l_batch_number VARCHAR2(20);
3149 l_par_batch_curr_code VARCHAR2(20);
3150 l_par_batch_owner_id NUMBER;
3151 l_claim_number VARCHAR2(30);
3152 l_claim_id NUMBER;
3153 l_claim_minor_version NUMBER;
3154 l_msg_count NUMBER;
3155
3156 l_tot_app_claim_amt NUMBER ;
3157 l_batch_type VARCHAR2(30);
3158
3159
3160 --Start: Fix for Bug 10628160
3161 l_custom_setup_id NUMBER := NULL;
3162 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
3163 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
3164 l_split_claim_id NUMBER := NULL;
3165 l_msg_count3 NUMBER := NULL;
3166 l_msg_data VARCHAR2(100) := NULL;
3167 l_msg_count2 NUMBER := NULL;
3168 l_msg_data2 VARCHAR2(100) := NULL;
3169 l_return_status2 VARCHAR2(15) := NULL;
3170 l_return_status VARCHAR2(15) := NULL;
3171 --End: Fix for Bug 10628160
3172
3173
3174 BEGIN
3175
3176
3177
3178
3179 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3180 'Procedure Starts: Batch Id :'||p_batch_id);
3181
3182 SELECT batch_number, vendor_id, vendor_site_id, org_id,
3183 currency_code, batch_amount_threshold, batch_line_amount_threshold, claim_number, claim_minor_version,batch_type,owner_id
3184 INTO l_batch_number, l_vendor_id, l_vendor_site_id, l_org_id,
3185 l_par_batch_curr_code, l_batch_threshold, l_line_threshold, l_claim_number, l_claim_minor_version,l_batch_type,l_par_batch_owner_id
3186 FROM ozf_sd_batch_headers_all
3187 WHERE batch_id = p_batch_id;
3188
3189 ozf_sd_batch_pvt.create_batch_header(l_vendor_id,
3190 l_vendor_site_id,
3191 l_org_id,
3192 l_batch_threshold,
3193 l_line_threshold,
3194 l_par_batch_curr_code,
3195 'F' ,
3196 'APPROVED',
3197 l_claim_number||'_'||l_claim_minor_version,
3198 l_claim_minor_version,
3199 p_batch_id, -- current batch (i.e) parent batch_id
3200 l_new_batch_id,
3201 l_batch_type,
3202 l_par_batch_owner_id);
3203
3204 UPDATE ozf_sd_batch_lines_all
3205 SET batch_id = l_new_batch_id,
3206 object_version_number = object_version_number + 1,
3207 last_update_date = sysdate,
3208 last_updated_by = fnd_global.user_id
3209 WHERE batch_id = p_batch_id
3210 AND status_code = 'APPROVED'
3211 AND purge_flag <> 'Y';
3212
3213 --raising business event
3214 ozf_sd_util_pvt.sd_raise_event (l_new_batch_id, 'CREATE', x_return_status);
3215
3216 SAVEPOINT BEF_INVOKE_CLM_CHILD_BATCH;
3217
3218 --Start:Fix for Bug#10628160 - Create a Claim with new custom setup for -ve amount
3219 --to get claim number
3220 -- Check if the child batch is having -ve claim sum
3221 select sum(batch_curr_claim_amount)
3222 INTO l_tot_app_claim_amt
3223 from ozf_sd_batch_lines_all
3224 where batch_id=l_new_batch_id ;
3225
3226
3227
3228 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3229 ' l_tot_app_claim_amt = ' || l_tot_app_claim_amt);
3230
3231 IF (l_tot_app_claim_amt < 0) THEN
3232 l_claim_rec.claim_class := 'CHARGE';
3233 l_claim_rec.source_object_class := 'SD_SUPPLIER';
3234 OZF_CLAIM_DEF_RULE_PVT.get_clam_def_rule(p_claim_rec => l_claim_rec,
3235 x_clam_def_rec_type => l_clam_def_rec_type,
3236 x_return_status => l_return_status,
3237 x_msg_count => l_msg_count3,
3238 x_msg_data => l_msg_data);
3239 l_custom_setup_id := l_clam_def_rec_type.custom_setup_id;
3240
3241
3242 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3243 ' l_custom_setup_id' || to_char(l_custom_setup_id));
3244
3245 OZF_CLAIM_PVT.Get_Claim_Number(l_split_claim_id,
3246 l_custom_setup_id,
3247 l_claim_number,
3248 l_msg_data2,
3249 l_msg_count2,
3250 l_return_status2);
3251
3252
3253 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3254 ' Negative Claim Number = ' || l_claim_number);
3255
3256 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3257 ' l_new_batch_id = ' || l_new_batch_id);
3258
3259 UPDATE ozf_sd_batch_headers_all
3260 SET claim_number = l_claim_number
3261 WHERE batch_id = l_new_batch_id;
3262
3263 END IF;
3264
3265 --End:Fix for Bug#10628160
3266
3267 IF (l_tot_app_claim_amt) <> 0 THEN
3268
3269 PROCESS_CLAIM(l_new_batch_id, x_return_status, x_msg_data, l_claim_id);
3273 OZF_SD_UTIL_PVT.CREATE_ADJUSTMENT(l_new_batch_id, 'F', x_return_status, l_msg_count, x_msg_data);
3270
3271 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN -- If claim is successful
3272
3274
3275 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN -- If adjustment is successful
3276 --raising business event
3277 ozf_sd_util_pvt.sd_raise_event (l_new_batch_id, 'CLAIM', x_return_status);
3278 UPDATE ozf_sd_batch_headers_all
3279 SET status_code = 'CLOSED',
3280 claim_id = l_claim_id,
3281 last_update_date = sysdate,
3282 last_updated_by = fnd_global.user_id,
3283 object_version_number = object_version_number + 1
3284 WHERE batch_id = l_new_batch_id;
3285 ELSE
3286 ROLLBACK TO BEF_INVOKE_CLM_CHILD_BATCH;
3287 UPDATE ozf_sd_batch_headers_all
3288 SET status_code = 'PENDING_CLAIM',
3289 last_update_date = sysdate,
3290 last_updated_by = fnd_global.user_id,
3291 object_version_number = object_version_number + 1
3292 WHERE batch_id = l_new_batch_id;
3293 END IF; -- If adjustment is successful
3294
3295 ELSE -- If claim is not successful
3296 ROLLBACK TO BEF_INVOKE_CLM_CHILD_BATCH;
3297 UPDATE ozf_sd_batch_headers_all
3298 SET status_code = 'PENDING_CLAIM',
3299 last_update_date = sysdate,
3300 last_updated_by = fnd_global.user_id,
3301 object_version_number = object_version_number + 1
3302 WHERE batch_id = l_new_batch_id;
3303 END IF; -- If claim is successful
3304
3305 END IF ;
3306
3307 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
3308 'Procedure Ends');
3309
3310 END PROCESS_CHILD_BATCH;
3311
3312
3313
3314
3315
3316 PROCEDURE PROCESS_COMP_APPR_BATCH(p_batch_id IN NUMBER,
3317 x_return_status OUT nocopy VARCHAR2,
3318 x_msg_data OUT nocopy VARCHAR2) IS
3319
3320
3321 l_tot_app_claim_amt NUMBER ;
3322 l_claim_number VARCHAR2(30);
3323 l_claim_id NUMBER;
3324 l_claim_minor_version NUMBER;
3325 l_msg_count NUMBER;
3326 --Start: Fix for Bug 10628160
3327 l_custom_setup_id NUMBER := NULL;
3328 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
3329 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
3330 l_split_claim_id NUMBER := NULL;
3331 l_msg_count3 NUMBER := NULL;
3332 l_msg_data VARCHAR2(100) := NULL;
3333 l_msg_count2 NUMBER := NULL;
3334 l_msg_data2 VARCHAR2(100) := NULL;
3335 l_return_status2 VARCHAR2(15) := NULL;
3336 l_return_status VARCHAR2(15) := NULL;
3337 --End: Fix for Bug 10628160
3338
3339 BEGIN
3340
3341
3342 SAVEPOINT BEFORE_INVOKING_CLAIM;
3343
3344
3345
3346 -- Check if the batch is negative
3347 --Start:Fix for Bug#10628160 - Create a Claim with new custom setup for -ve amount
3348 --to get claim number
3349
3350 select sum(batch_curr_claim_amount) INTO l_tot_app_claim_amt from ozf_sd_batch_lines_all
3351 where batch_id=p_batch_id ;
3352
3353 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
3354 ' l_tot_app_claim_amt = ' || l_tot_app_claim_amt);
3355
3356 IF (l_tot_app_claim_amt < 0) THEN
3357 l_claim_rec.claim_class := 'CHARGE';
3358 l_claim_rec.source_object_class := 'SD_SUPPLIER';
3359 OZF_CLAIM_DEF_RULE_PVT.get_clam_def_rule(p_claim_rec => l_claim_rec,
3360 x_clam_def_rec_type => l_clam_def_rec_type,
3361 x_return_status => l_return_status,
3362 x_msg_count => l_msg_count3,
3363 x_msg_data => l_msg_data);
3364 l_custom_setup_id := l_clam_def_rec_type.custom_setup_id;
3365
3366
3367 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
3368 ' l_custom_setup_id' || to_char(l_custom_setup_id));
3369
3370 OZF_CLAIM_PVT.Get_Claim_Number(l_split_claim_id,
3371 l_custom_setup_id,
3372 l_claim_number,
3373 l_msg_data2,
3374 l_msg_count2,
3375 l_return_status2);
3376
3377
3378 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
3379 ' Negative Claim Number = ' || l_claim_number);
3380
3381 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
3382 ' l_new_batch_id = ' || p_batch_id);
3383
3384 UPDATE ozf_sd_batch_headers_all
3385 SET claim_number = l_claim_number
3386 WHERE batch_id = p_batch_id;
3387
3388 END IF;
3389
3390 --End:Fix for Bug#10628160
3391
3392
3393 IF (l_tot_app_claim_amt <> 0) THEN
3394
3395
3396 PROCESS_CLAIM(p_batch_id, x_return_status, x_msg_data, l_claim_id);
3397
3398 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3399
3400 OZF_SD_UTIL_PVT.CREATE_ADJUSTMENT(p_batch_id, 'F', x_return_status, l_msg_count, x_msg_data); -- to pass p_comp_wrt_off
3401
3402 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3403 --raising business event
3404 ozf_sd_util_pvt.sd_raise_event (p_batch_id, 'CLAIM', x_return_status);
3405 UPDATE ozf_sd_batch_headers_all
3406 SET status_code = 'CLOSED',
3407 claim_id = l_claim_id,
3408 last_update_date = sysdate,
3412 ELSE -- If adjustment is not successful
3409 last_updated_by = fnd_global.user_id,
3410 object_version_number = object_version_number + 1
3411 WHERE batch_id = p_batch_id;
3413 ROLLBACK TO BEFORE_INVOKING_CLAIM;
3414 UPDATE ozf_sd_batch_headers_all
3415 SET status_code = 'PENDING_CLAIM',
3416 last_update_date = sysdate,
3417 last_updated_by = fnd_global.user_id,
3418 object_version_number = object_version_number + 1
3419 WHERE batch_id = p_batch_id;
3420 END IF; -- If adjustment is successful
3421
3422 ELSE -- If claim is not successful
3423
3424 ROLLBACK TO BEFORE_INVOKING_CLAIM;
3425 UPDATE ozf_sd_batch_headers_all
3426 SET status_code = 'PENDING_CLAIM',
3427 last_update_date = sysdate,
3428 last_updated_by = fnd_global.user_id,
3429 object_version_number = object_version_number + 1
3430 WHERE batch_id = p_batch_id;
3431
3432 END IF; -- If claim is successful
3433
3434 END IF ;
3435
3436 END PROCESS_COMP_APPR_BATCH;
3437
3438
3439
3440 PROCEDURE update_dispute_data(p_batch_number varchar2,p_batch_id number) IS
3441
3442 type v_disputes IS TABLE OF VARCHAR2(50) INDEX BY binary_integer;
3443 l_disputes v_disputes;
3444 l_dispute_index NUMBER(2);
3445
3446 BEGIN
3447 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_dispute_data',
3448 'Procedure Starts: Batch number -> '||p_batch_number||' Batch Id -> '||p_batch_id );
3449
3450 FOR v_dispute_code IN (SELECT bl.batch_id,
3451 bl.batch_line_id,
3452 bint.gbl_claim_rej_code1,
3453 bint.gbl_claim_rej_code2,
3454 bint.gbl_claim_rej_code3,
3455 bint.gbl_claim_rej_code4,
3456 bint.gbl_claim_rej_code5,
3457 bint.gbl_claim_rej_code6,
3458 bint.gbl_claim_rej_code7,
3459 bint.gbl_claim_rej_code8,
3460 bint.gbl_claim_rej_code9,
3461 bint.gbl_claim_rej_code10
3462 FROM ozf_sd_batch_lines_all bl,
3463 ozf_sd_batch_lines_int_all bint
3464 WHERE bl.batch_id = bint.ship_frm_sd_claim_request_id
3465 AND bl.batch_line_number = bint.batch_line_number
3466 AND bint.GBL_CLAIM_DISPOSITION_CODE = 'REJECTED'
3467 AND bint.ship_frm_sd_claim_request_id = p_batch_number
3468 AND bint.processed_flag = 'N'
3469 AND bl.purge_flag <> 'Y'
3470 )
3471
3472 LOOP
3473
3474
3475 l_dispute_index := 0;
3476
3477 IF v_dispute_code.gbl_claim_rej_code1 IS NOT NULL THEN
3478 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code1;
3479 l_dispute_index := l_dispute_index + 1;
3480 END IF;
3481
3482 IF v_dispute_code.gbl_claim_rej_code2 IS NOT NULL THEN
3483 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code2;
3484 l_dispute_index := l_dispute_index + 1;
3485 END IF;
3486
3487 IF v_dispute_code.gbl_claim_rej_code3 IS NOT NULL THEN
3488 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code3;
3489 l_dispute_index := l_dispute_index + 1;
3490 END IF;
3491
3492 IF v_dispute_code.gbl_claim_rej_code4 IS NOT NULL THEN
3493 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code4;
3494 l_dispute_index := l_dispute_index + 1;
3495 END IF;
3496
3497 IF v_dispute_code.gbl_claim_rej_code5 IS NOT NULL THEN
3498 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code5;
3499 l_dispute_index := l_dispute_index + 1;
3500 END IF;
3501
3502 IF v_dispute_code.gbl_claim_rej_code6 IS NOT NULL THEN
3503 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code6;
3504 l_dispute_index := l_dispute_index + 1;
3505 END IF;
3506
3507 IF v_dispute_code.gbl_claim_rej_code7 IS NOT NULL THEN
3508 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code7;
3509 l_dispute_index := l_dispute_index + 1;
3510 END IF;
3511
3512 IF v_dispute_code.gbl_claim_rej_code8 IS NOT NULL THEN
3513 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code8;
3514 l_dispute_index := l_dispute_index + 1;
3515 END IF;
3516
3517 IF v_dispute_code.gbl_claim_rej_code9 IS NOT NULL THEN
3518 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code9;
3519 l_dispute_index := l_dispute_index + 1;
3520 END IF;
3521
3522 IF v_dispute_code.gbl_claim_rej_code10 IS NOT NULL THEN
3523 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code10;
3524 l_dispute_index := l_dispute_index + 1;
3525 END IF;
3526
3527 FOR i IN 0 .. l_dispute_index - 1 LOOP
3528
3529 INSERT INTO ozf_sd_batch_line_disputes
3530 (batch_line_dispute_id,
3531 batch_id,
3532 batch_line_id,
3533 object_version_number,
3534 dispute_code,
3535 review_flag,
3536 creation_date,
3537 last_update_date,
3538 last_updated_by,
3539 request_id,
3540 created_by,
3541 created_from,
3542 last_update_login,
3543 program_application_id,
3544 program_update_date,
3545 program_id,
3546 security_group_id)
3547 VALUES
3548 (ozf_sd_batch_line_disputes_s.nextval,
3549 v_dispute_code.batch_id,
3550 v_dispute_code.batch_line_id,
3551 1,
3552 l_disputes(i),
3553 NULL, --review flag
3554 sysdate,
3555 sysdate,
3556 fnd_global.user_id,
3557 fnd_global.conc_request_id,
3558 fnd_global.user_id,
3559 NULL, --created from
3560 fnd_global.conc_login_id,
3561 fnd_global.prog_appl_id, --l_program_application_id,
3562 NULL, --l_program_update_date,
3563 fnd_global.conc_program_id, -- p_Operating_Unit,
3564 fnd_global.security_group_id);
3565 END LOOP;
3566 END LOOP;
3567 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_dispute_data',
3568 'Procedure Ends');
3569 END update_dispute_data;
3570
3571 PROCEDURE process_claim(p_batch_id IN NUMBER,
3572 x_return_status OUT nocopy VARCHAR2,
3573 x_msg_data OUT nocopy VARCHAR2,
3574 x_claim_id OUT NOCOPY NUMBER
3575 ) IS
3576
3577 l_claim_id NUMBER := NULL;
3578 -- Incase auto claim is run
3579 l_claim_ret_status VARCHAR2(15) := NULL;
3580 l_claim_msg_count NUMBER := NULL;
3581 l_claim_msg_data VARCHAR2(500) := NULL;
3582 l_claim_type VARCHAR2(20) := 'SUPPLIER';
3583 --always defaulted to external claim
3584
3585 BEGIN
3586 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::process_claim',
3587 'Procedure Starts: Batch Id -> '||p_batch_id);
3588 --call claim API
3589 ozf_claim_accrual_pvt.initiate_sd_payment(1,
3590 fnd_api.g_false,
3591 fnd_api.g_true,
3592 fnd_api.g_valid_level_full,
3593 l_claim_ret_status,
3594 l_claim_msg_count,
3595 l_claim_msg_data,
3596 p_batch_id,
3597 l_claim_type,
3598 l_claim_id);
3599
3600 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::process_claim',
3601 'Return Status of claim API: '||l_claim_ret_status ||' Msg: '||l_claim_msg_data || 'Msg Count: '|| l_claim_msg_count);
3602
3603 FOR I IN 1..l_claim_msg_count LOOP
3604 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'OZF_SD_BATCH_FEED_PVT::process_claim',
3605 'Claim API Msg: '|| SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
3606 END LOOP;
3607
3608 x_return_status := l_claim_ret_status;
3609 x_msg_data := l_claim_msg_data;
3610 x_claim_id := l_claim_id;
3611
3612 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::process_claim',
3613 'Procedure Ends');
3614 END process_claim;
3615
3616 END ozf_sd_batch_feed_pvt;
3617
[Home] [Help]