[Home] [Help]
PACKAGE BODY: APPS.JA_ZZ_INVOICE_CREATE
Source
1 PACKAGE BODY ja_zz_invoice_create AS
2 /* $Header: jazzricb.pls 120.6 2005/10/30 01:48:14 appldev ship $ */
3
4 -- pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 -----------------------------------------------------------------------------
7 -- PRIVATE FUNCTIONS/PROCEDURES ** FORWARD DECLARATION ** --
8 -----------------------------------------------------------------------------
9 FUNCTION val_interface_lines(
10 p_request_id IN NUMBER
11 , p_trx_header_id IN NUMBER
12 , p_trx_line_id IN NUMBER
13 , p_customer_trx_id IN NUMBER
14 , p_cust_trx_type_id IN NUMBER
15 , p_trx_date IN DATE) RETURN BOOLEAN;
16
17 FUNCTION ja_tw_validate_gdff (p_request_id IN NUMBER) RETURN NUMBER;
18
19 FUNCTION ja_th_validate_gdff (p_request_id IN NUMBER) RETURN NUMBER;
20
21 -----------------------------------------------------------------------------
22 -- PUBLIC FUNCTIONS/PROCEDURES --
23 -----------------------------------------------------------------------------
24
25 -----------------------------------------------------------------------------
26 -- FUNCTION --
27 -- validate_gdff --
28 -- --
29 -- PARAMETERS --
30 -- INPUT --
31 -- p_request_id Number -- Concurrent Request_id --
32 -- --
33 -- RETURNS --
34 -- 0 Number -- Validation Fails, if there is any --
35 -- exceptional case which is handled --
36 -- in WHEN OTHERS --
37 -- 1 Number -- Validation Succeeds --
38 -- --
39 -----------------------------------------------------------------------------
40
41 t_interface_line_tbl R_interface_line;
42 t_interface_line_tbl1 R_interface_line1;
43
44
45 FUNCTION validate_gdff (p_request_id IN NUMBER) RETURN NUMBER IS
46
47 l_request_id NUMBER;
48 l_return_code NUMBER(1);
49 l_country_code VARCHAR2(2);
50 pg_debug VARCHAR2(1);
51
52 BEGIN
53
54 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
55
56 ------------------------------------------------------------
57 -- Let's assume everything is OK --
58 ------------------------------------------------------------
59 l_request_id := p_request_id;
60
61 l_return_code := 1;
62
63 l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
64
65 IF l_country_code = 'TW' THEN
66 l_return_code := ja_tw_validate_gdff(l_request_id);
67 ELSIF l_country_code = 'TH' THEN
68 l_return_code := ja_th_validate_gdff(l_request_id);
69 END IF;
70
71 IF pg_debug = 'Y' THEN
72 ar_invoice_utils.debug('Return value from ja_zz_invoice_create.'
73 ||'validate_gdff() = '||TO_CHAR(l_return_code));
74 END IF;
75
76 RETURN l_return_code;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80
81 IF pg_debug = 'Y' THEN
82 ar_invoice_utils.debug('-- Return From Exception when others');
83 ar_invoice_utils.debug('-- Return Code: 0');
84 ar_invoice_utils.debug('ja_zz_invoice_create.validate_gdff()-');
85 END IF;
86
87 RETURN 0;
88
89 END validate_gdff;
90
91 --
92 -- Validation for TAIWAN
93 --
94
95 -----------------------------------------------------------------------------
96 -- ** Private ** Private ** Private ** Private ** Private ** Private ** --
97 -----------------------------------------------------------------------------
98
99 -----------------------------------------------------------------------------
100 -- FUNCTION --
101 -- ja_tw_validate_gdff --
102 -- --
103 -- PARAMETERS --
104 -- INPUT --
105 -- p_request_id Number -- Concurrent Request_id --
106 -- --
107 -- RETURNS --
108 -- 0 Number -- Validation Fails, if there is any --
109 -- exceptional case which is handled --
110 -- in WHEN OTHERS --
111 -- 1 Number -- Validation Succeeds --
112 -- --
113 -----------------------------------------------------------------------------
114 FUNCTION ja_tw_validate_gdff (p_request_id IN NUMBER) RETURN NUMBER IS
115
116 l_return_code NUMBER(1);
117 l_country_code VARCHAR2(2);
118 l_index binary_integer;
119 pg_debug VARCHAR2(1);
120
121 CURSOR c_trx_lines (c_request_id NUMBER) IS
122 SELECT header.trx_header_id
123 , lines.trx_line_id
124 , lines.customer_trx_id
125 , header.cust_trx_type_id
126 , header.trx_date
127 , lines.vat_tax_id
128 , lines.line_type
129 FROM
130 ar_trx_lines_gt lines,
131 ar_trx_header_gt header,
132 ra_batch_sources_all rbs
133 WHERE
134 lines.request_id = c_request_id
135 AND lines.customer_trx_id IS NOT NULL
136 AND header.trx_header_id = lines.trx_header_id
137 AND header.batch_source_id = rbs.batch_source_id
138 ORDER BY header.trx_date;
139
140 BEGIN
141
142 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
143
144 --
145 -- Let's assume everything is OK
146 --
147 l_return_code := 1;
148
149
150 -------------------------------------------------------
151 -- Validate all the rows for this concurrent request --
152 -------------------------------------------------------
153
154 OPEN c_trx_lines(p_request_id);
155
156 LOOP
157
158 Fetch c_trx_lines BULK COLLECT INTO
159 t_interface_line_tbl.trx_header_id,
160 t_interface_line_tbl.trx_line_id,
161 t_interface_line_tbl.customer_trx_id,
162 t_interface_line_tbl.cust_trx_type_id,
163 t_interface_line_tbl.trx_date,
164 t_interface_line_tbl.vat_tax_id,
165 t_interface_line_tbl.line_type;
166
167 EXIT WHEN c_trx_lines%NOTFOUND;
168
169 END LOOP;
170
171 CLOSE c_trx_lines;
172
173 FOR l_index IN 1..t_interface_line_tbl.trx_line_id.LAST
174 LOOP
175
176 IF NOT val_interface_lines(
177 p_request_id
178 , t_interface_line_tbl.trx_header_id(l_index)
179 , t_interface_line_tbl.trx_line_id(l_index)
180 , t_interface_line_tbl.customer_trx_id(l_index)
181 , t_interface_line_tbl.cust_trx_type_id(l_index)
182 , t_interface_line_tbl.trx_date(l_index))
183 THEN
184 IF pg_debug = 'Y' THEN
185 ar_invoice_utils.debug('-- ja_tw_validate_gdff.'
186 ||'val_interface_lines routine failed');
187 END IF;
188
189 l_return_code := 0;
190 END IF;
191 END LOOP;
192
193 IF pg_debug = 'Y' THEN
194 ar_invoice_utils.debug('Return value from ja_tw_validate_gdff.'
195 ||'ja_tw_validate_gdff() = '||TO_CHAR(l_return_code));
196 END IF;
197
198 RETURN l_return_code;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202
203 IF pg_debug = 'Y' THEN
204 ar_invoice_utils.debug('-- Return From Exception when others');
205 ar_invoice_utils.debug('-- Return Code: 0');
206 ar_invoice_utils.debug('ja_zz_invoice_create.ja_tw_validate_gdff()-');
207 END IF;
208
209 RETURN 0;
210
211 END ja_tw_validate_gdff ;
212
213 -------------------------------------------------------------------------------
214 -- ** Private ** Private ** Private ** Private ** Private ** Private ** --
215 -------------------------------------------------------------------------------
216
217 -------------------------------------------------------------------------------
218 -- PRIBATE FUNCTION --
219 -- val_interface_lines --
220 -- --
221 -- PARAMETERS --
222 -- INPUT --
223 -- p_request_id NUMBER -- Transaction Source ID --
224 -- p_trx_header_id NUMBER -- Trasaction Header ID --
225 -- p_trx_line_id NUMBER -- Trasaction Line ID --
226 -- p_cust_trx_type_id NUMBER -- Transaction Type --
227 -- p_trx_date DATE -- Transaction Date --
228 -- --
229 -- RETURNS --
230 -- TRUE/FALSE BOOLEAN --
231 -- --
232 -------------------------------------------------------------------------------
233 FUNCTION val_interface_lines(
234 p_request_id IN NUMBER
235 , p_trx_header_id IN NUMBER
236 , p_trx_line_id IN NUMBER
237 , p_customer_trx_id IN NUMBER
238 , p_cust_trx_type_id IN NUMBER
239 , p_trx_date IN DATE) RETURN BOOLEAN IS
240
241 not_inv_class EXCEPTION;
242 auto_trx_num_no EXCEPTION;
243 fatal_error EXCEPTION;
244 l_exception_name VARCHAR2(10);
245 l_message_text VARCHAR2(240);
246 l_val_status VARCHAR2(10); -- SUCCESS,FAIL,or FATAL
247 l_cust_trx_type_id NUMBER;
248 l_trx_header_id NUMBER;
249 l_trx_line_id NUMBER;
250 l_customer_trx_id NUMBER;
251 l_trx_date DATE;
252 l_invalid_value VARCHAR2(240);
253 l_batch_source_id NUMBER;
254 l_auto_trx_num_flag VARCHAR2(1);
255 l_inv_word VARCHAR2(2);
256 l_init_trx_num VARCHAR2(8);
257 l_fin_trx_num VARCHAR2(8);
258 l_last_trx_date VARCHAR2(30);
259 l_adv_days NUMBER;
260 l_gui_type VARCHAR2(2);
261 l_inv_class VARCHAR2(20);
262
263 l_debug_loc VARCHAR2(100);
264
265 pg_debug VARCHAR2(1);
266
267 BEGIN
268
269 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
270
271 l_cust_trx_type_id := p_cust_trx_type_id;
272 l_trx_header_id := p_trx_header_id;
273 l_trx_line_id := p_trx_line_id;
274 l_customer_trx_id := p_customer_trx_id;
275 l_trx_date := p_trx_date;
276
277 --
278 -- Get the transaction type information
279 --
280 l_debug_loc := 'ja_zz_validate_invoice_gdf.get_trx_type_info';
281 ja_zz_validate_invoice_gdf.get_trx_type_info(
282 l_cust_trx_type_id
283 , l_gui_type
284 , l_inv_class);
285 --
286 -- Get transaction source id
287 --
288 l_debug_loc := 'Get Transaction Source ID.';
289 BEGIN
290 SELECT TO_NUMBER(cr.argument3)
291 INTO l_batch_source_id
292 FROM fnd_concurrent_requests cr
293 WHERE request_id = p_request_id;
294 END;
295 --
296 -- Get the transaction source information
297 --
298 l_debug_loc := 'ja_zz_validate_invoice_gdf.get_trx_src_info';
299 ja_zz_validate_invoice_gdf.get_trx_src_info(
300 l_batch_source_id
301 , l_auto_trx_num_flag
302 , l_inv_word
303 , l_init_trx_num
304 , l_fin_trx_num
305 , l_last_trx_date
306 , l_adv_days);
307
308 --
309 -- Exit when the invoice class is other than Invoice.
310 --
311 IF l_inv_class <> 'INV' THEN
312 RAISE not_inv_class;
313 END IF;
314 --
315 -- Exit when automatic trx numbering flag is 'No'.
316 --
317 IF l_auto_trx_num_flag = 'N' THEN
318 RAISE auto_trx_num_no;
319 END IF;
320 --
321 -- Check if Source and Type Relationship is defined.
322 --
323 l_debug_loc := 'ja_zz_validate_invoice_gdf.val_src_type_rel';
324 IF ja_zz_validate_invoice_gdf.val_src_type_rel(
325 l_trx_header_id
326 , l_trx_line_id
327 , l_batch_source_id
328 , l_cust_trx_type_id
329 , 'RAXTRX') = 'FATAL'
330 THEN
331 RAISE fatal_error;
332 END IF;
333 --
334 -- Check if transaction date is within valid range.
335 --
336 l_debug_loc := 'ja_zz_validate_invoice_gdf.val_trx_date';
337 IF ja_zz_validate_invoice_gdf.val_trx_date(
338 l_trx_header_id
339 , l_trx_line_id
340 , l_batch_source_id
341 , l_trx_date
342 , l_last_trx_date
343 , l_adv_days
344 , 'RAXTRX') = 'FATAL'
345 THEN
346 RAISE fatal_error;
347 END IF;
348 --
349 -- Check if transaction number is within valid range.
350 --
351 l_debug_loc := 'ja_zz_validate_invoice_gdf.val_trx_num';
352 IF ja_zz_validate_invoice_gdf.val_trx_num(
353 l_trx_header_id
354 , l_trx_line_id
355 , l_batch_source_id
356 , l_fin_trx_num
357 , 'RAXTRX') = 'FATAL'
358 THEN
359 RAISE fatal_error;
360 END IF;
361
362 --
363 -- Check if a transaction header has multiple tax codes..
364 --
365 l_debug_loc := 'ja_zz_validate_invoice_gdf.val_mixed_tax_codes';
366 IF ja_zz_validate_invoice_gdf.val_mixed_tax_codes(
367 l_trx_header_id
368 , l_trx_line_id
369 , l_customer_trx_id
370 , 'RAXTRX') = 'FATAL'
371 THEN
372 RAISE fatal_error;
373 END IF;
374
375 --
376 -- Copy GUI Type of the transaction type to GDF in Transactions.
377 --
378 l_debug_loc := 'ja_zz_validate_invoice_gdf.copy_gui_type';
379 IF NOT ja_zz_validate_invoice_gdf.copy_gui_type(
380 l_trx_line_id
381 , l_gui_type
382 , 'RAXTRX')
383 THEN
384 RAISE fatal_error;
385 END IF;
386
387 RETURN TRUE;
388
389 EXCEPTION
390 WHEN not_inv_class THEN
391 RETURN TRUE;
392 WHEN auto_trx_num_no THEN
393 RETURN TRUE;
394 WHEN OTHERS THEN
395 IF pg_debug ='Y' THEN
396 ar_invoice_utils.debug('-- Found an exception at ' ||l_debug_loc||'.');
397 ar_invoice_utils.debug('-- ' ||SQLERRM);
398 END IF;
399
400 RETURN FALSE;
401 END;
402
403
404 --
405 -- Validation for THAILAND
406 --
407
408 -----------------------------------------------------------------------------
409 -- ** Private ** Private ** Private ** Private ** Private ** Private ** --
410 -----------------------------------------------------------------------------
411 -----------------------------------------------------------------------------
412 -- FUNCTION --
413 -- ja_th_validate_gdff --
414 -- --
415 -- PARAMETERS --
416 -- INPUT --
417 -- p_request_id Number -- Concurrent Request_id --
418 -- --
419 -- RETURNS --
420 -- 0 Number -- Validation Fails, if there is any --
421 -- exceptional case which is handled --
422 -- in WHEN OTHERS --
423 -- 1 Number -- Validation Succeeds --
424 -- --
425 -----------------------------------------------------------------------------
426
427 FUNCTION ja_th_validate_gdff(p_request_id IN NUMBER)
428 RETURN NUMBER IS
429
430 CURSOR tax_invoice_headers(c_request_id NUMBER) IS
431 SELECT distinct
432 lines.trx_line_id,
433 lines.customer_trx_id,
434 header.cust_trx_type_id,
435 header.trx_date,
436 fnd_date.canonical_to_date(types.global_attribute2) last_issued_date,
437 to_number(types.global_attribute3) advance_days,
438 lines.vat_tax_id,
439 lines.line_type
440 FROM ar_trx_lines_gt lines,
441 ar_trx_header_gt header,
442 ra_batch_sources_all rbs,
443 ra_cust_trx_types types
444 WHERE lines.request_id = c_request_id
445 AND header.trx_header_id = lines.trx_header_id
446 AND header.batch_source_id = rbs.batch_source_id
447 AND header.cust_trx_type_id = types.cust_trx_type_id
448 AND nvl(types.global_attribute1, 'N') = 'Y';
449
450 return_code NUMBER;
451 validation1 NUMBER;
452 validation2 NUMBER;
453 validation3 NUMBER;
454 l_index1 binary_integer;
455
456 pg_debug VARCHAR2(1);
457
458 BEGIN
459
460 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
461
462 IF pg_debug = 'Y' THEN
463 ar_invoice_utils.debug('ja_th_validate_gdff()+');
464 END IF;
465
466 return_code := 1;
467
468 -------------------------------------------------------
469 -- Validate all the rows for this concurrent request --
470 -------------------------------------------------------
471 Open tax_invoice_headers(p_request_id);
472
473 LOOP
474
475 Fetch tax_invoice_headers BULK COLLECT INTO
476 t_interface_line_tbl1.trx_line_id,
477 t_interface_line_tbl1.customer_trx_id,
478 t_interface_line_tbl1.cust_trx_type_id,
479 t_interface_line_tbl1.trx_date,
480 t_interface_line_tbl1.last_issued_date,
481 t_interface_line_tbl1.advance_days,
482 t_interface_line_tbl1.vat_tax_id,
483 t_interface_line_tbl1.line_type
484 ;
485
486 EXIT WHEN tax_invoice_headers%NOTFOUND;
487
488 END LOOP;
489
490 CLOSE tax_invoice_headers;
491
492 FOR l_index1 IN 1..t_interface_line_tbl1.trx_line_id.LAST
493 LOOP
494
495 validation1 := ja_zz_validate_invoice_gdf.validate_trx_date(
496 t_interface_line_tbl1.customer_trx_id(l_index1),
497 t_interface_line_tbl1.trx_date(l_index1),
498 t_interface_line_tbl1.last_issued_date(l_index1),
499 t_interface_line_tbl1.advance_days(l_index1),
500 'RAXTRX');
501
502 IF t_interface_line_tbl1.line_type(l_index1) = 'LINE' AND t_interface_line_tbl1.vat_tax_id(l_index1) IS NULL THEN
503 validation2 := 1;
504 ELSE
505 validation2 := ja_zz_validate_invoice_gdf.validate_tax_code(
506 t_interface_line_tbl1.customer_trx_id(l_index1),
507 'RAXTRX');
508 END IF;
509
510 IF validation1 = 1 AND validation2 = 1 THEN
511 validation3 := ja_zz_validate_invoice_gdf.update_last_issued_date(
512 t_interface_line_tbl1.customer_trx_id(l_index1),
513 t_interface_line_tbl1.cust_trx_type_id(l_index1),
514 t_interface_line_tbl1.trx_date(l_index1),
515 'RAXTRX');
516 END IF;
517
518 IF validation1 = -1 OR validation2 = -1 OR validation3 = -1 THEN
519 -- At the first sign of Fatal error, quite validation with
520 -- return_code=0.
521 return_code := 0;
522 exit;
523 END IF;
524
525 END LOOP;
526
527 IF pg_debug = 'Y' THEN
528 ar_invoice_utils.debug('ja_th_validate_gdff()-');
529 END IF;
530
531 return(return_code);
532
533 EXCEPTION
534 WHEN others THEN
535
536 IF pg_debug = 'Y' THEN
537 ar_invoice_utils.debug('-- Return From Exception when others');
538 ar_invoice_utils.debug('-- Return Code: 0');
539 ar_invoice_utils.debug('ja_th_validate_gdff()-');
540 END IF;
541
542 return(0);
543
544 END ja_th_validate_gdff;
545
546 END ja_zz_invoice_create;