Examples of .pca files

You know, even I wrote this tool, I don't actually write my .pca files from scratch. I never remember the exact syntax and I'm too lazy to type anyway. Guess what I do when I generate a new web module from Designer and I need to start a new .pca file. I copy a previous file and I edit it. So I thought I would put online a few of my latest .pca files. That will show you what I actually do with WebPC, which constructs I use most often and how I document my files. I put the latest stuff first because as time goes I tend to find faster cooler ways of doing things.

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.