-- po_imaging.sql   Extract PO.pdf indexes for Document Imaging System

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

-- change log
-- 08/13/04 bknox Created
-- 08/20/04 bknox Changed Requisition Detail restrictions to match actual sent items:)

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

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK Off
SET HEADING OFF
SET LINESIZE 512
SET TRIMSPOOL ON
SET PAGESIZE 0          
SET NEWPAGE 0
SET VERIFY OFF
SET TERMOUT 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

COLUMN Sort_Name NOPRINT

TTITLE OFF
BTITLE OFF

CLEAR BREAK
CLEAR COMPUTE

SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0   
-- no page breaks
SET WRAP OFF
SET SPACE 0      
-- no space(s) between columns
SET TIME OFF
SET TIMING OFF   
-- prevent time stamp at end of report

SET FEEDBACK OFF

COLUMN FPBPOHD_CODE NOPRINT

SET LINESIZE 512
SET TRIMSPOOL ON


!echo
!echo "po_imaging extract beginning"
!echo

!rm -f po_imaging.csv
SPOOL po_imaging.csv

--SELECT '"PO#_FileName","POdate","VendorName","VendorID","TotalAmt","RequestorName","RequestorMail","ReqID","Requistion#","Buyer","ATTN_To"' FROM DUAL;

SELECT FPBPOHD_CODE,                           -- For Sort
  '"'||
       FPBPOHD_CODE||'.pdf'||'","'||           -- Document
       PO_date||'","'||
--     Vendor_PIDM||'","'||
       Vendor||'","'||                         -- Vendor Name
       VendorID||'","'||
       TOTAL_AMT||'","'||
--     GOBEACC_PIDM||'","'||
       NVL(FPBREQH_NAME,NVL(Name,''))||'","'|| -- Requestor i.e., Person Entering theReq  --  --GOBEACC_USERNAME,
       FPBPOHD_EMAIL_ADDR||'","'||             -- Requestor's email
--     NVL(Name,'')||'","'||                   -- Requestor from email_pidm lookup
       NVL(ID,'')||'","'||                     -- From email_pidm lookup
       NVL(Requisition,'')||'","'||
--     FPBPOHD_BUYR_CODE||'","'||
       FTVBUYR_NAME||'","'||
     FPBPOHD_ATTENTION_TO
||'"'
  FROM
