[Home] [Help]
PACKAGE BODY: APPS.OZF_SUPP_TRADE_PROFILE_PUB
Source
1 PACKAGE BODY OZF_SUPP_TRADE_PROFILE_PUB as
2 /* $Header: ozfpstpb.pls 120.6 2012/01/05 19:15:44 nirprasa noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_SUPP_TRADE_PROFILE_PVT
7 -- Purpose
8 -- End of Comments
9 -- ===============================================================
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_SUPP_TRADE_PROFILE_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfpstpb.pls';
12
13 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
14
15
16
17 ---------------------------------------------------------------------
18 -- PROCEDURE
19 -- Validate_GL_KeyFlex_val
20 --
21 -- PURPOSE
22 -- This procedure validates the GL Key flex code
23 --Parameters
24 -- p_supp_trade_profile_rec -Header Recordset
25 -- x_return_status - Result
26 ---------------------------------------------------------------------
27
28 PROCEDURE Validate_GL_KeyFlex_val(
29 p_combination_id IN VARCHAR2 ,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2
33 )
34 IS
35
36 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_GL_KeyFlex_val';
37 l_combination_id NUMBER := -1 ;
38
39 CURSOR c_get_comb IS
40 SELECT count(1) FROM GL_CODE_COMBINATIONS_KFV
41 WHERE code_combination_id = p_combination_id ;
42
43 BEGIN
44 IF g_debug THEN
45 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts' );
46 END IF;
47 x_return_status := FND_API.g_ret_sts_success ;
48
49 OPEN c_get_comb;
50 FETCH c_get_comb INTO l_combination_id ;
51
52 IF(c_get_comb%NOTFOUND) THEN
53 IF g_debug THEN
54 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' The Key flex comination Id is invalid' );
55 END IF;
56
57 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
58 FND_MESSAGE.set_name('OZF', 'The Key flex comination Id is invalid');
59 FND_MSG_PUB.add;
60 END IF;
61
62 RAISE FND_API.G_EXC_ERROR ;
63
64 END IF ;
65
66 CLOSE c_get_comb ;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
71 IF g_debug THEN
72 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
73 END IF;
74
75 END Validate_GL_KeyFlex_val ;
76
77
78 ---------------------------------------------------------------------
79 -- PROCEDURE
80 -- Validate_Supp_Trade_Profile
81 --
82 -- PURPOSE
83 -- This procedure validates Supplier trade profile record
84 --Parameters
85 -- p_supp_trade_profile_rec -Header Recordset
86 -- x_return_status - Result
87 ---------------------------------------------------------------------
88
89 PROCEDURE Validate_Supp_Trade_Profile(
90 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_supp_trade_profile_rec IN OUT NOCOPY supp_trade_profile_rec_type
95 )
96
97 IS
98
99 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Supp_Trade_Profile';
100 l_supp_site_count NUMBER := 0 ;
101 l_cut_acct_comb_count NUMBER := 0 ;
102 l_comn_chan_count NUMBER := 0 ;
103 l_sett_inc_count NUMBER := 0 ;
104 l_sett_dec_count NUMBER := 0 ;
105 l_cust_sett_count NUMBER := 0 ;
106 l_freq_unit_count NUMBER := 0 ;
107 l_comp_baisis_count NUMBER := 0 ;
108 l_org_count NUMBER := 0 ;
109 l_cust_count NUMBER := 0 ;
110 l_exec_error VARCHAR2(1) := 'N' ;
111
112 BEGIN
113
114 IF g_debug THEN
115 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts' );
116 END IF;
117 x_return_status := FND_API.g_ret_sts_success;
118
119
120 -- Initialize message list if p_init_msg_list is set to TRUE.
121 IF FND_API.to_Boolean( p_init_msg_list )
122 THEN
123 FND_MSG_PUB.initialize;
124 END IF;
125
126
127
128 -- The Org_id should not be null
129 IF ( (p_supp_trade_profile_rec.supp_trade_profile_id IS NULL OR
130 p_supp_trade_profile_rec.supp_trade_profile_id = FND_API.g_miss_num) AND
131 (p_supp_trade_profile_rec.org_id IS NULL OR
132 p_supp_trade_profile_rec.org_id = FND_API.g_miss_num)) THEN
133
134
135 IF g_debug THEN
136 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' The Org Id is null' );
137 END IF;
138 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
139 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_ORG_ID');
140 FND_MSG_PUB.add;
141 END IF;
142 RAISE FND_API.G_EXC_ERROR ;
143
144 ELSE
145
146 SELECT COUNT(1) INTO l_org_count
147 FROM hr_operating_units
148 WHERE organization_id=p_supp_trade_profile_rec.org_id ;
149
150 IF ( l_org_count = 0 ) THEN
151 IF g_debug THEN
152 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' The Org Id is not valid' );
153 END IF;
154
155 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
156 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_ORG_ID');
157 FND_MSG_PUB.add;
158 END IF;
159 RAISE FND_API.G_EXC_ERROR ;
160 END IF;
161
162 END IF ;
163
164
165
166 -- Validate approval_communication,request_communication,claim_communication
167
168 IF (p_supp_trade_profile_rec.approval_communication <>FND_API.g_miss_char AND
169 p_supp_trade_profile_rec.approval_communication IS NOT NULL ) THEN
170
171 SELECT COUNT(*) INTO l_comn_chan_count
172 FROM ozf_lookups
173 WHERE lookup_type='OZF_CLAIM_COMMUNICATION'
174 AND lookup_code = p_supp_trade_profile_rec.approval_communication ;
175
176 IF l_comn_chan_count=0 THEN
177
178 IF g_debug THEN
179 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid Claim Communication Channel' );
180 END IF;
181
182 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
183 FND_MESSAGE.set_name('OZF', 'Invalid Approval Communication channel'); -- Select Valid Approval Communication Channel
184 FND_MSG_PUB.add;
185 END IF;
186 RAISE FND_API.G_EXC_ERROR ;
187 END IF ;
188
189 END IF ;
190
191
192 IF ( p_supp_trade_profile_rec.pre_approval_flag = 'Y'
193 AND
194 (p_supp_trade_profile_rec.approval_communication IS NULL OR p_supp_trade_profile_rec.approval_communication = FND_API.g_miss_char) ) THEN
195
196 IF g_debug THEN
197 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid approval Communication Channel' );
198 END IF;
199
200 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
201 FND_MESSAGE.set_name('OZF', 'OZF_APPR_COMM_VALIDATION');
202 FND_MSG_PUB.add;
203 RAISE FND_API.G_EXC_ERROR ;
204 END IF;
205
206 END IF ;
207
208
209 IF (p_supp_trade_profile_rec.request_communication <>FND_API.g_miss_char
210 AND p_supp_trade_profile_rec.request_communication IS NOT NULL ) THEN
211
212 SELECT COUNT(*) INTO l_comn_chan_count
213 FROM ozf_lookups
214 WHERE lookup_type='OZF_REQ_COMMUNICATION'
215 AND lookup_code = p_supp_trade_profile_rec.request_communication ;
216
217 IF l_comn_chan_count=0 THEN
218
219 IF g_debug THEN
220 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid request Communication Channel' );
221 END IF;
222
223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
224 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_REQ_COMM_CHAN'); -- Select Valed Request Communication Channel
225 FND_MSG_PUB.add;
226 END IF;
227 RAISE FND_API.G_EXC_ERROR ;
228 END IF ;
229
230 END IF ;
231
232
233 IF (p_supp_trade_profile_rec.claim_communication <>FND_API.g_miss_char
234 AND p_supp_trade_profile_rec.claim_communication IS NOT NULL) THEN
235
236 SELECT COUNT(*) INTO l_comn_chan_count
237 FROM ozf_lookups
238 WHERE lookup_type='OZF_CLAIM_COMMUNICATION'
239 AND lookup_code = p_supp_trade_profile_rec.claim_communication ;
240
241 IF l_comn_chan_count=0 THEN
242
243 IF g_debug THEN
244 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid claim Communication Channel' );
245 END IF;
246 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
247 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CLM_COMM_CHAN'); -- Select Valid Claim Communication channel
248 FND_MSG_PUB.add;
249 END IF;
250 RAISE FND_API.G_EXC_ERROR ;
251 END IF ;
252
253 END IF ;
254
255 IF ( p_supp_trade_profile_rec.auto_debit = 'Y'
256 AND
257 (p_supp_trade_profile_rec.claim_communication <>FND_API.g_miss_char AND p_supp_trade_profile_rec.claim_communication IS NOT NULL) ) THEN
258
259 IF g_debug THEN
260 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid claim Communication + autodebit combination' );
261 END IF;
262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
263 FND_MESSAGE.set_name('OZF', 'OZF_AUTO_DEBIT_VALIDATION');
264 FND_MSG_PUB.add;
265 END IF;
266 RAISE FND_API.G_EXC_ERROR ;
267
268 END IF ;
269
270
271 --Validate settlement_method_supplier_inc,settlement_method_supplier_dec and settlement_method_customer
272
273 IF ( p_supp_trade_profile_rec.settlement_method_supplier_inc <>FND_API.g_miss_char
274 AND p_supp_trade_profile_rec.settlement_method_supplier_inc IS NOT NULL ) THEN
275
276 SELECT COUNT(*) INTO l_sett_inc_count
277 FROM ozf_claim_sttlmnt_methods_all cs,
278 ozf_lookups ol
279 WHERE cs.settlement_method = ol.lookup_code
280 AND ol.lookup_type = 'OZF_PAYMENT_METHOD'
281 AND cs.source_object_class='PPINCVENDOR'
282 AND cs.org_id = p_supp_trade_profile_rec.org_id
283 AND ol.lookup_code = p_supp_trade_profile_rec.settlement_method_supplier_inc ;
284
285
286 IF ( l_sett_inc_count=0 ) THEN
287
288 IF g_debug THEN
289 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Settelemnt method INC is invalid' );
290 END IF;
291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
292 FND_MESSAGE.set_name('OZF', 'OZF_DEF_SET_METH_INC'); -- Select valid value for Default Settlement for Supplier Price Increase Claims
293 FND_MSG_PUB.add;
294 END IF;
295 RAISE FND_API.G_EXC_ERROR ;
296 END IF ;
297
298 END IF ;
299
300
301 IF ( p_supp_trade_profile_rec.settlement_method_supplier_dec <>FND_API.g_miss_char
302 AND p_supp_trade_profile_rec.settlement_method_supplier_dec IS NOT NULL ) THEN
303
304 SELECT COUNT(*) INTO l_sett_dec_count
305 FROM ozf_claim_sttlmnt_methods_all cs,
306 ozf_lookups ol
307 WHERE cs.settlement_method = ol.lookup_code
308 AND ol.lookup_type = 'OZF_PAYMENT_METHOD'
309 AND cs.source_object_class='PPVENDOR'
310 AND cs.org_id = p_supp_trade_profile_rec.org_id
311 AND ol.lookup_code = p_supp_trade_profile_rec.settlement_method_supplier_dec ;
312
313
314 IF ( l_sett_dec_count = 0 ) THEN
315
316 IF g_debug THEN
317 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Settelemnt method DEC is invalid' );
318 END IF;
319 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
320 FND_MESSAGE.set_name('OZF', 'OZF_DEF_SET_METH_DEC'); --Select valid value for Default Settlement for Supplier Price Decrease Claims
321 FND_MSG_PUB.add;
322 END IF;
323 RAISE FND_API.G_EXC_ERROR ;
324 END IF ;
325
326 END IF ;
327
328 IF ( p_supp_trade_profile_rec.settlement_method_customer <>FND_API.g_miss_char
329 AND p_supp_trade_profile_rec.settlement_method_customer IS NOT NULL ) THEN
330
331 SELECT COUNT(*) INTO l_cust_sett_count
332 FROM ozf_claim_sttlmnt_methods_all cs,
333 ozf_lookups ol
334 WHERE cs.settlement_method = ol.lookup_code
335 AND ol.lookup_type = 'OZF_PAYMENT_METHOD'
336 AND cs.source_object_class='PPCUSTOMER'
337 AND cs.org_id = p_supp_trade_profile_rec.org_id
338 AND ol.lookup_code = p_supp_trade_profile_rec.settlement_method_customer ;
339
340
341 IF ( l_cust_sett_count = 0 ) THEN
342
343 IF g_debug THEN
344 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Settelemnt method Customer is invalid' );
345 END IF;
346 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
347
348 FND_MESSAGE.set_name('OZF', 'OZF_DEF_SET_METH_CUST'); -- Select Valid value for Default Settlement for Customer Claims
349 FND_MSG_PUB.add;
350 END IF;
351 RAISE FND_API.G_EXC_ERROR ;
352 END IF ;
353
354 END IF ;
355
356
357 -- Validate default_days_covered(4), authorization_period(4), grace_days(4), qty_increase_tolerance(10)
358
359 IF ( p_supp_trade_profile_rec.default_days_covered <>FND_API.g_miss_num
360 AND
361 p_supp_trade_profile_rec.default_days_covered IS NOT NULL
362 AND
363 p_supp_trade_profile_rec.default_days_covered NOT BETWEEN 0 AND 9999
364 ) THEN
365
366 IF g_debug THEN
367 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid default days covered' );
368 END IF;
369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
370
371 FND_MESSAGE.set_name('OZF', 'OZF_DEFAULT_DAYS_INVALID_VAL');
372 FND_MSG_PUB.add;
373 END IF;
374 RAISE FND_API.G_EXC_ERROR ;
375
376 END IF ;
377
378
379 IF ( p_supp_trade_profile_rec.authorization_period <>FND_API.g_miss_num
380 AND
381 p_supp_trade_profile_rec.authorization_period IS NOT NULL
382 AND
383 p_supp_trade_profile_rec.authorization_period NOT BETWEEN 0 AND 9999
384 ) THEN
385
386 IF g_debug THEN
387 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid authorization period' );
388 END IF;
389 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
390 FND_MESSAGE.set_name('OZF', 'OZF_AUTH_PERIOD_INVALID_VAL');
391 FND_MSG_PUB.add;
392 END IF;
393 RAISE FND_API.G_EXC_ERROR ;
394
395 END IF ;
396
397
398 IF ( p_supp_trade_profile_rec.grace_days <>FND_API.g_miss_num
399 AND
400 p_supp_trade_profile_rec.grace_days IS NOT NULL
401 AND
402 p_supp_trade_profile_rec.grace_days NOT BETWEEN 0 AND 9999
403 ) THEN
404
405 IF g_debug THEN
406 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid grace days' );
407 END IF;
408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
409 FND_MESSAGE.set_name('OZF', 'OZF_GRACE_DAYS_INVALID_VAL');
410 FND_MSG_PUB.add;
411 END IF;
412 RAISE FND_API.G_EXC_ERROR ;
413
414 END IF ;
415
416 IF ( p_supp_trade_profile_rec.qty_increase_tolerance <>FND_API.g_miss_num
417 AND
418 p_supp_trade_profile_rec.qty_increase_tolerance IS NOT NULL
419 AND
420 p_supp_trade_profile_rec.qty_increase_tolerance NOT BETWEEN 0 AND 99
421 ) THEN
422
423 IF g_debug THEN
424 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid value for increase tolerance' );
425 END IF;
426 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
427
428 FND_MESSAGE.set_name('OZF', 'OZF_QTY_INC_TOLERANCE_INVALID');
429 FND_MSG_PUB.add;
430 END IF;
431 RAISE FND_API.G_EXC_ERROR ;
432
433 END IF ;
434
435
436
437 -- The claim frequency unit and value should be provided together
438
439 IF ((NVL(p_supp_trade_profile_rec.claim_frequency,FND_API.g_miss_num) = FND_API.g_miss_num AND
440 NVL(p_supp_trade_profile_rec.claim_frequency_unit,FND_API.g_miss_char)<> FND_API.g_miss_char)
441 OR
442 (NVL(p_supp_trade_profile_rec.claim_frequency,FND_API.g_miss_num) <> FND_API.g_miss_num AND
443 NVL(p_supp_trade_profile_rec.claim_frequency_unit,FND_API.g_miss_char) = FND_API.g_miss_char)) THEN
444
445 IF g_debug THEN
446 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' The claim frequency and unit should be provided together' );
447 END IF;
448 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
449 FND_MESSAGE.set_name('OZF', 'The claim frequency and unit should be provided together');
450 FND_MSG_PUB.add;
451 END IF;
452 RAISE FND_API.G_EXC_ERROR ;
453 END IF ;
454
455
456
457 -- Validate the values for claim frequency and unit
458 IF ( p_supp_trade_profile_rec.claim_frequency <>FND_API.g_miss_num
459 AND
460 p_supp_trade_profile_rec.claim_frequency IS NOT NULL
461 AND
462 p_supp_trade_profile_rec.claim_frequency_unit <>FND_API.g_miss_char ) THEN
463
464
465 IF (p_supp_trade_profile_rec.claim_frequency < 0) THEN
466
467 IF g_debug THEN
468 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid value for claim frequency' );
469 END IF;
470
471 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
472 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_FREQ_NEG');
473 FND_MSG_PUB.add;
474 END IF;
475 RAISE FND_API.G_EXC_ERROR ;
476 END IF ;
477
478 SELECT COUNT(*) INTO l_freq_unit_count
479 FROM ozf_lookups
480 WHERE lookup_type='OZF_AUTOPAY_PERIOD_TYPE'
481 AND lookup_code = p_supp_trade_profile_rec.claim_frequency_unit ;
482
483 IF ( l_freq_unit_count=0 ) THEN
484
485 IF g_debug THEN
486 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid value for claim frequency Unit' );
487 END IF;
488 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
489 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CLM_FREQ_UNIT'); -- Invalid Claim Frequency Unit
490 FND_MSG_PUB.add;
491 END IF;
492 RAISE FND_API.G_EXC_ERROR ;
493
494 END IF ;
495 END IF ;
496
497
498 IF ( p_supp_trade_profile_rec.claim_computation_basis <>FND_API.g_miss_num
499 AND p_supp_trade_profile_rec.claim_computation_basis IS NOT NULL ) THEN
500
501 SELECT COUNT(*) INTO l_comp_baisis_count
502 FROM qp_price_formulas_vl formula
503 WHERE trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate))
504 AND nvl(end_date_active, trunc(sysdate))
505 AND formula.price_formula_id = p_supp_trade_profile_rec.claim_computation_basis ;
506
507 IF (l_comp_baisis_count = 0 ) THEN
508 IF g_debug THEN
509 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid value for claim computation baisis' );
510 END IF;
511 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
512 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CLM_COMP_BASIS'); -- Invalid Claim Computation Baisis
513 FND_MSG_PUB.add;
514 END IF;
515 RAISE FND_API.G_EXC_ERROR ;
516 END IF;
517
518 END IF ;
519
520 -- Validate the key flex field values
521
522 IF (p_supp_trade_profile_rec.gl_contra_liability_acct <> FND_API.g_miss_num AND
523 p_supp_trade_profile_rec.gl_contra_liability_acct IS NOT NULL ) THEN
524
525
526 Validate_GL_KeyFlex_val( p_combination_id => p_supp_trade_profile_rec.gl_contra_liability_acct,
527 x_return_status => x_return_status,
528 x_msg_count => x_msg_count,
529 x_msg_data => x_msg_data ) ;
530
531 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
532 IF g_debug THEN
533 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid GL Contra Laibility' );
534 END IF;
535 RAISE FND_API.G_EXC_ERROR ;
536 END IF ;
537
538 END IF ;
539
540
541 IF (p_supp_trade_profile_rec.gl_cost_adjustment_acct <> FND_API.g_miss_num AND
542 p_supp_trade_profile_rec.gl_cost_adjustment_acct IS NOT NULL ) THEN
543
544
545 Validate_GL_KeyFlex_val( p_combination_id => p_supp_trade_profile_rec.gl_cost_adjustment_acct,
546 x_return_status => x_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data ) ;
549
550 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
551 IF g_debug THEN
552 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid GL Cost Adjustment' );
553 END IF;
554 RAISE FND_API.G_EXC_ERROR ;
555 END IF ;
556
557 END IF ;
558
559 IF g_debug THEN
560 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Ends' );
561 END IF;
562
563
564 EXCEPTION
565 WHEN FND_API.G_EXC_ERROR THEN
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 FND_MSG_PUB.Count_And_Get (
568 p_encoded => FND_API.G_FALSE,
569 p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572 IF g_debug THEN
573 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
574 END IF;
575
576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 FND_MSG_PUB.Count_And_Get (
579 p_encoded => FND_API.G_FALSE,
580 p_count => x_msg_count,
581 p_data => x_msg_data
582 );
583 IF g_debug THEN
584 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
585 END IF;
586
587 WHEN OTHERS THEN
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
590 THEN
591 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
592 END IF;
593 FND_MSG_PUB.Count_And_Get (
594 p_encoded => FND_API.G_FALSE,
595 p_count => x_msg_count,
596 p_data => x_msg_data
597 );
598 IF g_debug THEN
599 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
600 END IF;
601
602
603 END Validate_Supp_Trade_Profile ;
604
605
606
607
608
609 ---------------------------------------------------------------------
610 -- PROCEDURE
611 -- Validate_Code_Cov_rec
612 --
613 -- PURPOSE
614 -- This procedure validates code conversion record
615 --Parameters
616 -- p_code_conv_rec - Code conversion record
617 -- x_return_status - Result
618 ---------------------------------------------------------------------
619
620
621
622 PROCEDURE Validate_Code_Cov_rec(
623 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
624 x_return_status OUT NOCOPY VARCHAR2,
625 x_msg_count OUT NOCOPY NUMBER,
626 x_msg_data OUT NOCOPY VARCHAR2,
627 p_code_conv_rec IN OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_rec_type,
628 p_mode IN VARCHAR2
629 )
630
631 IS
632
633 CURSOR csr_code_conv(cv_supp_trade_profile_id NUMBER
634 , cv_external_code VARCHAR2
635 , cv_internal_code VARCHAR2
636 , cv_start_date_active DATE
637 , cv_end_date_active DATE
638 , cv_conv_id NUMBER := -1)
639 IS
640 select code_conversion_id from ozf_supp_code_conversions_all where external_code = cv_external_code
641 and code_conversion_id <> cv_conv_id
642 and supp_trade_profile_id = cv_supp_trade_profile_id
643 and ( to_date(cv_start_date_active,'dd-mm-yyyy') between
644 to_date(start_date_active,'dd-mm-yyyy') and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
645 or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
646 to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')))
647 union
648 select code_conversion_id from ozf_supp_code_conversions_all where internal_code = cv_internal_code
649 and code_conversion_id <> cv_conv_id
650 and supp_trade_profile_id = cv_supp_trade_profile_id
651 and ( to_date(cv_start_date_active,'dd-mm-yyyy') between to_date(start_date_active,'dd-mm-yyyy')
652 and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
653 or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
654 to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')));
655
656
657
658 CURSOR csr_get_item(cv_item_number varchar2) IS
659 SELECT inventory_item_id
660 FROM mtl_system_items_vl
661 WHERE (organization_id = FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID'))
662 AND ENABLED_FLAG = 'Y'
663 AND fnd_date.canonical_to_date(sysdate) BETWEEN fnd_date.canonical_to_date(NVL(START_DATE_ACTIVE,sysdate))
664 AND fnd_date.canonical_to_date(NVL(END_DATE_ACTIVE,sysdate))
665 AND concatenated_segments = cv_item_number ;
666
667 -- ;
668
669
670
671 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Code_Cov_rec';
672 l_int_code_count NUMBER := 0 ;
673 l_dummy NUMBER := 0;
674
675
676
677 BEGIN
678
679 IF g_debug THEN
680 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts' );
681 END IF;
682
683 x_return_status := FND_API.g_ret_sts_success ;
684
685 -- Validate code conversion type
686 IF ( p_code_conv_rec.CODE_CONVERSION_TYPE IS NULL
687 OR p_code_conv_rec.CODE_CONVERSION_TYPE = FND_API.g_miss_char
688 OR p_code_conv_rec.CODE_CONVERSION_TYPE<>'OZF_PRODUCT_CODES' ) THEN
689
690 IF g_debug THEN
691 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid claim conversion type' );
692 END IF;
693 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
694 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CONTYPE_MISSING');
695 FND_MSG_PUB.add;
696 x_return_status := FND_API.G_RET_STS_ERROR ;
697 RETURN ;
698 END IF;
699 END IF ;
700
701
702
703 IF (p_mode = 'C') THEN
704 -- Validate internal code
705
706 OPEN csr_get_item(p_code_conv_rec.INTERNAL_CODE) ;
707
708 FETCH csr_get_item INTO p_code_conv_rec.INTERNAL_CODE ;
709
710 IF (csr_get_item%NOTFOUND) THEN
711
712 IF g_debug THEN
713 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid internal code' );
714 END IF;
715 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
716 FND_MESSAGE.set_name('CSS', 'CSS_DEF_INVALID_PRODUCT');
717 FND_MSG_PUB.add;
718 x_return_status := FND_API.G_RET_STS_ERROR ;
719 RETURN ;
720 END IF;
721
722 END IF ;
723
724 CLOSE csr_get_item ;
725 END IF ;
726
727
728 -- Validate external code
729 IF p_code_conv_rec.external_code = FND_API.g_miss_char OR
730 p_code_conv_rec.external_code IS NULL THEN
731
732 IF g_debug THEN
733 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid External code' );
734 END IF;
735 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
736 FND_MESSAGE.set_name('OZF', 'OZF_EXTERNAL_CODE_MISSING'||NVL(p_code_conv_rec.external_code,'NULL'));
737 FND_MSG_PUB.add;
738 END IF;
739 x_return_status := FND_API.G_RET_STS_ERROR ;
740 RETURN ;
741 END IF;
742
743
744 -- Validate uniquness of code conversion record
745 IF (p_mode = 'C') THEN
746 OPEN csr_code_conv(p_code_conv_rec.supp_trade_profile_id,
747 p_code_conv_rec.external_code,
748 p_code_conv_rec.internal_code,
749 p_code_conv_rec.start_date_active,
750 p_code_conv_rec.end_date_active);
751
752 FETCH csr_code_conv
753 INTO l_dummy;
754 CLOSE csr_code_conv;
755
756 IF (l_dummy > 0) THEN
757
758 IF g_debug THEN
759 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Duplicate code conversion' );
760 END IF;
761 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
762 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONVERSION_DUPLICATE');
763 FND_MSG_PUB.add;
764 END IF;
765 x_return_status := FND_API.G_RET_STS_ERROR ;
766 RETURN ;
767
768 END IF;
769 END IF ;
770
771
772 -- Validate start date for code conversion
773 IF NVL(p_code_conv_rec.start_date_active,TRUNC(SYSDATE)) < TRUNC(SYSDATE)
774 THEN
775
776 IF g_debug THEN
777 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid start date' );
778 END IF;
779
780 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
781 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_STDATE_BKDATED');
782 FND_MSG_PUB.add;
783 END IF;
784 x_return_status := FND_API.G_RET_STS_ERROR ;
785 RETURN ;
786 END IF;
787
788 IF g_debug THEN
789 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' End' );
790 END IF;
791
792 EXCEPTION
793
794 WHEN OTHERS THEN
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796 IF g_debug THEN
797 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
798 END IF;
799
800
801 END Validate_Code_Cov_rec ;
802
803
804
805 ---------------------------------------------------------------------
806 -- PROCEDURE
807 -- Populate_code_conv_rec
808 --
809 -- PURPOSE
810 -- This procedure populates the code conversion record with values in DB
811 --Parameters
812 -- p_code_con_rec - Input Code conversion record
813 -- x_code_con_rec - Output Code conversion record
814 ---------------------------------------------------------------------
815
816
817
818 PROCEDURE Populate_code_conv_rec(
819 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2,
823 p_code_con_rec IN OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_rec_type)
824
825 IS
826 L_API_NAME CONSTANT VARCHAR2(30) := 'Populate_code_conv_rec';
827
828
829 CURSOR c_pop_rec IS
830 SELECT code_conversion_id,
831 object_version_number,
832 SYSDATE,
833 DECODE(p_code_con_rec.last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_code_con_rec.last_updated_by),
834 DECODE(p_code_con_rec.last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.CONC_LOGIN_ID,p_code_con_rec.last_update_login),
835 ORG_ID,
836 supp_trade_profile_id,
837 DECODE( p_code_con_rec.code_conversion_type, FND_API.g_miss_char, code_conversion_type, p_code_con_rec.code_conversion_type),
838 external_code,
839 internal_code,
840 DECODE( p_code_con_rec.description, FND_API.g_miss_char, description, p_code_con_rec.description),
841 DECODE( p_code_con_rec.start_date_active, FND_API.G_MISS_DATE,start_date_active , p_code_con_rec.start_date_active),
842 DECODE( p_code_con_rec.end_date_active, FND_API.G_MISS_DATE,end_date_active , p_code_con_rec.end_date_active),
843 DECODE( p_code_con_rec.attribute_category, FND_API.g_miss_char, attribute_category,p_code_con_rec.attribute_category),
844 DECODE( p_code_con_rec.attribute1, FND_API.g_miss_char, attribute1, p_code_con_rec.attribute1),
845 DECODE( p_code_con_rec.attribute2, FND_API.g_miss_char, attribute2, p_code_con_rec.attribute2),
846 DECODE( p_code_con_rec.attribute3, FND_API.g_miss_char, attribute3, p_code_con_rec.attribute3),
847 DECODE( p_code_con_rec.attribute4, FND_API.g_miss_char, attribute4, p_code_con_rec.attribute4),
848 DECODE( p_code_con_rec.attribute5, FND_API.g_miss_char, attribute5, p_code_con_rec.attribute5),
849 DECODE( p_code_con_rec.attribute6, FND_API.g_miss_char, attribute6, p_code_con_rec.attribute6),
850 DECODE( p_code_con_rec.attribute7, FND_API.g_miss_char, attribute7, p_code_con_rec.attribute7),
851 DECODE( p_code_con_rec.attribute8, FND_API.g_miss_char, attribute8, p_code_con_rec.attribute8),
852 DECODE( p_code_con_rec.attribute9, FND_API.g_miss_char, attribute9, p_code_con_rec.attribute9),
853 DECODE( p_code_con_rec.attribute10, FND_API.g_miss_char, attribute10, p_code_con_rec.attribute10),
854 DECODE( p_code_con_rec.attribute11, FND_API.g_miss_char, attribute11, p_code_con_rec.attribute11),
855 DECODE( p_code_con_rec.attribute12, FND_API.g_miss_char, attribute12, p_code_con_rec.attribute12),
856 DECODE( p_code_con_rec.attribute13, FND_API.g_miss_char, attribute13, p_code_con_rec.attribute13),
857 DECODE( p_code_con_rec.attribute14, FND_API.g_miss_char, attribute14, p_code_con_rec.attribute14),
858 DECODE( p_code_con_rec.attribute15, FND_API.g_miss_char, attribute15, p_code_con_rec.attribute15)
859 FROM ozf_supp_code_conversions_all
860 WHERE supp_trade_profile_id = p_code_con_rec.supp_trade_profile_id
861 AND code_conversion_id = p_code_con_rec.code_conversion_id;
862
863
864
865 BEGIN
866
867 x_return_status := FND_API.g_ret_sts_success ;
868
869 IF g_debug THEN
870 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts' );
871 END IF;
872
873
874 OPEN c_pop_rec ;
875
876 FETCH c_pop_rec
877 INTO p_code_con_rec.code_conversion_id,
878 p_code_con_rec.OBJECT_VERSION_NUMBER,
879 p_code_con_rec.last_update_date,
880 p_code_con_rec.last_updated_by,
881 p_code_con_rec.last_update_login,
882 p_code_con_rec.ORG_ID,
883 p_code_con_rec.supp_trade_profile_id,
884 p_code_con_rec.code_conversion_type,
885 p_code_con_rec.external_code,
886 p_code_con_rec.internal_code,
887 p_code_con_rec.description,
888 p_code_con_rec.start_date_active,
889 p_code_con_rec.end_date_active,
890 p_code_con_rec.attribute_category,
891 p_code_con_rec.attribute1,
892 p_code_con_rec.attribute2,
893 p_code_con_rec.attribute3,
894 p_code_con_rec.attribute4,
895 p_code_con_rec.attribute5,
896 p_code_con_rec.attribute6,
897 p_code_con_rec.attribute7,
898 p_code_con_rec.attribute8,
899 p_code_con_rec.attribute9,
900 p_code_con_rec.attribute10,
901 p_code_con_rec.attribute11,
902 p_code_con_rec.attribute12,
903 p_code_con_rec.attribute13,
904 p_code_con_rec.attribute14,
905 p_code_con_rec.attribute15 ;
906 IF ( c_pop_rec%NOTFOUND) THEN
907
908 IF g_debug THEN
909 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' No data found for cursor' );
910 END IF;
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
912 ELSE
913 x_return_status := FND_API.g_ret_sts_success ;
914 END IF ;
915
916 CLOSE c_pop_rec;
917
918
919 EXCEPTION
920
921 WHEN OTHERS THEN
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923 IF g_debug THEN
924 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
925 END IF;
926
927
928
929 END Populate_code_conv_rec ;
930
931
932
933
934 ---------------------------------------------------------------------
935 -- PROCEDURE
936 -- Convert_Supp_Trade_Profile
937 --
938 -- PURPOSE
939 -- This procedure Converts the public trade profile record to prive
940 --Parameters
941 -- p_supp_trade_profile_rec - Private trade profile record
942 -- x_supp_trade_profile_rec - Public trade profile record
943 ---------------------------------------------------------------------
944
945 PROCEDURE Convert_Supp_Trade_Profile(
946 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_supp_trade_profile_rec IN supp_trade_profile_rec_type,
951 x_supp_trade_profile_rec OUT NOCOPY OZF_SUPP_TRADE_PROFILE_PVT.supp_trade_profile_rec_type,
952 p_action IN VARCHAR2 := 'C'
953 )
954
955 IS
956 L_API_NAME CONSTANT VARCHAR2(30) := 'Convert_Supp_Trade_Profile';
957 l_supp_trade_profile_rec OZF_SUPP_TRADE_PROFILE_PVT.supp_trade_profile_rec_type ;
958 BEGIN
959
960 IF g_debug THEN
961 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts' );
962 END IF;
963
964
965 IF (p_action = 'U') THEN
966 -- Default initialization Starts
967
968 l_supp_trade_profile_rec.supp_trade_profile_id := FND_API.g_miss_num ;
969 l_supp_trade_profile_rec.object_version_number := FND_API.g_miss_num ;
970 l_supp_trade_profile_rec.last_update_date := FND_API.G_MISS_DATE ;
971 l_supp_trade_profile_rec.last_updated_by := FND_API.g_miss_num ;
972 l_supp_trade_profile_rec.creation_date := FND_API.G_MISS_DATE ;
973 l_supp_trade_profile_rec.created_by := FND_API.g_miss_num ;
974 l_supp_trade_profile_rec.last_update_login := FND_API.g_miss_num ;
975 l_supp_trade_profile_rec.request_id := FND_API.g_miss_num ;
976 l_supp_trade_profile_rec.program_application_id := FND_API.g_miss_num ;
977 l_supp_trade_profile_rec.program_update_date := FND_API.G_MISS_DATE ;
978 l_supp_trade_profile_rec.program_id := FND_API.g_miss_num ;
979 l_supp_trade_profile_rec.created_from := FND_API.g_miss_char ;
980 l_supp_trade_profile_rec.supplier_id := FND_API.g_miss_num ;
981 l_supp_trade_profile_rec.supplier_site_id := FND_API.g_miss_num ;
982 l_supp_trade_profile_rec.party_id := FND_API.g_miss_num ;
983 l_supp_trade_profile_rec.cust_account_id := FND_API.g_miss_num ;
984 l_supp_trade_profile_rec.cust_acct_site_id := FND_API.g_miss_num ;
985 l_supp_trade_profile_rec.site_use_id := FND_API.g_miss_num ;
986 l_supp_trade_profile_rec.pre_approval_flag := FND_API.g_miss_char ;
987 l_supp_trade_profile_rec.approval_communication := FND_API.g_miss_char ;
988 l_supp_trade_profile_rec.gl_contra_liability_acct := FND_API.g_miss_num ;
989 l_supp_trade_profile_rec.gl_cost_adjustment_acct := FND_API.g_miss_num ;
990 l_supp_trade_profile_rec.default_days_covered := FND_API.g_miss_num ;
991 l_supp_trade_profile_rec.create_claim_price_increase := FND_API.g_miss_char ;
992 l_supp_trade_profile_rec.skip_approval_flag := FND_API.g_miss_char ;
993 l_supp_trade_profile_rec.skip_adjustment_flag := FND_API.g_miss_char ;
994 l_supp_trade_profile_rec.settlement_method_supplier_inc := FND_API.g_miss_char ;
995 l_supp_trade_profile_rec.settlement_method_supplier_dec := FND_API.g_miss_char ;
996 l_supp_trade_profile_rec.settlement_method_customer := FND_API.g_miss_char ;
997 l_supp_trade_profile_rec.authorization_period := FND_API.g_miss_num ;
998 l_supp_trade_profile_rec.grace_days := FND_API.g_miss_num ;
999 l_supp_trade_profile_rec.allow_qty_increase := FND_API.g_miss_char ;
1000 l_supp_trade_profile_rec.qty_increase_tolerance := FND_API.g_miss_num ;
1001 l_supp_trade_profile_rec.request_communication := FND_API.g_miss_char ;
1002 l_supp_trade_profile_rec.claim_communication := FND_API.g_miss_char ;
1003 l_supp_trade_profile_rec.claim_frequency := FND_API.g_miss_num ;
1004 l_supp_trade_profile_rec.claim_frequency_unit := FND_API.g_miss_char ;
1005 l_supp_trade_profile_rec.claim_computation_basis := FND_API.g_miss_num ;
1006 l_supp_trade_profile_rec.attribute_category := FND_API.g_miss_char ;
1007 l_supp_trade_profile_rec.attribute1 := FND_API.g_miss_char ;
1008 l_supp_trade_profile_rec.attribute2 := FND_API.g_miss_char ;
1009 l_supp_trade_profile_rec.attribute3 := FND_API.g_miss_char ;
1010 l_supp_trade_profile_rec.attribute4 := FND_API.g_miss_char ;
1011 l_supp_trade_profile_rec.attribute5 := FND_API.g_miss_char ;
1012 l_supp_trade_profile_rec.attribute6 := FND_API.g_miss_char ;
1013 l_supp_trade_profile_rec.attribute7 := FND_API.g_miss_char ;
1014 l_supp_trade_profile_rec.attribute8 := FND_API.g_miss_char ;
1015 l_supp_trade_profile_rec.attribute9 := FND_API.g_miss_char ;
1016 l_supp_trade_profile_rec.attribute10 := FND_API.g_miss_char ;
1017 l_supp_trade_profile_rec.attribute11 := FND_API.g_miss_char ;
1018 l_supp_trade_profile_rec.attribute12 := FND_API.g_miss_char ;
1019 l_supp_trade_profile_rec.attribute13 := FND_API.g_miss_char ;
1020 l_supp_trade_profile_rec.attribute14 := FND_API.g_miss_char ;
1021 l_supp_trade_profile_rec.attribute15 := FND_API.g_miss_char ;
1022 l_supp_trade_profile_rec.attribute16 := FND_API.g_miss_char ;
1023 l_supp_trade_profile_rec.attribute17 := FND_API.g_miss_char ;
1024 l_supp_trade_profile_rec.attribute18 := FND_API.g_miss_char ;
1025 l_supp_trade_profile_rec.attribute19 := FND_API.g_miss_char ;
1026 l_supp_trade_profile_rec.attribute20 := FND_API.g_miss_char ;
1027 l_supp_trade_profile_rec.attribute21 := FND_API.g_miss_char ;
1028 l_supp_trade_profile_rec.attribute22 := FND_API.g_miss_char ;
1029 l_supp_trade_profile_rec.attribute23 := FND_API.g_miss_char ;
1030 l_supp_trade_profile_rec.attribute24 := FND_API.g_miss_char ;
1031 l_supp_trade_profile_rec.attribute25 := FND_API.g_miss_char ;
1032 l_supp_trade_profile_rec.attribute26 := FND_API.g_miss_char ;
1033 l_supp_trade_profile_rec.attribute27 := FND_API.g_miss_char ;
1034 l_supp_trade_profile_rec.attribute28 := FND_API.g_miss_char ;
1035 l_supp_trade_profile_rec.attribute29 := FND_API.g_miss_char ;
1036 l_supp_trade_profile_rec.attribute30 := FND_API.g_miss_char ;
1037 l_supp_trade_profile_rec.dpp_attribute_category := FND_API.g_miss_char ;
1038 l_supp_trade_profile_rec.dpp_attribute1 := FND_API.g_miss_char ;
1039 l_supp_trade_profile_rec.dpp_attribute2 := FND_API.g_miss_char ;
1040 l_supp_trade_profile_rec.dpp_attribute3 := FND_API.g_miss_char ;
1041 l_supp_trade_profile_rec.dpp_attribute4 := FND_API.g_miss_char ;
1042 l_supp_trade_profile_rec.dpp_attribute5 := FND_API.g_miss_char ;
1043 l_supp_trade_profile_rec.dpp_attribute6 := FND_API.g_miss_char ;
1044 l_supp_trade_profile_rec.dpp_attribute7 := FND_API.g_miss_char ;
1045 l_supp_trade_profile_rec.dpp_attribute8 := FND_API.g_miss_char ;
1046 l_supp_trade_profile_rec.dpp_attribute9 := FND_API.g_miss_char ;
1047 l_supp_trade_profile_rec.dpp_attribute10 := FND_API.g_miss_char ;
1048 l_supp_trade_profile_rec.dpp_attribute11 := FND_API.g_miss_char ;
1049 l_supp_trade_profile_rec.dpp_attribute12 := FND_API.g_miss_char ;
1050 l_supp_trade_profile_rec.dpp_attribute13 := FND_API.g_miss_char ;
1051 l_supp_trade_profile_rec.dpp_attribute14 := FND_API.g_miss_char ;
1052 l_supp_trade_profile_rec.dpp_attribute15 := FND_API.g_miss_char ;
1053 l_supp_trade_profile_rec.dpp_attribute16 := FND_API.g_miss_char ;
1054 l_supp_trade_profile_rec.dpp_attribute17 := FND_API.g_miss_char ;
1055 l_supp_trade_profile_rec.dpp_attribute18 := FND_API.g_miss_char ;
1056 l_supp_trade_profile_rec.dpp_attribute19 := FND_API.g_miss_char ;
1057 l_supp_trade_profile_rec.dpp_attribute20 := FND_API.g_miss_char ;
1058 l_supp_trade_profile_rec.dpp_attribute21 := FND_API.g_miss_char ;
1059 l_supp_trade_profile_rec.dpp_attribute22 := FND_API.g_miss_char ;
1060 l_supp_trade_profile_rec.dpp_attribute23 := FND_API.g_miss_char ;
1061 l_supp_trade_profile_rec.dpp_attribute24 := FND_API.g_miss_char ;
1062 l_supp_trade_profile_rec.dpp_attribute25 := FND_API.g_miss_char ;
1063 l_supp_trade_profile_rec.dpp_attribute26 := FND_API.g_miss_char ;
1064 l_supp_trade_profile_rec.dpp_attribute27 := FND_API.g_miss_char ;
1065 l_supp_trade_profile_rec.dpp_attribute28 := FND_API.g_miss_char ;
1066 l_supp_trade_profile_rec.dpp_attribute29 := FND_API.g_miss_char ;
1067 l_supp_trade_profile_rec.dpp_attribute30 := FND_API.g_miss_char ;
1068 l_supp_trade_profile_rec.org_id := FND_API.g_miss_num ;
1069 l_supp_trade_profile_rec.security_group_id := FND_API.g_miss_num ;
1070 l_supp_trade_profile_rec.claim_currency_code := FND_API.g_miss_char ;
1071 l_supp_trade_profile_rec.min_claim_amt := FND_API.g_miss_num ;
1072 l_supp_trade_profile_rec.min_claim_amt_line_lvl := FND_API.g_miss_num ;
1073 l_supp_trade_profile_rec.auto_debit := FND_API.g_miss_char ;
1074 l_supp_trade_profile_rec.days_before_claiming_debit := FND_API.g_miss_num ;
1075 l_supp_trade_profile_rec.ssd_imd_claim_flag := FND_API.g_miss_char ;
1076
1077 -- Default initialization Ends
1078 END IF ;
1079
1080
1081 IF (p_supp_trade_profile_rec.object_version_number<>FND_API.g_miss_num) THEN
1082 l_supp_trade_profile_rec.object_version_number := p_supp_trade_profile_rec.object_version_number ;
1083 END IF ;
1084
1085 IF (p_supp_trade_profile_rec.last_update_date <> FND_API.G_MISS_DATE) THEN
1086 l_supp_trade_profile_rec.last_update_date := p_supp_trade_profile_rec.last_update_date ;
1087 END IF ;
1088
1089 IF (p_supp_trade_profile_rec.last_updated_by <> FND_API.g_miss_num) THEN
1090 l_supp_trade_profile_rec.last_updated_by := p_supp_trade_profile_rec.last_updated_by ;
1091 END IF ;
1092
1093 IF (p_supp_trade_profile_rec.creation_date <> FND_API.G_MISS_DATE) THEN
1094 l_supp_trade_profile_rec.creation_date := p_supp_trade_profile_rec.creation_date ;
1095 END IF ;
1096
1097 IF (p_supp_trade_profile_rec.created_by <> FND_API.g_miss_num) THEN
1098 l_supp_trade_profile_rec.created_by := p_supp_trade_profile_rec.created_by ;
1099 END IF ;
1100
1101 IF (p_supp_trade_profile_rec.last_update_login <> FND_API.g_miss_num ) THEN
1102 l_supp_trade_profile_rec.last_update_login := p_supp_trade_profile_rec.last_update_login ;
1103 END IF ;
1104
1105 IF (p_supp_trade_profile_rec.request_id <> FND_API.g_miss_num ) THEN
1106 l_supp_trade_profile_rec.request_id := p_supp_trade_profile_rec.request_id ;
1107 END IF ;
1108
1109 IF (p_supp_trade_profile_rec.program_application_id <> FND_API.g_miss_num ) THEN
1110 l_supp_trade_profile_rec.program_application_id := p_supp_trade_profile_rec.program_application_id ;
1111 END IF ;
1112
1113 IF (p_supp_trade_profile_rec.program_update_date <> FND_API.G_MISS_DATE) THEN
1114 l_supp_trade_profile_rec.program_update_date := p_supp_trade_profile_rec.program_update_date ;
1115 END IF ;
1116
1117 IF(p_supp_trade_profile_rec.program_id <> FND_API.g_miss_num) THEN
1118 l_supp_trade_profile_rec.program_id := p_supp_trade_profile_rec.program_id ;
1119 END IF ;
1120
1121 IF (p_supp_trade_profile_rec.created_from <> FND_API.g_miss_char) THEN
1122 l_supp_trade_profile_rec.created_from := p_supp_trade_profile_rec.created_from ;
1123 END IF ;
1124
1125
1126
1127 l_supp_trade_profile_rec.supplier_id := p_supp_trade_profile_rec.supplier_id ;
1128 l_supp_trade_profile_rec.supplier_site_id := p_supp_trade_profile_rec.supplier_site_id ;
1129 l_supp_trade_profile_rec.party_id := p_supp_trade_profile_rec.party_id ;
1130 l_supp_trade_profile_rec.cust_account_id := p_supp_trade_profile_rec.cust_account_id ;
1131 l_supp_trade_profile_rec.cust_acct_site_id := p_supp_trade_profile_rec.cust_acct_site_id ;
1132 l_supp_trade_profile_rec.site_use_id := p_supp_trade_profile_rec.site_use_id ;
1133
1134
1135 IF(p_supp_trade_profile_rec.pre_approval_flag <> FND_API.g_miss_char ) THEN
1136 l_supp_trade_profile_rec.pre_approval_flag := p_supp_trade_profile_rec.pre_approval_flag ;
1137 END IF ;
1138
1139 IF(p_supp_trade_profile_rec.approval_communication = FND_API.g_miss_char OR p_supp_trade_profile_rec.approval_communication IS NULL ) THEN
1140
1141 l_supp_trade_profile_rec.approval_communication := FND_API.g_miss_char ;
1142 ELSE
1143 l_supp_trade_profile_rec.approval_communication := p_supp_trade_profile_rec.approval_communication ;
1144 END IF ;
1145
1146
1147 IF (p_supp_trade_profile_rec.gl_contra_liability_acct <> FND_API.g_miss_num ) THEN
1148 l_supp_trade_profile_rec.gl_contra_liability_acct := p_supp_trade_profile_rec.gl_contra_liability_acct ;
1149 END IF ;
1150
1151 IF (p_supp_trade_profile_rec.gl_cost_adjustment_acct <> FND_API.g_miss_num) THEN
1152 l_supp_trade_profile_rec.gl_cost_adjustment_acct := p_supp_trade_profile_rec.gl_cost_adjustment_acct ;
1153 END IF ;
1154
1155 IF (p_supp_trade_profile_rec.default_days_covered <> FND_API.g_miss_num) THEN
1156 l_supp_trade_profile_rec.default_days_covered := p_supp_trade_profile_rec.default_days_covered ;
1157 END IF ;
1158
1159 IF(p_supp_trade_profile_rec.create_claim_price_increase<> FND_API.g_miss_char) THEN
1160 l_supp_trade_profile_rec.create_claim_price_increase := p_supp_trade_profile_rec.create_claim_price_increase ;
1161 END IF ;
1162
1163 IF(p_supp_trade_profile_rec.skip_approval_flag <> FND_API.g_miss_char ) THEN
1164 l_supp_trade_profile_rec.skip_approval_flag := p_supp_trade_profile_rec.skip_approval_flag ;
1165 END IF ;
1166
1167 IF(p_supp_trade_profile_rec.skip_adjustment_flag <> FND_API.g_miss_char ) THEN
1168 l_supp_trade_profile_rec.skip_adjustment_flag := p_supp_trade_profile_rec.skip_adjustment_flag ;
1169 END IF ;
1170
1171 IF(p_supp_trade_profile_rec.settlement_method_supplier_inc <> FND_API.g_miss_char ) THEN
1172 l_supp_trade_profile_rec.settlement_method_supplier_inc := p_supp_trade_profile_rec.settlement_method_supplier_inc ;
1173 END IF ;
1174
1175 IF(p_supp_trade_profile_rec.settlement_method_supplier_dec <> FND_API.g_miss_char ) THEN
1176 l_supp_trade_profile_rec.settlement_method_supplier_dec := p_supp_trade_profile_rec.settlement_method_supplier_dec ;
1177 END IF ;
1178
1179 IF(p_supp_trade_profile_rec.settlement_method_customer <> FND_API.g_miss_char) THEN
1180 l_supp_trade_profile_rec.settlement_method_customer := p_supp_trade_profile_rec.settlement_method_customer ;
1181 END IF ;
1182
1183 IF(p_supp_trade_profile_rec.authorization_period <> FND_API.g_miss_num) THEN
1184 l_supp_trade_profile_rec.authorization_period := p_supp_trade_profile_rec.authorization_period ;
1185 END IF ;
1186
1187 IF(p_supp_trade_profile_rec.grace_days <> FND_API.g_miss_num ) THEN
1188 l_supp_trade_profile_rec.grace_days := p_supp_trade_profile_rec.grace_days ;
1189 END IF ;
1190
1191 IF(p_supp_trade_profile_rec.allow_qty_increase <> FND_API.g_miss_char ) THEN
1192 l_supp_trade_profile_rec.allow_qty_increase := p_supp_trade_profile_rec.allow_qty_increase ;
1193 END IF ;
1194
1195 IF(p_supp_trade_profile_rec.qty_increase_tolerance <> FND_API.g_miss_num ) THEN
1196 l_supp_trade_profile_rec.qty_increase_tolerance := p_supp_trade_profile_rec.qty_increase_tolerance ;
1197 END IF ;
1198
1199 IF(p_supp_trade_profile_rec.request_communication <> FND_API.g_miss_char ) THEN
1200 l_supp_trade_profile_rec.request_communication := p_supp_trade_profile_rec.request_communication ;
1201 END IF ;
1202
1203 IF(p_supp_trade_profile_rec.claim_communication<> FND_API.g_miss_char ) THEN
1204 l_supp_trade_profile_rec.claim_communication := p_supp_trade_profile_rec.claim_communication ;
1205 END IF ;
1206
1207 IF(p_supp_trade_profile_rec.claim_frequency <> FND_API.g_miss_num ) THEN
1208 l_supp_trade_profile_rec.claim_frequency := p_supp_trade_profile_rec.claim_frequency ;
1209 END IF ;
1210
1211 IF(p_supp_trade_profile_rec.claim_frequency_unit <> FND_API.g_miss_char) THEN
1212 l_supp_trade_profile_rec.claim_frequency_unit := p_supp_trade_profile_rec.claim_frequency_unit ;
1213 END IF ;
1214
1215 IF( p_supp_trade_profile_rec.claim_computation_basis <> FND_API.g_miss_num ) THEN
1216 l_supp_trade_profile_rec.claim_computation_basis := p_supp_trade_profile_rec.claim_computation_basis ;
1217 END IF ;
1218
1219 IF(p_supp_trade_profile_rec.attribute_category <> FND_API.g_miss_char ) THEN
1220 l_supp_trade_profile_rec.attribute_category := p_supp_trade_profile_rec.attribute_category ;
1221 END IF ;
1222
1223 IF(p_supp_trade_profile_rec.attribute1<> FND_API.g_miss_char) THEN
1224 l_supp_trade_profile_rec.attribute1 := p_supp_trade_profile_rec.attribute1 ;
1225 END IF ;
1226
1227 IF(p_supp_trade_profile_rec.attribute2<> FND_API.g_miss_char) THEN
1228 l_supp_trade_profile_rec.attribute2 := p_supp_trade_profile_rec.attribute2 ;
1229 END IF ;
1230
1231 IF(p_supp_trade_profile_rec.attribute3<> FND_API.g_miss_char) THEN
1232 l_supp_trade_profile_rec.attribute3 := p_supp_trade_profile_rec.attribute3 ;
1233 END IF ;
1234
1235 IF(p_supp_trade_profile_rec.attribute4<> FND_API.g_miss_char) THEN
1236 l_supp_trade_profile_rec.attribute4 := p_supp_trade_profile_rec.attribute4 ;
1237 END IF ;
1238
1239 IF(p_supp_trade_profile_rec.attribute5<> FND_API.g_miss_char) THEN
1240 l_supp_trade_profile_rec.attribute5 := p_supp_trade_profile_rec.attribute5 ;
1241 END IF ;
1242
1243 IF(p_supp_trade_profile_rec.attribute6<> FND_API.g_miss_char) THEN
1244 l_supp_trade_profile_rec.attribute6 := p_supp_trade_profile_rec.attribute6 ;
1245 END IF ;
1246
1247 IF(p_supp_trade_profile_rec.attribute7<> FND_API.g_miss_char) THEN
1248 l_supp_trade_profile_rec.attribute7 := p_supp_trade_profile_rec.attribute7 ;
1249 END IF ;
1250
1251 IF(p_supp_trade_profile_rec.attribute8<> FND_API.g_miss_char) THEN
1252 l_supp_trade_profile_rec.attribute8 := p_supp_trade_profile_rec.attribute8 ;
1253 END IF ;
1254
1255 IF(p_supp_trade_profile_rec.attribute9<> FND_API.g_miss_char) THEN
1256 l_supp_trade_profile_rec.attribute9 := p_supp_trade_profile_rec.attribute9 ;
1257 END IF ;
1258
1259 IF(p_supp_trade_profile_rec.attribute10<> FND_API.g_miss_char) THEN
1260 l_supp_trade_profile_rec.attribute10 := p_supp_trade_profile_rec.attribute10 ;
1261 END IF ;
1262
1263 IF(p_supp_trade_profile_rec.attribute11<> FND_API.g_miss_char) THEN
1264 l_supp_trade_profile_rec.attribute11 := p_supp_trade_profile_rec.attribute11 ;
1265 END IF ;
1266
1267 IF(p_supp_trade_profile_rec.attribute12<> FND_API.g_miss_char) THEN
1268 l_supp_trade_profile_rec.attribute12 := p_supp_trade_profile_rec.attribute12 ;
1269 END IF ;
1270
1271 IF(p_supp_trade_profile_rec.attribute13<> FND_API.g_miss_char) THEN
1272 l_supp_trade_profile_rec.attribute13 := p_supp_trade_profile_rec.attribute13 ;
1273 END IF ;
1274
1275 IF(p_supp_trade_profile_rec.attribute14<> FND_API.g_miss_char) THEN
1276 l_supp_trade_profile_rec.attribute14 := p_supp_trade_profile_rec.attribute14 ;
1277 END IF ;
1278
1279 IF(p_supp_trade_profile_rec.attribute15<> FND_API.g_miss_char) THEN
1280 l_supp_trade_profile_rec.attribute15 := p_supp_trade_profile_rec.attribute15 ;
1281 END IF ;
1282
1283 IF(p_supp_trade_profile_rec.attribute16<> FND_API.g_miss_char) THEN
1284 l_supp_trade_profile_rec.attribute16 := p_supp_trade_profile_rec.attribute16 ;
1285 END IF ;
1286
1287 IF(p_supp_trade_profile_rec.attribute17<> FND_API.g_miss_char) THEN
1288 l_supp_trade_profile_rec.attribute17 := p_supp_trade_profile_rec.attribute17 ;
1289 END IF ;
1290
1291 IF(p_supp_trade_profile_rec.attribute18<> FND_API.g_miss_char) THEN
1292 l_supp_trade_profile_rec.attribute18 := p_supp_trade_profile_rec.attribute18 ;
1293 END IF ;
1294
1295 IF(p_supp_trade_profile_rec.attribute19<> FND_API.g_miss_char) THEN
1296 l_supp_trade_profile_rec.attribute19 := p_supp_trade_profile_rec.attribute19 ;
1297 END IF ;
1298
1299 IF(p_supp_trade_profile_rec.attribute20<> FND_API.g_miss_char) THEN
1300 l_supp_trade_profile_rec.attribute20 := p_supp_trade_profile_rec.attribute20 ;
1301 END IF ;
1302
1303 IF(p_supp_trade_profile_rec.attribute21<> FND_API.g_miss_char) THEN
1304 l_supp_trade_profile_rec.attribute21 := p_supp_trade_profile_rec.attribute21 ;
1305 END IF ;
1306
1307 IF(p_supp_trade_profile_rec.attribute22<> FND_API.g_miss_char) THEN
1308 l_supp_trade_profile_rec.attribute22 := p_supp_trade_profile_rec.attribute22 ;
1309 END IF ;
1310
1311 IF(p_supp_trade_profile_rec.attribute23<> FND_API.g_miss_char) THEN
1312 l_supp_trade_profile_rec.attribute23 := p_supp_trade_profile_rec.attribute23 ;
1313 END IF ;
1314
1315 IF(p_supp_trade_profile_rec.attribute24<> FND_API.g_miss_char) THEN
1316 l_supp_trade_profile_rec.attribute24 := p_supp_trade_profile_rec.attribute24 ;
1317 END IF ;
1318
1319 IF(p_supp_trade_profile_rec.attribute25<> FND_API.g_miss_char) THEN
1320 l_supp_trade_profile_rec.attribute25 := p_supp_trade_profile_rec.attribute25 ;
1321 END IF ;
1322
1323 IF(p_supp_trade_profile_rec.attribute26<> FND_API.g_miss_char) THEN
1324 l_supp_trade_profile_rec.attribute26 := p_supp_trade_profile_rec.attribute26 ;
1325 END IF ;
1326
1327 IF(p_supp_trade_profile_rec.attribute27<> FND_API.g_miss_char) THEN
1328 l_supp_trade_profile_rec.attribute27 := p_supp_trade_profile_rec.attribute27 ;
1329 END IF ;
1330
1331 IF(p_supp_trade_profile_rec.attribute28<> FND_API.g_miss_char) THEN
1332 l_supp_trade_profile_rec.attribute28 := p_supp_trade_profile_rec.attribute28 ;
1333 END IF ;
1334
1335 IF(p_supp_trade_profile_rec.attribute29<> FND_API.g_miss_char) THEN
1336 l_supp_trade_profile_rec.attribute29 := p_supp_trade_profile_rec.attribute29 ;
1337 END IF ;
1338
1339 IF(p_supp_trade_profile_rec.attribute30<> FND_API.g_miss_char) THEN
1340 l_supp_trade_profile_rec.attribute30 := p_supp_trade_profile_rec.attribute30 ;
1341 END IF ;
1342
1343
1344 --ninarasi fix start for bug 12739578
1345 IF(p_supp_trade_profile_rec.dpp_attribute_category <> FND_API.g_miss_char ) THEN
1346 l_supp_trade_profile_rec.dpp_attribute_category := p_supp_trade_profile_rec.dpp_attribute_category ;
1347 END IF ;
1348
1349 IF(p_supp_trade_profile_rec.dpp_attribute1<> FND_API.g_miss_char) THEN
1350 l_supp_trade_profile_rec.dpp_attribute1 := p_supp_trade_profile_rec.dpp_attribute1 ;
1351 END IF ;
1352
1353 IF(p_supp_trade_profile_rec.dpp_attribute2<> FND_API.g_miss_char) THEN
1354 l_supp_trade_profile_rec.dpp_attribute2 := p_supp_trade_profile_rec.dpp_attribute2 ;
1355 END IF ;
1356
1357 IF(p_supp_trade_profile_rec.dpp_attribute3<> FND_API.g_miss_char) THEN
1358 l_supp_trade_profile_rec.dpp_attribute3 := p_supp_trade_profile_rec.dpp_attribute3 ;
1359 END IF ;
1360
1361 IF(p_supp_trade_profile_rec.dpp_attribute4<> FND_API.g_miss_char) THEN
1362 l_supp_trade_profile_rec.dpp_attribute4 := p_supp_trade_profile_rec.dpp_attribute4 ;
1363 END IF ;
1364
1365 IF(p_supp_trade_profile_rec.dpp_attribute5<> FND_API.g_miss_char) THEN
1366 l_supp_trade_profile_rec.dpp_attribute5 := p_supp_trade_profile_rec.dpp_attribute5 ;
1367 END IF ;
1368
1369 IF(p_supp_trade_profile_rec.dpp_attribute6<> FND_API.g_miss_char) THEN
1370 l_supp_trade_profile_rec.dpp_attribute6 := p_supp_trade_profile_rec.dpp_attribute6 ;
1371 END IF ;
1372
1373 IF(p_supp_trade_profile_rec.dpp_attribute7<> FND_API.g_miss_char) THEN
1374 l_supp_trade_profile_rec.dpp_attribute7 := p_supp_trade_profile_rec.dpp_attribute7 ;
1375 END IF ;
1376
1377 IF(p_supp_trade_profile_rec.dpp_attribute8<> FND_API.g_miss_char) THEN
1378 l_supp_trade_profile_rec.dpp_attribute8 := p_supp_trade_profile_rec.dpp_attribute8 ;
1379 END IF ;
1380
1381 IF(p_supp_trade_profile_rec.dpp_attribute9<> FND_API.g_miss_char) THEN
1382 l_supp_trade_profile_rec.dpp_attribute9 := p_supp_trade_profile_rec.dpp_attribute9 ;
1383 END IF ;
1384
1385 IF(p_supp_trade_profile_rec.dpp_attribute10<> FND_API.g_miss_char) THEN
1386 l_supp_trade_profile_rec.dpp_attribute10 := p_supp_trade_profile_rec.dpp_attribute10 ;
1387 END IF ;
1388
1389 IF(p_supp_trade_profile_rec.dpp_attribute11<> FND_API.g_miss_char) THEN
1390 l_supp_trade_profile_rec.dpp_attribute11 := p_supp_trade_profile_rec.dpp_attribute11 ;
1391 END IF ;
1392
1393 IF(p_supp_trade_profile_rec.dpp_attribute12<> FND_API.g_miss_char) THEN
1394 l_supp_trade_profile_rec.dpp_attribute12 := p_supp_trade_profile_rec.dpp_attribute12 ;
1395 END IF ;
1396
1397 IF(p_supp_trade_profile_rec.dpp_attribute13<> FND_API.g_miss_char) THEN
1398 l_supp_trade_profile_rec.dpp_attribute13 := p_supp_trade_profile_rec.dpp_attribute13 ;
1399 END IF ;
1400
1401 IF(p_supp_trade_profile_rec.dpp_attribute14<> FND_API.g_miss_char) THEN
1402 l_supp_trade_profile_rec.dpp_attribute14 := p_supp_trade_profile_rec.dpp_attribute14 ;
1403 END IF ;
1404
1405 IF(p_supp_trade_profile_rec.dpp_attribute15<> FND_API.g_miss_char) THEN
1406 l_supp_trade_profile_rec.dpp_attribute15 := p_supp_trade_profile_rec.dpp_attribute15 ;
1407 END IF ;
1408
1409 IF(p_supp_trade_profile_rec.dpp_attribute16<> FND_API.g_miss_char) THEN
1410 l_supp_trade_profile_rec.dpp_attribute16 := p_supp_trade_profile_rec.dpp_attribute16 ;
1411 END IF ;
1412
1413 IF(p_supp_trade_profile_rec.dpp_attribute17<> FND_API.g_miss_char) THEN
1414 l_supp_trade_profile_rec.dpp_attribute17 := p_supp_trade_profile_rec.dpp_attribute17 ;
1415 END IF ;
1416
1417 IF(p_supp_trade_profile_rec.dpp_attribute18<> FND_API.g_miss_char) THEN
1418 l_supp_trade_profile_rec.dpp_attribute18 := p_supp_trade_profile_rec.dpp_attribute18 ;
1419 END IF ;
1420
1421 IF(p_supp_trade_profile_rec.dpp_attribute19<> FND_API.g_miss_char) THEN
1422 l_supp_trade_profile_rec.dpp_attribute19 := p_supp_trade_profile_rec.dpp_attribute19 ;
1423 END IF ;
1424
1425 IF(p_supp_trade_profile_rec.dpp_attribute20<> FND_API.g_miss_char) THEN
1426 l_supp_trade_profile_rec.dpp_attribute20 := p_supp_trade_profile_rec.dpp_attribute20 ;
1427 END IF ;
1428
1429 IF(p_supp_trade_profile_rec.dpp_attribute21<> FND_API.g_miss_char) THEN
1430 l_supp_trade_profile_rec.dpp_attribute21 := p_supp_trade_profile_rec.dpp_attribute21 ;
1431 END IF ;
1432
1433 IF(p_supp_trade_profile_rec.dpp_attribute22<> FND_API.g_miss_char) THEN
1434 l_supp_trade_profile_rec.dpp_attribute22 := p_supp_trade_profile_rec.dpp_attribute22 ;
1435 END IF ;
1436
1437 IF(p_supp_trade_profile_rec.dpp_attribute23<> FND_API.g_miss_char) THEN
1438 l_supp_trade_profile_rec.dpp_attribute23 := p_supp_trade_profile_rec.dpp_attribute23 ;
1439 END IF ;
1440
1441 IF(p_supp_trade_profile_rec.dpp_attribute24<> FND_API.g_miss_char) THEN
1442 l_supp_trade_profile_rec.dpp_attribute24 := p_supp_trade_profile_rec.dpp_attribute24 ;
1443 END IF ;
1444
1445 IF(p_supp_trade_profile_rec.dpp_attribute25<> FND_API.g_miss_char) THEN
1446 l_supp_trade_profile_rec.dpp_attribute25 := p_supp_trade_profile_rec.dpp_attribute25 ;
1447 END IF ;
1448
1449 IF(p_supp_trade_profile_rec.dpp_attribute26<> FND_API.g_miss_char) THEN
1450 l_supp_trade_profile_rec.dpp_attribute26 := p_supp_trade_profile_rec.dpp_attribute26 ;
1451 END IF ;
1452
1453 IF(p_supp_trade_profile_rec.dpp_attribute27<> FND_API.g_miss_char) THEN
1454 l_supp_trade_profile_rec.dpp_attribute27 := p_supp_trade_profile_rec.dpp_attribute27 ;
1455 END IF ;
1456
1457 IF(p_supp_trade_profile_rec.dpp_attribute28<> FND_API.g_miss_char) THEN
1458 l_supp_trade_profile_rec.dpp_attribute28 := p_supp_trade_profile_rec.dpp_attribute28 ;
1459 END IF ;
1460
1461 IF(p_supp_trade_profile_rec.dpp_attribute29<> FND_API.g_miss_char) THEN
1462 l_supp_trade_profile_rec.dpp_attribute29 := p_supp_trade_profile_rec.dpp_attribute29 ;
1463 END IF ;
1464
1465 IF(p_supp_trade_profile_rec.dpp_attribute30<> FND_API.g_miss_char) THEN
1466 l_supp_trade_profile_rec.dpp_attribute30 := p_supp_trade_profile_rec.dpp_attribute30 ;
1467 END IF ;
1468
1469 --ninarasi fix end for bug 12739578
1470 l_supp_trade_profile_rec.org_id := p_supp_trade_profile_rec.org_id ;
1471
1472
1473 IF(p_supp_trade_profile_rec.security_group_id<> FND_API.g_miss_num) THEN
1474 l_supp_trade_profile_rec.security_group_id := p_supp_trade_profile_rec.security_group_id ;
1475 END IF ;
1476
1477 IF(p_supp_trade_profile_rec.claim_currency_code<> FND_API.g_miss_char) THEN
1478 l_supp_trade_profile_rec.claim_currency_code := p_supp_trade_profile_rec.claim_currency_code ;
1479 END IF ;
1480
1481 IF(p_supp_trade_profile_rec.min_claim_amt<> FND_API.g_miss_num) THEN
1482 l_supp_trade_profile_rec.min_claim_amt := p_supp_trade_profile_rec.min_claim_amt ;
1483 END IF ;
1484
1485 IF(p_supp_trade_profile_rec.min_claim_amt_line_lvl<> FND_API.g_miss_num) THEN
1486 l_supp_trade_profile_rec.min_claim_amt_line_lvl := p_supp_trade_profile_rec.min_claim_amt_line_lvl ;
1487 END IF ;
1488
1489 IF(p_supp_trade_profile_rec.auto_debit<> FND_API.g_miss_char) THEN
1490 l_supp_trade_profile_rec.auto_debit := p_supp_trade_profile_rec.auto_debit ;
1491 END IF ;
1492
1493 IF(p_supp_trade_profile_rec.days_before_claiming_debit<> FND_API.g_miss_num) THEN
1494 l_supp_trade_profile_rec.days_before_claiming_debit := p_supp_trade_profile_rec.days_before_claiming_debit ;
1495 END IF ;
1496
1497 IF(p_supp_trade_profile_rec.ssd_imd_claim_flag <> FND_API.g_miss_char) THEN
1498 l_supp_trade_profile_rec.ssd_imd_claim_flag := p_supp_trade_profile_rec.ssd_imd_claim_flag ;
1499 END IF ;
1500
1501 -- Fix for Bug#10056887
1502 l_supp_trade_profile_rec.supplier_neg_batch_flag := p_supp_trade_profile_rec.supplier_neg_batch_flag;
1503
1504 x_supp_trade_profile_rec := l_supp_trade_profile_rec ;
1505
1506 IF g_debug THEN
1507 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Ends' );
1508 END IF;
1509
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1514 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1515 THEN
1516 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1517 END IF;
1518 FND_MSG_PUB.Count_And_Get (
1519 p_encoded => FND_API.G_FALSE,
1520 p_count => x_msg_count,
1521 p_data => x_msg_data
1522 );
1523 IF g_debug THEN
1524 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
1525 END IF;
1526
1527
1528
1529 END Convert_Supp_Trade_Profile ;
1530
1531
1532 ---------------------------------------------------------------------
1533 -- PROCEDURE
1534 -- Populate_Supp_Trade_Profile
1535 --
1536 -- PURPOSE
1537 -- This procedure Populates the trade profile record with values in DB
1538 --Parameters
1539 -- p_supp_trade_profile_rec - User Trade profile record
1540 -- x_supp_trade_profile_rec - DB Trade profile record
1541 ---------------------------------------------------------------------
1542
1543 PROCEDURE Populate_Supp_Trade_Profile(
1544 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1545 x_return_status OUT NOCOPY VARCHAR2,
1546 x_msg_count OUT NOCOPY NUMBER,
1547 x_msg_data OUT NOCOPY VARCHAR2,
1548 p_supp_trade_profile_rec IN OUT NOCOPY supp_trade_profile_rec_type
1549 )
1550
1551 IS
1552
1553
1554 L_API_NAME CONSTANT VARCHAR2(30) := 'Populate_Supp_Trade_Profile';
1555 CURSOR c_trd_prfl IS
1556 SELECT DECODE(p_supp_trade_profile_rec.object_version_number,FND_API.g_miss_num,object_version_number,p_supp_trade_profile_rec.object_version_number) object_version_number,
1557 DECODE(p_supp_trade_profile_rec.party_id,FND_API.g_miss_num,party_id,p_supp_trade_profile_rec.party_id) party_id,
1558 DECODE(p_supp_trade_profile_rec.site_use_id,FND_API.g_miss_num,site_use_id,p_supp_trade_profile_rec.site_use_id) site_use_id,
1559 DECODE(p_supp_trade_profile_rec.cust_account_id,FND_API.g_miss_num,cust_account_id,p_supp_trade_profile_rec.cust_account_id) cust_account_id,
1560 DECODE(p_supp_trade_profile_rec.cust_acct_site_id,FND_API.g_miss_num,cust_acct_site_id,p_supp_trade_profile_rec.cust_acct_site_id) cust_acct_site_id,
1561 creation_date ,
1562 supplier_id,
1563 supplier_site_id,
1564 DECODE(p_supp_trade_profile_rec.attribute_category,FND_API.g_miss_char,attribute_category,p_supp_trade_profile_rec.attribute_category) attribute_category,
1565 DECODE(p_supp_trade_profile_rec.attribute1,FND_API.g_miss_char,attribute1,p_supp_trade_profile_rec.attribute1) attribute1,
1566 DECODE(p_supp_trade_profile_rec.attribute2,FND_API.g_miss_char,attribute2,p_supp_trade_profile_rec.attribute2) attribute2,
1567 DECODE(p_supp_trade_profile_rec.attribute3,FND_API.g_miss_char,attribute3,p_supp_trade_profile_rec.attribute3) attribute3,
1568 DECODE(p_supp_trade_profile_rec.attribute4,FND_API.g_miss_char,attribute4,p_supp_trade_profile_rec.attribute4) attribute4,
1569 DECODE(p_supp_trade_profile_rec.attribute5,FND_API.g_miss_char,attribute5,p_supp_trade_profile_rec.attribute5) attribute5,
1570 DECODE(p_supp_trade_profile_rec.attribute6,FND_API.g_miss_char,attribute6,p_supp_trade_profile_rec.attribute6) attribute6,
1571 DECODE(p_supp_trade_profile_rec.attribute7,FND_API.g_miss_char,attribute7,p_supp_trade_profile_rec.attribute7) attribute7,
1572 DECODE(p_supp_trade_profile_rec.attribute8,FND_API.g_miss_char,attribute8,p_supp_trade_profile_rec.attribute8) attribute8,
1573 DECODE(p_supp_trade_profile_rec.attribute9,FND_API.g_miss_char,attribute9,p_supp_trade_profile_rec.attribute9) attribute9,
1574 DECODE(p_supp_trade_profile_rec.attribute10,FND_API.g_miss_char,attribute10,p_supp_trade_profile_rec.attribute10) attribute10,
1575 DECODE(p_supp_trade_profile_rec.attribute11,FND_API.g_miss_char,attribute11,p_supp_trade_profile_rec.attribute11) attribute11,
1576 DECODE(p_supp_trade_profile_rec.attribute12,FND_API.g_miss_char,attribute12,p_supp_trade_profile_rec.attribute12) attribute12,
1577 DECODE(p_supp_trade_profile_rec.attribute13,FND_API.g_miss_char,attribute13,p_supp_trade_profile_rec.attribute13) attribute13,
1578 DECODE(p_supp_trade_profile_rec.attribute14,FND_API.g_miss_char,attribute14,p_supp_trade_profile_rec.attribute14) attribute14,
1579 DECODE(p_supp_trade_profile_rec.attribute15,FND_API.g_miss_char,attribute15,p_supp_trade_profile_rec.attribute15) attribute15,
1580 DECODE(p_supp_trade_profile_rec.attribute16,FND_API.g_miss_char,attribute16,p_supp_trade_profile_rec.attribute16) attribute16,
1581 DECODE(p_supp_trade_profile_rec.attribute17,FND_API.g_miss_char,attribute17,p_supp_trade_profile_rec.attribute17) attribute17,
1582 DECODE(p_supp_trade_profile_rec.attribute18,FND_API.g_miss_char,attribute18,p_supp_trade_profile_rec.attribute18) attribute18,
1583 DECODE(p_supp_trade_profile_rec.attribute19,FND_API.g_miss_char,attribute19,p_supp_trade_profile_rec.attribute19) attribute19,
1584 DECODE(p_supp_trade_profile_rec.attribute20,FND_API.g_miss_char,attribute20,p_supp_trade_profile_rec.attribute20) attribute20,
1585 DECODE(p_supp_trade_profile_rec.attribute21,FND_API.g_miss_char,attribute21,p_supp_trade_profile_rec.attribute21) attribute21,
1586 DECODE(p_supp_trade_profile_rec.attribute22,FND_API.g_miss_char,attribute22,p_supp_trade_profile_rec.attribute22) attribute22,
1587 DECODE(p_supp_trade_profile_rec.attribute23,FND_API.g_miss_char,attribute23,p_supp_trade_profile_rec.attribute23) attribute23,
1588 DECODE(p_supp_trade_profile_rec.attribute24,FND_API.g_miss_char,attribute24,p_supp_trade_profile_rec.attribute24) attribute24,
1589 DECODE(p_supp_trade_profile_rec.attribute25,FND_API.g_miss_char,attribute25,p_supp_trade_profile_rec.attribute25) attribute25,
1590 DECODE(p_supp_trade_profile_rec.attribute26,FND_API.g_miss_char,attribute26,p_supp_trade_profile_rec.attribute26) attribute26,
1591 DECODE(p_supp_trade_profile_rec.attribute27,FND_API.g_miss_char,attribute27,p_supp_trade_profile_rec.attribute27) attribute27,
1592 DECODE(p_supp_trade_profile_rec.attribute28,FND_API.g_miss_char,attribute28,p_supp_trade_profile_rec.attribute28) attribute28,
1593 DECODE(p_supp_trade_profile_rec.attribute29,FND_API.g_miss_char,attribute29,p_supp_trade_profile_rec.attribute29) attribute29,
1594 DECODE(p_supp_trade_profile_rec.attribute30,FND_API.g_miss_char,attribute30,p_supp_trade_profile_rec.attribute30) attribute30,
1595 org_id ,
1596 DECODE(p_supp_trade_profile_rec.pre_approval_flag ,FND_API.g_miss_char,pre_approval_flag,p_supp_trade_profile_rec.pre_approval_flag) pre_approval_flag,
1597 DECODE(p_supp_trade_profile_rec.approval_communication ,FND_API.g_miss_char,approval_communication,p_supp_trade_profile_rec.approval_communication) approval_communication,
1598 DECODE(p_supp_trade_profile_rec.gl_contra_liability_acct ,FND_API.g_miss_num, gl_contra_liability_acct,p_supp_trade_profile_rec.gl_contra_liability_acct) gl_contra_liability_acct,
1599 DECODE(p_supp_trade_profile_rec.gl_cost_adjustment_acct ,FND_API.g_miss_num,gl_cost_adjustment_acct,p_supp_trade_profile_rec.gl_cost_adjustment_acct) gl_cost_adjustment_acct,
1600 DECODE(p_supp_trade_profile_rec.default_days_covered ,FND_API.g_miss_num,default_days_covered,p_supp_trade_profile_rec.default_days_covered) default_days_covered,
1601 DECODE(p_supp_trade_profile_rec.create_claim_price_increase ,FND_API.g_miss_char ,create_claim_price_increase,p_supp_trade_profile_rec.create_claim_price_increase) create_claim_price_increase,
1602 DECODE(p_supp_trade_profile_rec.skip_approval_flag , FND_API.g_miss_char,skip_approval_flag,p_supp_trade_profile_rec.skip_approval_flag) skip_approval_flag,
1603 DECODE(p_supp_trade_profile_rec.skip_adjustment_flag ,FND_API.g_miss_char ,skip_adjustment_flag, p_supp_trade_profile_rec.skip_adjustment_flag) skip_adjustment_flag,
1604 DECODE(p_supp_trade_profile_rec.settlement_method_supplier_inc,FND_API.g_miss_char,settlement_method_supplier_inc,p_supp_trade_profile_rec.settlement_method_supplier_inc) settlement_method_supplier_inc,
1605 DECODE(p_supp_trade_profile_rec.settlement_method_supplier_dec,FND_API.g_miss_char,settlement_method_supplier_dec,p_supp_trade_profile_rec.settlement_method_supplier_dec) settlement_method_supplier_dec,
1606 DECODE(p_supp_trade_profile_rec.settlement_method_customer,FND_API.g_miss_char,settlement_method_customer,p_supp_trade_profile_rec.settlement_method_customer) settlement_method_customer,
1607 DECODE(p_supp_trade_profile_rec.authorization_period ,FND_API.g_miss_num,authorization_period,p_supp_trade_profile_rec.authorization_period) authorization_period ,
1608 DECODE(p_supp_trade_profile_rec.grace_days ,FND_API.g_miss_num,grace_days,p_supp_trade_profile_rec.grace_days) grace_days,
1609 DECODE(p_supp_trade_profile_rec.allow_qty_increase ,FND_API.g_miss_char,allow_qty_increase,p_supp_trade_profile_rec.allow_qty_increase) allow_qty_increase,
1610 DECODE(p_supp_trade_profile_rec.qty_increase_tolerance ,FND_API.g_miss_num,qty_increase_tolerance,p_supp_trade_profile_rec.qty_increase_tolerance) qty_increase_tolerance,
1611 DECODE(p_supp_trade_profile_rec.request_communication ,FND_API.g_miss_char,request_communication,p_supp_trade_profile_rec.request_communication) request_communication,
1612 DECODE(p_supp_trade_profile_rec.claim_communication ,FND_API.g_miss_char,claim_communication,p_supp_trade_profile_rec.claim_communication) claim_communication,
1613 DECODE(p_supp_trade_profile_rec.claim_frequency ,FND_API.g_miss_num,claim_frequency,p_supp_trade_profile_rec.claim_frequency) claim_frequency,
1614 DECODE(p_supp_trade_profile_rec.claim_frequency_unit ,FND_API.g_miss_char,claim_frequency_unit,p_supp_trade_profile_rec.claim_frequency_unit) claim_frequency_unit,
1615 DECODE(p_supp_trade_profile_rec.claim_computation_basis ,FND_API.g_miss_num, claim_computation_basis, p_supp_trade_profile_rec.claim_computation_basis) claim_computation_basis,
1616 DECODE(p_supp_trade_profile_rec.claim_currency_code ,FND_API.g_miss_char, claim_currency_code,p_supp_trade_profile_rec.claim_currency_code) claim_currency_code,
1617 DECODE(p_supp_trade_profile_rec.min_claim_amt ,FND_API.g_miss_num ,min_claim_amt,p_supp_trade_profile_rec.min_claim_amt) min_claim_amt,
1618 DECODE(p_supp_trade_profile_rec.min_claim_amt_line_lvl ,FND_API.g_miss_num,min_claim_amt_line_lvl,p_supp_trade_profile_rec.min_claim_amt_line_lvl) min_claim_amt_line_lvl,
1619 DECODE(p_supp_trade_profile_rec.auto_debit ,FND_API.g_miss_char, auto_debit,p_supp_trade_profile_rec.auto_debit) auto_debit,
1620 DECODE(p_supp_trade_profile_rec.days_before_claiming_debit , FND_API.g_miss_num, days_before_claiming_debit,p_supp_trade_profile_rec.days_before_claiming_debit) days_before_claiming_debit,
1621 DECODE(p_supp_trade_profile_rec.ssd_imd_claim_flag ,FND_API.g_miss_char, ssd_imd_claim_flag,p_supp_trade_profile_rec.ssd_imd_claim_flag) ssd_imd_claim_flag,
1622 security_group_id
1623 FROM ozf_supp_trd_prfls_all
1624 WHERE supp_trade_profile_id = p_supp_trade_profile_rec.supp_trade_profile_id ;
1625
1626
1627 l_ref_supp_trade_profile_rec c_trd_prfl%ROWTYPE;
1628
1629
1630
1631
1632
1633 BEGIN
1634
1635 IF g_debug THEN
1636 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts' );
1637 END IF;
1638
1639 OPEN c_trd_prfl ;
1640 FETCH c_trd_prfl INTO l_ref_supp_trade_profile_rec;
1641 IF ( c_trd_prfl%NOTFOUND) THEN
1642 IF g_debug THEN
1643 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid trade profile Id' );
1644 END IF;
1645 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1646 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_TRD_PRF_ID');
1647 FND_MSG_PUB.add;
1648 END IF;
1649 RAISE FND_API.G_EXC_ERROR;
1650 END IF;
1651 CLOSE c_trd_prfl;
1652
1653
1654
1655 -----------------------------------------------------------------
1656 --- Populate the null data
1657 -----------------------------------------------------------------
1658
1659 p_supp_trade_profile_rec.supp_trade_profile_id := p_supp_trade_profile_rec.supp_trade_profile_id ;
1660 p_supp_trade_profile_rec.object_version_number := l_ref_supp_trade_profile_rec.object_version_number ;
1661 p_supp_trade_profile_rec.last_update_date := sysdate ;
1662 p_supp_trade_profile_rec.last_updated_by := p_supp_trade_profile_rec.last_updated_by ;
1663 p_supp_trade_profile_rec.supplier_id := l_ref_supp_trade_profile_rec.supplier_id ;
1664 p_supp_trade_profile_rec.supplier_site_id := l_ref_supp_trade_profile_rec.supplier_site_id ;
1665 p_supp_trade_profile_rec.party_id := l_ref_supp_trade_profile_rec.party_id ;
1666 p_supp_trade_profile_rec.cust_account_id := l_ref_supp_trade_profile_rec.cust_account_id ;
1667 p_supp_trade_profile_rec.cust_acct_site_id := l_ref_supp_trade_profile_rec.cust_acct_site_id ;
1668 p_supp_trade_profile_rec.site_use_id := l_ref_supp_trade_profile_rec.site_use_id ;
1669 p_supp_trade_profile_rec.pre_approval_flag := l_ref_supp_trade_profile_rec.pre_approval_flag ;
1670 p_supp_trade_profile_rec.approval_communication := l_ref_supp_trade_profile_rec.approval_communication ;
1671 p_supp_trade_profile_rec.gl_contra_liability_acct := l_ref_supp_trade_profile_rec.gl_contra_liability_acct ;
1672 p_supp_trade_profile_rec.gl_cost_adjustment_acct := l_ref_supp_trade_profile_rec.gl_cost_adjustment_acct ;
1673 p_supp_trade_profile_rec.default_days_covered := l_ref_supp_trade_profile_rec.default_days_covered ;
1674 p_supp_trade_profile_rec.create_claim_price_increase := l_ref_supp_trade_profile_rec.create_claim_price_increase;
1675 p_supp_trade_profile_rec.skip_approval_flag := l_ref_supp_trade_profile_rec.skip_approval_flag ;
1676 p_supp_trade_profile_rec.skip_adjustment_flag := l_ref_supp_trade_profile_rec.skip_adjustment_flag ;
1677 p_supp_trade_profile_rec.settlement_method_supplier_dec := l_ref_supp_trade_profile_rec.settlement_method_supplier_dec;
1678 p_supp_trade_profile_rec.settlement_method_supplier_inc := l_ref_supp_trade_profile_rec.settlement_method_supplier_inc;
1679 p_supp_trade_profile_rec.settlement_method_customer := l_ref_supp_trade_profile_rec.settlement_method_customer ;
1680 p_supp_trade_profile_rec.authorization_period := l_ref_supp_trade_profile_rec.authorization_period ;
1681 p_supp_trade_profile_rec.grace_days := l_ref_supp_trade_profile_rec.grace_days ;
1682 p_supp_trade_profile_rec.allow_qty_increase := l_ref_supp_trade_profile_rec.allow_qty_increase ;
1683 p_supp_trade_profile_rec.qty_increase_tolerance := l_ref_supp_trade_profile_rec.qty_increase_tolerance ;
1684 p_supp_trade_profile_rec.request_communication := l_ref_supp_trade_profile_rec.request_communication ;
1685 p_supp_trade_profile_rec.claim_communication := l_ref_supp_trade_profile_rec.claim_communication ;
1686 p_supp_trade_profile_rec.claim_frequency := l_ref_supp_trade_profile_rec.claim_frequency ;
1687 p_supp_trade_profile_rec.claim_frequency_unit := l_ref_supp_trade_profile_rec.claim_frequency_unit ;
1688 p_supp_trade_profile_rec.claim_computation_basis := l_ref_supp_trade_profile_rec.claim_computation_basis ;
1689 p_supp_trade_profile_rec.attribute_category := l_ref_supp_trade_profile_rec.attribute_category ;
1690 p_supp_trade_profile_rec.attribute1 := l_ref_supp_trade_profile_rec.attribute1 ;
1691 p_supp_trade_profile_rec.attribute2 := l_ref_supp_trade_profile_rec.attribute2 ;
1692 p_supp_trade_profile_rec.attribute3 := l_ref_supp_trade_profile_rec.attribute3 ;
1693 p_supp_trade_profile_rec.attribute4 := l_ref_supp_trade_profile_rec.attribute4 ;
1694 p_supp_trade_profile_rec.attribute5 := l_ref_supp_trade_profile_rec.attribute5 ;
1695 p_supp_trade_profile_rec.attribute6 := l_ref_supp_trade_profile_rec.attribute6 ;
1696 p_supp_trade_profile_rec.attribute7 := l_ref_supp_trade_profile_rec.attribute7 ;
1697 p_supp_trade_profile_rec.attribute8 := l_ref_supp_trade_profile_rec.attribute8 ;
1698 p_supp_trade_profile_rec.attribute9 := l_ref_supp_trade_profile_rec.attribute9 ;
1699 p_supp_trade_profile_rec.attribute10 := l_ref_supp_trade_profile_rec.attribute10 ;
1700 p_supp_trade_profile_rec.attribute11 := l_ref_supp_trade_profile_rec.attribute11 ;
1701 p_supp_trade_profile_rec.attribute12 := l_ref_supp_trade_profile_rec.attribute12 ;
1702 p_supp_trade_profile_rec.attribute13 := l_ref_supp_trade_profile_rec.attribute13 ;
1703 p_supp_trade_profile_rec.attribute14 := l_ref_supp_trade_profile_rec.attribute14 ;
1704 p_supp_trade_profile_rec.attribute15 := l_ref_supp_trade_profile_rec.attribute15 ;
1705 p_supp_trade_profile_rec.attribute16 := l_ref_supp_trade_profile_rec.attribute16 ;
1706 p_supp_trade_profile_rec.attribute17 := l_ref_supp_trade_profile_rec.attribute17 ;
1707 p_supp_trade_profile_rec.attribute18 := l_ref_supp_trade_profile_rec.attribute18 ;
1708 p_supp_trade_profile_rec.attribute19 := l_ref_supp_trade_profile_rec.attribute19 ;
1709 p_supp_trade_profile_rec.attribute20 := l_ref_supp_trade_profile_rec.attribute20 ;
1710 p_supp_trade_profile_rec.attribute21 := l_ref_supp_trade_profile_rec.attribute21 ;
1711 p_supp_trade_profile_rec.attribute22 := l_ref_supp_trade_profile_rec.attribute22 ;
1712 p_supp_trade_profile_rec.attribute23 := l_ref_supp_trade_profile_rec.attribute23 ;
1713 p_supp_trade_profile_rec.attribute24 := l_ref_supp_trade_profile_rec.attribute24 ;
1714 p_supp_trade_profile_rec.attribute25 := l_ref_supp_trade_profile_rec.attribute25 ;
1715 p_supp_trade_profile_rec.attribute26 := l_ref_supp_trade_profile_rec.attribute26 ;
1716 p_supp_trade_profile_rec.attribute27 := l_ref_supp_trade_profile_rec.attribute27 ;
1717 p_supp_trade_profile_rec.attribute28 := l_ref_supp_trade_profile_rec.attribute28 ;
1718 p_supp_trade_profile_rec.attribute29 := l_ref_supp_trade_profile_rec.attribute29 ;
1719 p_supp_trade_profile_rec.attribute30 := l_ref_supp_trade_profile_rec.attribute30 ;
1720 p_supp_trade_profile_rec.org_id := l_ref_supp_trade_profile_rec.org_id ;
1721 p_supp_trade_profile_rec.security_group_id := l_ref_supp_trade_profile_rec.security_group_id ;
1722 p_supp_trade_profile_rec.claim_currency_code := l_ref_supp_trade_profile_rec.claim_currency_code ;
1723 p_supp_trade_profile_rec.min_claim_amt := l_ref_supp_trade_profile_rec.min_claim_amt ;
1724 p_supp_trade_profile_rec.min_claim_amt_line_lvl := l_ref_supp_trade_profile_rec.min_claim_amt_line_lvl ;
1725 p_supp_trade_profile_rec.auto_debit := l_ref_supp_trade_profile_rec.auto_debit ;
1726 p_supp_trade_profile_rec.days_before_claiming_debit := l_ref_supp_trade_profile_rec.days_before_claiming_debit ;
1727 -- Fix for Bug 10056887
1728 p_supp_trade_profile_rec.supplier_neg_batch_flag := p_supp_trade_profile_rec.supplier_neg_batch_flag;
1729 p_supp_trade_profile_rec.ssd_imd_claim_flag := p_supp_trade_profile_rec.ssd_imd_claim_flag;
1730 IF g_debug THEN
1731 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Ends' );
1732 END IF;
1733
1734
1735 EXCEPTION
1736
1737 WHEN OTHERS THEN
1738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1739 IF g_debug THEN
1740 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
1741 END IF;
1742
1743 END Populate_Supp_Trade_Profile ;
1744
1745
1746
1747
1748 ---------------------------------------------------------------------
1749 -- PROCEDURE
1750 -- Create_Supp_Trade_Profile
1751 --
1752 -- PURPOSE
1753 -- Public API for creating Supplier Trade Profile
1754 ---------------------------------------------------------------------
1755
1756
1757 PROCEDURE Create_Supp_Trade_Profile(
1758 p_api_version_number IN NUMBER,
1759 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1760 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1761 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1762 x_return_status OUT NOCOPY VARCHAR2,
1763 x_msg_count OUT NOCOPY NUMBER,
1764 x_msg_data OUT NOCOPY VARCHAR2,
1765 p_supp_trade_profile_rec IN supp_trade_profile_rec_type ,
1766 p_code_conversion_rec_tbl IN code_conversion_tbl_type,
1767 p_price_protection_set_tbl IN process_setup_tbl_type,
1768 x_supp_trade_profile_id OUT NOCOPY NUMBER,
1769 X_created_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type,
1770 X_created_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type
1771 )
1772 IS
1773
1774 --Code modified for bug10634985
1775 CURSOR c_get_cust_dtl IS
1776 SELECT cust_acct.party_id,
1777 acct_site.cust_acct_site_id
1778 FROM hz_cust_acct_sites_all acct_site ,
1779 hz_cust_site_uses_all site,
1780 hz_cust_accounts cust_acct
1781 WHERE site.site_use_code = 'BILL_TO'
1782 AND acct_site.status='A'
1783 AND site.status='A'
1784 AND cust_acct.status='A'
1785 AND site.cust_acct_site_id = acct_site.cust_acct_site_id
1786 AND acct_site.cust_account_id=cust_acct.cust_account_id
1787 AND acct_site.cust_account_id = p_supp_trade_profile_rec.cust_account_id
1788 AND site.site_use_id = p_supp_trade_profile_rec.site_use_id;
1789
1790 /* SELECT acct_site.cust_account_id ,
1791 acct_site.cust_acct_site_id
1792 FROM hz_cust_acct_sites_all acct_site ,
1793 hz_party_sites party_site ,
1794 hz_locations loc ,
1795 hz_cust_site_uses_all site ,
1796 hz_parties party ,
1797 hz_cust_accounts cust_acct
1798 WHERE site.site_use_code = 'BILL_TO'
1799 AND site.cust_acct_site_id = acct_site.cust_acct_site_id
1800 AND acct_site.party_site_id= party_site.party_site_id
1801 AND party_site.location_id = loc.location_id
1802 AND acct_site.status='A'
1803 AND acct_site.cust_account_id=cust_acct.cust_account_id
1804 AND cust_acct.party_id=party.party_id
1805 AND cust_acct.status='A'
1806 AND site.status='A'
1807 AND cust_acct.party_id = p_supp_trade_profile_rec.cust_account_id
1808 AND site.site_use_id = p_supp_trade_profile_rec.site_use_id ; */
1809
1810
1811
1812
1813 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Supp_Trade_Profile';
1814 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1815 l_return_status_full VARCHAR2(1);
1816 l_object_version_number NUMBER := 1 ;
1817 l_org_id NUMBER ;
1818
1819 l_supp_prf_count NUMBER := 0 ;
1820 l_supp_trade_profile_rec OZF_SUPP_TRADE_PROFILE_PUB.supp_trade_profile_rec_type ;
1821 l_supp_site_count NUMBER := 0 ;
1822
1823 l_pvt_supp_rec OZF_SUPP_TRADE_PROFILE_PVT.supp_trade_profile_rec_type ;
1824 l_supp_site_comb_count NUMBER := 0 ;
1825
1826 l_ref_cust_dtl_rec c_get_cust_dtl%ROWTYPE ;
1827
1828 l_pro_ver_tbl OZF_PROCESS_SETUP_PVT.process_setup_tbl_type ;
1829
1830 l_code_conversion_rec supp_code_conversion_rec_type ;
1831 l_code_conversion_rec_tbl code_conversion_tbl_type := code_conversion_tbl_type() ;
1832
1833 l_upd_code_con_tbl OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ;
1834 l_del_code_con_tbl OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ;
1835
1836 l_exec_error VARCHAR2(1) := 'N' ;
1837
1838
1839 BEGIN
1840
1841
1842 IF g_debug THEN
1843 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||'Starts');
1844 END IF;
1845
1846
1847
1848 -- Standard call to check for call compatibility.
1849 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1850 p_api_version_number,
1851 l_api_name,
1852 G_PKG_NAME)
1853 THEN
1854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1855 IF g_debug THEN
1856 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||'In compatible call');
1857 END IF;
1858 END IF;
1859
1860 -- Initialize message list if p_init_msg_list is set to TRUE.
1861 IF FND_API.to_Boolean( p_init_msg_list )
1862 THEN
1863 FND_MSG_PUB.initialize;
1864 END IF;
1865
1866
1867
1868 -------------------------------------------------
1869 -- Start : Defaulting data
1870 -------------------------------------------------
1871
1872 l_supp_trade_profile_rec := p_supp_trade_profile_rec ;
1873
1874 IF ( l_supp_trade_profile_rec.pre_approval_flag IS NULL
1875 OR
1876 l_supp_trade_profile_rec.pre_approval_flag NOT IN ('Y','N')) THEN
1877
1878 l_supp_trade_profile_rec.pre_approval_flag := 'N';
1879 END IF ;
1880
1881
1882 IF ( l_supp_trade_profile_rec.create_claim_price_increase IS NULL
1883 OR
1884 l_supp_trade_profile_rec.create_claim_price_increase NOT IN ('Y','N')) THEN
1885
1886 l_supp_trade_profile_rec.create_claim_price_increase := 'N';
1887 END IF ;
1888
1889 IF ( l_supp_trade_profile_rec.skip_approval_flag IS NULL
1890 OR
1891 l_supp_trade_profile_rec.skip_approval_flag NOT IN ('Y','N')) THEN
1892
1893 l_supp_trade_profile_rec.skip_approval_flag := 'N';
1894 END IF ;
1895
1896
1897 IF ( l_supp_trade_profile_rec.skip_adjustment_flag IS NULL
1898 OR
1899 l_supp_trade_profile_rec.skip_adjustment_flag NOT IN ('Y','N')) THEN
1900
1901 l_supp_trade_profile_rec.skip_adjustment_flag := 'N';
1902 END IF ;
1903
1904
1905 IF ( l_supp_trade_profile_rec.allow_qty_increase IS NULL
1906 OR
1907 l_supp_trade_profile_rec.allow_qty_increase NOT IN ('Y','N')) THEN
1908
1909 l_supp_trade_profile_rec.allow_qty_increase := 'N';
1910 END IF ;
1911
1912
1913 IF ( l_supp_trade_profile_rec.auto_debit IS NULL
1914 OR
1915 l_supp_trade_profile_rec.auto_debit NOT IN ('Y','N')) THEN
1916
1917 l_supp_trade_profile_rec.auto_debit := 'N';
1918 END IF ;
1919
1920 IF ( l_supp_trade_profile_rec.ssd_imd_claim_flag IS NULL
1921 OR
1922 l_supp_trade_profile_rec.ssd_imd_claim_flag NOT IN ('Y','N')) THEN
1923
1924 l_supp_trade_profile_rec.ssd_imd_claim_flag := 'N';
1925 END IF ;
1926
1927 IF g_debug THEN
1928 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||'Data initialized');
1929 END IF;
1930
1931 -------------------------------------------------
1932 -- End : Defaulting data
1933 -------------------------------------------------
1934
1935
1936 -------------------------------------------------
1937 -- Start data validation
1938 -------------------------------------------------
1939
1940
1941 -- Validate supplier_id+supplier_site_id
1942 SELECT ap.vendor_id INTO l_supp_site_count
1943 FROM ap_suppliers ap,
1944 ap_supplier_sites_all aps,
1945 po_lookup_codes plc,
1946 hz_party_sites hps,
1947 hz_locations hzl,
1948 fnd_territories_vl fndt
1949 WHERE aps.party_site_id = hps.party_site_id
1950 AND hps.location_id = hzl.location_id
1951 AND nvl(hps.end_date_active, sysdate) >= sysdate
1952 AND hzl.country = fndt.territory_code
1953 AND aps.vendor_id = ap.vendor_id
1954 AND ap.vendor_type_lookup_code = plc.lookup_code (+)
1955 AND plc.lookup_type (+) = 'VENDOR TYPE'
1956 AND nvl(aps.rfq_only_site_flag, 'N') ='N'
1957 AND NVL(aps.inactive_date, SYSDATE +1) > SYSDATE
1958 AND aps.vendor_site_id = p_supp_trade_profile_rec.supplier_site_id
1959 AND aps.org_id = p_supp_trade_profile_rec.org_id ;
1960
1961
1962 l_supp_trade_profile_rec.supplier_id := l_supp_site_count ;
1963
1964
1965
1966 IF ( (p_supp_trade_profile_rec.supplier_id IS NULL OR p_supp_trade_profile_rec.supplier_id = FND_API.g_miss_num)
1967 OR
1968 l_supp_site_count IS NULL
1969 ) THEN
1970
1971 IF g_debug THEN
1972 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||'Invalid Supplier Site Id');
1973 END IF;
1974
1975 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1976 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_SITE_ID');
1977 FND_MSG_PUB.add;
1978 END IF;
1979
1980 RAISE FND_API.G_EXC_ERROR ;
1981 END IF ;
1982
1983
1984
1985 -- Existence checking for Supplier + Supplier site combination
1986 SELECT COUNT(*) INTO l_supp_site_comb_count
1987 FROM ozf_supp_trd_prfls_all
1988 WHERE supplier_id = p_supp_trade_profile_rec.supplier_id
1989 AND supplier_site_id = p_supp_trade_profile_rec.supplier_site_id ;
1990
1991 IF ( l_supp_site_comb_count<>0 ) THEN
1992
1993 IF g_debug THEN
1994 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Record for supplier+supplier site already exixts');
1995 END IF;
1996
1997 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1998 FND_MESSAGE.set_name('OZF', 'OZF_SUPP_TRADE_PROFILE_DUPLIC');
1999 FND_MSG_PUB.add;
2000 END IF;
2001
2002 RAISE FND_API.G_EXC_ERROR ;
2003
2004 END IF ;
2005
2006
2007
2008 -- Validate and populate the customer details
2009
2010 IF ((p_supp_trade_profile_rec.cust_account_id <> FND_API.g_miss_num AND p_supp_trade_profile_rec.cust_account_id IS NOT NULL )
2011 AND (p_supp_trade_profile_rec.site_use_id <> FND_API.g_miss_num AND p_supp_trade_profile_rec.site_use_id IS NOT NULL)) THEN
2012
2013
2014 OPEN c_get_cust_dtl ;
2015 FETCH c_get_cust_dtl INTO l_ref_cust_dtl_rec;
2016 IF ( c_get_cust_dtl%NOTFOUND) THEN
2017
2018 IF g_debug THEN
2019 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid customer');
2020 END IF;
2021
2022 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2023 FND_MESSAGE.set_name('OZF', 'OZF_SELECT_CUSTOMER_NAME');
2024 FND_MSG_PUB.add;
2025 END IF;
2026
2027 RAISE FND_API.G_EXC_ERROR;
2028 ELSE
2029 l_supp_trade_profile_rec.party_id := l_ref_cust_dtl_rec.party_id ; --Code modified for bug10634985
2030 l_supp_trade_profile_rec.cust_acct_site_id := l_ref_cust_dtl_rec.cust_acct_site_id ;
2031
2032 END IF ;
2033 CLOSE c_get_cust_dtl ;
2034
2035 ELSE
2036 IF g_debug THEN
2037 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid customer');
2038 END IF;
2039
2040 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2041 FND_MESSAGE.set_name('OZF', 'OZF_SELECT_CUSTOMER_NAME');
2042 FND_MSG_PUB.add;
2043 END IF;
2044
2045 RAISE FND_API.G_EXC_ERROR;
2046
2047 END IF ;
2048
2049
2050
2051
2052 -- Common validations for trade profile record
2053 Validate_Supp_Trade_Profile( p_init_msg_list => p_init_msg_list,
2054 x_return_status => x_return_status,
2055 x_msg_count => x_msg_count,
2056 x_msg_data => x_msg_data,
2057 p_supp_trade_profile_rec => l_supp_trade_profile_rec ) ;
2058
2059
2060
2061
2062
2063 IF x_return_status = FND_API.g_ret_sts_error THEN
2064
2065 IF g_debug THEN
2066 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Validation failed');
2067 END IF;
2068
2069 RAISE FND_API.G_EXC_ERROR;
2070 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2071 IF g_debug THEN
2072 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Validation failed');
2073 END IF;
2074
2075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2076 END IF;
2077
2078
2079
2080 -------------------------------------------------
2081 -- End data validation
2082 -------------------------------------------------
2083
2084 -------------------------------------------------
2085 -- Convert the public record to private
2086 -------------------------------------------------
2087
2088 Convert_Supp_Trade_Profile(
2089 p_init_msg_list => p_init_msg_list,
2090 x_return_status => x_return_status,
2091 x_msg_count => x_msg_count,
2092 x_msg_data => x_msg_data,
2093 p_supp_trade_profile_rec => l_supp_trade_profile_rec,
2094 x_supp_trade_profile_rec => l_pvt_supp_rec,
2095 p_action => 'C');
2096
2097
2098
2099
2100
2101 IF x_return_status = FND_API.g_ret_sts_error THEN
2102
2103 IF g_debug THEN
2104 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Conversion failed');
2105 END IF;
2106
2107 RAISE FND_API.G_EXC_ERROR;
2108 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2109
2110 IF g_debug THEN
2111 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Conversion failed');
2112 END IF;
2113
2114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2115 END IF;
2116
2117
2118 -------------------------------------------------
2119 -- Call the private API
2120 -------------------------------------------------
2121
2122
2123 OZF_SUPP_TRADE_PROFILE_PVT.Create_Supp_Trade_Profile(
2124 p_api_version_number => p_api_version_number,
2125 p_init_msg_list => p_init_msg_list,
2126 p_commit => FND_API.G_FALSE,
2127 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2128 x_return_status => x_return_status,
2129 x_msg_count => x_msg_count,
2130 x_msg_data => x_msg_data,
2131 p_supp_trade_profile_rec => l_pvt_supp_rec,
2132 x_supp_trade_profile_id => x_supp_trade_profile_id) ;
2133
2134
2135
2136 IF x_return_status = FND_API.g_ret_sts_error THEN
2137
2138 IF g_debug THEN
2139 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private API call failed');
2140 END IF;
2141
2142 RAISE FND_API.G_EXC_ERROR;
2143
2144 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2145
2146 IF g_debug THEN
2147 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private API call failed');
2148 END IF;
2149
2150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2151
2152 ELSE
2153 IF (p_commit = FND_API.G_TRUE) THEN
2154
2155 IF g_debug THEN
2156 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private API Successful');
2157 END IF;
2158
2159 IF(p_commit = FND_API.G_TRUE) THEN
2160 COMMIT ;
2161 END IF ;
2162 END IF ;
2163 END IF;
2164
2165
2166 -------------------------------------------------
2167 -- Process code conversion
2168 -------------------------------------------------
2169 IF (p_code_conversion_rec_tbl IS NOT NULL ) THEN
2170
2171 FOR i in 1 .. p_code_conversion_rec_tbl.COUNT
2172 LOOP
2173
2174 l_code_conversion_rec := p_code_conversion_rec_tbl(i);
2175 l_code_conversion_rec.SUPP_TRADE_PROFILE_ID := x_supp_trade_profile_id;
2176
2177 l_code_conversion_rec_tbl.extend;
2178 l_code_conversion_rec_tbl(l_code_conversion_rec_tbl.COUNT) := l_code_conversion_rec ;
2179
2180 END LOOP ;
2181
2182 IF g_debug THEN
2183 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling process code conversion');
2184 END IF;
2185
2186 Process_code_conversion(
2187 P_Api_Version_Number => p_api_version_number,
2188 P_Init_Msg_List => p_init_msg_list,
2189 P_Commit => FND_API.G_FALSE,
2190 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2191 X_Return_Status => x_return_status,
2192 X_Msg_Count => x_msg_count,
2193 X_Msg_Data => x_msg_data,
2194 p_code_conversion_tbl => l_code_conversion_rec_tbl ,
2195 X_created_code_con_tbl => X_created_codes_tbl ,
2196 X_updated_code_con_tbl => l_upd_code_con_tbl ,
2197 X_deleted_code_con_tbl => l_del_code_con_tbl) ;
2198
2199
2200
2201 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2202 l_exec_error := 'Y' ;
2203 END IF ;
2204 END IF ;
2205 -------------------------------------------------
2206 -- Process price protection
2207 -------------------------------------------------
2208
2209 IF(p_price_protection_set_tbl IS NOT NULL ) THEN
2210 IF g_debug THEN
2211 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling process price protection');
2212 END IF;
2213
2214 Process_price_protection( P_Api_Version_Number => p_api_version_number,
2215 P_Init_Msg_List => p_init_msg_list,
2216 P_Commit => FND_API.G_FALSE,
2217 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2218 X_Return_Status => x_return_status,
2219 X_Msg_Count => x_msg_count,
2220 X_Msg_Data => x_msg_data,
2221 p_trade_prf_id => x_supp_trade_profile_id,
2222 p_process_setup_tbl => p_price_protection_set_tbl ,
2223 X_created_process_tbl => X_created_process_tbl,
2224 X_updated_process_tbl => l_pro_ver_tbl ) ;
2225
2226 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2227 l_exec_error := 'Y' ;
2228 END IF ;
2229 END IF ;
2230
2231 IF(p_commit = FND_API.G_TRUE) THEN
2232 COMMIT ;
2233 END IF ;
2234
2235 IF(l_exec_error = 'Y') THEN
2236 x_return_status := FND_API.G_RET_STS_ERROR;
2237 END IF ;
2238
2239 IF g_debug THEN
2240 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Ends');
2241 END IF;
2242
2243
2244 EXCEPTION
2245 WHEN FND_API.G_EXC_ERROR THEN
2246 x_return_status := FND_API.G_RET_STS_ERROR;
2247 -- Standard call to get message count and if count=1, get the message
2248 FND_MSG_PUB.Count_And_Get (
2249 p_encoded => FND_API.G_FALSE,
2250 p_count => x_msg_count,
2251 p_data => x_msg_data
2252 );
2253
2254 IF g_debug THEN
2255 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm);
2256 END IF;
2257
2258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2260 -- Standard call to get message count and if count=1, get the message
2261 FND_MSG_PUB.Count_And_Get (
2262 p_encoded => FND_API.G_FALSE,
2263 p_count => x_msg_count,
2264 p_data => x_msg_data
2265 );
2266
2267 IF g_debug THEN
2268 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm);
2269 END IF;
2270
2271
2272 WHEN OTHERS THEN
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2274 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2275 THEN
2276 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2277 END IF;
2278 -- Standard call to get message count and if count=1, get the message
2279 FND_MSG_PUB.Count_And_Get (
2280 p_encoded => FND_API.G_FALSE,
2281 p_count => x_msg_count,
2282 p_data => x_msg_data
2283 );
2284
2285 IF g_debug THEN
2286 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm);
2287 END IF;
2288
2289 END Create_Supp_Trade_Profile ;
2290
2291
2292
2293 ---------------------------------------------------------------------
2294 -- PROCEDURE
2295 -- Update_Supp_Trade_Profile
2296 --
2297 -- PURPOSE
2298 -- Public API for Upfating Supplier Trade Profile along with code
2299 -- conversion and price protection details
2300 ---------------------------------------------------------------------
2301
2302
2303
2304 PROCEDURE Update_Supp_Trade_Profile(
2305 p_api_version_number IN NUMBER,
2306 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2307 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2308 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2309 x_return_status OUT NOCOPY VARCHAR2,
2310 x_msg_count OUT NOCOPY NUMBER,
2311 x_msg_data OUT NOCOPY VARCHAR2,
2312 p_supp_trade_profile_rec IN supp_trade_profile_rec_type ,
2313 p_code_conversion_rec_tbl IN code_conversion_tbl_type,
2314 p_price_protection_set_tbl IN process_setup_tbl_type,
2315 X_created_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type,
2316 X_updated_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type,
2317 X_created_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type,
2318 X_updated_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type,
2319 X_deleted_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type
2320 )
2321
2322 IS
2323 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Supp_Trade_Profile_all';
2324
2325 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2326
2327 l_crt_code_con_tbl OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ;
2328 l_upd_code_con_tbl OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ;
2329 l_del_code_con_tbl OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ;
2330 l_exec_error VARCHAR2(1) := 'N' ;
2331
2332
2333 BEGIN
2334
2335 IF g_debug THEN
2336 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts');
2337 END IF;
2338
2339
2340 -- Standard call to check for call compatibility.
2341 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2342 p_api_version_number,
2343 l_api_name,
2344 G_PKG_NAME)
2345 THEN
2346
2347 IF g_debug THEN
2348 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' In compatible Call');
2349 END IF ;
2350
2351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2352 END IF;
2353
2354 -- Initialize message list if p_init_msg_list is set to TRUE.
2355 IF FND_API.to_Boolean( p_init_msg_list )
2356 THEN
2357 FND_MSG_PUB.initialize;
2358 END IF;
2359
2360
2361 -------------------------------------------------
2362 -- Update Supplier trade profile
2363 -------------------------------------------------
2364
2365 IF g_debug THEN
2366 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling Update Supplier Trade Profile');
2367 END IF;
2368 Update_Supp_Trade_Profile(
2369 p_api_version_number => p_api_version_number ,
2370 p_init_msg_list => p_init_msg_list ,
2371 p_commit => p_commit,
2372 x_return_status => x_return_status,
2373 x_msg_count => x_msg_count,
2374 x_msg_data => x_msg_data,
2375 p_supp_trade_profile_rec => p_supp_trade_profile_rec );
2376
2377 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2378 l_exec_error := 'Y' ;
2379 END IF ;
2380
2381
2382
2383 -------------------------------------------------
2384 -- Process code conversion
2385 ------------------------------------------------
2386 IF g_debug THEN
2387 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling Process code conversion');
2388 END IF;
2389
2390 Process_code_conversion(
2391 p_api_version_number => p_api_version_number ,
2392 p_init_msg_list => p_init_msg_list ,
2393 P_Commit => p_commit,
2394 x_return_status => x_return_status,
2395 x_msg_count => x_msg_count,
2396 x_msg_data => x_msg_data,
2397 p_code_conversion_tbl => p_code_conversion_rec_tbl,
2398 X_created_code_con_tbl => X_created_codes_tbl ,
2399 X_updated_code_con_tbl => X_updated_codes_tbl ,
2400 X_deleted_code_con_tbl => X_deleted_codes_tbl) ;
2401
2402 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2403 l_exec_error := 'Y' ;
2404 END IF ;
2405
2406 -----------------------------------------------
2407 -- Process price protection
2408 -----------------------------------------------
2409 IF g_debug THEN
2410 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling Process Price Protection');
2411 END IF;
2412
2413 Process_price_protection(
2414 P_Api_Version_Number => p_api_version_number ,
2415 P_Init_Msg_List => p_init_msg_list ,
2416 P_Commit => p_commit,
2417 X_Return_Status => x_return_status,
2418 X_Msg_Count => x_msg_count,
2419 X_Msg_Data => x_msg_data,
2420 p_trade_prf_id => p_supp_trade_profile_rec.SUPP_TRADE_PROFILE_ID,
2421 p_process_setup_tbl => p_price_protection_set_tbl ,
2422 X_created_process_tbl => X_created_process_tbl,
2423 X_updated_process_tbl => X_updated_process_tbl) ;
2424
2425 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2426 l_exec_error := 'Y' ;
2427 END IF ;
2428
2429 IF g_debug THEN
2430 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Ends');
2431 END IF;
2432
2433 IF (l_exec_error = 'Y') THEN
2434 x_return_status := FND_API.g_ret_sts_error ;
2435 END IF ;
2436
2437 END Update_Supp_Trade_Profile ;
2438
2439
2440
2441 ---------------------------------------------------------------------
2442 -- PROCEDURE
2443 -- Update_Supp_Trade_Profile
2444 --
2445 -- PURPOSE
2446 -- Public API for Upating Supplier Trade Profile
2447 ---------------------------------------------------------------------
2448
2449
2450 PROCEDURE Update_Supp_Trade_Profile(
2451 p_api_version_number IN NUMBER,
2452 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2453 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2454 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2455 x_return_status OUT NOCOPY VARCHAR2,
2456 x_msg_count OUT NOCOPY NUMBER,
2457 x_msg_data OUT NOCOPY VARCHAR2,
2458 p_supp_trade_profile_rec IN supp_trade_profile_rec_type
2459 )
2460 IS
2461 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Supp_Trade_Profile';
2462 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2463 l_supp_trade_profile_rec supp_trade_profile_rec_type ;
2464 l_pvt_supp_rec OZF_SUPP_TRADE_PROFILE_PVT.supp_trade_profile_rec_type;
2465 l_ver_num number ;
2466
2467 BEGIN
2468
2469 IF g_debug THEN
2470 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts');
2471 END IF;
2472
2473 -- Standard call to check for call compatibility.
2474 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2475 p_api_version_number,
2476 l_api_name,
2477 G_PKG_NAME)
2478 THEN
2479 IF g_debug THEN
2480 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Incompatible Call');
2481 END IF;
2482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2483 END IF;
2484
2485 -- Initialize message list if p_init_msg_list is set to TRUE.
2486 IF FND_API.to_Boolean( p_init_msg_list )
2487 THEN
2488 FND_MSG_PUB.initialize;
2489 END IF;
2490
2491 l_supp_trade_profile_rec := p_supp_trade_profile_rec ;
2492
2493 Populate_Supp_Trade_Profile(p_init_msg_list => p_init_msg_list ,
2494 x_return_status => x_return_status ,
2495 x_msg_count => x_msg_count ,
2496 x_msg_data => x_msg_data ,
2497 p_supp_trade_profile_rec => l_supp_trade_profile_rec
2498 ) ;
2499
2500 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' *****The app channel '||l_supp_trade_profile_rec.approval_communication);
2501
2502
2503 IF x_return_status = FND_API.g_ret_sts_error THEN
2504 IF g_debug THEN
2505 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Populate Supplier trade profile failed');
2506 END IF;
2507 RAISE FND_API.G_EXC_ERROR;
2508 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2509 IF g_debug THEN
2510 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Populate Supplier trade profile failed');
2511 END IF;
2512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2513 END IF;
2514
2515
2516 Validate_Supp_Trade_Profile( p_init_msg_list => p_init_msg_list ,
2517 x_return_status => x_return_status ,
2518 x_msg_count => x_msg_count ,
2519 x_msg_data => x_msg_data ,
2520 p_supp_trade_profile_rec => l_supp_trade_profile_rec) ;
2521
2522
2523 IF x_return_status = FND_API.g_ret_sts_error THEN
2524 IF g_debug THEN
2525 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Validate Supplier trade profile failed');
2526 END IF;
2527 RAISE FND_API.G_EXC_ERROR;
2528 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2529 IF g_debug THEN
2530 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Validate Supplier trade profile failed');
2531 END IF;
2532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2533 END IF;
2534
2535
2536 -------------------------------------------------
2537 -- Convert the public record to private
2538 -------------------------------------------------
2539
2540 Convert_Supp_Trade_Profile(
2541 p_init_msg_list => p_init_msg_list,
2542 x_return_status => x_return_status,
2543 x_msg_count => x_msg_count,
2544 x_msg_data => x_msg_data,
2545 p_supp_trade_profile_rec => l_supp_trade_profile_rec,
2546 x_supp_trade_profile_rec => l_pvt_supp_rec ,
2547 p_action => 'U');
2548
2549 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' *****The app channel '||l_pvt_supp_rec.approval_communication);
2550
2551 IF x_return_status = FND_API.g_ret_sts_error THEN
2552 IF g_debug THEN
2553 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Convert Supplier trade profile failed');
2554 END IF;
2555 RAISE FND_API.G_EXC_ERROR;
2556 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2557 IF g_debug THEN
2558 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Convert Supplier trade profile failed');
2559 END IF;
2560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2561 END IF;
2562
2563
2564 -------------------------------------------------
2565 -- Call the private API
2566 -------------------------------------------------
2567 l_pvt_supp_rec.supp_trade_profile_id := l_supp_trade_profile_rec.supp_trade_profile_id ;
2568
2569 IF g_debug THEN
2570 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling private Update profile');
2571 END IF;
2572
2573 OZF_SUPP_TRADE_PROFILE_PVT.Update_Supp_Trade_Profile( p_api_version_number => p_api_version_number,
2574 p_init_msg_list => p_init_msg_list,
2575 x_return_status => x_return_status,
2576 x_msg_count => x_msg_count,
2577 x_msg_data => x_msg_data,
2578 p_supp_trade_profile_rec => l_pvt_supp_rec,
2579 x_object_version_number => l_ver_num ) ;
2580
2581
2582 IF x_return_status = FND_API.g_ret_sts_error THEN
2583 IF g_debug THEN
2584 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private update profile failed');
2585 END IF;
2586 RAISE FND_API.G_EXC_ERROR;
2587 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2588 IF g_debug THEN
2589 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private update profile failed');
2590 END IF;
2591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2592 END IF;
2593
2594 IF(p_commit = FND_API.G_TRUE) THEN
2595 commit;
2596 END IF ;
2597
2598 IF g_debug THEN
2599 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Ends');
2600 END IF;
2601
2602 EXCEPTION
2603 WHEN FND_API.G_EXC_ERROR THEN
2604 x_return_status := FND_API.G_RET_STS_ERROR;
2605 -- Standard call to get message count and if count=1, get the message
2606 FND_MSG_PUB.Count_And_Get (
2607 p_encoded => FND_API.G_FALSE,
2608 p_count => x_msg_count,
2609 p_data => x_msg_data
2610 );
2611
2612 IF g_debug THEN
2613 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm);
2614 END IF;
2615
2616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2618 -- Standard call to get message count and if count=1, get the message
2619 FND_MSG_PUB.Count_And_Get (
2620 p_encoded => FND_API.G_FALSE,
2621 p_count => x_msg_count,
2622 p_data => x_msg_data
2623 );
2624 IF g_debug THEN
2625 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm);
2626 END IF;
2627
2628 WHEN OTHERS THEN
2629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2631 THEN
2632 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2633 END IF;
2634 -- Standard call to get message count and if count=1, get the message
2635 FND_MSG_PUB.Count_And_Get (
2636 p_encoded => FND_API.G_FALSE,
2637 p_count => x_msg_count,
2638 p_data => x_msg_data
2639 );
2640
2641 IF g_debug THEN
2642 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm);
2643 END IF;
2644
2645 END Update_Supp_Trade_Profile ;
2646
2647
2648 ---------------------------------------------------------------------
2649 -- PROCEDURE
2650 -- Process_code_conversion
2651 --
2652 -- PURPOSE
2653 -- Public API for processing(create/update/delete) the code conversion
2654 -- details
2655 ---------------------------------------------------------------------
2656
2657 PROCEDURE Process_code_conversion(
2658 p_api_version_number IN NUMBER,
2659 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2660 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2661 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2662 x_return_status OUT NOCOPY VARCHAR2,
2663 x_msg_count OUT NOCOPY NUMBER,
2664 x_msg_data OUT NOCOPY VARCHAR2,
2665 p_code_conversion_tbl IN code_conversion_tbl_type ,
2666 X_created_code_con_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ,
2667 X_updated_code_con_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ,
2668 X_deleted_code_con_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type)
2669
2670
2671 IS
2672 L_API_NAME CONSTANT VARCHAR2(30) := 'Process_code_conversion';
2673
2674 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2675
2676 l_code_conversion_rec supp_code_conversion_rec_type ;
2677
2678 l_pvt_code_con_rec OZF_CODE_CONVERSION_PVT.supp_code_conversion_rec_type ;
2679
2680 l_crt_code_conv OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type := OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type() ;
2681 l_crt_count NUMBER := 0 ;
2682
2683 l_upd_code_conv OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type := OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type() ;
2684 l_upd_count NUMBER := 0 ;
2685
2686 l_del_code_conv OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type := OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type() ;
2687 l_del_count NUMBER := 0 ;
2688
2689 l_code_conversion_id_tbl JTF_NUMBER_TABLE ;
2690
2691 l_code_conversion_ver_tbl JTF_NUMBER_TABLE ;
2692
2693 l_int_code_count NUMBER := 0 ;
2694 l_int_cc_count NUMBER := 0 ;
2695
2696 l_exec_err VARCHAR2(1) := 'N' ;
2697
2698 CURSOR csr_supp_rec(cv_supp_trade_profile_id NUMBER)
2699 IS
2700 select ORG_ID FROM ozf_supp_trd_prfls_all
2701 WHERE supp_trade_profile_id= cv_supp_trade_profile_id ;
2702
2703 BEGIN
2704
2705 IF g_debug THEN
2706 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Starts');
2707 END IF;
2708
2709 -- Standard call to check for call compatibility.
2710 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2711 p_api_version_number,
2712 l_api_name,
2713 G_PKG_NAME)
2714 THEN
2715 IF g_debug THEN
2716 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Incompatible Call');
2717 END IF;
2718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2719 END IF;
2720
2721 -- Initialize message list if p_init_msg_list is set to TRUE.
2722 IF FND_API.to_Boolean( p_init_msg_list )
2723 THEN
2724 FND_MSG_PUB.initialize;
2725 END IF;
2726
2727 ------------------------------------------------------------------------
2728 -- Traverse the code conversion table
2729 ------------------------------------------------------------------------
2730
2731 FOR i in 1 .. p_code_conversion_tbl.COUNT
2732 LOOP
2733
2734 l_code_conversion_rec := p_code_conversion_tbl(i);
2735
2736 OPEN csr_supp_rec( l_code_conversion_rec.supp_trade_profile_id);
2737 FETCH csr_supp_rec
2738 INTO l_code_conversion_rec.ORG_ID;
2739
2740 IF ( csr_supp_rec%NOTFOUND) THEN
2741
2742 IF g_debug THEN
2743 l_exec_err := 'Y' ;
2744 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid record');
2745 END IF;
2746
2747 ELSE
2748
2749 l_pvt_code_con_rec := NULL ;
2750
2751 -- Convert to the PRIVATE API format
2752 l_pvt_code_con_rec.CODE_CONVERSION_ID := l_code_conversion_rec.CODE_CONVERSION_ID ;
2753 l_pvt_code_con_rec.LAST_UPDATE_DATE := l_code_conversion_rec.LAST_UPDATE_DATE ;
2754 l_pvt_code_con_rec.LAST_UPDATED_BY := l_code_conversion_rec.LAST_UPDATED_BY ;
2755 l_pvt_code_con_rec.CREATION_DATE := l_code_conversion_rec.CREATION_DATE ;
2756 l_pvt_code_con_rec.CREATED_BY := l_code_conversion_rec.CREATED_BY ;
2757 l_pvt_code_con_rec.LAST_UPDATE_LOGIN := l_code_conversion_rec.LAST_UPDATE_LOGIN;
2758 l_pvt_code_con_rec.ORG_ID := l_code_conversion_rec.ORG_ID;
2759 l_pvt_code_con_rec.SUPP_TRADE_PROFILE_ID := l_code_conversion_rec.SUPP_TRADE_PROFILE_ID ;
2760 l_pvt_code_con_rec.CODE_CONVERSION_TYPE := l_code_conversion_rec.CODE_CONVERSION_TYPE;
2761 l_pvt_code_con_rec.EXTERNAL_CODE := l_code_conversion_rec.EXTERNAL_CODE ;
2762 l_pvt_code_con_rec.INTERNAL_CODE := l_code_conversion_rec.INTERNAL_CODE;
2763 l_pvt_code_con_rec.DESCRIPTION := l_code_conversion_rec.DESCRIPTION;
2764
2765 IF (l_code_conversion_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE) THEN
2766
2767 l_pvt_code_con_rec.START_DATE_ACTIVE := SYSDATE ;
2768 ELSE
2769 l_pvt_code_con_rec.START_DATE_ACTIVE := l_code_conversion_rec.START_DATE_ACTIVE ;
2770 END IF ;
2771
2772 l_pvt_code_con_rec.END_DATE_ACTIVE := l_code_conversion_rec.END_DATE_ACTIVE;
2773 l_pvt_code_con_rec.ATTRIBUTE_CATEGORY := l_code_conversion_rec.ATTRIBUTE_CATEGORY;
2774 l_pvt_code_con_rec.ATTRIBUTE1 := l_code_conversion_rec.ATTRIBUTE1;
2775 l_pvt_code_con_rec.ATTRIBUTE2 := l_code_conversion_rec.ATTRIBUTE2 ;
2776 l_pvt_code_con_rec.ATTRIBUTE3 := l_code_conversion_rec.ATTRIBUTE3 ;
2777 l_pvt_code_con_rec.ATTRIBUTE4 := l_code_conversion_rec.ATTRIBUTE4 ;
2778 l_pvt_code_con_rec.ATTRIBUTE5 := l_code_conversion_rec.ATTRIBUTE5 ;
2779 l_pvt_code_con_rec.ATTRIBUTE6 := l_code_conversion_rec.ATTRIBUTE6 ;
2780 l_pvt_code_con_rec.ATTRIBUTE7 := l_code_conversion_rec.ATTRIBUTE7 ;
2781 l_pvt_code_con_rec.ATTRIBUTE8 := l_code_conversion_rec.ATTRIBUTE8 ;
2782 l_pvt_code_con_rec.ATTRIBUTE9 := l_code_conversion_rec.ATTRIBUTE9 ;
2783 l_pvt_code_con_rec.ATTRIBUTE10 := l_code_conversion_rec.ATTRIBUTE10 ;
2784 l_pvt_code_con_rec.ATTRIBUTE11 := l_code_conversion_rec.ATTRIBUTE11 ;
2785 l_pvt_code_con_rec.ATTRIBUTE12 := l_code_conversion_rec.ATTRIBUTE12 ;
2786 l_pvt_code_con_rec.ATTRIBUTE13 := l_code_conversion_rec.ATTRIBUTE13 ;
2787 l_pvt_code_con_rec.ATTRIBUTE14 := l_code_conversion_rec.ATTRIBUTE14 ;
2788 l_pvt_code_con_rec.ATTRIBUTE15 := l_code_conversion_rec.ATTRIBUTE15 ;
2789 l_pvt_code_con_rec.SECURITY_GROUP_ID := l_code_conversion_rec.SECURITY_GROUP_ID ;
2790
2791
2792
2793 -- Check for action to be performed on the code conversion record
2794
2795 IF ( l_code_conversion_rec.CODE_CONVERSION_ACTION = 'C') THEN
2796
2797
2798 -- Validate the internal code
2799
2800 l_pvt_code_con_rec.OBJECT_VERSION_NUMBER := 1;
2801
2802 Validate_Code_Cov_rec(
2803 p_init_msg_list => p_init_msg_list,
2804 x_return_status => x_return_status,
2805 x_msg_count => x_msg_count,
2806 x_msg_data => x_msg_data,
2807 p_code_conv_rec => l_pvt_code_con_rec,
2808 p_mode => 'C') ;
2809
2810 IF g_debug THEN
2811 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Validate code conv returned '||x_return_status );
2812 END IF;
2813 -- Add to create list of the record is valid
2814 IF (x_return_status = FND_API.g_ret_sts_success ) THEN
2815 l_crt_code_conv.extend ;
2816
2817 -- initialize the code conversion id
2818 SELECT ozf_supp_code_conv_all_s.nextval
2819 INTO l_pvt_code_con_rec.CODE_CONVERSION_ID
2820 FROM DUAL;
2821
2822 IF g_debug THEN
2823 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' New code conversion Id'||l_pvt_code_con_rec.CODE_CONVERSION_ID );
2824 END IF;
2825
2826 l_crt_code_conv(l_crt_code_conv.COUNT) := l_pvt_code_con_rec;
2827
2828 ELSE
2829 l_exec_err := 'Y' ;
2830 END IF ;
2831
2832 ELSIF (l_code_conversion_rec.CODE_CONVERSION_ACTION = 'U') THEN
2833
2834
2835 -- Populate the internal code
2836 Populate_code_conv_rec(
2837 p_init_msg_list => p_init_msg_list,
2838 x_return_status => x_return_status,
2839 x_msg_count => x_msg_count,
2840 x_msg_data => x_msg_data,
2841 p_code_con_rec => l_pvt_code_con_rec) ;
2842
2843 IF g_debug THEN
2844 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Populate code conv returned'||x_return_status );
2845 END IF;
2846
2847
2848 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2849
2850 -- Validate the internal code
2851 Validate_Code_Cov_rec(
2852 p_init_msg_list => p_init_msg_list,
2853 x_return_status => x_return_status,
2854 x_msg_count => x_msg_count,
2855 x_msg_data => x_msg_data,
2856 p_code_conv_rec => l_pvt_code_con_rec,
2857 p_mode => 'U') ;
2858
2859 IF g_debug THEN
2860 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Validate code conv returned'||x_return_status );
2861 END IF;
2862
2863 -- Add the valid record to update list
2864 IF (x_return_status = FND_API.g_ret_sts_success ) THEN
2865
2866 l_upd_code_conv.extend ;
2867 l_upd_code_conv(l_upd_code_conv.COUNT) := l_pvt_code_con_rec;
2868
2869 ELSE
2870
2871 l_exec_err := 'Y' ;
2872 END IF ;
2873 END IF ;
2874
2875 ELSIF (l_code_conversion_rec.CODE_CONVERSION_ACTION = 'D') THEN
2876
2877 -- Validate the code conversion Id
2878
2879 SELECT COUNT(1) INTO l_int_cc_count
2880 FROM ozf_supp_code_conversions_all
2881 WHERE code_conversion_id = l_pvt_code_con_rec.CODE_CONVERSION_ID ;
2882
2883 IF (l_int_cc_count > 0) THEN
2884 l_del_code_conv.extend ;
2885 l_del_code_conv(l_del_code_conv.COUNT) := l_pvt_code_con_rec;
2886
2887 END IF ;
2888 END IF ;
2889
2890
2891 END IF ; -- cursor record found
2892
2893 CLOSE csr_supp_rec;
2894
2895 END LOOP ;
2896
2897
2898 ------------------------------------------------------------------------
2899 -- Call the private API based on the record action
2900 ------------------------------------------------------------------------
2901
2902 IF ( l_crt_code_conv.count >0 ) THEN
2903
2904 -- Mass insertion
2905
2906 FORALL indx IN 1..l_crt_code_conv.count
2907 INSERT INTO ozf_supp_code_conversions_all(
2908 code_conversion_id,
2909 object_version_number,
2910 last_update_date,
2911 last_updated_by,
2912 creation_date,
2913 created_by,
2914 last_update_login,
2915 org_id,
2916 supp_trade_profile_id,
2917 code_conversion_type,
2918 external_code,
2919 internal_code,
2920 description,
2921 start_date_active,
2922 end_date_active,
2923 attribute_category,
2924 attribute1,
2925 attribute2,
2926 attribute3,
2927 attribute4,
2928 attribute5,
2929 attribute6,
2930 attribute7,
2931 attribute8,
2932 attribute9,
2933 attribute10,
2934 attribute11,
2935 attribute12,
2936 attribute13,
2937 attribute14,
2938 attribute15
2939 ) VALUES (
2940 l_crt_code_conv(indx).CODE_CONVERSION_ID,
2941 l_crt_code_conv(indx).OBJECT_VERSION_NUMBER,
2942 SYSDATE,
2943 FND_GLOBAL.USER_ID,
2944 SYSDATE,
2945 FND_GLOBAL.USER_ID,
2946 FND_GLOBAL.CONC_LOGIN_ID,
2947 l_crt_code_conv(indx).ORG_ID,
2948 l_crt_code_conv(indx).SUPP_TRADE_PROFILE_ID,
2949 DECODE( l_crt_code_conv(indx).code_conversion_type, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).code_conversion_type),
2950 DECODE( l_crt_code_conv(indx).external_code, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).external_code),
2951 DECODE( l_crt_code_conv(indx).internal_code, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).internal_code),
2952 DECODE( l_crt_code_conv(indx).description, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).description),
2953 DECODE( l_crt_code_conv(indx).start_date_active, FND_API.G_MISS_DATE, to_date(NULL), l_crt_code_conv(indx).start_date_active),
2954 DECODE( l_crt_code_conv(indx).end_date_active, FND_API.G_MISS_DATE, to_date(NULL), l_crt_code_conv(indx).end_date_active),
2955 DECODE( l_crt_code_conv(indx).attribute_category, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute_category),
2956 DECODE( l_crt_code_conv(indx).attribute1, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute1),
2957 DECODE( l_crt_code_conv(indx).attribute2, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute2),
2958 DECODE( l_crt_code_conv(indx).attribute3, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute3),
2959 DECODE( l_crt_code_conv(indx).attribute4, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute4),
2960 DECODE( l_crt_code_conv(indx).attribute5, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute5),
2961 DECODE( l_crt_code_conv(indx).attribute6, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute6),
2962 DECODE( l_crt_code_conv(indx).attribute7, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute7),
2963 DECODE( l_crt_code_conv(indx).attribute8, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute8),
2964 DECODE( l_crt_code_conv(indx).attribute9, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute9),
2965 DECODE( l_crt_code_conv(indx).attribute10, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute10),
2966 DECODE( l_crt_code_conv(indx).attribute11, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute11),
2967 DECODE( l_crt_code_conv(indx).attribute12, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute12),
2968 DECODE( l_crt_code_conv(indx).attribute13, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute13),
2969 DECODE( l_crt_code_conv(indx).attribute14, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute14),
2970 DECODE( l_crt_code_conv(indx).attribute15, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute15)
2971 );
2972
2973
2974 IF (P_Commit = FND_API.G_TRUE) THEN
2975
2976 COMMIT ;
2977 X_created_code_con_tbl := l_crt_code_conv ;
2978 IF g_debug THEN
2979 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Mass Insertion was successful' );
2980 END IF;
2981 END IF ;
2982
2983
2984 END IF ;
2985
2986
2987 IF (l_upd_code_conv.count >0 ) THEN
2988
2989 -- Mass update
2990 FORALL indx IN 1..l_upd_code_conv.count
2991
2992 Update ozf_supp_code_conversions_all
2993 SET
2994 object_version_number = l_upd_code_conv(indx).object_version_number +1 ,
2995 last_update_date = sysdate,
2996 last_updated_by = FND_GLOBAL.USER_ID,
2997 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
2998 org_id = l_upd_code_conv(indx).org_id,
2999 supp_trade_profile_id = l_upd_code_conv(indx).supp_trade_profile_id ,
3000 code_conversion_type = l_upd_code_conv(indx).code_conversion_type,
3001 external_code = l_upd_code_conv(indx).external_code,
3002 internal_code = l_upd_code_conv(indx).internal_code,
3003 description = l_upd_code_conv(indx).description,
3004 start_date_active = l_upd_code_conv(indx).start_date_active,
3005 end_date_active = l_upd_code_conv(indx).end_date_active,
3006 attribute_category = l_upd_code_conv(indx).attribute_category,
3007 attribute1 = l_upd_code_conv(indx).attribute1,
3008 attribute2 = l_upd_code_conv(indx).attribute2,
3009 attribute3 = l_upd_code_conv(indx).attribute3,
3010 attribute4 = l_upd_code_conv(indx).attribute4,
3011 attribute5 = l_upd_code_conv(indx).attribute5,
3012 attribute6 = l_upd_code_conv(indx).attribute6,
3013 attribute7 = l_upd_code_conv(indx).attribute7,
3014 attribute8 = l_upd_code_conv(indx).attribute8,
3015 attribute9 = l_upd_code_conv(indx).attribute9,
3016 attribute10 = l_upd_code_conv(indx).attribute10,
3017 attribute11 = l_upd_code_conv(indx).attribute11,
3018 attribute12 = l_upd_code_conv(indx).attribute12,
3019 attribute13 = l_upd_code_conv(indx).attribute13,
3020 attribute14 = l_upd_code_conv(indx).attribute14,
3021 attribute15 = l_upd_code_conv(indx).attribute15
3022 WHERE
3023 code_conversion_id = l_upd_code_conv(indx).code_conversion_id ;
3024
3025 IF (P_Commit = FND_API.G_TRUE) THEN
3026
3027 COMMIT ;
3028 X_updated_code_con_tbl := l_upd_code_conv ;
3029 IF g_debug THEN
3030 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Mass Update was successful' );
3031 END IF;
3032 END IF ;
3033
3034 END IF ;
3035
3036 IF (l_del_code_conv.COUNT >0 ) THEN
3037
3038 -- Mass delete
3039 FORALL indx IN 1..l_del_code_conv.count
3040 DELETE FROM ozf_supp_code_conversions_all
3041 WHERE
3042 code_conversion_id = l_del_code_conv(indx).code_conversion_id ;
3043
3044 IF (P_Commit = FND_API.G_TRUE) THEN
3045
3046 COMMIT ;
3047 X_deleted_code_con_tbl := l_del_code_conv ;
3048 IF g_debug THEN
3049 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Mass Delete was successful' );
3050 END IF;
3051 END IF ;
3052
3053 END IF ;
3054
3055
3056 IF (l_exec_err = 'Y') THEN
3057 x_return_status := FND_API.g_ret_sts_error ;
3058 END IF ;
3059
3060 IF g_debug THEN
3061 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' End' );
3062 END IF;
3063
3064 EXCEPTION
3065 WHEN FND_API.G_EXC_ERROR THEN
3066 x_return_status := FND_API.G_RET_STS_ERROR;
3067 -- Standard call to get message count and if count=1, get the message
3068 FND_MSG_PUB.Count_And_Get (
3069 p_encoded => FND_API.G_FALSE,
3070 p_count => x_msg_count,
3071 p_data => x_msg_data
3072 );
3073
3074 IF g_debug THEN
3075 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
3076 END IF;
3077
3078 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3079
3080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3081 -- Standard call to get message count and if count=1, get the message
3082 FND_MSG_PUB.Count_And_Get (
3083 p_encoded => FND_API.G_FALSE,
3084 p_count => x_msg_count,
3085 p_data => x_msg_data
3086 );
3087
3088 IF g_debug THEN
3089 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
3090 END IF;
3091
3092 WHEN OTHERS THEN
3093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3094 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3095 THEN
3096 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3097 END IF;
3098 -- Standard call to get message count and if count=1, get the message
3099 FND_MSG_PUB.Count_And_Get (
3100 p_encoded => FND_API.G_FALSE,
3101 p_count => x_msg_count,
3102 p_data => x_msg_data
3103 );
3104
3105 IF g_debug THEN
3106 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
3107 END IF;
3108
3109
3110 END Process_code_conversion ;
3111
3112
3113
3114
3115
3116
3117 ---------------------------------------------------------------------
3118 -- PROCEDURE
3119 -- Process_price_protection
3120 --
3121 -- PURPOSE
3122 -- Public API for processing(create/update/delete) the price protection
3123 -- details
3124 ---------------------------------------------------------------------
3125
3126 PROCEDURE Process_price_protection(
3127 P_Api_Version_Number IN NUMBER,
3128 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3129 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3130 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3131 X_Return_Status OUT NOCOPY VARCHAR2,
3132 X_Msg_Count OUT NOCOPY NUMBER,
3133 X_Msg_Data OUT NOCOPY VARCHAR2,
3134 p_trade_prf_id IN NUMBER,
3135 p_process_setup_tbl IN process_setup_tbl_type ,
3136 X_created_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type,
3137 X_updated_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type)
3138 IS
3139 L_API_NAME CONSTANT VARCHAR2(30) := 'Process_price_protection';
3140
3141 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
3142 CURSOR c_get_pp_data IS
3143 SELECT NVL(ps.enabled_flag,'N') enabledFlag,
3144 ps.process_setup_id processSetupId,
3145 ps.process_setup_id objId,
3146 ps.org_id orgId,
3147 fl.meaning processName,
3148 ps.object_version_number objVerNum,
3149 fl.lookup_code processCode,
3150 ps.supp_trade_profile_id suppTradeProfileId,
3151 NVL(ps.automatic_flag,'N') automaticFlag,
3152 ps.SECURITY_GROUP_ID secGrpId,
3153 ps.attribute_category attribute_category,
3154 ps.attribute1 attribute1,
3155 ps.attribute1 attribute2,
3156 ps.attribute1 attribute3,
3157 ps.attribute1 attribute4,
3158 ps.attribute1 attribute5,
3159 ps.attribute1 attribute6,
3160 ps.attribute1 attribute7,
3161 ps.attribute1 attribute8,
3162 ps.attribute1 attribute9,
3163 ps.attribute1 attribute10,
3164 ps.attribute1 attribute11,
3165 ps.attribute1 attribute12,
3166 ps.attribute1 attribute13,
3167 ps.attribute1 attribute14,
3168 ps.attribute1 attribute15
3169 FROM dpp_lookups fl , ozf_process_setup_all ps
3170 WHERE (ps.supp_trade_profile_id(+) = p_trade_prf_id
3171 AND fl.lookup_type = 'DPP_EXECUTION_PROCESSES'
3172 AND fl.lookup_code = ps.process_code (+)
3173 AND fl.tag is not NULL
3174 AND fl.enabled_flag = 'Y')
3175 ORDER BY fl.lookup_code ;
3176
3177 l_process_exists VARCHAR2(1) := 'N' ;
3178
3179 l_process_rec OZF_PROCESS_SETUP_PVT.process_setup_rec_type ;
3180 l_process_crt_tbl OZF_PROCESS_SETUP_PVT.process_setup_tbl_type := OZF_PROCESS_SETUP_PVT.process_setup_tbl_type() ;
3181 l_crt_count NUMBER := 0 ;
3182
3183 l_process_upd_tbl OZF_PROCESS_SETUP_PVT.process_setup_tbl_type := OZF_PROCESS_SETUP_PVT.process_setup_tbl_type() ;
3184 l_upd_count NUMBER := 0 ;
3185
3186 l_org_id NUMBER := 0 ;
3187
3188 l_crt_id_tbl JTF_NUMBER_TABLE ;
3189
3190 l_exec_err VARCHAR2(1) := 'N' ;
3191
3192 CURSOR c_sel_trd_org IS
3193 SELECT org_id FROM ozf_supp_trd_prfls_all
3194 WHERE supp_trade_profile_id = p_trade_prf_id ;
3195
3196 BEGIN
3197
3198
3199 IF g_debug THEN
3200 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||'Starts' );
3201 END IF;
3202
3203 -- Standard call to check for call compatibility.
3204 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3205 p_api_version_number,
3206 l_api_name,
3207 G_PKG_NAME)
3208 THEN
3209 IF g_debug THEN
3210 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Incopatible Call' );
3211 END IF;
3212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3213 END IF;
3214
3215 -- Initialize message list if p_init_msg_list is set to TRUE.
3216 IF FND_API.to_Boolean( p_init_msg_list )
3217 THEN
3218 FND_MSG_PUB.initialize;
3219 END IF;
3220
3221
3222 x_return_status := FND_API.g_ret_sts_success ;
3223
3224
3225
3226 -- If the supplier trade profile Id is null, then return
3227
3228 OPEN c_sel_trd_org ;
3229
3230 FETCH c_sel_trd_org INTO l_org_id ;
3231
3232
3233 IF ( c_sel_trd_org%NOTFOUND) THEN
3234
3235 IF g_debug THEN
3236 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid record' );
3237 END IF;
3238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3239 RETURN ;
3240 ELSE
3241 ---------------------------------------------------
3242 --Traverse the process setup table
3243 ---------------------------------------------------
3244
3245 FOR r_pp_data IN c_get_pp_data LOOP
3246
3247 l_process_exists := 'N' ;
3248
3249 <<api_loop>> FOR indx in 1 .. p_process_setup_tbl.COUNT
3250 LOOP
3251
3252 -- Check if the process code exixts in the table
3253 IF (p_process_setup_tbl(indx).PROCESS_CODE = r_pp_data.processCode ) THEN
3254
3255 l_process_exists := 'Y' ;
3256
3257
3258 ---------------------------------------------------------------------
3259 --- SET THE DATA FOR UPDATE : START
3260 ---------------------------------------------------------------------
3261 IF (r_pp_data.processSetupId IS NOT NULL ) then
3262
3263
3264 l_process_rec := null ;
3265
3266 l_process_rec.PROCESS_SETUP_ID := r_pp_data.processSetupId ;
3267 l_process_rec.OBJECT_VERSION_NUMBER := r_pp_data.objVerNum ;
3268 l_process_rec.LAST_UPDATE_DATE := SYSDATE ;
3269
3270 IF (p_process_setup_tbl(indx).last_updated_by = FND_API.G_MISS_NUM) THEN
3271 l_process_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID ;
3272 ELSE
3273 l_process_rec.LAST_UPDATED_BY := p_process_setup_tbl(indx).last_updated_by ;
3274 END IF ;
3275
3276
3277 l_process_rec.ORG_ID := r_pp_data.orgId ;
3278 l_process_rec.SUPP_TRADE_PROFILE_ID := r_pp_data.suppTradeProfileId ;
3279 l_process_rec.PROCESS_CODE := r_pp_data.processCode ;
3280
3281
3282
3283 IF(p_process_setup_tbl(indx).ENABLED_FLAG IS NULL OR
3284 p_process_setup_tbl(indx).ENABLED_FLAG= FND_API.g_miss_char) THEN
3285
3286 l_process_rec.ENABLED_FLAG := 'N' ;
3287
3288 ELSE
3289 IF(p_process_setup_tbl(indx).ENABLED_FLAG NOT IN ('Y','N')) THEN
3290 IF g_debug THEN
3291 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid Enabled Flag' );
3292 END IF;
3293 l_exec_err := 'Y' ;
3294 EXIT api_loop ;
3295
3296 ELSE
3297 l_process_rec.ENABLED_FLAG := p_process_setup_tbl(indx).ENABLED_FLAG ;
3298 END IF ;
3299
3300 END IF ;
3301
3302
3303
3304 IF(p_process_setup_tbl(indx).AUTOMATIC_FLAG IS NULL OR
3305 p_process_setup_tbl(indx).AUTOMATIC_FLAG= FND_API.g_miss_char) THEN
3306
3307 l_process_rec.AUTOMATIC_FLAG := 'N' ;
3308
3309 ELSE
3310
3311 IF (p_process_setup_tbl(indx).AUTOMATIC_FLAG NOT IN ('Y','N')) THEN
3312
3313 IF g_debug THEN
3314 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid Automatic Flag' );
3315 END IF;
3316 l_exec_err := 'Y' ;
3317 EXIT api_loop ;
3318
3319 ELSE
3320 l_process_rec.AUTOMATIC_FLAG := p_process_setup_tbl(indx).AUTOMATIC_FLAG ;
3321 END IF ;
3322
3323 END IF ;
3324
3325
3326
3327 IF (p_process_setup_tbl(indx).attribute_category = FND_API.g_miss_char) THEN
3328 l_process_rec.ATTRIBUTE_CATEGORY := r_pp_data.attribute_category ;
3329 ELSE
3330 l_process_rec.ATTRIBUTE_CATEGORY := p_process_setup_tbl(indx).attribute_category ;
3331 END IF ;
3332
3333 IF (p_process_setup_tbl(indx).attribute1 = FND_API.g_miss_char) THEN
3334 l_process_rec.ATTRIBUTE1 := r_pp_data.attribute1 ;
3335 ELSE
3336 l_process_rec.ATTRIBUTE1 := p_process_setup_tbl(indx).attribute1 ;
3337 END IF ;
3338
3339 IF (p_process_setup_tbl(indx).attribute2 = FND_API.g_miss_char) THEN
3340 l_process_rec.ATTRIBUTE2 := r_pp_data.attribute2 ;
3341 ELSE
3342 l_process_rec.ATTRIBUTE2 := p_process_setup_tbl(indx).attribute2 ;
3343 END IF ;
3344
3345 IF (p_process_setup_tbl(indx).attribute3 = FND_API.g_miss_char) THEN
3346 l_process_rec.ATTRIBUTE3 := r_pp_data.attribute3 ;
3347 ELSE
3348 l_process_rec.ATTRIBUTE3 := p_process_setup_tbl(indx).attribute3 ;
3349 END IF ;
3350
3351 IF (p_process_setup_tbl(indx).attribute4 = FND_API.g_miss_char) THEN
3352 l_process_rec.ATTRIBUTE4 := r_pp_data.attribute4 ;
3353 ELSE
3354 l_process_rec.ATTRIBUTE4 := p_process_setup_tbl(indx).attribute4 ;
3355 END IF ;
3356
3357 IF (p_process_setup_tbl(indx).attribute5 = FND_API.g_miss_char) THEN
3358 l_process_rec.ATTRIBUTE5 := r_pp_data.attribute5 ;
3359 ELSE
3360 l_process_rec.ATTRIBUTE5 := p_process_setup_tbl(indx).attribute5 ;
3361 END IF ;
3362
3363 IF (p_process_setup_tbl(indx).attribute6 = FND_API.g_miss_char) THEN
3364 l_process_rec.ATTRIBUTE6 := r_pp_data.attribute6 ;
3365 ELSE
3366 l_process_rec.ATTRIBUTE6 := p_process_setup_tbl(indx).attribute6 ;
3367 END IF ;
3368
3369 IF (p_process_setup_tbl(indx).attribute7 = FND_API.g_miss_char) THEN
3370 l_process_rec.ATTRIBUTE7 := r_pp_data.attribute7 ;
3371 ELSE
3372 l_process_rec.ATTRIBUTE7 := p_process_setup_tbl(indx).attribute7 ;
3373 END IF ;
3374
3375 IF (p_process_setup_tbl(indx).attribute8 = FND_API.g_miss_char) THEN
3376 l_process_rec.ATTRIBUTE8 := r_pp_data.attribute8 ;
3377 ELSE
3378 l_process_rec.ATTRIBUTE8 := p_process_setup_tbl(indx).attribute8 ;
3379 END IF ;
3380
3381 IF (p_process_setup_tbl(indx).attribute9 = FND_API.g_miss_char) THEN
3382 l_process_rec.ATTRIBUTE9 := r_pp_data.attribute9 ;
3383 ELSE
3384 l_process_rec.ATTRIBUTE9 := p_process_setup_tbl(indx).attribute9 ;
3385 END IF ;
3386
3387 IF (p_process_setup_tbl(indx).attribute10 = FND_API.g_miss_char) THEN
3388 l_process_rec.ATTRIBUTE10 := r_pp_data.attribute10 ;
3389 ELSE
3390 l_process_rec.ATTRIBUTE10 := p_process_setup_tbl(indx).attribute10 ;
3391 END IF ;
3392
3393 IF (p_process_setup_tbl(indx).attribute11 = FND_API.g_miss_char) THEN
3394 l_process_rec.ATTRIBUTE11 := r_pp_data.attribute11 ;
3395 ELSE
3396 l_process_rec.ATTRIBUTE11 := p_process_setup_tbl(indx).attribute11 ;
3397 END IF ;
3398
3399 IF (p_process_setup_tbl(indx).attribute12 = FND_API.g_miss_char) THEN
3400 l_process_rec.ATTRIBUTE12 := r_pp_data.attribute12 ;
3401 ELSE
3402 l_process_rec.ATTRIBUTE12 := p_process_setup_tbl(indx).attribute12 ;
3403 END IF ;
3404
3405 IF (p_process_setup_tbl(indx).attribute13 = FND_API.g_miss_char) THEN
3406 l_process_rec.ATTRIBUTE13 := r_pp_data.attribute13 ;
3407 ELSE
3408 l_process_rec.ATTRIBUTE13 := p_process_setup_tbl(indx).attribute13 ;
3409 END IF ;
3410
3411 IF (p_process_setup_tbl(indx).attribute14 = FND_API.g_miss_char) THEN
3412 l_process_rec.ATTRIBUTE14 := r_pp_data.attribute14 ;
3413 ELSE
3414 l_process_rec.ATTRIBUTE14 := p_process_setup_tbl(indx).attribute14 ;
3415 END IF ;
3416
3417 IF (p_process_setup_tbl(indx).attribute15 = FND_API.g_miss_char) THEN
3418 l_process_rec.ATTRIBUTE15 := r_pp_data.attribute15 ;
3419 ELSE
3420 l_process_rec.ATTRIBUTE15 := p_process_setup_tbl(indx).attribute15 ;
3421 END IF ;
3422
3423
3424
3425 IF (p_process_setup_tbl(indx).SECURITY_GROUP_ID = FND_API.g_miss_num) THEN
3426 l_process_rec.SECURITY_GROUP_ID := r_pp_data.secGrpId ;
3427 ELSE
3428 l_process_rec.SECURITY_GROUP_ID := p_process_setup_tbl(indx).SECURITY_GROUP_ID ;
3429 END IF ;
3430
3431
3432
3433 ----------------------------------------------------------------------------------------
3434 -- SET THE DATA FOR UPDATE : END
3435 ----------------------------------------------------------------------------------------
3436 IF g_debug THEN
3437 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Data for update' );
3438 END IF;
3439 l_process_upd_tbl.extend ;
3440 l_process_upd_tbl(l_process_upd_tbl.COUNT) := l_process_rec ;
3441 l_upd_count := l_upd_count +1 ;
3442
3443
3444
3445 ELSE
3446
3447
3448 --------------------------------------------------------------------------
3449 -- SET THE DATA FOR CREATE : END
3450 --------------------------------------------------------------------------
3451 l_process_rec := null ;
3452
3453
3454 l_process_rec.ORG_ID := l_org_id ;
3455 l_process_rec.SUPP_TRADE_PROFILE_ID := p_trade_prf_id ;
3456 l_process_rec.PROCESS_CODE := r_pp_data.processCode ;
3457
3458
3459 IF(p_process_setup_tbl(indx).ENABLED_FLAG IS NULL OR
3460 p_process_setup_tbl(indx).ENABLED_FLAG= FND_API.g_miss_char) THEN
3461
3462 l_process_rec.ENABLED_FLAG := 'N' ;
3463
3464 ELSE
3465 IF(p_process_setup_tbl(indx).ENABLED_FLAG NOT IN ('Y','N')) THEN
3466 IF g_debug THEN
3467 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid Enabled Flag' );
3468 END IF;
3469 l_exec_err := 'Y' ;
3470 EXIT api_loop ;
3471
3472 ELSE
3473 l_process_rec.ENABLED_FLAG := 'Y' ;
3474 END IF ;
3475
3476 END IF ;
3477
3478
3479
3480 IF(p_process_setup_tbl(indx).AUTOMATIC_FLAG IS NULL OR
3481 p_process_setup_tbl(indx).AUTOMATIC_FLAG= FND_API.g_miss_char) THEN
3482
3483 l_process_rec.AUTOMATIC_FLAG := 'N' ;
3484
3485 ELSE
3486
3487 IF (p_process_setup_tbl(indx).AUTOMATIC_FLAG NOT IN ('Y','N')) THEN
3488
3489 IF g_debug THEN
3490 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Invalid Automatic Flag' );
3491 END IF;
3492 l_exec_err := 'Y' ;
3493 EXIT api_loop ;
3494
3495 ELSE
3496 l_process_rec.AUTOMATIC_FLAG := p_process_setup_tbl(indx).AUTOMATIC_FLAG ;
3497 END IF ;
3498
3499 END IF ;
3500
3501
3502
3503
3504 l_process_rec.ATTRIBUTE_CATEGORY := p_process_setup_tbl(indx).attribute_category ;
3505 l_process_rec.ATTRIBUTE1 := p_process_setup_tbl(indx).attribute1 ;
3506 l_process_rec.ATTRIBUTE2 := p_process_setup_tbl(indx).attribute2 ;
3507 l_process_rec.ATTRIBUTE3 := p_process_setup_tbl(indx).attribute3 ;
3508 l_process_rec.ATTRIBUTE4 := p_process_setup_tbl(indx).attribute4 ;
3509 l_process_rec.ATTRIBUTE5 := p_process_setup_tbl(indx).attribute5 ;
3510 l_process_rec.ATTRIBUTE6 := p_process_setup_tbl(indx).attribute6 ;
3511 l_process_rec.ATTRIBUTE7 := p_process_setup_tbl(indx).attribute7 ;
3512 l_process_rec.ATTRIBUTE8 := p_process_setup_tbl(indx).attribute8 ;
3513 l_process_rec.ATTRIBUTE9 := p_process_setup_tbl(indx).attribute9 ;
3514 l_process_rec.ATTRIBUTE10 := p_process_setup_tbl(indx).attribute10 ;
3515 l_process_rec.ATTRIBUTE11 := p_process_setup_tbl(indx).attribute11 ;
3516 l_process_rec.ATTRIBUTE12 := p_process_setup_tbl(indx).attribute12 ;
3517 l_process_rec.ATTRIBUTE13 := p_process_setup_tbl(indx).attribute13 ;
3518 l_process_rec.ATTRIBUTE14 := p_process_setup_tbl(indx).attribute14 ;
3519 l_process_rec.ATTRIBUTE15 := p_process_setup_tbl(indx).attribute15 ;
3520 l_process_rec.SECURITY_GROUP_ID := p_process_setup_tbl(indx).SECURITY_GROUP_ID ;
3521
3522 ----------------------------------------------------------------------------------------
3523 -- SET THE DATA FOR CREATE : END
3524 ----------------------------------------------------------------------------------------
3525 IF g_debug THEN
3526 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Data for create' );
3527 END IF;
3528 l_process_crt_tbl.extend ;
3529 l_process_crt_tbl(l_process_crt_tbl.COUNT) := l_process_rec ;
3530 l_crt_count := l_crt_count +1 ;
3531
3532
3533 END IF ;
3534
3535 END IF ;
3536
3537
3538 EXIT api_loop WHEN l_process_exists = 'Y' ;
3539
3540 END LOOP api_loop ;
3541
3542 -- If the flag is N , it means the DB record needs to be created
3543
3544 IF (l_process_exists = 'N' AND r_pp_data.processSetupId IS NULL) THEN
3545
3546 --------------------------------------------------------------------------
3547 -- The data for respective process code needs to be created
3548 --------------------------------------------------------------------------
3549 l_process_rec := null ;
3550
3551 l_process_rec.ORG_ID := l_org_id ;
3552 l_process_rec.SUPP_TRADE_PROFILE_ID := p_trade_prf_id ;
3553 l_process_rec.PROCESS_CODE := r_pp_data.processCode ;
3554 l_process_rec.ENABLED_FLAG := r_pp_data.enabledFlag ;
3555 l_process_rec.AUTOMATIC_FLAG := r_pp_data.automaticFlag ;
3556
3557 ----------------------------------------------------------------------------------------
3558 -- SET THE DATA FOR CREATE : END
3559 ----------------------------------------------------------------------------------------
3560 IF g_debug THEN
3561 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Data for create' );
3562 END IF;
3563 l_process_crt_tbl.extend ;
3564 l_process_crt_tbl(l_process_crt_tbl.COUNT) := l_process_rec ;
3565 l_crt_count := l_crt_count +1 ;
3566
3567
3568 END IF ;
3569
3570
3571 END LOOP ;
3572
3573
3574
3575 ----------------------------------------------------
3576 -- Call the private API
3577 ----------------------------------------------------
3578 IF (l_process_crt_tbl.COUNT >0) THEN
3579
3580
3581 OZF_PROCESS_SETUP_PVT.create_process_setup( p_api_version_number => P_Api_Version_Number ,
3582 x_return_status => X_Return_Status ,
3583 x_msg_count => X_Msg_Count ,
3584 x_msg_data => X_Msg_Data ,
3585 p_process_setup_tbl => l_process_crt_tbl,
3586 x_process_setup_id_tbl => l_crt_id_tbl
3587 ) ;
3588
3589 IF g_debug THEN
3590 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private create process setup returned'||x_return_status );
3591 END IF;
3592
3593 IF (x_return_status = FND_API.g_ret_sts_success) THEN
3594
3595 X_created_process_tbl := l_process_crt_tbl;
3596
3597 IF(P_Commit = FND_API.G_TRUE) THEN
3598 COMMIT ;
3599 END IF ;
3600
3601 ELSE
3602 l_exec_err := 'Y' ;
3603 END IF ;
3604
3605 END IF ;
3606
3607 IF (l_process_upd_tbl.COUNT >0) THEN
3608
3609
3610 OZF_PROCESS_SETUP_PVT.Update_Process_Setup_Tbl(
3611 P_Api_Version_Number => P_Api_Version_Number ,
3612 P_Init_Msg_List => P_Init_Msg_List ,
3613 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3614 X_Return_Status => X_Return_Status ,
3615 X_Msg_Count => X_Msg_Count,
3616 X_Msg_Data => X_Msg_Data,
3617 P_process_setup_Tbl => l_process_upd_tbl) ;
3618
3619 IF g_debug THEN
3620 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private Update process setup returned'||x_return_status );
3621 END IF;
3622
3623 IF g_debug THEN
3624 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Message returned'||X_Msg_Data );
3625 END IF;
3626
3627 IF (x_return_status = FND_API.g_ret_sts_success) THEN
3628 X_updated_process_tbl := l_process_upd_tbl ;
3629 IF(P_Commit = FND_API.G_TRUE) THEN
3630 COMMIT ;
3631 END IF ;
3632 ELSE
3633 l_exec_err := 'Y' ;
3634 END IF ;
3635
3636
3637
3638 END IF ;
3639
3640 END IF ;
3641
3642 IF (l_exec_err = 'Y') THEN
3643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3644 END IF ;
3645
3646 EXCEPTION
3647 WHEN OTHERS THEN
3648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3649 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3650 THEN
3651 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3652 END IF;
3653 -- Standard call to get message count and if count=1, get the message
3654 FND_MSG_PUB.Count_And_Get (
3655 p_encoded => FND_API.G_FALSE,
3656 p_count => x_msg_count,
3657 p_data => x_msg_data );
3658
3659 IF g_debug THEN
3660 ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||sqlerrm );
3661 END IF;
3662
3663 END Process_price_protection ;
3664
3665
3666
3667 END OZF_SUPP_TRADE_PROFILE_PUB;
3668
3669