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