1 PACKAGE BODY PER_PERSAIPE_XMLP_PKG AS
2 /* $Header: PERSAIPEB.pls 120.1 2008/03/13 06:22:00 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_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 --p_r_date:=p_date;
119
120
121
122 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
123 IF P_ORG_ID IS NULL THEN
124 BEGIN
125 SELECT
126 DISTINCT
127 POSE.ORGANIZATION_ID_PARENT
128 INTO
129 L_PARENT_ID
130 FROM
131 PER_ORG_STRUCTURE_ELEMENTS POSE
132 WHERE POSE.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
133 AND POSE.ORGANIZATION_ID_PARENT not in (
134 SELECT
135 POSE1.ORGANIZATION_ID_CHILD
136 FROM
137 PER_ORG_STRUCTURE_ELEMENTS POSE1
138 WHERE POSE1.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID );
139 EXCEPTION
140 WHEN OTHERS THEN
141 L_ERR := 1;
142 END;
143 END IF;
144 IF L_ERR = 0 THEN
145 L_ORG_CONDITION := ' and org.organization_id in ' || '(select to_char(pose.organization_id_child) '
146 || 'from per_org_structure_elements pose ' || 'connect by pose.organization_id_parent = '
147 || '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
148 ,L_PARENT_ID)) || ' and pose.org_structure_version_id = '
149 || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union select '
150 || '''' || TO_CHAR(NVL(P_ORG_ID
151 ,L_PARENT_ID)) || '''' || ' from sys.dual) ';
152 ELSE
153 L_ORG_CONDITION := 'and 1 = 2';
154 END IF;
155 ELSIF P_ORG_ID IS NOT NULL THEN
156 L_ORG_CONDITION := ' and org.organization_id = :p_org_id';
157 ELSE
158 L_ORG_CONDITION := 'and 1 = 1';
159 END IF;
160 RETURN (TRUE);
161 END AFTERPFORM;
162
163 FUNCTION CF_VERSIONFORMULA RETURN NUMBER IS
164 L_NUMBER NUMBER;
165 BEGIN
166 SELECT
167 VERSION_NUMBER
168 INTO
169 L_NUMBER
170 FROM
171 PER_ORG_STRUCTURE_VERSIONS
172 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
173 RETURN (L_NUMBER);
174 EXCEPTION
175 WHEN OTHERS THEN
176 RETURN NULL;
177 END CF_VERSIONFORMULA;
178
179 FUNCTION CF_HIERARCHYFORMULA RETURN CHAR IS
180 L_NAME VARCHAR2(80);
181 BEGIN
182 SELECT
183 NAME
184 INTO
185 L_NAME
186 FROM
187 PER_ORGANIZATION_STRUCTURES
188 WHERE ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID;
189 RETURN (L_NAME);
190 EXCEPTION
191 WHEN OTHERS THEN
192 RETURN NULL;
193 END CF_HIERARCHYFORMULA;
194
195 FUNCTION CP_1_P RETURN NUMBER IS
196 BEGIN
197 RETURN CP_1;
198 END CP_1_P;
199
200 END PER_PERSAIPE_XMLP_PKG;
[Home] [Help]