[Home] [Help]
PACKAGE BODY: APPS.PN_LEASE_PUB
Source
1 PACKAGE BODY pn_lease_pub AS
2 /* $Header: PNLSPUBB.pls 120.4 2011/09/12 08:39:04 acprakas ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'PN_LEASE_PUB';
5 g_table_name CONSTANT VARCHAR2 (40) := 'PN_LEASES_ALL';
6 g_tl_table_name CONSTANT VARCHAR2 (40) := 'NONE';
7 g_resp_appl_id CONSTANT NUMBER := fnd_global.resp_appl_id;
8 g_resp_id CONSTANT NUMBER := fnd_global.resp_appl_id;
9 g_user_id CONSTANT NUMBER := fnd_global.user_id;
10 g_login_id CONSTANT NUMBER := fnd_global.login_id;
11 g_org_id CONSTANT NUMBER := fnd_profile.VALUE ('ORG_ID');--NVL(PN_MO_CACHE_UTILS.get_current_org_id,fnd_profile.VALUE ('org_id'));
12
13 -- Procedure to create a new lease
14 PROCEDURE create_lease (
15 p_api_version IN NUMBER
16 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
17 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
18 , p_validate IN VARCHAR2
19 , p_lease_rec IN OUT NOCOPY PN_LEASE_PVT.lease_rec
20 , p_lease_exp_rev_accounts_rec IN OUT NOCOPY PN_LEASE_PVT.lease_account_rec
21 , p_lease_accrual_account_rec IN OUT NOCOPY PN_LEASE_PVT.lease_account_rec
22 , p_lease_liab_rcvl_account_rec IN OUT NOCOPY PN_LEASE_PVT.lease_account_rec
23 , p_contacts_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_contacts_tbl
24 , p_tenancies_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_tenancies_tbl
25 , p_insurance_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_insurance_tbl_type
26 , p_right_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_right_tbl_type
27 , p_obligation_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_obligation_tbl_type
28 , p_option_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_option_tbl_type
29 , p_lease_payment_term_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_terms_tbl
30 , p_note_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_note_tbl_type
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 , x_return_status OUT NOCOPY VARCHAR2
34 , p_lease_context IN VARCHAR2
35 )
36
37 AS
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_LEASE';
40 l_api_name_full CONSTANT VARCHAR2 (61)
41 --l_msg_count NUMBER;
42 --l_msg_data VARCHAR2(2000);
43 := g_pkg_name
44 || '.'
45 || l_api_name;
46 BEGIN
47 -- Standard start of API savepoint
48 SAVEPOINT create_lease;
49
50 --Added to clear message cache in case of API call wrong version.
51
52 -- Initialize message list if p_init_msg_list is set to TRUE
53 IF fnd_api.to_boolean (p_init_msg_list)
54 THEN
55 ----dbms_output.put_line('Hello:1');
56 fnd_msg_pub.initialize;
57 END IF;
58
59 -- Standard call to check for call compatibility
60 IF NOT fnd_api.compatible_api_call (l_api_version
61 , p_api_version
62 , l_api_name -- CREATE_LEASE
63 , g_pkg_name -- PN_LEASE_PUB
64 )
65 THEN
66 RAISE fnd_api.g_exc_error;
67 END IF;
68
69 -- Initialize API return status to success
70 x_return_status := fnd_api.g_ret_sts_success;
71
72 ----dbms_output.put_line('In Public :p_lease_rec.lease_num:1'||p_lease_rec.lease_num); --Rupak
73 -- ------dbms_output.put_line('Resp_appl_id:'||g_resp_appl_id);
74 -- ------dbms_output.put_line('Resp_id:'||g_RESP_id);
75 -- ------dbms_output.put_line('Login Id:'||g_login_id);
76 IF NVL (mo_global.check_valid_org (g_org_id), 'N') <> 'Y'
77 THEN
78
79 fnd_message.set_name ('MO', 'MO_ORG_INVALID');
80 fnd_msg_pub.ADD;
81 RAISE fnd_api.g_exc_error;
82 END IF;
83
84 /*********************************************/
85 -- Required Parameters Validation
86 /*********************************************/
87 -- -------------------------------------------
88 -- Make sure the Lease Number is not null if Auto Lease Number is setup to No
89 IF (NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_LEASE_NUMBER', p_lease_rec.org_id), 'N')) <> 'Y'
90 THEN
91
92 IF ( p_lease_rec.lease_num IS NULL
93 OR p_lease_rec.lease_num = pn_lease_utils.g_pn_miss_char
94 )
95 THEN
96 ------dbms_output.put_line('Public:1'); --Rupak
97 pn_lease_utils.add_null_parameter_msg
98 (p_token_apiname => l_api_name_full
99 , p_token_nullparam => 'lease_num');
100 RAISE fnd_api.g_exc_error;
101 END IF;
102 END IF;
103 ------dbms_output.put_line('Public:12'); --Rupak
104 -- Make sure the Lease Name is not null
105 -- -------------------------------------------
106 IF ( p_lease_rec.lease_name IS NULL
107 OR p_lease_rec.lease_name = pn_lease_utils.g_pn_miss_char
108 )
109 THEN
110 pn_lease_utils.add_null_parameter_msg
111 (p_token_apiname => l_api_name_full
112 , p_token_nullparam => 'lease_name');
113 RAISE fnd_api.g_exc_error;
114 END IF;
115 ------dbms_output.put_line('Public:2'); --Rupak
116 -- -------------------------------------------
117 -- Make sure the Lease Type is not null
118 -- -------------------------------------------
119 IF ( p_lease_rec.lease_type IS NULL
120 AND p_lease_rec.lease_type_code IS NULL
121 )
122 OR ( p_lease_rec.lease_type = pn_lease_utils.g_pn_miss_char
123 AND p_lease_rec.lease_type_code = pn_lease_utils.g_pn_miss_char
124 )
125 THEN
126 pn_lease_utils.add_null_parameter_msg
127 (p_token_apiname => l_api_name_full
128 , p_token_nullparam => 'lease_type');
129 RAISE fnd_api.g_exc_error;
130 END IF;
131
132 ---------------------------------------------
133 -- Make sure the Lease Class is not null
134 ---------------------------------------------
135 IF ( p_lease_rec.lease_class IS NULL
136 AND p_lease_rec.lease_class_code IS NULL
137 )
138 OR ( p_lease_rec.lease_class = pn_lease_utils.g_pn_miss_char
139 AND p_lease_rec.lease_class_code = pn_lease_utils.g_pn_miss_char
140 )
141 THEN
142 pn_lease_utils.add_null_parameter_msg
143 (p_token_apiname => l_api_name_full
144 , p_token_nullparam => 'lease_class');
145 RAISE fnd_api.g_exc_error;
146 END IF;
147 ------dbms_output.put_line('Public:4'); --Rupak
148 ---------------------------------------------
149 -- Make sure the Parent Lease is not null ID Lease Class is SUB_LEASE
150 ---------------------------------------------
151 IF ( p_lease_rec.lease_class = 'Sublease'
152 OR p_lease_rec.lease_class_code = 'SUB_LEASE'
153 )
154 THEN
155 IF ( p_lease_rec.parent_lease_id IS NULL
156 AND p_lease_rec.parent_lease_name IS NULL
157 )
158 OR ( p_lease_rec.parent_lease_name =
159 pn_lease_utils.g_pn_miss_char
160 AND p_lease_rec.parent_lease_id =
161 pn_lease_utils.g_pn_miss_num
162 )
163 THEN
164 pn_lease_utils.add_null_parameter_msg
165 (p_token_apiname => l_api_name_full
166 , p_token_nullparam => 'Parent_Lease');
167 RAISE fnd_api.g_exc_error;
168 END IF;
169 END IF;
170 ------dbms_output.put_line('Public:5'); --Rupak
171 -- -------------------------------------------
172 -- Make sure the abstracted_by_user is not null
173 -- -------------------------------------------
174 IF ( p_lease_rec.abstracted_by_user IS NULL
175 AND p_lease_rec.abstracted_by_user_id IS NULL
176 )
177 OR ( p_lease_rec.abstracted_by_user = pn_lease_utils.g_pn_miss_char
178 AND p_lease_rec.abstracted_by_user_id = pn_lease_utils.g_pn_miss_num
179 )
180 THEN
181 pn_lease_utils.add_null_parameter_msg
182 (p_token_apiname => l_api_name_full
183 , p_token_nullparam => 'abstracted_by_user');
184 RAISE fnd_api.g_exc_error;
185 END IF;
186 ------dbms_output.put_line('Public:6'); --Rupak
187 -- -------------------------------------------
188 -- Make sure the Approval_status is not null
189 -- -------------------------------------------
190 IF ( p_lease_rec.status IS NULL
191 AND p_lease_rec.status_code IS NULL)
192 OR ( p_lease_rec.status = pn_lease_utils.g_pn_miss_char
193 AND p_lease_rec.status_code = pn_lease_utils.g_pn_miss_char
194 )
195 THEN
196 pn_lease_utils.add_null_parameter_msg
197 (p_token_apiname => l_api_name_full
198 , p_token_nullparam => 'Approval_status');
199 RAISE fnd_api.g_exc_error;
200 END IF;
201 ------dbms_output.put_line('Public:7'); --Rupak
202 -- -------------------------------------------
203 -- Make sure the lease_status is not null
204 -- -------------------------------------------
205 IF ( p_lease_rec.lease_status_code IS NULL
206 AND p_lease_rec.lease_status IS NULL
207 )
208 OR ( p_lease_rec.lease_status_code = pn_lease_utils.g_pn_miss_char
209 AND p_lease_rec.lease_status = pn_lease_utils.g_pn_miss_char
210 )
211 THEN
212 pn_lease_utils.add_null_parameter_msg
213 (p_token_apiname => l_api_name_full
214 , p_token_nullparam => 'lease_status');
215 RAISE fnd_api.g_exc_error;
216 END IF;
217 ------dbms_output.put_line('Public:8'); --Rupak
218 -- -------------------------------------------
219 -- Make sure the lease_termination_date is not null
220 -- -------------------------------------------
221 IF ( p_lease_rec.lease_termination_date IS NULL
222 OR p_lease_rec.lease_termination_date = fnd_api.g_miss_date
223 )
224 THEN
225 pn_lease_utils.add_null_parameter_msg
226 (p_token_apiname => l_api_name_full
227 , p_token_nullparam => 'lease_termination_date');
228 RAISE fnd_api.g_exc_error;
229 END IF;
230 ------dbms_output.put_line('Public:9'); --Rupak
231 -- -------------------------------------------
232 -- Make sure the lease_execution_date is not null
233 -- -------------------------------------------
234 IF ( p_lease_rec.lease_execution_date IS NULL
235 OR p_lease_rec.lease_execution_date = fnd_api.g_miss_date
236 )
237 THEN
238 pn_lease_utils.add_null_parameter_msg
239 (p_token_apiname => l_api_name_full
240 , p_token_nullparam => 'lease_execution_date');
241 RAISE fnd_api.g_exc_error;
242 END IF;
243 ------dbms_output.put_line('Public:10'); --Rupak
244 -- -------------------------------------------
245 -- Make sure the lease_commencement_date is not null
246 -- -------------------------------------------
247 IF ( p_lease_rec.lease_commencement_date IS NULL
248 OR p_lease_rec.lease_commencement_date = fnd_api.g_miss_date
249 )
250 THEN
251 pn_lease_utils.add_null_parameter_msg
252 (p_token_apiname => l_api_name_full
253 , p_token_nullparam => 'lease_commencement_date');
254 RAISE fnd_api.g_exc_error;
255 END IF;
256 ------dbms_output.put_line('Public:11'); --Rupak
257 -- -------------------------------------------
258 -- Make sure the pymt_term_pro_rule is not null
259 -- -------------------------------------------
260 IF ( p_lease_rec.pymt_term_pro_rule IS NULL
261 AND p_lease_rec.pymt_term_pro_rule_id IS NULL
262 )
263 OR ( p_lease_rec.pymt_term_pro_rule = fnd_api.g_null_char
264 AND p_lease_rec.pymt_term_pro_rule_id = pn_lease_utils.g_pn_miss_char
265 )
266 THEN
267 pn_lease_utils.add_null_parameter_msg
268 (p_token_apiname => l_api_name_full
269 , p_token_nullparam => 'pymt_term_pro_rule');
270 RAISE fnd_api.g_exc_error;
271 END IF;
272 ------dbms_output.put_line('Public:12'); --Rupak
273 -- -------------------------------------------
274 -- Make sure the responsible_user is not null
275 -- -------------------------------------------
276 IF ( p_lease_rec.responsible_user IS NULL
277 AND p_lease_rec.responsible_user_id IS NULL
278 )
279 OR ( p_lease_rec.responsible_user = pn_lease_utils.g_pn_miss_char
280 AND p_lease_rec.responsible_user_id = pn_lease_utils.g_pn_miss_num
281 )
282 THEN
283 pn_lease_utils.add_null_parameter_msg
284 (p_token_apiname => l_api_name_full
285 , p_token_nullparam => 'responsible_user');
286 RAISE fnd_api.g_exc_error;
287 END IF;
288 ------dbms_output.put_line('Public:13'); --Rupak
289 -----+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
290 -----+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
291
292 ----dbms_output.put_line('In Public:B4 PVT Create Lease:Contacts');--Rupak
293 pn_lease_pvt.create_lease
294 (p_commit => p_commit
295 , p_validate => p_validate
296 , p_lease_rec => p_lease_rec
297 , p_lease_exp_rev_accounts_rec => p_lease_exp_rev_accounts_rec
298 , p_lease_accrual_account_rec => p_lease_accrual_account_rec
299 , p_lease_liab_rcvl_account_rec => p_lease_liab_rcvl_account_rec
300 , p_contacts_tbl => p_contacts_tbl
301 , p_tenancies_tbl => p_tenancies_tbl
302 , p_insurance_tbl => p_insurance_tbl
303 , p_right_tbl => p_right_tbl
304 , p_obligation_tbl => p_obligation_tbl
305 , p_option_tbl => p_option_tbl
306 , p_lease_payment_term_tbl => p_lease_payment_term_tbl
307 , p_note_tbl => p_note_tbl
308 , x_msg_count => x_msg_count
309 , x_msg_data => x_msg_data
310 , x_return_status => x_return_status
311 , p_lease_context => p_lease_context
312 );
313
314 ----dbms_output.put_line('In Public:After PVT Create Lease'||x_return_status);
315 ------dbms_output.put_line('In Public:Hi:x_msg_count'||x_msg_count);
316 -- Standard check of p_commit
317 IF fnd_api.to_boolean (p_commit)
318 THEN
319 COMMIT;
320 END IF;
321
322 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
323 , p_count => x_msg_count
324 , p_data => x_msg_data
325 );
326
327 /*----dbms_output.put_line('hello:1');--Rupak
328 IF (x_return_status = 'E' OR x_return_status = 'U') THEN
329 ----dbms_output.put_line('hello:2');
330 FOR i IN 1..x_msg_count LOOP
331 ----dbms_output.put_line('hello:3');
332 -- Standard call to get message count and if count is 1, get message info
333 fnd_msg_pub.get (p_msg_index => i
334 , p_encoded => 'F'
335 , p_data => x_msg_data
336 , p_msg_index_out => x_msg_count
337 );
338 ----dbms_output.put_line('In Public:x_msg_count '||x_msg_count);
339 ----dbms_output.put_line('In Public:x_msg_data '||x_msg_data);
340 END LOOP;
341 END IF;*/
342
343 --test_lease(1,'In Public:3');
344 EXCEPTION
345 WHEN fnd_api.g_exc_error
346 THEN
347 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
348 , p_count => x_msg_count
349 , p_data => x_msg_data
350 );
351 --ROLLBACK TO update_lease_pub;
352 x_return_status := fnd_api.g_ret_sts_error;
353 ------dbms_output.put_line('In Public:fnd_api.g_exc_error'||x_return_status);
354 --RAISE;
355 WHEN fnd_api.g_exc_unexpected_error
356 THEN
357 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
358 , p_count => x_msg_count
359 , p_data => x_msg_data
360 );
361 --ROLLBACK TO update_lease_pub;
362 x_return_status := fnd_api.g_ret_sts_unexp_error;
363 ------dbms_output.put_line('In Public:fnd_api.g_exc_unexpected_error'||x_return_status);
364 --RAISE;
365 WHEN OTHERS
366 THEN
367 --ROLLBACK TO update_lease_pub;
368 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
369 fnd_message.set_token ('P_TEXT', SQLERRM);
370 fnd_msg_pub.ADD;
371 x_return_status := fnd_api.g_ret_sts_error;
372 --RAISE;
373 END create_lease;
374
375 -- Procedure to update lease
376 PROCEDURE update_lease (
377 p_api_version IN NUMBER
378 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
379 , p_commit IN VARCHAR2 := fnd_api.g_false
380 , p_validate IN VARCHAR2 := fnd_api.g_false
381 , p_lease_rec IN OUT NOCOPY PN_LEASE_PVT.lease_rec
382 , p_lease_exp_rev_accounts_rec IN OUT NOCOPY PN_LEASE_PVT.lease_account_rec
383 , p_lease_accrual_account_rec IN OUT NOCOPY PN_LEASE_PVT.lease_account_rec
384 , p_lease_liab_rcvl_account_rec IN OUT NOCOPY PN_LEASE_PVT.lease_account_rec
385 , p_lease_contacts_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_contacts_tbl
386 , p_lease_tenancies_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_tenancies_tbl
387 , p_lease_context IN VARCHAR2 DEFAULT 'UPD'
388 , p_right_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_right_tbl_type
389 , p_note_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_note_tbl_type
390 , p_obligation_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_obligation_tbl_type
391 , p_insurance_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_insurance_tbl_type
392 , p_option_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_option_tbl_type
393 , p_lease_payment_term_tbl IN OUT NOCOPY PN_LEASE_PVT.lease_terms_tbl
394 -- , x_lease_id OUT NOCOPY NUMBER
395 -- , x_lease_number OUT NOCOPY VARCHAR2
396 -- , x_lease_name OUT NOCOPY VARCHAR2
397 , x_return_status OUT NOCOPY VARCHAR2
398 , x_msg_count OUT NOCOPY NUMBER
399 , x_msg_data OUT NOCOPY VARCHAR2
400 )
401
402 IS
403
404 l_api_version CONSTANT NUMBER := 1.0;
405 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_LEASE';
406 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name|| '.'|| l_api_name;
407 l_msg_count NUMBER;
408 l_msg_data VARCHAR2 (2000);
409 l_debug_mode VARCHAR2 (1);
410 BEGIN
411 -- Standard start of API savepoint
412 --SAVEPOINT update_lease_pub;
413 l_debug_mode := NVL (fnd_profile.VALUE ('PN_DEBUG_MODE'), 'N');
414 ------dbms_output.put_line ( 'In Public:p_lease_rec.lease_id'|| p_lease_rec.lease_id); --Rupak
415
416 --test_lease(1,'In Public');
417 --test_lease(1,'In Public-01:'||p_lease_rec.lease_id);
418 -- Initialize message list if p_init_msg_list is set to TRUE
419 IF fnd_api.to_boolean (p_init_msg_list)
420 THEN
421 fnd_msg_pub.initialize;
422 END IF;
423
424 IF l_debug_mode = 'Y'
425 THEN
426 pn_debug.set_curr_function (p_function => 'UPDATE_LEASE'
427 , p_debug_mode => l_debug_mode);
428 END IF;
429
430 -- Standard call to check for call compatibility
431 IF NOT fnd_api.compatible_api_call (l_api_version
432 , p_api_version
433 , l_api_name
434 , g_pkg_name
435 )
436 THEN
437 RAISE fnd_api.g_exc_error;
438 END IF;
439
440 x_return_status := fnd_api.g_ret_sts_success;
441
442
443 IF ( p_lease_rec.lease_id IS NULL OR p_lease_rec.lease_id = pn_lease_utils.g_pn_miss_num) THEN
444 IF ( p_lease_rec.lease_name IS NOT NULL AND p_lease_rec.lease_name <> pn_lease_utils.g_pn_miss_char) THEN
445 BEGIN
446
447 SELECT lease_id
448 INTO p_lease_rec.lease_id
449 FROM pn_leases_all
450 WHERE name = p_lease_rec.lease_name
451 AND org_id = g_org_id;
452
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 p_lease_rec.lease_id := NULL;
456 fnd_message.set_name ('PN', 'PN_API_ALL_NULL_PARAMETER');
457 fnd_message.set_token ('API_NAME', l_api_name_full);
458 fnd_message.set_token ('NULL_PARAM', 'LEASE NAME:NULL');
459 fnd_msg_pub.ADD;
460 RAISE fnd_api.g_exc_error;
461 WHEN TOO_MANY_ROWS THEN
462 fnd_message.set_name ('PN', 'PN_API_ALL_NULL_PARAMETER');
463 fnd_message.set_token ('API_NAME', l_api_name_full);
464 fnd_message.set_token ('NULL_PARAM', 'LEASE NAME:NOT UNIQUE');
465 fnd_msg_pub.ADD;
466 RAISE fnd_api.g_exc_error;
467 END;
468 ELSIF(p_lease_rec.lease_num IS NOT NULL AND p_lease_rec.lease_num <> pn_lease_utils.g_pn_miss_char) THEN
469 BEGIN
470 SELECT lease_id
471 INTO p_lease_rec.lease_id
472 FROM pn_leases_all
473 WHERE lease_num = p_lease_rec.lease_num
474 AND org_id = g_org_id;
475 EXCEPTION
476 WHEN NO_DATA_FOUND THEN
477 p_lease_rec.lease_id := NULL;
478 fnd_message.set_name ('PN', 'PN_API_ALL_NULL_PARAMETER');
479 fnd_message.set_token ('API_NAME', l_api_name_full);
480 fnd_message.set_token ('NULL_PARAM', 'LEASE NAME:NULL');
481 fnd_msg_pub.ADD;
482 RAISE fnd_api.g_exc_error;
483 END;
484 END IF;
485 END IF;
486
487
488
489 pn_lease_pvt.update_lease
490 (p_api_version => p_api_version
491 , p_init_msg_list => p_init_msg_list
492 , p_commit => p_commit
493 , p_validate => p_validate
494 , p_lease_rec => p_lease_rec
495 , p_lease_exp_rev_accounts_rec => p_lease_exp_rev_accounts_rec
496 , p_lease_accrual_account_rec => p_lease_accrual_account_rec
497 , p_lease_liab_rcvl_account_rec => p_lease_liab_rcvl_account_rec
498 , p_lease_contacts_tbl => p_lease_contacts_tbl
499 , p_lease_tenancies_tbl => p_lease_tenancies_tbl
500 , p_lease_context => p_lease_context
501 , p_right_tbl => p_right_tbl
502 , p_note_tbl => p_note_tbl
503 , p_obligation_tbl => p_obligation_tbl
504 , p_insurance_tbl => p_insurance_tbl
505 , p_option_tbl => p_option_tbl
506 , p_lease_payment_term_tbl => p_lease_payment_term_tbl
507 -- , x_lease_id => x_lease_id
508 -- , x_lease_number => x_lease_number
509 -- , x_lease_name => x_lease_name
510 , x_return_status => x_return_status
511 , x_msg_count => x_msg_count
512 , x_msg_data => x_msg_data
513 );
514 ----dbms_output.put_line('In Public:AFTER Calling PVT Update:1');--Rupak
515 IF fnd_api.to_boolean (p_commit)
516 THEN
517 COMMIT;
518 END IF;
519
520 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
521 , p_count => x_msg_count
522 , p_data => x_msg_data
523 );
524 ----dbms_output.put_line('In Public:AFTER Calling PVT Update:2');--Rupak
525 EXCEPTION
526 WHEN fnd_api.g_exc_error
527 THEN
528 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
529 , p_count => x_msg_count
530 , p_data => x_msg_data
531 );
532 --ROLLBACK TO update_lease_pub;
533 x_return_status := fnd_api.g_ret_sts_error;
534 ------dbms_output.put_line('In Public:fnd_api.g_exc_error'||x_return_status);
535 --RAISE;
536 WHEN fnd_api.g_exc_unexpected_error
537 THEN
538 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
539 , p_count => x_msg_count
540 , p_data => x_msg_data
541 );
542 --ROLLBACK TO update_lease_pub;
543 x_return_status := fnd_api.g_ret_sts_unexp_error;
544 ------dbms_output.put_line('In Public:fnd_api.g_exc_unexpected_error'||x_return_status);
545 --RAISE;
546 WHEN OTHERS
547 THEN
548 --ROLLBACK TO update_lease_pub;
549 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
550 fnd_message.set_token ('P_TEXT', SQLERRM);
551 fnd_msg_pub.ADD;
552 x_return_status := fnd_api.g_ret_sts_error;
553 --RAISE;
554 END update_lease;
555
556
557 --Procedure to Create Payment terms
558
559 PROCEDURE create_terms (
560 p_api_version IN NUMBER
561 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
562 , p_commit IN VARCHAR2 := fnd_api.g_false
563 , p_validate IN VARCHAR2 := fnd_api.g_false
564 , p_payment_terms_tbl IN OUT NOCOPY pn_lease_pvt.lease_terms_tbl
565 , p_lease_id IN NUMBER
566 , p_lease_context IN VARCHAR2 DEFAULT NULL
567 , x_return_status OUT NOCOPY VARCHAR2
568 , x_msg_count OUT NOCOPY NUMBER
569 , x_msg_data OUT NOCOPY VARCHAR2
570 )
571
572 AS
573 l_api_version CONSTANT NUMBER := 1.0;
574 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_TERMS';
575 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name|| '.'|| l_api_name;
576 l_msg_count NUMBER;
577 l_msg_data VARCHAR2 (2000);
578 l_debug_mode VARCHAR2 (1);
579 BEGIN
580 l_debug_mode := NVL (fnd_profile.VALUE ('PN_DEBUG_MODE'), 'N');
581 IF fnd_api.to_boolean (p_init_msg_list)
582 THEN
583 fnd_msg_pub.initialize;
584 END IF;
585
586 IF l_debug_mode = 'Y'
587 THEN
588 pn_debug.set_curr_function (p_function => 'CREATE_TERMS'
589 , p_debug_mode => l_debug_mode);
590 END IF;
591
592 -- Standard call to check for call compatibility
593 IF NOT fnd_api.compatible_api_call (l_api_version
594 , p_api_version
595 , l_api_name
596 , g_pkg_name
597 )
598 THEN
599 RAISE fnd_api.g_exc_error;
600 END IF;
601
602 x_return_status := fnd_api.g_ret_sts_success;
603 ------dbms_output.put_line (' IN Public :B4 Call of the Procedure:create_lease:p_payment_terms_tbl'||p_payment_terms_tbl(1).payment_purpose);
604 pn_lease_pvt.create_terms (p_api_version => p_api_version
605 , p_init_msg_list => p_init_msg_list
606 , p_commit => p_commit
607 , p_validate => p_validate
608 , p_payment_terms_tbl => p_payment_terms_tbl
609 , p_lease_id => p_lease_id
610 , p_lease_context => p_lease_context
611 , x_return_status => x_return_status
612 , x_msg_count => x_msg_count
613 , x_msg_data => x_msg_data
614 );
615
616 IF fnd_api.to_boolean (p_commit)
617 THEN
618 COMMIT;
619 END IF;
620 ------dbms_output.put_line (' IN Public :AFTER Call of the Procedure:create_lease');
621
622 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
623 , p_count => x_msg_count
624 , p_data => x_msg_data
625 );
626
627
628 EXCEPTION
629 WHEN fnd_api.g_exc_error
630 THEN
631 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
632 , p_count => x_msg_count
633 , p_data => x_msg_data
634 );
635 --ROLLBACK TO update_lease_pub;
636 x_return_status := fnd_api.g_ret_sts_error;
637 ------dbms_output.put_line('In Public:fnd_api.g_exc_error'||x_return_status);
638 --RAISE;
639 WHEN fnd_api.g_exc_unexpected_error
640 THEN
641 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
642 , p_count => x_msg_count
643 , p_data => x_msg_data
644 );
645 --ROLLBACK TO update_lease_pub;
646 x_return_status := fnd_api.g_ret_sts_unexp_error;
647 ------dbms_output.put_line('In Public:fnd_api.g_exc_unexpected_error'||x_return_status);
648 --RAISE;
649 WHEN OTHERS THEN
650 x_return_status := fnd_api.g_ret_sts_error;
651
652 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
653 , p_count => x_msg_count
654 , p_data => x_msg_data
655 );
656
657 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
658 fnd_message.set_token ('P_TEXT', SQLERRM);
659 fnd_msg_pub.ADD;
660 --RAISE;
661 END create_terms;
662
663 -- Procedure to update an payment terms
664
665
666 PROCEDURE update_terms (
667 p_api_version IN NUMBER
668 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
669 , p_commit IN VARCHAR2 := FND_API.G_FALSE
670 , p_validate IN VARCHAR2 := FND_API.G_FALSE
671 , p_payment_terms_tbl IN OUT NOCOPY pn_lease_pvt.lease_terms_tbl
672 , p_lease_id IN NUMBER
673 , p_lease_context IN VARCHAR2 DEFAULT NULL
674 , x_return_status OUT NOCOPY VARCHAR2
675 , x_msg_count OUT NOCOPY NUMBER
676 , x_msg_data OUT NOCOPY VARCHAR2
677 )
678
679 AS
680 l_api_version CONSTANT NUMBER := 1.0;
681 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TERMS';
682 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name|| '.'|| l_api_name;
683 l_msg_count NUMBER;
684 l_msg_data VARCHAR2 (2000);
685 l_debug_mode VARCHAR2 (1);
686 BEGIN
687 l_debug_mode := NVL (fnd_profile.VALUE ('PN_DEBUG_MODE'), 'N');
688 IF fnd_api.to_boolean (p_init_msg_list)
689 THEN
690 fnd_msg_pub.initialize;
691 END IF;
692
693 IF l_debug_mode = 'Y'
694 THEN
695 pn_debug.set_curr_function (p_function => 'CREATE_TERMS'
696 , p_debug_mode => l_debug_mode);
697 END IF;
698
699 -- Standard call to check for call compatibility
700 IF NOT fnd_api.compatible_api_call (l_api_version
701 , p_api_version
702 , l_api_name
703 , g_pkg_name
704 )
705 THEN
706 RAISE fnd_api.g_exc_error;
707 END IF;
708
709
710
711 x_return_status := fnd_api.g_ret_sts_success;
712 ------dbms_output.put_line (' IN Public :B4 Call of the Procedure:update_lease:p_payment_terms_tbl'||p_payment_terms_tbl(1).payment_purpose);
713 pn_lease_pvt.update_terms (p_api_version => p_api_version
714 , p_init_msg_list => p_init_msg_list
715 , p_commit => p_commit
716 , p_validate => p_validate
717 , p_payment_terms_tbl => p_payment_terms_tbl
718 , p_lease_id => p_lease_id
719 , p_lease_context => p_lease_context
720 , x_return_status => x_return_status
721 , x_msg_count => x_msg_count
722 , x_msg_data => x_msg_data
723 );
724 IF fnd_api.to_boolean (p_commit)
725 THEN
726 COMMIT;
727 END IF;
728
729 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
730 , p_count => x_msg_count
731 , p_data => x_msg_data
732 );
733
734 ------dbms_output.put_line (' IN Public :AFTER Call of the Procedure:update_lease');
735 EXCEPTION
736 WHEN fnd_api.g_exc_error
737 THEN
738 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
739 , p_count => x_msg_count
740 , p_data => x_msg_data
741 );
742 --ROLLBACK TO update_lease_pub;
743 x_return_status := fnd_api.g_ret_sts_error;
744 ------dbms_output.put_line('In Public:fnd_api.g_exc_error'||x_return_status);
745 --RAISE;
746 WHEN fnd_api.g_exc_unexpected_error
747 THEN
748 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
749 , p_count => x_msg_count
750 , p_data => x_msg_data
751 );
752 --ROLLBACK TO update_lease_pub;
753 x_return_status := fnd_api.g_ret_sts_unexp_error;
754 ------dbms_output.put_line('In Public:fnd_api.g_exc_unexpected_error'||x_return_status);
755 --RAISE;
756 WHEN OTHERS THEN
757 x_return_status := fnd_api.g_ret_sts_error;
758
759 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
760 , p_count => x_msg_count
761 , p_data => x_msg_data
762 );
763
764 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
765 fnd_message.set_token ('P_TEXT', SQLERRM);
766 fnd_msg_pub.ADD;
767 --RAISE;
768 ------dbms_output.put_line (' EXCEPTION:IN Public :update_lease'||SQLERRM);
769 END update_terms;
770
771 PROCEDURE update_status( p_api_version IN NUMBER
772 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
773 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
774 , p_lease_id IN OUT NOCOPY NUMBER
775 , p_new_approval_status_code IN OUT NOCOPY VARCHAR2
776 , p_new_lease_status IN OUT NOCOPY VARCHAR2
777 , p_operation IN VARCHAR2 DEFAULT 'UPDATE_STATUS'
778 , x_return_status OUT NOCOPY VARCHAR2
779 , x_msg_count OUT NOCOPY NUMBER
780 , x_msg_data OUT NOCOPY VARCHAR2
781 )
782 IS
783
784 -- Below global variables to be removed at the time of plugging this procedure into pn_lease_pkg
785 -- g_pkg_name VARCHAR2(30);
786 g_program_unit CONSTANT VARCHAR2 (30) := 'update_status';
787 -- g_org_id NUMBER;
788 --------------------------------------
789
790 l_api_version CONSTANT NUMBER := 1.0;
791 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_STATUS';
792 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name|| '.'|| l_api_name;
793
794
795
796
797 BEGIN
798 -- Standard start of API savepoint
799
800 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - Start of status update procedure', 3);
801
802 SAVEPOINT update_status;
803
804 -- Set API return status to success
805 x_return_status := fnd_api.g_ret_sts_success;
806
807 -- Initialize message list if p_init_msg_list is set to TRUE
808
809 IF fnd_api.to_boolean (p_init_msg_list)
810 THEN
811
812 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - Initializing message list', 3);
813
814 fnd_msg_pub.initialize;
815 END IF;
816
817 -- Standard call to check for call compatibility
818 IF NOT fnd_api.compatible_api_call (l_api_version
819 , p_api_version
820 , l_api_name -- CREATE_LEASE
821 , g_pkg_name -- PN_LEASE_PUB
822 )
823 THEN
824 RAISE fnd_api.g_exc_error;
825 END IF;
826
827 -- Check if passed org_id is valid or not
828 IF NVL (mo_global.check_valid_org (g_org_id), 'N') <> 'Y'
829 THEN
830
831 fnd_message.set_name ('MO', 'MO_ORG_INVALID');
832 fnd_msg_pub.ADD;
833 RAISE fnd_api.g_exc_error;
834 END IF;
835
836 /*********************************************/
837 -- Required Parameters Validation
838 /*********************************************/
839
840 -- -------------------------------------------
841 -- Make sure the Lease Type is not null
842 -- -------------------------------------------
843
844 IF ( p_lease_id IS NULL OR p_lease_id = pn_lease_utils.g_pn_miss_num
845 )
846 THEN
847 pn_lease_utils.add_null_parameter_msg
848 (p_token_apiname => l_api_name_full
849 , p_token_nullparam => 'p_lease_id');
850 RAISE fnd_api.g_exc_error;
851 END IF;
852
853 -- -------------------------------------------
854 -- Make sure the new approval status_code is not null
855 -- -------------------------------------------
856
857 IF p_new_approval_status_code IS NULL OR p_new_approval_status_code = pn_lease_utils.g_pn_miss_char
858 THEN
859 pn_lease_utils.add_null_parameter_msg
860 (p_token_apiname => l_api_name_full
861 , p_token_nullparam => 'p_new_approval_status_code');
862 RAISE fnd_api.g_exc_error;
863 END IF;
864
865 -- -------------------------------------------
866 -- Make sure the new lease status is not null
867 -- -------------------------------------------
868 IF p_new_lease_status IS NULL OR p_new_lease_status= pn_lease_utils.g_pn_miss_char
869 THEN
870 pn_lease_utils.add_null_parameter_msg
871 (p_token_apiname => l_api_name_full
872 , p_token_nullparam => 'p_new_lease_status');
873 RAISE fnd_api.g_exc_error;
874 END IF;
875
876
877 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - Calling update_status procedure.. ', 3);
878
879 pn_lease_pvt.update_status( p_lease_id => p_lease_id
880 , p_new_approval_status_code => p_new_approval_status_code
881 , p_new_lease_status => p_new_lease_status
882 , p_operation => p_operation
883 , x_return_status => x_return_status
884 , x_msg_count => x_msg_count
885 , x_msg_data => x_msg_data
886 );
887 --pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - update_status procedure x_return_status: '||x_return_status, 3);
888 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - update_status procedure x_return_status: '||x_return_status, 3);
889
890 -- Standard check of p_commit
891 IF fnd_api.to_boolean (p_commit)
892 THEN
893 COMMIT;
894 END IF;
895
896 -- Get the messages and count from the message stack
897 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
898 , p_count => x_msg_count
899 , p_data => x_msg_data
900 );
901
902 EXCEPTION
903 WHEN fnd_api.g_exc_error
904 THEN
905 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
906 , p_count => x_msg_count
907 , p_data => x_msg_data
908 );
909 --ROLLBACK TO update_status;
910 x_return_status := fnd_api.g_ret_sts_error;
911 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - In public update_status api: fnd_api.g_exc_error: '||x_return_status, 3);
912
913 RAISE;
914 WHEN fnd_api.g_exc_unexpected_error
915 THEN
916 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false
917 , p_count => x_msg_count
918 , p_data => x_msg_data
919 );
920 --ROLLBACK TO update_status;
921 x_return_status := fnd_api.g_ret_sts_unexp_error;
922 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - In public update_status api: fnd_api.g_ret_sts_unexp_error: '||x_return_status, 3);
923 RAISE;
924 WHEN OTHERS
925 THEN
926 --ROLLBACK TO update_status;
927 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
928 fnd_message.set_token ('P_TEXT', SQLERRM);
929 fnd_msg_pub.ADD;
930 x_return_status := fnd_api.g_ret_sts_error;
931 pn_lease_pvt.pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - In public update_status api: unknown error '||x_return_status, 3);
932 RAISE;
933 END update_status;
934
935 END pn_lease_pub;