4 Replies Latest reply on Mar 4, 2019 4:07 PM by GauravNagpal

    Delete records from table by removing hard code values

    GauravNagpal

      Experts,

       

      I have a function which delete records based on the PV_Interface_id  value but now i would like to remove this hard coded values from PV_INTERFACE_ID and would like to store in the LOOKUP/Value set values. Could you please suggest me the best design of doing this implementation with little hint.

       

      FUNCTION delete_interface_records (pn_batch_id       IN VARCHAR2,

                                         pv_interface_id   IN VARCHAR2)

          RETURN NUMBER

      IS

      BEGIN

          --ap invoice interface

          IF    UPPER (pv_interface_id) = 'APINT04'

             OR UPPER (pv_interface_id) = 'APINT15'

             OR UPPER (pv_interface_id) = 'APINT17'

             OR UPPER (pv_interface_id) = 'APINT19'

             OR UPPER (pv_interface_id) = 'APINT28'

             OR                                            

               UPPER (pv_interface_id) = 'APINT31'

             OR                                             

               UPPER (pv_interface_id) = 'APINT08'

             OR UPPER (pv_interface_id) = 'APINT101'

             OR UPPER (pv_interface_id) = 'APINT36'

             OR                                               

               UPPER (pv_interface_id) = 'APINT37'

             OR                                               

               UPPER (pv_interface_id) = 'APINT45'

             OR UPPER (pv_interface_id) = 'APINT47'

             OR UPPER (pv_interface_id) = 'APINT49'

             OR UPPER (pv_interface_id) = 'APINT55'

             OR UPPER (pv_interface_id) = 'APINT56'

             OR UPPER (pv_interface_id) = 'APINT59'

             OR UPPER (pv_interface_id) = 'APINT60'

             OR UPPER (pv_interface_id) = 'APINT61'

             OR UPPER (pv_interface_id) = 'APINT62'

             OR UPPER (pv_interface_id) = 'APINT64'

             OR UPPER (pv_interface_id) = 'APINT103'

          THEN

              BEGIN

                  DELETE FROM ap_invoices_interface

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

       

       

              BEGIN

                  DELETE FROM ap_invoice_lines_interface

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

         

              IF UPPER (pv_interface_id) = 'APINT08'

              THEN

                  BEGIN

                      DELETE FROM xxrci_ss_work_order

                            WHERE attribute1 = pn_batch_id;

       

       

                      COMMIT;

                  EXCEPTION

                      WHEN NO_DATA_FOUND

                      THEN

                          RETURN 1;

                      WHEN OTHERS

                      THEN

                          RETURN 1;

                  END;

              END IF;

       

          ELSIF UPPER (pv_interface_id) = 'GLINT03'

          THEN

              BEGIN

                  DELETE FROM gl_budget_interface

                        WHERE request_id = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

       

       

          ELSIF UPPER (pv_interface_id) = 'GLINT16'

          THEN

              BEGIN

                  DELETE FROM gl_daily_rates_interface

                        WHERE ATTRIBUTE10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          --gl interface

          ELSIF    UPPER (pv_interface_id) = 'GLINT05'

                OR UPPER (pv_interface_id) = 'GLINT06'

                OR UPPER (pv_interface_id) = 'GLINT07'

                OR UPPER (pv_interface_id) = 'GLINT08'

                OR UPPER (pv_interface_id) = 'GLINT09'

                OR UPPER (pv_interface_id) = 'GLINT10'

                OR UPPER (pv_interface_id) = 'GLINT11'

                OR UPPER (pv_interface_id) = 'GLINT12'

                OR UPPER (pv_interface_id) = 'GLINT13'

                OR UPPER (pv_interface_id) = 'GLINT14'

                OR UPPER (pv_interface_id) = 'GLINT19'

                OR UPPER (pv_interface_id) = 'GLINT20'

                OR UPPER (pv_interface_id) = 'GLINT21'

                OR UPPER (pv_interface_id) = 'GLINT28'

          THEN

              BEGIN

                  DELETE FROM gl_interface

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          --td to ap interface

          ELSIF UPPER (pv_interface_id) = 'APINT14'

          THEN

              BEGIN

                  DELETE FROM ce_statement_headers_int

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

       

       

              BEGIN

                  DELETE FROM ce_statement_lines_interface

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          --titan to projects interface

          ELSIF UPPER (pv_interface_id) = 'PAINT02'

          THEN

              BEGIN

                  DELETE FROM pa_transaction_interface_all

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          --pcard to ap interface

          ELSIF UPPER (pv_interface_id) = 'APINT26'

          THEN

              BEGIN

                  DELETE FROM ap_expense_feed_lines_all

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          --fa interface

          ELSIF UPPER (pv_interface_id) = 'FAINT01'

          THEN

              BEGIN

                  DELETE FROM fa_mass_ext_retirements

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          --titan to po interface

          ELSIF UPPER (pv_interface_id) = 'POINT06'

          THEN

              BEGIN

                  DELETE FROM rcv_headers_interface

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

       

       

              BEGIN

                  DELETE FROM rcv_transactions_interface

                        WHERE attribute10 = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

       

       

              BEGIN

                  DELETE FROM po_interface_errors

                        WHERE batch_id = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          ELSIF UPPER (pv_interface_id) = 'PAINT08'

          THEN

              BEGIN

                  DELETE FROM xxrci_pa_cmnts_outside_system

                        WHERE cmt_creation_date = TO_DATE (SYSDATE);

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          ELSIF UPPER (pv_interface_id) = 'APINT27'

          THEN

              BEGIN

                  DELETE FROM ap_credit_card_trxns_all

                        WHERE record_type = pn_batch_id;

       

       

                  COMMIT;

              EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                      RETURN 1;

                  WHEN OTHERS

                  THEN

                      RETURN 1;

              END;

          END IF;

       

       

          RETURN 0;

      END delete_interface_records;