1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
|
CREATE OR REPLACE FORCE VIEW "IMPAYE" ("CODE_UR_CYCLE1", "CODE_CATEGORIE_CYCLE1", "NOMBRE_FACT_EMISE_CYCLE1", "NOMBRE_FACT_IMPAYE_CYCLE1", "MONTANT_FACT_IMPAYE_CYCLE1", "NOMBRE_AV_IMPAYE_CYCLE1") AS
SELECT CODE_UR_CYCLE1, CODE_CATEGORIE_CYCLE1, NOMBRE_FACT_EMISE_CYCLE1, NOMBRE_FACT_IMPAYE_CYCLE1, MONTANT_FACT_IMPAYE_CYCLE1, NOMBRE_AV_IMPAYE_CYCLE1
FROM
(
SELECT RC_UR_CODE AS CODE_UR_CYCLE1, SUBSTR (AGM_FACT_FPAYEUR, 1, 1) AS CODE_CATEGORIE_CYCLE1, COUNT (*) AS NOMBRE_FACT_IMPAYE_CYCLE1, SUM (AGM_FACT_IMPAYE) AS MONTANT_FACT_IMPAYE_CYCLE1
FROM AGM_ABONNES_GZ ABONNE, AGM_FACTURE_GZ FACTURE, CF_ABONNE ABONNE_CF
WHERE FACTURE.CF_AB_CODE = ABONNE.CF_AB_CODE AND FACTURE.CF_AB_REF = ABONNE_CF.CF_AB_REF AND FACTURE.CF_AB_CODE = ABONNE_CF.CF_AB_CODE AND FACTURE.CF_AB_REF = ABONNE.CF_AB_REF AND AGM_FACT_IMPAYE > 0 AND FACTURE.CF_AB_CODE = 'GZ' AND ADD_MONTHS (P1.get_p, -1) = CF_FACT_DATFACT
GROUP BY RC_UR_CODE, SUBSTR (AGM_FACT_FPAYEUR, 1, 1)
),(
SELECT RC_UR_CODE AS CODE_UR2, SUBSTR (AGM_FACT_FPAYEUR, 1, 1) AS CODE_CATEGORIE2, COUNT (*) AS NOMBRE_FACT_EMISE_CYCLE1
FROM AGM_FACTURE_GZ FACTURE, CF_ABONNE ABONNE
WHERE FACTURE.CF_AB_REF = ABONNE.CF_AB_REF AND FACTURE.CF_AB_CODE = ABONNE.CF_AB_CODE AND FACTURE.CF_AB_CODE = 'GZ' AND ADD_MONTHS (P1.get_p, -1) = CF_FACT_DATFACT
GROUP BY RC_UR_CODE, SUBSTR (AGM_FACT_FPAYEUR, 1,1)
),(
SELECT RC_UR_CODE AS CODE_UR3, SUBSTR (AGM_FACT_FPAYEUR, 1, 1) AS CODE_CATEGORIE3, COUNT (*) AS NOMBRE_AV_IMPAYE_CYCLE1
FROM AGM_ABONNES_GZ ABONNE, AGM_FACTURE_GZ FACTURE, CF_ABONNE ABONNE_CF
WHERE FACTURE.CF_AB_CODE = ABONNE.CF_AB_CODE AND FACTURE.CF_AB_REF = ABONNE_CF.CF_AB_REF AND FACTURE.CF_AB_CODE = ABONNE_CF.CF_AB_CODE AND FACTURE.CF_AB_REF = ABONNE.CF_AB_REF AND AGM_FACT_IMPAYE > AGM_AB_AVPERC AND FACTURE.CF_AB_CODE = 'GZ' AND ADD_MONTHS (P1.get_p, -1) = CF_FACT_DATFACT
GROUP BY RC_UR_CODE, SUBSTR (AGM_FACT_FPAYEUR, 1, 1)
)
WHERE CODE_UR3 = CODE_UR_CYCLE1 AND CODE_UR3 = CODE_UR2 AND CODE_CATEGORIE3 = CODE_CATEGORIE2 AND CODE_CATEGORIE3 = CODE_CATEGORIE_CYCLE1
ORDER BY CODE_UR_CYCLE1, CODE_CATEGORIE_CYCLE1 |
Partager