STEP 31 SDR INSERT ROUTINES

Owned by kurt.martin
Last updated: Aug 30, 2022
1 min read

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

FAA DATA IMPORT PROCEDURE.TXT

simply copy and place into your preferred SQL tool

 

 

 

/*

Now, we copy the temp FAA data from M70 , over to WSDRS production

*/

 

 

INSERT INTO M72_SDR

SELECT

M70.SDR_NUMBER_NUM, M70.AIRCRAFT_ID,

DATA_COMPRESSION(M70.AIRCRAFT_MARK_ID),

NVL(M70.PRODUCT_CD,'A'), TRIM(M70.CARRIER_CD),

TRIM(M70.COUNTRY_CD),

NVL(TRIM(M70.HOW_DISCOVERED_CODE),'N'),

NVL(TRIM(M70.HQ_STATUS_CODE),'R'),

NVL(TRUNC(M70.ENTRY_DATE),TRUNC(SYSDATE)),

NVL(TRUNC(M70.OCCURENCE_DATE),TRUNC(SYSDATE)),

NVL(TRUNC(M70.LAST_UPDATE),TRUNC(SYSDATE)),

NVL(TRIM(M70.PART_CONDITION),'UNKNOWN'),

M70.PART_CSN_VALUE,

NVL(TRIM(M70.PART_LOCATION),'UNKNOWN'),

NVL(TRIM(M70.PART_NAME),'UNKNOWN'),

NVL(DATA_COMPRESSION(M70.PART_NUMBER),'UNKNOWN'),

M70.PART_TIME, M70.PART_TOTAL_TIME,

NVL(TRIM(M70.REGION_CODE),M70.COUNTRY_CD), 'C',

TRIM(SUBSTR(M70.SDR_TEXT,1,2000)),

NVL(TRIM(M70.WHEN_DISCOVERED_CODE),'IN'),

M70.SUBMITTER_CONTROL_NUM,

NVL(TRUNC(M70.ENTRY_DATE),TRUNC(SYSDATE)),

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01' ) ,

0,1,

DECODE(M70.COUNTRY_CD,'US',51294000,'AU',51491850,'EU',100045813,51171000) ,

'N','N','N','N'

FROM M70_SDR_TRANSIT M70 ;


-- AIRCRAFT

INSERT INTO M73_SDR_PRODUCT_MAKE_MODEL

SELECT

M70.SDR_NUMBER_NUM, 'A',

M70.AIRCRAFT_MODEL, M70.AIRCRAFT_MANUFACTURER_ID,

NVL(DATA_COMPRESSION(M70.AIRCRAFT_SERIAL_NUMBER),'UNKNOWN'),

TRUNC(M70.ENTRY_DATE),

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(TRUNC(M70.ENTRY_DATE),TRUNC(SYSDATE)),

0,DATA_COMPRESSION(M70.AIRCRAFT_MARK_ID),'AIRCRAFT'

FROM M70_SDR_TRANSIT M70 WHERE M70.AIRCRAFT_MANUFACTURER_ID > 0 ;

 

--- ENGINE

INSERT INTO M73_SDR_PRODUCT_MAKE_MODEL

SELECT

M70.SDR_NUMBER_NUM, 'E',

M70.ENGINE_MODEL, M70.ENGINE_MANUFACTURER_ID,

NVL(DATA_COMPRESSION(M70.ENGINE_SERIAL_NUMBER),'UNKNOWN'),

M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0,NULL,'ENGINE'

FROM M70_SDR_TRANSIT M70 WHERE M70.ENGINE_MANUFACTURER_ID > 0 ;

 

--- PROPELLER

 

INSERT INTO M73_SDR_PRODUCT_MAKE_MODEL

SELECT

M70.SDR_NUMBER_NUM, 'P',

M70.PROPELLER_MODEL, M70.PROPELLER_MANUFACTURER_ID,

NVL(DATA_COMPRESSION(M70.PROPELLER_SERIAL_NUMBER),'UNKNOWN'),

M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0,NULL,'PROPELLER'

FROM M70_SDR_TRANSIT M70 WHERE M70.PROPELLER_MANUFACTURER_ID > 0 ;

 

--- COMPONENT/EQUIPMENT

INSERT INTO M73_SDR_PRODUCT_MAKE_MODEL

SELECT

M70.SDR_NUMBER_NUM, 'Q',

