SEPA Letter for Italy Customer

Receivables

The SEPA direct-debit pre-notification letter for Italian customers — the advance notice of the amount and date each customer's account will be debited, in the format Italian SEPA practice requires before collection.

Related The Italian pre-notification companion to the SEPA Debit Authorization and Bank Detail Check Report.

Sample build of the SEPA Letter for Italy Customer — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Batch

Sample

Credit Manager

$96,400.00

Due Date

2026-02-28

210

Letters

$1.80M

To collect

No valid mandate

Customer	Mandate	Amount	Collection Date	Iban	Status
Acme Industrial	2026-04-30	$1,240,500.00	2026-04-30	Sample	Open
Northwind Trading	2026-03-31	$842,150.75	2026-03-31	—	Posted
Globex Holdings	2026-02-28	$96,400.00	2026-02-28	Sample	Validated
Initech LLC	2026-01-31	$1,005,233.10	2026-01-31	—	Open
Umbrella Corp	2025-12-31	$58,720.40	2025-12-31	Sample	Paid
Acme Industrial	2026-04-30	$1,240,500.00	2026-04-30	Sample	Open

AI Analyst · active

reading

The report generates the pre-notification per customer ahead of the SEPA collection date.

flag

Seven customers in the run have no valid mandate — debiting them without one is non-compliant and the collection will be returned.

root cause & next step

Secure mandates before including those customers; SEPA requires a valid mandate and advance notice for every debit.

Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →

This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.

Data sources

How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

WITH BANK_DETAILS AS
(
SELECT
			IEP.cust_account_id	
		,	IEP.acct_site_use_id
		--,	IEB.bank_account_name
		,	IEB.bank_account_num
		,	CBB.bank_name			bank_account_name		
		,	CBB.bank_number				 	
		,	CBB.branch_number			 	
		
FROM
			iby_pmt_instr_uses_all 	   		IPI
		,	iby_external_payers_all 		IEP
        ,   iby_ext_bank_accounts 	   		IEB
		,	ce_bank_branches_v 		   		CBB
	
WHERE
			1							=1
		AND IPI.ext_pmt_party_id 		=IEP.ext_payer_id
		AND	IPI.instrument_id			=IEB.ext_bank_account_id
        AND IEB.bank_id 	   			=CBB.bank_party_id
        AND IEB.branch_id 				=CBB.branch_party_id
		AND SYSDATE BETWEEN IPI.start_date AND IPI.end_date
)

SELECT
			ARA
		,	party_number
		,	party_name
		,	ADDRESS
		,	postal_code
        ,	city
        ,	province
		,	town_or_city
		,	name
		,	bank_account_name
		,	bank_account_num
		,	bank_number
		,	branch_number
		,	DUE_DATE
		, 	LEN_LINE-14 	LEN_LINE
		,	rpad(' ', 5) ||
               rpad('Fat.N. ', 7) ||
               TRX_NUMBER ||' N. di ric. '||RECEIPT_NUMBER||
               ' del ' ||INVOICE_DATE||
               lpad('di Euro',8)||
			  lpad(to_char(AMOUNT,'FM9G999G999G999D90', 'nls_numeric_characters = '',.'''), 16)		AMOUNT
		,	rpad(' ', LEN_LINE-32) ||
               lpad('TOTALE', 8) ||
               lpad(to_char(SUM(AMOUNT) OVER(PARTITION BY PARTY_ID),'FM999G999G999G999D90', 'nls_numeric_characters = '',.'''), 24)  TOTAL
		,	LOCATION
		,	CM
		,	SYS_DATE
FROM
(
	SELECT
			IDA.authorization_reference_number										ARA
		,	HP.party_id
		,	HP.party_number
		,	HP.party_name
		,	HP.address1																ADDRESS
		--,	HP.address1||','||HP.address2||','||HP.address3||','||HP.address4		ADDRESS
        ,	HP.postal_code
        ,	HP.city
        ,	HP.province
		,	HL.town_or_city
		,	ARM.name
		,	BB.bank_account_name
		,	BB.bank_account_num
		,	BB.bank_number
		,	BB.branch_number
		,	:P_DUE_DATE												DUE_DATE
		,	RCT.trx_number 
		,	ACR.receipt_number				 
		,	TO_CHAR(RCT.trx_date,'DD/MM/YY') 			 			INVOICE_DATE
		,	ACR.amount												AMOUNT
		,	'CUSTOM ITALY S.R.L.'									LOCATION
		,	:P_CREDIT_MANAGER										CM
		,	TO_CHAR(sysdate,'DD/MM/YY')								SYS_DATE
		,	LENGTH(rpad(' ', 5) ||
               rpad('Fat.N. ', 7) ||
               RCT.trx_number ||' N. di ric. '||ACR.receipt_number||
               ' del ' ||TO_CHAR(RCT.trx_date,'DD/MM/YY')||
               lpad('di Euro',24)) LEN_LINE
		
	FROM
			hz_cust_accounts 				HCA
		,	hz_parties 						HP
		,	hz_cust_site_uses_all 			HCSUA
		,	ar_cash_receipts_all 			ACR
		,	ar_cash_receipt_history_all		ARH
		,	ar_receipt_methods				ARM
		/*,	ce_bank_acct_uses_all			CBA
		,	ce_bank_accounts 				BA
		,	ce_bank_branches_v 				BB*/
		,	bank_details					BB
		,	ra_customer_trx_all			 	RCT
		--,	ar_payment_schedules_all 		APSA
		,	ar_receivable_applications_all 	ARAA
		,	iby_fndcpt_tx_extensions 		FT
		,	iby_debit_authorizations_f		IDA
		,	ar_batches_all					ABA
		,	ar_receipt_classes				ARC
		,	fun_all_business_units_v 		FABU
		,	hr_locations					HL
		/* ,	(
				SELECT 
							RDA.customer_trx_id
						,	SUM(RDA.amount)					AMOUNT
				FROM
							ra_cust_trx_line_gl_dist_all	RDA
				WHERE
							RDA.account_class				='REC'
						AND NVL(RDA.latest_rec_flag,'Y')	='Y' 
				GROUP BY 
							RDA.customer_trx_id
			)								DIST */
		
	WHERE
			1								=1
		AND ACR.pay_from_customer 			=HCA.cust_account_id
		AND HCA.party_id 					=HP.party_id
		AND ACR.customer_site_use_id 		=HCSUA.site_use_id
		AND ACR.receipt_method_id			=ARM.receipt_method_id
		/* AND ACR.remit_bank_acct_use_id	=CBA.bank_acct_use_id(+)
		AND CBA.bank_account_id				=BA.bank_account_id(+)
		AND BA.bank_branch_id 				=BB.branch_party_id(+) */
		AND HCA.cust_account_id				=BB.cust_account_id(+)
		AND HCSUA.site_use_id				=BB.acct_site_use_id(+)
		--AND RCT.customer_trx_id 			=DIST.customer_trx_id
		AND RCT.customer_trx_id				=ARAA.applied_customer_trx_id
		AND ARAA.cash_receipt_id 			=ACR.cash_receipt_id
		AND ACR.cash_receipt_id 			=ARH.cash_receipt_id
		AND ACR.payment_trxn_extension_id	=FT.trxn_extension_id
		AND FT.debit_authorization_id 		=IDA.debit_authorization_id
		AND SYSDATE BETWEEN IDA.effective_start_date AND IDA.effective_end_date
		AND ACR.receipt_batch_id			=ABA.batch_id
		AND ABA.receipt_method_id  			=ARM.receipt_method_id
		AND	ABA.receipt_class_id			=ARC.receipt_class_id
		AND FABU.bu_id 						=RCT.org_id
		AND FABU.location_id 				=hl.location_id
		AND FABU.bu_name					='IT BU EUR'
		AND ARH.STATUS						='CONFIRMED'
		AND (ABA.name IN (:P_BATCH) OR  'All' IN (:P_BATCH||'All'))
		
/* GROUP BY
			IDA.authorization_reference_number
		,	HP.party_id
		,	HP.party_number
		,	HP.party_name
		,	HP.address1
		,	HP.postal_code
        ,	HP.city
        ,	HP.province
		,	HL.town_or_city
		,	ARM.name
		,	BB.bank_account_name
		,	BB.bank_account_num
		,	BB.bank_number
		,	BB.branch_number
		,	:P_DUE_DATE						
		,	RCT.trx_number 
		,	ACR.receipt_number				 
		,	RCT.TRX_DATE 			 		
		,	:P_CREDIT_MANAGER									
		,	sysdate */	
)
ORDER BY 
		party_name

:P_BATCH :P_CREDIT_MANAGER :P_DUE_DATE

The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.

●— fact → dimension join

Element	Type	Definition
IBY_EXTERNAL_PAYERS_ALL	dimension	dimension
IBY_EXT_BANK_ACCOUNTS	dimension	dimension
HZ_CUST_ACCOUNTS	dimension	dimension
AR_PAYMENT_SCHEDULES_ALL	dimension	dimension
Amount	measure	measure

Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.

How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →

See the complete model

How this report's fact and dimensions fit the full picture, via conformed keys.

Receivables data model →Enterprise model →

Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.

Table	Reporting columns	Subject areas
IBY_PMT_INSTR_USES_ALL	3	2
IBY_EXTERNAL_PAYERS_ALL	8	1
IBY_EXT_BANK_ACCOUNTS	12	12
HZ_CUST_ACCOUNTS	14	43
AR_PAYMENT_SCHEDULES_ALL	32	6

Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.