[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: ------
557: lv_pan_no JAI_AP_TDS_VENDOR_HDRS.pan_no%type;
558: lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
559: ln_exchange_rate number;
560: ln_fin_year JAI_AP_TDS_YEARS.fin_year%type;
561: lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
562:
563: ln_no_of_tds_inv_generated number := 0;
564: lb_result boolean;
565: ln_req_id number;
1266: from gl_sets_of_books
1267: where set_of_books_id = cp_set_of_books_id;
1268:
1269: cursor c_get_ja_in_ap_inv_id is
1270: 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
1271: from dual;
1272:
1273: cursor c_ap_payment_schedules_all(p_invoice_id number) is
1274: select payment_priority
1839: END IF;
1840: -- End for bug 5722028.
1841:
1842:
1843: insert into jai_ap_tds_thhold_trxs
1844: (
1845: threshold_trx_id ,
1846: invoice_id ,
1847: invoice_line_number ,
1875: tds_rounding_factor -- Bug 5722028. Added by csahoo
1876: )
1877: values
1878: (
1879: jai_ap_tds_thhold_trxs_s.nextval ,
1880: pn_invoice_id ,
1881: pn_invoice_line_number ,
1882: pn_invoice_distribution_id ,
1883: pn_threshold_grp_id ,
1965:
1966: CURSOR get_thhold_rollbk (cp_threshold_grp_id IN NUMBER )
1967: IS
1968: SELECT SUM(NVL(jattt.taxable_amount,0))
1969: FROM jai_ap_tds_thhold_trxs jattt
1970: WHERE jattt.threshold_grp_id = cp_threshold_grp_id
1971: AND (jattt.tds_event like 'THRESHOLD ROLLBACK%' OR
1972: -- Bug 5722028. Added by csahoo
1973: -- added the following condition
2227: p_start_thhold_trx_id in number
2228: )
2229: is
2230:
2231: cursor c_jai_ap_tds_thhold_trxs
2232: (p_invoice_id number, p_start_thhold_trx_id number, p_vendor_id number, p_vendor_site_id number) is
2233: select invoice_to_tds_authority_id,
2234: invoice_to_vendor_id,
2235: invoice_to_tds_authority_num,
2233: select invoice_to_tds_authority_id,
2234: invoice_to_vendor_id,
2235: invoice_to_tds_authority_num,
2236: invoice_to_vendor_num
2237: from jai_ap_tds_thhold_trxs
2238: where threshold_trx_id >= p_start_thhold_trx_id
2239: and invoice_id = p_invoice_id
2240: and vendor_id = p_vendor_id
2241: and vendor_site_id = p_vendor_site_id;
2307: Fnd_File.put_line(Fnd_File.LOG, 'Before Loop ');
2308:
2309: /* Get all the tds invoices that have been created and call the base API to approve it */
2310: for cur_rec in
2311: c_jai_ap_tds_thhold_trxs(p_invoice_id , p_start_thhold_trx_id , p_vendor_id , p_vendor_site_id)
2312: loop
2313:
2314:
2315: /* Get the status of both the invoices and call approval API, if it is not already approved */
2505:
2506: cursor c_check_inv_to_tds_authority (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
2507: select threshold_trx_id,
2508: invoice_id
2509: from jai_ap_tds_thhold_trxs
2510: where invoice_to_tds_authority_num = p_invoice_num
2511: and tds_authority_vendor_id = p_vendor_id
2512: and tds_authority_vendor_site_id = p_vendor_site_id
2513: and invoice_to_tds_authority_id is null;
2514:
2515:
2516: cursor c_check_inv_to_vendor (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
2517: select threshold_trx_id
2518: from jai_ap_tds_thhold_trxs
2519: where invoice_to_vendor_num = p_invoice_num
2520: and vendor_id = p_vendor_id
2521: and vendor_site_id = p_vendor_site_id
2522: and invoice_to_vendor_id is null;
2520: and vendor_id = p_vendor_id
2521: and vendor_site_id = p_vendor_site_id
2522: and invoice_to_vendor_id is null;
2523:
2524: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%type;
2525: ln_invoice_id ap_invoices_all.invoice_id%type;
2526:
2527:
2528: begin
2533:
2534: if ln_threshold_trx_id is not null then
2535: /* Invoice being created is the invoice to TDS authority */
2536:
2537: update jai_ap_tds_thhold_trxs
2538: set invoice_to_tds_authority_id = p_invoice_id
2539: where threshold_trx_id = ln_threshold_trx_id;
2540:
2541: else
2548:
2549: if ln_threshold_trx_id is not null then
2550:
2551: /* Invoice being created is teh invoice to TDS authority */
2552: update jai_ap_tds_thhold_trxs
2553: set invoice_to_vendor_id = p_invoice_id
2554: where threshold_trx_id = ln_threshold_trx_id;
2555:
2556: end if; /* TDS invoice to vendor */
2955: begin
2956:
2957: fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
2958:
2959: insert into jai_ap_tds_thhold_trxs
2960: (
2961: threshold_trx_id ,
2962: invoice_id ,
2963: tds_event ,
2988: last_update_login
2989: )
2990: values
2991: (
2992: jai_ap_tds_thhold_trxs_s.nextval ,
2993: p_invoice_id ,
2994: p_tds_event ,
2995: p_tax_id ,
2996: p_tax_rate ,
3460: CURSOR c_taxable_amount(c_threshold_grp_id NUMBER,
3461: c_single_threshold_amt NUMBER)
3462: IS
3463: SELECT NVL(SUM(a.taxable_amount),0) taxable_amount
3464: FROM jai_ap_tds_thhold_trxs a
3465: WHERE a.threshold_grp_id = c_threshold_grp_id
3466: AND a.tds_event = 'INVOICE VALIDATE'
3467: AND a.taxable_amount >= c_single_threshold_amt
3468: AND NOT EXISTS (SELECT '1'
3479: CURSOR c_thhold_trxs(p_invoice_distribution_id NUMBER,
3480: p_single_threshold_amt NUMBER)
3481: IS
3482: SELECT 'Y'
3483: FROM jai_ap_tds_thhold_trxs a,
3484: jai_ap_tds_inv_taxes b
3485: WHERE a.invoice_id = b.invoice_id
3486: AND b.invoice_distribution_id = p_invoice_distribution_id
3487: AND a.tds_event = 'INVOICE VALIDATE'
3494: ln_diff_tds_amount NUMBER;
3495: r_threshold_grp c_threshold_grp%ROWTYPE;
3496: v_si_flag VARCHAR2(1);
3497: v_pp_flag VARCHAR2(1);
3498: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
3499: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
3500: lv_tds_cm_num ap_invoices_all.invoice_num%type;
3501: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
3502: ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
3497: v_pp_flag VARCHAR2(1);
3498: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
3499: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
3500: lv_tds_cm_num ap_invoices_all.invoice_num%type;
3501: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
3502: ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
3503: ln_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
3504:
3505: CURSOR get_thhold_transn (cp_threshold_grp_id IN NUMBER )
3504:
3505: CURSOR get_thhold_transn (cp_threshold_grp_id IN NUMBER )
3506: IS
3507: SELECT SUM(NVL(jattt.taxable_amount,0))
3508: FROM jai_ap_tds_thhold_trxs jattt
3509: WHERE jattt.threshold_grp_id = cp_threshold_grp_id
3510: AND ( jattt.tds_event like 'THRESHOLD TRANSITION%' OR
3511: -- Bug 5722028. Added by csahoo
3512: -- added the following condition
3523: PRAGMA AUTONOMOUS_TRANSACTION;
3524:
3525: CURSOR cur_thhold_trxs IS
3526: SELECT 'Y'
3527: FROM jai_ap_tds_thhold_trxs a
3528: WHERE a.tds_event = 'INVOICE VALIDATE'
3529: AND a.taxable_amount >= p_single_threshold_amt
3530: AND a.invoice_id IN (SELECT invoice_id
3531: FROM ap_invoice_distributions_all