sqldump7.sql

return

update reportsetup set PrinterName = 'E:\FCS HUB\Output'
update outputqueue set datetimecreated = '20290000000000'

-- sup-947

    select *, 
    coalesce(
        ( 
            select Value 
            from reportformatparamvalues 
            where reportid = 184 
            and paramname = reportformatparams.name 
            and servicecode = 'DA1' 
            and filename = reportformatparams.filename
        ),''
    ) CurrentValue, 
    coalesce(
        ( 
            select Value
            from reportformatparamvalues 
            where reportid = 184 
            and paramname = reportformatparams.name 
            and servicecode = '' 
            and filename = reportformatparams.filename
        ),''
    ) CurrentValueAMC 
    from 
    reportformatparams 
    where filename = 'GADemail_NXP.xsl' 
    and name like 'Svc%' 
    order by name

    select * from ReportFormatParams, ReportFormatParamValues
    where ReportFormatParamValues.ParamName = ReportFormatParams.Name
    and ReportFormatParamValues.ReportID = 184
    and ReportFormatParams.FileName = 'GADemail_NXP.xsl'
    and ReportFormatParams.Name like 'Svc%'
    order by ReportFormatParams.FileName, ReportFormatParams.Name


-- sup-920 huawei mb dim code

    select top 10 dimensioncode, weight, *
    from pallet 
    where DateTimeDespatched=''
    and singlehawbpiece = 1
    and piecenumber > 0 
    order by datetimecreated desc

    select * from dimensions

    delete from dimensions where fcstype = 925
    insert into Dimensions
    (customercode, dimensioncode, length, width, height, weight, fcstype)
    values
    ('dell', 'abc', 10, 20, 30, -1, 925)

    insert into Dimensions
    (customercode, dimensioncode, length, width, height, weight, fcstype)
    values
    ('dell', 'def', 10, 20, 30, -1, 925)

    update customer set DimMotherboxes = 1 where customercode = 'dell'

    select dimensioncode, weight, * from pallet

    update customerservice set MBPromptDimCode = 1

    update pallet set weight = 0, dimensioncode = '' where id = 'MBAMS03CEA'

    select 
    (select statuscode from orders where ordernumber = box.ordernumber),
    * from box where 
    palletid = '' and customercode = 'dell'
    and datetimescannedout = ''
    and statuscode = 20

    select * from pallet where id = 'mbams03cf2'


-- sup-456

    select *, (select reportname from reportsetup where id = reportsetupid)
    from outputqueue
    where reference = '3146647346'
    order by id

    delete from outputqueue where id > 15491483
    update outputqueue set statuscode = 6000 where id = 15491479
    
    select * from userreports where fcsref = '3146647346'

    select * from ReportFormatParamValues where reportid = 186

    select * from hawb where hawbnumber = '3146647346'


-- sup-523

    select field1, field15, field16, field17 
    from customdata 
    where tableid = 100 and field1 = 'ARW-CHRW-HKG1'

    update customdata 
    set field16 = 'DG', field17 = 'EXT'
    where tableid = 100 and field1 = 'ARW-CHRW-HKG1'

    select * from orders where ordernumber = 'HK3601101'

    select * from ProcessQueue, ProcessQueueItems
    where ProcessQueueItems.ProcessQueueID = ProcessQueue.ID
    and ProcessQueueItems.Reference = 'HK3601101'

    update ProcessQueue set StatusCode = 5010 where ID = 258021

    select * from processqueueitems where reference = 'HK3601101'


-- sup-478

    select top 10 * from outputqueue where reference = '7NL7930' order by datetimecreated desc
    update outputqueue set statuscode = 6010 where statuscode <> 6010
    update outputqueue set statuscode = 6000 where id = 16277653

    select top 10 * from outputqueue where dependencies <> ''
    select * from userreports order by datetimecreated desc

    select * from hawb where hawbnumber = '7NL7927'

    select outboundhawbnumber from orders where ordernumber in ('HK3707276', 'HK3722572')

    select * from processqueue where id in 
    (
        select processqueueid from processqueueitems where reference in ('HK3707276', 'HK3722572')
    )

    update processqueue set statuscode = 5010 where id = 258058


