STEP 16 AIRCRAFT MANUFACTURER AND MODEL CONVERSION

Owned by kurt.martin
Last updated: Aug 30, 2022
8 min read

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

--

 

 

 

Here we attempt to convert as much of the manufacturer and model data as possible from the FAA standards (6/12 characters) over to the CAWIS (20/20 characters) standards (found on Table Y62_PRODUCT_MAKE_MODEL)

There are multiple redundant lookups in the model conversion sections since no single method ever cleans up all the incoming FAA model data on a single pass.

IDENTIFY FAA AIRCRAFT MANUFACTURERS NOT ON Z980 TERM CONVERSION

   Are there any aircraft manufacturers in this batch, that we've never seen before? (this rarely occurs)

 

   If yes, we check to see if its similar to anything CAWIS already has on Y60/Y61/Y62 - or - take the aircraft tail mark go to the FAA registry and acquire the full spelling of that new manufacturer. We would then go to the CAWIS code tables and ADD the new manufacturer and execute the Z980 insert statement generated down below, taking care to replace the TO_TXT

   ( found at the 2nd occurence of M70.AIRCRAFT_MANUFACTURER, in the insert statement, with whatever the full spelling of this manufacturer we just acquired )

   Notes :

For instances where the FAA register returns an amateur built or ultralite aircraft, it’s acceptable to simply put AMTR into the to_txt of the Z980_term_conversion record.

When a Z980 manufacturer spell check record (Term-type_cds 1,2,3,4) is ultimately found to contain a value in the To_txt column THAT DOES NOT EXIST on the Y60_manufacturer table, that record is deleted by overnight script.

*/

SELECT DISTINCT(M70.AIRCRAFT_MANUFACTURER) , AIRCRAFT_MARK_ID, AIRCRAFT_MODEL,

'INSERT INTO Z980_TERM_CONVERSION VALUES(''1'', ''' || M70.AIRCRAFT_MANUFACTURER ||

