1 PACKAGE BODY GML_ORAUDTR_XMLP_PKG AS
2 /* $Header: ORAUDTRB.pls 120.1 2008/01/06 13:44:12 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 PRN_LINES := 0;
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
9 SELECT
10 ORGN_NAME
11 INTO CP_ORGN_NAME
12 FROM
13 SY_ORGN_MST
14 WHERE ORGN_CODE = P_DEFAULT_ORGN;
15 SELECT
16 USER_NAME
17 INTO CP_DEFAULT_USER
18 FROM
19 FND_USER
20 WHERE USER_ID = P_DEFAULT_USER;
21 RETURN (TRUE);
22 EXCEPTION
23 WHEN OTHERS THEN
24 RETURN TRUE;
25 END;
26 RETURN (TRUE);
27 END BEFOREREPORT;
28
29 FUNCTION AFTERPFORM RETURN BOOLEAN IS
30 DSORT3 VARCHAR2(20);
31 DSORT4 VARCHAR2(20);
32 MSORT3 VARCHAR2(20);
33 MSORT4 VARCHAR2(20);
34 BEGIN
35 PARAM_WHERE_CLAUSE := ' ';
36 PARAM_WHERE_CLAUSE1 := ' ';
37 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OH.ORGN_CODE = :P_DEFAULT_ORGN ';
38 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND OH.ORGN_CODE = :P_DEFAULT_ORGN ';
39 IF (P_FROM_SHIP_TO IS NOT NULL) THEN
40 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OP_CUST_MST.CUST_NO >= :P_FROM_SHIP_TO ';
41 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND OP_CUST_MST.CUST_NO >= :P_FROM_SHIP_TO ';
42 END IF;
43 IF (P_TO_SHIP_TO IS NOT NULL) THEN
44 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OP_CUST_MST.CUST_NO <= :P_TO_SHIP_TO ';
45 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND OP_CUST_MST.CUST_NO <= :P_TO_SHIP_TO ';
46 END IF;
47 IF (P_FROM_ORDER_NO IS NOT NULL) THEN
48 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OH.ORDER_NO >= :P_FROM_ORDER_NO ';
49 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND OH.ORDER_NO >= :P_FROM_ORDER_NO ';
50 END IF;
51 IF (P_TO_ORDER_NO IS NOT NULL) THEN
52 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OH.ORDER_NO <= :P_TO_ORDER_NO ';
53 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND OH.ORDER_NO <= :P_TO_ORDER_NO ';
54 END IF;
55 IF (P_FROM_ITEM_NO IS NOT NULL) THEN
56 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND IC_ITEM_MST.ITEM_NO >= :P_FROM_ITEM_NO ';
57 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND IC_ITEM_MST.ITEM_NO >= :P_FROM_ITEM_NO ';
58 END IF;
59 IF (P_TO_ITEM_NO IS NOT NULL) THEN
60 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND IC_ITEM_MST.ITEM_NO <= :P_TO_ITEM_NO ';
61 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND IC_ITEM_MST.ITEM_NO <= :P_TO_ITEM_NO ';
62 END IF;
63 IF (P_FROM_CHANGEDATE IS NOT NULL) THEN
64 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND AD.CREATION_DATE >= :P_FROM_CHANGEDATE ';
65 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND AD.CREATION_DATE >= :P_FROM_CHANGEDATE ';
66 END IF;
67 IF (P_TO_CHANGEDATE IS NOT NULL) THEN
68 P_TO_CHANGEDATE := TO_DATE((TO_CHAR(P_TO_CHANGEDATE
69 ,'MMDDYYYY') || '235959')
70 ,'MMDDYYYYHH24MISS');
71 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND AD.CREATION_DATE <= :P_TO_CHANGEDATE ';
72 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND AD.CREATION_DATE <= :P_TO_CHANGEDATE ';
73 END IF;
74 IF (P_FROM_OPER IS NOT NULL) THEN
75 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OD.CREATED_BY >= :P_FROM_OPER ';
76 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND AD.CREATED_BY >= :P_FROM_OPER ';
77 END IF;
78 IF (P_TO_OPER IS NOT NULL) THEN
79 PARAM_WHERE_CLAUSE := PARAM_WHERE_CLAUSE || ' AND OD.CREATED_BY <= :P_TO_OPER ';
80 PARAM_WHERE_CLAUSE1 := PARAM_WHERE_CLAUSE1 || ' AND AD.CREATED_BY <= :P_TO_OPER ';
81 END IF;
82 IF (P_SORT1 IS NULL OR P_SORT2 IS NULL) THEN
83 /*SRW.MESSAGE(1111
84 ,'INVALID OPTION')*/NULL;
85 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
86 END IF;
87 SELECT
88 DECODE(P_SORT3
89 ,'1'
90 ,'OD.LAST_UPDATE_DATE'
91 ,'2'
92 ,'IC_ITEM_MST.ITEM_NO'
93 ,'3'
94 ,'OD.CREATED_BY'
95 ,'4'
96 ,'OP_CUST_MST.CUST_NO')
97 INTO MSORT3
98 FROM
99 DUAL;
100 SELECT
101 DECODE(P_SORT4
102 ,'1'
103 ,'OD.LAST_UPDATE_DATE'
104 ,'2'
105 ,'IC_ITEM_MST.ITEM_NO'
106 ,'3'
107 ,'OD.CREATED_BY'
108 ,'4'
109 ,'OP_CUST_MST.CUST_NO')
110 INTO MSORT4
111 FROM
112 DUAL;
113 IF (P_SORT3 IS NOT NULL) THEN
114 P_SORTM := P_SORTM || ' , ' || MSORT3;
115 END IF;
116 IF (P_SORT4 IS NOT NULL) THEN
117 P_SORTM := P_SORTM || ' , ' || MSORT4;
118 END IF;
119 SELECT
120 DECODE(P_SORT3
121 ,'1'
122 ,'AD.LAST_UPDATE_DATE'
123 ,'2'
124 ,'IC_ITEM_MST.ITEM_NO'
125 ,'3'
126 ,'AD.CREATED_BY'
127 ,'4'
128 ,'OP_CUST_MST.CUST_NO')
129 INTO DSORT3
130 FROM
131 DUAL;
132 SELECT
133 DECODE(P_SORT4
134 ,'1'
135 ,'AD.LAST_UPDATE_DATE'
136 ,'2'
137 ,'IC_ITEM_MST.ITEM_NO'
138 ,'3'
139 ,'AD.CREATED_BY'
140 ,'4'
141 ,'OP_CUST_MST.CUST_NO')
142 INTO DSORT4
143 FROM
144 DUAL;
145 IF (P_SORT3 IS NOT NULL) THEN
146 P_SORTD := P_SORTD || ' , ' || DSORT3;
147 END IF;
148 IF (P_SORT4 IS NOT NULL) THEN
149 P_SORTD := P_SORTD || ' , ' || DSORT4;
150 END IF;
151 RETURN (TRUE);
152 END AFTERPFORM;
153
154 FUNCTION FMT_MASK(P_VALUE IN NUMBER
155 ,P_CUR IN CHARACTER) RETURN CHARACTER IS
156 V_DIGI_COUNT NUMBER;
157 V_VALUE NUMBER;
158 V_MASK_STR VARCHAR2(20);
159 V_PRECISION NUMBER;
160 BEGIN
161 V_MASK_STR := '9';
162 V_VALUE := P_VALUE;
163 BEGIN
164 SELECT
165 DECIMAL_PRECISION
166 INTO V_PRECISION
167 FROM
168 GL_CURR_MST
169 WHERE CURRENCY_CODE = P_CUR;
170 EXCEPTION
171 WHEN OTHERS THEN
172 V_PRECISION := 0;
173 END;
174 LOOP
175 IF ((V_VALUE) >= 10) THEN
176 V_VALUE := V_VALUE / 10;
177 V_MASK_STR := V_MASK_STR || '9';
178 ELSE
179 EXIT;
180 END IF;
181 END LOOP;
182 IF (V_PRECISION > 0) THEN
183 V_MASK_STR := V_MASK_STR || '.';
184 WHILE (V_PRECISION > 0) LOOP
185
186 V_PRECISION := V_PRECISION - 1;
187 V_MASK_STR := V_MASK_STR || '9';
188 END LOOP;
189 END IF;
190 RETURN NULL;
191 END FMT_MASK;
192
193 FUNCTION CF_SORT_DESCFORMULA RETURN VARCHAR2 IS
194 BEGIN
195 SELECT
196 MEANING
197 INTO CP_SORT_1
198 FROM
199 GEM_LOOKUP_VALUES
200 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORAUDTR'
201 AND LOOKUP_CODE = P_SORT1;
202 SELECT
203 MEANING
204 INTO CP_SORT_2
205 FROM
206 GEM_LOOKUP_VALUES
207 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORAUDTR'
208 AND LOOKUP_CODE = P_SORT2;
209 IF (P_SORT3 IS NOT NULL) THEN
210 SELECT
211 MEANING
212 INTO CP_SORT_3
213 FROM
214 GEM_LOOKUP_VALUES
215 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORAUDTR'
216 AND LOOKUP_CODE = P_SORT3;
217 END IF;
218 IF (P_SORT4 IS NOT NULL) THEN
219 SELECT
220 MEANING
221 INTO CP_SORT_4
222 FROM
223 GEM_LOOKUP_VALUES
224 WHERE LOOKUP_TYPE like 'GEMMS_OP_ORAUDTR'
225 AND LOOKUP_CODE = P_SORT4;
226 END IF;
227 RETURN NULL;
228 EXCEPTION
229 WHEN OTHERS THEN
230 RETURN NULL;
231 END CF_SORT_DESCFORMULA;
232
233 PROCEDURE ORUADTR_HEADER IS
234 BEGIN
235 NULL;
236 END ORUADTR_HEADER;
237 function F_CF_1(v_value1 number,billing_currency varchar2) return varchar2 is
238 v_str VARCHAR2(20) ;
239 xx NUMBER ;
240 v_value2 number;
241 BEGIN
242 v_value2:=v_value1;
243 begin
244 select decimal_precision into xx
245 from gl_curr_mst
246 where currency_code = billing_currency ;
247 Exception
248 when others then
249 xx := 0 ;
250 End ;
251 v_str := '';
252 LOOP
253 if ((v_value2) >= 10) then
254 v_value2 := v_value2/10;
255 v_str := v_str ||'9' ;
256 Else
257 v_str := v_str ||'0D' ;
258 Exit ;
259 End if ;
260 End LOOP ;
261 WHILE (xx > 0 ) LOOP
262 xx := xx - 1 ;
263 v_str := v_str ||'9' ;
264 END LOOP;
265 return(v_str);
266 end;
267
268 FUNCTION CF_USER_NAMEFORMULA(CREATED_BY_1 IN NUMBER) RETURN CHAR IS
269 V_USER_NAME VARCHAR2(100);
270 BEGIN
271 SELECT
272 USER_NAME
273 INTO V_USER_NAME
274 FROM
275 FND_USER
276 WHERE USER_ID = CREATED_BY_1;
277 RETURN (V_USER_NAME);
278 EXCEPTION
279 WHEN OTHERS THEN
280 RETURN (V_USER_NAME);
281 END CF_USER_NAMEFORMULA;
282
283 FUNCTION CF_USER_NAME2FORMULA(CREATED_BY2 IN NUMBER) RETURN CHAR IS
284 V_USER_NAME VARCHAR2(100);
285 BEGIN
286 SELECT
287 USER_NAME
288 INTO V_USER_NAME
289 FROM
290 FND_USER
291 WHERE USER_ID = CREATED_BY2;
292 RETURN (V_USER_NAME);
293 EXCEPTION
294 WHEN OTHERS THEN
295 RETURN (V_USER_NAME);
296 END CF_USER_NAME2FORMULA;
297
298 FUNCTION AFTERREPORT RETURN BOOLEAN IS
299 BEGIN
300 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
301 RETURN (TRUE);
302 END AFTERREPORT;
303
304 FUNCTION CP_ORGN_NAME_P RETURN VARCHAR2 IS
305 BEGIN
306 RETURN CP_ORGN_NAME;
307 END CP_ORGN_NAME_P;
308
309 FUNCTION CP_DEFAULT_USER_P RETURN VARCHAR2 IS
310 BEGIN
311 RETURN CP_DEFAULT_USER;
312 END CP_DEFAULT_USER_P;
313
314 FUNCTION CP_SORT_1_P RETURN VARCHAR2 IS
315 BEGIN
316 RETURN CP_SORT_1;
317 END CP_SORT_1_P;
318
319 FUNCTION CP_SORT_2_P RETURN VARCHAR2 IS
320 BEGIN
321 RETURN CP_SORT_2;
322 END CP_SORT_2_P;
323
324 FUNCTION CP_SORT_3_P RETURN VARCHAR2 IS
325 BEGIN
326 RETURN CP_SORT_3;
327 END CP_SORT_3_P;
328
329 FUNCTION CP_SORT_4_P RETURN VARCHAR2 IS
330 BEGIN
331 RETURN CP_SORT_4;
332 END CP_SORT_4_P;
333
334 END GML_ORAUDTR_XMLP_PKG;
335
[Home] [Help]