SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_mpay_bordereaux_sepa_data]
	@bordereau_id int

as

set nocount off;

declare @data_lot_str varchar(16) = CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '');
declare @numero_lot varchar(35) = LEFT('LOT-' + CAST(@bordereau_id AS VARCHAR(12)) + '-' + @data_lot_str,35) ;

with bdx_archive as
(
	select aaold.acc_actor_id, aaold.acc_iban, aaold.acc_mandat_number, aaold.acc_currency_code, ppold.pol_policy_id, mpayb_operation_utc_date
	from acc_mpay_bordereaux_details dold 
		inner join acc_mpay_bordereaux bold on bold.mpayb_bordereau_id = dold.mpaybd_bordereau_id
		inner join acc_invoices iold on iold.inv_invoice_id = dold.mpaybd_invoice_id
		inner join pol_policies_situations psold	on	psold.pols_policy_situation_id = iold.inv_policy_situation_id
		inner join pol_policies ppold on ppold.pol_policy_id = psold.pols_policy_id 
		inner join pol_policies_accounts paold		on	paold.polacc_policy_id = psold.pols_policy_id
		inner join act_accounts	aaold				on	aaold.acc_actor_id = paold.polacc_client_id
												and	aaold.acc_account_number = paold.polacc_account_number
	where bold.mpayb_status_code='C' and bold.mpayb_bordereau_id <> @bordereau_id
)
select	/*i.inv_invoice_id, ps.pols_number, i.inv_operation_code__last, a.a_operation_utc_date, a.a_amount, a.a_branch_id, a.a_currency_code,
				i.inv_start_date, i.inv_end_date, i.inv_monthly_payment_date, i.inv_monthly_payment_reject_count, i.inv_monthly_payment_status_code,
				a.a_account_id, d.mpaybd_monthly_payment_status_code, ps.pols_policy_id, a.a_actor_id, pa.polacc_account_number, vact.adr_complete_name,*/


			/*

			TypePrel = case when oldmandat.acc_actor_id is null then 0 /*'FRST'*/ else 1 /*'RCUR'*/ end, --WHEN Temp_TypePrel='FNAL' then 2  WHEN Temp_TypePrel='OOFF' then 3   else 0   end
			PmtInfId= @numero_lot,
			Temp_Client_id = pp.pol_client_id, 
			Temp_Ordre_Contrat=convert(smallint,0), -- max(Temp_Ordre_Contrat),
			PmtMtd='DD', 
			BtchBookg ='false', 
			PmtTpInf_SvcLvl_Cd='CORE', 
			DateTransaction=inv_monthly_payment_date, 
			NomSociete=vactent.adr_complete_name, 
			NumSiret='', 
			IBANlot=braaccount.acc_iban,
			BIClot =braaccount.acc_bic,
			ChrgBr='SLEV',
			PmtId_EndToEndID=LEFT('TRF-' + @data_lot_str + '-' + CAST(i.inv_invoice_id AS VARCHAR(12)), 35),

							/*RIGHT(
								'00000000000000000' +
								CONCAT(
									DATEPART(YEAR, GETDATE()) % 100,
									RIGHT('0' + CAST(MONTH(GETDATE()) AS varchar(2)), 2),
									RIGHT('0' + CAST(DAY(GETDATE()) AS varchar(2)), 2),
									RIGHT('0' + CAST(DATEPART(HOUR, GETDATE()) AS varchar(2)), 2),
									RIGHT('0' + CAST(DATEPART(MINUTE, GETDATE()) AS varchar(2)), 2),
									RIGHT('0' + CAST(DATEPART(SECOND, GETDATE()) AS varchar(2)), 2),
									RIGHT('00' + CAST(DATEPART(MILLISECOND, GETDATE()) AS varchar(3)), 3)
								),
								17
							),*/ 
			Amt_InstdAmt=a.a_amount,
			-- RmtInf_Ustrd=max(isnull(Temp_Obs, 'Prélévement ' + convert(varchar,Temp_id))),

			RmtInf_Ustrd =	CASE 
								WHEN ps.pols_number IS NULL THEN LEFT('PRELEVEMENT ' + CAST(i.inv_invoice_id AS VARCHAR(30)), 140)
        
								ELSE LEFT(
									LEFT(ps.pols_number + SPACE(25), 25) 
									+ ' ' 
									+ CONVERT(VARCHAR(10), inv_monthly_payment_date, 104) 
									+ ' ' 
									+ RIGHT('000' + CAST(ISNULL(i.inv_monthly_payment_reject_count, 0) % 1000 AS VARCHAR(3)), 3) 
									, 140 
								)
							END, 
			NomTiers=aa.acc_holder,
			IBANtrx=aa.acc_iban,
			Temp_OLD_IBAN = oldrib.acc_iban, 
			BICtrx=aa.acc_bic,
			Mandat=aa.acc_mandat_number,
			Temp_OLD_Mandat = oldrib.acc_mandat_number,
			DteSign=aa.acc_mandat_sign_date,
			NumEmetteur=braaccount.acc_issuer, --Bank_Emetteur,
			PrvtId_Othr_Id=braaccount.acc_ics, --  creditor_bank.act_internal_reference, -- PE ACESTA CRED CA TREBUIE SA-L ADAUG IN CRYSTAL --Bank_ICS, -- Bank_ICS este ID-ul de creditor al companiei tale (obtinut prin intermediul bancii tale).
			PrvtId_Othr_SchemeNm_Prtry='SEPA',
			Fichier=bdx.mpayb_reference + '-' + replace(replace(convert(varchar,bdx.mpayb_cr_date,120),':','-'),' ','_') + creditor_bank.act_internal_reference,
			PstCd=vact.adr_postal_code,
			TwnNm=LEFT(vact.adr_city, 35),
			AdrLine=vact.adr_address1

			*/

			-- ---- lot -------------------------------------------------------
			-- RCUR partout : les regles SEPA (reprises par COPS) n'imposent plus FRST
			-- depuis le rulebook 2016. Cela supprime toute une classe de rejets et
			-- donne un seul lot PmtInf. /!\ a confirmer aupres de la banque.
			-- Si la banque exige FRST, remplacez par la logique correcte :
			--   « existe-t-il un encaissement REUSSI anterieur sur la RUM courante ? »
			TypePrel            = 1,                    -- 1 = RCUR
			PmtInfId            = @numero_lot,          -- 30 car. max (« -RCUR » est ajoute)
			PmtMtd              = 'DD',
			PmtTpInf_SvcLvl_Cd  = 'CORE',               -- -> LclInstrm/Cd (SvcLvl vaut NURG, cote code)
			ChrgBr              = 'SLEV',
			DateTransaction     = i.inv_monthly_payment_date,   -- -> ReqdColltnDt

			-- ---- creancier -------------------------------------------------
			NomSociete          = vactent.adr_complete_name,
			NumSiret            = '',
			IBANlot             = braaccount.acc_iban,
			BIClot              = braaccount.acc_bic,
			PrvtId_Othr_Id      = braaccount.acc_ics,   -- ICS - obligatoire
			PrvtId_Othr_SchemeNm_Prtry = 'SEPA',        -- correct aussi pour COPS

			-- ---- identification de la transaction ---------------------------
			-- Revient dans le pain.002 sous OrgnlEndToEndId.
			-- Format : DD-<bordereau>-<facture>   ex. DD-4821-1234567  (18 car.)
			-- Unique car le bordereau change a chaque representation.
			-- Analysable : PARSENAME(REPLACE(id,'-','.'), 1) = la facture
			PmtId_EndToEndID    = LEFT(
									  'DD-' + CAST(bdx.mpayb_bordereau_id AS VARCHAR(10))
									+ '-'   + CAST(i.inv_invoice_id AS VARCHAR(12))
								  , 35),

			Amt_InstdAmt        = a.a_amount,           -- XPF, sans decimales

			-- ---- motif du paiement (140 car. en COPS, contre 31 auparavant) --
			-- Largeur fixe, une seule branche pour rester analysable par positions :
			--   [1-25] police   [27-36] echeance   [38-40] nb rejets   [42-61] bordereau
			RmtInf_Ustrd        = LEFT(
									  LEFT(ISNULL(ps.pols_number,
												  'FACT-' + CAST(i.inv_invoice_id AS VARCHAR(20)))
										   + SPACE(25), 25)
									+ ' ' + CONVERT(VARCHAR(10), i.inv_monthly_payment_date, 104)
									+ ' ' + RIGHT('000' + CAST(ISNULL(i.inv_monthly_payment_reject_count, 0) % 1000 AS VARCHAR(3)), 3)
									+ ' ' + LEFT(ISNULL(bdx.mpayb_reference, '') + SPACE(20), 20)
								  , 140),

			-- ---- debiteur --------------------------------------------------
			NomTiers            = LEFT(aa.acc_holder, 70),   -- le guide SEPA limite a 70
			IBANtrx             = aa.acc_iban,
			BICtrx              = aa.acc_bic,
			PstCd               = vact.adr_postal_code,
			Ctry                = coalesce(vactent.adr_country_code, vact.adr_country_code, 'XPF'),  -- PF / NC / WF, par ligne
			TwnNm               = LEFT(vact.adr_city, 35),
			AdrLine             = vact.adr_address1,

			-- ---- mandat ----------------------------------------------------
			Mandat              = aa.acc_mandat_number,
			DteSign             = aa.acc_mandat_sign_date,
			Temp_OLD_Mandat     = oldrib.acc_mandat_number,  -- amendement si la RUM change
			Temp_OLD_IBAN       = oldrib.acc_iban,           -- amendement si l'IBAN change

			-- ---- fichier ---------------------------------------------------
			Fichier             = bdx.mpayb_reference + '_'
								+ REPLACE(REPLACE(CONVERT(VARCHAR, bdx.mpayb_cr_date, 120), ':', '-'), ' ', '_') + '_'
								+ creditor_bank.act_internal_reference,

			-- ---- optionnel : seulement si vous encaissez AUSSI en EUR --------
			devise_code      = aa.acc_currency_code,

			-- ---- pour enregistrer l'EndToEndId dans votre table --------------
			inv_invoice_id      = i.inv_invoice_id,
			mpayb_id            = bdx.mpayb_bordereau_id,

			NumeroClient = vact.act_internal_reference,
			NumeroContrat = ps.pols_number,
			NomClient = vact.adr_complete_name
		from	acc_mpay_bordereaux_details d
				inner join acc_mpay_bordereaux bdx on bdx.mpayb_bordereau_id = d.mpaybd_bordereau_id
				inner join vw_act_banks creditor_bank on creditor_bank.bk_actor_id = bdx.mpayb_bank_id
				inner join acc_invoices i on i.inv_invoice_id = d.mpaybd_invoice_id
				inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = i.inv_policy_situation_id
				inner join pol_policies pp on pp.pol_policy_id = ps.pols_policy_id
				inner join pol_policies_accounts pa		on	pa.polacc_policy_id = ps.pols_policy_id
				inner join act_accounts	aa				on	aa.acc_actor_id = pa.polacc_client_id
														and	aa.acc_account_number = pa.polacc_account_number			
				inner join acc_accounts a				on	a.a_account_id = i.inv_invoice_id

				inner join vw_act_actors_adresses vact	on	vact.act_actor_id = a.a_actor_id
				inner join vw_act_actors_adresses vactent on vactent.act_actor_id = pp.pol_branch_id
				
				outer APPLY(
								select TOP (1) arch.*
								from bdx_archive arch
								where		arch.pol_policy_id = pp.pol_policy_id 
										and arch.acc_currency_code = aa.acc_currency_code
								order by mpayb_operation_utc_date
							) oldrib

				outer APPLY(
								select TOP (1) arch.*
								from bdx_archive arch
								where arch.acc_actor_id = pp.pol_client_id and arch.acc_mandat_number = aa.acc_mandat_number
							) oldmandat


				outer apply(	select top (1) *
								from act_accounts braaccount
								where		braaccount.acc_actor_id = pp.pol_branch_id
										and braaccount.acc_bank_id = bdx.mpayb_bank_id
										and braaccount.acc_currency_code = bdx.mpayb_currency_code
										and braaccount.acc_freeze = 0
										and braaccount.acc_use_for_monthly_dues = 1) braaccount

		where	d.mpaybd_bordereau_id = @bordereau_id;

		-- exec p_acc_mpay_bordereaux_sepa_data 50000413

		-- select distinct mpaybd_bordereau_id from acc_mpay_bordereaux_details order by mpaybd_bordereau_id desc
GO