''' ,  ''' || M70.AIRCRAFT_MANUFACTURER ||''', 0, ''ADMIN'', SYSDATE ,SYSDATE ) ; ' INS1

FROM   M70_SDR_TRANSIT  M70

WHERE  TRIM(M70.AIRCRAFT_MANUFACTURER) IS NOT NULL

AND    NOT EXISTS

(SELECT Z980.FROM_TXT

 FROM   Z980_TERM_CONVERSION Z980

 WHERE  Z980.TERM_TYPE_CD = '1'

 AND    TRIM(Z980.FROM_TXT) = M70.AIRCRAFT_MANUFACTURER);

/*

 

CONVERT AIRCRAFT MANUFACTURER TEXT TO CAWIS STANDARD

Here, we determine if the FAA spelling of the Aircraft manufacturer already exists on the CAWIS Y60_MANUFACTURER table, and if not we swap it for its equivalent from the Z980_TERM_CONVERSION table

 

TERM_TYPE_Codes

1 = aircraft

2 = engine

3 = propeller

4 = equipment/component

 

/* UPDATE Y72 MODEL CONVERSION WHERE POSSIBLE */
INSERT INTO Y72_FAA_MODEL_CONVERSION
SELECT M70.AIRCRAFT_MANUFACTURER,
M70.AIRCRAFT_MODEL,
'A',
Y62.MANUFACTURER_ID,
Y62.MODEL_ID,
'Y','ADMIN',TRUNC(SYSDATE),TRUNC(SYSDATE)
FROM M70_SDR_TRANSIT M70, Y62_PRODUCT_MAKE_MODEL Y62
WHERE M70.SDR_NUMBER_NUM = (SELECT X.SDR_NUMBER_NUM FROM M70_SDR_TRANSIT X
WHERE X.AIRCRAFT_MANUFACTURER = M70.AIRCRAFT_MANUFACTURER
AND X.AIRCRAFT_MODEL = M70.AIRCRAFT_MODEL
AND ROWNUM = 1)
AND NOT EXISTS (SELECT 1 FROM Y72_FAA_MODEL_CONVERSION X
WHERE X.PRODUCT_TYPE_CD = 'A'
AND X.FAA_MANUFACTURER = M70.AIRCRAFT_MANUFACTURER
AND X.FAA_MODEL_ID = M70.AIRCRAFT_MODEL)
AND Y62.PRODUCT_TYPE_CD = 'A'
AND Y62.MANUFACTURER_ID = (SELECT X.MANUFACTURER_ID FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'A'
AND DATA_COMPRESSION(X.MODEL_ID) = M70.AIRCRAFT_MODEL
AND ROWNUM = 1)
AND Y62.MODEL_ID = (SELECT X.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'A'
AND DATA_COMPRESSION(X.MODEL_ID) = M70.AIRCRAFT_MODEL
AND ROWNUM = 1);

COMMIT;


AIRCRAFT - FULL CONVERT WHERE POSSIBLE

*/

UPDATE M70_SDR_TRANSIT X
SET AIRCRAFT_MANUFACTURER =
(SELECT GET_MANUFACTURER_NAME(Y.MANUFACTURER_ID)
FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'A'
AND Y.FAA_MANUFACTURER = TRIM(X.AIRCRAFT_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.AIRCRAFT_MODEL)) ,
AIRCRAFT_MANUFACTURER_ID =
(SELECT Y.MANUFACTURER_ID FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'A'
AND Y.FAA_MANUFACTURER = TRIM(X.AIRCRAFT_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.AIRCRAFT_MODEL)) ,
AIRCRAFT_MODEL =
(SELECT Y.MODEL_ID FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'A'
AND Y.FAA_MANUFACTURER = TRIM(X.AIRCRAFT_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.AIRCRAFT_MODEL))
WHERE TRIM(AIRCRAFT_MODEL) IS NOT NULL
AND AIRCRAFT_MANUFACTURER_ID IS NULL
AND EXISTS
(SELECT 1 FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'A'
AND Y.FAA_MANUFACTURER = TRIM(X.AIRCRAFT_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.AIRCRAFT_MODEL) ) ;

 

UPDATE M70_SDR_TRANSIT M70 SET M70.AIRCRAFT_MANUFACTURER  =

           (SELECT TO_TXT FROM Z980_TERM_CONVERSION

            WHERE  TERM_TYPE_CD =  '1'

            AND    FROM_TXT = M70.AIRCRAFT_MANUFACTURER)

WHERE M70.AIRCRAFT_MANUFACTURER  NOT IN

            (SELECT GET_MANUFACTURER_NAME(MANUFACTURER_ID)

             FROM Y62_PRODUCT_MAKE_MODEL WHERE PRODUCT_TYPE_CD = 'A')

AND  EXISTS (SELECT 1 FROM Z980_TERM_CONVERSION

             WHERE  TERM_TYPE_CD =  '1'

             AND    FROM_TXT = M70.AIRCRAFT_MANUFACTURER) ;

            

COMMIT;

 

 

/*

SET AIRCRAFT MANUFACTURER_ID

Here we determine the CAWIS numeric Aircraft_manufacturer_id, if possible, having (hopefully) determined the correct manufacturer-text in the previous steps.

*/              

 

UPDATE M70_SDR_TRANSIT

SET AIRCRAFT_MANUFACTURER_ID =

      (SELECT Y60.MANUFACTURER_ID FROM Y60_MANUFACTURER Y60

       WHERE GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID) = AIRCRAFT_MANUFACTURER)

WHERE NVL(AIRCRAFT_MANUFACTURER_ID,0) = 0

AND   TRIM(AIRCRAFT_MANUFACTURER) IS NOT NULL ;

COMMIT;

 

/*



SET AIRCRAFT MODEL

Here we update the Aircraft-model IF POSSIBLE, WHERE a MFG/MODEL MATCH IS FOUND USING DATA COMPRESSION against Y62_PRODUCT_MAKE_MODEL.

Note: all models on M70 are already in a data compressed state

*/   

UPDATE M70_SDR_TRANSIT M70

SET M70.AIRCRAFT_MODEL =        --- UPDATE WITH UNCOMPRESSED MODEL

   (SELECT Y62.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL Y62

    WHERE Y62.PRODUCT_TYPE_CD            = 'A'

    AND   DATA_COMPRESSION(Y62.MODEL_ID) = M70.AIRCRAFT_MODEL

    AND   Y62.MANUFACTURER_ID = M70.AIRCRAFT_MANUFACTURER_ID)

WHERE M70.AIRCRAFT_MODEL =      --- IF IT EXISTS ON A DATA COMPRESSION LOOKUP

   (SELECT DATA_COMPRESSION(Y62.MODEL_ID) FROM Y62_PRODUCT_MAKE_MODEL Y62

    WHERE Y62.PRODUCT_TYPE_CD            = 'A'

    AND   DATA_COMPRESSION(Y62.MODEL_ID) = M70.AIRCRAFT_MODEL

    AND   Y62.MANUFACTURER_ID            = M70.AIRCRAFT_MANUFACTURER_ID)

AND NOT EXISTS                  --- AND THE CURRENT M70 AIRCRAFT MFG/MODEL IS NOT ON Y62

    (SELECT  1 FROM Y62_PRODUCT_MAKE_MODEL Y62

     WHERE   Y62.PRODUCT_TYPE_CD = 'A'

     AND     Y62.MANUFACTURER_ID = M70.AIRCRAFT_MANUFACTURER_ID 

     AND     Y62.MODEL_ID        = M70.AIRCRAFT_MODEL ) ;                                                         

COMMIT;

 

 

/*

SET AIRCRAFT MODEL

Here we update the Aircraft-model if a match can be found on Y72_FAA_MODEL_CONVERSION using FAA MODEL / CAWIS MFG

 

*/                                                            

UPDATE M70_SDR_TRANSIT M70

SET M70.AIRCRAFT_MODEL =

             (SELECT MAX(Y72.MODEL_ID) FROM Y72_FAA_MODEL_CONVERSION Y72

              WHERE Y72.PRODUCT_TYPE_CD = 'A'

              AND   Y72.FAA_MODEL_ID    = M70.AIRCRAFT_MODEL

              AND   Y72.MANUFACTURER_ID = M70.AIRCRAFT_MANUFACTURER_ID )

WHERE EXISTS (SELECT 1 FROM Y72_FAA_MODEL_CONVERSION Y72

              WHERE Y72.PRODUCT_TYPE_CD = 'A'

              AND   Y72.MANUFACTURER_ID = M70.AIRCRAFT_MANUFACTURER_ID

              AND   Y72.FAA_MODEL_ID = M70.AIRCRAFT_MODEL) 

AND NOT EXISTS               

    (SELECT  1 FROM Y62_PRODUCT_MAKE_MODEL Y62

     WHERE   Y62.PRODUCT_TYPE_CD = 'A'

     AND     Y62.MANUFACTURER_ID = M70.AIRCRAFT_MANUFACTURER_ID 

     AND     Y62.MODEL_ID        = M70.AIRCRAFT_MODEL ) ;                                                        

COMMIT;

 

/*

DOUBLE CHECK AIRCRAFT-MANUFACTURER ID'S WHERE AIRCRAFT-MANUFACTURER IS NOT NULL, BUT AIRCRAFT-MANUFACTURER-ID IS NULL

*/     

--- HAVE WE GOT ANY UNCONVERTED AIRCRAFT MANUFACTURERS ?

SELECT * FROM M70_SDR_TRANSIT

WHERE NVL(AIRCRAFT_MANUFACTURER_ID,0) = 0

AND   TRIM(AIRCRAFT_MANUFACTURER) IS NOT NULL ;

 

--- ENTER A MANUFACTURER_ID MANUALLY, IF WE CAN DETERMINE IT

SELECT DISTINCT(M70.AIRCRAFT_MANUFACTURER) ,

       Z980.TO_TXT,

       Y60.MANUFACTURER_ID,

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER_ID = ' || Y60.MANUFACTURER_ID ||

' WHERE NVL(AIRCRAFT_MANUFACTURER_ID ,0) = 0 AND AIRCRAFT_MANUFACTURER = '''

