1 PACKAGE BODY PATC AS
2 /* $Header: PAXTTXCB.pls 120.43.12020000.6 2013/03/31 08:19:34 arbandyo ship $ */
3
4 temp_status VARCHAR2(30) := NULL;
5 temp_bill_flag VARCHAR2(1) := NULL;
6 level_flag VARCHAR2(1) := NULL ;
7 INVALID_DATA EXCEPTION;
8 G_TRX_SKIP_FLAG VARCHAR2(1) := NULL;
9
10 /* Start changes for 12714789 */
11 l_temp_task_bill_flag VARCHAR2(1) := NULL;
12 /* End changes for 12714789 */
13
14
15 PROCEDURE print_message (p_msg varchar2) IS
16
17 BEGIN
18
19 pa_cc_utils.log_message('Log: '||substr(p_msg,1,250));
20 --r_debug.r_msg('Log: '||p_msg);
21 NULL;
22
23 END print_message;
24
25
26 -- =====================================================================
27 -- This procedure is called only after the item being validated passes
28 -- all validation checks. It sets the return status parameter to NULL,
29 -- indicating the item is valid, and then determines the billability of
30 -- the item based on either the billable flag defined at any applicable
31 -- transaction controls or the task's billable flag.
32
33 PROCEDURE set_billable_flag ( txn_cntrl_bill_flag IN VARCHAR2
34 , task_bill_flag IN VARCHAR2 )
35 IS
36 BEGIN
37
38 temp_status := NULL;
39
40 IF ( txn_cntrl_bill_flag = 'N' ) THEN
41 temp_bill_flag := 'N';
42 ELSE
43 temp_bill_flag := task_bill_flag;
44 END IF;
45
46 END set_billable_flag;
47
48
49 -- This API prints the debug messages
50
51
52 -- =====================================================================
53 -- This procedure is the front-end API of the transaction controls stored
54 -- package. It accepts the following as input parameters:
55 -- - project id
56 -- - task id
57 -- - expenditure item date
58 -- - non-labor resource (only for usage items)
59 -- - incurred_by person id
60 -- - quantity (optional)
61 -- It returns the following after validation:
62 -- - billable flag
63 -- - status code
64 -- * NULL if item is valid
65 -- * if item is invalid, the status code will be one of the
66 -- following error messages:
67 -- PA_EX_QTY_EXIST - quantity must be entered
68 -- PA_EXP_INV_PJTK - invalid project/task
69 -- PA_EX_PROJECT_DATE - item date not within active project dates
70 -- PA_EX_PROJECT_CLOSED - project is closed
71 -- PA_EXP_TASK_EFF - item date not within active task dates
72 -- PA_EXP_TASK_STATUS - task is not chargeable
73 -- PA_EXP_PJ_TC - item violates project-level controls
74 -- PA_EXP_TASK_TC - item violates task-level controls
75 -- PA_NO_VALID_ASSIGN - item violates the PJRM controls
76
77 PROCEDURE get_status (
78 X_project_id IN NUMBER
79 , X_task_id IN NUMBER
80 , X_ei_date IN DATE
81 , X_expenditure_type IN VARCHAR2
82 , X_non_labor_resource IN VARCHAR2
83 , X_person_id IN NUMBER
84 , X_quantity IN NUMBER
85 , X_denom_currency_code IN VARCHAR2
86 , X_acct_currency_code IN VARCHAR2
87 , X_denom_raw_cost IN NUMBER
88 , X_acct_raw_cost IN NUMBER
89 , X_acct_rate_type IN VARCHAR2
90 , X_acct_rate_date IN DATE
91 , X_acct_exchange_rate IN NUMBER
92 , X_transfer_ei IN NUMBER
93 , X_incurred_by_org_id IN NUMBER
94 , X_nl_resource_org_id IN NUMBER
95 , X_transaction_source IN VARCHAR2
96 , X_calling_module IN VARCHAR2
97 , X_vendor_id IN NUMBER
98 , X_entered_by_user_id IN NUMBER
99 , X_attribute_category IN VARCHAR2
100 , X_attribute1 IN VARCHAR2
101 , X_attribute2 IN VARCHAR2
102 , X_attribute3 IN VARCHAR2
103 , X_attribute4 IN VARCHAR2
104 , X_attribute5 IN VARCHAR2
105 , X_attribute6 IN VARCHAR2
106 , X_attribute7 IN VARCHAR2
107 , X_attribute8 IN VARCHAR2
108 , X_attribute9 IN VARCHAR2
109 , X_attribute10 IN VARCHAR2
110 , X_attribute11 IN VARCHAR2
111 , X_attribute12 IN VARCHAR2
112 , X_attribute13 IN VARCHAR2
113 , X_attribute14 IN VARCHAR2
114 , X_attribute15 IN VARCHAR2
115 , X_msg_application IN OUT NOCOPY VARCHAR2
116 , X_msg_type OUT NOCOPY VARCHAR2
117 , X_msg_token1 OUT NOCOPY VARCHAR2
118 , X_msg_token2 OUT NOCOPY VARCHAR2
119 , X_msg_token3 OUT NOCOPY VARCHAR2
120 , X_msg_count OUT NOCOPY NUMBER
121 , X_status OUT NOCOPY VARCHAR2
122 , X_billable_flag OUT NOCOPY VARCHAR2
123 , p_projfunc_currency_code IN VARCHAR2
124 , p_projfunc_cost_rate_type IN VARCHAR2
125 , p_projfunc_cost_rate_date IN DATE
126 , p_projfunc_cost_exchg_rate IN NUMBER
127 , p_assignment_id IN NUMBER
128 , p_work_type_id IN NUMBER
129 , p_sys_link_function IN VARCHAR2
130 , P_Po_Header_Id IN NUMBER default null -- PA.M/CWK
131 , P_Po_Line_Id IN NUMBER default null -- PA.M/CWK
132 , P_Person_Type IN VARCHAR2 default null -- PA.M/CWK
133 , P_Po_Price_Type IN VARCHAR2 default null -- PA.M/CWK
134 , P_Document_Type IN VARCHAR2 default null -- Added these for R12
135 , P_Document_Line_Type IN VARCHAR2 default null
136 , P_Document_Dist_Type IN VARCHAR2 default null
137 -- 10253400: Payroll integration enhancement for 12.2..start
138 , P_Job_id IN NUMBER default null
139 , P_Location_id IN NUMBER default null
140 , P_Pay_Element_Type_Id IN NUMBER default null
141 , P_payroll_accrual_flag IN VARCHAR2 default null ---- Added for payroll billable_flag changes for 12.2 bug#12344689
142 -- 10253400: Payroll integration enhancement for 12.2..end
143 , P_pa_ref_num1 IN NUMBER default null
144 , P_pa_ref_num2 IN NUMBER default null
145 , P_pa_ref_num3 IN NUMBER default null
146 , P_pa_ref_num4 IN NUMBER default null
147 , P_pa_ref_num5 IN NUMBER default null
148 , P_pa_ref_num6 IN NUMBER default null
149 , P_pa_ref_num7 IN NUMBER default null
150 , P_pa_ref_num8 IN NUMBER default null
151 , P_pa_ref_num9 IN NUMBER default null
152 , P_pa_ref_num10 IN NUMBER default null
153 , P_pa_ref_var1 IN VARCHAR2 default null
154 , P_pa_ref_var2 IN VARCHAR2 default null
155 , P_pa_ref_var3 IN VARCHAR2 default null
156 , P_pa_ref_var4 IN VARCHAR2 default null
157 , P_pa_ref_var5 IN VARCHAR2 default null
158 , P_pa_ref_var6 IN VARCHAR2 default null
159 , P_pa_ref_var7 IN VARCHAR2 default null
160 , P_pa_ref_var8 IN VARCHAR2 default null
161 , P_pa_ref_var9 IN VARCHAR2 default null
162 , P_pa_ref_var10 IN VARCHAR2 default null)
163
164 IS
165
166
167 level_flag_local varchar2(1) := NULL; /* Added against bug 674526 */
168 ----p_person_id NUMBER ; /*2188422*/
169 /*l_sys_link_func VARCHAR2(100):= 'XX'; Bug# 2955795*/
170
171 p_msg_type VARCHAR2(1) ; /*2188422*/
172 Temp_allow_unscheduled_exp varchar2(1) := NULL;
173 Temp_assignment_id NUMBER ;
174 l_check_pjrm_tc_flag VARCHAR2(1) := 'N';
175 l_return_string VARCHAR2(1000) := 'X' ;
176 temp_msg_type VARCHAR2(1000);
177 temp_msg_token1 VARCHAR2(1000);
178 temp_msg_token2 VARCHAR2(1000);
179 temp_msg_token3 VARCHAR2(1000);
180 temp_msg_count NUMBER ;
181 temp_status VARCHAR2(1000);
182 l_return_status_code VARCHAR2(1);
183 l_err_msg_code VARCHAR2(80);
184
185 /* Start changes for bug#12597910 */
186
187 l_temp_bill_flag VARCHAR2(1) :=NULL;
188 l_bill_labor_accrual_flag VARCHAR2(1) :=NULL;
189
190 /* End changes for bug#12597910 */
191
192 --Bug 3017533
193 L_BeforeCE_AsgnId NUMBER;
194
195 /* added for bug#3088249 */
196 l_pa_date DATE;
197 l_prvdr_org_id NUMBER;
198 /* Added for bug 3681318 */
199 l_CURRENT_EMPLOYEE_FLAG varchar2(1);
200 l_CURRENT_NPW_FLAG VARCHAR2(1);
201 l_person_type VARCHAR2(10);
202 l_job_id NUMBER ; -- Added for bug 4044057
203 l_ac_termination_date per_periods_of_service.actual_termination_date%type; /*Basebug#4604614 (BaseBug#4118885) */
204 -- bug 10271889 start
205 l_final_process_date per_periods_of_service.final_process_date%TYPE;
206
207 l_task_count NUMBER := 0; -- Added as part of Bug 16220146
208 l_task_id NUMBER; -- Added as part of Bug 16220146
209
210 CURSOR cur_final_process_date (X_person_id NUMBER, X_date DATE) IS
211 SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
212 FROM per_periods_of_service
213 WHERE person_id = X_person_id
214 AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
215
216 -- bug 10271889 end
217
218 -- This cursor selects all applicable task-level transaction controls
219
220 CURSOR task_level_tc IS /*2188422*/ /*Bug# 2955795:Removed c_sys_link_func as it is not reqd*/
221 SELECT
222 tc.task_id
223 , tc.person_id
224 , tc.expenditure_category
225 , tc.expenditure_type
226 , tc.non_labor_resource
227 , tc.chargeable_flag
228 , tc.billable_indicator
229 , tc.SCHEDULED_EXP_ONLY
230 , tc.employees_only_flag -- PA.M/CWK changes
231 , tc.workplan_res_only_flag -- PA.M/Task Assignment changes
232 FROM
233 pa_transaction_controls tc
234 WHERE
235 tc.project_id = X_project_id
236 AND ( tc.task_id is null
237 OR
238 tc.task_id = l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
239 )
240 /******** Bug fix :2345895 Start donot modify refer to bug for details
241 AND ( tc.person_id IS NULL these lines are commented out for bug fix 2345895
242 or or c_person_id = -9999 ) --Added for bug# 2188422
243 Commented for bug# 2188422
244 OR
245 (X_person_id is NOT NULL and --Added for # 1652082
246 tc.person_id = X_person_id )
247 OR -- Added or clause for # 1652082
248 (X_vendor_id is NOT NULL and
249 X_person_id is NULL)
250 )
251 ***END of bug fix 2345895 ********/
252 /*AND ((p_sys_link_function = 'VI'Commented for bug 5735180*/ /*Bug# 2955795: Replaced c_sys_link_func by p_sys_link_function*/
253 /* AND (tc.expenditure_category is NOT NULL
254 OR tc.expenditure_type is NOT NULL
255 OR tc.task_id is NOT NULL
256 )
257 AND tc.person_id is null -- Added for bug 2942492
258 AND tc.non_labor_resource is null) Commented for Bug 5735180*/ /* Added for bug 2942492, added ( in start and )
259 for bug 2939224 */
260 /* OR Commented for bug5735180 */ /* Added for bug#2939224 */
261 /* Added VI following condition for bug 5735180*/
262 AND ( --Added for Bug 5735180
263 (p_sys_link_function in ('USG', 'PJ','VI') AND ((tc.person_id is NULL) OR ((X_person_id is NOT NULL) AND
264 (X_person_id = tc.person_id)))) -- Modified for bug 4585740
265 OR
266 ( nvl(p_sys_link_function,'-99') NOT IN ('VI', 'USG', 'PJ') /* Bug 5721949 */
267 /*Bug# 2955795: Replaced c_sys_link_func by p_sys_link_function*/
268 /* Bug 2939224-Added not in USG and PG and VI */
269 AND (tc.person_id is NULL OR
270 /**tc.person_id = tc.person_id Bug 2467454 **/
271 tc.person_id = x_person_id
272 )
273 )
274 )
275 /** Bug fix :2345895 End **/
276 AND ( tc.expenditure_category IS NULL
277 OR tc.expenditure_category =
278 ( SELECT expenditure_category
279 FROM pa_expenditure_types
280 WHERE expenditure_type = X_expenditure_type ) )
281 AND ( tc.expenditure_type IS NULL
282 OR tc.expenditure_type = X_expenditure_type )
283 AND ( tc.non_labor_resource IS NULL
284 OR tc.non_labor_resource = X_non_labor_resource )
285 AND X_ei_date BETWEEN tc.start_date_active
286 AND nvl( tc.end_date_active, X_ei_date )
287 GROUP BY
288 tc.task_id
289 , decode(p_sys_link_function,'VI',0, 'USG', 0, 'PJ', 0, tc.person_id) -- for VI group by task,exp_cat,exp_tpe /*2955795*/ /* Added USG and PJ here as no grouping on person_id is required for these sys links */
290 /*** , tc.person_id commented for bug fix :2345895 **/-- for <> VI group by task,person,exp_cat,exp_type
291 , tc.expenditure_category
292 , tc.expenditure_type
293 , tc.non_labor_resource
294 , tc.start_date_active
295 , tc.end_date_active
296 , tc.chargeable_flag
297 , tc.billable_indicator
298 , tc.SCHEDULED_EXP_ONLY
299 , tc.employees_only_flag -- PA.M/CWK changes
300 , tc.workplan_res_only_flag -- PA.M/Task Assignment changes
301 , tc.person_id
302 ORDER BY tc.task_id;
303
304 /* Commented for bug 2957441 ,decode(c_sys_link_func,'VI',nvl(tc.person_id,0),0); */
305
306 -- This cursor selects project/task information
307
308 CURSOR project_info IS
309 SELECT
310 p.project_status_code
311 , nvl( p.start_date, X_ei_date ) p_start_date
312 , nvl( p.completion_date, X_ei_date ) p_end_date
313 , nvl( p.limit_to_txn_controls_flag, 'N' ) p_limit_flag
314 , nvl( p.template_flag, 'N') p_template_flag
315 , nvl( t.chargeable_flag, 'N' ) t_chargeable_flag
316 , nvl( t.billable_flag, 'N' ) t_billable_flag
317 , nvl( t.start_date, X_ei_date ) t_start_date
318 , nvl( t.completion_date, X_ei_date ) t_end_date
319 , nvl( t.limit_to_txn_controls_flag, 'N' ) t_limit_flag
320 , t.retirement_cost_flag t_ret_cost_flag -- PA.L Retirement Cost Processing
321 , pt.project_type_class_code p_proj_typ_class_code -- PA.L Retirement Cost Processing
322 , nvl(p.assign_precedes_task, 'N') p_assign_precedes_task -- Bug 3017533
323 , nvl(p.bill_labor_accrual, 'N') bill_labor_accrual /* Start changes for bug#12597910 */
324 FROM
325 pa_tasks t
326 , pa_projects_all p
327 , pa_project_types_all pt -- PA.L Retirement Cost Processing
328 WHERE
329 t.task_id = l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
330 AND p.project_id = t.project_id
331 AND p.project_id = X_project_id
332 AND p.project_type = pt.project_type -- PA.L Retirement Cost Processing
333 AND p.org_id = pt.org_id ; -- For the Bug 5368274.Reverted the
334 -- fixes of bug 3989402.
335
336
337 tc task_level_tc%ROWTYPE;
338
339 proj project_info%ROWTYPE;
340
341 patcx_bill_flag VARCHAR2(1);
342
343 X_org_id NUMBER(15) DEFAULT NULL;
344 l_calling_module Varchar2(20); --Added for bug#13803367
345
346 -- =====================================================================
347 -- This procedure sets the return status parameter to a particular message
348 -- name and raises the INVALID_DATA exception which stops all processing.
349
350 PROCEDURE return_error ( msg_name IN VARCHAR2)
351 IS
352 BEGIN
353 -- Do not set status and billable flag to NULL if it is called
354 -- from PAVVIT. This done to reduce rejections during transfer.
355 -- For more Info Refer Bug# 290684 or Incident# 81149.
356
357 -- Bug 987539: If the calling module is transferring records from
358 -- AP, we do not report any error. PAAPIMP is the only module
359 -- which transfers records from AP
360 IF ( X_calling_Module <> 'PAAPIMP')
361 OR -- added this for bug fix :2345895
362 ( X_calling_Module = 'PAAPIMP' and Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' ) THEN
363 print_message('PATC ERROR:'||msg_name);
364 X_status := msg_name;
365 -- Begin bug 4518893
366 If ( nvl(X_msg_type,'W') = 'E' ) THEN
367 X_billable_flag := NULL;
368 End If;
369 -- End bug 4518893
370 RAISE INVALID_DATA;
371 END IF;
372
373 END return_error;
374
375
376
377 -- =====================================================================
378 -- This procedure checks if a quantity has been entered for VENDOR INVOICE
379 -- items having an expenditure type where a cost rate is required.
380
381 PROCEDURE check_quantity
382 IS
383 dummy NUMBER;
384 BEGIN
385 SELECT count(1)
386 INTO dummy
387 FROM dual
388 WHERE NOT EXISTS
389 ( SELECT 1
390 FROM pa_expend_typ_sys_links etsl
391 , pa_expenditure_types et
392 WHERE etsl.expenditure_type = et.expenditure_type
393 AND etsl.expenditure_type = X_expenditure_type
394 AND etsl.system_linkage_function = 'VI'
395 AND et.cost_rate_flag = 'Y' );
396
397 IF ( dummy = 0 ) THEN
398 IF ( X_quantity IS NULL ) THEN
399 return_error( 'PA_EX_QTY_EXIST' );
400 END IF;
401 END IF;
402
403 END check_quantity;
404
405 -- =====================================================================
406 -- This procedure checks whether expenditure type is active on the
407 -- expenditure Item date.
408
409 PROCEDURE check_etype_date
410 IS
411 dummy NUMBER;
412 BEGIN
413 SELECT count(1)
414
415 INTO dummy
416 FROM pa_expenditure_types et
417 WHERE et.expenditure_type = X_expenditure_type
418 AND X_ei_date BETWEEN et.start_date_active AND
419 nvl( et.end_date_active, X_ei_date ) ;
420
421 IF ( dummy = 0 ) THEN
422
423 G_PREV_EXP_TYPE_ACTIVE :=0;
424 return_error('EXP_TYPE_INACTIVE') ;
425
426 ELSE
427 G_PREV_EXP_TYPE_ACTIVE :=1;
428
429 END IF ;
430
431 END check_etype_date;
432
433 -- =====================================================================
434 -- This procedure checks whether non_labor_resource org is active on the
435 -- expenditure Item date.
436
437 PROCEDURE check_nlro_date
438 IS
439 dummy NUMBER;
440 BEGIN
441 SELECT count(1)
442 INTO dummy
443 FROM pa_organizations_v
444 WHERE organization_id = X_nl_resource_org_id
445 AND X_ei_date between date_from and nvl(date_to,X_ei_date);
446 IF ( dummy = 0 ) THEN
447 IF pa_trx_import.g_skip_tc_flag <> 'Y' then /* Added for Bug # 2170237 */
448 return_error('PA_TR_EPE_NLR_ORG_NOT_ACTIVE') ;
449 END IF ; /* Added for Bug # 2170237 */
450 END IF ;
451
452 END check_nlro_date;
453
454 -- =====================================================================
455 -- This procedure checks for the level of transaction control
456
457 PROCEDURE check_level
458 IS
459 dummy NUMBER;
460 BEGIN
461 SELECT count(1)
462 INTO dummy
463 FROM dual
464 WHERE EXISTS
465 ( SELECT 1
466 FROM pa_transaction_controls
467 WHERE project_id = X_project_id
468 AND task_id = l_task_id ) ; -- Replaced X_task_id with l_task_id for bug 16220146
469
470 IF ( dummy = 0 ) THEN
471
472 G_PREV_LEVEL := 'P';
473 level_flag := 'P' ;
474 ELSE
475
476 G_PREV_LEVEL := 'T';
477 level_flag := 'T' ;
478 END IF;
479
480 END check_level;
481
482 -- ==========================================================================
483 /*Added check_person_id() for bug# 2188422*/
484 -- This Procedure checks for the for valid person Id if the system linkage is ER
485 /*2955795 :Restructured this procedure and replaced the existing logic to determine if a transaction
486 belongs to Expense Report or Supplier invoices on the basis of system_linkage attached to expenditure type*/
487 /*This is done in view of new IN parameter to get_status() - p_sys_link_function storing 'VI'/'ER' for
488 Expense Report/Supplier invoices ,which was not there earlier .*/
489 /*For earlier logic please refer to previous versions*/
490
491 PROCEDURE check_person_id /*Bug# 2955795*/
492 IS
493 dummy NUMBER;
494 dummy2 NUMBER; /* bug # 2426506 */
495 DUMMY3 number; --Added Bug 16451280
496
497 BEGIN
498 IF p_sys_link_function ='ER' THEN /*2955795*/
499
500 /* bug # 2426506 */
501 SELECT count(*)
502 INTO dummy2
503 FROM po_vendors_ap_v
504 WHERE vendor_id = x_vendor_id
505 AND vendor_type_lookup_code = 'EMPLOYEE'
506 AND employee_id is NULL ;
507
508 SELECT count(*)
509 INTO dummy
510 FROM po_vendors_ap_v
511 WHERE vendor_id = x_vendor_id
512 AND employee_id is NULL ;
513 /* Start Code Change for Bug 16451280 */
514 select COUNT(*)
515 into DUMMY3
516 from PER_PEOPLE_F PPL,
517 PER_ASSIGNMENTS_F ASGN ,
518 PER_PERSON_TYPES PTYPE ,
519 PO_VENDORS_AP_V POAPV
520 where
521 POAPV.VENDOR_ID = X_VENDOR_ID
522 and POAPV.VENDOR_ID = ASGN.VENDOR_ID
523 and PPL.PERSON_ID = ASGN.PERSON_ID
524 and poapv.EMPLOYEE_ID is null
525 and PTYPE.PERSON_TYPE_ID = PPL.PERSON_TYPE_ID
526 and PTYPE.SYSTEM_PERSON_TYPE in ('OTHER','CWK');
527
528 /* End Code Change for Bug 16451280 */ /* bug # 2426506, added if condition */
529
530 IF (DUMMY3 <> 0) and P_SYS_LINK_FUNCTION='ER' then --Added if condition for Bug 16451280
531 null;
532 ELSE
533 IF (dummy2<>0) and p_sys_link_function='ER' then /*2955795 :Added p_sys_link_function condition*/
534 x_msg_type :='E';
535 return_error('PA_ER_CANNOT_XFACE_EMP');
536 ELSE
537 IF ( dummy <> 0 ) and p_sys_link_function = 'ER' then /*2955795 :Added p_sys_link_function condition*/
538 x_msg_type :='E';
539 return_error( 'PA_ER_CANNOT_XFACE');
540 End IF;
541 END IF;
542 END IF;
543 END IF; /*p_sys_link_function ='ER' :Bug# 2955795*/
544 END check_person_id; /*2955795*/
545 /*End of changes for bug# 2188422*/
546 -- ======================================================================+
547 /** This API checks the person is valid or not for the given
548 ** Expenditure item date . For expense Reports the x_person_id will be
549 ** NULL so derive the person_id based on vendor and check whether the person
550 ** is active or Not
551 ** Bug fix :2483863 **/
552 FUNCTION check_active_employee (p_vendor_id Number
553 ,p_person_id Number
554 ,p_Ei_Date Date ) Return varchar2 IS
555
556 l_return_string varchar2(10) := 'Y';
557 l_return_number Number := NULL;
558 l_emp_number Number := Null;
559 CURSOR cur_emp IS
560 SELECT vend.employee_id
561 FROM po_vendors vend
562 WHERE vend.vendor_id = p_vendor_id
563 AND p_ei_date BETWEEN nvl(vend.start_date_active,p_ei_date) AND
564 nvl( vend.end_date_active, trunc(sysdate) ) ;
565
566 BEGIN
567 If nvl(p_person_id,0) = 0 then
568 OPEN cur_emp;
569 FETCH cur_emp INTO l_emp_number;
570 CLOSE cur_emp;
571 Else
572 l_emp_number := p_person_id;
573 End If;
574
575 If l_emp_number is NOT NULL then
576
577 l_return_number := pa_utils.GetEmpOrgId( l_emp_number, p_ei_date );
578 If l_return_number is NULL then
579 l_return_string := 'N';
580 End If;
581 End If;
582 Return l_return_string;
583
584 END check_active_employee;
585 /** End of bug fix : 2483863 ***/
586
587 -- ======================================================================+
588 /* Added check_etype_eclass for bug 2831477 */
589
590 PROCEDURE check_etype_eclass( X_etype IN VARCHAR2
591 , X_system_linkage IN VARCHAR2 ) IS
592 BEGIN
593
594 If (nvl(G_EXP_TYPE,'NULL') <> X_etype OR nvl(G_EXP_TYPE_SYS_LINK,'NULL') <> X_system_linkage) then
595
596 SELECT
597 system_linkage_function
598 ,start_date_active
599 ,end_date_active
600 INTO
601 G_EXP_TYPE_SYS_LINK
602 ,G_EXP_TYPE_START_DATE
603 ,G_EXP_TYPE_END_DATE
604 FROM pa_expend_typ_sys_links
605 WHERE system_linkage_function = X_system_linkage
606 AND expenditure_type = X_etype ;
607
608
609 G_EXP_TYPE := X_etype;
610
611 End If;
612
613 Exception
614 When No_Data_Found Then
615 G_EXP_TYPE_SYS_LINK := Null;
616 G_EXP_TYPE_START_DATE := Null;
617 G_EXP_TYPE_END_DATE := Null;
618 When Others Then
619 Raise;
620
621 END check_etype_eclass;
622
623 --=====================================================================+
624 /* Added this procedure for bug 2942492 */
625
626 /* Added parameter p_sys_link for bug 2939224 */
627
628 FUNCTION check_person_level_TCs(p_sys_link in varchar2,
629 p_project_id IN NUMBER,
630 p_task_id IN NUMBER DEFAULT NULL,
631 x_person_id in NUMBER DEFAULT NULL) -- added the parameter x_person_id for bug 4585740
632 RETURN BOOLEAN
633
634 IS
635
636 TC_EXISTS NUMBER;
637 l_tc_count NUMBER; /* Added for bug4778164 */
638
639 BEGIN
640 /* Starts - Added for bug4778164 */
641 l_tc_count := 0;
642
643 If p_task_id is not null then
644
645 SELECT count(1) INTO l_tc_count
646 From pa_transaction_controls
647 WHERE project_id = p_project_id
648 AND task_id = p_task_id;
649
650 Else
651
652 SELECT count(1) INTO l_tc_count
653 From pa_transaction_controls
654 WHERE project_id = p_project_id
655 AND task_id is NULL; --Added for the bug 12915555
656
657 End If;
658 /* Ends - Added for bug4778164 */
659 /*Commented the following for bug 5735180
660 If ( l_tc_count > 0 AND p_sys_link = 'VI') THEN */ /* Added l_tc_count for bug4778164 */ -- added for bug 2939224
661 /* Commented the following for bug 5735180
662
663 IF p_task_id is not null then
664
665 print_message(' p '||p_project_id||' t '||p_task_id);
666
667 SELECT 1 INTO TC_EXISTS
668 From pa_transaction_controls
669 WHERE project_id = p_project_id
670 AND task_id = p_task_id
671 AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
672 AND person_id IS NULL
673 AND non_labor_resource IS NULL
674 AND ROWNUM =1;
675
676 ELSE
677
678 SELECT 1 INTO TC_EXISTS
679 FROM pa_transaction_controls
680 WHERE project_id = p_project_id
681 AND task_id is NULL
682 AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
683 AND person_id IS NULL
684 AND non_labor_resource IS NULL
685 AND ROWNUM =1;
686
687 END IF;
688 */
689 /* Code added for bug 2939224 */
690 /*Commented and added VI also for bug 5735180
691 ELSIF (( l_tc_count > 0 ) AND (p_sys_link = 'USG' or p_sys_link = 'PJ')) THEN Added l_tc_count for bug4778164 */
692 IF (( l_tc_count > 0 ) AND (p_sys_link = 'USG' or p_sys_link = 'PJ' or p_sys_link = 'VI')) THEN /* Added l_tc_count for bug4778164 */
693
694 IF p_task_id is not null then
695
696 print_message(' p '||p_project_id||' t '||p_task_id);
697
698 SELECT 1 INTO TC_EXISTS
699 FROM pa_transaction_controls
700 WHERE project_id = p_project_id
701 AND task_id = p_task_id
702 -- Commented for bug 4585740
703 -- AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
704 AND (person_id IS NULL OR x_person_id is NOT NULL) -- Modified for bug 4585740
705 AND ROWNUM =1;
706
707 ELSE
708
709 SELECT 1 INTO TC_EXISTS
710 FROM pa_transaction_controls
711 WHERE project_id = p_project_id
712 AND task_id is NULL
713 -- Commented for bug 4585740
714 -- AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
715 AND (person_id IS NULL OR x_person_id is NOT NULL) -- Modified for bug 4585740
716 AND ROWNUM =1;
717
718 END IF;
719
720 END IF;
721
722 /* end of code added for bug 2939224 */
723 RETURN TRUE;
724
725 EXCEPTION
726 WHEN NO_DATA_FOUND THEN
727 RETURN FALSE;
728
729 END;
730 /*======================================================================*/
731 /**Bug# 3494771 :
732 **This procedure is to check if a given expenditure_type is of
733 **system_linkage_function 'VI' .
734 **This check is required for validation of exp. type when user
735 **enters it manually in IProcurement.
736 */
737 PROCEDURE check_exp_type
738 IS
739 dummy NUMBER;
740 BEGIN
741
742 dummy :=0;
743
744 SELECT count(1)
745 INTO dummy
746 FROM dual
747 WHERE EXISTS
748 ( SELECT 1
749 FROM pa_expend_typ_sys_links etsl
750 , pa_expenditure_types et
751 WHERE etsl.expenditure_type = et.expenditure_type
752 AND etsl.expenditure_type = X_expenditure_type
753 AND etsl.system_linkage_function = 'VI'
754 );
755
756 IF ( dummy = 0 ) THEN
757 return_error( 'PA_INVALID_EXPENDITURE_TYPE' );
758 END IF;
759
760 END check_exp_type;
761 /*End of changes for bug# 3494771*/
762 /*=====================================================================*/
763
764 -- This is the start of the get_status procedure logic
765
766 BEGIN
767
768 ---- Added for payroll billable_flag changes for 12.2 start bug#12344689
769 /* Start changes for bug#12597910 Commented the Condition */
770 /* IF ( P_payroll_accrual_flag = 'Y') THEN
771 --{
772 BEGIN
773
774 select BILL_LABOR_ACCRUAL into X_billable_flag from PA_PROJECTS WHERE project_id = X_project_id;
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 X_billable_flag := NULL;
779 END;
780
781 --} ---- Added for payroll billable_flag changes for 12.2 End bug#12344689
782 END IF;*/
783
784 /* End changes for bug#12597910 */
785
786 /** assign the in param assignment id to global variable **/
787 PATC.G_OVERIDE_ASSIGNMENT_ID := p_assignment_id;
788 print_message('Stage:PATC:10.10.001 GET_STATUS API');
789 print_message('IN PARAMS : Project_id:['||X_project_id||'] Task_id :['||X_task_id||'] EI_date:['||X_ei_date||
790 ']Exp Type:['||X_expenditure_type||'] non_labor_resource:['||X_non_labor_resource||']'||
791 'person_id:['||X_person_id||']quantity:['||X_quantity||']denom_currency_code:['||
792 X_denom_currency_code||']acct_currency_code:['||X_acct_currency_code||']transfer_ei:['||
793 X_transfer_ei||']incurred_by_org_id:['||X_incurred_by_org_id||']vendor_id:['||X_vendor_id||
794 ']System linkage:['||P_sys_link_function||'] assignment_id:['||p_assignment_id||
795 ']work_type_id:['||p_work_type_id||']Billable_flag:['||x_billable_flag||']'||
796 'calling module :['||X_Calling_Module||']Transaction_source:['||X_transaction_source||
797 ']TC skip Flag:['||Pa_Trx_Import.G_Skip_Tc_Flag||']');
798
799 /** Initialize the transaction controls skip flag for each call
800 * if the calling module is PAAPIMP and transaction source is AP EXPENSE
801 * we should revalidate the transaction at the time of import.and override the
802 * Pa_Trx_Import.G_Skip_Tc_flag **/
803 G_TRX_SKIP_FLAG := nvl(Pa_Trx_Import.G_Skip_Tc_Flag,'N');
804
805 /* Start: changes for CBS Enhancement: Bug 16220146 */
806 select count(*) into l_task_count
807 from pa_tasks
808 where project_id = X_project_id
809 and task_id = X_task_id;
810
811 IF l_task_count <> 0 THEN
812 l_task_id := X_task_id;
813 ELSE
814 BEGIN
815 select proj_element_id into l_task_id
816 from pa_alternate_tasks
817 where alt_task_id = X_task_id;
818 EXCEPTION
819 WHEN OTHERS THEN
820 l_task_id := X_task_id;
821 END;
822 END IF;
823
824 print_message('Actual Task Id : Task_id :['||l_task_id||'}');
825
826 /* End: changes for CBS Enhancement: Bug 16220146 */
827
828 /*Added for bug#13803367 */
829 IF (x_calling_module ='PAXVOTCBDEL') THEN
830 --{
831
832 l_calling_module := 'PAXVOTCB';
833
834
835 patcx.tc_extension(
836 X_project_id => X_project_id
837 , X_task_id => l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
838 , X_expenditure_item_date => X_ei_date
839 , X_expenditure_type => X_expenditure_type
840 , X_non_labor_resource => X_non_labor_resource
841 , X_incurred_by_person_id => X_person_id
842 , X_quantity => X_quantity
843 , X_denom_currency_code => X_denom_currency_code
844 , X_acct_currency_code => X_acct_currency_code
845 , X_denom_raw_cost => X_denom_raw_cost
846 , X_acct_raw_cost => X_acct_raw_cost
847 , X_acct_rate_type => X_acct_rate_type
848 , X_acct_rate_date => X_acct_rate_date
849 , X_acct_exchange_rate => X_acct_exchange_rate
850 , X_transferred_from_id => X_transfer_ei
851 , X_incurred_by_org_id => X_incurred_by_org_id
852 , X_nl_resource_org_id => X_nl_resource_org_id
853 , X_transaction_source => X_transaction_source
854 , X_calling_module => l_calling_module
855 , X_vendor_id => X_vendor_id
856 , X_entered_by_user_id => X_entered_by_user_id
857 , X_attribute_category => X_attribute_category
858 , X_attribute1 => X_attribute1
859 , X_attribute2 => X_attribute2
860 , X_attribute3 => X_attribute3
861 , X_attribute4 => X_attribute4
862 , X_attribute5 => X_attribute5
863 , X_attribute6 => X_attribute6
864 , X_attribute7 => X_attribute7
865 , X_attribute8 => X_attribute8
866 , X_attribute9 => X_attribute9
867 , X_attribute10 => X_attribute10
868 , X_attribute11 => X_attribute11
869 , X_attribute12 => X_attribute12
870 , X_attribute13 => X_attribute13
871 , X_attribute14 => X_attribute14
872 , X_attribute15 => X_attribute15
873 , X_msg_application => X_msg_application
874 , X_billable_flag => patcx_bill_flag
875 , X_msg_type => temp_msg_type
876 , X_msg_token1 => temp_msg_token1
877 , X_msg_token2 => temp_msg_token2
878 , X_msg_token3 => temp_msg_token3
879 , X_msg_count => temp_msg_count
880 , X_outcome => temp_status
881 , p_projfunc_currency_code => p_projfunc_currency_code
882 , p_projfunc_cost_rate_type => p_projfunc_cost_rate_type
883 , p_projfunc_cost_rate_date => p_projfunc_cost_rate_date
884 , p_projfunc_cost_exchg_rate => p_projfunc_cost_exchg_rate
885 , x_assignment_id => PATC.G_OVERIDE_ASSIGNMENT_ID
886 , p_work_type_id => p_work_type_id
887 , p_sys_link_function => p_sys_link_function
888 , P_Po_Header_Id => P_Po_Header_Id
889 , P_Po_Line_Id => P_Po_Line_Id
890 , P_Person_Type => l_Person_Type
891 , P_Po_Price_Type => P_Po_Price_Type
892 , P_Document_Type => P_Document_Type
893 , P_Document_Line_Type => P_Document_Line_Type
894 , P_Document_Dist_Type => P_Document_Dist_Type
895 , P_pa_ref_num1 => P_pa_ref_num1
896 , P_pa_ref_num2 => P_pa_ref_num2
897 , P_pa_ref_num3 => P_pa_ref_num3
898 , P_pa_ref_num4 => P_pa_ref_num4
899 , P_pa_ref_num5 => P_pa_ref_num5
900 , P_pa_ref_num6 => P_pa_ref_num6
901 , P_pa_ref_num7 => P_pa_ref_num7
902 , P_pa_ref_num8 => P_pa_ref_num8
903 , P_pa_ref_num9 => P_pa_ref_num9
904 , P_pa_ref_num10 => P_pa_ref_num10
905 , P_pa_ref_var1 => P_pa_ref_var1
906 , P_pa_ref_var2 => P_pa_ref_var2
907 , P_pa_ref_var3 => P_pa_ref_var3
908 , P_pa_ref_var4 => P_pa_ref_var4
909 , P_pa_ref_var5 => P_pa_ref_var5
910 , P_pa_ref_var6 => P_pa_ref_var6
911 , P_pa_ref_var7 => P_pa_ref_var7
912 , P_pa_ref_var8 => P_pa_ref_var8
913 , P_pa_ref_var9 => P_pa_ref_var9
914 , P_pa_ref_var10 => P_pa_ref_var10 );
915
916 IF ( patcx_bill_flag = 'N' OR patcx_bill_flag = 'Y' ) THEN
917 X_billable_flag := patcx_bill_flag;
918 ELSE
919 X_billable_flag := NVL(temp_bill_flag,'N');
920 END IF;
921
922 Pa_Trx_Import.G_Skip_Tc_Flag := G_TRX_SKIP_FLAG;
923 --}
924 ELSE --End of changes for bug#13803367
925 --{
926
927 /* Start- Commented the following code to override the skip tc flag for bug 4549869
928 IF ( x_calling_module = 'PAAPIMP' and x_transaction_source = 'AP EXPENSE') Then */
929 /** If the expenditure type is part of ER and VI then we should revalidate the
930 the transaction for expense report during import **/
931 /*Bug# 2955795 :This check is not required since we have p_sys_link_function now to
932 store 'ER'/'VI' for Expense Report/Supplier Invoice*/
933
934 /*check_person_id(p_mode => 'SYS_LINK_CHECK'
935 ,x_return_string => l_return_string );
936 If l_return_string = 'VI,ER,' or l_return_string = 'ER,VI,' then Bug# 2955795*/
937 /*
938 If p_sys_link_function ='ER' Then Bug# 2955795*/
939 /* print_message('Overriding the Pa_Trx_Import.G_Skip_Tc_Flag ');
940 Pa_Trx_Import.G_Skip_Tc_Flag := 'N';
941 End if;
942 End If;
943 Ends- Commented the following code to override the skip tc flag for bug 4549869 */
944 X_msg_type := 'E'; -- Initiliaze Error/Warning indicator parameter.
945
946 -- Calling Module PAXPRRPE - Exp Adjustments GUI Form
947 -- PAXINADI - Invoice Review GUI Form
948
949 IF ( (X_Calling_Module not in ('PAXPRRPE', 'PAXINADI')) AND
950 (substr(X_Calling_Module, 1, 2) <> 'PO' )) THEN
951
952 -- No Need to do quanitity checks for calls from GUI Forms
953 print_message('Stage:PATC:10.10.002');
954 IF P_sys_link_function = 'VI' THEN
955 check_quantity;
956 END IF;
957 END IF;
958
959 /* Bug# 3494771 : If calling module is from IProcurement then we need to
960 validate expenditure type maually entered by user */
961 IF X_Calling_Module ='POWEBREQ' THEN
962 print_message('Stage:PATC:10.10.02.5');
963 check_exp_type;
964 END IF;
965 /*End of changes for Bug# 3494771*/
966
967 -- Fix for Bug # 801194. For related Items, we skip the validations for
968 -- expenditure type date since the exp type for related items are created only
969 -- for 1 day. We just go thru the task level transaction control and the
970 -- tc_extension so that client controls and TC are not ignored
971
972 IF X_Calling_Module <> 'CreateRelatedItem' THEN
973
974 print_message('Stage:PATC:10.10.003');
975
976 IF (nvl(G_PREV_EI_DATE,trunc(sysdate-100000)) <> X_ei_date OR nvl(G_PREV_EXP_TYPE,'NO EXP TYPE') <> X_expenditure_type) THEN
977
978 G_PREV_EI_DATE := X_ei_date;
979 G_PREV_EXP_TYPE := X_expenditure_type;
980 check_etype_date ;
981
982 ELSE
983
984 IF( G_PREV_EXP_TYPE_ACTIVE =1) THEN
985 NULL;
986 ELSIF (G_PREV_EXP_TYPE_ACTIVE =0) THEN
987 return_error('EXP_TYPE_INACTIVE') ;
988 END IF;
989
990 END IF;
991
992 /* Added the call to check_etype_eclass and the validations after that for bug 2831477 */
993
994 IF p_sys_link_function is not null THEN
995
996 check_etype_eclass(X_expenditure_type, p_sys_link_function);
997
998 IF ( G_EXP_TYPE_SYS_LINK is NULL ) then
999 return_error('INVALID_ETYPE_SYSLINK') ;
1000 END IF ;
1001
1002 IF ( X_ei_date NOT BETWEEN G_EXP_TYPE_START_DATE AND nvl( G_EXP_TYPE_END_DATE, X_ei_date ) ) THEN
1003 return_error('ETYPE_SLINK_INACTIVE');
1004 END IF;
1005
1006 END IF;
1007
1008 END IF ; -- X_Calling_Module <> 'CreateRelatedItem'
1009
1010 /*Bug# 2188422*/
1011 /** ---p_person_id := x_person_id ; bug fix 2345895 **/
1012 /* l_sys_link_func :='XX'; Bug# 2955795*/
1013
1014 /** added 'APXIIMPT','apiimptb.pls' for payable import process bug fix : 2467454**/
1015 /** Added SelfService for validation during Self Service Expense Report Entry. 2971043 **/
1016 IF x_calling_module in ('APXINENT','apiindib.pls','apiimptb.pls','APXIIMPT','SelfService') THEN
1017
1018 print_message('Stage:PATC:10.10.003.1');
1019 check_person_id ; /*2955795*/
1020
1021 /*If ( l_return_string = 'VI,ER,' or l_return_string = 'ER,VI,'
1022 or l_return_string = 'VI,') then
1023 print_message('Stage:PATC:Setting the l_sys_link_func to VI');
1024 l_sys_link_func := 'VI';
1025 End if;
1026 :Bug# 2955795 :Setting of l_sys_link_func is not required now */
1027
1028 END IF;
1029 /*Bug# 2188422*/
1030
1031 /*Bug 2726763: Since the check to see if project is chargeable is done in pa_trx_import
1032 -- only if the transaction source's skip_tc_validation_flag is 'N', we have
1033 -- to add the check here for calling module 'APXIIMPT'. For all other calling
1034 -- modules the check will be performed when selecting the project from the
1035 -- form's LOV */
1036 If (x_calling_module = 'APXIIMPT') Then
1037
1038 print_message('Calling module = APXIIMPT');
1039
1040 If not pa_utils.IsCrossChargeable(X_Project_Id) then
1041 print_message('Project not chargeable');
1042 return_error('PA_PROJECT_NOT_VALID');
1043 End If ;
1044
1045 End If;
1046 /* end of Bug fix 2726763 */
1047
1048 -- Perform basic validation against transaction:
1049 -- * project status is not 'CLOSED'
1050 -- * task is chargeable
1051 -- * item date is between start and end dates of project/task
1052 -- * Made changes in the call of Return Error Based on the global Parameter
1053 -- * pa_trx_import.g_skip_tc_flag <> 'Y' then -- code added for Bug 1299910
1054
1055 OPEN project_info;
1056
1057 FETCH project_info INTO proj;
1058
1059 -- Fix for Bug # 801194. included CreateRelatedItem below to avoid
1060 -- Project level and task level checks for related items
1061
1062 IF X_Calling_Module not in ('PAXPRRPE', 'PAXINADI','CreateRelatedItem') THEN
1063 -- Project level and task level checks are not required
1064 -- because as they are validated in the GUI forms for these
1065 -- Calling Modules.
1066 print_message('Stage:PATC:10.10.004');
1067 IF ( project_info%ROWCOUNT = 0 ) THEN -- Project/Task combination is
1068 -- not valid
1069 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1070 print_message('Stage:PATC:10.10.005');
1071 return_error( 'PA_EXP_INV_PJTK' );
1072 End If;
1073
1074 ELSIF ( proj.p_template_flag = 'Y' ) THEN -- Checks if it is a
1075 -- template project
1076
1077 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1078 print_message('Stage:PATC:10.10.006');
1079 return_error( 'PA_EX_TEMPLATE_PROJECT');
1080 End If;
1081
1082 -- BUG: 4600792 PQE:R12 CHANGE AWARD END WHEN ENCUMBRANCE EXISTS, IMPORT ENC REVERSALS FOR CLOSE
1083 --
1084 /* Added trunc() for bug#5999555 */
1085 ELSIF ( trunc(X_ei_date) NOT BETWEEN trunc(proj.p_start_date) AND trunc(proj.p_end_date) )
1086 AND PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='Y' THEN
1087
1088 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1089 print_message('Stage:PATC:10.10.007');
1090 return_error( 'PA_EX_PROJECT_DATE' );
1091 End If;
1092
1093 ELSIF (PA_PROJECT_UTILS.Check_prj_stus_action_allowed(proj.project_status_code,'NEW_TXNS') = 'N' ) THEN
1094
1095 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1096 print_message('Stage:PATC:10.10.008');
1097 return_error( 'PA_NEW_TXNS_NOT_ALLOWED' );
1098 End If;
1099
1100 -- BUG: 4600792 PQE:R12 CHANGE AWARD END WHEN ENCUMBRANCE EXISTS, IMPORT ENC REVERSALS FOR CLOSE
1101 --
1102 -- ELSIF ( X_ei_date NOT BETWEEN proj.t_start_date AND proj.t_end_date ) AND PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='Y' THEN
1103 ELSIF ( trunc(X_ei_date) NOT BETWEEN trunc(proj.t_start_date) AND trunc(proj.t_end_date) ) AND PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='Y' THEN /* For bug 10628175 */
1104 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1105 print_message('Stage:PATC:10.10.009');
1106 return_error( 'PA_EXP_TASK_EFF' );
1107 End If;
1108
1109 ELSIF ( proj.t_chargeable_flag = 'N' ) THEN
1110
1111 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1112 print_message('Stage:PATC:10.10.010');
1113 return_error( 'PA_EXP_TASK_STATUS' );
1114 End If;
1115
1116 ELSIF (Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y') Then
1117
1118 print_message('Calling Check_fin_Task_published');
1119 PA_PROJ_ELEMENTS_UTILS.Check_Fin_Task_Published(
1120 p_project_id => x_project_id
1121 ,p_task_id => l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
1122 ,x_return_status => l_return_status_code
1123 ,x_error_message_code => l_err_msg_code);
1124
1125 IF l_return_status_code = 'N' THEN
1126
1127 print_message('check_fin_task_published returned status of N');
1128 return_error(l_err_msg_code);
1129
1130 END IF;
1131
1132 If p_sys_link_function = 'VI' and X_calling_module not in ('APXRICAD','POXPOEPO') Then /*Added for bug 3608942,6118060 , 12660934 */
1133 /* start for bug#3088249 */
1134 /* Modified the following select statement for bug 3620355
1135 select to_number(SUBSTR(USERENV('CLIENT_INFO'),1,10)) into l_prvdr_org_id from dual; */
1136 /* Begin Bug 5214766
1137 We no longer use client info to get the operating_unit in r12 due to MOAC being introduced.
1138 We using pa_moac_utils.get_current_org_id;
1139 select to_number(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' '
1140 ,NULL ,SUBSTRB(USERENV('CLIENT_INFO'),1,10))) into l_prvdr_org_id from dual;
1141 */
1142 l_prvdr_org_id := pa_moac_utils.get_current_org_id;
1143 /* End bug 5214766*/ /*14621140 - Added a closing brace here other wise get pa date was not getting called*/
1144
1145 /* Added for bug 15953273 */
1146 IF l_prvdr_org_id IS NULL THEN
1147
1148 IF pa_acc_gen_wf_pkg.g_invoice_id IS NOT NULL THEN
1149
1150 SELECT org_id
1151 INTO l_prvdr_org_id
1152 FROM ap_invoices_all
1153 WHERE invoice_id = pa_acc_gen_wf_pkg.g_invoice_id;
1154
1155 END IF;
1156
1157 END IF;
1158 /* Added for bug 15953273 */
1159
1160 l_pa_date := pa_utils2.get_pa_date( p_ei_date => X_ei_date
1161 ,p_gl_date => SYSDATE
1162 ,p_org_id => l_prvdr_org_id);
1163
1164 IF l_pa_date is null THEN
1165 print_message('PA Date is null');
1166 return_error( 'INVALID_PA_DATE');
1167 END IF;
1168 /* end for bug#3088249 */
1169 End If;
1170
1171 END IF;
1172
1173 END IF; -- X_Calling_Module not in ('PAXPRRPE', 'PAXINADI','CreateRelatedItem')
1174
1175 -- BUG: 4600792 PQE:R12 CHANGE AWARD END WHEN ENCUMBRANCE EXISTS, IMPORT ENC REVERSALS FOR CLOSE
1176 --
1177 -- Commneted resetting this global variable for bug 10078729/6050441
1178 /* Initializing the value -- Bug4138033
1179 PA_TRX_IMPORT.Set_GVal_ProjTskEi_Date('Y');
1180 End Bug# 4138033 */
1181
1182 -- If item passes basic validation, then validate the item against all
1183 -- applicable task-level transaction controls
1184
1185 -- If there any transaction controls defined for task set level_flag = 'T'
1186 -- else set the flag to 'P'.
1187
1188 level_flag := NULL ;
1189
1190 IF (nvl(G_PREV_PROJ_ID,-99) <> x_project_id OR nvl(G_PREV_TASK_ID,-99) <> l_task_id) THEN -- Replaced X_task_id with l_task_id for bug 16220146
1191
1192 G_PREV_PROJ_ID := x_project_id;
1193 G_PREV_TASK_ID := l_task_id; -- Replaced X_task_id with l_task_id for bug 16220146
1194
1195 check_level ;
1196
1197 ELSE /* proj id and task id are the same */
1198
1199 level_flag := G_PREV_LEVEL;
1200
1201 END IF;
1202 -- Begin to check transaction controls
1203
1204 OPEN task_level_tc; /*Bug# 2955795*/
1205
1206 -- The following fetch will get both task level and project level controls
1207 -- in the order.
1208
1209 FETCH task_level_tc INTO tc;
1210
1211 /* Start changes for Bug#12714789 */
1212 l_temp_task_bill_flag := tc.billable_indicator;
1213 /* end changes for bug#12714789 */
1214
1215 /* Start -- CWK block moved from below to here for assigning the person_type at the initial stage only
1216 for bug 5948324*/
1217
1218 /** Begin PA.M/CWK changes **/
1219
1220 -- Fix start for bug : 3681318
1221 IF X_person_id IS NOT NULL /*AND P_PERSON_TYPE IS NULL*/ then /*Commented p_person_type for bug 7395534 */
1222
1223 BEGIN
1224 select p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
1225 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1226 from per_all_people_f p
1227 where p.person_id = x_person_id
1228 and trunc( x_ei_date) between trunc(p.effective_start_date) and trunc( p.effective_end_date) /* Added trunc() for bug 16451280 */
1229 and ((p.current_employee_flag = 'Y') OR (p.current_npw_flag = 'Y')); -- added for bug 7395534
1230 --and p.effective_start_date <= x_ei_date
1231 --and p.effective_end_date >= x_ei_date ;
1232
1233 if l_CURRENT_EMPLOYEE_FLAG IS NOT NULL then
1234 l_person_type := 'EMP' ;
1235 else
1236 l_person_type := 'CWK' ;
1237 end if ;
1238
1239 Exception
1240 WHEN NO_DATA_FOUND THEN
1241 /* Bug 6053374: If condition introduced. No error should be thrown in this case, for a Standard Invoice.*/
1242 IF p_sys_link_function = 'VI' THEN
1243 null;
1244 ELSE
1245 /*changes for bug 10271889 start*/
1246 patc.check_termination (X_person_id, x_ei_date, l_ac_termination_date);
1247
1248 /* Start changes for bug#13709322 */
1249
1250 IF (l_ac_termination_date IS NULL) THEN
1251 --{
1252 patc.check_termination_for_cwk(X_person_id, x_ei_date,
1253 l_ac_termination_date);
1254
1255 IF (l_ac_termination_date IS NOT NULL) THEN
1256 --{
1257 l_person_type := 'CWK';
1258 --}
1259 END IF;
1260
1261
1262 --}
1263 ELSE
1264 --{
1265 l_person_type := 'EMP';
1266 --}
1267 END IF;
1268 /* End changes for bug#13709322 */
1269
1270 IF (l_ac_termination_date IS NOT NULL) THEN
1271
1272 OPEN cur_final_process_date (X_person_id, X_ei_date);
1273 FETCH cur_final_process_date INTO l_final_process_date;
1274 CLOSE cur_final_process_date;
1275 /*commented out for 12737060
1276 select p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
1277 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1278 from per_all_people_f p
1279 where p.person_id = x_person_id
1280 and x_ei_date between p.effective_start_date and l_final_process_date
1281 and nvl(p.effective_end_date,l_final_process_date) >= l_final_process_date; --added for 11933246 */
1282 --and ((p.current_employee_flag = 'Y') OR (p.current_npw_flag = 'Y')); -- added for bug 7395534
1283 --and p.effective_start_date <= x_ei_date
1284 --and p.effective_end_date >= x_ei_date ;
1285 /*Commeneted the changes for bug#13709322 */
1286
1287 /*12737060 select distinct p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
1288 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1289 from per_all_people_f p, per_periods_of_service pos
1290 where p.person_id = x_person_id
1291 and p.person_id = pos.person_id
1292 and p.effective_start_date = pos.date_start
1293 and x_ei_date between pos.date_start and
1294 /*12737060 nvl(nvl(pos.final_process_date, pos.actual_termination_date),to_date('4712/12/31','YYYY/MM/DD'));
1295 */
1296 /* Start changes for bug#13709322 */
1297
1298 IF (x_calling_module like 'PSP%') THEN /*Changed condition for
1299 bug#13775807*/
1300 --{
1301 IF (l_person_type = 'EMP') THEN
1302 --{
1303 select distinct p.CURRENT_EMPLOYEE_FLAG ,
1304 p.CURRENT_NPW_FLAG
1305 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1306 from per_all_people_f p, per_periods_of_service pos
1307 where p.person_id = x_person_id
1308 and p.person_id = pos.person_id
1309 and p.effective_start_date = pos.date_start
1310 and x_ei_date between pos.date_start and
1311 nvl(pos.final_process_date,to_date('31-12-4712','DD-MM-YYYY'));
1312 --}
1313 ELSE
1314 --{
1315 select distinct p.CURRENT_EMPLOYEE_FLAG ,
1316 p.CURRENT_NPW_FLAG
1317 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1318 from per_all_people_f p, per_periods_of_placement pop
1319 where p.person_id = x_person_id
1320 and p.person_id = pop.person_id
1321 and p.effective_start_date = pop.date_start
1322 and x_ei_date between pop.date_start and
1323 nvl(pop.final_process_date,to_date('31-12-4712','DD-MM-YYYY'));
1324
1325 --}
1326 END IF;
1327
1328
1329 --}
1330 ELSE
1331 --{
1332
1333 IF (l_person_type = 'EMP') THEN
1334 --{
1335
1336 select distinct p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
1337 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1338 from per_all_people_f p, per_periods_of_service pos
1339 where p.person_id = x_person_id
1340 and p.person_id = pos.person_id
1341 and p.effective_start_date = pos.date_start
1342 and x_ei_date between pos.date_start and
1343 nvl(nvl(pos.final_process_date,
1344 pos.actual_termination_date),to_date('4712/12/31','YYYY/MM/DD'));
1345
1346 ELSE
1347 --{
1348 select distinct p.CURRENT_EMPLOYEE_FLAG ,
1349 p.CURRENT_NPW_FLAG
1350 into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
1351 from per_all_people_f p, per_periods_of_placement pop
1352 where p.person_id = x_person_id
1353 and p.person_id = pop.person_id
1354 and p.effective_start_date = pop.date_start
1355 and x_ei_date between pop.date_start and
1356 nvl(nvl(pop.final_process_date,
1357 pop.actual_termination_date),to_date('4712/12/31','YYYY/MM/DD'));
1358
1359 --}
1360 END IF;
1361
1362 --}
1363 END IF;
1364
1365 /* End changes for bug#13709322 */
1366
1367
1368
1369 if l_CURRENT_EMPLOYEE_FLAG IS NOT NULL then
1370 l_person_type := 'EMP' ;
1371 else
1372 l_person_type := 'CWK' ;
1373 end if ;
1374 ELSE
1375 /* Raise ; Commented for bug 5151539 */
1376 print_message('No Active Assignment with the given information'); /* Added for bug5151539 */
1377 return_error( 'PA_NO_ASSIGNMENT'); /* Added for bug5151539 */
1378 END IF; /*changes for bug 10271889 end*/
1379 END IF;
1380 END ;
1381
1382 END IF ; -- X_person_id IS NOT NULL AND P_PERSON_TYPE IS NULL
1383
1384 /*if p_person_type IS NOT NULL and X_person_id IS NOT NULL then
1385 l_person_type := p_person_type ;
1386 end if ; Commented for bug 7395534 */
1387 -- Fix end for bug : 3681318
1388
1389 ---******** Selva Code starts
1390
1391 IF task_level_tc%NOTFOUND THEN
1392
1393 print_message('Stage:PATC:10.11.001');
1394
1395 IF proj.t_limit_flag = 'Y' THEN
1396
1397 /* Commented for bug 2939224
1398 IF (p_sys_link_function <> 'VI' OR (p_sys_link_function = 'VI' AND check_person_level_TCs(x_project_id, x_task_id))) THEN */ -- added for bug 2942492
1399
1400 -- Added x_person_id parameter to function call for bug 4585740
1401 /* Added x_person_id is NULL condition below for bug 8290672 */
1402 If ((x_person_id is NULL) OR (check_person_level_TCs(p_sys_link_function, x_project_id, l_task_id,x_person_id))) THEN -- added for bug 2939224/bug 4912880 added x_person_id to call -- Replaced X_task_id with l_task_id for bug 16220146
1403
1404 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1405 print_message('Stage:PATC:10.11.002');
1406
1407 -- Added following block for CWk changes bug# 5948324
1408 if l_person_Type = 'CWK' Then
1409 return_error('PA_CWK_TXN_NOT_ALLOWED');
1410 Else
1411 return_error('PA_EXP_TASK_TC');
1412 END IF;
1413 -- End CWK changes bug# 5948324
1414
1415 End If;
1416
1417 END IF;
1418
1419 END IF ;
1420
1421 -- Changed by Rajnish .
1422 -- Removed ( level_flag <> 'T') from the following If condition
1423 -- This is the fix for bug # 1363773
1424
1425 /*
1426 IF ( level_flag <> 'T' AND proj.p_limit_flag = 'Y') THEN commented for bug 1363773 */
1427
1428 IF proj.p_limit_flag = 'Y' THEN /*Added for bug 1363773*/
1429
1430 /* Commented the if for bug 2939224
1431 IF (p_sys_link_function <> 'VI' OR
1432 (p_sys_link_function = 'VI' AND check_person_level_TCs(x_project_id))) THEN */-- added for bug 2942492
1433 -- Added x_person_id parameter to function call for bug 4585740
1434 /* Added x_person_id is NULL condition below for bug 8290672 */
1435 IF ((x_person_id is NULL) OR (check_person_level_TCs(p_sys_link_function, x_project_id,x_person_id))) THEN -- added for bug 2939224/bug 4912880 added x_person_id to call
1436
1437 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1438 print_message('Stage:PATC:10.11.003');
1439 return_error( 'PA_EXP_PJ_TC' );
1440 End If;
1441
1442 END IF;
1443
1444 END IF ;
1445
1446 ELSE -- task_level_tc found
1447
1448 print_message('Stage:PATC:10.12.001');
1449 print_message('Transaction contol record:task_id['||tc.task_id||
1450 ']person_id['||tc.person_id||']exp_cat['||tc.expenditure_category||
1451 ']exp_type['||tc.expenditure_type||']non_lab_res['||tc.non_labor_resource||
1452 ']charge_flag['||tc.chargeable_flag||']bill_flag['||tc.billable_indicator||
1453 ']schd_exp['||tc.SCHEDULED_EXP_ONLY||']' );
1454
1455 -- if the task level limit flag is true then there should be
1456 -- a transaction control at task level.
1457
1458 IF ( proj.t_limit_flag = 'Y' AND tc.task_id is NULL ) THEN
1459
1460 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1461
1462 print_message('Stage:PATC:10.12.002');
1463 -- Begin CWK/FP_M changes
1464 /* IF p_sys_link_function not in ('USG', 'PJ') THEN --Bug 4266933*//*Commented for Bug 5735180*/
1465
1466
1467 If l_Person_Type = 'EMP' Then
1468 return_error( 'PA_EXP_TASK_TC' );
1469 Elsif l_Person_Type = 'CWK' Then
1470 return_error('PA_CWK_TXN_NOT_ALLOWED');
1471 Else
1472 -- Added the following block for the condition Limit to TC='Y' and don't have any
1473 -- TCs and EI is not based on Employee Bug#5948324
1474 return_error('PA_TR_EPE_TASK_TXN_CTRLS');
1475 End If;
1476
1477 -- End CWK/FP_M changes
1478
1479 /* END IF; --Bug 4266933*/ /*For Bug 5735180*/
1480
1481 End If;
1482
1483 END IF ;
1484
1485 -- Modified against Bug# 674526
1486 -- a new temporary variable is created to get the level
1487 -- of Txn Control here.
1488 -- rest of the code is kept as it is
1489
1490 IF tc.task_id is NOT NULL THEN
1491 level_flag_local := 'T' ;
1492 ELSE
1493 level_flag_local := 'P' ;
1494 END IF ;
1495
1496 /** Added this code for Transaction Controls for PJRM
1497 * If PRM installed and system linkage function in ST,OT,ER and
1498 * transaction controls setup donot allow unscheduled assignment and assignment is NULL then
1499 * return error else do nothing
1500 * Added the following if - else conditions based on the following matrix
1501 * PJRM check is required based on the system linkage function ( ST, OT, ER).If sys link func param is
1502 * Null , then figure out based on Trx source and calling module . If Trx source is NULL
1503 * figure out based on the calling module and expenditure type
1504 * source calling module sys_link Trx source skip tc flag
1505 * --------------------------------------------------------------------------------
1506 * 1.Pre approved PAXPRRPE Yes - check <> Y
1507 * 2.SST PAXVSSTS - Yes check <> Y
1508 * 3.SSE SelfService - - check <> Y
1509 * 4.OTL PAXVOTCB - Yes check <> Y
1510 * 5.AP payables-VI APXINENT - - check <> Y
1511 * 5.AP payables-ER APXINENT - - donot check
1512 * 6.Import PAAPIMP Yes AP EXPENSE donot check
1513 * 7.Import PAAPIMP Yes - check <> Y
1514 * 8.All others Yes check <> Y
1515 * 9.Payable Imports APXIIMPT - -
1516 **/
1517 l_check_pjrm_tc_flag := 'N';
1518 l_return_string := NULL;
1519
1520 --- Bug 4092732 IF nvl(PA_INSTALL.is_prm_licensed,'N') = 'Y' Then
1521
1522 IF p_sys_link_function in ('ST','OT','ER') THEN
1523
1524 IF ( x_calling_module = 'PAAPIMP' and x_transaction_source = 'AP EXPENSE') OR
1525 ( x_calling_module <> 'PAAPIMP' and Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' ) Then
1526
1527 print_message('Stage:PATC:10.12.003.1');
1528 l_check_pjrm_tc_flag := 'Y';
1529
1530 END IF;
1531 /** added 'APXIIMPT','apiimptb.pls' for bug fix : 2467454 **/
1532 ELSIF ( x_calling_module in ('APXINENT','apiindib.pls','apiimptb.pls','APXIIMPT')) Then
1533 /** Based on the expenditure_type derive the system linkage function
1534 * IF expenditure_type is part of ER,VI the display warning
1535 * else raise error **/
1536
1537 print_message('Stage:PATC:10.12.003.2');
1538 /*This is not required now since we have p_sys_link_function now that contains
1539 store 'ER'/'VI' for Expense Report/Supplier Invoice
1540 check_person_id(p_mode => 'SYS_LINK_CHECK'
1541 ,x_return_string => l_return_string ); Bug# 2955795*/
1542
1543 If p_sys_link_function = 'ER' then /*Bug# 2955795*/
1544 l_check_pjrm_tc_flag := 'Y';
1545 Else
1546 l_check_pjrm_tc_flag := 'N';
1547 End if;
1548
1549 ELSIF ( x_calling_module IN ('SelfService','PAXVSSTS','PAXVOTCB')) Then
1550
1551 print_message('Stage:PATC:10.12.003.3');
1552 l_check_pjrm_tc_flag := 'Y';
1553
1554 ELSIF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' then
1555
1556 print_message('Stage:PATC:10.12.003.4');
1557 l_check_pjrm_tc_flag := 'N';
1558
1559 END IF;
1560
1561 /* Bug 4092732
1562 ELSE -- prm is not installed
1563
1564 print_message('Stage:PATC:10.12.003.5');
1565 l_check_pjrm_tc_flag := 'N';
1566 PATC.G_OVERIDE_ASSIGNMENT_ID := p_assignment_id;
1567
1568 END If;
1569 */
1570 Temp_allow_unscheduled_exp := nvl(tc.SCHEDULED_EXP_ONLY,'N') ;
1571 IF ( p_assignment_id is NULL and x_person_id is NOT NULL and l_check_pjrm_tc_flag = 'Y') Then
1572
1573 print_message('Stage:PATC:10.12.003.6');
1574 temp_assignment_id := PA_UTILS4.get_assignment_id
1575 (p_person_id => x_person_id
1576 ,p_project_id => x_project_id
1577 ,p_task_id => l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
1578 ,p_ei_date => x_ei_date );
1579 Else
1580
1581 print_message('Stage:PATC:10.12.003.7');
1582 temp_assignment_id := p_assignment_id;
1583
1584 End If;
1585
1586 print_message('Check for PJRM controls temp_assignment_id:['||temp_assignment_id||
1587 ']l_return_string:['||l_return_string||
1588 ']l_check_pjrm_tc_flag:['||l_check_pjrm_tc_flag||
1589 ']Prm Installed['||nvl(PA_INSTALL.is_prm_licensed,'N')||
1590 ']level_flag_local['||level_flag_local||']task_limit_flag['||proj.t_limit_flag||
1591 ']project_limit_flag['||proj.p_limit_flag||']' );
1592
1593 If (nvl(temp_assignment_id,0) = 0 and l_check_pjrm_tc_flag = 'Y' and nvl(tc.SCHEDULED_EXP_ONLY,'N') = 'Y' ) then
1594
1595 Print_Message('Stage:PATC:10.12.003.8');
1596 -- Bug 7715496 Changed message to PA_PJR_NO_ASSIGNMENT
1597 X_status := 'PA_PJR_NO_ASSIGNMENT'; -- Bug#3442186 Changed status from PA_INVALID_ASSIGNMENT
1598 X_Billable_Flag := NULL;
1599 Print_Message('PATC ERROR: ' || X_status);
1600 RAISE INVALID_DATA;
1601
1602 Else
1603
1604 Print_Message('Stage:PATC:10.12.003.9');
1605 PATC.G_OVERIDE_ASSIGNMENT_ID := temp_assignment_id;
1606
1607 End if; -- PA_NO_ASSIGNMENT
1608
1609 /** End of PJRM changes **/
1610
1611 /*The block has been moved to top for bug 5948324 as the person_type should get assigned
1612 before the initial check for TC */
1613
1614 -- Begin bug 4068808
1615 If ((proj.p_limit_flag = 'Y' and level_flag_local = 'P') or (proj.t_limit_flag = 'Y' and level_flag_local = 'T')) Then
1616
1617 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added for bug 4549869 */
1618
1619 -- If P_Person_Type = 'CWK' and Tc.Employees_Only_flag = 'Y' Then
1620 If l_Person_Type = 'CWK' and Tc.Employees_Only_flag = 'E' Then
1621
1622 Print_Message('Stage:PATC:10.12.004.1a');
1623 X_Status := 'PA_CWK_TXN_NOT_ALLOWED';
1624 X_Billable_Flag := NULL;
1625 Print_Message('PATC ERROR: ' || X_status);
1626 RAISE INVALID_DATA;
1627
1628 ElsIf l_Person_Type = 'EMP' and Tc.Employees_Only_flag = 'C' Then
1629
1630 Print_Message('Stage:PATC:10.12.004.1b');
1631 If level_flag_local = 'P' Then
1632 X_Status := 'PA_TR_EPE_PROJ_TXN_CTRLS';
1633 Else
1634 X_Status := 'PA_TR_EPE_TASK_TXN_CTRLS';
1635 End If;
1636 X_Billable_Flag := NULL;
1637 Print_Message('PATC ERROR: ' || X_status);
1638 RAISE INVALID_DATA;
1639
1640 Else
1641
1642 print_message('Stage:PATC:10.12.004.1c');
1643
1644 End If;
1645
1646 End If; /* Added for bug 4549869 */
1647
1648 ElsIf ((proj.p_limit_flag = 'N' and level_flag_local = 'P') or (proj.t_limit_flag = 'N' and level_flag_local = 'T')) Then
1649
1650 If Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added for bug 4549869 */
1651
1652 If l_Person_Type = 'CWK' and (tc.Employees_Only_Flag = 'C' or tc.Employees_Only_flag is Null) Then
1653
1654 If tc.chargeable_flag = 'N' Then -- Added for Bug#4549448
1655
1656 Print_Message('Stage:PATC:10.12.004.1d');
1657 X_Status := 'PA_CWK_TXN_NOT_ALLOWED';
1658 X_Billable_Flag := NULL;
1659 Print_Message('PATC ERROR: ' || X_status);
1660 RAISE INVALID_DATA;
1661
1662 End If; -- Added for Bug#4549448
1663
1664 ElsIf l_Person_Type = 'EMP' and (tc.Employees_Only_Flag = 'E' or tc.Employees_Only_flag is Null) Then
1665
1666 If tc.chargeable_flag = 'N' Then -- Added for Bug#4549448
1667
1668 Print_Message('Stage:PATC:10.12.004.1e');
1669
1670 If level_flag_local = 'P' Then
1671 X_Status := 'PA_TR_EPE_PROJ_TXN_CTRLS';
1672 Else
1673 X_Status := 'PA_TR_EPE_TASK_TXN_CTRLS';
1674 End If;
1675
1676 X_Billable_Flag := NULL;
1677 Print_Message('PATC ERROR: ' || X_status);
1678 RAISE INVALID_DATA;
1679
1680 End If; -- Added for Bug#4549448
1681
1682 -- Start, added the following block for bug 4556126
1683 -- Added 'BTC', 'WIP', 'INV' conditions for bug 6626535
1684
1685 /* 8333176: Removed BTC from the below condition to exclude BTC transactions being returned with error
1686 to avoid the billable_flag being NULL when inserting the BTC lines in Create and dstribute burden process */
1687
1688 ElsIf (p_sys_link_function in ('USG', 'PJ', 'VI', 'WIP', 'INV') and x_person_id is NULL) Then
1689
1690 If tc.chargeable_flag = 'N' Then
1691
1692 Print_Message('Stage:PATC:10.12.004.1e');
1693
1694 If level_flag_local = 'P' Then
1695 X_Status := 'PA_TR_EPE_PROJ_TXN_CTRLS';
1696 Else
1697 X_Status := 'PA_TR_EPE_TASK_TXN_CTRLS';
1698 End If;
1699
1700 X_Billable_Flag := NULL;
1701 Print_Message('PATC ERROR: ' || X_status);
1702 RAISE INVALID_DATA;
1703
1704 End If;
1705 -- End, added the following block for bug 4556126
1706 End If;
1707
1708 End If; -- Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' /* Added for bug 4549869 */
1709
1710 Else
1711
1712 print_message('Stage:PATC:10.12.004.2');
1713
1714 End If;
1715 -- End bug 4068808
1716
1717 If Pa_Pjc_CWk_Utils.Is_CWK_TC_Xface_Allowed(X_Project_Id) <> 'Y' And
1718 -- P_Person_Type = 'CWK' And
1719 l_Person_Type = 'CWK' And
1720 P_Sys_Link_Function in ('ST','OT') And
1721 P_PO_Line_Id Is Not Null Then
1722
1723 If Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added for bug 4549869 */
1724
1725 Print_Message('Stage:PATC:10.12.004.3');
1726 X_Status := 'PA_CWK_TC_NOT_ALLOWED';
1727 X_Billable_Flag := NULL;
1728 Print_Message('PATC ERROR: '|| X_status);
1729 RAISE INVALID_DATA;
1730
1731 End If; /* Added for bug 4549869 */
1732
1733 Else
1734
1735 Print_Message('Stage:PATC:10.12.004.4');
1736
1737 End If;
1738
1739 /** End PA.M/CWK changes **/
1740
1741 /** Begin PA.M/Task Assignment changes **/
1742
1743 If P_Sys_Link_Function in ('ST','OT','ER') and
1744 Pa_Project_Structure_Utils.Check_Workplan_Enabled(P_Project_Id => X_Project_Id) = 'Y' and
1745 Tc.Workplan_Res_Only_flag = 'Y' Then
1746
1747 If Pa_Task_Assignment_Utils.Check_Task_Asgmt_Exists (
1748 P_Person_Id => X_Person_id,
1749 P_Financial_Task_Id => l_task_id, -- Replaced X_task_id with l_task_id for bug 16220146
1750 P_Ei_Date => X_Ei_Date ) = 'N' Then
1751
1752 Print_Message('Stage:PATC:10.12.004.5');
1753 X_Status := 'PA_WP_RES_NOT_DEFINED';
1754 X_Billable_Flag := NULL;
1755 Print_Message('PATC ERROR: ' || X_Status);
1756 RAISE INVALID_DATA;
1757
1758 Else
1759
1760 Print_Message('Stage:PATC:10.12.004.6');
1761
1762 End If;
1763
1764 Else
1765
1766 Print_Message('Stage:PATC:10.12.004.7');
1767
1768 End If;
1769
1770 /** End PA.M/Task Assignment changes **/
1771
1772 If ((proj.p_limit_flag = 'Y' and level_flag_local = 'P') or (proj.t_limit_flag = 'Y' and level_flag_local = 'T')) Then
1773
1774 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added for bug 4549869 */
1775
1776 IF tc.chargeable_flag = 'N' THEN
1777
1778 IF level_flag_local = 'T' THEN
1779
1780 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1781
1782 print_message('Stage:PATC:10.12.005');
1783 return_error( 'PA_EXP_TASK_TC' );
1784
1785 End If;
1786
1787 END IF ;
1788 IF level_flag_local = 'P' THEN
1789
1790 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1791 print_message('Stage:PATC:10.12.006');
1792 return_error( 'PA_EXP_PJ_TC' );
1793 End If;
1794 END IF ;
1795
1796 ELSE
1797
1798 print_message('Stage:PATC:10.12.007');
1799 IF tc.person_id is NOT NULL AND tc.expenditure_category is NULL THEN
1800
1801 FETCH task_level_tc INTO tc;
1802 IF task_level_tc%FOUND THEN
1803 IF level_flag = 'T' AND
1804 tc.task_id is NOT NULL AND
1805 tc.chargeable_flag = 'N' THEN
1806
1807 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1808 print_message('Stage:PATC:10.12.008');
1809 return_error( 'PA_EXP_TASK_TC' );
1810 End If;
1811 /* Start changes for Bug#12714789 */
1812 l_temp_task_bill_flag := tc.billable_indicator;
1813 /* end changes for bug#12714789 */
1814
1815 END IF ;
1816 IF level_flag = 'P' AND
1817 tc.chargeable_flag = 'N' THEN
1818
1819 IF Pa_Trx_Import.G_Skip_Tc_Flag <> 'Y' Then /* Added If Condition for bug 1299909 */
1820 print_message('Stage:PATC:10.12.009');
1821 return_error( 'PA_EXP_PJ_TC' );
1822 End If;
1823 /* Start changes for Bug#12714789 */
1824 l_temp_task_bill_flag := tc.billable_indicator;
1825 /* end changes for bug#12714789 */
1826
1827 END IF ;
1828
1829 END IF ;
1830
1831 END IF ;
1832
1833 END IF ;
1834
1835 End If; /* Added for bug 4549869 */
1836
1837 End If; -- proj.p_limit_flag = 'Y' or proj.t_limit_flag = 'Y'
1838
1839 END IF ; -- IF task_level_tc%NOTFOUND
1840
1841 print_message('Stage:PATC:10.12.010');
1842 /* Start changes for Bug#12714789 */
1843 set_billable_flag(l_temp_task_bill_flag, proj.t_billable_flag );
1844 /* end changes for bug#12714789 */
1845
1846 set_billable_flag( tc.billable_indicator, proj.t_billable_flag );
1847
1848 ---******** Selva Code ends
1849
1850 CLOSE task_level_tc;
1851
1852 -- Bug # 801194. For related items, this check is not required
1853
1854 IF ( X_Calling_Module <> 'CreateRelatedItem') THEN
1855
1856 /** bug fix :2483863 for expense reports the employee should be validated
1857 ** Validate ER during Payable Entry form if the sys link is ER
1858 ** otherwise validate it during payable import to project
1859 **/
1860 /* If (x_calling_module in ('APXINENT','apiindib.pls','APXIIMPT','apiimptb.pls')) OR
1861 (x_calling_module = 'PAAPIMP' AND x_transaction_source = 'AP EXPENSE' ) Then
1862 check_person_id(p_mode => 'SYS_LINK_CHECK'
1863 ,x_return_string => l_return_string );
1864 print_message('Stage:PATC:10.12.010.1');
1865 End If; Commented for bug# 2955795 */
1866
1867 /** Added SelfService for validation during Self Service Expense Report Entry. 2971043 **/
1868 /** At present Iexpenses does not pass the p_sys_link_function parameter. But since
1869 in Ixpenses only Expense Reports are entered hence comparing based on calling
1870 module only, so that we do not introduce any dependencies.**/
1871 If (x_calling_module in ('APXINENT','apiindib.pls','APXIIMPT','apiimptb.pls') AND p_sys_link_function = 'ER') /*2955795*/
1872 OR (x_calling_module = 'SelfService')
1873 OR
1874 ( x_calling_module = 'PAAPIMP' AND
1875 x_transaction_source = 'AP EXPENSE' AND p_sys_link_function = 'ER' ) THEN /*Bug# 2955795*/
1876
1877 print_message('Stage:PATC:10.12.010.2');
1878
1879 IF nvl(check_active_employee
1880 (p_vendor_id => x_vendor_id
1881 ,p_person_id => x_person_id
1882 ,p_ei_date => x_ei_date),'N') = 'N' then
1883 print_message('Stage:PATC:10.12.010.3');
1884 return_error( 'NO_ASSIGNMENT');
1885
1886 End if;
1887
1888 End if;
1889 /** End of Bug fix: 2483863 **/
1890
1891 -- Bug 4044057 To add validation for active assignment for entered Purchase Order for CWK
1892 IF x_calling_module = 'PAXVOTCB' and p_po_header_id is not null THEN
1893
1894 l_job_id := pa_utils.GetEmpJobId(
1895 X_person_id => x_person_id,
1896 X_date => X_ei_date ,
1897 X_po_header_id => p_po_header_id,
1898 X_po_line_id => p_po_line_id);
1899
1900 -- Added PO params for bug 4044057
1901 -- Need to validate the assigment for the entered PO
1902 IF l_job_id is NULL THEN
1903
1904 print_message('Stage:PATC:10.12.010.4');
1905 return_error('NO_PO_ASSIGNMENT');
1906
1907 END IF;
1908
1909 END IF; -- End of Bug 4044057
1910
1911 -- Bug 570709. To add the expenditure org validation for transactions
1912 IF X_incurred_by_org_id is NOT NULL THEN
1913
1914 IF pa_trx_import.g_skip_tc_flag <> 'Y' then /* Added for Bug # 2170237 */
1915
1916 /* Enhancement request BUG12801896 changes STARTS here */
1917 /*IF pa_utils2.CheckExporg(X_incurred_by_org_id,X_ei_date) = 'N' then
1918
1919 print_message('Stage:PATC:10.12.011');
1920 return_error( 'PA_EXP_ORG_NOT_ACTIVE');
1921 END IF; */ --commented the condition for BUG12801896
1922
1923 IF (pa_utils2.CheckExporg(X_incurred_by_org_id,X_ei_date) = 'N' AND x_calling_module <> 'PAXPRRPE') THEN
1924 print_message('Stage:PATC:10.12.011');
1925 return_error( 'PA_EXP_ORG_NOT_ACTIVE');
1926 else
1927 print_message('Stage:PATC:10.12.011.01');
1928 END IF;
1929 /* Enhancement request BUG12801896 changes ENDS here */
1930
1931 END IF; /* Added for Bug # 2170237 */
1932
1933 ELSE
1934 /* Added for Bug#4604614 (BaseBug#4118885) -- Start */
1935 IF ( X_Calling_Module in ( 'PAXVSSTS','PAXVOTCB' )) THEN
1936 -- Bug 6156072: Base Bug 6045051: start
1937 -- Bug 6156072: Base Bug 6045051: If condition introduced to check for person_type and then
1938 -- call corresponding procedure to check for termination
1939 IF (l_Person_Type = 'EMP') THEN
1940 patc.check_termination (X_person_id, x_ei_date, l_ac_termination_date);
1941 ELSIF (l_Person_Type = 'CWK') THEN
1942 patc.check_termination_for_cwk (X_person_id, x_ei_date, l_ac_termination_date);
1943 END IF;
1944 -- Bug 6156072: Base Bug 6045051: end
1945 end if;
1946
1947 IF ( l_ac_termination_date is not null ) then
1948 X_org_id := pa_utils.GetEmpOrgId( X_person_id, l_ac_termination_date);
1949 ELSE
1950 X_org_id := pa_utils.GetEmpOrgId( X_person_id, X_ei_date );
1951 END IF;
1952 /* Added for Bug#4604614 (BaseBug#4118885) -- End */
1953
1954 IF ( X_org_id IS NULL ) THEN
1955 print_message('Stage:PATC:10.12.012');
1956 return_error( 'NO_ASSIGNMENT');
1957 END IF;
1958
1959 IF pa_trx_import.g_skip_tc_flag <> 'Y' then /* Added for Bug # 2170237 */
1960
1961 IF pa_utils2.CheckExporg(X_org_id,X_ei_date) = 'N' then
1962 print_message('Stage:PATC:10.12.013');
1963 return_error( 'PA_EXP_ORG_NOT_ACTIVE');
1964 END IF;
1965 END IF; /* Added for Bug # 2170237 */
1966
1967 END IF ;
1968
1969 -- Check for Non_labor_resource_org
1970
1971 if ( X_nl_resource_org_id is NOT NULL ) then
1972 print_message('Stage:PATC:10.12.014');
1973 check_nlro_date ;
1974 end if ;
1975
1976 END IF ; -- IF ( X_Calling_Module <> 'CreateRelatedItem')
1977
1978 patcx_bill_flag := temp_bill_flag;
1979
1980 print_message('Stage:PATC:10.13.00:Calling patcx.tc_extension api');
1981
1982 --Bug 3017533
1983 L_BeforeCE_AsgnId := PATC.G_OVERIDE_ASSIGNMENT_ID;
1984
1985 patcx.tc_extension(
1986 X_project_id => X_project_id
1987 , X_task_id => l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
1988 , X_expenditure_item_date => X_ei_date
1989 , X_expenditure_type => X_expenditure_type
1990 , X_non_labor_resource => X_non_labor_resource
1991 , X_incurred_by_person_id => X_person_id
1992 , X_quantity => X_quantity
1993 , X_denom_currency_code => X_denom_currency_code
1994 , X_acct_currency_code => X_acct_currency_code
1995 , X_denom_raw_cost => X_denom_raw_cost
1996 , X_acct_raw_cost => X_acct_raw_cost
1997 , X_acct_rate_type => X_acct_rate_type
1998 , X_acct_rate_date => X_acct_rate_date
1999 , X_acct_exchange_rate => X_acct_exchange_rate
2000 , X_transferred_from_id => X_transfer_ei
2001 , X_incurred_by_org_id => X_incurred_by_org_id
2002 , X_nl_resource_org_id => X_nl_resource_org_id
2003 , X_transaction_source => X_transaction_source
2004 , X_calling_module => X_calling_module
2005 , X_vendor_id => X_vendor_id
2006 , X_entered_by_user_id => X_entered_by_user_id
2007 , X_attribute_category => X_attribute_category
2008 , X_attribute1 => X_attribute1
2009 , X_attribute2 => X_attribute2
2010 , X_attribute3 => X_attribute3
2011 , X_attribute4 => X_attribute4
2012 , X_attribute5 => X_attribute5
2013 , X_attribute6 => X_attribute6
2014 , X_attribute7 => X_attribute7
2015 , X_attribute8 => X_attribute8
2016 , X_attribute9 => X_attribute9
2017 , X_attribute10 => X_attribute10
2018 , X_attribute11 => X_attribute11
2019 , X_attribute12 => X_attribute12
2020 , X_attribute13 => X_attribute13
2021 , X_attribute14 => X_attribute14
2022 , X_attribute15 => X_attribute15
2023 , X_msg_application => X_msg_application
2024 , X_billable_flag => patcx_bill_flag
2025 , X_msg_type => temp_msg_type
2026 , X_msg_token1 => temp_msg_token1
2027 , X_msg_token2 => temp_msg_token2
2028 , X_msg_token3 => temp_msg_token3
2029 , X_msg_count => temp_msg_count
2030 , X_outcome => temp_status
2031 , p_projfunc_currency_code => p_projfunc_currency_code
2032 , p_projfunc_cost_rate_type => p_projfunc_cost_rate_type
2033 , p_projfunc_cost_rate_date => p_projfunc_cost_rate_date
2034 , p_projfunc_cost_exchg_rate => p_projfunc_cost_exchg_rate
2035 , x_assignment_id => PATC.G_OVERIDE_ASSIGNMENT_ID
2036 , p_work_type_id => p_work_type_id
2037 , p_sys_link_function => p_sys_link_function
2038 , P_Po_Header_Id => P_Po_Header_Id
2039 , P_Po_Line_Id => P_Po_Line_Id
2040 , P_Person_Type => l_Person_Type
2041 , P_Po_Price_Type => P_Po_Price_Type
2042 , P_Document_Type => P_Document_Type
2043 , P_Document_Line_Type => P_Document_Line_Type
2044 , P_Document_Dist_Type => P_Document_Dist_Type
2045 , P_pa_ref_num1 => P_pa_ref_num1
2046 , P_pa_ref_num2 => P_pa_ref_num2
2047 , P_pa_ref_num3 => P_pa_ref_num3
2048 , P_pa_ref_num4 => P_pa_ref_num4
2049 , P_pa_ref_num5 => P_pa_ref_num5
2050 , P_pa_ref_num6 => P_pa_ref_num6
2051 , P_pa_ref_num7 => P_pa_ref_num7
2052 , P_pa_ref_num8 => P_pa_ref_num8
2053 , P_pa_ref_num9 => P_pa_ref_num9
2054 , P_pa_ref_num10 => P_pa_ref_num10
2055 , P_pa_ref_var1 => P_pa_ref_var1
2056 , P_pa_ref_var2 => P_pa_ref_var2
2057 , P_pa_ref_var3 => P_pa_ref_var3
2058 , P_pa_ref_var4 => P_pa_ref_var4
2059 , P_pa_ref_var5 => P_pa_ref_var5
2060 , P_pa_ref_var6 => P_pa_ref_var6
2061 , P_pa_ref_var7 => P_pa_ref_var7
2062 , P_pa_ref_var8 => P_pa_ref_var8
2063 , P_pa_ref_var9 => P_pa_ref_var9
2064 , P_pa_ref_var10 => P_pa_ref_var10 );
2065
2066 print_message('Stage:PATC:10.13.001');
2067 print_message('End of patcx.tc_extension api patcx_bill_flag :'||patcx_bill_flag||
2068 'Assignment :'||PATC.G_OVERIDE_ASSIGNMENT_ID||'patcx_status['||temp_status||']');
2069
2070 /*Start Bug4518893 */
2071
2072 /* IF Condition commented Start changes for bug#12597910 */
2073
2074 -- IF ( nvl(P_payroll_accrual_flag,'N') <> 'Y') THEN --- Condition Added for payroll billable_flag changes for 12.2 start bug#12344689
2075 --{
2076 IF ( patcx_bill_flag = 'N' OR patcx_bill_flag = 'Y' ) THEN
2077 X_billable_flag := patcx_bill_flag;
2078 ELSE
2079 X_billable_flag := temp_bill_flag;
2080 END IF;
2081 --}
2082 -- END IF; /* End of bug#12344689 changes */
2083 /*End Bug4518893 */
2084
2085
2086 /* Bug 7685120 Move the following code to the point where we have set the Global variables for work type in PATC
2087 IF ( temp_status IS NOT NULL ) THEN
2088
2089 If pa_trx_import.g_skip_tc_flag <> 'Y' then /* Added for Bug # 2108456
2090
2091 print_message('Stage:PATC:10.13.002');
2092 X_msg_type := temp_msg_type;
2093 X_msg_count := temp_msg_count;
2094 X_msg_token1 := temp_msg_token1;
2095 X_msg_token2 := temp_msg_token2;
2096 X_msg_token3 := temp_msg_token3;
2097 return_error( temp_status );
2098
2099 End If;
2100
2101 END IF;
2102
2103 CLOSE project_info;
2104 End of change for Bug 7685120 */
2105
2106 /* Start bug4518893
2107 IF ( patcx_bill_flag = 'N' OR patcx_bill_flag = 'Y' ) THEN
2108 X_billable_flag := patcx_bill_flag;
2109 ELSE
2110 X_billable_flag := temp_bill_flag;
2111 END IF;
2112 End bug4518893 */
2113
2114 /* Bug 2648550 starts */
2115 -- new work_type_id and tp_amt_type_code,assignment_name,work_type_name
2116 -- is derived using the new assignment_id all references to PATC to be changed
2117 -- to use the newly derived work_type_id,tp_amt_type_code,assignment_name,work_type_name
2118 --Bug 3017533, only if the asgn has changed from previous and assign_precedes_task is set
2119 --for all modules other than PAXTREPE then override the WT, TP amt type
2120
2121 /* Start changes for bug#12597910 */
2122
2123 l_temp_bill_flag := X_billable_flag;
2124 l_bill_labor_accrual_flag := proj.BILl_LABOR_ACCRUAL;
2125
2126 /* End changes for bug#12597910 */
2127
2128 If ( (nvl(PATC.G_OVERIDE_ASSIGNMENT_ID,0) <> nvl(L_BeforeCE_AsgnId,0)) and
2129 (proj.p_assign_precedes_task = 'Y') and
2130 (X_Calling_Module <> 'PAXTREPE') ) Then
2131
2132 PATC.G_OVERIDE_WORK_TYPE_ID := PA_UTILS4.get_work_type_id (
2133 p_project_id =>X_project_id
2134 , p_task_id =>l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
2135 , p_assignment_id=>nvl(PATC.G_OVERIDE_ASSIGNMENT_ID,0) );
2136
2137 PATC.G_OVERIDE_TP_AMT_TYPE_CODE := pa_utils4.get_tp_amt_type_code(
2138 p_work_type_id => PATC.G_OVERIDE_WORK_TYPE_ID );
2139
2140 PATC.G_OVERIDE_ASSIGNMENT_NAME := pa_utils4.get_assignment_name(
2141 p_assignment_id =>PATC.G_OVERIDE_ASSIGNMENT_ID);
2142
2143 PATC.G_OVERIDE_WORK_TYPE_NAME := pa_utils4.get_work_type_name(
2144 p_work_type_id => PATC.G_OVERIDE_WORK_TYPE_ID);
2145
2146 -- start of projcurrency and EI attrib changes
2147 -- Override the Billable flag if the work type biilability is enabled
2148 print_message('Stage:PATC:10.13.003');
2149
2150 /* IF Condition commented for bug#12597910 */
2151 -- IF ( nvl(P_payroll_accrual_flag,'N') <> 'Y') THEN --- Condition Added for payroll billable_flag changes for 12.2 start bug#12344689
2152 --{
2153
2154 X_billable_flag := PA_UTILS4.get_trxn_work_billabilty
2155 /* (p_work_type_id => p_work_type_id commented for bug 2648550 */
2156 /* Bug 2648550 used global variable in work_type_id parameter */
2157 (p_work_type_id => PATC.G_OVERIDE_WORK_TYPE_ID
2158 ,p_tc_extn_bill_flag => X_billable_flag);
2159 /* end of bug 2648550 */
2160 --}
2161 --END IF; /* End of bug#12344689 changes */
2162
2163 /* Start changes for bug#12597910 */
2164
2165
2166 IF ( nvl(P_payroll_accrual_flag,'N') = 'Y') THEN
2167 --{
2168
2169 IF (l_temp_bill_flag = 'Y' AND fnd_profile.value('PA_TRXN_BILLABLE_WORK_TYPE') = 'N') THEN
2170 --{
2171 X_billable_flag := nvl(l_bill_labor_accrual_flag,'N');
2172 --}
2173 END IF;
2174
2175 IF (l_temp_bill_flag = 'N' AND fnd_profile.value('PA_TRXN_BILLABLE_WORK_TYPE') = 'N') THEN
2176 --{
2177 X_billable_flag := 'N';
2178 --}
2179 END IF;
2180
2181 IF (l_temp_bill_flag = 'Y' AND fnd_profile.value('PA_TRXN_BILLABLE_WORK_TYPE') = 'Y') THEN
2182 --{
2183 IF (X_billable_flag = 'Y') THEN
2184 --{
2185 X_billable_flag := nvl(l_bill_labor_accrual_flag,'N');
2186 --}
2187 ELSE
2188 --{
2189 X_billable_flag := 'N';
2190 --}
2191 END IF;
2192 --}
2193 END IF;
2194
2195 --}
2196 END IF;
2197
2198 /* End changes for bug#12597910 */
2199
2200 Else
2201
2202 PATC.G_OVERIDE_WORK_TYPE_ID := p_work_type_id;
2203 PATC.G_OVERIDE_TP_AMT_TYPE_CODE := pa_utils4.get_tp_amt_type_code(
2204 p_work_type_id => p_work_type_id);
2205 PATC.G_OVERIDE_ASSIGNMENT_NAME := pa_utils4.get_assignment_name(
2206 p_assignment_id =>p_assignment_id);
2207 PATC.G_OVERIDE_WORK_TYPE_NAME := pa_utils4.get_work_type_name(
2208 p_work_type_id => p_work_type_id);
2209
2210 -- start of projcurrency and EI attrib changes
2211 -- Override the Billable flag if the work type biilability is enabled
2212 print_message('Stage:PATC:10.13.004');
2213
2214 /* IF Condition commented for bug#12597910 */
2215 --IF ( nvl(P_payroll_accrual_flag,'N') <> 'Y') THEN --- Condition Added for payroll billable_flag changes for 12.2 start bug#12344689
2216 --{
2217 X_billable_flag := PA_UTILS4.get_trxn_work_billabilty
2218 (p_work_type_id => p_work_type_id
2219 ,p_tc_extn_bill_flag => X_billable_flag);
2220 --}
2221 --END IF; /* End of bug#12344689 changes */
2222
2223 /* Start changes for bug#12597910 */
2224
2225
2226 IF ( nvl(P_payroll_accrual_flag,'N') = 'Y') THEN
2227 --{
2228
2229 IF (l_temp_bill_flag = 'Y' AND fnd_profile.value('PA_TRXN_BILLABLE_WORK_TYPE') = 'N') THEN
2230 --{
2231 X_billable_flag := nvl(l_bill_labor_accrual_flag,'N');
2232 --}
2233 END IF;
2234
2235 IF (l_temp_bill_flag = 'N' AND fnd_profile.value('PA_TRXN_BILLABLE_WORK_TYPE') = 'N') THEN
2236 --{
2237 X_billable_flag := 'N';
2238 --}
2239 END IF;
2240
2241 IF (l_temp_bill_flag = 'Y' AND fnd_profile.value('PA_TRXN_BILLABLE_WORK_TYPE') = 'Y') THEN
2242 --{
2243 IF (X_billable_flag = 'Y') THEN
2244 --{
2245 X_billable_flag := nvl(l_bill_labor_accrual_flag,'N');
2246 --}
2247 ELSE
2248 --{
2249 X_billable_flag := 'N';
2250 --}
2251 END IF;
2252 --}
2253 END IF;
2254
2255 --}
2256 END IF;
2257
2258 /* End changes for bug#12597910 */
2259
2260 End If; --Bug 3017533
2261 /*Start of changes for Bug 7685120 */
2262 IF ( temp_status IS NOT NULL ) THEN
2263
2264 If pa_trx_import.g_skip_tc_flag <> 'Y' then /* Added for Bug # 2108456 */
2265
2266 print_message('Stage:PATC:10.13.002');
2267 X_msg_type := temp_msg_type;
2268 X_msg_count := temp_msg_count;
2269 X_msg_token1 := temp_msg_token1;
2270 X_msg_token2 := temp_msg_token2;
2271 X_msg_token3 := temp_msg_token3;
2272 return_error( temp_status );
2273
2274 End If; /* Added for Bug # 2108456 */
2275
2276 END IF;
2277
2278 CLOSE project_info;
2279 /*End of change for Bug 7685120 */
2280
2281 -- end of projcurrency and EI attrib changes
2282
2283 print_message('Stage:PATC:10.13.005');
2284 print_message('Billable Flag after override:['||X_billable_flag||
2285 ']override assn:['||PATC.G_OVERIDE_ASSIGNMENT_ID||']x_status ['||x_status||']');
2286
2287 -- Begin PA.L Retirement Cost Processing changes
2288 print_message('Stage:PATC:10.14.00: Check capitalizable_flag against retirement_cost_flag');
2289 If pa_trx_import.g_skip_tc_flag <> 'Y' then
2290
2291 If proj.p_proj_typ_class_code = 'CAPITAL' and
2292 proj.t_ret_cost_flag = 'Y' and
2293 x_billable_flag = 'Y' then
2294
2295 -- The transaction cannot be capitalizable and a retirement cost at the same time.
2296 return_error('PA_TRX_CANT_BE_CAP');
2297
2298 End If;
2299
2300 End If;
2301 -- End PA.L Retirement Cost Processing changes
2302
2303 print_message('END of GET_STATUS API ');
2304
2305 Pa_Trx_Import.G_Skip_Tc_Flag := G_TRX_SKIP_FLAG;
2306 --}
2307 END IF;--Added for bug#13803367
2308 EXCEPTION
2309 WHEN INVALID_DATA THEN
2310 Pa_Trx_Import.G_Skip_Tc_Flag := G_TRX_SKIP_FLAG;
2311 NULL;
2312 WHEN OTHERS THEN
2313 Pa_Trx_Import.G_Skip_Tc_Flag := G_TRX_SKIP_FLAG;
2314 X_status := SQLCODE;
2315 X_billable_flag := NULL;
2316 print_message('Failed in GET_PATC api sqlerror:'||X_status);
2317
2318 END get_status;
2319
2320
2321 /* Added procedure check_termination for Bug#4604614 (BaseBug#4118885) */
2322
2323 procedure check_termination (p_person_id in per_all_people_f.person_id%type,
2324 p_ei_date in pa_expenditure_items_all.expenditure_item_date%type,
2325 x_actual_termination_date out nocopy per_periods_of_service.actual_termination_date%type) IS
2326
2327 cursor check_periods_of_service is
2328 select null
2329 from per_periods_of_service
2330 where person_id = p_person_id
2331 and p_ei_date between date_start and nvl(actual_termination_date, p_ei_date);
2332
2333 l_actual_termination_date per_periods_of_service.actual_termination_date%type := NULL;
2334 begin
2335
2336 open check_periods_of_service;
2337
2338 fetch check_periods_of_service into l_actual_termination_date;
2339 if check_periods_of_service%notfound then
2340 begin
2341 select actual_termination_date into l_actual_termination_date from (
2342 select actual_termination_date
2343 from per_periods_of_service
2344 where person_id = p_person_id
2345 and actual_termination_date < p_ei_date
2346 order by actual_termination_date desc)
2347 where rownum = 1;
2348 exception
2349 when no_data_found then
2350 l_actual_termination_date := NULL;
2351 end;
2352 end if;
2353 close check_periods_of_service;
2354
2355 x_actual_termination_date := l_actual_termination_date;
2356
2357 exception
2358 when others then
2359 raise;
2360
2361 end check_termination;
2362
2363 -- Bug 6156072: Base Bug 6045051: start
2364 -- Bug 6156072: Base Bug 6045051: new procedure added to check if EI Date falls between active service periods
2365 -- of the contingent worker. This is similar to procedure check_termination
2366 procedure check_termination_for_cwk (p_person_id in per_all_people_f.person_id%type,
2367 p_ei_date in pa_expenditure_items_all.expenditure_item_date%type,
2368 x_actual_termination_date out nocopy per_periods_of_placement.actual_termination_date%type) IS
2369
2370 cursor check_periods_of_service is
2371 select null
2372 from per_periods_of_placement
2373 where person_id = p_person_id
2374 and p_ei_date between date_start and nvl(actual_termination_date, p_ei_date);
2375
2376 l_actual_termination_date per_periods_of_placement.actual_termination_date%type := NULL;
2377 begin
2378
2379 open check_periods_of_service;
2380
2381 fetch check_periods_of_service into l_actual_termination_date;
2382 if check_periods_of_service%notfound then
2383 begin
2384 select actual_termination_date into l_actual_termination_date from (
2385 select actual_termination_date
2386 from per_periods_of_placement
2387 where person_id = p_person_id
2388 and actual_termination_date < p_ei_date
2389 order by actual_termination_date desc)
2390 where rownum = 1;
2391 exception
2392 when no_data_found then
2393 l_actual_termination_date := NULL;
2394 end;
2395 end if;
2396 close check_periods_of_service;
2397
2398 x_actual_termination_date := l_actual_termination_date;
2399
2400 exception
2401 when others then
2402 raise;
2403
2404 end check_termination_for_cwk;
2405 -- Bug 6156072: Base Bug 6045051: end
2406
2407 END PATC;
[Home] [Help]