Proposal summary
This proposal describes the HSBC German EFT Template creation with NetSuite. The Payment file templates are used to generate EFT in different formats.
Requirement
QIMA would like to create 2 EFT File Formats for HSBC German bank using XML format in NetSuite.
Our Solution
The requirement can be achieved by creating two EFT template using the XML format for all currencies based on the client’s requirement. Also need to create company bank details and entity bank details for the new Payment file templates. Then that templates needs to be tested by creating the vendor bills for the test vendor and need to do the bill processing for verification.
Field Mapping
DE SEPA XML v3 example.xml
<?xml version=”1.0″ encoding=”UTF-8″?>
<Document xmlns=”urn:iso:std:iso:20022:tech:xsd:pain.001.001.03″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xsi:schemaLocation=”urn:iso:std:iso:20022:tech:xsd:pain.001.001.03 file:///C:/Users/43820833/AppData/Local/Microsoft/Windows/Temporary%20Internet%20Files/Content.Outlook/A73TDDNM/pain_001_001_03_SEPACreditTransfer.xsd”>
<CstmrCdtTrfInitn>
<GrpHdr>
<MsgId>Payment file administration.ID</MsgId>
<CreDtTm>Payment file administration.TIMESTAMP</CreDtTm>
<NbOfTxs>Total number of payments</NbOfTxs>
<CtrlSum>Total amount of payments</CtrlSum>
<InitgPty>
<Nm>Company bank details.Statement Name</Nm>
<Id>
<OrgId>
<Othr>
<Id>Company Bank details. HSBC CUSTOMER CONNECT ID</Id>
</Othr>
</OrgId>
</Id>
</InitgPty>
</GrpHdr>
<PmtInf>
<PmtInfId>Payment file administration. REFERENCE NOTE</PmtInfId>
<PmtMtd>Static Value: “TRF”</PmtMtd>
<NbOfTxs>Total number of payments</NbOfTxs>
<CtrlSum>Total amount of payments</CtrlSum>
<PmtTpInf>
<SvcLvl>
<Cd>Static Value: “SEPA”</Cd>
</SvcLvl>
</PmtTpInf>
<ReqdExctnDt>Payment file administration. PROCESS DATE</ReqdExctnDt>
<Dbtr>
<Nm>company bank.Statement Name</Nm>
<PstlAdr>
<StrtNm>company bank.Street</StrtNm>
<PstCd>company bank.zip</PstCd>
<TwnNm>company bank.Town</TwnNm>
<CtrySubDvsn>company bank.state code</CtrySubDvsn>
<Ctry>company bank.countrycode</Ctry>
</PstlAdr>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>company bank.IBAN</IBAN>
</Id>
<Ccy>company bank.currency</Ccy>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>company bank .BIC</BIC>
<PstlAdr>
<Ctry>company bank.countrycode</Ctry>
</PstlAdr>
</FinInstnId>
</DbtrAgt>
<CdtTrfTxInf>
<PmtId>
<InstrId>Payment file administration.ID</InstrId>
<EndToEndId>Payment.tranID</EndToEndId>
</PmtId>
<Amt>
<InstdAmt Ccy=”EUR”>payment.amount</InstdAmt>
</Amt>
<ChrgBr>Static value: “SHAR”</ChrgBr>
<CdtrAgt>
<FinInstnId>
<BIC>Entity bank details.BIC</BIC>
<PstlAdr>
<Ctry>Entity bank details. country code</Ctry>
</PstlAdr>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>Entity bank details.name</Nm>
<PstlAdr>
<StrtNm>Entity bank details.Street</StrtNm>
<PstCd>Entity bank details.zip</PstCd>
<TwnNm>Entity bank details.Town</TwnNm>
<CtrySubDvsn>Entity bank details.state code</CtrySubDvsn>
<Ctry>Entity bank details.country code</Ctry>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>Entity bank details.IBAN</IBAN>
</Id>
</CdtrAcct>
<RmtInf>
<Ustrd>Payment. reference</Ustrd>
</RmtInf>
</CdtTrfTxInf>
</PmtInf>
</CstmrCdtTrfInitn>
</Document>
Priority Payment DE XML v3 sample file – 2 instructions in 1 batch.xml
<?xml version=”1.0″ encoding=”utf-8″?>
<Document xmlns=”urn:iso:std:iso:20022:tech:xsd:pain.001.001.03″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xsi:schemaLocation=”urn:iso:std:iso:20022:tech:xsd:pain.001.001.03 file:///Y:/Andrew%20Calero/schema/pain.001.001.03.xsd”>
<CstmrCdtTrfInitn>
<GrpHdr>
<MsgId>Payment file administration.ID</MsgId>
<CreDtTm>Payment file administration.TIMESTAMP</CreDtTm>
<NbOfTxs>Total number of payments</NbOfTxs>
<CtrlSum>Total amount of payments</CtrlSum>
<InitgPty>
<Nm>Company bank details.Statement Name</Nm>
<Id>
<OrgId>
<Othr>
<Id>Company Bank details. HSBC CUSTOMER CONNECT ID</Id>
</Othr>
</OrgId>
</Id>
</InitgPty>
</GrpHdr>
<PmtInf>
<PmtInfId>Payment file administration. REFERENCE NOTE</PmtInfId>
<PmtMtd>Static Value: “TRF”</PmtMtd>
<BtchBookg>Static value: “true”</BtchBookg>
<PmtTpInf>
<SvcLvl>
<Cd>Static Value: “URGP”</Cd>
</SvcLvl>
</PmtTpInf>
<ReqdExctnDt>Payment file administration. PROCESS DATE</ReqdExctnDt>
<Dbtr>
<Nm>company bank.Statement Name</Nm>
<PstlAdr>
<StrtNm>company bank.Street</StrtNm>
<PstCd>company bank.zip</PstCd>
<TwnNm>company bank.Town</TwnNm>
<CtrySubDvsn>company bank.state code</CtrySubDvsn>
<Ctry>company bank.countrycode</Ctry>
</PstlAdr>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>company bank.IBAN</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>company bank.BIC</BIC>
<PstlAdr>
<Ctry>company bank.countrycode</Ctry>
</PstlAdr>
</FinInstnId>
</DbtrAgt>
<CdtTrfTxInf>
<PmtId>
<InstrId>Payment file administration.ID</InstrId>
<EndToEndId>Payment.tranID</EndToEndId>
</PmtId>
<Amt>
<InstdAmt Ccy=”EUR”>payment.amount</InstdAmt>
</Amt>
<ChrgBr>Static value: “SHAR”</ChrgBr>
<CdtrAgt>
<FinInstnId>
<BIC>Entity bank details.BIC</BIC>
<PstlAdr>
<Ctry>Entity bank details. country code</Ctry>
</PstlAdr>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>Entity bank details.name</Nm>
<PstlAdr>
<StrtNm>Entity bank details.Street</StrtNm>
<PstCd>Entity bank details.zip</PstCd>
<TwnNm>Entity bank details.Town</TwnNm>
<CtrySubDvsn>Entity bank details.state code</CtrySubDvsn>
<Ctry>Entity bank details.country code</Ctry>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>Entity bank details.IBAN</IBAN>
</Id>
</CdtrAcct>
<RmtInf>
<Ustrd>Payment. reference</Ustrd>
</RmtInf>
</CdtTrfTxInf>
</PmtInf>
</CstmrCdtTrfInitn>
</Document>
Validation
| Field | Minimum Length | Maximum Length |
| Company Bank details. HSBC CUSTOMER CONNECT ID | 1 | 35 |
| Company Bank details. Statement Name | 1 | 140 |
| company bank. Street | 1 | 70 |
| company bank.zip | 1 | 16 |
| company bank. Town | 1 | 35 |
| company bank. IBAN | 1 | 34 |
| company bank. state code | 1 | 35 |
| company bank. Country code | 2 | 2 |
| company bank details. BIC | 6 | 10 |
| Entity bank details. BIC | 6 | 10 |
| Entity bank details.name | 1 | 140 |
| Entity bank details. Street | 1 | 70 |
| Entity bank details. IBAN | 1 | 34 |
| Entity bank details.zip | 1 | 16 |
| Entity bank details. Town | 1 | 35 |
| Entity bank details. state code | 1 | 35 |
| Entity bank details. country code | 2 | 2 |
| Entity bank details. Account number | 1 | 35 |
Assumptions
- We have added the field mapping of both Payment file templates and highlighted the corresponding NetSuite fields in Green color. We will create the two payment templates for all currencies in XML format based on field mapping.
- We will not provide any validation for fields other than the mentioned validations.
Risk
- J&J will not create the actual company bank details and vendor bank details in this scope. QIMA has to create company bank details and vendor bank details with Actual details.