1 PACKAGE BODY PER_PERSADIS_XMLP_PKG AS
2 /* $Header: PERSADISB.pls 120.1 2011/05/19 11:49:58 abdash ship $ */
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
111 L_TYPE_CONDITION := 'and hr1.lookup_code = :p_disability_type';
108 IF P_DISABILITY_TYPE IS NULL THEN
109 L_TYPE_CONDITION := 'and 1 = 1';
110 ELSE
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
132 P_DATE_DISP := fnd_date.date_to_displaydate(P_DATE_T, calendar_aware=>1); -- Bug#12547417
133
134 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
135 IF P_ORG_ID IS NULL THEN
136 BEGIN
137 SELECT
138 DISTINCT
139 POSE.ORGANIZATION_ID_PARENT
140 INTO
141 L_PARENT_ID
142 FROM
143 PER_ORG_STRUCTURE_ELEMENTS POSE
144 WHERE POSE.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
145 AND POSE.ORGANIZATION_ID_PARENT not in (
146 SELECT
147 POSE1.ORGANIZATION_ID_CHILD
148 FROM
149 PER_ORG_STRUCTURE_ELEMENTS POSE1
150 WHERE POSE1.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID );
151 EXCEPTION
152 WHEN OTHERS THEN
153 L_ERR := 1;
154 END;
155 END IF;
156 IF L_ERR = 0 THEN
157 L_ORG_CONDITION := ' and org.organization_id in ' || '(select to_char(pose.organization_id_child) '
158 || 'from per_org_structure_elements pose ' || 'connect by pose.organization_id_parent = '
159 || 'prior pose.organization_id_child ' || 'and pose.org_structure_version_id = '
160 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' start with pose.organization_id_parent = '
161 || TO_CHAR(NVL(P_ORG_ID
162 ,L_PARENT_ID)) || ' and pose.org_structure_version_id = '
163 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union select '
164 || '''' || TO_CHAR(NVL(P_ORG_ID
165 ,L_PARENT_ID)) || '''' || ' from sys.dual) ';
166 ELSE
167 L_ORG_CONDITION := 'and 1 = 2';
168 END IF;
169 ELSIF P_ORG_ID IS NOT NULL THEN
170 L_ORG_CONDITION := ' and org.organization_id = :p_org_id';
171 ELSE
172 L_ORG_CONDITION := 'and 1 = 1';
173 END IF;
174 RETURN (TRUE);
175 END AFTERPFORM;
176
177 FUNCTION CF_EMP_OFFICEFORMULA RETURN CHAR IS
178 L_NAME VARCHAR2(80);
179 BEGIN
180 SELECT
181 NAME
182 INTO
183 L_NAME
184 FROM
185 HR_ORGANIZATION_UNITS
186 WHERE ORGANIZATION_ID = P_EMP_OFFICE;
187 RETURN (L_NAME);
188 EXCEPTION
189 WHEN OTHERS THEN
190 RETURN NULL;
191 END CF_EMP_OFFICEFORMULA;
192
193 FUNCTION CF_1FORMULA RETURN NUMBER IS
194 BEGIN
195 CP_1 := 1;
196 RETURN NULL;
197 END CF_1FORMULA;
198
199 FUNCTION CF_HIERARCHYFORMULA RETURN CHAR IS
200 L_NAME VARCHAR2(80);
201 BEGIN
202 SELECT
203 NAME
204 INTO
205 L_NAME
206 FROM
207 PER_ORGANIZATION_STRUCTURES
208 WHERE ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID;
209 RETURN (L_NAME);
210 EXCEPTION
211 WHEN OTHERS THEN
212 RETURN NULL;
213 END CF_HIERARCHYFORMULA;
214
215 FUNCTION CF_VERSIONFORMULA RETURN NUMBER IS
216 L_NUMBER NUMBER;
217 BEGIN
218 SELECT
219 VERSION_NUMBER
220 INTO
221 L_NUMBER
222 FROM
223 PER_ORG_STRUCTURE_VERSIONS
224 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
225 RETURN (L_NUMBER);
226 EXCEPTION
227 WHEN OTHERS THEN
228 RETURN NULL;
229 END CF_VERSIONFORMULA;
230
231 FUNCTION CF_DIS_STATUSFORMULA RETURN CHAR IS
232 L_TYPE VARCHAR2(80);
233 BEGIN
234 SELECT
235 MEANING
236 INTO
237 L_TYPE
238 FROM
239 HR_LOOKUPS
240 WHERE LOOKUP_TYPE = 'DISABILITY_STATUS'
241 AND LOOKUP_CODE = P_DISABILITY_STATUS;
242 RETURN (L_TYPE);
243 EXCEPTION
244 WHEN OTHERS THEN
245 RETURN (NULL);
246 END CF_DIS_STATUSFORMULA;
247
248 FUNCTION CP_1_P RETURN NUMBER IS
249 BEGIN
250 RETURN CP_1;
251 END CP_1_P;
252
253 END PER_PERSADIS_XMLP_PKG;
[Home] [Help]