1 PACKAGE BODY PER_PERSACTR_XMLP_PKG AS
2 /* $Header: PERSACTRB.pls 120.3 2011/05/19 11:50:05 abdash ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4
5 x boolean ;
6 BEGIN
7 x:= P_BUSINESS_GROUP_IDVALIDTRIGGE;
8 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
9 INSERT INTO FND_SESSIONS
10 (SESSION_ID
11 ,EFFECTIVE_DATE)
12 VALUES (USERENV('SESSIONID')
13 ,NVL(LP_DATE
14 ,SYSDATE));
15 RETURN (TRUE);
16 END BEFOREREPORT;
17
18 FUNCTION AFTERREPORT RETURN BOOLEAN IS
19 BEGIN
20 DELETE FROM FND_SESSIONS
21 WHERE SESSION_ID = USERENV('SESSIONID');
22 RETURN (TRUE);
23 END AFTERREPORT;
24
25 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
26 V_BUSINESS_GROUP HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
27 BEGIN
28 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
29 RETURN V_BUSINESS_GROUP;
30 END CF_BUSINESS_GROUPFORMULA;
31
32 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
33 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
34 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
35 SELECT
36 ORG_INFORMATION9
37 FROM
38 HR_ORGANIZATION_INFORMATION
39 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
40 AND ORG_INFORMATION9 is not null
41 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
42 BEGIN
43 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID);
44 FETCH LEGISLATION_CODE
45 INTO
46 V_LEGISLATION_CODE;
47 CLOSE LEGISLATION_CODE;
48 RETURN V_LEGISLATION_CODE;
49 END CF_LEGISLATION_CODEFORMULA;
50
51 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
52 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
53 V_FORMAT_MASK VARCHAR2(100) := NULL;
54 V_FIELD_LENGTH NUMBER(3) := 14;
55 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
56 SELECT
57 CURRENCY_CODE
58 FROM
59 FND_CURRENCIES
60 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
61 BEGIN
62 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
63 FETCH CURRENCY_FORMAT_MASK
64 INTO
65 V_CURRENCY_CODE;
66 CLOSE CURRENCY_FORMAT_MASK;
67 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
68 ,V_FIELD_LENGTH);
69 RETURN V_FORMAT_MASK;
70 END CF_CURRENCY_FORMAT_MASKFORMULA;
71
72 PROCEDURE SET_CURRENCY_FORMAT_MASK IS
73 BEGIN
74 NULL;
75 END SET_CURRENCY_FORMAT_MASK;
76
77 FUNCTION P_BUSINESS_GROUP_IDVALIDTRIGGE RETURN BOOLEAN IS
78 BEGIN
79 RETURN (TRUE);
80 END P_BUSINESS_GROUP_IDVALIDTRIGGE;
81
82 FUNCTION CF_1FORMULA RETURN NUMBER IS
83 BEGIN
84 CP_1 := 1;
85 RETURN (NULL);
86 END CF_1FORMULA;
87
88 FUNCTION CF_ORGFORMULA RETURN CHAR IS
89 L_NAME VARCHAR2(80);
90 BEGIN
91 SELECT
92 NAME
93 INTO
94 L_NAME
95 FROM
96 HR_ORGANIZATION_UNITS
97 WHERE ORGANIZATION_ID = P_ORG_ID;
98 RETURN (L_NAME);
99 EXCEPTION
100 WHEN OTHERS THEN
101 RETURN NULL;
102 END CF_ORGFORMULA;
103
104 FUNCTION CF_CON_TYPEFORMULA RETURN CHAR IS
105 L_TYPE VARCHAR2(80);
106 BEGIN
107 SELECT
108 MEANING
109 INTO
110 L_TYPE
111 FROM
112 HR_LOOKUPS
113 WHERE LOOKUP_TYPE = 'CONTRACT_TYPE'
114 AND LOOKUP_CODE = P_CON_TYPE;
115 RETURN (L_TYPE);
116 EXCEPTION
117 WHEN OTHERS THEN
118 RETURN (NULL);
119 END CF_CON_TYPEFORMULA;
120
121 FUNCTION CF_DURATION_UNITSFORMULA RETURN CHAR IS
122 L_UNIT VARCHAR2(80);
123 BEGIN
124 SELECT
125 MEANING
126 INTO
127 L_UNIT
128 FROM
129 HR_LOOKUPS
130 WHERE LOOKUP_TYPE = 'QUALIFYING_UNITS'
131 AND LOOKUP_CODE = P_EXP_UNITS;
132 RETURN (L_UNIT);
133 EXCEPTION
134 WHEN OTHERS THEN
135 RETURN (NULL);
136 END CF_DURATION_UNITSFORMULA;
137
138 FUNCTION AFTERPFORM RETURN BOOLEAN IS
139 L_PARENT_ID NUMBER;
140 L_ERR NUMBER := 0;
141 BEGIN
142 L_TYPE_CONDITION := ' and hr1.lookup_code = DECODE(:p_con_type,NULL,hr1.lookup_code,:p_con_type) ';
143 L_STATUS_CONDITION := ' and hr2.lookup_code = DECODE(:p_emp_status,NULL,hr2.lookup_code,:p_emp_status) ';
144 IF P_DATE IS NULL THEN
145 LP_DATE := TRUNC(SYSDATE);
146 ELSE
147 LP_DATE:=to_date(P_DATE,'YYYY/MM/DD HH24:MI:SS');
148 END IF;
149
150 --LP_DATE:=to_date(substr(P_DATE,1,10),'DD-MON-YYYY');
151
152 P_DATE_DISP := fnd_date.date_to_displaydate(LP_DATE, calendar_aware=>1); -- Bug#12547417
153
154 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
155 IF P_ORG_ID IS NULL THEN
156 BEGIN
157 SELECT
158 DISTINCT
159 POSE.ORGANIZATION_ID_PARENT
160 INTO
161 L_PARENT_ID
162 FROM
163 PER_ORG_STRUCTURE_ELEMENTS POSE
164 WHERE POSE.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
165 AND POSE.ORGANIZATION_ID_PARENT not in (
166 SELECT
167 POSE1.ORGANIZATION_ID_CHILD
168 FROM
169 PER_ORG_STRUCTURE_ELEMENTS POSE1
170 WHERE POSE1.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID );
171 EXCEPTION
172 WHEN OTHERS THEN
173 L_ERR := 1;
174 END;
175 END IF;
176 IF L_ERR = 0 THEN
177 L_ORG_CONDITION := ' and org.organization_id in ' || '(select to_char(pose.organization_id_child) '
178 || 'from per_org_structure_elements pose ' || 'connect by pose.organization_id_parent = '
179 || 'prior pose.organization_id_child ' || 'and pose.org_structure_version_id = '
180 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' start with pose.organization_id_parent = '
181 || TO_CHAR(NVL(P_ORG_ID
182 ,L_PARENT_ID)) || ' and pose.org_structure_version_id = '
183 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union select '
184 || '''' || TO_CHAR(NVL(P_ORG_ID
185 ,L_PARENT_ID)) || '''' || ' from sys.dual) ';
186 ELSE
187 L_ORG_CONDITION := 'and 1 = 2';
188 END IF;
189 ELSIF P_ORG_ID IS NOT NULL THEN
190 L_ORG_CONDITION := ' and org.organization_id in ' || '(select ' || '''' || TO_CHAR(P_ORG_ID) || '''' || ' from sys.dual)';
191 ELSE
192 L_ORG_CONDITION := 'and 1 = 1';
193 END IF;
194 RETURN (TRUE);
195 END AFTERPFORM;
196
197 FUNCTION CF_EMP_STATUSFORMULA RETURN CHAR IS
198 L_STATUS VARCHAR2(80);
199 BEGIN
200 SELECT
201 MEANING
202 INTO
203 L_STATUS
204 FROM
205 HR_LOOKUPS
206 WHERE LOOKUP_TYPE = 'SA_EMPLOYMENT_STATUS'
207 AND LOOKUP_CODE = P_EMP_STATUS;
208 RETURN (L_STATUS);
209 EXCEPTION
210 WHEN OTHERS THEN
211 RETURN (NULL);
212 END CF_EMP_STATUSFORMULA;
213
214 FUNCTION CF_VERSIONFORMULA RETURN NUMBER IS
215 L_NUMBER NUMBER;
216 BEGIN
217 SELECT
218 VERSION_NUMBER
219 INTO
220 L_NUMBER
221 FROM
222 PER_ORG_STRUCTURE_VERSIONS
223 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
224 RETURN (L_NUMBER);
225 EXCEPTION
226 WHEN OTHERS THEN
227 RETURN NULL;
228 END CF_VERSIONFORMULA;
229
230 FUNCTION CF_HIERARCHYFORMULA RETURN CHAR IS
231 L_NAME VARCHAR2(80);
232 BEGIN
233 SELECT
234 NAME
235 INTO
236 L_NAME
237 FROM
238 PER_ORGANIZATION_STRUCTURES
239 WHERE ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID;
240 RETURN (L_NAME);
241 EXCEPTION
242 WHEN OTHERS THEN
243 RETURN NULL;
244 END CF_HIERARCHYFORMULA;
245
246 FUNCTION CP_1_P RETURN NUMBER IS
247 BEGIN
248 RETURN CP_1;
249 END CP_1_P;
250
251 END PER_PERSACTR_XMLP_PKG;
[Home] [Help]