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

		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;
GO
