1 PACKAGE BODY OZF_SUPP_TRADE_PROFILE_PVT as
2 /* $Header: ozfvstpb.pls 120.5.12010000.8 2008/11/07 06:52:42 kpatro ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_SUPP_TRADE_PROFILE_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- 18-JUN-2008 KPATRO Fix for bug 7165146
12 -- 16-SEP-2008 kdass ER 7377460 - added DFFs for DPP section
13 -- 09-OCT-2008 kdass ER 7475578 - Supplier Trade Profile changes for Price Protection price increase enhancement
14 -- 03-Nov-2008 kpatro Bug#7524863 - 12.1 ARROW - FLEXFIELD VALUES NOT DISPLAYED ON UI AFTER UPDATE
15
16 -- NOTE
17 --
18 -- End of Comments
19 -- ===============================================================
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_SUPP_TRADE_PROFILE_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvstpb.pls';
22
23 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
24
25 CURSOR g_functional_currency_code_csr IS
26 SELECT gs.currency_code
27 FROM gl_sets_of_books gs,
28 ozf_sys_parameters osp
29 WHERE gs.set_of_books_id = osp.set_of_books_id
30 AND osp.org_id = MO_GLOBAL.get_current_org_id();
31 -------------------------------------------------------------------------------
32 PROCEDURE Complete_supp_trade_prfl_rec
33 (
34 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
35 x_complete_rec OUT NOCOPY supp_trade_profile_rec_type
36 )
37 IS
38
39 CURSOR c_supp_trade IS
40 SELECT *
41 FROM ozf_supp_trd_prfls_all
42 WHERE supp_trade_profile_id = p_supp_trade_profile_rec.supp_trade_profile_id;
43 l_supp_trade_profile_rec c_supp_trade%rowtype;
44
45 BEGIN
46 x_complete_rec := p_supp_trade_profile_rec;
47 OPEN c_supp_trade;
48 FETCH c_supp_trade INTO l_supp_trade_profile_rec;
49 CLOSE c_supp_trade;
50
51 -- This procedure should complete the record by going through all the items in the incoming record.
52
53
54 IF p_supp_trade_profile_rec.request_id = FND_API.g_miss_num THEN
55 x_complete_rec.request_id := null;
56 END IF;
57 IF p_supp_trade_profile_rec.request_id is null THEN
58 x_complete_rec.request_id := l_supp_trade_profile_rec.request_id;
59 END IF;
60
61
62 IF p_supp_trade_profile_rec.program_application_id = FND_API.g_miss_num THEN
63 x_complete_rec.program_application_id := null;
64 END IF;
65 IF p_supp_trade_profile_rec.program_application_id is null THEN
66 x_complete_rec.program_application_id := l_supp_trade_profile_rec.program_application_id;
67 END IF;
68
69
70 IF p_supp_trade_profile_rec.program_update_date = FND_API.g_miss_date THEN
71 x_complete_rec.program_update_date := null;
72 END IF;
73 IF p_supp_trade_profile_rec.program_update_date is null THEN
74 x_complete_rec.program_update_date := l_supp_trade_profile_rec.program_update_date;
75 END IF;
76
77
78 IF p_supp_trade_profile_rec.program_id = FND_API.g_miss_num THEN
79 x_complete_rec.program_id := null;
80 END IF;
81 IF p_supp_trade_profile_rec.program_id is null THEN
82 x_complete_rec.program_id := l_supp_trade_profile_rec.program_id;
83 END IF;
84
85
86 IF p_supp_trade_profile_rec.created_from = FND_API.g_miss_char THEN
87 x_complete_rec.created_from := null;
88 END IF;
89 IF p_supp_trade_profile_rec.created_from is null THEN
90 x_complete_rec.created_from := l_supp_trade_profile_rec.created_from;
91 END IF;
92
93
94 IF p_supp_trade_profile_rec.party_id = FND_API.g_miss_num THEN
95 x_complete_rec.party_id := null;
96 END IF;
97 IF p_supp_trade_profile_rec.party_id is null THEN
98 x_complete_rec.party_id := l_supp_trade_profile_rec.party_id;
99 END IF;
100
101 IF p_supp_trade_profile_rec.site_use_id = FND_API.g_miss_num THEN
102 x_complete_rec.site_use_id := null;
103 END IF;
104 IF p_supp_trade_profile_rec.site_use_id is null THEN
105 x_complete_rec.site_use_id := l_supp_trade_profile_rec.site_use_id;
106 END IF;
107
108
109 IF p_supp_trade_profile_rec.cust_account_id = FND_API.g_miss_num THEN
110 x_complete_rec.cust_account_id := null;
111 END IF;
112 IF p_supp_trade_profile_rec.cust_account_id is null THEN
113 x_complete_rec.cust_account_id := l_supp_trade_profile_rec.cust_account_id;
114 END IF;
115
116
117 IF p_supp_trade_profile_rec.cust_acct_site_id = FND_API.g_miss_num THEN
118 x_complete_rec.cust_acct_site_id := null;
119 END IF;
120 IF p_supp_trade_profile_rec.cust_acct_site_id is null THEN
121 x_complete_rec.cust_acct_site_id := l_supp_trade_profile_rec.cust_acct_site_id;
122 END IF;
123
124 IF p_supp_trade_profile_rec.supplier_id = FND_API.g_miss_num THEN
125 x_complete_rec.supplier_id := null;
126 END IF;
127 IF p_supp_trade_profile_rec.supplier_id is null THEN
128 x_complete_rec.supplier_id := l_supp_trade_profile_rec.supplier_id;
129 END IF;
130
131 IF p_supp_trade_profile_rec.supplier_site_id = FND_API.g_miss_num THEN
132 x_complete_rec.supplier_site_id := null;
133 END IF;
134 IF p_supp_trade_profile_rec.supplier_site_id is null THEN
135 x_complete_rec.supplier_site_id := l_supp_trade_profile_rec.supplier_site_id;
136 END IF;
137
138 IF p_supp_trade_profile_rec.attribute_category = FND_API.g_miss_char THEN
139 x_complete_rec.attribute_category := null;
140 END IF;
141 IF p_supp_trade_profile_rec.attribute_category is null THEN
142 x_complete_rec.attribute_category := l_supp_trade_profile_rec.attribute_category;
143 END IF;
144
145 IF p_supp_trade_profile_rec.dpp_attribute_category = FND_API.g_miss_char THEN
146 x_complete_rec.dpp_attribute_category := null;
147 END IF;
148 IF p_supp_trade_profile_rec.dpp_attribute_category is null THEN
149 x_complete_rec.dpp_attribute_category := l_supp_trade_profile_rec.dpp_attribute_category;
150 END IF;
151
152 --pre-approval -flag
153 IF p_supp_trade_profile_rec.pre_approval_flag = FND_API.g_miss_char THEN
154 x_complete_rec.pre_approval_flag := null;
155 END IF;
156 IF p_supp_trade_profile_rec.pre_approval_flag is null THEN
157 x_complete_rec.pre_approval_flag := l_supp_trade_profile_rec.pre_approval_flag;
158 END IF;
159 --approval_communication
160 IF p_supp_trade_profile_rec.approval_communication = FND_API.g_miss_char THEN
161 x_complete_rec.approval_communication := null;
162 END IF;
163 IF p_supp_trade_profile_rec.approval_communication is null THEN
164 x_complete_rec.approval_communication := l_supp_trade_profile_rec.approval_communication;
165 END IF;
166 --gl_contra_liability_acct
167 IF p_supp_trade_profile_rec.gl_contra_liability_acct = FND_API.g_miss_num THEN
168 x_complete_rec.gl_contra_liability_acct := null;
169 END IF;
170 IF p_supp_trade_profile_rec.gl_contra_liability_acct is null THEN
171 x_complete_rec.gl_contra_liability_acct := l_supp_trade_profile_rec.gl_contra_liability_acct;
172 END IF;
173 --gl_cost_adjustment_acct
174 IF p_supp_trade_profile_rec.gl_cost_adjustment_acct = FND_API.g_miss_num THEN
175 x_complete_rec.gl_cost_adjustment_acct := null;
176 END IF;
177 IF p_supp_trade_profile_rec.gl_cost_adjustment_acct is null THEN
178 x_complete_rec.gl_cost_adjustment_acct := l_supp_trade_profile_rec.gl_cost_adjustment_acct;
179 END IF;
180 --default_days_covered
181 IF p_supp_trade_profile_rec.default_days_covered = FND_API.g_miss_num THEN
182 x_complete_rec.default_days_covered := null;
183 END IF;
184 IF p_supp_trade_profile_rec.default_days_covered is null THEN
185 x_complete_rec.default_days_covered := l_supp_trade_profile_rec.default_days_covered;
186 END IF;
187 --create_claim_price_increase
188 IF p_supp_trade_profile_rec.create_claim_price_increase = FND_API.g_miss_char THEN
189 x_complete_rec.create_claim_price_increase := null;
190 END IF;
191 IF p_supp_trade_profile_rec.create_claim_price_increase is null THEN
192 x_complete_rec.create_claim_price_increase := l_supp_trade_profile_rec.create_claim_price_increase;
193 END IF;
194 --allow_qty_increase
195 IF p_supp_trade_profile_rec.allow_qty_increase = FND_API.g_miss_char THEN
196 x_complete_rec.allow_qty_increase := null;
197 END IF;
198 IF p_supp_trade_profile_rec.allow_qty_increase is null THEN
199 x_complete_rec.allow_qty_increase := l_supp_trade_profile_rec.allow_qty_increase;
200 END IF;
201 --qty_increase_tolerance
202 IF p_supp_trade_profile_rec.qty_increase_tolerance = FND_API.g_miss_num THEN
203 x_complete_rec.qty_increase_tolerance := null;
204 END IF;
205 IF p_supp_trade_profile_rec.qty_increase_tolerance is null THEN
206 x_complete_rec.qty_increase_tolerance := l_supp_trade_profile_rec.qty_increase_tolerance;
207 END IF;
208 --authorization_period
209 IF p_supp_trade_profile_rec.authorization_period = FND_API.g_miss_num THEN
210 x_complete_rec.authorization_period := null;
211 END IF;
212 IF p_supp_trade_profile_rec.authorization_period is null THEN
213 x_complete_rec.authorization_period := l_supp_trade_profile_rec.authorization_period;
214 END IF;
215 --grace_days
216 IF p_supp_trade_profile_rec.grace_days = FND_API.g_miss_num THEN
217 x_complete_rec.grace_days := null;
218 END IF;
219 IF p_supp_trade_profile_rec.grace_days is null THEN
220 x_complete_rec.grace_days := l_supp_trade_profile_rec.grace_days;
221 END IF;
222 --request_communication
223 IF p_supp_trade_profile_rec.request_communication = FND_API.g_miss_char THEN
224 x_complete_rec.request_communication := null;
225 END IF;
226 IF p_supp_trade_profile_rec.request_communication is null THEN
227 x_complete_rec.request_communication := l_supp_trade_profile_rec.request_communication;
228 END IF;
229 --claim_communication
230 IF p_supp_trade_profile_rec.claim_communication = FND_API.g_miss_char THEN
231 x_complete_rec.claim_communication := null;
232 END IF;
233 IF p_supp_trade_profile_rec.claim_communication is null THEN
234 x_complete_rec.claim_communication := l_supp_trade_profile_rec.claim_communication;
235 END IF;
236 --claim_frequency
237 IF p_supp_trade_profile_rec.claim_frequency = FND_API.g_miss_num THEN
238 x_complete_rec.claim_frequency := null;
239 END IF;
240 IF p_supp_trade_profile_rec.claim_frequency is null THEN
241 x_complete_rec.claim_frequency := l_supp_trade_profile_rec.claim_frequency;
242 END IF;
243 --claim_frequency_unit
244 IF p_supp_trade_profile_rec.claim_frequency_unit = FND_API.g_miss_char THEN
245 x_complete_rec.claim_frequency_unit := null;
246 END IF;
247 IF p_supp_trade_profile_rec.claim_frequency_unit is null THEN
248 x_complete_rec.claim_frequency_unit := l_supp_trade_profile_rec.claim_frequency_unit;
249 END IF;
250 --claim_computation_basis
251 IF p_supp_trade_profile_rec.claim_computation_basis = FND_API.g_miss_num THEN
252 x_complete_rec.claim_computation_basis := null;
253 END IF;
254 IF p_supp_trade_profile_rec.claim_computation_basis is null THEN
255 x_complete_rec.claim_computation_basis := l_supp_trade_profile_rec.claim_computation_basis;
256 END IF;
257 --claim_currency_code
258 IF p_supp_trade_profile_rec.claim_currency_code = FND_API.g_miss_char THEN
259 x_complete_rec.claim_currency_code := null;
260 END IF;
261 IF p_supp_trade_profile_rec.claim_currency_code is null THEN
262 x_complete_rec.claim_currency_code := l_supp_trade_profile_rec.claim_currency_code;
263 END IF;
264 --auto_debit
265 IF p_supp_trade_profile_rec.auto_debit = FND_API.g_miss_char THEN
266 x_complete_rec.auto_debit := null;
267 END IF;
268 IF p_supp_trade_profile_rec.auto_debit is null THEN
269 x_complete_rec.auto_debit := l_supp_trade_profile_rec.auto_debit;
270 END IF;
271
272 IF p_supp_trade_profile_rec.min_claim_amt = FND_API.g_miss_num THEN
273 x_complete_rec.min_claim_amt := null;
274 END IF;
275 IF p_supp_trade_profile_rec.min_claim_amt is null THEN
276 x_complete_rec.min_claim_amt := l_supp_trade_profile_rec.min_claim_amt;
277 END IF;
278
279 IF p_supp_trade_profile_rec.min_claim_amt_line_lvl = FND_API.g_miss_num THEN
280 x_complete_rec.min_claim_amt_line_lvl := null;
281 END IF;
282 IF p_supp_trade_profile_rec.min_claim_amt_line_lvl is null THEN
283 x_complete_rec.min_claim_amt_line_lvl := l_supp_trade_profile_rec.min_claim_amt_line_lvl;
284 END IF;
285
286 IF p_supp_trade_profile_rec.days_before_claiming_debit = FND_API.g_miss_num THEN
287 x_complete_rec.days_before_claiming_debit := null;
288 END IF;
289 IF p_supp_trade_profile_rec.days_before_claiming_debit is null THEN
290 x_complete_rec.days_before_claiming_debit := l_supp_trade_profile_rec.days_before_claiming_debit;
291 END IF;
292 IF p_supp_trade_profile_rec.org_id = FND_API.g_miss_num THEN
293 x_complete_rec.org_id := null;
294 END IF;
295 IF p_supp_trade_profile_rec.org_id is null THEN
296 x_complete_rec.org_id := l_supp_trade_profile_rec.org_id;
297 END IF;
298
299 END Complete_supp_trade_prfl_rec;
300 -------------------------------------------------------------------------------
301 PROCEDURE populate_supp_defaults(
302 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
303 x_supp_trade_profile_rec OUT NOCOPY supp_trade_profile_rec_type,
304 x_return_status OUT NOCOPY VARCHAR2
305 )
306 IS
307 l_cust_acct_site_id NUMBER;
308
309 L_API_NAME CONSTANT VARCHAR2(30) := 'populate_supp_defaults';
310
311 CURSOR c_cust_acct_site_id (a_id IN NUMBER) IS
312 SELECT cust_acct_site_id
313 from HZ_CUST_SITE_USES
314 WHERE site_use_id = a_id;
315 BEGIN
316 x_supp_trade_profile_rec := p_supp_trade_profile_rec;
317
318 /* -- defaulting flags not shown on screen and mandatory in db
319 x_supp_trade_profile_rec.internet_deal_view_flag :='F';
320 x_supp_trade_profile_rec.print_flag :='F';
321 */
322 -- set pre-approval-flag to N if null
323 IF x_supp_trade_profile_rec.pre_approval_flag = FND_API.g_miss_char OR
324 x_supp_trade_profile_rec.pre_approval_flag IS NULL
325 THEN
326 x_supp_trade_profile_rec.pre_approval_flag :='N';
327 END IF;
328
329 IF x_supp_trade_profile_rec.allow_qty_increase = FND_API.g_miss_char OR
330 x_supp_trade_profile_rec.allow_qty_increase IS NULL
331 THEN
332 x_supp_trade_profile_rec.allow_qty_increase :='N';
333 END IF;
334
335 IF x_supp_trade_profile_rec.auto_debit = FND_API.g_miss_char OR
336 x_supp_trade_profile_rec.auto_debit IS NULL
337 THEN
338 x_supp_trade_profile_rec.auto_debit :='N';
339 END IF;
340
341 -- default cust_acct_site_id if site use is is found
342 IF x_supp_trade_profile_rec.cust_account_id <> FND_API.g_miss_num OR
343 x_supp_trade_profile_rec.cust_account_id IS NOT NULL
344 THEN
345 IF x_supp_trade_profile_rec.site_use_id <> FND_API.g_miss_num OR
346 x_supp_trade_profile_rec.site_use_id IS NOT NULL
347 THEN
348 OPEN c_cust_acct_site_id(x_supp_trade_profile_rec.site_use_id);
349 FETCH c_cust_acct_site_id INTO l_cust_acct_site_id;
350 CLOSE c_cust_acct_site_id;
351 END IF;
352 x_supp_trade_profile_rec.cust_acct_site_id :=l_cust_acct_site_id;
353 END IF;
354 -- Start of Fix for Bug 7165146
355 IF x_supp_trade_profile_rec.default_days_covered = FND_API.g_miss_num OR
356 x_supp_trade_profile_rec.default_days_covered IS NULL
357 THEN
358 x_supp_trade_profile_rec.default_days_covered := null;
359 END IF;
360
361 IF x_supp_trade_profile_rec.create_claim_price_increase = FND_API.g_miss_char OR
362 x_supp_trade_profile_rec.create_claim_price_increase IS NULL
363 THEN
364 x_supp_trade_profile_rec.create_claim_price_increase := null;
365 END IF;
366
367
368 IF x_supp_trade_profile_rec.qty_increase_tolerance = FND_API.g_miss_num OR
369 x_supp_trade_profile_rec.qty_increase_tolerance IS NULL
370 THEN
371 x_supp_trade_profile_rec.qty_increase_tolerance := null;
372 END IF;
373
374 IF x_supp_trade_profile_rec.authorization_period = FND_API.g_miss_num OR
375 x_supp_trade_profile_rec.authorization_period IS NULL
376 THEN
377 x_supp_trade_profile_rec.authorization_period := null;
378 END IF;
379
380 IF x_supp_trade_profile_rec.grace_days = FND_API.g_miss_num OR
381 x_supp_trade_profile_rec.grace_days IS NULL
382 THEN
383 x_supp_trade_profile_rec.grace_days := null;
384 END IF;
385
386 IF x_supp_trade_profile_rec.days_before_claiming_debit = FND_API.g_miss_num OR
387 x_supp_trade_profile_rec.days_before_claiming_debit IS NULL
388 THEN
389 x_supp_trade_profile_rec.days_before_claiming_debit := null;
390 END IF;
391 -- End of Fix for Bug 7165146
392
393 -- Fix for Bug 7524863
394 IF x_supp_trade_profile_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char OR
395 x_supp_trade_profile_rec.ATTRIBUTE_CATEGORY IS NULL
396 THEN
397 x_supp_trade_profile_rec.ATTRIBUTE_CATEGORY := null;
398 END IF;
399 IF x_supp_trade_profile_rec.ATTRIBUTE1 = FND_API.g_miss_char OR
400 x_supp_trade_profile_rec.ATTRIBUTE1 IS NULL
401 THEN
402 x_supp_trade_profile_rec.ATTRIBUTE1 := null;
403 END IF;
404 IF x_supp_trade_profile_rec.ATTRIBUTE2 = FND_API.g_miss_char OR
405 x_supp_trade_profile_rec.ATTRIBUTE2 IS NULL
406 THEN
407 x_supp_trade_profile_rec.ATTRIBUTE2 := null;
408 END IF;
409 IF x_supp_trade_profile_rec.ATTRIBUTE3 = FND_API.g_miss_char OR
410 x_supp_trade_profile_rec.ATTRIBUTE3 IS NULL
411 THEN
412 x_supp_trade_profile_rec.ATTRIBUTE3 := null;
413 END IF;
414 IF x_supp_trade_profile_rec.ATTRIBUTE4 = FND_API.g_miss_char OR
415 x_supp_trade_profile_rec.ATTRIBUTE4 IS NULL
416 THEN
417 x_supp_trade_profile_rec.ATTRIBUTE4 := null;
418 END IF;
419 IF x_supp_trade_profile_rec.ATTRIBUTE5 = FND_API.g_miss_char OR
420 x_supp_trade_profile_rec.ATTRIBUTE5 IS NULL
421 THEN
422 x_supp_trade_profile_rec.ATTRIBUTE5 := null;
423 END IF;
424 IF x_supp_trade_profile_rec.ATTRIBUTE6 = FND_API.g_miss_char OR
425 x_supp_trade_profile_rec.ATTRIBUTE6 IS NULL
426 THEN
427 x_supp_trade_profile_rec.ATTRIBUTE6 := null;
428 END IF;
429 IF x_supp_trade_profile_rec.ATTRIBUTE7 = FND_API.g_miss_char OR
430 x_supp_trade_profile_rec.ATTRIBUTE7 IS NULL
431 THEN
432 x_supp_trade_profile_rec.ATTRIBUTE7 := null;
433 END IF;
434 IF x_supp_trade_profile_rec.ATTRIBUTE8 = FND_API.g_miss_char OR
435 x_supp_trade_profile_rec.ATTRIBUTE8 IS NULL
436 THEN
437 x_supp_trade_profile_rec.ATTRIBUTE8 := null;
438 END IF;
439 IF x_supp_trade_profile_rec.ATTRIBUTE9 = FND_API.g_miss_char OR
440 x_supp_trade_profile_rec.ATTRIBUTE9 IS NULL
441 THEN
442 x_supp_trade_profile_rec.ATTRIBUTE9 := null;
443 END IF;
444 IF x_supp_trade_profile_rec.ATTRIBUTE10 = FND_API.g_miss_char OR
445 x_supp_trade_profile_rec.ATTRIBUTE10 IS NULL
446 THEN
447 x_supp_trade_profile_rec.ATTRIBUTE10 := null;
448 END IF;
449 IF x_supp_trade_profile_rec.ATTRIBUTE11 = FND_API.g_miss_char OR
450 x_supp_trade_profile_rec.ATTRIBUTE11 IS NULL
451 THEN
452 x_supp_trade_profile_rec.ATTRIBUTE11 := null;
453 END IF;
454 IF x_supp_trade_profile_rec.ATTRIBUTE12 = FND_API.g_miss_char OR
455 x_supp_trade_profile_rec.ATTRIBUTE12 IS NULL
456 THEN
457 x_supp_trade_profile_rec.ATTRIBUTE12 := null;
458 END IF;
459 IF x_supp_trade_profile_rec.ATTRIBUTE13 = FND_API.g_miss_char OR
460 x_supp_trade_profile_rec.ATTRIBUTE13 IS NULL
461 THEN
462 x_supp_trade_profile_rec.ATTRIBUTE13 := null;
463 END IF;
464 IF x_supp_trade_profile_rec.ATTRIBUTE14 = FND_API.g_miss_char OR
465 x_supp_trade_profile_rec.ATTRIBUTE14 IS NULL
466 THEN
467 x_supp_trade_profile_rec.ATTRIBUTE14 := null;
468 END IF;
469 IF x_supp_trade_profile_rec.ATTRIBUTE15 = FND_API.g_miss_char OR
470 x_supp_trade_profile_rec.ATTRIBUTE15 IS NULL
471 THEN
472 x_supp_trade_profile_rec.ATTRIBUTE15 := null;
473 END IF;
474 IF x_supp_trade_profile_rec.ATTRIBUTE16 = FND_API.g_miss_char OR
475 x_supp_trade_profile_rec.ATTRIBUTE16 IS NULL
476 THEN
477 x_supp_trade_profile_rec.ATTRIBUTE16 := null;
478 END IF;
479 IF x_supp_trade_profile_rec.ATTRIBUTE17 = FND_API.g_miss_char OR
480 x_supp_trade_profile_rec.ATTRIBUTE17 IS NULL
481 THEN
482 x_supp_trade_profile_rec.ATTRIBUTE17 := null;
483 END IF;
484 IF x_supp_trade_profile_rec.ATTRIBUTE18 = FND_API.g_miss_char OR
485 x_supp_trade_profile_rec.ATTRIBUTE18 IS NULL
486 THEN
487 x_supp_trade_profile_rec.ATTRIBUTE18 := null;
488 END IF;
489 IF x_supp_trade_profile_rec.ATTRIBUTE19 = FND_API.g_miss_char OR
490 x_supp_trade_profile_rec.ATTRIBUTE19 IS NULL
491 THEN
492 x_supp_trade_profile_rec.ATTRIBUTE19 := null;
493 END IF;
494 IF x_supp_trade_profile_rec.ATTRIBUTE20 = FND_API.g_miss_char OR
495 x_supp_trade_profile_rec.ATTRIBUTE20 IS NULL
496 THEN
497 x_supp_trade_profile_rec.ATTRIBUTE20 := null;
498 END IF;
499 IF x_supp_trade_profile_rec.ATTRIBUTE21 = FND_API.g_miss_char OR
500 x_supp_trade_profile_rec.ATTRIBUTE21 IS NULL
501 THEN
502 x_supp_trade_profile_rec.ATTRIBUTE21 := null;
503 END IF;
504 IF x_supp_trade_profile_rec.ATTRIBUTE22 = FND_API.g_miss_char OR
505 x_supp_trade_profile_rec.ATTRIBUTE22 IS NULL
506 THEN
507 x_supp_trade_profile_rec.ATTRIBUTE22 := null;
508 END IF;
509 IF x_supp_trade_profile_rec.ATTRIBUTE23 = FND_API.g_miss_char OR
510 x_supp_trade_profile_rec.ATTRIBUTE23 IS NULL
511 THEN
512 x_supp_trade_profile_rec.ATTRIBUTE23 := null;
513 END IF;
514 IF x_supp_trade_profile_rec.ATTRIBUTE24 = FND_API.g_miss_char OR
515 x_supp_trade_profile_rec.ATTRIBUTE24 IS NULL
516 THEN
517 x_supp_trade_profile_rec.ATTRIBUTE24 := null;
518 END IF;
519 IF x_supp_trade_profile_rec.ATTRIBUTE25 = FND_API.g_miss_char OR
520 x_supp_trade_profile_rec.ATTRIBUTE25 IS NULL
521 THEN
522 x_supp_trade_profile_rec.ATTRIBUTE25 := null;
523 END IF;
524 IF x_supp_trade_profile_rec.ATTRIBUTE26 = FND_API.g_miss_char OR
525 x_supp_trade_profile_rec.ATTRIBUTE26 IS NULL
526 THEN
527 x_supp_trade_profile_rec.ATTRIBUTE26 := null;
528 END IF;
529 IF x_supp_trade_profile_rec.ATTRIBUTE27 = FND_API.g_miss_char OR
530 x_supp_trade_profile_rec.ATTRIBUTE27 IS NULL
531 THEN
532 x_supp_trade_profile_rec.ATTRIBUTE27 := null;
533 END IF;
534 IF x_supp_trade_profile_rec.ATTRIBUTE28 = FND_API.g_miss_char OR
535 x_supp_trade_profile_rec.ATTRIBUTE28 IS NULL
536 THEN
537 x_supp_trade_profile_rec.ATTRIBUTE28 := null;
538 END IF;
539 IF x_supp_trade_profile_rec.ATTRIBUTE29 = FND_API.g_miss_char OR
540 x_supp_trade_profile_rec.ATTRIBUTE29 IS NULL
541 THEN
542 x_supp_trade_profile_rec.ATTRIBUTE29 := null;
543 END IF;
544 IF x_supp_trade_profile_rec.ATTRIBUTE30 = FND_API.g_miss_char OR
545 x_supp_trade_profile_rec.ATTRIBUTE30 IS NULL
546 THEN
547 x_supp_trade_profile_rec.ATTRIBUTE30 := null;
548 END IF;
549
550 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE_CATEGORY = FND_API.g_miss_char OR
551 x_supp_trade_profile_rec.DPP_ATTRIBUTE_CATEGORY IS NULL
552 THEN
553 x_supp_trade_profile_rec.DPP_ATTRIBUTE_CATEGORY := null;
554 END IF;
555
556 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE1 = FND_API.g_miss_char OR
557 x_supp_trade_profile_rec.DPP_ATTRIBUTE1 IS NULL
558 THEN
559 x_supp_trade_profile_rec.DPP_ATTRIBUTE1 := null;
560 END IF;
561 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE2 = FND_API.g_miss_char OR
562 x_supp_trade_profile_rec.DPP_ATTRIBUTE2 IS NULL
563 THEN
564 x_supp_trade_profile_rec.DPP_ATTRIBUTE2 := null;
565 END IF;
566 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE3 = FND_API.g_miss_char OR
567 x_supp_trade_profile_rec.DPP_ATTRIBUTE3 IS NULL
568 THEN
569 x_supp_trade_profile_rec.DPP_ATTRIBUTE3 := null;
570 END IF;
571 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE4 = FND_API.g_miss_char OR
572 x_supp_trade_profile_rec.DPP_ATTRIBUTE4 IS NULL
573 THEN
574 x_supp_trade_profile_rec.DPP_ATTRIBUTE4 := null;
575 END IF;
576 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE5 = FND_API.g_miss_char OR
577 x_supp_trade_profile_rec.DPP_ATTRIBUTE5 IS NULL
578 THEN
579 x_supp_trade_profile_rec.DPP_ATTRIBUTE5 := null;
580 END IF;
581 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE6 = FND_API.g_miss_char OR
582 x_supp_trade_profile_rec.DPP_ATTRIBUTE6 IS NULL
583 THEN
584 x_supp_trade_profile_rec.DPP_ATTRIBUTE6 := null;
585 END IF;
586 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE7 = FND_API.g_miss_char OR
587 x_supp_trade_profile_rec.DPP_ATTRIBUTE7 IS NULL
588 THEN
589 x_supp_trade_profile_rec.DPP_ATTRIBUTE7 := null;
590 END IF;
591 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE8 = FND_API.g_miss_char OR
592 x_supp_trade_profile_rec.DPP_ATTRIBUTE8 IS NULL
593 THEN
594 x_supp_trade_profile_rec.DPP_ATTRIBUTE8 := null;
595 END IF;
596 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE9 = FND_API.g_miss_char OR
597 x_supp_trade_profile_rec.DPP_ATTRIBUTE9 IS NULL
598 THEN
599 x_supp_trade_profile_rec.DPP_ATTRIBUTE9 := null;
600 END IF;
601 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE10 = FND_API.g_miss_char OR
602 x_supp_trade_profile_rec.DPP_ATTRIBUTE10 IS NULL
603 THEN
604 x_supp_trade_profile_rec.DPP_ATTRIBUTE10 := null;
605 END IF;
606 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE11 = FND_API.g_miss_char OR
607 x_supp_trade_profile_rec.DPP_ATTRIBUTE11 IS NULL
608 THEN
609 x_supp_trade_profile_rec.DPP_ATTRIBUTE11 := null;
610 END IF;
611 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE12 = FND_API.g_miss_char OR
612 x_supp_trade_profile_rec.DPP_ATTRIBUTE12 IS NULL
613 THEN
614 x_supp_trade_profile_rec.DPP_ATTRIBUTE12 := null;
615 END IF;
616 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE13 = FND_API.g_miss_char OR
617 x_supp_trade_profile_rec.DPP_ATTRIBUTE13 IS NULL
618 THEN
619 x_supp_trade_profile_rec.DPP_ATTRIBUTE13 := null;
620 END IF;
621 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE14 = FND_API.g_miss_char OR
622 x_supp_trade_profile_rec.DPP_ATTRIBUTE14 IS NULL
623 THEN
624 x_supp_trade_profile_rec.DPP_ATTRIBUTE14 := null;
625 END IF;
626 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE15 = FND_API.g_miss_char OR
627 x_supp_trade_profile_rec.DPP_ATTRIBUTE15 IS NULL
628 THEN
629 x_supp_trade_profile_rec.DPP_ATTRIBUTE15 := null;
630 END IF;
631 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE16 = FND_API.g_miss_char OR
632 x_supp_trade_profile_rec.DPP_ATTRIBUTE16 IS NULL
633 THEN
634 x_supp_trade_profile_rec.DPP_ATTRIBUTE16 := null;
635 END IF;
636 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE17 = FND_API.g_miss_char OR
637 x_supp_trade_profile_rec.DPP_ATTRIBUTE17 IS NULL
638 THEN
639 x_supp_trade_profile_rec.DPP_ATTRIBUTE17 := null;
640 END IF;
641 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE18 = FND_API.g_miss_char OR
642 x_supp_trade_profile_rec.DPP_ATTRIBUTE18 IS NULL
643 THEN
644 x_supp_trade_profile_rec.DPP_ATTRIBUTE18 := null;
645 END IF;
646 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE19 = FND_API.g_miss_char OR
647 x_supp_trade_profile_rec.DPP_ATTRIBUTE19 IS NULL
648 THEN
649 x_supp_trade_profile_rec.DPP_ATTRIBUTE19 := null;
650 END IF;
651 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE20 = FND_API.g_miss_char OR
652 x_supp_trade_profile_rec.DPP_ATTRIBUTE20 IS NULL
653 THEN
654 x_supp_trade_profile_rec.DPP_ATTRIBUTE20 := null;
655 END IF;
656 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE21 = FND_API.g_miss_char OR
657 x_supp_trade_profile_rec.DPP_ATTRIBUTE21 IS NULL
658 THEN
659 x_supp_trade_profile_rec.DPP_ATTRIBUTE21 := null;
660 END IF;
661 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE22 = FND_API.g_miss_char OR
662 x_supp_trade_profile_rec.DPP_ATTRIBUTE22 IS NULL
663 THEN
664 x_supp_trade_profile_rec.DPP_ATTRIBUTE22 := null;
665 END IF;
666 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE23 = FND_API.g_miss_char OR
667 x_supp_trade_profile_rec.DPP_ATTRIBUTE23 IS NULL
668 THEN
669 x_supp_trade_profile_rec.DPP_ATTRIBUTE23 := null;
670 END IF;
671 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE24 = FND_API.g_miss_char OR
672 x_supp_trade_profile_rec.DPP_ATTRIBUTE24 IS NULL
673 THEN
674 x_supp_trade_profile_rec.DPP_ATTRIBUTE24 := null;
675 END IF;
676 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE25 = FND_API.g_miss_char OR
677 x_supp_trade_profile_rec.DPP_ATTRIBUTE25 IS NULL
678 THEN
679 x_supp_trade_profile_rec.DPP_ATTRIBUTE25 := null;
680 END IF;
681 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE26 = FND_API.g_miss_char OR
682 x_supp_trade_profile_rec.DPP_ATTRIBUTE26 IS NULL
683 THEN
684 x_supp_trade_profile_rec.DPP_ATTRIBUTE26 := null;
685 END IF;
686 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE27 = FND_API.g_miss_char OR
687 x_supp_trade_profile_rec.DPP_ATTRIBUTE27 IS NULL
688 THEN
689 x_supp_trade_profile_rec.DPP_ATTRIBUTE27 := null;
690 END IF;
691 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE28 = FND_API.g_miss_char OR
692 x_supp_trade_profile_rec.DPP_ATTRIBUTE28 IS NULL
693 THEN
694 x_supp_trade_profile_rec.DPP_ATTRIBUTE28 := null;
695 END IF;
696 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE29 = FND_API.g_miss_char OR
697 x_supp_trade_profile_rec.DPP_ATTRIBUTE29 IS NULL
698 THEN
699 x_supp_trade_profile_rec.DPP_ATTRIBUTE29 := null;
700 END IF;
701 IF x_supp_trade_profile_rec.DPP_ATTRIBUTE30 = FND_API.g_miss_char OR
702 x_supp_trade_profile_rec.DPP_ATTRIBUTE30 IS NULL
703 THEN
704 x_supp_trade_profile_rec.DPP_ATTRIBUTE30 := null;
705 END IF;
706 -- End Fix for Bug 7524863
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
712 THEN
713 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
714 END IF;
715 END populate_supp_defaults;
716 --------------------------------------------------------------------------------
717 PROCEDURE Create_Supp_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_supp_trade_profile_rec IN supp_trade_profile_rec_type,
726 x_supp_trade_profile_id OUT NOCOPY NUMBER
727 )
728 IS
729 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Supp_Trade_Profile';
730 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
731 l_return_status_full VARCHAR2(1);
732 l_object_version_number NUMBER := 1;
733 l_org_id NUMBER ;
734 l_SUPP_TRADE_PROFILE_ID NUMBER;
735 l_CUST_ACCOUNT_ID NUMBER;
736 l_cust_acct_site_id NUMBER;
737 l_dummy NUMBER;
738 l_cust_dummy NUMBER;
739 l_party_dummy NUMBER;
740 l_party_dummy1 NUMBER;
741 l_party_id NUMBER;
742
743 l_supp_trade_profile_rec supp_trade_profile_rec_type;
744 l_x_supp_trade_profile_rec supp_trade_profile_rec_type;
745 l_null VARCHAR2(10) := 'NULL';
746
747 CURSOR c_id IS
748 SELECT ozf_supp_trd_prfls_all_s.NEXTVAL
749 FROM dual;
750
751 CURSOR c_id_exists (l_id IN NUMBER) IS
752 SELECT count(supp_trade_profile_id)
753 FROM ozf_supp_trd_prfls_all
754 WHERE SUPP_TRADE_PROFILE_ID = l_id;
755
756 CURSOR c_customer_id (p_id IN NUMBER) IS
757 SELECT cust_account_id
758 from HZ_CUST_ACCOUNTS
759 WHERE party_id = p_id
760 AND status = 'A';
761
762 CURSOR c_party_id (c_id IN NUMBER) IS
763 SELECT party_id
764 from HZ_CUST_ACCOUNTS
765 WHERE cust_account_id = c_id;
766
767 BEGIN
768
769 -- Standard Start of API savepoint
770 SAVEPOINT Create_Supp_Trade_Profile_PVT;
771 IF g_debug THEN
772 OZF_UTILITY_PVT.debug_message('Private API: Create trade profile');
773 END IF;
774 -- Standard call to check for call compatibility.
775 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
776 p_api_version_number,
777 l_api_name,
778 G_PKG_NAME)
779 THEN
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781 END IF;
782
783 -- Initialize message list if p_init_msg_list is set to TRUE.
784 IF FND_API.to_Boolean( p_init_msg_list )
785 THEN
786 FND_MSG_PUB.initialize;
787 END IF;
788 -- Initialize API return status to SUCCESS
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790 -- Local variable initialization
791 --IF p_supp_trade_profile_rec.SUPP_TRADE_PROFILE_ID IS NULL OR
792 -- p_supp_trade_profile_rec.SUPP_TRADE_PROFILE_ID = FND_API.g_miss_num
793 -- THEN
794 LOOP
795 l_dummy := NULL;
796 OPEN c_id;
797 FETCH c_id INTO l_SUPP_TRADE_PROFILE_ID;
798 CLOSE c_id;
799
800 OPEN c_id_exists(l_SUPP_TRADE_PROFILE_ID);
801 FETCH c_id_exists INTO l_dummy;
802 CLOSE c_id_exists;
803 EXIT WHEN l_dummy=0;
804 END LOOP;
805 --END IF;
806
807 -- =========================================================================
808 -- Validate Environment
809 -- =========================================================================
810 IF FND_GLOBAL.User_Id IS NULL
811 THEN
812 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
813 FND_MESSAGE.set_name('OZF', 'OZF_USER_PROFILE_MISSING');
814 FND_MSG_PUB.add;
815 END IF;
816 RAISE FND_API.G_EXC_ERROR;
817 END IF;
818
819 -- Assign the record to a local variable
820 l_supp_trade_profile_rec := p_supp_trade_profile_rec;
821
822 -- populate defaults
823 populate_supp_defaults (p_supp_trade_profile_rec => l_supp_trade_profile_rec,
824 x_supp_trade_profile_rec => l_x_supp_trade_profile_rec,
825 x_return_status => x_return_status);
826
827 IF x_return_status = FND_API.g_ret_sts_error THEN
828 RAISE FND_API.G_EXC_ERROR;
829 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
831 END IF;
832 l_supp_trade_profile_rec := l_x_supp_trade_profile_rec;
833
834 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
835 THEN
836 -- Invoke validation procedures
837 Validate_supp_trade_profile(
838 p_api_version_number => 1.0,
839 p_init_msg_list => FND_API.G_FALSE,
840 p_validation_level => p_validation_level,
841 p_supp_trade_profile_rec =>l_supp_trade_profile_rec,
842 x_return_status => x_return_status,
843 x_msg_count => x_msg_count,
844 x_msg_data => x_msg_data
845 );
846 END IF;
847
848 IF x_return_status = FND_API.g_ret_sts_error THEN
849 RAISE FND_API.G_EXC_ERROR;
850 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 END IF;
853
854 -- Debug Message
855 -- Invoke table handler(OZF_supp_trd_prfls_PKG.Insert_Row)
856 BEGIN
857 OZF_supp_trd_prfls_PKG.Insert_Row(
858 px_supp_trade_profile_id => l_supp_trade_profile_id,
859 px_object_version_number => l_object_version_number,
860 p_last_update_date => SYSDATE,
861 p_last_updated_by => FND_GLOBAL.USER_ID,
862 p_creation_date => SYSDATE,
863 p_created_by => FND_GLOBAL.USER_ID,
864 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
865 p_request_id => l_supp_trade_profile_rec.request_id,
866 p_program_application_id => l_supp_trade_profile_rec.program_application_id,
867 p_program_update_date => l_supp_trade_profile_rec.program_update_date,
868 p_program_id => l_supp_trade_profile_rec.program_id,
869 p_created_from => l_supp_trade_profile_rec.created_from,
870 p_party_id => l_supp_trade_profile_rec.party_id,
871 p_site_use_id => l_supp_trade_profile_rec.site_use_id,
872 p_cust_account_id => l_supp_trade_profile_rec.cust_account_id,
873 p_cust_acct_site_id => l_supp_trade_profile_rec.cust_acct_site_id,
874 p_supplier_id => l_supp_trade_profile_rec.supplier_id,
875 p_supplier_site_id => l_supp_trade_profile_rec.supplier_site_id,
876 p_attribute_category => l_supp_trade_profile_rec.attribute_category,
877 p_attribute1 => l_supp_trade_profile_rec.attribute1,
878 p_attribute2 => l_supp_trade_profile_rec.attribute2,
879 p_attribute3 => l_supp_trade_profile_rec.attribute3,
880 p_attribute4 => l_supp_trade_profile_rec.attribute4,
881 p_attribute5 => l_supp_trade_profile_rec.attribute5,
882 p_attribute6 => l_supp_trade_profile_rec.attribute6,
883 p_attribute7 => l_supp_trade_profile_rec.attribute7,
884 p_attribute8 => l_supp_trade_profile_rec.attribute8,
885 p_attribute9 => l_supp_trade_profile_rec.attribute9,
886 p_attribute10 => l_supp_trade_profile_rec.attribute10,
887 p_attribute11 => l_supp_trade_profile_rec.attribute11,
888 p_attribute12 => l_supp_trade_profile_rec.attribute12,
889 p_attribute13 => l_supp_trade_profile_rec.attribute13,
890 p_attribute14 => l_supp_trade_profile_rec.attribute14,
891 p_attribute15 => l_supp_trade_profile_rec.attribute15,
892 p_attribute16 => l_supp_trade_profile_rec.attribute16,
893 p_attribute17 => l_supp_trade_profile_rec.attribute17,
894 p_attribute18 => l_supp_trade_profile_rec.attribute18,
895 p_attribute19 => l_supp_trade_profile_rec.attribute19,
896 p_attribute20 => l_supp_trade_profile_rec.attribute20,
897 p_attribute21 => l_supp_trade_profile_rec.attribute21,
898 p_attribute22 => l_supp_trade_profile_rec.attribute22,
899 p_attribute23 => l_supp_trade_profile_rec.attribute23,
900 p_attribute24 => l_supp_trade_profile_rec.attribute24,
901 p_attribute25 => l_supp_trade_profile_rec.attribute25,
902 p_attribute26 => l_supp_trade_profile_rec.attribute26,
903 p_attribute27 => l_supp_trade_profile_rec.attribute27,
904 p_attribute28 => l_supp_trade_profile_rec.attribute28,
905 p_attribute29 => l_supp_trade_profile_rec.attribute29,
906 p_attribute30 => l_supp_trade_profile_rec.attribute30,
907 p_dpp_attribute_category => l_supp_trade_profile_rec.dpp_attribute_category,
908 p_dpp_attribute1 => l_supp_trade_profile_rec.dpp_attribute1,
909 p_dpp_attribute2 => l_supp_trade_profile_rec.dpp_attribute2,
910 p_dpp_attribute3 => l_supp_trade_profile_rec.dpp_attribute3,
911 p_dpp_attribute4 => l_supp_trade_profile_rec.dpp_attribute4,
912 p_dpp_attribute5 => l_supp_trade_profile_rec.dpp_attribute5,
913 p_dpp_attribute6 => l_supp_trade_profile_rec.dpp_attribute6,
914 p_dpp_attribute7 => l_supp_trade_profile_rec.dpp_attribute7,
915 p_dpp_attribute8 => l_supp_trade_profile_rec.dpp_attribute8,
916 p_dpp_attribute9 => l_supp_trade_profile_rec.dpp_attribute9,
917 p_dpp_attribute10 => l_supp_trade_profile_rec.dpp_attribute10,
918 p_dpp_attribute11 => l_supp_trade_profile_rec.dpp_attribute11,
919 p_dpp_attribute12 => l_supp_trade_profile_rec.dpp_attribute12,
920 p_dpp_attribute13 => l_supp_trade_profile_rec.dpp_attribute13,
921 p_dpp_attribute14 => l_supp_trade_profile_rec.dpp_attribute14,
922 p_dpp_attribute15 => l_supp_trade_profile_rec.dpp_attribute15,
923 p_dpp_attribute16 => l_supp_trade_profile_rec.dpp_attribute16,
924 p_dpp_attribute17 => l_supp_trade_profile_rec.dpp_attribute17,
925 p_dpp_attribute18 => l_supp_trade_profile_rec.dpp_attribute18,
926 p_dpp_attribute19 => l_supp_trade_profile_rec.dpp_attribute19,
927 p_dpp_attribute20 => l_supp_trade_profile_rec.dpp_attribute20,
928 p_dpp_attribute21 => l_supp_trade_profile_rec.dpp_attribute21,
929 p_dpp_attribute22 => l_supp_trade_profile_rec.dpp_attribute22,
930 p_dpp_attribute23 => l_supp_trade_profile_rec.dpp_attribute23,
931 p_dpp_attribute24 => l_supp_trade_profile_rec.dpp_attribute24,
932 p_dpp_attribute25 => l_supp_trade_profile_rec.dpp_attribute25,
933 p_dpp_attribute26 => l_supp_trade_profile_rec.dpp_attribute26,
934 p_dpp_attribute27 => l_supp_trade_profile_rec.dpp_attribute27,
935 p_dpp_attribute28 => l_supp_trade_profile_rec.dpp_attribute28,
936 p_dpp_attribute29 => l_supp_trade_profile_rec.dpp_attribute29,
937 p_dpp_attribute30 => l_supp_trade_profile_rec.dpp_attribute30,
938 px_org_id => l_supp_trade_profile_rec.org_id ,
939 p_pre_approval_flag => l_supp_trade_profile_rec.pre_approval_flag ,
940 p_approval_communication => l_supp_trade_profile_rec.approval_communication ,
941 p_gl_contra_liability_acct => l_supp_trade_profile_rec.gl_contra_liability_acct ,
942 p_gl_cost_adjustment_acct => l_supp_trade_profile_rec.gl_cost_adjustment_acct ,
943 p_default_days_covered => l_supp_trade_profile_rec.default_days_covered ,
944 p_create_claim_price_increase => l_supp_trade_profile_rec.create_claim_price_increase ,
945 p_authorization_period => l_supp_trade_profile_rec.authorization_period ,
946 p_grace_days => l_supp_trade_profile_rec.grace_days ,
947 p_allow_qty_increase => l_supp_trade_profile_rec.allow_qty_increase ,
948 p_qty_increase_tolerance => l_supp_trade_profile_rec.qty_increase_tolerance ,
949 p_request_communication => l_supp_trade_profile_rec.request_communication ,
950 p_claim_communication => l_supp_trade_profile_rec.claim_communication ,
951 p_claim_frequency => l_supp_trade_profile_rec.claim_frequency ,
952 p_claim_frequency_unit => l_supp_trade_profile_rec.claim_frequency_unit ,
953 p_claim_computation_basis => l_supp_trade_profile_rec.claim_computation_basis ,
954 p_claim_currency_code => l_supp_trade_profile_rec.claim_currency_code ,
955 p_min_claim_amt => l_supp_trade_profile_rec.min_claim_amt ,
956 p_min_claim_amt_line_lvl => l_supp_trade_profile_rec.min_claim_amt_line_lvl ,
957 p_auto_debit => l_supp_trade_profile_rec.auto_debit ,
958 p_days_before_claiming_debit => l_supp_trade_profile_rec.days_before_claiming_debit
959
960 );
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
965 FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
966 FND_MSG_PUB.add;
967 END IF;
968
969 RAISE FND_API.G_EXC_ERROR;
970 END;
971 --
972 -- End of API body
973 --
974
975 -- Standard check for p_commit
976 IF FND_API.to_Boolean( p_commit )
977 THEN
978 COMMIT WORK;
979 END IF;
980
981 -- Debug Message
982 IF g_debug THEN
983 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
984 END IF;
985
986 -- Standard call to get message count and if count is 1, get message info.
987 FND_MSG_PUB.Count_And_Get
988 (p_count => x_msg_count,
989 p_data => x_msg_data
990 );
991
992 EXCEPTION
993 WHEN OZF_Utility_PVT.resource_locked THEN
994 x_return_status := FND_API.g_ret_sts_error;
995 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
996 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES_LOCKED');
997 FND_MSG_PUB.add;
998 END IF;
999 WHEN FND_API.G_EXC_ERROR THEN
1000 ROLLBACK TO Create_Supp_Trade_Profile_PVT;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 -- Standard call to get message count and if count=1, get the message
1003 FND_MSG_PUB.Count_And_Get (
1004 p_encoded => FND_API.G_FALSE,
1005 p_count => x_msg_count,
1006 p_data => x_msg_data
1007 );
1008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1009 ROLLBACK TO Create_Supp_Trade_Profile_PVT;
1010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1011 -- Standard call to get message count and if count=1, get the message
1012 FND_MSG_PUB.Count_And_Get (
1013 p_encoded => FND_API.G_FALSE,
1014 p_count => x_msg_count,
1015 p_data => x_msg_data
1016 );
1017 WHEN OTHERS THEN
1018 ROLLBACK TO Create_Supp_Trade_Profile_PVT;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1021 THEN
1022 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1023 END IF;
1024 -- Standard call to get message count and if count=1, get the message
1025 FND_MSG_PUB.Count_And_Get (
1026 p_encoded => FND_API.G_FALSE,
1027 p_count => x_msg_count,
1028 p_data => x_msg_data
1029 );
1030 End Create_Supp_Trade_Profile;
1031 -------------------------------------------------------------------------------
1032 PROCEDURE Update_Supp_Trade_Profile(
1033 p_api_version_number IN NUMBER,
1034 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1035 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1036 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1037 x_return_status OUT NOCOPY VARCHAR2,
1038 x_msg_count OUT NOCOPY NUMBER,
1039 x_msg_data OUT NOCOPY VARCHAR2,
1040 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1041 x_object_version_number OUT NOCOPY NUMBER
1042 )
1043 IS
1044 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Supp_Trade_Profile';
1045 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1046 -- Local Variables
1047 l_object_version_number NUMBER;
1048 l_SUPP_TRADE_PROFILE_ID NUMBER;
1049 l_cust_account_id NUMBER;
1050 l_cust_acct_site_id NUMBER;
1051 l_org_id NUMBER;
1052 l_supp_site_id NUMBER;
1053 l_supp_id NUMBER;
1054
1055 CURSOR c_supp_trade IS
1056 SELECT *
1057 FROM ozf_supp_trd_prfls_all
1058 WHERE supp_trade_profile_id = p_supp_trade_profile_rec.supp_trade_profile_id;
1059
1060 CURSOR c_get_supp_trade_profile(v_supp_trade_profile_id in NUMBER) IS
1061 SELECT *
1062 FROM ozf_supp_trd_prfls_all
1063 WHERE supp_trade_profile_id = v_supp_trade_profile_id;
1064
1065
1066 l_ref_supp_trade_profile_rec c_supp_trade%ROWTYPE;
1067 l_tar_supp_trade_profile_rec supp_trade_profile_rec_type := p_supp_trade_profile_rec;
1068 l_rowid ROWID;
1069 l_supp_trade_profile_rec supp_trade_profile_rec_type;
1070 l_x_supp_trade_profile_rec supp_trade_profile_rec_type;
1071
1072
1073 CURSOR check_supp_profile (p_id in number) IS
1074 SELECT supplier_id , supplier_site_id
1075 FROM ozf_supp_trd_prfls_all -- R12 Enhancements
1076 WHERE supp_trade_profile_id = p_id;
1077
1078
1079 BEGIN
1080 -- Standard Start of API savepoint
1081 SAVEPOINT UPDATE_Supp_Trade_Profile_PVT;
1082 -- OZF_UTILITY_PVT.debug_message('IN UPDATE_Supp_Trade_Profile_PVT org id '||p_supp_trade_profile_rec.org_id);
1083 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 -- Standard call to check for call compatibility
1085 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1086 p_api_version_number,
1087 l_api_name,
1088 G_PKG_NAME
1089 )
1090 THEN
1091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092 END IF;
1093 -- Initialize message list if p_init_msg_list is set to TRUE.
1094 IF FND_API.to_Boolean( p_init_msg_list )
1095 THEN
1096 FND_MSG_PUB.initialize;
1097 END IF;
1098 -- Debug Message
1099 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1100 -- Initialize API return status to SUCCESS
1101 x_return_status := FND_API.G_RET_STS_SUCCESS;
1102 l_org_id := l_tar_supp_trade_profile_rec.org_id; -- R12 Enhancements
1103
1104 -- Debug Message
1105 IF g_debug THEN
1106 OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
1107 END IF;
1108 OPEN c_supp_trade;
1109 FETCH c_supp_trade INTO l_ref_supp_trade_profile_rec;
1110 IF ( c_supp_trade%NOTFOUND) THEN
1111 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1112 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1113 FND_MSG_PUB.add;
1114 END IF;
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117 CLOSE c_supp_trade;
1118 -- Debug Message
1119 IF g_debug THEN
1120 OZF_UTILITY_PVT.debug_message('Private API: - Close Cursor');
1121 END IF;
1122
1123 IF (l_tar_supp_trade_profile_rec.object_version_number is NULL or
1124 l_tar_supp_trade_profile_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1126 FND_MESSAGE.set_name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
1127 FND_MSG_PUB.add;
1128 END IF;
1129 raise FND_API.G_EXC_ERROR;
1130 End if;
1131
1132 -- get the supplier and supplier site values for the supplier trade profile id
1133 OPEN check_supp_profile(p_supp_trade_profile_rec.supp_trade_profile_id);
1134 FETCH check_supp_profile INTO l_supp_id,l_supp_site_id;
1135 CLOSE check_supp_profile;
1136 -- set to miss num if value is null
1137 IF l_supp_id is null THEN
1138 l_supp_id := FND_API.G_MISS_NUM;
1139 END IF;
1140 IF l_supp_site_id is null THEN
1141 l_supp_site_id := FND_API.G_MISS_NUM;
1142 END IF;
1143
1144
1145 -- if the supplier and supplier site from the record are the same as in the db.then update else create
1146
1147 IF l_supp_id <> p_supp_trade_profile_rec.supplier_id or
1148 l_supp_site_id <> p_supp_trade_profile_rec.supplier_site_id
1149 THEN
1150 IF g_debug THEN
1151 OZF_UTILITY_PVT.debug_message('Calling create in update method');
1152 END IF;
1153 Create_Supp_Trade_Profile (
1154 p_api_version_number => 1.0,
1155 p_init_msg_list => FND_API.G_FALSE,
1156 p_commit => FND_API.G_FALSE,
1157 p_validation_level => p_validation_level,
1158 x_return_status => x_return_status,
1159 x_msg_count => x_msg_count,
1160 x_msg_data => x_msg_data,
1161 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1162 x_supp_trade_profile_id => l_supp_trade_profile_id
1163 );
1164 IF x_return_status = FND_API.g_ret_sts_error THEN
1165 RAISE FND_API.G_EXC_ERROR;
1166 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169 x_object_version_number := 1;
1170 ELSE -- update mode
1171 -- complete the record (get missing values filled from db)
1172 Complete_supp_trade_prfl_rec(
1173 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1174 x_complete_rec => l_supp_trade_profile_rec
1175 );
1176 IF g_debug THEN
1177 OZF_UTILITY_PVT.debug_message('In the update');
1178 END IF;
1179 -- populate defaults
1180 populate_supp_defaults (p_supp_trade_profile_rec => l_supp_trade_profile_rec,
1181 x_supp_trade_profile_rec => l_x_supp_trade_profile_rec,
1182 x_return_status => x_return_status);
1183
1184 IF x_return_status = FND_API.g_ret_sts_error THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188 END IF;
1189 l_supp_trade_profile_rec := l_x_supp_trade_profile_rec;
1190
1191
1192 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1193 Check_supp_trd_prfl_items(
1194 p_supp_trade_profile_rec => l_supp_trade_profile_rec,
1195 p_validation_mode => JTF_PLSQL_API.g_update,
1196 x_return_status => x_return_status
1197 );
1198 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1199 RAISE FND_API.G_EXC_ERROR;
1200 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1202 END IF;
1203 END IF;
1204
1205 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1206 Validate_supp_trd_prfl_rec(
1207 p_api_version_number => 1.0,
1208 p_init_msg_list => FND_API.G_FALSE,
1209 x_return_status => x_return_status,
1210 x_msg_count => x_msg_count,
1211 x_msg_data => x_msg_data,
1212 p_supp_trade_profile_rec => l_supp_trade_profile_rec
1213 );
1214 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1215 RAISE FND_API.G_EXC_ERROR;
1216 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1218 END IF;
1219 END IF;
1220
1221 -- Debug Message
1222 OZF_supp_trd_prfls_PKG.Update_Row(
1223 p_supp_trade_profile_id => l_supp_trade_profile_rec.supp_trade_profile_id,
1224 p_object_version_number => l_supp_trade_profile_rec.object_version_number,
1225 p_last_update_date => SYSDATE,
1226 p_last_updated_by => FND_GLOBAL.USER_ID,
1227 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
1228 p_request_id => l_supp_trade_profile_rec.request_id,
1229 p_program_application_id => l_supp_trade_profile_rec.program_application_id,
1230 p_program_update_date => l_supp_trade_profile_rec.program_update_date,
1231 p_program_id => l_supp_trade_profile_rec.program_id,
1232 p_created_from => l_supp_trade_profile_rec.created_from,
1233 p_party_id => l_supp_trade_profile_rec.party_id,
1234 p_site_use_id => l_supp_trade_profile_rec.site_use_id,
1235 p_cust_account_id => l_supp_trade_profile_rec.cust_account_id,
1236 p_cust_acct_site_id => l_supp_trade_profile_rec.cust_acct_site_id,
1237 p_supplier_id => l_supp_trade_profile_rec.supplier_id,
1238 p_supplier_site_id => l_supp_trade_profile_rec.supplier_site_id,
1239 p_attribute_category => l_supp_trade_profile_rec.attribute_category,
1240 p_attribute1 => l_supp_trade_profile_rec.attribute1,
1241 p_attribute2 => l_supp_trade_profile_rec.attribute2,
1242 p_attribute3 => l_supp_trade_profile_rec.attribute3,
1243 p_attribute4 => l_supp_trade_profile_rec.attribute4,
1244 p_attribute5 => l_supp_trade_profile_rec.attribute5,
1245 p_attribute6 => l_supp_trade_profile_rec.attribute6,
1246 p_attribute7 => l_supp_trade_profile_rec.attribute7,
1247 p_attribute8 => l_supp_trade_profile_rec.attribute8,
1248 p_attribute9 => l_supp_trade_profile_rec.attribute9,
1249 p_attribute10 => l_supp_trade_profile_rec.attribute10,
1250 p_attribute11 => l_supp_trade_profile_rec.attribute11,
1251 p_attribute12 => l_supp_trade_profile_rec.attribute12,
1252 p_attribute13 => l_supp_trade_profile_rec.attribute13,
1253 p_attribute14 => l_supp_trade_profile_rec.attribute14,
1254 p_attribute15 => l_supp_trade_profile_rec.attribute15,
1255 p_attribute16 => l_supp_trade_profile_rec.attribute16,
1256 p_attribute17 => l_supp_trade_profile_rec.attribute17,
1257 p_attribute18 => l_supp_trade_profile_rec.attribute18,
1258 p_attribute19 => l_supp_trade_profile_rec.attribute19,
1259 p_attribute20 => l_supp_trade_profile_rec.attribute20,
1260 p_attribute21 => l_supp_trade_profile_rec.attribute21,
1261 p_attribute22 => l_supp_trade_profile_rec.attribute22,
1262 p_attribute23 => l_supp_trade_profile_rec.attribute23,
1263 p_attribute24 => l_supp_trade_profile_rec.attribute24,
1264 p_attribute25 => l_supp_trade_profile_rec.attribute25,
1265 p_attribute26 => l_supp_trade_profile_rec.attribute26,
1266 p_attribute27 => l_supp_trade_profile_rec.attribute27,
1267 p_attribute28 => l_supp_trade_profile_rec.attribute28,
1268 p_attribute29 => l_supp_trade_profile_rec.attribute29,
1269 p_attribute30 => l_supp_trade_profile_rec.attribute30,
1270 p_dpp_attribute_category => l_supp_trade_profile_rec.dpp_attribute_category,
1271 p_dpp_attribute1 => l_supp_trade_profile_rec.dpp_attribute1,
1272 p_dpp_attribute2 => l_supp_trade_profile_rec.dpp_attribute2,
1273 p_dpp_attribute3 => l_supp_trade_profile_rec.dpp_attribute3,
1274 p_dpp_attribute4 => l_supp_trade_profile_rec.dpp_attribute4,
1275 p_dpp_attribute5 => l_supp_trade_profile_rec.dpp_attribute5,
1276 p_dpp_attribute6 => l_supp_trade_profile_rec.dpp_attribute6,
1277 p_dpp_attribute7 => l_supp_trade_profile_rec.dpp_attribute7,
1278 p_dpp_attribute8 => l_supp_trade_profile_rec.dpp_attribute8,
1279 p_dpp_attribute9 => l_supp_trade_profile_rec.dpp_attribute9,
1280 p_dpp_attribute10 => l_supp_trade_profile_rec.dpp_attribute10,
1281 p_dpp_attribute11 => l_supp_trade_profile_rec.dpp_attribute11,
1282 p_dpp_attribute12 => l_supp_trade_profile_rec.dpp_attribute12,
1283 p_dpp_attribute13 => l_supp_trade_profile_rec.dpp_attribute13,
1284 p_dpp_attribute14 => l_supp_trade_profile_rec.dpp_attribute14,
1285 p_dpp_attribute15 => l_supp_trade_profile_rec.dpp_attribute15,
1286 p_dpp_attribute16 => l_supp_trade_profile_rec.dpp_attribute16,
1287 p_dpp_attribute17 => l_supp_trade_profile_rec.dpp_attribute17,
1288 p_dpp_attribute18 => l_supp_trade_profile_rec.dpp_attribute18,
1289 p_dpp_attribute19 => l_supp_trade_profile_rec.dpp_attribute19,
1290 p_dpp_attribute20 => l_supp_trade_profile_rec.dpp_attribute20,
1291 p_dpp_attribute21 => l_supp_trade_profile_rec.dpp_attribute21,
1292 p_dpp_attribute22 => l_supp_trade_profile_rec.dpp_attribute22,
1293 p_dpp_attribute23 => l_supp_trade_profile_rec.dpp_attribute23,
1294 p_dpp_attribute24 => l_supp_trade_profile_rec.dpp_attribute24,
1295 p_dpp_attribute25 => l_supp_trade_profile_rec.dpp_attribute25,
1296 p_dpp_attribute26 => l_supp_trade_profile_rec.dpp_attribute26,
1297 p_dpp_attribute27 => l_supp_trade_profile_rec.dpp_attribute27,
1298 p_dpp_attribute28 => l_supp_trade_profile_rec.dpp_attribute28,
1299 p_dpp_attribute29 => l_supp_trade_profile_rec.dpp_attribute29,
1300 p_dpp_attribute30 => l_supp_trade_profile_rec.dpp_attribute30,
1301 p_org_id => l_org_id ,
1302 p_pre_approval_flag => l_supp_trade_profile_rec.pre_approval_flag ,
1303 p_approval_communication => l_supp_trade_profile_rec.approval_communication ,
1304 p_gl_contra_liability_acct => l_supp_trade_profile_rec.gl_contra_liability_acct ,
1305 p_gl_cost_adjustment_acct => l_supp_trade_profile_rec.gl_cost_adjustment_acct ,
1306 p_default_days_covered => l_supp_trade_profile_rec.default_days_covered ,
1307 p_create_claim_price_increase => l_supp_trade_profile_rec.create_claim_price_increase ,
1308 p_authorization_period => l_supp_trade_profile_rec.authorization_period ,
1309 p_grace_days => l_supp_trade_profile_rec.grace_days ,
1310 p_allow_qty_increase => l_supp_trade_profile_rec.allow_qty_increase ,
1311 p_qty_increase_tolerance => l_supp_trade_profile_rec.qty_increase_tolerance ,
1312 p_request_communication => l_supp_trade_profile_rec.request_communication ,
1313 p_claim_communication => l_supp_trade_profile_rec.claim_communication ,
1314 p_claim_frequency => l_supp_trade_profile_rec.claim_frequency ,
1315 p_claim_frequency_unit => l_supp_trade_profile_rec.claim_frequency_unit ,
1316 p_claim_computation_basis => l_supp_trade_profile_rec.claim_computation_basis ,
1317 p_claim_currency_code => l_supp_trade_profile_rec.claim_currency_code,
1318 p_min_claim_amt => l_supp_trade_profile_rec.min_claim_amt,
1319 p_min_claim_amt_line_lvl => l_supp_trade_profile_rec.min_claim_amt_line_lvl,
1320 p_auto_debit => l_supp_trade_profile_rec.auto_debit,
1321 p_days_before_claiming_debit => l_supp_trade_profile_rec.days_before_claiming_debit
1322 );
1323 -- set the return object version number
1324 x_object_version_number := l_supp_trade_profile_rec.object_version_number;
1325 END IF; -- end of check for create or update of trade profile
1326 -- End of API body.
1327 -- Standard check for p_commit
1328 IF FND_API.to_Boolean( p_commit )
1329 THEN
1330 COMMIT WORK;
1331 END IF;
1332 -- Debug Message
1333 IF g_debug THEN
1334 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1335 END IF;
1336 -- Standard call to get message count and if count is 1, get message info.
1337 FND_MSG_PUB.Count_And_Get
1338 (p_count => x_msg_count,
1339 p_data => x_msg_data
1340 );
1341 EXCEPTION
1342 WHEN OZF_Utility_PVT.resource_locked THEN
1343 x_return_status := FND_API.g_ret_sts_error;
1344 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1345 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
1346 FND_MSG_PUB.add;
1347 END IF;
1348 WHEN FND_API.G_EXC_ERROR THEN
1349 ROLLBACK TO UPDATE_Supp_Trade_Profile_PVT;
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 -- Standard call to get message count and if count=1, get the message
1352 FND_MSG_PUB.Count_And_Get (
1353 p_encoded => FND_API.G_FALSE,
1354 p_count => x_msg_count,
1355 p_data => x_msg_data
1356 );
1357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1358 ROLLBACK TO UPDATE_Supp_Trade_Profile_PVT;
1359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1360 -- Standard call to get message count and if count=1, get the message
1361 FND_MSG_PUB.Count_And_Get (
1362 p_encoded => FND_API.G_FALSE,
1363 p_count => x_msg_count,
1364 p_data => x_msg_data
1365 );
1366 WHEN OTHERS THEN
1367 ROLLBACK TO UPDATE_Supp_Trade_Profile_PVT;
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1369 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1370 THEN
1371 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1372 END IF;
1373 -- Standard call to get message count and if count=1, get the message
1374 FND_MSG_PUB.Count_And_Get (
1375 p_encoded => FND_API.G_FALSE,
1376 p_count => x_msg_count,
1377 p_data => x_msg_data
1378 );
1379 End Update_Supp_Trade_Profile;
1380 -------------------------------------------------------------------------------
1381 PROCEDURE Delete_Supp_Trade_Profile(
1382 p_api_version_number IN NUMBER,
1383 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1384 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1385 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1386 x_return_status OUT NOCOPY VARCHAR2,
1387 x_msg_count OUT NOCOPY NUMBER,
1388 x_msg_data OUT NOCOPY VARCHAR2,
1389 p_supp_trade_profile_id IN NUMBER,
1390 p_object_version_number IN NUMBER
1391 )
1392
1393 IS
1394 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Supp_Trade_Profile';
1395 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1396 l_object_version_number NUMBER;
1397
1398 BEGIN
1399 -- Standard Start of API savepoint
1400 SAVEPOINT Delete_Supp_Trade_Profile_PVT;
1401 -- Standard call to check for call compatibility.
1402 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1403 p_api_version_number,
1404 l_api_name,
1405 G_PKG_NAME)
1406 THEN
1407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1408 END IF;
1409 -- Initialize message list if p_init_msg_list is set to TRUE.
1410 IF FND_API.to_Boolean( p_init_msg_list )
1411 THEN
1412 FND_MSG_PUB.initialize;
1413 END IF;
1414 -- Debug Message
1415 IF g_debug THEN
1416 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1417 END IF;
1418 -- Initialize API return status to SUCCESS
1419 x_return_status := FND_API.G_RET_STS_SUCCESS;
1420 -- Api body
1421 --
1422 -- Debug Message
1423 IF g_debug THEN
1424 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1425 END IF;
1426 -- Invoke table handler(OZF_supp_trd_prfls_PKG.Delete_Row)
1427 OZF_supp_trd_prfls_PKG.Delete_Row(
1428 p_SUPP_TRADE_PROFILE_ID => p_SUPP_TRADE_PROFILE_ID);
1429 -- End of API body
1430 -- Standard check for p_commit
1431 IF FND_API.to_Boolean( p_commit )
1432 THEN
1433 COMMIT WORK;
1434 END IF;
1435 -- Debug Message
1436 IF g_debug THEN
1437 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1438 END IF;
1439 -- Standard call to get message count and if count is 1, get message info.
1440 FND_MSG_PUB.Count_And_Get
1441 (p_count => x_msg_count,
1442 p_data => x_msg_data
1443 );
1444
1445 EXCEPTION
1446 WHEN OZF_Utility_PVT.resource_locked THEN
1447 x_return_status := FND_API.g_ret_sts_error;
1448 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1449 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES_LOCKED');
1450 FND_MSG_PUB.add;
1451 END IF;
1452 WHEN FND_API.G_EXC_ERROR THEN
1453 ROLLBACK TO Delete_Supp_Trade_Profile_PVT;
1454 x_return_status := FND_API.G_RET_STS_ERROR;
1455 -- Standard call to get message count and if count=1, get the message
1456 FND_MSG_PUB.Count_And_Get (
1457 p_encoded => FND_API.G_FALSE,
1458 p_count => x_msg_count,
1459 p_data => x_msg_data
1460 );
1461
1462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1463 ROLLBACK TO Delete_Supp_Trade_Profile_PVT;
1464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1465 -- Standard call to get message count and if count=1, get the message
1466 FND_MSG_PUB.Count_And_Get (
1467 p_encoded => FND_API.G_FALSE,
1468 p_count => x_msg_count,
1469 p_data => x_msg_data
1470 );
1471 WHEN OTHERS THEN
1472 ROLLBACK TO Delete_Supp_Trade_Profile_PVT;
1473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1474 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1475 THEN
1476 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1477 END IF;
1478 -- Standard call to get message count and if count=1, get the message
1479 FND_MSG_PUB.Count_And_Get (
1480 p_encoded => FND_API.G_FALSE,
1481 p_count => x_msg_count,
1482 p_data => x_msg_data
1483 );
1484 End Delete_Supp_Trade_Profile;
1485 -------------------------------------------------------------------------------
1486 PROCEDURE Lock_Supp_Trade_Profile(
1487 p_api_version_number IN NUMBER,
1488 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1489 x_return_status OUT NOCOPY VARCHAR2,
1490 x_msg_count OUT NOCOPY NUMBER,
1491 x_msg_data OUT NOCOPY VARCHAR2,
1492 p_supp_trade_profile_id IN NUMBER,
1493 p_object_version IN NUMBER
1494 )
1495 IS
1496 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Supp_Trade_Profile';
1497 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1498 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1499 l_SUPP_TRADE_PROFILE_ID NUMBER;
1500
1501 CURSOR c_Supp_Trade_Profile IS
1502 SELECT SUPP_TRADE_PROFILE_ID
1503 FROM ozf_supp_trd_prfls_all
1504 WHERE SUPP_TRADE_PROFILE_ID = p_SUPP_TRADE_PROFILE_ID
1505 AND object_version_number = p_object_version
1506 FOR UPDATE NOWAIT;
1507 BEGIN
1508 -- Debug Message
1509 IF g_debug THEN
1510 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1511 END IF;
1512
1513 -- Initialize message list if p_init_msg_list is set to TRUE.
1514 IF FND_API.to_Boolean( p_init_msg_list )
1515 THEN
1516 FND_MSG_PUB.initialize;
1517 END IF;
1518
1519 -- Standard call to check for call compatibility.
1520 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1521 p_api_version_number,
1522 l_api_name,
1523 G_PKG_NAME)
1524 THEN
1525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1526 END IF;
1527 -- Initialize API return status to SUCCESS
1528 x_return_status := FND_API.G_RET_STS_SUCCESS;
1529 ------------------------ lock -------------------------
1530 IF g_debug THEN
1531 OZF_Utility_PVT.debug_message(l_full_name||': start');
1532 END IF;
1533 OPEN c_Supp_Trade_Profile;
1534 FETCH c_Supp_Trade_Profile INTO l_SUPP_TRADE_PROFILE_ID;
1535 IF (c_Supp_Trade_Profile%NOTFOUND) THEN
1536 CLOSE c_Supp_Trade_Profile;
1537 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1538 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1539 FND_MSG_PUB.add;
1540 END IF;
1541 RAISE FND_API.g_exc_error;
1542 END IF;
1543 CLOSE c_Supp_Trade_Profile;
1544 -------------------- finish --------------------------
1545 FND_MSG_PUB.count_and_get(
1546 p_encoded => FND_API.g_false,
1547 p_count => x_msg_count,
1548 p_data => x_msg_data);
1549 IF g_debug THEN
1550 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1551 END IF;
1552 EXCEPTION
1553 WHEN OZF_Utility_PVT.resource_locked THEN
1554 x_return_status := FND_API.g_ret_sts_error;
1555 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1556 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES LOCKED');
1557 FND_MSG_PUB.add;
1558 END IF;
1559 WHEN FND_API.G_EXC_ERROR THEN
1560 ROLLBACK TO LOCK_Supp_Trade_Profile_PVT;
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 -- Standard call to get message count and if count=1, get the message
1563 FND_MSG_PUB.Count_And_Get (
1564 p_encoded => FND_API.G_FALSE,
1565 p_count => x_msg_count,
1566 p_data => x_msg_data
1567 );
1568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1569 ROLLBACK TO LOCK_Supp_Trade_Profile_PVT;
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1571 -- Standard call to get message count and if count=1, get the message
1572 FND_MSG_PUB.Count_And_Get (
1573 p_encoded => FND_API.G_FALSE,
1574 p_count => x_msg_count,
1575 p_data => x_msg_data
1576 );
1577 WHEN OTHERS THEN
1578 ROLLBACK TO LOCK_Supp_Trade_Profile_PVT;
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1581 THEN
1582 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1583 END IF;
1584 -- Standard call to get message count and if count=1, get the message
1585 FND_MSG_PUB.Count_And_Get (
1586 p_encoded => FND_API.G_FALSE,
1587 p_count => x_msg_count,
1588 p_data => x_msg_data
1589 );
1590 End Lock_Supp_Trade_Profile;
1591 -------------------------------------------------------------------------------
1592 PROCEDURE check_supp_trd_prfl_uk_items(
1593 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1594 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1595 x_return_status OUT NOCOPY VARCHAR2)
1596 IS
1597 l_valid_flag VARCHAR2(1);
1598 l_supp_id NUMBER;
1599 l_supp_site_id NUMBER;
1600 l_dummy NUMBER;
1601
1602
1603 -- one supplier trade profile per supplier and supplier site and org id combination
1604
1605 CURSOR c_dupl_profile_exists(l_id in NUMBER,l_supp_site_id in NUMBER) IS
1606 SELECT supp_trade_profile_id
1607 FROM ozf_supp_trd_prfls
1608 WHERE SUPPLIER_SITE_ID = l_supp_site_id
1609 AND SUPPLIER_ID = l_id;
1610
1611
1612 BEGIN
1613 x_return_status := FND_API.g_ret_sts_success;
1614 l_valid_flag := FND_API.g_true;
1615 IF (p_supp_trade_profile_rec.supplier_id <> FND_API.g_miss_num AND
1616 p_supp_trade_profile_rec.supplier_id IS NOT NULL AND
1617 p_supp_trade_profile_rec.supplier_site_id <> FND_API.g_miss_num AND
1618 p_supp_trade_profile_rec.supplier_site_id IS NOT NULL )
1619 THEN
1620 l_supp_id := p_supp_trade_profile_rec.supplier_id;
1621 l_supp_site_id := p_supp_trade_profile_rec.supplier_site_id;
1622 OPEN c_dupl_profile_exists(l_supp_id,l_supp_site_id);
1623 FETCH c_dupl_profile_exists into l_dummy;
1624 CLOSE c_dupl_profile_exists;
1625 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1626
1627 -- if there is another record then throw duplicate exception
1628 IF l_dummy IS NOT NULL
1629 THEN
1630 l_valid_flag := FND_API.g_false;
1631 END IF;
1632 ELSE
1633
1634 -- if there is another record then throw duplicate exception
1635 IF l_dummy <> p_supp_trade_profile_rec.supp_trade_profile_id
1636 THEN
1637 l_valid_flag := FND_API.g_false;
1638 END IF;
1639 END IF; -- end of p_validation_mode = create
1640 END IF;
1641 --Seed the message OZF_SUPP_TRADE_DUPLICATE
1642 IF l_valid_flag = FND_API.g_false THEN
1643 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1644 FND_MESSAGE.set_name('OZF', 'OZF_SUPP_TRADE_PROFILE_DUPLIC');
1645 FND_MSG_PUB.add;
1646 END IF;
1647 x_return_status := FND_API.G_RET_STS_ERROR;
1648 END IF;
1649 END check_supp_trd_prfl_uk_items;
1650 -------------------------------------------------------------------------------
1651 PROCEDURE check_supp_trd_prfl_req_items(
1652 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1653 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1654 x_return_status OUT NOCOPY VARCHAR2
1655 )
1656 IS
1657 l_check1 NUMBER;
1658 l_check2 NUMBER;
1659 l_party_id NUMBER;
1660 l_supp_trade_profile_rec supp_trade_profile_rec_type;
1661
1662 CURSOR c_party_id (c_id IN NUMBER) IS
1663 SELECT party_id
1664 from HZ_CUST_ACCOUNTS
1665 WHERE cust_account_id = c_id;
1666
1667 BEGIN
1668 x_return_status := FND_API.g_ret_sts_success;
1669
1670 IF p_supp_trade_profile_rec.supplier_id = FND_API.g_miss_num OR
1671 p_supp_trade_profile_rec.supplier_id IS NULL
1672 THEN
1673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1674 FND_MESSAGE.set_name('OZF', 'OZF_SUPPLIER_MISSING');
1675 FND_MSG_PUB.add;
1676 END IF;
1677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1678 END IF;
1679 IF p_supp_trade_profile_rec.supplier_site_id = FND_API.g_miss_num OR
1680 p_supp_trade_profile_rec.supplier_site_id IS NULL
1681 THEN
1682 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1683 FND_MESSAGE.set_name('OZF', 'OZF_SUPPLIER_SITE_MISSING');
1684 FND_MSG_PUB.add;
1685 END IF;
1686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1687 END IF;
1688
1689 IF p_supp_trade_profile_rec.party_id = FND_API.g_miss_num OR
1690 p_supp_trade_profile_rec.party_id IS NULL
1691 THEN
1692 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1693 FND_MESSAGE.set_name('OZF', 'OZF_CUSTOMER_MISSING');
1694 FND_MSG_PUB.add;
1695 END IF;
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END IF;
1698 IF p_supp_trade_profile_rec.cust_account_id = FND_API.g_miss_num OR
1699 p_supp_trade_profile_rec.cust_account_id IS NULL
1700 THEN
1701 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1702 FND_MESSAGE.set_name('OZF', 'OZF_CUSTOMER_ACCOUNT_MISSING');
1703 FND_MSG_PUB.add;
1704 END IF;
1705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1706 END IF;
1707
1708
1709 END check_supp_trd_prfl_req_items;
1710 -------------------------------------------------------------------------------
1711 PROCEDURE check_supp_trd_prfl_FK_items(
1712 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1713 x_return_status OUT NOCOPY VARCHAR2)
1714 IS
1715 BEGIN
1716 x_return_status := FND_API.g_ret_sts_success;
1717 END check_supp_trd_prfl_FK_items;
1718
1719 PROCEDURE check_supp_trd_prfl_Lk_items(
1720 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1721 x_return_status OUT NOCOPY VARCHAR2
1722 )
1723 IS
1724 BEGIN
1725 x_return_status := FND_API.g_ret_sts_success;
1726 END check_supp_trd_prfl_Lk_items;
1727
1728
1729 -------------------------------------------------------------------------------
1730 PROCEDURE Check_supp_trd_prfl_items (
1731 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1732 p_validation_mode IN VARCHAR2,
1733 x_return_status OUT NOCOPY VARCHAR2
1734 )
1735 IS
1736 BEGIN
1737 -- Check Items Uniqueness API calls
1738 check_supp_trd_prfl_uk_items(
1739 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1740 p_validation_mode => p_validation_mode,
1741 x_return_status => x_return_status);
1742 IF x_return_status <> FND_API.g_ret_sts_success THEN
1743 RETURN;
1744 END IF;
1745 -- Check Items Required/NOT NULL API calls
1746 check_supp_trd_prfl_req_items(
1747 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1748 p_validation_mode => p_validation_mode,
1749 x_return_status => x_return_status);
1750 IF x_return_status <> FND_API.g_ret_sts_success THEN
1751 RETURN;
1752 END IF;
1753 -- Check Items Foreign Keys API calls
1754 check_supp_trd_prfl_FK_items(
1755 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1756 x_return_status => x_return_status);
1757 IF x_return_status <> FND_API.g_ret_sts_success THEN
1758 RETURN;
1759 END IF;
1760 -- Check Items Lookups
1761 check_supp_trd_prfl_Lk_items(
1762 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1763 x_return_status => x_return_status);
1764 IF x_return_status <> FND_API.g_ret_sts_success THEN
1765 RETURN;
1766 END IF;
1767
1768
1769
1770 --Check the tolerances.--not required -deepika
1771 /* Check_Batch_Tolerances(
1772 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1773 x_return_status => x_return_status
1774 );
1775 IF x_return_status <> FND_API.g_ret_sts_success THEN
1776 RETURN;
1777 END IF; */
1778 END Check_supp_trd_prfl_Items;
1779 -------------------------------------------------------------------------------
1780 PROCEDURE Validate_supp_trade_profile(
1781 p_api_version_number IN NUMBER,
1782 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1783 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1784 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
1785 x_return_status OUT NOCOPY VARCHAR2,
1786 x_msg_count OUT NOCOPY NUMBER,
1787 x_msg_data OUT NOCOPY VARCHAR2
1788 )
1789 IS
1790 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Trade_Profile';
1791 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1792 l_object_version_number NUMBER;
1793 l_supp_trade_profile_rec OZF_SUPP_TRADE_PROFILE_PVT.supp_trade_profile_rec_type;
1794
1795 BEGIN
1796 -- Standard Start of API savepoint
1797 SAVEPOINT VALIDATE_Supp_Trade_Prfl_PVT;
1798 -- Standard call to check for call compatibility.
1799 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1800 p_api_version_number,
1801 l_api_name,
1802 G_PKG_NAME)
1803 THEN
1804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1805 END IF;
1806 -- Initialize message list if p_init_msg_list is set to TRUE.
1807 IF FND_API.to_Boolean( p_init_msg_list )
1808 THEN
1809 FND_MSG_PUB.initialize;
1810 END IF;
1811 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1812 Check_supp_trd_prfl_Items(
1813 p_supp_trade_profile_rec => p_supp_trade_profile_rec,
1814 p_validation_mode => JTF_PLSQL_API.g_create,
1815 x_return_status => x_return_status
1816 );
1817 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1818 RAISE FND_API.G_EXC_ERROR;
1819 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1821 END IF;
1822 END IF;
1823
1824 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1825 Validate_supp_trd_prfl_rec(
1826 p_api_version_number => 1.0,
1827 p_init_msg_list => FND_API.G_FALSE,
1828 x_return_status => x_return_status,
1829 x_msg_count => x_msg_count,
1830 x_msg_data => x_msg_data,
1831 p_supp_trade_profile_rec => p_supp_trade_profile_rec);
1832 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1833 RAISE FND_API.G_EXC_ERROR;
1834 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1836 END IF;
1837 END IF;
1838
1839 -- Initialize API return status to SUCCESS
1840 x_return_status := FND_API.G_RET_STS_SUCCESS;
1841 -- Standard call to get message count and if count is 1, get message info.
1842 FND_MSG_PUB.Count_And_Get
1843 (p_count => x_msg_count,
1844 p_data => x_msg_data
1845 );
1846 EXCEPTION
1847 WHEN OZF_Utility_PVT.resource_locked THEN
1848 x_return_status := FND_API.g_ret_sts_error;
1849 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1850 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED ');
1851 FND_MSG_PUB.add;
1852 END IF;
1853 WHEN FND_API.G_EXC_ERROR THEN
1854 ROLLBACK TO VALIDATE_Supp_Trade_Prfl_PVT;
1855 x_return_status := FND_API.G_RET_STS_ERROR;
1856 -- Standard call to get message count and if count=1, get the message
1857 FND_MSG_PUB.Count_And_Get (
1858 p_encoded => FND_API.G_FALSE,
1859 p_count => x_msg_count,
1860 p_data => x_msg_data
1861 );
1862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1863 ROLLBACK TO VALIDATE_Supp_Trade_Prfl_PVT;
1864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1865 -- Standard call to get message count and if count=1, get the message
1866 FND_MSG_PUB.Count_And_Get (
1867 p_encoded => FND_API.G_FALSE,
1868 p_count => x_msg_count,
1869 p_data => x_msg_data
1870 );
1871 WHEN OTHERS THEN
1872 ROLLBACK TO VALIDATE_Supp_Trade_Prfl_PVT;
1873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1875 THEN
1876 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1877 END IF;
1878 -- Standard call to get message count and if count=1, get the message
1879 FND_MSG_PUB.Count_And_Get (
1880 p_encoded => FND_API.G_FALSE,
1881 p_count => x_msg_count,
1882 p_data => x_msg_data
1883 );
1884 End Validate_Supp_Trade_Profile;
1885 -------------------------------------------------------------------------------
1886 PROCEDURE Validate_supp_trd_prfl_rec(
1887 p_api_version_number IN NUMBER,
1888 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1889 x_return_status OUT NOCOPY VARCHAR2,
1890 x_msg_count OUT NOCOPY NUMBER,
1891 x_msg_data OUT NOCOPY VARCHAR2,
1892 p_supp_trade_profile_rec IN supp_trade_profile_rec_type
1893 )
1894 IS
1895 BEGIN
1896 -- Initialize message list if p_init_msg_list is set to TRUE.
1897 IF FND_API.to_Boolean( p_init_msg_list )
1898 THEN
1899 FND_MSG_PUB.initialize;
1900 END IF;
1901
1902 -- Initialize API return status to SUCCESS
1903 x_return_status := FND_API.G_RET_STS_SUCCESS;
1904
1905 -- Hint: Default days covered shd lie in 0-9999
1906 IF p_supp_trade_profile_rec.default_days_covered is not null and
1907 ( p_supp_trade_profile_rec.default_days_covered > 9999 or
1908 p_supp_trade_profile_rec.default_days_covered < 0)
1909 THEN
1910 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1911 FND_MESSAGE.set_name('OZF', 'OZF_DEFAULT_DAYS_INVALID_VAL');
1912 FND_MSG_PUB.add;
1913 END IF;
1914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1915 END IF ;
1916 -- Hint: qty_increase_tolerance shd lie in 1-100
1917 IF p_supp_trade_profile_rec.qty_increase_tolerance is not null and
1918 (p_supp_trade_profile_rec.qty_increase_tolerance > 100 or
1919 p_supp_trade_profile_rec.qty_increase_tolerance < 0)
1920 THEN
1921 x_return_status := FND_API.G_RET_STS_ERROR;
1922 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1923 FND_MESSAGE.set_name('OZF', 'OZF_QTY_INC_TOLERANCE_INVALID');
1924 FND_MSG_PUB.add;
1925 END IF;
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 END IF ;
1928 IF p_supp_trade_profile_rec.grace_days is not null and
1929 ( p_supp_trade_profile_rec.grace_days > 9999 or
1930 p_supp_trade_profile_rec.grace_days < 0)
1931 THEN
1932 x_return_status := FND_API.G_RET_STS_ERROR;
1933 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1934 FND_MESSAGE.set_name('OZF', 'OZF_GRACE_DAYS_INVALID_VAL');
1935 FND_MSG_PUB.add;
1936 END IF;
1937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1938 END IF ;
1939 IF p_supp_trade_profile_rec.authorization_period is not null and
1940 (p_supp_trade_profile_rec.authorization_period > 9999 or
1941 p_supp_trade_profile_rec.authorization_period < 0)
1942 THEN
1943 x_return_status := FND_API.G_RET_STS_ERROR;
1944 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1945 FND_MESSAGE.set_name('OZF', 'OZF_AUTH_PERIOD_INVALID_VAL');
1946 FND_MSG_PUB.add;
1947 END IF;
1948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1949 END IF ;
1950 --gdeepika 2/21/2008 claim frequency cannot be negetive--
1951 IF p_supp_trade_profile_rec.claim_frequency is not null and
1952 p_supp_trade_profile_rec.claim_frequency < 0
1953 THEN
1954 x_return_status := FND_API.G_RET_STS_ERROR;
1955 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1956 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_FREQ_NEG');
1957 FND_MSG_PUB.add;
1958 END IF;
1959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1960 END IF ;
1961 --2/22/2008 gdeepika- claim amount thresholds cannot be negative -6839040
1962 IF(p_supp_trade_profile_rec.min_claim_amt IS NOT null)
1963 THEN
1964 IF (p_supp_trade_profile_rec.min_claim_amt < 0)
1965 THEN
1966 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1967 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AMT_INVALID');
1968 FND_MSG_PUB.add;
1969 END IF;
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1972 END IF ;
1973 END IF;
1974
1975 IF( p_supp_trade_profile_rec.min_claim_amt_line_lvl IS NOT null )
1976 THEN
1977 IF (p_supp_trade_profile_rec.min_claim_amt_line_lvl < 0)
1978 THEN
1979 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1980 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_NEG');
1981 FND_MSG_PUB.add;
1982 END IF;
1983 x_return_status := FND_API.G_RET_STS_ERROR;
1984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1985 END IF ;
1986 END IF;
1987
1988 --2/22/2008 gdeepika- end of fix -6839040
1989 --check whether the claim line amount is greater than the claim line level amount
1990 IF(p_supp_trade_profile_rec.min_claim_amt IS NOT null
1991 AND p_supp_trade_profile_rec.min_claim_amt_line_lvl IS NOT null )
1992 THEN
1993
1994 IF (p_supp_trade_profile_rec.min_claim_amt < p_supp_trade_profile_rec.min_claim_amt_line_lvl)
1995 THEN
1996 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1997 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AMT_ERROR');
1998 FND_MSG_PUB.add;
1999 END IF;
2000 x_return_status := FND_API.G_RET_STS_ERROR;
2001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2002 END IF ;
2003 END IF;
2004
2005 IF p_supp_trade_profile_rec.days_before_claiming_debit is not null and
2006 p_supp_trade_profile_rec.days_before_claiming_debit > 9999 or
2007 p_supp_trade_profile_rec.days_before_claiming_debit < 0
2008 THEN
2009 x_return_status := FND_API.G_RET_STS_ERROR;
2010 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2011 FND_MESSAGE.set_name('OZF', 'OZF_DAYS_BEF_CLAIMING_DEBIT');
2012 FND_MSG_PUB.add;
2013 END IF;
2014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2015 END IF ;
2016 -- Debug Message
2017 IF g_debug THEN
2018 OZF_UTILITY_PVT.debug_message('Private API: Validate the trade profile record');
2019 END IF;
2020 -- Standard call to get message count and if count is 1, get message info.
2021 FND_MSG_PUB.Count_And_Get
2022 (p_count => x_msg_count,
2023 p_data => x_msg_data
2024 );
2025 END Validate_supp_trd_prfl_rec;
2026 --------------------------------------------------------------------------------------
2027 END OZF_SUPP_TRADE_PROFILE_PVT;
2028
2029
[Home] [Help]