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