1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| INSERT INTO z_qlik_paie
SELECT LS_NUMBULPAI, to_char(LS_DATDEB,'YYYYMM') AS MOIS_REF, LS_AE_AGEETA, SubStr(LS_AE_AGEETA,4,5), LS_RU_NUMRUB, LS_BASNBREUR, LS_TXPUSAL,
CASE WHEN LS_INDTYPRUBPAI = 1 THEN (Sum(LS_MTSALEUR) + Sum(LS_MTEMPEUR))
WHEN LS_INDTYPRUBPAI = 2 THEN ((Sum(LS_MTSALEUR) * -1) + Sum(LS_MTEMPEUR))
END AS MONTANT,
LS_DESLIGBUL, LS_TI_NUMTIE, LS_BU_CODBUD, LS_GE_CODGES, LS_NA_CODNAT, LS_NA_CODNOR, LS_SR_CODSOURUB, LS_SV_CODSER, LS_AN_CODANT, LS_CA_CODANA, LS_CA_TYPANA,
CASE WHEN LS_INDTYPRUBPAI = 1 AND LS_MTSALEUR != 0 THEN 'Brut'
WHEN LS_INDTYPRUBPAI = 2 AND LS_MTSALEUR != 0 THEN 'Charge Salariale'
ELSE 'Charge Patronale'
END AS TYPE_LIGNE, NULL, LS_AA_NUM
FROM H_lig_sal_bis
WHERE ls_datdeb >= To_Char('01/01/2018','dd/mm/yyyy')
AND ls_datdeb <= To_Char('01/' || SubStr(&&1,5,2) || '/' || SubStr(&&1,1,4),'dd/mm/yyyy')
AND LS_SEQIMP <> 0
AND (LS_SEQTRT BETWEEN 0 AND 8999 OR LS_SEQTRT IN (9954,9955,9957))
AND (LS_MTSALEUR <> 0 OR LS_MTEMPEUR <> 0)
AND LS_RU_NUMRUB NOT IN (321,1744,5905,8083)
AND SubStr(LS_AE_AGEETA,1,3) IN ('001','002','007','008')
GROUP BY LS_NUMBULPAI, LS_DATDEB, LS_AE_AGEETA, LS_RU_NUMRUB, LS_BASNBREUR, LS_TXPUSAL, LS_INDTYPRUBPAI, LS_DESLIGBUL,LS_MTSALEUR,
LS_TI_NUMTIE, LS_BU_CODBUD, LS_GE_CODGES, LS_NA_CODNAT, LS_NA_CODNOR, LS_SR_CODSOURUB, LS_SV_CODSER, LS_AN_CODANT, LS_CA_CODANA, LS_CA_TYPANA, LS_AA_NUM
ORDER BY LS_AE_AGEETA, LS_DATDEB DESC; |
Partager