1 PACKAGE BODY ONT_OEXOEUBK_XMLP_PKG AS
2 /* $Header: OEXOEUBKB.pls 120.3 2008/05/05 06:39:13 dwkrishn noship $ */
3 function Item_dspFormula(inventory_item_id_L IN number,
4 item_identifier_type_L IN VARCHAR2,
5 ordered_item_id_L IN NUMBER,
6 ORGANIZATION_ID_L IN NUMBER,
7 ordered_item_L IN VARCHAR2) return Char is
8 v_item varchar2(2000);
9 v_description varchar2(500);
10 begin
11 if (inventory_item_id_L is null) then
12 return null;
13 end if;
14 if (item_identifier_type_L is null or item_identifier_type_L = 'INT')
15 or (p_print_description in ('I','D','F')) then
16 select sitems.concatenated_segments item,
17 sitems.description description
18 into v_item,v_description
19 from mtl_system_items_vl sitems
20 where sitems.customer_order_enabled_flag = 'Y'
21
22 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
23 and sitems.inventory_item_id = inventory_item_id_L;
24 /*
25 srw.reference (:p_item_flex_code);
26 srw.reference (:Item_dsp);
27 srw.reference (:p_item_structure_num);
28 --modified the use_exit to use item_flex to derive the value for
29 -- the item for FP bug 3693140
30 srw.user_exit (' FND FLEXIDVAL
31 CODE=":p_item_flex_code"
32 NUM=":p_item_structure_num"
33 APPL_SHORT_NAME="INV"
34 DATA= ":item_flex"
35 VALUE=":Item_dsp"
36 DISPLAY="ALL"'
37 ); */
38 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV',p_item_flex_code,p_item_structure_num,ORGANIZATION_ID_L,INVENTORY_ITEM_ID_L, 'ALL', 'Y', 'VALUE');
39 elsif (item_identifier_type_L = 'CUST' and p_print_description in ('C','P','O')) then
40 select citems.customer_item_number item,
41 nvl(citems.customer_item_desc,sitems.description) description
42 into v_item,v_description
43 from mtl_customer_items citems,
44 mtl_customer_item_xrefs cxref,
45 mtl_system_items_vl sitems
46 where citems.customer_item_id = cxref.customer_item_id
47 and cxref.inventory_item_id = sitems.inventory_item_id
48 and citems.customer_item_id = ordered_item_id_L
49 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
50 and sitems.inventory_item_id = inventory_item_id_L;
51 -- and sitems.customer_order_enabled_flag = 'Y'
52 -- and sitems.bom_item_type in (1,4)
53 elsif (p_print_description in ('C','P','O')) then
54 Begin
55 select items.cross_reference item,
56 nvl(items.description,sitems.description) description
57 into v_item,v_description
58 from mtl_cross_reference_types xtypes,
59 mtl_cross_references items,
60 mtl_system_items_vl sitems
61 where xtypes.cross_reference_type = items.cross_reference_type
62 and items.inventory_item_id = sitems.inventory_item_id
63 and items.cross_reference = ordered_item_L
64 and items.cross_reference_type = item_identifier_type_L
65 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
66 and sitems.inventory_item_id = inventory_item_id_L
67 --Bug 3433353 Being
68 and items.org_independent_flag = 'N'
69 and items.organization_id = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0);
70 -- and sitems.customer_order_enabled_flag = 'Y'
71 -- and sitems.bom_item_type in (1,4)
72 Exception When NO_DATA_FOUND Then
73 select items.cross_reference item,
74 nvl(items.description,sitems.description) description
75 into v_item,v_description
76 from mtl_cross_reference_types xtypes,
77 mtl_cross_references items,
78 mtl_system_items_vl sitems
79 where xtypes.cross_reference_type =
80 items.cross_reference_type
81 and items.inventory_item_id =
82 sitems.inventory_item_id
83 and items.cross_reference = ordered_item_L
84 and items.cross_reference_type = item_identifier_type_L
85 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
86 and sitems.inventory_item_id = inventory_item_id_L
87 and items.org_independent_flag = 'Y';
88 End;
89 --Bug 3433353 End
90 end if;
91
92 if (p_print_description in ('I','C')) then
93 return(v_item||' - '||v_description);
94 elsif (p_print_description in ('D','P')) then
95 return(v_description);
96 else
97 return(v_item);
98 end if;
99
100 RETURN NULL;
101 Exception
102 When Others Then
103 return('Item Not Found');
104 end;
105
106 function BeforeReport return boolean is
107 begin
108 BEGIN
109 P_ORGANIZATION_ID1 := P_ORGANIZATION_ID;
110
111 BEGIN
112 -- SRW.USER_EXIT('FND SRWINIT');
113 null;
114 /* EXCEPTION
115 WHEN SRW.USER_EXIT_FAILURE THEN
116 SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger');
117 return (FALSE);*/
118 END;
119
120 BEGIN /*MOAC*/
121
122 --P_ORGANIZATION_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
123 P_ORGANIZATION_ID1:= MO_GLOBAL.GET_CURRENT_ORG_ID();
124
125 END;
126
127 /*------------------------------------------------------------------------------
128 Following PL/SQL block gets the company name, functional currency and precision.
129 ------------------------------------------------------------------------------*/
130
131 DECLARE
132 l_company_name VARCHAR2 (100);
133 l_functional_currency VARCHAR2 (15);
134
135 BEGIN
136
137 SELECT sob.name ,
138 sob.currency_code
139 INTO
140 l_company_name ,
141 l_functional_currency
142 FROM gl_sets_of_books sob,
143 fnd_currencies cur
144 WHERE sob.set_of_books_id = p_sob_id
145 AND sob.currency_code = cur.currency_code
146 ;
147
148 rp_company_name := l_company_name;
149 rp_functional_currency := l_functional_currency ;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 NULL ;
154 END ;
155
156 /*------------------------------------------------------------------------------
157 Following PL/SQL block gets the report name for the passed concurrent request Id.
158 ------------------------------------------------------------------------------*/
159 DECLARE
160 l_report_name VARCHAR2(240);
161 BEGIN
162 SELECT cp.user_concurrent_program_name
163 INTO l_report_name
164 FROM FND_CONCURRENT_PROGRAMS_VL cp,
165 FND_CONCURRENT_REQUESTS cr
166 WHERE cr.request_id = P_CONC_REQUEST_ID
167 AND cp.application_id = cr.program_application_id
168 AND cp.concurrent_program_id = cr.concurrent_program_id
169 ;
170
171 RP_Report_Name := l_report_name;
172 EXCEPTION
173 WHEN NO_DATA_FOUND
174 THEN RP_REPORT_NAME := 'Unbooked Orders Report';
175 END;
176
177 /*------------------------------------------------------------------------------
178 Following PL/SQL block builds up the lexical parameters, to be used in the
179 WHERE clause of the query. This also populates the report level variables, used
180 to store the flexfield structure.
181 ------------------------------------------------------------------------------*/
182 /* BEGIN
183 -- SRW.REFERENCE(:P_item_flex_code);
184 -- SRW.REFERENCE(:P_item_structure_num);
185
186
187 SRW.USER_EXIT('FND FLEXSQL CODE=":P_item_flex_code"
188 NUM=":P_ITEM_STRUCTURE_NUM"
189 APPL_SHORT_NAME="INV"
190 OUTPUT=":rp_item_flex_all_seg"
191 MODE="SELECT"
192 DISPLAY="ALL"
193 TABLEALIAS="MSI"
194 ');
195
196
197 EXCEPTION
198 WHEN SRW.USER_EXIT_FAILURE THEN
199 srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT');
200 END;
201 */
202
203
204 DECLARE
205 l_meaning VARCHAR2 (80);
206 BEGIN
207 SELECT MEANING
208 INTO l_meaning
209 FROM OE_LOOKUPS
210 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
211 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
212 ;
213
214 rp_print_description := l_meaning ;
215 EXCEPTION WHEN NO_DATA_FOUND THEN
216 rp_print_description := 'Internal Item Description';
217 when OTHERS then
218 null;
219 -- srw.message(2000,'Failed in BEFORE REPORT trigger. Get Print Description');
220
221 END ;
222
223 /*------------------------------------------------------------------------------
224 THE Following PL/SQL block populates the order_date_range and created_by range
225 parameters used in the report margins
226 ------------------------------------------------------------------------------*/
227 BEGIN
228 if (P_created_by_low is NOT NULL OR P_created_by_high is NOT NULL) then
229 if (P_Created_by_low is NULL) then
230 P_Created_by_low := ' ';
231 end if;
232 lp_created_by_range := ' From '||P_Created_by_low||' To '||P_Created_by_high;
233 end if;
234
235 if (P_order_date_low is NOT NULL OR P_order_date_high is NOT NULL) then
236 lp_order_date_range := ' From '||nvl(to_char(P_Order_date_low, 'DD-MON-RRRR'), ' ')
237 || ' To ' ||nvl(to_char(P_Order_date_high, 'DD-MON-RRRR'), ' ');
238 end if;
239 END;
240 END ;
241 IF Oe_Sys_Parameters.Value('RECURRING_CHARGES',mo_global.get_current_org_id())='Y' Then
242 C_PERIODICITY_DSP_FLAG := 'Y';
243 ELSE
244 C_PERIODICITY_DSP_FLAG := 'N';
245 END IF;
246 return (TRUE);
247 end BeforeReport;
248
249 -------------------------
250 FUNCTION AFTERREPORT RETURN BOOLEAN IS
251 BEGIN
252 BEGIN
253 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
254 EXCEPTION
255 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
256 /*SRW.MESSAGE(1
257 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
258 RETURN (FALSE);
259 END;
260 RETURN (TRUE);
261 END AFTERREPORT;
262
263 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
264 BEGIN
265 RETURN (TRUE);
266 END P_ITEM_FLEX_CODEVALIDTRIGGER;
267
268 FUNCTION AFTERPFORM RETURN BOOLEAN IS
269 BEGIN
270 BEGIN
271 IF (UPPER(P_ORDER_BY) = 'CREATED_BY') THEN
272 LP_ORDER_BY := 'order by 4,1,2';
273 SELECT
274 MEANING
275 INTO LP_ORDER_BY_MEAN
276 FROM
277 OE_LOOKUPS
278 WHERE LOOKUP_CODE = UPPER(P_ORDER_BY)
279 AND LOOKUP_TYPE = 'OEXOEUBK ORDER BY';
280 ELSE
281 LP_ORDER_BY := 'order by 1,2,4';
282 LP_ORDER_BY_MEAN := 'Order Number';
283 END IF;
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 LP_ORDER_BY_MEAN := P_ORDER_BY;
287 END;
288 IF (lp_order_by IS NULL) THEN
289 lp_order_by := ' ';
290 END IF;
291
292 BEGIN
293 IF (P_ORDER_DATE_LOW IS NOT NULL) AND (P_ORDER_DATE_HIGH IS NOT NULL) THEN
294 LP_ORDER_DATE := 'and h.ordered_date between :p_order_date_low and (:p_order_date_high+1) ';
295 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
296 LP_ORDER_DATE := 'and h.ordered_date >= :p_order_date_low';
297 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
298 LP_ORDER_DATE := 'and h.ordered_date <= (:p_order_date_high+1) ';
299 END IF;
300
301 IF (lp_order_date IS NULL) THEN
302 lp_order_date := ' ';
303 END IF;
304
305 END;
306 BEGIN
307 IF (P_CREATED_BY_LOW IS NOT NULL) AND (P_CREATED_BY_HIGH IS NOT NULL) THEN
308 LP_CREATED_BY := 'and fu.user_name between :p_created_by_low and :p_created_by_high ';
309 ELSIF (P_CREATED_BY_LOW IS NOT NULL) THEN
310 LP_CREATED_BY := 'and fu.user_name >= :p_created_by_low ';
311 ELSIF (P_CREATED_BY_HIGH IS NOT NULL) THEN
312 LP_CREATED_BY := 'and fu.user_name <= :p_created_by_high ';
313 END IF;
314 IF (lp_created_by IS NULL) THEN
315 lp_created_by := ' ';
316 END IF;
317
318 END;
319 IF P_ORDER_CATEGORY IS NOT NULL THEN
320 IF P_ORDER_CATEGORY = 'SALES' THEN
321 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
322 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
323 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
324 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
325 LP_ORDER_CATEGORY := NULL;
326 END IF;
327 ELSE
328 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
329 END IF;
330 IF (lp_order_category IS NULL) THEN
331 lp_order_category := ' ';
332 END IF;
333
334 IF P_LINE_CATEGORY IS NOT NULL THEN
335 IF P_LINE_CATEGORY = 'SALES' THEN
336 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
337 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
338 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
339 ELSIF P_LINE_CATEGORY = 'ALL' THEN
340 LP_LINE_CATEGORY := NULL;
341 END IF;
342 ELSE
343 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
344 END IF;
345 IF (lp_line_category IS NULL) THEN
346 lp_line_category := ' ';
347 END IF;
348 RETURN (TRUE);
349 END AFTERPFORM;
350
351 -----------------------
352 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
353 BEGIN
354 DECLARE
355 L_MEANING VARCHAR2(80);
356 BEGIN
357 SELECT
358 MEANING
359 INTO L_MEANING
360 FROM
361 OE_LOOKUPS
362 WHERE LOOKUP_TYPE = 'REPORT_ORDER_CATEGORY'
363 AND LOOKUP_CODE = P_ORDER_CATEGORY;
364 RETURN (L_MEANING);
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 RETURN (NULL);
368 END;
369 RETURN NULL;
370 END RP_ORDER_CATEGORYFORMULA;
371
372 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
373 BEGIN
374 DECLARE
375 L_MEANING VARCHAR2(80);
376 BEGIN
377 SELECT
378 MEANING
379 INTO L_MEANING
380 FROM
381 OE_LOOKUPS
382 WHERE LOOKUP_TYPE = 'REPORT_LINE_DISPLAY'
383 AND LOOKUP_CODE = P_LINE_CATEGORY;
384 RETURN (L_MEANING);
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 RETURN (NULL);
388 END;
389 RETURN NULL;
390 END RP_LINE_CATEGORYFORMULA;
391
392 FUNCTION C_MASTER_ORGFORMULA RETURN NUMBER IS
393 V_MASTER_ORG VARCHAR2(20);
394 BEGIN
395 SELECT
396 NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
397 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
398 ,0)
399 INTO V_MASTER_ORG
400 FROM
401 DUAL;
402 RETURN V_MASTER_ORG;
403 END C_MASTER_ORGFORMULA;
404
405 FUNCTION C_PERIODICITY_DSPFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
406 L_UOM_CLASS VARCHAR2(20) := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
407 L_CHARGE_PERIODICITY VARCHAR2(25);
408 BEGIN
409 IF CHARGE_PERIODICITY_CODE IS NULL THEN
410 RETURN (P_ONE_TIME);
411 ELSE
412 SELECT
413 UNIT_OF_MEASURE
414 INTO L_CHARGE_PERIODICITY
415 FROM
416 MTL_UNITS_OF_MEASURE_VL
417 WHERE UOM_CLASS = L_UOM_CLASS
418 AND UOM_CODE = CHARGE_PERIODICITY_CODE;
419 RETURN L_CHARGE_PERIODICITY;
420 END IF;
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 RETURN NULL;
424 END C_PERIODICITY_DSPFORMULA;
425
426 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
427 BEGIN
428 RETURN RP_REPORT_NAME;
429 END RP_REPORT_NAME_P;
430
431 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
432 BEGIN
433 RETURN RP_SUB_TITLE;
434 END RP_SUB_TITLE_P;
435
436 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
437 BEGIN
438 RETURN RP_COMPANY_NAME;
439 END RP_COMPANY_NAME_P;
440
441 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
442 BEGIN
443 RETURN RP_FUNCTIONAL_CURRENCY;
444 END RP_FUNCTIONAL_CURRENCY_P;
445
446 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
447 BEGIN
448 RETURN RP_DATA_FOUND;
449 END RP_DATA_FOUND_P;
450
451 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
452 BEGIN
453 RETURN RP_ITEM_FLEX_ALL_SEG;
454 END RP_ITEM_FLEX_ALL_SEG_P;
458 RETURN RP_PRINT_DESCRIPTION;
455
456 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
457 BEGIN
459 END RP_PRINT_DESCRIPTION_P;
460
461 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
462 BEGIN
463 RETURN RP_DUMMY_ITEM;
464 END RP_DUMMY_ITEM_P;
465
466 END ONT_OEXOEUBK_XMLP_PKG;
467
[Home] [Help]