[Home] [Help]
PACKAGE BODY: APPS.JAI_BOE_GENERAL_PKG
Source
1 PACKAGE BODY jai_boe_general_pkg AS
2 /* $Header: jai_boe_general.plb 120.10 2011/07/22 10:04:03 zhhou noship $ */
3
4 /* ===============================================
5 -- FUNCTION NAME:
6 -- get_boe_accounting
7 -- DESCRIPTION:
8
9 -- Get the ccid, firstly get ccid by organization + location + taxtype/taxcode+regime ,
10 -- secondly get ccid by regime.
11 -- In: pv_tax_type_code The tax type code which need to get account.
12 -- In: pv_account_type Account Type, e.g PAID_PAYABLES, WRITE_OFf.
13 -- In: pn_organization_id The organization id of tax type attached.
14 -- In: pn_location_id The location id of tax type attached.
15 -- HISTORY:
16 -- 1.00 21-Dec-2010 Wenqiong Zhou Creation
17 * ================================================*/
18 FUNCTION get_boe_accounting(pv_tax_type_code IN VARCHAR2
19 ,pv_account_type IN VARCHAR2
20 ,pn_organization_id IN NUMBER
21 ,pn_location_id IN NUMBER) RETURN NUMBER IS
22 CURSOR get_account_setup(cn_regime_id IN NUMBER) IS
23 SELECT to_number(nvl(b.attribute_value, a.attribute_value)) attribute_value
24 FROM (SELECT jrp.organization_id organization_id,
25 acc.attribute_value attribute_value,
26 jrp.regime_org_id
27 FROM jai_rgm_parties jrp,
28 jai_rgm_registrations jrr,
29 jai_rgm_definitions jrg,
30 jai_rgm_registrations acc
31 WHERE 1 = 1
32 AND jrg.regime_id = jrp.regime_id
33 AND jrg.regime_id = cn_regime_id --
34 AND jrg.regime_id = jrr.regime_id
35 AND acc.attribute_code = pv_account_type
36 AND jrr.registration_type = 'TAX_TYPES'
37 AND acc.registration_type = 'ACCOUNTS'
38 AND acc.parent_registration_id = jrr.registration_id
39 AND jrr.attribute_code = pv_tax_type_code --'CVD', tax type code
40 AND JRP.LOCATION_ID = pn_location_id
41 ) a,
42 (SELECT jrp.organization_id organization_id,
43 jpr.attribute_value attribute_value,
44 jrp.regime_org_id
45 FROM jai_rgm_parties jrp,
46 jai_rgm_registrations jrr,
47 jai_rgm_registrations acc,
48 jai_rgm_party_regns jpr,
49 jai_rgm_definitions jrg
50 WHERE jrg.regime_id = jrp.regime_id
51 AND jrg.regime_id = cn_regime_id
52 AND jrg.regime_id = jrr.regime_id
53 AND acc.attribute_code = pv_account_type
54 AND jrr.registration_type = 'TAX_TYPES'
55 AND acc.registration_type = 'ACCOUNTS'
56 AND acc.parent_registration_id = jrr.registration_id
57 AND jrr.attribute_code = pv_tax_type_code
58 AND jrp.regime_org_id = jpr.regime_org_id
59 AND jpr.registration_id = acc.registration_id
60 AND jrp.location_id = pn_location_id) b
61 WHERE a.organization_id = b.organization_id(+)
62 AND a.organization_id = pn_organization_id;
63
64 --Added by zhiwei for Bug#12781845 begin
65 ------------------------------------------------------
66 CURSOR get_account_without_org(cn_regime_id IN NUMBER)
67 IS
68 SELECT
69 acc.attribute_value attribute_value
70
71 FROM
72 jai_rgm_registrations jrr,
73 jai_rgm_definitions jrg,
74 jai_rgm_registrations acc
75 WHERE 1 = 1
76
77 AND jrg.regime_id = cn_regime_id
78 AND jrg.regime_id = jrr.regime_id
79 AND acc.attribute_code = pv_account_type --'PAID_PAYABLES'
80 AND jrr.registration_type = 'TAX_TYPES'
81 AND acc.registration_type = 'ACCOUNTS'
82 AND acc.parent_registration_id = jrr.registration_id
83 AND jrr.attribute_code = pv_tax_type_code ;--'ADDITIONAL_CUSTOMS'
84
85 ------------------------------------------------------
86 --Added by zhiwei for Bug#12781845 end
87
88 ln_regime_id jai_rgm_registrations.regime_id%TYPE;
89 ln_ccid NUMBER;
90 v_debug CHAR(1);
91 BEGIN
92 v_debug := 'Y';
93
94 --Get the regime id
95
96 SELECT regime_id
97 INTO ln_regime_id
98 FROM jai_rgm_definitions
99 WHERE regime_code = jai_constants.customs_regime;
100
101 OPEN get_account_setup(ln_regime_id);
102 FETCH get_account_setup
103 INTO ln_ccid;
104 CLOSE get_account_setup;
105
106 --Added by zhiwei for Bug#12781845 begin
107 ------------------------------------------------------
108 if nvl(ln_ccid,0) = 0 then
109
110 OPEN get_account_without_org(ln_regime_id);
111 FETCH get_account_without_org
112 INTO ln_ccid;
113 CLOSE get_account_without_org;
114
115 end if;
116 ------------------------------------------------------
117 --Added by zhiwei for Bug#12781845 end
118
119 IF v_debug = 'Y' THEN
120 fnd_file.put_line(fnd_file.log,
121 'get ccid: = ' || ln_ccid || 'BY REGIMEID:' ||
122 ln_regime_id);
123
124 END IF;
125 RETURN ln_ccid;
126
127 END get_boe_accounting;
128
129 /* ===============================================
130 -- FUNCTION NAME:
131 -- get_depb_account
132 -- DESCRIPTION:
133
134 -- Get the DEPB account id of the CUSTOMS regime.
135 -- RETURN: DEPB account id.
136 -- HISTORY:
137 -- 1.00 21-Dec-2010 Wenqiong Zhou Creation
138 * ================================================*/
139 FUNCTION get_depb_account
140 RETURN NUMBER IS
141
142 CURSOR get_others_attribute_value(cn_regime_id IN NUMBER) IS
143 SELECT to_number(attribute_value)
144 FROM jai_rgm_registrations
145 WHERE regime_id = cn_regime_id
146 AND registration_type = 'ACCOUNTS'
147 AND attribute_code = 'DEPB';
148
149 ln_regime_id jai_rgm_registrations.regime_id%TYPE;
150 ln_depb_account_id NUMBER;
151 v_debug CHAR(1);
152
153 BEGIN
154 v_debug := jai_constants.no;
155
156 SELECT regime_id
157 INTO ln_regime_id
158 FROM jai_rgm_definitions
159 WHERE regime_code = 'CUSTOMS';
160
161 OPEN get_others_attribute_value(ln_regime_id);
162 FETCH get_others_attribute_value
163 INTO ln_depb_account_id;
164 CLOSE get_others_attribute_value;
165
166 IF ln_depb_account_id IS NULL THEN
167 IF v_debug = 'Y' THEN
168 fnd_file.put_line(fnd_file.log,
169 ' Can not get regime DEPB Account by regime id ' ||
170 ln_regime_id );
171 END IF;
172 END IF;
173
174 RETURN ln_depb_account_id;
175
176 END get_depb_account;
177
178
179 --==========================================================================
180 -- PROCEDURE NAME:
181 --
182 -- ap_void_check Public
183 --
184 -- DESCRIPTION:
185 --
186 -- This procedure is written that would check Bill of Entry invoice is applied or not.
187 -- If the BOE invoice is applied, the payment for the invoice will not be voided
188 -- PARAMETERS:
189 -- In: pn_check_id Pass the check id for the payment
190 -- Out: xv_return_code Indicates the return code,
191 -- 'Y': the BOE inovice has been applied
192 -- 'N': the BOE inovice has not been applied
193 -- 'E' : for errors
194 -- xv_return_msg Indicates the return message
195 --
196 --
197 -- DESIGN REFERENCES:
198 --
199 -- CHANGE HISTORY:
200 --
201 --=========================================================================
202 PROCEDURE ap_void_check(pn_check_id IN NUMBER
203 ,xv_return_code OUT NOCOPY VARCHAR2
204 ,xv_return_mesg OUT NOCOPY VARCHAR2) IS
205 CURSOR get_invoice_info_cur(pn_check_id IN NUMBER) IS
206 SELECT ai.invoice_id,
207 ai.application_id,
208 ai.product_table,
209 ai.reference_key1,
210 ai.reference_key2,
211 ai.reference_key3,
212 ai.reference_key4,
213 ai.reference_key5
214 FROM ap_invoices_all ai, ap_invoice_payments_all aip
215 WHERE ai.invoice_id = aip.invoice_id
216 AND aip.check_id = pn_check_id;
217
218 lv_return_code VARCHAR2(1);
219 lv_return_mesg VARCHAR2(240);
220 lv_boe_payment_flag VARCHAR2(1);
221 BEGIN
222 lv_boe_payment_flag := check_boe_payment(pn_check_id);
223
224 IF lv_boe_payment_flag = 'N' THEN
225 xv_return_code := 'E';
226 xv_return_mesg := 'The payment is NOT generated by BOE invoice payment';
227 RETURN;
228 END IF;
229
230 FOR invoice_info_rec IN get_invoice_info_cur(pn_check_id) LOOP
231
232 get_boe_applied_status(pn_application_id => invoice_info_rec.application_id,
233 pv_product_table => invoice_info_rec.product_table,
234 pv_reference_key1 => invoice_info_rec.reference_key1,
235 pv_reference_key2 => invoice_info_rec.reference_key2,
236 pv_reference_key3 => invoice_info_rec.reference_key3,
237 pv_reference_key4 => invoice_info_rec.reference_key4,
238 pv_reference_key5 => invoice_info_rec.reference_key5,
239 xv_return_code => lv_return_code,
240 xv_return_mesg => lv_return_mesg);
241
242 xv_return_code := lv_return_code;
243 xv_return_mesg := lv_return_mesg;
244
245 IF lv_return_code <> 'N' THEN
246 RETURN;
247 END IF;
248 END LOOP;
249 EXCEPTION
250 WHEN OTHERS THEN
251 xv_return_code := 'E';
252 xv_return_mesg := 'Errors in the procedure jai_ap_utils_pkg.ap_void_check. ' ||
253 SQLCODE || ':' || SQLERRM;
254 END ap_void_check;
255
256 --==========================================================================
257 -- PROCEDURE NAME:
258 --
259 -- get_boe_applied_status Public
260 --
261 -- DESCRIPTION:
262 --
263 -- This procedure is written that will get the status of the BOE so that
264 -- judge the BOE invoice if voided or not
265 --
266 -- PARAMETERS:
267 -- In: pn_application_id Identfier of JA product
268 -- In: pv_product_table The product table name for the reference table
269 -- In: pv_reference_key1 reference columns
270 -- In: pv_reference_key2 reference columns
271 -- In: pv_reference_key3 reference columns
272 -- In: pv_reference_key4 reference columns
273 -- In: pv_reference_key5 reference columns
274 -- Out: xv_return_code Indicates the return code,
275 -- 'Y': the BOE inovice has been applied
276 -- 'N': the BOE inovice has not been applied
277 -- 'E' : for errors
278 -- xv_return_msg Indicates the return message
279 --
280 --
281 -- DESIGN REFERENCES:
282 --
283 -- CHANGE HISTORY:
284 --
285 --=========================================================================
286
287 PROCEDURE get_boe_applied_status(pn_application_id IN NUMBER
288 ,pv_product_table IN VARCHAR2
289 ,pv_reference_key1 IN VARCHAR2
293 ,pv_reference_key5 IN VARCHAR2 DEFAULT NULL
290 ,pv_reference_key2 IN VARCHAR2 DEFAULT NULL
291 ,pv_reference_key3 IN VARCHAR2 DEFAULT NULL
292 ,pv_reference_key4 IN VARCHAR2 DEFAULT NULL
294 ,xv_return_code OUT NOCOPY VARCHAR2
295 ,xv_return_mesg OUT NOCOPY VARCHAR2)
296 IS
297 CURSOR get_boe_status_cur(pn_boe_id IN NUMBER)
298 IS
299 SELECT 'APPLIED' boe_status, jbh.boe_id
300 FROM jai_cmn_boe_hdrs jbh
301 WHERE jbh.boe_id = pn_boe_id
302 -- AND jbh.status = 'APPLIED' Comment out the condition for bug#12673597.
303 AND nvl(jbh.amount_applied, 0) > 0;
304
305 lv_boe_status VARCHAR2(10);
306 lv_boe_id NUMBER(15);
307 BEGIN
308
309 OPEN get_boe_status_cur(to_number(pv_reference_key1));
310 FETCH get_boe_status_cur
311 INTO lv_boe_status, lv_boe_id;
312 CLOSE get_boe_status_cur;
313
314 IF lv_boe_status = 'APPLIED' THEN
315 xv_return_code := 'Y';
316 xv_return_mesg := 'The BOE invoice: ' || lv_boe_id ||
317 'has already been applied!';
318 ELSE
319 xv_return_code := 'N';
320 xv_return_mesg := 'The BOE invoice: ' || lv_boe_id ||
321 'has not been applied!';
322 END IF;
323 EXCEPTION
324 WHEN OTHERS THEN
325 xv_return_code := 'E';
326 xv_return_mesg := 'Errors in the procedure jai_ap_utils_pkg.get_boe_applied_status' ||
327 SQLCODE || ':' || SQLERRM;
328
329 END get_boe_applied_status;
330
331 --==========================================================================
332 -- FUNCTION NAME:
333 --
334 -- check_boe_payment Public
335 --
336 -- DESCRIPTION:
337 --
338 -- This procedure is written that check the AP invoice is generated by the
339 -- BOE invoice or not
340 --
341 -- PARAMETERS:
342 -- In: pn_check_id Identfier AP payment
343 --
344 -- RETURN VALUE
345 -- 'Y' The payment is generated by BOE invoice payment
346 -- 'N' The payment is NOT generated by BOE invoice payment
347 --
348 -- DESIGN REFERENCES:
349 --
350 -- CHANGE HISTORY:
351 --
352 --=========================================================================
353 FUNCTION check_boe_payment(pn_check_id IN NUMBER) RETURN VARCHAR2
354 IS
355 CURSOR get_invoice_info_cur IS
356 SELECT ai.application_id,
357 ai.product_table,
358 ai.reference_key1,
359 ai.reference_key2,
360 ai.reference_key3,
361 ai.reference_key4,
362 ai.reference_key5
363 FROM ap_invoices_all ai, ap_invoice_payments_all aip
364 WHERE ai.invoice_id = aip.invoice_id
365 AND aip.check_id = pn_check_id;
366
367 ln_applicaton_id ap_invoices_all.application_id%TYPE;
368 lv_product_table ap_invoices_all.product_table%TYPE;
369 lv_reference_key1 ap_invoices_all.reference_key1%TYPE;
370 lv_reference_key2 ap_invoices_all.reference_key2%TYPE;
371 lv_reference_key3 ap_invoices_all.reference_key3%TYPE;
372 lv_reference_key4 ap_invoices_all.reference_key4%TYPE;
373 lv_reference_key5 ap_invoices_all.reference_key5%TYPE;
374 lv_return VARCHAR2(1);
375
376 BEGIN
377 OPEN get_invoice_info_cur;
378 FETCH get_invoice_info_cur
379 INTO ln_applicaton_id, lv_product_table, lv_reference_key1, lv_reference_key2, lv_reference_key3, lv_reference_key4, lv_reference_key5;
380 CLOSE get_invoice_info_cur;
381
382 IF nvl(ln_applicaton_id, -1) <> 7000 OR
383 nvl(lv_product_table, '###') <> 'JAI_CMN_BOE_HDRS' OR
384 lv_reference_key1 IS NULL
385 THEN
386 lv_return := 'N';
387 ELSE
388 lv_return := 'Y';
389 END IF;
390 RETURN lv_return;--Added by Wenqiong for Bug#12592954.
391 END check_boe_payment;
392
393
394 --==========================================================================
395 -- FUNCTION NAME:
396 --
397 -- chk_boe_tax Public
398 --
399 -- DESCRIPTION:
400 --
401 -- This function is written that check the tax type of tax belongs to
402 -- BOE or not
403 --
404 -- PARAMETERS:
405 -- In: pv_tax_type tax type
406 --
407 -- RETURN VALUE
408 -- 1 The tax type belongs to BOE Tax type
409 -- 0 The tax type don't belongs to BOE Tax type
410 --
411 -- DESIGN REFERENCES:
412 --
413 -- CHANGE HISTORY:
414 --
415 --=========================================================================
416 FUNCTION chk_boe_tax
417 (
418 pv_tax_type IN VARCHAR2
419 )RETURN number IS
420
421 ln_flag number; --0 indicate is not boe tax , 1 indicate belongs to boe tax.
422 BEGIN
423 if(UPPER(nvl(pv_tax_type,'###')) in
424 ('CVD',
425 jai_constants.tax_type_add_cvd ,
426 'CUSTOMS',
427 jai_constants.tax_type_cvd_Edu_cess,
428 jai_constants.tax_type_customs_edu_cess ,
429 jai_constants.tax_type_sh_customs_edu_cess,
430 jai_constants.tax_type_sh_cvd_edu_cess,
431 jai_constants.tax_type_boe_other1,
432 jai_constants.tax_type_boe_other2,
436 jai_constants.tax_type_boe_surcharge_duty,
433 jai_constants.tax_type_boe_other3,
434 jai_constants.tax_type_boe_other4,
435 jai_constants.tax_type_boe_other5,
437 jai_constants.tax_type_boe_add_customs))then
438 ln_flag := 1;
439 else
440 ln_flag := 0;
441 end if;
442 return ln_flag;
443 END chk_boe_tax;
444
445 -- Added by Qiong.liu for bug12621302 on 9-Jun-2011 BEGIN
446 --==========================================================================
447 -- PROCEDURE NAME:
448 --
449 -- cancelled_boe Public
450 --
451 -- DESCRIPTION:
452 --
453 -- This procedure update boe table and set related amount to 0 when boe be cancelled
454 -- Should be called by JAINBOEI.fmb,JAI_AP_IA_TRIGGER_PKG
455 --
456 -- PARAMETERS:
457 -- In: pn_boe_id Identifier BOE
458
459 -- OUt:
460 --
461 --
462 -- DESIGN REFERENCES:
463 --
464 --
465 -- CHANGE HISTORY:
466 --
467 -- 09-JUN-2011 Qiong Liu created
468 --==========================================================================
469 PROCEDURE cancelled_boe
470 ( pn_boe_id IN NUMBER
471 ) IS
472 lv_count_header number;
473 lv_count_detail number;
474 lv_count_round number;
475 lv_count_tax number;
476 BEGIN
477 UPDATE JAI_CMN_BOE_HDRS hdr
478 SET hdr.status = 'CANCELLED'
479 ,hdr.BOE_AMOUNT = 0
480 ,hdr.AMOUNT_APPLIED = 0
481 ,hdr.AMOUNT_WRITTEN_OFF = 0
482 ,hdr.DEPB_AMOUNT = 0
483 ,hdr.last_update_date = SYSDATE
484 ,hdr.last_update_login = fnd_global.login_id
485 ,hdr.last_updated_by = fnd_global.user_id
486 WHERE hdr.boe_id = pn_boe_id;
487
488
489 UPDATE JAI_BOE_ROUNDINGS jbr
490 SET jbr.TAX_AMOUNT = 0
491 ,jbr.TOTAL_AMOUNT = 0
492 ,jbr.last_update_date = SYSDATE
493 ,jbr.last_update_login = fnd_global.login_id
494 ,jbr.last_updated_by = fnd_global.user_id
495 WHERE jbr.boe_id = pn_boe_id;
496
497 UPDATE JAI_BOE_DETAILS jbd
498 SET jbd.quantity = 0
499 ,jbd.line_amount = 0
500 ,jbd.tax_amount = 0
501 ,jbd.assessable_value = 0
502 ,jbd.last_update_date = SYSDATE
503 ,jbd.last_update_login = fnd_global.login_id
504 ,jbd.last_updated_by = fnd_global.user_id
505 WHERE jbd.boe_id = pn_boe_id;
506
507 UPDATE JAI_BOE_DETAIL_TAXES jbdt
508 SET jbdt.tax_amount = 0
509 ,jbdt.base_tax_amount = 0
510 ,jbdt.func_tax_amount = 0
511 ,jbdt.applied_amount = 0
512 ,jbdt.assessable_value = 0
513 ,jbdt.last_update_date = SYSDATE
514 ,jbdt.last_update_login = fnd_global.login_id
515 ,jbdt.last_updated_by = fnd_global.user_id
516 WHERE EXISTS
517 (
518 SELECT 'x'
519 FROM JAI_BOE_DETAILS jbd
520 WHERE jbd.boe_detail_id = jbdt.boe_detail_id
521 AND jbd.boe_id = pn_boe_id
522 );
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 fnd_file.put_line(fnd_file.log,
527 'UPDATE TABLE in jai_boe_general_pkg.cancelled error:' ||
528 Sqlcode||Sqlerrm);
529 END cancelled_boe;
530 -- Added by Qiong.liu for bug12621302 on 9-Jun-2011 END
531
532 END jai_boe_general_pkg;