/*
*    Description: Assigns XGL Ids to the BATProcess records for all distinct orders in the specified BAT session.
* The XGL for each order is looked up with a call to the spBATGetXLedgerXgls stored proc, which looks up
* XGLs by expense type and assigns the XGL to the BATProcess records.
*
* Any detected errors are logged to the BATErrorLog via spBATRecordError or to the StorageErrorLog via spStorageRecordError
*    if @inIsStorageProcessing = 1.
*
*    Input @params:
*    =============================================================================================
*    @inOrderID: The primary key for the order of interest.
*    @inBatSessionID: If specified, this is a BatSession identifier used to locate our BATProcess records attached to the specified order.
*        This will be ignored if @inBatProcessPriKey is > 0.
*    @inErrorLoggingSessionId: A BAT or Storage session identifier used for any error logging.  This could be the blank session identifier.
*    @inIsStorageProcessing: If 1, then any errors are reported via spStorageRecordError.  Otherwise, errors are reported
*        via spBATRecordError.
*    
*    Returned parameters:
*    =============================================================================================
*    None.
*/
create procedure [dbo].[BATGetXLedgerXglsForAllOrders]
    @inBatSessionID varchar(50),
    @inErrorLoggingSessionId varchar(50),
    @inIsStorageProcessing bit = null
as
set nocount on

declare @theCounter int
select @theCounter = 1
declare @theOrderCount int
declare @theCurrentOrderId int

-- Get all of the distinct Order Ids for the BAT session.
declare @theDistinctOrderIds table( OrderFid int, RowNumber int )
insert into @theDistinctOrderIds
(
    OrderFid,
    RowNumber
)
select distinct
    OrdPriKey as OrderFid,
    row_number() over (order by OrdPriKey) as RowNumber
from BATProcess
where
    BATSessionID = @inBatSessionID
group by OrdPriKey
    
-- Loop through each Order Id and call our SP that assigns Xgl Ids to the BATProcess records in this session for those orders.
select @theOrderCount = max( RowNumber ) from @theDistinctOrderIds
while( @theCounter <= @theOrderCount )
begin
    -- Get the current order primary key:
    select @theCurrentOrderId = OrderFid from @theDistinctOrderIds where RowNumber = @theCounter

    -- Assign XGL Ids to the BATProcess records for the current order:
    exec spBATGetXLedgerXgls
        @inOrderID = @theCurrentOrderId,
        @inBatSessionID = @inBatSessionID,
        @inErrorLoggingSessionId = @inErrorLoggingSessionId,
        @inBatProcessPriKey = null,
        @inIsStorageProcessing = 0

    set @theCounter = @theCounter + 1
end
GO
GRANT EXECUTE ON  [dbo].[BATGetXLedgerXglsForAllOrders] TO [MssExec]
GO