1 PACKAGE BODY ONT_OEXOEUBD_XMLP_PKG AS
2 /* $Header: OEXOEUBDB.pls 120.1 2007/12/25 07:26:50 npannamp noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
9 /*SRW.MESSAGE(1
10 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
11 RETURN (FALSE);
12 END;
13 RETURN (TRUE);
14 END AFTERREPORT;
15
16 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
17 BEGIN
18 RETURN (TRUE);
19 END P_ITEM_FLEX_CODEVALIDTRIGGER;
20
21 FUNCTION AFTERPFORM RETURN BOOLEAN IS
22 BEGIN
23 BEGIN
24 SELECT
25 MEANING
26 INTO LP_ORDER_BY_MEAN
27 FROM
28 OE_LOOKUPS
29 WHERE LOOKUP_CODE = UPPER(P_ORDER_BY)
30 AND LOOKUP_TYPE = 'ONT_OEXOEUBD_XMLP_PKG SORT BY';
31 IF (UPPER(P_ORDER_BY) = 'CREATED_BY') THEN
32 LP_ORDER_BY := 'order by 5,2,1';
33 ELSIF (UPPER(P_ORDER_BY) = 'MANAGER') THEN
34 LP_ORDER_BY := 'order by 4,2,1';
35 ELSIF (SUBSTR(UPPER(P_ORDER_BY)
36 ,1
37 ,1) = 'O') THEN
38 LP_ORDER_BY := 'ORDER BY 10 ASC,order_number';
39 --LP_ORDER_BY := 'ORDER BY 10 ASC,16 ASC, 1,2,3,4,5,6,7,8,15,9';
40 END IF;
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 LP_ORDER_BY_MEAN := P_ORDER_BY;
44 END;
45 BEGIN
46 IF (P_ORDER_DATE_LOW IS NOT NULL) AND (P_ORDER_DATE_HIGH IS NOT NULL) THEN
47 LP_ORDER_DATE := 'and h.ordered_date between :P_order_date_low and (:P_order_date_high) + 1';
48 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
49 LP_ORDER_DATE := 'and h.ordered_date >= :P_order_date_low';
50 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
51 LP_ORDER_DATE := 'and h.ordered_date <= (:P_order_date_high) + 1';
52 END IF;
53 END;
54 BEGIN
55 IF (P_CREATED_BY_LOW IS NOT NULL) AND (P_CREATED_BY_HIGH IS NOT NULL) THEN
56 LP_CREATED_BY := 'and fu.user_name between :P_created_by_low and :P_created_by_high ';
57 ELSIF (P_CREATED_BY_LOW IS NOT NULL) THEN
58 LP_CREATED_BY := 'and fu.user_name >= :P_created_by_low ';
59 ELSIF (P_CREATED_BY_HIGH IS NOT NULL) THEN
60 LP_CREATED_BY := 'and fu.user_name <= :P_created_by_high ';
61 END IF;
62 END;
63 BEGIN
64 IF (P_MANAGER_LOW IS NOT NULL) AND (P_MANAGER_HIGH IS NOT NULL) THEN
65 LP_MANAGER := 'and ppf_mgr.email_address between :p_manager_low and :p_manager_high ';
66 ELSIF (P_MANAGER_LOW IS NOT NULL) THEN
67 LP_MANAGER := 'and ppf_mgr.email_address >= :p_manager_low ';
68 ELSIF (P_MANAGER_HIGH IS NOT NULL) THEN
69 LP_MANAGER := 'and ppf_mgr.email_address <= :p_manager_high ';
70 END IF;
71 END;
72 BEGIN
73 IF (P_ORDER_TYPE_LOW IS NOT NULL) AND (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
74 LP_ORDER_TYPE := 'and ( ot.transaction_type_id between :p_order_type_low and :p_order_type_high ) ';
75 SELECT
76 OEOT.NAME
77 INTO L_ORDER_TYPE_LOW
78 FROM
79 OE_TRANSACTION_TYPES_TL OEOT
80 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
81 AND OEOT.LANGUAGE = USERENV('LANG');
82 SELECT
83 OEOT.NAME
84 INTO L_ORDER_TYPE_HIGH
85 FROM
86 OE_TRANSACTION_TYPES_TL OEOT
87 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
88 AND OEOT.LANGUAGE = USERENV('LANG');
89 ELSIF (P_ORDER_TYPE_LOW IS NOT NULL) THEN
90 LP_ORDER_TYPE := 'and ot.transaction_type_id >= :p_order_type_low ';
91 SELECT
92 OEOT.NAME
93 INTO L_ORDER_TYPE_LOW
94 FROM
95 OE_TRANSACTION_TYPES_TL OEOT
96 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
97 AND OEOT.LANGUAGE = USERENV('LANG');
98 ELSIF (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
99 LP_ORDER_TYPE := 'and ot.transaction_type_id <= :p_order_type_high ';
100 SELECT
101 OEOT.NAME
102 INTO L_ORDER_TYPE_HIGH
103 FROM
104 OE_TRANSACTION_TYPES_TL OEOT
105 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
106 AND OEOT.LANGUAGE = USERENV('LANG');
107 END IF;
108 END;
109 BEGIN
110 IF (P_USE_FUNCTIONAL_CURRENCY = 'Y') THEN
111 L_USE_FUNCTIONAL_CURRENCY := 'Yes';
112 ELSIF (P_USE_FUNCTIONAL_CURRENCY = 'N') THEN
113 L_USE_FUNCTIONAL_CURRENCY := 'No';
114 END IF;
115 END;
116 RETURN (TRUE);
117 END AFTERPFORM;
118
119 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
120 BEGIN
121 RETURN NULL;
122 END RP_ORDER_CATEGORYFORMULA;
123
124 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
125 BEGIN
126 RETURN NULL;
127 END RP_LINE_CATEGORYFORMULA;
128
129 FUNCTION C_LINE_AMTFORMULA(TRANSACTIONAL_CURR_CODE IN VARCHAR2
130 ,LINE_AMT IN NUMBER
131 ,CONVERSION_TYPE_CODE IN VARCHAR2
132 ,ORDERED_DATE IN DATE
133 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
134 BEGIN
135 DECLARE
136 L_CONVERSION_RATE NUMBER(15,3);
137 BEGIN
138 /*SRW.REFERENCE(TRANSACTIONAL_CURR_CODE)*/NULL;
139 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
140 /*SRW.REFERENCE(LINE_AMT)*/NULL;
141 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
142 /*SRW.REFERENCE(ORDERED_DATE)*/NULL;
143 L_CONVERSION_RATE := 0;
144 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
145 RETURN (NVL(LINE_AMT
146 ,0));
147 ELSIF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
148 IF TRANSACTIONAL_CURR_CODE = RP_FUNCTIONAL_CURRENCY THEN
149 L_CONVERSION_RATE := 1;
150 ELSE
151 IF CONVERSION_RATE IS NULL THEN
152 L_CONVERSION_RATE := GL_CURRENCY_API.GET_RATE(P_SOB_ID
153 ,TRANSACTIONAL_CURR_CODE
154 ,ORDERED_DATE
155 ,CONVERSION_TYPE_CODE);
156 ELSE
157 L_CONVERSION_RATE := CONVERSION_RATE;
158 END IF;
159 END IF;
160 RETURN (NVL(L_CONVERSION_RATE
161 ,0) * NVL(LINE_AMT
162 ,0));
163 END IF;
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 RETURN (0);
167 WHEN OTHERS THEN
168 RETURN (0);
169 END;
170 END C_LINE_AMTFORMULA;
171
172 FUNCTION C_PRECISIONFORMULA(TRANSACTIONAL_CURR_CODE IN VARCHAR2) RETURN NUMBER IS
173 BEGIN
174 DECLARE
175 W_PRECISION NUMBER;
176 BEGIN
177 SELECT
178 PRECISION
179 INTO W_PRECISION
180 FROM
181 FND_CURRENCIES
182 WHERE CURRENCY_CODE = TRANSACTIONAL_CURR_CODE;
183 RETURN (W_PRECISION);
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 W_PRECISION := 2;
187 RETURN (W_PRECISION);
188 END;
189 RETURN NULL;
190 END C_PRECISIONFORMULA;
191
192 FUNCTION C_RECUR_CHARGESFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
193 L_UOM_CLASS VARCHAR2(50) := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
194 L_CHARGE_PERIODICITY VARCHAR2(25);
195 BEGIN
196 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
197 SELECT
198 UNIT_OF_MEASURE
199 INTO L_CHARGE_PERIODICITY
200 FROM
201 MTL_UNITS_OF_MEASURE_VL
202 WHERE UOM_CODE = CHARGE_PERIODICITY_CODE
203 AND UOM_CLASS = L_UOM_CLASS;
204 RETURN L_CHARGE_PERIODICITY;
205 ELSE
206 RETURN (P_ONE_TIME);
207 END IF;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 RETURN NULL;
211 END C_RECUR_CHARGESFORMULA;
212
213 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
214 BEGIN
215 RETURN RP_REPORT_NAME;
216 END RP_REPORT_NAME_P;
217
218 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
219 BEGIN
220 RETURN RP_SUB_TITLE;
221 END RP_SUB_TITLE_P;
222
223 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
224 BEGIN
225 RETURN RP_COMPANY_NAME;
226 END RP_COMPANY_NAME_P;
227
228 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
229 BEGIN
230 RETURN RP_FUNCTIONAL_CURRENCY;
231 END RP_FUNCTIONAL_CURRENCY_P;
232
233 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
234 BEGIN
235 RETURN RP_DATA_FOUND;
236 END RP_DATA_FOUND_P;
237
238 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
239 BEGIN
240 RETURN RP_ITEM_FLEX_ALL_SEG;
241 END RP_ITEM_FLEX_ALL_SEG_P;
242
243 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
244 BEGIN
245 RETURN RP_PRINT_DESCRIPTION;
246 END RP_PRINT_DESCRIPTION_P;
247
248 FUNCTION RP_C_EXT_TOTAL_ROUNDED_P RETURN NUMBER IS
249 BEGIN
250 RETURN RP_C_EXT_TOTAL_ROUNDED;
251 END RP_C_EXT_TOTAL_ROUNDED_P;
252
253 FUNCTION RP_C_RECUR_CHARGE_ROUNDED_P RETURN NUMBER IS
254 BEGIN
255 RETURN RP_C_RECUR_CHARGE_ROUNDED;
256 END RP_C_RECUR_CHARGE_ROUNDED_P;
257
258 function BeforeReport return boolean is
259 begin
260
261 --ADDED AS FIX TO IMPLEMENT UNCONVERTED FORMAT TRIGGER
262 F_Periodicity := OE_Sys_Parameters.Value('RECURRING_CHARGES',mo_global.get_current_org_id());
263 --added for proper date formats
264 P_ORDER_DATE_LOW_V:=to_char(P_ORDER_DATE_LOW,'DD-MON-YY');
265 P_ORDER_DATE_HIGH_V:=to_char(P_ORDER_DATE_HIGH,'DD-MON-YY');
266
267
268 DECLARE
269 BEGIN
270
271 /*BEGIN
272 --SRW.USER_EXIT('FND SRWINIT');
273 EXCEPTION
274 WHEN SRW.USER_EXIT_FAILURE THEN
275 SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger');
276 return (FALSE);
277 END;*/
278
279
280 BEGIN /*MOAC*/
281
282 --P_ORGANIZATION_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
283 P_ORGANIZATION_ID_V:= MO_GLOBAL.GET_CURRENT_ORG_ID();
284
285 END;
286
287
288 /*------------------------------------------------------------------------------
289 Following PL/SQL block gets the company name, functional currency and precision.
290 ------------------------------------------------------------------------------*/
291
292
293 DECLARE
294 l_company_name VARCHAR2 (100);
295 l_functional_currency VARCHAR2 (15);
296
297 BEGIN
298
299 SELECT sob.name ,
300 sob.currency_code
301 INTO
302 l_company_name ,
303 l_functional_currency
304 FROM gl_sets_of_books sob,
305 fnd_currencies cur
306 WHERE sob.set_of_books_id = p_sob_id
307 AND sob.currency_code = cur.currency_code
308 ;
309
310 rp_company_name := l_company_name;
311 rp_functional_currency := l_functional_currency ;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 NULL ;
316 END ;
317
318 /*------------------------------------------------------------------------------
319 Following PL/SQL block gets the report name for the passed concurrent request Id.
320 ------------------------------------------------------------------------------*/
321 DECLARE
322 l_report_name VARCHAR2(240);
323 BEGIN
324 SELECT cp.user_concurrent_program_name
325 INTO l_report_name
326 FROM FND_CONCURRENT_PROGRAMS_VL cp,
327 FND_CONCURRENT_REQUESTS cr
328 WHERE cr.request_id = P_CONC_REQUEST_ID
329 AND cp.application_id = cr.program_application_id
330 AND cp.concurrent_program_id = cr.concurrent_program_id
331 ;
332
333 RP_Report_Name := l_report_name;
334 EXCEPTION
335 WHEN NO_DATA_FOUND
336 THEN RP_REPORT_NAME := 'Unbooked Orders Detail Report';
337 END;
338
339 /*------------------------------------------------------------------------------
340 Following PL/SQL block builds up the lexical parameters, to be used in the
341 WHERE clause of the query. This also populates the report level variables, used
342 to store the flexfield structure.
343 ------------------------------------------------------------------------------*/
344 /* BEGIN
345 -- SRW.REFERENCE(:P_item_flex_code);
346 -- SRW.REFERENCE(:P_item_structure_num);
347
348
349 SRW.USER_EXIT('FND FLEXSQL CODE=":P_item_flex_code"
350 NUM=":P_ITEM_STRUCTURE_NUM"
351 APPL_SHORT_NAME="INV"
352 OUTPUT=":rp_item_flex_all_seg"
353 MODE="SELECT"
354 DISPLAY="ALL"
355 TABLEALIAS="MSI"
356 ');
357
358 EXCEPTION
359 WHEN SRW.USER_EXIT_FAILURE THEN
360 srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT');
361 END;*/
362
363
364 DECLARE
365 l_meaning VARCHAR2 (80);
366 BEGIN
367 SELECT MEANING
368 INTO l_meaning
369 FROM OE_LOOKUPS
370 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
371 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
372 ;
373
374 rp_print_description := l_meaning ;
375 EXCEPTION WHEN NO_DATA_FOUND THEN
376 rp_print_description := 'Internal Item Description';
377 when OTHERS then
378 --srw.message(2000,'Failed in BEFORE REPORT trigger. Get Print Description');
379 null;
380
381 END ;
382
383
384
385
386 /*------------------------------------------------------------------------------
387 THE Following PL/SQL block populates the order_date_range and created_by range
388 parameters used in the report margins
389 ------------------------------------------------------------------------------*/
390 DECLARE
391 l_created_by_low VARCHAR2(50);
392 l_manager_low VARCHAR2(50);
393
394 BEGIN
395 if (P_created_by_low is NOT NULL OR P_created_by_high is NOT NULL) then
396 if (P_created_by_low is NULL) then
397 l_created_by_low := ' ';
398 else l_created_by_low := P_created_by_low;
399 end if;
400 lp_created_by_range := 'From '||l_Created_by_low||' To '||P_created_by_high;
401 end if;
402
403 if (p_manager_low is NOT NULL OR P_manager_high is NOT NULL) then
404 if (p_manager_low is NULL) then
405 l_manager_low := ' ';
406 else l_manager_low := p_manager_low;
407 end if;
408 lp_manager_range := 'Manager From '||l_manager_low||' To '||P_manager_high;
409 end if;
410
411 if (P_order_date_low is NOT NULL OR P_order_date_high is NOT NULL) then
412 lp_order_date_range := 'From '||nvl(to_char(P_order_date_low, 'DD-MON-RRRR'), ' ')
413 || ' To ' ||nvl(to_char(P_order_date_high, 'DD-MON-RRRR'), ' ');
414 end if;
415 END;
416
417 DECLARE
418 l_order_type_low VARCHAR2 (50);
419 l_order_type_high VARCHAR2 (50);
420 BEGIN
421
422 if ( p_order_type_low is NULL) AND ( p_order_type_high is NULL ) then
423 NULL ;
424 else
425 if p_order_type_low is NULL then
426 l_order_type_low := ' ';
427 else
428 l_order_type_low := substr(l_order_type_low ,1,18);
429 end if ;
430 if p_order_type_high is NULL then
431 l_order_type_high := ' ';
432 else
433 l_order_type_high := substr(l_order_type_high,1,18);
434 end if ;
435 lp_order_type_range := 'Order Type From '||l_order_type_low||' To '||l_order_type_high ;
436
437 end if ;
438 END;
439
440
441 END ;
442 return (TRUE);
443 end;
444
445 END ONT_OEXOEUBD_XMLP_PKG;
446
447
[Home] [Help]