[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.9 2006/09/29 12:12:07 ssdeshpa noship $ */
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 /**************************************************************************/
49 -- Start of comments
50 --
51 -- Procedure Name : get_error_message
52 -- Description : This procedure unwinds the error messages from stack
53 -- Business Rules :
54 -- Parameters : p_all_message
55 -- Version : 1.0
56 -- History : 23-Feb-2005 PAGARG Created
57 -- End of comments
58 PROCEDURE get_error_message(p_all_message OUT NOCOPY error_message_type)
59 IS
60 l_msg_index_out NUMBER;
61 l_data VARCHAR2(2000);
62 l_counter NUMBER := 0;
63 BEGIN
64 FOR l_counter IN 1..fnd_msg_pub.count_msg
65 LOOP
66 fnd_msg_pub.get
67 (p_data => l_data,
68 p_msg_index_out => l_msg_index_out,
69 p_encoded => FND_API.G_FALSE,
70 p_msg_index => l_counter);
71 p_all_message(l_counter) := l_data;
72 END LOOP;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 NULL;
77 END get_error_message;
78 /**************************************************************************/
79
80 -- Start of comments
81 --
82 -- Procedure Name : log_messages
83 -- Description : This procedure logs the messages to concuurent log, output
84 -- Business Rules :
85 -- Parameters : log_msg_flag, p_tif_rec, msg_text
86 -- Version : 1.0
87 -- History : 18-FEB-03 SPILLAIP Created
88 -- : 27-MAR-03 RABHUPAT modified
89 -- : 22-JUL-03 RABHUPAT modified
90 -- End of comments
91
92 -- Y for putting in log and output,V for not processed, E for processed with Error,P for processed,O for output
93
94 PROCEDURE log_messages(log_msg_flag IN VARCHAR2 DEFAULT '',
95 p_transaction_number IN VARCHAR2 DEFAULT '',
96 p_contract_number IN VARCHAR2 DEFAULT '',
97 p_asset_number IN VARCHAR2 DEFAULT '',
98 p_date_effective IN DATE DEFAULT OKC_API.G_MISS_DATE,
99 p_quote_type IN VARCHAR2 DEFAULT '',
100 p_quote_reason IN VARCHAR2 DEFAULT '',
101 p_quote_number IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
102 msg_tbl IN msg_tbl_type
103 ) IS
104 val_count NUMBER := 0;
105 err_count NUMBER := 0;
106 pro_count NUMBER := 0;
107
108 val_msg_count NUMBER := 0;
109 err_msg_count NUMBER := 0;
110 pro_msg_count NUMBER := 0;
111
112 tot_count NUMBER := 0;
113 count_msg NUMBER := 1;
114 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
115 l_org_name VARCHAR2(40);
116 l_quote_type VARCHAR2(100);
117 l_quote_reason VARCHAR2(100);
118
119
120 -- cursor to retrieve operating_units
121 CURSOR org_csr (l_org_id IN NUMBER) IS
122 SELECT name
123 FROM hr_operating_units
124 WHERE organization_id = l_org_id;
125
126 /* gets the meaning for each quote_type_code from FND_LOOKUPS*/
127 CURSOR get_quote_type_meaning_csr(p_quote_type IN VARCHAR2) IS
128 SELECT meaning
129 FROM FND_LOOKUPS
130 WHERE lookup_code = p_quote_type AND lookup_type = 'OKL_QUOTE_TYPE' AND enabled_flag = 'Y';
131
132 /* gets the meaning for each quote_reason from FND_LOOKUPS*/
133 CURSOR get_quote_reason_meaning_csr(p_quote_reason IN VARCHAR2) IS
134 SELECT meaning
135 FROM FND_LOOKUPS
136 WHERE lookup_code = p_quote_reason AND lookup_type = 'OKL_QUOTE_REASON' AND enabled_flag = 'Y';
137
138 BEGIN
139 -- transactions which are not processed due to errors will be inserted in to val_msg_tbl
140 IF(log_msg_flag = 'V') THEN
141 val_count := val_log_tbl.COUNT + 1;
142 val_log_tbl(val_count).transaction_number := p_transaction_number;
143 val_log_tbl(val_count).contract_number := p_contract_number;
144 val_log_tbl(val_count).asset_number := p_asset_number;
145 val_log_tbl(val_count).date_effective_from := p_date_effective;
146 val_log_tbl(val_count).quote_type := p_quote_type;
147 val_log_tbl(val_count).quote_reason := p_quote_reason;
148
149 IF(msg_tbl.COUNT > 0) THEN
150 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
151 LOOP
152 val_msg_count := val_msg_tbl.COUNT + 1;
153 val_msg_tbl(val_msg_count).transaction_number := p_transaction_number;
154 val_msg_tbl(val_msg_count).msg_text := msg_tbl(i).msg_text;
155 END LOOP;
156 END IF;
157 -- transactions which are processed with errors will be inserted in to err_msg_tbl
158 ELSIF(log_msg_flag = 'E') THEN
159 err_count := err_log_tbl.COUNT + 1;
160 err_log_tbl(err_count).transaction_number := p_transaction_number;
161 err_log_tbl(err_count).contract_number := p_contract_number;
162 err_log_tbl(err_count).asset_number := p_asset_number;
163 err_log_tbl(err_count).date_effective_from := p_date_effective;
164 err_log_tbl(err_count).quote_type := p_quote_type;
165 err_log_tbl(err_count).quote_reason := p_quote_reason;
166 err_log_tbl(err_count).quote_number := p_quote_number;
167
168 IF(msg_tbl.COUNT > 0) THEN
169 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
170 LOOP
171 err_msg_count := err_msg_tbl.COUNT + 1;
172 err_msg_tbl(err_msg_count).transaction_number := p_transaction_number;
173 err_msg_tbl(err_msg_count).msg_text := msg_tbl(i).msg_text;
174 END LOOP;
175 END IF;
176 -- transactions which are processed will be inserted in to pro_msg_tbl
177 ELSIF(log_msg_flag = 'P') THEN
178 pro_count := pro_log_tbl.COUNT + 1;
179 pro_log_tbl(pro_count).transaction_number := p_transaction_number;
180 pro_log_tbl(pro_count).contract_number := p_contract_number;
181 pro_log_tbl(pro_count).asset_number := p_asset_number;
182 pro_log_tbl(pro_count).date_effective_from := p_date_effective;
183 pro_log_tbl(pro_count).quote_type := p_quote_type;
184 pro_log_tbl(pro_count).quote_reason := p_quote_reason;
185 pro_log_tbl(pro_count).quote_number := p_quote_number;
186
187 END IF;
188 -- enter the messages in to LOG
189 IF(log_msg_flag = 'Y') THEN
190 -- cursor to retrieve operating unit name
191 FOR org_rec IN org_csr (l_org_id) LOOP
192 l_org_name := org_rec.name;
193 END LOOP;
194 tot_count := p_quote_number;
195 val_count := val_log_tbl.COUNT;
196 err_count := err_log_tbl.COUNT;
197 pro_count := tot_count-(val_count+err_count);
198 -- loop through the message and write to CM log
199 fnd_file.put_line(fnd_file.log,'====================================');
200 fnd_file.put_line(fnd_file.log,'Termination Interface - LOG MESSAGES');
201 fnd_file.put_line(fnd_file.log,'====================================');
202 fnd_file.put_line(fnd_file.log,'Operating Unit :'||l_org_name);
203 fnd_file.put_line(fnd_file.log,'Run Date :'||SYSDATE);
204 fnd_file.new_line(fnd_file.log);
205 fnd_file.put_line(fnd_file.log,'-----------------------');
206 fnd_file.put_line(fnd_file.log,'SUMMARY OF TRANSACTIONS');
207 fnd_file.put_line(fnd_file.log,'-----------------------');
208 fnd_file.new_line(fnd_file.log);
209 fnd_file.put_line(fnd_file.log,'Processed Successfully : '||pro_count);
210 fnd_file.put_line(fnd_file.log,'Not Processed Due To Errors : '||val_count);
211 fnd_file.put_line(fnd_file.log,'Processed with Errors : '||err_count);
212 fnd_file.new_line(fnd_file.log);
213 fnd_file.put_line(fnd_file.log,'Total Records : '||tot_count);
214 fnd_file.new_line(fnd_file.log);
215 IF (val_log_tbl.COUNT > 0) THEN
216 fnd_file.put_line(fnd_file.log,'----------------------------------------');
217 fnd_file.put_line(fnd_file.log,'TRANSACTIONS NOT PROCESSED DUE TO ERRORS');
218 fnd_file.put_line(fnd_file.log,'----------------------------------------');
219 FOR record_number in val_log_tbl.FIRST..val_log_tbl.LAST LOOP
220 fnd_file.new_line(fnd_file.log);
221 fnd_file.put_line(fnd_file.log,'Transaction Number : '||val_log_tbl(record_number).transaction_number);
222 fnd_file.put_line(fnd_file.log,'Contract Number : '||val_log_tbl(record_number).contract_number);
223 fnd_file.put_line(fnd_file.log,'Quote Effective From Date : '||val_log_tbl(record_number).date_effective_from);
224 -- to find the meaning of quote_type_code
225 l_quote_type := NULL;
226 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => val_log_tbl(record_number).quote_type)
227 LOOP
228 l_quote_type := get_type.meaning;
229 END LOOP;
230 fnd_file.put(fnd_file.log,'Quote Type : ');
231 IF(l_quote_type IS NOT NULL) THEN
232 fnd_file.put(fnd_file.log,l_quote_type);
233 END IF;
234 fnd_file.put_line(fnd_file.log,'( '||val_log_tbl(record_number).quote_type||' )');
235 -- to find the meaning of quote_reason_code
236 l_quote_reason := NULL;
237 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => val_log_tbl(record_number).quote_reason)
238 LOOP
239 l_quote_reason := get_reason.meaning;
240 END LOOP;
241 fnd_file.put(fnd_file.log,'Quote Reason : ');
242 IF(l_quote_reason IS NOT NULL) THEN
243 fnd_file.put(fnd_file.log,l_quote_reason);
244 END IF;
245 fnd_file.put_line(fnd_file.log,'( '||val_log_tbl(record_number).quote_reason||' )');
246 fnd_file.put_line(fnd_file.log,'Asset Number : '||val_log_tbl(record_number).asset_number);
247 fnd_file.put_line(fnd_file.log,'Messages ');
248 count_msg := 1;
249 -- modified for BUG#3062867
250 IF(val_msg_tbl.COUNT>0) THEN
251 FOR i IN val_msg_tbl.FIRST..val_msg_tbl.LAST
252 LOOP
253 IF(val_msg_tbl(i).transaction_number = val_log_tbl(record_number).transaction_number) THEN
254 fnd_file.put_line(fnd_file.log,count_msg||': '||val_msg_tbl(i).msg_text);
255 count_msg := count_msg+1;
256 END IF;
257 END LOOP;
258 END IF;
259
260 fnd_file.new_line(fnd_file.log);
261 END LOOP;
262 END IF; -- val_log_tbl COUNT > 0
263 IF (err_log_tbl.COUNT > 0) THEN
264 fnd_file.put_line(fnd_file.log,'----------------------------------');
265 fnd_file.put_line(fnd_file.log,'TRANSACTIONS PROCESSED WITH ERRORS');
266 fnd_file.put_line(fnd_file.log,'----------------------------------');
267 FOR record_number in err_log_tbl.FIRST..err_log_tbl.LAST LOOP
268 fnd_file.new_line(fnd_file.log);
269 fnd_file.put_line(fnd_file.log,'Transaction Number : '||err_log_tbl(record_number).transaction_number);
270 fnd_file.put_line(fnd_file.log,'Contract Number : '||err_log_tbl(record_number).contract_number);
271 fnd_file.put_line(fnd_file.log,'Quote Effective From Date : '||err_log_tbl(record_number).date_effective_from);
272 -- to find the meaning of quote_type_code
273 l_quote_type := NULL;
274 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => err_log_tbl(record_number).quote_type)
275 LOOP
276 l_quote_type := get_type.meaning;
277 END LOOP;
278 fnd_file.put(fnd_file.log,'Quote Type : ');
279 IF(l_quote_type IS NOT NULL) THEN
280 fnd_file.put(fnd_file.log,l_quote_type);
281 END IF;
282 fnd_file.put_line(fnd_file.log,'( '||err_log_tbl(record_number).quote_type||' )');
283 -- to find the meaning of quote_reason_code
284 l_quote_reason := NULL;
285 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => err_log_tbl(record_number).quote_reason)
286 LOOP
287 l_quote_reason := get_reason.meaning;
288 END LOOP;
289 fnd_file.put(fnd_file.log,'Quote Reason : ');
290 IF(l_quote_reason IS NOT NULL) THEN
291 fnd_file.put(fnd_file.log,l_quote_reason);
292 END IF;
293 fnd_file.put_line(fnd_file.log,'( '||err_log_tbl(record_number).quote_reason||' )');
294 fnd_file.put_line(fnd_file.log,'Asset Number : '||err_log_tbl(record_number).asset_number);
295 fnd_file.put_line(fnd_file.log,'Quote Number : '||err_log_tbl(record_number).quote_number);
296 fnd_file.put_line(fnd_file.log,'Messages ');
297 count_msg := 1;
298
299 -- modified for BUG#3062867
300 IF(err_msg_tbl.COUNT>0) THEN
301 FOR i IN err_msg_tbl.FIRST..err_msg_tbl.LAST
302 LOOP
303 IF(err_msg_tbl(i).transaction_number = err_log_tbl(record_number).transaction_number) THEN
304 fnd_file.put_line(fnd_file.log,count_msg||': '||err_msg_tbl(i).msg_text);
305 count_msg := count_msg+1;
306 END IF;
307 END LOOP;
308 END IF;
309
310 fnd_file.new_line(fnd_file.log);
311 END LOOP;
312 END IF; -- err_log_tbl COUNT > 0
313 count_msg := 1;
314 -- statement came along with flag 'Y'
315 IF(msg_tbl.COUNT>0) THEN
316 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
317 LOOP
318 fnd_file.put_line(fnd_file.log,count_msg ||': '||msg_tbl(i).msg_text);
319 count_msg := count_msg+1;
320 END LOOP;
321 END IF;
322 fnd_file.put_line(fnd_file.log,'=======================================');
323 END IF;
324 -- enter the messages in to OUTPUT
325 IF(log_msg_flag = 'O') THEN
326 tot_count := p_quote_number;
327 val_count := val_log_tbl.COUNT;
328 err_count := err_log_tbl.COUNT;
329 pro_count := tot_count-(val_count+err_count);
330 -- cursor to retrieve operating unit name
331 FOR org_rec IN org_csr (l_org_id) LOOP
332 l_org_name := org_rec.name;
333 END LOOP;
334 -- loop through the message and write to CM log
335 fnd_file.put_line(fnd_file.output,'=======================================');
336 fnd_file.put_line(fnd_file.output,'Termination Interface - OUTPUT MESSAGES');
337 fnd_file.put_line(fnd_file.output,'=======================================');
338 fnd_file.put_line(fnd_file.output,'Operating Unit :'||l_org_name);
339 fnd_file.put_line(fnd_file.output,'Run Date :'||SYSDATE);
340 fnd_file.new_line(fnd_file.output);
341 fnd_file.put_line(fnd_file.output,'-----------------------');
342 fnd_file.put_line(fnd_file.output,'SUMMARY OF TRANSACTIONS');
343 fnd_file.put_line(fnd_file.output,'-----------------------');
344 fnd_file.new_line(fnd_file.output);
345 fnd_file.put_line(fnd_file.output,'Processed Successfully : '||pro_count);
346 fnd_file.put_line(fnd_file.output,'Not Processed Due To Errors : '||val_count);
347 fnd_file.put_line(fnd_file.output,'Processed with Errors : '||err_count);
348 fnd_file.new_line(fnd_file.output);
349 --pro_count := pro_log_tbl.COUNT+val_log_tbl.COUNT+err_log_tbl.COUNT;
350 fnd_file.put_line(fnd_file.output,'Total Records : '||tot_count);
351 fnd_file.new_line(fnd_file.output);
352 IF (val_log_tbl.COUNT > 0) THEN
353 fnd_file.put_line(fnd_file.output,'----------------------------------------');
354 fnd_file.put_line(fnd_file.output,'TRANSACTIONS NOT PROCESSED DUE TO ERRORS');
355 fnd_file.put_line(fnd_file.output,'----------------------------------------');
356 FOR record_number in val_log_tbl.FIRST..val_log_tbl.LAST LOOP
357 fnd_file.new_line(fnd_file.output);
358 fnd_file.put_line(fnd_file.output,'Transaction Number : '||val_log_tbl(record_number).transaction_number);
359 fnd_file.put_line(fnd_file.output,'Contract Number : '||val_log_tbl(record_number).contract_number);
360 fnd_file.put_line(fnd_file.output,'Quote Effective From Date : '||val_log_tbl(record_number).date_effective_from);
361
362 -- to find the meaning of quote_type_code
363 l_quote_type := NULL;
364 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => val_log_tbl(record_number).quote_type)
365 LOOP
366 l_quote_type := get_type.meaning;
367 END LOOP;
368 fnd_file.put(fnd_file.output,'Quote Type : ');
369 IF(l_quote_type IS NOT NULL) THEN
370 fnd_file.put(fnd_file.output,l_quote_type);
371 END IF;
372 fnd_file.put_line(fnd_file.output,'( '||val_log_tbl(record_number).quote_type||' )');
373 -- to find the meaning of quote_reason_code
374 l_quote_reason := NULL;
375 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => val_log_tbl(record_number).quote_reason)
376 LOOP
377 l_quote_reason := get_reason.meaning;
378 END LOOP;
379 fnd_file.put(fnd_file.output,'Quote Reason : ');
380 IF(l_quote_reason IS NOT NULL) THEN
381 fnd_file.put(fnd_file.output,l_quote_reason);
382 END IF;
383 fnd_file.put_line(fnd_file.output,'( '||val_log_tbl(record_number).quote_reason||' )');
384 fnd_file.put_line(fnd_file.output,'Asset Number : '||val_log_tbl(record_number).asset_number);
385 fnd_file.put_line(fnd_file.output,'Messages ');
386 count_msg := 1;
387
388 -- modified for BUG#3062867
389 IF(val_msg_tbl.COUNT>0) THEN
390 FOR i IN val_msg_tbl.FIRST..val_msg_tbl.LAST
391 LOOP
392 IF(val_msg_tbl(i).transaction_number = val_log_tbl(record_number).transaction_number) THEN
393 fnd_file.put_line(fnd_file.output,count_msg||': '||val_msg_tbl(i).msg_text);
394 count_msg := count_msg+1;
395 END IF;
396 END LOOP;
397 END IF;
398
399 fnd_file.new_line(fnd_file.output);
400 END LOOP;
401 END IF; -- val_msg_tbl COUNT > 0
402 IF (err_log_tbl.COUNT > 0) THEN
403 fnd_file.put_line(fnd_file.output,'----------------------------------');
404 fnd_file.put_line(fnd_file.output,'TRANSACTIONS PROCESSED WITH ERRORS');
405 fnd_file.put_line(fnd_file.output,'----------------------------------');
406 FOR record_number in err_log_tbl.FIRST..err_log_tbl.LAST LOOP
407 fnd_file.new_line(fnd_file.output);
408 fnd_file.put_line(fnd_file.output,'Transaction Number : '||err_log_tbl(record_number).transaction_number);
409 fnd_file.put_line(fnd_file.output,'Contract Number : '||err_log_tbl(record_number).contract_number);
410 fnd_file.put_line(fnd_file.output,'Quote Effective From Date : '||err_log_tbl(record_number).date_effective_from);
411 -- to find the meaning of quote_type_code
412 l_quote_type := NULL;
413 FOR get_type IN get_quote_type_meaning_csr(p_quote_type => err_log_tbl(record_number).quote_type)
414 LOOP
415 l_quote_type := get_type.meaning;
416 END LOOP;
417 fnd_file.put(fnd_file.output,'Quote Type : ');
418 IF(l_quote_type IS NOT NULL) THEN
419 fnd_file.put(fnd_file.output,l_quote_type);
420 END IF;
421 fnd_file.put_line(fnd_file.output,'( '||err_log_tbl(record_number).quote_type||' )');
422 -- to find the meaning of quote_reason_code
423 l_quote_reason := NULL;
424 FOR get_reason IN get_quote_reason_meaning_csr(p_quote_reason => err_log_tbl(record_number).quote_reason)
425 LOOP
426 l_quote_reason := get_reason.meaning;
427 END LOOP;
428 fnd_file.put(fnd_file.output,'Quote Reason : ');
429 IF(l_quote_reason IS NOT NULL) THEN
430 fnd_file.put(fnd_file.output,l_quote_reason);
431 END IF;
432 fnd_file.put_line(fnd_file.output,'( '||err_log_tbl(record_number).quote_reason||' )');
433 fnd_file.put_line(fnd_file.output,'Asset Number : '||err_log_tbl(record_number).asset_number);
434 fnd_file.put_line(fnd_file.output,'Quote Number : '||err_log_tbl(record_number).quote_number);
435 fnd_file.put_line(fnd_file.output,'Messages ');
436 count_msg := 1;
437
438 -- modified for BUG#3062867
439 IF(err_msg_tbl.COUNT>0) THEN
440 FOR i IN err_msg_tbl.FIRST..err_msg_tbl.LAST
441 LOOP
442 IF(err_msg_tbl(i).transaction_number = err_log_tbl(record_number).transaction_number) THEN
443 fnd_file.put_line(fnd_file.output,count_msg||': '||err_msg_tbl(i).msg_text);
444 count_msg := count_msg+1;
445 END IF;
446 END LOOP;
447 END IF;
448
449 fnd_file.new_line(fnd_file.output);
450 END LOOP;
451 END IF; -- err_log_tbl COUNT > 0
452 count_msg := 1;
453 IF(msg_tbl.COUNT>0) THEN
454 FOR i IN msg_tbl.FIRST..msg_tbl.LAST
455 LOOP
456 fnd_file.put_line(fnd_file.output,count_msg ||': '||msg_tbl(i).msg_text);
457 count_msg := count_msg+1;
458 END LOOP;
459 END IF;
460 fnd_file.put_line(fnd_file.output,'=======================================');
461 END IF;
462 EXCEPTION
463 WHEN OTHERS THEN
464 -- store SQL error message on message stack for caller
465 OKC_API.set_message(p_app_name => g_app_name,
466 p_msg_name => g_unexpected_error,
467 p_token1 => g_sqlcode_token,
468 p_token1_value => sqlcode,
469 p_token2 => g_sqlerrm_token,
470 p_token2_value => sqlerrm);
471 END log_messages;
472
473 -- Start of comments
474 --
475 -- Procedure Name : validate_quote_type_and_reason
476 -- Description : This procedure checks whether quote_type and quote_reason are valid
477 -- Business Rules :
478 -- Parameters : Input parameters : p_tif_tbl, p_sys_date
479 -- Version : 1.0
480 -- History : 11-MAR-03 RABHUPAT Created
481 -- End of comments
482
483 PROCEDURE validate_quote_type_and_reason(p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2,
485 x_msg_count OUT NOCOPY NUMBER,
486 x_msg_data OUT NOCOPY VARCHAR2,
487 x_return_status OUT NOCOPY VARCHAR2,
488 p_tif_tbl IN tif_tbl_type,
489 x_tif_tbl OUT NOCOPY tif_tbl_type) IS
490 /* cursor retrives the distinct quote_types from INTERFACE TABLE*/
491 CURSOR get_quote_type_csr IS
492 SELECT DISTINCT(quote_type_code) quote_type
493 FROM OKL_TERMNT_INTERFACE
494 WHERE status = 'ENTERED' AND quote_type_code IS NOT NULL;
495
496 /* validates each quote_type in INTERFACE table against FND_LOOKUPS*/
497 CURSOR validate_quote_type_csr(p_quote_type IN VARCHAR2) IS
498 SELECT COUNT(lookup_code) code
499 FROM FND_LOOKUPS
500 WHERE lookup_code = p_quote_type AND lookup_type = 'OKL_QUOTE_TYPE' AND enabled_flag = 'Y';
501
502 /* cursor retrives the distinct quote_reason from INTERFACE TABLE*/
503 CURSOR get_quote_reason_csr IS
504 SELECT DISTINCT(quote_reason_code) quote_reason
505 FROM OKL_TERMNT_INTERFACE
506 WHERE status = 'ENTERED' AND quote_reason_code IS NOT NULL;
507
508 /* validates each quote_reason in INTERFACE table against FND_LOOKUPS*/
509 CURSOR validate_quote_reason_csr(p_quote_reason IN VARCHAR2) IS
510 SELECT COUNT(lookup_code) code
511 FROM FND_LOOKUPS
512 WHERE lookup_code = p_quote_reason AND lookup_type = 'OKL_QUOTE_REASON' AND enabled_flag = 'Y';
513
514 l_quote_type VARCHAR2(20) :='';
515 l_quote_reason VARCHAR2(30) :='';
516 l_code NUMBER := 0;
517 lp_tif_tbl tif_tbl_type;
518 l_msg_tbl msg_tbl_type;
519 BEGIN
520 x_return_status := OKC_API.G_RET_STS_SUCCESS;
521 lp_tif_tbl := p_tif_tbl;
522 IF(lp_tif_tbl.COUNT>0) THEN
523 --loops through the interface table records to populate WHO_columns
524 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
525 LOOP
526 lp_tif_tbl(record_number).CREATION_DATE := SYSDATE;
527 lp_tif_tbl(record_number).CREATED_BY := FND_GLOBAL.USER_ID;
528 END LOOP;
529 END IF;
530 /* cursor retrives the distinct quote_types from INTERFACE TABLE*/
531 FOR term_rec IN get_quote_type_csr
532 LOOP
533 l_quote_type := term_rec.quote_type;
534 l_code := 0;
535 /* validates each quote_type in INTERFACE table against FND_LOOKUPS*/
536 FOR check_type IN validate_quote_type_csr(p_quote_type => l_quote_type)
537 LOOP
538 l_code := check_type.code;
539 END LOOP;
540 /* if quote_type not exists in FND_LOOKUPS then ERROR out corresponding columns */
541 IF(l_code = 0)THEN
542 IF(lp_tif_tbl.COUNT>0) THEN
543 /*loops through the interface table records */
544 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
545 LOOP
546 IF(lp_tif_tbl(record_number).quote_type_code = l_quote_type) THEN
547 -- quote type entered is invalid
548 lp_tif_tbl(record_number).status := 'ERROR';
549 OKC_API.set_message(p_app_name => g_app_name,
550 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
551 p_token1 => 'COL_NAME',
552 p_token1_value => 'QUOTE_TYPE_CODE');
553 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 ';
554 log_messages(log_msg_flag => 'V',
555 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
556 p_contract_number => lp_tif_tbl(record_number).contract_number,
557 p_asset_number => lp_tif_tbl(record_number).asset_number,
558 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
559 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
560 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
561 msg_tbl => l_msg_tbl);
562 END IF;
563 END LOOP;
564 END IF;
565 ELSE
566 IF(lp_tif_tbl.COUNT>0) THEN
567 /*loops through the interface table records */
568 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
569 LOOP
570 IF(lp_tif_tbl(record_number).quote_type_code LIKE 'TER_MAN%') THEN
571 --manual quotes are not allowed
572 lp_tif_tbl(record_number).status := 'ERROR';
573 OKC_API.set_message(p_app_name => g_app_name,
574 p_msg_name => 'OKL_AM_MAN_QUOTE_TYPE',
575 p_token1 => 'TRANSACTION NUMBER',
576 p_token1_value => lp_tif_tbl(record_number).transaction_number);
577 l_msg_tbl(0).msg_text := 'manual quotes not allowed for transaction number '||lp_tif_tbl(record_number).transaction_number;
578 log_messages(log_msg_flag => 'V',
579 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
580 p_contract_number => lp_tif_tbl(record_number).contract_number,
581 p_asset_number => lp_tif_tbl(record_number).asset_number,
582 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
583 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
584 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
585 msg_tbl => l_msg_tbl);
586 --Bug# 3925453: pagarg +++ T and A +++++++ Start ++++++++++
587 ELSIF(lp_tif_tbl(record_number).quote_type_code = 'TER_RELASE_WO_PURCHASE')
588 THEN
589 -- Release quotes are not allowed
590 lp_tif_tbl(record_number).status := 'ERROR';
591 OKC_API.set_message(p_app_name => g_app_name,
592 p_msg_name => 'OKL_AM_TER_INTF_RELEASE_QTE');
593 l_msg_tbl(0).msg_text := 'Creation of Release quote is not allowed for termination interface';
594 log_messages(log_msg_flag => 'V',
595 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
596 p_contract_number => lp_tif_tbl(record_number).contract_number,
597 p_asset_number => lp_tif_tbl(record_number).asset_number,
598 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
599 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
600 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
601 msg_tbl => l_msg_tbl);
602 --Bug# 3925453: pagarg +++ T and A +++++++ End ++++++++++
603 END IF;
604 END LOOP;
605 END IF;
606 END IF;
607 END LOOP;
608
609 /* cursor retrives the distinct quote_reason from INTERFACE TABLE*/
610 FOR term_rec IN get_quote_reason_csr
611 LOOP
612 l_quote_reason := term_rec.quote_reason;
613 l_code := 0;
614 /* validates each quote_reason in INTERFACE table against FND_LOOKUPS*/
615 FOR check_reason IN validate_quote_reason_csr(p_quote_reason => l_quote_reason)
616 LOOP
617 l_code := check_reason.code;
618 END LOOP;
619 /* if quote_reason not exists in FND_LOOKUPS then ERROR out corresponding columns */
620 IF(l_code = 0)THEN
621 IF(lp_tif_tbl.COUNT>0) THEN
622 /*loops through the interface table records */
623 FOR record_number IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
624 LOOP
625 IF(lp_tif_tbl(record_number).quote_reason_code = l_quote_reason) THEN
626 -- quote reason entered is invalid
627 lp_tif_tbl(record_number).status := 'ERROR';
628 OKC_API.set_message(p_app_name => g_app_name,
629 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
630 p_token1 => 'COL_NAME',
631 p_token1_value => 'QUOTE_REASON_CODE');
632 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 ';
633 log_messages(log_msg_flag => 'V',
634 p_transaction_number => lp_tif_tbl(record_number).transaction_number,
635 p_contract_number => lp_tif_tbl(record_number).contract_number,
636 p_asset_number => lp_tif_tbl(record_number).asset_number,
637 p_date_effective => lp_tif_tbl(record_number).date_effective_from,
638 p_quote_type => lp_tif_tbl(record_number).quote_type_code,
639 p_quote_reason => lp_tif_tbl(record_number).quote_reason_code,
640 msg_tbl => l_msg_tbl );
641 END IF;
642 END LOOP;
643 END IF;
644 END IF;
645 END LOOP;
646 x_tif_tbl := lp_tif_tbl;
647 EXCEPTION
648 WHEN OTHERS THEN
649 -- store SQL error message on message stack for caller
650 OKC_API.set_message(p_app_name => g_app_name,
651 p_msg_name => g_unexpected_error,
652 p_token1 => g_sqlcode_token,
653 p_token1_value => sqlcode,
654 p_token2 => g_sqlerrm_token,
655 p_token2_value => sqlerrm);
656 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
657 l_msg_tbl(0).msg_text := 'validate_quote_type_and_reason: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
658 log_messages(log_msg_flag => 'V',
659 msg_tbl => l_msg_tbl );
660 END validate_quote_type_and_reason;
661
662 -- Start of comments
663 --
664 -- Procedure Name : validate_required
665 -- Description : This procedure checks whether required fields are entered or not
666 -- Business Rules :
667 -- Parameters : Input parameters : p_tif_rec, p_sys_date
668 -- Version : 1.0
669 -- History : 04-FEB-03 RABHUPAT Created
670 -- : 14-APR-03 RABHUPAT added validation for auto_accept_yn
671 -- : removed logic for setting default for quote_reason
672 -- : as the column is changed to NOT NULL
673 -- End of comments
674
675 PROCEDURE validate_required(
676 p_api_version IN NUMBER,
677 p_init_msg_list IN VARCHAR2,
678 x_msg_count OUT NOCOPY NUMBER,
679 x_msg_data OUT NOCOPY VARCHAR2,
680 x_return_status OUT NOCOPY VARCHAR2,
681 p_tif_rec IN tif_rec_type,
682 p_sys_date IN DATE,
683 x_tif_rec OUT NOCOPY tif_rec_type) IS
684 message VARCHAR2(200);
685 l_msg_tbl msg_tbl_type;
686 BEGIN
687 x_tif_rec := p_tif_rec;
688 x_return_status := OKC_API.G_RET_STS_SUCCESS;
689 --checks whether asset id or asset number entered or not
690 IF((p_tif_rec.asset_id IS NULL) OR (p_tif_rec.asset_id = OKC_API.G_MISS_NUM)) THEN
691 IF((p_tif_rec.asset_number IS NULL) OR (p_tif_rec.asset_number = OKC_API.G_MISS_CHAR)) THEN
692 x_tif_rec.status := 'ERROR';
693 OKC_API.set_message(p_app_name => g_app_name,
694 p_msg_name => 'OKC_AM_ASSET_REQUIRED',
695 p_token1 => 'CONTRACT_NUMBER',
696 p_token1_value => p_tif_rec.contract_number);
697 message :='asset number and id not entered for contract_number '||p_tif_rec.contract_number;
698 END IF;
699 END IF;
700 IF(x_tif_rec.status <> 'ERROR') THEN
701 /*checks whether asset is serialized or not and if serialized checks whether quantity is one or null
702 for non serialized assets checks whether quantity entered is greater than zero or not*/
703 IF((p_tif_rec.serial_number IS NOT NULL) AND (p_tif_rec.serial_number <> OKC_API.G_MISS_CHAR)) THEN
704 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
705 x_tif_rec.status := 'ERROR';
706 OKC_API.set_message(p_app_name => g_app_name,
707 p_msg_name => 'OKL_AM_SER_ASSET_QTY',
708 p_token1 => 'ASSET_NUMBER',
709 p_token1_value => p_tif_rec.asset_number,
710 p_token2 => 'SERIAL_NUMBER',
711 p_token2_value => p_tif_rec.serial_number);
712 message :='serialized asset with serial number '||p_tif_rec.serial_number||' should have quantity as one ';
713 END IF;
714 ELSE
715 -- if asset quantity is less than 1
716 IF(p_tif_rec.units_to_terminate < 1) THEN
717 x_tif_rec.status := 'ERROR';
718 OKC_API.set_message(p_app_name => g_app_name,
719 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
720 p_token1 => 'COL_NAME',
721 p_token1_value => 'UNITS_TO_TERMINATE');
722 message :='asset '||p_tif_rec.asset_number||' '||p_tif_rec.asset_id||' can not have quantity less than one';
723 END IF;
724 END IF;
725 END IF;
726
727 -- defaults date_effective_from to Sysdate if null and error out if past date entered.
728 IF(x_tif_rec.status <> 'ERROR') THEN
729 IF((p_tif_rec.date_effective_from IS NULL) OR (p_tif_rec.date_effective_from = OKC_API.G_MISS_DATE)) THEN
730 x_tif_rec.date_effective_from := p_sys_date;
731 --Bug 4136202 : Commented check for past date as this is now allowed (Effective dated termination impact)
732 /*ELSIF(p_tif_rec.date_effective_from < p_sys_date) THEN
733 x_tif_rec.status := 'ERROR';
734 OKC_API.set_message(p_app_name => g_app_name,
735 p_msg_name => 'OKL_AM_DATE_EFF_FROM_PAST',
736 p_token1 => 'COL_NAME',
737 p_token1_value => 'DATE_EFFECTIVE_FROM');
738 message :='date_effective_from '||p_tif_rec.contract_number||' should not be a past date';*/
739 END IF;
740 END IF;
741 /* defaults the auto_accept_yn to 'N' */
742 IF(x_tif_rec.status <> 'ERROR')THEN
743 IF(x_tif_rec.auto_accept_yn IS NULL OR x_tif_rec.auto_accept_yn = OKC_API.G_MISS_CHAR)THEN
744 x_tif_rec.auto_accept_yn := 'N';
745 -- if some wrong charcter entered default it to 'N'
746 ELSIF(x_tif_rec.auto_accept_yn NOT IN ('Y','N')) THEN
747 x_tif_rec.auto_accept_yn := 'N';
748 END IF;
749 END IF;
750 IF(x_tif_rec.status = 'ERROR')THEN
751 l_msg_tbl(0).msg_text := message;
752 log_messages(log_msg_flag => 'V',
753 p_transaction_number => x_tif_rec.transaction_number,
754 p_contract_number => x_tif_rec.contract_number,
755 p_asset_number => x_tif_rec.asset_number,
756 p_date_effective => x_tif_rec.date_effective_from,
757 p_quote_type => x_tif_rec.quote_type_code,
758 p_quote_reason => x_tif_rec.quote_reason_code,
759 msg_tbl => l_msg_tbl );
760 END IF;
761 OKL_AM_UTIL_PVT.process_messages(
762 p_trx_source_table => 'OKL_TERMNT_INTERFACE',
763 p_trx_id => p_tif_rec.transaction_number,
764 x_return_status => x_return_status);
765
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 -- store SQL error message on message stack for caller
770 OKC_API.set_message(p_app_name => g_app_name,
771 p_msg_name => g_unexpected_error,
772 p_token1 => g_sqlcode_token,
773 p_token1_value => sqlcode,
774 p_token2 => g_sqlerrm_token,
775 p_token2_value => sqlerrm);
776 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
777 l_msg_tbl(0).msg_text := 'validate_required:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
778 log_messages(log_msg_flag => 'V',
779 msg_tbl => l_msg_tbl );
780 END validate_required;
781
782 -- Start of comments
783 --
784 -- Procedure Name : validate_data
785 -- Description : This procedure checks whether data entered is appropriate
786 -- : or not, also populates the remaining columns required
787 -- Business Rules :
788 -- Parameters : Input parameters : p_tif_rec
789 -- Version : 1.0
790 -- History : 04-FEB-03 RABHUPAT Created
791 -- : 28-MAR-03 RABHUPAT added another cursor to retrive quantity
792 -- : and changed the cursor for finding serial number and code
793 -- End of comments
794
795 PROCEDURE validate_data(
796 p_api_version IN NUMBER,
797 p_init_msg_list IN VARCHAR2,
798 x_msg_count OUT NOCOPY NUMBER,
799 x_msg_data OUT NOCOPY VARCHAR2,
800 x_return_status OUT NOCOPY VARCHAR2,
801 p_tif_rec IN tif_rec_type,
802 x_tif_rec OUT NOCOPY tif_rec_type
803 ) IS
804
805 l_serial_number OKL_TERMNT_INTERFACE.serial_number%TYPE;
806 l_contract_id OKL_TERMNT_INTERFACE.contract_id%TYPE;
807 l_contract_status OKC_K_HEADERS_B.sts_code%TYPE;
808 l_asset_number OKL_TERMNT_INTERFACE.asset_number%TYPE;
809 l_asset_id OKL_TERMNT_INTERFACE.asset_id%TYPE;
810 l_asset_description OKL_TERMNT_INTERFACE.asset_description%TYPE;
811 l_asset_status OKL_K_LINES_FULL_V.sts_code%TYPE;
812 l_tif_rec tif_rec_type;
813 l_quantity NUMBER;
814 l_instance_id NUMBER;
815 l_installbase_id NUMBER;
816 l_org_id NUMBER;
817 l_session_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
818 l_count_asset NUMBER := 0;
819 message VARCHAR2(200);
820 l_msg_tbl msg_tbl_type;
821 /*this cursor retrives the contract_id and status from OKC_K_HEADERS_B with contract number
822 as input parameter*/
823 CURSOR get_chr_dtls_csr(p_chr_no IN VARCHAR2) IS
824 SELECT id,sts_code,authoring_org_id
825 FROM okc_k_headers_b
826 WHERE contract_number=p_chr_no;
827 /* this cursor retrives the asset_id,asset_number,asset_status and description from okc_k_lines_v
828 and okc_line_styles_v with contract_id,asset_id,asset_number as input parameters.*/
829 CURSOR get_aset_dtls_csr(p_chr_id IN NUMBER,
830 p_ast_number IN VARCHAR2,
831 p_ast_id IN NUMBER) IS
832 SELECT oklv.id id, oklv.name num, oklv.sts_code status, oklv.item_description description
833 FROM okc_k_lines_v oklv, okc_line_styles_v ols
834 WHERE oklv.chr_id= p_chr_id
835 AND ((oklv.name = p_ast_number)
836 OR (oklv.id = p_ast_id))
837 AND oklv.lse_id=ols.id
838 AND ols.lty_code = 'FREE_FORM1';
839
840 /* this cursor retrives the instance ids for the asset with asset_id as input parameter.*/
841 CURSOR get_instance_id_csr(p_ast_id IN NUMBER) IS
842 SELECT oklv.id id
843 FROM okc_k_lines_v oklv, okc_line_styles_v ols
844 WHERE oklv.cle_id = p_ast_id
845 AND oklv.lse_id=ols.id
846 AND ols.lty_code = 'FREE_FORM2';
847 /*this cursor is used to retrive the installbase id for an instance of the asset*/
848 CURSOR get_installbase_id_csr(p_instance_id IN NUMBER) IS
849 SELECT oklv.id id
850 FROM okc_k_lines_v oklv, okc_line_styles_v ols
851 WHERE oklv.cle_id = p_instance_id
852 AND oklv.lse_id=ols.id
853 AND ols.lty_code = 'INST_ITEM';
854 /* this cursor retrives the serial_number for the instance of the asset*/
855 CURSOR get_sno_csr(p_installbase_id IN NUMBER) IS
856 SELECT oiiv.serial_number sno
857 FROM okc_k_items_v okiv,okx_install_items_v oiiv
858 WHERE okiv.cle_id = p_installbase_id
859 AND okiv.object1_id1=oiiv.instance_id;
860 /* this cursor retrives the total quantity for the asset*/
861 CURSOR get_qty_csr(p_asset_number IN VARCHAR2) IS
862 SELECT current_units quantity
863 FROM OKX_ASSETS_V
864 WHERE asset_number = p_asset_number;
865
866 BEGIN
867 x_tif_rec := p_tif_rec;
868 x_tif_rec.status := 'WORKING';
869 x_return_status := OKC_API.G_RET_STS_SUCCESS;
870 -- fetching the contract_id,status using the cursor
871 FOR l_chr_dtl_csr IN get_chr_dtls_csr(p_chr_no => x_tif_rec.contract_number)
872 LOOP
873 l_contract_id := l_chr_dtl_csr.id;
874 l_contract_status := l_chr_dtl_csr.sts_code;
875 l_org_id := l_chr_dtl_csr.authoring_org_id;
876 END LOOP;
877 -- contract number entered not exists
878 IF(l_contract_id IS NULL OR l_contract_id = OKC_API.G_MISS_NUM) THEN
879 x_tif_rec.status := 'ERROR';
880 OKC_API.set_message(p_app_name => g_app_name,
881 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
882 p_token1 => 'COL_NAME',
883 p_token1_value => 'CONTRACT_NUMBER');
884 message :='contract_number '||x_tif_rec.contract_number||' entered not exists';
885 ELSIF(x_tif_rec.contract_id IS NULL OR x_tif_rec.contract_id = OKC_API.G_MISS_NUM) THEN
886 x_tif_rec.contract_id := l_contract_id;
887 ELSIF(x_tif_rec.contract_id <> l_contract_id) THEN
888 x_tif_rec.status := 'ERROR';
889 -- contract_id and contract_number entered not matches
890 OKC_API.set_message(p_app_name => g_app_name,
891 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
892 p_token1 => 'COL_NAME',
893 p_token1_value => 'CONTRACT_NUMBER');
894 message :='contract_number '||x_tif_rec.contract_number||' and contract_id '||x_tif_rec.contract_id ||' entered not matches';
895 END IF;
896 IF(x_tif_rec.status <> 'ERROR' AND l_contract_status NOT IN('BOOKED','EVERGREEN')) THEN
897 x_tif_rec.status := 'ERROR';
898 -- contract status is not in BOOKED or EVERGREEN
899 -- this message is striked off in the DLD
900 OKC_API.set_message(p_app_name => g_app_name,
901 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
902 p_token1 => 'COL_NAME',
903 p_token1_value => 'CONTRACT_NUMBER');
904 message :='contract_number '||x_tif_rec.contract_number||' is not in booked state or in evergreen state';
905 END IF;
906
907 IF(x_tif_rec.status <> 'ERROR') THEN
908 --fetching the asset details using the cursor
909 FOR l_ast_dtl_csr IN get_aset_dtls_csr(p_chr_id => x_tif_rec.contract_id
910 ,p_ast_number => x_tif_rec.asset_number
911 ,p_ast_id => x_tif_rec.asset_id)
912 LOOP
913 l_asset_id := l_ast_dtl_csr.id;
914 l_asset_number := l_ast_dtl_csr.num;
915 x_tif_rec.asset_description := l_ast_dtl_csr.description;
916 l_asset_status := l_ast_dtl_csr.status;
917 l_count_asset := l_count_asset+1;
918 END LOOP;
919
920 IF(l_count_asset >1) THEN
921 x_tif_rec.status := 'ERROR';
922 -- asset number and asset id not matches
923 OKC_API.set_message(p_app_name => g_app_name,
924 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
925 p_token1 => 'COL_NAME',
926 p_token1_value => 'ASSET_NUMBER');
927 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
928 ELSIF(l_asset_id IS NULL OR l_asset_id = OKC_API.G_MISS_NUM) THEN
929 x_tif_rec.status := 'ERROR';
930 -- asset is not associated to contract
931 OKC_API.set_message(p_app_name => g_app_name,
932 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
933 p_token1 => 'COL_NAME',
934 p_token1_value => 'ASSET_NUMBER');
935 message :='asset is not associated to the contract_number '||x_tif_rec.contract_number||' entered';
936 ELSIF(l_asset_number IS NULL OR l_asset_number = OKC_API.G_MISS_CHAR) THEN
937 x_tif_rec.status := 'ERROR';
938 -- asset is not associated to contract
939 OKC_API.set_message(p_app_name => g_app_name,
940 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
941 p_token1 => 'COL_NAME',
942 p_token1_value => 'ASSET_NUMBER');
943 message :='asset is not associated to the contract_number '||x_tif_rec.contract_number||' entered';
944 ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
945 IF(p_tif_rec.asset_number <> l_asset_number) THEN
946 x_tif_rec.status := 'ERROR';
947 -- asset id and asset number entered not matches
948 OKC_API.set_message(p_app_name => g_app_name,
949 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
950 p_token1 => 'COL_NAME',
951 p_token1_value => 'ASSET_NUMBER');
952 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
953 ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
954 IF(p_tif_rec.asset_id <> l_asset_id) THEN
955 x_tif_rec.status := 'ERROR';
956 -- asset id and asset number entered not matches
957 OKC_API.set_message(p_app_name => g_app_name,
958 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
959 p_token1 => 'COL_NAME',
960 p_token1_value => 'ASSET_NUMBER');
961 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
962 END IF;
963 ELSE
964 x_tif_rec.asset_id := l_asset_id;
965 END IF;
966 ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
967 IF(p_tif_rec.asset_id <> l_asset_id) THEN
968 x_tif_rec.status := 'ERROR';
969 -- asset id and asset number entered not matches
970 OKC_API.set_message(p_app_name => g_app_name,
971 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
972 p_token1 => 'COL_NAME',
973 p_token1_value => 'ASSET_NUMBER');
974 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
975 ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
976 IF(p_tif_rec.asset_number <> l_asset_number) THEN
977 x_tif_rec.status := 'ERROR';
978 -- asset id and asset number entered not matches
979 OKC_API.set_message(p_app_name => g_app_name,
980 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
981 p_token1 => 'COL_NAME',
982 p_token1_value => 'ASSET_NUMBER');
983 message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
984 END IF;
985 ELSE
986 x_tif_rec.asset_number := l_asset_number;
987 END IF;
988 ELSIF(l_asset_status NOT IN('BOOKED','EVERGREEN')) THEN
989 x_tif_rec.status := 'ERROR';
990 -- asset status is not in BOOKED or EVERGREEN
991 OKC_API.set_message(p_app_name => g_app_name,
992 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
993 p_token1 => 'COL_NAME',
994 p_token1_value => 'ASSET_NUMBER');
995 message :='asset_number '||x_tif_rec.asset_number||' is not in booked state or in evergreen state';
996 END IF;
997 IF(x_tif_rec.status <> 'ERROR') THEN
998 IF(x_tif_rec.serial_number IS NOT NULL AND x_tif_rec.serial_number <> OKC_API.G_MISS_CHAR) THEN
999 x_tif_rec.status := 'ENTERED';
1000 --fetching instance_id using cursor
1001 FOR l_instance_id_csr IN get_instance_id_csr(p_ast_id => x_tif_rec.asset_id)
1002 LOOP
1003 l_instance_id := l_instance_id_csr.id;
1004 IF(l_instance_id IS NULL OR l_instance_id = OKC_API.G_MISS_NUM) THEN
1005 x_tif_rec.status := 'ERROR';
1006 -- instance line not present for the asset
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 => 'SERIAL_NUMBER');
1011 message :='instance line is not present for the asset '||x_tif_rec.asset_number;
1012 ELSE
1013 --fetch installbase_id using cursor
1014 FOR l_installbase_id_csr IN get_installbase_id_csr(p_instance_id => l_instance_id)
1015 LOOP
1016 l_installbase_id := l_installbase_id_csr.id;
1017 END LOOP;
1018 IF(l_installbase_id IS NULL OR l_installbase_id = OKC_API.G_MISS_NUM) THEN
1019 x_tif_rec.status := 'ERROR';
1020 -- installbase has no entries for the instance
1021 OKC_API.set_message(p_app_name => g_app_name,
1022 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1023 p_token1 => 'COL_NAME',
1024 p_token1_value => 'SERIAL_NUMBER');
1025 message :='installbase line is not present for the asset '||x_tif_rec.asset_number;
1026 EXIT;
1027 ELSE
1028 -- cursor to find serial number
1029 FOR l_sno_csr IN get_sno_csr(p_installbase_id => l_installbase_id)
1030 LOOP
1031 l_serial_number := l_sno_csr.sno;
1032 END LOOP;
1033 IF(l_serial_number = x_tif_rec.serial_number) THEN
1034 x_tif_rec.status := 'WORKING';
1035 x_tif_rec.units_to_terminate := 1;
1036 EXIT;
1037 ELSIF(l_serial_number IS NULL OR l_serial_number = OKC_API.G_MISS_CHAR) THEN
1038 x_tif_rec.status := 'ERROR';
1039 -- asset is not serialized
1040 OKC_API.set_message(p_app_name => g_app_name,
1041 p_msg_name => 'OKC_AM_NO_SERIALIZED_ASSET',
1042 p_token1 => 'COL_NAME',
1043 p_token1_value => 'ASSET_NUMBER');
1044 message :='asset is not serialized '||x_tif_rec.asset_number;
1045 EXIT;
1046 END IF;
1047 END IF;
1048 END IF;
1049 END LOOP;
1050 IF(x_tif_rec.status = 'ENTERED') THEN
1051 x_tif_rec.status := 'ERROR';
1052 -- enter serial number associated for this asset
1053 OKC_API.set_message(p_app_name => g_app_name,
1054 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1055 p_token1 => 'COL_NAME',
1056 p_token1_value => 'SERIAL_NUMBER');
1057 message :='Enter serial number associated for this asset '||x_tif_rec.asset_number;
1058 END IF;
1059 ELSE
1060 -- cursor to get the asset quantity
1061 FOR l_qty_csr IN get_qty_csr(p_asset_number => x_tif_rec.asset_number)
1062 LOOP
1063 l_quantity := l_qty_csr.quantity;
1064 END LOOP;
1065 IF(l_quantity < x_tif_rec.units_to_terminate) THEN
1066 x_tif_rec.status := 'ERROR';
1067 -- quantity entered is more than asset quantity
1068 OKC_API.set_message(p_app_name => g_app_name,
1069 p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1070 p_token1 => 'ASSET_NUMBER',
1071 p_token1_value => x_tif_rec.asset_number);
1072 message :='no.of units entered to terminate is more than the quantity associated with asset '||x_tif_rec.asset_number;
1073 END IF;
1074 END IF;
1075 END IF; -- instance line
1076 END IF; -- asset if
1077 IF(x_tif_rec.status <> 'ERROR') THEN
1078 -- validate the session ORG_ID with the contract authoring_org_id, if org_id null then assign authoring_org_id
1079 IF((p_tif_rec.org_id IS NULL) OR (p_tif_rec.org_id = OKC_API.G_MISS_NUM)) THEN
1080 x_tif_rec.org_id := l_org_id;
1081 END IF;
1082 IF(x_tif_rec.org_id <> l_session_org_id) THEN
1083 x_tif_rec.status := 'ENTERED';
1084 -- org_id not matches with session org_id so not processing it, but status remains in ENTERED
1085 OKC_API.set_message(p_app_name => g_app_name,
1086 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1087 p_token1 => 'COL_NAME',
1088 p_token1_value => 'ORG_ID');
1089 message :='org id is not for this session, not errored out';
1090 END IF;
1091 END IF;
1092
1093 --Bug #3921591: pagarg +++ Rollover +++++++ Start ++++++++++
1094 --------------------------
1095 -- Validation of quote type and auto_accepted_yn flag
1096 --------------------------
1097 -- If quote type is rollover quote and auto_accepted_yn is 'Y' then throw
1098 -- error as rollover quote can be accepted from rolled over contract only
1099 IF(x_tif_rec.status <> 'ERROR')
1100 THEN
1101 -- Check if quote type is rollover quote and auto_accept_yn is 'Y' then
1102 -- set the status of record to ERROR and store the error message
1103 IF p_tif_rec.quote_type_code LIKE 'TER_ROLL%'
1104 AND p_tif_rec.auto_accept_yn = 'Y'
1105 THEN
1106 x_tif_rec.status := 'ERROR';
1107 OKC_API.set_message(p_app_name => g_app_name,
1108 p_msg_name => 'OKL_NO_ACPT_ROLL_QTE');
1109 message := 'Rollover quotes can only be accepted from booking process of a rolled over contract';
1110 END IF;
1111 END IF;
1112 --Bug #3921591: pagarg +++ Rollover +++++++ End ++++++++++
1113
1114 IF(x_tif_rec.status = 'ERROR')THEN
1115 l_msg_tbl(0).msg_text := message;
1116 log_messages(log_msg_flag => 'V',
1117 p_transaction_number => x_tif_rec.transaction_number,
1118 p_contract_number => x_tif_rec.contract_number,
1119 p_asset_number => x_tif_rec.asset_number,
1120 p_date_effective => x_tif_rec.date_effective_from,
1121 p_quote_type => x_tif_rec.quote_type_code,
1122 p_quote_reason => x_tif_rec.quote_reason_code,
1123 msg_tbl => l_msg_tbl );
1124 END IF;
1125 OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
1126 p_trx_id => p_tif_rec.transaction_number,
1127 x_return_status => x_return_status);
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 -- store SQL error message on message stack for caller
1131 OKC_API.set_message(p_app_name => g_app_name,
1132 p_msg_name => g_unexpected_error,
1133 p_token1 => g_sqlcode_token,
1134 p_token1_value => sqlcode,
1135 p_token2 => g_sqlerrm_token,
1136 p_token2_value => sqlerrm);
1137 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1138 l_msg_tbl(0).msg_text := 'validate_data: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1139 log_messages(log_msg_flag => 'V',
1140 msg_tbl => l_msg_tbl );
1141 END validate_data;
1142
1143 -- Start of comments
1144 --
1145 -- Procedure Name : validate_record
1146 -- Description : This procedure calls validate_required and
1147 -- : validate_data procedures
1148 -- Business Rules :
1149 -- Parameters : Input parameters : p_tif_rec
1150 -- Version : 1.0
1151 -- History : 04-FEB-03 RABHUPAT Created
1152 -- : 28-MAR-03 RABHUPAT removed parameters for counting successful
1153 -- : transactions for validate_required and validate_data
1154 -- End of comments
1155
1156 PROCEDURE validate_record(
1157 p_api_version IN NUMBER,
1158 p_init_msg_list IN VARCHAR2,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2,
1161 x_return_status OUT NOCOPY VARCHAR2,
1162 p_tif_rec IN tif_rec_type,
1163 p_sys_date IN DATE,
1164 x_tif_rec OUT NOCOPY tif_rec_type
1165 ) IS
1166 l_tif_rec tif_rec_type;
1167 l_msg_tbl msg_tbl_type;
1168 BEGIN
1169 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1170 -- this procedure checks whether required fields are entered or not
1171 validate_required(p_api_version => p_api_version,
1172 p_init_msg_list => OKC_API.G_FALSE,
1173 x_msg_count => x_msg_count,
1174 x_msg_data => x_msg_data,
1175 x_return_status => x_return_status,
1176 p_sys_date => p_sys_date,
1177 p_tif_rec => p_tif_rec,
1178 x_tif_rec => x_tif_rec);
1179
1180 IF(x_tif_rec.status <> 'ERROR') THEN
1181 BEGIN
1182 l_tif_rec := x_tif_rec;
1183 /*this procedure validates the data against the database and also
1184 populates the remaining columns */
1185 validate_data(p_api_version => p_api_version,
1186 p_init_msg_list => OKC_API.G_FALSE,
1187 x_msg_count => x_msg_count,
1188 x_msg_data => x_msg_data,
1189 x_return_status => x_return_status,
1190 p_tif_rec => l_tif_rec,
1191 x_tif_rec => x_tif_rec);
1192 END;
1193 END IF;
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 -- store SQL error message on message stack for caller
1197 OKC_API.set_message(p_app_name => g_app_name,
1198 p_msg_name => g_unexpected_error,
1199 p_token1 => g_sqlcode_token,
1200 p_token1_value => sqlcode,
1201 p_token2 => g_sqlerrm_token,
1202 p_token2_value => sqlerrm);
1203 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1204 l_msg_tbl(0).msg_text := 'validate_record: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1205 log_messages(log_msg_flag => 'V',
1206 msg_tbl => l_msg_tbl );
1207 END validate_record;
1208
1209 -- Start of comments
1210 --
1211 -- Procedure Name : validate_transaction
1212 -- Description : this procedure accepts table of records and passes
1213 -- : each record to the validate_record
1214 -- Business Rules :
1215 -- Parameters : Input parameters : p_tif_tbl
1216 -- Version : 1.0
1217 -- History : 04-FEB-03 RABHUPAT Created
1218 -- : 28-MAR-03 RABHUPAT removed parameters for counting successful
1219 -- : transactions for validate_required and validate_data
1220
1221 -- End of comments
1222
1223 PROCEDURE validate_transaction(
1224 p_api_version IN NUMBER,
1225 p_init_msg_list IN VARCHAR2,
1226 x_msg_count OUT NOCOPY NUMBER,
1227 x_msg_data OUT NOCOPY VARCHAR2,
1228 x_return_status OUT NOCOPY VARCHAR2,
1229 p_tif_tbl IN tif_tbl_type,
1230 p_sys_date IN DATE,
1231 x_tif_tbl OUT NOCOPY tif_tbl_type
1232 ) IS
1233 record_number NUMBER;
1234 l_msg_tbl msg_tbl_type;
1235 BEGIN
1236 record_number:=0;
1237 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1238 IF (p_tif_tbl.COUNT > 0) THEN
1239 record_number := p_tif_tbl.FIRST;
1240 LOOP
1241 IF(p_tif_tbl(record_number).status <> 'ERROR') THEN
1242 -- this procedure validates the record
1243 validate_record(p_api_version => p_api_version,
1244 p_init_msg_list => OKC_API.G_FALSE,
1245 x_msg_count => x_msg_count,
1246 x_msg_data => x_msg_data,
1247 x_return_status => x_return_status,
1248 p_sys_date => p_sys_date,
1249 p_tif_rec => p_tif_tbl(record_number),
1250 x_tif_rec => x_tif_tbl(record_number));
1251 ELSE
1252 x_tif_tbl(record_number) := p_tif_tbl(record_number);
1253 END IF;
1254 EXIT WHEN (record_number = p_tif_tbl.LAST);
1255 record_number := p_tif_tbl.NEXT(record_number);
1256 END LOOP;
1257 END IF;
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 -- store SQL error message on message stack for caller
1261 OKC_API.set_message(p_app_name => g_app_name,
1262 p_msg_name => g_unexpected_error,
1263 p_token1 => g_sqlcode_token,
1264 p_token1_value => sqlcode,
1265 p_token2 => g_sqlerrm_token,
1266 p_token2_value => sqlerrm);
1267 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1268 l_msg_tbl(0).msg_text := 'validate_transaction:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1269 log_messages(log_msg_flag => 'V',
1270 msg_tbl => l_msg_tbl );
1271 END validate_transaction;
1272
1273 -- Start of comments
1274 --
1275 -- Procedure Name : update_row
1276 -- Description : This procedure updates the INTERFACE table with
1277 -- : modified column values
1278 -- Business Rules :
1279 -- Parameters : Input parameters : p_tif_rec
1280 -- Version : 1.0
1281 -- History : 04-FEB-03 RABHUPAT Created
1282 -- : 28-MAR-03 RABHUPAT added WHO column updation logic
1283 -- End of comments
1284
1285
1286 PROCEDURE update_row(p_api_version IN NUMBER,
1287 p_init_msg_list IN VARCHAR2,
1288 x_msg_count OUT NOCOPY NUMBER,
1289 x_msg_data OUT NOCOPY VARCHAR2,
1290 x_return_status OUT NOCOPY VARCHAR2,
1291 p_tif_rec IN tif_rec_type) IS
1292 l_api_version CONSTANT NUMBER := p_api_version;
1293 l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
1294 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1295 l_msg_tbl msg_tbl_type;
1296 BEGIN
1297 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1298 --Check API version, initialize message list and create savepoint.
1299 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1300 G_PKG_NAME,
1301 p_init_msg_list,
1302 l_api_version,
1303 p_api_version,
1304 '_PVT',
1305 x_return_status);
1306 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1307 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1308 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1309 RAISE OKC_API.G_EXCEPTION_ERROR;
1310 END IF;
1311
1312 UPDATE OKL_TERMNT_INTERFACE
1313 SET
1314 OKL_TERMNT_INTERFACE.TRANSACTION_NUMBER = p_tif_rec.transaction_number
1315 ,OKL_TERMNT_INTERFACE.BATCH_NUMBER = p_tif_rec.batch_number
1316 ,OKL_TERMNT_INTERFACE.CONTRACT_ID = p_tif_rec.contract_id
1317 ,OKL_TERMNT_INTERFACE.CONTRACT_NUMBER = p_tif_rec.contract_number
1318 ,OKL_TERMNT_INTERFACE.ASSET_ID = p_tif_rec.asset_id
1319 ,OKL_TERMNT_INTERFACE.ASSET_NUMBER = p_tif_rec.asset_number
1320 ,OKL_TERMNT_INTERFACE.ASSET_DESCRIPTION = p_tif_rec.asset_description
1321 ,OKL_TERMNT_INTERFACE.SERIAL_NUMBER = p_tif_rec.serial_number
1322 ,OKL_TERMNT_INTERFACE.ORIG_SYSTEM = p_tif_rec.orig_system
1323 ,OKL_TERMNT_INTERFACE.ORIG_SYSTEM_REFERENCE = p_tif_rec.orig_system_reference
1324 ,OKL_TERMNT_INTERFACE.UNITS_TO_TERMINATE = p_tif_rec.units_to_terminate
1325 ,OKL_TERMNT_INTERFACE.COMMENTS = p_tif_rec.comments
1326 ,OKL_TERMNT_INTERFACE.DATE_PROCESSED = p_tif_rec.date_processed
1327 ,OKL_TERMNT_INTERFACE.DATE_EFFECTIVE_FROM = p_tif_rec.date_effective_from
1328 ,OKL_TERMNT_INTERFACE.TERMINATION_NOTIFICATION_EMAIL = p_tif_rec.termination_notification_email
1329 ,OKL_TERMNT_INTERFACE.TERMINATION_NOTIFICATION_YN = p_tif_rec.termination_notification_yn
1330 ,OKL_TERMNT_INTERFACE.AUTO_ACCEPT_YN = p_tif_rec.auto_accept_yn
1331 ,OKL_TERMNT_INTERFACE.QUOTE_TYPE_CODE = p_tif_rec.quote_type_code
1332 ,OKL_TERMNT_INTERFACE.QUOTE_REASON_CODE = p_tif_rec.quote_reason_code
1333 ,OKL_TERMNT_INTERFACE.QTE_ID = p_tif_rec.qte_id
1334 ,OKL_TERMNT_INTERFACE.STATUS = p_tif_rec.status
1335 ,OKL_TERMNT_INTERFACE.ORG_ID = p_tif_rec.org_id
1336 ,OKL_TERMNT_INTERFACE.REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1, NULL,FND_GLOBAL.CONC_REQUEST_ID),p_tif_rec.request_id)
1337 ,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)
1338 ,OKL_TERMNT_INTERFACE.PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),p_tif_rec.program_id)
1339 ,OKL_TERMNT_INTERFACE.PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,p_tif_rec.program_update_date,SYSDATE)
1340 ,OKL_TERMNT_INTERFACE.ATTRIBUTE_CATEGORY = p_tif_rec.attribute_category
1341 ,OKL_TERMNT_INTERFACE.ATTRIBUTE1 = p_tif_rec.attribute1
1342 ,OKL_TERMNT_INTERFACE.ATTRIBUTE2 = p_tif_rec.attribute2
1343 ,OKL_TERMNT_INTERFACE.ATTRIBUTE3 = p_tif_rec.attribute3
1344 ,OKL_TERMNT_INTERFACE.ATTRIBUTE4 = p_tif_rec.attribute4
1345 ,OKL_TERMNT_INTERFACE.ATTRIBUTE5 = p_tif_rec.attribute5
1346 ,OKL_TERMNT_INTERFACE.ATTRIBUTE6 = p_tif_rec.attribute6
1347 ,OKL_TERMNT_INTERFACE.ATTRIBUTE7 = p_tif_rec.attribute7
1348 ,OKL_TERMNT_INTERFACE.ATTRIBUTE8 = p_tif_rec.attribute8
1349 ,OKL_TERMNT_INTERFACE.ATTRIBUTE9 = p_tif_rec.attribute9
1350 ,OKL_TERMNT_INTERFACE.ATTRIBUTE10 = p_tif_rec.attribute10
1351 ,OKL_TERMNT_INTERFACE.ATTRIBUTE11 = p_tif_rec.attribute11
1352 ,OKL_TERMNT_INTERFACE.ATTRIBUTE12 = p_tif_rec.attribute12
1353 ,OKL_TERMNT_INTERFACE.ATTRIBUTE13 = p_tif_rec.attribute13
1354 ,OKL_TERMNT_INTERFACE.ATTRIBUTE14 = p_tif_rec.attribute14
1355 ,OKL_TERMNT_INTERFACE.ATTRIBUTE15 = p_tif_rec.attribute15
1356 ,OKL_TERMNT_INTERFACE.CREATED_BY = p_tif_rec.created_by
1357 ,OKL_TERMNT_INTERFACE.CREATION_DATE = p_tif_rec.creation_date
1358 ,OKL_TERMNT_INTERFACE.LAST_UPDATED_BY = FND_GLOBAL.USER_ID
1359 ,OKL_TERMNT_INTERFACE.LAST_UPDATE_DATE = SYSDATE
1360 ,OKL_TERMNT_INTERFACE.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1361 ,OKL_TERMNT_INTERFACE.GROUP_NUMBER = p_tif_rec.group_number
1362 WHERE
1363 OKL_TERMNT_INTERFACE.ROWID =p_tif_rec.row_id;
1364 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1365
1366 OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
1367 p_trx_id => p_tif_rec.transaction_number,
1368 x_return_status => l_return_status);
1369 x_return_status := l_return_status;
1370 EXCEPTION
1371 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1372 x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1373 G_PKG_NAME,
1374 'OKC_API.G_RET_STS_ERROR',
1375 x_msg_count,
1376 x_msg_data,
1377 '_PVT');
1378 --unexpected error
1379 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1380 x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1381 G_PKG_NAME,
1382 'OKC_API.G_RET_STS_UNEXP_ERROR',
1383 x_msg_count,
1384 x_msg_data,
1385 '_PVT');
1386 WHEN OTHERS THEN
1387 -- store SQL error message on message stack for caller
1388 OKC_API.set_message(p_app_name => g_app_name,
1389 p_msg_name => g_unexpected_error,
1390 p_token1 => g_sqlcode_token,
1391 p_token1_value => sqlcode,
1392 p_token2 => g_sqlerrm_token,
1393 p_token2_value => sqlerrm);
1394 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1395 l_msg_tbl(0).msg_text := 'update_row:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1396 log_messages(log_msg_flag => 'V',
1397 msg_tbl => l_msg_tbl );
1398 END update_row;
1399
1400 -- Start of comments
1401 --
1402 -- Procedure Name : change_status
1403 -- Description : This procedure calls the update_row, also divides the
1404 -- : Entries into batches
1405 -- Business Rules :
1406 -- Parameters : Input parameters : p_tif_tbl
1407 -- Version : 1.0
1408 -- History : 04-FEB-03 RABHUPAT Created
1409 -- End of comments
1410
1411 PROCEDURE change_status(
1412 p_api_version IN NUMBER,
1413 p_init_msg_list IN VARCHAR2,
1414 x_msg_count OUT NOCOPY NUMBER,
1415 x_msg_data OUT NOCOPY VARCHAR2,
1416 x_return_status OUT NOCOPY VARCHAR2,
1417 p_tif_tbl IN tif_tbl_type,
1418 x_tif_tbl OUT NOCOPY tif_tbl_type
1419 ) IS
1420 record_number NUMBER;
1421 l_msg_tbl msg_tbl_type;
1422 /* this cursor rearranges the data by using the columns contract_number,
1423 quote_type_code, quote_reason_code and asset_id */
1424 CURSOR get_termnt_intface_dtls_csr(p_status IN VARCHAR2) IS
1425 SELECT
1426 ROWID
1427 ,TRANSACTION_NUMBER
1428 ,BATCH_NUMBER
1429 ,CONTRACT_ID
1430 ,CONTRACT_NUMBER
1431 ,ASSET_ID
1432 ,ASSET_NUMBER
1433 ,ASSET_DESCRIPTION
1434 ,SERIAL_NUMBER
1435 ,ORIG_SYSTEM
1436 ,ORIG_SYSTEM_REFERENCE
1437 ,UNITS_TO_TERMINATE
1438 ,COMMENTS
1439 ,DATE_PROCESSED
1440 ,DATE_EFFECTIVE_FROM
1441 ,TERMINATION_NOTIFICATION_EMAIL
1442 ,TERMINATION_NOTIFICATION_YN
1443 ,AUTO_ACCEPT_YN
1444 ,QUOTE_TYPE_CODE
1445 ,QUOTE_REASON_CODE
1446 ,QTE_ID
1447 ,STATUS
1448 ,ORG_ID
1449 ,REQUEST_ID
1450 ,PROGRAM_APPLICATION_ID
1451 ,PROGRAM_ID
1452 ,PROGRAM_UPDATE_DATE
1453 ,ATTRIBUTE_CATEGORY
1454 ,ATTRIBUTE1
1455 ,ATTRIBUTE2
1456 ,ATTRIBUTE3
1457 ,ATTRIBUTE4
1458 ,ATTRIBUTE5
1459 ,ATTRIBUTE6
1460 ,ATTRIBUTE7
1461 ,ATTRIBUTE8
1462 ,ATTRIBUTE9
1463 ,ATTRIBUTE10
1464 ,ATTRIBUTE11
1465 ,ATTRIBUTE12
1466 ,ATTRIBUTE13
1467 ,ATTRIBUTE14
1468 ,ATTRIBUTE15
1469 ,CREATED_BY
1470 ,CREATION_DATE
1471 ,LAST_UPDATED_BY
1472 ,LAST_UPDATE_DATE
1473 ,LAST_UPDATE_LOGIN
1474 ,GROUP_NUMBER
1475 FROM OKL_TERMNT_INTERFACE
1476 WHERE status = p_status
1477 ORDER BY CONTRACT_NUMBER,QUOTE_TYPE_CODE,DATE_EFFECTIVE_FROM,QUOTE_REASON_CODE,ASSET_ID;
1478 BEGIN
1479 record_number:=0;
1480 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1481 IF (p_tif_tbl.COUNT > 0) THEN
1482 record_number := p_tif_tbl.FIRST;
1483 -- loops through the table of records to update the values
1484 LOOP
1485 update_row(p_api_version => p_api_version
1486 ,p_init_msg_list => OKC_API.G_FALSE
1487 ,x_msg_data => x_msg_data
1488 ,x_msg_count => x_msg_count
1489 ,x_return_status => x_return_status
1490 ,p_tif_rec => p_tif_tbl(record_number));
1491 EXIT WHEN (record_number = p_tif_tbl.LAST);
1492 record_number := p_tif_tbl.NEXT(record_number);
1493 END LOOP;
1494 END IF;
1495 record_number:=0;
1496 x_tif_tbl.DELETE;
1497 -- populates the plsql table with rows having status as 'WORKING'
1498 FOR termnt_rec IN get_termnt_intface_dtls_csr(p_status=>'WORKING')
1499 LOOP
1500 x_tif_tbl(record_number).row_id := termnt_rec.rowid;
1501 x_tif_tbl(record_number).transaction_number := termnt_rec.transaction_number;
1502 x_tif_tbl(record_number).batch_number := termnt_rec.batch_number;
1503 x_tif_tbl(record_number).contract_id := termnt_rec.contract_id;
1504 x_tif_tbl(record_number).contract_number := termnt_rec.contract_number;
1505 x_tif_tbl(record_number).asset_id := termnt_rec.asset_id;
1506 x_tif_tbl(record_number).asset_number := termnt_rec.asset_number;
1507 x_tif_tbl(record_number).asset_description := termnt_rec.asset_description;
1508 x_tif_tbl(record_number).serial_number := termnt_rec.serial_number;
1509 x_tif_tbl(record_number).orig_system := termnt_rec.orig_system;
1510 x_tif_tbl(record_number).orig_system_reference := termnt_rec.orig_system_reference;
1511 x_tif_tbl(record_number).units_to_terminate := termnt_rec.units_to_terminate;
1512 x_tif_tbl(record_number).comments := termnt_rec.comments;
1513 x_tif_tbl(record_number).date_processed := termnt_rec.date_processed;
1514 x_tif_tbl(record_number).date_effective_from := termnt_rec.date_effective_from;
1515 x_tif_tbl(record_number).termination_notification_email := termnt_rec.termination_notification_email;
1516 x_tif_tbl(record_number).termination_notification_yn := termnt_rec.termination_notification_yn;
1517 x_tif_tbl(record_number).auto_accept_yn := termnt_rec.auto_accept_yn;
1518 x_tif_tbl(record_number).quote_type_code := termnt_rec.quote_type_code;
1519 x_tif_tbl(record_number).quote_reason_code := termnt_rec.quote_reason_code;
1520 x_tif_tbl(record_number).qte_id := termnt_rec.qte_id;
1521 x_tif_tbl(record_number).status := termnt_rec.status;
1522 x_tif_tbl(record_number).org_id := termnt_rec.org_id;
1523 x_tif_tbl(record_number).request_id := termnt_rec.request_id;
1524 x_tif_tbl(record_number).program_application_id := termnt_rec.program_application_id;
1525 x_tif_tbl(record_number).program_id := termnt_rec.program_id;
1526 x_tif_tbl(record_number).program_update_date := termnt_rec.program_update_date;
1527 x_tif_tbl(record_number).attribute_category := termnt_rec.attribute_category;
1528 x_tif_tbl(record_number).attribute1 := termnt_rec.attribute1;
1529 x_tif_tbl(record_number).attribute2 := termnt_rec.attribute2;
1530 x_tif_tbl(record_number).attribute3 := termnt_rec.attribute3;
1531 x_tif_tbl(record_number).attribute4 := termnt_rec.attribute4;
1532 x_tif_tbl(record_number).attribute5 := termnt_rec.attribute5;
1533 x_tif_tbl(record_number).attribute6 := termnt_rec.attribute6;
1534 x_tif_tbl(record_number).attribute7 := termnt_rec.attribute7;
1535 x_tif_tbl(record_number).attribute8 := termnt_rec.attribute8;
1536 x_tif_tbl(record_number).attribute9 := termnt_rec.attribute9;
1537 x_tif_tbl(record_number).attribute10 := termnt_rec.attribute10;
1538 x_tif_tbl(record_number).attribute11 := termnt_rec.attribute11;
1539 x_tif_tbl(record_number).attribute12 := termnt_rec.attribute12;
1540 x_tif_tbl(record_number).attribute13 := termnt_rec.attribute13;
1541 x_tif_tbl(record_number).attribute14 := termnt_rec.attribute14;
1542 x_tif_tbl(record_number).attribute15 := termnt_rec.attribute15;
1543 x_tif_tbl(record_number).created_by := termnt_rec.created_by;
1544 x_tif_tbl(record_number).creation_date := termnt_rec.creation_date;
1545 x_tif_tbl(record_number).last_updated_by := termnt_rec.last_updated_by;
1546 x_tif_tbl(record_number).last_update_date := termnt_rec.last_update_date;
1547 x_tif_tbl(record_number).last_update_login := termnt_rec.last_update_login;
1548 x_tif_tbl(record_number).group_number := termnt_rec.group_number;
1549 record_number := record_number+1;
1550 END LOOP;
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 -- store SQL error message on message stack for caller
1555 OKC_API.set_message(p_app_name => g_app_name,
1556 p_msg_name => g_unexpected_error,
1557 p_token1 => g_sqlcode_token,
1558 p_token1_value => sqlcode,
1559 p_token2 => g_sqlerrm_token,
1560 p_token2_value => sqlerrm);
1561 l_msg_tbl(0).msg_text := 'change_status:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1562 log_messages(log_msg_flag => 'V',
1563 msg_tbl => l_msg_tbl );
1564 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1565 END change_status;
1566
1567
1568 -- Start of comments
1569 --
1570 -- Procedure Name : remove_duplicates
1571 -- Description : This procedure error out the duplicate records entered
1572 -- : for the quote and calls change_status to update in database
1573 -- Business Rules :
1574 -- Parameters : Input parameters : p_tif_tbl
1575 -- Version : 1.0
1576 -- History : 14-MAR-03 RABHUPAT Created
1577 -- End of comments
1578
1579 PROCEDURE remove_duplicates(p_api_version IN NUMBER,
1580 p_init_msg_list IN VARCHAR2,
1581 x_msg_count OUT NOCOPY NUMBER,
1582 x_msg_data OUT NOCOPY VARCHAR2,
1583 x_return_status OUT NOCOPY VARCHAR2,
1584 p_tif_tbl IN tif_tbl_type,
1585 x_tif_tbl OUT NOCOPY tif_tbl_type) IS
1586 l_tif_rec tif_rec_type;
1587 l_serial_number l_tif_rec.serial_number%TYPE;
1588 l_contract_id l_tif_rec.contract_id%TYPE;
1589 l_asset_id l_tif_rec.asset_id%TYPE;
1590 l_quote_type l_tif_rec.quote_type_code%TYPE;
1591 l_date_effective_from l_tif_rec.date_effective_from%TYPE;
1592 lp_tif_tbl tif_tbl_type;
1593 lx_tif_tbl tif_tbl_type;
1594 l_reason_type l_tif_rec.quote_reason_code%TYPE;
1595 l_asset_qty NUMBER;
1596 l_quote_qty NUMBER;
1597 message VARCHAR2(200);
1598 l_msg_tbl msg_tbl_type;
1599 /* this cursor retrives the total quantity for the asset*/
1600 CURSOR get_qty_csr(p_asset_number IN VARCHAR2) IS
1601 SELECT current_units quantity
1602 FROM OKX_ASSETS_V
1603 WHERE asset_number = p_asset_number;
1604 BEGIN
1605 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1606 IF(p_tif_tbl.COUNT>0)THEN
1607 -- updates the values in the tbl to database and retrives the data with status='WORKING'
1608 change_status(p_api_version => p_api_version,
1609 p_init_msg_list => OKC_API.G_FALSE,
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data,
1612 x_return_status => x_return_status,
1613 p_tif_tbl => p_tif_tbl,
1614 x_tif_tbl => lp_tif_tbl);
1615 END IF;
1616 l_contract_id := OKC_API.G_MISS_NUM;
1617 IF(lp_tif_tbl.COUNT>0 AND x_return_status = FND_API.G_RET_STS_SUCCESS)THEN
1618 -- loops through the interface records
1619 FOR term_rec IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
1620 LOOP
1621 IF((lp_tif_tbl(term_rec).contract_id = l_contract_id) AND
1622 (lp_tif_tbl(term_rec).asset_id = l_asset_id) AND
1623 (lp_tif_tbl(term_rec).date_effective_from = l_date_effective_from) AND
1624 (lp_tif_tbl(term_rec).quote_type_code = l_quote_type) AND
1625 (lp_tif_tbl(term_rec).quote_reason_code = l_reason_type) )THEN
1626 IF(lp_tif_tbl(term_rec).serial_number IS NULL OR lp_tif_tbl(term_rec).serial_number = OKC_API.G_MISS_CHAR) THEN
1627 IF(l_asset_qty<l_quote_qty+lp_tif_tbl(term_rec).units_to_terminate) THEN
1628 lp_tif_tbl(term_rec).status := 'ERROR';
1629 -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1630 OKC_API.set_message(p_app_name => g_app_name,
1631 p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1632 p_token1 => 'COL_NAME',
1633 p_token1_value => 'ASSET_NUMBER');
1634 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;
1635 ELSE
1636 l_quote_qty := l_quote_qty +lp_tif_tbl(term_rec).units_to_terminate;
1637 END IF;
1638 ELSIF(l_serial_number IS NOT NULL) THEN
1639 IF(l_serial_number = lp_tif_tbl(term_rec).serial_number)THEN
1640 lp_tif_tbl(term_rec).status := 'ERROR';
1641 -- if same serial_number is repeated in a group tthen ERROR out
1642 OKC_API.set_message(p_app_name => g_app_name,
1643 p_msg_name => 'OKL_AM_DUP_LINE',
1644 p_token1 => 'SERIAL_NUMBER',
1645 p_token1_value => lp_tif_tbl(term_rec).serial_number);
1646 message :='duplicate record for serialized asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1647 ELSIF(l_asset_qty<l_quote_qty+lp_tif_tbl(term_rec).units_to_terminate) THEN
1648 lp_tif_tbl(term_rec).status := 'ERROR';
1649 -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1650 OKC_API.set_message(p_app_name => g_app_name,
1651 p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1652 p_token1 => 'COL_NAME',
1653 p_token1_value => 'ASSET_NUMBER');
1654 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;
1655 ELSE
1656 l_quote_qty := l_quote_qty +lp_tif_tbl(term_rec).units_to_terminate;
1657 END IF;
1658 END IF;
1659 ELSE
1660 l_contract_id := lp_tif_tbl(term_rec).contract_id;
1661 l_asset_id := lp_tif_tbl(term_rec).asset_id;
1662 l_date_effective_from := lp_tif_tbl(term_rec).date_effective_from;
1663 l_quote_type := lp_tif_tbl(term_rec).quote_type_code;
1664 l_reason_type := lp_tif_tbl(term_rec).quote_reason_code;
1665 l_serial_number := lp_tif_tbl(term_rec).serial_number;
1666 l_quote_qty := lp_tif_tbl(term_rec).units_to_terminate;
1667 -- cursor to get the asset quantity
1668 FOR l_qty_csr IN get_qty_csr(p_asset_number => lp_tif_tbl(term_rec).asset_number)
1669 LOOP
1670 l_asset_qty := l_qty_csr.quantity;
1671 END LOOP;
1672 END IF;
1673 IF(lp_tif_tbl(term_rec).status = 'ERROR') THEN
1674 l_msg_tbl(0).msg_text := message;
1675 log_messages(log_msg_flag => 'V',
1676 p_transaction_number => lp_tif_tbl(term_rec).transaction_number,
1677 p_contract_number => lp_tif_tbl(term_rec).contract_number,
1678 p_asset_number => lp_tif_tbl(term_rec).asset_number,
1679 p_date_effective => lp_tif_tbl(term_rec).date_effective_from,
1680 p_quote_type => lp_tif_tbl(term_rec).quote_type_code,
1681 p_quote_reason => lp_tif_tbl(term_rec).quote_reason_code,
1682 msg_tbl => l_msg_tbl );
1683 END IF;
1684 END LOOP;
1685 -- updates the values in database and gets records with status='WORKING'
1686 change_status(p_api_version => p_api_version,
1687 p_init_msg_list => OKC_API.G_FALSE,
1688 x_msg_count => x_msg_count,
1689 x_msg_data => x_msg_data,
1690 x_return_status => x_return_status,
1691 p_tif_tbl => lp_tif_tbl,
1692 x_tif_tbl => lx_tif_tbl);
1693 x_tif_tbl := lx_tif_tbl;
1694 END IF;
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 -- store SQL error message on message stack for caller
1698 OKC_API.set_message(p_app_name => g_app_name,
1699 p_msg_name => g_unexpected_error,
1700 p_token1 => g_sqlcode_token,
1701 p_token1_value => sqlcode,
1702 p_token2 => g_sqlerrm_token,
1703 p_token2_value => sqlerrm);
1704 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1705 l_msg_tbl(0).msg_text := 'remove_duplicates:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1706 log_messages(log_msg_flag => 'V',
1707 msg_tbl => l_msg_tbl );
1708 END remove_duplicates;
1709
1710 -- Start of comments
1711 --
1712 -- Procedure Name : create_quote
1713 -- Description : This procedure calls the create_termination_quote API
1714 -- : and quote_line_details API
1715 -- Business Rules :
1716 -- Parameters : Input parameters : p_tif_tbl,p_quot_rec,p_assn_tbl,p_qpyv_tbl
1717 -- Version : 1.0
1718 -- History : 04-FEB-03 RABHUPAT Created
1719 -- : 14-MAR-03 RABHUPAT added p_qpyv_tbl parameter
1720 -- : 14-APR-03 RABHUPAT added code to accept the quotes
1721 -- : rmunjulu Bug 4239780 Should not set PreProceeds flag when accepting a quote
1722 -- End of comments
1723
1724 PROCEDURE create_quote(p_api_version IN NUMBER,
1725 p_init_msg_list IN VARCHAR2,
1726 x_msg_count OUT NOCOPY NUMBER,
1727 x_msg_data OUT NOCOPY VARCHAR2,
1728 x_return_status OUT NOCOPY VARCHAR2,
1729 p_tif_tbl IN tif_tbl_type,
1730 x_tif_tbl OUT NOCOPY tif_tbl_type,
1731 p_quot_rec IN quot_rec_type,
1732 p_assn_tbl IN assn_tbl_type,
1733 p_qpyv_tbl IN qpyv_tbl_type,
1734 p_batch_offset IN NUMBER,
1735 p_record_number IN NUMBER) IS
1736 l_return_status VARCHAR2(1);
1737 l_tif_rec tif_rec_type;
1738 l_serial_number l_tif_rec.serial_number%TYPE;
1739 l_quantity l_tif_rec.units_to_terminate%TYPE;
1740 l_installbase_id NUMBER;
1741 l_instance_id NUMBER;
1742 batch_offset NUMBER;
1743 record_number NUMBER;
1744 l_sys_date DATE;
1745 lp_quot_rec quot_rec_type;
1746 lx_quot_rec quot_rec_type;
1747 lp_assn_tbl assn_tbl_type;
1748 lx_assn_tbl assn_tbl_type;
1749 lx_tqlv_tbl tqlv_tbl_type;
1750 lp_qpyv_tbl qpyv_tbl_type;
1751 lp_qld_tbl qld_tbl_type;
1752 qdt_counter NUMBER;
1753 l_error_msg_rec ERROR_MESSAGE_TYPE;
1754 l_msg_tbl msg_tbl_type;
1755 l_qte_msg_count NUMBER := 0;
1756 l_det_msg_count NUMBER := 0;
1757 --added parameters for accepting quote logic
1758 l_accept_flag VARCHAR2(1) := 'Y';
1759 lp_term_rec quot_rec_type;
1760 lx_term_rec quot_rec_type;
1761 lx_err_msg VARCHAR2(2000);
1762
1763 /* this cursor retrives the instance ids for the asset with asset_id as input parameter.*/
1764 CURSOR get_instance_id_csr(p_ast_id IN NUMBER) IS
1765 SELECT oklv.id id
1766 FROM okc_k_lines_v oklv, okc_line_styles_v ols
1767 WHERE oklv.cle_id = p_ast_id
1768 AND oklv.lse_id=ols.id
1769 AND ols.lty_code = 'FREE_FORM2';
1770 /*this cursor is used to retrive the installbase id for an instance of the asset*/
1771 CURSOR get_installbase_id_csr(p_instance_id IN NUMBER) IS
1772 SELECT oklv.id id
1773 FROM okc_k_lines_v oklv, okc_line_styles_v ols
1774 WHERE oklv.cle_id = p_instance_id
1775 AND oklv.lse_id=ols.id
1776 AND ols.lty_code = 'INST_ITEM';
1777 /* this cursor retrives the serial_number and quantity for the instance of the asset*/
1778 CURSOR get_sno_qty_csr(p_installbase_id IN NUMBER) IS
1779 SELECT oiiv.serial_number sno,oiiv.quantity qty
1780 FROM okc_k_items_v okiv,okx_install_items_v oiiv
1781 WHERE okiv.cle_id = p_installbase_id
1782 AND okiv.object1_id1=oiiv.instance_id;
1783 BEGIN
1784 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1785 x_tif_tbl := p_tif_tbl;
1786 lp_quot_rec := p_quot_rec;
1787 lp_assn_tbl := p_assn_tbl;
1788 lp_qpyv_tbl := p_qpyv_tbl;
1789 batch_offset := p_batch_offset;
1790 qdt_counter := 0;
1791 /* sysdate for date processed */
1792 SELECT trunc(SYSDATE) INTO l_sys_date FROM DUAL;
1793 /* calls the create_termination_quote API and retrives the quote_id */
1794 OKL_AM_CREATE_QUOTE_PUB.create_terminate_quote(p_api_version => p_api_version
1795 ,p_init_msg_list => OKC_API.G_FALSE
1796 ,x_msg_data => x_msg_data
1797 ,x_msg_count => x_msg_count
1798 ,x_return_status => l_return_status
1799 ,p_quot_rec => lp_quot_rec
1800 ,p_assn_tbl => lp_assn_tbl
1801 ,p_qpyv_tbl => lp_qpyv_tbl
1802 ,x_quot_rec => lx_quot_rec
1803 ,x_assn_tbl => lx_assn_tbl
1804 ,x_tqlv_tbl => lx_tqlv_tbl);
1805 /* unwinding the messages from stack and keeping them in log*/
1806 --PAGARG 23-Feb-05 instead of calling accounting util call local procedure
1807 GET_ERROR_MESSAGE(l_error_msg_rec);
1808 IF (l_error_msg_rec.COUNT > 0) THEN
1809 l_qte_msg_count := 0;
1810 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1811 LOOP
1812 IF(length(l_error_msg_rec(m))>0) THEN
1813 l_msg_tbl(l_qte_msg_count).msg_text := l_error_msg_rec(m);
1814 l_qte_msg_count := l_qte_msg_count+1;
1815 END IF;
1816 END LOOP;
1817 END IF;
1818 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1819 LOOP
1820 --updates the interface records with quote_id,date_processed and status='PROCESSED'
1821 x_tif_tbl(batch_offset).qte_id := lx_quot_rec.id;
1822 x_tif_tbl(batch_offset).status := 'PROCESSED';
1823 x_tif_tbl(batch_offset).date_processed := l_sys_date;
1824 IF(x_tif_tbl(batch_offset).serial_number IS NOT NULL) THEN
1825 -- cursor to get instance id
1826 FOR l_instance_id_csr IN get_instance_id_csr(p_ast_id => p_tif_tbl(batch_offset).asset_id)
1827 LOOP
1828 l_instance_id := l_instance_id_csr.id;
1829 -- cursor to find installbase id
1830 FOR l_installbase_id_csr IN get_installbase_id_csr(p_instance_id => l_instance_id)
1831 LOOP
1832 l_installbase_id := l_installbase_id_csr.id;
1833 END LOOP;
1834 -- cursor to find serial no and quantiy
1835 FOR l_sno_qty_csr IN get_sno_qty_csr(p_installbase_id => l_installbase_id)
1836 LOOP
1837 l_serial_number := l_sno_qty_csr.sno;
1838 l_quantity := l_sno_qty_csr.qty;
1839 END LOOP;
1840 -- populates the tbl required for quote line details
1841 IF(l_serial_number = x_tif_tbl(batch_offset).serial_number) THEN
1842 lp_qld_tbl(qdt_counter).fin_line_id := p_tif_tbl(batch_offset).asset_id;
1843 lp_qld_tbl(qdt_counter).ib_line_id := l_installbase_id;
1844 lp_qld_tbl(qdt_counter).SELECT_YN := 'Y';
1845 EXIT;
1846 END IF;
1847 END LOOP;
1848 -- populates the table required for quote line details
1849 lp_qld_tbl(qdt_counter).qte_id := lx_quot_rec.id;
1850 lp_qld_tbl(qdt_counter).dnz_chr_id := p_tif_tbl(batch_offset).contract_id;
1851 lp_qld_tbl(qdt_counter).instance_quantity := p_tif_tbl(batch_offset).units_to_terminate;
1852 lp_qld_tbl(qdt_counter).qst_code := lx_quot_rec.qst_code;
1853 IF(lx_tqlv_tbl.COUNT>0) THEN
1854 FOR j IN lx_tqlv_tbl.FIRST..lx_tqlv_tbl.LAST LOOP
1855 IF(lp_qld_tbl(qdt_counter).fin_line_id = lx_tqlv_tbl(j).kle_id AND lx_tqlv_tbl(j).qlt_code = 'AMCFIA') THEN
1856 lp_qld_tbl(qdt_counter).tql_id := lx_tqlv_tbl(j).id;
1857 EXIT;
1858 END IF;
1859 END LOOP;
1860 END IF;
1861 qdt_counter := qdt_counter+1;
1862 -- added for non serialized
1863 ELSE
1864 l_msg_tbl(0).msg_text := 'quote created for transaction_number '||x_tif_tbl(batch_offset).transaction_number;
1865 log_messages(log_msg_flag => 'P',
1866 p_transaction_number => x_tif_tbl(batch_offset).transaction_number,
1867 p_contract_number => x_tif_tbl(batch_offset).contract_number,
1868 p_asset_number => x_tif_tbl(batch_offset).asset_number,
1869 p_date_effective => x_tif_tbl(batch_offset).date_effective_from,
1870 p_quote_type => x_tif_tbl(batch_offset).quote_type_code,
1871 p_quote_reason => x_tif_tbl(batch_offset).quote_reason_code,
1872 p_quote_number => lx_quot_rec.quote_number,
1873 msg_tbl => l_msg_tbl );
1874 END IF;
1875 IF(batch_offset = p_record_number) THEN
1876 IF(lp_qld_tbl.COUNT>0) THEN
1877 -- calls the quote_line_dtls API to create quote line details for the serialized assets
1878 OKL_AM_TERMNT_QUOTE_PUB.quote_line_dtls(p_api_version => p_api_version,
1879 p_init_msg_list => OKC_API.G_FALSE,
1880 x_return_status => l_return_status,
1881 x_msg_count => x_msg_count,
1882 x_msg_data => x_msg_data,
1883 p_qld_tbl => lp_qld_tbl);
1884 lp_qld_tbl.DELETE;
1885 /* unwinding the messages from stack and keeping them in log*/
1886 --PAGARG 23-Feb-05 instead of calling accounting util call local procedure
1887 GET_ERROR_MESSAGE(l_error_msg_rec);
1888 IF (l_error_msg_rec.COUNT > 0) THEN
1889 l_det_msg_count := 0;
1890 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1891 LOOP
1892 IF(length(l_error_msg_rec(m))>0) THEN
1893 l_msg_tbl(l_det_msg_count).msg_text := l_error_msg_rec(m);
1894 l_det_msg_count := l_det_msg_count+1;
1895 END IF;
1896 END LOOP;
1897 END IF;
1898 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1899 batch_offset := p_batch_offset;
1900 FOR i IN p_batch_offset..p_record_number LOOP
1901 IF(x_tif_tbl(i).serial_number IS NOT NULL) THEN
1902 --x_tif_tbl(i).status := 'ERROR';
1903 -- error in quote details
1904 l_msg_tbl(l_det_msg_count).msg_text := 'quote created, error in quote details for transaction_number '||x_tif_tbl(i).transaction_number;
1905 log_messages(log_msg_flag => 'E',
1906 p_transaction_number => x_tif_tbl(i).transaction_number,
1907 p_contract_number => x_tif_tbl(i).contract_number,
1908 p_asset_number => x_tif_tbl(i).asset_number,
1909 p_date_effective => x_tif_tbl(i).date_effective_from,
1910 p_quote_type => x_tif_tbl(i).quote_type_code,
1911 p_quote_reason => x_tif_tbl(i).quote_reason_code,
1912 p_quote_number => lx_quot_rec.quote_number,
1913 msg_tbl => l_msg_tbl );
1914 -- since quote details is failed quote cannot be accepted changing l_accept_flag='N'
1915 l_accept_flag := 'N';
1916 END IF;
1917 END LOOP;
1918 ELSE
1919 batch_offset := p_batch_offset;
1920 FOR i IN p_batch_offset..p_record_number LOOP
1921 IF(x_tif_tbl(i).serial_number IS NOT NULL) THEN
1922 l_msg_tbl(0).msg_text := 'quote and quote details created for transaction_number '||x_tif_tbl(i).transaction_number;
1923 log_messages(log_msg_flag => 'P',
1924 p_transaction_number => x_tif_tbl(i).transaction_number,
1925 p_contract_number => x_tif_tbl(i).contract_number,
1926 p_asset_number => x_tif_tbl(i).asset_number,
1927 p_date_effective => x_tif_tbl(i).date_effective_from,
1928 p_quote_type => x_tif_tbl(i).quote_type_code,
1929 p_quote_reason => x_tif_tbl(i).quote_reason_code,
1930 p_quote_number => lx_quot_rec.quote_number,
1931 msg_tbl => l_msg_tbl );
1932 END IF;
1933 END LOOP;
1934 END IF;
1935 END IF; -- if(lp_qld_tbl >0)
1936 EXIT;
1937 END IF;
1938 batch_offset := batch_offset+1;
1939 END LOOP;--for batch
1940 -- adding the code for accepting the quote
1941 IF(l_accept_flag = 'Y') THEN
1942 -- if any record which is part of this quote has auto_accept_yn='N'
1943 -- dont accept the quote
1944 FOR batch_offset IN p_batch_offset..p_record_number
1945 LOOP
1946 IF(x_tif_tbl(batch_offset).auto_accept_yn = 'N') THEN
1947 l_accept_flag := 'N';
1948 EXIT;
1949 END IF;
1950 END LOOP;
1951 -- accept the quote
1952 IF(l_accept_flag = 'Y') THEN
1953 lp_term_rec.id := lx_quot_rec.id;
1954 lp_term_rec.accepted_yn := 'Y';
1955 -- lp_term_rec.preproceeds_yn := 'Y'; -- rmunjulu Bug 4239780 Should not set PreProceeds flag
1956 lp_term_rec.date_effective_to := lx_quot_rec.date_effective_to;
1957 -- calling the API to accept quote
1958 OKL_AM_TERMNT_QUOTE_PUB.terminate_quote (p_api_version => p_api_version,
1959 p_init_msg_list => OKL_API.G_FALSE,
1960 x_return_status => l_return_status,
1961 x_msg_count => x_msg_count,
1962 x_msg_data => x_msg_data,
1963 p_term_rec => lp_term_rec,
1964 x_term_rec => lx_term_rec,
1965 x_err_msg => lx_err_msg);
1966 /* unwinding the messages from stack and keeping them in log*/
1967 --PAGARG 23-Feb-05 instead of calling accounting util call local procedure
1968 GET_ERROR_MESSAGE(l_error_msg_rec);
1969 IF (l_error_msg_rec.COUNT > 0) THEN
1970 l_det_msg_count := 0;
1971 FOR m IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1972 LOOP
1973 IF(length(l_error_msg_rec(m))>0) THEN
1974 l_msg_tbl(l_det_msg_count).msg_text := l_error_msg_rec(m);
1975 l_det_msg_count := l_det_msg_count+1;
1976 END IF;
1977 END LOOP;
1978 END IF;
1979 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1980 FOR i IN p_batch_offset..p_record_number
1981 LOOP
1982 -- error in accepting quote
1983 --l_msg_tbl(l_det_msg_count).msg_text := 'error in acccepting quote for transaction_number '||x_tif_tbl(i).transaction_number;
1984 log_messages(log_msg_flag => 'E',
1985 p_transaction_number => x_tif_tbl(i).transaction_number,
1986 p_contract_number => x_tif_tbl(i).contract_number,
1987 p_asset_number => x_tif_tbl(i).asset_number,
1988 p_date_effective => x_tif_tbl(i).date_effective_from,
1989 p_quote_type => x_tif_tbl(i).quote_type_code,
1990 p_quote_reason => x_tif_tbl(i).quote_reason_code,
1991 p_quote_number => lx_quot_rec.quote_number,
1992 msg_tbl => l_msg_tbl );
1993 END LOOP;
1994 END IF;
1995 ELSE
1996 -- quote can not be accepted as one line has auto_accept_yn = 'N'
1997 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';
1998 FOR i IN p_batch_offset..p_record_number
1999 LOOP
2000 -- error in accepting quote
2001 log_messages(log_msg_flag => 'E',
2002 p_transaction_number => x_tif_tbl(i).transaction_number,
2003 p_contract_number => x_tif_tbl(i).contract_number,
2004 p_asset_number => x_tif_tbl(i).asset_number,
2005 p_date_effective => x_tif_tbl(i).date_effective_from,
2006 p_quote_type => x_tif_tbl(i).quote_type_code,
2007 p_quote_reason => x_tif_tbl(i).quote_reason_code,
2008 p_quote_number => lx_quot_rec.quote_number,
2009 msg_tbl => l_msg_tbl );
2010 END LOOP;
2011 END IF;
2012 END IF;
2013 -- end of auto accept logic
2014 ELSE
2015 FOR batch_offset IN p_batch_offset..p_record_number LOOP
2016 -- updates status of all rows for which quote is not created.
2017 x_tif_tbl(batch_offset).status := 'ERROR';
2018 l_msg_tbl(l_qte_msg_count).msg_text := 'quote not created for transaction_number '||x_tif_tbl(batch_offset).transaction_number;
2019 log_messages(log_msg_flag => 'V',
2020 p_transaction_number => x_tif_tbl(batch_offset).transaction_number,
2021 p_contract_number => x_tif_tbl(batch_offset).contract_number,
2022 p_asset_number => x_tif_tbl(batch_offset).asset_number,
2023 p_date_effective => x_tif_tbl(batch_offset).date_effective_from,
2024 p_quote_type => x_tif_tbl(batch_offset).quote_type_code,
2025 p_quote_reason => x_tif_tbl(batch_offset).quote_reason_code,
2026 msg_tbl => l_msg_tbl );
2027 END LOOP;
2028 END IF;
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 -- store SQL error message on message stack for caller
2032 OKC_API.set_message(p_app_name => g_app_name,
2033 p_msg_name => g_unexpected_error,
2034 p_token1 => g_sqlcode_token,
2035 p_token1_value => sqlcode,
2036 p_token2 => g_sqlerrm_token,
2037 p_token2_value => sqlerrm);
2038 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2039 l_msg_tbl(0).msg_text := 'create_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2040 log_messages(log_msg_flag => 'V',
2041 msg_tbl => l_msg_tbl );
2042 END create_quote;
2043
2044 -- Start of comments
2045 --
2046 -- Procedure Name : populate_quote
2047 -- Description : This procedure populates the data required for calling
2048 -- : create_termination_quote API and quote_line_details API
2049 -- Business Rules :
2050 -- Parameters : Input parameters : p_tif_tbl
2051 -- Version : 1.0
2052 -- History : 04-FEB-03 RABHUPAT Created
2053 -- : 14-MAR-03 RABHUPAT added another parameter to create_quote
2054 -- End of comments
2055
2056 PROCEDURE populate_quote(
2057 p_api_version IN NUMBER,
2058 p_init_msg_list IN VARCHAR2,
2059 x_msg_count OUT NOCOPY NUMBER,
2060 x_msg_data OUT NOCOPY VARCHAR2,
2061 x_return_status OUT NOCOPY VARCHAR2,
2062 p_tif_tbl IN tif_tbl_type,
2063 x_tif_tbl OUT NOCOPY tif_tbl_type,
2064 p_group_number IN NUMBER
2065 )IS
2066 l_tif_rec tif_rec_type;
2067 lp_tif_tbl tif_tbl_type;
2068 lx_tif_tbl tif_tbl_type;
2069 lp_quot_rec quot_rec_type;
2070 lx_quot_rec quot_rec_type;
2071 lp_assn_tbl assn_tbl_type;
2072 lx_assn_tbl assn_tbl_type;
2073 lx_tqlv_tbl tqlv_tbl_type;
2074 lp_qpyv_tbl qpyv_tbl_type;
2075 lp_qld_tbl qld_tbl_type;
2076 batch_offset NUMBER;
2077 group_number NUMBER;
2078 record_number NUMBER;
2079 quote_lines NUMBER;
2080 qdt_counter NUMBER;
2081 l_installbase_id NUMBER;
2082 l_instance_id NUMBER;
2083 l_quote_success NUMBER;
2084 i NUMBER;
2085 l_serial_number l_tif_rec.serial_number%TYPE;
2086 l_quantity l_tif_rec.units_to_terminate%TYPE;
2087 l_contract_number l_tif_rec.contract_number%TYPE;
2088 l_quote_type l_tif_rec.quote_type_code%TYPE;
2089 l_quote_reason l_tif_rec.quote_reason_code%TYPE;
2090 l_date_effective_from l_tif_rec.date_effective_from%TYPE;
2091 l_msg_tbl msg_tbl_type;
2092
2093 BEGIN
2094 record_number := 0;
2095 quote_lines := 0;
2096 batch_offset := 0;
2097 group_number := p_group_number;
2098 lp_tif_tbl := p_tif_tbl;
2099 lx_tif_tbl := p_tif_tbl;
2100 l_contract_number := OKC_API.G_MISS_CHAR;
2101 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2102 IF(p_tif_tbl.COUNT>0) THEN
2103 FOR record_number IN p_tif_tbl.FIRST..p_tif_tbl.LAST LOOP
2104 -- checking the condition to separate the batches for quote creation
2105 IF(((l_contract_number <> p_tif_tbl(record_number).contract_number) OR
2106 (l_quote_type <> p_tif_tbl(record_number).quote_type_code) OR
2107 (l_quote_reason <> p_tif_tbl(record_number).quote_reason_code) OR
2108 (l_date_effective_from <> p_tif_tbl(record_number).date_effective_from))
2109 AND (record_number <>p_tif_tbl.FIRST)) THEN
2110 -- populates the lp_quot_rec and calls create_quote
2111 lp_quot_rec.khr_id := p_tif_tbl(record_number-1).contract_id;
2112 lp_quot_rec.date_effective_from := p_tif_tbl(record_number-1).date_effective_from;
2113 lp_quot_rec.org_id := p_tif_tbl(record_number-1).org_id;
2114 lp_quot_rec.qtp_code := p_tif_tbl(record_number-1).quote_type_code;
2115 lp_quot_rec.qrs_code := p_tif_tbl(record_number-1).quote_reason_code;
2116 create_quote(
2117 p_api_version => p_api_version
2118 ,p_init_msg_list => OKC_API.G_FALSE
2119 ,x_msg_count => x_msg_count
2120 ,x_msg_data => x_msg_data
2121 ,x_return_status => x_return_status
2122 ,p_tif_tbl => lp_tif_tbl
2123 ,x_tif_tbl => lx_tif_tbl
2124 ,p_quot_rec => lp_quot_rec
2125 ,p_assn_tbl => lp_assn_tbl
2126 ,p_qpyv_tbl => lp_qpyv_tbl
2127 ,p_batch_offset => batch_offset
2128 ,p_record_number => record_number-1);
2129 lp_assn_tbl.DELETE;
2130 lp_tif_tbl.DELETE;
2131 lp_tif_tbl := lx_tif_tbl;
2132 lx_tif_tbl.DELETE;
2133 FOR i IN batch_offset..record_number-1 LOOP
2134 lp_tif_tbl(i).group_number := group_number;
2135 END LOOP;
2136 group_number := group_number+1;
2137 batch_offset := record_number;
2138 quote_lines := 0;
2139 END IF;--not equal condition
2140 -- copy p_tif_tbl record in to local variables
2141 l_contract_number := p_tif_tbl(record_number).contract_number;
2142 l_quote_type := p_tif_tbl(record_number).quote_type_code;
2143 l_quote_reason := p_tif_tbl(record_number).quote_reason_code;
2144 l_date_effective_from := p_tif_tbl(record_number).date_effective_from;
2145 --assign values to assn_tbl;
2146 IF(lp_assn_tbl.COUNT>0) THEN
2147 IF(p_tif_tbl(record_number).asset_id = lp_assn_tbl(quote_lines).p_asset_id) THEN
2148 lp_assn_tbl(quote_lines).p_quote_qty := lp_assn_tbl(quote_lines).p_quote_qty+p_tif_tbl(record_number).units_to_terminate;
2149 ELSE
2150 quote_lines := quote_lines+1;
2151 lp_assn_tbl(quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
2152 lp_assn_tbl(quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
2153 lp_assn_tbl(quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
2154 END IF;
2155 ELSE
2156 --assign values to assn_tbl
2157 lp_assn_tbl(quote_lines).p_asset_id := p_tif_tbl(record_number).asset_id;
2158 lp_assn_tbl(quote_lines).p_asset_number := p_tif_tbl(record_number).asset_number;
2159 lp_assn_tbl(quote_lines).p_quote_qty := p_tif_tbl(record_number).units_to_terminate;
2160 END IF;
2161 IF(record_number = p_tif_tbl.LAST) THEN
2162 -- populates the lp_quot_rec and calls create_quote for the last record
2163 lp_quot_rec.khr_id := p_tif_tbl(record_number).contract_id;
2164 lp_quot_rec.date_effective_from := p_tif_tbl(record_number).date_effective_from;
2165 lp_quot_rec.org_id := p_tif_tbl(record_number).org_id;
2166 lp_quot_rec.qtp_code := p_tif_tbl(record_number).quote_type_code;
2167 lp_quot_rec.qrs_code := p_tif_tbl(record_number).quote_reason_code;
2168 create_quote(p_api_version => p_api_version
2169 ,p_init_msg_list => OKC_API.G_FALSE
2170 ,x_msg_count => x_msg_count
2171 ,x_msg_data => x_msg_data
2172 ,x_return_status => x_return_status
2173 ,p_tif_tbl => lp_tif_tbl
2174 ,x_tif_tbl => lx_tif_tbl
2175 ,p_quot_rec => lp_quot_rec
2176 ,p_assn_tbl => lp_assn_tbl
2177 ,p_qpyv_tbl => lp_qpyv_tbl
2178 ,p_batch_offset => batch_offset
2179 ,p_record_number => record_number);
2180 FOR i IN batch_offset..record_number LOOP
2181 lx_tif_tbl(i).group_number := group_number;
2182 END LOOP;
2183 END IF;
2184 END LOOP;--p_tif_tbl.FIRST..p_tif_tbl.LAST
2185 x_tif_tbl.DELETE;
2186 x_tif_tbl := lx_tif_tbl;
2187 IF (x_tif_tbl.COUNT>0) THEN
2188 -- updates the INTERFACE table
2189 FOR record_number IN x_tif_tbl.FIRST..x_tif_tbl.LAST LOOP
2190 update_row(p_api_version => p_api_version
2191 ,p_init_msg_list => OKC_API.G_FALSE
2192 ,x_msg_data => x_msg_data
2193 ,x_msg_count => x_msg_count
2194 ,x_return_status => x_return_status
2195 ,p_tif_rec => x_tif_tbl(record_number));
2196 -- Save message from stack into transaction message table
2197 OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
2198 p_trx_id => lx_tif_tbl(record_number).transaction_number,
2199 x_return_status => x_return_status);
2200 END LOOP;
2201 END IF;
2202 END IF;--p_tif_tbl.COUNT>0 END IF; -- To check if table contains any record.
2203 EXCEPTION
2204 WHEN OTHERS THEN
2205 -- store SQL error message on message stack for caller
2206 OKC_API.set_message(p_app_name => g_app_name,
2207 p_msg_name => g_unexpected_error,
2208 p_token1 => g_sqlcode_token,
2209 p_token1_value => sqlcode,
2210 p_token2 => g_sqlerrm_token,
2211 p_token2_value => sqlerrm);
2212 l_msg_tbl(0).msg_text := 'populate_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2213 log_messages(log_msg_flag => 'V',
2214 msg_tbl => l_msg_tbl );
2215 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2216 END populate_quote;
2217
2218 /* RABHUPAT added party logic for termination interface logic, only recipient role can be overwritten not others
2219 and creates quote for each party record defaults the quote_role_code to 'RECIPIENT'*/
2220
2221 -- Start of comments
2222 --
2223 -- Procedure Name : validate_party
2224 -- Description : This procedure validates the party table fields
2225 -- :populates the data required for calling
2226 -- : create_termination_quote API
2227 -- Business Rules :
2228 -- Parameters : Input parameters : p_tip_tbl,p_tif_tbl
2229 -- Version : 1.0
2230 -- History : 14-MAR-03 RABHUPAT Created
2231 -- End of comments
2232
2233 PROCEDURE validate_party(p_api_version IN NUMBER,
2234 p_init_msg_list IN VARCHAR2,
2235 x_msg_count OUT NOCOPY NUMBER,
2236 x_msg_data OUT NOCOPY VARCHAR2,
2237 x_return_status OUT NOCOPY VARCHAR2,
2238 p_tif_tbl IN tif_tbl_type,
2239 x_tif_tbl OUT NOCOPY tif_tbl_type,
2240 p_tip_tbl IN tip_tbl_type,
2241 x_tip_tbl OUT NOCOPY tip_tbl_type) IS
2242
2243 -- cursor to retrive contract_id from OKL_TERMNT_INTERFACE joined by transaction_number
2244 CURSOR get_contract_id_csr(p_transaction_number IN NUMBER) IS
2245 SELECT contract_id,status
2246 FROM OKL_TERMNT_INTERFACE
2247 WHERE transaction_number = p_transaction_number;
2248
2249 -- cursor to select the party details using contract_id and quote_role_code
2250 --Fixed Bug # 5484903
2251 CURSOR quote_party_details_csr(p_chr_id IN NUMBER, p_role_code IN VARCHAR2) IS
2252 SELECT id,object1_id1,object1_id2,jtot_object1_code
2253 FROM OKC_K_PARTY_ROLES_B
2254 WHERE dnz_chr_id = p_chr_id
2255 AND chr_id = dnz_chr_id
2256 AND rle_code = p_role_code;
2257 -- cursor to select the party_name if party is VENDOR
2258 CURSOR get_vendor_info_csr(p_object1_id1 IN VARCHAR2, p_object1_id2 IN VARCHAR2) IS
2259 SELECT party_number,name
2260 FROM OKX_VENDORS_V
2261 WHERE id1 = p_object1_id1 AND id2 = p_object1_id2;
2262
2263 -- cursor to select the party_name if party is LESSEE
2264 CURSOR get_lessee_info_csr(p_object1_id1 IN VARCHAR2, p_object1_id2 IN VARCHAR2) IS
2265 SELECT party_name,party_number,email_address
2266 FROM HZ_PARTIES
2267 WHERE ((party_id = p_object1_id1) AND party_type IN ( 'PERSON','ORGANIZATION'));
2268
2269 l_count NUMBER;
2270 l_dup_flag VARCHAR2(1);
2271 l_transaction_number OKL_TERMNT_INTF_PTY.transaction_number%TYPE;
2272 l_chr_id NUMBER;
2273 lp_tip_tbl tip_tbl_type;
2274 lx_tip_tbl tip_tbl_type;
2275 l_qpy_id OKL_TERMNT_INTF_PTY.qpy_id%TYPE;
2276 l_party_object_id1 OKL_TERMNT_INTF_PTY.party_object_id1%TYPE;
2277 l_party_object_id2 OKL_TERMNT_INTF_PTY.party_object_id2%TYPE;
2278 l_party_object_code OKL_TERMNT_INTF_PTY.party_object_code%TYPE;
2279 l_contract_party_number OKL_TERMNT_INTF_PTY.contract_party_number%TYPE;
2280 l_contract_party_id OKL_TERMNT_INTF_PTY.contract_party_id%TYPE;
2281 l_contract_party_name OKL_TERMNT_INTF_PTY.contract_party_name%TYPE;
2282 l_email_address OKL_TERMNT_INTF_PTY.email_address%TYPE;
2283 message VARCHAR2(200);
2284 l_msg_tbl msg_tbl_type;
2285 BEGIN
2286 x_tif_tbl := p_tif_tbl;
2287 lp_tip_tbl := p_tip_tbl;
2288 lx_tip_tbl := p_tip_tbl;
2289 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2290 l_count := 0;
2291 IF(lp_tip_tbl.COUNT>0) THEN
2292 -- sets the session variables
2293 FOR record_number IN lp_tip_tbl.FIRST..lp_tip_tbl.LAST LOOP
2294 -- populate WHO columns in party table
2295 lx_tip_tbl(record_number).CREATION_DATE := SYSDATE;
2296 lx_tip_tbl(record_number).CREATED_BY := FND_GLOBAL.USER_ID;
2297 l_dup_flag := 'N';
2298 IF(l_transaction_number = lp_tip_tbl(record_number).transaction_number) THEN
2299 lx_tip_tbl(record_number).status := 'ERROR';
2300 l_dup_flag := 'Y';
2301 --transaction_number repeated, ERROR out only the party record not the INTERFACE record
2302 -- striked off in the DLD
2303 OKC_API.set_message(p_app_name => g_app_name,
2304 p_msg_name => 'OKL_AM_DUP_TRAN_NUM',
2305 p_token1 => 'TRANSACTION_NUMBER',
2306 p_token1_value => lx_tip_tbl(record_number).transaction_number);
2307 message :='transaction_number '||lx_tip_tbl(record_number).transaction_number||' repeated in party table';
2308 ELSIF(lp_tip_tbl(record_number).contract_party_role IS NULL) THEN
2309 lx_tip_tbl(record_number).status := 'ERROR';
2310 -- contract_party_role should be entered
2311 OKC_API.set_message(p_app_name => g_app_name,
2312 p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2313 p_token1 => 'COL_NAME',
2314 p_token1_value => 'CONTRACT PARTY ROLE');
2315 message :='contract party role should be entered for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2316 ELSE
2317 lx_tip_tbl(record_number).status := 'WORKING';
2318 END IF;
2319 IF(lx_tip_tbl(record_number).status <> 'ERROR') THEN
2320 l_transaction_number := lp_tip_tbl(record_number).transaction_number;
2321 l_chr_id := NULL;
2322 -- fetches the associated contract_id from INTERFACE table based on transaction_number
2323 FOR interface_dtls IN get_contract_id_csr(l_transaction_number) LOOP
2324 IF(interface_dtls.status <> 'WORKING') THEN
2325 lx_tip_tbl(record_number).status := 'ERROR';
2326 -- corresponding transaction_number in INTERFACE table is ERRORED out
2327 -- striked off in the DLD
2328 OKC_API.set_message(p_app_name => g_app_name,
2329 p_msg_name => 'OKL_AM_ERR_INTERFACE',
2330 p_token1 => 'TRANSACTION_NUMBER',
2331 p_token1_value => lx_tip_tbl(record_number).transaction_number);
2332 message :='transaction_number in OKL_TERMNT_INTERFACE is ERROR for transaction number '||lx_tip_tbl(record_number).transaction_number;
2333 ELSE
2334 l_chr_id := interface_dtls.contract_id;
2335 END IF;
2336 END LOOP;
2337 IF(l_chr_id IS NULL OR l_chr_id = OKC_API.G_MISS_NUM)THEN
2338 lx_tip_tbl(record_number).status := 'ERROR';
2339 -- wrong transaction number
2340 OKC_API.set_message(p_app_name => g_app_name,
2341 p_msg_name => 'OKL_AM_JOIN_MIS_MATCH',
2342 p_token1 => 'TRANSACTION_NUMBER',
2343 p_token1_value => lx_tip_tbl(record_number).transaction_number);
2344 message :='wrong transaction number '||lx_tip_tbl(record_number).transaction_number;
2345 END IF;
2346 END IF;
2347 -- if no role specified default it to RECIPIENT
2348 IF(lx_tip_tbl(record_number).status <> 'ERROR') THEN
2349 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
2350 lx_tip_tbl(record_number).quote_role_code := 'RECIPIENT';
2351 END IF;
2352 IF(lx_tip_tbl(record_number).quote_role_code = 'RECIPIENT') THEN
2353 -- retrive the quote_party information
2354 l_count := 0;
2355 l_qpy_id := NULL;
2356 l_party_object_id1 := NULL;
2357 l_party_object_id2 := NULL;
2358 -- retrive quote party roles
2359 FOR object_ids IN quote_party_details_csr(l_chr_id,lp_tip_tbl(record_number).contract_party_role) LOOP
2360 l_qpy_id := object_ids.id;
2361 l_party_object_id1 := object_ids.object1_id1;
2362 l_party_object_id2 := object_ids.object1_id2;
2363 l_party_object_code := object_ids.jtot_object1_code;
2364 l_count := l_count+1;
2365 l_contract_party_number := NULL;
2366 /* If party is vendor */
2367 IF(l_party_object_code = 'OKX_VENDOR') THEN
2368 FOR vendor_dtls IN get_vendor_info_csr(l_party_object_id1, l_party_object_id2) LOOP
2369 l_contract_party_number := vendor_dtls.party_number;
2370 l_contract_party_id := l_party_object_id1;
2371 l_contract_party_name := vendor_dtls.name;
2372 -- populate all party details required
2373 IF(lp_tip_tbl(record_number).contract_party_number = l_contract_party_number) THEN
2374 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2375 lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
2376 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2377 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2378 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2379 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2380 l_count := -1;
2381 EXIT;
2382 ELSIF(lp_tip_tbl(record_number).contract_party_id = l_contract_party_id) THEN
2383 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2384 lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
2385 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2386 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2387 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2388 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2389 l_count := -1;
2390 EXIT;
2391 END IF;
2392 END LOOP;
2393 /* If party is Lessee */
2394 ELSIF(l_party_object_code = 'OKX_PARTY') THEN
2395 FOR lessee_dtls IN get_lessee_info_csr(l_party_object_id1, l_party_object_id2) LOOP
2396 l_contract_party_number := lessee_dtls.party_number;
2397 l_contract_party_name := lessee_dtls.party_name;
2398 l_email_address := lessee_dtls.email_address;
2399 l_contract_party_id := l_party_object_id1;
2400 -- populate all party details required
2401 IF(lp_tip_tbl(record_number).contract_party_number = l_contract_party_number) THEN
2402 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2403 lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
2404 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2405 lx_tip_tbl(record_number).email_address := l_email_address;
2406 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2407 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2408 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2409 l_count := -1;
2410 EXIT;
2411 ELSIF(lp_tip_tbl(record_number).contract_party_id = l_contract_party_id) THEN
2412 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2413 lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
2414 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2415 lx_tip_tbl(record_number).email_address := l_email_address;
2416 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2417 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2418 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2419 l_count := -1;
2420 EXIT;
2421 END IF;
2422 END LOOP;
2423 END IF;
2424 IF(l_count = -1) THEN
2425 EXIT;
2426 END IF;
2427 END LOOP;
2428 IF(l_count = 0) THEN
2429 lx_tip_tbl(record_number). status := 'ERROR';
2430 -- neither lessee nor vendor
2431 OKC_API.set_message(p_app_name => g_app_name,
2432 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2433 p_token1 => 'COL_NAME',
2434 p_token1_value => 'CONTRACT PARTY ROLE');
2435 message :='quote party should be a lessee or vendor for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2436 ELSIF(l_count>1) THEN
2437 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
2438 (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
2439 lx_tip_tbl(record_number). status := 'ERROR';
2440 -- neither contract_party_number nor contract_party_id are entered
2441 OKC_API.set_message(p_app_name => g_app_name,
2442 p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2443 p_token1 => 'COL_NAME',
2444 p_token1_value => 'CONTRACT_PARTY_NAME OR PARTY_ID');
2445 message :='contract_party_number or contract_party_id should be entered for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2446 ELSIF(lp_tip_tbl(record_number).contract_party_number IS NOT NULL) THEN
2447 IF(l_contract_party_number <> lp_tip_tbl(record_number).contract_party_number) THEN
2448 lx_tip_tbl(record_number). status := 'ERROR';
2449 -- contract party number entered is not valid
2450 OKC_API.set_message(p_app_name => g_app_name,
2451 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2452 p_token1 => 'COL_NAME',
2453 p_token1_value => 'CONTRACT_PARTY_NUMBER');
2454 message :='contract party number entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2455 END IF;
2456 ELSIF(lp_tip_tbl(record_number).contract_party_id IS NOT NULL) THEN
2457 IF(l_contract_party_id <> lp_tip_tbl(record_number).contract_party_id) THEN
2458 lx_tip_tbl(record_number). status := 'ERROR';
2459 -- contract party id entered is not valid
2460 OKC_API.set_message(p_app_name => g_app_name,
2461 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2462 p_token1 => 'COL_NAME',
2463 p_token1_value => 'CONTRACT_PARTY_NUMBER');
2464 message :='contract party id entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2465 END IF;
2466 END IF;
2467 ELSIF(l_count = 1) THEN
2468 -- if only one party is associated then assign those parties even the user not entered
2469 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
2470 (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
2471 lx_tip_tbl(record_number).qpy_id := l_qpy_id;
2472 lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
2473 lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
2474 lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
2475 lx_tip_tbl(record_number).email_address := l_email_address;
2476 lx_tip_tbl(record_number).party_object_id1 := l_party_object_id1;
2477 lx_tip_tbl(record_number).party_object_id2 := l_party_object_id2;
2478 lx_tip_tbl(record_number).party_object_code := l_party_object_code;
2479 ELSIF(lp_tip_tbl(record_number).contract_party_number IS NOT NULL) THEN
2480 IF(l_contract_party_number <> lp_tip_tbl(record_number).contract_party_number) THEN
2481 lx_tip_tbl(record_number). status := 'ERROR';
2482 -- contract party number entered is not valid
2483 OKC_API.set_message(p_app_name => g_app_name,
2484 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2485 p_token1 => 'COL_NAME',
2486 p_token1_value => 'CONTRACT_PARTY_NUMBER');
2487 message :='contract party number entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2488 END IF;
2489 ELSIF(lp_tip_tbl(record_number).contract_party_id IS NOT NULL) THEN
2490 IF(l_contract_party_id <> lp_tip_tbl(record_number).contract_party_id) THEN
2491 lx_tip_tbl(record_number). status := 'ERROR';
2492 -- contract party id entered is not valid
2493 OKC_API.set_message(p_app_name => g_app_name,
2494 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2495 p_token1 => 'COL_NAME',
2496 p_token1_value => 'CONTRACT_PARTY_NUMBER');
2497 message :='contract party id entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2498 END IF;
2499 END IF;
2500 END IF;
2501 ELSE
2502 lx_tip_tbl(record_number).status := 'ERROR';
2503 -- not recipient
2504 OKC_API.set_message(p_app_name => g_app_name,
2505 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2506 p_token1 => 'COL_NAME',
2507 p_token1_value => 'QUOTE_ROLE_CODE');
2508 message :='quote party type allowed is only RECIPIENT for transaction_number'||lx_tip_tbl(record_number).transaction_number;
2509 END IF;
2510 END IF;-- for cursor object_ids
2511 -- if party record is error make related record in INTERFACE table also ERROR, but not when duplicate record found
2512 IF(lx_tip_tbl(record_number).status = 'ERROR' AND l_dup_flag = 'N') THEN
2513 IF(x_tif_tbl.COUNT>0) THEN
2514 FOR term_rec IN x_tif_tbl.FIRST..x_tif_tbl.LAST
2515 LOOP
2516 IF(x_tif_tbl(term_rec).transaction_number = lx_tip_tbl(record_number).transaction_number) THEN
2517 x_tif_tbl(term_rec).status := 'ERROR';
2518 l_msg_tbl(0).msg_text := message;
2519 log_messages(log_msg_flag => 'V',
2520 p_transaction_number => x_tif_tbl(term_rec).transaction_number,
2521 p_contract_number => x_tif_tbl(term_rec).contract_number,
2522 p_asset_number => x_tif_tbl(term_rec).asset_number,
2523 p_date_effective => x_tif_tbl(term_rec).date_effective_from,
2524 p_quote_type => x_tif_tbl(term_rec).quote_type_code,
2525 p_quote_reason => x_tif_tbl(term_rec).quote_reason_code,
2526 msg_tbl => l_msg_tbl );
2527
2528 EXIT;
2529 END IF;
2530 END LOOP;
2531 END IF;
2532 END IF;
2533 END LOOP;
2534 END IF;
2535 x_tip_tbl := lx_tip_tbl;
2536 EXCEPTION
2537 WHEN OTHERS THEN
2538 -- store SQL error message on message stack for caller
2539 OKC_API.set_message(p_app_name => g_app_name,
2540 p_msg_name => g_unexpected_error,
2541 p_token1 => g_sqlcode_token,
2542 p_token1_value => sqlcode,
2543 p_token2 => g_sqlerrm_token,
2544 p_token2_value => sqlerrm);
2545 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2546 l_msg_tbl(0).msg_text := 'validate_party:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2547 log_messages(log_msg_flag => 'V',
2548 msg_tbl => l_msg_tbl );
2549 END validate_party;
2550
2551 -- Start of comments
2552 --
2553 -- Procedure Name : update_party
2554 -- Description : This procedure updates the party table fields
2555 -- Business Rules :
2556 -- Parameters : Input parameters : p_tip_tbl,p_tif_tbl
2557 -- Version : 1.0
2558 -- History : 14-MAR-03 RABHUPAT Created
2559 -- End of comments
2560
2561 PROCEDURE update_party(p_api_version IN NUMBER,
2562 p_init_msg_list IN VARCHAR2,
2563 x_msg_count OUT NOCOPY NUMBER,
2564 x_msg_data OUT NOCOPY VARCHAR2,
2565 x_return_status OUT NOCOPY VARCHAR2,
2566 p_tip_rec IN tip_rec_type) IS
2567 l_api_version CONSTANT NUMBER := p_api_version;
2568 l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
2569 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2570 l_msg_tbl msg_tbl_type;
2571 BEGIN
2572 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2573 --Check API version, initialize message list and create savepoint.
2574 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2575 G_PKG_NAME,
2576 p_init_msg_list,
2577 l_api_version,
2578 p_api_version,
2579 '_PVT',
2580 x_return_status);
2581 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2582 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2583 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2584 RAISE OKC_API.G_EXCEPTION_ERROR;
2585 END IF;
2586 -- updates the party table
2587 UPDATE OKL_TERMNT_INTF_PTY
2588 SET
2589 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_ID = p_tip_rec.contract_party_id,
2590 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_ROLE = p_tip_rec.contract_party_role,
2591 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_NAME = p_tip_rec.contract_party_name,
2592 OKL_TERMNT_INTF_PTY.CONTRACT_PARTY_NUMBER = p_tip_rec.contract_party_number,
2593 OKL_TERMNT_INTF_PTY.PARTY_OBJECT_CODE = p_tip_rec.party_object_code,
2594 OKL_TERMNT_INTF_PTY.PARTY_OBJECT_ID1 = p_tip_rec.party_object_id1,
2595 OKL_TERMNT_INTF_PTY.PARTY_OBJECT_ID2 = p_tip_rec.party_object_id2,
2596 OKL_TERMNT_INTF_PTY.EMAIL_ADDRESS = p_tip_rec.email_address,
2597 OKL_TERMNT_INTF_PTY.ALLOCATION_PERCENTAGE = p_tip_rec.allocation_percentage,
2598 OKL_TERMNT_INTF_PTY.DELAY_DAYS = p_tip_rec.delay_days,
2599 OKL_TERMNT_INTF_PTY.QPY_ID = p_tip_rec.qpy_id,
2600 OKL_TERMNT_INTF_PTY.TRANSACTION_NUMBER = p_tip_rec.transaction_number,
2601 OKL_TERMNT_INTF_PTY.STATUS = p_tip_rec.status,
2602 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),
2603 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),
2604 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),
2605 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),
2606 OKL_TERMNT_INTF_PTY.ATTRIBUTE_CATEGORY = p_tip_rec.attribute_category,
2607 OKL_TERMNT_INTF_PTY.ATTRIBUTE1 = p_tip_rec.attribute1,
2608 OKL_TERMNT_INTF_PTY.ATTRIBUTE2 = p_tip_rec.attribute2,
2609 OKL_TERMNT_INTF_PTY.ATTRIBUTE3 = p_tip_rec.attribute3,
2610 OKL_TERMNT_INTF_PTY.ATTRIBUTE4 = p_tip_rec.attribute4,
2611 OKL_TERMNT_INTF_PTY.ATTRIBUTE5 = p_tip_rec.attribute5,
2612 OKL_TERMNT_INTF_PTY.ATTRIBUTE6 = p_tip_rec.attribute6,
2613 OKL_TERMNT_INTF_PTY.ATTRIBUTE7 = p_tip_rec.attribute7,
2614 OKL_TERMNT_INTF_PTY.ATTRIBUTE8 = p_tip_rec.attribute8,
2615 OKL_TERMNT_INTF_PTY.ATTRIBUTE9 = p_tip_rec.attribute9,
2616 OKL_TERMNT_INTF_PTY.ATTRIBUTE10 = p_tip_rec.attribute10,
2617 OKL_TERMNT_INTF_PTY.ATTRIBUTE11 = p_tip_rec.attribute11,
2618 OKL_TERMNT_INTF_PTY.ATTRIBUTE12 = p_tip_rec.attribute12,
2619 OKL_TERMNT_INTF_PTY.ATTRIBUTE13 = p_tip_rec.attribute13,
2620 OKL_TERMNT_INTF_PTY.ATTRIBUTE14 = p_tip_rec.attribute14,
2621 OKL_TERMNT_INTF_PTY.ATTRIBUTE15 = p_tip_rec.attribute15,
2622 OKL_TERMNT_INTF_PTY.CREATED_BY = p_tip_rec.created_by,
2623 OKL_TERMNT_INTF_PTY.CREATION_DATE = p_tip_rec.creation_date,
2624 OKL_TERMNT_INTF_PTY.LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2625 OKL_TERMNT_INTF_PTY.LAST_UPDATE_DATE = SYSDATE,
2626 OKL_TERMNT_INTF_PTY.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2627 OKL_TERMNT_INTF_PTY.QUOTE_ROLE_CODE = p_tip_rec.quote_role_code
2628 WHERE
2629 OKL_TERMNT_INTF_PTY.ROWID =p_tip_rec.row_id;
2630
2631 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2632 x_return_status := l_return_status;
2633 EXCEPTION
2634 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2635 x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
2636 G_PKG_NAME,
2637 'OKC_API.G_RET_STS_ERROR',
2638 x_msg_count,
2639 x_msg_data,
2640 '_PVT');
2641 -- for unexpected error
2642 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2643 x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
2644 G_PKG_NAME,
2645 'OKC_API.G_RET_STS_UNEXP_ERROR',
2646 x_msg_count,
2647 x_msg_data,
2648 '_PVT');
2649 WHEN OTHERS THEN
2650 -- store SQL error message on message stack for caller
2651 OKC_API.set_message(p_app_name => g_app_name,
2652 p_msg_name => g_unexpected_error,
2653 p_token1 => g_sqlcode_token,
2654 p_token1_value => sqlcode,
2655 p_token2 => g_sqlerrm_token,
2656 p_token2_value => sqlerrm);
2657 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2658 l_msg_tbl(0).msg_text := 'update_party:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2659 log_messages(log_msg_flag => 'V',
2660 msg_tbl => l_msg_tbl );
2661 END update_party;
2662
2663 -- Start of comments
2664 --
2665 -- Procedure Name : select_party_info
2666 -- Description : This procedure retrives the data from database
2667 -- : based on status and updates the party table fields
2668 -- Business Rules :
2669 -- Parameters : Input parameters : p_tip_tbl,p_pty_status
2670 -- Version : 1.0
2671 -- History : 14-MAR-03 RABHUPAT Created
2672 -- End of comments
2673
2674 PROCEDURE select_party_info(
2675 p_api_version IN NUMBER,
2676 p_init_msg_list IN VARCHAR2,
2677 x_msg_count OUT NOCOPY NUMBER,
2678 x_msg_data OUT NOCOPY VARCHAR2,
2679 x_return_status OUT NOCOPY VARCHAR2,
2680 p_tip_tbl IN tip_tbl_type,
2681 x_tip_tbl OUT NOCOPY tip_tbl_type,
2682 p_pty_status IN VARCHAR2
2683 ) IS
2684 record_number NUMBER;
2685 l_msg_tbl msg_tbl_type;
2686 /* this cursor rearranges the data by using the columns contract_number,
2687 quote_type_code, quote_reason_code and asset_id */
2688 CURSOR intface_pty_csr(p_status IN VARCHAR2) IS
2689 SELECT
2690 ROWID
2691 ,CONTRACT_PARTY_ID
2692 ,CONTRACT_PARTY_ROLE
2693 ,CONTRACT_PARTY_NAME
2694 ,CONTRACT_PARTY_NUMBER
2695 ,PARTY_OBJECT_CODE
2696 ,PARTY_OBJECT_ID1
2697 ,PARTY_OBJECT_ID2
2698 ,EMAIL_ADDRESS
2699 ,ALLOCATION_PERCENTAGE
2700 ,DELAY_DAYS
2701 ,QPY_ID
2702 ,TRANSACTION_NUMBER
2703 ,STATUS
2704 ,REQUEST_ID
2705 ,PROGRAM_APPLICATION_ID
2706 ,PROGRAM_ID
2707 ,PROGRAM_UPDATE_DATE
2708 ,ATTRIBUTE_CATEGORY
2709 ,ATTRIBUTE1
2710 ,ATTRIBUTE2
2711 ,ATTRIBUTE3
2712 ,ATTRIBUTE4
2713 ,ATTRIBUTE5
2714 ,ATTRIBUTE6
2715 ,ATTRIBUTE7
2716 ,ATTRIBUTE8
2717 ,ATTRIBUTE9
2718 ,ATTRIBUTE10
2719 ,ATTRIBUTE11
2720 ,ATTRIBUTE12
2721 ,ATTRIBUTE13
2722 ,ATTRIBUTE14
2723 ,ATTRIBUTE15
2724 ,CREATED_BY
2725 ,CREATION_DATE
2726 ,LAST_UPDATED_BY
2727 ,LAST_UPDATE_DATE
2728 ,LAST_UPDATE_LOGIN
2729 ,QUOTE_ROLE_CODE
2730 FROM OKL_TERMNT_INTF_PTY
2731 WHERE STATUS = P_STATUS
2732 ORDER BY TRANSACTION_NUMBER;
2733
2734 BEGIN
2735 record_number:=0;
2736 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2737 IF (p_tip_tbl.COUNT > 0) THEN
2738 record_number := p_tip_tbl.FIRST;
2739 -- loops throught the table of records to update the values
2740 LOOP
2741 update_party(p_api_version => p_api_version
2742 ,p_init_msg_list => OKC_API.G_FALSE
2743 ,x_msg_data => x_msg_data
2744 ,x_msg_count => x_msg_count
2745 ,x_return_status => x_return_status
2746 ,p_tip_rec => p_tip_tbl(record_number));
2747 EXIT WHEN (record_number = p_tip_tbl.LAST);
2748 record_number := p_tip_tbl.NEXT(record_number);
2749 END LOOP;
2750 END IF;
2751 record_number:=0;
2752 x_tip_tbl.DELETE;
2753 -- cursor to retrive the data from party table
2754 FOR party_rec IN intface_pty_csr(p_status => p_pty_status) LOOP
2755 x_tip_tbl(record_number).row_id := party_rec.rowid;
2756 x_tip_tbl(record_number).contract_party_id := party_rec.contract_party_id;
2757 x_tip_tbl(record_number).contract_party_role := party_rec.contract_party_role;
2758 x_tip_tbl(record_number).contract_party_name := party_rec.contract_party_name;
2759 x_tip_tbl(record_number).contract_party_number := party_rec.contract_party_number;
2760 x_tip_tbl(record_number).party_object_code := party_rec.party_object_code;
2761 x_tip_tbl(record_number).party_object_id1 := party_rec.party_object_id1;
2762 x_tip_tbl(record_number).party_object_id2 := party_rec.party_object_id2;
2763 x_tip_tbl(record_number).email_address := party_rec.email_address;
2764 x_tip_tbl(record_number).allocation_percentage := party_rec.allocation_percentage;
2765 x_tip_tbl(record_number).delay_days := party_rec.delay_days;
2766 x_tip_tbl(record_number).qpy_id := party_rec.qpy_id;
2767 x_tip_tbl(record_number).transaction_number := party_rec.transaction_number;
2768 x_tip_tbl(record_number).status := party_rec.status;
2769 x_tip_tbl(record_number).request_id := party_rec.request_id;
2770 x_tip_tbl(record_number).program_application_id := party_rec.program_application_id;
2771 x_tip_tbl(record_number).program_id := party_rec.program_id;
2772 x_tip_tbl(record_number).program_update_date := party_rec.program_update_date;
2773 x_tip_tbl(record_number).attribute_category := party_rec.attribute_category;
2774 x_tip_tbl(record_number).attribute1 := party_rec.attribute1;
2775 x_tip_tbl(record_number).attribute2 := party_rec.attribute2;
2776 x_tip_tbl(record_number).attribute3 := party_rec.attribute3;
2777 x_tip_tbl(record_number).attribute4 := party_rec.attribute4;
2778 x_tip_tbl(record_number).attribute5 := party_rec.attribute5;
2779 x_tip_tbl(record_number).attribute6 := party_rec.attribute6;
2780 x_tip_tbl(record_number).attribute7 := party_rec.attribute7;
2781 x_tip_tbl(record_number).attribute8 := party_rec.attribute8;
2782 x_tip_tbl(record_number).attribute9 := party_rec.attribute9;
2783 x_tip_tbl(record_number).attribute10 := party_rec.attribute10;
2784 x_tip_tbl(record_number).attribute11 := party_rec.attribute11;
2785 x_tip_tbl(record_number).attribute12 := party_rec.attribute12;
2786 x_tip_tbl(record_number).attribute13 := party_rec.attribute13;
2787 x_tip_tbl(record_number).attribute14 := party_rec.attribute14;
2788 x_tip_tbl(record_number).attribute15 := party_rec.attribute15;
2789 x_tip_tbl(record_number).created_by := party_rec.created_by;
2790 x_tip_tbl(record_number).creation_date := party_rec.creation_date;
2791 x_tip_tbl(record_number).last_updated_by := party_rec.last_updated_by;
2792 x_tip_tbl(record_number).last_update_date := party_rec.last_update_date;
2793 x_tip_tbl(record_number).last_update_login := party_rec.last_update_login;
2794 x_tip_tbl(record_number).quote_role_code := party_rec.quote_role_code;
2795 record_number := record_number+1;
2796 END LOOP;
2797 EXCEPTION
2798 WHEN OTHERS THEN
2799 -- store SQL error message on message stack for caller
2800 OKC_API.set_message(p_app_name => g_app_name,
2801 p_msg_name => g_unexpected_error,
2802 p_token1 => g_sqlcode_token,
2803 p_token1_value => sqlcode,
2804 p_token2 => g_sqlerrm_token,
2805 p_token2_value => sqlerrm);
2806 l_msg_tbl(0).msg_text := 'select_party_info:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2807 log_messages(log_msg_flag => 'V',
2808 msg_tbl => l_msg_tbl );
2809 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2810 END select_party_info;
2811
2812 -- Start of comments
2813 --
2814 -- Procedure Name : populate_party_for_quote
2815 -- Description : This procedure populates the party information
2816 -- : and creates the quote
2817 -- Business Rules :
2818 -- Parameters : Input parameters : p_tip_tbl,p_tif_tbl
2819 -- Version : 1.0
2820 -- History : 14-MAR-03 RABHUPAT Created
2821 -- End of comments
2822
2823 PROCEDURE populate_party_for_quote(p_api_version IN NUMBER,
2824 p_init_msg_list IN VARCHAR2,
2825 x_msg_count OUT NOCOPY NUMBER,
2826 x_msg_data OUT NOCOPY VARCHAR2,
2827 x_return_status OUT NOCOPY VARCHAR2,
2828 p_tip_tbl IN tip_tbl_type,
2829 p_tif_tbl IN tif_tbl_type,
2830 x_tif_tbl OUT NOCOPY tif_tbl_type,
2831 x_group_number OUT NOCOPY NUMBER) IS
2832 l_tip_rec tip_rec_type;
2833 lp_tif_tbl tif_tbl_type;
2834 lx_tif_tbl tif_tbl_type;
2835 lp_quot_rec quot_rec_type;
2836 lx_quot_rec quot_rec_type;
2837 lp_assn_tbl assn_tbl_type;
2838 lx_assn_tbl assn_tbl_type;
2839 lx_tqlv_tbl tqlv_tbl_type;
2840 lp_qpyv_tbl qpyv_tbl_type;
2841 lx_qpyv_tbl qpyv_tbl_type;
2842 x_q_party_uv_tbl OKL_AM_PARTIES_PVT.q_party_uv_tbl_type;
2843 l_msg_tbl msg_tbl_type;
2844 x_record_count NUMBER;
2845 BEGIN
2846 lp_tif_tbl := p_tif_tbl;
2847 x_group_number := 0;
2848 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2849 IF(p_tip_tbl.COUNT>0) THEN
2850 -- populating the values required to create_quote
2851 FOR party_rec IN p_tip_tbl.FIRST..p_tip_tbl.LAST
2852 LOOP
2853 l_tip_rec :=p_tip_tbl(party_rec);
2854 IF(lp_tif_tbl.COUNT>0) THEN
2855 FOR term_rec IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
2856 LOOP
2857 -- populates quot_rec and assn_tbl
2858 IF(p_tip_tbl(party_rec).transaction_number = lp_tif_tbl(term_rec).transaction_number) THEN
2859 -- set the group number
2860 lp_tif_tbl(term_rec).group_number := x_group_number;
2861 x_group_number := x_group_number+1;
2862 -- populate quot_rec
2863 lp_quot_rec.khr_id := lp_tif_tbl(term_rec).contract_id;
2864 lp_quot_rec.date_effective_from := lp_tif_tbl(term_rec).date_effective_from;
2865 lp_quot_rec.org_id := lp_tif_tbl(term_rec).org_id;
2866 lp_quot_rec.qtp_code := lp_tif_tbl(term_rec).quote_type_code;
2867 lp_quot_rec.qrs_code := lp_tif_tbl(term_rec).quote_reason_code;
2868 --populate assn_tbl
2869 lp_assn_tbl(0).p_asset_id := lp_tif_tbl(term_rec).asset_id;
2870 lp_assn_tbl(0).p_asset_number := lp_tif_tbl(term_rec).asset_number;
2871 lp_assn_tbl(0).p_quote_qty := lp_tif_tbl(term_rec).units_to_terminate;
2872 -- populate all the five default options and override the RECIPIENT
2873 OKL_AM_PARTIES_PVT.fetch_rule_quote_parties (p_api_version => p_api_version,
2874 p_init_msg_list => OKC_API.G_FALSE,
2875 x_msg_count => x_msg_count,
2876 x_msg_data => x_msg_data,
2877 x_return_status => x_return_status,
2878 p_qtev_rec => lp_quot_rec,
2879 x_qpyv_tbl => lp_qpyv_tbl,
2880 x_record_count => x_record_count,
2881 x_q_party_uv_tbl => x_q_party_uv_tbl);
2882 -- populates qpyv_tbl
2883 IF(lp_qpyv_tbl.COUNT>0) THEN
2884 FOR rule_party IN lp_qpyv_tbl.FIRST..lp_qpyv_tbl.LAST
2885 LOOP
2886 IF(lp_qpyv_tbl(rule_party).qpt_code = l_tip_rec.quote_role_code) THEN
2887 lp_qpyv_tbl(rule_party).party_jtot_object1_code := l_tip_rec.party_object_code;
2888 lp_qpyv_tbl(rule_party).party_object1_id1 := l_tip_rec.party_object_id1;
2889 lp_qpyv_tbl(rule_party).party_object1_id2 := l_tip_rec.party_object_id2;
2890 lp_qpyv_tbl(rule_party).cpl_id := l_tip_rec.qpy_id;
2891 END IF;
2892 END LOOP;
2893 END IF;
2894 -- calls Create quote parties with p_validate_only = TRUE
2895 OKL_AM_PARTIES_PVT.create_quote_parties (
2896 p_qtev_rec => lp_quot_rec,
2897 p_qpyv_tbl => lp_qpyv_tbl,
2898 p_validate_only => TRUE,
2899 x_qpyv_tbl => lx_qpyv_tbl,
2900 x_return_status => x_return_status);
2901 IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2902 lx_tif_tbl.DELETE;
2903 -- create quote and quote_details
2904 create_quote(p_api_version => p_api_version
2905 ,p_init_msg_list => OKC_API.G_FALSE
2906 ,x_msg_count => x_msg_count
2907 ,x_msg_data => x_msg_data
2908 ,x_return_status => x_return_status
2909 ,p_tif_tbl => lp_tif_tbl
2910 ,x_tif_tbl => lx_tif_tbl
2911 ,p_quot_rec => lp_quot_rec
2912 ,p_assn_tbl => lp_assn_tbl
2913 ,p_qpyv_tbl => lp_qpyv_tbl -- add it in create_quote also
2914 ,p_batch_offset => term_rec
2915 ,p_record_number => term_rec);
2916 ELSE
2917 lp_tif_tbl(term_rec).status := 'ERROR';
2918 END IF;
2919 -- update party record and interface record with values returned from create_quote
2920 IF(lx_tif_tbl(term_rec).status = 'PROCESSED') THEN
2921 l_tip_rec.status :='PROCESSED';
2922 lp_tif_tbl(term_rec) := lx_tif_tbl(term_rec);
2923 ELSE
2924 l_tip_rec.status :='ERROR';
2925 -- quote not created for transaction_number
2926 END IF;
2927 --updates the party table
2928 update_party(p_api_version => p_api_version
2929 ,p_init_msg_list => OKC_API.G_FALSE
2930 ,x_msg_data => x_msg_data
2931 ,x_msg_count => x_msg_count
2932 ,x_return_status => x_return_status
2933 ,p_tip_rec => l_tip_rec);
2934 EXIT;
2935 END IF;
2936 END LOOP;
2937 END IF;
2938 END LOOP;
2939 END IF;
2940 x_tif_tbl := lp_tif_tbl;
2941 EXCEPTION
2942 WHEN OTHERS THEN
2943 -- store SQL error message on message stack for caller
2944 OKC_API.set_message(p_app_name => g_app_name,
2945 p_msg_name => g_unexpected_error,
2946 p_token1 => g_sqlcode_token,
2947 p_token1_value => sqlcode,
2948 p_token2 => g_sqlerrm_token,
2949 p_token2_value => sqlerrm);
2950 l_msg_tbl(0).msg_text := 'populate_party_for_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2951 log_messages(log_msg_flag => 'V',
2952 msg_tbl => l_msg_tbl );
2953 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2954 END populate_party_for_quote;
2955 /*PARTY_LOGIC END*/
2956
2957
2958 -- Start of comments
2959 --
2960 -- Procedure Name : termination_interface
2961 -- Description : This is the only public procedure and calls all the
2962 -- : required procedures to generate a quote
2963 -- Business Rules :
2964 -- Parameters : Input parameters : p_api_version, p_init_msg_list
2965 -- Version : 1.0
2966 -- History : 04-FEB-03 RABHUPAT Created
2967 -- : 14-MAR-03 RABHUPAT added calls for party logic procedures
2968 -- End of comments
2969
2970 PROCEDURE termination_interface(p_api_version IN NUMBER,
2971 p_init_msg_list IN VARCHAR2,
2972 x_msg_count OUT NOCOPY NUMBER,
2973 x_msg_data OUT NOCOPY VARCHAR2,
2974 x_return_status OUT NOCOPY VARCHAR2,
2975 err_buf OUT NOCOPY VARCHAR2,
2976 ret_code OUT NOCOPY NUMBER)
2977 IS
2978
2979 -- this cursor selects the rows with status as 'ENTERED' and orders by group_number
2980 CURSOR get_termnt_intface_dtls_csr(p_status IN VARCHAR2) IS
2981 SELECT
2982 ROWID
2983 ,TRANSACTION_NUMBER
2984 ,BATCH_NUMBER
2985 ,CONTRACT_ID
2986 ,CONTRACT_NUMBER
2987 ,ASSET_ID
2988 ,ASSET_NUMBER
2989 ,ASSET_DESCRIPTION
2990 ,SERIAL_NUMBER
2991 ,ORIG_SYSTEM
2992 ,ORIG_SYSTEM_REFERENCE
2993 ,UNITS_TO_TERMINATE
2994 ,COMMENTS
2995 ,DATE_PROCESSED
2996 ,DATE_EFFECTIVE_FROM
2997 ,TERMINATION_NOTIFICATION_EMAIL
2998 ,TERMINATION_NOTIFICATION_YN
2999 ,AUTO_ACCEPT_YN
3000 ,QUOTE_TYPE_CODE
3001 ,QUOTE_REASON_CODE
3002 ,QTE_ID
3003 ,STATUS
3004 ,ORG_ID
3005 ,REQUEST_ID
3006 ,PROGRAM_APPLICATION_ID
3007 ,PROGRAM_ID
3008 ,PROGRAM_UPDATE_DATE
3009 ,ATTRIBUTE_CATEGORY
3010 ,ATTRIBUTE1
3011 ,ATTRIBUTE2
3012 ,ATTRIBUTE3
3013 ,ATTRIBUTE4
3014 ,ATTRIBUTE5
3015 ,ATTRIBUTE6
3016 ,ATTRIBUTE7
3017 ,ATTRIBUTE8
3018 ,ATTRIBUTE9
3019 ,ATTRIBUTE10
3020 ,ATTRIBUTE11
3021 ,ATTRIBUTE12
3022 ,ATTRIBUTE13
3023 ,ATTRIBUTE14
3024 ,ATTRIBUTE15
3025 ,CREATED_BY
3026 ,CREATION_DATE
3027 ,LAST_UPDATED_BY
3028 ,LAST_UPDATE_DATE
3029 ,LAST_UPDATE_LOGIN
3030 ,GROUP_NUMBER
3031 FROM OKL_TERMNT_INTERFACE
3032 WHERE status = p_status
3033 ORDER BY CONTRACT_NUMBER;
3034
3035 l_api_version CONSTANT NUMBER := 1;
3036 l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
3037 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3038 record_number NUMBER;
3039 x_installbase_id NUMBER;
3040 l_sys_date DATE;
3041 l_tif_tbl tif_tbl_type;
3042 lx_tif_tbl tif_tbl_type;
3043 l_tip_tbl tip_tbl_type;
3044 lx_tip_tbl tip_tbl_type;
3045 l_group_number NUMBER := 0;
3046 tot_rec_processed NUMBER := 0;
3047 l_msg_tbl msg_tbl_type;
3048 BEGIN
3049
3050 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3051 --Check API version, initialize message list and create savepoint.
3052 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3053 G_PKG_NAME,
3054 p_init_msg_list,
3055 l_api_version,
3056 p_api_version,
3057 '_PVT',
3058 x_return_status);
3059 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3060 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3061 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3062 RAISE OKC_API.G_EXCEPTION_ERROR;
3063 END IF;
3064 -- this retrives the sysdate and passes to a local variable
3065 SELECT trunc(SYSDATE) INTO l_sys_date FROM DUAL;
3066 record_number:=0;
3067
3068 -- loops through the records to populate the plsql table
3069 FOR termnt_rec IN get_termnt_intface_dtls_csr(p_status=>'ENTERED')
3070 LOOP
3071 l_tif_tbl(record_number).row_id := termnt_rec.rowid;
3072 l_tif_tbl(record_number).transaction_number := termnt_rec.transaction_number;
3073 l_tif_tbl(record_number).batch_number := termnt_rec.batch_number;
3074 l_tif_tbl(record_number).contract_id := termnt_rec.contract_id;
3075 l_tif_tbl(record_number).contract_number := termnt_rec.contract_number;
3076 l_tif_tbl(record_number).asset_id := termnt_rec.asset_id;
3077 l_tif_tbl(record_number).asset_number := termnt_rec.asset_number;
3078 l_tif_tbl(record_number).asset_description := termnt_rec.asset_description;
3079 l_tif_tbl(record_number).serial_number := termnt_rec.serial_number;
3080 l_tif_tbl(record_number).orig_system := termnt_rec.orig_system;
3081 l_tif_tbl(record_number).orig_system_reference := termnt_rec.orig_system_reference;
3082 l_tif_tbl(record_number).units_to_terminate := termnt_rec.units_to_terminate;
3083 l_tif_tbl(record_number).comments := termnt_rec.comments;
3084 l_tif_tbl(record_number).date_processed := termnt_rec.date_processed;
3085 l_tif_tbl(record_number).date_effective_from := termnt_rec.date_effective_from;
3086 l_tif_tbl(record_number).termination_notification_email := termnt_rec.termination_notification_email;
3087 l_tif_tbl(record_number).termination_notification_yn := termnt_rec.termination_notification_yn;
3088 l_tif_tbl(record_number).auto_accept_yn := termnt_rec.auto_accept_yn;
3089 l_tif_tbl(record_number).quote_type_code := termnt_rec.quote_type_code;
3090 l_tif_tbl(record_number).quote_reason_code := termnt_rec.quote_reason_code;
3091 l_tif_tbl(record_number).qte_id := termnt_rec.qte_id;
3092 l_tif_tbl(record_number).status := termnt_rec.status;
3093 l_tif_tbl(record_number).org_id := termnt_rec.org_id;
3094 l_tif_tbl(record_number).request_id := termnt_rec.request_id;
3095 l_tif_tbl(record_number).program_application_id := termnt_rec.program_application_id;
3096 l_tif_tbl(record_number).program_id := termnt_rec.program_id;
3097 l_tif_tbl(record_number).program_update_date := termnt_rec.program_update_date;
3098 l_tif_tbl(record_number).attribute_category := termnt_rec.attribute_category;
3099 l_tif_tbl(record_number).attribute1 := termnt_rec.attribute1;
3100 l_tif_tbl(record_number).attribute2 := termnt_rec.attribute2;
3101 l_tif_tbl(record_number).attribute3 := termnt_rec.attribute3;
3102 l_tif_tbl(record_number).attribute4 := termnt_rec.attribute4;
3103 l_tif_tbl(record_number).attribute5 := termnt_rec.attribute5;
3104 l_tif_tbl(record_number).attribute6 := termnt_rec.attribute6;
3105 l_tif_tbl(record_number).attribute7 := termnt_rec.attribute7;
3106 l_tif_tbl(record_number).attribute8 := termnt_rec.attribute8;
3107 l_tif_tbl(record_number).attribute9 := termnt_rec.attribute9;
3108 l_tif_tbl(record_number).attribute10 := termnt_rec.attribute10;
3109 l_tif_tbl(record_number).attribute11 := termnt_rec.attribute11;
3110 l_tif_tbl(record_number).attribute12 := termnt_rec.attribute12;
3111 l_tif_tbl(record_number).attribute13 := termnt_rec.attribute13;
3112 l_tif_tbl(record_number).attribute14 := termnt_rec.attribute14;
3113 l_tif_tbl(record_number).attribute15 := termnt_rec.attribute15;
3114 l_tif_tbl(record_number).created_by := termnt_rec.created_by;
3115 l_tif_tbl(record_number).creation_date := termnt_rec.creation_date;
3116 l_tif_tbl(record_number).last_updated_by := termnt_rec.last_updated_by;
3117 l_tif_tbl(record_number).last_update_date := termnt_rec.last_update_date;
3118 l_tif_tbl(record_number).last_update_login := termnt_rec.last_update_login;
3119 l_tif_tbl(record_number).group_number := termnt_rec.group_number;
3120 record_number := record_number+1;
3121 END LOOP;
3122
3123 tot_rec_processed := l_tif_tbl.COUNT;
3124 -- validates quote_type and quote_reason and populates WHO columns
3125 validate_quote_type_and_reason(p_api_version => l_api_version,
3126 p_init_msg_list => OKC_API.G_TRUE,
3127 x_msg_count => x_msg_count,
3128 x_msg_data => x_msg_data,
3129 x_return_status => x_return_status,
3130 p_tif_tbl => l_tif_tbl,
3131 x_tif_tbl => lx_tif_tbl);
3132 l_tif_tbl.DELETE;
3133 l_tif_tbl := lx_tif_tbl;
3134 lx_tif_tbl.DELETE;
3135 -- validates contract and asset details entered, populates the remaining calls required
3136 validate_transaction(p_api_version => l_api_version,
3137 p_init_msg_list => OKC_API.G_FALSE,
3138 x_msg_count => x_msg_count,
3139 x_msg_data => x_msg_data,
3140 x_return_status => x_return_status,
3141 p_tif_tbl => l_tif_tbl,
3142 p_sys_date => l_sys_date,
3143 x_tif_tbl => lx_tif_tbl);
3144 l_tif_tbl.DELETE;
3145 l_tif_tbl := lx_tif_tbl;
3146 lx_tif_tbl.DELETE;
3147 -- updates the values in database and gets records with status='WORKING'
3148 change_status(p_api_version => l_api_version,
3149 p_init_msg_list => OKC_API.G_FALSE,
3150 x_msg_count => x_msg_count,
3151 x_msg_data => x_msg_data,
3152 x_return_status => x_return_status,
3153 p_tif_tbl => l_tif_tbl,
3154 x_tif_tbl => lx_tif_tbl);
3155
3156 l_tif_tbl.DELETE;
3157 l_tif_tbl := lx_tif_tbl;
3158 lx_tif_tbl.DELETE;
3159 -- selects the party information and populates WHO columns in party table
3160 select_party_info(p_api_version => l_api_version,
3161 p_init_msg_list => OKC_API.G_FALSE,
3162 x_msg_count => x_msg_count,
3163 x_msg_data => x_msg_data,
3164 x_return_status => x_return_status,
3165 p_tip_tbl => l_tip_tbl,
3166 x_tip_tbl => lx_tip_tbl,
3167 p_pty_status => 'ENTERED');
3168 l_tip_tbl.DELETE;
3169 l_tip_tbl := lx_tip_tbl;
3170 lx_tip_tbl.DELETE;
3171 -- validates party information
3172 validate_party(p_api_version => l_api_version,
3173 p_init_msg_list => OKC_API.G_FALSE,
3174 x_msg_count => x_msg_count,
3175 x_msg_data => x_msg_data,
3176 x_return_status => x_return_status,
3177 p_tif_tbl => l_tif_tbl,
3178 x_tif_tbl => lx_tif_tbl,
3179 p_tip_tbl => l_tip_tbl,
3180 x_tip_tbl => lx_tip_tbl);
3181 l_tip_tbl.DELETE;
3182 l_tip_tbl := lx_tip_tbl;
3183 lx_tip_tbl.DELETE;
3184
3185 l_tif_tbl.DELETE;
3186 l_tif_tbl := lx_tif_tbl;
3187 lx_tif_tbl.DELETE;
3188 -- updates values in database and gets records with status='WORKING'
3189 select_party_info(p_api_version => l_api_version,
3190 p_init_msg_list => OKC_API.G_FALSE,
3191 x_msg_count => x_msg_count,
3192 x_msg_data => x_msg_data,
3193 x_return_status => x_return_status,
3194 p_tip_tbl => l_tip_tbl,
3195 x_tip_tbl => lx_tip_tbl,
3196 p_pty_status => 'WORKING');
3197 l_tip_tbl.DELETE;
3198 l_tip_tbl := lx_tip_tbl;
3199 lx_tip_tbl.DELETE;
3200 -- populates the fields required for quote and creates quote for entries in party table
3201 populate_party_for_quote(p_api_version => l_api_version,
3202 p_init_msg_list => OKC_API.G_FALSE,
3203 x_msg_count => x_msg_count,
3204 x_msg_data => x_msg_data,
3205 x_return_status => x_return_status,
3206 p_tip_tbl => l_tip_tbl,
3207 p_tif_tbl => l_tif_tbl,
3208 x_tif_tbl => lx_tif_tbl,
3209 x_group_number => l_group_number);
3210 l_tif_tbl.DELETE;
3211 l_tif_tbl := lx_tif_tbl;
3212 lx_tif_tbl.DELETE;
3213 -- updates the values to database and removes the duplicates while grouping
3214 remove_duplicates(p_api_version => l_api_version,
3215 p_init_msg_list => OKC_API.G_FALSE,
3216 x_msg_count => x_msg_count,
3217 x_msg_data => x_msg_data,
3218 x_return_status => x_return_status,
3219 p_tif_tbl => l_tif_tbl,
3220 x_tif_tbl => lx_tif_tbl);
3221
3222 l_tif_tbl.DELETE;
3223 l_tif_tbl := lx_tif_tbl;
3224 lx_tif_tbl.DELETE;
3225 -- creates groups and calls create_quote
3226 populate_quote(p_api_version => l_api_version,
3227 p_init_msg_list => OKC_API.G_FALSE,
3228 x_msg_count => x_msg_count,
3229 x_msg_data => x_msg_data,
3230 x_return_status => x_return_status,
3231 p_tif_tbl => l_tif_tbl,
3232 x_tif_tbl => lx_tif_tbl,
3233 p_group_number => l_group_number);
3234
3235 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3236 log_messages(log_msg_flag => 'Y',
3237 p_quote_number => tot_rec_processed,
3238 msg_tbl => l_msg_tbl );
3239 log_messages(log_msg_flag => 'O',
3240 p_quote_number => tot_rec_processed,
3241 msg_tbl => l_msg_tbl );
3242 EXCEPTION
3243 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3244 x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3245 G_PKG_NAME,
3246 'OKC_API.G_RET_STS_ERROR',
3247 x_msg_count,
3248 x_msg_data,
3249 '_PVT');
3250 -- unexpected error
3251 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3252 x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3253 G_PKG_NAME,
3254 'OKC_API.G_RET_STS_UNEXP_ERROR',
3255 x_msg_count,
3256 x_msg_data,
3257 '_PVT');
3258 WHEN OTHERS THEN
3259 -- store SQL error message on message stack for caller
3260 OKC_API.set_message(p_app_name => g_app_name,
3261 p_msg_name => g_unexpected_error,
3262 p_token1 => g_sqlcode_token,
3263 p_token1_value => sqlcode,
3264 p_token2 => g_sqlerrm_token,
3265 p_token2_value => sqlerrm);
3266 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3267 l_msg_tbl(0).msg_text := 'termination_interface:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3268 log_messages(log_msg_flag => 'Y',
3269 p_quote_number => tot_rec_processed,
3270 msg_tbl => l_msg_tbl );
3271 log_messages(log_msg_flag => 'O',
3272 p_quote_number => tot_rec_processed,
3273 msg_tbl => l_msg_tbl );
3274
3275 END termination_interface;
3276 END OKL_AM_TERMNT_INTERFACE_PVT;