CREATE PROCEDURE [dbo].[MssWebUpdateInternationalShipmentDetails]
    @inOrderId int,
    @inUpdatedBy int,
    @inInternationalTransportationModeFID int = null,
    @inUpdateInternationalTransportationModeFID bit,
    @inInternationalDirectionFID int = null,
    @inUpdateInternationalDirectionFID bit,
    @inInternationalServiceTypeFID int = null,
    @inUpdateInternationalServiceTypeFID bit,
    @inCostPlus bit = null,
    @inUpdateCostPlus bit,
    @inReadyToBill bit = null,
    @inUpdateReadyToBill bit,
    @inReleasedToBill bit = null,
    @inUpdateReleasedToBill bit,
    @inTariffIndicator bit = null,
    @inUpdateTariffIndicator bit,
    @inGppApplies bit = null,
    @inUpdateGppApplies bit,
    @inInternationalValuationDeclinedReasonFID int = null,
    @inUpdateInternationalValuationDeclinedReasonFID bit,
    @inCertificationNumber varchar(64) = null,
    @inUpdateCertificationNumber bit,
    @inDeductibleAmount money = null,
    @inUpdateDeductibleAmount bit,
    @inInternationalValueTypeFID int = null,
    @inUpdateInternationalValueTypeFID bit,
    @inTotalLossCoverage bit = null,
    @inUpdateTotalLossCoverage bit,
    @inMechanicalMalfunction bit = null,
    @inUpdateMechanicalMalfunction bit,
    @inPairsOrSets bit = null,
    @inUpdatePairsOrSets bit,
    @inMoldOrMildew bit = null,
    @inUpdateMoldOrMildew bit,
    @inInsuredRate money = null,
    @inUpdateInsuredRate bit,
    @inInternalRate money = null,
    @inUpdateInternalRate bit,
    @inBilledRate money = null,
    @inUpdateBilledRate bit,
    @inIncludeAutoOnCertificate bit = null,
    @inUpdateIncludeAutoOnCertificate bit,
    @inInsuredValueForAuto money = null,
    @inUpdateInsuredValueForAuto bit,
    @inReadyToBillDate datetime = null,
    @inUpdateReadyToBillDate bit,
    @inCertificateDate datetime = null,
    @inUpdateCertificateDate bit,
    @inReleaseToBillDate datetime = null,
    @inUpdateReleaseToBillDate bit
