1 PACKAGE BODY PSP_PSPENCSC_XMLP_PKG AS
2 /* $Header: PSPENCSCB.pls 120.3.12020000.2 2013/04/05 07:19:13 lkodaman ship $ */
3 FUNCTION SUSP_ORGANIZATIONFORMULA(SUSPENSE_ORG_ACCOUNT_ID IN NUMBER) RETURN VARCHAR2 IS
4 V_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
5 BEGIN
6 SELECT
7 NAME
8 INTO V_NAME
9 FROM
10 HR_ORGANIZATION_UNITS
11 WHERE ORGANIZATION_ID = (
12 SELECT
13 ORGANIZATION_ID
14 FROM
15 PSP_ORGANIZATION_ACCOUNTS
16 WHERE ORGANIZATION_ACCOUNT_ID = SUSPENSE_ORG_ACCOUNT_ID );
17 RETURN (V_NAME);
18 RETURN NULL;
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 RETURN ('no_data_found');
22 WHEN TOO_MANY_ROWS THEN
23 RETURN ('too many rows');
24 WHEN OTHERS THEN
25 RETURN ('error');
26 END SUSP_ORGANIZATIONFORMULA;
27
28 FUNCTION CF_TASK_NAMEFORMULA(TASK_ID1 IN NUMBER
29 ,PROJECT_ID1 IN NUMBER) RETURN VARCHAR2 IS
30 V_TASK_NUMBER VARCHAR2(300); -- Bug : 16591849
31 BEGIN
32 IF TASK_ID1 IS NOT NULL THEN
33 SELECT
34 TASK_NUMBER
35 INTO V_TASK_NUMBER
36 FROM
37 PA_TASKS_EXPEND_V
38 WHERE PROJECT_ID = PROJECT_ID1
39 AND TASK_ID = TASK_ID1;
40 RETURN (V_TASK_NUMBER);
41 END IF;
42 RETURN NULL;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 RETURN ('no_data_found');
46 WHEN TOO_MANY_ROWS THEN
47 RETURN ('too many rows');
48 WHEN OTHERS THEN
49 RETURN ('error');
50 END CF_TASK_NAMEFORMULA;
51
52 FUNCTION CF_AWARD_NUMBERFORMULA(AWARD_ID1 IN NUMBER) RETURN VARCHAR2 IS
53 V_AWARD_NUMBER VARCHAR2(30);
54 BEGIN
55 IF AWARD_ID1 IS NOT NULL THEN
56 SELECT
57 DISTINCT
58 AWARD_NUMBER
59 INTO V_AWARD_NUMBER
60 FROM
61 GMS_AWARDS_BASIC_V
62 WHERE AWARD_ID = AWARD_ID1;
63 RETURN (V_AWARD_NUMBER);
64 END IF;
65 RETURN NULL;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 RETURN ('no_data_found');
69 WHEN TOO_MANY_ROWS THEN
70 RETURN ('too many rows');
71 WHEN OTHERS THEN
72 RETURN ('error');
73 END CF_AWARD_NUMBERFORMULA;
74
75 FUNCTION CF_PROJECT_NUMBERFORMULA(PROJECT_ID1 IN NUMBER) RETURN VARCHAR2 IS
76 V_PROJECT_NUMBER VARCHAR2(30);
77 BEGIN
78 IF PROJECT_ID1 IS NOT NULL THEN
79 SELECT
80 PROJECT_NUMBER
81 INTO V_PROJECT_NUMBER
82 FROM
83 GMS_PROJECTS_EXPEND_V
84 WHERE PROJECT_ID = PROJECT_ID1;
85 RETURN (V_PROJECT_NUMBER);
86 END IF;
87 RETURN NULL;
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 RETURN ('no_data_found');
91 WHEN TOO_MANY_ROWS THEN
92 RETURN ('too many rows');
93 WHEN OTHERS THEN
94 RETURN ('error');
95 END CF_PROJECT_NUMBERFORMULA;
96
97 FUNCTION CF_ORG_NAMEFORMULA(EXPENDITURE_ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
98 V_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
99 BEGIN
100 IF EXPENDITURE_ORGANIZATION_ID IS NOT NULL THEN
101 SELECT
102 NAME
103 INTO V_ORG_NAME
104 FROM
105 PA_ORGANIZATIONS_EXPEND_V
106 WHERE ORGANIZATION_ID = EXPENDITURE_ORGANIZATION_ID
107 AND ACTIVE_FLAG = 'Y';
108 RETURN (V_ORG_NAME);
109 END IF;
110 RETURN NULL;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 RETURN ('no_data_found');
114 WHEN TOO_MANY_ROWS THEN
115 RETURN ('too many rows');
116 WHEN OTHERS THEN
117 RETURN ('error');
118 END CF_ORG_NAMEFORMULA;
119
120 FUNCTION CF_GL_DESCFORMULA(GL_CODE_COMBINATION_ID IN NUMBER) RETURN VARCHAR2 IS
121 V_GL_CODE VARCHAR2(255);
122 V_SOB NUMBER := PSP_GENERAL.GET_SPECIFIC_PROFILE('GL_SET_OF_BKS_ID');
123 BEGIN
124 IF GL_CODE_COMBINATION_ID IS NOT NULL THEN
125 V_GL_CODE := PSP_GENERAL.GET_GL_DESCRIPTION(V_SOB
126 ,GL_CODE_COMBINATION_ID);
127 RETURN (V_GL_CODE);
128 END IF;
129 RETURN NULL;
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 RETURN ('no_data_found');
133 WHEN TOO_MANY_ROWS THEN
134 RETURN ('too many rows');
135 WHEN OTHERS THEN
136 RETURN ('error');
137 END CF_GL_DESCFORMULA;
138
139 FUNCTION CF_EMPLOYEE_NUMBERFORMULA(PERSON_ID1 IN NUMBER) RETURN VARCHAR2 IS
140 V_EMPLOYEE_NUMBER VARCHAR2(30);
141 BEGIN
142 SELECT
143 PAF1.EMPLOYEE_NUMBER
144 INTO V_EMPLOYEE_NUMBER
145 FROM
146 PER_ALL_PEOPLE_F PAF1
147 WHERE PERSON_ID = PERSON_ID1
148 AND EFFECTIVE_START_DATE = (
149 SELECT
150 MIN(EFFECTIVE_START_DATE)
151 FROM
152 PER_ALL_PEOPLE_F PAF2
153 WHERE PAF1.PERSON_ID = PAF2.PERSON_ID
154 AND PAF2.CURRENT_EMPLOYEE_FLAG = 'Y' );
155 RETURN (V_EMPLOYEE_NUMBER);
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 RETURN ('no_data_found');
159 WHEN TOO_MANY_ROWS THEN
160 RETURN ('too many rows');
161 WHEN OTHERS THEN
162 RETURN ('error');
163 END CF_EMPLOYEE_NUMBERFORMULA;
164
165 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
166 BEGIN
167 -- HR_STANDARD.EVENT('BEFORE REPORT');
168 CP_BEGIN_DATE := TO_CHAR(P_BEGIN_DATE,'DD-MON-YYYY');
169 CP_END_DATE := TO_CHAR(P_END_DATE,'DD-MON-YYYY');
170 RETURN (TRUE);
171 END BEFOREREPORT;
172
173 FUNCTION P_ORGANIZATIONSVALIDTRIGGER RETURN BOOLEAN IS
174 BEGIN
175 RETURN (TRUE);
176 END P_ORGANIZATIONSVALIDTRIGGER;
177
178 FUNCTION CF_ORG_TOTAL_DSPFORMULA(CS_ORG_TOTAL IN NUMBER
179 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
180 BEGIN
181 /*SRW.REFERENCE(CS_ORG_TOTAL)*/NULL;
182 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
183 RETURN (TO_CHAR(CS_ORG_TOTAL
184 ,CF_CURRENCY_FORMAT));
185 END CF_ORG_TOTAL_DSPFORMULA;
186
187 FUNCTION CF_EMP_TOTAL_DSPFORMULA(CS_EMP_TOTAL IN NUMBER
188 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
189 BEGIN
190 /*SRW.REFERENCE(CS_EMP_TOTAL)*/NULL;
191 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
192 RETURN (TO_CHAR(CS_EMP_TOTAL
193 ,CF_CURRENCY_FORMAT));
194 END CF_EMP_TOTAL_DSPFORMULA;
195
196 FUNCTION CF_ENC_AMOUNT_DSPFORMULA(CS_ENC_AMOUNT IN NUMBER
197 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
198 BEGIN
199 /*SRW.REFERENCE(CS_ENC_AMOUNT)*/NULL;
200 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
201 RETURN (TO_CHAR(CS_ENC_AMOUNT
202 ,CF_CURRENCY_FORMAT));
203 END CF_ENC_AMOUNT_DSPFORMULA;
204
205 FUNCTION AFTERREPORT RETURN BOOLEAN IS
206 BEGIN
207 -- HR_STANDARD.EVENT('AFTER REPORT');
208 RETURN (TRUE);
209 END AFTERREPORT;
210
211 FUNCTION CF_SUSPENSE_ORG_ACCOUNTFORMULA(GL_CODE_COMBINATION_ID IN NUMBER
212 ,CF_PROJECT_NUMBER IN VARCHAR2
213 ,CF_AWARD_NUMBER IN VARCHAR2
214 ,CF_TASK_NUMBER IN VARCHAR2
215 ,CF_ORG_NAME IN VARCHAR2
216 ,EXPENDITURE_TYPE IN VARCHAR2) RETURN CHAR IS
217 L_SUSPENSE_ORG_ACCOUNT VARCHAR2(2000);
218 V_SOB NUMBER := PSP_GENERAL.GET_SPECIFIC_PROFILE('GL_SET_OF_BKS_ID');
219 V_RETCODE NUMBER;
220 L_CHART_OF_ACCTS VARCHAR2(20);
221 BEGIN
222 IF GL_CODE_COMBINATION_ID IS NOT NULL THEN
223 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(V_SOB
224 ,L_CHART_OF_ACCTS);
225 L_SUSPENSE_ORG_ACCOUNT := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
226 ,KEY_FLEX_CODE => 'GL#'
227 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
228 ,COMBINATION_ID => GL_CODE_COMBINATION_ID);
229 ELSE
230 L_SUSPENSE_ORG_ACCOUNT := CF_PROJECT_NUMBER || ' ' || CF_AWARD_NUMBER || ' ' || CF_TASK_NUMBER || ' ' || CF_ORG_NAME || ' ' || EXPENDITURE_TYPE;
231 END IF;
232 RETURN (L_SUSPENSE_ORG_ACCOUNT);
233 END CF_SUSPENSE_ORG_ACCOUNTFORMULA;
234
235 FUNCTION AFTERPFORM RETURN BOOLEAN IS
236 CURSOR C1(P_LOOKUP_CODE IN VARCHAR2,L_TEMPLATE_ID IN NUMBER) IS
237 SELECT
238 COUNT(1)
239 FROM
240 PSP_REPORT_TEMPLATE_DETAILS
241 WHERE TEMPLATE_ID = L_TEMPLATE_ID
242 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
243 AND CRITERIA_LOOKUP_CODE = P_LOOKUP_CODE;
244 L_NUM NUMBER;
245 L_NUM1 NUMBER;
246 L_NUM2 NUMBER;
247 BEGIN
248 IF P_ORG_TEMPLATE_ID IS NULL THEN
249 P_ORGANIZATIONS := ' and 1 = 1 ';
250 ELSE
251 OPEN C1('ORG',P_ORG_TEMPLATE_ID);
252 FETCH C1
253 INTO L_NUM;
254 CLOSE C1;
255 IF L_NUM <> 0 THEN
256 P_ORGANIZATIONS := ' and b.organization_id IN (select criteria_value1 from psp_report_template_details
257 where template_id = ' || P_ORG_TEMPLATE_ID || '
258 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
259 and criteria_lookup_code = ''ORG'' ' || ' ) ';
260 ELSE
261 P_ORGANIZATIONS := ' and 1 = 1 ';
262 END IF;
263 END IF;
264 IF P_PAY_TEMPLATE_ID IS NULL THEN
265 P_PAYROLL_ID := ' and 1 = 1 ';
266 ELSE
267 OPEN C1('PAY',P_PAY_TEMPLATE_ID);
268 FETCH C1
269 INTO L_NUM2;
270 CLOSE C1;
271 IF L_NUM2 <> 0 THEN
272 P_PAYROLL_ID := ' and a.payroll_id IN (select criteria_value1 from psp_report_template_details
273 where template_id = ' || P_PAY_TEMPLATE_ID || '
274 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
275 and criteria_lookup_code = ''PAY'' ' || ' ) ';
276 ELSE
277 P_PAYROLL_ID := ' and 1 = 1 ';
278 END IF;
279 END IF;
280 RETURN (TRUE);
281 END AFTERPFORM;
282
283 FUNCTION CF_CHARGING_INSTRUCTIONSFORMUL RETURN VARCHAR2 IS
284 L_SUSPENSE_ORG_ACCOUNT VARCHAR2(2000);
285 BEGIN
286 RETURN (NULL);
287 END CF_CHARGING_INSTRUCTIONSFORMUL;
288
289 FUNCTION CF_PAY_TOTALFORMULA(CS_PAY_TOTAL IN NUMBER
290 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
291 BEGIN
292 /*SRW.REFERENCE(CS_PAY_TOTAL)*/NULL;
293 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
294 RETURN (TO_CHAR(CS_PAY_TOTAL
295 ,CF_CURRENCY_FORMAT));
296 END CF_PAY_TOTALFORMULA;
297
298 FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
299 BEGIN
300 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
301 RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
302 ,30));
303 END CF_CURRENCY_FORMATFORMULA;
304
305 FUNCTION CF_CURRENCY_TOTAL_DSPFORMULA(CS_CURRENCY_TOTAL IN NUMBER
306 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
307 BEGIN
308 /*SRW.REFERENCE(CS_CURRENCY_TOTAL)*/NULL;
309 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
310 RETURN (TO_CHAR(CS_CURRENCY_TOTAL
311 ,CF_CURRENCY_FORMAT));
312 END CF_CURRENCY_TOTAL_DSPFORMULA;
313
314 END PSP_PSPENCSC_XMLP_PKG;
[Home] [Help]