|| M70.AIRCRAFT_MANUFACTURER || ''' ; ' AS UPD1

FROM   M70_SDR_TRANSIT      M70,

       Z980_TERM_CONVERSION Z980,

       Y60_MANUFACTURER     Y60

WHERE  Z980.TERM_TYPE_CD     = '1'

AND    Z980.FROM_TXT         = TRIM(M70.AIRCRAFT_MANUFACTURER)

AND    GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID)

                             = TRIM(Z980.TO_TXT)

AND    NVL(M70.AIRCRAFT_MANUFACTURER_ID,0) = 0 ;

 


/*

DOUBLE CHECK AIRCRAFT MODELS

*/     

-- Mismatch against TC CAWIS-MODEL, BUT matches FAA model

SELECT DISTINCT(M70.AIRCRAFT_MODEL) ,

-- Update model

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = '''     || Y72.MODEL_ID ||

''' WHERE AIRCRAFT_MODEL = ''' || M70.AIRCRAFT_MODEL || ''' ; ' AS UPD1 ,

-- Possibly insert problem model into y72, so it wont come up again

' INSERT INTO Y72_FAA_MODEL_CONVERSION VALUES (''' || Y72.FAA_MANUFACTURER || ''',''' ||

Y72.FAA_MODEL_ID || ''',''A'',' || M70.AIRCRAFT_MANUFACTURER_ID || ',''' ||

