In this article we will look into how to achieve complex changes only requirements using custom global reports data model.
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 DUALTip: 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.
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