(SELECT -- Purchase Order Header Table 
       FPBPOHD_CODE FPBPOHD_CODE, FPBPOHD_EMAIL_ADDR FPBPOHD_EMAIL_ADDR,
       TO_CHAR(FPBPOHD_PO_DATE, 'MM/DD/YY')  PO_date,
       FPBPOHD_VEND_PIDM                     Vendor_PIDM,
       FPBPOHD_BUYR_CODE                     FPBPOHD_BUYR_CODE,
       FPBPOHD_ATTENTION_TO                  FPBPOHD_ATTENTION_TO
--     DECODE(FPBPOHD_CLOSED_IND,'C','Closed','Open') closed
  FROM FPBPOHD 
 WHERE FPBPOHD_CHANGE_SEQ_NUM IS NULL
   AND TRUNC(FPBPOHD_PO_PRINTED_DATE) = TRUNC(SYSDATE)
-- AND FPBPOHD_CLOSED_IND != 'C'
),
(SELECT 
SPRIDEN_PIDM  VendorPIDM,
SPRIDEN_ID      VendorID,
SUBSTR(spriden_last_name,1,30)  Vendor  --Name
  FROM SPRIDEN
 WHERE SPRIDEN_CHANGE_IND IS NULL
),
(SELECT                       -- Requisitions Closed (for PO and Req Numbers)
        FPRREQD_REQH_CODE Requisition,    -- Requisition Code from FPRREQD: Request Detail Table
        FPRREQD_POHD_CODE FPRREQD_POHD_CODE,             -- PO on which included this Item
        FPBREQH_USER_ID   Requestor,       -- Requestor from FPBREQH: Request Header Table
        FPBREQH_NAME,
        FPBREQH_EMAIL_ADDR,
        FPRREQD_BUYR_CODE,
        FPBREQH_ATTENTION_TO 
   FROM FPRREQD,
(SELECT -- Requisitions Converted (Closed) to POs Yesterday
--      FPBREQH_CLOSED_DATE,  
        FPBREQH_CODE    FPBREQH_CODE,  
        FPBREQH_USER_ID FPBREQH_USER_ID, FPBREQH_NAME FPBREQH_NAME, FPBREQH_EMAIL_ADDR FPBREQH_EMAIL_ADDR, FPBREQH_ATTENTION_TO FPBREQH_ATTENTION_TO,
        FPBREQH_VEND_PIDM 
   FROM FPBREQH
--WHERE FPBREQH_COMPLETE_IND = 'Y'
--  AND FPBREQH_APPR_IND = 'Y'
)
  WHERE FPRREQD_REQH_CODE    = FPBREQH_CODE
    AND FPRREQD_POHD_CODE IS NOT NULL 
    AND NVL(FPRREQD_CANCEL_IND,'x') != 'Y' -- Requisition Cancelled     (note !Cancelled)
  GROUP BY FPRREQD_REQH_CODE, FPRREQD_POHD_CODE, FPBREQH_USER_ID, FPBREQH_NAME, FPBREQH_EMAIL_ADDR, FPRREQD_BUYR_CODE, FPBREQH_ATTENTION_TO
),
(SELECT   -- Created from TABLE FPRPODA  Purchase Order Accounting Detail Table
  FPRPODA_POHD_CODE FPRPODA_POHD_CODE, SUM((FPRPODA_AMT-FPRPODA_DISC_AMT+FPRPODA_TAX_AMT+FPRPODA_ADDL_CHRG_AMT)) TOTAL_AMT
  FROM FPRPODA
 WHERE  FPRPODA_FSYR_CODE > '03'
   AND FPRPODA_ACTIVITY_DATE  >  TO_DATE('06/30/'||'2004'||' 00:00:00','MM/DD/YYYY HH24:MI:SS')
 GROUP BY FPRPODA_POHD_CODE
),
(SELECT Name,ID, goremal_pidm Email_pidm, RTRIM(NVL(goremal_email_address,'bknox@uaex.edu')) Email_Address FROM goremal,
(SELECT 
SPRIDEN_PIDM  SPRIDEN_PIDM,
SPRIDEN_ID      ID,
DECODE(NVL(SPBPERS_PREF_FIRST_NAME,'X'),'X',spriden_first_name,spbpers_pref_first_name)||' '||RTRIM(spriden_last_name) Name
  FROM SPRIDEN, SPBPERS --, PEBEMPL
 WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+)
   AND SPRIDEN_CHANGE_IND IS NULL
-- AND SPRIDEN_ENTITY_IND = 'P'
   AND (SPRIDEN_PIDM <  623 OR SPRIDEN_PIDM > 623)    -- Catastrophic Leave, Bank 
)
 WHERE GOREMAL_STATUS_IND = 'A'
   AND GOREMAL_PREFERRED_IND = 'Y' 
   AND SPRIDEN_PIDM = goremal_pidm
),
--(SELECT GOBEACC_PIDM, GOBEACC_USERNAME
--  FROM GOBEACC),
(SELECT FTVBUYR_CODE, FTVBUYR_NAME
   FROM FTVBUYR
--WHERE FTVBUYR_TERM_DATE > SYSDATE OR FTVBUYR_TERM_DATE IS NULL
)
WHERE FPRPODA_POHD_CODE = FPBPOHD_CODE  -- PO (+)
  AND FPBPOHD_BUYR_CODE = FTVBUYR_CODE
--AND GOBEACC_USERNAME = Requestor
--AND SPRIDEN_PIDM = GOBEACC_PIDM
  AND FPBPOHD_EMAIL_ADDR = Email_Address (+)
  AND FPRPODA_POHD_CODE = FPRREQD_POHD_CODE (+) 
  AND VendorPIDM = Vendor_PIDM
ORDER BY FPBPOHD_CODE;

SPOOL OFF

-- -------------------------------------------- create transmission file above

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET NUMWIDTH 10
SET HEADING ON
SET SPACE 1

!echo
!echo "po_imaging extract complete"
!echo

SET FEEDBACK ON
SET VERIFY OFF

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

-- SET SHOWMODE ON
SET TERMOUT ON
-- SET ECHO ON