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