1 PACKAGE BODY ONT_OEXOEORS_XMLP_PKG AS
2 /* $Header: OEXOEORSB.pls 120.3 2008/05/05 12:41:59 dwkrishn noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 DECLARE
8 BEGIN
9 --added as fix
10 P_RETURN_DATE_LOW_V :=to_char(P_RETURN_DATE_LOW,'DD-MON-YY');
11 P_RETURN_DATE_HIGH_V :=to_char(P_RETURN_DATE_HIGH,'DD-MON-YY');
12 P_EXP_REC_DATE_LOW_V :=to_char(P_EXP_REC_DATE_LOW,'DD-MON-YY');
13 P_EXP_REC_DATE_HIGH_V :=to_char(P_EXP_REC_DATE_HIGH,'DD-MON-YY');
14
15 BEGIN
16 null;
17 EXCEPTION
18 --WHEN SRW.USER_EXIT_FAILURE THEN
19 when others then
20 null;
21 --SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger');
22 return (FALSE);
23 END;
24
25 BEGIN
26
27 --P_ORGANIZATION_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
28 P_ORGANIZATION_ID_V:= MO_GLOBAL.GET_CURRENT_ORG_ID();
29
30 END;
31 /*------------------------------------------------------------------------------
32 Following PL/SQL block gets the company name, functional currency and precision.
33 ------------------------------------------------------------------------------*/
34
35
36 DECLARE
37 l_company_name VARCHAR2 (100);
38 l_functional_currency VARCHAR2 (15);
39
40 BEGIN
41
42 SELECT sob.name ,
43 sob.currency_code
44 INTO
45 l_company_name ,
46 l_functional_currency
47 FROM gl_sets_of_books sob,
48 fnd_currencies cur
49 WHERE sob.set_of_books_id = p_sob_id
50 AND sob.currency_code = cur.currency_code
51 ;
52
53 rp_company_name := l_company_name;
54 rp_functional_currency := l_functional_currency ;
55
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 NULL ;
59 END ;
60
61 DECLARE
62 l_report_name VARCHAR2(240);
63 BEGIN
64 SELECT cp.user_concurrent_program_name
65 INTO l_report_name
66 FROM 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 ;
72
73 RP_Report_Name := l_report_name;
74
75 RP_Report_Name := substr(RP_Report_Name,1,instr(RP_Report_Name,' (XML)'));
76 EXCEPTION
77 WHEN NO_DATA_FOUND
78 THEN RP_REPORT_NAME := 'Credit Order Summary Report';
79 END;
80
81 /*------------------------------------------------------------------------------
82 Following PL/SQL block builds up the lexical parameters, to be used in the
83 WHERE clause of the query. This also populates the report level variables, used
84 to store the flexfield structure.
85 ------------------------------------------------------------------------------*/
86 BEGIN
87 -- SRW.REFERENCE(:P_item_flex_code);
88 -- SRW.REFERENCE(:P_ITEM_STRUCTURE_NUM);
89
90 null;
91
92 /* SRW.USER_EXIT('FND FLEXSQL CODE=":p_item_flex_code"
93 NUM=":p_item_structure_num"
94 APPL_SHORT_NAME="INV"
95 OUTPUT=":rp_item_flex_all_seg"
96 MODE="SELECT"
97 DISPLAY="ALL"
98 TABLEALIAS="SI"
99 ');*/
100
101 EXCEPTION
102 --WHEN SRW.USER_EXIT_FAILURE THEN
103 --srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT');
104 when others then
105 null;
106 END;
107
108
109
110 DECLARE
111 l_return_date_low VARCHAR2 (50);
112 l_return_date_high VARCHAR2 (50);
113 l_exp_rec_date_low VARCHAR2 (50);
114 l_exp_rec_date_high VARCHAR2 (50);
115 l_return_number_low VARCHAR2 (50);
116 l_return_number_high VARCHAR2 (50);
117 l_customer_name_low VARCHAR2 (50);
118 l_customer_name_high VARCHAR2 (50);
119 l_customer_number_low VARCHAR2 (50);
120 l_customer_number_high VARCHAR2 (50);
121
122 BEGIN
123
124 if ( p_return_date_low is NULL) AND ( p_return_date_high is NULL ) then
125 NULL ;
126 else
127 if p_return_date_low is NULL then
128 l_return_date_low := ' ';
129 else
130 l_return_date_low := to_char(p_return_date_low, 'DD-MON-RRRR');
131 end if ;
132 if p_return_date_high is NULL then
133 l_return_date_high := ' ';
134 else
135 l_return_date_high := to_char(p_return_date_high, 'DD-MON-RRRR');
136 end if ;
137 rp_return_date_range := 'From '||l_return_date_low||' To '||l_return_date_high ;
138
139 end if ;
140
141
142 if ( p_exp_rec_date_low is NULL) AND ( p_exp_rec_date_high is NULL ) then
143 NULL ;
144 else
145 if p_exp_rec_date_low is NULL then
146 l_exp_rec_date_low := ' ';
147 else
148 l_exp_rec_date_low := to_char(p_exp_rec_date_low, 'DD-MON-RRRR');
149 end if ;
150 if p_exp_rec_date_high is NULL then
151 l_exp_rec_date_high := ' ';
152 else
153 l_exp_rec_date_high := to_char(p_exp_rec_date_high, 'DD-MON-RRRR');
154 end if ;
155 rp_exp_rec_date_range := 'From '||l_exp_rec_date_low||' To '||l_exp_rec_date_high ;
156 end if ;
157
158
159 if ( p_return_num_low is NULL) AND ( p_return_num_high is NULL ) then
160 NULL ;
161 else
162 if p_return_num_low is NULL then
163 l_return_number_low := ' ';
164 else
165 l_return_number_low := substr(p_return_num_low,1,18) ;
166 end if ;
167 if p_return_num_high is NULL then
168 l_return_number_high := ' ';
169 else
170 l_return_number_high := substr((p_return_num_high),1,18);
171 end if ;
172 rp_return_number_range := 'From '||l_return_number_low||' To '||l_return_number_high ;
173 end if ;
174
175
176 if ( p_customer_number_low is NULL) AND ( p_customer_number_high is NULL ) then
177 NULL ;
178 else
179 if p_customer_number_low is NULL then
180 l_customer_number_low := ' ';
181 else
182 l_customer_number_low := substr(p_customer_number_low,1,18) ;
183 end if ;
184 if p_customer_number_high is NULL then
185 l_customer_number_high := ' ';
186 else
187 l_customer_number_high := substr((p_customer_number_high),1,18);
188 end if ;
189 rp_cust_no_range := 'From '||l_customer_number_low||' To '||l_customer_number_high ;
190 end if ;
191
192
193 if ( p_customer_name_low is NULL) AND ( p_customer_name_high is NULL ) then
194 NULL ;
195 else
196 if p_customer_name_low is NULL then
197 l_customer_name_low := ' ';
198 else
199 l_customer_name_low := substr(p_customer_name_low,1,18) ;
200 end if ;
201 if p_customer_name_high is NULL then
202 l_customer_name_high := ' ';
203 else
204 l_customer_name_high := substr((p_customer_name_high),1,18);
205 end if ;
206 rp_cust_name_range := 'From '||l_customer_name_low||' To '||l_customer_name_high ;
207 end if ;
208
209
210 END ;
211
212
213 DECLARE
214 l_meaning VARCHAR2 (80);
215 BEGIN
216 SELECT MEANING
217 INTO l_meaning
218 FROM OE_LOOKUPS
219 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
220 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
221 ;
222
223 rp_print_description := l_meaning ;
224 EXCEPTION WHEN NO_DATA_FOUND THEN
225 rp_print_description := 'Description';
226 END ;
227
228 DECLARE
229 l_meaning VARCHAR2 (80);
230 BEGIN
231 SELECT MEANING
232 INTO l_meaning
233 FROM OE_LOOKUPS
234 WHERE LOOKUP_TYPE = 'YES_NO'
235 AND LOOKUP_CODE = substr(upper(p_open_returns_only),1,1)
236 ;
237
238 rp_open_returns_only := l_meaning ;
239 EXCEPTION WHEN NO_DATA_FOUND THEN
240 rp_open_returns_only := 'Yes';
241 END ;
242
243 DECLARE
244 l_fc_display VARCHAR2(80);
245 BEGIN
246 select meaning
247 into l_fc_display
248 from oe_lookups
249 where lookup_type='YES_NO'
250 and lookup_code = p_use_functional_currency
251 ;
252
253 rp_use_functional_currency := l_fc_display ;
254
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 NULL;
258 END;
259
260 EXCEPTION WHEN OTHERS THEN
261 -- SRW.MESSAGE (4000, ' Error in Before Report Trigger');
262 null;
263
264 END ;
265 return (TRUE);
266 end;
267 FUNCTION AFTERREPORT RETURN BOOLEAN IS
268 BEGIN
269 BEGIN
270 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
271 EXCEPTION
272 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
273 /*SRW.MESSAGE(1
274 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
275 RETURN (FALSE);
276 END;
277 RETURN (TRUE);
278 END AFTERREPORT;
279
280 FUNCTION P_ORGANIZATION_IDVALIDTRIGGER RETURN BOOLEAN IS
281 BEGIN
282 RETURN (TRUE);
283 END P_ORGANIZATION_IDVALIDTRIGGER;
284
285 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
286 BEGIN
287 RETURN (TRUE);
288 END P_ITEM_FLEX_CODEVALIDTRIGGER;
289
290 FUNCTION P_SOB_IDVALIDTRIGGER RETURN BOOLEAN IS
291 BEGIN
292 RETURN (TRUE);
293 END P_SOB_IDVALIDTRIGGER;
294
295 FUNCTION P_USE_FUNCTIONAL_CURRENCYVALID RETURN BOOLEAN IS
296 BEGIN
297 RETURN (TRUE);
298 END P_USE_FUNCTIONAL_CURRENCYVALID;
299
300 FUNCTION C_ACTUAL_RECEIPT_DAYS(QTY_AUTHORIZED IN NUMBER
301 ,RECEIPT_DAYS IN NUMBER) RETURN NUMBER IS
302 ACTUAL_DAYS NUMBER;
303 BEGIN
304 IF QTY_AUTHORIZED = 0 THEN
305 ACTUAL_DAYS := 0;
306 ELSE
307 ACTUAL_DAYS := RECEIPT_DAYS;
308 END IF;
309 RETURN (ACTUAL_DAYS);
310 END C_ACTUAL_RECEIPT_DAYS;
311
312 FUNCTION C_ACTUAL_RETURN_DAYS(QTY_AUTHORIZED IN NUMBER
313 ,RETURN_DAYS IN NUMBER) RETURN NUMBER IS
314 ACTUAL_DAYS NUMBER;
315 BEGIN
316 IF QTY_AUTHORIZED = 0 THEN
317 ACTUAL_DAYS := 0;
318 ELSE
319 ACTUAL_DAYS := RETURN_DAYS;
320 END IF;
321 RETURN (ACTUAL_DAYS);
322 END C_ACTUAL_RETURN_DAYS;
323
324 FUNCTION AFTERPFORM RETURN BOOLEAN IS
325 BEGIN
326 /*SRW.MESSAGE(99999
327 ,'$Header: OEXOEORSB.pls 120.3 2008/05/05 12:41:59 dwkrishn noship $')*/NULL;
328 BEGIN
329 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
330 LP_CUSTOMER_NAME := 'and ( PARTY.PARTY_NAME between :p_customer_name_low and :p_customer_name_high ) ';
331 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
332 LP_CUSTOMER_NAME := 'and PARTY.PARTY_NAME >= :p_customer_name_low ';
333 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
334 LP_CUSTOMER_NAME := 'and PARTY.PARTY_NAME <= :p_customer_name_high ';
335 END IF;
336 IF (P_CUSTOMER_NUMBER_LOW IS NOT NULL) AND (P_CUSTOMER_NUMBER_HIGH IS NOT NULL) THEN
337 LP_CUSTOMER_NUMBER := 'and ( CUST_ACCT.ACCOUNT_NUMBER between :p_customer_number_low and :p_customer_number_high ) ';
338 ELSIF (P_CUSTOMER_NUMBER_LOW IS NOT NULL) THEN
339 LP_CUSTOMER_NUMBER := 'and rac.customer_number >= :p_customer_number_low ';
340 LP_CUSTOMER_NUMBER := 'and CUST_ACCT.ACCOUNT_NUMBER >= :p_customer_number_low ';
341 ELSIF (P_CUSTOMER_NUMBER_HIGH IS NOT NULL) THEN
342 LP_CUSTOMER_NUMBER := 'and rac.customer_number <= :p_customer_number_high ';
343 LP_CUSTOMER_NUMBER := 'and CUST_ACCT.ACCOUNT_NUMBER <= :p_customer_number_high ';
344 END IF;
345 IF (P_WAREHOUSE IS NOT NULL) THEN
346 LP_WAREHOUSE := 'and wh.name = :p_warehouse ';
347 END IF;
348 IF (P_RETURN_TYPE IS NOT NULL) THEN
349 LP_RETURN_TYPE := 'and otYPE.transaction_type_id = :p_return_type ';
350 SELECT
351 OEOT.NAME
352 INTO L_ORDER_TYPE
353 FROM
354 OE_TRANSACTION_TYPES_TL OEOT
355 WHERE OEOT.TRANSACTION_TYPE_ID = P_RETURN_TYPE
356 AND OEOT.LANGUAGE = USERENV('LANG');
357 END IF;
358 IF (P_RETURN_LINE_TYPE IS NOT NULL) THEN
359 LP_RETURN_LINE_TYPE := 'and ltYPE.transaction_type_id = :p_return_line_type ';
360 SELECT
361 OEOT.NAME
362 INTO L_LINE_TYPE
363 FROM
364 OE_TRANSACTION_TYPES_TL OEOT
365 WHERE OEOT.TRANSACTION_TYPE_ID = P_RETURN_LINE_TYPE
366 AND OEOT.LANGUAGE = USERENV('LANG');
367 END IF;
368 IF (P_LINE_CATEGORY = 'CREDIT') OR (P_LINE_CATEGORY IS NULL) THEN
369 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
370 END IF;
371 IF (P_RETURN_NUM_LOW IS NOT NULL) AND (P_RETURN_NUM_HIGH IS NOT NULL) THEN
372 LP_RETURN_NUM := 'and ( h.order_number between to_number(:p_return_num_low) and to_number(:p_return_num_high)) ';
373 ELSIF (P_RETURN_NUM_LOW IS NOT NULL) THEN
374 LP_RETURN_NUM := 'and h.order_number >= to_number(:p_return_num_low) ';
375 ELSIF (P_RETURN_NUM_HIGH IS NOT NULL) THEN
376 LP_RETURN_NUM := 'and h.order_number <= to_number(:p_return_num_high) ';
377 END IF;
378 IF (P_EXP_REC_DATE_LOW IS NOT NULL) AND (P_EXP_REC_DATE_HIGH IS NOT NULL) THEN
379 LP_EXP_REC_DATE := 'and (l.request_date between :p_exp_rec_date_low and :p_exp_rec_date_high) ';
380 ELSIF (P_EXP_REC_DATE_LOW IS NOT NULL) THEN
381 LP_EXP_REC_DATE := 'and l.request_date >= :p_exp_rec_date_low ';
382 ELSIF (P_EXP_REC_DATE_HIGH IS NOT NULL) THEN
383 LP_EXP_REC_DATE := 'and l.request_date <= :p_exp_rec_date_high ';
384 END IF;
385 IF (P_RETURN_DATE_LOW IS NOT NULL) AND (P_RETURN_DATE_HIGH IS NOT NULL) THEN
386 LP_RETURN_DATE := 'and (h.ordered_date between :p_return_date_low and :p_return_date_high) ';
387 ELSIF (P_RETURN_DATE_LOW IS NOT NULL) THEN
388 LP_RETURN_DATE := 'and h.ordered_date >= :p_return_date_low ';
389 ELSIF (P_RETURN_DATE_HIGH IS NOT NULL) THEN
390 LP_RETURN_DATE := 'and h.ordered_date <= :p_return_date_high ';
391 END IF;
392 IF (P_RETURN_DAYS_LOW IS NOT NULL) AND (P_RETURN_DAYS_HIGH IS NOT NULL) THEN
393 LP_RETURN_DAYS := 'and decode(l.line_category_code, ''ORDER'', 0, trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id)) - trunc(h.ordered_date)) between :p_return_days_low and :p_return_days_high ';
394 ELSIF (P_RETURN_DAYS_LOW IS NOT NULL) THEN
395 LP_RETURN_DAYS := 'and decode(l.line_category_code, ''ORDER'', 0, trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id)) - trunc(h.ordered_date)) >= :p_return_days_low ';
396 ELSIF (P_RETURN_DAYS_HIGH IS NOT NULL) THEN
397 LP_RETURN_DAYS := 'and decode(l.line_category_code, ''ORDER'', 0, trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id)) - trunc(h.ordered_date)) <= :p_return_days_high ';
398 END IF;
399 IF (P_REC_DAYS_LOW IS NOT NULL) AND (P_REC_DAYS_HIGH IS NOT NULL) THEN
400 LP_REC_DAYS := 'and decode(l.line_category_code, ''ORDER'', 0, trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id)) - trunc(l.request_date)) between :p_rec_days_low and :p_rec_days_high ';
401 ELSIF (P_REC_DAYS_LOW IS NOT NULL) THEN
402 LP_REC_DAYS := 'and decode(l.line_category_code, ''ORDER'', 0, trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id)) - trunc(l.request_date)) >= :p_rec_days_low ';
403 ELSIF (P_REC_DAYS_HIGH IS NOT NULL) THEN
404 LP_REC_DAYS := 'and decode(l.line_category_code, ''ORDER'', 0, trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id)) - trunc(l.request_date)) <= :p_rec_days_high ';
405 END IF;
406 IF P_OPEN_RETURNS_ONLY = 'Y' THEN
407 LP_OPEN_RETURNS_ONLY := 'and nvl(h.open_flag,''N'') = ''Y'' ';
408 END IF;
409 END;
410 RETURN (TRUE);
411 END AFTERPFORM;
412
413 FUNCTION C_DATA_NOT_FOUNDFORMULA(CURRENCY2 IN VARCHAR2) RETURN NUMBER IS
414 BEGIN
415 RP_DATA_FOUND := CURRENCY2;
416 RETURN (0);
417 END C_DATA_NOT_FOUNDFORMULA;
418
419 FUNCTION C_ORDER_COUNTFORMULA RETURN NUMBER IS
420 BEGIN
421 RETURN (1);
422 END C_ORDER_COUNTFORMULA;
423
424 FUNCTION RP_ORDER_BYFORMULA RETURN VARCHAR2 IS
425 BEGIN
426 DECLARE
427 L_SORT_BY VARCHAR2(100);
428 BEGIN
429 SELECT
430 MEANING
431 INTO L_SORT_BY
432 FROM
433 OE_LOOKUPS
434 WHERE LOOKUP_CODE = P_ORDER_BY
435 AND LOOKUP_TYPE = 'OEXOEORS SORT BY';
436 RETURN (L_SORT_BY);
437 EXCEPTION
438 WHEN NO_DATA_FOUND THEN
439 RETURN ('Customer');
440 END;
441 RETURN NULL;
442 END RP_ORDER_BYFORMULA;
443
444 FUNCTION C_MASTER_ORGFORMULA RETURN NUMBER IS
445 V_MASTER_ORG VARCHAR2(20);
446 BEGIN
447 V_MASTER_ORG := NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
448 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
449 ,0);
450 RETURN V_MASTER_ORG;
451 END C_MASTER_ORGFORMULA;
452
453 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
454 BEGIN
455 RETURN RP_DUMMY_ITEM;
456 END RP_DUMMY_ITEM_P;
457
458 FUNCTION C_AUTHORIZED_AMOUNT_P(currency2 varchar2,authorized_amount number,conversion_type_code varchar2,return_date date,conversion_rate number) RETURN NUMBER IS
459 BEGIN
460 Declare
461 l_conversion_rate number (15,3);
462 BEGIN
463 l_conversion_rate := 0 ;
464
465 if P_USE_FUNCTIONAL_CURRENCY = 'N' then
466 c_authorized_amount := nvl (authorized_amount,0);
467 /* srw.user_exit (
468 'FND FORMAT_CURRENCY
469 CODE=":CURRENCY2"
470 DISPLAY_WIDTH="11"
471 AMOUNT=":c_authorized_amount"
472 DISPLAY=":c_authorized_amount_dsp"
473 ');*/
474 return (c_authorized_amount);
475 end if ;
476
477
478 IF p_use_functional_currency = 'Y' THEN
479 IF currency2 = rp_functional_currency then
480 l_conversion_rate := 1 ;
481 else
482 IF conversion_rate is null then
483 l_conversion_rate := gl_currency_api.get_rate (
484 p_sob_id,
485 currency2,
486 return_date,
487 conversion_type_code );
488 ELSE
489 l_conversion_rate :=conversion_rate ;
490 END IF;
491 END IF;
492
493 c_authorized_amount := nvl (l_conversion_rate,0) * nvl ( authorized_amount,0);
494 /* srw.user_exit (
495 'FND FORMAT_CURRENCY
496 CODE=":RP_FUNCTIONAL_CURRENCY"
497 DISPLAY_WIDTH="11"
498 AMOUNT=":c_authorized_amount"
499 DISPLAY=":c_authorized_amount_dsp"
500 ');*/
501 return (c_authorized_amount);
502
503 END IF ;
504
505 /* EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 :c_authorized_amount := 0 ;
508
509 return ('NO RATE');*/
510 end;
511
512
513
514 END C_AUTHORIZED_AMOUNT_P;
515
516 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
517 BEGIN
518 RETURN RP_REPORT_NAME;
519 END RP_REPORT_NAME_P;
520
521 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
522 BEGIN
523 RETURN RP_SUB_TITLE;
524 END RP_SUB_TITLE_P;
525
526 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
527 BEGIN
528 RETURN RP_COMPANY_NAME;
529 END RP_COMPANY_NAME_P;
530
531 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
532 BEGIN
533 RETURN RP_FUNCTIONAL_CURRENCY;
534 END RP_FUNCTIONAL_CURRENCY_P;
535
536 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
537 BEGIN
538 RETURN RP_DATA_FOUND;
539 END RP_DATA_FOUND_P;
540
541 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
542 BEGIN
543 RETURN RP_ITEM_FLEX_ALL_SEG;
544 END RP_ITEM_FLEX_ALL_SEG_P;
545
546 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
547 BEGIN
548 RETURN RP_PRINT_DESCRIPTION;
549 END RP_PRINT_DESCRIPTION_P;
550
551 FUNCTION RP_RETURN_NUMBER_RANGE_P RETURN VARCHAR2 IS
552 BEGIN
553 RETURN RP_RETURN_NUMBER_RANGE;
554 END RP_RETURN_NUMBER_RANGE_P;
555
556 FUNCTION RP_EXP_REC_DATE_RANGE_P RETURN VARCHAR2 IS
557 BEGIN
558 RETURN RP_EXP_REC_DATE_RANGE;
559 END RP_EXP_REC_DATE_RANGE_P;
560
561 FUNCTION RP_RETURN_DATE_RANGE_P RETURN VARCHAR2 IS
562 BEGIN
563 RETURN RP_RETURN_DATE_RANGE;
564 END RP_RETURN_DATE_RANGE_P;
565
566 FUNCTION RP_OPEN_RETURNS_ONLY_P RETURN VARCHAR2 IS
567 BEGIN
568 RETURN RP_OPEN_RETURNS_ONLY;
569 END RP_OPEN_RETURNS_ONLY_P;
570
571 FUNCTION RP_USE_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
572 BEGIN
573 RETURN RP_USE_FUNCTIONAL_CURRENCY;
574 END RP_USE_FUNCTIONAL_CURRENCY_P;
575
576 FUNCTION RP_CUST_NAME_RANGE_P RETURN VARCHAR2 IS
577 BEGIN
578 RETURN RP_CUST_NAME_RANGE;
579 END RP_CUST_NAME_RANGE_P;
580
581 FUNCTION RP_CUST_NO_RANGE_P RETURN VARCHAR2 IS
582 BEGIN
583 RETURN RP_CUST_NO_RANGE;
584 END RP_CUST_NO_RANGE_P;
585
586 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
587 ,X_TO_CURRENCY IN VARCHAR2
588 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
589 X0 VARCHAR2(2000);
590 BEGIN
591 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
592 STPROC.BIND_O(X0);
593 STPROC.BIND_I(X_FROM_CURRENCY);
594 STPROC.BIND_I(X_TO_CURRENCY);
595 STPROC.BIND_I(X_EFFECTIVE_DATE);
596 STPROC.EXECUTE;
597 STPROC.RETRIEVE(1
598 ,X0);*/null;
599 RETURN X0;
600 END IS_FIXED_RATE;
601
602 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
603 ,X_TO_CURRENCY IN VARCHAR2
604 ,X_EFFECTIVE_DATE IN DATE
605 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
606 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
607 BEGIN
608 /* STPROC.INIT('declare X_FIXED_RATE BOOLEAN; begin X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE); GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY,
609 :X_EFFECTIVE_DATE, X_FIXED_RATE, :X_RELATIONSHIP); :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
610 STPROC.BIND_IO(X_FIXED_RATE);
611 STPROC.BIND_I(X_FROM_CURRENCY);
612 STPROC.BIND_I(X_TO_CURRENCY);
613 STPROC.BIND_I(X_EFFECTIVE_DATE);
614 STPROC.BIND_IO(X_RELATIONSHIP);
615 STPROC.EXECUTE;
616 STPROC.RETRIEVE(1
617 ,X_FIXED_RATE);
618 STPROC.RETRIEVE(5
619 ,X_RELATIONSHIP);*/null;
620 END GET_RELATION;
621
622 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
623 X0 VARCHAR2(2000);
624 BEGIN
625 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
626 STPROC.BIND_O(X0);
627 STPROC.EXECUTE;
628 STPROC.RETRIEVE(1
629 ,X0);*/
630 RETURN X0;
631 END GET_EURO_CODE;
632
633 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
634 ,X_TO_CURRENCY IN VARCHAR2
635 ,X_CONVERSION_DATE IN DATE
636 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
637 X0 NUMBER;
638 BEGIN
639 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
640 STPROC.BIND_O(X0);
641 STPROC.BIND_I(X_FROM_CURRENCY);
642 STPROC.BIND_I(X_TO_CURRENCY);
643 STPROC.BIND_I(X_CONVERSION_DATE);
644 STPROC.BIND_I(X_CONVERSION_TYPE);
645 STPROC.EXECUTE;
646 STPROC.RETRIEVE(1
647 ,X0);*/
648 RETURN X0;
649 END GET_RATE;
650
651 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
652 ,X_FROM_CURRENCY IN VARCHAR2
653 ,X_CONVERSION_DATE IN DATE
654 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
655 X0 NUMBER;
656 BEGIN
657 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
658 STPROC.BIND_O(X0);
659 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
660 STPROC.BIND_I(X_FROM_CURRENCY);
661 STPROC.BIND_I(X_CONVERSION_DATE);
662 STPROC.BIND_I(X_CONVERSION_TYPE);
663 STPROC.EXECUTE;
664 STPROC.RETRIEVE(1
665 ,X0);*/
666 RETURN X0;
667 END GET_RATE;
668
669 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
670 ,X_TO_CURRENCY IN VARCHAR2
671 ,X_CONVERSION_DATE IN DATE
672 ,X_CONVERSION_TYPE IN VARCHAR2
673 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
674 X0 NUMBER;
675 BEGIN
676 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
677 STPROC.BIND_O(X0);
678 STPROC.BIND_I(X_FROM_CURRENCY);
679 STPROC.BIND_I(X_TO_CURRENCY);
680 STPROC.BIND_I(X_CONVERSION_DATE);
681 STPROC.BIND_I(X_CONVERSION_TYPE);
682 STPROC.BIND_I(X_AMOUNT);
683 STPROC.EXECUTE;
684 STPROC.RETRIEVE(1
685 ,X0);*/
686 RETURN X0;
687 END CONVERT_AMOUNT;
688
689 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
690 ,X_FROM_CURRENCY IN VARCHAR2
691 ,X_CONVERSION_DATE IN DATE
692 ,X_CONVERSION_TYPE IN VARCHAR2
693 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
694 X0 NUMBER;
695 BEGIN
696 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
697 STPROC.BIND_O(X0);
698 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
699 STPROC.BIND_I(X_FROM_CURRENCY);
700 STPROC.BIND_I(X_CONVERSION_DATE);
701 STPROC.BIND_I(X_CONVERSION_TYPE);
702 STPROC.BIND_I(X_AMOUNT);
703 STPROC.EXECUTE;
704 STPROC.RETRIEVE(1
705 ,X0);*/
706 RETURN X0;
707 END CONVERT_AMOUNT;
708
709 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
710 ,PERIOD IN VARCHAR2
711 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
712 X0 VARCHAR2(2000);
713 BEGIN
714 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
715 STPROC.BIND_O(X0);
716 STPROC.BIND_I(SOB_ID);
717 STPROC.BIND_I(PERIOD);
718 STPROC.BIND_I(CURR_CODE);
719 STPROC.EXECUTE;
720 STPROC.RETRIEVE(1
721 ,X0);*/
722 RETURN X0;
723 END GET_DERIVE_TYPE;
724
725 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
726 v_item varchar2(2000);
727 v_description varchar2(500);
728 begin
729 if (item_identifier_type is null or item_identifier_type = 'INT')
730 or (p_print_description in ('I','D','F')) then
731 select
732 -- sitems.concatenated_segments item,
733 sitems.description description
734 into
735 -- v_item,
736 v_description
737 from mtl_system_items_vl sitems
738 where
739 sitems.customer_order_enabled_flag = 'Y' and
740 sitems.bom_item_type in (1,4)
741 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
742 and sitems.inventory_item_id = inventory_item_id1;
743 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');
744 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
745 select citems.customer_item_number item,
746 nvl(citems.customer_item_desc,sitems.description) description
747 into v_item,v_description
748 from mtl_customer_items citems,
749 mtl_customer_item_xrefs cxref,
750 mtl_system_items_vl sitems
751 where citems.customer_item_id = cxref.customer_item_id
752 and cxref.inventory_item_id = sitems.inventory_item_id
753 and citems.customer_item_id = ordered_item_id
754 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
755 and sitems.inventory_item_id = inventory_item_id1;
756 -- and sitems.customer_order_enabled_flag = 'Y'
757 -- and sitems.bom_item_type in (1,4)
758 elsif (p_print_description in ('C','P','O')) then
759 Begin
760 select items.cross_reference item,
761 nvl(items.description,sitems.description) description
762 into v_item,v_description
763 from mtl_cross_reference_types xtypes,
764 mtl_cross_references items,
765 mtl_system_items_vl sitems
766 where xtypes.cross_reference_type = items.cross_reference_type
767 and items.inventory_item_id = sitems.inventory_item_id
768 and items.cross_reference = ordered_item
769 and items.cross_reference_type = item_identifier_type
770 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
771 and sitems.inventory_item_id = inventory_item_id1
772 -- Bug 3433353 Begin
773 and items.org_independent_flag ='N'
774 and items.organization_id = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0);
775 -- and sitems.customer_order_enabled_flag = 'Y'
776 -- and sitems.bom_item_type in (1,4)
777 Exception When NO_DATA_FOUND Then
778 select items.cross_reference item,
779 nvl(items.description,sitems.description) description
780 into v_item,v_description
781 from mtl_cross_reference_types xtypes,
782 mtl_cross_references items,
783 mtl_system_items_vl sitems
784 where xtypes.cross_reference_type =
785 items.cross_reference_type
786 and items.inventory_item_id = sitems.inventory_item_id
787 and items.cross_reference = ordered_item
788 and items.cross_reference_type = item_identifier_type
789 and nvl(sitems.organization_id,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',MO_GLOBAL.GET_CURRENT_ORG_ID),0)
790 and sitems.inventory_item_id = inventory_item_id1
791 and items.org_independent_flag = 'Y';
792 End;
793 --Bug 3433353 End
794 end if;
795
796 if (p_print_description in ('I','C')) then
797 return(v_item||' - '||v_description);
798 elsif (p_print_description in ('D','P')) then
799 return(v_description);
800 else
801 return(v_item);
802 end if;
803
804
805 RETURN NULL;
806 Exception
807 When Others Then
808 return('Item Not Found');
809 end;
810
811 END ONT_OEXOEORS_XMLP_PKG;
[Home] [Help]