Oracle Apps Knowledge Sharing

 ----------------------------------------------------------------------
  -- PROCEDURE validate_imported_invoices 
  -- This procedure is to validate imported invoices in AP
  ---------------------------------------------------------------------- 
  PROCEDURE validate_imported_invoices(pin_group_id    IN  NUMBER,
                                       pov_err_message OUT VARCHAR2
                                      )  
  IS
    CURSOR cur_imp_invoices IS
    SELECT ai.invoice_id ap_invoice_id,
           ai.approval_iteration
      FROM ap_invoices_all ai
     WHERE ap_invoices_pkg.get_approval_status(ai.invoice_id,
                                               ai.invoice_amount,
                                               ai.payment_status_flag,
                                               ai.invoice_type_lookup_code
                                              ) NOT IN ( 'APPROVED','UNPAID');  
  -- This condition is pickup invoices eligible for validation only
 
    ln_processed_cnt      NUMBER DEFAULT 0;
    ln_failed_cnt         NUMBER DEFAULT 0;
    ln_holds_cnt          NUMBER;
    lv_approval_status    VARCHAR2(100);
    lv_funds_return_code  VARCHAR2(100);    
 
  BEGIN
 
    FOR rec_imp_inv IN cur_imp_invoices
    LOOP
      BEGIN
 
        lv_approval_status    := NULL;
        lv_funds_return_code  := NULL;
        ln_holds_cnt          := NULL;
 
        IF (AP_APPROVAL_PKG.BATCH_APPROVAL
                (p_run_option           => NULL,
                 p_sob_id               => 1234,
                 p_inv_start_date       => NULL,
                 p_inv_end_date         => NULL,
                 p_inv_batch_id         => NULL,
                 p_vendor_id            => NULL,
                 p_pay_group            => NULL,
                 p_invoice_id           => rec_imp_inv.invoice_id,
                 p_entered_by           => NULL,
                 p_debug_switch         => 'N',
                 p_conc_request_id      => fnd_profile.VALUE('CONC_REQUEST_ID'),
                 p_commit_size          => NULL,
                 p_org_id               => 123,
                 p_report_holds_count   => ln_holds_cnt,
                 p_transaction_num      => NULL))   
       THEN
        DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
                      ||' Invoice Validated.Status(holds count) : '                   
                      ||ln_holds_cnt);
       ELSE
         DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
                      ||' Invoice Validation Failed ');
       END IF;          
 
        ln_processed_cnt := ln_processed_cnt+1;
 
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
    ||' Invoice Validation failed with unhandled exception.Error:'||SQLERRM);
          ln_failed_cnt := ln_failed_cnt + 1;
      END;
    END LOOP;
    pov_err_message := 'PROCESSED: '||ln_processed_cnt||' FAILED: '||ln_failed_cnt;
  END;