--
-- Script: payroll_voucher_adjs.sql
--
-- Purpose: This is a recursive script (calls itself) that prompts for each
--    set of adjustment values to match against (year, pay ID, pay number,
--    sequence number, and possibly SSN(s)), until no more are entered, and
--    builds a character string list (adjs) of periods and optional SSN's in
--    the form of yyyyiinnnss######### (with SSN's) or yyyyiinnnss (without
--    SSN's), separated by semicolons, to match against during the payroll
--    voucher processing.
--
-- Note: The variable "adjs" must be declared before this script is called:
--    define adjs=';'
--
-- Author: Stephen Rea
-- Released: 7/7/04 
--
set heading off pagesize 0 verify off feedback off
undefine adjyear adjpayid adjpayno adjseqno adjssns adjyins adjnext
--
-- Get the year for the next set of adjustments, or blank if no more to enter.
--
column adjnext new_value adjnext
select decode(length('&adjs'),1,'first','next') adjnext from dual;
set termout on
prompt
accept adjyear char prompt 'Enter adjustment year for &adjnext set of adjustments, if any: '
set termout off
--
-- If there are no more adjustments (year is blank), execute a begin-comment-
-- block marker to bypass the rest of this script.
--
spool payvoudo.sql
select decode('&adjyear','','/*','') from dual;
spool off
@payvoudo.sql
--
-- Get the pay ID, pay number, sequence number and zero or more SSN's for
-- this set of adjustments.  (A blank SSN indicates all records for this
-- adjustment period will be used.)  Note: These and the rest of this script
-- will be bypassed if no adjustment year was entered.
--
set termout on
accept adjpayid char prompt 'Enter adjustment pay ID (must be 2 chars; required): '
accept adjpayno char prompt 'Enter adjustment pay number (up to 3 digits; required): '
accept adjseqno char prompt 'Enter adjustment sequence number (up to 2 digits; required): '
accept adjssns char prompt 'Enter adjustment SSN''s, separated by commas (optional): '
set termout off
--
-- Left pad and concatenate the four-digit year, upper-case pay ID, pay number,
-- and sequence number values in the form of yyyyiinnnss.
--
column adjyins new_value adjyins
select to_char(to_date('&adjyear','RRRR'),'RRRR') ||
   upper(lpad(decode('&adjpayid','','~~','&adjpayid'),2)) ||
   lpad(decode('&adjpayno','','~~~','&adjpayno'),3,'0') ||
   lpad(decode('&adjseqno','','~~','&adjseqno'),2,'0') adjyins from dual;
--
-- Append the one or more SSN's with their periods in the form of
-- yyyyiinnnss######### for each SSN or just yyyyiinnnss if no SSN's,
-- separated by semicolons, to the list of adjustment values to match.
--
column adjs new_value adjs
select '&adjs' || '&adjyins' || replace('&adjssns',',',';' || '&adjyins') || ';' adjs from dual;
--
-- If any of the required fields weren't entered, tell the user to re-enter
-- the entire set (there will just be an un-matchable set in the list, which
-- won't hurt anything).
--
spool payvoudo.sql
select decode(instr('&adjyins','~'),0,'','prompt' || chr(10) ||
   'prompt Missing a required value.  Please re-enter this set.') from dual;
spool off
set termout on
@payvoudo.sql
set termout off
--
-- Rerun this script to get the next adjustment set.  Note: This rerun and
-- the commands above will be bypassed if no adjustment year was entered.
--
@payroll_voucher_adjs.sql
--
-- Define the end-comment-block marker.  It is in a commented line to prevent
-- error messages about unmatched comment block markers, but it will be seen
-- and matched against if Oracle is looking for the end of the comment block.
--
-- */