1 PACKAGE BODY ONT_OEXIODIS_XMLP_PKG AS
2 /* $Header: OEXIODISB.pls 120.3 2008/05/05 10:13:23 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 IF P_OPEN_ORDER_ONLY IS NOT NULL THEN
7 LP_OPEN_ORDER_ONLY := 'and oeh.open_flag = ''Y'' ';
8 ELSE
9 LP_OPEN_ORDER_ONLY := ' ';
10 END IF;
11 IF P_ORDER_NUM_LOW IS NOT NULL THEN
12 LP_ORDER_NUM_LOW := 'and oeh.order_number >= :p_order_num_low ';
13 ELSE
14 LP_ORDER_NUM_LOW := ' ';
15 END IF;
16 IF P_ORDER_NUM_HIGH IS NOT NULL THEN
17 LP_ORDER_NUM_HIGH := 'and oeh.order_number <= :p_order_num_high ';
18 ELSE
19 LP_ORDER_NUM_HIGH := ' ';
20 END IF;
21 BEGIN
22 IF P_ORDER_DATE_LOW IS NOT NULL THEN
23 LP_ORDER_DATE_LOW := 'and oeh.ordered_date >= :p_order_date_low ';
24 SELECT
25 OEOT.NAME
26 INTO L_ORDER_TYPE_LOW
27 FROM
28 OE_TRANSACTION_TYPES_TL OEOT
29 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
30 AND OEOT.LANGUAGE = USERENV('LANG');
31 ELSE
32 LP_ORDER_DATE_LOW := ' ';
33 END IF;
34 EXCEPTION WHEN others then
35 NULL;
36 END;
37 BEGIN
38 IF P_ORDER_DATE_HIGH IS NOT NULL THEN
39 LP_ORDER_DATE_HIGH := 'and oeh.ordered_date < :p_order_date_high+1 ';
40 SELECT
41 OEOT.NAME
42 INTO L_ORDER_TYPE_HIGH
43 FROM
44 OE_TRANSACTION_TYPES_TL OEOT
45 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
46 AND OEOT.LANGUAGE = USERENV('LANG');
47 ELSE
48 LP_ORDER_DATE_HIGH := ' ';
49 END IF;
50 EXCEPTION WHEN others then
51 NULL;
52 END;
53 BEGIN
54 IF P_ORDER_TYPE_LOW IS NOT NULL THEN
55 LP_ORDER_TYPE_LOW := 'and oet.transaction_type_id >= ''' || P_ORDER_TYPE_LOW || '''';
56 SELECT
57 OEOT.NAME
58 INTO L_ORDER_TYPE_LOW
59 FROM
60 OE_TRANSACTION_TYPES_TL OEOT
61 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
62 AND OEOT.LANGUAGE = USERENV('LANG');
63 ELSE
64 LP_ORDER_TYPE_LOW := ' ';
65 END IF;
66 EXCEPTION WHEN others then
67 NULL;
68 END;
69 BEGIN
70 IF P_ORDER_TYPE_HIGH IS NOT NULL THEN
71 LP_ORDER_TYPE_HIGH := 'and oet.transaction_type_id >= ''' || P_ORDER_TYPE_HIGH || '''';
72 SELECT
73 OEOT.NAME
74 INTO L_ORDER_TYPE_HIGH
75 FROM
76 OE_TRANSACTION_TYPES_TL OEOT
77 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
78 AND OEOT.LANGUAGE = USERENV('LANG');
79 ELSE
80 LP_ORDER_TYPE_HIGH := ' ';
81 END IF;
82 EXCEPTION WHEN others then
83 NULL;
84 END;
85 IF P_REQUISITION_NUM_LOW IS NOT NULL THEN
86 LP_REQUISITION_NUM_LOW := 'and porh.segment1 >= to_char(:p_requisition_num_low) ';
87 ELSE
88 LP_REQUISITION_NUM_LOW := ' ';
89 END IF;
90 IF P_REQUISITION_NUM_HIGH IS NOT NULL THEN
91 LP_REQUISITION_NUM_HIGH := 'and porh.segment1 <= to_char(:p_requisition_num_high) ';
92 ELSE
93 LP_REQUISITION_NUM_HIGH := ' ';
94 END IF;
95 END;
96
97 RETURN (TRUE);
98 END AFTERPFORM;
99
100 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
101 BEGIN
102 BEGIN
103 BEGIN
104 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
105 CP_ORDER_DATE_LOW := TO_CHAR(P_ORDER_DATE_LOW,'YYYY/MM/DD HH24:MI:SS');
106 CP_ORDER_DATE_HIGH :=TO_CHAR(P_ORDER_DATE_HIGH,'YYYY/MM/DD HH24:MI:SS');
107 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
108 EXCEPTION
109 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
110 /*SRW.MESSAGE(1000
111 ,'Failed in BEFORE REPORT trigger')*/NULL;
112 RETURN (FALSE);
113 END;
114 BEGIN
115 P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
116 END;
117 DECLARE
118 L_COMPANY_NAME VARCHAR2(30);
119 BEGIN
120 SELECT
121 NAME
122 INTO L_COMPANY_NAME
123 FROM
124 GL_SETS_OF_BOOKS
125 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
126 RP_COMPANY_NAME := L_COMPANY_NAME;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 NULL;
130 END;
131 DECLARE
132 L_REPORT_NAME VARCHAR2(240);
133 BEGIN
134 SELECT
135 CP.USER_CONCURRENT_PROGRAM_NAME
136 INTO L_REPORT_NAME
137 FROM
138 FND_CONCURRENT_PROGRAMS_VL CP,
139 FND_CONCURRENT_REQUESTS CR
140 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
141 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
142 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
143 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 RP_REPORT_NAME := 'Internal Order and Purchasing Requisition Discrepancy Report';
147 END;
148 BEGIN
149 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
150 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
151 /*SRW.REFERENCE(P_ITEM_LOW)*/NULL;
152 /*SRW.REFERENCE(P_ITEM_HIGH)*/NULL;
153 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HIGH IS NOT NULL THEN
154 LP_ITEM := ' and ' || LP_ITEM;
155 END IF;
156 EXCEPTION
157 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
158 /*SRW.MESSAGE(2000
159 ,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT')*/NULL;
160 END;
161 BEGIN
162 RP_DUMMY_ITEM := NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
163 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
164 ,0);
165 EXCEPTION
166 WHEN OTHERS THEN
167 /*SRW.MESSAGE(1000
168 ,'Error in fetching Master Organization Id for the session')*/NULL;
169 RP_DUMMY_ITEM := 0;
170 END;
171 END;
172 RETURN (TRUE);
173 END BEFOREREPORT;
174
175 FUNCTION C_BUILD_LBLFORMULA RETURN VARCHAR2 IS
176 BEGIN
177 DECLARE
178 L_DATE VARCHAR2(11);
179 H_DATE VARCHAR2(11);
180 BEGIN
181 IF P_ORDER_NUM_LOW IS NOT NULL OR P_ORDER_NUM_HIGH IS NOT NULL THEN
182 RP_ORDER_RANGE := 'From ' || NVL(P_ORDER_NUM_LOW
183 ,' ') || ' To ' || NVL(P_ORDER_NUM_HIGH
184 ,' ');
185 END IF;
186 L_DATE := ' ';
187 H_DATE := ' ';
188 IF P_ORDER_DATE_LOW IS NOT NULL THEN
189 L_DATE := TO_CHAR(P_ORDER_DATE_LOW);
190 END IF;
191 IF P_ORDER_DATE_HIGH IS NOT NULL THEN
192 H_DATE := TO_CHAR(P_ORDER_DATE_HIGH);
193 END IF;
194 IF P_ORDER_DATE_LOW IS NOT NULL OR P_ORDER_DATE_HIGH IS NOT NULL THEN
195 RP_ORDER_DATE_RANGE := 'From ' || L_DATE || ' To ' || H_DATE;
196 END IF;
197 IF P_REQUISITION_NUM_LOW IS NOT NULL OR P_REQUISITION_NUM_HIGH IS NOT NULL THEN
198 RP_REQUISITION_RANGE := 'From ' || P_REQUISITION_NUM_LOW || ' To ' || P_REQUISITION_NUM_HIGH;
199 END IF;
200 IF P_ORDER_TYPE_LOW IS NOT NULL OR P_ORDER_TYPE_HIGH IS NOT NULL THEN
201 RP_ORDER_TYPE_RANGE := 'From ' || L_ORDER_TYPE_LOW || ' To ' || L_ORDER_TYPE_HIGH;
202 END IF;
203 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HIGH IS NOT NULL THEN
204 RP_ITEM_RANGE := 'From ' || P_ITEM_LOW || ' To ' || P_ITEM_HIGH;
205 END IF;
206 DECLARE
207 ITEM_DISPLAY_MEANING VARCHAR2(80);
208 BEGIN
209 SELECT
210 MEANING
211 INTO ITEM_DISPLAY_MEANING
212 FROM
213 OE_LOOKUPS
214 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
215 AND LOOKUP_CODE = P_PRINT_DESCRIPTION;
216 RP_ITEM_DISPLAY_METHOD := ITEM_DISPLAY_MEANING;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 RP_ITEM_DISPLAY_METHOD := NULL;
220 END;
221 DECLARE
222 OPEN_ORDER_ONLY VARCHAR2(80);
223 BEGIN
224 SELECT
225 MEANING
226 INTO OPEN_ORDER_ONLY
227 FROM
228 OE_LOOKUPS
229 WHERE LOOKUP_TYPE = 'YES_NO'
230 AND LOOKUP_CODE = P_OPEN_ORDER_ONLY;
231 RP_OPEN_ORDER_ONLY := OPEN_ORDER_ONLY;
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 RP_OPEN_ORDER_ONLY := NULL;
235 END;
236 DECLARE
237 ORDER_BY VARCHAR2(80);
238 BEGIN
239 SELECT
240 MEANING
241 INTO ORDER_BY
242 FROM
243 OE_LOOKUPS
244 WHERE LOOKUP_TYPE = 'OEXIODIS ORDER BY'
245 AND LOOKUP_CODE = P_ORDER_BY;
246 RP_ORDER_BY := ORDER_BY;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 RP_ORDER_BY := P_ORDER_BY;
250 END;
251 RETURN (1);
252 END;
253 RETURN NULL;
254 END C_BUILD_LBLFORMULA;
255
256 FUNCTION CF_SO_HOLDFORMULA(OM_LINE_ID IN NUMBER
257 ,OM_HEADER_ID IN NUMBER) RETURN VARCHAR2 IS
258 BEGIN
259 /*SRW.REFERENCE(OM_LINE_ID)*/NULL;
260 /*SRW.REFERENCE(OM_HEADER_ID)*/NULL;
261 DECLARE
262 L_NUM_HOLD NUMBER;
263 L_MEANING VARCHAR2(80);
264 BEGIN
265 SELECT
266 COUNT(ORDER_HOLD_ID)
267 INTO L_NUM_HOLD
268 FROM
269 OE_ORDER_HOLDS_ALL
270 WHERE HEADER_ID = OM_HEADER_ID
271 AND HOLD_RELEASE_ID is null
272 AND ( LINE_ID = OM_LINE_ID
273 OR LINE_ID is null );
274 IF (L_NUM_HOLD = 0) THEN
275 SELECT
276 MEANING
277 INTO L_MEANING
278 FROM
279 OE_LOOKUPS
280 WHERE LOOKUP_TYPE = 'YES_NO'
281 AND LOOKUP_CODE = 'N';
282 ELSE
283 SELECT
284 MEANING
285 INTO L_MEANING
286 FROM
287 OE_LOOKUPS
288 WHERE LOOKUP_TYPE = 'YES_NO'
289 AND LOOKUP_CODE = 'Y';
290 END IF;
291 RETURN (L_MEANING);
292 END;
293 RETURN NULL;
294 END CF_SO_HOLDFORMULA;
295
296 FUNCTION CF_PO_ITEM_DISPLAYFORMULA(CF_ITEM_FLEX IN VARCHAR2
297 ,RQ_ITEM_DESCRIPTION IN VARCHAR2) RETURN VARCHAR2 IS
298 BEGIN
299 /*SRW.REFERENCE(P_PRINT_DESCRIPTION)*/NULL;
300 /*SRW.REFERENCE(CF_ITEM_FLEX)*/NULL;
301 RP_DATA_FOUND := 'X';
302 IF UPPER(P_PRINT_DESCRIPTION) in ('F','O') THEN
303 RETURN (CF_ITEM_FLEX);
304 ELSIF UPPER(P_PRINT_DESCRIPTION) in ('D','P') THEN
305 RETURN (RQ_ITEM_DESCRIPTION);
306 ELSIF UPPER(P_PRINT_DESCRIPTION) in ('C','I') THEN
307 RETURN (CF_ITEM_FLEX || RQ_ITEM_DESCRIPTION);
308 END IF;
309 RETURN NULL;
310 END CF_PO_ITEM_DISPLAYFORMULA;
311
312 FUNCTION CF_ORDER_BYFORMULA RETURN VARCHAR2 IS
313 BEGIN
314 DECLARE
315 ORDER_BY VARCHAR2(80);
316 BEGIN
317 IF P_ORDER_BY = 'ORDER_NUM' THEN
318 ORDER_BY := ' om_order_number, om_order_date ';
319 ELSIF P_ORDER_BY = 'ORDER_TYPE' THEN
320 ORDER_BY := ' om_order_type,om_order_number ';
321 ELSIF P_ORDER_BY = 'ORDER_DATE' THEN
322 ORDER_BY := ' om_order_date, om_order_number ';
323 ELSE
324 ORDER_BY := ' om_order_date ';
325 END IF;
326 RETURN (ORDER_BY);
327 END;
328 RETURN NULL;
329 END CF_ORDER_BYFORMULA;
330
331 FUNCTION P_CUSTOMER_NUMBER_LOWVALIDTRIG RETURN BOOLEAN IS
332 BEGIN
333 RETURN (TRUE);
334 END P_CUSTOMER_NUMBER_LOWVALIDTRIG;
335
336 FUNCTION AFTERREPORT RETURN BOOLEAN IS
337 BEGIN
338 BEGIN
339 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
340 EXCEPTION
341 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
342 /*SRW.MESSAGE(1
343 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
344 RETURN (FALSE);
345 END;
346 RETURN (TRUE);
347 END AFTERREPORT;
348
349 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
350 BEGIN
351 RETURN RP_REPORT_NAME;
352 END RP_REPORT_NAME_P;
353
354 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
355 BEGIN
356 RETURN RP_COMPANY_NAME;
357 END RP_COMPANY_NAME_P;
358
359 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
360 BEGIN
361 RETURN RP_SUB_TITLE;
362 END RP_SUB_TITLE_P;
363
364 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
365 BEGIN
366 RETURN RP_ORDER_RANGE;
367 END RP_ORDER_RANGE_P;
368
369 FUNCTION RP_REQUISITION_RANGE_P RETURN VARCHAR2 IS
370 BEGIN
371 RETURN RP_REQUISITION_RANGE;
372 END RP_REQUISITION_RANGE_P;
373
374 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
375 BEGIN
376 RETURN RP_ORDER_DATE_RANGE;
377 END RP_ORDER_DATE_RANGE_P;
378
379 FUNCTION RP_ITEM_DISPLAY_METHOD_P RETURN VARCHAR2 IS
380 BEGIN
381 RETURN RP_ITEM_DISPLAY_METHOD;
382 END RP_ITEM_DISPLAY_METHOD_P;
383
384 FUNCTION RP_OPEN_ORDER_ONLY_P RETURN VARCHAR2 IS
385 BEGIN
386 RETURN RP_OPEN_ORDER_ONLY;
387 END RP_OPEN_ORDER_ONLY_P;
388
389 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
390 BEGIN
391 RETURN RP_ORDER_BY;
395 BEGIN
392 END RP_ORDER_BY_P;
393
394 FUNCTION RP_ITEM_FLEX2_P RETURN VARCHAR2 IS
396 RETURN RP_ITEM_FLEX2;
397 END RP_ITEM_FLEX2_P;
398
399 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
400 BEGIN
401 RETURN RP_ITEM_FLEX_ALL_SEG;
402 END RP_ITEM_FLEX_ALL_SEG_P;
403
404 FUNCTION RP_ITEM_RANGE_P RETURN VARCHAR2 IS
405 BEGIN
406 RETURN RP_ITEM_RANGE;
407 END RP_ITEM_RANGE_P;
408
409 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
410 BEGIN
411 RETURN RP_DATA_FOUND;
412 END RP_DATA_FOUND_P;
413
414 FUNCTION RP_ORDER_TYPE_RANGE_P RETURN VARCHAR2 IS
415 BEGIN
416 RETURN RP_ORDER_TYPE_RANGE;
417 END RP_ORDER_TYPE_RANGE_P;
418
419 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
420 BEGIN
421 RETURN RP_DUMMY_ITEM;
422 END RP_DUMMY_ITEM_P;
423
424 function item_dspFormula(item_identifier_type in VARCHAR2,ordered_item_id in number,ordered_item in varchar2,ORGANIZATION_ID in number, INVENTORY_ITEM_ID in number) return Char is
425 v_item varchar2(2000);
426 v_description varchar2(500);
427 begin
428 if (item_identifier_type is null or item_identifier_type = 'INT')
429 or (p_print_description in ('I','D','F')) then
430
431 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code,p_item_structure_num ,item_dspFormula.ORGANIZATION_ID,item_dspFormula.INVENTORY_ITEM_ID, 'ALL', 'Y',
432 'VALUE');
433
434 select sitems.description description
435 into v_description
436 from mtl_system_items_vl sitems
437 --where sitems.customer_order_enabled_flag = 'Y'
438 where sitems.bom_item_type in (1,4)
439 and nvl(sitems.organization_id,0) = RP_DUMMY_ITEM
440 and sitems.inventory_item_id = item_dspFormula.inventory_item_id;
441
442 /* srw.reference (:item_flex);
443 srw.reference (:p_item_flex_code);
444 srw.reference (:Item_dsp);
445 srw.reference (:p_item_structure_num);
446 srw.user_exit (' FND FLEXIDVAL
447 CODE=":p_item_flex_code"
448 NUM=":p_item_structure_num"
449 APPL_SHORT_NAME="INV"
450 DATA= ":item_flex"
451 VALUE=":Item_dsp"
452 DISPLAY="ALL"'
453 );*/
454 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code,p_item_structure_num ,item_dspFormula.ORGANIZATION_ID,item_dspFormula.INVENTORY_ITEM_ID, 'ALL', 'Y',
455 'VALUE');
456 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
457 select citems.customer_item_number item,
458 nvl(citems.customer_item_desc,sitems.description) description
459 into v_item,v_description
460 from mtl_customer_items citems,
461 mtl_customer_item_xrefs cxref,
462 mtl_system_items_vl sitems
463 where citems.customer_item_id = cxref.customer_item_id
464 and cxref.inventory_item_id = sitems.inventory_item_id
465 and citems.customer_item_id = item_dspFormula.ordered_item_id
466 and nvl(sitems.organization_id,0) = RP_DUMMY_ITEM
467 and sitems.inventory_item_id = item_dspFormula.inventory_item_id;
468 -- and sitems.customer_order_enabled_flag = 'Y'
469 -- and sitems.bom_item_type in (1,4)
470 elsif (p_print_description in ('C','P','O')) then
471 select items.cross_reference item,
472 nvl(items.description,sitems.description) description
473 into v_item,v_description
474 from mtl_cross_reference_types xtypes,
475 mtl_cross_references items,
476 mtl_system_items_vl sitems
477 where xtypes.cross_reference_type = items.cross_reference_type
478 and items.inventory_item_id = sitems.inventory_item_id
479 and items.cross_reference = item_dspFormula.ordered_item
480 and items.cross_reference_type = item_dspFormula.item_identifier_type
481 and nvl(sitems.organization_id,0) = RP_DUMMY_ITEM
482 and sitems.inventory_item_id = item_dspFormula.inventory_item_id;
483 -- and sitems.customer_order_enabled_flag = 'Y'
484 -- and sitems.bom_item_type in (1,4)
485 end if;
486
487 if (p_print_description in ('I','C')) then
488 return(v_item||' - '||v_description);
489 elsif (p_print_description in ('D','P')) then
490 return(v_description);
491 else
492 return(v_item);
493 end if;
494
495
496 RETURN NULL;
497 EXCEPTION
498 WHEN NO_DATA_FOUND THEN
499 RETURN ('Item not found');
500 end;
501
502
503
504 END ONT_OEXIODIS_XMLP_PKG;
505
[Home] [Help]