[Home] [Help]
PACKAGE BODY: APPS.JAI_BOE_MIGRATION_PKG
Source
1 PACKAGE BODY JAI_BOE_MIGRATION_PKG AS
2 /* $Header: jai_boe_migrate.plb 120.6 2011/07/22 07:19:21 zhhou noship $ */
3
4 --==========================================================================
5
6 -- FUNCTION NAME:
7
8 --
9
10 -- boe_batch_migration Public
11
12 --
13
14 -- DESCRIPTION:
15 --
16
17 -- This procedure will be called by the concurrent to do batch migration
18 --for dedicate BOEs.
19 --
20
21 -- PARAMETERS:
22
23 -- In: pv_migration_type migration type, 'All', 'Unapplied' or 'None'
24
25 -- In: pd_migration_date GL date for create accounting entry.
26
27 -- Out: errbuf used for concurrent
28
29 -- Out: retcode used for concurrent
30
31 -- DESIGN REFERENCES:
32
33 --
34
35 -- CHANGE HISTORY:
36
37 --
38
39 --=========================================================================
40 PROCEDURE boe_batch_migration
41 (
42 errbuf OUT NOCOPY VARCHAR2
43 ,retcode OUT NOCOPY VARCHAR2
44 ,pv_migration_type IN VARCHAR2
45 ,pv_migration_date IN VARCHAR2
46 ) IS
47 CURSOR temp_cur IS
48 SELECT boe_id, tax_category_id FROM JAI_BOE_MIGRATION_TEMP;
49
50 temp_rec temp_cur%ROWTYPE;
51 ld_gl_date DATE;
52 ln_org_id NUMBER;
53 lv_valid_flag VARCHAR2(1);
54 BEGIN
55 ld_gl_date := fnd_date.canonical_to_date(pv_migration_date);
56 ln_org_id := MO_GLOBAL.get_current_org_id();
57
58 Fnd_File.put_line(Fnd_File.LOG,'**Entering boe_migration_batch for org_id:' ||ln_org_id||' migration_type:'||pv_migration_type||' migration_date'||ld_gl_date);
59
60 IF pv_migration_type <> 'NONE' THEN
61 -- no accounting migration needed for 'None',don't check gl Date.
62 is_in_open_period(ld_gl_date,ln_org_id,lv_valid_flag);
63 IF lv_valid_flag = 'N' THEN
64 Fnd_File.put_line(Fnd_File.LOG,' ** migration date ' || ld_gl_date ||' is not in open period. ** ');
65 RETURN;
66 END IF;--IF pv_migration_type <> 'NONE'
67 END IF;--lv_valid_flag = 'N'
68
69 OPEN temp_cur;
70 LOOP
71 FETCH temp_cur INTO temp_rec;
72 EXIT WHEN temp_cur%NOTFOUND;
73 boe_migration(pn_org_id => ln_org_id
74 ,pn_boe_id => temp_rec.boe_id
75 ,pn_tax_category => temp_rec.tax_category_id
76 ,pv_migration_type => pv_migration_type
77 ,pd_migration_date => ld_gl_date);
78 END LOOP;
79 CLOSE temp_cur;
80 END boe_batch_migration;
81 --==========================================================================
82
83 -- FUNCTION NAME:
84
85 --
86
87 -- boe_migration Private
88
89 --
90
91 -- DESCRIPTION:
92 --
93
94 -- This procedure will process the BOE migration for one BOE record.
95 --
96
97 -- PARAMETERS:
98 -- ln: pn_org_id The org id set in request.
99 -- In: pn_boe_id The BOE_ID that need to be processed.
100
101 -- In: pn_tax_category Tax category id which cover all the tax
102 -- types in dedicate BOE dtls.
103
104 -- In: pv_migration_type migration type, 'All', 'Unapplied' or 'None'
105
106 -- In: pd_migration_date GL date for create accounting entry.
107
108 -- DESIGN REFERENCES:
109
110 --
111
112 -- CHANGE HISTORY:
113
114 --
115
116 --=========================================================================
117 PROCEDURE boe_migration
118 (
119 pn_org_id IN NUMBER
120 ,pn_boe_id IN NUMBER
121 ,pn_tax_category IN NUMBER
122 ,pv_migration_type IN VARCHAR2
123 ,pd_migration_date IN DATE
124 ) IS
125 ln_boe_amount NUMBER;
126 ln_applied_amount NUMBER;
127 ln_available_amount NUMBER;
128 lv_consolidate_flag VARCHAR2(1);
129 ln_percentage NUMBER := 0;
130 lv_valid_category_flag VARCHAR2(1);
131 lv_boe_invoice_num jai_cmn_boe_hdrs.INVOICE_NUM%TYPE;
132
133 CURSOR boe_hdr_cur(pn_boe_id NUMBER) IS
134 SELECT BOE_AMOUNT
135 ,AMOUNT_APPLIED
136 ,AMOUNT_WRITTEN_OFF
137 ,INVOICE_NUM,
138 CONSOLIDATED_FLAG
139 FROM jai_cmn_boe_hdrs_all
140 WHERE boe_id = pn_boe_id;
141
142 boe_hdr_rec boe_hdr_cur%ROWTYPE;
143
144 BEGIN
145 Fnd_File.put_line(Fnd_File.LOG,' **Entering boe_migration for boe_id: '|| pn_boe_id ||' tax_category:' || pn_tax_category);
146 -- Do validation for the provided boe_id and tax_category_id.
147 validation_boe(pn_org_id => pn_org_id
148 ,pn_boe_id => pn_boe_id
149 ,pn_tax_category_id => pn_tax_category
150 ,xv_valid_flag => lv_valid_category_flag);
151
152 IF lv_valid_category_flag = 'Y' THEN
153
154 SAVEPOINT do_migration;
155
156 OPEN boe_hdr_cur(pn_boe_id);
157 FETCH boe_hdr_cur INTO boe_hdr_rec;
158 CLOSE boe_hdr_cur;
159
160 ln_boe_amount := boe_hdr_rec.BOE_AMOUNT;
161 ln_applied_amount := nvl(boe_hdr_rec.AMOUNT_APPLIED,0);
162 ln_available_amount := round((ln_boe_amount - ln_applied_amount),2);
163 lv_consolidate_flag := boe_hdr_rec.CONSOLIDATED_FLAG;
164 lv_boe_invoice_num := boe_hdr_rec.invoice_num;
165
166 update_boe_hdr(pn_boe_id
167 ,pn_org_id
168 ,ln_applied_amount
169 ,lv_boe_invoice_num);
170 ln_percentage := round((nvl(ln_applied_amount ,0) / ln_boe_amount),2);
171 merge_boe_details(pn_boe_id => pn_boe_id
172 ,pn_tax_category_id => pn_tax_category
173 ,pv_consolidate_flag => lv_consolidate_flag
174 ,pn_percentage => ln_percentage);
175 insert_boe_rounding(pn_boe_id);
176
177 IF pv_migration_type <> 'NONE' THEN
178 accounting_migration(pn_boe_id => pn_boe_id
179 ,pn_availabe_boe_amount => ln_available_amount
180 ,pd_migration_date => pd_migration_date);
181 END IF;--pv_migration_type <> 'NONE'
182 Fnd_File.put_line(Fnd_File.LOG,'**Ending boe_migration after finish all operations. **');
183 RETURN;
184 ELSE
185 Fnd_File.put_line(Fnd_File.LOG,'*Exit migration for validation errors.');
186 RETURN;
187
188 END IF;--lv_valid_category_flag = 'Y'
189
190 EXCEPTION
191 WHEN invalid_tax_category_tax_type THEN
192 Fnd_File.put_line(Fnd_File.LOG,' **Exit migration for invalid_tax_category_tax_type. ');
193 ROLLBACK TO do_migration;
194 RETURN;
195
196 WHEN OTHERS THEN
197 Fnd_File.put_line(Fnd_File.LOG,' **Exit migration for unknow errors: ' || SQLERRM);
198 ROLLBACK TO do_migration;
199 RETURN;
200
201 END boe_migration;
202
203 --==========================================================================
204
205 -- PROCEDURE NAME:
206
207 --
208
209 -- update_boe_hdr Private
210
211 --
212
213 -- DESCRIPTION:
214 --
215
216 -- This procedure will process the BOE migration for one BOE record.
217 --
218
219 -- PARAMETERS:
220
221 -- In: pn_boe_id The BOE_ID that need to be updated.
222
223 -- In: pn_amount_applied The applied amount of this BOE.
224
225 -- In: pv_invoice_num The BOE invoice num of this BOE.
226
227 -- DESIGN REFERENCES:
228
229 --
230
231 -- CHANGE HISTORY:
232
233 --
234
235 --=========================================================================
236 PROCEDURE update_boe_hdr
237 (
238 pn_boe_id IN NUMBER
239 ,pn_org_id IN NUMBER
240 ,pn_amount_applied IN NUMBER
241 ,pv_invoice_num IN VARCHAR2
242 ) IS
243
244 CURSOR boe_invoice_cur(pn_invoice_num IN VARCHAR2) IS
245 SELECT PAYMENT_STATUS_FLAG
246 FROM AP_INVOICES
247 WHERE INVOICE_NUM = pn_invoice_num;
248
249 boe_invoice_payment_status AP_INVOICES.PAYMENT_STATUS_FLAG%TYPE;
250 boe_status jai_cmn_boe_hdrs.status%TYPE;
251
252 BEGIN
253 Fnd_File.put_line(Fnd_File.LOG,' **Start update_boe_hdr for boe_id:' || pn_boe_id ||' invoice num:' || pv_invoice_num ||'applied amount:' || pn_amount_applied || ' **');
254
255 OPEN boe_invoice_cur(pv_invoice_num);
256 FETCH boe_invoice_cur INTO boe_invoice_payment_status;
257 CLOSE boe_invoice_cur;
258
259 IF nvl(boe_invoice_payment_status ,'N') = 'N' THEN
260 boe_status := 'CONFIRMED';
261 ELSIF nvl(boe_invoice_payment_status,'N') = 'Y' AND nvl(pn_amount_applied,0) = 0 THEN
262 boe_status := 'AVAILABLE';
263 ELSIF nvl(boe_invoice_payment_status,'N') = 'Y' AND nvl(pn_amount_applied,0) > 0 THEN
264 boe_status := 'APPLIED';
265 END IF;--nvl(boe_invoice_payment_status ,'N') = 'N'
266
267 UPDATE jai_cmn_boe_hdrs_all SET
268 status = boe_status,
269 migration_flag = 'Y',
270 org_id = pn_org_id,
271 BOE_WITHOUT_REFER_FLAG = decode(CONSOLIDATED_FLAG,'Y',NULL,'N','Y'),
272 CUSTOMS_AUTHORITY_ID = VENDOR_ID,
273 CUSTOMS_AUTHORITY_SITE_ID = VENDOR_SITE_ID,
274 BOE_DATE = IMPORT_DATE,
275 last_update_date = SYSDATE,
276 last_updated_by = fnd_global.user_id
277 WHERE boe_id = pn_boe_id;
278 Fnd_File.put_line(Fnd_File.LOG,' **Finish update_boe_hdr for boe_id:' || pn_boe_id ||' with boe status:' || boe_status || '--- **');
279
280 END update_boe_hdr;
281
282 --==========================================================================
283
284 -- PROCEDURE NAME:
285
286 --
287
288 -- merge_boe_details Private
289
290 --
291
292 -- DESCRIPTION:
293 --
294
295 -- This procedure will process the dedicate BOE details and merge them
296 -- dependends on the consolidate flag.
297
298 -- PARAMETERS:
299
300 -- In: pn_boe_id The BOE_ID that need to be updated.
301
302 -- In: pn_tax_category_id The applied amount of this BOE.
303
304 -- In: pv_consolidate_flag 'Y': item independent BOE
305
306 -- 'N': item based BOE
307
308 -- in pn_percentage The percentage of applied amount in total BOE amount.
309
310 -- DESIGN REFERENCES:
311
312 --
313
314 -- CHANGE HISTORY:
315
316 --
317
318 --=========================================================================
319 /****************************** Start merge_boe_details ******************************/
320 PROCEDURE merge_boe_details
321 (
322 pn_boe_id IN NUMBER
323 ,pn_tax_category_id IN NUMBER
324 ,pv_consolidate_flag IN VARCHAR2
325 ,pn_percentage IN NUMBER
326 ) IS
327 BEGIN
328 IF nvl(pv_consolidate_flag ,'N') = 'Y' THEN
329 merge_consolidate_boe_details(pn_boe_id => pn_boe_id
330 ,pn_tax_category_id => pn_tax_category_id
331 ,pn_percentage => pn_percentage);
332 ELSE
333 merge_item_boe_details(pn_boe_id => pn_boe_id
334 ,pn_tax_category_id => pn_tax_category_id
335 ,pn_percentage => pn_percentage);
336 END IF;--nvl(pv_consolidate_flag ,'N') = 'Y'
337 END merge_boe_details;
338 /****************************** End merge_boe_details ******************************/
339 --==========================================================================
340
341 -- PROCEDURE NAME:
342
343 --
344
345 -- merge_consolidate_boe_details Private
346
347 --
348
349 -- DESCRIPTION:
350 --
351
352 -- This procedure will process the item independent BOE details
353 -- and merge them into one BOE detail line.
354
355 -- PARAMETERS:
356
357 -- In: pn_boe_id The BOE_ID that need to be updated.
358
359 -- In: pn_tax_category_id The applied amount of this BOE.
360
361 -- in pn_percentage The percentage of applied amount in total BOE amount.
362
363 -- DESIGN REFERENCES:
364
365 --
366
367 -- CHANGE HISTORY:
368
369 --
370
371 --=========================================================================
372 /****************************** Start merge_consolidate_boe_details ******************************/
373 PROCEDURE merge_consolidate_boe_details
374 (
375 pn_boe_id IN NUMBER
376 ,pn_tax_category_id IN NUMBER
377 ,pn_percentage IN NUMBER
378 ) IS
379 CURSOR consolidate_boe_detail_cur(pn_boe_id NUMBER) IS
380 SELECT BOE_ID,
381 SUM(BASIC_DUTY_AMOUNT) customs,
382 SUM(ADDL_DUTY_AMOUNT) additional_customs_duty,
383 SUM(SURCH_DUTY_AMOUNT) surcharge_duty,
384 SUM(TOTAL_DUTY_AMOUNT) total_tax_amount,
385 SUM(EDU_CESS_CVD_AMOUNT) cvd_education_cess,
386 SUM(EDU_CESS_CUSTOMS_AMOUNT) customs_edu_cess,
387 SUM(ADDITIONAL_CVD_AMT) additional_CVD,
388 SUM(SH_CUSTOMS_CESS_AMOUNT) customs_sh_education_cess,
389 SUM(SH_CVD_CESS_AMOUNT) cvd_sh_edu_cess
390 FROM jai_cmn_boe_dtls
391 WHERE BOE_ID = pn_boe_id
392 GROUP BY BOE_ID;
393
394 CURSOR detail_line_cur(pn_boe_id NUMBER) IS
395 SELECT customs_tariff,duty_code,PKG_NUMBER,SERIAL_NUMBER
396 FROM jai_cmn_boe_dtls
397 WHERE BOE_ID = pn_boe_id AND rownum = 1;
398
399 ln_boe_detail_line_num NUMBER := 1;
400 ln_boe_detail_id NUMBER;
401 consolidate_boe_detail_rec consolidate_boe_detail_cur%ROWTYPE;
402 detail_line_rec detail_line_cur%ROWTYPE;
403 l_boe_detail_type_rec JAI_BOE_DETAILS%ROWTYPE;
404 dedicate_dtls_rec dedicate_dtls_summary_type;
405 BEGIN
406 Fnd_File.put_line(Fnd_File.LOG,'**Start merge_consolidate_boe_details for boe_id:'||pn_boe_id || ' with applied percentage:'||pn_percentage || '--- **');
407
408 OPEN consolidate_boe_detail_cur(pn_boe_id);
409 FETCH consolidate_boe_detail_cur INTO consolidate_boe_detail_rec;
410 CLOSE consolidate_boe_detail_cur;
411
412 OPEN detail_line_cur(pn_boe_id);
413 FETCH detail_line_cur INTO detail_line_rec;
414 CLOSE detail_line_cur;
415 SELECT jai_boe_details_s.NEXTVAL INTO ln_boe_detail_id FROM dual;
416
417 l_boe_detail_type_rec.boe_detail_id := ln_boe_detail_id;
418 l_boe_detail_type_rec.boe_id := pn_boe_id;
419 l_boe_detail_type_rec.detail_line_num := ln_boe_detail_line_num;
420 l_boe_detail_type_rec.customs_tariff_code := detail_line_rec.customs_tariff;
421 l_boe_detail_type_rec.duty_code := detail_line_rec.duty_code;
422 l_boe_detail_type_rec.description := 'Migrated Line for BOE '||pn_boe_id;
423 l_boe_detail_type_rec.assessable_value := NULL;
424 l_boe_detail_type_rec.tax_category_id := pn_tax_category_id;
425 l_boe_detail_type_rec.tax_amount := consolidate_boe_detail_rec.total_tax_amount;
426 l_boe_detail_type_rec.PKG_MARKS_NUM := detail_line_rec.PKG_NUMBER;
427 l_boe_detail_type_rec.PKG_SERIAL_NUMBER := detail_line_rec.SERIAL_NUMBER;
428
429 dedicate_dtls_rec.boe_id := pn_boe_id;
430 dedicate_dtls_rec.total_tax_amount := nvl(consolidate_boe_detail_rec.total_tax_amount,0);
431 dedicate_dtls_rec.customs := nvl(consolidate_boe_detail_rec.customs,0);
432 dedicate_dtls_rec.additional_customs_duty := nvl(consolidate_boe_detail_rec.additional_customs_duty,0);
433 dedicate_dtls_rec.surcharge_duty := nvl(consolidate_boe_detail_rec.surcharge_duty,0);
434 dedicate_dtls_rec.cvd_education_cess := nvl(consolidate_boe_detail_rec.cvd_education_cess,0);
435 dedicate_dtls_rec.customs_edu_cess := nvl(consolidate_boe_detail_rec.customs_edu_cess,0);
436 dedicate_dtls_rec.additional_CVD := nvl(consolidate_boe_detail_rec.additional_CVD,0);
437 dedicate_dtls_rec.customs_sh_education_cess := nvl(consolidate_boe_detail_rec.customs_sh_education_cess,0);
438 dedicate_dtls_rec.cvd_sh_edu_cess := nvl(consolidate_boe_detail_rec.cvd_sh_edu_cess,0);
439
440 Fnd_File.put_line(Fnd_File.LOG,'**total_tax_amount:' ||dedicate_dtls_rec.total_tax_amount);
441 Fnd_File.put_line(Fnd_File.LOG,'**customs:' || dedicate_dtls_rec.customs);
442 Fnd_File.put_line(Fnd_File.LOG,'**additional_customs_duty:' ||dedicate_dtls_rec.additional_customs_duty);
443 Fnd_File.put_line(Fnd_File.LOG,'**surcharge_duty:' ||dedicate_dtls_rec.surcharge_duty);
444 Fnd_File.put_line(Fnd_File.LOG,'**cvd_education_cess:'||dedicate_dtls_rec.cvd_education_cess);
445 Fnd_File.put_line(Fnd_File.LOG,'**customs_edu_cess:'||dedicate_dtls_rec.customs_edu_cess);
446 Fnd_File.put_line(Fnd_File.LOG,'**additional_CVD:'||dedicate_dtls_rec.additional_CVD);
447 Fnd_File.put_line(Fnd_File.LOG,'**customs_sh_education_cess:' ||dedicate_dtls_rec.customs_sh_education_cess);
448 Fnd_File.put_line(Fnd_File.LOG,'**cvd_sh_edu_cess:' ||dedicate_dtls_rec.cvd_sh_edu_cess);
449
450 insert_boe_detail(p_boe_detail_rec => l_boe_detail_type_rec);
451 details_split(dedicate_dtl_rec => dedicate_dtls_rec
452 ,pn_tax_category_id => pn_tax_category_id
453 ,pn_boe_detail_id => ln_boe_detail_id
454 ,pn_applied_percentage => pn_percentage);
455 Fnd_File.put_line(Fnd_File.LOG,'**Finish merge_consolidate_boe_details --- **');
456
457 END merge_consolidate_boe_details;
458 --==========================================================================
459
460 -- PROCEDURE NAME:
461
462 --
463
464 -- merge_item_boe_details Private
465
466 --
467
468 -- DESCRIPTION:
469 --
470
471 -- This procedure will process the item based BOE details
472 -- group by item_id and merge the same items into one BOE detail line.
473
474 -- PARAMETERS:
475
476 -- In: pn_boe_id The BOE_ID that need to be updated.
477
478 -- In: pn_tax_category_id The applied amount of this BOE.
479
480 -- in pn_percentage The percentage of applied amount in total BOE amount.
481
482 -- DESIGN REFERENCES:
483
484 --
485
486 -- CHANGE HISTORY:
487
488 --
489
490 --=========================================================================
491 PROCEDURE merge_item_boe_details
492 (
493 pn_boe_id IN NUMBER
494 ,pn_tax_category_id IN NUMBER
495 ,pn_percentage IN NUMBER
496 ) IS
497
498 CURSOR item_boe_detail_cur(v_boe_id NUMBER) IS
499 SELECT BOE_ID, item_number, SUM(QUANTITY) QUANTITY,
500 SUM(BASIC_DUTY_AMOUNT) customs,
501 SUM(ADDL_DUTY_AMOUNT) additional_customs_duty,
502 SUM(SURCH_DUTY_AMOUNT) surcharge_duty,
503 SUM(TOTAL_DUTY_AMOUNT) total_tax_amount,
504 SUM(EDU_CESS_CVD_AMOUNT) cvd_education_cess,
505 SUM(EDU_CESS_CUSTOMS_AMOUNT) customs_edu_cess,
506 SUM(ADDITIONAL_CVD_AMT) additional_CVD,
507 SUM(SH_CUSTOMS_CESS_AMOUNT) customs_sh_education_cess,
508 SUM(SH_CVD_CESS_AMOUNT) cvd_sh_edu_cess
509 FROM jai_cmn_boe_dtls
510 WHERE BOE_ID = v_boe_id
511 GROUP BY BOE_ID, item_number;
512
513 CURSOR detail_line_cur(pn_boe_id NUMBER, pn_item_number NUMBER) IS
514 SELECT uom_code,customs_tariff,duty_code,PKG_NUMBER,SERIAL_NUMBER
515 FROM jai_cmn_boe_dtls
516 WHERE BOE_ID = pn_boe_id AND item_number = pn_item_number AND rownum = 1;
517
518 ln_boe_detail_line_num NUMBER := 0;
519 ln_boe_detail_id NUMBER;
520 detail_line_rec detail_line_cur%ROWTYPE;
521 item_boe_detail_rec item_boe_detail_cur%ROWTYPE;
522 l_boe_detail_type_rec JAI_BOE_DETAILS%ROWTYPE;
523 dedicate_dtls_rec dedicate_dtls_summary_type;
524 BEGIN
525 Fnd_File.put_line(Fnd_File.LOG,' **Start merge_item_boe_details for boe_id:' ||pn_boe_id || ' with applied percentage:' ||pn_percentage || '--- **');
526
527 FOR item_boe_detail_rec IN item_boe_detail_cur(pn_boe_id) LOOP
528
529 OPEN detail_line_cur(pn_boe_id,item_boe_detail_rec.item_number);
530 FETCH detail_line_cur INTO detail_line_rec;
531 CLOSE detail_line_cur;
532
533 SELECT jai_boe_details_s.NEXTVAL INTO ln_boe_detail_id FROM dual;
534 ln_boe_detail_line_num := ln_boe_detail_line_num + 1;
535
536 l_boe_detail_type_rec.boe_detail_id := ln_boe_detail_id;
537 l_boe_detail_type_rec.boe_id := pn_boe_id;
538 l_boe_detail_type_rec.detail_line_num := ln_boe_detail_line_num;
539 l_boe_detail_type_rec.INVENTORY_ITEM_ID := item_boe_detail_rec.item_number;
540 l_boe_detail_type_rec.QUANTITY := item_boe_detail_rec.QUANTITY;
541 l_boe_detail_type_rec.UOM_CODE := detail_line_rec.uom_code;
542 l_boe_detail_type_rec.customs_tariff_code := detail_line_rec.customs_tariff;
543 l_boe_detail_type_rec.duty_code := detail_line_rec.duty_code;
544 l_boe_detail_type_rec.description := 'Migrated Line for BOE '||pn_boe_id;
545 l_boe_detail_type_rec.assessable_value := NULL;
546 l_boe_detail_type_rec.tax_category_id := pn_tax_category_id;
547 l_boe_detail_type_rec.tax_amount := item_boe_detail_rec.total_tax_amount;
548 l_boe_detail_type_rec.PKG_MARKS_NUM := detail_line_rec.PKG_NUMBER;
549 l_boe_detail_type_rec.PKG_SERIAL_NUMBER := detail_line_rec.SERIAL_NUMBER;
550 dedicate_dtls_rec.boe_id := pn_boe_id;
551
552 dedicate_dtls_rec.total_tax_amount := nvl(item_boe_detail_rec.total_tax_amount,0);
553 dedicate_dtls_rec.customs := nvl(item_boe_detail_rec.customs,0);
554 dedicate_dtls_rec.additional_customs_duty := nvl(item_boe_detail_rec.additional_customs_duty,0);
555 dedicate_dtls_rec.surcharge_duty := nvl(item_boe_detail_rec.surcharge_duty,0);
556 dedicate_dtls_rec.cvd_education_cess := nvl(item_boe_detail_rec.cvd_education_cess,0);
557 dedicate_dtls_rec.customs_edu_cess := nvl(item_boe_detail_rec.customs_edu_cess,0);
558 dedicate_dtls_rec.additional_CVD := nvl(item_boe_detail_rec.additional_CVD,0);
559 dedicate_dtls_rec.customs_sh_education_cess
560 := nvl(item_boe_detail_rec.customs_sh_education_cess,0);
561 dedicate_dtls_rec.cvd_sh_edu_cess := nvl(item_boe_detail_rec.cvd_sh_edu_cess,0);
562
563 insert_boe_detail(p_boe_detail_rec => l_boe_detail_type_rec);
564 details_split(dedicate_dtls_rec
565 ,pn_tax_category_id
566 ,ln_boe_detail_id
567 ,pn_percentage);
568 Fnd_File.put_line(Fnd_File.LOG,'**Finish merge_item_boe_details for boe_id:' ||pn_boe_id || ' with boe_detail_id:' ||ln_boe_detail_id || '--- **');
569 END LOOP;
570 --CLOSE item_boe_detail_cur; --Commented by zhiwei for Bug#12777985 on 20110722
571
572 END merge_item_boe_details;
573 --==========================================================================
574
575 -- PROCEDURE NAME:
576
577 --
578
579 -- insert_boe_detail Private
580
581 --
582
583 -- DESCRIPTION:
584 --
585
586 -- This procedure will insert a record into jai_boe_details table.
587
588 -- PARAMETERS:
589
590 -- In: p_boe_detail_rec The BOE Detail line that need to be inserted.
591
592
593 -- DESIGN REFERENCES:
594
595 --
596
597 -- CHANGE HISTORY:
598
599 --
600
601 --=========================================================================
602 PROCEDURE insert_boe_detail(p_boe_detail_rec IN JAI_BOE_DETAILS%ROWTYPE) IS
603 BEGIN
604 Fnd_File.put_line(Fnd_File.LOG,' **Start insert_boe_detail for boe_detail_id:' ||p_boe_detail_rec.BOE_DETAIL_ID || '--- **');
605
606 INSERT INTO JAI_BOE_DETAILS
607 (BOE_DETAIL_ID,
608 DETAIL_LINE_NUM,
609 BOE_ID, QUANTITY,
610 UOM_CODE,
611 INVENTORY_ITEM_ID,
612 CUSTOMS_TARIFF_CODE,
613 DUTY_CODE,
614 DESCRIPTION,
615 TAX_CATEGORY_ID,
616 TAX_AMOUNT,
617 PKG_MARKS_NUM,
618 PKG_SERIAL_NUMBER,
619 CREATION_DATE,
620 CREATED_BY,
621 LAST_UPDATE_DATE,
622 LAST_UPDATED_BY,
623 LAST_UPDATE_LOGIN,
624 OBJECT_VERSION_NUMBER)
625 VALUES
626 (p_boe_detail_rec.BOE_DETAIL_ID,
627 p_boe_detail_rec.DETAIL_LINE_NUM,
628 p_boe_detail_rec.BOE_ID,
629 p_boe_detail_rec.QUANTITY,
630 p_boe_detail_rec.UOM_CODE,
631 p_boe_detail_rec.INVENTORY_ITEM_ID,
632 p_boe_detail_rec.CUSTOMS_TARIFF_CODE,
633 p_boe_detail_rec.DUTY_CODE,
634 p_boe_detail_rec.DESCRIPTION,
635 p_boe_detail_rec.TAX_CATEGORY_ID,
636 round(p_boe_detail_rec.TAX_AMOUNT,2),
637 p_boe_detail_rec.PKG_MARKS_NUM,
638 p_boe_detail_rec.PKG_SERIAL_NUMBER,
639 SYSDATE,
640 fnd_global.user_id,
641 SYSDATE,
642 fnd_global.user_id,
643 fnd_global.user_id,
644 1);
645 END insert_boe_detail;
646 --==========================================================================
647
648 -- PROCEDURE NAME:
649
650 --
651
652 -- insert_boe_detail_tax Private
653
654 --
655
656 -- DESCRIPTION:
657 --
658
659 -- This procedure will insert a record into JAI_BOE_DETAIL_TAXES table.
660
661 -- PARAMETERS:
662
663 -- In: p_boe_detail_tax_rec The BOE Detail Tax that need to be inserted.
664
665
666 -- DESIGN REFERENCES:
667
668 --
669
670 -- CHANGE HISTORY:
671
672 --
673
674 --=========================================================================
675 PROCEDURE insert_boe_detail_tax(p_boe_detail_tax_rec IN JAI_BOE_DETAIL_TAXES%ROWTYPE) IS
676 BEGIN
677 Fnd_File.put_line(Fnd_File.LOG,'**Start insert_boe_detail_tax for BOE_DETAIL_TAX_ID:'||p_boe_detail_tax_rec.BOE_DETAIL_TAX_ID || '--- **');
678 INSERT INTO JAI_BOE_DETAIL_TAXES
679 (BOE_DETAIL_TAX_ID,
680 BOE_DETAIL_ID,
681 TAX_LINE_NO,
682 TAX_ID,
683 TAX_TYPE,
684 TAX_AMOUNT,
685 APPLIED_AMOUNT,
686 INCLUSIVE_TAX_FLAG,
687 BOE_FLAG,
688 CREATION_DATE,
689 CREATED_BY,
690 LAST_UPDATE_DATE,
691 LAST_UPDATED_BY,
692 LAST_UPDATE_LOGIN,
693 OBJECT_VERSION_NUMBER)
694 VALUES
695 (p_boe_detail_tax_rec.BOE_DETAIL_TAX_ID,
696 p_boe_detail_tax_rec.BOE_DETAIL_ID,
697 p_boe_detail_tax_rec.TAX_LINE_NO,
698 p_boe_detail_tax_rec.TAX_ID,
699 p_boe_detail_tax_rec.TAX_TYPE,
700 round(p_boe_detail_tax_rec.TAX_AMOUNT,2),
701 round(p_boe_detail_tax_rec.APPLIED_AMOUNT,2),
702 'N',
703 'Y',
704 SYSDATE,
705 fnd_global.user_id,
706 SYSDATE,
707 fnd_global.user_id,
708 fnd_global.user_id,
709 1);
710 END insert_boe_detail_tax;
711 --==========================================================================
712
713 -- PROCEDURE NAME:
714
715 --
716
717 -- insert_boe_rounding Private
718
719 --
720
721 -- DESCRIPTION:
722 --
723
724 -- This procedure will insert a record into JAI_BOE_ROUNDINGS table.
725
726 -- PARAMETERS:
727
728 -- In: pn_boe_id The BOE id which need insert boe rounding records.
729
730
731 -- DESIGN REFERENCES:
732
733 --
734
735 -- CHANGE HISTORY:
736
737 --
738
739 --=========================================================================
740 PROCEDURE insert_boe_rounding(pn_boe_id IN NUMBER) IS
741 l_boe_rounding_rec JAI_BOE_ROUNDINGS%ROWTYPE;
742 ln_boe_rounding_id NUMBER;
743 CURSOR boe_dtl_tax_cur(pn_boe_id NUMBER) IS
744 SELECT
745 jbt.tax_id,
746 SUM(jbt.tax_amount) tax_amount
747 FROM jai_boe_details jbd,
748 jai_boe_detail_taxes jbt
749 WHERE jbd.boe_detail_id = jbt.boe_detail_id
750 AND jbd.boe_id = pn_boe_id
751 AND jbt.boe_flag = 'Y'
752 GROUP BY jbt.tax_id;
753
754 boe_dtl_tax_rec boe_dtl_tax_cur%ROWTYPE;
755 BEGIN
756 Fnd_File.put_line(Fnd_File.LOG,' **Start insert_boe_rounding for boe_id:' ||pn_boe_id || '--- **');
757
758 FOR boe_dtl_tax_rec IN boe_dtl_tax_cur(pn_boe_id) LOOP
759
760 SELECT jai_boe_roundings_s.NEXTVAL INTO ln_boe_rounding_id FROM dual;
761 l_boe_rounding_rec.rounding_id := ln_boe_rounding_id;
762 l_boe_rounding_rec.boe_id := pn_boe_id;
763 l_boe_rounding_rec.tax_id := boe_dtl_tax_rec.tax_id;
764 l_boe_rounding_rec.tax_amount := round(boe_dtl_tax_rec.tax_amount,2);
765 l_boe_rounding_rec.total_amount := round(boe_dtl_tax_rec.tax_amount,2);
766
767 INSERT INTO JAI_BOE_ROUNDINGS
768 (ROUNDING_ID,
769 BOE_ID,
770 TAX_ID,
771 TAX_AMOUNT,
772 ROUNDING_AMOUNT,
773 TOTAL_AMOUNT,
774 CREATION_DATE,
775 CREATED_BY,
776 LAST_UPDATE_DATE,
777 LAST_UPDATED_BY,
778 LAST_UPDATE_LOGIN,
779 OBJECT_VERSION_NUMBER)
780 VALUES
781 (l_boe_rounding_rec.rounding_id,
782 l_boe_rounding_rec.boe_id,
783 l_boe_rounding_rec.tax_id,
784 l_boe_rounding_rec.tax_amount,
785 l_boe_rounding_rec.rounding_amount,
786 l_boe_rounding_rec.total_amount,
787 SYSDATE,
788 fnd_global.user_id,
789 SYSDATE,
790 fnd_global.user_id,
791 fnd_global.user_id,
792 1);
793 END LOOP;
794
795 END insert_boe_rounding;
796 --==========================================================================
797
798 -- PROCEDURE NAME:
799
800 --
801
802 -- get_tax_id Private
803
804 --
805
806 -- DESCRIPTION:
807 --
808
809 -- This procedure will get tax id based on the tax_category_id and tax_type.
810
811 -- PARAMETERS:
812
813 -- In: pn_tax_category_id The tax category id.
814
815 -- In: pv_tax_type The tax type code.
816
817 -- Out: pn_tax_id tax id that based on the input parameters.
818
819
820 -- DESIGN REFERENCES:
821
822 --
823
824 -- CHANGE HISTORY:
825
826 --
827
828 --=========================================================================
829 PROCEDURE get_tax_id
830 (
831 pn_tax_category_id IN NUMBER
832 ,pv_tax_type IN VARCHAR2
833 ,xn_tax_id OUT NOCOPY NUMBER
834 ) IS
835 CURSOR tax_cnt_cur(v_tax_category_id NUMBER, v_tax_type VARCHAR2) IS
836 SELECT count(b.tax_id) cnt_tax
837 FROM jai_cmn_tax_ctg_lines jctl,
838 jai_cmn_taxes_all b
839 WHERE jctl.tax_category_id = v_tax_category_id
840 AND jctl.tax_id = b.tax_id
841 AND b.tax_type = v_tax_type;
842 CURSOR tax_cur(v_tax_category_id NUMBER, v_tax_type VARCHAR2) IS
843 SELECT b.tax_id,
844 b.tax_name
845 FROM jai_cmn_tax_ctg_lines jctl,
846 jai_cmn_taxes_all b
847 WHERE jctl.tax_category_id = v_tax_category_id
848 AND jctl.tax_id = b.tax_id
849 AND b.tax_type = v_tax_type;
850
851 ln_tax_cnt NUMBER;
852 tax_rec tax_cur%ROWTYPE;
853 BEGIN
854 OPEN tax_cnt_cur(pn_tax_category_id,pv_tax_type);
855 FETCH tax_cnt_cur INTO ln_tax_cnt;
856 CLOSE tax_cnt_cur;
857
858 IF ln_tax_cnt <> 1 THEN
859 Fnd_File.put_line(Fnd_File.LOG,' ** tax_category_id ' || pn_tax_category_id ||' is invalid since not only 1 tax_id for the composition of tax_category_id:' ||pn_tax_category_id ||' and tax_type : ' || pv_tax_type || '** ');
860 RAISE invalid_tax_category_tax_type;
861 END IF;
862
863 OPEN tax_cur(pn_tax_category_id,pv_tax_type);
864 FETCH tax_cur INTO tax_rec;
865 CLOSE tax_cur;
866
867 xn_tax_id := tax_rec.tax_id;
868
869 Fnd_File.put_line(Fnd_File.LOG,' **Finish get_tax_id for category_id:' ||pn_tax_category_id || ' tax_type:' || pv_tax_type ||'and get tax_id =' || xn_tax_id || ' --- **');
870
871 END get_tax_id;
872
873 --==========================================================================
874
875 -- PROCEDURE NAME:
876
877 --
878
879 -- insert_tax_wise_data Private
880
881 --
882
883 -- DESCRIPTION:
884 --
885
886 -- This procedure will insert tax wise data into DB.
887
888 -- PARAMETERS:
889
890 -- In: pn_tax_category_id The tax category id.
891
892 -- In: pv_tax_type The tax type code.
893
894 -- In: pn_boe_detail_id The boe detail id that the taxes data associated.
895
896 -- In: pn_boe_tax_line_num The boe tax line number.
897
898 -- In: pn_tax_amount The tax amount of this tax id.
899
900 -- In: pn_tax_applied_amount The applied tax amount of this tax id.
901
902 -- DESIGN REFERENCES:
903
904 --
905
906 -- CHANGE HISTORY:
907
908 --
909
910 --=========================================================================
911 PROCEDURE insert_tax_wise_data
912 (
913 pn_tax_category_id IN NUMBER
914 ,pv_tax_type IN VARCHAR2
915 ,pn_boe_detail_id IN NUMBER
916 ,pn_boe_tax_line_num IN NUMBER
917 ,pn_tax_amount IN NUMBER
918 ,pn_tax_applied_amount IN NUMBER
919 ) IS
920
921 ln_tax_id NUMBER;
922 ln_boe_tax_id NUMBER;
923 l_boe_tax_type_rec JAI_BOE_DETAIL_TAXES%ROWTYPE;
924
925 BEGIN
926 Fnd_File.put_line(Fnd_File.LOG,' **Start insert_tax_wise_data for boe_detail_id:' ||pn_boe_detail_id || '--- **');
927 get_tax_id(pn_tax_category_id => pn_tax_category_id
928 ,pv_tax_type => pv_tax_type
929 --OUT--
930 ,xn_tax_id => ln_tax_id);
931
932 SELECT jai_boe_detail_taxes_s.NEXTVAL INTO ln_boe_tax_id FROM dual;
933
934 l_boe_tax_type_rec.BOE_DETAIL_TAX_ID := ln_boe_tax_id;
935 l_boe_tax_type_rec.TAX_ID := ln_tax_id;
936 l_boe_tax_type_rec.BOE_DETAIL_ID := pn_boe_detail_id;
937 l_boe_tax_type_rec.TAX_LINE_NO := pn_boe_tax_line_num;
938 l_boe_tax_type_rec.tax_type := pv_tax_type;
939 l_boe_tax_type_rec.tax_amount := pn_tax_amount;
940 l_boe_tax_type_rec.APPLIED_AMOUNT := pn_tax_applied_amount;
941 insert_boe_detail_tax(p_boe_detail_tax_rec => l_boe_tax_type_rec);
942
943 END insert_tax_wise_data;
944 --==========================================================================
945
946 -- PROCEDURE NAME:
947
948 --
949
950 -- details_split Private
951
952 --
953
954 -- DESCRIPTION:
955 --
956
957 -- This procedure will split the dedicate BOE dettails into serveral
958 -- detail taxes based on the tax amount the dedicate details have.
959
960 -- PARAMETERS:
961
962 -- In: dedicate_dtl_rec The dedicate detail record.
963
964 -- In: pn_tax_category_id The tax category id.
965
966 -- In: pn_boe_detail_id The boe detail id that the taxes data associated.
967
968 -- In: pn_applied_percentage The percentage of applied amount.
969
970 -- DESIGN REFERENCES:
971
972 --
973
974 -- CHANGE HISTORY:
975
976 --
977
978 --=========================================================================
979 PROCEDURE details_split
980 (
981 dedicate_dtl_rec dedicate_dtls_summary_type
982 ,pn_tax_category_id NUMBER
983 ,pn_boe_detail_id NUMBER
984 ,pn_applied_percentage NUMBER
985 ) IS
986 ln_boe_tax_line_num NUMBER := 0;
987 BEGIN
988 Fnd_File.put_line(Fnd_File.LOG,' **Start details_split for boe_detail_id:' ||pn_boe_detail_id || '--- **');
989 IF nvl(dedicate_dtl_rec.customs,0) > 0 THEN
990 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
991 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
992 ,pv_tax_type => jai_constants.tax_type_customs
993 ,pn_boe_detail_id => pn_boe_detail_id
994 ,pn_boe_tax_line_num => ln_boe_tax_line_num
995 ,pn_tax_amount => dedicate_dtl_rec.customs
996 ,pn_tax_applied_amount => dedicate_dtl_rec.customs * nvl(pn_applied_percentage,0));
997
998 END IF;
999
1000 IF nvl(dedicate_dtl_rec.additional_customs_duty,0) > 0 THEN
1001 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1002 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1003 ,pv_tax_type => jai_constants.tax_type_boe_add_customs
1004 ,pn_boe_detail_id => pn_boe_detail_id
1005 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1006 ,pn_tax_amount => dedicate_dtl_rec.additional_customs_duty
1007 ,pn_tax_applied_amount => dedicate_dtl_rec.additional_customs_duty * nvl(pn_applied_percentage,0));
1008
1009 END IF;
1010
1011 IF nvl(dedicate_dtl_rec.surcharge_duty,0) > 0 THEN
1012 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1013 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1014 ,pv_tax_type => jai_constants.tax_type_boe_surcharge_duty
1015 ,pn_boe_detail_id => pn_boe_detail_id
1016 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1017 ,pn_tax_amount => dedicate_dtl_rec.surcharge_duty
1018 ,pn_tax_applied_amount => dedicate_dtl_rec.surcharge_duty * nvl(pn_applied_percentage,0));
1019 END IF;
1020
1021 IF nvl(dedicate_dtl_rec.cvd_education_cess,0) > 0 THEN
1022 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1023 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1024 ,pv_tax_type => jai_constants.tax_type_cvd_edu_cess
1025 ,pn_boe_detail_id => pn_boe_detail_id
1026 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1027 ,pn_tax_amount => dedicate_dtl_rec.cvd_education_cess
1028 ,pn_tax_applied_amount => dedicate_dtl_rec.cvd_education_cess * nvl(pn_applied_percentage,0));
1029
1030 END IF;
1031 IF nvl(dedicate_dtl_rec.customs_edu_cess,0) > 0 THEN
1032 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1033 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1034 ,pv_tax_type => jai_constants.tax_type_customs_edu_cess
1035 ,pn_boe_detail_id => pn_boe_detail_id
1036 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1037 ,pn_tax_amount => dedicate_dtl_rec.customs_edu_cess
1038 ,pn_tax_applied_amount => dedicate_dtl_rec.customs_edu_cess * nvl(pn_applied_percentage ,0));
1039 END IF;
1040 IF nvl(dedicate_dtl_rec.additional_CVD,0) > 0 THEN
1041 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1042 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1043 ,pv_tax_type => jai_constants.tax_type_add_cvd
1044 ,pn_boe_detail_id => pn_boe_detail_id
1045 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1046 ,pn_tax_amount => dedicate_dtl_rec.additional_CVD
1047 ,pn_tax_applied_amount => dedicate_dtl_rec.additional_CVD * nvl(pn_applied_percentage ,0));
1048
1049 END IF;
1050
1051 IF nvl(dedicate_dtl_rec.customs_sh_education_cess,0) > 0 THEN
1052 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1053 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1054 ,pv_tax_type => jai_constants.tax_type_sh_customs_edu_Cess
1055 ,pn_boe_detail_id => pn_boe_detail_id
1056 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1057 ,pn_tax_amount => dedicate_dtl_rec.customs_sh_education_cess
1058 ,pn_tax_applied_amount => dedicate_dtl_rec.customs_sh_education_cess * nvl(pn_applied_percentage ,0));
1059
1060 END IF;
1061
1062 IF nvl(dedicate_dtl_rec.cvd_sh_edu_cess,0) > 0 THEN
1063 ln_boe_tax_line_num := ln_boe_tax_line_num + 1;
1064 insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
1065 ,pv_tax_type => jai_constants.tax_type_sh_cvd_edu_cess
1066 ,pn_boe_detail_id => pn_boe_detail_id
1067 ,pn_boe_tax_line_num => ln_boe_tax_line_num
1068 ,pn_tax_amount => dedicate_dtl_rec.cvd_sh_edu_cess
1069 ,pn_tax_applied_amount => dedicate_dtl_rec.cvd_sh_edu_cess * nvl(pn_applied_percentage,0));
1070
1071 END IF;
1072
1073 END details_split;
1074
1075 --==========================================================================
1076
1077 -- PROCEDURE NAME:
1078
1079 --
1080
1081 -- validation_boe Private
1082
1083 --
1084
1085 -- DESCRIPTION:
1086 --
1087
1088 -- This procedure will do validation for the input parameters.
1089
1090
1091 -- PARAMETERS:
1092
1093 -- In: pn_boe_id The Id of dedicate BOE.
1094
1095 -- In: pn_tax_category_id The tax category id.
1096
1097 -- In: pv_migration_type migration type for indicating if process accounting.
1098
1099 -- In: pd_migration_date The GL date for creating accounting entries.
1100
1101 -- Out: pv_valid_flag Indicate that whether the parameters are valid.
1102
1103 -- DESIGN REFERENCES:
1104
1105 --
1106
1107 -- CHANGE HISTORY:
1108
1109 --
1110
1111 --=========================================================================
1112 PROCEDURE validation_boe
1113 (
1114 pn_org_id IN NUMBER
1115 ,pn_boe_id IN NUMBER
1116 ,pn_tax_category_id IN NUMBER
1117 ,xv_valid_flag OUT NOCOPY VARCHAR2
1118 ) IS
1119
1120 CURSOR boe_cur(ln_boe_id NUMBER) IS
1121 SELECT hdr.boe_id,
1122 hdr.BOE_AMOUNT,
1123 hdr.AMOUNT_APPLIED,
1124 hdr.AMOUNT_WRITTEN_OFF,
1125 hdr.status,
1126 hdr.migration_flag,
1127 OU.ORGANIZATION_ID OU_ID
1128 FROM JAI_CMN_BOE_HDRS_ALL hdr,
1129 org_organization_definitions INV,
1130 hr_all_organization_units OU
1131 WHERE INV.operating_unit = OU.organization_id
1132 AND INV.organization_id = hdr.organization_id
1133 AND hdr.boe_id = ln_boe_id;
1134
1135 CURSOR tax_category_cur(ln_tax_category_id NUMBER) IS
1136 SELECT org_id
1137 FROM jai_cmn_tax_ctgs_all
1138 WHERE tax_category_id = ln_tax_category_id;
1139
1140 boe_rec boe_cur%ROWTYPE;
1141 ln_org_id_tax_category NUMBER;
1142 ln_available_amount NUMBER := 0;
1143 BEGIN
1144 Fnd_File.put_line(Fnd_File.LOG,' **Start validation_boe for boe_id:' || pn_boe_id || '--- **');
1145 xv_valid_flag := 'Y';
1146 OPEN boe_cur(pn_boe_id);
1147 FETCH boe_cur INTO boe_rec;
1148 CLOSE boe_cur;
1149
1150 OPEN tax_category_cur(pn_tax_category_id);
1151 FETCH tax_category_cur INTO ln_org_id_tax_category;
1152 CLOSE tax_category_cur;
1153
1154 IF boe_rec.boe_id IS NULL THEN
1155 xv_valid_flag := 'N';
1156 Fnd_File.put_line(Fnd_File.LOG,' ** boe_id ' || pn_boe_id ||' does not exist. ** ');
1157 RETURN;
1158 END IF;--boe_rec.boe_id IS NULL
1159
1160 IF boe_rec.ou_id <> pn_org_id THEN
1161 xv_valid_flag := 'N';
1162 Fnd_File.put_line(Fnd_File.LOG,' ** boe_id ' || pn_boe_id || 'with org_id '||boe_rec.ou_id ||' is not in the default ou'||pn_org_id ||' , no need to migrate. ** ');
1163 RETURN;
1164 END IF;-- boe_rec.ou_id <> pn_org_id
1165
1166 IF ln_org_id_tax_category <> pn_org_id THEN
1167 xv_valid_flag := 'N';
1168 Fnd_File.put_line(Fnd_File.LOG,' ** tax_category_id ' || pn_tax_category_id || ' is not in the default ou'||pn_org_id ||' , no need to migrate. ** ');
1169 RETURN;
1170 END IF;--ln_org_id_tax_category <> pn_org_id
1171
1172 IF nvl(boe_rec.AMOUNT_WRITTEN_OFF,0) > 0 THEN
1173 xv_valid_flag := 'N';
1174 Fnd_File.put_line(Fnd_File.LOG,' ** boe_id ' || pn_boe_id ||' has been written off, no need to migrate. ** ');
1175 RETURN;
1176 END IF;--nvl(boe_rec.AMOUNT_WRITTEN_OFF,0) > 0
1177
1178 ln_available_amount := nvl(boe_rec.BOE_AMOUNT,0) - nvl(boe_rec.amount_applied,0);
1179
1180 IF ln_available_amount = 0 THEN
1181 xv_valid_flag := 'N';
1182 Fnd_File.put_line(Fnd_File.LOG,' ** boe_id ' || pn_boe_id ||' has no available amount but migration type is unapplied. ** ');
1183 RETURN;
1184 END IF;-- ln_available_amount = 0
1185
1186 IF boe_rec.status IS NOT NULL OR boe_rec.migration_flag IS NOT NULL THEN
1187 xv_valid_flag := 'N';
1188 Fnd_File.put_line(Fnd_File.LOG,' ** boe_id ' || pn_boe_id ||' is not a dedicate BOE. ** ');
1189 RETURN;
1190 END IF; --boe_rec.status IS NOT NULL OR boe_rec.migration_flag IS NOT NULL
1191 IS_BOE_TAX_Category(pn_tax_category_id
1192 ,xv_valid_flag);
1193 IF xv_valid_flag = 'N' THEN
1194 Fnd_File.put_line(Fnd_File.LOG,' ** invalid tax_category_id. ** ');
1195 RETURN;
1196 END IF; --pv_valid_flag = 'N'
1197
1198
1199 END validation_boe;
1200 --==========================================================================
1201
1202 -- PROCEDURE NAME:
1203
1204 --
1205
1206 -- is_boe_tax_category Private
1207
1208 --
1209
1210 -- DESCRIPTION:
1211 --
1212
1213 -- This procedure will check if the tax codes that the cateogry covered are BOE taxes,
1214
1215 -- and only one tax code for each tax type.
1216
1217
1218 -- PARAMETERS:
1219
1220 -- In: pn_tax_category_id The tax category id.
1221
1222 -- Out: pv_valid_flag Indicate that whether the tax category id is valid.
1223
1224 -- DESIGN REFERENCES:
1225
1226 --
1227
1228 -- CHANGE HISTORY:
1229
1230 --
1231
1232 --=========================================================================
1233 PROCEDURE is_boe_tax_category
1234 (
1235 pn_tax_category_id IN NUMBER
1236 ,xv_valid_flag OUT NOCOPY VARCHAR2
1237 ) IS
1238 CURSOR tax_category_cur(ln_tax_category_id NUMBER) IS
1239 SELECT jitc.tax_category_id,
1240 jctl.tax_id, b.tax_type,
1241 rttv.regime_code
1242 FROM jai_cmn_tax_ctgs_all jitc,
1243 jai_cmn_tax_ctg_lines jctl,
1244 jai_cmn_taxes_all b,
1245 jai_regime_tax_types_v rttv
1246 WHERE jitc.tax_category_id = jctl.tax_category_id
1247 AND jctl.tax_id = b.tax_id
1248 AND rttv.tax_type = b.tax_type
1249 AND jitc.tax_category_id = ln_tax_category_id;
1250
1251 CURSOR tax_id_cnt_cur(ln_tax_category_id NUMBER) IS
1252 SELECT jitc.tax_category_id,
1253 b.tax_type,
1254 COUNT(jctl.tax_id) cnt_tax_id
1255 FROM jai_cmn_tax_ctgs_all jitc,
1256 jai_cmn_tax_ctg_lines jctl,
1257 jai_cmn_taxes_all b,
1258 jai_regime_tax_types_v rttv
1259 WHERE jitc.tax_category_id = jctl.tax_category_id
1260 AND jctl.tax_id = b.tax_id
1261 AND rttv.tax_type = b.tax_type
1262 AND rttv.regime_code = jai_constants.customs_regime
1263 AND jitc.tax_category_id = ln_tax_category_id
1264 GROUP BY jitc.tax_category_id, b.tax_type, rttv.regime_code;
1265
1266 tax_category_rec tax_category_cur%ROWTYPE;
1267 tax_id_cnt_rec tax_id_cnt_cur%ROWTYPE;
1268 ln_cnt NUMBER := 0;
1269
1270
1271 BEGIN
1272 Fnd_File.put_line(Fnd_File.LOG,' **Start is_boe_tax_Category for tax_category_id:' ||pn_tax_category_id || '--- **');
1273 xv_valid_flag := 'Y';
1274
1275 FOR tax_category_rec IN tax_category_cur(pn_tax_category_id) LOOP
1276 ln_cnt := ln_cnt + 1;
1277 IF tax_category_rec.regime_code <> jai_constants.customs_regime THEN
1278 xv_valid_flag := 'N';
1279 Fnd_File.put_line(Fnd_File.LOG,' ** tax_category_id ' || pn_tax_category_id ||'has non customs tax_id ' ||tax_category_rec.tax_id || '** ');
1280 EXIT;
1281 END IF;
1282 END LOOP;
1283
1284 IF ln_cnt = 0 THEN
1285 xv_valid_flag := 'N';
1286 Fnd_File.put_line(Fnd_File.LOG,' ** tax_category_id ' || pn_tax_category_id ||' does not exist. ** ');
1287 RETURN;
1288 END IF;
1289
1290 FOR tax_id_cnt_rec IN tax_id_cnt_cur(pn_tax_category_id) LOOP
1291 IF tax_id_cnt_rec.cnt_tax_id > 1 THEN
1292 xv_valid_flag := 'N';
1293 Fnd_File.put_line(Fnd_File.LOG,' ** tax_category_id ' || pn_tax_category_id ||' has more than 1 tax_id for the same tax_type. ** ');
1294 EXIT;
1295 END IF;
1296 END LOOP;
1297 Fnd_File.put_line(Fnd_File.LOG,' **Finished is_boe_tax_Category');
1298 END is_boe_tax_Category;
1299 --==========================================================================
1300
1301 -- PROCEDURE NAME:
1302
1303 --
1304
1305 -- is_boe_tax_category Private
1306
1307 --
1308
1309 -- DESCRIPTION:
1310 --
1311
1312 -- This procedure will check if the provided date is in open period.
1313
1314
1315
1316 -- PARAMETERS:
1317
1318 -- In: pd_date The GL date to be checked.
1319
1320 -- In: pn_ou_id The org id of the BOE.
1321
1322 -- Out: pv_open_flag Indicate that whether the GL date is in open period.
1323
1324 -- DESIGN REFERENCES:
1325
1326 --
1327
1328 -- CHANGE HISTORY:
1329
1330 --
1331
1332 --=========================================================================
1333 PROCEDURE is_in_open_period
1334 (
1335 pd_date DATE
1336 ,pn_ou_id NUMBER
1337 ,xv_open_flag OUT NOCOPY VARCHAR2
1338 ) IS
1339 ln_count NUMBER;
1340
1341
1342 CURSOR ledger_cur(v_org_id NUMBER) IS
1343 SELECT set_of_books_id ledger_id
1344 FROM hr_operating_units
1345 WHERE organization_id = v_org_id;
1346 ln_ledger_id NUMBER;
1347
1348 BEGIN
1349 Fnd_File.put_line(Fnd_File.LOG,' **Start is_in_open_period for date:' || pd_date ||'--- **');
1350 xv_open_flag := 'Y';
1351
1352 OPEN ledger_cur(pn_ou_id);
1353 FETCH ledger_cur INTO ln_ledger_id;
1354 CLOSE ledger_cur;
1355
1356 Fnd_File.put_line(Fnd_File.LOG,'ledger id is:'||ln_ledger_id);
1357
1358 SELECT COUNT(1)
1359 INTO ln_count
1360 FROM gl_period_statuses_v gpsv,
1361 fnd_application fa
1362 WHERE gpsv.application_id = fa.application_id
1363 AND gpsv.closing_status = 'O'
1364 AND pd_date BETWEEN gpsv.start_date AND NVL(gpsv.end_date,pd_date)
1365 AND fa.application_short_name = 'SQLGL'
1366 AND gpsv.set_of_books_id = ln_ledger_id;
1367
1368 Fnd_File.put_line(Fnd_File.LOG,'count of open period records:'||ln_count);
1369 IF ln_count = 0 THEN
1370 xv_open_flag := 'N';
1371 END IF;--ln_count = 0
1372 END is_in_open_period;
1373 --==========================================================================
1374
1375 -- PROCEDURE NAME:
1376
1377 --
1378
1379 -- accounting_migration Private
1380
1381 --
1382
1383 -- DESCRIPTION:
1384 --
1385
1386 -- This procedure will migrate accounting entries for the BOE..
1387
1388
1389
1390 -- PARAMETERS:
1391
1392 -- In: pn_boe_id The BOE_ID that need to do accounting migration.
1393
1394 -- In: pn_availabe_boe_amount The available BOE amount that need to be migrated.
1395
1396 -- Out: pd_migration_date The GL date for creating GL entries.
1397
1398 -- DESIGN REFERENCES:
1399
1400 --
1401
1402 -- CHANGE HISTORY:
1403
1404 --
1405
1406 --=========================================================================
1407 PROCEDURE accounting_migration
1408 (
1409 pn_boe_id NUMBER
1410 ,pn_availabe_boe_amount NUMBER
1411 ,pd_migration_date DATE
1412 ) IS
1413
1414 ln_boe_account_ccid NUMBER;
1415 ln_organization_id NUMBER;
1416 ln_location_id NUMBER;
1417 ln_tax_account_ccid NUMBER;
1418 lv_reference10 gl_interface.reference10%TYPE;
1419 lv_reference23 gl_interface.reference23%TYPE;
1420 lv_reference24 gl_interface.reference24%TYPE;
1421 lv_reference25 gl_interface.reference25%TYPE;
1422 lv_reference26 gl_interface.reference26%TYPE;
1423
1424 CURSOR boe_account_cur(v_boe_id NUMBER) IS
1425 SELECT org.boe_account_id boe_account,
1426 hdr.organization_id,
1427 hdr.location_id
1428 FROM JAI_CMN_INVENTORY_ORGS org,
1429 jai_cmn_boe_hdrs_all hdr
1430 WHERE org.organization_id = hdr.organization_id
1431 AND org.location_id = hdr.location_id
1432 AND hdr.boe_id = v_boe_id;
1433
1434 CURSOR tax_type_amount_cur(v_boe_id NUMBER) IS
1435 SELECT jbt.tax_type,
1436 SUM(jbt.tax_amount) tax_amt,
1437 SUM(jbt.applied_amount) applied_amt,
1438 (SUM(jbt.tax_amount) - SUM(jbt.applied_amount)) available_amt
1439 FROM jai_boe_details jbd,
1440 jai_boe_detail_taxes jbt
1441 WHERE jbd.boe_id = v_boe_id
1442 AND jbd.boe_detail_id = jbt.boe_detail_id
1443 GROUP BY jbt.tax_type;
1444
1445 boe_account_rec boe_account_cur%ROWTYPE;
1446 tax_type_amount_rec tax_type_amount_cur%ROWTYPE;
1447 BEGIN
1448 lv_reference10 := 'India Local BOE system tax break up for migrated BOE Id ' || pn_boe_id;
1449 lv_reference23 := 'JAINBOEI';
1450 lv_reference24 := 'ja_in_boe_tax_break';
1451 lv_reference25 := 'BOE_ID';
1452 lv_reference26 := pn_boe_id;
1453
1454 OPEN boe_account_cur(pn_boe_id);
1455 FETCH boe_account_cur INTO boe_account_rec;
1456 CLOSE boe_account_cur;
1457 ln_organization_id := boe_account_rec.organization_id;
1458 ln_location_id := boe_account_rec.location_id;
1459 ln_boe_account_ccid := boe_account_rec.boe_account;
1460
1461 --Credit Entry
1462 jai_cmn_gl_pkg.create_gl_entry(ln_organization_id
1463 ,'INR'
1464 ,pn_availabe_boe_amount
1465 ,0 --debit_amount
1466 ,ln_boe_account_ccid --Get from Organization Additional Information
1467 ,'Payables India'
1468 ,'Bill of Entry India'
1469 ,fnd_global.user_id
1470 ,pd_migration_date
1471 ,NULL
1472 ,NULL
1473 ,1
1474 ,lv_reference10
1475 ,lv_reference23
1476 ,lv_reference24
1477 ,lv_reference25
1478 ,lv_reference26);
1479
1480
1481 FOR tax_type_amount_rec IN tax_type_amount_cur(pn_boe_id) LOOP
1482 ln_tax_account_ccid := jai_boe_general_pkg.get_boe_accounting(tax_type_amount_rec.tax_type
1483 ,'PAID_PAYABLES'
1484 ,ln_organization_id
1485 ,ln_location_id);
1486 IF ln_tax_account_ccid IS NOT NULL THEN
1487 --Debt Entry
1488 jai_cmn_gl_pkg.create_gl_entry(ln_organization_id
1489 ,'INR'
1490 ,0 --credit amount
1491 ,tax_type_amount_rec.available_amt
1492 ,ln_tax_account_ccid --Get from Regime setup.
1493 ,'Payables India'
1494 ,'Bill of Entry India'
1495 ,fnd_global.user_id
1496 ,pd_migration_date
1497 ,NULL
1498 ,NULL
1499 ,1
1500 ,lv_reference10
1501 ,lv_reference23
1502 ,lv_reference24
1503 ,lv_reference25
1504 ,lv_reference26);
1505 ELSE
1506 Fnd_File.put_line(Fnd_File.LOG,'Paid_payables account is not setup for tax type:'||tax_type_amount_rec.tax_type);
1507 END IF; --ln_tax_account_ccid IS NOT NULL
1508
1509 END LOOP;
1510
1511 END accounting_migration;
1512
1513 END JAI_BOE_MIGRATION_PKG;