[Home] [Help]
32: d) Do not add invoice amount to cumulative amount in group information.
33:
34: 3) Update cursor c_jai_ap_tds_thhold_slabs_new to get slab for multi rate
35: a. Added cp_legal_entity_id parameter
36: b. Added alias for table jai_ap_tds_thhold_types. Added table alias prefix to
37: threshold_hdr_id,threshold_type_id.
38: 4) When invoice is prepayment, do not get slab throuth nature accounting.
39: ---------------------------------------------------------------------------- */
40: G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_DTC_GENERATION_PKG';
1601:
1602: cursor c_valid_accounting_date (cp_thr_hdr_id number, cp_accounting_date date)
1603: is
1604: SELECT 'Y'
1605: FROM jai_ap_tds_thhold_types
1606: WHERE threshold_hdr_id = cp_thr_hdr_id
1607: AND cp_accounting_date between nvl(from_date, cp_accounting_date) and nvl(to_date, cp_accounting_date);
1608:
1609: cursor c_po_vendors(p_vendor_id number) is
2514: (SELECT FIRST_VALUE(threshold_type_id)
2515: OVER (ORDER BY NVL(DEFAULT_TYPE,'N') DESC ,threshold_type_id ASC)
2516: ------------------------------------------------
2517: --Added by Chong for bug#15939571 20130319 End
2518: from jai_ap_tds_thhold_types
2519: where threshold_hdr_id = p_threshold_hdr_id
2520: and threshold_type = p_threshold_type
2521: /* Bug 4522540. Added by Lakshmi Gopalsami
2522: Added the following date condition */
2529:
2530: /*following cursor added for FP bug 6345725 - need to check if there are any active slab(s) defined */
2531: cursor c_check_slabs_end_dated (p_threshold_hdr_id number) is
2532: select 1
2533: from jai_ap_tds_thhold_types
2534: where threshold_hdr_id = p_threshold_hdr_id
2535: and trunc(p_accounting_Date) between from_date and nvl(to_date, p_accounting_date + 1);
2536:
2537: ln_check_slab_exists NUMBER;
2922: from jai_ap_tds_thhold_slabs
2923: where threshold_hdr_id = p_threshold_hdr_id
2924: and threshold_type_id in
2925: ( select jattt.threshold_type_id
2926: from jai_ap_tds_thhold_types jattt
2927: where jattt.threshold_hdr_id = p_threshold_hdr_id
2928: and jattt.threshold_type = p_threshold_type
2929: and trunc(p_accounting_Date) between jattt.from_date and nvl(jattt.to_date, p_accounting_date + 1)
2930: and exists (select '1'
2960: -- cursor to validate accounting date for every threshold type
2961: cursor c_valid_accounting_date (cp_thr_hdr_id number, cp_accounting_date date)
2962: is
2963: SELECT 'Y'
2964: FROM jai_ap_tds_thhold_types
2965: WHERE threshold_hdr_id = cp_thr_hdr_id
2966: AND cp_accounting_date between nvl(from_date, cp_accounting_date) and nvl(to_date, cp_accounting_date);
2967: -------------------------------------------------------------
2968: --added by Chong for bug#16580553 20130402 End
5146: JATTTA.TAX_CATEGORY_ID
5147: --, JATTTA.OPERATING_UNIT_ID
5148: ,jatth.threshold_hdr_id --Added by Chong for bug#16414088 20130313
5149: FROM JAI_AP_TDS_THHOLD_HDRS JATTH,
5150: JAI_AP_TDS_THHOLD_TYPES JATTTY,
5151: JAI_AP_TDS_THHOLD_SLABS JATTS,
5152: JAI_AP_TDS_THHOLD_TAXES JATTTA
5153: WHERE JATTS.THRESHOLD_SLAB_ID = p_threshold_slab_id
5154: AND JATTH.THRESHOLD_HDR_ID = JATTTY.THRESHOLD_HDR_ID
9083: jatts.to_amount,
9084: jatts.tax_rate
9085: FROM jai_ap_tds_thhold_slabs jatts
9086: ,jai_ap_tds_thhold_account jatta
9087: ,jai_ap_tds_thhold_types jattt
9088: WHERE jatts.threshold_hdr_id = jatta.threshold_hdr_id
9089: AND jatts.threshold_type_id = jatta.threshold_type_id
9090: AND jattt.threshold_type_id = jatts.threshold_type_id
9091: AND jattt.threshold_hdr_id = jatts.threshold_hdr_id
9115: (SELECT FIRST_VALUE(threshold_type_id)
9116: OVER (ORDER BY NVL(DEFAULT_TYPE,'N') DESC ,threshold_type_id ASC)
9117: ------------------------------------------------
9118: --Added by Chong for bug#15939571 20130313 End
9119: FROM jai_ap_tds_thhold_types
9120: WHERE threshold_hdr_id = p_threshold_hdr_id
9121: AND threshold_type = p_threshold_type
9122: AND trunc(sysdate) between from_date and
9123: nvl(to_date, sysdate + 1))
9128: r_get_threshold_grp_dtl c_get_threshold_grp_dtl%ROWTYPE;
9129: ln_effective_invoice_amt NUMBER;
9130: r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%ROWTYPE;
9131: r_get_tds_thhold_slabs_byNt c_get_tds_thhold_slabs_byNt%ROWTYPE;
9132: lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
9133: lv_multiple_rate_setup jai_ap_tds_thhold_hdrs.multiple_rate_setup%TYPE;
9134: lv_invoice_distribution_id jai_ap_tds_inv_taxes.invoice_distribution_id%TYPE;
9135: lv_set_of_book_id NUMBER;
9136: lv_legal_entity_id NUMBER;