--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')
;
Telegram: DB developers channel