PACKAGE BODY: APPS.HRI_BPL_ASG_STATUS_TYPES

Source

1 PACKAGE BODY HRI_BPL_ASG_STATUS_TYPES AS
2  /* $Header: hribastp.pkb 115.1 2004/06/03 06:09 prasharm noship $ */
3 --
4 /**************************************************************************
5  *Description: This function gets called from the function
6  *            HRI_OLTP_VIEW_ASG_STATUS_TYPES.get_asg_user_status
7  **************************************************************************/
8   FUNCTION get_asg_user_status
9     (p_asg_type VARCHAR2,
10      p_current_employee_flag VARCHAR2,
11      p_user_status VARCHAR2
12     )
13   RETURN VARCHAR2
14   IS
15     --
16     cursor c_user_status (c_per_system_status varchar2) is
17     SELECT
18 	DECODE(y1.per_system_status,
19 	        'TERM_APL',
20 	        y1T.user_status,
21 	        DECODE(p_current_employee_flag ,
22 	               'Y',
23 	               p_user_status,
24 	               x1T.user_status
25 	        )
26 	) appl_user_status,
27 	DECODE(y1.per_system_status,
28 		'TERM_ASSIGN',
29 		y1T.user_status,
30 		x1T.user_status
31 	) emp_user_status
32     FROM
33 	per_ass_status_type_amends_tl y1T ,
34 	 (select *
35 	  from   per_ass_status_type_amends
36 	  where  business_group_id = hr_bis.get_sec_profile_bg_id) y1,
37 	per_assignment_status_types_tl x1T,
38 	per_assignment_status_types x1
39     WHERE
40 	x1.per_system_status = c_per_system_status AND
41 	x1.default_flag = 'Y' AND
42 	x1.business_group_id is null AND
43 	x1.assignment_status_type_id = x1T.assignment_status_type_id AND
44 	x1T.language = userenv('LANG') AND
45 	x1.assignment_status_type_id = y1.assignment_status_type_id(+) AND
46 	y1.ass_status_type_amend_id = y1T.ass_status_type_amend_id(+) AND
47 	y1T.language (+) = userenv('LANG');
48     --
49     l_record c_user_status%ROWTYPE;
50     --
51   BEGIN
52     --
53     IF (p_asg_type = 'A') THEN
54     --
55       open c_user_status ('TERM_APL');
56       fetch c_user_status into l_record;
57       close c_user_status;
58     --
59       return l_record.appl_user_status;
60     --
61     ELSIF (p_asg_type = 'E') THEN
62     --
63       open c_user_status ('TERM_ASSIGN');
64       fetch c_user_status into l_record;
65       close c_user_status;
66     --
67       return l_record.emp_user_status;
68     --
69     END IF;
70     --
71   END get_asg_user_status;
72   --
73 END HRI_BPL_ASG_STATUS_TYPES;