-- open_po.sql		Open PO (Aging) Report by Established Date

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:) 

-- 05/28/99 bknox created
-- 08/17/99 bknox removed Fiscal Year Code hardcode

SET NEWPAGE 0
SET PAGESIZE 40

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

SET LINESIZE 256
SET TRIMSPOOL ON
     
-- 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

SET TERMOUT  ON

-- Accept run options from operator

-- use current date to get asofdate
COLUMN asofdate NOPRINT NEW_VALUE asofdate FORMAT A10 TRUNC 
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate 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;

-- 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,'Month DD, YYYY') today,
       TO_CHAR(SYSDATE,'HH:MI:SS am') time FROM DUAL;

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

COLUMN PIDM NOPRINT
COLUMN Vendor_Name   HEADING "Vendor Name"        FORMAT A45 TRUNC
COLUMN Sort_Date NOPRINT HEADING "Sort|Date"      FORMAT A14 TRUNC
COLUMN Est_Date      HEADING "Established|Date"   FORMAT A11 TRUNC
COLUMN Enc_Amt       HEADING "Encumbrance|Amount" FORMAT '999,999,999.99'
COLUMN Liq           HEADING "Liquidated"         FORMAT '999,999,999.99'
COLUMN Remaining_Bal HEADING "Remaining|Balance"  FORMAT '999,999,999.99'

BREAK ON REPORT

COMPUTE SUM LABEL 'Total:' OF Enc_Amt       ON REPORT
COMPUTE SUM LABEL 'Total:' OF Liq           ON REPORT
COMPUTE SUM LABEL 'Total:' OF Remaining_Bal ON REPORT

SET NUMWIDTH 10
--                                           COL 56 | IS CENTER
TTITLE LEFT 'Report open_po'                     COL 42 cesvar -
       SKIP 2 COL 44                     'Open Purchase Order Report' -
       SKIP 1 LEFT 'As of: ' asofdatealphavar -
       SKIP 2

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

SET TERMOUT OFF

!rm -f open_po.lst

!echo
!echo "open_po report beginning"
!echo

SPOOL open_po

SELECT FGBENCH_NUM          Document_Code,
       FGBENCH_VENDOR_PIDM  PIDM,
       spriden_last_name    Vendor_Name,
       Enc_Amt,
       Liq,
       Remaining_Bal,
       TO_CHAR(FGBENCH_ESTAB_DATE, 'YYYYMMDDHHMISS')  Sort_Date,
       TO_CHAR(FGBENCH_ESTAB_DATE, 'MM/DD/YYYY')      Est_Date
  FROM FGBENCH, SPRIDEN,
      (SELECT FGBENCP_NUM ENCP_NUM,
          SUM(NVL(FGBENCP_ORIG_ENCB_AMT, 0) +
              NVL(FGBENCP_SUM_ENCB_ADJT, 0)) Enc_Amt,
          SUM(NVL(FGBENCP_SUM_ENCB_LIQ, 0))  Liq,
          SUM(NVL(FGBENCP_ORIG_ENCB_AMT, 0) +
              NVL(FGBENCP_SUM_ENCB_ADJT, 0) +
              NVL(FGBENCP_SUM_ENCB_LIQ, 0))  Remaining_Bal
        FROM FGBENCP
--     WHERE FGBENCP_FSYR_CODE = '99'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND spriden_pidm = FGBENCH_VENDOR_PIDM
   AND spriden_change_ind IS NULL
 ORDER BY Sort_Date, Document_Code;

SPOOL OFF

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

!echo
!echo "open_po report complete"
!echo

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

SET TERMOUT ON