Achieve complex requirements using Custom Global Reports Data Model
Achieve complex requirements using Custom Global Reports Data Model

 760 total views

In this article we will look into how to achieve complex changes only requirements using custom global reports data model.

Want to learn Fusion Technical tools? Checkout this article

We normally come across many cases where we create changes only extract and also add some filter conditions to the etext template to filter out the unwanted rows that are being picked up by the changes only extract.

Sample Usecase:

We need to pull the Direct Deposit information only when some of the fields have changed and if these settings can’t be configured at the extract attribute level using Exclude from Comparison, Mark as changed and Always Display settings, then the only alternative way is to read through the FILE_FRAGMENT XML and write you own custom logic to check for changes to the old and new values and get your requirement done.

Another usecase could be like fetching the changes to the enterprise hire date for the people who join earlier than their tentative join date. if we use the seeded changes functionality all the hires, global transfers, transfers, rehires would also get picked up. And if we add condition in etext template, we we will get a blank file if there are no such employees. However if there the client doesn’t need a blank file then we might need this custom approach to get it done.

We are going to clone the seeded globalreportsdatamodel and modify the dataset with a custom SQL that we can create to read through the XML data present in FILE_FRAGMENT using advanced XML reading functions in SQL like XMLTYPE, XMLSEQUENCE, EXTRACT VALUE, etc.

We need to make sure to add a dummy SQL at the end in order to make the bursting work even if there isn’t any data fetched by the extract or none of the rows meet the custom selection criteria that we write.

Sample Custom Global Reports Data Model Query:

SELECT :payrollactionid payroll_action_id, xml_data.*
  FROM (SELECT EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Payment_ID/text()'
                  ).getstringval () AS payment_id,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Person_Number/text()'
                  ).getstringval () AS person_number,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Person_Last_Name/text()'
                  ).getstringval () AS last_name,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Person_First_Name/text()'
                  ).getstringval () AS first_name,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Worker_Number/text()'
                  ).getstringval () AS worker_number,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Legal_Employer_Name/text()'
                  ).getstringval () AS legal_employer,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Routing_Number/text()'
                  ).getstringval () AS routing_number,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Account_Number/text()'
                  ).getstringval () AS account_number,
               EXTRACT (VALUE (file_fragment),
                        '//DD_Details/Amount/text()'
                       ).getstringval () AS amount,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Amount_Type/text()'
                  ).getstringval () AS amount_type,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Percentage/text()'
                  ).getstringval () AS percentage,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Payment_Method_Name/text()'
                  ).getstringval () AS payment_method_name,
               TO_DATE
                  (SUBSTR
                      (EXTRACT
                          (VALUE (file_fragment),
                           '//DD_Details/PERSONAL_PAY_METHOD_START_DATE/text()'
                          ).getstringval (),
                       1,
                       10
                      ),
                   'YYYY-MM-DD'
                  ) AS start_date,
               TO_DATE
                  (SUBSTR
                      (EXTRACT
                          (VALUE (file_fragment),
                           '//DD_Details/PERSONAL_PAY_METHOD_END_DATE/text()'
                          ).getstringval (),
                       1,
                       10
                      ),
                   'YYYY-MM-DD'
                  ) AS end_date,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Payment_ID_OLD/text()'
                  ).getstringval () AS payment_id_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Person_Number_OLD/text()'
                  ).getstringval () AS person_number_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Person_Last_Name_OLD/text()'
                  ).getstringval () AS last_name_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Person_First_Name_OLD/text()'
                  ).getstringval () AS first_name_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Worker_Number_OLD/text()'
                  ).getstringval () AS worker_number_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Legal_Employer_Name_OLD/text()'
                  ).getstringval () AS legal_employer_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Routing_Number_OLD/text()'
                  ).getstringval () AS routing_number_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Account_Number_OLD/text()'
                  ).getstringval () AS account_number_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Amount_OLD/text()'
                  ).getstringval () AS amount_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Amount_Type_OLD/text()'
                  ).getstringval () AS amount_type_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Percentage_OLD/text()'
                  ).getstringval () AS percentage_old,
               EXTRACT
                  (VALUE (file_fragment),
                   '//DD_Details/Payment_Method_Name_OLD/text()'
                  ).getstringval () AS payment_method_name_old,
               TO_DATE
                  (SUBSTR
                      (EXTRACT
                          (VALUE (file_fragment),
                           '//DD_Details/PERSONAL_PAY_METHOD_START_DATE_OLD/text()'
                          ).getstringval (),
                       1,
                       10
                      ),
                   'YYYY-MM-DD'
                  ) AS start_date_old,
               TO_DATE
                  (SUBSTR
                      (EXTRACT
                          (VALUE (file_fragment),
                           '//DD_Details/PERSONAL_PAY_METHOD_END_DATE_OLD/text()'
                          ).getstringval (),
                       1,
                       10
                      ),
                   'YYYY-MM-DD'
                  ) AS end_date_old
          FROM (SELECT source_id payroll_action_id,
                       XMLTYPE (file_fragment) file_fragment
                  FROM pay_file_details
                 WHERE source_id = :payrollactionid
                   AND LENGTH (file_fragment) <> 0),
               TABLE
                  (XMLSEQUENCE
                      (EXTRACT
                          (file_fragment,
                           '/DD_Extract/Person_Data_Group/Person_DD_Rec/DD_Records'
                          )
                      )
                  ) file_fragment) xml_data
 WHERE ((NVL (payment_id, 'NULL') != 'NULL')
        AND (((NVL (routing_number, 'NULL') = NVL (routing_number_old, 'NULL'))
             OR (NVL (account_number, 'NULL') != NVL (account_number_old, 'NULL')))
			 AND ((NVL (amount, 'NULL') != NVL (amount_old, 'NULL'))
             OR (NVL (percentage, 'NULL') != NVL (percentage_old, 'NULL'))
             OR (NVL (payment_method_name, 'NULL') != NVL (payment_method_name_old, 'NULL'))
             OR (NVL (start_date, 'NULL') != NVL (start_date_old, 'NULL'))
             OR (NVL (end_date, 'NULL') != NVL (end_date_old, 'NULL')))))
