[Home] [Help]
PACKAGE BODY: APPS.OZF_RESALE_COMMON_PVT
Source
1 PACKAGE BODY OZF_RESALE_COMMON_PVT AS
2 /* $Header: ozfvrscb.pls 120.20 2008/02/19 23:02:59 nirprasa ship $ */
3
4 -- Package name : OZF_RESALE_COMMON_PVT
5 -- Purpose :
6 -- HISTORY :
7 -- notE :
8 -- END of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_RESALE_COMMON_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvscb.pls';
12
13 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_debug_high);
14 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_debug_low);
15 OZF_UNEXP_ERROR CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_unexp_error);
16 OZF_ERROR CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_error);
17
18 G_CHBK_UTIL_TYPE CONSTANT VARCHAR2(30) := 'CHARGEBACK';
19 G_SPP_UTIL_TYPE CONSTANT VARCHAR2(30) :='UTILIZED';
20 G_TP_ACCRUAL_UTIL_TYPE CONSTANT VARCHAR2(30) :='ADJUSTMENT';
21
22 G_CHBK_ADJ_TYPE_ID CONSTANT NUMBER := -10;
23
24 G_ACCEPT_ALLOWED CONSTANT VARCHAR2(30) := 'ACCEPT_ALLOWED';
25 G_ACCEPT_CLAIMED CONSTANT VARCHAR2(30) := 'ACCEPT_CLAIMED';
26
27 G_ITEM_ORG_ID NUMBER := FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID');
28
29 -------------------------------------------------------------------------------
30
31 ---------------------------------------------------------------------
32 -- PROCEDURE
33 -- Insert_Resale_Log
34 --
35 -- PURPOSE
36 -- This procedure inserts a record in ozf_resale_logs_all table
37 --
38 -- PARAMETERS
39 --
40 --
41 -- NOTES
42 ---------------------------------------------------------------------
43 PROCEDURE Insert_Resale_Log (
44 p_id_value IN VARCHAR2,
45 p_id_type IN VARCHAR2,
46 p_error_code IN VARCHAR2,
47 p_error_message IN VARCHAR2 := NULL,
48 p_column_name IN VARCHAR2,
49 p_column_value IN VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2 )
51 IS
52 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Resale_Log';
53 l_api_version_number CONSTANT NUMBER := 1.0;
54 l_log_id NUMBER;
55 l_org_id NUMBER;
56 BEGIN
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58 IF OZF_DEBUG_HIGH_ON THEN
59 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
60 END IF;
61 IF OZF_DEBUG_LOW_ON THEN
62 OZF_UTILITY_PVT.debug_message('id_value:'||p_id_value );
63 OZF_UTILITY_PVT.debug_message('id_type:'||p_id_type );
64 OZF_UTILITY_PVT.debug_message('error_code:'||p_error_code);
65 IF p_error_message is NOT NULL THEN
66 OZF_UTILITY_PVT.debug_message('error_message:'||p_error_message);
67 ELSE
68 OZF_UTILITY_PVT.debug_message('error_message:'||fnd_message.get_string('OZF',p_error_code));
69 END IF;
70 OZF_UTILITY_PVT.debug_message('column_name'||p_column_name);
71 OZF_UTILITY_PVT.debug_message('column_value:'||p_column_value);
72 END IF;
73 --
74
75 IF p_error_code IS NOT NULL THEN
76 OPEN g_log_id_csr;
77 FETCH g_log_id_csr into l_log_id;
78 CLOSE g_log_id_csr;
79
80 -- julou bug 6317120. get org_id from table
81 IF p_id_type = 'BATCH' THEN
82 OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
83 FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
84 CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85 ELSIF p_id_type = 'LINE' THEN
86 OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87 FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88 CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89 ELSIF p_id_type = 'IFACE' THEN
90 OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
91 FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
92 CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
93 END IF;
94
95 BEGIN
96 OZF_RESALE_LOGS_PKG.Insert_Row(
97 px_resale_log_id => l_log_id,
98 p_resale_id => p_id_value,
99 p_resale_id_type => p_id_type,
100 p_error_code => p_error_code,
101 p_error_message => nvl(p_error_message, fnd_message.get_string('OZF',p_error_code)),
102 p_column_name => p_column_name,
103 p_column_value => p_column_value,
104 --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
105 px_org_id => l_org_id
106 );
107 EXCEPTION
108 WHEN OTHERS THEN
109 OZF_UTILITY_PVT.error_message('OZF_INS_RESALE_LOG_WRG');
110 RAISE FND_API.g_exc_unexpected_error;
111 END;
112 END IF;
113 --
114 IF OZF_DEBUG_HIGH_ON THEN
115 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
116 END IF;
117 EXCEPTION
118 WHEN FND_API.G_EXC_ERROR THEN
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122 WHEN OTHERS THEN
123 IF OZF_UNEXP_ERROR THEN
124 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
125 END IF;
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
127 END Insert_Resale_Log;
128
129 ---------------------------------------------------------------------
130 -- PROCEDURE
131 -- Bulk_Insert_Resale_Log
132 --
133 -- PURPOSE
134 -- This procedure inserts a lot of records in ozf_resale_logs_all table
135 --
136 -- PARAMETERS
137 --
138 --
139 -- NOTES
140 ---------------------------------------------------------------------
141 PROCEDURE Bulk_Insert_Resale_Log (
142 p_id_value IN number_tbl_type,
143 p_id_type IN VARCHAR2,
144 p_error_code IN varchar_tbl_type,
145 p_column_name IN varchar_tbl_type,
146 p_column_value IN long_varchar_tbl_type,
147 p_batch_id IN NUMBER, -- bug # 5997978 fixed
148 x_return_status OUT NOCOPY VARCHAR2
149 )
150 IS
151 l_api_name varchar2(30) := 'Bulk_Insert_Resale_Log';
152 -- Start: bug # 5997978 fixed
153 l_batch_org_id NUMBER;
154 l_org_id NUMBER;
155 -- End: bug # 5997978 fixed
156 BEGIN
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158 IF OZF_DEBUG_HIGH_ON THEN
159 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
160 END IF;
161 -- Start: bug # 5997978 fixed
162 OPEN g_resale_batch_org_id_csr(p_batch_id);
163 FETCH g_resale_batch_org_id_csr INTO l_batch_org_id;
164 CLOSE g_resale_batch_org_id_csr;
165 l_org_id := MO_GLOBAL.get_valid_org(l_batch_org_id);
166 IF (l_batch_org_id IS NULL OR l_org_id IS NULL) THEN
167 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_ORG_ID_NOTFOUND');
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170 -- End: bug # 5997978 fixed
171
172 -- bulk insert into resale logs table for above id's
173 FORALL i in 1..p_id_value.count
174 INSERT INTO ozf_resale_logs_all (
175 RESALE_LOG_ID,
176 RESALE_ID,
177 RESALE_ID_TYPE,
178 ERROR_CODE,
179 ERROR_MESSAGE,
180 COLUMN_NAME,
181 COLUMN_VALUE,
182 ORG_ID
183 ) VALUES (
184 ozf_resale_logs_all_s.nextval,
185 p_id_value(i),
186 p_id_type,
187 p_error_code(i),
188 FND_MESSAGE.get_string('OZF',p_error_code(i)),
189 p_column_name(i),
190 p_column_value(i),
191 -- Start: bug # 5997978 fixed
192 -- NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99)
193 l_org_id
194 -- End: bug # 5997978 fixed
195 );
196
197 IF OZF_DEBUG_HIGH_ON THEN
198 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
199 END IF;
200 EXCEPTION
201 WHEN FND_API.G_EXC_ERROR THEN
202 x_return_status := FND_API.G_RET_STS_ERROR;
203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 WHEN OTHERS THEN
206 IF OZF_UNEXP_ERROR THEN
207 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
208 END IF;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 END Bulk_Insert_Resale_Log;
211
212 ---------------------------------------------------------------------
213 -- PROCEDURE
214 -- Log_Null_Values
215 --
216 -- PURPOSE
217 -- This procedure checks null values from ozf_resale_lines_int_all table
218 --
219 -- PARAMETERS
220 --
221 --
222 -- NOTES
223 -- JXWU this proceducre should be moved to preprocess
224 --
225 ---------------------------------------------------------------------
226 PROCEDURE Log_Null_Values (
227 p_batch_id IN VARCHAR2,
228 x_return_status OUT NOCOPY VARCHAR2
229 )
230 IS
231 l_api_name CONSTANT VARCHAR2(30) := 'Log_Null_Values';
232 l_id_tbl number_tbl_type;
233 l_err_tbl varchar_tbl_type;
234 l_col_tbl varchar_tbl_type;
235 l_val_tbl long_varchar_tbl_type;
236 l_return_status varchar2(1);
237 --
238 l_report_start_date date;
239 l_report_end_date date;
240
241 CURSOR batch_info_csr (p_id IN NUMBER) IS
242 SELECT report_start_date, report_end_date
243 FROM ozf_resale_batches
244 WHERE resale_batch_id = p_id;
245 -- bugfix
246 CURSOR null_columns_csr (p_start_date IN DATE, p_end_date IN DATE) IS
247 SELECT resale_line_int_id, 'OZF_RESALE_ORD_NUM_MISS', 'ORDER_NUMBER', NULL
248 FROM ozf_resale_lines_int_all
249 WHERE resale_batch_id = p_batch_id
250 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
251 AND order_number IS NULL
252 UNION ALL
253 SELECT resale_line_int_id, 'OZF_RESALE_ORD_DATE_MISS', 'DATE_ORDERED', NULL
254 FROM ozf_resale_lines_int_all
255 WHERE resale_batch_id = p_batch_id
256 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
257 AND date_ordered IS NULL
258 UNION ALL
259 SELECT resale_line_int_id, 'OZF_ORD_DATE_LT_START', 'DATE_ORDERED', TO_CHAR(date_ordered)
260 FROM ozf_resale_lines_int_all
261 WHERE resale_batch_id = p_batch_id
262 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
263 AND date_ordered IS NOT NULL
264 AND date_ordered < p_start_date
265 UNION ALL
266 SELECT resale_line_int_id, 'OZF_ORD_DATE_GT_END', 'DATE_ORDERED', TO_CHAR(date_ordered)
267 FROM ozf_resale_lines_int_all
268 WHERE resale_batch_id = p_batch_id
269 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
270 AND date_ordered IS NOT NULL
271 AND date_ordered > p_end_date
272 UNION ALL
273 SELECT resale_line_int_id, 'OZF_RESALE_PRODUCT_ID_MISS', 'INVENTORY_ITEM_ID', NULL
274 FROM ozf_resale_lines_int_all
275 WHERE resale_batch_id = p_batch_id
276 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
277 AND inventory_item_id IS NULL
278 UNION ALL
279 SELECT resale_line_int_id, 'OZF_RESALE_UOM_MISS', 'UOM_CODE', NULL
280 FROM ozf_resale_lines_int_all
281 WHERE resale_batch_id = p_batch_id
282 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
283 AND uom_code IS NULL
284 UNION ALL
285 SELECT resale_line_int_id, 'OZF_RESALE_SOLD_FROM_MISS', 'SOLD_FROM_CUST_ACCOUNT_ID', NULL
286 FROM ozf_resale_lines_int_all
287 WHERE resale_batch_id = p_batch_id
288 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
289 AND sold_from_cust_account_id IS NULL
290 UNION ALL
291 SELECT resale_line_int_id, 'OZF_RESALE_SHIP_FROM_MISS', 'SHIP_FROM_CUST_ACCOUNT_ID', NULL
292 FROM ozf_resale_lines_int_all
293 WHERE resale_batch_id = p_batch_id
294 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
295 AND ship_from_cust_account_id IS NULL
296 UNION ALL
297 SELECT resale_line_int_id, 'OZF_RESALE_PRICE_LIST_NULL', 'AGREEMENT_ID', NULL
298 FROM ozf_resale_lines_int_all
299 WHERE resale_batch_id = p_batch_id
300 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
301 AND agreement_type = 'PL'
302 AND agreement_id IS NULL
303 UNION ALL
304 SELECT resale_line_int_id, 'OZF_RESALE_AGREE_NUM_NULL', 'AGREEMENT_ID', NULL
305 FROM ozf_resale_lines_int_all
306 WHERE resale_batch_id = p_batch_id
307 AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
308 AND agreement_type = 'SPO'
309 AND agreement_id IS NULL;
310
311 BEGIN
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313 --
314 IF OZF_DEBUG_HIGH_ON THEN
315 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
316 END IF;
317
318 -- get batch start and end date
319 OPEN batch_info_csr (p_batch_id);
320 FETCH batch_info_csr INTO l_report_start_date, l_report_end_date;
321 CLOSE batch_info_csr;
322
323 -- bulk select all lines with missing order numbers
324 OPEN null_columns_csr (l_report_start_date, l_report_end_date);
325 FETCH null_columns_csr BULK COLLECT INTO l_id_tbl, l_err_tbl, l_col_tbl, l_val_tbl;
326 CLOSE null_columns_csr;
327 --
328
329 IF l_id_tbl.exists(1) THEN
330 -- log disputed lines
331 IF OZF_DEBUG_LOW_ON THEN
332 OZF_UTILITY_PVT.debug_message('Number of errors: ' || l_id_tbl.LAST);
333 END IF;
334 Bulk_Insert_Resale_Log (
335 p_id_value => l_id_tbl,
336 p_id_type => G_ID_TYPE_IFACE,
337 p_error_code => l_err_tbl,
338 p_column_name => l_col_tbl,
339 p_column_value => l_val_tbl,
340 p_batch_id => p_batch_id, --bug # 5997978 fixed
341 x_return_status => l_return_status
342 );
343 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
344 RAISE FND_API.G_EXC_ERROR;
345 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END IF;
348 END IF;
349 --
350 IF OZF_DEBUG_HIGH_ON THEN
351 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
352 END IF;
353 EXCEPTION
354 WHEN FND_API.G_EXC_ERROR THEN
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 WHEN OTHERS THEN
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 IF OZF_UNEXP_ERROR THEN
361 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
362 END IF;
363 END Log_Null_Values;
364
365 ---------------------------------------------------------------------
366 -- PROCEDURE
367 -- Log_Invalid_Values
368 --
369 -- PURPOSE
370 -- This procedure checks invalid values from ozf_resale_lines_int_all table
371 --
372 -- PARAMETERS
373 --
374 -- NOTES:
375 -- JXWU this proceducre should be moved to preprocess
376 --
377 ---------------------------------------------------------------------
378 PROCEDURE Log_Invalid_Values (
379 p_batch_id IN VARCHAR2,
380 x_return_status OUT NOCOPY VARCHAR2
381 )
382 IS
383 l_api_name CONSTANT VARCHAR2(30) := 'Log_Invalid_Values';
384 l_id_tbl number_tbl_type;
385 l_err_tbl varchar_tbl_type;
386 l_col_tbl varchar_tbl_type;
387 l_val_tbl long_varchar_tbl_type;
388 l_return_status varchar2(1);
389 -- bugfix 4901702 SQL Repository - sangara
390 CURSOR invalid_columns_csr (p_resale_batch_id IN NUMBER) IS
391 SELECT orsl.resale_line_int_id
392 , 'OZF_CLAIM_CUST_NOT_IN_DB'
393 , 'SOLD_FROM_CUST_ACCOUNT_ID'
394 , to_char(orsl.sold_from_cust_account_id)
395 FROM ozf_resale_lines_int_all orsl
396 WHERE orsl.status_code = 'OPEN'
397 AND orsl.direct_customer_flag = 'T'
398 AND orsl.resale_batch_id = p_resale_batch_id
399 AND orsl.sold_from_cust_account_id IS NOT NULL
400 AND NOT EXISTS ( SELECT 1
401 FROM hz_cust_accounts hca
402 WHERE hca.cust_account_id = orsl.sold_from_cust_account_id)
403 UNION ALL
404 SELECT orsl.resale_line_int_id
405 , 'OZF_CLAIM_CUST_NOT_IN_DB'
406 , 'SHIP_FROM_CUST_ACCOUNT_ID'
407 , to_char(orsl.ship_from_cust_account_id)
408 FROM ozf_resale_lines_int_all orsl
409 WHERE orsl.status_code = 'OPEN'
410 AND orsl.direct_customer_flag = 'T'
411 AND orsl.resale_batch_id = p_resale_batch_id
412 AND orsl.ship_from_cust_account_id IS NOT NULL
413 AND NOT EXISTS ( SELECT 1
414 FROM hz_cust_accounts hca
415 WHERE hca.cust_account_id = orsl.ship_from_cust_account_id)
416 UNION ALL
417 SELECT orsl.resale_line_int_id
418 , 'OZF_RESALE_UOM_NOT_IN_DB'
419 , 'UOM_CODE'
420 , orsl.uom_code
421 FROM ozf_resale_lines_int_all orsl
422 WHERE orsl.direct_customer_flag ='T'
423 AND orsl.status_code = 'OPEN'
424 AND orsl.resale_batch_id = p_resale_batch_id
425 AND orsl.uom_code IS NOT NULL
426 AND NOT EXISTS ( SELECT 1
427 FROM mtl_units_of_measure mum
428 WHERE mum.uom_code = orsl.uom_code )
429 UNION ALL
430 SELECT orsl.resale_line_int_id
431 , 'OZF_RESALE_ORDTYPE_NOT_IN_DB'
432 , 'ORDER_TYPE_ID'
433 , to_char(orsl.order_type_id)
434 FROM ozf_resale_lines_int_all orsl
435 WHERE orsl.status_code = 'OPEN'
436 AND orsl.direct_customer_flag = 'T'
437 AND orsl.order_type_id IS NOT NULL
438 AND orsl.resale_batch_id = p_resale_batch_id
439 AND NOT EXISTS ( SELECT 1
440 FROM oe_transaction_types_all ottv
441 WHERE ottv.transaction_type_id = orsl.order_type_id)
442 UNION ALL
443 /*
444 SELECT orsl.resale_line_int_id
445 , 'OZF_RESALE_PRICE_NOT_IN_DB'
446 , 'AGREEMENT_ID'
447 , to_char(orsl.agreement_id)
448 FROM ozf_resale_lines_int_all orsl
449 WHERE orsl.status_code = 'OPEN'
450 AND orsl.direct_customer_flag = 'T'
451 AND orsl.resale_batch_id = p_resale_batch_id
452 AND orsl.agreement_id IS NOT NULL
453 AND NOT EXISTS ( SELECT 1
454 FROM qp_list_headers_b qlhv
455 WHERE qlhv.list_header_id = orsl.agreement_id
456 AND qlhv.list_type_code = 'PRL')
457 UNION ALL
458 */
459 SELECT orsl.resale_line_int_id
460 , 'OZF_RESALE_PRODUCT_NOT_IN_DB'
461 , 'INVENTORY_ITEM_ID'
462 , to_char(orsl.inventory_item_id)
463 FROM ozf_resale_lines_int_all orsl
464 WHERE orsl.status_code = 'OPEN'
465 AND orsl.direct_customer_flag = 'T'
466 AND orsl.resale_batch_id = p_resale_batch_id
467 AND orsl.inventory_item_id IS NOT NULL
468 AND NOT EXISTS ( SELECT 1
469 FROM mtl_system_items_b msi
470 WHERE msi.inventory_item_id = orsl.inventory_item_id
471 AND msi.organization_id = G_ITEM_ORG_ID);
472
473 BEGIN
474 x_return_status := FND_API.G_RET_STS_SUCCESS;
475 -- Debug Message
476 IF OZF_DEBUG_HIGH_ON THEN
477 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
478 END IF;
479 -- bulk select all lines with missing order numbers
480 OPEN invalid_columns_csr (p_batch_id);
481 FETCH invalid_columns_csr BULK COLLECT INTO l_id_tbl, l_err_tbl, l_col_tbl, l_val_tbl;
482 CLOSE invalid_columns_csr;
483 --
484
485 IF l_id_tbl.exists(1) THEN
486 -- log disputed lines
487 IF OZF_DEBUG_LOW_ON THEN
488 OZF_UTILITY_PVT.debug_message('Number of errors: ' || l_id_tbl.LAST);
489 END IF;
490 -- log disputed lines
491 Bulk_Insert_Resale_Log (
492 p_id_value => l_id_tbl,
493 p_id_type => G_ID_TYPE_IFACE,
494 p_error_code => l_err_tbl,
495 p_column_name => l_col_tbl,
496 p_column_value => l_val_tbl,
497 p_batch_id => p_batch_id, --bug # 5997978 fixed
498 x_return_status => l_return_status
499 );
500 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
501 RAISE FND_API.G_EXC_ERROR;
502 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505 --
506 END IF;
507
508 IF OZF_DEBUG_HIGH_ON THEN
509 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
510 END IF;
511 EXCEPTION
512 WHEN FND_API.G_EXC_ERROR THEN
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
516 WHEN OTHERS THEN
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 IF OZF_UNEXP_ERROR THEN
519 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
520 END IF;
521 END Log_Invalid_Values;
522
523 ---------------------------------------------------------------------
524 -- PROCEDURE
525 -- Bulk_Dispute_Line
526 --
527 -- PURPOSE
528 -- This procedure update disputed lines
529 --
530 -- PARAMETERS
531 --
532 -- NOTES:
533 -- JXWU this proceducre should be moved to preprocess
534 --
535 ---------------------------------------------------------------------
536 PROCEDURE Bulk_Dispute_Line (
537 p_batch_id IN NUMBER,
538 p_line_status IN VARCHAR2,
539 x_return_status OUT NOCOPY VARCHAR2
540 )
541 IS
542 l_api_name CONSTANT VARCHAR2(30) := 'Bulk_Dispute_Line';
543 l_api_version_number CONSTANT NUMBER := 1.0;
544 --
545 --
546 BEGIN
547 x_return_status := FND_API.G_RET_STS_SUCCESS;
548 IF OZF_DEBUG_HIGH_ON THEN
549 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
550 END IF;
551 --
552 BEGIN
553 UPDATE ozf_resale_lines_int_all orli
554 SET orli.dispute_code = (SELECT orl.error_code
555 FROM ozf_resale_logs_all orl
556 WHERE orl.resale_id = orli.resale_line_int_id
557 AND resale_id_type = 'IFACE'
558 AND rownum = 1)
559 , orli.status_code = G_BATCH_ADJ_DISPUTED
560 , followup_action_code = 'C'
561 , response_type = 'CA'
562 , response_code = 'N'
563 WHERE orli.resale_batch_id = p_batch_id
564 AND orli.status_code = p_line_status
565 AND EXISTS( SELECT 1
566 FROM ozf_resale_logs_all c
567 WHERE c.resale_id = orli.resale_line_int_id
568 AND c.resale_id_type = 'IFACE');
569 EXCEPTION
570 WHEN OTHERS THEN
571 IF OZF_UNEXP_ERROR THEN
572 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
573 END IF;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 END;
576 --
577 IF OZF_DEBUG_HIGH_ON THEN
578 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
579 END IF;
580 EXCEPTION
581 WHEN FND_API.G_EXC_ERROR THEN
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 WHEN OTHERS THEN
586 IF OZF_UNEXP_ERROR THEN
587 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
588 END IF;
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 END Bulk_Dispute_Line;
591
592 ---------------------------------------------------------------------
593 -- PROCEDURE
594 -- Update_Batch_Calculations
595 --
596 -- PURPOSE
597 -- ThIS procedure updates batch column based on data processing
598 --
599 -- PARAMETERS
600 --
601 --
602 -- NOTES
603 ---------------------------------------------------------------------
604 PROCEDURE Update_Batch_Calculations (
605 p_api_version IN NUMBER
606 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
607 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
608 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
609 ,p_resale_batch_id IN NUMBER
610 ,x_return_status OUT NOCOPY VARCHAR2
611 ,x_msg_data OUT NOCOPY VARCHAR2
612 ,x_msg_count OUT NOCOPY NUMBER
613 )
614 IS
615 l_api_name CONSTANT VARCHAR2(30) := 'Update_Batch_Calculations';
616 l_api_version CONSTANT NUMBER := 1.0;
617 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
618 --
619 l_return_status VARCHAR2(1);
620
621 l_lines_disputed NUMBER;
622 l_lines_w_tolerance NUMBER;
623 l_lines_invalid NUMBER;
624 l_lines_duplicated NUMBER;
625 --
626 l_tolerance NUMBER;
627 l_header_tolerance_calc_cd VARCHAR2(30);
628 l_header_tolerance_operand NUMBER;
629 --
630 l_calculated_amount NUMBER;
631 l_total_accepted_amount NUMBER;
632 l_total_allowed_amount NUMBER;
633 l_total_disputed_amount NUMBER;
634 l_total_claimed_amount NUMBER;
635 l_total_duplicated_amount NUMBER;
636 l_status_code VARCHAR2(30);
637 l_need_tolerance BOOLEAN;
638 --
639 CURSOR tolerance_line_count_csr (p_id NUMBER)IS
640 SELECT count(1)
641 FROM ozf_resale_lines_int
642 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
643 AND tolerance_flag = 'T'
644 AND resale_batch_id = p_id;
645
646 CURSOR invalid_line_count_csr (p_id NUMBER)IS
647 SELECT count(1)
648 FROM ozf_resale_lines_int
649 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
650 AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
651 AND resale_batch_id = p_id;
652
653 CURSOR header_tolerance_csr(p_id in NUMBER) IS
654 SELECT header_tolerance_operand, header_tolerance_calc_code
655 FROM ozf_resale_batches
656 WHERE resale_batch_id = p_id;
657
658 CURSOR csr_duplicated_dispute_amount(p_resale_batch_id IN NUMBER) IS
659 SELECT NVL(COUNT(resale_line_int_id), 0)
660 , NVL(SUM(total_claimed_amount), 0)
661 FROM ozf_resale_lines_int_all
662 WHERE resale_batch_id = p_resale_batch_id
663 AND status_code = 'DUPLICATED';
664
665 BEGIN
666 -- Standard begin of API savepoint
667 SAVEPOINT Update_Batch_Calculations;
668
669 -- Standard call to check FOR call compatibility.
670 IF NOT FND_API.Compatible_API_Call (
671 l_api_version,
672 p_api_version,
673 l_api_name,
674 G_PKG_NAME)
675 THEN
676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
677 END IF;
678
679 --Initialize message if p_init_msg_list IS TRUE.
680 IF FND_API.To_BOOLEAN (p_init_msg_list) THEN
681 FND_MSG_PUB.initialize;
682 END IF;
683
684 -- Debug Message
685 IF OZF_DEBUG_HIGH_ON THEN
686 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
687 END IF;
688 -- Initialize API return status to sucess
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690
691 -- get data regard thIS process
692 OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
693 FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
694 CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
695
696 OPEN tolerance_line_count_csr (p_resale_batch_id);
697 FETCH tolerance_line_count_csr INTO l_lines_w_tolerance;
698 CLOSE tolerance_line_count_csr;
699
700 -- get header level tolerance
701 OPEN header_tolerance_csr(p_resale_batch_id);
702 FETCH header_tolerance_csr INTO l_header_tolerance_operand,l_header_tolerance_calc_cd;
703 CLOSE header_tolerance_csr;
704
705 OPEN invalid_line_count_csr(p_resale_batch_id);
706 FETCH invalid_line_count_csr INTO l_lines_invalid;
707 CLOSE invalid_line_count_csr;
708
709 OPEN OZF_RESALE_COMMON_PVT.g_total_amount_csr (p_resale_batch_id);
710 FETCH OZF_RESALE_COMMON_PVT.g_total_amount_csr INTO l_calculated_amount,
711 l_total_claimed_amount,
712 l_total_accepted_amount,
713 l_total_allowed_amount,
714 l_total_disputed_amount;
715 CLOSE OZF_RESALE_COMMON_PVT.g_total_amount_csr;
716
717 --bug # 6134121 fixed by ateotia(+)
718 OPEN csr_duplicated_dispute_amount(p_resale_batch_id);
719 FETCH csr_duplicated_dispute_amount INTO l_lines_duplicated
720 , l_total_duplicated_amount;
721 CLOSE csr_duplicated_dispute_amount;
722 --bug # 6134121 fixed by ateotia(-)
723
724 IF OZF_DEBUG_LOW_ON THEN
725 OZF_UTILITY_PVT.debug_message('diputed line:' || l_lines_disputed || ' tolerance line' || l_lines_w_tolerance);
726 END IF;
727 --bug # 6134121 fixed by ateotia(+)
728 --IF l_lines_disputed = 0 THEN
729 IF (l_lines_disputed = 0 AND l_lines_duplicated = 0)THEN
730 --bug # 6134121 fixed by ateotia(-)
731 -- Need to check header tolerance
732 IF l_header_tolerance_operand IS NULL or
733 l_header_tolerance_calc_cd IS NULL
734 THEN
735 -- No need for tolerance
736 l_need_tolerance := false;
737 ELSE
738 l_need_tolerance := true;
739 -- Check tolerance level
740 -- % will be based on the transaction value of thIS batch
741 IF l_header_tolerance_calc_cd = '%' THEN
742 l_tolerance := l_total_allowed_amount * (l_header_tolerance_operand /100);
743 ELSE
744 l_tolerance := l_header_tolerance_operand;
745 END IF;
746 END IF;
747
748 IF l_need_tolerance THEN
749 -- BUG 4879544 (+)
750 -- IF l_total_accepted_amount <= l_total_claimed_amount + l_tolerance AND
751 -- l_total_accepted_amount >= l_total_claimed_amount - l_tolerance THEN
752 IF l_total_allowed_amount - l_tolerance <= l_total_accepted_amount AND
753 l_total_allowed_amount + l_tolerance >= l_total_accepted_amount THEN
754 -- BUG 4879544 (-)
755 l_status_code := 'PROCESSED';
756 ELSE
757 l_status_code := 'DISPUTED';
758 -- BUG 4879544 (+)
759 Insert_Resale_Log (
760 p_id_value => p_resale_batch_id,
761 p_id_type => 'BATCH',
762 p_error_code => 'OZF_BATCH_AMT_OUT_TOLERANCE',
763 p_column_name => 'ALLOWED_AMOUNT',
764 p_column_value => l_total_allowed_amount,
765 x_return_status => l_return_status
766 );
767 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
768 RAISE FND_API.G_EXC_ERROR;
769 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772 -- BUG 4879544 (-)
773 END IF;
774 ELSE
775 -- No need to check tolerance
776 l_status_code := 'PROCESSED';
777 END IF;
778 ELSE
779 -- batch IS in dispute
780 l_status_code := 'DISPUTED';
781 END IF;
782
783 -- invalid lines are the lines with status 'DISPUTED' AND dISpute code as 'INVLD'
784 -- lines_invalid = l_lines_invalid,
785 -- Lastly, I will UPDATE the batch
786
787 --bug # 6134121 fixed by ateotia(+)
788 /*OPEN csr_duplicated_dispute_amount(p_resale_batch_id);
789 FETCH csr_duplicated_dispute_amount INTO l_lines_duplicated
790 , l_total_duplicated_amount;
791 CLOSE csr_duplicated_dispute_amount;*/
792 --bug # 6134121 fixed by ateotia(-)
793
794 BEGIN
795 UPDATE ozf_resale_batches_all
796 SET status_code = l_status_code,
797 allowed_amount =l_total_allowed_amount,
798 accepted_amount = l_total_accepted_amount,
799 disputed_amount = ABS(l_total_disputed_amount) + ABS(l_total_duplicated_amount),
800 lines_w_tolerance = l_lines_w_tolerance,
801 lines_disputed = l_lines_disputed + l_lines_duplicated,
802 lines_invalid = l_lines_invalid
803 WHERE resale_batch_id = p_resale_batch_id;
804 EXCEPTION
805 WHEN OTHERS THEN
806 OZF_UTILITY_PVT.error_message('OZF_UPD_RESALE_BATCH_WRG');
807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
808 END;
809
810 -- Debug Message
811 IF OZF_DEBUG_HIGH_ON THEN
812 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
813 END IF;
814
815 --Standard call to get message count AND if count=1, get the message
816 FND_MSG_PUB.Count_and_Get (
817 p_encoded => FND_API.G_FALSE,
818 p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 EXCEPTION
822 WHEN FND_API.G_EXC_ERROR THEN
823 ROLLBACK TO Update_Batch_Calculations;
824 x_return_status := FND_API.G_RET_STS_ERROR;
825 -- Standard call to get message count AND IF count=1, get the message
826 FND_MSG_PUB.Count_and_Get (
827 p_encoded => FND_API.G_FALSE,
828 p_count => x_msg_count,
829 p_data => x_msg_data
830 );
831 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
832 ROLLBACK TO Update_Batch_Calculations;
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 -- Standard call to get message count AND IF count=1, get the message
835 FND_MSG_PUB.Count_and_Get (
836 p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data
839 );
840 WHEN OTHERS THEN
841 ROLLBACK TO Update_Batch_Calculations;
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843 IF OZF_UNEXP_ERROR THEN
844 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
845 END IF;
846 -- Standard call to get message count AND IF count=1, get the message
847 FND_MSG_PUB.Count_and_Get (
848 p_encoded => FND_API.G_FALSE,
849 p_count => x_msg_count,
850 p_data => x_msg_data
851 );
852 END Update_Batch_Calculations;
853
854 ---------------------------------------------------------------------
855 -- PROCEDURE
856 -- Update_Line_Calculations
857 --
858 -- PURPOSE
859 -- This procedure update ozf_lines_int_all table based on the data processing
860 -- PARAMETERS
861 -- x_return_status out VARCHAR2
862 --
863 -- NOTES
864 --
865 ---------------------------------------------------------------------
866 PROCEDURE Update_Line_Calculations(
867 p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE,
868 p_unit_price IN NUMBER,
869 p_line_quantity IN NUMBER,
870 p_allowed_amount IN NUMBER,
871 x_return_status OUT NOCOPY VARCHAR2
872 )
873 IS
874 l_api_name CONSTANT VARCHAR2(30) := 'Update_Process_Results';
875 l_api_version CONSTANT NUMBER := 1.0;
876 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
877 --
878 l_return_status VARCHAR2(1);
879
880 l_allowed_or_claimed VARCHAR2(30);
881 l_accepted_amount NUMBER;
882 --
883 l_tolerance NUMBER;
884 l_status_code VARCHAR2(30);
885 l_tolerance_flag VARCHAR2(1);
886 l_line_tolerance_amount NUMBER;
887 --
888 l_line_tolerance_calc_cd VARCHAR2(30);
889 l_line_tolerance_operand NUMBER;
890 l_followup_action_code VARCHAR2(30):= NULL;
891 l_response_type VARCHAR2(30):= NULL;
892 --
893 CURSOR line_tolerance_csr(p_id in NUMBER) IS
894 SELECT line_tolerance_operand, line_tolerance_calc_code
895 FROM ozf_resale_batches
896 WHERE resale_batch_id = p_id;
897
898 l_dispute_code VARCHAR2(30) := null;
899 l_net_adjusted_amount NUMBER;
900
901 CURSOR allowed_or_claimed_csr IS
902 SELECT ship_debit_calc_type
903 from ozf_sys_parameters;
904
905 -- bug 5969118 Ship and Debit return order generates positive claim amount
906 CURSOR c_batch_type(p_batch_id NUMBER) IS
907 SELECT batch_type
908 FROM ozf_resale_batches_all
909 WHERE resale_batch_id = p_batch_id;
910
911 l_batch_type VARCHAR2(30);
912 l_total_accepted_amount NUMBER;
913 l_total_allowed_amount NUMBER;
914 -- bug 5969118 end
915
916 --
917 BEGIN
918 -- Standard begin of API savepoint
919 SAVEPOINT Update_Line_Calculations;
920
921 -- Debug Message
922 IF OZF_DEBUG_HIGH_ON THEN
923 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
924 END IF;
925 IF OZF_DEBUG_LOW_ON THEN
926 OZF_UTILITY_PVT.debug_message('unit_price:'||p_unit_price);
927 OZF_UTILITY_PVT.debug_message('line_quantity:'||p_line_quantity);
928 OZF_UTILITY_PVT.debug_message('Allowed_amount:'|| p_allowed_amount);
929 END IF;
930
931 -- Initialize API return status to sucess
932 x_return_status := FND_API.G_RET_STS_SUCCESS;
933
934 IF p_resale_line_int_rec.claimed_amount IS NOT NULL THEN
935 --bug 6790803 for negative quantity
936 --IF p_allowed_amount = p_resale_line_int_rec.claimed_amount THEN
937 IF abs(p_allowed_amount) = p_resale_line_int_rec.claimed_amount THEN
938 -- No dispute in line as allowed and claimed are same
939 l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
940 l_line_tolerance_amount := 0;
941 l_tolerance_flag := 'F';
942 l_accepted_amount := p_allowed_amount;
943 ELSE
944 -- Check tolerance level
945 OPEN line_tolerance_csr (p_resale_line_int_rec.resale_batch_id);
946 FETCH line_tolerance_csr INTO l_line_tolerance_operand,l_line_tolerance_calc_cd;
947 CLOSE line_tolerance_csr;
948
949 -- tolerance % will be based on unit_price, or the total of the transaction
950 IF l_line_tolerance_calc_cd IS NULL THEN
951 l_tolerance := 0;
952 ELSE
953 IF l_line_tolerance_calc_cd = '%' THEN
954 l_tolerance := p_unit_price * l_line_tolerance_operand / 100;
955 ELSE
956 l_tolerance := l_line_tolerance_operand;
957 END IF;
958 END IF;
959
960 -- Set lines that do not fall INTO tolerence as DISPUTED
961 IF p_allowed_amount - l_tolerance <= p_resale_line_int_rec.claimed_amount AND
962 p_allowed_amount + l_tolerance >= p_resale_line_int_rec.claimed_amount THEN
963 l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
964 l_line_tolerance_amount := l_tolerance;
965 l_tolerance_flag := 'T';
966
967 -- use system parameter flag to determine with amount assign to accepted amount
968 OPEN allowed_or_claimed_csr;
969 FETCH allowed_or_claimed_csr into l_allowed_or_claimed;
970 CLOSE allowed_or_claimed_csr;
971
972 -- default to allowed
973 IF l_allowed_or_claimed IS NULL THEN
974 l_allowed_or_claimed := G_ACCEPT_ALLOWED;
975 END IF;
976
977 IF l_allowed_or_claimed = G_ACCEPT_ALLOWED THEN
978 l_accepted_amount := p_allowed_amount;
979 ELSE
980 l_accepted_amount := p_resale_line_int_rec.claimed_amount;
981 END IF;
982 ELSE
983 l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED;
984 l_line_tolerance_amount := l_tolerance;
985 l_tolerance_flag := 'F';
986 l_dispute_code := 'OZF_AMT_NOT_MATCH';
987 l_followup_action_code := 'C';
988 l_response_type := 'CA';
989
990 -- BUG 4879544 (+)
991 Insert_Resale_Log (
992 p_id_value => p_resale_line_int_rec.resale_line_int_id,
993 p_id_type => 'IFACE',
994 p_error_code => 'OZF_IFACE_AMT_OUT_TOLERANCE',
995 p_column_name => 'LINE_TOLERANCE_AMOUNT',
996 p_column_value => l_line_tolerance_amount,
997 x_return_status => l_return_status
998 );
999 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1000 RAISE FND_API.G_EXC_ERROR;
1001 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004 -- BUG 4879544 (-)
1005 END if;
1006 END IF;
1007 l_net_adjusted_amount := p_resale_line_int_rec.claimed_amount - l_accepted_amount;
1008 ELSE
1009 --Here user did not specific the claimed amount, I will calculate it based on the request
1010 l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
1011 l_accepted_amount := p_allowed_amount;
1012 l_line_tolerance_amount := null;
1013 l_tolerance_flag := 'F';
1014 l_net_adjusted_amount := NULL;
1015 END IF;
1016
1017 -- Update Line
1018 BEGIN
1019 -- bug 5969118 Ship and Debit return order generates positive claim amount
1020 OPEN c_batch_type(p_resale_line_int_rec.resale_batch_id);
1021 FETCH c_batch_type INTO l_batch_type;
1022 CLOSE c_batch_type;
1023
1024 IF l_batch_type = 'SHIP_DEBIT' AND p_resale_line_int_rec.resale_transfer_type = 'BN' THEN
1025 l_total_accepted_amount := ABS(l_accepted_amount * p_line_quantity) * -1;
1026 l_total_allowed_amount := ABS(p_allowed_amount * p_line_quantity) * -1;
1027 ELSE
1028 l_total_accepted_amount := l_accepted_amount * ABS(p_line_quantity);
1029 l_total_allowed_amount := p_allowed_amount * ABS(p_line_quantity);
1030 END IF;
1031 -- bug 5969118 end
1032
1033 UPDATE ozf_resale_lines_int_all
1034 SET accepted_amount = l_accepted_amount,
1035 -- total_accepted_amount = l_accepted_amount * ABS(p_line_quantity),
1036 -- bug 5969118 Ship and Debit return order generates positive claim amount
1037 total_accepted_amount = l_total_accepted_amount,
1038 -- bug 5969118 end
1039 allowed_amount = p_allowed_amount,
1040 -- total_allowed_amount = p_allowed_amount * ABS(p_line_quantity),
1041 -- bug 5969118 Ship and Debit return order generates positive claim amount
1042 total_allowed_amount = l_total_allowed_amount,
1043 -- bug 5969118 end
1044 net_adjusted_amount = l_net_adjusted_amount,
1045 calculated_price = p_unit_price,
1046 acctd_calculated_price = p_resale_line_int_rec.acctd_calculated_price,
1047 calculated_amount = p_unit_price * p_line_quantity,
1048 acctd_selling_price = p_resale_line_int_rec.acctd_selling_price,
1049 exchange_rate = p_resale_line_int_rec.exchange_rate,
1050 exchange_rate_date = p_resale_line_int_rec.exchange_rate_date,
1051 exchange_rate_type = p_resale_line_int_rec.exchange_rate_type,
1052 status_code = l_status_code,
1053 dispute_code = l_dispute_code,
1054 line_tolerance_amount = l_line_tolerance_amount,
1055 tolerance_flag = l_tolerance_flag,
1056 followup_action_code = l_followup_action_code,
1057 response_type = l_response_type,
1058 response_code = decode(l_status_code, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED, 'N',
1059 OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED, 'Y')
1060 WHERE resale_line_int_id = p_resale_line_int_rec.resale_line_int_id;
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 IF OZF_UNEXP_ERROR THEN
1064 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1065 END IF;
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067 END;
1068
1069 -- Debug Message
1070 IF OZF_DEBUG_HIGH_ON THEN
1071 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1072 END IF;
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 ROLLBACK TO Update_Line_Calculations;
1076 x_return_status := FND_API.G_RET_STS_ERROR;
1077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078 ROLLBACK TO Update_Line_Calculations;
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 WHEN OTHERS THEN
1081 ROLLBACK TO Update_Line_Calculations;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 IF OZF_UNEXP_ERROR THEN
1084 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1085 END IF;
1086 END Update_Line_Calculations;
1087
1088 ---------------------------------------------------------------------
1089 -- PROCEDURE
1090 -- Check_Duplicate_Line
1091 --
1092 -- PURPOSE
1093 -- This procedure tries to see whether the current line AND adjustments have been sent before.
1094 --
1095 -- PARAMETERS
1096 --
1097 --
1098 -- NOTES
1099 ---------------------------------------------------------------------
1100 PROCEDURE Check_Duplicate_Line(
1101 p_api_version_number IN NUMBER
1102 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
1103 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1104 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1105 ,p_resale_line_int_id IN NUMBER
1106 ,p_direct_customer_flag IN VARCHAR2
1107 ,p_claimed_amount IN NUMBER
1108 ,p_batch_type IN VARCHAR2
1109 ,x_dup_line_id OUT NOCOPY NUMBER
1110 ,x_dup_adjustment_id OUT NOCOPY NUMBER
1111 ,x_reprocessing OUT NOCOPY BOOLEAN
1112 ,x_return_status OUT NOCOPY VARCHAR2
1113 ,x_msg_count OUT NOCOPY NUMBER
1114 ,x_msg_data OUT NOCOPY VARCHAR2
1115 )IS
1116 l_api_name CONSTANT VARCHAR2(30) := 'Check_Duplicate_Line';
1117 l_api_version CONSTANT NUMBER := 1.0;
1118 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1119 --
1120 l_adjustment_id NUMBER;
1121 l_claimed_amount NUMBER;
1122 l_line_id NUMBER := NULL;
1123
1124 CURSOR dup_line_direct_resale_csr ( p_resale_line_int_id IN NUMBER )
1125 IS
1126 SELECT orl.resale_line_id
1127 -- Bug 4670154 (+)
1128 FROM ozf_resale_lines_all orl,
1129 ozf_resale_lines_int_all orli
1130 /*
1131 FROM ozf_resale_lines orl,
1132 ozf_resale_lines_int orli
1133 */
1134 -- Bug 4670154 (-)
1135 WHERE orl.order_number = orli.order_number
1136 AND orl.date_ordered = orli.date_ordered
1137 -- 6704619 (+)
1138 AND (orl.date_shipped = orli.date_shipped
1139 OR (orl.date_shipped IS NULL AND orli.date_shipped IS NULL))
1140 -- 6704619 (-)
1141 --AND orl.invoice_number = orli.invoice_number
1142 --AND orl.date_invoiced = orli.date_invoiced
1143 AND orl.inventory_item_id = orli.inventory_item_id
1144 AND orl.quantity = orli.quantity
1145 AND orl.uom_code = orli.uom_code
1146 AND orl.sold_from_cust_account_id = orli.sold_from_cust_account_id
1147 --AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
1148 AND orl.ship_from_cust_account_id = orli.ship_from_cust_account_id
1149 AND orl.direct_customer_flag = 'T'
1150 AND orl.bill_to_cust_account_id = orli.bill_to_cust_account_id
1151 AND orli.resale_line_int_id = p_resale_line_int_id
1152 AND rownum = 1;
1153
1154 CURSOR dup_line_indirect_resale_csr ( p_resale_line_int_id IN NUMBER )
1155 IS
1156 SELECT orl.resale_line_id
1157 -- Bug 4670154 (+)
1158 FROM ozf_resale_lines_all orl,
1159 ozf_resale_lines_int_all orli
1160 /*
1161 FROM ozf_resale_lines orl,
1162 ozf_resale_lines_int orli
1163 */
1164 -- Bug 4670154 (-)
1165 WHERE orl.order_number = orli.order_number
1166 AND orl.date_ordered = orli.date_ordered
1167 -- 6704619 (+)
1168 AND (orl.date_shipped = orli.date_shipped
1169 OR (orl.date_shipped IS NULL AND orli.date_shipped IS NULL))
1170 -- 6704619 (-)
1171 --AND orl.invoice_number = orli.invoice_number
1172 --AND orl.date_invoiced = orli.date_invoiced
1173 AND orl.inventory_item_id = orli.inventory_item_id
1174 AND orl.quantity = orli.quantity
1175 AND orl.uom_code = orli.uom_code
1176 AND orl.sold_from_cust_account_id = orli.sold_from_cust_account_id
1177 --AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
1178 AND orl.ship_from_cust_account_id = orli.ship_from_cust_account_id
1179 AND orl.direct_customer_flag = 'F'
1180 AND orl.bill_to_party_name = orli.bill_to_party_name
1181 AND orli.resale_line_int_id = p_resale_line_int_id
1182 AND rownum = 1;
1183
1184 CURSOR dup_adj_csr(
1185 p_line_id IN NUMBER,
1186 p_batch_type IN VARCHAR2)
1187 IS
1188 SELECT orsa.resale_adjustment_id
1189 , orsa.claimed_amount
1190 -- Bug 4670154 (+)
1191 FROM ozf_resale_adjustments_all orsa
1192 , ozf_resale_batches_all orsb
1193 /*
1194 FROM ozf_resale_adjustments orsa
1195 , ozf_resale_batches orsb
1196 */
1197 -- Bug 4670154 (-)
1198 WHERE orsa.resale_line_id = p_line_id
1199 AND orsa.resale_batch_id = orsb.resale_batch_id
1200 AND orsb.batch_type = p_batch_type
1201 -- Bug 4670154 (+)
1202 AND orsa.list_header_id IS NULL
1203 AND orsa.list_line_id IS NULL;
1204 -- Bug 4670154 (-)
1205
1206 CURSOR dup_line_direct_iface_csr ( p_resale_line_int_id IN NUMBER )
1207 IS
1208 SELECT orlo.resale_line_int_id
1209 -- Bug 4670154 (+)
1210 FROM ozf_resale_lines_int_all orlo,
1211 ozf_resale_lines_int_all orli
1212 /*
1213 FROM ozf_resale_lines_int orlo,
1214 ozf_resale_lines_int orli
1215 */
1216 -- Bug 4670154 (-)
1217 WHERE orlo.order_number = orli.order_number
1218 AND orlo.date_ordered = orli.date_ordered
1219 -- 6704619 (+)
1220 AND (orlo.date_shipped = orli.date_shipped
1221 OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
1222 -- 6704619 (-)
1223 AND orlo.inventory_item_id = orli.inventory_item_id
1224 AND orlo.quantity = orli.quantity
1225 AND orlo.uom_code = orli.uom_code
1226 AND orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
1227 --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1228 AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1229 AND orlo.claimed_amount = orli.claimed_amount
1230 AND orlo.bill_to_cust_account_id = orli.bill_to_cust_account_id
1231 AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1232 AND orlo.resale_line_int_id <> p_resale_line_int_id
1233 AND orlo.resale_batch_id <> orli.resale_batch_id
1234 AND orli.resale_line_int_id = p_resale_line_int_id
1235 AND orlo.duplicated_line_id IS NULL
1236 AND orlo.duplicated_adjustment_id IS NULL
1237 AND orlo.direct_customer_flag = 'T'
1238 AND orlo.creation_date <= orli.creation_date
1239 AND rownum = 1;
1240
1241 CURSOR dup_line_indirect_iface_csr ( p_resale_line_int_id IN NUMBER )
1242 IS
1243 SELECT orlo.resale_line_int_id
1244 -- Bug 4670154 (+)
1245 FROM ozf_resale_lines_int_all orlo,
1246 ozf_resale_lines_int_all orli
1247 /*
1248 FROM ozf_resale_lines_int orlo,
1249 ozf_resale_lines_int orli
1250 */
1251 -- Bug 4670154 (-)
1252 WHERE orlo.order_number = orli.order_number
1253 AND orlo.date_ordered = orli.date_ordered
1254 -- 6704619 (+)
1255 AND (orlo.date_shipped = orli.date_shipped
1256 OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
1257 -- 6704619 (-)
1258 --AND orl.invoice_number = orli.invoice_number
1259 --AND orl.date_invoiced = orli.date_invoiced
1260 AND orlo.inventory_item_id = orli.inventory_item_id
1261 AND orlo.quantity = orli.quantity
1262 AND orlo.uom_code = orli.uom_code
1263 AND orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
1264 --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1265 AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1266 AND orlo.claimed_amount = orli.claimed_amount
1267 AND orlo.bill_to_party_name = orli.bill_to_party_name
1268 AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1269 AND orlo.resale_line_int_id <> p_resale_line_int_id
1270 AND orlo.resale_batch_id <> orli.resale_batch_id
1271 AND orli.resale_line_int_id = p_resale_line_int_id
1272 AND orlo.duplicated_line_id IS NULL
1273 AND orlo.duplicated_adjustment_id IS NULL
1274 AND orlo.direct_customer_flag = 'F'
1275 AND orlo.creation_date <= orli.creation_date
1276 AND rownum = 1;
1277
1278 CURSOR dup_line_nondirect_resale_csr ( p_resale_line_int_id IN NUMBER )
1279 IS
1280 SELECT orl.resale_line_id
1281 FROM ozf_resale_lines_all orl,
1282 ozf_resale_lines_int_all orli
1283 -- Bug 4670154 (+)
1284 /*
1285 FROM ozf_resale_lines orl,
1286 ozf_resale_lines_int orli
1287 */
1288 -- Bug 4670154 (-)
1289 WHERE orl.order_number = orli.order_number
1290 AND orl.date_ordered = orli.date_ordered
1291 -- 6704619 (+)
1292 AND (orl.date_shipped = orli.date_shipped
1293 OR (orl.date_shipped IS NULL AND orli.date_shipped IS NULL))
1294 -- 6704619 (-)
1295 --AND orl.invoice_number = orli.invoice_number
1296 --AND orl.date_invoiced = orli.date_invoiced
1297 AND orl.inventory_item_id = orli.inventory_item_id
1298 AND orl.quantity = orli.quantity
1299 AND orl.uom_code = orli.uom_code
1300 AND orl.sold_from_cust_account_id = orli.sold_from_cust_account_id
1301 --AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
1302 AND orl.ship_from_cust_account_id = orli.ship_from_cust_account_id
1303 AND orl.bill_to_cust_account_id is null
1304 AND orl.bill_to_party_name is null
1305 AND orl.direct_customer_flag is null
1306 AND orli.resale_line_int_id = p_resale_line_int_id
1307 AND rownum = 1;
1308
1309 CURSOR dup_line_nondirect_iface_csr ( p_resale_line_int_id IN NUMBER )
1310 IS
1311 SELECT orlo.resale_line_int_id
1312 -- Bug 4670154 (+)
1313 FROM ozf_resale_lines_int_all orlo,
1314 ozf_resale_lines_int_all orli
1315 /*
1316 FROM ozf_resale_lines_int orlo,
1317 ozf_resale_lines_int orli
1318 */
1319 -- Bug 4670154 (-)
1320 WHERE orlo.order_number = orli.order_number
1321 AND orlo.date_ordered = orli.date_ordered
1322 -- 6704619 (+)
1323 AND (orlo.date_shipped = orli.date_shipped
1324 OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
1325 -- 6704619 (-)
1326 AND orlo.inventory_item_id = orli.inventory_item_id
1327 AND orlo.quantity = orli.quantity
1328 AND orlo.uom_code = orli.uom_code
1329 AND orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
1330 --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1331 AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1332 AND orlo.claimed_amount = orli.claimed_amount
1333 AND orlo.bill_to_cust_account_id is null
1334 AND orlo.bill_to_party_name is null
1335 AND orlo.direct_customer_flag is null
1336 AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1337 AND orlo.resale_line_int_id <> p_resale_line_int_id
1338 AND orlo.resale_batch_id <> orli.resale_batch_id
1339 AND orli.resale_line_int_id = p_resale_line_int_id
1340 AND orlo.duplicated_line_id IS NULL
1341 AND orlo.duplicated_adjustment_id IS NULL
1342 AND orlo.creation_date <= orli.creation_date
1343 AND rownum = 1;
1344 ---
1345
1346 CURSOR dup_line_dir_iface_self_csr ( p_resale_line_int_id IN NUMBER )
1347 IS
1348 SELECT orlo.resale_line_int_id
1349 FROM ozf_resale_lines_int_all orlo,
1350 ozf_resale_lines_int_all orli
1351 WHERE orlo.order_number = orli.order_number
1352 AND orlo.date_ordered = orli.date_ordered
1353 -- 6704619 (+)
1354 AND (orlo.date_shipped = orli.date_shipped
1355 OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
1356 -- 6704619 (-)
1357 AND orlo.inventory_item_id = orli.inventory_item_id
1358 AND orlo.quantity = orli.quantity
1359 AND orlo.uom_code = orli.uom_code
1360 AND orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
1361 AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1362 --AND orlo.claimed_amount = orli.claimed_amount
1363 AND orlo.bill_to_cust_account_id = orli.bill_to_cust_account_id
1364 AND orlo.status_code IN ('OPEN', 'PROCESSED')
1365 AND orlo.resale_line_int_id <> p_resale_line_int_id
1366 AND orlo.resale_batch_id = orli.resale_batch_id
1367 AND orli.resale_line_int_id = p_resale_line_int_id
1368 AND orlo.duplicated_line_id IS NULL
1369 AND orlo.duplicated_adjustment_id IS NULL
1370 AND orlo.direct_customer_flag = 'T'
1371 AND orlo.creation_date <= orli.creation_date
1372 AND rownum = 1;
1373
1374 CURSOR dup_line_indir_iface_self_csr ( p_resale_line_int_id IN NUMBER )
1375 IS
1376 SELECT orlo.resale_line_int_id
1377 FROM ozf_resale_lines_int_all orlo,
1378 ozf_resale_lines_int_all orli
1379 WHERE orlo.order_number = orli.order_number
1380 AND orlo.date_ordered = orli.date_ordered
1381 -- 6704619 (+)
1382 AND (orlo.date_shipped = orli.date_shipped
1383 OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
1384 -- 6704619 (-)
1385 AND orlo.inventory_item_id = orli.inventory_item_id
1386 AND orlo.quantity = orli.quantity
1387 AND orlo.uom_code = orli.uom_code
1388 AND orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
1389 AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1390 --AND orlo.claimed_amount = orli.claimed_amount
1391 AND orlo.bill_to_party_name = orli.bill_to_party_name
1392
1393 AND orlo.status_code IN ('OPEN', 'PROCESSED')
1394 AND orlo.resale_line_int_id <> p_resale_line_int_id
1395 AND orlo.resale_batch_id = orli.resale_batch_id
1396 AND orli.resale_line_int_id = p_resale_line_int_id
1397 AND orlo.duplicated_line_id IS NULL
1398 AND orlo.duplicated_adjustment_id IS NULL
1399 AND orlo.direct_customer_flag = 'F'
1400 AND orlo.creation_date <= orli.creation_date
1401 AND rownum = 1;
1402
1403 CURSOR dup_line_nondir_iface_self_csr ( p_resale_line_int_id IN NUMBER )
1404 IS
1405 SELECT orlo.resale_line_int_id
1406 FROM ozf_resale_lines_int_all orlo,
1407 ozf_resale_lines_int_all orli
1408 WHERE orlo.order_number = orli.order_number
1409 AND orlo.date_ordered = orli.date_ordered
1410 -- 6704619 (+)
1411 AND (orlo.date_shipped = orli.date_shipped
1412 OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
1413 -- 6704619 (-)
1414 AND orlo.inventory_item_id = orli.inventory_item_id
1415 AND orlo.quantity = orli.quantity
1416 AND orlo.uom_code = orli.uom_code
1417 AND orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
1418 AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1419 --AND orlo.claimed_amount = orli.claimed_amount
1420 AND orlo.bill_to_cust_account_id is null
1421 AND orlo.bill_to_party_name is null
1422 AND orlo.direct_customer_flag is null
1423 AND orlo.status_code IN ('OPEN', 'PROCESSED')
1424 AND orlo.resale_line_int_id <> p_resale_line_int_id
1425 AND orlo.resale_batch_id = orli.resale_batch_id
1426 AND orli.resale_line_int_id = p_resale_line_int_id
1427 AND orlo.duplicated_line_id IS NULL
1428 AND orlo.duplicated_adjustment_id IS NULL
1429 AND orlo.creation_date <= orli.creation_date
1430 AND rownum = 1;
1431
1432
1433 BEGIN
1434
1435 -- Standard call to check for call compatibility.
1436 IF NOT FND_API.Compatible_API_Call (
1437 l_api_version,
1438 p_api_version_number,
1439 l_api_name,
1440 G_PKG_NAME)
1441 THEN
1442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1443 END IF;
1444
1445 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
1446 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
1447 FND_MSG_PUB.initialize;
1448 END IF;
1449
1450 -- Debug Message
1451 IF OZF_DEBUG_HIGH_ON THEN
1452 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1453 END IF;
1454
1455 -- Initialize API return status to sucess
1456 x_return_status := FND_API.G_RET_STS_SUCCESS;
1457 IF OZF_DEBUG_LOW_ON THEN
1458 OZF_UTILITY_PVT.debug_message('resale_line_int_id'||p_resale_line_int_id);
1459 OZF_UTILITY_PVT.debug_message('direct_customer_flag'||p_direct_customer_flag);
1460 OZF_UTILITY_PVT.debug_message('claimed_amount'||p_claimed_amount);
1461 END IF;
1462
1463 -- Check with resale data first
1464 IF p_direct_customer_flag IS NULL THEN
1465 OPEN dup_line_nondirect_resale_csr(p_resale_line_int_id);
1466 FETCH dup_line_nondirect_resale_csr INTO l_line_id;
1467 CLOSE dup_line_nondirect_resale_csr;
1468 ELSE
1469 IF p_direct_customer_flag = 'T' THEN
1470 OPEN dup_line_direct_resale_csr(p_resale_line_int_id);
1471 FETCH dup_line_direct_resale_csr INTO l_line_id;
1472 CLOSE dup_line_direct_resale_csr;
1473 ELSE
1474 OPEN dup_line_indirect_resale_csr(p_resale_line_int_id);
1475 FETCH dup_line_indirect_resale_csr INTO l_line_id;
1476 CLOSE dup_line_indirect_resale_csr;
1477 END IF;
1478 END IF;
1479
1480 x_reprocessing := false;
1481 IF l_line_id IS NOT NULL THEN
1482 OPEN dup_adj_csr(l_line_id, p_batch_type);
1483 FETCH dup_adj_csr INTO l_adjustment_id, l_claimed_amount;
1484 CLOSE dup_adj_csr;
1485 --
1486
1487 x_dup_adjustment_id := l_adjustment_id;
1488 x_reprocessing := l_adjustment_id IS NOT NULL AND
1489 p_claimed_amount = l_claimed_amount;
1490
1491 IF OZF_DEBUG_LOW_ON THEN
1492 OZF_UTILITY_PVT.debug_message('l_adjustment_id = '||l_adjustment_id);
1493 OZF_UTILITY_PVT.debug_message('x_dup_adjustment_id = '||x_dup_adjustment_id);
1494 IF x_reprocessing THEN
1495 OZF_UTILITY_PVT.debug_message('x_reprocessing >> Yes');
1496 ELSE
1497 OZF_UTILITY_PVT.debug_message('x_reprocessing >> No');
1498 END IF;
1499 END IF;
1500
1501 ELSE
1502 -- check to see whether it can be a duplicate of an interface line.
1503 -- In this case, reprocess is always false.
1504 IF p_direct_customer_flag IS NULL THEN
1505 OPEN dup_line_nondirect_iface_csr(p_resale_line_int_id);
1506 FETCH dup_line_nondirect_iface_csr INTO l_line_id;
1507 CLOSE dup_line_nondirect_iface_csr;
1508 ELSE
1509 IF p_direct_customer_flag = 'T' THEN
1510 OPEN dup_line_direct_iface_csr(p_resale_line_int_id);
1511 FETCH dup_line_direct_iface_csr INTO l_line_id;
1512 CLOSE dup_line_direct_iface_csr;
1513 ELSE
1514 OPEN dup_line_indirect_iface_csr(p_resale_line_int_id);
1515 FETCH dup_line_indirect_iface_csr INTO l_line_id;
1516 CLOSE dup_line_indirect_iface_csr;
1517 END IF;
1518 END IF;
1519 -- -1 to indicate it's a duplication to an interface line.
1520 IF l_line_id is NOT NULL THEN
1521 l_adjustment_id := -1;
1522 --x_reprocessing := true;
1523 x_reprocessing := false;
1524 END IF;
1525 END IF;
1526
1527 IF l_line_id IS NULL THEN
1528 IF p_direct_customer_flag IS NULL THEN
1529 OPEN dup_line_nondir_iface_self_csr(p_resale_line_int_id);
1530 FETCH dup_line_nondir_iface_self_csr INTO l_line_id;
1531 CLOSE dup_line_nondir_iface_self_csr;
1532 ELSE
1533 IF p_direct_customer_flag = 'T' THEN
1534 OPEN dup_line_dir_iface_self_csr(p_resale_line_int_id);
1535 FETCH dup_line_dir_iface_self_csr INTO l_line_id;
1536 CLOSE dup_line_dir_iface_self_csr;
1537 ELSE
1538 OPEN dup_line_indir_iface_self_csr(p_resale_line_int_id);
1539 FETCH dup_line_indir_iface_self_csr INTO l_line_id;
1540 CLOSE dup_line_indir_iface_self_csr;
1541 END IF;
1542 END IF;
1543 -- -1 to indicate it's a duplication to an interface line within the same batch
1544 IF l_line_id IS NOT NULL THEN
1545 l_adjustment_id := -1;
1546 x_reprocessing := true;
1547 END IF;
1548 END IF;
1549
1550
1551 IF OZF_DEBUG_LOW_ON THEN
1552 OZF_UTILITY_PVT.debug_message('dup adj id' || l_adjustment_id);
1553 OZF_UTILITY_PVT.debug_message('dup line id' || l_line_id);
1554 END IF;
1555
1556 x_dup_adjustment_id := l_adjustment_id;
1557 x_dup_line_id := l_line_id;
1558
1559
1560 -- Debug Message
1561 IF OZF_DEBUG_HIGH_ON THEN
1562 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1563 END IF;
1564
1565 --Standard call to get message count AND IF count=1, get the message
1566 FND_MSG_PUB.Count_And_Get (
1567 p_encoded => FND_API.G_FALSE,
1568 p_count => x_msg_count,
1569 p_data => x_msg_data
1570 );
1571 EXCEPTION
1572 WHEN FND_API.G_EXC_ERROR THEN
1573 x_return_status := FND_API.G_RET_STS_ERROR;
1574 -- Standard call to get message count AND IF count=1, get the message
1575 FND_MSG_PUB.Count_And_Get (
1576 p_encoded => FND_API.G_FALSE,
1577 p_count => x_msg_count,
1578 p_data => x_msg_data
1579 );
1580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1582 -- Standard call to get message count AND IF count=1, get the message
1583 FND_MSG_PUB.Count_And_Get (
1584 p_encoded => FND_API.G_FALSE,
1585 p_count => x_msg_count,
1586 p_data => x_msg_data
1587 );
1588 WHEN OTHERS THEN
1589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1590 IF OZF_UNEXP_ERROR THEN
1591 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1592 END IF;
1593 -- Standard call to get message count AND IF count=1, get the message
1594 FND_MSG_PUB.Count_And_Get (
1595 p_encoded => FND_API.G_FALSE,
1596 p_count => x_msg_count,
1597 p_data => x_msg_data
1598 );
1599 END Check_Duplicate_Line;
1600
1601 ---------------------------------------------------------------------
1602 -- PROCEDURE
1603 -- Update_Duplicates
1604 --
1605 -- PURPOSE
1606 -- This procedure updates the duplicates
1607 --
1608 -- PARAMETERS
1609 --
1610 --
1611 -- NOTES
1612 ---------------------------------------------------------------------
1613 PROCEDURE Update_Duplicates (
1614 p_api_version IN NUMBER
1615 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
1616 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1617 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1618 ,p_resale_batch_id IN NUMBER
1619 ,p_resale_batch_type IN VARCHAR2
1620 ,p_batch_status IN VARCHAR2
1621 ,x_batch_status OUT NOCOPY VARCHAR2
1622 ,x_return_status OUT NOCOPY VARCHAR2
1623 ,x_msg_data OUT NOCOPY VARCHAR2
1624 ,x_msg_count OUT NOCOPY NUMBER
1625 )
1626 IS
1627 l_api_name CONSTANT VARCHAR2(30) := 'Update_Duplicates';
1628 l_api_version CONSTANT NUMBER := 1.0;
1629 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1630 --
1631 l_return_status VARCHAR2(30);
1632 l_msg_data VARCHAR2(2000);
1633 l_msg_count NUMBER;
1634
1635 i NUMBER;
1636
1637 l_dup_line_id NUMBER;
1638 l_dup_adjustment_id NUMBER;
1639 l_reprocessing BOOLEAN;
1640 l_duplicate_count NUMBER := 0;
1641 l_batch_count NUMBER := 0;
1642 --
1643 l_open_lines_tbl number_tbl_type;
1644 l_direct_customer_tbl varchar_tbl_type;
1645 l_claimed_amount_tbl number_tbl_type;
1646 --
1647 CURSOR open_lines_csr(p_id IN NUMBER) IS
1648 SELECT resale_line_int_id, direct_customer_flag, claimed_amount
1649 FROM ozf_resale_lines_int
1650 -- WHERE status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1651 WHERE resale_batch_id = p_id;
1652
1653 CURSOR batch_count_csr(pc_batch_id NUMBER) IS
1654 SELECT NVL(batch_count,0)
1655 FROM ozf_resale_batches
1656 WHERE resale_batch_id = pc_batch_id;
1657
1658 CURSOR duplicate_count_csr(p_id NUMBER) IS
1659 SELECT count(1)
1660 FROM ozf_resale_lines_int
1661 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
1662 AND resale_batch_id = p_id;
1663
1664 BEGIN
1665 SAVEPOINT Update_Duplicates;
1666
1667 -- Standard call to check for call compatibility.
1668 IF NOT FND_API.Compatible_API_Call (
1669 l_api_version,
1670 p_api_version,
1671 l_api_name,
1672 G_PKG_NAME)
1673 THEN
1674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1675 END IF;
1676
1677 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
1678 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
1679 FND_MSG_PUB.initialize;
1680 END IF;
1681
1682 -- Debug Message
1683 IF OZF_DEBUG_HIGH_ON THEN
1684 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1685 END IF;
1686
1687 -- Initialize API return status to sucess
1688 x_return_status := FND_API.G_RET_STS_SUCCESS;
1689
1690 OPEN open_lines_csr(p_resale_batch_id);
1691 FETCH open_lines_csr BULK COLLECT INTO l_open_lines_tbl, l_direct_customer_tbl, l_claimed_amount_tbl;
1692 CLOSE open_lines_csr;
1693
1694 IF l_open_lines_tbl.EXISTS(1) THEN
1695 FOR i IN 1..l_open_lines_tbl.LAST
1696 LOOP
1697 -- BUG 4670154 (+)
1698 UPDATE ozf_resale_lines_int_all
1699 SET duplicated_line_id = NULL
1700 , duplicated_adjustment_id = NULL
1701 WHERE resale_line_int_id = l_open_lines_tbl(i);
1702 -- BUG 4670154 (-)
1703
1704 OZF_RESALE_COMMON_PVT.Check_Duplicate_Line (
1705 p_api_version_number => 1.0
1706 ,p_init_msg_LIST => FND_API.G_FALSE
1707 ,p_commit => FND_API.G_FALSE
1708 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1709 ,x_return_status => l_return_status
1710 ,x_msg_count => l_msg_count
1711 ,x_msg_data => l_msg_data
1712 ,p_resale_line_int_id => l_open_lines_tbl(i)
1713 ,p_direct_customer_flag => l_direct_customer_tbl(i)
1714 ,p_claimed_amount => l_claimed_amount_tbl(i)
1715 ,p_batch_type => p_resale_batch_type
1716 ,x_dup_line_id => l_dup_line_id
1717 ,x_dup_adjustment_id => l_dup_adjustment_id
1718 ,x_reprocessing => l_reprocessing
1719 );
1720
1721 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1722 OZF_UTILITY_PVT.error_message('OZF_RESALE_CHK_DUP_ERR');
1723 ELSE
1724 IF l_dup_adjustment_id IS NOT NULL AND l_reprocessing THEN
1725 -- Set the line and adjustment as duplicates
1726 UPDATE ozf_resale_lines_int
1727 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
1728 , duplicated_line_id = l_dup_line_id
1729 , duplicated_adjustment_id = l_dup_adjustment_id
1730 , dispute_code = 'OZF_RESALE_DUP'
1731 WHERE resale_line_int_id = l_open_lines_tbl(i);
1732
1733 Insert_Resale_Log (
1734 p_id_value => l_open_lines_tbl(i),
1735 p_id_type => 'IFACE',
1736 p_error_code => 'OZF_RESALE_DUP',
1737 p_column_name => 'DUPLICATED_ADJUSTMENT_ID',
1738 p_column_value => l_dup_adjustment_id,
1739 x_return_status => l_return_status );
1740 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1741 RAISE FND_API.G_EXC_ERROR;
1742 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1744 END IF;
1745 ELSE
1746 -- record dup line if necessary
1747 UPDATE ozf_resale_lines_int
1748 SET duplicated_line_id = l_dup_line_id
1749 , duplicated_adjustment_id = l_dup_adjustment_id
1750 WHERE resale_line_int_id = l_open_lines_tbl(i);
1751 END IF;
1752 END IF;
1753 END LOOP;
1754 END IF;
1755
1756 OPEN duplicate_count_csr (p_resale_batch_id);
1757 FETCH duplicate_count_csr INTO l_duplicate_count;
1758 CLOSE duplicate_count_csr;
1759
1760 OPEN batch_count_csr(p_resale_batch_id);
1761 FETCH batch_count_csr INTO l_batch_count;
1762 CLOSE batch_count_csr;
1763
1764 IF l_duplicate_count = l_batch_count THEN
1765 -- Reject batch if all lines are duplicates
1766 x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED ;
1767 --
1768 UPDATE ozf_resale_batches_all
1769 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
1770 WHERE resale_batch_id = p_resale_batch_id;
1771 --
1772 --bug # 6134121 fixed by ateotia(+)
1773 ELSIF (l_duplicate_count >=1) THEN
1774 --dispute the batch if lines are duplicated within in the same batch
1775 x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED ;
1776 UPDATE ozf_resale_batches_all
1777 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1778 WHERE resale_batch_id = p_resale_batch_id;
1779 --bug # 6134121 fixed by ateotia(-)
1780 ELSE
1781 -- JXWU In this case we just keep the current status
1782 x_batch_status := p_batch_status;
1783 /*
1784 -- Open batch IS there are some Open lines to process
1785 x_batch_status := 'OPEN';
1786 --
1787 BEGIN
1788 -- set DISPUTED_code to NULL for the lines to be processed.
1789 UPDATE ozf_resale_lines_int
1790 SET dispute_code = NULL
1791 WHERE resale_batch_id = p_resale_batch_id
1792 AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
1793
1794 -- UPDATE tracing order lines to processed for this order to be processed
1795 UPDATE ozf_resale_lines_int
1796 SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
1797 WHERE resale_batch_id = p_resale_batch_id
1798 AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1799 AND tracing_flag = 'T';
1800 --
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803 IF OZF_UNEXP_ERROR THEN
1804 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1805 END IF;
1806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1807 END;
1808 */
1809 --
1810 END IF;
1811
1812 -- Debug Message
1813 IF OZF_DEBUG_HIGH_ON THEN
1814 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1815 END IF;
1816
1817 --Standard call to get message count AND IF count=1, get the message
1818 FND_MSG_PUB.Count_And_Get (
1819 p_encoded => FND_API.G_FALSE,
1820 p_count => x_msg_count,
1821 p_data => x_msg_data
1822 );
1823
1824 EXCEPTION
1825 WHEN FND_API.G_EXC_ERROR THEN
1826 ROLLBACK TO Update_Duplicates;
1827 x_return_status := FND_API.G_RET_STS_ERROR;
1828 -- Standard call to get message count AND IF count=1, get the message
1829 FND_MSG_PUB.Count_And_Get (
1830 p_encoded => FND_API.G_FALSE,
1831 p_count => x_msg_count,
1832 p_data => x_msg_data
1833 );
1834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1835 ROLLBACK TO Update_Duplicates;
1836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1837 -- Standard call to get message count AND IF count=1, get the message
1838 FND_MSG_PUB.Count_And_Get (
1839 p_encoded => FND_API.G_FALSE,
1840 p_count => x_msg_count,
1841 p_data => x_msg_data
1842 );
1843 WHEN OTHERS THEN
1844 ROLLBACK TO Update_Duplicates;
1845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1846 IF OZF_UNEXP_ERROR THEN
1847 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1848 END IF;
1849 -- Standard call to get message count AND IF count=1, get the message
1850 FND_MSG_PUB.Count_And_Get (
1851 p_encoded => FND_API.G_FALSE,
1852 p_count => x_msg_count,
1853 p_data => x_msg_data
1854 );
1855 END Update_Duplicates;
1856
1857 ---------------------------------------------------------------------
1858 -- PROCEDURE
1859 -- Validate_Batch
1860 --
1861 -- PURPOSE
1862 -- This procedure validates the batch information
1863 --
1864 -- PARAMETERS
1865 --
1866 --
1867 -- NOTES
1868 ---------------------------------------------------------------------
1869 PROCEDURE Validate_Batch(
1870 p_api_version IN NUMBER
1871 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
1872 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1873 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1874 ,p_resale_batch_id IN NUMBER
1875 ,x_batch_status OUT NOCOPY VARCHAR2
1876 ,x_return_status OUT NOCOPY VARCHAR2
1877 ,x_msg_data OUT NOCOPY VARCHAR2
1878 ,x_msg_count OUT NOCOPY NUMBER
1879 )IS
1880 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Batch';
1881 l_api_version CONSTANT NUMBER := 1.0;
1882 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1883 --
1884 l_return_status VARCHAR2(30);
1885 l_batch_count NUMBER;
1886 l_status_code VARCHAR2(30);
1887 l_report_date date;
1888 l_report_start_date date;
1889 l_report_end_date date;
1890 l_partner_cust_account_id NUMBER;
1891 --
1892 l_int_line_count NUMBER;
1893 l_line_count NUMBER;
1894 l_total_line_count NUMBER := NULL;
1895 l_partner_id_count NUMBER;
1896
1897 CURSOR batch_info_csr IS
1898 SELECT batch_count
1899 , status_code
1900 , report_date
1901 , report_start_date
1902 , report_end_date
1903 , partner_cust_account_id
1904 FROM ozf_resale_batches
1905 WHERE resale_batch_id = p_resale_batch_id;
1906
1907 CURSOR int_line_count_csr IS
1908 SELECT count(1)
1909 FROM ozf_resale_lines_int
1910 WHERE resale_batch_id = p_resale_batch_id;
1911
1912 CURSOR line_count_csr IS
1913 SELECT count(1)
1914 FROM ozf_resale_batch_line_maps
1915 WHERE resale_batch_id = p_resale_batch_id;
1916
1917 CURSOR count_cust_acctid_csr(p_id NUMBER) IS
1918 SELECT 1
1919 FROM dual
1920 WHERE EXISTS (SELECT hca.cust_account_id
1921 FROM hz_cust_accounts hca
1922 WHERE hca.cust_account_id = p_id);
1923 --
1924 BEGIN
1925
1926 -- Standard call to check for call compatibility.
1927 IF NOT FND_API.Compatible_API_Call (
1928 l_api_version,
1929 p_api_version,
1930 l_api_name,
1931 G_PKG_NAME)
1932 THEN
1933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1934 END IF;
1935
1936 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
1937 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
1938 FND_MSG_PUB.initialize;
1939 END IF;
1940
1941 -- Debug Message
1942 IF OZF_DEBUG_HIGH_ON THEN
1943 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1944 END IF;
1945
1946 -- Initialize API return status to sucess
1947 x_return_status := FND_API.G_RET_STS_SUCCESS;
1948
1949 -- First, do some basic check
1950 OPEN batch_info_csr;
1951 FETCH batch_info_csr INTO l_batch_count,
1952 l_status_code,
1953 l_report_date,
1954 l_report_start_date,
1955 l_report_end_date,
1956 l_partner_cust_account_id;
1957 CLOSE batch_info_csr;
1958
1959 IF OZF_DEBUG_LOW_ON THEN
1960 OZF_UTILITY_PVT.debug_message('Report date:' || l_report_date);
1961 OZF_UTILITY_PVT.debug_message('Report Start date:' || l_report_start_date);
1962 OZF_UTILITY_PVT.debug_message('Report End date:' || l_report_end_date);
1963 END IF;
1964
1965 -- Check status
1966 IF l_status_code <> G_BATCH_PROCESSING THEN
1967 --
1968 x_return_status := FND_API.G_RET_STS_ERROR;
1969 --
1970 Insert_Resale_Log (
1971 p_id_value => p_resale_batch_id,
1972 p_id_type => G_ID_TYPE_BATCH,
1973 p_error_code => 'OZF_BATCH_STATUS_WNG',
1974 p_column_name => 'STATUS_CODE',
1975 p_column_value => l_status_code,
1976 x_return_status => l_return_status );
1977 --
1978 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1979 RAISE FND_API.G_EXC_ERROR;
1980 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1982 END IF;
1983 --
1984 END IF;
1985
1986 -- Check report_date
1987 IF l_report_date IS NULL THEN
1988 --
1989 x_return_status := FND_API.G_RET_STS_ERROR;
1990 --
1991 Insert_Resale_Log (
1992 p_id_value => p_resale_batch_id,
1993 p_id_type => G_ID_TYPE_BATCH,
1994 p_error_code => 'OZF_BATCH_REPORT_DATE_NULL',
1995 p_column_name => 'REPORT_date',
1996 p_column_value => NULL,
1997 x_return_status => l_return_status );
1998 --
1999 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2000 RAISE FND_API.G_EXC_ERROR;
2001 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2003 END IF;
2004 --
2005 END IF;
2006
2007 -- Check report_start_date
2008 IF l_report_start_date IS NULL THEN
2009 --
2010 x_return_status := FND_API.G_RET_STS_ERROR;
2011 --
2012 Insert_Resale_Log (
2013 p_id_value => p_resale_batch_id,
2014 p_id_type => G_ID_TYPE_BATCH,
2015 p_error_code => 'OZF_BATCH_REPORT_ST_DATE_NULL',
2016 p_column_name => 'REPORT_START_date',
2017 p_column_value => NULL,
2018 x_return_status => l_return_status );
2019 --
2020 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2021 RAISE FND_API.G_EXC_ERROR;
2022 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024 END IF;
2025 --
2026 END IF;
2027
2028 -- Check report_end_date
2029 IF l_report_end_date IS NULL THEN
2030 --
2031 x_return_status := FND_API.G_RET_STS_ERROR;
2032 --
2033 Insert_Resale_Log (
2034 p_id_value => p_resale_batch_id,
2035 p_id_type => G_ID_TYPE_BATCH,
2036 p_error_code => 'OZF_BATCH_REPORT_END_DATE_NULL',
2037 p_column_name => 'REPORT_END_date',
2038 p_column_value => NULL,
2039 x_return_status => l_return_status );
2040 --
2041 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2042 RAISE FND_API.G_EXC_ERROR;
2043 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2045 END IF;
2046 --
2047 END IF;
2048
2049 --
2050 IF l_report_date IS NOT NULL AND
2051 l_report_start_date IS NOT NULL AND
2052 l_report_end_date IS NOT NULL
2053 THEN
2054 IF l_report_start_date > l_report_end_date THEN
2055
2056 x_return_status := FND_API.G_RET_STS_ERROR;
2057 --
2058 Insert_Resale_Log (
2059 p_id_value => p_resale_batch_id,
2060 p_id_type => G_ID_TYPE_BATCH,
2061 p_error_code => 'OZF_RESALE_WNG_DATE_RANGE',
2062 p_column_name => 'REPORT_START_DATE',
2063 p_column_value => NULL,
2064 x_return_status => l_return_status );
2065 --
2066 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2067 RAISE FND_API.G_EXC_ERROR;
2068 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2070 END IF;
2071 --
2072 END IF;
2073 --
2074 END IF;
2075
2076 -- Check partner_cust_account_id
2077 IF l_partner_cust_account_id IS NULL THEN
2078 --
2079 x_return_status := FND_API.G_RET_STS_ERROR;
2080 --
2081 Insert_Resale_Log (
2082 p_id_value => p_resale_batch_id,
2083 p_id_type => G_ID_TYPE_BATCH,
2084 p_error_code => 'OZF_BATCH_PARTNER_NULL',
2085 p_column_name => 'PARTNER_CUST_ACCOUNT_ID',
2086 p_column_value => NULL,
2087 x_return_status => l_return_status );
2088 --
2089 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2090 RAISE FND_API.G_EXC_ERROR;
2091 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2093 END IF;
2094 --
2095 ELSE
2096 -- make sure partner cust_account_id IS valid
2097 OPEN count_cust_acctid_csr(l_partner_cust_account_id);
2098 FETCH count_cust_acctid_csr INTO l_partner_id_count;
2099 CLOSE count_cust_acctid_csr;
2100 --
2101 IF l_partner_id_count IS NULL THEN
2102 x_return_status := FND_API.G_RET_STS_ERROR;
2103 --
2104 Insert_Resale_Log (
2105 p_id_value => p_resale_batch_id,
2106 p_id_type => G_ID_TYPE_BATCH,
2107 p_error_code => 'OZF_BATCH_PARTNER_ERR',
2108 p_column_name => 'PARTNER_CUST_ACCOUNT_ID',
2109 p_column_value => l_partner_cust_account_id,
2110 x_return_status => l_return_status );
2111 --
2112 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2113 RAISE FND_API.G_EXC_ERROR;
2114 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2116 END IF;
2117 --
2118 END IF;
2119 END IF;
2120
2121 -- check batch count. why IS this required ??
2122 IF l_batch_count IS NULL OR
2123 l_batch_count = 0
2124 THEN
2125 --
2126 x_return_status := FND_API.G_RET_STS_ERROR;
2127 --
2128 Insert_Resale_Log (
2129 p_id_value => p_resale_batch_id,
2130 p_id_type => G_ID_TYPE_BATCH,
2131 p_error_code => 'OZF_BATCH_COUNT_NULL',
2132 p_column_name => 'BATCH_COUNT',
2133 p_column_value => NULL,
2134 x_return_status => l_return_status );
2135 --
2136 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2137 RAISE FND_API.G_EXC_ERROR;
2138 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2140 END IF;
2141 --
2142 END IF;
2143
2144 -- THEN I will check whether the batch_count = line belongs to that batch
2145 OPEN int_line_count_csr;
2146 FETCH int_line_count_csr INTO l_int_line_count;
2147 CLOSE int_line_count_csr;
2148
2149 OPEN line_count_csr;
2150 FETCH line_count_csr INTO l_line_count;
2151 CLOSE line_count_csr;
2152
2153 IF l_int_line_count IS NOT NULL THEN
2154 -- records the total number of lines in interface table for the batch
2155 l_total_line_count := l_int_line_count;
2156 IF l_line_count IS NOT NULL THEN
2157 -- records the total number of lines in int table and map table for the batch
2158 l_total_line_count := l_total_line_count + l_line_count;
2159 END IF;
2160 ELSE
2161 IF l_line_count IS NOT NULL THEN
2162 -- records the total number of lines in map table for the batch
2163 l_total_line_count := l_line_count;
2164 END IF;
2165 END IF;
2166
2167 -- batch without any lines cannot be processed
2168 IF l_total_line_count IS NULL THEN
2169 --
2170 x_return_status := FND_API.G_RET_STS_ERROR;
2171 --
2172 Insert_Resale_Log (
2173 p_id_value => p_resale_batch_id,
2174 p_id_type => G_ID_TYPE_BATCH,
2175 p_error_code => 'OZF_BATCH_LINE_COUNT_ERR',
2176 p_column_name => NULL,
2177 p_column_value => NULL,
2178 x_return_status => l_return_status );
2179 --
2180 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2181 RAISE FND_API.G_EXC_ERROR;
2182 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2184 END IF;
2185 --
2186 ELSE
2187 -- checks if the batch count and actual number of lines in batch are same
2188 IF l_batch_count IS NOT NULL AND
2189 l_batch_count <> l_total_line_count
2190 THEN
2191 --
2192 x_return_status := FND_API.G_RET_STS_ERROR;
2193 --
2194 Insert_Resale_Log (
2195 p_id_value => p_resale_batch_id,
2196 p_id_type => G_ID_TYPE_BATCH,
2197 p_error_code => 'OZF_BATCH_COUNT_ERR',
2198 p_column_name => 'BATCH_COUNT',
2199 p_column_value => l_batch_count,
2200 x_return_status => l_return_status );
2201 --
2202 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2203 RAISE FND_API.G_EXC_ERROR;
2204 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2206 END IF;
2207 --
2208 END IF;
2209 END IF;
2210
2211 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2212 -- SLKRISHN common update
2213 BEGIN
2214 UPDATE ozf_resale_batches
2215 SET status_code= G_BATCH_DISPUTED
2216 WHERE resale_batch_id = p_resale_batch_id;
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 IF OZF_UNEXP_ERROR THEN
2220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2221 END IF;
2222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2223 END;
2224 END IF;
2225
2226 -- Debug Message
2227 IF OZF_DEBUG_HIGH_ON THEN
2228 OZF_UTILITY_PVT.debug_message(l_full_name||': END');
2229 END IF;
2230
2231 --Standard call to get message count AND IF count=1, get the message
2232 FND_MSG_PUB.Count_And_Get (
2233 p_encoded => FND_API.G_FALSE,
2234 p_count => x_msg_count,
2235 p_data => x_msg_data
2236 );
2237 --
2238 EXCEPTION
2239 WHEN FND_API.G_EXC_ERROR THEN
2240 x_return_status := FND_API.G_RET_STS_ERROR;
2241 -- Standard call to get message count AND IF count=1, get the message
2242 FND_MSG_PUB.Count_And_Get (
2243 p_encoded => FND_API.G_FALSE,
2244 p_count => x_msg_count,
2245 p_data => x_msg_data
2246 );
2247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2249 -- Standard call to get message count AND IF count=1, get the message
2250 FND_MSG_PUB.Count_And_Get (
2251 p_encoded => FND_API.G_FALSE,
2252 p_count => x_msg_count,
2253 p_data => x_msg_data
2254 );
2255 WHEN OTHERS THEN
2256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2257 IF OZF_UNEXP_ERROR THEN
2258 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2259 END IF;
2260 -- Standard call to get message count AND IF count=1, get the message
2261 FND_MSG_PUB.Count_And_Get (
2262 p_encoded => FND_API.G_FALSE,
2263 p_count => x_msg_count,
2264 p_data => x_msg_data
2265 );
2266 END Validate_Batch;
2267
2268 ---------------------------------------------------------------------
2269 -- PROCEDURE
2270 -- Validate_Order_Record
2271 --
2272 -- PURPOSE
2273 -- This procedure validates the order information
2274 --
2275 -- PARAMETERS
2276 --
2277 --
2278 -- NOTES
2279 ---------------------------------------------------------------------
2280 PROCEDURE Validate_Order_Record(
2281 p_api_version IN NUMBER
2282 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
2283 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2284 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2285 ,p_resale_batch_id IN NUMBER
2286 ,x_return_status OUT NOCOPY VARCHAR2
2287 ,x_msg_data OUT NOCOPY VARCHAR2
2288 ,x_msg_count OUT NOCOPY NUMBER
2289 )IS
2290 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
2291 l_api_version CONSTANT NUMBER := 1.0;
2292 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2293 --
2294 l_return_status varchar2(1);
2295 BEGIN
2296
2297 -- Standard call to check for call compatibility.
2298 IF NOT FND_API.Compatible_API_Call (
2299 l_api_version,
2300 p_api_version,
2301 l_api_name,
2302 G_PKG_NAME)
2303 THEN
2304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2305 END IF;
2306
2307 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
2308 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
2309 FND_MSG_PUB.initialize;
2310 END IF;
2311
2312 -- Debug Message
2313 IF OZF_DEBUG_HIGH_ON THEN
2314 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
2315 END IF;
2316
2317 -- Initialize API return status to sucess
2318 x_return_status := FND_API.G_RET_STS_SUCCESS;
2319
2320 -- Log lines with null values when required
2321 Log_Null_Values (
2322 p_batch_id =>p_resale_batch_id,
2323 x_return_status => l_return_status);
2324 --
2325 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2326 RAISE FND_API.G_EXC_ERROR;
2327 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2329 END IF;
2330 --
2331
2332 -- Log lines with invalid values
2333 Log_Invalid_Values (
2334 p_batch_id =>p_resale_batch_id,
2335 x_return_status => l_return_status);
2336 --
2337 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2338 RAISE FND_API.G_EXC_ERROR;
2339 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2341 END IF;
2342 --
2343
2344 Bulk_Dispute_Line (
2345 p_batch_id => p_resale_batch_id,
2346 p_line_status => G_BATCH_ADJ_OPEN,
2347 x_return_status => l_return_status
2348 );
2349 --
2350 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2351 RAISE FND_API.G_EXC_ERROR;
2352 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354 END IF;
2355 --
2356
2357 -- Debug Message
2358 IF OZF_DEBUG_HIGH_ON THEN
2359 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
2360 END IF;
2361
2362 --Standard call to get message count AND IF count=1, get the message
2363 FND_MSG_PUB.Count_And_Get (
2364 p_encoded => FND_API.G_FALSE,
2365 p_count => x_msg_count,
2366 p_data => x_msg_data
2367 );
2368 EXCEPTION
2369 WHEN FND_API.G_EXC_ERROR THEN
2370 x_return_status := FND_API.G_RET_STS_ERROR;
2371 -- Standard call to get message count AND IF count=1, get the message
2372 FND_MSG_PUB.Count_And_Get (
2373 p_encoded => FND_API.G_FALSE,
2374 p_count => x_msg_count,
2375 p_data => x_msg_data
2376 );
2377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2379 -- Standard call to get message count AND IF count=1, get the message
2380 FND_MSG_PUB.Count_And_Get (
2381 p_encoded => FND_API.G_FALSE,
2382 p_count => x_msg_count,
2383 p_data => x_msg_data
2384 );
2385 WHEN OTHERS THEN
2386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2387 IF OZF_UNEXP_ERROR THEN
2388 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2389 END IF;
2390 -- Standard call to get message count AND IF count=1, get the message
2391 FND_MSG_PUB.Count_And_Get (
2392 p_encoded => FND_API.G_FALSE,
2393 p_count => x_msg_count,
2394 p_data => x_msg_data
2395 );
2396 END Validate_Order_Record;
2397
2398 ---------------------------------------------------------------------
2399 -- PROCEDURE
2400 -- Create_Utilization_record
2401 --
2402 -- PURPOSE
2403 -- ThIS procedure prepare the record FOR utilization
2404 --
2405 -- PARAMETERS
2406 --
2407 --
2408 -- NOTES
2409 ---------------------------------------------------------------------
2410 PROCEDURE Create_Utilization_record(
2411 p_line_int_rec IN g_interface_rec_csr%ROWTYPE
2412 ,p_batch_type IN VARCHAR2
2413 ,p_fund_id IN NUMBER
2414 ,p_line_id IN NUMBER
2415 ,p_cust_account_id IN NUMBER
2416 ,p_approver_id IN NUMBER
2417 ,p_line_agreement_flag IN VARCHAR2
2418 ,p_utilization_type IN VARCHAR2
2419 ,p_adjustment_type_id IN NUMBER
2420 ,p_budget_source_type IN VARCHAR2
2421 ,p_budget_source_id IN NUMBER
2422 ,p_justification IN VARCHAR2
2423 ,p_to_create_utilization IN BOOLEAN
2424 ,x_return_status OUT NOCOPY VARCHAR2
2425 )
2426 IS
2427 l_api_name CONSTANT VARCHAR2(30) := 'Create_Utilization_Rec';
2428 l_api_version CONSTANT NUMBER := 1.0;
2429 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2430 --
2431 l_return_status VARCHAR2(30);
2432 l_msg_data VARCHAR2(2000);
2433 l_msg_count NUMBER;
2434
2435 l_pric_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
2436 l_pric_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
2437 l_pric_price_adj_rec ozf_resale_adjustments_all%ROWTYPE;
2438
2439 l_adjustment_id NUMBER;
2440 l_rate NUMBER;
2441 l_exchange_type VARCHAR2(30);
2442 BEGIN
2443 -- Standard begin of API savepoint
2444 SAVEPOINT IDSM_Create_Utiz_Rec;
2445 x_return_status := FND_API.G_RET_STS_SUCCESS;
2446
2447 --
2448 IF OZF_DEBUG_HIGH_ON THEN
2449 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
2450 END IF;
2451
2452 -- Create act Utilization Record.
2453 l_pric_act_util_rec.object_type := 'TP_ORDER';
2454 l_pric_act_util_rec.object_id := p_line_id;
2455 l_pric_act_util_rec.product_level_type := 'PRODUCT';
2456 l_pric_act_util_rec.product_id := p_line_int_rec.inventory_item_Id;
2457
2458 -- Pass partner account id for this
2459 l_pric_act_util_rec.billto_cust_account_id := p_cust_account_id;
2460 l_pric_act_util_rec.utilization_type := p_utilization_type;
2461 l_pric_act_util_rec.adjustment_type_id := p_adjustment_type_id;
2462
2463 -- Reference for batch
2464 l_pric_act_util_rec.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
2465 l_pric_act_util_rec.reference_id := p_line_int_rec.resale_batch_id;
2466
2467 -- Add gl_date
2468 l_pric_act_util_rec.gl_date := p_line_int_rec.date_shipped;
2469
2470 -- Add org_id
2471 l_pric_act_util_rec.org_id := p_line_int_rec.org_id;
2472
2473 -- Create Budget Record.
2474 l_pric_act_budgets_rec.parent_source_id := p_fund_id;
2475 l_pric_act_budgets_rec.arc_act_budget_used_by := p_budget_source_type;
2476 l_pric_act_budgets_rec.act_budget_used_by_id := p_budget_source_id;
2477 l_pric_act_budgets_rec.budget_source_type := p_budget_source_type;
2478 l_pric_act_budgets_rec.budget_source_id := p_budget_source_id;
2479 l_pric_act_budgets_rec.status_code := 'APPROVED';--l_utilization_rec.status_code;
2480
2481 -- get request amount in budget currency
2482 l_pric_act_budgets_rec.request_currency := p_line_int_rec.currency_code;
2483 l_pric_act_budgets_rec.request_amount := p_line_int_rec.total_accepted_amount;
2484
2485 IF OZF_DEBUG_LOW_ON THEN
2486 OZF_UTILITY_PVT.debug_message('request currency'|| l_pric_act_budgets_rec.request_currency);
2487 OZF_UTILITY_PVT.debug_message('request amount: '||l_pric_act_budgets_rec.request_amount);
2488 END IF;
2489
2490 IF p_batch_type <> G_SPECIAL_PRICING THEN
2491 l_pric_act_budgets_rec.parent_src_curr :=
2492 OZF_ACTBUDGETS_PVT.Get_Object_Currency (
2493 'FUND'
2494 ,l_pric_act_budgets_rec.parent_source_id
2495 ,l_return_status
2496 );
2497 IF l_return_status = FND_API.g_ret_sts_error THEN
2498 RAISE FND_API.g_exc_error;
2499 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2500 RAISE FND_API.g_exc_unexpected_error;
2501 END IF;
2502
2503 l_pric_act_budgets_rec.parent_src_apprvd_amt := p_line_int_rec.total_accepted_amount;
2504
2505 IF p_line_int_rec.currency_code <> l_pric_act_budgets_rec.parent_src_curr THEN
2506 IF OZF_DEBUG_LOW_ON THEN
2507 OZF_UTILITY_PVT.debug_message('in exchange');
2508 END IF;
2509 -- get convertion type
2510 OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2511 FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2512 CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2513
2514 OZF_UTILITY_PVT.Convert_Currency (
2515 p_from_currency => p_line_int_rec.currency_code
2516 ,p_to_currency => l_pric_act_budgets_rec.parent_src_curr
2517 ,p_conv_type => l_exchange_type
2518 ,p_conv_rate => FND_API.G_MISS_NUM
2519 ,p_conv_date => sysdate
2520 ,p_from_amount => p_line_int_rec.total_accepted_amount
2521 ,x_return_status => l_return_status
2522 ,x_to_amount => l_pric_act_budgets_rec.parent_src_apprvd_amt
2523 ,x_rate => l_rate);
2524
2525 IF l_return_status = FND_API.g_ret_sts_error THEN
2526 RAISE FND_API.g_exc_error;
2527 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2528 RAISE FND_API.g_exc_unexpected_error;
2529 END IF;
2530 --
2531 END IF;
2532
2533 END IF;
2534
2535 IF OZF_DEBUG_LOW_ON THEN
2536 OZF_UTILITY_PVT.debug_message('act currency: '||p_line_int_rec.currency_code);
2537 OZF_UTILITY_PVT.debug_message('par src: '||l_pric_act_budgets_rec.parent_src_curr);
2538 OZF_UTILITY_PVT.debug_message('approve amount: '||l_pric_act_budgets_rec.parent_src_apprvd_amt);
2539 END IF;
2540
2541 l_pric_act_budgets_rec.transfer_type := 'UTILIZED';
2542 l_pric_act_budgets_rec.justification := p_justification;
2543
2544 -- Add approver_id AND requester_id
2545 l_pric_act_budgets_rec.approver_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2546 l_pric_act_budgets_rec.requester_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2547
2548 -- Insert INTO ozf_adjustment TABLE.
2549 OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2550 FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2551 CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2552
2553 l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2554 l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2555 l_pric_price_adj_rec.resale_line_id := p_line_id;
2556 l_pric_price_adj_rec.status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED;
2557 l_pric_price_adj_rec.orig_system_agreement_uom := p_line_int_rec.orig_system_agreement_uom;
2558 l_pric_price_adj_rec.orig_system_agreement_name := p_line_int_rec.orig_system_agreement_name;
2559 l_pric_price_adj_rec.orig_system_agreement_type := p_line_int_rec.orig_system_agreement_type;
2560 l_pric_price_adj_rec.orig_system_agreement_status := p_line_int_rec.orig_system_agreement_status;
2561 l_pric_price_adj_rec.orig_system_agreement_curr := p_line_int_rec.orig_system_agreement_curr;
2562 l_pric_price_adj_rec.orig_system_agreement_price := p_line_int_rec.orig_system_agreement_price;
2563 l_pric_price_adj_rec.orig_system_agreement_quantity := p_line_int_rec.orig_system_agreement_quantity;
2564 l_pric_price_adj_rec.agreement_id := p_line_int_rec.agreement_id;
2565 l_pric_price_adj_rec.agreement_type := p_line_int_rec.agreement_type;
2566 l_pric_price_adj_rec.agreement_name := p_line_int_rec.agreement_name;
2567 l_pric_price_adj_rec.agreement_price := p_line_int_rec.agreement_price;
2568 l_pric_price_adj_rec.agreement_uom_code := p_line_int_rec.agreement_uom_code;
2569 l_pric_price_adj_rec.corrected_agreement_id := p_line_int_rec.corrected_agreement_id;
2570 l_pric_price_adj_rec.corrected_agreement_name := p_line_int_rec.corrected_agreement_name;
2571 l_pric_price_adj_rec.credit_code := p_line_int_rec.credit_code;
2572 l_pric_price_adj_rec.credit_advice_date := p_line_int_rec.credit_advice_date;
2573 l_pric_price_adj_rec.claimed_amount := p_line_int_rec.claimed_amount;
2574 l_pric_price_adj_rec.total_claimed_amount := p_line_int_rec.total_claimed_amount;
2575 l_pric_price_adj_rec.allowed_amount := p_line_int_rec.allowed_amount;
2576 l_pric_price_adj_rec.total_allowed_amount := p_line_int_rec.total_allowed_amount;
2577 l_pric_price_adj_rec.accepted_amount := p_line_int_rec.accepted_amount;
2578 l_pric_price_adj_rec.total_accepted_amount := p_line_int_rec.total_accepted_amount;
2579 l_pric_price_adj_rec.calculated_price := p_line_int_rec.calculated_price;
2580 l_pric_price_adj_rec.acctd_calculated_price := p_line_int_rec.acctd_calculated_price;
2581 l_pric_price_adj_rec.calculated_amount := p_line_int_rec.calculated_amount;
2582 l_pric_price_adj_rec.line_agreement_flag := p_line_agreement_flag;
2583 l_pric_price_adj_rec.tolerance_flag := p_line_int_rec.tolerance_flag;
2584 l_pric_price_adj_rec.line_tolerance_amount := p_line_int_rec.line_tolerance_amount;
2585 l_pric_price_adj_rec.operand := NULL;
2586 l_pric_price_adj_rec.operand_calculation_code := NULL;
2587 l_pric_price_adj_rec.priced_quantity := p_line_int_rec.quantity;
2588 l_pric_price_adj_rec.priced_uom_code := p_line_int_rec.uom_code;
2589 l_pric_price_adj_rec.priced_unit_price := p_line_int_rec.calculated_price;
2590 l_pric_price_adj_rec.liSt_header_id := NULL;
2591 l_pric_price_adj_rec.liSt_line_id := NULL;
2592
2593 OZF_RESALE_COMMON_PVT.Create_Adj_And_Utilization(
2594 p_api_version => 1
2595 ,p_init_msg_list => FND_API.G_FALSE
2596 ,p_commit => FND_API.G_FALSE
2597 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
2598 ,p_price_adj_rec => l_pric_price_adj_rec
2599 ,p_act_budgets_rec => l_pric_act_budgets_rec
2600 ,p_act_util_rec => l_pric_act_util_rec
2601 ,p_to_create_utilization => p_to_create_utilization
2602 ,x_return_status => l_return_status
2603 ,x_msg_data => l_msg_data
2604 ,x_msg_count => l_msg_count
2605 );
2606 IF l_return_status = FND_API.g_ret_sts_error THEN
2607 RAISE FND_API.g_exc_error;
2608 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2609 RAISE FND_API.g_exc_unexpected_error;
2610 END IF;
2611
2612 -- update the interface duplicated line
2613 UPDATE ozf_resale_lines_int_all
2614 SET duplicated_line_id = p_line_id
2615 , duplicated_adjustment_id =l_adjustment_id
2616 WHERE duplicated_line_id = p_line_int_rec.resale_line_int_id
2617 AND duplicated_adjustment_id = -1;
2618
2619 IF OZF_DEBUG_HIGH_ON THEN
2620 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
2621 END IF;
2622
2623 EXCEPTION
2624 WHEN FND_API.g_exc_error THEN
2625 ROLLBACK TO IDSM_Create_Utiz_Rec;
2626 x_return_status := FND_API.G_RET_STS_ERROR;
2627 WHEN FND_API.g_exc_unexpected_error THEN
2628 ROLLBACK TO IDSM_Create_Utiz_Rec;
2629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630 WHEN OTHERS THEN
2631 ROLLBACK TO IDSM_Create_Utiz_Rec;
2632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2633 IF OZF_UNEXP_ERROR THEN
2634 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2635 END IF;
2636 END Create_Utilization_record;
2637
2638 ---------------------------------------------------------------------
2639 -- PROCEDURE
2640 -- Create_Adj_And_Utilization
2641 --
2642 -- PURPOSE
2643 -- This function receives the price adjustment rec AND utilization record
2644 -- It them inserts the price adjustmetns AND utilization
2645 --
2646 -- PARAMETERS
2647 --
2648 -- p_adj_rec IN ozf_chargeback_price_adj_all%rowtype
2649 -- p_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type
2650 -- p_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
2651 --
2652 --
2653 -- NOTES
2654 ---------------------------------------------------------------------
2655 PROCEDURE Create_Adj_And_Utilization(
2656 p_api_version IN NUMBER
2657 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
2658 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2659 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2660 ,p_price_adj_rec IN ozf_resale_adjustments_all%rowtype
2661 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
2662 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
2663 ,p_to_create_utilization IN BOOLEAN
2664 ,x_return_status OUT NOCOPY VARCHAR2
2665 ,x_msg_data OUT NOCOPY VARCHAR2
2666 ,x_msg_count OUT NOCOPY NUMBER
2667 )IS
2668 l_api_name CONSTANT VARCHAR2(30) := 'Create_Adj_and_Utilization';
2669 l_api_version CONSTANT NUMBER := 1.0;
2670 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2671 --
2672 l_act_budget_id NUMBER;
2673 l_msg_data VARCHAR2(2000);
2674 l_msg_count NUMBER;
2675 l_return_status VARCHAR2(30);
2676
2677 l_adjustment_id NUMBER:= p_price_adj_rec.resale_adjustment_id;
2678
2679 l_obj_ver_num NUMBER := 1;
2680 l_org_id NUMBER;
2681
2682 l_utilized_amount NUMBER;
2683 BEGIN
2684 -- Standard BEGIN of API savepoint
2685 SAVEPOINT Create_Adj_And_Utilization;
2686 -- Standard call to check for call compatibility.
2687 IF NOT FND_API.Compatible_API_Call (
2688 l_api_version,
2689 p_api_version,
2690 l_api_name,
2691 G_PKG_NAME)
2692 THEN
2693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2694 END IF;
2695
2696 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
2697 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
2698 FND_MSG_PUB.initialize;
2699 END IF;
2700
2701 -- Debug Message
2702 IF OZF_DEBUG_HIGH_ON THEN
2703 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
2704 END IF;
2705
2706 -- Initialize API return status to sucess
2707 x_return_status := FND_API.G_RET_STS_SUCCESS;
2708
2709 IF OZF_DEBUG_LOW_ON THEN
2710 OZF_UTILITY_PVT.debug_message('adj_id:' || p_price_adj_rec.resale_adjustment_id ||'line_id:' || p_price_adj_rec.resale_line_id);
2711 IF p_to_create_utilization THEN
2712 OZF_UTILITY_PVT.debug_message('create utilization:T');
2713 ELSE
2714 OZF_UTILITY_PVT.debug_message('create utilization:F');
2715 END IF;
2716 END IF;
2717
2718 -- get price_adj_id
2719 IF l_adjustment_id IS NULL THEN
2720 OPEN g_adjustment_id_csr;
2721 FETCH g_adjustment_id_csr INTO l_adjustment_id;
2722 CLOSE g_adjustment_id_csr;
2723 END IF;
2724
2725 IF p_price_adj_rec.org_id IS NOT NULL THEN
2726 l_org_id := p_price_adj_rec.org_id;
2727 ELSE
2728 l_org_id := MO_GLOBAL.get_current_org_id();
2729 END IF;
2730
2731 -- We only need to record for every price adjustment information
2732 BEGIN
2733 OZF_RESALE_ADJUSTMENTS_PKG.Insert_Row(
2734 px_resale_adjustment_id => l_adjustment_id,
2735 px_object_version_number => l_obj_ver_num,
2736 p_last_update_date => sysdate,
2737 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
2738 p_creation_date => sysdate,
2739 p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
2740 p_created_by => NVL(FND_GLOBAL.user_id,-1),
2741 p_created_from => p_price_adj_rec.created_from,
2742 p_last_update_login => NVL(FND_GLOBAL.conc_login_id,-1),
2743 p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
2744 p_program_update_date => sysdate,
2745 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
2746 p_resale_line_id => p_price_adj_rec.resale_line_id,
2747 p_resale_batch_id => p_price_adj_rec.resale_batch_id,
2748 p_orig_system_agreement_uom => p_price_adj_rec.orig_system_agreement_uom,
2749 p_orig_system_agreement_name => p_price_adj_rec.orig_system_agreement_name,
2750 p_orig_system_agreement_type => p_price_adj_rec.orig_system_agreement_type,
2751 p_orig_system_agreement_status=> p_price_adj_rec.orig_system_agreement_status,
2752 p_orig_system_agreement_curr => p_price_adj_rec.orig_system_agreement_curr,
2753 p_orig_system_agreement_price => p_price_adj_rec.orig_system_agreement_price,
2754 p_orig_system_agreement_quant => p_price_adj_rec.orig_system_agreement_quantity,
2755 p_agreement_id => p_price_adj_rec.agreement_id ,
2756 p_agreement_type => p_price_adj_rec.agreement_type ,
2757 p_agreement_name => p_price_adj_rec.agreement_name ,
2758 p_agreement_price => p_price_adj_rec.agreement_price ,
2759 p_agreement_uom_code => p_price_adj_rec.agreement_uom_code,
2760 p_corrected_agreement_id => p_price_adj_rec.corrected_agreement_id ,
2761 p_corrected_agreement_name => p_price_adj_rec.corrected_agreement_name ,
2762 p_credit_code => p_price_adj_rec.credit_code,
2763 p_credit_advice_date => p_price_adj_rec.credit_advice_date,
2764 p_total_allowed_amount => p_price_adj_rec.total_allowed_amount,
2765 p_allowed_amount => p_price_adj_rec.allowed_amount,
2766 p_total_accepted_amount => p_price_adj_rec.total_accepted_amount,
2767 p_accepted_amount => p_price_adj_rec.accepted_amount,
2768 p_total_claimed_amount => p_price_adj_rec.total_claimed_amount,
2769 p_claimed_amount => p_price_adj_rec.claimed_amount,
2770 p_calculated_price => p_price_adj_rec.calculated_price,
2771 p_acctd_calculated_price => p_price_adj_rec.acctd_calculated_price,
2772 p_calculated_amount => p_price_adj_rec.calculated_amount,
2773 p_line_agreement_flag => p_price_adj_rec.line_agreement_flag,
2774 p_tolerance_flag => p_price_adj_rec.tolerance_flag,
2775 p_line_tolerance_amount => p_price_adj_rec.line_tolerance_amount,
2776 p_operand => p_price_adj_rec.operand,
2777 p_operand_calculation_code => p_price_adj_rec.operand_calculation_code,
2778 p_priced_quantity => p_price_adj_rec.priced_quantity,
2779 p_priced_uom_code => p_price_adj_rec.priced_uom_code,
2780 p_priced_unit_price => p_price_adj_rec.priced_unit_price,
2781 p_list_header_id => p_price_adj_rec.list_header_id,
2782 p_list_line_id => p_price_adj_rec.list_line_id,
2783 p_status_code => 'CLOSED',
2784 px_org_id => l_org_id
2785 );
2786 --
2787 EXCEPTION
2788 WHEN OTHERS THEN
2789 IF OZF_UNEXP_ERROR THEN
2790 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2791 END IF;
2792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2793 END;
2794
2795 -- Create accrual only when its required
2796 IF p_to_create_utilization THEN
2797 IF OZF_DEBUG_LOW_ON THEN
2798 ozf_utility_pvt.debug_message('p_act_util_rec.object_type = '|| p_act_util_rec.object_type);
2799 ozf_utility_pvt.debug_message('p_act_util_rec.object_id = '|| p_act_util_rec.object_id);
2800 ozf_utility_pvt.debug_message('p_act_util_rec.product_level_type = '|| p_act_util_rec.product_level_type);
2801 ozf_utility_pvt.debug_message('p_act_util_rec.product_id = '|| p_act_util_rec.product_id);
2802 ozf_utility_pvt.debug_message('p_act_util_rec.billto_cust_account_id = '|| p_act_util_rec.billto_cust_account_id);
2803 ozf_utility_pvt.debug_message('p_act_util_rec.gl_date = '|| p_act_util_rec.gl_date);
2804 ozf_utility_pvt.debug_message('p_act_util_rec.reference_type = '|| p_act_util_rec.reference_type);
2805 ozf_utility_pvt.debug_message('p_act_util_rec.reference_id = '|| p_act_util_rec.reference_id);
2806 ozf_utility_pvt.debug_message('p_act_util_rec.price_adjustment_id = '|| p_act_util_rec.price_adjustment_id);
2807 ozf_utility_pvt.debug_message('p_act_util_rec.utilization_type = '|| p_act_util_rec.utilization_type);
2808 ozf_utility_pvt.debug_message('p_act_util_rec.adjustment_type_id = '|| p_act_util_rec.adjustment_type_id);
2809 ozf_utility_pvt.debug_message('p_act_util_rec.org_id = '|| p_act_util_rec.org_id);
2810
2811 ozf_utility_pvt.debug_message('p_act_budgets_rec.act_budget_used_by_id = '|| p_act_budgets_rec.act_budget_used_by_id);
2812 ozf_utility_pvt.debug_message('p_act_budgets_rec.budget_source_id = '|| p_act_budgets_rec.budget_source_id);
2813 ozf_utility_pvt.debug_message('p_act_budgets_rec.status_code = '|| p_act_budgets_rec.status_code);
2814 ozf_utility_pvt.debug_message('p_act_budgets_rec.transfer_type = '|| p_act_budgets_rec.transfer_type);
2815 ozf_utility_pvt.debug_message('p_act_budgets_rec.arc_act_budget_used_by= '|| p_act_budgets_rec.arc_act_budget_used_by);
2816 ozf_utility_pvt.debug_message('p_act_budgets_rec.budget_source_type = '|| p_act_budgets_rec.budget_source_type);
2817 ozf_utility_pvt.debug_message('p_act_budgets_rec.approver_id = '|| p_act_budgets_rec.approver_id);
2818 ozf_utility_pvt.debug_message('p_act_budgets_rec.requester_id = '|| p_act_budgets_rec.requester_id);
2819 ozf_utility_pvt.debug_message('p_act_budgets_rec.request_currency = '|| p_act_budgets_rec.request_currency);
2820 ozf_utility_pvt.debug_message('p_act_budgets_rec.parent_source_id = '|| p_act_budgets_rec.parent_source_id);
2821 ozf_utility_pvt.debug_message('p_act_budgets_rec.parent_src_curr = '|| p_act_budgets_rec.parent_src_curr);
2822 ozf_utility_pvt.debug_message('p_act_budgets_rec.justification = '|| p_act_budgets_rec.justification);
2823 ozf_utility_pvt.debug_message('p_act_budgets_rec.arc_act_budget_used_by= '|| p_act_budgets_rec.arc_act_budget_used_by);
2824 ozf_utility_pvt.debug_message('p_act_budgets_rec.budget_source_type = '|| p_act_budgets_rec.budget_source_type);
2825 ozf_utility_pvt.debug_message('p_act_budgets_rec.request_currency = '|| p_act_budgets_rec.request_currency);
2826 ozf_utility_pvt.debug_message('p_act_budgets_rec.request_amount = '|| p_act_budgets_rec.request_amount);
2827 ozf_utility_pvt.debug_message('p_act_budgets_rec.parent_src_apprvd_amt = '|| p_act_budgets_rec.parent_src_apprvd_amt);
2828 END IF;
2829
2830 BEGIN
2831 --
2832 OZF_FUND_ADJUSTMENT_PVT.Process_Act_Budgets (
2833 x_return_status => l_return_status,
2834 x_msg_count => l_msg_count,
2835 x_msg_data => l_msg_data,
2836 p_act_budgets_rec => p_act_budgets_rec,
2837 p_act_util_rec => p_act_util_rec,
2838 x_act_budget_id => l_act_budget_id,
2839 x_utilized_amount => l_utilized_amount
2840 );
2841 IF OZF_DEBUG_LOW_ON THEN
2842 OZF_UTILITY_PVT.debug_message('OZF_FUND_ADJUSTMENT_PVT.Process_Act_Budgets return result: '||l_return_status);
2843 OZF_UTILITY_PVT.debug_message('post to budget: budget_source_id:' || p_act_budgets_rec.budget_source_id);
2844 OZF_UTILITY_PVT.debug_message('post to budget: amount:' || p_act_budgets_rec.request_amount);
2845 OZF_UTILITY_PVT.debug_message('x_utilized_amount: '||l_utilized_amount);
2846 END IF;
2847
2848 IF l_return_status = FND_API.g_ret_sts_error THEN
2849 RAISE FND_API.g_exc_error;
2850 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2851 RAISE FND_API.g_exc_unexpected_error;
2852 END IF;
2853 -- bug 5391758,5216124
2854 IF l_utilized_amount = 0 AND p_act_budgets_rec.request_amount <> 0 THEN
2855 ozf_utility_pvt.error_message ( 'OZF_COMMAMT_LESS_REQAMT');
2856 RAISE fnd_api.g_exc_error;
2857 END IF;
2858
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 IF OZF_UNEXP_ERROR THEN
2862 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2863 END IF;
2864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2865 END;
2866 END IF;
2867
2868 -- Debug Message
2869 IF OZF_DEBUG_HIGH_ON THEN
2870 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
2871 END IF;
2872
2873 --Standard call to get message count AND IF count=1, get the message
2874 FND_MSG_PUB.Count_And_Get (
2875 p_encoded => FND_API.G_FALSE,
2876 p_count => x_msg_count,
2877 p_data => x_msg_data
2878 );
2879 EXCEPTION
2880 WHEN FND_API.G_EXC_ERROR THEN
2881 ROLLBACK TO Create_Adj_And_Utilization;
2882 x_return_status := FND_API.G_RET_STS_ERROR;
2883 -- Standard call to get message count AND IF count=1, get the message
2884 FND_MSG_PUB.Count_And_Get (
2885 p_encoded => FND_API.G_FALSE,
2886 p_count => x_msg_count,
2887 p_data => x_msg_data
2888 );
2889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2890 ROLLBACK TO Create_Adj_And_Utilization;
2891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2892 -- Standard call to get message count AND IF count=1, get the message
2893 FND_MSG_PUB.Count_And_Get (
2894 p_encoded => FND_API.G_FALSE,
2895 p_count => x_msg_count,
2896 p_data => x_msg_data
2897 );
2898 WHEN OTHERS THEN
2899 ROLLBACK TO Create_Adj_And_Utilization;
2900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2901 IF OZF_UNEXP_ERROR THEN
2902 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2903 END IF;
2904 -- Standard call to get message count AND IF count=1, get the message
2905 FND_MSG_PUB.Count_And_Get (
2906 p_encoded => FND_API.G_FALSE,
2907 p_count => x_msg_count,
2908 p_data => x_msg_data
2909 );
2910 END Create_Adj_And_Utilization;
2911
2912 ---------------------------------------------------------------------
2913 -- PROCEDURE
2914 -- Create_Utilization
2915 --
2916 -- PURPOSE
2917 -- This procedure creates utilization
2918 --
2919 -- PARAMETERS
2920 --
2921 --
2922 -- NOTES
2923 ---------------------------------------------------------------------
2924 PROCEDURE Create_Utilization(
2925 p_api_version IN NUMBER
2926 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
2927 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2928 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2929 ,p_line_int_rec IN g_interface_rec_csr%ROWTYPE
2930 ,p_fund_id IN NUMBER
2931 ,p_line_id IN NUMBER
2932 ,p_cust_account_id IN NUMBER
2933 ,p_approver_id IN NUMBER
2934 ,x_return_status OUT NOCOPY VARCHAR2
2935 ,x_msg_data OUT NOCOPY VARCHAR2
2936 ,x_msg_count OUT NOCOPY NUMBER
2937 )
2938 IS
2939 l_api_name CONSTANT VARCHAR2(30) := 'Create_Utilization';
2940 l_api_version CONSTANT NUMBER := 1.0;
2941 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2942 --
2943 l_return_status VARCHAR2(30);
2944 l_msg_data VARCHAR2(2000);
2945 l_msg_count NUMBER;
2946 --
2947 l_dup_adjustment_id NUMBER;
2948 l_dup_total_accepted_amount NUMBER;
2949 l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE := p_line_int_rec;
2950
2951 CURSOR dup_adj_csr(p_line_id in NUMBER, p_batch_type in VARCHAR2) IS
2952 SELECT a.resale_adjustment_id,
2953 a.total_accepted_amount
2954 FROM ozf_resale_adjustments a,
2955 ozf_resale_batches b,
2956 ozf_resale_lines c
2957 WHERE a.resale_line_id = p_line_id
2958 AND a.resale_batch_id = b.resale_batch_id
2959 AND b.batch_type = p_batch_type
2960 AND b.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
2961 AND c.resale_line_id = a.resale_line_id
2962 AND c.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
2963 -- BUG 4670154 (+)
2964 AND a.list_header_id IS NULL
2965 AND a.list_line_id IS NULL;
2966 -- BUG 4670154 (-)
2967
2968 CURSOR dup_adj_rec_csr(p_adj_id NUMBER) IS
2969 SELECT *
2970 FROM ozf_resale_adjustments
2971 WHERE resale_adjustment_id = p_adj_id;
2972
2973 l_dup_adj_rec dup_adj_rec_csr%ROWTYPE;
2974
2975 l_batch_type VARCHAR2(30);
2976 l_utilization_type VARCHAR2(30);
2977 l_adjustment_type_id NUMBER := NULL;
2978 l_budget_source_type VARCHAR2(30);
2979 l_justification VARCHAR2(30);
2980 l_to_create_utilization BOOLEAN;
2981 l_budget_source_id NUMBER;
2982 --
2983 BEGIN
2984 -- Standard BEGIN of API savepoint
2985 SAVEPOINT IDSM_Create_Utilization;
2986 -- Standard call to check for call compatibility.
2987 IF NOT FND_API.Compatible_API_Call (
2988 l_api_version,
2989 p_api_version,
2990 l_api_name,
2991 G_PKG_NAME)
2992 THEN
2993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2994 END IF;
2995
2996 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
2997 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
2998 FND_MSG_PUB.initialize;
2999 END IF;
3000
3001 -- Debug Message
3002 IF OZF_DEBUG_HIGH_ON THEN
3003 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
3004 END IF;
3005
3006 -- Initialize API return status to sucess
3007 x_return_status := FND_API.G_RET_STS_SUCCESS;
3008
3009 IF OZF_DEBUG_LOW_ON THEN
3010 OZF_UTILITY_PVT.debug_message('IN create_utilization');
3011 END IF;
3012
3013 OPEN g_batch_type_csr(l_line_int_rec.resale_batch_id);
3014 FETCH g_batch_type_csr into l_batch_type;
3015 CLOSE g_batch_type_csr;
3016
3017 IF OZF_DEBUG_LOW_ON THEN
3018 OZF_UTILITY_PVT.debug_message('batch type' ||l_batch_type);
3019 END IF;
3020
3021 IF l_batch_type = G_CHARGEBACK THEN
3022 l_utilization_type:= G_CHBK_UTIL_TYPE;
3023 l_adjustment_type_id := G_CHBK_ADJ_TYPE_id;
3024 l_budget_source_type := 'PRIC';
3025 l_justification := 'CHARGEBACK';
3026 l_to_create_utilization := true;
3027 IF l_line_int_rec.corrected_agreement_id IS NOT NULL THEN
3028 l_budget_source_id := l_line_int_rec.corrected_agreement_id;
3029 ELSE
3030 l_budget_source_id := l_line_int_rec.agreement_id;
3031 END IF;
3032
3033 ELSIF l_batch_type = G_SPECIAL_PRICING THEN
3034 IF OZF_DEBUG_LOW_ON THEN
3035 OZF_UTILITY_PVT.debug_message('In batch type as spp.');
3036 END IF;
3037 l_utilization_type:= G_SPP_UTIL_TYPE;
3038 l_budget_source_type := 'OFFR';
3039 l_justification := 'SPECIAL PRICE';
3040 l_to_create_utilization := false; --???
3041 IF l_line_int_rec.corrected_agreement_id IS NOT NULL THEN
3042 l_budget_source_id := l_line_int_rec.corrected_agreement_id;
3043 ELSE
3044 l_budget_source_id := l_line_int_rec.agreement_id;
3045 END IF;
3046 /*
3047 ELSIF l_batch_type = G_TP_ACCRUAL THEN
3048 -- Third party acrrual run from inter face.
3049 l_utilization_type:= G_TP_ACCRUAL_UTIL_TYPE;
3050 l_budget_source_id := l_line_int_rec.price_list_id;
3051 l_adjustment_type_id := G_CHBK_ADJ_TYPE_id;
3052 l_justification := 'THIRD PARTY PRICE DIFF';
3053 */
3054 END IF;
3055
3056 IF OZF_DEBUG_LOW_ON THEN
3057 OZF_UTILITY_PVT.debug_message('dup line id' ||l_line_int_rec.duplicated_line_id);
3058 OZF_UTILITY_PVT.debug_message('dup_accepted_amount:'||l_dup_total_accepted_amount);
3059 OZF_UTILITY_PVT.debug_message('total_accepted_amount:'||l_line_int_rec.total_accepted_amount);
3060 END IF;
3061
3062 IF l_line_int_rec.duplicated_line_id IS NOT NULL THEN
3063 IF l_line_int_rec.duplicated_adjustment_id = -1 THEN
3064 -- Create utilization using int rec
3065 Create_Utilization_record(
3066 p_line_int_rec => l_line_int_rec
3067 ,p_batch_type => l_batch_type
3068 ,p_fund_id => p_fund_id
3069 ,p_line_id => p_line_id
3070 ,p_cust_account_id => p_cust_account_id
3071 ,p_approver_id => p_approver_id
3072 ,p_line_agreement_flag => 'T'
3073 ,p_utilization_type => l_utilization_type
3074 ,p_adjustment_type_id => l_adjustment_type_id
3075 ,p_budget_source_type => l_budget_source_type
3076 ,p_budget_source_id => l_budget_source_id
3077 ,p_justification => l_justification
3078 ,p_to_create_utilization =>l_to_create_utilization
3079 ,x_return_status => l_return_status
3080 );
3081 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3082 RAISE FND_API.G_EXC_ERROR;
3083 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3085 END IF;
3086 ELSE
3087 -- Go check whether there IS a need to create a reverse utilization
3088 OPEN dup_adj_csr (l_line_int_rec.duplicated_line_id, l_batch_type);
3089 FETCH dup_adj_csr INTO l_dup_adjustment_id, l_dup_total_accepted_amount;
3090 CLOSE dup_adj_csr;
3091 -- Here the claimed_amount should NOT equal to the current claimed amount
3092 IF l_dup_total_accepted_amount IS NULL OR -- bug 5222273
3093 l_dup_total_accepted_amount <> l_line_int_rec.total_accepted_amount THEN
3094 -- AND the create one FOR the current int rec.
3095 -- creat utilization using int rec
3096 Create_Utilization_record(
3097 p_line_int_rec => l_line_int_rec
3098 ,p_batch_type => l_batch_type
3099 ,p_fund_id => p_fund_id
3100 ,p_line_id => p_line_id
3101 ,p_cust_account_id => p_cust_account_id
3102 ,p_approver_id => p_approver_id
3103 ,p_line_agreement_flag => 'T'
3104 ,p_utilization_type => l_utilization_type
3105 ,p_adjustment_type_id => l_adjustment_type_id
3106 ,p_budget_source_type => l_budget_source_type
3107 ,p_budget_source_id => l_budget_source_id
3108 ,p_justification => l_justification
3109 ,p_to_create_utilization =>l_to_create_utilization
3110 ,x_return_status => l_return_status
3111 );
3112 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3113 RAISE FND_API.G_EXC_ERROR;
3114 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3116 END IF;
3117
3118 -- We need to reverse the old utilization
3119 -- Here I need to repopulate the adj related columns.
3120 OPEN dup_adj_rec_csr(l_dup_adjustment_id);
3121 FETCH dup_adj_rec_csr INTO l_dup_adj_rec;
3122 CLOSE dup_adj_rec_csr;
3123 l_line_int_rec.orig_system_agreement_uom := l_dup_adj_rec.orig_system_agreement_uom;
3124 l_line_int_rec.orig_system_agreement_name := l_dup_adj_rec.orig_system_agreement_name;
3125 l_line_int_rec.orig_system_agreement_type := l_dup_adj_rec.orig_system_agreement_type;
3126 l_line_int_rec.orig_system_agreement_status := l_dup_adj_rec.orig_system_agreement_status;
3127 l_line_int_rec.orig_system_agreement_curr := l_dup_adj_rec.orig_system_agreement_curr;
3128 l_line_int_rec.orig_system_agreement_price := l_dup_adj_rec.orig_system_agreement_price;
3129 l_line_int_rec.orig_system_agreement_quantity:= l_dup_adj_rec.orig_system_agreement_quantity;
3130 l_line_int_rec.agreement_id := l_dup_adj_rec.agreement_id;
3131 l_line_int_rec.agreement_type := l_dup_adj_rec.agreement_type;
3132 l_line_int_rec.agreement_name := l_dup_adj_rec.agreement_name;
3133 l_line_int_rec.agreement_price := l_dup_adj_rec.agreement_price;
3134 l_line_int_rec.agreement_uom_code := l_dup_adj_rec.agreement_uom_code;
3135 l_line_int_rec.corrected_agreement_id := l_dup_adj_rec.corrected_agreement_id;
3136 l_line_int_rec.corrected_agreement_name := l_dup_adj_rec.corrected_agreement_name;
3137 l_line_int_rec.credit_code := l_dup_adj_rec.credit_code;
3138 l_line_int_rec.credit_advice_date := l_dup_adj_rec.credit_advice_date;
3139 l_line_int_rec.claimed_amount := l_dup_adj_rec.claimed_amount;
3140 l_line_int_rec.total_claimed_amount := l_dup_adj_rec.total_claimed_amount;
3141 l_line_int_rec.allowed_amount := l_dup_adj_rec.allowed_amount;
3142 l_line_int_rec.total_allowed_amount := l_dup_adj_rec.total_allowed_amount;
3143 l_line_int_rec.accepted_amount := -1 * l_dup_adj_rec.accepted_amount;
3144 l_line_int_rec.total_accepted_amount := -1 * l_dup_adj_rec.total_accepted_amount;
3145 l_line_int_rec.calculated_price := l_dup_adj_rec.calculated_price;
3146 l_line_int_rec.acctd_calculated_price := l_dup_adj_rec.acctd_calculated_price;
3147 l_line_int_rec.calculated_amount := l_line_int_rec.calculated_amount;
3148 l_line_int_rec.tolerance_flag := l_dup_adj_rec.tolerance_flag;
3149 l_line_int_rec.line_tolerance_amount := l_dup_adj_rec.line_tolerance_amount;
3150 l_line_int_rec.quantity := l_dup_adj_rec.priced_quantity;
3151 l_line_int_rec.uom_code := l_dup_adj_rec.priced_uom_code;
3152 l_line_int_rec.calculated_price := l_dup_adj_rec.priced_unit_price;
3153
3154 Create_Utilization_record(
3155 p_line_int_rec => l_line_int_rec
3156 ,p_batch_type => l_batch_type
3157 ,p_fund_id => p_fund_id
3158 ,p_line_id => p_line_id
3159 ,p_cust_account_id => p_cust_account_id
3160 ,p_approver_id => p_approver_id
3161 ,p_line_agreement_flag => 'F'
3162 ,p_utilization_type => l_utilization_type
3163 ,p_adjustment_type_id => l_adjustment_type_id
3164 ,p_budget_source_type => l_budget_source_type
3165 ,p_budget_source_id => l_budget_source_id
3166 ,p_justification => l_justification
3167 ,p_to_create_utilization =>l_to_create_utilization
3168 ,x_return_status => l_return_status
3169 );
3170 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3171 RAISE FND_API.G_EXC_ERROR;
3172 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3174 END IF;
3175 ELSE
3176 -- This IS a duplicate. No need to create utilization
3177 -- SLKRISHN move update to resale common pvt
3178 UPDATE ozf_resale_lines_int_all
3179 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED,
3180 duplicated_line_id = p_line_id,
3181 duplicated_adjustment_id = l_dup_adjustment_id
3182 WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
3183 END IF;
3184 END IF;
3185 ELSE
3186 IF OZF_DEBUG_LOW_ON THEN
3187 OZF_UTILITY_PVT.debug_message('In creat utilization with nothing');
3188 END IF;
3189 -- Create utilization using int rec
3190 Create_Utilization_record(
3191 p_line_int_rec => l_line_int_rec
3192 ,p_batch_type => l_batch_type
3193 ,p_fund_id => p_fund_id
3194 ,p_line_id => p_line_id
3195 ,p_cust_account_id => p_cust_account_id
3196 ,p_approver_id => p_approver_id
3197 ,p_line_agreement_flag => 'T'
3198 ,p_utilization_type => l_utilization_type
3199 ,p_adjustment_type_id => l_adjustment_type_id
3200 ,p_budget_source_type => l_budget_source_type
3201 ,p_budget_source_id => l_budget_source_id
3202 ,p_justification => l_justification
3203 ,p_to_create_utilization =>l_to_create_utilization
3204 ,x_return_status => l_return_status
3205 );
3206 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3207 RAISE FND_API.G_EXC_ERROR;
3208 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3210 END IF;
3211 END IF;
3212
3213 -- Debug Message
3214 IF OZF_DEBUG_HIGH_ON THEN
3215 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
3216 END IF;
3217
3218 --Standard call to get message count AND IF count=1, get the message
3219 FND_MSG_PUB.Count_And_Get (
3220 p_encoded => FND_API.G_FALSE,
3221 p_count => x_msg_count,
3222 p_data => x_msg_data
3223 );
3224 EXCEPTION
3225 WHEN FND_API.G_EXC_ERROR THEN
3226 ROLLBACK TO IDSM_Create_Utilization;
3227 x_return_status := FND_API.G_RET_STS_ERROR;
3228 -- Standard call to get message count AND IF count=1, get the message
3229 FND_MSG_PUB.Count_And_Get (
3230 p_encoded => FND_API.G_FALSE,
3231 p_count => x_msg_count,
3232 p_data => x_msg_data
3233 );
3234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3235 ROLLBACK TO IDSM_Create_Utilization;
3236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3237 -- Standard call to get message count AND IF count=1, get the message
3238 FND_MSG_PUB.Count_And_Get (
3239 p_encoded => FND_API.G_FALSE,
3240 p_count => x_msg_count,
3241 p_data => x_msg_data
3242 );
3243 WHEN OTHERS THEN
3244 ROLLBACK TO IDSM_Create_Utilization;
3245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3246 IF OZF_UNEXP_ERROR THEN
3247 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3248 END IF;
3249 -- Standard call to get message count AND IF count=1, get the message
3250 FND_MSG_PUB.Count_And_Get (
3251 p_encoded => FND_API.G_FALSE,
3252 p_count => x_msg_count,
3253 p_data => x_msg_data
3254 );
3255 END Create_Utilization;
3256
3257 ---------------------------------------------------------------------
3258 -- PROCEDURE
3259 -- Create_Sales_Transaction
3260 --
3261 -- PURPOSE
3262 -- This procedure inserts a record in ozf sales transaction table
3263 --
3264 -- PARAMETERS
3265 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
3266 -- x_headerid out NUMBER
3267 -- x_return_status out VARCHAR2
3268 --
3269 -- NOTES
3270 --
3271 ---------------------------------------------------------------------
3272 PROCEDURE Create_Sales_Transaction(
3273 p_api_version IN NUMBER
3274 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3275 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3276 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3277 ,p_line_int_rec IN g_interface_rec_csr%rowtype
3278 ,p_header_id IN NUMBER
3279 ,p_line_id IN NUMBER
3280 ,x_sales_transaction_id OUT NOCOPY NUMBER
3281 ,x_return_status OUT NOCOPY VARCHAR2
3282 ,x_msg_data OUT NOCOPY VARCHAR2
3283 ,x_msg_count OUT NOCOPY NUMBER
3284 )
3285 IS
3286 l_api_name CONSTANT VARCHAR2(30) := 'Create_Sales_Transaction';
3287 l_api_version CONSTANT NUMBER := 1.0;
3288 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3289 --
3290 l_return_status VARCHAR2(30);
3291 l_msg_data VARCHAR2(2000);
3292 l_msg_count NUMBER;
3293
3294 l_sales_transaction_id NUMBER;
3295 l_object_version_number NUMBER := 1;
3296 l_org_id NUMBER;
3297
3298 l_sales_transaction_rec OZF_SALES_TRANSACTIONS_PVT.SALES_TRANSACTION_REC_TYPE;
3299
3300 CURSOR party_id_csr(p_cust_account_id NUMBER) IS
3301 SELECT party_id
3302 FROM hz_cust_accounts
3303 WHERE cust_account_id = p_cust_account_id;
3304
3305 CURSOR party_site_id_csr(p_account_site_id NUMBER) IS
3306 SELECT party_site_id
3307 FROM hz_cust_acct_sites
3308 WHERE cust_acct_site_id = p_account_site_id;
3309
3310 BEGIN
3311 -- Standard BEGIN of API savepoint
3312 SAVEPOINT Create_Sales_Transaction;
3313 -- Standard call to check for call compatibility.
3314 IF NOT FND_API.Compatible_API_Call (
3315 l_api_version,
3316 p_api_version,
3317 l_api_name,
3318 G_PKG_NAME)
3319 THEN
3320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3321 END IF;
3322
3323 -- Debug Message
3324 IF OZF_DEBUG_HIGH_ON THEN
3325 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
3326 END IF;
3327
3328 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
3329 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
3330 FND_MSG_PUB.initialize;
3331 END IF;
3332
3333 -- Initialize API return status to sucess
3334 x_return_status := FND_API.G_RET_STS_SUCCESS;
3335
3336 l_sales_transaction_rec.sold_from_cust_account_id :=p_line_int_rec.sold_from_cust_account_id;
3337 OPEN party_id_csr(l_sales_transaction_rec.sold_from_cust_account_id);
3338 FETCH party_id_csr INTO l_sales_transaction_rec.sold_from_party_id;
3339 CLOSE party_id_csr;
3340
3341 OPEN party_site_id_csr(p_line_int_rec.sold_from_site_id);
3342 FETCH party_site_id_csr INTO l_sales_transaction_rec.sold_from_party_site_id;
3343 CLOSE party_site_id_csr;
3344
3345 l_sales_transaction_rec.sold_to_cust_account_id := p_line_int_rec.bill_to_cust_account_id;
3346 l_sales_transaction_rec.sold_to_party_id := p_line_int_rec.bill_to_party_id;
3347 l_sales_transaction_rec.sold_to_party_site_id := p_line_int_rec.bill_to_party_site_id;
3348 l_sales_transaction_rec.bill_to_site_use_id := p_line_int_rec.bill_to_site_use_id;
3349 l_sales_transaction_rec.ship_to_site_use_id := p_line_int_rec.ship_to_site_use_id;
3350 l_sales_transaction_rec.transaction_date := p_line_int_rec.date_ordered;
3351 IF p_line_int_rec.product_transfer_movement_type = 'TI' THEN
3352 l_sales_transaction_rec.transfer_type := 'IN';
3353 ELSIF p_line_int_rec.product_transfer_movement_type = 'TO' THEN
3354 l_sales_transaction_rec.transfer_type := 'OUT';
3355 ELSIF p_line_int_rec.product_transfer_movement_type = 'DC' THEN
3356 l_sales_transaction_rec.transfer_type := 'OUT';
3357 ELSIF p_line_int_rec.product_transfer_movement_type = 'CD' THEN
3358 l_sales_transaction_rec.transfer_type := 'IN';
3359 END IF;
3360 l_sales_transaction_rec.quantity := p_line_int_rec.quantity;
3361 l_sales_transaction_rec.uom_code := p_line_int_rec.uom_code;
3362 l_sales_transaction_rec.amount := p_line_int_rec.selling_price * p_line_int_rec.quantity;
3363 l_sales_transaction_rec.currency_code := p_line_int_rec.currency_code;
3364 l_sales_transaction_rec.inventory_item_id := p_line_int_rec.inventory_item_id;
3365 l_sales_transaction_rec.header_id := p_header_id;
3366 l_sales_transaction_rec.line_id := p_line_id;
3367 l_sales_transaction_rec.reason_code := NULL;
3368 l_sales_transaction_rec.source_code := 'IS';
3369 l_sales_transaction_rec.error_flag := NULL;
3370
3371 -- We need to create sales transactions based on these lines.
3372 OZF_SALES_TRANSACTIONS_PVT.Create_Transaction (
3373 p_api_version => 1.0
3374 ,p_init_msg_list => FND_API.G_FALSE
3375 ,p_commit => FND_API.G_FALSE
3376 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3377 ,p_transaction_rec => l_sales_transaction_rec
3378 ,x_sales_transaction_id => l_sales_transaction_id
3379 ,x_return_status => l_return_status
3380 ,x_msg_data => l_msg_data
3381 ,x_msg_count => l_msg_count
3382 );
3383 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3384 RAISE FND_API.G_EXC_ERROR;
3385 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3387 END IF;
3388 --
3389
3390 -- Debug Message
3391 IF OZF_DEBUG_HIGH_ON THEN
3392 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
3393 END IF;
3394
3395 --Standard call to get message count AND IF count=1, get the message
3396 FND_MSG_PUB.Count_And_Get (
3397 p_encoded => FND_API.G_FALSE,
3398 p_count => x_msg_count,
3399 p_data => x_msg_data
3400 );
3401 EXCEPTION
3402 WHEN FND_API.G_EXC_ERROR THEN
3403 ROLLBACK TO Create_Sales_Transaction;
3404 x_return_status := FND_API.G_RET_STS_ERROR;
3405 -- Standard call to get message count AND IF count=1, get the message
3406 FND_MSG_PUB.Count_And_Get (
3407 p_encoded => FND_API.G_FALSE,
3408 p_count => x_msg_count,
3409 p_data => x_msg_data
3410 );
3411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3412 ROLLBACK TO Create_Sales_Transaction;
3413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3414 -- Standard call to get message count AND IF count=1, get the message
3415 FND_MSG_PUB.Count_And_Get (
3416 p_encoded => FND_API.G_FALSE,
3417 p_count => x_msg_count,
3418 p_data => x_msg_data
3419 );
3420 WHEN OTHERS THEN
3421 ROLLBACK TO Create_Sales_Transaction;
3422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3423 IF OZF_UNEXP_ERROR THEN
3424 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3425 END IF;
3426 -- Standard call to get message count AND IF count=1, get the message
3427 FND_MSG_PUB.Count_And_Get (
3428 p_encoded => FND_API.G_FALSE,
3429 p_count => x_msg_count,
3430 p_data => x_msg_data
3431 );
3432 END Create_Sales_Transaction;
3433
3434 ---------------------------------------------------------------------
3435 -- PROCEDURE
3436 -- Insert_Resale_Header
3437 --
3438 -- PURPOSE
3439 -- This procedure inserts a record in to resale header table
3440 --
3441 -- PARAMETERS
3442 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
3443 -- x_headerid OUT NUMBER
3444 -- x_return_status OUT VARCHAR2
3445 --
3446 -- NOTES
3447 --
3448 ---------------------------------------------------------------------
3449 PROCEDURE Insert_Resale_Header(
3450 p_api_version IN NUMBER
3451 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
3452 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3453 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3454 ,p_line_int_rec IN g_interface_rec_csr%rowtype
3455 ,x_header_id OUT NOCOPY NUMBER
3456 ,x_return_status OUT NOCOPY VARCHAR2
3457 ,x_msg_data OUT NOCOPY VARCHAR2
3458 ,x_msg_count OUT NOCOPY NUMBER
3459 )
3460 IS
3461 l_api_name CONSTANT VARCHAR2(30) := 'Insert_resale_header';
3462 l_api_version CONSTANT NUMBER := 1.0;
3463 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3464 --
3465 l_return_status VARCHAR2(30);
3466 l_msg_data VARCHAR2(2000);
3467 l_msg_count NUMBER;
3468
3469 l_header_id NUMBER;
3470 l_object_version_number NUMBER := 1;
3471 l_org_id NUMBER;
3472 BEGIN
3473 -- Standard BEGIN of API savepoint
3474 SAVEPOINT Insert_Resale_Header;
3475 -- Standard call to check for call compatibility.
3476 IF NOT FND_API.Compatible_API_Call (
3477 l_api_version,
3478 p_api_version,
3479 l_api_name,
3480 G_PKG_NAME)
3481 THEN
3482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3483 END IF;
3484
3485 -- Debug Message
3486 IF OZF_DEBUG_HIGH_ON THEN
3487 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
3488 END IF;
3489
3490 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
3491 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
3492 FND_MSG_PUB.initialize;
3493 END IF;
3494
3495 -- Initialize API return status to sucess
3496 x_return_status := FND_API.G_RET_STS_SUCCESS;
3497
3498 -- INSERT the order information to ozf_resale_headers_all
3499 OPEN g_header_id_csr;
3500 FETCH g_header_id_csr INTO l_header_id;
3501 CLOSE g_header_id_csr;
3502
3503 x_header_id := l_header_id;
3504 l_org_id := p_line_int_rec.org_id; -- bug # 5997978 fixed
3505 IF OZF_DEBUG_LOW_ON THEN
3506 OZF_UTILITY_PVT.debug_message('before INSERT: header_id' || l_header_id);
3507 END IF;
3508
3509 OZF_RESALE_HEADERS_PKG.Insert_Row(
3510 px_resale_header_id => l_header_id,
3511 px_object_version_number => l_object_version_number,
3512 p_last_update_date => SYSdate,
3513 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
3514 p_creation_date => SYSdate,
3515 p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
3516 p_created_by => NVL(FND_GLOBAL.user_id,-1),
3517 p_last_update_login => NVL(FND_GLOBAL.conc_login_id,-1),
3518 p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
3519 p_program_update_date => SYSdate,
3520 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
3521 p_created_from => p_line_int_rec.created_from,
3522 p_date_shipped => p_line_int_rec.date_shipped,
3523 p_date_ordered => p_line_int_rec.date_ordered,
3524 p_order_type_id => p_line_int_rec.order_type_id,
3525 p_order_type => p_line_int_rec.order_type,
3526 p_order_category => p_line_int_rec.order_category,
3527 p_status_code => G_BATCH_PROCESSED,
3528 p_direct_customer_flag => p_line_int_rec.direct_customer_flag,
3529 p_order_number => p_line_int_rec.order_number,
3530 p_price_LIST_id => p_line_int_rec.price_LIST_id,
3531 p_bill_to_cust_account_id => p_line_int_rec.bill_to_cust_account_id,
3532 p_bill_to_site_use_id => p_line_int_rec.bill_to_site_use_id,
3533 p_bill_to_party_name => p_line_int_rec.bill_to_party_name,
3534 p_bill_to_party_id =>p_line_int_rec.bill_to_party_id ,
3535 p_bill_to_party_site_id =>p_line_int_rec.bill_to_party_site_id ,
3536 p_bill_to_location => p_line_int_rec.bill_to_location ,
3537 p_bill_to_duns_number => p_line_int_rec.bill_to_duns_number,
3538 p_bill_to_address => p_line_int_rec.bill_to_address,
3539 p_bill_to_city => p_line_int_rec.bill_to_city ,
3540 p_bill_to_state => p_line_int_rec.bill_to_state,
3541 p_bill_to_postal_code => p_line_int_rec.bill_to_postal_code,
3542 p_bill_to_country => p_line_int_rec.bill_to_country,
3543 p_bill_to_contact_party_id => p_line_int_rec.bill_to_contact_party_id,
3544 p_bill_to_contact_name => p_line_int_rec.bill_to_contact_name,
3545 p_bill_to_email => p_line_int_rec.bill_to_email,
3546 p_bill_to_phone => p_line_int_rec.bill_to_phone,
3547 p_bill_to_fax => p_line_int_rec.bill_to_fax,
3548 p_ship_to_cust_account_id => p_line_int_rec.ship_to_cust_account_id,
3549 p_ship_to_site_use_id => p_line_int_rec.ship_to_site_use_id,
3550 p_ship_to_party_name => p_line_int_rec.ship_to_party_name,
3551 p_ship_to_party_id =>p_line_int_rec.ship_to_party_id ,
3552 p_ship_to_party_site_id =>p_line_int_rec.ship_to_party_site_id ,
3553 p_ship_to_location => p_line_int_rec.ship_to_location,
3554 p_ship_to_duns_number => p_line_int_rec.ship_to_duns_number,
3555 p_ship_to_address => p_line_int_rec.ship_to_address,
3556 p_ship_to_city => p_line_int_rec.ship_to_city,
3557 p_ship_to_state => p_line_int_rec.ship_to_state,
3558 p_ship_to_postal_code => p_line_int_rec.ship_to_postal_code,
3559 p_ship_to_country => p_line_int_rec.ship_to_country,
3560 p_ship_to_contact_party_id => p_line_int_rec.ship_to_contact_party_id,
3561 p_ship_to_contact_name => p_line_int_rec.ship_to_contact_name,
3562 p_ship_to_email => p_line_int_rec.ship_to_email,
3563 p_ship_to_phone => p_line_int_rec.ship_to_phone,
3564 p_ship_to_fax => p_line_int_rec.ship_to_fax,
3565 p_sold_from_cust_account_id=> p_line_int_rec.sold_from_cust_account_id,
3566 p_ship_from_cust_account_id=> p_line_int_rec.ship_from_cust_account_id,
3567 p_header_attribute_category=> p_line_int_rec.header_attribute_category,
3568 p_header_attribute1 => p_line_int_rec.header_attribute1,
3569 p_header_attribute2 => p_line_int_rec.header_attribute2,
3570 p_header_attribute3 => p_line_int_rec.header_attribute3,
3571 p_header_attribute4 => p_line_int_rec.header_attribute4,
3572 p_header_attribute5 => p_line_int_rec.header_attribute5,
3573 p_header_attribute6 => p_line_int_rec.header_attribute6,
3574 p_header_attribute7 => p_line_int_rec.header_attribute7,
3575 p_header_attribute8 => p_line_int_rec.header_attribute8,
3576 p_header_attribute9 => p_line_int_rec.header_attribute9,
3577 p_header_attribute10 => p_line_int_rec.header_attribute10,
3578 p_header_attribute11 => p_line_int_rec.header_attribute11,
3579 p_header_attribute12 => p_line_int_rec.header_attribute12,
3580 p_header_attribute13 => p_line_int_rec.header_attribute13,
3581 p_header_attribute14 => p_line_int_rec.header_attribute14,
3582 p_header_attribute15 => p_line_int_rec.header_attribute15,
3583 p_attribute_category => NULL,
3584 p_attribute1 => NULL,
3585 p_attribute2 => NULL,
3586 p_attribute3 => NULL,
3587 p_attribute4 => NULL,
3588 p_attribute5 => NULL,
3589 p_attribute6 => NULL,
3590 p_attribute7 => NULL,
3591 p_attribute8 => NULL,
3592 p_attribute9 => NULL,
3593 p_attribute10 => NULL,
3594 p_attribute11 => NULL,
3595 p_attribute12 => NULL,
3596 p_attribute13 => NULL,
3597 p_attribute14 => NULL,
3598 p_attribute15 => NULL,
3599 px_org_id => l_org_id);
3600
3601 -- Debug Message
3602 IF OZF_DEBUG_HIGH_ON THEN
3603 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
3604 END IF;
3605
3606 --Standard call to get message count AND IF count=1, get the message
3607 FND_MSG_PUB.Count_And_Get (
3608 p_encoded => FND_API.G_FALSE,
3609 p_count => x_msg_count,
3610 p_data => x_msg_data
3611 );
3612 EXCEPTION
3613 WHEN FND_API.G_EXC_ERROR THEN
3614 ROLLBACK TO Insert_Resale_Header;
3615 x_return_status := FND_API.G_RET_STS_ERROR;
3616 -- Standard call to get message count AND IF count=1, get the message
3617 FND_MSG_PUB.Count_And_Get (
3618 p_encoded => FND_API.G_FALSE,
3619 p_count => x_msg_count,
3620 p_data => x_msg_data
3621 );
3622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3623 ROLLBACK TO Insert_Resale_Header;
3624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3625 -- Standard call to get message count AND IF count=1, get the message
3626 FND_MSG_PUB.Count_And_Get (
3627 p_encoded => FND_API.G_FALSE,
3628 p_count => x_msg_count,
3629 p_data => x_msg_data
3630 );
3631 WHEN OTHERS THEN
3632 ROLLBACK TO Insert_Resale_Header;
3633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3634 IF OZF_UNEXP_ERROR THEN
3635 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3636 END IF;
3637 -- Standard call to get message count AND IF count=1, get the message
3638 FND_MSG_PUB.Count_And_Get (
3639 p_encoded => FND_API.G_FALSE,
3640 p_count => x_msg_count,
3641 p_data => x_msg_data
3642 );
3643 END Insert_Resale_Header;
3644
3645 ---------------------------------------------------------------------
3646 -- PROCEDURE
3647 -- Insert_Resale_Line
3648 --
3649 -- PURPOSE
3650 -- This procedure inserts a record IN resale line table
3651 --
3652 -- PARAMETERS
3653 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
3654 -- x_return_status OUT VARCHAR2
3655 --
3656 -- NOTES
3657 --
3658 ---------------------------------------------------------------------
3659 PROCEDURE Insert_Resale_Line(
3660 p_api_version IN NUMBER
3661 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
3662 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3663 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3664 ,p_line_int_rec IN g_interface_rec_csr%rowtype
3665 ,p_header_id IN NUMBER
3666 ,x_line_id OUT NOCOPY NUMBER
3667 ,x_return_status OUT NOCOPY VARCHAR2
3668 ,x_msg_data OUT NOCOPY VARCHAR2
3669 ,x_msg_count OUT NOCOPY NUMBER
3670 )
3671
3672 IS
3673 l_api_name CONSTANT VARCHAR2(30) := 'Insert_resale_line';
3674 l_api_version CONSTANT NUMBER := 1.0;
3675 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3676 --
3677 l_return_status VARCHAR2(30);
3678 l_msg_data VARCHAR2(2000);
3679 l_msg_count NUMBER;
3680
3681 l_line_id NUMBER;
3682 l_obj_ver_num NUMBER := 1;
3683 l_org_id NUMBER;
3684 l_map_id NUMBER;
3685 BEGIN
3686 -- Standard BEGIN of API savepoint
3687 SAVEPOINT Insert_Resale_Line;
3688 -- Standard call to check for call compatibility.
3689 IF NOT FND_API.Compatible_API_Call (
3690 l_api_version,
3691 p_api_version,
3692 l_api_name,
3693 G_PKG_NAME)
3694 THEN
3695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3696 END IF;
3697
3698 -- Debug Message
3699 IF OZF_DEBUG_HIGH_ON THEN
3700 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
3701 END IF;
3702
3703 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
3704 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
3705 FND_MSG_PUB.initialize;
3706 END IF;
3707
3708 -- Initialize API return status to sucess
3709 x_return_status := FND_API.G_RET_STS_SUCCESS;
3710
3711 OPEN g_line_id_csr;
3712 FETCH g_line_id_csr INTO l_line_id;
3713 CLOSE g_line_id_csr;
3714 x_line_id := l_line_id;
3715 l_org_id := p_line_int_rec.org_id; -- bug # 5997978 fixed
3716 IF OZF_DEBUG_LOW_ON THEN
3717 OZF_UTILITY_PVT.debug_message('before line INSERT: header id' || p_header_id);
3718 OZF_UTILITY_PVT.debug_message('before line INSERT:' || l_line_id);
3719 END IF;
3720
3721 OZF_RESALE_LINES_PKG.Insert_Row(
3722 p_resale_line_id => l_line_id ,
3723 p_resale_header_id => p_header_id ,
3724 p_resale_transfer_type => p_line_int_rec.resale_transfer_type ,
3725 px_object_version_number => l_obj_ver_num,
3726 p_last_update_date => SYSdate,
3727 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
3728 p_creation_date => SYSdate,
3729 p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
3730 p_created_by => NVL(FND_GLOBAL.user_id,-1),
3731 p_last_update_login => NVL(FND_GLOBAL.conc_login_id,-1),
3732 p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
3733 p_program_update_date => SYSdate,
3734 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
3735 p_created_from => p_line_int_rec.created_from,
3736 p_status_code => G_BATCH_ADJ_PROCESSED ,
3737 p_product_trans_movement_type => p_line_int_rec.product_transfer_movement_type ,
3738 p_product_transfer_date => p_line_int_rec.product_transfer_date,
3739 p_end_cust_party_id => p_line_int_rec.end_cust_party_id,
3740 p_end_cust_site_use_id => p_line_int_rec.end_cust_site_use_id,
3741 p_end_cust_site_use_code => p_line_int_rec.end_cust_site_use_code,
3742 p_end_cust_party_site_id => p_line_int_rec.end_cust_party_site_id ,
3743 p_end_cust_party_name => p_line_int_rec.end_cust_party_name ,
3744 p_end_cust_location => p_line_int_rec.end_cust_location ,
3745 p_end_cust_address => p_line_int_rec.end_cust_address ,
3746 p_end_cust_city => p_line_int_rec.end_cust_city ,
3747 p_end_cust_state => p_line_int_rec.end_cust_state ,
3748 p_end_cust_postal_code => p_line_int_rec.end_cust_postal_code ,
3749 p_end_cust_country => p_line_int_rec.end_cust_country ,
3750 p_end_cust_contact_party_id => p_line_int_rec.end_cust_contact_party_id ,
3751 p_end_cust_contact_name => p_line_int_rec.end_cust_contact_name ,
3752 p_end_cust_email => p_line_int_rec.end_cust_email ,
3753 p_end_cust_phone => p_line_int_rec.end_cust_phone ,
3754 p_end_cust_fax => p_line_int_rec.end_cust_fax ,
3755 p_bill_to_cust_account_id => p_line_int_rec.bill_to_cust_account_id,
3756 p_bill_to_site_use_id => p_line_int_rec.bill_to_site_use_id ,
3757 p_bill_to_party_name => p_line_int_rec.bill_to_party_name ,
3758 p_bill_to_party_id => p_line_int_rec.bill_to_party_id ,
3759 p_bill_to_party_site_id => p_line_int_rec.bill_to_party_site_id ,
3760 p_bill_to_duns_number => p_line_int_rec.bill_to_duns_number ,
3761 p_bill_to_location => p_line_int_rec.bill_to_location ,
3762 p_bill_to_address => p_line_int_rec.bill_to_address ,
3763 p_bill_to_city => p_line_int_rec.bill_to_city ,
3764 p_bill_to_state => p_line_int_rec.bill_to_state ,
3765 p_bill_to_postal_code => p_line_int_rec.bill_to_postal_code ,
3766 p_bill_to_country => p_line_int_rec.bill_to_country ,
3767 p_bill_to_contact_party_id => p_line_int_rec.bill_to_contact_party_id ,
3768 p_bill_to_contact_name => p_line_int_rec.bill_to_contact_name ,
3769 p_bill_to_email => p_line_int_rec.bill_to_email ,
3770 p_bill_to_phone => p_line_int_rec.bill_to_phone ,
3771 p_bill_to_fax => p_line_int_rec.bill_to_fax ,
3772 p_ship_to_cust_account_id => p_line_int_rec.ship_to_cust_account_id ,
3773 p_ship_to_site_use_id => p_line_int_rec.ship_to_site_use_id ,
3774 p_ship_to_party_name => p_line_int_rec.ship_to_party_name ,
3775 p_ship_to_party_id => p_line_int_rec.ship_to_party_id ,
3776 p_ship_to_party_site_id => p_line_int_rec.ship_to_party_site_id ,
3777 p_ship_to_duns_number => p_line_int_rec.ship_to_duns_number ,
3778 p_ship_to_location => p_line_int_rec.ship_to_location ,
3779 p_ship_to_address => p_line_int_rec.ship_to_address,
3780 p_ship_to_city => p_line_int_rec.ship_to_city ,
3781 p_ship_to_state => p_line_int_rec.ship_to_state ,
3782 p_ship_to_postal_code => p_line_int_rec.ship_to_postal_code ,
3783 p_ship_to_country => p_line_int_rec.ship_to_country ,
3784 p_ship_to_contact_party_id => p_line_int_rec.ship_to_contact_party_id ,
3785 p_ship_to_contact_name => p_line_int_rec.ship_to_contact_name ,
3786 p_ship_to_email => p_line_int_rec.ship_to_email ,
3787 p_ship_to_phone => p_line_int_rec.ship_to_phone ,
3788 p_ship_to_fax => p_line_int_rec.ship_to_fax ,
3789 p_ship_from_cust_account_id => p_line_int_rec.ship_from_cust_account_id ,
3790 p_ship_from_site_id => p_line_int_rec.ship_from_site_id,
3791 p_ship_from_party_name => p_line_int_rec.ship_from_party_name,
3792 p_ship_from_location => p_line_int_rec.ship_from_location ,
3793 p_ship_from_address => p_line_int_rec.ship_from_address ,
3794 p_ship_from_city => p_line_int_rec.ship_from_city ,
3795 p_ship_from_state => p_line_int_rec.ship_from_state ,
3796 p_ship_from_postal_code => p_line_int_rec.ship_from_postal_code ,
3797 p_ship_from_country => p_line_int_rec.ship_from_country,
3798 p_ship_from_contact_party_id => p_line_int_rec.ship_from_contact_party_id ,
3799 p_ship_from_contact_name => p_line_int_rec.ship_from_contact_name ,
3800 p_ship_from_email => p_line_int_rec.ship_from_email ,
3801 p_ship_from_fax => p_line_int_rec.ship_from_fax ,
3802 p_ship_from_phone => p_line_int_rec.ship_from_phone ,
3803 p_sold_from_cust_account_id => p_line_int_rec.sold_from_cust_account_id ,
3804 p_sold_from_site_id => p_line_int_rec.sold_from_site_id ,
3805 p_sold_from_party_name => p_line_int_rec.sold_from_party_name,
3806 p_sold_from_location => p_line_int_rec.sold_from_location ,
3807 p_sold_from_address => p_line_int_rec.sold_from_address ,
3808 p_sold_from_city => p_line_int_rec.sold_from_city ,
3809 p_sold_from_state => p_line_int_rec.sold_from_state ,
3810 p_sold_from_postal_code => p_line_int_rec.sold_from_postal_code ,
3811 p_sold_from_country => p_line_int_rec.sold_from_country,
3812 p_sold_from_contact_party_id => p_line_int_rec.sold_from_contact_party_id ,
3813 p_sold_from_contact_name => p_line_int_rec.sold_from_contact_name ,
3814 p_sold_from_email => p_line_int_rec.sold_from_email,
3815 p_sold_from_phone => p_line_int_rec.sold_from_phone,
3816 p_sold_from_fax => p_line_int_rec.sold_from_fax,
3817 p_price_LIST_id => p_line_int_rec.price_LIST_id ,
3818 p_price_LIST_name => p_line_int_rec.price_LIST_name ,
3819 p_invoice_number => p_line_int_rec.invoice_number ,
3820 p_date_invoiced => p_line_int_rec.date_invoiced,
3821 p_po_number => p_line_int_rec.po_number ,
3822 p_po_release_number => p_line_int_rec.po_release_number ,
3823 p_po_type => p_line_int_rec.po_type ,
3824 p_order_number => p_line_int_rec.order_number ,
3825 p_date_ordered => p_line_int_rec.date_ordered,
3826 p_date_shipped => p_line_int_rec.date_shipped,
3827 p_purchase_uom_code => p_line_int_rec.purchase_uom_code ,
3828 p_quantity => p_line_int_rec.quantity ,
3829 p_uom_code => p_line_int_rec.uom_code ,
3830 p_currency_code => p_line_int_rec.currency_code ,
3831 p_exchange_rate => p_line_int_rec.exchange_rate ,
3832 p_exchange_rate_type => p_line_int_rec.exchange_rate_type,
3833 p_exchange_rate_date => p_line_int_rec.exchange_rate_date,
3834 p_selling_price => p_line_int_rec.selling_price ,
3835 p_acctd_selling_price => p_line_int_rec.acctd_selling_price ,
3836 p_purchase_price => p_line_int_rec.purchase_price ,
3837 p_acctd_purchase_price => p_line_int_rec.acctd_purchase_price ,
3838 p_tracing_flag => p_line_int_rec.tracing_flag ,
3839 p_orig_system_quantity => p_line_int_rec. orig_system_quantity,
3840 p_orig_system_uom => p_line_int_rec.orig_system_uom ,
3841 p_orig_system_currency_code => p_line_int_rec.orig_system_currency_code,
3842 p_orig_system_selling_price => p_line_int_rec.orig_system_selling_price ,
3843 p_orig_system_line_reference => p_line_int_rec.orig_system_line_reference ,
3844 p_orig_system_reference => p_line_int_rec.orig_system_reference ,
3845 p_orig_system_purchase_uom => p_line_int_rec.orig_system_purchase_uom,
3846 p_orig_system_purchase_curr => p_line_int_rec.orig_system_purchase_curr,
3847 p_orig_system_purchase_price => p_line_int_rec.orig_system_purchase_price,
3848 p_orig_system_purchase_quant => p_line_int_rec.orig_system_purchase_quantity,
3849 p_orig_system_item_number => p_line_int_rec.orig_system_item_number,
3850 p_product_category_id => p_line_int_rec.product_category_id ,
3851 p_category_name => p_line_int_rec.category_name ,
3852 p_inventory_item_segment1 => p_line_int_rec.inventory_item_segment1 ,
3853 p_inventory_item_segment2 => p_line_int_rec.inventory_item_segment2 ,
3854 p_inventory_item_segment3 => p_line_int_rec.inventory_item_segment3 ,
3855 p_inventory_item_segment4 => p_line_int_rec.inventory_item_segment4 ,
3856 p_inventory_item_segment5 => p_line_int_rec.inventory_item_segment5 ,
3857 p_inventory_item_segment6 => p_line_int_rec.inventory_item_segment6 ,
3858 p_inventory_item_segment7 => p_line_int_rec.inventory_item_segment7 ,
3859 p_inventory_item_segment8 => p_line_int_rec.inventory_item_segment8 ,
3860 p_inventory_item_segment9 => p_line_int_rec.inventory_item_segment9,
3861 p_inventory_item_segment10 => p_line_int_rec.inventory_item_segment10,
3862 p_inventory_item_segment11 => p_line_int_rec.inventory_item_segment11,
3863 p_inventory_item_segment12 => p_line_int_rec.inventory_item_segment12,
3864 p_inventory_item_segment13 => p_line_int_rec.inventory_item_segment13,
3865 p_inventory_item_segment14 => p_line_int_rec.inventory_item_segment14,
3866 p_inventory_item_segment15 => p_line_int_rec.inventory_item_segment15,
3867 p_inventory_item_segment16 => p_line_int_rec.inventory_item_segment16,
3868 p_inventory_item_segment17 => p_line_int_rec.inventory_item_segment17,
3869 p_inventory_item_segment18 => p_line_int_rec.inventory_item_segment18,
3870 p_inventory_item_segment19 => p_line_int_rec.inventory_item_segment19,
3871 p_inventory_item_segment20 => p_line_int_rec.inventory_item_segment20 ,
3872 p_inventory_item_id => p_line_int_rec.inventory_item_id ,
3873 p_item_description => p_line_int_rec.item_description ,
3874 p_upc_code => p_line_int_rec.upc_code ,
3875 p_item_number => p_line_int_rec.item_number ,
3876 p_direct_customer_flag => p_line_int_rec.direct_customer_flag ,
3877 p_attribute_category => NULL,
3878 p_attribute1 => NULL,
3879 p_attribute2 => NULL,
3880 p_attribute3 => NULL,
3881 p_attribute4 => NULL,
3882 p_attribute5 => NULL,
3883 p_attribute6 => NULL,
3884 p_attribute7 => NULL,
3885 p_attribute8 => NULL,
3886 p_attribute9 => NULL,
3887 p_attribute10 => NULL,
3888 p_attribute11 => NULL,
3889 p_attribute12 => NULL,
3890 p_attribute13 => NULL,
3891 p_attribute14 => NULL,
3892 p_attribute15 => NULL,
3893 p_line_attribute_category => p_line_int_rec.line_attribute_category,
3894 p_line_attribute1 => p_line_int_rec.line_attribute1 ,
3895 p_line_attribute2 => p_line_int_rec.line_attribute2 ,
3896 p_line_attribute3 => p_line_int_rec.line_attribute3,
3897 p_line_attribute4 => p_line_int_rec.line_attribute4 ,
3898 p_line_attribute5 => p_line_int_rec.line_attribute5 ,
3899 p_line_attribute6 => p_line_int_rec.line_attribute6 ,
3900 p_line_attribute7 => p_line_int_rec.line_attribute7,
3901 p_line_attribute8 => p_line_int_rec.line_attribute8,
3902 p_line_attribute9 => p_line_int_rec.line_attribute9,
3903 p_line_attribute10 => p_line_int_rec.line_attribute10,
3904 p_line_attribute11 => p_line_int_rec.line_attribute11,
3905 p_line_attribute12 => p_line_int_rec.line_attribute12,
3906 p_line_attribute13 => p_line_int_rec.line_attribute13,
3907 p_line_attribute14 => p_line_int_rec.line_attribute14,
3908 p_line_attribute15 => p_line_int_rec.line_attribute15 ,
3909 px_org_id => l_org_id );
3910
3911
3912 IF OZF_DEBUG_HIGH_ON THEN
3913 OZF_UTILITY_PVT.debug_message('line INSERT successful id:' || l_line_id);
3914 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
3915 END IF;
3916
3917 --Standard call to get message count AND IF count=1, get the message
3918 FND_MSG_PUB.Count_And_Get (
3919 p_encoded => FND_API.G_FALSE,
3920 p_count => x_msg_count,
3921 p_data => x_msg_data
3922 );
3923 x_return_status := l_return_status;
3924 EXCEPTION
3925 WHEN FND_API.G_EXC_ERROR THEN
3926 ROLLBACK TO Insert_Resale_Line;
3927 x_return_status := FND_API.G_RET_STS_ERROR;
3928 -- Standard call to get message count AND IF count=1, get the message
3929 FND_MSG_PUB.Count_And_Get (
3930 p_encoded => FND_API.G_FALSE,
3931 p_count => x_msg_count,
3932 p_data => x_msg_data
3933 );
3934 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3935 ROLLBACK TO Insert_Resale_Line;
3936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3937 -- Standard call to get message count AND IF count=1, get the message
3938 FND_MSG_PUB.Count_And_Get (
3939 p_encoded => FND_API.G_FALSE,
3940 p_count => x_msg_count,
3941 p_data => x_msg_data
3942 );
3943 WHEN OTHERS THEN
3944 ROLLBACK TO Insert_Resale_Line;
3945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3946 IF OZF_UNEXP_ERROR THEN
3947 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3948 END IF;
3949 -- Standard call to get message count AND IF count=1, get the message
3950 FND_MSG_PUB.Count_And_Get (
3951 p_encoded => FND_API.G_FALSE,
3952 p_count => x_msg_count,
3953 p_data => x_msg_data
3954 );
3955 END Insert_Resale_Line;
3956
3957 ---------------------------------------------------------------------
3958 -- PROCEDURE
3959 -- Insert_Resale_Line_Mapping
3960 --
3961 -- PURPOSE
3962 -- This procedure inserts a record IN resale_batch_line_mapping table
3963 --
3964 -- PARAMETERS
3965 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
3966 -- x_return_status OUT VARCHAR2
3967 --
3968 -- NOTES
3969 --
3970 ---------------------------------------------------------------------
3971 PROCEDURE Insert_Resale_Line_Mapping(
3972 p_api_version IN NUMBER
3973 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
3974 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3975 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3976 ,p_resale_batch_id IN NUMBER
3977 ,p_line_id IN NUMBER
3978 ,x_return_status OUT NOCOPY VARCHAR2
3979 ,x_msg_data OUT NOCOPY VARCHAR2
3980 ,x_msg_count OUT NOCOPY NUMBER
3981 )
3982 IS
3983 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Resale_Line_Mapping';
3984 l_api_version CONSTANT NUMBER := 1.0;
3985 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3986 --
3987 l_return_status VARCHAR2(30);
3988 l_msg_data VARCHAR2(2000);
3989 l_msg_count NUMBER;
3990
3991 l_obj_ver_num NUMBER := 1;
3992 l_org_id NUMBER;
3993 l_batch_org_id NUMBER; -- bug # 5997978 fixed
3994 l_map_id NUMBER;
3995 BEGIN
3996 -- Standard BEGIN of API savepoint
3997 SAVEPOINT Insert_Resale_Line_Mapping;
3998 -- Standard call to check for call compatibility.
3999 IF NOT FND_API.Compatible_API_Call (
4000 l_api_version,
4001 p_api_version,
4002 l_api_name,
4003 G_PKG_NAME)
4004 THEN
4005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4006 END IF;
4007
4008 -- Debug Message
4009 IF OZF_DEBUG_HIGH_ON THEN
4010 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
4011 END IF;
4012
4013 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
4014 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
4015 FND_MSG_PUB.initialize;
4016 END IF;
4017
4018 -- Initialize API return status to sucess
4019 x_return_status := FND_API.G_RET_STS_SUCCESS;
4020 OPEN g_map_id_csr;
4021 FETCH g_map_id_csr INTO l_map_id;
4022 CLOSE g_map_id_csr;
4023
4024 -- Start: bug # 5997978 fixed
4025 OPEN g_resale_batch_org_id_csr(p_resale_batch_id);
4026 FETCH g_resale_batch_org_id_csr INTO l_batch_org_id;
4027 CLOSE g_resale_batch_org_id_csr;
4028 l_org_id := MO_GLOBAL.get_valid_org(l_batch_org_id);
4029 IF (l_batch_org_id IS NULL OR l_org_id IS NULL) THEN
4030 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_ORG_ID_NOTFOUND');
4031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4032 END IF;
4033 -- End: bug # 5997978 fixed
4034
4035 -- INSERT INTO mapping table
4036 OZF_RESALE_BATCH_LINE_MAPS_PKG.Insert_Row(
4037 px_resale_batch_line_map_id => l_map_id,
4038 p_resale_batch_id => p_resale_batch_id,
4039 p_resale_line_id => p_line_id,
4040 px_object_version_number => l_obj_ver_num,
4041 p_last_update_date => SYSdate,
4042 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
4043 p_creation_date => SYSdate,
4044 p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
4045 p_created_by => NVL(FND_GLOBAL.user_id,-1),
4046 p_last_update_login => NVL(FND_GLOBAL.conc_login_id,-1),
4047 p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
4048 p_program_update_date => SYSdate,
4049 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
4050 p_created_from => NULL,
4051 p_attribute_category => NULL,
4052 p_attribute1 => NULL,
4053 p_attribute2 => NULL,
4054 p_attribute3 => NULL,
4055 p_attribute4 => NULL,
4056 p_attribute5 => NULL,
4057 p_attribute6 => NULL,
4058 p_attribute7 => NULL,
4059 p_attribute8 => NULL,
4060 p_attribute9 => NULL,
4061 p_attribute10 => NULL,
4062 p_attribute11 => NULL,
4063 p_attribute12 => NULL,
4064 p_attribute13 => NULL,
4065 p_attribute14 => NULL,
4066 p_attribute15 => NULL,
4067 px_org_id => l_org_id);
4068
4069 -- Debug Message
4070 IF OZF_DEBUG_HIGH_ON THEN
4071 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
4072 END IF;
4073
4074 --Standard call to get message count AND IF count=1, get the message
4075 FND_MSG_PUB.Count_And_Get (
4076 p_encoded => FND_API.G_FALSE,
4077 p_count => x_msg_count,
4078 p_data => x_msg_data
4079 );
4080 EXCEPTION
4081 WHEN FND_API.G_EXC_ERROR THEN
4082 ROLLBACK TO Insert_Resale_Line_Mapping;
4083 x_return_status := FND_API.G_RET_STS_ERROR;
4084 -- Standard call to get message count AND IF count=1, get the message
4085 FND_MSG_PUB.Count_And_Get (
4086 p_encoded => FND_API.G_FALSE,
4087 p_count => x_msg_count,
4088 p_data => x_msg_data
4089 );
4090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4091 ROLLBACK TO Insert_Resale_Line_Mapping;
4092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4093 -- Standard call to get message count AND IF count=1, get the message
4094 FND_MSG_PUB.Count_And_Get (
4095 p_encoded => FND_API.G_FALSE,
4096 p_count => x_msg_count,
4097 p_data => x_msg_data
4098 );
4099 WHEN OTHERS THEN
4100 ROLLBACK TO Insert_Resale_Line_Mapping;
4101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4102 IF OZF_UNEXP_ERROR THEN
4103 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4104 END IF;
4105 -- Standard call to get message count AND IF count=1, get the message
4106 FND_MSG_PUB.Count_And_Get (
4107 p_encoded => FND_API.G_FALSE,
4108 p_count => x_msg_count,
4109 p_data => x_msg_data
4110 );
4111 END Insert_Resale_Line_Mapping;
4112
4113 ---------------------------------------------------------------------
4114 -- PROCEDURE
4115 -- Delete_Log
4116 --
4117 -- PURPOSE
4118 -- This procedure delets the log for all open lines of batch
4119 --
4120 -- PARAMETERS
4121 -- p_resale_batch_id IN number
4122 -- x_return_status out VARCHAR2
4123 --
4124 -- NOTES
4125 -----------------------------------------------------------------------
4126 PROCEDURE Delete_Log(
4127 p_api_version IN NUMBER
4128 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4129 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4130 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4131 ,p_resale_batch_id IN NUMBER
4132 ,x_return_status OUT NOCOPY VARCHAR2
4133 ,x_msg_data OUT NOCOPY VARCHAR2
4134 ,x_msg_count OUT NOCOPY NUMBER
4135 )
4136 IS
4137 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Log';
4138 l_api_version CONSTANT NUMBER := 1.0;
4139 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4140 --
4141 l_return_status VARCHAR2(30);
4142 l_msg_data VARCHAR2(2000);
4143 l_msg_count NUMBER;
4144
4145 BEGIN
4146 -- Standard BEGIN of API savepoint
4147 SAVEPOINT Delete_Log;
4148 -- Standard call to check for call compatibility.
4149 IF NOT FND_API.Compatible_API_Call (
4150 l_api_version,
4151 p_api_version,
4152 l_api_name,
4153 G_PKG_NAME)
4154 THEN
4155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4156 END IF;
4157
4158 -- Debug Message
4159 IF OZF_DEBUG_HIGH_ON THEN
4160 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
4161 END IF;
4162
4163 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
4164 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
4165 FND_MSG_PUB.initialize;
4166 END IF;
4167
4168 -- Initialize API return status to sucess
4169 x_return_status := FND_API.G_RET_STS_SUCCESS;
4170
4171 -- delete batch logs
4172 DELETE FROM OZF_RESALE_LOGS
4173 WHERE resale_id = p_resale_batch_id
4174 AND resale_id_type = G_ID_TYPE_BATCH;
4175
4176 -- delete interface logs
4177 DELETE FROM OZF_RESALE_LOGS a
4178 WHERE exists (
4179 SELECT 1
4180 FROM OZF_RESALE_LINES_INT b
4181 WHERE b.resale_batch_id = p_resale_batch_id
4182 AND a.resale_id = b.resale_line_int_id
4183 AND a.resale_id_type = G_ID_TYPE_IFACE
4184 );
4185
4186 -- Debug Message
4187 IF OZF_DEBUG_HIGH_ON THEN
4188 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
4189 END IF;
4190
4191 --Standard call to get message count AND IF count=1, get the message
4192 FND_MSG_PUB.Count_And_Get (
4193 p_encoded => FND_API.G_FALSE,
4194 p_count => x_msg_count,
4195 p_data => x_msg_data
4196 );
4197 EXCEPTION
4198 WHEN FND_API.G_EXC_ERROR THEN
4199 ROLLBACK TO Delete_Log;
4200 x_return_status := FND_API.G_RET_STS_ERROR;
4201 -- Standard call to get message count AND IF count=1, get the message
4202 FND_MSG_PUB.Count_And_Get (
4203 p_encoded => FND_API.G_FALSE,
4204 p_count => x_msg_count,
4205 p_data => x_msg_data
4206 );
4207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4208 ROLLBACK TO Delete_Log;
4209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4210 -- Standard call to get message count AND IF count=1, get the message
4211 FND_MSG_PUB.Count_And_Get (
4212 p_encoded => FND_API.G_FALSE,
4213 p_count => x_msg_count,
4214 p_data => x_msg_data
4215 );
4216 WHEN OTHERS THEN
4217 ROLLBACK TO Delete_Log;
4218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4219 IF OZF_UNEXP_ERROR THEN
4220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4221 END IF;
4222 -- Standard call to get message count AND IF count=1, get the message
4223 FND_MSG_PUB.Count_And_Get (
4224 p_encoded => FND_API.G_FALSE,
4225 p_count => x_msg_count,
4226 p_data => x_msg_data
4227 );
4228 END Delete_Log;
4229
4230
4231 ---------------------------------------------------------------------
4232 -- PROCEDURE
4233 -- Create_Party
4234 --
4235 -- PURPOSE
4236 -- This procedure creates party, party site, party site use and relationship
4237 --
4238 -- PARAMETERS
4239 -- px_party_rec IN OUT party_rec_type
4240 -- x_return_status out VARCHAR2
4241 --
4242 -- NOTES
4243 --
4244 ---------------------------------------------------------------------
4245 PROCEDURE Create_Party
4246 ( p_api_version IN NUMBER
4247 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4248 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4249 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4250 ,px_party_rec IN OUT NOCOPY party_rec_type
4251 ,x_return_status OUT NOCOPY VARCHAR2
4252 ,x_msg_count OUT NOCOPY NUMBER
4253 ,x_msg_data OUT NOCOPY VARCHAR2
4254 )
4255 IS
4256
4257 l_api_name CONSTANT VARCHAR2(30) := 'Create_Party';
4258 l_api_version CONSTANT NUMBER := 1.0;
4259 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4260
4261 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
4262 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
4263 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
4264 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
4265 l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
4266
4267 l_party_id NUMBER;
4268 l_party_number VARCHAR2(2000);
4269 l_party_no VARCHAR2(2000);
4270 l_profile_id NUMBER;
4271 l_location_id NUMBER;
4272 l_party_site_id NUMBER;
4273 l_party_site_number VARCHAR2(2000);
4274 l_party_site_use_id NUMBER;
4275 l_relationship_id NUMBER;
4276
4277 BEGIN
4278 -- Standard BEGIN of API savepoint
4279 SAVEPOINT Party_Create;
4280 -- Standard call to check for call compatibility.
4281 IF NOT FND_API.Compatible_API_Call (
4282 l_api_version,
4283 p_api_version,
4284 l_api_name,
4285 G_PKG_NAME)
4286 THEN
4287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4288 END IF;
4289
4290 --Initialize message LIST IF p_init_msg_LIST IS TRUE.
4291 IF FND_API.To_Boolean (p_init_msg_LIST) THEN
4292 FND_MSG_PUB.initialize;
4293 END IF;
4294
4295 -- Debug Message
4296 IF OZF_DEBUG_HIGH_ON THEN
4297 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
4298 END IF;
4299
4300 x_return_status := FND_API.g_ret_sts_success;
4301
4302 -- Create Organization
4303 IF px_party_rec.name IS NOT NULL THEN
4304 IF OZF_DEBUG_LOW_ON THEN
4305 OZF_UTILITY_PVT.debug_message('NAME '|| px_party_rec.name);
4306 END IF;
4307 l_organization_rec.organization_name := px_party_rec.name;
4308 -- Bug 4630628 (+)
4309 --l_organization_rec.created_by_module := 'TCA_V2_API';
4310 l_organization_rec.created_by_module := 'OZF_RESALE';
4311 -- Bug 4630628 (-)
4312 l_organization_rec.party_rec.status := 'A';
4313 l_organization_rec.application_id := 682;
4314
4315 HZ_PARTY_V2PUB.create_organization(
4316 p_init_msg_list => FND_API.G_FALSE,
4317 p_organization_rec => l_organization_rec,
4318 x_return_status => x_return_status,
4319 x_msg_count => x_msg_count,
4320 x_msg_data => x_msg_data,
4321 x_party_id => px_party_rec.party_id,
4322 x_party_number => l_party_number,
4323 x_profile_id => l_profile_id);
4324
4325 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4326 RAISE FND_API.G_EXC_ERROR;
4327 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4329 END IF;
4330 IF OZF_DEBUG_LOW_ON THEN
4331 OZF_UTILITY_PVT.debug_message('Party Id '||px_party_rec.party_id);
4332 END IF;
4333 END IF;
4334
4335 -- Create Location
4336 IF px_party_rec.address IS NOT NULL THEN
4337
4338 l_location_rec.country := px_party_rec.country;
4339 l_location_rec.address1 := px_party_rec.address;
4340 l_location_rec.city := px_party_rec.city;
4341 l_location_rec.postal_code := px_party_rec.postal_code;
4342 l_location_rec.state := px_party_rec.state;
4343 -- Bug 4630628 (+)
4344 --l_location_rec.created_by_module := 'TCA_V2_API';
4345 l_location_rec.created_by_module := 'OZF_RESALE';
4346 -- Bug 4630628 (-)
4347
4348 HZ_LOCATION_V2PUB.create_location(
4349 p_init_msg_list => FND_API.G_FALSE,
4350 p_location_rec => l_location_rec,
4351 x_return_status => x_return_status,
4352 x_msg_count => x_msg_count,
4353 x_msg_data => x_msg_data,
4354 x_location_id => l_location_id);
4355
4356 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4357 RAISE FND_API.G_EXC_ERROR;
4358 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4360 END IF;
4361 IF OZF_DEBUG_LOW_ON THEN
4362 OZF_UTILITY_PVT.debug_message('Location ID '|| l_location_id);
4363 END IF;
4364 END IF;
4365
4366 -- Create Party Site
4367 IF px_party_rec.party_id IS NOT NULL AND
4368 l_location_id IS NOT NULL THEN
4369
4370 l_party_site_rec.party_id := px_party_rec.party_id;
4371 l_party_site_rec.location_id := l_location_id;
4372 l_party_site_rec.identifying_address_flag := 'Y';
4373 l_party_site_rec.status := 'A';
4374 -- Bug 4630628 (+)
4375 --l_party_site_rec.created_by_module := 'TCA_V2_API';
4376 l_party_site_rec.created_by_module := 'OZF_RESALE';
4377 -- Bug 4630628 (-)
4378
4379 HZ_PARTY_SITE_V2PUB.create_party_site (
4380 p_init_msg_list => FND_API.G_FALSE,
4381 p_party_site_rec => l_party_site_rec,
4382 x_return_status => x_return_status,
4383 x_msg_count => x_msg_count,
4384 x_msg_data => x_msg_data,
4385 x_party_site_id => px_party_rec.party_site_id,
4386 x_party_site_number=> l_party_site_number);
4387
4388 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4389 RAISE FND_API.G_EXC_ERROR;
4390 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4392 END IF;
4393 IF OZF_DEBUG_LOW_ON THEN
4394 OZF_UTILITY_PVT.debug_message('Party Site ID '|| px_party_rec.party_site_id);
4395 END IF;
4396 END IF;
4397
4398 -- Create Party Site Use
4399 IF px_party_rec.party_site_id IS NOT NULL THEN
4400
4401 l_party_site_use_rec.party_site_id := px_party_rec.party_site_id;
4402 -- Bug 4630628 (+)
4403 --l_party_site_use_rec.created_by_module := 'TCA_V2_API';
4404 l_party_site_use_rec.created_by_module := 'OZF_RESALE';
4405 -- Bug 4630628 (-)
4406 l_party_site_use_rec.application_id := 682;
4407
4408
4409 IF (px_party_rec.site_use_code is null OR px_party_rec.site_use_code = FND_API.G_MISS_CHAR) THEN
4410 l_party_site_use_rec.site_use_type := 'BILL_TO';
4411 ELSE
4412 l_party_site_use_rec.site_use_type := px_party_rec.site_use_code; -- 'BILL_TO';
4413 END IF;
4414
4415 HZ_PARTY_SITE_V2PUB.create_party_site_use(
4416 p_init_msg_list => FND_API.G_FALSE,
4417 p_party_site_use_rec => l_party_site_use_rec,
4418 x_return_status => x_return_status,
4419 x_msg_count => x_msg_count,
4420 x_msg_data => x_msg_data,
4421 x_party_site_use_id => px_party_rec.party_site_use_id
4422 );
4423 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4424 RAISE FND_API.G_EXC_ERROR;
4425 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4427 END IF;
4428 IF OZF_DEBUG_LOW_ON THEN
4429 OZF_UTILITY_PVT.debug_message('Party Site Use ID '|| px_party_rec.party_site_use_id);
4430 END IF;
4431 END IF;
4432
4433 -- Create Relationship
4434 IF px_party_rec.party_id IS NOT NULL AND
4435 px_party_rec.partner_party_id IS NOT NULL THEN
4436
4437 l_relationship_rec.subject_id := px_party_rec.party_id;
4438 l_relationship_rec.subject_type := 'ORGANIZATION';
4439 l_relationship_rec.subject_table_name := 'HZ_PARTIES';
4440 l_relationship_rec.object_id := px_party_rec.partner_party_id;
4441 l_relationship_rec.object_type := 'ORGANIZATION';
4442 l_relationship_rec.object_table_name := 'HZ_PARTIES';
4443 l_relationship_rec.relationship_type := 'CUSTOMER/SELLER';
4444 l_relationship_rec.start_date := sysdate;
4445 l_relationship_rec.relationship_code := 'CUSTOMER_OF';
4446 -- Bug 4630628 (+)
4447 --l_relationship_rec.created_by_module := 'TCA_V2_API';
4448 l_relationship_rec.created_by_module := 'OZF_RESALE';
4449 -- Bug 4630628 (-)
4450 l_relationship_rec.application_id := 682;
4451 l_relationship_rec.status := 'A';
4452
4453
4454 HZ_RELATIONSHIP_V2PUB.create_relationship(
4455 p_init_msg_list => FND_API.G_FALSE,
4456 p_relationship_rec => l_relationship_rec,
4457 x_relationship_id => l_relationship_id,
4458 x_party_id => l_party_id,
4459 x_party_number => l_party_no,
4460 x_return_status => x_return_status,
4461 x_msg_count => x_msg_count,
4462 x_msg_data => x_msg_data,
4463 p_create_org_contact => 'Y'
4464 );
4465 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4466 RAISE FND_API.G_EXC_ERROR;
4467 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4469 END IF;
4470 IF OZF_DEBUG_LOW_ON THEN
4471 OZF_UTILITY_PVT.debug_message('Relationship ID '|| l_relationship_id);
4472 END IF;
4473 END IF;
4474
4475
4476
4477 -- Debug Message
4478 IF OZF_DEBUG_HIGH_ON THEN
4479 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
4480 END IF;
4481 --Standard call to get message count AND IF count=1, get the message
4482 FND_MSG_PUB.Count_And_Get (
4483 p_encoded => FND_API.G_FALSE,
4484 p_count => x_msg_count,
4485 p_data => x_msg_data);
4486
4487 EXCEPTION
4488 WHEN FND_API.G_EXC_ERROR THEN
4489 x_return_status := FND_API.G_RET_STS_ERROR;
4490 -- Standard call to get message count and if count=1, get the message
4491 FND_MSG_PUB.Count_And_Get (
4492 p_encoded => FND_API.G_FALSE,
4493 p_count => x_msg_count,
4494 p_data => x_msg_data
4495 );
4496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4498 -- Standard call to get message count and if count=1, get the message
4499 FND_MSG_PUB.Count_And_Get (
4500 p_encoded => FND_API.G_FALSE,
4501 p_count => x_msg_count,
4502 p_data => x_msg_data
4503 );
4504 WHEN OTHERS THEN
4505 OZF_UTILITY_PVT.debug_message('SQLERRM '|| sqlerrm);
4506
4507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4508 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4509 THEN
4510 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4511 END IF;
4512 -- Standard call to get message count and if count=1, get the message
4513 FND_MSG_PUB.Count_And_Get (
4514 p_encoded => FND_API.G_FALSE,
4515 p_count => x_msg_count,
4516 p_data => x_msg_data
4517 );
4518 END Create_Party;
4519
4520
4521 ---------------------------------------------------------------------
4522 -- PROCEDURE
4523 -- Build_Global_Resale_Rec
4524 --
4525 -- PURPOSE
4526 -- Build Global Resale Record for Pricing Simulation
4527 --
4528 -- PARAMETERS
4529 -- p_caller_type IN VARCHAR2
4530 -- p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
4531 -- p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
4532 --
4533 -- NOTES
4534 --
4535 ---------------------------------------------------------------------
4536 PROCEDURE Build_Global_Resale_Rec
4537 ( p_api_version IN NUMBER
4538 ,p_init_msg_list IN VARCHAR2
4539 ,p_commit IN VARCHAR2
4540 ,p_validation_level IN NUMBER
4541 ,p_caller_type IN VARCHAR2
4542 ,p_line_index IN NUMBER
4543 ,p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
4544 ,p_resale_header_rec IN OZF_RESALE_HEADERS%ROWTYPE
4545 ,p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
4546 ,x_return_status OUT NOCOPY VARCHAR2
4547 ,x_msg_count OUT NOCOPY NUMBER
4548 ,x_msg_data OUT NOCOPY VARCHAR2
4549 )
4550 IS
4551 l_api_name CONSTANT VARCHAR2(30) := 'Build_Global_Resale_Rec';
4552 l_api_version CONSTANT NUMBER := 1.0;
4553 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4554 --
4555 l_qp_context_request_id NUMBER;
4556
4557 BEGIN
4558 -- Debug Message
4559 IF OZF_DEBUG_HIGH_ON THEN
4560 ozf_utility_pvt.debug_message(l_full_name||': start');
4561 END IF;
4562
4563 IF NOT FND_API.Compatible_API_Call (
4564 l_api_version,
4565 p_api_version,
4566 l_api_name,
4567 G_PKG_NAME)
4568 THEN
4569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4570 END IF;
4571
4572 --Initialize message list if p_init_msg_list is TRUE.
4573 IF FND_API.To_Boolean (p_init_msg_list) THEN
4574 FND_MSG_PUB.initialize;
4575 END IF;
4576
4577 x_return_status := FND_API.G_RET_STS_SUCCESS;
4578
4579 IF p_caller_type = 'IFACE' AND
4580 p_resale_line_int_rec.resale_line_int_id IS NULL THEN
4581 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4582 FND_MESSAGE.set_name('OZF', 'OZF_RESALE_INT_RECD_NULL');
4583 FND_MSG_PUB.add;
4584 END IF;
4585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4586 END IF;
4587
4588 IF p_caller_type = 'RESALE' AND
4589 p_resale_line_rec.resale_line_id IS NULL THEN
4590 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4591 FND_MESSAGE.set_name('OZF', 'OZF_RESALE_RECD_NULL');
4592 FND_MSG_PUB.add;
4593 END IF;
4594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4595 END IF;
4596
4597 l_qp_context_request_id := QP_Price_Request_Context.Get_Request_Id;
4598
4599 IF p_caller_type = 'IFACE' THEN
4600 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.qp_context_request_id := l_qp_context_request_id;
4601 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_index := p_line_index;
4602 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.resale_table_type := 'IFACE';
4603 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_id := p_resale_line_int_rec.resale_line_int_id;
4604 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.resale_transfer_type := p_resale_line_int_rec.resale_transfer_type;
4605 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.product_transfer_movement_type := p_resale_line_int_rec.product_transfer_movement_type;
4606 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.product_transfer_date := p_resale_line_int_rec.product_transfer_date;
4607 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.tracing_flag := p_resale_line_int_rec.tracing_flag;
4608 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.sold_from_cust_account_id := p_resale_line_int_rec.sold_from_cust_account_id;
4609 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.sold_from_site_id := p_resale_line_int_rec.sold_from_site_id;
4610 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.sold_from_contact_party_id := p_resale_line_int_rec.sold_from_contact_party_id;
4611 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_from_cust_account_id := p_resale_line_int_rec.ship_from_cust_account_id;
4612 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_from_site_id := p_resale_line_int_rec.ship_from_site_id;
4613 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_from_contact_party_id := p_resale_line_int_rec.ship_from_contact_party_id;
4614 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.bill_to_party_id := p_resale_line_int_rec.bill_to_party_id;
4615 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.bill_to_party_site_id := p_resale_line_int_rec.bill_to_party_site_id;
4616 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.bill_to_contact_party_id := p_resale_line_int_rec.bill_to_contact_party_id;
4617 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_to_party_id := p_resale_line_int_rec.ship_to_party_id;
4618 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_to_party_site_id := p_resale_line_int_rec.ship_to_party_site_id;
4619 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_to_contact_party_id := p_resale_line_int_rec.ship_to_contact_party_id;
4620 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_party_id := p_resale_line_int_rec.end_cust_party_id;
4621 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_site_use_id := p_resale_line_int_rec.end_cust_site_use_id;
4622 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_site_use_code := p_resale_line_int_rec.end_cust_site_use_code;
4623 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_party_site_id := p_resale_line_int_rec.end_cust_party_site_id;
4624 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_contact_party_id := p_resale_line_int_rec.end_cust_contact_party_id;
4625 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.data_source_code := p_resale_line_int_rec.data_source_code;
4626 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute_category := p_resale_line_int_rec.header_attribute_category;
4627 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute1 := p_resale_line_int_rec.header_attribute1;
4628 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute2 := p_resale_line_int_rec.header_attribute2;
4629 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute3 := p_resale_line_int_rec.header_attribute3;
4630 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute4 := p_resale_line_int_rec.header_attribute4;
4631 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute5 := p_resale_line_int_rec.header_attribute5;
4632 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute6 := p_resale_line_int_rec.header_attribute6;
4633 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute7 := p_resale_line_int_rec.header_attribute7;
4634 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute8 := p_resale_line_int_rec.header_attribute8;
4635 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute9 := p_resale_line_int_rec.header_attribute9;
4636 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute10 := p_resale_line_int_rec.header_attribute10;
4637 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute11 := p_resale_line_int_rec.header_attribute11;
4638 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute12 := p_resale_line_int_rec.header_attribute12;
4639 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute13 := p_resale_line_int_rec.header_attribute13;
4640 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute14 := p_resale_line_int_rec.header_attribute14;
4641 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute15 := p_resale_line_int_rec.header_attribute15;
4642 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute_category := p_resale_line_int_rec.line_attribute_category;
4643 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute1 := p_resale_line_int_rec.line_attribute1;
4644 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute2 := p_resale_line_int_rec.line_attribute2;
4645 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute3 := p_resale_line_int_rec.line_attribute3;
4646 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute4 := p_resale_line_int_rec.line_attribute4;
4647 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute5 := p_resale_line_int_rec.line_attribute5;
4648 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute6 := p_resale_line_int_rec.line_attribute6;
4649 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute7 := p_resale_line_int_rec.line_attribute7;
4650 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute8 := p_resale_line_int_rec.line_attribute8;
4651 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute9 := p_resale_line_int_rec.line_attribute9;
4652 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute10 := p_resale_line_int_rec.line_attribute10;
4653 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute11 := p_resale_line_int_rec.line_attribute11;
4654 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute12 := p_resale_line_int_rec.line_attribute12;
4655 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute13 := p_resale_line_int_rec.line_attribute13;
4656 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute14 := p_resale_line_int_rec.line_attribute14;
4657 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute15 := p_resale_line_int_rec.line_attribute15;
4658
4659 ELSIF p_caller_type = 'RESALE' THEN
4660 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.qp_context_request_id := l_qp_context_request_id;
4661 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_index := p_line_index;
4662 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.resale_table_type := 'RESALE';
4663 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_id := p_resale_line_rec.resale_line_id;
4664 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.resale_transfer_type := p_resale_line_rec.resale_transfer_type;
4665 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.product_transfer_movement_type := p_resale_line_rec.product_transfer_movement_type;
4666 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.product_transfer_date := p_resale_line_rec.product_transfer_date;
4667 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.tracing_flag := p_resale_line_rec.tracing_flag;
4668 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.sold_from_cust_account_id := p_resale_line_rec.sold_from_cust_account_id;
4669 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.sold_from_site_id := p_resale_line_rec.sold_from_site_id;
4670 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.sold_from_contact_party_id := p_resale_line_rec.sold_from_contact_party_id;
4671 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_from_cust_account_id := p_resale_line_rec.ship_from_cust_account_id;
4672 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_from_site_id := p_resale_line_rec.ship_from_site_id;
4673 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_from_contact_party_id := p_resale_line_rec.ship_from_contact_party_id;
4674 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.bill_to_party_id := p_resale_line_rec.bill_to_party_id;
4675 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.bill_to_party_site_id := p_resale_line_rec.bill_to_party_site_id;
4676 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.bill_to_contact_party_id := p_resale_line_rec.bill_to_contact_party_id;
4677 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_to_party_id := p_resale_line_rec.ship_to_party_id;
4678 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_to_party_site_id := p_resale_line_rec.ship_to_party_site_id;
4679 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.ship_to_contact_party_id := p_resale_line_rec.ship_to_contact_party_id;
4680 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_party_id := p_resale_line_rec.end_cust_party_id;
4681 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_site_use_id := p_resale_line_rec.end_cust_site_use_id;
4682 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_site_use_code := p_resale_line_rec.end_cust_site_use_code;
4683 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_party_site_id := p_resale_line_rec.end_cust_party_site_id;
4684 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.end_cust_contact_party_id := p_resale_line_rec.end_cust_contact_party_id;
4685 --OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.data_source_code := ?
4686 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute_category := p_resale_header_rec.header_attribute_category;
4687 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute1 := p_resale_header_rec.header_attribute1;
4688 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute2 := p_resale_header_rec.header_attribute2;
4689 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute3 := p_resale_header_rec.header_attribute3;
4690 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute4 := p_resale_header_rec.header_attribute4;
4691 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute5 := p_resale_header_rec.header_attribute5;
4692 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute6 := p_resale_header_rec.header_attribute6;
4693 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute7 := p_resale_header_rec.header_attribute7;
4694 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute8 := p_resale_header_rec.header_attribute8;
4695 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute9 := p_resale_header_rec.header_attribute9;
4696 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute10 := p_resale_header_rec.header_attribute10;
4697 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute11 := p_resale_header_rec.header_attribute11;
4698 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute12 := p_resale_header_rec.header_attribute12;
4699 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute13 := p_resale_header_rec.header_attribute13;
4700 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute14 := p_resale_header_rec.header_attribute14;
4701 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.header_attribute15 := p_resale_header_rec.header_attribute15;
4702 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute_category := p_resale_line_rec.line_attribute_category;
4703 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute1 := p_resale_line_rec.line_attribute1;
4704 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute2 := p_resale_line_rec.line_attribute2;
4705 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute3 := p_resale_line_rec.line_attribute3;
4706 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute4 := p_resale_line_rec.line_attribute4;
4707 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute5 := p_resale_line_rec.line_attribute5;
4708 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute6 := p_resale_line_rec.line_attribute6;
4709 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute7 := p_resale_line_rec.line_attribute7;
4710 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute8 := p_resale_line_rec.line_attribute8;
4711 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute9 := p_resale_line_rec.line_attribute9;
4712 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute10 := p_resale_line_rec.line_attribute10;
4713 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute11 := p_resale_line_rec.line_attribute11;
4714 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute12 := p_resale_line_rec.line_attribute12;
4715 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute13 := p_resale_line_rec.line_attribute13;
4716 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute14 := p_resale_line_rec.line_attribute14;
4717 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_REC.line_attribute15 := p_resale_line_rec.line_attribute15;
4718 END IF;
4719
4720
4721 FND_MSG_PUB.Count_And_Get (
4722 p_encoded => FND_API.G_FALSE,
4723 p_count => x_msg_count,
4724 p_data => x_msg_data
4725 );
4726
4727 -- Debug Message
4728 IF OZF_DEBUG_HIGH_ON THEN
4729 ozf_utility_pvt.debug_message(l_full_name||': end');
4730 END IF;
4731 EXCEPTION
4732 WHEN FND_API.G_EXC_ERROR THEN
4733 x_return_status := FND_API.G_RET_STS_ERROR;
4734 FND_MSG_PUB.Count_And_Get (
4735 p_encoded => FND_API.G_FALSE,
4736 p_count => x_msg_count,
4737 p_data => x_msg_data
4738 );
4739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4741 FND_MSG_PUB.Count_And_Get (
4742 p_encoded => FND_API.G_FALSE,
4743 p_count => x_msg_count,
4744 p_data => x_msg_data
4745 );
4746 WHEN OTHERS THEN
4747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4748 FND_MSG_PUB.Count_And_Get (
4749 p_encoded => FND_API.G_FALSE,
4750 p_count => x_msg_count,
4751 p_data => x_msg_data
4752 );
4753 END Build_Global_Resale_Rec;
4754
4755 END OZF_RESALE_COMMON_PVT;