[Home] [Help]
PACKAGE BODY: APPS.ONT_OEXOEORD_XMLP_PKG
Source
1 PACKAGE BODY ONT_OEXOEORD_XMLP_PKG AS
2 /* $Header: OEXOEORDB.pls 120.3 2008/05/05 12:39:26 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 APF BOOLEAN;
5 APF1 BOOLEAN;
6 BEGIN
7 BEGIN
8 BEGIN
9 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
10 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
11 EXCEPTION
12 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
13 /*SRW.MESSAGE(1000
14 ,'Failed in BEFORE REPORT trigger')*/NULL;
15 RETURN (FALSE);
16 END;
17 BEGIN
18 P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
19 END;
20 BEGIN
21 IF P_ITEM IS NOT NULL THEN
22 SELECT
23 CONCATENATED_SEGMENTS
24 INTO P_ITEM_NAME
25 FROM
26 MTL_SYSTEM_ITEMS_KFV
27 WHERE INVENTORY_ITEM_ID = P_ITEM
28 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
29 AND BOM_ITEM_TYPE in ( 1 , 4 )
30 AND ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
31 END IF;
32 END;
33 DECLARE
34 L_COMPANY_NAME VARCHAR2(100);
35 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
36 BEGIN
37 SELECT
38 SOB.NAME,
39 SOB.CURRENCY_CODE
40 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
41 FROM
42 GL_SETS_OF_BOOKS SOB,
43 FND_CURRENCIES CUR
44 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
45 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
46 RP_COMPANY_NAME := L_COMPANY_NAME;
47 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 NULL;
51 END;
52 BEGIN
53 RP_CURR_PROFILE := FND_PROFILE.VALUE('ONT_UNIT_PRICE_PRECISION_TYPE');
54 EXCEPTION
55 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
56 /*SRW.MESSAGE(3000
57 ,'Failed in BEFORE REPORT Trigger FND GETPROFILE USER_EXIT')*/NULL;
58 END;
59 DECLARE
60 L_REPORT_NAME VARCHAR2(240);
61 BEGIN
62 SELECT
63 CP.USER_CONCURRENT_PROGRAM_NAME
64 INTO L_REPORT_NAME
65 FROM
66 FND_CONCURRENT_PROGRAMS_VL CP,
67 FND_CONCURRENT_REQUESTS CR
68 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
69 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
70 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
71 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 RP_REPORT_NAME := 'Credit Order Detail Report';
75 END;
76 BEGIN
77 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
78 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
79 IF (P_ITEM IS NOT NULL) THEN
80 LP_ITEM := ' AND ' || LP_ITEM;
81 ELSE
82 LP_ITEM := ' ';
83 END IF;
84 EXCEPTION
85 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
86 /*SRW.MESSAGE(2000
87 ,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT')*/NULL;
88 END;
89 DECLARE
90 L_MEANING VARCHAR2(80);
91 BEGIN
92 SELECT
93 MEANING
94 INTO L_MEANING
95 FROM
96 OE_LOOKUPS
97 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
98 AND LOOKUP_CODE = SUBSTR(UPPER(P_PRINT_DESCRIPTION)
99 ,1
100 ,1);
101 RP_PRINT_DESCRIPTION := L_MEANING;
102 EXCEPTION
103 WHEN NO_DATA_FOUND THEN
104 RP_PRINT_DESCRIPTION := 'Description';
105 END;
106 DECLARE
107 L_MEANING VARCHAR2(80);
108 BEGIN
109 SELECT
110 MEANING
111 INTO L_MEANING
112 FROM
113 OE_LOOKUPS
114 WHERE LOOKUP_TYPE = 'YES_NO'
115 AND LOOKUP_CODE = SUBSTR(UPPER(P_OPEN_RETURNS_ONLY)
116 ,1
117 ,1);
118 RP_OPEN_RETURNS_ONLY := L_MEANING;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 RP_OPEN_RETURNS_ONLY := 'Yes';
122 END;
123 END;
124 APF := P_ORDER_BYVALIDTRIGGER;
125 APF1 := P_CREDIT_ONLYVALIDTRIGGER;
126 RETURN (TRUE);
127 END BEFOREREPORT;
128
129 FUNCTION AFTERREPORT RETURN BOOLEAN IS
130 BEGIN
131 BEGIN
132 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
133 EXCEPTION
134 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
135 /*SRW.MESSAGE(1
136 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
137 RETURN (FALSE);
138 END;
139 RETURN (TRUE);
140 END AFTERREPORT;
141
142 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
143 BEGIN
144 RETURN (TRUE);
145 END P_ITEM_FLEX_CODEVALIDTRIGGER;
146
147 FUNCTION AFTERPFORM RETURN BOOLEAN IS
148 BEGIN
149 /*SRW.MESSAGE(99999
150 ,'$Header: OEXOEORDB.pls 120.3 2008/05/05 12:39:26 dwkrishn noship $')*/NULL;
151 DECLARE
152 BLANKS CONSTANT VARCHAR2(5) DEFAULT ' ';
153 ALL_RANGE CONSTANT VARCHAR2(16) DEFAULT 'From' || BLANKS || 'To' || BLANKS;
154 BEGIN
155 IF (P_RETURN_TYPE IS NOT NULL) THEN
156 LP_RETURN_TYPE := ' AND otype.transaction_type_id = :P_RETURN_TYPE ';
157 SELECT
158 OEOT.NAME
159 INTO L_ORDER_TYPE
160 FROM
161 OE_TRANSACTION_TYPES_TL OEOT
162 WHERE OEOT.TRANSACTION_TYPE_ID = P_RETURN_TYPE
163 AND OEOT.LANGUAGE = USERENV('LANG');
164 ELSE
165 LP_RETURN_TYPE := ' ';
166 END IF;
167 IF (P_SALESREP IS NOT NULL) THEN
168 LP_SALESREP := ' AND SR.NAME = :P_SALESREP ';
169 ELSE
170 LP_SALESREP := ' ';
171 END IF;
172 SELECT
173 USERENV('LANG')
174 INTO P_LANG
175 FROM
176 DUAL;
177 P_ORGANIZATION_ID := NVL(P_ORGANIZATION_ID
178 ,0);
179 IF (P_RETURN_LINE_TYPE IS NOT NULL) THEN
180 LP_RETURN_LINE_TYPE := 'and ltype.transaction_type_id = :p_return_line_type ';
181 SELECT
182 OEOT.NAME
183 INTO L_LINE_TYPE
184 FROM
185 OE_TRANSACTION_TYPES_TL OEOT
186 WHERE OEOT.TRANSACTION_TYPE_ID = P_RETURN_LINE_TYPE
187 AND OEOT.LANGUAGE = USERENV('LANG');
188 ELSE
189 LP_RETURN_LINE_TYPE := ' ';
190 END IF;
191 IF (P_LINE_CATEGORY = 'CREDIT') OR (P_LINE_CATEGORY IS NULL) THEN
192 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
193 ELSE
194 LP_LINE_CATEGORY := ' ';
195 END IF;
196 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
197 IF (P_CUSTOMER_NAME_LOW = P_CUSTOMER_NAME_HIGH) THEN
198 LP_CUSTOMER_NAME := ' and party.party_name = :p_customer_name_low';
199 ELSE
200 LP_CUSTOMER_NAME := ' AND (party.party_name BETWEEN' || ' :P_CUSTOMER_NAME_LOW AND' || ' :P_CUSTOMER_NAME_HIGH) ';
201 END IF;
202 CUSTOMER_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NAME_LOW
203 ,1
204 ,20) || ' To ' || SUBSTR(P_CUSTOMER_NAME_HIGH
205 ,1
206 ,20);
207 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
208 LP_CUSTOMER_NAME := ' AND party.party_name >=' || ' :P_CUSTOMER_NAME_LOW ';
209 CUSTOMER_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NAME_LOW
210 ,1
211 ,20) || ' To ' || BLANKS;
212 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
213 LP_CUSTOMER_NAME := ' AND party.party_name <=' || ' :P_CUSTOMER_NAME_HIGH ';
214 CUSTOMER_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_CUSTOMER_NAME_HIGH
215 ,1
216 ,20);
217 ELSE
218 LP_CUSTOMER_NAME := ' ';
219 CUSTOMER_PARMS := ALL_RANGE;
220 END IF;
221 IF (P_OPEN_RETURNS_ONLY = 'Y') THEN
222 LP_OPEN_RETURNS_ONLY := 'AND H.OPEN_FLAG = ''Y''';
223 ELSE
224 LP_OPEN_RETURNS_ONLY := ' ';
225 END IF;
226 IF (P_CUSTOMER_NUM_LOW IS NOT NULL) AND (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
227 IF (P_CUSTOMER_NUM_LOW = P_CUSTOMER_NUM_HIGH) THEN
228 LP_CUSTOMER_NUM := ' and cust.account_number = :p_customer_num_low';
229 ELSE
230 LP_CUSTOMER_NUM := ' AND (cust.account_number BETWEEN' || ' :P_CUSTOMER_NUM_LOW AND' || ' :P_CUSTOMER_NUM_HIGH) ';
231 END IF;
232 CUSTOMER_NUM_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NUM_LOW
233 ,1
234 ,20) || ' To ' || SUBSTR(P_CUSTOMER_NUM_HIGH
235 ,1
236 ,20);
237 ELSIF (P_CUSTOMER_NUM_LOW IS NOT NULL) THEN
238 LP_CUSTOMER_NUM := ' AND cust.account_number >=' || ' :P_CUSTOMER_NUM_LOW ';
239 CUSTOMER_NUM_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NUM_LOW
240 ,1
241 ,20) || ' To ' || BLANKS;
242 ELSIF (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
243 LP_CUSTOMER_NUM := ' AND cust.account_number <=' || ' :P_CUSTOMER_NUM_HIGH ';
244 CUSTOMER_NUM_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_CUSTOMER_NUM_HIGH
245 ,1
246 ,20);
247 ELSE
248 CUSTOMER_NUM_PARMS := ALL_RANGE;
249 LP_CUSTOMER_NUM := ' ';
250 END IF;
251 IF (P_RETURN_NUM_LOW IS NOT NULL) AND (P_RETURN_NUM_HIGH IS NOT NULL) THEN
252 IF (P_RETURN_NUM_LOW = P_RETURN_NUM_HIGH) THEN
253 LP_RETURN_NUM := ' and h.order_number= :p_return_num_low';
254 ELSE
255 LP_RETURN_NUM := ' AND (H.ORDER_NUMBER BETWEEN' || ' TO_NUMBER(:P_RETURN_NUM_LOW) AND' || ' TO_NUMBER(:P_RETURN_NUM_HIGH)) ';
256 END IF;
257 RETURN_NUM_PARMS := 'From ' || P_RETURN_NUM_LOW || ' To ' || P_RETURN_NUM_HIGH;
258 ELSIF (P_RETURN_NUM_LOW IS NOT NULL) THEN
259 LP_RETURN_NUM := ' AND H.ORDER_NUMBER >= ' || ' TO_NUMBER(:P_RETURN_NUM_LOW) ';
260 RETURN_NUM_PARMS := 'From ' || P_RETURN_NUM_LOW || ' To ' || BLANKS;
261 ELSIF (P_RETURN_NUM_HIGH IS NOT NULL) THEN
262 LP_RETURN_NUM := ' AND H.ORDER_NUMBER <=' || ' TO_NUMBER(:P_RETURN_NUM_HIGH) ';
263 RETURN_NUM_PARMS := 'From ' || BLANKS || 'To ' || P_RETURN_NUM_HIGH;
264 ELSE
265 LP_RETURN_NUM :=' ';
266 RETURN_NUM_PARMS := ' ';
267 END IF;
268 IF (P_RETURN_DATE_LOW IS NOT NULL) AND (P_RETURN_DATE_HIGH IS NOT NULL) THEN
269 IF (P_RETURN_DATE_LOW = P_RETURN_DATE_HIGH) THEN
270 LP_RETURN_DATE := ' and h.ordered_date = :p_return_date_low';
271 ELSE
272 LP_RETURN_DATE := ' AND (H.ORDERED_DATE BETWEEN' || ' :P_RETURN_DATE_LOW' || ' AND' || ' :P_RETURN_DATE_HIGH) ';
273 END IF;
274 RETURN_DATE_PARMS := 'From ' || TO_CHAR(P_RETURN_DATE_LOW
275 ,'YYYY/MM/DD') || ' To ' || TO_CHAR(P_RETURN_DATE_HIGH
276 ,'YYYY/MM/DD');
277 ELSIF (P_RETURN_DATE_LOW IS NOT NULL) THEN
278 LP_RETURN_DATE := ' AND H.ORDERED_DATE >=' || ' :P_RETURN_DATE_LOW ';
279 RETURN_DATE_PARMS := 'From ' || TO_CHAR(P_RETURN_DATE_LOW
280 ,'YYYY/MM/DD') || ' To ' || BLANKS;
281 ELSIF (P_RETURN_DATE_HIGH IS NOT NULL) THEN
282 LP_RETURN_DATE := ' AND H.ORDERED_DATE <=' || ' :P_RETURN_DATE_HIGH ';
283 RETURN_DATE_PARMS := 'From ' || BLANKS || 'To ' || TO_CHAR(P_RETURN_DATE_HIGH
284 ,'YYYY/MM/DD');
285 ELSE
286 LP_RETURN_DATE := ' ';
287 RETURN_DATE_PARMS := ' ';
288 END IF;
289 END;
290 RETURN (TRUE);
291 END AFTERPFORM;
292
293 FUNCTION P_ORDER_BYVALIDTRIGGER RETURN BOOLEAN IS
294 BEGIN
295 BEGIN
296 IF (P_ORDER_BY IS NOT NULL) THEN
297 SELECT
298 SUBSTR(MEANING
299 ,1
300 ,50)
301 INTO SORT_BY_MEANING
302 FROM
303 OE_LOOKUPS
304 WHERE LOOKUP_TYPE = 'OEXOEORD ORDER BY'
305 AND LOOKUP_CODE = P_ORDER_BY;
306 ELSE
307 SELECT
308 SUBSTR(MEANING
309 ,1
310 ,50)
311 INTO SORT_BY_MEANING
312 FROM
313 OE_LOOKUPS
314 WHERE LOOKUP_TYPE = 'OEXOEORD ORDER BY'
315 AND LOOKUP_CODE = 'RETURN_NUMBER';
316 END IF;
317 RETURN (TRUE);
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 RETURN (FALSE);
321 END;
322 RETURN (TRUE);
323 END P_ORDER_BYVALIDTRIGGER;
324
325 FUNCTION P_ITEM_DISPLAYVALIDTRIGGER RETURN BOOLEAN IS
326 BEGIN
327 RETURN NULL;
328 END P_ITEM_DISPLAYVALIDTRIGGER;
329
330 FUNCTION AMT_EXPECTEDFORMULA(QTY_AUTHORIZED IN NUMBER
331 ,SELLING_PRICE IN NUMBER
332 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
333 BEGIN
334 DECLARE
335 L_STD_PRECISION NUMBER;
336 L_EXT_PRECISION NUMBER;
337 L_MIN_ACCT_UNIT NUMBER;
338 L_AMT_EXPECTED NUMBER;
339 BEGIN
340 /*SRW.REFERENCE(QTY_AUTHORIZED)*/NULL;
341 /*SRW.REFERENCE(SELLING_PRICE)*/NULL;
342 /*SRW.REFERENCE(P_MIN_PRECISION)*/NULL;
343 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
344 /*SRW.REFERENCE(RP_CURR_PROFILE)*/NULL;
345 FND_CURRENCY.GET_INFO(CURRENCY_CODE
346 ,L_STD_PRECISION
347 ,L_EXT_PRECISION
348 ,L_MIN_ACCT_UNIT);
349 L_AMT_EXPECTED := NVL(QTY_AUTHORIZED
350 ,0) * NVL(SELLING_PRICE
351 ,0);
352 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
353 L_AMT_EXPECTED := ROUND(L_AMT_EXPECTED
354 ,L_EXT_PRECISION);
355 ELSE
356 L_AMT_EXPECTED := ROUND(L_AMT_EXPECTED
357 ,L_STD_PRECISION);
358 END IF;
359 RETURN (L_AMT_EXPECTED);
360 EXCEPTION
361 WHEN OTHERS THEN
362 RETURN NULL;
363 END;
364 END AMT_EXPECTEDFORMULA;
365
366 FUNCTION AMT_RECEIVEDFORMULA(QTY_RECEIVED IN NUMBER
367 ,SELLING_PRICE IN NUMBER
368 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
369 BEGIN
370 DECLARE
371 L_STD_PRECISION NUMBER;
372 L_EXT_PRECISION NUMBER;
373 L_MIN_ACCT_UNIT NUMBER;
374 L_AMT_RECEIVED NUMBER;
375 BEGIN
376 /*SRW.REFERENCE(QTY_RECEIVED)*/NULL;
377 /*SRW.REFERENCE(P_MIN_PRECISION)*/NULL;
378 /*SRW.REFERENCE(SELLING_PRICE)*/NULL;
379 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
380 /*SRW.REFERENCE(RP_CURR_PROFILE)*/NULL;
381 FND_CURRENCY.GET_INFO(CURRENCY_CODE
382 ,L_STD_PRECISION
383 ,L_EXT_PRECISION
384 ,L_MIN_ACCT_UNIT);
385 L_AMT_RECEIVED := NVL(QTY_RECEIVED
386 ,0) * NVL(SELLING_PRICE
387 ,0);
388 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
389 L_AMT_RECEIVED := ROUND(L_AMT_RECEIVED
390 ,L_EXT_PRECISION);
391 ELSE
392 L_AMT_RECEIVED := ROUND(L_AMT_RECEIVED
393 ,L_STD_PRECISION);
394 END IF;
395 RETURN (L_AMT_RECEIVED);
396 EXCEPTION
397 WHEN OTHERS THEN
398 RETURN NULL;
399 END;
400 END AMT_RECEIVEDFORMULA;
401
402 FUNCTION AMT_ACCEPTEDFORMULA(QTY_ACCEPTED IN NUMBER
403 ,SELLING_PRICE IN NUMBER
404 ,CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
405 BEGIN
406 DECLARE
407 L_STD_PRECISION NUMBER;
408 L_EXT_PRECISION NUMBER;
409 L_MIN_ACCT_UNIT NUMBER;
410 L_AMT_ACCEPTED NUMBER;
411 BEGIN
412 /*SRW.REFERENCE(QTY_ACCEPTED)*/NULL;
413 /*SRW.REFERENCE(P_MIN_PRECISION)*/NULL;
414 /*SRW.REFERENCE(SELLING_PRICE)*/NULL;
415 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
416 /*SRW.REFERENCE(RP_CURR_PROFILE)*/NULL;
417 FND_CURRENCY.GET_INFO(CURRENCY_CODE
418 ,L_STD_PRECISION
419 ,L_EXT_PRECISION
420 ,L_MIN_ACCT_UNIT);
421 L_AMT_ACCEPTED := NVL(QTY_ACCEPTED
422 ,0) * NVL(SELLING_PRICE
423 ,0);
424 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
425 L_AMT_ACCEPTED := ROUND(L_AMT_ACCEPTED
426 ,L_EXT_PRECISION);
427 ELSE
428 L_AMT_ACCEPTED := ROUND(L_AMT_ACCEPTED
429 ,L_STD_PRECISION);
430 END IF;
431 RETURN (L_AMT_ACCEPTED);
432 EXCEPTION
433 WHEN OTHERS THEN
434 RETURN NULL;
435 END;
436 END AMT_ACCEPTEDFORMULA;
437
438 FUNCTION P_CREDIT_ONLYVALIDTRIGGER RETURN BOOLEAN IS
439 BEGIN
440 BEGIN
441 IF (P_CREDIT_ONLY IS NOT NULL) THEN
442 SELECT
443 SUBSTR(MEANING
444 ,1
445 ,5)
446 INTO CREDIT_ONLY_MEANING
447 FROM
448 FND_LOOKUPS
449 WHERE LOOKUP_TYPE = 'YES_NO'
450 AND LOOKUP_CODE = SUBSTR(UPPER(P_CREDIT_ONLY)
451 ,1
452 ,1);
453 END IF;
454 RETURN (TRUE);
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RETURN (FALSE);
458 END;
459 RETURN (TRUE);
460 END P_CREDIT_ONLYVALIDTRIGGER;
461
462 FUNCTION REF_NUMFORMULA(REF_ID IN VARCHAR2
463 ,REF_TYPE_CODE IN VARCHAR2
464 ,LINE_ID IN NUMBER) RETURN VARCHAR2 IS
465 BEGIN
466 DECLARE
467 REF_NUMBER VARCHAR2(240);
468 BEGIN
469 IF (REF_ID IS NOT NULL) THEN
470 IF (REF_TYPE_CODE = 'INVOICE') THEN
471 SELECT
472 TRX.TRX_NUMBER
473 INTO REF_NUMBER
474 FROM
475 RA_CUSTOMER_TRX TRX,
476 RA_CUSTOMER_TRX_LINES_ALL TRXL
477 WHERE TRXL.CUSTOMER_TRX_LINE_ID = REF_ID
478 AND TRXL.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID;
479 ELSIF (REF_TYPE_CODE = 'SERIAL') THEN
480 SELECT
481 L2.RETURN_ATTRIBUTE2
482 INTO REF_NUMBER
483 FROM
484 OE_ORDER_LINES_ALL L2
485 WHERE L2.LINE_ID = LINE_ID;
486 ELSIF (REF_TYPE_CODE = 'ORDER') THEN
487 SELECT
488 OH2.ORDER_NUMBER
489 INTO REF_NUMBER
490 FROM
491 OE_ORDER_HEADERS OH2
492 WHERE OH2.HEADER_ID = REF_ID;
493 ELSIF (REF_TYPE_CODE = 'PO') THEN
494 SELECT
495 OH2.CUST_PO_NUMBER
496 INTO REF_NUMBER
497 FROM
498 OE_ORDER_HEADERS OH2
499 WHERE OH2.HEADER_ID = REF_ID;
500 END IF;
501 END IF;
502 RETURN (REF_NUMBER);
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 RETURN (NULL);
506 END;
507 RETURN NULL;
508 END REF_NUMFORMULA;
509
510 FUNCTION C_TOTAL_RMAFORMULA(TOTAL_RMA IN NUMBER) RETURN NUMBER IS
511 BEGIN
512 RETURN (TOTAL_RMA);
513 END C_TOTAL_RMAFORMULA;
514
515 FUNCTION C_TOTAL_LINESFORMULA(TOTAL_LINES IN NUMBER) RETURN NUMBER IS
516 BEGIN
517 RETURN (TOTAL_LINES);
518 END C_TOTAL_LINESFORMULA;
519
520 FUNCTION C_MASTER_ORGFORMULA RETURN NUMBER IS
521 V_MASTER_ORG VARCHAR2(20);
522 BEGIN
523 V_MASTER_ORG := NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
524 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
525 ,0);
526 RETURN V_MASTER_ORG;
527 END C_MASTER_ORGFORMULA;
528
529 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
530 BEGIN
531 RETURN RP_REPORT_NAME;
532 END RP_REPORT_NAME_P;
533
534 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
535 BEGIN
536 RETURN RP_SUB_TITLE;
537 END RP_SUB_TITLE_P;
538
539 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
540 BEGIN
541 RETURN RP_COMPANY_NAME;
542 END RP_COMPANY_NAME_P;
543
544 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
545 BEGIN
546 RETURN RP_FUNCTIONAL_CURRENCY;
547 END RP_FUNCTIONAL_CURRENCY_P;
548
549 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
550 BEGIN
551 RETURN RP_DATA_FOUND;
552 END RP_DATA_FOUND_P;
553
554 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
555 BEGIN
556 RETURN RP_ITEM_FLEX_ALL_SEG;
557 END RP_ITEM_FLEX_ALL_SEG_P;
558
559 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
560 BEGIN
561 RETURN RP_PRINT_DESCRIPTION;
562 END RP_PRINT_DESCRIPTION_P;
563
564 FUNCTION RP_OPEN_RETURNS_ONLY_P RETURN VARCHAR2 IS
565 BEGIN
566 RETURN RP_OPEN_RETURNS_ONLY;
567 END RP_OPEN_RETURNS_ONLY_P;
568
569 FUNCTION RP_CURR_PROFILE_P RETURN VARCHAR2 IS
570 BEGIN
571 RETURN RP_CURR_PROFILE;
572 END RP_CURR_PROFILE_P;
573
574 FUNCTION ITEM_DSPFORMULA(ITEM_IDENTIFIER_TYPE IN VARCHAR2,INVENTORY_ITEM_ID1 IN NUMBER,ORDERED_ITEM_ID IN NUMBER,ORDERED_ITEM IN VARCHAR2,C_ORGANIZATION_ID IN VARCHAR2,C_INVENTORY_ITEM_ID IN VARCHAR2) RETURN CHAR IS
575 v_item varchar2(2000);
576 v_description varchar2(500);
577 begin
578 if (item_identifier_type is null or item_identifier_type = 'INT')
579 or (p_print_description in ('I','D','F')) then
580 select sitems.concatenated_segments item,
581 sitems.description description
582 into v_item,v_description
583 from mtl_system_items_vl sitems
584 -- where sitems.customer_order_enabled_flag = 'Y'
585 -- and sitems.bom_item_type in (1,4)
586 where nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
587 and sitems.inventory_item_id = inventory_item_id1;
588 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code, p_item_structure_num, C_ORGANIZATION_ID, C_INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE');
589
590 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
591 select citems.customer_item_number item,
592 nvl(citems.customer_item_desc,sitems.description) description
593 into v_item,v_description
594 from mtl_customer_items citems,
595 mtl_customer_item_xrefs cxref,
596 mtl_system_items_vl sitems
597 where citems.customer_item_id = cxref.customer_item_id
598 and cxref.inventory_item_id = sitems.inventory_item_id
599 and citems.customer_item_id = ordered_item_id
600 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
601 and sitems.inventory_item_id = inventory_item_id1;
602 -- and sitems.customer_order_enabled_flag = 'Y'
603 -- and sitems.bom_item_type in (1,4)
604 elsif (p_print_description in ('C','P','O')) then
605 Begin
606 select items.cross_reference item,
607 nvl(items.description,sitems.description) description
608 into v_item,v_description
609 from mtl_cross_reference_types xtypes,
610 mtl_cross_references items,
611 mtl_system_items_vl sitems
612 where xtypes.cross_reference_type = items.cross_reference_type
613 and items.inventory_item_id = sitems.inventory_item_id
614 and items.cross_reference = ordered_item
615 and items.cross_reference_type = item_identifier_type
616 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
617 and sitems.inventory_item_id = inventory_item_id1
618 --Bug 3433353 Start
619 and items.org_independent_flag = 'N'
620 and items.organization_id = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0);
621 -- and sitems.customer_order_enabled_flag = 'Y'
622 -- and sitems.bom_item_type in (1,4)
623 Exception When NO_DATA_FOUND Then
624 Select items.cross_reference item,
625 nvl(items.description,sitems.description) description into
626 v_item,v_description
627 from mtl_cross_reference_types xtypes,
628 mtl_cross_references items,
629 mtl_system_items_vl sitems
630 where xtypes.cross_reference_type =
631 items.cross_reference_type
632 and items.inventory_item_id =
633 sitems.inventory_item_id
634 and items.cross_reference = ordered_item
635 and items.cross_reference_type = item_identifier_type
636 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
637 and sitems.inventory_item_id = inventory_item_id1
638 and items.org_independent_flag = 'Y';
639 End;
640 --Bug 3433353 End
641 end if;
642
643 if (p_print_description in ('I','C')) then
644 return(v_item||' - '||v_description);
645 elsif (p_print_description in ('D','P')) then
646 return(v_description);
647 else
648 return(v_item);
649 end if;
650 RETURN NULL;
651 Exception
652 When Others Then
653 return('Item Not Found');
654 end;
655
656
657 END ONT_OEXOEORD_XMLP_PKG;
658