-- po_check_zero.sql		List POs with Zero Remaining Balance

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

SET ECHO OFF

-- 08/17/99 bknox

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 128
SET VERIFY OFF
SET TERMOUT ON

-- !echo "FGBENCH_NUM          Remaining_Bal"

COLUMN FGBENCH_NUM   HEADING "FGBENCH_NUM" FORMAT A8
COLUMN Remaining_Bal HEADING "Remaining|Balance" FORMAT 9999999990.99MI

SELECT FGBENCH_NUM          Document_Code,
       Remaining_Bal
  FROM FGBENCH,
      (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 = '&&Fiscal_Year_Code'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0;