1 PACKAGE BODY PA_PAXALRUN_XMLP_PKG AS
2 /* $Header: PAXALRUNB.pls 120.0 2008/01/02 11:14:09 krreddy noship $ */
3 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 EXCEPTION
7 WHEN OTHERS THEN
8 RETURN (FALSE);
9 END GET_COVER_PAGE_VALUES;
10
11 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
12 X_PURGEABLE VARCHAR2(20);
13 INVALID_TRX_SOURCE EXCEPTION;
14 BEGIN
15 DECLARE
16 INIT_FAILURE EXCEPTION;
17 NDF VARCHAR2(80);
18 V_PROCESS_MODE VARCHAR2(20) := 'REPORT';
19 X_RETCODE VARCHAR2(630) := NULL;
20 X_ERRBUF VARCHAR2(630);
21 V_RUN_ID NUMBER;
22 V_REQUEST_ID NUMBER;
23 V_RULE_NAME VARCHAR2(60);
24 V_ORG_ID VARCHAR2(10);
25 NUMBER_OF_MESSAGES NUMBER;
26 MESSAGE_BUF VARCHAR2(2000);
27 BEGIN
28 P_EXPND_ITEM_DATE_1 := TO_CHAR(P_EXPND_ITEM_DATE,'DD-MON-YYYY');
29 V_ORG_ID := PA_MOAC_UTILS.GET_CURRENT_ORG_ID;
30 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
31 P_RULE_OPTIMIZER := FND_PROFILE.VALUE('PA_RULE_BASED_OPTIMIZER');
32 V_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
33 V_ORG_ID := PA_MOAC_UTILS.GET_CURRENT_ORG_ID;
34 IF (GET_COMPANY_NAME <> TRUE) THEN
35 RAISE INIT_FAILURE;
36 END IF;
37 SELECT
38 MEANING
39 INTO
40 NDF
41 FROM
42 PA_LOOKUPS
43 WHERE LOOKUP_CODE = 'NO_DATA_FOUND'
44 AND LOOKUP_TYPE = 'MESSAGE';
45 C_NO_DATA_FOUND := NDF;
46 IF (P_RUN_MODE <> 'A') THEN
47 PA_ALLOC_RUN.ALLOCATION_RUN(V_PROCESS_MODE
48 ,P_DEBUG_MODE
49 ,P_RUN_MODE
50 ,P_RULE_ID
51 ,P_RUN_PERIOD
52 ,P_EXPND_ITEM_DATE
53 ,V_RUN_ID
54 ,X_RETCODE
55 ,X_ERRBUF);
56 P_RUN_ID := V_RUN_ID;
57 IF (X_RETCODE <> '0') THEN
58 NUMBER_OF_MESSAGES := PA_DEBUG.NO_OF_DEBUG_MESSAGES;
59 FOR i IN 1 .. NUMBER_OF_MESSAGES LOOP
60 PA_DEBUG.GET_MESSAGE(I
61 ,MESSAGE_BUF);
62 END LOOP;
63 RAISE_APPLICATION_ERROR(-20101
64 ,NULL);
65 END IF;
66 END IF;
67 BEGIN
68 SELECT
69 RULE_NAME
70 INTO
71 C_RULE_NAME
72 FROM
73 PA_ALLOC_RULES
74 WHERE RULE_ID = P_RULE_ID;
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 NULL;
78 END;
79 BEGIN
80 SELECT
81 DECODE(P_RUN_MODE
82 ,'G'
83 ,DRAFT_REQUEST_ID
84 ,'R'
85 ,RELEASE_REQUEST_ID
86 ,DRAFT_REQUEST_ID),
87 RUN_STATUS_CODE,
88 RUN_STATUS,
89 EXPND_ITEM_DATE,
90 RUN_PERIOD
91 INTO
92 C_REQUEST_ID
93 ,P_RUN_STATUS_CODE
94 ,C_RUN_STATUS
95 ,C_EXPND_ITEM_DATE
96 ,C_RUN_PERIOD
97 FROM
98 PA_ALLOC_RUNS_V
99 WHERE RUN_ID = P_RUN_ID;
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 C_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
103 C_RUN_PERIOD := P_RUN_PERIOD;
104 C_EXPND_ITEM_DATE := P_EXPND_ITEM_DATE;
105 BEGIN
106 SELECT
107 MEANING
108 INTO
109 C_RUN_STATUS
110 FROM
111 PA_LOOKUPS
112 WHERE LOOKUP_TYPE = 'ALLOC_RUN_STATUS'
113 AND LOOKUP_CODE = 'DF';
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 NULL;
117 END;
118 END;
119 C_FUNC_CURRENCY := PA_CURRENCY.GET_CURRENCY_CODE;
120 BEGIN
121 SELECT
122 PURGEABLE_FLAG
123 INTO
124 X_PURGEABLE
125 FROM
126 PA_TRANSACTION_SOURCES
127 WHERE TRANSACTION_SOURCE = 'ALLOCATIONS';
128 P_PURGEABLE := X_PURGEABLE;
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 NULL;
132 END;
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 SELECT
136 MEANING
137 INTO
138 NDF
139 FROM
140 PA_LOOKUPS
141 WHERE LOOKUP_CODE = 'NO_DATA_FOUND'
142 AND LOOKUP_TYPE = 'MESSAGE';
143 C_NO_DATA_FOUND := NDF;
144 C_DUMMY_DATA := 1;
145 WHEN INIT_FAILURE THEN
146 NULL;
147 WHEN INVALID_TRX_SOURCE THEN
148 IF (P_RUN_MODE = 'R') THEN
149 RAISE_APPLICATION_ERROR(-20101
150 ,NULL);
151 END IF;
152 WHEN OTHERS THEN
153 X_RETCODE := SQLCODE;
154 X_ERRBUF := SQLERRM;
155 RAISE_APPLICATION_ERROR(-20101
156 ,NULL);
157 END;
158 RETURN (TRUE);
159 END BEFOREREPORT;
160
161 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
162 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
163 BEGIN
164 SELECT
165 GL.NAME
166 INTO
167 L_NAME
168 FROM
169 GL_SETS_OF_BOOKS GL,
170 PA_IMPLEMENTATIONS PI
171 WHERE GL.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID;
172 C_COMPANY_NAME_HEADER := L_NAME;
173 RETURN (TRUE);
174 EXCEPTION
175 WHEN OTHERS THEN
176 RETURN (FALSE);
177 END GET_COMPANY_NAME;
178
179 FUNCTION AFTERREPORT RETURN BOOLEAN IS
180 BEGIN
181 DECLARE
182 NUMBER_OF_MESSAGES NUMBER;
183 MESSAGE_BUF VARCHAR2(2000);
184 GEN_ERROR EXCEPTION;
185 BEGIN
186 NUMBER_OF_MESSAGES := PA_DEBUG.NO_OF_DEBUG_MESSAGES;
187 FOR i IN 1 .. NUMBER_OF_MESSAGES LOOP
188 PA_DEBUG.GET_MESSAGE(I
189 ,MESSAGE_BUF);
190 END LOOP;
191 EXCEPTION
192 WHEN OTHERS THEN
193 RAISE_APPLICATION_ERROR(-20101
194 ,NULL);
195 END;
196 IF (P_RUN_STATUS_CODE in ('RS','RF')) THEN
197 BEGIN
198 IF (P_PURGEABLE = 'Y') THEN
199 DELETE FROM PA_TRANSACTION_INTERFACE
200 WHERE TRANSACTION_SOURCE = 'ALLOCATIONS'
201 AND BATCH_NAME = TO_CHAR(P_RUN_ID);
202 END IF;
203 DELETE FROM PA_TRANSACTION_XFACE_CONTROL
204 WHERE TRANSACTION_SOURCE = 'ALLOCATIONS'
205 AND BATCH_NAME = TO_CHAR(P_RUN_ID);
206 COMMIT;
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 NULL;
210 WHEN OTHERS THEN
211 RAISE_APPLICATION_ERROR(-20101
212 ,NULL);
213 END;
214 END IF;
215 RETURN (TRUE);
216 END AFTERREPORT;
217
218 FUNCTION DRAFT_EXCEPTION_EXISTS RETURN number IS
219 DUMMY NUMBER;
220 BEGIN
221 SELECT
222 1
223 INTO
224 DUMMY
225 FROM
226 DUAL
227 WHERE exists (
228 SELECT
229 1
230 FROM
231 PA_ALLOC_EXCEPTIONS
232 WHERE RUN_ID = P_RUN_ID );
233 RETURN (DUMMY);
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 RETURN (0);
237 END DRAFT_EXCEPTION_EXISTS;
238
239 FUNCTION ALLOC_TXN_EXISTS RETURN number IS
240 DUMMY NUMBER;
241 BEGIN
242 SELECT
243 1
244 INTO
245 DUMMY
246 FROM
247 DUAL
248 WHERE exists (
249 SELECT
250 1
251 FROM
252 PA_ALLOC_TRANSACTIONS_V
253 WHERE RUN_ID = P_RUN_ID );
254 RETURN (DUMMY);
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 RETURN (0);
258 END ALLOC_TXN_EXISTS;
259
260 FUNCTION RELEASE_EXCEPTION_EXISTS RETURN number IS
261 DUMMY NUMBER;
262 BEGIN
263 SELECT
264 1
265 INTO
266 DUMMY
267 FROM
268 DUAL
269 WHERE exists (
270 SELECT
271 1
272 FROM
273 PA_ALLOC_TRANSACTIONS_V
274 WHERE RUN_ID = P_RUN_ID
275 AND REJECTION_CODE is not null );
276 RETURN (DUMMY);
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 RETURN (0);
280 END RELEASE_EXCEPTION_EXISTS;
281
282 FUNCTION C_FMT_MASKFORMULA RETURN VARCHAR2 IS
283 TMP_FMT_MASK VARCHAR2(15);
284 BEGIN
285 TMP_FMT_MASK := PA_CURRENCY.CURRENCY_FMT_MASK(15);
286 RETURN TMP_FMT_MASK;
287 END C_FMT_MASKFORMULA;
288
289 FUNCTION C_DSP_SUM_CURRENT_ALLOCATIONFO(SUM_CURRENT_ALLOCATION IN NUMBER
290 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
291 BEGIN
292 RETURN TO_CHAR(SUM_CURRENT_ALLOCATION
293 ,C_FMT_MASK);
294 END C_DSP_SUM_CURRENT_ALLOCATIONFO;
295
296 FUNCTION C_DSP_SUM_PREV_ALLOCATIONFORMU(SUM_PREVIOUS_ALLOCATION IN NUMBER
297 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
298 BEGIN
299 RETURN TO_CHAR(SUM_PREVIOUS_ALLOCATION
300 ,C_FMT_MASK);
301 END C_DSP_SUM_PREV_ALLOCATIONFORMU;
302
303 FUNCTION C_DSP_TOTAL_ALLOCATIONFORMULA(SUM_TOTAL_ALLOCATION IN NUMBER
304 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
305 BEGIN
306 RETURN TO_CHAR(SUM_TOTAL_ALLOCATION
307 ,C_FMT_MASK);
308 END C_DSP_TOTAL_ALLOCATIONFORMULA;
309
310 FUNCTION C_DSP_CURRENT_ALLOCATIONFORMUL(CURRENT_ALLOCATION IN NUMBER
311 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
312 BEGIN
313 RETURN TO_CHAR(CURRENT_ALLOCATION
314 ,C_FMT_MASK);
315 END C_DSP_CURRENT_ALLOCATIONFORMUL;
316
317 FUNCTION C_DSP_CURRENT_SUMFORMULA(CS_CURRENT_SUM IN NUMBER
318 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
319 BEGIN
320 RETURN TO_CHAR(CS_CURRENT_SUM
321 ,C_FMT_MASK);
322 END C_DSP_CURRENT_SUMFORMULA;
323
324 FUNCTION C_DSP_PREVIOUS_SUMFORMULA(CS_PREVIOUS_SUM IN NUMBER
325 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
326 BEGIN
327 RETURN TO_CHAR(CS_PREVIOUS_SUM
328 ,C_FMT_MASK);
329 END C_DSP_PREVIOUS_SUMFORMULA;
330
331 FUNCTION C_DSP_TOT_SUMFORMULA(CS_TOT_SUM IN NUMBER
332 ,C_FMT_MASK IN VARCHAR2) RETURN VARCHAR2 IS
333 BEGIN
334 RETURN TO_CHAR(CS_TOT_SUM
335 ,C_FMT_MASK);
336 END C_DSP_TOT_SUMFORMULA;
337
338 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
339 BEGIN
340 RETURN C_COMPANY_NAME_HEADER;
341 END C_COMPANY_NAME_HEADER_P;
342
343 FUNCTION C_NO_DATA_FOUND_P RETURN VARCHAR2 IS
344 BEGIN
345 RETURN C_NO_DATA_FOUND;
346 END C_NO_DATA_FOUND_P;
347
348 FUNCTION C_DUMMY_DATA_P RETURN NUMBER IS
349 BEGIN
350 RETURN C_DUMMY_DATA;
351 END C_DUMMY_DATA_P;
352
353 FUNCTION C_RULE_NAME_P RETURN VARCHAR2 IS
354 BEGIN
355 RETURN C_RULE_NAME;
356 END C_RULE_NAME_P;
357
358 FUNCTION C_REQUEST_ID_P RETURN NUMBER IS
359 BEGIN
360 RETURN C_REQUEST_ID;
361 END C_REQUEST_ID_P;
362
363 FUNCTION C_RUN_STATUS_P RETURN VARCHAR2 IS
364 BEGIN
365 RETURN C_RUN_STATUS;
366 END C_RUN_STATUS_P;
367
368 FUNCTION C_FUNC_CURRENCY_P RETURN VARCHAR2 IS
369 BEGIN
370 RETURN C_FUNC_CURRENCY;
371 END C_FUNC_CURRENCY_P;
372
373 FUNCTION C_RUN_PERIOD_P RETURN VARCHAR2 IS
374 BEGIN
375 RETURN C_RUN_PERIOD;
376 END C_RUN_PERIOD_P;
377
378 FUNCTION C_EXPND_ITEM_DATE_P RETURN DATE IS
379 BEGIN
380 RETURN C_EXPND_ITEM_DATE;
381 END C_EXPND_ITEM_DATE_P;
382
383 END PA_PAXALRUN_XMLP_PKG;
384
[Home] [Help]