STEP 18 PROPELLER MANUFACTURER AND MODEL CONVERSION

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

/*

Same procedure as in Steps 16/17 for Aircraft & engine model conversions

*/

SELECT DISTINCT(M70.PROPELLER_MANUFACTURER) , AIRCRAFT_MARK_ID, PROPELLER_MODEL,

'INSERT INTO Z980_TERM_CONVERSION VALUES(''3'', ''' || M70.PROPELLER_MANUFACTURER ||

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

FROM   M70_SDR_TRANSIT  M70

WHERE  TRIM(M70.PROPELLER_MANUFACTURER) IS NOT NULL

AND    NOT EXISTS

(SELECT Z980.FROM_TXT

 FROM   Z980_TERM_CONVERSION Z980

 WHERE  Z980.TERM_TYPE_CD = '3'

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

/* UPDATE Y72 MODEL CONVERSION WHERE POSSIBLE */
INSERT INTO Y72_FAA_MODEL_CONVERSION
SELECT M70.PROPELLER_MANUFACTURER,
M70.PROPELLER_MODEL,
'P',
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.PROPELLER_MANUFACTURER = M70.PROPELLER_MANUFACTURER
AND X.PROPELLER_MODEL = M70.PROPELLER_MODEL
AND ROWNUM = 1)
AND NOT EXISTS (SELECT 1 FROM Y72_FAA_MODEL_CONVERSION X
WHERE X.PRODUCT_TYPE_CD = 'P'
AND X.FAA_MANUFACTURER = M70.PROPELLER_MANUFACTURER
AND X.FAA_MODEL_ID = M70.PROPELLER_MODEL)
AND Y62.PRODUCT_TYPE_CD = 'P'
AND Y62.MANUFACTURER_ID = (SELECT X.MANUFACTURER_ID FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'P'
AND DATA_COMPRESSION(X.MODEL_ID) = M70.PROPELLER_MODEL
AND ROWNUM = 1)
AND Y62.MODEL_ID = (SELECT X.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'P'
AND DATA_COMPRESSION(X.MODEL_ID) = M70.PROPELLER_MODEL
AND ROWNUM = 1);

/*
PROPELLER - FULL CONVERT WHERE POSSIBLE
*/
UPDATE M70_SDR_TRANSIT X
SET PROPELLER_MANUFACTURER =
(SELECT GET_MANUFACTURER_NAME(Y.MANUFACTURER_ID)
FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'P'
AND Y.FAA_MANUFACTURER = TRIM(X.PROPELLER_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.PROPELLER_MODEL)) ,
PROPELLER_MANUFACTURER_ID =
(SELECT Y.MANUFACTURER_ID FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'P'
AND Y.FAA_MANUFACTURER = TRIM(X.PROPELLER_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.PROPELLER_MODEL)) ,
PROPELLER_MODEL =
(SELECT Y.MODEL_ID FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'P'
AND Y.FAA_MANUFACTURER = TRIM(X.PROPELLER_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.PROPELLER_MODEL))
WHERE TRIM(PROPELLER_MODEL) IS NOT NULL
AND PROPELLER_MANUFACTURER_ID IS NULL
AND EXISTS
(SELECT 1 FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'P'
AND Y.FAA_MANUFACTURER = TRIM(X.PROPELLER_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.PROPELLER_MODEL) ) ;

/*

CONVERT PROPELLER MANUFACTURER TEXT TO CAWIS STANDARD

*/

UPDATE M70_SDR_TRANSIT M70

SET M70.PROPELLER_MANUFACTURER  =

           (SELECT TO_TXT FROM Z980_TERM_CONVERSION

            WHERE  TERM_TYPE_CD =  '3'

            AND    FROM_TXT = M70.PROPELLER_MANUFACTURER)

WHERE M70.PROPELLER_MANUFACTURER  NOT IN

            (SELECT GET_MANUFACTURER_NAME(MANUFACTURER_ID)

             FROM Y62_PRODUCT_MAKE_MODEL WHERE PRODUCT_TYPE_CD = 'P')

AND  EXISTS (SELECT 1 FROM Z980_TERM_CONVERSION

             WHERE  TERM_TYPE_CD =  '3'

             AND    FROM_TXT = M70.PROPELLER_MANUFACTURER) ;

COMMIT;

/*

SET PROPELLER MANUFACTURER_ID

*/             

UPDATE M70_SDR_TRANSIT

SET PROPELLER_MANUFACTURER_ID =

      (SELECT Y60.MANUFACTURER_ID FROM Y60_MANUFACTURER Y60

       WHERE GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID) = PROPELLER_MANUFACTURER)

