Apps R12 API to Cancel Workflow, Purge Notification and Rollback Transaction

DECLARE

   CURSOR c1

   IS

      SELECT *

        FROM (

SELECT  transaction_id,

                     process_name,

                     item_type,

                     item_key,

                     transaction_effective_date

FROM  HR_API_TRANSACTIONS

WHERE SELECTED_PERSON_ID 

IN (SELECT PERSON_ID FROM PER_PEOPLE_X

WHERE EMPLOYEE_NUMBER IN

('12345'

))

AND STATUS = 'Y'

AND CREATION_DATE NOT LIKE SYSDATE -1

AND ITEM_KEY = '3573'

AND ITEM_TYPE = 'HRSSA'

ORDER BY CREATION_DATE DESC

);

   l_cancel_workflow   VARCHAR2 (1) := 'N';

BEGIN

   FOR i IN c1

   -- Block to Cancel Workflow

   LOOP

      l_cancel_workflow := 'N';

      BEGIN

         wf_engine.

          abortprocess (itemtype   => i.item_type,

                        itemkey    => i.item_key,

                        process    => i.process_name);

         COMMIT;

         l_cancel_workflow := 'Y';

         DBMS_OUTPUT.

          put_line ('Item Key has been Aborted/Cancelled: ' || i.item_key);

      EXCEPTION

         WHEN OTHERS

         THEN

            l_cancel_workflow := 'N';

            DBMS_OUTPUT.

             put_line (

               'Cancel Workflow Exception: ' || SQLERRM || '- ' || i.item_key);

      END;

      IF l_cancel_workflow = 'Y'

      THEN

         -- Block to Purge Notification

         BEGIN

            wf_purge.total (itemtype => i.item_type, itemkey => i.item_key);

            DBMS_OUTPUT.put_line ('Notification Purged : ' || i.item_key);

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.

                put_line (

                     'Purge Notification Exception: '

                  || SQLERRM

                  || '- '

                  || i.item_key);

         END;

         -- Block to Rollback Transaction

         BEGIN

            hr_transaction_api.rollback_transaction (i.transaction_id);

            DBMS_OUTPUT.

             put_line ('Transaction Rolled Back : ' || i.transaction_id);

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.

                put_line (

                     'Purge Notification Exception: '

                  || SQLERRM

                  || '- '

                  || i.item_key);

         END;

      END IF;

   END LOOP;

END;

