1 PACKAGE BODY INV_INVTRACS_XMLP_PKG AS
2 /* $Header: INVTRACSB.pls 120.2 2008/01/08 06:33:08 dwkrishn noship $ */
3 /* $Header: INVTRACSB.pls 120.2 2008/01/08 06:33:08 dwkrishn noship $ */
4 FUNCTION SORT_COLFORMULA RETURN VARCHAR2 IS
5 BEGIN
6 DECLARE
7 BATCH VARCHAR2(50);
8 BEGIN
9 IF P_SORT_ID = 2 THEN
10 RETURN ('mtt.transaction_type_name');
11 ELSE
12 IF P_SORT_ID = 3 THEN
13 RETURN ('mtst.transaction_source_type_name');
14 ELSE
15 IF P_SORT_ID = 4 THEN
16 BATCH := 'decode(mta.gl_batch_id,-1,''' || '' || ''')';
17 RETURN (BATCH);
18 ELSE
19 NULL;
20 END IF;
21 END IF;
22 END IF;
23 END;
24 RETURN NULL;
25 END SORT_COLFORMULA;
26 FUNCTION GET_DEBIT(NET_ACTIVITY IN NUMBER) RETURN NUMBER IS
27 BEGIN
28 IF NET_ACTIVITY > 0 THEN
29 RETURN (NET_ACTIVITY);
30 ELSE
31 RETURN (0);
32 END IF;
33 RETURN NULL;
34 END GET_DEBIT;
35 FUNCTION GET_CREDIT(NET_ACTIVITY IN NUMBER) RETURN NUMBER IS
36 BEGIN
37 IF NET_ACTIVITY < 0 THEN
38 RETURN (NET_ACTIVITY);
39 ELSE
40 RETURN (0);
41 END IF;
42 RETURN NULL;
43 END GET_CREDIT;
44 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
45 BEGIN
46 BEGIN
47 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
48 -- P_TO_DATE_DSP := TO_CHAR(P_TO_DATE,'DD-MON-YYYY');
49 --P_FROM_DATE_DSP := TO_CHAR(P_FROM_DATE,'DD-MON-YYYY');
50
51
52 P_FROM_DATE_DSP := TO_CHAR(TO_DATE(P_FROM_DATE
53 ,'YYYY/MM/DD HH24:MI:SS')
54 ,'DD-MON-RRRR');
55 P_TO_DATE_DSP := TO_CHAR(TO_DATE(P_TO_DATE
56 ,'YYYY/MM/DD HH24:MI:SS')
57 ,'DD-MON-RRRR');
58
59 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
60 EXCEPTION
61 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
62 /*SRW.MESSAGE(1
63 ,'Error in SRWINIT')*/NULL;
64 RAISE;
65 END;
66 BEGIN
67 IF P_SORT_ID = 1 THEN
68 NULL;
69 ELSE
70 P_ITEM_FLEX := 'NULL';
71 END IF;
72 EXCEPTION
73 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
74 /*SRW.MESSAGE(2
75 ,'Error in MSTK')*/NULL;
76 RAISE;
77 END;
78 BEGIN
79 NULL;
80 EXCEPTION
81 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
82 /*SRW.MESSAGE(3
83 ,'Error in GL#')*/NULL;
84 RAISE;
85 END;
86 BEGIN
87 IF P_ACCT_LO IS NOT NULL OR P_ACCT_HI IS NOT NULL THEN
88 NULL;
89 ELSE
90 NULL;
91 END IF;
92 EXCEPTION
93 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
94 /*SRW.MESSAGE(4
95 ,'Error in GL#')*/NULL;
96 RAISE;
97 END;
98 RETURN (TRUE);
99 END BEFOREREPORT;
100 FUNCTION AFTERREPORT RETURN BOOLEAN IS
101 BEGIN
102 BEGIN
103 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
104 EXCEPTION
105 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
106 /*SRW.MESSAGE(1
107 ,'Error in SRWEXIT')*/NULL;
108 END;
109 RETURN (TRUE);
110 END AFTERREPORT;
111 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
112 BEGIN
113 RETURN ('(' || R_CURRENCY_CODE || ')');
114 END C_CURRENCY_CODEFORMULA;
115 FUNCTION C_GL_BATCH_WHEREFORMULA RETURN VARCHAR2 IS
116 BEGIN
117 IF P_GL_BATCH_ID IS NOT NULL THEN
118 RETURN ('and mta.gl_batch_id = ogb.gl_batch_id (+)
119 and mta.gl_batch_id = ' || TO_CHAR(P_GL_BATCH_ID));
120 ELSE
121 IF P_SORT_ID = 4 AND P_GL_BATCH_ID IS NULL THEN
122 RETURN ('and mta.gl_batch_id = ogb.gl_batch_id (+)');
123 ELSE
124 return (' ');
125 END IF;
126 END IF;
127 RETURN NULL;
128 END C_GL_BATCH_WHEREFORMULA;
129 FUNCTION C_GL_BATCH_FROMFORMULA RETURN VARCHAR2 IS
130 BEGIN
131 IF P_SORT_ID = 4 OR P_GL_BATCH_ID IS NOT NULL THEN
132 RETURN (', org_gl_batches ogb');
133 ELSE
134 return (' ');
135 END IF;
136 RETURN NULL;
137 END C_GL_BATCH_FROMFORMULA;
138 FUNCTION C_TYPE_FROMFORMULA RETURN VARCHAR2 IS
139 BEGIN
140 IF P_SORT_ID = 2 THEN
141 RETURN (', mtl_transaction_types mtt');
142 ELSE
143 IF P_SORT_ID = 3 THEN
144 RETURN (', mtl_txn_source_types mtst');
145 ELSE
146 return (' ');
147 END IF;
148 END IF;
149 RETURN NULL;
150 END C_TYPE_FROMFORMULA;
151 FUNCTION C_TYPE_WHEREFORMULA RETURN VARCHAR2 IS
152 BEGIN
153 IF P_SORT_ID = 2 THEN
154 RETURN ('and mmt.transaction_type_id = mtt.transaction_type_id');
155 ELSE
156 IF P_SORT_ID = 3 THEN
157 RETURN ('and mtst.transaction_source_type_id = mmt.transaction_source_type_id');
158 ELSE
159 return (' ');
160 END IF;
161 END IF;
162 RETURN NULL;
163 END C_TYPE_WHEREFORMULA;
164 FUNCTION C_ITEM_FROMFORMULA RETURN VARCHAR2 IS
165 BEGIN
166 IF P_SORT_ID = 1 THEN
167 RETURN ('mtl_system_items msi,');
168 ELSE
169 RETURN ('/* Do not select from mtl_system_items */');
170 END IF;
171 RETURN NULL;
172 END C_ITEM_FROMFORMULA;
173 FUNCTION C_ITEM_WHEREFORMULA RETURN VARCHAR2 IS
174 BEGIN
175 IF P_SORT_ID = 1 THEN
176 RETURN ('and mta.inventory_item_id = msi.inventory_item_id and msi.organization_id
177 = :P_ORG_ID ');
178 ELSE
179 NULL;
180 END IF;
181 RETURN ' ';
182 END C_ITEM_WHEREFORMULA;
183 FUNCTION C_BATCH_DESCFORMULA RETURN VARCHAR2 IS
184 BEGIN
185 IF P_SORT_ID = 4 OR P_GL_BATCH_ID IS NOT NULL THEN
186 RETURN ('ogb.gl_batch_id, ogb.description');
187 ELSE
188 NULL;
189 END IF;
190 RETURN NULL;
191 END C_BATCH_DESCFORMULA;
192 FUNCTION C_ACCT_PADFORMULA(C_ACCT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
193 BEGIN
194 RETURN (C_ACCT_PAD);
195 END C_ACCT_PADFORMULA;
196 FUNCTION C_SORT_PADFORMULA(C_SORT_PAD IN VARCHAR2
197 ,SORT_OPTION IN VARCHAR2) RETURN VARCHAR2 IS
198 BEGIN
199 IF P_SORT_ID = 1 THEN
200 RETURN (C_SORT_PAD);
201 ELSE
202 RETURN (SORT_OPTION);
203 END IF;
204 RETURN NULL;
205 END C_SORT_PADFORMULA;
206 FUNCTION C_DATE_WHEREFORMULA RETURN VARCHAR2 IS
207 L_FORMAT VARCHAR2(100):='DD-MON-YYYY HH24:MI:SS';
208 BEGIN
209 IF P_FROM_DATE_T IS NOT NULL AND P_TO_DATE_T IS NOT NULL THEN
210 RETURN ('and mta.transaction_date between ' || 'to_date(''' || P_FROM_DATE_T || ''',''' ||L_FORMAT|| ''')' || ' and ' || 'to_date(''' || P_TO_DATE_T || ''',''' ||L_FORMAT|| ''')');
211 ELSE
212 IF P_FROM_DATE_T IS NOT NULL AND P_TO_DATE_T IS NULL THEN
213 RETURN ('and mta.transaction_date >= ' || 'to_date(''' || P_FROM_DATE_T || ''',''' ||L_FORMAT|| ''')');
214 ELSE
215 IF P_FROM_DATE_T IS NULL AND P_TO_DATE_T IS NOT NULL THEN
216 RETURN ('and mta.transaction_date <= ' || 'to_date(''' || P_TO_DATE_T || ''',''' ||L_FORMAT|| ''')');
217 ELSE
218 RETURN (' ');
219 END IF;
220 END IF;
221 END IF;
222 RETURN NULL;
223 END C_DATE_WHEREFORMULA;
224 FUNCTION C_BATCH_SELFORMULA RETURN VARCHAR2 IS
225 BEGIN
226 IF P_SORT_ID = 4 OR P_GL_BATCH_ID IS NOT NULL THEN
227 RETURN ('ogb.gl_batch_id gl_batch_number, ogb.description Batch_desc,');
228 ELSE
229 RETURN ('0 gl_batch_number, ''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'' Batch_desc,');
230 END IF;
231 RETURN NULL;
232 END C_BATCH_SELFORMULA;
233 FUNCTION AFTERPFORM RETURN BOOLEAN IS
234 L_FORMAT VARCHAR2(100):='DD-MON-YYYY HH24:MI:SS';
235 BEGIN
236
237 P_FROM_DATE_T := TO_CHAR(TO_DATE(P_FROM_DATE
238 ,'YYYY/MM/DD HH24:MI:SS')
239 ,'DD-MON-RRRR HH24:MI:SS');
240 P_TO_DATE_T := TO_CHAR(TO_DATE(P_TO_DATE
241 ,'YYYY/MM/DD HH24:MI:SS')
242 ,'DD-MON-RRRR');
243 IF (P_TO_DATE_T IS NOT NULL) THEN
244 P_TO_DATE_T := TO_CHAR(TO_DATE(P_TO_DATE_T || ' 23:59:59'
245 ,'DD-MON-RRRR HH24:MI:SS')
246 ,'DD-MON-RRRR HH24:MI:SS');
247 ELSE
248 P_TO_DATE_T := TO_CHAR(TO_DATE(P_TO_DATE_T
249 ,'DD-MON-RRRR HH24:MI:SS')
250 ,'DD-MON-RRRR HH24:MI:SS');
251 END IF;
252 /*SRW.MESSAGE(1112
253 ,'from date: ' || P_FROM_DATE || ' to date: ' || P_TO_DATE)*/NULL;
254 BEGIN
255 IF P_FROM_DATE_T IS NOT NULL AND P_TO_DATE_T IS NOT NULL THEN
256 P_DATE_RANGE := 'and (mta.transaction_date) between ' || 'to_date(''' || P_FROM_DATE_T || ''','''|| L_FORMAT ||''')' || ' and ' || 'to_date(''' || P_TO_DATE_T || ''',''' ||L_FORMAT ||''')';
257 ELSIF P_FROM_DATE_T IS NOT NULL AND P_TO_DATE_T IS NULL THEN
258 P_DATE_RANGE := 'and (mta.transaction_date) >= ' || 'to_date(''' || P_FROM_DATE_T || ''','''|| L_FORMAT|| ''')';
259 ELSIF P_FROM_DATE_T IS NULL AND P_TO_DATE_T IS NOT NULL THEN
260 P_DATE_RANGE := 'and (mta.transaction_date) <= ' || 'to_date(''' || P_TO_DATE_T || ''',''' ||L_FORMAT|| ''')';
261 ELSE
262 P_DATE_RANGE := ' ';
263 END IF;
264 /*SRW.MESSAGE(1113
265 ,'date range: ' || P_DATE_RANGE)*/NULL;
266 END;
267 RETURN (TRUE);
268 END AFTERPFORM;
269 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
270 BEGIN
271 RETURN (TRUE);
272 END BETWEENPAGE;
273 END INV_INVTRACS_XMLP_PKG;
274
[Home] [Help]