UNION
SELECT '-1' payroll_action_id, ' ' payment_id, ' ' person_number, ' ' last_name,
       ' ' first_name, ' ' worker_number, ' ' legal_employer,
       ' ' routing_number, ' ' account_number, ' ' amount, ' ' amount_type,
       ' ' percentage, ' ' payment_method_name,
       TO_DATE ('1900-01-01', 'YYYY-MM-DD') start_date,
       TO_DATE ('4712-12-31', 'YYYY-MM-DD') end_date, ' ' payment_id_old,
       ' ' person_number_old, ' ' last_name_old, ' ' first_name_old,
       ' ' worker_number_old, ' ' legal_employer_old, ' ' routing_number_old,
       ' ' account_number_old, ' ' amount_old, ' ' amount_type_old,
       ' ' percentage_old, ' ' payment_method_name_old,
       TO_DATE ('1900-01-01', 'YYYY-MM-DD') start_date_old,
       TO_DATE ('4712-12-31', 'YYYY-MM-DD') end_date_old
  FROM DUAL

Tip: Use this feature only if you cannot achieve your requirement using the changes only settings at attribute level or you cannot handle this in etext template. It might be a bit complex but it will get your requirements achieved successfully.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has One Comment

  1. Subhan

    Hi Sricharan,
    How can we join my own SQL dataset with global report data model dataset to get DBI’s of an extract with PER_EXT_RESET_UE by using extract tool… If possible could you please publish an article on this….
    Thanks for the blog, its really helpful..
    Regards
    Subhan

Leave a Reply