1 PACKAGE BODY PER_PERSADIS_XMLP_PKG AS
2 /* $Header: PERSADISB.pls 120.0 2007/12/24 13:20:42 amakrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 RETURN (TRUE);
7 END BEFOREREPORT;
8
9 FUNCTION AFTERREPORT RETURN BOOLEAN IS
10 BEGIN
11 RETURN (TRUE);
12 END AFTERREPORT;
13
14 FUNCTION CF_BUSINESS_GROUPFORMULA RETURN VARCHAR2 IS
15 V_BUSINESS_GROUP HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
16 BEGIN
17 V_BUSINESS_GROUP := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
18 RETURN V_BUSINESS_GROUP;
19 END CF_BUSINESS_GROUPFORMULA;
20
21 FUNCTION CF_LEGISLATION_CODEFORMULA RETURN VARCHAR2 IS
22 V_LEGISLATION_CODE HR_ORGANIZATION_INFORMATION.ORG_INFORMATION9%TYPE := NULL;
23 CURSOR LEGISLATION_CODE(C_BUSINESS_GROUP_ID IN HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE) IS
24 SELECT
25 ORG_INFORMATION9
26 FROM
27 HR_ORGANIZATION_INFORMATION
28 WHERE ORGANIZATION_ID = C_BUSINESS_GROUP_ID
29 AND ORG_INFORMATION9 is not null
30 AND ORG_INFORMATION_CONTEXT = 'Business Group Information';
31 BEGIN
32 OPEN LEGISLATION_CODE(P_BUSINESS_GROUP_ID);
33 FETCH LEGISLATION_CODE
34 INTO
35 V_LEGISLATION_CODE;
36 CLOSE LEGISLATION_CODE;
37 RETURN V_LEGISLATION_CODE;
38 END CF_LEGISLATION_CODEFORMULA;
39
40 FUNCTION CF_CURRENCY_FORMAT_MASKFORMULA(CF_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
41 V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
42 V_FORMAT_MASK VARCHAR2(100) := NULL;
43 V_FIELD_LENGTH NUMBER(3) := 14;
44 CURSOR CURRENCY_FORMAT_MASK(C_TERRITORY_CODE IN FND_CURRENCIES.ISSUING_TERRITORY_CODE%TYPE) IS
45 SELECT
46 CURRENCY_CODE
47 FROM
48 FND_CURRENCIES
49 WHERE ISSUING_TERRITORY_CODE = C_TERRITORY_CODE;
50 BEGIN
51 OPEN CURRENCY_FORMAT_MASK(CF_LEGISLATION_CODE);
52 FETCH CURRENCY_FORMAT_MASK
53 INTO
54 V_CURRENCY_CODE;
55 CLOSE CURRENCY_FORMAT_MASK;
56 V_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(V_CURRENCY_CODE
57 ,V_FIELD_LENGTH);
58 RETURN V_FORMAT_MASK;
59 END CF_CURRENCY_FORMAT_MASKFORMULA;
60
61 PROCEDURE SET_CURRENCY_FORMAT_MASK IS
62 BEGIN
63 NULL;
64 END SET_CURRENCY_FORMAT_MASK;
65
66 FUNCTION P_BUSINESS_GROUP_IDVALIDTRIGGE RETURN BOOLEAN IS
67 BEGIN
68 RETURN (TRUE);
69 END P_BUSINESS_GROUP_IDVALIDTRIGGE;
70
71 FUNCTION CF_ORGFORMULA RETURN CHAR IS
72 L_NAME VARCHAR2(80);
73 BEGIN
74 SELECT
75 NAME
76 INTO
77 L_NAME
78 FROM
79 HR_ORGANIZATION_UNITS
80 WHERE ORGANIZATION_ID = P_ORG_ID;
81 RETURN (L_NAME);
82 EXCEPTION
83 WHEN OTHERS THEN
84 RETURN NULL;
85 END CF_ORGFORMULA;
86
87 FUNCTION CF_DIS_TYPEFORMULA RETURN CHAR IS
88 L_TYPE VARCHAR2(80);
89 BEGIN
90 SELECT
91 MEANING
92 INTO
93 L_TYPE
94 FROM
95 HR_LOOKUPS
96 WHERE LOOKUP_TYPE = 'DISABILITY_CATEGORY'
97 AND LOOKUP_CODE = P_DISABILITY_TYPE;
98 RETURN (L_TYPE);
99 EXCEPTION
100 WHEN OTHERS THEN
101 RETURN (NULL);
102 END CF_DIS_TYPEFORMULA;
103
104 FUNCTION AFTERPFORM RETURN BOOLEAN IS
105 L_PARENT_ID NUMBER;
106 L_ERR NUMBER := 0;
107 BEGIN
108 IF P_DISABILITY_TYPE IS NULL THEN
109 L_TYPE_CONDITION := 'and 1 = 1';
110 ELSE
111 L_TYPE_CONDITION := 'and hr1.lookup_code = :p_disability_type';
112 END IF;
113 IF P_DISABILITY_STATUS IS NULL THEN
114 L_STATUS_CONDITION := 'and 1 = 1';
115 ELSE
116 L_STATUS_CONDITION := 'and hr3.lookup_code = :p_disability_status';
117 END IF;
118 IF P_EMP_OFFICE IS NULL THEN
119 L_EMP_OFFICE_CONDITION := 'and 1 = 1';
120 ELSE
121 L_EMP_OFFICE_CONDITION := 'and disb.dis_information1 = :p_emp_office';
122 END IF;
123 /*IF P_DATE IS NULL THEN
124 P_DATE := TRUNC(SYSDATE);
125 END IF;*/
126 IF P_DATE IS NULL THEN
127 P_DATE_T := TRUNC(SYSDATE);
128 else
129 P_DATE_T := P_DATE;
130 END IF;
131 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
132 IF P_ORG_ID IS NULL THEN
133 BEGIN
134 SELECT
135 DISTINCT
136 POSE.ORGANIZATION_ID_PARENT
137 INTO
138 L_PARENT_ID
139 FROM
140 PER_ORG_STRUCTURE_ELEMENTS POSE
141 WHERE POSE.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
142 AND POSE.ORGANIZATION_ID_PARENT not in (
143 SELECT
144 POSE1.ORGANIZATION_ID_CHILD
145 FROM
146 PER_ORG_STRUCTURE_ELEMENTS POSE1
147 WHERE POSE1.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID );
148 EXCEPTION
149 WHEN OTHERS THEN
150 L_ERR := 1;
151 END;
152 END IF;
153 IF L_ERR = 0 THEN
154 L_ORG_CONDITION := ' and org.organization_id in ' || '(select to_char(pose.organization_id_child) '
155 || 'from per_org_structure_elements pose ' || 'connect by pose.organization_id_parent = '
156 || 'prior pose.organization_id_child ' || 'and pose.org_structure_version_id = '
157 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' start with pose.organization_id_parent = '
158 || TO_CHAR(NVL(P_ORG_ID
159 ,L_PARENT_ID)) || ' and pose.org_structure_version_id = '
160 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union select '
161 || '''' || TO_CHAR(NVL(P_ORG_ID
162 ,L_PARENT_ID)) || '''' || ' from sys.dual) ';
163 ELSE
164 L_ORG_CONDITION := 'and 1 = 2';
165 END IF;
166 ELSIF P_ORG_ID IS NOT NULL THEN
167 L_ORG_CONDITION := ' and org.organization_id = :p_org_id';
168 ELSE
169 L_ORG_CONDITION := 'and 1 = 1';
170 END IF;
171 RETURN (TRUE);
172 END AFTERPFORM;
173
174 FUNCTION CF_EMP_OFFICEFORMULA RETURN CHAR IS
175 L_NAME VARCHAR2(80);
176 BEGIN
177 SELECT
178 NAME
179 INTO
180 L_NAME
181 FROM
182 HR_ORGANIZATION_UNITS
183 WHERE ORGANIZATION_ID = P_EMP_OFFICE;
184 RETURN (L_NAME);
185 EXCEPTION
186 WHEN OTHERS THEN
187 RETURN NULL;
188 END CF_EMP_OFFICEFORMULA;
189
190 FUNCTION CF_1FORMULA RETURN NUMBER IS
191 BEGIN
192 CP_1 := 1;
193 RETURN NULL;
194 END CF_1FORMULA;
195
196 FUNCTION CF_HIERARCHYFORMULA RETURN CHAR IS
197 L_NAME VARCHAR2(80);
198 BEGIN
199 SELECT
200 NAME
201 INTO
202 L_NAME
203 FROM
204 PER_ORGANIZATION_STRUCTURES
205 WHERE ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID;
206 RETURN (L_NAME);
207 EXCEPTION
208 WHEN OTHERS THEN
209 RETURN NULL;
210 END CF_HIERARCHYFORMULA;
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_DIS_STATUSFORMULA RETURN CHAR IS
229 L_TYPE VARCHAR2(80);
230 BEGIN
231 SELECT
232 MEANING
233 INTO
234 L_TYPE
235 FROM
236 HR_LOOKUPS
237 WHERE LOOKUP_TYPE = 'DISABILITY_STATUS'
238 AND LOOKUP_CODE = P_DISABILITY_STATUS;
239 RETURN (L_TYPE);
240 EXCEPTION
241 WHEN OTHERS THEN
242 RETURN (NULL);
243 END CF_DIS_STATUSFORMULA;
244
245 FUNCTION CP_1_P RETURN NUMBER IS
246 BEGIN
247 RETURN CP_1;
248 END CP_1_P;
249
250 END PER_PERSADIS_XMLP_PKG;
[Home] [Help]