1 PACKAGE BODY WIP_WIPDJDSP_XMLP_PKG AS
2 /* $Header: WIPDJDSPB.pls 120.1 2008/01/31 12:14:53 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 QTY_PRECISION := wip_common_xmlp_pkg.get_precision(p_qty_precision);
7
8 select id_flex_num_cl
9 into item_id_flex_num
10 from(
11 SELECT fifst.id_flex_num id_flex_num_cl
12 FROM fnd_id_flex_structures fifst
13 WHERE fifst.application_id = 401
14 AND fifst.id_flex_code = 'MSTK'
15 AND fifst.enabled_flag = 'Y'
16 AND fifst.freeze_flex_definition_flag = 'Y'
17 ORDER BY fifst.id_flex_num)
18 where rownum<2;
19 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
20 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK"
21 APPL_SHORT_NAME="INV" OUTPUT=":P_ITEM_DATA"
22 MODE="SELECT" DISPLAY="ALL" TABLEALIAS="SI"')*/NULL;
23 RETURN (TRUE);
24 END BEFOREREPORT;
25
26 FUNCTION AFTERREPORT RETURN BOOLEAN IS
27 BEGIN
28 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
29 RETURN (TRUE);
30 END AFTERREPORT;
31
32 FUNCTION DYNAMIC_ORDER_BY RETURN CHARACTER IS
33 ORDER_BY_CLAUSE VARCHAR2(500);
34 BEGIN
35 IF (SORT_CODE = 7) THEN
36 ORDER_BY_CLAUSE := 'ORDER BY 5 ASC,6 ASC , WO.FIRST_UNIT_START_DATE';
37 ELSIF (SORT_CODE = 8) THEN
38 ORDER_BY_CLAUSE := 'ORDER BY 5 ASC,6 ASC ,WO.LAST_UNIT_COMPLETION_DATE';
39 END IF;
40 ORDER_BY_CLAUSE := ORDER_BY_CLAUSE || ', WE.WIP_ENTITY_NAME, WO.OPERATION_SEQ_NUM, WE.WIP_ENTITY_ID';
41 RETURN (ORDER_BY_CLAUSE);
42 END DYNAMIC_ORDER_BY;
43
44 FUNCTION DATE_LIMITER RETURN CHARACTER IS
45 LIMIT_DATES VARCHAR2(200);
46 BEGIN
47 IF (FROM_DATE IS NOT NULL) THEN
48 IF (TO_DATE IS NOT NULL) THEN
49 LIMIT_DATES := ' AND DECODE(' || TO_CHAR(SORT_CODE) || ',7,trunc(WO.FIRST_UNIT_START_DATE),8, trunc(WO.LAST_UNIT_COMPLETION_DATE)) BETWEEN TO_DATE(''' || TO_CHAR(FROM_DATE
50 ,'YYYYMMDD') || ''',''YYYYMMDD'') AND TO_DATE(''' || TO_CHAR(TO_DATE
51 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
52 ELSE
53 LIMIT_DATES := ' AND DECODE(' || TO_CHAR(SORT_CODE) || ',7,trunc(WO.FIRST_UNIT_START_DATE),8, trunc(WO.LAST_UNIT_COMPLETION_DATE)) >= TO_DATE(''' || TO_CHAR(FROM_DATE
54 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
55 END IF;
56 ELSE
57 IF (TO_DATE IS NOT NULL) THEN
58 LIMIT_DATES := ' AND DECODE(' || TO_CHAR(SORT_CODE) || ',7,trunc(WO.FIRST_UNIT_START_DATE),8,trunc(WO.LAST_UNIT_COMPLETION_DATE)) <= TO_DATE(''' || TO_CHAR(TO_DATE
59 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
60 ELSE
61 LIMIT_DATES := ' ';
62 END IF;
63 END IF;
64 RETURN (LIMIT_DATES);
65 END DATE_LIMITER;
66
67 FUNCTION DEPARTMENT_LIMITER RETURN CHARACTER IS
68 LIMIT_DEPT VARCHAR2(80);
69 BEGIN
70 IF (P_FROM_DEPT IS NOT NULL) THEN
71 IF (P_TO_DEPT IS NOT NULL) THEN
72 LIMIT_DEPT := ' AND BD.DEPARTMENT_CODE BETWEEN ''' || P_FROM_DEPT || ''' AND ''' || P_TO_DEPT || '''';
73 ELSE
74 LIMIT_DEPT := ' AND BD.DEPARTMENT_CODE >= ''' || P_FROM_DEPT || '''';
75 END IF;
76 ELSE
77 IF (P_TO_DEPT IS NOT NULL) THEN
78 LIMIT_DEPT := ' AND BD.DEPARTMENT_CODE <= ''' || P_TO_DEPT || '''';
79 ELSE
80 LIMIT_DEPT := ' ';
81 END IF;
82 END IF;
83 RETURN (LIMIT_DEPT);
84 END DEPARTMENT_LIMITER;
85
86 FUNCTION C_QUANTITY_IN_PREVFORMULA(WE_ID_p IN NUMBER ,OP_SEQ_p IN NUMBER) RETURN NUMBER IS
87 BEGIN
88 /*SRW.REFERENCE(WE_ID)*/NULL;
89 /*SRW.REFERENCE(ORG_ID)*/NULL;
90 /*SRW.REFERENCE(OP_SEQ)*/NULL;
91
92 SELECT (SUM(QUANTITY_IN_QUEUE)
93 + SUM(QUANTITY_RUNNING)
94 + SUM(QUANTITY_REJECTED)
95 + SUM(QUANTITY_WAITING_TO_MOVE))
96 INTO QUANTITY_REM_IN_PREV
97 FROM WIP_OPERATIONS
98 WHERE WIP_ENTITY_ID = WE_ID_p
99 AND ORGANIZATION_ID = org_id
100 AND OPERATION_SEQ_NUM < OP_SEQ_p;
101 RETURN (QUANTITY_REM_IN_PREV);
102 END C_QUANTITY_IN_PREVFORMULA;
103
104 FUNCTION QUANTITY_REMAININGFORMULA(STATUS_TYPE IN NUMBER
105 ,QUANTITY_SCHEDULED IN NUMBER
106 ,QUANTITY_IN_QUEUE IN NUMBER
107 ,QUANTITY_RUNNING IN NUMBER
108 ,C_QUANTITY_IN_PREV IN NUMBER) RETURN NUMBER IS
109 BEGIN
110 IF (STATUS_TYPE = 1) THEN
111 RETURN QUANTITY_SCHEDULED;
112 ELSE
113 RETURN (QUANTITY_IN_QUEUE + QUANTITY_RUNNING + C_QUANTITY_IN_PREV);
114 END IF;
115 RETURN NULL;
116 END QUANTITY_REMAININGFORMULA;
117
118 FUNCTION STATUS_LIMITER RETURN CHARACTER IS
119 STATUS VARCHAR2(3000);
120 BEGIN
121 IF (P_INCLUDE_UNRELEASE = 1) THEN
122 STATUS := 'AND DJ.STATUS_TYPE IN (1,3,4,6)';
123 ELSE
124 STATUS := 'AND DJ.STATUS_TYPE IN (3,4,6)';
125 END IF;
126 RETURN (STATUS);
127 END STATUS_LIMITER;
128
129 FUNCTION AFTERPFORM RETURN BOOLEAN IS
130 BEGIN
131 IF P_SCHEDULE_GROUP IS NOT NULL THEN
132 P_SG_OUTER := ' ';
133 END IF;
134 RETURN (TRUE);
135 END AFTERPFORM;
136
137 FUNCTION C_LIMITER RETURN VARCHAR2 IS
138 C_OUT VARCHAR2(200);
139 BEGIN
140 IF P_SCHEDULE_GROUP IS NOT NULL THEN
141 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME = ''' || P_SCHEDULE_GROUP || '''';
142 ELSE
143 C_OUT := ' ';
144 END IF;
145 RETURN (C_OUT);
146 END C_LIMITER;
147
148 FUNCTION QUANTITY_REM_IN_PREV_P RETURN NUMBER IS
149 BEGIN
150 RETURN QUANTITY_REM_IN_PREV;
151 END QUANTITY_REM_IN_PREV_P;
152
153 END WIP_WIPDJDSP_XMLP_PKG;
154
[Home] [Help]