-- bmw security scan

    update customer set BoxFlagSecure = 30, BoxFlagUnsecure = 40
    update boxflags set ShipmentFlagNone = 20, ShipmentFlagSome = 20, ShipmentFlagAll = 10  where id = 30
    update boxflags set ShipmentFlagNone = 0,  ShipmentFlagSome = 20, ShipmentFlagAll = 20 where id = 40

    truncate table securitycompany
    insert into securitycompany (customercode, name, sequence) values ('BMW', 'Securicor', 1)
    insert into securitycompany (customercode, name, sequence) values ('BMW', 'Group 4', 2)

    truncate table securitymethod
    insert into securitymethod (customercode, name, sequence) values ('BMW', 'Dog', 1)
    insert into securitymethod (customercode, name, sequence) values ('BMW', 'X-ray', 2)
    insert into securitymethod (customercode, name, sequence) values ('BMW', 'Examination', 2)

    -- bmw despatched "3006136329" "190 204088 3246" "190 204089 5808" 

    select boxnumber, flags, flagsindicators, ordernumber, outboundhawb from box where outboundhawb='6TL9823' order by flags
    select * from securityhistory where latest = 1 order by id desc

    update outputqueue set statuscode = 6000 where id = 10820795

    select count(*) total,  sum( case when (flags & 2) != 0 then 1 else 0 end ) withflag30, sum( case when (flags & 4) != 0 then 1 else 0 end ) withflag40 from box where customercode='BMW' and outboundhawb='6TL9823'

    select boxnumber, flags, flagsindicators from box where ordernumber = '3007513903'
    select ordernumber, flags, flagsindicators from orders where ordernumber = '3007513903'

    select boxnumber, flags, flagsindicators from box where ordernumber = '3007491102'
    select ordernumber, flags, flagsindicators from orders where ordernumber = '3007491102'

    delete from box where ordernumber = '3007578960'
    delete from orders where ordernumber = '3007578960'

    select boxnumber, flags, flagsindicators from box where ordernumber = '3007578960'
    select ordernumber, flags, flagsindicators from orders where ordernumber = '3007578960'


-- sup-693

    -- nex ser

    select 
        MAWB.CustomerCode,
        MAWB.Number Manifest,
        MAWB.DestinationCountry Country,
        count(distinct HAWB.HAWBNumber) HAWBs, 
        count(distinct Orders.OrderNumber) Orders,
        count(distinct Box.BoxNumber) Boxes,
        count(distinct ILP.ProductIDEndUser) DifferentHSCodes,
        count(distinct ILP.CountryOfOrigin) DifferentCOO
    from MAWB, HAWB, Orders, Box, InvoiceLineProduct ILP
    where HAWB.CarrierMAWBNumber = MAWB.Number
    and Orders.OutboundHAWBNumber = HAWB.HAWBNumber
    and ILP.OrderNumber = Orders.OrderNumber
    and Box.OrderNumber = Orders.OrderNumber
    and MAWB.DestinationCountry in (select countrycode from country where EUCountry = 1)
    group by MAWB.CustomerCode, MAWB.Number, MAWB.DestinationCountry
    having 
        count(distinct ILP.ProductIDEndUser)
        >
        count(distinct Orders.OrderNumber)
    order by 
        count(distinct HAWB.HAWBNumber), 
        count(distinct Orders.OrderNumber),
        count(distinct ILP.ProductIDEndUser)

    update outputqueue set statuscode = 6030 where statuscode < 6030

    insert into outputqueue
    (datetimecreated, customercode, reportid, entity, reference, statuscode)
    values
    ('20290000000000', 'NXP', 'm2despatched', 'manifest', '180406WP3FUA', 6000)

    insert into outputqueue
    (datetimecreated, customercode, reportid, entity, reference, statuscode)
    values
    ('20290000000000', 'NXP', 'm2despatched', 'manifest', '180411WP3', 6000)

    insert into outputqueue
    (datetimecreated, customercode, reportid, entity, reference, statuscode)
    values
    ('20290000000000', 'NXP', 'm2despatched', 'manifest', '180519WP3FUT', 6000)


    select top 50 * from OutputQueue order by id desc


-- dev-1375

    -- nex ser
    select * from outputqueue where reportid = 'm1created' and statuscode < 6030 order by id desc

    insert into outputqueue
    (datetimecreated, customercode, reportid, entity, reference, statuscode)
    values
    ('20290000000000', 'NXP', 'm1created', 'hawb', 'XSN000012376', 6000)

    insert into outputqueue
    (datetimecreated, customercode, reportid, entity, reference, statuscode, reportsetupid)
    values
    ('20290000000000', 'NXP', 'm1created', 'hawb', 'XSN000012376', 6000, 175)


-- sup-727

    -- dhlctl

    -- old
    update CustomerService set RateCalcHAWB = 0, AutoRatingRequired = 1
    update HAWB set ExportProcessingComplete = 0 where CarrierReference in ('2521673291')

    -- new
    update CustomerService set RateCalcHAWB = 1, AutoRatingRequired = 0
    update HAWB set ExportProcessingComplete = 0 where CarrierReference in ('2521674691')


