-- applicable_gross.sql		Applicable Gross

SET ECHO OFF

--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 

-- change log
-- 03/06/02 bknox created using UNIX script report (Report Generator)

SET TERMOUT OFF
@/home/common/gysbsec.sql ban_default_q
SET TERMOUT ON

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

SET SCAN ON
-- get user's printer specs -- begin

   !/bin/clear
   !sed "s/string/applicable_gross/g" getprinter.sql > applicable_gross.rpt
   START applicable_gross.rpt
   !rm -f applicable_gross.rpt
   !echo
   !echo "Formatted for:"
   !echo
   !echo "    Printer: "&&prnt_code
   !echo "Orientation: "&&prnt_form
   !echo " Line Count: "&&line_count
   !echo "All Margins: "&&prnt_margin" inch"
   SET PAGESIZE &&line_count

-- get user's printer specs -- end

!echo " Paper Size: Letter"
SET LINESIZE 128

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK OFF
SET HEADING ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT ON

-- SET NEWPAGE 0
-- SET ECHO OFF     Suppress Display of commands
-- SET FEEDBACK OFF Suppress Display of record counts
-- SET LINESIZE n   Set line length
-- SET PAGESIZE n   Set lines per page
-- SET SHOWMODE OFF Suppress Display of old and new setting of SQL*Plus system variables
-- SET NEWPAGE  0   Set no lines before TITLE and eject first page
-- SET TRIMSPOOL ON Trim trailing spaces from end of SPOOLed record
-- SET VERIFY   ON  Show each line of the file before and after substitution
-- SET TERMOUT  OFF Suppress Display of output

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

--DEBUG
-- SET ECHO ON
-- SET SHOWMODE ON
-- SET FEEDBACK ON
--DEBUG

-- use current date to get asofdate
COLUMN asofdate NOPRINT NEW_VALUE asofdate
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate FROM DUAL;

-- Accept run options from operator

!echo " "
!echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *"
!echo "Please Enter Parameters for the Applicable Gross Program"
!echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *"
!echo "Parameter(s):"
!echo
ACCEPT runyear CHAR PROMPT ' Enter Year -------------------------(YYYY)----> '

!echo " "
ACCEPT play_on CHAR   PROMPT ' To Cancel, press <Ctrl+c> or To Continue, press <Enter>---> '

-- Accept run options from operator

COLUMN runyear NOPRINT NEW_VALUE runyearvar;

SELECT '&runyear' runyear FROM DUAL;

-- Time and Date

COLUMN today  NOPRINT NEW_VALUE datevar FORMAT A18 TRUNC
COLUMN time   NOPRINT NEW_VALUE timevar FORMAT A11 TRUNC
SELECT TO_CHAR(SYSDATE,'fmMonth DD, yyyy') today,
       TO_CHAR(SYSDATE,'HH:MI:SS am') time FROM DUAL;

COLUMN asofdatealpha NOPRINT NEW_VALUE asofdatealphavar FORMAT A18 TRUNC
SELECT TO_CHAR(TO_DATE('&&asofdate', 'MM/DD/YYYY'), 'fmMonth DD, yyyy') asofdatealpha FROM DUAL;

COLUMN ces    NOPRINT NEW_VALUE cesvar
SELECT RTRIM(gubinst_name) ces FROM general.gubinst;

COLUMN coa    NOPRINT NEW_VALUE coavar
COLUMN ARK_APPL_GRS HEADING "ARK       |Appl_Gross" FORMAT 99,999,990.00MI
COLUMN FED_APPL_GRS HEADING "FED       |Appl_Gross" FORMAT 99,999,990.00MI
COLUMN DIFF         HEADING "Applicable|Gross Diff" FORMAT 99,999,990.00MI
COLUMN PIDM      NOPRINT 
COLUMN Sort_Last NOPRINT 

SET NUMWIDTH 10

TTITLE ON
BTITLE ON

SET TERMOUT OFF

--                                           COL 56 | IS CENTER
TTITLE LEFT 'Report applicable_gross'         COL 42 cesvar -
       SKIP 2 COL 48                        'Applicable Gross' -
       SKIP 1 LEFT 'Year: ' runyearvar  -
       SKIP 2

