SoftadminUtil.P27_Pain_001_001_03_Generate

Show allHide all
Type: Stored procedure

Generates pain.001.001.03 files.

Example

Handelsbanken
SELECT * INTO #P27Payment FROM SoftadminUtil.P27_Pain_Payment_Definition();

INSERT #P27Payment
(
	InstructionId,
	EndToEndId,
	Amount,
	Currency,
	PayDate,
	FromIban,
	FromBban,
	FromBankgiro,
	FromBic,
	FromName,
	FromId,
	ToIban,
	ToBban,
	ToClearingNumber,
	ToBankgiro,
	ToPlusGiro,
	ToName,
	InvoiceReference,
	OcrReference,
	UnstructuredReference,
	IsSalary,
	ServiceLevelCode,
	CategoryPurpose
)
VALUES
(
	'InstrId', -- Not mandatory. Only included here because the example file from Handelsbanken had it.
	'EndToEndId', -- Mandatory. Basically corresponds to PaymentId in the Bankgiro component. Used for identifying payments in reports (e.g. pain.002 and camt.0054).
	100,
	'SEK',
	CONVERT(date, '2021-10-31'),
	'SE1110000000000123456789',
	NULL,
	NULL,
	'HANDSESS', -- If FromIban or FromBban is used.
	'TESTCOMPANY', -- Not mandatory.
	'123456789', -- "Identification assigned by an institution."
	NULL,
	'12345678', -- Handelsbanken seems to use national account numbers here and a separate clearing number (in ToClearingNumber) whereas some other banks have all information in BBAN and no separate clearing number.
	'1234',
	NULL,
	NULL,
	'TESTCOMPANY', -- Full name of the creditor is mandatory in P27.
	NULL, -- Basically corresponds to Reference in the Bankgiro component, but indicates that it contains invoice numbers.
	NULL, -- Basically corresponds to Reference in the Bankgiro component, but indicates that it contains OCR numbers. Will be validated as OCR by the bank.
	N'123456', -- Basically corresponds to BankAccountText in the Bankgiro component.
	NULL,
	'NURG', -- Always 'NURG' for Handelsbanken.
	'SUPP' -- Always 'SUPP' for Handelsbanken unless it is a salary payment (where the procedure will automatically use 'SALA').
),
(
	'InstrId',
	'EndToEndId',
	100,
	'EUR',
	CONVERT(date, '2021-10-31'),
	'SE1110000000000123456789',
	NULL,
	NULL,
	'HANDSESS',
	'TESTCOMPANY',
	'123456789',
	NULL,
	'12345678',
	'1234',
	NULL,
	NULL,
	'TESTCOMPANY',
	NULL,
	NULL,
	N'123456',
	NULL,
	'NURG',
	'SUPP'
);

DECLARE
	@Varbinary varbinary(MAX),
	@Xml xml,
	@ErrorMessage nvarchar(MAX);

EXEC SoftadminUtil.P27_Pain_001_001_03_Generate
	@MessageId = 'Exempel Kontoinsättning', -- This id is for the whole file and is referenced in e.g. pain.002 files.
	@InitiatorId = '123456789', -- Handelsbanken: "Business organisation number or SHB number"
	@CreationDatetime = '2021-10-18T00:00:00', -- The procedure uses the current date/time if the parameter is omitted. The parameter can be used if a specific date/time is required, e.g. for testing.
	@OutputAsXml = 1, -- If 1, the file will be returned as xml in @XmlOut, which can be useful for troubleshooting. If 0 or if the parameter is omitted, the file will be returned as varbinary(max) with correct encoding and xml header for UTF-8.
	@VarbinaryOut = @Varbinary OUTPUT,
	@XmlOut = @Xml OUTPUT,
	@ErrorMessageOut = @ErrorMessage OUTPUT;

See also

Parameters

@CreationDatetime optional datetime
The date and time of creation to include in the file. If not set, the database server's current date and time will be used.
@ErrorMessageOut mandatory nvarchar
Error message intended for developers if the file could not be generated. The procedure will also have a negative return value when errors occur.
@InitiatorBicOrBei optional varchar
@InitiatorId optional varchar
@InitiatorIdSchemeCode optional varchar
@InitiatorName optional nvarchar
@MessageId mandatory varchar
A unique identifier for the file. It might be returned by some reports, e.g. pain.002 files.
@OutputAsXml optional bit
Set to 1 to return the generated file in the @XmlOut parameter instead of the @VarbinaryOut parameter. Defaults to 0.
@PaymentInformationIdPrefix optional varchar
Can be set to generate a determinstic file (together with @CreationDatetime) e.g. for automated tests.
@UseRawAccountNumbers optional bit
If set to 1, all BBAN, Bankgiro, and PlusGiro columns will be included as is in the generated file. If not set, the characters '.', '-', and ' ' will be stripped from all BBAN, Bankgiro, and PlusGiro columns before including them in the generated file.
@VarbinaryOut mandatory varbinary
The generated file encoded as UTF-8. Will be NULL if @OutputAsXml = 1.
@XmlOut optional xml
The generated file if @OutputAsXml = 1. Will be NULL otherwise.

Resultsets

Resultset: #P27Payment

Do not assume anything about the contents of the table after the procedure has been called.
Table count:
Row count:

Columns