[Home] [Help]
PACKAGE BODY: APPS.PN_APPROVE_VARENT_PKG
Source
4 --------------------------------------------------------------------------------
1 PACKAGE BODY pn_approve_varent_pkg AS
2 -- $Header: PNVRAPPB.pls 120.16.12010000.5 2009/01/21 10:23:29 jsundara ship $
3
5 -- PROCEDURE : approve_payment_term
6 -- DESCRIPTION: This procedure is called by the variable rent payment term
7 -- approval concurrent program.
8 -- HISTORY:
9 -- 21-Feb-02 PSidhu o Added check to approve payment terms only if
10 -- main lease is in final status.Fix for bug# 2223128.
11 -- 28-Jun-02 Pidhu o Added p_org_id parameter to procedure
12 -- approve_payment_term_batch.
13 -- 15-Aug-02 DThota o Added p_period_date parameter to procedure
14 -- approve_payment_term_batch, CURSOR csr_get_inv for
15 -- Mass Calculate Variable Rent
16 -- 09-Jan-03 DThota o Changed p_period_date to VARCHAR2 from DATE in
17 -- approve_payment_term_batch and put in
18 -- fnd_date.canonical_to_date before p_period_date in
19 -- the WHERE clause of CURSOR csr_get_inv. Bug#2733870
20 -- 14-JUL-05 HRodda o Bug 4284035 - Replaced pn_leases with _ALL table.
21 -- 26-Oct-06 Shabda o Modified cursor csr_get_inv to accomodate true_up invoices
22 -- 27-Oct-06 Shabda o Modified cursor csr_get_inv to accomodate adjustment terms
23 -- for variance, which may not have corresponding forecasted
24 -- terms
25 -- 10-Apr-07 Lbala o Removed call to pn_variable_amount_pkg.get_prior_transfer_flag
26 -- for bug # 5965171
27 -- 31-MAy-07 Lokesh o Bug # 6079479 Changed Cursor csr_get_inv
28
29 --------------------------------------------------------------------------------
30
31 PROCEDURE approve_payment_term_batch
32 (
33 errbuf OUT NOCOPY VARCHAR2,
34 retcode OUT NOCOPY VARCHAR2,
35 p_lease_num_from IN VARCHAR2,
36 p_lease_num_to IN VARCHAR2,
37 p_location_code_from IN VARCHAR2,
38 p_location_code_to IN VARCHAR2,
39 p_vrent_num_from IN VARCHAR2,
43 p_responsible_user IN NUMBER,
40 p_vrent_num_to IN VARCHAR2,
41 p_period_num_from IN NUMBER,
42 p_period_num_to IN NUMBER,
44 p_var_rent_inv_id IN NUMBER,
45 p_var_rent_type IN VARCHAR2,
46 p_var_rent_id IN NUMBER,
47 p_org_id IN NUMBER,
48 p_period_date IN VARCHAR2
49
50 ) IS
51 CURSOR csr_get_vrent_wloc IS
52 SELECT pvr.var_rent_id,
53 pvr.rent_num,
54 pvr.invoice_on,
55 pl.status
56 FROM pn_leases pl,
57 pn_lease_details_all pld,
58 pn_var_rents_all pvr,
59 pn_locations_all ploc
60 WHERE pl.lease_id = pvr.lease_id
61 AND pld.lease_id = pvr.lease_id
62 AND ploc.location_id = pvr.location_id
63 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
64 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
65 AND ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
66 AND ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
67 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
68 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
69 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
70 AND (pl.org_id = p_org_id or p_org_id is null)
71 ORDER BY pl.lease_id, pvr.var_rent_id;
72
73 CURSOR csr_get_vrent_woloc IS
74 SELECT pvr.var_rent_id,
75 pvr.rent_num,
76 pvr.invoice_on,
77 pl.status
78 FROM pn_leases pl,
79 pn_var_rents_all pvr,
80 pn_lease_details_all pld
81 WHERE pl.lease_id = pvr.lease_id
82 AND pld.lease_id = pvr.lease_id
83 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
84 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
85 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
86 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
87 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
88 AND pvr.var_rent_id = NVL(p_var_rent_id,pvr.var_rent_id)
89 AND (pl.org_id = p_org_id or p_org_id is null)
90 ORDER BY pl.lease_id,pvr.var_rent_id;
91
92 CURSOR csr_get_inv(ip_var_rent_id NUMBER,
93 ip_rent_type VARCHAR2,
94 ip_var_rent_type VARCHAR2) IS
95 SELECT * FROM
96 (SELECT per.period_num,
97 inv.var_rent_inv_id,
98 inv.adjust_num,
99 inv.period_id,
100 inv.var_rent_id,
101 inv.invoice_date,
102 inv.for_per_rent,
103 inv.actual_invoiced_amount,
104 decode(ip_rent_type,'FORECASTED',inv.forecasted_term_status,'ACTUAL',inv.actual_term_status,
105 'VARIANCE',inv.variance_term_status) term_status
106 FROM pn_var_rent_inv_all inv,
107 pn_var_periods_all per
108 WHERE per.var_rent_id = inv.var_rent_id
109 AND per.period_id = inv.period_id
110 AND inv.var_rent_id = ip_var_rent_id
111 AND per.period_num >= NVL(p_period_num_from,per.period_num)
112 AND per.period_num <= NVL(p_period_num_to,period_num)
113 AND (inv.forecasted_exp_code = decode(inv.adjust_num
114 ,0,decode(ip_rent_type,'FORECASTED','N','VARIANCE','Y',inv.forecasted_exp_code)
115 ,inv.forecasted_exp_code)
116 OR inv.true_up_exp_code = 'N')
117 AND inv.actual_exp_code = decode(ip_rent_type,'ACTUAL','N',inv.actual_exp_code)
118 AND inv.variance_exp_code = decode(ip_rent_type,'VARIANCE','N',inv.variance_exp_code)
119 AND inv.var_rent_inv_id = NVL(p_var_rent_inv_id,inv.var_rent_inv_id)
120 AND ip_rent_type = NVL(ip_var_rent_type,ip_rent_type)
121 AND invoice_date <= NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'))
122 UNION
123 SELECT per.period_num,
124 inv.var_rent_inv_id,
125 inv.adjust_num,
126 inv.period_id,
127 inv.var_rent_id,
128 inv.invoice_date,
129 inv.for_per_rent,
130 inv.actual_invoiced_amount,
131 decode(ip_rent_type,'FORECASTED',inv.forecasted_term_status,'ACTUAL',inv.actual_term_status,
132 'VARIANCE',inv.variance_term_status) term_status
133 FROM pn_var_rent_inv_all inv,
134 pn_var_periods_all per,
135 pn_payment_terms_all pmt
136 WHERE per.var_rent_id = inv.var_rent_id
137 AND per.period_id = inv.period_id
138 AND inv.var_rent_id = ip_var_rent_id
139 AND per.period_num >= NVL(p_period_num_from,per.period_num)
140 AND per.period_num <= NVL(p_period_num_to,period_num)
141 AND (inv.forecasted_exp_code = decode(inv.adjust_num
145 AND inv.actual_exp_code = decode(ip_rent_type,'ACTUAL','N',inv.actual_exp_code)
142 ,0,decode(ip_rent_type,'FORECASTED','N','VARIANCE','Y',inv.forecasted_exp_code)
143 ,inv.forecasted_exp_code)
144 OR inv.true_up_exp_code = 'N')
146 AND inv.variance_exp_code = decode(ip_rent_type,'VARIANCE','N',inv.variance_exp_code)
147 AND pmt.var_rent_inv_id = inv.var_rent_inv_id
148 AND pmt.status = 'APPROVED'
149 AND ip_rent_type = NVL(ip_var_rent_type,ip_rent_type)
150 AND invoice_date <= NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'))
151 )temp
152 ORDER BY period_num,invoice_date;
153
154
155 l_var_rent_id pn_var_rents.var_rent_id%type;
156 l_rent_num pn_var_rents.rent_num%type;
157 l_invoice_on pn_var_rents.invoice_on%type;
158 l_lease_status pn_leases.status%type;
159 l_rent_type pn_payment_terms.var_rent_type%type;
160 l_rent_amt NUMBER;
161 l_exists VARCHAR2(1) := 'N';
162 l_errmsg VARCHAR2(2000);
163 l_errmsg1 VARCHAR2(2000);
164 l_counter NUMBER := 0;
165 l_context VARCHAR2(2000);
166 l_var_rent_type VARCHAR2(30);
167 l_inv_sch_date DATE;
168 l_inv_start_date DATE;
169
170
171 BEGIN
172
173
174 pn_variable_amount_pkg.put_log('pn_approve_varent_pkg.approve_payment_term (+)' );
175
176 fnd_message.set_name ('PN','PN_VRAP_PRM');
177 fnd_message.set_token ('LSNO_FRM',p_lease_num_from);
178 fnd_message.set_token ('LSNO_TO',p_lease_num_to);
179 fnd_message.set_token ('LOC_CODE_FRM',p_location_code_from);
180 fnd_message.set_token ('LOC_CODE_TO',p_location_code_to);
181 fnd_message.set_token ('VR_FRM',p_vrent_num_from);
182 fnd_message.set_token ('VR_TO',p_vrent_num_to);
183 fnd_message.set_token ('PRD_FRM',p_period_num_from);
184 fnd_message.set_token ('PRD_TO',p_period_num_to);
185 fnd_message.set_token ('RESP_USR',p_responsible_user);
186 pnp_debug_pkg.put_log_msg(fnd_message.get);
187
188
189 /* Checking Location Code From, Location Code To to open appropriate cursor */
190
191 IF p_location_code_from IS NOT NULL or p_location_code_to IS NOT NULL THEN
192 OPEN csr_get_vrent_wloc;
193 ELSE
194 OPEN csr_get_vrent_woloc;
195 END IF;
196
197 LOOP
198 IF csr_get_vrent_wloc%ISOPEN THEN
199 FETCH csr_get_vrent_wloc
200 INTO l_var_rent_id
201 ,l_rent_num
202 ,l_invoice_on
203 ,l_lease_status;
204 EXIT WHEN csr_get_vrent_wloc%NOTFOUND;
205 ELSIF csr_get_vrent_woloc%ISOPEN THEN
206 FETCH csr_get_vrent_woloc
207 INTO l_var_rent_id
208 ,l_rent_num
209 ,l_invoice_on
210 ,l_lease_status;
211 EXIT WHEN csr_get_vrent_woloc%NOTFOUND;
212 END IF;
213
214
215
216 fnd_message.set_name ('PN','PN_LEASE_STATUS');
217 fnd_message.set_token ('STATUS',l_lease_status);
218 pnp_debug_pkg.put_log_msg(fnd_message.get);
219
220 fnd_message.set_name ('PN','PN_SOI_VRN');
221 fnd_message.set_token ('NUM',l_rent_num);
222 pnp_debug_pkg.put_log_msg(fnd_message.get);
223
224
225 IF NVL(l_lease_status,'D') = 'D' THEN
226
227 /* If main lease is in draft status do not approve any payment term */
228 fnd_message.set_name('PN','PN_NO_APPR_TERM');
229 l_errmsg1 := fnd_message.get;
230 pn_variable_amount_pkg.put_output
231 ('+----------------------------------------------------------+');
232 pn_variable_amount_pkg.put_output(l_errmsg1);
233 pn_variable_amount_pkg.put_output
234 ('+----------------------------------------------------------+');
235
236
237
238 ELSE
239
240 IF l_invoice_on = 'FORECASTED' THEN
241 l_rent_type := 'VARIANCE';
242 ELSIF l_invoice_on = 'ACTUAL' THEN
243 l_rent_type := 'ACTUAL';
244 END IF;
245
246 IF p_var_rent_type = 'ADJUSTMENT' THEN
247 IF l_invoice_on = 'FORECASTED' THEN
248 l_var_rent_type := 'VARIANCE';
249 ELSIF l_invoice_on = 'ACTUAL' THEN
250 l_var_rent_type := 'ACTUAL';
251 END IF;
252 ELSE
253 l_var_rent_type := p_var_rent_type;
254 END IF;
255
256 IF l_invoice_on = 'FORECASTED' THEN
257
258 pn_variable_amount_pkg.put_output
259 ('+-----------------------------------------------------------+');
260 fnd_message.set_name ('PN','PN_VTERM_FORC_TRM');
261 pnp_debug_pkg.put_log_msg(fnd_message.get||' ...');
262
263 --l_var_rent_type := 'FORECASTED';
264
265 FOR rec_get_inv in csr_get_inv (l_var_rent_id,'FORECASTED','FORECASTED')
266 LOOP
267
268 fnd_message.set_name ('PN','PN_VTERM_PRD_NUM');
269 fnd_message.set_token ('NUM',rec_get_inv.period_num);
270 pnp_debug_pkg.put_log_msg(fnd_message.get);
271
272
273 /*l_inv_start_date := pn_var_rent_calc_pkg.inv_start_date(inv_start_date => rec_get_inv.invoice_date
274 ,vr_id => l_var_rent_id
275 ,approved_status => 'N'); */
276
277 l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date(inv_start_date => rec_get_inv.invoice_date
278 ,vr_id => l_var_rent_id
282 pnp_debug_pkg.put_log_msg(fnd_message.get);
279 ,p_period_id => rec_get_inv.period_id );
280 fnd_message.set_name ('PN','PN_SOI_INV_DT');
281 fnd_message.set_token ('DATE',l_inv_sch_date);
283
284
285 IF rec_get_inv.term_status = 'Y' OR
286 NVL(rec_get_inv.for_per_rent,-1)= 0 THEN
287
288 set_transferred_code
289 ( p_var_rent_inv_id => rec_get_inv.var_rent_inv_id
290 ,p_rent_type => 'FORECASTED'
291 ,p_term_status => rec_get_inv.term_status
292 ,p_rent_amt => rec_get_inv.for_per_rent
293 ,p_period_id => rec_get_inv.period_id
294 ,p_var_rent_id => rec_get_inv.var_rent_id
295 ,p_invoice_date => rec_get_inv.invoice_date
296 ,p_counter => l_counter);
297
298
299 END IF; --NVL(l_exists,'N') = 'Y'
300
301 END LOOP;
302
303 fnd_message.set_name ('PN','PN_VTERM_AFORC_TRM');
304 fnd_message.set_token ('DATE',l_counter);
305 pnp_debug_pkg.put_log_msg(fnd_message.get);
306
307
308 END IF; --l_invoice_on = 'FORECASTED'
309
310 l_counter := 0;
311 pn_variable_amount_pkg.put_log
312 ('+-----------------------------------------------------------+');
313 pn_variable_amount_pkg.put_log
314 ('Processing '||INITCAP(l_rent_type)||' terms.... ');
315
316
317 FOR rec_get_inv in csr_get_inv(l_var_rent_id,l_rent_type, l_var_rent_type)
318 LOOP
319
320 fnd_message.set_name ('PN','PN_VTERM_PRD_NUM');
321 fnd_message.set_token ('NUM',rec_get_inv.period_num);
322 pnp_debug_pkg.put_log_msg(fnd_message.get);
323
324 /*l_inv_start_date := pn_var_rent_calc_pkg.inv_start_date(inv_start_date => rec_get_inv.invoice_date
325 ,vr_id => l_var_rent_id
326 ,approved_status => 'N'); */
327
328 l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date(inv_start_date => rec_get_inv.invoice_date
329 ,vr_id => l_var_rent_id
330 ,p_period_id => rec_get_inv.period_id );
331 fnd_message.set_name ('PN','PN_SOI_INV_DT');
332 fnd_message.set_token ('DATE',l_inv_sch_date);
333 pnp_debug_pkg.put_log_msg(fnd_message.get);
334
335 l_rent_amt := NULL;
336
337 IF l_rent_type ='VARIANCE' and rec_get_inv.adjust_num = 0 THEN
338 l_rent_amt
339 := rec_get_inv.actual_invoiced_amount - rec_get_inv.for_per_rent;
340 ELSE
341 l_rent_amt := rec_get_inv.actual_invoiced_amount;
342 END IF;
343
344 IF rec_get_inv.term_status = 'Y' or NVL(l_rent_amt,-1) = 0 THEN
345
346 set_transferred_code
347 ( p_var_rent_inv_id => rec_get_inv.var_rent_inv_id
348 ,p_rent_type => l_rent_type
349 ,p_term_status => rec_get_inv.term_status
350 ,p_rent_amt => l_rent_amt
351 ,p_period_id => rec_get_inv.period_id
352 ,p_var_rent_id => rec_get_inv.var_rent_id
353 ,p_invoice_date => rec_get_inv.invoice_date
354 ,p_counter => l_counter);
355
356 END IF; --NVL(l_exists,'N') = 'Y'
357
358 END LOOP;
359
360 fnd_message.set_name ('PN','PN_VRAP_PROC');
361 fnd_message.set_token ('STATUS',INITCAP(l_rent_type));
362 fnd_message.set_token ('NUM',l_counter);
363 pnp_debug_pkg.put_log_msg(fnd_message.get);
364
365 END IF; --NVL(l_lease_status,'DRAFT') = 'DRAFT'
366
367 END LOOP;
368
369 pn_variable_amount_pkg.put_log('pn_approve_varent_pkg.approve_payment_term (-) : ');
370
371 EXCEPTION
372
373 WHEN OTHERS THEN
374 pn_variable_amount_pkg.put_log
375 (SUBSTRB('Error in pn_approve_varent_pkg.approved_payment_term_batch : '||
376 TO_CHAR(sqlcode)||
377 ' - '||
378 sqlerrm ||
379 ' : '||
380 l_context,1,244));
381 Errbuf := SQLERRM;
382 Retcode := 2;
383 ROLLBACK;
384 RAISE;
385
386 END approve_payment_term_batch;
387
388 --------------------------------------------------------------------------------
389 -- PROCEDURE : set_transferred_code
390 -- DESCRIPTION: This procedure sets the exported codes in pn_var_grp_dates and
391 -- pn_var_vol_hist to 'Y' and exported code in pn_var_deductions
392 -- to 'Y' if variable rent term type is 'ACTUAL' or 'VARIANCE'.
393 -- Also creates schedules and items if a terms exists for the
394 -- variable rent amount.
395 -- 21-Feb-02 Psidhu o Added call to pnt_payment_terms_pkg.
396 -- check_approved_schedule_exists. Approve a payment
397 -- term if period of payment term does not overlap
398 -- with an existing approved schedule. Bug # 2235148.
399 -- 22-NOV-04 Kiran o Bug 3751438 - rewrote to ensure the term details and
400 -- distributions are validated.
401 -- 23-NOV-05 pikhar o Passed org_id in pn_mo_cache_utils.get_profile_value
402 -- 09-JAN-07 lbala o Removed call to check_approved_schedule_exists for
403 -- M28 item# 11
404 -- 12-Mar-07 Shabda o 5911819 - Modified vol_hist_all update stamements.
405 -- 30-NOV-07 rkartha o Bug#7652214 Correcting the forward port of Bug#6412170.
406 -- Added parameters customer_id and CUSTOMER_SITE_USE_ID to
410 --------------------------------------------------------------------------------
407 -- cursor csr_get_term and E_NO_CUST EXCEPTION to check if
408 -- the term has customer info and bill-to-site.
409 -- If the terms doesnt have, then it cant be approved.
411 PROCEDURE set_transferred_code
412 (
413 p_var_rent_inv_id NUMBER,
414 p_rent_type VARCHAR2,
415 p_term_status VARCHAR2,
416 p_rent_amt NUMBER,
417 p_period_id NUMBER,
418 p_var_rent_id NUMBER,
419 p_invoice_date DATE,
420 p_counter IN OUT NOCOPY NUMBER
421 ) IS
422
423 /* -- CURSORS -- */
424
425 /* get all the term info */
426 CURSOR csr_get_term(ip_var_rent_inv_id NUMBER,
427 ip_rent_type VARCHAR2) IS
428 SELECT payment_term_id
429 ,lease_id
430 ,actual_amount
431 ,schedule_day
432 ,start_date
433 ,end_date
434 ,normalize
435 ,project_id
436 ,task_id
437 ,organization_id
438 ,expenditure_type
439 ,expenditure_item_date
440 ,distribution_set_id
441 ,org_id
442 ,customer_id
443 ,CUSTOMER_SITE_USE_ID
444 FROM pn_payment_terms_all
445 WHERE var_rent_inv_id = ip_var_rent_inv_id
446 /*AND var_rent_type = ip_rent_type*/;
447
448 /* get the distributions for a term */
449 CURSOR get_distributions_c (p_term_ID IN NUMBER) IS
450 SELECT pd.account_class
451 ,pd.percentage
452 FROM pn_distributions_all pd
453 WHERE pd.payment_term_ID = p_term_ID;
454
455 /* get the account class meaning from lookups */
456 CURSOR get_acc_class_c (p_acc_class_code IN VARCHAR2) is
457 SELECT meaning
458 FROM fnd_lookups
459 WHERE lookup_code = p_acc_class_code
460 AND lookup_type in ('PN_PAY_ACCOUNT_TYPE','PN_REC_ACCOUNT_TYPE');
461
462 /* get lease class code */
463 CURSOR get_lease_class(p_lease_ID IN NUMBER) IS
464 SELECT lease_class_code
465 FROM pn_leases_all
469 -- Get the details of
466 WHERE lease_ID = p_lease_ID;
467
468
470 CURSOR period_FY_cur(p_period_id NUMBER,
471 p_var_rent_id NUMBER) IS
472 SELECT period_id
473 FROM pn_var_periods_all pvp, pn_var_rents_all pvr
474 WHERE pvr.var_rent_id = pvp.var_rent_id
475 AND pvp.period_id = p_period_id
476 AND pvr.var_rent_id = p_var_rent_id
477 AND pvr.proration_rule IN ('FLY', 'FY')
478 AND pvp.period_num = 1;
479
480
481 l_payment_term_id pn_payment_terms.payment_term_id%TYPE := NULL;
482 l_lease_id pn_payment_terms.lease_id%TYPE := NULL;
483 l_actual_amt pn_payment_terms.actual_amount%TYPE := NULL;
484 l_schedule_day pn_payment_terms.schedule_day%TYPE := NULL;
485 l_start_date pn_payment_terms.start_date%TYPE := NULL;
486 l_end_date pn_payment_terms.end_date%TYPE := NULL;
487 l_normalize pn_payment_terms_all.normalize%TYPE := NULL;
488 l_project_id pn_payment_terms_all.project_id%TYPE := NULL;
489 l_task_id pn_payment_terms_all.task_id%TYPE := NULL;
490 l_organization_id pn_payment_terms_all.organization_id%TYPE := NULL;
491 l_expenditure_type pn_payment_terms_all.expenditure_type%TYPE := NULL;
492 l_expenditure_item_date pn_payment_terms_all.expenditure_item_date%TYPE := NULL;
493 l_distribution_set_id pn_payment_terms_all.distribution_set_id%TYPE := NULL;
494 l_org_id pn_payment_terms_all.org_id%TYPE;
495 l_cust_id pn_payment_terms_all.customer_id%type;
496 l_cust_site_id pn_payment_terms_all.CUSTOMER_SITE_USE_ID%type;
497 l_errmsg1 VARCHAR2(2000);
498
499
500 E_NO_CUST EXCEPTION; /*Exception added for bug#6412170 */
501
502
503 l_return_status VARCHAR2 (2) := NULL;
504 l_errbuf VARCHAR2(2000) := NULL;
505 l_retcode VARCHAR2(2000) := NULL;
506 l_errmsg VARCHAR2(2000) := NULL;
507 l_error BOOLEAN;
508
509 /* -- variables for validating the term distributions -- */
510
511 /* accounting option */
512 l_acc_optn VARCHAR2(30);
513 /* account flags to indicate existance of accounts */
514 l_exp_rev_exists BOOLEAN;
515 l_lia_rec_exists BOOLEAN;
516 l_acc_unearn_exists BOOLEAN;
517 /* account percent allocations */
518 l_exp_rev_dist_pct NUMBER;
519 l_lia_rec_dist_pct NUMBER;
520 l_acc_unearn_dist_pct NUMBER;
521 /* account class count */
525 l_unearn_count NUMBER;
522 l_rec_count NUMBER;
523 l_lia_count NUMBER;
524 l_acc_count NUMBER;
526 l_acc_class VARCHAR2(90);
527 l_acc_class_code VARCHAR2(30);
528 /* total number of dist */
529 l_dist_count NUMBER;
530
531 /* exception */
532 TERM_DIST_INVALID EXCEPTION;
533
534 l_lease_class_code VARCHAR2(30);
535
536 /* -- variables to verify project info -- */
537 l_status VARCHAR2(2000);
538 l_bill_flag VARCHAR2(10);
539 l_msg_app VARCHAR2(30);
540 l_msg_type VARCHAR2(30);
541 l_msg_token1 VARCHAR2(30);
542 l_msg_token2 VARCHAR2(30);
543 l_msg_token3 VARCHAR2(30);
544 l_msg_count NUMBER;
545
546 /* -- variables for validating the term distributions -- */
547
548 BEGIN
549
550 pnp_debug_pkg.log('p_rent_type:'||p_rent_type);
551 pn_variable_amount_pkg.put_log('pn_approve_varent_pkg.set_transferred_code (+) : ');
552
553 l_error := FALSE;
554
555 IF p_term_status = 'Y' THEN
556
557 FOR rec IN csr_get_term(p_var_rent_inv_id,p_rent_type) LOOP
558
559 l_payment_term_id := rec.payment_term_id;
560 l_lease_id := rec.lease_id;
561 l_actual_amt := rec.actual_amount;
562 l_schedule_day := rec.schedule_day;
563 l_start_date := rec.start_date;
564 l_end_date := rec.end_date;
565 l_normalize := rec.normalize;
566 l_project_id := rec.project_id;
567 l_task_id := rec.task_id;
568 l_organization_id := rec.organization_id;
569 l_expenditure_type := rec.expenditure_type;
570 l_expenditure_item_date := rec.expenditure_item_date;
571 l_distribution_set_id := rec.distribution_set_id;
572 l_org_id := rec.org_id;
573 l_cust_id := rec.customer_id;
574 l_cust_site_id := rec.CUSTOMER_SITE_USE_ID;
575
576
577 fnd_message.set_name ('PN','PN_VRAP_VAL');
578 fnd_message.set_token ('DATE',l_start_date);
579 fnd_message.set_token ('DAY',l_schedule_day);
580 fnd_message.set_token ('AMT',l_actual_amt);
581 fnd_message.set_token ('ID',l_payment_term_id);
582 pnp_debug_pkg.put_log_msg(fnd_message.get);
583
584 /* check if term distributions are correct */
585 /* check if an approved schedule exists overlapping the period of the term */
586 BEGIN
587
588 /* init variables */
589 /* init the exists flags and counters */
590 l_acc_optn := NVL( pn_mo_cache_utils.get_profile_value
591 ('PN_ACCOUNTING_OPTION',l_org_id),'Y');
592
593 l_exp_rev_exists := FALSE;
594 l_lia_rec_exists := FALSE;
595 l_acc_unearn_exists := FALSE;
596
597 l_exp_rev_dist_pct := 0;
598 l_lia_rec_dist_pct := 0;
599 l_acc_unearn_dist_pct := 0;
600
601 l_rec_count := 0;
602 l_lia_count := 0;
603 l_acc_count := 0;
604 l_unearn_count := 0;
605 l_acc_class := NULL;
606
607 l_dist_count := 0;
608
609
610 FOR lease_rec IN get_lease_class(l_lease_id) LOOP
611 l_lease_class_code := lease_rec.lease_class_code;
612 END LOOP;
613
614
615 IF l_lease_class_code not in ('DIRECT') THEN
616 /* If Customer info is not present, do not approve the terms bug#6412170*/
617 IF (l_cust_id is null) THEN
618 fnd_message.set_name('PN','PN_APPR_NO_CUST');
619 l_errmsg1 := fnd_message.get;
620 RAISE E_NO_CUST;
621
622 /* If bill-to-Site is not present, do not approve the terms bug#6412170*/
623 ELSIF (l_cust_site_id is null) THEN
624 fnd_message.set_name('PN','PN_APPR_NO_SITE');
625 l_errmsg1 := fnd_message.get;
626 RAISE E_NO_CUST;
627
628 END IF;
629 END IF; /* 7712952 */
630
631 FOR dist_rec IN get_distributions_c(l_payment_term_id) LOOP
632
633 IF dist_rec.account_class IN ('EXP','REV') THEN
634 l_exp_rev_exists := TRUE;
638 l_lia_rec_exists := TRUE;
635 l_exp_rev_dist_pct := l_exp_rev_dist_pct + NVL(dist_rec.percentage,0);
636
637 ELSIF dist_rec.account_class IN ('LIA','REC') THEN
639 l_lia_rec_dist_pct := l_lia_rec_dist_pct + NVL(dist_rec.percentage,0);
640
641 IF dist_rec.account_class = 'LIA' THEN
642 l_lia_count := l_lia_count + 1;
643 ELSE
647 ELSIF dist_rec.account_class IN ('ACC','UNEARN') THEN
644 l_rec_count := l_rec_count + 1;
645 END IF;
646
648 l_acc_unearn_exists := TRUE;
649 l_acc_unearn_dist_pct := l_acc_unearn_dist_pct + NVL(dist_rec.percentage,0);
650
651 IF dist_rec.account_class = 'ACC' THEN
652 l_acc_count := l_acc_count + 1;
653 ELSE
654 l_unearn_count := l_unearn_count + 1;
655 END IF;
656
657 END IF;
658
659 l_dist_count := l_dist_count + 1;
660
661 END LOOP; /* get_distributions_c */
662
663 IF l_dist_count > 0 AND
664 (l_project_id IS NULL AND
665 l_task_id IS NULL AND
666 l_organization_id IS NULL AND
667 l_expenditure_type IS NULL AND
668 l_expenditure_item_date IS NULL) AND
669 l_distribution_set_id IS NULL
670 THEN
671 /* distributions exist, NO project info, NO distribution sets */
672
673 /* REC, LIA, ACC, UNEARN cannot be split */
674 IF l_lia_count > 1 OR
675 l_rec_count > 1 OR
676 l_acc_count > 1 OR
677 l_unearn_count > 1
678 THEN
679
680 IF l_lia_count > 1 THEN
681 FOR cls_rec IN get_acc_class_c('LIA') LOOP
682 l_acc_class := cls_rec.meaning;
683 END LOOP;
684
685 ELSIF l_rec_count > 1 THEN
686 FOR cls_rec IN get_acc_class_c('REC') LOOP
687 l_acc_class := cls_rec.meaning;
688 END LOOP;
689
690 ELSIF l_acc_count > 1 THEN
691 FOR cls_rec IN get_acc_class_c('ACC') LOOP
692 l_acc_class := cls_rec.meaning;
693 END LOOP;
694
695 ELSIF l_unearn_count > 1 THEN
696 FOR cls_rec IN get_acc_class_c('UNEARN') LOOP
697 l_acc_class := cls_rec.meaning;
698 END LOOP;
699
700 END IF;
701
702 fnd_message.set_name('PN', 'PN_DUP_ACCOUNT_IN_DIST');
703 fnd_message.set_token('ACCOUNT_CLASS', l_acc_class);
704 fnd_message.set_token('ACCOUNT_CLASS_A', l_acc_class);
705 RAISE TERM_DIST_INVALID;
706
707 END IF;
708
709 /* percentages for all account classes must add upto 100 */
710 IF (l_exp_rev_exists AND l_exp_rev_dist_pct <> 100) OR
711 (l_lia_rec_exists AND l_lia_rec_dist_pct <> 100) OR
712 (l_acc_unearn_exists AND l_acc_unearn_dist_pct <> 100)
713 THEN
714
715 fnd_message.set_name('PN', 'PN_DIST_PRCNT_TTL_MSG');
716 RAISE TERM_DIST_INVALID;
717
718 END IF;
719
720 END IF; /* distributions exist, NO project info, NO distribution sets */
721
722 IF l_lease_class_code = 'DIRECT' THEN
723
724 IF NVL(l_normalize, 'N') = 'Y' THEN
725
726 IF NOT (l_exp_rev_exists AND
727 l_acc_unearn_exists) THEN
728
729 fnd_message.set_name('PN', 'PN_ACC_NORMALISED');
730 RAISE TERM_DIST_INVALID;
731
732 END IF; /* all dist does not exist */
733
734 ELSE
735
736 IF (l_project_id IS NULL AND
737 l_task_id IS NULL AND
738 l_organization_id IS NULL AND
739 l_expenditure_type IS NULL AND
740 l_expenditure_item_date IS NULL) AND
741 l_distribution_set_id IS NULL
742 THEN
743 /* DO NOT REMOVE THIS CONDITION */
744 IF NOT l_exp_rev_exists THEN
745
746 IF l_dist_count > 0 THEN
747 fnd_message.set_name('PN', 'PN_EXP_DIST_MSG');
748 ELSE
749 fnd_message.set_name('PN', 'PN_ACC_UNNORMALISED');
750 END IF;
751 RAISE TERM_DIST_INVALID;
752
753 END IF; /* exp acc does not exist */
754
755 ELSE
756
757 IF l_distribution_set_id IS NULL THEN
758
759 IF (l_project_id IS NULL OR
760 l_task_id IS NULL OR
761 l_organization_id IS NULL OR
762 l_expenditure_type IS NULL OR
763 l_expenditure_item_date IS NULL) THEN
764
765 fnd_message.set_name('PN', 'PN_ACC_UNNORMALISED');
766 RAISE TERM_DIST_INVALID;
767
768 ELSE
769
770 /* all project information is here - so validate */
771 PATC.get_status
772 ( x_project_id => l_project_id
773 , x_task_id => l_task_id
774 , x_ei_date => l_expenditure_item_date
775 , x_expenditure_type => l_expenditure_type
776 , x_non_labor_resource => NULL
777 , x_person_id => NULL
778 , x_incurred_by_org_id => l_organization_id
779 , x_msg_application => l_msg_app
780 , x_msg_type => l_msg_type
781 , x_msg_token1 => l_msg_token1
782 , x_msg_token2 => l_msg_token2
783 , x_msg_token3 => l_msg_token3
784 , x_msg_count => l_msg_count
788 IF l_status IS NOT NULL THEN
785 , x_status => l_status
786 , x_billable_flag => l_bill_flag);
787
789
790 fnd_message.set_name('PA', l_status);
791 RAISE TERM_DIST_INVALID;
792
793 END IF;
794
795 END IF; /* incomplete project info */
796
797 END IF; /* no distribution set */
798
799 END IF; /* existance of project info or distribution set */
800
801 END IF; /* normalize */
802
803 ELSIF l_lease_class_code IN ('SUB_LEASE', 'THIRD_PARTY') THEN
807 /* for normalized terms, we need not look at the accounting
804
805 IF NVL(l_normalize, 'N') = 'Y' THEN
806
808 option. After revenue recognition, all accounts needed */
809 IF NOT (l_exp_rev_exists AND
810 l_lia_rec_exists AND
811 l_acc_unearn_exists)
812 THEN
813
814 fnd_message.set_name('PN', 'PN_ALL_ACNT_DIST_MSG');
815 RAISE TERM_DIST_INVALID;
816
817 END IF;
818
819 ELSE
820 /* The business rule is
821 - If Accounting Option = Y (All Terms) then we need REC and REV
822 - If Accounting Option = M or N (Normalized Terms or None) then,
823 either no dist OR both REC and REV
824 Since we know that distributions exist, we just check if
825 _BOTH_ REV and REC exist
826 */
827 IF l_acc_optn = 'Y' THEN
828
829 IF NOT (l_exp_rev_exists AND l_lia_rec_exists) THEN
830
831 fnd_message.set_name('PN', 'PN_REVREC_DIST_MSG');
832 RAISE TERM_DIST_INVALID;
833
834 END IF;
835
836 ELSIF l_acc_optn IN ('M', 'N') THEN
837
838 IF l_dist_count > 0 AND
839 NOT (l_exp_rev_exists AND l_lia_rec_exists) THEN
840
841 fnd_message.set_name('PN', 'PN_REVREC_DIST_MSG');
842 RAISE TERM_DIST_INVALID;
843
844 END IF;
845
846 END IF; /* accounting option */
847
848 END IF; /* normalize */
849
850 END IF; /* l_lease_class_code */
851
852 EXCEPTION
853 WHEN TERM_DIST_INVALID THEN
854 l_error := TRUE;
855 l_errmsg := fnd_message.get;
856 pn_variable_amount_pkg.put_output
857 ('+----------------------------------------------------------+');
858 pn_variable_amount_pkg.put_output(l_errmsg);
859 pn_variable_amount_pkg.put_output
860 ('+----------------------------------------------------------+');
861
862 END;
863
864 IF NOT l_error THEN
865 pn_schedules_items.schedules_items
866 ( errbuf => l_errbuf
867 ,retcode => l_retcode
868 ,p_lease_id => l_lease_id
869 ,p_lease_context => 'ADD'
870 ,p_called_from => 'VAR'
871 ,p_term_id => l_payment_term_id
872 ,p_term_end_dt => NULL);
873
874 IF NVL(l_retcode,-1) <> 2 THEN
875 UPDATE pn_payment_terms_all
876 SET status = 'APPROVED',
877 last_update_date = SYSDATE,
878 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
879 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
880 approved_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
881 WHERE payment_term_id = l_payment_term_id;
882 END IF;
883 END IF;
884
885 END LOOP;
886
890 NVL(p_rent_amt,-1) = 0 THEN
887 END IF; --p_term_status = 'Y'
888
889 IF (p_term_status = 'Y' AND NVL(l_retcode,-1) <> 2 AND NOT l_error) OR
891
892 p_counter := p_counter + 1;
893
894 /* Set the variance exp code to 'Y' if p_rent_type is 'FORECASTED',
895 actual_exp_code to 'Y' if p_rent_type is 'ACTUAL' and variance_exp_code to
896 'Y' if p_rent_type is 'VARIANCE' in pn_var_grp_dates and pn_var_vol_hist
897 to 'Y'
898 */
899 UPDATE pn_var_rent_inv_all
900 SET forecasted_exp_code = DECODE(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
901 actual_exp_code = DECODE(p_rent_type,'ACTUAL','Y',actual_exp_code),
902 variance_exp_code = DECODE(p_rent_type,'VARIANCE','Y',variance_exp_code),
903 last_update_date = SYSDATE,
904 last_updated_by = NVL(fnd_profile.VALUE ('USER_ID'), 0),
905 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
906 WHERE var_rent_inv_id = p_var_rent_inv_id;
907
908 /* pinky */
909 UPDATE pn_var_rent_inv_all
910 SET true_up_exp_code = 'Y'
911 WHERE var_rent_inv_id = p_var_rent_inv_id
912 AND true_up_exp_code IS NOT NULL;
913 /* pinky */
914
915 UPDATE pn_var_grp_dates_all
916 SET forecasted_exp_code = decode(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
917 actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
918 variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
919 last_update_date = SYSDATE,
920 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
921 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
922 WHERE period_id = p_period_id
923 AND invoice_date = p_invoice_date
924 AND var_rent_id = p_var_rent_id;
925
926 UPDATE pn_var_vol_hist_all
927 SET forecasted_exp_code = decode(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
928 last_update_date = SYSDATE,
929 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
930 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
931 WHERE period_id = p_period_id
932 AND p_rent_type = 'FORECASTED'
933 AND grp_date_id IN
934 (SELECT grp_date_id
935 FROM pn_var_grp_dates_all
936 WHERE period_id = p_period_id
937 AND invoice_date = p_invoice_date
938 AND var_rent_id = p_var_rent_id);
939
940
941
942 UPDATE pn_var_vol_hist_all
943 SET actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
944 variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
945 last_update_date = SYSDATE,
946 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
947 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
948 WHERE period_id = p_period_id
949 AND vol_hist_status_code = 'APPROVED'
950 AND p_rent_type <> 'FORECASTED'
951 AND grp_date_id IN
952 (SELECT grp_date_id
953 FROM pn_var_grp_dates_all
954 WHERE period_id = p_period_id
955 AND invoice_date = p_invoice_date
956 AND var_rent_id = p_var_rent_id);
957
958 /* update the transferred flag in pn_var_deductions to 'Y' for all the group
959 dates that have been transferred only if variable rent term type is actual
960 or variance */
961
962 IF p_rent_type in ('ACTUAL','VARIANCE') THEN
963 UPDATE pn_var_deductions_all
964 SET exported_code = 'Y',
965 last_update_date = SYSDATE,
966 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
970 (SELECT grp_date_id
967 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
968 WHERE period_id = p_period_id
969 AND grp_date_id IN
971 FROM pn_var_grp_dates_all
972 WHERE period_id = p_period_id
973 AND invoice_date = p_invoice_date
974 AND var_rent_id = p_var_rent_id);
975 END IF;
976
977 /* Special handling for period number 1 for FLY or FY agreement */
978 FOR rec IN period_FY_cur(p_period_id,p_var_rent_id)
979 LOOP
980
981 UPDATE pn_var_grp_dates_all
982 SET forecasted_exp_code = decode(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
983 actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
984 variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
985 last_update_date = SYSDATE,
986 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
987 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
988 WHERE period_id = p_period_id
989 AND var_rent_id = p_var_rent_id;
990
994 last_update_date = SYSDATE,
991 UPDATE pn_var_vol_hist_all
992 SET actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
993 variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
995 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
996 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
997 WHERE period_id = p_period_id
998 AND vol_hist_status_code = 'APPROVED'
999 AND p_rent_type <> 'FORECASTED'
1000 AND grp_date_id IN
1001 (SELECT grp_date_id
1002 FROM pn_var_grp_dates_all
1003 WHERE period_id = p_period_id);
1004
1005 UPDATE pn_var_deductions_all
1006 SET exported_code = 'Y',
1007 last_update_date = SYSDATE,
1008 last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
1009 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
1010 WHERE period_id = p_period_id
1011 AND p_rent_type <> 'FORECASTED'
1012 AND grp_date_id IN
1013 (SELECT grp_date_id
1014 FROM pn_var_grp_dates_all
1015 WHERE period_id = p_period_id);
1016
1017
1018 END LOOP;
1019
1020 END IF;
1021
1022 pn_variable_amount_pkg.put_log('pn_approve_varent_pkg.set_transferred_code (-) : ');
1023
1024 EXCEPTION
1025
1026 WHEN E_NO_CUST THEN
1027 pn_variable_amount_pkg.put_output
1028 ('+----------------------------------------------------------+');
1029 pn_variable_amount_pkg.put_output(l_errmsg1);
1030 pn_variable_amount_pkg.put_output('+----------------------------------------------------------+');
1031
1032 WHEN OTHERS THEN
1033 pn_variable_amount_pkg.put_log
1034 ('Error in pn_approve_varent_pkg.set_transferred_code - '||sqlerrm);
1035
1036 END set_transferred_code;
1037
1038 END pn_approve_varent_pkg;