-- requisition_close.sql  Close the Requisition Records in all related Tables for Zero Balance Encumbrances, by Fiscal Year 

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

-- 07/18/05 bknox created to Remove Zero'd but prior year(s) Requisitions

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK ON
SET HEADING ON
--SET LINESIZE 126 -- 156 Legal
SET LINESIZE 186
-- SET PAGESIZE 68 for Portrait or 53 for Landscape
-- SET PAGESIZE 68
SET PAGESIZE 53          
-- SET NEWPAGE 0
SET VERIFY OFF
-- 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

SET HEADING Off
SET TERMOUT ON
!echo
!echo "This Program will Close Open Requisitions with Zero Balances"
ACCEPT play_on CHAR   PROMPT 'Do you wish to Continue, press Y/y <Enter>---> '
!echo
SET TERMOUT OFF
COLUMN play_on NOPRINT NEW_VALUE play_on
SELECT '&&play_on' FROM DUAL;
!rm -f  requisition_close_start.sql
spool requisition_close_start.sql
select '!echo Cancelled by user' || chr(10) || '/*'
   from dual
   where not exists (select 'x' from DUAL WHERE '&&play_on' = 'Y' OR '&&play_on' = 'y');
spool off
!echo
@requisition_close_start.sql

SET TERMOUT ON

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET FEEDBACK OFF
SET HEADING Off

ACCEPT FSYR_CODE CHAR   PROMPT 'Enter Fiscal Year Code (YY)---> '

!rm -f requisition_close.lst

SPOOL requisition_close.lst

SELECT 'Closing Open Requisitions with Zero Balance' FROM DUAL;
!echo

SET FEEDBACK ON

!echo "Updating FGBENCD: Encumbrance Distribution Table"
update fgbencd
   set fgbencd_status='C'          -- fgbench_status_ind set to 'C' in final step below
 where fgbencd_num IN (SELECT Document_Code FROM (SELECT FGBENCH_NUM  Document_Code,
       Remaining_Bal
  FROM FGBENCH,                                           -- FGBENCH: Encumbrance Ledger Header Table
      (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                                      -- FGBENCP: Encumbrance Period Detail Table
       WHERE FGBENCP_FSYR_CODE = '&&FSYR_CODE'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'R'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Updating FGBENCH: Encumbrance Ledger Header Table"
update FGBENCH
   set FGBENCH_STATUS = 'C'
 where FGBENCH_NUM IN (SELECT Document_Code FROM (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 = '&&FSYR_CODE'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'R'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Updating FPRREQD: Request Detail Table"
update FPRREQD
   set FPRREQD_closed_ind='Y'
 where FPRREQD_REQH_CODE IN (SELECT Document_Code FROM (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 = '&&FSYR_CODE'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'R'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Updating FPRREQA: Request Accounting Table"
update FPRREQA
   set FPRREQA_closed_ind='Y'
 where FPRREQA_REQH_CODE IN (SELECT Document_Code FROM (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 = '&&FSYR_CODE'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'R'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

SET FEEDBACK OFF
!echo "Printing Log of Requisitions Closed"
SELECT 'Closing PO ', fgbench_num FROM fgbench
 where fgbench_num IN (SELECT Document_Code FROM (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 = '&&FSYR_CODE'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'R'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

SET FEEDBACK ON

!echo "Updating FGBENCH: Encumbrance Ledger Header Table"
update fgbench
   set fgbench_status_ind ='C'
--SELECT 'Found ', fgbench_num FROM fgbench
 where fgbench_num IN (SELECT Document_Code FROM (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 = '&&FSYR_CODE'
 GROUP BY FGBENCP_NUM)
 WHERE FGBENCH_TYPE = 'R'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

SPOOL OFF

COMMIT;

!chmod 666 requisition_close.lst

SET TERMOUT OFF
*/
!echo
!rm -f  requisition_close_start.sql
SET TERMOUT ON