Hi,
I've created a procedure containing 4 others procedures (INSERT INTO Table...)

CREATE OR REPLACE PROCEDURE ALLPROC AS
BEGIN
PROC1;
PROC2;
PROC3;
PROC4;
END ALLPROC;

Can you tell me if those 4 procedures are running in parallell or one after the other ?
I've got the feeling that they are running in parallell which creates a problem for PROC4...
Can you please clarify this?

To be clearer, PROC4 is based on the result of PROC1/2/3 and return incorrect calculation when included in the ALLPROC Procedure.
When excluding it from ALLPROC and running it manually, I've got correct calculation.

Another issue that happens sometimes is that the calculation from PROC4 is double or triple of the expected value.
This does not happens if I run ALLPROC (excl PROC4); close Oracle SQL developer; reopen it and run PROC4 separately.

Any idea why this is happening and/or what I should do to have correct output??

Thanks


More details:

PROC1=

INSERT INTO DESTINATION_TABLE
select '1 Total Shipment Volume TYPE AA' as Type,
REGION,
TRUNC(EVENT_DATETIME)as RepPeriod,
count(distinct transaction_number) as VOLUME
from SOURCE_TABLE1
WHERE EVENT_DATETIME >= TRUNC(SYSDATE - 1) AND EVENT_DATETIME < TRUNC(SYSDATE)
GROUP BY REGION,TRUNC(EVENT_DATETIME);

PROC2=
INSERT INTO DESTINATION_TABLE
Select '2 Volume TYPE AAB' as Type,
REGION,
TRUNC (EVENT_DATETIME) as RepPeriod,
count(transaction_number) as VOLUME
from SOURCE_TABLE1
WHERE EVENT_DATETIME >= TRUNC(SYSDATE - 1) AND EVENT_DATETIME < TRUNC(SYSDATE)
AND MASS_UPDATE='0'
AND CAT=’xxx’
GROUP BY REGION,TRUNC(EVENT_DATETIME);

PROC3=
INSERT INTO DESTINATION_TABLE
Select '3 VOLUME TYPE CC' as Type,
REGION,
TRUNC (EVENT_DATETIME) as RepPeriod,
count(transaction_number) as VOLUME
from SOURCE_TABLE1
WHERE EVENT_DATETIME >= TRUNC(SYSDATE - 1) AND EVENT_DATETIME < TRUNC(SYSDATE)
AND MASS_UPDATE='1'
AND CAT = ‘yyyyy’
GROUP BY REGION,TRUNC(EVENT_DATETIME);

PROC4=
INSERT INTO DESTINATION_TABLE
select '4 Total DVRA Volumes for productivity (1+2-3)' as Type,
a.REGION,
a.REPPERIOD,
a.VOL1-NVL(b.VOLUME,0) as volume
from
(select distinct REGION,REPPERIOD,sum(VOLUME) as VOL1
from DESTINATION_TABLE
where TYPE like '%TYPE AA%'
group by REGION,REPPERIOD
)a
left join
(select REGION,REPPERIOD,VOLUME
from DESTINATION_TABLE
where Type like '3%'
)b
on a.REGION=b.REGION and a.REPPERIOD=b.REPPERIOD
where a.VOL1-NVL(b.VOLUME,0)<>0
and a.REPPERIOD=TRUNC(SYSDATE -1)
;

The issue is related to PROC4 that brings 2 duplicates lines per region for the same repperiod where the sum of volume is also in double.