[Home] [Help]
PACKAGE BODY: APPS.GHR_PD_COVERSHEET_DATA
Source
1 PACKAGE BODY ghr_pd_coversheet_data AS
2 /* $Header: ghrwspdc.pkb 115.2 1999/11/09 22:52:45 pkm ship $ */
3
4 FUNCTION
5 get_fr_position_group(p_position_id IN number,
6 p_info_type IN varchar2,
7 p_info_number IN number)
8 RETURN VARCHAR2
9 IS
10
11 CURSOR c_get_position_group
12 IS
13
14 SELECT DECODE(p_info_number,
15 3,poei_information3,
16 5,poei_information5,
17 12,poei_information12)
18 FROM per_position_extra_info
19 WHERE information_type = p_info_type
20 AND position_id = p_position_id;
21
22 l_position_group per_position_extra_info.poei_information1%TYPE;
23
24 BEGIN
25
26 OPEN c_get_position_group;
27 FETCH c_get_position_group INTO l_position_group;
28 IF c_get_position_group%NOTFOUND THEN
29 l_position_group := NULL;
30 END IF;
31 CLOSE c_get_position_group;
32
33 RETURN l_position_group;
34
35 END;
36
37
38 FUNCTION
39 get_gen_emp(p_pa_req_id IN number,
40 p_info_number IN number)
41 RETURN VARCHAR2
42 IS
43
44 CURSOR c_pdi_gen_emp
45 IS
46 SELECT decode(p_info_number,
47 3,rei_information3,
48 4,rei_information4,
49 5,rei_information5,
50 6,rei_information6)
51 FROM ghr_pa_request_extra_info
52 WHERE information_type = 'GHR_US_PD_GEN_EMP'
53 AND pa_request_id = p_pa_req_id;
54
55 l_gen_emp_return per_position_extra_info.poei_information1%TYPE;
56 BEGIN
57
58 OPEN c_pdi_gen_emp;
59 FETCH c_pdi_gen_emp INTO l_gen_emp_return;
60 IF c_pdi_gen_emp%NOTFOUND THEN
61 l_gen_emp_return := null;
62 END IF;
63 CLOSE c_pdi_gen_emp;
64 RETURN l_gen_emp_return;
65 END;
66
67
68 FUNCTION get_pay_plan(p_lookup_cd IN VARCHAR2)
69 RETURN VARCHAR2
70 IS
71 l_pay_plan VARCHAR2(80);
72 CURSOR c_pay_plan
73 IS
74 SELECT description
75 FROM GHR_PAY_PLANS
76 WHERE pay_plan = p_lookup_cd;
77
78 BEGIN
79 OPEN c_pay_plan;
80 FETCH c_pay_plan INTO l_pay_plan;
81 IF c_pay_plan%NOTFOUND THEN
82 l_pay_plan := null;
83 END IF;
84
85 RETURN l_pay_plan;
86 END;
87
88 FUNCTION flexfield( p_position_id IN NUMBER, p_structure IN VARCHAR2, p_segment IN VARCHAR2)
89 RETURN VARCHAR2
90 IS
91
92 CURSOR GET_SEGMENT(p_position_id NUMBER, p_structure VARCHAR2, p_segment VARCHAR2) IS
93 SELECT
94 DECODE(flex_seg.SEGMENT_NUM,
95 1, ppd.SEGMENT1, 2,ppd.SEGMENT2, 3, ppd.SEGMENT3,
96 4,ppd.SEGMENT4, 5,ppd.SEGMENT5,6,ppd.SEGMENT6,
97 7, ppd.SEGMENT7,
98 8,ppd.SEGMENT8, 9,ppd.SEGMENT9,10,ppd.SEGMENT10,
99 11, ppd.SEGMENT11,
100 12,ppd.SEGMENT12, 13,ppd.SEGMENT13,14,ppd.SEGMENT14, 15, ppd.SEGMENT15,
101 16,ppd.SEGMENT16, 17,ppd.SEGMENT17,18,ppd.SEGMENT18, 19, ppd.SEGMENT19,
102 20,ppd.SEGMENT20, 21,ppd.SEGMENT21,22,ppd.SEGMENT22,
103 23, ppd.SEGMENT23,
104 24,ppd.SEGMENT24, 25,ppd.SEGMENT25,26,ppd.SEGMENT26, 27, ppd.SEGMENT27,
105 28,ppd.SEGMENT28, 29,ppd.SEGMENT29,30,ppd.SEGMENT30)
106 FROM
107 PER_POSITION_DEFINITIONS PPD,
108 FND_ID_FLEX_STRUCTURES_TL flex_struct ,
109 FND_ID_FLEX_SEGMENTS flex_seg,
110 PER_POSITIONS pos
111 WHERE pos.POSITION_ID = p_position_id
112 AND ppd.POSITION_DEFINITION_ID = pos.POSITION_DEFINITION_ID
113 AND ppd.enabled_FLAG = 'Y'
114 AND NVL(ppd.START_DATE_ACTIVE,sysdate) <= sysdate
115 AND (ppd.END_DATE_ACTIVE IS NULL
116 OR ppd.END_DATE_ACTIVE > sysdate)
117 AND flex_struct.ID_FLEX_NUM = PPD.ID_FLEX_NUM
118 AND flex_struct.ID_FLEX_STRUCTURE_NAME = p_structure
119 AND flex_struct.LANGUAGE = 'US'
120 AND flex_seg.ID_FLEX_NUM = flex_struct.ID_FLEX_NUM
121 AND flex_seg.ID_FLEX_CODE = flex_struct.ID_FLEX_CODE
122 AND flex_seg.SEGMENT_NAME = p_segment;
123
124 l_flexfield VARCHAR2(30);
125
126 BEGIN
127 OPEN GET_SEGMENT(p_position_id, p_structure,p_segment );
128 FETCH GET_SEGMENT INTO l_flexfield;
129 IF GET_SEGMENT%NOTFOUND THEN
130 l_flexfield := NULL;
131 END IF;
132 CLOSE GET_SEGMENT;
133 RETURN l_flexfield;
134 END;
135 END ghr_pd_coversheet_data;