SQL & PL/SQL

Hi everyone,

I have a table where a customer sends us data, sometimes due to i/o issues or whatever reasons the data might be corrupted. I want to have a sanity check in place to overcome this.

DB Version: 19.0

I posted an image above but I don't see it after I apply my changes, so here are the table creation scripts

create table xxc_temp(cust_num number,ship_to varchar2(30),po_num varchar2(30),release_num number,call_of_id number,creation_date date);
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYP', 10, 5, 
    TO_DATE('8/28/2023 3:04:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYR', 9, 1, 
    TO_DATE('8/27/2023 9:20:46 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYP', 7, 3, 
    TO_DATE('8/27/2023 11:05:46 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '167_61370597', 'F4VBYR', 1, 1, 
    TO_DATE('8/27/2023 3:05:46 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYP', 6, 2, 
    TO_DATE('8/26/2023 11:00:46 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYP', 5, 1, 
    TO_DATE('8/25/2023 11:05:46 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYQ', 4, 4, 
    TO_DATE('8/24/2023 3:05:46 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYQ', 3, 3, 
    TO_DATE('8/23/2023 8:05:26 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYQ', 2, 2, 
    TO_DATE('8/22/2023 4:05:46 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPS.XXC_TEMP
   (CUST_NUM, SHIP_TO, PO_NUM, RELEASE_NUM, CALL_OF_ID, 
    CREATION_DATE)
 Values
   (61370597, '010_61370597', 'F3VBYQ', 1, 1, 
    TO_DATE('8/21/2023 2:05:46 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;

The release_num is an incremental number for the cust_num & ship_to combination and call_of_id is an incremental number for a cust_num,ship_to & po_num. I want to find the missing release (or) call-off if any when my process runs on a given day

For eg., on 8/27 21:20 for cust_num 61370597 & ship_to 010_61370597 the release_num that's available in table is 9 but previously it was 7 at 11a, release_num 8 is missing so I want to return this information so I can take appropriate measures.

Appreciate if someone can provide their inputs.

edit: corrected the ship-to