AP 发票状态的获取

1.获取invoice_status_lookup_code

 ap_invoices_pkg.get_approval_status(ap.invoice_id,

                                                 ap.invoice_amount,
                                                 ap.payment_status_flag,
                                                 ap.invoice_type_lookup_code) approval_status,
                                                
  2.获取转换的描述                                              
  FUNCTION get_approal_status(p_invoice_id         IN NUMBER,
                              p_status_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
    l_nls_approved             VARCHAR2(240);
    l_nls_needs_reapproval     VARCHAR2(240);
    l_nls_never_approved       VARCHAR2(240);
    l_nls_cancelled            VARCHAR2(240);
    l_nls_available            VARCHAR2(240);
    l_nls_full                 VARCHAR2(240);
    l_nls_unapproved           VARCHAR2(240);
    l_nls_unpaid               VARCHAR2(240);
    l_nls_permanent            VARCHAR2(240);
    l_nls_not_required         VARCHAR2(240);
    l_nls_required             VARCHAR2(240);
    l_nls_initiated            VARCHAR2(240);
    l_nls_wfapproved           VARCHAR2(240);
    l_nls_rejected             VARCHAR2(240);
    l_nls_manually_approved    VARCHAR2(240);
    l_nls_selected_for_payment VARCHAR2(240);
    l_inv_sel_count            NUMBER;
    l_status_display           VARCHAR2(240);
  BEGIN
    SELECT l1.displayed_field,
           l2.displayed_field,
           l3.displayed_field,
           l4.displayed_field,
           l5.displayed_field,
           l6.displayed_field,
           l7.displayed_field,
           l8.displayed_field,
           l9.displayed_field,
           l10.displayed_field,
           l11.displayed_field,
           l12.displayed_field,
           l13.displayed_field,
           l14.displayed_field,
           l15.displayed_field,
           l16.displayed_field
      INTO l_nls_approved,
           l_nls_needs_reapproval,
           l_nls_never_approved,
           l_nls_cancelled,
           l_nls_available,
           l_nls_full,
           l_nls_unapproved,
           l_nls_unpaid,
           l_nls_permanent,
           l_nls_not_required,
           l_nls_required,
           l_nls_initiated,
           l_nls_wfapproved,
           l_nls_rejected,
           l_nls_manually_approved,
           l_nls_selected_for_payment
      FROM ap_lookup_codes l1,
           ap_lookup_codes l2,
           ap_lookup_codes l3,
           ap_lookup_codes l4,
           ap_lookup_codes l5,
           ap_lookup_codes l6,
           ap_lookup_codes l7,
           ap_lookup_codes l8,
           ap_lookup_codes l9,
           ap_lookup_codes l10,
           ap_lookup_codes l11,
           ap_lookup_codes l12,
           ap_lookup_codes l13,
           ap_lookup_codes l14,
           ap_lookup_codes l15,
           ap_lookup_codes l16
     WHERE 1 = 1
       AND l1.lookup_type = 'NLS TRANSLATION'
       AND l1.lookup_code = 'APPROVED'
       AND l2.lookup_type = 'NLS TRANSLATION'
       AND l2.lookup_code = 'NEEDS REAPPROVAL'
       AND l3.lookup_type = 'NLS TRANSLATION'
       AND l3.lookup_code = 'NEVER APPROVED'
       AND l4.lookup_type = 'NLS TRANSLATION'
       AND l4.lookup_code = 'CANCELLED'
       AND l5.lookup_type = 'PREPAY STATUS'
       AND l5.lookup_code = 'AVAILABLE'
       AND l6.lookup_type = 'PREPAY STATUS'
       AND l6.lookup_code = 'FULL'
       AND l7.lookup_type = 'PREPAY STATUS'
       AND l7.lookup_code = 'UNAPPROVED'
       AND l8.lookup_type = 'PREPAY STATUS'
       AND l8.lookup_code = 'UNPAID'
       AND l9.lookup_type = 'PREPAY STATUS'
       AND l9.lookup_code = 'PERMANENT'
       AND l10.lookup_type = 'AP_WFAPPROVAL_STATUS'
       AND l10.lookup_code = 'NOT REQUIRED'
       AND l11.lookup_type = 'AP_WFAPPROVAL_STATUS'
       AND l11.lookup_code = 'REQUIRED'
       AND l12.lookup_type = 'AP_WFAPPROVAL_STATUS'
       AND l12.lookup_code = 'INITIATED'
       AND l13.lookup_type = 'AP_WFAPPROVAL_STATUS'
       AND l13.lookup_code = 'WFAPPROVED'
       AND l14.lookup_type = 'AP_WFAPPROVAL_STATUS'
       AND l14.lookup_code = 'REJECTED'
       AND l15.lookup_type = 'AP_WFAPPROVAL_STATUS'
       AND l15.lookup_code = 'MANUALLY APPROVED'
       AND l16.lookup_type = 'NLS TRANSLATION'
       AND l16.lookup_code = 'SELECTED FOR PAYMENT';
 
    IF (p_status_lookup_code = 'NEVER APPROVED') THEN
      l_status_display := l_nls_never_approved;
    ELSIF (p_status_lookup_code = 'APPROVED') THEN
      l_status_display := l_nls_approved;
    ELSIF (p_status_lookup_code = 'CANCELLED') THEN
      l_status_display := l_nls_cancelled;
    ELSIF (p_status_lookup_code = 'NEEDS REAPPROVAL') THEN
      l_status_display := l_nls_needs_reapproval;
    ELSIF (p_status_lookup_code = 'AVAILABLE') THEN
      l_status_display := l_nls_available;
    ELSIF (p_status_lookup_code = 'FULL') THEN
      l_status_display := l_nls_full;
    ELSIF (p_status_lookup_code = 'UNAPPROVED') THEN
      l_status_display := l_nls_unapproved;
    ELSIF (p_status_lookup_code = 'UNPAID') THEN
      l_status_display := l_nls_unpaid;
    ELSIF (p_status_lookup_code = 'PERMANENT') THEN
      l_status_display := l_nls_permanent;
    ELSIF (p_status_lookup_code = 'NOT REQUIRED') THEN
      l_status_display := l_nls_not_required;
    ELSIF (p_status_lookup_code = 'REQUIRED') THEN
      l_status_display := l_nls_required;
    ELSIF (p_status_lookup_code = 'INITIATED') THEN
      l_status_display := l_nls_initiated;
    ELSIF (p_status_lookup_code = 'WFAPPROVED') THEN
      l_status_display := l_nls_wfapproved;
    ELSIF (p_status_lookup_code = 'REJECTED') THEN
      l_status_display := l_nls_rejected;
    ELSIF (p_status_lookup_code = 'MANUALLY APPROVED') THEN
      l_status_display := l_nls_manually_approved;
    END IF;
 
    -- Added for Payment Process Enhancements.
    IF (p_status_lookup_code IN ('APPROVED',
                                 'UNPAID')) THEN
      SELECT COUNT(*)
        INTO l_inv_sel_count
        FROM ap_payment_schedules_all
       WHERE invoice_id = p_invoice_id
         AND checkrun_id IS NOT NULL
         AND rownum = 1; --bug5739273
    
      IF (l_inv_sel_count > 0) THEN
        l_status_display := l_nls_selected_for_payment;
      END IF;
    END IF;
    RETURN l_status_display;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_approal_status;