STEP 29 REVIEW ALL MODELS

/*

This is a precaution only

Ensure all Aircraft/engine/propeller/equipment models are found on Y62-product_make_model

*/

 – update text decriptions

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER  = GET_MANUFACTURER_NAME(AIRCRAFT_MANUFACTURER_ID);

UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER    = GET_MANUFACTURER_NAME(ENGINE_MANUFACTURER_ID);

UPDATE M70_SDR_TRANSIT SET PROPELLER_MANUFACTURER = GET_MANUFACTURER_NAME(PROPELLER_MANUFACTURER_ID);

COMMIT;

UPDATE M70_SDR_TRANSIT X SET COMPONENT_MODEL = 'EQUIPMENT'

WHERE (COMPONENT_MODEL IS NULL  AND COMPONENT_MANUFACTURER_ID IS NOT NULL ) ;

UPDATE M70_SDR_TRANSIT X SET COMPONENT_MODEL = 'EQUIPMENT'

WHERE (COMPONENT_MODEL <> 'EQUIPMENT') ;

COMMIT;

 – double check the model data for completeness

SELECT 'A',SDR_NUMBER_NUM, AIRCRAFT_MANUFACTURER_ID , AIRCRAFT_MANUFACTURER , AIRCRAFT_MODEL , AIRCRAFT_SERIAL_NUMBER

FROM M70_SDR_TRANSIT

WHERE (AIRCRAFT_MANUFACTURER_ID IS     NULL AND AIRCRAFT_MODEL IS NOT NULL) 

OR    (AIRCRAFT_MANUFACTURER_ID IS NOT NULL AND AIRCRAFT_MODEL IS NULL)

UNION

SELECT 'E',SDR_NUMBER_NUM, ENGINE_MANUFACTURER_ID   , ENGINE_MANUFACTURER   , ENGINE_MODEL   , ENGINE_SERIAL_NUMBER

FROM M70_SDR_TRANSIT

WHERE (ENGINE_MANUFACTURER_ID IS     NULL AND ENGINE_MODEL IS NOT NULL) 

OR    (ENGINE_MANUFACTURER_ID IS NOT NULL AND ENGINE_MODEL IS NULL)

UNION

SELECT 'P',SDR_NUMBER_NUM, PROPELLER_MANUFACTURER_ID, PROPELLER_MANUFACTURER, PROPELLER_MODEL, PROPELLER_SERIAL_NUMBER

FROM M70_SDR_TRANSIT

WHERE (PROPELLER_MANUFACTURER_ID IS     NULL AND PROPELLER_MODEL IS NOT NULL) 

OR    (PROPELLER_MANUFACTURER_ID IS NOT NULL AND PROPELLER_MODEL IS NULL) ;

 – double check for integrity

SELECT 'A - BAD MODEL', X.* FROM M70_SDR_TRANSIT X

WHERE (AIRCRAFT_MODEL IS NOT NULL  OR AIRCRAFT_MANUFACTURER_ID IS NOT NULL )

AND    NOT EXISTS (SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y

WHERE  Y.PRODUCT_TYPE_CD = 'A'

AND    Y.MANUFACTURER_ID = NVL(X.AIRCRAFT_MANUFACTURER_ID,0)

AND    Y.MODEL_ID        = NVL(X.AIRCRAFT_MODEL,'X') )

UNION                 

SELECT 'E - BAD MODEL', X.* FROM M70_SDR_TRANSIT X

WHERE (ENGINE_MODEL IS NOT NULL  OR ENGINE_MANUFACTURER_ID IS NOT NULL )

AND    NOT EXISTS (SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y

WHERE  Y.PRODUCT_TYPE_CD = 'E'

AND    Y.MANUFACTURER_ID = NVL(X.ENGINE_MANUFACTURER_ID,0)

AND    Y.MODEL_ID        = NVL(X.ENGINE_MODEL,'X') )

UNION                            

SELECT 'P - BAD MODEL', X.* FROM M70_SDR_TRANSIT X

WHERE (PROPELLER_MODEL IS NOT NULL  OR PROPELLER_MANUFACTURER_ID IS NOT NULL )

AND    NOT EXISTS (SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y

WHERE  Y.PRODUCT_TYPE_CD = 'P'

AND    Y.MANUFACTURER_ID = NVL(X.PROPELLER_MANUFACTURER_ID,0)

AND    Y.MODEL_ID        = NVL(X.PROPELLER_MODEL,'X') )

UNION

SELECT 'Q - BAD MODEL', X.* FROM M70_SDR_TRANSIT X

WHERE (COMPONENT_MODEL IS NOT NULL  OR COMPONENT_MANUFACTURER_ID IS NOT NULL )

AND    NOT EXISTS (SELECT 1 FROM Y62_PRODUCT_MAKE_MODEL Y

WHERE  Y.PRODUCT_TYPE_CD = 'Q'

AND    Y.MANUFACTURER_ID = NVL(X.COMPONENT_MANUFACTURER_ID,0)

AND    Y.MODEL_ID        = NVL(X.COMPONENT_MODEL,'X') );