-- po_flags.sql		List the Current Indicators and Status Flags for PO related Tables

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

-- bknox this is a pre-audit for the po_close.sql and po_open.sql

SET ECHO OFF
SET TERMOUT ON

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

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

!echo
!echo 'PO Flags List'
!echo
ACCEPT POnumber CHAR PROMPT ' Enter PO Number for Selection --------------(xxxxxxxx)------> '
ACCEPT FSYR_CODE CHAR PROMPT ' Enter FSYR_CODE for Selection --------------(xx)------------> '
!echo

SELECT '&&POnumber', DECODE(FPBPOHD_BLANKET_IND,'Y',' is a Blanket PO',' is Not a Blanket PO')
  FROM fpbpohd
 WHERE fpbpohd_code = '&&POnumber'
   AND FPBPOHD_CHANGE_SEQ_NUM IS NULL; 

SELECT 'Vendor: ',spriden_last_name
  FROM fpbpohd, spriden
 WHERE fpbpohd_code = '&&POnumber'
   AND FPBPOHD_CHANGE_SEQ_NUM IS NULL
   AND spriden_pidm = FPBPOHD_VEND_PIDM
   AND spriden_change_ind IS NULL;   

!echo
!echo "-----------------------------------------------------------------------"
SET FEEDBACK ON
!echo "FGBENCH: Encumbrance Ledger Header Table"
!echo "fgbench_status_ind - (C)losed and (O)pen"
SELECT 'fgbench_status_ind: ', NVL(fgbench_status_ind,'NULL')
  FROM fgbench
 WHERE fgbench_num = '&&POnumber';

!echo "-----------------------------------------------------------------------"
!echo "FGBENCD: Encumbrance Distribution Table"
!echo 'fgbencd_status'
SELECT 'fgbencd_status: ', NVL(fgbencd_status,'NULL'), ' Item Number: ', ltrim(to_char(FGBENCD_ITEM,'9999')), ' SEQ: ', ltrim(to_char(FGBENCD_SEQ_NUM,'999'))
  FROM fgbencd
 WHERE fgbencd_num = '&&POnumber'
   AND FGBENCD_FSYR_CODE = '&&FSYR_CODE';

!echo "-----------------------------------------------------------------------"
!echo "FPBPOHD: Purchase Order Header Table"
!echo fpbpohd_closed_ind
SELECT 'fpbpohd_closed_ind: ', NVL(fpbpohd_closed_ind,'NULL')
  FROM fpbpohd
 WHERE fpbpohd_code = '&&POnumber'
   AND FPBPOHD_CHANGE_SEQ_NUM IS NULL;

!echo "-----------------------------------------------------------------------"
!echo "FPRPODT: Purchase Order Detail GOODS Table"
!echo fprpodt_closed_ind 
SELECT 'fprpodt_closed_ind: ', NVL(fprpodt_closed_ind,'NULL'), ' Item Number: ', ltrim(to_char(FPRPODT_ITEM,'9999')), 'Blanket Balance: ', ltrim(to_char(FPRPODT_BO_REMAIN_BAL,'999990.99'))
  FROM fprpodt
 WHERE fprpodt_pohd_code = '&&POnumber'
--   AND FPRPODT_CANCEL_IND  IS NULL
--   AND FPRPODT_CANCEL_DATE IS NULL
   AND FPRPODT_CHANGE_SEQ_NUM IS NULL;
-- , 'Cancel Ind is: ', FPRPODT_CANCEL_IND, 'Cancel Date is: ', FPRPODT_CANCEL_DATE

!echo "-----------------------------------------------------------------------"
!echo "FPRPODA: Purchase Order Accounting Detail Table"
!echo fprpoda_closed_ind
SELECT 'fprpoda_closed_ind: ', NVL(fprpoda_closed_ind,'NULL'), ' Item Number: ', ltrim(to_char(FPRPODA_ITEM,'9999')), 'SEQ: ', ltrim(to_char(FPRPODA_SEQ_NUM,'999'))
  FROM fprpoda
 WHERE fprpoda_pohd_code = '&&POnumber'
   AND FPRPODA_FSYR_CODE = 'FSYR_CODE'
   AND FPRPODA_CHANGE_SEQ_NUM IS NULL;
!echo "-----------------------------------------------------------------------"

CLEAR COMPUTE

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

set termout on