'EQUIPMENT',

NVL(M70.COMPONENT_MANUFACTURER_ID,1),

NVL(REPLACE(M70.COMPONENT_MODEL,'EQUIPMENT','') || DATA_COMPRESSION(M70.COMPONENT_SERIAL_NUMBER),'UNKNOWN'),

M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE),0,

NVL(DATA_COMPRESSION(M70.COMPONENT_SERIAL_NUMBER),'UNKNOWN'),

NVL(TRIM(M70.COMPONENT_NAME)),

(SELECT SUBSTR(TR57.JASC_TITLE_ETXT,1,30) FROM TR57_JASC TR57 WHERE TR57.JASC_NBR = M70.JASC_NBR_1))

FROM M70_SDR_TRANSIT M70  ;

 

---- ATA/JASC

INSERT INTO M74_SDR_JASC

SELECT

M70.SDR_NUMBER_NUM, 1, TRIM(M70.JASC_NBR_1),

M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE),  0

FROM M70_SDR_TRANSIT M70;

 

---- NATURE CODES

INSERT INTO M77_SDR_NATURE

SELECT

M70.SDR_NUMBER_NUM, 1, NVL(TRIM(M70.NATURE_CD_1),'O'),

M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0 

FROM M70_SDR_TRANSIT M70   ;

 

INSERT INTO M77_SDR_NATURE

SELECT

M70.SDR_NUMBER_NUM, 2, TRIM(M70.NATURE_CD_2), M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0 

FROM M70_SDR_TRANSIT M70 WHERE TRIM(M70.NATURE_CD_2) IS NOT NULL   ;



INSERT INTO M77_SDR_NATURE

SELECT

M70.SDR_NUMBER_NUM, 3, TRIM(M70.NATURE_CD_3), M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0 

FROM M70_SDR_TRANSIT M70 WHERE TRIM(M70.NATURE_CD_3) IS NOT NULL   ;

 

---- ACTION TAKEN CODES

 

INSERT INTO M78_SDR_ACTION_TAKEN

SELECT '1',

M70.SDR_NUMBER_NUM, NVL(TRIM(M70.ACTION_TAKEN_CD_1),'O'),M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0 

FROM M70_SDR_TRANSIT M70  ;

 

INSERT INTO M78_SDR_ACTION_TAKEN

SELECT '2',

M70.SDR_NUMBER_NUM, TRIM(M70.ACTION_TAKEN_CD_2), M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0  

FROM M70_SDR_TRANSIT M70 WHERE TRIM(M70.ACTION_TAKEN_CD_2) IS NOT NULL ;

 

INSERT INTO M78_SDR_ACTION_TAKEN

SELECT '3',

M70.SDR_NUMBER_NUM, TRIM(M70.ACTION_TAKEN_CD_3),M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0 

FROM M70_SDR_TRANSIT M70 WHERE TRIM(M70.ACTION_TAKEN_CD_3) IS NOT NULL ;

 

INSERT INTO M78_SDR_ACTION_TAKEN

SELECT '4',

M70.SDR_NUMBER_NUM, TRIM(M70.ACTION_TAKEN_CD_4), M70.ENTRY_DATE,

DECODE(M70.COUNTRY_CD,'US','FAA01','AU','CASA','EU','EASA01','ADMIN') ,

NVL(M70.ENTRY_DATE,SYSDATE), 0 

FROM M70_SDR_TRANSIT M70 WHERE TRIM(M70.ACTION_TAKEN_CD_4) IS NOT NULL ;

 

--- ENSURE an equipment record exists for every SDR

INSERT INTO M73_SDR_PRODUCT_MAKE_MODEL

SELECT M72.SDR_NUMBER_NUM, 'Q','EQUIPMENT', 1, 'UNKNOWN',

       M72.DATE_CURRENT_DTE, M72.LAST_MOD_BY_USER_ID,

       M72.LAST_MOD_TIMESTAMP_DTE , 0, 'UNKNOWN','UNKNOWN'

FROM   M72_SDR M72

WHERE  M72.PRODUCT_CD = 'Q'

AND NOT EXISTS (SELECT 1 FROM M73_SDR_PRODUCT_MAKE_MODEL M73

                WHERE M73.PRODUCT_TYPE_CD = 'Q'

                AND M73.SDR_NUMBER_NUM = M72.SDR_NUMBER_NUM) ;

 

COMMIT;