SoftadminUtil.P27_Pain_001_001_03_Generate

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', -- Different payments should have different values, but the example file did not.
	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

@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.
@MessageId mandatory varchar
A unique identifier for the file. It might be returned by some reports, e.g. pain.002 files.
@VarbinaryOut mandatory varbinary
The generated file encoded as UTF-8. Will be NULL if @OutputAsXml = 1.
@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.
@InitiatorBicOrBei optional varchar
@InitiatorId optional varchar
@InitiatorIdSchemeCode optional varchar
@InitiatorName optional nvarchar
@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.
@UseEndToEndIdAsPaymentInformationId optional bit
If set, there will be a one-to-one mapping from payments to PaymentInformationId.
If not set, the procedure will generate PaymentInformationIds automatically, and it is possible that multiple payments will belong to the same PaymentInformationId. Defaults to 0.
@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.
@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

Resultset: #P27PaymentInformation

Will contain the mapping from EndToEndId to PaymentId if the table was present when the procedure was called. The definition of the table is according to SoftadminUtil.P27_Pain_PaymentInformation_Definition.
Table count:
Row count:
Columns