WHERE NVL(PROPELLER_MANUFACTURER_ID,0) = 0

AND   TRIM(PROPELLER_MANUFACTURER) IS NOT NULL ;

COMMIT;

/*

SET PROPELLER MODEL 1st pass

*/   

UPDATE M70_SDR_TRANSIT M70

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

   (SELECT Y62.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL Y62

    WHERE Y62.PRODUCT_TYPE_CD            = 'P'

    AND   DATA_COMPRESSION(Y62.MODEL_ID) = M70.PROPELLER_MODEL

    AND   Y62.MANUFACTURER_ID = M70.PROPELLER_MANUFACTURER_ID)

WHERE M70.PROPELLER_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            = 'P'

    AND   DATA_COMPRESSION(Y62.MODEL_ID) = M70.PROPELLER_MODEL

    AND   Y62.MANUFACTURER_ID            = M70.PROPELLER_MANUFACTURER_ID)

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

    (SELECT  1 FROM Y62_PRODUCT_MAKE_MODEL Y62

     WHERE   Y62.PRODUCT_TYPE_CD = 'P'

     AND     Y62.MANUFACTURER_ID = M70.PROPELLER_MANUFACTURER_ID 

     AND     Y62.MODEL_ID        = M70.PROPELLER_MODEL ) ;                                                         

COMMIT;

/*

SET PROPELLER MODEL 2nd pass

*/                                                            

 UPDATE M70_SDR_TRANSIT M70

SET M70.PROPELLER_MODEL =

             (SELECT MAX(Y72.MODEL_ID) FROM Y72_FAA_MODEL_CONVERSION Y72

              WHERE Y72.PRODUCT_TYPE_CD = 'P'

              AND   Y72.FAA_MODEL_ID    = M70.PROPELLER_MODEL

              AND   Y72.MANUFACTURER_ID = M70.PROPELLER_MANUFACTURER_ID )

WHERE EXISTS (SELECT 1 FROM Y72_FAA_MODEL_CONVERSION Y72

              WHERE Y72.PRODUCT_TYPE_CD = 'P'

              AND   Y72.MANUFACTURER_ID = M70.PROPELLER_MANUFACTURER_ID

              AND   Y72.FAA_MODEL_ID = M70.PROPELLER_MODEL) 

AND NOT EXISTS               

    (SELECT  1 FROM Y62_PRODUCT_MAKE_MODEL Y62

     WHERE   Y62.PRODUCT_TYPE_CD = 'P'

     AND     Y62.MANUFACTURER_ID = M70.PROPELLER_MANUFACTURER_ID 

     AND     Y62.MODEL_ID        = M70.PROPELLER_MODEL ) ;                                                        

COMMIT;

/*

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

*/     

--- HAVE WE GOT ANY UNCONVERTED PROPELLER MANUFACTURERS ?

SELECT * FROM M70_SDR_TRANSIT

WHERE NVL(PROPELLER_MANUFACTURER_ID,0) = 0

AND   TRIM(PROPELLER_MANUFACTURER) IS NOT NULL ;

SELECT DISTINCT(M70.PROPELLER_MANUFACTURER) ,

       Z980.TO_TXT,

       Y60.MANUFACTURER_ID,

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

' WHERE NVL(PROPELLER_MANUFACTURER_ID ,0) = 0 AND PROPELLER_MANUFACTURER = '''

|| M70.PROPELLER_MANUFACTURER || ''' ; ' AS UPD1

FROM   M70_SDR_TRANSIT      M70,

       Z980_TERM_CONVERSION Z980,

       Y60_MANUFACTURER     Y60

WHERE  Z980.TERM_TYPE_CD     = '3'

AND    Z980.FROM_TXT         = TRIM(M70.PROPELLER_MANUFACTURER)

AND    GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID)

                             = TRIM(Z980.TO_TXT)

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

/*

DOUBLE CHECK PROPELLER MODELS

*/     

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

SELECT DISTINCT(M70.PROPELLER_MODEL) ,

-- update model

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

''' WHERE PROPELLER_MODEL = ''' || M70.PROPELLER_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 || ''',''P'',' || M70.PROPELLER_MANUFACTURER_ID || ',''' ||

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

FROM   M70_SDR_TRANSIT          M70,

       Y72_FAA_MODEL_CONVERSION Y72

WHERE  Y72.PRODUCT_TYPE_CD  = 'P'

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

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

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

-- PROPELLER mfg/model still not found on Y62 table

SELECT DISTINCT(M70.PROPELLER_MODEL) , M70.PROPELLER_MANUFACTURER_ID,

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

''' , PROPELLER_MANUFACTURER_ID = ' || Y72.MANUFACTURER_ID ||

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

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

