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