1 PACKAGE BODY WSH_WSHRDBDR_XMLP_PKG AS
2 /* $Header: WSHRDBDRB.pls 120.1.12020000.2 2012/07/04 10:19:18 suppal ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 EXCEPTION
9 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
10 /*SRW.MESSAGE(1
11 ,'Failed FND SRWINIT.')*/NULL;
12 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
13 END;
14 BEGIN
15 DECLARE
16 L_REPORT_NAME VARCHAR2(240);
17 BEGIN
18 SELECT
19 CP.USER_CONCURRENT_PROGRAM_NAME
20 INTO L_REPORT_NAME
21 FROM
22 FND_CONCURRENT_PROGRAMS_VL CP,
23 FND_CONCURRENT_REQUESTS CR
24 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
25 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
26 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
27 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 RP_REPORT_NAME := 'Backorder Detail Report';
31 END;
32 END;
33 RETURN (TRUE);
34 END BEFOREREPORT;
35
36 FUNCTION AFTERREPORT RETURN BOOLEAN IS
37 BEGIN
38 BEGIN
39 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
40 EXCEPTION
41 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
42 /*SRW.MESSAGE(1
43 ,'Failed in SRWEXIT')*/NULL;
44 RAISE;
45 END;
46 RETURN (TRUE);
47 END AFTERREPORT;
48
49 FUNCTION P_ORGANIZATION_IDVALIDTRIGGER RETURN BOOLEAN IS
50 BEGIN
51 RETURN (TRUE);
52 END P_ORGANIZATION_IDVALIDTRIGGER;
53
54 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
55 BEGIN
56 RETURN (TRUE);
57 END P_ITEM_FLEX_CODEVALIDTRIGGER;
58
59 FUNCTION AFTERPFORM RETURN BOOLEAN IS
60 BEGIN
61 DECLARE
62 CURSOR STRUCT_NUM(FLEX_CODE IN VARCHAR2) IS
63 SELECT
64 ID_FLEX_NUM
65 FROM
66 FND_ID_FLEX_STRUCTURES
67 WHERE ID_FLEX_CODE = FLEX_CODE;
68 STRUCT_NUMBER NUMBER;
69 BEGIN
70 BEGIN
71 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
72 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
73 EXCEPTION
74 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
75 /*SRW.MESSAGE(1000
76 ,'Failed in After Form trigger')*/NULL;
77 RETURN (FALSE);
78 END;
79 --Bug 14133213 start
80 --If sort by is specified then populating the
81 --condition dynamically
82 IF P_SORT_BY IS NOT NULL
83 THEN
84 IF p_source_system ='OE' AND p_sort_by ='ORDER'
85 THEN
86 LP_ORDER_BY := ' TO_NUMBER(wdd.source_header_number) ';
87 ELSE
88 IF p_sort_by ='ORDER'
89 THEN
90 LP_ORDER_BY := ' UPPER(wdd.source_header_number) ';
91 ELSIF p_sort_by ='CUSTOMER'
92 THEN
93 LP_ORDER_BY := ' UPPER(substrb(party.party_name,1,50)) ';
94 ELSIF p_sort_by ='WAREHOUSE'
95 THEN
96 LP_ORDER_BY := ' UPPER(ood.organization_name) ';
97 END IF;
98 END IF;
99 END IF;
100 --Bug 14133213 end
101 IF P_ORDER_NUM_HIGH IS NOT NULL AND P_ORDER_NUM_LOW IS NOT NULL THEN
102 LP_HEADER_NUMBER := ' AND to_number(wdd.source_header_number) between :p_order_num_low and :p_order_num_high';
103 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
104 LP_HEADER_NUMBER := ' and to_number(wdd.source_header_number) >= :p_order_num_low';
105 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
106 LP_HEADER_NUMBER := ' and to_number(wdd.source_header_number) <= :p_order_num_high';
107 ELSE
108 LP_HEADER_NUMBER := ' ';
109 END IF;
110 IF P_ITEM_CATE_SET_ID IS NOT NULL THEN
111 LP_ITEM_FROM := ' mtl_item_categories mic, ';
112 LP_ITEM_WHERE := ' and wdd.inventory_item_id = mic.inventory_item_id
113 and mic.organization_id = wdd.organization_id
114 and mic.category_set_id = :p_item_cate_set_id ';
115 IF P_ITEM_CATEGORY_ID IS NOT NULL THEN
116 LP_ITEM_WHERE := LP_ITEM_WHERE || ' and mic.category_id = :p_item_category_id ';
117 END IF;
118 ELSE
119 LP_ITEM_FROM := ' ';
120 LP_ITEM_WHERE := ' ';
121 END IF;
122 IF P_ITEM_ID IS NOT NULL THEN
123 LP_ITEM_ID := ' and wdd.inventory_item_id = :p_item_id ';
124 ELSE
125 LP_ITEM_ID := ' ';
126 END IF;
127 IF P_TRANSACTION_TYPE_ID IS NOT NULL THEN
128 SELECT
129 NAME
130 INTO RP_ORDER_TYPE_NAME
131 FROM
132 OE_TRXT_TYPES_NOORGS_VL
133 WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_TYPE_ID;
134 LP_ORDER_TYPE := ' and wdd.source_header_type_id = :p_transaction_type_id ';
135 ELSE
136 LP_ORDER_TYPE := ' ';
137 END IF;
138 OPEN STRUCT_NUM(P_ITEM_FLEX_CODE);
139 FETCH STRUCT_NUM
140 INTO STRUCT_NUMBER;
141 CLOSE STRUCT_NUM;
142 LP_STRUCTURE_NUM := STRUCT_NUMBER;
143 IF P_SORT_BY IS NOT NULL THEN
144 BEGIN
145 SELECT meaning
146 INTO rp_sort_by
147 FROM fnd_lookup_values_vl
148 --Bug 14133213 modified the looktype to get the value of sortby
149 WHERE lookup_type = 'WSH_WSHRDBDR_SORT_BY'
150 AND enabled_flag = 'Y'
151 AND lookup_code = P_SORT_BY;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 NULL;
155 END;
156 END IF;
157 IF P_CUSTOMER_ID IS NOT NULL THEN
158 BEGIN
159 SELECT
160 SUBSTRB(PARTY.PARTY_NAME
161 ,1
162 ,50)
163 INTO RP_CUSTOMER
164 FROM
165 HZ_PARTIES PARTY,
166 HZ_CUST_ACCOUNTS CUST_ACCT
167 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
168 AND CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER_ID;
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 NULL;
172 END;
173 LP_CUSTOMER := ' and wdd.customer_id = :p_customer_id';
174 ELSE
175 LP_CUSTOMER := ' ';
176 END IF;
177 IF P_CREATED_BY_ID IS NOT NULL THEN
178 BEGIN
179 SELECT
180 USER_NAME
181 INTO RP_USER
182 FROM
183 FND_USER
184 WHERE USER_ID = P_CREATED_BY_ID;
185 LP_CREATED_BY := ' and wdd.created_by = :p_created_by_id ';
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 RP_USER := P_CREATED_BY_ID;
189 END;
190 ELSE
191 LP_CREATED_BY := ' ';
192 END IF;
193 IF P_SHIP_TO_COUNTRY_CODE IS NOT NULL THEN
194 BEGIN
195 SELECT
196 TERRITORY_SHORT_NAME
197 INTO RP_TERRITORY_CODE
198 FROM
199 FND_TERRITORIES_VL
200 WHERE TERRITORY_CODE = P_SHIP_TO_COUNTRY_CODE;
201 LP_COUNTRY_FROM := ' wsh_locations wl, ';
202 LP_COUNTRY_WHERE := ' and wdd.ship_to_location_id = wl.wsh_location_id
203 and wl.country = :p_ship_to_country_code ';
204
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 RP_TERRITORY_CODE := P_SHIP_TO_COUNTRY_CODE;
208 END;
209 ELSE
210 LP_COUNTRY_FROM := ' ';
211 LP_COUNTRY_WHERE := ' ';
212 END IF;
213 IF P_ORGANIZATION_ID IS NOT NULL THEN
214 LP_WAREHOUSE := ' and wdd.organization_id = :p_organization_id';
215 ELSE
216 LP_WAREHOUSE := ' ';
217 END IF;
218 END;
219 RETURN (TRUE);
220 END AFTERPFORM;
221
222 FUNCTION C_SET_LBLFORMULA RETURN VARCHAR2 IS
223 SCODE VARCHAR2(80);
224 BEGIN
225 IF P_SOURCE_SYSTEM = 'OE' THEN
226 RP_ORDER_NUM_LOW := P_ORDER_NUM_LOW;
227 RP_ORDER_NUM_HIGH := P_ORDER_NUM_HIGH;
228 END IF;
229 DECLARE
230 ITEM_DISPLAY_MEANING VARCHAR2(80);
231 BEGIN
232 SELECT
233 MEANING
234 INTO ITEM_DISPLAY_MEANING
235 FROM
236 WSH_LOOKUPS
237 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY'
238 AND LOOKUP_CODE = P_ITEM_DISPLAY;
239 RP_FLEX_OR_DESC := ITEM_DISPLAY_MEANING;
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 RP_FLEX_OR_DESC := NULL;
243 END;
244 IF P_ITEM_CATE_SET_ID IS NOT NULL THEN
245 DECLARE
246 CATEGORY_SET VARCHAR2(40);
247 BEGIN
248 SELECT
249 SUBSTR(DESCRIPTION
250 ,1
251 ,40)
252 INTO CATEGORY_SET
253 FROM
254 MTL_CATEGORY_SETS
255 WHERE CATEGORY_SET_ID = P_ITEM_CATE_SET_ID;
256 RP_CATEGORY_SET := CATEGORY_SET;
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 RP_CATEGORY_SET := 'Not Set up';
260 END;
261 END IF;
262 IF P_ITEM_CATEGORY_ID IS NOT NULL THEN
263 DECLARE
264 CATEGORY VARCHAR2(40);
265 BEGIN
266 SELECT
267 SUBSTR(DESCRIPTION
268 ,1
269 ,40)
270 INTO CATEGORY
271 FROM
272 MTL_CATEGORIES
273 WHERE CATEGORY_ID = P_ITEM_CATEGORY_ID;
274 RP_CATEGORY := CATEGORY;
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 RP_CATEGORY := 'Not Set up';
278 END;
279 END IF;
280 IF P_ORGANIZATION_ID IS NOT NULL THEN
281 DECLARE
282 WAREHOUSE_NAME HR_ORGANIZATION_UNITS.NAME%TYPE;
283 BEGIN
284 SELECT
285 NAME
286 INTO WAREHOUSE_NAME
287 FROM
288 HR_ORGANIZATION_UNITS
289 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
290 RP_WAREHOUSE := WAREHOUSE_NAME;
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 RP_WAREHOUSE := NULL;
294 END;
295 END IF;
296 IF P_ITEM_ID IS NOT NULL THEN
297 RP_ITEM := WSH_UTIL_CORE.GET_ITEM_NAME(P_ITEM_ID
298 ,P_ORGANIZATION_ID
299 ,P_ITEM_FLEX_CODE
300 ,LP_STRUCTURE_NUM);
301 END IF;
302 BEGIN
303 SELECT
304 MEANING
305 INTO SCODE
306 FROM
307 WSH_LOOKUPS
308 WHERE LOOKUP_CODE = P_SOURCE_SYSTEM
309 AND LOOKUP_TYPE = 'SOURCE_SYSTEM';
310 EXCEPTION
311 WHEN NO_DATA_FOUND THEN
312 NULL;
313 END;
314 RP_SOURCE_SYSTEM := SCODE;
315 RETURN (1);
316 RETURN NULL;
317 END C_SET_LBLFORMULA;
318
319 FUNCTION CF_ITEM_DISPLAYFORMULA(INVENTORY_ITEM_ID IN NUMBER
320 ,ORGANIZATION_ID IN NUMBER
321 ,ITEM_DESCRIPTION IN VARCHAR2) RETURN VARCHAR2 IS
322 BEGIN
323 DECLARE
324 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
325 SELECT
326 DESCRIPTION
327 FROM
328 MTL_SYSTEM_ITEMS_VL
329 WHERE INVENTORY_ITEM_ID = ID
330 AND ORGANIZATION_ID = ORG_ID;
331 NAME VARCHAR2(800);
332 BEGIN
333 IF P_ITEM_DISPLAY = 'D' THEN
334 IF INVENTORY_ITEM_ID IS NOT NULL THEN
335 OPEN INVENTORY_LABEL(INVENTORY_ITEM_ID,ORGANIZATION_ID);
336 FETCH INVENTORY_LABEL
337 INTO NAME;
338 CLOSE INVENTORY_LABEL;
339 ELSE
340 NAME := ITEM_DESCRIPTION;
341 END IF;
342 ELSIF P_ITEM_DISPLAY = 'F' THEN
343 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(INVENTORY_ITEM_ID
344 ,ORGANIZATION_ID
345 ,P_ITEM_FLEX_CODE
346 ,LP_STRUCTURE_NUM);
347 ELSE
348 IF INVENTORY_ITEM_ID IS NOT NULL THEN
349 OPEN INVENTORY_LABEL(INVENTORY_ITEM_ID,ORGANIZATION_ID);
350 FETCH INVENTORY_LABEL
351 INTO NAME;
352 CLOSE INVENTORY_LABEL;
353 ELSE
354 NAME := ITEM_DESCRIPTION;
355 END IF;
356 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(INVENTORY_ITEM_ID
357 ,ORGANIZATION_ID
358 ,P_ITEM_FLEX_CODE
359 ,LP_STRUCTURE_NUM) || ' ' || NAME;
360 END IF;
361 RETURN NAME;
362 END;
363 RETURN NULL;
364 END CF_ITEM_DISPLAYFORMULA;
365
366 FUNCTION CF_LINE_ITEM_DISPLAYFORMULA(LINE_ITEM IN NUMBER
367 ,ORGANIZATION_ID IN NUMBER
368 ,ITEM_DESCRIPTION IN VARCHAR2) RETURN VARCHAR2 IS
369 BEGIN
370 DECLARE
371 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
372 SELECT
373 DESCRIPTION
374 FROM
375 MTL_SYSTEM_ITEMS_VL
376 WHERE INVENTORY_ITEM_ID = ID
377 AND ORGANIZATION_ID = ORG_ID;
378 NAME VARCHAR2(800);
379 BEGIN
380 IF P_ITEM_DISPLAY = 'D' THEN
381 IF LINE_ITEM IS NOT NULL THEN
382 OPEN INVENTORY_LABEL(LINE_ITEM,ORGANIZATION_ID);
383 FETCH INVENTORY_LABEL
384 INTO NAME;
385 CLOSE INVENTORY_LABEL;
386 ELSE
387 NAME := ITEM_DESCRIPTION;
388 END IF;
389 ELSIF P_ITEM_DISPLAY = 'F' THEN
390 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(LINE_ITEM
391 ,ORGANIZATION_ID
392 ,P_ITEM_FLEX_CODE
393 ,LP_STRUCTURE_NUM);
394 ELSE
395 IF LINE_ITEM IS NOT NULL THEN
396 OPEN INVENTORY_LABEL(LINE_ITEM,ORGANIZATION_ID);
397 FETCH INVENTORY_LABEL
398 INTO NAME;
399 CLOSE INVENTORY_LABEL;
400 ELSE
401 NAME := ITEM_DESCRIPTION;
402 END IF;
403 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(LINE_ITEM
404 ,ORGANIZATION_ID
405 ,P_ITEM_FLEX_CODE
406 ,LP_STRUCTURE_NUM) || ' ' || NAME;
407 END IF;
408 RETURN NAME;
409 END;
410 RETURN NULL;
411 END CF_LINE_ITEM_DISPLAYFORMULA;
412
413 FUNCTION CF_LINE_SHIPPED_QTYFORMULA(SOURCE_LINE_SET_ID IN NUMBER
414 ,SOURCE_HEADER_ID IN NUMBER
415 ,REQUESTED_QUANTITY_UOM IN VARCHAR2
416 ,ORDER_UOM IN VARCHAR2
417 ,LINE_ITEM IN NUMBER) RETURN NUMBER IS
418 BEGIN
419 DECLARE
420 L_SHIPPED_QTY NUMBER;
421 SHIPPED_QTY_ORD_UOM NUMBER;
422 BEGIN
423 IF SOURCE_LINE_SET_ID IS NOT NULL THEN
424 BEGIN
425 SELECT
426 SUM(NVL(SHIPPED_QUANTITY
427 ,0))
428 INTO L_SHIPPED_QTY
429 FROM
430 WSH_DELIVERY_DETAILS
431 WHERE SOURCE_HEADER_ID = CF_LINE_SHIPPED_QTYFORMULA.SOURCE_HEADER_ID
432 AND SOURCE_LINE_SET_ID = CF_LINE_SHIPPED_QTYFORMULA.SOURCE_LINE_SET_ID
433 AND RELEASED_STATUS = 'C'
434 AND SOURCE_CODE = P_SOURCE_SYSTEM
435 GROUP BY
436 SOURCE_LINE_SET_ID;
437 EXCEPTION
438 WHEN NO_DATA_FOUND THEN
439 SELECT
440 SUM(NVL(SHIPPED_QUANTITY
441 ,0))
442 INTO L_SHIPPED_QTY
443 FROM
444 WSH_DELIVERY_DETAILS WDD
445 WHERE SOURCE_LINE_ID = CF_LINE_SHIPPED_QTYFORMULA.SOURCE_LINE_SET_ID
446 AND RELEASED_STATUS = 'C'
447 AND SOURCE_CODE = P_SOURCE_SYSTEM
448 GROUP BY
449 SOURCE_LINE_ID;
450 END;
451 END IF;
452 SHIPPED_QTY_ORD_UOM := WSH_WV_UTILS.CONVERT_UOM(REQUESTED_QUANTITY_UOM
453 ,ORDER_UOM
454 ,L_SHIPPED_QTY
455 ,LINE_ITEM);
456 RETURN (NVL(SHIPPED_QTY_ORD_UOM
457 ,0));
458 EXCEPTION
459 WHEN NO_DATA_FOUND THEN
460 RETURN 0;
461 END;
462 END CF_LINE_SHIPPED_QTYFORMULA;
463
464 FUNCTION CF_SELLING_PRICEFORMULA(SOURCE_LINE_ID IN NUMBER) RETURN NUMBER IS
465 SPRICE NUMBER;
466 BEGIN
467 IF P_SOURCE_SYSTEM = 'OE' THEN
468 SELECT
469 OL.UNIT_SELLING_PRICE
470 INTO SPRICE
471 FROM
472 OE_ORDER_LINES_ALL OL
473 WHERE OL.LINE_ID = CF_SELLING_PRICEFORMULA.SOURCE_LINE_ID;
474 END IF;
475 RETURN (SPRICE);
476 EXCEPTION
477 WHEN OTHERS THEN
478 RETURN (NULL);
479 END CF_SELLING_PRICEFORMULA;
480
481 FUNCTION CF_LINE_BO_QTYFORMULA(SOURCE_LINE_SET_ID IN NUMBER
482 ,SOURCE_HEADER_ID IN NUMBER
483 ,REQUESTED_QUANTITY_UOM IN VARCHAR2
484 ,ORDER_UOM IN VARCHAR2
485 ,LINE_ITEM IN NUMBER) RETURN NUMBER IS
486 BEGIN
487 DECLARE
488 BO_QTY NUMBER;
489 BO_QTY_ORD_UOM NUMBER;
490 BEGIN
491 IF SOURCE_LINE_SET_ID IS NOT NULL THEN
492 BEGIN
493 SELECT
494 SUM(NVL(REQUESTED_QUANTITY
495 ,0))
496 INTO BO_QTY
497 FROM
498 WSH_DELIVERY_DETAILS
499 WHERE SOURCE_HEADER_ID = CF_LINE_BO_QTYFORMULA.SOURCE_HEADER_ID
500 AND SOURCE_LINE_SET_ID = CF_LINE_BO_QTYFORMULA.SOURCE_LINE_SET_ID
501 AND RELEASED_STATUS = 'B'
502 AND replenishment_status IS NULL -- bug#6689448 (replenishment project)
503 AND SOURCE_CODE = P_SOURCE_SYSTEM
504 GROUP BY
505 SOURCE_LINE_SET_ID;
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 SELECT
509 SUM(NVL(REQUESTED_QUANTITY
510 ,0))
511 INTO BO_QTY
512 FROM
513 WSH_DELIVERY_DETAILS
514 WHERE SOURCE_LINE_ID = CF_LINE_BO_QTYFORMULA.SOURCE_LINE_SET_ID
515 AND RELEASED_STATUS = 'B'
516 AND replenishment_status IS NULL -- bug#6689448 (replenishment project)
517 AND SOURCE_CODE = P_SOURCE_SYSTEM;
518 END;
519 END IF;
520 BO_QTY_ORD_UOM := WSH_WV_UTILS.CONVERT_UOM(REQUESTED_QUANTITY_UOM
521 ,ORDER_UOM
522 ,BO_QTY
523 ,LINE_ITEM);
524 RETURN (NVL(BO_QTY_ORD_UOM
525 ,0));
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 RETURN 0;
529 END;
530 END CF_LINE_BO_QTYFORMULA;
531
532 FUNCTION CF_LINE_ORDERED_AMTFORMULA(CF_LINE_ORDERED_QTY IN NUMBER
533 ,CF_selling_price IN NUMBER) RETURN NUMBER IS
534 BEGIN
535 DECLARE
536 ORD_AMT NUMBER;
537 BEGIN
538 ORD_AMT := TRUNC((NVL(CF_LINE_ORDERED_QTY
539 ,0) * NVL(CF_selling_price
540 ,0))
541 ,4);
542 RETURN (ORD_AMT);
543 END;
544 END CF_LINE_ORDERED_AMTFORMULA;
545
546 FUNCTION CF_LINE_BO_AMTFORMULA(CF_LINE_BO_QTY IN NUMBER
547 ,CF_selling_price IN NUMBER) RETURN NUMBER IS
548 BEGIN
549 DECLARE
550 BO_AMT NUMBER;
551 BEGIN
552 BO_AMT := TRUNC((NVL(CF_LINE_BO_QTY
553 ,0) * NVL(CF_selling_price
554 ,0))
555 ,4);
556 RETURN (BO_AMT);
557 END;
558 END CF_LINE_BO_AMTFORMULA;
559
560 FUNCTION CF_LINE_ORDERED_QTYFORMULA(SOURCE_LINE_SET_ID IN NUMBER) RETURN NUMBER IS
561 BEGIN
562 DECLARE
563 LINE_SET_ORD_QTY NUMBER;
564 BEGIN
565 IF P_SOURCE_SYSTEM = 'OE' THEN
566 IF SOURCE_LINE_SET_ID IS NOT NULL THEN
567 BEGIN
568 SELECT
569 SUM(NVL(ORDERED_QUANTITY
570 ,0))
571 INTO LINE_SET_ORD_QTY
572 FROM
573 OE_ORDER_LINES_ALL
574 WHERE LINE_SET_ID = SOURCE_LINE_SET_ID
575 GROUP BY
576 LINE_SET_ID;
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 SELECT
580 SUM(NVL(ORDERED_QUANTITY
581 ,0))
582 INTO LINE_SET_ORD_QTY
583 FROM
584 OE_ORDER_LINES_ALL
585 WHERE LINE_ID = SOURCE_LINE_SET_ID;
586 END;
587 END IF;
588 RETURN LINE_SET_ORD_QTY;
589 ELSE
590 RETURN NULL;
591 END IF;
592 EXCEPTION
593 WHEN NO_DATA_FOUND THEN
594 RETURN 0;
595 END;
596 END CF_LINE_ORDERED_QTYFORMULA;
597
598 FUNCTION CF_PROMISE_DATEFORMULA(SOURCE_LINE_ID IN NUMBER) RETURN DATE IS
599 PDATE VARCHAR2(20);
600 BEGIN
601 IF P_SOURCE_SYSTEM = 'OE' THEN
602 SELECT
603 OL.PROMISE_DATE
604 INTO PDATE
605 FROM
606 OE_ORDER_LINES_ALL OL
607 WHERE OL.LINE_ID = SOURCE_LINE_ID;
608 END IF;
609 RETURN (PDATE);
610 EXCEPTION
611 WHEN OTHERS THEN
612 RETURN (NULL);
613 END CF_PROMISE_DATEFORMULA;
614
615 FUNCTION CF_DAYS_LATEFORMULA(CF_PROMISE_DATE IN DATE) RETURN NUMBER IS
616 -- DLATE NUMBER;
617 BEGIN
618 DLATE := TRUNC(SYSDATE) - TRUNC(CF_PROMISE_DATE);
619 RETURN (DLATE);
620 END CF_DAYS_LATEFORMULA;
621
622 FUNCTION CF_SOURCE_CODEFORMULA(SOURCE_CODE IN VARCHAR2) RETURN CHAR IS
623 SCODE VARCHAR2(80);
624 BEGIN
625 SELECT
626 MEANING
627 INTO SCODE
628 FROM
629 WSH_LOOKUPS
630 WHERE LOOKUP_CODE = CF_SOURCE_CODEFORMULA.SOURCE_CODE
631 AND LOOKUP_TYPE = 'SOURCE_SYSTEM';
632 RETURN (SCODE);
633 END CF_SOURCE_CODEFORMULA;
634
635 FUNCTION CF_DISTINCT_UOMFORMULA(ORDER_NUMBER IN VARCHAR2) RETURN NUMBER IS
636 UOM_COUNT NUMBER;
637 BEGIN
638 SELECT
639 count(distinct( LINES.ORDER_QUANTITY_UOM ))
640 INTO UOM_COUNT
641 FROM
642 OE_ORDER_LINES_ALL LINES,
643 OE_ORDER_HEADERS_ALL HEAD
644 WHERE LINES.HEADER_ID = HEAD.HEADER_ID
645 AND HEAD.ORDER_NUMBER = CF_DISTINCT_UOMFORMULA.ORDER_NUMBER;
646 IF UOM_COUNT > 1 THEN
647 RETURN 0;
648 ELSE
649 RETURN 1;
650 END IF;
651 -- bug 14133213 added exception handling
652 -- because in case of source as OKE the order number can be string
653 -- and the above code will give invalid number exception.
654 EXCEPTION
655 WHEN OTHERS THEN
656 RETURN 1;
657 END CF_DISTINCT_UOMFORMULA;
658
659 FUNCTION CF_SORT_ENABLEDFORMULA(CS_COUNT_DUOM IN NUMBER
660 ,CS_SUM_DUOM IN NUMBER) RETURN NUMBER IS
661 BEGIN
662 IF CS_COUNT_DUOM <> CS_SUM_DUOM THEN
663 RETURN 0;
664 ELSE
665 RETURN 1;
666 END IF;
667 END CF_SORT_ENABLEDFORMULA;
668
669 FUNCTION CF_CURR_ENABLEDFORMULA(CS_COUNT_SORT_ENABLED IN NUMBER
670 ,CS_SUM_SORT_ENABLED IN NUMBER) RETURN NUMBER IS
671 BEGIN
672 IF CS_COUNT_SORT_ENABLED <> CS_SUM_SORT_ENABLED THEN
673 RETURN 0;
674 ELSE
675 RETURN 1;
676 END IF;
677 END CF_CURR_ENABLEDFORMULA;
678
679 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
680 BEGIN
681 RETURN RP_REPORT_NAME;
682 END RP_REPORT_NAME_P;
683
684 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
685 BEGIN
686 RETURN RP_SUB_TITLE;
687 END RP_SUB_TITLE_P;
688
689 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
690 BEGIN
691 RETURN RP_DATA_FOUND;
692 END RP_DATA_FOUND_P;
693
694 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
695 BEGIN
696 RETURN RP_ITEM_FLEX_ALL_SEG;
697 END RP_ITEM_FLEX_ALL_SEG_P;
698
699 FUNCTION RP_ITEM_FLEX_ALL_SEG_WHERE_P RETURN VARCHAR2 IS
700 BEGIN
701 RETURN RP_ITEM_FLEX_ALL_SEG_WHERE;
702 END RP_ITEM_FLEX_ALL_SEG_WHERE_P;
703
704 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
705 BEGIN
706 RETURN RP_ORDER_BY;
707 END RP_ORDER_BY_P;
708
709 FUNCTION RP_FLEX_OR_DESC_P RETURN VARCHAR2 IS
710 BEGIN
711 RETURN RP_FLEX_OR_DESC;
712 END RP_FLEX_OR_DESC_P;
713
714 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
715 BEGIN
716 RETURN RP_ORDER_RANGE;
717 END RP_ORDER_RANGE_P;
718
719 FUNCTION RP_CATEGORY_SET_P RETURN VARCHAR2 IS
720 BEGIN
721 RETURN RP_CATEGORY_SET;
722 END RP_CATEGORY_SET_P;
723
724 FUNCTION RP_CATEGORY_P RETURN VARCHAR2 IS
725 BEGIN
726 RETURN RP_CATEGORY;
727 END RP_CATEGORY_P;
728
729 FUNCTION RP_ORDER_NUM_HIGH_P RETURN NUMBER IS
730 BEGIN
731 RETURN RP_ORDER_NUM_HIGH;
732 END RP_ORDER_NUM_HIGH_P;
733
734 FUNCTION RP_ORDER_NUM_LOW_P RETURN NUMBER IS
735 BEGIN
736 RETURN RP_ORDER_NUM_LOW;
737 END RP_ORDER_NUM_LOW_P;
738
739 FUNCTION RP_WAREHOUSE_P RETURN VARCHAR2 IS
740 BEGIN
741 RETURN RP_WAREHOUSE;
742 END RP_WAREHOUSE_P;
743
744 FUNCTION RP_ITEM_P RETURN VARCHAR2 IS
745 BEGIN
746 RETURN RP_ITEM;
747 END RP_ITEM_P;
748
749 FUNCTION RP_SOURCE_SYSTEM_P RETURN VARCHAR2 IS
750 BEGIN
751 RETURN RP_SOURCE_SYSTEM;
752 END RP_SOURCE_SYSTEM_P;
753
754 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
755 ,NAME IN VARCHAR2) IS
756 BEGIN
757 /*STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
758 STPROC.BIND_I(APPLICATION);
759 STPROC.BIND_I(NAME);
760 STPROC.EXECUTE; */NULL;
761 END SET_NAME;
762
763 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
764 ,VALUE IN VARCHAR2
765 ,TRANSLATE IN BOOLEAN) IS
766 BEGIN
767 /*STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
768 STPROC.BIND_I(TRANSLATE);
769 STPROC.BIND_I(TOKEN);
770 STPROC.BIND_I(VALUE);
771 STPROC.EXECUTE;*/NULL;
772 END SET_TOKEN;
773
774 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
775 BEGIN
776 /*STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
777 STPROC.BIND_O(MSGOUT);
778 STPROC.EXECUTE;
779 STPROC.RETRIEVE(1
780 ,MSGOUT);*/NULL;
781 END RETRIEVE;
782
783 PROCEDURE CLEAR IS
784 BEGIN
785 /*STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
786 STPROC.EXECUTE;*/NULL;
787 END CLEAR;
788
789 FUNCTION GET_STRING(APPIN IN VARCHAR2
790 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
791 X0 VARCHAR2(2000);
792 BEGIN
793 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
794 STPROC.BIND_O(X0);
795 STPROC.BIND_I(APPIN);
796 STPROC.BIND_I(NAMEIN);
797 STPROC.EXECUTE;
798 STPROC.RETRIEVE(1
799 ,X0);
800 RETURN X0;*/
801 RETURN(NULL);
802 END GET_STRING;
803
804 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
805 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
806 X0 NUMBER;
807 BEGIN
808 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
809 STPROC.BIND_O(X0);
810 STPROC.BIND_I(APPIN);
811 STPROC.BIND_I(NAMEIN);
812 STPROC.EXECUTE;
813 STPROC.RETRIEVE(1
814 ,X0);
815 RETURN X0;*/
816 RETURN(NULL);
817 END GET_NUMBER;
818
819 FUNCTION GET RETURN VARCHAR2 IS
820 X0 VARCHAR2(2000);
821 BEGIN
822 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
823 STPROC.BIND_O(X0);
824 STPROC.EXECUTE;
825 STPROC.RETRIEVE(1
826 ,X0);
827 RETURN X0;*/
828 RETURN(NULL);
829 END GET;
830
831 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
832 X0 VARCHAR2(2000);
833 BEGIN
834 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
835 STPROC.BIND_O(X0);
836 STPROC.EXECUTE;
837 STPROC.RETRIEVE(1
838 ,X0);
839 RETURN X0;*/
840 RETURN(NULL);
841 END GET_ENCODED;
842
843 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
844 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
845 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
846 BEGIN
847 /*STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
848 STPROC.BIND_I(ENCODED_MESSAGE);
849 STPROC.BIND_O(APP_SHORT_NAME);
850 STPROC.BIND_O(MESSAGE_NAME);
851 STPROC.EXECUTE;
852 STPROC.RETRIEVE(2
853 ,APP_SHORT_NAME);
854 STPROC.RETRIEVE(3
855 ,MESSAGE_NAME);*/
856 NULL;
857 END PARSE_ENCODED;
858
859 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
860 BEGIN
861 /*STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
862 STPROC.BIND_I(ENCODED_MESSAGE);
863 STPROC.EXECUTE;*/NULL;
864 END SET_ENCODED;
865
866 PROCEDURE RAISE_ERROR IS
867 BEGIN
868 /* STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
869 STPROC.EXECUTE;*/NULL;
870 END RAISE_ERROR;
871
872 END WSH_WSHRDBDR_XMLP_PKG;
873
874
[Home] [Help]