M70.AIRCRAFT_MODEL || ''',''Y'',''ADMIN'',SYSDATE,SYSDATE); '  AS INS1

FROM   M70_SDR_TRANSIT          M70,

       Y72_FAA_MODEL_CONVERSION Y72

WHERE  Y72.PRODUCT_TYPE_CD  = 'A'

AND    Y72.MANUFACTURER_ID  = NVL(M70.AIRCRAFT_MANUFACTURER_ID,0)

AND    Y72.FAA_MODEL_ID     = NVL(M70.AIRCRAFT_MODEL,'X')

AND    Y72.MODEL_ID        <> NVL(M70.AIRCRAFT_MODEL,'X') ;

 

 



-- Aircraft mfg/model still not found on Y62 table 1

SELECT DISTINCT(M70.AIRCRAFT_MODEL) , M70.AIRCRAFT_MANUFACTURER_ID,

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = '''          || Y72.MODEL_ID ||

''' , AIRCRAFT_MANUFACTURER_ID = ' || Y72.MANUFACTURER_ID ||

' WHERE AIRCRAFT_MODEL = '''       || M70.AIRCRAFT_MODEL  || ''' ; ' AS UPD1,

' INSERT INTO Y72_FAA_MODEL_CONVERSION VALUES (''' || Y72.FAA_MANUFACTURER || ''',''' ||

Y72.FAA_MODEL_ID || ''',''A'',' || M70.AIRCRAFT_MANUFACTURER_ID || ',''' ||

M70.AIRCRAFT_MODEL || ''',''Y'',''ADMIN'',SYSDATE,SYSDATE); '  AS INS1

FROM   M70_SDR_TRANSIT          M70,

       Y72_FAA_MODEL_CONVERSION Y72

WHERE  Y72.PRODUCT_TYPE_CD  = 'A'

AND    Y72.MANUFACTURER_ID  = M70.AIRCRAFT_MANUFACTURER_ID

AND    Y72.FAA_MODEL_ID     = NVL(M70.AIRCRAFT_MODEL,'X')

AND    Y72.MODEL_ID        <> NVL(M70.AIRCRAFT_MODEL,'X')

AND NOT EXISTS

(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL X

 WHERE  X.PRODUCT_TYPE_CD              = 'A'

 AND    X.MANUFACTURER_ID              = M70.AIRCRAFT_MANUFACTURER_ID

 AND    X.MODEL_ID                     = M70.AIRCRAFT_MODEL) ;

-- Aircraft mfg/model still not found on Y62 table 2

-- The model was found, but the Aircraft_manufacturer_id needs updating

SELECT DISTINCT(M70.AIRCRAFT_MODEL) , AIRCRAFT_MANUFACTURER_ID , AIRCRAFT_MANUFACTURER , 'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = '''          || Y62.MODEL_ID ||

