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,
...
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
...