BTITLE LEFT SKIP 1 ' Run: ' datevar '  ' timevar COL 63 ' Page No.:  ' FORMAT 999 SQL.PNO SKIP 1

BREAK ON REPORT
COMPUTE SUM LABEL 'Total:' OF ARK_APPL_GRS ON REPORT
COMPUTE SUM LABEL 'Total:' OF FED_APPL_GRS ON REPORT
COMPUTE SUM LABEL 'Total:' OF DIFF ON REPORT

!rm -f applicable_gross.lst

!echo
!echo "applicable_gross report beginning"
!echo

set feedback on

SPOOL applicable_gross

SELECT
 Sort_Last, 
 Name,
 ARK_PIDM PIDM, 
 NVL(ARK_APPL_GRS,0) ARK_APPL_GRS,
 NVL(FED_APPL_GRS,0) FED_APPL_GRS,
 NVL(FED_APPL_GRS,0) - NVL(ARK_APPL_GRS,0) DIFF   -- Applicable Gross
  FROM
(        -- Arkansas Applicable Gross
SELECT   -- Created from TABLE PERDTOT  Employee Monthly Benefit / Deduction Totals Repeating Table
  PERDTOT_PIDM ARK_PIDM,
  SUM(NVL(PERDTOT_APPL_GRS,0)) ARK_APPL_GRS 
  FROM PERDTOT
WHERE PERDTOT_YEAR = '&&runyearvar'
  AND PERDTOT_BDCA_CODE = 'ARK'
GROUP BY PERDTOT_PIDM
),
(        -- Federal Applicable Gross 
SELECT   -- Created from TABLE PERDTOT  Employee Monthly Benefit / Deduction Totals Repeating Table
  PERDTOT_PIDM FED_PIDM,
  SUM(NVL(PERDTOT_APPL_GRS,0)) FED_APPL_GRS
  FROM PERDTOT
WHERE PERDTOT_YEAR = '&&runyearvar'
  AND PERDTOT_BDCA_CODE = 'FED'
GROUP BY PERDTOT_PIDM
),
(
SELECT   -- Created From Names Query
SPRIDEN_PIDM  NAME_PIDM,
SPRIDEN_ID      ID,
SPBPERS_SSN   SSN,
SUBSTR(DECODE(SPBPERS_NAME_PREFIX,'Mr',NULL,'Mr.',NULL,'Miss',NULL,'Mrs',NULL,'Mrs.',NULL,'Ms',NULL,'Ms.',NULL,NULL,NULL, SPBPERS_NAME_PREFIX||' ')||RTRIM(DECODE(NVL(SPBPERS_PREF_FIRST_NAME,'X'),'X',spriden_first_name,spbpers_pref_first_name))||' '||RTRIM(spriden_last_name),1,25) Name,
SUBSTR(RTRIM(spriden_last_name)||', '||RTRIM(spriden_first_name)||' '||RTRIM(spriden_mi),1,25) "Legal_Name",
SUBSTR(spriden_last_name,1,15) Sort_Last
  FROM SPRIDEN, SPBPERS
 WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+)
   AND SPRIDEN_CHANGE_IND IS NULL
   AND SPRIDEN_ENTITY_IND = 'P'
   AND (SPRIDEN_PIDM <  623 OR SPRIDEN_PIDM > 623)    -- Catastrophic Leave, Bank 
)
 WHERE ARK_PIDM (+) = NAME_PIDM
   AND FED_PIDM (+) = NAME_PIDM
   AND NOT (ARK_APPL_GRS = 0 AND FED_APPL_GRS = 0 AND FED_APPL_GRS - ARK_APPL_GRS = 0)
ORDER BY Sort_Last, Name;

SPOOL OFF

!chmod 666 applicable_gross.lst

TTITLE OFF
BTITLE OFF
CLEAR COLUMNS
CLEAR BREAK
CLEAR COMPUTE
SET NUMWIDTH 10
SET HEADING ON

!echo
!echo "applicable_gross report complete"
!echo

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';

SET TERMOUT OFF
@/home/common/gysbsec_reset.sql
SET TERMOUT ON