[Home] [Help]
PACKAGE BODY: APPS.OZF_SD_BATCH_FEED_PVT
Source
1 PACKAGE BODY OZF_SD_BATCH_FEED_PVT as
2 /* $Header: ozfvsdfb.pls 120.21.12010000.10 2008/10/22 11:54:22 annsrini ship $ */
3
4 -- Start of Comments
5 -- Package name : OZF_SD_BATCH_FEED_PVT
6 -- Purpose :
7 -- History :
8 -- NOTE :
9 -- End of Comments
10
11 g_pkg_name constant VARCHAR2(30) := 'OZF_SD_BATCH_FEED_PVT';
12 g_file_name constant VARCHAR2(12) := 'ozfvsdfb.pls';
13
14 -- Author : MBHATT
15 -- Created : 11/16/2007 2:39:16 PM
16 -- Purpose :
17 -- Public function and procedure declarations
18 -- Private type declarations
19 PROCEDURE update_stale_data_batch_line(p_batch_number IN VARCHAR2,
20 p_batch_line_number IN VARCHAR2) AS
21
22 BEGIN
23 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');
24
25 UPDATE ozf_sd_batch_lines_int_all
26 SET processed_flag = 'S',
27 last_update_date = sysdate,
28 last_updated_by = fnd_global.user_id
29 WHERE ship_frm_sd_claim_request_id = p_batch_number
30 AND batch_line_number = p_batch_line_number
31 AND processed_flag = 'N';
32
33 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');
34
35 EXCEPTION
36 WHEN others THEN
37 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);
38
39 END update_stale_data_batch_line;
40
41 PROCEDURE populate_interface(p_batch_number IN VARCHAR2,
42 p_operating_unit IN VARCHAR2,
43 p_frm_cntct1_name IN VARCHAR2,
44 p_frm_cntct1_email IN VARCHAR2,
45 p_frm_cntct1_fax IN VARCHAR2,
46 p_frm_cntct1_phone IN VARCHAR2,
47 p_frm_gbl_ptnr_role_class_cd IN VARCHAR2,
48 p_frm_gbl_business_id IN NUMBER,
49 p_frm_gbl_supply_chain_cd IN VARCHAR2,
50 p_frm_business_name IN VARCHAR2,
51 p_frm_prop_business_id1 IN VARCHAR2,
52 p_frm_prop_domain_id1 IN VARCHAR2,
53 p_frm_auth_id1 IN VARCHAR2,
54 p_frm_gbl_ptnr_class_cd IN VARCHAR2,
55 p_frm_cntct2_name IN VARCHAR2,
56 p_frm_cntct2_email IN VARCHAR2,
57 p_frm_cntct2_fax IN VARCHAR2,
58 p_frm_cntct2_phone IN VARCHAR2,
59 p_frm_gbl_loc_id IN NUMBER,
60 p_frm_prop_domain_id2 IN VARCHAR2,
61 p_frm_prop_auth_id2 IN VARCHAR2,
62 p_frm_prop_loc_id IN VARCHAR2,
63 p_frm_add_line1 IN VARCHAR2,
64 p_frm_add_line2 IN VARCHAR2,
65 p_frm_add_line3 IN VARCHAR2,
66 p_frm_city IN VARCHAR2,
67 p_frm_country IN VARCHAR2,
68 p_frm_postal_code IN VARCHAR2,
69 p_frm_po_box_id IN VARCHAR2,
70 p_frm_region IN VARCHAR2,
71 p_gbl_doc_func_code IN VARCHAR2,
72 p_ship_from_sd_auth_id IN VARCHAR2,
73 p_dist_by_gbl_business_id IN NUMBER,
74 p_dist_by_gbl_supp_chain_cd IN VARCHAR2,
75 p_dist_by_business_name IN VARCHAR2,
76 p_dist_by_prop_business_id IN VARCHAR2,
77 p_dist_by_prop_domain_id1 IN VARCHAR2,
78 p_dist_by_prop_auth_id1 IN VARCHAR2,
79 p_dist_by_gbl_ptnr_class_cd IN VARCHAR2,
80 p_dist_by_cntct_name IN VARCHAR2,
81 p_dist_by_cntct_email IN VARCHAR2,
82 p_dist_by_cntct_fax IN VARCHAR2,
83 p_dist_by_cntct_phone IN VARCHAR2,
84 p_dist_by_gbl_loc_id IN NUMBER,
85 p_dist_by_prop_domain_id2 IN VARCHAR2,
86 p_dist_by_prop_auth_id2 IN VARCHAR2,
87 p_dist_by_prop_loc_id IN VARCHAR2,
88 p_dist_by_add_line1 IN VARCHAR2,
89 p_dist_by_add_line2 IN VARCHAR2,
90 p_dist_by_add_line3 IN VARCHAR2,
91 p_dist_by_city IN VARCHAR2,
92 p_dist_by_country IN VARCHAR2,
93 p_dist_by_postal_code IN VARCHAR2,
94 p_dist_by_po_box_id IN VARCHAR2,
95 p_dist_by_region IN VARCHAR2,
96 p_ship_to_business_name IN VARCHAR2,
97 p_ship_to_gbl_business_id IN NUMBER,
98 p_ship_to_gbl_supp_chain_cd IN VARCHAR2,
99 p_ship_to_prop_business_id IN VARCHAR2,
100 p_ship_to_prop_domain_id1 IN VARCHAR2,
101 p_ship_to_prop_auth_id1 IN VARCHAR2,
102 p_ship_to_gbl_ptnr_class_cd IN VARCHAR2,
103 p_ship_to_cust_cntct_name IN VARCHAR2,
104 p_ship_to_cust_cntct_email IN VARCHAR2,
105 p_ship_to_cust_cntct_fax IN VARCHAR2,
106 p_ship_to_cust_cntct_phone IN VARCHAR2,
107 p_ship_to_cust_gbl_loc_id IN NUMBER,
108 p_ship_to_prop_domain_id2 IN VARCHAR2,
109 p_ship_to_prop_auth_id2 IN VARCHAR2,
110 p_ship_to_cust_prop_loc_id IN VARCHAR2,
111 p_ship_to_cust_add1 IN VARCHAR2,
112 p_ship_to_cust_add2 IN VARCHAR2,
113 p_ship_to_cust_add3 IN VARCHAR2,
114 p_ship_to_cust_city IN VARCHAR2,
115 p_ship_to_cust_country IN VARCHAR2,
116 p_ship_to_cust_postal_code IN VARCHAR2,
117 p_ship_to_cust_po_box_id IN VARCHAR2,
118 p_ship_to_cust_region IN VARCHAR2,
119 p_sold_to_business_name IN VARCHAR2,
120 p_sold_to_gbl_business_id IN NUMBER,
121 p_sold_to_gbl_supp_chain_cd IN VARCHAR2,
122 p_sold_to_prop_business_id IN VARCHAR2,
123 p_sold_to_prop_domain_id1 IN VARCHAR2,
124 p_sold_to_prop_auth_id1 IN VARCHAR2,
125 p_sold_to_gbl_ptnr_class_cd IN VARCHAR2,
126 p_sold_to_cust_cntct_name IN VARCHAR2,
127 p_sold_to_cust_cntct_email IN VARCHAR2,
128 p_sold_to_cust_cntct_fax IN VARCHAR2,
129 p_sold_to_cust_cntct_phone IN VARCHAR2,
130 p_sold_to_cust_gbl_loc_id IN NUMBER,
131 p_sold_to_prop_domain_id2 IN VARCHAR2,
132 p_sold_to_prop_auth_id2 IN VARCHAR2,
133 p_sold_to_cust_prop_loc_id IN VARCHAR2,
134 p_sold_to_cust_add1 IN VARCHAR2,
135 p_sold_to_cust_add2 IN VARCHAR2,
136 p_sold_to_cust_add3 IN VARCHAR2,
137 p_sold_to_cust_city IN VARCHAR2,
138 p_sold_to_cust_country IN VARCHAR2,
139 p_sold_to_cust_postal_code IN VARCHAR2,
140 p_sold_to_cust_po_box_id IN VARCHAR2,
141 p_sold_to_cust_region IN VARCHAR2,
142 p_end_cust_business_name IN VARCHAR2,
143 p_end_cust_gbl_business_id IN NUMBER,
144 p_end_cust_gbl_supp_chain_cd IN VARCHAR2,
145 p_end_cust_prop_business_id IN VARCHAR2,
146 p_end_cust_prop_domain_id1 IN VARCHAR2,
147 p_end_cust_prop_auth_id1 IN VARCHAR2,
148 p_end_cust_gbl_ptnr_class_cd IN VARCHAR2,
149 p_end_cust_cntct_name IN VARCHAR2,
150 p_end_cust_cntct_email IN VARCHAR2,
151 p_end_cust_cntct_fax IN VARCHAR2,
152 p_end_cust_cntct_phone IN VARCHAR2,
153 p_end_cust_gbl_loc_id IN NUMBER,
154 p_end_cust_prop_domain_id2 IN VARCHAR2,
155 p_end_cust_prop_auth_id2 IN VARCHAR2,
156 p_end_cust_prop_loc_id IN VARCHAR2,
157 p_end_cust_add1 IN VARCHAR2,
158 p_end_cust_add2 IN VARCHAR2,
159 p_end_cust_add3 IN VARCHAR2,
160 p_end_cust_city IN VARCHAR2,
161 p_end_cust_country IN VARCHAR2,
162 p_end_cust_postal_code IN VARCHAR2,
163 p_end_cust_po_box_id IN VARCHAR2,
164 p_end_cust_region IN VARCHAR2,
165 p_ship_frm_sd_claim_req_date IN DATE,
166 p_ship_frm_sd_claim_req_id IN VARCHAR2,
167 p_credit_ref_id IN VARCHAR2,
168 p_debit_ref_id IN VARCHAR2,
169 p_batch_line_id IN NUMBER,
170 p_batch_line_number IN NUMBER,
171 p_order_date IN DATE,
172 p_order_line_number IN NUMBER,
173 p_order_number IN NUMBER,
174 p_invoice_date IN DATE,
175 p_invoice_line_number IN NUMBER,
176 p_invoice_number IN VARCHAR2,
177 p_cost_price IN NUMBER,
178 p_cost_price_curr_code IN VARCHAR2,
179 p_auth_price IN NUMBER,
180 p_auth_price_curr_code IN VARCHAR2,
181 p_resale_price IN NUMBER,
182 p_resale_price_curr_code IN VARCHAR2,
183 p_uom IN VARCHAR2,
184 p_line_status IN VARCHAR2,
185 p_disposition_code1 IN VARCHAR2,
186 p_disposition_code2 IN VARCHAR2,
187 p_disposition_code3 IN VARCHAR2,
188 p_disposition_code4 IN VARCHAR2,
189 p_disposition_code5 IN VARCHAR2,
190 p_disposition_code6 IN VARCHAR2,
191 p_disposition_code7 IN VARCHAR2,
192 p_disposition_code8 IN VARCHAR2,
193 p_disposition_code9 IN VARCHAR2,
194 p_disposition_code10 IN VARCHAR2,
195 p_vendor_part_number IN VARCHAR2,
196 p_dist_part_number IN VARCHAR2,
197 p_date_shipped IN DATE,
198 p_qty_shipped IN NUMBER,
199 p_claim_amt_curr_code IN VARCHAR2,
200 p_last_sub_claim_amt IN NUMBER,
201 p_vendor_auth_line_item_no IN VARCHAR2,
202 p_vendor_apprvd_amt IN NUMBER,
203 p_vendor_apprvd_amt_curr_cd IN VARCHAR2,
204 p_batch_submission_date IN DATE,
205 p_batch_id IN NUMBER,
206 p_to_cntct1_name IN VARCHAR2,
207 p_to_cntct1_email IN VARCHAR2,
208 p_to_cntct1_fax IN VARCHAR2,
209 p_to_cntct1_phone IN VARCHAR2,
210 p_to_gbl_ptnr_role_class_cd IN VARCHAR2,
211 p_to_gbl_business_id IN NUMBER,
212 p_to_gbl_supply_chain_cd IN VARCHAR2,
213 p_to_business_name IN VARCHAR2,
214 p_to_prop_business_id1 IN VARCHAR2,
215 p_to_prop_domain_id1 IN VARCHAR2,
216 p_to_auth_id1 IN VARCHAR2,
217 p_to_gbl_ptnr_class_cd IN VARCHAR2,
218 p_to_cntct2_name IN VARCHAR2,
219 p_to_cntct2_email IN VARCHAR2,
220 p_to_cntct2_fax IN VARCHAR2,
221 p_to_cntct2_phone IN VARCHAR2,
222 p_to_gbl_loc_id IN VARCHAR2,
223 p_to_prop_domain_id2 IN VARCHAR2,
224 p_to_prop_auth_id2 IN VARCHAR2,
225 p_to_prop_loc_id IN VARCHAR2,
226 p_to_add_line1 IN VARCHAR2,
227 p_to_add_line2 IN VARCHAR2,
228 p_to_add_line3 IN VARCHAR2,
229 p_to_city IN VARCHAR2,
230 p_to_country IN VARCHAR2,
231 p_to_postal_code IN VARCHAR2,
232 p_to_po_box_id IN VARCHAR2,
233 p_to_region IN VARCHAR2) AS
234 l_seq NUMBER;
235 l_batch_status VARCHAR2(15);
236
237 BEGIN
238 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface', 'Procedure Starts');
239
240 ozf_sd_batch_feed_pvt.update_stale_data_batch_line(p_batch_number, p_batch_line_number);
241
242 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface',
243 'WebADI Batch - before inserting into interface table');
244
245 BEGIN
246 INSERT INTO ozf_sd_batch_lines_int_all
247 (batch_line_int_id,
248 frm_contact1_name,
249 frm_contact1_email,
250 frm_contact1_fax,
251 frm_contact1_phone,
252 frm_gbl_ptnr_role_class_code,
253 frm_business_name,
254 frm_gbl_business_id,
255 frm_gbl_supply_chain_code,
256 frm_prop_business_id,
257 frm_prop_domain_id1,
258 frm_prop_id_authority1,
259 frm_gbl_partner_class_code,
260 frm_contact2_name,
261 frm_contact2_email,
262 frm_contact2_fax,
263 frm_contact2_phone,
264 frm_gbl_location_id,
265 frm_prop_domain_id2,
266 frm_prop_id_authority2,
267 frm_prop_location_id,
268 frm_address_line1,
269 frm_address_line2,
270 frm_address_line3,
271 frm_city_name,
272 frm_gbl_country_code,
273 frm_national_postal_code,
274 frm_pobox_id,
275 frm_region_name,
276 gbl_doc_function_code,
277 ship_frm_sd_authorization_id,
278 dist_by_business_name,
279 dist_by_gbl_business_id,
280 dist_by_gbl_supply_chain_code,
281 dist_by_prop_business_id,
282 dist_by_prop_domain_id1,
283 dist_by_prop_idauthority1,
284 dist_by_gbl_partner_class_code,
285 dist_by_contact1_name,
286 dist_by_contact1_email,
287 dist_by_contact1_fax,
288 dist_by_contact1_phone,
289 dist_by_gbl_location_id,
290 dist_by_prop_domain_id2,
291 dist_by_prop_idauthority2,
292 dist_by_prop_location_id,
293 dist_by_address_line1,
294 dist_by_address_line2,
295 dist_by_address_line3,
296 dist_by_city_name,
297 dist_by_gbl_country_code,
298 dist_by_national_postal_code,
299 dist_by_pobox_id,
300 dist_by_region_name,
301 ship_to_business_name,
302 ship_to_gbl_business_id,
303 ship_to_gbl_supply_chain_code,
304 ship_to_prop_business_id,
305 ship_to_prop_domain_id1,
306 ship_to_prop_id_authority1,
307 ship_to_gbl_partner_class_code,
308 ship_to_contact1_name,
309 ship_to_contact1_email,
310 ship_to_contact1_fax,
311 ship_to_contact1_phone,
312 ship_to_gbl_location_id,
313 ship_to_prop_domain_id2,
314 ship_to_prop_id_authority2,
315 ship_to_prop_location_id,
316 ship_to_address_line1,
317 ship_to_address_line2,
318 ship_to_address_line3,
319 ship_to_city_name,
320 ship_to_gbl_country_code,
321 ship_to_national_postal_code,
322 ship_to_pobox_id,
323 ship_to_region_name,
324 sold_to_business_name,
325 sold_to_gbl_business_id,
326 sold_to_gbl_supply_chain_code,
327 sold_to_prop_business_id,
328 sold_to_prop_domain_id1,
329 sold_to_prop_id_authority1,
330 sold_to_gbl_partner_class_code,
331 sold_to_contact1_name,
332 sold_to_contact1_email,
333 sold_to_contact1_fax,
334 sold_to_contact1_phone,
335 sold_to_gbl_location_id,
336 sold_to_prop_domain_id2,
337 sold_to_prop_id_authority2,
338 sold_to_prop_location_id,
339 sold_to_address_line1,
340 sold_to_address_line2,
341 sold_to_address_line3,
342 sold_to_city_name,
343 sold_to_gbl_country_code,
344 sold_to_national_postal_code,
345 sold_to_pobox_id,
346 sold_to_region_name,
347 used_by_business_name,
348 used_by_gbl_business_id,
349 used_by_gbl_supply_chain_code,
350 used_by_prop_business_id,
351 used_by_prop_domain_id1,
352 used_by_prop_id_authority1,
353 used_by_gbl_partner_class_code,
354 used_by_contact1_name,
355 used_by_contact1_email,
356 used_by_contact1_fax,
357 used_by_contact1_phone,
358 used_by_gbl_location_id,
359 used_by_prop_domain_id2,
360 used_by_prop_id_authority2,
361 used_by_prop_location_id,
362 used_by_address_line1,
363 used_by_address_line2,
364 used_by_address_line3,
365 used_by_city_name,
366 used_by_gbl_country_code,
367 used_by_national_postal_code,
368 used_by_pobox_id,
369 used_by_region_name,
370 ship_frm_sd_claim_request_date,
371 ship_frm_sd_claim_request_id,
372 credit_reference_id,
373 debit_reference_id,
374 batch_line_number,
375 order_date,
376 order_line_number,
377 order_number,
378 invoice_date,
379 invoice_line_number,
380 invoice_number,
381 cost_currency_code,
382 cost_monetary_amount,
383 auth_cost_currency_code,
384 auth_cost_monetary_amount,
385 resale_currency_code,
386 resale_monetary_amount,
387 gbl_uom,
388 gbl_claim_disposition_code,
389 vendor_product_id,
390 dist_product_id,
391 ship_date,
392 shipped_quantity,
393 vendor_auth_lineitem_line_nbr,
394 vendor_auth_cost_currency_code,
395 vendor_auth_cost_monetary_amt,
396 this_doc_generation_date,
397 this_document_id,
398 to_contact1_name,
399 to_contact1_email,
400 to_contact1_fax,
401 to_contact1_phone,
402 to_gbl_partner_role_class_code,
403 to_business_name1,
404 to_gbl_business_id,
405 to_gbl_supply_chain_code,
406 to_prop_business_id,
407 to_prop_domain_id1,
408 to_prop_id_authority1,
409 to_gbl_partner_class_code,
410 to_contact2_name,
411 to_contact2_email,
412 to_contact2_fax,
413 to_contact2_phone,
414 to_gbl_location_id,
415 to_prop_domain_id2,
416 to_prop_id_authority2,
417 to_prop_location_id,
418 to_address_line1,
419 to_address_line2,
420 to_address_line3,
421 to_city_name,
422 to_gbl_country_code,
423 to_national_postal_code,
424 to_pobox_id,
425 to_region_name,
426 creation_date,
427 last_update_date,
428 last_updated_by,
429 created_by,
430 processed_flag,
431 batch_id,
432 batch_line_id,
433 gbl_claim_rej_code1,
434 gbl_claim_rej_code2,
435 gbl_claim_rej_code3,
436 gbl_claim_rej_code4,
437 gbl_claim_rej_code5,
438 gbl_claim_rej_code6,
439 gbl_claim_rej_code7,
440 gbl_claim_rej_code8,
441 gbl_claim_rej_code9,
442 gbl_claim_rej_code10)
443 VALUES
444 (ozf_sd_batch_lines_int_all_s.nextval,
445 p_to_cntct1_name,
446 p_to_cntct1_email,
447 p_to_cntct1_fax,
448 p_to_cntct1_phone,
449 p_to_gbl_ptnr_role_class_cd,
450 p_to_business_name,
451 p_to_gbl_business_id,
452 p_to_gbl_supply_chain_cd,
453 p_to_prop_business_id1,
454 p_to_prop_domain_id1,
455 p_to_auth_id1,
456 p_to_gbl_ptnr_class_cd,
457 p_to_cntct2_name,
458 p_to_cntct2_email,
459 p_to_cntct2_fax,
460 p_to_cntct2_phone,
461 p_to_gbl_loc_id,
462 p_to_prop_domain_id2,
463 p_to_prop_auth_id2,
464 p_to_prop_loc_id,
465 p_to_add_line1,
466 p_to_add_line2,
467 p_to_add_line3,
468 p_to_city,
469 p_to_country,
470 p_to_postal_code,
471 p_to_po_box_id,
472 p_to_region,
473 'RESPONSE',
474 p_ship_from_sd_auth_id,
475 p_dist_by_business_name,
476 p_dist_by_gbl_business_id,
477 'Electronic Components', --P_DIST_BY_GBL_SUPP_CHAIN_CD ,
478 p_dist_by_prop_business_id,
479 p_dist_by_prop_domain_id1,
480 p_dist_by_prop_auth_id1,
481 'Distributor', --P_DIST_BY_GBL_PTNR_CLASS_CD ,
482 p_dist_by_cntct_name,
483 p_dist_by_cntct_email,
484 p_dist_by_cntct_fax,
485 p_dist_by_cntct_phone,
486 p_dist_by_gbl_loc_id,
487 p_dist_by_prop_domain_id2,
488 p_dist_by_prop_auth_id2,
489 p_dist_by_prop_loc_id,
490 p_dist_by_add_line1,
491 p_dist_by_add_line2,
492 p_dist_by_add_line3,
493 p_dist_by_city,
494 p_dist_by_country,
495 p_dist_by_postal_code,
496 p_dist_by_po_box_id,
497 p_dist_by_region,
498 p_ship_to_business_name,
499 p_ship_to_gbl_business_id,
500 p_ship_to_gbl_supp_chain_cd,
501 p_ship_to_prop_business_id,
502 p_ship_to_prop_domain_id1,
503 p_ship_to_prop_auth_id1,
504 p_ship_to_gbl_ptnr_class_cd,
505 p_ship_to_cust_cntct_name,
506 p_ship_to_cust_cntct_email,
507 p_ship_to_cust_cntct_fax,
508 p_ship_to_cust_cntct_phone,
509 p_ship_to_cust_gbl_loc_id,
510 p_ship_to_prop_domain_id2,
511 p_ship_to_prop_auth_id2,
512 p_ship_to_cust_prop_loc_id,
513 p_ship_to_cust_add1,
514 p_ship_to_cust_add2,
515 p_ship_to_cust_add3,
516 p_ship_to_cust_city,
517 p_ship_to_cust_country,
518 p_ship_to_cust_postal_code,
519 p_ship_to_cust_po_box_id,
520 p_ship_to_cust_region,
521 p_sold_to_business_name,
522 p_sold_to_gbl_business_id,
523 p_sold_to_gbl_supp_chain_cd,
524 p_sold_to_prop_business_id,
525 p_sold_to_prop_domain_id1,
526 p_sold_to_prop_auth_id1,
527 p_sold_to_gbl_ptnr_class_cd,
528 p_sold_to_cust_cntct_name,
529 p_sold_to_cust_cntct_email,
530 p_sold_to_cust_cntct_fax,
531 p_sold_to_cust_cntct_phone,
532 p_sold_to_cust_gbl_loc_id,
533 p_sold_to_prop_domain_id2,
534 p_sold_to_prop_auth_id2,
535 p_sold_to_cust_prop_loc_id,
536 p_sold_to_cust_add1,
537 p_sold_to_cust_add2,
538 p_sold_to_cust_add3,
539 p_sold_to_cust_city,
540 p_sold_to_cust_country,
541 p_sold_to_cust_postal_code,
542 p_sold_to_cust_po_box_id,
543 p_sold_to_cust_region,
544 p_end_cust_business_name,
545 p_end_cust_gbl_business_id,
546 p_end_cust_gbl_supp_chain_cd,
547 p_end_cust_prop_business_id,
548 p_end_cust_prop_domain_id1,
549 p_end_cust_prop_auth_id1,
550 p_end_cust_gbl_ptnr_class_cd,
551 p_end_cust_cntct_name,
552 p_end_cust_cntct_email,
553 p_end_cust_cntct_fax,
554 p_end_cust_cntct_phone,
555 p_end_cust_gbl_loc_id,
556 p_end_cust_prop_domain_id2,
557 p_end_cust_prop_auth_id2,
558 p_end_cust_prop_loc_id,
559 p_end_cust_add1,
560 p_end_cust_add2,
561 p_end_cust_add3,
562 p_end_cust_city,
563 p_end_cust_country,
564 p_end_cust_postal_code,
565 p_end_cust_po_box_id,
566 p_end_cust_region,
567 p_ship_frm_sd_claim_req_date,
568 p_batch_number, --Inserting batch_number for SHIP_FRM_SD_CLAIM_REQ_ID to be in sync with xml
569 p_credit_ref_id,
570 p_debit_ref_id,
571 p_batch_line_number,
572 p_order_date,
573 p_order_line_number,
574 p_order_number,
575 p_invoice_date,
576 p_invoice_line_number,
577 p_invoice_number,
578 p_cost_price_curr_code,
579 p_cost_price,
580 p_auth_price_curr_code,
581 p_auth_price,
582 p_resale_price_curr_code,
583 p_resale_price,
584 p_uom,
585 p_line_status,
586 p_vendor_part_number,
587 p_dist_part_number,
588 p_date_shipped,
589 p_qty_shipped,
590 p_vendor_auth_line_item_no,
591 p_vendor_apprvd_amt_curr_cd,
592 p_vendor_apprvd_amt,
593 p_batch_submission_date,
594 p_batch_number,
595 p_frm_cntct1_name,
596 p_frm_cntct1_email,
597 p_frm_cntct1_fax,
598 p_frm_cntct1_phone,
599 p_frm_gbl_ptnr_role_class_cd,
600 p_frm_business_name,
601 p_frm_gbl_business_id,
602 p_frm_gbl_supply_chain_cd,
603 p_frm_prop_business_id1,
604 p_frm_prop_domain_id1,
605 p_frm_auth_id1,
606 p_frm_gbl_ptnr_class_cd,
607 p_frm_cntct2_name,
608 p_frm_cntct2_email,
609 p_frm_cntct2_fax,
610 p_frm_cntct2_phone,
611 p_frm_gbl_loc_id,
612 p_frm_prop_domain_id2,
613 p_frm_prop_auth_id2,
614 p_frm_prop_loc_id,
615 p_frm_add_line1,
616 p_frm_add_line2,
617 p_frm_add_line3,
618 p_frm_city,
619 p_frm_country,
620 p_frm_postal_code,
621 p_frm_po_box_id,
622 p_frm_region,
623 sysdate,
624 sysdate,
625 0,
626 0,
627 'N',
628 p_batch_id,
629 p_batch_line_id,
630 p_disposition_code1,
631 p_disposition_code2,
632 p_disposition_code3,
633 p_disposition_code4,
634 p_disposition_code5,
635 p_disposition_code6,
636 p_disposition_code7,
637 p_disposition_code8,
638 p_disposition_code9,
639 p_disposition_code10);
640
641 EXCEPTION
642 WHEN others THEN
643 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface', 'Exception: '||sqlerrm);
644 END;
645
646 COMMIT;
647 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::populate_interface', 'Procedure Ends');
648
649 END populate_interface;
650
651 PROCEDURE update_data(p_batch_number IN VARCHAR2,
652 x_return_status OUT nocopy VARCHAR2,
653 x_msg_data OUT nocopy VARCHAR2
654 ) IS
655 l_cnt_hdr NUMBER;
656 l_cnt_line NUMBER;
657 l_cnt_tot_line NUMBER;
658 l_cnt_approved_lines NUMBER;
659 l_cnt_rejected_lines NUMBER;
660 l_message VARCHAR2(500);
661 l_to_amount NUMBER;
662 l_claim_number NUMBER;
663 l_accepted_amount NUMBER;
664 l_batch_id NUMBER;
665 l_batch_status VARCHAR2(15);
666 l_currency_code VARCHAR2(15);
667 l_msg_count NUMBER;
668 BEGIN
669
670 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'Procedure Starts');
671
672 x_return_status := fnd_api.g_ret_sts_success;
673
674 --Retreive batch id
675 BEGIN
676 SELECT batch_id, status_code
677 INTO l_batch_id, l_batch_status
678 FROM ozf_sd_batch_headers_all
679 WHERE batch_number = p_batch_number;
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 UPDATE ozf_sd_batch_lines_int_all
683 SET validation_txt = 'Batch ID does not exist',
684 last_update_date = sysdate,
685 last_updated_by = fnd_global.user_id
686 WHERE ship_frm_sd_claim_request_id = p_batch_number
687 AND processed_flag = 'N';
688 COMMIT;
689 x_return_status := fnd_api.g_ret_sts_error;
690 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_BATCH_INVALID');
691 RETURN;
692 WHEN OTHERS THEN
693 x_return_status := fnd_api.g_ret_sts_error;
694 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_BATCH_INVALID');
695 RETURN;
696 END;
697
698 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
699 'Batch Id: '||l_batch_id||' and Batch Status: '||l_batch_status);
700
701 --raising business event
702 ozf_sd_util_pvt.sd_raise_event (l_batch_id, 'RESPONSE', x_return_status);
703
704 IF l_batch_status NOT IN ('WIP','SUBMITTED' ) THEN
705 UPDATE ozf_sd_batch_lines_int_all
706 SET processed_flag = 'E',
707 validation_txt = 'Batch not in WIP or SUBMITTED status',
708 last_update_date = sysdate,
709 last_updated_by = fnd_global.user_id
710 WHERE ship_frm_sd_claim_request_id = p_batch_number
711 AND processed_flag = 'N';
712 COMMIT;
713 x_return_status := fnd_api.g_ret_sts_error;
714 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_BATCH_STATUS_INVALID');
715 RETURN;
716 END IF;
717
718 --check whether lines exist for given batch id
719 SELECT COUNT(1)
720 INTO l_cnt_line
721 FROM ozf_sd_batch_lines_int_all
722 WHERE ship_frm_sd_claim_request_id = p_batch_number
723 AND processed_flag = 'N';
724
725 IF l_cnt_line = 0 THEN
726 UPDATE ozf_sd_batch_lines_int_all
727 SET validation_txt = 'There are no Lines for this Batch ID',
728 last_update_date = sysdate,
729 last_updated_by = fnd_global.user_id
730 WHERE ship_frm_sd_claim_request_id = p_batch_number
731 AND processed_flag = 'N';
732 COMMIT;
733 x_return_status := fnd_api.g_ret_sts_error;
734 x_msg_data := 'There are no Lines for this Batch ID';
735 RETURN;
736 END IF;
737
738 BEGIN
739 DELETE ozf_sd_batch_line_disputes
740 WHERE batch_id = l_batch_id
741 AND batch_line_id IN (SELECT bl.batch_line_id
742 FROM ozf_sd_batch_lines_all bl, ozf_sd_batch_lines_int_all intr
743 WHERE bl.batch_id = l_batch_id
744 AND bl.batch_id = intr.ship_frm_sd_claim_request_id
745 AND bl.batch_line_number = intr.batch_line_number
746 AND intr.processed_flag = 'N'
747 AND bl.purge_flag <> 'Y'
748 );
749 EXCEPTION
750 WHEN others THEN
751 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
752 'Exception Delete OZF_SD_BATCH_LINE_DISPUTES: '||sqlerrm);
753 END;
754
755 FOR v_vendor_ref IN (SELECT batch_line_number, credit_reference_id
756 FROM ozf_sd_batch_lines_int_all
757 WHERE ship_frm_sd_claim_request_id = p_batch_number
758 AND processed_flag = 'N')
759
760 LOOP
761
762 UPDATE ozf_sd_batch_lines_all
763 SET vendor_ref_id = v_vendor_ref.credit_reference_id
764 WHERE batch_id = l_batch_id
765 AND batch_line_number = v_vendor_ref.batch_line_number
766 AND purge_flag <> 'Y';
767
768 END LOOP;
769
770 -- cursor to check if lines are in SUBMITTED status (response for lines in already REJECTED status should be marked as error )
771 FOR v_batch_lines IN (SELECT batch_id, batch_line_number, batch_line_id, status_code
772 FROM ozf_sd_batch_lines_all
773 WHERE batch_id = l_batch_id
774 AND purge_flag <> 'Y')
775 LOOP
776 IF v_batch_lines.status_code <> 'SUBMITTED' THEN
777 UPDATE ozf_sd_batch_lines_int_all
778 SET validation_txt = 'Batch Line not in SUBMITTED status',
779 GBL_CLAIM_REJ_CODE10 = 'OZF_SD_DATA_ERROR',
780 last_update_date = sysdate,
781 last_updated_by = fnd_global.user_id
782 WHERE ship_frm_sd_claim_request_id = v_batch_lines.batch_id
783 AND batch_line_number = v_batch_lines.batch_line_number
784 AND processed_flag = 'N';
785
786
787 x_return_status := fnd_api.g_ret_sts_error;
788 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR');
789 END IF;
790 END LOOP;
791
792 FOR v_missing_invalid_sts_lines IN (SELECT batch_line_number,
793 vendor_auth_cost_monetary_amt, vendor_auth_cost_currency_code
794 FROM ozf_sd_batch_lines_int_all
795 WHERE ship_frm_sd_claim_request_id = p_batch_number
796 AND processed_flag = 'N'
797 AND ( (gbl_claim_disposition_code IS NULL) OR ( gbl_claim_disposition_code NOT IN ('APPROVED', 'REJECTED') ) )
798 )
799 LOOP
800
801 UPDATE ozf_sd_batch_lines_int_all
802 SET gbl_claim_rej_code10 = 'OZF_SD_NO_RESPONSE',
803 validation_txt = 'STATUS CODE IS EITHER MISSING OR NOT IN APPROVED/REJECTED',
804 last_update_date = sysdate,
805 last_updated_by = fnd_global.user_id
806 WHERE ship_frm_sd_claim_request_id = p_batch_number
807 AND batch_line_number = v_missing_invalid_sts_lines.batch_line_number;
808
809 x_return_status := fnd_api.g_ret_sts_error;
810 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR');
811
812 UPDATE ozf_sd_batch_lines_all
813 SET status_code = 'REJECTED',
814 approved_amount = v_missing_invalid_sts_lines.vendor_auth_cost_monetary_amt,
815 approved_currency_code = v_missing_invalid_sts_lines.vendor_auth_cost_currency_code,
816 object_version_number = object_version_number + 1,
817 last_update_date = sysdate,
818 last_updated_by = fnd_global.user_id
819 WHERE batch_id = l_batch_id
820 AND batch_line_number = v_missing_invalid_sts_lines.batch_line_number
821 AND purge_flag <> 'Y' ;
822
823 END LOOP;
824
825 --Update Lines for Rejected lines
826 FOR v_reject_lines IN (SELECT batch_line_number, vendor_auth_cost_monetary_amt,
827 vendor_auth_cost_currency_code
828 FROM ozf_sd_batch_lines_int_all
829 WHERE ship_frm_sd_claim_request_id = p_batch_number
830 AND processed_flag = 'N'
831 AND gbl_claim_disposition_code = 'REJECTED')
832 LOOP
833
834 UPDATE ozf_sd_batch_lines_all
835 SET status_code = 'REJECTED',
836 approved_amount = v_reject_lines.vendor_auth_cost_monetary_amt,
837 approved_currency_code = v_reject_lines.vendor_auth_cost_currency_code,
838 object_version_number = object_version_number + 1,
839 last_update_date = sysdate,
840 last_updated_by = fnd_global.user_id
841 WHERE batch_id = l_batch_id
842 AND batch_line_number = v_reject_lines.batch_line_number
843 AND purge_flag <> 'Y';
844
845 END LOOP;
846
847 --Update Lines for Accepted lines
848 FOR v_accepted_lines IN (SELECT batch_line_number
849 FROM ozf_sd_batch_lines_int_all
850 WHERE ship_frm_sd_claim_request_id = p_batch_number
851 AND processed_flag = 'N'
852 AND gbl_claim_disposition_code = 'APPROVED')
853 LOOP
854
855 UPDATE ozf_sd_batch_lines_all
856 SET status_code = 'APPROVED',
857 approved_amount = agreement_price,
858 approved_currency_code = agreement_currency_code,
859 object_version_number = object_version_number + 1,
860 last_update_date = sysdate,
861 last_updated_by = fnd_global.user_id
862 WHERE batch_id = l_batch_id
863 AND batch_line_number = v_accepted_lines.batch_line_number
864 AND purge_flag <> 'Y';
865
866 END LOOP;
867
868 --get total number of lines
869 SELECT COUNT(1)
870 INTO l_cnt_tot_line
871 FROM ozf_sd_batch_lines_all
872 WHERE batch_id = l_batch_id
873 AND purge_flag <> 'Y';
874
875 --get number of approved lines
876 SELECT COUNT(1)
877 INTO l_cnt_approved_lines
878 FROM ozf_sd_batch_lines_all
879 WHERE batch_id = l_batch_id
880 AND status_code = 'APPROVED'
881 AND purge_flag <> 'Y';
882
883 --get number of rejected lines
884 SELECT COUNT(1)
885 INTO l_cnt_rejected_lines
886 FROM ozf_sd_batch_lines_all
887 WHERE batch_id = l_batch_id
888 AND status_code = 'REJECTED'
889 AND purge_flag <> 'Y';
890
891 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);
892 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);
893 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);
894
895 --All lines are rejected or if none approved
896
897 IF ( ( l_cnt_rejected_lines = l_cnt_tot_line) OR ( l_cnt_approved_lines = 0 ) ) THEN
898
899 update_rejected_line_data(p_batch_number, l_batch_id, l_batch_id, x_return_status , x_msg_data );
900
901 UPDATE ozf_sd_batch_headers_all
902 SET status_code = 'WIP',
903 object_version_number = object_version_number + 1,
904 last_update_date = sysdate,
905 last_updated_by = fnd_global.user_id
906 WHERE batch_id = l_batch_id;
907
908
909
910 UPDATE ozf_sd_batch_lines_int_all
911 SET processed_flag = 'Y',
912 last_update_date = sysdate,
913 last_updated_by = fnd_global.user_id
914 WHERE ship_frm_sd_claim_request_id = p_batch_number
915 AND processed_flag = 'N';
916
917 COMMIT;
918 RETURN;
919
920 END IF;
921
922 -- All lines APPROVED case
923 IF l_cnt_approved_lines = l_cnt_tot_line THEN
924
925 --when all lines of WIP/submitted batch get approved, then claim_number to be created without minor_version
926 IF l_batch_status IN ('WIP','SUBMITTED' ) THEN
927 UPDATE ozf_sd_batch_headers_all
928 SET -- claim_number = substr(claim_number,1,instr(claim_number,'_')-1) ,
929 claim_minor_version = NULL
930 where batch_id = l_batch_id;
931 END IF;
932
933 UPDATE ozf_sd_batch_headers_all
934 SET status_code = 'APPROVED',
935 object_version_number = object_version_number + 1,
936 last_update_date = sysdate,
937 last_updated_by = fnd_global.user_id
938 WHERE batch_id = l_batch_id;
939
940 --Mark all rows as processed
941 UPDATE ozf_sd_batch_lines_int_all
942 SET processed_flag = 'Y',
943 last_update_date = sysdate,
944 last_updated_by = fnd_global.user_id
945 WHERE ship_frm_sd_claim_request_id = p_batch_number
946 AND processed_flag = 'N';
947
948 COMMIT;
949
950 process_claim(l_batch_id, x_return_status, x_msg_data);
951
952 --raising business event
953 ozf_sd_util_pvt.sd_raise_event (l_batch_id, 'CLAIM', x_return_status);
954
955 COMMIT;
956 END IF;
957
958 IF ( ( l_cnt_approved_lines > 0 ) AND ( l_cnt_approved_lines <> l_cnt_tot_line ) ) THEN
959
960 batch_create_approved_line_new(l_batch_id, x_return_status, x_msg_data);
961
962 --Mark all rows as processed
963 UPDATE ozf_sd_batch_lines_int_all
964 SET processed_flag = 'Y',
965 last_update_date = sysdate,
966 last_updated_by = fnd_global.user_id
967 WHERE ship_frm_sd_claim_request_id = p_batch_number
968 AND processed_flag = 'N';
969
970 COMMIT;
971 RETURN;
972 END IF;
973
974 IF x_return_status = fnd_api.g_ret_sts_error THEN
975 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
976 'Entered throwing exception' || x_msg_data);
977 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
978 fnd_message.set_token('MESSAGE', x_msg_data);
979 RAISE fnd_api.g_exc_error;
980 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
981 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
982 'Entered throwing unexpected exception');
983 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
984 fnd_message.set_token('MESSAGE', x_msg_data);
985 RAISE fnd_api.g_exc_unexpected_error;
986 END IF;
987
988 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'Procedure Ends');
989
990 EXCEPTION
991 WHEN fnd_api.g_exc_error THEN
992 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
993 'OZF EXCEPTION G_EXC_ERROR: '||x_msg_data);
994 x_return_status := fnd_api.g_ret_sts_error;
995 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
996 fnd_message.set_token('MESSAGE', x_msg_data);
997 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
998 'OZF done:' || x_msg_data || '::::');
999
1000 WHEN fnd_api.g_exc_unexpected_error THEN
1001 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
1002 'OZF EXCEPTION G_EXC_UNEXPECTED_ERROR');
1003 x_return_status := fnd_api.g_ret_sts_unexp_error;
1004 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
1005 fnd_message.set_token('MESSAGE', x_msg_data);
1006 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
1007 'OZF done G_EXC_UNEXPECTED_ERROR:' || x_msg_data || '::::');
1008 WHEN others THEN
1009 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
1010 'OZF EXCEPTION OTHERS' || sqlerrm);
1011 x_return_status := fnd_api.g_ret_sts_error;
1012 fnd_message.set_name('OZF', 'OZF_SD_FEED_DATA_ERROR');
1013 fnd_message.set_token('MESSAGE', x_msg_data);
1014 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
1015 'OZF done OTHERS:' || x_msg_data ||'::::');
1016 END update_data;
1017
1018 PROCEDURE BATCH_CREATE_APPROVED_LINE_NEW(p_batch_id IN NUMBER,
1019 x_return_status OUT nocopy VARCHAR2,
1020 x_msg_data OUT nocopy VARCHAR2) IS
1021
1022 l_cnt_total_line NUMBER;
1023 l_cnt_rejected_line NUMBER;
1024 l_new_batch_id NUMBER := NULL;
1025 l_message VARCHAR2(1000);
1026 l_vendor_id NUMBER;
1027 l_vendor_site_id NUMBER;
1028 l_org_id NUMBER;
1029 l_claim_amount NUMBER;
1030 l_dup_dispute NUMBER;
1031 l_batch_threshold NUMBER;
1032 l_line_threshold NUMBER;
1033 l_currency_code VARCHAR2(20);
1034 l_batch_number VARCHAR2(20);
1035 l_par_batch_curr_code VARCHAR2(20);
1036 l_claim_number VARCHAR2(30);
1037 l_claim_minor_version NUMBER;
1038
1039 BEGIN
1040
1041 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::batch_create_approved_line_new',
1042 'Procedure Starts: Batch Id :'||p_batch_id);
1043
1044 SELECT batch_number, vendor_id, vendor_site_id, org_id,
1045 currency_code, batch_amount_threshold, batch_line_amount_threshold, claim_number, claim_minor_version
1046 INTO l_batch_number, l_vendor_id, l_vendor_site_id, l_org_id,
1047 l_par_batch_curr_code, l_batch_threshold, l_line_threshold, l_claim_number, l_claim_minor_version
1048 FROM ozf_sd_batch_headers_all
1049 WHERE batch_id = p_batch_id;
1050
1051 ozf_sd_batch_pvt.create_batch_header(l_vendor_id,
1052 l_vendor_site_id,
1053 l_org_id,
1054 l_batch_threshold,
1055 l_line_threshold,
1056 l_par_batch_curr_code,
1057 'F' ,
1058 l_new_batch_id);
1059
1060 --raising business event
1061 ozf_sd_util_pvt.sd_raise_event (l_new_batch_id, 'CREATE', x_return_status);
1062
1063
1064 /*set batch header as WIP for parent batch*/
1065 UPDATE ozf_sd_batch_headers_all
1066 SET status_code = 'WIP',
1067 object_version_number = object_version_number + 1,
1068 claim_minor_version = l_claim_minor_version + 1,
1069 last_update_date = sysdate,
1070 last_updated_by = fnd_global.user_id
1071 -- child_batch_id = l_new_batch_id
1072 WHERE batch_id = p_batch_id;
1073
1074 /*set batch header as APPROVED for child batch*/
1075 UPDATE ozf_sd_batch_headers_all
1076 SET status_code = 'APPROVED',
1077 parent_batch_id = p_batch_id,
1078 claim_number = l_claim_number||'_'||l_claim_minor_version,
1079 claim_minor_version = l_claim_minor_version,
1080 object_version_number = object_version_number + 1,
1081 last_update_date = sysdate,
1082 last_updated_by = fnd_global.user_id
1083 WHERE batch_id = l_new_batch_id ;
1084
1085 update_rejected_line_data(l_batch_number, p_batch_id, l_new_batch_id, x_return_status, x_msg_data);
1086
1087 -- cursor to update batch_id of all APPROVED lines with New Batch Id
1088 FOR v_update_batch IN (SELECT batch_id, batch_line_number, batch_line_id
1089 FROM ozf_sd_batch_lines_all
1090 WHERE batch_id = p_batch_id
1091 AND status_code = 'APPROVED'
1092 AND purge_flag <> 'Y')
1093 LOOP
1094
1095 UPDATE ozf_sd_batch_lines_all
1096 SET batch_id = l_new_batch_id,
1097 object_version_number = object_version_number + 1,
1098 last_update_date = sysdate,
1099 last_updated_by = fnd_global.user_id
1100 WHERE batch_line_number = v_update_batch.batch_line_number AND
1101 batch_id = v_update_batch.batch_id;
1102
1103 END LOOP;
1104
1105 commit;
1106
1107 process_claim(l_new_batch_id, x_return_status, x_msg_data);
1108
1109 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::batch_create_approved_line_new',
1110 'Procedure Ends');
1111
1112 END BATCH_CREATE_APPROVED_LINE_NEW;
1113
1114 PROCEDURE update_dispute_data(p_batch_number varchar2,p_batch_id number,new_batch_id number) IS
1115
1116 type v_disputes IS TABLE OF VARCHAR2(50) INDEX BY binary_integer;
1117 l_disputes v_disputes;
1118 l_dispute_index NUMBER(2);
1119
1120 BEGIN
1121 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_dispute_data',
1122 'Procedure Starts: Batch number -> '||p_batch_number||' Batch Id -> '||p_batch_id|| ' New Batch Id -> '||new_batch_id);
1123
1124 FOR v_dispute_code IN (SELECT bl.batch_id,
1125 bl.batch_line_id,
1126 bint.gbl_claim_rej_code1,
1127 bint.gbl_claim_rej_code2,
1128 bint.gbl_claim_rej_code3,
1129 bint.gbl_claim_rej_code4,
1130 bint.gbl_claim_rej_code5,
1131 bint.gbl_claim_rej_code6,
1132 bint.gbl_claim_rej_code7,
1133 bint.gbl_claim_rej_code8,
1134 bint.gbl_claim_rej_code9,
1135 bint.gbl_claim_rej_code10
1136 FROM ozf_sd_batch_lines_all bl,
1137 ozf_sd_batch_lines_int_all bint
1138 WHERE bl.batch_id = bint.ship_frm_sd_claim_request_id
1139 AND bl.batch_line_number = bint.batch_line_number
1140 AND bl.status_code = 'REJECTED'
1141 AND bint.ship_frm_sd_claim_request_id = p_batch_number
1142 AND bint.processed_flag = 'N'
1143 AND bl.purge_flag <> 'Y'
1144 )
1145
1146 LOOP
1147
1148 l_dispute_index := 0;
1149
1150 IF v_dispute_code.gbl_claim_rej_code1 IS NOT NULL THEN
1151 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code1;
1152 l_dispute_index := l_dispute_index + 1;
1153 END IF;
1154
1155 IF v_dispute_code.gbl_claim_rej_code2 IS NOT NULL THEN
1156 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code2;
1157 l_dispute_index := l_dispute_index + 1;
1158 END IF;
1159
1160 IF v_dispute_code.gbl_claim_rej_code3 IS NOT NULL THEN
1161 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code3;
1162 l_dispute_index := l_dispute_index + 1;
1163 END IF;
1164
1165 IF v_dispute_code.gbl_claim_rej_code4 IS NOT NULL THEN
1166 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code4;
1167 l_dispute_index := l_dispute_index + 1;
1168 END IF;
1169
1170 IF v_dispute_code.gbl_claim_rej_code5 IS NOT NULL THEN
1171 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code5;
1172 l_dispute_index := l_dispute_index + 1;
1173 END IF;
1174
1175 IF v_dispute_code.gbl_claim_rej_code6 IS NOT NULL THEN
1176 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code6;
1177 l_dispute_index := l_dispute_index + 1;
1178 END IF;
1179
1180 IF v_dispute_code.gbl_claim_rej_code7 IS NOT NULL THEN
1181 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code7;
1182 l_dispute_index := l_dispute_index + 1;
1183 END IF;
1184
1185 IF v_dispute_code.gbl_claim_rej_code8 IS NOT NULL THEN
1186 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code8;
1187 l_dispute_index := l_dispute_index + 1;
1188 END IF;
1189
1190 IF v_dispute_code.gbl_claim_rej_code9 IS NOT NULL THEN
1191 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code9;
1192 l_dispute_index := l_dispute_index + 1;
1193 END IF;
1194
1195 IF v_dispute_code.gbl_claim_rej_code10 IS NOT NULL THEN
1196 l_disputes(l_dispute_index) := v_dispute_code.gbl_claim_rej_code10;
1197 l_dispute_index := l_dispute_index + 1;
1198 END IF;
1199
1200 FOR i IN 0 .. l_dispute_index - 1 LOOP
1201
1202 INSERT INTO ozf_sd_batch_line_disputes
1203 (batch_line_dispute_id,
1204 batch_id,
1205 batch_line_id,
1206 object_version_number,
1207 dispute_code,
1208 review_flag,
1209 creation_date,
1210 last_update_date,
1211 last_updated_by,
1212 request_id,
1213 created_by,
1214 created_from,
1215 last_update_login,
1216 program_application_id,
1217 program_update_date,
1218 program_id,
1219 security_group_id)
1220 VALUES
1221 (ozf_sd_batch_line_disputes_s.nextval,
1222 v_dispute_code.batch_id,
1223 v_dispute_code.batch_line_id,
1224 1,
1225 l_disputes(i),
1226 NULL, --review flag
1227 sysdate,
1228 sysdate,
1229 fnd_global.user_id,
1230 fnd_global.conc_request_id,
1231 fnd_global.user_id,
1232 NULL, --created from
1233 fnd_global.conc_login_id,
1234 fnd_global.prog_appl_id, --l_program_application_id,
1235 NULL, --l_program_update_date,
1236 fnd_global.conc_program_id, -- p_Operating_Unit,
1237 fnd_global.security_group_id);
1238 END LOOP;
1239 END LOOP;
1240 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_dispute_data',
1241 'Procedure Ends');
1242 END update_dispute_data;
1243
1244 PROCEDURE process_claim(p_batch_id IN NUMBER,
1245 x_return_status OUT nocopy VARCHAR2,
1246 x_msg_data OUT nocopy VARCHAR2) IS
1247
1248 l_claim_id NUMBER := NULL;
1249 -- Incase auto claim is run
1250 l_claim_ret_status VARCHAR2(15) := NULL;
1251 l_claim_msg_count NUMBER := NULL;
1252 l_claim_msg_data VARCHAR2(500) := NULL;
1253 l_claim_type VARCHAR2(20) := 'SUPPLIER';
1254 --always defaulted to external claim
1255
1256 BEGIN
1257 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::process_claim',
1258 'Procedure Starts: Batch Id -> '||p_batch_id);
1259 --call claim API
1260 --update claim number and set batch /closed
1261 ozf_claim_accrual_pvt.initiate_sd_payment(1,
1262 fnd_api.g_false,
1263 fnd_api.g_true,
1264 fnd_api.g_valid_level_full,
1265 l_claim_ret_status,
1266 l_claim_msg_count,
1267 l_claim_msg_data,
1268 p_batch_id,
1269 l_claim_type,
1270 l_claim_id);
1271
1272 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::process_claim',
1273 'Return Status of claim API: '||l_claim_ret_status ||' Msg: '||l_claim_msg_data || 'Msg Count: '|| l_claim_msg_count);
1274
1275 FOR I IN 1..l_claim_msg_count LOOP
1276 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'OZF_SD_BATCH_FEED_PVT::process_claim',
1277 'Claim API Msg: '|| SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
1278 END LOOP;
1279
1280 IF l_claim_ret_status = FND_API.G_RET_STS_SUCCESS THEN
1281 UPDATE ozf_sd_batch_headers_all
1282 SET status_code = 'CLOSED',
1283 claim_id = l_claim_id,
1284 last_update_date = sysdate,
1285 last_updated_by = fnd_global.user_id,
1286 object_version_number = object_version_number + 1
1287 WHERE batch_id = p_batch_id;
1288 ELSE
1289 x_return_status := l_claim_ret_status;
1290 x_msg_data := l_claim_msg_data;
1291 RAISE fnd_api.g_exc_error;
1292 END IF;
1293 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::process_claim',
1294 'Procedure Ends');
1295 END process_claim;
1296
1297 PROCEDURE update_rejected_line_data(p_batch_number VARCHAR2,
1298 p_batch_id NUMBER,
1299 new_batch_id NUMBER,
1300 x_return_status OUT nocopy VARCHAR2,
1301 x_msg_data OUT nocopy VARCHAR2) IS
1302 l_currency_code VARCHAR2(15);
1303
1304 BEGIN
1305 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_rejected_line_data',
1306 'Procedure Starts: Batch number -> '||p_batch_number||' Batch Id -> '||p_batch_id|| ' New Batch Id -> '||new_batch_id);
1307
1308 FOR v_rejected_lines IN (SELECT intr.*
1309 FROM ozf_sd_batch_lines_int_all intr, ozf_sd_batch_lines_all bl
1310 WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
1311 AND intr.processed_flag = 'N'
1312 AND bl.purge_flag <> 'Y'
1313 AND intr.gbl_claim_disposition_code = 'REJECTED'
1314 AND intr.ship_frm_sd_claim_request_id = bl.batch_id
1315 AND intr.batch_line_number = bl.batch_line_number) LOOP
1316
1317 IF v_rejected_lines.gbl_claim_rej_code1 IS NULL AND
1318 v_rejected_lines.gbl_claim_rej_code2 IS NULL AND
1319 v_rejected_lines.gbl_claim_rej_code3 IS NULL AND
1320 v_rejected_lines.gbl_claim_rej_code4 IS NULL AND
1321 v_rejected_lines.gbl_claim_rej_code5 IS NULL AND
1322 v_rejected_lines.gbl_claim_rej_code6 IS NULL AND
1323 v_rejected_lines.gbl_claim_rej_code7 IS NULL AND
1324 v_rejected_lines.gbl_claim_rej_code8 IS NULL AND
1325 v_rejected_lines.gbl_claim_rej_code9 IS NULL AND
1326 v_rejected_lines.gbl_claim_rej_code10 IS NULL THEN
1327
1328 UPDATE ozf_sd_batch_lines_int_all
1329 SET gbl_claim_rej_code10 = 'OZF_SD_DATA_ERROR',
1330 validation_txt = 'REJECTION CODE MISSING',
1331 last_update_date = sysdate,
1332 last_updated_by = fnd_global.user_id
1333 WHERE batch_line_number = v_rejected_lines.batch_line_number
1334 AND ship_frm_sd_claim_request_id = p_batch_number;
1335
1336 x_return_status := fnd_api.g_ret_sts_error;
1337 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR');
1338
1339 END IF;
1340
1341 SELECT claim_amount_currency_code
1342 INTO l_currency_code
1343 FROM ozf_sd_batch_lines_all
1344 WHERE batch_id = p_batch_id
1345 AND batch_line_number = v_rejected_lines.batch_line_number;
1346
1347 IF l_currency_code <> v_rejected_lines.vendor_auth_cost_currency_code THEN
1348 UPDATE ozf_sd_batch_lines_int_all
1349 SET validation_txt = 'CURRENCY CODE NOT MATCHING',
1350 gbl_claim_rej_code10 = 'OZF_SD_DATA_ERROR',
1351 gbl_claim_disposition_code = 'REJECTED',
1352 last_update_date = sysdate,
1353 last_updated_by = fnd_global.user_id
1354 WHERE batch_line_number = v_rejected_lines.batch_line_number AND
1355 ship_frm_sd_claim_request_id = p_batch_number;
1356
1357 x_return_status := fnd_api.g_ret_sts_error;
1358 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR');
1359
1360 END IF;
1361
1362 IF v_rejected_lines.vendor_auth_cost_monetary_amt < 0 THEN
1363 UPDATE ozf_sd_batch_lines_int_all
1364 SET validation_txt = 'VENDOR_AUTH_COST_MONETARY_AMT CAN NOT BE NEGATIVE',
1365 gbl_claim_rej_code10 = 'OZF_SD_DATA_ERROR',
1366 last_update_date = sysdate,
1367 last_updated_by = fnd_global.user_id
1368 WHERE batch_line_number = v_rejected_lines.batch_line_number AND
1369 ship_frm_sd_claim_request_id = p_batch_number;
1370
1371 x_return_status := fnd_api.g_ret_sts_error;
1372 x_msg_data := FND_MESSAGE.GET_STRING('OZF','OZF_SD_FEED_DATA_ERROR');
1373
1374 END IF;
1375
1376 UPDATE ozf_sd_batch_lines_all
1377 SET approved_amount = v_rejected_lines.vendor_auth_cost_monetary_amt,
1378 approved_currency_code = v_rejected_lines.vendor_auth_cost_currency_code,
1379 object_version_number = object_version_number + 1,
1380 last_update_date = sysdate,
1381 last_updated_by = fnd_global.user_id
1382 WHERE batch_line_number = v_rejected_lines.batch_line_number AND
1383 batch_id = p_batch_id;
1384
1385 END LOOP;
1386
1387 update_dispute_data(p_batch_number,p_batch_id,new_batch_id);
1388
1389 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_rejected_line_data',
1390 'Procedure Ends');
1391 END update_rejected_line_data;
1392
1393 END ozf_sd_batch_feed_pvt;
1394