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 is a mismatch with the ATA/JASC code provided. The ATA/JASC code describes what section of the aircraft is of concern is this SDR.

JASC codes :

  https://wwwapps.tc.gc.ca/Saf-Sec-Sur/2/AWD-CN/documents/JASC.TXT

*/

 UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'E'

 WHERE JASC_NBR_1 BETWEEN 7000 AND 8699 AND PRODUCT_CD <> 'E';

 UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'P'

 WHERE JASC_NBR_1 BETWEEN 6100 AND 6199 AND PRODUCT_CD <> 'P';

 UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = 'Q'

 WHERE PRODUCT_CD <> 'Q'

 AND   ( JASC_NBR_1 BETWEEN 1000 AND 1000

 OR      JASC_NBR_1 BETWEEN 2500 AND 2565

 OR      JASC_NBR_1 BETWEEN 6800 AND 6999 ) ;

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;