1 PACKAGE BODY PER_PERSACTR_XMLP_PKG AS
2 /* $Header: PERSACTRB.pls 120.2 2008/03/11 10:23:38 vjaganat noship $ */
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 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
153 IF P_ORG_ID IS NULL THEN
154 BEGIN
155 SELECT
156 DISTINCT
157 POSE.ORGANIZATION_ID_PARENT
158 INTO
159 L_PARENT_ID
160 FROM
161 PER_ORG_STRUCTURE_ELEMENTS POSE
162 WHERE POSE.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
163 AND POSE.ORGANIZATION_ID_PARENT not in (
164 SELECT
165 POSE1.ORGANIZATION_ID_CHILD
166 FROM
167 PER_ORG_STRUCTURE_ELEMENTS POSE1
168 WHERE POSE1.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID );
169 EXCEPTION
170 WHEN OTHERS THEN
171 L_ERR := 1;
172 END;
173 END IF;
174 IF L_ERR = 0 THEN
175 L_ORG_CONDITION := ' and org.organization_id in ' || '(select to_char(pose.organization_id_child) '
176 || 'from per_org_structure_elements pose ' || 'connect by pose.organization_id_parent = '
177 || 'prior pose.organization_id_child ' || 'and pose.org_structure_version_id = '
178 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' start with pose.organization_id_parent = '
179 || TO_CHAR(NVL(P_ORG_ID
180 ,L_PARENT_ID)) || ' and pose.org_structure_version_id = '
181 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union select '
182 || '''' || TO_CHAR(NVL(P_ORG_ID
183 ,L_PARENT_ID)) || '''' || ' from sys.dual) ';
184 ELSE
185 L_ORG_CONDITION := 'and 1 = 2';
186 END IF;
187 ELSIF P_ORG_ID IS NOT NULL THEN
188 L_ORG_CONDITION := ' and org.organization_id in ' || '(select ' || '''' || TO_CHAR(P_ORG_ID) || '''' || ' from sys.dual)';
189 ELSE
190 L_ORG_CONDITION := 'and 1 = 1';
191 END IF;
192 RETURN (TRUE);
193 END AFTERPFORM;
194
195 FUNCTION CF_EMP_STATUSFORMULA RETURN CHAR IS
196 L_STATUS VARCHAR2(80);
197 BEGIN
198 SELECT
199 MEANING
200 INTO
201 L_STATUS
202 FROM
203 HR_LOOKUPS
204 WHERE LOOKUP_TYPE = 'SA_EMPLOYMENT_STATUS'
205 AND LOOKUP_CODE = P_EMP_STATUS;
206 RETURN (L_STATUS);
207 EXCEPTION
208 WHEN OTHERS THEN
209 RETURN (NULL);
210 END CF_EMP_STATUSFORMULA;
211
212 FUNCTION CF_VERSIONFORMULA RETURN NUMBER IS
213 L_NUMBER NUMBER;
214 BEGIN
215 SELECT
216 VERSION_NUMBER
217 INTO
218 L_NUMBER
219 FROM
220 PER_ORG_STRUCTURE_VERSIONS
221 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
222 RETURN (L_NUMBER);
223 EXCEPTION
224 WHEN OTHERS THEN
225 RETURN NULL;
226 END CF_VERSIONFORMULA;
227
228 FUNCTION CF_HIERARCHYFORMULA RETURN CHAR IS
229 L_NAME VARCHAR2(80);
230 BEGIN
231 SELECT
232 NAME
233 INTO
234 L_NAME
235 FROM
236 PER_ORGANIZATION_STRUCTURES
237 WHERE ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID;
238 RETURN (L_NAME);
239 EXCEPTION
240 WHEN OTHERS THEN
241 RETURN NULL;
242 END CF_HIERARCHYFORMULA;
243
244 FUNCTION CP_1_P RETURN NUMBER IS
245 BEGIN
246 RETURN CP_1;
247 END CP_1_P;
248
249 END PER_PERSACTR_XMLP_PKG;
[Home] [Help]