STEP 10 Allocating Standard CAWIS format SDR numbers

Sept 2021 : NOTE - all required code for this process is found at :

FAA DATA IMPORT PROCEDURE.TXT

simply copy and place into your preferred SQL tool

–

Now that the FAA data has been moved into its proper columns and cleansed somewhat, we need to review each column for data integrity, and backfill some mandatory defaults.

Here we allocate SDR numbers in the traditional date/sequence-number format given to us by the FAA from 1974 thru 2006.

Note: we only use the Submitter-control-numbers as a unique identifier for each record on the FAA database, but we only use the SDR_NUMBER when referencing these reports internally

The TC SDR format (for Canadian records) is YYYYMMDDNNN where NNN is a sequential number for records received on any given date.

The FAA format is YYYYMMDDNNNNN. (2 extra digits for sequential numbers)  We prefix the new SDR number with current year & month YYYYMM, + day as "01", and then count sequentially upward from there.

If we are doing more than 1 upload in a given month, we would first acquire the max FAA SDR_number from the previous upload, grab the last 5 digits (sequence number), then add that to the ROWNUM, swapping it in for 0000 as seen in the 2nd statement below.

*/

SELECT MAX(SDR_NUMBER_NUM) FROM M72_SDR WHERE COUNTRY_CD = 'US' ;

--- Generate statements to update SDR-numbers to standard format, copy, paste and execute

SELECT 

'UPDATE M70_SDR_TRANSIT SET SDR_NUMBER_NUM = ''' || TO_CHAR(SYSDATE,'YYYYMM') || '01'

|| SUBSTR(REPLACE(TO_CHAR(ROWNUM + 0000,'99999'),' ','0'),2,5) ||

''' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''' ;'

FROM     M70_SDR_TRANSIT ;

/*

Note : It doesn’t really matter what the generated FAA SDR numbers are, as long as they

They would never conflict with Canadian or Australian SDR numbers due to length differences. We simply try to keep new FAA entries prefixed, with the year/month that we acquired the data.

If we ever exceeded, 99,999 records in a given upload, we would simply need to increment the Day by 1. The max upload ever seen in a given day was around 50,000. However, 4000-6000 records per month is more normal.

The output statements will look something like this

Open

/*