tag:blogger.com,1999:blog-60964473899421611132024-03-13T04:56:39.169+00:00The PeopleTools FilesApp Designer, PeopleCode, Portal, PIA, App Servers, Integration Broker...Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.comBlogger19125tag:blogger.com,1999:blog-6096447389942161113.post-77608301154640808412017-04-07T20:12:00.002+01:002017-04-07T20:16:39.709+01:00Global Payroll/Absence Management Element Hierarchy<link href="//cdnjs.cloudflare.com/ajax/libs/prism/1.6.0/themes/prism.min.css" rel="stylesheet"></link><script src="//cdnjs.cloudflare.com/ajax/libs/prism/1.6.0/prism.min.js"></script><script src="//cdnjs.cloudflare.com/ajax/libs/prism/1.6.0/components/prism-sql.js"></script><style type="text/css">
pre.language-sql {
height: 500px;
}
</style>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:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-HWHG0CJfFhk/WOfQknIQqII/AAAAAAAAAXI/yxiWT_yVO-kDym-17qtCjyKr1Tf9mgHkwCLcB/s1600/Absence01.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="142" src="https://1.bp.blogspot.com/-HWHG0CJfFhk/WOfQknIQqII/AAAAAAAAAXI/yxiWT_yVO-kDym-17qtCjyKr1Tf9mgHkwCLcB/s320/Absence01.jpg" width="320" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://4.bp.blogspot.com/-oqQHuarZhOA/WOfQkRYNIdI/AAAAAAAAAW8/Q8-68ZM957caqMf6MnVpLyiF7bckeOOhgCLcB/s1600/Absence02.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="209" src="https://4.bp.blogspot.com/-oqQHuarZhOA/WOfQkRYNIdI/AAAAAAAAAW8/Q8-68ZM957caqMf6MnVpLyiF7bckeOOhgCLcB/s320/Absence02.jpg" width="320" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://3.bp.blogspot.com/-v3qPnWvPdtU/WOfQkTjcu5I/AAAAAAAAAW4/1bKPNe5TGTQQs5TvhFakGalMeXYyI8G0QCLcB/s1600/Absence03.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="183" src="https://3.bp.blogspot.com/-v3qPnWvPdtU/WOfQkTjcu5I/AAAAAAAAAW4/1bKPNe5TGTQQs5TvhFakGalMeXYyI8G0QCLcB/s320/Absence03.jpg" width="320" /></a></div><br />
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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://3.bp.blogspot.com/-BYNkb0I5GE8/WOfQkpHAy6I/AAAAAAAAAXA/ncY-99gKdNUJMUF9uriVaVLUGNINW16sACEw/s1600/Formula01.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="191" src="https://3.bp.blogspot.com/-BYNkb0I5GE8/WOfQkpHAy6I/AAAAAAAAAXA/ncY-99gKdNUJMUF9uriVaVLUGNINW16sACEw/s320/Formula01.jpg" width="320" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://4.bp.blogspot.com/-OrNEVHHcR_g/WOfQkuZzZBI/AAAAAAAAAXE/PWmi7jXhbcg9zNjg0lP-6b3Hc0tc8BffACEw/s1600/Formula02.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="187" src="https://4.bp.blogspot.com/-OrNEVHHcR_g/WOfQkuZzZBI/AAAAAAAAAXE/PWmi7jXhbcg9zNjg0lP-6b3Hc0tc8BffACEw/s320/Formula02.jpg" width="320" /></a></div><br />
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.<br />
<br />
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:<br />
<br />
<pre><code class="language-sql">/*
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
;</code></pre><br />
If I run this SQL in my demo database with these parameters<br />
<br />
<ul><li>dir=down</li>
<li>elementName=SICK 100 ENT</li>
<li>country=ALL</li>
<li>elementType=AE</li>
<li>level=999</li>
</ul><br />
I get this result<br />
<br />
<pre><span style="font-family: Courier New, Courier, monospace;">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</span></pre><br />
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.<br />
<br />
Some other comments on the SQL:<br />
<ol><li>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.</li>
<li>It can be run for any element type in Global Payroll or Absence Management.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
</ol>Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com2tag:blogger.com,1999:blog-6096447389942161113.post-92032704811858029922016-03-08T21:07:00.001+00:002016-03-08T21:07:06.174+00:00Implementing a new Mobile Approval Type (Part 3)In <a href="http://getlevel0.blogspot.com/2016/02/implementing-new-mobile-approval-type.html">Implementing a new Mobile Approval Type (Part 1)</a> 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 <a href="http://getlevel0.blogspot.co.uk/2016/03/implementing-new-mobile-approval-type.html">Implementing a new Mobile Approval Type (Part 2)</a> 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.<br />
<br />
<h2>
Pending Approvals Page Loaded</h2>
<h3>
Custom Methods that are Called</h3>
<div>
<ol>
<li>DataHandler.OXF_TOIL</li>
<li>DataHandler.GetApprovalItemRowset</li>
<li>DataHandler.PopulateItemForList</li>
</ol>
</div>
<h3>
Screen Shot</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-FtpZ28AtSq8/Vt85Px0DPmI/AAAAAAAAAHg/A1XlORZNGi4/s1600/Pending_TOIL_Approvals_Loaded.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="128" src="https://3.bp.blogspot.com/-FtpZ28AtSq8/Vt85Px0DPmI/AAAAAAAAAHg/A1XlORZNGi4/s640/Pending_TOIL_Approvals_Loaded.jpg" width="640" /></a></div>
<div>
<br /></div>
<h2>
TOIL Hours on Left Clicked</h2>
<h3>
Custom Methods that are Called</h3>
<div>
<ol>
<li>DataHandler.OXF_TOIL</li>
<li>DataHandler.GetApprovalItemRowset</li>
<li>DataHandler.PopulateItemForList</li>
</ol>
</div>
<h3>
Screen Shot</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-AOi9uIq828A/Vt85QAOHqcI/AAAAAAAAAHk/GrmBVv6DD30/s1600/Pending_TOIL_Approvals_-_TOIL_Hours_Selected.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="128" src="https://1.bp.blogspot.com/-AOi9uIq828A/Vt85QAOHqcI/AAAAAAAAAHk/GrmBVv6DD30/s640/Pending_TOIL_Approvals_-_TOIL_Hours_Selected.jpg" width="640" /></a></div>
<div>
<br /></div>
<h2>
TOIL Request Clicked / TOIL Details Page Loaded</h2>
<h3>
Custom Methods that are Called</h3>
<div>
<ol>
<li>DataHandler.OXF_TOIL</li>
<li>DataHandler.RetrieveApprovalItemDetail</li>
<li>DataHandler.PopulateItemSummaryForDetail</li>
<li>DetailDocument.ConstructDocument</li>
<li>SubPage.OXF_TOIL</li>
<li>SubPage.AddDynamicButton</li>
<li>SubPage.RenderPage</li>
<li>TOIL_Workflow.Thread_Descr</li>
<li>TOIL_Workflow.getThreadDescr</li>
<li>TOIL_Workflow.getUserName</li>
<li>DataHandler.OXF_TOIL</li>
<li>Ajax.IsRequireToRunPreApprovalProcess</li>
</ol>
</div>
<h3>
Screen Shot</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-2Zs57m02vbE/Vt85QV0VD9I/AAAAAAAAAHo/-a3FMIG6OBg/s1600/TOIL_Approval_Main_Page_with_Comments.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="https://4.bp.blogspot.com/-2Zs57m02vbE/Vt85QV0VD9I/AAAAAAAAAHo/-a3FMIG6OBg/s640/TOIL_Approval_Main_Page_with_Comments.jpg" width="640" /></a></div>
<div>
<br /></div>
<h2>
TOIL Line Clicked</h2>
<h3>
Custom Methods that are Called</h3>
<div>
<ol>
<li>DataHandler.OXF_TOIL</li>
<li>DetailDocument.OXF_TOIL</li>
<li>DetailDocument.ConstructDocument</li>
<li>SubPage.OXF_TOIL</li>
<li>SubPage.AddDynamicButton</li>
<li>SubPage.RenderPage</li>
</ol>
</div>
<h3>
Screen Shot</h3>
<h4>
With RenderPage Processing</h4>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-szQ026YdT3g/Vt85RXE_r8I/AAAAAAAAAH0/KD96uS4qIzM/s1600/TOIL_Sub_Page_with_RenderPage.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="116" src="https://2.bp.blogspot.com/-szQ026YdT3g/Vt85RXE_r8I/AAAAAAAAAH0/KD96uS4qIzM/s640/TOIL_Sub_Page_with_RenderPage.jpg" width="640" /></a></div>
<h4 style="clear: both; text-align: left;">
Without RenderPage Processing</h4>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-Wcb_ltklZxU/Vt85RrqpbZI/AAAAAAAAAH0/qRU5ati1m4s/s1600/TOIL_Sub_Page_without_RenderPage.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="116" src="https://2.bp.blogspot.com/-Wcb_ltklZxU/Vt85RrqpbZI/AAAAAAAAAH0/qRU5ati1m4s/s640/TOIL_Sub_Page_without_RenderPage.jpg" width="640" /></a></div>
<div>
<br /></div>
<h2>
TOIL Deny Clicked</h2>
<h3>
Custom Methods that are Called</h3>
<div>
<ol>
<li>TOIL_Workflow.Event_Handler</li>
<li>TOIL_Workflow.OnHeaderDeny</li>
<li>Comments.AddApplicationComments</li>
</ol>
</div>
<h3>
Explanation</h3>
<div>
AddApplicationComments is what inserts the comments entered on the page.<br />
<br /></div>
<h2>
TOIL Approved Clicked</h2>
<h3>
Custom Methods that are Called</h3>
<div>
<ol>
<li>DataHandler.OXF_TOIL</li>
<li>Ajax.PreApprovalProcess</li>
<li>TOIL_Workflow.Event_Handler</li>
<li>TOIL_Claim.TOIL_Claim</li>
<li>TOIL_Claim.Update_TOIL_Entitlement</li>
<li>Results_Build.Results_Build</li>
<li>Results_Build.Schedule_Update_Balances</li>
<li>TOIL_Workflow.OnHeaderApprove</li>
<li>Comments.AddApplicationComments</li>
<li>DataHandler.OXF_TOIL</li>
<li>DataHandler.GetApprovalItemRowset</li>
<li>DataHandler.OXF_TOIL</li>
<li>DataHandler.GetApprovalItemRowset</li>
</ol>
</div>
<h3>
Explanation</h3>
<div>
<div>
AddApplicationComments is what inserts the comments entered on the page.</div>
<div>
<br /></div>
<div>
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.</div>
</div>
Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-84684214728334165972016-03-07T19:05:00.000+00:002016-03-07T19:06:12.443+00:00Implementing a new Mobile Approval Type (Part 2)<div>In <a href="http://getlevel0.blogspot.com/2016/02/implementing-new-mobile-approval-type.html">Implementing a new Mobile Approval Type (Part 1)</a> 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 .<br />
</div><br />
<h2>Delivered Documentation</h2><div>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: <a href="http://docs.oracle.com/cd/E62891_01/hcm92pbr6/eng/hcm/ecch/task_UsingThePeopleSoftFluidUserInterfaceToWorkWithApprovals.html#u7e153b6c-4be0-4d0c-99fd-83a5ba6f9326__u70d866a1-1a24-4373-8c80-64e22115916a">Using the PeopleSoft Fluid User Interface to Work with Approvals</a> where it mentions that the Root Package ID specified on the Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page (see <a href="http://getlevel0.blogspot.com/2016/02/implementing-new-mobile-approval-type.html">Implementing a new Mobile Approval Type (Part 1)</a>) and that this is the "application package that holds the DataHandler, DetailDocuement and SubPage classes for the transaction."<br />
</div><br />
<h2>Custom Classes</h2>Besides the DataHandler, DetailDocuement and SubPage listed in the documentation, there are two other classes required: Ajax and Comments.<br />
<br />
<div>These five class types <b>have </b>to be defined using this naming structure:</div><br />
<div><Root Package>:<Class Type>:<AWE Process ID></div><br />
<div>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:</div><ul><li>OXF_ABSENCE_MGMT:Ajax:OXF_TOIL</li>
<li>OXF_ABSENCE_MGMT:Comments:OXF_TOIL</li>
<li>OXF_ABSENCE_MGMT:DataHandler:OXF_TOIL</li>
<li>OXF_ABSENCE_MGMT:DetailDocuement:OXF_TOIL</li>
<li>OXF_ABSENCE_MGMT:SubPage:OXF_TOIL</li>
</ul><br />
<h2>Ajax Class</h2><div><dl><dt>Extends</dt>
<dd>HMAP_APPROVAL:Ajax:AjaxInterface</dd>
<dt>Defines</dt>
<dd>AdhocAJAXFunction
<dl><dt>Use</dt>
<dd>Could be used to implement Check Eligibility functionality. We have not implemented that functionality.</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>PreApprovalProcess
<dl><dt>Use</dt>
<dd>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".</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>IsRequireToRunPreApprovalProcess
<dl><dt>Use</dt>
<dd>Always returns true. Maybe if it was set to false then the PreApprovalProcess method wouldn't get run when the request was approved.</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>CheckEligibility
<dl><dt>Use</dt>
<dd>Defined, and coded, but not in use. Would be called from AdhocAJAXFunction, if there was a Check Eligibility button added to the Approval page.</dd></dl></dd></dl></div><br />
<h2>Comments Class</h2><div><dl><dt>Extends</dt>
<dd>HMAP_APPROVAL:ApprovalComments</dd>
<dt>Defines</dt>
<dd>method AddApplicationComments
<dl><dt>Use</dt>
<dd>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</dd></dl></dd></dl></div><br />
<h2>DataHandler Class</h2><div><dl><dt>Extends</dt>
<dd>HMAF_AWE:MOBILE:Handler:ApprovalFrameworkBase</dd>
<dt>Defines</dt>
<dd>GetApprovalItemRowset
<dl><dt>Use</dt>
<dd>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.</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>PopulateItemForList
<dl><dt>Use</dt>
<dd>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.</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>RetrieveApprovalItemDetail
<dl><dt>Use</dt>
<dd>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. </dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>PopulateItemSummaryForDetail
<dl><dt>Use</dt>
<dd>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:
<ul><li>ItemDate</li>
<li>ItemKey</li>
<li>ItemType</li>
<li>TotalItemLines</li>
<li>EmplID</li>
<li>EmplRcd</li>
<li>RequestorComments</li>
</ul></dd></dl></dd></dl></div><br />
<h2>DetailDocument Class</h2><div><dl><dt>Extends</dt>
<dd>HMAP_APPROVAL:Document:Data:SubDetailInfoDataDocument</dd>
<dt>Defines</dt>
<dd>OXF_TOIL
<dl><dt>Use</dt>
<dd>Constructor method.</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>ConstructDocument
<dl><dt>Use</dt>
<dd>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".</dd></dl></dd></dl></div><br />
<h2>SubPage Class</h2><div><dl><dt>Extends</dt>
<dd>HMAP_APPROVAL:Page:SubPageBase</dd>
<dt>Defines</dt>
<dd>OXF_TOIL
<dl><dt>Use</dt>
<dd>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.)</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>AddDynamicButton
<dl><dt>Use</dt>
<dd>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.)</dd></dl></dd></dl><dl><dt>Defines</dt>
<dd>RenderPage
<dl><dt>Use</dt>
<dd>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.</dd></dl></dd></dl></div><br />
<h2>TOIL AWE View</h2><div>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.</div><br />
<div>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.</div><br />
<div>The record is referenced throughout the Application classes defined above, usually to gather TOIL data together to present to an Approver.</div>Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-60407147884841038272016-02-24T14:56:00.000+00:002016-02-24T14:56:04.783+00:00New Release of Chrome Extension PS Utilities - with a fabulous new featureToday the <a href="https://chrome.google.com/webstore/detail/ps-utilities/jajoopnifcliapcngocgiidifkmboemc?hl=en-US">PS Utilities Chrome Extension</a> has been updated to version 3.0.0. This latest release fixes a few minor bugs, updates the extension to better handle PeopleTools 8.54 (especially Fluid pages) and has one fabulous new feature: <b>One-click to turn on PeopleCode and SQL Tracing!</b><br />
<br />
How clunky is it to turn on and off tracing from within PIA? How many mouse clicks does it take to get to the Trace PeopleCode and Trace SQL components, choose your options and save each component? This new feature in PS Utilities hides all those mouse clicks. All you need is one click to turn on tracing and another click to turn it off.<br />
<br />
There are 2 simple steps you need to perform to to get it working for you. You first need to enable the Tracing feature on the Features tab of the PS Utilities Options page. And you then need to perform a one time setup of PeopleCode and SQL Trace options under the new Tracing tab on the Options page. Here's a screen shot with my favourite Trace options selected. Notice that these options match the options you see if you go to PeopleTools > Utilities > Debug > Trace PeopleCode and PeopleTools > Utilities > Debug > Trace SQL.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-xXEC3xuKPj0/Vsy1xu7stsI/AAAAAAAAAHA/u4hj5kXiRCQ/s1600/PS%2BUtilities%2BTrace%2BSetup.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://4.bp.blogspot.com/-xXEC3xuKPj0/Vsy1xu7stsI/AAAAAAAAAHA/u4hj5kXiRCQ/s320/PS%2BUtilities%2BTrace%2BSetup.jpg" width="202" /></a></div>
<br />
<br />
Then on the PS Utilities Bar you need to press the<a href="https://3.bp.blogspot.com/-8Wz_8FRMgko/Vsy3JIHfJQI/AAAAAAAAAHM/vQlKItCPIAY/s1600/trace_off.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://3.bp.blogspot.com/-8Wz_8FRMgko/Vsy3JIHfJQI/AAAAAAAAAHM/vQlKItCPIAY/s1600/trace_off.png" /></a> icon to turn on tracing. And press the <a href="https://3.bp.blogspot.com/-qw_Hqx5HN4c/Vsy3I5IeQSI/AAAAAAAAAHQ/XuRGg87OjII/s1600/trace_on.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://3.bp.blogspot.com/-qw_Hqx5HN4c/Vsy3I5IeQSI/AAAAAAAAAHQ/XuRGg87OjII/s1600/trace_on.png" /></a> icon to turn off tracing. It's as simple as that.<br />
<br />
You will see the<a href="https://3.bp.blogspot.com/-8Wz_8FRMgko/Vsy3JIHfJQI/AAAAAAAAAHM/vQlKItCPIAY/s1600/trace_off.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://3.bp.blogspot.com/-8Wz_8FRMgko/Vsy3JIHfJQI/AAAAAAAAAHM/vQlKItCPIAY/s1600/trace_off.png" /></a> icon rotating when you login to PeopleSoft or if you navigate to some pages. It does this while it determines if your user ID has permissions to access the PeopleCode and SQL Trace components. If your user ID does not have permission to either of these components then you will see the <a href="https://4.bp.blogspot.com/-4K0ARE_JnBc/Vsy3I7jh5QI/AAAAAAAAAHI/kS-gj4OMByw/s1600/trace_no_access.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://4.bp.blogspot.com/-4K0ARE_JnBc/Vsy3I7jh5QI/AAAAAAAAAHI/kS-gj4OMByw/s1600/trace_no_access.png" /></a> icon and you will be unable to turn on tracing.<br />
<br />
Keep in mind that PS Utilities is unable to determine if a Trace is already being performed via these delivered options:<br />
<ol>
<li>Sign on page trace options</li>
<li>Application server trace options</li>
<li>PeopleTools > Utilities > Debug components: Trace PeopleCode or Trace SQL</li>
<li>Built-in PeopleCode Functions SetTracePC or SetTraceSQL</li>
</ol>
<div>
If you want to turn on tracing for just a short period of time to trace some specific functionality (such as the click of a button or the save of a page), this new Trace feature of PS Utilities will make that so much easier. Try it out.<br />
<br />
And if you have any feedback it would be greatly appreciated. You can leave a comment here on this blog post or you can <a href="https://chrome.google.com/webstore/detail/ps-utilities/jajoopnifcliapcngocgiidifkmboemc/support">submit a support request</a> directly on the Chrome Store page for the extension.</div>
Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com1tag:blogger.com,1999:blog-6096447389942161113.post-22129290330282970882016-02-23T19:23:00.000+00:002016-02-23T19:23:32.839+00:00Implementing a new Mobile Approval Type (Part 1)<h2>
<span style="font-weight: normal;">Introduction</span></h2>
Over the last year I have worked on an upgrade from HR9.0 / PeopleTools 8.52 to HR9.2 / PeopleTools 8.54. One of the most interesting things I worked on was implementing a new Mobile Approval type. My client had implemented a custom Approval Workflow Engine (AWE) process back in HR9.0 to handle the approval of Time Off In Lieu (TOIL) requests. And they wanted to make use of the new Mobile Application Platform (MAP) built Fluid Approval pages delivered in HR9.2. This required implementing a new mobile approval type for this custom AWE Process.<br />
<br />
This blog post will be the first covering this implementation.<br />
<br />
<h2>
<span style="font-weight: normal;">General comments about implementing TOIL Approval</span></h2>
Most of the configuration and code was based on the Absence Approval Setup. The OXFTOIL configuration added to Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page was based on the ABSENCE configuration on the same page. The TOIL Approvals App Package PeopleCode (to be reviewed in a later blog post) in OXF_ABSENCE_MGMT App Package was based on the PeopleCode in the GP_ABS_EVT_HANDLER App Package.<br />
<br />
<h2>
<span style="font-weight: normal;">Mobile Approval Options</span></h2>
TOIL Approvals using the new (as of HR9.2/PT8.54) MAP built Approval Fluid pages is configured via Enterprise Components > Approvals > Approvals > Mobile Approval Options > Transactions page . Here are screenshots of the configuration entered on this page/component. And below I'll show how it relates to what users see and how the TOIL Approvals work.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-cOAIznZeSE0/Vsx-Z4jt6II/AAAAAAAAAGc/gk6aY2LF4Ho/s1600/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="https://4.bp.blogspot.com/-cOAIznZeSE0/Vsx-Z4jt6II/AAAAAAAAAGc/gk6aY2LF4Ho/s640/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B1.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-5sWdau7E5b4/Vsx-cOd02_I/AAAAAAAAAGc/i92hsbYH8SM/s1600/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="74" src="https://3.bp.blogspot.com/-5sWdau7E5b4/Vsx-cOd02_I/AAAAAAAAAGc/i92hsbYH8SM/s640/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B2.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-_HLWdWM01E0/Vsx-eUfxXrI/AAAAAAAAAGc/igKoDhQPoI8/s1600/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="78" src="https://4.bp.blogspot.com/-_HLWdWM01E0/Vsx-eUfxXrI/AAAAAAAAAGc/igKoDhQPoI8/s640/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B3.jpg" width="640" /></a></div>
<a href="https://3.bp.blogspot.com/-Z7lFHd5or0s/Vsx-gSWojAI/AAAAAAAAAGc/XyAB6eWwFHc/s1600/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="72" src="https://3.bp.blogspot.com/-Z7lFHd5or0s/Vsx-gSWojAI/AAAAAAAAAGc/XyAB6eWwFHc/s640/OXF_TOIL%2BMobile%2BApproval%2BOptions%2B4.jpg" width="640" /></a><br />
<ol>
<li>*Order<br />
<dl><dd>You would think this value determines the order of the Transaction List you see on the Pending Approvals page. But have not been able to verify this is the case. (Maybe when changes to this value were made that an App Server or Web Server needed rebooting?)</dd></dl>
</li>
<br />
<br />
<li>*Transaction ID<br />
<dl><dd>Doesn't seem to be used other than to be the unique key in this list of Mobile Approval Transactions</dd></dl>
</li>
<br />
<br />
<li>*Transaction Name<br />
<dl><dd>The Transaction Name shows up in three different places <br />
<ol>
<li>Pending Approvals Page</li>
<li>TOIL Approval Main Page</li>
<li>TOIL Approval Line Detail Page</li>
</ol>
</dd></dl>
</li>
<br />
<br />
<li>*Process ID<br />
<dl><dd>Ties this Mobile Approval configuration to an AWE Process defined in Enterprise Components > Approvals > Approvals > Transaction Registry</dd></dl>
</li>
<br />
<br />
<li>Include in JQuery Mobile<br />
<dl><dd>MAP can make use of JQuery Mobile. Have not seen anywhere this has been used so unsure how this setting will affect the TOIL Approval</dd></dl>
</li>
<br />
<br />
<li>Allow Mass Approvals<br />
<dl><dd>This has been turned off for TOIL (but is on for Absence Requests.) If it was on, on the Pending Approval page when TOIL Hours is selected on the left hand side, then on the right hand side the Approve and Deny buttons would be displayed above the list and check boxes would be displayed next to each TOIL Hour request.</dd></dl>
</li>
<br />
<br />
<li>Transaction Group<br />
<dl><dd>Unused. If Transaction Groups were defined in Enterprise Components > Approvals > Approvals > Mobile Approval Options > General Settings page , then you could possibly Group various Approval Transactions into one group.</dd></dl>
</li>
<br />
<br />
<li>Transaction Handler Class<br />
<dl><dd>Unused</dd></dl>
</li>
<br />
<br />
<li>Root Package ID<br />
<dl><dd>This is the PeopleCode App Package where all the work is done. (To be reviewed in a later blog post)</dd></dl>
</li>
<br />
<br />
<li>Approve<br />
<dl><dd>On. Means the Approve button is displayed on the TOIL Approval Main Page</dd></dl>
</li>
<br />
<br />
<li>Deny<br />
<dl><dd>On. Means the Deny button is displayed on the TOIL Approval Main Page</dd></dl>
</li>
<br />
<br />
<li>Pushback<br />
<dl><dd>Off. Means the Pushbck button is NOT displayed on the TOIL Approval Main Page</dd></dl>
</li>
<br />
<br />
<li>Hold<br />
<dl><dd>Off. Means the Hold button is NOT displayed on the TOIL Approval Main Page</dd></dl>
</li>
<br />
<br />
<li>Request Information<br />
<dl><dd>Off. Means the Request Information button is NOT displayed on the TOIL Approval Main Page</dd></dl>
</li>
<br />
<br />
<li>*Small Image<br />
<dl><dd>Image used is MAP_TIME. Sets the image a user sees in the list of Approval Transactions on left hand side of Pending Approvals page.</dd></dl>
</li>
<br />
<br />
<li>Medium Image<br />
<dl><dd>Not set. Unsure of it's use.</dd></dl>
</li>
<br />
<br />
<li>Large Image<br />
<dl><dd>Not set. Unsure of it's use.</dd></dl>
</li>
<br />
<br />
</ol>
<h2>
<span style="font-weight: normal;">How Configuration is Reflected in MAP Pages</span></h2>
<h3>
<span style="font-weight: normal;">Pending TOIL Approvals</span></h3>
<ul>
<li>Transaction Name (TOIL Hours) is displayed in three places on this page. </li>
<li>Because Allow Mass Approvals is off, the Check Box and Approve and Deny buttons are not displayed on this page. </li>
<li>MAP_TIME image is displayed to the left of TOIL Hours on left hand side list of outstanding approval types.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-nR7IbcoUqB0/VsyD_0ujBvI/AAAAAAAAAG0/78M4Bbd5m3s/s1600/pending%2Btoil%2Bapprovals%2Bedited.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="128" src="https://3.bp.blogspot.com/-nR7IbcoUqB0/VsyD_0ujBvI/AAAAAAAAAG0/78M4Bbd5m3s/s640/pending%2Btoil%2Bapprovals%2Bedited.jpg" width="640" /></a></div>
<h3>
<span style="font-weight: normal;">TOIL Approval Main Page</span></h3>
<ul>
<li>Transaction Name (TOIL Hours) is displayed in one place on this page. </li>
<li>Approve and Deny buttons are displayed on this page </li>
<li>Pushback, Hold and Request Information buttons are NOT displayed on this page</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-WywhXfE9NzU/VsyD7cU_1cI/AAAAAAAAAG0/sIcCH37sHB4/s1600/toil%2Bapproval%2Bmain%2Bpage%2Bedited.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="https://1.bp.blogspot.com/-WywhXfE9NzU/VsyD7cU_1cI/AAAAAAAAAG0/sIcCH37sHB4/s640/toil%2Bapproval%2Bmain%2Bpage%2Bedited.jpg" width="640" /></a></div>
<h3>
<span style="font-weight: normal;">TOIL Approval Line Detail Page</span></h3>
<ul>
<li>Transaction Name (TOIL Hours) is displayed in one place on this page.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-FsLcAX1qqmg/VsyEBir_MMI/AAAAAAAAAG0/YAff-rvsuuA/s1600/TOIL%2BApproval%2BLine%2BDetail%2BPage.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="86" src="https://3.bp.blogspot.com/-FsLcAX1qqmg/VsyEBir_MMI/AAAAAAAAAG0/YAff-rvsuuA/s640/TOIL%2BApproval%2BLine%2BDetail%2BPage.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<h2 style="clear: both; text-align: left;">
<span style="font-weight: normal;">What else?</span></h2>
<div>
The most important part of implementing Mobile Approvals is the App Package PeopleCode that is required. I hope to cover that in an upcoming post. Stay tuned.</div>
<div>
<br /></div>
Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-72659425528781078822015-08-12T19:06:00.000+01:002015-08-19T18:31:21.451+01:00Save PeopleCode to FileI've worked with a few people who perform a search for a semi-colon character ( ; ) in PeopleCode in the <b>Find In</b> dialog box and use the <b>Save PeopleCode to File</b> option. They do this so all PeopleCode can be saved to a file. And then whenever they want to perform a search they will search the file instead of using the <b>Find In</b> dialog box again. Using a text editor to search the file is usually MUCH quicker than using the <b>Find In</b> dialog box.<br />
<br />
I think people use semi-colon for their search because they think every piece of PeopleCode must have one. This in fact is wrong. Here is a 30 line piece of Record FieldChange PeopleCode that will save and compile using PeopleTools 8.54.13. (Granted - it is non-sensical. But I'm just proving the point that not all PeopleCode must have a semi-colon.)<br />
<br />
This PeopleCode program makes use of a wide variety of built-in functions and language constructs, such as:<br />
<ul>
<li>If, Else, End-If</li>
<li>Evaluate, When, When-Other, End-Evaluate</li>
<li>Upper, Lower, String</li>
<li>Abs, Max, AccruableDays</li>
<li>%Date</li>
<li>For, End-For, Exit</li>
<li>Try, Throw, Catch, End-Try</li>
<li>CreateJavaObject, CreateException</li>
<li>Error, MsgGet</li>
<li>RECORD.FIELD notation</li>
</ul>
The moral of this story is: keep in mind that not all PeopleCode has a to have a semi-colon. And if you use the <b>Find In</b> PeopleCode, semi-colon ( ; ), <b>Save PeopleCode to File</b> option, realise that you may not have all your PeopleCode saved to your file. Even though it can be slow, I use the <b>Find In</b> dialog box. I just time other things around it, like writing a blog post.<br />
<br />
<div class="mw-geshi mw-code mw-content-ltr" dir="ltr">
<div class="peoplecode source-peoplecode">
<pre class="de1"><span class="coMULTI">/* Comment of course */</span>
<span class="kw2">Evaluate</span> PSOPRDEFN.<span class="me1">OPRID</span>
<span class="kw2">When</span> <span class="sy0">=</span> <span class="st0">"VP1"</span>
<span class="kw1">If</span> PSOPRDEFN.<span class="me1">OPRID</span> <span class="sy0">=</span> <span class="kw4">Upper</span><span class="br0">(</span><span class="st0">"cc"</span><span class="br0">)</span> <span class="kw1">And</span>
<span class="st0">"y"</span> <span class="sy0"><></span> <span class="st0">"z"</span> <span class="kw1">Then</span>
<span class="kw1">If</span> <span class="br0">(</span><span class="st0">"a"</span> <span class="sy0">=</span> <span class="st0">"B"</span><span class="br0">)</span> <span class="kw1">Then</span>
<span class="kw1">While</span> <span class="nu0">1</span> <span class="sy0">=</span> <span class="nu0">1</span>
PSOPRDEFN.<span class="me1">OPRID</span> <span class="sy0">=</span> <span class="kw4">Lower</span><span class="br0">(</span><span class="st0">"AA"</span><span class="br0">)</span>
<span class="kw1">End-While</span>
<span class="kw1">End-If</span>
<span class="kw1">End-If</span>
<span class="coMULTI"><* Comments too *></span>
<span class="kw2">When</span> <span class="sy0">=</span> <span class="st0">"PS"</span>
<span class="kw1">If</span> <span class="kw4">Abs</span><span class="br0">(</span><span class="nu19">1.0</span><span class="br0">)</span> <span class="sy0">=</span> <span class="kw4">Max</span><span class="br0">(</span><span class="kw4">AccruableDays</span><span class="br0">(</span><span class="kw3">%Date</span>, <span class="kw3">%Date</span>, <span class="nu0">0</span><span class="br0">)</span><span class="br0">)</span> <span class="kw1">Then</span>
<span class="kw2">Return</span>
<span class="kw1">End-If</span>
When<span class="sy0">-</span>Other
<span class="kw1">For</span> &<span class="re0">intCounter</span> <span class="sy0">=</span> <span class="nu0">1</span> To PSOPRDEFN.<span class="me1">FAILEDLOGINS</span>
<span class="kw4">try</span>
<span class="kw1">If</span> &<span class="re0">intCounter</span> <span class="sy0">=</span> <span class="kw4">Round</span><span class="br0">(</span><span class="nu19">100.12912</span>, <span class="nu0">0</span><span class="br0">)</span> <span class="kw1">Or</span>
<span class="kw2">String</span><span class="br0">(</span>&<span class="re0">intCounter</span><span class="br0">)</span> <span class="sy0">=</span> <span class="kw4">CreateJavaObject</span><span class="br0">(</span><span class="st0">"String"</span>, <span class="st0">"Hello"</span><span class="br0">)</span>.<span class="me1">concat</span><span class="br0">(</span><span class="st0">" World"</span><span class="br0">)</span> <span class="kw1">Then</span>
<span class="kw2">Exit</span>
<span class="kw1">Else</span>
<span class="kw4">throw</span> <span class="kw4">CreateException</span><span class="br0">(</span><span class="nu0">2</span>, <span class="nu0">160</span>, <span class="st0">"'%1' doesn't support property or method '%2'"</span>, <span class="st0">"SomeClass"</span>, <span class="st0">"SomeMethod"</span><span class="br0">)</span>
<span class="kw1">End-If</span>
catch Exception &<span class="re0">ex</span>c
<span class="kw4">Error</span> <span class="kw4">MsgGet</span><span class="br0">(</span><span class="nu0">0</span>, <span class="nu0">0</span>, <span class="st0">"hello world!"</span><span class="br0">)</span>
end<span class="sy0">-</span><span class="kw4">try</span>
<span class="kw1">End-For</span>
<span class="kw2">End-Evaluate</span></pre>
</div>
</div>
Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com7tag:blogger.com,1999:blog-6096447389942161113.post-4392884244287689882014-03-25T20:43:00.000+00:002014-03-25T20:44:34.089+00:00How to Handle a Job Definition with a Deleted Process Definition<div>We are currently in the middle of a Financials 8.9 to 9.2 Upgrade. One of the changes delivered is the old delivered Journal Post COBOL (GLPPPOST) has been replaced with a new App Engine called GL_JP. The old COBOL was included in a custom Job Definition. Following the 9.2 upgrade process we were left with our Job Definition that still included the old Process Definition, even though it was deleted as part of the upgrade. Following the upgrade one of my colleagues tried to delete the old Process Definition from the Job Definition and replace it with the new one. But they received this error when trying to save:</div><blockquote>"The attempt to use CreateProcessRequest was unsuccessful because the system was unable to find the Process Name/Process Type from the Process Scheduler Definition table. Verify the Process Name/Process Type passed as parameters are valid. If this is a single process,check if the process is found in the Process Definition. If this is a Job(with process type of 'PSJob), check if the job is found in the Job Definition."</blockquote><div>This is due to some SaveEdit processing where the Job Definition tries to verify if there is any recursive references in the Job. Because it does this in SaveEdit, it appears it is trying to verify the old version of the Job Definition with the old Process Definition, which of course doesn't exist. (I think this is because Jobs and Process Definitions are cached. I may be wrong on this however.) So how to get around it? I found a reference on the Oracle Support website mentioning the workaround of recreating the Job Definition. But if it is a complicated Job Definition with many Job Items, I think the simplest way to get around the issue is to:</div><ol><li>Temporarily re-create the Process Definition</li>
<li>Delete this Process from the Job (because the Process Definition now exists, the SaveEdit processing will run to success)</li>
<li>Add the new Process Definition to the Job</li>
<li>Delete the Process Definition. To do this you need to:</li>
<ol style="list-style-type: lower-roman;"><li>Create a temporary Application Designer project definition</li>
<li>Add the Process Definition</li>
<li>On the Upgrade Tab for Process Definitions change the Action for this Process Definition from Copy to Delete</li>
<li>Save the Project to file (take note of location where you save it)</li>
<li>Choose Tools, Copy Project, From File... and select the file you just created</li>
<li>It will likely prompt you that the Definition already exists in the Database. If it does select Use Project Definition from: File and click OK</li>
<li>Then click Copy to copy the project from file, deleting the Process Definition you created in the first step above.</li>
</ol></ol><div>You are then left with a Job Definition which includes the new Process Definition. And the old Process Definition again has been deleted.<br />
<br />
</div><div>This gets around the issue following the upgrade. I think the better way is to not have the issue to begin with. I think modifying the upgrade steps so the Job Definition gets updated prior to the old Process Definition being deleted would be better. Which is what I will be telling another colleague who is responsible for managing the upgrade.</div>Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-75009582218731671212014-03-05T21:53:00.001+00:002014-03-05T21:53:56.847+00:00Attachments issue in Google Chrome with PeopleTools version 8.53.10<div>I was just assigned a PeopleTools 8.53 issue where an attachment can't be added in Google Chrome web browser. The attachment modal dialog is displayed and you can choose the file to upload. But when you click the Upload button the dialog disappears but the screen remains blue and nothing is clickable/editable. A search on Oracle Support returns a solution: <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=11708355275664&id=1628372.1&_afrWindowMode=0&_adf.ctrl-state=zuq8xq9w7_4">https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=11708355275664&id=1628372.1&_afrWindowMode=0&_adf.ctrl-state=zuq8xq9w7_4</a> The solution is a temporary workaround as the bug is not due to be fixed until PeopleTools 8.54. The workaround as provided works but in some situations (most notably the home page) the code throws the error <code>Uncaught ReferenceError: bLoadCompleted is not defined. </code><br />
<br />
So instead of adding the line of code as suggested <br />
<br />
<code>|| bLoadCompleted </code> <br />
<br />
a better solution would be to add this <br />
<br />
<code>|| (typeof bLoadCompleted != "undefined" && bLoadCompleted)</code> <br />
<br />
Using the <code>typeof bLoadCompleted != "undefined"</code> prevents the <code>Uncaught ReferenceError: bLoadCompleted is not defined. </code> from being displayed. And now your web browsers debugging console won't get filled up with errors.</div>Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-84940630810021957652013-10-22T20:58:00.001+01:002013-10-22T20:58:14.276+01:00Setting Parameters for PS Utilities FavoritesMy last <a href="http://getlevel0.blogspot.com/2013/07/chrome-extension-for-peoplesoft.html">post</a> I talked about the <a href="https://chrome.google.com/webstore/detail/ps-utilities/jajoopnifcliapcngocgiidifkmboemc?hl=en-US">PS Utilities Chrome Extension</a>. Since it's initial release the PS Utilities extension has been updated with some new features such as<br />
<ol>
<li>adding the toolbar to the sign in page,</li>
<li>a Quick Logins menu item on the sign in page to allow you to store credentials and quickly sign in to an environment,</li>
<li>managing separate PeopleSoft environments,</li>
<li>adding help pages,</li>
<li>and improving the overall User Interface.</li>
</ol>
<div>
One feature there from the beginning has been adding Favorites that span your PeopleSoft environments. This makes managing Favorites so much easier. You can skip adding Favorites using the delivered functionality in each individual environment. (Inevitably they always got out of synch for me.) Instead you can use the PS Utilities Favorites functionality. You can add a Favorite once in one environment and the favorite will then show up in each environment you have PS Utilities active.<br />
<br /></div>
<div>
One great thing is you can add parameters to customize your Favorites. Open the PS Utilities options page and click on the Favorite tab (or choose Manage Favorites from the Favorites menu item.) In the table at the bottom the fourth column is the Parameters tab. The value must begin with a question mark (?) and then include name/value pairs separated with an equal sign (=) and series of pairs separated by an ampersand (&). (See <a href="http://en.wikipedia.org/wiki/Query_string">Query Strings</a> and <a href="http://en.wikipedia.org/wiki/Percent-encoding">Percent Encoding</a> for more information.) In PeopleSoft the name is almost always the name of the field used in the Search record. For example, if I want to always link to my User Profile, I would include <b>?OPRID=NYETMAN</b> in the parameters column for the <b>MAINTAIN_SECURITY</b>, <b>USER_MAINT</b>, <b>GBL</b> Favorite entry.<br />
<br /></div>
<div>
One handy name/value pair you can use is <b>ACTION=A</b>. Most components are registered in the Portal Registry in Update Display mode. But if you include <b>ACTION=A</b> then the component processor switches to Add mode instead (assuming the component has Add mode enabled and you have security access to Add mode.). If you use <b>ACTION=A</b> by itself you will likely be presented with the Add Search page (where you normally have to enter values to uniquely distinguish the new entry you are adding.) But sometimes (like the Personal Data component in PS HCM) that value is pre-populated (NEW in this case) which means the component logic will create the new value automatically. To skip this you can include <b>EMPLID=NEW</b> in your parameter list to jump right into the component in Add mode. The full parameter value for this favorite would be <b>?ACTION=A&EMPLID=NEW</b>.</div>
<div>
<br />
The Favorites menu item is probably one of my favorite and most used pieces of the PS Utilities extension.</div>
Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-27489285552543307232013-07-05T11:34:00.000+01:002013-07-05T11:34:24.836+01:00Chrome Extension for PeopleSoftMy friend and former colleague <a href="http://www.uffegraakjaer.com/" target="_blank">Uffe Graakjaer</a> has just published a new Chrome Extension called <a href="https://chrome.google.com/webstore/detail/ps-utilities/jajoopnifcliapcngocgiidifkmboemc?hl=en" target="_blank">PS Utilities</a> that might be of interest to PeopleSoft users. It can be found <a href="https://chrome.google.com/webstore/detail/ps-utilities/jajoopnifcliapcngocgiidifkmboemc?hl=en" target="_blank">here</a>.<br />
<br />
The extension is great for developers, administrators, testers, etc. who<br />
<ol>
<li>use Google Chrome web browser</li>
<li>to access multiple PeopleSoft environments</li>
<li>with different User IDs.</li>
</ol>
The main features are available through a new toolbar that can be positioned anywhere on the screen. The toolbar is configurable and can include<br />
<ol>
<li>the User ID that you are logged in as</li>
<li>the name of the PeopleSoft Environment that you are logged in to</li>
<li>a Shortcuts menu which, unlike Favourites, can span all environments</li>
<li>a link to display menu/component/page information normally accessed through CTRL-J keyboard combination.</li>
</ol>
I recommend checking it out. Uffe plans to add more features soon. Any ideas for new features (or feedback on the current extension) would also be appreciated.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com6tag:blogger.com,1999:blog-6096447389942161113.post-10221220093521462352012-05-03T13:47:00.000+01:002012-05-03T13:47:34.989+01:00Issue with BI Publisher/XML Publisher Word Add-inCame across a problem yesterday with the Oracle BI Publisher (XML Publisher) add-in for Microsoft Word. The main issue was receiving a Macro error when using any of the Oracle BI Publisher menu items (such as Data, Load Sample XML Data…) I've found references on website forums stating that corrupt .exd files might be the culprit. This seemed to be the case for me. If you come across such an issue, here are the steps that I followed to fix the issue:<br />
<br />
<ol>
<li>Close all open Word windows. (Sometimes I had to shut down the PC completely.)</li>
<li>Open My Computer and from the Tools menu choose Folder Options… On the View Tab, scroll down through the Advanced Settings: list and ensure the <b>Hide protected operating systems files (Recommended)</b> is NOT ticked. Press OK. </li>
<li>Now go Start, Run… and enter the following <b>%userprofile%\Application Data\Microsoft\Forms</b> and press the OK button. </li>
<li>In the window that opens up find the file that ends with <b>.exd</b>. Right click the file and choose Rename. Keep the filename as is and add <b>.old</b> to the end of it. (The next time this file is needed a new version will get placed here.) If there is more than one file that ends with <b>.exd</b> then rename all of these. Close the Window. </li>
<li>If you had <b>Hide protected operating systems files (Recommended)</b> ticked all along, follow the instructions in step 3 to go back and tick that setting again. </li>
<li>Now start up Word and see if BI Publisher/XML Publisher is working. If not, there may possibly be other <b>.exd</b> files in other locations that are corrupt. Seek those out and try renaming them. Or there might be another unrelated problem. I'd start by reinstalling the <b>Oracle BI Publisher Desktop</b>. </li>
</ol>
Hope this helps.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-27044988753334060692012-04-24T11:30:00.000+01:002012-04-24T11:30:19.092+01:00Using WITH Clause to mimic App Engine Temp TablesI've been working on an App Engine that makes use of Temp tables and uses the %Table meta-sql. It's sometimes difficult to debug your App Engine SQL because the data in the temp table gets deleted at the end of the run. So I get around this in my SQL client by using the WITH clause<br />
<br />
<pre>WITH <alias_one> AS
(subquery_sql_statement),
<alias_two> AS
(sql_statement_from_alias_one)
</pre><br />
That doesn’t make a lot of sense, so here’s a simple example you can run. <br />
<br />
<pre>with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE, 1 REPORT_REQUEST_NBR, 1 REQUEST_SEQ_NBR, 1 REPORT_NUMBER, '9999991' EMPLID, 'PGT' ACAD_CAREER FROM dual
union
SELECT '1' PROCESS_INSTANCE, 1 REPORT_REQUEST_NBR, 1 REQUEST_SEQ_NBR, 1 REPORT_NUMBER, '9999992' EMPLID, 'UG' ACAD_CAREER FROM dual
)
select * from NY_SSR_TSC_TMP;
</pre><br />
So I’ll turn an App Engine sql statement like this (from the delivered SSR_TSC_LIB App Engine - it's a library used by the Campus Solutions Transcript Generation Process):<br />
<br />
<pre>SELECT Z.REPORT_REQUEST_NBR
,Z.REQUEST_SEQ_NBR
,Z.REPORT_NUMBER
,%Bind(SEQNO)
,%Bind(PRINT_AREA_CODE)
,%Bind(SSR_ROWTYPE)
,A.AID_YEAR
,A.ITEM_TYPE
,A.ACAD_CAREER
,B.DESCR
,A.DISBURSED_AMOUNT
,A.ACCEPT_AMOUNT
,%Bind(TSCRPT_TYPE_AET.AWARD_AID_YEAR_PRT)
,%Bind(TSCRPT_TYPE_AET.AWARD_AMOUNT_PRT)
FROM %Table(SSR_TSC_TMP) Z
, PS_STDNT_AWARDS A
, PS_ITEM_TYPE_FA B
WHERE Z.PROCESS_INSTANCE = %ProcessInstance
AND A.EMPLID = Z.EMPLID
AND A.INSTITUTION = %Bind(INSTITUTION)
AND A.ACAD_CAREER = Z.ACAD_CAREER
AND A.ACCEPT_AMOUNT > 0
AND A.ITEM_TYPE = B.ITEM_TYPE
AND A.SETID = B.SETID
AND A.AID_YEAR = B.AID_YEAR
AND B.INCLUDE_IN_TSCRPT = 'Y'
AND A.AWARD_STATUS = 'A'
AND B.EFF_STATUS = 'A'
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_ITEM_TYPE_FA B1
WHERE B.SETID = B1.SETID
AND B.ITEM_TYPE = B1.ITEM_TYPE
AND B.AID_YEAR = B1.AID_YEAR);
</pre><br />
into something like this (%Table(SSR_TSC_TMP) changed to NY_SSR_TSC_TMP, %ProcessInstance changed to 0, some %Bind’s removed and the %Bind(INSTITUTION) in the WHERE clause changed to 'ZZZZZ'):<br />
<br />
<pre>with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE,
1 REPORT_REQUEST_NBR,
1 REQUEST_SEQ_NBR,
1 REPORT_NUMBER,
'9999991' EMPLID,
'PGT' ACAD_CAREER
FROM dual
)
SELECT Z.REPORT_REQUEST_NBR
,Z.REQUEST_SEQ_NBR
,Z.REPORT_NUMBER
,A.AID_YEAR
,A.ITEM_TYPE
,A.ACAD_CAREER
,B.DESCR
FROM NY_SSR_TSC_TMP Z
, PS_STDNT_AWARDS A
, PS_ITEM_TYPE_FA B
WHERE Z.PROCESS_INSTANCE = 0
AND A.EMPLID = Z.EMPLID
AND A.INSTITUTION = 'ZZZZZ'
AND A.ACAD_CAREER = Z.ACAD_CAREER
AND A.ACCEPT_AMOUNT > 0
AND A.ITEM_TYPE = B.ITEM_TYPE
AND A.SETID = B.SETID
AND A.AID_YEAR = B.AID_YEAR
AND B.INCLUDE_IN_TSCRPT = 'Y'
AND A.AWARD_STATUS = 'A'
AND B.EFF_STATUS = 'A'
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_ITEM_TYPE_FA B1
WHERE B.SETID = B1.SETID
AND B.ITEM_TYPE = B1.ITEM_TYPE
AND B.AID_YEAR = B1.AID_YEAR);
</pre><br />
The power of the WITH clause really proves itself out whenever the new dummy table is referenced more than once in your SQL statement.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-56886973207564574432012-03-16T16:42:00.000+00:002012-03-16T16:42:59.815+00:00Problems with Pagelet Wizard Data Source ParametersA fellow developer ran into a problem recently when he tried creating a pagelet using the Pagelet Wizard. For the purposes of this post I'll simplify what he was doing in order to get to the heart of the problem. The underlying query he used for the pagelet had 2 prompts. The second prompt used the value of the first prompt to limit the values that are returned. You often see prompts like this where you first select a Business Unit first and then another value from a table where Business Unit is a key. And you need to select the Business Unit first or the 2nd prompt will return no values. When he ran this Query from Query Manager or Query Viewer, the prompts worked as expected.<br />
<br />
Later he created the pagelet using this query in the Pagelet Wizard. The prompts from the query were picked up without any problem as Data Source parameters (Step 3 of 6 in the Pagelet Wizard.) By specifying them as "User Specified" for the Usage Type, you get the Customize Pagelet link (the pencil icon on the pagelet border) when it is published to the homepage. On the homepage he could click the pencil icon and the prompts were displayed correctly, but they didn't work. The first prompt could be selected. But the value returned had no impact on the second prompt. After we looked into it we discovered that it was an issue with this customize pagelet component (PTPPB_USER_PREF). <br />
<br />
To get around this issue I suggested that a new component needed to be displayed whenever the pencil icon was clicked. Luckily this can be easily done. Whenever a homepage pagelet has been created using the Pagelet Wizard, a new content reference is added to the Portal Registry. One of the options that is available for a pagelet content reference is the content reference for the Edit URL Information. By default the Pagelet Wizard sets this to the delivered component (the one we discovered that doesn't work when using prompts where the first value should be used to limit the second prompt values.) So by overwriting this content reference, we can set it to anything we want. So that's what we did. <br />
<br />
The new component was built using derived records and fields. So we could make the prompts work the way we needed them to. We had to write the values selected back to the table that is used to store Customise pagelet values for Pagelet Wizard built pagelets. This was easy enough to figure out. It's the PTPPB_USER_PRMS table, and is simply keyed on the Pagelet name, Pagelet Field and OPRID. <br />
<br />
So problem solved, right? Not quite. The client we were working for had decided to allow quite a few users access to the Pagelet Wizard, not just developers or analysts. They figured if users could have access to Query Manager to build Queries, they could also have access to the Pagaelet Wizard to let them display their query results in a nice, easy to view format. This means that almost any Pagelet Wizard user could modify and redeploy this Pagelet. Each time this happens, the Customize content reference would get set to the default, over-writing our custom component's content reference.<br />
<br />
This solution really is only an option where Pagelet Wizard is used by developers or analysts and is subject to change control, which is how it was at my previous employer. In this situation support documentation can be created to highlight the fact that if the pagelet is modified, the Customise content reference needs to be reset to our custom component. <br />
<br />
In the end, my fellow developer had to customise the delivered Customise component, to make it work correctly. Sometimes you just have to customise, don't you?Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-90452306378076491742011-01-07T10:52:00.003+00:002012-04-24T11:09:53.998+01:00Debugging the Save WarningWhile developing pages and components, I'll sometimes get a Save Warning message, i.e. "You have unsaved data on this page. Click OK to go back and save, or Cancel to continue." when I try to leave a component with out having made any changes. This is often caused by PeopleCode updating the value of a field (often hidden) on your page. A quick way to figure out what is the offending field is to use JavaScript debugging. I often develop in FireFox and use the FireBug add-on. (Google Chrome and IE8 have their built-in debuggers.) After turning on your JavaScript debugger, go to the offending page and choose PT_SAVEWARNING JavaScript library. Find the checkFormChanged function and place breakpoints on both of the return true statements in this code block. (This is lines 181 and 184 in PeopleTools 8.50.)<br />
<br />
<div dir="ltr" class="mw-geshi" style="text-align: left;"><div class="javascript source-javascript"><pre class="de1"><span class="kw1">for</span> <span class="br0">(</span><span class="kw2">var</span> j <span class="sy0">=</span> <span class="nu0">0</span><span class="sy0">;</span> j <span class="sy0"><</span> form.<span class="me1">length</span><span class="sy0">;</span> <span class="sy0">++</span>j<span class="br0">)</span>
<span class="br0">{</span>
bIsChanged <span class="sy0">=</span> isChanged<span class="br0">(</span>form.<span class="me1">elements</span><span class="br0">[</span>j<span class="br0">]</span><span class="sy0">,</span> objFrame<span class="br0">)</span><span class="sy0">;</span>
<span class="kw1">if</span> <span class="br0">(</span>bIsChanged <span class="sy0">&&</span> form.<span class="me1">ICSaveWarningFilter</span><span class="br0">)</span> <span class="br0">{</span>
<span class="kw1">if</span> <span class="br0">(</span>form.<span class="me1">ICSaveWarningFilter</span>.<span class="me1">value</span> <span class="sy0">!=</span> <span class="st0">"1"</span><span class="br0">)</span>
<span class="kw1">return</span> <span class="kw2">true</span><span class="sy0">;</span>
<span class="br0">}</span>
<span class="kw1">else</span> <span class="kw1">if</span> <span class="br0">(</span>bIsChanged<span class="br0">)</span>
<span class="kw1">return</span> <span class="kw2">true</span><span class="sy0">;</span>
<span class="br0">}</span></pre></div></div><br />
<br />
Next add a new watch expression in your debugger: <br />
<br />
<div dir="ltr" class="mw-geshi" style="text-align: left;"><div class="javascript source-javascript"><pre class="de1">form.<span class="me1">elements</span><span class="br0">[</span>j<span class="br0">]</span>.<span class="me1">id</span></pre></div></div><br />
<br />
Back in your browser window, do whatever you were doing to trigger the Save Warning message. This time the debugger should stop though and you can inspect your watch expression to see what Form element ID is the one that has changed. This normally matches the RECNAME_FIELDNAME of your field. You can then debug backwards from there in PeopleCode to find the code that is updating this field.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com3tag:blogger.com,1999:blog-6096447389942161113.post-11640890564452463512010-12-02T14:03:00.004+00:002010-12-02T14:37:39.792+00:00Improving PeopleSoft with jQuery - Some Examples (Part 2)<span style="font-size:130%;">Making a Collapsible Data Area a Bit Better</span><br />Have you ever turned on the Collapsible Data Area property on a group box or grid? If you do, all you get on the page to expand or collapse your group box or grid is a little tiny triangle at the far left of the group box or grid label. It isn't that easy to click. Wouldn't it be better if you could click the entire bar to make your grid or group box expand or collapse? Well, here's a little snippet of JavaScript using jQuery that lets you do this.<br /><br /><div dir="ltr" class="mw-geshi" style="text-align: left;"><div class="javascript source-javascript"><pre class="de1"><span class="co1">//% New JavaScript used for making the entire bar of an Expandable/Collapsible Group Box clickable.</span><br /><br />$<span class="br0">(</span>document<span class="br0">)</span>.<span class="me1">ready</span><span class="br0">(</span><span class="kw2">function</span><span class="br0">(</span><span class="br0">)</span><span class="br0">{</span><br /><br /> <span class="co1">//%This line finds all <img> elements with a title of "Expand section" or "Collapse Section", then gets the parent <a>, </span><br /> <span class="co1">//%then the parent <td>. On this <td> it 1.) updates the css to make the cursor a pointer and 2.) adds a click handler</span><br /> $<span class="br0">(</span><span class="st0">"img[title='Expand section'],img[title='Collapse section']"</span><span class="br0">)</span>.<span class="me1">parent</span><span class="br0">(</span><span class="st0">'a'</span><span class="br0">)</span>.<span class="me1">parent</span><span class="br0">(</span><span class="st0">'td'</span><span class="br0">)</span>.<span class="me1">css</span><span class="br0">(</span><span class="st0">'cursor'</span><span class="sy0">,</span><span class="st0">'pointer'</span><span class="br0">)</span>.<span class="me1">click</span><span class="br0">(</span><span class="kw2">function</span><span class="br0">(</span><span class="br0">)</span><span class="br0">{</span><br /><br /> <span class="co1">//%The click handler added to this <td> is a new function taken from the first <a> element that can be found in relation to this <td></span><br /> <span class="kw2">var</span> theClick<span class="sy0">=</span><span class="kw2">new</span> <span class="kw2">Function</span><span class="br0">(</span>$<span class="br0">(</span><span class="st0">"a img[title='Expand section'], a img[title='Collapse section']"</span><span class="sy0">,</span> <span class="kw1">this</span><span class="br0">)</span>.<span class="me1">parent</span><span class="br0">(</span><span class="st0">'a'</span><span class="br0">)</span>.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"href"</span><span class="br0">)</span><span class="br0">)</span><span class="sy0">;</span><br /> theClick<span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span><br /><br /> <span class="br0">}</span><span class="br0">)</span><span class="sy0">;</span><br /><span class="br0">}</span><span class="br0">)</span><span class="sy0">;</span></pre></div></div><br /><div><span style="font-size:130%;"><span class="Apple-style-span">Points to Consider</span></span><br /><ul><li>Based on PeopleTools 8.50 with a SWAN stylesheet.<br /></li><li>This code works on group boxes with a title positioned on the left of the group box. With group boxes where the title is positioned in the center or to the right it might not work. Or with grids that have any of the navigation bar items displayed then this will likely not work.</li><li>Because of these limitations, I would not include this code on PT_PAGESCRIPT to make it a system wide feature. I instead would only include it on specific pages where I know the group box(es) or grid(s) will be formatted in the correct manner.<br /></li></ul></div><div><span class="Apple-style-span" style="font-size:130%;">Summary</span></div><div>This has been another relatively simple example of how jQuery can make your PeopleSoft pages that much better.<br /></div>Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com0tag:blogger.com,1999:blog-6096447389942161113.post-63970681845847119682010-11-25T12:58:00.012+00:002010-11-26T17:33:16.080+00:00Improving PeopleSoft with jQuery - Some Examples (Part 1)I must thank Jim Marion for turning me on to <a href="http://jquery.com/">jQuery</a>. He has made various posts over the last 3 or 4 years about it (<a href="http://jjmpsj.blogspot.com/search/label/jQuery">see here</a>.) I've also sat in on one or 2 presentations he's made where he's mentioned it. It's a great JavaScript library that lets you make even more improvements to your PeopleSoft application.<br /><br />Since PeopleTools 8.50 (or maybe it was a late patch of tools 8.49) there is no longer a size limit on HTML Definitions. PT_PAGESCRIPT is the JavaScript definition that is included in all (or at least most) pages served up by PeopleSoft. This is now the best place for including the jQuery library.<br /><br />So now that we have access to jQuery, what kind of things can we do with it? Here's the first of a couple of examples I've come up with.<br /><br /><span><span class="Apple-style-span" style="font-size: large;">Add options to the Page Bar</span></span><br />Have you ever tried to print a PeopleSoft page using your browsers print functionality. It doesn't always look good. Depending on the browser and what exactly has the focus when you choose Print, it may include the Portal Heading and the target content may have scroll bars included. I think it would be great if the Page Bar had an option to print the page. Here's a way of getting JavaScript and jQuery to do it for you.<div><br /></div>Why do you need jQuery to do it? Because as a PeopleSoft developer, you don't have access to the Page Bar. It is not generated via an iScript, HTML Definition or an Application Package. You could use something like MonkeyGrease on your web servers to rewrite the response to include a new link on the Page Bar. But that seems to be a bit overkill. By using jQuery, everything is under control within Application Designer like most any PeopleSoft customisation.<br />So onto the code! Add the following to PT_PAGESCRIPT after the jQuery library. (I hope it is self documented well enough for you to follow through.)<br /><br /><div style="text-align: left;" class="mw-geshi" dir="ltr"><div class="javascript source-javascript"><pre class="de1"><span class="co1">//%New Print button added to Page Bar</span><br /><span class="co1">//%Ensure that document is ready before proceeding</span><br />$<span class="br0">(</span>document<span class="br0">)</span>.<span class="me1">ready</span><span class="br0">(</span><span class="kw2">function</span><span class="br0">(</span><span class="br0">)</span> <span class="br0">{</span><br /> <span class="co1">//%Check if the Page Bar is on the page</span><br /> <span class="kw1">if</span><span class="br0">(</span>$<span class="br0">(</span><span class="st0">"#PAGEBAR"</span><span class="br0">)</span>.<span class="me1">length</span><span class="sy0">==</span><span class="nu0">1</span><span class="br0">)</span> <span class="br0">{</span><br /> <span class="co1">//% Declare local variables</span><br /> <span class="kw2">var</span> printAnchor<span class="sy0">;</span><br /> <span class="kw2">var</span> printImg<span class="sy0">;</span><br /> <span class="kw2">var</span> newText <span class="sy0">=</span> <span class="st0">"Print Page"</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%Add new style to prevent #PAGEBAR from displaying when printing</span><br /> $<span class="br0">(</span><span class="st0">'<style media="print"> #PAGEBAR {display: none} </style>'</span><span class="br0">)</span>.<span class="me1">appendTo</span><span class="br0">(</span><span class="st0">'head'</span><span class="br0">)</span><span class="sy0">;</span> <br /> <br /> <span class="co1">//%Get the Page Bar div and table cell</span><br /> <span class="kw2">var</span> pgBar <span class="sy0">=</span> $<span class="br0">(</span><span class="st0">"#PAGEBAR"</span><span class="br0">)</span><span class="sy0">;</span><br /> <span class="kw2">var</span> pgBarLinksCell <span class="sy0">=</span> $<span class="br0">(</span><span class="st0">"table > tbody > tr:eq(0) > td:eq(2)"</span><span class="sy0">,</span> pgBar<span class="br0">)</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%Check if there are any anchors on the Page Bar </span><br /> <span class="co1">//%Though it seems if there is a Page Bar then there is always an anchor </span><br /> <span class="kw1">if</span> <span class="br0">(</span> $<span class="br0">(</span><span class="st0">"a"</span><span class="sy0">,</span> pgBarLinksCell<span class="br0">)</span>.<span class="me1">length</span> <span class="sy0">></span> <span class="nu0">0</span> <span class="br0">)</span> <span class="br0">{</span><br /> <span class="co1">//% If it is, clone the last one and separate the image from the anchor</span><br /> printAnchor <span class="sy0">=</span> $<span class="br0">(</span><span class="st0">"a:last"</span><span class="sy0">,</span> pgBarLinksCell<span class="br0">)</span>.<span class="me1">clone</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span><br /> printImg <span class="sy0">=</span> $<span class="br0">(</span><span class="st0">"img"</span><span class="sy0">,</span> printAnchor<span class="br0">)</span><span class="sy0">;</span><br /> printAnchor.<span class="me1">html</span><span class="br0">(</span><span class="st0">""</span><span class="br0">)</span><span class="sy0">;</span><br /> <span class="br0">}</span> <span class="kw1">else</span> <span class="br0">{</span><br /> <span class="co1">//%otherwise build it manually </span><br /> printAnchor <span class="sy0">=</span> $<span class="br0">(</span><span class="st0">"<a tabindex='0'></a>"</span><span class="br0">)</span><span class="sy0">;</span><br /> printImg <span class="sy0">=</span> $<span class="br0">(</span><span class="st0">"<img hspace='0' border='0' align='absmiddle' vspace='0' ></img>"</span><span class="br0">)</span><span class="sy0">;</span><br /> <span class="kw2">var</span> cssObj <span class="sy0">=</span> <span class="br0">{</span><br /> <span class="st0">'font-size'</span> <span class="sy0">:</span> <span class="st0">'9pt'</span><span class="sy0">,</span> <br /> <span class="st0">'font-weight'</span> <span class="sy0">:</span> <span class="st0">'normal'</span><span class="sy0">,</span> <br /> <span class="st0">'font-style'</span> <span class="sy0">:</span> <span class="st0">'normal'</span><span class="sy0">,</span><br /> <span class="st0">'color'</span> <span class="sy0">:</span> <span class="st0">'rgb(51, 102, 153)'</span><span class="sy0">,</span> <br /> <span class="st0">'text-decoration'</span> <span class="sy0">:</span> <span class="st0">'none'</span><br /> <span class="br0">}</span><br /> printAnchor.<span class="me1">css</span><span class="br0">(</span>cssObj<span class="br0">)</span><span class="sy0">;</span><br /> <span class="br0">}</span><br /> <span class="co1">//%set (or overwrite) the anchor attributes</span><br /> printAnchor.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"id"</span><span class="sy0">,</span> <span class="st0">"OXFPRINT"</span><span class="br0">)</span><span class="sy0">;</span><br /> printAnchor.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"name"</span><span class="sy0">,</span> <span class="st0">"OXFPRINT"</span><span class="br0">)</span><span class="sy0">;</span><br /> printAnchor.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"href"</span><span class="sy0">,</span> <span class="st0">""</span><span class="br0">)</span><span class="sy0">;</span><br /> printAnchor.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"tabindex"</span><span class="sy0">,</span> parseInt<span class="br0">(</span>printAnchor.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"tabindex"</span><span class="br0">)</span><span class="br0">)</span><span class="sy0">+</span><span class="nu0">1</span><span class="br0">)</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%set (or overwrite) the image attributes</span><br /> printImg.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"title"</span><span class="sy0">,</span> newText<span class="br0">)</span><span class="sy0">;</span><br /> printImg.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"alt"</span><span class="sy0">,</span> newText<span class="br0">)</span><span class="sy0">;</span><br /> printImg.<span class="me1">attr</span><span class="br0">(</span><span class="st0">"src"</span><span class="sy0">,</span><span class="st0">"/cs/FSQA850/cache/PT_PRINT_1.gif"</span><span class="br0">)</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%Add a click event to the anchor</span><br /> printAnchor.<span class="me1">click</span><span class="br0">(</span><span class="kw2">function</span><span class="br0">(</span>event<span class="br0">)</span> <span class="br0">{</span><br /> <br /> <span class="co1">//%prevent the default from occuring</span><br /> event.<span class="me1">preventDefault</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%IE work-around printing in an iframe</span><br /> <span class="kw1">try</span> <span class="br0">{</span> <br /> document.<span class="me1">execCommand</span><span class="br0">(</span><span class="st0">'print'</span><span class="sy0">,</span> <span class="kw2">false</span><span class="sy0">,</span> <span class="kw2">null</span><span class="br0">)</span><span class="sy0">;</span> <br /> <span class="br0">}</span> <br /> <span class="kw1">catch</span><span class="br0">(</span>e<span class="br0">)</span> <span class="br0">{</span> <br /> window.<span class="kw3">print</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span> <br /> <span class="br0">}</span><br /> <span class="kw1">return</span> <span class="kw2">false</span><span class="sy0">;</span><br /> <span class="br0">}</span><span class="br0">)</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%Combine (or recombine) anchor, image and text</span><br /> printAnchor.<span class="me1">wrapInner</span><span class="br0">(</span>printImg<span class="br0">)</span>.<span class="me1">append</span><span class="br0">(</span><span class="st0">"&nbsp;"</span><span class="sy0">+</span>newText<span class="br0">)</span><span class="sy0">;</span><br /> <br /> <span class="co1">//%append the new Print Anchor to the end of the Page Bar</span><br /> $<span class="br0">(</span>pgBarLinksCell<span class="br0">)</span>.<span class="me1">append</span><span class="br0">(</span><span class="st0">"&nbsp;&nbsp;&nbsp;"</span><span class="br0">)</span>.<span class="me1">append</span><span class="br0">(</span>printAnchor<span class="br0">)</span><span class="sy0">;</span><br /> <span class="br0">}</span><br /> <span class="kw1">else</span> <span class="br0">{</span><br /> <span class="co1">//%write the code to include a Page Bar</span><br /> <span class="br0">}</span><br /><span class="br0">}</span><span class="br0">)</span><span class="sy0">;</span></pre></div></div><br /><br /><span><span class="Apple-style-span" style="font-size: large;">Here's what it looks like</span></span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://2.bp.blogspot.com/_UDppdbcA6Sg/TO_lmiM6NgI/AAAAAAAAAA0/JKz7he2qleg/s1600/pagebar%2Bwith%2Bprint.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 198px;" src="http://2.bp.blogspot.com/_UDppdbcA6Sg/TO_lmiM6NgI/AAAAAAAAAA0/JKz7he2qleg/s400/pagebar%2Bwith%2Bprint.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5543902116495111682" /></a><div><span><span class="Apple-style-span" style="font-size: large;">Points to Consider</span></span><br /><ul><li>The path to the Printer image is hard coded. Meta-HTML %Image won't work in this situation because it is a JavaScript library. To make it better, you could use %URL or create an iScript that uses %Request.GetImageURL to return the path to the image and use jQuery within the script to make an AJAX call to the iScript.</li><li>Also, I think the image is too gray. It's based on the older style. To match the newer swan style it should have more blue.</li><li>I've tested this on IE7, FireFox 3.6 and Google Chrome 7.0.5. It's possible it won't work with other browsers.</li></ul></div><div><span class="Apple-style-span" style="font-size: large;">Summary</span></div><div>With jQuery you have an extra layer of control over your user's interface. This was just one example of what can be done using the jQuery JavaScript library. I hope to post another couple examples soon.</div>Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com6tag:blogger.com,1999:blog-6096447389942161113.post-956640956521293842010-09-30T23:59:00.013+01:002010-10-01T16:03:33.307+01:00PeopleSoft BrandingHere's one way to change your PeopleSoft Portal Header from this<br /><br /><a href="http://2.bp.blogspot.com/_UDppdbcA6Sg/TKUXZhN4MnI/AAAAAAAAAAU/bBFA0aqPw-U/s1600/old+header.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 512px; height: 22px;" src="http://2.bp.blogspot.com/_UDppdbcA6Sg/TKUXZhN4MnI/AAAAAAAAAAU/bBFA0aqPw-U/s400/old+header.jpg" alt="" id="BLOGGER_PHOTO_ID_5522846245220528754" border="0" /></a>to something like this<br /><br /><a href="http://4.bp.blogspot.com/_UDppdbcA6Sg/TKUXyUDHZlI/AAAAAAAAAAc/wYuV-43a8G8/s1600/new+header.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 512px; height: 34px;" src="http://4.bp.blogspot.com/_UDppdbcA6Sg/TKUXyUDHZlI/AAAAAAAAAAc/wYuV-43a8G8/s400/new+header.jpg" alt="" id="BLOGGER_PHOTO_ID_5522846671182456402" border="0" /></a><span style="font-size:130%;">Preface</span><br />I was grateful I discovered @peoplesoftwiki's <a href="http://www.peoplesoftwiki.com/branding-header-links/">great post</a> about PeopleSoft Branding. It meant that this post wouldn't have to be quite as long. Because as it turned out I followed the same steps as @peoplesoftwiki (extending Application Package PT_BRANDING and overriding the Branding Package on PeopleTools Options page.) I went one or 2 steps further it seems so I'd like to expand on that.<br /><br />Before I begin, I need to tell you the main assumption I made. We are moving to PeopleTools 8.50 and we have decided to use the new Swan style. This is important as there is code in PT_BRANDING methods that is wrapped in if statements checking whether the style = "swan". That meant I only had to focus on making changes to items where this was true.<br /><br />And one more thing before going any further: configuration good, customisation bad. (Though I don't always follow that mantra.)<br /><br /><span style="font-size:130%;">Application Package</span><br />So just like @peoplesoftwiki, I extended PT_BRANDING. I made a few changes, including:<br /><ol><li>adding new methods to get data out of the system that I wanted to display in the Portal Header</li><ul><li>setGreeting, to prefix the Users greeting as set in Personalize Content with the description of the Environment as set in PeopleTools Options</li><li> getOxfamLinks, to read and labels and links from the message catalog (labels being the message, and the links being the explanation text) to be displayed along the top of our branded portal.</li></ul><li>replacing calls to the delivered HTML Definitions that formed the basis of the Portal Header. Those 3 methods and the HTML defintions are</li><ul><li>getIframeHeaderHTML - PT_IFRAME_HDR_SWAN</li><li>GetExpPasswordHdrHTML - PORTAL_EXP_PASSWORD_HDR</li><li>GetUniHeaderHTML - PORTAL_UNI_HEADER_NNS_SWAN<br /></li></ul></ol>The changes made to the methods getIframeHeaderHTML, GetExpPasswordHdrHTML and GetPortalUniHeaderNNS were all very similar, so I'll just go in to details for the method getIframeHeaderHTML. I basically left the code all the same, except for adding calls to my new methods setGreeting and getOxfamLinks and another call to get some more text from the message catalog and a call to the GetPortalHomepageURL() function to retrieve the Homepage URL.<br /><br /><span style="font-size:130%;">HTML Definition</span><br />To make use of all this, I would either need to change the delievered HTML definition or create my own. So I created my own by opening the delivered PT_IFRAME_HDR_SWAN and doing a Save As to create OXF_IFRAME_HDR_SWAN. By doing this I had complete control over the HTML I wanted displayed. The main change I made was to the pthdr2container <div>, changing it from<br /><br /><div style="text-align: left;" class="mw-geshi" dir="ltr"><div class="html4strict source-html4strict"><pre class="de1"><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2container"</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2logoswan"</span>></span> <span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2greeting"</span>><br /><<span class="kw2">span</span> <span class="kw3">class</span><span class="sy0">=</span><span class="st0">"greeting"</span>></span>%bind(:15)<span class="sc2"><<span class="sy0">/</span><span class="kw2">span</span>><br /><<span class="sy0">/</span><span class="kw2">div</span>></span><br />%bind(:16)<br /><span class="sc2"><<span class="kw2">ul</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2links"</span>></span><br />%bind(:17)<br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">ul</span>></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span></pre></div></div>to<br /><div style="text-align: left;" class="mw-geshi" dir="ltr"><div class="html4strict source-html4strict"><pre class="de1"><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2container"</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">class</span><span class="sy0">=</span><span class="st0">"oxfblend"</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2logoswan"</span><br /><span class="kw3"> onclick</span><span class="sy0">=</span><span class="st0">"javascript:window.location='%bind(:27)';return false;"</span>></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2greeting"</span>></span><br /><span class="sc2"><<span class="kw2">span</span> <span class="kw3">class</span><span class="sy0">=</span><span class="st0">"oxfps"</span>></span>%bind(:26)<span class="sc2"><<span class="sy0">/</span><span class="kw2">span</span>></span><br /><span class="sc2"><<span class="kw2">span</span> <span class="kw3">class</span><span class="sy0">=</span><span class="st0">"oxfgreeting"</span>></span>%bind(:15)<span class="sc2"><<span class="sy0">/</span><span class="kw2">span</span>></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span><br /><span class="sc2"><<span class="kw2">ul</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"oxfhdrlinks"</span>></span><br />%bind(:25)<br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">ul</span>></span><br /><span class="sc2"><<span class="kw2">ul</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"pthdr2links"</span>></span><br /><span class="sc2"><<span class="kw2">span</span>></span>%bind(:17) <span class="sc2"><<span class="sy0">/</span><span class="kw2">span</span>></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">ul</span>></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">id</span><span class="sy0">=</span><span class="st0">"oxfheaderLine3"</span>></span><br /><span class="sc2"><<span class="kw2">div</span> <span class="kw3">class</span><span class="sy0">=</span><span class="st0">"clearer"</span>><<span class="sy0">/</span><span class="kw2">div</span>></span><span class="sc-1"><!--Important - ensures floats<br />are contained--></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span><br /><span class="sc2"><<span class="sy0">/</span><span class="kw2">div</span>></span></pre></div></div>The main changes were adding new <div> oxfblend, oxfheaderLine3 and clearer, new <span> oxfps and oxfgreeting and new <ul> oxfhdrlinks. I also removed %bind(:16), the search box, which we didn't think made any sense to have in the Portal Header.<br /><br /><span style="font-size:130%;">Stylesheets</span><br />To make all these new elements on the page look any good, I had to update the Stylesheet. PSHDR2_SWAN held all the styles to control the Portal Header. For example, it defined the style pthdr2logoswan which has the background image as the Oracle Logo. Well we wanted to use the Oxfam Logo so I made a copy of PSHDR2_SWAN to create OXF_HDR2_SWAN and I made my changes to that.<br /><br />Remember, configuration good, customisation bad. So I copied PSSTYLEDEF_SWAN to create OXF_STYLEDEF_SWAN. And in OXF_STYLEDEF_SWAN, I replaced PSHDR2_SWAN with OXF_HDR2_SWAN. When you switch to the new swan style, you update Default Stylesheet on PeopleTools Options to PSSTYLEDEF_SWAN. I instead switched it to OXF_STYLEDEF_SWAN.<br /><br /><span style="font-size:130%;">Images</span><br />I had to create 4 image definitions in App Designer. Each of these are referenced in the stylesheet OXF_HDR2_SWAN as background images for 4 different elements.<br /><a href="http://3.bp.blogspot.com/_UDppdbcA6Sg/TKWbkYjW8oI/AAAAAAAAAAs/j86wRcykhAU/s1600/Oxfam_Portal_Header_Images.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 512px; height: 34px;" src="http://3.bp.blogspot.com/_UDppdbcA6Sg/TKWbkYjW8oI/AAAAAAAAAAs/j86wRcykhAU/s400/Oxfam_Portal_Header_Images.jpg" alt="" id="BLOGGER_PHOTO_ID_5522991567408132738" border="0" /></a><br /><span style="font-size:130%;">Bringing it all Together</span><br />So here's how it all works together. The PT_BRANDING extended Application Package (with new methods) calls the new HTML Definitions. Updated or newly created bind parameters are passed to the custom HTML Definition to generate the content of the Portal Header. The new stylesheets and images provide the formatting for the Portal Header HTML. What you can come up with is only limited by your imagination.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com5tag:blogger.com,1999:blog-6096447389942161113.post-86515183022715986452010-07-28T16:51:00.004+01:002010-10-01T16:03:49.069+01:00Most Recently UsedA new feature of PeopleTools 8.50 is the horizontal navigation menu that runs across the top of the screen below the header. It means the menu down the left hand side is no longer needed. This new horizontal menu has a Favourites menu item, and on this it keeps track of the most recently accessed components you have navigated to. But it is capped at the last 5. Wouldn't it be great to increase this, to 10 maybe. The Most Recently Used items are set using the PT_HNAV_JS HTML Definition. Line 1474 has a CONSTANT called MAX_MRU which is set at 5 as delivered. Increase this to 10 (or more if you want) and voila, your Most Recently Used items increase to 10.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com4tag:blogger.com,1999:blog-6096447389942161113.post-3450506030880706322010-07-28T14:43:00.005+01:002010-10-01T16:03:59.848+01:00WelcomeWelcome to my PeopleTools blog. I've been a PeopleSoft Developer for over 10 years. I've been regularly following a number of blogs for the last 2 years now and I thought it was about time I gave something back. So expect to find posts about various PeopleTools topics: App Designer, PeopleCode, Portal, PIA, App Servers, Integration Broker, and so on. And tell all of your PeopleSoft friends.Neil Yetmanhttp://www.blogger.com/profile/02963294804884377748noreply@blogger.com2