1 PACKAGE BODY FUN_TRX_TYPES_PUB AS
2 -- $Header: funtrxutilb.pls 120.15.12010000.4 2009/11/17 07:41:05 srampure ship $
3
4 /****************************************************************
5 * PROCEDURE get_trx_type_by_id *
6 * *
7 * This procedure returns status of a transaction and the *
8 * invoicing option for a given trx_type_id *
9 ****************************************************************/
10
11 PROCEDURE get_trx_type_by_id (
12 p_trx_type_id in number,
13 x_trx_type_code out NOCOPY varchar2,
14 x_need_invoice out NOCOPY varchar2,
15 x_enabled out NOCOPY varchar2
16 ) IS
17
18 CURSOR c_trx_type_id IS
19 select trx_type_code ,
20 allow_invoicing_flag ,
21 enabled_flag
22 from FUN_TRX_TYPES_VL
23 where trx_type_id = p_trx_type_id;
24
25 l_trx_type_id c_trx_type_id%rowtype;
26
27 BEGIN
28
29 OPEN c_trx_type_id;
30 FETCH c_trx_type_id into l_trx_type_id;
31
32 IF c_trx_type_id%NOTFOUND THEN
33 close c_trx_type_id;
34 x_trx_type_code :=null;
35 x_need_invoice :=null;
36 x_enabled :=null;
37
38 END IF;
39
40 x_trx_type_code :=l_trx_type_id.trx_type_code;
41 x_need_invoice :=l_trx_type_id.allow_invoicing_flag;
42 x_enabled :=l_trx_type_id.enabled_flag;
43
44 close c_trx_type_id;
45
46 END;
47
48
49 /****************************************************************
50 * PROCEDURE :get_trx_type_code *
54 ****************************************************************/
51 * *
52 * This procedure returns the status of a transaction and *
53 * invoicing option for a given transaction type code *
55
56 PROCEDURE get_trx_type_by_code (
57 p_trx_type_code in varchar2,
58 x_trx_type_id out NOCOPY number,
59 x_need_invoice out NOCOPY varchar2,
60 x_enabled out NOCOPY varchar2
61 ) IS
62
63 CURSOR c_trx_type_code IS
64 select trx_type_id ,
65 allow_invoicing_flag ,
66 enabled_flag
67 from FUN_TRX_TYPES_VL
68 where trx_type_code = p_trx_type_code;
69
70 l_trx_type_code c_trx_type_code%rowtype;
71
72 BEGIN
73 OPEN c_trx_type_code;
74 FETCH c_trx_type_code into l_trx_type_code;
75 IF c_trx_type_code%NOTFOUND THEN
76 close c_trx_type_code;
77 x_trx_type_id :=null;
78 x_need_invoice :=null;
79 x_enabled :=null;
80 END IF;
81
82 x_trx_type_id :=l_trx_type_code.trx_type_id;
83 x_need_invoice :=l_trx_type_code.allow_invoicing_flag;
84 x_enabled :=l_trx_type_code.enabled_flag;
85
86 close c_trx_type_code;
87
88 END;
89
90 /****************************************************************
91 * FUNCTION : is_trx_type_manual_approve *
92 * *
93 * This function returns the manual approval option for a *
94 * transaction type name given trx_type_id *
95 ****************************************************************/
96
97 FUNCTION is_trx_type_manual_approve
98 (
99 p_trx_type_id in number
100 ) RETURN VARCHAR2 IS
101
102 CURSOR c_trx_type_manual_approve is
103 select manual_approve_flag
104 from FUN_TRX_TYPES_VL
105 where trx_type_id = p_trx_type_id;
106
107 x_manual_approve varchar2(100);
108
109 BEGIN
110
111 OPEN c_trx_type_manual_approve;
112 FETCH c_trx_type_manual_approve into x_manual_approve;
113 IF c_trx_type_manual_approve%NOTFOUND THEN
114 close c_trx_type_manual_approve;
115 return null;
116 END IF;
117
118 close c_trx_type_manual_approve;
119
120 RETURN x_manual_approve;
121
122
123 END;
124
125
126 /****************************************************************
127 * PROCEDURE : get_trx_type_map *
128 * *
129 * This procedure returns the mapping details of transation*
130 * type name given the org it is associated with and the id*
131 ****************************************************************/
132 -- <bug 3520961>
133 -- The whole code of get_trx_type_map has been changed, remove
134 -- the use of cursor and change the defaulting logic
135 -- Added parameter p_trx_date for bug 5176112
136
137 PROCEDURE get_trx_type_map (
138 p_org_id in number,
139 p_trx_type_id in number,
140 p_trx_date in date,
141 p_trx_id in number,
142 x_memo_line_id out NOCOPY number,
143 x_memo_line_name out NOCOPY varchar2,
144 x_ar_trx_type_id out NOCOPY number,
145 x_ar_trx_type_name out NOCOPY varchar2,
146 x_default_term_id out NOCOPY number
147 ) IS
148 --ER: 8288979
149 l_init_amount_dr number;
150 l_init_amount_cr number;
151 l_trx_type varchar2(4);
152
153 CURSOR c_get_trx_amount IS
154 SELECT INIT_AMOUNT_DR, INIT_AMOUNT_CR
155 FROM fun_trx_headers
156 WHERE TRX_ID = p_trx_id;
157
158 CURSOR c_get_map IS
159 SELECT m.memo_line_id,
160 m.name,
161 ar.cust_trx_type_id,
162 ar.name ,
163 Nvl(ar.default_term, 4)
164 FROM FUN_TRX_TYPE_AR_MAPS tm,
165 RA_CUST_TRX_TYPES_ALL ar,
166 AR_MEMO_LINES_ALL_VL m
167 WHERE tm.trx_type_id = p_trx_type_id AND
168 tm.org_id = p_org_id AND
169 tm.ar_trx_type_id = ar.cust_trx_type_id AND
170 tm.memo_line_id = m.memo_line_id AND
171 tm.org_id = m.org_id AND
172 ar.org_id = m.org_id AND
173 p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date) AND
174 p_trx_date BETWEEN Nvl(m.start_date, p_trx_date) AND Nvl(m.end_date, p_trx_date);
175 -- Bug: 9126518
176 CURSOR c_get_cm_map IS
177 SELECT m.memo_line_id,
178 m.name,
179 ar.cust_trx_type_id,
180 ar.name ,
181 null default_term_id
182 FROM FUN_TRX_TYPE_AR_MAPS tm,
183 RA_CUST_TRX_TYPES_ALL ar,
184 AR_MEMO_LINES_ALL_VL m
185 WHERE tm.trx_type_id = p_trx_type_id AND
186 tm.org_id = p_org_id AND
187 tm.ar_cm_trx_type_id = ar.cust_trx_type_id AND
188 tm.memo_line_id = m.memo_line_id AND
189 tm.org_id = m.org_id AND
190 ar.org_id = m.org_id AND
191 p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date) AND
192 p_trx_date BETWEEN Nvl(m.start_date, p_trx_date) AND Nvl(m.end_date, p_trx_date);
193
194
195 CURSOR c_get_def_map IS
196 SELECT m.memo_line_id,
197 m.name,
198 ar.cust_trx_type_id,
199 ar.name,
200 4 default_term_id
201 FROM fun_system_options fun,
202 RA_CUST_TRX_TYPES_ALL ar,
203 AR_MEMO_LINES_ALL_VL m
204 WHERE fun.default_memo_line_id = m.memo_line_id
205 AND fun.default_ar_trx_type_id = ar.cust_trx_type_id
206 AND ar.org_id = m.org_id
207 AND ar.org_id = p_org_id
211 CURSOR c_get_def_cm_map IS
208 AND p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date)
209 AND p_trx_date BETWEEN Nvl(m.start_date, p_trx_date) AND Nvl(m.end_date, p_trx_date);
210
212 SELECT m.memo_line_id,
213 m.name,
214 ar.cust_trx_type_id,
215 ar.name,
216 null default_term_id
217 FROM fun_system_options fun,
218 RA_CUST_TRX_TYPES_ALL ar,
219 AR_MEMO_LINES_ALL_VL m
220 WHERE fun.default_memo_line_id = m.memo_line_id
221 AND fun.default_cm_trx_type_id = ar.cust_trx_type_id
222 AND ar.org_id = m.org_id
223 AND ar.org_id = p_org_id
224 AND p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date)
225 AND p_trx_date BETWEEN Nvl(m.start_date, p_trx_date) AND Nvl(m.end_date, p_trx_date);
226
227 BEGIN
228
229 l_trx_type := 'INV';
230 OPEN c_get_trx_amount;
231 FETCH c_get_trx_amount INTO l_init_amount_dr,
232 l_init_amount_cr;
233 IF(l_init_amount_dr is null) THEN
234 l_init_amount_dr := 0;
235 END IF;
236 IF(l_init_amount_cr is null) THEN
237 l_init_amount_cr := 0;
238 END IF;
239 -- Credit memo transaction
240 if( l_init_amount_dr < 0 OR l_init_amount_cr > 0) THEN
241 l_trx_type := 'CM';
242 END IF;
243
244 IF( l_trx_type = 'INV') THEN
245 OPEN c_get_map;
246 FETCH c_get_map INTO x_memo_line_id,
247 x_memo_line_name,
248 x_ar_trx_type_id,
249 x_ar_trx_type_name ,
250 x_default_term_id;
251 CLOSE c_get_map;
252 IF x_memo_line_id IS NULL OR x_memo_line_name IS NULL
253 OR x_ar_trx_type_id IS NULL OR x_ar_trx_type_name IS NULL
254 THEN
255 OPEN c_get_def_map;
256 FETCH c_get_def_map INTO x_memo_line_id,
257 x_memo_line_name,
258 x_ar_trx_type_id,
259 x_ar_trx_type_name ,
260 x_default_term_id;
261 CLOSE c_get_def_map;
262
263 END IF;
264 ELSE
265 --ER: 8288979. This is for credit memo transaction
266 OPEN c_get_cm_map;
267 FETCH c_get_cm_map INTO x_memo_line_id,
268 x_memo_line_name,
269 x_ar_trx_type_id,
270 x_ar_trx_type_name ,
271 x_default_term_id;
272 CLOSE c_get_cm_map;
273 IF x_memo_line_id IS NULL OR x_memo_line_name IS NULL
274 OR x_ar_trx_type_id IS NULL OR x_ar_trx_type_name IS NULL
275 THEN
276 OPEN c_get_def_cm_map;
277 FETCH c_get_def_cm_map INTO x_memo_line_id,
278 x_memo_line_name,
279 x_ar_trx_type_id,
280 x_ar_trx_type_name ,
281 x_default_term_id;
282 CLOSE c_get_def_cm_map;
283
284 END IF;
285
286 END IF;
287
288
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 RAISE;
293
294 END get_trx_type_map;
295
296
297 /****************************************************************
298 * FUNCTION : get_ar_trx_creation_sign *
299 * *
300 * For a given intercompany transaction type, this function*
301 * returns the transaction creation sign of the associated *
302 * AR transaction type. The input to the function is the *
303 * intercompany transaction type, organization id and the *
304 * transaction batch date *
305 * *
306 ****************************************************************/
307 FUNCTION get_ar_trx_creation_sign (
308 p_org_id in number,
309 p_trx_type_id in number,
310 p_trx_date in date,
311 p_trx_type in varchar2
312 ) RETURN NUMBER IS
313
314 --ER: 8288979
315 CURSOR c_get_sign (p_trx_type_id NUMBER,
316 p_org_id NUMBER,
317 p_trx_date DATE)
318 IS
319 SELECT decode(ar.creation_sign,'P',+1,'N',-1,'A',0)
320 FROM FUN_TRX_TYPE_AR_MAPS tm,
321 RA_CUST_TRX_TYPES_ALL ar
322 WHERE tm.trx_type_id = p_trx_type_id AND
323 tm.org_id = p_org_id AND
324 tm.org_id = ar.org_id AND
325 tm.ar_trx_type_id = ar.cust_trx_type_id AND
326 p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date);
327
328 CURSOR c_get_cm_sign (p_trx_type_id NUMBER,
329 p_org_id NUMBER,
330 p_trx_date DATE)
331 IS
332 SELECT decode(ar.creation_sign,'P',+1,'N',-1,'A',0)
333 FROM FUN_TRX_TYPE_AR_MAPS tm,
334 RA_CUST_TRX_TYPES_ALL ar
335 WHERE tm.trx_type_id = p_trx_type_id AND
336 tm.org_id = p_org_id AND
337 tm.org_id = ar.org_id AND
338 tm.ar_cm_trx_type_id = ar.cust_trx_type_id AND
339 p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date);
340
341 CURSOR c_get_def ( p_org_id NUMBER,
342 p_trx_date DATE)
343 IS
344 SELECT decode(ar.creation_sign,'P',+1,'N',-1,'A',0)
345 FROM fun_system_options fun,
346 RA_CUST_TRX_TYPES_ALL ar
347 WHERE fun.default_ar_trx_type_id = ar.cust_trx_type_id
348 AND ar.org_id = p_org_id
349 AND p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date);
350
351 CURSOR c_get_def_cm ( p_org_id NUMBER,
352 p_trx_date DATE)
353 IS
354 SELECT decode(ar.creation_sign,'P',+1,'N',-1,'A',0)
355 FROM fun_system_options fun,
356 RA_CUST_TRX_TYPES_ALL ar
357 WHERE fun.default_cm_trx_type_id = ar.cust_trx_type_id
358 AND ar.org_id = p_org_id
359 AND p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date);
360
361 x_ar_creation_sign number;
362 BEGIN
363 IF( p_trx_type = 'INV') THEN
364 OPEN c_get_sign (p_trx_type_id, p_org_id, p_trx_date);
365 FETCH c_get_sign INTO x_ar_creation_sign;
366
367 IF c_get_sign%NOTFOUND
368 THEN
369 OPEN c_get_def (p_org_id, p_trx_date);
370 FETCH c_get_def INTO x_ar_creation_sign;
371 CLOSE c_get_def;
372 END IF;
373
374 CLOSE c_get_sign;
375 ELSE
376 --ER: 8288979. This is for credit memo transaction
377 OPEN c_get_cm_sign (p_trx_type_id, p_org_id, p_trx_date);
378 FETCH c_get_cm_sign INTO x_ar_creation_sign;
379
380 IF c_get_cm_sign%NOTFOUND
381 THEN
382 OPEN c_get_def_cm (p_org_id, p_trx_date);
383 FETCH c_get_def_cm INTO x_ar_creation_sign;
384 CLOSE c_get_def_cm;
385 END IF;
386 CLOSE c_get_cm_sign;
387 END IF;
388 RETURN x_ar_creation_sign;
389
390 EXCEPTION
391 WHEN OTHERS THEN
392 RETURN NULL;
393 END get_ar_trx_creation_sign;
394
395 END FUN_TRX_TYPES_PUB;
[Home] [Help]