Global Payroll/Absence Management Element Hierarchy

Quickly after I began supporting Absence Management I realized that elements have a hierarchical relationship. For instance, you can have an Absence Entitlement element that uses a Formula element to calculate entitlement when the absence occurs. And that formula may be made up of other elements. Let’s look at an Absence Entitlement delivered in the PeopleSoft HR9.1 Demo Database whose element name is SICK 100 ENT as an example. Here are 3 screen shots of pages showing the elements (circled in red) that it uses:

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 - 188

All 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:

1. 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.
2. It can be run for any element type in Global Payroll or Absence Management.
3. 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.
4. 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.
5. 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.

Implementing a new Mobile Approval Type (Part 3)

In Implementing a new Mobile Approval Type (Part 1) I talked about the initial configuration you need to make in Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page and how that configuration gets reflected when viewing new Mobile Approvals. In Implementing a new Mobile Approval Type (Part 2)  I talk about the Application Package PeopleCode Classes that are required for Mobile Approvals, plus one other Record Definition. In this post I demonstrate how the custom classes/methods were called during page load and mouse clicks events. I reviewed Trace Files to determine which methods are called and in what order they were called. The screenshots and classes refer to our custom TOIL App Package Classes. But I think the delivered App Package Classes, for say the Absence Mobile Approvals, defined in the GP_ABS_EVT_HANDLER App Package, could also be referred to. But if you need any clarification please let me know. I may be able to provide our custom code.

Pending Approvals Page Loaded

Custom Methods that are Called

1. DataHandler.OXF_TOIL
2. DataHandler.GetApprovalItemRowset
3. DataHandler.PopulateItemForList

Screen Shot

TOIL Hours on Left Clicked

Custom Methods that are Called

1. DataHandler.OXF_TOIL
2. DataHandler.GetApprovalItemRowset
3. DataHandler.PopulateItemForList

Screen Shot

TOIL Request Clicked / TOIL Details Page Loaded

Custom Methods that are Called

1. DataHandler.OXF_TOIL
2. DataHandler.RetrieveApprovalItemDetail
3. DataHandler.PopulateItemSummaryForDetail
4. DetailDocument.ConstructDocument
5. SubPage.OXF_TOIL
6. SubPage.AddDynamicButton
7. SubPage.RenderPage
8. TOIL_Workflow.Thread_Descr
9. TOIL_Workflow.getThreadDescr
10. TOIL_Workflow.getUserName
11. DataHandler.OXF_TOIL
12. Ajax.IsRequireToRunPreApprovalProcess

Screen Shot

TOIL Line Clicked

Custom Methods that are Called

1. DataHandler.OXF_TOIL
2. DetailDocument.OXF_TOIL
3. DetailDocument.ConstructDocument
4. SubPage.OXF_TOIL
5. SubPage.AddDynamicButton
6. SubPage.RenderPage

Screen Shot

With RenderPage Processing

Without RenderPage Processing

TOIL Deny Clicked

Custom Methods that are Called

1. TOIL_Workflow.Event_Handler
2. TOIL_Workflow.OnHeaderDeny
3. Comments.AddApplicationComments

Explanation

AddApplicationComments is what inserts the comments entered on the page.

TOIL Approved Clicked

Custom Methods that are Called

1. DataHandler.OXF_TOIL
2. Ajax.PreApprovalProcess
3. TOIL_Workflow.Event_Handler
4. TOIL_Claim.TOIL_Claim
5. TOIL_Claim.Update_TOIL_Entitlement
6. Results_Build.Results_Build
7. Results_Build.Schedule_Update_Balances
8. TOIL_Workflow.OnHeaderApprove
9. Comments.AddApplicationComments
10. DataHandler.OXF_TOIL
11. DataHandler.GetApprovalItemRowset
12. DataHandler.OXF_TOIL
13. DataHandler.GetApprovalItemRowset

Explanation

AddApplicationComments is what inserts the comments entered on the page.

The final 2 calls to DataHandler.OXF_TOIL and DataHandler.GetApprovalItemRowset are likely from the Pending Approvals page being displayed again, which is normally where you get redirected to after approving something.

Implementing a new Mobile Approval Type (Part 2)

In Implementing a new Mobile Approval Type (Part 1) I talked about the initial configuration you need to make in Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page and how that configuration gets reflected when viewing new Mobile Approvals. In this post I talk about the Application Package PeopleCode Classes that are required for Mobile Approvals, plus one other Record Definition .

Delivered Documentation

There seems to be very little documentation delivered by Oracle on how to implement a custom AWE Process in the new MAP-built Approval pages. The one place something is mentioned is this PeopleBook: Using the PeopleSoft Fluid User Interface to Work with Approvals where it mentions that the Root Package ID specified on the Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page (see Implementing a new Mobile Approval Type (Part 1)) and that this is the "application package that holds the DataHandler, DetailDocuement and SubPage classes for the transaction."

Custom Classes

Besides the DataHandler, DetailDocuement and SubPage listed in the documentation, there are two other classes required: Ajax and Comments.

These five class types have to be defined using this naming structure:

<Root Package>:<Class Type>:<AWE Process ID>

So for our custom TOIL Approval Workflow, the AWE Process ID is OXF_TOIL and the Root Package we used was OXF_ABSENCE_MGMT. So the five Classes were defined like this:

