Semantic mapping of staging fields (CAMPO1…CAMPO130) to functional meanings within this domain.
Dimension codes appear in operation input/output and lookup table definitions.
SELECT Scenario,
Period,
Entity,
Account,
CostCenter,
Employee,
Category,
Amount,
Sts,
CASE
WHEN length(TransferMonth::Text)=1
THEN concat('0',TransferMonth::text)
ELSE TransferMonth::text
END AS TransferMonth,
TransferTo,
TransferEntityID,
TransferStatus
FROM
(SELECT a.cod_scenario AS Scenario,
a.cod_periodo AS Period,
a.cod_azienda AS Entity,
a.cod_conto AS Account,
a.cod_dest1 AS CostCenter,
a.cod_dest4 AS Employee,
a.cod_categoria AS Category,
a.importo AS Amount,
b.NOTE AS Sts,
date_part('month', to_timestamp(((c.NOTE::numeric - 25569) * 86400))) AS TransferMonth,
c.NOTE AS TransferDate,
d.NOTE AS TransferStatus,
e.NOTE AS TransferTo,
f.cod_azienda as TransferEntityID
FROM DATI_SALDI_LORDI a
INNER JOIN DATI_NOTE b ON (a.cod_scenario=b.cod_scenario
AND a.cod_azienda=b.cod_azienda
AND a.cod_categoria=b.cod_categoria
AND a.cod_dest1=b.cod_dest1
AND b.COD_PERIODO ='12'
AND a.cod_dest2= b.cod_dest2
AND a.cod_dest3= b.cod_dest3
AND a.cod_dest4=b.cod_dest4
AND b.cod_conto='ST_450'
AND b.note='Transfer')
INNER JOIN DATI_NOTE c ON (a.cod_scenario=c.cod_scenario
AND a.cod_azienda=c.cod_azienda
AND a.cod_dest1=c.cod_dest1
AND a.cod_categoria=c.cod_categoria
AND a.cod_dest2= c.cod_dest2
AND a.cod_dest3= c.cod_dest3
AND a.cod_dest4=c.cod_dest4
AND c.cod_periodo = '12'
AND c.cod_conto='ST_475')
INNER JOIN DATI_NOTE d ON (a.cod_scenario=d.cod_scenario
AND a.cod_azienda=d.cod_azienda
AND a.cod_dest1=d.cod_dest1
AND a.cod_dest2= d.cod_dest2
AND a.cod_dest3= d.cod_dest3
AND a.cod_categoria=d.cod_categoria
AND a.cod_dest4=d.cod_dest4
AND d.cod_periodo = '12'
AND d.cod_conto='ST_455')
INNER JOIN DATI_NOTE e ON (a.cod_scenario=e.cod_scenario
AND a.cod_azienda=e.cod_azienda
AND a.cod_dest1=e.cod_dest1
AND a.cod_categoria=e.cod_categoria
AND a.cod_dest2= e.cod_dest2
AND a.cod_dest3= e.cod_dest3
AND a.cod_dest4=e.cod_dest4
AND e.cod_periodo = '12'
AND e.cod_conto='ST_460')
INNER JOIN AZIENDA f on e.note=f.desc_azienda0
WHERE a.cod_dest2='PD_500'
AND a.cod_dest3='CM_500'
AND a.cod_dest5='NA'
AND a.cod_conto in ('60010','ST_612','ST_642','ST_495','ST_500','ST_540','60030','60035','60040','60050','60060','60065','60055')
AND a.cod_categoria in ('010')
AND d.note ='Pending'
AND a.cod_azienda={1}
AND a.cod_dest1={2}
AND a.cod_scenario={3}
)T
SELECT Scenario,
Period,
Entity,
Account,
CostCenter,
Employee,
Category,
Amount
FROM
(
SELECT a.cod_scenario AS Scenario,
a.cod_periodo AS Period,
a.cod_azienda AS Entity,
a.cod_conto AS Account,
a.cod_dest1 AS CostCenter,
a.cod_dest4 AS Employee,
a.cod_categoria AS Category,
a.importo AS Amount
FROM DATI_SALDI_LORDI a
INNER JOIN DATI_NOTE d ON (a.cod_scenario=d.cod_scenario
AND a.cod_dest1=d.cod_dest1
AND a.cod_dest2= d.cod_dest2
AND a.cod_dest3= d.cod_dest3
AND a.cod_dest4=d.cod_dest4
AND d.cod_periodo = '12'
AND d.cod_conto='ST_455'
AND d.note='Approved'
AND d.cod_categoria='010')
WHERE a.cod_dest2='PD_500'
AND a.cod_dest3='CM_500'
AND a.cod_dest5='NA'
AND a.cod_conto in ('60010','ST_612','ST_642','ST_495','ST_500','ST_540','60030','60035','60040','60050','60060','60065','60055')
AND a.cod_categoria in ('160', '170')
AND a.cod_dest1={2}
AND a.cod_scenario={3}
)T
SELECT Scenario,
Period,
Entity,
Account,
CostCenter,
Employee,
Category,
Amount
FROM
(
SELECT a.cod_scenario AS Scenario,
a.cod_periodo AS Period,
a.cod_azienda AS Entity,
a.cod_conto AS Account,
a.cod_dest1 AS CostCenter,
a.cod_dest4 AS Employee,
a.cod_categoria AS Category,
a.importo AS Amount
FROM DATI_SALDI_LORDI a
INNER JOIN DATI_NOTE d ON (a.cod_scenario=d.cod_scenario
AND a.cod_dest1=d.cod_dest1
AND a.cod_dest2= d.cod_dest2
AND a.cod_dest3= d.cod_dest3
AND a.cod_dest4=d.cod_dest4
AND d.cod_periodo = '12'
AND d.cod_conto='ST_455'
AND d.note='Rejected'
AND d.cod_categoria='010')
WHERE a.cod_dest2='PD_500'
AND a.cod_dest3='CM_500'
AND a.cod_dest5='NA'
AND a.cod_conto in ('60010','ST_612','ST_642','ST_495','ST_500','ST_540','60030','60035','60040','60050','60060','60065','60055')
AND a.cod_categoria in ('160', '170')
AND a.cod_dest1={2}
AND a.cod_scenario={3}
)T
SELECT a.Scenario,a.Entity,f.desc_azienda0 as EntityDesc,a.cod_categoria as Category,a.Account,a.CostCenter,a.Employee,a.EmployeeInfo,d.note EntityTransferDESC,e.cod_azienda EntityTransferID,'Pending' as Pending
FROM
(
Select cod_scenario as Scenario,cod_azienda as Entity,cod_categoria,cod_conto as Account,cod_dest1 as CostCenter,cod_dest4 as Employee,note as EmployeeInfo from dati_note
where cod_conto='ST_400'
UNION
Select cod_scenario as Scenario,cod_azienda as Entity,cod_categoria,cod_conto as Account,cod_dest1 as CostCenter,cod_dest4 as Employee,note as EmployeeInfo from dati_note
where cod_conto='ST_410'
UNION
Select cod_scenario as Scenario,cod_azienda as Entity,cod_categoria,cod_conto as Account,cod_dest1 as CostCenter,cod_dest4 as Employee,note as EmployeeInfo from dati_note
where cod_conto='ST_420'
UNION
Select cod_scenario as Scenario,cod_azienda as Entity,cod_categoria,cod_conto as Account,cod_dest1 as CostCenter,cod_dest4 as Employee,note as EmployeeInfo from dati_note
where cod_conto='ST_450'
UNION
Select cod_scenario as Scenario,cod_azienda as Entity,cod_categoria,cod_conto as Account,cod_dest1 as CostCenter,cod_dest4 as Employee,note as EmployeeInfo from dati_note
where cod_conto='ST_475'
UNION
Select cod_scenario as Scenario,cod_azienda as Entity,cod_categoria,cod_conto as Account,cod_dest1 as CostCenter,cod_dest4 as Employee,note as EmployeeInfo from dati_note
where cod_conto='ST_460'
) a
INNER JOIN dati_note c
ON ( a.Scenario=c.cod_scenario AND
a.Entity=c.cod_azienda AND
a.cod_categoria=c.cod_categoria AND
a.CostCenter=c.cod_dest1 AND
a.Employee=c.cod_dest4 AND
c.note='Transfer' AND
c.cod_conto='ST_450'
)
INNER JOIN dati_note d
ON ( a.Scenario=d.cod_scenario AND
a.Entity=d.cod_azienda AND
a.CostCenter=d.cod_dest1 AND
a.cod_categoria=d.cod_categoria AND
a.Employee=d.cod_dest4 AND
d.cod_conto='ST_460'
)
INNER JOIN azienda e
ON d.note=e.desc_azienda0
INNER JOIN azienda f
ON a.entity=f.cod_azienda
WHERE a.cod_categoria='010'
and a.Scenario={3}
and a.Entity={1}
and a.CostCenter={2}
select
a.cod_scenario as Scenario,
a.cod_azienda as Entity,
a.cod_conto as Account,
a.cod_dest1 as CostCenter,
a.cod_dest4 as Employee,
a.cod_categoria as Category,
a.Note as EmployeeInfo,
d.cod_azienda as EntityTransferID,
b.note as EntityTransferDESC,
'Transferred' as Transferred
from dati_note a
INNER JOIN DATI_NOTE b
ON (
a.cod_scenario=b.cod_scenario AND
a.cod_azienda=b.cod_azienda AND
a.cod_dest1=b.cod_dest1 AND
a.cod_dest4=b.cod_dest4 AND
a.cod_categoria=b.cod_categoria AND
b.cod_conto='ST_465')
INNER JOIN DATI_NOTE c
ON (
a.cod_scenario=c.cod_scenario AND
a.cod_azienda=c.cod_azienda AND
a.cod_dest1=c.cod_dest1 AND
a.cod_dest4=c.cod_dest4 AND
a.cod_categoria=c.cod_categoria AND
c.cod_conto='ST_455' AND
c.note='Approved')
INNER JOIN AZIENDA d
ON b.note=d.desc_azienda0
WHERE
a.provenienza<>'MAP_30_050' AND
a.cod_dest1={2} AND
a.cod_scenario={3}
select
a.cod_scenario as Scenario,
a.cod_azienda as Entity,
a.cod_conto as Account,
a.cod_dest1 as CostCenter,
a.cod_dest4 as Employee,
a.cod_categoria as Category,
a.Note as EmployeeInfo,
d.cod_azienda as EntityTransferID,
b.note as EntityTransferDESC,
'Active' as Active
from dati_note a
INNER JOIN DATI_NOTE b
ON (
a.cod_scenario=b.cod_scenario AND
a.cod_azienda=b.cod_azienda AND
a.cod_dest1=b.cod_dest1 AND
a.cod_dest4=b.cod_dest4 AND
a.cod_categoria=b.cod_categoria AND
b.cod_conto='ST_465')
INNER JOIN DATI_NOTE c
ON (
a.cod_scenario=c.cod_scenario AND
a.cod_azienda=c.cod_azienda AND
a.cod_dest1=c.cod_dest1 AND
a.cod_dest4=c.cod_dest4 AND
a.cod_categoria=c.cod_categoria AND
c.cod_conto='ST_455' AND
c.note='Rejected')
INNER JOIN AZIENDA d
ON b.note=d.desc_azienda0
WHERE
a.provenienza<>'MAP_30_060' AND
a.cod_dest1={2} AND
a.cod_scenario={3}