Script to Convert AP Invoices to Approved Status

Script to Convert AP Invoices in Approved Status

PROCEDURE approve_converted_invoices (
   errorbuf     VARCHAR2,
   retcode      NUMBER,
   ip_op_unit   VARCHAR2
)
IS
   CURSOR app_inv_cur
   IS
      SELECT DISTINCT aia.invoice_num inv_number, stg.ls_op_unit op_unit
                 FROM ap_invoices_all aia, xxx.xxx_ap_invoices_conv_stg stg
                WHERE 1 = 1
                  AND aia.invoice_num = stg.ls_sup_inv_num
                  AND aia.invoice_num IN (SELECT ls_sup_inv_num
                                            FROM xxx.xxx_ap_invoices_conv_stg
                                           WHERE status_stg = 'VAL')
                  AND aia.wfapproval_status = 'REQUIRED'
                  AND stg.ls_op_unit = ip_op_unit;

   --'VLAIDATED');
   l_sub_request_id      NUMBER         := NULL;
   -- l_user_id         NUMBER         := 1581;
    --apps.fnd_global.user_id; --
   l_resp_id             NUMBER         := NULL;
   l_app_id              NUMBER         := NULL;
   v_request_completed   BOOLEAN;
   v_request_id          NUMBER;
   v_phase               VARCHAR2 (80)  := NULL;
   v_status              VARCHAR2 (80)  := NULL;
   v_dev_phase           VARCHAR2 (30)  := NULL;
   v_dev_status          VARCHAR2 (30)  := NULL;
   v_message             VARCHAR2 (240);
BEGIN
   FOR app_inv_rec IN app_inv_cur
   LOOP
      BEGIN
         SELECT DISTINCT fr.responsibility_id, frx.application_id
                    INTO l_resp_id, l_app_id
                    FROM apps.fnd_responsibility frx,
                         apps.fnd_responsibility_tl fr
                   WHERE fr.responsibility_id = frx.responsibility_id
                     AND UPPER (fr.responsibility_name) LIKE
                            UPPER (DECODE (app_inv_rec.op_unit,
                                           'Payables Manager'
                                          )
                                  );

         -- fnd_client_info.set_org_context (85);
         IF     l_user_id IS NOT NULL
            AND l_resp_id IS NOT NULL
            AND l_app_id IS NOT NULL
         THEN
            DBMS_OUTPUT.put_line ('aa');
            apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_app_id);
            l_sub_request_id :=
               apps.fnd_request.submit_request ('SQLAP',
                                                'APXIAWRE',
                                                'Invoice Approval Workflow',
                                                NULL,
                                                FALSE,
                                                NULL,
                                                app_inv_rec.inv_number,
                                                NULL,
                                                NULL
                                               );
            COMMIT;

            LOOP
               v_request_completed :=
                  apps.fnd_concurrent.wait_for_request
                              (l_sub_request_id                  -- Request ID
                                               ,
                               20
                                 -- Time Interval
                  ,
                               0
                                -- Total Time to wait
                  ,
                               v_phase
                                      -- Phase displyed on screen
                  ,
                               v_status          -- Status displayed on screen
                                       ,
                               v_dev_phase
                                          -- Phase available for developer
                  ,
                               v_dev_status  -- Status available for developer
                                           ,
                               v_message
                              -- Execution Message
                              );
               EXIT WHEN v_request_completed;
            END LOOP;
         END IF;

         DBMS_OUTPUT.put_line (l_sub_request_id);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Failed in submiting the Program: '
                                  || SQLERRM
                                 );
      END;
   END LOOP;
END approve_converted_invoices;