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