[Home] [Help]
PACKAGE BODY: APPS.PN_OPEX_TERMS_PKG
Source
1 PACKAGE BODY PN_OPEX_TERMS_PKG AS
2 -- $Header: PNOTERMB.pls 120.7 2011/11/03 19:34:06 asahoo ship $
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 := nvl(template_rec.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 := nvl(template_rec.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 -- 08-FEB-11 acprakas o Bug#11718340. Modified to set end date of previous
1046 -- estimated payment term to 1 day prior to start date of new estimated
1047 -- payment term.
1048 -- 16-FEB-11 acprakas o Bug#11778290. Modified to delete end dated term.
1049 -------------------------------------------------------------------------------
1050
1051 PROCEDURE contract_prev_est_term(
1052 p_lease_id IN NUMBER,
1053 p_est_payment_id IN NUMBER,
1054 x_return_status IN OUT NOCOPY VARCHAR2)
1055 IS
1056
1057 CURSOR opex_est_pay_cur(est_pay_trm_id IN NUMBER)
1058 IS
1059 SELECT *
1060 FROM pn_opex_est_payments_all
1061 WHERE est_payment_id = est_pay_trm_id;
1062
1063 CURSOR pay_sch_cur(p1_lease_id IN NUMBER , pl_contract_sch_date IN DATE) IS
1064 SELECT payment_schedule_id
1065 FROM pn_payment_schedules_all
1066 WHERE lease_id = p1_lease_id
1067 and payment_status_lookup_code = 'DRAFT'
1068 AND schedule_date >= pl_contract_sch_date;
1069
1070
1071 prev_pay_term_id NUMBER;
1072 opex_est_pay_rec opex_est_pay_cur%ROWTYPE;
1073 last_sch_date DATE;
1074 prev_trm_end_date DATE;
1075 l_sch_id NUMBER;
1076 l_approved_item_count NUMBER := -1;
1077
1078
1079 CURSOR last_sch_day_cur( c_lease_id IN NUMBER , c_pay_term_id IN NUMBER) IS
1080 SELECT
1081 pmt.lease_id,
1082 ADD_MONTHS(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy'),
1083 DECODE(max(pmt.frequency_code),'MON', 0, 'QTR', 2, 'SA', 5 , 'YR', 11)) as last_sch_date
1084 FROM pn_payment_terms_all pmt,
1085 pn_payment_schedules_all sch
1086 WHERE sch.payment_status_lookup_code = 'APPROVED'
1087 AND pmt.payment_term_id = c_pay_term_id
1088 AND sch.lease_id = c_lease_id
1089 AND pmt.lease_id = c_lease_id
1090 GROUP BY pmt.lease_id;
1091
1092
1093 /*CURSOR last_sch_day_cur( c_lease_id IN NUMBER , c_pay_term_id IN NUMBER) IS
1094 SELECT
1095 pmt.lease_id,
1096 (TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy')) as last_sch_date
1097 FROM pn_payment_terms_all pmt,
1098 pn_payment_schedules_all sch
1099 WHERE sch.payment_status_lookup_code = 'APPROVED'
1100 AND pmt.payment_term_id = c_pay_term_id
1101 AND sch.lease_id = c_lease_id
1102 AND pmt.lease_id = c_lease_id
1103 GROUP BY pmt.lease_id;*/
1104
1105
1106 CURSOR payment_cur(pay_term_id IN NUMBER) IS
1107 SELECT * FROM
1108 pn_payment_terms_all
1109 WHERE payment_term_id = pay_term_id;
1110
1111 last_sch_day_rec last_sch_day_cur%ROWTYPE;
1112 payment_rec payment_cur%ROWTYPE;
1113
1114 BEGIN
1115 x_return_status := 'S';
1116 OPEN opex_est_pay_cur(p_est_payment_id);
1117 FETCH opex_est_pay_cur INTO opex_est_pay_rec;
1118 IF opex_est_pay_cur%NOTFOUND THEN
1119 pnp_debug_pkg.put_log_msg('No Est Term Err' );
1120 RAISE NO_DATA_FOUND;
1121 END IF;
1122
1123
1124 -- Getting the payment term that has to be contracted.
1125 BEGIN
1126 SELECT payment_term_id INTO prev_pay_term_id
1127 FROM pn_opex_est_payments_all
1128 WHERE est_payment_id <> p_est_payment_id
1129 AND AGREEMENT_ID = opex_est_pay_rec.agreement_id
1130 AND END_DATE = (
1131 SELECT MAX(END_DATE) FROM pn_opex_est_payments_all
1132 WHERE AGREEMENT_ID = opex_est_pay_rec.agreement_id
1133 AND est_payment_id <> p_est_payment_id)
1134 AND ROWNUM = 1;
1135 EXCEPTION
1136 WHEN NO_DATA_FOUND THEN
1137 pnp_debug_pkg.put_log_msg('No Prev Term to be contracted' );
1138 RETURN; -- No term to be contracted;
1139 END;
1140
1141 pnp_debug_pkg.put_log_msg('Payment term to be contracted ' || prev_pay_term_id);
1142
1143 FOR payment_rec1 IN payment_cur(prev_pay_term_id) LOOP
1144 payment_rec := payment_rec1 ;
1145 END LOOP;
1146
1147 pnp_debug_pkg.put_log_msg('Prev Payment term to be contracted 1' || payment_rec.payment_term_id);
1148 pnp_debug_pkg.put_log_msg('Prev Payment term end date' || payment_rec.end_date);
1149 pnp_debug_pkg.put_log_msg('New payment_term start date ' || opex_est_pay_rec.start_date);
1150
1151 IF payment_rec.end_date >= opex_est_pay_rec.start_date THEN
1152 --do all this only if the parev end date is greater than the start date
1153 pnp_debug_pkg.put_log_msg('Into if for contraction');
1154
1155 -- Getting the last approved schedule date
1156 pnp_debug_pkg.put_log_msg('Getting the last approved schedule date');
1157
1158 /* pn_opex_terms_pkg.last_schedule_day(p_lease_id => p_lease_id,
1159 p_payment_term_id => prev_pay_term_id,
1160 x_end_date => last_sch_date);
1161 pnp_debug_pkg.put_log_msg('last approved schedule date ' || last_sch_date); */
1162
1163 /*
1164 FOR last_sch_day_rec in last_sch_day_cur(p_lease_id ,prev_pay_term_id) LOOP
1165 IF payment_rec.frequency_code = 'MON' THEN
1166 last_sch_date := last_day(last_sch_day_rec.last_sch_date);
1167 ELSE
1168 last_sch_date := last_sch_day_rec.last_sch_date - 1;
1169 END IF;
1170 END LOOP;
1171 */
1172 pnp_debug_pkg.put_log_msg('last approved schedule date ' || last_sch_date);
1173
1174
1175
1176 -- Contract previous estpmt Id to last approved schedule date.
1177 -- Logic..
1178 -- If last schedule date is not null set prev term end date to last schedule date
1179 -- else
1180 -- set end date to start date - 1
1181 -- else est end date to the start date
1182
1183 IF opex_est_pay_rec.start_date <= payment_rec.end_date THEN
1184 -- if they prev end date is less than the start date set prev term end date to prev term start date.
1185 IF opex_est_pay_rec.start_date > payment_rec.start_date THEN
1186 prev_trm_end_date := opex_est_pay_rec.start_date - 1;
1187 ELSE
1188 prev_trm_end_date := opex_est_pay_rec.start_date;
1189
1190 select count(1)
1191 into l_approved_item_count
1192 from pn_payment_items_all
1193 where payment_term_id = prev_pay_term_id
1194 and nvl(export_to_ap_flag,'N') = 'Y'
1195 and payment_item_type_lookup_code = 'CASH';
1196
1197 END IF;
1198 END IF;
1199
1200
1201
1202
1203
1204 -- 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
1205
1206 l_sch_id := NULL;
1207 FOR sch_rec in pay_sch_cur(p_lease_id , prev_trm_end_date ) LOOP
1208
1209 l_sch_id := sch_rec.payment_schedule_id;
1210
1211 pnp_debug_pkg.put_log_msg('Deleting Schedule_id ' || l_sch_id);
1212
1213 DELETE FROM pn_payment_items_all
1214 WHERE payment_term_id = prev_pay_term_id
1215 AND payment_schedule_id = l_sch_id
1216 AND payment_item_type_lookup_code = 'CASH';
1217
1218 DELETE FROM pn_payment_schedules_all
1219 WHERE NOT EXISTS (SELECT NULL
1220 FROM pn_payment_items_all
1221 WHERE payment_schedule_id = l_sch_id)
1222 AND payment_schedule_id = l_sch_id;
1223
1224 END LOOP; /*sch cursor */
1225
1226
1227
1228 pnp_debug_pkg.put_log_msg('Updating pn_opex_est_payments_all and pn_payment_terms_all with date ' || prev_trm_end_date );
1229
1230 IF (prev_trm_end_date IS NOT NULL) THEN
1231
1232 IF l_approved_item_count = 0
1233 THEN
1234
1235 delete from pn_opex_est_payments_all
1236 where payment_term_id = prev_pay_term_id;
1237
1238 delete from pn_distributions_all
1239 where payment_term_id = prev_pay_term_id;
1240
1241 delete from pn_payment_terms_all
1242 where payment_term_id = prev_pay_term_id;
1243
1244 ELSE
1245 UPDATE pn_opex_est_payments_all
1246 SET END_DATE = prev_trm_end_date
1247 Where payment_term_id = prev_pay_term_id;
1248
1249 UPDATE pn_payment_terms_all
1250 SET END_DATE = prev_trm_end_date
1251 Where payment_term_id = prev_pay_term_id;
1252 END IF;
1253 END IF;
1254
1255
1256 ELSE
1257 pnp_debug_pkg.put_log_msg('Terms are independent and need not be contracted.');
1258 END IF; --payment_rec.end_date >= opex_est_pay_rec.start_date
1259
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 pnp_debug_pkg.put_log_msg('contract_prev_est_term Errored' );
1263 --dbms_output.put_line('Exception');
1264 x_return_status := 'E';
1265
1266 END contract_prev_est_term;
1267
1268
1269
1270 /*===========================================================================+
1271 | PROCEDURE
1272 | LAST_SCHEDULE_DAY
1273 |
1274 | DESCRIPTION
1275 | Find last date till which schedules are approved
1276 |
1277 | SCOPE - PUBLIC
1278 |
1279 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1280 |
1281 | ARGUMENTS : IN:
1282 | p_lease_id
1283 | p_payment_term_id
1284 |
1285 | OUT:
1286 | x_end_date
1287 |
1288 | RETURNS : None
1289 |
1290 | NOTES :
1291 |
1292 | MODIFICATION HISTORY
1293 |
1294 | 02-JAN-2007 pikhar o Created
1295 1-OCT-2011 asahoo Fix for Bug:13114165 for R12, in case there is no approved schedule, last approved scheduled date can not be sysdate
1296 | Commented out that line.
1297 +===========================================================================*/
1298 PROCEDURE LAST_SCHEDULE_DAY( p_lease_id IN NUMBER,
1299 p_payment_term_id IN NUMBER,
1300 x_end_date OUT NOCOPY VARCHAR2) IS
1301
1302 schedule_date DATE;
1303 end_date DATE;
1304 frequency NUMBER;
1305 l_pay_term_id NUMBER;
1306 last_sch_date DATE;
1307
1308
1309 CURSOR schedule_end_date_cur IS
1310 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
1311 FROM pn_payment_terms_all pmt,
1312 pn_payment_schedules_all sch,
1313 pn_opex_est_payments_all est
1314 WHERE pmt.payment_term_id = est.payment_term_id
1315 AND est.est_payment_id = p_payment_term_id
1316 AND sch.payment_status_lookup_code = 'APPROVED'
1317 AND sch.lease_id = p_lease_id;
1318
1319 CURSOR frequency_cur(p_pay_trm_id IN NUMBER) IS
1320 SELECT DECODE(pmt.FREQUENCY_CODE, 'MON', 1, 'QTR', 3, 'SA', 6 , 'YR', 12) frequency
1321 FROM pn_payment_terms_all pmt
1322 WHERE pmt.payment_term_id = p_pay_trm_id;
1323
1324
1325 CURSOR opex_est_pay_cur(est_pay_trm_id IN NUMBER)
1326 IS
1327 SELECT payment_term_id
1328 FROM pn_opex_est_payments_all
1329 WHERE est_payment_id = est_pay_trm_id;
1330
1331 CURSOR last_sch_day_cur( c_lease_id IN NUMBER , c_pay_term_id IN NUMBER) IS
1332 SELECT
1333 pmt.lease_id,
1334 ADD_MONTHS(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy'),
1335 DECODE(max(pmt.frequency_code),'MON', 0, 'QTR', 2, 'SA', 5 , 'YR', 11)) as last_sch_date
1336 FROM pn_payment_terms_all pmt,
1337 pn_payment_schedules_all sch
1338 WHERE sch.payment_status_lookup_code = 'APPROVED'
1339 AND pmt.payment_term_id = c_pay_term_id
1340 AND sch.lease_id = c_lease_id
1341 AND pmt.lease_id = c_lease_id
1342 GROUP BY pmt.lease_id;
1343
1344
1345 BEGIN
1346
1347 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (+)');
1348
1349
1350 FOR rec IN opex_est_pay_cur(p_payment_term_id) LOOP
1351 l_pay_term_id := rec.payment_term_id;
1352 END LOOP;
1353
1354 IF l_pay_term_id IS NOT NULL THEN
1355 FOR rec IN frequency_cur(l_pay_term_id) LOOP
1356 frequency := rec.frequency;
1357 END LOOP;
1358
1359
1360 FOR last_sch_day_rec in last_sch_day_cur(p_lease_id ,l_pay_term_id) LOOP
1361 IF frequency = 1 THEN
1362 last_sch_date := last_day(last_sch_day_rec.last_sch_date) + 1;
1363 ELSE
1364 last_sch_date := last_sch_day_rec.last_sch_date;
1365 END IF;
1366 END LOOP;
1367
1368 x_end_date := to_char(last_sch_date);
1369
1370 --ELSE
1371 --x_end_date := to_char(sysdate);
1372 END IF;
1373
1374
1375
1376 /*FOR rec IN schedule_end_date_cur LOOP
1377 schedule_date := to_date(rec.schedule_date , 'DD-MM-YYYY');
1378 END LOOP;
1379
1380
1381 FOR rec IN frequency_cur LOOP
1382 frequency := rec.frequency;
1383 END LOOP;
1384
1385 end_date := add_months(schedule_date, frequency);
1386 end_date := end_date - 1;
1387
1388 --dbms_output.put_line('schedule_date = '||schedule_date);
1389 --dbms_output.put_line('frequency = '||frequency);
1390 --dbms_output.put_line('end date = '||end_date);
1391
1392 x_end_date := to_char(end_date);*/
1393
1394 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (-)');
1395
1396 EXCEPTION
1397 WHEN OTHERS THEN
1398 RAISE;
1399
1400 END LAST_SCHEDULE_DAY;
1401
1402
1403
1404
1405 PROCEDURE ALAST_SCHEDULE_DAY( p_lease_id IN NUMBER,
1406 p_payment_term_id IN NUMBER,
1407 x_end_date OUT NOCOPY VARCHAR2) IS
1408
1409 schedule_date DATE;
1410 end_date DATE;
1411 frequency NUMBER;
1412
1413
1414 CURSOR schedule_end_date_cur IS
1415 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
1416 FROM pn_payment_terms_all pmt,
1417 pn_payment_schedules_all sch
1418 WHERE pmt.payment_term_id = p_payment_term_id
1419 AND sch.payment_status_lookup_code = 'APPROVED'
1420 AND sch.lease_id = p_lease_id;
1421
1422 CURSOR frequency_cur IS
1423 SELECT DECODE(pmt.FREQUENCY_CODE, 'MON', 1, 'QTR', 3, 'SA', 6 , 'YR', 12) frequency
1424 FROM pn_payment_terms_all pmt
1425 WHERE pmt.payment_term_id = p_payment_term_id;
1426
1427
1428
1429 BEGIN
1430
1431 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (+)');
1432
1433 FOR rec IN schedule_end_date_cur LOOP
1434 --dbms_output.put_line('schedule_date = L1');
1435 schedule_date := rec.schedule_date ;
1436 END LOOP;
1437
1438
1439 FOR rec IN frequency_cur LOOP
1440 --dbms_output.put_line('freq');
1441 frequency := rec.frequency;
1442 END LOOP;
1443 --dbms_output.put_line('out');
1444 end_date := add_months(schedule_date, frequency);
1445 end_date := end_date - 1;
1446
1447 --dbms_output.put_line('schedule_date = '||schedule_date);
1448 --dbms_output.put_line('frequency = '||frequency);
1449 --dbms_output.put_line('end date = '||end_date);
1450
1451 x_end_date := to_char(end_date);
1452
1453 pnp_debug_pkg.debug ('PN_OPEX_TERMS_PKG.LAST_SCHEDULE_DAY (-)');
1454
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 RAISE;
1458
1459 END ALAST_SCHEDULE_DAY;
1460
1461
1462 FUNCTION get_curr_est_pay_term_amt(agr_id IN NUMBER)
1463 RETURN NUMBER
1464 IS
1465 amount NUMBER ;
1466 BEGIN
1467 IF agr_id IS NULL THEN
1468 RETURN NULL;
1469
1470 ELSE
1471 BEGIN
1472 SELECT EST_PMT_AMOUNT INTO amount
1473 FROM pn_opex_est_payments_all
1474 WHERE agreement_id = agr_id
1475 AND END_DATE IN
1476 (SELECT MAX(END_DATE) FROM
1477 pn_opex_est_payments_all
1478 WHERE agreement_id = agr_id)
1479 AND ROWNUM = 1;
1480 EXCEPTION
1481 WHEN NO_DATA_FOUND THEN
1482 RETURN NULL;
1483 END;
1484 END IF;
1485 RETURN amount;
1486
1487 END get_curr_est_pay_term_amt;
1488
1489 /*===========================================================================+
1490 | FUNCTION
1491 | GET_CURR_EST_PAY_TERM
1492 |
1493 | DESCRIPTION
1494 | Finds the latest estimated payment term
1495 |
1496 | SCOPE - PUBLIC
1497 |
1498 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1499 |
1500 | ARGUMENTS :agr_id IN NUMBER
1501 |
1502 | RETURNS : NUMBER
1503 |
1504 | NOTES :
1505 |
1506 | MODIFICATION HISTORY
1507 |
1508 | 20-JUN-07 sdmahesh o Bug 6132914 - Modified to get latest estimated
1509 | pay term in case of multiple terms with same
1510 | maximum end date
1511 +===========================================================================*/
1512
1513 FUNCTION get_curr_est_pay_term(agr_id IN NUMBER)
1514 RETURN NUMBER
1515 IS
1516 max_id NUMBER := -1;
1517 CURSOR csr_max_dt_est_term(p_agr_id NUMBER) IS
1518 SELECT est_payment_id
1519 FROM pn_opex_est_payments_all
1520 WHERE agreement_id = p_agr_id
1521 AND END_DATE IN
1522 (SELECT MAX(END_DATE) FROM
1523 pn_opex_est_payments_all
1524 WHERE agreement_id = p_agr_id);
1525
1526
1527 BEGIN
1528 IF agr_id IS NULL THEN
1529 RETURN NULL;
1530 ELSE
1531 FOR rec IN csr_max_dt_est_term(agr_id) LOOP
1532 IF rec.est_payment_id > max_id THEN
1533 max_id := rec.est_payment_id;
1534 END IF;
1535 END LOOP;
1536 END IF;
1537 RETURN max_id;
1538 END get_curr_est_pay_term;
1539
1540
1541 FUNCTION get_latest_recon(agr_id IN NUMBER)
1542 RETURN NUMBER
1543 IS
1544 x_recon_id NUMBER ;
1545 BEGIN
1546 IF agr_id IS NULL THEN
1547 RETURN NULL;
1548 ELSE
1549 BEGIN
1550 SELECT * INTO x_recon_id
1551 FROM (SELECT recon_id FROM pn_opex_recon_all
1552 WHERE agreement_id = agr_id
1553 ORDER BY period_end_dt DESC , revision_number DESC)
1554 WHERE ROWNUM = 1 ;
1555 EXCEPTION
1556 WHEN NO_DATA_FOUND THEN
1557 RETURN NULL;
1558 END;
1559 END IF;
1560 RETURN x_recon_id;
1561 END get_latest_recon;
1562
1563 /*===========================================================================+
1564 | FUNCTION
1565 | GET_PROP_ID
1566 |
1567 | DESCRIPTION
1568 | Finds the property associated with a location
1569 |
1570 | SCOPE - PUBLIC
1571 |
1572 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1573 |
1574 | ARGUMENTS :p_location_id IN NUMBER
1575 |
1576 | RETURNS : NUMBER
1577 |
1578 | NOTES :
1579 |
1580 | MODIFICATION HISTORY
1581 |
1582 | 23-MAY-2007 sdmahesh o Bug 6069029
1583 | Created
1584 +===========================================================================*/
1585
1586 FUNCTION get_prop_id(p_location_id IN NUMBER)
1587 RETURN NUMBER
1588 IS
1589
1590 CURSOR csr_prop_id(loc_id IN NUMBER) IS
1591 SELECT loc.property_id prop_id
1592 FROM pn_locations_all loc
1593 WHERE loc.parent_location_id IS NULL
1594 START WITH loc.location_id = loc_id
1595 CONNECT BY PRIOR loc.parent_location_id=loc.location_id;
1596
1597 rec csr_prop_id%ROWTYPE;
1598
1599 BEGIN
1600 OPEN csr_prop_id(p_location_id);
1601 FETCH csr_prop_id INTO rec;
1602 IF csr_prop_id%NOTFOUND THEN
1603 RAISE NO_DATA_FOUND;
1604 END IF;
1605 CLOSE csr_prop_id;
1606 RETURN rec.prop_id;
1607
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610 RETURN NULL;
1611 WHEN OTHERS THEN
1612 RAISE;
1613 END get_prop_id;
1614
1615
1616
1617 /*===========================================================================+
1618 | FUNCTION
1619 | GET_STMT_DUE_DATE
1620 |
1621 | DESCRIPTION
1622 | Finds the Statement due date for a reconciliation period
1623 |
1624 | SCOPE - PUBLIC
1625 |
1626 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1627 |
1628 | ARGUMENTS : IN:
1629 | agr_id
1630 |
1631 |
1632 |
1633 | RETURNS : DATE
1634 |
1635 | NOTES :
1636 |
1637 | MODIFICATION HISTORY
1638 |
1639 | 02-MAY-2007 sdmahesh o Bug 5940429
1640 | Ensured that all cases are taken care of in
1641 | DECODE for finding WORKING_DATE
1642 +===========================================================================*/
1643
1644 FUNCTION get_stmt_due_date(agr_id IN NUMBER)
1645 RETURN DATE
1646 IS
1647 x_stmt_due_date DATE;
1648 latest_recon_id NUMBER;
1649
1650 working_date DATE;
1651 CURSOR st_due_cur(agr_id IN NUMBER)
1652 IS
1653 SELECT * FROM
1654 pn_opex_critical_dates_all
1655 WHERE agreement_id = agr_id
1656 AND critical_date_type_code = 'RSDFL';
1657
1658
1659 CURSOR recon_cur(p_recon_id IN NUMBER)
1660 IS
1661 SELECT * FROM
1662 pn_opex_recon_all
1663 WHERE recon_id = p_recon_id;
1664
1665 st_due_rec st_due_cur%ROWTYPE;
1666 recon_rec recon_cur%ROWTYPE;
1667
1668
1669 BEGIN
1670
1671 latest_recon_id := get_latest_recon(agr_id);
1672 IF latest_recon_id IS NULL THEN
1673 RETURN NULL;
1674 END IF;
1675
1676 OPEN st_due_cur(agr_id);
1677 FETCH st_due_cur INTO st_due_rec;
1678 IF st_due_cur%NOTFOUND THEN
1679 RAISE NO_DATA_FOUND;
1680 END IF;
1681
1682 CLOSE st_due_cur;
1683
1684 OPEN recon_cur(latest_recon_id);
1685 FETCH recon_cur INTO recon_rec;
1686 IF recon_cur%NOTFOUND THEN
1687 RAISE NO_DATA_FOUND;
1688 END IF;
1689
1690 CLOSE recon_cur;
1691
1692 -- Processing to get the date.
1693
1694 BEGIN
1695 SELECT DECODE (st_due_rec.event_code ,
1696 'RS' , recon_rec.period_start_dt ,
1697 'RE' , recon_rec.period_end_dt ,
1698 'CS' , TO_DATE('01-01-'||TO_CHAR(recon_rec.period_end_dt,'YYYY'),'DD-MM-YYYY'),
1699 'ST' , recon_rec.st_recv_dt ,
1700 null) INTO working_date FROM DUAL;
1701 IF working_date IS NULL THEN
1702 RETURN NULL ;
1703 END IF;
1704
1705 SELECT DECODE (st_due_rec.when_code , 'A',DECODE (st_due_rec.time_unit_code,
1706 'M' , ADD_MONTHS(working_date , NVL(st_due_rec.time_unit,0)),
1707 'D' , working_date + NVL(st_due_rec.time_unit,0),
1708 'Y' , ADD_MONTHS(working_date , NVL(st_due_rec.time_unit,0) * 12),
1709 'W' , working_date + NVL(st_due_rec.time_unit,0)*7 ,
1710 null),--default
1711 'B' ,DECODE (st_due_rec.time_unit_code,
1712 'M' , ADD_MONTHS(working_date , -NVL(st_due_rec.time_unit,0)),
1713 'D' , working_date + -NVL(st_due_rec.time_unit,0),
1714 'Y' , ADD_MONTHS(working_date , -NVL(st_due_rec.time_unit,0) * 12),
1715 'W' , working_date + -NVL(st_due_rec.time_unit,0)*7,
1716 null) -- default
1717 , null) INTO working_date from dual;
1718
1719 EXCEPTION
1720 WHEN NO_DATA_FOUND THEN
1721 RETURN NULL;
1722 END;
1723
1724 IF working_date IS NULL THEN
1725 RETURN NULL;
1726 ELSE
1727 x_stmt_due_date := working_date;
1728 RETURN working_date;
1729 END IF;
1730
1731 EXCEPTION
1732 WHEN NO_DATA_FOUND THEN
1733
1734 IF st_due_cur%ISOPEN THEN
1735 CLOSE st_due_cur;
1736 END IF;
1737
1738 IF recon_cur%ISOPEN THEN
1739 CLOSE recon_cur;
1740 END IF;
1741 RETURN NULL;
1742
1743 WHEN OTHERS THEN
1744 RAISE;
1745 END get_stmt_due_date;
1746
1747
1748 PROCEDURE delete_agreement (p_agreement_id IN NUMBER
1749 ,x_return_status IN OUT NOCOPY VARCHAR2)
1750 IS
1751 l_deletion_allowed VARCHAR2(1) := 'N';
1752 BEGIN
1753 x_return_status := 'S';
1754 BEGIN
1755 SELECT 'N' INTO l_deletion_allowed
1756 FROM DUAL WHERE EXISTS
1757 (SELECT payment_term_id
1758 FROM pn_payment_terms_all
1759 WHERE opex_agr_id = p_agreement_id
1760 AND status = 'APPROVED');
1761
1762 EXCEPTION
1763 WHEN NO_DATA_FOUND THEN
1764 l_deletion_allowed := 'Y';
1765 END;
1766 IF l_deletion_allowed = 'Y' THEN
1767 pnp_debug_pkg.debug ('Deleting.agreement');
1768 -- Deleting agreement tables
1769
1770 DELETE FROM PN_OPEX_NOTES_ALL
1771 WHERE agreement_id = p_agreement_id;
1772
1773 DELETE FROM PN_OPEX_CRITICAL_DATES_ALL
1774 WHERE agreement_id = p_agreement_id;
1775
1776
1777 DELETE FROM PN_OPEX_EXP_GRPS_ALL
1778 WHERE agreement_id = p_agreement_id;
1779
1780 DELETE FROM PN_OPEX_PRORAT_BASIS_DTLS_ALL
1781 WHERE agreement_id = p_agreement_id;
1782
1783 DELETE FROM PN_OPEX_EST_PAYMENTS_ALL
1784 WHERE agreement_id = p_agreement_id;
1785
1786 -- Deleting reconciliation tables
1787
1788 FOR i IN (SELECT recon_id FROM
1789 pn_opex_recon_all WHERE agreement_id = p_agreement_id) LOOP
1790 DELETE FROM PN_OPEX_RECON_CRDT_ALL
1791 WHERE recon_id = i.recon_id;
1792
1793 DELETE FROM PN_OPEX_RECON_PRTBS_ALL
1794 WHERE recon_id = i.recon_id;
1795
1796 DELETE FROM PN_OPEX_RECON_EXP_GRP_ALL
1797 WHERE recon_id = i.recon_id;
1798
1799 DELETE FROM PN_OPEX_RECON_DETAILS_ALL
1800 WHERE recon_id = i.recon_id;
1801
1802 DELETE FROM PN_OPEX_NOTES_ALL
1803 WHERE recon_id = i.recon_id;
1804 END LOOP;
1805
1806 DELETE FROM PN_PAYMENT_TERMS_ALL
1807 WHERE opex_agr_id = p_agreement_id;
1808
1809 DELETE FROM PN_OPEX_AGREEMENTS_ALL
1810 WHERE agreement_id = p_agreement_id;
1811
1812 DELETE FROM PN_OPEX_RECON_ALL
1813 WHERE agreement_id = p_agreement_id;
1814
1815 DELETE FROM PN_OPEX_EST_PAYMENTS_ALL
1816 WHERE agreement_id = p_agreement_id;
1817
1818 ELSE
1819 pnp_debug_pkg.debug ('Agreemnt cannot be deleted');
1820 x_return_status := 'E';
1821 END IF;
1822
1823 EXCEPTION
1824 WHEN OTHERS THEN
1825 x_return_status := 'U';
1826 END delete_agreement;
1827
1828
1829 FUNCTION recon_pct_change(p_agr_id IN NUMBER , p_recon_id IN NUMBER , p_period_start_dt DATE , p_ten_tot_charge NUMBER)
1830 RETURN NUMBER
1831 IS
1832
1833 rec_id NUMBER;
1834 ten_tot_crg NUMBER;
1835 pct_change NUMBER;
1836 begin
1837 begin
1838 --Fix for bug#13335165 in R12, added recon_id to the Where condition
1839 select recon_id , ten_tot_charge INTO rec_id,ten_tot_crg FROM pn_opex_recon_all WHERE
1840 agreement_id = p_agr_id
1841 AND recon_id = p_recon_id
1842 AND period_end_dt + 1 = p_period_start_dt
1843 AND current_flag = 'Y';
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 RETURN NULL;
1847 END;
1848
1849 IF p_ten_tot_charge IS NULL OR ten_tot_crg IS NULL THEN
1850 RETURN NULL;
1851 ELSE
1852 IF ten_tot_crg = 0 THEN
1853 RETURN null;
1854 END if;
1855 pct_change := (p_ten_tot_charge - ten_tot_crg) / ten_tot_crg *100 ;
1856 END IF;
1857 RETURN round(pct_change,2);
1858
1859 EXCEPTION
1860 WHEN others THEN
1861 raise;
1862 END;
1863
1864 ------------------------------------------------------------------------
1865 -- PROCEDURE : put_log
1866 -- DESCRIPTION: This procedure will display the text in the log file
1867 -- of a concurrent program
1868 --
1869 -- 22-Feb-2007 Prabhakar o Created.
1870 ------------------------------------------------------------------------
1871
1872 PROCEDURE put_log ( p_string IN VARCHAR2 ) IS
1873 BEGIN
1874 pnp_debug_pkg.log(p_string);
1875 END put_log;
1876
1877
1878 ------------------------------------------------------------------------
1879 -- PROCEDURE : put_output
1880 -- DESCRIPTION: This procedure will display the text in the log file
1881 -- of a concurrent program
1882 --
1883 -- 22-Feb-2007 Prabhakar o Created.
1884 ------------------------------------------------------------------------
1885
1886 PROCEDURE put_output ( p_string IN VARCHAR2 ) IS
1887 BEGIN
1888 pnp_debug_pkg.put_log_msg(p_string);
1889 END put_output;
1890
1891
1892 ------------------------------------------------------------------------
1893 -- PROCEDURE : display_error_messages
1894 -- DESCRIPTION: This procedure will parse a string of error message codes
1895 -- delimited of with a comma. It will lookup each code using
1896 -- fnd_messages routine.
1897 --
1898 -- 22-Feb-2007 Prabhakar o Created.
1899 ------------------------------------------------------------------------
1900
1901 PROCEDURE display_error_messages (
1902 ip_message_string IN VARCHAR2
1903 ) IS
1904 message_string VARCHAR2 (4000);
1905 msg_len NUMBER;
1906 ind_message VARCHAR2 (40);
1907 comma_loc NUMBER;
1908 BEGIN
1909 message_string := ip_message_string;
1910
1911 IF message_string IS NOT NULL THEN
1912 -- append a comma to the end of the string.
1913 message_string := message_string
1914 || ',';
1915 -- get location of the first comma
1916 comma_loc := INSTR (message_string, ',', 1, 1);
1917 -- get length of message
1918 msg_len := LENGTH (message_string);
1919 ELSE
1920 comma_loc := 0;
1921 END IF;
1922
1923 fnd_message.clear;
1924
1925 --
1926 -- loop will cycle thru each occurrence of delimted text
1927 -- and display message with its code..
1928 --
1929 WHILE comma_loc <> 0
1930 LOOP
1931 --
1932 -- get error message to process
1933 --
1934 ind_message := SUBSTR (message_string, 1, comma_loc
1935 - 1);
1936
1937 --
1938 -- check the length of error message code
1939 --
1940 --
1941 IF LENGTH (ind_message) > 30 THEN
1942 put_log ( '**** MESSAGE CODE '
1943 || ind_message
1944 || ' TOO LONG');
1945 ELSE
1946 --put_log ( 'Message Code='
1947 -- || ind_message);
1948
1949 --
1950 -- Convert error message code to its 'user-friendly' message;
1951 --
1952 fnd_message.set_name ('PN', ind_message);
1953 --
1954 -- Display message to the output log
1955 --
1956 put_output ( '-->'
1957 || fnd_message.get
1958 || ' ('
1959 || ind_message
1960 || ')');
1961 --
1962 -- delete the current message from string of messges
1963 -- e.g.
1964 -- before: message_string = "message1, message2, message3,"
1965 -- after: message_string = "message2, message3,"
1966 --
1967 message_string := SUBSTR (
1968 message_string
1969 , comma_loc
1970 + 1
1971 , LENGTH (message_string)
1972 - comma_loc
1973 );
1974 --
1975 -- locate the first occurrence of a comma
1976 --
1977 comma_loc := INSTR (message_string, ',', 1, 1);
1978 END IF; --LENGTH (ind_message) > 30
1979 END LOOP;
1980 END display_error_messages;
1981
1982
1983
1984
1985 ------------------------------------------------------------------------
1986 -- FUNCTION : format
1987 -- DESCRIPTION: This function is used the print_basis_periods procedure
1988 -- to format any amount to This is only used to display
1989 -- date to the output or log files.
1990 --
1991 -- 22-Feb-2007 Prabhakar o Created.
1992 --
1993 ------------------------------------------------------------------------
1994
1995
1996 FUNCTION format (
1997 p_number IN NUMBER
1998 ,p_precision IN NUMBER DEFAULT NULL
1999 ,p_currency_code IN VARCHAR2 DEFAULT NULL
2000 )
2001 RETURN VARCHAR2 IS
2002 v_currency_code gl_sets_of_books.currency_code%TYPE;
2003 v_formatted_number VARCHAR2 (100);
2004 v_format_mask VARCHAR2 (100);
2005 v_field_length NUMBER := 20;
2006 v_min_acct_unit NUMBER;
2007 BEGIN
2008
2009 /* if p_number is not blank, apply format
2010 if it is blank, just print a blank space */
2011
2012 IF p_number IS NOT NULL THEN
2013
2014 /* deriving a format mask if precision is specified. */
2015
2016 IF p_precision IS NOT NULL THEN
2017 fnd_currency.safe_build_format_mask (
2018 format_mask => v_format_mask
2019 ,field_length => v_field_length
2020 ,precision => p_precision
2021 ,min_acct_unit => v_min_acct_unit
2022 );
2023 ELSE
2024
2025
2026 /* getting format make for currency code defined */
2027
2028 v_format_mask := fnd_currency.get_format_mask (
2029 currency_code => p_currency_code
2030 ,field_length => v_field_length
2031 );
2032 END IF;
2033
2034 v_formatted_number := TO_CHAR (p_number, v_format_mask);
2035 ELSE
2036
2037 /* set formatted number to a space if no number is passed */
2038
2039 v_formatted_number := ' ';
2040 END IF;
2041
2042 RETURN v_formatted_number;
2043
2044 END format;
2045
2046 -------------------------------------------------------------------------------
2047 -- PROCEDURE : approve_opex_pay_term
2048 -- DESCRIPTION: This procedure is called by the mass opex payment
2049 -- batch for single term approval.
2050 --
2051 --
2052 -- 22-Feb-2007 Prabhakar o Created.
2053 -------------------------------------------------------------------------------
2054
2055 PROCEDURE approve_opex_pay_term (ip_lease_id IN NUMBER
2056 ,ip_opex_pay_term_id IN NUMBER
2057 ,op_msg OUT NOCOPY VARCHAR2
2058 ) IS
2059
2060 v_msg VARCHAR2(1000);
2061 err_msg VARCHAR2(2000);
2062 err_code VARCHAR2(2000);
2063 l_include_in_var_rent VARCHAR2(30);
2064
2065 BEGIN
2066 put_log('pn_opex_terms_pkg.approve_index_pay_term (+) : ');
2067
2068 pn_index_lease_common_pkg.chk_for_payment_reqd_fields (
2069 p_payment_term_id => ip_opex_pay_term_id
2070 ,p_msg => v_msg
2071 );
2072
2073 IF v_msg IS NULL THEN
2074 v_msg := 'PN_INDEX_APPROVE_SUCCESS';
2075 --
2076 -- call api to create schedules and items
2077 --
2078
2079 pn_schedules_items.schedules_items (
2080 errbuf => err_msg
2081 ,retcode => err_code
2082 ,p_lease_id => ip_lease_id
2083 ,p_lease_context => 'ADD'
2084 ,p_called_from => 'VAR'
2085 ,p_term_id => ip_opex_pay_term_id
2086 ,p_term_end_dt => NULL
2087 );
2088
2089 --
2090 -- update status of payment term record
2091 --
2092
2093 UPDATE pn_payment_terms_all
2094 SET status = 'APPROVED'
2095 ,last_update_date = SYSDATE
2096 ,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
2097 ,approved_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
2098 WHERE payment_term_id = ip_opex_pay_term_id;
2099
2100 END IF;
2101
2102 op_msg := v_msg;
2103
2104 put_log('pn_opex_terms_pkg.approve_index_pay_term (-) : ');
2105
2106 END approve_opex_pay_term;
2107
2108
2109
2110 -------------------------------------------------------------------------------
2111 -- PROCEDURE : approve_opex_pay_term_batch
2112 -- DESCRIPTION: This procedure is called by the mass opex payment term
2113 -- approval concurrent program.
2114 --
2115 --
2116 -- 22-Feb-2007 Prabhakar o Created.
2117 -- 22-Mar-2007 Prabhakar o Modified the property_id where condition
2118 -- not to cause both side NULL equalization.
2119 -- 15-MAY-2007 sdmahesh o Bug # 6039220
2120 -- Changed the order of concurrent program
2121 -- parameters
2122 -- Modifed CURSOR opex_recs w.r.t. join with
2123 -- PN_PROPERTIES_ALL
2124 -- 23-MAY-07 sdmahesh o Bug 6069029
2125 -- Modifed CURSOR OPEX_RECS w.r.t. join with
2126 -- PN_PROPERTIES_ALL.Used PN_OPEX_TERMS_PKG.GET_PROP_ID
2127 -------------------------------------------------------------------------------
2128
2129 PROCEDURE approve_opex_pay_term_batch (
2130 errbuf OUT NOCOPY VARCHAR2
2131 ,retcode OUT NOCOPY VARCHAR2
2132 ,ip_agreement_number_lower IN VARCHAR2
2133 ,ip_agreement_number_upper IN VARCHAR2
2134 ,ip_main_lease_number_lower IN VARCHAR2
2135 ,ip_main_lease_number_upper IN VARCHAR2
2136 ,ip_location_code_lower IN VARCHAR2
2137 ,ip_location_code_upper IN VARCHAR2
2138 ,ip_user_responsible IN VARCHAR2
2139 ,ip_payment_start_date_lower IN VARCHAR2
2140 ,ip_payment_start_date_upper IN VARCHAR2
2141 ,ip_payment_function IN VARCHAR2
2142 ,ip_property_code_ret_by_id IN VARCHAR2
2143 ,ip_payment_status IN VARCHAR2
2144 ) IS
2145 CURSOR opex_recs (
2146 p_agreement_number_lower IN VARCHAR2
2147 ,p_agreement_number_upper IN VARCHAR2
2148 ,p_main_lease_number_lower IN VARCHAR2
2149 ,p_main_lease_number_upper IN VARCHAR2
2150 ,p_location_code_lower IN VARCHAR2
2151 ,p_location_code_upper IN VARCHAR2
2152 ,p_user_responsible IN VARCHAR2
2153 ,p_payment_start_date_lower IN VARCHAR2
2154 ,p_payment_start_date_upper IN VARCHAR2
2155 ,p_property_code_ret_by_id IN VARCHAR2
2156 ,p_payment_function IN VARCHAR2
2157 ,p_payment_status IN VARCHAR2
2158 ) IS
2159 SELECT popex.lease_id,
2160 popex.agreement_id,
2161 ppt.payment_term_id,
2162 pl.lease_num,
2163 popex.agr_num,
2164 ppt.start_date,
2165 ppt.actual_amount,
2166 ppt.frequency_code,
2167 ppt.end_date,
2168 ppt.status,
2169 ppt.schedule_day,
2170 ppt.currency_code,
2171 DECODE(ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE",
2172 prop.property_id,
2173 loc.location_code,
2174 popex.created_by
2175 FROM pn_leases_all pl,
2176 pn_opex_agreements_all popex,
2177 pn_payment_terms_all ppt,
2178 pn_properties_all prop,
2179 pn_locations_all loc,
2180 pn_tenancies_all ten
2181 WHERE pl.lease_id = popex.lease_id
2182 AND popex.agreement_id = ppt.opex_agr_id
2183 AND popex.tenancy_id = ten.tenancy_id
2184 AND ten.location_id = loc.location_id
2185 AND prop.property_id(+) = pn_opex_terms_pkg.get_prop_id(ppt.location_id)
2186 AND(popex.agr_num BETWEEN nvl(p_agreement_number_lower, popex.agr_num) AND nvl(p_agreement_number_upper, popex.agr_num))
2187 AND(pl.lease_num BETWEEN nvl(p_main_lease_number_lower, pl.lease_num) AND nvl(p_main_lease_number_upper, pl.lease_num))
2188 AND(loc.location_code BETWEEN nvl(p_location_code_lower, loc.location_code)AND nvl(p_location_code_upper, loc.location_code))
2189 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))
2190 AND popex.created_by = nvl(p_user_responsible, popex.created_by)
2191 AND ppt.status = p_payment_status
2192 AND(p_payment_function IS NULL OR
2193 (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
2194 (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
2195 (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
2196 (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
2197 (p_payment_function = 'ALL' AND ppt.opex_type IN('ESTPMT', 'CATCHUP', 'RECON') AND ppt.opex_agr_id IS NOT NULL))
2198 AND(p_property_code_ret_by_id IS NULL OR p_property_code_ret_by_id = prop.property_id)
2199 AND nvl(pl.status, 'D') = 'F';
2200
2201 v_msg VARCHAR2 (1000);
2202 v_counter NUMBER := 0;
2203 l_errmsg VARCHAR2(2000);
2204 l_errmsg1 VARCHAR2(2000);
2205 l_return_status VARCHAR2 (2) := NULL;
2206 l_nxt_schdate DATE;
2207 l_day pn_payment_terms_all.schedule_day%TYPE;
2208 l_info VARCHAR2(1000);
2209 l_message VARCHAR2(2000) := NULL;
2210 l_appr_count NUMBER := 0;
2211 l_batch_size NUMBER := 1000;
2212
2213 BEGIN
2214 put_log('pn_opex_terms_pkg.approve_index_pay_term_batch (+) : ');
2215
2216 put_log ('ip_agreement_number_lower '|| ip_agreement_number_lower);
2217 put_log ('ip_agreement_number_upper '|| ip_agreement_number_upper);
2218 put_log ('ip_main_lease_number_lower '|| ip_main_lease_number_lower);
2219 put_log ('ip_main_lease_number_upper '|| ip_main_lease_number_upper);
2220 put_log ('ip_location_code_lower '|| ip_location_code_lower);
2221 put_log ('ip_location_code_upper '|| ip_location_code_upper);
2222 put_log ('ip_user_responsible '|| ip_user_responsible);
2223 put_log ('ip_payment_start_date_lower '|| ip_payment_start_date_lower);
2224 put_log ('ip_payment_start_date_upper '|| ip_payment_start_date_upper);
2225 put_log ('ip_property_code_ret_by_id '|| ip_property_code_ret_by_id);
2226 put_log ('ip_payment_function '|| ip_payment_function);
2227 put_log ('ip_payment_status '|| ip_payment_status);
2228 put_log ('Processing the Following Lease Periods:');
2229
2230 /* get all opex payment terms to process */
2231
2232 FOR opex_rec IN opex_recs (
2233 ip_agreement_number_lower
2234 ,ip_agreement_number_upper
2235 ,ip_main_lease_number_lower
2236 ,ip_main_lease_number_upper
2237 ,ip_location_code_lower
2238 ,ip_location_code_upper
2239 ,ip_user_responsible
2240 ,ip_payment_start_date_lower
2241 ,ip_payment_start_date_upper
2242 ,ip_property_code_ret_by_id
2243 ,ip_payment_function
2244 ,ip_payment_status
2245 )
2246 LOOP
2247 v_counter := v_counter + 1;
2248
2249 put_output ('****************************************');
2250 fnd_message.set_name ('PN','PN_RICAL_PROC');
2251 put_output(fnd_message.get||'......');
2252 fnd_message.set_name ('PN','PN_OPEX_CAL_AGR_NO');
2253 fnd_message.set_token ('NUM', opex_rec.agr_num);
2254 put_output(fnd_message.get);
2255 put_output ('****************************************');
2256
2257 l_info := ' approving payment term ID: '||opex_rec.payment_term_id||' ';
2258 approve_opex_pay_term (
2259 ip_lease_id => opex_rec.lease_id
2260 ,ip_opex_pay_term_id => opex_rec.payment_term_id
2261 ,op_msg => v_msg);
2262
2263 l_message := NULL;
2264 fnd_message.set_name ('PN','PN_RICAL_PAYMENT');
2265 l_message := ' '||fnd_message.get;
2266 fnd_message.set_name ('PN','PN_RICAL_START');
2267 l_message := l_message||' '||fnd_message.get;
2268 fnd_message.set_name ('PN','PN_RICAL_END');
2269 l_message := l_message||' '||fnd_message.get;
2270 fnd_message.set_name ('PN','PN_RICAL_PAYMENT');
2271 l_message := l_message||' '||fnd_message.get;
2272 put_output(l_message);
2273
2274 l_message := NULL;
2275
2276 fnd_message.set_name ('PN','PN_RICAL_FREQ');
2277 l_message := ' '||fnd_message.get;
2278 fnd_message.set_name ('PN','PN_RICAL_DATE');
2279 l_message := l_message||' '||fnd_message.get;
2280 fnd_message.set_name ('PN','PN_RICAL_DATE');
2281 l_message := l_message||' '||fnd_message.get;
2282 fnd_message.set_name ('PN','PN_RICAL_AMT');
2283 l_message := l_message||' '||fnd_message.get;
2284 fnd_message.set_name ('PN','PN_RICAL_STATUS');
2285 l_message := l_message||' '||fnd_message.get;
2286 fnd_message.set_name ('PN','PN_RICAL_PAYMENT_TYPE');
2287 l_message := l_message||' '||fnd_message.get;
2288 fnd_message.set_name ('PN','PN_RICAL_NORZ');
2289 l_message := l_message||' '||fnd_message.get;
2290 put_output(l_message);
2291
2292 put_output (
2293 ' --------- ----------- ----------- ---------- ----------- ------------------ ----------'
2294 );
2295
2296 put_output ('. ');
2297 put_output (
2298 LPAD (opex_rec.frequency_code, 18, ' ')
2299 || LPAD (opex_rec.start_date, 13, ' ')
2300 || LPAD (opex_rec.end_date, 13, ' ')
2301 || LPAD (format (opex_rec.actual_amount, 2, opex_rec.currency_code), 12, ' ')
2302 || LPAD (opex_rec.status, 13, ' ')
2303 || LPAD (opex_rec.NORMALIZE, 11, ' ')
2304 );
2305 put_output ('. ');
2306 display_error_messages (ip_message_string => v_msg);
2307
2308 END LOOP;
2309
2310 IF v_counter = 0 THEN
2311 fnd_message.set_name ('PN','PN_RICAL_MSG');
2312 put_output (fnd_message.get||' :');
2313 display_error_messages (ip_message_string => 'PN_INDEX_NO_PAYT_TO_APPROVE');
2314 END IF;
2315
2316 put_log('pn_opex_terms_pkg.approve_index_pay_term_batch (-) : ');
2317
2318 END approve_opex_pay_term_batch;
2319
2320
2321 /*===========================================================================+
2322 | FUNCTION
2323 | GET_UNPAID_AMT
2324 |
2325 | DESCRIPTION
2326 | Finds the unpaid amount for a reconciliation period
2327 |
2328 | SCOPE - PUBLIC
2329 |
2330 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2331 |
2332 | ARGUMENTS : IN: p_recon_id
2333 |
2334 | RETURNS : NUMBER
2335 |
2336 | NOTES :
2337 |
2338 | MODIFICATION HISTORY
2339 |
2340 | 26-JUN-2007 sdmahesh o Bug 6146157
2341 | Modified the unpaid amount calculation logic.
2342 | Added CSR_RECON_EXP_GRP
2343 +===========================================================================*/
2344 FUNCTION get_unpaid_amt(p_recon_id IN NUMBER) RETURN NUMBER
2345 IS
2346
2347 CURSOR amt_cur(c_recon_id IN NUMBER) IS
2348
2349 SELECT SUM(item.actual_amount) act_amt
2350 FROM pn_payment_items_all item,
2351 pn_payment_terms_all term,
2352 pn_opex_recon_all recon
2353 WHERE item.payment_item_type_lookup_code = 'CASH'
2354 AND item.payment_term_id = term.payment_term_id
2355 AND term.opex_recon_id = recon.recon_id
2356 AND recon.recon_id = c_recon_id ;
2357
2358 CURSOR recon_cur(c_recon_id IN NUMBER) IS
2359 SELECT * FROM
2360 pn_opex_recon_all WHERE recon_id = c_recon_id;
2361
2362 CURSOR recon_det_cur(c_recon_id IN NUMBER) IS
2363 SELECT NVL(expected_ovr , expected) AS amt FROM
2364 pn_opex_recon_details_all
2365 WHERE recon_id = c_recon_id
2366 AND TYPE = '1PRP';
2367
2368 CURSOR csr_recon_exp_grp(c_recon_id IN NUMBER) IS
2369 SELECT amount_st,
2370 recoverable_st
2371 FROM pn_opex_recon_exp_grp_all
2372 WHERE recon_id = c_recon_id;
2373
2374 amt_rec amt_cur%ROWTYPE;
2375 recon_rec recon_cur%ROWTYPE;
2376 recon_det_rec recon_det_cur%ROWTYPE;
2377
2378 unpaid_amount NUMBER;
2379 exp_grp_exst BOOLEAN := FALSE;
2380
2381
2382 BEGIN
2383 IF p_recon_id IS NULL THEN
2384 RETURN NULL;
2385 END IF;
2386 OPEN recon_cur(p_recon_id);
2387 FETCH recon_cur INTO recon_rec;
2388
2389 OPEN amt_cur(p_recon_id);
2390 FETCH amt_cur INTO amt_rec;
2391
2392 OPEN recon_det_cur(p_recon_id);
2393 FETCH recon_det_cur INTO recon_det_rec;
2394
2395 FOR rec IN csr_recon_exp_grp(p_recon_id) LOOP
2396 IF rec.amount_st IS NOT NULL OR
2397 rec.recoverable_st IS NOT NULL THEN
2398 exp_grp_exst := TRUE;
2399 EXIT;
2400 END IF;
2401 END LOOP;
2402
2403 IF recon_cur%ISOPEN THEN
2404 CLOSE recon_cur;
2405 END IF;
2406 IF amt_cur%ISOPEN THEN
2407 CLOSE amt_cur;
2408 END IF;
2409 IF recon_det_cur%ISOPEN THEN
2410 CLOSE recon_det_cur;
2411 END IF;
2412 IF csr_recon_exp_grp%ISOPEN THEN
2413 CLOSE csr_recon_exp_grp;
2414 END IF;
2415
2416 IF recon_rec.amt_due_st IS NOT NULL AND exp_grp_exst THEN
2417 unpaid_amount := recon_rec.amt_due_st - NVL(amt_rec.act_amt,0);
2418 RETURN unpaid_amount;
2419 ELSIF recon_rec.st_amt_due IS NOT NULL THEN
2420 unpaid_amount := recon_rec.st_amt_due - NVL(amt_rec.act_amt,0) - NVL(recon_det_rec.amt,0);
2421 RETURN unpaid_amount;
2422 ELSE
2423 RETURN NULL;
2424 END IF;
2425
2426 EXCEPTION
2427 WHEN OTHERS THEN
2428 RETURN NULL;
2429
2430 END get_unpaid_amt;
2431 END PN_OPEX_TERMS_PKG;