Description
AdditionalUpdatesCodeStart2
Parameters
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program6/bbankstateimport.p)
/* =============================================================================== */
/* Treat the records starting with '2' - Detail Lines */
/* =============================================================================== */
assign vdtotal = vdtotal + 1.
/* Case Item code based */
CASE SUBSTRING(vcDocLine,2,1,"CHARACTER":U):
WHEN '1':U
THEN DO:
ASSIGN vlCheck = FALSE.
/* BTS 4985 - JLA */
if vlImportCODADetailLines = ?
then assign vlImportCODADetailLines = false.
/* If you don't want to load detail lines within a bank statement line, */
/* skip these detail lines (<> "0000":U) */
if not vlImportCODADetailLines and
SUBSTRING(vcDocLine,7,4,"CHARACTER":U) <> "0000":U
then next.
/* If you do want to load detail lines within a bank statement line, */
/* skip these main total line (= "0000":U) when detail lines are found */
if vlImportCODADetailLines and
SUBSTRING(vcDocLine,7,4,"CHARACTER":U) = "0000":U and
SUBSTRING(vcDocLine,54,1,"CHARACTER":U) = "3":U
then do:
/* A detail line has an identical record-id and itemcode, */
/* a different detail number and a costcode starting with 8 */
if can-find(first tBankStateImportFileContent where
substring(tBankStateImportFileContent.tcBankStateFileContentLine,1,6,"CHARACTER":U) = substring(vcDocLine,1,6,"CHARACTER":U) and
substring(tBankStateImportFileContent.tcBankStateFileContentLine,7,4,"CHARACTER":U) <> substring(vcDocLine,7,4,"CHARACTER":U) and
substring(tBankStateImportFileContent.tcBankStateFileContentLine,54,1,"CHARACTER":U) = "8":U)
then next.
end.
/* To be sure we read only the correct detail lines (as tested above) */
/* we check the currect record again if it's a correct detail line */
if vlImportCODADetailLines and
SUBSTRING(vcDocLine,7,4,"CHARACTER":U) <> "0000":U and
SUBSTRING(vcDocLine,54,1,"CHARACTER":U) <> "8":U
then next.
/* BTS 4985 - JLA */
assign viCnt = viCnt + 1
vtPostingDate = date(SUBSTRING(vcDocLine, 118,2,"CHARACTER":U) +
SUBSTRING(vcDocLine, 116,2,"CHARACTER":U) +
SUBSTRING(vcDocLine, 120,2,"CHARACTER":U)).
if available tApiBankStateBSI
then assign vcBankStateNumber = tApiBankStateBSI.BankStateNumber.
/* Get the Posting Year/Period/Date based on the input date */
<Q-1 run PeriodByStartEndDate (first) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vtPostingDate, (Date)
output dataset tqPeriodByStartEndDate) in BPeriod >
find first tqPeriodByStartEndDate no-lock no-error.
if available tqPeriodByStartEndDate
then assign viPeriodID = tqPeriodByStartEndDate.tiPeriod_ID
viPeriodPeriod = tqPeriodByStartEndDate.tiPeriodPeriod
viPeriodYear = tqPeriodByStartEndDate.tiPeriodYear.
else do:
assign oiReturnStatus = -1
viPeriodID = ?
vcmsgReturn = substitute(trim(#T-7'&1 - No matching GL period found for posting date &2.':255(12344)T-7#), vcmsgLine, vtPostingDate).
<M-2 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-2983':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
/* Get the Currency Field */
<Q-3 run GLByGLIdMatchedCode (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (GLId)
input vcGLCode, (GLCode)
output dataset tqGLByGLIdMatchedCode) in BGL >
find first tqGLByGLIdMatchedCode no-lock no-error.
if available tqGLByGLIdMatchedCode and not tqGLByGLIdMatchedCode.tlGLIsLocalCurrency
THEN assign vcGLCurrencyCode = tqGLByGLIdMatchedCode.tcCurrencyCode
viGLCurrencyId = tqGLByGLIdMatchedCode.tiCurrency_ID.
else assign vcGLCurrencyCode = vcCompanyLC
viGLCurrencyId = viCompanyLCId.
/* Check Uniqueness */
/* use the year of the posting date, because it must not be in a posting period, it's just the calendar year */
assign viBankStateYear = year(vtPostingDate).
<Q-4 assign vlFcQueryRecordsAvailable = BankStateByYear (NoCache)
(input viCompanyId, (CompanyId)
input vcBankStateNumber, (BankStateNumber)
input tqGLByGLIdMatchedCode.tiGL_ID, (GLID)
input viBankStateYear, (BankStateYear)) in BBankEntry >
if vlFcQueryRecordsAvailable <> false
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-8'LINE &1 POSITION 3: Bank statement number &2 already exists for this period.':255(13892)T-8#),string(viLineCount - 1), vcBankStateNumber + '/':U + string(year(vtPostingDate))).
<M-6 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-2984':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
/* 0 = credit , but debit (+) for SC5 / 1 = debit , but credit(-) for SC5 */
assign vcSign = (IF SUBSTRING(vcDocLine, 32,1,"CHARACTER":U) = '0':U then '+':U else '-':U)
vcCostCode = SUBSTRING(vcDocLine, 54,8,"CHARACTER":U).
/* find the right Period using the postingdate */
assign vcBankStateLineDescr = trim(replace(SUBSTRING(vcDocLine,63,53,"CHARACTER":U),' ':U,' ':U)) + " ":U.
IF substring(vcDocLine,128,1,"CHARACTER":U) = "0":U AND vcBankStateLineDescr = "":U
then assign vcBankStateLineDescr = trim(substring(vcDocLine,11,13,"CHARACTER":U) + '/':U + substring(vcDocLine,24,8,"CHARACTER":U)).
/* If not description specified, we force one as it is a mandatory field */
if vcBankStateLineDescr = '':U
then assign vcBankStateLineDescr = "<X>":U.
create tApiBankStateLineBSI.
assign tApiBankStateLineBSI.tc_Rowid = string(viCnt + 1000000)
tApiBankStateLineBSI.tc_ParentRowid = tApiBankStateBSI.tc_Rowid
tApiBankStateLineBSI.Company_ID = viCompanyId
tApiBankStateLineBSI.BankStateLine_ID = viCnt + 1000000
tApiBankStateLineBSI.BankState_ID = viTempBankStateID
tApiBankStateLineBSI.Period_ID = viPeriodID
tApiBankStateLineBSI.BankStateLineDescription = substring(vcBankStateLineDescr,1,(if length(vcBankStateLineDescr,"CHARACTER":U) > 40 then 40 else length(vcBankStateLineDescr,"CHARACTER":U)),"CHARACTER":U)
tApiBankStateLineBSI.BankStateLineAmountTC = decimal(vcSign + SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000
tApiBankStateLineBSI.BankStateLineNumber = int(SUBSTRING(vcDocLine, 3,4,"CHARACTER":U))
tApiBankStateLineBSI.BankStateLineExtCostCode = vcCostCode
tApiBankStateLineBSI.BankStateLineStatus = {&BANKSTATELINESTATUS-UNALLOC}
tApiBankStateLineBSI.BankStateLineInOut = vcSign
tApiBankStateLineBSI.BankStateLineValueDate = date(SUBSTRING(vcDocLine, 50,2,"CHARACTER":U) + SUBSTRING(vcDocLine, 48,2,"CHARACTER":U) + SUBSTRING(vcDocLine, 52,2,"CHARACTER":U))
tApiBankStateLineBSI.BankStateLinePostingDate = vtPostingDate
tApiBankStateLineBSI.tiPeriodYear = viPeriodYear
tApiBankStateLineBSI.tiPeriodPeriod = viPeriodPeriod
tApiBankStateBSI.tiPeriodYear = viPeriodYear
tApiBankStateBSI.tiPeriodPeriod = viPeriodPeriod
tApiBankStateBSI.Currency_ID = viGLCurrencyId
tApiBankStateBSI.tcCurrencyCode = vcGLCurrencyCode
tApiBankStateBSI.tcGLCode = vcGLCode
tApiBankStateBSI.BankStateYear = viBankStateYear
tApiBankStateBSI.BankStatePostingDate = vtPostingDate.
IF SUBSTRING(vcDocLine, 32,1,"CHARACTER":U) = '0':U
THEN assign vdtotalcredits = vdtotalcredits + decimal(SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000.
ELSE assign vdtotaldebits = vdtotaldebits + decimal(SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000.
assign vdtotalMvmtsPerBalance = vdtotalMvmtsPerBalance + decimal(vcSign + SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000.
/* Structured Message - */
if substring(vcDocLine,62,4,"CHARACTER":U) = "1101":U
then assign tApiBankStateLineBSI.BankStateLineExtTSM = SUBSTRING(vcDocLine,66,12,"CHARACTER":U).
END. /* When 1 */
WHEN '2':U
THEN DO:
IF SUBSTRING(vcDocLine, 7,4,"CHARACTER":U) = "0000":U
THEN assign vcBankStateLineDescr = vcBankStateLineDescr + trim(replace(SUBSTRING(vcDocLine,11,53,"CHARACTER":U),' ':U,' ':U)) + " ":U
tApiBankStateLineBSI.BankStateLineDescription = substring(vcBankStateLineDescr,1,(if length(vcBankStateLineDescr,"CHARACTER":U) > 40 then 40 else length(vcBankStateLineDescr,"CHARACTER":U)),"CHARACTER":U)
tApiBankStateLineBSI.BankStateLineExtReference = SUBSTRING(vcDocLine, 64,26,"CHARACTER":U).
if decimal(vcSign + SUBSTRING(vcDocLine, 93, 15,"CHARACTER":U)) / 1000 <> 0
then assign tApiBankStateLineBSI.BankStateLineExtOriginalTC = decimal(vcSign + SUBSTRING(vcDocLine, 93, 15,"CHARACTER":U)) / 1000
tApiBankStateLineBSI.BankStateLineExtCurrency = SUBSTRING(vcDocLine, 90, 3,"CHARACTER":U).
END. /* When 2 */
WHEN '3':U
THEN DO:
IF SUBSTRING(vcDocLine, 7,4,"CHARACTER":U) = "0000":U
THEN do :
assign viziplength = 0.
/* if first 4 positions are numeric, we assume it it the ZIP */
if SUBSTRING(vcDocLine,100,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,100,1,"CHARACTER":U) <= "9":U and
SUBSTRING(vcDocLine,101,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,101,1,"CHARACTER":U) <= "9":U and
SUBSTRING(vcDocLine,102,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,102,1,"CHARACTER":U) <= "9":U and
SUBSTRING(vcDocLine,103,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,103,1,"CHARACTER":U) <= "9":U
then assign tApiBankStateLineBSI.BankStateLineExtZip = SUBSTRING(vcDocLine,100,4,"CHARACTER":U)
viziplength = if SUBSTRING(vcDocLine,103,1,"CHARACTER":U) = " ":U
then 5
else 4.
assign tApiBankStateLineBSI.BankStateLineExtBankNumber = if decimal(SUBSTRING(vcDocLine, 11,12,"CHARACTER":U)) > 0 then SUBSTRING(vcDocLine, 11,12,"CHARACTER":U) else "":U
tApiBankStateLineBSI.BankStateLineExtName = SUBSTRING(vcDocLine, 48,26,"CHARACTER":U)
tApiBankStateLineBSI.BankStateLineExtAddress = SUBSTRING(vcDocLine, 74,26,"CHARACTER":U)
tApiBankStateLineBSI.BankStateLineExtCity = SUBSTRING(vcDocLine,100 + viziplength ,26 - viziplength,"CHARACTER":U).
end.
if vcBankStateLineDescr = "":U or length(tApiBankStateLineBSI.BankStateLineDescription,"CHARACTER":U) < 40
then assign tApiBankStateLineBSI.BankStateLineDescription = tApiBankStateLineBSI.BankStateLineDescription + SUBSTRING(vcDocLine, 48,26,"CHARACTER":U).
if length(tApiBankStateLineBSI.BankStateLineDescription,"CHARACTER":U) > 40
then tApiBankStateLineBSI.BankStateLineDescription = substring(tApiBankStateLineBSI.BankStateLineDescription,1,40,"CHARACTER":U).
END. /* When 3 */
END CASE.