Y72.FAA_MODEL_ID || ''',''P'',' || M70.PROPELLER_MANUFACTURER_ID || ',''' ||

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

FROM   M70_SDR_TRANSIT          M70,

       Y72_FAA_MODEL_CONVERSION Y72

WHERE  Y72.PRODUCT_TYPE_CD  = 'P'

AND    Y72.MANUFACTURER_ID  = M70.PROPELLER_MANUFACTURER_ID

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

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

AND NOT EXISTS

(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL X

 WHERE  X.PRODUCT_TYPE_CD              = 'P'

 AND    X.MANUFACTURER_ID              = M70.PROPELLER_MANUFACTURER_ID

 AND    X.MODEL_ID                     = M70.PROPELLER_MODEL) ;

-- PROPELLER mfg/model still not found on Y62 table

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

SELECT DISTINCT(M70.PROPELLER_MODEL) , PROPELLER_MANUFACTURER_ID , PROPELLER_MANUFACTURER ,

'UPDATE M70_SDR_TRANSIT SET PROPELLER_MODEL = '''          || Y62.MODEL_ID ||

''' , PROPELLER_MANUFACTURER_ID = ' || Y62.MANUFACTURER_ID ||

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

FROM   M70_SDR_TRANSIT          M70,

       Y62_PRODUCT_MAKE_MODEL   Y62

WHERE  Y62.PRODUCT_TYPE_CD  = 'P'

AND    DATA_COMPRESSION(Y62.MODEL_ID) = M70.PROPELLER_MODEL

AND    Y62.MODEL_ID                  <> M70.PROPELLER_MODEL

AND NOT EXISTS

(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL X

 WHERE  X.PRODUCT_TYPE_CD              = 'P'

 AND    X.MANUFACTURER_ID              = M70.PROPELLER_MANUFACTURER_ID

 AND    X.MODEL_ID                     = M70.PROPELLER_MODEL) ;

/*

FILL DEFAULTS

*/     

UPDATE M70_SDR_TRANSIT SET PROPELLER_MANUFACTURER_ID = 000

WHERE  PROPELLER_MANUFACTURER_ID IS NULL

AND    TRIM(PROPELLER_MODEL) IS NOT NULL ;

UPDATE M70_SDR_TRANSIT SET PROPELLER_MANUFACTURER = 'XXX'

WHERE  TRIM(PROPELLER_MANUFACTURER) IS NULL

AND    TRIM(PROPELLER_MODEL) IS NOT NULL ;

UPDATE M70_SDR_TRANSIT SET PROPELLER_MODEL = 'XXX'

WHERE  TRIM(PROPELLER_MODEL) IS NULL

AND    PROPELLER_MANUFACTURER_ID IS NOT NULL;

COMMIT;

/*

Final PROPELLER manufacturer model check.  

*/     

SELECT DISTINCT(M70.PROPELLER_MANUFACTURER),

       M70.PROPELLER_MODEL,

       M70.PROPELLER_MANUFACTURER_ID  ,

       GET_MANUFACTURER_NAME(M70.PROPELLER_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.PROPELLER_MODEL || ''' ; ' || CHR(13) ||

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

''', PROPELLER_MANUFACTURER = ''' || M70.PROPELLER_MANUFACTURER ||

''', PROPELLER_MANUFACTURER_ID = ' || M70.PROPELLER_MANUFACTURER_ID || ' WHERE PROPELLER_MODEL = ''' ||

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

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

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

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

'INSERT INTO Y62_PRODUCT_MAKE_MODEL VALUES (''P'','''|| M70.PROPELLER_MODEL || ''',' || M70.PROPELLER_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.PROPELLER_MANUFACTURER || ''',''' ||

M70.PROPELLER_MODEL || ''',''P'',' || M70.PROPELLER_MANUFACTURER_ID || ',''' ||

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

FROM   M70_SDR_TRANSIT  M70

WHERE  M70.PROPELLER_MODEL IS NOT NULL

AND NOT EXISTS

(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62

 WHERE  Y62.PRODUCT_TYPE_CD = 'P'

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

 AND    Y62.MODEL_ID        = M70.PROPELLER_MODEL)

 ORDER BY M70.PROPELLER_MANUFACTURER, M70.PROPELLER_MODEL ;