-- po_close.sql		Close the PO related Records for Open but Zero Encumbrance POs, 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

-- 08/17/99 bknox 

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 POs 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  po_close_start.sql
spool po_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
@po_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)---> '

SPOOL po_close.lst

SELECT 'Closing Open POs with Zero Balance' FROM DUAL;
!echo
!echo "Updating FGBENCD: Encumbrance Distribution Table"
update fgbencd
   set fgbencd_status='C'
 where fgbencd_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 = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Updating FPBPOHD: Purchase Order Header Table"
update fpbpohd
   set fpbpohd_closed_ind='Y'
 where fpbpohd_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 = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Updating FPRPODT: Purchase Order Detail GOODS Table"
update fprpodt
   set fprpodt_closed_ind='Y'
 where fprpodt_pohd_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 = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Updating FPRPODA: Purchase Order Accounting Detail Table"
update fprpoda
   set fprpoda_closed_ind='Y'
 where fprpoda_pohd_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 = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

!echo "Printing Log of POs 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 = 'P'
   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_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 = 'P'
   AND FGBENCH_STATUS_IND = 'O'
   AND FGBENCH_NUM = ENCP_NUM
   AND Remaining_Bal = 0));

SPOOL OFF

COMMIT;

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