As you can see on the Calculation page, the Absence Entitlement uses a Formula called SICK FM METHOD 100. Here are 2 screen shots of this element showing the two elements (circled in red) that it uses.
As you can see on the Field-by-Field Definition page for this formula, the first sequence number uses a Variable element called SICK VR METHOD and the sixth sequence number uses an Accumulator element called SICK 100 ENT_ENT.
Wouldn’t it be great if you could see this hierarchical structure in a succinct manner instead of having to navigate to various components and dig through pages and scroll areas? Well, I think I have come up with such a way. Here’s a SQL that you can use to see this hierarchical structure:
/*
GP-AM Element Hierarchy
Purpose - To return a hierarchical view of a Global Payroll/Absence Management Element's children or parents.
Prompts
dir - up or down
elementName - The name of the element
country - The country for which the element is defined
elementType - The type of element. Use one of these codes:
AA Components BR Bracket EG Elem Group GC GenCtrl RR Rounding
AC Accum CT Count EM Error MSg HR HistRule SE Section
AE Abs Entl DD Deduction ER Earnings PO Proration SY SystemElem
AR Array DR Duration FC Fictitious PR Process VR Variable
AT Abs Take DT Date FM Formula RC Rate Code WA WritArray
level - The level to which you want to return rows. For all rows, enter a large number for which you would supopse
there would NOT be a level, such as 999.
*/
WITH PARENT_CHILD_PIN AS
(SELECT * FROM (
SELECT 'N' as FOLLOW, AC.PIN_MBR_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACM_MBR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_MBR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'N' as FOLLOW, AT.PIN_TAKE_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_DAY AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_TAKE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_DAY AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'N' as FOLLOW, DD.PIN_MBR_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACM_MBR_VW DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_MBR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'N' as FOLLOW, EG.PIN_ELEM_NUM AS PIN_NUM, EG.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ELEM_GRP_MBR EG JOIN PS_GP_PIN P ON P.PIN_NUM = EG.PIN_ELEM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = EG.PIN_NUM AND PP.PIN_TYPE = 'EG' WHERE EG.EFFDT = (select MAX(EG1.EFFDT) from PS_GP_ELEM_GRP_MBR EG1 WHERE EG.PIN_NUM = EG1.PIN_NUM AND EG1.EFFDT <= SYSDATE)
union SELECT 'N' as FOLLOW, ER.PIN_MBR_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACM_MBR_VW ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_MBR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_ADJ_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_ADJ_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_ANTARR_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_ANTARR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_ANT_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_ANT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_BASE_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_PCT_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_PD_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_PD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_PYBK_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_PYBK_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_RATE_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_AA_UNIT_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_AA_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_ARR_ACM_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_ARR_ACM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AA.PIN_ENTL_BAL_NUM AS PIN_NUM, AA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AA JOIN PS_GP_PIN P ON P.PIN_NUM = AA.PIN_ENTL_BAL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AA.PIN_NUM AND PP.PIN_TYPE = 'AA'
union SELECT 'Y' as FOLLOW, AC.PIN_ACM_FRML_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_ACM_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_BASIS_DT_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_BASIS_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_BGN_DAY_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_BGN_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_BGN_DT_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_BGN_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_BGN_MTH_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_BGN_MTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_NUM
union SELECT 'Y' as FOLLOW, AC.PIN_PCT_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACM_MBR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_USER_KEY1_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_USER_KEY1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_USER_KEY2_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_USER_KEY2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_USER_KEY3_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_USER_KEY3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_USER_KEY4_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_USER_KEY4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_USER_KEY5_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_USER_KEY5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AC.PIN_USER_KEY6_NUM AS PIN_NUM, AC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACCUMULATOR AC JOIN PS_GP_PIN P ON P.PIN_NUM = AC.PIN_USER_KEY6_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AC.PIN_NUM AND PP.PIN_TYPE = 'AC'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_ADJ_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_ADJ_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_ANTARR_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_ANTARR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_ANT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_ANT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_BASE_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_PCT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_PD_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_PD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_PYBK_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_PYBK_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_RATE_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_AA_UNIT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_AA_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_ADJ_UNIT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_ADJ_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_ARR_ACM_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_ARR_ACM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_BASIS_DT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_BASIS_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_BGN_DAY_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_BGN_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_BGN_DT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_BGN_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_BGN_MTH_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_BGN_MTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_COND_ENT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_COND_ENT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_ENT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_ENT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_GCTL2_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_GCTL2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_GCTL_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_GCTL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_MBR_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AGEN_ACM_VW AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_MBR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_NUM
union SELECT 'Y' as FOLLOW, AE.PIN_PAY_ER_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_PAY_ER_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_PAY_UNIT_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_PAY_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_PRO_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_PRO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_RND_ENTL_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_ENTL AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_RND_ENTL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_USER_KEY1_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_USER_KEY1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_USER_KEY2_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_USER_KEY2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_USER_KEY3_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_USER_KEY3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_USER_KEY4_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_USER_KEY4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_USER_KEY5_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_USER_KEY5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AE.PIN_USER_KEY6_NUM AS PIN_NUM, AE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT AE JOIN PS_GP_PIN P ON P.PIN_NUM = AE.PIN_USER_KEY6_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AE.PIN_NUM AND PP.PIN_TYPE = 'AE'
union SELECT 'Y' as FOLLOW, AR.PIN_ELEM_NUM AS PIN_NUM, AR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ARRAY_KEY AR JOIN PS_GP_PIN P ON P.PIN_NUM = AR.PIN_ELEM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AR.PIN_NUM AND PP.PIN_TYPE = 'AR'
union SELECT 'Y' as FOLLOW, AR.PIN_FRML_NUM AS PIN_NUM, AR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ARRAY AR JOIN PS_GP_PIN P ON P.PIN_NUM = AR.PIN_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AR.PIN_NUM AND PP.PIN_TYPE = 'AR'
union SELECT 'Y' as FOLLOW, AR.PIN_FRML_NUM AS PIN_NUM, AR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ARRAY_PRC AR JOIN PS_GP_PIN P ON P.PIN_NUM = AR.PIN_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AR.PIN_NUM AND PP.PIN_TYPE = 'AR'
union SELECT 'Y' as FOLLOW, AR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ARRAY AR JOIN PS_GP_PIN P ON P.PIN_NUM = AR.PIN_NUM
union SELECT 'Y' as FOLLOW, AR.PIN_VAR_NUM AS PIN_NUM, AR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ARRAY_FLD AR JOIN PS_GP_PIN P ON P.PIN_NUM = AR.PIN_VAR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AR.PIN_NUM AND PP.PIN_TYPE = 'AR'
union SELECT 'Y' as FOLLOW, AT.PIN_AMT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_ELM AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_AMT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_ELM AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_BAL_OFFSET_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_BAL_OFFSET_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_DATE_FROM_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_DATE_FROM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_DATE_TO_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_DATE_TO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_DAY_FM_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_DAY_FM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_ELIG_DATE_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_ELIG_DATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_ENT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_ENT AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_ENT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_ENT AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_FCST_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_FCST_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_FCST_RSLT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TK_BAL_V AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_FCST_RSLT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TK_BAL_V AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_FCST_RSLT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TK_FCS_V AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_FCST_RSLT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TK_FCS_V AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_LINKED_TO_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_LINKED_TO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_LINK_COND_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_LINK_COND_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_LINK_PRD_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_LINK_PRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_MIN_PRD_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_MIN_PRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_NEG_ABS_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_NEG_ABS_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_NEG_LMT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_NEG_LMT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_NUM
union SELECT 'Y' as FOLLOW, AT.PIN_PCT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_ELM AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_ELM AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_RATE_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_ELM AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_ELM AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_SOVR_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ELM_DFN_SOVR AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_SOVR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT'
union SELECT 'Y' as FOLLOW, AT.PIN_TAKE_ELEM_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_ELM AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_TAKE_ELEM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_ELM AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_UNIT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE_ELM AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE_ELM AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_WAIT_CNT_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_WAIT_CNT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, AT.PIN_WAIT_PRD_NUM AS PIN_NUM, AT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ABS_TAKE AT JOIN PS_GP_PIN P ON P.PIN_NUM = AT.PIN_WAIT_PRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = AT.PIN_NUM AND PP.PIN_TYPE = 'AT' WHERE AT.EFFDT = (select MAX(AT1.EFFDT) from PS_GP_ABS_TAKE AT1 WHERE AT.PIN_NUM = AT1.PIN_NUM AND AT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_KEY1_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_KEY1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_KEY2_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_KEY2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_KEY3_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_KEY3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_KEY4_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_KEY4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_KEY5_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_KEY5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_NUM
union SELECT 'Y' as FOLLOW, BR.PIN_RND_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_RND_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL1_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL1_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL2_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL2_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL3_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL3_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL4_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL4_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL5_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL5_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL6_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL6_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL6_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL6_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL7_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL7_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL7_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL7_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL8_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL8_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, BR.PIN_VAL8_NUM AS PIN_NUM, BR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_BRACKET_DTL BR JOIN PS_GP_PIN P ON P.PIN_NUM = BR.PIN_VAL8_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = BR.PIN_NUM AND PP.PIN_TYPE = 'BR' WHERE BR.EFFDT = (select MAX(BR1.EFFDT) from PS_GP_BRACKET_DTL BR1 WHERE BR.PIN_NUM = BR1.PIN_NUM AND BR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, CT.PIN_CNT_FRML_NUM AS PIN_NUM, CT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_COUNT CT JOIN PS_GP_PIN P ON P.PIN_NUM = CT.PIN_CNT_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = CT.PIN_NUM AND PP.PIN_TYPE = 'CT' WHERE CT.EFFDT = (select MAX(CT1.EFFDT) from PS_GP_COUNT CT1 WHERE CT.PIN_NUM = CT1.PIN_NUM AND CT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, CT.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_COUNT CT JOIN PS_GP_PIN P ON P.PIN_NUM = CT.PIN_NUM
union SELECT 'Y' as FOLLOW, DD.PIN_AA_ADJ_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_ADJ_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_ANTARR_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_ANTARR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_ANT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_ANT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_BASE_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_PCT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_PD_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_PD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_PYBK_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_PYBK_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_RATE_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AA_UNIT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AA_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_AMT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_AMT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_ARR_ACM_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_ARR_ACM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_ARR_AMT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_ARR_AMT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_BASE_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_BASIS_DT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_BASIS_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_BGN_DAY_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_BGN_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_BGN_DT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_BGN_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_BGN_MTH_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_BGN_MTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_ENTL_BAL_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_ENTL_BAL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_GCTL_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_GCTL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_MBR_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AGEN_ACM_VW DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_MBR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_NUM
union SELECT 'Y' as FOLLOW, DD.PIN_PARTL_FRML_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_PARTL_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_PCT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACM_MBR_VW DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_PCT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_POST_FRML_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_POST_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_PRE_FRML_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_PRE_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_PRO_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_PRO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_PYBK_DED_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_PYBK_DED_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RATE_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RCP_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RCP_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RND_BASE_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RND_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RND_PCT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RND_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RND_RATE_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RND_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RND_RSLV_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RND_RSLV_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_RND_UNIT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_RND_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_SOVR_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ELM_DFN_SOVR DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_SOVR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_UNIT_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD' WHERE DD.EFFDT = (select MAX(DD1.EFFDT) from PS_GP_ERN_DED DD1 WHERE DD.PIN_NUM = DD1.PIN_NUM AND DD1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DD.PIN_USER_KEY1_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_USER_KEY1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_USER_KEY2_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_USER_KEY2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_USER_KEY3_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_USER_KEY3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_USER_KEY4_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_USER_KEY4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_USER_KEY5_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_USER_KEY5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DD.PIN_USER_KEY6_NUM AS PIN_NUM, DD.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT DD JOIN PS_GP_PIN P ON P.PIN_NUM = DD.PIN_USER_KEY6_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DD.PIN_NUM AND PP.PIN_TYPE = 'DD'
union SELECT 'Y' as FOLLOW, DR.PIN_DATE_FROM_NUM AS PIN_NUM, DR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DURATION DR JOIN PS_GP_PIN P ON P.PIN_NUM = DR.PIN_DATE_FROM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DR.PIN_NUM AND PP.PIN_TYPE = 'DR' WHERE DR.EFFDT = (select MAX(DR1.EFFDT) from PS_GP_DURATION DR1 WHERE DR.PIN_NUM = DR1.PIN_NUM AND DR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DR.PIN_DATE_TO_NUM AS PIN_NUM, DR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DURATION DR JOIN PS_GP_PIN P ON P.PIN_NUM = DR.PIN_DATE_TO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DR.PIN_NUM AND PP.PIN_TYPE = 'DR' WHERE DR.EFFDT = (select MAX(DR1.EFFDT) from PS_GP_DURATION DR1 WHERE DR.PIN_NUM = DR1.PIN_NUM AND DR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DR.PIN_FRML_NUM AS PIN_NUM, DR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DURATION DR JOIN PS_GP_PIN P ON P.PIN_NUM = DR.PIN_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DR.PIN_NUM AND PP.PIN_TYPE = 'DR' WHERE DR.EFFDT = (select MAX(DR1.EFFDT) from PS_GP_DURATION DR1 WHERE DR.PIN_NUM = DR1.PIN_NUM AND DR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_DURATION DR JOIN PS_GP_PIN P ON P.PIN_NUM = DR.PIN_NUM
union SELECT 'Y' as FOLLOW, DT.PIN_CALC_DAY_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_CALC_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_CALC_MONTH_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_CALC_MONTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_CALC_YEAR_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_CALC_YEAR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_DATE_DAY_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_DATE_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_DATE_FROM_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_DATE_FROM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_DATE_MONTH_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_DATE_MONTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_DATE_YEAR_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_DATE_YEAR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_EXTR_DAY_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_EXTR_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_EXTR_MONTH_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_EXTR_MONTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_EXTR_YEAR_NUM AS PIN_NUM, DT.PIN_NUM AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_EXTR_YEAR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = DT.PIN_NUM AND PP.PIN_TYPE = 'DT' WHERE DT.EFFDT = (select MAX(DT1.EFFDT) from PS_GP_DATE DT1 WHERE DT.PIN_NUM = DT1.PIN_NUM AND DT1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, DT.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_DATE DT JOIN PS_GP_PIN P ON P.PIN_NUM = DT.PIN_NUM
union SELECT 'Y' as FOLLOW, EG.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ELEM_GRP EG JOIN PS_GP_PIN P ON P.PIN_NUM = EG.PIN_NUM
union SELECT 'Y' as FOLLOW, ER.PIN_AA_ADJ_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_ADJ_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_ANTARR_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_ANTARR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_ANT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_ANT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_BASE_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_PCT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_PD_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_PD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_PYBK_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_PYBK_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_RATE_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AA_UNIT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AA_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_AMT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_AMT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_ARR_ACM_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_ARR_ACM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_ARR_AMT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_ARR_AMT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_BASE_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_BASIS_DT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_BASIS_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_BGN_DAY_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_BGN_DAY_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_BGN_DT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_BGN_DT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_BGN_MTH_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_BGN_MTH_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_ENTL_BAL_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PIN_CMPNT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_ENTL_BAL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_GCTL_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_GCTL_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_MBR_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AGEN_ACM_VW ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_MBR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_NUM
union SELECT 'Y' as FOLLOW, ER.PIN_PARTL_FRML_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_PARTL_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_PCT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ACM_MBR_VW ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_PCT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_POST_FRML_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_POST_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_PRE_FRML_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_PRE_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_PRO_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_PRO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_PYBK_DED_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_PYBK_DED_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RATE_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RCP_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RCP_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RND_BASE_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RND_BASE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RND_PCT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RND_PCT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RND_RATE_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RND_RATE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RND_RSLV_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RND_RSLV_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_RND_UNIT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_RND_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_SOVR_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ELM_DFN_SOVR ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_SOVR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_UNIT_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_ERN_DED ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_UNIT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER' WHERE ER.EFFDT = (select MAX(ER1.EFFDT) from PS_GP_ERN_DED ER1 WHERE ER.PIN_NUM = ER1.PIN_NUM AND ER1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, ER.PIN_USER_KEY1_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_USER_KEY1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_USER_KEY2_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_USER_KEY2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_USER_KEY3_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_USER_KEY3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_USER_KEY4_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_USER_KEY4_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_USER_KEY5_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_USER_KEY5_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, ER.PIN_USER_KEY6_NUM AS PIN_NUM, ER.PIN_NUM AS PARENT_PIN_NUM from PS_GP_AUTOGEN_DFLT ER JOIN PS_GP_PIN P ON P.PIN_NUM = ER.PIN_USER_KEY6_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = ER.PIN_NUM AND PP.PIN_TYPE = 'ER'
union SELECT 'Y' as FOLLOW, FC.PIN_CURPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_IN FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_CURPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_IN FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_CURPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_OUT FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_CURPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_OUT FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_CURPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_OUT_DTL FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_CURPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_OUT_DTL FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_FICTPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_IN FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_FICTPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_IN FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_FICTPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_OUT FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_FICTPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_OUT FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_FICTPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_OUT_DTL FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_FICTPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_OUT_DTL FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_FICTPRD_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_SEG FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_FICTPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_SEG FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_HIST_RULE_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_TBL FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_HIST_RULE_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_TBL FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FC.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_FC_TBL FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_NUM
union SELECT 'Y' as FOLLOW, FC.PIN_PRC_NUM AS PIN_NUM, FC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FC_TBL FC JOIN PS_GP_PIN P ON P.PIN_NUM = FC.PIN_PRC_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FC.PIN_NUM AND PP.PIN_TYPE = 'FC' WHERE FC.EFFDT = (select MAX(FC1.EFFDT) from PS_GP_FC_TBL FC1 WHERE FC.PIN_NUM = FC1.PIN_NUM AND FC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FM.PIN_FRML_FLD1_NUM AS PIN_NUM, FM.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FORMULA_DTL FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_FRML_FLD1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FM.PIN_NUM AND PP.PIN_TYPE = 'FM' WHERE FM.EFFDT = (select MAX(FM1.EFFDT) from PS_GP_FORMULA_DTL FM1 WHERE FM.PIN_NUM = FM1.PIN_NUM AND FM1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FM.PIN_FRML_FLD2_NUM AS PIN_NUM, FM.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FORMULA_DTL FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_FRML_FLD2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FM.PIN_NUM AND PP.PIN_TYPE = 'FM' WHERE FM.EFFDT = (select MAX(FM1.EFFDT) from PS_GP_FORMULA_DTL FM1 WHERE FM.PIN_NUM = FM1.PIN_NUM AND FM1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FM.PIN_FRML_FLD3_NUM AS PIN_NUM, FM.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FORMULA_DTL FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_FRML_FLD3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FM.PIN_NUM AND PP.PIN_TYPE = 'FM' WHERE FM.EFFDT = (select MAX(FM1.EFFDT) from PS_GP_FORMULA_DTL FM1 WHERE FM.PIN_NUM = FM1.PIN_NUM AND FM1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FM.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_FORMULA FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_NUM
union SELECT 'Y' as FOLLOW, FM.PIN_RND1_NUM AS PIN_NUM, FM.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FORMULA_DTL FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_RND1_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FM.PIN_NUM AND PP.PIN_TYPE = 'FM' WHERE FM.EFFDT = (select MAX(FM1.EFFDT) from PS_GP_FORMULA_DTL FM1 WHERE FM.PIN_NUM = FM1.PIN_NUM AND FM1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FM.PIN_RND2_NUM AS PIN_NUM, FM.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FORMULA_DTL FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_RND2_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FM.PIN_NUM AND PP.PIN_TYPE = 'FM' WHERE FM.EFFDT = (select MAX(FM1.EFFDT) from PS_GP_FORMULA_DTL FM1 WHERE FM.PIN_NUM = FM1.PIN_NUM AND FM1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, FM.PIN_RND3_NUM AS PIN_NUM, FM.PIN_NUM AS PARENT_PIN_NUM from PS_GP_FORMULA_DTL FM JOIN PS_GP_PIN P ON P.PIN_NUM = FM.PIN_RND3_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = FM.PIN_NUM AND PP.PIN_TYPE = 'FM' WHERE FM.EFFDT = (select MAX(FM1.EFFDT) from PS_GP_FORMULA_DTL FM1 WHERE FM.PIN_NUM = FM1.PIN_NUM AND FM1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, GC.PIN_FRML_NUM AS PIN_NUM, GC.PIN_NUM AS PARENT_PIN_NUM from PS_GP_GCTL_DTL GC JOIN PS_GP_PIN P ON P.PIN_NUM = GC.PIN_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = GC.PIN_NUM AND PP.PIN_TYPE = 'GC' WHERE GC.EFFDT = (select MAX(GC1.EFFDT) from PS_GP_GCTL_DTL GC1 WHERE GC.PIN_NUM = GC1.PIN_NUM AND GC1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, GC.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_GCTL GC JOIN PS_GP_PIN P ON P.PIN_NUM = GC.PIN_NUM
union SELECT 'Y' as FOLLOW, HR.PIN_CURPRD_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_ELEM HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_CURPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_ELEM HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_DATE_FROM_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_DATE_FROM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_RULE HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_DATE_TO_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_DATE_TO_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_RULE HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_FRML_END_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_FRML_END_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_RULE HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_FRML_PRD_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_FRML_PRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_RULE HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_HISTPRD_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_ELEM HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_HISTPRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_ELEM HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_NUM
union SELECT 'Y' as FOLLOW, HR.PIN_STOP_PROC_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_STOP_PROC_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_RULE HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, HR.PIN_USE_PRD_NUM AS PIN_NUM, HR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_HIST_RULE HR JOIN PS_GP_PIN P ON P.PIN_NUM = HR.PIN_USE_PRD_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = HR.PIN_NUM AND PP.PIN_TYPE = 'HR' WHERE HR.EFFDT = (select MAX(HR1.EFFDT) from PS_GP_HIST_RULE HR1 WHERE HR.PIN_NUM = HR1.PIN_NUM AND HR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PIN_DRIVER_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_DRIVER_NUM <> 0
union SELECT 'Y' as FOLLOW, PIN_USER_FLD1_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_USER_FLD1_NUM <> 0
union SELECT 'Y' as FOLLOW, PIN_USER_FLD2_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_USER_FLD2_NUM <> 0
union SELECT 'Y' as FOLLOW, PIN_USER_FLD3_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_USER_FLD3_NUM <> 0
union SELECT 'Y' as FOLLOW, PIN_USER_FLD4_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_USER_FLD4_NUM <> 0
union SELECT 'Y' as FOLLOW, PIN_USER_FLD5_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_USER_FLD5_NUM <> 0
union SELECT 'Y' as FOLLOW, PIN_USER_FLD6_NUM AS PIN_NUM, PIN_NUM AS PARENT_PIN_NUM FROM PS_GP_PIN P WHERE PIN_USER_FLD6_NUM <> 0
union SELECT 'Y' as FOLLOW, PO.PIN_DENOMIN_NUM AS PIN_NUM, PO.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PRORATION PO JOIN PS_GP_PIN P ON P.PIN_NUM = PO.PIN_DENOMIN_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PO.PIN_NUM AND PP.PIN_TYPE = 'PO' WHERE PO.EFFDT = (select MAX(PO1.EFFDT) from PS_GP_PRORATION PO1 WHERE PO.PIN_NUM = PO1.PIN_NUM AND PO1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PO.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_PRORATION PO JOIN PS_GP_PIN P ON P.PIN_NUM = PO.PIN_NUM
union SELECT 'Y' as FOLLOW, PO.PIN_NUMERATOR_NUM AS PIN_NUM, PO.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PRORATION PO JOIN PS_GP_PIN P ON P.PIN_NUM = PO.PIN_NUMERATOR_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PO.PIN_NUM AND PP.PIN_TYPE = 'PO' WHERE PO.EFFDT = (select MAX(PO1.EFFDT) from PS_GP_PRORATION PO1 WHERE PO.PIN_NUM = PO1.PIN_NUM AND PO1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PR.PIN_COND_NUM AS PIN_NUM, PR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PROCESS_DTL PR JOIN PS_GP_PIN P ON P.PIN_NUM = PR.PIN_COND_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PR.PIN_NUM AND PP.PIN_TYPE = 'PR' WHERE PR.EFFDT = (select MAX(PR1.EFFDT) from PS_GP_PROCESS_DTL PR1 WHERE PR.PIN_NUM = PR1.PIN_NUM AND PR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PR.PIN_GROSS_NUM AS PIN_NUM, PR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PROCESS PR JOIN PS_GP_PIN P ON P.PIN_NUM = PR.PIN_GROSS_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PR.PIN_NUM AND PP.PIN_TYPE = 'PR' WHERE PR.EFFDT = (select MAX(PR1.EFFDT) from PS_GP_PROCESS PR1 WHERE PR.PIN_NUM = PR1.PIN_NUM AND PR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PR.PIN_MIN_NUM AS PIN_NUM, PR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PROCESS PR JOIN PS_GP_PIN P ON P.PIN_NUM = PR.PIN_MIN_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PR.PIN_NUM AND PP.PIN_TYPE = 'PR' WHERE PR.EFFDT = (select MAX(PR1.EFFDT) from PS_GP_PROCESS PR1 WHERE PR.PIN_NUM = PR1.PIN_NUM AND PR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PR.PIN_NET_NUM AS PIN_NUM, PR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PROCESS PR JOIN PS_GP_PIN P ON P.PIN_NUM = PR.PIN_NET_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PR.PIN_NUM AND PP.PIN_TYPE = 'PR' WHERE PR.EFFDT = (select MAX(PR1.EFFDT) from PS_GP_PROCESS PR1 WHERE PR.PIN_NUM = PR1.PIN_NUM AND PR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, PR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_PROCESS PR JOIN PS_GP_PIN P ON P.PIN_NUM = PR.PIN_NUM
union SELECT 'Y' as FOLLOW, PR.PIN_SECT_NUM AS PIN_NUM, PR.PIN_NUM AS PARENT_PIN_NUM from PS_GP_PROCESS_DTL PR JOIN PS_GP_PIN P ON P.PIN_NUM = PR.PIN_SECT_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = PR.PIN_NUM AND PP.PIN_TYPE = 'PR' WHERE PR.EFFDT = (select MAX(PR1.EFFDT) from PS_GP_PROCESS_DTL PR1 WHERE PR.PIN_NUM = PR1.PIN_NUM AND PR1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, RC.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_RATE_CODE RC JOIN PS_GP_PIN P ON P.PIN_NUM = RC.PIN_NUM
union SELECT 'Y' as FOLLOW, RR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_ROUND_RULE RR JOIN PS_GP_PIN P ON P.PIN_NUM = RR.PIN_NUM
union SELECT 'Y' as FOLLOW, SE.PIN_ELEM_NUM AS PIN_NUM, SE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_SECTION_DTL SE JOIN PS_GP_PIN P ON P.PIN_NUM = SE.PIN_ELEM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = SE.PIN_NUM AND PP.PIN_TYPE = 'SE' WHERE SE.EFFDT = (select MAX(SE1.EFFDT) from PS_GP_SECTION_DTL SE1 WHERE SE.PIN_NUM = SE1.PIN_NUM AND SE1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, SE.PIN_FRML_NUM AS PIN_NUM, SE.PIN_NUM AS PARENT_PIN_NUM from PS_GP_SECTION_DTL SE JOIN PS_GP_PIN P ON P.PIN_NUM = SE.PIN_FRML_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = SE.PIN_NUM AND PP.PIN_TYPE = 'SE' WHERE SE.EFFDT = (select MAX(SE1.EFFDT) from PS_GP_SECTION_DTL SE1 WHERE SE.PIN_NUM = SE1.PIN_NUM AND SE1.EFFDT <= SYSDATE)
union SELECT 'Y' as FOLLOW, SE.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_SECTION SE JOIN PS_GP_PIN P ON P.PIN_NUM = SE.PIN_NUM
union SELECT 'Y' as FOLLOW, SY.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_SYSTEM_PIN SY JOIN PS_GP_PIN P ON P.PIN_NUM = SY.PIN_NUM
union SELECT 'Y' as FOLLOW, SY.PIN_SETID_NUM AS PIN_NUM, SY.PIN_NUM AS PARENT_PIN_NUM from PS_GP_SYSTEM_PIN SY JOIN PS_GP_PIN P ON P.PIN_NUM = SY.PIN_SETID_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = SY.PIN_NUM AND PP.PIN_TYPE = 'SY'
union SELECT 'Y' as FOLLOW, VR.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_VARIABLE VR JOIN PS_GP_PIN P ON P.PIN_NUM = VR.PIN_NUM
union SELECT 'Y' as FOLLOW, WA.PIN_ELEM_NUM AS PIN_NUM, WA.PIN_NUM AS PARENT_PIN_NUM from PS_GP_WA_FLD WA JOIN PS_GP_PIN P ON P.PIN_NUM = WA.PIN_ELEM_NUM JOIN PS_GP_PIN PP ON PP.PIN_NUM = WA.PIN_NUM AND PP.PIN_TYPE = 'WA'
union SELECT 'Y' as FOLLOW, WA.PIN_NUM AS PIN_NUM, NULL AS PARENT_PIN_NUM from PS_GP_WA_ARRAY WA JOIN PS_GP_PIN P ON P.PIN_NUM = WA.PIN_NUM
) Y
),
DOWN_STRUCTURE AS
(SELECT 'DOWN' AS DIR
,LEVEL LVL
,LPAD(' '
,4 * (LEVEL - 1)
,'- ') || '[' || PV.DESCRSHORT || '] ' || P.PIN_NM || ' ' ||
P.COUNTRY || ' - ' || TO_CHAR(X.PIN_NUM) STRUCTURE
,sys_connect_by_path('[' || PV.DESCRSHORT || '] ' || P.PIN_NM || ' ' ||
P.COUNTRY || ' - ' || TO_CHAR(X.PIN_NUM), ' / ') PATH
,X.PARENT_PIN_NUM
,X.PIN_NUM
,X.FOLLOW
,PRIOR X.PARENT_PIN_NUM
,PRIOR X.PIN_NUM
,PRIOR X.FOLLOW
FROM PARENT_CHILD_PIN X
JOIN PS_GP_PIN P
ON P.PIN_NUM = X.PIN_NUM
JOIN PS_GP_PIN_TYPE PV
ON P.PIN_TYPE = PV.PIN_TYPE
START WITH UPPER('&dir') = 'DOWN'
AND X.PARENT_PIN_NUM IS NULL
AND P.PIN_NM = UPPER('&elementName')
AND P.COUNTRY = UPPER('&country')
AND P.PIN_TYPE = UPPER('&elementType')
CONNECT BY NOCYCLE UPPER('&dir') = 'DOWN'
AND PRIOR X.PIN_NUM = X.PARENT_PIN_NUM
AND PRIOR X.FOLLOW = 'Y'
AND LEVEL <= &level
ORDER SIBLINGS BY P.PIN_TYPE, P.PIN_NM),
UP_STRUCTURE AS
(SELECT 'UP' AS DIR
,LEVEL AS LVL
,LPAD(' '
,4 * (LEVEL - 1)
,'- ') || '[' || PV.DESCRSHORT || '] ' || P.PIN_NM || ' ' ||
P.COUNTRY || ' - ' || TO_CHAR(X.PIN_NUM) STRUCTURE
,sys_connect_by_path('[' || PV.DESCRSHORT || '] ' || P.PIN_NM || ' ' ||
P.COUNTRY || ' - ' || TO_CHAR(X.PIN_NUM), ' / ') PATH
,X.PARENT_PIN_NUM
,X.PIN_NUM
,X.FOLLOW
,PRIOR X.PARENT_PIN_NUM
,PRIOR X.PIN_NUM
,PRIOR X.FOLLOW
FROM PARENT_CHILD_PIN X
JOIN PS_GP_PIN P
ON P.PIN_NUM = X.PIN_NUM
JOIN PS_GP_PIN_TYPE PV
ON P.PIN_TYPE = PV.PIN_TYPE
START WITH UPPER('&dir') = 'UP'
AND P.PIN_NM = UPPER('&elementName')
AND P.COUNTRY = UPPER('&country')
AND P.PIN_TYPE = UPPER('&elementType')
AND X.PARENT_PIN_NUM IS NOT NULL
AND X.PARENT_PIN_NUM <> 0
CONNECT BY NOCYCLE UPPER('&dir') = 'UP'
AND PRIOR X.PARENT_PIN_NUM = X.PIN_NUM
AND X.FOLLOW = 'Y'
AND LEVEL <= &level
ORDER SIBLINGS BY P.PIN_TYPE, P.PIN_NM)
select DIR, LVL, STRUCTURE from DOWN_STRUCTURE
union all
select DIR, LVL, STRUCTURE from UP_STRUCTURE
;If I run this SQL in my demo database with these parameters
- dir=down
- elementName=SICK 100 ENT
- country=ALL
- elementType=AE
- level=999
I get this result
DIR LVL STRUCTURE
DOWN 1 [Abs Entl] SICK 100 ENT ALL - 1279
DOWN 2 - - [Components] SICK 100 ENT_UNAD ALL - 1281
DOWN 2 - - [Components] SICK 100 ENT_UNP ALL - 1280
DOWN 2 - - [Accum] SICK 100 ENT_ADJU ALL - 1284
DOWN 3 - - - - [Components] SICK 100 ENT_UNAD ALL - 1281
DOWN 3 - - - - [SystemElem] ORIG BEGIN DATE ALL - 188
DOWN 2 - - [Accum] SICK 100 ENT_BAL ALL - 1282
DOWN 3 - - - - [Components] SICK 100 ENT_UNAD ALL - 1281
DOWN 3 - - - - [Components] SICK 100 ENT_UNP ALL - 1280
DOWN 3 - - - - [Abs Entl] SICK 100 ENT ALL - 1279
DOWN 3 - - - - [Formula] SICK FM OFFSET 100 ALL - 1261
DOWN 3 - - - - [SystemElem] ORIG BEGIN DATE ALL - 188
DOWN 2 - - [Accum] SICK 100 ENT_ENT ALL - 1283
DOWN 3 - - - - [Abs Entl] SICK 100 ENT ALL - 1279
DOWN 3 - - - - [SystemElem] ORIG BEGIN DATE ALL - 188
DOWN 2 - - [Accum] SICK 100 ENT_TAKE ALL - 1285
DOWN 3 - - - - [Components] SICK 100 ENT_UNP ALL - 1280
DOWN 3 - - - - [SystemElem] ORIG BEGIN DATE ALL - 188
DOWN 2 - - [Formula] SICK FM METHOD 100 ALL - 1259
DOWN 3 - - - - [Accum] SICK 100 ENT_ENT ALL - 1283
DOWN 4 - - - - - - [Abs Entl] SICK 100 ENT ALL - 1279
DOWN 4 - - - - - - [SystemElem] ORIG BEGIN DATE ALL - 188
DOWN 3 - - - - [Variable] SICK VR METHOD ALL - 1250
DOWN 2 - - [SystemElem] ORIG BEGIN DATE ALL - 188All of the elements listed at Level 2 include all of the items I circled in the screen shots above for the Absence Entitlement element. And near the end of the results you can see under - - [Formula] SICK FM METHOD 100 ALL - 1259 at Level 3 are the 2 elements I circled in the screen shots above for the SICK FM METHOD 100 element. And one of those (the SICK 100 ENT_ENT Accumulator) goes down one more level to show the Elements that accumulator uses.
Some other comments on the SQL:
- The SQL is written for Oracle databases only using Oracle's hierarchical query syntax (STARTS WITH ... CONNECT BY ...) It also makes use of Oracle substitution variables.
- It can be run for any element type in Global Payroll or Absence Management.
- It can limit the number of levels returned. Some elements might go 10 or 15 elements deep. In those cases it might be simpler to limit the number of levels to 4 or 5 levels, depending on what you are looking for.
- It can switch the “direction” to search. Instead of going down it also can go up. In this case I found it easier to limit the number of levels.
- In most cases the SQL will return all child elements. But in other cases it won’t. In our example above, the elements included under the Accumulators are listed, but it doesn’t go any further down for those items. These are items from the Members page of the Accumulators component. If the elements were included via the Accumulator's Definition or Level pages, then those would get returned AND they would continue down into deeper and deeper levels.