''' , AIRCRAFT_MANUFACTURER_ID = ' || Y62.MANUFACTURER_ID ||

' WHERE AIRCRAFT_MODEL = '''       || M70.AIRCRAFT_MODEL  || ''' ; ' AS UPD1

FROM   M70_SDR_TRANSIT          M70,

       Y62_PRODUCT_MAKE_MODEL   Y62

WHERE  Y62.PRODUCT_TYPE_CD  = 'A'

AND    DATA_COMPRESSION(Y62.MODEL_ID) = M70.AIRCRAFT_MODEL

AND    Y62.MODEL_ID                  <> M70.AIRCRAFT_MODEL

AND NOT EXISTS

(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL X

 WHERE  X.PRODUCT_TYPE_CD              = 'A'

 AND    X.MANUFACTURER_ID              = M70.AIRCRAFT_MANUFACTURER_ID

 AND    X.MODEL_ID                     = M70.AIRCRAFT_MODEL) ;

 



/*

--- BELL HELICOPTERS ONLY

We have Bell Textron Canada (mfg-id=266), and Bell Textron USA (267) on the y62 table. Very often, these two manufacturer ID's get intermixed during the conversion

*/

SELECT DISTINCT(M70.AIRCRAFT_MANUFACTURER),

       M70.AIRCRAFT_MODEL,

       M70.AIRCRAFT_MANUFACTURER_ID  ,

' UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER_ID = 267 WHERE AIRCRAFT_MODEL = ''' ||

M70.AIRCRAFT_MODEL || ''';' AS UPD1

FROM   M70_SDR_TRANSIT  M70

WHERE  M70.AIRCRAFT_MANUFACTURER_ID = 266

AND  EXISTS

(SELECT Y62.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL Y62

 WHERE  Y62.MANUFACTURER_ID = 267

 AND    DATA_COMPRESSION(Y62.MODEL_ID) = DATA_COMPRESSION(M70.AIRCRAFT_MODEL)) ;

/*

FILL DEFAULTS

   This is a PRECAUTIONARY STEP ONLY in case the conversion has nulled something out. Any bad models will be identified in the final part of this Step.

*/     

 

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER_ID = 000

WHERE  AIRCRAFT_MANUFACTURER_ID IS NULL

AND    TRIM(AIRCRAFT_MODEL) IS NOT NULL ;

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER = 'XXX'

WHERE  TRIM(AIRCRAFT_MANUFACTURER) IS NULL

AND    TRIM(AIRCRAFT_MODEL) IS NOT NULL ;

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = 'XXX'

WHERE  TRIM(AIRCRAFT_MODEL) IS NULL

AND    AIRCRAFT_MANUFACTURER_ID IS NOT NULL;

COMMIT;

 

/* 

BOMBARDIER issue only

Most Bombardier models are replicated in CAWIS under various manufacturers.

(Dehavilland - Can, Viking, Airbus Canada, MHI RJ)

Due to the limitations of the WSDRS online query/reporting configuration, ( you have to specify EVERY manufacturer/model combination you're searching for, rather than just querying by MODEL) it was decided to move ALL incoming Bombardier-related reports under the manufacturer “Bombardier” for simpler query availability.

Any given model listed under multiple manufacturers is absolutely equivalent to the others regardless of manufacturer designation.

*/

UPDATE M70_SDR_TRANSIT

SET   AIRCRAFT_MANUFACTURER_ID = 310

WHERE AIRCRAFT_MANUFACTURER_ID <> 310

AND   AIRCRAFT_MODEL IN (SELECT MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL WHERE  PRODUCT_TYPE_CD = 'A'

AND    MANUFACTURER_ID = 310) ;

COMMIT;

/*

Final Aircraft manufacturer model check.

    Now, We generate a report on any aircraft mfg/model combination in the FAA data that is NOT FOUND on Y62_PRODUCT_MAKE_MODEL. We also generate a series of Select, update and insert statements to make any necessary repairs.

        If the aircraft mfg/model is NOT found on the y62 table our alternatives are to:   

Lookup all models available for that Manufacturer and possibly assign the closest model to the one we have, using the Update statement provided. It may also be necessary to update the manufacturer_id at this point.

       or

Insert the new model into the TR24, Y61, Y62, Y72 tables - paying careful respect to the spacing and punctuation conventions already in place for models under this Manufacturer.      

    NOTE: WE WOULD NEVER CREATE NEW MODELS FOR CANADIAN PRODUCTS - such as BOMBARDIER, DEHAVILLAND, MHI RJ, VIKING, AIRBUS CANADA, BELL HELICOPTER, PRATT & WHITNEY CANADA - as all of the legitimate models for these entities are carefully monitored by Continuing Airworthiness and should already be on available on the tables. A correction to the FAA data would always be made in these instances.

 

*/     

SELECT DISTINCT(M70.AIRCRAFT_MANUFACTURER),

       M70.AIRCRAFT_MODEL,

       M70.AIRCRAFT_MANUFACTURER_ID  ,

       GET_MANUFACTURER_NAME(M70.AIRCRAFT_MANUFACTURER_ID) ,

 CHR(13) || '--------------' || CHR(13) ||

'SELECT GET_MANUFACTURER_NAME(Y62.MANUFACTURER_ID) , Y62.* FROM Y62_PRODUCT_MAKE_MODEL Y62 ' ||

' WHERE DATA_COMPRESSION(Y62.MODEL_ID) LIKE ''' || M70.AIRCRAFT_MODEL || ''' ; ' || CHR(13) ||

 

' UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = ''' || M70.AIRCRAFT_MODEL ||

