!***************************************************************************
!                                                                          *
!    Program ID: simple_convert.sqr             Creation Date: 11/28/2006  *
!        Author: Douglas Pace                                              *
!                                                                          *
!       Purpose: Loads a few vendor tables.  But this example doesn't      *
!                actually work since I only include two tables, and        *
!                on one of the tables, I only use a few of the real        *
!                columns.  You will have to do your own trace and get      *
!                the full SQL for your particular situation.  But this     *
!                provides a good template.                                 *
!                interface loader                                          *
!                                                                          *
!                Tables affected:                                          *
!                PRT_BUDGETS_STG    (selects from)                         *
!                PS_INTFC_PROJ_FUND (inserts into)                         *
!                                                                          *
!       Specification:                                                     *
!          Path:                                                           *
!          File:                                                           *
!                                                                          *
!                                                                          *
!***************************************************************************
!*                                                                         *
!* Modification History:                                                   *
!*                                                                         *
!* The table below summarizes changes to this SQR.                         *
!*                                                                         *
!* Date     Person            Description                                  *
!* -------- ----------------- ---------------------------------------------*
!* 11/28/06 Douglas Pace      Completed initial version                    *
!***************************************************************************

begin-program

  do Insert-Vndr-Addr-Scrol

  do Insert-Vendor-Invoice

end-program

!********************************************************************
!**  Procedure:  Insert-Vndr-Addr-Scrol
!**                                                                 *
!**  Purpose:    Insert one row into table Vndr-Addr-Scrol          *
!**                                                                 *
!********************************************************************
begin-procedure Insert-Vndr-Addr-Scrol
#debugF Show '+Insert-Vndr-Addr-Scrol'

let $SQL-STATEMENT = 'INSERT INTO PS_VNDR_ADDR_SCROL'
begin-sql ON-ERROR=SQL-Error
INSERT INTO PS_VNDR_ADDR_SCROL(SETID,
  VENDOR_ID,
  ADDRESS_SEQ_NUM,
  DESCR)
VALUES('MFG',    !  SETID
  '0000000029',    !  VENDOR_ID
  1,    !  ADDRESS_SEQ_NUM
  'description')    !  DESCR
  ;
end-sql

#debugF Show '-Insert-Vndr-Addr-Scrol'
end-procedure ! Insert-Vndr-Addr-Scrol



!********************************************************************
!**  Procedure:  Insert-Vendor-Invoice
!**                                                                 *
!**  Purpose:    Insert one row into table Vendor-Invoice           *
!**              Note - this procedure does not actually            *
!**              Insert into the table as delivered by PeopleSoft.  *
!**              I deleted tons of columns to make the code short.  *
!********************************************************************
begin-procedure Insert-Vendor-Invoice
#debugF Show '+Insert-Vendor-Invoice'

let $SQL-STATEMENT = 'INSERT INTO PS_VENDOR_INVOICE'
begin-sql ON-ERROR=SQL-Error
INSERT INTO PS_VENDOR_INVOICE(SETID,
  VENDOR_ID,
  EFFDT,
  EFF_STATUS)
VALUES('MFG',    !  SETID
  '0000000029',    !  VENDOR_ID
  '1998-07-02',    !  EFFDT
  'A')    !  EFF_STATUS
  ;
end-sql

#debugF Show '-Insert-Vendor-Invoice'
end-procedure ! Insert-Vendor-Invoice

!************************************************************************
!*  Procedure:    SQL-Error                                             *
!*                                                                      *
!*  Purpose:      This function will be called by any SQL statement     *
!*                that encounters an error during execution.            *
!*                                                                      *
!*                Set $SQL-STATEMENT prior to the execution of any      *
!*                SQL statement with descriptive text to help identify  *
!*                exactly which statement caused the error.  You may    *
!*                want to include specifics in this description, such   *
!*                as "INSERT with VENDOR_ID: 0000034"                   *
!************************************************************************
begin-procedure SQL-Error
#debugF Show '+SQL-Error'

  !set error variable to indicate problems occurred
  Move 'Y' to $Error_Flag

  !If debug flag, write the error out to the log file
  let $Msg = ' - SQL Statement = ' || $SQL-STATEMENT
  Show $Msg
  let $Msg = 'SQL Status = ' || edit(#sql-status, '99999')
  Show $Msg
  let $Msg = 'SQL Error  = ' || $sql-error
  Show $Msg

#debugF Show '-SQL-Error'
end-procedure !SQL-Error