1 PACKAGE BODY GME_GMEBCHPL_XMLP_PKG AS
2 /* $Header: GMEBCHPLB.pls 120.1 2008/04/03 06:34:04 dwkrishn noship $ */
3 FUNCTION CF_TRANS_QTYFORMULA(TRANS_QTY IN NUMBER) RETURN NUMBER IS
4 TEMP NUMBER;
5 BEGIN
6 SELECT
7 ABS(TRANS_QTY)
8 INTO TEMP
9 FROM
10 DUAL;
11 RETURN (TEMP);
12 END CF_TRANS_QTYFORMULA;
13
14 FUNCTION CF_SUM_TRANS_QTYFORMULA(SUM_TRANS_QTY IN NUMBER) RETURN NUMBER IS
15 TEMP NUMBER;
16 BEGIN
17 SELECT
18 ABS(SUM_TRANS_QTY)
19 INTO TEMP
20 FROM
21 DUAL;
22 RETURN (TEMP);
23 END CF_SUM_TRANS_QTYFORMULA;
24
25 FUNCTION CF_SUM_TRANS_QTY2FORMULA(SUM_TRANS_QTY2 IN NUMBER) RETURN NUMBER IS
26 TEMP NUMBER;
27 BEGIN
28 SELECT
29 ABS(SUM_TRANS_QTY2)
30 INTO TEMP
31 FROM
32 DUAL;
33 RETURN (TEMP);
34 END CF_SUM_TRANS_QTY2FORMULA;
35
36 FUNCTION CF_BATCHRANGEFORMULA RETURN VARCHAR2 IS
37 BEGIN
38 IF FROMBATCH IS NOT NULL AND TOBATCH IS NOT NULL AND LPAD(FROMBATCH
39 ,32
40 ,'0') = LPAD(TOBATCH
41 ,32
42 ,'0') THEN
43 CP_BATCHRANGE := ' and Lpad(h.batch_no,32,''0'') = ' || '''' || LPAD(FROMBATCH
44 ,32
45 ,'0') || '''';
46 ELSIF FROMBATCH IS NOT NULL AND TOBATCH IS NOT NULL THEN
47 CP_BATCHRANGE := ' and Lpad(h.batch_no,32,''0'') between ' || '''' || LPAD(FROMBATCH
48 ,32
49 ,'0') || '''' || ' and ' || '''' || LPAD(TOBATCH
50 ,32
51 ,'0') || '''';
52 ELSIF FROMBATCH IS NULL AND TOBATCH IS NULL THEN
53 CP_BATCHRANGE := ' ';
54 ELSIF FROMBATCH IS NOT NULL AND TOBATCH IS NULL THEN
55 CP_BATCHRANGE := 'and Lpad(h.batch_no,32,''0'') >= ' || '''' || LPAD(FROMBATCH
56 ,32
57 ,'0') || '''';
58 END IF;
59 RETURN (CP_BATCHRANGE);
60 END CF_BATCHRANGEFORMULA;
61
62 FUNCTION CF_WHSERANGEFORMULA RETURN VARCHAR2 IS
63 BEGIN
64 IF FROMWHSE IS NOT NULL AND TOWHSE IS NOT NULL AND FROMWHSE = TOWHSE THEN
65 CP_WHSERANGE := ' and mr.subinventory_code = ' || '''' || FROMWHSE || '''';
66 ELSIF FROMWHSE IS NOT NULL AND TOWHSE IS NOT NULL THEN
67 CP_WHSERANGE := ' and mr.subinventory_code between ' || '''' || FROMWHSE || ''' and ' || '''' || TOWHSE || '''';
68 ELSIF FROMWHSE IS NULL AND TOWHSE IS NULL THEN
69 CP_WHSERANGE := ' ';
70 ELSIF FROMWHSE IS NOT NULL AND TOWHSE IS NULL THEN
71 CP_WHSERANGE := 'and mr.subinventory_code >= ' || '''' || FROMWHSE || '''';
72 END IF;
73 RETURN (CP_WHSERANGE);
74 END CF_WHSERANGEFORMULA;
75
76 FUNCTION CF_DATERANGEFORMULA RETURN VARCHAR2 IS
77 BEGIN
78 RETURN NULL;
79 END CF_DATERANGEFORMULA;
80
81 FUNCTION CF_SORTBYFORMULA RETURN VARCHAR2 IS
82 BEGIN
83 IF SORTBY = '1' THEN
84 CP_SORTBY := ' ORDER BY 6 ASC,1 ASC,2 ASC,9 ASC,3 ASC,17 ASC,4 ASC,5 ASC,16 ASC,14 ASC,12 ASC,11 ASC ,h.batch_no, mr.subinventory_code, msik.concatenated_segments, mil.concatenated_segments, mr.lot_number ASC';
85 ELSIF SORTBY = '2' THEN
86 CP_SORTBY := ' ORDER BY 6 ASC,1 ASC,2 ASC,9 ASC,3 ASC,17 ASC,4 ASC,5 ASC,16 ASC,14 ASC,12 ASC,11 ASC , mr.subinventory_code, msik.concatenated_segments, mil.concatenated_segments,h.batch_no ASC';
87 END IF;
88 RETURN (CP_SORTBY);
89 END CF_SORTBYFORMULA;
90
91 FUNCTION CF_TRANS_QTY2FORMULA(TRANS_QTY2 IN NUMBER) RETURN NUMBER IS
92 TEMP NUMBER;
93 BEGIN
94 SELECT
95 ABS(TRANS_QTY2)
96 INTO TEMP
97 FROM
98 DUAL;
99 RETURN (TEMP);
100 END CF_TRANS_QTY2FORMULA;
101
102 FUNCTION AFTERPFORM RETURN BOOLEAN IS
103 BEGIN
104 IF FROMBATCH > TOBATCH THEN
105 RAISE_APPLICATION_ERROR(-20101,null);
106 END IF;
107 IF FROMDATE > TODATE THEN
108 RAISE_APPLICATION_ERROR(-20101,null);
109 END IF;
110 IF FROMWHSE > TOWHSE THEN
111 RAISE_APPLICATION_ERROR(-20101,null);
112 END IF;
113 IF FROMBATCH IS NULL THEN
114 IF TOBATCH IS NOT NULL THEN
115 RAISE_APPLICATION_ERROR(-20101,null);
116 END IF;
117 END IF;
118 IF FROMWHSE IS NULL THEN
119 IF TOWHSE IS NOT NULL THEN
120 RAISE_APPLICATION_ERROR(-20101,null);
121 END IF;
122 END IF;
123 RETURN (TRUE);
124 END AFTERPFORM;
125
126 FUNCTION SORTRETCFFORMULA RETURN VARCHAR2 IS
127 X_SORT1 VARCHAR2(80);
128 CURSOR CUR_SELECT IS
129 SELECT
130 MEANING
131 FROM
132 FND_LOOKUP_VALUES_VL
133 WHERE LOOKUP_CODE = SORTBY
134 AND LOOKUP_TYPE = 'PM_RIPMIUSR_SORT';
135 BEGIN
136 OPEN CUR_SELECT;
137 FETCH CUR_SELECT
138 INTO X_SORT1;
139 CLOSE CUR_SELECT;
140 RETURN (X_SORT1);
141 END SORTRETCFFORMULA;
142
143 FUNCTION CF_ACTUAL_START_DATEFORMULA(ACTUAL_START_DATE IN DATE) RETURN DATE IS
144 BEGIN
145 IF ACTUAL_START_DATE <> NULL THEN
146 RETURN (ACTUAL_START_DATE);
147 ELSE
148 RETURN (NULL);
149 END IF;
150 RETURN NULL;
151 END CF_ACTUAL_START_DATEFORMULA;
152
153 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
154 BEGIN
155 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
156 C_FROMDATE := TO_CHAR(FROMDATE,'DD-MON-YYYY');
157 C_TODATE := TO_CHAR(TODATE,'DD-MON-YYYY');
158 RETURN (TRUE);
159 END BEFOREREPORT;
160
161 PROCEDURE HEADER IS
162 BEGIN
163 NULL;
164 END HEADER;
165
166 FUNCTION CF_PLAN_QTY2FORMULA(ITEM_UM IN VARCHAR2
167 ,ITEM_UM2 IN VARCHAR2
168 ,ITEM_ID IN NUMBER
169 ,PLAN_QTY2 IN NUMBER) RETURN NUMBER IS
170 X_RET NUMBER;
171 V_QTY NUMBER;
172 BEGIN
173 IF ITEM_UM <> ITEM_UM2 THEN
174 V_QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => ITEM_ID
175 ,PRECISION => 5
176 ,FROM_QUANTITY => NVL(PLAN_QTY2
177 ,0)
178 ,FROM_UNIT => ITEM_UM
179 ,TO_UNIT => ITEM_UM2
180 ,FROM_NAME => NULL
181 ,TO_NAME => NULL);
182 END IF;
183 RETURN (V_QTY);
184 END CF_PLAN_QTY2FORMULA;
185
186 FUNCTION AFTERREPORT RETURN BOOLEAN IS
187 BEGIN
188 RETURN (TRUE);
189 END AFTERREPORT;
190
191 FUNCTION CF_CONTEXTORGFORMULA RETURN CHAR IS
192 CURSOR C_GET_ORG IS
193 SELECT
194 ORGANIZATION_CODE
195 FROM
196 MTL_PARAMETERS
197 WHERE ORGANIZATION_ID = P_ORG_ID;
198 L_ORG VARCHAR2(6);
199 BEGIN
200 OPEN C_GET_ORG;
201 FETCH C_GET_ORG
202 INTO L_ORG;
203 CLOSE C_GET_ORG;
204 L_ORG := '(' || L_ORG || ')';
205 RETURN L_ORG;
206 END CF_CONTEXTORGFORMULA;
207
208 FUNCTION CF_INV_PLAN_QTYFORMULA(ITEM_UM IN VARCHAR2
209 ,ITEM_UM4 IN VARCHAR2
210 ,ITEM_ID IN NUMBER
211 ,PLAN_QTY2 IN NUMBER) RETURN NUMBER IS
212 V_QTY NUMBER;
213 BEGIN
214 IF ITEM_UM <> ITEM_UM4 THEN
215 V_QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID
216 ,5
217 ,PLAN_QTY2
218 ,ITEM_UM
219 ,ITEM_UM4
220 ,NULL
221 ,NULL);
222 RETURN (V_QTY);
223 ELSE
224 RETURN (PLAN_QTY2);
225 END IF;
226 END CF_INV_PLAN_QTYFORMULA;
227
228 FUNCTION CP_BATCHRANGE_P RETURN VARCHAR2 IS
229 BEGIN
230 RETURN CP_BATCHRANGE;
231 END CP_BATCHRANGE_P;
232
233 FUNCTION CP_WHSERANGE_P RETURN VARCHAR2 IS
234 BEGIN
235 RETURN CP_WHSERANGE;
236 END CP_WHSERANGE_P;
237
238 FUNCTION CP_DATERANGE_P RETURN VARCHAR2 IS
239 BEGIN
240 RETURN CP_DATERANGE;
241 END CP_DATERANGE_P;
242
243 FUNCTION CP_SORTBY_P RETURN VARCHAR2 IS
244 BEGIN
245 RETURN CP_SORTBY;
246 END CP_SORTBY_P;
247
248 END GME_GMEBCHPL_XMLP_PKG;
249
[Home] [Help]