-- sup-913

    -- dhlctl

    select * from processqueueitems, processqueue where reference = '281830234' and id = processqueueid

    update orders set outboundhawbnumber = '', statuscode = 1007 where ordernumber = '281830234'
    update processqueue set statuscode = 5010 where id = 289403

    select hawbnumber, type, gls from hawb where hawbnumber = '7TB6934'

    delete from processqueue where datetimecreated like '20181219%'

    select * from processqueue, processqueueitems where datetimecreated like '20181219%' 
    and processqueueid = id
    order by id desc


-- sup-926

    select top 100 * from outputqueue  where entity = 'hawb'

    select * from outputqueue where reference = '8723904906'
    order by id desc

    update outputqueue set statuscode = 6000 where id = '11796043'

    select ordernumber from orders where outboundhawbnumber in
    (select hawbnumber from hawb where CarrierMAWBNumber = '8723904906')

    insert into outputqueue 
    (datetimecreated, customercode, reportid, entity, reference, reportsetupid, statuscode)
    select 
    '20190109000000', 'nxp', 'despatched', 'shipment', ordernumber, '187', 6000
    from orders where outboundhawbnumber in
    (select hawbnumber from hawb where CarrierMAWBNumber = '8723904906')

    insert into outputqueue 
    (datetimecreated, customercode, reportid, entity, reference, reportsetupid, statuscode)
    select 
    '20190109000000', 'nxp', 'm1despatched', 'hawb', hawbnumber, '188', 6000
    from hawb where CarrierMAWBNumber = '8723904906'

    select count(*) from outputqueue where statuscode = 6000

    update hawb set carriermawbnumber = 'x8723904906' where carriermawbnumber = '8723904906'
    and hawbnumber = '6598951726'


-- sc-65215 aplg hawb issues

    select customercode, ordernumber, ShipmentAdviceFile 
    from orders
    where
    customercode = 'APLG'
    and inboundhawbnumber <> ''
    and 
    (
        select count(*) 
        from hawb 
        where customercode = orders.customercode
        and hawbnumber = orders.inboundhawbnumber
    ) = 0

    update hawb
    set NumberOfPieces = 
    (
        select count(*) 
        from box
        where customercode = 'APLG'
        and InboundHAWBNumber = hawb.HAWBNumber
    )
    where customercode = 'APLG'
    and hawbnumber in (
        '7KHV100'
    )

    select '''' + hawbnumber + ''','
    from hawb
    where
    customercode = 'APLG'
    and DateTimeCreated >= '20190607'
    and NumberOfPieces <>
    (
        select count(*) 
        from box
        where customercode = 'APLG'
        and InboundHAWBNumber = hawb.HAWBNumber
    )

-- sup-1095 / dev-2136

    select 
    CarrierReference, min(FCSType), max(FCSType)
    from Tracking
    where datetimecreated > '201805'
    and fcstype in (920,930)
    group by CarrierReference
    having count(*) > 2

-- sup-1090

    -- invoice ref is 15 chars in invoice & invoiceaddress

    select *
    from InvoiceAddress ia
    where Reference in
    (
        select Reference
        from InvoiceAddress
        where CustomerCode=ia.CustomerCode
        group by Reference
        having count(*) > 1
    )
    order by reference

    select top 10 * from invoice where 
    BillToID = '414673376'
    or BillFromID = '414673376'
    or soldtoid = '414673376'

    select billfromcountrycode from invoice 

    select * from scriptaudit 
    where id like 'datadict%'
    order by datetime desc

    select * from outputqueue 
    where reportid = 'm1despatched'
    and reference = '5954234385'

    update outputqueue 
    set StatusCode = 6000
    where id = 12206895
    
    select * from ReportSetup where reportname = 'FCSSCI_NXP_SCI.xsl'

    select * from outputqueue where reportsetupid in (169, 171)
    select top 10 * from outputqueue

-- sup-1097 nexctl

    --truncate table GSMHistory
    --truncate table Tracking

    select ordernumber, preferredservicecode from orders where ordernumber = 'SB4134301'
    select * from GSMHistory
    select * from Tracking

-- sup-1108 mnlamp (3c3)

    select * from Invoice
    where ordernumber in ('AM0000447', 'AM1533824')

    select * from InvoiceLine
    where ordernumber in ('AM0000447', 'AM1533824')

    select * from InvoiceLineProduct
    where ordernumber in ('AM0000447', 'AM1533824')



select * from userid