-- taxrpt.sql Sales and Use Tax Report
--**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:) 

-- 06/17/99 bjulian created using UNIX script report (Report Generator)
-- 04/21/03 bknox changed FTVTRAT dates selection to be determined by fabinvh_trans_date
-- 01/09/08 bknox added taxrpt_rebate for County and City Sales Tax Rebates (changed law - Renee)
-- 02/18/08 bknox changed taxrpt_rebate to exclude vendors that do not collect taxes FTVVEND_COLLECT_TAX != 'N'
-- 02/18/08 bknox removed  AND farintx_pay_tax_to = 'T'
-- 02/18/08 bknox removed  AND farintx_rucl_code = 'TAXL'
-- 02/19/08 bknox replaced farintx based tax_rebate code with FARINVC based Query using the Publications Sales Tax Code

SET ECHO OFF

SET TERMOUT ON

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

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 128
SET PAGESIZE 43
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 frist page
-- 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

HOST echo " "
HOST echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *"
HOST echo "Please Enter Parameters for the Sales and Use Tax Report Program"
HOST echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *"
HOST echo "Parameter(s):"
HOST echo
ACCEPT begindate CHAR PROMPT ' Enter Begin Date -------------------------(MM/DD/YYYY)----> '
ACCEPT enddate   CHAR PROMPT ' Enter End Date   -------------------------(MM/DD/YYYY)----> '

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

-- -- Accept run options from operator

COLUMN begindate NOPRINT NEW_VALUE begindatevar;
COLUMN enddate   NOPRINT NEW_VALUE enddatevar;

SELECT TO_DATE('&&begindate','MM/DD/YYYY') begindate,
       TO_DATE('&&enddate','MM/DD/YYYY') enddate    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
SELECT 'Z' coa FROM DUAL;

COLUMN farintx_trat_code HEADING "Rate|Code"         FORMAT A8
COLUMN ftvtrat_desc      HEADING "Taxing Unit"       FORMAT A35
COLUMN ftvtrat_rate      HEADING "Tax|Rate"          FORMAT 999.999MI
COLUMN Tax_Amt           HEADING "Taxes|Due"         FORMAT 99,999,990.99MI
COLUMN Taxable           HEADING "Taxable|Purchases" FORMAT 999,999,990.99MI
COLUMN farintx_invh_code HEADING "Invoice|Number"    FORMAT A8

SET NUMWIDTH 10

TTITLE ON
BTITLE ON

BREAK ON REPORT ON farintx_trat_code SKIP 1

COMPUTE SUM LABEL 'Rate Cd:' OF Tax_Amt ON farintx_trat_code 
COMPUTE SUM LABEL 'Rate Cd:' OF Taxable ON farintx_trat_code 
COMPUTE SUM LABEL 'Total:'   OF Tax_Amt ON REPORT

SET TERMOUT OFF

--                                           COL 56 | IS CENTER
TTITLE LEFT 'Report taxrpt'         COL 42 cesvar -
       SKIP 2 COL 44                   'Sales and Use Tax Report' -
       SKIP 1 LEFT 'Begin Date: ' begindate -
       SKIP 1 LEFT '  End Date: ' enddate -
       SKIP 3

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

SPOOL taxrpt

SELECT farintx_trat_code,
       ftvtrat_desc,
       ftvtrat_rate,
       100*SUM(farintx_tax_amt)/ftvtrat_rate Taxable,
       SUM(farintx_tax_amt) Tax_Amt,
       farintx_invh_code
  FROM ftvtrat, farintx, fabinvh
 WHERE TRUNC(FTVTRAT_EFF_DATE) <= fabinvh_trans_date
   AND      (FTVTRAT_NCHG_DATE  > TRUNC(fabinvh_trans_date) OR FTVTRAT_NCHG_DATE IS NULL)
   AND      (FTVTRAT_TERM_DATE  > TRUNC(fabinvh_trans_date) OR FTVTRAT_TERM_DATE IS NULL)
   AND FTVTRAT_STATUS_IND = 'A'
   AND TRUNC(fabinvh_trans_date) BETWEEN '&&begindatevar' AND '&&enddatevar'
   AND fabinvh_complete_ind = 'Y'
   AND fabinvh_appr_ind = 'Y'
   AND fabinvh_cancel_ind != 'Y'
   AND ftvtrat_code = farintx_trat_code 
   AND fabinvh_code = farintx_invh_code
   AND farintx_pay_tax_to = 'T'
   AND farintx_rucl_code = 'TAXL'
 GROUP BY farintx_trat_code, farintx_invh_code, ftvtrat_desc, ftvtrat_rate;    

