EBS R12 AP Invoice Auto Approval By Custom Procedure

CREATE OR REPLACE PROCEDURE xxap_inv_auto_approval (p_invoice_id IN NUMBER)
IS
   lv_invoice_amount          ap_invoices_all.invoice_amount%TYPE;
   lv_validation_request_id   ap_invoices_all.validation_request_id%TYPE;
   lv_wfapproval_status       ap_invoices_all.wfapproval_status%TYPE;  
   lv_hist_id                 NUMBER;
   lv_user_id                 NUMBER;
   lv_login_id                NUMBER;
   lv_user_name               VARCHAR2 (100)                  := 'FIN_INV';
   lv_validation_status       VARCHAR2 (60);
   lv_stop_approval_result    BOOLEAN;
   lv_hist_rec                ap_inv_aprvl_hist_all%ROWTYPE;
   lv_org_id                  ap_invoices_all.org_id%TYPE;

BEGIN
   BEGIN
      SELECT org_id, invoice_amount, wfapproval_status
        INTO lv_org_id, lv_invoice_amount, lv_wfapproval_status
        FROM ap_invoices_all
       WHERE invoice_id = p_invoice_id;

      SELECT user_id
        INTO lv_user_id
        FROM fnd_user
       WHERE user_name = lv_user_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,'Exception in first block-' || SQLERRM
                           );
         DBMS_OUTPUT.put_line ('Exception in first block-' || SQLERRM);
   END;

   BEGIN
      lv_validation_status :=
         apps.ap_invoices_pkg.get_approval_status (p_invoice_id,
                                                   NULL,
                                                   NULL,
                                                   NULL
                                                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line
                        (fnd_file.LOG,
                            'Exception retrieving invoice validation status-'
                         || SQLERRM
                        );
         DBMS_OUTPUT.put_line
                         (   'Exception retrieving invoice validation status-'
                          || SQLERRM
                         );
   END;

   IF (UPPER (lv_validation_status) <> 'APPROVED')
   THEN
      fnd_file.put_line (fnd_file.LOG, 'Invoice is not validated');
      DBMS_OUTPUT.put_line ('Invoice is not validated');
   ELSE
      /*set values for manual approval*/
      IF (lv_wfapproval_status = 'INITIATED')
      THEN
         lv_stop_approval_result :=
            ap_workflow_pkg.stop_approval
                               (p_invoice_id,
                                NULL,
                                'INV_SUM_ACTIONS_WFAPPROVE.do_force_approval'
                               );

         --Bug5527190
         IF lv_stop_approval_result = TRUE
         THEN
            fnd_file.put_line
                             (fnd_file.LOG,
                              'Invoice approval workflow is forcibly stopped'
                             );
            DBMS_OUTPUT.put_line
                              ('Invoice approval workflow is forcibly stopped');
         END IF;
      END IF;

      UPDATE ap_invoices_all
         SET wfapproval_status = 'MANUALLY APPROVED'
       WHERE invoice_id = p_invoice_id;

      UPDATE ap_invoice_lines_all
         SET wfapproval_status = 'MANUALLY APPROVED'
       WHERE invoice_id = p_invoice_id;

      --insert into the history table
      lv_hist_rec.history_type := 'DOCUMENTAPPROVAL';
      lv_hist_rec.invoice_id := p_invoice_id;
      lv_hist_rec.iteration := 0;
      lv_hist_rec.org_id := lv_org_id;
      lv_hist_rec.approver_name := lv_user_name;
      lv_hist_rec.amount_approved := lv_invoice_amount;
      lv_hist_rec.created_by := lv_user_id;
      lv_hist_rec.last_updated_by := lv_user_id;
      lv_hist_rec.last_update_login := -1;
      lv_hist_rec.creation_date := SYSDATE;
      lv_hist_rec.last_update_date := SYSDATE;
      lv_hist_rec.response := 'MANUALLY APPROVED';
      lv_hist_rec.approver_comments := 'System Auto Approved';
      ap_workflow_pkg.insert_history_table (lv_hist_rec);
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG,
                         'Exception in auto approval main block-' || SQLERRM
                        );
      DBMS_OUTPUT.put_line ('Exception in auto approval main block-' || SQLERRM);
END xxap_inv_auto_approval;