1 PACKAGE BODY OZF_Trade_Profile_PVT as
2 /* $Header: ozfvctpb.pls 120.5.12010000.3 2010/04/26 07:13:17 kpatro ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Trade_Profile_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- 4/5/2010 nepanda Bug 9539273 - 12.1.3 multi currency - trade profile / autopay issues
15 -- 4/25/2010 kpatro ER#9453443 - RBS PAD/ CLAIM OFFER CODE CREATE/ UPDATE FLOW CHANGES
16 -- ===============================================================
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Trade_Profile_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvctpb.pls';
19
20 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
21
22 CURSOR g_functional_currency_code_csr IS
23 SELECT gs.currency_code
24 FROM gl_sets_of_books gs,
25 ozf_sys_parameters osp
26 WHERE gs.set_of_books_id = osp.set_of_books_id
27 AND osp.org_id = MO_GLOBAL.get_current_org_id();
28
29 -------------------------------------------------------------------------------
30 PROCEDURE Complete_trade_profile_Rec
31 (
32 p_trade_profile_rec IN trade_profile_rec_type,
33 x_complete_rec OUT NOCOPY trade_profile_rec_type
34 )
35 IS
36
37 CURSOR c_trade IS
38 SELECT *
39 FROM ozf_cust_trd_prfls_all
40 WHERE trade_profile_id = p_trade_profile_rec.trade_profile_id;
41 l_trade_profile_rec c_trade%rowtype;
42
43 BEGIN
44 x_complete_rec := p_trade_profile_rec;
45 OPEN c_trade;
46 FETCH c_trade INTO l_trade_profile_rec;
47 IF c_trade%NOTFOUND THEN
48 CLOSE c_trade;
49 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
50 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_PROFILE_MISSING');
51 FND_MSG_PUB.add;
52 END IF;
53 RAISE FND_API.g_exc_error;
54 END IF;
55 CLOSE c_trade;
56
57 -- This procedure should complete the record by going through all the items in the incoming record.
58
59 -- IF p_trade_profile_rec.last_update_date = FND_API.g_miss_date THEN
60 -- x_complete_rec.last_update_date := l_trade_profile_rec.last_update_date;
61 -- END IF;
62
63 -- IF p_trade_profile_rec.object_version_number = FND_API.g_miss_num THEN
64 -- x_complete_rec.object_version_number := l_trade_profile_rec.object_version_number;
65 -- END IF;
66
67 -- IF p_trade_profile_rec.last_updated_by = FND_API.g_miss_num THEN
68 -- x_complete_rec.last_updated_by := l_trade_profile_rec.last_updated_by;
69 -- END IF;
70
71 -- IF p_trade_profile_rec.creation_date = FND_API.g_miss_date THEN
72 -- x_complete_rec.creation_date := l_trade_profile_rec.creation_date;
73 -- END IF;
74
75 -- IF p_trade_profile_rec.last_update_login = FND_API.g_miss_num THEN
76 -- x_complete_rec.last_update_login := l_trade_profile_rec.last_update_login;
77 -- END IF;
78
79 IF p_trade_profile_rec.request_id = FND_API.g_miss_num THEN
80 x_complete_rec.request_id := null;
81 END IF;
82 IF p_trade_profile_rec.request_id is null THEN
83 x_complete_rec.request_id := l_trade_profile_rec.request_id;
84 END IF;
85
86
87 IF p_trade_profile_rec.program_application_id = FND_API.g_miss_num THEN
88 x_complete_rec.program_application_id := null;
89 END IF;
90 IF p_trade_profile_rec.program_application_id is null THEN
91 x_complete_rec.program_application_id := l_trade_profile_rec.program_application_id;
92 END IF;
93
94
95 IF p_trade_profile_rec.program_update_date = FND_API.g_miss_date THEN
96 x_complete_rec.program_update_date := null;
97 END IF;
98 IF p_trade_profile_rec.program_update_date is null THEN
99 x_complete_rec.program_update_date := l_trade_profile_rec.program_update_date;
100 END IF;
101
102
103 IF p_trade_profile_rec.program_id = FND_API.g_miss_num THEN
104 x_complete_rec.program_id := null;
105 END IF;
106 IF p_trade_profile_rec.program_id is null THEN
107 x_complete_rec.program_id := l_trade_profile_rec.program_id;
108 END IF;
109
110
111 IF p_trade_profile_rec.created_from = FND_API.g_miss_char THEN
112 x_complete_rec.created_from := null;
113 END IF;
114 IF p_trade_profile_rec.created_from is null THEN
115 x_complete_rec.created_from := l_trade_profile_rec.created_from;
116 END IF;
117
118
119 IF p_trade_profile_rec.party_id = FND_API.g_miss_num THEN
120 x_complete_rec.party_id := null;
121 END IF;
122 IF p_trade_profile_rec.party_id is null THEN
123 x_complete_rec.party_id := l_trade_profile_rec.party_id;
124 END IF;
125
126 IF p_trade_profile_rec.site_use_id = FND_API.g_miss_num THEN
127 x_complete_rec.site_use_id := null;
128 END IF;
129 IF p_trade_profile_rec.site_use_id is null THEN
130 x_complete_rec.site_use_id := l_trade_profile_rec.site_use_id;
131 END IF;
132
133 IF p_trade_profile_rec.autopay_flag = FND_API.g_miss_char THEN
134 x_complete_rec.autopay_flag := null;
135 END IF;
136 IF p_trade_profile_rec.autopay_flag is null THEN
137 x_complete_rec.autopay_flag := l_trade_profile_rec.autopay_flag;
138 END IF;
139
140
141 IF p_trade_profile_rec.claim_currency = FND_API.g_miss_char THEN
142 x_complete_rec.claim_currency := null;
143 END IF;
144 IF p_trade_profile_rec.claim_currency is null THEN
145 x_complete_rec.claim_currency := l_trade_profile_rec.claim_currency;
146 END IF;
147
148 IF p_trade_profile_rec.print_flag = FND_API.g_miss_char THEN
149 x_complete_rec.print_flag := null;
150 END IF;
151 IF p_trade_profile_rec.print_flag is null THEN
152 x_complete_rec.print_flag := l_trade_profile_rec.print_flag;
153 END IF;
154
155 IF p_trade_profile_rec.internet_deal_view_flag = FND_API.g_miss_char THEN
156 x_complete_rec.internet_deal_view_flag := null;
157 END IF;
158 IF p_trade_profile_rec.internet_deal_view_flag is null THEN
159 x_complete_rec.internet_deal_view_flag := l_trade_profile_rec.internet_deal_view_flag;
160 END IF;
161
162
163 IF p_trade_profile_rec.internet_claims_flag = FND_API.g_miss_char THEN
164 x_complete_rec.internet_claims_flag := null;
165 END IF;
166 IF p_trade_profile_rec.internet_claims_flag is null THEN
167 x_complete_rec.internet_claims_flag := l_trade_profile_rec.internet_claims_flag;
168 END IF;
169
170 IF p_trade_profile_rec.payment_method = FND_API.g_miss_char THEN
171 x_complete_rec.payment_method := null;
172 END IF;
173 IF p_trade_profile_rec.payment_method is null THEN
174 x_complete_rec.payment_method := l_trade_profile_rec.payment_method;
175 END IF;
176
177 IF p_trade_profile_rec.discount_type = FND_API.g_miss_char THEN
178 x_complete_rec.discount_type := null;
179 END IF;
180 IF p_trade_profile_rec.discount_type is null THEN
181 x_complete_rec.discount_type := l_trade_profile_rec.discount_type;
182 END IF;
183
184 IF p_trade_profile_rec.cust_account_id = FND_API.g_miss_num THEN
185 x_complete_rec.cust_account_id := null;
186 END IF;
187 IF p_trade_profile_rec.cust_account_id is null THEN
188 x_complete_rec.cust_account_id := l_trade_profile_rec.cust_account_id;
189 END IF;
190
191 IF p_trade_profile_rec.internet_deal_view_flag = FND_API.g_miss_char THEN
192 x_complete_rec.internet_deal_view_flag := null;
193 END IF;
194 IF p_trade_profile_rec.internet_deal_view_flag is null THEN
195 x_complete_rec.internet_deal_view_flag := l_trade_profile_rec.internet_deal_view_flag;
196 END IF;
197
198 IF p_trade_profile_rec.cust_acct_site_id = FND_API.g_miss_num THEN
199 x_complete_rec.cust_acct_site_id := null;
200 END IF;
201 IF p_trade_profile_rec.cust_acct_site_id is null THEN
202 x_complete_rec.cust_acct_site_id := l_trade_profile_rec.cust_acct_site_id;
203 END IF;
204
205 IF p_trade_profile_rec.vendor_id = FND_API.g_miss_num THEN
206 x_complete_rec.vendor_id := null;
207 END IF;
208 IF p_trade_profile_rec.vendor_id is null THEN
209 x_complete_rec.vendor_id := l_trade_profile_rec.vendor_id;
210 END IF;
211
212 IF p_trade_profile_rec.vendor_site_id = FND_API.g_miss_num THEN
213 x_complete_rec.vendor_site_id := null;
214 END IF;
215 IF p_trade_profile_rec.vendor_site_id is null THEN
216 x_complete_rec.vendor_site_id := l_trade_profile_rec.vendor_site_id;
217 END IF;
218
219 IF p_trade_profile_rec.vendor_site_code = FND_API.g_miss_char THEN
220 x_complete_rec.vendor_site_code := null;
221 END IF;
222 IF p_trade_profile_rec.vendor_site_code is null THEN
223 x_complete_rec.vendor_site_code := l_trade_profile_rec.vendor_site_code;
224 END IF;
225
226 IF p_trade_profile_rec.attribute_category = FND_API.g_miss_char THEN
227 x_complete_rec.attribute_category := null;
228 END IF;
229 IF p_trade_profile_rec.attribute_category is null THEN
230 x_complete_rec.attribute_category := l_trade_profile_rec.attribute_category;
231 END IF;
232
233 IF p_trade_profile_rec.context = FND_API.g_miss_char THEN
234 x_complete_rec.context := null;
235 END IF;
236 IF p_trade_profile_rec.context is null THEN
237 x_complete_rec.context := l_trade_profile_rec.context;
238 END IF;
239
240 IF p_trade_profile_rec.org_id = FND_API.g_miss_num THEN
241 x_complete_rec.org_id := null;
242 END IF;
243 IF p_trade_profile_rec.org_id is null THEN
244 x_complete_rec.org_id := l_trade_profile_rec.org_id;
245 END IF;
246
247
248 IF p_trade_profile_rec.days_due = FND_API.g_miss_num THEN
249 x_complete_rec.days_due := null;
250 END IF;
251 IF p_trade_profile_rec.days_due is null THEN
252 x_complete_rec.days_due := l_trade_profile_rec.days_due;
253 END IF;
254
255 IF p_trade_profile_rec.autopay_periodicity = FND_API.g_miss_num THEN
256 x_complete_rec.autopay_periodicity := null;
257 END IF;
258 IF p_trade_profile_rec.autopay_periodicity is null THEN
259 x_complete_rec.autopay_periodicity := l_trade_profile_rec.autopay_periodicity;
260 END IF;
261
262
263 IF p_trade_profile_rec.claim_threshold = FND_API.g_miss_num THEN
264 x_complete_rec.claim_threshold := null;
265 END IF;
266 IF p_trade_profile_rec.claim_threshold is null THEN
267 x_complete_rec.claim_threshold := l_trade_profile_rec.claim_threshold;
268 END IF;
269
270
271 IF p_trade_profile_rec.pos_write_off_threshold = FND_API.g_miss_num THEN
272 x_complete_rec.pos_write_off_threshold := null;
273 END IF;
274 IF p_trade_profile_rec.pos_write_off_threshold is null THEN
275 x_complete_rec.pos_write_off_threshold := l_trade_profile_rec.pos_write_off_threshold;
276 END IF;
277
278 IF p_trade_profile_rec.neg_write_off_threshold = FND_API.g_miss_num THEN
279 x_complete_rec.neg_write_off_threshold := null;
280 END IF;
281 IF p_trade_profile_rec.neg_write_off_threshold is null THEN
282 x_complete_rec.neg_write_off_threshold := l_trade_profile_rec.neg_write_off_threshold;
283 END IF;
284
285 IF p_trade_profile_rec.un_earned_pay_allow_to = FND_API.g_miss_char THEN
286 x_complete_rec.un_earned_pay_allow_to := null;
287 END IF;
288 IF p_trade_profile_rec.un_earned_pay_allow_to is null THEN
289 x_complete_rec.un_earned_pay_allow_to := l_trade_profile_rec.un_earned_pay_allow_to;
290 END IF;
291
292 IF p_trade_profile_rec.un_earned_pay_thold_type = FND_API.g_miss_char THEN
293 x_complete_rec.un_earned_pay_thold_type := null;
294 END IF;
295 IF p_trade_profile_rec.un_earned_pay_thold_type is null THEN
296 x_complete_rec.un_earned_pay_thold_type := l_trade_profile_rec.un_earned_pay_thold_type;
297 END IF;
298
299 IF p_trade_profile_rec.un_earned_pay_threshold = FND_API.g_miss_num THEN
300 x_complete_rec.un_earned_pay_threshold := null;
301 END IF;
302 IF p_trade_profile_rec.un_earned_pay_threshold is null THEN
303 x_complete_rec.un_earned_pay_threshold := l_trade_profile_rec.un_earned_pay_thold_amount;
304 END IF;
305
306 IF p_trade_profile_rec.un_earned_pay_thold_flag = FND_API.g_miss_char THEN
307 x_complete_rec.un_earned_pay_thold_flag := null;
308 END IF;
309 IF p_trade_profile_rec.un_earned_pay_thold_flag is null THEN
310 x_complete_rec.un_earned_pay_thold_flag := l_trade_profile_rec.un_earned_pay_thold_flag;
311 END IF;
312
313 IF p_trade_profile_rec.header_tolerance_calc_code = FND_API.g_miss_char THEN
314 x_complete_rec.header_tolerance_calc_code := null;
315 END IF;
316 IF p_trade_profile_rec.header_tolerance_calc_code is null THEN
317 x_complete_rec.header_tolerance_calc_code := l_trade_profile_rec.header_tolerance_calc_code;
318 END IF;
319
320 IF p_trade_profile_rec.header_tolerance_operand = FND_API.g_miss_num THEN
321 x_complete_rec.header_tolerance_operand := null;
322 END IF;
323 IF p_trade_profile_rec.header_tolerance_operand is null THEN
324 x_complete_rec.header_tolerance_operand := l_trade_profile_rec.header_tolerance_operand;
325 END IF;
326
327 IF p_trade_profile_rec.line_tolerance_calc_code = FND_API.g_miss_char THEN
328 x_complete_rec.line_tolerance_calc_code := null;
329 END IF;
330 IF p_trade_profile_rec.line_tolerance_calc_code is null THEN
331 x_complete_rec.line_tolerance_calc_code := l_trade_profile_rec.line_tolerance_calc_code;
332 END IF;
333
334 IF p_trade_profile_rec.line_tolerance_operand = FND_API.g_miss_num THEN
335 x_complete_rec.line_tolerance_operand := null;
336 END IF;
337 IF p_trade_profile_rec.line_tolerance_operand is null THEN
338 x_complete_rec.line_tolerance_operand := l_trade_profile_rec.line_tolerance_operand;
339 END IF;
340
341
342 END Complete_trade_profile_Rec;
343 -------------------------------------------------------------------------------
344 PROCEDURE populate_defaults(
345 p_trade_profile_rec IN trade_profile_rec_type,
346 x_trade_profile_rec OUT NOCOPY trade_profile_rec_type,
347 x_return_status OUT NOCOPY VARCHAR2
348 )
349 IS
350 l_cust_acct_site_id NUMBER;
351 l_vendor_site_code VARCHAR2(80);
352 L_API_NAME CONSTANT VARCHAR2(30) := 'Populate_Defaults';
353
354 CURSOR c_cust_acct_site_id (a_id IN NUMBER) IS
355 SELECT cust_acct_site_id
356 from HZ_CUST_SITE_USES
357 WHERE site_use_id = a_id;
358
359 CURSOR vendor_site_code_csr (a_id IN NUMBER) IS
360 SELECT vendor_site_code
361 from PO_VENDOR_SITES
362 WHERE vendor_site_id = a_id;
363
364 BEGIN
365 x_trade_profile_rec := p_trade_profile_rec;
366
367 -- defaulting flags not shown on screen and mandatory in db
368 x_trade_profile_rec.internet_deal_view_flag :='F';
369 x_trade_profile_rec.print_flag :='F';
370
371 -- set autopay flag to F if null
372 IF x_trade_profile_rec.autopay_flag = FND_API.g_miss_char OR
373 x_trade_profile_rec.autopay_flag IS NULL
374 THEN
375 x_trade_profile_rec.autopay_flag :='F';
376 END IF;
377
378 -- default cust_acct_site_id if site use is is found
379 IF x_trade_profile_rec.cust_account_id <> FND_API.g_miss_num OR
380 x_trade_profile_rec.cust_account_id IS NOT NULL
381 THEN
382 IF x_trade_profile_rec.site_use_id <> FND_API.g_miss_num OR
383 x_trade_profile_rec.site_use_id IS NOT NULL
384 THEN
385 OPEN c_cust_acct_site_id(x_trade_profile_rec.site_use_id);
386 FETCH c_cust_acct_site_id INTO l_cust_acct_site_id;
387 CLOSE c_cust_acct_site_id;
388 END IF;
389 x_trade_profile_rec.cust_acct_site_id :=l_cust_acct_site_id;
390 END IF;
391
392 -- store vendor_code if not passed
393 IF x_trade_profile_rec.vendor_site_id <> FND_API.g_miss_num OR
394 x_trade_profile_rec.vendor_site_id IS NOT NULL
395 THEN
396 IF x_trade_profile_rec.vendor_id <> FND_API.g_miss_num OR
397 x_trade_profile_rec.vendor_id IS NOT NULL
398 THEN
399 OPEN vendor_site_code_csr(x_trade_profile_rec.vendor_site_id);
400 FETCH vendor_site_code_csr INTO l_vendor_site_code;
401 CLOSE vendor_site_code_csr;
402 END IF;
403 x_trade_profile_rec.vendor_site_code :=l_vendor_site_code;
404 END IF;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
410 THEN
411 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
412 END IF;
413 END populate_defaults;
414 --------------------------------------------------------------------------------
415 PROCEDURE Create_Trade_Profile(
416 p_api_version_number IN NUMBER,
417 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
418 p_commit IN VARCHAR2 := FND_API.G_FALSE,
419 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
420 x_return_status OUT NOCOPY VARCHAR2,
421 x_msg_count OUT NOCOPY NUMBER,
422 x_msg_data OUT NOCOPY VARCHAR2,
423 p_trade_profile_rec IN trade_profile_rec_type,
424 x_trade_profile_id OUT NOCOPY NUMBER
425 )
426 IS
427 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Trade_Profile';
428 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
429 l_return_status_full VARCHAR2(1);
430 l_object_version_number NUMBER := 1;
431 l_org_id NUMBER ;
432 l_TRADE_PROFILE_ID NUMBER;
433 l_CUST_ACCOUNT_ID NUMBER;
434 l_cust_acct_site_id NUMBER;
435 l_dummy NUMBER;
436 l_cust_dummy NUMBER;
437 l_party_dummy NUMBER;
438 l_party_dummy1 NUMBER;
439 l_party_id NUMBER;
440
441 l_trade_profile_rec trade_profile_rec_type;
442 l_x_trade_profile_rec trade_profile_rec_type;
443 l_null VARCHAR2(10) := 'NULL';
444
445 CURSOR c_id IS
446 SELECT ozf_cust_trd_prfls_all_s.NEXTVAL
447 FROM dual;
448
449 CURSOR c_id_exists (l_id IN NUMBER) IS
450 SELECT count(trade_profile_id)
451 FROM ozf_cust_trd_prfls_all
452 WHERE TRADE_PROFILE_ID = l_id;
453
454 CURSOR c_customer_id (p_id IN NUMBER) IS
455 SELECT cust_account_id
456 from HZ_CUST_ACCOUNTS
457 WHERE party_id = p_id
458 AND status = 'A';
459
460 CURSOR c_party_id (c_id IN NUMBER) IS
461 SELECT party_id
462 from HZ_CUST_ACCOUNTS
463 WHERE cust_account_id = c_id;
464
465 BEGIN
466
467 -- Standard Start of API savepoint
468 SAVEPOINT CREATE_Trade_Profile_PVT;
469 IF g_debug THEN
470 OZF_UTILITY_PVT.debug_message('Private API: Create trade profile');
471 END IF;
472 -- Standard call to check for call compatibility.
473 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
474 p_api_version_number,
475 l_api_name,
476 G_PKG_NAME)
477 THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END IF;
480
481 -- Initialize message list if p_init_msg_list is set to TRUE.
482 IF FND_API.to_Boolean( p_init_msg_list )
483 THEN
484 FND_MSG_PUB.initialize;
485 END IF;
486 -- Initialize API return status to SUCCESS
487 x_return_status := FND_API.G_RET_STS_SUCCESS;
488 -- Local variable initialization
489 IF p_trade_profile_rec.TRADE_PROFILE_ID IS NULL OR
490 p_trade_profile_rec.TRADE_PROFILE_ID = FND_API.g_miss_num
491 THEN
492 LOOP
493 l_dummy := NULL;
494 OPEN c_id;
495 FETCH c_id INTO l_TRADE_PROFILE_ID;
496 CLOSE c_id;
497
498 OPEN c_id_exists(l_TRADE_PROFILE_ID);
499 FETCH c_id_exists INTO l_dummy;
500 CLOSE c_id_exists;
501 EXIT WHEN l_dummy=0;
502 END LOOP;
503 END IF;
504
505 -- =========================================================================
506 -- Validate Environment
507 -- =========================================================================
508 IF FND_GLOBAL.User_Id IS NULL
509 THEN
510 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
511 FND_MESSAGE.set_name('OZF', 'OZF_USER_PROFILE_MISSING');
512 FND_MSG_PUB.add;
513 END IF;
514 RAISE FND_API.G_EXC_ERROR;
515 END IF;
516
517 -- Assign the record to a local variable
518 l_trade_profile_rec := p_trade_profile_rec;
519
520 -- checking party and defaulting it if cust_account_id is passed
521 IF p_trade_profile_rec.party_id = FND_API.g_miss_num OR
522 p_trade_profile_rec.party_id IS NULL
523 THEN
524 IF g_debug THEN
525 OZF_UTILITY_PVT.debug_message('Party Id is null');
526 END IF;
527 IF l_trade_profile_rec.cust_account_id = FND_API.g_miss_num OR
528 l_trade_profile_rec.cust_account_id IS NULL
529 THEN
530 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
531 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_CUST_MISSING');
532 FND_MSG_PUB.add;
533 END IF;
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 ELSE
536 OPEN c_party_id(l_trade_profile_rec.cust_account_id);
537 FETCH c_party_id INTO l_party_id;
538 CLOSE c_party_id;
539 l_trade_profile_rec.party_id := l_party_id;
540 END IF;
541 END IF;
542
543 IF l_trade_profile_rec.claim_currency is null OR
544 l_trade_profile_rec.claim_currency = FND_API.g_miss_char THEN
545
546 OPEN g_functional_currency_code_csr;
547 FETCH g_functional_currency_code_csr INTO l_trade_profile_rec.claim_currency;
548 CLOSE g_functional_currency_code_csr;
549 END IF;
550
551 -- populate defaults
552 populate_defaults (p_trade_profile_rec => l_trade_profile_rec,
553 x_trade_profile_rec => l_x_trade_profile_rec,
554 x_return_status => x_return_status);
555
556 IF x_return_status = FND_API.g_ret_sts_error THEN
557 RAISE FND_API.G_EXC_ERROR;
558 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561 l_trade_profile_rec := l_x_trade_profile_rec;
562
563 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
564 THEN
565 -- Invoke validation procedures
566 Validate_trade_profile(
567 p_api_version_number => 1.0,
568 p_init_msg_list => FND_API.G_FALSE,
569 p_validation_level => p_validation_level,
570 p_trade_profile_rec =>l_trade_profile_rec,
571 x_return_status => x_return_status,
572 x_msg_count => x_msg_count,
573 x_msg_data => x_msg_data
574 );
575 END IF;
576
577 IF x_return_status = FND_API.g_ret_sts_error THEN
578 RAISE FND_API.G_EXC_ERROR;
579 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 END IF;
582
583 -- Debug Message
584 -- Invoke table handler(OZF_cust_trd_prfls_PKG.Insert_Row)
585 BEGIN
586 OZF_cust_trd_prfls_PKG.Insert_Row(
587 px_trade_profile_id => l_trade_profile_id,
588 px_object_version_number => l_object_version_number,
589 p_last_update_date => SYSDATE,
590 p_last_updated_by => FND_GLOBAL.USER_ID,
591 p_creation_date => SYSDATE,
592 p_created_by => FND_GLOBAL.USER_ID,
593 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
594 p_request_id => l_trade_profile_rec.request_id,
595 p_program_application_id => l_trade_profile_rec.program_application_id,
596 p_program_update_date => l_trade_profile_rec.program_update_date,
597 p_program_id => l_trade_profile_rec.program_id,
598 p_created_from => l_trade_profile_rec.created_from,
599 p_party_id => l_trade_profile_rec.party_id,
600 p_site_use_id => l_trade_profile_rec.site_use_id,
601 p_autopay_flag => l_trade_profile_rec.autopay_flag,
602 p_claim_threshold => l_trade_profile_rec.claim_threshold,
603 p_claim_currency => l_trade_profile_rec.claim_currency,
604 p_print_flag => l_trade_profile_rec.print_flag,
605 p_internet_deal_view_flag => l_trade_profile_rec.internet_deal_view_flag,
606 p_internet_claims_flag => l_trade_profile_rec.internet_claims_flag,
607 p_autopay_periodicity => l_trade_profile_rec.autopay_periodicity,
608 p_autopay_periodicity_type => l_trade_profile_rec.autopay_periodicity_type,
609 p_payment_method => l_trade_profile_rec.payment_method,
610 p_discount_type => l_trade_profile_rec.discount_type,
611 p_cust_account_id => l_trade_profile_rec.cust_account_id,
612 p_cust_acct_site_id => l_trade_profile_rec.cust_acct_site_id,
613 p_vendor_id => l_trade_profile_rec.vendor_id,
614 p_vendor_site_id => l_trade_profile_rec.vendor_site_id,
615 p_vendor_site_code => l_trade_profile_rec.vendor_site_code,
616 p_context => l_trade_profile_rec.context,
617 p_attribute_category => l_trade_profile_rec.attribute_category,
618 p_attribute1 => l_trade_profile_rec.attribute1,
619 p_attribute2 => l_trade_profile_rec.attribute2,
620 p_attribute3 => l_trade_profile_rec.attribute3,
621 p_attribute4 => l_trade_profile_rec.attribute4,
622 p_attribute5 => l_trade_profile_rec.attribute5,
623 p_attribute6 => l_trade_profile_rec.attribute6,
624 p_attribute7 => l_trade_profile_rec.attribute7,
625 p_attribute8 => l_trade_profile_rec.attribute8,
626 p_attribute9 => l_trade_profile_rec.attribute9,
627 p_attribute10 => l_trade_profile_rec.attribute10,
628 p_attribute11 => l_trade_profile_rec.attribute11,
629 p_attribute12 => l_trade_profile_rec.attribute12,
630 p_attribute13 => l_trade_profile_rec.attribute13,
631 p_attribute14 => l_trade_profile_rec.attribute14,
632 p_attribute15 => l_trade_profile_rec.attribute15,
633 px_org_id => l_trade_profile_rec.org_id,
634 p_days_due => l_trade_profile_rec.days_due,
635 p_pos_write_off_threshold => l_trade_profile_rec.pos_write_off_threshold,
636 p_neg_write_off_threshold => l_trade_profile_rec.neg_write_off_threshold,
637 p_un_earned_pay_allow_to => l_trade_profile_rec.un_earned_pay_allow_to,
638 p_un_earned_pay_thold_type => l_trade_profile_rec.un_earned_pay_thold_type,
639 p_un_earned_pay_threshold => l_trade_profile_rec.un_earned_pay_threshold,
640 p_un_earned_pay_thold_flag => l_trade_profile_rec.un_earned_pay_thold_flag,
641 p_header_tolerance_calc_code => l_trade_profile_rec.header_tolerance_calc_code,
642 p_header_tolerance_operand => l_trade_profile_rec.header_tolerance_operand,
643 p_line_tolerance_calc_code => l_trade_profile_rec.line_tolerance_calc_code,
644 p_line_tolerance_operand => l_trade_profile_rec.line_tolerance_operand
645 );
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
650 FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
651 FND_MSG_PUB.add;
652 END IF;
653
654 RAISE FND_API.G_EXC_ERROR;
655 END;
656 --
657 -- End of API body
658 --
659
660 -- Standard check for p_commit
661 IF FND_API.to_Boolean( p_commit )
662 THEN
663 COMMIT WORK;
664 END IF;
665
666 -- Debug Message
667 IF g_debug THEN
668 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
669 END IF;
670
671 -- Standard call to get message count and if count is 1, get message info.
672 FND_MSG_PUB.Count_And_Get
673 (p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676
677 EXCEPTION
678 WHEN OZF_Utility_PVT.resource_locked THEN
679 x_return_status := FND_API.g_ret_sts_error;
680 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
681 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES_LOCKED');
682 FND_MSG_PUB.add;
683 END IF;
684 WHEN FND_API.G_EXC_ERROR THEN
685 ROLLBACK TO CREATE_Trade_Profile_PVT;
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 -- Standard call to get message count and if count=1, get the message
688 FND_MSG_PUB.Count_And_Get (
689 p_encoded => FND_API.G_FALSE,
690 p_count => x_msg_count,
691 p_data => x_msg_data
692 );
693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
694 ROLLBACK TO CREATE_Trade_Profile_PVT;
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
696 -- Standard call to get message count and if count=1, get the message
697 FND_MSG_PUB.Count_And_Get (
698 p_encoded => FND_API.G_FALSE,
699 p_count => x_msg_count,
700 p_data => x_msg_data
701 );
702 WHEN OTHERS THEN
703 ROLLBACK TO CREATE_Trade_Profile_PVT;
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
706 THEN
707 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
708 END IF;
709 -- Standard call to get message count and if count=1, get the message
710 FND_MSG_PUB.Count_And_Get (
711 p_encoded => FND_API.G_FALSE,
712 p_count => x_msg_count,
713 p_data => x_msg_data
714 );
715 End Create_Trade_Profile;
716 -------------------------------------------------------------------------------
717 PROCEDURE Update_Trade_Profile(
718 p_api_version_number IN NUMBER,
719 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
720 p_commit IN VARCHAR2 := FND_API.G_FALSE,
721 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
722 x_return_status OUT NOCOPY VARCHAR2,
723 x_msg_count OUT NOCOPY NUMBER,
724 x_msg_data OUT NOCOPY VARCHAR2,
725 p_trade_profile_rec IN trade_profile_rec_type,
726 x_object_version_number OUT NOCOPY NUMBER
727 )
728 IS
729 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Trade_Profile';
730 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
731 -- Local Variables
732 l_object_version_number NUMBER;
733 l_TRADE_PROFILE_ID NUMBER;
734 --l_cust_account_id NUMBER;
735 l_site_use_id NUMBER;
736 l_cust_acct_site_id NUMBER;
737 l_org_id NUMBER;
738
739 CURSOR c_trade IS
740 SELECT *
741 FROM ozf_cust_trd_prfls_all
742 WHERE trade_profile_id = p_trade_profile_rec.trade_profile_id;
743
744 CURSOR c_get_trade_profile(v_trade_profile_id in NUMBER) IS
745 SELECT *
746 FROM ozf_cust_trd_prfls_all
747 WHERE trade_profile_id = v_trade_profile_id;
748
749 CURSOR c_cust_acct_site_id (a_id IN NUMBER) IS
750 SELECT cust_acct_site_id
751 from HZ_CUST_SITE_USES
752 WHERE site_use_id = a_id;
753
754 --l_ref_trade_profile_rec c_get_Trade_Profile%ROWTYPE;
755 l_ref_trade_profile_rec c_trade%ROWTYPE;
756 l_tar_trade_profile_rec trade_profile_rec_type := p_trade_profile_rec;
757 l_rowid ROWID;
758 l_trade_profile_rec trade_profile_rec_type;
759 l_x_trade_profile_rec trade_profile_rec_type;
760
761 /*CURSOR check_acct_profile (p_id in number) IS
762 SELECT cust_account_id
763 FROM ozf_cust_trd_prfls_all -- R12 Enhancements
764 WHERE trade_profile_id = p_id;*/
765
766 CURSOR check_site_profile (p_id in number) IS
767 SELECT site_use_id
768 FROM ozf_cust_trd_prfls_all --For R12.1 Enhancements
769 WHERE trade_profile_id = p_id;
770
771
772 BEGIN
773 -- Standard Start of API savepoint
774 SAVEPOINT UPDATE_Trade_Profile_PVT;
775 -- Standard call to check for call compatibility
776 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
777 p_api_version_number,
778 l_api_name,
779 G_PKG_NAME
780 )
781 THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784 -- Initialize message list if p_init_msg_list is set to TRUE.
785 IF FND_API.to_Boolean( p_init_msg_list )
786 THEN
787 FND_MSG_PUB.initialize;
788 END IF;
789 -- Debug Message
790 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
791 -- Initialize API return status to SUCCESS
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793 l_org_id := l_tar_trade_profile_rec.org_id; -- R12 Enhancements
794
795 -- Debug Message
796 IF g_debug THEN
797 OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
798 END IF;
799 OPEN c_trade;
800 FETCH c_trade INTO l_ref_trade_profile_rec;
801 IF ( c_trade%NOTFOUND) THEN
802 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
803 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
804 FND_MSG_PUB.add;
805 END IF;
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808 CLOSE c_trade;
809 -- Debug Message
810 IF g_debug THEN
811 OZF_UTILITY_PVT.debug_message('Private API: - Close Cursor');
812 END IF;
813
814 IF (l_tar_trade_profile_rec.object_version_number is NULL or
815 l_tar_trade_profile_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
816 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
817 FND_MESSAGE.set_name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
818 FND_MSG_PUB.add;
819 END IF;
820 raise FND_API.G_EXC_ERROR;
821 End if;
822
823 -- check if site use id exists and is different than the one in db for trade profile
824 -- For R12.1 Enhancements
825 OPEN check_site_profile(p_trade_profile_rec.trade_profile_id);
826 FETCH check_site_profile INTO l_site_use_id;
827 CLOSE check_site_profile;
828 -- set to miss num if value is null
829 IF l_site_use_id is null THEN
830 l_site_use_id := FND_API.G_MISS_NUM;
831 END IF;
832
833 -- if cust account id in db is not the same as the account from user create a trade profile
834 IF g_debug THEN
835 OZF_UTILITY_PVT.debug_message('before create in update!!');
836 END IF;
837 IF l_site_use_id <> p_trade_profile_rec.site_use_id THEN
838 IF g_debug THEN
839 OZF_UTILITY_PVT.debug_message('into create');
840 END IF;
841 Create_Trade_Profile (
842 p_api_version_number => 1.0,
843 p_init_msg_list => FND_API.G_FALSE,
844 p_commit => FND_API.G_FALSE,
845 p_validation_level => p_validation_level,
846 x_return_status => x_return_status,
847 x_msg_count => x_msg_count,
848 x_msg_data => x_msg_data,
849 p_trade_profile_rec => p_trade_profile_rec,
850 x_trade_profile_id => l_trade_profile_id
851 );
852 IF x_return_status = FND_API.g_ret_sts_error THEN
853 RAISE FND_API.G_EXC_ERROR;
854 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
856 END IF;
857 x_object_version_number := 1;
858 ELSE -- update for existing trade profiles at party or account levels
859 -- complete the record (get missing values filled from db)
860 Complete_trade_profile_Rec(
861 p_trade_profile_rec => p_trade_profile_rec,
862 x_complete_rec => l_trade_profile_rec
863 );
864 IF g_debug THEN
865 OZF_UTILITY_PVT.debug_message('into update part');
866 END IF;
867 -- populate defaults
868 populate_defaults (p_trade_profile_rec => l_trade_profile_rec,
869 x_trade_profile_rec => l_x_trade_profile_rec,
870 x_return_status => x_return_status);
871
872 IF x_return_status = FND_API.g_ret_sts_error THEN
873 RAISE FND_API.G_EXC_ERROR;
874 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876 END IF;
877 l_trade_profile_rec := l_x_trade_profile_rec;
878
879 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
880 Check_trade_profile_Items(
881 p_trade_profile_rec => l_trade_profile_rec,
882 p_validation_mode => JTF_PLSQL_API.g_update,
883 x_return_status => x_return_status
884 );
885 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
886 RAISE FND_API.G_EXC_ERROR;
887 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 END IF;
890 END IF;
891
892 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
893 Validate_trade_profile_Rec(
894 p_api_version_number => 1.0,
895 p_init_msg_list => FND_API.G_FALSE,
896 x_return_status => x_return_status,
897 x_msg_count => x_msg_count,
898 x_msg_data => x_msg_data,
899 p_trade_profile_rec => l_trade_profile_rec
900 );
901 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
902 RAISE FND_API.G_EXC_ERROR;
903 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
905 END IF;
906 END IF;
907 IF g_debug THEN
908 OZF_UTILITY_PVT.debug_message('cust_account_id'||l_trade_profile_rec.cust_account_id);
909 END IF;
910
911 -- Debug Message
912 OZF_cust_trd_prfls_PKG.Update_Row(
913 p_trade_profile_id => l_trade_profile_rec.trade_profile_id,
914 p_object_version_number => l_trade_profile_rec.object_version_number,
915 p_last_update_date => SYSDATE,
916 p_last_updated_by => FND_GLOBAL.USER_ID,
917 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
918 p_request_id => l_trade_profile_rec.request_id,
919 p_program_application_id => l_trade_profile_rec.program_application_id,
920 p_program_update_date => l_trade_profile_rec.program_update_date,
921 p_program_id => l_trade_profile_rec.program_id,
922 p_created_from => l_trade_profile_rec.created_from,
923 p_party_id => l_trade_profile_rec.party_id,
924 p_site_use_id => l_trade_profile_rec.site_use_id,
925 p_autopay_flag => l_trade_profile_rec.autopay_flag,
926 p_claim_threshold => l_trade_profile_rec.claim_threshold,
927 p_claim_currency => l_trade_profile_rec.claim_currency,
928 p_print_flag => l_trade_profile_rec.print_flag,
929 p_internet_deal_view_flag => l_trade_profile_rec.internet_deal_view_flag,
930 p_internet_claims_flag => l_trade_profile_rec.internet_claims_flag,
931 p_autopay_periodicity => l_trade_profile_rec.autopay_periodicity,
932 p_autopay_periodicity_type => l_trade_profile_rec.autopay_periodicity_type,
933 p_payment_method => p_trade_profile_rec.payment_method,
934 p_discount_type => l_trade_profile_rec.discount_type,
935 p_cust_account_id => l_trade_profile_rec.cust_account_id,
936 p_cust_acct_site_id => l_trade_profile_rec.cust_acct_site_id,
937 p_vendor_id => l_trade_profile_rec.vendor_id,
938 p_vendor_site_id => l_trade_profile_rec.vendor_site_id,
939 p_vendor_site_code => l_trade_profile_rec.vendor_site_code,
940 p_context => l_trade_profile_rec.context,
941 p_attribute_category => l_trade_profile_rec.attribute_category,
942 p_attribute1 => l_trade_profile_rec.attribute1,
943 p_attribute2 => l_trade_profile_rec.attribute2,
944 p_attribute3 => l_trade_profile_rec.attribute3,
945 p_attribute4 => l_trade_profile_rec.attribute4,
946 p_attribute5 => l_trade_profile_rec.attribute5,
947 p_attribute6 => p_trade_profile_rec.attribute6,
948 p_attribute7 => l_trade_profile_rec.attribute7,
949 p_attribute8 => l_trade_profile_rec.attribute8,
950 p_attribute9 => l_trade_profile_rec.attribute9,
951 p_attribute10 => l_trade_profile_rec.attribute10,
952 p_attribute11 => l_trade_profile_rec.attribute11,
953 p_attribute12 => l_trade_profile_rec.attribute12,
954 p_attribute13 => l_trade_profile_rec.attribute13,
955 p_attribute14 => l_trade_profile_rec.attribute14,
956 p_attribute15 => l_trade_profile_rec.attribute15,
957 p_org_id => l_org_id,
958 p_days_due => l_trade_profile_rec.days_due,
959 p_pos_write_off_threshold => l_trade_profile_rec.pos_write_off_threshold,
960 p_neg_write_off_threshold => l_trade_profile_rec.neg_write_off_threshold,
961 p_un_earned_pay_allow_to => l_trade_profile_rec.un_earned_pay_allow_to,
962 p_un_earned_pay_thold_type => l_trade_profile_rec.un_earned_pay_thold_type,
963 p_un_earned_pay_threshold => l_trade_profile_rec.un_earned_pay_threshold,
964 p_un_earned_pay_thold_flag => l_trade_profile_rec.un_earned_pay_thold_flag,
965 p_header_tolerance_calc_code => l_trade_profile_rec.header_tolerance_calc_code,
966 p_header_tolerance_operand => l_trade_profile_rec.header_tolerance_operand,
967 p_line_tolerance_calc_code => l_trade_profile_rec.line_tolerance_calc_code,
968 p_line_tolerance_operand => l_trade_profile_rec.line_tolerance_operand
969 );
970 -- set the return object version number
971 x_object_version_number := l_trade_profile_rec.object_version_number;
972 END IF; -- end of check for create or update of trade profile
973 -- End of API body.
974 -- Standard check for p_commit
975 IF FND_API.to_Boolean( p_commit )
976 THEN
977 COMMIT WORK;
978 END IF;
979 -- Debug Message
980 IF g_debug THEN
981 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
982 END IF;
983 -- Standard call to get message count and if count is 1, get message info.
984 FND_MSG_PUB.Count_And_Get
985 (p_count => x_msg_count,
986 p_data => x_msg_data
987 );
988 EXCEPTION
989 WHEN OZF_Utility_PVT.resource_locked THEN
990 x_return_status := FND_API.g_ret_sts_error;
991 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
992 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
993 FND_MSG_PUB.add;
994 END IF;
995 WHEN FND_API.G_EXC_ERROR THEN
996 ROLLBACK TO UPDATE_Trade_Profile_PVT;
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 -- Standard call to get message count and if count=1, get the message
999 FND_MSG_PUB.Count_And_Get (
1000 p_encoded => FND_API.G_FALSE,
1001 p_count => x_msg_count,
1002 p_data => x_msg_data
1003 );
1004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1005 ROLLBACK TO UPDATE_Trade_Profile_PVT;
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007 -- Standard call to get message count and if count=1, get the message
1008 FND_MSG_PUB.Count_And_Get (
1009 p_encoded => FND_API.G_FALSE,
1010 p_count => x_msg_count,
1011 p_data => x_msg_data
1012 );
1013 WHEN OTHERS THEN
1014 ROLLBACK TO UPDATE_Trade_Profile_PVT;
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1016 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1017 THEN
1018 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1019 END IF;
1020 -- Standard call to get message count and if count=1, get the message
1021 FND_MSG_PUB.Count_And_Get (
1022 p_encoded => FND_API.G_FALSE,
1023 p_count => x_msg_count,
1024 p_data => x_msg_data
1025 );
1026 End Update_Trade_Profile;
1027 -------------------------------------------------------------------------------
1028 PROCEDURE Delete_Trade_Profile(
1029 p_api_version_number IN NUMBER,
1030 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1031 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1032 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1033 x_return_status OUT NOCOPY VARCHAR2,
1034 x_msg_count OUT NOCOPY NUMBER,
1035 x_msg_data OUT NOCOPY VARCHAR2,
1036 p_trade_profile_id IN NUMBER,
1037 p_object_version_number IN NUMBER
1038 )
1039
1040 IS
1041 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Trade_Profile';
1042 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1043 l_object_version_number NUMBER;
1044
1045 BEGIN
1046 -- Standard Start of API savepoint
1047 SAVEPOINT DELETE_Trade_Profile_PVT;
1048 -- Standard call to check for call compatibility.
1049 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1050 p_api_version_number,
1051 l_api_name,
1052 G_PKG_NAME)
1053 THEN
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 END IF;
1056 -- Initialize message list if p_init_msg_list is set to TRUE.
1057 IF FND_API.to_Boolean( p_init_msg_list )
1058 THEN
1059 FND_MSG_PUB.initialize;
1060 END IF;
1061 -- Debug Message
1062 IF g_debug THEN
1063 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1064 END IF;
1065 -- Initialize API return status to SUCCESS
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067 -- Api body
1068 --
1069 -- Debug Message
1070 IF g_debug THEN
1071 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1072 END IF;
1073 -- Invoke table handler(OZF_cust_trd_prfls_PKG.Delete_Row)
1074 OZF_cust_trd_prfls_PKG.Delete_Row(
1075 p_TRADE_PROFILE_ID => p_TRADE_PROFILE_ID);
1076 -- End of API body
1077 -- Standard check for p_commit
1078 IF FND_API.to_Boolean( p_commit )
1079 THEN
1080 COMMIT WORK;
1081 END IF;
1082 -- Debug Message
1083 IF g_debug THEN
1084 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1085 END IF;
1086 -- Standard call to get message count and if count is 1, get message info.
1087 FND_MSG_PUB.Count_And_Get
1088 (p_count => x_msg_count,
1089 p_data => x_msg_data
1090 );
1091 EXCEPTION
1092 WHEN OZF_Utility_PVT.resource_locked THEN
1093 x_return_status := FND_API.g_ret_sts_error;
1094 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1095 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES_LOCKED');
1096 FND_MSG_PUB.add;
1097 END IF;
1098 WHEN FND_API.G_EXC_ERROR THEN
1099 ROLLBACK TO DELETE_Trade_Profile_PVT;
1100 x_return_status := FND_API.G_RET_STS_ERROR;
1101 -- Standard call to get message count and if count=1, get the message
1102 FND_MSG_PUB.Count_And_Get (
1103 p_encoded => FND_API.G_FALSE,
1104 p_count => x_msg_count,
1105 p_data => x_msg_data
1106 );
1107
1108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1109 ROLLBACK TO DELETE_Trade_Profile_PVT;
1110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1111 -- Standard call to get message count and if count=1, get the message
1112 FND_MSG_PUB.Count_And_Get (
1113 p_encoded => FND_API.G_FALSE,
1114 p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117 WHEN OTHERS THEN
1118 ROLLBACK TO DELETE_Trade_Profile_PVT;
1119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1120 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1121 THEN
1122 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1123 END IF;
1124 -- Standard call to get message count and if count=1, get the message
1125 FND_MSG_PUB.Count_And_Get (
1126 p_encoded => FND_API.G_FALSE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data
1129 );
1130 End Delete_Trade_Profile;
1131 -------------------------------------------------------------------------------
1132 PROCEDURE Lock_Trade_Profile(
1133 p_api_version_number IN NUMBER,
1134 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 x_msg_count OUT NOCOPY NUMBER,
1137 x_msg_data OUT NOCOPY VARCHAR2,
1138 p_trade_profile_id IN NUMBER,
1139 p_object_version IN NUMBER
1140 )
1141 IS
1142 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Trade_Profile';
1143 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1144 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1145 l_TRADE_PROFILE_ID NUMBER;
1146
1147 CURSOR c_Trade_Profile IS
1148 SELECT TRADE_PROFILE_ID
1149 FROM ozf_cust_trd_prfls_all
1150 WHERE TRADE_PROFILE_ID = p_TRADE_PROFILE_ID
1151 AND object_version_number = p_object_version
1152 FOR UPDATE NOWAIT;
1153 BEGIN
1154 -- Debug Message
1155 IF g_debug THEN
1156 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1157 END IF;
1158
1159 -- Initialize message list if p_init_msg_list is set to TRUE.
1160 IF FND_API.to_Boolean( p_init_msg_list )
1161 THEN
1162 FND_MSG_PUB.initialize;
1163 END IF;
1164
1165 -- Standard call to check for call compatibility.
1166 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1167 p_api_version_number,
1168 l_api_name,
1169 G_PKG_NAME)
1170 THEN
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 END IF;
1173 -- Initialize API return status to SUCCESS
1174 x_return_status := FND_API.G_RET_STS_SUCCESS;
1175 ------------------------ lock -------------------------
1176 IF g_debug THEN
1177 OZF_Utility_PVT.debug_message(l_full_name||': start');
1178 END IF;
1179 OPEN c_Trade_Profile;
1180 FETCH c_Trade_Profile INTO l_TRADE_PROFILE_ID;
1181 IF (c_Trade_Profile%NOTFOUND) THEN
1182 CLOSE c_Trade_Profile;
1183 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1184 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1185 FND_MSG_PUB.add;
1186 END IF;
1187 RAISE FND_API.g_exc_error;
1188 END IF;
1189 CLOSE c_Trade_Profile;
1190 -------------------- finish --------------------------
1191 FND_MSG_PUB.count_and_get(
1192 p_encoded => FND_API.g_false,
1193 p_count => x_msg_count,
1194 p_data => x_msg_data);
1195 IF g_debug THEN
1196 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1197 END IF;
1198 EXCEPTION
1199 WHEN OZF_Utility_PVT.resource_locked THEN
1200 x_return_status := FND_API.g_ret_sts_error;
1201 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1202 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES LOCKED');
1203 FND_MSG_PUB.add;
1204 END IF;
1205 WHEN FND_API.G_EXC_ERROR THEN
1206 ROLLBACK TO LOCK_Trade_Profile_PVT;
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208 -- Standard call to get message count and if count=1, get the message
1209 FND_MSG_PUB.Count_And_Get (
1210 p_encoded => FND_API.G_FALSE,
1211 p_count => x_msg_count,
1212 p_data => x_msg_data
1213 );
1214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1215 ROLLBACK TO LOCK_Trade_Profile_PVT;
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1217 -- Standard call to get message count and if count=1, get the message
1218 FND_MSG_PUB.Count_And_Get (
1219 p_encoded => FND_API.G_FALSE,
1220 p_count => x_msg_count,
1221 p_data => x_msg_data
1222 );
1223 WHEN OTHERS THEN
1224 ROLLBACK TO LOCK_Trade_Profile_PVT;
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1227 THEN
1228 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1229 END IF;
1230 -- Standard call to get message count and if count=1, get the message
1231 FND_MSG_PUB.Count_And_Get (
1232 p_encoded => FND_API.G_FALSE,
1233 p_count => x_msg_count,
1234 p_data => x_msg_data
1235 );
1236 End Lock_Trade_Profile;
1237 -------------------------------------------------------------------------------
1238 PROCEDURE check_trade_profile_uk_items(
1239 p_trade_profile_rec IN trade_profile_rec_type,
1240 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1241 x_return_status OUT NOCOPY VARCHAR2)
1242 IS
1243 l_valid_flag VARCHAR2(1);
1244 l_CUST_ACCOUNT_ID NUMBER;
1245 l_site_use_ID NUMBER;
1246 l_party_id NUMBER;
1247
1248 --l_cust_dummy NUMBER;
1249 l_site_dummy NUMBER;
1250 l_party_dummy NUMBER;
1251
1252 --For R12.1 Enhancements
1253 -- one trade profile per cust account
1254 /*CURSOR c_cust_id_exists (l_id IN NUMBER) IS
1255 SELECT count(cust_account_id)
1256 FROM ozf_cust_trd_prfls
1257 WHERE CUST_ACCOUNT_ID = l_id;*/
1258
1259
1260
1261 -- one trade profile per party (without cust account)
1262 CURSOR c_party_id_exists(l_id in NUMBER) IS
1263 SELECT count(party_id)
1264 FROM ozf_cust_trd_prfls
1265 WHERE CUST_ACCOUNT_ID is NULL
1266 AND PARTY_ID = l_id;
1267
1268 --For R12.1 Enhancements
1269 -- one trade profile per cust bill_to site
1270 CURSOR c_cust_site_use_exists (l_id IN NUMBER) IS
1271 SELECT count(site_use_id)
1272 FROM ozf_cust_trd_prfls
1273 WHERE site_use_id = l_id;
1274
1275 BEGIN
1276 x_return_status := FND_API.g_ret_sts_success;
1277 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1278 -- check if customer account id is unique
1279 IF p_trade_profile_rec.site_use_id = FND_API.g_miss_num OR
1280 p_trade_profile_rec.site_use_id is null
1281 THEN
1282 -- check if party without cust account id is unique
1283 IF p_trade_profile_rec.party_id = FND_API.g_miss_num OR
1284 p_trade_profile_rec.party_id is null
1285 THEN
1286 -- raise error
1287 l_valid_flag := FND_API.g_false;
1288 ELSE
1289 l_party_dummy := NULL;
1290 l_party_id := p_trade_profile_rec.party_id;
1291 OPEN c_party_id_exists(l_party_id);
1292 FETCH c_party_id_exists INTO l_party_dummy;
1293 CLOSE c_party_id_exists;
1294 IF l_party_dummy <> 0 THEN
1295 IF g_debug THEN
1296 OZF_UTILITY_PVT.debug_message('no cust or party 2'|| l_party_dummy ||l_valid_flag);
1297 END IF;
1298 -- l_valid_flag := FND_API.g_false;
1299 END IF;
1300 END IF;
1301 ELSE
1302 --For R12.1 Enhancements
1303 /*l_cust_dummy := NULL;
1304 l_CUST_ACCOUNT_ID := p_trade_profile_rec.cust_account_id;*/
1305
1306 l_site_dummy := NULL;
1307 l_site_use_id := p_trade_profile_rec.site_use_id;
1308
1309 OPEN c_cust_site_use_exists(l_CUST_ACCOUNT_ID);
1310 FETCH c_cust_site_use_exists INTO l_site_dummy;
1311 CLOSE c_cust_site_use_exists;
1312 OZF_UTILITY_PVT.debug_message('no l_site_dummy '|| l_site_dummy );
1313 IF l_site_dummy <> 0 THEN
1314 l_valid_flag := FND_API.g_false;
1315 END IF;
1316 END IF;
1317 ELSE
1318 l_valid_flag := FND_API.g_true;
1319 END IF;
1320
1321 IF l_valid_flag = FND_API.g_false THEN
1322 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1323 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_CUST_DUPLICATE');
1324 FND_MSG_PUB.add;
1325 END IF;
1326 x_return_status := FND_API.G_RET_STS_ERROR;
1327 END IF;
1328 END check_trade_profile_uk_items;
1329 -------------------------------------------------------------------------------
1330 PROCEDURE check_trade_profile_req_items(
1331 p_trade_profile_rec IN trade_profile_rec_type,
1332 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1333 x_return_status OUT NOCOPY VARCHAR2
1334 )
1335 IS
1336 l_check1 NUMBER;
1337 l_check2 NUMBER;
1338 l_party_id NUMBER;
1339 l_trade_profile_rec trade_profile_rec_type;
1340
1341 CURSOR c_party_id (c_id IN NUMBER) IS
1342 SELECT party_id
1343 from HZ_CUST_ACCOUNTS
1344 WHERE cust_account_id = c_id;
1345
1346 BEGIN
1347 x_return_status := FND_API.g_ret_sts_success;
1348 -- checking if party id exists
1349 OPEN c_party_id(p_trade_profile_rec.cust_account_id);
1350 FETCH c_party_id INTO l_party_id;
1351 CLOSE c_party_id;
1352
1353 IF p_trade_profile_rec.party_id = FND_API.g_miss_num OR
1354 p_trade_profile_rec.party_id IS NULL THEN
1355 IF p_trade_profile_rec.site_use_id = FND_API.g_miss_num OR --For R12.1 Enhancements
1356 p_trade_profile_rec.site_use_id IS NULL
1357 THEN
1358 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1359 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_PARTY_MISSING');
1360 FND_MSG_PUB.add;
1361 END IF;
1362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1363 END IF;
1364 END IF;
1365 OZF_UTILITY_PVT.debug_message('p_trade_profile_rec.payment_method : ' || p_trade_profile_rec.payment_method);
1366 -- Fix for ER#9453443
1367 IF p_trade_profile_rec.payment_method IN ('CHECK','EFT','WIRE','AP_DEBIT','AP_DEFAULT') THEN
1368 IF p_trade_profile_rec.vendor_id = FND_API.g_miss_num OR
1369 p_trade_profile_rec.vendor_id IS NULL THEN
1370 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1371 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_VENDOR_MISSING');
1372 FND_MSG_PUB.add;
1373 END IF;
1374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1375 END IF;
1376 IF p_trade_profile_rec.vendor_site_id = FND_API.g_miss_num OR
1377 p_trade_profile_rec.vendor_site_id IS NULL
1378 THEN
1379 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1380 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_VENSITE_MISSING');
1381 FND_MSG_PUB.add;
1382 END IF;
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 END IF;
1385 END IF;
1386 IF p_trade_profile_rec.autopay_flag = FND_API.g_miss_char OR
1387 p_trade_profile_rec.autopay_flag IS NULL
1388 THEN
1389 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1390 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_AUTO_MISSING ');
1391 FND_MSG_PUB.add;
1392 END IF;
1393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1394 END IF;
1395
1396 -- defaulted to F in the create api
1397 IF p_trade_profile_rec.autopay_flag = FND_API.g_miss_char OR
1398 p_trade_profile_rec.internet_deal_view_flag IS NULL
1399 THEN
1400 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1401 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_INTERNET_MISSING');
1402 FND_MSG_PUB.add;
1403 END IF;
1404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1405 END IF;
1406 IF p_trade_profile_rec.autopay_flag = FND_API.g_miss_char OR
1407 p_trade_profile_rec.print_flag IS NULL
1408 THEN
1409 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1410 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_PRINT_MISSING');
1411 FND_MSG_PUB.add;
1412 END IF;
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 END IF;
1415
1416 -- bill to site requitred for credit memos
1417 --nepanda : fix for bug # 9539273 - issue #2
1418 /* IF p_trade_profile_rec.payment_method = 'CREDIT_MEMO' THEN
1419 IF p_trade_profile_rec.site_use_id IS NULL OR
1420 p_trade_profile_rec.site_use_id = FND_API.G_MISS_NUM
1421 THEN
1422 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1423 FND_MESSAGE.set_name('OZF', 'OZF_TRADE_SITE_MISSING');
1424 FND_MSG_PUB.add;
1425 END IF;
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427 END IF;
1428 END IF;*/
1429
1430 /*
1431 IF p_trade_profile_rec.autopay_periodicity IS NULL AND
1432 p_trade_profile_rec.autopay_periodicity_type IS NULL
1433 THEN
1434 IF p_trade_profile_rec.claim_currency IS NULL AND
1435 p_trade_profile_rec.claim_threshold IS NULL
1436 THEN
1437 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1438 FND_MESSAGE.set_name('OZF', 'ERROR1');
1439 FND_MSG_PUB.add;
1440 END IF;
1441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1442 END IF;
1443 ELSE
1444 IF p_trade_profile_rec.autopay_periodicity IS NULL OR
1445 p_trade_profile_rec.autopay_periodicity_type IS NULL
1446 THEN
1447 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1448 FND_MESSAGE.set_name('OZF', 'ERROR1');
1449 FND_MSG_PUB.add;
1450 END IF;
1451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452 END IF;
1453 END IF;
1454
1455 IF p_trade_profile_rec.claim_currency IS NULL AND
1456 p_trade_profile_rec.claim_threshold IS NULL
1457 THEN
1458 IF p_trade_profile_rec.autopay_periodicity IS NULL AND
1459 p_trade_profile_rec.autopay_periodicity_type IS NULL
1460 THEN
1461 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1462 FND_MESSAGE.set_name('OZF', 'ERROR3');
1463 FND_MSG_PUB.add;
1464 END IF;
1465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1466 END IF;
1467 ELSE
1468 IF p_trade_profile_rec.claim_currency IS NULL OR
1469 p_trade_profile_rec.claim_threshold IS NULL
1470 THEN
1471 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1472 FND_MESSAGE.set_name('OZF', 'ERROR4');
1473 FND_MSG_PUB.add;
1474 END IF;
1475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1476 END IF;
1477 END IF;
1478 */
1479 END check_trade_profile_req_items;
1480 -------------------------------------------------------------------------------
1481 PROCEDURE check_trade_profile_FK_items(
1482 p_trade_profile_rec IN trade_profile_rec_type,
1483 x_return_status OUT NOCOPY VARCHAR2)
1484 IS
1485 BEGIN
1486 x_return_status := FND_API.g_ret_sts_success;
1487 END check_trade_profile_FK_items;
1488
1489 PROCEDURE check_trade_profile_Lk_items(
1490 p_trade_profile_rec IN trade_profile_rec_type,
1491 x_return_status OUT NOCOPY VARCHAR2
1492 )
1493 IS
1494 BEGIN
1495 x_return_status := FND_API.g_ret_sts_success;
1496 END check_trade_profile_Lk_items;
1497
1498
1499
1500 -- PROCEDURE
1501 -- Check_Batch_Tolerances
1502 --
1503 -- HISTORY
1504 -- 05/18/2004 upoluri Create.
1505 ---------------------------------------------------------------------
1506 PROCEDURE Check_Batch_Tolerances(
1507 p_trade_profile_rec IN trade_profile_rec_type
1508 ,x_return_status OUT NOCOPY VARCHAR2
1509 )
1510 IS
1511
1512 BEGIN
1513 x_return_status := FND_API.g_ret_sts_success;
1514
1515 IF (p_trade_profile_rec.header_tolerance_calc_code is null
1516 AND p_trade_profile_rec.header_tolerance_operand is not null)
1517 THEN
1518 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1519 THEN
1520 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BATCH_TOL_TYPE_REQ');
1521 FND_MSG_PUB.add;
1522 END IF;
1523 x_return_status := FND_API.g_ret_sts_error;
1524 RETURN;
1525 ELSIF (p_trade_profile_rec.header_tolerance_calc_code is not null
1526 AND p_trade_profile_rec.header_tolerance_operand is null)
1527 THEN
1528 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1529 THEN
1530 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BATCH_TOL_VAL_REQ');
1531 FND_MSG_PUB.add;
1532 END IF;
1533 x_return_status := FND_API.g_ret_sts_error;
1534 RETURN;
1535 END IF;
1536
1537
1538
1539 IF (p_trade_profile_rec.line_tolerance_calc_code is null
1540 AND p_trade_profile_rec.line_tolerance_operand is not null )
1541 THEN
1542 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1543 THEN
1544 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_TOL_TYPE_REQ');
1545 FND_MSG_PUB.add;
1546 END IF;
1547 x_return_status := FND_API.g_ret_sts_error;
1548 RETURN;
1549 ELSIF (p_trade_profile_rec.line_tolerance_calc_code is not null
1550 AND p_trade_profile_rec.line_tolerance_operand is null)
1551 THEN
1552 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1553 THEN
1554 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_TOL_VAL_REQ');
1555 FND_MSG_PUB.add;
1556 END IF;
1557 x_return_status := FND_API.g_ret_sts_error;
1558 RETURN;
1559 END IF;
1560
1561 END Check_Batch_Tolerances;
1562
1563
1564
1565
1566 -------------------------------------------------------------------------------
1567 PROCEDURE Check_trade_profile_Items (
1568 p_trade_profile_rec IN trade_profile_rec_type,
1569 p_validation_mode IN VARCHAR2,
1570 x_return_status OUT NOCOPY VARCHAR2
1571 )
1572 IS
1573 BEGIN
1574 -- Check Items Uniqueness API calls
1575 check_trade_profile_uk_items(
1576 p_trade_profile_rec => p_trade_profile_rec,
1577 p_validation_mode => p_validation_mode,
1578 x_return_status => x_return_status);
1579 IF x_return_status <> FND_API.g_ret_sts_success THEN
1580 RETURN;
1581 END IF;
1582 -- Check Items Required/NOT NULL API calls
1583 check_trade_profile_req_items(
1584 p_trade_profile_rec => p_trade_profile_rec,
1585 p_validation_mode => p_validation_mode,
1586 x_return_status => x_return_status);
1587 IF x_return_status <> FND_API.g_ret_sts_success THEN
1588 RETURN;
1589 END IF;
1590 -- Check Items Foreign Keys API calls
1591 check_trade_profile_FK_items(
1592 p_trade_profile_rec => p_trade_profile_rec,
1593 x_return_status => x_return_status);
1594 IF x_return_status <> FND_API.g_ret_sts_success THEN
1595 RETURN;
1596 END IF;
1597 -- Check Items Lookups
1598 check_trade_profile_Lk_items(
1599 p_trade_profile_rec => p_trade_profile_rec,
1600 x_return_status => x_return_status);
1601 IF x_return_status <> FND_API.g_ret_sts_success THEN
1602 RETURN;
1603 END IF;
1604
1605
1606 --Check the tolerances.
1607 Check_Batch_Tolerances(
1608 p_trade_profile_rec => p_trade_profile_rec,
1609 x_return_status => x_return_status
1610 );
1611 IF x_return_status <> FND_API.g_ret_sts_success THEN
1612 RETURN;
1613 END IF;
1614 END Check_trade_profile_Items;
1615 -------------------------------------------------------------------------------
1616 PROCEDURE Validate_trade_profile(
1617 p_api_version_number IN NUMBER,
1618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1619 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1620 p_trade_profile_rec IN trade_profile_rec_type,
1621 x_return_status OUT NOCOPY VARCHAR2,
1622 x_msg_count OUT NOCOPY NUMBER,
1623 x_msg_data OUT NOCOPY VARCHAR2
1624 )
1625 IS
1626 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Trade_Profile';
1627 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1628 l_object_version_number NUMBER;
1629 l_trade_profile_rec OZF_Trade_Profile_PVT.trade_profile_rec_type;
1630
1631 BEGIN
1632 -- Standard Start of API savepoint
1633 SAVEPOINT VALIDATE_Trade_Profile_PVT;
1634 -- Standard call to check for call compatibility.
1635 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1636 p_api_version_number,
1637 l_api_name,
1638 G_PKG_NAME)
1639 THEN
1640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1641 END IF;
1642 -- Initialize message list if p_init_msg_list is set to TRUE.
1643 IF FND_API.to_Boolean( p_init_msg_list )
1644 THEN
1645 FND_MSG_PUB.initialize;
1646 END IF;
1647 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1648 Check_trade_profile_Items(
1649 p_trade_profile_rec => p_trade_profile_rec,
1650 p_validation_mode => JTF_PLSQL_API.g_create,
1651 x_return_status => x_return_status
1652 );
1653 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1654 RAISE FND_API.G_EXC_ERROR;
1655 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1657 END IF;
1658 END IF;
1659
1660 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1661 Validate_trade_profile_Rec(
1662 p_api_version_number => 1.0,
1663 p_init_msg_list => FND_API.G_FALSE,
1664 x_return_status => x_return_status,
1665 x_msg_count => x_msg_count,
1666 x_msg_data => x_msg_data,
1667 p_trade_profile_rec => p_trade_profile_rec);
1668 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1669 RAISE FND_API.G_EXC_ERROR;
1670 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1672 END IF;
1673 END IF;
1674
1675 -- Initialize API return status to SUCCESS
1676 x_return_status := FND_API.G_RET_STS_SUCCESS;
1677 -- Standard call to get message count and if count is 1, get message info.
1678 FND_MSG_PUB.Count_And_Get
1679 (p_count => x_msg_count,
1680 p_data => x_msg_data
1681 );
1682 EXCEPTION
1683 WHEN OZF_Utility_PVT.resource_locked THEN
1684 x_return_status := FND_API.g_ret_sts_error;
1685 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1686 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED ');
1687 FND_MSG_PUB.add;
1688 END IF;
1689 WHEN FND_API.G_EXC_ERROR THEN
1690 ROLLBACK TO VALIDATE_Trade_Profile_PVT;
1691 x_return_status := FND_API.G_RET_STS_ERROR;
1692 -- Standard call to get message count and if count=1, get the message
1693 FND_MSG_PUB.Count_And_Get (
1694 p_encoded => FND_API.G_FALSE,
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1699 ROLLBACK TO VALIDATE_Trade_Profile_PVT;
1700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701 -- Standard call to get message count and if count=1, get the message
1702 FND_MSG_PUB.Count_And_Get (
1703 p_encoded => FND_API.G_FALSE,
1704 p_count => x_msg_count,
1705 p_data => x_msg_data
1706 );
1707 WHEN OTHERS THEN
1708 ROLLBACK TO VALIDATE_Trade_Profile_PVT;
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1711 THEN
1712 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1713 END IF;
1714 -- Standard call to get message count and if count=1, get the message
1715 FND_MSG_PUB.Count_And_Get (
1716 p_encoded => FND_API.G_FALSE,
1717 p_count => x_msg_count,
1718 p_data => x_msg_data
1719 );
1720 End Validate_Trade_Profile;
1721 -------------------------------------------------------------------------------
1722 PROCEDURE Validate_trade_profile_rec(
1723 p_api_version_number IN NUMBER,
1724 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1725 x_return_status OUT NOCOPY VARCHAR2,
1726 x_msg_count OUT NOCOPY NUMBER,
1727 x_msg_data OUT NOCOPY VARCHAR2,
1728 p_trade_profile_rec IN trade_profile_rec_type
1729 )
1730 IS
1731 BEGIN
1732 -- Initialize message list if p_init_msg_list is set to TRUE.
1733 IF FND_API.to_Boolean( p_init_msg_list )
1734 THEN
1735 FND_MSG_PUB.initialize;
1736 END IF;
1737
1738 -- Initialize API return status to SUCCESS
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740
1741 -- Hint: Validate data
1742 -- If data not valid
1743 -- THEN
1744 -- x_return_status := FND_API.G_RET_STS_ERROR;
1745 -- Debug Message
1746 IF g_debug THEN
1747 OZF_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1748 END IF;
1749 -- Standard call to get message count and if count is 1, get message info.
1750 FND_MSG_PUB.Count_And_Get
1751 (p_count => x_msg_count,
1752 p_data => x_msg_data
1753 );
1754 END Validate_trade_profile_Rec;
1755 -------------------------------------------------------------------------------
1756 END OZF_Trade_Profile_PVT;
[Home] [Help]