SPOOL OFF

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

HOST echo
HOST echo "taxrpt report complete"
HOST echo


COLUMN FTRTGTR_TRAT_CODE HEADING "Rate|Code"         FORMAT A8
COLUMN ftvtrat_desc      HEADING "Taxing Unit"       FORMAT A35
COLUMN ftvtrat_rate      HEADING "Tax|Rate"          FORMAT 999.999MI
COLUMN Tax_Amt           HEADING "Taxes|Due"         FORMAT 99,999,990.99MI
COLUMN Taxable           HEADING "Taxable|Purchases" FORMAT 999,999,990.99MI
COLUMN RebateOn          HEADING "RebateOn|Purchases" FORMAT 999,999,990.99MI
COLUMN Rebate_Amt        HEADING "Rebate|Amount"     FORMAT 99,999,990.99MI
COLUMN FARINVC_INVH_CODE HEADING "Invoice|Number"    FORMAT A8

SET NUMWIDTH 10

TTITLE ON
BTITLE ON

BREAK ON REPORT ON FTRTGTR_TRAT_CODE SKIP 1

COMPUTE SUM LABEL 'Rate Cd:' OF Tax_Amt ON FTRTGTR_TRAT_CODE 
COMPUTE SUM LABEL 'Rate Cd:' OF Taxable ON FTRTGTR_TRAT_CODE 
COMPUTE SUM LABEL 'Total:'   OF Tax_Amt ON REPORT
-- changes below
COMPUTE SUM LABEL 'Rate Cd:' OF Rebate_Amt ON FTRTGTR_TRAT_CODE 
COMPUTE SUM LABEL 'Rate Cd:' OF RebateOn ON FTRTGTR_TRAT_CODE
COMPUTE SUM LABEL 'Total:'   OF Rebate_Amt ON REPORT

SET TERMOUT OFF

--                                           COL 56 | IS CENTER
TTITLE LEFT 'Report taxrpt_rebate'         COL 42 cesvar -
       SKIP 2 COL 44                   'Sales and Use Tax Report' -
       SKIP 1 LEFT 'Begin Date: ' begindate -
       SKIP 1 LEFT '  End Date: ' enddate -
       SKIP 3

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

SPOOL taxrpt_rebate

SELECT 
  FTRTGTR_TRAT_CODE,     -- replaced farintx_trat_code,
  FTVTRAT_DESC,
  FTVTRAT_RATE,
  SUM(ROUND(FARINVC_APPR_QTY*FARINVC_APPR_UNIT_PRICE,2)) Taxable, --EXTENDED,
  CASE WHEN SUM(ROUND(FARINVC_APPR_QTY*FARINVC_APPR_UNIT_PRICE,2))> 2500 THEN 
  SUM(ROUND(FARINVC_APPR_QTY*FARINVC_APPR_UNIT_PRICE,2))-2500
  ELSE 0
  END RebateOn,
  ROUND(FTVTRAT_RATE*.01*CASE WHEN SUM(ROUND(FARINVC_APPR_QTY*FARINVC_APPR_UNIT_PRICE,2))> 2500 THEN 
  SUM(ROUND(FARINVC_APPR_QTY*FARINVC_APPR_UNIT_PRICE,2))-2500
  ELSE 0
  END,2) Rebate_Amt,
  FARINVC_INVH_CODE
 FROM FARINVC, FABINVH, FTVVEND,
