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