SQL to Get Departnment Hierarcy in Single line in Fusion

SELECT
h_child.organization_id
,LISTAGG(haou.name , '|') WITHIN GROUP (Order by distance) DeptHierarchy
FROM 
per_dept_tree_node_rf    pgtn_rf
,hr_all_organization_units haou
,hr_all_organization_units h_child
WHERE
pgtn_rf.ancestor_pk1_value = haou.organization_id
and pgtn_rf.pk1_value = h_child.organization_id  
and pgtn_rf.tree_code  ='XXXX_DEPT_STRUCTURE'
and NVL(:P_START_DATE,trunc(SYSDATE))  between haou.effective_start_date and haou.effective_end_date
and EXISTS(
SELECT 1
FROM
FND_TREE_VERSION ftv
WHERE
ftv.tree_code = pgtn_rf.tree_code
AND pgtn_rf.tree_version_id = ftv.tree_version_id
AND ftv.status = 'ACTIVE'
AND SYSDATE  between TRUNC(ftv.effective_start_date) AND TRUNC(ftv.effective_end_date)
)

No Data shown in Financial Tax Register Report after completion

Financial Tax register populate data on following tables. 

ZX_REP_CONTEXT_T
ZX_REP_TRX_DETAIL_T
ZX_REP_ACTG_EXT_T
ZX_REP_TRX_JX_EXT_T

Which can be referred using view ZX_REP_EXTRACT_V

Report can be run using parameter SUBMIT_PURGE, which is to delete data post completion. 

Purging Routine -  ZX_EXTRACT_PKG.purge(p_request_id)

This routine is defined to DELETE transaction for given REQUEST ID when profile  FND: Debug Log Enabled  is set No. 

If Post completion there is no data in above view/tables, please check if Program is subimtted with SUBMIT PURGE parameter ? 

To Validate if report is generating data or not, set the profile value to YES and run the program. This will not purge the tables. 

Refer metalink note for more detail on Financial tax Register - 

How to Use and Troubleshoot the Financial Tax Register in Release 12 E-Business Tax (Doc ID 1073468.1)