1 PACKAGE BODY RLM_RLMDPPSR_XMLP_PKG AS
2 /* $Header: RLMDPPSRB.pls 120.1 2008/02/12 13:26:12 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_CURRENT_ORG_ID NUMBER;
5 L_OU_NAME VARCHAR2(240);
6 BEGIN
7 BEGIN
8 BEGIN
9 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
10 M_2 := M_2FORMATTRIGGER;
11 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
12 EXCEPTION
13 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
14 /*SRW.MESSAGE(1000
15 ,'Failed in BEFORE REPORT trigger')*/NULL;
16 RETURN (FALSE);
17 END;
18 L_CURRENT_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
19 IF (L_CURRENT_ORG_ID IS NULL AND P_ORG_ID IS NOT NULL) THEN
20 MO_GLOBAL.SET_POLICY_CONTEXT(P_ACCESS_MODE => 'S'
21 ,P_ORG_ID => P_ORG_ID);
22 L_CURRENT_ORG_ID := P_ORG_ID;
23 END IF;
24 L_OU_NAME := FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(L_CURRENT_ORG_ID);
25 CP_DEFAULT_OU := L_OU_NAME;
26 IF (P_REQUEST_ID IS NOT NULL) THEN
27 P_WHERE_CLAUSE := ' and e.request_id = ' || P_REQUEST_ID;
28 END IF;
29 END;
30 RETURN (TRUE);
31 END BEFOREREPORT;
32
33 FUNCTION AFTERREPORT RETURN BOOLEAN IS
34 BEGIN
35 BEGIN
36 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
37 EXCEPTION
38 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
39 /*SRW.MESSAGE(1
40 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
41 RETURN (FALSE);
42 END;
43 IF (P_REQUEST_ID IS NOT NULL) THEN
44 DELETE FROM RLM_DEMAND_EXCEPTIONS
45 WHERE REQUEST_ID = P_REQUEST_ID
46 AND PURGE_STATUS is not null;
47 COMMIT;
48 END IF;
49 RETURN (TRUE);
50 END AFTERREPORT;
51
52 FUNCTION CF_SCHEDULE_TYPEFORMULA(SCHEDULE_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
53 BEGIN
54 DECLARE
55 X_SCHEDULE_TYPE VARCHAR2(80);
56 BEGIN
57 SELECT
58 MEANING
59 INTO X_SCHEDULE_TYPE
60 FROM
61 FND_LOOKUPS
62 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
63 AND LOOKUP_CODE = SCHEDULE_TYPE;
64 RETURN X_SCHEDULE_TYPE;
65 EXCEPTION
66 WHEN OTHERS THEN
67 RETURN '-1';
68 END;
69 RETURN NULL;
70 END CF_SCHEDULE_TYPEFORMULA;
71
72 FUNCTION CF_PURGE_STATUSFORMULA(PURGE_STATUS IN VARCHAR2) RETURN VARCHAR2 IS
73 BEGIN
74 DECLARE
75 V_PURGE_STATUS VARCHAR(20);
76 X_YES VARCHAR2(20);
77 BEGIN
78 IF (P_EXECUTION_MODE = 'V') THEN
79 SELECT
80 MEANING
81 INTO X_YES
82 FROM
83 FND_LOOKUPS
84 WHERE LOOKUP_TYPE = 'YES_NO'
85 AND LOOKUP_CODE = 'N';
86 RETURN X_YES;
87 ELSE
88 SELECT
89 MEANING
90 INTO X_YES
91 FROM
92 FND_LOOKUPS
93 WHERE LOOKUP_TYPE = 'YES_NO'
94 AND LOOKUP_CODE = PURGE_STATUS;
95 RETURN X_YES;
96 END IF;
97 END;
98 RETURN NULL;
99 END CF_PURGE_STATUSFORMULA;
100
101 FUNCTION CF_1FORMULA RETURN NUMBER IS
102 BEGIN
103 DECLARE
104 V_COUNT NUMBER;
105 V_COUNT1 NUMBER;
106 V_COUNT2 NUMBER;
107 BEGIN
108 SELECT
109 count(distinct SCHEDULE_HEADER_ID)
110 INTO V_COUNT1
111 FROM
112 RLM_DEMAND_EXCEPTIONS
113 WHERE REQUEST_ID = P_REQUEST_ID
114 AND ( MESSAGE_NAME like '%SUCCESS%'
115 OR MESSAGE_NAME like '%PURGABLE%'
116 OR MESSAGE_NAME IS NULL );
117 SELECT
118 count(distinct SCHEDULE_HEADER_ID)
119 INTO V_COUNT2
120 FROM
121 RLM_DEMAND_EXCEPTIONS
122 WHERE REQUEST_ID = P_REQUEST_ID
123 AND MESSAGE_NAME NOT like '%SUCCESS%'
127 RETURN V_COUNT;
124 AND MESSAGE_NAME NOT like '%PURGABLE%'
125 AND MESSAGE_NAME IS NOT NULL;
126 V_COUNT := V_COUNT1 + V_COUNT2;
128 END;
129 RETURN NULL;
130 END CF_1FORMULA;
131
132 FUNCTION CF_COUNT2FORMULA RETURN NUMBER IS
133 BEGIN
134 DECLARE
135 V_COUNT NUMBER;
136 BEGIN
137 SELECT
138 count(distinct SCHEDULE_HEADER_ID)
139 INTO V_COUNT
140 FROM
141 RLM_DEMAND_EXCEPTIONS
142 WHERE REQUEST_ID = P_REQUEST_ID
143 AND ( MESSAGE_NAME like '%SUCCESS%'
144 OR MESSAGE_NAME like '%PURGABLE%'
145 OR MESSAGE_NAME IS NULL );
146 RETURN V_COUNT;
147 END;
148 RETURN NULL;
149 END CF_COUNT2FORMULA;
150
151 FUNCTION CF_COUNT3FORMULA RETURN NUMBER IS
152 BEGIN
153 DECLARE
154 V_COUNT NUMBER;
155 BEGIN
156 SELECT
157 count(distinct SCHEDULE_HEADER_ID)
158 INTO V_COUNT
159 FROM
160 RLM_DEMAND_EXCEPTIONS
161 WHERE REQUEST_ID = P_REQUEST_ID
162 AND MESSAGE_NAME NOT like '%SUCCESS%'
163 AND MESSAGE_NAME NOT like '%PURGABLE%'
164 AND MESSAGE_NAME IS NOT NULL;
165 RETURN V_COUNT;
166 END;
167 RETURN NULL;
168 END CF_COUNT3FORMULA;
169
170 FUNCTION CF_1FORMULA0006 RETURN VARCHAR2 IS
171 BEGIN
172 DECLARE
173 V_REF VARCHAR2(35);
174 BEGIN
175 IF (P_SCHEDULE_REF_NO IS NOT NULL) THEN
176 SELECT
177 DISTINCT
178 SCHEDULE_REFERENCE_NUM
179 INTO V_REF
180 FROM
181 RLM_DEMAND_EXCEPTIONS
182 WHERE REQUEST_ID = P_REQUEST_ID;
183 RETURN V_REF;
184 ELSE
185 RETURN NULL;
186 END IF;
187 EXCEPTION
188 WHEN OTHERS THEN
189 RETURN '-1';
190 END;
191 RETURN NULL;
192 END CF_1FORMULA0006;
193
194 FUNCTION CF_1FORMULA0004 RETURN VARCHAR2 IS
195 BEGIN
196 DECLARE
197 V_REF VARCHAR2(40);
198 BEGIN
199 IF (P_SHIP_TO_ADDRESS_ID_FROM IS NOT NULL) THEN
200 SELECT
201 ACCT_SITE.ECE_TP_LOCATION_CODE
202 INTO V_REF
203 FROM
204 HZ_CUST_ACCT_SITES ACCT_SITE
205 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = P_SHIP_TO_ADDRESS_ID_FROM;
206 RETURN V_REF;
207 ELSE
208 RETURN NULL;
212 RETURN '-1';
209 END IF;
210 EXCEPTION
211 WHEN OTHERS THEN
213 END;
214 RETURN NULL;
215 END CF_1FORMULA0004;
216
217 FUNCTION CF_SHIP_TO_TOFORMULA RETURN VARCHAR2 IS
218 BEGIN
219 DECLARE
220 V_REF VARCHAR2(40);
221 BEGIN
222 IF (P_SHIP_TO_ADDRESS_ID_TO IS NOT NULL) THEN
223 SELECT
224 ACCT_SITE.ECE_TP_LOCATION_CODE
225 INTO V_REF
226 FROM
227 HZ_CUST_ACCT_SITES ACCT_SITE
228 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = P_SHIP_TO_ADDRESS_ID_TO;
229 RETURN V_REF;
230 ELSE
231 RETURN NULL;
232 END IF;
233 EXCEPTION
234 WHEN OTHERS THEN
235 RETURN '-1';
236 END;
237 RETURN NULL;
238 END CF_SHIP_TO_TOFORMULA;
239
240 FUNCTION CF_P_SCHED_TYPEFORMULA RETURN VARCHAR2 IS
241 BEGIN
242 DECLARE
243 X_SCHEDULE_TYPE VARCHAR2(80);
244 BEGIN
245 IF (P_SCHEDULE_TYPE IS NULL) THEN
246 RETURN NULL;
247 ELSE
248 SELECT
249 MEANING
250 INTO X_SCHEDULE_TYPE
251 FROM
252 FND_LOOKUPS
253 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
254 AND LOOKUP_CODE = P_SCHEDULE_TYPE;
255 RETURN X_SCHEDULE_TYPE;
256 END IF;
257 EXCEPTION
258 WHEN OTHERS THEN
259 RETURN '-1';
260 END;
261 RETURN NULL;
262 END CF_P_SCHED_TYPEFORMULA;
263
264 FUNCTION CF_EXECUTIONFORMULA RETURN VARCHAR2 IS
265 BEGIN
266 DECLARE
267 X_EXEC VARCHAR2(80);
268 BEGIN
269 SELECT
270 MEANING
271 INTO X_EXEC
272 FROM
273 FND_LOOKUPS
274 WHERE LOOKUP_TYPE = 'RLM_EXECUTION_MODE'
275 AND LOOKUP_CODE = P_EXECUTION_MODE;
276 RETURN X_EXEC;
277 EXCEPTION
278 WHEN OTHERS THEN
279 RETURN '-1';
280 END;
281 RETURN NULL;
282 END CF_EXECUTIONFORMULA;
283
284 FUNCTION CF_YES_NOFORMULA RETURN VARCHAR2 IS
285 BEGIN
286 DECLARE
287 X_YES VARCHAR2(80);
288 BEGIN
289 SELECT
290 MEANING
291 INTO X_YES
292 FROM
293 FND_LOOKUPS
294 WHERE LOOKUP_TYPE = 'YES_NO'
295 AND LOOKUP_CODE = P_AUTHORIZATION;
296 RETURN X_YES;
297 EXCEPTION
298 WHEN OTHERS THEN
299 RETURN '-1';
300 END;
301 RETURN NULL;
302 END CF_YES_NOFORMULA;
303
304 FUNCTION CF_PURGE_ELIGIBLEFORMULA(PURGE_STATUS IN VARCHAR2) RETURN VARCHAR2 IS
305 BEGIN
306 DECLARE
307 V_PURGE_STATUS VARCHAR(20);
308 X_YES VARCHAR2(20);
309 BEGIN
310 SELECT
311 MEANING
312 INTO X_YES
313 FROM
314 FND_LOOKUPS
315 WHERE LOOKUP_TYPE = 'YES_NO'
316 AND LOOKUP_CODE = PURGE_STATUS;
317 RETURN X_YES;
318 END;
319 RETURN NULL;
320 END CF_PURGE_ELIGIBLEFORMULA;
321
322 FUNCTION CF_STATUS RETURN VARCHAR2 IS
323 BEGIN
324 DECLARE
325 X_STATUS VARCHAR2(80);
326 BEGIN
327 SELECT
328 MEANING
329 INTO X_STATUS
330 FROM
331 FND_LOOKUPS
332 WHERE LOOKUP_TYPE = 'RLM_STATUS'
333 AND LOOKUP_CODE = P_STATUS;
334 RETURN X_STATUS;
335 EXCEPTION
336 WHEN OTHERS THEN
337 RETURN '-1';
338 END;
339 RETURN NULL;
340 END CF_STATUS;
341
342 FUNCTION CF_CUSTOMERFORMULA RETURN VARCHAR2 IS
343 BEGIN
344 DECLARE
345 V_CUSTOMER_NAME VARCHAR2(50);
346 BEGIN
347 IF (P_CUSTOMER IS NOT NULL) THEN
348 SELECT
349 SUBSTRB(PARTY.PARTY_NAME
350 ,1
351 ,50) CUSTOMER_NAME
352 INTO V_CUSTOMER_NAME
353 FROM
354 HZ_PARTIES PARTY,
355 HZ_CUST_ACCOUNTS CUST_ACCT
356 WHERE CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER
357 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
358 END IF;
359 RETURN V_CUSTOMER_NAME;
360 EXCEPTION
361 WHEN OTHERS THEN
362 RETURN '-1';
363 END;
364 END CF_CUSTOMERFORMULA;
365
366 FUNCTION CP_DEFAULT_OU_P RETURN VARCHAR2 IS
367 BEGIN
368 RETURN CP_DEFAULT_OU;
369 END CP_DEFAULT_OU_P;
370
371 FUNCTION M_2FORMATTRIGGER RETURN VARCHAR2 IS
372 BEGIN
373
374 DECLARE
375 V_COUNT NUMBER;
376
377
378 BEGIN
379
380 SELECT COUNT(*) INTO V_COUNT FROM RLM_DEMAND_EXCEPTIONS
381 WHERE REQUEST_ID = P_REQUEST_ID;
382
383 IF(V_COUNT > 0) THEN
384 RETURN ('Y');
385 ELSE
386 RETURN ('N');
387 END IF;
388
389
390 END;
391
392 RETURN ('N');
393
394 END;
395
396 END RLM_RLMDPPSR_XMLP_PKG;
[Home] [Help]