Script to Validate AP Converted Invoices

 Script to Validate AP Invoices

PROCEDURE validate_converted_invoices (errorbuf VARCHAR2, retcode NUMBER)
IS
   CURSOR val_inv_cur
   IS
      SELECT DISTINCT aba.batch_name, stg.ls_op_unit, stg.ls_org_id,
                      aba.batch_id
                 FROM apps.ap_invoices_all aia,
                      apps.ap_batches_all aba,
                      xxx.xxx_ap_invoices_conv_stg stg
                WHERE aia.batch_id = aba.batch_id
                  AND aia.invoice_num = stg.ls_sup_inv_num
                  AND invoice_num IN (SELECT ls_sup_inv_num
                                        FROM xxx.xxx_ap_invoices_conv_stg
                                       WHERE status_stg IN ('L', 'S'));

   CURSOR update_loaded_inv
   IS
      SELECT DISTINCT aia.invoice_num, aia.invoice_id, aba.batch_name,
                      stg.ls_op_unit op_unit, aia.vendor_id
                 FROM apps.ap_invoices_all aia,
                      xxx.xxx_ap_invoices_conv_stg stg,
                      apps.ap_batches_all aba
                WHERE 1 = 1
                  AND aia.invoice_num = stg.ls_sup_inv_num
                  AND aia.vendor_id = stg.new_vendor_id
                  AND aia.batch_id = aba.batch_id
                  AND aia.invoice_num IN (SELECT ls_sup_inv_num
                                            FROM xxx.xxx_ap_invoices_conv_stg
                                           WHERE status_stg IN ('L', 'S'));

   l_sub_request_id      NUMBER         := NULL;
   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 upd_inv_rec IN update_loaded_inv
   LOOP
      BEGIN
         UPDATE xxx.xxx_ap_invoices_conv_stg stg
            SET invoice_id = upd_inv_rec.invoice_id,
                batch_name = upd_inv_rec.batch_name
          WHERE ls_sup_inv_num = upd_inv_rec.invoice_num
            AND new_vendor_id = upd_inv_rec.vendor_id;

         COMMIT;
      END;
   END LOOP;

   FOR val_inv_rec IN val_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 (val_inv_rec.ls_op_unit,
                                           'Payables Manager'
                                          )
                                  );

         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);
            DBMS_OUTPUT.put_line (l_user_id);
            DBMS_OUTPUT.put_line (l_resp_id);
            DBMS_OUTPUT.put_line (l_app_id);
            l_sub_request_id :=
               apps.fnd_request.submit_request ('SQLAP',
                                                'APPRVL',
                                                'Invoice Validation',
                                                NULL,
                                                FALSE,
                                                val_inv_rec.ls_org_id,
                                                'All',
                                                val_inv_rec.batch_id,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL
                                               --'N',
                                               --1000
                                               );
            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
                              );

               IF v_request_completed
               THEN
                  UPDATE xxx.xxx_ap_invoices_conv_stg
                     SET status_stg = 'VAL'
                   WHERE status_stg IN ('L', 'S')
                     AND ls_sup_inv_num IN (
                                         SELECT invoice_num
                                           FROM apps.ap_invoices_all
                                          WHERE batch_id =
                                                          val_inv_rec.batch_id);

                  COMMIT;
               END IF;

               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 validate_converted_invoices;