May 09, 2013, 12:11 AM
kumud.vaishyou can have a look on code
-*
-* File: advbkrpt.fex
-*
-* ADVANCE BOOKINGS
-* Author: Ken Hazel 05/25/2006
-*
-* This report provides a summary of booking activity by market and fare class for a specified
-* period in the future. You can compare the current capture date with a previous capture
-* date or choose to summarize the report on a period basis. This report can help you
-* see how many markets are progressing for future periods in terms of bookings compared to
-* authorization levels.
-*
-* --- Report Parameters ---
-* Start_Date = Starting 'Current' Departure Date Range
-* End_Date = Ending 'Current' Departure Date Range
-* &Depart_No = Departure Number
-* &City_Pair = City Pair
-* &Bus_Unit = Business Unit
-* &Analyst = Analyst
-* &DOW = Day of Week (1-7 -See WEEKDAY comment below)
-* &Resource = Compartment/Resource
-* &PostDepData = 0=Non Departed 1=Post Departure 2=Both
-* &ShowPrevDates = 0=No Previous Dates 1=Previous Dates
-* &GetClsData = 0=Compartment 1=Compartment/Class Data
-* &LegSeg_Type = Leg or Segment
-* ----------------------------------------
-* --- DEFAULTS SECTION -------------------
-SET ECHO=ALL;
-RUN
-*TO DISPLAY THE SQLS GENERATED
SET TRACEOFF = ALL
SET TRACEON=STMTRACE
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
-*SET TRACEOFF = ALL
-*SET TRACEON = STMTRACE//CLIENT
-*SET TRACEON = SQLAGGR//CLIENT
-*SET XRETRIEVAL = OFF
-*SET TRACEUSER = ON
-SET ECHO = ALL;
SET WEEKFIRST = 1
-*APP MAP FOCHOLD c:\ibi\WebFOCUS71\ibi_html\holdfiles
-*APP HOLD FOCHOLD
-*FILEDEF ADVHOLD c:\ibi\WebFOCUS71\ibi_html\holdfiles\ADVHOLD.FTM
-DEFAULT &Start_Date = '2005/01/01';
-DEFAULT &End_Date = '2005/01/01';
-DEFAULT &Depart_No = '0121';
-DEFAULT &ORG = FOC_NONE;
-DEFAULT &DEST = FOC_NONE;
-DEFAULT &Bus_Unit = FOC_NONE;
-DEFAULT &Analyst = FOC_NONE;
-DEFAULT &DOW = FOC_NONE;
-DEFAULT &Eqp_Code = 'FOC_NONE';
-DEFAULT &Resource = FOC_NONE;
-*DEFAULT &Equip_Code = FOC_NONE;
-DEFAULT &WFFMT = HTML;
-DEFAULT &City_Level = '0';
-DEFAULT &Dep_Level = '0';
-DEFAULT &Res_Level = '0';
-DEFAULT &Set_Level = '1';
-DEFAULT &Eqp_Level = '0';
-DEFAULT &Click_Val = '0';
-DEFAULT &Dow_Level = '0';
-*--------------------------Added by Kumud on 21st Mar,2012----------------
-DEFAULT &Time_Level = '0';
-DEFAULT &Capacity = '0';
-DEFAULT &Show_Traf = '0';
-*-------------------------------------------------------------------------
-DEFAULT &Dte_Level = '0';
-DEFAULT &Periods = '0';
-DEFAULT &ShowDmd = ' ';
-*---------------------------Added on 15th Jan, 2009----------------------------------------------------------------------------------------
-DEFAULT &Show_Vol= '0';
-DEFAULT &Show_Rev= '0';
-*------------------------------------------------------------------------------------------------------------------------------------------
-*-------------------09 DEC 2010-----------------------start------------------------------------------------------------
-DEFAULT &ShowAdjAuth = 1;
-SET &ShowAdjAu = IF &ShowAdjAuth EQ '1' THEN ' ' ELSE 'NOPRINT';
-DEFAULT &ShowAdjAvail = 1;
-SET &ShowAdjAv = IF &ShowAdjAvail EQ '1' THEN ' ' ELSE 'NOPRINT';
-SET &ShowProjBrd = 0;
-SET &ShowProjBr = IF &ShowProjBrd EQ '1' THEN ' ' ELSE 'NOPRINT';
-*---------------------------------------------------------------------------------------------------------------------------
-DEFAULT &YR_YR= '0';
-DEFAULT &Current_Capture = '2004/03/16';
-DEFAULT &Previous_Capture = '2005/08/26'
-DEFAULT &LegSeg_Type = 'Leg';
-DEFAULT &Show_Eqp = '0';
-DEFAULT &UseViewer = '0';
-DEFAULT &RTrip = '0';
-IF &UseViewer NE 1 THEN SKPVIEWER;
SET WEBVIEWER = ON
-SKPVIEWER
-*SET BUSDAYS = SMTWTFS
-*SET WEEKFIRST =1
-*---COMMON GLOBAL VARIABLES -------
-*Initialize Report Description variables
-*Report Header / Report Summary Description / Text for day of week / Report Footer
-SET &&HeaderTxt = '';
-SET &&sum_txt = '';
-SET &dow_txt = 'All';
-* &&Level - global (&&) used to determine report column output options
-* Setlevel = 0 for resource summary, 1 for class details, 2 = sum by equip code, 3= details by equip code
-SET &&Level = &Set_Level;
-IF &Eqp_Code NE 'FOC_NONE' THEN GOTO SKIPEQP;
-SET &Eqp_Code='All';
-SKIPEQP
-SET &showeqp = IF &Show_Eqp EQ 1 THEN ' ' ELSE 'NOPRINT';
-SET &&R_Trip = IF &RTrip EQ '1' THEN 1 ELSE 0;
-* Show parents global variable used for subclass aggregation - default to zero (off)
-SET &&Show_Parents =0;
-SKPLVL
-* ----------------------------------------
-* Handle report link selection (click_val)
-IF &Click_Val EQ 0 THEN SKIPLINKS;
-IF &Click_Val EQ 1 THEN CITYCLK;
-IF &Click_Val EQ 2 THEN DEPCLK;
-IF &Click_Val EQ 3 THEN RESCLK;
-IF &Click_Val EQ 4 THEN EQPCLK;
-IF &Click_Val EQ 5 THEN DTECLK;
-IF &Click_Val EQ 6 THEN DOWCLK;
-*--------------------------Added by Kumud on 21st Mar,2012----------------
-IF &Click_Val EQ 7 THEN TIMECLK ELSE SKIPLINKS;
-*-------------------------------------------------------------------------
-* -----
-CITYCLK
-SET &City_Level = IF &City_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-* -----
-DEPCLK
-SET &Dep_Level = IF &Dep_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-* -----
-RESCLK
-SET &Res_Level = IF &Res_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-* -----
-EQPCLK
-SET &Eqp_Level = IF &Eqp_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-* -----
-DOWCLK
-SET &Dow_Level = IF &Dow_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-* -----
-DTECLK
-SET &Dte_Level = IF &Dte_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-* -----
-*--------------------------Added by Kumud on 21st Mar,2012----------------
-TIMECLK
-SET &Time_Level = IF &Time_Level EQ 0 THEN 1 ELSE 0;
-GOTO SKIPLINKS;
-*
-*-------------------------------------------------------------------------
-SKIPLINKS
-* ----------------------------------------
-* YR_YR 0 - no prev dates
-* 1 - same departure dates for prev. capture
-* 2 - get prev dates that match same daysleft range
-SET &YR_YR = IF ((&YR_YR GT 0) AND ((&Previous_Capture EQ ' ') OR (&Previous_Capture EQ '0')) ) THEN 0 ELSE &YR_YR;
-SET &Previous_Capture = IF &YR_YR EQ 0 THEN ' ' ELSE &Previous_Capture;
-* ----------------------------------------
-* -------- LOAD SET COMMANDS ------
-* Load Basic Set Commands from routine
-INCLUDE rptldset
-* ----------------------------------------
-* default prev start / end (will work with ShowPrevDates =1 only)
-SET &Prev_Start = &Start_Date;
-SET &Prev_End = &End_Date;
-* -------------------
-IF (&YR_YR NE '2') THEN SKIPHIST;
-* Get date Differences for Historical Option
-SET &In_Date = &Current_Capture;
-*Get numeric value from field
-SET &Mtype = 'C';
-SET &MNum = 0;
-SET &Out_Date = &Start_Date;
-INCLUDE getdate
-SET &S_DIFF = &MNum;
-SET &MNum = 0;
-SET &Out_Date = &End_Date;
-INCLUDE getdate
-SET &E_DIFF = &MNum;
-* --------------------
-* Now use same dl difference for prev start / end from prev capture
-* Get Prev Start Range
-SET &In_Date = &Previous_Capture;
-*Get numeric value from field
-SET &Mtype = 'D';
-SET &MNum = &S_DIFF;
-INCLUDE getdate
-SET &Prev_Start = &Out_Date;
-SET &MNum = &E_DIFF;
-INCLUDE getdate
-SET &Prev_End = &Out_Date;
-SKIPHIST
-* ----------------------------------------
-* Set NewJoin parameters
-SET &GetHist = IF (&YR_YR EQ 0) THEN 0 ELSE 1;
-SET &&foot_txt = IF &GetHist EQ 0 THEN '' ELSE 'Historical Dates -- Capture:'| &Previous_Capture | ' Dep Range:' | &Prev_Start | ' to ' | &Prev_End;
-* Hide history display if no history report option selected
-SET &&ShowHst = IF (&YR_YR EQ 0) THEN 'NOPRINT' ELSE ' ' ;
-SET &&ShowDemand = IF (&ShowDmd EQ 0) THEN 'NOPRINT' ELSE ' ' ;
-*SET &InclCls = IF &&Level GT 0 THEN '1' ELSE '0';
-SET &InclCls = IF &&Level EQ 1 OR &&Level EQ 3 THEN '1' ELSE '0';
-* get match of non departed capture records matching capture date used
-SET &CapType = '0';
-SET &InclPDep = '0';
-SET &InclOpt = '0';
-SET &CDtlType = '1';
-SET &SortOrd = '1';
-*--------------------------------Added by Kumud on 25th Jun 2012 for KPI columns addition ---------------------------------
-*SET &Capacity = '0';
-*SET &Show_Traf = '1';
-*--------------------------------------------------------------------------------------------------------------------------
-* ----------------------------------------
-*INCLUDE newjoin
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-SET ECHO = ALL;
-DEFAULT &CapType ='0';
-DEFAULT &InclPDep ='0';
-DEFAULT &InclOpt ='1';
-DEFAULT &InclCls ='1';
-DEFAULT &CDtlType ='1';
-DEFAULT &SortOrd ='1';
-DEFAULT &Bus_Unit = 'FOC_NONE';
-DEFAULT &Depart_No = '0121';
-DEFAULT &ORG = 'FOC_NONE';
-DEFAULT &DEST = 'FOC_NONE';
-DEFAULT &DOW = 'FOC_NONE';
-*--------------------------Added by Kumud on 21st Mar,2012----------------
-*DEFAULT &Time = 'FOC_NONE';
-*SET &Time = IF ((&Time EQ 'ALL') OR (&Time EQ 'ALL')) THEN 'FOC_NONE' ELSE &Time;
-*-------------------------------------------------------------------------
-DEFAULT &Resource = 'FOC_NONE';
-DEFAULT &Eqp_Code = 'FOC_NONE';
-DEFAULT &Start_Date = '2006/04/02';
-DEFAULT &End_Date = '2006/04/02';
-DEFAULT &Current_Capture= '2006/04/02'
-DEFAULT &Previous_Capture = 'FOC_NONE';
-DEFAULT &Prev_Start ='FOC_NONE';
-DEFAULT &Prev_End ='FOC_NONE';
-IF &CapType EQ 6 THEN GOTO EXITC;
-SET &Capture2='2004/03/16';
-EXITC
-* ------ PREPARE PRIMARY WHERE VARIABLES ------
-* ------ Remove spaces from departure code variable (or set to FOC_NONE)
-* [NOTE: FOC_NONE causes code line to be ignored (used in where clause in mstrjoin.fex)]
-*-------------------------------Added on 7th Dec, 2009---------------------------------------------------------
-SET &Analyst1 = IF ((&Analyst EQ 'All') OR (&Analyst EQ 'FOC_NONE')) THEN 'FOC_NONE' ELSE &Analyst;
-*---------------------------------------------------------------------------------------------------------------
-IF (&Depart_No EQ 'ALL') OR (&Depart_No EQ 'All') OR (&Depart_No EQ 'FOC_NONE') THEN GOTO ALLDEP;
-SET &MYLEN = &Depart_No.LENGTH;
-SET &SPACECOUNT = 4-&MYLEN;
-SET &Depart_No = IF (&SPACECOUNT EQ 3) THEN ' ' |&Depart_No ELSE &Depart_No;
-SET &Depart_No = IF (&SPACECOUNT EQ 2) THEN ' ' |&Depart_No ELSE &Depart_No;
-SET &Depart_No = IF (&SPACECOUNT EQ 1) THEN ' ' |&Depart_No ELSE &Depart_No;
-SET &Dep_No = &Depart_No;
-GOTO ENDDEP;
-ALLDEP
-SET &Dep_No = 'FOC_NONE';
-ENDDEP
-* --- Prepare remaining variables
-SET &DAYOFWK = IF((&DOW EQ 'ALL') OR (&DOW EQ 'All')) THEN 'FOC_NONE' ELSE &DOW;
-SET &BU = IF ((&Bus_Unit EQ 'ALL') OR (&Bus_Unit EQ 'All')) THEN 'FOC_NONE' ELSE &Bus_Unit ;
-SET &Res = IF ((&Resource EQ 'ALL') OR (&Resource EQ 'All')) THEN 'FOC_NONE' ELSE &Resource;
-* -- Org and Dest values passed are ID values(not codes)
-SET &Origin = IF ((&ORG EQ 'ALL') OR (&ORG EQ 'All')) THEN 'FOC_NONE' ELSE &ORG;
-SET &Destin = IF ((&DEST EQ 'ALL') OR (&DEST EQ 'All')) THEN 'FOC_NONE' ELSE &DEST;
-SET &E_Code = IF ((&Eqp_Code EQ 'ALL') OR (&Eqp_Code EQ 'All')) THEN 'FOC_NONE' ELSE &Eqp_Code;
-*SET &DTime = IF ((&Time EQ 'ALL') OR (&Time EQ 'ALL')) THEN 'FOC_NONE' ELSE &Time;
-* ------ SET MSTRJOIN PROCESS CONTROLLING VARIABLES ------
-SET &CDtlType ='0';
-SET &B_Date = &Start_Date;
-SET &E_Date = &End_Date;
-SET &Capture1 = &Current_Capture;
-*---- For old report variables
-*IF &PostDepData EQ MISSING THEN SKPPDDATA;
-*SET &InclOpt = '0';
-*SET &InclPDep = IF &PostDepData =EQ 4 THEN '1' ELSE '0';
-*SET &CapType = IF (&PostDepData EQ 4 OR &PostDepData EQ 1) THEN '2' ELSE IF &PostDepData EQ 2 THEN '5' ELSE IF &PostDepData EQ 3 THEN '9' ELSE '0';
-*SKPPDDATA
-*IF &ShowPrevDates EQ MISSING THEN SKPPREV;
-*SET &GetHist = &ShowPrevDates;
-*SKPPREV
-*IF &GetClsData EQ MISSING THEN SKPPREV;
-*SET &InclCls = &GetClsData;
-*SET &SortOrd = &GetClsData;
-*SKPCLSDATA
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-SET &Hist_Adv = IF &B_Date LT &Capture1 THEN '0' ELSE '1';
-*------------------------------------------------------------------------------------------------------------------------------------------
-* ------- SQL CODE START -------
-*-SET &&IBIAPP_app=&IBIAPPapp;
ENGINE SQLMSS SET DEFAULT_CONNECTION rtspo
SQL SQLMSS PREPARE SQLOUT FOR
-* --------------------------------
-* --- 1st call (get current and compartment details (& class details if InclCls =1)
-*INCLUDE MSTRJOIN.FEX
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* MSTRJOIN.FEX - SQL COLUMNS (SQL PASSTHROUGH) called from primary join file NEWJOIN.FEX
-* 03/30/2007 -KH
-*
-* SPECIAL HANDLING OF CLASSES AND SUBCLASSES
-* Because joining of subclasses does not produce 1 record per class + 1 record per subclass
-* it is necessary to join two separate SQL queries into a single output (UNION ALL). This routine can be called multiple
-* Times using parameters to accomplish this. For this reason the data structures for each call be identical.
-* This is why class header info should be added per call.Reports should treat subclasses as class output.
-* Sorting within the hierarchy is optional and controlled by parameters
-*
-* PARAMETERS
-* InclPDep - Include leg post departure data in output
-* InclOpt - Include optimization details data
-* Capture1 - Capture Date Filter (required)
-* Capture2 - (optional) for capture filter date range end
-* CapType - Capture Date Range Options
-* '0' Non-Departed Captures
-* '1' Return capture records that exaclty match the current capture date
-* '2' Return post departure capture record (< current post and DL = -1 )
-* '3' Return captures that occur on or before the capture date (dups?)
-* '4' Return captures by range of capture dates (Capture1 & Capture2)
-* '5' Return all captures
-* -- Opt Cases Only --
-* '6' to return only optimized case captures for the given current capture date
-* '7' to return Post Departure Optmized case captures, and Day0, Day-1 captures for optimized cases
-* (post departure only [days left -1 capture exists])
-* -- Special --
-* '9' Exit without applying any departure OR capture date values to where clause
-*
-*
-* InclCls - Include class/subclass data in output or resource values only
-* CDtlType - '0' to return class detail data in output
-* '1' to return subclass detail data in output as class details
-* NOTE: subclass and class detail fields use same names, each returns one or the other
-* SortOrd - '0' = do not calculate sort order
-* - '1' = Include Subclasses in hierarchy order
-* Class value -> Multiply each parent class order by 100
-* Subclasses -> Add subclass_order value to calculated parent class value (above)
-* - '2' = Add Subclasses to bottom of hierarchy
-* Class value -> Remains class order value
-* Subclasses -> Add 100 to subclass_order value
-* &B_Date / &E_Date - start and end date ranges (calling routine uses nested unions to incorporate historical range records)
-*
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* --- SELECT STATEMENT ---
SELECT
bu."BU_Id"
, dep."Departure_Id", dep."Departure_Code", dep."Departure_OriginalDate"
, DATENAME(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW', DATEPART(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW1'
,'' AS "Last_Name"
, dtl."Departure_Detail_Id", dtl."Departure_Detail_Leg_Seg_Type"
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
,dtl."Departure_Detail_Departure_OriginalTime" AS 'DEP_ORGTIME'
,cpt."No_show_percentage"/100 AS 'CPT_NOSHOWPER'
-IF (&Capacity NE '0') THEN ENDCAPA1;
,leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY'
-GOTO CALSKP;
-ENDCAPA1
-IF (&Capacity NE '1') THEN ENDCAPB1;
,leg."Leg_Compartment_Detail_Physical_Capacity" AS 'DEP_CAPACITY'
-GOTO CALSKP;
-ENDCAPB1
,ISNULL(leg."Leg_Compartment_Detail_Theoretical_Capacity",cpt."CURRENT_AUTHORIZATION") AS 'DEP_CAPACITY'
-CALSKP
-*--------------------------------------------------------------------------------------------------------------------------------------
, eqp."Equipment_Code"
, cpr."CityPair_Name"
, cap."Departure_Detail_Capture_Date"
, cap."Departure_Detail_Capture_Days_Left"
, cpt."DDCC_ID",
cpt."COMPARTMENT_CODE", cpt."COMPARTMENT_ORDER",
cpt."CURRENT_AUTHORIZATION" AS 'CPT_CURRENT_AUTHORIZATION',
cpt."ADJUSTED_AUTHORIZATION" AS 'CPT_ADJUSTED_AUTHORIZATION',
cpt."CURRENT_AVAILABLITY" AS 'CPT_CURRENT_AVAILABLITY',
cpt."ADJUSTED_AVAILABLITY" AS 'CPT_ADJUSTED_AVAILABLITY',
cpt."PROJECTED_BOOKING" AS 'CPT_PROJECTED_BOOKING',
cpt."CURRENT_BOOKING" AS 'CPT_CURRENT_BOOKING',
cpt."GROUP_BOOKING" AS 'CPT_GROUP_BOOKING',
cpt."TOTAL_DEMAND" AS 'CPT_TOTAL_DEMAND',
cpt."REMAINING_DEMAND" AS 'CPT_REMAINING_DEMAND',
ISNULL(cpt."PROJECTED_REVENUE",tmp."PROJECTED_REVENUE") AS 'CPT_PROJECTED_REVENUE',
cpt."WAITLIST" AS 'CPT_WAITLIST',
cpt."REMAINING_CANCELLATION" AS 'CPT_REMAINING_CANCELLATION'
,cpt."VOLUME_TARGET" AS 'CPT_VOLUME_TARGET',
cpt."REVENUE_TARGET" AS 'CPT_REVENUE_TARGET',
cpt."PROJECTED_BOARDING" AS 'CPT_PROJECTED_BOARDING'
-IF (&InclPDep NE '1') THEN ENDPDP;
, dpd."Departure_Detail_PostDeparture_Id" , dpd."Departure_Detail_PostDeparture_Departure_Detail_Id_fk"
, dpd."Departure_Detail_PostDeparture_IsManual" , dpd."Departure_Detail_PostDeparture_DayMark" , dpd."Departure_Detail_PostDeparture_TimeMark"
, dpd."Departure_Detail_PostDeparture_Minutes_Late" , dpd."Departure_Detail_PostDeparture_Creation_Date"
-ENDPDP
-IF (&InclOpt EQ '0') THEN ENDOPT;
, cd."Case_Id", cd."Case_Number", cd."Case_BU_Id_fk", cd."Case_Primary_Analyst_Id_fk", cd."Case_Departure_Id_fk"
, cd."Case_Departure_Detail_Id_fk", cd."Case_Secondary_Analyst_Id_fk", cd."Case_CityPair_Id_fk", cd."Case_Departure_Date"
, cd."Case_Status_Id_fk", cd."Case_Revenue_Id_fk", cd."Case_Importance", cd."Case_Creation_Date", cd."Case_Approve", cd."Case_Reminder"
, cd."CaptureDate", cd."Case_Expiration_Date"
, cs."Case_Id_Fk", cs."Case_Status"
-ENDOPT
-IF (&InclCls EQ '0') THEN ENDSELECT;
,
(&CDtlType) AS "IS_SUBCLS",
cls."DDCCLS_ID",
cls."CLASS_CODE" AS "CLS_CODE",
cls."CLASS_ORDER" AS "CLS_ORDER",
cst."CLASS_STRUCTURE_IS_SUMMARY"
-IF (&CDtlType EQ '1')THEN GETSUBCLS;
,cls."CURRENT_AUTHORIZATION" ,
cls."CURRENT_AVAILABLITY" ,
cls."ADJUSTED_AUTHORIZATION" ,
cls."ADJUSTED_AVAILABLITY" ,
cls."PROJECTED_BOOKING" ,
cls."CURRENT_BOOKING" ,
cls."GROUP_BOOKING" ,
cls."TOTAL_DEMAND" ,
cls."REMAINING_DEMAND" ,
cls."PROJECTED_REVENUE" ,
cls."WAITLIST" ,
cls."CLASS_CODE" ,
cls."PROJECTED_BOARDING",
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
cls."No_show_percentage"/100 AS 'NOSHOWPER',
-IF (&Capacity NE '0') THEN ENDCLSA1;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO CLSSKP;
-ENDCLSA1
-IF (&Capacity NE '1') THEN ENDCLSB1;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO CLSSKP;
-ENDCLSB1
cls."CURRENT_AUTHORIZATION" AS 'DEP_CAPACITY',
-CLSSKP
-*--------------------------------------------------------------------------------------------------------------------------------------
-IF (&SortOrd = '1') THEN CALCORD;
cls."CLASS_ORDER" AS "CLASS_ORDER"
-GOTO ENDCORD;
-CALCORD
(cls."CLASS_ORDER" * 100) AS "CLASS_ORDER"
-ENDCORD
-GOTO ENDSELECT;
-GETSUBCLS
, sub."CURRENT_AUTHORIZATION",
sub."CURRENT_AVAILABLITY",
sub."ADJUSTED_AUTHORIZATION",
sub."ADJUSTED_AVAILABLITY",
sub."PROJECTED_BOOKING",
sub."CURRENT_BOOKING",
sub."GROUP_BOOKING",
sub."TOTAL_DEMAND",
sub."REMAINING_DEMAND",
sub."PROJECTED_REVENUE",
sub."WAITLIST",
sub."SUBCLASS_CODE" AS "CLASS_CODE" ,
sub."PROJECTED_BOARDING",
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
sub."No_show_percentage"/100 AS 'NOSHOWPER',
-IF (&Capacity NE '0') THEN ENDSCLSA1;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO SCLSSKP;
-ENDSCLSA1
-IF (&Capacity NE '1') THEN ENDSCLSB1;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO SCLSSKP;
-ENDSCLSB1
sub."CURRENT_AUTHORIZATION" AS 'DEP_CAPACITY',
-SCLSSKP
-*--------------------------------------------------------------------------------------------------------------------------------------
-IF (&SortOrd = '1') THEN CLSORD;
-IF (&SortOrd = '2') THEN BTMORD;
cls."CLASS_ORDER" AS "CLASS_ORDER"
-GOTO ENDCORD;
-CLSORD
(sub."SUBCLASS_ORDER" + (cls."CLASS_ORDER" * 100)) AS "CLASS_ORDER"
-GOTO ENDCORD;
-BTMORD
(sub."SUBCLASS_ORDER" + 100 )AS "CLASS_ORDER"
-ENDCORD
-ENDSELECT
FROM
RTS_PO.DBO.BUSINESS_UNITS(NOLOCK) bu
JOIN RTS_PO.DBO.DEPARTURE(NOLOCK) dep
ON bu."BU_ID" = dep."DEPARTURE_BUSINESS_UNIT_ID_FK"
-*----------------------------------------------------Added on 7th Dec, 2009--------------------------------------------
JOIN RTS_PO.DBO.DEPARTURE_DETAILS(NOLOCK) dtl
ON dep."DEPARTURE_ID" = dtl."DEPARTURE_DETAIL_DEPARTURE_ID_FK"
JOIN RTS_PO.DBO.ROUTE_LEG_SEGMENT(NOLOCK) lsg
ON dtl."DEPARTURE_DETAIL_ROUTE_LEG_SEGMENT_ID_FK" = lsg."ROUTE_LEG_SEGMENT_ID"
JOIN RTS_PO.DBO.CITY_PAIRS(NOLOCK) cpr
ON lsg."ROUTE_LEG_SEGMENT_CITYPAIR_ID_FK" = cpr."CITYPAIR_ID"
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURES(NOLOCK) cap
ON dtl."DEPARTURE_DETAIL_ID" = cap."DEPARTURE_DETAIL_CAPTURE_DEPATURE_DETAILS_ID_FK"
-*----------------------------------------------------Added on 18th Feb, 2010--------------------------------------------
-IF &Analyst1 EQ 'FOC_NONE' THEN GOTO PANA0;
JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date ,
CAST(CONVERT(VARCHAR(8),ISNULL(Analyst_Expiry_date,GETDATE()), 1)AS DATETIME) Analyst_Expiry_date,
Primary_Analyst_Departure_Code_fk ,Leg_CityPair_ID_FK
FROM Users us JOIN Primary_Analysts
ON User_Count_Id = Analyst_Id_fk
WHERE Last_Name = '&Analyst1') Ana
ON dep."Departure_Code" = Ana."Primary_Analyst_Departure_Code_fk"
AND cap."DEPARTURE_DETAIL_CAPTURE_DATE" BETWEEN Ana."Analyst_Effective_date" AND Ana."Analyst_Expiry_date"
AND lsg.Route_Leg_Segment_CityPair_Id_fk = ISNULL(Ana.Leg_CityPair_ID_FK,lsg.Route_Leg_Segment_CityPair_Id_fk)
-PANA0
JOIN RTS_PO.DBO.Equipments(NOLOCK) eqp
ON cap."Departure_Detail_Capture_Equipment_Id_fk" = eqp."Equipment_Id"
-*----------------------------------------------------------------------------------------------------------------------
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURE_COMPARTMENTS(NOLOCK) cpt
ON cap."DEPARTURE_DETAIL_CAPTURE_ID" = cpt."DDCC_DEPARTURE_DETAIL_CAPTURE_ID_FK"
LEFT JOIN RTS_PO.DBO."LEG_COMPARTMENT_DETAILS"(NOLOCK) leg
ON cpt."DDCC_ID" = leg."DDCC_ID_FK"
JOIN (SELECT DDCCls_DDCC_Id_fk ,
SUM(CASE WHEN Class_Structure_Is_Summary = 0 THEN ISNULL(CLS.Projected_Booking,CLS.Current_Booking) * CLS.CurrentFareValue
ELSE ISNULL(DDCScls.Projected_Booking,DDCScls.Current_Booking) * DDCScls.CurrentFareValue END) AS PROJECTED_REVENUE
FROM RTS_PO.DBO.Departure_Detail_Capture_Classes CLS (NOLOCK)
JOIN RTS_PO.DBO.class_structures Cs
ON Cs.Class_Structure_Id = CLS.DDCCls_Class_Structure_Id_fk
LEFT JOIN RTS_PO.DBO.Departure_Detail_Capture_Subclasses DDCScls (NOLOCK)
On CLS.DDCCls_Id=DDCScls.DDCSCls_DDCCls_Id_fk
GROUP BY DDCCls_DDCC_Id_fk) Tmp
ON cpt."DDCC_ID"=Tmp."DDCCls_DDCC_Id_fk"
-* Include Post Departure Details (left join)
-IF (&InclPDep NE '1') THEN SKPPD;
LEFT OUTER JOIN RTS_PO.DBO.DEPARTURE_DETAIL_POSTDEPARTURE(NOLOCK) dpd
ON dtl."DEPARTURE_DETAIL_ID" = dpd."DEPARTURE_DETAIL_POSTDEPARTURE_DEPARTURE_DETAIL_ID_FK"
-*LEFT OUTER JOIN RTS_PO.DBO.POSTDEPARTURE_COMPARTMENT_STRUCTURES pdc
-*ON dpd."DEPARTURE_DETAIL_POSTDEPARTURE_ID" = pdc."DEPARTURE_DETAIL_POSTDEPARTURE_ID_FK"
-SKPPD
-*INCLUDE Optimization Case Details (left join)
-IF (&InclOpt EQ '0') THEN SKPOPT;
LEFT OUTER JOIN RTS_PO.DBO.CASE_DETAILS(NOLOCK) cd
ON
-*-------------------------------Added on 25th Nov, 2009---------------------------------------------------------
-*dtl."DEPARTURE_DETAIL_ID" =
-* dtl.Departure_Detail_Departure_Id_fk=
-* CASE WHEN (RTS_PO.DBO.ufn_GetBusinessUnitType(1)= 'Leg') THEN
-* cd."CASE_DEPARTURE_DETAIL_ID_FK" ELSE cd."CASE_DEPARTURE_ID_FK" END
-*---------------------------------------------------------------------------------------------------------------
dtl."DEPARTURE_DETAIL_ID" = cd."CASE_DEPARTURE_DETAIL_ID_FK"
AND cap.DEPARTURE_DETAIL_CAPTURE_DATE = cd."CAPTUREDATE"
LEFT OUTER JOIN RTS_PO.DBO.CASE_STATUS_SUMMARY(NOLOCK) cs
ON cd."CASE_ID" = cs."CASE_ID_FK"
-SKPOPT
-IF (&InclCls EQ '0') THEN SKIPCLASSES;
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURE_CLASSES(NOLOCK) cls
ON cpt."DDCC_ID" = cls."DDCCLS_DDCC_ID_FK"
LEFT OUTER JOIN RTS_PO.DBO.CLASS_STRUCTURES(NOLOCK) cst
ON cls."DDCCLS_CLASS_STRUCTURE_ID_FK"= cst."CLASS_STRUCTURE_ID"
-* Join to obtain subclasses only
-IF (&CDtlType EQ '0') THEN SKIPCLASSES;
JOIN RTS_PO.DBO.Departure_Detail_Capture_Subclasses(NOLOCK) sub
ON cls."DDCCls_Id" = sub."DDCSCls_DDCCls_Id_fk"
-SKIPCLASSES
-* ------------------------------------
-* ***** SQL WHERE CLAUSE *****
-* set 1st where clause line to always appear. FOC_NONE causes each line to be skipped where included. Each AND adds to initial where
WHERE (1 = 1)
AND (bu."BU_ID" = &BU)
AND (dep."Departure_Code" = '&Dep_No')
-*-------------------------------Added on 7th Dec, 2009---------------------------------------------------------
-IF &&R_Trip NE 1 THEN SKPRTRIP;
AND ((cpr."Origination_City_Id_fk" = '&Origin') OR (cpr."Origination_City_Id_fk" = '&Destin'))
AND ((cpr."Destination_City_Id_fk" = '&Destin') OR (cpr."Destination_City_Id_fk" = '&Origin'))
-GOTO ENDOD;
-SKPRTRIP
AND (cpr."Origination_City_Id_fk" = '&Origin')
AND (cpr."Destination_City_Id_fk" = '&Destin')
-ENDOD
AND (DATENAME(weekday, dep."Departure_OriginalDate") = '&DAYOFWK')
AND (eqp."Equipment_Code" = '&E_Code')
AND (cpt."COMPARTMENT_CODE" = '&Res')
AND (dtl."DEPARTURE_DETAIL_LEG_SEG_TYPE" = '&LegSeg_Type')
-* ------- Exit w/o ending sql or applying any date ranges to where clause
-IF &CapType EQ '9' THEN ENDSQL;
-* --------------------------------------------------------
-* -- add filter to show only parent classes where selected
-IF &&Show_Parents NE '0' THEN SHOWPRNTS;
-IF (&InclCls EQ '0') THEN SKIPPRNTS;
-* -- hide parents [show all subclasses and only classes that are not summaries]
-*-*AND (((&CDtlType) = '1') OR (cst."CLASS_STRUCTURE_IS_SUMMARY" = '0'))
AND (cst."CLASS_STRUCTURE_IS_SUMMARY" = '0')
-GOTO SKIPPRNTS;
-SHOWPRNTS
-* show the parents (hiding any subclasses of parent) [all classes and subclasses not summaries]
-*AND ((cst."CLASS_STRUCTURE_IS_SUMMARY" = '0') OR ((&CDtlType) = '0'))
-SKIPPRNTS
-*---------------------------------------------------------
-* --- Apply departure date range filter ---
AND ((dep."Departure_OriginalDate" >= '&B_Date') AND (dep."Departure_OriginalDate" <= '&E_Date'))
-* ------------ Capture Date Filters ------------
-*IF &CapType EQ '8' THEN ENDDATES;
-IF &CapType EQ '7' THEN ALLOPT;
-IF &CapType EQ '6' THEN CUROPT;
-IF &CapType EQ '5' THEN ALLCAPS;
-IF &CapType EQ '4' THEN CAPRANGE;
-IF &CapType EQ '3' THEN PRECAPDT;
-IF &CapType EQ '2' THEN POSTDEPS;
-IF &CapType EQ '1' THEN EXACTCAP;
-IF &CapType EQ '0' THEN NONDEPCP;
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-IF &CapType EQ '10' THEN NONDEPCP1;
-*------------------------------------------------------------------------------------------------------------------------------------------
-* -------------------------------
-* Post departure Optmization comparison -- all opt cases plus the Day0 and Day-1 captures (CapType = 7)
-* (only for departures where a post departure capture record exists)
-ALLOPT
AND (
(cd."CaptureDate" = cap."Departure_Detail_Capture_Date")
OR
(cap."Departure_Detail_Capture_Days_Left" <=0)
)
AND EXISTS (SELECT '1' FROM RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURES cap WHERE (cap."Departure_Detail_Capture_Depature_Details_Id_fk" = Departure_Detail_Id) AND (cap."Departure_Detail_Capture_Days_Left" = -1) )
-GOTO ENDDATES;
-* --- Optimized case captures only for current capture (CapType = 6)
-CUROPT
-* AND (cd."CaptureDate" = '&Capture1')
AND ((cap."Departure_Detail_Capture_Date" >= '&Capture1') AND (cap."Departure_Detail_Capture_Date" <= '&Capture2'))
AND (cd."CaptureDate" = cap."Departure_Detail_Capture_Date")
-GOTO ENDDATES;
-* --- All captures <= current capture date (CapType = 5)
-ALLCAPS
AND (cap."Departure_Detail_Capture_Date" <= '&Capture1')
-GOTO ENDDATES;
-* --- range of captures (CapType = 4)
-CAPRANGE
-GOTO ENDDATES;
-* --- before capture (CapType = 3)
-PRECAPDT
-GOTO ENDDATES;
-* --- post departure captures only (day -1) (CapType = 2)
-POSTDEPS
AND
((cap."Departure_Detail_Capture_Date" <= '&Capture1') AND (cap."Departure_Detail_Capture_Days_Left" = -1) )
-GOTO ENDDATES;
-* --- exact capture date (included any DL-1 records(CapType = 1))
-EXACTCAP
AND (cap."Departure_Detail_Capture_Date" = '&Capture1')
-GOTO ENDDATES;
-* --- non-departed captures only filter (CapType = 0)
-NONDEPCP
AND ((cap."Departure_Detail_Capture_Date" = '&Capture1') AND (cap."Departure_Detail_Capture_Days_Left" >=0))
-GOTO ENDDATES;
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-NONDEPCP1
AND (dep."Departure_OriginalDate" < '&Capture1')
-GOTO ENDDATES;
-*------------------------------------------------------------------------------------------------------------------------------------------
-ENDDATES
-ENDSQL
-IF &InclCls EQ 0 THEN ENDCURRENT;
-* --- 2nd call to merge with subclasses if class/sublclass detail output selected (InclCls=1)
UNION ALL
-SET &CDtlType ='1';
-*INCLUDE MSTRJOIN.FEX
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* MSTRJOIN.FEX - SQL COLUMNS (SQL PASSTHROUGH) called from primary join file NEWJOIN.FEX
-* 03/30/2007 -KH
-*
-* SPECIAL HANDLING OF CLASSES AND SUBCLASSES
-* Because joining of subclasses does not produce 1 record per class + 1 record per subclass
-* it is necessary to join two separate SQL queries into a single output (UNION ALL). This routine can be called multiple
-* Times using parameters to accomplish this. For this reason the data structures for each call be identical.
-* This is why class header info should be added per call.Reports should treat subclasses as class output.
-* Sorting within the hierarchy is optional and controlled by parameters
-*
-* PARAMETERS
-* InclPDep - Include leg post departure data in output
-* InclOpt - Include optimization details data
-* Capture1 - Capture Date Filter (required)
-* Capture2 - (optional) for capture filter date range end
-* CapType - Capture Date Range Options
-* '0' Non-Departed Captures
-* '1' Return capture records that exaclty match the current capture date
-* '2' Return post departure capture record (< current post and DL = -1 )
-* '3' Return captures that occur on or before the capture date (dups?)
-* '4' Return captures by range of capture dates (Capture1 & Capture2)
-* '5' Return all captures
-* -- Opt Cases Only --
-* '6' to return only optimized case captures for the given current capture date
-* '7' to return Post Departure Optmized case captures, and Day0, Day-1 captures for optimized cases
-* (post departure only [days left -1 capture exists])
-* -- Special --
-* '9' Exit without applying any departure OR capture date values to where clause
-*
-*
-* InclCls - Include class/subclass data in output or resource values only
-* CDtlType - '0' to return class detail data in output
-* '1' to return subclass detail data in output as class details
-* NOTE: subclass and class detail fields use same names, each returns one or the other
-* SortOrd - '0' = do not calculate sort order
-* - '1' = Include Subclasses in hierarchy order
-* Class value -> Multiply each parent class order by 100
-* Subclasses -> Add subclass_order value to calculated parent class value (above)
-* - '2' = Add Subclasses to bottom of hierarchy
-* Class value -> Remains class order value
-* Subclasses -> Add 100 to subclass_order value
-* &B_Date / &E_Date - start and end date ranges (calling routine uses nested unions to incorporate historical range records)
-*
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* --- SELECT STATEMENT ---
SELECT
bu."BU_Id"
, dep."Departure_Id", dep."Departure_Code", dep."Departure_OriginalDate"
, DATENAME(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW', DATEPART(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW1'
, '' AS "Last_Name"
, dtl."Departure_Detail_Id", dtl."Departure_Detail_Leg_Seg_Type"
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
,dtl."Departure_Detail_Departure_OriginalTime" AS 'DEP_ORGTIME'
,cpt."No_show_percentage"/100 AS 'CPT_NOSHOWPER'
-IF (&Capacity NE '0') THEN ENDCAPA3;
,leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY'
-GOTO CALSKP11;
-ENDCAPA3
-IF (&Capacity NE '1') THEN ENDCAPB2;
,leg."Leg_Compartment_Detail_Physical_Capacity" AS 'DEP_CAPACITY'
-GOTO CALSKP11;
-ENDCAPB2
,ISNULL(leg."Leg_Compartment_Detail_Theoretical_Capacity",cpt."CURRENT_AUTHORIZATION") AS 'DEP_CAPACITY'
-CALSKP11
-*--------------------------------------------------------------------------------------------------------------------------------------
-*-------------------------------------------------------------------------
, eqp."Equipment_Code"
, cpr."CityPair_Name"
, cap."Departure_Detail_Capture_Date"
, cap."Departure_Detail_Capture_Days_Left"
, cpt."DDCC_ID",
cpt."COMPARTMENT_CODE", cpt."COMPARTMENT_ORDER",
cpt."CURRENT_AUTHORIZATION" AS 'CPT_CURRENT_AUTHORIZATION',
cpt."ADJUSTED_AUTHORIZATION" AS 'CPT_ADJUSTED_AUTHORIZATION',
cpt."CURRENT_AVAILABLITY" AS 'CPT_CURRENT_AVAILABLITY',
cpt."ADJUSTED_AVAILABLITY" AS 'CPT_ADJUSTED_AVAILABLITY',
cpt."PROJECTED_BOOKING" AS 'CPT_PROJECTED_BOOKING',
cpt."CURRENT_BOOKING" AS 'CPT_CURRENT_BOOKING',
cpt."GROUP_BOOKING" AS 'CPT_GROUP_BOOKING',
cpt."TOTAL_DEMAND" AS 'CPT_TOTAL_DEMAND',
cpt."REMAINING_DEMAND" AS 'CPT_REMAINING_DEMAND',
cpt."PROJECTED_REVENUE" AS 'CPT_PROJECTED_REVENUE',
cpt."WAITLIST" AS 'CPT_WAITLIST',
cpt."REMAINING_CANCELLATION" AS 'CPT_REMAINING_CANCELLATION'
,cpt."VOLUME_TARGET" AS 'CPT_VOLUME_TARGET',
cpt."REVENUE_TARGET" AS 'CPT_REVENUE_TARGET',
cpt."PROJECTED_BOARDING" AS 'CPT_PROJECTED_BOARDING'
-IF (&InclPDep NE '1') THEN ENDPDP1;
, dpd."Departure_Detail_PostDeparture_Id" , dpd."Departure_Detail_PostDeparture_Departure_Detail_Id_fk"
, dpd."Departure_Detail_PostDeparture_IsManual" , dpd."Departure_Detail_PostDeparture_DayMark" , dpd."Departure_Detail_PostDeparture_TimeMark"
, dpd."Departure_Detail_PostDeparture_Minutes_Late" , dpd."Departure_Detail_PostDeparture_Creation_Date"
-ENDPDP1
-IF (&InclOpt EQ '0') THEN ENDOPT1;
, cd."Case_Id", cd."Case_Number", cd."Case_BU_Id_fk", cd."Case_Primary_Analyst_Id_fk", cd."Case_Departure_Id_fk"
, cd."Case_Departure_Detail_Id_fk", cd."Case_Secondary_Analyst_Id_fk", cd."Case_CityPair_Id_fk", cd."Case_Departure_Date"
, cd."Case_Status_Id_fk", cd."Case_Revenue_Id_fk", cd."Case_Importance", cd."Case_Creation_Date", cd."Case_Approve", cd."Case_Reminder"
, cd."CaptureDate", cd."Case_Expiration_Date"
, cs."Case_Id_Fk", cs."Case_Status"
-ENDOPT1
-IF (&InclCls EQ '0') THEN ENDSELECT1;
,
(&CDtlType) AS "IS_SUBCLS",
cls."DDCCLS_ID",
cls."CLASS_CODE" AS "CLS_CODE",
cls."CLASS_ORDER" AS "CLS_ORDER",
cst."CLASS_STRUCTURE_IS_SUMMARY"
-IF (&CDtlType EQ '1')THEN GETSUBCLS1;
,cls."CURRENT_AUTHORIZATION" ,
cls."CURRENT_AVAILABLITY" ,
cls."ADJUSTED_AUTHORIZATION" ,
cls."ADJUSTED_AVAILABLITY" ,
cls."PROJECTED_BOOKING" ,
cls."CURRENT_BOOKING" ,
cls."GROUP_BOOKING" ,
cls."TOTAL_DEMAND" ,
cls."REMAINING_DEMAND" ,
cls."PROJECTED_REVENUE",
cls."WAITLIST" ,
cls."CLASS_CODE" ,
cls."PROJECTED_BOARDING" ,
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
cls."No_show_percentage"/100 AS 'NOSHOWPER',
-IF (&Capacity NE '0') THEN ENDCLSA2;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO CLSSKP11;
-ENDCLSA2
-IF (&Capacity NE '1') THEN ENDCLSB2;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO CLSSKP11;
-ENDCLSB2
cls."CURRENT_AUTHORIZATION" AS 'DEP_CAPACITY',
-CLSSKP11
-*--------------------------------------------------------------------------------------------------------------------------------------
-IF (&SortOrd = '1') THEN CALCORD1;
cls."CLASS_ORDER" AS "CLASS_ORDER"
-GOTO ENDCORD1;
-CALCORD1
(cls."CLASS_ORDER" * 100) AS "CLASS_ORDER"
-ENDCORD1
-GOTO ENDSELECT1;
-GETSUBCLS1
,sub."CURRENT_AUTHORIZATION",
sub."CURRENT_AVAILABLITY",
sub."ADJUSTED_AUTHORIZATION",
sub."ADJUSTED_AVAILABLITY",
sub."PROJECTED_BOOKING",
sub."CURRENT_BOOKING",
sub."GROUP_BOOKING",
sub."TOTAL_DEMAND",
sub."REMAINING_DEMAND",
sub."PROJECTED_REVENUE",
sub."WAITLIST",
sub."SUBCLASS_CODE" AS "CLASS_CODE" ,
sub."PROJECTED_BOARDING" ,
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
sub."No_show_percentage"/100 AS 'NOSHOWPER',
-IF (&Capacity NE '0') THEN ENDSCLSA2;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO SCLSSKP11;
-ENDSCLSA2
-IF (&Capacity NE '1') THEN ENDSCLSB2;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO SCLSSKP11;
-ENDSCLSB2
sub."CURRENT_AUTHORIZATION" AS 'DEP_CAPACITY',
-SCLSSKP11
-*--------------------------------------------------------------------------------------------------------------------------------------
-IF (&SortOrd = '1') THEN CLSORD1;
-IF (&SortOrd = '2') THEN BTMORD1;
cls."CLASS_ORDER" AS "CLASS_ORDER"
-GOTO ENDCORD1;
-CLSORD1
(sub."SUBCLASS_ORDER" + (cls."CLASS_ORDER" * 100)) AS "CLASS_ORDER"
-GOTO ENDCORD1;
-BTMORD1
(sub."SUBCLASS_ORDER" + 100 )AS "CLASS_ORDER"
-ENDCORD1
-ENDSELECT1
FROM
RTS_PO.DBO.BUSINESS_UNITS(NOLOCK) bu
JOIN RTS_PO.DBO.DEPARTURE(NOLOCK) dep
ON bu."BU_ID" = dep."DEPARTURE_BUSINESS_UNIT_ID_FK"
-*----------------------------------------------------Added on 7th Dec, 2009--------------------------------------------
-*----------------------------------------------------------------------------------------------------------------------
JOIN RTS_PO.DBO.DEPARTURE_DETAILS(NOLOCK) dtl
ON dep."DEPARTURE_ID" = dtl."DEPARTURE_DETAIL_DEPARTURE_ID_FK"
JOIN RTS_PO.DBO.ROUTE_LEG_SEGMENT(NOLOCK) lsg
ON dtl."DEPARTURE_DETAIL_ROUTE_LEG_SEGMENT_ID_FK" = lsg."ROUTE_LEG_SEGMENT_ID"
JOIN RTS_PO.DBO.CITY_PAIRS(NOLOCK) cpr
ON lsg."ROUTE_LEG_SEGMENT_CITYPAIR_ID_FK" = cpr."CITYPAIR_ID"
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURES(NOLOCK) cap
ON dtl."DEPARTURE_DETAIL_ID" = cap."DEPARTURE_DETAIL_CAPTURE_DEPATURE_DETAILS_ID_FK"
-*----------------------------------------------------Added on 18th Feb, 2010--------------------------------------------
-IF &Analyst1 EQ 'FOC_NONE' THEN GOTO PANA1;
JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date ,
CAST(CONVERT(VARCHAR(8),ISNULL(Analyst_Expiry_date,GETDATE()), 1)AS DATETIME) Analyst_Expiry_date,
Primary_Analyst_Departure_Code_fk ,Leg_CityPair_ID_FK
FROM Users us JOIN Primary_Analysts
ON User_Count_Id = Analyst_Id_fk
WHERE Last_Name = '&Analyst1') Ana
ON dep."Departure_Code" = Ana."Primary_Analyst_Departure_Code_fk"
AND cap."DEPARTURE_DETAIL_CAPTURE_DATE" BETWEEN Ana."Analyst_Effective_date" AND Ana."Analyst_Expiry_date"
AND lsg.Route_Leg_Segment_CityPair_Id_fk = ISNULL(Ana.Leg_CityPair_ID_FK,lsg.Route_Leg_Segment_CityPair_Id_fk)
-PANA1
JOIN RTS_PO.DBO.Equipments(NOLOCK) eqp
ON cap."Departure_Detail_Capture_Equipment_Id_fk" = eqp."Equipment_Id"
-*----------------------------------------------------------------------------------------------------------------------
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURE_COMPARTMENTS(NOLOCK) cpt
ON cap."DEPARTURE_DETAIL_CAPTURE_ID" = cpt."DDCC_DEPARTURE_DETAIL_CAPTURE_ID_FK"
LEFT JOIN RTS_PO.DBO."LEG_COMPARTMENT_DETAILS"(NOLOCK) leg
ON cpt."DDCC_ID" = leg."DDCC_ID_FK"
-* Include Post Departure Details (left join)
-IF (&InclPDep NE '1') THEN SKPPD1;
LEFT OUTER JOIN RTS_PO.DBO.DEPARTURE_DETAIL_POSTDEPARTURE(NOLOCK) dpd
ON dtl."DEPARTURE_DETAIL_ID" = dpd."DEPARTURE_DETAIL_POSTDEPARTURE_DEPARTURE_DETAIL_ID_FK"
-*LEFT OUTER JOIN RTS_PO.DBO.POSTDEPARTURE_COMPARTMENT_STRUCTURES pdc
-*ON dpd."DEPARTURE_DETAIL_POSTDEPARTURE_ID" = pdc."DEPARTURE_DETAIL_POSTDEPARTURE_ID_FK"
-SKPPD1
-* INCLUDE Optimization Case Details (left join)
-IF (&InclOpt EQ '0') THEN SKPOPT1;
LEFT OUTER JOIN RTS_PO.DBO.CASE_DETAILS(NOLOCK) cd
ON
-*-------------------------------Added on 25th Nov, 2009---------------------------------------------------------
-*dtl."DEPARTURE_DETAIL_ID" =
-* dtl.Departure_Detail_Departure_Id_fk=
-* CASE WHEN (RTS_PO.DBO.ufn_GetBusinessUnitType(1)= 'Leg') THEN
-* cd."CASE_DEPARTURE_DETAIL_ID_FK" ELSE cd."CASE_DEPARTURE_ID_FK" END
-*---------------------------------------------------------------------------------------------------------------
dtl."DEPARTURE_DETAIL_ID" = cd."CASE_DEPARTURE_DETAIL_ID_FK"
AND cap.DEPARTURE_DETAIL_CAPTURE_DATE = cd."CAPTUREDATE"
LEFT OUTER JOIN RTS_PO.DBO.CASE_STATUS_SUMMARY(NOLOCK) cs
ON cd."CASE_ID" = cs."CASE_ID_FK"
-SKPOPT1
-IF (&InclCls EQ '0') THEN SKIPCLASSES1;
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURE_CLASSES(NOLOCK) cls
ON cpt."DDCC_ID" = cls."DDCCLS_DDCC_ID_FK"
LEFT OUTER JOIN RTS_PO.DBO.CLASS_STRUCTURES(NOLOCK) cst
ON cls."DDCCLS_CLASS_STRUCTURE_ID_FK"= cst."CLASS_STRUCTURE_ID"
-* Join to obtain subclasses only
-IF (&CDtlType EQ '0') THEN SKIPCLASSES1;
JOIN RTS_PO.DBO.Departure_Detail_Capture_Subclasses(NOLOCK) sub
ON cls."DDCCls_Id" = sub."DDCSCls_DDCCls_Id_fk"
-SKIPCLASSES1
-* ------------------------------------
-* ***** SQL WHERE CLAUSE *****
-* set 1st where clause line to always appear. FOC_NONE causes each line to be skipped where included. Each AND adds to initial where
WHERE (1 = 1)
AND (bu."BU_ID" = &BU)
AND (dep."Departure_Code" = '&Dep_No')
-*-------------------------------Added on 7th Dec, 2009---------------------------------------------------------
-*---------------------------------------------------------------------------------------------------------------
-IF &&R_Trip NE 1 THEN SKPRTRIP1;
AND ((cpr."Origination_City_Id_fk" = '&Origin') OR (cpr."Origination_City_Id_fk" = '&Destin'))
AND ((cpr."Destination_City_Id_fk" = '&Destin') OR (cpr."Destination_City_Id_fk" = '&Origin'))
-GOTO ENDOD1;
-SKPRTRIP1
AND (cpr."Origination_City_Id_fk" = '&Origin')
AND (cpr."Destination_City_Id_fk" = '&Destin')
-ENDOD1
AND (DATENAME(weekday, dep."Departure_OriginalDate") = '&DAYOFWK')
AND (eqp."Equipment_Code" = '&E_Code')
AND (cpt."COMPARTMENT_CODE" = '&Res')
AND (dtl."DEPARTURE_DETAIL_LEG_SEG_TYPE" = '&LegSeg_Type')
-* ------- Exit w/o ending sql or applying any date ranges to where clause
-IF &CapType EQ '9' THEN ENDSQL1;
-* --------------------------------------------------------
-* -- add filter to show only parent classes where selected
-IF &&Show_Parents NE '0' THEN SHOWPRNTS1;
-IF (&InclCls EQ '0') THEN SKIPPRNTS1;
-* -- hide parents [show all subclasses and only classes that are not summaries]
-*-*AND (((&CDtlType) = '1') OR (cst."CLASS_STRUCTURE_IS_SUMMARY" = '0'))
-GOTO SKIPPRNTS1;
-SHOWPRNTS1
-* show the parents (hiding any subclasses of parent) [all classes and subclasses not summaries]
-*-*AND ((cst."CLASS_STRUCTURE_IS_SUMMARY" = '0') OR ((&CDtlType) = '0'))
-SKIPPRNTS1
-*---------------------------------------------------------
-* --- Apply departure date range filter ---
AND ((dep."Departure_OriginalDate" >= '&B_Date') AND (dep."Departure_OriginalDate" <= '&E_Date'))
-* ------------ Capture Date Filters ------------
-*IF &CapType EQ '8' THEN ENDDATES1;
-IF &CapType EQ '7' THEN ALLOPT1;
-IF &CapType EQ '6' THEN CUROPT1;
-IF &CapType EQ '5' THEN ALLCAPS1;
-IF &CapType EQ '4' THEN CAPRANGE1;
-IF &CapType EQ '3' THEN PRECAPDT1;
-IF &CapType EQ '2' THEN POSTDEPS1;
-IF &CapType EQ '1' THEN EXACTCAP1;
-IF &CapType EQ '0' THEN NONDEPCP1;
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-IF &CapType EQ '10' THEN NONDEPCP11;
-*------------------------------------------------------------------------------------------------------------------------------------------
-* -------------------------------
-* Post departure Optmization comparison -- all opt cases plus the Day0 and Day-1 captures (CapType = 7)
-* (only for departures where a post departure capture record exists)
-ALLOPT1
AND (
(cd."CaptureDate" = cap."Departure_Detail_Capture_Date")
OR
(cap."Departure_Detail_Capture_Days_Left" <=0)
)
AND EXISTS (SELECT '1'
May 09, 2013, 12:12 AM
kumud.vaishand the following is the error I am getting:
Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.
-RUN
-*TO DISPLAY THE SQLS GENERATED
SET TRACEOFF = ALL
SET TRACEON=STMTRACE
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
-*SET TRACEOFF = ALL
-*SET TRACEON = STMTRACE//CLIENT
-*SET TRACEON = SQLAGGR//CLIENT
-*SET XRETRIEVAL = OFF
-*SET TRACEUSER = ON
-SET ECHO = ALL;
SET WEEKFIRST = 1
-*APP MAP FOCHOLD c:\ibi\WebFOCUS71\ibi_html\holdfiles
-*APP HOLD FOCHOLD
-*FILEDEF ADVHOLD c:\ibi\WebFOCUS71\ibi_html\holdfiles\ADVHOLD.FTM
-DEFAULT &Start_Date = '2005/01/01';
-DEFAULT &End_Date = '2005/01/01';
-DEFAULT &Depart_No = '0121';
-DEFAULT &ORG = FOC_NONE;
-DEFAULT &DEST = FOC_NONE;
-DEFAULT &Bus_Unit = FOC_NONE;
-DEFAULT &Analyst = FOC_NONE;
-DEFAULT &DOW = FOC_NONE;
-DEFAULT &Eqp_Code = 'FOC_NONE';
-DEFAULT &Resource = FOC_NONE;
-*DEFAULT &Equip_Code = FOC_NONE;
-DEFAULT &WFFMT = HTML;
-DEFAULT &City_Level = '0';
-DEFAULT &Dep_Level = '0';
-DEFAULT &Res_Level = '0';
-DEFAULT &Set_Level = '1';
-DEFAULT &Eqp_Level = '0';
-DEFAULT &Click_Val = '0';
-DEFAULT &Dow_Level = '0';
-*--------------------------Added by Kumud on 21st Mar,2012----------------
-DEFAULT &Time_Level = '0';
-DEFAULT &Capacity = '0';
-DEFAULT &Show_Traf = '0';
-*-------------------------------------------------------------------------
-DEFAULT &Dte_Level = '0';
-DEFAULT &Periods = '0';
-DEFAULT &ShowDmd = ' ';
-*---------------------------Added on 15th Jan, 2009----------------------------------------------------------------------------------------
-DEFAULT &Show_Vol= '0';
-DEFAULT &Show_Rev= '0';
-*------------------------------------------------------------------------------------------------------------------------------------------
-*-------------------09 DEC 2010-----------------------start------------------------------------------------------------
-DEFAULT &ShowAdjAuth = 1;
-SET &ShowAdjAu = IF 1 EQ '1' THEN ' ' ELSE 'NOPRINT';
-DEFAULT &ShowAdjAvail = 1;
-SET &ShowAdjAv = IF 1 EQ '1' THEN ' ' ELSE 'NOPRINT';
-SET &ShowProjBrd = 0;
-SET &ShowProjBr = IF 0 EQ '1' THEN ' ' ELSE 'NOPRINT';
-*---------------------------------------------------------------------------------------------------------------------------
-DEFAULT &YR_YR= '0';
-DEFAULT &Current_Capture = '2004/03/16';
-DEFAULT &Previous_Capture = '2005/08/26'
-DEFAULT &LegSeg_Type = 'Leg';
-DEFAULT &Show_Eqp = '0';
-DEFAULT &UseViewer = '0';
-DEFAULT &RTrip = '0';
-IF NE 1 THEN SKPVIEWER;
-SKPVIEWER
-*SET BUSDAYS = SMTWTFS
-*SET WEEKFIRST =1
-*---COMMON GLOBAL VARIABLES -------
-*Initialize Report Description variables
-*Report Header / Report Summary Description / Text for day of week / Report Footer
-SET &&HeaderTxt = '';
-SET &&sum_txt = '';
-SET &dow_txt = 'All';
-* &&Level - global (&&) used to determine report column output options
-* Setlevel = 0 for resource summary, 1 for class details, 2 = sum by equip code, 3= details by equip code
-SET &&Level = 1;
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIPEQP;
-SET &Eqp_Code='All';
-SKIPEQP
-SET &showeqp = IF 1 EQ 1 THEN ' ' ELSE 'NOPRINT';
-SET &&R_Trip = IF 1 EQ '1' THEN 1 ELSE 0;
-* Show parents global variable used for subclass aggregation - default to zero (off)
-SET &&Show_Parents =0;
-SKPLVL
-* ----------------------------------------
-* Handle report link selection (click_val)
-IF 0 EQ 0 THEN SKIPLINKS;
-SKIPLINKS
-* ----------------------------------------
-* YR_YR 0 - no prev dates
-* 1 - same departure dates for prev. capture
-* 2 - get prev dates that match same daysleft range
-SET &YR_YR = IF ((0 GT 0) AND (( EQ ' ') OR ( EQ '0')) ) THEN 0 ELSE 0;
-SET &Previous_Capture = IF 0 EQ 0 THEN ' ' ELSE ;
-* ----------------------------------------
-* -------- LOAD SET COMMANDS ------
-* Load Basic Set Commands from routine
-INCLUDE rptldset
-* *******************
-* RPTLDSET.fex
-* Report base class - load set commands
-* Used to define basic set commands shared by all reports
-* Author: Ken Hazel
-* ********************
-* Define sets date with set format in order to allow where clause to filter by date
-*SET WEEKFIRST = 1
-* --------------------------------
-* Parameter entry screen passes "FOC_NONE" when "ALL" selected from drop downs.
-* - Use of FOC_NONE seems to cause the line of code not to execute
-* - this does not pass correctly to drill down reports or for use in report headers.
-* - use "-Set" commands to pass 'FOC_NONE' values as "ALL" for drill downs & headers.
-*---------------------
-* --------------------------------------
-* ----- Routine for floating dates -----
-SET ECHO=ALL;
-SET &Out_Date = 'None';
-*--- Process Capture Date ---
-SET &Cp_Sub = SUBSTR(4,2012/10/24,1,4,4,'A4');
-IF (2012 GT 999) THEN SKIPCAP;
-SKIPCAP
-*--- Process Start Date ---
-* Check for numeric value passed for start date
-SET &St_Sub = SUBSTR(4,2012/10/24,1,4,4,'A4');
-IF (2012 GT 999) THEN SKIPSTART;
-SKIPSTART
-*--- Process End Date ---
-* Check for numeric value passed for start date
-SET &En_Sub = SUBSTR(4,2012/10/24,1,4,4,'A4');
-IF (2012 GT 999) THEN SKIPEND;
-SKIPEND
-* --------------------------------------
-SET &Org_City = 'All';
-SET &Dest_City = 'All';
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIP1;
-SET &Depart_No = 'All';
-SKIP1
-*IF &City_Pair NE 'FOC_NONE' THEN GOTO SKIP2;
-*SET &City_Pair = 'ALL';
-SKIP2
-IF 2012/10/24 NE 'FOC_NONE' THEN GOTO SKIP3;
-SKIP3
-IF 2012/10/24 NE 'FOC_NONE' THEN GOTO SKIP6;
-SKIP6
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIP7;
-SET &Bus_Unit = 'All';
-SKIP7
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIP8;
-SET &Analyst = 'All';
-SKIP8
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIP9;
-SET &Resource = 'All';
-SKIP9
-IF ALL NE 'FOC_NONE' THEN GOTO SKIP10;
-SKIP10
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIP11;
-SET &ORG = 'ALL';
-SKIP11
-IF FOC_NONE NE 'FOC_NONE' THEN GOTO SKIP14;
-SET &DEST = 'ALL';
-*IF &ClassOption NE 'FOC_NONE' THEN GOTO SKIP11;
-*SET &ClassOption = 'ALL';
-*SKIP11
-*IF &Set_Level NE 'FOC_NONE' THEN GOTO SKIP13;
-*SET &Set_Level = 'ALL';
-*SKIP12
-*IF &Current_Capture NE 'FOC_NONE' THEN GOTO ENDDOW;
-*SET &Current_Capture = 'ALL';
-*SKIP13
-*IF &ReportID NE 'FOC_NONE' THEN GOTO SKIP14;
-*SET &ReportID = 'ALL';
-SKIP14
-* --------------------------------
-*[WEEKDAY] -Translates DOW into day text
-SET &dow_txt = 'All';
-IF ALL NE 'Sunday' THEN Day2;
-Day2
-IF ALL NE 'Monday' THEN Day3;
-Day3
-IF ALL NE 'Tuesday' THEN Day4;
-Day4
-IF ALL NE 'Wednesday' THEN Day5;
-Day5
-IF ALL NE 'Thursday' THEN Day6;
-Day6
-IF ALL NE 'Friday' THEN Day7;
-Day7
-IF ALL NE 'Saturday' THEN ENDDOW;
-ENDDOW
-* --------------------------------
-* Lookup City codes for org and dest
-IF ALL EQ 'ALL' THEN SKIPORG;
-SKIPORG
-IF ALL EQ 'ALL' THEN SKIPDEST;
-SKIPDEST
-* --------------------------------
-* ----------------------------------------
-* default prev start / end (will work with ShowPrevDates =1 only)
-SET &Prev_Start = 2012/10/24;
-SET &Prev_End = 2012/10/24;
-* -------------------
-IF (0 NE '2') THEN SKIPHIST;
-SKIPHIST
-* ----------------------------------------
-* Set NewJoin parameters
-SET &GetHist = IF (0 EQ 0) THEN 0 ELSE 1;
-SET &&foot_txt = IF 0 EQ 0 THEN '' ELSE 'Historical Dates -- Capture:'| | ' Dep Range:' | 2012/10/24 | ' to ' | 2012/10/24;
-* Hide history display if no history report option selected
-SET &&ShowHst = IF (0 EQ 0) THEN 'NOPRINT' ELSE ' ' ;
-SET &&ShowDemand = IF ( 1 EQ 0) THEN 'NOPRINT' ELSE ' ' ;
-*SET &InclCls = IF &&Level GT 0 THEN '1' ELSE '0';
-SET &InclCls = IF 1 EQ 1 OR 1 EQ 3 THEN '1' ELSE '0';
-* get match of non departed capture records matching capture date used
-SET &CapType = '0';
-SET &InclPDep = '0';
-SET &InclOpt = '0';
-SET &CDtlType = '1';
-SET &SortOrd = '1';
-*--------------------------------Added by Kumud on 25th Jun 2012 for KPI columns addition ---------------------------------
-*SET &Capacity = '0';
-*SET &Show_Traf = '1';
-*--------------------------------------------------------------------------------------------------------------------------
-* ----------------------------------------
-*INCLUDE newjoin
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-SET ECHO = ALL;
-DEFAULT &CapType ='0';
-DEFAULT &InclPDep ='0';
-DEFAULT &InclOpt ='1';
-DEFAULT &InclCls ='1';
-DEFAULT &CDtlType ='1';
-DEFAULT &SortOrd ='1';
-DEFAULT &Bus_Unit = 'FOC_NONE';
-DEFAULT &Depart_No = '0121';
-DEFAULT &ORG = 'FOC_NONE';
-DEFAULT &DEST = 'FOC_NONE';
-DEFAULT &DOW = 'FOC_NONE';
-*--------------------------Added by Kumud on 21st Mar,2012----------------
-*DEFAULT &Time = 'FOC_NONE';
-*SET &Time = IF ((&Time EQ 'ALL') OR (&Time EQ 'ALL')) THEN 'FOC_NONE' ELSE &Time;
-*-------------------------------------------------------------------------
-DEFAULT &Resource = 'FOC_NONE';
-DEFAULT &Eqp_Code = 'FOC_NONE';
-DEFAULT &Start_Date = '2006/04/02';
-DEFAULT &End_Date = '2006/04/02';
-DEFAULT &Current_Capture= '2006/04/02'
-DEFAULT &Previous_Capture = 'FOC_NONE';
-DEFAULT &Prev_Start ='FOC_NONE';
-DEFAULT &Prev_End ='FOC_NONE';
-IF 0 EQ 6 THEN GOTO EXITC;
-SET &Capture2='2004/03/16';
-EXITC
-* ------ PREPARE PRIMARY WHERE VARIABLES ------
-* ------ Remove spaces from departure code variable (or set to FOC_NONE)
-* [NOTE: FOC_NONE causes code line to be ignored (used in where clause in mstrjoin.fex)]
-*-------------------------------Added on 7th Dec, 2009---------------------------------------------------------
-SET &Analyst1 = IF ((All EQ 'All') OR (All EQ 'FOC_NONE')) THEN 'FOC_NONE' ELSE All;
-*---------------------------------------------------------------------------------------------------------------
-IF (All EQ 'ALL') OR (All EQ 'All') OR (All EQ 'FOC_NONE') THEN GOTO ALLDEP;
-ALLDEP
-SET &Dep_No = 'FOC_NONE';
-ENDDEP
-* --- Prepare remaining variables
-SET &DAYOFWK = IF((ALL EQ 'ALL') OR (ALL EQ 'All')) THEN 'FOC_NONE' ELSE ALL;
-SET &BU = IF ((All EQ 'ALL') OR (All EQ 'All')) THEN 'FOC_NONE' ELSE All ;
-SET &Res = IF ((All EQ 'ALL') OR (All EQ 'All')) THEN 'FOC_NONE' ELSE All;
-* -- Org and Dest values passed are ID values(not codes)
-SET &Origin = IF ((ALL EQ 'ALL') OR (ALL EQ 'All')) THEN 'FOC_NONE' ELSE ALL;
-SET &Destin = IF ((ALL EQ 'ALL') OR (ALL EQ 'All')) THEN 'FOC_NONE' ELSE ALL;
-SET &E_Code = IF ((All EQ 'ALL') OR (All EQ 'All')) THEN 'FOC_NONE' ELSE All;
-*SET &DTime = IF ((&Time EQ 'ALL') OR (&Time EQ 'ALL')) THEN 'FOC_NONE' ELSE &Time;
-* ------ SET MSTRJOIN PROCESS CONTROLLING VARIABLES ------
-SET &CDtlType ='0';
-SET &B_Date = 2012/10/24;
-SET &E_Date = 2012/10/24;
-SET &Capture1 = 2012/10/24;
-*---- For old report variables
-*IF &PostDepData EQ MISSING THEN SKPPDDATA;
-*SET &InclOpt = '0';
-*SET &InclPDep = IF &PostDepData =EQ 4 THEN '1' ELSE '0';
-*SET &CapType = IF (&PostDepData EQ 4 OR &PostDepData EQ 1) THEN '2' ELSE IF &PostDepData EQ 2 THEN '5' ELSE IF &PostDepData EQ 3 THEN '9' ELSE '0';
-*SKPPDDATA
-*IF &ShowPrevDates EQ MISSING THEN SKPPREV;
-*SET &GetHist = &ShowPrevDates;
-*SKPPREV
-*IF &GetClsData EQ MISSING THEN SKPPREV;
-*SET &InclCls = &GetClsData;
-*SET &SortOrd = &GetClsData;
-*SKPCLSDATA
-*---------------------------Added on 18th Aug, 2009----------------------------------------------------------------------------------------
-SET &Hist_Adv = IF 2012/10/24 LT 2012/10/24 THEN '0' ELSE '1';
-*------------------------------------------------------------------------------------------------------------------------------------------
-* ------- SQL CODE START -------
-*-SET &&IBIAPP_app=&IBIAPPapp;
ENGINE SQLMSS SET DEFAULT_CONNECTION rtspo
SQL SQLMSS PREPARE SQLOUT FOR
-* --------------------------------
-* --- 1st call (get current and compartment details (& class details if InclCls =1)
-*INCLUDE MSTRJOIN.FEX
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* MSTRJOIN.FEX - SQL COLUMNS (SQL PASSTHROUGH) called from primary join file NEWJOIN.FEX
-* 03/30/2007 -KH
-*
-* SPECIAL HANDLING OF CLASSES AND SUBCLASSES
-* Because joining of subclasses does not produce 1 record per class + 1 record per subclass
-* it is necessary to join two separate SQL queries into a single output (UNION ALL). This routine can be called multiple
-* Times using parameters to accomplish this. For this reason the data structures for each call be identical.
-* This is why class header info should be added per call.Reports should treat subclasses as class output.
-* Sorting within the hierarchy is optional and controlled by parameters
-*
-* PARAMETERS
-* InclPDep - Include leg post departure data in output
-* InclOpt - Include optimization details data
-* Capture1 - Capture Date Filter (required)
-* Capture2 - (optional) for capture filter date range end
-* CapType - Capture Date Range Options
-* '0' Non-Departed Captures
-* '1' Return capture records that exaclty match the current capture date
-* '2' Return post departure capture record (< current post and DL = -1 )
-* '3' Return captures that occur on or before the capture date (dups?)
-* '4' Return captures by range of capture dates (Capture1 & Capture2)
-* '5' Return all captures
-* -- Opt Cases Only --
-* '6' to return only optimized case captures for the given current capture date
-* '7' to return Post Departure Optmized case captures, and Day0, Day-1 captures for optimized cases
-* (post departure only [days left -1 capture exists])
-* -- Special --
-* '9' Exit without applying any departure OR capture date values to where clause
-*
-*
-* InclCls - Include class/subclass data in output or resource values only
-* CDtlType - '0' to return class detail data in output
-* '1' to return subclass detail data in output as class details
-* NOTE: subclass and class detail fields use same names, each returns one or the other
-* SortOrd - '0' = do not calculate sort order
-* - '1' = Include Subclasses in hierarchy order
-* Class value -> Multiply each parent class order by 100
-* Subclasses -> Add subclass_order value to calculated parent class value (above)
-* - '2' = Add Subclasses to bottom of hierarchy
-* Class value -> Remains class order value
-* Subclasses -> Add 100 to subclass_order value
-* &B_Date / &E_Date - start and end date ranges (calling routine uses nested unions to incorporate historical range records)
-*
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* --- SELECT STATEMENT ---
SELECT
bu."BU_Id"
, dep."Departure_Id", dep."Departure_Code", dep."Departure_OriginalDate"
, DATENAME(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW', DATEPART(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW1'
,'' AS "Last_Name"
, dtl."Departure_Detail_Id", dtl."Departure_Detail_Leg_Seg_Type"
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
,dtl."Departure_Detail_Departure_OriginalTime" AS 'DEP_ORGTIME'
,cpt."No_show_percentage"/100 AS 'CPT_NOSHOWPER'
-IF (1 NE '0') THEN ENDCAPA1;
-ENDCAPA1
-IF (1 NE '1') THEN ENDCAPB1;
,leg."Leg_Compartment_Detail_Physical_Capacity" AS 'DEP_CAPACITY'
-GOTO CALSKP;
-CALSKP
-*--------------------------------------------------------------------------------------------------------------------------------------
, eqp."Equipment_Code"
, cpr."CityPair_Name"
, cap."Departure_Detail_Capture_Date"
, cap."Departure_Detail_Capture_Days_Left"
, cpt."DDCC_ID",
cpt."COMPARTMENT_CODE", cpt."COMPARTMENT_ORDER",
cpt."CURRENT_AUTHORIZATION" AS 'CPT_CURRENT_AUTHORIZATION',
cpt."ADJUSTED_AUTHORIZATION" AS 'CPT_ADJUSTED_AUTHORIZATION',
cpt."CURRENT_AVAILABLITY" AS 'CPT_CURRENT_AVAILABLITY',
cpt."ADJUSTED_AVAILABLITY" AS 'CPT_ADJUSTED_AVAILABLITY',
cpt."PROJECTED_BOOKING" AS 'CPT_PROJECTED_BOOKING',
cpt."CURRENT_BOOKING" AS 'CPT_CURRENT_BOOKING',
cpt."GROUP_BOOKING" AS 'CPT_GROUP_BOOKING',
cpt."TOTAL_DEMAND" AS 'CPT_TOTAL_DEMAND',
cpt."REMAINING_DEMAND" AS 'CPT_REMAINING_DEMAND',
ISNULL(cpt."PROJECTED_REVENUE",tmp."PROJECTED_REVENUE") AS 'CPT_PROJECTED_REVENUE',
cpt."WAITLIST" AS 'CPT_WAITLIST',
cpt."REMAINING_CANCELLATION" AS 'CPT_REMAINING_CANCELLATION'
,cpt."VOLUME_TARGET" AS 'CPT_VOLUME_TARGET',
cpt."REVENUE_TARGET" AS 'CPT_REVENUE_TARGET',
cpt."PROJECTED_BOARDING" AS 'CPT_PROJECTED_BOARDING'
-IF (0 NE '1') THEN ENDPDP;
-ENDPDP
-IF (0 EQ '0') THEN ENDOPT;
-ENDOPT
-IF (1 EQ '0') THEN ENDSELECT;
,
(0) AS "IS_SUBCLS",
cls."DDCCLS_ID",
cls."CLASS_CODE" AS "CLS_CODE",
cls."CLASS_ORDER" AS "CLS_ORDER",
cst."CLASS_STRUCTURE_IS_SUMMARY"
-IF (0 EQ '1')THEN GETSUBCLS;
,cls."CURRENT_AUTHORIZATION" ,
cls."CURRENT_AVAILABLITY" ,
cls."ADJUSTED_AUTHORIZATION" ,
cls."ADJUSTED_AVAILABLITY" ,
cls."PROJECTED_BOOKING" ,
cls."CURRENT_BOOKING" ,
cls."GROUP_BOOKING" ,
cls."TOTAL_DEMAND" ,
cls."REMAINING_DEMAND" ,
cls."PROJECTED_REVENUE" ,
cls."WAITLIST" ,
cls."CLASS_CODE" ,
cls."PROJECTED_BOARDING",
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
cls."No_show_percentage"/100 AS 'NOSHOWPER',
-IF (1 NE '0') THEN ENDCLSA1;
-ENDCLSA1
-IF (1 NE '1') THEN ENDCLSB1;
leg."Leg_Compartment_Detail_Authorized_Capacity" AS 'DEP_CAPACITY',
-GOTO CLSSKP;
-CLSSKP
-*--------------------------------------------------------------------------------------------------------------------------------------
-IF (1 = '1') THEN CALCORD;
-CALCORD
(cls."CLASS_ORDER" * 100) AS "CLASS_ORDER"
-ENDCORD
-GOTO ENDSELECT;
-ENDSELECT
FROM
RTS_PO.DBO.BUSINESS_UNITS(NOLOCK) bu
JOIN RTS_PO.DBO.DEPARTURE(NOLOCK) dep
ON bu."BU_ID" = dep."DEPARTURE_BUSINESS_UNIT_ID_FK"
-*----------------------------------------------------Added on 7th Dec, 2009--------------------------------------------
JOIN RTS_PO.DBO.DEPARTURE_DETAILS(NOLOCK) dtl
ON dep."DEPARTURE_ID" = dtl."DEPARTURE_DETAIL_DEPARTURE_ID_FK"
JOIN RTS_PO.DBO.ROUTE_LEG_SEGMENT(NOLOCK) lsg
ON dtl."DEPARTURE_DETAIL_ROUTE_LEG_SEGMENT_ID_FK" = lsg."ROUTE_LEG_SEGMENT_ID"
JOIN RTS_PO.DBO.CITY_PAIRS(NOLOCK) cpr
ON lsg."ROUTE_LEG_SEGMENT_CITYPAIR_ID_FK" = cpr."CITYPAIR_ID"
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURES(NOLOCK) cap
ON dtl."DEPARTURE_DETAIL_ID" = cap."DEPARTURE_DETAIL_CAPTURE_DEPATURE_DETAILS_ID_FK"
-*----------------------------------------------------Added on 18th Feb, 2010--------------------------------------------
-IF FOC_NONE EQ 'FOC_NONE' THEN GOTO PANA0;
-PANA0
JOIN RTS_PO.DBO.Equipments(NOLOCK) eqp
ON cap."Departure_Detail_Capture_Equipment_Id_fk" = eqp."Equipment_Id"
-*----------------------------------------------------------------------------------------------------------------------
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURE_COMPARTMENTS(NOLOCK) cpt
ON cap."DEPARTURE_DETAIL_CAPTURE_ID" = cpt."DDCC_DEPARTURE_DETAIL_CAPTURE_ID_FK"
LEFT JOIN RTS_PO.DBO."LEG_COMPARTMENT_DETAILS"(NOLOCK) leg
ON cpt."DDCC_ID" = leg."DDCC_ID_FK"
JOIN (SELECT DDCCls_DDCC_Id_fk ,
SUM(CASE WHEN Class_Structure_Is_Summary = 0 THEN ISNULL(CLS.Projected_Booking,CLS.Current_Booking) * CLS.CurrentFareValue
ELSE ISNULL(DDCScls.Projected_Booking,DDCScls.Current_Booking) * DDCScls.CurrentFareValue END) AS PROJECTED_REVENUE
FROM RTS_PO.DBO.Departure_Detail_Capture_Classes CLS (NOLOCK)
JOIN RTS_PO.DBO.class_structures Cs
ON Cs.Class_Structure_Id = CLS.DDCCls_Class_Structure_Id_fk
LEFT JOIN RTS_PO.DBO.Departure_Detail_Capture_Subclasses DDCScls (NOLOCK)
On CLS.DDCCls_Id=DDCScls.DDCSCls_DDCCls_Id_fk
GROUP BY DDCCls_DDCC_Id_fk) Tmp
ON cpt."DDCC_ID"=Tmp."DDCCls_DDCC_Id_fk"
-* Include Post Departure Details (left join)
-IF (0 NE '1') THEN SKPPD;
-SKPPD
-*INCLUDE Optimization Case Details (left join)
-IF (0 EQ '0') THEN SKPOPT;
-SKPOPT
-IF (1 EQ '0') THEN SKIPCLASSES;
JOIN RTS_PO.DBO.DEPARTURE_DETAIL_CAPTURE_CLASSES(NOLOCK) cls
ON cpt."DDCC_ID" = cls."DDCCLS_DDCC_ID_FK"
LEFT OUTER JOIN RTS_PO.DBO.CLASS_STRUCTURES(NOLOCK) cst
ON cls."DDCCLS_CLASS_STRUCTURE_ID_FK"= cst."CLASS_STRUCTURE_ID"
-* Join to obtain subclasses only
-IF (0 EQ '0') THEN SKIPCLASSES;
-SKIPCLASSES
-* ------------------------------------
-* ***** SQL WHERE CLAUSE *****
-* set 1st where clause line to always appear. FOC_NONE causes each line to be skipped where included. Each AND adds to initial where
WHERE (1 = 1)
AND (bu."BU_ID" = FOC_NONE)
AND (dep."Departure_Code" = 'FOC_NONE')
-*-------------------------------Added on 7th Dec, 2009---------------------------------------------------------
-IF 1 NE 1 THEN SKPRTRIP;
AND ((cpr."Origination_City_Id_fk" = 'FOC_NONE') OR (cpr."Origination_City_Id_fk" = 'FOC_NONE'))
AND ((cpr."Destination_City_Id_fk" = 'FOC_NONE') OR (cpr."Destination_City_Id_fk" = 'FOC_NONE'))
-GOTO ENDOD;
-ENDOD
AND (DATENAME(weekday, dep."Departure_OriginalDate") = 'FOC_NONE')
AND (eqp."Equipment_Code" = 'FOC_NONE')
AND (cpt."COMPARTMENT_CODE" = 'FOC_NONE')
AND (dtl."DEPARTURE_DETAIL_LEG_SEG_TYPE" = 'Leg')
-* ------- Exit w/o ending sql or applying any date ranges to where clause
-IF 0 EQ '9' THEN ENDSQL;
-* --------------------------------------------------------
-* -- add filter to show only parent classes where selected
-IF 0 NE '0' THEN SHOWPRNTS;
-IF (1 EQ '0') THEN SKIPPRNTS;
-* -- hide parents [show all subclasses and only classes that are not summaries]
-*-*AND (((&CDtlType) = '1') OR (cst."CLASS_STRUCTURE_IS_SUMMARY" = '0'))
AND (cst."CLASS_STRUCTURE_IS_SUMMARY" = '0')
-GOTO SKIPPRNTS;
-SKIPPRNTS
-*---------------------------------------------------------
-* --- Apply departure date range filter ---
AND ((dep."Departure_OriginalDate" >= '2012/10/24') AND (dep."Departure_OriginalDate" <= '2012/10/24'))
-* ------------ Capture Date Filters ------------
-*IF &CapType EQ '8' THEN ENDDATES;
-IF 0 EQ '7' THEN ALLOPT;
-IF 0 EQ '6' THEN CUROPT;
-IF 0 EQ '5' THEN ALLCAPS;
-IF 0 EQ '4' THEN CAPRANGE;
-IF 0 EQ '3' THEN PRECAPDT;
-IF 0 EQ '2' THEN POSTDEPS;
-IF 0 EQ '1' THEN EXACTCAP;
-IF 0 EQ '0' THEN NONDEPCP;
-NONDEPCP
AND ((cap."Departure_Detail_Capture_Date" = '2012/10/24') AND (cap."Departure_Detail_Capture_Days_Left" >=0))
-GOTO ENDDATES;
-ENDDATES
-ENDSQL
-IF 1 EQ 0 THEN ENDCURRENT;
-* --- 2nd call to merge with subclasses if class/sublclass detail output selected (InclCls=1)
UNION ALL
-SET &CDtlType ='1';
-*INCLUDE MSTRJOIN.FEX
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* MSTRJOIN.FEX - SQL COLUMNS (SQL PASSTHROUGH) called from primary join file NEWJOIN.FEX
-* 03/30/2007 -KH
-*
-* SPECIAL HANDLING OF CLASSES AND SUBCLASSES
-* Because joining of subclasses does not produce 1 record per class + 1 record per subclass
-* it is necessary to join two separate SQL queries into a single output (UNION ALL). This routine can be called multiple
-* Times using parameters to accomplish this. For this reason the data structures for each call be identical.
-* This is why class header info should be added per call.Reports should treat subclasses as class output.
-* Sorting within the hierarchy is optional and controlled by parameters
-*
-* PARAMETERS
-* InclPDep - Include leg post departure data in output
-* InclOpt - Include optimization details data
-* Capture1 - Capture Date Filter (required)
-* Capture2 - (optional) for capture filter date range end
-* CapType - Capture Date Range Options
-* '0' Non-Departed Captures
-* '1' Return capture records that exaclty match the current capture date
-* '2' Return post departure capture record (< current post and DL = -1 )
-* '3' Return captures that occur on or before the capture date (dups?)
-* '4' Return captures by range of capture dates (Capture1 & Capture2)
-* '5' Return all captures
-* -- Opt Cases Only --
-* '6' to return only optimized case captures for the given current capture date
-* '7' to return Post Departure Optmized case captures, and Day0, Day-1 captures for optimized cases
-* (post departure only [days left -1 capture exists])
-* -- Special --
-* '9' Exit without applying any departure OR capture date values to where clause
-*
-*
-* InclCls - Include class/subclass data in output or resource values only
-* CDtlType - '0' to return class detail data in output
-* '1' to return subclass detail data in output as class details
-* NOTE: subclass and class detail fields use same names, each returns one or the other
-* SortOrd - '0' = do not calculate sort order
-* - '1' = Include Subclasses in hierarchy order
-* Class value -> Multiply each parent class order by 100
-* Subclasses -> Add subclass_order value to calculated parent class value (above)
-* - '2' = Add Subclasses to bottom of hierarchy
-* Class value -> Remains class order value
-* Subclasses -> Add 100 to subclass_order value
-* &B_Date / &E_Date - start and end date ranges (calling routine uses nested unions to incorporate historical range records)
-*
-* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-* --- SELECT STATEMENT ---
SELECT
bu."BU_Id"
, dep."Departure_Id", dep."Departure_Code", dep."Departure_OriginalDate"
, DATENAME(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW', DATEPART(WEEKDAY, dep."Departure_OriginalDate") AS 'DEP_DOW1'
, '' AS "Last_Name"
, dtl."Departure_Detail_Id", dtl."Departure_Detail_Leg_Seg_Type"
-*--------------------------Added by Kumud on 21st Mar,2012-----------------------------------------------------------------------------
,dtl."Departure_Detail_Departure_OriginalTime" AS 'DEP_ORGTIME'
,cpt."No_show_percentage"/100 AS 'CPT_NOSHOWPER'
-IF (1 NE '0') THEN ENDCAPA3;
0 ERROR AT OR NEAR LINE 743 IN PROCEDURE ADVBKRPTFOCEXEC *
(FOC305) SPECIFIED LABEL NOT FOUND: ENDCAPA3