1 PACKAGE BODY PSP_PSPDAREG_XMLP_PKG AS
2 /* $Header: PSPDAREGB.pls 120.3 2007/10/29 07:20:23 amakrish noship $ */
3 FUNCTION CF_NEW_CIFORMULA(GLCCID_NEW IN NUMBER
4 ,PROJECT_ID_NEW IN NUMBER
5 ,TASK_ID_NEW IN NUMBER
6 ,AWARD_ID_NEW IN NUMBER
7 ,EXP_ORG_ID_NEW IN NUMBER
8 ,EXPENDITURE_TYPE_NEW IN VARCHAR2) RETURN CHAR IS
9 V_RETCODE NUMBER;
10 L_CHART_OF_ACCTS VARCHAR2(20);
11 GL_FLEX_VALUES VARCHAR2(2000);
12 L_PROJECT_NAME VARCHAR2(30);
13 L_AWARD_NUMBER VARCHAR2(15);
14 L_TASK_NUMBER VARCHAR2(25);
15 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
16 L_POETA_NEW VARCHAR2(2000);
17 BEGIN
18 IF GLCCID_NEW IS NOT NULL THEN
19 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(TO_NUMBER(P_SET_OF_BOOKS_ID)
20 ,L_CHART_OF_ACCTS);
21 GL_FLEX_VALUES := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
22 ,KEY_FLEX_CODE => 'GL#'
23 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
24 ,COMBINATION_ID => GLCCID_NEW);
25 RETURN (GL_FLEX_VALUES);
26 ELSE
27 IF PROJECT_ID_NEW IS NOT NULL THEN
28 SELECT
29 NAME
30 INTO L_PROJECT_NAME
31 FROM
32 PA_PROJECTS_ALL
33 WHERE PROJECT_ID = PROJECT_ID_NEW;
34 SELECT
35 TASK_NUMBER
36 INTO L_TASK_NUMBER
37 FROM
38 PA_TASKS
39 WHERE TASK_ID = TASK_ID_NEW;
40 IF AWARD_ID_NEW IS NOT NULL THEN
41 SELECT
42 AWARD_NUMBER
43 INTO L_AWARD_NUMBER
44 FROM
45 GMS_AWARDS_ALL
46 WHERE AWARD_ID = AWARD_ID_NEW;
47 ELSE
48 L_AWARD_NUMBER := '';
49 END IF;
50 SELECT
51 NAME
52 INTO L_ORG_NAME
53 FROM
54 HR_ALL_ORGANIZATION_UNITS
55 WHERE ORGANIZATION_ID = EXP_ORG_ID_NEW;
56 L_POETA_NEW := L_PROJECT_NAME || ' ' || L_TASK_NUMBER || ' ' || L_AWARD_NUMBER || ' ' || L_ORG_NAME || ' ' || EXPENDITURE_TYPE_NEW;
57 ELSE
58 L_POETA_NEW := '';
59 END IF;
60 RETURN (L_POETA_NEW);
61 END IF;
62 END CF_NEW_CIFORMULA;
63
64 FUNCTION CF_OLD_CIFORMULA(GLCCID_OLD IN NUMBER
65 ,PROJECT_ID_OLD IN NUMBER
66 ,TASK_ID_OLD IN NUMBER
67 ,AWARD_ID_OLD IN NUMBER
68 ,EXP_ORG_ID_OLD IN NUMBER
69 ,EXPENDITURE_TYPE_OLD IN VARCHAR2) RETURN CHAR IS
70 V_RETCODE NUMBER;
71 L_CHART_OF_ACCTS VARCHAR2(20);
72 GL_FLEX_VALUES VARCHAR2(2000);
73 L_PROJECT_NAME VARCHAR2(30);
74 L_AWARD_NUMBER VARCHAR2(15);
75 L_TASK_NUMBER VARCHAR2(25);
76 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
77 L_POETA_OLD VARCHAR2(2000);
78 BEGIN
79 IF GLCCID_OLD IS NOT NULL THEN
80 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(TO_NUMBER(P_SET_OF_BOOKS_ID)
81 ,L_CHART_OF_ACCTS);
82 GL_FLEX_VALUES := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
83 ,KEY_FLEX_CODE => 'GL#'
84 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
85 ,COMBINATION_ID => GLCCID_OLD);
86 RETURN (GL_FLEX_VALUES);
87 ELSE
88 IF PROJECT_ID_OLD IS NOT NULL THEN
89 SELECT
90 NAME
91 INTO L_PROJECT_NAME
92 FROM
93 PA_PROJECTS_ALL
94 WHERE PROJECT_ID = PROJECT_ID_OLD;
95 SELECT
96 TASK_NUMBER
97 INTO L_TASK_NUMBER
98 FROM
99 PA_TASKS
100 WHERE TASK_ID = TASK_ID_OLD;
101 IF AWARD_ID_OLD IS NOT NULL THEN
102 SELECT
103 AWARD_NUMBER
104 INTO L_AWARD_NUMBER
105 FROM
106 GMS_AWARDS_ALL
107 WHERE AWARD_ID = AWARD_ID_OLD;
108 ELSE
109 L_AWARD_NUMBER := '';
110 END IF;
111 SELECT
112 NAME
113 INTO L_ORG_NAME
114 FROM
115 HR_ALL_ORGANIZATION_UNITS
116 WHERE ORGANIZATION_ID = EXP_ORG_ID_OLD;
117 L_POETA_OLD := L_PROJECT_NAME || ' ' || L_TASK_NUMBER || ' ' || L_AWARD_NUMBER || ' ' || L_ORG_NAME || ' ' || EXPENDITURE_TYPE_OLD;
118 ELSE
119 L_POETA_OLD := '';
120 END IF;
121 RETURN (L_POETA_OLD);
122 END IF;
123 END CF_OLD_CIFORMULA;
124
125 FUNCTION CF_BATCHFORMULA RETURN CHAR IS
126 BEGIN
127 RETURN (NVL(P_BATCH_NAME
128 ,'All'));
129 END CF_BATCHFORMULA;
130
131 FUNCTION CF_APPROVED_BYFORMULA RETURN CHAR IS
132 V_FULLNAME VARCHAR2(30);
133 BEGIN
134 IF P_APPROVED_BY IS NULL THEN
135 RETURN ('All');
136 ELSE
137 SELECT
138 PPX.FULL_NAME
139 INTO V_FULLNAME
140 FROM
141 PER_PEOPLE_X PPX,
142 FND_USER FU
143 WHERE FU.USER_ID = P_APPROVED_BY
144 AND PPX.PERSON_ID = FU.EMPLOYEE_ID;
145 RETURN (V_FULLNAME);
146 END IF;
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 RETURN (NULL);
150 END CF_APPROVED_BYFORMULA;
151
152 FUNCTION CF_SUBMITTED_BYFORMULA RETURN CHAR IS
153 V_FULLNAME VARCHAR2(30);
154 BEGIN
155 IF P_SUBMITTED_BY IS NULL THEN
156 RETURN ('All');
157 ELSE
158 SELECT
159 PPX.FULL_NAME
160 INTO V_FULLNAME
161 FROM
162 PER_PEOPLE_X PPX,
163 FND_USER FU
164 WHERE FU.USER_ID = P_SUBMITTED_BY
165 AND PPX.PERSON_ID = FU.EMPLOYEE_ID;
166 RETURN (V_FULLNAME);
167 END IF;
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN
170 RETURN (NULL);
171 END CF_SUBMITTED_BYFORMULA;
172
173 FUNCTION CF_ASSIGN_ORG_IDFORMULA RETURN CHAR IS
174 V_ORGNAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
175 BEGIN
176 IF P_ASSIGNMENT_ORGANIZATION_ID IS NULL THEN
177 RETURN ('All');
178 ELSE
179 SELECT
180 NAME
181 INTO V_ORGNAME
182 FROM
183 HR_ORGANIZATION_UNITS
184 WHERE ORGANIZATION_ID = P_ASSIGNMENT_ORGANIZATION_ID;
185 RETURN (V_ORGNAME);
186 END IF;
187 END CF_ASSIGN_ORG_IDFORMULA;
188
189 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
190 BEGIN
191 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
192 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
193 RETURN (TRUE);
194 END BEFOREREPORT;
195
196 FUNCTION AFTERREPORT RETURN BOOLEAN IS
197 BEGIN
198 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
199 RETURN (TRUE);
200 END AFTERREPORT;
201
202 FUNCTION AFTERPFORM RETURN BOOLEAN IS
203 BEGIN
204 IF P_PRIMARY_ORGANIZATION_ID IS NULL THEN
205 P_PRIMARY_ORG_WHERE_CLAUSE := 'AND 1=1';
206 ELSE
207 P_PRIMARY_ORG_WHERE_CLAUSE := 'AND pact.person_id IN
208 (SELECT person_id
209 FROM per_all_assignments_f
210 WHERE organization_id = :P_PRIMARY_ORGANIZATION_ID
211 AND primary_flag = ''Y''
212 AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date)';
213 END IF;
214 RETURN (TRUE);
215 END AFTERPFORM;
216
217 FUNCTION CF_PRIMARY_ORG_NAMEFORMULA RETURN CHAR IS
218 V_ORGNAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
219 BEGIN
220 IF P_PRIMARY_ORGANIZATION_ID IS NULL THEN
221 RETURN ('All');
222 ELSE
223 SELECT
224 DISTINCT
225 NAME
226 INTO V_ORGNAME
227 FROM
228 HR_ALL_ORGANIZATION_UNITS
229 WHERE ORGANIZATION_ID = P_PRIMARY_ORGANIZATION_ID;
230 RETURN (V_ORGNAME);
231 END IF;
232 END CF_PRIMARY_ORG_NAMEFORMULA;
233
234 FUNCTION CF_SOB_IDFORMULA RETURN CHAR IS
235 V_SOB_ID NUMBER;
236 BEGIN
237 V_SOB_ID := PSP_GENERAL.GET_SPECIFIC_PROFILE('GL_SET_OF_BKS_ID');
238 RETURN (TO_CHAR(V_SOB_ID));
239 END CF_SOB_IDFORMULA;
240
241 FUNCTION CF_SOB_ID2FORMULA RETURN CHAR IS
242 V_SOB_ID NUMBER;
243 BEGIN
244 V_SOB_ID := PSP_GENERAL.GET_SPECIFIC_PROFILE('GL_SET_OF_BKS_ID');
245 RETURN (TO_CHAR(V_SOB_ID));
246 END CF_SOB_ID2FORMULA;
247
248 FUNCTION CF_ADJUSTMENT_BYFORMULA(ADJUSTMENT_BY IN VARCHAR2) RETURN CHAR IS
249 CURSOR ADJUST_BY_CUR IS
250 SELECT
251 LTRIM(MEANING)
252 FROM
253 FND_LOOKUP_VALUES
254 WHERE LOOKUP_TYPE = 'PSP_MESSAGE_TEXT'
255 AND LOOKUP_CODE = ADJUSTMENT_BY;
256 L_ADJUSTMENT_BY VARCHAR2(20);
257 BEGIN
258 OPEN ADJUST_BY_CUR;
259 FETCH ADJUST_BY_CUR
260 INTO L_ADJUSTMENT_BY;
261 CLOSE ADJUST_BY_CUR;
262 RETURN L_ADJUSTMENT_BY;
263 END CF_ADJUSTMENT_BYFORMULA;
264
265 FUNCTION CF_SUM_CURRENCYFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
266 BEGIN
267 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
268 RETURN ('(' || CURRENCY_CODE || ')');
269 END CF_SUM_CURRENCYFORMULA;
270
271 FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
272 BEGIN
273 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
274 RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
275 ,30));
276 END CF_CURRENCY_FORMATFORMULA;
277
278 FUNCTION CF_ORIG_AMOUNT_TOTAL_DSPFORMUL(CF_CURRENCY_FORMAT IN VARCHAR2
279 ,CS_ORIG_AMOUNT_TOTAL IN NUMBER) RETURN CHAR IS
280 BEGIN
281 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
282 /*SRW.REFERENCE(CS_ORIG_AMOUNT_TOTAL)*/NULL;
283 RETURN (TO_CHAR(CS_ORIG_AMOUNT_TOTAL
284 ,CF_CURRENCY_FORMAT));
285 END CF_ORIG_AMOUNT_TOTAL_DSPFORMUL;
286
287 FUNCTION CF_ADJUSTED_AMOUNT_TOTAL_DSPFO(CF_CURRENCY_FORMAT IN VARCHAR2
288 ,CS_ADJUSTED_AMOUNT_TOTAL IN NUMBER) RETURN CHAR IS
289 BEGIN
290 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
291 /*SRW.REFERENCE(CS_ADJUSTED_AMOUNT_TOTAL)*/NULL;
292 RETURN (TO_CHAR(CS_ADJUSTED_AMOUNT_TOTAL
293 ,CF_CURRENCY_FORMAT));
294 END CF_ADJUSTED_AMOUNT_TOTAL_DSPFO;
295
296 FUNCTION CF_ORIGINAL_AMOUNT_DSPFORMULA(CF_CURRENCY_FORMAT IN VARCHAR2
297 ,ORIGINAL_AMOUNT IN NUMBER) RETURN CHAR IS
298 BEGIN
299 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
300 /*SRW.REFERENCE(ORIGINAL_AMOUNT)*/NULL;
301 RETURN (TO_CHAR(ORIGINAL_AMOUNT
302 ,CF_CURRENCY_FORMAT));
303 END CF_ORIGINAL_AMOUNT_DSPFORMULA;
304
305 FUNCTION CF_ADJUSTED_AMOUNT_DSPFORMULA(CF_CURRENCY_FORMAT IN VARCHAR2
306 ,ADJUSTED_AMOUNT IN NUMBER) RETURN CHAR IS
307 BEGIN
308 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
309 /*SRW.REFERENCE(ADJUSTED_AMOUNT)*/NULL;
310 RETURN (TO_CHAR(ADJUSTED_AMOUNT
311 ,CF_CURRENCY_FORMAT));
312 END CF_ADJUSTED_AMOUNT_DSPFORMULA;
313
314 END PSP_PSPDAREG_XMLP_PKG;
[Home] [Help]