1 PACKAGE BODY GML_ORUARPJ_XMLP_PKG AS
2 /* $Header: ORUARPJB.pls 120.1 2008/01/06 13:44:32 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 SORT4 VARCHAR2(20);
5 DECODESQL VARCHAR(50);
6 BEGIN
7 PARAM_WHERE_CLAUSE := ' ';
8 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and hdr.orgn_code = :p_default_orgn ';
9 IF (P_FROM_WHSE IS NOT NULL) THEN
10 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and dtl.from_whse >= :p_from_whse ';
11 END IF;
12 IF (P_TO_WHSE IS NOT NULL) THEN
13 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and dtl.from_whse <= :p_to_whse ';
14 END IF;
15 IF (P_FROM_ORDER_NO IS NOT NULL) THEN
16 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and hdr.order_no >= :p_from_order_no ';
17 END IF;
18 IF (P_TO_ORDER_NO IS NOT NULL) THEN
19 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and hdr.order_no <= :p_to_order_no ';
20 END IF;
21 IF (P_FROM_ITEM_NO IS NOT NULL) THEN
22 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and item.item_no >= :p_from_item_no ';
23 END IF;
24 IF (P_TO_ITEM_NO IS NOT NULL) THEN
25 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and item.item_no <= :p_to_item_no ';
26 END IF;
27 IF (P_FROM_CUST_NO IS NOT NULL) THEN
28 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and cust.cust_no >= :p_from_cust_no ';
29 END IF;
30 IF (P_TO_CUST_NO IS NOT NULL) THEN
31 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and cust.cust_no <= :p_to_cust_no ';
32 END IF;
33 IF (P_SHIPDATE IS NOT NULL) THEN
34 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' and TRUNC(DTL.SCHED_SHIPDATE) <= TRUNC(:P_SHIPDATE) ';
35 END IF;
36 SELECT
37 DECODE(P_SORT_1
38 ,'5'
39 ,3
40 ,'1'
41 ,4
42 ,'3'
43 ,5
44 ,'2'
45 ,6
46 ,'4'
47 ,8)
48 INTO DECODESQL
49 FROM
50 DUAL;
51 P_SORT := 'ORDER BY ' || DECODESQL;
52 SELECT
53 DECODE(P_SORT_2
54 ,'5'
55 ,3
56 ,'1'
57 ,4
58 ,'3'
59 ,5
60 ,'2'
61 ,6
62 ,'4'
63 ,8)
64 INTO DECODESQL
65 FROM
66 DUAL;
67 P_SORT := P_SORT || ',' || DECODESQL;
68 SELECT
69 DECODE(P_SORT_3
70 ,'5'
71 ,3
72 ,'1'
73 ,4
74 ,'3'
75 ,5
76 ,'2'
77 ,6
78 ,'4'
79 ,8)
80 INTO DECODESQL
81 FROM
82 DUAL;
83 P_SORT := P_SORT || ',' || DECODESQL;
84 IF (P_SORT_4 IS NOT NULL) THEN
85 IF (P_SORT_4 = '5') THEN
86 P_SORT := P_SORT || ' ,DTL.FROM_WHSE ';
87 END IF;
88 IF (P_SORT_4 = '2') THEN
89 P_SORT := P_SORT || ' ,HDR.ORDER_NO ';
90 END IF;
91 IF (P_SORT_4 = '1') THEN
92 P_SORT := P_SORT || ' ,ITEM.ITEM_NO ';
93 END IF;
94 IF (P_SORT_4 = '4') THEN
95 P_SORT := P_SORT || ' ,CUST.CUST_NO ';
96 END IF;
97 IF (P_SORT_4 = '3') THEN
98 P_SORT := P_SORT || ' ,DTL.SCHED_SHIPDATE';
99 END IF;
100 ELSE
101 P_SORT := P_SORT;
102 END IF;
103 RETURN (TRUE);
104 END AFTERPFORM;
105
106 FUNCTION CF_1FORMULA(QC_GRADE_WANTED IN VARCHAR2
107 ,ITEM_ID_1 IN NUMBER
108 ,FROM_WHSE IN VARCHAR2
109 ,ITEM_UM IN VARCHAR2
110 ,ORDER_UM1 IN VARCHAR2) RETURN NUMBER IS
111 BEGIN
112 DECLARE
113 V_SHIP_QTY NUMBER;
114 V_COMMITTEDSALES_QTY NUMBER;
115 V_COMMITTEDPROD_QTY NUMBER;
116 V_INVENTORY_AVAIL NUMBER;
117 V_TEMP NUMBER;
118 BEGIN
119 IF (LTRIM(RTRIM(QC_GRADE_WANTED)) IS NULL) THEN
120 SELECT
121 SUM(SUMINV.ONHAND_SHIP_QTY),
122 SUM(SUMINV.COMMITTEDSALES_QTY),
123 SUM(SUMINV.COMMITTEDPROD_QTY)
124 INTO V_SHIP_QTY,V_COMMITTEDSALES_QTY,V_COMMITTEDPROD_QTY
125 FROM
126 IC_SUMM_INV SUMINV
127 WHERE SUMINV.ITEM_ID = ITEM_ID_1
128 AND SUMINV.WHSE_CODE = FROM_WHSE;
129 ELSE
130 SELECT
131 SUM(SUMINV.ONHAND_SHIP_QTY),
132 SUM(SUMINV.COMMITTEDSALES_QTY),
133 SUM(SUMINV.COMMITTEDPROD_QTY)
134 INTO V_SHIP_QTY,V_COMMITTEDSALES_QTY,V_COMMITTEDPROD_QTY
135 FROM
136 IC_SUMM_INV SUMINV
137 WHERE SUMINV.ITEM_ID = ITEM_ID_1
138 AND SUMINV.WHSE_CODE = FROM_WHSE
139 AND SUMINV.QC_GRADE = QC_GRADE_WANTED;
140 END IF;
141 V_TEMP := GMISYUM.SY_UOMCV(ITEM_ID_1
142 ,0
143 ,V_INVENTORY_AVAIL
144 ,ITEM_UM
145 ,ITEM_UM
146 ,V_INVENTORY_AVAIL
147 ,ORDER_UM1
148 ,0
149 ,0
150 ,NULL);
151 V_INVENTORY_AVAIL := V_SHIP_QTY - V_COMMITTEDSALES_QTY - V_COMMITTEDPROD_QTY;
152 IF (V_INVENTORY_AVAIL < 0) THEN
153 RETURN 0;
154 ELSE
155 RETURN V_INVENTORY_AVAIL;
156 END IF;
157 END;
158 RETURN NULL;
159 END CF_1FORMULA;
160
161 function F_CF_1(v_value1 number,billing_currency varchar2) return varchar2 is
162 v_str VARCHAR2(20) ;
163 xx NUMBER ;
164 v_value2 number;
165 BEGIN
166 v_value2:=v_value1;
167 begin
168 select decimal_precision into xx
169 from gl_curr_mst
170 where currency_code = billing_currency ;
171 Exception
172 when others then
173 xx := 0 ;
174 End ;
175 v_str := '';
176 LOOP
177 if ((v_value2) >= 10) then
178 v_value2 := v_value2/10;
179 v_str := v_str ||'9' ;
180 Else
181 v_str := v_str ||'0D' ;
182 Exit ;
183 End if ;
184 End LOOP ;
185 WHILE (xx > 0 ) LOOP
186 xx := xx - 1 ;
187 v_str := v_str ||'9' ;
188 END LOOP;
189 return(v_str);
190 end;
191
192 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
193 BEGIN
194 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
195 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
196 PRN_LINES := 0;
197 BEGIN
198 SELECT
199 ORGN_NAME
200 INTO CP_ORGN_NAME
201 FROM
202 SY_ORGN_MST
203 WHERE ORGN_CODE = P_DEFAULT_ORGN;
204 SELECT
205 USER_NAME
206 INTO CP_USER
207 FROM
208 FND_USER
209 WHERE P_DEFAULT_USER = USER_ID;
210 IF P_SORT_1 IS NOT NULL THEN
211 SELECT
212 MEANING
213 INTO CP_SORT_1
214 FROM
215 GEM_LOOKUPS
216 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
217 AND LOOKUP_CODE = P_SORT_1;
218 END IF;
219 IF P_SORT_2 IS NOT NULL THEN
220 SELECT
221 MEANING
222 INTO CP_SORT_2
223 FROM
224 GEM_LOOKUPS
225 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
226 AND LOOKUP_CODE = P_SORT_2;
227 END IF;
228 IF P_SORT_3 IS NOT NULL THEN
229 SELECT
230 MEANING
231 INTO CP_SORT_3
232 FROM
233 GEM_LOOKUPS
234 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
235 AND LOOKUP_CODE = P_SORT_3;
236 END IF;
237 IF P_SORT_4 IS NOT NULL THEN
238 SELECT
239 MEANING
240 INTO CP_SORT_4
241 FROM
242 GEM_LOOKUPS
243 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
244 AND LOOKUP_CODE = P_SORT_4;
245 END IF;
246 RETURN (TRUE);
247 EXCEPTION
248 WHEN OTHERS THEN
249 RETURN TRUE;
250 END;
251 RETURN (TRUE);
252 END BEFOREREPORT;
253
254 FUNCTION CF_SORT_DESCFORMULA RETURN VARCHAR2 IS
255 BEGIN
256 IF P_SORT_1 IS NOT NULL THEN
257 SELECT
258 MEANING
259 INTO CP_SORT_1
260 FROM
261 GEM_LOOKUP_VALUES
262 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
263 AND LOOKUP_CODE = P_SORT_1;
264 END IF;
265 IF P_SORT_2 IS NOT NULL THEN
266 SELECT
267 MEANING
268 INTO CP_SORT_2
269 FROM
270 GEM_LOOKUP_VALUES
271 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
272 AND LOOKUP_CODE = P_SORT_2;
273 END IF;
274 IF P_SORT_3 IS NOT NULL THEN
275 SELECT
276 MEANING
277 INTO CP_SORT_3
278 FROM
279 GEM_LOOKUP_VALUES
280 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
281 AND LOOKUP_CODE = P_SORT_3;
282 END IF;
283 IF P_SORT_4 IS NOT NULL THEN
284 SELECT
285 MEANING
286 INTO CP_SORT_4
287 FROM
288 GEM_LOOKUP_VALUES
289 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORUARPJ'
290 AND LOOKUP_CODE = P_SORT_4;
291 END IF;
292 RETURN NULL;
293 EXCEPTION
294 WHEN OTHERS THEN
295 RETURN NULL;
296 END CF_SORT_DESCFORMULA;
297
298 PROCEDURE GML_ORUARPJ_XMLP_PKG_HEADER IS
299 BEGIN
300 NULL;
301 END GML_ORUARPJ_XMLP_PKG_HEADER;
302
303 FUNCTION AFTERREPORT RETURN BOOLEAN IS
304 BEGIN
305 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
306 RETURN (TRUE);
307 END AFTERREPORT;
308
309 FUNCTION CP_ORGN_NAME_P RETURN VARCHAR2 IS
310 BEGIN
311 RETURN CP_ORGN_NAME;
312 END CP_ORGN_NAME_P;
313
314 FUNCTION CP_USER_P RETURN VARCHAR2 IS
315 BEGIN
316 RETURN CP_USER;
317 END CP_USER_P;
318
319 FUNCTION CP_SORT_1_P RETURN VARCHAR2 IS
320 BEGIN
321 RETURN CP_SORT_1;
322 END CP_SORT_1_P;
323
324 FUNCTION CP_SORT_2_P RETURN VARCHAR2 IS
325 BEGIN
326 RETURN CP_SORT_2;
327 END CP_SORT_2_P;
328
329 FUNCTION CP_SORT_3_P RETURN VARCHAR2 IS
330 BEGIN
331 RETURN CP_SORT_3;
332 END CP_SORT_3_P;
333
334 FUNCTION CP_SORT_4_P RETURN VARCHAR2 IS
335 BEGIN
336 RETURN CP_SORT_4;
337 END CP_SORT_4_P;
338
339 END GML_ORUARPJ_XMLP_PKG;
340
[Home] [Help]