1 PACKAGE BODY PER_PERSAIQE_XMLP_PKG AS
2 /* $Header: PERSAIQEB.pls 120.2 2011/05/19 11:52:45 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_1FORMULA RETURN NUMBER IS
72 BEGIN
73 CP_1 := 1;
74 RETURN (NULL);
75 END CF_1FORMULA;
76
77 FUNCTION CF_ORGFORMULA RETURN CHAR IS
78 L_NAME VARCHAR2(80);
79 BEGIN
80 SELECT
81 NAME
82 INTO
83 L_NAME
84 FROM
85 HR_ORGANIZATION_UNITS
86 WHERE ORGANIZATION_ID = P_ORG_ID;
87 RETURN (L_NAME);
88 EXCEPTION
89 WHEN OTHERS THEN
90 RETURN NULL;
91 END CF_ORGFORMULA;
92
93 FUNCTION CF_DURATION_UNITSFORMULA RETURN CHAR IS
94 L_UNIT VARCHAR2(80);
95 BEGIN
96 SELECT
97 MEANING
98 INTO
99 L_UNIT
100 FROM
101 HR_LOOKUPS
102 WHERE LOOKUP_TYPE = 'QUALIFYING_UNITS'
103 AND LOOKUP_CODE = P_EXP_UNITS;
104 RETURN (L_UNIT);
105 EXCEPTION
106 WHEN OTHERS THEN
107 RETURN (NULL);
108 END CF_DURATION_UNITSFORMULA;
109
110 FUNCTION AFTERPFORM RETURN BOOLEAN IS
111 L_PARENT_ID NUMBER;
112 L_ERR NUMBER := 0;
113 BEGIN
114 if p_date is null then
115 p_date := fnd_date.date_to_canonical(trunc(SYSDATE));
116 end if;
117 p_r_date := fnd_date.canonical_to_date(p_date);
118
119 p_date_disp := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_date), calendar_aware=>1); -- Bug#12547417
120
121 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
122 IF P_ORG_ID IS NULL THEN
123 BEGIN
124 SELECT
125 DISTINCT
126 POSE.ORGANIZATION_ID_PARENT
127 INTO
128 L_PARENT_ID
129 FROM
130 PER_ORG_STRUCTURE_ELEMENTS POSE
131 WHERE POSE.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
132 AND POSE.ORGANIZATION_ID_PARENT not in (
133 SELECT
134 POSE1.ORGANIZATION_ID_CHILD
135 FROM
136 PER_ORG_STRUCTURE_ELEMENTS POSE1
137 WHERE POSE1.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID );
138 EXCEPTION
139 WHEN OTHERS THEN
140 L_ERR := 1;
141 END;
142 END IF;
143 IF L_ERR = 0 THEN
144 L_ORG_CONDITION := ' and org.organization_id in ' || '(select to_char(pose.organization_id_child) '
145 || 'from per_org_structure_elements pose ' || 'connect by pose.organization_id_parent = '
146 || 'prior pose.organization_id_child ' || 'and pose.org_structure_version_id = ' || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' start with pose.organization_id_parent = ' || TO_CHAR(NVL(P_ORG_ID
147 ,L_PARENT_ID)) || ' and pose.org_structure_version_id = ' ||
148 TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union select ' || '''' ||
149 TO_CHAR(NVL(P_ORG_ID
150 ,L_PARENT_ID)) || '''' || ' from sys.dual) ';
151 ELSE
152 L_ORG_CONDITION := 'and 1 = 2';
153 END IF;
154 ELSIF P_ORG_ID IS NOT NULL THEN
155 L_ORG_CONDITION := ' and org.organization_id = :p_org_id';
156 ELSE
157 L_ORG_CONDITION := 'and 1 = 1';
158 END IF;
159 RETURN (TRUE);
160 END AFTERPFORM;
161
162 FUNCTION CF_VERSIONFORMULA RETURN NUMBER IS
163 L_NUMBER NUMBER;
164 BEGIN
165 SELECT
166 VERSION_NUMBER
167 INTO
168 L_NUMBER
169 FROM
170 PER_ORG_STRUCTURE_VERSIONS
171 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
172 RETURN (L_NUMBER);
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN NULL;
176 END CF_VERSIONFORMULA;
177
178 FUNCTION CF_HIERARCHYFORMULA RETURN CHAR IS
179 L_NAME VARCHAR2(80);
180 BEGIN
181 SELECT
182 NAME
183 INTO
184 L_NAME
185 FROM
186 PER_ORGANIZATION_STRUCTURES
187 WHERE ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID;
188 RETURN (L_NAME);
189 EXCEPTION
190 WHEN OTHERS THEN
191 RETURN NULL;
192 END CF_HIERARCHYFORMULA;
193
194 FUNCTION CP_1_P RETURN NUMBER IS
195 BEGIN
196 RETURN CP_1;
197 END CP_1_P;
198
199 END PER_PERSAIQE_XMLP_PKG;
[Home] [Help]