[Home] [Help]
PACKAGE BODY: APPS.OZF_WEBADI_INTERFACE_PVT
Source
1 PACKAGE BODY OZF_WEBADI_INTERFACE_PVT as
2 /* $Header: ozfadiwb.pls 120.22.12020000.2 2013/02/21 06:01:51 sagpraka ship $ */
3 -- Start of Comments
4 -- Package name : ozf_webadi_interface_pvt
5 -- Purpose :
6 -- History : 09-OCT-2003 vansub Created
7 -- NOTE :
8 -- End of Comments
9 /*
10 | MODIFICATION HISTORY
11 | Date Name Description of Changes
12 | ----------- ---------- -----------------------
13 | 27-OCT-2010 BKUNJAN BUG 10234121 - FP 9848949 R12- Trade mgmt - webadi for idsm does not
14 | validate against bill to location and ship to location
15 | 25-Jan-2011 MUTHSUBR Fix for Bug#10624065.
16 | 23-Mar-2011 BKUNJAN Bug fix 11873429 - CODE CONVERSION UPLOAD VIA WEB ADI GIVES `CANNOT UPDATE AN END DATED COD
17 | 20-APR-2011 MUTHSUBR Fix for Bug#11906081
18 | 27-MAY-2011 MUTHSUBR Bug#12559121 - CODE CONVERSION DOES NOT ALLOW ITEM NUMBER MAPPING ACROSS OUS
19 | 14-JUL-2011 NINARASI Bug 12717648 - FP of 12718707 - CODE CONVERSION UPLOAD VIA WEBADI FAILS AT OU IF ALREADY MAPPED AT CUSTOMER
20 +============================================================================*/
21
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_WEBADI_INTERFACE_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfadiwb.pls';
24
25 -- All of the parameters must be capital letter, WebADI enforces it to be in caps
26
27
28 PROCEDURE CODE_CONVERSION_WEBADI
29 (
30 P_PARTY_ID IN NUMBER,
31 P_CUST_ACCOUNT_ID IN NUMBER,
32 P_CODE_CONVERSION_TYPE IN VARCHAR2,
33 P_EXTERNAL_CODE IN VARCHAR2,
34 P_INTERNAL_CODE IN VARCHAR2,
35 P_DESCRIPTION IN VARCHAR2,
36 P_START_DATE_ACTIVE IN DATE,
37 P_END_DATE_ACTIVE IN DATE,
38 P_ORG_ID IN NUMBER,
39 P_INTERNAL_CODE_NAME IN VARCHAR2
40 )
41 IS
42
43 l_api_name CONSTANT VARCHAR2(30) := 'code_conversion_webadi';
44 l_api_version_number CONSTANT NUMBER := 1.0;
45 l_code_conversion_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
46 l_code_conversion_rec OZF_CODE_CONVERSION_PVT.CODE_CONVERSION_REC_TYPE := ozf_code_conversion_pvt.g_miss_code_conversion_rec;
47 l_code_conversion_tbl OZF_CODE_CONVERSION_PVT.CODE_CONVERSION_TBL_TYPE := ozf_code_conversion_pvt.g_miss_code_conversion_tbl;
48 l_error VARCHAR2(30) := 'OZF_WEBADI_ERROR';
49 l_object_version_no_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
50 l_internal_code VARCHAR2(4000);
51 x_msg_count NUMBER;
52 x_msg_data VARCHAR2(2000);
53 x_return_status VARCHAR(3);
54 l_message VARCHAR2(32000);
55 l_code VARCHAR2(30) := null;
56 l_org_id NUMBER;
57 l_code_conv_found BOOLEAN;--ninarasi Bug 12717648 FP of bug 12718707
58
59 -- Exceptions
60 ozf_webadi_error EXCEPTION;
61
62 --ninarasi Bug 12717648 FP of bug 12718707
63 CURSOR csr_code_conversion1(cv_external_code VARCHAR2
64 ,cv_start_date_active DATE
65 ,cv_party_id NUMBER
66 ,cv_account_id NUMBER
67 ,cv_conversion_type VARCHAR2
68 ,cv_org_id NUMBER -- Bug#12559121
69 )
70 IS
71 SELECT code_conversion_id,
72 object_version_number,
73 last_update_date,
74 last_updated_by,
75 creation_date,
76 created_by,
77 last_update_login,
78 org_id,
79 party_id,
80 cust_account_id,
81 code_conversion_type,
82 external_code,
83 internal_code,
84 description,
85 start_date_active,
86 end_date_active,
87 attribute_category,
88 attribute1,
89 attribute2,
90 attribute3,
91 attribute4,
92 attribute5,
93 attribute6,
94 attribute7,
95 attribute8,
96 attribute9,
97 attribute10,
98 attribute11,
99 attribute12,
100 attribute13,
101 attribute14,
102 attribute15,
103 security_group_id,
104 org_id
105 FROM ozf_code_conversions_all
106 WHERE external_code = cv_external_code
107 AND org_id = NVL(cv_org_id,0) -- Bug#12559121
108 AND code_conversion_type = NVL(cv_conversion_type,code_conversion_type) -- Bug#11906081
109 AND party_id = cv_party_id
110 AND cust_account_id = cv_account_id
111 AND TRUNC(cv_start_date_active) BETWEEN start_date_active and NVL(end_date_active,TRUNC(SYSDATE)) -- Bugfix 11822771
112 AND party_id IS NOT NULL
113 AND cust_account_id IS NOT NULL;
114
115 CURSOR csr_code_conversion2(cv_external_code VARCHAR2
116 ,cv_start_date_active DATE
117 ,cv_party_id NUMBER
118 ,cv_account_id NUMBER
119 ,cv_conversion_type VARCHAR2
120 ,cv_org_id NUMBER -- Bug#12559121
121 )
122 IS
123 SELECT code_conversion_id,
124 object_version_number,
125 last_update_date,
126 last_updated_by,
127 creation_date,
128 created_by,
129 last_update_login,
130 org_id,
131 party_id,
132 cust_account_id,
133 code_conversion_type,
134 external_code,
135 internal_code,
136 description,
137 start_date_active,
138 end_date_active,
139 attribute_category,
140 attribute1,
141 attribute2,
142 attribute3,
143 attribute4,
144 attribute5,
145 attribute6,
146 attribute7,
147 attribute8,
148 attribute9,
149 attribute10,
150 attribute11,
151 attribute12,
152 attribute13,
153 attribute14,
154 attribute15,
155 security_group_id,
156 org_id
157 FROM ozf_code_conversions_all
158 WHERE external_code = cv_external_code
159 AND org_id = NVL(cv_org_id,0) -- Bug#12559121
160 AND code_conversion_type = NVL(cv_conversion_type,code_conversion_type) -- Bug#11906081
161 AND party_id = cv_party_id
162 AND TRUNC(cv_start_date_active) BETWEEN start_date_active and NVL(end_date_active,TRUNC(SYSDATE)) -- Bugfix 11822771
163 AND party_id IS NOT NULL
164 AND cust_account_id IS NULL;
165
166 CURSOR csr_code_conversion3(cv_external_code VARCHAR2
167 ,cv_start_date_active DATE
168 ,cv_party_id NUMBER
169 ,cv_account_id NUMBER
170 ,cv_conversion_type VARCHAR2
171 ,cv_org_id NUMBER -- Bug#12559121
172 )
173 IS
174 SELECT code_conversion_id,
175 object_version_number,
176 last_update_date,
177 last_updated_by,
178 creation_date,
179 created_by,
180 last_update_login,
181 org_id,
182 party_id,
183 cust_account_id,
184 code_conversion_type,
185 external_code,
186 internal_code,
187 description,
188 start_date_active,
189 end_date_active,
190 attribute_category,
191 attribute1,
192 attribute2,
193 attribute3,
194 attribute4,
195 attribute5,
196 attribute6,
197 attribute7,
198 attribute8,
199 attribute9,
200 attribute10,
201 attribute11,
202 attribute12,
203 attribute13,
204 attribute14,
205 attribute15,
206 security_group_id,
207 org_id
208 FROM ozf_code_conversions_all
209 WHERE external_code = cv_external_code
210 AND org_id = NVL(cv_org_id,0) -- Bug#12559121
211 AND code_conversion_type = NVL(cv_conversion_type,code_conversion_type) -- Bug#11906081
212 AND TRUNC(cv_start_date_active) BETWEEN start_date_active and NVL(end_date_active,TRUNC(SYSDATE)) -- Bugfix 11822771
213 AND party_id IS NULL and cust_account_id IS NULL;
214
215 CURSOR get_item_id(p_item_name VARCHAR2)IS
216 SELECT INVENTORY_ITEM_ID ID FROM MTL_SYSTEM_ITEMS_VL
217 WHERE ORGANIZATION_ID = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
218 AND CONCATENATED_SEGMENTS = p_item_name;
219
220 BEGIN
221 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
222 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
223 END IF;
224
225 l_org_id := P_ORG_ID;
226 --Code Conversion for Customer Trade Profile for Account
227 IF p_party_id IS NOT NULL AND p_cust_account_id IS NOT NULL THEN
228
229 OPEN csr_code_conversion1(p_external_code
230 ,p_start_date_active
231 ,p_party_id
232 ,p_cust_account_id
233 ,p_code_conversion_type
234 ,l_org_id -- Bug#12559121
235 ); -- Bug#11906081
236
237 FETCH csr_code_conversion1
238 INTO l_code_conversion_rec.code_conversion_id
239 ,l_code_conversion_rec.object_version_number
240 ,l_code_conversion_rec.last_update_date
241 ,l_code_conversion_rec.last_updated_by
242 ,l_code_conversion_rec.creation_date
243 ,l_code_conversion_rec.created_by
244 ,l_code_conversion_rec.last_update_login
245 ,l_code_conversion_rec.org_id
246 ,l_code_conversion_rec.party_id
247 ,l_code_conversion_rec.cust_account_id
248 ,l_code_conversion_rec.code_conversion_type
249 ,l_code_conversion_rec.external_code
250 ,l_code_conversion_rec.internal_code
251 ,l_code_conversion_rec.description
252 ,l_code_conversion_rec.start_date_active
253 ,l_code_conversion_rec.end_date_active
254 ,l_code_conversion_rec.attribute_category
255 ,l_code_conversion_rec.attribute1
256 ,l_code_conversion_rec.attribute2
257 ,l_code_conversion_rec.attribute3
258 ,l_code_conversion_rec.attribute4
259 ,l_code_conversion_rec.attribute5
260 ,l_code_conversion_rec.attribute6
261 ,l_code_conversion_rec.attribute7
262 ,l_code_conversion_rec.attribute8
263 ,l_code_conversion_rec.attribute9
264 ,l_code_conversion_rec.attribute10
265 ,l_code_conversion_rec.attribute11
266 ,l_code_conversion_rec.attribute12
267 ,l_code_conversion_rec.attribute13
268 ,l_code_conversion_rec.attribute14
269 ,l_code_conversion_rec.attribute15
270 ,l_code_conversion_rec.security_group_id
271 ,l_code_conversion_rec.org_id;
272
273 --Code Conversion for Customer Trade Profile for Party
274 ELSIF p_party_id IS NOT NULL AND p_cust_account_id IS NULL THEN
275
276 OPEN csr_code_conversion2(p_external_code
277 ,p_start_date_active
278 ,p_party_id
279 ,p_cust_account_id
280 ,p_code_conversion_type
281 ,l_org_id -- Bug#12559121
282 ); -- Bug#11906081
283
284 FETCH csr_code_conversion2
285 INTO l_code_conversion_rec.code_conversion_id
286 ,l_code_conversion_rec.object_version_number
287 ,l_code_conversion_rec.last_update_date
288 ,l_code_conversion_rec.last_updated_by
289 ,l_code_conversion_rec.creation_date
290 ,l_code_conversion_rec.created_by
291 ,l_code_conversion_rec.last_update_login
292 ,l_code_conversion_rec.org_id
293 ,l_code_conversion_rec.party_id
294 ,l_code_conversion_rec.cust_account_id
295 ,l_code_conversion_rec.code_conversion_type
296 ,l_code_conversion_rec.external_code
297 ,l_code_conversion_rec.internal_code
298 ,l_code_conversion_rec.description
299 ,l_code_conversion_rec.start_date_active
300 ,l_code_conversion_rec.end_date_active
301 ,l_code_conversion_rec.attribute_category
302 ,l_code_conversion_rec.attribute1
303 ,l_code_conversion_rec.attribute2
304 ,l_code_conversion_rec.attribute3
305 ,l_code_conversion_rec.attribute4
306 ,l_code_conversion_rec.attribute5
307 ,l_code_conversion_rec.attribute6
308 ,l_code_conversion_rec.attribute7
309 ,l_code_conversion_rec.attribute8
310 ,l_code_conversion_rec.attribute9
311 ,l_code_conversion_rec.attribute10
312 ,l_code_conversion_rec.attribute11
313 ,l_code_conversion_rec.attribute12
314 ,l_code_conversion_rec.attribute13
315 ,l_code_conversion_rec.attribute14
316 ,l_code_conversion_rec.attribute15
317 ,l_code_conversion_rec.security_group_id
318 ,l_code_conversion_rec.org_id;
319
320 --Code Conversion for Operating Unit
321 ELSE
322
323 OPEN csr_code_conversion3(p_external_code
324 ,p_start_date_active
325 ,p_party_id
326 ,p_cust_account_id
327 ,p_code_conversion_type
328 ,l_org_id -- Bug#12559121
329 ); -- Bug#11906081
330
331 FETCH csr_code_conversion3
332 INTO l_code_conversion_rec.code_conversion_id
333 ,l_code_conversion_rec.object_version_number
334 ,l_code_conversion_rec.last_update_date
335 ,l_code_conversion_rec.last_updated_by
336 ,l_code_conversion_rec.creation_date
337 ,l_code_conversion_rec.created_by
338 ,l_code_conversion_rec.last_update_login
339 ,l_code_conversion_rec.org_id
340 ,l_code_conversion_rec.party_id
341 ,l_code_conversion_rec.cust_account_id
342 ,l_code_conversion_rec.code_conversion_type
343 ,l_code_conversion_rec.external_code
344 ,l_code_conversion_rec.internal_code
345 ,l_code_conversion_rec.description
346 ,l_code_conversion_rec.start_date_active
347 ,l_code_conversion_rec.end_date_active
348 ,l_code_conversion_rec.attribute_category
349 ,l_code_conversion_rec.attribute1
350 ,l_code_conversion_rec.attribute2
351 ,l_code_conversion_rec.attribute3
352 ,l_code_conversion_rec.attribute4
353 ,l_code_conversion_rec.attribute5
354 ,l_code_conversion_rec.attribute6
355 ,l_code_conversion_rec.attribute7
356 ,l_code_conversion_rec.attribute8
357 ,l_code_conversion_rec.attribute9
358 ,l_code_conversion_rec.attribute10
359 ,l_code_conversion_rec.attribute11
360 ,l_code_conversion_rec.attribute12
361 ,l_code_conversion_rec.attribute13
362 ,l_code_conversion_rec.attribute14
363 ,l_code_conversion_rec.attribute15
364 ,l_code_conversion_rec.security_group_id
365 ,l_code_conversion_rec.org_id;
366
367 END IF;
368
369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
370 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'fetched db rec');
371 END IF;
372
373 l_code_conversion_tbl := ozf_code_conversion_pvt.code_conversion_tbl_type();
374
375 l_code_conversion_tbl.extend(1);
376
377 l_code_conversion_tbl(1) := l_code_conversion_rec;
378 IF l_code_conversion_rec.internal_code IS NOT NULL AND
379 l_code_conversion_rec.internal_code <> P_internal_code
380 THEN
381 l_message := 'Cannot update internal code';
382 raise_application_error( -20000, l_message);
383 END IF;
384
385
386 -- Update End date only when it is NUll or a future date
387 IF l_code_conversion_Rec.End_Date_Active IS NOT NULL
388 AND Trunc(l_code_conversion_Rec.End_Date_Active) < Trunc(P_End_Date_Active)
389 AND P_End_Date_Active < SYSDATE THEN
390 l_message := 'End date Active cannot be updated';
391 raise_application_error( -20000, l_message);
392 END IF;
393
394 ---Update not allowed for Start Date when start date is earlier than current date
395 IF l_code_conversion_Rec.Start_Date_Active IS NOT NULL
396 AND trunc(l_code_conversion_Rec.Start_Date_Active) <> trunc(P_Start_Date_Active)
397 THEN
398 IF l_code_conversion_Rec.end_date_active < p_Start_Date_Active THEN
399 l_message := 'Cannot update an end dated code conversion map';
400 raise_application_error( -20000, l_message);
401 END IF;
402
403 END IF;
404
405
406 IF p_end_date_active IS NOT NULL AND p_end_date_active < p_Start_Date_Active THEN
407 l_message := 'End Date Active Cannot be less than start date active';
408 raise_application_error( -20000, l_message);
409 END IF;
410
411
412 -- Update not allowed for External Code
413 IF l_code_conversion_Rec.external_Code IS NOT NULL AND
414 l_code_conversion_Rec.external_Code <> P_external_Code
415 THEN
416 l_message := 'External Code Cannot be Updated';
417 raise_application_error( -20000, l_message);
418 END IF;
419
420
421 IF l_code_conversion_Rec.Start_Date_Active IS NULL THEN
422 IF p_Start_Date_Active < TRUNC(SYSDATE) THEN
423 l_message := 'Start date Active cannot be earlier than current date';
424 raise_application_error( -20000, l_message);
425 END IF;
426 ELSE
427 IF trunc(l_code_conversion_Rec.Start_Date_Active) <> trunc(P_Start_Date_Active) THEN
428 l_message := 'Start date Active cannot be earlier than current date';
429 raise_application_error( -20000, l_message);
430 END IF;
431 END IF;
432
433
434 l_code_conversion_tbl(1).code_conversion_type := p_code_conversion_type;
435 l_code_conversion_tbl(1).description := p_description;
436 l_code_conversion_tbl(1).start_date_active := p_start_date_active;
437 l_code_conversion_tbl(1).end_date_active := p_end_date_active;
438 l_code_conversion_tbl(1).party_id := p_party_id;
439 l_code_conversion_tbl(1).cust_account_id := p_cust_account_id;
440 l_code_conversion_tbl(1).org_id := l_org_id;
441
442 l_code_conv_found := TRUE; --ninarasi Bug 12717648 FP of bug 12718707
443 IF p_party_id IS NOT NULL AND p_cust_account_id IS NOT NULL THEN
444 IF ( csr_code_conversion1%NOTFOUND) THEN
445 l_code_conv_found := FALSE;
446 END IF;
447 ELSIF p_party_id IS NOT NULL AND p_cust_account_id IS NULL THEN
448 IF ( csr_code_conversion2%NOTFOUND) THEN
449 l_code_conv_found := FALSE;
450 END IF;
451 ELSE
452 IF ( csr_code_conversion3%NOTFOUND) THEN
453 l_code_conv_found := FALSE;
454 END IF;
455 END IF;
456
457 IF NOT l_code_conv_found THEN
458
459 IF p_external_code IS NOT NULL
460 THEN
461
462 l_code_conversion_tbl(1).external_code := p_external_code;
463 IF p_internal_code IS NULL THEN
464 IF p_code_conversion_type = 'Product' OR p_code_conversion_type = 'OZF_PRODUCT_CODES' THEN
465 OPEN get_item_id(p_internal_code_name);
466 FETCH get_item_id INTO l_internal_code;
467 CLOSE get_item_id;
468 l_code_conversion_tbl(1).internal_code := l_internal_code;
469 END IF;
470 ELSE
471 l_code_conversion_tbl(1).internal_code := p_internal_code;
472 END IF;
473
474 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
475 OZF_UTILITY_PVT.debug_message('external code '|| p_external_code);
476 OZF_UTILITY_PVT.debug_message('start date active '|| p_start_date_active);
477 OZF_UTILITY_PVT.debug_message('calling create code conversion ');
478 END IF;
479
480 OZF_CODE_CONVERSION_PVT.create_code_conversion
481 (
482 p_api_version_number => 1.0 ,
483 p_init_msg_list => FND_API.G_FALSE,
484 p_commit => FND_API.G_FALSE,
485 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
486 x_return_status => x_return_Status,
487 x_msg_count => x_msg_Count,
488 x_msg_data => x_msg_Data,
489 p_code_conversion_tbl => l_code_conversion_tbl,
490 x_code_conversion_id_tbl => l_code_conversion_id_tbl);
491
492 IF x_return_Status = FND_API.G_RET_STS_ERROR THEN
493 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
494 l_message := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
495 raise_application_error( -20000, l_message);
496 END IF;
497 ELSIF x_return_Status = FND_API.G_RET_STS_UNEXP_ERROR THEN
498 fnd_message.set_name ('OZF', 'OZF_WADI_CREATE_ERROR');
499 l_message := fnd_message.get();
500 l_code := 'OZF_WADI_CREATE_ERROR';
501 raise_application_error( -20000, l_message);
502 END IF;
503 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
504 FOR i IN 1 .. l_code_conversion_id_tbl.count
505 LOOP
506 OZF_UTILITY_PVT.debug_message('Code Conversion ID ' || l_code_conversion_id_tbl(i) );
507 END LOOP;
508 END IF;
509 ELSE
510 fnd_message.set_name ('OZF', 'OZF_REQ_PARAM_MISSING');
511 l_message := fnd_message.get();
512 l_code := 'OZF_REQ_PARAM_MISSING';
513 raise_application_error( -20000, l_message);
514 END IF;
515
516 ELSE
517
518 OZF_CODE_CONVERSION_PVT.update_code_conversion
519 (
520 p_api_version_number => 1.0 ,
521 p_init_msg_list => FND_API.G_FALSE,
522 p_commit => FND_API.G_FALSE,
523 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
524 x_return_status => x_return_Status,
525 x_msg_count => x_msg_Count,
526 x_msg_data => x_msg_Data,
527 p_code_conversion_tbl => l_code_conversion_tbl,
528 x_object_version_number => l_object_version_no_tbl
529 );
530
531 -- OZF_UTILITY_PVT.debug_message('after update code conversion'||x_msg_Count);
532
533 IF x_return_Status = FND_API.G_RET_STS_ERROR THEN
534 l_message := fnd_msg_pub.get(p_encoded => fnd_api.g_true);
535 OZF_UTILITY_PVT.debug_message('Message '||l_message);
536
537 IF length( l_message) > 30 THEN
538 l_message := substr(l_message,1,30);
539 END IF;
540 raise_application_error( -20000, l_message);
541 ELSIF x_return_Status = FND_API.G_RET_STS_UNEXP_ERROR THEN
542 fnd_message.set_name ('OZF', 'OZF_WADI_UPDATE_ERROR');
543 l_message := fnd_message.get();
544 l_code := 'OZF_WADI_UPDATE_ERROR';
545 raise_application_error( -20000, l_message);
546 END IF;
547
548 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
549 FOR i IN 1 .. l_object_version_no_tbl.count
550 LOOP
551 OZF_UTILITY_PVT.debug_message('Object Version Number ' || l_object_version_no_tbl(i) );
552 END LOOP;
553 END IF;
554 END IF;
555 --ninarasi Bug 12717648 FP of bug 12718707
556 IF p_party_id IS NOT NULL AND p_cust_account_id IS NOT NULL THEN
557 CLOSE csr_code_conversion1;
558 ELSIF p_party_id IS NOT NULL AND p_cust_account_id IS NULL THEN
559 CLOSE csr_code_conversion2;
560 ELSE
561 CLOSE csr_code_conversion3;
562 END IF;
563 --commit;
564
565 EXCEPTION
566 WHEN ozf_webadi_error THEN
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 /* IF length( l_message) > 30 THEN
569 l_message := substr(l_message,1,30);
570 END IF; */
571 raise_application_error( -20000, l_message);
572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
573 --Fix for Bug#10624065(+)
574 /*
575 IF length( SQLERRM) > 30 THEN
576 ozf_utility_pvt.debug_message(substr(SQLERRM,12,30));
577 fnd_message.set_name ('OZF', substr(SQLERRM,12,30));
578 ELSE
579 */
580 fnd_message.set_name ('OZF', NVL(l_code,l_message));
581 --END IF;
582 --Fix for Bug#10624065(-)
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
584 WHEN OTHERS THEN
585 -- Fix for Bug#10624065(+)
586 /*
587 IF length( SQLERRM) > 30 THEN
588 ozf_utility_pvt.debug_message(substr(SQLERRM,12,30));
589 fnd_message.set_name ('OZF', substr(SQLERRM,12,30));
590 ELSE
591 */
592 fnd_message.set_name ('OZF', NVL(l_code,l_message));
593 --END IF;
594 --Fix for Bug#10624065(-)
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596 END CODE_CONVERSION_WEBADI;
597
598 --//Added to get the Location count
599 --//Bugfix 10234121
600 FUNCTION check_location
601 ( p_party_id IN NUMBER
602 ,p_location IN VARCHAR2
603 ,p_site_use_code IN VARCHAR2)
604 RETURN NUMBER
605 IS
606
607 l_location_count NUMBER := 0;
608
609 CURSOR get_location_count(cv_party_id IN NUMBER
610 ,cv_location IN VARCHAR2
611 ,cv_site_use_code IN VARCHAR2) IS
612 SELECT count(hcsu.location)
613 FROM hz_cust_accounts hca,
614 hz_parties hp,
615 hz_cust_site_uses hcsu,
616 hz_cust_acct_sites hcas,
617 hz_party_sites hps
618 WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
619 AND hcas.party_site_id = hps.party_site_id
620 AND hcsu.status = 'A'
621 AND hps.status = 'A'
622 AND hp.status = 'A'
623 AND hcas.cust_account_id = hca.cust_account_id
624 AND hp.party_id = hca.party_id
625 AND hca.party_id = cv_party_id
626 AND hcsu.location = cv_location
627 AND hcsu.site_use_code = cv_site_use_code;
628
629 BEGIN
630 l_location_count := 0;
631 OPEN get_location_count (p_party_id,p_location,p_site_use_code);
632 FETCH get_location_count INTO l_location_count;
633 CLOSE get_location_count;
634
635 RETURN l_location_count;
636
637 END check_location;
638
639
640 PROCEDURE RESALE_WEBADI
641 (
642 P_BATCH_TYPE IN VARCHAR2,
643 P_BATCH_NUMBER IN VARCHAR2,
644 P_BATCH_COUNT IN NUMBER,
645 P_REPORT_DATE IN DATE,
646 P_REPORT_START_DATE IN DATE,
647 P_REPORT_END_DATE IN DATE,
648 P_DATA_SOURCE_CODE IN VARCHAR2,
649 P_REFERENCE_TYPE IN VARCHAR2,
650 P_REFERENCE_NUMBER IN VARCHAR2,
651 P_YEAR IN NUMBER,
652 P_MONTH IN NUMBER,
653 P_COMMENTS IN VARCHAR2,
654 P_PARTNER_CLAIM_NUMBER IN VARCHAR2,
655 P_TRANSACTION_PURPOSE IN VARCHAR2,
656 P_TRANSACTION_TYPE IN VARCHAR2,
657 P_PARTNER_TYPE IN VARCHAR2,
658 P_PARTNER_PARTY_ID IN NUMBER,
659 P_PARTNER_CUST_ACCOUNT_ID IN NUMBER,
660 P_PARTNER_SITE_ID IN NUMBER,
661 P_PARTNER_CONTACT_NAME IN VARCHAR2,
662 P_PARTNER_EMAIL IN VARCHAR2,
663 P_PARTNER_PHONE IN VARCHAR2,
664 P_PARTNER_FAX IN VARCHAR2,
665 P_CURRENCY IN VARCHAR2,
666 P_CLAIMED_AMOUNT IN NUMBER,
667 P_CREDIT_CODE IN VARCHAR2,
668 P_BATCH_CREDIT_ADVICE_DATE IN DATE,
669 P_RESALE_TRANSFER_TYPE IN VARCHAR2,
670 P_TRANSFER_MVMT_TYPE IN VARCHAR2,
671 P_TRANSFER_DATE IN DATE,
672 P_TRACING_FLAG IN VARCHAR2,
673 P_SHIP_FROM_PARTY_NAME IN VARCHAR2,
674 P_SHIP_FROM_ADDRESS IN VARCHAR2,
675 P_SHIP_FROM_LOCATION IN VARCHAR2,
676 P_SHIP_FROM_CITY IN VARCHAR2,
677 P_SHIP_FROM_STATE IN VARCHAR2,
678 P_SHIP_FROM_POSTAL_CODE IN VARCHAR2,
679 P_SHIP_FROM_COUNTRY IN VARCHAR2,
680 P_SHIP_FROM_CONTACT_NAME IN VARCHAR2,
681 P_SHIP_FROM_PHONE IN VARCHAR2,
682 P_SHIP_FROM_FAX IN VARCHAR2,
683 P_SHIP_FROM_EMAIL IN VARCHAR2,
684 P_SOLD_FROM_PARTY_NAME IN VARCHAR2,
685 P_SOLD_FROM_ADDRESS IN VARCHAR2,
686 P_SOLD_FROM_LOCATION IN VARCHAR2,
687 P_SOLD_FROM_CITY IN VARCHAR2,
688 P_SOLD_FROM_STATE IN VARCHAR2,
689 P_SOLD_FROM_POSTAL_CODE IN VARCHAR2,
690 P_SOLD_FROM_COUNTRY IN VARCHAR2,
691 P_SOLD_FROM_CONTACT_NAME IN VARCHAR2,
692 P_SOLD_FROM_PHONE IN VARCHAR2,
693 P_SOLD_FROM_FAX IN VARCHAR2,
694 P_SOLD_FROM_EMAIL IN VARCHAR2,
695 P_BILL_TO_PARTY_NAME IN VARCHAR2,
696 P_BILL_TO_DUNS_NUMBER IN VARCHAR2,
697 P_BILL_TO_ADDRESS IN VARCHAR2,
698 P_BILL_TO_LOCATION IN VARCHAR2,
699 P_BILL_TO_CITY IN VARCHAR2,
700 P_BILL_TO_STATE IN VARCHAR2,
701 P_BILL_TO_POSTAL_CODE IN VARCHAR2,
702 P_BILL_TO_COUNTRY IN VARCHAR2,
703 P_BILL_TO_CONTACT_NAME IN VARCHAR2,
704 P_BILL_TO_PHONE IN VARCHAR2,
705 P_BILL_TO_FAX IN VARCHAR2,
706 P_BILL_TO_EMAIL IN VARCHAR2,
707 P_SHIP_TO_PARTY_NAME IN VARCHAR2,
708 P_SHIP_TO_DUNS_NUMBER IN VARCHAR2,
709 P_SHIP_TO_ADDRESS IN VARCHAR2,
710 P_SHIP_TO_LOCATION IN VARCHAR2,
711 P_SHIP_TO_CITY IN VARCHAR2,
712 P_SHIP_TO_STATE IN VARCHAR2,
713 P_SHIP_TO_POSTAL_CODE IN VARCHAR2,
714 P_SHIP_TO_COUNTRY IN VARCHAR2,
715 P_SHIP_TO_CONTACT_NAME IN VARCHAR2,
716 P_SHIP_TO_PHONE IN VARCHAR2,
717 P_SHIP_TO_FAX IN VARCHAR2,
718 P_SHIP_TO_EMAIL IN VARCHAR2,
719 P_END_CUST_PARTY_NAME IN VARCHAR2,
720 P_END_CUST_DUNS_NUMBER IN VARCHAR2,
721 P_END_CUST_ADDRESS IN VARCHAR2,
722 P_END_CUST_LOCATION IN VARCHAR2,
723 P_END_CUST_SITE_USE_CODE IN VARCHAR2,
724 P_END_CUST_CITY IN VARCHAR2,
725 P_END_CUST_STATE IN VARCHAR2,
726 P_END_CUST_POSTAL_CODE IN VARCHAR2,
727 P_END_CUST_COUNTRY IN VARCHAR2,
728 P_END_CUST_CONTACT_NAME IN VARCHAR2,
729 P_END_CUST_PHONE IN VARCHAR2,
730 P_END_CUST_FAX IN VARCHAR2,
731 P_END_CUST_EMAIL IN VARCHAR2,
732 P_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
733 P_ORIG_SYSTEM_LINE_REFERENCE IN VARCHAR2,
734 P_ORIG_SYSTEM_CURRENCY_CODE IN VARCHAR2,
735 P_ORIG_SYSTEM_SELLING_PRICE IN NUMBER,
736 P_ORIG_SYSTEM_QUANTITY IN NUMBER,
737 P_ORIG_SYSTEM_UOM IN VARCHAR2,
738 P_ORIG_SYSTEM_PURCHASE_UOM IN VARCHAR2,
739 P_ORIG_SYSTEM_PURCHASE_CURR IN VARCHAR2,
740 P_ORIG_SYSTEM_PURCHASE_PRICE IN NUMBER,
741 P_ORIG_SYSTEM_PURCHASE_QUANT IN NUMBER,
742 P_ORIG_SYSTEM_AGREEMENT_UOM IN VARCHAR2,
743 P_ORIG_SYSTEM_AGREEMENT_NAME IN VARCHAR2,
744 P_ORIG_SYSTEM_AGREEMENT_TYPE IN VARCHAR2,
745 P_ORIG_SYSTEM_AGREEMENT_STATUS IN VARCHAR2,
746 P_ORIG_SYSTEM_AGREEMENT_CURR IN VARCHAR2,
747 P_ORIG_SYSTEM_AGREEMENT_PRICE IN NUMBER,
748 P_ORIG_SYSTEM_AGREEMENT_QUANT IN NUMBER,
749 P_ORIG_SYSTEM_ITEM_NUMBER IN VARCHAR2,
750 P_ORDER_TYPE IN VARCHAR2,
751 P_ORDER_CATEGORY IN VARCHAR2,
752 P_AGREEMENT_TYPE IN VARCHAR2,
753 P_AGREEMENT_NAME IN VARCHAR2,
754 P_AGREEMENT_PRICE IN NUMBER,
755 P_AGREEMENT_UOM IN VARCHAR2,
756 P_LINE_CURRENCY IN VARCHAR2,
757 P_EXCHANGE_RATE IN VARCHAR2,
758 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
759 P_EXCHANGE_RATE_DATE IN DATE,
760 P_PO_NUMBER IN VARCHAR2,
761 P_PO_RELEASE_NUMBER IN VARCHAR2,
762 P_PO_TYPE IN VARCHAR2,
763 P_INVOICE_NUMBER IN VARCHAR2,
764 P_DATE_INVOICED IN DATE,
765 P_ORDER_NUMBER IN VARCHAR2,
766 P_DATE_ORDERED IN DATE,
767 P_DATE_SHIPPED IN DATE,
768 P_LINE_CLAIMED_AMOUNT IN NUMBER,
769 P_PURCHASE_PRICE IN NUMBER,
770 P_PURCHASE_UOM IN VARCHAR2,
771 P_SELLING_PRICE IN NUMBER,
772 P_UOM IN VARCHAR2,
773 P_QUANTITY IN NUMBER,
774 P_LINE_CREDIT_CODE IN VARCHAR2,
775 P_UPC_CODE IN VARCHAR2,
776 P_ITEM_DESCRIPTION IN VARCHAR2,
777 P_RESPONSE_TYPE IN VARCHAR2,
778 P_RESPONSE_CODE IN VARCHAR2,
779 P_FOLLOWUP_ACTION_CODE IN VARCHAR2,
780 P_REJECT_REASON_CODE IN VARCHAR2,
781 P_CREDIT_ADVICE_DATE IN VARCHAR2,
782 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
783 P_ATTRIBUTE1 IN VARCHAR2,
784 P_ATTRIBUTE2 IN VARCHAR2,
785 P_ATTRIBUTE3 IN VARCHAR2,
786 P_ATTRIBUTE4 IN VARCHAR2,
787 P_ATTRIBUTE5 IN VARCHAR2,
788 P_ATTRIBUTE6 IN VARCHAR2,
789 P_ATTRIBUTE7 IN VARCHAR2,
790 P_ATTRIBUTE8 IN VARCHAR2,
791 P_ATTRIBUTE9 IN VARCHAR2,
792 P_ATTRIBUTE10 IN VARCHAR2,
793 P_ATTRIBUTE11 IN VARCHAR2,
794 P_ATTRIBUTE12 IN VARCHAR2,
795 P_ATTRIBUTE13 IN VARCHAR2,
796 P_ATTRIBUTE14 IN VARCHAR2,
797 P_ATTRIBUTE15 IN VARCHAR2,
798 P_INVENTORY_ITEM_SEGMENT1 IN VARCHAR2,
799 P_INVENTORY_ITEM_SEGMENT2 IN VARCHAR2,
800 P_INVENTORY_ITEM_SEGMENT3 IN VARCHAR2,
801 P_INVENTORY_ITEM_SEGMENT4 IN VARCHAR2,
802 P_INVENTORY_ITEM_SEGMENT5 IN VARCHAR2,
803 P_INVENTORY_ITEM_SEGMENT6 IN VARCHAR2,
804 P_INVENTORY_ITEM_SEGMENT7 IN VARCHAR2,
805 P_INVENTORY_ITEM_SEGMENT8 IN VARCHAR2,
806 P_INVENTORY_ITEM_SEGMENT9 IN VARCHAR2,
807 P_INVENTORY_ITEM_SEGMENT10 IN VARCHAR2,
808 P_INVENTORY_ITEM_SEGMENT11 IN VARCHAR2,
809 P_INVENTORY_ITEM_SEGMENT12 IN VARCHAR2,
810 P_INVENTORY_ITEM_SEGMENT13 IN VARCHAR2,
811 P_INVENTORY_ITEM_SEGMENT14 IN VARCHAR2,
812 P_INVENTORY_ITEM_SEGMENT15 IN VARCHAR2,
813 P_INVENTORY_ITEM_SEGMENT16 IN VARCHAR2,
814 P_INVENTORY_ITEM_SEGMENT17 IN VARCHAR2,
815 P_INVENTORY_ITEM_SEGMENT18 IN VARCHAR2,
816 P_INVENTORY_ITEM_SEGMENT19 IN VARCHAR2,
817 P_INVENTORY_ITEM_SEGMENT20 IN VARCHAR2,
818 P_HEADER_ATTRIBUTE_CATEGORY IN VARCHAR2,
819 P_HEADER_ATTRIBUTE1 IN VARCHAR2,
820 P_HEADER_ATTRIBUTE2 IN VARCHAR2,
821 P_HEADER_ATTRIBUTE3 IN VARCHAR2,
822 P_HEADER_ATTRIBUTE4 IN VARCHAR2,
823 P_HEADER_ATTRIBUTE5 IN VARCHAR2,
824 P_HEADER_ATTRIBUTE6 IN VARCHAR2,
825 P_HEADER_ATTRIBUTE7 IN VARCHAR2,
826 P_HEADER_ATTRIBUTE8 IN VARCHAR2,
827 P_HEADER_ATTRIBUTE9 IN VARCHAR2,
828 P_HEADER_ATTRIBUTE10 IN VARCHAR2,
829 P_HEADER_ATTRIBUTE11 IN VARCHAR2,
830 P_HEADER_ATTRIBUTE12 IN VARCHAR2,
831 P_HEADER_ATTRIBUTE13 IN VARCHAR2,
832 P_HEADER_ATTRIBUTE14 IN VARCHAR2,
833 P_HEADER_ATTRIBUTE15 IN VARCHAR2,
834 P_LINE_ATTRIBUTE_CATEGORY IN VARCHAR2,
835 P_LINE_ATTRIBUTE1 IN VARCHAR2,
836 P_LINE_ATTRIBUTE2 IN VARCHAR2,
837 P_LINE_ATTRIBUTE3 IN VARCHAR2,
838 P_LINE_ATTRIBUTE4 IN VARCHAR2,
839 P_LINE_ATTRIBUTE5 IN VARCHAR2,
840 P_LINE_ATTRIBUTE6 IN VARCHAR2,
841 P_LINE_ATTRIBUTE7 IN VARCHAR2,
842 P_LINE_ATTRIBUTE8 IN VARCHAR2,
843 P_LINE_ATTRIBUTE9 IN VARCHAR2,
844 P_LINE_ATTRIBUTE10 IN VARCHAR2,
845 P_LINE_ATTRIBUTE11 IN VARCHAR2,
846 P_LINE_ATTRIBUTE12 IN VARCHAR2,
847 P_LINE_ATTRIBUTE13 IN VARCHAR2,
848 P_LINE_ATTRIBUTE14 IN VARCHAR2,
849 P_LINE_ATTRIBUTE15 IN VARCHAR2,
850 P_RESALE_LINE_INT_ID IN NUMBER,
851 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
852 P_BILL_TO_PARTY IN VARCHAR2,
853 P_BILL_TO_PARTY_SITE IN VARCHAR2,
854 P_SHIP_TO_PARTY IN VARCHAR2,
855 P_SHIP_TO_PARTY_SITE IN VARCHAR2,
856 P_ORIG_BILL_TO_PARTY IN VARCHAR2,
857 P_ORIG_BILL_TO_PARTY_SITE IN VARCHAR2,
858 P_ORIG_SHIP_TO_PARTY IN VARCHAR2,
859 P_ORIG_SHIP_TO_PARTY_SITE IN VARCHAR2,
860 P_ORIG_END_CUST_PARTY IN VARCHAR2,
861 P_ORIG_END_CUST_PARTY_SITE IN VARCHAR2,
862 P_ORG_ID IN VARCHAR2,
863 P_LINE_STATUS IN VARCHAR2,
864 P_DISPUTE_CODE IN VARCHAR2,
865 P_INVOICE_LINE_NUMBER IN VARCHAR2,
866 P_ORDER_LINE_NUMBER IN VARCHAR2,
867 P_SUPPLIER_ITEM_COST IN NUMBER,
868 P_SUPPLIER_ITEM_UOM IN VARCHAR2
869 )
870 IS
871
872 l_api_name CONSTANT VARCHAR2(30) := 'RESALE_WEBADI';
873 l_api_version_number CONSTANT NUMBER := 1.0;
874
875
876 CURSOR c_chk_record_exists(pc_batch_number VARCHAR2)
877 IS
878 SELECT COUNT(batch_number)
879 FROM ozf_resale_batches_all
880 WHERE batch_number = pc_batch_number;
881
882
883 CURSOR c_chk_line_exists(pc_batch_number VARCHAR2)
884 IS
885 SELECT COUNT(b.resale_batch_id),a.status_code
886 FROM ozf_resale_batches_all a, ozf_resale_lines_int_all b
887 WHERE a.resale_batch_id = b.resale_batch_id
888 AND a.batch_number = pc_batch_number
889 GROUP BY a.resale_batch_id, a.status_code;
890
891
892 CURSOR c_get_update_record(pc_batch_number VARCHAR2, pc_resale_line_int_id NUMBER)
893 IS
894 SELECT a.resale_batch_id, a.object_version_number,
895 b.object_version_number
896 FROM ozf_resale_batches_all a, ozf_resale_lines_int_all b
897 WHERE a.resale_batch_id = b.resale_batch_id
898 AND a.batch_number = pc_batch_number
899 AND b.resale_line_int_id = pc_resale_line_int_id;
900
901 CURSOR C
902 IS
903 SELECT value
904 FROM v$parameter
905 WHERE name = 'utl_file_dir';
906
907 CURSOR csr_get_party_site_id(cv_site_number IN VARCHAR2) IS
908 SELECT party_site_id
909 FROM hz_party_sites
910 WHERE party_site_number = cv_site_number;
911
912
913 l_batch_count NUMBER := 0;
914 l_resale_batch_id NUMBER;
915 l_resale_line_int_id NUMBER;
916 l_resale_line_batch_id NUMBER;
917 l_batch_number VARCHAR2(3200);
918 l_batch_insert_flag VARCHAR2(1);
919 l_batch_update_flag VARCHAR2(1);
920 l_batch_obj NUMBER := 1;
921 l_line_obj NUMBER := 1;
922 l_line_count NUMBER := 0;
923 l_amount NUMBER := 0;
924 l_prev_batch_number VARCHAR2(3200);
925 l_msg_count NUMBER;
926 l_msg_data VARCHAR2(32000);
927 l_credit_code VARCHAR2(10);
928 l_status_code VARCHAR2(30);
929 l_line_status VARCHAR2(30);
930 l_batch_status VARCHAR2(30);
931 l_location_count NUMBER := 0;
932
933 l_resale_batch_rec ozf_resale_batches_all%rowtype;
934 l_int_line_tbl ozf_pre_process_pvt.resale_line_int_tbl_type := ozf_pre_process_pvt.resale_line_int_tbl_type();
935 l_resale_line_int_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
936 l_object_version_no_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
937 l_total_claimed_amount NUMBER := 0;
938 j NUMBER;
939 l_file utl_file.file_type;
940 l_file_dir VARCHAR2(500);
941 l_file_name VARCHAR2(3200);
942 l_text VARCHAR2(32000);
943 l_error_message VARCHAR2(3200);
944 l_batch_status_n VARCHAR2(60);
945 l_code VARCHAR2(30) := null;
946
947 -- Exceptions
948 ozf_webadi_error EXCEPTION;
949 l_org_id NUMBER;
950 l_direct_order_flag BOOLEAN := false;
951 l_partner_party_id NUMBER;
952
953 CURSOR csr_get_org_id(cv_org_name IN VARCHAR2) IS
954 SELECT organization_id
955 FROM hr_operating_units
956 WHERE name = cv_org_name;
957
958 BEGIN
959
960 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
961
962 x_return_status := FND_API.G_RET_STS_SUCCESS;
963
964 -- R12 MOAC Enhancement (+)
965 IF P_ORG_ID IS NULL THEN
966 fnd_message.set_name ('OZF', 'OZF_ENTER_OPEARTING_UNIT');
967 l_error_message := fnd_message.get();
968 l_code := 'OZF_ENTER_OPEARTING_UNIT';
969 raise_application_error( -20000, l_error_message);
970 END IF;
971
972 OPEN csr_get_org_id(P_ORG_ID);
973 FETCH csr_get_org_id INTO l_org_id;
974 CLOSE csr_get_org_id;
975
976 --l_org_id := MO_GLOBAL.get_valid_org(p_org_id);
977
978 IF l_org_id IS NULL THEN
979 fnd_message.set_name ('OZF', 'OZF_ENTER_OPERATING_UNIT');
980 l_error_message := fnd_message.get();
981 l_code := 'OZF_ENTER_OPERATING_UNIT';
982 raise_application_error( -20000, l_error_message);
983 --x_return_status := FND_API.G_RET_STS_ERROR;
984 --RAISE FND_API.G_EXC_ERROR;
985 END IF;
986
987 MO_GLOBAL.set_policy_context('S', l_org_id);
988 -- R12 MOAC Enhancement (-)
989 OZF_UTILITY_PVT.debug_message('Private API: 1');
990
991 -- Batch Level Required Value Validation
992
993 IF p_report_start_date IS NULL THEN
994 fnd_message.set_name ('OZF', 'OZF_ENTER_REPORT_START_DATE');
995 l_error_message := fnd_message.get();
996 l_code := 'OZF_ENTER_REPORT_START_DATE';
997 raise_application_error( -20000, l_error_message);
998 END IF;
999
1000 IF p_report_end_date IS NULL THEN
1001 fnd_message.set_name ('OZF', 'OZF_ENTER_REPORT_END_DATE');
1002 l_error_message := fnd_message.get();
1003 l_code := 'OZF_ENTER_REPORT_END_DATE';
1004 raise_application_error( -20000, l_error_message);
1005 END IF;
1006
1007 IF p_report_start_date > p_report_end_date THEN
1008 fnd_message.set_name ('OZF', 'OZF_END_DATE_LESS_START_DATE');
1009 l_error_message := fnd_message.get();
1010 l_code := 'OZF_END_DATE_LESS_START_DATE';
1011 raise_application_error( -20000, l_error_message);
1012 END IF;
1013
1014 IF p_partner_party_id IS NULL THEN
1015 fnd_message.set_name ('OZF', 'OZF_ENTER_PARTNER_ID');
1016 l_error_message := fnd_message.get();
1017 l_code := 'OZF_ENTER_PARTNER_ID';
1018 raise_application_error( -20000, l_error_message);
1019 END IF;
1020
1021 IF p_batch_type IS NULL AND p_transaction_type IS NULL THEN
1022 fnd_message.set_name ('OZF', 'OZF_ENTER_BATCH_TYPE');
1023 l_error_message := fnd_message.get();
1024 l_code := 'OZF_ENTER_BATCH_TYPE';
1025 raise_application_error( -20000, l_error_message);
1026 END IF;
1027
1028 -- Line Level Required Value Validation
1029
1030 IF p_item_description IS NULL THEN
1031 IF p_orig_system_item_number IS NULL THEN
1032 fnd_message.set_name ('OZF', 'OZF_ENTER_ITEM_NUMBER');
1033 l_error_message := fnd_message.get();
1034 l_code := 'OZF_ENTER_ITEM_NUMBER';
1035 raise_application_error( -20000, l_error_message);
1036 END IF;
1037 END IF;
1038
1039 IF p_quantity IS NULL THEN
1040 IF p_orig_system_quantity IS NULL THEN
1041 fnd_message.set_name ('OZF', 'OZF_ENTER_QUANTITY');
1042 l_error_message := fnd_message.get();
1043 l_code := 'OZF_ENTER_QUANTITY';
1044 raise_application_error( -20000, l_error_message);
1045 END IF;
1046 END IF;
1047
1048 IF p_order_number IS NULL THEN
1049 fnd_message.set_name ('OZF', 'OZF_ENTER_ORDER_NUMBER');
1050 l_error_message := fnd_message.get();
1051 l_code := 'OZF_ENTER_ORDER_NUMBER';
1052 raise_application_error( -20000, l_error_message);
1053 END IF;
1054 IF p_date_ordered IS NULL THEN
1055 fnd_message.set_name ('OZF', 'OZF_ENTER_ORDER_DATE');
1056 l_error_message := fnd_message.get();
1057 l_code := 'OZF_ENTER_ORDER_DATE';
1058 raise_application_error( -20000, l_error_message);
1059 END IF;
1060
1061 IF p_date_ordered < p_report_start_date THEN
1062 fnd_message.set_name ('OZF', 'OZF_ORDER_DATE_LESS_START_DATE');
1063 l_error_message := fnd_message.get();
1064 l_code := 'OZF_ORDER_DATE_LESS_START_DATE';
1065 raise_application_error( -20000, l_error_message);
1066 END IF;
1067
1068 IF p_date_ordered > p_report_end_date THEN
1069 fnd_message.set_name ('OZF', 'OZF_ORDER_DATE_GRT_END_DATE');
1070 l_error_message := fnd_message.get();
1071 l_code := 'OZF_ORDER_DATE_GRT_END_DATE';
1072 raise_application_error( -20000, l_error_message);
1073 END IF;
1074
1075 IF p_currency IS NULL THEN
1076 fnd_message.set_name ('OZF', 'OZF_ENTER_CURRENCY');
1077 l_error_message := fnd_message.get();
1078 l_code := 'OZF_ENTER_CURRENCY';
1079 raise_application_error( -20000, l_error_message);
1080 END IF;
1081
1082
1083 -- Start RSATYAVA Fix for Bug#10085365
1084 IF p_uom IS NULL THEN
1085 IF p_orig_system_uom IS NULL THEN
1086 fnd_message.set_name ('OZF', 'OZF_ENTER_UOM');
1087 l_error_message := fnd_message.get();
1088 l_code := 'OZF_ENTER_UOM';
1089 raise_application_error( -20000, l_error_message);
1090 END IF;
1091 END IF;
1092
1093 -- End RSATYAVA Fix for Bug#10085365
1094
1095 -- Bug#9670818(+)
1096
1097 -- REPORT_END_DATE must be lesser than SYSDATE
1098 IF (trunc(P_REPORT_END_DATE) > trunc(SYSDATE)) THEN
1099 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1100 FND_MESSAGE.set_name('OZF', 'OZF_REP_END_DATE_GT_SYSDATE');
1101 l_error_message := fnd_message.get();
1102 l_code := 'OZF_REP_END_DATE_GT_SYSDATE';
1103 raise_application_error( -20000, l_error_message);
1104 END IF;
1105 END IF;
1106
1107
1108 -- REPORT_DATE must be greater than or equal to REPORT_END_DATE and lesser than SYSDATE
1109 IF (trunc(P_REPORT_DATE) < trunc(P_REPORT_END_DATE) OR trunc(P_REPORT_DATE) > trunc(SYSDATE)) THEN
1110 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1111 FND_MESSAGE.set_name('OZF', 'OZF_REP_DATE_GT_SYSDATE');
1112 l_code := 'OZF_REP_DATE_GT_SYSDATE';
1113 l_error_message := fnd_message.get();
1114 raise_application_error( -20000, l_error_message);
1115 END IF;
1116 END IF;
1117
1118 -- Bug#9670818(-)
1119
1120
1121 -- Bugfix 10234121 -------------------------------------------------------------------------
1122 IF P_BATCH_TYPE ='CHARGEBACK' THEN
1123 IF (P_BILL_TO_PARTY_NAME IS NOT NULL AND P_BILL_TO_LOCATION IS NOT NULL) THEN
1124 l_location_count := check_location(TO_NUMBER(P_BILL_TO_PARTY_NAME),P_BILL_TO_LOCATION,'BILL_TO');
1125 IF l_location_count = 0 THEN
1126 fnd_message.set_name ('OZF', 'OZF_BILL_TO_LOCATION_WRNG');
1127 l_error_message := fnd_message.get();
1128 l_code := 'OZF_BILL_TO_LOCATION_WRNG';
1129 raise_application_error( -20000, l_error_message);
1130 END IF;
1131 END IF;
1132
1133 IF (P_SHIP_TO_PARTY_NAME IS NOT NULL AND P_SHIP_TO_LOCATION IS NOT NULL) THEN
1134 l_location_count := check_location(TO_NUMBER(P_SHIP_TO_PARTY_NAME),P_SHIP_TO_LOCATION,'SHIP_TO');
1135 IF l_location_count = 0 THEN
1136 fnd_message.set_name ('OZF', 'OZF_SHIP_TO_LOCATION_WRNG');
1137 l_error_message := fnd_message.get();
1138 l_code := 'OZF_SHIP_TO_LOCATION_WRNG';
1139 raise_application_error( -20000, l_error_message);
1140 END IF;
1141 END IF;
1142
1143 IF (P_SHIP_FROM_PARTY_NAME IS NOT NULL AND P_SHIP_FROM_LOCATION IS NOT NULL) THEN
1144 l_location_count := check_location(TO_NUMBER(P_SHIP_FROM_PARTY_NAME),P_SHIP_FROM_LOCATION,'SHIP_FROM');
1145 IF l_location_count = 0 THEN
1146 fnd_message.set_name ('OZF', 'OZF_SHIP_FROM_LOCATION_WRNG');
1147 l_error_message := fnd_message.get();
1148 l_code := 'OZF_SHIP_FROM_LOCATION_WRNG';
1149 raise_application_error( -20000, l_error_message);
1150 END IF;
1151 END IF;
1152
1153 IF (P_SOLD_FROM_PARTY_NAME IS NOT NULL AND P_SOLD_FROM_LOCATION IS NOT NULL) THEN
1154 l_location_count := check_location(TO_NUMBER(P_SOLD_FROM_PARTY_NAME),P_SOLD_FROM_LOCATION,'SOLD_FROM');
1155 IF l_location_count = 0 THEN
1156 fnd_message.set_name ('OZF', 'OZF_SOLD_FROM_LOCATION_WRNG');
1157 l_error_message := fnd_message.get();
1158 l_code := 'OZF_SOLD_FROM_LOCATION_WRNG';
1159 raise_application_error( -20000, l_error_message);
1160 END IF;
1161 END IF;
1162
1163 END IF;
1164
1165 ---End Bugfix 10234121 ----------------------------------------------------------------------
1166
1167 -- Writing to a file to record the data and debug for any problems
1168
1169 l_file_dir := FND_PROFILE.VALUE('BNE_SERVER_LOG_PATH');
1170
1171 IF l_file_dir IS NULL THEN
1172
1173 OPEN C;
1174 FETCH C INTO l_file_dir;
1175 CLOSE C;
1176
1177 END IF;
1178
1179 IF l_file_dir IS NOT NULL THEN
1180
1181 l_file_name := 'ozfrsladi-'||p_batch_number||'.log';
1182 BEGIN
1183 l_file := utl_file.fopen( l_file_dir, l_file_name, 'w' , 32000);
1184 EXCEPTION
1185 WHEN UTL_FILE.INVALID_PATH THEN
1186 SELECT decode(instr(value,',',1,1), 0, value, substr(value,1,instr(value,',',1,1)-1))
1187 INTO l_file_dir
1188 FROM v$parameter
1189 WHERE name = 'utl_file_dir';
1190 l_file := utl_file.fopen( l_file_dir, l_file_name, 'w' , 32000);
1191 END;
1192 END IF;
1193
1194
1195 G_COUNT := G_COUNT + 1;
1196
1197 -- Record all the values and store them in a file
1198 l_text := l_text ||'Batch Number '||p_batch_number||' \n ';
1199 l_text := l_text ||'Batch Type '||p_batch_type||' \n ';
1200 l_text := l_text ||'batch_count '||p_batch_count||' \n ';
1201 l_text := l_text ||'report_date '||p_report_date||' \n ';
1202 l_text := l_text ||'report_start_date'||p_report_start_date||' \n ';
1203 l_text := l_text ||'report_end_date '||p_report_end_date||' \n ';
1204 l_text := l_text ||'comments '||p_comments||' \n ';
1205 l_text := l_text ||'partner_claim_number '||p_partner_claim_number||' \n ';
1206 l_text := l_text ||'transaction_purpose_code '||p_transaction_purpose||' \n ';
1207 l_text := l_text ||'transaction_type_code '||p_transaction_type||' \n ';
1208 l_text := l_text ||'partner_type '||p_partner_type||' \n ';
1209 l_text := l_text ||'partner_party_id '||p_partner_party_id||' \n ';
1210 l_text := l_text ||'partner_cust_account_id '||p_partner_cust_account_id||' \n ';
1211 l_text := l_text ||'partner_site_id '||p_partner_site_id||' \n ';
1212 l_text := l_text ||'partner_contact_name '||p_partner_contact_name||' \n ';
1213 l_text := l_text ||'partner_email '||p_partner_email||' \n ';
1214 l_text := l_text ||'partner_phone '||p_partner_phone||' \n ';
1215 l_text := l_text ||'partner_fax'||p_partner_fax||' \n ';
1216 l_text := l_text ||'currency_code '||p_currency||' \n ';
1217 l_text := l_text ||'claimed_amount '||p_claimed_amount||' \n ';
1218 l_text := l_text ||'credit_code '||p_credit_code||' \n ';
1219 l_text := l_text ||'Completed Batch Assignments' ||' \n ';
1220
1221 IF p_resale_line_int_id IS NOT NULL THEN
1222 l_text := l_text ||'Resale Line Int ID from webadi '|| p_resale_line_int_id||' \n ';
1223 END IF;
1224 l_text := l_text ||'Response Type '||p_response_type||' \n ';
1225 l_text := l_text ||'Response Code '||p_response_code||' \n ';
1226 l_text := l_text ||'Reject Reason Code '||p_reject_reason_code||' \n ';
1227 l_text := l_text ||'Followup Action Code '||p_followup_action_code||' \n ';
1228 l_text := l_text ||'Resale Transfer Type '||p_resale_transfer_type||' \n ';
1229 l_text := l_text ||'Transfer Movement Type '||p_transfer_mvmt_type||' \n ';
1230 l_text := l_text ||'Transfer Date '||p_transfer_date||' \n ';
1231 l_text := l_text ||'End Customer Site Use '||p_end_cust_site_use_code||' \n ';
1232 l_text := l_text ||'End Customer Party ID '||p_end_cust_party_name||' \n ';
1233 l_text := l_text ||'End Customer Location '||p_end_cust_location||' \n ';
1234 l_text := l_text ||'End Customer Address '||p_end_cust_address||' \n ';
1235 l_text := l_text ||'End Customer City '||p_end_cust_city||' \n ';
1236 l_text := l_text ||'End Customer State '||p_end_cust_state||' \n ';
1237 l_text := l_text ||'End Customer Zip '||p_end_cust_postal_code||' \n ';
1238 l_text := l_text ||'End Customer Country '||p_end_cust_country||' \n ';
1239 l_text := l_text ||'End Customer Contact '||p_end_cust_contact_name||' \n ';
1240 l_text := l_text ||'End Customer Email '||p_end_cust_email||' \n ';
1241 l_text := l_text ||'End Customer Phone '||p_end_cust_phone||' \n ';
1242 l_text := l_text ||'End Customer Fax '||p_end_cust_fax||' \n ';
1243 l_text := l_text ||'[BUG 4186465] Bill To Party '||to_number(p_bill_to_party)||' \n ';
1244 l_text := l_text ||'[BUG 4186465] Bill To Party Site '||p_bill_to_party_site||' \n ';
1245 l_text := l_text ||'Bill To Party ID '||to_number(p_bill_to_PARTY_NAME)||' \n ';
1246 l_text := l_text ||'Bill To Location'||p_bill_to_location||' \n ';
1247 l_text := l_text ||'Bill To DUNS'||p_bill_to_duns_number||' \n ';
1248 l_text := l_text ||'Bill To Address '||p_bill_to_address||' \n ';
1249 l_text := l_text ||'Bill To City '||p_bill_to_city||' \n ';
1250 l_text := l_text ||'Bill To State '||p_bill_to_state||' \n ';
1251 l_text := l_text ||'Bill To Zip '||p_bill_to_postal_code||' \n ';
1252 l_text := l_text ||'Bill To Country '||p_bill_to_country||' \n ';
1253 l_text := l_text ||'Bill To Contact '||p_bill_to_contact_name||' \n ';
1254 l_text := l_text ||'Bill To Email '||p_bill_to_email||' \n ';
1255 l_text := l_text ||'Bill To Phone '||p_bill_to_phone||' \n ';
1256 l_text := l_text ||'Bill To Fax '||p_bill_to_fax||' \n ';
1257 l_text := l_text ||'[BUG 4186465] Ship To Party '||to_number(p_ship_to_party)||' \n ';
1258 l_text := l_text ||'[BUG 4186465] Ship To Party Site '||p_ship_to_party_site||' \n ';
1259 l_text := l_text ||'Ship To Party ID '||to_number(p_ship_to_PARTY_NAME)||' \n ';
1260 l_text := l_text ||'Ship To DUNS '||p_ship_to_duns_number||' \n ';
1261 l_text := l_text ||'Ship To Location '||p_ship_to_location||' \n ';
1262 l_text := l_text ||'Ship To Address '||p_ship_to_address||' \n ';
1263 l_text := l_text ||'Ship To City '||p_ship_to_city||' \n ';
1264 l_text := l_text ||'Ship To State '||p_ship_to_state||' \n ';
1265 l_text := l_text ||'Ship To Zip '||p_ship_to_postal_code||' \n ';
1266 l_text := l_text ||'Ship To Country '||p_ship_to_country||' \n ';
1267 l_text := l_text ||'Ship To Contact '||p_ship_to_contact_name||' \n ';
1268 l_text := l_text ||'Ship To Email '||p_ship_to_email||' \n ';
1269 l_text := l_text ||'Ship To Phone '||p_ship_to_phone||' \n ';
1270 l_text := l_text ||'Ship To Fax '||p_ship_to_fax||' \n ';
1271 l_text := l_text ||'Ship From Party '||to_number(p_ship_from_PARTY_NAME)||' \n ';
1272 l_text := l_text ||'Ship From Location '||p_ship_from_location||' \n ';
1273 l_text := l_text ||'Ship From Address '||p_ship_from_address||' \n ';
1274 l_text := l_text ||'Ship From City '||p_ship_from_city||' \n ';
1275 l_text := l_text ||'Ship From State '||p_ship_from_state||' \n ';
1276 l_text := l_text ||'Ship From Zip '||p_ship_from_postal_code||' \n ';
1277 l_text := l_text ||'Ship From Country '||p_ship_from_country||' \n ';
1278 l_text := l_text ||'Ship From Contact '||p_ship_from_contact_name||' \n ';
1279 l_text := l_text ||'Ship From Email '||p_ship_from_email||' \n ';
1280 l_text := l_text ||'Ship From Phone '||p_ship_from_phone||' \n ';
1281 l_text := l_text ||'Ship From Fax '||p_ship_from_fax||' \n ';
1282 l_text := l_text ||'Sold From Party '||to_number(p_sold_from_PARTY_NAME)||' \n ';
1283 l_text := l_text ||'Sold From Location '||p_sold_from_location||' \n ';
1284 l_text := l_text ||'Sold From Address '||p_sold_from_address||' \n ';
1285 l_text := l_text ||'Sold From City '||p_sold_from_city||' \n ';
1286 l_text := l_text ||'Sold From State '||p_sold_from_state||' \n ';
1287 l_text := l_text ||'Sold From Zip '||p_sold_from_postal_code||' \n ';
1288 l_text := l_text ||'Sold From Country '||p_sold_from_country||' \n ';
1289 l_text := l_text ||'Sold From Contact '||p_sold_from_contact_name||' \n ';
1290 l_text := l_text ||'Sold From Email '||p_sold_from_email||' \n ';
1291 l_text := l_text ||'Sold From Phone '||p_sold_from_phone||' \n ';
1292 l_text := l_text ||'Sold From Fax '||p_sold_from_fax||' \n ';
1293 l_text := l_text ||'Quantity '||p_quantity||' \n ';
1294 l_text := l_text ||'UOM '||p_uom||' \n ';
1295 l_text := l_text ||'Currency '||p_line_currency||' \n ';
1296 l_text := l_text ||'Exchange Rate '||p_exchange_rate||' \n ';
1297 l_text := l_text ||'Exchange Rate Type '||p_exchange_rate_type||' \n ';
1298 l_text := l_text ||'Exchange Rate Date '||p_exchange_rate_date||' \n ';
1299 l_text := l_text ||'Selling Price '||p_selling_price||' \n ';
1300 l_text := l_text ||'Purchase UOM '||p_purchase_uom||' \n ';
1301 l_text := l_text ||'Invoice Number '||p_invoice_number||' \n ';
1302 l_text := l_text ||'Date Invoiced '||p_date_invoiced||' \n ';
1303 l_text := l_text ||'Date Shipped '||p_date_shipped||' \n ';
1304 l_text := l_text ||'Credit Advice Date '||p_credit_advice_date||' \n ';
1305 l_text := l_text ||'Inventory Item ID '||to_number(p_item_description)||' \n ';
1306 l_text := l_text ||'UPC '||p_upc_code||' \n ';
1307 l_text := l_text ||'Purchase Price '||p_purchase_price||' \n ';
1308 l_text := l_text ||'Unit Claimed Amount '||p_line_claimed_amount||' \n ';
1309 l_text := l_text ||'Total Claimed Amount '||l_total_claimed_amount||' \n ';
1310 l_text := l_text ||'Credit Code '||p_credit_code||' \n ';
1311 l_text := l_text ||'Order Type '||p_order_type||' \n ';
1312 l_text := l_text ||'Order Category '||p_order_category||' \n ';
1313 l_text := l_text ||'Order Number '||p_order_number||' \n ';
1314 l_text := l_text ||'Date Ordered '||p_date_ordered||' \n ';
1315 l_text := l_text ||'PO Number '||p_po_number||' \n ';
1316 l_text := l_text ||'PO Release Number '||p_po_release_number||' \n ';
1317 l_text := l_text ||'PO Type '||p_po_type||' \n ';
1318 l_text := l_text ||'Agreement Type '||p_agreement_type||' \n ';
1319 l_text := l_text ||'Agreement Name '||p_agreement_name||' \n ';
1320 l_text := l_text ||'Agreement Price '||p_agreement_price||' \n ';
1321 l_text := l_text ||'Agreement UOM '||p_agreement_uom||' \n ';
1322 l_text := l_text ||'Line Assignments Started'||' \n ';
1323 l_text := l_text ||'Orig System Quantity '||p_orig_system_quantity||' \n ';
1324 l_text := l_text ||'Orig System UOM '||p_orig_system_uom||' \n ';
1325 l_text := l_text ||'Orig System Currency '||p_orig_system_currency_code||' \n ';
1326 l_text := l_text ||'Orig System Selling Price '||p_orig_system_selling_price||' \n ';
1327 l_text := l_text ||'Orig System Line Reference '||p_orig_system_line_reference||' \n ';
1328 l_text := l_text ||'Orig System Purchase UOM '||p_orig_system_purchase_uom||' \n ';
1329 l_text := l_text ||'Orig System Purchase Currency '||p_orig_system_purchase_curr||' \n ';
1330 l_text := l_text ||'Orig System Purchase Price '||p_orig_system_purchase_price||' \n ';
1331 l_text := l_text ||'Orig System Purchase Quantity '||p_orig_system_purchase_quant||' \n ';
1332 l_text := l_text ||'Orig System Agreement UOM '||p_orig_system_agreement_uom||' \n ';
1333 l_text := l_text ||'Orig System Agreement Name '||p_orig_system_agreement_name||' \n ';
1334 l_text := l_text ||'Orig System Agreement Type '||p_orig_system_agreement_type||' \n ';
1335 l_text := l_text ||'Orig System Agreement Status '||p_orig_system_agreement_status||' \n ';
1336 l_text := l_text ||'Orig System Agreement Currency '||p_orig_system_agreement_curr||' \n ';
1337 l_text := l_text ||'Orig System Agreement Price '||p_orig_system_agreement_price||' \n ';
1338 l_text := l_text ||'Orig System Agreement Quantity '||p_orig_system_agreement_quant||' \n ';
1339 l_text := l_text ||'Orig System Item Number '||p_orig_system_item_number||' \n ';
1340 l_text := l_text ||'[BUG 4469837] Orig System Bill To Party '||p_orig_bill_to_party||' \n ';
1341 l_text := l_text ||'[BUG 4469837] Orig System Bill To Party Site '||p_orig_bill_to_party_site||' \n ';
1342 l_text := l_text ||'[BUG 4469837] Orig System Ship To Party '||p_orig_ship_to_party||' \n ';
1343 l_text := l_text ||'[BUG 4469837] Orig System Ship To Party Site '||p_orig_ship_to_party_site||' \n ';
1344 l_text := l_text ||'[BUG 4469837] Orig System End Customer '||p_orig_end_cust_party||' \n ';
1345 l_text := l_text ||'[BUG 4469837] Orig System End Customer Location '||p_orig_end_cust_party_site||' \n ';
1346 l_text := l_text ||'Line Status is '||p_line_status||' \n ';
1347 l_text := l_text ||'Dispute code is '||p_dispute_code||' \n ';
1348
1349 -- For Bug#9447673 SSD IDSM ER(+)
1350 l_text := l_text ||'INVOICE LINE NUMBER is '||P_INVOICE_LINE_NUMBER||' \n ';
1351 l_text := l_text ||'ORDER LINE NUMBER is '||P_ORDER_LINE_NUMBER||' \n ';
1352 l_text := l_text ||'SUPPLIER ITEM COST is '||P_SUPPLIER_ITEM_COST||' \n ';
1353 l_text := l_text ||'SUPPLIER ITEM UOM is '||P_SUPPLIER_ITEM_UOM||' \n ';
1354
1355
1356 /*For Bug#9447673 SSD IDSM ER
1357 Checking the profile of partner party and if it same
1358 as that of the party uploading the batch then the batch is considered
1359 as the direct batch. i.e., setting the direct_order_flag as 'Y'
1360 */
1361 l_partner_party_id := FND_PROFILE.VALUE('OZF_IDSM_ORDER_SRC_PARTY_ID');
1362
1363 IF P_PARTNER_PARTY_ID = l_partner_party_id THEN
1364 IF p_batch_type <> 'TRACING' THEN
1365 fnd_message.set_name ('OZF', 'OZF_DIRECT_ORDER_TRC_ERR');
1366 l_error_message := fnd_message.get();
1367 l_code := 'OZF_DIRECT_ORDER_TRC_ERR';
1368 raise_application_error(-20000, l_error_message);
1369 ELSE
1370 l_direct_order_flag := true;
1371 END IF;
1372 END IF;
1373 -- For Bug#9447673 SSD IDSM ER(-)
1374
1375
1376
1377 -- Batch record level assignments
1378
1379 l_resale_batch_rec.last_update_date := SYSDATE;
1380 l_resale_batch_rec.last_updated_by := FND_GLOBAL.User_Id;
1381 l_resale_batch_rec.last_update_login := FND_GLOBAL.User_Id;
1382 l_resale_batch_rec.batch_type := p_batch_type;
1383 l_resale_batch_rec.batch_number := p_batch_number;
1384 l_resale_batch_rec.batch_count := p_batch_count;
1385 l_resale_batch_rec.year := p_year;
1386 l_resale_batch_rec.month := p_month;
1387 l_resale_batch_rec.report_date := NVL(p_report_date, TRUNC(SYSDATE));
1388 l_resale_batch_rec.report_start_date := p_report_start_date;
1389 l_resale_batch_rec.report_end_date := p_report_end_date;
1390 l_resale_batch_rec.data_source_code := p_data_source_code;
1391 l_resale_batch_rec.reference_type := p_reference_type;
1392 l_resale_batch_rec.reference_number := p_reference_number;
1393 l_resale_batch_rec.comments := p_comments;
1394 l_resale_batch_rec.partner_claim_number := p_partner_claim_number;
1395 l_resale_batch_rec.transaction_purpose_code := nvl(p_transaction_purpose,'00');
1396 l_resale_batch_rec.transaction_type_code := p_transaction_type;
1397 l_resale_batch_rec.partner_type := p_partner_type;
1398 l_resale_batch_rec.partner_party_id := p_partner_party_id;
1399 l_resale_batch_rec.partner_cust_account_id := p_partner_cust_account_id;
1400 l_resale_batch_rec.partner_site_id := p_partner_site_id;
1401 l_resale_batch_rec.partner_contact_name := p_partner_contact_name;
1402 l_resale_batch_rec.partner_email := p_partner_email;
1403 l_resale_batch_rec.partner_phone := p_partner_phone;
1404 l_resale_batch_rec.partner_fax := p_partner_fax;
1405 l_resale_batch_rec.currency_code := p_currency;
1406 l_resale_batch_rec.claimed_amount := p_claimed_amount;
1407 l_resale_batch_rec.credit_code := p_credit_code;
1408 l_resale_batch_rec.attribute_category := p_attribute_category;
1409 l_resale_batch_rec.attribute1 := p_attribute1;
1410 l_resale_batch_rec.attribute2 := p_attribute2;
1411 l_resale_batch_rec.attribute3 := p_attribute3;
1412 l_resale_batch_rec.attribute4 := p_attribute4;
1413 l_resale_batch_rec.attribute5 := p_attribute5;
1414 l_resale_batch_rec.attribute6 := p_attribute6;
1415 l_resale_batch_rec.attribute7 := p_attribute7;
1416 l_resale_batch_rec.attribute8 := p_attribute8;
1417 l_resale_batch_rec.attribute9 := p_attribute9;
1418 l_resale_batch_rec.attribute10 := p_attribute10;
1419 l_resale_batch_rec.attribute11 := p_attribute11;
1420 l_resale_batch_rec.attribute12 := p_attribute12;
1421 l_resale_batch_rec.attribute13 := p_attribute13;
1422 l_resale_batch_rec.attribute14 := p_attribute14;
1423 l_resale_batch_rec.attribute15 := p_attribute15;
1424 l_resale_batch_rec.batch_set_id_code := 'WEBADI';
1425 l_resale_batch_rec.credit_advice_date := p_batch_credit_advice_date;
1426
1427 -- Line level record assignments
1428
1429 IF p_line_claimed_amount IS NOT NULL THEN
1430 IF p_quantity IS NOT NULL THEN
1431 l_total_claimed_amount := p_line_claimed_amount*p_quantity;
1432 ELSIF p_orig_system_quantity IS NOT NULL THEN
1433 l_total_claimed_amount := p_line_claimed_amount*p_orig_system_quantity;
1434 END IF;
1435 END IF;
1436 l_int_line_tbl.extend(1);
1437 l_int_line_tbl(1).last_update_date := SYSDATE;
1438 l_int_line_tbl(1).last_updated_by := FND_GLOBAL.User_Id;
1439 l_int_line_tbl(1).last_update_login := FND_GLOBAL.User_Id;
1440 l_int_line_tbl(1).response_type := p_response_type;
1441 l_int_line_tbl(1).response_code := p_response_code;
1442 l_int_line_tbl(1).reject_reason_code := p_reject_reason_code;
1443 l_int_line_tbl(1).followup_action_code := p_followup_action_code;
1444 l_int_line_tbl(1).resale_transfer_type := p_resale_transfer_type;
1445 l_int_line_tbl(1).product_transfer_movement_type := p_transfer_mvmt_type;
1446 l_int_line_tbl(1).product_transfer_date := p_transfer_date;
1447 l_int_line_tbl(1).end_cust_site_use_code := p_end_cust_site_use_code;
1448 l_int_line_tbl(1).end_cust_party_id := to_number(p_end_cust_party_name);
1449 l_int_line_tbl(1).end_cust_location := p_end_cust_location;
1450 l_int_line_tbl(1).end_cust_address := p_end_cust_address;
1451 l_int_line_tbl(1).end_cust_city := p_end_cust_city;
1452 l_int_line_tbl(1).end_cust_state := p_end_cust_state;
1453 l_int_line_tbl(1).end_cust_postal_code := p_end_cust_postal_code;
1454 l_int_line_tbl(1).end_cust_country := p_end_cust_country;
1455 l_int_line_tbl(1).end_cust_contact_name := p_end_cust_contact_name;
1456 l_int_line_tbl(1).end_cust_email := p_end_cust_email;
1457 l_int_line_tbl(1).end_cust_phone := p_end_cust_phone;
1458 l_int_line_tbl(1).end_cust_fax := p_end_cust_fax;
1459 -- [BEGIN OF BUG 4198442 Fixing]
1460 l_int_line_tbl(1).bill_to_party_id := to_number(p_bill_to_party);
1461 OPEN csr_get_party_site_id(p_bill_to_party_site);
1462 FETCH csr_get_party_site_id INTO l_int_line_tbl(1).bill_to_party_site_id;
1463 CLOSE csr_get_party_site_id;
1464 -- [END OF BUG 4198442 Fixing]
1465 l_int_line_tbl(1).bill_to_cust_account_id := to_number(p_bill_to_PARTY_NAME);
1466 l_int_line_tbl(1).bill_to_location := p_bill_to_location;
1467 l_int_line_tbl(1).bill_to_duns_number := p_bill_to_duns_number;
1468 l_int_line_tbl(1).bill_to_address := p_bill_to_address;
1469 l_int_line_tbl(1).bill_to_city := p_bill_to_city;
1470 l_int_line_tbl(1).bill_to_state := p_bill_to_state;
1471 l_int_line_tbl(1).bill_to_postal_code := p_bill_to_postal_code;
1472 l_int_line_tbl(1).bill_to_country := p_bill_to_country;
1473 l_int_line_tbl(1).bill_to_contact_name := p_bill_to_contact_name;
1474 l_int_line_tbl(1).bill_to_email := p_bill_to_email;
1475 l_int_line_tbl(1).bill_to_phone := p_bill_to_phone;
1476 l_int_line_tbl(1).bill_to_fax := p_bill_to_fax;
1477 -- [BEGIN OF BUG 4198442 Fixing]
1478 l_int_line_tbl(1).ship_to_party_id := to_number(p_ship_to_party);
1479 OPEN csr_get_party_site_id(p_ship_to_party_site);
1480 FETCH csr_get_party_site_id INTO l_int_line_tbl(1).ship_to_party_site_id;
1481 CLOSE csr_get_party_site_id;
1482 -- [END OF BUG 4198442 Fixing]
1483 l_int_line_tbl(1).ship_to_cust_account_id := to_number(p_ship_to_PARTY_NAME);
1484 l_int_line_tbl(1).ship_to_duns_number := p_ship_to_duns_number;
1485 l_int_line_tbl(1).ship_to_location := p_ship_to_location;
1486 l_int_line_tbl(1).ship_to_address := p_ship_to_address;
1487 l_int_line_tbl(1).ship_to_city := p_ship_to_city;
1488 l_int_line_tbl(1).ship_to_state := p_ship_to_state;
1489 l_int_line_tbl(1).ship_to_postal_code := p_ship_to_postal_code;
1490 l_int_line_tbl(1).ship_to_country := p_ship_to_country;
1491 l_int_line_tbl(1).ship_to_contact_name := p_ship_to_contact_name;
1492 l_int_line_tbl(1).ship_to_email := p_ship_to_email;
1493 l_int_line_tbl(1).ship_to_phone := p_ship_to_phone;
1494 l_int_line_tbl(1).ship_to_fax := p_ship_to_fax;
1495 l_int_line_tbl(1).ship_from_cust_account_id := to_number(p_ship_from_PARTY_NAME);
1496 l_int_line_tbl(1).ship_from_location := p_ship_from_location;
1497 l_int_line_tbl(1).ship_from_address := p_ship_from_address;
1498 l_int_line_tbl(1).ship_from_city := p_ship_from_city;
1499 l_int_line_tbl(1).ship_from_state := p_ship_from_state;
1500 l_int_line_tbl(1).ship_from_postal_code := p_ship_from_postal_code;
1501 l_int_line_tbl(1).ship_from_country := p_ship_from_country;
1502 l_int_line_tbl(1).ship_from_contact_name := p_ship_from_contact_name;
1503 l_int_line_tbl(1).ship_from_email := p_ship_from_email;
1504 l_int_line_tbl(1).ship_from_phone := p_ship_from_phone;
1505 l_int_line_tbl(1).ship_from_fax := p_ship_from_fax;
1506 l_int_line_tbl(1).sold_from_cust_account_id := to_number(p_sold_from_PARTY_NAME);
1507 l_int_line_tbl(1).sold_from_location := p_sold_from_location;
1508 l_int_line_tbl(1).sold_from_address := p_sold_from_address;
1509 l_int_line_tbl(1).sold_from_city := p_sold_from_city;
1510 l_int_line_tbl(1).sold_from_state := p_sold_from_state;
1511 l_int_line_tbl(1).sold_from_postal_code := p_sold_from_postal_code;
1512 l_int_line_tbl(1).sold_from_country := p_sold_from_country;
1513 l_int_line_tbl(1).sold_from_contact_name := p_sold_from_contact_name;
1514 l_int_line_tbl(1).sold_from_email := p_sold_from_email;
1515 l_int_line_tbl(1).sold_from_phone := p_sold_from_phone;
1516 l_int_line_tbl(1).sold_from_fax := p_sold_from_fax;
1517 l_int_line_tbl(1).order_number := p_order_number;
1518 l_int_line_tbl(1).date_ordered := p_date_ordered;
1519 l_int_line_tbl(1).po_number := p_po_number;
1520 l_int_line_tbl(1).po_release_number := p_po_release_number;
1521 l_int_line_tbl(1).po_type := p_po_type;
1522 l_int_line_tbl(1).agreement_type := p_agreement_type;
1523 l_int_line_tbl(1).agreement_name := p_agreement_name;
1524 l_int_line_tbl(1).agreement_price := p_agreement_price;
1525 l_int_line_tbl(1).agreement_uom_code := p_agreement_uom;
1526 l_int_line_tbl(1).orig_system_quantity := p_orig_system_quantity;
1527 l_int_line_tbl(1).orig_system_uom := p_orig_system_uom;
1528 l_int_line_tbl(1).orig_system_currency_code := p_orig_system_currency_code;
1529 l_int_line_tbl(1).orig_system_selling_price := p_orig_system_selling_price;
1530 l_int_line_tbl(1).orig_system_line_reference := p_orig_system_line_reference;
1531 l_int_line_tbl(1).orig_system_purchase_uom := p_orig_system_purchase_uom;
1532 l_int_line_tbl(1).orig_system_purchase_curr := p_orig_system_purchase_curr;
1533 l_int_line_tbl(1).orig_system_purchase_price := p_orig_system_purchase_price;
1534 l_int_line_tbl(1).orig_system_purchase_quantity := p_orig_system_purchase_quant;
1535 l_int_line_tbl(1).orig_system_agreement_uom := p_orig_system_agreement_uom;
1536 l_int_line_tbl(1).orig_system_agreement_name := p_orig_system_agreement_name;
1537 l_int_line_tbl(1).orig_system_agreement_type := p_orig_system_agreement_type;
1538 l_int_line_tbl(1).orig_system_agreement_status := p_orig_system_agreement_status;
1539 l_int_line_tbl(1).orig_system_agreement_curr := p_orig_system_agreement_curr;
1540 l_int_line_tbl(1).orig_system_agreement_price := p_orig_system_agreement_price;
1541 l_int_line_tbl(1).orig_system_agreement_quantity := p_orig_system_agreement_quant;
1542 l_int_line_tbl(1).orig_system_item_number := p_orig_system_item_number;
1543 l_int_line_tbl(1).quantity := p_quantity;
1544 l_int_line_tbl(1).uom_code := p_uom;
1545 l_int_line_tbl(1).currency_code := p_line_currency;
1546 l_int_line_tbl(1).exchange_rate := p_exchange_rate;
1547 l_int_line_tbl(1).exchange_rate_type := p_exchange_rate_type;
1548 l_int_line_tbl(1).exchange_rate_date := p_exchange_rate_date;
1549 l_int_line_tbl(1).selling_price := p_selling_price;
1550 l_int_line_tbl(1).purchase_uom_code := p_purchase_uom;
1551 l_int_line_tbl(1).invoice_number := p_invoice_number;
1552 l_int_line_tbl(1).date_invoiced := p_date_invoiced;
1553 l_int_line_tbl(1).date_shipped := p_date_shipped;
1554 l_int_line_tbl(1).credit_advice_date := p_credit_advice_date;
1555 l_int_line_tbl(1).inventory_item_segment1 := p_inventory_item_segment1;
1556 l_int_line_tbl(1).inventory_item_segment2 := p_inventory_item_segment2;
1557 l_int_line_tbl(1).inventory_item_segment3 := p_inventory_item_segment3;
1558 l_int_line_tbl(1).inventory_item_segment4 := p_inventory_item_segment4;
1559 l_int_line_tbl(1).inventory_item_segment5 := p_inventory_item_segment5;
1560 l_int_line_tbl(1).inventory_item_segment6 := p_inventory_item_segment6;
1561 l_int_line_tbl(1).inventory_item_segment7 := p_inventory_item_segment7;
1562 l_int_line_tbl(1).inventory_item_segment8 := p_inventory_item_segment8;
1563 l_int_line_tbl(1).inventory_item_segment9 := p_inventory_item_segment9;
1564 l_int_line_tbl(1).inventory_item_segment10 := p_inventory_item_segment10;
1565 l_int_line_tbl(1).inventory_item_segment11 := p_inventory_item_segment11;
1566 l_int_line_tbl(1).inventory_item_segment12 := p_inventory_item_segment12;
1567 l_int_line_tbl(1).inventory_item_segment13 := p_inventory_item_segment13;
1568 l_int_line_tbl(1).inventory_item_segment14 := p_inventory_item_segment14;
1569 l_int_line_tbl(1).inventory_item_segment15 := p_inventory_item_segment15;
1570 l_int_line_tbl(1).inventory_item_segment16 := p_inventory_item_segment16;
1571 l_int_line_tbl(1).inventory_item_segment17 := p_inventory_item_segment17;
1572 l_int_line_tbl(1).inventory_item_segment18 := p_inventory_item_segment18;
1573 l_int_line_tbl(1).inventory_item_segment19 := p_inventory_item_segment19;
1574 l_int_line_tbl(1).inventory_item_segment20 := p_inventory_item_segment20;
1575 l_int_line_tbl(1).inventory_item_id := to_number(p_item_description);
1576 l_int_line_tbl(1).upc_code := p_upc_code;
1577 l_int_line_tbl(1).purchase_price := p_purchase_price;
1578 l_int_line_tbl(1).claimed_amount := p_line_claimed_amount;
1579 l_int_line_tbl(1).total_claimed_amount := l_total_claimed_amount;
1580 l_int_line_tbl(1).credit_code := p_credit_code;
1581 l_int_line_tbl(1).order_type := p_order_type;
1582 l_int_line_tbl(1).order_category := p_order_category;
1583 -- [BEGIN OF BUG 4332301 FIXING]
1584 --l_int_line_tbl(1).tracing_flag := p_tracing_flag;
1585 IF p_tracing_flag = 'YES' THEN
1586 l_int_line_tbl(1).tracing_flag := 'T';
1587 ELSIF p_tracing_flag = 'NO' THEN
1588 l_int_line_tbl(1).tracing_flag := 'F';
1589 ELSE
1590 l_int_line_tbl(1).tracing_flag := NULL;
1591 END IF;
1592 -- [END OF BUG 4332301 FIXING]
1593 l_int_line_tbl(1).header_attribute_category := p_header_attribute_category;
1594 l_int_line_tbl(1).header_attribute1 := p_header_attribute1;
1595 l_int_line_tbl(1).header_attribute2 := p_header_attribute2;
1596 l_int_line_tbl(1).header_attribute3 := p_header_attribute3;
1597 l_int_line_tbl(1).header_attribute4 := p_header_attribute4;
1598 l_int_line_tbl(1).header_attribute5 := p_header_attribute5;
1599 l_int_line_tbl(1).header_attribute6 := p_header_attribute6;
1600 l_int_line_tbl(1).header_attribute7 := p_header_attribute7;
1601 l_int_line_tbl(1).header_attribute8 := p_header_attribute8;
1602 l_int_line_tbl(1).header_attribute9 := p_header_attribute9;
1603 l_int_line_tbl(1).header_attribute10 := p_header_attribute10;
1604 l_int_line_tbl(1).header_attribute11 := p_header_attribute11;
1605 l_int_line_tbl(1).header_attribute12 := p_header_attribute12;
1606 l_int_line_tbl(1).header_attribute13 := p_header_attribute13;
1607 l_int_line_tbl(1).header_attribute14 := p_header_attribute14;
1608 l_int_line_tbl(1).header_attribute15 := p_header_attribute15;
1609 l_int_line_tbl(1).line_attribute_category := p_line_attribute_category;
1610 l_int_line_tbl(1).line_attribute1 := p_line_attribute1;
1611 l_int_line_tbl(1).line_attribute2 := p_line_attribute2;
1612 l_int_line_tbl(1).line_attribute3 := p_line_attribute3;
1613 l_int_line_tbl(1).line_attribute4 := p_line_attribute4;
1614 l_int_line_tbl(1).line_attribute5 := p_line_attribute5;
1615 l_int_line_tbl(1).line_attribute6 := p_line_attribute6;
1616 l_int_line_tbl(1).line_attribute7 := p_line_attribute7;
1617 l_int_line_tbl(1).line_attribute8 := p_line_attribute8;
1618 l_int_line_tbl(1).line_attribute9 := p_line_attribute9;
1619 l_int_line_tbl(1).line_attribute10 := p_line_attribute10;
1620 l_int_line_tbl(1).line_attribute11 := p_line_attribute11;
1621 l_int_line_tbl(1).line_attribute12 := p_line_attribute12;
1622 l_int_line_tbl(1).line_attribute13 := p_line_attribute13;
1623 l_int_line_tbl(1).line_attribute14 := p_line_attribute14;
1624 l_int_line_tbl(1).line_attribute15 := p_line_attribute15;
1625 -- Bug 4469837 (+)
1626 IF p_orig_bill_to_party IS NOT NULL AND
1627 l_int_line_tbl(1).bill_to_party_id IS NULL AND
1628 l_int_line_tbl(1).bill_to_cust_account_id IS NULL THEN
1629 l_int_line_tbl(1).bill_to_party_name := p_orig_bill_to_party;
1630 END IF;
1631 IF p_orig_bill_to_party_site IS NOT NULL AND
1632 l_int_line_tbl(1).bill_to_party_site_id IS NULL AND
1633 l_int_line_tbl(1).bill_to_location IS NULL THEN
1634 l_int_line_tbl(1).bill_to_location := p_orig_bill_to_party_site;
1635 END IF;
1636 IF p_orig_ship_to_party IS NOT NULL AND
1637 l_int_line_tbl(1).ship_to_party_id IS NULL AND
1638 l_int_line_tbl(1).ship_to_cust_account_id IS NULL THEN
1639 l_int_line_tbl(1).ship_to_party_name := p_orig_ship_to_party;
1640 END IF;
1641 IF p_orig_ship_to_party_site IS NOT NULL AND
1642 l_int_line_tbl(1).ship_to_party_site_id IS NULL AND
1643 l_int_line_tbl(1).ship_to_location IS NULL THEN
1644 l_int_line_tbl(1).ship_to_location := p_orig_ship_to_party_site;
1645 END IF;
1646 IF p_orig_end_cust_party IS NOT NULL AND
1647 l_int_line_tbl(1).end_cust_party_id IS NULL THEN
1648 l_int_line_tbl(1).end_cust_party_name := p_orig_end_cust_party;
1649 END IF;
1650 IF p_orig_end_cust_party_site IS NOT NULL AND
1651 l_int_line_tbl(1).end_cust_party_site_id IS NULL THEN
1652 l_int_line_tbl(1).end_cust_location := p_orig_end_cust_party_site;
1653 END IF;
1654 -- Bug 4469837 (-)
1655
1656 -- R12 MOAC Enhancement (+)
1657 l_int_line_tbl(1).org_id := l_org_id;
1658 -- R12 MOAC Enhancement (+)
1659
1660 -- For Bug#9447673 SSD IDSM ER(+)
1661 l_int_line_tbl(1).invoice_line_number := p_invoice_line_number;
1662 l_int_line_tbl(1).order_line_number := p_order_line_number;
1663 l_int_line_tbl(1).supplier_item_cost := p_supplier_item_cost;
1664 l_int_line_tbl(1).supplier_item_uom := p_supplier_item_uom;
1665
1666 IF (l_direct_order_flag) THEN
1667 l_resale_batch_rec.direct_order_flag := 'Y';
1668 ELSE
1669 l_resale_batch_rec.direct_order_flag := 'N';
1670 END IF;
1671 -- For Bug#9447673 SSD IDSM ER(-)
1672
1673
1674 OPEN c_chk_line_exists (P_BATCH_NUMBER);
1675 FETCH c_chk_line_exists INTO l_line_count, l_batch_status;
1676 CLOSE c_chk_line_exists;
1677 OZF_UTILITY_PVT.debug_message('batch status '|| l_batch_status);
1678 -- Transaction Purpose Code Validations
1679 IF l_batch_status IS NOT NULL THEN
1680 IF l_batch_status = 'CLOSED' THEN
1681 fnd_message.set_name ('OZF', 'OZF_WADI_CANNOT_UPDATE');
1682 l_code := 'OZF_WADI_CANNOT_UPDATE';
1683 l_error_message := fnd_message.get();
1684 RAISE_APPLICATION_ERROR( -20000, l_error_message);
1685 ELSIF l_batch_status IN ('NEW','OPEN','PROCESSED','DISPUTED') THEN
1686 l_status_code := l_batch_status;
1687 -- [BEGIN OF BUG 4212707 FIXING] by mchang
1688 ELSE
1689 /*
1690 FND_MESSAGE.set_name('OZF', 'OZF_WADI_BATCH_NO_UPLOAD');
1691 FND_MESSAGE.set_token( 'NUMBER', P_BATCH_NUMBER);
1692 FND_MESSAGE.set_token( 'STATUS'
1693 , OZF_UTILITY_PVT.get_lookup_meaning('OZF_RESALE_BATCH_STATUS'
1694 ,l_batch_status
1695 )
1696 );
1697 */
1698 --l_error_message := FND_MESSAGE.get();
1699 l_batch_status_n := OZF_UTILITY_PVT.get_lookup_meaning('OZF_RESALE_BATCH_STATUS'
1700 ,l_batch_status
1701 );
1702 l_error_message := 'Cannot update '||l_batch_status_n||' batch';
1703 RAISE_APPLICATION_ERROR( -20000, l_error_message);
1704 -- [END OF BUG 4212707 FIXING] by mchang
1705 END IF;
1706 ELSE
1707 IF p_transaction_purpose = '00' THEN
1708 l_status_code := 'NEW';
1709 l_line_status := 'NEW';
1710 ELSIF p_transaction_purpose IS NULL THEN
1711 l_status_code := 'NEW';
1712 END IF;
1713 END IF;
1714
1715 l_text := l_text || 'Status '|| l_status_code||' \n ';
1716
1717 -- ==============================================================================
1718 -- When l_line_count = 0 then it is a new batch
1719 -- When l_line_count > 0 but p_resale_line_int_id is NULL then new line is being inserted
1720 -- to the batch
1721 -- ==============================================================================
1722
1723
1724 IF l_status_code IN ('OPEN','NEW','PROCESSED') AND
1725 (l_line_count = 0 OR p_resale_line_int_id IS NULL) THEN
1726
1727 OPEN c_chk_record_exists (P_BATCH_NUMBER);
1728 FETCH c_chk_record_exists INTO l_batch_count;
1729 CLOSE c_chk_record_exists;
1730
1731 -- ==============================================================================
1732 -- WebADI sends batch and line data together to this API and it is called
1733 -- as many times as the number of lines exists, but batch has to be created
1734 -- the first time this API call is made. Batch is created when batch count = 0
1735 -- ==============================================================================
1736
1737 IF l_status_code = 'NEW' AND l_batch_count = 0 THEN
1738 SELECT ozf_resale_batches_all_s.nextval
1739 INTO l_resale_batch_rec.resale_batch_id
1740 FROM DUAL;
1741
1742 -- ==============================================================================
1743 -- Resale Batch ID is stored as global variable for the subsequent line record creation
1744 -- ==============================================================================
1745
1746 G_RESALE_BATCH_ID := l_resale_batch_rec.resale_batch_id;
1747
1748 ozf_utility_pvt.debug_message('Resale Batch ID '||G_RESALE_BATCH_ID);
1749 ozf_utility_pvt.debug_message('Resale Batch ID in create '|| l_resale_batch_rec.resale_batch_id);
1750
1751 l_text := l_text || 'Resale Batch ID in create '|| l_resale_batch_rec.resale_batch_id||' \n ';
1752
1753 -- R12 MOAC Enhancement (+)
1754 /*
1755 SELECT TO_NUMBER(NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99))
1756 INTO l_resale_batch_rec.org_id FROM DUAL;
1757 */
1758 -- R12 MOAC Enhancement (-)
1759 BEGIN
1760
1761 OZF_RESALE_BATCHES_PKG.INSERT_ROW
1762 (
1763 px_resale_batch_id => l_resale_batch_rec.resale_batch_id
1764 ,px_object_version_number => l_batch_obj
1765 ,p_last_update_date => SYSDATE
1766 ,p_last_updated_by => FND_GLOBAL.User_Id
1767 ,p_creation_date => SYSDATE
1768 ,p_request_id => NULL
1769 ,p_created_by => FND_GLOBAL.User_Id
1770 ,p_created_from => NULL
1771 ,p_last_update_login => FND_GLOBAL.User_Id
1772 ,p_program_application_id => NULL
1773 ,p_program_update_date => NULL
1774 ,p_program_id => NULL
1775 ,p_batch_number => l_resale_batch_rec.batch_number
1776 ,p_batch_type => l_resale_batch_rec.batch_type
1777 ,p_batch_count => l_resale_batch_rec.batch_count
1778 ,p_year => l_resale_batch_rec.year
1779 ,p_month => l_resale_batch_rec.month
1780 ,p_report_date => l_resale_batch_rec.report_date
1781 ,p_report_start_date => l_resale_batch_rec.report_start_date
1782 ,p_report_end_date => l_resale_batch_rec.report_end_date
1783 ,p_status_code => 'NEW'
1784 ,p_data_source_code => l_resale_batch_rec.data_source_code
1785 ,p_reference_type => l_resale_batch_rec.reference_type
1786 ,p_reference_number => l_resale_batch_rec.reference_number
1787 ,p_comments => l_resale_batch_rec.comments
1788 ,p_partner_claim_number => l_resale_batch_rec.partner_claim_number
1789 ,p_transaction_purpose_code => l_resale_batch_rec.transaction_purpose_code
1790 ,p_transaction_type_code => l_resale_batch_rec.transaction_type_code
1791 ,p_partner_type => l_resale_batch_rec.partner_type
1792 ,p_partner_id => NULL
1793 ,p_partner_party_id => l_resale_batch_rec.partner_party_id
1794 ,p_partner_cust_account_id => l_resale_batch_rec.partner_cust_account_id
1795 ,p_partner_site_id => l_resale_batch_rec.partner_site_id
1796 ,p_partner_contact_party_id => NULL
1797 ,p_partner_contact_name => l_resale_batch_rec.partner_contact_name
1798 ,p_partner_email => l_resale_batch_rec.partner_email
1799 ,p_partner_phone => l_resale_batch_rec.partner_phone
1800 ,p_partner_fax => l_resale_batch_rec.partner_fax
1801 ,p_header_tolerance_operand => NULL
1802 ,p_header_tolerance_calc_code => NULL
1803 ,p_line_tolerance_operand => NULL
1804 ,p_line_tolerance_calc_code => NULL
1805 ,p_currency_code => l_resale_batch_rec.currency_code
1806 ,p_claimed_amount => l_resale_batch_rec.claimed_amount
1807 ,p_allowed_amount => l_amount
1808 ,p_paid_amount => l_amount
1809 ,p_disputed_amount => l_amount
1810 ,p_accepted_amount => l_amount
1811 ,p_lines_invalid => l_amount
1812 ,p_lines_w_tolerance => l_amount
1813 ,p_lines_disputed => l_amount
1814 ,p_batch_set_id_code => 'WEBADI'
1815 ,p_credit_code => l_resale_batch_rec.credit_code
1816 ,p_credit_advice_date => l_resale_batch_rec.credit_advice_date
1817 ,p_purge_flag => NULL
1818 ,p_attribute_category => p_attribute_category
1819 ,p_attribute1 => p_attribute1
1820 ,p_attribute2 => p_attribute2
1821 ,p_attribute3 => p_attribute3
1822 ,p_attribute4 => p_attribute4
1823 ,p_attribute5 => p_attribute5
1824 ,p_attribute6 => p_attribute6
1825 ,p_attribute7 => p_attribute7
1826 ,p_attribute8 => p_attribute8
1827 ,p_attribute9 => p_attribute9
1828 ,p_attribute10 => p_attribute10
1829 ,p_attribute11 => p_attribute11
1830 ,p_attribute12 => p_attribute12
1831 ,p_attribute13 => p_attribute13
1832 ,p_attribute14 => p_attribute14
1833 ,p_attribute15 => p_attribute15
1834 ,px_org_id => l_org_id -- R12 MOAC Enhancement --l_resale_batch_rec.org_id
1835 ,p_direct_order_flag => l_resale_batch_rec.direct_order_flag -- For Bug#9447673 SSD IDSM
1836 );
1837
1838 EXCEPTION
1839
1840 WHEN OTHERS THEN
1841 l_text := l_text||'Error in Create Batch '||SQLERRM||' \n ';
1842 fnd_message.set_name ('OZF', 'OZF_WADI_CREATE_BATCH_ERROR');
1843 l_error_message := fnd_message.get();
1844 l_code := 'OZF_WADI_CREATE_BATCH_ERROR';
1845 raise_application_error( -20000, l_error_message);
1846 END;
1847
1848 END IF; -- l_batch_count = 0
1849
1850 l_text := l_text||'Creating Line Record'||' \n ';
1851 ozf_utility_pvt.debug_message('Creating Line Record');
1852 -- Bug 5417666 (+)
1853 --SELECT TO_NUMBER(NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99))
1854 --INTO l_int_line_tbl(1).org_id FROM DUAL;
1855 l_int_line_tbl(1).org_id := l_org_id;
1856 -- Bug 5417666 (-)
1857
1858 SELECT ozf_resale_lines_int_all_s.nextval
1859 INTO l_int_line_tbl(1).resale_line_int_id
1860 FROM DUAL;
1861 ozf_utility_pvt.debug_message('resale_line_int_id '||l_int_line_tbl(1).resale_line_int_id);
1862 IF G_RESALE_BATCH_ID = 0 OR l_resale_batch_rec.resale_batch_id IS NULL THEN
1863 SELECT resale_batch_id
1864 INTO l_int_line_tbl(1).resale_batch_id
1865 FROM ozf_resale_batches
1866 WHERE batch_number = p_batch_number;
1867 ELSE
1868 l_int_line_tbl(1).resale_batch_id := G_RESALE_BATCH_ID;
1869
1870 END IF;
1871 ozf_utility_pvt.debug_message('resale_batch_id '||l_int_line_tbl(1).resale_batch_id );
1872
1873 l_text := l_text||'Resale Batch ID '||l_int_line_tbl(1).resale_batch_id||' \n ';
1874
1875 insert_resale_int_line (
1876 p_api_version_number => 1.0,
1877 p_init_msg_list => FND_API.G_FALSE,
1878 p_Commit => FND_API.G_FALSE,
1879 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1880 p_int_line_tbl => l_int_line_tbl,
1881 x_return_status => x_return_status,
1882 x_msg_count => l_msg_count,
1883 x_msg_data => l_msg_data );
1884
1885 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1886 l_text := l_text||'Error in Create Line '||SQLERRM||' \n ';
1887 fnd_message.set_name ('OZF', 'OZF_WADI_CREATE_LINE_ERROR');
1888 l_error_message := fnd_message.get();
1889 l_code := 'OZF_WADI_CREATE_LINE_ERROR';
1890 raise_application_error( -20000, l_error_message);
1891 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1893 END IF;
1894
1895 ELSIF l_status_code IN ('OPEN','NEW','PROCESSED','DISPUTED') AND p_resale_line_int_id IS NOT NULL THEN
1896
1897 ozf_utility_pvt.debug_message('P_BATCH_NUMBER '||P_BATCH_NUMBER);
1898 ozf_utility_pvt.debug_message('P_RESALE_LINE_INT_ID '||P_RESALE_LINE_INT_ID);
1899
1900 OPEN c_get_update_record (P_BATCH_NUMBER, P_RESALE_LINE_INT_ID);
1901 LOOP
1902
1903 l_resale_line_int_id_tbl.extend;
1904 l_object_version_no_tbl.extend;
1905
1906 j := l_resale_line_int_id_tbl.count;
1907
1908 FETCH c_get_update_record
1909 INTO l_resale_batch_rec.resale_batch_id,
1910 l_resale_batch_rec.object_version_number,
1911 l_object_version_no_tbl(1);
1912 EXIT WHEN c_get_update_record%NOTFOUND;
1913
1914 END LOOP;
1915 CLOSE c_get_update_record;
1916
1917
1918
1919 l_int_line_tbl(1).resale_line_int_id := p_resale_line_int_id;
1920 l_int_line_tbl(1).resale_batch_id := l_resale_batch_rec.resale_batch_id;
1921 l_int_line_tbl(1).object_version_number := l_object_version_no_tbl(1);
1922 ozf_utility_pvt.debug_message('resale_line_int_id '||l_int_line_tbl(1).resale_line_int_id);
1923 ozf_utility_pvt.debug_message('resale_batch_id '||l_int_line_tbl(1).resale_batch_id);
1924
1925 l_text := l_text||'resale_line_int_id '||l_int_line_tbl(1).resale_line_int_id;
1926
1927 -- l_int_line_tbl(1).status_code := l_line_status;
1928 -- l_resale_batch_rec.status_code := l_status_code;
1929
1930 -- ==============================================================================
1931 -- WebADI sends batch and line data together to this API and it is called
1932 -- as many times as the number of lines exists, but batch has to be updated
1933 -- the first time this API call is made.
1934 -- ==============================================================================
1935 l_text := l_text||'Updating batch record ';
1936
1937 IF G_RESALE_BATCH_NUMBER = 0 OR l_status_code <> 'NEW' THEN
1938
1939 Update_Resale_Batch
1940 (
1941 p_api_version_number => 1.0,
1942 p_init_msg_list => FND_API.G_FALSE,
1943 P_Commit => FND_API.G_FALSE,
1944 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1945 p_int_batch_rec => l_resale_batch_rec,
1946 x_return_status => x_return_status,
1947 x_msg_count => l_msg_count,
1948 x_msg_data => l_msg_data
1949 );
1950
1951 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1952 l_text := l_text||'Error in Update Batch '||SQLERRM||' \n ';
1953 fnd_message.set_name ('OZF', 'OZF_WADI_UPDATE_BATCH_ERROR');
1954 l_error_message := fnd_message.get();
1955 l_code := 'OZF_WADI_UPDATE_BATCH_ERROR';
1956 raise_application_error( -20000, l_error_message);
1957 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1959 END IF;
1960
1961 ozf_utility_pvt.debug_message('x_return_status '||x_return_status);
1962 END IF;
1963
1964 l_text := l_text||'Updating line record ';
1965 Update_Resale_Int_Line
1966 (
1967 p_api_version_number => 1.0,
1968 p_init_msg_list => FND_API.G_FALSE,
1969 P_Commit => FND_API.G_FALSE,
1970 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1971 p_int_line_tbl => l_int_line_tbl,
1972 x_return_status => x_return_status,
1973 x_msg_count => l_msg_count,
1974 x_msg_data => l_msg_data
1975 );
1976
1977 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1978 l_text := l_text||'Error in Update Line '||SQLERRM||' \n ';
1979 fnd_message.set_name ('OZF', 'OZF_WADI_UPDATE_LINE_ERROR');
1980 l_error_message := fnd_message.get();
1981 l_code := 'OZF_WADI_UPDATE_LINE_ERROR';
1982 raise_application_error( -20000, l_error_message);
1983 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1985 END IF;
1986 ozf_utility_pvt.debug_message('x_return_status '||x_return_status);
1987 l_text := l_text||'line record updated';
1988
1989 OZF_UTILITY_PVT.debug_message('Interface Lines Table is updated successfully ' ||x_return_status);
1990
1991 G_RESALE_BATCH_NUMBER := P_BATCH_NUMBER;
1992
1993 END IF;
1994
1995 OZF_UTILITY_PVT.debug_message('length of text '||length(l_text));
1996 OZF_UTILITY_PVT.debug_message('length of text '||length(substr(l_text,1001,1000)));
1997 UTL_FILE.PUTF(l_file,l_text);
1998 UTL_FILE.FCLOSE(l_file);
1999
2000 EXCEPTION
2001 WHEN ozf_webadi_error THEN
2002 x_return_status := FND_API.G_RET_STS_ERROR;
2003 raise_application_error( -20000, l_error_message);
2004 WHEN UTL_FILE.INVALID_PATH THEN
2005 RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
2006 WHEN UTL_FILE.INVALID_MODE THEN
2007 RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
2008 WHEN UTL_FILE.INVALID_OPERATION then
2009 RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
2010 WHEN UTL_FILE.INVALID_FILEHANDLE then
2011 RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
2012 WHEN UTL_FILE.WRITE_ERROR then
2013 RAISE_APPLICATION_ERROR(-20104,'Write Error');
2014 WHEN UTL_FILE.READ_ERROR then
2015 RAISE_APPLICATION_ERROR(-20105,'Read Error');
2016 WHEN UTL_FILE.INTERNAL_ERROR then
2017 RAISE_APPLICATION_ERROR(-20106,'Internal Error');
2018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2019 UTL_FILE.FCLOSE(l_file);
2020 -- Fix for Bug#10624065(+)
2021 /*
2022 IF length( SQLERRM) > 30 THEN
2023 ozf_utility_pvt.debug_message(substr(SQLERRM,12,30));
2024 fnd_message.set_name ('OZF', substr(SQLERRM,12,30));
2025 ELSE
2026 */
2027 fnd_message.set_name ('OZF', NVL(l_code,l_error_message));
2028 --END IF;
2029 -- Fix for Bug#10624065(-)
2030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2031 WHEN OTHERS THEN
2032 UTL_FILE.FCLOSE(l_file);
2033 -- Fix for Bug#10624065(+)
2034 /*
2035 IF length( SQLERRM) > 30 THEN
2036 ozf_utility_pvt.debug_message(substr(SQLERRM,12,30));
2037 fnd_message.set_name ('OZF', substr(SQLERRM,12,30));
2038 ELSE
2039 */
2040 fnd_message.set_name ('OZF', NVL(l_code,l_error_message));
2041 --END IF;
2042 -- Fix for Bug#10624065(-)
2043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2044
2045 END RESALE_WEBADI;
2046
2047 PROCEDURE Update_Resale_Batch (
2048 p_api_version_number IN NUMBER,
2049 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2050 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2051 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2052 p_int_batch_rec IN ozf_resale_batches_all%rowtype,
2053 x_return_status OUT NOCOPY VARCHAR2,
2054 x_msg_count OUT NOCOPY NUMBER,
2055 x_msg_data OUT NOCOPY VARCHAR2
2056 )
2057 IS
2058
2059 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resale_Batch';
2060 l_api_version_number CONSTANT NUMBER := 1.0;
2061
2062 l_resale_batch_rec ozf_resale_batches_all%rowtype;
2063
2064 CURSOR get_resale_batch (pc_batch_number VARCHAR2)
2065 IS
2066 SELECT last_update_date,
2067 last_updated_by,
2068 last_update_login,
2069 object_version_number,
2070 NVL(p_int_batch_rec.batch_type,batch_type),
2071 NVL(p_int_batch_rec.batch_count,batch_count),
2072 NVL(p_int_batch_rec.year,year),
2073 NVL(p_int_batch_rec.month,month),
2074 NVL(p_int_batch_rec.report_date,report_date),
2075 NVL(p_int_batch_rec.report_start_date,report_start_date),
2076 NVL(p_int_batch_rec.report_end_date,report_end_date),
2077 NVL(p_int_batch_rec.data_source_code,data_source_code),
2078 NVL(p_int_batch_rec.reference_type,reference_type),
2079 NVL(p_int_batch_rec.reference_number,reference_number),
2080 NVL(p_int_batch_rec.comments,comments),
2081 NVL(p_int_batch_rec.partner_claim_number,partner_claim_number),
2082 NVL(p_int_batch_rec.transaction_purpose_code,transaction_purpose_code),
2083 NVL(p_int_batch_rec.transaction_type_code,transaction_type_code),
2084 NVL(p_int_batch_rec.partner_type,partner_type),
2085 NVL(p_int_batch_rec.partner_party_id,partner_party_id),
2086 DECODE(p_int_batch_rec.partner_cust_account_id,
2087 NULL,partner_cust_account_id,
2088 DECODE(p_int_batch_rec.partner_cust_account_id,partner_cust_account_id,partner_cust_account_id,p_int_batch_rec.partner_cust_account_id )),
2089 DECODE(p_int_batch_rec.partner_site_id,
2090 NULL,partner_site_id,
2091 DECODE(p_int_batch_rec.partner_site_id,partner_site_id,partner_site_id,p_int_batch_rec.partner_site_id )),
2092 DECODE(p_int_batch_rec.partner_contact_name,
2093 NULL,partner_contact_party_id,
2094 DECODE(p_int_batch_rec.partner_contact_name,partner_contact_name,partner_contact_party_id,NULL )),
2095 NVL(p_int_batch_rec.partner_contact_name,partner_contact_name),
2096 NVL(p_int_batch_rec.partner_email,partner_email ),
2097 NVL(p_int_batch_rec.partner_phone,partner_phone),
2098 NVL(p_int_batch_rec.partner_fax,partner_fax),
2099 NVL(p_int_batch_rec.currency_code,currency_code),
2100 NVL(p_int_batch_rec.claimed_amount,claimed_amount),
2101 NVL(p_int_batch_rec.credit_code,credit_code),
2102 NVL(p_int_batch_rec.attribute_category,attribute_category),
2103 NVL(p_int_batch_rec.attribute1,attribute1),
2104 NVL(p_int_batch_rec.attribute2,attribute2),
2105 NVL(p_int_batch_rec.attribute3,attribute3),
2106 NVL(p_int_batch_rec.attribute4,attribute4),
2107 NVL(p_int_batch_rec.attribute5,attribute5),
2108 NVL(p_int_batch_rec.attribute6,attribute6),
2109 NVL(p_int_batch_rec.attribute7,attribute7),
2110 NVL(p_int_batch_rec.attribute8,attribute8),
2111 NVL(p_int_batch_rec.attribute9,attribute9),
2112 NVL(p_int_batch_rec.attribute10,attribute10),
2113 NVL(p_int_batch_rec.attribute11,attribute11),
2114 NVL(p_int_batch_rec.attribute12,attribute12),
2115 NVL(p_int_batch_rec.attribute13,attribute13),
2116 NVL(p_int_batch_rec.attribute14,attribute14),
2117 NVL(p_int_batch_rec.attribute15,attribute15),
2118 NVL(p_int_batch_rec.batch_set_id_code,batch_set_id_code ),
2119 NVL(p_int_batch_rec.credit_advice_date,credit_advice_date),
2120 NVL(p_int_batch_rec.org_id,org_id),
2121 NVL(p_int_batch_rec.header_tolerance_operand,header_tolerance_operand),
2122 NVL(p_int_batch_rec.header_tolerance_calc_code,header_tolerance_calc_code),
2123 NVL(p_int_batch_rec.line_tolerance_operand,line_tolerance_operand),
2124 NVL(p_int_batch_rec.line_tolerance_calc_code,line_tolerance_calc_code),
2125 NVL(p_int_batch_rec.allowed_amount,allowed_amount),
2126 NVL(p_int_batch_rec.accepted_amount,accepted_amount),
2127 NVL(p_int_batch_rec.paid_amount,paid_amount),
2128 NVL(p_int_batch_rec.disputed_amount,disputed_amount),
2129 NVL(p_int_batch_rec.lines_invalid,lines_invalid),
2130 NVL(p_int_batch_rec.lines_w_tolerance,lines_w_tolerance),
2131 NVL(p_int_batch_rec.lines_disputed,lines_disputed),
2132 NVL(p_int_batch_rec.purge_flag,purge_flag),
2133 NVL(p_int_batch_rec.direct_order_flag,direct_order_flag) -- For Bug#9447673 SSD IDSM
2134 FROM ozf_resale_batches
2135 WHERE batch_number = pc_batch_number;
2136
2137 BEGIN
2138 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
2139 x_return_status := FND_API.G_RET_STS_SUCCESS;
2140
2141 l_resale_batch_rec.resale_batch_id := p_int_batch_rec.resale_batch_id;
2142 l_resale_batch_rec.batch_number := p_int_batch_rec.batch_number;
2143 l_resale_batch_rec.status_code := 'OPEN';
2144
2145
2146 OPEN get_resale_batch ( p_int_batch_rec.batch_number);
2147 FETCH get_resale_batch
2148 INTO l_resale_batch_rec.last_update_date,
2149 l_resale_batch_rec.last_updated_by,
2150 l_resale_batch_rec.last_update_login,
2151 l_resale_batch_rec.object_version_number,
2152 l_resale_batch_rec.batch_type,
2153 l_resale_batch_rec.batch_count,
2154 l_resale_batch_rec.year,
2155 l_resale_batch_rec.month,
2156 l_resale_batch_rec.report_date,
2157 l_resale_batch_rec.report_start_date,
2158 l_resale_batch_rec.report_end_date,
2159 l_resale_batch_rec.data_source_code,
2160 l_resale_batch_rec.reference_type,
2161 l_resale_batch_rec.reference_number,
2162 l_resale_batch_rec.comments,
2163 l_resale_batch_rec.partner_claim_number,
2164 l_resale_batch_rec.transaction_purpose_code,
2165 l_resale_batch_rec.transaction_type_code,
2166 l_resale_batch_rec.partner_type,
2167 l_resale_batch_rec.partner_party_id,
2168 l_resale_batch_rec.partner_cust_account_id,
2169 l_resale_batch_rec.partner_site_id,
2170 l_resale_batch_rec.partner_contact_party_id,
2171 l_resale_batch_rec.partner_contact_name,
2172 l_resale_batch_rec.partner_email,
2173 l_resale_batch_rec.partner_phone,
2174 l_resale_batch_rec.partner_fax,
2175 l_resale_batch_rec.currency_code,
2176 l_resale_batch_rec.claimed_amount,
2177 l_resale_batch_rec.credit_code,
2178 l_resale_batch_rec.attribute_category,
2179 l_resale_batch_rec.attribute1,
2180 l_resale_batch_rec.attribute2,
2181 l_resale_batch_rec.attribute3,
2182 l_resale_batch_rec.attribute4,
2183 l_resale_batch_rec.attribute5,
2184 l_resale_batch_rec.attribute6,
2185 l_resale_batch_rec.attribute7,
2186 l_resale_batch_rec.attribute8,
2187 l_resale_batch_rec.attribute9,
2188 l_resale_batch_rec.attribute10,
2189 l_resale_batch_rec.attribute11,
2190 l_resale_batch_rec.attribute12,
2191 l_resale_batch_rec.attribute13,
2192 l_resale_batch_rec.attribute14,
2193 l_resale_batch_rec.attribute15,
2194 l_resale_batch_rec.batch_set_id_code,
2195 l_resale_batch_rec.credit_advice_date,
2196 l_resale_batch_rec.org_id,
2197 l_resale_batch_rec.header_tolerance_operand,
2198 l_resale_batch_rec.header_tolerance_calc_code,
2199 l_resale_batch_rec.line_tolerance_operand,
2200 l_resale_batch_rec.line_tolerance_calc_code,
2201 l_resale_batch_rec.allowed_amount,
2202 l_resale_batch_rec.accepted_amount,
2203 l_resale_batch_rec.paid_amount,
2204 l_resale_batch_rec.disputed_amount,
2205 l_resale_batch_rec.lines_invalid,
2206 l_resale_batch_rec.lines_w_tolerance,
2207 l_resale_batch_rec.lines_disputed,
2208 l_resale_batch_rec.purge_flag,
2209 l_resale_batch_rec.direct_order_flag; -- For Bug#9447673 SSD IDSM
2210 CLOSE get_resale_batch;
2211
2212 ozf_pre_process_pvt.update_interface_batch
2213 (
2214 p_api_version_number => 1.0,
2215 p_init_msg_list => FND_API.G_FALSE,
2216 P_Commit => FND_API.G_FALSE,
2217 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2218 p_int_batch_rec => l_resale_batch_rec,
2219 x_return_status => x_return_status,
2220 x_msg_count => x_msg_count,
2221 x_msg_data => x_msg_data
2222 );
2223
2224 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2225 RAISE FND_API.G_EXC_ERROR;
2226 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2228 END IF;
2229 -- Standard call to get message count and if count is 1, get message info.
2230 FND_MSG_PUB.Count_And_Get
2231 (p_count => x_msg_count,
2232 p_data => x_msg_data
2233 );
2234
2235 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End');
2236 EXCEPTION
2237
2238 WHEN FND_API.G_EXC_ERROR THEN
2239 x_return_status := FND_API.G_RET_STS_ERROR;
2240 -- Standard call to get message count and if count=1, get the message
2241 FND_MSG_PUB.Count_And_Get (
2242 p_encoded => FND_API.G_FALSE,
2243 p_count => x_msg_count,
2244 p_data => x_msg_data
2245 );
2246 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2248 -- Standard call to get message count and if count=1, get the message
2249 FND_MSG_PUB.Count_And_Get (
2250 p_encoded => FND_API.G_FALSE,
2251 p_count => x_msg_count,
2252 p_data => x_msg_data
2253 );
2254 WHEN OTHERS THEN
2255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2256
2257 -- Standard call to get message count and if count=1, get the message
2258 FND_MSG_PUB.Count_And_Get (
2259 p_encoded => FND_API.G_FALSE,
2260 p_count => x_msg_count,
2261 p_data => x_msg_data
2262 );
2263 END Update_Resale_Batch;
2264
2265
2266
2267
2268 PROCEDURE Update_Resale_Int_Line (
2269 p_api_version_number IN NUMBER,
2270 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2271 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2272 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2273 p_int_line_tbl IN ozf_pre_process_pvt.resale_line_int_tbl_type,
2274 x_return_status OUT NOCOPY VARCHAR2,
2275 x_msg_count OUT NOCOPY NUMBER,
2276 x_msg_data OUT NOCOPY VARCHAR2
2277 )
2278 IS
2279 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resale_Int_Line';
2280 l_api_version_number CONSTANT NUMBER := 1.0;
2281
2282 l_int_line_tbl ozf_pre_process_pvt.resale_line_int_tbl_type := ozf_pre_process_pvt.resale_line_int_tbl_type();
2283
2284 CURSOR get_int_line ( pc_line_id NUMBER
2285 , pc_batch_id NUMBER)
2286 IS
2287 SELECT resale_line_int_id,
2288 object_version_number,
2289 last_update_date,
2290 last_updated_by,
2291 request_id,
2292 created_from,
2293 last_update_login,
2294 nvl(p_int_line_tbl(1).program_application_id,program_application_id),
2295 nvl(p_int_line_tbl(1).program_update_date,program_update_date),
2296 nvl(p_int_line_tbl(1).program_id,program_id),
2297 'OPEN',
2298 nvl(p_int_line_tbl(1).resale_transfer_type,resale_transfer_type),
2299 nvl(p_int_line_tbl(1).product_transfer_movement_type,product_transfer_movement_type ),
2300 nvl(p_int_line_tbl(1).product_transfer_date,product_transfer_date),
2301 nvl(p_int_line_tbl(1).tracing_flag,tracing_flag),
2302 DECODE(p_int_line_tbl(1).ship_from_party_name
2303 ,NULL,ship_from_cust_account_id
2304 ,DECODE(p_int_line_tbl(1).ship_from_party_name,ship_from_party_name,ship_from_cust_account_id, NULL)),
2305 DECODE(p_int_line_tbl(1).ship_from_location
2306 ,NULL,DECODE(p_int_line_tbl(1).ship_from_address
2307 ,NULL,ship_from_site_id
2308 ,DECODE(p_int_line_tbl(1).ship_from_address, ship_from_address,ship_from_site_id,NULL))
2309 ,DECODE(p_int_line_tbl(1).ship_from_location,ship_from_location,ship_from_site_id,NULL )),
2310 DECODE(p_int_line_tbl(1).ship_from_party_name,
2311 ship_from_party_name,ship_from_party_name,
2312 p_int_line_tbl(1).ship_from_party_name),
2313 nvl(p_int_line_tbl(1).ship_from_location,ship_from_location),
2314 nvl(p_int_line_tbl(1).ship_from_address,ship_from_address),
2315 nvl(p_int_line_tbl(1).ship_from_city,ship_from_city),
2316 nvl(p_int_line_tbl(1).ship_from_state,ship_from_state),
2317 nvl(p_int_line_tbl(1).ship_from_postal_code,ship_from_postal_code),
2318 nvl(p_int_line_tbl(1).ship_from_country,ship_from_country),
2319 DECODE(p_int_line_tbl(1).ship_from_contact_name
2320 ,NULL,ship_from_contact_party_id
2321 ,decode(p_int_line_tbl(1).ship_from_contact_name,ship_from_contact_name,ship_from_contact_party_id, NULL)),
2322 nvl(p_int_line_tbl(1).ship_from_contact_name,ship_from_contact_name),
2323 nvl(p_int_line_tbl(1).ship_from_email,ship_from_email),
2324 nvl(p_int_line_tbl(1).ship_from_phone,ship_from_phone),
2325 nvl(p_int_line_tbl(1).ship_from_fax,ship_from_fax),
2326 DECODE(p_int_line_tbl(1).sold_from_party_name
2327 ,NULL,DECODE(p_int_line_tbl(1).ship_from_location, ship_from_location,sold_from_cust_account_id,
2328 NULL)
2329 ,decode(p_int_line_tbl(1).sold_from_party_name,sold_from_party_name,sold_from_cust_account_id, NULL)),
2330 DECODE(p_int_line_tbl(1).sold_from_location
2331 ,NULL,DECODE(p_int_line_tbl(1).sold_from_address
2332 ,NULL,DECODE(p_int_line_tbl(1).ship_from_location, ship_from_location,sold_from_site_id,
2333 NULL)
2334 ,DECODE(p_int_line_tbl(1).sold_from_address, sold_from_address,sold_from_site_id,NULL))
2335 ,DECODE(p_int_line_tbl(1).sold_from_location,sold_from_location,sold_from_site_id,NULL )),
2336 DECODE(p_int_line_tbl(1).sold_from_party_name,
2337 sold_from_party_name,sold_from_party_name,
2338 p_int_line_tbl(1).sold_from_party_name),
2339 nvl(p_int_line_tbl(1).sold_from_location,sold_from_location),
2340 nvl(p_int_line_tbl(1).sold_from_address,sold_from_address),
2341 nvl(p_int_line_tbl(1).sold_from_city,sold_from_city),
2342 nvl(p_int_line_tbl(1).sold_from_state,sold_from_state),
2343 nvl(p_int_line_tbl(1).sold_from_postal_code,sold_from_postal_code),
2344 nvl(p_int_line_tbl(1).sold_from_country,sold_from_country),
2345 DECODE(p_int_line_tbl(1).sold_from_contact_name
2346 ,NULL,sold_from_contact_party_id
2347 ,decode(p_int_line_tbl(1).sold_from_contact_name,sold_from_contact_name,sold_from_contact_party_id, NULL)),
2348 nvl(p_int_line_tbl(1).sold_from_contact_name,sold_from_contact_name),
2349 nvl(p_int_line_tbl(1).sold_from_email,sold_from_email),
2350 nvl(p_int_line_tbl(1).sold_from_phone,sold_from_phone),
2351 nvl(p_int_line_tbl(1).sold_from_fax,sold_from_fax),
2352 DECODE(p_int_line_tbl(1).bill_to_party_name
2353 ,NULL,DECODE(p_int_line_tbl(1).ship_to_location, ship_to_location,bill_to_cust_account_id,
2354 NULL)
2355 ,decode(p_int_line_tbl(1).bill_to_party_name,bill_to_party_name,bill_to_cust_account_id, NULL)),
2356 DECODE(p_int_line_tbl(1).bill_to_location
2357 ,NULL,DECODE(p_int_line_tbl(1).bill_to_address
2358 ,NULL,DECODE(p_int_line_tbl(1).ship_to_location, ship_to_location,bill_to_site_use_id,
2359 NULL)
2360 ,DECODE(p_int_line_tbl(1).bill_to_address, bill_to_address,bill_to_site_use_id,NULL))
2361 ,DECODE(p_int_line_tbl(1).bill_to_location,bill_to_location,bill_to_site_use_id,NULL )),
2362 DECODE(p_int_line_tbl(1).bill_to_party_name
2363 ,NULL,DECODE(p_int_line_tbl(1).ship_to_location, ship_to_location,bill_to_party_id,
2364 NULL)
2365 ,decode(p_int_line_tbl(1).bill_to_party_name,bill_to_party_name,bill_to_party_id, NULL)),
2366 DECODE(p_int_line_tbl(1).bill_to_location
2367 ,NULL,DECODE(p_int_line_tbl(1).bill_to_address
2368 ,NULL,DECODE(p_int_line_tbl(1).ship_to_location, ship_to_location,bill_to_party_site_id,
2369 NULL)
2370 ,DECODE(p_int_line_tbl(1).bill_to_address, bill_to_address,bill_to_party_site_id,NULL))
2371 ,DECODE(p_int_line_tbl(1).bill_to_location,bill_to_location,bill_to_party_site_id,NULL )),
2372 DECODE(p_int_line_tbl(1).bill_to_party_name, NULL,
2373 bill_to_party_name,bill_to_party_name,
2374 p_int_line_tbl(1).bill_to_party_name),
2375 nvl(p_int_line_tbl(1).bill_to_duns_number,bill_to_duns_number),
2376 nvl(p_int_line_tbl(1).bill_to_location,bill_to_location),
2377 nvl(p_int_line_tbl(1).bill_to_address,bill_to_address),
2378 nvl(p_int_line_tbl(1).bill_to_city,bill_to_city),
2379 nvl(p_int_line_tbl(1).bill_to_state,bill_to_state),
2380 nvl(p_int_line_tbl(1).bill_to_postal_code,bill_to_postal_code),
2381 nvl(p_int_line_tbl(1).bill_to_country,bill_to_country),
2382 DECODE(p_int_line_tbl(1).bill_to_contact_name
2383 ,NULL,bill_to_contact_party_id
2384 ,decode(p_int_line_tbl(1).bill_to_contact_name,bill_to_contact_name,bill_to_contact_party_id, NULL)),
2385 nvl(p_int_line_tbl(1).bill_to_contact_name,bill_to_contact_name),
2386 nvl(p_int_line_tbl(1).bill_to_email,bill_to_email),
2387 nvl(p_int_line_tbl(1).bill_to_phone,bill_to_phone),
2388 nvl(p_int_line_tbl(1).bill_to_fax,bill_to_fax),
2389 DECODE(p_int_line_tbl(1).ship_to_party_name
2390 ,NULL,DECODE(p_int_line_tbl(1).ship_to_location, ship_to_location ,ship_to_cust_account_id,NULL)
2391 ,decode(p_int_line_tbl(1).ship_to_party_name,ship_to_party_name,ship_to_cust_account_id, NULL)),
2392 DECODE(p_int_line_tbl(1).ship_to_location
2393 ,NULL,DECODE(p_int_line_tbl(1).ship_to_address
2394 ,NULL,ship_to_site_use_id
2395 ,DECODE(p_int_line_tbl(1).ship_to_address, ship_to_address,ship_to_site_use_id,NULL))
2396 ,DECODE(p_int_line_tbl(1).ship_to_location,ship_to_location,ship_to_site_use_id,NULL )),
2397 DECODE(p_int_line_tbl(1).ship_to_party_name
2398 ,NULL,DECODE(p_int_line_tbl(1).ship_to_location, ship_to_location ,ship_to_cust_account_id,NULL)
2399 ,decode(p_int_line_tbl(1).ship_to_party_name,ship_to_party_name,ship_to_party_id, NULL)),
2400 DECODE(p_int_line_tbl(1).ship_to_location
2401 ,NULL,DECODE(p_int_line_tbl(1).ship_to_address
2402 ,NULL,ship_to_party_site_id
2403 ,DECODE(p_int_line_tbl(1).ship_to_address, ship_to_address,ship_to_party_site_id,NULL))
2404 ,DECODE(p_int_line_tbl(1).ship_to_location,ship_to_location,ship_to_party_site_id,NULL )),
2405 DECODE(p_int_line_tbl(1).ship_to_party_name,
2406 ship_to_party_name,ship_to_party_name,
2407 p_int_line_tbl(1).ship_to_party_name),
2408 nvl(p_int_line_tbl(1).ship_to_duns_number,ship_to_duns_number),
2409 nvl(p_int_line_tbl(1).ship_to_location,ship_to_location),
2410 nvl(p_int_line_tbl(1).ship_to_address,ship_to_address),
2411 nvl(p_int_line_tbl(1).ship_to_city,ship_to_city),
2412 nvl(p_int_line_tbl(1).ship_to_state,ship_to_state),
2413 nvl(p_int_line_tbl(1).ship_to_postal_code,ship_to_postal_code),
2414 nvl(p_int_line_tbl(1).ship_to_country,ship_to_country),
2415 DECODE(p_int_line_tbl(1).ship_to_contact_name
2416 ,NULL,ship_to_contact_party_id
2417 ,decode(p_int_line_tbl(1).ship_to_contact_name,ship_to_contact_name,ship_to_contact_party_id, NULL)),
2418 nvl(p_int_line_tbl(1).ship_to_contact_name,ship_to_contact_name),
2419 nvl(p_int_line_tbl(1).ship_to_email,ship_to_email),
2420 nvl(p_int_line_tbl(1).ship_to_phone,ship_to_phone),
2421 nvl(p_int_line_tbl(1).ship_to_fax,ship_to_fax),
2422 DECODE(p_int_line_tbl(1).end_cust_party_name
2423 ,NULL,end_cust_party_id
2424 ,decode(p_int_line_tbl(1).end_cust_party_name,end_cust_party_name,end_cust_party_id, NULL)),
2425 DECODE(p_int_line_tbl(1).end_cust_location
2426 ,NULL,DECODE(p_int_line_tbl(1).end_cust_address
2427 ,NULL,end_cust_site_use_id
2428 ,DECODE(p_int_line_tbl(1).end_cust_address, end_cust_address,end_cust_site_use_id,NULL))
2429 ,DECODE(p_int_line_tbl(1).end_cust_location,end_cust_location,end_cust_site_use_id,NULL )),
2430 nvl(p_int_line_tbl(1).end_cust_site_use_code,end_cust_site_use_code),
2431 DECODE(p_int_line_tbl(1).end_cust_location
2432 ,NULL,DECODE(p_int_line_tbl(1).end_cust_address
2433 ,NULL,end_cust_party_site_id
2434 ,DECODE(p_int_line_tbl(1).end_cust_address, end_cust_address,end_cust_party_site_id,NULL))
2435 ,DECODE(p_int_line_tbl(1).end_cust_location,end_cust_location,end_cust_party_site_id,NULL )),
2436 nvl(p_int_line_tbl(1).end_cust_party_name,end_cust_party_name),
2437 nvl(p_int_line_tbl(1).end_cust_location,end_cust_location),
2438 nvl(p_int_line_tbl(1).end_cust_address,end_cust_address),
2439 nvl(p_int_line_tbl(1).end_cust_city,end_cust_city),
2440 nvl(p_int_line_tbl(1).end_cust_state,end_cust_state),
2441 nvl(p_int_line_tbl(1).end_cust_postal_code,end_cust_postal_code),
2442 nvl(p_int_line_tbl(1).end_cust_country,end_cust_country),
2443 DECODE(p_int_line_tbl(1).end_cust_contact_name
2444 ,NULL,end_cust_contact_party_id
2445 ,decode(p_int_line_tbl(1).end_cust_contact_name,end_cust_contact_name,end_cust_contact_party_id, NULL)),
2446 nvl(p_int_line_tbl(1).end_cust_contact_name,end_cust_contact_name),
2447 nvl(p_int_line_tbl(1).end_cust_email,end_cust_email),
2448 nvl(p_int_line_tbl(1).end_cust_phone,end_cust_phone),
2449 nvl(p_int_line_tbl(1).end_cust_fax,end_cust_fax),
2450 nvl(p_int_line_tbl(1).direct_customer_flag,direct_customer_flag ),
2451 DECODE(p_int_line_tbl(1).order_type,NULL,order_type_id,NULL),
2452 nvl(p_int_line_tbl(1).order_type,order_type),
2453 nvl(p_int_line_tbl(1).order_category,order_category),
2454 DECODE(p_int_line_tbl(1).orig_system_agreement_type,NULL
2455 ,decode(p_int_line_tbl(1).agreement_type,NULL,agreement_type, p_int_line_tbl(1).agreement_type)
2456 ,decode(p_int_line_tbl(1).orig_system_agreement_type,orig_system_agreement_type, agreement_type, NULL)),
2457 DECODE(p_int_line_tbl(1).orig_system_agreement_name,NULL
2458 ,decode(p_int_line_tbl(1).agreement_name,NULL,agreement_id, NULL)
2459 ,decode(p_int_line_tbl(1).orig_system_agreement_name,orig_system_agreement_name, agreement_id, NULL)),
2460 DECODE(p_int_line_tbl(1).orig_system_agreement_name,NULL
2461 ,decode(p_int_line_tbl(1).agreement_name,NULL,agreement_name, p_int_line_tbl(1).agreement_name)
2462 ,decode(p_int_line_tbl(1).orig_system_agreement_name,orig_system_agreement_name, agreement_name, NULL)),
2463 DECODE(p_int_line_tbl(1).orig_system_agreement_price,NULL
2464 ,decode(p_int_line_tbl(1).agreement_price,NULL,agreement_price, p_int_line_tbl(1).agreement_price)
2465 ,decode(p_int_line_tbl(1).orig_system_agreement_price,orig_system_agreement_price, agreement_price, NULL)),
2466 DECODE(p_int_line_tbl(1).orig_system_agreement_uom,NULL
2467 ,decode(p_int_line_tbl(1).agreement_uom_code,NULL,agreement_uom_code, p_int_line_tbl(1).agreement_uom_code)
2468 ,decode(p_int_line_tbl(1).orig_system_agreement_uom,orig_system_agreement_uom, agreement_uom_code, NULL)),
2469 DECODE(p_int_line_tbl(1).orig_system_agreement_name,NULL
2470 ,decode(p_int_line_tbl(1).agreement_name,NULL,corrected_agreement_id, NULL)
2471 ,decode(p_int_line_tbl(1).orig_system_agreement_name,orig_system_agreement_name, corrected_agreement_id, NULL)),
2472 DECODE(p_int_line_tbl(1).orig_system_agreement_name,NULL
2473 ,decode(p_int_line_tbl(1).agreement_name,NULL,corrected_agreement_name, NULL)
2474 ,decode(p_int_line_tbl(1).orig_system_agreement_name,orig_system_agreement_name, corrected_agreement_name, NULL)),
2475 DECODE(p_int_line_tbl(1).price_list_name,NULL,price_list_id,NULL),
2476 nvl(p_int_line_tbl(1).price_list_name,price_list_name),
2477 nvl(p_int_line_tbl(1).orig_system_reference,orig_system_reference),
2478 nvl(p_int_line_tbl(1).orig_system_line_reference,orig_system_line_reference),
2479 nvl(p_int_line_tbl(1).orig_system_currency_code,orig_system_currency_code),
2480 nvl(p_int_line_tbl(1).orig_system_selling_price,orig_system_selling_price),
2481 nvl(p_int_line_tbl(1).orig_system_quantity,orig_system_quantity),
2482 nvl(p_int_line_tbl(1).orig_system_uom,orig_system_uom),
2483 nvl(p_int_line_tbl(1).orig_system_purchase_uom,orig_system_purchase_uom),
2484 nvl(p_int_line_tbl(1).orig_system_purchase_curr,orig_system_purchase_curr),
2485 nvl(p_int_line_tbl(1).orig_system_purchase_price,orig_system_purchase_price),
2486 nvl(p_int_line_tbl(1).orig_system_purchase_quantity,orig_system_purchase_quantity),
2487 nvl(p_int_line_tbl(1).orig_system_agreement_uom,orig_system_agreement_uom),
2488 nvl(p_int_line_tbl(1).orig_system_agreement_name,orig_system_agreement_name),
2489 nvl(p_int_line_tbl(1).orig_system_agreement_type,orig_system_agreement_type),
2490 nvl(p_int_line_tbl(1).orig_system_agreement_status,orig_system_agreement_status),
2491 nvl(p_int_line_tbl(1).orig_system_agreement_curr,orig_system_agreement_curr),
2492 nvl(p_int_line_tbl(1).orig_system_agreement_price,orig_system_agreement_price),
2493 nvl(p_int_line_tbl(1).orig_system_agreement_quantity,orig_system_agreement_quantity),
2494 nvl(p_int_line_tbl(1).orig_system_item_number,orig_system_item_number),
2495 nvl(p_int_line_tbl(1).currency_code,currency_code),
2496 nvl(p_int_line_tbl(1).exchange_rate,exchange_rate),
2497 nvl(p_int_line_tbl(1).exchange_rate_type,exchange_rate_type),
2498 nvl(p_int_line_tbl(1).exchange_rate_date,exchange_rate_date),
2499 nvl(p_int_line_tbl(1).po_number,po_number),
2500 nvl(p_int_line_tbl(1).po_release_number,po_release_number),
2501 nvl(p_int_line_tbl(1).po_type,po_type),
2502 nvl(p_int_line_tbl(1).invoice_number,invoice_number),
2503 nvl(p_int_line_tbl(1).date_invoiced,date_invoiced),
2504 nvl(p_int_line_tbl(1).order_number,order_number),
2505 nvl(p_int_line_tbl(1).date_ordered,date_ordered),
2506 nvl(p_int_line_tbl(1).date_shipped,date_shipped),
2507 nvl(p_int_line_tbl(1).claimed_amount,claimed_amount),
2508 nvl(p_int_line_tbl(1).allowed_amount,allowed_amount),
2509 nvl(p_int_line_tbl(1).total_allowed_amount,total_allowed_amount),
2510 nvl(p_int_line_tbl(1).accepted_amount,accepted_amount),
2511 nvl(p_int_line_tbl(1).total_accepted_amount,total_accepted_amount),
2512 nvl(p_int_line_tbl(1).line_tolerance_amount,line_tolerance_amount),
2513 nvl(p_int_line_tbl(1).tolerance_flag,tolerance_flag),
2514 DECODE(p_int_line_tbl(1).quantity,
2515 NULL,DECODE(p_int_line_tbl(1).orig_system_quantity,
2516 orig_system_quantity, total_claimed_amount,
2517 (p_int_line_tbl(1).orig_system_quantity*p_int_line_tbl(1).claimed_amount)),
2518 DECODE(p_int_line_tbl(1).quantity, quantity,
2519 total_claimed_amount,
2520 (p_int_line_tbl(1).quantity*p_int_line_tbl(1).claimed_amount))),
2521 DECODE(p_int_line_tbl(1).orig_system_purchase_price,NULL
2522 ,decode(p_int_line_tbl(1).purchase_price,NULL,purchase_price, p_int_line_tbl(1).purchase_price)
2523 ,decode(p_int_line_tbl(1).orig_system_purchase_price,orig_system_purchase_price, purchase_price, NULL)),
2524 DECODE(p_int_line_tbl(1).orig_system_purchase_uom,NULL
2525 ,decode(p_int_line_tbl(1).purchase_uom_code,NULL,purchase_uom_code, p_int_line_tbl(1).purchase_uom_code)
2526 ,decode(p_int_line_tbl(1).orig_system_purchase_uom,orig_system_purchase_uom, purchase_uom_code, NULL)),
2527 nvl(p_int_line_tbl(1).acctd_purchase_price,acctd_purchase_price),
2528 DECODE(p_int_line_tbl(1).orig_system_selling_price,NULL
2529 ,decode(p_int_line_tbl(1).selling_price,NULL,selling_price, p_int_line_tbl(1).selling_price)
2530 ,decode(p_int_line_tbl(1).orig_system_selling_price,orig_system_selling_price, selling_price, NULL)),
2531 nvl(p_int_line_tbl(1).acctd_selling_price,acctd_selling_price ),
2532 DECODE(p_int_line_tbl(1).orig_system_uom,NULL
2533 ,decode(p_int_line_tbl(1).uom_code,NULL,uom_code, p_int_line_tbl(1).uom_code)
2534 ,decode(p_int_line_tbl(1).orig_system_uom,orig_system_uom, uom_code, NULL)),
2535 DECODE(p_int_line_tbl(1).orig_system_quantity,NULL
2536 ,decode(p_int_line_tbl(1).quantity,NULL,quantity, p_int_line_tbl(1).quantity)
2537 ,decode(p_int_line_tbl(1).orig_system_quantity,orig_system_quantity, quantity, NULL)),
2538 nvl(p_int_line_tbl(1).calculated_price,calculated_price),
2539 nvl(p_int_line_tbl(1).acctd_calculated_price,acctd_calculated_price),
2540 nvl(p_int_line_tbl(1).calculated_amount,calculated_amount),
2541 nvl(p_int_line_tbl(1).credit_code,credit_code),
2542 nvl(p_int_line_tbl(1).credit_advice_date,credit_advice_date),
2543 nvl(p_int_line_tbl(1).upc_code,upc_code),
2544 DECODE(p_int_line_tbl(1).orig_system_item_number,NULL
2545 ,decode(p_int_line_tbl(1).inventory_item_id,NULL,inventory_item_id, p_int_line_tbl(1).inventory_item_id)
2546 ,decode(p_int_line_tbl(1).orig_system_item_number,orig_system_item_number, inventory_item_id, NULL)),
2547 nvl(p_int_line_tbl(1).item_number,item_number),
2548 nvl(p_int_line_tbl(1).item_description,item_description),
2549 nvl(p_int_line_tbl(1).inventory_item_segment1,inventory_item_segment1),
2550 nvl(p_int_line_tbl(1).inventory_item_segment2,inventory_item_segment2),
2551 nvl(p_int_line_tbl(1).inventory_item_segment3,inventory_item_segment3),
2552 nvl(p_int_line_tbl(1).inventory_item_segment4,inventory_item_segment4),
2553 nvl(p_int_line_tbl(1).inventory_item_segment5,inventory_item_segment5),
2554 nvl(p_int_line_tbl(1).inventory_item_segment6,inventory_item_segment6),
2555 nvl(p_int_line_tbl(1).inventory_item_segment7,inventory_item_segment7),
2556 nvl(p_int_line_tbl(1).inventory_item_segment8,inventory_item_segment8),
2557 nvl(p_int_line_tbl(1).inventory_item_segment9,inventory_item_segment9),
2558 nvl(p_int_line_tbl(1).inventory_item_segment10,inventory_item_segment10),
2559 nvl(p_int_line_tbl(1).inventory_item_segment11,inventory_item_segment11),
2560 nvl(p_int_line_tbl(1).inventory_item_segment12,inventory_item_segment12),
2561 nvl(p_int_line_tbl(1).inventory_item_segment13,inventory_item_segment13),
2562 nvl(p_int_line_tbl(1).inventory_item_segment14,inventory_item_segment14),
2563 nvl(p_int_line_tbl(1).inventory_item_segment15,inventory_item_segment15),
2564 nvl(p_int_line_tbl(1).inventory_item_segment16,inventory_item_segment16),
2565 nvl(p_int_line_tbl(1).inventory_item_segment17,inventory_item_segment17),
2566 nvl(p_int_line_tbl(1).inventory_item_segment18,inventory_item_segment18),
2567 nvl(p_int_line_tbl(1).inventory_item_segment19,inventory_item_segment19),
2568 nvl(p_int_line_tbl(1).inventory_item_segment20,inventory_item_segment20),
2569 nvl(p_int_line_tbl(1).product_category_id,product_category_id),
2570 nvl(p_int_line_tbl(1).category_name,category_name),
2571 nvl(p_int_line_tbl(1).duplicated_line_id,duplicated_line_id),
2572 nvl(p_int_line_tbl(1).duplicated_adjustment_id,duplicated_adjustment_id),
2573 nvl(p_int_line_tbl(1).response_type,response_type),
2574 nvl(p_int_line_tbl(1).response_code,response_code),
2575 nvl(p_int_line_tbl(1).reject_reason_code,reject_reason_code),
2576 nvl(p_int_line_tbl(1).followup_action_code,followup_action_code),
2577 nvl(p_int_line_tbl(1).net_adjusted_amount,net_adjusted_amount),
2578 NULL dispute_code,
2579 nvl(p_int_line_tbl(1).data_source_code,data_source_code),
2580 nvl(p_int_line_tbl(1).header_attribute_category,header_attribute_category),
2581 nvl(p_int_line_tbl(1).header_attribute1,header_attribute1),
2582 nvl(p_int_line_tbl(1).header_attribute2,header_attribute2),
2583 nvl(p_int_line_tbl(1).header_attribute3,header_attribute3),
2584 nvl(p_int_line_tbl(1).header_attribute4,header_attribute4),
2585 nvl(p_int_line_tbl(1).header_attribute5,header_attribute5),
2586 nvl(p_int_line_tbl(1).header_attribute6,header_attribute6),
2587 nvl(p_int_line_tbl(1).header_attribute7,header_attribute7),
2588 nvl(p_int_line_tbl(1).header_attribute8,header_attribute8),
2589 nvl(p_int_line_tbl(1).header_attribute9,header_attribute9),
2590 nvl(p_int_line_tbl(1).header_attribute10,header_attribute10),
2591 nvl(p_int_line_tbl(1).header_attribute11,header_attribute11),
2592 nvl(p_int_line_tbl(1).header_attribute12,header_attribute12),
2593 nvl(p_int_line_tbl(1).header_attribute13,header_attribute13),
2594 nvl(p_int_line_tbl(1).header_attribute14,header_attribute14),
2595 nvl(p_int_line_tbl(1).header_attribute15,header_attribute15),
2596 nvl(p_int_line_tbl(1).line_attribute_category,line_attribute_category),
2597 nvl(p_int_line_tbl(1).line_attribute1,line_attribute1),
2598 nvl(p_int_line_tbl(1).line_attribute2,line_attribute2),
2599 nvl(p_int_line_tbl(1).line_attribute3,line_attribute3),
2600 nvl(p_int_line_tbl(1).line_attribute4,line_attribute4),
2601 nvl(p_int_line_tbl(1).line_attribute5,line_attribute5),
2602 nvl(p_int_line_tbl(1).line_attribute6,line_attribute6),
2603 nvl(p_int_line_tbl(1).line_attribute7,line_attribute7),
2604 nvl(p_int_line_tbl(1).line_attribute8,line_attribute8),
2605 nvl(p_int_line_tbl(1).line_attribute9,line_attribute9),
2606 nvl(p_int_line_tbl(1).line_attribute10,line_attribute10),
2607 nvl(p_int_line_tbl(1).line_attribute11,line_attribute11),
2608 nvl(p_int_line_tbl(1).line_attribute12,line_attribute12),
2609 nvl(p_int_line_tbl(1).line_attribute13,line_attribute13),
2610 nvl(p_int_line_tbl(1).line_attribute14,line_attribute14),
2611 nvl(p_int_line_tbl(1).line_attribute15,line_attribute15),
2612 nvl(p_int_line_tbl(1).org_id,org_id),
2613
2614 -- For Bug#9447673 SSD IDSM ER(+)
2615 nvl(p_int_line_tbl(1).invoice_line_number,invoice_line_number),
2616 nvl(p_int_line_tbl(1).order_line_number,order_line_number),
2617 nvl(p_int_line_tbl(1).supplier_item_cost,supplier_item_cost),
2618 nvl(p_int_line_tbl(1).supplier_item_uom,supplier_item_uom)
2619 -- For Bug#9447673 SSD IDSM ER(-)
2620
2621 FROM ozf_resale_lines_int
2622 WHERE resale_line_int_id = pc_line_id
2623 AND resale_batch_id = pc_batch_id;
2624
2625
2626 BEGIN
2627 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
2628 x_return_status := FND_API.G_RET_STS_SUCCESS;
2629 OZF_UTILITY_PVT.debug_message('x_return_status ' || x_return_status);
2630
2631 l_int_line_tbl.extend;
2632 l_int_line_tbl(1).resale_batch_id := p_int_line_tbl(1).resale_batch_id;
2633
2634 OPEN get_int_line(p_int_line_tbl(1).resale_line_int_id, p_int_line_tbl(1).resale_batch_id);
2635 FETCH get_int_line INTO
2636 l_int_line_tbl(1).resale_line_int_id
2637 ,l_int_line_tbl(1).object_version_number
2638 ,l_int_line_tbl(1).last_update_date
2639 ,l_int_line_tbl(1).last_updated_by
2640 ,l_int_line_tbl(1).request_id
2641 ,l_int_line_tbl(1).created_from
2642 ,l_int_line_tbl(1).last_update_login
2643 ,l_int_line_tbl(1).program_application_id
2644 ,l_int_line_tbl(1).program_update_date
2645 ,l_int_line_tbl(1).program_id
2646 ,l_int_line_tbl(1).status_code
2647 ,l_int_line_tbl(1).resale_transfer_type
2648 ,l_int_line_tbl(1).product_transfer_movement_type
2649 ,l_int_line_tbl(1).product_transfer_date
2650 ,l_int_line_tbl(1).tracing_flag
2651 ,l_int_line_tbl(1).ship_from_cust_account_id
2652 ,l_int_line_tbl(1).ship_from_site_id
2653 ,l_int_line_tbl(1).ship_from_party_name
2654 ,l_int_line_tbl(1).ship_from_location
2655 ,l_int_line_tbl(1).ship_from_address
2656 ,l_int_line_tbl(1).ship_from_city
2657 ,l_int_line_tbl(1).ship_from_state
2658 ,l_int_line_tbl(1).ship_from_postal_code
2659 ,l_int_line_tbl(1).ship_from_country
2660 ,l_int_line_tbl(1).ship_from_contact_party_id
2661 ,l_int_line_tbl(1).ship_from_contact_name
2662 ,l_int_line_tbl(1).ship_from_email
2663 ,l_int_line_tbl(1).ship_from_phone
2664 ,l_int_line_tbl(1).ship_from_fax
2665 ,l_int_line_tbl(1).sold_from_cust_account_id
2666 ,l_int_line_tbl(1).sold_from_site_id
2667 ,l_int_line_tbl(1).sold_from_party_name
2668 ,l_int_line_tbl(1).sold_from_location
2669 ,l_int_line_tbl(1).sold_from_address
2670 ,l_int_line_tbl(1).sold_from_city
2671 ,l_int_line_tbl(1).sold_from_state
2672 ,l_int_line_tbl(1).sold_from_postal_code
2673 ,l_int_line_tbl(1).sold_from_country
2674 ,l_int_line_tbl(1).sold_from_contact_party_id
2675 ,l_int_line_tbl(1).sold_from_contact_name
2676 ,l_int_line_tbl(1).sold_from_email
2677 ,l_int_line_tbl(1).sold_from_phone
2678 ,l_int_line_tbl(1).sold_from_fax
2679 ,l_int_line_tbl(1).bill_to_cust_account_id
2680 ,l_int_line_tbl(1).bill_to_site_use_id
2681 ,l_int_line_tbl(1).bill_to_party_id
2682 ,l_int_line_tbl(1).bill_to_party_site_id
2683 ,l_int_line_tbl(1).bill_to_party_name
2684 ,l_int_line_tbl(1).bill_to_duns_number
2685 ,l_int_line_tbl(1).bill_to_location
2686 ,l_int_line_tbl(1).bill_to_address
2687 ,l_int_line_tbl(1).bill_to_city
2688 ,l_int_line_tbl(1).bill_to_state
2689 ,l_int_line_tbl(1).bill_to_postal_code
2690 ,l_int_line_tbl(1).bill_to_country
2691 ,l_int_line_tbl(1).bill_to_contact_party_id
2692 ,l_int_line_tbl(1).bill_to_contact_name
2693 ,l_int_line_tbl(1).bill_to_email
2694 ,l_int_line_tbl(1).bill_to_phone
2695 ,l_int_line_tbl(1).bill_to_fax
2696 ,l_int_line_tbl(1).ship_to_cust_account_id
2697 ,l_int_line_tbl(1).ship_to_site_use_id
2698 ,l_int_line_tbl(1).ship_to_party_id
2699 ,l_int_line_tbl(1).ship_to_party_site_id
2700 ,l_int_line_tbl(1).ship_to_party_name
2701 ,l_int_line_tbl(1).ship_to_duns_number
2702 ,l_int_line_tbl(1).ship_to_location
2703 ,l_int_line_tbl(1).ship_to_address
2704 ,l_int_line_tbl(1).ship_to_city
2705 ,l_int_line_tbl(1).ship_to_state
2706 ,l_int_line_tbl(1).ship_to_postal_code
2707 ,l_int_line_tbl(1).ship_to_country
2708 ,l_int_line_tbl(1).ship_to_contact_party_id
2709 ,l_int_line_tbl(1).ship_to_contact_name
2710 ,l_int_line_tbl(1).ship_to_email
2711 ,l_int_line_tbl(1).ship_to_phone
2712 ,l_int_line_tbl(1).ship_to_fax
2713 ,l_int_line_tbl(1).end_cust_party_id
2714 ,l_int_line_tbl(1).end_cust_site_use_id
2715 ,l_int_line_tbl(1).end_cust_site_use_code
2716 ,l_int_line_tbl(1).end_cust_party_site_id
2717 ,l_int_line_tbl(1).end_cust_party_name
2718 ,l_int_line_tbl(1).end_cust_location
2719 ,l_int_line_tbl(1).end_cust_address
2720 ,l_int_line_tbl(1).end_cust_city
2721 ,l_int_line_tbl(1).end_cust_state
2722 ,l_int_line_tbl(1).end_cust_postal_code
2723 ,l_int_line_tbl(1).end_cust_country
2724 ,l_int_line_tbl(1).end_cust_contact_party_id
2725 ,l_int_line_tbl(1).end_cust_contact_name
2726 ,l_int_line_tbl(1).end_cust_email
2727 ,l_int_line_tbl(1).end_cust_phone
2728 ,l_int_line_tbl(1).end_cust_fax
2729 ,l_int_line_tbl(1).direct_customer_flag
2730 ,l_int_line_tbl(1).order_type_id
2731 ,l_int_line_tbl(1).order_type
2732 ,l_int_line_tbl(1).order_category
2733 ,l_int_line_tbl(1).agreement_type
2734 ,l_int_line_tbl(1).agreement_id
2735 ,l_int_line_tbl(1).agreement_name
2736 ,l_int_line_tbl(1).agreement_price
2737 ,l_int_line_tbl(1).agreement_uom_code
2738 ,l_int_line_tbl(1).corrected_agreement_id
2739 ,l_int_line_tbl(1).corrected_agreement_name
2740 ,l_int_line_tbl(1).price_list_id
2741 ,l_int_line_tbl(1).price_list_name
2742 ,l_int_line_tbl(1).orig_system_reference
2743 ,l_int_line_tbl(1).orig_system_line_reference
2744 ,l_int_line_tbl(1).orig_system_currency_code
2745 ,l_int_line_tbl(1).orig_system_selling_price
2746 ,l_int_line_tbl(1).orig_system_quantity
2747 ,l_int_line_tbl(1).orig_system_uom
2748 ,l_int_line_tbl(1).orig_system_purchase_uom
2749 ,l_int_line_tbl(1).orig_system_purchase_curr
2750 ,l_int_line_tbl(1).orig_system_purchase_price
2751 ,l_int_line_tbl(1).orig_system_purchase_quantity
2752 ,l_int_line_tbl(1).orig_system_agreement_uom
2753 ,l_int_line_tbl(1).orig_system_agreement_name
2754 ,l_int_line_tbl(1).orig_system_agreement_type
2755 ,l_int_line_tbl(1).orig_system_agreement_status
2756 ,l_int_line_tbl(1).orig_system_agreement_curr
2757 ,l_int_line_tbl(1).orig_system_agreement_price
2758 ,l_int_line_tbl(1).orig_system_agreement_quantity
2759 ,l_int_line_tbl(1).orig_system_item_number
2760 ,l_int_line_tbl(1).currency_code
2761 ,l_int_line_tbl(1).exchange_rate
2762 ,l_int_line_tbl(1).exchange_rate_type
2763 ,l_int_line_tbl(1).exchange_rate_date
2764 ,l_int_line_tbl(1).po_number
2765 ,l_int_line_tbl(1).po_release_number
2766 ,l_int_line_tbl(1).po_type
2767 ,l_int_line_tbl(1).invoice_number
2768 ,l_int_line_tbl(1).date_invoiced
2769 ,l_int_line_tbl(1).order_number
2770 ,l_int_line_tbl(1).date_ordered
2771 ,l_int_line_tbl(1).date_shipped
2772 ,l_int_line_tbl(1).claimed_amount
2773 ,l_int_line_tbl(1).allowed_amount
2774 ,l_int_line_tbl(1).total_allowed_amount
2775 ,l_int_line_tbl(1).accepted_amount
2776 ,l_int_line_tbl(1).total_accepted_amount
2777 ,l_int_line_tbl(1).line_tolerance_amount
2778 ,l_int_line_tbl(1).tolerance_flag
2779 ,l_int_line_tbl(1).total_claimed_amount
2780 ,l_int_line_tbl(1).purchase_price
2781 ,l_int_line_tbl(1).purchase_uom_code
2782 ,l_int_line_tbl(1).acctd_purchase_price
2783 ,l_int_line_tbl(1).selling_price
2784 ,l_int_line_tbl(1).acctd_selling_price
2785 ,l_int_line_tbl(1).uom_code
2786 ,l_int_line_tbl(1).quantity
2787 ,l_int_line_tbl(1).calculated_price
2788 ,l_int_line_tbl(1).acctd_calculated_price
2789 ,l_int_line_tbl(1).calculated_amount
2790 ,l_int_line_tbl(1).credit_code
2791 ,l_int_line_tbl(1).credit_advice_date
2792 ,l_int_line_tbl(1).upc_code
2793 ,l_int_line_tbl(1).inventory_item_id
2794 ,l_int_line_tbl(1).item_number
2795 ,l_int_line_tbl(1).item_description
2796 ,l_int_line_tbl(1).inventory_item_segment1
2797 ,l_int_line_tbl(1).inventory_item_segment2
2798 ,l_int_line_tbl(1).inventory_item_segment3
2799 ,l_int_line_tbl(1).inventory_item_segment4
2800 ,l_int_line_tbl(1).inventory_item_segment5
2801 ,l_int_line_tbl(1).inventory_item_segment6
2802 ,l_int_line_tbl(1).inventory_item_segment7
2803 ,l_int_line_tbl(1).inventory_item_segment8
2804 ,l_int_line_tbl(1).inventory_item_segment9
2805 ,l_int_line_tbl(1).inventory_item_segment10
2806 ,l_int_line_tbl(1).inventory_item_segment11
2807 ,l_int_line_tbl(1).inventory_item_segment12
2808 ,l_int_line_tbl(1).inventory_item_segment13
2809 ,l_int_line_tbl(1).inventory_item_segment14
2810 ,l_int_line_tbl(1).inventory_item_segment15
2811 ,l_int_line_tbl(1).inventory_item_segment16
2812 ,l_int_line_tbl(1).inventory_item_segment17
2813 ,l_int_line_tbl(1).inventory_item_segment18
2814 ,l_int_line_tbl(1).inventory_item_segment19
2815 ,l_int_line_tbl(1).inventory_item_segment20
2816 ,l_int_line_tbl(1).product_category_id
2817 ,l_int_line_tbl(1).category_name
2818 ,l_int_line_tbl(1).duplicated_line_id
2819 ,l_int_line_tbl(1).duplicated_adjustment_id
2820 ,l_int_line_tbl(1).response_type
2821 ,l_int_line_tbl(1).response_code
2822 ,l_int_line_tbl(1).reject_reason_code
2823 ,l_int_line_tbl(1).followup_action_code
2824 ,l_int_line_tbl(1).net_adjusted_amount
2825 ,l_int_line_tbl(1).dispute_code
2826 ,l_int_line_tbl(1).data_source_code
2827 ,l_int_line_tbl(1).header_attribute_category
2828 ,l_int_line_tbl(1).header_attribute1
2829 ,l_int_line_tbl(1).header_attribute2
2830 ,l_int_line_tbl(1).header_attribute3
2831 ,l_int_line_tbl(1).header_attribute4
2832 ,l_int_line_tbl(1).header_attribute5
2833 ,l_int_line_tbl(1).header_attribute6
2834 ,l_int_line_tbl(1).header_attribute7
2835 ,l_int_line_tbl(1).header_attribute8
2836 ,l_int_line_tbl(1).header_attribute9
2837 ,l_int_line_tbl(1).header_attribute10
2838 ,l_int_line_tbl(1).header_attribute11
2839 ,l_int_line_tbl(1).header_attribute12
2840 ,l_int_line_tbl(1).header_attribute13
2841 ,l_int_line_tbl(1).header_attribute14
2842 ,l_int_line_tbl(1).header_attribute15
2843 ,l_int_line_tbl(1).line_attribute_category
2844 ,l_int_line_tbl(1).line_attribute1
2845 ,l_int_line_tbl(1).line_attribute2
2846 ,l_int_line_tbl(1).line_attribute3
2847 ,l_int_line_tbl(1).line_attribute4
2848 ,l_int_line_tbl(1).line_attribute5
2849 ,l_int_line_tbl(1).line_attribute6
2850 ,l_int_line_tbl(1).line_attribute7
2851 ,l_int_line_tbl(1).line_attribute8
2852 ,l_int_line_tbl(1).line_attribute9
2853 ,l_int_line_tbl(1).line_attribute10
2854 ,l_int_line_tbl(1).line_attribute11
2855 ,l_int_line_tbl(1).line_attribute12
2856 ,l_int_line_tbl(1).line_attribute13
2857 ,l_int_line_tbl(1).line_attribute14
2858 ,l_int_line_tbl(1).line_attribute15
2859 ,l_int_line_tbl(1).org_id
2860
2861 -- For Bug#9447673 SSD IDSM ER(+)
2862 ,l_int_line_tbl(1).invoice_line_number
2863 ,l_int_line_tbl(1).order_line_number
2864 ,l_int_line_tbl(1).supplier_item_cost
2865 ,l_int_line_tbl(1).supplier_item_uom;
2866 -- For Bug#9447673 SSD IDSM ER(-)
2867 CLOSE get_int_line;
2868 OZF_UTILITY_PVT.debug_message('x_return_status ' || x_return_status);
2869
2870 Ozf_pre_process_pvt.Update_interface_line
2871 (
2872 p_api_version_number => 1.0,
2873 p_init_msg_list => FND_API.G_FALSE,
2874 P_Commit => FND_API.G_FALSE,
2875 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2876 p_int_line_tbl => l_int_line_tbl,
2877 x_return_status => x_return_status,
2878 x_msg_count => x_msg_count,
2879 x_msg_data => x_msg_data
2880 );
2881 OZF_UTILITY_PVT.debug_message('x_return_status ' || x_return_status);
2882
2883 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2884 RAISE FND_API.G_EXC_ERROR;
2885 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2887 END IF;
2888
2889 -- Standard call to get message count and if count is 1, get message info.
2890 FND_MSG_PUB.Count_And_Get
2891 (p_count => x_msg_count,
2892 p_data => x_msg_data
2893 );
2894
2895 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End');
2896 EXCEPTION
2897
2898 WHEN FND_API.G_EXC_ERROR THEN
2899 x_return_status := FND_API.G_RET_STS_ERROR;
2900 -- Standard call to get message count and if count=1, get the message
2901 FND_MSG_PUB.Count_And_Get (
2902 p_encoded => FND_API.G_FALSE,
2903 p_count => x_msg_count,
2904 p_data => x_msg_data
2905 );
2906 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2908 -- Standard call to get message count and if count=1, get the message
2909 FND_MSG_PUB.Count_And_Get (
2910 p_encoded => FND_API.G_FALSE,
2911 p_count => x_msg_count,
2912 p_data => x_msg_data
2913 );
2914 WHEN OTHERS THEN
2915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2916
2917 -- Standard call to get message count and if count=1, get the message
2918 FND_MSG_PUB.Count_And_Get (
2919 p_encoded => FND_API.G_FALSE,
2920 p_count => x_msg_count,
2921 p_data => x_msg_data
2922 );
2923 END Update_Resale_Int_Line;
2924
2925 procedure insert_resale_int_line (
2926 p_api_version_number IN NUMBER,
2927 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2928 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2929 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2930 p_int_line_tbl IN ozf_pre_process_pvt.resale_line_int_tbl_type,
2931 x_return_status OUT NOCOPY VARCHAR2,
2932 x_msg_count OUT NOCOPY NUMBER,
2933 x_msg_data OUT NOCOPY VARCHAR2
2934 )
2935 IS
2936
2937 l_api_name CONSTANT VARCHAR2(30) := 'insert_resale_int_line';
2938 l_api_version_number CONSTANT NUMBER := 1.0;
2939
2940 l_resale_line_int_id NUMBER;
2941 l_object_version_number NUMBER;
2942 l_org_id NUMBER;
2943 l_amount NUMBER := 0;
2944 BEGIN
2945
2946 IF FND_API.to_Boolean( p_init_msg_list )
2947 THEN
2948 FND_MSG_PUB.initialize;
2949 END IF;
2950
2951 -- Debug Message
2952
2953 -- Initialize API return status to SUCCESS
2954 x_return_status := FND_API.G_RET_STS_SUCCESS;
2955
2956 BEGIN
2957
2958
2959 IF p_int_line_tbl.count > 0 THEN
2960
2961 FOR i in p_int_line_tbl.FIRST .. p_int_line_tbl.LAST
2962 LOOP
2963
2964 l_resale_line_int_id := p_int_line_tbl(i).resale_line_int_id;
2965 l_object_version_number := 1;
2966 l_org_id := p_int_line_tbl(i).org_id;
2967
2968 OZF_RESALE_LINES_INT_PKG.INSERT_ROW
2969 (
2970 px_resale_line_int_id => l_resale_line_int_id,
2971 px_object_version_number => l_object_version_number,
2972 p_last_update_date => sysdate,
2973 p_last_updated_by => FND_GLOBAL.user_id,
2974 p_creation_date => sysdate,
2975 p_request_id => NULL,
2976 p_created_by => FND_GLOBAL.user_id,
2977 p_created_from => NULL,
2978 p_last_update_login => FND_GLOBAL.user_id,
2979 p_program_application_id => NULL,
2980 p_program_update_date => NULL,
2981 p_program_id => NULL,
2982 p_response_type => p_int_line_tbl(i).response_type,
2983 p_response_code => p_int_line_tbl(i).response_code,
2984 p_reject_reason_code => p_int_line_tbl(i).reject_reason_code,
2985 p_followup_action_code => p_int_line_tbl(i).followup_action_code,
2986 p_resale_transfer_type => p_int_line_tbl(i).resale_transfer_type,
2987 p_product_trans_movement_type => p_int_line_tbl(i).product_transfer_movement_type,
2988 p_product_transfer_date => p_int_line_tbl(i).product_transfer_date,
2989 p_resale_batch_id => p_int_line_tbl(i).resale_batch_id,
2990 p_status_code => 'NEW',
2991 p_end_cust_party_id => p_int_line_tbl(i).end_cust_party_id,
2992 p_end_cust_site_use_id => NULL,
2993 p_end_cust_site_use_code => p_int_line_tbl(i).end_cust_site_use_code,
2994 p_end_cust_party_site_id => NULL,
2995 -- Bug 4469837 (+)
2996 --p_end_cust_party_name => NULL,
2997 p_end_cust_party_name => p_int_line_tbl(i).end_cust_party_name,
2998 -- Bug 4469837 (-)
2999 p_end_cust_location => p_int_line_tbl(i).end_cust_location,
3000 p_end_cust_address => p_int_line_tbl(i).end_cust_address,
3001 p_end_cust_city => p_int_line_tbl(i).end_cust_city,
3002 p_end_cust_state => p_int_line_tbl(i).end_cust_state,
3003 p_end_cust_postal_code => p_int_line_tbl(i).end_cust_postal_code,
3004 p_end_cust_country => p_int_line_tbl(i).end_cust_country,
3005 p_end_cust_contact_party_id => NULL,
3006 p_end_cust_contact_name => p_int_line_tbl(i).end_cust_contact_name,
3007 p_end_cust_email => p_int_line_tbl(i).end_cust_email,
3008 p_end_cust_phone => p_int_line_tbl(i).end_cust_phone,
3009 p_end_cust_fax => p_int_line_tbl(i).end_cust_fax,
3010 p_bill_to_cust_account_id => p_int_line_tbl(i).bill_to_cust_account_id,
3011 p_bill_to_site_use_id => NULL,
3012 -- Bug 4469837 (+)
3013 --p_bill_to_PARTY_NAME => NULL,
3014 p_bill_to_party_name => p_int_line_tbl(i).bill_to_party_name,
3015 -- Bug 4469837 (-)
3016 -- [BEGIN OF BUG 4186465 FIXING]
3017 p_bill_to_party_id => p_int_line_tbl(i).bill_to_party_id,
3018 p_bill_to_party_site_id => p_int_line_tbl(i).bill_to_party_site_id,
3019 -- [END OF BUG 4186465 FIXING]
3020 p_bill_to_location => p_int_line_tbl(i).bill_to_location,
3021 p_bill_to_duns_number => p_int_line_tbl(i).bill_to_duns_number,
3022 p_bill_to_address => p_int_line_tbl(i).bill_to_address,
3023 p_bill_to_city => p_int_line_tbl(i).bill_to_city,
3024 p_bill_to_state => p_int_line_tbl(i).bill_to_state,
3025 p_bill_to_postal_code => p_int_line_tbl(i).bill_to_postal_code,
3026 p_bill_to_country => p_int_line_tbl(i).bill_to_country,
3027 p_bill_to_contact_party_id => NULL,
3028 p_bill_to_contact_name => p_int_line_tbl(i).bill_to_contact_name,
3029 p_bill_to_email => p_int_line_tbl(i).bill_to_email,
3030 p_bill_to_phone => p_int_line_tbl(i).bill_to_phone,
3031 p_bill_to_fax => p_int_line_tbl(i).bill_to_fax,
3032 p_ship_to_cust_account_id => p_int_line_tbl(i).ship_to_cust_account_id,
3033 p_ship_to_site_use_id => NULL,
3034 -- Bug 4469837 (+)
3035 --p_ship_to_PARTY_NAME => NULL,
3036 p_ship_to_party_name => p_int_line_tbl(i).ship_to_party_name,
3037 -- Bug 4469837 (-)
3038 -- [BEGIN OF BUG 4186465 FIXING]
3039 p_ship_to_party_id => p_int_line_tbl(i).ship_to_party_id,
3040 p_ship_to_party_site_id => p_int_line_tbl(i).ship_to_party_site_id,
3041 -- [END OF BUG 4186465 FIXING]
3042 p_ship_to_duns_number => p_int_line_tbl(i).ship_to_duns_number,
3043 p_ship_to_location => p_int_line_tbl(i).ship_to_location,
3044 p_ship_to_address => p_int_line_tbl(i).ship_to_address,
3045 p_ship_to_city => p_int_line_tbl(i).ship_to_city,
3046 p_ship_to_state => p_int_line_tbl(i).ship_to_state,
3047 p_ship_to_postal_code => p_int_line_tbl(i).ship_to_postal_code,
3048 p_ship_to_country => p_int_line_tbl(i).ship_to_country,
3049 p_ship_to_contact_party_id => NULL,
3050 p_ship_to_contact_name => p_int_line_tbl(i).ship_to_contact_name,
3051 p_ship_to_email => p_int_line_tbl(i).ship_to_email,
3052 p_ship_to_phone => p_int_line_tbl(i).ship_to_phone,
3053 p_ship_to_fax => p_int_line_tbl(i).ship_to_fax,
3054 p_ship_from_cust_account_id => p_int_line_tbl(i).ship_from_cust_account_id,
3055 p_ship_from_site_id => NULL,
3056 p_ship_from_PARTY_NAME => NULL,
3057 p_ship_from_location => p_int_line_tbl(i).ship_from_location,
3058 p_ship_from_address => p_int_line_tbl(i).ship_from_address,
3059 p_ship_from_city => p_int_line_tbl(i).ship_from_city,
3060 p_ship_from_state => p_int_line_tbl(i).ship_from_state,
3061 p_ship_from_postal_code => p_int_line_tbl(i).ship_from_postal_code,
3062 p_ship_from_country => p_int_line_tbl(i).ship_from_country,
3063 p_ship_from_contact_party_id => NULL,
3064 p_ship_from_contact_name => p_int_line_tbl(i).ship_from_contact_name,
3065 p_ship_from_email => p_int_line_tbl(i).ship_from_email,
3066 p_ship_from_phone => p_int_line_tbl(i).ship_from_phone,
3067 p_ship_from_fax => p_int_line_tbl(i).ship_from_fax,
3068 p_sold_from_cust_account_id => p_int_line_tbl(i).sold_from_cust_account_id,
3069 p_sold_from_site_id => NULL,
3070 p_sold_from_PARTY_NAME => NULL,
3071 p_sold_from_location => p_int_line_tbl(i).sold_from_location,
3072 p_sold_from_address => p_int_line_tbl(i).sold_from_address,
3073 p_sold_from_city => p_int_line_tbl(i).sold_from_city,
3074 p_sold_from_state => p_int_line_tbl(i).sold_from_state,
3075 p_sold_from_postal_code => p_int_line_tbl(i).sold_from_postal_code,
3076 p_sold_from_country => p_int_line_tbl(i).sold_from_country,
3077 p_sold_from_contact_party_id => NULL,
3078 p_sold_from_contact_name => p_int_line_tbl(i).sold_from_contact_name,
3079 p_sold_from_email => p_int_line_tbl(i).sold_from_email,
3080 p_sold_from_phone => p_int_line_tbl(i).sold_from_phone,
3081 p_sold_from_fax => p_int_line_tbl(i).sold_from_fax,
3082 p_order_number => p_int_line_tbl(i).order_number,
3083 p_date_ordered => p_int_line_tbl(i).date_ordered,
3084 p_po_number => p_int_line_tbl(i).po_number,
3085 p_po_release_number => p_int_line_tbl(i).po_release_number,
3086 p_po_type => p_int_line_tbl(i).po_type,
3087 p_agreement_id => NULL,
3088 p_agreement_name => p_int_line_tbl(i).agreement_name,
3089 p_agreement_type => p_int_line_tbl(i).agreement_type,
3090 p_agreement_price => p_int_line_tbl(i).agreement_price,
3091 p_agreement_uom_code => p_int_line_tbl(i).agreement_uom_code,
3092 p_corrected_agreement_id => NULL,
3093 p_corrected_agreement_name => NULL,
3094 p_price_list_id => NULL,
3095 p_price_list_name => NULL,
3096 p_orig_system_quantity => p_int_line_tbl(i).orig_system_quantity,
3097 p_orig_system_uom => p_int_line_tbl(i).orig_system_uom,
3098 p_orig_system_currency_code => p_int_line_tbl(i).orig_system_currency_code,
3099 p_orig_system_selling_price => p_int_line_tbl(i).orig_system_selling_price,
3100 p_orig_system_reference => p_int_line_tbl(i).orig_system_reference,
3101 p_orig_system_line_reference => p_int_line_tbl(i).orig_system_line_reference,
3102 p_orig_system_purchase_uom => p_int_line_tbl(i).orig_system_purchase_uom,
3103 p_orig_system_purchase_curr => p_int_line_tbl(i).orig_system_purchase_curr,
3104 p_orig_system_purchase_price => p_int_line_tbl(i).orig_system_purchase_price,
3105 p_orig_system_purchase_quant => p_int_line_tbl(i).orig_system_purchase_quantity,
3106 p_orig_system_agreement_uom => p_int_line_tbl(i).orig_system_agreement_uom,
3107 p_ORIG_SYSTEM_AGREEMENT_name => p_int_line_tbl(i).orig_system_agreement_name,
3108 p_orig_system_agreement_type => p_int_line_tbl(i).orig_system_agreement_type,
3109 p_orig_system_agreement_status => p_int_line_tbl(i).orig_system_agreement_status,
3110 p_orig_system_agreement_curr => p_int_line_tbl(i).orig_system_agreement_curr,
3111 p_orig_system_agreement_price => p_int_line_tbl(i).orig_system_agreement_price,
3112 p_orig_system_agreement_quant => p_int_line_tbl(i).orig_system_agreement_quantity,
3113 p_orig_system_item_number => p_int_line_tbl(i).orig_system_item_number,
3114 p_quantity => p_int_line_tbl(i).quantity,
3115 p_uom_code => p_int_line_tbl(i).uom_code,
3116 p_currency_code => p_int_line_tbl(i).currency_code,
3117 p_exchange_rate => p_int_line_tbl(i).exchange_rate,
3118 p_exchange_rate_type => p_int_line_tbl(i).exchange_rate_type,
3119 p_exchange_rate_date => p_int_line_tbl(i).exchange_rate_date,
3120 p_selling_price => p_int_line_tbl(i).selling_price,
3121 p_purchase_uom_code => p_int_line_tbl(i).purchase_uom_code,
3122 p_invoice_number => p_int_line_tbl(i).invoice_number,
3123 p_date_invoiced => p_int_line_tbl(i).date_invoiced,
3124 p_date_shipped => p_int_line_tbl(i).date_shipped,
3125 p_credit_advice_date => p_int_line_tbl(i).credit_advice_date,
3126 p_product_category_id => NULL,
3127 p_category_name => NULL,
3128 p_inventory_item_segment1 => p_int_line_tbl(i).inventory_item_segment1,
3129 p_inventory_item_segment2 => p_int_line_tbl(i).inventory_item_segment2,
3130 p_inventory_item_segment3 => p_int_line_tbl(i).inventory_item_segment3,
3131 p_inventory_item_segment4 => p_int_line_tbl(i).inventory_item_segment4,
3132 p_inventory_item_segment5 => p_int_line_tbl(i).inventory_item_segment5,
3133 p_inventory_item_segment6 => p_int_line_tbl(i).inventory_item_segment6,
3134 p_inventory_item_segment7 => p_int_line_tbl(i).inventory_item_segment7,
3135 p_inventory_item_segment8 => p_int_line_tbl(i).inventory_item_segment8,
3136 p_inventory_item_segment9 => p_int_line_tbl(i).inventory_item_segment9,
3137 p_inventory_item_segment10 => p_int_line_tbl(i).inventory_item_segment10,
3138 p_inventory_item_segment11 => p_int_line_tbl(i).inventory_item_segment11,
3139 p_inventory_item_segment12 => p_int_line_tbl(i).inventory_item_segment12,
3140 p_inventory_item_segment13 => p_int_line_tbl(i).inventory_item_segment13,
3141 p_inventory_item_segment14 => p_int_line_tbl(i).inventory_item_segment14,
3142 p_inventory_item_segment15 => p_int_line_tbl(i).inventory_item_segment15,
3143 p_inventory_item_segment16 => p_int_line_tbl(i).inventory_item_segment16,
3144 p_inventory_item_segment17 => p_int_line_tbl(i).inventory_item_segment17,
3145 p_inventory_item_segment18 => p_int_line_tbl(i).inventory_item_segment18,
3146 p_inventory_item_segment19 => p_int_line_tbl(i).inventory_item_segment19,
3147 p_inventory_item_segment20 => p_int_line_tbl(i).inventory_item_segment20,
3148 p_inventory_item_id => p_int_line_tbl(i).inventory_item_id,
3149 p_item_description => NULL,
3150 p_upc_code => p_int_line_tbl(i).upc_code,
3151 p_item_number => NULL,
3152 p_claimed_amount => p_int_line_tbl(i).claimed_amount,
3153 p_purchase_price => p_int_line_tbl(i).purchase_price,
3154 p_acctd_purchase_price => NULL,
3155 p_net_adjusted_amount => NULL,
3156 p_accepted_amount => l_amount,
3157 p_total_accepted_amount => l_amount,
3158 p_allowed_amount => l_amount,
3159 p_total_allowed_amount => l_amount,
3160 p_calculated_price => l_amount,
3161 p_acctd_calculated_price => l_amount,
3162 p_calculated_amount => l_amount,
3163 p_line_tolerance_amount => NULL,
3164 p_total_claimed_amount => p_int_line_tbl(i).total_claimed_amount,
3165 p_credit_code => p_int_line_tbl(i).credit_code,
3166 p_direct_customer_flag => NULL,
3167 p_duplicated_line_id => NULL,
3168 p_duplicated_adjustment_id => NULL,
3169 p_order_type_id => NULL,
3170 p_order_type => p_int_line_tbl(i).order_type,
3171 p_order_category => p_int_line_tbl(i).order_category,
3172 p_dispute_code => NULL,
3173 p_data_source_code => p_int_line_tbl(i).data_source_code,
3174 p_tracing_flag => p_int_line_tbl(i).tracing_flag,
3175 p_header_attribute_category => p_int_line_tbl(i).header_attribute_category,
3176 p_header_attribute1 => p_int_line_tbl(i).header_attribute1,
3177 p_header_attribute2 => p_int_line_tbl(i).header_attribute2,
3178 p_header_attribute3 => p_int_line_tbl(i).header_attribute3,
3179 p_header_attribute4 => p_int_line_tbl(i).header_attribute4,
3180 p_header_attribute5 => p_int_line_tbl(i).header_attribute5,
3181 p_header_attribute6 => p_int_line_tbl(i).header_attribute6,
3182 p_header_attribute7 => p_int_line_tbl(i).header_attribute7,
3183 p_header_attribute8 => p_int_line_tbl(i).header_attribute8,
3184 p_header_attribute9 => p_int_line_tbl(i).header_attribute9,
3185 p_header_attribute10 => p_int_line_tbl(i).header_attribute10,
3186 p_header_attribute11 => p_int_line_tbl(i).header_attribute11,
3187 p_header_attribute12 => p_int_line_tbl(i).header_attribute12,
3188 p_header_attribute13 => p_int_line_tbl(i).header_attribute13,
3189 p_header_attribute14 => p_int_line_tbl(i).header_attribute14,
3190 p_header_attribute15 => p_int_line_tbl(i).header_attribute15,
3191 p_line_attribute_category => p_int_line_tbl(i).line_attribute_category,
3192 p_line_attribute1 => p_int_line_tbl(i).line_attribute1,
3193 p_line_attribute2 => p_int_line_tbl(i).line_attribute2,
3194 p_line_attribute3 => p_int_line_tbl(i).line_attribute3,
3195 p_line_attribute4 => p_int_line_tbl(i).line_attribute4,
3196 p_line_attribute5 => p_int_line_tbl(i).line_attribute5,
3197 p_line_attribute6 => p_int_line_tbl(i).line_attribute6,
3198 p_line_attribute7 => p_int_line_tbl(i).line_attribute7,
3199 p_line_attribute8 => p_int_line_tbl(i).line_attribute8,
3200 p_line_attribute9 => p_int_line_tbl(i).line_attribute9,
3201 p_line_attribute10 => p_int_line_tbl(i).line_attribute10,
3202 p_line_attribute11 => p_int_line_tbl(i).line_attribute11,
3203 p_line_attribute12 => p_int_line_tbl(i).line_attribute12,
3204 p_line_attribute13 => p_int_line_tbl(i).line_attribute13,
3205 p_line_attribute14 => p_int_line_tbl(i).line_attribute14,
3206 p_line_attribute15 => p_int_line_tbl(i).line_attribute15,
3207 px_org_id => l_org_id,
3208
3209 -- For Bug#9447673 SSD IDSM ER(+)
3210 p_invoice_line_number => p_int_line_tbl(i).invoice_line_number,
3211 p_order_line_number => p_int_line_tbl(i).order_line_number,
3212 p_supplier_item_cost => p_int_line_tbl(i).supplier_item_cost,
3213 p_supplier_item_uom => p_int_line_tbl(i).supplier_item_uom);
3214 -- For Bug#9447673 SSD IDSM ER(-)
3215
3216 END LOOP;
3217 END IF;
3218
3219 EXCEPTION
3220
3221 WHEN OTHERS THEN
3222
3223 ozf_utility_pvt.debug_message('Problem with updating line record '||SQLERRM);
3224 RAISE FND_API.G_EXC_ERROR;
3225
3226 END;
3227
3228
3229 -- Standard call to get message count and if count is 1, get message info.
3230 FND_MSG_PUB.Count_And_Get
3231 (p_count => x_msg_count,
3232 p_data => x_msg_data
3233 );
3234
3235 EXCEPTION
3236
3237 WHEN FND_API.G_EXC_ERROR THEN
3238 x_return_status := FND_API.G_RET_STS_ERROR;
3239 -- Standard call to get message count and if count=1, get the message
3240 FND_MSG_PUB.Count_And_Get (
3241 p_encoded => FND_API.G_FALSE,
3242 p_count => x_msg_count,
3243 p_data => x_msg_data
3244 );
3245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3247 -- Standard call to get message count and if count=1, get the message
3248 FND_MSG_PUB.Count_And_Get (
3249 p_encoded => FND_API.G_FALSE,
3250 p_count => x_msg_count,
3251 p_data => x_msg_data
3252 );
3253 WHEN OTHERS THEN
3254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3255 -- Standard call to get message count and if count=1, get the message
3256 FND_MSG_PUB.Count_And_Get (
3257 p_encoded => FND_API.G_FALSE,
3258 p_count => x_msg_count,
3259 p_data => x_msg_data
3260 );
3261
3262
3263 END insert_resale_int_line;
3264
3265 END OZF_WEBADI_INTERFACE_PVT;