[Home] [Help]
457: PRAGMA AUTONOMOUS_TRANSACTION;
458: cursor c_get_dist_prepay(p_invoice_id number) is
459: --select prepay_distribution_id, amount --Comments by Jia for FP Bug#7431371
460: select prepay_distribution_id, sum(amount) amount --Modified by Jia for FP Bug#7431371
461: from ap_invoice_distributions_all
462: where invoice_id = p_invoice_id
463: --Added the following clause to select the prepayment amounts with the same section type as the distribution - Bug 11070443
464: and
465: ((pn_section_type = 'TDS_SECTION' and global_attribute1 is not null)
469: group by prepay_distribution_id; --Addec by Jia for FP Bug#7431371
470:
471: cursor c_get_prepay_inv(p_prepay_dist_id number) is
472: select invoice_id
473: from ap_invoice_distributions_all
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
548: PRAGMA AUTONOMOUS_TRANSACTION;
549: cursor c_jai_apply_amount(cp_invoice_id number) is
550: select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
551: from jai_ap_tds_prepayments a,
552: ap_invoice_distributions_all b,
553: ap_invoice_distributions_all c,
554: jai_ap_tds_inv_taxes d
555: where a.invoice_distribution_id_prepay = b.invoice_distribution_id
556: and b.prepay_distribution_id = c.invoice_distribution_id
549: cursor c_jai_apply_amount(cp_invoice_id number) is
550: select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
551: from jai_ap_tds_prepayments a,
552: ap_invoice_distributions_all b,
553: ap_invoice_distributions_all c,
554: jai_ap_tds_inv_taxes d
555: where a.invoice_distribution_id_prepay = b.invoice_distribution_id
556: and b.prepay_distribution_id = c.invoice_distribution_id
557: and nvl(a.unapply_flag, 'N') <> 'Y'
566: applied and subtract the same before calculating Effective amount for Threshold Transition*/
567: cursor c_pp_single_apply_amount(cp_invoice_id number) is
568: select sum(application_amount) amount
569: from jai_ap_tds_prepayments a,
570: ap_invoice_distributions_all b
571: where a.invoice_id = cp_invoice_id
572: and a.invoice_id = b.invoice_id
573: and a.invoice_distribution_id_prepay = b.invoice_distribution_id
574: and prepay_distribution_id is not null
573: and a.invoice_distribution_id_prepay = b.invoice_distribution_id
574: and prepay_distribution_id is not null
575: and nvl(a.unapply_flag, 'N') <> 'Y'
576: and exists (select 1
577: from jai_ap_tds_inv_taxes jatit, ap_invoice_distributions_all aida
578: where aida.invoice_distribution_id = b.prepay_distribution_id
579: and aida.invoice_id = jatit.invoice_id
580: and aida.invoice_distribution_id = jatit.invoice_distribution_id
581: and jatit.threshold_slab_id_single is not null
584: /*Bug 12671504 - End*/
585: --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276, begin
586: cursor c_prepay_apply_amt(cp_invoice_id number) is
587: select abs(amount) amount, invoice_distribution_id, prepay_distribution_id
588: from ap_invoice_distributions_all
589: where invoice_id = cp_invoice_id
590: and line_type_lookup_code = 'PREPAY';
591:
592: cursor c_get_thhold_grp(cp_invoice_id number, cp_invoice_dist_id number) is
2029:
2030: ln_invoice_to_tds_line_id ap_invoice_lines_interface.invoice_line_id%type;
2031: ln_invoice_to_vendor_line_id ap_invoice_lines_interface.invoice_line_id%type;
2032:
2033: lv_invoice_to_tds_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
2034: lv_invoice_to_vendor_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
2035:
2036: ln_invoice_to_tds_amount number;
2037: ln_invoice_to_vendor_amount number;
2030: ln_invoice_to_tds_line_id ap_invoice_lines_interface.invoice_line_id%type;
2031: ln_invoice_to_vendor_line_id ap_invoice_lines_interface.invoice_line_id%type;
2032:
2033: lv_invoice_to_tds_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
2034: lv_invoice_to_vendor_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
2035:
2036: ln_invoice_to_tds_amount number;
2037: ln_invoice_to_vendor_amount number;
2038:
2044: lv_pay_group_lookup_code po_vendors.pay_group_lookup_code%type;
2045:
2046: lv_ja_in_ap_inv_id varchar2(15);
2047: ld_accounting_date date;
2048: lv_open_period ap_invoice_distributions_all.period_name%type;
2049: ln_tax_amount number;
2050:
2051: lv_invoice_num ap_invoices_all.invoice_num%type;
2052: lv_source_attribute jai_ap_tds_invoices.source_attribute%TYPE ; --rchandan for bug#4428980
4428: b.vendor_site_id,
4429: b.invoice_currency_code,
4430: b.exchange_rate,
4431: b.set_of_books_id
4432: FROM ap_invoice_distributions_all a,
4433: ap_invoices_all b
4434: WHERE a.invoice_id = b.invoice_id
4435: AND b.invoice_id = p_invoice_id
4436: AND a.line_type_lookup_code = 'PREPAY'
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
4821: WHERE invoice_distribution_id = (SELECT prepay_distribution_id
4822: FROM ap_invoice_distributions_all
4823: WHERE invoice_distribution_id = p_invoice_distribution_id));
4824: lv_pp_flag VARCHAR2(1);
4818: AND a.taxable_amount >= p_single_threshold_amt
4819: AND a.invoice_id IN (SELECT invoice_id
4820: FROM ap_invoice_distributions_all
4821: WHERE invoice_distribution_id = (SELECT prepay_distribution_id
4822: FROM ap_invoice_distributions_all
4823: WHERE invoice_distribution_id = p_invoice_distribution_id));
4824: lv_pp_flag VARCHAR2(1);
4825:
4826: BEGIN