Query to Extract all pending approvals from Self Service

SELECT TO_CHAR (hat.creation_date, 'DD-MON-YYYY HH:MI:SS AM')

          trans_creation_date,

       hat.transaction_id,

       hat.process_name,

       hat.transaction_ref_table,

       per.person_id,

       per.employee_number,

       per.full_name,

       hat.item_key,

       (SELECT LISTAGG (psah.user_name, CHR (10))

                  WITHIN GROUP (ORDER BY psah.user_name)

          FROM apps.pqh_ss_transaction_history psth,

               apps.pqh_ss_approval_history psah

         WHERE     psah.transaction_history_id = psth.transaction_history_id

               AND psth.item_key = hat.item_key)

          last_action_taken_by,

       (SELECT LISTAGG (psah.action, CHR (10))

                  WITHIN GROUP (ORDER BY psah.creation_date)

          FROM apps.pqh_ss_transaction_history psth,

               apps.pqh_ss_approval_history psah

         WHERE     psah.transaction_history_id = psth.transaction_history_id

               AND psth.item_key = hat.item_key)

          last_action,

       (SELECT LISTAGG (

                  TO_CHAR (psah.last_update_date,

                           'DD-MON-YYYY HH24:MI:SS AM'),

                  CHR (10))

               WITHIN GROUP (ORDER BY psah.creation_date)

          FROM apps.pqh_ss_transaction_history psth,

               apps.pqh_ss_approval_history psah

         WHERE     psah.transaction_history_id = psth.transaction_history_id

               AND psth.item_key = hat.item_key)

          last_action_date,

       (SELECT LISTAGG (wf.recipient_role, CHR (10))

                  WITHIN GROUP (ORDER BY wf.recipient_role)

          FROM wf_notifications wf

         WHERE     wf.item_key = hat.item_key

               AND wf.message_name = 'HR_EMBED_RN_NTF_APPR_MSG'

               AND wf.status = 'OPEN')

          next_approver

  FROM apps.hr_api_transactions hat,

       apps.hr_lookups hl,

       apps.per_all_people_f per

 WHERE     hl.lookup_type = 'PQH_SS_TRANSACTION_STATUS'

       AND hl.lookup_code = hat.status

       AND UPPER (hl.meaning) = 'PENDING APPROVAL'

       AND hat.selected_person_id = per.person_id

       AND TRUNC (hat.creation_date) BETWEEN per.effective_start_date

                                         AND per.effective_end_date

       AND hat.selected_person_id =

              NVL (:l_person_id, hat.selected_person_id)

       AND per.business_group_id = :l_business_group_id

API to Delete EIT Structure

--select * from fnd_descr_flex_contexts  where descriptive_flex_context_code = 'XXHR_XXX_EDUCATION'-- EIT Details

--select *   from fnd_descr_flex_col_usage_vl where descriptive_flex_context_code = 'XXHR_XXX_EDUCATION' - EIT Column Details

DECLARE

   CURSOR c1

   IS

      SELECT *

        FROM fnd_descr_flex_contexts

       WHERE descriptive_flex_context_code = 'XXHR_XXX_EDUCATION';

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         fnd_flex_dsc_api.delete_context (

            appl_short_name   => 'PER',                                  -- HR

            flexfield_name    => i.descriptive_flexfield_name,

            context           => i.descriptive_flex_context_code);

         DBMS_OUTPUT.put_line (

               i.descriptive_flex_context_code

            || ' has been deleted Successfully!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

                  ' Inner Exception: '

               || i.descriptive_flex_context_code

               || ' - '

               || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' Main Exception: ' || SQLERRM);

END;

Payment Method Master Query with all Tab Details

SELECT popmf.org_payment_method_id,

       popmf.org_payment_method_name,

       pea.external_account_id, -- Bank information --> Get Segment Information from "Bank Details KeyFlexField" KF in regard with your legislation

       pea.segment1 bank_segment1, -- Check the KFF, get the look ups used and decode the values of segments

       pea.segment2 bank_segment2,

       pea.segment4 bank_segment4,

       pea.segment5 bank_segment5,

       ppga.set_of_books_id,

       (SELECT name

          FROM gl_ledgers

         WHERE ledger_id = ppga.set_of_books_id)

          ledger_name,

       ppga.gl_cash_ac_id,

       (SELECT    segment1

               || '.'

               || segment2

               || '.'

               || segment3

               || '.'

               || segment4

               || '.'

               || segment5

               || '.'

               || segment6

               || '.'

               || segment7

          FROM gl_code_combinations

         WHERE code_combination_id = ppga.gl_cash_ac_id)

          gl_cash_account,

       ppga.gl_cash_clearing_ac_id,

       (SELECT    segment1

               || '.'

               || segment2

               || '.'

               || segment3

               || '.'

               || segment4

               || '.'

               || segment5

               || '.'

               || segment6

               || '.'

               || segment7

          FROM gl_code_combinations

         WHERE code_combination_id = ppga.gl_cash_clearing_ac_id)

          cash_clearning_account,

       ppga.gl_control_ac_id,

       (SELECT    segment1

               || '.'

               || segment2

               || '.'

               || segment3

               || '.'

               || segment4

               || '.'

               || segment5

               || '.'

               || segment6

               || '.'

               || segment7

          FROM gl_code_combinations

         WHERE code_combination_id = ppga.gl_cash_clearing_ac_id)

          payroll_control_account,

       cbaua.bank_account_id cash_mgmt_bank_account_id,

       cba.bank_account_name cash_mgmt_bank_account_name,

       popmf.transfer_to_gl_flag,

       popmf.cost_payment,

       popmf.cost_cleared_payment,

       popmf.cost_cleared_void_payment

  FROM pay_org_payment_methods_f popmf,

       pay_external_accounts pea,

       pay_payment_gl_accounts_f ppga,

       ce_bank_acct_uses_all cbaua,

       ce_bank_accounts cba

WHERE     popmf.business_group_id = 81

       AND popmf.org_payment_method_id = 72

       AND popmf.external_account_id = pea.external_account_id

       AND TRUNC (SYSDATE) BETWEEN popmf.effective_start_date

                               AND popmf.effective_end_date

       AND pea.enabled_flag = 'Y'

       AND ppga.org_payment_method_id = popmf.org_payment_method_id

       AND TRUNC (SYSDATE) BETWEEN ppga.effective_start_date

                               AND ppga.effective_end_date

       AND cbaua.payroll_bank_account_id = popmf.external_account_id

       AND cba.bank_account_id = cbaua.bank_account_id