Marius Mutu
05d83c3de3
Export lunar pentru analiza clientului (rulare manuala, transformare in xlsx). Rulajele apar sub notele lor (acelasi document+cont+lucrare); iesirile fara nota raman in lista marcate RULAJ FARA NOTA. Contul 6xx pe rulaje vine din CORESP_CONT_VENCHELT. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_016S4YBUMMmhackpjaXsEGgX
107 lines
3.7 KiB
SQL
107 lines
3.7 KiB
SQL
-- CE CONTINE: cheltuielile lunii.
|
|
-- TIP='NOTA' = nota contabila de cheltuiala (cont debitor 6xx) din registrul jurnal; valoarea in SUMA.
|
|
-- TIP='RULAJ' = iesirile din rulaje (cantitate iesita <> 0) care au generat nota, imediat sub ea (acelasi
|
|
-- document + cont + lucrare); valoarea in VALOARE (=CANTITATE x PRET).
|
|
-- TIP='RULAJ FARA NOTA' = iesire de stoc fara nota de cheltuiala; pastrata pentru verificare.
|
|
-- Ordinea: data inregistrarii, document, lucrare, cont.
|
|
WITH p AS (
|
|
SELECT 2026 AS an, 7 AS luna FROM dual
|
|
),
|
|
note AS (
|
|
SELECT A.*
|
|
FROM VACT_TOT A, p
|
|
WHERE A.STERS = 0 AND A.AN = p.an AND A.LUNA = p.luna
|
|
AND A.SCD LIKE '6%'
|
|
),
|
|
blocuri AS (
|
|
SELECT COD, NNIR, SCC, NVL(ID_LUCRARE,0) AS ID_LUCRARE,
|
|
MIN(DATAIREG) AS DATA_GRUPARE
|
|
FROM note
|
|
GROUP BY COD, NNIR, SCC, NVL(ID_LUCRARE,0)
|
|
),
|
|
doc_anchor AS (
|
|
SELECT COD, NNIR, MIN(DATAIREG) AS DATA_DOC
|
|
FROM note
|
|
GROUP BY COD, NNIR
|
|
),
|
|
iesiri AS (
|
|
SELECT R.*, CC.CONT_CHELT AS CONT_CHELT_CORESP
|
|
FROM VRUL_TOT R
|
|
JOIN p ON R.AN = p.an AND R.LUNA = p.luna
|
|
LEFT JOIN CORESP_CONT_VENCHELT CC ON CC.CONT = R.CONT AND CC.STERS = 0
|
|
WHERE R.STERS = 0 AND R.CANTE <> 0
|
|
)
|
|
SELECT COD, R_TIP, TIP, DATAIREG, FDOC, SERIE_ACT, NRACT, DATAACT, NNIR,
|
|
EXPLICATIA, SCD, ASCD, SCC, ASCC, SUMA,
|
|
DENUMIRE_ARTICOL, CODMAT, UM, CANTITATE, PRET, VALOARE,
|
|
GESTIUNE, SECTIE, LUCRARE, ID_SET, TIP_RULAJ, ID_ACT, ID_RUL
|
|
FROM (
|
|
SELECT B.DATA_GRUPARE, N.COD,
|
|
1 AS R_TIP,
|
|
'NOTA' AS TIP,
|
|
N.DATAIREG,
|
|
N.FDOC,
|
|
N.SERIE_ACT,
|
|
N.NRACT,
|
|
N.DATAACT,
|
|
N.NNIR,
|
|
N.EXPLICATIA,
|
|
N.SCD,
|
|
N.ASCD,
|
|
N.SCC,
|
|
N.ASCC,
|
|
N.SUMA,
|
|
CAST(NULL AS VARCHAR2(100)) AS DENUMIRE_ARTICOL,
|
|
CAST(NULL AS VARCHAR2(40)) AS CODMAT,
|
|
CAST(NULL AS VARCHAR2(10)) AS UM,
|
|
CAST(NULL AS NUMBER) AS CANTITATE,
|
|
CAST(NULL AS NUMBER) AS PRET,
|
|
CAST(NULL AS NUMBER) AS VALOARE,
|
|
N.GESTOUT AS GESTIUNE,
|
|
N.SECTIE,
|
|
N.NRORD AS LUCRARE,
|
|
N.ID_SET,
|
|
CAST(NULL AS VARCHAR2(100)) AS TIP_RULAJ,
|
|
N.ID_ACT AS ID_ACT,
|
|
CAST(NULL AS NUMBER) AS ID_RUL
|
|
FROM note N
|
|
JOIN blocuri B ON B.COD = N.COD AND B.NNIR = N.NNIR AND B.SCC = N.SCC
|
|
AND B.ID_LUCRARE = NVL(N.ID_LUCRARE,0)
|
|
UNION ALL
|
|
SELECT COALESCE(B.DATA_GRUPARE, D.DATA_DOC, R.DATAACT) AS DATA_GRUPARE,
|
|
R.COD,
|
|
2 AS R_TIP,
|
|
CASE WHEN B.SCC IS NULL THEN 'RULAJ FARA NOTA' ELSE 'RULAJ' END AS TIP,
|
|
CAST(NULL AS DATE) AS DATAIREG,
|
|
R.FDOC,
|
|
R.SERIE_ACT,
|
|
R.NRACT,
|
|
R.DATAACT,
|
|
R.NNIR,
|
|
CAST(NULL AS VARCHAR2(200)) AS EXPLICATIA,
|
|
R.CONT_CHELT_CORESP AS SCD,
|
|
CAST(NULL AS VARCHAR2(40)) AS ASCD,
|
|
R.CONT AS SCC,
|
|
R.ACONT AS ASCC,
|
|
CAST(NULL AS NUMBER) AS SUMA,
|
|
R.DENUMIRE AS DENUMIRE_ARTICOL,
|
|
R.CODMAT,
|
|
R.UM,
|
|
R.CANTE AS CANTITATE,
|
|
R.PRET,
|
|
R.CANTE * R.PRET AS VALOARE,
|
|
R.CGEST || ' ' || R.NUME_GESTIUNE AS GESTIUNE,
|
|
R.SECTIE,
|
|
R.NRORD AS LUCRARE,
|
|
R.ID_SET,
|
|
R.TIP_RULAJ,
|
|
CAST(NULL AS NUMBER) AS ID_ACT,
|
|
R.ID_RUL
|
|
FROM iesiri R
|
|
LEFT JOIN blocuri B ON B.COD = R.COD AND B.NNIR = R.NNIR AND B.SCC = R.CONT
|
|
AND B.ID_LUCRARE = NVL(R.ID_LUCRARE,0)
|
|
LEFT JOIN doc_anchor D ON D.COD = R.COD AND D.NNIR = R.NNIR
|
|
)
|
|
ORDER BY DATA_GRUPARE, COD, NNIR, LUCRARE NULLS FIRST, SCC,
|
|
R_TIP, ID_ACT, ID_RUL;
|