GpStructuredStorage file  7  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~sqli.vtd sqliForm.vtdsqliFormName.vtd. sqliStep.vtd1sqliStepFile.vtd8sqliStepStr.vtdKsqliStepStrEvent.vtd sqliVar.vtd sqliVer.vtdsqliReports.vtdsqliTranslation.vtdracKeyacNamed acIsDebugacType acVarInINI acAutoStart acAutoClose acEndMessageacLocalization2acFormNameAuthoracActive anVersion acLogType acInstCoded anUserChg anUserIns adTimeChg adTimeIns acIsLocalizedacRunOnInstall acRunOnUpdate acRunOnDelete anOldVersion acCryptPassacCompiledScriptacNote acAresLite acUseCompiled acAutoCompile100001SIzvoz Sepa v 4.0T0KYBPBFFFFFFF acKey acFormName2 acDocType acObjectName2acType acKeyShortCut2acTabSheetName2 anUserChg anUserIns adTimeChg adTimeIns acFormType acFormName2acName2 acKeyanNoacViewacSetOfacDescracReportacFileCreateTableanFileDelimiter acFileNameacFileTableName acFileDest acFileType acFileTypeXML acShowIfEmptyacTitleButtons anTitleLinesanBeginFromRecord anUserChg anUserIns adTimeChg adTimeIns acCharSet2 acStyleSheet acUnitNamed acNewThreadacDFMacPAS acSQLExport2acSQLExpacNoteacPCU acCompiled100001SGPFAENFKYBB windows-12500FJ.program AresProject; uses SysUtils, Classes, Forms, Dialogs, dlDatabase, Variants, DateUtils, dlGLcommon; function CurrDateTime() : string; var Year, Month, Day, Hour, Min, Sec, MSec: Word; begin DecodeDateTime(now, Year, Month, Day, Hour, Min, Sec, MSec); result := IntToStr(Year)+'-'+StrZero(Month,2)+'-'+StrZero(Day,2)+'T'+StrZero(Hour,2)+':'+StrZero(Min,2)+':'+StrZero(Sec,2); end; var XMLSepa : TdlXMLBase; dsPayOrd, dsOur : TdlDataSet; cHead, cDetail : string; cFileName, cDocType, cBankNo, cPayDate, cPoDatumuValute : string; nSumAmount : Currency; begin cHead := 'CstmrCdtTrfInitn.GrpHdr'; cDetail := 'CstmrCdtTrfInitn.PmtInf'; if not VarIsNull(Ares.Variables.AsString['FileName']) then cFileName:= Ares.Variables.AsString['FileName'] else cFileName := 'c:\sepa.xml'; // used when called from Ares IDE if not VarIsNull(Ares.Variables.asString['DocType']) then cDocType := Ares.Variables.asString['DocType'] else cDocType := 'LI00'; if not VarIsNull(Ares.Variables.AsString['BankNo']) then cBankNo := Ares.Variables.AsString['BankNo'] else cBankNo := '10'; if not VarIsNull(Ares.Variables.AsString['PayDate']) then cPayDate := StringReplace(Ares.Variables.AsString['PayDate'], '/', '-', [rfReplaceAll]) else cPayDate := '2010-01-10'; XMLSepa := TdlXMLBase.Create('1.0', 'windows-1250'); XMLSepa.AutoMultiply:=True; XMLSepa.DeclareNamespace('Document', '','urn:iso:std:iso:20022:tech:xsd:pain.001.001.03'); XMLSepa.DeclareNamespace('Document', 'xsi','http://www.w3.org/2001/XMLSchema-instance'); XMLSepa.ValueWrite('', 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03 ./pain.001.001.03.xsd', 'xsi:SchemaLocation'); dsOur := TdlDataSet.Create(nil); dsPayOrd := TdlDataSet.Create(nil); try dsPayOrd.sql.Text := 'select P.acSubject, P.acKey, (Case when PRM.acSubjName1 = ''T'' then P.acDebitor2 else T.acName2 end) as acDebitor2, (Case when PRM.acSubjName1 = ''T'' then P.acCreditor2 else S.acName2 end) as acCreditor2, P.anAmount, P.acCurrency, P.acPayPurpose2, P.acDebtorAcct1, P.acCreditorAcct1, P.adDateDue, ' + 'S.acAddress, S.acPost, PC.acName as acPlace, C.acISOCode, ' + 'B1.acIBANPrefix, B2.acIBANPrefix as acIBANPrefix2, ' + 'S1.acSWIFTCode, S2.acSWIFTCode as acSWIFTCode2, P.acUpnRFCode, '; if isOracle then begin dsPayOrd.sql.Add('P.acReferenceCred || case when P.acReferenceCred = ''RF'' then acrefcontrolcred else P.acCreditorAcct2 end || P.acCreditorAcct3 AS acSklicBreme, ' + 'P.acReferenceDebt || case when P.acReferenceDebt = ''RF'' then P.acrefcontroldebt else P.acDebtorAcct2 end || P.acDebtorAcct3 acSklicDobro, ' + 'rtrim(P.acCode1) || rtrim(P.acCode2) || rtrim(P.acCode3) AS acCode '); end else begin dsPayOrd.sql.Add('P.acReferenceCred + case when P.acReferenceCred = ''RF'' then acrefcontrolcred else P.acCreditorAcct2 end + P.acCreditorAcct3 as acSklicBreme, ' + 'P.acReferenceDebt + case when P.acReferenceDebt = ''RF'' then P.acrefcontroldebt else P.acDebtorAcct2 end + P.acDebtorAcct3 as acSklicDobro, ' + 'rtrim(P.acCode1)+rtrim(P.acCode2)+rtrim(P.acCode3) as acCode '); end; dsPayOrd.sql.Add('from tHE_PayOrder P ' + ' join tHE_SetSubj S on P.acSubject = S.acSubject ' + ' join tHE_SetSubj T on P.acDebitor2 = T.acSubject ' + ' left join tHE_SetPostCode PC on S.acPost = PC.acPost ' + ' left join tHE_SetCountry C on C.acCountry = S.acCountry ' + ' left join vHE_SetSubjBank B1 on B1.acBnkAcctNo1 = P.acDebtorAcct1 and B1.acSubject = P.acDebitor2 and B1.acType = ''D'' and B1.anNo = ' + cBankNo + ' ' + ' left join vHE_SetSubjBank B2 on B2.acBnkAcctNo1 = P.acCreditorAcct1 and B2.acSubject = P.acCreditor2 and B2.acType = ''D'' and B2.anNo = ' + cBankNo + ' ' + ' left join tHE_SetSubj S1 on S1.acSubject = B1.acBank ' + ' left join tHE_SetSubj S2 on S2.acSubject = B2.acBank ' + ' cross join tHE_SetParam PRM ' + 'where P.acStatus = ''P'' and P.acDocType = ' + QuotedStr(cDocType) + ' and P.anBankNo = ' + cBankNo); dsPayOrd.Open; dsOur.SQL.Text := 'select S.acAddress, S.acPost, PC.acName as acPlace, C.acISOCode, S.acCode ' + 'from the_SetSubj S ' + ' left join tHE_SetPostCode PC on S.acPost = PC.acPost ' + ' left join tHE_SetCountry C on C.acCountry = S.acCountry ' + 'where acSubject = ' + QuotedStr(dsPayOrd.FieldByName('acDebitor2').asString); dsOur.Open; if dsOur.Eof then begin dlMessageDlg('Our company not defined!', mtError, [mbOK], 0); exit; end; cPoDatumuValute := GetValueInTable('acFillSalePrice', 'tPA_SetDocType', 'acDocType=' + QuotedStr(cDocType)); if not dsPayOrd.Eof then begin nSumAmount := 0; while not dsPayOrd.Eof do begin nSumAmount := nSumAmount + dsPayOrd.FieldByName('anAmount').asCurrency; dsPayOrd.Next; end; dsPayOrd.First; XMLSepa.ValueWrite(cHead + '.MsgId', CurrDateTime()); // '2010-01-08T15:30:00' XMLSepa.ValueWrite(cHead + '.CreDtTm', CurrDateTime()); XMLSepa.ValueWrite(cHead + '.NbOfTxs', IntToStr(dsPayOrd.RecordCount)); // XMLSepa.ValueWrite(cHead + '.CtrlSum', StringReplace(FormatFloat('#0.00', nSumAmount), ',', '.', [rfReplaceAll])); // XMLSepa.ValueWrite(cHead + '.Grpg', 'SNGL'); XMLSepa.ValueWrite(cHead + '.InitgPty.Nm', dsPayOrd.FieldByName('acDebitor2').asString); //XMLSepa.ValueWrite(cHead + '.InitgPty.PstlAdr.AdrLine', dsOur.FieldByName('acAddress').asString); //XMLSepa.ValueWrite(cHead + '.InitgPty.PstlAdr.AdrLine', dsOur.FieldByName('acPost').asString + ' ' + dsOur.FieldByName('acPlace').asString); //XMLSepa.ValueWrite(cHead + '.InitgPty.PstlAdr.Ctry', dsOur.FieldByName('acISOCode').asString); XMLSepa.ValueWrite(cHead + '.InitgPty.Id.OrgId.Othr.Id', dsOur.FieldByName('acCode').asString); XMLSepa.ValueWrite(cHead + '.InitgPty.Id.OrgId.Othr.SchmeNm.Cd', 'TXID'); end; while (not dsPayOrd.eof) and (not dsOur.eof) do begin if dsPayOrd.FieldByName('acSWIFTCode').asString = '' then begin //dlMessageDlg('Payers SWIFT(BIC) code not entered!', mtError, [mbOK], 0); end; if dsPayOrd.FieldByName('acSWIFTCode2').asString = '' then begin //dlMessageDlg('Receivers SWIFT(BIC) code not entered!', mtError, [mbOK], 0); end; XMLSepa.ValueWrite(cDetail); XMLSepa.ValueWrite(cDetail + '.PmtInfId', dsPayOrd.FieldByName('acSklicDobro').asString); XMLSepa.ValueWrite(cDetail + '.PmtMtd', 'TRF'); XMLSepa.ValueWrite(cDetail + '.PmtTpInf.InstrPrty', 'NORM'); if Ares.Variables['PayOrdType'] = '3' then // UPN begin XMLSepa.ValueWrite(cDetail + '.PmtTpInf.SvcLvl.Cd', 'SEPA'); //UPN XMLSepa.ValueWrite(cDetail + '.PmtTpInf.LclInstrm.Prtry', 'SEPA'); //UPN end else begin XMLSepa.ValueWrite(cDetail + '.PmtTpInf.LclInstrm.Prtry', 'BN02'); //BN02 end; if cPoDatumuValute = 'T' then XMLSepa.ValueWrite(cDetail + '.ReqdExctnDt', FormatDateTime('yyyy-mm-dd', dsPayOrd.FieldByName('adDateDue').asDateTime)) else XMLSepa.ValueWrite(cDetail + '.ReqdExctnDt', cPayDate); XMLSepa.ValueWrite(cDetail + '.Dbtr.Nm', dsPayOrd.FieldByName('acSubject').asString); XMLSepa.ValueWrite(cDetail + '.Dbtr.PstlAdr.Ctry', dsOur.FieldByName('acISOCode').asString); XMLSepa.ValueWrite(cDetail + '.Dbtr.PstlAdr.AdrLine', dsOur.FieldByName('acAddress').asString); XMLSepa.ValueWrite(cDetail + '.Dbtr.PstlAdr.AdrLine', dsOur.FieldByName('acPost').asString + ' ' + dsOur.FieldByName('acPlace').asString); XMLSepa.ValueWrite(cDetail + '.DbtrAcct.Id.IBAN', 'SI56' + StringReplace(dsPayOrd.FieldByName('acDebtorAcct1').asString, '-', '', [rfReplaceAll])); // SI56 XMLSepa.ValueWrite(cDetail + '.DbtrAgt.FinInstnId.BIC', dsPayOrd.FieldByName('acSWIFTCode').asString); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.PmtId.InstrId', dsPayOrd.FieldByName('acKey').asString); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.PmtId.EndToEndId', dsPayOrd.FieldByName('acSklicDobro').asString); //XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.PmtTpInf.InstrPrty', 'NORM'); //XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.PmtTpInf.LclInstrm.Cd', 'BN02'); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Amt.InstdAmt', StringReplace(FormatFloat('#0.00', dsPayOrd.FieldByName('anAmount').asCurrency), ',', '.', [rfReplaceAll])); // Amount XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Amt.InstdAmt', dsPayOrd.FieldByName('acCurrency').asString, 'Ccy'); // Currency XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.ChrgBr', 'SLEV'); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.CdtrAgt.FinInstnId.BIC', dsPayOrd.FieldByName('acSWIFTCode2').asString); // BIC koda XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Cdtr.Nm', dsPayOrd.FieldByName('acCreditor2').asString); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Cdtr.PstlAdr.Ctry', dsPayOrd.FieldByName('acISOCode').asString); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Cdtr.PstlAdr.AdrLine', dsPayOrd.FieldByName('acAddress').asString); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Cdtr.PstlAdr.AdrLine', dsPayOrd.FieldByName('acPost').asString + ' ' + dsPayOrd.FieldByName('acPlace').asString); XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.CdtrAcct.Id.IBAN', 'SI56' + StringReplace(dsPayOrd.FieldByName('acCreditorAcct1').asString, '-', '', [rfReplaceAll])); // SI56 if Ares.Variables['PayOrdType'] = '3' then // UPN XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.Purp.Cd', dsPayOrd.FieldByName('acUpnRFCode').asString) else XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.RgltryRptg.Dtls.Inf', dsPayOrd.FieldByName('acCode').asString); // 'A3011' XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.RmtInf.Strd.CdtrRefInf.Tp.CdOrPrtry.Cd', 'SCOR'); // sklic breme //XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.RmtInf.Strd.CdtrRefInf.Ref', '/SIB/' +dsPayOrd.FieldByName('acSklicBreme').asString); // sklic breme XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.RmtInf.Strd.CdtrRefInf.Ref', dsPayOrd.FieldByName('acSklicBreme').asString); // sklic breme XMLSepa.ValueWrite(cDetail + '.CdtTrfTxInf.RmtInf.Strd.AddtlRmtInf', dsPayOrd.FieldByName('acPayPurpose2').asString); dsPayOrd.Next; end; finally dsPayOrd.Free; dsOur.Free; end; XMLSepa.SaveToFile(cFileName); dlMessageDlg(Format(cMsg0243, [cFileName]), mtInformation, [mbOK], 0); // 'Kreiranje datoteke konano (%s)!' XMLSepa.Free; DefDatabase.ExecSQL('update tHE_PayOrder set acStatus = ''O'', adDateOfAppr = ' + QuotedStr(cPayDate) + ' where acStatus = ''P'' and acDocType = ' +QuotedStr(cDocType)+ ' and anBankNo = ' +cBankNo, []); end. acKeyanNoanSeqNoacHField2acHType anHLengthanHDecacPField2acPTypeanPLegthanPDec acPFormat acPSeparator anUserChg anUserIns adTimeChg adTimeIns acKeyanNoanSeqNo acFieldName2 anFieldSize acFieldTitleacFieldReadOnly acEditType acFieldConst anUserChg anUserIns adTimeIns adTimeChg acFieldFormatacFieldDefault acLookupSQL acKeyanNo acDataEventacDataReturnValue acKeyAndNo anUserChg anUserIns adTimeChg adTimeIns acKey acWhereKeyanNo acFieldName acParamVar acFieldType acFieldREQacFieldCaption2 acDefaultExpacDefaultValue acLookUpSQL acOperator acVarHint2 acAddApostrof anUserChg anUserIns adTimeChg adTimeIns acPrtCrit ACKEY ANVERSIONANNOACSQLEXPACNOTE ADTIMECHG ANUSERCHG ADTIMEINS ANUSERINSACREPORTACLANGIDACLOCALIZATION2 ACFORMNAMEdACNAMEdACDEFAULTPRINTER ACPREVIEWANMAXPRSTLEVELACSUBSTITUTESIGNACPRINTIFEMPTY ACPRINTTOTEXT ANNOOFCOPY ACORDERBY ACREGUSERCODEACACTIVE ACKEYSHORTCUT2 ANUSERINS ADTIMEINS ANUSERCHG ADTIMECHG ACTEXT1ACTEXT2ACTEXT3ACNOTE ACREPORTDATAacKey acUnitName acObjectName acTranslationacLocalisation