STEP 17 ENGINE 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 procedures as found in Step 16 for Aircraft model conversion
*/
SELECT DISTINCT(M70.ENGINE_MANUFACTURER) , AIRCRAFT_MARK_ID, ENGINE_MODEL,
'INSERT INTO Z980_TERM_CONVERSION VALUES(''2'', ''' || M70.ENGINE_MANUFACTURER ||
''' , ''' || M70.ENGINE_MANUFACTURER ||''', 0, ''ADMIN'', SYSDATE ,SYSDATE ) ; ' INS1
FROM M70_SDR_TRANSIT M70
WHERE TRIM(M70.ENGINE_MANUFACTURER) IS NOT NULL
AND NOT EXISTS
(SELECT Z980.FROM_TXT
FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = '2'
AND TRIM(Z980.FROM_TXT) = M70.ENGINE_MANUFACTURER);
/* UPDATE Y72 MODEL CONVERSION WHERE POSSIBLE */
INSERT INTO Y72_FAA_MODEL_CONVERSION
SELECT M70.ENGINE_MANUFACTURER,
M70.ENGINE_MODEL,
'E',
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.ENGINE_MANUFACTURER = M70.ENGINE_MANUFACTURER
AND X.ENGINE_MODEL = M70.ENGINE_MODEL
AND ROWNUM = 1)
AND NOT EXISTS (SELECT 1 FROM Y72_FAA_MODEL_CONVERSION X
WHERE X.PRODUCT_TYPE_CD = 'E'
AND X.FAA_MANUFACTURER = M70.ENGINE_MANUFACTURER
AND X.FAA_MODEL_ID = M70.ENGINE_MODEL)
AND Y62.PRODUCT_TYPE_CD = 'E'
AND Y62.MANUFACTURER_ID = (SELECT X.MANUFACTURER_ID FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'E'
AND DATA_COMPRESSION(X.MODEL_ID) = M70.ENGINE_MODEL
AND ROWNUM = 1)
AND Y62.MODEL_ID = (SELECT X.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'E'
AND DATA_COMPRESSION(X.MODEL_ID) = M70.ENGINE_MODEL
AND ROWNUM = 1);
COMMIT;
/*
ENGINE - FULL CONVERT WHERE POSSIBLE
*/
UPDATE M70_SDR_TRANSIT X
SET ENGINE_MANUFACTURER =
(SELECT GET_MANUFACTURER_NAME(Y.MANUFACTURER_ID)
FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'E'
AND Y.FAA_MANUFACTURER = TRIM(X.ENGINE_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.ENGINE_MODEL)) ,
ENGINE_MANUFACTURER_ID =
(SELECT Y.MANUFACTURER_ID FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'E'
AND Y.FAA_MANUFACTURER = TRIM(X.ENGINE_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.ENGINE_MODEL)) ,
ENGINE_MODEL =
(SELECT Y.MODEL_ID FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'E'
AND Y.FAA_MANUFACTURER = TRIM(X.ENGINE_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.ENGINE_MODEL))
WHERE TRIM(ENGINE_MODEL) IS NOT NULL
AND ENGINE_MANUFACTURER_ID IS NULL
AND EXISTS
(SELECT 1 FROM Y72_FAA_MODEL_CONVERSION Y
WHERE Y.PRODUCT_TYPE_CD = 'E'
AND Y.FAA_MANUFACTURER = TRIM(X.ENGINE_MANUFACTURER)
AND Y.FAA_MODEL_ID = TRIM(X.ENGINE_MODEL) ) ;
/*
CONVERT ENGINE MANUFACTURER TEXT TO CAWIS STANDARD
*/
UPDATE M70_SDR_TRANSIT M70
SET M70.ENGINE_MANUFACTURER =
(SELECT TO_TXT FROM Z980_TERM_CONVERSION
WHERE TERM_TYPE_CD = '2'
AND FROM_TXT = M70.ENGINE_MANUFACTURER)
WHERE M70.ENGINE_MANUFACTURER NOT IN
(SELECT GET_MANUFACTURER_NAME(MANUFACTURER_ID)
FROM Y62_PRODUCT_MAKE_MODEL WHERE PRODUCT_TYPE_CD = 'E')
AND EXISTS (SELECT 1 FROM Z980_TERM_CONVERSION
WHERE TERM_TYPE_CD = '2'
AND FROM_TXT = M70.ENGINE_MANUFACTURER) ;
COMMIT;
/*
SET ENGINE MANUFACTURER_ID
*/
UPDATE M70_SDR_TRANSIT
SET ENGINE_MANUFACTURER_ID =
(SELECT Y60.MANUFACTURER_ID FROM Y60_MANUFACTURER Y60
WHERE GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID) = ENGINE_MANUFACTURER)
WHERE NVL(ENGINE_MANUFACTURER_ID,0) = 0
AND TRIM(ENGINE_MANUFACTURER) IS NOT NULL ;
COMMIT;
/*
SET ENGINE MODEL 1st pass
*/
UPDATE M70_SDR_TRANSIT M70
SET M70.ENGINE_MODEL = --- UPDATE WITH UNCOMPRESSED MODEL
(SELECT Y62.MODEL_ID FROM Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND DATA_COMPRESSION(Y62.MODEL_ID) = M70.ENGINE_MODEL
AND Y62.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID)
WHERE M70.ENGINE_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 = 'E'
AND DATA_COMPRESSION(Y62.MODEL_ID) = M70.ENGINE_MODEL
AND Y62.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID)
AND NOT EXISTS --- AND THE CURRENT M70 ENGINE MFG/MODEL IS NOT ON Y62
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND Y62.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND Y62.MODEL_ID = M70.ENGINE_MODEL ) ;
COMMIT;
/*
SET ENGINE MODEL 2nd pass
*/
UPDATE M70_SDR_TRANSIT M70
SET M70.ENGINE_MODEL =
(SELECT MAX(Y72.MODEL_ID) FROM Y72_FAA_MODEL_CONVERSION Y72
WHERE Y72.PRODUCT_TYPE_CD = 'E'
AND Y72.FAA_MODEL_ID = M70.ENGINE_MODEL
AND Y72.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID )
WHERE EXISTS (SELECT 1 FROM Y72_FAA_MODEL_CONVERSION Y72
WHERE Y72.PRODUCT_TYPE_CD = 'E'
AND Y72.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND Y72.FAA_MODEL_ID = M70.ENGINE_MODEL)
AND NOT EXISTS
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND Y62.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND Y62.MODEL_ID = M70.ENGINE_MODEL ) ;
COMMIT;
/*
DOUBLE CHECK for ENGINE-MANUFACTURER ID'S WHERE ENGINE MANUFACTURER IS NOT NULL, BUT ENGINE MANUFACTURER ID IS NULL
*/
--- HAVE WE GOT ANY UNCONVERTED ENGINE MANUFACTURERS ?
SELECT * FROM M70_SDR_TRANSIT
WHERE NVL(ENGINE_MANUFACTURER_ID,0) = 0
AND TRIM(ENGINE_MANUFACTURER) IS NOT NULL ;
SELECT DISTINCT(M70.ENGINE_MANUFACTURER) ,
Z980.TO_TXT,
Y60.MANUFACTURER_ID,
'UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER_ID = ' || Y60.MANUFACTURER_ID ||
' WHERE NVL(ENGINE_MANUFACTURER_ID ,0) = 0 AND ENGINE_MANUFACTURER = '''
|| M70.ENGINE_MANUFACTURER || ''' ; ' AS UPD1
FROM M70_SDR_TRANSIT M70,
Z980_TERM_CONVERSION Z980,
Y60_MANUFACTURER Y60
WHERE Z980.TERM_TYPE_CD = '2'
AND Z980.FROM_TXT = TRIM(M70.ENGINE_MANUFACTURER)
AND GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID)
= TRIM(Z980.TO_TXT)
AND NVL(M70.ENGINE_MANUFACTURER_ID,0) = 0 ;
/*
DOUBLE CHECK ENGINE MODELS
*/
-- Mismatch against TC CAWIS-MODEL, BUT matches FAA model
SELECT DISTINCT(M70.ENGINE_MODEL) ,
-- Update model
'UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = ''' || Y72.MODEL_ID ||
''' WHERE ENGINE_MODEL = ''' || M70.ENGINE_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.ENGINE_MANUFACTURER_ID || ',''' ||
M70.ENGINE_MODEL || ''',''Y'',''ADMIN'',SYSDATE,SYSDATE); ' AS INS1
FROM M70_SDR_TRANSIT M70,
Y72_FAA_MODEL_CONVERSION Y72
WHERE Y72.PRODUCT_TYPE_CD = 'E'
AND Y72.MANUFACTURER_ID = NVL(M70.ENGINE_MANUFACTURER_ID,0)
AND Y72.FAA_MODEL_ID = NVL(M70.ENGINE_MODEL,'X')
AND Y72.MODEL_ID <> NVL(M70.ENGINE_MODEL,'X') ;
-- ENGINE mfg/model still not found on Y62 table
SELECT DISTINCT(M70.ENGINE_MODEL) , M70.ENGINE_MANUFACTURER_ID,
'UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = ''' || Y72.MODEL_ID ||
''' , ENGINE_MANUFACTURER_ID = ' || Y72.MANUFACTURER_ID ||
' WHERE ENGINE_MODEL = ''' || M70.ENGINE_MODEL || ''' ; ' AS UPD1,
' INSERT INTO Y72_FAA_MODEL_CONVERSION VALUES (''' || Y72.FAA_MANUFACTURER || ''',''' ||
Y72.FAA_MODEL_ID || ''',''E'',' || M70.ENGINE_MANUFACTURER_ID || ',''' ||
M70.ENGINE_MODEL || ''',''Y'',''ADMIN'',SYSDATE,SYSDATE); ' AS INS1
FROM M70_SDR_TRANSIT M70,
Y72_FAA_MODEL_CONVERSION Y72
WHERE Y72.PRODUCT_TYPE_CD = 'E'
AND Y72.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND Y72.FAA_MODEL_ID = NVL(M70.ENGINE_MODEL,'X')
AND Y72.MODEL_ID <> NVL(M70.ENGINE_MODEL,'X')
AND NOT EXISTS
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'E'
AND X.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND X.MODEL_ID = M70.ENGINE_MODEL) ;
-- ENGINE mfg/model still not found on Y62 table
-- The model was found, but the ENGINE_manufacturer_id needs updating
SELECT DISTINCT(M70.ENGINE_MODEL) , ENGINE_MANUFACTURER_ID , ENGINE_MANUFACTURER ,
'UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = ''' || Y62.MODEL_ID ||
''' , ENGINE_MANUFACTURER_ID = ' || Y62.MANUFACTURER_ID ||
' WHERE ENGINE_MODEL = ''' || M70.ENGINE_MODEL || ''' ; ' AS UPD1
FROM M70_SDR_TRANSIT M70,
Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND DATA_COMPRESSION(Y62.MODEL_ID) = M70.ENGINE_MODEL
AND Y62.MODEL_ID <> M70.ENGINE_MODEL
AND NOT EXISTS
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL X
WHERE X.PRODUCT_TYPE_CD = 'E'
AND X.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND X.MODEL_ID = M70.ENGINE_MODEL) ;
/*
--- PRATT & WHITNEY CANADA/USA ISSUE
*/
SELECT DISTINCT(M70.ENGINE_MANUFACTURER),
M70.ENGINE_MODEL,
M70.ENGINE_MANUFACTURER_ID ,
Y56.NAME_COMMON_NM ,
' UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER_ID = 1470 WHERE ENGINE_MODEL = ''' ||
M70.ENGINE_MODEL || ''';' AS UPD1
FROM M70_SDR_TRANSIT M70,
Y60_MANUFACTURER Y60,
Y56_ORGANIZATION Y56
WHERE M70.ENGINE_MODEL IS NOT NULL
AND Y60.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND Y56.PARTY_ID = Y60.PARTY_ID
AND M70.ENGINE_MANUFACTURER_ID = 1469
AND EXISTS
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND Y62.MANUFACTURER_ID = 1470
AND Y62.MODEL_ID = M70.ENGINE_MODEL) ;
SELECT DISTINCT(M70.ENGINE_MANUFACTURER),
M70.ENGINE_MODEL,
M70.ENGINE_MANUFACTURER_ID ,
Y56.NAME_COMMON_NM ,
' UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER_ID = 1469 WHERE ENGINE_MODEL = ''' ||
M70.ENGINE_MODEL || ''';' AS UPD1
FROM M70_SDR_TRANSIT M70,
Y60_MANUFACTURER Y60,
Y56_ORGANIZATION Y56
WHERE M70.ENGINE_MODEL IS NOT NULL
AND Y60.MANUFACTURER_ID = M70.ENGINE_MANUFACTURER_ID
AND Y56.PARTY_ID = Y60.PARTY_ID
AND M70.ENGINE_MANUFACTURER_ID = 1470
AND EXISTS
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND Y62.MANUFACTURER_ID = 1469
AND Y62.MODEL_ID = M70.ENGINE_MODEL) ;
-----
/*
FILL DEFAULTS
*/
UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER_ID = 000
WHERE ENGINE_MANUFACTURER_ID IS NULL
AND TRIM(ENGINE_MODEL) IS NOT NULL ;
UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER = 'XXX'
WHERE TRIM(ENGINE_MANUFACTURER) IS NULL
AND TRIM(ENGINE_MODEL) IS NOT NULL ;
UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = 'XXX'
WHERE TRIM(ENGINE_MODEL) IS NULL
AND ENGINE_MANUFACTURER_ID IS NOT NULL;
COMMIT;
/*
Final ENGINE manufacturer/model check.
*/
SELECT DISTINCT(M70.ENGINE_MANUFACTURER),
M70.ENGINE_MODEL,
M70.ENGINE_MANUFACTURER_ID ,
GET_MANUFACTURER_NAME(M70.ENGINE_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.ENGINE_MODEL || ''' ; ' || CHR(13) ||
' UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = ''' || M70.ENGINE_MODEL ||
''', ENGINE_MANUFACTURER = ''' || M70.ENGINE_MANUFACTURER ||
''', ENGINE_MANUFACTURER_ID = ' || M70.ENGINE_MANUFACTURER_ID || ' WHERE ENGINE_MODEL = ''' ||
M70.ENGINE_MODEL || ''' ;' || CHR(13) ||
'INSERT INTO TR24_MODEL VALUES ( ''' || M70.ENGINE_MODEL || ''',0,''ADMIN'',SYSDATE,SYSDATE); ' || CHR(13) ||
'INSERT INTO Y61_MAKE_MODEL VALUES (' || M70.ENGINE_MANUFACTURER_ID || ', ''' ||
M70.ENGINE_MODEL || ''' , ''' || DATA_COMPRESSION(M70.ENGINE_MODEL) || ''' , 0,''ADMIN'',SYSDATE,SYSDATE);' || CHR(13) ||
'INSERT INTO Y62_PRODUCT_MAKE_MODEL VALUES (''E'','''|| M70.ENGINE_MODEL || ''',' || M70.ENGINE_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.ENGINE_MANUFACTURER || ''',''' ||
M70.ENGINE_MODEL || ''',''E'',' || M70.ENGINE_MANUFACTURER_ID || ',''' ||
M70.ENGINE_MODEL || ''',''Y'',''ADMIN'',SYSDATE,SYSDATE); ' || CHR(13) || CHR(13) AS INS1
FROM M70_SDR_TRANSIT M70
WHERE M70.ENGINE_MODEL IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y62
WHERE Y62.PRODUCT_TYPE_CD = 'E'
AND Y62.MANUFACTURER_ID = NVL(M70.ENGINE_MANUFACTURER_ID,1)
AND Y62.MODEL_ID = M70.ENGINE_MODEL)
ORDER BY M70.ENGINE_MANUFACTURER, M70.ENGINE_MODEL ;
/*