Group,
For anyone that is interested this is what I did for an EDI file input
routine. Sits in a button on the form so some of the PROPERTY commands
relate to form objects.
Jan
-- Title: EDILoad.rmd
-- Comment:
-- Created: Nov 2007
-- Author: Jan D. Johansen
-- Copyright © 2007 Dane Data Systems
-- Last Update:
-- Uses Forms:
-- Uses Reports:
CLEAR VAR
vFileName,vTotalCheckNbr,vTotalCheckPay,vTotalCheckDate,v820ActualTotal
RECALC VARIABLES
SET ERROR MESSAGE 2038 OFF
DROP TABLE tEDILoad
DROP TABLE tEDITable
DROP TABLE tEDITable820
DROP TABLE tscanfile
SET ERROR MESSAGE 2038 ON
PROPERTY EDI820List REFRESHLIST TRUE
PROPERTY CSVList REFRESHLIST TRUE
SET VAR vTotalEDISales = NULL
SET VAR vTotalCheckPay = NULL
SET VAR vTotalCheckNbr = NULL
SET VAR vTotalCheckDate = NULL
SET VAR v820ActualTotal = NULL
SET VAR vEDIStartDate = NULL
SET VAR vEDIEndDate = NULL
SET VAR vFileName TEXT = NULL
RECALC VARIABLES
CLEAR VAR vRowCount,vTotalRows,vRowData,vSGETRowType,vTransDateTEXT, +
vTransDateINT,vTransDate,vRowUPC,vRowPriceTxt,vRowPrice,vRowQTYTxt, +
vRowQTY,vRowStore,vTransMONTxt,vTransDAYTxt,vTransYEARTxt,vTransMON,vTransDAY,
+
vTransYEAR,vStoreSLOCPStart,vQtySLOCPStart,vQtySLOCPEnd,vRowEnd, +
vSLOCP1,vSDQStartSLOCP,vSLOCP1,vSLOCP2,vSLOCP3,vSLOCP4,vSLOCP5, +
vTotalAmountTxt,vTotalAmount,vEDIAmount1Txt,vEDIAmount2Txt,vEDIAmount1, +
vEDIAmount2,vCheckDateTEXT,vCheckYEARTxt,vCheckMONTxt,vCheckDAYTxt, +
vCheckDateINT,vCheckYEAR,vCheckMON,vCheckDAY,vCheckDate,vCheckNumber, +
vEDITransaction,vEDIPayment
SET VAR vRowCount INT = NULL
SET VAR vTotalRows INT = NULL
SET VAR vRowData TEXT = NULL
SET VAR vSGETRowType TEXT = NULL
SET VAR vTransDateTEXT TEXT = NULL
SET VAR vTransDateINT INT = NULL
SET VAR vTransDate DATE = NULL
SET VAR vRowUPC TEXT = NULL
SET VAR vRowPriceTxt TEXT = NULL
SET VAR vRowPrice CURR = NULL
SET VAR vRowQTYTxt TEXT = NULL
SET VAR vRowQTY INT = NULL
SET VAR vRowStore TEXT = NULL
SET VAR vTransMONTxt TEXT = NULL
SET VAR vTransDAYTxt TEXT = NULL
SET VAR vTransYEARTxt TEXT = NULL
SET VAR vTransMON INT = NULL
SET VAR vTransDAY INT = NULL
SET VAR vTransYEAR INT = NULL
SET VAR vStoreSLOCPStart INT = NULL
SET VAR vQtySLOCPStart INT = NULL
SET VAR vQtySLOCPEnd INT = NULL
SET VAR vRowEnd INT = NULL
SET VAR vSLOCP1 INT = NULL
SET VAR vSLOCP2 INT = NULL
SET VAR vSLOCP3 INT = NULL
SET VAR vSLOCP4 INT = NULL
SET VAR vSLOCP5 INT = NULL
SET VAR vTotalAmountTxt TEXT = NULL
SET VAR vTotalAmount CURR = NULL
SET VAR vEDIAmount1Txt TEXT = NULL
SET VAR vEDIAmount2Txt TEXT = NULL
SET VAR vEDIAmount1 CURR = NULL
SET VAR vEDIAmount2 CURR = NULL
SET VAR vCheckDateTEXT TEXT = NULL
SET VAR vCheckYEARTxt TEXT = NULL
SET VAR vCheckMONTxt TEXT = NULL
SET VAR vCheckDAYTxt TEXT = NULL
SET VAR vCheckDateINT INT = NULL
SET VAR vCheckYEAR INT = NULL
SET VAR vCheckMON INT = NULL
SET VAR vCheckDAY INT = NULL
SET VAR vCheckDate DATE = NULL
SET VAR vCheckNumber TEXT = NULL
SET VAR vEDITransaction TEXT = NULL
SET VAR vEDIPayment TEXT = NULL
PLUGINS LoadFileNamePlus.RBL "vFileName +
|FULLPATH ON +
|TITLE Select File +
|VIEW_MODE DETAILS +
|FILTER EDI Text Files (*.txt)#*.txt"
IF vFileName CONT "[Esc]" THEN
RETURN
ENDIF
SET VAR vPauseMsg = +
((CHAR(009))+(CHAR(009))&"EDI File loading ..."+(CHAR(009))+(CHAR(013))+ +
(CHAR(013))+ +
(CHAR(009))+(CHAR(009))&"Please Stand By ..."+(CHAR(009)))
CLS
PAUSE 3 USING .vPauseMsg +
CAPTION " Patience is virtue ... " +
OPTION GAUGE_VISIBLE ON +
|GAUGE_COLOR GREEN +
|GAUGE_INTERVAL 10 +
|BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_NAME ARIAL +
|MESSAGE_FONT_COLOR RED +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_BOLD ON
-- Start Gauge
ProcessMessage
SET ERROR MESSAGE 2038 OFF
DROP TABLE tEDILoad
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE `tEDILoad` +
(`EDIRawLoad` TEXT (100))
COMMENT ON TABLE `tEDILoad` IS "Temporary Load Table for EDI Load"
LOAD tEDILoad FROM .vFileName AS FORMATTED USING EDIRawLoad 1 100
ALTER TABLE tEDILoad ADD EDIRow INTEGER
AUTONUM EDIRow IN tEDILoad USING 1 1 NUM
-- check for proper type
SELECT EDIRawLoad INTO vCheckRow INDIC ivCheckRow +
FROM tEDILoad WHERE EDIRow = 3
SET VAR vCheckVersion = (SGET(.vCheckRow,3,4))
IF vCheckVersion = "852" THEN
PAUSE 1 USING " Incorrect EDI format!" +
CAPTION " Oops..." +
ICON SERIOUS +
OPTION MESSAGE_FONT_NAME Arial +
|MESSAGE_FONT_SIZE 10 +
|BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_COLOR RED
SET VAR vFileName = NULL
GOTO Done
ENDIF
--check for loaded file
CLEAR VAR vCheckFileID
SET VAR vCheckFileID INT = NULL
SELECT EDIRawLoad INTO vCheckForFile INDIC ivCheckForFile +
FROM tEDILoad WHERE EDIRow = 2
SET VAR vCheckFileStart = (SLOCP(.vCheckForFile,"*",6))
SET VAR vCheckFileTxt = (SGET(.vCheckForFile,4,(.vCheckFileStart+1)))
SET VAR vCheckFileID = .vCheckFileTxt
SELECT (COUNT(LoadFileNbr)) INTO vCountFile INDIC ivCountFile +
FROM EDILoadTrans WHERE LoadFileNbr = .vCheckFileID
IF vCountFile > 0 THEN
PAUSE 1 USING " File already loaded!" +
CAPTION " Oops..." +
ICON SERIOUS +
OPTION MESSAGE_FONT_NAME Arial +
|MESSAGE_FONT_SIZE 10 +
|BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_COLOR RED
SET VAR vFileName = NULL
GOTO Done
ENDIF
LABEL EDI820 -- LABEL ED820
SET ERROR MESSAGE 2038 OFF
DROP TABLE tEDITable820
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE `tEDITable820` +
(`EDICheckDate` DATE, +
`EDITotalSale` CURRENCY, +
`EDICheckID` TEXT (20), +
`EDITransDate` DATE, +
`EDIEntity` TEXT (15), +
`EDIPayment` TEXT (12), +
`EDIAmount1` CURRENCY, +
`EDIAmount2` CURRENCY, +
`EDIFMStore`=(SGET(EDIEntity,5,4)) TEXT (5) )
COMMENT ON TABLE `tEDITable820` IS "Temporary Data Table for EDI 820 Load"
SELECT COUNT(*) INTO vTotalRows INDIC ivTotalRows FROM tEDILoad
SET VAR vRowCount = 1
SET WHILEOPT OFF
WHILE vRowCount < .vTotalRows THEN
SELECT EDIRawLoad INTO vRowData INDIC ivRowData +
FROM tEDILoad WHERE EDIRow = .vRowCount
SET VAR vSGETRowType = (SGET(.vRowData,3,1))
IF vSGETRowType = "GS*" THEN
SET VAR vSLOCP1 = (SLOCP(.vRowData,"*",4))
SET VAR vSLOCP2 = (SLOCP(.vRowData,"*",5))
SET VAR vCheckDateTEXT = +
(SGET(.vRowData,(.vSLOCP2-(.vSLOCP1 + 1)),(.vSLOCP1 + 1)))
SET VAR vCheckYEARTxt = (SGET(.vCheckDateTEXT,4,1))
SET VAR vCheckMONTxt = (SGET(.vCheckDateTEXT,2,5))
SET VAR vCheckDAYTxt = (SGET(.vCheckDateTEXT,2,7))
SET VAR vCheckDateINT = .vCheckDateTEXT
SET VAR vCheckYEAR = .vCheckYEARTxt
SET VAR vCheckMON = .vCheckMONTxt
SET VAR vCheckDAY = .vCheckDAYTxt
SET VAR vCheckDate = (RDATE(.vCheckMON,.vCheckDAY,.vCheckYEAR))
SET VAR vSLOCP1 = 0
SET VAR vSLOCP2 = 0
ENDIF
IF vSGETRowType = "BPR" THEN
SET VAR vSLOCP1 = (SLOCP(.vRowData,"*",2))
SET VAR vSLOCP2 = (SLOCP(.vRowData,"*",3))
SET VAR vTotalAmountTxt = +
(SGET(.vRowData,(.vSLOCP2-(.vSLOCP1 + 1)),(.vSLOCP1 + 1)))
SET VAR vTotalAmount = .vTotalAmountTxt
SET VAR vSLOCP1 = 0
SET VAR vSLOCP2 = 0
ENDIF
IF vSGETRowType = "TRN" THEN
SET VAR vSLOCP1 = (SLOCP(.vRowData,"*",2))
SET VAR vSLOCP2 = (SLOC(.vRowData,"~"))
SET VAR vCheckNumber = +
(SGET(.vRowData,(.vSLOCP2-(.vSLOCP1 + 1)),(.vSLOCP1 + 1)))
SET VAR vSLOCP1 = 0
SET VAR vSLOCP2 = 0
ENDIF
IF vSGETRowType = "NM1" THEN
SET VAR vSLOCP1 = (SLOCP(.vRowData,"*",9))
SET VAR vSLOCP2 = (SLOC(.vRowData,"~"))
SET VAR vEDIEntity = +
(SGET(.vRowData,(.vSLOCP2-(.vSLOCP1 + 1)),(.vSLOCP1 + 1)))
SET VAR vSLOCP1 = 0
SET VAR vSLOCP2 = 0
ENDIF
IF vSGETRowType = "RMR" THEN
SET VAR vSLOCP1 = (SLOCP(.vRowData,"*",2))
SET VAR vSLOCP2 = (SLOCP(.vRowData,"*",3))
SET VAR vSLOCP3 = (SLOCP(.vRowData,"*",4))
SET VAR vSLOCP4 = (SLOCP(.vRowData,"*",5))
SET VAR vSLOCP5 = (SLOC(.vRowData,"~"))
SET VAR vEDIPayment = +
(SGET(.vRowData,(.vSLOCP2-(.vSLOCP1 + 1)),(.vSLOCP1 + 1)))
SET VAR vEDIAmount1Txt = +
(SGET(.vRowData,(.vSLOCP4-(.vSLOCP3 + 1)),(.vSLOCP3 + 1)))
SET VAR vEDIAmount2Txt = +
(SGET(.vRowData,(.vSLOCP5-(.vSLOCP4 + 1)),(.vSLOCP4 + 1)))
SET VAR vEDIAmount1 = .vEDIAmount1Txt
SET VAR vEDIAmount2 = .vEDIAmount2Txt
SET VAR vSLOCP1 = 0
SET VAR vSLOCP2 = 0
SET VAR vSLOCP3 = 0
SET VAR vSLOCP4 = 0
SET VAR vSLOCP5 = 0
ENDIF
IF vSGETRowType = "DTM" THEN
SET VAR vDTMRow = (SGET(.vRowData,3,5))
IF vDTMRow = "003" THEN
SET VAR vTransDateTEXT = (SGET(.vRowData,8,9))
SET VAR vTransYEARTxt = (SGET(.vTransDateTEXT,4,1))
SET VAR vTransMONTxt = (SGET(.vTransDateTEXT,2,5))
SET VAR vTransDAYTxt = (SGET(.vTransDateTEXT,2,7))
SET VAR vTransDateINT = .vTransDateTEXT
SET VAR vTransYEAR = .vTransYEARTxt
SET VAR vTransMON = .vTransMONTxt
SET VAR vTransDAY = .vTransDAYTxt
SET VAR vTransDate = (RDATE(.vTransMON,.vTransDAY,.vTransYEAR))
INSERT INTO tEDITable820 +
(EDICheckDate,EDITotalSale,EDICheckID,EDITransDate, +
EDIEntity,EDIPayment,EDIAmount1,EDIAmount2) +
VALUES +
(.vCheckDate,.vTotalAmount,.vCheckNumber,.vTransDate, +
.vEDIEntity,.vEDIPayment,.vEDIAmount1,.vEDIAmount2)
ENDIF
ENDIF
LABEL DoNext
SET VAR vRowCount = .vRowCount + 1
ENDWHILE
SET WHILEOPT ON
SELECT EDITotalSale,EDICheckID,EDICheckDate INTO +
vTotalCheckPay INDIC ivTotalCheckPay, +
vTotalCheckNbr INDIC ivTotalCheckNbr, +
vTotalCheckDate INDIC ivTotalCheckDate +
FROM tEDITable820 WHERE COUNT = 1
--override vtotalcheckdate to today's date
SET VAR vTotalCheckDate = .#DATE
SELECT (SUM(EDIAmount2)) INTO v820ActualTotal INDIC iv820ActualTotal +
FROM tEDITable820
PROPERTY EDIPages ACTIVEPAGEINDEX 0
PROPERTY CSVPage ENABLED FALSE
PROPERTY EDI820List REFRESHLIST TRUE
PROPERTY SendPayment ENABLED TRUE
LABEL Done
RECALC VARIABLES
CLS
RETURN
