Scribd Logo
Upload

Welcome to Scribd!

  • 7 views

    V FactTicket Fabri

    Uploaded by

    waqar
    its important

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    V FactTicket Fabri

    Uploaded by

    waqar
    7 views3 pages
    its important

    Original Title

    V_FactTicket_Fabri

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    its important

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    7 views3 pages

    V FactTicket Fabri

    Uploaded by

    waqar
    its important

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 3

    /****** Object: View [CBI_TABLES].

    [V_FactTicket_Fabric] Script Date: 10/24/2024


    12:04:10 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [CBI_TABLES].[V_FactTicket_Fabric] AS SELECT


    /****** Script for SelectTopNRows command from SSMS ******/

    [FactTicketKey]
    -- ,[NaturalKey]
    [TicketNumber]
    ,[amadeusBookingRlocID]
    -- ,[bookingOriginalRlocID]
    --,[dateOfIssue]
    ,[DateOfIssueKey]
    ,[DimPassengerTypeKey]
    ,[isRevenuePassenger]
    ,[isStudent]
    ,[DimTicketRAEventKey]
    ,[passengerLastName]
    ,[passengerFirstName]
    ,[provisionalFinalIndicator]
    ,[DomIntIndicator]
    ,[DimTicketStatusKey]
    ,[dimTourcodeKey]
    ,[DimTicketTransactionCodeKey]
    ,[DimAirportKey_TrueOrigin]
    ,[DimAirportKey_TrueDestination]
    ,[DimAirlineCarrierKey_ValidatingCarrier]
    ,[DimTicketTransactionTypeKey]
    -- ,[isdeleted]
    ,[SourceSystemCode]
    ,[TicketSourceSystemPk]
    ,[Tkt_ORC_Amt]
    ,[Refunded_Tkt_ORC_Amt]
    ,[Tkt_Net_Acct_Fare_Amt]
    ,[Refunded_Tkt_Net_Acct_Fare_Amt]
    ,[Tkt_Published_Acct_Fare_Amt]
    ,[Refunded_Tkt_Published_Acct_Fare_Amt]
    ,[Tkt_Selling_Acct_Fare_Amt]
    ,[Refunded_Tkt_Selling_Acct_Fare_Amt]
    ,[Tkt_COAM_Commission_Acct_Amt]
    ,[Refunded_Tkt_COAM_Commission_Acct_Amt]
    ,[Tkt_SPAM_Commission_Acct_Amt]
    ,[Refunded_Tkt_SPAM_Commission_Acct_Amt]
    ,[Tkt_Fee_Acct_Amt]
    ,[Refunded_Tkt_Fee_Acct_Amt]
    ,[Tkt_Tax_Acct_Amt]
    ,[Refunded_Tkt_Tax_Acct_Amt]
    ,[Tkt_Fee_Count]
    ,[Refunded_Tkt_Fee_Count]
    ,[Tkt_Tax_Count]
    ,[Refunded_Tkt_Tax_Count]
    ,[Total_Ticket_Count]
    ,[Refunded_Total_Ticket_Count]
    -- ,[RecordCreatedTimestamp]
    ,[RecordUpdatedTimestamp]
    ,coalesce ([RecordUpdatedTimestamp],[RecordcreatedTimestamp]) as
    RecordUpdatedDate
    ,[DimTicketEMDOfficeKey]
    , cA.customerAccountNumber
    ,pymnt.PaymentType
    ,pymnt.PaymentCode

    --,concat( datepart(year, a.dateOfIssue),datepart(month, a.dateOfIssue),'-',


    b.AirportCode ,'-',c.Airportcode ) as PosTargetKey
    FROM [CBI_TABLES].[FactTicket] a
    --left join [CBI_TABLES].[V_DimAirport] b on a.DimAirportKey_TrueOrigin
    =b.dimairportkey
    --left join [CBI_TABLES].[V_DimAirport] c on a.DimAirportKey_TrueDestination
    =c.dimairportkey

    left join (
    select distinct t.Sourcesystempk , customerAccountNumber from [dbo].
    [TicketEMDOrder] t

    inner join (
    select TKT_EMD_OrderHGNK , substring(tfop.customerAccountNumber, patindex('%
    [^0]%',tfop.customerAccountNumber), 100) as customerAccountNumber
    FROM [dbo].[TICKETEMDFORMOFPAYMENT] tfop
    where tfop.currentrecordindicator='Y'
    and tfop.isdeleted =0
    and tfop.ordertype ='TKT'
    and customerAccountNumber is not null) CustAcct on
    CustAcct.TKT_EMD_OrderHGNK=t.hashguid

    where
    --dateofissue > '2020-01-01 00:00:00.000'
    t.OrderType = 'TKT'
    and t.currentrecordindicator = 'Y'
    and t.isdeleted=0
    ) cA on cA.Sourcesystempk = A.TicketSourceSystemPK

    left join (
    select distinct t.Sourcesystempk , PaymentType, PaymentCode from [dbo].
    [TicketEMDOrder] t

    inner join (
    select TKT_EMD_OrderHGNK , PaymentType, PaymentCode
    FROM [dbo].[TICKETEMDFORMOFPAYMENT] tfop
    where tfop.currentrecordindicator='Y'
    and tfop.isdeleted =0
    and tfop.ordertype ='TKT'
    AND (paymentcode like 'GOV%' or paymentcode like 'GR%')) pType on
    pType.TKT_EMD_OrderHGNK=t.hashguid

    where
    --dateofissue > '2020-01-01 00:00:00.000'
    t.OrderType = 'TKT'
    and t.currentrecordindicator = 'Y'
    and t.isdeleted=0
    ) pymnt on pymnt.Sourcesystempk = A.TicketSourceSystemPK;
    GO

    You might also like