[Home] [Help]
PACKAGE BODY: APPS.PN_OPEX_TERMS_PKG
Source
1 PACKAGE BODY PN_OPEX_TERMS_PKG AS
2 -- $Header: PNOTERMB.pls 120.2 2007/10/11 06:27:45 rthumma noship $
3
4 -------------------------------------------------------------------
5 -- PROCEDURE CREATE_OPEX_PAYMENT_TERMS
6 -------------------------------------------------------------------
7 PROCEDURE create_opex_payment_terms(
8 p_est_payment_id IN NUMBER,
9 p_term_template_id IN NUMBER DEFAULT NULL,
10 p_lease_id IN NUMBER,
11 x_payment_term_id OUT NOCOPY NUMBER,
12 x_catch_up_term_id OUT NOCOPY NUMBER,
13 x_return_status IN OUT NOCOPY VARCHAR2
14 ) IS
15 l_lease_class_code pn_leases.lease_class_code%TYPE;
16 l_distribution_id pn_distributions.distribution_id%TYPE;
17 l_lease_change_id pn_lease_details.lease_change_id%TYPE;
18 l_rowid ROWID;
19 l_distribution_count NUMBER := 0;
20 l_inv_start_date DATE;
21 l_payment_start_date DATE;
22 l_payment_end_date DATE;
23 l_frequency pn_payment_terms_all.frequency_code%type;
24 l_schedule_day pn_payment_terms_all.schedule_day%type;
25 l_set_of_books_id gl_sets_of_books.set_of_books_id%type;
26 l_context varchar2(2000);
27 l_area pn_payment_terms_all.area%TYPE;
28 l_area_type_code pn_payment_terms_all.area_type_code%TYPE;
29 l_org_id NUMBER;
30 l_schedule_day_char VARCHAR2(8);
31 l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%type;
32 i_cnt number;
33 l_est_payment_term_id pn_payment_terms_all.payment_term_id%TYPE;
34 l_catch_up_payment_term_id pn_payment_terms_all.payment_term_id%TYPE;
35 l_currency_code pn_payment_terms_all.currency_code%TYPE;
36
37
38 CURSOR opex_est_pay_cur(est_pay_trm_id IN NUMBER)
39 IS
40 SELECT *
41 FROM pn_opex_est_payments_all
42 WHERE est_payment_id = est_pay_trm_id;
43
44 CURSOR term_template_cur (term_temp_id IN NUMBER)
45 IS
46 SELECT *
47 FROM pn_term_templates_all
48 WHERE term_template_id = term_temp_id;
49
50 CURSOR agreement_cur(agr_id IN NUMBER)
51 IS
52 SELECT agr.* , loc.location_id
53 FROM pn_opex_agreements_all agr,
54 pn_locations_all loc,
55 pn_tenancies_all ten
56 WHERE agreement_id = agr_id
57 AND agr.tenancy_id = ten.tenancy_id
58 AND ten.location_id = loc.location_id;
59
60 CURSOR distributions_cur (term_temp_id IN NUMBER)
61 IS
62 SELECT *
63 FROM pn_distributions_all
64 WHERE term_template_id = term_temp_id;
65
66
67
68 -- Used to default the previous terms values and
69 -- could be used in case contraction is to be done.
70 CURSOR prev_pay_term_cur (arg_id IN NUMBER)
71 IS
72 SELECT * FROM pn_payment_terms_all
73 WHERE payment_term_id =
74 (SELECT MAX(payment_term_id) FROM pn_payment_terms_all
75 WHERE opex_agr_id = arg_id
76 AND opex_type = 'ESTPMT');
77
78 template_rec pn_term_templates_all%ROWTYPE;
79 opex_est_pay_rec opex_est_pay_cur%ROWTYPE;
80 agreement_rec agreement_cur%ROWTYPE;
81 distributions_rec distributions_cur%ROWTYPE;
82 pay_term_rec prev_pay_term_cur%ROWTYPE;
83
84
85 BEGIN
86 x_return_status := 'S';
87 SAVEPOINT create_term;
88 --dbms_output.put_line('Testing');
89
90 pnp_debug_pkg.put_log_msg ('opex_create_payment_term');
91
92 l_context := 'Validating input parameters';
93
94 IF (p_est_payment_id IS NULL OR
95 p_lease_id IS NULL ) THEN
96 pnp_debug_pkg.put_log_msg ('Input Prameters missing');
97 END IF;
98
99
100
101 l_context := 'Getting lease class code and lease change id';
102 BEGIN
103 SELECT pl.lease_class_code,
104 pld.lease_change_id,
105 pl.org_id
106 INTO l_lease_class_code,
107 l_lease_change_id,
108 l_org_id
109 FROM pn_leases_all pl,
110 pn_lease_details_all pld
111 WHERE pl.lease_id = pld.lease_id
112 AND pld.lease_id = p_lease_id;
113
114 EXCEPTION
115 WHEN TOO_MANY_ROWS THEN
116 pnp_debug_pkg.put_log_msg ('Cannot Get Main Lease Details - TOO_MANY_ROWS');
117 WHEN NO_DATA_FOUND THEN
118 pnp_debug_pkg.put_log_msg ('Cannot Get Main Lease Details - NO_DATA_FOUND');
119 WHEN OTHERS THEN
120 pnp_debug_pkg.put_log_msg ('Cannot Get Main Lease Details - Unknown Error:'|| SQLERRM);
121 END;
122
123 l_context := 'Getting set of books id';
124 --dbms_output.put_line('getting set of books');
125
126 l_set_of_books_id := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID'
127 ,l_org_id));
128
129 pnp_debug_pkg.put_log_msg ('create_payment_terms - Set of books id :'||l_set_of_books_id);
130
131 IF p_term_template_id IS NOT NULL THEN
132
133 l_context := 'opening cursor csr_template';
134 OPEN term_template_cur(p_term_template_id);
135 FETCH term_template_cur INTO template_rec;
136 IF term_template_cur%NOTFOUND THEN
137 pnp_debug_pkg.put_log_msg ('No template dat found');
138 RAISE NO_DATA_FOUND;
139 END IF;
140 CLOSE term_template_cur;
141 END IF;
142
143 --dbms_output.put_line('template cur');
144
145 l_context := 'opening est terms table';
146
147 OPEN opex_est_pay_cur(p_est_payment_id);
148 FETCH opex_est_pay_CUR INTO opex_est_pay_rec;
149 IF opex_est_pay_cur%NOTFOUND THEN
150 pnp_debug_pkg.put_log_msg ('No template dat found');
151 RAISE NO_DATA_FOUND;
152 END IF;
153 CLOSE opex_est_pay_cur;
154
155 --dbms_output.put_line('est Table cur');
156
157 l_context := 'opening agreement_cur';
158
159 OPEN agreement_cur(opex_est_pay_rec.agreement_id);
160 FETCH agreement_cur INTO agreement_rec;
161 IF agreement_cur%NOTFOUND THEN
162 pnp_debug_pkg.put_log_msg ('No template dat found');
163 RAISE NO_DATA_FOUND ;
164 END IF;
165 CLOSE agreement_cur;
166
167 --dbms_output.put_line('agreement curr');
168
169 OPEN prev_pay_term_cur(opex_est_pay_rec.agreement_id);
170 FETCH prev_pay_term_cur INTO pay_term_rec;
171 IF prev_pay_term_cur%NOTFOUND THEN
172 pnp_debug_pkg.put_log_msg ('No template dat found');
173 pay_term_rec := NULL;
174 END IF;
175 CLOSE prev_pay_term_cur;
176
177
178 IF l_lease_class_code = 'DIRECT' THEN
179 /* lease is of class: DIRECT */
180 template_rec.customer_id := NULL;
181 template_rec.customer_site_use_id := NULL;
182 template_rec.cust_ship_site_id := NULL;
183 template_rec.cust_trx_type_id := NULL;
184 template_rec.inv_rule_id := NULL;
185 template_rec.account_rule_id := NULL;
186 template_rec.salesrep_id := NULL;
187 template_rec.cust_po_number := NULL;
188 template_rec.receipt_method_id := NULL;
189 ELSE
190 /* lease is 'sub-lease' or third-party */
191 template_rec.project_id := NULL;
192 template_rec.task_id := NULL;
193 template_rec.organization_id := NULL;
194 template_rec.expenditure_type := NULL;
195 template_rec.expenditure_item_date := NULL;
196 template_rec.vendor_id := NULL;
197 template_rec.vendor_site_id := NULL;
198 template_rec.tax_group_id := NULL;
199 template_rec.distribution_set_id := NULL;
200 template_rec.po_header_id := NULL;
201 END IF;
202
203 IF pn_r12_util_pkg.is_r12 THEN
204 template_rec.tax_group_id := null;
205 template_rec.tax_code_id := null;
206 ELSE
207 template_rec.tax_classification_code := null;
208 END IF;
209
210 --dbms_output.put_line('setting main values');
211
212
213 -- put the start date and the end for the payment term from est_pay term
214
215 l_payment_start_date := opex_est_pay_rec.start_date;
216 l_payment_end_date := opex_est_pay_rec.end_date;
217
218 l_context := 'Setting frequency and schedule day';
219
220
221 null;
222
223
224 l_frequency := agreement_rec.est_pay_freq_code;
225 l_schedule_day := to_char(l_payment_start_date,'dd');
226 l_currency_code := NVL(agreement_rec.est_pay_currency_code , template_rec.currency_code);
227 --dbms_output.put_line('set freq and sch day');
228
229
230 -- Need to check how to get the location.
231
232 IF agreement_rec.location_id IS NOT NULL AND
233 l_payment_start_date IS NOT NULL THEN
234
235 l_area_type_code := 'LOCTN_RENTABLE';
236 l_area := pnp_util_func.fetch_tenancy_area(
237 p_lease_id => p_lease_id,
238 p_location_id => agreement_rec.location_id,
239 p_as_of_date => l_payment_start_date,
240 p_area_type_code => l_area_type_code);
241
242 END IF;
243
244 -- For estimated term
245
246
247 IF NVL(opex_est_pay_rec.est_pmt_amount,0) <> 0 THEN
248
249 --dbms_output.put_line('tttttt'||agreement_rec.est_pay_freq_code);
250
251 --dbms_output.put_line('inserting row 1');
252 --dbms_output.put_line('x_last_update_login' || NVL(fnd_profile.value('LOGIN_ID'),0));
253 --dbms_output.put_line('x_last_updated_by '|| NVL (fnd_profile.VALUE ('USER_ID'), 0));
254 --dbms_output.put_line('x_payment_purpose_code' || NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code) );
255 --dbms_output.put_line('x_payment_term_type_code' || NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code));
256 --dbms_output.put_line('x_frequency_code' || NVL(agreement_rec.est_pay_freq_code , l_frequency));
257 --dbms_output.put_line('x_lease_id' || p_lease_id);
258 --dbms_output.put_line('x_lease_change_id' || l_lease_change_id);
259 --dbms_output.put_line('l_payment_start_date' || l_payment_start_date);
260 --dbms_output.put_line('x_end_date' || l_payment_end_date);
261 --dbms_output.put_line('x_currency_code' || l_currency_code);
262 --dbms_output.put_line('x_set_of_books_id' || NVL(template_rec.set_of_books_id,l_set_of_books_id));
263
264 pnp_debug_pkg.put_log_msg ('opex_create_payment_term');
265 pnp_debug_pkg.put_log_msg('inserting row 1');
266 pnp_debug_pkg.put_log_msg('x_last_update_login' || NVL(fnd_profile.value('LOGIN_ID'),0));
267 pnp_debug_pkg.put_log_msg('x_last_updated_by '|| NVL (fnd_profile.VALUE ('USER_ID'), 0));
268 pnp_debug_pkg.put_log_msg('x_payment_purpose_code' || NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code) );
269 pnp_debug_pkg.put_log_msg('x_payment_term_type_code' || NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code));
270 pnp_debug_pkg.put_log_msg('x_frequency_code' || NVL(agreement_rec.est_pay_freq_code , l_frequency));
271 pnp_debug_pkg.put_log_msg('x_lease_id' || agreement_rec.lease_id);
272 pnp_debug_pkg.put_log_msg('x_lease_change_id' || l_lease_change_id);
273 pnp_debug_pkg.put_log_msg('l_payment_start_date' || l_payment_start_date);
274 pnp_debug_pkg.put_log_msg('x_end_date' || l_payment_end_date);
275 pnp_debug_pkg.put_log_msg('x_currency_code' || l_currency_code);
276 pnp_debug_pkg.put_log_msg('x_set_of_books_id' || NVL(template_rec.set_of_books_id,l_set_of_books_id));
277
278 -- We retain the previous term's changes made. hence
279 -- if a previous term exists we store the values present
280 -- in the previous term , else we store the values of the
281 -- template.
282
283
284 pnt_payment_terms_pkg.insert_row (
285 x_rowid => l_rowid
286 ,x_payment_term_id => l_est_payment_term_id
287 ,x_index_period_id => null
288 ,x_index_term_indicator => null
289 ,x_var_rent_inv_id => null
290 ,x_var_rent_type => null
291 ,x_last_update_date => SYSDATE
292 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
293 ,x_creation_date => SYSDATE
294 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
295 ,x_payment_purpose_code => NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code)
296 ,x_payment_term_type_code => NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code)
297 ,x_frequency_code => NVL(agreement_rec.est_pay_freq_code , l_frequency)
298 ,x_lease_id => p_lease_id
299 ,x_lease_change_id => l_lease_change_id
300 ,x_start_date => l_payment_start_date
301 ,x_end_date => l_payment_end_date
302 ,x_set_of_books_id => NVL(template_rec.set_of_books_id,l_set_of_books_id)
303 ,x_currency_code => l_currency_code
304 ,x_rate => 1 -- not used in application
305 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
306 ,x_vendor_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_id ELSE pay_term_rec.vendor_id END
307 ,x_vendor_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_site_id ELSE pay_term_rec.vendor_site_id END
308 ,x_target_date => NULL
309 ,x_actual_amount => opex_est_pay_rec.est_pmt_amount
310 ,x_estimated_amount => NULL
311 ,x_attribute_category => NVL(opex_est_pay_rec.attribute_category , template_rec.attribute_category)
312 ,x_attribute1 => NVL(opex_est_pay_rec.attribute1 , template_rec.attribute1)
313 ,x_attribute2 => NVL(opex_est_pay_rec.attribute2 , template_rec.attribute2)
314 ,x_attribute3 => NVL(opex_est_pay_rec.attribute3 , template_rec.attribute3)
315 ,x_attribute4 => NVL(opex_est_pay_rec.attribute4 , template_rec.attribute4)
316 ,x_attribute5 => NVL(opex_est_pay_rec.attribute5 , template_rec.attribute5)
317 ,x_attribute6 => NVL(opex_est_pay_rec.attribute6 , template_rec.attribute6)
318 ,x_attribute7 => NVL(opex_est_pay_rec.attribute7 , template_rec.attribute7)
319 ,x_attribute8 => NVL(opex_est_pay_rec.attribute8 , template_rec.attribute8)
320 ,x_attribute9 => NVL(opex_est_pay_rec.attribute9 , template_rec.attribute9)
321 ,x_attribute10 => NVL(opex_est_pay_rec.attribute10 , template_rec.attribute10)
322 ,x_attribute11 => NVL(opex_est_pay_rec.attribute11 , template_rec.attribute11)
323 ,x_attribute12 => NVL(opex_est_pay_rec.attribute12 , template_rec.attribute12)
324 ,x_attribute13 => NVL(opex_est_pay_rec.attribute13 , template_rec.attribute13)
325 ,x_attribute14 => NVL(opex_est_pay_rec.attribute14 , template_rec.attribute14)
326 ,x_attribute15 => NVL(opex_est_pay_rec.attribute15 , template_rec.attribute15)
327 ,x_project_attribute_category => NULL
328 ,x_project_attribute1 => NULL
329 ,x_project_attribute2 => NULL
330 ,x_project_attribute3 => NULL
331 ,x_project_attribute4 => NULL
332 ,x_project_attribute5 => NULL
333 ,x_project_attribute6 => NULL
334 ,x_project_attribute7 => NULL
335 ,x_project_attribute8 => NULL
336 ,x_project_attribute9 => NULL
337 ,x_project_attribute10 => NULL
338 ,x_project_attribute11 => NULL
339 ,x_project_attribute12 => NULL
340 ,x_project_attribute13 => NULL
341 ,x_project_attribute14 => NULL
342 ,x_project_attribute15 => NULL
343 ,x_customer_id => template_rec.customer_id
344 ,x_customer_site_use_id => template_rec.customer_site_use_id
345 ,x_normalize => 'N'
346 ,x_location_id => agreement_rec.location_id
347 ,x_schedule_day => l_schedule_day
348 ,x_cust_ship_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_ship_site_id ELSE pay_term_rec.cust_ship_site_id END
349 ,x_ap_ar_term_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.ap_ar_term_id ELSE pay_term_rec.ap_ar_term_id END
350 ,x_cust_trx_type_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_trx_type_id ELSE pay_term_rec.cust_trx_type_id END
351 ,x_project_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.project_id ELSE pay_term_rec.project_id END
352 ,x_task_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.task_id ELSE pay_term_rec.task_id END
353 ,x_organization_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.organization_id ELSE pay_term_rec.organization_id END
354 ,x_expenditure_type => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_type ELSE pay_term_rec.expenditure_type END
355 ,x_expenditure_item_date => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_item_date ELSE pay_term_rec.expenditure_item_date END
356 ,x_tax_group_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_group_id ELSE pay_term_rec.tax_group_id END
357 ,x_tax_code_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
358 ,x_tax_classification_code => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_classification_code ELSE pay_term_rec.tax_classification_code END
359 ,x_tax_included => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_included ELSE pay_term_rec.tax_included END
360 ,x_distribution_set_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.distribution_set_id ELSE pay_term_rec.distribution_set_id END
361 ,x_inv_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.inv_rule_id ELSE pay_term_rec.inv_rule_id END
362 ,x_account_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.account_rule_id ELSE pay_term_rec.account_rule_id END
363 ,x_salesrep_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.salesrep_id ELSE pay_term_rec.salesrep_id END
364 ,x_approved_by => NULL
365 ,x_status => 'DRAFT'
366 ,x_po_header_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
367 ,x_cust_po_number => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
368 ,x_receipt_method_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
369 --C ,x_calling_form => NULL
370 ,x_org_id => l_org_id
371 ,x_term_template_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.term_template_id ELSE pay_term_rec.term_template_id END
372 ,x_area => l_area
373 ,x_area_type_code => l_area_type_code
374 );
375
376 -- Updating the opex columns in the pn_payment_terms_all
377
378 UPDATE pn_payment_terms_all
379 SET opex_agr_id = agreement_rec.agreement_id,
380 opex_type = 'ESTPMT'
381 WHERE payment_term_id = l_est_payment_term_id;
382
383
384 END IF;
385
386 --dbms_output.put_line('inserted payment amt row ' || l_est_payment_term_id );
387
388 -- For catch up term
389
390 IF NVL(opex_est_pay_rec.catch_up_amount,0) <> 0 THEN
391
392 pnt_payment_terms_pkg.insert_row (
393 x_rowid => l_rowid
394 ,x_payment_term_id => l_catch_up_payment_term_id
395 ,x_index_period_id => null
396 ,x_index_term_indicator => null
397 ,x_var_rent_inv_id => null
398 ,x_var_rent_type => null
399 ,x_last_update_date => SYSDATE
400 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
401 ,x_creation_date => SYSDATE
402 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
403 ,x_payment_purpose_code => NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code)
404 ,x_payment_term_type_code => NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code)
405 ,x_frequency_code => 'OT'
406 ,x_lease_id => p_lease_id
407 ,x_lease_change_id => l_lease_change_id
408 ,x_start_date => SYSDATE -- Defaulted to sysdate for catchup terms
409 ,x_end_date => SYSDATE -- Defaulted to sysdate for catchup terms
410 ,x_set_of_books_id => NVL(template_rec.set_of_books_id,l_set_of_books_id)
411 ,x_currency_code => l_currency_code
412 ,x_rate => 1 -- not used in application
413 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
414 ,x_vendor_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_id ELSE pay_term_rec.vendor_id END
415 ,x_vendor_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_site_id ELSE pay_term_rec.vendor_site_id END
416 ,x_target_date => NULL
417 ,x_actual_amount => opex_est_pay_rec.catch_up_amount
418 ,x_estimated_amount => NULL
419 ,x_attribute_category => NVL(opex_est_pay_rec.attribute_category , template_rec.attribute_category)
420 ,x_attribute1 => NVL(opex_est_pay_rec.attribute1 , template_rec.attribute1)
421 ,x_attribute2 => NVL(opex_est_pay_rec.attribute2 , template_rec.attribute2)
422 ,x_attribute3 => NVL(opex_est_pay_rec.attribute3 , template_rec.attribute3)
423 ,x_attribute4 => NVL(opex_est_pay_rec.attribute4 , template_rec.attribute4)
424 ,x_attribute5 => NVL(opex_est_pay_rec.attribute5 , template_rec.attribute5)
425 ,x_attribute6 => NVL(opex_est_pay_rec.attribute6 , template_rec.attribute6)
426 ,x_attribute7 => NVL(opex_est_pay_rec.attribute7 , template_rec.attribute7)
427 ,x_attribute8 => NVL(opex_est_pay_rec.attribute8 , template_rec.attribute8)
428 ,x_attribute9 => NVL(opex_est_pay_rec.attribute9 , template_rec.attribute9)
429 ,x_attribute10 => NVL(opex_est_pay_rec.attribute10 , template_rec.attribute10)
430 ,x_attribute11 => NVL(opex_est_pay_rec.attribute11 , template_rec.attribute11)
431 ,x_attribute12 => NVL(opex_est_pay_rec.attribute12 , template_rec.attribute12)
432 ,x_attribute13 => NVL(opex_est_pay_rec.attribute13 , template_rec.attribute13)
433 ,x_attribute14 => NVL(opex_est_pay_rec.attribute14 , template_rec.attribute14)
434 ,x_attribute15 => NVL(opex_est_pay_rec.attribute15 , template_rec.attribute15)
435 ,x_project_attribute_category => NULL
436 ,x_project_attribute1 => NULL
437 ,x_project_attribute2 => NULL
438 ,x_project_attribute3 => NULL
439 ,x_project_attribute4 => NULL
440 ,x_project_attribute5 => NULL
441 ,x_project_attribute6 => NULL
442 ,x_project_attribute7 => NULL
443 ,x_project_attribute8 => NULL
444 ,x_project_attribute9 => NULL
445 ,x_project_attribute10 => NULL
446 ,x_project_attribute11 => NULL
447 ,x_project_attribute12 => NULL
448 ,x_project_attribute13 => NULL
449 ,x_project_attribute14 => NULL
450 ,x_project_attribute15 => NULL
451 ,x_customer_id => template_rec.customer_id
452 ,x_customer_site_use_id => template_rec.customer_site_use_id
453 ,x_normalize => 'N'
454 ,x_location_id => agreement_rec.location_id
455 ,x_schedule_day => l_schedule_day
456 ,x_cust_ship_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_ship_site_id ELSE pay_term_rec.cust_ship_site_id END
457 ,x_ap_ar_term_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.ap_ar_term_id ELSE pay_term_rec.ap_ar_term_id END
458 ,x_cust_trx_type_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_trx_type_id ELSE pay_term_rec.cust_trx_type_id END
459 ,x_project_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.project_id ELSE pay_term_rec.project_id END
460 ,x_task_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.task_id ELSE pay_term_rec.task_id END
461 ,x_organization_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.organization_id ELSE pay_term_rec.organization_id END
462 ,x_expenditure_type => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_type ELSE pay_term_rec.expenditure_type END
463 ,x_expenditure_item_date => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_item_date ELSE pay_term_rec.expenditure_item_date END
464 ,x_tax_group_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_group_id ELSE pay_term_rec.tax_group_id END
465 ,x_tax_code_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
466 ,x_tax_classification_code => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_classification_code ELSE pay_term_rec.tax_classification_code END
467 ,x_tax_included => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_included ELSE pay_term_rec.tax_included END
468 ,x_distribution_set_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.distribution_set_id ELSE pay_term_rec.distribution_set_id END
469 ,x_inv_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.inv_rule_id ELSE pay_term_rec.inv_rule_id END
470 ,x_account_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.account_rule_id ELSE pay_term_rec.account_rule_id END
471 ,x_salesrep_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.salesrep_id ELSE pay_term_rec.salesrep_id END
472 ,x_approved_by => NULL
473 ,x_status => 'DRAFT'
474 ,x_po_header_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
475 ,x_cust_po_number => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
476 ,x_receipt_method_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
477 --C ,x_calling_form => NULL
478 ,x_org_id => l_org_id
479 ,x_term_template_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.term_template_id ELSE pay_term_rec.term_template_id END
480 ,x_area => l_area
481 ,x_area_type_code => l_area_type_code
482 );
483
484 -- Updating the opex columns in the pn_payment_terms_all
485
486 UPDATE pn_payment_terms_all
487 SET opex_agr_id = agreement_rec.agreement_id,
488 opex_type = 'CATCHUP'
489 WHERE payment_term_id = l_catch_up_payment_term_id;
490
491
492 END IF;
493 --dbms_output.put_line('inserted payment amt row ' || l_catch_up_payment_term_id );
494
495 l_distribution_count := 0;
496 l_context :='opening cursor csr_distributions';
497
498 IF l_est_payment_term_id IS NOT NULL THEN
499 FOR rec_distributions in distributions_cur(p_term_template_id)
500 LOOP
501 pnp_debug_pkg.put_log_msg(' account_id '||rec_distributions.account_id);
502 pnp_debug_pkg.put_log_msg(' account_class '||rec_distributions.account_id);
503 l_context := 'Inserting into pn_distributions';
504 pn_distributions_pkg.insert_row (
505 x_rowid => l_rowid
506 ,x_distribution_id => l_distribution_id
507 ,x_account_id => rec_distributions.account_id
508 ,x_payment_term_id => l_est_payment_term_id
509 ,x_term_template_id => NULL
510 ,x_account_class => rec_distributions.account_class
511 ,x_percentage => rec_distributions.percentage
512 ,x_line_number => rec_distributions.line_number
513 ,x_last_update_date => SYSDATE
514 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
515 ,x_creation_date => SYSDATE
516 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
517 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
518 ,x_attribute_category => rec_distributions.attribute_category
519 ,x_attribute1 => rec_distributions.attribute1
520 ,x_attribute2 => rec_distributions.attribute2
521 ,x_attribute3 => rec_distributions.attribute3
522 ,x_attribute4 => rec_distributions.attribute4
523 ,x_attribute5 => rec_distributions.attribute5
524 ,x_attribute6 => rec_distributions.attribute6
525 ,x_attribute7 => rec_distributions.attribute7
526 ,x_attribute8 => rec_distributions.attribute8
527 ,x_attribute9 => rec_distributions.attribute9
528 ,x_attribute10 => rec_distributions.attribute10
529 ,x_attribute11 => rec_distributions.attribute11
530 ,x_attribute12 => rec_distributions.attribute12
531 ,x_attribute13 => rec_distributions.attribute13
532 ,x_attribute14 => rec_distributions.attribute14
533 ,x_attribute15 => rec_distributions.attribute15
534 ,x_org_id => l_org_id
535 );
536 l_rowid := NULL;
537 l_distribution_id := NULL;
538 l_distribution_count := l_distribution_count + 1;
539 END LOOP;
540 l_context := 'exiting from loop';
541 END IF;
542
543 IF l_catch_up_payment_term_id IS NOT NULL THEN
544 FOR rec_distributions in distributions_cur(p_term_template_id)
545 LOOP
546 pnp_debug_pkg.put_log_msg(' account_id '||rec_distributions.account_id);
547 pnp_debug_pkg.put_log_msg(' account_class '||rec_distributions.account_id);
548 l_context := 'Inserting into pn_distributions';
549 pn_distributions_pkg.insert_row (
550 x_rowid => l_rowid
551 ,x_distribution_id => l_distribution_id
552 ,x_account_id => rec_distributions.account_id
553 ,x_payment_term_id => l_catch_up_payment_term_id
554 ,x_term_template_id => NULL
555 ,x_account_class => rec_distributions.account_class
556 ,x_percentage => rec_distributions.percentage
557 ,x_line_number => rec_distributions.line_number
558 ,x_last_update_date => SYSDATE
559 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
560 ,x_creation_date => SYSDATE
561 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
562 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
563 ,x_attribute_category => rec_distributions.attribute_category
564 ,x_attribute1 => rec_distributions.attribute1
565 ,x_attribute2 => rec_distributions.attribute2
566 ,x_attribute3 => rec_distributions.attribute3
567 ,x_attribute4 => rec_distributions.attribute4
568 ,x_attribute5 => rec_distributions.attribute5
569 ,x_attribute6 => rec_distributions.attribute6
570 ,x_attribute7 => rec_distributions.attribute7
571 ,x_attribute8 => rec_distributions.attribute8
572 ,x_attribute9 => rec_distributions.attribute9
573 ,x_attribute10 => rec_distributions.attribute10
574 ,x_attribute11 => rec_distributions.attribute11
575 ,x_attribute12 => rec_distributions.attribute12
576 ,x_attribute13 => rec_distributions.attribute13
577 ,x_attribute14 => rec_distributions.attribute14
578 ,x_attribute15 => rec_distributions.attribute15
579 ,x_org_id => l_org_id
580 );
581 l_rowid := NULL;
582 l_distribution_id := NULL;
583 l_distribution_count := l_distribution_count + 1;
584 END LOOP;
585 l_context := 'exiting from loop';
586
587 END IF;
588 --dbms_output.put_line('inserted dists ' );
589
590
591 x_payment_term_id := l_est_payment_term_id;
592 x_catch_up_term_id := l_catch_up_payment_term_id;
593
594
595 --dbms_output.put_line('updaated est payments' );
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 ROLLBACK TO create_term;
600 pnp_debug_pkg.put_log_msg(substrb('pn_variable_term_pkg.Error in opex_create_payment_term - ' ||
601 to_char(sqlcode)||' : '||sqlerrm || ' - '|| l_context,1,244));
602 --dbms_output.put_line('Exception');
603 x_return_status := 'E';
604 END create_opex_payment_terms;
605
606
607 PROCEDURE create_recon_pay_term(
608 p_recon_id IN NUMBER DEFAULT NULL,
609 p_agreement_id IN NUMBER,
610 p_st_end_date IN DATE,
611 p_amount IN NUMBER,
612 x_payment_term_id OUT NOCOPY NUMBER,
613 x_return_status IN OUT NOCOPY VARCHAR2
614 ) IS
615
616 l_lease_class_code pn_leases.lease_class_code%TYPE;
617 l_distribution_id pn_distributions.distribution_id%TYPE;
618 l_lease_change_id pn_lease_details.lease_change_id%TYPE;
619 l_rowid ROWID;
620 l_distribution_count NUMBER := 0;
621 l_inv_start_date DATE;
622 l_payment_start_date DATE;
623 l_payment_end_date DATE;
624 l_frequency pn_payment_terms_all.frequency_code%type;
625 l_schedule_day pn_payment_terms_all.schedule_day%type;
626 l_set_of_books_id gl_sets_of_books.set_of_books_id%type;
627 l_context varchar2(2000);
628 l_area pn_payment_terms_all.area%TYPE;
629 l_area_type_code pn_payment_terms_all.area_type_code%TYPE;
630 l_org_id NUMBER;
631 l_schedule_day_char VARCHAR2(8);
632 l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%type;
633 i_cnt number;
634 l_payment_term_id pn_payment_terms_all.payment_term_id%TYPE;
635 l_currency_code pn_payment_terms_all.currency_code%TYPE;
636
637
638 CURSOR opex_est_pay_cur(est_pay_trm_id IN NUMBER)
639 IS
640 SELECT *
641 FROM pn_opex_est_payments_all
642 WHERE est_payment_id = est_pay_trm_id;
643
644 CURSOR term_template_cur (term_temp_id IN NUMBER)
645 IS
646 SELECT *
647 FROM pn_term_templates_all
648 WHERE term_template_id = term_temp_id;
649
650 CURSOR agreement_cur(agr_id IN NUMBER)
651 IS
652 SELECT agr.* , loc.location_id
653 FROM pn_opex_agreements_all agr,
654 pn_locations_all loc,
655 pn_tenancies_all ten
656 WHERE agreement_id = agr_id
657 AND agr.tenancy_id = ten.tenancy_id
658 AND ten.location_id = loc.location_id;
659
660 CURSOR distributions_cur (term_temp_id IN NUMBER)
661 IS
662 SELECT *
663 FROM pn_distributions_all
664 WHERE term_template_id = term_temp_id;
665
666
667 -- Using the last estimated payment term to default the values in case present.
668
669 CURSOR prev_pay_term_cur (arg_id IN NUMBER)
670 IS
671 SELECT * FROM pn_payment_terms_all
672 WHERE payment_term_id =
673 (SELECT MAX(payment_term_id) FROM pn_payment_terms_all
674 WHERE opex_agr_id = arg_id
675 AND opex_type = 'ESTPMT');
676
677
678 template_rec pn_term_templates_all%ROWTYPE;
679 agreement_rec agreement_cur%ROWTYPE;
680 distributions_rec distributions_cur%ROWTYPE;
681 pay_term_rec prev_pay_term_cur%ROWTYPE;
682
683
684 BEGIN
685 x_return_status := 'S';
686 --dbms_output.put_line('Testing');
687
688 pnp_debug_pkg.put_log_msg ('opex_create_payment_term');
689
690 l_context := 'Validating input parameters';
691
692 IF (p_recon_id IS NULL OR
693 p_agreement_id is NULL) THEN
694 pnp_debug_pkg.put_log_msg ('Input Prameters missing');
695 END IF;
696
697
698 l_context := 'opening agreement_cur';
699
700 OPEN agreement_cur(p_agreement_id);
701 FETCH agreement_cur INTO agreement_rec;
702 IF agreement_cur%NOTFOUND THEN
703 pnp_debug_pkg.put_log_msg ('No template dat found');
704 RAISE NO_DATA_FOUND ;
705 END IF;
706 CLOSE agreement_cur;
707
708 --dbms_output.put_line('agreement curr');
709
710
711 l_context := 'Getting lease class code and lease change id';
712 BEGIN
713 SELECT pl.lease_class_code,
714 pld.lease_change_id,
715 pl.org_id
716 INTO l_lease_class_code,
717 l_lease_change_id,
718 l_org_id
719 FROM pn_leases_all pl,
720 pn_lease_details_all pld
721 WHERE pl.lease_id = pld.lease_id
722 AND pld.lease_id = agreement_rec.lease_id;
723 EXCEPTION
724 WHEN TOO_MANY_ROWS THEN
725 pnp_debug_pkg.put_log_msg ('Cannot Get Main Lease Details - TOO_MANY_ROWS');
726 WHEN NO_DATA_FOUND THEN
727 pnp_debug_pkg.put_log_msg ('Cannot Get Main Lease Details - NO_DATA_FOUND');
728 WHEN OTHERS THEN
729 pnp_debug_pkg.put_log_msg ('Cannot Get Main Lease Details - Unknown Error:'|| SQLERRM);
730 END;
731
732 l_context := 'Getting set of books id';
733 --dbms_output.put_line('getting set of books');
734
735 l_set_of_books_id := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID'
736 ,l_org_id));
737
738 pnp_debug_pkg.put_log_msg ('create_payment_terms - Set of books id :'||l_set_of_books_id);
739
740 IF agreement_rec.term_template_id IS NOT NULL THEN
741
742 l_context := 'opening cursor csr_template';
743 OPEN term_template_cur(agreement_rec.term_template_id);
744 FETCH term_template_cur INTO template_rec;
745 IF term_template_cur%NOTFOUND THEN
746 pnp_debug_pkg.put_log_msg ('No template dat found');
747 RAISE NO_DATA_FOUND;
748 END IF;
749 CLOSE term_template_cur;
750 END IF;
751
752 --dbms_output.put_line('template cur');
753
754 --N l_context := 'opening est terms table';
755 --N
756 --N OPEN opex_est_pay_cur(p_est_payment_id);
757 --N FETCH opex_est_pay_CUR INTO opex_est_pay_rec;
758 --N IF opex_est_pay_cur%NOTFOUND THEN
759 --N pnp_debug_pkg.put_log_msg ('No template dat found');
760 --N RAISE NO_DATA_FOUND;
761 --N END IF;
762 --N CLOSE opex_est_pay_cur;
763
764 --dbms_output.put_line('est Table cur');
765
766 OPEN prev_pay_term_cur(p_agreement_id);
767 FETCH prev_pay_term_cur INTO pay_term_rec;
768 IF prev_pay_term_cur%NOTFOUND THEN
769 pnp_debug_pkg.put_log_msg ('No template dat found');
770 pay_term_rec := NULL;
771 END IF;
772 CLOSE prev_pay_term_cur;
773
774
775 IF l_lease_class_code = 'DIRECT' THEN
776 /* lease is of class: DIRECT */
777 template_rec.customer_id := NULL;
778 template_rec.customer_site_use_id := NULL;
779 template_rec.cust_ship_site_id := NULL;
780 template_rec.cust_trx_type_id := NULL;
781 template_rec.inv_rule_id := NULL;
782 template_rec.account_rule_id := NULL;
783 template_rec.salesrep_id := NULL;
784 template_rec.cust_po_number := NULL;
785 template_rec.receipt_method_id := NULL;
786 ELSE
787 /* lease is 'sub-lease' or third-party */
788 template_rec.project_id := NULL;
789 template_rec.task_id := NULL;
790 template_rec.organization_id := NULL;
791 template_rec.expenditure_type := NULL;
792 template_rec.expenditure_item_date := NULL;
793 template_rec.vendor_id := NULL;
794 template_rec.vendor_site_id := NULL;
795 template_rec.tax_group_id := NULL;
796 template_rec.distribution_set_id := NULL;
797 template_rec.po_header_id := NULL;
798 END IF;
799
800 IF pn_r12_util_pkg.is_r12 THEN
801 template_rec.tax_group_id := null;
802 template_rec.tax_code_id := null;
803 ELSE
804 template_rec.tax_classification_code := null;
805 END IF;
806
807 --dbms_output.put_line('setting main values');
808
809
810 -- put the start date and the end for the payment term from est_pay term
811
812 l_payment_start_date := p_st_end_date;
813 l_payment_end_date := p_st_end_date;
814
815 l_context := 'Setting frequency and schedule day';
816
817
818 null;
819
820
821 l_frequency := 'OT';
822 l_schedule_day := to_char(l_payment_start_date,'dd');
823 l_currency_code := template_rec.currency_code;
824 --dbms_output.put_line('set freq and sch day');
825
826
827 -- Need to check how to get the location.
828
829 IF agreement_rec.location_id IS NOT NULL AND
830 l_payment_start_date IS NOT NULL THEN
831
832 l_area_type_code := 'LOCTN_RENTABLE';
833 l_area := pnp_util_func.fetch_tenancy_area(
834 p_lease_id => agreement_rec.lease_id,
835 p_location_id => agreement_rec.location_id,
836 p_as_of_date => l_payment_start_date,
837 p_area_type_code => l_area_type_code);
838
839 END IF;
840
841 -- For estimated term
842
843
844 --dbms_output.put_line('tttttt'||agreement_rec.est_pay_freq_code);
845
846 --dbms_output.put_line('inserting row 1');
847 --dbms_output.put_line('x_last_update_login' || NVL(fnd_profile.value('LOGIN_ID'),0));
848 --dbms_output.put_line('x_last_updated_by '|| NVL (fnd_profile.VALUE ('USER_ID'), 0));
849 --dbms_output.put_line('x_payment_purpose_code' || NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code) );
850 --dbms_output.put_line('x_payment_term_type_code' || NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code));
851 --dbms_output.put_line('x_frequency_code' || NVL(agreement_rec.est_pay_freq_code , l_frequency));
852 --dbms_output.put_line('x_lease_id' || agreement_rec.lease_id);
853 --dbms_output.put_line('x_lease_change_id' || l_lease_change_id);
854 --dbms_output.put_line('l_payment_start_date' || l_payment_start_date);
855 --dbms_output.put_line('x_end_date' || l_payment_end_date);
856 --dbms_output.put_line('x_currency_code' || l_currency_code);
857 --dbms_output.put_line('x_set_of_books_id' || NVL(template_rec.set_of_books_id,l_set_of_books_id));
858
859 pnp_debug_pkg.put_log_msg ('opex_create_payment_term');
860 pnp_debug_pkg.put_log_msg('inserting row 1');
861 pnp_debug_pkg.put_log_msg('x_last_update_login' || NVL(fnd_profile.value('LOGIN_ID'),0));
862 pnp_debug_pkg.put_log_msg('x_last_updated_by '|| NVL (fnd_profile.VALUE ('USER_ID'), 0));
863 pnp_debug_pkg.put_log_msg('x_payment_purpose_code' || NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code) );
864 pnp_debug_pkg.put_log_msg('x_payment_term_type_code' || NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code));
865 pnp_debug_pkg.put_log_msg('x_frequency_code' || NVL(agreement_rec.est_pay_freq_code , l_frequency));
866 pnp_debug_pkg.put_log_msg('x_lease_id' || agreement_rec.lease_id);
867 pnp_debug_pkg.put_log_msg('x_lease_change_id' || l_lease_change_id);
868 pnp_debug_pkg.put_log_msg('l_payment_start_date' || l_payment_start_date);
869 pnp_debug_pkg.put_log_msg('x_end_date' || l_payment_end_date);
870 pnp_debug_pkg.put_log_msg('x_currency_code' || l_currency_code);
871 pnp_debug_pkg.put_log_msg('x_set_of_books_id' || NVL(template_rec.set_of_books_id,l_set_of_books_id));
872
873
874
875 pnt_payment_terms_pkg.insert_row (
876 x_rowid => l_rowid
877 ,x_payment_term_id => l_payment_term_id
878 ,x_index_period_id => null
879 ,x_index_term_indicator => null
880 ,x_var_rent_inv_id => null
881 ,x_var_rent_type => null
882 ,x_last_update_date => SYSDATE
883 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
884 ,x_creation_date => SYSDATE
885 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
886 ,x_payment_purpose_code => NVL(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code)
887 ,x_payment_term_type_code => NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code)
888 ,x_frequency_code => l_frequency
889 ,x_lease_id => agreement_rec.lease_id
890 ,x_lease_change_id => l_lease_change_id
891 ,x_start_date => l_payment_start_date
892 ,x_end_date => l_payment_end_date
893 ,x_set_of_books_id => NVL(template_rec.set_of_books_id,l_set_of_books_id)
894 ,x_currency_code => l_currency_code
895 ,x_rate => 1 -- not used in application
896 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
897 ,x_vendor_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_id ELSE pay_term_rec.vendor_id END
898 ,x_vendor_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_site_id ELSE pay_term_rec.vendor_site_id END
899 ,x_target_date => NULL
900 ,x_actual_amount => p_amount
901 ,x_estimated_amount => NULL
902 ,x_attribute_category => template_rec.attribute_category
903 ,x_attribute1 => template_rec.attribute1
904 ,x_attribute2 => template_rec.attribute2
905 ,x_attribute3 => template_rec.attribute3
906 ,x_attribute4 => template_rec.attribute4
907 ,x_attribute5 => template_rec.attribute5
908 ,x_attribute6 => template_rec.attribute6
909 ,x_attribute7 => template_rec.attribute7
910 ,x_attribute8 => template_rec.attribute8
911 ,x_attribute9 => template_rec.attribute9
912 ,x_attribute10 => template_rec.attribute10
913 ,x_attribute11 => template_rec.attribute11
914 ,x_attribute12 => template_rec.attribute12
915 ,x_attribute13 => template_rec.attribute13
916 ,x_attribute14 => template_rec.attribute14
917 ,x_attribute15 => template_rec.attribute15
918 ,x_project_attribute_category => NULL
919 ,x_project_attribute1 => NULL
920 ,x_project_attribute2 => NULL
921 ,x_project_attribute3 => NULL
922 ,x_project_attribute4 => NULL
923 ,x_project_attribute5 => NULL
924 ,x_project_attribute6 => NULL
925 ,x_project_attribute7 => NULL
926 ,x_project_attribute8 => NULL
927 ,x_project_attribute9 => NULL
928 ,x_project_attribute10 => NULL
929 ,x_project_attribute11 => NULL
930 ,x_project_attribute12 => NULL
931 ,x_project_attribute13 => NULL
932 ,x_project_attribute14 => NULL
933 ,x_project_attribute15 => NULL
934 ,x_customer_id => template_rec.customer_id
935 ,x_customer_site_use_id => template_rec.customer_site_use_id
936 ,x_normalize => 'N'
937 ,x_location_id => agreement_rec.location_id
938 ,x_schedule_day => l_schedule_day
939 ,x_cust_ship_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_ship_site_id ELSE pay_term_rec.cust_ship_site_id END
940 ,x_ap_ar_term_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.ap_ar_term_id ELSE pay_term_rec.ap_ar_term_id END
941 ,x_cust_trx_type_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_trx_type_id ELSE pay_term_rec.cust_trx_type_id END
942 ,x_project_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.project_id ELSE pay_term_rec.project_id END
943 ,x_task_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.task_id ELSE pay_term_rec.task_id END
944 ,x_organization_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.organization_id ELSE pay_term_rec.organization_id END
945 ,x_expenditure_type => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_type ELSE pay_term_rec.expenditure_type END
946 ,x_expenditure_item_date => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_item_date ELSE pay_term_rec.expenditure_item_date END
947 ,x_tax_group_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_group_id ELSE pay_term_rec.tax_group_id END
948 ,x_tax_code_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
949 ,x_tax_classification_code => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_classification_code ELSE pay_term_rec.tax_classification_code END
950 ,x_tax_included => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_included ELSE pay_term_rec.tax_included END
951 ,x_distribution_set_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.distribution_set_id ELSE pay_term_rec.distribution_set_id END
952 ,x_inv_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.inv_rule_id ELSE pay_term_rec.inv_rule_id END
953 ,x_account_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.account_rule_id ELSE pay_term_rec.account_rule_id END
954 ,x_salesrep_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.salesrep_id ELSE pay_term_rec.salesrep_id END
955 ,x_approved_by => NULL
956 ,x_status => 'DRAFT'
957 ,x_po_header_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
958 ,x_cust_po_number => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
959 ,x_receipt_method_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
960 --C ,x_calling_form => NULL
961 ,x_org_id => l_org_id
962 ,x_term_template_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.term_template_id ELSE pay_term_rec.term_template_id END
963 ,x_area => l_area
964 ,x_area_type_code => l_area_type_code
965 );
966 -- Updating the opex columns in the pn_payment_terms_all
967
968 UPDATE pn_payment_terms_all
969 SET opex_recon_id = p_recon_id,
970 opex_agr_id = p_agreement_id,
971 opex_type = 'RECON'
972 WHERE payment_term_id = l_payment_term_id;
973
974 --dbms_output.put_line('inserted payment amt row ' || l_payment_term_id );
975
976
977 l_distribution_count := 0;
978 l_context :='opening cursor csr_distributions';
979
980 IF l_payment_term_id IS NOT NULL THEN
981 FOR rec_distributions in distributions_cur(agreement_rec.term_template_id)
982 LOOP
983 pnp_debug_pkg.put_log_msg(' account_id '||rec_distributions.account_id);
984 pnp_debug_pkg.put_log_msg(' account_class '||rec_distributions.account_id);
985 l_context := 'Inserting into pn_distributions';
986 pn_distributions_pkg.insert_row (
987 x_rowid => l_rowid
988 ,x_distribution_id => l_distribution_id
989 ,x_account_id => rec_distributions.account_id
990 ,x_payment_term_id => l_payment_term_id
991 ,x_term_template_id => NULL
992 ,x_account_class => rec_distributions.account_class
993 ,x_percentage => rec_distributions.percentage
994 ,x_line_number => rec_distributions.line_number
995 ,x_last_update_date => SYSDATE
996 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
997 ,x_creation_date => SYSDATE
998 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
999 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
1000 ,x_attribute_category => rec_distributions.attribute_category
1001 ,x_attribute1 => rec_distributions.attribute1
1002 ,x_attribute2 => rec_distributions.attribute2
1003 ,x_attribute3 => rec_distributions.attribute3
1004 ,x_attribute4 => rec_distributions.attribute4
1005 ,x_attribute5 => rec_distributions.attribute5
1006 ,x_attribute6 => rec_distributions.attribute6
1007 ,x_attribute7 => rec_distributions.attribute7
1008 ,x_attribute8 => rec_distributions.attribute8
1009 ,x_attribute9 => rec_distributions.attribute9
1010 ,x_attribute10 => rec_distributions.attribute10
1011 ,x_attribute11 => rec_distributions.attribute11
1012 ,x_attribute12 => rec_distributions.attribute12
1013 ,x_attribute13 => rec_distributions.attribute13
1014 ,x_attribute14 => rec_distributions.attribute14
1015 ,x_attribute15 => rec_distributions.attribute15
1016 ,x_org_id => l_org_id
1017 );
1018 l_rowid := NULL;
1019 l_distribution_id := NULL;
1020 l_distribution_count := l_distribution_count + 1;
1021 END LOOP;
1022 l_context := 'exiting from loop';
1023 END IF;
1024
1025 x_payment_term_id := l_payment_term_id;
1026
1027
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 pnp_debug_pkg.put_log_msg(substrb('pn_variable_term_pkg.Error in opex_create_payment_term - ' ||
1031 to_char(sqlcode)||' : '||sqlerrm || ' - '|| l_context,1,244));
1032 --dbms_output.put_line('Exception');
1033 x_return_status := 'E';
1034 END create_recon_pay_term;
1035
1036
1037 -------------------------------------------------------------------------------
1038 -- PROCEDURE contract_prev_est_term
1039 --
1040 -- History
1041 --
1042 -- 05-JUL-07 Pikhar o IF frequency is MONTHLY, last schedule day is last
1043 -- day of month, else the day one day prior to payment
1044 -- term start.
1045 -------------------------------------------------------------------------------
1046
1047 PROCEDURE contract_prev_est_term(
1048 p_lease_id IN NUMBER,
1049 p_est_payment_id IN NUMBER,
1050 x_return_status IN OUT NOCOPY VARCHAR2)
1051 IS
1052
1053 CURSOR opex_est_pay_cur(est_pay_trm_id IN NUMBER)
1054 IS
1055 SELECT *
1056 FROM pn_opex_est_payments_all
1057 WHERE est_payment_id = est_pay_trm_id;
1058
1059 CURSOR pay_sch_cur(p1_lease_id IN NUMBER , pl_contract_sch_date IN DATE) IS
1060 SELECT payment_schedule_id
1061 FROM pn_payment_schedules_all
1062 WHERE lease_id = p1_lease_id
1063 and payment_status_lookup_code = 'DRAFT'
1064 AND schedule_date >= pl_contract_sch_date;
1065
1066
1067 prev_pay_term_id NUMBER;
1068 opex_est_pay_rec opex_est_pay_cur%ROWTYPE;
1069 last_sch_date DATE;
1070 prev_trm_end_date DATE;
1071 l_sch_id NUMBER;
1072
1073
1074 CURSOR last_sch_day_cur( c_lease_id IN NUMBER , c_pay_term_id IN NUMBER) IS
1075 SELECT
1076 pmt.lease_id,
1077 ADD_MONTHS(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy'),
1078 DECODE(max(pmt.frequency_code),'MON', 0, 'QTR', 2, 'SA', 5 , 'YR', 11)) as last_sch_date
1079 FROM pn_payment_terms_all pmt,
1080 pn_payment_schedules_all sch
1081 WHERE sch.payment_status_lookup_code = 'APPROVED'
1082 AND pmt.payment_term_id = c_pay_term_id
1083 AND sch.lease_id = c_lease_id
1084 AND pmt.lease_id = c_lease_id
1085 GROUP BY pmt.lease_id;
1086
1087
1088 /*CURSOR last_sch_day_cur( c_lease_id IN NUMBER , c_pay_term_id IN NUMBER) IS
1089 SELECT
1090 pmt.lease_id,
1091 (TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy')) as last_sch_date
1092 FROM pn_payment_terms_all pmt,
1093 pn_payment_schedules_all sch
1094 WHERE sch.payment_status_lookup_code = 'APPROVED'
1095 AND pmt.payment_term_id = c_pay_term_id
1096 AND sch.lease_id = c_lease_id
1097 AND pmt.lease_id = c_lease_id
1098 GROUP BY pmt.lease_id;*/
1099
1100
1101 CURSOR payment_cur(pay_term_id IN NUMBER) IS
1102 SELECT * FROM
1103 pn_payment_terms_all
1104 WHERE payment_term_id = pay_term_id;
1105
1106 last_sch_day_rec last_sch_day_cur%ROWTYPE;
1107 payment_rec payment_cur%ROWTYPE;
1108
1109 BEGIN
1110 x_return_status := 'S';
1111 OPEN opex_est_pay_cur(p_est_payment_id);
1112 FETCH opex_est_pay_cur INTO opex_est_pay_rec;
1113 IF opex_est_pay_cur%NOTFOUND THEN
1114 pnp_debug_pkg.put_log_msg('No Est Term Err' );
1115 RAISE NO_DATA_FOUND;
1116 END IF;
1117
1118
1119 -- Getting the payment term that has to be contracted.
1120 BEGIN
1121 SELECT payment_term_id INTO prev_pay_term_id
1122 FROM pn_opex_est_payments_all
1123 WHERE est_payment_id <> p_est_payment_id
1124 AND AGREEMENT_ID = opex_est_pay_rec.agreement_id
1125 AND END_DATE = (
1126 SELECT MAX(END_DATE) FROM pn_opex_est_payments_all
1127 WHERE AGREEMENT_ID = opex_est_pay_rec.agreement_id
1128 AND est_payment_id <> p_est_payment_id)
1129 AND ROWNUM = 1;
1130 EXCEPTION
1131 WHEN NO_DATA_FOUND THEN
1132 pnp_debug_pkg.put_log_msg('No Prev Term to be contracted' );
1133 RETURN; -- No term to be contracted;
1134 END;
1135
1136 pnp_debug_pkg.put_log_msg('Payment term to be contracted ' || prev_pay_term_id);
1137
1138 FOR payment_rec1 IN payment_cur(prev_pay_term_id) LOOP
1139 payment_rec := payment_rec1 ;
1140 END LOOP;
1141
1142 pnp_debug_pkg.put_log_msg('Prev Payment term to be contracted 1' || payment_rec.payment_term_id);
1143 pnp_debug_pkg.put_log_msg('Prev Payment term end date' || payment_rec.end_date);
1144 pnp_debug_pkg.put_log_msg('New payment_term start date ' || opex_est_pay_rec.start_date);
1145
1146 IF payment_rec.end_date >= opex_est_pay_rec.start_date THEN
1147 --do all this only if the parev end date is greater than the start date
1148 pnp_debug_pkg.put_log_msg('Into if for contraction');
1149
1150 -- Getting the last approved schedule date
1151 pnp_debug_pkg.put_log_msg('Getting the last approved schedule date');
1152
1153 /* pn_opex_terms_pkg.last_schedule_day(p_lease_id => p_lease_id,
1154 p_payment_term_id => prev_pay_term_id,
1155 x_end_date => last_sch_date);
1156 pnp_debug_pkg.put_log_msg('last approved schedule date ' || last_sch_date); */
1157
1158 FOR last_sch_day_rec in last_sch_day_cur(p_lease_id ,prev_pay_term_id) LOOP
1159 IF payment_rec.frequency_code = 'MON' THEN
1160 last_sch_date := last_day(last_sch_day_rec.last_sch_date);
1161 ELSE
1162 last_sch_date := last_sch_day_rec.last_sch_date - 1;
1163 END IF;
1164 END LOOP;
1165 pnp_debug_pkg.put_log_msg('last approved schedule date ' || last_sch_date);
1166
1167
1168
1169 -- Contract previous estpmt Id to last approved schedule date.
1170 -- Logic..
1171 -- If last schedule date is not null set prev term end date to last schedule date
1172 -- else
1173 -- set end date to start date - 1
1174 -- else est end date to the start date
1175
1176 IF last_sch_date IS NOT NULL THEN
1177 prev_trm_end_date := last_sch_date;
1178 ELSE
1179 IF opex_est_pay_rec.start_date <= payment_rec.end_date THEN
1180 -- if they prev end date is less than the start date set prev term end date to prev term start date.
1181 IF opex_est_pay_rec.start_date > payment_rec.start_date THEN
1182 prev_trm_end_date := opex_est_pay_rec.start_date - 1;
1183 ELSE
1184 prev_trm_end_date := opex_est_pay_rec.start_date;
1185 END IF;
1186 END IF;
1187 END IF;
1188
1189
1190
1191
1192 -- Delete all schedules from pn_payment_schedules_all and payment_schedule id is not there in payment_items_all and schedule is in draft status
1193
1194 l_sch_id := NULL;
1195 FOR sch_rec in pay_sch_cur(p_lease_id , prev_trm_end_date ) LOOP
1196
1197 l_sch_id := sch_rec.payment_schedule_id;
1198
1199 pnp_debug_pkg.put_log_msg('Deleting Schedule_id ' || l_sch_id);
1200
1201 DELETE FROM pn_payment_items_all
1202 WHERE payment_term_id = prev_pay_term_id
1203 AND payment_schedule_id = l_sch_id
1204 AND payment_item_type_lookup_code = 'CASH';
1205
1206 DELETE FROM pn_payment_schedules_all
1207 WHERE NOT EXISTS (SELECT NULL
1208 FROM pn_payment_items_all
1209 WHERE payment_schedule_id = l_sch_id)
1210 AND payment_schedule_id = l_sch_id;
1211
1212 END LOOP; /*sch cursor */
1213
1214
1215
1216 pnp_debug_pkg.put_log_msg('Updating pn_opex_est_payments_all and pn_payment_terms_all with date ' || prev_trm_end_date );
1217
1218 IF (prev_trm_end_date IS NOT NULL) THEN
1219
1220 UPDATE pn_opex_est_payments_all
1221 SET END_DATE = prev_trm_end_date
1222 Where payment_term_id = prev_pay_term_id;
1223
1224 UPDATE pn_payment_terms_all
1225 SET END_DATE = prev_trm_end_date
1226 Where payment_term_id = prev_pay_term_id;
1227 END IF;
1228
1229 ELSE
1230 pnp_debug_pkg.put_log_msg('Terms are independent and need not be contracted.');
1231 END IF; --payment_rec.end_date >= opex_est_pay_rec.start_date
1232
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 pnp_debug_pkg.put_log_msg('contract_prev_est_term Errored' );
1236 --dbms_output.put_line('Exception');
1237 x_return_status := 'E';
1238
1239 END contract_prev_est_term;
1240
1241
1242
1243 /*===========================================================================+
1244 | PROCEDURE
1245 | LAST_SCHEDULE_DAY
1246 |
1247 | DESCRIPTION
1248 | Find last date till which schedules are approved
1249 |
1250 | SCOPE - PUBLIC
1251 |
1252 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1253 |
1254 | ARGUMENTS : IN:
1255 | p_lease_id
1256 | p_payment_term_id
1257 |
1258 | OUT:
1259 | x_end_date
1260 |
1261 | RETURNS : None
1262 |
1263 | NOTES :
1264 |
1265 | MODIFICATION HISTORY
1266 |
1267 | 02-JAN-2007 pikhar o Created
1268 +===========================================================================*/
1269 PROCEDURE LAST_SCHEDULE_DAY( p_lease_id IN NUMBER,
1270 p_payment_term_id IN NUMBER,
1271 x_end_date OUT NOCOPY VARCHAR2) IS
1272
1273 schedule_date DATE;
1274 end_date DATE;
1275 frequency NUMBER;
1276 l_pay_term_id NUMBER;
1277 last_sch_date DATE;
1278
1279
1280 CURSOR schedule_end_date_cur IS
1281 SELECT (to_date((substr(to_char(max(pmt.start_date),'dd-mm-yyyy'),1,2) || substr(to_char(max(sch.schedule_date),'dd-mm-yyyy'),3)), 'dd-mm-yyyy')) schedule_date
1282 FROM pn_payment_terms_all pmt,
1283 pn_payment_schedules_all sch,
1284 pn_opex_est_payments_all est
1285 WHERE pmt.payment_term_id = est.payment_term_id
1286 AND est.est_payment_id = p_payment_term_id
1287 AND sch.payment_status_lookup_code = 'APPROVED'
1288 AND sch.lease_id = p_lease_id;
1289
1290 CURSOR frequency_cur(p_pay_trm_id IN NUMBER) IS
1291 SELECT DECODE(pmt.FREQUENCY_CODE, 'MON', 1, 'QTR', 3, 'SA', 6 , 'YR', 12) frequency
1292 FROM pn_payment_terms_all pmt
1293 WHERE pmt.payment_term_id = p_pay_trm_id;
1294
1295
1296 CURSOR opex_est_pay_cur(est_pay_trm_id IN NUMBER)
1297 IS
1298 SELECT payment_term_id
1299 FROM pn_opex_est_payments_all
1300 WHERE est_payment_id = est_pay_trm_id;
1301
1302 CURSOR last_sch_day_cur( c_lease_id IN NUMBER , c_pay_term_id IN NUMBER) IS
1303 SELECT
1304 pmt.lease_id,
1305 ADD_MONTHS(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy'),
1306 DECODE(max(pmt.frequency_code),'MON', 0, 'QTR', 2, 'SA', 5 , 'YR', 11)) as last_sch_date
1307 FROM pn_payment_terms_all pmt,
1308 pn_payment_schedules_all sch
1309 WHERE sch.payment_status_lookup_code = 'APPROVED'
1310 AND pmt.payment_term_id = c_pay_term_id
1311 AND sch.lease_id = c_lease_id
1312 AND pmt.lease_id = c_lease_id
1313 GROUP BY pmt.lease_id;
1314
1315
1316 BEGIN
1317
1318 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (+)');
1319
1320
1321 FOR rec IN opex_est_pay_cur(p_payment_term_id) LOOP
1322 l_pay_term_id := rec.payment_term_id;
1323 END LOOP;
1324
1325 IF l_pay_term_id IS NOT NULL THEN
1326 FOR rec IN frequency_cur(l_pay_term_id) LOOP
1327 frequency := rec.frequency;
1328 END LOOP;
1329
1330
1331 FOR last_sch_day_rec in last_sch_day_cur(p_lease_id ,l_pay_term_id) LOOP
1332 IF frequency = 1 THEN
1333 last_sch_date := last_day(last_sch_day_rec.last_sch_date) + 1;
1334 ELSE
1335 last_sch_date := last_sch_day_rec.last_sch_date;
1336 END IF;
1337 END LOOP;
1338
1339 x_end_date := to_char(last_sch_date);
1340
1341 ELSE
1342 x_end_date := to_char(sysdate);
1343 END IF;
1344
1345
1346
1347 /*FOR rec IN schedule_end_date_cur LOOP
1348 schedule_date := to_date(rec.schedule_date , 'DD-MM-YYYY');
1349 END LOOP;
1350
1351
1352 FOR rec IN frequency_cur LOOP
1353 frequency := rec.frequency;
1354 END LOOP;
1355
1356 end_date := add_months(schedule_date, frequency);
1357 end_date := end_date - 1;
1358
1359 --dbms_output.put_line('schedule_date = '||schedule_date);
1360 --dbms_output.put_line('frequency = '||frequency);
1361 --dbms_output.put_line('end date = '||end_date);
1362
1363 x_end_date := to_char(end_date);*/
1364
1365 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (-)');
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 RAISE;
1370
1371 END LAST_SCHEDULE_DAY;
1372
1373
1374
1375
1376 PROCEDURE ALAST_SCHEDULE_DAY( p_lease_id IN NUMBER,
1377 p_payment_term_id IN NUMBER,
1378 x_end_date OUT NOCOPY VARCHAR2) IS
1379
1380 schedule_date DATE;
1381 end_date DATE;
1382 frequency NUMBER;
1383
1384
1385 CURSOR schedule_end_date_cur IS
1386 SELECT (to_date((substr(to_char(max(pmt.start_date),'dd-mm-yyyy'),1,2) || substr(to_char(max(sch.schedule_date),'dd-mm-yyyy'),3)), 'dd-mm-yyyy')) schedule_date
1387 FROM pn_payment_terms_all pmt,
1388 pn_payment_schedules_all sch
1389 WHERE pmt.payment_term_id = p_payment_term_id
1390 AND sch.payment_status_lookup_code = 'APPROVED'
1391 AND sch.lease_id = p_lease_id;
1392
1393 CURSOR frequency_cur IS
1394 SELECT DECODE(pmt.FREQUENCY_CODE, 'MON', 1, 'QTR', 3, 'SA', 6 , 'YR', 12) frequency
1395 FROM pn_payment_terms_all pmt
1396 WHERE pmt.payment_term_id = p_payment_term_id;
1397
1398
1399
1400 BEGIN
1401
1402 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (+)');
1403
1404 FOR rec IN schedule_end_date_cur LOOP
1405 --dbms_output.put_line('schedule_date = L1');
1406 schedule_date := rec.schedule_date ;
1407 END LOOP;
1408
1409
1410 FOR rec IN frequency_cur LOOP
1411 --dbms_output.put_line('freq');
1412 frequency := rec.frequency;
1413 END LOOP;
1414 --dbms_output.put_line('out');
1415 end_date := add_months(schedule_date, frequency);
1416 end_date := end_date - 1;
1417
1418 --dbms_output.put_line('schedule_date = '||schedule_date);
1419 --dbms_output.put_line('frequency = '||frequency);
1420 --dbms_output.put_line('end date = '||end_date);
1421
1422 x_end_date := to_char(end_date);
1423
1424 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (-)');
1425
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 RAISE;
1429
1430 END ALAST_SCHEDULE_DAY;
1431
1432
1433 FUNCTION get_curr_est_pay_term_amt(agr_id IN NUMBER)
1434 RETURN NUMBER
1435 IS
1436 amount NUMBER ;
1437 BEGIN
1438 IF agr_id IS NULL THEN
1439 RETURN NULL;
1440
1441 ELSE
1442 BEGIN
1443 SELECT EST_PMT_AMOUNT INTO amount
1444 FROM pn_opex_est_payments_all
1445 WHERE agreement_id = agr_id
1446 AND END_DATE IN
1447 (SELECT MAX(END_DATE) FROM
1448 pn_opex_est_payments_all
1449 WHERE agreement_id = agr_id)
1450 AND ROWNUM = 1;
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 RETURN NULL;
1454 END;
1455 END IF;
1456 RETURN amount;
1457
1458 END get_curr_est_pay_term_amt;
1459
1460 /*===========================================================================+
1461 | FUNCTION
1462 | GET_CURR_EST_PAY_TERM
1463 |
1464 | DESCRIPTION
1465 | Finds the latest estimated payment term
1466 |
1467 | SCOPE - PUBLIC
1468 |
1469 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1470 |
1471 | ARGUMENTS :agr_id IN NUMBER
1472 |
1473 | RETURNS : NUMBER
1474 |
1475 | NOTES :
1476 |
1477 | MODIFICATION HISTORY
1478 |
1479 | 20-JUN-07 sdmahesh o Bug 6132914 - Modified to get latest estimated
1480 | pay term in case of multiple terms with same
1481 | maximum end date
1482 +===========================================================================*/
1483
1484 FUNCTION get_curr_est_pay_term(agr_id IN NUMBER)
1485 RETURN NUMBER
1486 IS
1487 max_id NUMBER := -1;
1488 CURSOR csr_max_dt_est_term(p_agr_id NUMBER) IS
1489 SELECT est_payment_id
1490 FROM pn_opex_est_payments_all
1491 WHERE agreement_id = p_agr_id
1492 AND END_DATE IN
1493 (SELECT MAX(END_DATE) FROM
1494 pn_opex_est_payments_all
1495 WHERE agreement_id = p_agr_id);
1496
1497
1498 BEGIN
1499 IF agr_id IS NULL THEN
1500 RETURN NULL;
1501 ELSE
1502 FOR rec IN csr_max_dt_est_term(agr_id) LOOP
1503 IF rec.est_payment_id > max_id THEN
1504 max_id := rec.est_payment_id;
1505 END IF;
1506 END LOOP;
1507 END IF;
1508 RETURN max_id;
1509 END get_curr_est_pay_term;
1510
1511
1512 FUNCTION get_latest_recon(agr_id IN NUMBER)
1513 RETURN NUMBER
1514 IS
1515 x_recon_id NUMBER ;
1516 BEGIN
1517 IF agr_id IS NULL THEN
1518 RETURN NULL;
1519 ELSE
1520 BEGIN
1521 SELECT * INTO x_recon_id
1522 FROM (SELECT recon_id FROM pn_opex_recon_all
1523 WHERE agreement_id = agr_id
1524 ORDER BY period_end_dt DESC , revision_number DESC)
1525 WHERE ROWNUM = 1 ;
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND THEN
1528 RETURN NULL;
1529 END;
1530 END IF;
1531 RETURN x_recon_id;
1532 END get_latest_recon;
1533
1534 /*===========================================================================+
1535 | FUNCTION
1536 | GET_PROP_ID
1537 |
1538 | DESCRIPTION
1539 | Finds the property associated with a location
1540 |
1541 | SCOPE - PUBLIC
1542 |
1543 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1544 |
1545 | ARGUMENTS :p_location_id IN NUMBER
1546 |
1547 | RETURNS : NUMBER
1548 |
1549 | NOTES :
1550 |
1551 | MODIFICATION HISTORY
1552 |
1553 | 23-MAY-2007 sdmahesh o Bug 6069029
1554 | Created
1555 +===========================================================================*/
1556
1557 FUNCTION get_prop_id(p_location_id IN NUMBER)
1558 RETURN NUMBER
1559 IS
1560
1561 CURSOR csr_prop_id(loc_id IN NUMBER) IS
1562 SELECT loc.property_id prop_id
1563 FROM pn_locations_all loc
1564 WHERE loc.parent_location_id IS NULL
1565 START WITH loc.location_id = loc_id
1566 CONNECT BY PRIOR loc.parent_location_id=loc.location_id;
1567
1568 rec csr_prop_id%ROWTYPE;
1569
1570 BEGIN
1571 OPEN csr_prop_id(p_location_id);
1572 FETCH csr_prop_id INTO rec;
1573 IF csr_prop_id%NOTFOUND THEN
1574 RAISE NO_DATA_FOUND;
1575 END IF;
1576 CLOSE csr_prop_id;
1577 RETURN rec.prop_id;
1578
1579 EXCEPTION
1580 WHEN NO_DATA_FOUND THEN
1581 RETURN NULL;
1582 WHEN OTHERS THEN
1583 RAISE;
1584 END get_prop_id;
1585
1586
1587
1588 /*===========================================================================+
1589 | FUNCTION
1590 | GET_STMT_DUE_DATE
1591 |
1592 | DESCRIPTION
1593 | Finds the Statement due date for a reconciliation period
1594 |
1595 | SCOPE - PUBLIC
1596 |
1597 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1598 |
1599 | ARGUMENTS : IN:
1600 | agr_id
1601 |
1602 |
1603 |
1604 | RETURNS : DATE
1605 |
1606 | NOTES :
1607 |
1608 | MODIFICATION HISTORY
1609 |
1610 | 02-MAY-2007 sdmahesh o Bug 5940429
1611 | Ensured that all cases are taken care of in
1612 | DECODE for finding WORKING_DATE
1613 +===========================================================================*/
1614
1615 FUNCTION get_stmt_due_date(agr_id IN NUMBER)
1616 RETURN DATE
1617 IS
1618 x_stmt_due_date DATE;
1619 latest_recon_id NUMBER;
1620
1621 working_date DATE;
1622 CURSOR st_due_cur(agr_id IN NUMBER)
1623 IS
1624 SELECT * FROM
1625 pn_opex_critical_dates_all
1626 WHERE agreement_id = agr_id
1627 AND critical_date_type_code = 'RSDFL';
1628
1629
1630 CURSOR recon_cur(p_recon_id IN NUMBER)
1631 IS
1632 SELECT * FROM
1633 pn_opex_recon_all
1634 WHERE recon_id = p_recon_id;
1635
1636 st_due_rec st_due_cur%ROWTYPE;
1637 recon_rec recon_cur%ROWTYPE;
1638
1639
1640 BEGIN
1641
1642 latest_recon_id := get_latest_recon(agr_id);
1643 IF latest_recon_id IS NULL THEN
1644 RETURN NULL;
1645 END IF;
1646
1647 OPEN st_due_cur(agr_id);
1648 FETCH st_due_cur INTO st_due_rec;
1649 IF st_due_cur%NOTFOUND THEN
1650 RAISE NO_DATA_FOUND;
1651 END IF;
1652
1653 CLOSE st_due_cur;
1654
1655 OPEN recon_cur(latest_recon_id);
1656 FETCH recon_cur INTO recon_rec;
1657 IF recon_cur%NOTFOUND THEN
1658 RAISE NO_DATA_FOUND;
1659 END IF;
1660
1661 CLOSE recon_cur;
1662
1663 -- Processing to get the date.
1664
1665 BEGIN
1666 SELECT DECODE (st_due_rec.event_code ,
1667 'RS' , recon_rec.period_start_dt ,
1668 'RE' , recon_rec.period_end_dt ,
1669 'CS' , TO_DATE('01-01-'||TO_CHAR(recon_rec.period_end_dt,'YYYY'),'DD-MM-YYYY'),
1670 'ST' , recon_rec.st_recv_dt ,
1671 null) INTO working_date FROM DUAL;
1672 IF working_date IS NULL THEN
1673 RETURN NULL ;
1674 END IF;
1675
1676 SELECT DECODE (st_due_rec.when_code , 'A',DECODE (st_due_rec.time_unit_code,
1677 'M' , ADD_MONTHS(working_date , NVL(st_due_rec.time_unit,0)),
1678 'D' , working_date + NVL(st_due_rec.time_unit,0),
1679 'Y' , ADD_MONTHS(working_date , NVL(st_due_rec.time_unit,0) * 12),
1680 'W' , working_date + NVL(st_due_rec.time_unit,0)*7 ,
1681 null),--default
1682 'B' ,DECODE (st_due_rec.time_unit_code,
1683 'M' , ADD_MONTHS(working_date , -NVL(st_due_rec.time_unit,0)),
1684 'D' , working_date + -NVL(st_due_rec.time_unit,0),
1685 'Y' , ADD_MONTHS(working_date , -NVL(st_due_rec.time_unit,0) * 12),
1686 'W' , working_date + -NVL(st_due_rec.time_unit,0)*7,
1687 null) -- default
1688 , null) INTO working_date from dual;
1689
1690 EXCEPTION
1691 WHEN NO_DATA_FOUND THEN
1692 RETURN NULL;
1693 END;
1694
1695 IF working_date IS NULL THEN
1696 RETURN NULL;
1697 ELSE
1698 x_stmt_due_date := working_date;
1699 RETURN working_date;
1700 END IF;
1701
1702 EXCEPTION
1703 WHEN NO_DATA_FOUND THEN
1704
1705 IF st_due_cur%ISOPEN THEN
1706 CLOSE st_due_cur;
1707 END IF;
1708
1709 IF recon_cur%ISOPEN THEN
1710 CLOSE recon_cur;
1711 END IF;
1712 RETURN NULL;
1713
1714 WHEN OTHERS THEN
1715 RAISE;
1716 END get_stmt_due_date;
1717
1718
1719 PROCEDURE delete_agreement (p_agreement_id IN NUMBER
1720 ,x_return_status IN OUT NOCOPY VARCHAR2)
1721 IS
1722 l_deletion_allowed VARCHAR2(1) := 'N';
1723 BEGIN
1724 x_return_status := 'S';
1725 BEGIN
1726 SELECT 'N' INTO l_deletion_allowed
1727 FROM DUAL WHERE EXISTS
1728 (SELECT payment_term_id
1729 FROM pn_payment_terms_all
1730 WHERE opex_agr_id = p_agreement_id
1731 AND status = 'APPROVED');
1732
1733 EXCEPTION
1734 WHEN NO_DATA_FOUND THEN
1735 l_deletion_allowed := 'Y';
1736 END;
1737 IF l_deletion_allowed = 'Y' THEN
1738 pnp_debug_pkg.debug ('Deleting.agreement');
1739 -- Deleting agreement tables
1740
1741 DELETE FROM PN_OPEX_NOTES_ALL
1742 WHERE agreement_id = p_agreement_id;
1743
1744 DELETE FROM PN_OPEX_CRITICAL_DATES_ALL
1745 WHERE agreement_id = p_agreement_id;
1746
1747
1748 DELETE FROM PN_OPEX_EXP_GRPS_ALL
1749 WHERE agreement_id = p_agreement_id;
1750
1751 DELETE FROM PN_OPEX_PRORAT_BASIS_DTLS_ALL
1752 WHERE agreement_id = p_agreement_id;
1753
1754 DELETE FROM PN_OPEX_EST_PAYMENTS_ALL
1755 WHERE agreement_id = p_agreement_id;
1756
1757 -- Deleting reconciliation tables
1758
1759 FOR i IN (SELECT recon_id FROM
1760 pn_opex_recon_all WHERE agreement_id = p_agreement_id) LOOP
1761 DELETE FROM PN_OPEX_RECON_CRDT_ALL
1762 WHERE recon_id = i.recon_id;
1763
1764 DELETE FROM PN_OPEX_RECON_PRTBS_ALL
1765 WHERE recon_id = i.recon_id;
1766
1767 DELETE FROM PN_OPEX_RECON_EXP_GRP_ALL
1768 WHERE recon_id = i.recon_id;
1769
1770 DELETE FROM PN_OPEX_RECON_DETAILS_ALL
1771 WHERE recon_id = i.recon_id;
1772
1773 DELETE FROM PN_OPEX_NOTES_ALL
1774 WHERE recon_id = i.recon_id;
1775 END LOOP;
1776
1777 DELETE FROM PN_PAYMENT_TERMS_ALL
1778 WHERE opex_agr_id = p_agreement_id;
1779
1780 DELETE FROM PN_OPEX_AGREEMENTS_ALL
1781 WHERE agreement_id = p_agreement_id;
1782
1783 DELETE FROM PN_OPEX_RECON_ALL
1784 WHERE agreement_id = p_agreement_id;
1785
1786 DELETE FROM PN_OPEX_EST_PAYMENTS_ALL
1787 WHERE agreement_id = p_agreement_id;
1788
1789 ELSE
1790 pnp_debug_pkg.debug ('Agreemnt cannot be deleted');
1791 x_return_status := 'E';
1792 END IF;
1793
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 x_return_status := 'U';
1797 END delete_agreement;
1798
1799
1800 FUNCTION recon_pct_change(p_agr_id IN NUMBER , p_recon_id IN NUMBER , p_period_start_dt DATE , p_ten_tot_charge NUMBER)
1801 RETURN NUMBER
1802 IS
1803
1804 rec_id NUMBER;
1805 ten_tot_crg NUMBER;
1806 pct_change NUMBER;
1807 begin
1808 begin
1809 select recon_id , ten_tot_charge INTO rec_id,ten_tot_crg FROM pn_opex_recon_all WHERE
1810 agreement_id = p_agr_id
1811 AND period_end_dt + 1 = p_period_start_dt
1812 AND current_flag = 'Y';
1813 EXCEPTION
1814 WHEN no_data_found THEN
1815 RETURN NULL;
1816 END;
1817
1818 IF p_ten_tot_charge IS NULL OR ten_tot_crg IS NULL THEN
1819 RETURN NULL;
1820 ELSE
1821 IF ten_tot_crg = 0 THEN
1822 RETURN null;
1823 END if;
1824 pct_change := (p_ten_tot_charge - ten_tot_crg) / ten_tot_crg *100 ;
1825 END IF;
1826 RETURN round(pct_change,2);
1827
1828 EXCEPTION
1829 WHEN others THEN
1830 raise;
1831 END;
1832
1833 ------------------------------------------------------------------------
1834 -- PROCEDURE : put_log
1835 -- DESCRIPTION: This procedure will display the text in the log file
1836 -- of a concurrent program
1837 --
1838 -- 22-Feb-2007 Prabhakar o Created.
1839 ------------------------------------------------------------------------
1840
1841 PROCEDURE put_log ( p_string IN VARCHAR2 ) IS
1842 BEGIN
1843 pnp_debug_pkg.log(p_string);
1844 END put_log;
1845
1846
1847 ------------------------------------------------------------------------
1848 -- PROCEDURE : put_output
1849 -- DESCRIPTION: This procedure will display the text in the log file
1850 -- of a concurrent program
1851 --
1852 -- 22-Feb-2007 Prabhakar o Created.
1853 ------------------------------------------------------------------------
1854
1855 PROCEDURE put_output ( p_string IN VARCHAR2 ) IS
1856 BEGIN
1857 pnp_debug_pkg.put_log_msg(p_string);
1858 END put_output;
1859
1860
1861 ------------------------------------------------------------------------
1862 -- PROCEDURE : display_error_messages
1863 -- DESCRIPTION: This procedure will parse a string of error message codes
1864 -- delimited of with a comma. It will lookup each code using
1865 -- fnd_messages routine.
1866 --
1867 -- 22-Feb-2007 Prabhakar o Created.
1868 ------------------------------------------------------------------------
1869
1870 PROCEDURE display_error_messages (
1871 ip_message_string IN VARCHAR2
1872 ) IS
1873 message_string VARCHAR2 (4000);
1874 msg_len NUMBER;
1875 ind_message VARCHAR2 (40);
1876 comma_loc NUMBER;
1877 BEGIN
1878 message_string := ip_message_string;
1879
1880 IF message_string IS NOT NULL THEN
1881 -- append a comma to the end of the string.
1882 message_string := message_string
1883 || ',';
1884 -- get location of the first comma
1885 comma_loc := INSTR (message_string, ',', 1, 1);
1886 -- get length of message
1887 msg_len := LENGTH (message_string);
1888 ELSE
1889 comma_loc := 0;
1890 END IF;
1891
1892 fnd_message.clear;
1893
1894 --
1895 -- loop will cycle thru each occurrence of delimted text
1896 -- and display message with its code..
1897 --
1898 WHILE comma_loc <> 0
1899 LOOP
1900 --
1901 -- get error message to process
1902 --
1903 ind_message := SUBSTR (message_string, 1, comma_loc
1904 - 1);
1905
1906 --
1907 -- check the length of error message code
1908 --
1909 --
1910 IF LENGTH (ind_message) > 30 THEN
1911 put_log ( '**** MESSAGE CODE '
1912 || ind_message
1913 || ' TOO LONG');
1914 ELSE
1915 --put_log ( 'Message Code='
1916 -- || ind_message);
1917
1918 --
1919 -- Convert error message code to its 'user-friendly' message;
1920 --
1921 fnd_message.set_name ('PN', ind_message);
1922 --
1923 -- Display message to the output log
1924 --
1925 put_output ( '-->'
1926 || fnd_message.get
1927 || ' ('
1928 || ind_message
1929 || ')');
1930 --
1931 -- delete the current message from string of messges
1932 -- e.g.
1933 -- before: message_string = "message1, message2, message3,"
1934 -- after: message_string = "message2, message3,"
1935 --
1936 message_string := SUBSTR (
1937 message_string
1938 , comma_loc
1939 + 1
1940 , LENGTH (message_string)
1941 - comma_loc
1942 );
1943 --
1944 -- locate the first occurrence of a comma
1945 --
1946 comma_loc := INSTR (message_string, ',', 1, 1);
1947 END IF; --LENGTH (ind_message) > 30
1948 END LOOP;
1949 END display_error_messages;
1950
1951
1952
1953
1954 ------------------------------------------------------------------------
1955 -- FUNCTION : format
1956 -- DESCRIPTION: This function is used the print_basis_periods procedure
1957 -- to format any amount to This is only used to display
1958 -- date to the output or log files.
1959 --
1960 -- 22-Feb-2007 Prabhakar o Created.
1961 --
1962 ------------------------------------------------------------------------
1963
1964
1965 FUNCTION format (
1966 p_number IN NUMBER
1967 ,p_precision IN NUMBER DEFAULT NULL
1968 ,p_currency_code IN VARCHAR2 DEFAULT NULL
1969 )
1970 RETURN VARCHAR2 IS
1971 v_currency_code gl_sets_of_books.currency_code%TYPE;
1972 v_formatted_number VARCHAR2 (100);
1973 v_format_mask VARCHAR2 (100);
1974 v_field_length NUMBER := 20;
1975 v_min_acct_unit NUMBER;
1976 BEGIN
1977
1978 /* if p_number is not blank, apply format
1979 if it is blank, just print a blank space */
1980
1981 IF p_number IS NOT NULL THEN
1982
1983 /* deriving a format mask if precision is specified. */
1984
1985 IF p_precision IS NOT NULL THEN
1986 fnd_currency.safe_build_format_mask (
1987 format_mask => v_format_mask
1988 ,field_length => v_field_length
1989 ,precision => p_precision
1990 ,min_acct_unit => v_min_acct_unit
1991 );
1992 ELSE
1993
1994
1995 /* getting format make for currency code defined */
1996
1997 v_format_mask := fnd_currency.get_format_mask (
1998 currency_code => p_currency_code
1999 ,field_length => v_field_length
2000 );
2001 END IF;
2002
2003 v_formatted_number := TO_CHAR (p_number, v_format_mask);
2004 ELSE
2005
2006 /* set formatted number to a space if no number is passed */
2007
2008 v_formatted_number := ' ';
2009 END IF;
2010
2011 RETURN v_formatted_number;
2012
2013 END format;
2014
2015 -------------------------------------------------------------------------------
2016 -- PROCEDURE : approve_opex_pay_term
2017 -- DESCRIPTION: This procedure is called by the mass opex payment
2018 -- batch for single term approval.
2019 --
2020 --
2021 -- 22-Feb-2007 Prabhakar o Created.
2022 -------------------------------------------------------------------------------
2023
2024 PROCEDURE approve_opex_pay_term (ip_lease_id IN NUMBER
2025 ,ip_opex_pay_term_id IN NUMBER
2026 ,op_msg OUT NOCOPY VARCHAR2
2027 ) IS
2028
2029 v_msg VARCHAR2(1000);
2030 err_msg VARCHAR2(2000);
2031 err_code VARCHAR2(2000);
2032 l_include_in_var_rent VARCHAR2(30);
2033
2034 BEGIN
2035 put_log('pn_opex_terms_pkg.approve_index_pay_term (+) : ');
2036
2037 pn_index_lease_common_pkg.chk_for_payment_reqd_fields (
2038 p_payment_term_id => ip_opex_pay_term_id
2039 ,p_msg => v_msg
2040 );
2041
2042 IF v_msg IS NULL THEN
2043 v_msg := 'PN_INDEX_APPROVE_SUCCESS';
2044 --
2045 -- call api to create schedules and items
2046 --
2047
2048 pn_schedules_items.schedules_items (
2049 errbuf => err_msg
2050 ,retcode => err_code
2051 ,p_lease_id => ip_lease_id
2052 ,p_lease_context => 'ADD'
2053 ,p_called_from => 'VAR'
2054 ,p_term_id => ip_opex_pay_term_id
2055 ,p_term_end_dt => NULL
2056 );
2057
2058 --
2059 -- update status of payment term record
2060 --
2061
2062 UPDATE pn_payment_terms_all
2063 SET status = 'APPROVED'
2064 ,last_update_date = SYSDATE
2065 ,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
2066 ,approved_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
2067 WHERE payment_term_id = ip_opex_pay_term_id;
2068
2069 END IF;
2070
2071 op_msg := v_msg;
2072
2073 put_log('pn_opex_terms_pkg.approve_index_pay_term (-) : ');
2074
2075 END approve_opex_pay_term;
2076
2077
2078
2079 -------------------------------------------------------------------------------
2080 -- PROCEDURE : approve_opex_pay_term_batch
2081 -- DESCRIPTION: This procedure is called by the mass opex payment term
2082 -- approval concurrent program.
2083 --
2084 --
2085 -- 22-Feb-2007 Prabhakar o Created.
2086 -- 22-Mar-2007 Prabhakar o Modified the property_id where condition
2087 -- not to cause both side NULL equalization.
2088 -- 15-MAY-2007 sdmahesh o Bug # 6039220
2089 -- Changed the order of concurrent program
2090 -- parameters
2091 -- Modifed CURSOR opex_recs w.r.t. join with
2092 -- PN_PROPERTIES_ALL
2093 -- 23-MAY-07 sdmahesh o Bug 6069029
2094 -- Modifed CURSOR OPEX_RECS w.r.t. join with
2095 -- PN_PROPERTIES_ALL.Used PN_OPEX_TERMS_PKG.GET_PROP_ID
2096 -------------------------------------------------------------------------------
2097
2098 PROCEDURE approve_opex_pay_term_batch (
2099 errbuf OUT NOCOPY VARCHAR2
2100 ,retcode OUT NOCOPY VARCHAR2
2101 ,ip_agreement_number_lower IN VARCHAR2
2102 ,ip_agreement_number_upper IN VARCHAR2
2103 ,ip_main_lease_number_lower IN VARCHAR2
2104 ,ip_main_lease_number_upper IN VARCHAR2
2105 ,ip_location_code_lower IN VARCHAR2
2106 ,ip_location_code_upper IN VARCHAR2
2107 ,ip_user_responsible IN VARCHAR2
2108 ,ip_payment_start_date_lower IN VARCHAR2
2109 ,ip_payment_start_date_upper IN VARCHAR2
2110 ,ip_payment_function IN VARCHAR2
2111 ,ip_property_code_ret_by_id IN VARCHAR2
2112 ,ip_payment_status IN VARCHAR2
2113 ) IS
2114 CURSOR opex_recs (
2115 p_agreement_number_lower IN VARCHAR2
2116 ,p_agreement_number_upper IN VARCHAR2
2117 ,p_main_lease_number_lower IN VARCHAR2
2118 ,p_main_lease_number_upper IN VARCHAR2
2119 ,p_location_code_lower IN VARCHAR2
2120 ,p_location_code_upper IN VARCHAR2
2121 ,p_user_responsible IN VARCHAR2
2122 ,p_payment_start_date_lower IN VARCHAR2
2123 ,p_payment_start_date_upper IN VARCHAR2
2124 ,p_property_code_ret_by_id IN VARCHAR2
2125 ,p_payment_function IN VARCHAR2
2126 ,p_payment_status IN VARCHAR2
2127 ) IS
2128 SELECT popex.lease_id,
2129 popex.agreement_id,
2130 ppt.payment_term_id,
2131 pl.lease_num,
2132 popex.agr_num,
2133 ppt.start_date,
2134 ppt.actual_amount,
2135 ppt.frequency_code,
2136 ppt.end_date,
2137 ppt.status,
2138 ppt.schedule_day,
2139 ppt.currency_code,
2140 DECODE(ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE",
2141 prop.property_id,
2142 loc.location_code,
2143 popex.created_by
2144 FROM pn_leases_all pl,
2145 pn_opex_agreements_all popex,
2146 pn_payment_terms_all ppt,
2147 pn_properties_all prop,
2148 pn_locations_all loc,
2149 pn_tenancies_all ten
2150 WHERE pl.lease_id = popex.lease_id
2151 AND popex.agreement_id = ppt.opex_agr_id
2152 AND popex.tenancy_id = ten.tenancy_id
2153 AND ten.location_id = loc.location_id
2154 AND prop.property_id(+) = pn_opex_terms_pkg.get_prop_id(ppt.location_id)
2155 AND(popex.agr_num BETWEEN nvl(p_agreement_number_lower, popex.agr_num) AND nvl(p_agreement_number_upper, popex.agr_num))
2156 AND(pl.lease_num BETWEEN nvl(p_main_lease_number_lower, pl.lease_num) AND nvl(p_main_lease_number_upper, pl.lease_num))
2157 AND(loc.location_code BETWEEN nvl(p_location_code_lower, loc.location_code)AND nvl(p_location_code_upper, loc.location_code))
2158 AND(ppt.start_date BETWEEN nvl(fnd_date.canonical_to_date(p_payment_start_date_lower), ppt.start_date) AND nvl(fnd_date.canonical_to_date(p_payment_start_date_upper), ppt.start_date))
2159 AND popex.created_by = nvl(p_user_responsible, popex.created_by)
2160 AND ppt.status = p_payment_status
2161 AND(p_payment_function IS NULL OR
2162 (p_payment_function = 'RECON' AND ppt.opex_type = 'RECON' AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NOT NULL) OR
2163 (p_payment_function = 'CATCHUP' AND ppt.opex_type = 'CATCHUP' AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NULL) OR
2164 (p_payment_function = 'ESTPMT' AND ppt.opex_type = 'ESTPMT' AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NULL) OR
2165 (p_payment_function = 'ESTPMT_AND_CATCHUP' AND ppt.opex_type IN('ESTPMT', 'CATCHUP') AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NULL) OR
2166 (p_payment_function = 'ALL' AND ppt.opex_type IN('ESTPMT', 'CATCHUP', 'RECON') AND ppt.opex_agr_id IS NOT NULL))
2167 AND(p_property_code_ret_by_id IS NULL OR p_property_code_ret_by_id = prop.property_id)
2168 AND nvl(pl.status, 'D') = 'F';
2169
2170 v_msg VARCHAR2 (1000);
2171 v_counter NUMBER := 0;
2172 l_errmsg VARCHAR2(2000);
2173 l_errmsg1 VARCHAR2(2000);
2174 l_return_status VARCHAR2 (2) := NULL;
2175 l_nxt_schdate DATE;
2176 l_day pn_payment_terms_all.schedule_day%TYPE;
2177 l_info VARCHAR2(1000);
2178 l_message VARCHAR2(2000) := NULL;
2179 l_appr_count NUMBER := 0;
2180 l_batch_size NUMBER := 1000;
2181
2182 BEGIN
2183 put_log('pn_opex_terms_pkg.approve_index_pay_term_batch (+) : ');
2184
2185 put_log ('ip_agreement_number_lower '|| ip_agreement_number_lower);
2186 put_log ('ip_agreement_number_upper '|| ip_agreement_number_upper);
2187 put_log ('ip_main_lease_number_lower '|| ip_main_lease_number_lower);
2188 put_log ('ip_main_lease_number_upper '|| ip_main_lease_number_upper);
2189 put_log ('ip_location_code_lower '|| ip_location_code_lower);
2190 put_log ('ip_location_code_upper '|| ip_location_code_upper);
2191 put_log ('ip_user_responsible '|| ip_user_responsible);
2192 put_log ('ip_payment_start_date_lower '|| ip_payment_start_date_lower);
2193 put_log ('ip_payment_start_date_upper '|| ip_payment_start_date_upper);
2194 put_log ('ip_property_code_ret_by_id '|| ip_property_code_ret_by_id);
2195 put_log ('ip_payment_function '|| ip_payment_function);
2196 put_log ('ip_payment_status '|| ip_payment_status);
2197 put_log ('Processing the Following Lease Periods:');
2198
2199 /* get all opex payment terms to process */
2200
2201 FOR opex_rec IN opex_recs (
2202 ip_agreement_number_lower
2203 ,ip_agreement_number_upper
2204 ,ip_main_lease_number_lower
2205 ,ip_main_lease_number_upper
2206 ,ip_location_code_lower
2207 ,ip_location_code_upper
2208 ,ip_user_responsible
2209 ,ip_payment_start_date_lower
2210 ,ip_payment_start_date_upper
2211 ,ip_property_code_ret_by_id
2212 ,ip_payment_function
2213 ,ip_payment_status
2214 )
2215 LOOP
2216 v_counter := v_counter + 1;
2217
2218 put_output ('****************************************');
2219 fnd_message.set_name ('PN','PN_RICAL_PROC');
2220 put_output(fnd_message.get||'......');
2221 fnd_message.set_name ('PN','PN_OPEX_CAL_AGR_NO');
2222 fnd_message.set_token ('NUM', opex_rec.agr_num);
2223 put_output(fnd_message.get);
2224 put_output ('****************************************');
2225
2226 l_info := ' approving payment term ID: '||opex_rec.payment_term_id||' ';
2227 approve_opex_pay_term (
2228 ip_lease_id => opex_rec.lease_id
2229 ,ip_opex_pay_term_id => opex_rec.payment_term_id
2230 ,op_msg => v_msg);
2231
2232 l_message := NULL;
2233 fnd_message.set_name ('PN','PN_RICAL_PAYMENT');
2234 l_message := ' '||fnd_message.get;
2235 fnd_message.set_name ('PN','PN_RICAL_START');
2236 l_message := l_message||' '||fnd_message.get;
2237 fnd_message.set_name ('PN','PN_RICAL_END');
2238 l_message := l_message||' '||fnd_message.get;
2239 fnd_message.set_name ('PN','PN_RICAL_PAYMENT');
2240 l_message := l_message||' '||fnd_message.get;
2241 put_output(l_message);
2242
2243 l_message := NULL;
2244
2245 fnd_message.set_name ('PN','PN_RICAL_FREQ');
2246 l_message := ' '||fnd_message.get;
2247 fnd_message.set_name ('PN','PN_RICAL_DATE');
2248 l_message := l_message||' '||fnd_message.get;
2249 fnd_message.set_name ('PN','PN_RICAL_DATE');
2250 l_message := l_message||' '||fnd_message.get;
2251 fnd_message.set_name ('PN','PN_RICAL_AMT');
2252 l_message := l_message||' '||fnd_message.get;
2253 fnd_message.set_name ('PN','PN_RICAL_STATUS');
2254 l_message := l_message||' '||fnd_message.get;
2255 fnd_message.set_name ('PN','PN_RICAL_PAYMENT_TYPE');
2256 l_message := l_message||' '||fnd_message.get;
2257 fnd_message.set_name ('PN','PN_RICAL_NORZ');
2258 l_message := l_message||' '||fnd_message.get;
2259 put_output(l_message);
2260
2261 put_output (
2262 ' --------- ----------- ----------- ---------- ----------- ------------------ ----------'
2263 );
2264
2265 put_output ('. ');
2266 put_output (
2267 LPAD (opex_rec.frequency_code, 18, ' ')
2268 || LPAD (opex_rec.start_date, 13, ' ')
2269 || LPAD (opex_rec.end_date, 13, ' ')
2270 || LPAD (format (opex_rec.actual_amount, 2, opex_rec.currency_code), 12, ' ')
2271 || LPAD (opex_rec.status, 13, ' ')
2272 || LPAD (opex_rec.NORMALIZE, 11, ' ')
2273 );
2274 put_output ('. ');
2275 display_error_messages (ip_message_string => v_msg);
2276
2277 END LOOP;
2278
2279 IF v_counter = 0 THEN
2280 fnd_message.set_name ('PN','PN_RICAL_MSG');
2281 put_output (fnd_message.get||' :');
2282 display_error_messages (ip_message_string => 'PN_INDEX_NO_PAYT_TO_APPROVE');
2283 END IF;
2284
2285 put_log('pn_opex_terms_pkg.approve_index_pay_term_batch (-) : ');
2286
2287 END approve_opex_pay_term_batch;
2288
2289
2290 /*===========================================================================+
2291 | FUNCTION
2292 | GET_UNPAID_AMT
2293 |
2294 | DESCRIPTION
2295 | Finds the unpaid amount for a reconciliation period
2296 |
2297 | SCOPE - PUBLIC
2298 |
2299 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2300 |
2301 | ARGUMENTS : IN: p_recon_id
2302 |
2303 | RETURNS : NUMBER
2304 |
2305 | NOTES :
2306 |
2307 | MODIFICATION HISTORY
2308 |
2309 | 26-JUN-2007 sdmahesh o Bug 6146157
2310 | Modified the unpaid amount calculation logic.
2311 | Added CSR_RECON_EXP_GRP
2312 +===========================================================================*/
2313 FUNCTION get_unpaid_amt(p_recon_id IN NUMBER) RETURN NUMBER
2314 IS
2315
2316 CURSOR amt_cur(c_recon_id IN NUMBER) IS
2317
2318 SELECT SUM(item.actual_amount) act_amt
2319 FROM pn_payment_items_all item,
2320 pn_payment_terms_all term,
2321 pn_opex_recon_all recon
2322 WHERE item.payment_item_type_lookup_code = 'CASH'
2323 AND item.payment_term_id = term.payment_term_id
2324 AND term.opex_recon_id = recon.recon_id
2325 AND recon.recon_id = c_recon_id ;
2326
2327 CURSOR recon_cur(c_recon_id IN NUMBER) IS
2328 SELECT * FROM
2329 pn_opex_recon_all WHERE recon_id = c_recon_id;
2330
2331 CURSOR recon_det_cur(c_recon_id IN NUMBER) IS
2332 SELECT NVL(expected_ovr , expected) AS amt FROM
2333 pn_opex_recon_details_all
2334 WHERE recon_id = c_recon_id
2335 AND TYPE = '1PRP';
2336
2337 CURSOR csr_recon_exp_grp(c_recon_id IN NUMBER) IS
2338 SELECT amount_st,
2339 recoverable_st
2340 FROM pn_opex_recon_exp_grp_all
2341 WHERE recon_id = c_recon_id;
2342
2343 amt_rec amt_cur%ROWTYPE;
2344 recon_rec recon_cur%ROWTYPE;
2345 recon_det_rec recon_det_cur%ROWTYPE;
2346
2347 unpaid_amount NUMBER;
2348 exp_grp_exst BOOLEAN := FALSE;
2349
2350
2351 BEGIN
2352 IF p_recon_id IS NULL THEN
2353 RETURN NULL;
2354 END IF;
2355 OPEN recon_cur(p_recon_id);
2356 FETCH recon_cur INTO recon_rec;
2357
2358 OPEN amt_cur(p_recon_id);
2359 FETCH amt_cur INTO amt_rec;
2360
2361 OPEN recon_det_cur(p_recon_id);
2362 FETCH recon_det_cur INTO recon_det_rec;
2363
2364 FOR rec IN csr_recon_exp_grp(p_recon_id) LOOP
2365 IF rec.amount_st IS NOT NULL OR
2366 rec.recoverable_st IS NOT NULL THEN
2367 exp_grp_exst := TRUE;
2368 EXIT;
2369 END IF;
2370 END LOOP;
2371
2372 IF recon_cur%ISOPEN THEN
2373 CLOSE recon_cur;
2374 END IF;
2375 IF amt_cur%ISOPEN THEN
2376 CLOSE amt_cur;
2377 END IF;
2378 IF recon_det_cur%ISOPEN THEN
2379 CLOSE recon_det_cur;
2380 END IF;
2381 IF csr_recon_exp_grp%ISOPEN THEN
2382 CLOSE csr_recon_exp_grp;
2383 END IF;
2384
2385 IF recon_rec.amt_due_st IS NOT NULL AND exp_grp_exst THEN
2386 unpaid_amount := recon_rec.amt_due_st - NVL(amt_rec.act_amt,0);
2387 RETURN unpaid_amount;
2388 ELSIF recon_rec.st_amt_due IS NOT NULL THEN
2389 unpaid_amount := recon_rec.st_amt_due - NVL(amt_rec.act_amt,0) - NVL(recon_det_rec.amt,0);
2390 RETURN unpaid_amount;
2391 ELSE
2392 RETURN NULL;
2393 END IF;
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 RETURN NULL;
2398
2399 END get_unpaid_amt;
2400 END PN_OPEX_TERMS_PKG;