as
begin
    set nocount on

    declare @OrderAuditInfoFID bigint

    -- Prep for audit logging
    exec PrepOrderForAuditLog
        @inSysUserID = @inUpdatedBy,
        @inUpdateSource = 'MS Web',
        @outOrderAuditInfoFID = @OrderAuditInfoFID output

    -- Update InternationalShippingInformation table
    merge InternationalShippingInformation with (tablock) as target
    using (select @inOrderId as OrdersFID) as source
        on target.OrdersFID = source.OrdersFID
    when not matched then
        insert (OrdersFID, InternationalTransportationModeFID, InternationalDirectionFID, InternationalServiceTypeFID,
            CostPlus, ReadyToBill, ReleasedToBill, TariffIndicator, GppApplies, InternationalValuationDeclinedReasonFID,
            CertificationNumber, DeductibleAmount, InternationalValueTypeFID, TotalLossCoverage, MechanicalMalfunction,
            PairsOrSets, MoldOrMildew, InsuredRate, InternalRate, BilledRate, IncludeAutoOnCertificate, InsuredValueForAuto)
        values (
            source.OrdersFID,
            case when @inUpdateInternationalTransportationModeFID = 1 then @inInternationalTransportationModeFID else null end,
            case when @inUpdateInternationalDirectionFID = 1 then @inInternationalDirectionFID else null end,
            case when @inUpdateInternationalServiceTypeFID = 1 then @inInternationalServiceTypeFID else null end,
            case when @inUpdateCostPlus = 1 then @inCostPlus else 0 end,
            case when @inUpdateReadyToBill = 1 then @inReadyToBill else 0 end,
            case when @inUpdateReleasedToBill = 1 then @inReleasedToBill else 0 end,
            case when @inUpdateTariffIndicator = 1 then @inTariffIndicator else 0 end,
            case when @inUpdateGppApplies = 1 then @inGppApplies else 0 end,
            case when @inUpdateInternationalValuationDeclinedReasonFID = 1 then @inInternationalValuationDeclinedReasonFID else null end,
            case when @inUpdateCertificationNumber = 1 then @inCertificationNumber else null end,
            case when @inUpdateDeductibleAmount = 1 then @inDeductibleAmount else null end,
            case when @inUpdateInternationalValueTypeFID = 1 then @inInternationalValueTypeFID else null end,
            case when @inUpdateTotalLossCoverage = 1 then @inTotalLossCoverage else 0 end,
            case when @inUpdateMechanicalMalfunction = 1 then @inMechanicalMalfunction else 0 end,
            case when @inUpdatePairsOrSets = 1 then @inPairsOrSets else 0 end,
            case when @inUpdateMoldOrMildew = 1 then @inMoldOrMildew else 0 end,
            case when @inUpdateInsuredRate = 1 then @inInsuredRate else null end,
            case when @inUpdateInternalRate = 1 then @inInternalRate else null end,
            case when @inUpdateBilledRate = 1 then @inBilledRate else null end,
            case when @inUpdateIncludeAutoOnCertificate = 1 then @inIncludeAutoOnCertificate else 0 end,
            case when @inUpdateInsuredValueForAuto = 1 then @inInsuredValueForAuto else null end
        )
    when matched then
        update set
            InternationalTransportationModeFID =
                case
                    when @inUpdateInternationalTransportationModeFID = 1 then @inInternationalTransportationModeFID
                    else target.InternationalTransportationModeFID
                end,
            InternationalDirectionFID =
                case
                    when @inUpdateInternationalDirectionFID = 1 then @inInternationalDirectionFID
                    else target.InternationalDirectionFID
                end,
            InternationalServiceTypeFID =
                case
                    when @inUpdateInternationalServiceTypeFID = 1 then @inInternationalServiceTypeFID
                    else target.InternationalServiceTypeFID
                end,
            CostPlus =
                case
                    when @inUpdateCostPlus = 1 then @inCostPlus
                    else target.CostPlus
                end,
            ReadyToBill =
                case
                    when @inUpdateReadyToBill = 1 then @inReadyToBill
                    else target.ReadyToBill
                end,
            ReleasedToBill =
                case
                    when @inUpdateReleasedToBill = 1 then @inReleasedToBill
                    else target.ReleasedToBill
                end,
            TariffIndicator =
                case
                    when @inUpdateTariffIndicator = 1 then @inTariffIndicator
                    else target.TariffIndicator
                end,
            GppApplies =
                case
                    when @inUpdateGppApplies = 1 then @inGppApplies
                    else target.GppApplies
                end,
            InternationalValuationDeclinedReasonFID =
                case
                    when @inUpdateInternationalValuationDeclinedReasonFID = 1 then
                        case
                            when @inInternationalValuationDeclinedReasonFID is null then null
                            when exists (select 1 from InternationalValuationDeclinedReason where InternationalValuationDeclinedReasonID = @inInternationalValuationDeclinedReasonFID) then @inInternationalValuationDeclinedReasonFID
                            else target.InternationalValuationDeclinedReasonFID
                        end
                    else target.InternationalValuationDeclinedReasonFID
                end,
            CertificationNumber =
                case
                    when @inUpdateCertificationNumber = 1 then @inCertificationNumber
                    else target.CertificationNumber
                end,
            DeductibleAmount =
                case
                    when @inUpdateDeductibleAmount = 1 then @inDeductibleAmount
                    else target.DeductibleAmount
                end,
            InternationalValueTypeFID =
                case
                    when @inUpdateInternationalValueTypeFID = 1 then @inInternationalValueTypeFID
                    else target.InternationalValueTypeFID
                end,
            TotalLossCoverage =
                case
                    when @inUpdateTotalLossCoverage = 1 then @inTotalLossCoverage
                    else target.TotalLossCoverage
                end,
            MechanicalMalfunction =
                case
                    when @inUpdateMechanicalMalfunction = 1 then @inMechanicalMalfunction
                    else target.MechanicalMalfunction
                end,
            PairsOrSets =
                case
                    when @inUpdatePairsOrSets = 1 then @inPairsOrSets
                    else target.PairsOrSets
                end,
            MoldOrMildew =
                case
                    when @inUpdateMoldOrMildew = 1 then @inMoldOrMildew
                    else target.MoldOrMildew
                end,
            InsuredRate =
                case
                    when @inUpdateInsuredRate = 1 then @inInsuredRate
                    else target.InsuredRate
                end,
            InternalRate =
                case
                    when @inUpdateInternalRate = 1 then @inInternalRate
                    else target.InternalRate
                end,
            BilledRate =
                case
                    when @inUpdateBilledRate = 1 then @inBilledRate
                    else target.BilledRate
                end,
            IncludeAutoOnCertificate =
                case
                    when @inUpdateIncludeAutoOnCertificate = 1 then @inIncludeAutoOnCertificate
                    else target.IncludeAutoOnCertificate
                end,
            InsuredValueForAuto =
                case
                    when @inUpdateInsuredValueForAuto = 1 then @inInsuredValueForAuto
                    else target.InsuredValueForAuto
                end;

    -- Update InternationalShippingDate table for date fields
    merge InternationalShippingDate with (tablock) as target
    using (select @inOrderId as OrdersFID) as source
        on target.OrdersFID = source.OrdersFID
    when not matched then
        insert (OrdersFID, OrderAuditInfoFID, ReadyToBillDate, CertificateDate, ReleaseToBillDate)
        values (
            source.OrdersFID,
            @OrderAuditInfoFID,
            case when @inUpdateReadyToBillDate = 1 then @inReadyToBillDate else null end,
            case when @inUpdateCertificateDate = 1 then @inCertificateDate else null end,
            case when @inUpdateReleaseToBillDate = 1 then @inReleaseToBillDate else null end
        )
    when matched then
        update set
            ReadyToBillDate =
                case
                    when @inUpdateReadyToBillDate = 1 then @inReadyToBillDate
                    else target.ReadyToBillDate
                end,
            CertificateDate =
                case
                    when @inUpdateCertificateDate = 1 then @inCertificateDate
                    else target.CertificateDate
                end,
            ReleaseToBillDate =
                case
                    when @inUpdateReleaseToBillDate = 1 then @inReleaseToBillDate
                    else target.ReleaseToBillDate
                end,
            OrderAuditInfoFID = @OrderAuditInfoFID;

end
GO
GRANT EXECUTE ON  [dbo].[MssWebUpdateInternationalShipmentDetails] TO [MssExec]
GO