print "\n\n#Calls the procedure that construct the code that popups
messages to users\n\n";
$myStart = '^-- Name:
facdeptstat$statlist.QueryList$';
$myEnd = '^
exception$';
$mySearch = '^
WSGL.ClosePageBody;';
$myReplace = '
htp.p(build_alerts_js);'."\n".
' WSGL.ClosePageBody;';
&SimpleSearchRepRange($myStart,
$myEnd, $mySearch, $myReplace);
Note the indentation and the use of Perl's print to comment the code and provide action messages when the script is run all in one shot.
print "\n\n#Removes the buttons and return link at the bottom of
the query form.\n\n";
$myStart = '-- Name:
facdeptstat$statlist.QueryList';
$myEnd = '
exception';
$mySearch = '
htp.formOpen(curl => \'facdeptstat$statlist.querylist{;.}{;*}{;?}
WSGL.NavLinks;';
$myReplace = '';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
Above: note the use of {;.}{;*}{;?} which means 'match any number of characters' to avoid writing huge $mySearch variables. Also this is a multi-line search and replace.
print "\n\n##Improve the LOV of the program c/f\n\n";
$myStart = '-- Name:
gbchklaw$sel_apl_prg.l_pog_pd_name_listofvalues';
$myEnd = '
exception';
$mySearch = 'function
PassBack(P_L_POG_PD_NAME,P_L_POG_ORG_ABBRV,P_L_POG_ORG_DESC)';
$myReplace = 'function
PassBack(P_L_POG_PD_NAME,P_L_POG_ORG_ABBRV,P_L_POG_CAY_CODE,P_L_POG_ORG_DESC)';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_prg.l_pog_pd_name_listofvalues';
$myEnd = '
exception';
$mySearch = 'opener.document.forms[0].P_L_POG_ORG_DESC.value
= P_L_POG_ORG_DESC;';
$myReplace = 'opener.document.forms[0].P_L_POG_ORG_DESC.value
= P_L_POG_ORG_DESC;\n'.
' opener.document.forms[0].P_L_CAY_CODE.value = P_L_POG_CAY_CODE;';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$mySearch = '(--
Name: gbchklaw\$sel_apl_prg\.l_pog_pd_name_listofvalues)(.*?)'.
'(c1rec.L_POG_ORG_ABBRV,)(.*?)(replace)'.
'(.*?)( exception)';
$myReplace = '$1$2$3$4replace(replace(c1rec.L_POG_CAY_CODE,$4$5$6$7';
&mSearchRep($mySearch, $myReplace);
Above: note the way I group several actions
together under one general comment. Also check out the last construct:
&mSearcgRep: very concise way of getting a tough job done. You need
to know a few things about PERL for this but it saves a great deal of time.
If anybody has the time to explain it in detail I will be very glad to
publish their writting. You could even make it an article if you want.
print "\n\n##Replace all instances of x_pv_grant_pi
with pv_grant_pi_t1.\n\n";
$mySearch = 'X_PV_GRANT_PI';
$myReplace = 'pv_grant_pi_t1';
&mSearchRep($mySearch, $myReplace);
$mySearch = 'x_pv_grant_pi';
$myReplace = 'pv_grant_pi_t1';
&mSearchRep($mySearch, $myReplace);
Above: best and simplest case for using &mSearchRep:
a global search replace throughout the package.
print "\n\n##Optimize the BuildSQL where clause a bit: knock 1 outer join out.\n\n";
$myStart = '-- Name:
gbchklaw$sel_apl_prg.BuildSQL';
$myEnd = '
exception';
$mySearch = '
I_WHERE := I_WHERE || \' and L_CAY.CODE (+) = CF_APPL_SEL_PRG.CA\';';
$myReplace = '
I_WHERE := I_WHERE || \' and L_CAY.CODE = CF_APPL_SEL_PRG.CA\';';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
print "\n\n##Customize the CLA Status poplists\n\n";
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Vals(4) := \'COM\';'.
'{;.}{;*}{;?}'.
'D_L_CLA_STATUS.NumOfVV := 4;';
$myReplace = 'D_L_CLA_STATUS.NumOfVV := 3;';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_prg.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Vals(4) := \'COM\';'.
'{;.}{;*}{;?}'.
'D_L_CLA_STATUS.NumOfVV := 4;';
$myReplace = 'D_L_CLA_STATUS.NumOfVV := 3;';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_gab.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Vals(2) := \'RDY\';'.
'{;.}{;*}{;?}'.
'D_L_CLA_STATUS.Abbreviations(2) := \'RDY\';';
$myReplace = '';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_gab.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Vals(3) := \'SUB\';'.
'{;.}{;*}{;?}'.
'D_L_CLA_STATUS.Abbreviations(3) := \'SUB\';';
$myReplace = '';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
print "\n\n##Make module jump straight to control form display if only one record is found. \n\n";
$mySearch = '(--
Name: gbchklaw\$sel_apl_prg\.QueryList.*?)'
.'( WSGL\.DefaultPageCaption.*?<H1>Control
Form Selection</H1>.*? htp.para;)'
.'(.*? if I_ROWS_FETCHED != 0 then)';
$myReplace = '$1$3\n\n'
.' I_COUNT
:= QueryHits(\n'
.'
P_PENDING_OR_ALL,\n'
.'
P_COMPETING,\n'
.'
P_APPL_TYPE,\n'
.'
P_ACTIV_CODE,\n'
.'
P_ORG_CODE,\n'
.'
P_SERIAL_NUM,\n'
.'
U_SERIAL_NUM,\n'
.'
P_SUPPORT_YEAR,\n'
.'
U_SUPPORT_YEAR,\n'
.'
P_SUFFIX_CODE,\n'
.'
P_L_CAY_CODE,\n'
.'
P_L_POG_PD_NAME,\n'
.'
P_L_POG_ORG_ABBRV,\n'
.'
P_L_POG_ORG_DESC,\n'
.'
P_BOARD_DATE,\n'
.'
P_FY,\n'
.'
U_FY,\n'
.'
P_START_DATE,\n'
.'
U_START_DATE,\n'
.'
P_L_CLA_STATUS);\n\n'
#.'
P_SELECT_FOR_PAY);\n\n'
.' if
I_COUNT = 1 then\n'
.'
-- Jump staight to dispalying control form\n'
.'
dbms_sql.column_value(I_CURSOR, 1, CURR_VAL.FULL_GRANT_NUMBER);\n'
.'
dbms_sql.column_value(I_CURSOR, 3, NBT_VAL.L_CAY_CODE);\n'
.'
dbms_sql.column_value(I_CURSOR, 4, NBT_VAL.L_POG_ROLE_USAGE_CODE);\n'
.'
dbms_sql.column_value(I_CURSOR, 5, CURR_VAL.BOARD_DATE);\n'
.'
dbms_sql.column_value(I_CURSOR, 6, CURR_VAL.FMT_START_DATE);\n'
.'
dbms_sql.column_value(I_CURSOR, 7, NBT_VAL.L_CLA_STATUS);\n'
.'
dbms_sql.column_value(I_CURSOR, 8, NBT_VAL.L_CLA_CHECKED_DATE);\n'
.'
dbms_sql.column_value(I_CURSOR, 9, CURR_VAL.SCORE);\n'
.'
dbms_sql.column_value(I_CURSOR, 10, CURR_VAL.PERCENTILE);\n'
.'
dbms_sql.column_value(I_CURSOR, 12, curr_val.appl_id);\n'
.'
dbms_sql.column_value(I_CURSOR, 13, curr_val.appl_type);\n'
.'
dbms_sql.column_value(I_CURSOR, 14, curr_val.activ_code);\n'
.'
dbms_sql.column_value(I_CURSOR, 15, curr_val.cla_id);\n'
.' \n'
.'
gbchklaw\$.auto_bringup_cf(\n'
.'
P_APPL_ID=> curr_val.appl_id,\n'
.'
P_ACTIVITY_CODE=>curr_val.activ_code,\n'
.'
P_APPL_TYPE=>curr_val.appl_type,\n'
.'
P_FORM_TYPE=>\'PRG\',\n'
.'
P_FULL_GRANT_NUM=>curr_val.full_grant_number\n'
.'
);\n'
.'\n'
.'
return; \n\n'
.' end
if; -- if I_COUNT = 1 then\n\n'
.'--WebPC moved this code\n$2\n--WebPC';
&mSearchRep($mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_gab.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Vals(4)';
$myReplace = 'D_L_CLA_STATUS.Vals(2)';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_gab.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Meanings(4)';
$myReplace = 'D_L_CLA_STATUS.Meanings(2)';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_gab.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.Abbreviations(4)';
$myReplace = 'D_L_CLA_STATUS.Abbreviations(2)';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_gab.InitialiseDomain';
$myEnd = ' exception';
$mySearch = 'D_L_CLA_STATUS.NumOfVV := 4';
$myReplace = 'D_L_CLA_STATUS.NumOfVV := 2';
&mSimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
print "\n\n##Print little message about the
record count - context sensitive.\n\n";
$mySearch = '(htp.p\(WSGL\.MsgGetText\(110,WSGLM\.DSP110_RECORDS_N_M,.*?'.
'I_OF_TOTAL_TEXT\);)';
$myReplace = '$1\n'.
' if
i_next_but and (z_action is null or z_action != \'COUNT\') then
\n'.
'
htp.p(\'<b><font color="#000099">-- There are more records. Click
on button Count to know how many.</font></b>\');'.
' end
if;';
&mSearchRep($mySearch, $myReplace);
print "\n\n##Optimize the BuildSQL where clause a bit: knock 2 views out.\n\n";
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = 'NCI_GMS_PEOPLE_VW
L_NGE,';
$myReplace = '';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = 'NCI_GAB_ORGANIZATIONS_VW
L_GOS,';
$myReplace = '';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = 'L_NGE.NPN_FULL_NAME,';
$myReplace = 'CAR.NPN_FULL_NAME,';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = '
I_WHERE := I_WHERE || \' where L_NGE.ID (+) = CAR.GMS_NPN_ID\';';
$myReplace = '';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = '
I_WHERE := I_WHERE || \' and L_CLA.ID (+) = CAR.CLA_ID\';';
$myReplace = '
I_WHERE := I_WHERE || \' where L_CLA.ID (+) = CAR.CLA_ID\';';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = '
I_WHERE := I_WHERE || \' and L_GOS.ID (+) = CAR.GMS_ORG_ID\';';
$myReplace = '';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = 'L_NGE.NPN_FULL_NAME';
$myReplace = 'rtrim(CAR.NPN_FULL_NAME)';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = 'L_GOS.ABBREVIATION';
$myReplace = 'CAR.NON_ABBREVIATION';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
$myStart = '-- Name:
gbchklaw$sel_apl_rmc.BuildSQL';
$myEnd = '
exception';
$mySearch = 'L_GOS.DESCRIPTION';
$myReplace = 'CAR.NON_DESCRIPTION';
&mSimpleSearchRepRange ($myStart,
$myEnd, $mySearch, $myReplace);
print "\n\n##Redirect queries from cf_appl_selection_rmc_vw
to hdd_cf_appl_selection_rmc_vw\n\n";
$mySearch = '(CF_APPL_SELECTION_RMC_VW)';
$myReplace = 'hdd_$1';
&mSearchRep($mySearch, $myReplace);
$myStart = '^create or replace package gbchklaw$
is$';
$myEnd = '^end;$';
$mySearch = 'function get_gab_cla_id (p_appl_id
in number) return number;$';
$myReplace = 'function get_gab_cla_id (p_appl_id
in number) return number;'."\n".
'pragma restrict_references (get_gab_cla_id, WNDS, WNPS);';
&SimpleSearchRepRangeHdr($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '^create or replace package gbchklaw$
is$';
$myEnd = '^end;$';
$mySearch = 'function get_rmc_cla_id (p_appl_id
in number) return number;$';
$myReplace = 'function get_rmc_cla_id (p_appl_id
in number) return number;'."\n".
'pragma restrict_references (get_rmc_cla_id, WNDS, WNPS);';
&SimpleSearchRepRangeHdr($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '^create or replace package gbchklaw$
is$';
$myEnd = '^end;$';
$mySearch = 'function get_appl_npe_id (p_appl_id
in number) return number;$';
$myReplace = 'function get_appl_npe_id (p_appl_id
in number) return number;'."\n".
'pragma restrict_references (get_appl_npe_id, WNDS, WNPS);';
&SimpleSearchRepRangeHdr($myStart, $myEnd,
$mySearch, $myReplace);
$myStart = '^create or replace package gbchklaw$
is$';
$myEnd = '^end;$';
$mySearch = 'FUNCTION get_appl_gms_org_id
(p_appl_id IN NUMBER) RETURN NUMBER;$';
$myReplace = 'FUNCTION get_appl_gms_org_id
(p_appl_id IN NUMBER) RETURN NUMBER;'."\n".
'pragma restrict_references (get_appl_gms_org_id, WNDS, WNPS);';
&SimpleSearchRepRangeHdr($myStart, $myEnd,
$mySearch, $myReplace);
#Tweak the derivation of the name of the creator
of the control form
$myStart = '^--
Name: gbchklaw$checklist.FormInsert$';
$myEnd =
'^ exception$';
$mySearch = '^
FORM_VAL.L_NPN_PREP_NPN_FULL_NAME := \'get_username_fullname(user)\';$';
$myReplace = '
FORM_VAL.L_NPN_PREP_NPN_FULL_NAME := get_username_fullname(user);';
&SimpleSearchRepRange($myStart,
$myEnd, $mySearch, $myReplace);
#Removes the link to the other module component
at the top of the query form
$myStart = '^--
Name: gbchklaw$.firstpage$';
$myEnd =
'^ exception$';
$mySearch = '^
WSGL.NavLinks(WSGL.MENU_LONG, ';
$myReplace = '--
WSGL.NavLinks(WSGL.MENU_LONG, ';
&SimpleSearchRepRange($myStart,
$myEnd, $mySearch, $myReplace);
#Close the multi-record HTML form before Designer
opens the NEXT-PREVIOUS button form
$myStart = '^-- Name:
gbpdtrnw$pds.QueryList$';
$myEnd = '^ exception$';
$mySearch = '^
htp.formOpen(curl => \'gbpdtrnw$pds.querylist\', cattributes => \'NAME="frmZero"\');$';
$myReplace = '
WSGL.HiddenField(\'O_L_POG_PD_NAME\', P_L_POG_PD_NAME);'."\n".
' WSGL.HiddenField(\'O_L_POG_ORG_DESC\',
P_L_POG_ORG_DESC);'."\n".
' htp.p(\'</FORM>\');'."\n".
' htp.formOpen(curl => \'gbpdtrnw$pds.querylist\',
cattributes => \'NAME="frmZero"\');';
&SimpleSearchRepRange($myStart, $myEnd,
$mySearch, $myReplace);
#Adds check box header to the table of records
$myStart = '^-- Name:
gbpdtrnw$pds.QueryList$';
$myEnd = '^ exception$';
$mySearch = 'WSGL.LayoutHeader(35, \'LEFT\',
\'PD Name\');$';
$myReplace = 'WSGL.LayoutHeader(15, \'LEFT\',
\'Transfer?\');'."\n".
'
WSGL.LayoutHeader(35, \'LEFT\', \'PD Name\');';
&SimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
#Adds check boxes to the table of records
$myStart = '^-- Name:
gbpdtrnw$pds.QueryList$';
$myEnd = '^ exception$';
$mySearch = '^
WSGL.LayoutRowStart(\'TOP\');$';
$myReplace = '
WSGL.LayoutRowStart(\'TOP\');'."\n".
'
wsgl.layoutdata(\'<input type="checkbox" name="C\'||to_char(i_display_count)||\'">\'||'."\n".
'
\'<input type="hidden" name="appl_id_\'||to_char(i_display_count)||\'"
value="\'||to_char(CURR_VAL.APPL_ID)||\'">\'||'."\n".
'
\'<input type="hidden" name="npe_id_\'||to_char(i_display_count)||\'"
value="\'||to_char(CURR_VAL.NPE_ID)||\'">\');';
&SimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
#Add variable to count number of records displayed
$myStart = '^-- Name:
gbpdtrnw$pds.QueryList$';
$myEnd = '^ begin$';
$mySearch = '^
I_PREV_BUT boolean;$';
$myReplace = '
I_PREV_BUT boolean;'."\n".
' i_display_count
number(6) := 0;';
&SimpleSearchRepRange ($myStart, $myEnd,
$mySearch, $myReplace);
____________________________________________________________________________
Last Revised: March 30, 2000.