[Home] [Help]
PACKAGE BODY: APPS.PA_EXP_COPY
Source
1 PACKAGE BODY PA_EXP_COPY AS
2 /* $Header: PAXTEXCB.pls 120.4 2006/07/27 19:31:45 eyefimov noship $ */
3
4 dummy NUMBER;
5 org_id NUMBER(15);
6 X_user NUMBER(15);
7 X_module VARCHAR2(30);
8 copy_mode VARCHAR2(1);
9 outcome VARCHAR2(30);
10 X_exp_class_code VARCHAR2(2);
11
12 INVALID_EXPENDITURE EXCEPTION;
13 INVALID_ITEM EXCEPTION;
14 /*
15 * Bug# 728286
16 * New exception defined to check whether the new Exp group (in reverseexpgroup proc.)
17 * already exists in the system or not.
18 */
19 INVALID_EXP_GROUP EXCEPTION;
20
21
22 function check_reverse_allowed ( net_zero_flag varchar2,
23 related_item number,
24 transferred_item number ) return BOOLEAN ;
25
26 PROCEDURE ValidateEmp ( X_person_id IN NUMBER
27 , X_date IN DATE
28 , X_status OUT NOCOPY VARCHAR2 )
29
30 IS
31
32 BEGIN
33
34 X_status := NULL;
35 org_id := NULL;
36 dummy := NULL;
37
38 org_id := pa_utils.GetEmpOrgId ( X_person_id, X_date );
39
40 IF ( org_id IS NULL ) THEN
41 X_status := 'PA_EX_NO_ORG_ASSGN';
42 RETURN;
43 END IF;
44
45 dummy := NULL;
46 dummy := pa_utils.GetEmpJobId ( X_person_id, X_date );
47
48 IF ( dummy IS NULL ) THEN
49 X_status := 'PA_EX_NO_ASSGN';
50 RETURN;
51 END IF;
52
53 END ValidateEmp;
54
55 PROCEDURE CopyItems ( X_orig_exp_id IN NUMBER
56 , X_new_exp_id IN NUMBER
57 , X_date IN DATE
58 , X_person_id IN NUMBER
59 , P_Inc_by_Org_Id IN NUMBER ) /* Added parameter for bug 2683803 */
60
61 IS
62
63 temp_outcome VARCHAR2(30) DEFAULT NULL;
64 temp_outcome_type VARCHAR2(1) DEFAULT 'E';
65 temp_msg_application VARCHAR2(50) DEFAULT 'PA';
66 temp_msg_token1 VARCHAR2(240) DEFAULT NULL;
67 temp_msg_token2 VARCHAR2(240) DEFAULT NULL;
68 temp_msg_token3 VARCHAR2(240) DEFAULT NULL;
69 temp_msg_count NUMBER DEFAULT 1;
70 temp_status NUMBER DEFAULT NULL;
71 i BINARY_INTEGER DEFAULT 0;
72
73 -- Starts Added for bug#4618898
74 temp_acct_rate_type pa_expenditure_items_all.acct_rate_type%TYPE;
75 temp_acct_rate_date pa_expenditure_items_all.acct_rate_date%TYPE;
76 temp_acct_exchange_rate pa_expenditure_items_all.acct_exchange_rate%TYPE;
77 temp_projfunc_rate_type pa_expenditure_items_all.projfunc_cost_rate_type%TYPE;
78 temp_projfunc_rate_date pa_expenditure_items_all.projfunc_cost_rate_date%TYPE;
79 temp_projfunc_exchange_rate pa_expenditure_items_all.projfunc_cost_exchange_rate%TYPE;
80 temp_project_rate_type pa_expenditure_items_all.project_rate_type%TYPE;
81 temp_project_rate_date pa_expenditure_items_all.project_rate_date%TYPE;
82 temp_project_exchange_rate pa_expenditure_items_all.project_exchange_rate%TYPE;
83
84 l_status VARCHAR2(30);
85 l_dummy1 NUMBER;
86 l_dummy2 NUMBER;
87 l_dummy3 NUMBER;
88 -- Ends Added for bug#4618898
89
90 l_labor_cost_multiplier_name pa_expenditure_items_all.labor_cost_multiplier_name%TYPE;
91
92 l_denom_burdened_cost pa_expenditure_items_all.denom_burdened_cost%TYPE; /* bug#2794006 */
93
94 CURSOR getEI IS
95 SELECT
96 pa_expenditure_items_s.nextval expenditure_item_id
97 , X_new_exp_id expenditure_id
98 , i.task_id
99 , to_number( NULL ) raw_cost
100 , to_number( NULL ) raw_cost_rate
101 , decode( copy_mode, 'O',
102 next_day((X_date-7), /* bug 3693848 : Removed to_date function because X_date is already a date parameter */
103 to_char(i.expenditure_item_date, 'DAY')),
104 X_date ) expenditure_item_date
105 , i.organization_id /*added for bug-2478552*/
106 , i.non_labor_resource /*added for bug-2478552*/
107 , i.expenditure_type
108 , i.system_linkage_function
109 , decode( copy_mode, 'S', NULL, i.quantity ) quantity
110 , t.project_id
111 , t.billable_flag
112 , i.attribute_category
113 , i.attribute1
114 , i.attribute2
115 , i.attribute3
116 , i.attribute4
117 , i.attribute5
118 , i.attribute6
119 , i.attribute7
120 , i.attribute8
121 , i.attribute9
122 , i.attribute10
123 , X_person_id person_id
124 , job_id
125 , i.org_id
126 , i.labor_cost_multiplier_name
127 , i.receipt_currency_amount
128 , i.receipt_currency_code
129 , i.receipt_exchange_rate
130 , i.denom_currency_code
131 , i.denom_raw_cost
132 , i.denom_burdened_cost
133 , i.acct_currency_code
134 , i.acct_rate_date
135 , i.acct_rate_type
136 , i.acct_exchange_rate
137 , i.acct_raw_cost
138 , i.acct_burdened_cost
139 , i.acct_exchange_rounding_limit
140 , i.project_currency_code
141 , i.project_rate_type
142 , i.project_rate_date
143 , i.project_exchange_rate
144 , i.work_type_id
145 , i.assignment_id
146 , i.projfunc_currency_code
147 , i.projfunc_cost_rate_type
148 , i.projfunc_cost_rate_date
149 , i.projfunc_cost_exchange_rate
150 , com.expenditure_comment --bug 3066137
151 FROM
152 pa_tasks t
153 , pa_expenditure_items i
154 , pa_expenditure_comments com --bug 3066137
155 WHERE
156 ( X_exp_class_code = 'OE'
157 /** OR i.system_linkage_function = 'ST' ) Bug fix : 2329146 **/
158 OR i.system_linkage_function IN ('ST','OT','ER','USG','PJ','INV','WIP','BTC') )
159 /* bug#2794006 added 'PJ','INV','WIP','BTC' */
160 /* Found during unit testing for bug 2683803. Was not picking up eis whose slf is OT */
161 AND i.task_id = t.task_id
162 AND i.expenditure_item_id = com.expenditure_item_id(+) --bug 3066137
163 AND i.expenditure_id = X_orig_exp_id
164 AND i.adjusted_expenditure_item_id IS NULL
165 AND nvl(i.net_zero_adjustment_flag, 'N' ) <> 'Y'
166 AND i.source_expenditure_item_id IS NULL;
167
168 PROCEDURE CheckOutcome( X_outcome IN OUT NOCOPY VARCHAR2,
169 X_outcome_type IN VARCHAR2 DEFAULT 'E' )
170
171 IS
172
173 BEGIN
174
175 IF ( X_outcome IS NULL ) THEN
176 RETURN;
177 ELSE
178 IF ( X_outcome_type = 'W' ) THEN
179 RETURN;
180 ELSE
181 outcome := X_outcome;
182 pa_transactions.FlushEiTabs;
183
184 IF ( copy_mode = 'M' ) THEN
185 RAISE INVALID_EXPENDITURE;
186 ELSE
187 RAISE INVALID_ITEM;
188 END IF;
189 END IF;
190 END IF;
191
192 END CheckOutcome;
193
194 Function GetTpAmtTypeCode (P_Work_Type_Id IN NUMBER) Return Varchar2
195
196 Is
197
198 l_Tp_Amt_Type_Code Pa_Expenditure_Items_All.Tp_Amt_Type_Code%TYPE;
199
200 Begin
201 IF P_Work_Type_Id is NOT NULL THEN /* IF Added for Bug2220691*/
202 Select Tp_Amt_Type_Code
203 into l_Tp_Amt_Type_Code
204 from Pa_Work_Types_Vl
205 where Work_Type_Id = P_Work_Type_Id;
206 ELSE
207 l_Tp_Amt_Type_Code:=NULL; /* Bug 2220691 */
208 END IF;
209 Return l_Tp_Amt_Type_Code;
210
211 End GetTpAmtTypeCode;
212
213 BEGIN
214
215 FOR EI IN getEI LOOP
216
217 i := i + 1;
218
219 /* IF ( X_exp_class_code <> 'PT' ) THEN commented for bug#2527749 */
220 IF EI.person_id IS NOT NULL THEN /* added for bug#2527749 */
221
222 ValidateEmp ( EI.person_id
223 , EI.expenditure_item_date
224 , temp_outcome );
225 CheckOutcome ( temp_outcome );
226
227 END IF;
228
229 IF ( NOT pa_utils.CheckExpTypeActive( EI.expenditure_type
230 , EI.expenditure_item_date ) ) THEN
231 temp_outcome := 'PA_TR_EXP_TYPE_INACTIVE';
232 CheckOutcome( temp_outcome );
233
234 END IF;
235
236 IF ( X_exp_class_code = 'OE' ) THEN
237
238 EI.raw_cost_rate := pa_utils.GetExpTypeCostRate( EI.expenditure_type
239 , EI.expenditure_item_date );
240 EI.raw_cost := PA_CURRENCY.ROUND_CURRENCY_AMT( ( EI.quantity * EI.raw_cost_rate ) );
241
242 END IF;
243
244 pa_transactions_pub.validate_transaction(
245 X_project_id => EI.project_id
246 , X_task_id => EI.task_id
247 , X_ei_date => EI.expenditure_item_date
248 , X_expenditure_type => EI.expenditure_type
249 , X_non_labor_resource => EI.non_labor_resource /* bug-2478552*/
250 , X_person_id => X_person_id
251 , X_quantity => EI.quantity
252 , X_denom_currency_code => EI.denom_currency_code
253 , X_acct_currency_code => EI.acct_currency_code
254 , X_denom_raw_cost => EI.denom_raw_cost
255 , X_acct_raw_cost => EI.acct_raw_cost
256 , X_acct_rate_type => EI.acct_rate_type
257 , X_acct_rate_date => EI.acct_rate_date
258 , X_acct_exchange_rate => EI.acct_exchange_rate
259 , X_transfer_ei => NULL
260 , X_incurred_by_org_id => P_Inc_by_Org_Id
261 , X_nl_resource_org_id => EI.organization_id /*bug-2478552*/
262 , X_transaction_source => NULL
263 , X_calling_module => X_module
264 , X_vendor_id => NULL
265 , X_entered_by_user_id => X_user
266 , X_attribute_category => EI.attribute_category
267 , X_attribute1 => EI.attribute1
268 , X_attribute2 => EI.attribute2
269 , X_attribute3 => EI.attribute3
270 , X_attribute4 => EI.attribute4
271 , X_attribute5 => EI.attribute5
272 , X_attribute6 => EI.attribute6
273 , X_attribute7 => EI.attribute7
274 , X_attribute8 => EI.attribute8
275 , X_attribute9 => EI.attribute9
276 , X_attribute10 => EI.attribute10
277 , X_attribute11 => NULL
278 , X_attribute12 => NULL
279 , X_attribute13 => NULL
280 , X_attribute14 => NULL
281 , X_attribute15 => NULL
282 , X_msg_application => temp_msg_application
283 , X_msg_type => temp_outcome_type
284 , X_msg_token1 => temp_msg_token1
285 , X_msg_token2 => temp_msg_token2
286 , X_msg_token3 => temp_msg_token3
287 , X_msg_count => temp_msg_count
288 , X_msg_data => temp_outcome
289 , X_billable_flag => EI.billable_flag
290 , P_ProjFunc_Currency_Code => EI.ProjFunc_Currency_Code
291 , P_ProjFunc_Cost_Rate_Type => EI.ProjFunc_Cost_Rate_Type
292 , P_ProjFunc_Cost_Rate_Date => EI.ProjFunc_Cost_Rate_Date
293 , P_ProjFunc_Cost_Exchg_Rate => EI.ProjFunc_Cost_Exchange_Rate
294 , P_Assignment_Id => EI.Assignment_Id
295 , P_Work_Type_Id => EI.Work_Type_Id
296 , P_sys_link_function => EI.system_linkage_function );
297
298 CheckOutcome( temp_outcome ,temp_outcome_type);
299 /* Start of Bug 2648550 */
300 EI.Assignment_Id := PATC.G_OVERIDE_ASSIGNMENT_ID;
301 EI.Work_Type_Id := PATC.G_OVERIDE_WORK_TYPE_ID;
302 /* End of Bug 2648550 */
303
304 /* NO IC Changes, Copy just creates new EI from existing EI's
305 So they should be treated as New txns. For any new EI created
306 through form we do not derive the ic attributes. Following
307 the same approach here, loadei will insert the defaults for
308 all IC columns.
309 */
310
311 -- Begin bug 2678790
312 l_labor_cost_multiplier_name := Check_lcm(P_lcm_name => ei.labor_cost_multiplier_name,
313 P_ei_Date => EI.expenditure_item_date);
314 -- End bug 2678790
318 from dual; /* bug#2794006 */
315
316 select decode(EI.system_linkage_function,'BTC',EI.denom_burdened_cost,NULL)
317 into l_denom_burdened_cost
319
320 --BLOCK ADDED FOR CALCULATING THE EXCHANGE RATES FOR THE NEW ITEM for bug #4618898
321
322 -- Copy the rate types from the source EI.
323
324 temp_acct_rate_type := EI.acct_rate_type;
325 temp_projfunc_rate_type := EI.projfunc_cost_rate_type;
326 temp_project_rate_type := EI.project_rate_type;
327
328 -- New Item rate date will be the expenditure_item_date
329 IF ( EI.acct_rate_date is NOT NULL) THEN
330 temp_acct_rate_date := EI.expenditure_item_date;
331 END IF;
332
333 IF ( EI.projfunc_cost_rate_date is NOT NULL) THEN
334 temp_projfunc_rate_date := EI.expenditure_item_date;
335 END IF;
336
337 IF ( EI.project_rate_date is NOT NULL) THEN
338 temp_project_rate_date := EI.expenditure_item_date;
339 END IF;
340 -- If the rate type is User then copy the exchange rates from the source EI.
341 IF (EI.acct_rate_type = 'User') THEN
342 temp_acct_exchange_rate := EI.acct_exchange_rate;
343 ELSE
344 temp_acct_exchange_rate := NULL;
345 END IF;
346
347 IF (EI.project_rate_type = 'User') THEN
348 temp_project_exchange_rate := EI.project_exchange_rate;
349 ELSE
350 temp_project_exchange_rate := NULL;
351 END IF;
352
353 IF (EI.projfunc_cost_rate_type = 'User') THEN
354 temp_projfunc_exchange_rate := EI.projfunc_cost_exchange_rate;
355 ELSE
356 temp_projfunc_exchange_rate := NULL;
357 END IF;
358
359 -- calling API's to calculate the acct and projfunc cost exchange rates
360
361 If temp_acct_rate_type is not null and
362 temp_acct_rate_type <> 'User' and
363 temp_acct_rate_date is not null and
364 EI.denom_currency_code is not null Then
365
366 pa_multi_currency.convert_amount(
367 EI.denom_currency_code,
368 EI.acct_currency_code,
369 temp_acct_rate_date,
370 temp_acct_rate_type,
371 null,
372 'N',
373 'Y',
374 l_dummy1,
375 l_dummy2,
376 l_dummy3,
377 temp_acct_exchange_rate,
378 l_status);
379
380 End If;
381
382 If temp_projfunc_rate_type is not null and
383 temp_projfunc_rate_type <> 'User' and
384 temp_projfunc_rate_date is not null Then
385
386 pa_multi_currency.convert_amount(
387 nvl(EI.denom_currency_code,EI.acct_currency_code),
388 EI.projfunc_currency_code,
389 temp_projfunc_rate_date,
390 temp_projfunc_rate_type,
391 null,
392 'N',
393 'Y',
394 l_dummy1,
395 l_dummy2,
396 l_dummy3,
397 temp_projfunc_exchange_rate,
398 l_status);
399
400 End If; -- projfunc check
401
402 -- Proj curr changes
403
404 If temp_project_rate_type is not null and
405 temp_project_rate_type <> 'User' and
406 temp_project_rate_date is not null Then
407
408 pa_multi_currency.convert_amount(
409 nvl(EI.denom_currency_code,EI.acct_currency_code),
410 EI.project_currency_code,
411 temp_project_rate_date,
412 temp_project_rate_type,
413 null,
414 'N',
415 'Y',
416 l_dummy1,
417 l_dummy2,
418 l_dummy3,
419 temp_project_exchange_rate,
420 l_status);
424
421
422 End If;
423 --Block ends for bug #4618898
425 pa_transactions.LoadEi(
426 X_expenditure_item_id => EI.expenditure_item_id
427 ,X_expenditure_id => EI.expenditure_id
428 ,X_expenditure_item_date => EI.expenditure_item_date
429 ,X_project_id => EI.project_id --bugfix:2201207 NULL
430 ,X_task_id => EI.task_id
431 ,X_expenditure_type => EI.expenditure_type
432 ,X_non_labor_resource => EI.non_labor_resource /* bug-2478552*/
433 ,X_nl_resource_org_id => EI.organization_id /* bug-2478552*/
434 ,X_quantity => EI.quantity
435 ,X_raw_cost => NULL
436 ,X_raw_cost_rate => NULL
437 ,X_override_to_org_id => NULL
438 ,X_billable_flag => EI.billable_flag
439 ,X_bill_hold_flag => 'N'
440 ,X_orig_transaction_ref => NULL
441 ,X_transferred_from_ei => NULL
442 ,X_adj_expend_item_id => NULL
443 ,X_attribute_category => EI.attribute_category
444 ,X_attribute1 => EI.attribute1
445 ,X_attribute2 => EI.attribute2
446 ,X_attribute3 => EI.attribute3
447 ,X_attribute4 => EI.attribute4
448 ,X_attribute5 => EI.attribute5
449 ,X_attribute6 => EI.attribute6
450 ,X_attribute7 => EI.attribute7
451 ,X_attribute8 => EI.attribute8
452 ,X_attribute9 => EI.attribute9
453 ,X_attribute10 => EI.attribute10
454 ,X_ei_comment => EI.expenditure_comment /*Bug 3066137 */ ,X_transaction_source => NULL
455 ,X_source_exp_item_id => NULL
456 ,i => i
457 ,X_job_id => EI.job_id
458 ,X_org_id => EI.org_id
459 ,X_labor_cost_multiplier_name => l_labor_cost_multiplier_name
460 ,X_drccid => NULL
461 ,X_crccid => NULL
462 ,X_cdlsr1 => NULL
463 ,X_cdlsr2 => NULL
464 ,X_cdlsr3 => NULL
465 ,X_gldate => NULL
466 ,X_bcost => NULL
467 ,X_bcostrate => NULL
468 ,X_etypeclass => EI.system_linkage_function
469 ,X_burden_sum_dest_run_id => NULL
470 ,X_burden_compile_set_id => NULL
471 ,X_receipt_currency_amount => NULL
472 ,X_receipt_currency_code => EI.receipt_currency_code
473 ,X_receipt_exchange_rate => EI.receipt_exchange_rate
474 ,X_denom_currency_code => EI.denom_currency_code
475 ,X_denom_raw_cost => NULL
476 ,X_denom_burdened_cost => l_denom_burdened_cost /* bug#2794006 */
477 ,X_acct_currency_code => EI.acct_currency_code
478 ,X_acct_rate_date => EI.acct_rate_date
479 ,X_acct_rate_type => EI.acct_rate_type
480 ,X_acct_exchange_rate => EI.acct_exchange_rate
481 ,X_acct_raw_cost => NULL
482 ,X_acct_burdened_cost => NULL
483 ,X_acct_exchange_rounding_limit => EI.acct_exchange_rounding_limit
484 ,X_project_currency_code => EI.project_currency_code
485 ,X_project_rate_date => EI.project_rate_date
486 ,X_project_rate_type => EI.project_rate_type
487 ,X_project_exchange_rate => EI.project_exchange_rate
488 ,P_Assignment_Id => EI.Assignment_Id
489 ,P_Work_type_Id => EI.Work_Type_Id
490 ,P_Projfunc_Currency_Code => EI.ProjFunc_Currency_Code
491 ,P_Projfunc_Cost_Rate_Date => EI.ProjFunc_Cost_Rate_Date
492 ,P_Projfunc_Cost_Rate_Type => EI.ProjFunc_Cost_Rate_Type
493 ,P_Projfunc_Cost_Exchange_Rate => EI.ProjFunc_Cost_Exchange_Rate
494 ,P_Tp_Amt_Type_Code => GetTpAmtTypeCode(EI.Work_Type_Id));
495
496 END LOOP;
497
498 pa_transactions.InsItems(
499 X_user => X_user
500 , X_login => NULL
501 , X_module => X_module
502 , X_calling_process => 'EXPEND_COPY'
503 , Rows => i
504 , X_status => temp_status
508
505 , X_gl_flag => NULL );
506
507 pa_adjustments.CheckStatus( status_indicator => temp_status );
509 END CopyItems;
510
511 PROCEDURE preapproved (
512 copy_option IN VARCHAR2
513 , copy_items IN VARCHAR2
514 , orig_exp_group IN VARCHAR2
515 , new_exp_group IN VARCHAR2
516 , orig_exp_id IN NUMBER
517 , exp_ending_date IN DATE
518 , new_inc_by_person IN NUMBER
519 , userid IN NUMBER
520 , procedure_num_copied IN OUT NOCOPY NUMBER
521 , procedure_num_rejected IN OUT NOCOPY NUMBER
522 , procedure_return_code IN OUT NOCOPY VARCHAR2
523 /** start of Bug fix 2329146 **/
524 , p_sys_link_function IN VARCHAR2 default null
525 , p_exp_type_class_code IN VARCHAR2 default 'PT'
526 /** end of bug fix **/
527 , P_Update_Emp_Orgs IN VARCHAR2 default null)
528
529 IS
530
531 num_copied NUMBER := 0;
532 num_rejected NUMBER := 0;
533 new_exp_id NUMBER;
534 old_org_id NUMBER;
535 l_Inc_By_Org_Id NUMBER; /* Added local variable for bug 2683803 */
536
537 l_org_id NUMBER := pa_moac_utils.get_current_org_id;
538
539 /* Bug 954856 Added ORDER BY expenditure_id to cursor getEXP */
540 /* Bug 1118913 Removed pa_expenditures_s.nextval new_exp_id from
541 cursor. Whereever EXP.new_exp_id change to
542 new_exp_id */
543
544 CURSOR getEXP IS
545 SELECT
546 expenditure_id orig_exp_id
547 , description
548 , incurred_by_organization_id /* Included for Bug#2366542 */
549 , nvl( new_inc_by_person, incurred_by_person_id ) person_id
550 , decode( copy_mode, 'S', NULL,
551 decode( copy_items, 'Y', control_total_amount, NULL ))
552 control_total_amount
553 , attribute_category
554 , attribute1
555 , attribute2
556 , attribute3
557 , attribute4
558 , attribute5
559 , attribute6
560 , attribute7
561 , attribute8
562 , attribute9
563 , attribute10
564 , denom_currency_code
565 , acct_currency_code
566 , acct_rate_type
567 , acct_rate_date
568 , acct_exchange_rate
569 , person_type -- fix for bug : 3645842
570 FROM
571 pa_expenditures
572 WHERE
573 expenditure_group = orig_exp_group
574 AND expenditure_id = nvl( orig_exp_id, expenditure_id )
575 ORDER BY expenditure_id;
576
577 EXP getEXP%ROWTYPE;
578
579 -- Starts Added for bug#4618898
580 temp_acct_rate_type pa_expenditures.acct_rate_type%TYPE;
581 temp_acct_rate_date pa_expenditures.acct_rate_date%TYPE;
582 temp_acct_exchange_rate pa_expenditures.acct_exchange_rate%TYPE;
583 l_status VARCHAR2(30);
584 l_dummy1 NUMBER;
585 l_dummy2 NUMBER;
586 l_dummy3 NUMBER;
587 -- Ends Added for bug#4618898
588
589 BEGIN
590
591 copy_mode := copy_option;
592 X_user := userid;
593 /** commented for bug fix : 2329146 X_exp_class_code := 'PT'; **/
594 IF nvl(p_exp_type_class_code,'X') <> 'X' Then
595 X_exp_class_code := p_exp_type_class_code;
596 Else
597 X_exp_class_code := 'PT';
598 End if;
599
600 X_module := 'PAXEXCOP/PAXTEXCB';
601
602 IF ( orig_exp_group = new_exp_group ) THEN
603 outcome := 'PA_EX_SAME_EX';
604 RAISE INVALID_ITEM;
605 END IF;
606
607 OPEN getEXP;
608
609 LOOP
610
611 FETCH getEXP INTO EXP;
612
613 IF ( getEXP%ROWCOUNT = 0 ) THEN
614 outcome := 'PA_EX_NO_EX';
615 RAISE INVALID_ITEM;
616 END IF;
617
618 /* Enhancement bug 2683803
619 Remming out the assigning of a value to the org_id and doing it conditionally
620 further down in the process. To provide the customer option to choose
621 what they want to populate in the new expenditure being created
622 incurred by organization id field. */
623 -- org_id := EXP.incurred_by_organization_id; /* Included Bug#2366542 */
624
625 EXIT WHEN getEXP%NOTFOUND;
626
627 --
628 -- Bug # 1118913
629 --
630 -- Earlier the new expenditure id was generated
631 -- in the select statement. But there was problem
632 -- using the sequence generator in the SELECT statement
633
634 SELECT pa_expenditures_s.nextval
635 INTO new_exp_id
636 FROM DUAL ;
637
638 --
639 -- End bug 1118913
640 --
641
645 If Nvl(P_Update_Emp_Orgs,'Y') = 'Y' Then
642 BEGIN
643
644 -- Begin of enhancement bug 2683803
646
647 ValidateEmp ( EXP.person_id
648 , exp_ending_date
649 , outcome );
650
651 l_Inc_By_Org_Id := Org_Id;
652
653 If ( outcome IS NOT NULL ) Then
654
655 If ( copy_mode = 'M' ) Then
656 Raise INVALID_EXPENDITURE;
657 Else
658 Raise INVALID_ITEM;
659 End If;
660
661 End If;
662
663 Else
664
665 l_Inc_by_Org_Id := Exp.Incurred_By_Organization_Id;
666
667 End If;
668 -- End of enhancement bug 2683803
669
670 IF ( copy_items = 'Y' ) THEN
671
672 CopyItems ( EXP.orig_exp_id
673 , new_exp_id
674 , exp_ending_date
675 , EXP.person_id
676 , l_Inc_By_Org_Id ); /* Added parameter for bug 2683803 */
677
678 END IF;
679
680 --BLOCK ADDED FOR CALCULATING THE EXCHANGE RATES FOR THE NEW Expenditure for bug #4618898
681 -- Copy the rate types from the source EXP.
682 temp_acct_rate_type := EXP.acct_rate_type;
683
684 -- New Item rate date will be the expenditure_ending_date
685 IF ( EXP.acct_rate_date is NOT NULL) THEN
686 temp_acct_rate_date := exp_ending_date;
687 END IF;
688
689 -- If the rate type is User then copy the exchange rates from the source EXP.
690 IF (EXP.acct_rate_type = 'User') THEN
691 temp_acct_exchange_rate := EXP.acct_exchange_rate;
692 ELSE
693 temp_acct_exchange_rate := NULL;
694 END IF;
695
696 -- calling API's to calculate the acct and projfunc cost exchange rates
697
698 If temp_acct_rate_type is not null and
699 temp_acct_rate_type <> 'User' and
700 temp_acct_rate_date is not null and
701 EXP.denom_currency_code is not null Then
702
703 pa_multi_currency.convert_amount(
704 EXP.denom_currency_code,
705 EXP.acct_currency_code,
706 temp_acct_rate_date,
710 'Y',
707 temp_acct_rate_type,
708 null,
709 'N',
711 l_dummy1,
712 l_dummy2,
713 l_dummy3,
714 temp_acct_exchange_rate,
715 l_status);
716
717 End If;
718 --Block ends for bug #4618898
719
720 pa_transactions.InsertExp(
721 X_expenditure_id => new_exp_id,
722 X_expend_status => 'WORKING',
723 X_expend_ending => exp_ending_date,
724 /** X_expend_class => 'PT', bug fix : 2329146 **/
725 X_expend_class => X_exp_class_code,
726 X_inc_by_person => EXP.person_id,
727 /* X_inc_by_org => org_id, remmed out for bug 2683803 */
728 /* Used a local variable to hold the inc by org id instead
729 old the global variable because it get changed when validateemp()
730 gets called. Part of bug 2683803 enhancement unit testing */
731 X_inc_by_org => l_Inc_By_Org_id,
732 X_expend_group => new_exp_group,
733 X_entered_by_id => X_user,
734 X_created_by_id => X_user,
735 X_attribute_category => EXP.attribute_category,
736 X_attribute1 => EXP.attribute1,
737 X_attribute2 => EXP.attribute2,
738 X_attribute3 => EXP.attribute3,
739 X_attribute4 => EXP.attribute4,
740 X_attribute5 => EXP.attribute5,
741 X_attribute6 => EXP.attribute6,
742 X_attribute7 => EXP.attribute7,
743 X_attribute8 => EXP.attribute8,
744 X_attribute9 => EXP.attribute9,
745 X_attribute10 => EXP.attribute10,
746 X_description => EXP.description,
747 X_control_total => EXP.control_total_amount,
748 X_denom_currency_code => EXP.denom_currency_code,
749 X_acct_currency_code => EXP.acct_currency_code,
750 X_acct_rate_type => EXP.acct_rate_type,
751 X_acct_rate_date => EXP.acct_rate_date,
752 X_acct_exchange_rate => EXP.acct_exchange_rate,
753 X_person_type => EXP.person_type,
754 P_Org_Id => l_Org_Id); -- 12i MOAC changes
755
756 num_copied := num_copied + 1;
757
758 -- Copies the attachments for the original expenditure
759 -- to the newly created expenditure
760
761 fnd_attached_documents2_pkg.copy_attachments(
762 'PA_EXPENDITURES',
763 EXP.orig_exp_id,
764 null,
765 null,
766 null,
767 null,
768 'PA_EXPENDITURES',
769 new_exp_id,
770 null,
771 null,
772 null,
773 null,
774 FND_GLOBAL.USER_ID,
775 FND_GLOBAL.LOGIN_ID,
776 null,
777 null,
778 null);
779
780 EXCEPTION
781 WHEN INVALID_EXPENDITURE THEN
782 num_rejected := num_rejected + 1;
783 WHEN INVALID_ITEM THEN
784 num_rejected := num_rejected + 1;
785 WHEN OTHERS THEN
786 RAISE;
787
788 END;
789
790 END LOOP;
791
792 CLOSE getEXP;
793
794 procedure_return_code := 'PA_EX_COPY_OUTCOME';
795 procedure_num_copied := num_copied;
796 procedure_num_rejected := num_rejected;
797
798 EXCEPTION
799 WHEN OTHERS THEN
800 RAISE;
801
802 END preapproved;
803
804 PROCEDURE online ( orig_exp_id IN NUMBER
805 , new_exp_id IN NUMBER
806 , exp_ending_date IN DATE
807 , X_inc_by_person IN NUMBER
808 , userid IN NUMBER
812 l_inc_by_org_id NUMBER; /* Added due to unit testing for enhancement bug 2683803 */
809 , procedure_return_code IN OUT NOCOPY VARCHAR2 )
810
811 IS
813
814 BEGIN
815
816 copy_mode := 'O';
817 X_user := userid;
818
819 SELECT
820 e.expenditure_class_code,
821 e.incurred_by_organization_id
822 INTO
823 X_exp_class_code,
824 l_inc_by_org_id
825 FROM
826 pa_expenditures e
827 WHERE
828 e.expenditure_id = orig_exp_id;
829
830 IF ( X_exp_class_code = 'OT' ) THEN
831 X_module := 'PAXEXEER/PAXTEXCB';
832 ELSIF ( X_exp_class_code = 'OE' ) THEN
833 X_module := 'PAXEXTCE/PAXTEXCB';
834 END IF;
835
836 CopyItems ( orig_exp_id
837 , new_exp_id
838 , exp_ending_date
839 , X_inc_by_person
840 , l_inc_by_org_id ); /* Added parameter due to unit testing for enhancement bug 2683803 */
841
842 EXCEPTION
843 WHEN INVALID_ITEM THEN
844 procedure_return_code := outcome;
845
846 END online;
847
848 /*
849 Bug#: 728286
850 New parameter added: X_expgrp_status (status of the exp group to be created)
851 All the program/function calls are changed to named parameter method.
852 */
853
854 PROCEDURE ReverseExpGroup( X_orig_exp_group IN VARCHAR2
855 , X_new_exp_group IN VARCHAR2
856 , X_user_id IN NUMBER
857 , X_module IN VARCHAR2
858 , X_num_reversed IN OUT NOCOPY NUMBER
859 , X_num_rejected IN OUT NOCOPY NUMBER
860 , X_return_code IN OUT NOCOPY VARCHAR2
861 , X_expgrp_status IN VARCHAR2 DEFAULT 'WORKING')
862 IS
863
864 InsertExp Boolean := TRUE ;
865 InsertBatch Boolean := FALSE ;
866 no_of_items Number := 0 ;
867 num_reversed Number := 0 ;
868 num_rejected Number := 0 ;
869 exp_status Varchar2(20);
870 l_OtlItem_Reversable Boolean := TRUE; -- OTL Change
871 l_BackOutExp_Id Number := 0; -- added for bug 5405636
872
873 CURSOR RevExp is
874 SELECT
875 e.expenditure_id orig_exp_id
876 , pa_expenditures_s.nextval new_exp_id
877 , e.expenditure_ending_date
878 , e.description
879 , e.incurred_by_person_id person_id
880 , e.incurred_by_organization_id inc_by_org_id
881 , e.expenditure_class_code
882 , e.control_total_amount
883 , e.attribute_category
884 , e.attribute1
885 , e.attribute2
886 , e.attribute3
887 , e.attribute4
888 , e.attribute5
889 , e.attribute6
890 , e.attribute7
891 , e.attribute8
892 , e.attribute9
893 , e.attribute10
894 , e.denom_currency_code
895 , e.acct_currency_code
896 , e.acct_rate_type
897 , e.acct_rate_date
898 , e.acct_exchange_rate
899 , e.person_type -- CWK change
900 FROM
901 pa_expenditures e
902 WHERE
903 e.expenditure_group = X_orig_exp_group ;
904
905 cursor RevExpItems(expend_id NUMBER ) is
906 select
907 ei.expenditure_item_id
908 , ei.net_zero_adjustment_flag
909 , ei.source_expenditure_item_id
910 , ei.transferred_from_exp_item_id
911 , ei.task_id
912 , ei.transaction_source -- OTC changes
913 , ei.orig_transaction_reference -- OTC changes
914 from
915 pa_expenditure_items_all ei
916 where
917 expenditure_id = expend_id ;
918
919 cursor ReverseGroup is
920 select
921 expenditure_group
922 , expenditure_ending_date
923 , system_linkage_function
924 , control_count
925 , control_total_amount
926 , request_id
927 , program_id
928 , program_application_id
929 , transaction_source
930 from
931 pa_expenditure_groups
932 where
933 expenditure_group = X_orig_exp_group ;
934
935 Exp RevExp%rowtype ;
936 ExpEi RevExpItems%rowtype ;
937 ExpGroup ReverseGroup%rowtype ;
938 outcome VARCHAR2(100);
939 Dummy NUMBER;
940 l_project_id pa_tasks.project_id%TYPE;
941 l_org_id NUMBER := pa_moac_utils.get_current_org_id;
942
943 BEGIN
944 /*
945 Bug#: 728286
946 Check: The new Exp Group already exists in the system or not.
950
947 Note: This check is not required when it's called from the Form PAXTREPE because
948 this validation is already done there.
949 */
951 IF X_module <> 'PAXTREPE' THEN
952 BEGIN
953 SELECT 1
954 INTO Dummy
955 FROM pa_expenditure_groups
956 WHERE expenditure_group = X_new_exp_group;
957
958 outcome := 'PA_TR_EPE_GROUP_NOT_UNIQ';
959 RAISE INVALID_EXP_GROUP;
960
961 EXCEPTION
962 WHEN NO_DATA_FOUND THEN
963 NULL;
964 END;
965 END IF;
966
967
968 OPEN RevExp ;
969
970 LOOP
971
972 FETCH RevExp into Exp ;
973
974 IF ( RevExp%ROWCOUNT = 0 ) THEN
975 outcome := 'PA_EX_NO_EX';
976 RAISE INVALID_ITEM;
977 END IF;
978
979 EXIT WHEN RevExp%NOTFOUND;
980
981 InsertExp := TRUE ;
982 no_of_items := 0 ;
983
984 OPEN RevExpItems(Exp.orig_exp_id) ;
985 LOOP
986 Fetch RevExpItems into ExpEi ;
987
988 If ( RevExpItems%ROWCOUNT = 0 ) THEN
989 InsertExp := FALSE ;
990 EXIT ;
991 END IF;
992
993 EXIT WHEN RevExpItems%NOTFOUND;
994
995 SELECT project_id
996 INTO l_project_id
997 FROM pa_tasks
998 WHERE task_id = ExpEi.task_id;
999
1000 /* OTL item is checked in OTL to see if change has been made in OTL. */
1001 If ExpEi.transaction_source = 'ORACLE TIME AND LABOR' and
1002 ExpEi.Orig_Transaction_Reference is not null Then
1003
1004 Pa_Otc_Api.AdjustAllowedToOTCItem(ExpEi.Orig_Transaction_Reference,
1005 l_OtlItem_Reversable);
1006 End If;
1007
1008 -- The OR clause and pa_project_utils.check_project_action_allowed
1009 -- condition is added to the code for checking whether for project
1010 -- belonging to expenditure items have status for which new transactions
1011 -- are allowed. If new transactions are not allowed for particular
1012 -- project, the corresponding expenditure item will be rejected and
1013 -- will not be reversed. The corresponding bug# is 1257100
1014
1015 /* Added OTL Boolean Flag to if condition. Don't want to allow
1016 * the reversal of an OTL item that has already been changed in OTL. */
1017 if ((Not Check_Reverse_Allowed(
1018 ExpEi.Net_Zero_Adjustment_Flag,
1019 ExpEi.Source_Expenditure_Item_Id,
1020 ExpEi.Transferred_From_Exp_Item_Id)) OR
1021 (Pa_Project_Utils.Check_Project_Action_Allowed(l_Project_Id,'NEW_TXNS') ='N') OR
1022 (Not l_OtlItem_Reversable) ) Then
1023
1024 num_rejected := num_rejected + 1 ;
1025
1026 else -- reversal is allowed
1027
1028 pa_adjustments.BackOutItem(
1029 X_exp_item_id => ExpEi.expenditure_item_id,
1030 X_expenditure_id => Exp.new_exp_id,
1031 X_adj_activity => 'REVERSE BATCH',
1032 X_module => 'PAXTREPE',
1033 X_user => x_user_id,
1034 X_login => x_user_id,
1035 X_status => outcome );
1036
1037 IF outcome <> 0 then
1038 num_rejected := num_rejected + 1 ;
1039 RAISE INVALID_ITEM ;
1040 END IF;
1041
1042 /* Code added for bug 5405636 : Start*/
1043
1044 SELECT expenditure_item_id
1045 INTO l_BackOutExp_Id
1046 FROM pa_expenditure_items_all
1047 WHERE adjusted_expenditure_item_id = ExpEi.expenditure_item_id;
1048
1049 pa_costing.CreateReverseCdl(
1050 X_exp_item_id => ExpEi.expenditure_item_id,
1051 X_backout_id => l_BackOutExp_Id,
1052 X_user => x_user_id,
1053 X_status => outcome );
1054
1055 /* Code added for bug 5405636 : End */
1056
1057 IF outcome <> 0 then
1058 num_rejected := num_rejected + 1 ;
1059 RAISE INVALID_ITEM ;
1060 END IF;
1061
1062 pa_adjustments.ReverseRelatedItems(
1063 X_source_exp_item_id => ExpEi.expenditure_item_id,
1064 X_expenditure_id => NULL,
1065 X_module => 'PAXTREPE',
1066 X_user => x_user_id,
1067 X_login => x_user_id,
1068 X_status => outcome );
1069
1070 IF outcome <> 0 then
1071 num_rejected := num_rejected + 1 ;
1072 RAISE INVALID_ITEM ;
1073 END IF;
1074 no_of_items := no_of_items + 1 ;
1075 num_reversed := num_reversed + 1 ;
1076
1077 end if; -- Is reversal allowed?
1078
1079 END LOOP ;
1080 CLOSE RevExpItems ;
1081
1082 If ( InsertExp ) and (no_of_items > 0) then
1083
1084 IF X_expgrp_status = 'WORKING' THEN
1085 exp_status := 'SUBMITTED';
1086 ELSE
1087 exp_status := 'APPROVED';
1088 END IF;
1089
1090 pa_transactions.InsertExp(
1091 X_expenditure_id => Exp.new_exp_id,
1095 X_inc_by_person => Exp.person_id ,
1092 X_expend_status => exp_status,
1093 X_expend_ending => Exp.expenditure_ending_date ,
1094 X_expend_class => Exp.expenditure_class_code ,
1096 X_inc_by_org => Exp.inc_by_org_id ,
1097 X_expend_group => X_new_exp_group ,
1098 X_entered_by_id => X_user_id ,
1099 X_created_by_id => X_user_id ,
1100 X_attribute_category => Exp.attribute_category ,
1101 X_attribute1 => Exp.attribute1 ,
1102 X_attribute2 => Exp.attribute2 ,
1103 X_attribute3 => Exp.attribute3 ,
1104 X_attribute4 => Exp.attribute4 ,
1105 X_attribute5 => Exp.attribute5 ,
1106 X_attribute6 => Exp.attribute6 ,
1107 X_attribute7 => Exp.attribute7 ,
1108 X_attribute8 => Exp.attribute8 ,
1109 X_attribute9 => Exp.attribute9 ,
1110 X_attribute10 => Exp.attribute10 ,
1111 X_description => Exp.description ,
1112 X_control_total => Exp.control_total_amount,
1113 X_denom_currency_code => Exp.denom_currency_code ,
1114 X_acct_currency_code => Exp.acct_currency_code ,
1115 X_acct_rate_type => Exp.acct_rate_type ,
1116 X_acct_rate_date => Exp.acct_rate_date ,
1117 X_acct_exchange_rate => Exp.acct_exchange_rate,
1118 X_person_type => Exp.person_type,
1119 P_Org_Id => l_Org_Id); -- CWK change
1120
1121 -- Copies the attachments for the original expenditure
1122 -- to the newly created expenditure
1123
1124 fnd_attached_documents2_pkg.copy_attachments(
1125 X_from_entity_name => 'PA_EXPENDITURES',
1126 X_from_pk1_value => Exp.orig_exp_id,
1127 X_from_pk2_value => null,
1128 X_from_pk3_value => null,
1129 X_from_pk4_value => null,
1130 X_from_pk5_value => null,
1131 X_to_entity_name => 'PA_EXPENDITURES',
1132 X_to_pk1_value => Exp.new_exp_id,
1133 X_to_pk2_value => null,
1134 X_to_pk3_value => null,
1135 X_to_pk4_value => null,
1136 X_to_pk5_value => null,
1137 X_created_by => FND_GLOBAL.USER_ID,
1138 X_last_update_login => FND_GLOBAL.LOGIN_ID,
1139 X_program_application_id => null,
1140 X_program_id => null,
1141 X_request_id => null);
1142
1143 InsertBatch := TRUE ;
1144
1145 End if ;
1146
1147 END LOOP ;
1148
1149 CLOSE RevExp ;
1150
1151 if ((InsertBatch ) AND (X_module <> 'PAXTREPE')) then
1152
1153 OPEN ReverseGroup ;
1154 FETCH ReverseGroup into ExpGroup ;
1155 if ReverseGroup%notfound then
1156 return ;
1157 end if;
1158
1159 /* Bug#: 728286
1160 The supplied exp_group name is used to create the new Expenditure Group.
1161 The status is set as supplied by the calling program (thru param x_expgrp_status)
1162 */
1163 pa_transactions.InsertExpGroup(
1164 X_expenditure_group => X_new_exp_group ,
1165 X_exp_group_status_code => X_expgrp_status ,
1166 X_ending_date => ExpGroup.expenditure_ending_date ,
1167 X_system_linkage => ExpGroup.system_linkage_function ,
1168 X_created_by => X_user_id ,
1169 X_transaction_source => ExpGroup.transaction_source,
1170 P_Org_Id => l_Org_Id); -- 12i MOAC changes
1171
1172 end if;
1173
1174 if num_reversed <= 0 then
1175 outcome := 'PA_NO_ITEMS_FOR_REVERSAL' ;
1176 null ;
1177 end if;
1178
1179 X_num_reversed := num_reversed ;
1180 X_num_rejected := num_rejected ;
1181 X_return_code := outcome ;
1182
1183 EXCEPTION
1184 WHEN INVALID_ITEM THEN
1185 X_return_code := outcome;
1186 /*
1187 Bug#: 728286
1188 Error handling
1189 */
1190 WHEN INVALID_EXP_GROUP THEN
1191 X_return_code := outcome;
1192 WHEN OTHERS THEN
1193 RAISE ;
1194
1195 End ReverseExpGroup ;
1196
1197 FUNCTION check_reverse_allowed ( net_zero_flag varchar2,
1198 related_item number,
1199 transferred_item number ) return BOOLEAN
1200 IS
1201
1202 BEGIN
1203
1204 if nvl(net_zero_flag, 'N') = 'Y' then
1205 return FALSE ;
1206 elsif related_item is not null then
1207 return FALSE ;
1208 elsif transferred_item is not null then
1209 return FALSE ;
1210 end if;
1211
1212 return TRUE ;
1213
1214 END check_reverse_allowed ;
1215
1216 Function Check_lcm(P_Lcm_Name IN Pa_Expenditure_Items_All.Labor_Cost_Multiplier_Name%TYPE,
1217 P_Ei_Date IN Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE) RETURN VARCHAR2
1218
1219 Is
1220
1221 l_lcm_Name Pa_Expenditure_Items_All.Labor_Cost_Multiplier_Name%TYPE := NULL;
1222
1223 Begin
1224
1225 If P_lcm_name is not null Then
1226
1227 select
1228 labor_cost_multiplier_name
1229 into
1230 l_lcm_Name
1231 from
1232 pa_labor_cost_multipliers
1233 where
1234 labor_cost_multiplier_name = P_Lcm_Name
1235 and P_Ei_Date Between Start_Date_Active
1236 And End_Date_Active;
1237
1238 End If;
1239
1240 Return ( l_lcm_Name );
1241
1242
1243 Exception
1244 When Others Then
1245 Return ( NULL );
1246
1247 End Check_lcm;
1248
1249
1250 END PA_EXP_COPY;