[Home] [Help]
PACKAGE BODY: APPS.JA_TW_AR_AUTO_INVOICE
Source
1 PACKAGE BODY ja_tw_ar_auto_invoice as
2 /* $Header: jatwraib.pls 120.2 2005/10/18 22:49:24 ykonishi ship $ */
3
4 -----------------------------------------------------------------------------
5 -- PRIVATE FUNCTIONS/PROCEDURES ** FORWARD DECLARATION ** --
6 -----------------------------------------------------------------------------
7 FUNCTION val_interface_lines(
8 p_request_id IN NUMBER
9 , p_interface_line_id IN NUMBER
10 , p_customer_trx_id IN NUMBER
11 , p_cust_trx_type_id IN NUMBER
12 , p_trx_date IN DATE) RETURN BOOLEAN;
13
14 -----------------------------------------------------------------------------
15 -- PUBLIC FUNCTIONS/PROCEDURES --
16 -----------------------------------------------------------------------------
17
18 -----------------------------------------------------------------------------
19 -- FUNCTION --
20 -- validate_gdff --
21 -- --
22 -- PARAMETERS --
23 -- INPUT --
24 -- p_request_id Number -- Concurrent Request_id --
25 -- --
26 -- RETURNS --
27 -- 0 Number -- Validation Fails, if there is any --
28 -- exceptional case which is handled --
29 -- in WHEN OTHERS --
30 -- 1 Number -- Validation Succeeds --
31 -- --
32 -----------------------------------------------------------------------------
33 FUNCTION validate_gdff (p_request_id IN NUMBER) RETURN NUMBER IS
34
35 l_return_code NUMBER(1);
36 l_country_code VARCHAR2(2);
37
38 CURSOR c_trx_lines (x_request_id NUMBER) IS
39 SELECT l.interface_line_id
40 , l.customer_trx_id
41 , l.cust_trx_type_id
42 , l.trx_date
43 , l.tax_code
44 , l.line_type
45 FROM
46 ra_interface_lines_gt l
47 WHERE
48 l.request_id = x_request_id
49 AND NVL(l.interface_status, '~') <> 'P'
50 AND l.customer_trx_id IS NOT NULL
51 ORDER BY l.trx_date;
52
53 BEGIN
54 --
55 -- Let's assume everything is OK
56 --
57 l_return_code := 1;
58
59 FOR trx_line_rec IN c_trx_lines (p_request_id)
60 LOOP
61 IF NOT val_interface_lines(
62 p_request_id
63 , trx_line_rec.interface_line_id
64 , trx_line_rec.customer_trx_id
65 , trx_line_rec.cust_trx_type_id
66 , trx_line_rec.trx_date)
67 THEN
68 arp_standard.debug('-- ja_tw_ar_auto_invoice.'
69 ||'val_interface_lines routine failed');
70 l_return_code := 0;
71 END IF;
72 END LOOP;
73
74 arp_standard.debug('Return value from ja_tw_ar_auto_invoice.'
75 ||'validate_gdff() = '||TO_CHAR(l_return_code));
76
77 RETURN l_return_code;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81
82 arp_standard.debug('-- Return From Exception when others');
83 arp_standard.debug('-- Return Code: 0');
84 arp_standard.debug('ja_tw_ar_auto_invoice.validate_gdff()-');
85
86 RETURN 0;
87
88 END validate_gdff;
89
90 -------------------------------------------------------------------------------
91 -- PUBLICE FUNCTION --
92 -- trx_num_upd --
93 -- --
94 -- PARAMETERS --
95 -- INPUT --
96 -- p_batch_source_id NUMBER -- Transaction Source ID --
97 -- p_trx_number VARCHAR2(20) -- Original Transaction Number --
98 -- --
99 -- RETURNS --
100 -- l_trx_number VARCHAR(20) -- GUI Number --
101 -- --
102 -------------------------------------------------------------------------------
103 FUNCTION trx_num_upd(p_batch_source_id IN NUMBER
104 ,p_trx_number IN VARCHAR2) RETURN VARCHAR2 IS
105
106
107 l_gui_src_id NUMBER(15);
108 l_inv_word VARCHAR2(2);
109 l_batch_source_id NUMBER(15);
110 l_trx_number VARCHAR2(20);
111 l_country_code VARCHAR2(2);
112
113 BEGIN
114
115 l_batch_source_id := p_batch_source_id;
116 l_trx_number := p_trx_number;
117
118 --
119 -- Get GUI Source ID.
120 --
121 -- Bug 4673732 : R12 MOAC
122 l_gui_src_id := ja_tw_sh_gui_utils.get_gui_src_id(l_batch_source_id, NULL);
123 --
124 -- Get Invoice Word.
125 --
126 -- Bug 4673732 : R12 MOAC
127 l_inv_word := ja_tw_sh_gui_utils.get_inv_word(l_gui_src_id, NULL);
128 --
129 -- Generate GUI Number.
130 --
131 IF l_inv_word IS NULL THEN
132 IF LENGTHB(l_trx_number) < 8
133 THEN
134 l_trx_number := LPAD(l_trx_number,8,'0');
135 END IF;
136 ELSE
137 IF NVL(SUBSTRB(l_trx_number,1,2),'&*') <> l_inv_word
138 THEN
139 l_trx_number := l_inv_word || LPAD(l_trx_number,8,'0');
140 END IF;
141 END IF;
142
143
144 RETURN l_trx_number;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 RAISE;
149 END trx_num_upd;
150
151 -------------------------------------------------------------------------------
152 -- ** Private ** Private ** Private ** Private ** Private ** Private ** --
153 -------------------------------------------------------------------------------
154 -------------------------------------------------------------------------------
155 -- PRIBATE FUNCTION --
156 -- val_interface_lines --
157 -- --
158 -- PARAMETERS --
159 -- INPUT --
160 -- p_request_id NUMBER -- Transaction Source ID --
161 -- p_interface_line_id NUMBER -- Interface Line ID --
162 -- p_cust_trx_type_id NUMBER -- Transaction Type --
163 -- p_trx_date DATE -- Transaction Date --
164 -- --
165 -- RETURNS --
166 -- TRUE/FALSE BOOLEAN --
167 -- --
168 -------------------------------------------------------------------------------
169 FUNCTION val_interface_lines(
170 p_request_id IN NUMBER
171 , p_interface_line_id IN NUMBER
172 , p_customer_trx_id IN NUMBER
173 , p_cust_trx_type_id IN NUMBER
174 , p_trx_date IN DATE) RETURN BOOLEAN IS
175
176 not_inv_class EXCEPTION;
177 auto_trx_num_no EXCEPTION;
178 fatal_error EXCEPTION;
179 l_exception_name VARCHAR2(10);
180 l_message_text VARCHAR2(240);
181 l_val_status VARCHAR2(10); -- SUCCESS,FAIL,or FATAL
182 l_cust_trx_type_id NUMBER;
183 l_interface_line_id NUMBER;
184 l_customer_trx_id NUMBER;
185 l_trx_date DATE;
186 l_invalid_value VARCHAR2(240);
187 l_batch_source_id NUMBER;
188 l_auto_trx_num_flag VARCHAR2(1);
189 l_inv_word VARCHAR2(2);
190 l_init_trx_num VARCHAR2(8);
191 l_fin_trx_num VARCHAR2(8);
192 l_last_trx_date VARCHAR2(30);
193 l_adv_days NUMBER;
194 l_gui_type VARCHAR2(2);
195 l_inv_class VARCHAR2(20);
196
197 l_debug_loc VARCHAR2(100);
198 BEGIN
199
200 l_cust_trx_type_id := p_cust_trx_type_id;
201 l_interface_line_id := p_interface_line_id;
202 l_customer_trx_id := p_customer_trx_id;
203 l_trx_date := p_trx_date;
204
205 -- Get the transaction type information
206 --
207 l_debug_loc := 'ja_tw_sh_gui_utils.get_trx_type_info';
208 ja_tw_sh_gui_utils.get_trx_type_info(
209 l_cust_trx_type_id
210 , l_gui_type
211 , l_inv_class
212 -- Bug 4673732 : R12 MOAC
213 , NULL);
214 --
215 -- Get transaction source id
216 --
217 l_debug_loc := 'Get Transaction Source ID.';
218 BEGIN
219 SELECT TO_NUMBER(cr.argument3)
220 INTO l_batch_source_id
221 FROM fnd_concurrent_requests cr
222 WHERE request_id = p_request_id;
223 END;
224 --
225 -- Get the transaction source information
226 --
227 l_debug_loc := 'ja_tw_sh_gui_utils.get_trx_src_info';
228 ja_tw_sh_gui_utils.get_trx_src_info(
229 l_batch_source_id
230 , l_auto_trx_num_flag
231 , l_inv_word
232 , l_init_trx_num
233 , l_fin_trx_num
234 , l_last_trx_date
235 , l_adv_days
236 -- Bug 4673732 : R12 MOAC
237 , NULL);
238
239 --
240 -- Exit when the invoice class is other than Invoice.
241 --
242 IF l_inv_class <> 'INV' THEN
243 RAISE not_inv_class;
244 END IF;
245 --
246 -- Exit when automatic trx numbering flag is 'No'.
247 --
248 IF l_auto_trx_num_flag = 'N' THEN
249 RAISE auto_trx_num_no;
250 END IF;
251 --
252 -- Check if Source and Type Relationship is defined.
253 --
254 l_debug_loc := 'ja_tw_sh_gui_utils.val_src_type_rel';
255 IF ja_tw_sh_gui_utils.val_src_type_rel(
256 l_interface_line_id
257 , l_batch_source_id
258 , l_cust_trx_type_id
259 , 'RAXTRX') = 'FATAL'
260 THEN
261 RAISE fatal_error;
262 END IF;
263 --
264 -- Check if transaction date is within valid range.
265 --
266 l_debug_loc := 'ja_tw_sh_gui_utils.val_trx_date';
267 IF ja_tw_sh_gui_utils.val_trx_date(
268 l_interface_line_id
269 , l_batch_source_id
270 , l_trx_date
271 , l_last_trx_date
272 , l_adv_days
273 , 'RAXTRX'
274 , NULL) = 'FATAL'
275 THEN
276 RAISE fatal_error;
277 END IF;
278 --
279 -- Check if transaction number is within valid range.
280 --
281 l_debug_loc := 'ja_tw_sh_gui_utils.val_trx_num';
282 IF ja_tw_sh_gui_utils.val_trx_num(
283 l_interface_line_id
284 , l_batch_source_id
285 , l_fin_trx_num
286 , 'RAXTRX') = 'FATAL'
287 THEN
288 RAISE fatal_error;
289 END IF;
290
291 --
292 -- Check if a transaction header has multiple tax codes..
293 --
294 l_debug_loc := 'ja_tw_sh_gui_utils.val_mixed_tax_codes';
295 IF ja_tw_sh_gui_utils.val_mixed_tax_codes(
296 l_interface_line_id
297 , l_customer_trx_id
298 , 'RAXTRX') = 'FATAL'
299 THEN
300 RAISE fatal_error;
301 END IF;
302
303 --
304 -- Copy GUI Type of the transaction type to GDF in Transactions.
305 --
306 l_debug_loc := 'ja_tw_sh_gui_utils.copy_gui_type';
307 IF NOT ja_tw_sh_gui_utils.copy_gui_type(
308 l_interface_line_id
309 , l_gui_type
310 , 'RAXTRX')
311 THEN
312 RAISE fatal_error;
313 END IF;
314
315 RETURN TRUE;
316
317 EXCEPTION
318 WHEN not_inv_class THEN
319 RETURN TRUE;
320 WHEN auto_trx_num_no THEN
321 RETURN TRUE;
322 WHEN OTHERS THEN
323 arp_standard.debug('-- Found an exception at ' ||l_debug_loc||'.');
324 arp_standard.debug('-- ' ||SQLERRM);
325 RETURN FALSE;
326 END;
327
328 END ja_tw_ar_auto_invoice;