-- NMI 04/07/2005 Fiche 2473 : Ajout du taux et de la date de conversion en EURO
-- NMI 08/09/2005 Fiche 2502 : Inversion du decode de REGROUPEMENT GLOBAL
-- HKa 09/07/2007 Fiche 2596 : ajout de nouveaux paramètres
-- ============================================================================
-- PARAMETRES
--	&1 = répertoire du fichier de sortie
-- 	&2 = mois
--	&3 = année
--	&4 = code compagnie SAP
--	&5 = code compagnie
--	&6 = code activité
--	&7 = nature d'operation
-- ============================================================================

set arraysize 1 
set trimspool on
set verify off
set feedback off
set heading off
set PAGESIZE 0
set linesize 2000
set colsep ';'

set termout on

prompt -- ============================================================
prompt --   Debut des traitements
prompt -- ============================================================
prompt --   Debut des extractions
prompt --   -  Extraction du justificatif du fichier SAP

set termout off
spool &&1\EX053_JUSTIF_SAP_&&2.&&3.&&4.&&5.&&6.&&7..xls

select 
'DATE_STAT	CIE_SAP	CIE	ACTIVITE	ACT_POSTE_CIE	TYPE_CONTRAT	NATURE_CONTRAT	'
||'ETAT_CONTRAT	CONTRAT	CODE_FAMILLE	FAMILLE_COMPTABLE	REGROUPEMENT_TDB	'
||'STRUCTURE	REGROUPEMENT_GLOBAL	ANALYSE_FAC	ANALYSE_GLOBAL	RETRO_FAC	'
||'RETRO_TRAITE	CONTRAT_ET_POSTE	DEVISE	TERRITOIRE_SAP	TERRITOIRE	'
||'CBO_SAP	CBO	CSD_SAP	DATE_SURVENANCE	DATE_EFFET	EXERCICE_DU_TRAITE	NATURE_COMPTABLE	'
||'POSTE	POSTE_LIBELLE	POSTE_COPA	TAUX CONVERSION	DATE CONVERSION	MT	MT_EUR'
from dual;

