STEP 20 EQUIPMENT/COMPONENT DATA CONVERSION

Sept 2021 : NOTE - all required code for this process is found at :

simply copy and place into your preferred SQL tool

Note : The term EQUIPMENT = COMPONENT - these are absolutely equivalent in CAWIS (Product Type Q on the Y62 table)

Identify any component manufacturer NOT found on Z980_term_conversion

Either enter any new manufacturer into CAWIS

or -

update the FAA value to the closest manufacturer already in CAWIS

*/

SELECT DISTINCT(M70.COMPONENT_MANUFACTURER) ,

'INSERT INTO Z980_TERM_CONVERSION VALUES(''4'', ''' || M70.COMPONENT_MANUFACTURER ||

''' , ''' || M70.COMPONENT_MANUFACTURER ||''', 0, ''ADMIN'', SYSDATE ,SYSDATE ) ; ' ||

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

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

FROM M70_SDR_TRANSIT M70

WHERE NVL(M70.COMPONENT_MANUFACTURER,' ') <> ' '

AND NOT EXISTS

(SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

WHERE Z980.TERM_TYPE_CD = '4'

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

--- 1ST ATTEMPT - CONVERT USING Z980 TABLE

UPDATE M70_SDR_TRANSIT M70

SET M70.COMPONENT_MANUFACTURER =

(SELECT MAX(Z980.TO_TXT) FROM Z980_TERM_CONVERSION Z980

WHERE Z980.TERM_TYPE_CD = '4'

AND TRIM(Z980.FROM_TXT) = M70.COMPONENT_MANUFACTURER )

WHERE NVL(M70.COMPONENT_MANUFACTURER_ID,0) = 0

AND TRIM(M70.COMPONENT_MANUFACTURER) IS NOT NULL ;

COMMIT;

--- 2ND ATTEMPT USING Y60 TABLE

UPDATE M70_SDR_TRANSIT M70

SET M70.COMPONENT_MANUFACTURER_ID =

(SELECT Y60.MANUFACTURER_ID FROM Y60_MANUFACTURER Y60

WHERE GET_MANUFACTURER_NAME(Y60.MANUFACTURER_ID) = M70.COMPONENT_MANUFACTURER )

WHERE NVL(M70.COMPONENT_MANUFACTURER_ID,0) = 0

AND TRIM(M70.COMPONENT_MANUFACTURER) IS NOT NULL ;

COMMIT;

--- ENSURE EQUIPMENT/COMPONENT MODEL IS "EQUIPMENT"

UPDATE M70_SDR_TRANSIT

SET COMPONENT_MODEL = 'EQUIPMENT'

WHERE TRIM(COMPONENT_MODEL) <> 'EQUIPMENT'

AND TRIM(COMPONENT_MANUFACTURER) IS NOT NULL;

COMMIT;