/**
*
*    Gets an order primary key from the GBL Number number.
*    This also checks for duplicate orders and if requested, logs the dupliate order error.
*    According to the General Services Administration, which oversees the use of the GBL Numbers, GBL
*    Numbers are never duplicated.  However, users can put the same GBL number on multiple orders.
*
*    @param @inGblNumberNo GBL number.
*    @param @inLeadsOnly 0 to check all orders regardless of booked status; 1 to check only orders that have been booked (have an Order Number)
*    @param @outErrorCode 0 for no duplicates found, otherwise error code for duplicates (non-zero)
*    @param @outOrderPriKey Primary key of order, otherwise null if not found or duplicates exist
*/
CREATE PROCEDURE [dbo].[XmlImportGetOrderFromGblNumber]
    @inGblNumberNo varchar(21),
    @inLeadsOnly bit,
    @outErrorCode int output,
    @outOrderPriKey int output
as
set nocount on

-- Define error constants
declare @DUPLICATE_GBL_NUMBERS int = 2167

-- Initialize output params
set @outErrorCode = 0
set @outOrderPriKey = null

if( 1 <
(
    select count(*)
    from MilitaryOrder
    inner join Orders on Orders.PriKey = MilitaryOrder.OrdersFID and Orders.Archived = 0
    where
        isnull( MilitaryOrder.GBLNumber, '' ) = isnull( @inGblNumberNo, '' ) and
        isnull( Orders.OrderNo, '' ) = case @inLeadsOnly
            when 1 then ''
            else isnull( Orders.OrderNo, '' )
        end and
        Orders.Archived = 0
) )
begin
    set @outErrorCode = @DUPLICATE_GBL_NUMBERS
end
else
begin
    set @outOrderPriKey =
    (
        select Orders.PriKey
        from MilitaryOrder
        inner join Orders on Orders.PriKey = MilitaryOrder.OrdersFID and Orders.Archived = 0
        where
            isnull( MilitaryOrder.GBLNumber, '' ) = isnull( @inGblNumberNo, '' ) and
            isnull( Orders.OrderNo, '' ) = case @inLeadsOnly
                when 1 then ''
                else isnull( Orders.OrderNo, '' )
            end and
            Orders.Archived = 0
    )
end
GO
GRANT EXECUTE ON  [dbo].[XmlImportGetOrderFromGblNumber] TO [MssExec]
GO