select 
DATE_STAT
||CHR(9)||CIE_SAP
||CHR(9)||CIE
||CHR(9)||ACTIVITE
||CHR(9)||ACTIVITE||POSTE||CIE
||CHR(9)||TYPE_CONTRAT
||CHR(9)||NATURE_CONTRAT
||CHR(9)||ETAT_CONTRAT
||CHR(9)||CONTRAT
||CHR(9)||CODE_FAMILLE
||CHR(9)||FAMILLE_COMPTABLE
||CHR(9)||REGROUPEMENT_TDB
||CHR(9)||STRUCTURE
||CHR(9)||decode(STRUCTURE,'NAT', 'HORS GLOBAL', 'GLOBAL')
||CHR(9)||CONTRAT || decode(STRUCTURE, 'NAT', 'HORS GLOBAL', 'GLOBAL') || POSTE
||CHR(9)||CIE_SAP || ACTIVITE || decode(STRUCTURE, 'NAT', 'HORS GLOBAL', 'GLOBAL')
||CHR(9)||RETRO_FAC
||CHR(9)||RETRO_TRAITE
||CHR(9)||CONTRAT || POSTE
||CHR(9)||DEVISE
||CHR(9)||TERRITOIRE_SAP
||CHR(9)||TERRITOIRE
||CHR(9)||CBO_SAP
||CHR(9)||CBO
||CHR(9)||CSD_SAP
||CHR(9)||DATE_SURVENANCE
||CHR(9)||DATE_EFFET
||CHR(9)||EXE
||CHR(9)||NATURE_COMPTABLE
||CHR(9)||POSTE
||CHR(9)||POSTE_LIBELLE
||CHR(9)||POSTE_COPA
||CHR(9)||TAUX_CONV
||CHR(9)||REPLACE(to_char(sum(MT),'FM9999999990.09'),'.',',')
||CHR(9)||REPLACE(to_char(sum(MT_EUR),'FM9999999990.09'),'.',',')
from (
select 
   to_char(tba.tbafind,'dd/mm/yyyy') as DATE_STAT,
   cie.sap_ciecodc as CIE_SAP,
   cie.ciecodc as CIE,
   mtp.acrcodc as ACTIVITE,
   decode(str.strseqx,null,'F','T') as TYPE_CONTRAT,
   decode(str.strseqx,null,cfa.cfanarc,str.strnarc) as NATURE_CONTRAT,
   decode(str.strseqx,null,null,decode(R1.MAX_EXE,&&3,'EN_COURS','RUN_OFF')) as ETAT_CONTRAT,
   decode(str.strseqx,null,decode(cfa.acrcodc,'CES','Facultatives '||bef.beflonl,'Fac n° '||cfa.cfanumx||' - '||nvl(pol.polnoml,cai.cainoml)),
   to_char(trt.trtnumx)||'.'||to_char(str.strnumx)||' '||str.strlibl) as CONTRAT,
   decode(str.strseqx,null,fco_cfa.fcocodc,fco_str.fcocodc) as CODE_FAMILLE,
   decode(str.strseqx,null,fco_cfa.fcolibl,fco_str.fcolibl)  as FAMILLE_COMPTABLE,
   decode(str.strseqx,null,cfa.cfartbl, str.strrtbl)  as REGROUPEMENT_TDB,
   decode(str.strseqx,null,decode(pol.polseqx,null,cai.caioric,pol.poloric),decode(str.oricodc,null,'NAT',str.oricodc)) as STRUCTURE,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',cfa.cfarfac,'N'),'N') as RETRO_FAC,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',decode(cfa.cfatdsc,'P','Schema '||to_char(cfa.srpseqx),'S','Standard','Pas de Retro'),'N'),
   decode(trt.acrcodc,'ACC',decode(str.strtdsc,'P','Schema '||to_char(str.srpseqx),'S','Standard','Pas de Retro'),'N')) as RETRO_TRAITE,
   mtp.devcodc as DEVISE,
   decode(mtp.acrcodc,'CES',ter.ter_tercodc,'97') as TERRITOIRE_SAP,
   ter.tercodc as TERRITOIRE,
   cbo.cmicodc as CBO_SAP,
   cbo.cbocodc as CBO,
   decode(mtp.acrcodc,'CES',csd.bpccodc,'B004') as CSD_SAP,
   null as DATE_SURVENANCE,
   mtp.mtpannn as DATE_EFFET,
   decode(str.strseqx,null, CFAEXEN, TRTREFN) as EXE,
   mtp.acrcodc||ncl.ncenatx as NATURE_COMPTABLE,
   ncl.ncenatx as POSTE,
   ncl.ncllibl as POSTE_LIBELLE,
   '' as POSTE_COPA,
   mtp.mtpmmtm*power(10,dev.devprcn-dev.devdecn) as MT, 
   rskcdv.rspcdv02(mtp.mtpmmtm,mtp.devcodc,'500',&&2,&&3, '500') / 100 as MT_EUR,
   rskcdv.rspcdv05(mtp.devcodc, &&2, &&3) as TAUX_CONV
FROM
  rsbmtp mtp,
  rsbcfa cfa,
  rsbpol pol,
  rsbcai cai,
  rsbbef bef,
  rsbtrt trt,
  rsbstr str,
  rsbfco fco_cfa,
  rsbfco fco_str,
  rsbcbo cbo,
  rsbcsd csd,
  rsbter ter,
  rsbcie cie,
  rsbdev dev,
  rsbtba tba,
  rsbncl ncl,
  (select rsbtrt.trtnumx, max(rsbtrt.trtrefn) as MAX_EXE 
  from rsbtrt where rsbtrt.supcodc = 'N'
  group by rsbtrt.trtnumx) R1
  WHERE  
  mtp.cfaseqx = cfa.cfaseqx (+)
  and cfa.polseqx = pol.polseqx (+)
  and cfa.caiseqx = cai.caiseqx (+)
  and cfa.befcodc = bef.befcodc (+)
  and cfa.fcocodc = fco_cfa.fcocodc (+)
  and mtp.cbocodc = cbo.cbocodc
  and mtp.ciecodc = cbo.ciecodc 
  and mtp.csdcodc = csd.csdcodc (+)
  and mtp.ciecodc = csd.ciecodc (+)
  and mtp.strseqx = str.strseqx (+)
  and str.trtseqx = trt.trtseqx (+)
  and trt.trtnumx = R1.trtnumx (+)
  and str.fcocodc = fco_str.fcocodc (+)
  and mtp.ciecodc = cie.ciecodc
  and mtp.tercodc = ter.tercodc
  and mtp.ncenatx = ncl.ncenatx
  and mtp.devcodc = dev.devcodc
  and mtp.tbaseqx = tba.tbaseqx
  and tba.tdtcodc = 'CAL'
  and to_char(tba.tbafind,'mm') = &&2
  and to_char(tba.tbafind,'yyyy') = &&3
  and (('&&5' is null and mtp.ciecodc in (select distinct ciecodc from rsbcie where ltrim(rtrim(to_char(sap_ciecodc)))='&&4')) or ('&&5' is not null and mtp.ciecodc='&&5'))
  and (('&&6' is null and mtp.acrcodc in (select acrcodc from rsbacr)) or ('&&6' is not null and mtp.acrcodc='&&6'))
  and (('&&7' is null and mtp.ncenatx in (3,6,18,35,80,83,89,90,91,94,95,105,112,161,177,178,179,180,181,182,183)) or ('&&7' is not null and ltrim(rtrim(to_char(mtp.ncenatx)))='&&7'))
  and mtp.mtpmmtm <> 0
union all
select 
   to_char(tba.tbafind,'dd/mm/yyyy') as DATE_STAT,
   cie.sap_ciecodc as CIE_SAP,
   cie.ciecodc as CIE_CODE,
   mts.acrcodc as ACTIVITE,
   decode(str.strseqx,null,'F','T') as TYPE_CONTRAT,
   decode(str.strseqx,null,cfa.cfanarc,str.strnarc) as NATURE_CONTRAT,
   decode(str.strseqx,null,null,decode(R1.MAX_EXE,&&3,'EN_COURS','RUN_OFF')) as ETAT_CONTRAT,
   decode(str.strseqx,null,decode(cfa.acrcodc,'CES','Facultatives '||bef.beflonl,'Fac n° '||cfa.cfanumx||' - '||nvl(pol.polnoml,cai.cainoml)),
   to_char(trt.trtnumx)||'.'||to_char(str.strnumx)||' '||str.strlibl) as CONTRAT,
   decode(str.strseqx,null,fco_cfa.fcocodc,fco_str.fcocodc) as CODE_FAMILLE,
   decode(str.strseqx,null,fco_cfa.fcolibl,fco_str.fcolibl)  as FAMILLE_COMPTABLE,
   decode(str.strseqx,null,cfa.cfartbl, str.strrtbl)  as REGROUPEMENT_TDB,
   decode(str.strseqx,null,decode(pol.polseqx,null,cai.caioric,pol.poloric),decode(str.oricodc,null,'NAT',str.oricodc)) as STRUCTURE,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',cfa.cfarfac,'N'),'N') as RETRO_FAC,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',decode(cfa.cfatdsc,'P','Schema '||to_char(cfa.srpseqx),'S','Standard','Pas de Retro'),'N'),
   decode(trt.acrcodc,'ACC',decode(str.strtdsc,'P','Schema '||to_char(str.srpseqx),'S','Standard','Pas de Retro'),'N')) as RETRO_TRAITE,
   mts.devcodc as DEVISE,
   decode(mts.acrcodc,'CES',ter.ter_tercodc,'97') as TERRITOIRE_SAP,
   ter.tercodc as TERRITOIRE,
   cbo.cmicodc as CBO_SAP,
   cbo.cbocodc as CBO,
   decode(mts.acrcodc,'CES',csd.bpccodc,'B004') as CSD_SAP,
   decode(mts.ncenatx,20,null,30,null,31,null,mts.mtsannn) as DATE_SURVENANCE,
   decode(mts.ncenatx,20,mts.mtsannn,30,mts.mtsannn,31,mts.mtsannn,0) as DATE_EFFET,
   decode(str.strseqx,null, CFAEXEN, TRTREFN) as EXE,
   mts.acrcodc||ncl.ncenatx as NATURE_COMPTABLE,
   ncl.ncenatx as POSTE,
   ncl.ncllibl as POSTE_LIBELLE,
   '' as POSTE_COPA,
   mts.mtsmmtm*power(10,dev.devprcn-dev.devdecn) as MT, 
   rskcdv.rspcdv02(mts.mtsmmtm,mts.devcodc,'500',&&2,&&3, '500') / 100 as MT_EUR,
   rskcdv.rspcdv05(mts.devcodc, &&2, &&3) as TAUX_CONV
