1 PACKAGE BODY FUN_TRX_TYPES_PUB AS
2 -- $Header: funtrxutilb.pls 120.15 2006/07/03 09:46:15 bsilveir noship $
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 *
51 * *
52 * This procedure returns the status of a transaction and *
53 * invoicing option for a given transaction type code *
54 ****************************************************************/
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 x_memo_line_id out NOCOPY number,
142 x_memo_line_name out NOCOPY varchar2,
143 x_ar_trx_type_id out NOCOPY number,
144 x_ar_trx_type_name out NOCOPY varchar2,
145 x_default_term_id out NOCOPY number
146 ) IS
147
148 CURSOR c_get_map IS
149 SELECT m.memo_line_id,
150 m.name,
151 ar.cust_trx_type_id,
152 ar.name ,
153 Nvl(ar.default_term, 4)
154 FROM FUN_TRX_TYPE_AR_MAPS tm,
155 RA_CUST_TRX_TYPES_ALL ar,
156 AR_MEMO_LINES_ALL_VL m
157 WHERE tm.trx_type_id = p_trx_type_id AND
158 tm.org_id = p_org_id AND
159 tm.ar_trx_type_id = ar.cust_trx_type_id AND
160 tm.memo_line_id = m.memo_line_id AND
161 tm.org_id = m.org_id AND
162 ar.org_id = m.org_id AND
163 p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date) AND
164 p_trx_date BETWEEN Nvl(m.start_date, p_trx_date) AND Nvl(m.end_date, p_trx_date);
165
166
167 CURSOR c_get_def_map IS
168 SELECT m.memo_line_id,
169 m.name,
170 ar.cust_trx_type_id,
171 ar.name,
172 4 default_term_id
173 FROM fun_system_options fun,
174 RA_CUST_TRX_TYPES_ALL ar,
175 AR_MEMO_LINES_ALL_VL m
176 WHERE fun.default_memo_line_id = m.memo_line_id
177 AND fun.default_ar_trx_type_id = ar.cust_trx_type_id
178 AND ar.org_id = m.org_id
179 AND ar.org_id = p_org_id
180 AND p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date)
181 AND p_trx_date BETWEEN Nvl(m.start_date, p_trx_date) AND Nvl(m.end_date, p_trx_date);
182
183 BEGIN
184
185 OPEN c_get_map;
186 FETCH c_get_map INTO x_memo_line_id,
187 x_memo_line_name,
188 x_ar_trx_type_id,
189 x_ar_trx_type_name ,
190 x_default_term_id;
191 CLOSE c_get_map;
192
193 IF x_memo_line_id IS NULL OR x_memo_line_name IS NULL
194 OR x_ar_trx_type_id IS NULL OR x_ar_trx_type_name IS NULL
195 THEN
196 OPEN c_get_def_map;
197 FETCH c_get_def_map INTO x_memo_line_id,
198 x_memo_line_name,
199 x_ar_trx_type_id,
200 x_ar_trx_type_name ,
201 x_default_term_id;
202 CLOSE c_get_def_map;
203
204 END IF;
205
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 RAISE;
210
211 END get_trx_type_map;
212
213
214 /****************************************************************
215 * FUNCTION : get_ar_trx_creation_sign *
216 * *
217 * For a given intercompany transaction type, this function*
218 * returns the transaction creation sign of the associated *
219 * AR transaction type. The input to the function is the *
220 * intercompany transaction type, organization id and the *
221 * transaction batch date *
222 * *
223 ****************************************************************/
224
225 FUNCTION get_ar_trx_creation_sign (
226 p_org_id in number,
227 p_trx_type_id in number,
228 p_trx_date in date
229 ) RETURN NUMBER IS
230
231 CURSOR c_get_sign (p_trx_type_id NUMBER,
232 p_org_id NUMBER,
233 p_trx_date DATE)
234 IS
235 SELECT decode(ar.creation_sign,'P',+1,'N',-1,'A',0)
236 FROM FUN_TRX_TYPE_AR_MAPS tm,
237 RA_CUST_TRX_TYPES_ALL ar
238 WHERE tm.trx_type_id = p_trx_type_id AND
239 tm.org_id = p_org_id AND
240 tm.org_id = ar.org_id AND
241 tm.ar_trx_type_id = ar.cust_trx_type_id AND
242 p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date);
243
244 CURSOR c_get_def ( p_org_id NUMBER,
245 p_trx_date DATE)
246 IS
247 SELECT decode(ar.creation_sign,'P',+1,'N',-1,'A',0)
248 FROM fun_system_options fun,
249 RA_CUST_TRX_TYPES_ALL ar
250 WHERE fun.default_ar_trx_type_id = ar.cust_trx_type_id
251 AND ar.org_id = p_org_id
252 AND p_trx_date BETWEEN Nvl(ar.start_date,p_trx_date) AND Nvl(ar.end_date, p_trx_date);
253
254 x_ar_creation_sign number;
255 BEGIN
256
257 OPEN c_get_sign (p_trx_type_id, p_org_id, p_trx_date);
258 FETCH c_get_sign INTO x_ar_creation_sign;
259
260 IF c_get_sign%NOTFOUND
261 THEN
262 OPEN c_get_def (p_org_id, p_trx_date);
263 FETCH c_get_def INTO x_ar_creation_sign;
264 CLOSE c_get_def;
265 END IF;
266
267 CLOSE c_get_sign;
268
269 RETURN x_ar_creation_sign;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 RETURN NULL;
274 END get_ar_trx_creation_sign;
275
276
277
278 END FUN_TRX_TYPES_PUB;
[Home] [Help]