''', AIRCRAFT_MANUFACTURER = ''' || M70.AIRCRAFT_MANUFACTURER ||

''', AIRCRAFT_MANUFACTURER_ID = ' || M70.AIRCRAFT_MANUFACTURER_ID || ' WHERE AIRCRAFT_MODEL = ''' ||

M70.AIRCRAFT_MODEL || ''' ;' ||   CHR(13) ||

 

'INSERT INTO TR24_MODEL VALUES ( ''' || M70.AIRCRAFT_MODEL || ''',0,''ADMIN'',SYSDATE,SYSDATE); ' || CHR(13) ||

 

'INSERT INTO Y61_MAKE_MODEL VALUES (' || M70.AIRCRAFT_MANUFACTURER_ID || ', ''' ||

M70.AIRCRAFT_MODEL || ''' , ''' || DATA_COMPRESSION(M70.AIRCRAFT_MODEL) || ''' , 0,''ADMIN'',SYSDATE,SYSDATE);'   || CHR(13) ||

 

'INSERT INTO Y62_PRODUCT_MAKE_MODEL VALUES (''A'','''|| M70.AIRCRAFT_MODEL || ''',' || M70.AIRCRAFT_MANUFACTURER_ID ||

',''1'',NULL,NULL,NULL,NULL,''NOT IDENTIFIED'',SYSDATE,''ADMIN'',''FROM FAA SDR DATA'',0,''ADMIN'',' ||

'SYSDATE,SYSDATE,''N'',''999999'',NULL );' || CHR(13) ||

' INSERT INTO Y72_FAA_MODEL_CONVERSION VALUES (''' || M70.AIRCRAFT_MANUFACTURER || ''',''' ||

M70.AIRCRAFT_MODEL || ''',''A'',' || M70.AIRCRAFT_MANUFACTURER_ID || ',''' ||

M70.AIRCRAFT_MODEL || ''',''Y'',''ADMIN'',SYSDATE,SYSDATE); ' || CHR(13) || CHR(13)  AS INS1

 

FROM   M70_SDR_TRANSIT  M70

WHERE  M70.AIRCRAFT_MODEL IS NOT NULL

AND NOT EXISTS

(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62

 WHERE  Y62.PRODUCT_TYPE_CD = 'A'

 AND    Y62.MANUFACTURER_ID = NVL(M70.AIRCRAFT_MANUFACTURER_ID,1)

 AND    Y62.MODEL_ID        = M70.AIRCRAFT_MODEL)

 ORDER BY M70.AIRCRAFT_MANUFACTURER, M70.AIRCRAFT_MODEL ;

/*

 

At this point, our Aircraft manufacturers and models are appearing in the CAWIS standards.

Note: Looking at the repetitiveness in aircraft_mark_id column, its perfectly normal to find dozens of reports on the same aircraft in a given batch. When inspections are performed, reports are filed on each individual problem part on that aircraft.