STEP 21 SDR_PRODUCT_CODE
- kurt.martin
Sept 2021 : NOTE - all required code for this process is found at :
simply copy and place into your preferred SQL tool
FAA PRODUCT CODES ARE : 1,2,3,4
CAWIS PRODUCT CODES :
A - AIRCRAFT 1
E - ENGINE 2
P - PROPELLER 3
Q - EQUIPMENT/COMPONENT 4
SDR_PRODUCT_CODE refers to the product type of the subject of the SDR report itself.
IE : the subject of this SDR pertains to the airframe, or the engine or propeller, or a piece of equipment on the aircraft
*/
-- IF PRODUCT CODE IS '1' OR NULL, SET IT TO 'A'
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'A' WHERE NVL(PRODUCT_CD,'X') NOT IN ('2','3','4');
-- ENGINE
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'E' WHERE PRODUCT_CD = '2';
-- PROPELLER
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'P' WHERE PRODUCT_CD = '3';
-- EQUIPMENT/COMPONENT
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'Q' WHERE PRODUCT_CD = '4';
COMMIT ;
/*
We now update any Product-code that violates CAWIS/WSDRS editing rules. If the product_cd is 'A', then we must have aircraft manufacturer/model data to insert onto M73. Same for 'E' - engine and 'P' – propeller. The default is always 'Q'
Uncertain why the FAA would have an Engine type SDR, without providing the engine mfg/model info, for example. FAA SDR management is aware of the issue.
*/
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'Q' WHERE PRODUCT_CD = 'A' AND TRIM(AIRCRAFT_MODEL) IS NULL ;
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'Q' WHERE PRODUCT_CD = 'E' AND TRIM(ENGINE_MODEL) IS NULL ;
UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'Q' WHERE PRODUCT_CD = 'P' AND TRIM(PROPELLER_MODEL) IS NULL ;
COMMIT;