EBS AP模块，发票的Total和subtotal获取逻辑

 BEGIN
        SELECT SUM(decode(ail.line_type_lookup_code,
                          'ITEM',
                          (ail.amount - nvl(ail.included_tax_amount, 0)),
                          'RETAINAGE RELEASE',
                          (ail.amount - nvl(ail.included_tax_amount, 0)),
                          'RETROITEM',
                          (ail.amount - nvl(ail.included_tax_amount, 0)),
                          NULL)) item_total,
               SUM(decode(ail.line_type_lookup_code,
                          'FREIGHT',
                          (ail.amount - nvl(ail.included_tax_amount, 0)),
                          NULL)) freight_total,
               SUM(decode(ail.line_type_lookup_code,
                          'MISCELLANEOUS',
                          (ail.amount - nvl(ail.included_tax_amount, 0)),
                          NULL)) misc_total
          INTO l_item_total, l_freight_total, l_misc_total
          FROM ap_invoice_lines_all ail
         WHERE ail.invoice_id = cur_datas.invoice_id;
      EXCEPTION
        WHEN OTHERS THEN
          l_item_total    := NULL;
          l_freight_total := NULL;
          l_misc_total    := NULL;
      END;
    
      BEGIN
        SELECT (0 - SUM(decode(aid.line_type_lookup_code, 'PREPAY', aid.amount, NULL))) prepay_app_total
          INTO l_prepaid_amount
          FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
         WHERE ail.invoice_id = cur_datas.invoice_id
           AND ail.invoice_id = aid.invoice_id
           AND ail.line_number = aid.invoice_line_number
           AND aid.line_type_lookup_code = 'PREPAY'
           AND aid.prepay_distribution_id IS NOT NULL
           AND nvl(ail.invoice_includes_prepay_flag, 'N') = 'N';
      EXCEPTION
        WHEN OTHERS THEN
          l_prepaid_amount := NULL;
      END;
    
      BEGIN
        SELECT SUM(nvl(amount, 0))
          INTO l_retained_total
          FROM ap_invoice_distributions_all aid
         WHERE aid.invoice_id = cur_datas.invoice_id
           AND aid.line_type_lookup_code = 'RETAINAGE'
           AND EXISTS
         (SELECT 'X'
                  FROM ap_invoice_lines_all ail
                 WHERE ail.invoice_id = cur_datas.invoice_id
                   AND ail.line_number = aid.invoice_line_number
                   AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE');
      EXCEPTION
        WHEN OTHERS THEN
          l_retained_total := NULL;
      END;
    
      SELECT nvl(SUM(decode(nvl(zls.self_assessed_flag, 'N'),
                            'N',
                            CASE
                              WHEN EXISTS
                               (SELECT 'Prepay App Exists'
                                      FROM ap_invoice_lines prepay
                                     WHERE prepay.invoice_id = zls.trx_id
                                       AND prepay.line_type_lookup_code = 'PREPAY'
                                       AND prepay.prepay_invoice_id = zls.applied_from_trx_id
                                       AND prepay.prepay_line_number = zls.applied_from_line_id
                                       AND prepay.invoice_includes_prepay_flag = 'Y'
                                       AND (prepay.discarded_flag IS NULL OR prepay.discarded_flag = 'N')) THEN
                               0
                              ELSE
                               nvl(zls.tax_amt, 0)
                            END,
                            0)),
                 0)
        INTO l_total_tax_amount
        FROM zx_lines_summary zls
       WHERE zls.application_id = 200
         AND zls.entity_code = 'AP_INVOICES'
         AND zls.event_class_code IN
             ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
         AND zls.trx_id = cur_datas.invoice_id
         AND nvl(zls.reporting_only_flag, 'N') = 'N';
    
      SELECT nvl(SUM(nvl(ail.amount, 0)), 0)
        INTO l_ret_tax_amt
        FROM ap_invoice_lines_all ail
       WHERE ail.invoice_id = cur_datas.invoice_id
         AND line_type_lookup_code = 'RETROTAX';
    
      l_total_tax_amount := l_total_tax_amount + l_ret_tax_amt;
    
     l_subtotal    := nvl(l_item_total, 0) + nvl(l_retained_total, 0) -
                               nvl(abs(l_prepaid_amount), 0) -
                               nvl(ap_invoices_pkg.get_amount_withheld(cur_datas.invoice_id),
                                   0);
     l_total := nvl(l_subtotal, 0) + nvl(l_total_tax_amount, 0) +
                               nvl(l_freight_total, 0) + nvl(l_misc_total, 0);