(SELECT 
  FTVTGRP_TGRP_CODE,
  FTRTGTR_TRAT_CODE,
  FTVTRAT_DESC,
  FTVTRAT_RATE
FROM
(SELECT   -- Created from TABLE FTRTGTR  Tax group/Tax Rate repeating table
  FTRTGTR_TGTR_CODE,
  FTRTGTR_TRAT_CODE,
  TRUNC(FTRTGTR_EFF_DATE)              FTRTGTR_EFF_DATE,
  TRUNC(FTRTGTR_ACTIVITY_DATE)         FTRTGTR_ACTIVITY_DATE,
  FTRTGTR_USER_ID
  FROM FTRTGTR A
 WHERE TRUNC(A.FTRTGTR_EFF_DATE) <= SYSDATE
   AND ROWID IN 
       (SELECT ROWID
          FROM FTRTGTR B
         WHERE B.FTRTGTR_EFF_DATE = 
              (SELECT MAX(FTRTGTR_EFF_DATE)
                 FROM FTRTGTR C
                WHERE C.FTRTGTR_TGTR_CODE = B.FTRTGTR_TGTR_CODE --  AND C.FTRTGTR_TRAT_CODE = B.FTRTGTR_TRAT_CODE
               )
        )
),
(SELECT   -- Created from TABLE FTVTRAT  Tax Rate Table
  FTVTRAT_CODE,
  TRUNC(FTVTRAT_EFF_DATE)              FTVTRAT_EFF_DATE,
  TRUNC(FTVTRAT_TERM_DATE)             FTVTRAT_TERM_DATE,
  TRUNC(FTVTRAT_NCHG_DATE)             FTVTRAT_NCHG_DATE,
  TRUNC(FTVTRAT_ACTIVITY_DATE)         FTVTRAT_ACTIVITY_DATE,
  FTVTRAT_USER_ID,
  FTVTRAT_DESC,
  FTVTRAT_RATE,
  FTVTRAT_STATUS_IND,
  FTVTRAT_PIDM_TAXING_AUTHORITY,
  FTVTRAT_PAY_TAX_TO,
  FTVTRAT_COAS_CODE,
  FTVTRAT_FUND_CODE,
  FTVTRAT_ACCT_CODE,
  FTVTRAT_PRIORITY_CODE,
  FTVTRAT_ADDL_CHRG_IND,
  FTVTRAT_EXEMPT_PCT,
  FTVTRAT_AR_ACCT_CODE,
  FTVTRAT_AR_COAS_CODE,
  FTVTRAT_AR_FUND_CODE,
  FTVTRAT_INCLUDE_DISC_IND
  FROM FTVTRAT
 WHERE
       TRUNC(FTVTRAT_EFF_DATE) <= SYSDATE
   AND      (FTVTRAT_NCHG_DATE  > TRUNC(SYSDATE) OR FTVTRAT_NCHG_DATE IS NULL)
   AND      (FTVTRAT_TERM_DATE  > TRUNC(SYSDATE) OR FTVTRAT_TERM_DATE IS NULL)
   AND FTVTRAT_STATUS_IND = 'A'
   AND FTVTRAT_CODE != 'AR'
),
(SELECT   -- Created from TABLE FTVTGRP  Tax group validation table
  FTVTGRP_TGRP_CODE,
  TRUNC(FTVTGRP_EFF_DATE)              FTVTGRP_EFF_DATE,
  TRUNC(FTVTGRP_ACTIVITY_DATE)         FTVTGRP_ACTIVITY_DATE,
  FTVTGRP_USER_ID,
  TRUNC(FTVTGRP_NCHG_DATE)             FTVTGRP_NCHG_DATE,
  TRUNC(FTVTGRP_TERM_DATE)             FTVTGRP_TERM_DATE,
  FTVTGRP_TITLE,
  FTVTGRP_STATUS_IND,
  FTVTGRP_COAS_CODE_VALID,
  FTVTGRP_NON_TAXABLE
  FROM FTVTGRP
 WHERE
       TRUNC(FTVTGRP_EFF_DATE) <= SYSDATE
   AND      (FTVTGRP_NCHG_DATE  > TRUNC(SYSDATE) OR FTVTGRP_NCHG_DATE IS NULL)
   AND      (FTVTGRP_TERM_DATE  > TRUNC(SYSDATE) OR FTVTGRP_TERM_DATE IS NULL)
   AND FTVTGRP_STATUS_IND = 'A'
)
WHERE FTRTGTR_TGTR_CODE = FTVTGRP_TGRP_CODE
  AND FTRTGTR_TRAT_CODE = FTVTRAT_CODE
)
WHERE FARINVC_INVH_CODE = FABINVH_CODE
  AND FABINVH_VEND_PIDM = FTVVEND_PIDM
  AND FTVVEND_COLLECT_TAX != 'N'   --   'N' for 'No Taxes '
  AND FARINVC_TGRP_CODE   != 'NT'
  AND FTVTGRP_TGRP_CODE = FARINVC_TGRP_CODE
  AND TRUNC(fabinvh_trans_date) BETWEEN '&&begindatevar' AND '&&enddatevar'
GROUP BY FARINVC_INVH_CODE, 
         FARINVC_TGRP_CODE,
         FTVTGRP_TGRP_CODE,
         FTRTGTR_TRAT_CODE,
         FTVTRAT_DESC,
         FTVTRAT_RATE
HAVING SUM(ROUND(FARINVC_APPR_QTY*FARINVC_APPR_UNIT_PRICE,2))> 2500
ORDER BY FTRTGTR_TRAT_CODE
;
  

SPOOL OFF

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

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

SET TERMOUT ON