STEP 31 SDR INSERT ROUTINES
- kurt.martin
Sept 2021 : NOTE - all required code for this process is found at :
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;