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