1 PACKAGE BODY PA_PARCPRJR_XMLP_PKG AS
2 /* $Header: PARCPRJRB.pls 120.1 2008/04/09 11:59:56 krreddy noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 L_CP_STATUS BOOLEAN;
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 IF (P_RET_STATUS = 'Y') THEN
8 L_CP_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING'
9 ,'');
10 END IF;
11 RETURN (TRUE);
12 END AFTERREPORT;
13
14 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
15 L_RETURN_STATUS VARCHAR2(1);
16 L_MSG_COUNT NUMBER;
17 L_MSG_DATA VARCHAR2(10000);
18 L_DATE DATE;
19 L_API_VERSION NUMBER := 1.0;
20 L_RESOURCE_ID NUMBER(15);
21 L_EXCEPTION EXCEPTION;
22 BEGIN
23 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
24
25 P_TERM_RANGE_DATE1:=to_char(P_TERM_RANGE_DATE,'DD-MON-YY');
26 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
27 IF P_SELECTION_OPTION = 'EMP_RANGE' THEN
28 IF (P_FROM_EMP_NUM IS NULL OR P_TO_EMP_NUM IS NULL) THEN
29 CP_CHK_EMP_RANGE := 'N';
30 RETURN (TRUE);
31 END IF;
32 ELSIF P_SELECTION_OPTION = 'EMP_RANGE_ORG' THEN
33 IF (P_ORGANIZATION_ID IS NULL OR P_FROM_EMP_NUM IS NULL OR P_TO_EMP_NUM IS NULL) THEN
34 CP_CHK_ORG_AND_EMP_RANGE := 'N';
35 RETURN (TRUE);
36 END IF;
37 ELSIF P_SELECTION_OPTION = 'SINGLE_ORG' THEN
38 IF (P_ORGANIZATION_ID IS NULL) THEN
39 CP_CHK_SINGLE_ORG := 'N';
40 RETURN (TRUE);
41 END IF;
42 ELSIF P_SELECTION_OPTION = 'START_ORG' THEN
43 IF (P_ORG_STR_VERSION_ID IS NULL OR P_START_ORGANIZATION_ID IS NULL) THEN
44 CP_CHK_START_ORG := 'N';
45 RETURN (TRUE);
46 END IF;
47 END IF;
48 P_DEBUG_MODE := FND_PROFILE.VALUE('PA_DEBUG_MODE');
49 BEGIN
50 SELECT
51 SYSDATE
52 INTO L_DATE
53 FROM
54 DUAL;
55 EXCEPTION
56 WHEN NO_DATA_FOUND THEN
57 NULL;
58 WHEN OTHERS THEN
59 /*SRW.MESSAGE(100
60 ,' OTHER PROBLEM IN SELECTING THE SYSDATE ' || SQLERRM)*/NULL;
61 END;
62 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE(P_API_VERSION => L_API_VERSION
63 ,P_FROM_EMP_NUM => P_FROM_EMP_NUM
64 ,P_TO_EMP_NUM => P_TO_EMP_NUM
65 ,P_ORGANIZATION_ID => P_ORGANIZATION_ID
66 ,P_REFRESH => P_REFRESH
67 ,P_PULL_TERM_RES => P_PULL_TERM_RES
68 ,P_TERM_RANGE_DATE => P_TERM_RANGE_DATE
69 ,P_PERSON_TYPE => P_PERSON_TYPE
70 ,P_SELECTION_OPTION => P_SELECTION_OPTION
71 ,P_ORG_STR_VERSION_ID => P_ORG_STR_VERSION_ID
72 ,P_START_ORGANIZATION_ID => P_START_ORGANIZATION_ID
73 ,X_RETURN_STATUS => L_RETURN_STATUS
74 ,X_MSG_COUNT => L_MSG_COUNT
75 ,X_MSG_DATA => L_MSG_DATA
76 ,X_RESOURCE_ID => L_RESOURCE_ID);
77 /* IF L_RETURN_STATUS <> 'S' THEN
78 RAISE L_EXCEPTION;
79 END IF;*/
80 IF (L_RETURN_STATUS <> 'S' AND L_RETURN_STATUS <> 'W') THEN
81 RAISE L_EXCEPTION;
82 END IF;
83 IF (L_RETURN_STATUS = 'W') THEN
84 P_RET_STATUS := 'Y';
85 END IF;
86
87 SELECT
88 count(*)
89 INTO CP_COUNT
90 FROM
91 PA_RESOURCES RES,
92 PA_RESOURCES_DENORM OU,
93 HR_ALL_ORGANIZATION_UNITS HRORG
94 WHERE RES.RESOURCE_ID = OU.RESOURCE_ID
95 AND HRORG.ORGANIZATION_ID = OU.RESOURCE_ORGANIZATION_ID
96 AND ( DECODE(RES.REQUEST_ID
97 ,P_CONC_REQUEST_ID
98 ,RES.REQUEST_ID
99 ,NULL) = P_CONC_REQUEST_ID
100 OR DECODE(OU.REQUEST_ID
101 ,P_CONC_REQUEST_ID
102 ,OU.REQUEST_ID
103 ,NULL) = P_CONC_REQUEST_ID );
104 RETURN (TRUE);
105 EXCEPTION
106 WHEN L_EXCEPTION THEN
107 FND_MSG_PUB.GET(P_ENCODED => 'F'
108 ,P_MSG_INDEX => 1
109 ,P_DATA => L_MSG_DATA
110 ,P_MSG_INDEX_OUT => L_MSG_COUNT);
111 IF NVL(L_MSG_COUNT
112 ,0) > 0 THEN
113 /*SRW.MESSAGE(L_MSG_COUNT
114 ,L_MSG_DATA)*/NULL;
115 RAISE;
116 END IF;
117 WHEN OTHERS THEN
118 RAISE;
119 RETURN (TRUE);
120 END BEFOREREPORT;
121
122 FUNCTION CF_ORGANIZATION_NAMEFORMULA RETURN CHAR IS
123 L_NAME PA_R_EXPENDITURE_ORGS_V.NAME%TYPE := NULL;
124 BEGIN
125 IF P_ORGANIZATION_ID IS NOT NULL THEN
126 SELECT
127 SUBSTR(NAME
128 ,1
129 ,60)
130 INTO L_NAME
131 FROM
132 PA_R_EXPENDITURE_ORGS_V
133 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
134 END IF;
135 RETURN L_NAME;
136 END CF_ORGANIZATION_NAMEFORMULA;
137
138 FUNCTION CF_REFRESHFORMULA RETURN CHAR IS
139 L_MEANING FND_LOOKUPS.MEANING%TYPE := NULL;
140 BEGIN
141 IF P_REFRESH = 'Y' THEN
142 SELECT
143 MEANING
144 INTO L_MEANING
145 FROM
146 FND_LOOKUPS
147 WHERE LOOKUP_TYPE = 'YES_NO'
148 AND LOOKUP_CODE = 'Y';
149 ELSIF P_REFRESH = 'N' THEN
150 SELECT
151 MEANING
152 INTO L_MEANING
153 FROM
154 FND_LOOKUPS
155 WHERE LOOKUP_TYPE = 'YES_NO'
156 AND LOOKUP_CODE = 'N';
157 END IF;
158 RETURN L_MEANING;
159 END CF_REFRESHFORMULA;
160
161 FUNCTION CF_SELECTION_OPTIONFORMULA RETURN CHAR IS
162 L_SELECTION_OPTION PA_LOOKUPS.MEANING%TYPE := NULL;
163 BEGIN
164 IF P_SELECTION_OPTION IS NOT NULL THEN
165 SELECT
166 MEANING
167 INTO L_SELECTION_OPTION
168 FROM
169 PA_LOOKUPS
170 WHERE LOOKUP_TYPE = 'PA_RES_PULL_SELECT_TYPE'
171 AND LOOKUP_CODE = P_SELECTION_OPTION;
172 END IF;
173 RETURN L_SELECTION_OPTION;
174 END CF_SELECTION_OPTIONFORMULA;
175
176 FUNCTION CF_ORG_STR_VERSION_IDFORMULA RETURN CHAR IS
177 L_ORG_STR_VERSION_ID PER_ORGANIZATION_STRUCTURES.NAME%TYPE := NULL;
178 L_ORG_STR_VERSION_NUM PER_ORG_STRUCTURE_VERSIONS.VERSION_NUMBER%TYPE := NULL;
179 BEGIN
180 IF P_ORG_STR_VERSION_ID IS NOT NULL THEN
181 SELECT
182 SUBSTR(POS.NAME
183 ,1
184 ,60),
185 POSV.VERSION_NUMBER
186 INTO L_ORG_STR_VERSION_ID,L_ORG_STR_VERSION_NUM
187 FROM
188 PER_ORGANIZATION_STRUCTURES POS,
189 PER_ORG_STRUCTURE_VERSIONS POSV
190 WHERE POSV.ORGANIZATION_STRUCTURE_ID = POS.ORGANIZATION_STRUCTURE_ID
191 AND POSV.ORG_STRUCTURE_VERSION_ID = P_ORG_STR_VERSION_ID;
192 END IF;
193 IF P_ORG_STR_VERSION_ID IS NOT NULL THEN
194 RETURN L_ORG_STR_VERSION_ID || ' (' || L_ORG_STR_VERSION_NUM || ')';
195 ELSE
196 RETURN NULL;
197 END IF;
198 END CF_ORG_STR_VERSION_IDFORMULA;
199
200 FUNCTION CF_START_ORG_IDFORMULA RETURN CHAR IS
201 L_START_ORG_ID HR_ORGANIZATION_UNITS.NAME%TYPE := NULL;
202 BEGIN
203 IF P_START_ORGANIZATION_ID IS NOT NULL THEN
204 SELECT
205 SUBSTR(NAME
206 ,1
207 ,60)
208 INTO L_START_ORG_ID
209 FROM
210 HR_ORGANIZATION_UNITS
211 WHERE ORGANIZATION_ID = P_START_ORGANIZATION_ID;
212 END IF;
213 RETURN L_START_ORG_ID;
214 END CF_START_ORG_IDFORMULA;
215
216 FUNCTION CF_PULL_TERM_RESFORMULA RETURN CHAR IS
217 L_PULL_TERM_RES PA_LOOKUPS.MEANING%TYPE := NULL;
218 BEGIN
219 IF P_PULL_TERM_RES IS NOT NULL THEN
220 SELECT
221 MEANING
222 INTO L_PULL_TERM_RES
223 FROM
224 PA_LOOKUPS
225 WHERE LOOKUP_TYPE = 'YES_NO'
226 AND LOOKUP_CODE = P_PULL_TERM_RES;
227 END IF;
228 RETURN L_PULL_TERM_RES;
229 END CF_PULL_TERM_RESFORMULA;
230
231 FUNCTION CF_PERSON_TYPEFORMULA RETURN CHAR IS
232 L_PERSON_TYPE FND_LOOKUP_VALUES.MEANING%TYPE := NULL;
233 BEGIN
234 IF P_PERSON_TYPE IS NOT NULL THEN
235 SELECT
236 MEANING
237 INTO L_PERSON_TYPE
238 FROM
239 FND_LOOKUP_VALUES
240 WHERE LOOKUP_TYPE in ( 'PA_REP_ALL' , 'PERSON_TYPE' )
241 AND LOOKUP_CODE = P_PERSON_TYPE
242 AND LANGUAGE = USERENV('LANG');
243 END IF;
244 RETURN L_PERSON_TYPE;
245 END CF_PERSON_TYPEFORMULA;
246
247 FUNCTION CP_NODATAFOUND_P RETURN VARCHAR2 IS
248 BEGIN
249 RETURN CP_NODATAFOUND;
250 END CP_NODATAFOUND_P;
251
252 FUNCTION CP_COUNT_P RETURN NUMBER IS
253 BEGIN
254 RETURN CP_COUNT;
255 END CP_COUNT_P;
256
257 FUNCTION CP_CHK_ORG_OR_EMP_P RETURN VARCHAR2 IS
258 BEGIN
259 RETURN CP_CHK_ORG_OR_EMP;
260 END CP_CHK_ORG_OR_EMP_P;
261
262 FUNCTION CP_CHK_EMP_RANGE_P RETURN VARCHAR2 IS
263 BEGIN
264 RETURN CP_CHK_EMP_RANGE;
265 END CP_CHK_EMP_RANGE_P;
266
267 FUNCTION CP_CHK_ORG_AND_EMP_RANGE_P RETURN VARCHAR2 IS
268 BEGIN
269 RETURN CP_CHK_ORG_AND_EMP_RANGE;
270 END CP_CHK_ORG_AND_EMP_RANGE_P;
271
272 FUNCTION CP_CHK_SINGLE_ORG_P RETURN VARCHAR2 IS
273 BEGIN
274 RETURN CP_CHK_SINGLE_ORG;
275 END CP_CHK_SINGLE_ORG_P;
276
277 FUNCTION CP_CHK_START_ORG_P RETURN VARCHAR2 IS
278 BEGIN
279 RETURN CP_CHK_START_ORG;
280 END CP_CHK_START_ORG_P;
281
282 END PA_PARCPRJR_XMLP_PKG;
283
[Home] [Help]