1 PACKAGE BODY ONT_OEXOEITR_XMLP_PKG AS
2 /* $Header: OEXOEITRB.pls 120.2 2008/05/05 09:04:13 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 P_ORDER_DATE_LOW_T := to_char(P_ORDER_DATE_LOW,'DD-MON-YY');
9 P_ORDER_DATE_HIGH_T := TO_CHAR(P_ORDER_DATE_HIGH,'DD-MON-YY');
10
11 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
12 EXCEPTION
13 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
14 BEGIN
15 /*SRW.MESSAGE(1000
16 ,'Failed in BEFORE REPORT trigger')*/NULL;
17 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
18 END;
19 END;
20 BEGIN
21 P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
22 END;
23 DECLARE
24 L_COMPANY_NAME VARCHAR2(100);
25 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
26 BEGIN
27 SELECT
28 SOB.NAME,
29 SOB.CURRENCY_CODE
30 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
31 FROM
32 GL_SETS_OF_BOOKS SOB,
33 FND_CURRENCIES CUR
34 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
35 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
36 RP_COMPANY_NAME := L_COMPANY_NAME;
37 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 NULL;
41 END;
42 BEGIN
43 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
44 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
45 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
46 LP_ITEM := ' and ' || LP_ITEM;
47 ELSE
48 LP_ITEM := ' ';
49 END IF;
50 EXCEPTION
51 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
52 /*SRW.MESSAGE(1
53 ,'Failed in before report trigger:MSTK')*/NULL;
54 END;
55 DECLARE
56 L_MEANING VARCHAR2(80);
57 L_LOOKUP_TYPE VARCHAR2(80);
58 BEGIN
59 L_LOOKUP_TYPE := 'ITEM_DISPLAY_CODE';
60 SELECT
61 MEANING
62 INTO L_MEANING
63 FROM
64 OE_LOOKUPS
65 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
66 AND LOOKUP_CODE = SUBSTR(UPPER(P_PRINT_DESCRIPTION)
67 ,1
68 ,1);
69 RP_PRINT_DESCRIPTION := L_MEANING;
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 RP_PRINT_DESCRIPTION := 'Internal Item Description';
73 WHEN OTHERS THEN
74 /*SRW.MESSAGE(2000
75 ,'Failed in BEFORE REPORT trigger. Get Print Description')*/NULL;
76 END;
77 DECLARE
78 L_REPORT_NAME VARCHAR2(240);
79 BEGIN
80 SELECT
81 CP.USER_CONCURRENT_PROGRAM_NAME
82 INTO L_REPORT_NAME
83 FROM
84 FND_CONCURRENT_PROGRAMS_VL CP,
85 FND_CONCURRENT_REQUESTS CR
86 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
87 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
88 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
89 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 RP_REPORT_NAME := 'Orders by Item';
93 END;
94 END;
95 RETURN (TRUE);
96 END BEFOREREPORT;
97
98 FUNCTION AFTERREPORT RETURN BOOLEAN IS
99 BEGIN
100 BEGIN
101 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
102 EXCEPTION
103 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
104 /*SRW.MESSAGE(1
105 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
106 RETURN (FALSE);
107 END;
108 RETURN (TRUE);
109 END AFTERREPORT;
110
111 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
112 BEGIN
113 RETURN (TRUE);
114 END P_ITEM_FLEX_CODEVALIDTRIGGER;
115
116 FUNCTION AFTERPFORM RETURN BOOLEAN IS
117 BEGIN
118 IF P_ORDER_NUM_LOW IS NOT NULL AND P_ORDER_NUM_HIGH IS NOT NULL THEN
119 LP_ORDER_NUM := ' AND h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ';
120 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
121 LP_ORDER_NUM := ' and h.order_number >= to_number(:p_order_num_low) ';
122 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
123 LP_ORDER_NUM := ' and h.order_number <= to_number(:p_order_num_high) ';
124 ELSE
125 LP_ORDER_NUM := ' ';
126 END IF;
127 IF P_ORDER_TYPE_LOW IS NOT NULL AND P_ORDER_TYPE_HIGH IS NOT NULL THEN
128 LP_ORDER_TYPE := 'and ot.transaction_type_id between :P_order_type_low and :P_order_type_high ';
129 SELECT
130 OEOT.NAME
131 INTO L_ORDER_TYPE_LOW
132 FROM
133 OE_TRANSACTION_TYPES_TL OEOT
134 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
135 AND OEOT.LANGUAGE = USERENV('LANG');
136 SELECT
137 OEOT.NAME
138 INTO L_ORDER_TYPE_HIGH
139 FROM
140 OE_TRANSACTION_TYPES_TL OEOT
141 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
142 AND OEOT.LANGUAGE = USERENV('LANG');
143 ELSE
144 IF P_ORDER_TYPE_LOW IS NULL AND P_ORDER_TYPE_HIGH IS NOT NULL THEN
145 LP_ORDER_TYPE := 'and ot.transaction_type_id <= :P_order_type_high ';
146 SELECT
147 OEOT.NAME
148 INTO L_ORDER_TYPE_HIGH
149 FROM
150 OE_TRANSACTION_TYPES_TL OEOT
151 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
152 AND OEOT.LANGUAGE = USERENV('LANG');
153 ELSE
154 IF P_ORDER_TYPE_LOW IS NOT NULL AND P_ORDER_TYPE_HIGH IS NULL THEN
155 LP_ORDER_TYPE := 'and ot.transaction_type_id >= :P_order_type_low ';
156 SELECT
157 OEOT.NAME
158 INTO L_ORDER_TYPE_LOW
159 FROM
160 OE_TRANSACTION_TYPES_TL OEOT
161 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
162 AND OEOT.LANGUAGE = USERENV('LANG');
163 ELSE
164 LP_ORDER_TYPE := ' ';
165 END IF;
166 END IF;
167 END IF;
168 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
169 LP_ORDER_DATE := ' AND h.ordered_date >= :p_order_date_low and h.ordered_date < :p_order_date_high+1 ';
170 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
171 LP_ORDER_DATE := ' and h.ordered_date >= :p_order_date_low';
172 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
173 LP_ORDER_DATE := 'and h.ordered_date < :p_order_date_high+1 ';
174 ELSE
175 LP_ORDER_DATE := ' ';
176 END IF;
177 IF P_CUSTOMER_NAME_LOW IS NOT NULL AND P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
178 IF P_CUSTOMER_NAME_LOW = P_CUSTOMER_NAME_HIGH THEN
179 LP_CUSTOMER_NAME := ' AND party.party_name = :p_customer_name_low ';
180 ELSE
181 LP_CUSTOMER_NAME := ' AND party.party_name between :p_customer_name_low and :p_customer_name_high ';
182 END IF;
183 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
184 LP_CUSTOMER_NAME := ' and party.party_name >= :p_customer_name_low ';
185 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
186 LP_CUSTOMER_NAME := ' and party.party_name <= :p_customer_name_high ';
187 ELSE
188 LP_CUSTOMER_NAME := ' ';
189 END IF;
190 IF (P_OPENONLY IS NOT NULL) THEN
191 IF ((SUBSTR(UPPER(P_OPENONLY)
192 ,1
193 ,1)) = 'Y') THEN
194 LP_OPENONLY := ' AND NVL(H.OPEN_FLAG, ''N'') = ''Y''';
195 ELSE
196 LP_OPENONLY := ' ';
197 END IF;
198 END IF;
199 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
200 NULL;
201 ELSE
202 IF P_ORDER_CATEGORY IS NOT NULL THEN
203 IF P_ORDER_CATEGORY = 'SALES' THEN
204 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
205 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
206 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
207 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
208 LP_ORDER_CATEGORY := ' ';
209 END IF;
210 ELSE
211 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
212 END IF;
213 END IF;
214 IF P_LINE_CATEGORY IS NOT NULL THEN
215 IF P_LINE_CATEGORY = 'SALES' THEN
216 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
217 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
218 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
219 ELSIF P_LINE_CATEGORY = 'ALL' THEN
220 LP_LINE_CATEGORY := ' ';
221 END IF;
222 ELSE
223 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
224 END IF;
225 RETURN (TRUE);
226 END AFTERPFORM;
227
228 FUNCTION C_SET_LBLFORMULA RETURN VARCHAR2 IS
229 BEGIN
230 BEGIN
231 IF P_CUSTOMER_NAME_LOW IS NOT NULL OR P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
232 RP_CUSTOMER_RANGE := 'From ' || NVL(SUBSTR(P_CUSTOMER_NAME_LOW
233 ,1
234 ,16)
235 ,' ') || ' To ' || NVL(SUBSTR(P_CUSTOMER_NAME_HIGH
236 ,1
237 ,16)
238 ,' ');
239 END IF;
240 IF P_ORDER_DATE_LOW IS NOT NULL OR P_ORDER_DATE_HIGH IS NOT NULL THEN
241 RP_ORDER_DATE_RANGE := 'From ' || NVL(TO_CHAR(P_ORDER_DATE_LOW
242 ,'DD-MON-RRRR')
243 ,' ') || ' To ' || NVL(TO_CHAR(P_ORDER_DATE_HIGH
244 ,'DD-MON-RRRR')
245 ,' ');
246 END IF;
247 IF P_ORDER_NUM_LOW IS NOT NULL OR P_ORDER_NUM_HIGH IS NOT NULL THEN
248 RP_ORDER_RANGE := 'From ' || NVL(P_ORDER_NUM_LOW
249 ,' ') || ' To ' || NVL(P_ORDER_NUM_HIGH
250 ,' ');
251 END IF;
252 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
253 RP_ITEM_RANGE := 'From ' || NVL(P_ITEM_LOW
254 ,' ') || ' To ' || NVL(P_ITEM_HI
255 ,' ');
256 END IF;
257 BEGIN
258 DECLARE
259 MEANING VARCHAR2(80);
260 BEGIN
261 SELECT
262 MEANING
263 INTO MEANING
264 FROM
265 FND_LOOKUPS
266 WHERE LOOKUP_TYPE = 'YES_NO'
267 AND LOOKUP_CODE = P_OPENONLY;
268 RP_OPENONLY := MEANING;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 RP_OPENONLY := P_OPENONLY;
272 END;
273 END;
274 RETURN (1);
275 END;
276 RETURN NULL;
277 END C_SET_LBLFORMULA;
278
279 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
280 BEGIN
281 DECLARE
282 L_MEANING VARCHAR2(80);
283 L_LOOKUP_TYPE VARCHAR2(80);
284 L_LOOKUP_CODE VARCHAR2(80);
285 BEGIN
286 L_LOOKUP_TYPE := 'REPORT_ORDER_CATEGORY';
287 L_LOOKUP_CODE := P_ORDER_CATEGORY;
288 SELECT
289 MEANING
290 INTO L_MEANING
291 FROM
292 OE_LOOKUPS
293 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
294 AND LOOKUP_CODE = L_LOOKUP_CODE;
295 RETURN (L_MEANING);
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 RETURN (NULL);
299 END;
300 RETURN NULL;
301 END RP_ORDER_CATEGORYFORMULA;
302
303 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
304 BEGIN
305 DECLARE
306 L_MEANING VARCHAR2(80);
307 L_LOOKUP_TYPE VARCHAR2(80);
308 L_LOOKUP_CODE VARCHAR2(80);
309 BEGIN
310 L_LOOKUP_TYPE := 'REPORT_LINE_DISPLAY';
311 L_LOOKUP_CODE := P_LINE_CATEGORY;
312 SELECT
313 MEANING
314 INTO L_MEANING
315 FROM
316 OE_LOOKUPS
317 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
318 AND LOOKUP_CODE = L_LOOKUP_CODE;
319 RETURN (L_MEANING);
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 RETURN (NULL);
323 END;
324 RETURN NULL;
325 END RP_LINE_CATEGORYFORMULA;
326
327 FUNCTION CF_UNIT1FORMULA(UNIT IN VARCHAR2) RETURN CHAR IS
328 BEGIN
329 CP_UNIT1 := UNIT;
330 RETURN 1;
331 END CF_UNIT1FORMULA;
332
333 FUNCTION CF_UNIT2FORMULA(UNIT2 IN VARCHAR2) RETURN CHAR IS
334 BEGIN
335 CP_UNIT2 := UNIT2;
336 RETURN 1;
337 END CF_UNIT2FORMULA;
338
339 FUNCTION C_MASTER_ORGFORMULA RETURN NUMBER IS
340 V_MASTER_ORG VARCHAR2(20);
341 BEGIN
342 SELECT
343 NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
344 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
345 ,0)
346 INTO V_MASTER_ORG
347 FROM
348 DUAL;
349 RETURN V_MASTER_ORG;
350 END C_MASTER_ORGFORMULA;
351
352 FUNCTION CF_CHARGE_PERIODICITYFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
353 L_UOM_CLASS VARCHAR2(50) := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
354 L_CHARGE_PERIODICITY VARCHAR2(25);
355 BEGIN
356 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
357 SELECT
358 UNIT_OF_MEASURE
359 INTO L_CHARGE_PERIODICITY
360 FROM
361 MTL_UNITS_OF_MEASURE_VL
362 WHERE UOM_CLASS = L_UOM_CLASS
363 AND UOM_CODE = CHARGE_PERIODICITY_CODE;
364 RETURN L_CHARGE_PERIODICITY;
365 ELSE
366 RETURN (P_ONE_TIME);
367 END IF;
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 RETURN NULL;
371 END CF_CHARGE_PERIODICITYFORMULA;
372
373 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
374 BEGIN
375 RETURN RP_REPORT_NAME;
376 END RP_REPORT_NAME_P;
377
378 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
379 BEGIN
380 RETURN RP_SUB_TITLE;
381 END RP_SUB_TITLE_P;
382
383 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
384 BEGIN
385 RETURN RP_COMPANY_NAME;
386 END RP_COMPANY_NAME_P;
387
388 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
389 BEGIN
390 RETURN RP_FUNCTIONAL_CURRENCY;
391 END RP_FUNCTIONAL_CURRENCY_P;
392
393 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
394 BEGIN
395 RETURN RP_DATA_FOUND;
396 END RP_DATA_FOUND_P;
397
398 FUNCTION RP_ITEM_FLEX_LPROMPT_P RETURN VARCHAR2 IS
399 BEGIN
400 RETURN RP_ITEM_FLEX_LPROMPT;
401 END RP_ITEM_FLEX_LPROMPT_P;
402
403 FUNCTION RP_ITEM_FLEX_CODE_P RETURN VARCHAR2 IS
404 BEGIN
405 RETURN RP_ITEM_FLEX_CODE;
406 END RP_ITEM_FLEX_CODE_P;
407
408 FUNCTION RP_ITEM_FLEX_APROMPT_P RETURN VARCHAR2 IS
409 BEGIN
410 RETURN RP_ITEM_FLEX_APROMPT;
411 END RP_ITEM_FLEX_APROMPT_P;
412
413 FUNCTION RP_CUSTOMER_RANGE_P RETURN VARCHAR2 IS
414 BEGIN
415 RETURN RP_CUSTOMER_RANGE;
416 END RP_CUSTOMER_RANGE_P;
417
418 FUNCTION RP_TYPE_RANGE_P RETURN VARCHAR2 IS
419 BEGIN
420 RETURN RP_TYPE_RANGE;
421 END RP_TYPE_RANGE_P;
422
423 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
424 BEGIN
425 RETURN RP_ORDER_RANGE;
426 END RP_ORDER_RANGE_P;
427
428 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
429 BEGIN
430 RETURN RP_ORDER_DATE_RANGE;
431 END RP_ORDER_DATE_RANGE_P;
432
433 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
434 BEGIN
435 RETURN RP_ORDER_BY;
436 END RP_ORDER_BY_P;
437
438 FUNCTION RP_OPENONLY_P RETURN VARCHAR2 IS
439 BEGIN
440 RETURN RP_OPENONLY;
441 END RP_OPENONLY_P;
442
443 FUNCTION RP_ITEM_RANGE_P RETURN VARCHAR2 IS
444 BEGIN
445 RETURN RP_ITEM_RANGE;
446 END RP_ITEM_RANGE_P;
447
448 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
449 BEGIN
450 RETURN RP_ITEM_FLEX_ALL_SEG;
451 END RP_ITEM_FLEX_ALL_SEG_P;
452
453 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
454 BEGIN
455 RETURN RP_PRINT_DESCRIPTION;
456 END RP_PRINT_DESCRIPTION_P;
457
458 FUNCTION CP_UNIT1_P RETURN VARCHAR2 IS
459 BEGIN
460 RETURN CP_UNIT1;
461 END CP_UNIT1_P;
462
463 FUNCTION CP_UNIT2_P RETURN VARCHAR2 IS
464 BEGIN
465 RETURN CP_UNIT2;
466 END CP_UNIT2_P;
467 FUNCTION F_1FORMATTRIGGER RETURN VARCHAR2 IS
468 BEGIN
469 IF OE_SYS_PARAMETERS.VALUE('RECURRING_CHARGES',MO_GLOBAL.GET_CURRENT_ORG_ID()) ='Y' THEN
470 RETURN ('TRUE');
471 ELSE
472 RETURN ('FALSE');
473 END IF;
474 END F_1FORMATTRIGGER;
475
476 function Item_dspFormula(
477 ITEM_IDENTIFIER_TYPE IN VARCHAR2,
478 C_MASTER_ORG IN NUMBER,
479 INVENTORY_ITEM_ID_T IN NUMBER,
480 ORDERED_ITEM_ID IN NUMBER,
481 ORDERED_ITEM IN VARCHAR2,
482 ORGANIZATION_ID_T in number,
483 INVENTORY_ITEM_ID_T1 in number
484 )
485
486 return Char is
487 v_item varchar2(2000);
488 v_description varchar2(500);
489 begin
490
491 if (item_identifier_type is null or item_identifier_type = 'INT')
492 or (P_PRINT_DESCRIPTION in ('I','D','F')) then
493 select sitems.description description
494 into v_description
495 from mtl_system_items_vl sitems
496 -- where sitems.customer_order_enabled_flag = 'Y'
497 -- and sitems.bom_item_type in (1,4)
498 where nvl(sitems.organization_id,0) = c_master_org
499 and sitems.inventory_item_id = inventory_item_id_T;
500 /* srw.reference (:item_flex);
501 srw.reference (:p_item_flex_code);
502 srw.reference (:Item_dsp);
503 srw.reference (:p_item_structure_num);
504 srw.user_exit (' FND FLEXIDVAL
505 CODE=":p_item_flex_code"
506 NUM=":p_item_structure_num"
507 APPL_SHORT_NAME="INV"
508 DATA= ":item_flex"
509 VALUE=":Item_dsp"
510 DISPLAY="ALL"'
511 );*/
512 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', P_item_flex_code, P_ITEM_STRUCTURE_NUM, ORGANIZATION_ID_T, INVENTORY_ITEM_ID_T1, 'ALL', 'Y', 'VALUE');
513 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
514 select citems.customer_item_number item,
515 nvl(citems.customer_item_desc,sitems.description) description
516 into v_item,v_description
517 from mtl_customer_items citems,
518 mtl_customer_item_xrefs cxref,
519 mtl_system_items_vl sitems
520 where citems.customer_item_id = cxref.customer_item_id
521 and cxref.inventory_item_id = sitems.inventory_item_id
522 and citems.customer_item_id = ordered_item_id
523 and nvl(sitems.organization_id,0) = c_master_org
524 and sitems.inventory_item_id = inventory_item_id_T;
525 -- and sitems.customer_order_enabled_flag = 'Y'
526 -- and sitems.bom_item_type in (1,4)
527 elsif (p_print_description in ('C','P','O')) then
528 Begin
529 select items.cross_reference item,
530 nvl(items.description,sitems.description) description
531 into v_item,v_description
532 from mtl_cross_reference_types xtypes,
533 mtl_cross_references items,
534 mtl_system_items_vl sitems
535 where xtypes.cross_reference_type = items.cross_reference_type
536 and items.inventory_item_id = sitems.inventory_item_id
537 and items.cross_reference = ordered_item
538 and items.cross_reference_type = item_identifier_type
539 and nvl(sitems.organization_id,0) = c_master_org
540 and sitems.inventory_item_id = inventory_item_id_T
541 -- Bug 3433353 Start
542 and items.org_independent_flag = 'N'
543 and items.organization_id = c_master_org;
544 -- and sitems.customer_order_enabled_flag = 'Y'
545 -- and sitems.bom_item_type in (1,4)
546 Exception When NO_DATA_FOUND Then
547 Select items.cross_reference item,
548 nvl(items.description,sitems.description) description
549 into v_item,v_description
550 from mtl_cross_reference_types xtypes,
551 mtl_cross_references items,
552 mtl_system_items_vl sitems
553 where xtypes.cross_reference_type =
554 items.cross_reference_type
555 and items.inventory_item_id =
556 sitems.inventory_item_id
557 and items.cross_reference = ordered_item
558 and items.cross_reference_type = item_identifier_type
559 and nvl(sitems.organization_id,0) = c_master_org
560 and sitems.inventory_item_id = inventory_item_id_T
561 and items.org_independent_flag = 'Y';
562 End;
563 --Bug 3433353 End
564 end if;
565
566 if (p_print_description in ('I','C')) then
567 return(v_item||' - '||v_description);
568 elsif (p_print_description in ('D','P')) then
569 return(v_description);
570 else
571 return(v_item);
572 end if;
573 RETURN NULL;
574 Exception
575 When Others Then
576 return('Item Not Found');
577 end;
578
579
580 END ONT_OEXOEITR_XMLP_PKG;
581
[Home] [Help]