FROM
  rsbmts mts,
  rsbcfa cfa,
  rsbpol pol,
  rsbcai cai,
  rsbbef bef,
  rsbtrt trt,
  rsbstr str,
  rsbfco fco_cfa,
  rsbfco fco_str,
  rsbcbo cbo,
  rsbcsd csd,
  rsbter ter,
  rsbcie cie,
  rsbdev dev,
  rsbtba tba,
  rsbncl ncl,
  (select rsbtrt.trtnumx, max(rsbtrt.trtrefn) as MAX_EXE 
  from rsbtrt where rsbtrt.supcodc = 'N'
  group by rsbtrt.trtnumx) R1
WHERE  
  mts.cfaseqx = cfa.cfaseqx (+)
  and cfa.polseqx = pol.polseqx (+)
  and cfa.caiseqx = cai.caiseqx (+)
  and cfa.befcodc = bef.befcodc (+)
  and cfa.fcocodc = fco_cfa.fcocodc (+)
  and mts.cbocodc = cbo.cbocodc
  and mts.ciecodc = cbo.ciecodc 
  and mts.csdcodc = csd.csdcodc (+)
  and mts.ciecodc = csd.ciecodc (+)
  and mts.strseqx = str.strseqx (+)
  and str.trtseqx = trt.trtseqx (+)
  and trt.trtnumx = R1.trtnumx (+)
  and str.fcocodc = fco_str.fcocodc (+)
  and mts.ciecodc = cie.ciecodc
  and mts.tercodc = ter.tercodc
  and mts.ncenatx = ncl.ncenatx
  and mts.devcodc = dev.devcodc
  and mts.tbaseqx = tba.tbaseqx
  and tba.tdtcodc = 'CAL'
  and to_char(tba.tbafind,'mm') = &&2
  and to_char(tba.tbafind,'yyyy') = &&3
  and (('&&5' is null and mts.ciecodc in (select distinct ciecodc from rsbcie where ltrim(rtrim(to_char(sap_ciecodc)))='&&4')) or ('&&5' is not null and mts.ciecodc='&&5'))
  and (('&&6' is null and mts.acrcodc in (select acrcodc from rsbacr)) or ('&&6' is not null and mts.acrcodc='&&6'))
  and (('&&7' is null and mts.ncenatx in (15,20,26,30,31,66,87,92,93,113,184,185)) or ('&&7' is not null and ltrim(rtrim(to_char(mts.ncenatx)))='&&7'))
  and mts.mtsmmtm <> 0
) group by DATE_STAT, CIE_SAP, CIE, ACTIVITE, TYPE_CONTRAT, NATURE_CONTRAT, ETAT_CONTRAT, CONTRAT,
CODE_FAMILLE, FAMILLE_COMPTABLE, REGROUPEMENT_TDB, STRUCTURE, RETRO_FAC, RETRO_TRAITE, DEVISE, 
TERRITOIRE_SAP, TERRITOIRE,  CBO_SAP, CBO, CSD_SAP, DATE_SURVENANCE, DATE_EFFET, EXE,
NATURE_COMPTABLE, POSTE, POSTE_LIBELLE, POSTE_COPA, TAUX_CONV;

spool off
set termout on

prompt --   Fin des extractions
prompt -- ============================================================
prompt --   Fin des traitements
prompt -- ============================================================


commit;
exit;