STEP 18 PROPELLER MANUFACTURER AND MODEL CONVERSION
- kurt.martin
Sept 2021 : NOTE - all required code for this process is found at :
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 ;