• OXF_ABSENCE_MGMT:Ajax:OXF_TOIL
• OXF_ABSENCE_MGMT:Comments:OXF_TOIL
• OXF_ABSENCE_MGMT:DataHandler:OXF_TOIL
• OXF_ABSENCE_MGMT:DetailDocuement:OXF_TOIL
• OXF_ABSENCE_MGMT:SubPage:OXF_TOIL

Ajax Class

Extends

HMAP_APPROVAL:Ajax:AjaxInterface

Defines

AdhocAJAXFunction

Use

Could be used to implement Check Eligibility functionality. We have not implemented that functionality.

Defines

PreApprovalProcess

Use

Can be used to check Forecasting or other prerequisites required before allowing a User to Approve a request. In our case, we have no such prerequisite checks so we are setting everything to "Success".

Defines

IsRequireToRunPreApprovalProcess

Use

Always returns true. Maybe if it was set to false then the PreApprovalProcess method wouldn't get run when the request was approved.

Defines

CheckEligibility

Use

Defined, and coded, but not in use. Would be called from AdhocAJAXFunction, if there was a Check Eligibility button added to the Approval page.

Comments Class

Extends

HMAP_APPROVAL:ApprovalComments

Defines

method AddApplicationComments

Use

Once the Approval Item has been Approved, if there has been a comment added by the Approver, then the appropriate comment field on the appropriate table gets updated

DataHandler Class

Extends

HMAF_AWE:MOBILE:Handler:ApprovalFrameworkBase

Defines

GetApprovalItemRowset

Use

Perform a select against TOIL AWE View to return (as a rowset) all of the Outstanding TOIL Requests awaiting approval. Used for populating the number of TOIL requests on left hand side of Pending Approvals. And for populating the list of Pending Approvals on right hand side whenever TOIL or All is selected on left hand side.

Defines

PopulateItemForList

Use

Sets the action flags based on configuration on Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page. Makes reference to header record for TOIL (OXF_TOIL_EE_PRD) but it isn't actually used. Formats the data of each pending TOIL Approval you see on right hand side of pending approvals page when you have selected TOIL Hours on left hand side.

Defines

RetrieveApprovalItemDetail

Use

Perform a select against TOIL AWE View to return (as a record) all the details of a selected outstanding TOIL Request that is awaiting approval.

Defines

PopulateItemSummaryForDetail

Use

Similar to PopulateItemForList. Sets the action flags based on configuration on Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page. Makes reference to header record for TOIL (OXF_TOIL_EE_PRD) but it isn't actually used. Sets the following which is then passed in to a delivered method:

• ItemDate
• ItemKey
• ItemType
• TotalItemLines
• EmplID
• EmplRcd
• RequestorComments

DetailDocument Class

Extends

HMAP_APPROVAL:Document:Data:SubDetailInfoDataDocument

Defines

OXF_TOIL

Use

Constructor method.

Defines

ConstructDocument

Use

This is the method that builds the main detail page you see after you have selected one of the outstanding TOIL approvals from the Pending Approvals page. It is used for both the main TOIL page, along with the line subpage you see after selecting one of the TOIL lines associated with the request. For both, it starts by doing a select against the TOIL AWE View. For the main page, it then 1.) formats the information you see on the page: "TOIL Period", "Claim No", "Status" and "Total Hours", and 2.) does a select against OXF_TOIL_EE_EVT and for each row returned, it sets the "ItemKey" (made up of on OXF_ABS_TOIL_DT and OXF_TOIL_CODE Values), "Title" (the OXF_ABS_TOIL_DT Value), "Title1" (OXF_ABS_TOIL_HRS.Value concatenated with " Hours"), and "Description1" (TOIL Code description concatenated with OXF_ABS_TOIL_RSN Value). If it's the subpage instead, it does a select against OXF_TOIL_EE_EVT and formats the information you see on the page: "Date", "Hours Earned", "TOIL Code" and "TOIL Reason".

SubPage Class

Extends

HMAP_APPROVAL:Page:SubPageBase

Defines

OXF_TOIL

Use

Constructor method. Sets some labels. Also sets some other properties of the class (including a pointer to a HTML Definition - HMAP_SPB_ABS_CSS_JS - but it doesn't seemed to be used.)

Defines

AddDynamicButton

Use

Used if a Dynamic Button is needed on the Subpage. This is not used for TOIL Approvals (so only assuming this is the purpose of this method.)

Defines

RenderPage

Use

Used to format the display of main page and the subpage. If viewing subpage, it hides a couple of sections and turns the main data you see on subpage from 2 columns to one column. If viewing main page, it sets some labels and hides the Additional Info section.

TOIL AWE View

OXF_TOIL_AWE_VW is a new record definition based on record HGA_APPR_AWE_VW. The HGA_APPR_AWE_VW record is used in the code for the Absence Fluid Approvals.

When developing an AWE Process you create a Cross Reference View. For our OXF_TOIL AWE Process this view was called OXF_TOIL_XREF. This new view is very similar and joins in these tables: OXF_TOIL_EE_PRD (the Header record for the Approval), OXF_TOILPRD_TBL (a lookup table with descriptions), PERSON_NAME (the name of the person who has submitted the request), EOAW_STEPINST and EOAW_USERINST.

The record is referenced throughout the Application classes defined above, usually to gather TOIL data together to present to an Approver.