STEP 21 SDR_PRODUCT_CODE

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;