Search This Blog

Monday, May 13, 2013

Script to Convert AP Invoices to Approved Status


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;