1 PACKAGE BODY RLM_RLMDPDER_XMLP_PKG AS
2 /* $Header: RLMDPDERB.pls 120.0 2008/01/25 09:40:12 krreddy noship $ */
3 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 END BETWEENPAGE;
7
8 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
9 BEGIN
10 RETURN (TRUE);
11 END BEFOREPFORM;
12
13 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
14 P_DATE_FROM VARCHAR2(30) := NULL;
15 P_DATE_TO VARCHAR2(30) := NULL;
16 L_CURRENT_ORG_ID NUMBER;
17 L_OU_NAME VARCHAR2(240);
18 BEGIN
19 BEGIN
20 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
21 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
22 EXCEPTION
23 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
24 /*SRW.MESSAGE(1000
25 ,'Failed in BEFORE REPORT trigger')*/NULL;
26 RETURN (FALSE);
27 END;
28 L_CURRENT_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
29 IF (L_CURRENT_ORG_ID IS NULL AND P_ORG_ID IS NOT NULL) THEN
30 MO_GLOBAL.SET_POLICY_CONTEXT(P_ACCESS_MODE => 'S'
31 ,P_ORG_ID => P_ORG_ID);
32 L_CURRENT_ORG_ID := P_ORG_ID;
33 END IF;
34 L_OU_NAME := FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(L_CURRENT_ORG_ID);
35 CP_DEFAULT_OU := L_OU_NAME;
36 CP_ORG_ID := L_CURRENT_ORG_ID;
37 BEGIN
38 P_DATE_FROM := TO_CHAR(P_CREATION_DATE
39 ,'DD-MON-RRRR');
40 P_DATE_TO := TO_CHAR(P_CREATION_DATE_TO
41 ,'DD-MON-RRRR');
42 IF (P_WHERE_CLAUSE IS NULL) THEN
43 P_WHERE_CLAUSE := ' ';
44
45 END IF;
46 IF (P_WHERE_CLAUSE1 IS NULL) THEN
47 P_WHERE_CLAUSE1 := ' ';
48
49 END IF;
50 IF (P_WHERE_CLAUSE2 IS NULL) THEN
51 P_WHERE_CLAUSE2 := ' ';
52
53 END IF;
54
55 IF (P_REQUEST_ID_FROM IS NOT NULL) THEN
56 P_WHERE_CLAUSE := ' and e.request_id >= ' || TO_CHAR(P_REQUEST_ID_FROM);
57
58 END IF;
59 IF (P_REQUEST_ID_TO IS NOT NULL) THEN
60 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.request_id <= ' || TO_CHAR(P_REQUEST_ID_TO);
61 END IF;
62 IF (P_SHIP_FROM_ORG IS NOT NULL) THEN
63 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.cust_ship_from_org_ext like ' || '''' || P_SHIP_FROM_ORG || '''';
64 END IF;
65 IF (P_CUSTOMER_FROM IS NOT NULL) THEN
66 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.cust_name_ext >= ' || '''' || P_CUSTOMER_FROM || '''';
67 END IF;
68 IF (P_CUSTOMER_TO IS NOT NULL) THEN
69 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.cust_name_ext <= ' || '''' || P_CUSTOMER_TO || '''';
70 END IF;
71 IF (P_TRADING_PARTNER_FROM IS NOT NULL) THEN
72 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.ece_tp_translator_code >= ' || '''' || P_TRADING_PARTNER_FROM || '''';
73 END IF;
74 IF (P_TRADING_PARTNER_TO IS NOT NULL) THEN
75 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.ece_tp_translator_code <= ' || '''' || P_TRADING_PARTNER_TO || '''';
76 END IF;
77 IF (P_TP_LOCATION IS NOT NULL) THEN
78 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.ece_tp_location_code_ext like ' || '''' || P_TP_LOCATION || '''';
79 END IF;
80 IF (P_CUSTOMER_ITEM_FROM IS NOT NULL) THEN
81 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.customer_item_ext >= ' || '''' || P_CUSTOMER_ITEM_FROM || '''';
82 END IF;
83 IF (P_CUSTOMER_ITEM_TO IS NOT NULL) THEN
84 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.customer_item_ext <= ' || '''' || P_CUSTOMER_ITEM_TO || '''';
85 END IF;
86 IF (P_ITEM_FROM IS NOT NULL) THEN
87 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.inventory_item >= ' || '''' || P_ITEM_FROM || '''';
88 END IF;
89 IF (P_ITEM_TO IS NOT NULL) THEN
90 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.inventory_item <= ' || '''' || P_ITEM_TO || '''';
91 END IF;
92 IF (P_ORDER_NUMBER_FROM IS NOT NULL) THEN
93 P_WHERE_CLAUSE1 := P_WHERE_CLAUSE1 || ' and oh.order_number >= ' || TO_CHAR(P_ORDER_NUMBER_FROM);
94 P_WHERE_CLAUSE2 := P_WHERE_CLAUSE2 || ' and e.exception_id = -1';
95 END IF;
96 IF (P_ORDER_NUMBER_TO IS NOT NULL) THEN
97 P_WHERE_CLAUSE2 := P_WHERE_CLAUSE2 || ' and e.exception_id = -1';
98 P_WHERE_CLAUSE1 := P_WHERE_CLAUSE1 || ' and oh.order_number <= ' || TO_CHAR(P_ORDER_NUMBER_TO);
99 END IF;
100 IF (P_ORDER_TYPE_FROM IS NOT NULL) THEN
101 P_WHERE_CLAUSE2 := P_WHERE_CLAUSE2 || ' and e.exception_id = -1';
102 P_WHERE_CLAUSE1 := P_WHERE_CLAUSE1 || ' and oh.order_type_id >= ' || TO_CHAR(P_ORDER_TYPE_FROM);
103 SELECT
104 NAME
105 INTO P_ORDER_TYPE_NAME_FROM
106 FROM
107 OE_ORDER_TYPES_V
108 WHERE ORDER_TYPE_ID = P_ORDER_TYPE_FROM;
109 END IF;
110 IF (P_ORDER_TYPE_TO IS NOT NULL) THEN
111 P_WHERE_CLAUSE2 := P_WHERE_CLAUSE2 || ' and e.exception_id = -1';
112 P_WHERE_CLAUSE1 := P_WHERE_CLAUSE1 || ' and oh.order_type_id <= ' || TO_CHAR(P_ORDER_TYPE_TO);
113 SELECT
114 NAME
115 INTO P_ORDER_TYPE_NAME_TO
116 FROM
117 OE_ORDER_TYPES_V
118 WHERE ORDER_TYPE_ID = P_ORDER_TYPE_TO;
119 END IF;
120 IF (P_SCHEDULE_NUMBER_FROM IS NOT NULL) THEN
121 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.schedule_reference_num >= ' || '''' || P_SCHEDULE_NUMBER_FROM || '''';
122 END IF;
123 IF (P_SCHEDULE_NUMBER_TO IS NOT NULL) THEN
124 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.schedule_reference_num <= ' || '''' || P_SCHEDULE_NUMBER_TO || '''';
125 END IF;
126 IF (P_CREATION_DATE IS NOT NULL) THEN
127 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and trunc(e.creation_date) >= to_date( ' || '''' || P_DATE_FROM || '''' || ',' || ' ''DD-MON-RRRR'' ' || ')';
128 END IF;
129 IF (P_CREATION_DATE_TO IS NOT NULL) THEN
130 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and trunc(e.creation_date) <= to_date( ' || '''' || P_DATE_TO || '''' || ',' || ' ''DD-MON-RRRR'' ' || ')';
131 END IF;
132 IF (P_EXCEPTION_SEVERITY IS NOT NULL) THEN
133 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and e.exception_level = ' || '''' || P_EXCEPTION_SEVERITY || '''';
134 END IF;
135 IF (P_MESSAGE_CATEGORY_FROM IS NOT NULL) THEN
136 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and msg.message_category >= ' || TO_CHAR(P_MESSAGE_CATEGORY_FROM);
137 END IF;
138 IF (P_MESSAGE_CATEGORY_TO IS NOT NULL) THEN
139 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and msg.message_category <= ' || TO_CHAR(P_MESSAGE_CATEGORY_TO);
140 END IF;
141 IF (P_SORT_BY IS NOT NULL) THEN
142 IF P_SORT_BY = 'CUSTOMER' THEN
143 P_ORDER_BY := 'order by customer_name';
144 ELSIF P_SORT_BY = 'CUSTOMER_ITEM' THEN
145 P_ORDER_BY := 'order by customer item';
146 ELSIF P_SORT_BY = 'MESSAGE_CATEGORY' THEN
147 P_ORDER_BY := 'order by message_category';
148 ELSIF P_SORT_BY = 'ORDER_NUMBER' THEN
149 P_ORDER_BY := 'order by oe_order_number';
150 ELSIF P_SORT_BY = 'REQUEST_ID' THEN
151 P_ORDER_BY := 'order by request_id';
152 ELSIF P_SORT_BY = 'SCHEDULE_NUMBER' THEN
153 P_ORDER_BY := 'order by schedule_num';
154 ELSIF P_SORT_BY = 'SHIP_FROM' THEN
155 P_ORDER_BY := 'order by ship_from';
156 ELSIF P_SORT_BY = 'SHIP_TO' THEN
157 P_ORDER_BY := 'order by ship_to';
158 ELSIF P_SORT_BY = 'TP_LOCATION' THEN
159 P_ORDER_BY := 'order by tp_location';
163 END IF;
160 ELSIF P_SORT_BY = 'TRADING_PARTNER' THEN
161 P_ORDER_BY := 'order by trading_partner';
162 END IF;
164 END;
165 RETURN (TRUE);
166 END BEFOREREPORT;
167
168 FUNCTION CF_QUANTITY_TYPEFORMULA(QTY_TYPE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
169 BEGIN
170 DECLARE
171 X_QTY_TYPE VARCHAR2(8);
172 BEGIN
173 IF QTY_TYPE_CODE = 'CUMULATIVE' THEN
174 X_QTY_TYPE := 'Yes';
175 ELSE
176 X_QTY_TYPE := 'No';
177 END IF;
178 RETURN X_QTY_TYPE;
179 EXCEPTION
180 WHEN OTHERS THEN
181 RETURN '-1';
182 END;
183 RETURN NULL;
184 END CF_QUANTITY_TYPEFORMULA;
185
186 FUNCTION CF_ITEM_DETAIL_TYPEFORMULA(ITEM_DETAIL_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
187 BEGIN
188 DECLARE
189 X_ITEM_DETAIL_TYPE VARCHAR2(80);
190 CURSOR LOOKUP_CUR IS
191 SELECT
192 MEANING
193 FROM
194 FND_LOOKUPS
195 WHERE LOOKUP_TYPE = 'RLM_DETAIL_TYPE_CODE'
196 AND LOOKUP_CODE = ITEM_DETAIL_TYPE;
197 BEGIN
198 IF LOOKUP_CUR%ISOPEN THEN
199 CLOSE LOOKUP_CUR;
200 END IF;
201 OPEN LOOKUP_CUR;
202 FETCH LOOKUP_CUR
203 INTO X_ITEM_DETAIL_TYPE;
204 CLOSE LOOKUP_CUR;
205 RETURN X_ITEM_DETAIL_TYPE;
206 EXCEPTION
207 WHEN OTHERS THEN
208 RETURN '-1';
209 END;
210 RETURN NULL;
211 END CF_ITEM_DETAIL_TYPEFORMULA;
212
213 FUNCTION CF_ITEM_DETAIL_SUBTYPEFORMULA(ITEM_DETAIL_SUBTYPE IN VARCHAR2) RETURN VARCHAR2 IS
214 BEGIN
215 DECLARE
216 X_ITEM_DETAIL_SUBTYPE VARCHAR2(80);
217 CURSOR LOOKUP_CUR IS
218 SELECT
219 MEANING
220 FROM
221 FND_LOOKUPS
222 WHERE LOOKUP_TYPE = 'RLM_DETAIL_SUBTYPE_CODE'
223 AND LOOKUP_CODE = ITEM_DETAIL_SUBTYPE;
224 BEGIN
225 IF LOOKUP_CUR%ISOPEN THEN
226 CLOSE LOOKUP_CUR;
227 END IF;
228 OPEN LOOKUP_CUR;
229 FETCH LOOKUP_CUR
230 INTO X_ITEM_DETAIL_SUBTYPE;
231 CLOSE LOOKUP_CUR;
232 RETURN X_ITEM_DETAIL_SUBTYPE;
233 EXCEPTION
234 WHEN OTHERS THEN
235 RETURN '-1';
236 END;
237 RETURN NULL;
238 END CF_ITEM_DETAIL_SUBTYPEFORMULA;
239
240 FUNCTION CF_ORDER_TYPEFORMULA(ORDER_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
241 BEGIN
242 DECLARE
243 X_ORDER_TYPE VARCHAR2(80);
244 CURSOR ORDER_TYPE_CUR IS
245 SELECT
246 NAME
247 FROM
248 OE_ORDER_TYPES_V
249 WHERE ORDER_TYPE_ID = ORDER_TYPE_ID;
250 BEGIN
251 IF ORDER_TYPE_CUR%ISOPEN THEN
252 CLOSE ORDER_TYPE_CUR;
253 END IF;
254 OPEN ORDER_TYPE_CUR;
255 FETCH ORDER_TYPE_CUR
256 INTO X_ORDER_TYPE;
257 CLOSE ORDER_TYPE_CUR;
258 RETURN X_ORDER_TYPE;
259 EXCEPTION
260 WHEN OTHERS THEN
261 RETURN '-1';
262 END;
263 RETURN NULL;
264 END CF_ORDER_TYPEFORMULA;
265
266 FUNCTION AFTERREPORT RETURN BOOLEAN IS
267 BEGIN
268 BEGIN
269 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
270 EXCEPTION
271 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
272 /*SRW.MESSAGE(1
273 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
274 RETURN (FALSE);
275 END;
276 RETURN (TRUE);
277 END AFTERREPORT;
278
279 FUNCTION CF_SCHEDULE_TYPEFORMULA(SCHEDULE_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
280 BEGIN
281 DECLARE
282 X_SCHEDULE_TYPE VARCHAR2(80);
283 BEGIN
284 SELECT
285 MEANING
286 INTO X_SCHEDULE_TYPE
287 FROM
288 FND_LOOKUPS
289 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
290 AND LOOKUP_CODE = SCHEDULE_TYPE;
291 RETURN X_SCHEDULE_TYPE;
292 EXCEPTION
293 WHEN OTHERS THEN
294 RETURN '-1';
295 END;
296 RETURN NULL;
297 END CF_SCHEDULE_TYPEFORMULA;
298
299 FUNCTION CF_SCHEDULE_PURPOSEFORMULA(SCHEDULE_PURPOSE IN VARCHAR2) RETURN VARCHAR2 IS
300 BEGIN
301 DECLARE
302 X_SCHEDULE_PURPOSE VARCHAR2(80);
303 BEGIN
304 SELECT
305 MEANING
306 INTO X_SCHEDULE_PURPOSE
307 FROM
308 FND_LOOKUPS
309 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_PURPOSE'
310 AND LOOKUP_CODE = SCHEDULE_PURPOSE;
311 RETURN X_SCHEDULE_PURPOSE;
312 EXCEPTION
313 WHEN OTHERS THEN
314 RETURN '-1';
315 END;
316 RETURN NULL;
317 END CF_SCHEDULE_PURPOSEFORMULA;
318
319 FUNCTION CF_SCHEDULE_SOURCEFORMULA(SCHEDULE_SOURCE IN VARCHAR2) RETURN VARCHAR2 IS
320 BEGIN
321 DECLARE
322 X_SCHEDULE_SOURCE VARCHAR2(25);
323 BEGIN
324 IF SCHEDULE_SOURCE = 'MANUAL' THEN
325 SELECT
326 MEANING
327 INTO X_SCHEDULE_SOURCE
328 FROM
329 FND_LOOKUPS
330 WHERE LOOKUP_TYPE = 'RLM_SCHEDULE_SOURCE'
331 AND LOOKUP_CODE = SCHEDULE_SOURCE;
332 ELSE
333 X_SCHEDULE_SOURCE := SCHEDULE_SOURCE;
334 END IF;
335 RETURN X_SCHEDULE_SOURCE;
336 EXCEPTION
337 WHEN OTHERS THEN
338 RETURN '-1';
339 END;
340 RETURN NULL;
341 END CF_SCHEDULE_SOURCEFORMULA;
342
343 FUNCTION CP_DEFAULT_OU_P RETURN VARCHAR2 IS
344 BEGIN
345 RETURN CP_DEFAULT_OU;
346 END CP_DEFAULT_OU_P;
347
348 FUNCTION CP_ORG_ID_P RETURN NUMBER IS
349 BEGIN
350 RETURN CP_ORG_ID;
351 END CP_ORG_ID_P;
352
353 END RLM_RLMDPDER_XMLP_PKG;
[Home] [Help]