[Home] [Help]
23: Created this package for implementing the tax defaultation
24: functionality onto AP invoice.
25:
26: 2. 11/05/2005 rchandan for bug#4333449. Version 116.1
27: A new procedure to insert into jai_ap_tds_thhold_trxs table is added.
28:
29: India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
30: is not populated whenever an invoice is generated. Instead the Invoice details are
31: populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
27: A new procedure to insert into jai_ap_tds_thhold_trxs table is added.
28:
29: India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
30: is not populated whenever an invoice is generated. Instead the Invoice details are
31: populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
32: tables the jai_ap_tds_thhold_trxs table is also populated.
33:
34: 3. 11/05/2005 rchandan for bug#4323338. Version 116.2
35: India Org Info DFF is eliminated as a part of JA migration. A table by name JAI_AP_TDS_ORG_TANS is dropped
28:
29: India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
30: is not populated whenever an invoice is generated. Instead the Invoice details are
31: populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
32: tables the jai_ap_tds_thhold_trxs table is also populated.
33:
34: 3. 11/05/2005 rchandan for bug#4323338. Version 116.2
35: India Org Info DFF is eliminated as a part of JA migration. A table by name JAI_AP_TDS_ORG_TANS is dropped
36: and a view jai_ap_tds_org_tan_v is created to retrieve the PAN NO.
59:
60: 9. 29-Jun-2005 ssumaith - bug#4448789 - removal of hr_operating_units.legal_entity_id from this trigger.
61:
62: 10. 14-Jul-2005 rchandan for bug#4487676.File version 117.2
63: Sequnece jai_ap_tds_invoice_num_s is renamed to JAI_AP_TDS_THHOLD_TRXS_S1
64:
65: 11. 25-Jul-2005 Bug4513458. added by Lakshmi Gopalsami version 120.2
66: Issue:
67: ------
343: the distributions which have either a default_section_code or actual_section_code defined.
344:
345: 47. 14-Jan-2010 Jia For FP Bug#8278439
346: Issue: This is a forward port bug for the Bug#8269891.
347: The Threshold_hdr_id value in jai_ap_tds_thhold_trxs was not being populated, which was leading to
348: the record missing from the 'India - TDS PAyment Review' report, because the main query of this report
349: is dependent on the value of threshold_hdr_id column of jai_ap_tds_thhold_trxs.
350:
351: Fixed: In cases where no default_section_code was specified in the vendor additional information,
345: 47. 14-Jan-2010 Jia For FP Bug#8278439
346: Issue: This is a forward port bug for the Bug#8269891.
347: The Threshold_hdr_id value in jai_ap_tds_thhold_trxs was not being populated, which was leading to
348: the record missing from the 'India - TDS PAyment Review' report, because the main query of this report
349: is dependent on the value of threshold_hdr_id column of jai_ap_tds_thhold_trxs.
350:
351: Fixed: In cases where no default_section_code was specified in the vendor additional information,
352: the value for default_section_code in jai_ap_tds_inv_taxes was populated as null.
353: Hence during the execution of the cursor c_check_valid_tax it would fetch no records as the
474: where invoice_distribution_id = p_prepay_dist_id;
475:
476: cursor c_prepay_tds_cal(p_invoice_id number) is --Xiao for Bug#6767347
477: select 1 from
478: jai_ap_tds_thhold_trxs where
479: invoice_id = p_invoice_id;
480:
481: --Addec by Jia for FP Bug#7431371, Begin
482: -------------------------------------------------------------------------------
1125: lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
1126: lv_confirm_pan_flag JAI_AP_TDS_VENDOR_HDRS.confirm_pan_flag%type; /*Bug 11896260*/
1127: ln_exchange_rate number;
1128: ln_fin_year JAI_AP_TDS_YEARS.fin_year%type;
1129: lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
1130:
1131: ln_no_of_tds_inv_generated number := 0;
1132: lb_result boolean;
1133: ln_req_id number;
1999: from gl_sets_of_books
2000: where set_of_books_id = cp_set_of_books_id;
2001:
2002: cursor c_get_ja_in_ap_inv_id is
2003: select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
2004: from dual;
2005:
2006: cursor c_ap_payment_schedules_all(p_invoice_id number) is
2007: select payment_priority
2537: close c_ap_payment_schedules_all;
2538:
2539: /* Bug 5751783
2540: * Moved the assignment of ln_invoice_amount outside IF as this is used
2541: * in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
2542: * of the tds event.
2543: */
2544:
2545: Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
2667: END IF;
2668: -- End for bug 5722028.
2669:
2670:
2671: insert into jai_ap_tds_thhold_trxs
2672: (
2673: threshold_trx_id ,
2674: invoice_id ,
2675: invoice_line_number ,
2704: tds_rounding_factor -- Bug 5722028. Added by csahoo
2705: )
2706: values
2707: (
2708: jai_ap_tds_thhold_trxs_s.nextval ,
2709: pn_invoice_id ,
2710: pn_invoice_line_number ,
2711: pn_invoice_distribution_id ,
2712: pn_threshold_grp_id ,
2795: WHERE aia.invoice_id = jatit.invoice_id
2796: AND aia.cancelled_date IS NOT NULL)
2797: )
2798: minus
2799: /* select invoice_id from jai_ap_tds_thhold_trxs
2800: where threshold_grp_id = cp_threshold_grp_id; */ /* Commented and added below select for bug 14709400 */
2801: select invoice_id from jai_ap_tds_inv_taxes where invoice_id=p_invoice_id
2802: or (threshold_grp_id = cp_threshold_grp_id and nvl(threshold_slab_id_single,-99)<>-99 and threshold_trx_id is not null);
2803:
2803:
2804: /*Bug 13561970 - Modified cursor to get the number of records with THRESHOLD%. This would fetch both THRESHOLD TRANSITION and THRESHOLD ROLLBACK. Earlir it was THRESHOLD TRANSITION%*/
2805:
2806: cursor c_threshold_passed(cp_threshold_grp_id number) is
2807: select count(*) from jai_ap_tds_thhold_trxs /*Bug 9562876*/
2808: where threshold_grp_id = cp_threshold_grp_id
2809: and tds_event like 'THRESHOLD%';
2810:
2811: cursor c_jai_no_tds_trx_amt(cp_invoice_id number) is
2817: group by invoice_id;
2818:
2819: cursor c_thhold_grps_inv(cp_threshold_grp_id number) is
2820: select invoice_id, tds_event, tax_id, tax_rate, taxable_amount, tax_amount
2821: from jai_ap_tds_thhold_trxs
2822: where threshold_grp_id = cp_threshold_grp_id;
2823:
2824: cursor c_jai_cancelled_amount(cp_invoice_id number) is
2825: select nvl(cancelled_amount,0)
2835: and section_code = p_tds_section_code;
2836:
2837: cursor c_sur_already_calc(cp_threshold_grp_id number) is
2838: select 1
2839: from jai_ap_tds_thhold_trxs
2840: where threshold_grp_id = cp_threshold_grp_id
2841: and tds_event like 'SURCHARGE_CALCULATE';
2842: /* END, Bgowrava for Bug#8254510*/
2843:
2888:
2889: CURSOR get_thhold_rollbk (cp_threshold_grp_id IN NUMBER )
2890: IS
2891: SELECT SUM(NVL(jattt.taxable_amount,0))
2892: FROM jai_ap_tds_thhold_trxs jattt
2893: WHERE jattt.threshold_grp_id = cp_threshold_grp_id
2894: AND (jattt.tds_event like 'THRESHOLD ROLLBACK%');
2895: -- Bug 5722028. Added by csahoo
2896: -- added the following condition
2929:
2930: CURSOR c_get_rollback_amt(cp_threshold_grp_id NUMBER, cp_new_thhold_slab NUMBER)
2931: IS
2932: select ((tax_amount/tax_rate)*100) taxable_amount, tax_amount
2933: from jai_Ap_tds_thhold_trxs
2934: where invoice_id = (select invoice_id
2935: from jai_ap_tds_thgrp_audits
2936: where threshold_grp_audit_id = (select max(threshold_grp_audit_id)
2937: from jai_Ap_tds_thgrp_Audits
2974: /* START, by Bgowrava for Bug#8459564 */
2975: ln_threshold_slab_id_before jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
2976: ln_threshold_slab_id_temp VARCHAR2 (1000);
2977: ln_threshold_slab_id_after jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
2978: ln_taxable_amount jai_ap_tds_thhold_trxs.taxable_amount%TYPE;
2979: ln_pp_tds_not_deducted NUMBER ;
2980: ln_thhold_trxn_roll NUMBER ;
2981: /* END, by Bgowrava for Bug#8459564 */
2982: ln_thhold_total_inv_amt number := 0; --Added by Xiao For Bug#8485691, related 11i bug#8439217
3428: p_start_thhold_trx_id in number
3429: )
3430: is
3431:
3432: cursor c_jai_ap_tds_thhold_trxs
3433: (p_invoice_id number, p_start_thhold_trx_id number, p_vendor_id number, p_vendor_site_id number) is
3434: select invoice_to_tds_authority_id,
3435: invoice_to_vendor_id,
3436: invoice_to_tds_authority_num,
3434: select invoice_to_tds_authority_id,
3435: invoice_to_vendor_id,
3436: invoice_to_tds_authority_num,
3437: invoice_to_vendor_num
3438: from jai_ap_tds_thhold_trxs
3439: where threshold_trx_id >= p_start_thhold_trx_id
3440: and invoice_id = p_invoice_id
3441: and vendor_id = p_vendor_id
3442: and vendor_site_id = p_vendor_site_id;
3508: Fnd_File.put_line(Fnd_File.LOG, 'Before Loop ');
3509:
3510: /* Get all the tds invoices that have been created and call the base API to approve it */
3511: for cur_rec in
3512: c_jai_ap_tds_thhold_trxs(p_invoice_id , p_start_thhold_trx_id , p_vendor_id , p_vendor_site_id)
3513: loop
3514:
3515:
3516: /* Get the status of both the invoices and call approval API, if it is not already approved */
3706:
3707: cursor c_check_inv_to_tds_authority (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
3708: select threshold_trx_id,
3709: invoice_id
3710: from jai_ap_tds_thhold_trxs
3711: where invoice_to_tds_authority_num = p_invoice_num
3712: and tds_authority_vendor_id = p_vendor_id
3713: and tds_authority_vendor_site_id = p_vendor_site_id
3714: and invoice_to_tds_authority_id is null;
3715:
3716:
3717: cursor c_check_inv_to_vendor (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
3718: select threshold_trx_id
3719: from jai_ap_tds_thhold_trxs
3720: where invoice_to_vendor_num = p_invoice_num
3721: and vendor_id = p_vendor_id
3722: and vendor_site_id = p_vendor_site_id
3723: and invoice_to_vendor_id is null;
3721: and vendor_id = p_vendor_id
3722: and vendor_site_id = p_vendor_site_id
3723: and invoice_to_vendor_id is null;
3724:
3725: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%type;
3726: ln_invoice_id ap_invoices_all.invoice_id%type;
3727:
3728:
3729: begin
3734:
3735: if ln_threshold_trx_id is not null then
3736: /* Invoice being created is the invoice to TDS authority */
3737:
3738: update jai_ap_tds_thhold_trxs
3739: set invoice_to_tds_authority_id = p_invoice_id
3740: where threshold_trx_id = ln_threshold_trx_id;
3741:
3742: else
3749:
3750: if ln_threshold_trx_id is not null then
3751:
3752: /* Invoice being created is teh invoice to TDS authority */
3753: update jai_ap_tds_thhold_trxs
3754: set invoice_to_vendor_id = p_invoice_id
3755: where threshold_trx_id = ln_threshold_trx_id;
3756:
3757: end if; /* TDS invoice to vendor */
4179: begin
4180:
4181: fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
4182:
4183: insert into jai_ap_tds_thhold_trxs
4184: (
4185: threshold_trx_id ,
4186: invoice_id ,
4187: tds_event ,
4212: last_update_login
4213: )
4214: values
4215: (
4216: jai_ap_tds_thhold_trxs_s.nextval ,
4217: p_invoice_id ,
4218: p_tds_event ,
4219: p_tax_id ,
4220: p_tax_rate ,
4705: CURSOR c_taxable_amount(c_threshold_grp_id NUMBER,
4706: c_single_threshold_amt NUMBER)
4707: IS
4708: SELECT NVL(SUM(a.taxable_amount),0) taxable_amount
4709: FROM jai_ap_tds_thhold_trxs a
4710: WHERE a.threshold_grp_id = c_threshold_grp_id
4711: AND a.tds_event = 'INVOICE VALIDATE'
4712: AND a.taxable_amount >= c_single_threshold_amt
4713: AND NOT EXISTS (SELECT '1'
4724: CURSOR c_thhold_trxs(p_invoice_distribution_id NUMBER,
4725: p_single_threshold_amt NUMBER)
4726: IS
4727: SELECT 'Y'
4728: FROM jai_ap_tds_thhold_trxs a,
4729: jai_ap_tds_inv_taxes b
4730: WHERE a.invoice_id = b.invoice_id
4731: AND b.invoice_distribution_id = p_invoice_distribution_id
4732: AND a.tds_event = 'INVOICE VALIDATE'
4739: ln_diff_tds_amount NUMBER;
4740: r_threshold_grp c_threshold_grp%ROWTYPE;
4741: v_si_flag VARCHAR2(1);
4742: v_pp_flag VARCHAR2(1);
4743: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
4744: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
4745: lv_tds_cm_num ap_invoices_all.invoice_num%type;
4746: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
4747: ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
4742: v_pp_flag VARCHAR2(1);
4743: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
4744: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
4745: lv_tds_cm_num ap_invoices_all.invoice_num%type;
4746: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
4747: ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
4748: ln_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
4749:
4750: /* Bug 5751783.
4793: /*Bug 5751783 - End*/
4794: CURSOR get_thhold_transn (cp_threshold_grp_id IN NUMBER )
4795: IS
4796: SELECT SUM(NVL(jattt.taxable_amount,0))
4797: FROM jai_ap_tds_thhold_trxs jattt
4798: WHERE jattt.threshold_grp_id = cp_threshold_grp_id
4799: AND ( jattt.tds_event like 'THRESHOLD TRANSITION%' OR
4800: -- Bug 5722028. Added by csahoo
4801: -- added the following condition
4812: PRAGMA AUTONOMOUS_TRANSACTION;
4813:
4814: CURSOR cur_thhold_trxs IS
4815: SELECT 'Y'
4816: FROM jai_ap_tds_thhold_trxs a
4817: WHERE a.tds_event = 'INVOICE VALIDATE'
4818: AND a.taxable_amount >= p_single_threshold_amt
4819: AND a.invoice_id IN (SELECT invoice_id
4820: FROM ap_invoice_distributions_all