R12 Assets Query

 SELECT DISTINCT fab.asset_id ASSET_ID

                , fb.book_type_code asset_book -- *

                , fth.transaction_name transaction_name

                , fab.asset_number asset_number

                , REPLACE (fatl.description, ',', ' ') asset_description --*

                , NULL tag_number

                , fab.manufacturer_name manufacturer

                , fab.serial_number serial_number

                , REPLACE (fab.model_number, ',', '') model

                , fab.asset_type asset_type

                , TO_CHAR (fb.cost, 'fm999999999.90') fb_cost -- *

                , TO_CHAR (fb.date_placed_in_service, 'RRRR/MM/DD') date_placed_in_service

                , fct.prorate_convention_code prorate_convention

                , fab.current_units asset_units -- *

                , fcb.segment1 asset_category_segment1

                , fcb.segment2 asset_category_segment2

                , fcb.segment3 asset_category_segment3

                , fcb.segment4 asset_category_segment4

                , fcb.segment5 asset_category_segment5

                , fcb.segment6 asset_category_segment6

                , fcb.segment7 asset_category_segment7

                , NULL posting_status -- CHECK

                , NULL queue_name

                , fai.feeder_system_name feeder_system

                , (SELECT fab1.asset_number

                     FROM apps.fa_additions_b fab1

                    WHERE asset_id = fab.parent_asset_id)

                     parent_asset

                , fak.segment1 asset_key_segment1

                , fab.property_type_code property_type

                , fab.property_1245_1250_code property_class

                , TO_CHAR (fds.ytd_deprn, 'fm999999999.90') ytd_depreciation

                , TO_CHAR (fds.deprn_reserve, 'fm999999999.90') depreciation_reserve

                , fm.method_code depreciation_method

                , fm.life_in_months life_in_months

                , TO_CHAR (SUM (fai.payables_cost), 'fm9999999.90') invoice_cost

    FROM apps.fa_additions_b fab

       , (SELECT * FROM apps.fa_additions_tl WHERE language = 'US') fatl

       , apps.fa_books fb

       , apps.fa_transaction_headers fth

       , apps.fa_categories_b fcb

       , apps.fa_asset_keywords fak

       , apps.fa_methods fm

       , apps.fa_asset_invoices fai

       , apps.fa_convention_types fct

       , apps.fa_distribution_history fdh

       --   , apps.fa_deprn_summary fds

       , (SELECT t.* FROM (SELECT fds.* , RANK () OVER (PARTITION BY fds.asset_id ORDER BY fds.deprn_run_date DESC)  latest  FROM apps.fa_deprn_summary fds) t WHERE t.latest = 1) fds

       , apps.fa_book_controls fbc

       , apps.gl_code_combinations gcc

       , apps.gl_ledgers gl

   WHERE     1 = 1

         AND fb.asset_id NOT IN (SELECT v.asset_id

                                   FROM apps.fa_transaction_history_trx_v v

                                  WHERE v.transaction_type_code = 'FULL RETIREMENT')

         AND fab.asset_category_id = fcb.category_id

         AND fab.asset_id = fatl.asset_id

         AND fb.asset_id = fatl.asset_id

         AND fab.asset_id = fb.asset_id

         AND fb.asset_id = fth.asset_id

         AND fb.BOOK_TYPE_CODE =:P_BOOK_TYPE_CODE

         AND fb.book_type_code = fth.book_type_code

         AND fbc.book_type_code = fth.book_type_code

         AND fb.transaction_header_id_in = fth.transaction_header_id

         AND fab.asset_key_ccid = fak.code_combination_id(+)

         AND fb.deprn_method_code = fm.method_code(+)

         AND fb.life_in_months = fm.life_in_months(+)

         AND fb.asset_id = fai.asset_id(+) --fab

         AND fb.prorate_convention_code = fct.prorate_convention_code

         AND fb.asset_id = fdh.asset_id(+) -- fab

         AND fb.book_type_code = fdh.book_type_code(+)

         AND fdh.code_combination_id = gcc.code_combination_id(+)

         AND fab.asset_id = fds.asset_id

         AND fb.asset_id = fds.asset_id

         AND fb.book_type_code = fds.book_type_code(+)

         AND fb.book_type_code = fbc.book_type_code

         AND fbc.set_of_books_id = gl.ledger_id

         AND fb.date_ineffective IS NULL

         AND fai.date_ineffective IS NULL

         AND fdh.asset_id = fab.asset_id

         AND fdh.retirement_id IS NULL

         AND FAB.ASSET_ID IN (70,112          )

GROUP BY fab.asset_id

       , fb.book_type_code

       , fth.transaction_name

       , fab.asset_number

       , fatl.description

       , fab.manufacturer_name

       , fab.serial_number

       , fab.model_number

       , fab.asset_type

       , fb.cost

       , fb.date_placed_in_service

       , fct.prorate_convention_code

       , fab.current_units

       , fcb.segment1

       , fcb.segment2

       , fcb.segment3

       , fcb.segment4

       , fcb.segment5

       , fcb.segment6

       , fcb.segment7

       , fai.feeder_system_name

       , fak.segment1

       , fak.segment2

       , fak.segment3

       , fak.segment4

       , fak.segment5

       , fak.segment6

       , fak.segment7

       , fak.segment8

       , fak.segment9

       , fak.segment10

       , fab.property_type_code

       , fab.property_1245_1250_code

       , fab.owned_leased

       , fb.depreciate_flag

       , fm.method_code

       , fm.life_in_months

       , gcc.segment1

       , gcc.segment2

       , gcc.segment3

       , fb.original_deprn_start_date

       , fab.parent_asset_id

       , gl.currency_code

       , fds.ytd_deprn

       , fds.deprn_reserve

ORDER BY 1