1 PACKAGE BODY WIP_WIPLBPER_XMLP_PKG AS
2 /* $Header: WIPLBPERB.pls 120.1 2008/01/31 12:24:59 npannamp noship $ */
3 FUNCTION LIMIT_EFFICIENCY(C_EFF_TOTAL_MAIN IN NUMBER) RETURN BOOLEAN IS
4 BEGIN
5 IF (P_MINIMUM_EFFICIENCY IS NOT NULL) THEN
6 IF (P_MAXIMUM_EFFICIENCY IS NOT NULL) THEN
7 IF ((C_EFF_TOTAL_MAIN >= P_MINIMUM_EFFICIENCY) AND (C_EFF_TOTAL_MAIN <= P_MAXIMUM_EFFICIENCY)) THEN
8 RETURN (TRUE);
9 ELSE
10 RETURN (FALSE);
11 END IF;
12 ELSE
13 RETURN (C_EFF_TOTAL_MAIN >= P_MINIMUM_EFFICIENCY);
14 END IF;
15 ELSE
16 IF (P_MAXIMUM_EFFICIENCY IS NOT NULL) THEN
17 RETURN (C_EFF_TOTAL_MAIN <= P_MAXIMUM_EFFICIENCY);
18 ELSE
19 RETURN (TRUE);
20 END IF;
21 END IF;
22 RETURN NULL;
23 END LIMIT_EFFICIENCY;
24
25 FUNCTION LIMIT_DEPT RETURN CHARACTER IS
26 LIMIT_DEPT VARCHAR2(80);
27 BEGIN
28 IF (P_FROM_DEPARTMENT IS NOT NULL) THEN
29 IF (P_TO_DEPARTMENT IS NOT NULL) THEN
30 LIMIT_DEPT := 'AND BD.department_code BETWEEN ''' || P_FROM_DEPARTMENT || ''' AND ''' || P_TO_DEPARTMENT || '''';
31 ELSE
32 LIMIT_DEPT := 'AND BD.department_code >= ''' || P_FROM_DEPARTMENT || '''';
33 END IF;
34 ELSE
35 IF (P_TO_DEPARTMENT IS NOT NULL) THEN
36 LIMIT_DEPT := 'AND BD.department_code <= ''' || P_TO_DEPARTMENT || '''';
37 ELSE
38 LIMIT_DEPT := ' ';
39 END IF;
40 END IF;
41 RETURN (LIMIT_DEPT);
42 END LIMIT_DEPT;
43
44 FUNCTION LIMIT_RESOURCE RETURN CHARACTER IS
45 LIMIT_RES VARCHAR2(80);
46 BEGIN
47 IF (P_FROM_RESOURCE IS NOT NULL) THEN
48 IF (P_TO_RESOURCE IS NOT NULL) THEN
49 LIMIT_RES := 'AND BR.resource_code between ''' || P_FROM_RESOURCE || ''' AND ''' || P_TO_RESOURCE || '''';
50 ELSE
51 LIMIT_RES := 'AND BR.resource_code >= ''' || P_FROM_RESOURCE || '''';
52 END IF;
53 ELSE
54 IF (P_TO_RESOURCE IS NOT NULL) THEN
55 LIMIT_RES := 'AND BR.resource_code <= ''' || P_TO_RESOURCE || '''';
56 ELSE
57 LIMIT_RES := ' ';
58 END IF;
59 END IF;
60 RETURN (LIMIT_RES);
61 END LIMIT_RESOURCE;
62
63 FUNCTION LIMIT_JOB_DATES RETURN CHARACTER IS
64 LIMIT_DATES VARCHAR2(2000);
65 BEGIN
66 IF (P_FROM_DATE IS NOT NULL) THEN
67 IF (P_TO_DATE IS NOT NULL) THEN
68 LIMIT_DATES := 'AND ((WE.ENTITY_TYPE IN (1,3)
69 AND EXISTS
70 (SELECT 1 FROM WIP_DISCRETE_JOBS WDJ
71 WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
72 AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
73 AND TRUNC(WDJ.DATE_RELEASED) <= TO_DATE(''' || TO_CHAR(P_TO_DATE
74 ,'YYYYMMDD') || ''',''YYYYMMDD'') AND NVL(TRUNC(WDJ.DATE_CLOSED),TO_DATE(''' || TO_CHAR(P_FROM_DATE
75 ,'YYYYMMDD') || ''',''YYYYMMDD'')) >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
76 ,'YYYYMMDD') || ''',''YYYYMMDD'')))
77 OR (WE.ENTITY_TYPE = 2
78 AND EXISTS
79 (SELECT 1 FROM WIP_REPETITIVE_SCHEDULES WRS1
80 WHERE WRS1.ORGANIZATION_ID = WE.ORGANIZATION_ID
81 AND WRS1.REPETITIVE_SCHEDULE_ID =
82 WOP.REPETITIVE_SCHEDULE_ID
83 AND TRUNC(WRS1.DATE_RELEASED) <= TO_DATE(''' || TO_CHAR(P_TO_DATE
84 ,'YYYYMMDD') || ''',''YYYYMMDD'') AND NVL(TRUNC(WRS1.DATE_CLOSED),TO_DATE(''' || TO_CHAR(P_FROM_DATE
85 ,'YYYYMMDD') || ''',''YYYYMMDD'')) >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
86 ,'YYYYMMDD') || ''',''YYYYMMDD''))))';
87 ELSE
88 LIMIT_DATES := 'AND ((WE.ENTITY_TYPE IN (1,3)
89 AND EXISTS
90 (SELECT 1 FROM WIP_DISCRETE_JOBS WDJ
91 WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
92 AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
93 AND WDJ.DATE_RELEASED IS NOT NULL
94 AND NVL(TRUNC(WDJ.DATE_CLOSED),TO_DATE(''' || TO_CHAR(P_FROM_DATE
95 ,'YYYYMMDD') || ''',''YYYYMMDD'')) >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
96 ,'YYYYMMDD') || ''',''YYYYMMDD'')))
97 OR (WE.ENTITY_TYPE = 2
98 AND EXISTS
99 (SELECT 1 FROM WIP_REPETITIVE_SCHEDULES WRS1
100 WHERE WRS1.ORGANIZATION_ID = WE.ORGANIZATION_ID
101 AND WRS1.REPETITIVE_SCHEDULE_ID =
102 WOP.REPETITIVE_SCHEDULE_ID
103 AND WRS1.DATE_RELEASED IS NOT NULL
104 AND NVL(TRUNC(WRS1.DATE_CLOSED),TO_DATE(''' || TO_CHAR(P_FROM_DATE
105 ,'YYYYMMDD') || ''',''YYYYMMDD'')) >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
106 ,'YYYYMMDD') || ''',''YYYYMMDD''))))';
107 END IF;
108 ELSE
109 IF (P_TO_DATE IS NOT NULL) THEN
110 LIMIT_DATES := 'AND ((WE.ENTITY_TYPE IN (1,3)
111 AND EXISTS
112 (SELECT 1 FROM WIP_DISCRETE_JOBS WDJ
113 WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
114 AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
115 AND TRUNC(WDJ.DATE_RELEASED) <= TO_DATE(''' || TO_CHAR(P_TO_DATE
116 ,'YYYYMMDD') || ''',''YYYYMMDD'')))
117 OR (WE.ENTITY_TYPE = 2
118 AND EXISTS
119 (SELECT 1 FROM WIP_REPETITIVE_SCHEDULES WRS1
120 WHERE WRS1.ORGANIZATION_ID = WE.ORGANIZATION_ID
121 AND WRS1.REPETITIVE_SCHEDULE_ID =
122 WOP.REPETITIVE_SCHEDULE_ID
123 AND TRUNC(WRS1.DATE_RELEASED) <= TO_DATE(''' || TO_CHAR(P_TO_DATE
124 ,'YYYYMMDD') || ''',''YYYYMMDD''))))';
125 ELSE
126 LIMIT_DATES := ' ';
127 END IF;
128 END IF;
129 RETURN (LIMIT_DATES);
130 END LIMIT_JOB_DATES;
131
132 FUNCTION C_STD_UNITSFORMULA(BASIS_TYPE IN NUMBER
133 ,USAGE_RATE1 IN NUMBER
134 ,ASSY_UNITS1 IN NUMBER
135 ,USAGE_RATE IN NUMBER) RETURN NUMBER IS
136 BEGIN
137 IF (BASIS_TYPE = 1) THEN
138 RETURN (ROUND((USAGE_RATE1 * ASSY_UNITS1)
139 ,P_QTY_PRECISION));
140 ELSE
141 IF (ASSY_UNITS1 > 0) THEN
142 RETURN (USAGE_RATE);
143 ELSE
144 RETURN (0);
145 END IF;
146 END IF;
147 RETURN NULL;
148 END C_STD_UNITSFORMULA;
149
150 FUNCTION C_EFFICIENCYFORMULA(APPLIED IN NUMBER
151 ,C_STD_UNITS IN NUMBER) RETURN NUMBER IS
152 BEGIN
153 IF (APPLIED = 0) THEN
154 RETURN (-1);
155 ELSE
156 RETURN (100 * C_STD_UNITS / APPLIED);
157 END IF;
158 RETURN NULL;
159 END C_EFFICIENCYFORMULA;
160
161 FUNCTION C_EFF_TOTAL_MAINFORMULA(C_APPLIED_TOTAL IN NUMBER
162 ,C_STD_TOTAL IN NUMBER) RETURN NUMBER IS
163 BEGIN
164 IF (C_APPLIED_TOTAL = 0) THEN
165 RETURN (-1);
166 ELSE
167 RETURN (100 * C_STD_TOTAL / C_APPLIED_TOTAL);
168 END IF;
169 RETURN NULL;
170 END C_EFF_TOTAL_MAINFORMULA;
171
172 FUNCTION C_EFF_FLAGFORMULA(C_EFF_TOTAL_MAIN IN NUMBER) RETURN NUMBER IS
173 BEGIN
174 /*SRW.REFERENCE(C_EFF_TOTAL_MAIN)*/NULL;
175 IF (LIMIT_EFFICIENCY(C_EFF_TOTAL_MAIN)) THEN
176 RETURN (1);
177 ELSE
178 RETURN (0);
179 END IF;
180 RETURN NULL;
181 END C_EFF_FLAGFORMULA;
182
183 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
184 BEGIN
185 P_FROM_DATE1:=to_char(P_FROM_DATE,'DD-MON-YY');
186 P_TO_DATE1:=to_char(P_TO_DATE,'DD-MON-YY');
187 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
188 CP_CURRENCY_CODE := WIP_common_xmlp_pkg.get_precision(2);
189 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
190 RETURN (TRUE);
191 END BEFOREREPORT;
192
193 FUNCTION AFTERREPORT RETURN BOOLEAN IS
194 BEGIN
195 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
196 RETURN (TRUE);
197 END AFTERREPORT;
198
199 FUNCTION AFTERPFORM RETURN BOOLEAN IS
200 BEGIN
201 RETURN (TRUE);
202 END AFTERPFORM;
203
204 FUNCTION C_LIMIT_DATE_FLOWFORMULA RETURN CHAR IS
205 LIMIT_DATES VARCHAR2(2000);
206 BEGIN
207 IF (P_FROM_DATE IS NOT NULL) THEN
208 IF (P_TO_DATE IS NOT NULL) THEN
209 LIMIT_DATES := 'AND EXISTS
210 (SELECT 1 FROM WIP_FLOW_SCHEDULES WFS2
211 WHERE WFS2.WIP_ENTITY_ID = WF.WIP_ENTITY_ID
212 AND WFS2.ORGANIZATION_ID = WF.ORGANIZATION_ID
213 AND TRUNC(WFS2.SCHEDULED_START_DATE) <= TO_DATE(''' || TO_CHAR(P_TO_DATE
214 ,'YYYYMMDD') || ''',''YYYYMMDD'') AND NVL(TRUNC(WFS2.SCHEDULED_COMPLETION_DATE),TO_DATE(''' || TO_CHAR(P_FROM_DATE
215 ,'YYYYMMDD') || ''',''YYYYMMDD'')) >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
216 ,'YYYYMMDD') || ''',''YYYYMMDD'')))';
217 ELSE
218 LIMIT_DATES := 'AND EXISTS
219 (SELECT 1 FROM WIP_FLOW_SCHEDULES WFS2
220 WHERE WFS2.ORGANIZATION_ID = WF.ORGANIZATION_ID
221 AND WFS2.WIP_ENTITY_ID = WF.WIP_ENTITY_ID
222 AND WFS2.SCHEDULED_START_DATE IS NOT NULL
223 AND NVL(TRUNC(WFS2.SCHEDULED_COMPLETION_DATE),TO_DATE(''' || TO_CHAR(P_FROM_DATE
224 ,'YYYYMMDD') || ''',''YYYYMMDD'')) >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
225 ,'YYYYMMDD') || ''',''YYYYMMDD'')))';
226 END IF;
227 ELSE
228 IF (P_TO_DATE IS NOT NULL) THEN
229 LIMIT_DATES := 'AND EXISTS
230 (SELECT 1 FROM WIP_FLOW_SCHEDULES WFS2
231 WHERE WFS2.ORGANIZATION_ID = WF.ORGANIZATION_ID
232 AND WFS2.WIP_ENTITY_ID = WF.WIP_ENTITY_ID
233 AND TRUNC(WFS2.SCHEDULED_START_DATE) <= TO_DATE(''' || TO_CHAR(P_TO_DATE
234 ,'YYYYMMDD') || ''',''YYYYMMDD'')))';
235 ELSE
236 LIMIT_DATES := ' ';
237 END IF;
238 END IF;
239 RETURN (LIMIT_DATES);
240 END C_LIMIT_DATE_FLOWFORMULA;
241
242 END WIP_WIPLBPER_XMLP_PKG;
243
[Home] [Help]