[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_TERMNT_INTERFACE_PVT
Source
1 PACKAGE BODY OKL_AM_TERMNT_INTERFACE_PVT AS
2 /* $Header: OKLRTIFB.pls 120.11.12020000.3 2012/08/28 15:58:03 racheruv ship $ */
3
4 subtype quot_rec_type IS OKL_TRX_QUOTES_PUB.qtev_rec_type;
5 subtype assn_tbl_type IS OKL_AM_CREATE_QUOTE_PUB.assn_tbl_type;
6 subtype tqlv_tbl_type IS OKL_TXL_QUOTE_LINES_PUB.tqlv_tbl_type;
7 subtype qld_tbl_type IS OKL_AM_TERMNT_QUOTE_PUB.qte_ln_dtl_tbl;
8 subtype qpyv_tbl_type IS OKL_QUOTE_PARTIES_PUB.qpyv_tbl_type;
9 subtype tmgv_rec_type IS OKL_TMG_PVT.tmgv_rec_type;
10
11 TYPE msg_rec_type IS RECORD (
12 msg_text VARCHAR2(2000));
13
14 TYPE msg_tbl_type IS TABLE OF msg_rec_type
15 INDEX BY BINARY_INTEGER;
16 -- this record is used to store the messages for a particular line in the log
17 TYPE log_msg_rec_type IS RECORD (
18 transaction_number OKL_TERMNT_INTERFACE.transaction_number%TYPE,
19 msg_text VARCHAR2(2000));
20
21 TYPE log_msg_tbl_type IS TABLE OF log_msg_rec_type
22 INDEX BY BINARY_INTEGER;
23 -- this record is used to store the header inforamtion for particular line in the log
24 TYPE log_rec_type IS RECORD (
25 transaction_number OKL_TERMNT_INTERFACE.transaction_number%TYPE,
26 contract_number OKL_TERMNT_INTERFACE.contract_number%TYPE,
27 asset_number OKL_TERMNT_INTERFACE.asset_number%TYPE,
28 date_effective_from OKL_TERMNT_INTERFACE.date_effective_from%TYPE,
29 quote_type OKL_TERMNT_INTERFACE.quote_type_code%TYPE,
30 quote_reason OKL_TERMNT_INTERFACE.quote_reason_code%TYPE,
31 quote_number NUMBER);
32
33 TYPE log_tbl_type IS TABLE OF log_rec_type
34 INDEX BY BINARY_INTEGER;
35
36 val_log_tbl log_tbl_type;
37 pro_log_tbl log_tbl_type;
38 err_log_tbl log_tbl_type;
39
40 val_msg_tbl log_msg_tbl_type;
41 pro_msg_tbl log_msg_tbl_type;
42 err_msg_tbl log_msg_tbl_type;
43
44 -- PAGARG 23-Feb-05 Declared a table to messages from error stack
45 TYPE error_message_type IS TABLE OF VARCHAR2(2000)
46 INDEX BY BINARY_INTEGER;
47
48 --sechawla 12745559 02-aug-2011 : begin
49 ---------------------------------------------------------------------------
50 -- GLOBAL DEBUG VARIABLES
51 ---------------------------------------------------------------------------
52 G_DEBUG_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
53 G_DEBUG_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
54 G_DEBUG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
55 G_DEBUG_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
56 ---------------------------------------------------------------------------
57 --sechawla 12745559 02-aug-2011 : end
58
59 /**************************************************************************/
60 -- Start of comments
61 --
62 -- Procedure Name : get_error_message
63 -- Description : This procedure unwinds the error messages from stack
64 -- Business Rules :
65 -- Parameters : p_all_message
66 -- Version : 1.0
67 -- History : 23-Feb-2005 PAGARG Created
68 -- End of comments
69 PROCEDURE get_error_message(p_all_message OUT NOCOPY error_message_type)
70 IS
71 l_msg_index_out NUMBER;
72 l_data VARCHAR2(2000);
73 l_counter NUMBER := 0;
74 BEGIN
75 FOR l_counter IN 1..fnd_msg_pub.count_msg
76 LOOP
77 fnd_msg_pub.get
78 (p_data => l_data,
79 p_msg_index_out => l_msg_index_out,
80 p_encoded => FND_API.G_FALSE,
81 p_msg_index => l_counter);
82 p_all_message(l_counter) := l_data;
83 END LOOP;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 NULL;
88 END get_error_message;
89 /**************************************************************************/
90
91 -- Start of comments
92 --
93 -- Procedure Name : log_messages
94 -- Description : This procedure logs the messages to concuurent log, output
95 -- Business Rules :
96 -- Parameters : log_msg_flag, p_tif_rec, msg_text
97 -- Version : 1.0
98 -- History : 18-FEB-03 SPILLAIP Created
99 -- : 27-MAR-03 RABHUPAT modified
100 -- : 22-JUL-03 RABHUPAT modified
101 -- End of comments
102
103 -- Y for putting in log and output,V for not processed, E for processed with Error,P for processed,O for output
104
105 PROCEDURE log_messages(log_msg_flag IN VARCHAR2 DEFAULT '',
106 p_transaction_number IN VARCHAR2 DEFAULT '',
107 p_contract_number IN VARCHAR2 DEFAULT '',
108 p_asset_number IN VARCHAR2 DEFAULT '',
109 p_date_effective IN DATE DEFAULT OKC_API.G_MISS_DATE,
110 p_quote_type IN VARCHAR2 DEFAULT '',
111 p_quote_reason IN VARCHAR2 DEFAULT '',
112 p_quote_number IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
113 msg_tbl IN msg_tbl_type
114 ) IS
115 val_count NUMBER := 0;
116 err_count NUMBER := 0;
117 pro_count NUMBER := 0;
118
119 val_msg_count NUMBER := 0;
120 err_msg_count NUMBER := 0;
121 pro_msg_count NUMBER := 0;
122
123 tot_count NUMBER := 0;
124 count_msg NUMBER := 1;
125 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
126 l_org_name VARCHAR2(40);
127 l_quote_type VARCHAR2(100);
128 l_quote_reason VARCHAR2(100);
129
130
131 -- cursor to retrieve operating_units
132 CURSOR org_csr (l_org_id IN NUMBER) IS
133 SELECT name
134 FROM hr_operating_units
135 WHERE organization_id = l_org_id;
136
137 /* gets the meaning for each quote_type_code from FND_LOOKUPS*/
138 CURSOR get_quote_type_meaning_csr(p_quote_type IN VARCHAR2) IS
139 SELECT meaning
140 FROM FND_LOOKUPS
141 WHERE lookup_code = p_quote_type AND lookup_type = 'OKL_QUOTE_TYPE' AND enabled_flag = 'Y';
142
143 /* gets the meaning for each quote_reason from FND_LOOKUPS*/
144 CURSOR get_quote_reason_meaning_csr(p_quote_reason IN VARCHAR2) IS
145 SELECT meaning
146 FROM FND_LOOKUPS
147 WHERE lookup_code = p_quote_reason AND lookup_type = 'OKL_QUOTE_REASON' AND enabled_flag = 'Y';
148
149 BEGIN
150 -- transactions which are not processed due to errors will be inserted in to val_msg_tbl
151 IF(log_msg_flag = 'V') THEN
152 val_count := val_log_tbl.COUNT + 1;
153 val_log_tbl(val_count).transaction_number := p_transaction_number;
154 val_log_tbl(val_count).contract_number := p_contract_number;
155 val_log_tbl(val_count).asset_number := p_asset_number;
156 val_log_tbl(val_count).date_effective_from := p_date_effective;
157 val_log_tbl(val_count).quote_type := p_quote_type;
158 val_log_tbl(val_count).quote_reason := p_quote_reason;
159
160 IF(msg_tbl.COUNT > 0) THEN
161 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
162 LOOP
163 val_msg_count := val_msg_tbl.COUNT + 1;
164 val_msg_tbl(val_msg_count).transaction_number := p_transaction_number;
165 val_msg_tbl(val_msg_count).msg_text := msg_tbl(i).msg_text;
166 END LOOP;
167 END IF;
168 -- transactions which are processed with errors will be inserted in to err_msg_tbl
169 ELSIF(log_msg_flag = 'E') THEN
170 err_count := err_log_tbl.COUNT + 1;
171 err_log_tbl(err_count).transaction_number := p_transaction_number;
172 err_log_tbl(err_count).contract_number := p_contract_number;
173 err_log_tbl(err_count).asset_number := p_asset_number;
174 err_log_tbl(err_count).date_effective_from := p_date_effective;
175 err_log_tbl(err_count).quote_type := p_quote_type;
176 err_log_tbl(err_count).quote_reason := p_quote_reason;
177 err_log_tbl(err_count).quote_number := p_quote_number;
178
179 IF(msg_tbl.COUNT > 0) THEN
180 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
181 LOOP
182 err_msg_count := err_msg_tbl.COUNT + 1;
183 err_msg_tbl(err_msg_count).transaction_number := p_transaction_number;
184 err_msg_tbl(err_msg_count).msg_text := msg_tbl(i).msg_text;
185 END LOOP;
186 END IF;
187 -- transactions which are processed will be inserted in to pro_msg_tbl
188 ELSIF(log_msg_flag = 'P') THEN
189 pro_count := pro_log_tbl.COUNT + 1;
190 pro_log_tbl(pro_count).transaction_number := p_transaction_number;
191 pro_log_tbl(pro_count).contract_number := p_contract_number;
192 pro_log_tbl(pro_count).asset_number := p_asset_number;
193 pro_log_tbl(pro_count).date_effective_from := p_date_effective;
194 pro_log_tbl(pro_count).quote_type := p_quote_type;
195 pro_log_tbl(pro_count).quote_reason := p_quote_reason;
196 pro_log_tbl(pro_count).quote_number := p_quote_number;
197
198 END IF;
199 -- enter the messages in to LOG
200 IF(log_msg_flag = 'Y') THEN
201 -- cursor to retrieve operating unit name
202 FOR org_rec IN org_csr (l_org_id) LOOP
203 l_org_name := org_rec.name;
204 END LOOP;
205 tot_count := p_quote_number;
206 val_count := val_log_tbl.COUNT;
207 err_count := err_log_tbl.COUNT;
208 pro_count := tot_count-(val_count+err_count);
209 -- loop through the message and write to CM log
210 fnd_file.put_line(fnd_file.log,'====================================');
211 fnd_file.put_line(fnd_file.log,'Termination Interface - LOG MESSAGES');
212 fnd_file.put_line(fnd_file.log,'====================================');
213 fnd_file.put_line(fnd_file.log,'Operating Unit :'||l_org_name);
214 fnd_file.put_line(fnd_file.log,'Run Date :'||SYSDATE);
215 fnd_file.new_line(fnd_file.log);
216 fnd_file.put_line(fnd_file.log,'-----------------------');
217 fnd_file.put_line(fnd_file.log,'SUMMARY OF TRANSACTIONS');
218 fnd_file.put_line(fnd_file.log,'-----------------------');
219 fnd_file.new_line(fnd_file.log);
220 fnd_file.put_line(fnd_file.log,'Processed Successfully : '||pro_count);
221 fnd_file.put_line(fnd_file.log,'Not Processed Due To Errors : '||val_count);
222 fnd_file.put_line(fnd_file.log,'Processed with Errors : '||err_count);
223 fnd_file.new_line(fnd_file.log);
224 fnd_file.put_line(fnd_file.log,'Total Records : '||tot_count);
225 fnd_file.new_line(fnd_file.log);
226 IF (val_log_tbl.COUNT > 0) THEN
227 fnd_file.put_line(fnd_file.log,'----------------------------------------');
228 fnd_file.put_line(fnd_file.log,'TRANSACTIONS NOT PROCESSED DUE TO ERRORS');
229 fnd_file.put_line(fnd_file.log,'----------------------------------------');
230 FOR record_number in val_log_tbl.FIRST..val_log_tbl.LAST LOOP
231 fnd_file.new_line(fnd_file.log);
232 fnd_file.put_line(fnd_file.log,'Transaction Number : '||val_log_tbl(record_number).transaction_number);
233 fnd_file.put_line(fnd_file.log,'Contract Number : '||val_log_tbl(record_number).contract_number);
234 fnd_file.put_line(fnd_file.log,'Quote Effective From Date : '||val_log_tbl(record_number).date_effective_from);
235 -- to find the meaning of quote_type_code
236 l_quote_type := NULL;
237 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => val_log_tbl(record_number).quote_type)
238 LOOP
239 l_quote_type := get_type.meaning;
240 END LOOP;
241 fnd_file.put(fnd_file.log,'Quote Type : ');
242 IF(l_quote_type IS NOT NULL) THEN
243 fnd_file.put(fnd_file.log,l_quote_type);
244 END IF;
245 fnd_file.put_line(fnd_file.log,'( '||val_log_tbl(record_number).quote_type||' )');
246 -- to find the meaning of quote_reason_code
247 l_quote_reason := NULL;
248 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => val_log_tbl(record_number).quote_reason)
249 LOOP
250 l_quote_reason := get_reason.meaning;
251 END LOOP;
252 fnd_file.put(fnd_file.log,'Quote Reason : ');
253 IF(l_quote_reason IS NOT NULL) THEN
254 fnd_file.put(fnd_file.log,l_quote_reason);
255 END IF;
256 fnd_file.put_line(fnd_file.log,'( '||val_log_tbl(record_number).quote_reason||' )');
257 fnd_file.put_line(fnd_file.log,'Asset Number : '||val_log_tbl(record_number).asset_number);
258 fnd_file.put_line(fnd_file.log,'Messages ');
259 count_msg := 1;
260 -- modified for BUG#3062867
261 IF(val_msg_tbl.COUNT>0) THEN
262 FOR i IN val_msg_tbl.FIRST..val_msg_tbl.LAST
263 LOOP
264 IF(val_msg_tbl(i).transaction_number = val_log_tbl(record_number).transaction_number) THEN
265 fnd_file.put_line(fnd_file.log,count_msg||': '||val_msg_tbl(i).msg_text);
266 count_msg := count_msg+1;
267 END IF;
268 END LOOP;
269 END IF;
270
271 fnd_file.new_line(fnd_file.log);
272 END LOOP;
273 END IF; -- val_log_tbl COUNT > 0
274 IF (err_log_tbl.COUNT > 0) THEN
275 fnd_file.put_line(fnd_file.log,'----------------------------------');
276 fnd_file.put_line(fnd_file.log,'TRANSACTIONS PROCESSED WITH ERRORS');
277 fnd_file.put_line(fnd_file.log,'----------------------------------');
278 FOR record_number in err_log_tbl.FIRST..err_log_tbl.LAST LOOP
279 fnd_file.new_line(fnd_file.log);
280 fnd_file.put_line(fnd_file.log,'Transaction Number : '||err_log_tbl(record_number).transaction_number);
281 fnd_file.put_line(fnd_file.log,'Contract Number : '||err_log_tbl(record_number).contract_number);
282 fnd_file.put_line(fnd_file.log,'Quote Effective From Date : '||err_log_tbl(record_number).date_effective_from);
283 -- to find the meaning of quote_type_code
284 l_quote_type := NULL;
285 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => err_log_tbl(record_number).quote_type)
286 LOOP
287 l_quote_type := get_type.meaning;
288 END LOOP;
289 fnd_file.put(fnd_file.log,'Quote Type : ');
290 IF(l_quote_type IS NOT NULL) THEN
291 fnd_file.put(fnd_file.log,l_quote_type);
292 END IF;
293 fnd_file.put_line(fnd_file.log,'( '||err_log_tbl(record_number).quote_type||' )');
294 -- to find the meaning of quote_reason_code
295 l_quote_reason := NULL;
296 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => err_log_tbl(record_number).quote_reason)
297 LOOP
298 l_quote_reason := get_reason.meaning;
299 END LOOP;
300 fnd_file.put(fnd_file.log,'Quote Reason : ');
301 IF(l_quote_reason IS NOT NULL) THEN
302 fnd_file.put(fnd_file.log,l_quote_reason);
303 END IF;
304 fnd_file.put_line(fnd_file.log,'( '||err_log_tbl(record_number).quote_reason||' )');
305 fnd_file.put_line(fnd_file.log,'Asset Number : '||err_log_tbl(record_number).asset_number);
306 fnd_file.put_line(fnd_file.log,'Quote Number : '||err_log_tbl(record_number).quote_number);
307 fnd_file.put_line(fnd_file.log,'Messages ');
308 count_msg := 1;
309
310 -- modified for BUG#3062867
311 IF(err_msg_tbl.COUNT>0) THEN
312 FOR i IN err_msg_tbl.FIRST..err_msg_tbl.LAST
313 LOOP
314 IF(err_msg_tbl(i).transaction_number = err_log_tbl(record_number).transaction_number) THEN
315 fnd_file.put_line(fnd_file.log,count_msg||': '||err_msg_tbl(i).msg_text);
316 count_msg := count_msg+1;
317 END IF;
318 END LOOP;
319 END IF;
320
321 fnd_file.new_line(fnd_file.log);
322 END LOOP;
323 END IF; -- err_log_tbl COUNT > 0
324 count_msg := 1;
325 -- statement came along with flag 'Y'
326 IF(msg_tbl.COUNT>0) THEN
327 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
328 LOOP
329 fnd_file.put_line(fnd_file.log,count_msg ||': '||msg_tbl(i).msg_text);
330 count_msg := count_msg+1;
331 END LOOP;
332 END IF;
333 fnd_file.put_line(fnd_file.log,'=======================================');
334 END IF;
335 -- enter the messages in to OUTPUT
336 IF(log_msg_flag = 'O') THEN
337 tot_count := p_quote_number;
338 val_count := val_log_tbl.COUNT;
339 err_count := err_log_tbl.COUNT;
340 pro_count := tot_count-(val_count+err_count);
341 -- cursor to retrieve operating unit name
342 FOR org_rec IN org_csr (l_org_id) LOOP
343 l_org_name := org_rec.name;
344 END LOOP;
345 -- loop through the message and write to CM log
346 fnd_file.put_line(fnd_file.output,'=======================================');
347 fnd_file.put_line(fnd_file.output,'Termination Interface - OUTPUT MESSAGES');
348 fnd_file.put_line(fnd_file.output,'=======================================');
349 fnd_file.put_line(fnd_file.output,'Operating Unit :'||l_org_name);
350 fnd_file.put_line(fnd_file.output,'Run Date :'||SYSDATE);
351 fnd_file.new_line(fnd_file.output);
352 fnd_file.put_line(fnd_file.output,'-----------------------');
353 fnd_file.put_line(fnd_file.output,'SUMMARY OF TRANSACTIONS');
354 fnd_file.put_line(fnd_file.output,'-----------------------');
355 fnd_file.new_line(fnd_file.output);
356 fnd_file.put_line(fnd_file.output,'Processed Successfully : '||pro_count);
357 fnd_file.put_line(fnd_file.output,'Not Processed Due To Errors : '||val_count);
358 fnd_file.put_line(fnd_file.output,'Processed with Errors : '||err_count);
359 fnd_file.new_line(fnd_file.output);
360 --pro_count := pro_log_tbl.COUNT+val_log_tbl.COUNT+err_log_tbl.COUNT;
361 fnd_file.put_line(fnd_file.output,'Total Records : '||tot_count);
362 fnd_file.new_line(fnd_file.output);
363 IF (val_log_tbl.COUNT > 0) THEN
364 fnd_file.put_line(fnd_file.output,'----------------------------------------');
365 fnd_file.put_line(fnd_file.output,'TRANSACTIONS NOT PROCESSED DUE TO ERRORS');
366 fnd_file.put_line(fnd_file.output,'----------------------------------------');
367 FOR record_number in val_log_tbl.FIRST..val_log_tbl.LAST LOOP
368 fnd_file.new_line(fnd_file.output);
369 fnd_file.put_line(fnd_file.output,'Transaction Number : '||val_log_tbl(record_number).transaction_number);
370 fnd_file.put_line(fnd_file.output,'Contract Number : '||val_log_tbl(record_number).contract_number);
371 fnd_file.put_line(fnd_file.output,'Quote Effective From Date : '||val_log_tbl(record_number).date_effective_from);
372
373 -- to find the meaning of quote_type_code
374 l_quote_type := NULL;
375 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => val_log_tbl(record_number).quote_type)
376 LOOP
377 l_quote_type := get_type.meaning;
378 END LOOP;
379 fnd_file.put(fnd_file.output,'Quote Type : ');
380 IF(l_quote_type IS NOT NULL) THEN
381 fnd_file.put(fnd_file.output,l_quote_type);
382 END IF;
383 fnd_file.put_line(fnd_file.output,'( '||val_log_tbl(record_number).quote_type||' )');
384 -- to find the meaning of quote_reason_code
385 l_quote_reason := NULL;
386 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => val_log_tbl(record_number).quote_reason)
387 LOOP
388 l_quote_reason := get_reason.meaning;
389 END LOOP;
390 fnd_file.put(fnd_file.output,'Quote Reason : ');
391 IF(l_quote_reason IS NOT NULL) THEN
392 fnd_file.put(fnd_file.output,l_quote_reason);
393 END IF;
394 fnd_file.put_line(fnd_file.output,'( '||val_log_tbl(record_number).quote_reason||' )');
395 fnd_file.put_line(fnd_file.output,'Asset Number : '||val_log_tbl(record_number).asset_number);
396 fnd_file.put_line(fnd_file.output,'Messages ');
397 count_msg := 1;
398
399 -- modified for BUG#3062867
400 IF(val_msg_tbl.COUNT>0) THEN
401 FOR i IN val_msg_tbl.FIRST..val_msg_tbl.LAST
402 LOOP
403 IF(val_msg_tbl(i).transaction_number = val_log_tbl(record_number).transaction_number) THEN
404 fnd_file.put_line(fnd_file.output,count_msg||': '||val_msg_tbl(i).msg_text);
405 count_msg := count_msg+1;
406 END IF;
407 END LOOP;
408 END IF;
409
410 fnd_file.new_line(fnd_file.output);
411 END LOOP;
412 END IF; -- val_msg_tbl COUNT > 0
413 IF (err_log_tbl.COUNT > 0) THEN
414 fnd_file.put_line(fnd_file.output,'----------------------------------');
415 fnd_file.put_line(fnd_file.output,'TRANSACTIONS PROCESSED WITH ERRORS');
416 fnd_file.put_line(fnd_file.output,'----------------------------------');
417 FOR record_number in err_log_tbl.FIRST..err_log_tbl.LAST LOOP
418 fnd_file.new_line(fnd_file.output);
419 fnd_file.put_line(fnd_file.output,'Transaction Number : '||err_log_tbl(record_number).transaction_number);
420 fnd_file.put_line(fnd_file.output,'Contract Number : '||err_log_tbl(record_number).contract_number);
421 fnd_file.put_line(fnd_file.output,'Quote Effective From Date : '||err_log_tbl(record_number).date_effective_from);
422 -- to find the meaning of quote_type_code
423 l_quote_type := NULL;
424 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => err_log_tbl(record_number).quote_type)
425 LOOP
426 l_quote_type := get_type.meaning;
427 END LOOP;
428 fnd_file.put(fnd_file.output,'Quote Type : ');
429 IF(l_quote_type IS NOT NULL) THEN
430 fnd_file.put(fnd_file.output,l_quote_type);
431 END IF;
432 fnd_file.put_line(fnd_file.output,'( '||err_log_tbl(record_number).quote_type||' )');
433 -- to find the meaning of quote_reason_code
434 l_quote_reason := NULL;
435 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => err_log_tbl(record_number).quote_reason)
436 LOOP
437 l_quote_reason := get_reason.meaning;
438 END LOOP;
439 fnd_file.put(fnd_file.output,'Quote Reason : ');
440 IF(l_quote_reason IS NOT NULL) THEN
441 fnd_file.put(fnd_file.output,l_quote_reason);
442 END IF;
443 fnd_file.put_line(fnd_file.output,'( '||err_log_tbl(record_number).quote_reason||' )');
444 fnd_file.put_line(fnd_file.output,'Asset Number : '||err_log_tbl(record_number).asset_number);
445 fnd_file.put_line(fnd_file.output,'Quote Number : '||err_log_tbl(record_number).quote_number);
446 fnd_file.put_line(fnd_file.output,'Messages ');
447 count_msg := 1;
448
449 -- modified for BUG#3062867
450 IF(err_msg_tbl.COUNT>0) THEN
451 FOR i IN err_msg_tbl.FIRST..err_msg_tbl.LAST
452 LOOP
453 IF(err_msg_tbl(i).transaction_number = err_log_tbl(record_number).transaction_number) THEN
454 fnd_file.put_line(fnd_file.output,count_msg||': '||err_msg_tbl(i).msg_text);
455 count_msg := count_msg+1;
456 END IF;
457 END LOOP;
458 END IF;
459
460 fnd_file.new_line(fnd_file.output);
461 END LOOP;
462 END IF; -- err_log_tbl COUNT > 0
463 count_msg := 1;
464 IF(msg_tbl.COUNT>0) THEN
465 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
466 LOOP
467 fnd_file.put_line(fnd_file.output,count_msg ||': '||msg_tbl(i).msg_text);
468 count_msg := count_msg+1;
469 END LOOP;
470 END IF;
471 fnd_file.put_line(fnd_file.output,'=======================================');
472 END IF;
473 EXCEPTION
474 WHEN OTHERS THEN
475 -- store SQL error message on message stack for caller
476 OKC_API.set_message(p_app_name => g_app_name,
477 p_msg_name => g_unexpected_error,
478 p_token1 => g_sqlcode_token,
479 p_token1_value => sqlcode,
480 p_token2 => g_sqlerrm_token,
481 p_token2_value => sqlerrm);
482 END log_messages;
483
484 -- Start of comments
485 --
486 -- Procedure Name : validate_quote_type_and_reason
487 -- Description : This procedure checks whether quote_type and quote_reason are valid
488 -- Business Rules :
489 -- Parameters : Input parameters : p_tif_tbl, p_sys_date
490 -- Version : 1.0
491 -- History : 11-MAR-03 RABHUPAT Created
492 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
493 -- End of comments
494
495 PROCEDURE validate_quote_type_and_reason(p_api_version IN NUMBER,
496 p_init_msg_list IN VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2,
499 x_return_status OUT NOCOPY VARCHAR2,
500 p_tif_tbl IN tif_tbl_type,
501 x_tif_tbl OUT NOCOPY tif_tbl_type) IS
502 /* cursor retrives the distinct quote_types from INTERFACE TABLE*/
503 CURSOR get_quote_type_csr IS
504 SELECT DISTINCT(quote_type_code) quote_type
505 FROM OKL_TERMNT_INTERFACE
506 WHERE status = 'ENTERED' AND quote_type_code IS NOT NULL;
507
508 /* validates each quote_type in INTERFACE table against FND_LOOKUPS*/
509 CURSOR validate_quote_type_csr(p_quote_type IN VARCHAR2) IS
510 SELECT COUNT(lookup_code) code
511 FROM FND_LOOKUPS
512 WHERE lookup_code = p_quote_type AND lookup_type = 'OKL_QUOTE_TYPE' AND enabled_flag = 'Y';
513
514 /* cursor retrives the distinct quote_reason from INTERFACE TABLE*/
515 CURSOR get_quote_reason_csr IS
516 SELECT DISTINCT(quote_reason_code) quote_reason
517 FROM OKL_TERMNT_INTERFACE
518 WHERE status = 'ENTERED' AND quote_reason_code IS NOT NULL;
519
520 /* validates each quote_reason in INTERFACE table against FND_LOOKUPS*/
521 CURSOR validate_quote_reason_csr(p_quote_reason IN VARCHAR2) IS
522 SELECT COUNT(lookup_code) code
523 FROM FND_LOOKUPS
524 WHERE lookup_code = p_quote_reason AND lookup_type = 'OKL_QUOTE_REASON' AND enabled_flag = 'Y';
525
526 l_quote_type VARCHAR2(20) :='';
527 l_quote_reason VARCHAR2(30) :='';
528 l_code NUMBER := 0;
529 lp_tif_tbl tif_tbl_type;
530 l_msg_tbl msg_tbl_type;
531 BEGIN
532
533
534 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
535 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_quote_type_reason','Begin(+)');
536 END IF;
537
538 x_return_status := OKC_API.G_RET_STS_SUCCESS;
539 lp_tif_tbl := p_tif_tbl;
540 IF(lp_tif_tbl.COUNT>0) THEN
541
542
543
544 --loops through the interface table records to populate WHO_columns
545 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
546 LOOP
547
548 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
549 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_quote_type_reason.',
550 'who cols populated : ');
551 end if;
552
553 lp_tif_tbl(record_number).CREATION_DATE := SYSDATE;
554 lp_tif_tbl(record_number).CREATED_BY := FND_GLOBAL.USER_ID;
555 END LOOP;
556 END IF;
557 /* cursor retrives the distinct quote_types from INTERFACE TABLE*/
558 FOR term_rec IN get_quote_type_csr
559 LOOP
560 l_quote_type := term_rec.quote_type;
561 l_code := 0;
562 /* validates each quote_type in INTERFACE table against FND_LOOKUPS*/
563 FOR check_type IN validate_quote_type_csr(p_quote_type => l_quote_type)
564 LOOP
565 l_code := check_type.code;
566 END LOOP;
567 /* if quote_type not exists in FND_LOOKUPS then ERROR out corresponding columns */
568 IF(l_code = 0)THEN
569 IF(lp_tif_tbl.COUNT>0) THEN
570 /*loops through the interface table records */
571 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
572 LOOP
573 IF(lp_tif_tbl(record_number).quote_type_code = l_quote_type) THEN
574 -- quote type entered is invalid
575 lp_tif_tbl(record_number).status := 'ERROR';
576 OKC_API.set_message(p_app_name => g_app_name,
577 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
578 p_token1 => 'COL_NAME',
579 p_token1_value => 'QUOTE_TYPE_CODE');
580 l_msg_tbl(0).msg_text := 'quote type: '||l_quote_type||' entered for transaction number: '||lp_tif_tbl(record_number).transaction_number ||' is not valid ';
581 log_messages(log_msg_flag => 'V',
582 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
583 p_contract_number => lp_tif_tbl(record_number).contract_number,
584 p_asset_number => lp_tif_tbl(record_number).asset_number,
585 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
586 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
587 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
588 msg_tbl => l_msg_tbl);
589 END IF;
590 END LOOP;
591 END IF;
592 ELSE
593 IF(lp_tif_tbl.COUNT>0) THEN
594 /*loops through the interface table records */
595 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
596 LOOP
597 IF(lp_tif_tbl(record_number).quote_type_code LIKE 'TER_MAN%') THEN
598 --manual quotes are not allowed
599 lp_tif_tbl(record_number).status := 'ERROR';
600 OKC_API.set_message(p_app_name => g_app_name,
601 p_msg_name => 'OKL_AM_MAN_QUOTE_TYPE',
602 p_token1 => 'TRANSACTION NUMBER',
603 p_token1_value => lp_tif_tbl(record_number).transaction_number);
604 l_msg_tbl(0).msg_text := 'manual quotes not allowed for transaction number '||lp_tif_tbl(record_number).transaction_number;
605 log_messages(log_msg_flag => 'V',
606 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
607 p_contract_number => lp_tif_tbl(record_number).contract_number,
608 p_asset_number => lp_tif_tbl(record_number).asset_number,
609 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
610 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
611 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
612 msg_tbl => l_msg_tbl);
613 --Bug# 3925453: pagarg +++ T and A +++++++ Start ++++++++++
614 ELSIF(lp_tif_tbl(record_number).quote_type_code = 'TER_RELASE_WO_PURCHASE')
615 THEN
616 -- Release quotes are not allowed
617 lp_tif_tbl(record_number).status := 'ERROR';
618 OKC_API.set_message(p_app_name => g_app_name,
619 p_msg_name => 'OKL_AM_TER_INTF_RELEASE_QTE');
620 l_msg_tbl(0).msg_text := 'Creation of Release quote is not allowed for termination interface';
621 log_messages(log_msg_flag => 'V',
622 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
623 p_contract_number => lp_tif_tbl(record_number).contract_number,
624 p_asset_number => lp_tif_tbl(record_number).asset_number,
625 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
626 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
627 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
628 msg_tbl => l_msg_tbl);
629 --Bug# 3925453: pagarg +++ T and A +++++++ End ++++++++++
630 END IF;
631 END LOOP;
632 END IF;
633 END IF;
634 END LOOP;
635
636 /* cursor retrives the distinct quote_reason from INTERFACE TABLE*/
637 FOR term_rec IN get_quote_reason_csr
638 LOOP
639 l_quote_reason := term_rec.quote_reason;
640 l_code := 0;
641 /* validates each quote_reason in INTERFACE table against FND_LOOKUPS*/
642 FOR check_reason IN validate_quote_reason_csr(p_quote_reason => l_quote_reason)
643 LOOP
644 l_code := check_reason.code;
645 END LOOP;
646 /* if quote_reason not exists in FND_LOOKUPS then ERROR out corresponding columns */
647 IF(l_code = 0)THEN
648 IF(lp_tif_tbl.COUNT>0) THEN
649 /*loops through the interface table records */
650 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
651 LOOP
652 IF(lp_tif_tbl(record_number).quote_reason_code = l_quote_reason) THEN
653 -- quote reason entered is invalid
654 lp_tif_tbl(record_number).status := 'ERROR';
655 OKC_API.set_message(p_app_name => g_app_name,
656 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
657 p_token1 => 'COL_NAME',
658 p_token1_value => 'QUOTE_REASON_CODE');
659 l_msg_tbl(0).msg_text := 'quote reason '||l_quote_reason||' entered for transaction number '||lp_tif_tbl(record_number).transaction_number ||' is not valid ';
660 log_messages(log_msg_flag => 'V',
661 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
662 p_contract_number => lp_tif_tbl(record_number).contract_number,
663 p_asset_number => lp_tif_tbl(record_number).asset_number,
664 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
665 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
666 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
667 msg_tbl => l_msg_tbl );
668 END IF;
669 END LOOP;
670 END IF;
671 END IF;
672 END LOOP;
673 x_tif_tbl := lp_tif_tbl;
674
675
676
677 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
678 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_quote_type_reason ','End(-)');
679 END IF;
680 EXCEPTION
681 WHEN OTHERS THEN
682 -- store SQL error message on message stack for caller
683 OKC_API.set_message(p_app_name => g_app_name,
684 p_msg_name => g_unexpected_error,
685 p_token1 => g_sqlcode_token,
686 p_token1_value => sqlcode,
687 p_token2 => g_sqlerrm_token,
688 p_token2_value => sqlerrm);
689 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
690 l_msg_tbl(0).msg_text := 'validate_quote_type_and_reason: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
691 log_messages(log_msg_flag => 'V',
692 msg_tbl => l_msg_tbl );
693 END validate_quote_type_and_reason;
694
695 -- Start of comments
696 --
697 -- Procedure Name : validate_required
698 -- Description : This procedure checks whether required fields are entered or not
699 -- Business Rules :
700 -- Parameters : Input parameters : p_tif_rec, p_sys_date
701 -- Version : 1.0
702 -- History : 04-FEB-03 RABHUPAT Created
703 -- : 14-APR-03 RABHUPAT added validation for auto_accept_yn
704 -- : removed logic for setting default for quote_reason
705 -- : as the column is changed to NOT NULL
706 -- End of comments
707
708 PROCEDURE validate_required(
709 p_api_version IN NUMBER,
710 p_init_msg_list IN VARCHAR2,
711 x_msg_count OUT NOCOPY NUMBER,
712 x_msg_data OUT NOCOPY VARCHAR2,
713 x_return_status OUT NOCOPY VARCHAR2,
714 p_tif_rec IN tif_rec_type,
715 p_sys_date IN DATE,
716 x_tif_rec OUT NOCOPY tif_rec_type) IS
717 message VARCHAR2(200);
718 l_msg_tbl msg_tbl_type;
719 BEGIN
720 x_tif_rec := p_tif_rec;
721 x_return_status := OKC_API.G_RET_STS_SUCCESS;
722 --checks whether asset id or asset number entered or not
723 IF((p_tif_rec.asset_id IS NULL) OR (p_tif_rec.asset_id = OKC_API.G_MISS_NUM)) THEN
724 IF((p_tif_rec.asset_number IS NULL) OR (p_tif_rec.asset_number = OKC_API.G_MISS_CHAR)) THEN
725 x_tif_rec.status := 'ERROR';
726 OKC_API.set_message(p_app_name => g_app_name,
727 p_msg_name => 'OKC_AM_ASSET_REQUIRED',
728 p_token1 => 'CONTRACT_NUMBER',
729 p_token1_value => p_tif_rec.contract_number);
730 message :='asset number and id not entered for contract_number '||p_tif_rec.contract_number;
731 END IF;
732 END IF;
733 IF(x_tif_rec.status <> 'ERROR') THEN
734 /*checks whether asset is serialized or not and if serialized checks whether quantity is one or null
735 for non serialized assets checks whether quantity entered is greater than zero or not*/
736 IF((p_tif_rec.serial_number IS NOT NULL) AND (p_tif_rec.serial_number <> OKC_API.G_MISS_CHAR)) THEN
737 IF((p_tif_rec.units_to_terminate IS NOT NULL) AND (p_tif_rec.units_to_terminate <> OKC_API.G_MISS_NUM) AND (p_tif_rec.units_to_terminate <> 1)) THEN
738 x_tif_rec.status := 'ERROR';
739 OKC_API.set_message(p_app_name => g_app_name,
740 p_msg_name => 'OKL_AM_SER_ASSET_QTY',
741 p_token1 => 'ASSET_NUMBER',
742 p_token1_value => p_tif_rec.asset_number,
743 p_token2 => 'SERIAL_NUMBER',
744 p_token2_value => p_tif_rec.serial_number);
745 message :='serialized asset with serial number '||p_tif_rec.serial_number||' should have quantity as one ';
746 END IF;
747 ELSE
748 -- if asset quantity is less than 1
749 IF(p_tif_rec.units_to_terminate < 1) THEN
750 x_tif_rec.status := 'ERROR';
751 OKC_API.set_message(p_app_name => g_app_name,
752 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
753 p_token1 => 'COL_NAME',
754 p_token1_value => 'UNITS_TO_TERMINATE');
755 message :='asset '||p_tif_rec.asset_number||' '||p_tif_rec.asset_id||' can not have quantity less than one';
756 END IF;
757 END IF;
758 END IF;
759
760 -- defaults date_effective_from to Sysdate if null and error out if past date entered.
761 IF(x_tif_rec.status <> 'ERROR') THEN
762 IF((p_tif_rec.date_effective_from IS NULL) OR (p_tif_rec.date_effective_from = OKC_API.G_MISS_DATE)) THEN
763 x_tif_rec.date_effective_from := p_sys_date;
764 --Bug 4136202 : Commented check for past date as this is now allowed (Effective dated termination impact)
765 /*ELSIF(p_tif_rec.date_effective_from < p_sys_date) THEN
766 x_tif_rec.status := 'ERROR';
767 OKC_API.set_message(p_app_name => g_app_name,
768 p_msg_name => 'OKL_AM_DATE_EFF_FROM_PAST',
769 p_token1 => 'COL_NAME',
770 p_token1_value => 'DATE_EFFECTIVE_FROM');
771 message :='date_effective_from '||p_tif_rec.contract_number||' should not be a past date';*/
772 END IF;
773 END IF;
774 /* defaults the auto_accept_yn to 'N' */
775 IF(x_tif_rec.status <> 'ERROR')THEN
776 IF(x_tif_rec.auto_accept_yn IS NULL OR x_tif_rec.auto_accept_yn = OKC_API.G_MISS_CHAR)THEN
777 x_tif_rec.auto_accept_yn := 'N';
778 -- if some wrong charcter entered default it to 'N'
779 ELSIF(x_tif_rec.auto_accept_yn NOT IN ('Y','N')) THEN
780 x_tif_rec.auto_accept_yn := 'N';
781 END IF;
782 END IF;
783 IF(x_tif_rec.status = 'ERROR')THEN
784 l_msg_tbl(0).msg_text := message;
785 log_messages(log_msg_flag => 'V',
786 p_transaction_number => x_tif_rec.transaction_number,
787 p_contract_number => x_tif_rec.contract_number,
788 p_asset_number => x_tif_rec.asset_number,
789 p_date_effective => x_tif_rec.date_effective_from,
790 p_quote_type => x_tif_rec.quote_type_code,
791 p_quote_reason => x_tif_rec.quote_reason_code,
792 msg_tbl => l_msg_tbl );
793 END IF;
794 OKL_AM_UTIL_PVT.process_messages(
795 p_trx_source_table => 'OKL_TERMNT_INTERFACE',
796 p_trx_id => p_tif_rec.transaction_number,
797 x_return_status => x_return_status);
798
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 -- store SQL error message on message stack for caller
803 OKC_API.set_message(p_app_name => g_app_name,
804 p_msg_name => g_unexpected_error,
805 p_token1 => g_sqlcode_token,
806 p_token1_value => sqlcode,
807 p_token2 => g_sqlerrm_token,
808 p_token2_value => sqlerrm);
809 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
810 l_msg_tbl(0).msg_text := 'validate_required:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
811 log_messages(log_msg_flag => 'V',
812 msg_tbl => l_msg_tbl );
813 END validate_required;
814
815 -- Start of comments
816 --
817 -- Procedure Name : validate_data
818 -- Description : This procedure checks whether data entered is appropriate
819 -- : or not, also populates the remaining columns required
820 -- Business Rules :
821 -- Parameters : Input parameters : p_tif_rec
822 -- Version : 1.0
823 -- History : 04-FEB-03 RABHUPAT Created
824 -- : 28-MAR-03 RABHUPAT added another cursor to retrive quantity
825 -- : and changed the cursor for finding serial number and code
826 -- End of comments
827
828 PROCEDURE validate_data(
829 p_api_version IN NUMBER,
830 p_init_msg_list IN VARCHAR2,
831 x_msg_count OUT NOCOPY NUMBER,
832 x_msg_data OUT NOCOPY VARCHAR2,
833 x_return_status OUT NOCOPY VARCHAR2,
834 p_tif_rec IN tif_rec_type,
835 x_tif_rec OUT NOCOPY tif_rec_type
836 ) IS
837
838 l_serial_number OKL_TERMNT_INTERFACE.serial_number%TYPE;
839 l_contract_id OKL_TERMNT_INTERFACE.contract_id%TYPE;
840 l_contract_status OKC_K_HEADERS_B.sts_code%TYPE;
841 l_asset_number OKL_TERMNT_INTERFACE.asset_number%TYPE;
842 l_asset_id OKL_TERMNT_INTERFACE.asset_id%TYPE;
843 l_asset_description OKL_TERMNT_INTERFACE.asset_description%TYPE;
844 l_asset_status OKL_K_LINES_FULL_V.sts_code%TYPE;
845 l_tif_rec tif_rec_type;
846 l_quantity NUMBER;
847 l_instance_id NUMBER;
848 l_installbase_id NUMBER;
849 l_org_id NUMBER;
850 l_session_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
851 l_count_asset NUMBER := 0;
852 message VARCHAR2(200);
853 l_msg_tbl msg_tbl_type;
854 /*this cursor retrives the contract_id and status from OKC_K_HEADERS_B with contract number
855 as input parameter*/
856 CURSOR get_chr_dtls_csr(p_chr_no IN VARCHAR2) IS
857 SELECT id,sts_code,authoring_org_id
858 FROM okc_k_headers_b
859 WHERE contract_number=p_chr_no;
860 /* this cursor retrives the asset_id,asset_number,asset_status and description from okc_k_lines_v
861 and okc_line_styles_v with contract_id,asset_id,asset_number as input parameters.*/
862 CURSOR get_aset_dtls_csr(p_chr_id IN NUMBER,
863 p_ast_number IN VARCHAR2,
864 p_ast_id IN NUMBER) IS
865 SELECT oklv.id id, oklv.name num, oklv.sts_code status, oklv.item_description description
866 FROM okc_k_lines_v oklv, okc_line_styles_v ols
867 WHERE oklv.chr_id= p_chr_id
868 AND ((oklv.name = p_ast_number)
869 OR (oklv.id = p_ast_id))
870 AND oklv.lse_id=ols.id
871 AND ols.lty_code = 'FREE_FORM1';
872
873 /* this cursor retrives the instance ids for the asset with asset_id as input parameter.*/
874 CURSOR get_instance_id_csr(p_ast_id IN NUMBER) IS
875 SELECT oklv.id id
876 FROM okc_k_lines_v oklv, okc_line_styles_v ols
877 WHERE oklv.cle_id = p_ast_id
878 AND oklv.lse_id=ols.id
879 AND ols.lty_code = 'FREE_FORM2';
880 /*this cursor is used to retrive the installbase id for an instance of the asset*/
881 CURSOR get_installbase_id_csr(p_instance_id IN NUMBER) IS
882 SELECT oklv.id id
883 FROM okc_k_lines_v oklv, okc_line_styles_v ols
884 WHERE oklv.cle_id = p_instance_id
885 AND oklv.lse_id=ols.id
886 AND ols.lty_code = 'INST_ITEM';
887 /* this cursor retrives the serial_number for the instance of the asset*/
888 CURSOR get_sno_csr(p_installbase_id IN NUMBER) IS
889 SELECT oiiv.serial_number sno
890 FROM okc_k_items_v okiv,okx_install_items_v oiiv
891 WHERE okiv.cle_id = p_installbase_id
892 AND okiv.object1_id1=oiiv.instance_id;
893 /* this cursor retrives the total quantity for the asset*/
894 CURSOR get_qty_csr(p_asset_number IN VARCHAR2) IS
895 SELECT current_units quantity
896 FROM OKX_ASSETS_V
897 WHERE asset_number = p_asset_number;
898
899 BEGIN
900 x_tif_rec := p_tif_rec;
901 x_tif_rec.status := 'WORKING';
902 x_return_status := OKC_API.G_RET_STS_SUCCESS;
903 -- fetching the contract_id,status using the cursor
904 FOR l_chr_dtl_csr IN get_chr_dtls_csr(p_chr_no => x_tif_rec.contract_number)
905 LOOP
906 l_contract_id := l_chr_dtl_csr.id;
907 l_contract_status := l_chr_dtl_csr.sts_code;
908 l_org_id := l_chr_dtl_csr.authoring_org_id;
909 END LOOP;
910 -- contract number entered not exists
911 IF(l_contract_id IS NULL OR l_contract_id = OKC_API.G_MISS_NUM) THEN
912 x_tif_rec.status := 'ERROR';
913 OKC_API.set_message(p_app_name => g_app_name,
914 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
915 p_token1 => 'COL_NAME',
916 p_token1_value => 'CONTRACT_NUMBER');
917 message :='contract_number '||x_tif_rec.contract_number||' entered not exists';
918 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
919 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
920 'message 1 '|| message);
921 END IF;
922 ELSIF(x_tif_rec.contract_id IS NULL OR x_tif_rec.contract_id = OKC_API.G_MISS_NUM) THEN
923 x_tif_rec.contract_id := l_contract_id;
924 ELSIF(x_tif_rec.contract_id <> l_contract_id) THEN
925 x_tif_rec.status := 'ERROR';
926 -- contract_id and contract_number entered not matches
927 OKC_API.set_message(p_app_name => g_app_name,
928 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
929 p_token1 => 'COL_NAME',
930 p_token1_value => 'CONTRACT_NUMBER');
931 message :='contract_number '||x_tif_rec.contract_number||' and contract_id '||x_tif_rec.contract_id ||' entered not matches';
932 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
933 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
934 'message 2 '|| message);
935 END IF;
936 END IF;
937 IF(x_tif_rec.status <> 'ERROR' AND l_contract_status NOT IN('BOOKED','EVERGREEN')) THEN
938 x_tif_rec.status := 'ERROR';
939 -- contract status is not in BOOKED or EVERGREEN
940 -- this message is striked off in the DLD
941 OKC_API.set_message(p_app_name => g_app_name,
942 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
943 p_token1 => 'COL_NAME',
944 p_token1_value => 'CONTRACT_NUMBER');
945 message :='contract_number '||x_tif_rec.contract_number||' is not in booked state or in evergreen state';
946 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
947 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
948 'message 3 '|| message);
949 END IF;
950 END IF;
951
952 IF(x_tif_rec.status <> 'ERROR') THEN
953 --fetching the asset details using the cursor
954 FOR l_ast_dtl_csr IN get_aset_dtls_csr(p_chr_id => x_tif_rec.contract_id
955 ,p_ast_number => x_tif_rec.asset_number
956 ,p_ast_id => x_tif_rec.asset_id)
957 LOOP
958 l_asset_id := l_ast_dtl_csr.id;
959 l_asset_number := l_ast_dtl_csr.num;
960 x_tif_rec.asset_description := l_ast_dtl_csr.description;
961 l_asset_status := l_ast_dtl_csr.status;
962 l_count_asset := l_count_asset+1;
963 END LOOP;
964
965 IF(l_count_asset >1) THEN
966 x_tif_rec.status := 'ERROR';
967 -- asset number and asset id not matches
968 OKC_API.set_message(p_app_name => g_app_name,
969 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
970 p_token1 => 'COL_NAME',
971 p_token1_value => 'ASSET_NUMBER');
972 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
973
974 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
975 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
976 'message 4'|| message);
977 END IF;
978 ELSIF(l_asset_id IS NULL OR l_asset_id = OKC_API.G_MISS_NUM) THEN
979 x_tif_rec.status := 'ERROR';
980 -- asset is not associated to contract
981 OKC_API.set_message(p_app_name => g_app_name,
982 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
983 p_token1 => 'COL_NAME',
984 p_token1_value => 'ASSET_NUMBER');
985 message :='asset is not associated to the contract_number '||x_tif_rec.contract_number||' entered';
986 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
988 'message 5 '|| message);
989 END IF;
990 ELSIF(l_asset_number IS NULL OR l_asset_number = OKC_API.G_MISS_CHAR) THEN
991 x_tif_rec.status := 'ERROR';
992 -- asset is not associated to contract
993 OKC_API.set_message(p_app_name => g_app_name,
994 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
995 p_token1 => 'COL_NAME',
996 p_token1_value => 'ASSET_NUMBER');
997 message :='asset is not associated to the contract_number '||x_tif_rec.contract_number||' entered';
998
999 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1000 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1001 'message 6 '|| message);
1002 END IF;
1003 ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
1004 IF(p_tif_rec.asset_number <> l_asset_number) THEN
1005 x_tif_rec.status := 'ERROR';
1006 -- asset id and asset number entered not matches
1007 OKC_API.set_message(p_app_name => g_app_name,
1008 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1009 p_token1 => 'COL_NAME',
1010 p_token1_value => 'ASSET_NUMBER');
1011 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1012 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1014 'message 7 '|| message);
1015 END IF;
1016 ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
1017 IF(p_tif_rec.asset_id <> l_asset_id) THEN
1018 x_tif_rec.status := 'ERROR';
1019 -- asset id and asset number entered not matches
1020 OKC_API.set_message(p_app_name => g_app_name,
1021 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1022 p_token1 => 'COL_NAME',
1023 p_token1_value => 'ASSET_NUMBER');
1024 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1025 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1026 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1027 'message 8 '|| message);
1028 END IF;
1029 END IF;
1030 ELSE
1031 x_tif_rec.asset_id := l_asset_id;
1032 END IF;
1033 ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
1034 IF(p_tif_rec.asset_id <> l_asset_id) THEN
1035 x_tif_rec.status := 'ERROR';
1036 -- asset id and asset number entered not matches
1037 OKC_API.set_message(p_app_name => g_app_name,
1038 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1039 p_token1 => 'COL_NAME',
1040 p_token1_value => 'ASSET_NUMBER');
1041 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1042 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1043 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1044 'message 9 '|| message);
1045 END IF;
1046 ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
1047 IF(p_tif_rec.asset_number <> l_asset_number) THEN
1048 x_tif_rec.status := 'ERROR';
1049 -- asset id and asset number entered not matches
1050 OKC_API.set_message(p_app_name => g_app_name,
1051 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1052 p_token1 => 'COL_NAME',
1053 p_token1_value => 'ASSET_NUMBER');
1054 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1055
1056 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1057 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1058 'message 10 '|| message);
1059 END IF;
1060 END IF;
1061 ELSE
1062 x_tif_rec.asset_number := l_asset_number;
1063 END IF;
1064 ELSIF(l_asset_status NOT IN('BOOKED','EVERGREEN')) THEN
1065 x_tif_rec.status := 'ERROR';
1066 -- asset status is not in BOOKED or EVERGREEN
1067 OKC_API.set_message(p_app_name => g_app_name,
1068 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1069 p_token1 => 'COL_NAME',
1070 p_token1_value => 'ASSET_NUMBER');
1071 message :='asset_number '||x_tif_rec.asset_number||' is not in booked state or in evergreen state';
1072 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1073 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1074 'message 11 '|| message);
1075 END IF;
1076 END IF;
1077 IF(x_tif_rec.status <> 'ERROR') THEN
1078 IF(x_tif_rec.serial_number IS NOT NULL AND x_tif_rec.serial_number <> OKC_API.G_MISS_CHAR) THEN
1079 x_tif_rec.status := 'ENTERED';
1080 --fetching instance_id using cursor
1081 FOR l_instance_id_csr IN get_instance_id_csr(p_ast_id => x_tif_rec.asset_id)
1082 LOOP
1083 l_instance_id := l_instance_id_csr.id;
1084 IF(l_instance_id IS NULL OR l_instance_id = OKC_API.G_MISS_NUM) THEN
1085 x_tif_rec.status := 'ERROR';
1086 -- instance line not present for the asset
1087 OKC_API.set_message(p_app_name => g_app_name,
1088 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1089 p_token1 => 'COL_NAME',
1090 p_token1_value => 'SERIAL_NUMBER');
1091 message :='instance line is not present for the asset '||x_tif_rec.asset_number;
1092 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1093 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1094 'message 12 '|| message);
1095 END IF;
1096 ELSE
1097 --fetch installbase_id using cursor
1098 FOR l_installbase_id_csr IN get_installbase_id_csr(p_instance_id => l_instance_id)
1099 LOOP
1100 l_installbase_id := l_installbase_id_csr.id;
1101 END LOOP;
1102 IF(l_installbase_id IS NULL OR l_installbase_id = OKC_API.G_MISS_NUM) THEN
1103 x_tif_rec.status := 'ERROR';
1104 -- installbase has no entries for the instance
1105 OKC_API.set_message(p_app_name => g_app_name,
1106 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1107 p_token1 => 'COL_NAME',
1108 p_token1_value => 'SERIAL_NUMBER');
1109 message :='installbase line is not present for the asset '||x_tif_rec.asset_number;
1110 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1111 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1112 'message 13 '|| message);
1113 END IF;
1114 EXIT;
1115 ELSE
1116 -- cursor to find serial number
1117 FOR l_sno_csr IN get_sno_csr(p_installbase_id => l_installbase_id)
1118 LOOP
1119 l_serial_number := l_sno_csr.sno;
1120 END LOOP;
1121 IF(l_serial_number = x_tif_rec.serial_number) THEN
1122 x_tif_rec.status := 'WORKING';
1123 x_tif_rec.units_to_terminate := 1;
1124 EXIT;
1125 ELSIF(l_serial_number IS NULL OR l_serial_number = OKC_API.G_MISS_CHAR) THEN
1126 x_tif_rec.status := 'ERROR';
1127 -- asset is not serialized
1128 OKC_API.set_message(p_app_name => g_app_name,
1129 p_msg_name => 'OKC_AM_NO_SERIALIZED_ASSET',
1130 p_token1 => 'COL_NAME',
1131 p_token1_value => 'ASSET_NUMBER');
1132 message :='asset is not serialized '||x_tif_rec.asset_number;
1133 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1134 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1135 'message 14 '|| message);
1136 END IF;
1137 EXIT;
1138 END IF;
1139 END IF;
1140 END IF;
1141 END LOOP;
1142 IF(x_tif_rec.status = 'ENTERED') THEN
1143 x_tif_rec.status := 'ERROR';
1144 -- enter serial number associated for this asset
1145 OKC_API.set_message(p_app_name => g_app_name,
1146 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1147 p_token1 => 'COL_NAME',
1148 p_token1_value => 'SERIAL_NUMBER');
1149 message :='Enter serial number associated for this asset '||x_tif_rec.asset_number;
1150 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1151 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1152 'message 15 '|| message);
1153 END IF;
1154 END IF;
1155 ELSE
1156 -- cursor to get the asset quantity
1157 FOR l_qty_csr IN get_qty_csr(p_asset_number => x_tif_rec.asset_number)
1158 LOOP
1159 l_quantity := l_qty_csr.quantity;
1160 END LOOP;
1161 IF(l_quantity < x_tif_rec.units_to_terminate) THEN
1162 x_tif_rec.status := 'ERROR';
1163 -- quantity entered is more than asset quantity
1164 OKC_API.set_message(p_app_name => g_app_name,
1165 p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1166 p_token1 => 'ASSET_NUMBER',
1167 p_token1_value => x_tif_rec.asset_number);
1168 message :='no.of units entered to terminate is more than the quantity associated with asset '||x_tif_rec.asset_number;
1169 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1170 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1171 'message 16 '|| message);
1172 END IF;
1173 END IF;
1174 END IF;
1175 END IF; -- instance line
1176 END IF; -- asset if
1177 IF(x_tif_rec.status <> 'ERROR') THEN
1178 -- validate the session ORG_ID with the contract authoring_org_id, if org_id null then assign authoring_org_id
1179 IF((p_tif_rec.org_id IS NULL) OR (p_tif_rec.org_id = OKC_API.G_MISS_NUM)) THEN
1180 x_tif_rec.org_id := l_org_id;
1181 END IF;
1182 IF(x_tif_rec.org_id <> l_session_org_id) THEN
1183 x_tif_rec.status := 'ENTERED';
1184 -- org_id not matches with session org_id so not processing it, but status remains in ENTERED
1185 OKC_API.set_message(p_app_name => g_app_name,
1186 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1187 p_token1 => 'COL_NAME',
1188 p_token1_value => 'ORG_ID');
1189 message :='org id is not for this session, not errored out';
1190 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1191 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1192 'message 17 '|| message);
1193 END IF;
1194 END IF;
1195 END IF;
1196
1197 --Bug #3921591: pagarg +++ Rollover +++++++ Start ++++++++++
1198 --------------------------
1199 -- Validation of quote type and auto_accepted_yn flag
1200 --------------------------
1201 -- If quote type is rollover quote and auto_accepted_yn is 'Y' then throw
1202 -- error as rollover quote can be accepted from rolled over contract only
1203 IF(x_tif_rec.status <> 'ERROR')
1204 THEN
1205 -- Check if quote type is rollover quote and auto_accept_yn is 'Y' then
1206 -- set the status of record to ERROR and store the error message
1207 IF p_tif_rec.quote_type_code LIKE 'TER_ROLL%'
1208 AND p_tif_rec.auto_accept_yn = 'Y'
1209 THEN
1210 x_tif_rec.status := 'ERROR';
1211 OKC_API.set_message(p_app_name => g_app_name,
1212 p_msg_name => 'OKL_NO_ACPT_ROLL_QTE');
1213 message := 'Rollover quotes can only be accepted from booking process of a rolled over contract';
1214 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1215 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1216 'message 18 '|| message);
1217 END IF;
1218 END IF;
1219 END IF;
1220 --Bug #3921591: pagarg +++ Rollover +++++++ End ++++++++++
1221
1222 IF(x_tif_rec.status = 'ERROR')THEN
1223 l_msg_tbl(0).msg_text := message;
1224 log_messages(log_msg_flag => 'V',
1225 p_transaction_number => x_tif_rec.transaction_number,
1226 p_contract_number => x_tif_rec.contract_number,
1227 p_asset_number => x_tif_rec.asset_number,
1228 p_date_effective => x_tif_rec.date_effective_from,
1229 p_quote_type => x_tif_rec.quote_type_code,
1230 p_quote_reason => x_tif_rec.quote_reason_code,
1231 msg_tbl => l_msg_tbl );
1232 END IF;
1233 OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
1234 p_trx_id => p_tif_rec.transaction_number,
1235 x_return_status => x_return_status);
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 -- store SQL error message on message stack for caller
1239 OKC_API.set_message(p_app_name => g_app_name,
1240 p_msg_name => g_unexpected_error,
1241 p_token1 => g_sqlcode_token,
1242 p_token1_value => sqlcode,
1243 p_token2 => g_sqlerrm_token,
1244 p_token2_value => sqlerrm);
1245 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1246 l_msg_tbl(0).msg_text := 'validate_data: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1247 log_messages(log_msg_flag => 'V',
1248 msg_tbl => l_msg_tbl );
1249 END validate_data;
1250
1251 -- Start of comments
1252 --
1253 -- Procedure Name : validate_record
1254 -- Description : This procedure calls validate_required and
1255 -- : validate_data procedures
1256 -- Business Rules :
1257 -- Parameters : Input parameters : p_tif_rec
1258 -- Version : 1.0
1259 -- History : 04-FEB-03 RABHUPAT Created
1260 -- : 28-MAR-03 RABHUPAT removed parameters for counting successful
1261 -- : transactions for validate_required and validate_data
1262 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
1263 -- End of comments
1264
1265 PROCEDURE validate_record(
1266 p_api_version IN NUMBER,
1267 p_init_msg_list IN VARCHAR2,
1268 x_msg_count OUT NOCOPY NUMBER,
1269 x_msg_data OUT NOCOPY VARCHAR2,
1270 x_return_status OUT NOCOPY VARCHAR2,
1271 p_tif_rec IN tif_rec_type,
1272 p_sys_date IN DATE,
1273 x_tif_rec OUT NOCOPY tif_rec_type
1274 ) IS
1275 l_tif_rec tif_rec_type;
1276 l_msg_tbl msg_tbl_type;
1277 BEGIN
1278
1279 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1280 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_record','Begin(+)');
1281 END IF;
1282
1283 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1284 -- this procedure checks whether required fields are entered or not
1285 validate_required(p_api_version => p_api_version,
1286 p_init_msg_list => OKC_API.G_FALSE,
1287 x_msg_count => x_msg_count,
1288 x_msg_data => x_msg_data,
1289 x_return_status => x_return_status,
1290 p_sys_date => p_sys_date,
1291 p_tif_rec => p_tif_rec,
1292 x_tif_rec => x_tif_rec);
1293 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1294 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_record.',
1295 'validate_required :'||x_return_status);
1296 end if;
1297
1298 IF(x_tif_rec.status <> 'ERROR') THEN
1299 BEGIN
1300 l_tif_rec := x_tif_rec;
1301 /*this procedure validates the data against the database and also
1302 populates the remaining columns */
1303 validate_data(p_api_version => p_api_version,
1304 p_init_msg_list => OKC_API.G_FALSE,
1305 x_msg_count => x_msg_count,
1306 x_msg_data => x_msg_data,
1307 x_return_status => x_return_status,
1308 p_tif_rec => l_tif_rec,
1309 x_tif_rec => x_tif_rec);
1310
1311 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1312 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_record.',
1313 'validate_data :'||x_return_status);
1314 end if;
1315
1316 END;
1317 END IF;
1318 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1319 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_record ','End(-)');
1320 END IF;
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 -- store SQL error message on message stack for caller
1324 OKC_API.set_message(p_app_name => g_app_name,
1325 p_msg_name => g_unexpected_error,
1326 p_token1 => g_sqlcode_token,
1327 p_token1_value => sqlcode,
1328 p_token2 => g_sqlerrm_token,
1329 p_token2_value => sqlerrm);
1330 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1331 l_msg_tbl(0).msg_text := 'validate_record: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1332 log_messages(log_msg_flag => 'V',
1333 msg_tbl => l_msg_tbl );
1334 END validate_record;
1335
1336 -- Start of comments
1337 --
1338 -- Procedure Name : validate_transaction
1339 -- Description : this procedure accepts table of records and passes
1340 -- : each record to the validate_record
1341 -- Business Rules :
1342 -- Parameters : Input parameters : p_tif_tbl
1343 -- Version : 1.0
1344 -- History : 04-FEB-03 RABHUPAT Created
1345 -- : 28-MAR-03 RABHUPAT removed parameters for counting successful
1346 -- : transactions for validate_required and validate_data
1347 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
1348
1349 -- End of comments
1350
1351 PROCEDURE validate_transaction(
1352 p_api_version IN NUMBER,
1353 p_init_msg_list IN VARCHAR2,
1354 x_msg_count OUT NOCOPY NUMBER,
1355 x_msg_data OUT NOCOPY VARCHAR2,
1356 x_return_status OUT NOCOPY VARCHAR2,
1357 p_tif_tbl IN tif_tbl_type,
1358 p_sys_date IN DATE,
1359 x_tif_tbl OUT NOCOPY tif_tbl_type
1360 ) IS
1361 record_number NUMBER;
1362 l_msg_tbl msg_tbl_type;
1363 BEGIN
1364 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1365 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_transaction','Begin(+)');
1366 END IF;
1367
1368
1369 record_number:=0;
1370 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1371 IF (p_tif_tbl.COUNT > 0) THEN
1372 record_number := p_tif_tbl.FIRST;
1373 LOOP
1374 IF(p_tif_tbl(record_number).status <> 'ERROR') THEN
1375 -- this procedure validates the record
1376
1377
1378
1379 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1380 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_transaction.',
1381 'calling validate_record..for rec number ..'||record_number);
1382 end if;
1383
1384 validate_record(p_api_version => p_api_version,
1385 p_init_msg_list => OKC_API.G_FALSE,
1386 x_msg_count => x_msg_count,
1387 x_msg_data => x_msg_data,
1388 x_return_status => x_return_status,
1389 p_sys_date => p_sys_date,
1390 p_tif_rec => p_tif_tbl(record_number),
1391 x_tif_rec => x_tif_tbl(record_number));
1392
1393 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1394 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_transaction.',
1395 'after validate_record..for rec number ..'||record_number||' '||x_return_status);
1396 end if;
1397
1398 ELSE
1399 x_tif_tbl(record_number) := p_tif_tbl(record_number);
1400 END IF;
1401 EXIT WHEN (record_number = p_tif_tbl.LAST);
1402 record_number := p_tif_tbl.NEXT(record_number);
1403 END LOOP;
1404 END IF;
1405
1406
1407 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1408 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_transaction ','End(-)');
1409 END IF;
1410 EXCEPTION
1411 WHEN OTHERS THEN
1412 -- store SQL error message on message stack for caller
1413 OKC_API.set_message(p_app_name => g_app_name,
1414 p_msg_name => g_unexpected_error,
1415 p_token1 => g_sqlcode_token,
1416 p_token1_value => sqlcode,
1417 p_token2 => g_sqlerrm_token,
1418 p_token2_value => sqlerrm);
1419 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1420 l_msg_tbl(0).msg_text := 'validate_transaction:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1421 log_messages(log_msg_flag => 'V',
1422 msg_tbl => l_msg_tbl );
1423 END validate_transaction;
1424
1425 -- Start of comments
1426 --
1427 -- Procedure Name : update_row
1428 -- Description : This procedure updates the INTERFACE table with
1429 -- : modified column values
1430 -- Business Rules :
1431 -- Parameters : Input parameters : p_tif_rec
1432 -- Version : 1.0
1433 -- History : 04-FEB-03 RABHUPAT Created
1434 -- : 28-MAR-03 RABHUPAT added WHO column updation logic
1435 -- End of comments
1436
1437
1438 PROCEDURE update_row(p_api_version IN NUMBER,
1439 p_init_msg_list IN VARCHAR2,
1440 x_msg_count OUT NOCOPY NUMBER,
1441 x_msg_data OUT NOCOPY VARCHAR2,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 p_tif_rec IN tif_rec_type) IS
1444 l_api_version CONSTANT NUMBER := p_api_version;
1445 l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
1446 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1447 l_msg_tbl msg_tbl_type;
1448 BEGIN
1449 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1450 --Check API version, initialize message list and create savepoint.
1451 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1452 G_PKG_NAME,
1453 p_init_msg_list,
1454 l_api_version,
1455 p_api_version,
1456 '_PVT',
1457 x_return_status);
1458 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1459 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1461 RAISE OKC_API.G_EXCEPTION_ERROR;
1462 END IF;
1463
1464 UPDATE OKL_TERMNT_INTERFACE
1465 SET
1466 OKL_TERMNT_INTERFACE.TRANSACTION_NUMBER = p_tif_rec.transaction_number
1467 ,OKL_TERMNT_INTERFACE.BATCH_NUMBER = p_tif_rec.batch_number
1468 ,OKL_TERMNT_INTERFACE.CONTRACT_ID = p_tif_rec.contract_id
1469 ,OKL_TERMNT_INTERFACE.CONTRACT_NUMBER = p_tif_rec.contract_number
1470 ,OKL_TERMNT_INTERFACE.ASSET_ID = p_tif_rec.asset_id
1471 ,OKL_TERMNT_INTERFACE.ASSET_NUMBER = p_tif_rec.asset_number
1472 ,OKL_TERMNT_INTERFACE.ASSET_DESCRIPTION = p_tif_rec.asset_description
1473 ,OKL_TERMNT_INTERFACE.SERIAL_NUMBER = p_tif_rec.serial_number
1474 ,OKL_TERMNT_INTERFACE.ORIG_SYSTEM = p_tif_rec.orig_system
1475 ,OKL_TERMNT_INTERFACE.ORIG_SYSTEM_REFERENCE = p_tif_rec.orig_system_reference
1476 ,OKL_TERMNT_INTERFACE.UNITS_TO_TERMINATE = p_tif_rec.units_to_terminate
1477 ,OKL_TERMNT_INTERFACE.COMMENTS = p_tif_rec.comments
1478 ,OKL_TERMNT_INTERFACE.DATE_PROCESSED = p_tif_rec.date_processed
1479 ,OKL_TERMNT_INTERFACE.DATE_EFFECTIVE_FROM = p_tif_rec.date_effective_from
1480 ,OKL_TERMNT_INTERFACE.TERMINATION_NOTIFICATION_EMAIL = p_tif_rec.termination_notification_email
1481 ,OKL_TERMNT_INTERFACE.TERMINATION_NOTIFICATION_YN = p_tif_rec.termination_notification_yn
1482 ,OKL_TERMNT_INTERFACE.AUTO_ACCEPT_YN = p_tif_rec.auto_accept_yn
1483 ,OKL_TERMNT_INTERFACE.QUOTE_TYPE_CODE = p_tif_rec.quote_type_code
1484 ,OKL_TERMNT_INTERFACE.QUOTE_REASON_CODE = p_tif_rec.quote_reason_code
1485 ,OKL_TERMNT_INTERFACE.QTE_ID = p_tif_rec.qte_id
1486 ,OKL_TERMNT_INTERFACE.STATUS = p_tif_rec.status
1487 ,OKL_TERMNT_INTERFACE.ORG_ID = p_tif_rec.org_id
1488 ,OKL_TERMNT_INTERFACE.REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1, NULL,FND_GLOBAL.CONC_REQUEST_ID),p_tif_rec.request_id)
1489 ,OKL_TERMNT_INTERFACE.PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),p_tif_rec.program_application_id)
1490 ,OKL_TERMNT_INTERFACE.PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),p_tif_rec.program_id)
1491 ,OKL_TERMNT_INTERFACE.PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,p_tif_rec.program_update_date,SYSDATE)
1492 ,OKL_TERMNT_INTERFACE.ATTRIBUTE_CATEGORY = p_tif_rec.attribute_category
1493 ,OKL_TERMNT_INTERFACE.ATTRIBUTE1 = p_tif_rec.attribute1
1494 ,OKL_TERMNT_INTERFACE.ATTRIBUTE2 = p_tif_rec.attribute2
1495 ,OKL_TERMNT_INTERFACE.ATTRIBUTE3 = p_tif_rec.attribute3
1496 ,OKL_TERMNT_INTERFACE.ATTRIBUTE4 = p_tif_rec.attribute4
1497 ,OKL_TERMNT_INTERFACE.ATTRIBUTE5 = p_tif_rec.attribute5
1498 ,OKL_TERMNT_INTERFACE.ATTRIBUTE6 = p_tif_rec.attribute6
1499 ,OKL_TERMNT_INTERFACE.ATTRIBUTE7 = p_tif_rec.attribute7
1500 ,OKL_TERMNT_INTERFACE.ATTRIBUTE8 = p_tif_rec.attribute8
1501 ,OKL_TERMNT_INTERFACE.ATTRIBUTE9 = p_tif_rec.attribute9
1502 ,OKL_TERMNT_INTERFACE.ATTRIBUTE10 = p_tif_rec.attribute10
1503 ,OKL_TERMNT_INTERFACE.ATTRIBUTE11 = p_tif_rec.attribute11
1504 ,OKL_TERMNT_INTERFACE.ATTRIBUTE12 = p_tif_rec.attribute12
1505 ,OKL_TERMNT_INTERFACE.ATTRIBUTE13 = p_tif_rec.attribute13
1506 ,OKL_TERMNT_INTERFACE.ATTRIBUTE14 = p_tif_rec.attribute14
1507 ,OKL_TERMNT_INTERFACE.ATTRIBUTE15 = p_tif_rec.attribute15
1508 ,OKL_TERMNT_INTERFACE.CREATED_BY = p_tif_rec.created_by
1509 ,OKL_TERMNT_INTERFACE.CREATION_DATE = p_tif_rec.creation_date
1510 ,OKL_TERMNT_INTERFACE.LAST_UPDATED_BY = FND_GLOBAL.USER_ID
1511 ,OKL_TERMNT_INTERFACE.LAST_UPDATE_DATE = SYSDATE
1512 ,OKL_TERMNT_INTERFACE.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1513 ,OKL_TERMNT_INTERFACE.GROUP_NUMBER = p_tif_rec.group_number
1514 WHERE
1515 OKL_TERMNT_INTERFACE.ROWID =p_tif_rec.row_id;
1516 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1517
1518 OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
1519 p_trx_id => p_tif_rec.transaction_number,
1520 x_return_status => l_return_status);
1521 x_return_status := l_return_status;
1522 EXCEPTION
1523 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1524 x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1525 G_PKG_NAME,
1526 'OKC_API.G_RET_STS_ERROR',
1527 x_msg_count,
1528 x_msg_data,
1529 '_PVT');
1530 --unexpected error
1531 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1532 x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1533 G_PKG_NAME,
1534 'OKC_API.G_RET_STS_UNEXP_ERROR',
1535 x_msg_count,
1536 x_msg_data,
1537 '_PVT');
1538 WHEN OTHERS THEN
1539 -- store SQL error message on message stack for caller
1540 OKC_API.set_message(p_app_name => g_app_name,
1541 p_msg_name => g_unexpected_error,
1542 p_token1 => g_sqlcode_token,
1543 p_token1_value => sqlcode,
1544 p_token2 => g_sqlerrm_token,
1545 p_token2_value => sqlerrm);
1546 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1547 l_msg_tbl(0).msg_text := 'update_row:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1548 log_messages(log_msg_flag => 'V',
1549 msg_tbl => l_msg_tbl );
1550 END update_row;
1551
1552 -- Start of comments
1553 --
1554 -- Procedure Name : change_status
1555 -- Description : This procedure calls the update_row, also divides the
1556 -- : Entries into batches
1557 -- Business Rules :
1558 -- Parameters : Input parameters : p_tif_tbl
1559 -- Version : 1.0
1560 -- History : 04-FEB-03 RABHUPAT Created
1561 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
1562 -- End of comments
1563
1564 PROCEDURE change_status(
1565 p_api_version IN NUMBER,
1566 p_init_msg_list IN VARCHAR2,
1567 x_msg_count OUT NOCOPY NUMBER,
1568 x_msg_data OUT NOCOPY VARCHAR2,
1569 x_return_status OUT NOCOPY VARCHAR2,
1570 p_tif_tbl IN tif_tbl_type,
1571 x_tif_tbl OUT NOCOPY tif_tbl_type
1572 ) IS
1573 record_number NUMBER;
1574 l_msg_tbl msg_tbl_type;
1575 /* this cursor rearranges the data by using the columns contract_number,
1576 quote_type_code, quote_reason_code and asset_id */
1577 CURSOR get_termnt_intface_dtls_csr(p_status IN VARCHAR2) IS
1578 SELECT
1579 ROWID
1580 ,TRANSACTION_NUMBER
1581 ,BATCH_NUMBER
1582 ,CONTRACT_ID
1583 ,CONTRACT_NUMBER
1584 ,ASSET_ID
1585 ,ASSET_NUMBER
1586 ,ASSET_DESCRIPTION
1587 ,SERIAL_NUMBER
1588 ,ORIG_SYSTEM
1589 ,ORIG_SYSTEM_REFERENCE
1590 ,UNITS_TO_TERMINATE
1591 ,COMMENTS
1592 ,DATE_PROCESSED
1593 ,DATE_EFFECTIVE_FROM
1594 ,TERMINATION_NOTIFICATION_EMAIL
1595 ,TERMINATION_NOTIFICATION_YN
1596 ,AUTO_ACCEPT_YN
1597 ,QUOTE_TYPE_CODE
1598 ,QUOTE_REASON_CODE
1599 ,QTE_ID
1600 ,STATUS
1601 ,ORG_ID
1602 ,REQUEST_ID
1603 ,PROGRAM_APPLICATION_ID
1604 ,PROGRAM_ID
1605 ,PROGRAM_UPDATE_DATE
1606 ,ATTRIBUTE_CATEGORY
1607 ,ATTRIBUTE1
1608 ,ATTRIBUTE2
1609 ,ATTRIBUTE3
1610 ,ATTRIBUTE4
1611 ,ATTRIBUTE5
1612 ,ATTRIBUTE6
1613 ,ATTRIBUTE7
1614 ,ATTRIBUTE8
1615 ,ATTRIBUTE9
1616 ,ATTRIBUTE10
1617 ,ATTRIBUTE11
1618 ,ATTRIBUTE12
1619 ,ATTRIBUTE13
1620 ,ATTRIBUTE14
1621 ,ATTRIBUTE15
1622 ,CREATED_BY
1623 ,CREATION_DATE
1624 ,LAST_UPDATED_BY
1625 ,LAST_UPDATE_DATE
1626 ,LAST_UPDATE_LOGIN
1627 ,GROUP_NUMBER
1628 FROM OKL_TERMNT_INTERFACE
1629 WHERE status = p_status
1630 ORDER BY CONTRACT_NUMBER,QUOTE_TYPE_CODE,DATE_EFFECTIVE_FROM,QUOTE_REASON_CODE,ASSET_ID;
1631 BEGIN
1632
1633 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1634 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.change_status','Begin(+)');
1635 END IF;
1636 record_number:=0;
1637 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1638 IF (p_tif_tbl.COUNT > 0) THEN
1639 record_number := p_tif_tbl.FIRST;
1640 -- loops through the table of records to update the values
1641 LOOP
1642 update_row(p_api_version => p_api_version
1643 ,p_init_msg_list => OKC_API.G_FALSE
1644 ,x_msg_data => x_msg_data
1645 ,x_msg_count => x_msg_count
1646 ,x_return_status => x_return_status
1647 ,p_tif_rec => p_tif_tbl(record_number));
1648
1649 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1650 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.change_status.',
1651 'update_row for : '||record_number||':'||x_return_status);
1652 end if;
1653
1654 EXIT WHEN (record_number = p_tif_tbl.LAST);
1655 record_number := p_tif_tbl.NEXT(record_number);
1656 END LOOP;
1657 END IF;
1658 record_number:=0;
1659 x_tif_tbl.DELETE;
1660 -- populates the plsql table with rows having status as 'WORKING'
1661 FOR termnt_rec IN get_termnt_intface_dtls_csr(p_status=>'WORKING')
1662 LOOP
1663 x_tif_tbl(record_number).row_id := termnt_rec.rowid;
1664 x_tif_tbl(record_number).transaction_number := termnt_rec.transaction_number;
1665 x_tif_tbl(record_number).batch_number := termnt_rec.batch_number;
1666 x_tif_tbl(record_number).contract_id := termnt_rec.contract_id;
1667 x_tif_tbl(record_number).contract_number := termnt_rec.contract_number;
1668 x_tif_tbl(record_number).asset_id := termnt_rec.asset_id;
1669 x_tif_tbl(record_number).asset_number := termnt_rec.asset_number;
1670 x_tif_tbl(record_number).asset_description := termnt_rec.asset_description;
1671 x_tif_tbl(record_number).serial_number := termnt_rec.serial_number;
1672 x_tif_tbl(record_number).orig_system := termnt_rec.orig_system;
1673 x_tif_tbl(record_number).orig_system_reference := termnt_rec.orig_system_reference;
1674 x_tif_tbl(record_number).units_to_terminate := termnt_rec.units_to_terminate;
1675 x_tif_tbl(record_number).comments := termnt_rec.comments;
1676 x_tif_tbl(record_number).date_processed := termnt_rec.date_processed;
1677 x_tif_tbl(record_number).date_effective_from := termnt_rec.date_effective_from;
1678 x_tif_tbl(record_number).termination_notification_email := termnt_rec.termination_notification_email;
1679 x_tif_tbl(record_number).termination_notification_yn := termnt_rec.termination_notification_yn;
1680 x_tif_tbl(record_number).auto_accept_yn := termnt_rec.auto_accept_yn;
1681 x_tif_tbl(record_number).quote_type_code := termnt_rec.quote_type_code;
1682 x_tif_tbl(record_number).quote_reason_code := termnt_rec.quote_reason_code;
1683 x_tif_tbl(record_number).qte_id := termnt_rec.qte_id;
1684 x_tif_tbl(record_number).status := termnt_rec.status;
1685 x_tif_tbl(record_number).org_id := termnt_rec.org_id;
1686 x_tif_tbl(record_number).request_id := termnt_rec.request_id;
1687 x_tif_tbl(record_number).program_application_id := termnt_rec.program_application_id;
1688 x_tif_tbl(record_number).program_id := termnt_rec.program_id;
1689 x_tif_tbl(record_number).program_update_date := termnt_rec.program_update_date;
1690 x_tif_tbl(record_number).attribute_category := termnt_rec.attribute_category;
1691 x_tif_tbl(record_number).attribute1 := termnt_rec.attribute1;
1692 x_tif_tbl(record_number).attribute2 := termnt_rec.attribute2;
1693 x_tif_tbl(record_number).attribute3 := termnt_rec.attribute3;
1694 x_tif_tbl(record_number).attribute4 := termnt_rec.attribute4;
1695 x_tif_tbl(record_number).attribute5 := termnt_rec.attribute5;
1696 x_tif_tbl(record_number).attribute6 := termnt_rec.attribute6;
1697 x_tif_tbl(record_number).attribute7 := termnt_rec.attribute7;
1698 x_tif_tbl(record_number).attribute8 := termnt_rec.attribute8;
1699 x_tif_tbl(record_number).attribute9 := termnt_rec.attribute9;
1700 x_tif_tbl(record_number).attribute10 := termnt_rec.attribute10;
1701 x_tif_tbl(record_number).attribute11 := termnt_rec.attribute11;
1702 x_tif_tbl(record_number).attribute12 := termnt_rec.attribute12;
1703 x_tif_tbl(record_number).attribute13 := termnt_rec.attribute13;
1704 x_tif_tbl(record_number).attribute14 := termnt_rec.attribute14;
1705 x_tif_tbl(record_number).attribute15 := termnt_rec.attribute15;
1706 x_tif_tbl(record_number).created_by := termnt_rec.created_by;
1707 x_tif_tbl(record_number).creation_date := termnt_rec.creation_date;
1708 x_tif_tbl(record_number).last_updated_by := termnt_rec.last_updated_by;
1709 x_tif_tbl(record_number).last_update_date := termnt_rec.last_update_date;
1710 x_tif_tbl(record_number).last_update_login := termnt_rec.last_update_login;
1711 x_tif_tbl(record_number).group_number := termnt_rec.group_number;
1712 record_number := record_number+1;
1713 END LOOP;
1714
1715 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1716 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.change_status','End(+)');
1717 END IF;
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 -- store SQL error message on message stack for caller
1722 OKC_API.set_message(p_app_name => g_app_name,
1723 p_msg_name => g_unexpected_error,
1724 p_token1 => g_sqlcode_token,
1725 p_token1_value => sqlcode,
1726 p_token2 => g_sqlerrm_token,
1727 p_token2_value => sqlerrm);
1728 l_msg_tbl(0).msg_text := 'change_status:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1729 log_messages(log_msg_flag => 'V',
1730 msg_tbl => l_msg_tbl );
1731 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1732 END change_status;
1733
1734
1735 -- Start of comments
1736 --
1737 -- Procedure Name : remove_duplicates
1738 -- Description : This procedure error out the duplicate records entered
1739 -- : for the quote and calls change_status to update in database
1740 -- Business Rules :
1741 -- Parameters : Input parameters : p_tif_tbl
1742 -- Version : 1.0
1743 -- History : 14-MAR-03 RABHUPAT Created
1744 -- End of comments
1745
1746 PROCEDURE remove_duplicates(p_api_version IN NUMBER,
1747 p_init_msg_list IN VARCHAR2,
1748 x_msg_count OUT NOCOPY NUMBER,
1749 x_msg_data OUT NOCOPY VARCHAR2,
1750 x_return_status OUT NOCOPY VARCHAR2,
1751 p_tif_tbl IN tif_tbl_type,
1752 x_tif_tbl OUT NOCOPY tif_tbl_type) IS
1753 l_tif_rec tif_rec_type;
1754 l_serial_number l_tif_rec.serial_number%TYPE;
1755 l_contract_id l_tif_rec.contract_id%TYPE;
1756 l_asset_id l_tif_rec.asset_id%TYPE;
1757 l_quote_type l_tif_rec.quote_type_code%TYPE;
1758 l_date_effective_from l_tif_rec.date_effective_from%TYPE;
1759 lp_tif_tbl tif_tbl_type;
1760 lx_tif_tbl tif_tbl_type;
1761 l_reason_type l_tif_rec.quote_reason_code%TYPE;
1762 l_asset_qty NUMBER;
1763 l_quote_qty NUMBER;
1764 message VARCHAR2(200);
1765 l_msg_tbl msg_tbl_type;
1766 /* this cursor retrives the total quantity for the asset*/
1767 CURSOR get_qty_csr(p_asset_number IN VARCHAR2) IS
1768 SELECT current_units quantity
1769 FROM OKX_ASSETS_V
1770 WHERE asset_number = p_asset_number;
1771 BEGIN
1772 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1773 IF(p_tif_tbl.COUNT>0)THEN
1774 -- updates the values in the tbl to database and retrives the data with status='WORKING'
1775 change_status(p_api_version => p_api_version,
1776 p_init_msg_list => OKC_API.G_FALSE,
1777 x_msg_count => x_msg_count,
1778 x_msg_data => x_msg_data,
1779 x_return_status => x_return_status,
1780 p_tif_tbl => p_tif_tbl,
1781 x_tif_tbl => lp_tif_tbl);
1782 END IF;
1783 l_contract_id := OKC_API.G_MISS_NUM;
1784 IF(lp_tif_tbl.COUNT>0 AND x_return_status = FND_API.G_RET_STS_SUCCESS)THEN
1785 -- loops through the interface records
1786 FOR term_rec IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
1787 LOOP
1788 IF((lp_tif_tbl(term_rec).contract_id = l_contract_id) AND
1789 (lp_tif_tbl(term_rec).asset_id = l_asset_id) AND
1790 (lp_tif_tbl(term_rec).date_effective_from = l_date_effective_from) AND
1791 (lp_tif_tbl(term_rec).quote_type_code = l_quote_type) AND
1792 (lp_tif_tbl(term_rec).quote_reason_code = l_reason_type) )THEN
1793 IF(lp_tif_tbl(term_rec).serial_number IS NULL OR lp_tif_tbl(term_rec).serial_number = OKC_API.G_MISS_CHAR) THEN
1794 IF(l_asset_qty<l_quote_qty+lp_tif_tbl(term_rec).units_to_terminate) THEN
1795 lp_tif_tbl(term_rec).status := 'ERROR';
1796 -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1797 OKC_API.set_message(p_app_name => g_app_name,
1798 p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1799 p_token1 => 'COL_NAME',
1800 p_token1_value => 'ASSET_NUMBER');
1801 message :='quote quantity exceeds asset quantity for asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1802 ELSE
1803 l_quote_qty := l_quote_qty +lp_tif_tbl(term_rec).units_to_terminate;
1804 END IF;
1805 ELSIF(l_serial_number IS NOT NULL) THEN
1806 IF(l_serial_number = lp_tif_tbl(term_rec).serial_number)THEN
1807 lp_tif_tbl(term_rec).status := 'ERROR';
1808 -- if same serial_number is repeated in a group tthen ERROR out
1809 OKC_API.set_message(p_app_name => g_app_name,
1810 p_msg_name => 'OKL_AM_DUP_LINE',
1811 p_token1 => 'SERIAL_NUMBER',
1812 p_token1_value => lp_tif_tbl(term_rec).serial_number);
1813 message :='duplicate record for serialized asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1814 ELSIF(l_asset_qty<l_quote_qty+lp_tif_tbl(term_rec).units_to_terminate) THEN
1815 lp_tif_tbl(term_rec).status := 'ERROR';
1816 -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1817 OKC_API.set_message(p_app_name => g_app_name,
1818 p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1819 p_token1 => 'COL_NAME',
1820 p_token1_value => 'ASSET_NUMBER');
1821 message :='quote quantity exceeds asset quantity for asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1822 ELSE
1823 l_quote_qty := l_quote_qty +lp_tif_tbl(term_rec).units_to_terminate;
1824 END IF;
1825 END IF;
1826 ELSE
1827 l_contract_id := lp_tif_tbl(term_rec).contract_id;
1828 l_asset_id := lp_tif_tbl(term_rec).asset_id;
1829 l_date_effective_from := lp_tif_tbl(term_rec).date_effective_from;
1830 l_quote_type := lp_tif_tbl(term_rec).quote_type_code;
1831 l_reason_type := lp_tif_tbl(term_rec).quote_reason_code;
1832 l_serial_number := lp_tif_tbl(term_rec).serial_number;
1833 l_quote_qty := lp_tif_tbl(term_rec).units_to_terminate;
1834 -- cursor to get the asset quantity
1835 FOR l_qty_csr IN get_qty_csr(p_asset_number => lp_tif_tbl(term_rec).asset_number)
1836 LOOP
1837 l_asset_qty := l_qty_csr.quantity;
1838 END LOOP;
1839 END IF;
1840 IF(lp_tif_tbl(term_rec).status = 'ERROR') THEN
1841 l_msg_tbl(0).msg_text := message;
1842 log_messages(log_msg_flag => 'V',
1843 p_transaction_number => lp_tif_tbl(term_rec).transaction_number,
1844 p_contract_number => lp_tif_tbl(term_rec).contract_number,
1845 p_asset_number => lp_tif_tbl(term_rec).asset_number,
1846 p_date_effective => lp_tif_tbl(term_rec).date_effective_from,
1847 p_quote_type => lp_tif_tbl(term_rec).quote_type_code,
1848 p_quote_reason => lp_tif_tbl(term_rec).quote_reason_code,
1849 msg_tbl => l_msg_tbl );
1850 END IF;
1851 END LOOP;
1852 -- updates the values in database and gets records with status='WORKING'
1853 change_status(p_api_version => p_api_version,
1854 p_init_msg_list => OKC_API.G_FALSE,
1855 x_msg_count => x_msg_count,
1856 x_msg_data => x_msg_data,
1857 x_return_status => x_return_status,
1858 p_tif_tbl => lp_tif_tbl,
1859 x_tif_tbl => lx_tif_tbl);
1860 x_tif_tbl := lx_tif_tbl;
1861 END IF;
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 -- store SQL error message on message stack for caller
1865 OKC_API.set_message(p_app_name => g_app_name,
1866 p_msg_name => g_unexpected_error,
1867 p_token1 => g_sqlcode_token,
1868 p_token1_value => sqlcode,
1869 p_token2 => g_sqlerrm_token,
1870 p_token2_value => sqlerrm);
1871 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1872 l_msg_tbl(0).msg_text := 'remove_duplicates:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1873 log_messages(log_msg_flag => 'V',
1874 msg_tbl => l_msg_tbl );
1875 END remove_duplicates;
1876
1877 -- Start of comments
1878 --
1879 -- Procedure Name : create_quote
1880 -- Description : This procedure calls the create_termination_quote API
1881 -- : and quote_line_details API
1882 -- Business Rules :
1883 -- Parameters : Input parameters : p_tif_tbl,p_quot_rec,p_assn_tbl,p_qpyv_tbl
1884 -- Version : 1.0
1885 -- History : 04-FEB-03 RABHUPAT Created
1886 -- : 14-MAR-03 RABHUPAT added p_qpyv_tbl parameter
1887 -- : 14-APR-03 RABHUPAT added code to accept the quotes
1888 -- : rmunjulu Bug 4239780 Should not set PreProceeds flag when accepting a quote
1889 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
1890 -- End of comments
1891
1892 PROCEDURE create_quote(p_api_version IN NUMBER,
1893 p_init_msg_list IN VARCHAR2,
1894 x_msg_count OUT NOCOPY NUMBER,
1895 x_msg_data OUT NOCOPY VARCHAR2,
1896 x_return_status OUT NOCOPY VARCHAR2,
1897 p_tif_tbl IN tif_tbl_type,
1898 x_tif_tbl OUT NOCOPY tif_tbl_type,
1899 p_quot_rec IN quot_rec_type,
1900 p_assn_tbl IN assn_tbl_type,
1901 p_qpyv_tbl IN qpyv_tbl_type,
1902 p_batch_offset IN NUMBER,
1903 p_record_number IN NUMBER) IS
1904 l_return_status VARCHAR2(1);
1905 l_tif_rec tif_rec_type;
1906 l_serial_number l_tif_rec.serial_number%TYPE;
1907 l_quantity l_tif_rec.units_to_terminate%TYPE;
1908 l_installbase_id NUMBER;
1909 l_instance_id NUMBER;
1910 batch_offset NUMBER;
1911 record_number NUMBER;
1912 l_sys_date DATE;
1913 lp_quot_rec quot_rec_type;
1914 lx_quot_rec quot_rec_type;
1915 lp_assn_tbl assn_tbl_type;
1916 lx_assn_tbl assn_tbl_type;
1917 lx_tqlv_tbl tqlv_tbl_type;
1918 lp_qpyv_tbl qpyv_tbl_type;
1919 lp_qld_tbl qld_tbl_type;
1920 qdt_counter NUMBER;
1921 l_error_msg_rec ERROR_MESSAGE_TYPE;
1922 l_msg_tbl msg_tbl_type;
1923 l_qte_msg_count NUMBER := 0;
1924 l_det_msg_count NUMBER := 0;
1925 --added parameters for accepting quote logic
1926 l_accept_flag VARCHAR2(1) := 'Y';
1927 lp_term_rec quot_rec_type;
1928 lx_term_rec quot_rec_type;
1929 lx_err_msg VARCHAR2(2000);
1930
1931 /* this cursor retrives the instance ids for the asset with asset_id as input parameter.*/
1932 CURSOR get_instance_id_csr(p_ast_id IN NUMBER) IS
1933 SELECT oklv.id id
1934 FROM okc_k_lines_v oklv, okc_line_styles_v ols
1935 WHERE oklv.cle_id = p_ast_id
1936 AND oklv.lse_id=ols.id
1937 AND ols.lty_code = 'FREE_FORM2';
1938 /*this cursor is used to retrive the installbase id for an instance of the asset*/
1939 CURSOR get_installbase_id_csr(p_instance_id IN NUMBER) IS
1940 SELECT oklv.id id
1941 FROM okc_k_lines_v oklv, okc_line_styles_v ols
1942 WHERE oklv.cle_id = p_instance_id
1943 AND oklv.lse_id=ols.id
1944 AND ols.lty_code = 'INST_ITEM';
1945 /* this cursor retrives the serial_number and quantity for the instance of the asset*/
1946 CURSOR get_sno_qty_csr(p_installbase_id IN NUMBER) IS
1947 SELECT oiiv.serial_number sno,oiiv.quantity qty
1948 FROM okc_k_items_v okiv,okx_install_items_v oiiv
1949 WHERE okiv.cle_id = p_installbase_id
1950 AND okiv.object1_id1=oiiv.instance_id;
1951
1952 l_term_from_intf VARCHAR2(1) := 'Y'; --sechawla 7383445 - added
1953
1954 BEGIN
1955
1956 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1957 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote','Begin(+)');
1958 END IF;
1959
1960 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1961 x_tif_tbl := p_tif_tbl;
1962 lp_quot_rec := p_quot_rec;
1963 lp_assn_tbl := p_assn_tbl;
1964 lp_qpyv_tbl := p_qpyv_tbl;
1965 batch_offset := p_batch_offset;
1966 qdt_counter := 0;
1967
1968 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1969 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote.',
1970 'p_tif_tbl.count '||p_tif_tbl.count);
1971
1972 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote.',
1973 'p_assn_tbl.count '||p_assn_tbl.count);
1974 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote.',
1975 'p_qpyv_tbl.count '||p_qpyv_tbl.count);
1976 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote.',
1977 'p_batch_offset '||p_batch_offset);
1978 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote.',
1979 'p_record_number '||p_record_number);
1980 END IF;
1981 /* sysdate for date processed */
1982 SELECT trunc(SYSDATE) INTO l_sys_date FROM DUAL;
1983
1984 --sechawla 7383445 : begin
1985 -- if any record which is part of this quote has auto_accept_yn='N'
1986 -- dont accept the quote
1987 FOR batch_offset IN p_batch_offset..p_record_number
1988 LOOP
1989 IF(x_tif_tbl(batch_offset).auto_accept_yn = 'N') THEN
1990 l_term_from_intf := 'N';
1991 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1992 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote.',
1993 'going to exit batch_offset loop ');
1994 END IF;
1995 EXIT;
1996 END IF;
1997 END LOOP;
1998 --sechawla 7383445 : end
1999
2000
2001 --Added new parameter p_term_from_intf by sechawla for bug 7383445
2002 /* calls the create_termination_quote API and retrives the quote_id */
2003 OKL_AM_CREATE_QUOTE_PUB.create_terminate_quote(p_api_version => p_api_version
2004 ,p_init_msg_list => OKC_API.G_FALSE
2005 ,x_msg_data => x_msg_data
2006 ,x_msg_count => x_msg_count
2007 ,x_return_status => l_return_status
2008 ,p_quot_rec => lp_quot_rec
2009 ,p_assn_tbl => lp_assn_tbl
2010 ,p_qpyv_tbl => lp_qpyv_tbl
2011 ,x_quot_rec => lx_quot_rec
2012 ,x_assn_tbl => lx_assn_tbl
2013 ,x_tqlv_tbl => lx_tqlv_tbl
2014 ,p_term_from_intf => l_term_from_intf); --sechawla 7383445 - added
2015 /* unwinding the messages from stack and keeping them in log*/
2016 --PAGARG 23-Feb-05 instead of calling accounting util call local procedure
2017 GET_ERROR_MESSAGE(l_error_msg_rec);
2018 IF (l_error_msg_rec.COUNT > 0) THEN
2019 l_qte_msg_count := 0;
2020 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2021 LOOP
2022 IF(length(l_error_msg_rec(m))>0) THEN
2023 l_msg_tbl(l_qte_msg_count).msg_text := l_error_msg_rec(m);
2024 l_qte_msg_count := l_qte_msg_count+1;
2025 END IF;
2026 END LOOP;
2027 END IF;
2028 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2029 LOOP
2030 --updates the interface records with quote_id,date_processed and status='PROCESSED'
2031 x_tif_tbl(batch_offset).qte_id := lx_quot_rec.id;
2032 x_tif_tbl(batch_offset).status := 'PROCESSED';
2033 x_tif_tbl(batch_offset).date_processed := l_sys_date;
2034 IF(x_tif_tbl(batch_offset).serial_number IS NOT NULL) THEN
2035 -- cursor to get instance id
2036 FOR l_instance_id_csr IN get_instance_id_csr(p_ast_id => p_tif_tbl(batch_offset).asset_id)
2037 LOOP
2038 l_instance_id := l_instance_id_csr.id;
2039 -- cursor to find installbase id
2040 FOR l_installbase_id_csr IN get_installbase_id_csr(p_instance_id => l_instance_id)
2041 LOOP
2042 l_installbase_id := l_installbase_id_csr.id;
2043 END LOOP;
2044 -- cursor to find serial no and quantiy
2045 FOR l_sno_qty_csr IN get_sno_qty_csr(p_installbase_id => l_installbase_id)
2046 LOOP
2047 l_serial_number := l_sno_qty_csr.sno;
2048 l_quantity := l_sno_qty_csr.qty;
2049 END LOOP;
2050 -- populates the tbl required for quote line details
2051 IF(l_serial_number = x_tif_tbl(batch_offset).serial_number) THEN
2052 lp_qld_tbl(qdt_counter).fin_line_id := p_tif_tbl(batch_offset).asset_id;
2053 lp_qld_tbl(qdt_counter).ib_line_id := l_installbase_id;
2054 lp_qld_tbl(qdt_counter).SELECT_YN := 'Y';
2055 EXIT;
2056 END IF;
2057 END LOOP;
2058 -- populates the table required for quote line details
2059 lp_qld_tbl(qdt_counter).qte_id := lx_quot_rec.id;
2060 lp_qld_tbl(qdt_counter).dnz_chr_id := p_tif_tbl(batch_offset).contract_id;
2061 lp_qld_tbl(qdt_counter).instance_quantity := p_tif_tbl(batch_offset).units_to_terminate;
2062 lp_qld_tbl(qdt_counter).qst_code := lx_quot_rec.qst_code;
2063 IF(lx_tqlv_tbl.COUNT>0) THEN
2064 FOR j IN lx_tqlv_tbl.FIRST..lx_tqlv_tbl.LAST LOOP
2065 IF(lp_qld_tbl(qdt_counter).fin_line_id = lx_tqlv_tbl(j).kle_id AND lx_tqlv_tbl(j).qlt_code = 'AMCFIA') THEN
2066 lp_qld_tbl(qdt_counter).tql_id := lx_tqlv_tbl(j).id;
2067 EXIT;
2068 END IF;
2069 END LOOP;
2070 END IF;
2071 qdt_counter := qdt_counter+1;
2072 -- added for non serialized
2073 ELSE
2074 l_msg_tbl(0).msg_text := 'quote created for transaction_number '||x_tif_tbl(batch_offset).transaction_number;
2075 log_messages(log_msg_flag => 'P',
2076 p_transaction_number => x_tif_tbl(batch_offset).transaction_number,
2077 p_contract_number => x_tif_tbl(batch_offset).contract_number,
2078 p_asset_number => x_tif_tbl(batch_offset).asset_number,
2079 p_date_effective => x_tif_tbl(batch_offset).date_effective_from,
2080 p_quote_type => x_tif_tbl(batch_offset).quote_type_code,
2081 p_quote_reason => x_tif_tbl(batch_offset).quote_reason_code,
2082 p_quote_number => lx_quot_rec.quote_number,
2083 msg_tbl => l_msg_tbl );
2084 END IF;
2085 IF(batch_offset = p_record_number) THEN
2086 IF(lp_qld_tbl.COUNT>0) THEN
2087 -- calls the quote_line_dtls API to create quote line details for the serialized assets
2088 OKL_AM_TERMNT_QUOTE_PUB.quote_line_dtls(p_api_version => p_api_version,
2089 p_init_msg_list => OKC_API.G_FALSE,
2090 x_return_status => l_return_status,
2091 x_msg_count => x_msg_count,
2092 x_msg_data => x_msg_data,
2093 p_qld_tbl => lp_qld_tbl);
2094 lp_qld_tbl.DELETE;
2095 /* unwinding the messages from stack and keeping them in log*/
2096 --PAGARG 23-Feb-05 instead of calling accounting util call local procedure
2097 GET_ERROR_MESSAGE(l_error_msg_rec);
2098 IF (l_error_msg_rec.COUNT > 0) THEN
2099 l_det_msg_count := 0;
2100 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2101 LOOP
2102 IF(length(l_error_msg_rec(m))>0) THEN
2103 l_msg_tbl(l_det_msg_count).msg_text := l_error_msg_rec(m);
2104 l_det_msg_count := l_det_msg_count+1;
2105 END IF;
2106 END LOOP;
2107 END IF;
2108 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2109 batch_offset := p_batch_offset;
2110 FOR i IN p_batch_offset..p_record_number LOOP
2111 IF(x_tif_tbl(i).serial_number IS NOT NULL) THEN
2112 --x_tif_tbl(i).status := 'ERROR';
2113 -- error in quote details
2114 l_msg_tbl(l_det_msg_count).msg_text := 'quote created, error in quote details for transaction_number '||x_tif_tbl(i).transaction_number;
2115 log_messages(log_msg_flag => 'E',
2116 p_transaction_number => x_tif_tbl(i).transaction_number,
2117 p_contract_number => x_tif_tbl(i).contract_number,
2118 p_asset_number => x_tif_tbl(i).asset_number,
2119 p_date_effective => x_tif_tbl(i).date_effective_from,
2120 p_quote_type => x_tif_tbl(i).quote_type_code,
2121 p_quote_reason => x_tif_tbl(i).quote_reason_code,
2122 p_quote_number => lx_quot_rec.quote_number,
2123 msg_tbl => l_msg_tbl );
2124 -- since quote details is failed quote cannot be accepted changing l_accept_flag='N'
2125 l_accept_flag := 'N';
2126 END IF;
2127 END LOOP;
2128 ELSE
2129 batch_offset := p_batch_offset;
2130 FOR i IN p_batch_offset..p_record_number LOOP
2131 IF(x_tif_tbl(i).serial_number IS NOT NULL) THEN
2132 l_msg_tbl(0).msg_text := 'quote and quote details created for transaction_number '||x_tif_tbl(i).transaction_number;
2133 log_messages(log_msg_flag => 'P',
2134 p_transaction_number => x_tif_tbl(i).transaction_number,
2135 p_contract_number => x_tif_tbl(i).contract_number,
2136 p_asset_number => x_tif_tbl(i).asset_number,
2137 p_date_effective => x_tif_tbl(i).date_effective_from,
2138 p_quote_type => x_tif_tbl(i).quote_type_code,
2139 p_quote_reason => x_tif_tbl(i).quote_reason_code,
2140 p_quote_number => lx_quot_rec.quote_number,
2141 msg_tbl => l_msg_tbl );
2142 END IF;
2143 END LOOP;
2144 END IF;
2145 END IF; -- if(lp_qld_tbl >0)
2146 EXIT;
2147 END IF;
2148 batch_offset := batch_offset+1;
2149 END LOOP;--for batch
2150 -- adding the code for accepting the quote
2151 IF(l_accept_flag = 'Y') THEN
2152 -- if any record which is part of this quote has auto_accept_yn='N'
2153 -- dont accept the quote
2154 FOR batch_offset IN p_batch_offset..p_record_number
2155 LOOP
2156 IF(x_tif_tbl(batch_offset).auto_accept_yn = 'N') THEN
2157 l_accept_flag := 'N';
2158 EXIT;
2159 END IF;
2160 END LOOP;
2161 -- accept the quote
2162 IF(l_accept_flag = 'Y') THEN
2163 lp_term_rec.id := lx_quot_rec.id;
2164 lp_term_rec.accepted_yn := 'Y';
2165 -- lp_term_rec.preproceeds_yn := 'Y'; -- rmunjulu Bug 4239780 Should not set PreProceeds flag
2166 lp_term_rec.date_effective_to := lx_quot_rec.date_effective_to;
2167 -- calling the API to accept quote
2168 OKL_AM_TERMNT_QUOTE_PUB.terminate_quote (p_api_version => p_api_version,
2169 p_init_msg_list => OKL_API.G_FALSE,
2170 x_return_status => l_return_status,
2171 x_msg_count => x_msg_count,
2172 x_msg_data => x_msg_data,
2173 p_term_rec => lp_term_rec,
2174 x_term_rec => lx_term_rec,
2175 x_err_msg => lx_err_msg);
2176 /* unwinding the messages from stack and keeping them in log*/
2177 --PAGARG 23-Feb-05 instead of calling accounting util call local procedure
2178 GET_ERROR_MESSAGE(l_error_msg_rec);
2179 IF (l_error_msg_rec.COUNT > 0) THEN
2180 l_det_msg_count := 0;
2181 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2182 LOOP
2183 IF(length(l_error_msg_rec(m))>0) THEN
2184 l_msg_tbl(l_det_msg_count).msg_text := l_error_msg_rec(m);
2185 l_det_msg_count := l_det_msg_count+1;
2186 END IF;
2187 END LOOP;
2188 END IF;
2189 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2190 FOR i IN p_batch_offset..p_record_number
2191 LOOP
2192 -- error in accepting quote
2193 --l_msg_tbl(l_det_msg_count).msg_text := 'error in acccepting quote for transaction_number '||x_tif_tbl(i).transaction_number;
2194 log_messages(log_msg_flag => 'E',
2195 p_transaction_number => x_tif_tbl(i).transaction_number,
2196 p_contract_number => x_tif_tbl(i).contract_number,
2197 p_asset_number => x_tif_tbl(i).asset_number,
2198 p_date_effective => x_tif_tbl(i).date_effective_from,
2199 p_quote_type => x_tif_tbl(i).quote_type_code,
2200 p_quote_reason => x_tif_tbl(i).quote_reason_code,
2201 p_quote_number => lx_quot_rec.quote_number,
2202 msg_tbl => l_msg_tbl );
2203 END LOOP;
2204 END IF;
2205 ELSE
2206 -- quote can not be accepted as one line has auto_accept_yn = 'N'
2207 l_msg_tbl(0).msg_text := 'Quote '||lx_quot_rec.quote_number||' cannot be accepted as one of the transaction line contains auto_accept_yn as N';
2208 FOR i IN p_batch_offset..p_record_number
2209 LOOP
2210 -- error in accepting quote
2211 log_messages(log_msg_flag => 'E',
2212 p_transaction_number => x_tif_tbl(i).transaction_number,
2213 p_contract_number => x_tif_tbl(i).contract_number,
2214 p_asset_number => x_tif_tbl(i).asset_number,
2215 p_date_effective => x_tif_tbl(i).date_effective_from,
2216 p_quote_type => x_tif_tbl(i).quote_type_code,
2217 p_quote_reason => x_tif_tbl(i).quote_reason_code,
2218 p_quote_number => lx_quot_rec.quote_number,
2219 msg_tbl => l_msg_tbl );
2220 END LOOP;
2221 END IF;
2222 END IF;
2223 -- end of auto accept logic
2224 ELSE
2225 FOR batch_offset IN p_batch_offset..p_record_number LOOP
2226 -- updates status of all rows for which quote is not created.
2227 x_tif_tbl(batch_offset).status := 'ERROR';
2228 l_msg_tbl(l_qte_msg_count).msg_text := 'quote not created for transaction_number '||x_tif_tbl(batch_offset).transaction_number;
2229 log_messages(log_msg_flag => 'V',
2230 p_transaction_number => x_tif_tbl(batch_offset).transaction_number,
2231 p_contract_number => x_tif_tbl(batch_offset).contract_number,
2232 p_asset_number => x_tif_tbl(batch_offset).asset_number,
2233 p_date_effective => x_tif_tbl(batch_offset).date_effective_from,
2234 p_quote_type => x_tif_tbl(batch_offset).quote_type_code,
2235 p_quote_reason => x_tif_tbl(batch_offset).quote_reason_code,
2236 msg_tbl => l_msg_tbl );
2237 END LOOP;
2238 END IF;
2239
2240 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2241 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote','End(+)');
2242 END IF;
2243
2244 EXCEPTION
2245 WHEN OTHERS THEN
2246 -- store SQL error message on message stack for caller
2247 OKC_API.set_message(p_app_name => g_app_name,
2248 p_msg_name => g_unexpected_error,
2249 p_token1 => g_sqlcode_token,
2250 p_token1_value => sqlcode,
2251 p_token2 => g_sqlerrm_token,
2252 p_token2_value => sqlerrm);
2253 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2254 l_msg_tbl(0).msg_text := 'create_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2255 log_messages(log_msg_flag => 'V',
2256 msg_tbl => l_msg_tbl );
2257 END create_quote;
2258
2259 -- Start of comments
2260 --
2261 -- Procedure Name : populate_quote
2262 -- Description : This procedure populates the data required for calling
2263 -- : create_termination_quote API and quote_line_details API
2264 -- Business Rules :
2265 -- Parameters : Input parameters : p_tif_tbl
2266 -- Version : 1.0
2267 -- History : 04-FEB-03 RABHUPAT Created
2268 -- : 14-MAR-03 RABHUPAT added another parameter to create_quote
2269 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
2270 -- End of comments
2271
2272 PROCEDURE populate_quote(
2273 p_api_version IN NUMBER,
2274 p_init_msg_list IN VARCHAR2,
2275 x_msg_count OUT NOCOPY NUMBER,
2276 x_msg_data OUT NOCOPY VARCHAR2,
2277 x_return_status OUT NOCOPY VARCHAR2,
2278 p_tif_tbl IN tif_tbl_type,
2279 x_tif_tbl OUT NOCOPY tif_tbl_type,
2280 p_group_number IN NUMBER
2281 )IS
2282 l_tif_rec tif_rec_type;
2283 lp_tif_tbl tif_tbl_type;
2284 lx_tif_tbl tif_tbl_type;
2285 lp_quot_rec quot_rec_type;
2286 lx_quot_rec quot_rec_type;
2287 lp_assn_tbl assn_tbl_type;
2288 lx_assn_tbl assn_tbl_type;
2289 lx_tqlv_tbl tqlv_tbl_type;
2290 lp_qpyv_tbl qpyv_tbl_type;
2291 lp_qld_tbl qld_tbl_type;
2292 batch_offset NUMBER;
2293 group_number NUMBER;
2294 record_number NUMBER;
2295 quote_lines NUMBER;
2296 qdt_counter NUMBER;
2297 l_installbase_id NUMBER;
2298 l_instance_id NUMBER;
2299 l_quote_success NUMBER;
2300 i NUMBER;
2301 l_serial_number l_tif_rec.serial_number%TYPE;
2302 l_quantity l_tif_rec.units_to_terminate%TYPE;
2303 l_contract_number l_tif_rec.contract_number%TYPE;
2304 l_quote_type l_tif_rec.quote_type_code%TYPE;
2305 l_quote_reason l_tif_rec.quote_reason_code%TYPE;
2306 l_date_effective_from l_tif_rec.date_effective_from%TYPE;
2307 l_msg_tbl msg_tbl_type;
2308
2309 BEGIN
2310
2311 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2312 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote','Begin(+)');
2313 END IF;
2314 record_number := 0;
2315 quote_lines := 0;
2316 batch_offset := 0;
2317 group_number := p_group_number;
2318 lp_tif_tbl := p_tif_tbl;
2319 lx_tif_tbl := p_tif_tbl;
2320 l_contract_number := OKC_API.G_MISS_CHAR;
2321 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2322
2323 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2324 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2325 'p_group_number : '||p_group_number);
2326 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2327 'p_tif_tbl.count : '||p_tif_tbl.count);
2328
2329 end if;
2330
2331 IF(p_tif_tbl.COUNT>0) THEN
2332 FOR record_number IN p_tif_tbl.FIRST..p_tif_tbl.LAST LOOP
2333
2334 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2335 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2336 'inside p_tif_tbl LOOP record_number '||record_number);
2337 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2338 'inside p_tif_tbl LOOP K Number '||p_tif_tbl(record_number).contract_number);
2339 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2340 'inside p_tif_tbl LOOP quote_type_code '||p_tif_tbl(record_number).quote_type_code);
2341 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2342 'inside p_tif_tbl LOOP quote_reason_code '||p_tif_tbl(record_number).quote_reason_code);
2343 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2344 'inside p_tif_tbl LOOP date_effective_from '||p_tif_tbl(record_number).date_effective_from);
2345
2346 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2347 'inside p_tif_tbl LOOP l_contract_number '||l_contract_number);
2348 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2349 'inside p_tif_tbl LOOP l_quote_type '||l_quote_type);
2350 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2351 'inside p_tif_tbl LOOP l_quote_reason '||l_quote_reason);
2352 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2353 'inside p_tif_tbl LOOP l_date_effective_from '||l_date_effective_from);
2354 END IF;
2355
2356 -- checking the condition to separate the batches for quote creation
2357 IF(((l_contract_number <> p_tif_tbl(record_number).contract_number) OR
2358 (l_quote_type <> p_tif_tbl(record_number).quote_type_code) OR
2359 (l_quote_reason <> p_tif_tbl(record_number).quote_reason_code) OR
2360 (l_date_effective_from <> p_tif_tbl(record_number).date_effective_from))
2361 AND (record_number <>p_tif_tbl.FIRST)) THEN
2362
2363 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2364 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2365 'entered IF... ');
2366
2367 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2368 'inside IF contract_id '||p_tif_tbl(record_number-1).contract_id);
2369 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2370 'inside IF date_effective_from '||p_tif_tbl(record_number-1).date_effective_from);
2371 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2372 'inside IF org_id '||p_tif_tbl(record_number-1).org_id);
2373 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2374 'inside IF quote_type_code '||p_tif_tbl(record_number-1).quote_type_code);
2375 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2376 'inside IF quote_reason_code '||p_tif_tbl(record_number-1).quote_reason_code);
2377 END IF;
2378
2379 -- populates the lp_quot_rec and calls create_quote
2380 lp_quot_rec.khr_id := p_tif_tbl(record_number-1).contract_id;
2381 lp_quot_rec.date_effective_from := p_tif_tbl(record_number-1).date_effective_from;
2382 lp_quot_rec.org_id := p_tif_tbl(record_number-1).org_id;
2383 lp_quot_rec.qtp_code := p_tif_tbl(record_number-1).quote_type_code;
2384 lp_quot_rec.qrs_code := p_tif_tbl(record_number-1).quote_reason_code;
2385 create_quote(
2386 p_api_version => p_api_version
2387 ,p_init_msg_list => OKC_API.G_FALSE
2388 ,x_msg_count => x_msg_count
2389 ,x_msg_data => x_msg_data
2390 ,x_return_status => x_return_status
2391 ,p_tif_tbl => lp_tif_tbl
2392 ,x_tif_tbl => lx_tif_tbl
2393 ,p_quot_rec => lp_quot_rec
2394 ,p_assn_tbl => lp_assn_tbl
2395 ,p_qpyv_tbl => lp_qpyv_tbl
2396 ,p_batch_offset => batch_offset
2397 ,p_record_number => record_number-1);
2398
2399 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2400 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2401 'after Craete_quote '||x_return_status);
2402 END IF;
2403
2404 lp_assn_tbl.DELETE;
2405 lp_tif_tbl.DELETE;
2406 lp_tif_tbl := lx_tif_tbl;
2407 lx_tif_tbl.DELETE;
2408
2409 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2410 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2411 'batch_offset '||batch_offset);
2412 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2413 'record_number '||record_number);
2414 END IF;
2415
2416 FOR i IN batch_offset..record_number-1 LOOP
2417
2418 lp_tif_tbl(i).group_number := group_number;
2419
2420 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2421 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2422 'inside grp number loop '||i||':'||group_number);
2423 END IF;
2424 END LOOP;
2425 group_number := group_number+1;
2426 batch_offset := record_number;
2427 quote_lines := 0;
2428 END IF;--not equal condition
2429
2430 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2431 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2432 'outside not equal IF condition.. ');
2433 END IF;
2434
2435 -- copy p_tif_tbl record in to local variables
2436 l_contract_number := p_tif_tbl(record_number).contract_number;
2437 l_quote_type := p_tif_tbl(record_number).quote_type_code;
2438 l_quote_reason := p_tif_tbl(record_number).quote_reason_code;
2439 l_date_effective_from := p_tif_tbl(record_number).date_effective_from;
2440
2441 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2442 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2443 'l_contract_number '||p_tif_tbl(record_number).contract_number);
2444 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2445 'l_quote_type '||p_tif_tbl(record_number).quote_type_code);
2446 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2447 'l_quote_reason '||p_tif_tbl(record_number).quote_reason_code);
2448 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2449 'l_date_effective_from '||p_tif_tbl(record_number).date_effective_from);
2450 END IF;
2451 --assign values to assn_tbl;
2452 IF(lp_assn_tbl.COUNT>0) THEN
2453
2454 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2455 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2456 'inside lp_assn_tbl.COUNT>0 .. asset_id '||lp_assn_tbl(quote_lines).p_asset_id);
2457 END IF;
2458 IF(p_tif_tbl(record_number).asset_id = lp_assn_tbl(quote_lines).p_asset_id) THEN
2459 lp_assn_tbl(quote_lines).p_quote_qty := lp_assn_tbl(quote_lines).p_quote_qty+p_tif_tbl(record_number).units_to_terminate;
2460 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2461 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2462 'inside asset equal condition .. p_quote_qty '||lp_assn_tbl(quote_lines).p_quote_qty+p_tif_tbl(record_number).units_to_terminate);
2463 END IF;
2464
2465 ELSE
2466 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2467 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2468 'inside asset <> condition .. quote_lines '||quote_lines);
2469 END IF;
2470
2471 quote_lines := quote_lines+1;
2472 lp_assn_tbl(quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
2473 lp_assn_tbl(quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
2474 lp_assn_tbl(quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
2475
2476 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2477 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2478 'inside asset <> condition .. p_asset_id '||p_tif_tbl(record_number).asset_id);
2479 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2480 'inside asset <> condition .. p_asset_number '||p_tif_tbl(record_number).asset_number);
2481 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2482 'inside asset <> condition .. p_quote_qty '||p_tif_tbl(record_number).units_to_terminate);
2483 END IF;
2484 END IF;
2485 ELSE
2486
2487 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2488 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2489 'inside lp_assn_tbl.COUNT<= 0 .. ');
2490 END IF;
2491 --assign values to assn_tbl
2492 lp_assn_tbl(quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
2493 lp_assn_tbl(quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
2494 lp_assn_tbl(quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
2495
2496 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2497 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2498 'inside lp_assn_tbl.COUNT<= 0 .. p_asset_id '||p_tif_tbl(record_number).asset_id);
2499 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2500 'inside lp_assn_tbl.COUNT<= 0 .. p_asset_number '||p_tif_tbl(record_number).asset_number);
2501 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2502 'inside lp_assn_tbl.COUNT<= 0 .. p_quote_qty '||p_tif_tbl(record_number).units_to_terminate);
2503 END IF;
2504
2505 END IF;
2506 IF(record_number = p_tif_tbl.LAST) THEN
2507
2508 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2509 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2510 'last record... ');
2511 END IF;
2512 -- populates the lp_quot_rec and calls create_quote for the last record
2513 lp_quot_rec.khr_id := p_tif_tbl(record_number).contract_id;
2514 lp_quot_rec.date_effective_from := p_tif_tbl(record_number).date_effective_from;
2515 lp_quot_rec.org_id := p_tif_tbl(record_number).org_id;
2516 lp_quot_rec.qtp_code := p_tif_tbl(record_number).quote_type_code;
2517 lp_quot_rec.qrs_code := p_tif_tbl(record_number).quote_reason_code;
2518
2519 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2520
2521 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2522 'last record .. khr_id '||p_tif_tbl(record_number).contract_id);
2523 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2524 'last record .. date_effective_from '||p_tif_tbl(record_number).date_effective_from);
2525 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2526 'last record .. org_id '||p_tif_tbl(record_number).org_id);
2527 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2528 'last record .. qtp_code '||p_tif_tbl(record_number).quote_type_code);
2529 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2530 'last record .. qrs_code '||p_tif_tbl(record_number).quote_reason_code);
2531 END IF;
2532 create_quote(p_api_version => p_api_version
2533 ,p_init_msg_list => OKC_API.G_FALSE
2534 ,x_msg_count => x_msg_count
2535 ,x_msg_data => x_msg_data
2536 ,x_return_status => x_return_status
2537 ,p_tif_tbl => lp_tif_tbl
2538 ,x_tif_tbl => lx_tif_tbl
2539 ,p_quot_rec => lp_quot_rec
2540 ,p_assn_tbl => lp_assn_tbl
2541 ,p_qpyv_tbl => lp_qpyv_tbl
2542 ,p_batch_offset => batch_offset
2543 ,p_record_number => record_number);
2544
2545 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2546
2547 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2548 'after create_quote.. '||x_return_status);
2549 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2550 'batch_offset.. '||batch_offset);
2551 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2552 'record_number.. '||record_number);
2553 END IF;
2554
2555 FOR i IN batch_offset..record_number LOOP
2556 lx_tif_tbl(i).group_number := group_number;
2557 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2558 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2559 'group number .. '||group_number||':'||i);
2560 END IF;
2561
2562 END LOOP;
2563 END IF;
2564 END LOOP;--p_tif_tbl.FIRST..p_tif_tbl.LAST
2565 x_tif_tbl.DELETE;
2566 x_tif_tbl := lx_tif_tbl;
2567 IF (x_tif_tbl.COUNT>0) THEN
2568 -- updates the INTERFACE table
2569
2570 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2571 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2572 'x_tif_tbl.COUNT .. '||x_tif_tbl.COUNT);
2573 END IF;
2574 FOR record_number IN x_tif_tbl.FIRST..x_tif_tbl.LAST LOOP
2575
2576 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2577 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2578 'record_number .. '||record_number);
2579 END IF;
2580 update_row(p_api_version => p_api_version
2581 ,p_init_msg_list => OKC_API.G_FALSE
2582 ,x_msg_data => x_msg_data
2583 ,x_msg_count => x_msg_count
2584 ,x_return_status => x_return_status
2585 ,p_tif_rec => x_tif_tbl(record_number));
2586 -- Save message from stack into transaction message table
2587 OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
2588 p_trx_id => lx_tif_tbl(record_number).transaction_number,
2589 x_return_status => x_return_status);
2590 END LOOP;
2591 END IF;
2592 END IF;--p_tif_tbl.COUNT>0 END IF; -- To check if table contains any record.
2593
2594 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2595 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote','End(+)');
2596 END IF;
2597
2598 EXCEPTION
2599 WHEN OTHERS THEN
2600 -- store SQL error message on message stack for caller
2601 OKC_API.set_message(p_app_name => g_app_name,
2602 p_msg_name => g_unexpected_error,
2603 p_token1 => g_sqlcode_token,
2604 p_token1_value => sqlcode,
2605 p_token2 => g_sqlerrm_token,
2606 p_token2_value => sqlerrm);
2607 l_msg_tbl(0).msg_text := 'populate_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2608 log_messages(log_msg_flag => 'V',
2609 msg_tbl => l_msg_tbl );
2610 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2611 END populate_quote;
2612
2613 /* RABHUPAT added party logic for termination interface logic, only recipient role can be overwritten not others
2614 and creates quote for each party record defaults the quote_role_code to 'RECIPIENT'*/
2615
2616 -- Start of comments
2617 --
2618 -- Procedure Name : validate_party
2619 -- Description : This procedure validates the party table fields
2620 -- :populates the data required for calling
2621 -- : create_termination_quote API
2622 -- Business Rules :
2623 -- Parameters : Input parameters : p_tip_tbl,p_tif_tbl
2624 -- Version : 1.0
2625 -- History : 14-MAR-03 RABHUPAT Created
2626 -- End of comments
2627
2628 PROCEDURE validate_party(p_api_version IN NUMBER,
2629 p_init_msg_list IN VARCHAR2,
2630 x_msg_count OUT NOCOPY NUMBER,
2631 x_msg_data OUT NOCOPY VARCHAR2,
2632 x_return_status OUT NOCOPY VARCHAR2,
2633 p_tif_tbl IN tif_tbl_type,
2634 x_tif_tbl OUT NOCOPY tif_tbl_type,
2635 p_tip_tbl IN tip_tbl_type,
2636 x_tip_tbl OUT NOCOPY tip_tbl_type) IS
2637
2638 -- cursor to retrive contract_id from OKL_TERMNT_INTERFACE joined by transaction_number
2639 CURSOR get_contract_id_csr(p_transaction_number IN VARCHAR2) IS
2640 SELECT contract_id,status
2641 FROM OKL_TERMNT_INTERFACE
2642 WHERE transaction_number = p_transaction_number;
2643
2644 -- cursor to select the party details using contract_id and quote_role_code
2645 --Fixed Bug # 5484903
2646 CURSOR quote_party_details_csr(p_chr_id IN NUMBER, p_role_code IN VARCHAR2) IS
2647 SELECT id,object1_id1,object1_id2,jtot_object1_code
2648 FROM OKC_K_PARTY_ROLES_B
2649 WHERE dnz_chr_id = p_chr_id
2650 AND chr_id = dnz_chr_id
2651 AND rle_code = p_role_code;
2652 -- cursor to select the party_name if party is VENDOR
2653 CURSOR get_vendor_info_csr(p_object1_id1 IN VARCHAR2, p_object1_id2 IN VARCHAR2) IS
2654 SELECT party_number,name
2655 FROM OKX_VENDORS_V
2656 WHERE id1 = p_object1_id1 AND id2 = p_object1_id2;
2657
2658 -- cursor to select the party_name if party is LESSEE
2659 CURSOR get_lessee_info_csr(p_object1_id1 IN VARCHAR2, p_object1_id2 IN VARCHAR2) IS
2660 SELECT party_name,party_number,email_address
2661 FROM HZ_PARTIES
2662 WHERE ((party_id = p_object1_id1) AND party_type IN ( 'PERSON','ORGANIZATION'));
2663
2664 --cursor to check the record in interface table for a transaction number
2665 Cursor l_rec_exists_csr (p_transaction_number VARCHAR2) IS
2666 select count(*) from OKL_TERMNT_INTERFACE
2667 where transaction_number = p_transaction_number;
2668
2669 l_count NUMBER;
2670 l_dup_flag VARCHAR2(1);
2671 l_transaction_number OKL_TERMNT_INTF_PTY.transaction_number%TYPE;
2672 l_chr_id NUMBER;
2673 lp_tip_tbl tip_tbl_type;
2674 lx_tip_tbl tip_tbl_type;
2675 l_qpy_id OKL_TERMNT_INTF_PTY.qpy_id%TYPE;
2676 l_party_object_id1 OKL_TERMNT_INTF_PTY.party_object_id1%TYPE;
2677 l_party_object_id2 OKL_TERMNT_INTF_PTY.party_object_id2%TYPE;
2678 l_party_object_code OKL_TERMNT_INTF_PTY.party_object_code%TYPE;
2679 l_contract_party_number OKL_TERMNT_INTF_PTY.contract_party_number%TYPE;
2680 l_contract_party_id OKL_TERMNT_INTF_PTY.contract_party_id%TYPE;
2681 l_contract_party_name OKL_TERMNT_INTF_PTY.contract_party_name%TYPE;
2682 l_email_address OKL_TERMNT_INTF_PTY.email_address%TYPE;
2683 message VARCHAR2(200);
2684 l_msg_tbl msg_tbl_type;
2685
2686 l_trxn_exists BOOLEAN := FALSE;
2687 l_temp NUMBER;
2688 BEGIN
2689
2690 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2691 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','Begin(+)');
2692 END IF;
2693
2694 x_tif_tbl := p_tif_tbl;
2695 lp_tip_tbl := p_tip_tbl;
2696 lx_tip_tbl := p_tip_tbl;
2697 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2698 l_count := 0;
2699
2700 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2701 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party', 'lp_tip_tbl.count = '|| lp_tip_tbl.count);
2702 END IF;
2703
2704 IF(lp_tip_tbl.COUNT>0) THEN
2705 -- sets the session variables
2706 FOR record_number IN lp_tip_tbl.FIRST..lp_tip_tbl.LAST LOOP
2707
2708 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2709 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','Record Number '||record_number);
2710 END IF;
2711
2712 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2713 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','l_transaction_number = '
2714 || l_transaction_number ||
2715 'lp_tip_tbl(record_number).transaction_number ='|| lp_tip_tbl(record_number).transaction_number);
2716 END IF;
2717
2718 -- vsgandhi
2719 -- bug 13512581 : 06-jan-2012
2720 -- check if there is a corresponding record in interface table for a party record of a particular transaction number
2721
2722 OPEN l_rec_exists_csr(lx_tip_tbl(record_number).transaction_number);
2723 FETCH l_rec_exists_csr into l_temp;
2724 IF l_temp >0 THEN
2725 l_trxn_exists := TRUE;
2726 ELSE
2727 l_trxn_exists := FALSE;
2728 END IF;
2729 CLOSE l_rec_exists_csr;
2730
2731 IF l_trxn_exists THEN
2732 -- populate WHO columns in party table
2733 lx_tip_tbl(record_number).CREATION_DATE := SYSDATE;
2734 lx_tip_tbl(record_number).CREATED_BY := FND_GLOBAL.USER_ID;
2735 l_dup_flag := 'N';
2736 IF(l_transaction_number = lp_tip_tbl(record_number).transaction_number) THEN
2737 lx_tip_tbl(record_number).status := 'ERROR';
2738 l_dup_flag := 'Y';
2739 --transaction_number repeated, ERROR out only the party record not the INTERFACE record
2740 -- striked off in the DLD
2741 OKC_API.set_message(p_app_name => g_app_name,
2742 p_msg_name => 'OKL_AM_DUP_TRAN_NUM',
2743 p_token1 => 'TRANSACTION_NUMBER',
2744 p_token1_value => lx_tip_tbl(record_number).transaction_number);
2745 message :='transaction_number '||lx_tip_tbl(record_number).transaction_number||' repeated in party table';
2746 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2747 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','message 1 '||message);
2748 END IF;
2749
2750 ELSIF(lp_tip_tbl(record_number).contract_party_role IS NULL) THEN
2751 lx_tip_tbl(record_number).status := 'ERROR';
2752 -- contract_party_role should be entered
2753 OKC_API.set_message(p_app_name => g_app_name,
2754 p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2755 p_token1 => 'COL_NAME',
2756 p_token1_value => 'CONTRACT PARTY ROLE');
2757 message :='contract party role should be entered for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2758
2759 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2760 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','message 2 '||message);
2761 END IF;
2762 ELSE
2763 lx_tip_tbl(record_number).status := 'WORKING';
2764 END IF;
2765
2766 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2767 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','lx_tip_tbl(record_number).status1 '
2768 ||lx_tip_tbl(record_number).status);
2769 END IF;
2770
2771 IF(lx_tip_tbl(record_number).status <> 'ERROR') THEN
2772 l_transaction_number := lp_tip_tbl(record_number).transaction_number;
2773 l_chr_id := NULL;
2774 -- fetches the associated contract_id from INTERFACE table based on transaction_number
2775 FOR interface_dtls IN get_contract_id_csr(l_transaction_number) LOOP
2776 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2777 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','interface_dtls.status '
2778 ||interface_dtls.status);
2779 END IF;
2780 IF(interface_dtls.status <> 'WORKING') THEN
2781 lx_tip_tbl(record_number).status := 'ERROR';
2782 -- corresponding transaction_number in INTERFACE table is ERRORED out
2783 -- striked off in the DLD
2784 OKC_API.set_message(p_app_name => g_app_name,
2785 p_msg_name => 'OKL_AM_ERR_INTERFACE',
2786 p_token1 => 'TRANSACTION_NUMBER',
2787 p_token1_value => lx_tip_tbl(record_number).transaction_number);
2788 message :='transaction_number in OKL_TERMNT_INTERFACE is ERROR for transaction number '||lx_tip_tbl(record_number).transaction_number;
2789 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2790 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','message 3 '||message);
2791 END IF;
2792
2793 ELSE
2794 l_chr_id := interface_dtls.contract_id;
2795 END IF;
2796 END LOOP;
2797
2798 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2799 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','lx_tip_tbl(record_number).status2 '
2800 ||lx_tip_tbl(record_number).status || 'l_chr_id = '|| l_chr_id);
2801 END IF;
2802
2803 IF(l_chr_id IS NULL OR l_chr_id = OKC_API.G_MISS_NUM)THEN
2804 lx_tip_tbl(record_number).status := 'ERROR';
2805 -- wrong transaction number
2806 OKC_API.set_message(p_app_name => g_app_name,
2807 p_msg_name => 'OKL_AM_JOIN_MIS_MATCH',
2808 p_token1 => 'TRANSACTION_NUMBER',
2809 p_token1_value => lx_tip_tbl(record_number).transaction_number);
2810 message :='wrong transaction number '||lx_tip_tbl(record_number).transaction_number;
2811 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2812 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','message 4 '||message);
2813 END IF;
2814 END IF;
2815 END IF;
2816 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2817 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','lx_tip_tbl(record_number).status3 '
2818 ||lx_tip_tbl(record_number).status);
2819 END IF;
2820 -- if no role specified default it to RECIPIENT
2821 IF(lx_tip_tbl(record_number).status <> 'ERROR') THEN
2822 IF(lx_tip_tbl(record_number).quote_role_code IS NULL OR lx_tip_tbl(record_number).quote_role_code = OKC_API.G_MISS_CHAR) THEN
2823 lx_tip_tbl(record_number).quote_role_code := 'RECIPIENT';
2824 END IF;
2825 IF(lx_tip_tbl(record_number).quote_role_code = 'RECIPIENT') THEN
2826 -- retrive the quote_party information
2827 l_count := 0;
2828 l_qpy_id := NULL;
2829 l_party_object_id1 := NULL;
2830 l_party_object_id2 := NULL;
2831 -- retrive quote party roles
2832 FOR object_ids IN quote_party_details_csr(l_chr_id,lp_tip_tbl(record_number).contract_party_role) LOOP
2833 l_qpy_id := object_ids.id;
2834 l_party_object_id1 := object_ids.object1_id1;
2835 l_party_object_id2 := object_ids.object1_id2;
2836 l_party_object_code := object_ids.jtot_object1_code;
2837 l_count := l_count+1;
2838 l_contract_party_number := NULL;
2839 /* If party is vendor */
2840 IF(l_party_object_code = 'OKX_VENDOR') THEN
2841 FOR vendor_dtls IN get_vendor_info_csr(l_party_object_id1, l_party_object_id2) LOOP
2842 l_contract_party_number := vendor_dtls.party_number;
2843 l_contract_party_id := l_party_object_id1;
2844 l_contract_party_name := vendor_dtls.name;
2845 -- populate all party details required
2846 IF(lp_tip_tbl(record_number).contract_party_number = l_contract_party_number) THEN
2847 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2848 lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
2849 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2850 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2851 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2852 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2853 l_count := -1;
2854 EXIT;
2855 ELSIF(lp_tip_tbl(record_number).contract_party_id = l_contract_party_id) THEN
2856 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2857 lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
2858 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2859 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2860 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2861 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2862 l_count := -1;
2863 EXIT;
2864 END IF;
2865 END LOOP;
2866 /* If party is Lessee */
2867 ELSIF(l_party_object_code = 'OKX_PARTY') THEN
2868 FOR lessee_dtls IN get_lessee_info_csr(l_party_object_id1, l_party_object_id2) LOOP
2869 l_contract_party_number := lessee_dtls.party_number;
2870 l_contract_party_name := lessee_dtls.party_name;
2871 l_email_address := lessee_dtls.email_address;
2872 l_contract_party_id := l_party_object_id1;
2873 -- populate all party details required
2874
2875 --sechawla 02-aug-2011 12745559
2876 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2877 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2878 'l_party_object_id1 '||l_party_object_id1 );
2879 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2880 'lx_tip_tbl(record_number).email_address .. '||lx_tip_tbl(record_number).email_address);
2881 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2882 'l_email_address .. '||l_email_address);
2883 END IF;
2884
2885 IF(lp_tip_tbl(record_number).contract_party_number = l_contract_party_number) THEN
2886 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2887 lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
2888 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2889
2890 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2891 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2892 'lx_tip_tbl(record_number).email_address after assignment.. '||lx_tip_tbl(record_number).email_address
2893 ||'l_email_address' ||l_email_address);
2894 END IF;
2895
2896 --sechawla 02-aug-2011 12745559 : added IF condition
2897 IF lx_tip_tbl(record_number).email_address IS NULL THEN
2898 lx_tip_tbl(record_number).email_address := l_email_address;
2899 END IF;
2900
2901 --sechawla 02-aug-2011 12745559
2902
2903
2904 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2905 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2906 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2907 l_count := -1;
2908 EXIT;
2909 ELSIF(lp_tip_tbl(record_number).contract_party_id = l_contract_party_id) THEN
2910 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2911 lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
2912 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2913
2914 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2915 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2916 'lx_tip_tbl(record_number).email_address after assignment.. '||lx_tip_tbl(record_number).email_address
2917 || 'l_email_address = '|| l_email_address);
2918 END IF;
2919
2920 --sechawla 02-aug-2011 12745559 : added IF condition
2921 IF lx_tip_tbl(record_number).email_address IS NULL THEN
2922 lx_tip_tbl(record_number).email_address := l_email_address;
2923 END IF;
2924
2925 --sechawla 02-aug-2011 12745559
2926
2927 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2928 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2929 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2930 l_count := -1;
2931 EXIT;
2932 END IF;
2933
2934
2935 END LOOP;
2936 END IF;
2937 IF(l_count = -1) THEN
2938 EXIT;
2939 END IF;
2940 END LOOP;
2941 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2942 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2943 'count ....... '||l_count);
2944 END IF;
2945
2946 IF(l_count = 0) THEN
2947 lx_tip_tbl(record_number). status := 'ERROR';
2948 -- neither lessee nor vendor
2949 OKC_API.set_message(p_app_name => g_app_name,
2950 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2951 p_token1 => 'COL_NAME',
2952 p_token1_value => 'CONTRACT PARTY ROLE');
2953 message :='quote party should be a lessee or vendor for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2954 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2955 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2956 'message11 '||message);
2957 END IF;
2958 ELSIF(l_count>1) THEN
2959 IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
2960 (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
2961 lx_tip_tbl(record_number). status := 'ERROR';
2962 -- neither contract_party_number nor contract_party_id are entered
2963 OKC_API.set_message(p_app_name => g_app_name,
2964 p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2965 p_token1 => 'COL_NAME',
2966 p_token1_value => 'CONTRACT_PARTY_NAME OR PARTY_ID');
2967 message :='contract_party_number or contract_party_id should be entered for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2968 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2969 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2970 'message 22 '||message);
2971 END IF;
2972 ELSIF(lp_tip_tbl(record_number).contract_party_number IS NOT NULL) THEN
2973 IF(l_contract_party_number <> lp_tip_tbl(record_number).contract_party_number) THEN
2974 lx_tip_tbl(record_number). status := 'ERROR';
2975 -- contract party number entered is not valid
2976 OKC_API.set_message(p_app_name => g_app_name,
2977 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2978 p_token1 => 'COL_NAME',
2979 p_token1_value => 'CONTRACT_PARTY_NUMBER');
2980 message :='contract party number entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2981 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2982 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2983 'message 33 '||message);
2984 END IF;
2985 END IF;
2986 ELSIF(lp_tip_tbl(record_number).contract_party_id IS NOT NULL) THEN
2987 IF(l_contract_party_id <> lp_tip_tbl(record_number).contract_party_id) THEN
2988 lx_tip_tbl(record_number). status := 'ERROR';
2989 -- contract party id entered is not valid
2990 OKC_API.set_message(p_app_name => g_app_name,
2991 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2992 p_token1 => 'COL_NAME',
2993 p_token1_value => 'CONTRACT_PARTY_NUMBER');
2994 message :='contract party id entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2995 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2996 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2997 'message 44 '||message);
2998 END IF;
2999 END IF;
3000 END IF;
3001 ELSIF(l_count = 1) THEN
3002 -- if only one party is associated then assign those parties even the user not entered
3003 IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
3004 (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
3005 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
3006 lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
3007 lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
3008 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
3009
3010 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3011 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
3012 'lx_tip_tbl(record_number).email_address after assignment.. '||lx_tip_tbl(record_number).email_address
3013 ||'l_email_address' ||l_email_address);
3014 END IF;
3015
3016 --sechawla 02-aug-2011 12745559 : added IF condition
3017 IF lx_tip_tbl(record_number).email_address IS NULL THEN
3018 lx_tip_tbl(record_number).email_address := l_email_address;
3019 END IF;
3020
3021 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
3022 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
3023 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
3024 ELSIF(lp_tip_tbl(record_number).contract_party_number IS NOT NULL) THEN
3025 IF(l_contract_party_number <> lp_tip_tbl(record_number).contract_party_number) THEN
3026 lx_tip_tbl(record_number). status := 'ERROR';
3027 -- contract party number entered is not valid
3028 OKC_API.set_message(p_app_name => g_app_name,
3029 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
3030 p_token1 => 'COL_NAME',
3031 p_token1_value => 'CONTRACT_PARTY_NUMBER');
3032 message :='contract party number entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
3033 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3034 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
3035 'message 55 '||message);
3036 END IF;
3037
3038 END IF;
3039 ELSIF(lp_tip_tbl(record_number).contract_party_id IS NOT NULL) THEN
3040 IF(l_contract_party_id <> lp_tip_tbl(record_number).contract_party_id) THEN
3041 lx_tip_tbl(record_number). status := 'ERROR';
3042 -- contract party id entered is not valid
3043 OKC_API.set_message(p_app_name => g_app_name,
3044 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
3045 p_token1 => 'COL_NAME',
3046 p_token1_value => 'CONTRACT_PARTY_NUMBER');
3047 message :='contract party id entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
3048 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3049 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
3050 'message 66 '||message);
3051 END IF;
3052 END IF;
3053 END IF;
3054 END IF;
3055 ELSE
3056 lx_tip_tbl(record_number).status := 'ERROR';
3057 -- not recipient
3058 OKC_API.set_message(p_app_name => g_app_name,
3059 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
3060 p_token1 => 'COL_NAME',
3061 p_token1_value => 'QUOTE_ROLE_CODE');
3062 message :='quote party type allowed is only RECIPIENT for transaction_number'||lx_tip_tbl(record_number).transaction_number;
3063 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3064 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
3065 'message 77 '||message);
3066 END IF;
3067 END IF;
3068 END IF;-- for cursor object_ids
3069 -- if party record is error make related record in INTERFACE table also ERROR, but not when duplicate record found
3070 IF(lx_tip_tbl(record_number).status = 'ERROR' AND l_dup_flag = 'N') THEN
3071 IF(x_tif_tbl.COUNT>0) THEN
3072 FOR term_rec IN x_tif_tbl.FIRST..x_tif_tbl.LAST
3073 LOOP
3074 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3075 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
3076 'if party record is error make related record in INTERFACE table also ERROR'
3077 || x_tif_tbl(term_rec).transaction_number
3078 || 'status '
3079 || x_tif_tbl(term_rec).status);
3080 END IF;
3081 IF(x_tif_tbl(term_rec).transaction_number = lx_tip_tbl(record_number).transaction_number) THEN
3082 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3083 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
3084 'x_tif_tbl(term_rec).transaction_number'|| x_tif_tbl(term_rec).transaction_number);
3085 END IF;
3086
3087 x_tif_tbl(term_rec).status := 'ERROR';
3088 l_msg_tbl(0).msg_text := message;
3089 log_messages(log_msg_flag => 'V',
3090 p_transaction_number => x_tif_tbl(term_rec).transaction_number,
3091 p_contract_number => x_tif_tbl(term_rec).contract_number,
3092 p_asset_number => x_tif_tbl(term_rec).asset_number,
3093 p_date_effective => x_tif_tbl(term_rec).date_effective_from,
3094 p_quote_type => x_tif_tbl(term_rec).quote_type_code,
3095 p_quote_reason => x_tif_tbl(term_rec).quote_reason_code,
3096 msg_tbl => l_msg_tbl );
3097
3098 EXIT;
3099 END IF;
3100 END LOOP;
3101 END IF;
3102 END IF;
3103 END IF; -- vsgandhi
3104 END LOOP;
3105 END IF;
3106 x_tip_tbl := lx_tip_tbl;
3107
3108 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3109 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','End(+)');
3110 END IF;
3111
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114 -- store SQL error message on message stack for caller
3115 OKC_API.set_message(p_app_name => g_app_name,
3116 p_msg_name => g_unexpected_error,
3117 p_token1 => g_sqlcode_token,
3118 p_token1_value => sqlcode,
3119 p_token2 => g_sqlerrm_token,
3120 p_token2_value => sqlerrm);
3121 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3122 l_msg_tbl(0).msg_text := 'validate_party:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3123 log_messages(log_msg_flag => 'V',
3124 msg_tbl => l_msg_tbl );
3125 END validate_party;
3126
3127 -- Start of comments
3128 --
3129 -- Procedure Name : update_party
3130 -- Description : This procedure updates the party table fields
3131 -- Business Rules :
3132 -- Parameters : Input parameters : p_tip_tbl,p_tif_tbl
3133 -- Version : 1.0
3134 -- History : 14-MAR-03 RABHUPAT Created
3135 -- End of comments
3136
3137 PROCEDURE update_party(p_api_version IN NUMBER,
3138 p_init_msg_list IN VARCHAR2,
3139 x_msg_count OUT NOCOPY NUMBER,
3140 x_msg_data OUT NOCOPY VARCHAR2,
3141 x_return_status OUT NOCOPY VARCHAR2,
3142 p_tip_rec IN tip_rec_type) IS
3143 l_api_version CONSTANT NUMBER := p_api_version;
3144 l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
3145 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3146 l_msg_tbl msg_tbl_type;
3147 BEGIN
3148 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3149 --Check API version, initialize message list and create savepoint.
3150 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3151 G_PKG_NAME,
3152 p_init_msg_list,
3153 l_api_version,
3154 p_api_version,
3155 '_PVT',
3156 x_return_status);
3157 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3159 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3160 RAISE OKC_API.G_EXCEPTION_ERROR;
3161 END IF;
3162 -- updates the party table
3163 UPDATE OKL_TERMNT_INTF_PTY
3164 SET
3165 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_ID = p_tip_rec.contract_party_id,
3166 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_ROLE = p_tip_rec.contract_party_role,
3167 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_NAME = p_tip_rec.contract_party_name,
3168 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_NUMBER = p_tip_rec.contract_party_number,
3169 OKL_TERMNT_INTF_PTY.PARTY_OBJECT_CODE = p_tip_rec.party_object_code,
3170 OKL_TERMNT_INTF_PTY.PARTY_OBJECT_ID1 = p_tip_rec.party_object_id1,
3171 OKL_TERMNT_INTF_PTY.PARTY_OBJECT_ID2 = p_tip_rec.party_object_id2,
3172 OKL_TERMNT_INTF_PTY.EMAIL_ADDRESS = p_tip_rec.email_address,
3173 OKL_TERMNT_INTF_PTY.ALLOCATION_PERCENTAGE = p_tip_rec.allocation_percentage,
3174 OKL_TERMNT_INTF_PTY.DELAY_DAYS = p_tip_rec.delay_days,
3175 OKL_TERMNT_INTF_PTY.QPY_ID = p_tip_rec.qpy_id,
3176 OKL_TERMNT_INTF_PTY.TRANSACTION_NUMBER = p_tip_rec.transaction_number,
3177 OKL_TERMNT_INTF_PTY.STATUS = p_tip_rec.status,
3178 OKL_TERMNT_INTF_PTY.REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1, NULL,FND_GLOBAL.CONC_REQUEST_ID),p_tip_rec.request_id),
3179 OKL_TERMNT_INTF_PTY.PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),p_tip_rec.program_application_id),
3180 OKL_TERMNT_INTF_PTY.PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),p_tip_rec.program_id),
3181 OKL_TERMNT_INTF_PTY.PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,p_tip_rec.program_update_date,SYSDATE),
3182 OKL_TERMNT_INTF_PTY.ATTRIBUTE_CATEGORY = p_tip_rec.attribute_category,
3183 OKL_TERMNT_INTF_PTY.ATTRIBUTE1 = p_tip_rec.attribute1,
3184 OKL_TERMNT_INTF_PTY.ATTRIBUTE2 = p_tip_rec.attribute2,
3185 OKL_TERMNT_INTF_PTY.ATTRIBUTE3 = p_tip_rec.attribute3,
3186 OKL_TERMNT_INTF_PTY.ATTRIBUTE4 = p_tip_rec.attribute4,
3187 OKL_TERMNT_INTF_PTY.ATTRIBUTE5 = p_tip_rec.attribute5,
3188 OKL_TERMNT_INTF_PTY.ATTRIBUTE6 = p_tip_rec.attribute6,
3189 OKL_TERMNT_INTF_PTY.ATTRIBUTE7 = p_tip_rec.attribute7,
3190 OKL_TERMNT_INTF_PTY.ATTRIBUTE8 = p_tip_rec.attribute8,
3191 OKL_TERMNT_INTF_PTY.ATTRIBUTE9 = p_tip_rec.attribute9,
3192 OKL_TERMNT_INTF_PTY.ATTRIBUTE10 = p_tip_rec.attribute10,
3193 OKL_TERMNT_INTF_PTY.ATTRIBUTE11 = p_tip_rec.attribute11,
3194 OKL_TERMNT_INTF_PTY.ATTRIBUTE12 = p_tip_rec.attribute12,
3195 OKL_TERMNT_INTF_PTY.ATTRIBUTE13 = p_tip_rec.attribute13,
3196 OKL_TERMNT_INTF_PTY.ATTRIBUTE14 = p_tip_rec.attribute14,
3197 OKL_TERMNT_INTF_PTY.ATTRIBUTE15 = p_tip_rec.attribute15,
3198 OKL_TERMNT_INTF_PTY.CREATED_BY = p_tip_rec.created_by,
3199 OKL_TERMNT_INTF_PTY.CREATION_DATE = p_tip_rec.creation_date,
3200 OKL_TERMNT_INTF_PTY.LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3201 OKL_TERMNT_INTF_PTY.LAST_UPDATE_DATE = SYSDATE,
3202 OKL_TERMNT_INTF_PTY.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
3203 OKL_TERMNT_INTF_PTY.QUOTE_ROLE_CODE = p_tip_rec.quote_role_code
3204 WHERE
3205 OKL_TERMNT_INTF_PTY.ROWID =p_tip_rec.row_id;
3206
3207 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3208 x_return_status := l_return_status;
3209 EXCEPTION
3210 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3211 x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3212 G_PKG_NAME,
3213 'OKC_API.G_RET_STS_ERROR',
3214 x_msg_count,
3215 x_msg_data,
3216 '_PVT');
3217 -- for unexpected error
3218 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3219 x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3220 G_PKG_NAME,
3221 'OKC_API.G_RET_STS_UNEXP_ERROR',
3222 x_msg_count,
3223 x_msg_data,
3224 '_PVT');
3225 WHEN OTHERS THEN
3226 -- store SQL error message on message stack for caller
3227 OKC_API.set_message(p_app_name => g_app_name,
3228 p_msg_name => g_unexpected_error,
3229 p_token1 => g_sqlcode_token,
3230 p_token1_value => sqlcode,
3231 p_token2 => g_sqlerrm_token,
3232 p_token2_value => sqlerrm);
3233 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3234 l_msg_tbl(0).msg_text := 'update_party:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3235 log_messages(log_msg_flag => 'V',
3236 msg_tbl => l_msg_tbl );
3237 END update_party;
3238
3239 -- Start of comments
3240 --
3241 -- Procedure Name : select_party_info
3242 -- Description : This procedure retrives the data from database
3243 -- : based on status and updates the party table fields
3244 -- Business Rules :
3245 -- Parameters : Input parameters : p_tip_tbl,p_pty_status
3246 -- Version : 1.0
3247 -- History : 14-MAR-03 RABHUPAT Created
3248 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
3249 -- End of comments
3250
3251 PROCEDURE select_party_info(
3252 p_api_version IN NUMBER,
3253 p_init_msg_list IN VARCHAR2,
3254 x_msg_count OUT NOCOPY NUMBER,
3255 x_msg_data OUT NOCOPY VARCHAR2,
3256 x_return_status OUT NOCOPY VARCHAR2,
3257 p_tip_tbl IN tip_tbl_type,
3258 x_tip_tbl OUT NOCOPY tip_tbl_type,
3259 p_pty_status IN VARCHAR2
3260 ) IS
3261 record_number NUMBER;
3262 l_msg_tbl msg_tbl_type;
3263 /* this cursor rearranges the data by using the columns contract_number,
3264 quote_type_code, quote_reason_code and asset_id */
3265 CURSOR intface_pty_csr(p_status IN VARCHAR2) IS
3266 SELECT
3267 ROWID
3268 ,CONTRACT_PARTY_ID
3269 ,CONTRACT_PARTY_ROLE
3270 ,CONTRACT_PARTY_NAME
3271 ,CONTRACT_PARTY_NUMBER
3272 ,PARTY_OBJECT_CODE
3273 ,PARTY_OBJECT_ID1
3274 ,PARTY_OBJECT_ID2
3275 ,EMAIL_ADDRESS
3276 ,ALLOCATION_PERCENTAGE
3277 ,DELAY_DAYS
3278 ,QPY_ID
3279 ,TRANSACTION_NUMBER
3280 ,STATUS
3281 ,REQUEST_ID
3282 ,PROGRAM_APPLICATION_ID
3283 ,PROGRAM_ID
3284 ,PROGRAM_UPDATE_DATE
3285 ,ATTRIBUTE_CATEGORY
3286 ,ATTRIBUTE1
3287 ,ATTRIBUTE2
3288 ,ATTRIBUTE3
3289 ,ATTRIBUTE4
3290 ,ATTRIBUTE5
3291 ,ATTRIBUTE6
3292 ,ATTRIBUTE7
3293 ,ATTRIBUTE8
3294 ,ATTRIBUTE9
3295 ,ATTRIBUTE10
3296 ,ATTRIBUTE11
3297 ,ATTRIBUTE12
3298 ,ATTRIBUTE13
3299 ,ATTRIBUTE14
3300 ,ATTRIBUTE15
3301 ,CREATED_BY
3302 ,CREATION_DATE
3303 ,LAST_UPDATED_BY
3304 ,LAST_UPDATE_DATE
3305 ,LAST_UPDATE_LOGIN
3306 ,QUOTE_ROLE_CODE
3307 FROM OKL_TERMNT_INTF_PTY
3308 WHERE STATUS = P_STATUS
3309 ORDER BY TRANSACTION_NUMBER;
3310
3311 BEGIN
3312
3313 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3314 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info','Begin(+)');
3315 END IF;
3316 record_number:=0;
3317 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3318
3319 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3320 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3321 'p_tip_tbl.COUNT : '||p_tip_tbl.COUNT);
3322 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3323 'record_number : '||record_number);
3324 end if;
3325
3326 IF (p_tip_tbl.COUNT > 0) THEN
3327 record_number := p_tip_tbl.FIRST;
3328
3329 -- loops throught the table of records to update the values
3330 LOOP
3331 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3332 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3333 'inside p_tip_tbl.COUNT > 0 LOOP : '||record_number
3334 || 'transaction number' || p_tip_tbl(record_number).transaction_number
3335 || 'email address ' || p_tip_tbl(record_number).email_address);
3336 END IF;
3337
3338 update_party(p_api_version => p_api_version
3339 ,p_init_msg_list => OKC_API.G_FALSE
3340 ,x_msg_data => x_msg_data
3341 ,x_msg_count => x_msg_count
3342 ,x_return_status => x_return_status
3343 ,p_tip_rec => p_tip_tbl(record_number));
3344 EXIT WHEN (record_number = p_tip_tbl.LAST);
3345 record_number := p_tip_tbl.NEXT(record_number);
3346 END LOOP;
3347
3348 END IF;
3349
3350
3351 record_number:=0;
3352 x_tip_tbl.DELETE;
3353
3354 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3355 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3356 ' p_pty_status = ' || p_pty_status );
3357 END IF;
3358 -- cursor to retrive the data from party table
3359 FOR party_rec IN intface_pty_csr(p_status => p_pty_status) LOOP
3360 x_tip_tbl(record_number).row_id := party_rec.rowid;
3361 x_tip_tbl(record_number).contract_party_id := party_rec.contract_party_id;
3362 x_tip_tbl(record_number).contract_party_role := party_rec.contract_party_role;
3363 x_tip_tbl(record_number).contract_party_name := party_rec.contract_party_name;
3364 x_tip_tbl(record_number).contract_party_number := party_rec.contract_party_number;
3365 x_tip_tbl(record_number).party_object_code := party_rec.party_object_code;
3366 x_tip_tbl(record_number).party_object_id1 := party_rec.party_object_id1;
3367 x_tip_tbl(record_number).party_object_id2 := party_rec.party_object_id2;
3368 x_tip_tbl(record_number).email_address := party_rec.email_address;
3369 x_tip_tbl(record_number).allocation_percentage := party_rec.allocation_percentage;
3370 x_tip_tbl(record_number).delay_days := party_rec.delay_days;
3371 x_tip_tbl(record_number).qpy_id := party_rec.qpy_id;
3372 x_tip_tbl(record_number).transaction_number := party_rec.transaction_number;
3373 x_tip_tbl(record_number).status := party_rec.status;
3374 x_tip_tbl(record_number).request_id := party_rec.request_id;
3375 x_tip_tbl(record_number).program_application_id := party_rec.program_application_id;
3376 x_tip_tbl(record_number).program_id := party_rec.program_id;
3377 x_tip_tbl(record_number).program_update_date := party_rec.program_update_date;
3378 x_tip_tbl(record_number).attribute_category := party_rec.attribute_category;
3379 x_tip_tbl(record_number).attribute1 := party_rec.attribute1;
3380 x_tip_tbl(record_number).attribute2 := party_rec.attribute2;
3381 x_tip_tbl(record_number).attribute3 := party_rec.attribute3;
3382 x_tip_tbl(record_number).attribute4 := party_rec.attribute4;
3383 x_tip_tbl(record_number).attribute5 := party_rec.attribute5;
3384 x_tip_tbl(record_number).attribute6 := party_rec.attribute6;
3385 x_tip_tbl(record_number).attribute7 := party_rec.attribute7;
3386 x_tip_tbl(record_number).attribute8 := party_rec.attribute8;
3387 x_tip_tbl(record_number).attribute9 := party_rec.attribute9;
3388 x_tip_tbl(record_number).attribute10 := party_rec.attribute10;
3389 x_tip_tbl(record_number).attribute11 := party_rec.attribute11;
3390 x_tip_tbl(record_number).attribute12 := party_rec.attribute12;
3391 x_tip_tbl(record_number).attribute13 := party_rec.attribute13;
3392 x_tip_tbl(record_number).attribute14 := party_rec.attribute14;
3393 x_tip_tbl(record_number).attribute15 := party_rec.attribute15;
3394 x_tip_tbl(record_number).created_by := party_rec.created_by;
3395 x_tip_tbl(record_number).creation_date := party_rec.creation_date;
3396 x_tip_tbl(record_number).last_updated_by := party_rec.last_updated_by;
3397 x_tip_tbl(record_number).last_update_date := party_rec.last_update_date;
3398 x_tip_tbl(record_number).last_update_login := party_rec.last_update_login;
3399 x_tip_tbl(record_number).quote_role_code := party_rec.quote_role_code;
3400
3401 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3402 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3403 ' inside party loop : '||record_number||
3404 ' transaction_number :'||x_tip_tbl(record_number).transaction_number ||
3405 ' email address : '||x_tip_tbl(record_number).email_address);
3406 END IF;
3407 record_number := record_number+1;
3408 END LOOP;
3409 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3410 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3411 ' total records = ' || record_number );
3412 END IF;
3413
3414 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3415 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info','End(+)');
3416 END IF;
3417
3418 EXCEPTION
3419 WHEN OTHERS THEN
3420 -- store SQL error message on message stack for caller
3421 OKC_API.set_message(p_app_name => g_app_name,
3422 p_msg_name => g_unexpected_error,
3423 p_token1 => g_sqlcode_token,
3424 p_token1_value => sqlcode,
3425 p_token2 => g_sqlerrm_token,
3426 p_token2_value => sqlerrm);
3427 l_msg_tbl(0).msg_text := 'select_party_info:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3428 log_messages(log_msg_flag => 'V',
3429 msg_tbl => l_msg_tbl );
3430 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3431 END select_party_info;
3432
3433 -- Start of comments
3434 --
3435 -- Procedure Name : populate_party_for_quote
3436 -- Description : This procedure populates the party information
3437 -- : and creates the quote
3438 -- Business Rules :
3439 -- Parameters : Input parameters : p_tip_tbl,p_tif_tbl
3440 -- Version : 1.0
3441 -- History : 14-MAR-03 RABHUPAT Created
3442 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
3443 -- : 10-Aug-2012 vsgandhi ER 11937978
3444 -- End of comments
3445
3446
3447 PROCEDURE populate_party_for_quote(
3448 p_api_version IN NUMBER,
3449 p_init_msg_list IN VARCHAR2,
3450 x_msg_count OUT NOCOPY NUMBER,
3451 x_msg_data OUT NOCOPY VARCHAR2,
3452 x_return_status OUT NOCOPY VARCHAR2,
3453 p_tip_tbl IN tip_tbl_type,
3454 p_tif_tbl IN tif_tbl_type,
3455 x_tif_tbl OUT NOCOPY tif_tbl_type,
3456 x_group_number OUT NOCOPY NUMBER)
3457 IS
3458 l_tip_tbl tip_tbl_type;
3459 lx_tif_tbl tif_tbl_type;
3460 lp_quot_rec quot_rec_type;
3461 lx_quot_rec quot_rec_type;
3462 lp_assn_tbl assn_tbl_type;
3463 lx_assn_tbl assn_tbl_type;
3464 lx_tqlv_tbl tqlv_tbl_type;
3465 lp_qpyv_tbl qpyv_tbl_type;
3466 lx_qpyv_tbl qpyv_tbl_type;
3467 l_msg_tbl msg_tbl_type;
3468 l_lessee_tip_tbl tip_tbl_type;
3469 l_vendor_tip_tbl tip_tbl_type;
3470 lp_lessee_assn_tbl assn_tbl_type;
3471 lp_vendor_assn_tbl assn_tbl_type;
3472 x_out_tif_tbl tif_tbl_type;
3473 l_lessee_tif_tbl tif_tbl_type;
3474 l_vendor_tif_tbl tif_tbl_type;
3475 l_out_tif_rec NUMBER := 0;
3476 l_quote_lines NUMBER := 0;
3477 v_quote_lines NUMBER := 0;
3478 l_lessee_count NUMBER := 0;
3479 l_vendor_count NUMBER := 0;
3480 l_party_count NUMBER := 0;
3481 x_record_count NUMBER;
3482 group_number NUMBER := 0;
3483 l_contract_record NUMBER;
3484 l_qte_msg_count NUMBER := 0;
3485 x_q_party_uv_tbl OKL_AM_PARTIES_PVT.q_party_uv_tbl_type;
3486 l_tif_rec tif_rec_type;
3487 l_contract_number l_tif_rec.contract_number%TYPE;
3488 l_quote_type l_tif_rec.quote_type_code%TYPE;
3489 l_quote_reason l_tif_rec.quote_reason_code%TYPE;
3490 l_date_effective_from l_tif_rec.date_effective_from%TYPE;
3491 l_quote_role_code OKL_TERMNT_INTF_PTY.QUOTE_ROLE_CODE%TYPE;
3492
3493 l_error_msg_rec ERROR_MESSAGE_TYPE;
3494 BEGIN
3495 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3496 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote',
3497 'Begin(+)');
3498 END IF;
3499 x_group_number := 0;
3500 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3501
3502 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3503 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3504 'p_tip_tbl count :'||p_tip_tbl.count||' p_tif_tbl count : '||p_tif_tbl.count);
3505 END IF;
3506
3507 -- Rearrange the party table records wrt interface table records order
3508 IF(p_tif_tbl.COUNT >0) THEN
3509 FOR i IN p_tif_tbl.FIRST..p_tif_tbl.LAST
3510 LOOP
3511 IF(p_tip_tbl.COUNT>0) THEN
3512 FOR j IN p_tip_tbl.FIRST..p_tip_tbl.LAST
3513 LOOP
3514 IF p_tip_tbl(j).transaction_number = p_tif_tbl(i).transaction_number THEN
3515 l_tip_tbl(l_party_count) := p_tip_tbl(j) ;
3516 l_party_count := l_party_count+1;
3517 END IF;
3518 END LOOP;
3519 END IF;
3520 END LOOP;
3521 END IF;
3522 -- End Rearrange the party recs
3523
3524 IF(p_tif_tbl.COUNT >0) THEN
3525 FOR record_number IN p_tif_tbl.FIRST..p_tif_tbl.LAST
3526 LOOP
3527 IF(l_tip_tbl.COUNT>0) THEN
3528 FOR party_rec IN l_tip_tbl.FIRST..l_tip_tbl.LAST
3529 LOOP
3530 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3531 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3532 'party table count = '|| party_rec || 'p_tip_tbl record status :'||l_tip_tbl(party_rec).status ||
3533 'transaction number = ' || l_tip_tbl(party_rec).transaction_number );
3534 END IF;
3535 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3536 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3537 'interface table count = ' || record_number || 'p_tip_tbl record status : :'||p_tif_tbl(record_number).status ||
3538 'transaction Number =' || p_tif_tbl(record_number).transaction_number );
3539 END IF;
3540
3541 -- If interface table and party table transaction numbers match
3542 IF l_tip_tbl(party_rec).transaction_number = p_tif_tbl(record_number).transaction_number THEN
3543
3544 --errorout_vg('Processing transaction number = '|| l_tip_tbl(party_rec).transaction_number );
3545 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3546 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3547 'processing begins for transaction number = ' || l_tip_tbl(party_rec).transaction_number );
3548 END IF;
3549
3550 -- checking the condition to separate the batches for quote creation
3551 IF(((l_contract_number <> p_tif_tbl(record_number).contract_number) OR
3552 (l_quote_type <> p_tif_tbl(record_number).quote_type_code) OR
3553 (l_quote_reason <> p_tif_tbl(record_number).quote_reason_code) OR
3554 (l_date_effective_from <> p_tif_tbl(record_number).date_effective_from)) AND
3555 (party_rec <> l_tip_tbl.FIRST)) THEN
3556
3557 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3558 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3559 'Inside not equal condition ' );
3560 END IF;
3561 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3562 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3563 'Fetch rule Quote parties for contract id = ' || p_tif_tbl(l_contract_record).contract_id ||
3564 'org id = '|| p_tif_tbl(l_contract_record).org_id ||
3565 'Date effective form = ' || p_tif_tbl(l_contract_record).date_effective_from);
3566 END IF;
3567
3568 --errorout_vg('Matching record not found');
3569 -- populates the lp_quot_rec and calls create_quote
3570 lp_quot_rec.khr_id := p_tif_tbl(l_contract_record).contract_id;
3571 lp_quot_rec.date_effective_from := p_tif_tbl(l_contract_record).date_effective_from;
3572 lp_quot_rec.org_id := p_tif_tbl(l_contract_record).org_id;
3573 lp_quot_rec.qtp_code := p_tif_tbl(l_contract_record).quote_type_code;
3574 lp_quot_rec.qrs_code := p_tif_tbl(l_contract_record).quote_reason_code;
3575 OKL_AM_PARTIES_PVT.fetch_rule_quote_parties
3576 ( p_api_version => p_api_version,
3577 p_init_msg_list => OKC_API.G_FALSE,
3578 x_msg_count => x_msg_count,
3579 x_msg_data => x_msg_data,
3580 x_return_status => x_return_status,
3581 p_qtev_rec => lp_quot_rec,
3582 x_qpyv_tbl => lp_qpyv_tbl,
3583 x_record_count => x_record_count,
3584 x_q_party_uv_tbl => x_q_party_uv_tbl);
3585
3586 --errorout_vg('AFter OKL_AM_PARTIES_PVT.fetch_rule_quote_parties ' || x_return_status);
3587 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3588 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3589 'Fetch rule Quote parties status = ' || x_return_status ||
3590 'Party record count' || lp_qpyv_tbl.COUNT );
3591 END IF;
3592 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3593 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3594 'Lessee tip table count = ' || l_lessee_tip_tbl.count );
3595 END IF;
3596
3597 --errorout_vg('Lessee tip table count = '|| l_lessee_tip_tbl.count );
3598 -- Processing records with lessee as the receipint
3599 IF l_lessee_tip_tbl.count >0 THEN
3600 IF(lp_qpyv_tbl.COUNT >0) THEN
3601 FOR rule_party IN lp_qpyv_tbl.FIRST..lp_qpyv_tbl.LAST
3602 LOOP
3603 IF(lp_qpyv_tbl(rule_party).qpt_code = l_lessee_tip_tbl(0).quote_role_code) THEN
3604 lp_qpyv_tbl(rule_party).party_jtot_object1_code := l_lessee_tip_tbl(0).party_object_code;
3605 lp_qpyv_tbl(rule_party).party_object1_id1 := l_lessee_tip_tbl(0).party_object_id1;
3606 lp_qpyv_tbl(rule_party).party_object1_id2 := l_lessee_tip_tbl(0).party_object_id2;
3607 lp_qpyv_tbl(rule_party).cpl_id := l_lessee_tip_tbl(0).qpy_id;
3608 IF l_lessee_tip_tbl(0).email_address IS NOT NULL THEN
3609 lp_qpyv_tbl(rule_party).email_address := l_lessee_tip_tbl(0).email_address;
3610 END IF;
3611 END IF;
3612 END LOOP;
3613 END IF;
3614
3615 -- calls Create quote parties with p_validate_only = TRUE
3616 OKL_AM_PARTIES_PVT.create_quote_parties
3617 ( p_qtev_rec => lp_quot_rec,
3618 p_qpyv_tbl => lp_qpyv_tbl,
3619 p_validate_only => TRUE,
3620 x_qpyv_tbl => lx_qpyv_tbl,
3621 x_return_status => x_return_status);
3622
3623 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3624 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3625 'Create Quote Parties status = ' || x_return_status );
3626 END IF;
3627
3628 GET_ERROR_MESSAGE(l_error_msg_rec);
3629 IF (l_error_msg_rec.COUNT > 0) THEN
3630 l_qte_msg_count := 0;
3631 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3632 LOOP
3633 IF(LENGTH(l_error_msg_rec(m)) >0) THEN
3634 l_msg_tbl(l_qte_msg_count).msg_text := l_error_msg_rec(m);
3635 l_qte_msg_count := l_qte_msg_count+1;
3636 END IF;
3637 END LOOP;
3638 END IF;
3639 --errorout_vg('Create Quote Party status = '|| x_return_status );
3640 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3641 lx_tif_tbl.DELETE;
3642 create_quote(
3643 p_api_version => p_api_version ,
3644 p_init_msg_list => OKC_API.G_FALSE,
3645 x_msg_count => x_msg_count ,
3646 x_msg_data => x_msg_data ,
3647 x_return_status => x_return_status ,
3648 p_tif_tbl => l_lessee_tif_tbl ,
3649 x_tif_tbl => lx_tif_tbl ,
3650 p_quot_rec => lp_quot_rec ,
3651 p_assn_tbl => lp_lessee_assn_tbl ,
3652 p_qpyv_tbl => lp_qpyv_tbl ,
3653 p_batch_offset => l_lessee_tif_tbl.FIRST ,
3654 p_record_number => l_lessee_tif_tbl.LAST);
3655
3656 --errorout_vg('Create Quote Status = ' || x_return_status);
3657 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3658 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3659 'Create Quote Status for lessee = ' || x_return_status );
3660 END IF;
3661
3662 l_lessee_tif_tbl.delete;
3663 l_lessee_tif_tbl := lx_tif_tbl;
3664 lx_tif_tbl.delete;
3665
3666 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_lessee_tif_tbl(0).status = 'PROCESSED') THEN
3667 FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
3668 LOOP
3669 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3670 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3671 'Update Party to Processed = ' || l_lessee_tip_tbl(i).transaction_number );
3672 END IF;
3673 l_lessee_tip_tbl(i).status :='PROCESSED';
3674 update_party(
3675 p_api_version => p_api_version ,
3676 p_init_msg_list => OKC_API.G_FALSE ,
3677 x_msg_data => x_msg_data ,
3678 x_msg_count => x_msg_count ,
3679 x_return_status => x_return_status ,
3680 p_tip_rec => l_lessee_tip_tbl(i));
3681 --errorout_vg('Update Party Status = '|| x_return_status || 'For Transaction '
3682 -- || l_lessee_tip_tbl(i).transaction_number);
3683 END LOOP;
3684
3685 FOR i IN l_lessee_tif_tbl.FIRST..l_lessee_tif_tbl.LAST
3686 LOOP
3687 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3688 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3689 'Update interface record group number for :' || l_lessee_tif_tbl(i).transaction_number ||
3690 ' group number = ' ||group_number );
3691 END IF;
3692
3693 l_lessee_tif_tbl(i).group_number := group_number;
3694 update_row(
3695 p_api_version => p_api_version ,
3696 p_init_msg_list => OKC_API.G_FALSE ,
3697 x_msg_data => x_msg_data ,
3698 x_msg_count => x_msg_count ,
3699 x_return_status => x_return_status ,
3700 p_tif_rec => l_lessee_tif_tbl(i));
3701
3702 --errorout_vg('Update Row Status = '|| x_return_status || 'For Transaction '
3703 -- || l_lessee_tip_tbl(i).transaction_number
3704 -- ||'group_number = ' ||group_number );
3705 -- Save message from stack into transaction message table
3706
3707 OKL_AM_UTIL_PVT.process_messages(
3708 p_trx_source_table => 'OKL_TERMNT_INTERFACE',
3709 p_trx_id => l_lessee_tif_tbl(i).transaction_number,
3710 x_return_status => x_return_status);
3711
3712 END LOOP;
3713 group_number := group_number+1;
3714
3715 ELSE
3716 FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
3717 LOOP
3718 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3719 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3720 'Update Party to Errored = ' || l_lessee_tip_tbl(i).transaction_number );
3721 END IF;
3722
3723 l_lessee_tip_tbl(i).status :='ERROR';
3724 update_party(
3725 p_api_version => p_api_version ,
3726 p_init_msg_list => OKC_API.G_FALSE ,
3727 x_msg_data => x_msg_data ,
3728 x_msg_count => x_msg_count ,
3729 x_return_status => x_return_status ,
3730 p_tip_rec => l_lessee_tip_tbl(i));
3731
3732 --errorout_vg('Update Party Status = '|| x_return_status || 'For Transaction '
3733 -- || l_lessee_tip_tbl(i).transaction_number);
3734 END LOOP;
3735
3736 END IF; -- End if for x_return_status success
3737
3738 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3739 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3740 'Create Quote status = ' || x_return_status );
3741 END IF;
3742
3743 FOR i IN l_lessee_tif_tbl.FIRST..l_lessee_tif_tbl.LAST
3744 LOOP
3745 x_out_tif_tbl(l_out_tif_rec) := l_lessee_tif_tbl(i);
3746 l_out_tif_rec := l_out_tif_rec+1;
3747 END LOOP;
3748 l_lessee_tif_tbl.delete;
3749
3750 ELSE -- else for party creation failed
3751
3752 FOR i IN l_lessee_tif_tbl.FIRST..l_lessee_tif_tbl.LAST
3753 LOOP
3754 l_lessee_tif_tbl(i).status := 'ERROR';
3755 l_msg_tbl(l_qte_msg_count).msg_text := 'quote not created for transaction_number '||l_lessee_tif_tbl(i).transaction_number;
3756
3757 log_messages(
3758 log_msg_flag => 'V',
3759 p_transaction_number => l_lessee_tif_tbl(i).transaction_number,
3760 p_contract_number => l_lessee_tif_tbl(i).contract_number,
3761 p_asset_number => l_lessee_tif_tbl(i).asset_number,
3762 p_date_effective => l_lessee_tif_tbl(i).date_effective_from,
3763 p_quote_type => l_lessee_tif_tbl(i).quote_type_code,
3764 p_quote_reason => l_lessee_tif_tbl(i).quote_reason_code,
3765 msg_tbl => l_msg_tbl );
3766 END LOOP;
3767
3768 FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
3769 LOOP
3770
3771 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3772 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3773 'Update Party to Errored = ' || l_lessee_tip_tbl(i).transaction_number );
3774 END IF;
3775
3776 l_lessee_tip_tbl(i).status :='ERROR';
3777 update_party(
3778 p_api_version => p_api_version ,
3779 p_init_msg_list => OKC_API.G_FALSE ,
3780 x_msg_data => x_msg_data ,
3781 x_msg_count => x_msg_count ,
3782 x_return_status => x_return_status ,
3783 p_tip_rec => l_lessee_tip_tbl(i));
3784 END LOOP;
3785 END IF; -- if party creation success
3786 END IF; -- l_lesseee_tip_tbl
3787
3788 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3789 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3790 'Vendor tip table count = ' || l_vendor_tip_tbl.count );
3791 END IF;
3792
3793 --errorout_vg('Vendor tip table count = '|| l_vendor_tip_tbl.count );
3794 IF l_vendor_tip_tbl.count >0 THEN
3795 IF(lp_qpyv_tbl.COUNT >0) THEN
3796 FOR rule_party IN lp_qpyv_tbl.FIRST..lp_qpyv_tbl.LAST
3797 LOOP
3798 IF(lp_qpyv_tbl(rule_party).qpt_code = l_vendor_tip_tbl(0).quote_role_code) THEN
3799 lp_qpyv_tbl(rule_party).party_jtot_object1_code := l_vendor_tip_tbl(0).party_object_code;
3800 lp_qpyv_tbl(rule_party).party_object1_id1 := l_vendor_tip_tbl(0).party_object_id1;
3801 lp_qpyv_tbl(rule_party).party_object1_id2 := l_vendor_tip_tbl(0).party_object_id2;
3802 lp_qpyv_tbl(rule_party).cpl_id := l_vendor_tip_tbl(0).qpy_id;
3803 IF l_vendor_tip_tbl(0).email_address IS NOT NULL THEN
3804 lp_qpyv_tbl(rule_party).email_address := l_vendor_tip_tbl(0).email_address;
3805 END IF;
3806 END IF;
3807 END LOOP;
3808 END IF;
3809
3810 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3811 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3812 'Before Create quote parties ' || lp_qpyv_tbl.count );
3813 END IF;
3814
3815 -- calls Create quote parties with p_validate_only = TRUE
3816 OKL_AM_PARTIES_PVT.create_quote_parties (
3817 p_qtev_rec => lp_quot_rec,
3818 p_qpyv_tbl => lp_qpyv_tbl,
3819 p_validate_only => TRUE,
3820 x_qpyv_tbl => lx_qpyv_tbl,
3821 x_return_status => x_return_status);
3822
3823 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3824 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3825 'Create Quote Parties status = ' || x_return_status );
3826 END IF;
3827
3828 GET_ERROR_MESSAGE(l_error_msg_rec);
3829 IF (l_error_msg_rec.COUNT > 0) THEN
3830 l_qte_msg_count := 0;
3831 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3832 LOOP
3833 IF(LENGTH(l_error_msg_rec(m)) >0) THEN
3834 l_msg_tbl(l_qte_msg_count).msg_text := l_error_msg_rec(m);
3835 l_qte_msg_count := l_qte_msg_count+1;
3836 END IF;
3837 END LOOP;
3838 END IF;
3839
3840 --errorout_vg('Create quote parties status = '|| x_return_status);
3841 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3842 lx_tif_tbl.DELETE;
3843 create_quote(
3844 p_api_version => p_api_version ,
3845 p_init_msg_list => OKC_API.G_FALSE,
3846 x_msg_count => x_msg_count ,
3847 x_msg_data => x_msg_data ,
3848 x_return_status => x_return_status ,
3849 p_tif_tbl => l_vendor_tif_tbl ,
3850 x_tif_tbl => lx_tif_tbl ,
3851 p_quot_rec => lp_quot_rec ,
3852 p_assn_tbl => lp_vendor_assn_tbl ,
3853 p_qpyv_tbl => lp_qpyv_tbl ,
3854 p_batch_offset => l_vendor_tif_tbl.FIRST ,
3855 p_record_number => l_vendor_tif_tbl.LAST);
3856
3857 --errorout_vg('Create Quote Status = '|| x_return_status);
3858 l_vendor_tif_tbl.delete;
3859 l_vendor_tif_tbl := lx_tif_tbl;
3860 lx_tif_tbl.delete;
3861
3862 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3863 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3864 'Create Quote status = ' || x_return_status );
3865 END IF;
3866
3867 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_vendor_tif_tbl(0).status = 'PROCESSED' ) THEN
3868 FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
3869 LOOP
3870 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3871 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3872 'Update Party to processed = ' || l_vendor_tip_tbl(i).transaction_number );
3873 END IF;
3874
3875 l_vendor_tip_tbl(i).status := 'PROCESSED';
3876 update_party(
3877 p_api_version => p_api_version ,
3878 p_init_msg_list => OKC_API.G_FALSE ,
3879 x_msg_data => x_msg_data ,
3880 x_msg_count => x_msg_count ,
3881 x_return_status => x_return_status ,
3882 p_tip_rec => l_vendor_tip_tbl(i));
3883
3884 END LOOP;
3885 FOR i IN l_vendor_tif_tbl.FIRST..l_vendor_tif_tbl.LAST
3886 LOOP
3887
3888 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3889 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3890 'Update interface record group number for ' || l_vendor_tif_tbl(i).transaction_number ||
3891 'group number = ' ||group_number );
3892
3893 END IF;
3894 l_vendor_tif_tbl(i).group_number := group_number;
3895 update_row(
3896 p_api_version => p_api_version ,
3897 p_init_msg_list => OKC_API.G_FALSE ,
3898 x_msg_data => x_msg_data ,
3899 x_msg_count => x_msg_count ,
3900 x_return_status => x_return_status ,
3901 p_tif_rec => l_vendor_tif_tbl(i));
3902
3903 -- Save message from stack into transaction message table
3904 OKL_AM_UTIL_PVT.process_messages(
3905 p_trx_source_table => 'OKL_TERMNT_INTERFACE',
3906 p_trx_id => l_vendor_tif_tbl(i).transaction_number,
3907 x_return_status => x_return_status);
3908
3909 END LOOP;
3910 group_number := group_number+1;
3911
3912 ELSE
3913 FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
3914 LOOP
3915 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3916 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3917 'Update Party to Error = ' || l_vendor_tip_tbl(i).transaction_number );
3918 END IF;
3919
3920 l_vendor_tip_tbl(i).status := 'ERROR';
3921 update_party(
3922 p_api_version => p_api_version ,
3923 p_init_msg_list => OKC_API.G_FALSE ,
3924 x_msg_data => x_msg_data ,
3925 x_msg_count => x_msg_count ,
3926 x_return_status => x_return_status ,
3927 p_tip_rec => l_vendor_tip_tbl(i));
3928 END LOOP;
3929
3930 END IF; -- end of create quote success
3931
3932 FOR i IN l_vendor_tif_tbl.FIRST..l_vendor_tif_tbl.LAST
3933 LOOP
3934 x_out_tif_tbl(l_out_tif_rec) := l_vendor_tif_tbl(i);
3935 l_out_tif_rec := l_out_tif_rec+1;
3936 END LOOP;
3937 l_vendor_tif_tbl.delete;
3938
3939 ELSE
3940 FOR i IN l_vendor_tif_tbl.FIRST..l_vendor_tif_tbl.LAST
3941 LOOP
3942 l_vendor_tif_tbl(i).status := 'ERROR';
3943 l_msg_tbl(l_qte_msg_count).msg_text := 'quote not created for transaction_number '||l_vendor_tif_tbl(i).transaction_number;
3944 log_messages(
3945 log_msg_flag => 'V',
3946 p_transaction_number => l_vendor_tif_tbl(i).transaction_number,
3947 p_contract_number => l_vendor_tif_tbl(i).contract_number,
3948 p_asset_number => l_vendor_tif_tbl(i).asset_number,
3949 p_date_effective => l_vendor_tif_tbl(i).date_effective_from,
3950 p_quote_type => l_vendor_tif_tbl(i).quote_type_code,
3951 p_quote_reason => l_vendor_tif_tbl(i).quote_reason_code,
3952 msg_tbl => l_msg_tbl );
3953 END LOOP;
3954
3955 FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
3956 LOOP
3957 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3958 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3959 'Update Party to Error = ' || l_vendor_tip_tbl(i).transaction_number);
3960 END IF;
3961
3962 l_vendor_tip_tbl(i).status := 'ERROR';
3963 update_party(
3964 p_api_version => p_api_version ,
3965 p_init_msg_list => OKC_API.G_FALSE ,
3966 x_msg_data => x_msg_data ,
3967 x_msg_count => x_msg_count ,
3968 x_return_status => x_return_status ,
3969 p_tip_rec => l_vendor_tip_tbl(i));
3970 END LOOP;
3971 END IF;
3972 END IF; -- l_vendor_tip_tbl
3973
3974 lp_assn_tbl.DELETE;
3975 lx_tif_tbl.DELETE;
3976 l_lessee_tip_tbl.DELETE;
3977 l_vendor_tip_tbl.DELETE;
3978 lp_lessee_assn_tbl.DELETE;
3979 lp_vendor_assn_tbl.DELETE;
3980 l_lessee_tif_tbl.DELETE;
3981 l_vendor_tif_tbl.DELETE;
3982 l_lessee_count := 0;
3983 l_vendor_count := 0;
3984 l_quote_lines := 0;
3985 v_quote_lines := 0;
3986
3987 END IF;--not equal condition
3988
3989 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3990 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3991 'Outside <> condition ' );
3992 END IF;
3993
3994 --errorout_vg('Out side not matching condition');
3995 -- copy p_tif_tbl record in to local variables
3996
3997 l_contract_number := p_tif_tbl(record_number).contract_number;
3998 l_quote_type := p_tif_tbl(record_number).quote_type_code;
3999 l_quote_reason := p_tif_tbl(record_number).quote_reason_code;
4000 l_date_effective_from := p_tif_tbl(record_number).date_effective_from;
4001 l_contract_record := record_number;
4002 --errorout_vg('p_tip_tbl(party_rec).contract_party_role '|| l_tip_tbl(party_rec).contract_party_role
4003 -- ||'for transaction_number' || l_tip_tbl(party_rec).transaction_number);
4004
4005 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4006 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4007 'p_tip_tbl(party_rec).contract_party_role ' || l_tip_tbl(party_rec).contract_party_role ||
4008 'for transaction_number' || l_tip_tbl(party_rec).transaction_number);
4009 END IF;
4010
4011 IF l_tip_tbl(party_rec).contract_party_role = 'LESSEE' THEN
4012 l_lessee_tip_tbl(l_lessee_count) := l_tip_tbl(party_rec);
4013 l_lessee_tif_tbl(l_lessee_count) := p_tif_tbl(record_number);
4014 IF(lp_lessee_assn_tbl.COUNT >0) THEN
4015 IF(p_tif_tbl(record_number).asset_id = lp_lessee_assn_tbl(l_quote_lines).p_asset_id) THEN
4016 lp_lessee_assn_tbl(l_quote_lines).p_quote_qty := lp_lessee_assn_tbl(l_quote_lines).p_quote_qty +p_tif_tbl(record_number).units_to_terminate;
4017 ELSE
4018 l_quote_lines := l_quote_lines+1;
4019 lp_lessee_assn_tbl(l_quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
4020 lp_lessee_assn_tbl(l_quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
4021 lp_lessee_assn_tbl(l_quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
4022 END IF;
4023 ELSE
4024 --assign values to assn_tbl
4025 lp_lessee_assn_tbl(l_quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
4026 lp_lessee_assn_tbl(l_quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
4027 lp_lessee_assn_tbl(l_quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
4028 END IF;
4029 l_lessee_count := l_lessee_count +1;
4030 ELSE
4031 l_vendor_tip_tbl(l_vendor_count) := l_tip_tbl(party_rec) ;
4032 l_vendor_tif_tbl(l_vendor_count) := p_tif_tbl(record_number);
4033 IF(lp_vendor_assn_tbl.COUNT >0) THEN
4034 IF(p_tif_tbl(record_number).asset_id = lp_vendor_assn_tbl(v_quote_lines).p_asset_id) THEN
4035 lp_vendor_assn_tbl(v_quote_lines).p_quote_qty := lp_vendor_assn_tbl(v_quote_lines).p_quote_qty +p_tif_tbl(record_number).units_to_terminate;
4036 ELSE
4037 v_quote_lines := v_quote_lines+1;
4038 lp_vendor_assn_tbl(v_quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
4039 lp_vendor_assn_tbl(v_quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
4040 lp_vendor_assn_tbl(v_quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
4041 END IF;
4042 ELSE
4043 --assign values to assn_tbl
4044 lp_vendor_assn_tbl(v_quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
4045 lp_vendor_assn_tbl(v_quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
4046 lp_vendor_assn_tbl(v_quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
4047 END IF;
4048 l_vendor_count := l_vendor_count +1 ;
4049 END IF;
4050
4051 -- Last record of the party table
4052 IF(party_rec = l_tip_tbl.LAST) THEN
4053 --errorout_vg('Inside last record');
4054 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4055 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4056 'Last Record of the party table');
4057 END IF;
4058
4059 -- populates the lp_quot_rec and calls create_quote for the last record
4060 lp_quot_rec.khr_id := p_tif_tbl(record_number).contract_id;
4061 lp_quot_rec.date_effective_from := p_tif_tbl(record_number).date_effective_from;
4062 lp_quot_rec.org_id := p_tif_tbl(record_number).org_id;
4063 lp_quot_rec.qtp_code := p_tif_tbl(record_number).quote_type_code;
4064 lp_quot_rec.qrs_code := p_tif_tbl(record_number).quote_reason_code;
4065
4066 OKL_AM_PARTIES_PVT.fetch_rule_quote_parties (
4067 p_api_version => p_api_version,
4068 p_init_msg_list => OKC_API.G_FALSE,
4069 x_msg_count => x_msg_count,
4070 x_msg_data => x_msg_data,
4071 x_return_status => x_return_status,
4072 p_qtev_rec => lp_quot_rec,
4073 x_qpyv_tbl => lp_qpyv_tbl,
4074 x_record_count => x_record_count,
4075 x_q_party_uv_tbl => x_q_party_uv_tbl);
4076
4077 --errorout_vg('Fetch rule quote parties status ='|| x_return_status);
4078 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4079 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4080 'Fetch rule Quote parties status = ' || x_return_status );
4081 END IF;
4082
4083 --errorout_vg('l_lessee_tip_tbl.count '|| l_lessee_tip_tbl.count);
4084 --errorout_vg('l_lessee_tif_tbl.count '|| l_lessee_tif_tbl.count);
4085
4086 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4087 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4088 'Lessee tip table count = ' || l_lessee_tip_tbl.count ||
4089 'tif tbl count '|| l_lessee_tif_tbl.count );
4090 END IF;
4091
4092 IF l_lessee_tip_tbl.count >0 THEN
4093 IF(lp_qpyv_tbl.COUNT >0) THEN
4094 FOR rule_party IN lp_qpyv_tbl.FIRST..lp_qpyv_tbl.LAST
4095 LOOP
4096 IF(lp_qpyv_tbl(rule_party).qpt_code = l_lessee_tip_tbl(0).quote_role_code) THEN
4097 lp_qpyv_tbl(rule_party).party_jtot_object1_code := l_lessee_tip_tbl(0).party_object_code;
4098 lp_qpyv_tbl(rule_party).party_object1_id1 := l_lessee_tip_tbl(0).party_object_id1;
4099 lp_qpyv_tbl(rule_party).party_object1_id2 := l_lessee_tip_tbl(0).party_object_id2;
4100 lp_qpyv_tbl(rule_party).cpl_id := l_lessee_tip_tbl(0).qpy_id;
4101 IF l_lessee_tip_tbl(0).email_address IS NOT NULL THEN
4102 lp_qpyv_tbl(rule_party).email_address := l_lessee_tip_tbl(0).email_address;
4103 END IF;
4104 END IF;
4105 END LOOP;
4106 END IF;
4107
4108 -- calls Create quote parties with p_validate_only = TRUE
4109 OKL_AM_PARTIES_PVT.create_quote_parties (
4110 p_qtev_rec => lp_quot_rec,
4111 p_qpyv_tbl => lp_qpyv_tbl,
4112 p_validate_only => TRUE,
4113 x_qpyv_tbl => lx_qpyv_tbl,
4114 x_return_status => x_return_status);
4115
4116 --errorout_vg('After create quote parties status = ' ||x_return_status );
4117 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4118 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4119 'Create Quote Parties status = ' || x_return_status );
4120 END IF;
4121
4122 GET_ERROR_MESSAGE(l_error_msg_rec);
4123 IF (l_error_msg_rec.COUNT > 0) THEN
4124 l_qte_msg_count := 0;
4125 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
4126 LOOP
4127 IF(LENGTH(l_error_msg_rec(m)) >0) THEN
4128 l_msg_tbl(l_qte_msg_count).msg_text := l_error_msg_rec(m);
4129 l_qte_msg_count := l_qte_msg_count+1;
4130 END IF;
4131 END LOOP;
4132 END IF;
4133
4134 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4135 lx_tif_tbl.DELETE;
4136 create_quote(
4137 p_api_version => p_api_version ,
4138 p_init_msg_list => OKC_API.G_FALSE,
4139 x_msg_count => x_msg_count ,
4140 x_msg_data => x_msg_data ,
4141 x_return_status => x_return_status ,
4142 p_tif_tbl => l_lessee_tif_tbl ,
4143 x_tif_tbl => lx_tif_tbl ,
4144 p_quot_rec => lp_quot_rec ,
4145 p_assn_tbl => lp_lessee_assn_tbl ,
4146 p_qpyv_tbl => lp_qpyv_tbl ,
4147 p_batch_offset => l_lessee_tif_tbl.FIRST ,
4148 p_record_number => l_lessee_tif_tbl.LAST);
4149
4150 --errorout_vg('Create Quote Status = '|| x_return_status);
4151 l_lessee_tif_tbl.DELETE;
4152 l_lessee_tif_tbl := lx_tif_tbl;
4153 lx_tif_tbl.DELETE;
4154
4155 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4156 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4157 'Create Quote status = ' || x_return_status );
4158 END IF;
4159
4160 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_lessee_tif_tbl(0).status = 'PROCESSED' ) THEN
4161 FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
4162 LOOP
4163 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4164 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4165 'Update Party to processed = ' || l_lessee_tip_tbl(i).transaction_number );
4166 END IF;
4167
4168 --errorout_vg('Update Party to processed = ' || l_lessee_tip_tbl(i).transaction_number);
4169 l_lessee_tip_tbl(i).status := 'PROCESSED';
4170 update_party(
4171 p_api_version => p_api_version ,
4172 p_init_msg_list => OKC_API.G_FALSE ,
4173 x_msg_data => x_msg_data ,
4174 x_msg_count => x_msg_count ,
4175 x_return_status => x_return_status ,
4176 p_tip_rec => l_lessee_tip_tbl(i));
4177
4178 END LOOP;
4179
4180 FOR i IN l_lessee_tif_tbl.FIRST..l_lessee_tif_tbl.LAST
4181 LOOP
4182 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4183 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4184 'Update interface record group number for ' || l_lessee_tip_tbl(i).transaction_number ||
4185 ' group number = ' ||group_number );
4186 END IF;
4187
4188 --errorout_vg('Update row group number = ' || l_lessee_tip_tbl(i).transaction_number || 'group number =' ||group_number );
4189 l_lessee_tif_tbl(i).group_number := group_number;
4190 update_row(
4191 p_api_version => p_api_version ,
4192 p_init_msg_list => OKC_API.G_FALSE ,
4193 x_msg_data => x_msg_data ,
4194 x_msg_count => x_msg_count ,
4195 x_return_status => x_return_status ,
4196 p_tif_rec => l_lessee_tif_tbl(i));
4197
4198 -- Save message from stack into transaction message table
4199 OKL_AM_UTIL_PVT.process_messages(
4200 p_trx_source_table => 'OKL_TERMNT_INTERFACE',
4201 p_trx_id => l_lessee_tif_tbl(i).transaction_number,
4202 x_return_status => x_return_status);
4203
4204 END LOOP;
4205 group_number := group_number+1;
4206 ELSE
4207 FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
4208 LOOP
4209 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4210 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4211 'Update Party to Error = ' || l_lessee_tip_tbl(i).transaction_number);
4212 END IF;
4213
4214 l_lessee_tip_tbl(i).status := 'ERROR';
4215 update_party(
4216 p_api_version => p_api_version ,
4217 p_init_msg_list => OKC_API.G_FALSE ,
4218 x_msg_data => x_msg_data ,
4219 x_msg_count => x_msg_count ,
4220 x_return_status => x_return_status ,
4221 p_tip_rec => l_lessee_tip_tbl(i));
4222 --errorout_vg('After updating the party to error ' || x_return_status );
4223 END LOOP;
4224 END IF;
4225
4226 FOR i IN l_lessee_tif_tbl.FIRST..l_lessee_tif_tbl.LAST
4227 LOOP
4228 x_out_tif_tbl(l_out_tif_rec) := l_lessee_tif_tbl(i);
4229 l_out_tif_rec := l_out_tif_rec+1;
4230 END LOOP;
4231 l_lessee_tif_tbl.delete;
4232
4233 ELSE
4234 FOR i IN l_lessee_tif_tbl.FIRST..l_lessee_tif_tbl.LAST
4235 LOOP
4236 l_lessee_tif_tbl(i).status := 'ERROR';
4237 l_msg_tbl(l_qte_msg_count).msg_text := 'quote not created for transaction_number '||l_lessee_tif_tbl(i).transaction_number;
4238
4239 log_messages(log_msg_flag => 'V',
4240 p_transaction_number => l_lessee_tif_tbl(i).transaction_number,
4241 p_contract_number => l_lessee_tif_tbl(i).contract_number,
4242 p_asset_number => l_lessee_tif_tbl(i).asset_number,
4243 p_date_effective => l_lessee_tif_tbl(i).date_effective_from,
4244 p_quote_type => l_lessee_tif_tbl(i).quote_type_code,
4245 p_quote_reason => l_lessee_tif_tbl(i).quote_reason_code,
4246 msg_tbl => l_msg_tbl );
4247 END LOOP;
4248
4249 FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
4250 LOOP
4251 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4252 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4253 'Update Party to Error = ' || l_lessee_tip_tbl(i).transaction_number );
4254 END IF;
4255
4256 l_lessee_tip_tbl(i).status := 'ERROR';
4257 update_party(
4258 p_api_version => p_api_version ,
4259 p_init_msg_list => OKC_API.G_FALSE ,
4260 x_msg_data => x_msg_data ,
4261 x_msg_count => x_msg_count ,
4262 x_return_status => x_return_status ,
4263 p_tip_rec => l_lessee_tip_tbl(i));
4264
4265 --errorout_vg('After updating the party to error ' || x_return_status );
4266 END LOOP;
4267 END IF;
4268 END IF; -- l_lesseee_tip_tbl
4269
4270 --errorout_vg('Vendor table count = ' ||l_vendor_tip_tbl.count );
4271 --errorout_vg('interface table count = '||l_vendor_tif_tbl.count );
4272
4273 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4274 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4275 'Vendor tip table count = ' || l_vendor_tip_tbl.count ||
4276 'tif_tbl count = ' || l_vendor_tif_tbl.count );
4277 END IF;
4278
4279 IF l_vendor_tip_tbl.count >0 THEN
4280 IF(lp_qpyv_tbl.COUNT >0) THEN
4281 FOR rule_party IN lp_qpyv_tbl.FIRST..lp_qpyv_tbl.LAST
4282 LOOP
4283 IF(lp_qpyv_tbl(rule_party).qpt_code = l_vendor_tip_tbl(0).quote_role_code) THEN
4284 lp_qpyv_tbl(rule_party).party_jtot_object1_code := l_vendor_tip_tbl(0).party_object_code;
4285 lp_qpyv_tbl(rule_party).party_object1_id1 := l_vendor_tip_tbl(0).party_object_id1;
4286 lp_qpyv_tbl(rule_party).party_object1_id2 := l_vendor_tip_tbl(0).party_object_id2;
4287 lp_qpyv_tbl(rule_party).cpl_id := l_vendor_tip_tbl(0).qpy_id;
4288 IF l_vendor_tip_tbl(0).email_address IS NOT NULL THEN
4289 l_vendor_tip_tbl(0).email_address := l_vendor_tip_tbl(0).email_address;
4290 END IF;
4291 END IF;
4292 END LOOP;
4293 END IF;
4294
4295 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4296 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4297 'Before Create quote parties ' || lp_qpyv_tbl.count );
4298 END IF;
4299
4300 -- calls Create quote parties with p_validate_only = TRUE
4301 OKL_AM_PARTIES_PVT.create_quote_parties (
4302 p_qtev_rec => lp_quot_rec,
4303 p_qpyv_tbl => lp_qpyv_tbl,
4304 p_validate_only => TRUE,
4305 x_qpyv_tbl => lx_qpyv_tbl,
4306 x_return_status => x_return_status);
4307
4308 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4309 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4310 'Create Quote Parties status = ' || x_return_status );
4311 END IF;
4312
4313 --errorout_vg('Create quote party status '|| x_return_status);
4314 GET_ERROR_MESSAGE(l_error_msg_rec);
4315 IF (l_error_msg_rec.COUNT > 0) THEN
4316 l_qte_msg_count := 0;
4317 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
4318 LOOP
4319 IF(LENGTH(l_error_msg_rec(m)) >0) THEN
4320 l_msg_tbl(l_qte_msg_count).msg_text := l_error_msg_rec(m);
4321 l_qte_msg_count := l_qte_msg_count+1;
4322 END IF;
4323 END LOOP;
4324 END IF;
4325
4326 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4327 lx_tif_tbl.DELETE;
4328 create_quote(
4329 p_api_version => p_api_version ,
4330 p_init_msg_list => OKC_API.G_FALSE,
4331 x_msg_count => x_msg_count ,
4332 x_msg_data => x_msg_data ,
4333 x_return_status => x_return_status ,
4334 p_tif_tbl => l_vendor_tif_tbl ,
4335 x_tif_tbl => lx_tif_tbl ,
4336 p_quot_rec => lp_quot_rec ,
4337 p_assn_tbl => lp_vendor_assn_tbl ,
4338 p_qpyv_tbl => lp_qpyv_tbl ,
4339 p_batch_offset => l_vendor_tif_tbl.FIRST ,
4340 p_record_number => l_vendor_tif_tbl.LAST);
4341
4342 l_vendor_tif_tbl.DELETE;
4343 l_vendor_tif_tbl := lx_tif_tbl;
4344 lx_tif_tbl.DELETE;
4345 --errorout_vg('create quote status ' || x_return_status );
4346
4347 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4348 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4349 'Create Quote status = ' || x_return_status );
4350 END IF;
4351
4352 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_vendor_tif_tbl(0).status = 'PROCESSED' ) THEN
4353 FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
4354 LOOP
4355 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4356 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4357 'Update Party to processed = ' || l_vendor_tip_tbl(i).transaction_number );
4358 END IF;
4359
4360 l_vendor_tip_tbl(i).status := 'PROCESSED';
4361 update_party(
4362 p_api_version => p_api_version ,
4363 p_init_msg_list => OKC_API.G_FALSE ,
4364 x_msg_data => x_msg_data ,
4365 x_msg_count => x_msg_count ,
4366 x_return_status => x_return_status ,
4367 p_tip_rec => l_vendor_tip_tbl(i));
4368
4369 --errorout_vg('After party update to processed' || x_return_status );
4370 END LOOP;
4371
4372 FOR i IN l_vendor_tif_tbl.FIRST..l_vendor_tif_tbl.LAST
4373 LOOP
4374 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4375 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4376 'Update interface record group number = ' || l_vendor_tip_tbl(i).transaction_number ||
4377 'group number = ' ||group_number );
4378 END IF;
4379 --errorout_vg('update the group number for'|| l_vendor_tif_tbl(i).transaction_number
4380 -- || ' to group_number = ' || group_number );
4381
4382 l_vendor_tif_tbl(i).group_number := group_number;
4383 update_row(
4384 p_api_version => p_api_version ,
4385 p_init_msg_list => OKC_API.G_FALSE ,
4386 x_msg_data => x_msg_data ,
4387 x_msg_count => x_msg_count ,
4388 x_return_status => x_return_status ,
4389 p_tif_rec => l_vendor_tif_tbl(i));
4390
4391 -- Save message from stack into transaction message table
4392 OKL_AM_UTIL_PVT.process_messages(
4393 p_trx_source_table => 'OKL_TERMNT_INTERFACE',
4394 p_trx_id => l_vendor_tif_tbl(i).transaction_number,
4395 x_return_status => x_return_status);
4396
4397 END LOOP;
4398 group_number := group_number+1;
4399 ELSE
4400 FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
4401 LOOP
4402 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4403 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4404 'Update Party to Error = ' || l_vendor_tip_tbl(i).transaction_number );
4405 END IF;
4406 --errorout_vg('Vendor Transaction Number : error= '|| l_vendor_tip_tbl(i).transaction_number);
4407
4408 l_vendor_tip_tbl(i).status := 'ERROR';
4409 update_party(
4410 p_api_version => p_api_version ,
4411 p_init_msg_list => OKC_API.G_FALSE ,
4412 x_msg_data => x_msg_data ,
4413 x_msg_count => x_msg_count ,
4414 x_return_status => x_return_status ,
4415 p_tip_rec => l_vendor_tip_tbl(i));
4416
4417 END LOOP;
4418 END IF;
4419 FOR i IN l_vendor_tif_tbl.FIRST..l_vendor_tif_tbl.LAST
4420 LOOP
4421 x_out_tif_tbl(l_out_tif_rec) := l_vendor_tif_tbl(i);
4422 l_out_tif_rec := l_out_tif_rec+1;
4423 END LOOP;
4424 l_vendor_tif_tbl.delete;
4425 ELSE
4426 FOR i IN l_vendor_tif_tbl.FIRST..l_vendor_tif_tbl.LAST
4427 LOOP
4428 l_vendor_tif_tbl(i).status := 'ERROR';
4429 l_msg_tbl(l_qte_msg_count).msg_text := 'quote not created for transaction_number '||l_vendor_tif_tbl(i).transaction_number;
4430
4431 log_messages(
4432 log_msg_flag => 'V',
4433 p_transaction_number => l_vendor_tif_tbl(i).transaction_number,
4434 p_contract_number => l_vendor_tif_tbl(i).contract_number,
4435 p_asset_number => l_vendor_tif_tbl(i).asset_number,
4436 p_date_effective => l_vendor_tif_tbl(i).date_effective_from,
4437 p_quote_type => l_vendor_tif_tbl(i).quote_type_code,
4438 p_quote_reason => l_vendor_tif_tbl(i).quote_reason_code,
4439 msg_tbl => l_msg_tbl );
4440 END LOOP;
4441
4442 FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
4443 LOOP
4444 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4445 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4446 'Update Party to Error = ' || l_vendor_tip_tbl(i).transaction_number );
4447 END IF;
4448
4449 l_vendor_tip_tbl(i).status := 'ERROR';
4450 update_party(
4451 p_api_version => p_api_version ,
4452 p_init_msg_list => OKC_API.G_FALSE ,
4453 x_msg_data => x_msg_data ,
4454 x_msg_count => x_msg_count ,
4455 x_return_status => x_return_status ,
4456 p_tip_rec => l_vendor_tip_tbl(i));
4457 --errorout_vg('After updating the party to error ' || x_return_status );
4458
4459 END LOOP;
4460 END IF;
4461 END IF; -- l_vendor_tip_tbl
4462 --errorout_vg('Party table processing ends here');
4463 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4464 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4465 'Party Table processing ends here ' );
4466 END IF;
4467 EXIT;
4468 END IF; -- End of last record
4469 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4470 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4471 'processing Ends for transaction number = ' || l_tip_tbl(party_rec).transaction_number );
4472 END IF;
4473 END IF; -- if transaction numbers are same
4474 END LOOP; --l_tip_tbl.FIRST..p_tip_tbl.LAST
4475 END IF; -- p_tip_tbl.COUNT>0 END IF; -- To check if table contains any record.
4476 END LOOP; -- p_tif_tbl.first..last
4477 END IF; -- p_tif_tbl.count>0
4478 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4479 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote','End(+)');
4480 END IF;
4481 --errorout_vg('x_out_tif_tbl.coun t' || x_out_tif_tbl.count );
4482 --errorout_vg('Group NUmber = '|| group_number );
4483 l_tip_tbl.DELETE;
4484 x_tif_tbl.DELETE;
4485 IF x_out_tif_tbl.count > 0 THEN
4486 x_tif_tbl := x_out_tif_tbl;
4487 ELSE
4488 x_tif_tbl := p_tif_tbl;
4489 END IF;
4490 x_out_tif_tbl.DELETE;
4491 x_group_number := group_number ;
4492
4493 EXCEPTION
4494 WHEN OTHERS THEN
4495 -- store SQL error message on message stack for caller
4496 OKC_API.set_message(
4497 p_app_name => g_app_name,
4498 p_msg_name => g_unexpected_error,
4499 p_token1 => g_sqlcode_token,
4500 p_token1_value => SQLCODE,
4501 p_token2 => g_sqlerrm_token,
4502 p_token2_value => sqlerrm);
4503
4504 l_msg_tbl(0).msg_text := 'populate_party_for_quote:ended with unexpected error sqlcode: '||SQLCODE||' sqlerrm: '||sqlerrm;
4505 log_messages(log_msg_flag => 'V', msg_tbl => l_msg_tbl );
4506 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4507 END populate_party_for_quote;
4508 /*PARTY_LOGIC END*/
4509
4510
4511 -- Start of comments
4512 --
4513 -- Procedure Name : termination_interface
4514 -- Description : This is the only public procedure and calls all the
4515 -- : required procedures to generate a quote
4516 -- Business Rules :
4517 -- Parameters : Input parameters : p_api_version, p_init_msg_list
4518 -- Version : 1.0
4519 -- History : 04-FEB-03 RABHUPAT Created
4520 -- : 14-MAR-03 RABHUPAT added calls for party logic procedures
4521 -- : 02-aug-2011 sechawla 12745559 : added debug messages in this procedure
4522 -- End of comments
4523
4524 PROCEDURE termination_interface(p_api_version IN NUMBER,
4525 p_init_msg_list IN VARCHAR2,
4526 x_msg_count OUT NOCOPY NUMBER,
4527 x_msg_data OUT NOCOPY VARCHAR2,
4528 x_return_status OUT NOCOPY VARCHAR2,
4529 err_buf OUT NOCOPY VARCHAR2,
4530 ret_code OUT NOCOPY NUMBER)
4531 IS
4532
4533 -- this cursor selects the rows with status as 'ENTERED' and orders by group_number
4534 CURSOR get_termnt_intface_dtls_csr(p_status IN VARCHAR2) IS
4535 SELECT
4536 ROWID
4537 ,TRANSACTION_NUMBER
4538 ,BATCH_NUMBER
4539 ,CONTRACT_ID
4540 ,CONTRACT_NUMBER
4541 ,ASSET_ID
4542 ,ASSET_NUMBER
4543 ,ASSET_DESCRIPTION
4544 ,SERIAL_NUMBER
4545 ,ORIG_SYSTEM
4546 ,ORIG_SYSTEM_REFERENCE
4547 ,UNITS_TO_TERMINATE
4548 ,COMMENTS
4549 ,DATE_PROCESSED
4550 ,DATE_EFFECTIVE_FROM
4551 ,TERMINATION_NOTIFICATION_EMAIL
4552 ,TERMINATION_NOTIFICATION_YN
4553 ,AUTO_ACCEPT_YN
4554 ,QUOTE_TYPE_CODE
4555 ,QUOTE_REASON_CODE
4556 ,QTE_ID
4557 ,STATUS
4558 ,ORG_ID
4559 ,REQUEST_ID
4560 ,PROGRAM_APPLICATION_ID
4561 ,PROGRAM_ID
4562 ,PROGRAM_UPDATE_DATE
4563 ,ATTRIBUTE_CATEGORY
4564 ,ATTRIBUTE1
4565 ,ATTRIBUTE2
4566 ,ATTRIBUTE3
4567 ,ATTRIBUTE4
4568 ,ATTRIBUTE5
4569 ,ATTRIBUTE6
4570 ,ATTRIBUTE7
4571 ,ATTRIBUTE8
4572 ,ATTRIBUTE9
4573 ,ATTRIBUTE10
4574 ,ATTRIBUTE11
4575 ,ATTRIBUTE12
4576 ,ATTRIBUTE13
4577 ,ATTRIBUTE14
4578 ,ATTRIBUTE15
4579 ,CREATED_BY
4580 ,CREATION_DATE
4581 ,LAST_UPDATED_BY
4582 ,LAST_UPDATE_DATE
4583 ,LAST_UPDATE_LOGIN
4584 ,GROUP_NUMBER
4585 FROM OKL_TERMNT_INTERFACE
4586 WHERE status = p_status
4587 ORDER BY CONTRACT_NUMBER,TRANSACTION_NUMBER; --sechawla 02-Aug-2011 12745559 : added transaction_number
4588
4589 l_api_version CONSTANT NUMBER := 1;
4590 l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
4591 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4592 record_number NUMBER;
4593 x_installbase_id NUMBER;
4594 l_sys_date DATE;
4595 l_tif_tbl tif_tbl_type;
4596 lx_tif_tbl tif_tbl_type;
4597 l_tip_tbl tip_tbl_type;
4598 lx_tip_tbl tip_tbl_type;
4599 l_group_number NUMBER := 0;
4600 tot_rec_processed NUMBER := 0;
4601 l_msg_tbl msg_tbl_type;
4602 BEGIN
4603
4604 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4605 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface','Begin(+)');
4606 END IF;
4607
4608 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4609 --Check API version, initialize message list and create savepoint.
4610 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4611 G_PKG_NAME,
4612 p_init_msg_list,
4613 l_api_version,
4614 p_api_version,
4615 '_PVT',
4616 x_return_status);
4617 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4618 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4619 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4620 RAISE OKC_API.G_EXCEPTION_ERROR;
4621 END IF;
4622 -- this retrives the sysdate and passes to a local variable
4623 SELECT trunc(SYSDATE) INTO l_sys_date FROM DUAL;
4624 record_number:=0;
4625
4626 -- loops through the records to populate the plsql table
4627 FOR termnt_rec IN get_termnt_intface_dtls_csr(p_status=>'ENTERED')
4628 LOOP
4629
4630 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4631 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4632 'record_number :'||record_number||' Transaction Number : '||termnt_rec.transaction_number);
4633 end if;
4634
4635 l_tif_tbl(record_number).row_id := termnt_rec.rowid;
4636 l_tif_tbl(record_number).transaction_number := termnt_rec.transaction_number;
4637 l_tif_tbl(record_number).batch_number := termnt_rec.batch_number;
4638 l_tif_tbl(record_number).contract_id := termnt_rec.contract_id;
4639 l_tif_tbl(record_number).contract_number := termnt_rec.contract_number;
4640 l_tif_tbl(record_number).asset_id := termnt_rec.asset_id;
4641 l_tif_tbl(record_number).asset_number := termnt_rec.asset_number;
4642 l_tif_tbl(record_number).asset_description := termnt_rec.asset_description;
4643 l_tif_tbl(record_number).serial_number := termnt_rec.serial_number;
4644 l_tif_tbl(record_number).orig_system := termnt_rec.orig_system;
4645 l_tif_tbl(record_number).orig_system_reference := termnt_rec.orig_system_reference;
4646 l_tif_tbl(record_number).units_to_terminate := termnt_rec.units_to_terminate;
4647 l_tif_tbl(record_number).comments := termnt_rec.comments;
4648 l_tif_tbl(record_number).date_processed := termnt_rec.date_processed;
4649 l_tif_tbl(record_number).date_effective_from := termnt_rec.date_effective_from;
4650 l_tif_tbl(record_number).termination_notification_email := termnt_rec.termination_notification_email;
4651 l_tif_tbl(record_number).termination_notification_yn := termnt_rec.termination_notification_yn;
4652 l_tif_tbl(record_number).auto_accept_yn := termnt_rec.auto_accept_yn;
4653 l_tif_tbl(record_number).quote_type_code := termnt_rec.quote_type_code;
4654 l_tif_tbl(record_number).quote_reason_code := termnt_rec.quote_reason_code;
4655 l_tif_tbl(record_number).qte_id := termnt_rec.qte_id;
4656 l_tif_tbl(record_number).status := termnt_rec.status;
4657 l_tif_tbl(record_number).org_id := termnt_rec.org_id;
4658 l_tif_tbl(record_number).request_id := termnt_rec.request_id;
4659 l_tif_tbl(record_number).program_application_id := termnt_rec.program_application_id;
4660 l_tif_tbl(record_number).program_id := termnt_rec.program_id;
4661 l_tif_tbl(record_number).program_update_date := termnt_rec.program_update_date;
4662 l_tif_tbl(record_number).attribute_category := termnt_rec.attribute_category;
4663 l_tif_tbl(record_number).attribute1 := termnt_rec.attribute1;
4664 l_tif_tbl(record_number).attribute2 := termnt_rec.attribute2;
4665 l_tif_tbl(record_number).attribute3 := termnt_rec.attribute3;
4666 l_tif_tbl(record_number).attribute4 := termnt_rec.attribute4;
4667 l_tif_tbl(record_number).attribute5 := termnt_rec.attribute5;
4668 l_tif_tbl(record_number).attribute6 := termnt_rec.attribute6;
4669 l_tif_tbl(record_number).attribute7 := termnt_rec.attribute7;
4670 l_tif_tbl(record_number).attribute8 := termnt_rec.attribute8;
4671 l_tif_tbl(record_number).attribute9 := termnt_rec.attribute9;
4672 l_tif_tbl(record_number).attribute10 := termnt_rec.attribute10;
4673 l_tif_tbl(record_number).attribute11 := termnt_rec.attribute11;
4674 l_tif_tbl(record_number).attribute12 := termnt_rec.attribute12;
4675 l_tif_tbl(record_number).attribute13 := termnt_rec.attribute13;
4676 l_tif_tbl(record_number).attribute14 := termnt_rec.attribute14;
4677 l_tif_tbl(record_number).attribute15 := termnt_rec.attribute15;
4678 l_tif_tbl(record_number).created_by := termnt_rec.created_by;
4679 l_tif_tbl(record_number).creation_date := termnt_rec.creation_date;
4680 l_tif_tbl(record_number).last_updated_by := termnt_rec.last_updated_by;
4681 l_tif_tbl(record_number).last_update_date := termnt_rec.last_update_date;
4682 l_tif_tbl(record_number).last_update_login := termnt_rec.last_update_login;
4683 l_tif_tbl(record_number).group_number := termnt_rec.group_number;
4684
4685 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4686 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4687 'l_tif_tbl(record_number).group_number :'||l_tif_tbl(record_number).group_number);
4688 end if;
4689
4690 record_number := record_number+1;
4691 END LOOP;
4692
4693 tot_rec_processed := l_tif_tbl.COUNT;
4694
4695 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4696 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4697 'tot_rec_processed :'||tot_rec_processed);
4698 end if;
4699
4700 -- validates quote_type and quote_reason and populates WHO columns
4701 validate_quote_type_and_reason(p_api_version => l_api_version,
4702 p_init_msg_list => OKC_API.G_TRUE,
4703 x_msg_count => x_msg_count,
4704 x_msg_data => x_msg_data,
4705 x_return_status => x_return_status,
4706 p_tif_tbl => l_tif_tbl,
4707 x_tif_tbl => lx_tif_tbl);
4708 l_tif_tbl.DELETE;
4709 l_tif_tbl := lx_tif_tbl;
4710 lx_tif_tbl.DELETE;
4711
4712 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4713 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4714 'after validate_quote_type_and_reason : '||x_return_status);
4715 -- FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4716 ---'after validate_quote_type_and_reason : trx number (0) : '||l_tif_tbl(0).transaction_number);
4717 -- FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4718 --'after validate_quote_type_and_reason : trx number (1) : '||l_tif_tbl(1).transaction_number);
4719 end if;
4720
4721 -- validates contract and asset details entered, populates the remaining calls required
4722 validate_transaction(p_api_version => l_api_version,
4723 p_init_msg_list => OKC_API.G_FALSE,
4724 x_msg_count => x_msg_count,
4725 x_msg_data => x_msg_data,
4726 x_return_status => x_return_status,
4727 p_tif_tbl => l_tif_tbl,
4728 p_sys_date => l_sys_date,
4729 x_tif_tbl => lx_tif_tbl);
4730
4731 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4732 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4733 'after validate_transaction : '||x_return_status);
4734
4735 end if;
4736
4737 l_tif_tbl.DELETE;
4738 l_tif_tbl := lx_tif_tbl;
4739 lx_tif_tbl.DELETE;
4740
4741 /*
4742 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4743 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4744 'after validate_transaction : trx number (0) : '||l_tif_tbl(0).transaction_number);
4745 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4746 'after validate_transaction : trx number (1) : '||l_tif_tbl(1).transaction_number);
4747 END IF;
4748 */
4749 -- updates the values in database and gets records with status='WORKING'
4750 change_status(p_api_version => l_api_version,
4751 p_init_msg_list => OKC_API.G_FALSE,
4752 x_msg_count => x_msg_count,
4753 x_msg_data => x_msg_data,
4754 x_return_status => x_return_status,
4755 p_tif_tbl => l_tif_tbl,
4756 x_tif_tbl => lx_tif_tbl);
4757
4758 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4759 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4760 'after change_status : '||x_return_status);
4761 end if;
4762
4763 l_tif_tbl.DELETE;
4764 l_tif_tbl := lx_tif_tbl;
4765 lx_tif_tbl.DELETE;
4766
4767 /*
4768 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4769 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4770 'after change_status : trx number (0) : '||l_tif_tbl(0).transaction_number);
4771 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4772 'after change_status : trx number (1) : '||l_tif_tbl(1).transaction_number);
4773 END IF;
4774 */
4775 -- selects the party information and populates WHO columns in party table
4776
4777 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4778 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4779 'Before select_party_info : '||l_tip_tbl.count || 'l_tif_tbl count'|| l_tif_tbl.count);
4780 END IF;
4781
4782 select_party_info(p_api_version => l_api_version,
4783 p_init_msg_list => OKC_API.G_FALSE,
4784 x_msg_count => x_msg_count,
4785 x_msg_data => x_msg_data,
4786 x_return_status => x_return_status,
4787 p_tip_tbl => l_tip_tbl,
4788 x_tip_tbl => lx_tip_tbl,
4789 p_pty_status => 'ENTERED');
4790
4791 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4792 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4793 'after select_party_info : '||x_return_status);
4794 /*
4795 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4796 'after select_party_info : party 0 '||lx_tip_tbl(0).transaction_number);
4797 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4798 'after select_party_info : party 1 '||lx_tip_tbl(1).transaction_number);
4799 */
4800 end if;
4801
4802 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4803 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4804 'After select_party_info : '||lx_tip_tbl.count);
4805 END IF;
4806
4807 l_tip_tbl.DELETE;
4808 l_tip_tbl := lx_tip_tbl;
4809 lx_tip_tbl.DELETE;
4810 -- validates party information
4811 validate_party(p_api_version => l_api_version,
4812 p_init_msg_list => OKC_API.G_FALSE,
4813 x_msg_count => x_msg_count,
4814 x_msg_data => x_msg_data,
4815 x_return_status => x_return_status,
4816 p_tif_tbl => l_tif_tbl,
4817 x_tif_tbl => lx_tif_tbl,
4818 p_tip_tbl => l_tip_tbl,
4819 x_tip_tbl => lx_tip_tbl);
4820
4821 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4822 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4823 'after validate_party : '||x_return_status);
4824 /*
4825 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4826 'after validate_party : party 0 '||lx_tip_tbl(0).transaction_number);
4827 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4828 'after validate_party : party 1 '||lx_tip_tbl(1).transaction_number);
4829 */
4830 end if;
4831
4832 l_tip_tbl.DELETE;
4833 l_tip_tbl := lx_tip_tbl;
4834 lx_tip_tbl.DELETE;
4835
4836 l_tif_tbl.DELETE;
4837 l_tif_tbl := lx_tif_tbl;
4838 lx_tif_tbl.DELETE;
4839 -- updates values in database and gets records with status='WORKING'
4840 select_party_info(p_api_version => l_api_version,
4841 p_init_msg_list => OKC_API.G_FALSE,
4842 x_msg_count => x_msg_count,
4843 x_msg_data => x_msg_data,
4844 x_return_status => x_return_status,
4845 p_tip_tbl => l_tip_tbl,
4846 x_tip_tbl => lx_tip_tbl,
4847 p_pty_status => 'WORKING');
4848
4849 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4850 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4851 'after select_party_info : '||x_return_status);
4852 /*
4853 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4854 'after select_party_info : party 0 '||lx_tip_tbl(0).transaction_number);
4855 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4856 'after select_party_info : party 1 '||lx_tip_tbl(1).transaction_number);
4857 */
4858 end if;
4859
4860 l_tip_tbl.DELETE;
4861 l_tip_tbl := lx_tip_tbl;
4862 lx_tip_tbl.DELETE;
4863 -- populates the fields required for quote and creates quote for entries in party table
4864 populate_party_for_quote(p_api_version => l_api_version,
4865 p_init_msg_list => OKC_API.G_FALSE,
4866 x_msg_count => x_msg_count,
4867 x_msg_data => x_msg_data,
4868 x_return_status => x_return_status,
4869 p_tip_tbl => l_tip_tbl,
4870 p_tif_tbl => l_tif_tbl,
4871 x_tif_tbl => lx_tif_tbl,
4872 x_group_number => l_group_number);
4873
4874 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4875 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4876 'after populate_party_for_quote : '||x_return_status);
4877 /*
4878 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4879 'after populate_party_for_quote : party 0 '||l_tip_tbl(0).transaction_number);
4880 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4881 'after populate_party_for_quote : party 1 '||l_tip_tbl(1).transaction_number);
4882
4883 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4884 'after populate_party_for_quote : trx 0 '||lx_tif_tbl(0).transaction_number);
4885 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4886 'after populate_party_for_quote : trx 1 '||lx_tif_tbl(1).transaction_number);
4887 */
4888 end if;
4889
4890 l_tif_tbl.DELETE;
4891 l_tif_tbl := lx_tif_tbl;
4892 lx_tif_tbl.DELETE;
4893 -- updates the values to database and removes the duplicates while grouping
4894 remove_duplicates(p_api_version => l_api_version,
4895 p_init_msg_list => OKC_API.G_FALSE,
4896 x_msg_count => x_msg_count,
4897 x_msg_data => x_msg_data,
4898 x_return_status => x_return_status,
4899 p_tif_tbl => l_tif_tbl,
4900 x_tif_tbl => lx_tif_tbl);
4901
4902 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4903 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4904 'after remove_duplicates : '||x_return_status);
4905 end if;
4906
4907 l_tif_tbl.DELETE;
4908 l_tif_tbl := lx_tif_tbl;
4909 lx_tif_tbl.DELETE;
4910 -- creates groups and calls create_quote
4911 populate_quote(p_api_version => l_api_version,
4912 p_init_msg_list => OKC_API.G_FALSE,
4913 x_msg_count => x_msg_count,
4914 x_msg_data => x_msg_data,
4915 x_return_status => x_return_status,
4916 p_tif_tbl => l_tif_tbl,
4917 x_tif_tbl => lx_tif_tbl,
4918 p_group_number => l_group_number);
4919
4920 IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4921 FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface.',
4922 'after populate_quote : '||x_return_status);
4923 end if;
4924
4925 IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4926 FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface ','End(-)');
4927 END IF;
4928
4929 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4930 log_messages(log_msg_flag => 'Y',
4931 p_quote_number => tot_rec_processed,
4932 msg_tbl => l_msg_tbl );
4933 log_messages(log_msg_flag => 'O',
4934 p_quote_number => tot_rec_processed,
4935 msg_tbl => l_msg_tbl );
4936 EXCEPTION
4937 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4938 x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4939 G_PKG_NAME,
4940 'OKC_API.G_RET_STS_ERROR',
4941 x_msg_count,
4942 x_msg_data,
4943 '_PVT');
4944 -- unexpected error
4945 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4946 x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4947 G_PKG_NAME,
4948 'OKC_API.G_RET_STS_UNEXP_ERROR',
4949 x_msg_count,
4950 x_msg_data,
4951 '_PVT');
4952 WHEN OTHERS THEN
4953 -- store SQL error message on message stack for caller
4954 OKC_API.set_message(p_app_name => g_app_name,
4955 p_msg_name => g_unexpected_error,
4956 p_token1 => g_sqlcode_token,
4957 p_token1_value => sqlcode,
4958 p_token2 => g_sqlerrm_token,
4959 p_token2_value => sqlerrm);
4960 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4961 l_msg_tbl(0).msg_text := 'termination_interface:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
4962 log_messages(log_msg_flag => 'Y',
4963 p_quote_number => tot_rec_processed,
4964 msg_tbl => l_msg_tbl );
4965 log_messages(log_msg_flag => 'O',
4966 p_quote_number => tot_rec_processed,
4967 msg_tbl => l_msg_tbl );
4968
4969 END termination_interface;
4970 END OKL_AM_TERMNT_INTERFACE_PVT;