STEP 20 EQUIPMENT/COMPONENT DATA CONVERSION
- kurt.martin
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;