SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
--ABG TRADES, VOYAGE FLAGS, VOYAGE ATTRIBUTES CHECKS DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOYAGE_ATTR; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOYAGE_ATTR AS WITH BRAND_TRADE_ATTRIB_USED AS ( SELECT * FROM ( SELECT DISTINCT BRAND, CASE WHEN WORLD_CRUISE_IND='Y' THEN 'World Cruise' ELSE TRADE END TRADE, VARIABLE, 1 CNT FROM RMS_DB_PRD1.RMS.DF_DMD_SEG_VAR_IMPT --WHERE BRAND='AI' ) PIVOT ( SUM(CNT) FOR VARIABLE IN ( 'EMBARK_PORT', 'HOLIDAY_IND', 'SUBTRADE', 'PRODUCT_CODE', 'DURATION', 'SEA_DAYS_BTW_PORTS_GROUP', 'SAILING_TRIP_TYPE', 'WEEKDAY_IND', 'SHIP_CLASS', 'EMBARK_REGION3', 'SCHOOL_HOLIDAY_IND', 'FLY_CRUISE_FLAG', 'EMBARK_REGION2', 'DEBARK_REGION2', 'DEBARK_PORT', 'VOYAGE_CLUSTER', 'SAIL_WOY', 'DEBARK_REGION3' ) ) AS p (BRAND, TRADE, EMBARK_PORT,HOLIDAY_IND,SUBTRADE,PRODUCT_CODE,DURATION,SEA_DAYS_BTW_PORTS_GROUP,SAILING_TRIP_TYPE,WEEKDAY_IND, SHIP_CLASS,EMBARK_REGION3,SCHOOL_HOLIDAY_IND,FLY_CRUISE_FLAG,EMBARK_REGION2,DEBARK_REGION2,DEBARK_PORT,VOYAGE_CLUSTER,SAIL_WOY,DEBARK_REGION3) ) -- SELECT a.*, CASE WHEN a.TRADE_NAME is null or a.SUBTRADE_NAME is null or a.EMBARK_PORT is null or a.DEBARK_PORT is null or a.EMBARK_REGION3='Other' or a.DEBARK_REGION3='Other' THEN 0 ELSE 1 END ABG_CHECK, CASE WHEN a.BRAND NOT IN ('CO','AI') AND (a.SUPPORT_RMS_FLAG='N' OR a.SUPPORT_RMS_FLAG IS NULL) THEN 0 ELSE 1 END SUPPORT_RMS_CHECK, CASE WHEN a.BRAND NOT IN ('CO','AI') AND (a.DISPLAY_IND='N' OR a.DISPLAY_IND IS NULL) THEN 0 ELSE 1 END DISPLAY_FLAG_CHECK, CASE WHEN a.EMBARK_PORT IS NULL THEN 0 WHEN b.HOLIDAY_IND=1 AND a.HOLIDAY_IND IS NULL THEN 0 WHEN b.SUBTRADE=1 AND a.SUBTRADE_NAME IS NULL THEN 0 WHEN b.PRODUCT_CODE=1 AND a.PRODUCT_CODE IS NULL THEN 0 WHEN b.DURATION=1 AND a.DURATION IS NULL THEN 0 --SEA DAYS BETWEEN PORTS CAN BE NULL SO THIS IS FINE --WHEN b.SEA_DAYS_BTW_PORTS_GROUP=1 AND a.SEA_DAYS_BTW_PORTS_GROUP IS NULL THEN 0 WHEN b.SAILING_TRIP_TYPE=1 AND a.SAILING_TRIP_TYPE IS NULL THEN 0 WHEN b.WEEKDAY_IND=1 AND a.WEEKDAY_IND IS NULL THEN 0 WHEN b.SHIP_CLASS=1 AND a.SHIP_CLASS IS NULL THEN 0 WHEN b.EMBARK_REGION3=1 AND a.EMBARK_REGION3 IS NULL THEN 0 WHEN b.SCHOOL_HOLIDAY_IND=1 AND a.SCHOOL_HOLIDAY_IND IS NULL THEN 0 WHEN b.FLY_CRUISE_FLAG=1 AND a.FLY_CRUISE_FLAG IS NULL THEN 0 WHEN b.EMBARK_REGION2=1 AND a.EMBARK_REGION2 IS NULL THEN 0 WHEN b.DEBARK_REGION2=1 AND a.DEBARK_REGION2 IS NULL THEN 0 WHEN b.DEBARK_PORT=1 AND a.DEBARK_PORT IS NULL THEN 0 --VOYAGE CLUSTER CAN ACTUALLY BE NULL SO THIS IS FINE --WHEN VOYAGE_CLUSTER IS NULL THEN 0 WHEN b.SAIL_WOY=1 AND a.SAIL_WOY IS NULL THEN 0 WHEN b.DEBARK_REGION3=1 AND a.DEBARK_REGION3 IS NULL THEN 0 ELSE 1 END ATTR_CHECK FROM ( SELECT v.BRAND, v.VOYAGE_KEY, VOYAGE, --ABG DATA TRADE_NAME, SUBTRADE_NAME, EMBARK_PORT, EMBARK_REGION2, EMBARK_REGION3, DEBARK_PORT,DEBARK_REGION2, DEBARK_REGION3, --VOYAGE STATUS AND FLAGS VOYAGE_STATUS, DISPLAY_IND, SUPPORT_RMS_FLAG, --OTHER ATTRIBUTES USED SHIP_CLASS, SAIL_WOY, DURATION, e.SEA_DAYS_BTW_PORTS_GROUP, WEEKDAY_IND, nvl2(sh.voyage_key, 'Y', 'N') as SCHOOL_HOLIDAY_IND, HOLIDAY_IND, SAILING_TRIP_TYPE, PRODUCT_CODE,FLY_CRUISE_FLAG,VOYAGE_CLUSTER,WORLD_CRUISE_IND FROM RMS_DB_PRD1.RMS.MV_RM_VOYAGE v left join ( select distinct a.brand, a.voyage_key from RMS_DB_PRD1.RMS.mv_rm_school_holiday a left join RMS_DB_PRD1.RMS.umf_port_Dominant_Market b on (a.brand = 'PA' and a.brand = b.brand and a.market = b.dominant_market and substr(a.voyage_key, REGEXP_INSTR(a.voyage_key, '-', 1, 1) + 9, 3) = b.embark_port) where a.brand <>'CL' and school_holiday_ind = 'Y' and (a.brand <> 'PA' or b.embark_port is not null) ) sh on (v.brand = sh.brand and v.voyage_key = sh.voyage_key) left join RMS_DB_PRD1.RMS.umf_SEA_DAYS_BTW_PORTS_grp e on (v.brand = e.brand and v.trade_name = e.trade and e.SEA_DAYS_BTW_PORTS_lb<=v.SEA_DAYS_BTW_PORTS and v.SEA_DAYS_BTW_PORTS<e.SEA_DAYS_BTW_PORTS_ub) WHERE v.BRAND <>'CL' AND SAIL_DATE>'01/01/2017' AND v.BRAND||v.VOYAGE not in (SELECT BRAND||VOYAGE FROM RMS_DB_PRD1.RMS.UMF_EXCLUDED_HIST_VOYAGE WHERE BRAND<>'CL') ) a LEFT JOIN BRAND_TRADE_ATTRIB_USED b ON (a.BRAND=b.BRAND AND CASE WHEN a.WORLD_CRUISE_IND='Y' THEN 'World Cruise' ELSE a.TRADE_NAME END=b.TRADE) ; --CHECK THET WE HAVE UMF_LEAD_CATEGORY POPULATED CORRECTLY FOR EVERY SHIP/ CABIN VERSION DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION_DETAIL; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION_DETAIL AS --CHECK AGAINST CURRENT CAPACITY DATA FOR FUTURE VOYAGE AND LIKE SAILINGS... SELECT DISTINCT a.BRAND,a.SHIP_CODE, v.VOYAGE, a.CABIN_VERSION, a.CATEGORY, 'FOUND IN ALLOC CAP' SRC --, c.* FROM ( SELECT 'REV' CAP_TYPE, BRAND, VOYAGE, SHIP_CODE, CABIN_VERSION,CATEGORY, LOWER_BERTHS FROM RMS_DB_PRD1.RMS.MV_RM_ALLOCATED_CAPACITY WHERE LOWER_BERTHS>0 UNION ALL SELECT 'NON_REV' CAP_TYPE, BRAND, VOYAGE, SHIP_CODE, CABIN_VERSION,CATEGORY, LOWER_BERTHS FROM RMS_DB_PRD1.RMS.MV_RM_NON_REVENUE_CAPACITY WHERE LOWER_BERTHS>0 )a LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (a.BRAND=v.BRAND AND a.VOYAGE=v.VOYAGE) LEFT JOIN RMS_DB_PRD1.RMS.UMF_LEAD_CATEGORY c ON (a.BRAND=c.BRAND AND a.SHIP_CODE=c.SHIP_CODE AND a.CATEGORY=c.POLAR_CATEGORY AND a.CABIN_VERSION=c.CABIN_VERSION) WHERE a.BRAND<>'CL' AND c.BRAND is null AND a.BRAND||a.VOYAGE not in (SELECT BRAND||VOYAGE FROM RMS_DB_PRD1.RMS.UMF_EXCLUDED_HIST_VOYAGE WHERE BRAND <>'CL') AND v.SAIL_DATE>'01/01/2017' UNION ALL --CHECK AGAINST HISTORICAL CATEGORIES SELECT DISTINCT d.BRAND,d.SHIP_CODE, d.VOYAGE, d.CABIN_VERSION, d.BOOKED_CABIN_CATEGORY CATEGORY, 'FOUND IN BOOKING DATA' SRC --, c.* FROM RMS_DB_PRD1.RMS.DF_SUMMARY_PREP d LEFT JOIN RMS_DB_PRD1.RMS.UMF_LEAD_CATEGORY c ON (d.BRAND=c.BRAND AND d.SHIP_CODE=c.SHIP_CODE AND d.BOOKED_CABIN_CATEGORY=c.POLAR_CATEGORY AND d.CABIN_VERSION=c.CABIN_VERSION) INNER JOIN ( SELECT DISTINCT COMPANY_CODE BRAND, SHIP_CODE, SHIP_VERSION CABIN_VERSION FROM RMS_DB_PRD1.RMS.rms_shipcab WHERE BRAND NOT IN ('CO','AI') UNION ALL SELECT DISTINCT COMPANY_CODE BRAND, SHIP_CODE, SHIP_VERSION CABIN_VERSION FROM RMS_DB_PRD1.ETL_COSTA.rms_shipcab WHERE BRAND='CO' UNION ALL SELECT DISTINCT COMPANY_CODE BRAND, SHIP_CODE, SHIP_VERSION CABIN_VERSION FROM RMS_DB_PRD1.ETL_AIDA.rms_shipcab WHERE BRAND='AI' ) s ON (d.BRAND=s.BRAND AND d.SHIP_CODE=s.SHIP_CODE AND d.CABIN_VERSION=s.CABIN_VERSION) WHERE d.BRAND<>'CL' AND c.BRAND is null AND d.BOOKED_CABIN_CATEGORY not in ('Y') union ALL --CHECK AGAINST VOYAGES IN MV_RM_VOYAGE AND RMS_VOYAGE SELECT DISTINCT v.BRAND, v.SHIP_CODE, v.VOYAGE, v.CABIN_VERSION, '' CATEGORY, CASE WHEN u.BRAND IS NULL THEN 'FOUND IN MV_RM_VOYAGE - MSG FRM UMF_LEAD_CATEGORY' WHEN s.BRAND IS NULL AND v.BRAND NOT IN ('CO','AI') THEN 'FOUND IN MV_RM_VOYAGE - MSG FRM SHIPCAB' END SRC FROM RMS_DB_PRD1.RMS.MV_RM_VOYAGE v LEFT JOIN (SELECT DISTINCT BRAND, SHIP_CODE, CABIN_VERSION FROM RMS_DB_PRD1.RMS.UMF_LEAD_CATEGORY WHERE BRAND<>'CL') u ON (v.BRAND=u.BRAND AND v.SHIP_CODE=u.SHIP_CODE AND v.CABIN_VERSION=u.CABIN_VERSION) LEFT JOIN ( SELECT DISTINCT COMPANY_CODE BRAND, SHIP_CODE, SHIP_VERSION CABIN_VERSION FROM RMS_DB_PRD1.RMS.rms_shipcab WHERE BRAND NOT IN ('CO','AI') UNION ALL SELECT DISTINCT COMPANY_CODE BRAND, SHIP_CODE, SHIP_VERSION CABIN_VERSION FROM RMS_DB_PRD1.ETL_COSTA.rms_shipcab WHERE BRAND='CO' UNION ALL SELECT DISTINCT COMPANY_CODE BRAND, SHIP_CODE, SHIP_VERSION CABIN_VERSION FROM RMS_DB_PRD1.ETL_AIDA.rms_shipcab WHERE BRAND='AI' ) s ON (v.BRAND=s.BRAND AND v.SHIP_CODE=s.SHIP_CODE AND v.CABIN_VERSION=s.CABIN_VERSION) WHERE v.BRAND <>'CL' AND v.SAIL_DATE>'01/01/2017' AND (u.BRAND is NULL OR s.BRAND IS NULL) --------------------------------------- union all SELECT DISTINCT v.COMPANY_CODE BRAND, v.SHIP_CODE, v.VOYAGE, CAST(v.CABIN_VERSION AS NUMBER) CABIN_VERSION, '' CATEGORY,'RMSVOYAGE' SRC FROM RMS_DB_PRD1.RMS.RMS_VOYAGE v LEFT JOIN (SELECT DISTINCT BRAND, SHIP_CODE, CABIN_VERSION FROM RMS_DB_PRD1.RMS.UMF_LEAD_CATEGORY WHERE BRAND <>'CL') u ON (v.COMPANY_CODE=u.BRAND AND v.SHIP_CODE=u.SHIP_CODE AND v.CABIN_VERSION=u.CABIN_VERSION) WHERE v.COMPANY_CODE<>'CL' AND u.BRAND is null ------------------------------------------ ; --AGGREAGTE CABIN VERSION CHECK TO VOYAGE/LIKE SAILING LEVEL SO ITS ONLY FLAGGING UP AN ISSUE IF WE ARE MISSING CATEGORY MAPPING FOR THE VOYAGE OR ITS LIKE SAILINGS DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION AS SELECT l.BRAND, l.VOYAGE_KEY, l.LIKE_SAILING, c1.SRC FUT_VOY_SRC, c2.SRC LS_SRC FROM RMS_DB_PRD1.RMS.DF_LIKE_SAILINGS l LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION_DETAIL c1 ON (l.BRAND=c1.BRAND AND l.VOYAGE=c1.VOYAGE) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION_DETAIL c2 ON (l.BRAND=c2.BRAND AND l.VOYAGE_LIKE=c2.VOYAGE) WHERE l.BRAND<>'CL' AND (c1.SRC IS NOT NULL OR c2.SRC IS NOT NULL) ; ---DOES THE VOYAGE HAVE CURRENT PRICES? DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE AS SELECT * FROM RMS_DB_PRD1.RMS.RM_CUR_LEAD_PRICE WHERE BRAND<>'CL' AND LEAD_PRICE is not null AND LEAD_PRICE>0 ; --NO MISSING FARE TYPE, SUBMETA, REVIEWED MARKET OR BASE MARKET MAPPING DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_MAPPINGS_MISSING_BKGS; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_MAPPINGS_MISSING_BKGS AS SELECT a.*, CASE WHEN BKGS_SM_MISSING>20 THEN 0 ELSE 1 END SM_MAP_MISSING_CHECK, CASE WHEN BKGS_FT_MISSING>20 THEN 0 ELSE 1 END FT_MAP_MISSING_CHECK, CASE WHEN BKGS_RM_MISSING>20 THEN 0 ELSE 1 END RM_MAP_MISSING_CHECK, CASE WHEN BKGS_BM_MISSING>20 THEN 0 ELSE 1 END BM_MAP_MISSING_CHECK FROM ( SELECT BRAND, VOYAGE_KEY, SUM(CASE WHEN ((BRAND='PA' AND GUEST_ID IN ('01','02')) OR (BRAND<>'PA' AND BERTH_TYPE ='Lower')) THEN ADJ_PAX_CNT ELSE 0 END) LB_BKGS, SUM(CASE WHEN ((BRAND='PA' AND GUEST_ID IN ('01','02')) OR (BRAND<>'PA' AND BERTH_TYPE ='Lower')) AND GUEST_TYPE='FIT' AND FARE_TYPE IN ('Standard','Restricted') THEN ADJ_PAX_CNT ELSE 0 END) LB_PUB_FIT_BKGS, SUM(CASE WHEN BOOKED_SUBMETA='Other' THEN ADJ_PAX_CNT ELSE 0 END) BKGS_SM_MISSING, SUM(CASE WHEN FARE_TYPE='Other' THEN ADJ_PAX_CNT ELSE 0 END) BKGS_FT_MISSING, SUM(CASE WHEN REVIEWED_MARKET='Other' THEN ADJ_PAX_CNT ELSE 0 END) BKGS_RM_MISSING, SUM(CASE WHEN BASE_MARKET='Other' THEN ADJ_PAX_CNT ELSE 0 END) BKGS_BM_MISSING FROM RMS_DB_PRD1.RMS.DF_SUMMARY_PREP a WHERE BRAND <>'CL' AND SAIL_DATE>'01/01/2017' AND BOOKED_CABIN_CATEGORY<>'Y' AND NET_FARE_BRAND>0 AND CANCEL_WP IS NULL GROUP BY BRAND, VOYAGE_KEY ) a ; --CHECK IF SUBMETA CURRENT PRICES ARE INVERTED AT THE NET LEVEL DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_SM_INV; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_SM_INV AS with PRICES AS ( SELECT BRAND, SHIP_CODE, VOYAGE,VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE, PRODUCT,SUBMETA,FARE_TYPE, LEAD_PRICE FROM RMS_DB_PRD1.RMS.RM_CUR_LEAD_PRICE WHERE BRAND <>'CL' AND BERTH_TYPE='Lower' AND FILLIN_FLAG is NULL ) -- SELECT a.*, ((LEAD_PRICE-ABOVE_SM_FARE)/ABOVE_SM_FARE) SM_INV_PCT, CASE WHEN ((LEAD_PRICE-ABOVE_SM_FARE)/ABOVE_SM_FARE) > 0 THEN 0 ELSE 1 END CUR_NET_SM_INV_CHECK, CASE WHEN ((LEAD_PRICE-ABOVE_SM_FARE)/ABOVE_SM_FARE) BETWEEN 0.0001 AND 0.05 THEN 1 ELSE 0 END CUR_NET_SM_5PCT_INV_CHECK, CASE WHEN ((LEAD_PRICE-ABOVE_SM_FARE)/ABOVE_SM_FARE) BETWEEN 0.0001 AND 0.1 THEN 1 ELSE 0 END CUR_NET_SM_10PCT_INV_CHECK, CASE WHEN ((LEAD_PRICE-ABOVE_SM_FARE)/ABOVE_SM_FARE) BETWEEN 0.0001 AND 0.15 THEN 1 ELSE 0 END CUR_NET_SM_15PCT_INV_CHECK FROM ( SELECT a.*, LAG(LEAD_PRICE,1,null) OVER (PARTITION BY BRAND, SHIP_CODE, VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE, PRODUCT,FARE_TYPE ORDER BY SUBMETA_ORDER) ABOVE_SM_FARE FROM ( SELECT p.BRAND, p.SHIP_CODE, p.VOYAGE_KEY,p.BASE_MARKET, p.PRODUCT_TYPE,p.PRODUCT, p.FARE_TYPE, p.SUBMETA, s.SUBMETA_ORDER,p.LEAD_PRICE FROM PRICES p LEFT JOIN RMS_DB_PRD1.RMS.DIM_SUBMETA s ON (p.BRAND=s.BRAND AND p.SUBMETA=s.RAW_SUBMETA) ) a ) a WHERE LEAD_PRICE>ABOVE_SM_FARE AND LEAD_PRICE<99999 --AND NOT (LEAD_PRICE<BELOW_SM_FARE AND SHIP_CODE LIKE 'QM' AND SUBMETA LIKE 'B') --AND NOT (LEAD_PRICE>ABOVE_SM_FARE AND SHIP_CODE LIKE 'QM' AND SUBMETA LIKE 'O') ; --CHECK IF FARE TYPE CURRENT PRICES ARE INVERTED AT THE NET LEVEL DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_FT_INV; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_FT_INV AS with PRICES AS ( SELECT BRAND, SHIP_CODE, VOYAGE,VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE, PRODUCT,SUBMETA,FARE_TYPE, LEAD_PRICE FROM RMS_DB_PRD1.RMS.RM_CUR_LEAD_PRICE WHERE BRAND <>'CL' AND BERTH_TYPE='Lower' AND FILLIN_FLAG is NULL AND SAIL_DATE>CURRENT_DATE() ) -- SELECT a.*, ((RSTR_FARE-STD_FARE)/STD_FARE) FT_INV_PCT, CASE WHEN ((RSTR_FARE-STD_FARE)/STD_FARE)>0 THEN 0 ELSE 1 END CUR_NET_FT_INV_CHECK, CASE WHEN ((RSTR_FARE-STD_FARE)/STD_FARE) BETWEEN 0.0001 AND 0.05 THEN 1 ELSE 0 END CUR_NET_FT_INV_5PCT_CHECK, CASE WHEN ((RSTR_FARE-STD_FARE)/STD_FARE) BETWEEN 0.0001 AND 0.1 THEN 1 ELSE 0 END CUR_NET_FT_INV_10PCT_CHECK, CASE WHEN ((RSTR_FARE-STD_FARE)/STD_FARE) BETWEEN 0.0001 AND 0.15 THEN 1 ELSE 0 END CUR_NET_FT_INV_15PCT_CHECK FROM ( SELECT * FROM ( SELECT p.BRAND, p.SHIP_CODE, p.VOYAGE_KEY,p.BASE_MARKET, p.PRODUCT_TYPE,p.PRODUCT,p.FARE_TYPE, p.SUBMETA, p.LEAD_PRICE FROM PRICES p ) PIVOT ( SUM(LEAD_PRICE) FOR FARE_TYPE IN ('Standard', 'Restricted') ) AS p (BRAND,SHIP_CODE,VOYAGE_KEY,BASE_MARKET,PRODUCT_TYPE,PRODUCT,SUBMETA,STD_FARE,RSTR_FARE) --WHERE NOT (BASE_MARKET='United Kingdom' AND SUBMETA='S') ) a WHERE STD_FARE IS NOT NULL AND RSTR_FARE IS NOT NULL AND STD_FARE<RSTR_FARE ; SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_FT_INV WHERE BRAND='HA' AND BASE_MARKET<>'ROW'; SELECT * --BRAND, SHIP_CODE, VOYAGE,VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE, PRODUCT,SUBMETA,FARE_TYPE, LEAD_PRICE FROM RMS_DB_PRD1.RMS.RM_CUR_LEAD_PRICE WHERE BRAND <>'CL' AND BERTH_TYPE='Lower' AND VOYAGE_KEY='U337-20230826BOSYQB' AND SUBMETA='F' ; --CHECK IF FARE TYPE CURRENT PRICES ARE INVERTED AT THE NET LEVEL DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_INV; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_INV AS --SELECT unique BRAND, VOYAGE_KEY --FROM ( SELECT p.BRAND, p.VOYAGE_KEY, nvl(s.CUR_NET_SM_INV_CHECK,1) CUR_NET_SM_INV_CHECK, nvl(s.CUR_NET_SM_5PCT_INV_CHECK,1) CUR_NET_SM_5PCT_INV_CHECK, nvl(s.CUR_NET_SM_10PCT_INV_CHECK,1) CUR_NET_SM_10PCT_INV_CHECK, nvl(s.CUR_NET_SM_15PCT_INV_CHECK,1) CUR_NET_SM_15PCT_INV_CHECK, -- nvl(CUR_NET_FT_INV_CHECK,1) CUR_NET_FT_INV_CHECK, nvl(CUR_NET_FT_5PCT_INV_CHECK,1) CUR_NET_FT_5PCT_INV_CHECK, nvl(CUR_NET_FT_10PCT_INV_CHECK,1) CUR_NET_FT_10PCT_INV_CHECK, nvl(CUR_NET_FT_15PCT_INV_CHECK,1) CUR_NET_FT_15PCT_INV_CHECK FROM RMS_DB_PRD1.RMS.MV_RM_VOYAGE p LEFT JOIN ( SELECT BRAND, VOYAGE_KEY, MIN(nvl(CUR_NET_SM_INV_CHECK,1)) CUR_NET_SM_INV_CHECK, MIN(nvl(CUR_NET_SM_5PCT_INV_CHECK,1)) CUR_NET_SM_5PCT_INV_CHECK, MIN(nvl(CUR_NET_SM_10PCT_INV_CHECK,1)) CUR_NET_SM_10PCT_INV_CHECK, MIN(nvl(CUR_NET_SM_15PCT_INV_CHECK,1)) CUR_NET_SM_15PCT_INV_CHECK FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_SM_INV GROUP BY BRAND, VOYAGE_KEY )s ON (p.BRAND=s.BRAND AND p.VOYAGE_KEY=s.VOYAGE_KEY) LEFT JOIN ( SELECT BRAND, VOYAGE_KEY, MIN(nvl(CUR_NET_FT_INV_CHECK,1)) CUR_NET_FT_INV_CHECK, MIN(nvl(CUR_NET_FT_INV_5PCT_CHECK,1)) CUR_NET_FT_5PCT_INV_CHECK, MIN(nvl(CUR_NET_FT_INV_10PCT_CHECK,1)) CUR_NET_FT_10PCT_INV_CHECK, MIN(nvl(CUR_NET_FT_INV_15PCT_CHECK,1)) CUR_NET_FT_15PCT_INV_CHECK FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_FT_INV GROUP BY BRAND, VOYAGE_KEY ) f ON (p.BRAND=f.BRAND AND p.VOYAGE_KEY=f.VOYAGE_KEY) WHERE p.SAIL_DATE>BATCH_DATE AND p.VOYAGE_STATUS<>'C' --) ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_INV; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_FT_INV; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_SM_INV; ---AIR COST TO PAX AND COST TO BUSINESS EXIST AND SIMILAR DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_AIR_COST; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_AIR_COST AS WITH PAX_AIR_COST AS ( SELECT c.BRAND, c.VOYAGE_KEY, BASE_MARKET, PRODUCT_TYPE, PRODUCT, MAX(nvl(AIR_ADD_ON_BRAND,0)) PAX_AIR_COST FROM RMS_DB_PRD1.RMS.RM_CUR_LEAD_PRICE c LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (c.BRAND=v.BRAND AND c.VOYAGE_KEY=v.VOYAGE_KEY) WHERE c.BRAND<>'CL' --AND VOYAGE_KEY='SM07221211-20221211MRSMRS' AND c.PRODUCT_TYPE='Air' AND c.SAIL_DATE>v.BATCH_DATE GROUP BY c.BRAND, c.VOYAGE_KEY, BASE_MARKET, PRODUCT_TYPE, PRODUCT ) -- , BUS_AIR_COST AS ( SELECT BRAND, VOYAGE_KEY, BASE_MARKET, AIR_CITY, nvl(AIR_COST,0) BUS_AIR_COST FROM RMS_DB_PRD1.RMS.OPT_MODEL_INPUT_AIR_COST WHERE BRAND<>'CL' --AND VOYAGE_KEY='SM07221211-20221211MRSMRS' ) -- SELECT a.*, --CAN HAVE AN AIR COST TO BUSINESS BUT NOT TO THE CUSTOMER (FOR BUNDLED PRODUCT) CASE WHEN BUS_AIR_COST=0 OR ABS(AIR_COST_DIFF_PCT)>1 THEN 0 ELSE 1 END AIR_COST_CHECK FROM ( SELECT p.*, nvl(b.BUS_AIR_COST,0) BUS_AIR_COST, DIV0NULL((PAX_AIR_COST -nvl(b.BUS_AIR_COST,0)),nvl(b.BUS_AIR_COST,0)) AIR_COST_DIFF_PCT FROM PAX_AIR_COST p LEFT JOIN BUS_AIR_COST b ON (p.BRAND=b.BRAND AND p.VOYAGE_KEY=b.VOYAGE_KEY AND p.BASE_MARKET=b.BASE_MARKET AND p.PRODUCT=b.AIR_CITY) ) a ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_AIR_COST WHERE BRAND='PO'; ----------- --COMBINE ALL REFERENCE DATA CHECKS INTO ONE TABLE DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_REF_CHECKS; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_REF_CHECKS AS SELECT DISTINCT v.BRAND, v.VOYAGE_KEY, v.VOYAGE, v.TRADE_NAME, v.SUBTRADE_NAME, EXTRACT(YEAR FROM ADD_MONTHS(v.SAIL_DATE,1)) FIN_YR, CASE WHEN EXTRACT(MONTH FROM v.SAIL_DATE) in (12,1,2) THEN 'Q1' WHEN EXTRACT(MONTH FROM v.SAIL_DATE) in (3,4,5) THEN 'Q2' WHEN EXTRACT(MONTH FROM v.SAIL_DATE) in (6,7,8) THEN 'Q3' ELSE 'Q4' END FIN_QTR, CASE WHEN v.DURATION<=6 THEN '1 to 6' WHEN v.DURATION=7 THEN '7' WHEN v.DURATION=8 THEN '8' WHEN v.DURATION<=13 THEN '9 to 13' WHEN v.DURATION=14 THEN '14' WHEN v.DURATION<=25 THEN '15 to 25' ELSE '25+' END DURATION_GRP, v.EMBARK_PORT||v.DEBARK_PORT ITIN, v.SEASON, v.VOYAGE_STATUS, --VOYAGE DATA CHECKS a.ABG_CHECK,a.SUPPORT_RMS_CHECK,a.DISPLAY_FLAG_CHECK,a.ATTR_CHECK, --CABIN VERSION CHECK CASE WHEN i.BRAND is null then 1 ELSE 0 END CABIN_VERSION_CHECK, i.SRC CABIN_VERSION_FAIL, --BASE MARKET CHECK CASE WHEN nvl(mm.BM_MAP_MISSING_CHECK,1)=0 AND bm.VOYAGE_KEY IS NULL THEN 0 ELSE 1 END BASE_MARKET_MAPPING_CHECK, --CURRENT PRICE CHECK CASE WHEN d.VOYAGE_KEY is null then 0 ELSE 1 END CUR_PRICE_CHECK, --MISSING MAPPINGS nvl(mm.SM_MAP_MISSING_CHECK,1) SM_MAP_MISSING_CHECK, nvl(mm.FT_MAP_MISSING_CHECK,1) FT_MAP_MISSING_CHECK, nvl(mm.RM_MAP_MISSING_CHECK,1) RM_MAP_MISSING_CHECK, --INV nvl(inv.CUR_NET_SM_INV_CHECK,1) CUR_NET_SM_INV_CHECK,nvl(inv.CUR_NET_SM_5PCT_INV_CHECK,1) CUR_NET_SM_5PCT_INV_CHECK,nvl(inv.CUR_NET_SM_10PCT_INV_CHECK,1) CUR_NET_SM_10PCT_INV_CHECK,nvl(inv.CUR_NET_SM_15PCT_INV_CHECK,1) CUR_NET_SM_15PCT_INV_CHECK, nvl(inv.CUR_NET_FT_INV_CHECK,1) CUR_NET_FT_INV_CHECK,nvl(inv.CUR_NET_FT_5PCT_INV_CHECK,1) CUR_NET_FT_5PCT_INV_CHECK,nvl(inv.CUR_NET_FT_10PCT_INV_CHECK,1) CUR_NET_FT_10PCT_INV_CHECK,nvl(inv.CUR_NET_FT_15PCT_INV_CHECK,1) CUR_NET_FT_15PCT_INV_CHECK, --AIR COST CHECK CASE WHEN ac.VOYAGE_KEY IS NOT NULL THEN 0 ELSE 1 END AIR_COST_CHECK -- FROM RMS_DB_PRD1.RMS.MV_RM_VOYAGE v LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_VOYAGE_ATTR a ON (v.BRAND=a.BRAND AND v.VOYAGE_KEY=a.VOYAGE_KEY) LEFT JOIN (SELECT DISTINCT BRAND, VOYAGE_KEY FROM RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE) d ON (v.BRAND=d.BRAND AND v.VOYAGE_KEY=d.VOYAGE_KEY) LEFT JOIN (SELECT DISTINCT BRAND, VOYAGE_KEY, LISTAGG(DISTINCT FUT_VOY_SRC,'|') OVER (PARTITION BY BRAND, VOYAGE_KEY) SRC FROM RMSPOA_DB_UAT1.RMS.X_HC_CABIN_VERSION WHERE FUT_VOY_SRC IS NOT NULL) i ON (v.BRAND=i.BRAND AND v.VOYAGE_KEY=i.VOYAGE_KEY) LEFT JOIN (SELECT DISTINCT BRAND, VOYAGE_KEY FROM RMS_DB_PRD1.RMS.MV_RM_BASE_MARKET_MAPPING WHERE BRAND<>'CL' AND REVIEWED_MARKET=BASE_MARKET) bm ON (v.BRAND=bm.BRAND AND v.VOYAGE_KEY=bm.VOYAGE_KEY) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_MAPPINGS_MISSING_BKGS mm ON (v.BRAND=mm.BRAND AND v.VOYAGE_KEY=mm.VOYAGE_KEY) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_CUR_PRICE_INV inv ON (v.BRAND=inv.BRAND AND v.VOYAGE_KEY=inv.VOYAGE_KEY) LEFT JOIN (SELECT BRAND, VOYAGE_KEY FROM RMSPOA_DB_UAT1.RMS.X_HC_AIR_COST WHERE AIR_COST_CHECK=0) ac ON (v.BRAND=ac.BRAND AND v.VOYAGE_KEY=ac.VOYAGE_KEY) -- WHERE v.BRAND <> ('CL') AND v.SAIL_DATE>v.BATCH_DATE AND v.DISPLAY_IND='Y' AND v.VOYAGE_STATUS in ('A','B') AND v.BRAND||v.VOYAGE not in (SELECT BRAND||VOYAGE FROM RMS_DB_PRD1.RMS.UMF_EXCLUDED_HIST_VOYAGE WHERE BRAND<>'CL') ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_VOYAGE_ATTR WHERE VOYAGE_KEY='J401-20240222SYD1SOU2'; ------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------LIKE SAILINGS CHECK------ ------------------------------- --LIKE SAILINGS EXIST --LIKE SAILINGS OCC/CLF AT REASONABLE LEVEL --LIKE SAILING SUBMETAS OVERBOOKING STRATEGY SIMILAR --LIKE SAILING HAS REASONABLE AMOUNT OF OPTIMISABLE FIT DMD (Not just GRPS, NEGOTIATED or CONCESSIONS CRUISE) --ALLOC CAP SIMILARITY --NEGOTIATED /GRP EXPECTATION SIMILAR --SAME REVIEW MARKETS AS FUTURE VOYAGE --SAME BASE MARKETS AS FUTURE VOYAGE --PPD SIMILAR ACROSS LIKE SAILINGS (INFLATION) --VOYAGE CUR OCC SIMILAR TO AVG LS OCC AT SAME POINT ----CHECK IF THERE ARE ANY LIKE SAILINGS, IF NO THEN FAIL DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_COUNT; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_COUNT AS SELECT v.BRAND, v.SHIP_CODE,v.TRADE_NAME, v.SUBTRADE_NAME, v.VOYAGE_KEY,v.CABIN_VERSION,v.VOYAGE_STATUS, v.DURATION, v.SAIL_DATE, v.EMBARK_PORT, v.DEBARK_PORT, v.SAILING_TRIP_TYPE, v.HOLIDAY_IND, v.SPECIAL_EVENT_FLAG, v.SHIP_CLASS, EXTRACT(YEAR FROM ADD_MONTHS(v.SAIL_DATE,1)) FIN_YR, CASE WHEN EXTRACT(MONTH FROM v.SAIL_DATE) in (12,1,2) THEN 'Q1' WHEN EXTRACT(MONTH FROM v.SAIL_DATE) in (3,4,5) THEN 'Q2' WHEN EXTRACT(MONTH FROM v.SAIL_DATE) in (6,7,8) THEN 'Q3' ELSE 'Q4' END FIN_QTR, CASE WHEN DURATION<=6 THEN '1 to 6' WHEN DURATION=7 THEN '7' WHEN DURATION=8 THEN '8' WHEN DURATION<=13 THEN '9 to 13' WHEN DURATION=14 THEN '14' WHEN DURATION<=25 THEN '15 to 25' ELSE '25+' END DURATION_GRP, -- SUM(CASE WHEN LIKE_SAILING is null then 0 else 1 END) LS_COUNT, LISTAGG(VOYAGE_LIKE,', ') WITHIN GROUP (ORDER BY LIKE_SAILING) LS_LIST FROM RMS_DB_PRD1.RMS.MV_RM_VOYAGE v LEFT JOIN (SELECT * FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT WHERE BRAND <>'CL' AND SAIL_DATE>BATCH_DATE AND OUTLIER_FLAG=0) o ON (v.BRAND=o.BRAND AND v.VOYAGE_KEY=o.VOYAGE_KEY) WHERE v.BRAND <>'CL' AND v.SAIL_DATE>v.BATCH_DATE AND v.DISPLAY_IND='Y' AND v.VOYAGE_STATUS in ('A','B') --FILTER OUT 2 DAY AND NO ALLOC CAP VOYAGES AND v.BRAND||v.VOYAGE not in (SELECT BRAND||VOYAGE FROM RMS_DB_PRD1.RMS.UMF_EXCLUDED_HIST_VOYAGE WHERE BRAND <>'CL') GROUP BY v.BRAND, v.SHIP_CODE,v.VOYAGE_KEY,v.CABIN_VERSION,v.VOYAGE_STATUS,v.TRADE_NAME, v.SUBTRADE_NAME, v.DURATION, v.SAIL_DATE, v.EMBARK_PORT, v.DEBARK_PORT, v.SAILING_TRIP_TYPE, v.HOLIDAY_IND, v.SPECIAL_EVENT_FLAG, v.SHIP_CLASS ; --GET THE CAPACITY DATA FOR OCC AND CLF CHECKS DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS AS SELECT BRAND, SHIP_CODE, CABIN_VERSION, VOYAGE_KEY, VOYAGE, SAIL_DATE, BOOKED_SUBMETA, --LB ONLY BRANDS SUM(nvl(NON_REV_LB_ALLOC_CAP,0)) NON_REV_LB_ALLOC_CAP, SUM(nvl(LB_ALLOC_CAP,0)) LB_ALLOC_CAP, SUM(nvl(LB_SHIP_CAP,0)) LB_SHIP_CAP, --TWAD BRANDS SUM(nvl(NON_REV_QD_ALLOC_CAP,0)) NON_REV_QD_ALLOC_CAP, SUM(nvl(QD_ALLOC_CAP,0)) QD_ALLOC_CAP, SUM(nvl(NON_REV_QD_LB_ALLOC_CAP,0)) NON_REV_QD_LB_ALLOC_CAP, SUM(nvl(QD_LB_ALLOC_CAP,0)) QD_LB_ALLOC_CAP, SUM(nvl(SHIP_TW_CAP,0)) TW_SHIP_CAP, SUM(nvl(SHIP_QD_CAP,0)) QD_SHIP_CAP, SUM(nvl(SHIP_QD_LB_CAP,0)) QD_LB_SHIP_CAP FROM ( SELECT v.BRAND, v.SHIP_CODE, v.CABIN_VERSION, v.VOYAGE_KEY,v.VOYAGE, v.SAIL_DATE, u.MODEL_SUBMETA BOOKED_SUBMETA, u.POLAR_CATEGORY CATEGORY, --LOWER BERTHS nr.LOWER_BERTHS NON_REV_LB_ALLOC_CAP, cap.LOWER_BERTHS LB_ALLOC_CAP, --QUADS nr.TOTAL_BERTHS - nr.TWINS_LB NON_REV_QD_ALLOC_CAP, cap.TOTAL_BERTHS - cap.TWINS_LB QD_ALLOC_CAP, --QUADS_LB nr.QUADS_LB NON_REV_QD_LB_ALLOC_CAP, cap.QUADS_LB QD_LB_ALLOC_CAP, --SHIP ship_cap.LOWER_BERTHS LB_SHIP_CAP, ship_cap.TWINS_LB SHIP_TW_CAP, ship_cap.TOTAL_BERTHS - ship_cap.TWINS_LB SHIP_QD_CAP, ship_cap.QUADS_LB SHIP_QD_LB_CAP FROM RMS_DB_PRD1.RMS.MV_RM_VOYAGE v LEFT JOIN RMS_DB_PRD1.RMS.UMF_LEAD_CATEGORY u ON (v.BRAND=u.BRAND AND v.SHIP_CODE=u.SHIP_CODE AND v.CABIN_VERSION=u.CABIN_VERSION) LEFT JOIN RMS_DB_PRD1.RMS.mv_rm_non_revenue_capacity nr ON (v.BRAND=nr.BRAND AND v.VOYAGE=nr.VOYAGE AND v.SHIP_CODE=nr.SHIP_CODE AND v.CABIN_VERSION=nr.CABIN_VERSION AND u.POLAR_CATEGORY=nr.CATEGORY) LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_ALLOCATED_CAPACITY cap ON (v.BRAND=cap.BRAND AND v.VOYAGE=cap.VOYAGE AND v.SHIP_CODE=cap.SHIP_CODE AND v.CABIN_VERSION=cap.CABIN_VERSION AND u.POLAR_CATEGORY=cap.CATEGORY) LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_SHIP_CAPACITY ship_cap ON (v.BRAND=ship_cap.BRAND AND v.SHIP_CODE=ship_cap.SHIP_CODE AND v.CABIN_VERSION=ship_cap.CABIN_VERSION AND u.POLAR_CATEGORY=ship_cap.CATEGORY) where v.BRAND <>'CL' AND v.SAIL_DATE>'01/01/2017' AND v.VOYAGE_STATUS<>'C' --AND v.SAIL_DATE<='01/JAN/2020' --AND VOYAGE_STATUS <> 'C' --AND DISPLAY_IND='Y' ) GROUP BY BRAND, SHIP_CODE, CABIN_VERSION, VOYAGE_KEY, VOYAGE, SAIL_DATE, BOOKED_SUBMETA ; --CHECK OCC AND CLF --CHECK PROPORTION OF VOYAGE ALLOCATED LB CAPACITY VS SHIP CAPACITY --CHECK PROPORTION OF PUBLIC FIT DMD VS CAPACITY DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_OCC; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_OCC AS SELECT a.*, --CHECKS CASE WHEN LB_ALLOC_CAP_PROP>=0.1 THEN 1 ELSE 0 END LB_ALLOC_CAP_CHECK, CASE WHEN LB_OCC>=0.8 THEN 1 ELSE 0 END LB_OCC_CHECK, CASE WHEN LB_PUB_FIT_DMD_PROP>=0.3 THEN 1 ELSE 0 END PUB_FIT_DMD_CHECK FROM ( SELECT c.BRAND, c.VOYAGE_KEY, c.VOYAGE, c.LB_ALLOC_CAP, c.LB_SHIP_CAP, nvl(b.LB_BKGS,0) LB_BKGS, DIV0NULL(c.LB_ALLOC_CAP,c.LB_SHIP_CAP) LB_ALLOC_CAP_PROP, CASE WHEN c.LB_ALLOC_CAP=0 THEN 1 ELSE nvl(b.LB_BKGS,0)/c.LB_ALLOC_CAP END LB_OCC, CASE WHEN nvl(b.LB_BKGS,0) = 0 THEN 1 ELSE nvl(LB_PUB_FIT_BKGS,0)/nvl(b.LB_BKGS,0) END LB_PUB_FIT_DMD_PROP FROM ( SELECT c.BRAND, VOYAGE_KEY, VOYAGE, SUM(LB_ALLOC_CAP + CASE WHEN tw.TWAD_FLAG=1 THEN QD_LB_ALLOC_CAP ELSE 0 END) LB_ALLOC_CAP, SUM(LB_SHIP_CAP + CASE WHEN tw.TWAD_FLAG=1 THEN QD_LB_SHIP_CAP ELSE 0 END) LB_SHIP_CAP FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS c LEFT JOIN (SELECT BRAND, VALUE TWAD_FLAG FROM RMS_DB_PRD1.RMS.RM_BRAND_PARAM WHERE PARAM_NAME = 'TWINS_QUAD_MODE') tw ON (c.BRAND=tw.BRAND) GROUP BY c.BRAND, VOYAGE_KEY, VOYAGE ) c LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_MAPPINGS_MISSING_BKGS b ON (c.BRAND=b.BRAND AND c.VOYAGE_KEY=b.VOYAGE_KEY) ) a ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS; ------------------ DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_OCC; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_OCC AS SELECT a.*, DIV0NULL(a.CUM_NET_PAX,b.LB_SHIP_CAP) FIT_OCC, b.LB_SHIP_CAP FROM ( SELECT a.*, SUM(INC_NET_PAX) OVER (PARTITION BY BRAND, VOYAGE_KEY ORDER BY WP DESC) CUM_NET_PAX, MIN(WP) OVER (PARTITION BY BRAND, VOYAGE_KEY) CUR_WP FROM ( SELECT BRAND, VOYAGE_KEY, WP, SUM(INC_NET_PAX) INC_NET_PAX FROM RMS_DB_PRD1.RMS.DF_BKG_CURVES WHERE SAIL_DATE>='01/01/2017' --AND VOYAGE_KEY='I348-20231023BNEBNE' AND INC_NET_PAX IS NOT NULL GROUP BY BRAND, VOYAGE_KEY, WP ORDER BY 1,2,3 ) a ) a LEFT JOIN ( SELECT BRAND, VOYAGE_KEY, SUM(LB_ALLOC_CAP) LB_SHIP_CAP FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS GROUP BY BRAND, VOYAGE_KEY ) b ON (a.BRAND=b.BRAND AND a.VOYAGE_KEY=b.VOYAGE_KEY) ORDER BY 1,2,3 DESC ; DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_LS_OCC; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_LS_OCC AS SELECT l.BRAND, l.VOYAGE_KEY, l.LIKE_SAILING, l.WEIGHT, o.WP, o.CUR_WP, nvl(o.FIT_OCC,0) FIT_OCC, nvl(lso.FIT_OCC,0) LS_FIT_OCC, nvl(o.FIT_OCC,0) - nvl(lso.FIT_OCC,0) OCC_DIFF, CASE WHEN ABS(nvl(o.FIT_OCC,0) - nvl(lso.FIT_OCC,0))>0.2 THEN 0 ELSE 1 END LS_FIT_OCC_DIFF_CHECK FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT l LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_OCC o ON (l.BRAND=o.BRAND AND l.VOYAGE_KEY=o.VOYAGE_KEY) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_OCC lso ON (l.BRAND=lso.BRAND AND l.LIKE_SAILING=lso.VOYAGE_KEY AND o.WP=lso.WP) WHERE l.BRAND<>'CL' AND l.OUTLIER_FLAG=0 AND l.SAIL_DATE>BATCH_DATE ORDER BY 1,2,3,4,5 DESC ; --CHECK OVERBOOKING STRATEGY OF SAILED VOYAGES DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_SM_OB_STRAT; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_SM_OB_STRAT AS WITH OB_BKGS AS ( SELECT BRAND, SHIP_CODE, VOYAGE_KEY, VOYAGE, BERTHED_SUBMETA, SUM(ADJ_PAX_CNT) OB_BKGS FROM RMS_DB_PRD1.RMS.DF_SUMMARY_PREP WHERE BRAND <> 'CL' AND SAIL_DATE>'01/01/2017' AND CANCEL_WP IS NULL AND GUEST_ID IN ('01','02') AND GUEST_TYPE='FIT' AND BERTHED_SUBMETA IS NOT NULL AND BERTHED_SUBMETA<>'Other' AND BOOKED_SUBMETA<>BERTHED_SUBMETA GROUP BY BRAND, SHIP_CODE, VOYAGE_KEY, VOYAGE, BERTHED_SUBMETA ) -- , OB_STRAT AS ( SELECT c.BRAND, c.VOYAGE_KEY, c.VOYAGE, c.SAIL_DATE, c.BOOKED_SUBMETA SUBMETA, nvl(o.OB_BKGS,0) OB_BKGS, DIV0(nvl(o.OB_BKGS,0),SUM(nvl(c.LB_SHIP_CAP,0) + CASE WHEN tw.TWAD_FLAG=1 THEN nvl(c.QD_LB_SHIP_CAP,0) ELSE 0 END) OVER (PARTITION BY c.BRAND, c.VOYAGE_KEY)) OB_PROP FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_SM_CAPS c LEFT JOIN OB_BKGS o ON (o.BRAND=c.BRAND AND o.VOYAGE_KEY=c.VOYAGE_KEY AND o.BERTHED_SUBMETA=c.BOOKED_SUBMETA) LEFT JOIN (SELECT BRAND, VALUE TWAD_FLAG FROM RMS_DB_PRD1.RMS.RM_BRAND_PARAM WHERE PARAM_NAME = 'TWINS_QUAD_MODE') tw ON (c.BRAND=tw.BRAND) WHERE c.BRAND <> 'CL' ) -- SELECT a.*, MAX_LS_SM_OB - MIN_LS_SM_OB MAX_SM_OB_DIFF, CASE WHEN MAX_LS_SM_OB - MIN_LS_SM_OB >0.15 THEN 0 ELSE 1 END SM_OB_CHECK FROM ( SELECT l.BRAND, l.VOYAGE_KEY, l.LIKE_SAILING, l.WEIGHT, o.SUBMETA, o.OB_BKGS, o.OB_PROP, MAX(o.OB_PROP) OVER (PARTITION BY l.BRAND, l.VOYAGE_KEY,o.SUBMETA) MAX_LS_SM_OB, MIN(o.OB_PROP) OVER (PARTITION BY l.BRAND, l.VOYAGE_KEY,o.SUBMETA) MIN_LS_SM_OB FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT l LEFT JOIN OB_STRAT o ON (l.BRAND=o.BRAND AND l.LIKE_SAILING=o.VOYAGE_KEY) LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE vl ON (l.BRAND=vl.BRAND AND l.LIKE_SAILING=vl.VOYAGE_KEY) WHERE l.BRAND<>'CL' --AND l.VOYAGE_KEY='FO07190804-20190804TRGTRG' AND l.SAIL_DATE>l.BATCH_DATE AND l.OUTLIER_FLAG=0 AND vl.SAIL_DATE<=l.BATCH_DATE AND o.SUBMETA IS NOT NULL --AND o.SUBMETA='Balcony' ) a ; --CHECK PPD OF VOYAGES DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_PPD; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_PPD AS SELECT BRAND, VOYAGE_KEY, BOOKED_SUBMETA,BERTH_TYPE, MIN(LS_PPD) PPD_MIN, MAX(CASE WHEN QUARTILE = 1 THEN LS_PPD END) PPD_Q1, MAX(CASE WHEN QUARTILE = 2 THEN LS_PPD END) PPD_MED, MAX(CASE WHEN QUARTILE = 3 THEN LS_PPD END) PPD_Q3, MAX(LS_PPD) PPD_MAX, COUNT(*) CNT FROM ( SELECT s.BRAND, VOYAGE_KEY, BOOKED_SUBMETA, BERTH_TYPE, DIV0NULL(NET_FARE_BRAND,DURATION) LS_PPD, NTILE(4) OVER (PARTITION BY s.BRAND, VOYAGE_KEY, BOOKED_SUBMETA, BERTH_TYPE ORDER BY DIV0NULL(NET_FARE_BRAND,DURATION)) QUARTILE FROM RMS_DB_PRD1.RMS.DF_SUMMARY_PREP s LEFT JOIN (SELECT BRAND, VALUE, PARAM_NAME FROM RMS_DB_PRD1.RMS.RM_BRAND_PARAM WHERE PARAM_NAME = 'TWINS_QUAD_MODE') tw ON (s.BRAND=tw.BRAND) WHERE s.BRAND<>'CL' AND FARE_TYPE_2='Public' AND BKG_STATUS<>'C' AND NET_FARE_BRAND>50 AND SAIL_DATE>'01/01/2017' --ONLY LOOK AT LOWERS WITHIN TWINDS AND QUADS AND (tw.VALUE=0 OR GUEST_ID IN ('01','02')) ) a GROUP BY BRAND, VOYAGE_KEY, BOOKED_SUBMETA,BERTH_TYPE HAVING COUNT(*)>20 ; DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_PPD; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_PPD AS SELECT a.*, -----PRICE PER DIEM CHECKS------------------------- --LS LB PPD CHECK DIV0((LS_LB_PPD_MED-CASE WHEN LB_PPD_CNT>=50 THEN LB_PPD_MED ELSE AVG_LS_LB_PPD_MED END),CASE WHEN LB_PPD_CNT>=50 THEN LB_PPD_MED ELSE AVG_LS_LB_PPD_MED END) LB_PPD_DIFF_PROP, DIV0((LS_UB_PPD_MED-CASE WHEN UB_PPD_CNT>=50 THEN UB_PPD_MED ELSE AVG_LS_UB_PPD_MED END),CASE WHEN UB_PPD_CNT>=50 THEN UB_PPD_MED ELSE AVG_LS_UB_PPD_MED END) UB_PPD_DIFF_PROP, CASE WHEN ABS(DIV0( (LS_LB_PPD_MED-CASE WHEN LB_PPD_CNT>=50 THEN LB_PPD_MED ELSE AVG_LS_LB_PPD_MED END), CASE WHEN LB_PPD_CNT>=50 THEN LB_PPD_MED ELSE AVG_LS_LB_PPD_MED END ))>0.3 THEN 0 ELSE 1 END LS_LB_PPD_CHECK, CASE WHEN ABS(DIV0( (LS_UB_PPD_MED-CASE WHEN UB_PPD_CNT>=50 THEN UB_PPD_MED ELSE AVG_LS_UB_PPD_MED END), CASE WHEN UB_PPD_CNT>=50 THEN UB_PPD_MED ELSE AVG_LS_UB_PPD_MED END ))>0.3 THEN 0 ELSE 1 END LS_UB_PPD_CHECK FROM ( SELECT o.*, vl.SAIL_DATE LS_SAIL_DATE, fs.BOOKED_SUBMETA, --CUR VOYAGE PPD STATS --LB ppd_lb.PPD_MED LB_PPD_MED, ppd_lb.CNT LB_PPD_CNT, --UB ppd_ub.PPD_MED UB_PPD_MED, ppd_ub.CNT UB_PPD_CNT, --LIKE SAILING PPD STATS --LB lsppd_lb.PPD_MED LS_LB_PPD_MED, lsppd_lb.CNT LS_LB_PPD_CNT, DIV0(SUM(lsppd_lb.PPD_MED * nvl(lsppd_lb.CNT,0)) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY, fs.BOOKED_SUBMETA),SUM(nvl(lsppd_lb.CNT,0)) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY, fs.BOOKED_SUBMETA)) AVG_LS_LB_PPD_MED, --UB lsppd_ub.PPD_MED LS_UB_PPD_MED, lsppd_ub.CNT LS_UB_PPD_CNT, DIV0(SUM(lsppd_ub.PPD_MED * nvl(lsppd_ub.CNT,0)) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY, fs.BOOKED_SUBMETA),SUM(nvl(lsppd_ub.CNT,0)) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY, fs.BOOKED_SUBMETA)) AVG_LS_UB_PPD_MED ------------------------------------- FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT o LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (o.BRAND=v.BRAND AND o.VOYAGE_KEY=v.VOYAGE_KEY) LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE vl ON (o.BRAND=vl.BRAND AND o.LIKE_SAILING=vl.VOYAGE_KEY) --FUTURE VOYAGE SUBMETAS (AS NOT ALL LS WILL HAVE THE SAME SUBMETAS AS FUTURE VOYAGES LEFT JOIN (SELECT DISTINCT BRAND, SHIP_CODE, CABIN_VERSION, MODEL_SUBMETA BOOKED_SUBMETA FROM RMS_DB_PRD1.RMS.UMF_LEAD_CATEGORY WHERE BRAND<>'CL') fs ON (o.BRAND=fs.BRAND AND o.SHIP_CODE=fs.SHIP_CODE AND v.CABIN_VERSION=fs.CABIN_VERSION) -------------PRICE PER DIEM DATA-------------------------------- --- FUTURE VOYAGE LB PPD LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_PPD ppd_lb ON (o.BRAND=ppd_lb.BRAND AND o.VOYAGE_KEY=ppd_lb.VOYAGE_KEY AND fs.BOOKED_SUBMETA=ppd_lb.BOOKED_SUBMETA AND ppd_lb.BERTH_TYPE='Lower') --- LIKE SAILING LB PPD LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_PPD lsppd_lb ON (o.BRAND=lsppd_lb.BRAND AND o.LIKE_SAILING=lsppd_lb.VOYAGE_KEY AND fs.BOOKED_SUBMETA=lsppd_lb.BOOKED_SUBMETA AND lsppd_lb.BERTH_TYPE='Lower') --- FUTURE VOYAGE UB PPD LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_PPD ppd_ub ON (o.BRAND=ppd_ub.BRAND AND o.VOYAGE_KEY=ppd_ub.VOYAGE_KEY AND fs.BOOKED_SUBMETA=ppd_ub.BOOKED_SUBMETA AND ppd_ub.BERTH_TYPE='Upper') --- LIKE SAILING UB PPD LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_PPD lsppd_ub ON (o.BRAND=lsppd_ub.BRAND AND o.LIKE_SAILING=lsppd_ub.VOYAGE_KEY AND fs.BOOKED_SUBMETA=lsppd_ub.BOOKED_SUBMETA AND lsppd_ub.BERTH_TYPE='Upper') WHERE o.BRAND<>'CL' --AND o.VOYAGE_KEY='1307N-20230302SFO1SFO2' AND o.SAIL_DATE>o.BATCH_DATE ORDER BY o.VOYAGE_KEY, fs.BOOKED_SUBMETA, o.LIKE_SAILING ) a ; ---CHECK IF LIKE SAILINGS HAVE THE SAME BASE MARKETS/ BM PROPORTIONS DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_BM_PROP; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_LS_BM_PROP AS SELECT a.*, a.MAX_BM_PROP-MIN_BM_PROP BM_PROP_DIFF, CASE WHEN a.MAX_BM_PROP-MIN_BM_PROP>0.3 THEN 0 ELSE 1 END LS_BM_PROP_CHECK FROM ( SELECT a.*, MIN(BM_PROP) OVER (PARTITION BY BRAND, VOYAGE_KEY, BASE_MARKET) MIN_BM_PROP, MAX(BM_PROP) OVER (PARTITION BY BRAND, VOYAGE_KEY, BASE_MARKET) MAX_BM_PROP FROM ( SELECT a.*, DIV0NULL(PAX,TOT_PAX) BM_PROP FROM ( SELECT o.BRAND, o.VOYAGE_KEY, o.LIKE_SAILING, p.BASE_MARKET, p.PAX, SUM(p.PAX) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY, o.LIKE_SAILING) TOT_PAX FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT o LEFT JOIN ( SELECT BRAND, VOYAGE_KEY, BASE_MARKET, SUM(INC_NET_NET_PAX+UNC_INC_NET_NET_PAX) PAX FROM RMS_DB_PRD1.RMS.DF_UNCONST_INC WHERE BRAND <>'CL' GROUP BY BRAND, VOYAGE_KEY, BASE_MARKET ) p ON (p.BRAND=o.BRAND AND p.VOYAGE_KEY=o.LIKE_SAILING) WHERE o.BRAND <>'CL' AND o.SAIL_DATE>o.BATCH_DATE AND o.OUTLIER_FLAG=0 --AND p.PAX>50 AND p.BASE_MARKET<>'Other' --AND o.VOYAGE_KEY='PA22240303-20240303LRMMRS' ) a ORDER BY 1,2,3 ) a ) a ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_LS_BM_PROP; DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_BM_MAP; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_BM_MAP AS WITH PRE_BM AS ( SELECT BRAND, VOYAGE_KEY, SUM(INC_NET_NET_PAX + UNC_INC_NET_NET_PAX) DMD_PRE_BM_MAP FROM RMS_DB_PRD1.RMS.df_unconst_inc WHERE BRAND<>'CL' AND SAIL_DATE>'01/01/2016' --AND VOYAGE_KEY ='W839-20180728BNEBNE' GROUP BY BRAND, VOYAGE_KEY ) -- SELECT n.*, b.DMD_PRE_BM_MAP, CASE WHEN ABS(DMD_PRE_BM_MAP - DMD_POST_BM_MAP)> 50 THEN 0 ELSE 1 END BM_MAP_DMD_DROP_CHECK FROM ( SELECT n.BRAND, n.VOYAGE_KEY, n.LIKE_SAILING, SUM(n.UNC_INC_NET_NET_PAX_LIKE) DMD_POST_BM_MAP FROM RMS_DB_PRD1.RMS.df_norm_unconst_inc n WHERE n.BRAND<>'CL' GROUP BY n.BRAND, n.VOYAGE_KEY, n.LIKE_SAILING ) n LEFT JOIN PRE_BM b ON (n.BRAND=b.BRAND AND n.LIKE_SAILING=b.VOYAGE_KEY) ; --GET THE NEGOTIATED AND NON OPT DMD FOR FUTURE VOYAGESD AND LIKE SAILINGS DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_NEG; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_NEG AS with neg_prep as ( SELECT BRAND, VOYAGE_KEY, LIKE_SAILING, SUM(dmd_amt) dmd_amt FROM ( select o.brand,o.ship_code,o.voyage_key,o.LIKE_SAILING,s.fare_type,s.berth_type,s.booked_submeta as submeta, case when nvl(t.total_weight,0)=0 then avg(s.net_net_berths) else DIV0NULL(sum(o.weight*s.net_net_berths),t.total_weight) end as dmd_amt from RMS_DB_PRD1.RMS.df_outlier_output o inner join ( select brand,voyage_key,sum(weight) as total_weight from RMS_DB_PRD1.RMS.df_outlier_output where outlier_flag=0 group by brand,voyage_key ) t on (o.brand=t.brand and o.voyage_key=t.voyage_key) inner join ( select brand,voyage,voyage_key,booked_submeta,fare_type,berth_type, sum(inc_net_net_pax) as net_net_berths from RMS_DB_PRD1.RMS.df_summary s where s.BRAND <>'CL' and s.sail_Date<=s.BATCH_DATE AND s.GUEST_TYPE='FIT' group by brand,voyage,voyage_key,booked_submeta,fare_type,berth_type ) s INNER JOIN ( SELECT BRAND, VALUE FROM RMS_DB_PRD1.RMS.RM_BRAND_PARAM WHERE BRAND <>'CL' AND PARAM_NAME LIKE 'GEN_NOOPT_FCST' ) p ON (s.BRAND=p.BRAND AND s.FARE_TYPE=p.VALUE) on (o.brand=s.brand and o.like_sailing=s.voyage_key) where o.BRAND <>'CL' and o.sail_Date>o.BATCH_DATE and o.outlier_flag=0 --AND s.BERTH_TYPE='Lower' group by o.brand,o.ship_code,o.voyage_key,o.LIKE_SAILING,s.fare_Type,s.berth_type,s.booked_submeta,t.total_weight ) GROUP BY BRAND, VOYAGE_KEY, LIKE_SAILING ) -- , neg_ovrd AS ( SELECT v.BRAND, v.SHIP_CODE, v.VOYAGE_KEY, SUBMETA, BERTH_TYPE, SUM(OVRD_VALUE) NOOPT_FCST_OVRD FROM RMS_DB_PRD1.RMS.UMF_DF_SYS_FCST_OVRD n LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (n.BRAND=v.BRAND AND n.VOYAGE_KEY=v.VOYAGE_KEY) INNER JOIN ( SELECT BRAND, VALUE FROM RMS_DB_PRD1.RMS.RM_BRAND_PARAM WHERE BRAND <>'CL' AND PARAM_NAME LIKE 'GEN_NOOPT_FCST' ) p ON (n.BRAND=p.BRAND AND n.FARE_TYPE=p.VALUE) WHERE n.BRAND <>'CL' AND v.BATCH_DATE between nvl(n.ovrd_start_date,v.BATCH_DATE) and nvl(n.ovrd_end_date,v.SAIL_DATE) GROUP BY v.BRAND, v.SHIP_CODE, v.VOYAGE_KEY, SUBMETA, BERTH_TYPE ) -- , neg_fcst AS ( SELECT BRAND, VOYAGE_KEY, SUM(no_opt_dmd) no_opt_dmd FROM ( select nvl(n.brand,o.brand) as brand, nvl(n.ship_code, o.ship_code) as ship_code, nvl(n.voyage_key,o.voyage_key) as voyage_key, nvl(n.berth_type, o.berth_type) as berth_type, nvl(n.submeta, o.submeta) as submeta, coalesce(o.NOOPT_FCST_OVRD,sum(n.dmd_amt), 0) as no_opt_dmd from RMS_DB_PRD1.RMS.DF_SYS_FCST_NO_OPT n LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (n.BRAND=v.BRAND AND n.VOYAGE_KEY=v.VOYAGE_KEY) full outer join neg_ovrd o on (n.brand=o.brand and n.voyage_key=o.voyage_key and n.berth_type=o.berth_type and n.submeta=o.submeta) where nvl(n.brand,o.brand) <>'CL' group by nvl(n.brand,o.brand) , nvl(n.ship_code, o.ship_code),nvl(n.voyage_key,o.voyage_key),nvl(n.berth_type, o.berth_type), nvl(n.submeta, o.submeta), o.NOOPT_FCST_OVRD ) GROUP BY BRAND, VOYAGE_KEY ) -- SELECT n.brand as brand, n.voyage_key as voyage_key, n.like_sailing as like_sailing, coalesce(n.dmd_amt,0) as ls_dmd_amt, coalesce(o.no_opt_dmd,0) as no_opt_dmd from neg_prep n left join neg_fcst o on (n.brand=o.brand and n.voyage_key=o.voyage_key) where n.brand <>'CL' -- AND n.VOYAGE_KEY LIKE 'I340-%' ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_NEG; --GET THE GROUP DMD FOR LIKE SAILINGS DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_GRP; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_GRP AS SELECT a.*, nvl(l.GRP_SUBMETA_FCST,0) GRP_FCST FROM ( SELECT l.BRAND, l.SHIP_CODE, l.VOYAGE_KEY, l.LIKE_SAILING,nvl(DIV0NULL(SUM(l.WEIGHT*g.INC_NET_NET_PAX),SUM(l.WEIGHT)),0) LS_GRP_BKGS FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT l LEFT JOIN RMS_DB_PRD1.RMS.DF_SUMMARY g ON (l.BRAND=g.BRAND AND l.LIKE_SAILING=g.VOYAGE_KEY) WHERE l.BRAND<>'CL' AND g.GUEST_TYPE='LGR' AND g.BERTH_TYPE='Lower' AND l.OUTLIER_FLAG=0 GROUP BY l.BRAND, l.SHIP_CODE, l.VOYAGE_KEY, l.LIKE_SAILING ) a LEFT JOIN ( SELECT BRAND, VOYAGE_KEY, SUM(GRP_SUBMETA_FCST) GRP_SUBMETA_FCST FROM RMS_DB_PRD1.RMS.DF_SYS_GRP_FCST WHERE BRAND<>'CL' --AND VOYAGE_KEY LIKE 'I340-%' --AND SUBMETA='V' GROUP BY BRAND, VOYAGE_KEY ) l ON (a.BRAND=l.BRAND AND a.VOYAGE_KEY=l.VOYAGE_KEY) ; ---GET THE LIKE SAILING SIMILARITY AND MAX SIMILARITY FOR ---SAILED VOYS ---SAILED VOYS WITHIN 5 WOYs ---SAILED PRE COVID VOYS ---SAILED PRE COVID VOYS WITHIN 5 WOYs DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_SIM; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_SIM AS SELECT 'AI' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_AI_VOY_ITIN_SIMILARITY UNION ALL SELECT 'CO' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_CO_VOY_ITIN_SIMILARITY UNION ALL SELECT 'CU' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_CU_VOY_ITIN_SIMILARITY UNION ALL SELECT 'HA' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_HA_VOY_ITIN_SIMILARITY UNION ALL SELECT 'PA' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_PA_VOY_ITIN_SIMILARITY UNION ALL SELECT 'PC' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_PC_VOY_ITIN_SIMILARITY UNION ALL SELECT 'PO' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_PO_VOY_ITIN_SIMILARITY UNION ALL SELECT 'SB' BRAND, VOYAGE_KEY, VOYAGE_KEY_LIKE, DIST FROM RMSPOA_DB_UAT1.RMS.X_SB_VOY_ITIN_SIMILARITY ; DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_MIN_SIM; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_MIN_SIM AS SELECT BRAND, VOYAGE_KEY, --MIN SAILED DIST MIN(DIST) MIN_SAILED_DIST, MIN(CASE WHEN ABS(SAIL_WOY-LS_SAIL_WOY)<=5 OR ABS((SAIL_WOY +52) -LS_SAIL_WOY)<= 5 OR ABS(SAIL_WOY - (LS_SAIL_WOY + 52))<= 5 THEN DIST ELSE NULL END) MIN_SAILED_WTHN_5WOY_DIST, MIN(CASE WHEN LS_SAIL_YEAR<2020 THEN DIST ELSE NULL END) MIN_PRECOV_DIST, MIN(CASE WHEN LS_SAIL_YEAR<2020 AND (ABS(SAIL_WOY-LS_SAIL_WOY)<=5 OR ABS((SAIL_WOY +52) -LS_SAIL_WOY)<= 5 OR ABS(SAIL_WOY - (LS_SAIL_WOY + 52))<= 5) THEN DIST ELSE NULL END) MIN_PRECOV_WTHN_5WOY_DIST FROM ( SELECT s.*, v.SAIL_DATE, vl.SAIL_DATE LS_SAIL_DATE, v.SAIL_YEAR, vl.SAIL_YEAR LS_SAIL_YEAR, v.SAIL_WOY, vl.SAIL_WOY LS_SAIL_WOY FROM RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_SIM s LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (s.BRAND=v.BRAND AND s.VOYAGE_KEY=v.VOYAGE_KEY) LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE vl ON (s.BRAND=vl.BRAND AND s.VOYAGE_KEY_LIKE=vl.VOYAGE_KEY) WHERE v.SAIL_DATE>=v.BATCH_DATE AND vl.SAIL_DATE<=v.BATCH_DATE ) GROUP BY BRAND, VOYAGE_KEY ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_DF_BM_MAP WHERE VOYAGE_KEY='AU05230430-20230430HAMHAM'; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_OCC -- --CHECK BRING IN PREVIOUS CHECKS AND LOOK AT THE LIKE SAILINGS ---LOOK AT: --LS PPD --LS OCC/CLF --LS MISSING MAPPINGS --BM MAPPING THE SAME --OPT PUBLIC DMD PCT --SUBMETA OB STRATEGY (VS TOTAL SHIP CAPACITY) --LIKE SAILING ALLOC CAP PROPORTIONS OF OVERALL SHIP VS CURRENT VOYAGE --NEGOTIATED AND GROUPS PROPORTION VS CURRENT VOYAGE --CURRENT VOYAGE OCCUPANCY VS LIKE SAILINGS AT SAME POINT DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_CHECK_DETAIL; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_CHECK_DETAIL AS SELECT a.*, --IF WE STILL HAVE LS AFTER DROPPING THE FAILING LS, THEN RECOMMEND TO REMOVE CASE WHEN LS_PASS_COUNT>0 AND LS_OVERALL_CHECK=1 THEN 'Keep' WHEN LS_PASS_COUNT>0 AND LS_OVERALL_CHECK=0 THEN 'Remove' ELSE 'All Fail' END LS_RECOMMENDATION, COALESCE(CASE WHEN LS_ALLOC_CAP_PROP_CHECK=0 THEN 'LS alloc cap prop of ship diff| ' ELSE NULL END, CASE WHEN LS_PUB_FIT_DMD_CHECK=0 THEN 'Not much public FIT dmd on LS | ' ELSE NULL END, CASE WHEN LS_FILLED_CHECK=0 THEN 'LS didnt fill | ' ELSE NULL END, --CASE WHEN SM_OB_CHECK=0 THEN 'LS sm overbooking prop v different | ' ELSE NULL END, CASE WHEN LS_LB_PPD_CHECK=0 THEN 'LS PPD very different from cur | ' ELSE NULL END, CASE WHEN LS_BM_PROP_CHECK=0 THEN 'LS market prop diff | ' ELSE NULL END, CASE WHEN LS_NOOPT_PROP_CHECK=0 THEN 'LS net or grp prop diff | ' ELSE NULL END, CASE WHEN LS_ITIN_SIM_CHECK=0 THEN 'LS itin diff | ' ELSE NULL END ) LS_REC_REASON FROM ( SELECT a.*, SUM(LS_OVERALL_CHECK) OVER (PARTITION BY BRAND, VOYAGE_KEY) LS_PASS_COUNT FROM ( SELECT a.*, --FINAL LIKE SAILING PASS OR FAIL CHECK TO RECOMMEND REMOVAL OR TO KEEP LEAST(LS_ALLOC_CAP_PROP_CHECK,LS_PUB_FIT_DMD_CHECK,LS_FILLED_CHECK,LS_LB_PPD_CHECK,LS_BM_PROP_CHECK,LS_NOOPT_PROP_CHECK,LS_ITIN_SIM_CHECK) LS_OVERALL_CHECK FROM ( SELECT DISTINCT * FROM ( SELECT o.BATCH_DATE, o.BRAND, o.VOYAGE_KEY,v.VOYAGE_STATUS, o.SHIP_CODE, o.SAIL_DATE, v.SAIL_YEAR, v.RPT_CODE, o.LIKE_SAILING, vl.SAIL_DATE LS_SAIL_DATE, vl.SHIP_CODE LS_SHIP_CODE, vl.SAIL_YEAR LS_SAIL_YEAR, o.WEIGHT, o.OVRD_FLAG, --CUR BKGS bkg.LB_BKGS, bkg.LB_PUB_FIT_BKGS, --LS BKGS ls_bkg.LB_BKGS LS_LB_BKGS, ls_bkg.LB_PUB_FIT_BKGS LS_LB_PUB_FIT_BKGS, --CUR ALLOC CAP co.LB_ALLOC_CAP, --CUR SHIP CAP co.LB_SHIP_CAP, --CUR ALLOC PROP DIV0NULL(co.LB_ALLOC_CAP,co.LB_SHIP_CAP) LB_ALLOC_PROP, --LS ALLOC CAP lo.LB_ALLOC_CAP LS_LB_ALLOC_CAP, --LS SHIP CAP lo.LB_SHIP_CAP LS_LB_SHIP_CAP, --LS ALLOC CAP PROP DIV0NULL(lo.LB_ALLOC_CAP,lo.LB_SHIP_CAP) LS_LB_ALLOC_PROP, --ALLOC CAP PROP CHECK CASE WHEN ABS(DIV0NULL(co.LB_ALLOC_CAP,co.LB_SHIP_CAP) - DIV0NULL(lo.LB_ALLOC_CAP,lo.LB_SHIP_CAP))>0.2 THEN 0 ELSE 1 END LS_ALLOC_CAP_PROP_CHECK, --LB_OCC co.LB_OCC, --LS LB OCC lo.LB_OCC LS_LB_OCC, --PUB LS DMD CHECK CASE WHEN o.BATCH_DATE>vl.SAIL_DATE THEN lo.PUB_FIT_DMD_CHECK ELSE 1 END LS_PUB_FIT_DMD_CHECK, --SAILED VOYAGE FILLED CHECK CASE WHEN o.BATCH_DATE>vl.SAIL_DATE THEN lo.LB_OCC_CHECK ELSE 1 END LS_FILLED_CHECK, --LS MAPPING CHECKS nvl(ls_bkg.SM_MAP_MISSING_CHECK,0) LS_SM_MAP_MISSING_CHECK, nvl(ls_bkg.FT_MAP_MISSING_CHECK,0) LS_FT_MAP_MISSING_CHECK, nvl(ls_bkg.RM_MAP_MISSING_CHECK,0) LS_RM_MAP_MISSING_CHECK, nvl(ls_bkg.BM_MAP_MISSING_CHECK,0) LS_BM_MAP_MISSING_CHECK, --OVERBOOKING STRATEGY CHECK nvl(ob.SM_OB_CHECK,1) SM_OB_CHECK, --LIKE SAILING PPD DEVIATION CHECKS nvl(ppd.LS_LB_PPD_CHECK,1) LS_LB_PPD_CHECK, nvl(ppd.LS_UB_PPD_CHECK,1) LS_UB_PPD_CHECK, --CHECK IF THE BASE MARKETS IN TEH LIKE SAILINGS HAVE SIMILAR ENOUGH DMD PROPORTIONS CASE WHEN ls_bm.VOYAGE_KEY IS NOT NULL THEN 0 ELSE 1 END LS_BM_PROP_CHECK, --CHECK IF ANY DEMAND IS DROPPED FROM THE LS DUE TO DIFFERENT BASE MARKETS BETWEEN CURRENT VOYAGE AND LIKE SAILINGS nvl(bm_map.BM_MAP_DMD_DROP_CHECK,1) LS_BM_MAP_DMD_DROP_CHECK, --LS OCC lsocc.OCC_DIFF LS_FIT_OCC_DIFF, nvl(lsocc.LS_FIT_OCC_DIFF_CHECK,1) LS_FIT_OCC_DIFF_CHECK, --GROUP DATA nvl(lgr.LS_GRP_BKGS,0) LS_GRP_BKGS, nvl(lgr.GRP_FCST,0) GRP_FCST, --NO OPT DATA nvl(neg.LS_DMD_AMT,0) LS_NO_OPT_BKGS, nvl(neg.NO_OPT_DMD,0) NO_OPT_FCST, --NO OPT DMD PROP DIV0NULL(nvl(lgr.GRP_FCST,0) + nvl(neg.LS_DMD_AMT,0),co.LB_SHIP_CAP) NOOPT_PROP, DIV0NULL(nvl(lgr.LS_GRP_BKGS,0) + nvl(neg.LS_DMD_AMT,0),lo.LB_SHIP_CAP) LS_NOOPT_PROP, CASE WHEN ABS(DIV0NULL(nvl(lgr.LS_GRP_BKGS,0) + nvl(neg.LS_DMD_AMT,0),lo.LB_SHIP_CAP)-DIV0NULL(nvl(lgr.GRP_FCST,0) + nvl(neg.LS_DMD_AMT,0),co.LB_SHIP_CAP))>0.2 THEN 0 ELSE 1 END LS_NOOPT_PROP_CHECK, --LIKE SAILING ITIN SIMILARITY METRICS s1.DIST LS_ITIN_SIM_DIST, MIN(CASE WHEN vl.SAIL_YEAR<=2020 THEN s1.DIST ELSE NULL END) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY) MIN_LS_PRE_COV_ITIN_DIST, MAX(CASE WHEN vl.SAIL_YEAR<=2020 THEN s1.DIST ELSE NULL END) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY) MAX_LS_PRE_COV_ITIN_DIST, --MINIMUM DISTS FOUND IN SIMILARITY WORK sm1.MIN_SAILED_DIST, sm1.MIN_SAILED_WTHN_5WOY_DIST, sm1.MIN_PRECOV_DIST, sm1.MIN_PRECOV_WTHN_5WOY_DIST, --CHECK THE RANGE OF LS SIMILARITY, IF THERE ARE LESS SIMILAR LS THEN REMOVE THEM CASE WHEN s1.DIST - MIN(CASE WHEN vl.SAIL_YEAR<=2020 THEN s1.DIST ELSE NULL END) OVER (PARTITION BY o.BRAND, o.VOYAGE_KEY) >= 0.25 THEN 0 ELSE 1 END LS_ITIN_SIM_CHECK --GET CURRENT VOYAGE AND LIKE SAILINGS FROM RMS_DB_PRD1.RMS.DF_OUTLIER_OUTPUT o --GET CURRENT VOYAGE INFO LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (o.BRAND=v.BRAND AND o.VOYAGE_KEY=v.VOYAGE_KEY) --GET CURRENT VOYAGE INFO LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE vl ON (o.BRAND=vl.BRAND AND o.LIKE_SAILING=vl.VOYAGE_KEY) --------OCCUPANCY AND BOOKING DATA------------------------------ --CURRENT VOYAGE OCC DATA LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_VOY_OCC co ON (v.BRAND=co.BRAND AND v.VOYAGE_KEY=co.VOYAGE_KEY) --LIKE SAILING OCC DATA LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_VOY_OCC lo ON (vl.BRAND=lo.BRAND AND vl.VOYAGE_KEY=lo.VOYAGE_KEY) --CURRENT VOYAGE BOOKINGS (ALSO CHECKING IF ALL BOOKINGS HAVE SUBMETAS AND FARE TYPES) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_MAPPINGS_MISSING_BKGS bkg ON (v.BRAND=bkg.BRAND AND v.VOYAGE_KEY=bkg.VOYAGE_KEY) --LIKE SAILINGS BOOKINGS (ALSO CHECKING IF ALL BOOKINGS HAVE SUBMETAS AND FARE TYPES) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_MAPPINGS_MISSING_BKGS ls_bkg ON (o.BRAND=ls_bkg.BRAND AND o.LIKE_SAILING=ls_bkg.VOYAGE_KEY) LEFT JOIN (SELECT BRAND, VOYAGE_KEY, LIKE_SAILING, MIN(SM_OB_CHECK) SM_OB_CHECK FROM RMSPOA_DB_UAT1.RMS.X_HC_LS_SM_OB_STRAT GROUP BY BRAND, VOYAGE_KEY, LIKE_SAILING) ob ON (o.BRAND=ob.BRAND AND o.VOYAGE_KEY=ob.VOYAGE_KEY AND o.LIKE_SAILING=ob.LIKE_SAILING) LEFT JOIN (SELECT BRAND, VOYAGE_KEY, LIKE_SAILING, MIN(LS_LB_PPD_CHECK) LS_LB_PPD_CHECK, MIN(LS_UB_PPD_CHECK) LS_UB_PPD_CHECK FROM RMSPOA_DB_UAT1.RMS.X_HC_LS_PPD GROUP BY BRAND, VOYAGE_KEY, LIKE_SAILING) ppd ON (o.BRAND=ppd.BRAND AND o.VOYAGE_KEY=ppd.VOYAGE_KEY AND o.LIKE_SAILING=ppd.LIKE_SAILING) LEFT JOIN (SELECT DISTINCT BRAND, VOYAGE_KEY FROM RMSPOA_DB_UAT1.RMS.X_HC_LS_BM_PROP WHERE LS_BM_PROP_CHECK = 0) ls_bm ON (o.BRAND=ls_bm.BRAND AND o.VOYAGE_KEY=ls_bm.VOYAGE_KEY) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_DF_BM_MAP bm_map ON (o.BRAND=bm_map.BRAND AND o.VOYAGE_KEY=bm_map.VOYAGE_KEY AND o.LIKE_SAILING=bm_map.LIKE_SAILING) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_GRP lgr ON (o.BRAND=lgr.BRAND AND o.VOYAGE_KEY=lgr.VOYAGE_KEY AND o.LIKE_SAILING=lgr.LIKE_SAILING) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_NEG neg ON (o.BRAND=neg.BRAND AND o.VOYAGE_KEY=neg.VOYAGE_KEY AND o.LIKE_SAILING=neg.LIKE_SAILING) LEFT JOIN (SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_VOY_CUR_FIT_LS_OCC WHERE CUR_WP=WP) lsocc ON (o.BRAND=lsocc.BRAND AND o.VOYAGE_KEY=lsocc.VOYAGE_KEY AND o.LIKE_SAILING=lsocc.LIKE_SAILING) --LIKE SAILING SIMILARITY RESULTS LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_SIM s1 ON (o.BRAND=s1.BRAND AND o.VOYAGE_KEY=s1.VOYAGE_KEY AND o.LIKE_SAILING=s1.VOYAGE_KEY_LIKE) LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_MIN_SIM sm1 ON (o.BRAND=sm1.BRAND AND o.VOYAGE_KEY=sm1.VOYAGE_KEY) WHERE o.BRAND <>'CL' --AND o.VOYAGE_KEY like 'V110-20210429SOU1HAM1' AND o.OUTLIER_FLAG=0 AND o.SAIL_DATE>o.BATCH_DATE --AND d.PAX_BKGS is not null AND v.SAIL_DATE>v.BATCH_DATE AND v.DISPLAY_IND='Y' AND v.VOYAGE_STATUS in ('A','B') AND v.BRAND||v.VOYAGE not in (SELECT BRAND||VOYAGE FROM RMS_DB_PRD1.RMS.UMF_EXCLUDED_HIST_VOYAGE WHERE BRAND<>'CL') --AND v.VOYAGE='X311' ) a ORDER BY 1,2,3,4,5 ) a ) a ) a ; --SELECT * FROM RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_CHECK_DETAIL WHERE BRAND='PA'; ------------------------------------------- ------------------------------------------- --TO ADD: --SIMILARITY CHECK --SAIL ATTR CHECK --SAIL WOY CHECK --GRP PACING CHECK --NO OPT PACING CHECK ----PASS SOME LIKE SAILING CHECKS THAT ARE OVERRIDES - THESE HAVE BEEN LOOKED AT BY THE ANALYST DROP TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_CHECK; CREATE TABLE RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_CHECK AS SELECT a.BRAND, a.SHIP_CODE, a.TRADE_NAME, a.SUBTRADE_NAME, a.VOYAGE_KEY, b.RPT_CODE, a.VOYAGE_STATUS, a.CABIN_VERSION, a.DURATION, a.SAIL_DATE, a.EMBARK_PORT, a. DEBARK_PORT, a.SAILING_TRIP_TYPE, a.HOLIDAY_IND, a.SPECIAL_EVENT_FLAG, a.SHIP_CLASS, a.FIN_YR, a.FIN_QTR, a.DURATION_GRP, a.LS_COUNT, a.LS_LIST, --AGGREGATED CHECKS CASE WHEN LS_COUNT=0 THEN 0 ELSE 1 END LS_COUNT_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_ALLOC_CAP_PROP_CHECK END),SUM(b.WEIGHT)),1) LS_ALLOC_CAP_PROP_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_PUB_FIT_DMD_CHECK END),SUM(b.WEIGHT)),1) LS_PUB_FIT_DMD_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_FILLED_CHECK END),SUM(b.WEIGHT)),1) LS_FILLED_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * LS_SM_MAP_MISSING_CHECK),SUM(b.WEIGHT)),1) LS_SM_MAP_MISSING_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * LS_FT_MAP_MISSING_CHECK),SUM(b.WEIGHT)),1) LS_FT_MAP_MISSING_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * LS_RM_MAP_MISSING_CHECK),SUM(b.WEIGHT)),1) LS_RM_MAP_MISSING_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * LS_BM_MAP_MISSING_CHECK),SUM(b.WEIGHT)),1) LS_BM_MAP_MISSING_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE SM_OB_CHECK END),SUM(b.WEIGHT)),1) SM_OB_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_LB_PPD_CHECK END),SUM(b.WEIGHT)),1) LS_LB_PPD_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_UB_PPD_CHECK END),SUM(b.WEIGHT)),1) LS_UB_PPD_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_BM_PROP_CHECK END),SUM(b.WEIGHT)),1) LS_BM_PROP_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_BM_MAP_DMD_DROP_CHECK END),SUM(b.WEIGHT)),1) LS_BM_MAP_DMD_DROP_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_FIT_OCC_DIFF_CHECK END),SUM(b.WEIGHT)),1) LS_FIT_OCC_DIFF_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_NOOPT_PROP_CHECK END),SUM(b.WEIGHT)),1) LS_NOOPT_PROP_CHECK, nvl(DIV0NULL(SUM(b.WEIGHT * CASE WHEN OVRD_FLAG='Y' THEN 1 ELSE LS_ITIN_SIM_CHECK END),SUM(b.WEIGHT)),1) LS_ITIN_SIM_CHECK --nvl(DIV0NULL(SUM(b.WEIGHT * ),SUM(b.WEIGHT)),1) , FROM RMSPOA_DB_UAT1.RMS.X_HC_LS_COUNT a LEFT JOIN RMSPOA_DB_UAT1.RMS.X_HC_DF_LS_CHECK_DETAIL b ON (a.BRAND=b.BRAND AND a.VOYAGE_KEY=b.VOYAGE_KEY) GROUP BY a.BRAND, a.SHIP_CODE, a.TRADE_NAME, a.SUBTRADE_NAME, a.VOYAGE_KEY, b.RPT_CODE, a.VOYAGE_STATUS, a.CABIN_VERSION, a.DURATION, a.SAIL_DATE, a.EMBARK_PORT, a. DEBARK_PORT, a.SAILING_TRIP_TYPE, a.HOLIDAY_IND, a.SPECIAL_EVENT_FLAG, a.SHIP_CLASS, a.FIN_YR, a.FIN_QTR, a.DURATION_GRP, a.LS_COUNT, a.LS_LIST ; ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ---------------- ---MRM CHECKS--- ---------------- -- --ELASTICITIES CHECKS DROP TABLE X_HC_MRM_ELAST; CREATE TABLE X_HC_MRM_ELAST AS --elasticity_jumps_tis_fcst SELECT i.brand, i.voyage_key, ROUND (MAX (ELAST_JUMP), 4) AS MAX_ELAST_JUMP, MIN(ELAST_JUMP_CHECK) AS MAX_ELAST_JUMP_CHECK, MAX(CASE WHEN i.inc_fcst >= 30 THEN elasticity ELSE NULL END) MIN_ELAST, MIN(SMALL_ELAST_1_CHECK) AS SMALL_ELAST_1_CHECK, MIN(SMALL_ELAST_1_5_CHECK) AS SMALL_ELAST_1_5_CHECK, MIN(SMALL_ELAST_2_CHECK) AS SMALL_ELAST_2_CHECK, DIV0NULL(SUM(elasticity * nvl(inc_fcst,0.1)),SUM(inc_fcst)) VOY_AVG_ELAST, CASE WHEN DIV0NULL(SUM(elasticity * nvl(inc_fcst,0.1)),SUM(inc_fcst)) > -1.5 THEN 0 ELSE 1 END VOY_AVG_ELAST_1_5_CHECK, CASE WHEN DIV0NULL(SUM(elasticity * nvl(inc_fcst,0.1)),SUM(inc_fcst)) > -1.8 THEN 0 ELSE 1 END VOY_AVG_ELAST_1_8_CHECK, CASE WHEN DIV0NULL(SUM(elasticity * nvl(inc_fcst,0.1)),SUM(inc_fcst)) > -2 THEN 0 ELSE 1 END VOY_AVG_ELAST_2_CHECK FROM ( SELECT i.brand, i.voyage_key, i.product_type, i.product, i.submeta, i.fare_type, i.base_market, i.time_interval_id, CASE WHEN i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN ABS (i.elasticity - i.next_ti_elas) ELSE NULL END AS ELAST_JUMP, i.elasticity, inc_fcst, i.next_ti_inc_fcst, --ELAST JUMP CHECK CASE WHEN ABS (i.elasticity - i.next_ti_elas) > 1.5 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END ELAST_JUMP_CHECK, --MIN ELAST CHECK CASE WHEN i.elasticity > (-1) AND i.inc_fcst >= 30 THEN 0 ELSE 1 END SMALL_ELAST_1_CHECK, CASE WHEN i.elasticity > (-1.5) AND i.inc_fcst >= 30 THEN 0 ELSE 1 END SMALL_ELAST_1_5_CHECK, CASE WHEN i.elasticity > (-2) AND i.inc_fcst >= 30 THEN 0 ELSE 1 END SMALL_ELAST_2_CHECK FROM ( SELECT i.brand, i.voyage_key, i.product_type, i.product, i.submeta, i.fare_type, i.base_market, i.time_interval_id, i.elasticity, i.inc_fcst, LEAD (time_interval_id, 1, NULL) OVER (PARTITION BY brand,voyage_key, fare_type, base_market, submeta, product_type, product ORDER BY time_interval_id ASC) next_ti, LEAD (elasticity, 1, NULL) OVER (PARTITION BY brand, voyage_key, fare_type, base_market, submeta, product_type, product ORDER BY time_interval_id ASC) next_ti_elas, LEAD (inc_fcst, 1, NULL) OVER (PARTITION BY brand, voyage_key, fare_type, base_market, submeta, product_type, product ORDER BY time_interval_id ASC) next_ti_inc_fcst FROM RMS_DB_PRD1.RMS.opt_model_input i WHERE BRAND not in ('CL') AND cur_avail_optimal_price IS NOT NULL --AND elasticity IS NOT NULL --AND berth_type = 'Lower' AND base_market != 'Other' AND OPT_FLAG=1 AND INPUT_IND='Y' ) i ) i GROUP BY i.brand, i.voyage_key ; --SELECT * FROM X_HC_MRM_ELAST; --SELECT * FROM UMF_REF_PRICE_OVRD; --SELECT * FROM RMS_DB_PRD1.RMS.UMF_REF_PRICE_MAP; /* SELECT BRAND, AVG(MAX_ELAST_JUMP) AVG_MAX_ELAST_JUMP, AVG(MAX_ELAST_JUMP_CHECK) AS MAX_ELAST_JUMP_CHECK, AVG(SMALL_ELAST_1_CHECK) AS SMALL_ELAST_1_CHECK, AVG(SMALL_ELAST_1_5_CHECK) AS SMALL_ELAST_1_5_CHECK, AVG(SMALL_ELAST_2_CHECK) AS SMALL_ELAST_2_CHECK, AVG(VOY_AVG_ELAST_1_5_CHECK) AS VOY_AVG_ELAST_1_5_CHECK, AVG(VOY_AVG_ELAST_1_8_CHECK) AS VOY_AVG_ELAST_1_8_CHECK, AVG(VOY_AVG_ELAST_2_CHECK) AS VOY_AVG_ELAST_2_CHECK, AVG(DMD_MSG_ELAST_CHECK) AS DMD_MSG_ELAST_CHECK FROM X_HC_MRM_ELAST --WHERE BRAND='PO' GROUP BY BRAND; */ DROP TABLE X_HC_MRM_REF_PRICE; CREATE TABLE X_HC_MRM_REF_PRICE AS SELECT i.brand, i.voyage_key, MAX(REF_JUMP) MAX_REF_JUMP, MIN(REF_JUMP_40_CHECK) AS REF_JUMP_40_CHECK, MIN(REF_JUMP_60_CHECK) AS REF_JUMP_60_CHECK, MIN(REF_JUMP_100_CHECK) AS REF_JUMP_100_CHECK, MIN(REF_JUMP_150_CHECK) AS REF_JUMP_150_CHECK, MIN(REF_JUMP_20PCT_CHECK) AS REF_JUMP_20PCT_CHECK, MIN(REF_JUMP_40PCT_CHECK) AS REF_JUMP_40PCT_CHECK, MIN(REF_JUMP_60PCT_CHECK) AS REF_JUMP_60PCT_CHECK, ROUND(DIV0NULL(SUM(REF_PRICE_PPD * nvl(inc_fcst,0.1)),SUM(inc_fcst)),0) VOY_AVG_REF, -- SUM(DMD_MISSING_REF) DMD_MSG_REF, CASE WHEN SUM(DMD_MISSING_REF) > 15 THEN 0 ELSE 1 END DMD_MSG_REF_CHECK FROM ( SELECT i.brand, i.voyage_key, i.product_type, i.product, i.submeta, i.fare_type, i.base_market, i.time_interval_id, i.berth_type, CASE WHEN i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN ABS (i.REF_PRICE_PPD - i.NEXT_TI_REF_PPD) ELSE NULL END AS REF_JUMP, i.REF_PRICE_PPD, inc_fcst, i.next_ti_inc_fcst, --REF JUMP AMT CHECK CASE WHEN ABS (i.REF_PRICE_PPD - i.NEXT_TI_REF_PPD) > 40 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_40_CHECK, CASE WHEN ABS (i.REF_PRICE_PPD - i.NEXT_TI_REF_PPD) > 60 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_60_CHECK, CASE WHEN ABS (i.REF_PRICE_PPD - i.NEXT_TI_REF_PPD) > 100 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_100_CHECK, CASE WHEN ABS (i.REF_PRICE_PPD - i.NEXT_TI_REF_PPD) > 150 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_150_CHECK, --REF JUMP PCT CHECK CASE WHEN ABS (i.NEXT_TI_REF_PPD / i.REF_PRICE_PPD) NOT BETWEEN 0.8 AND 1.2 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_20PCT_CHECK, CASE WHEN ABS (i.NEXT_TI_REF_PPD / i.REF_PRICE_PPD) NOT BETWEEN 0.6 AND 1.4 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_40PCT_CHECK, CASE WHEN ABS (i.NEXT_TI_REF_PPD / i.REF_PRICE_PPD) NOT BETWEEN 0.4 AND 1.6 AND i.inc_fcst >= 30 AND i.next_ti_inc_fcst >= 30 THEN 0 ELSE 1 END REF_JUMP_60PCT_CHECK, --MISSING REFS CASE WHEN REF_PRICE_PPD IS NULL THEN inc_fcst ELSE 0 END DMD_MISSING_REF FROM ( SELECT i.brand, i.voyage_key, i.product_type, i.product, i.submeta, i.fare_type, i.base_market, i.time_interval_id, i.berth_type, (i.AVG_CALIB_REF_PRICE/v.DURATION) REF_PRICE_PPD, i.inc_fcst, LEAD (i.time_interval_id, 1, NULL) OVER (PARTITION BY i.brand,i.voyage_key, i.fare_type, i.base_market, i.submeta, i.product_type, i.product, i.berth_type ORDER BY i.time_interval_id ASC) next_ti, LEAD (i.AVG_CALIB_REF_PRICE/v.DURATION, 1, NULL) OVER (PARTITION BY i.brand, i.voyage_key, i.fare_type, i.base_market, i.submeta, i.product_type, i.product, i.berth_type ORDER BY i.time_interval_id ASC) NEXT_TI_REF_PPD, LEAD (i.inc_fcst, 1, NULL) OVER (PARTITION BY i.brand, i.voyage_key, i.fare_type, i.base_market, i.submeta, i.product_type, i.product, i.berth_type ORDER BY i.time_interval_id ASC) next_ti_inc_fcst FROM RMS_DB_PRD1.RMS.opt_model_input i LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (i.BRAND=v.BRAND AND i.VOYAGE_KEY=v.VOYAGE_KEY) WHERE i.BRAND not in ('CL') AND i.cur_avail_optimal_price IS NOT NULL --AND elasticity IS NOT NULL --AND berth_type = 'Lower' AND i.base_market != 'Other' AND i.OPT_FLAG=1 AND i.INPUT_IND='Y' ) i ) i GROUP BY i.brand, i.voyage_key ; --MISSING REF PRICE OR ELASTICITY --GET THE CODE THAT GENERATES OPT_IND and INPUT_IND --ANY GRAIN NOT IN HERE AND MISSING AN ELASTICITY OR REFERENCE PRICE IS A PROBLEM DROP TABLE X_HC_MRM_ELAST_MISSING; CREATE TABLE X_HC_MRM_ELAST_MISSING AS WITH NO_OPT AS ( SELECT i.brand, i.ship_code, i.voyage_key, i.fare_type, i.submeta, i.berth_type, i.base_market, i.product_type, i.product, min(dno.input_ind) as input_ind from RMS_DB_PRD1.RMS.opt_model_input_product_type i inner join RMS_DB_PRD1.RMS.v_umf_do_not_opt_seg dno on (i.brand = dno.brand and i.ship_code = nvl(dno.ship_code, i.ship_code) and i.voyage_key = nvl(dno.voyage_key, i.voyage_key) and i.fare_type = nvl(dno.fare_type, i.fare_type) and i.submeta = nvl(dno.submeta, i.submeta) and i.berth_type = nvl(dno.berth_type, i.berth_type) and i.base_market = nvl(dno.base_market, i.base_market) and i.product_type = nvl(dno.product_type, i.product_type) and i.product = nvl(dno.product, i.product)) where i.brand <> 'CL' group by i.brand, i.ship_code, i.voyage_key, i.fare_type, i.submeta, i.berth_type, i.base_market, i.product_type, i.product ) --, OPT AS ( SELECT BRAND, VOYAGE_KEY, MIN(CASE WHEN i.CUR_AVAIL_OPTIMAL_PRICE IS NULL THEN 0 ELSE 1 END) REF_PRICE, MIN(CASE WHEN i.ELASTICITY IS NULL THEN 0 ELSE 1 END) ELAST FROM RMS_DB_PRD1.RMS.opt_model_input i WHERE NOT EXISTS ( SELECT 1 FROM NO_OPT n WHERE i.BRAND = n.BRAND and i.VOYAGE_KEY=n.VOYAGE_KEY AND i.BASE_MARKET=n.BASE_MARKET AND i.PRODUCT_TYPE=n.PRODUCT_TYPE AND i.PRODUCT=n.PRODUCT AND i.SUBMETA=n.SUBMETA AND i.FARE_TYPE=n.FARE_TYPE AND i.BERTH_TYPE=n.BERTH_TYPE ) AND i.BRAND<>'CL' AND (i.CUR_AVAIL_OPTIMAL_PRICE IS NULL OR i.ELASTICITY IS NULL) AND i.CUR_NET_FARE IS NOT NULL GROUP BY BRAND, VOYAGE_KEY --AND i.VOYAGE_KEY='J501-20250308HKG2SOU2' ; --SELECT * FROM rms.OPT_MODEL_OUTPUT@rmsprd; DROP TABLE X_HC_REF_PRC_WP; CREATE TABLE X_HC_REF_PRC_WP AS SELECT o.BRAND, o.VOYAGE_KEY,floor((next_day(sail_date,'Mon')-next_day(o.BATCH_DATE,'Mon'))/7) CUR_WP,o.BASE_MARKET, o.PRODUCT_TYPE, o.SUBMETA,t.SUBMETA_GRP,o.FARE_TYPE, o.BERTH_TYPE, w.WP,o.TIME_INTERVAL_ID, o.CUR_TIME_INTERVAL_ID, o.CUR_AVAIL_OPT_PRICE, o.AVG_CALIB_REF_PRICE REF_PRICE FROM RMS_DB_PRD1.RMS.OPT_MODEL_OUTPUT o LEFT JOIN RMS_DB_PRD1.RMS.RM_TIME_INTERVAL t ON (o.BRAND=t.BRAND AND o.VOYAGE_KEY=t.VOYAGE_KEY AND o.BASE_MARKET=t.BASE_MARKET AND o.SUBMETA=t.SUBMETA AND o.TIME_INTERVAL_ID=t.TIME_INTERVAL_ID) LEFT JOIN RMS_DB_PRD1.RMS.DIM_WP w ON (w.WP<=t.WP_UB AND w.WP>=t.WP_LB AND w.WP<=floor((next_day(sail_date,'Mon')-next_day(o.BATCH_DATE,'Mon'))/7)) WHERE o.BRAND <>'CL' --AND o.VOYAGE_KEY LIKE 'V410-%' --AND BERTH_TYPE='Lower' --ORDER BY 1,2,3,4,5,6,7,8,9 DESC ; DROP TABLE X_HC_REF_PRC_INV; CREATE TABLE X_HC_REF_PRC_INV AS WITH SUBMETA_INVERSIONS AS ( SELECT DISTINCT BRAND, VOYAGE_KEY, BASE_MARKET, PRODUCT_TYPE, SUBMETA, FARE_TYPE, BERTH_TYPE, TIME_INTERVAL_ID, REF_PRICE, ABOVE_SM_REF_FARE, BELOW_SM_REF_FARE, CASE WHEN div0((REF_PRICE-ABOVE_SM_REF_FARE),ABOVE_SM_REF_FARE)>0.01 THEN 0 ELSE 1 END REF_PRC_SM_INV_CHECK, CASE WHEN div0((REF_PRICE-ABOVE_SM_REF_FARE),ABOVE_SM_REF_FARE)>0.05 THEN 0 ELSE 1 END REF_PRC_SM_INV_5PCT_CHECK, CASE WHEN div0((REF_PRICE-ABOVE_SM_REF_FARE),ABOVE_SM_REF_FARE)>0.1 THEN 0 ELSE 1 END REF_PRC_SM_INV_10PCT_CHECK, CASE WHEN div0((REF_PRICE-ABOVE_SM_REF_FARE),ABOVE_SM_REF_FARE)>0.15 THEN 0 ELSE 1 END REF_PRC_SM_INV_15PCT_CHECK FROM ( SELECT a.*, LAG(REF_PRICE) OVER (PARTITION BY BRAND, VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE,FARE_TYPE, BERTH_TYPE, WP ORDER BY SUBMETA_ORDER) ABOVE_SM_REF_FARE, LEAD(REF_PRICE) OVER (PARTITION BY BRAND, VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE,FARE_TYPE, BERTH_TYPE, WP ORDER BY SUBMETA_ORDER) BELOW_SM_REF_FARE FROM ( SELECT p.BRAND, p.VOYAGE_KEY,p.BASE_MARKET, p.PRODUCT_TYPE,p.FARE_TYPE, p.SUBMETA, s.SUBMETA_ORDER, p.BERTH_TYPE,p.TIME_INTERVAL_ID, p.WP, p.REF_PRICE FROM X_HC_REF_PRC_WP p LEFT JOIN RMS_DB_PRD1.RMS.DIM_SUBMETA s ON (p.BRAND=s.BRAND AND p.SUBMETA=s.RAW_SUBMETA) ) a ) a WHERE div0((REF_PRICE-ABOVE_SM_REF_FARE),ABOVE_SM_REF_FARE)>0.01 AND REF_PRICE<89999 ) -- , FARE_TYPE_INVERSIONS AS ( SELECT DISTINCT BRAND, VOYAGE_KEY, BASE_MARKET, PRODUCT_TYPE, SUBMETA, FARE_TYPE, BERTH_TYPE, TIME_INTERVAL_ID, REF_PRICE, ABOVE_FT_REF_FARE, BELOW_FT_REF_FARE, CASE WHEN div0((REF_PRICE-ABOVE_FT_REF_FARE),ABOVE_FT_REF_FARE)>0.01 THEN 0 ELSE 1 END REF_PRC_FT_INV_CHECK, CASE WHEN div0((REF_PRICE-ABOVE_FT_REF_FARE),ABOVE_FT_REF_FARE)>0.05 THEN 0 ELSE 1 END REF_PRC_FT_INV_5PCT_CHECK, CASE WHEN div0((REF_PRICE-ABOVE_FT_REF_FARE),ABOVE_FT_REF_FARE)>0.1 THEN 0 ELSE 1 END REF_PRC_FT_INV_10PCT_CHECK, CASE WHEN div0((REF_PRICE-ABOVE_FT_REF_FARE),ABOVE_FT_REF_FARE)>0.15 THEN 0 ELSE 1 END REF_PRC_FT_INV_15PCT_CHECK FROM ( SELECT a.*, LAG(REF_PRICE) OVER (PARTITION BY BRAND, VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE,BERTH_TYPE,SUBMETA,WP ORDER BY FARE_TYPE_ORDER) ABOVE_FT_REF_FARE, LEAD(REF_PRICE) OVER (PARTITION BY BRAND, VOYAGE_KEY,BASE_MARKET, PRODUCT_TYPE,BERTH_TYPE,SUBMETA,WP ORDER BY FARE_TYPE_ORDER) BELOW_FT_REF_FARE FROM ( SELECT p.BRAND, p.VOYAGE_KEY,p.BASE_MARKET, p.PRODUCT_TYPE,p.FARE_TYPE, CASE WHEN FARE_TYPE='Standard' THEN 1 ELSE 2 END FARE_TYPE_ORDER, p.SUBMETA, p.BERTH_TYPE, p.WP, p.TIME_INTERVAL_ID, p.REF_PRICE FROM X_HC_REF_PRC_WP p ) a --WHERE NOT (BASE_MARKET='United Kingdom' AND SUBMETA='S') ) a WHERE div0((REF_PRICE-ABOVE_FT_REF_FARE),ABOVE_FT_REF_FARE)>0.01 AND REF_PRICE<89999 ) -- SELECT DISTINCT p.BRAND, p.VOYAGE_KEY, REF_PRC_SM_INV_CHECK, REF_PRC_SM_INV_5PCT_CHECK, REF_PRC_SM_INV_10PCT_CHECK, REF_PRC_SM_INV_15PCT_CHECK, -- REF_PRC_FT_INV_CHECK, REF_PRC_FT_INV_5PCT_CHECK, REF_PRC_FT_INV_10PCT_CHECK, REF_PRC_FT_INV_15PCT_CHECK, CASE WHEN (s.BRAND IS NOT NULL AND f.BRAND IS NOT NULL) THEN 'SM FT Inv '||' '||p.BASE_MARKET||' '||p.SUBMETA||' TI '||p.TIME_INTERVAL_ID WHEN s.BRAND IS NOT NULL THEN 'SM Inv '||' '||p.BASE_MARKET||' '||p.SUBMETA||' TI '||p.TIME_INTERVAL_ID WHEN f.BRAND IS NOT NULL THEN 'FT Inv '||' '||p.BASE_MARKET||' '||p.SUBMETA||' TI '||p.TIME_INTERVAL_ID END REASON FROM X_HC_REF_PRC_WP p LEFT JOIN SUBMETA_INVERSIONS s ON (p.BRAND=s.BRAND AND p.VOYAGE_KEY=s.VOYAGE_KEY AND p.BASE_MARKET=s.BASE_MARKET AND p.PRODUCT_TYPE=s.PRODUCT_TYPE AND p.SUBMETA=s.SUBMETA AND p.BERTH_TYPE=s.BERTH_TYPE AND p.TIME_INTERVAL_ID=s.TIME_INTERVAL_ID) LEFT JOIN FARE_TYPE_INVERSIONS f ON (p.BRAND=f.BRAND AND p.VOYAGE_KEY=f.VOYAGE_KEY AND p.BASE_MARKET=f.BASE_MARKET AND p.PRODUCT_TYPE=f.PRODUCT_TYPE AND p.SUBMETA=f.SUBMETA AND p.BERTH_TYPE=s.BERTH_TYPE AND p.TIME_INTERVAL_ID=s.TIME_INTERVAL_ID) --WHERE s.BRAND is not null OR f.BRAND is not NULL --) ; --SELECT * FROM X_HC_REF_PRC_INV WHERE BRAND='PA' AND REASON IS NOT NULL; /* SELECT BRAND, AVG(MAX_REF_JUMP) AVG_MAX_REF_JUMP, AVG(REF_JUMP_40_CHECK) AS REF_JUMP_40_CHECK, AVG(REF_JUMP_60_CHECK) AS REF_JUMP_60_CHECK, AVG(REF_JUMP_20PCT_CHECK) AS REF_JUMP_20PCT_CHECK, AVG(REF_JUMP_40PCT_CHECK) AS REF_JUMP_40PCT_CHECK, AVG(REF_JUMP_60PCT_CHECK) AS REF_JUMP_60PCT_CHECK, AVG(REF_JUMP_100_CHECK) AS REF_JUMP_100_CHECK, AVG(REF_JUMP_150_CHECK) AS REF_JUMP_150_CHECK FROM X_HC_MRM_REF_PRICE --WHERE BRAND='PO' GROUP BY BRAND ; */ DROP TABLE X_HC_MRM_CHECKS; CREATE TABLE X_HC_MRM_CHECKS AS SELECT e.*, r.MAX_REF_JUMP, r.REF_JUMP_20PCT_CHECK, r.REF_JUMP_40PCT_CHECK, r.REF_JUMP_60PCT_CHECK, -- nvl(REF_PRC_SM_INV_CHECK,1) REF_PRC_SM_INV_CHECK, nvl(REF_PRC_SM_INV_5PCT_CHECK,1) REF_PRC_SM_INV_5PCT_CHECK, nvl(REF_PRC_SM_INV_10PCT_CHECK,1) REF_PRC_SM_INV_10PCT_CHECK, nvl(REF_PRC_SM_INV_15PCT_CHECK,1) REF_PRC_SM_INV_15PCT_CHECK, -- nvl(REF_PRC_FT_INV_CHECK,1) REF_PRC_FT_INV_CHECK, nvl(REF_PRC_FT_INV_5PCT_CHECK,1) REF_PRC_FT_INV_5PCT_CHECK, nvl(REF_PRC_FT_INV_10PCT_CHECK,1) REF_PRC_FT_INV_10PCT_CHECK, nvl(REF_PRC_FT_INV_15PCT_CHECK,1) REF_PRC_FT_INV_15PCT_CHECK, -- nvl(miss.REF_PRICE,1) REF_PRICE_MISSING_CHECK, nvl(miss.ELAST,1) ELAST_PRICE_MISSING_CHECK FROM X_HC_MRM_ELAST e LEFT JOIN X_HC_MRM_REF_PRICE r ON (e.BRAND=r.BRAND AND e.VOYAGE_KEY=r.VOYAGE_KEY) LEFT JOIN RMS_DB_PRD1.RMS.MV_RM_VOYAGE v ON (e.BRAND=v.BRAND AND e.VOYAGE_KEY=v.VOYAGE_KEY) LEFT JOIN ( SELECT BRAND, VOYAGE_KEY, MIN(REF_PRC_SM_INV_CHECK) REF_PRC_SM_INV_CHECK, MIN(REF_PRC_SM_INV_5PCT_CHECK) REF_PRC_SM_INV_5PCT_CHECK, MIN(REF_PRC_SM_INV_10PCT_CHECK) REF_PRC_SM_INV_10PCT_CHECK, MIN(REF_PRC_SM_INV_15PCT_CHECK) REF_PRC_SM_INV_15PCT_CHECK, -- MIN(REF_PRC_FT_INV_CHECK) REF_PRC_FT_INV_CHECK, MIN(REF_PRC_FT_INV_5PCT_CHECK) REF_PRC_FT_INV_5PCT_CHECK, MIN(REF_PRC_FT_INV_10PCT_CHECK) REF_PRC_FT_INV_10PCT_CHECK, MIN(REF_PRC_FT_INV_15PCT_CHECK) REF_PRC_FT_INV_15PCT_CHECK FROM X_HC_REF_PRC_INV WHERE REASON IS NOT NULL GROUP BY BRAND, VOYAGE_KEY ) rinv ON (e.BRAND=rinv.BRAND AND e.VOYAGE_KEY=rinv.VOYAGE_KEY) LEFT JOIN X_HC_MRM_ELAST_MISSING miss ON (e.BRAND=miss.BRAND AND e.VOYAGE_KEY=miss.VOYAGE_KEY) WHERE e.BRAND<>'CL' AND v.SAIL_DATE>v.BATCH_DATE AND v.DISPLAY_IND='Y' AND v.VOYAGE_STATUS in ('A','B') AND v.BRAND||v.VOYAGE not in (SELECT BRAND||VOYAGE FROM RMS_DB_PRD1.RMS.UMF_EXCLUDED_HIST_VOYAGE WHERE BRAND<>'CL') ;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear