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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
DECLARE
opeId operation.ope_id%TYPE;
sessionId INT;
typeExtraction VARCHAR(10);
suiviBatch INT;
dateDebut VARCHAR(10);
dateFin VARCHAR(10);
CURSOR cursorOperationEtape IS SELECT
OPERATION.OPE_ID
FROM
OPERATION INNER JOIN TYPE_OPERATION
ON OPERATION.TYP_TOP_ID = TYPE_OPERATION.TOP_ID
LEFT OUTER JOIN APUREMENT_INCIDENT_CAISSE
ON OPERATION.OPE_ID = APUREMENT_INCIDENT_CAISSE.OPE_ID
LEFT OUTER JOIN APUREMENT_INCIDENT_ATELIER
ON OPERATION.OPE_ID = APUREMENT_INCIDENT_ATELIER.OPE_ID
LEFT OUTER JOIN incident_atelier_reception
ON APUREMENT_INCIDENT_ATELIER.OPE_ID_IAR = incident_atelier_reception.OPE_ID
LEFT OUTER JOIN incident_caisse_reception
ON APUREMENT_INCIDENT_CAISSE.OPE_ID_ICR = incident_caisse_reception.OPE_ID
WHERE
ope_date_operation between dateDebut and dateFin
AND type_operation.TOP_STATUT_FINAL = 'Y'
AND (OPERATION.ope_num_operation <> OPERATION.ope_code_regroupement
OR ope_statut IN ('Annulé','Supprimé'))
AND (TOP_IDENTIFIANT not in ('INCCAEXD','INCCAEXE','INCATBED','INCATBEE'))
AND ((incident_caisse_reception.ICR_MT_RESTANT_APURER IS NULL AND incident_atelier_reception.IAR_MT_RESTANT_APURER IS NULL)
OR incident_caisse_reception.ICR_MT_RESTANT_APURER = 0
OR incident_atelier_reception.IAR_MT_RESTANT_APURER = 0
);
CURSOR cursorListePere(fils number) is SELECT
ope_id, ope_num_operation
FROM operation
START WITH ope_id=fils
CONNECT BY PRIOR ope_id_precedente = ope_id;
BEGIN
sessionId := ?;
typeExtraction := ?;
suiviBatch := ?;
dateDebut := ?;
dateFin := ?;
FOR vOperationAEtape IN cursorOperationEtape LOOP
FOR pereId IN cursorListePere(vOperationAEtape.ope_id) LOOP
INSERT INTO EXTR_ID_ARC_PUR (EXTR_ID,OPE_ID,EXT_TYPE_EXTRACTION,ID_FONC,SES_ID,SVBATCH_ID) values (S_EXTR_ID_ARC_PUR.NEXTVAL,pereId.OPE_ID, typeExtraction, pereId.OPE_NUM_OPERATION, sessionId,suiviBatch);
END LOOP;
END LOOP;
INSERT INTO EXTR_ID_ARC_PUR (EXTR_ID,OPE_ID,EXT_TYPE_EXTRACTION,ID_FONC,SES_ID,SVBATCH_ID)
(
SELECT S_EXTR_ID_ARC_PUR.NEXTVAL,OPE_ID, typeExtraction, OPE_NUM_OPERATION, sessionId,suiviBatch
FROM operation
INNER JOIN type_operation
ON operation.typ_top_id = type_operation.top_id
WHERE
ope_date_operation between dateDebut and dateFin
AND type_operation.TOP_STATUT_FINAL = 'Y'
AND ope_num_operation = ope_code_regroupement
AND ope_statut NOT IN ('Annulé','Supprimé')
);
DELETE EXTR_ID_ARC_PUR EXTR_1
WHERE SES_ID = sessionId AND ROWID > (
SELECT MIN(ROWID)
FROM EXTR_ID_ARC_PUR EXTR_2
WHERE EXTR_2.OPE_ID = EXTR_1.OPE_ID AND
EXTR_2.ID_FONC = EXTR_1.ID_FONC AND
EXTR_2.SES_ID = EXTR_2.SES_ID
);
END; |
