September 13, 2016 at 1:30 pm
Hello Team,
i have an issue where i need advanced knowledge and guidance :
i will try to explain the logic as best as i can :
Copy Plan has ------àISCIS(1 0r more)
The copy Plan can be scheduled by:
>Rotation Equally
>Rotation By Percentage
Copy Plans are Rotated weekly the columns in the #copyplan is:
RegionID int,
CopyHeaderID int,
SchedulerLogID int,
SchedulerID int,
[Priority] int,
LogDate DateTime,
HourOfTheDay int,
PositionInTheHour INT,
SchedulerOrderSpotID INT,
Length INT,
SecondsIntoTheHour INT,
OrderSpotID INT,
IsWegenerISCIScheduling BIT,
OrderDetailID INT,
OrderHeaderID INT
If the copy plan is assigned as a weekly rotation of ISCI with 10 spots and 4 ISCIS ‘Industry Standard Coding Identification’ we will rotate the ISCI 10 times equally ,
e.g isci 1 on Monday, isci 2 on Tuesday, isci 3 on Wednesday, isci 4 on Thursday, isci21 on Friday, until it is rotated 10 times.
If the rotation is passed as by percentage e.g let us assume we have 10 spots and 2 iscis, we want to rotate ISCI 1 60% and isci 2 40 %, we will start by rotating the isci one 6 times ,
when the and when it ends we rotate the isci 2 4 times 60 % and $05 being a function of 100%.
I am to apply this logic and modify the procedure below:
see script for dbo.AssignStationISCIForSchedulin.
below:
[dbo].[AssignStationISCIForScheduling]
AS
BEGIN
declare @RegionID int, @SchedulerLogID int, @OrderCopyHeaderID int, @OrderCopyMediaID int, @OrderCopyDetailID int
declare @ContractDetailID int, @SchedulerOrderSpotID int, @OrderDetailID int, @StationRegionID int, @RegionFilterID int, @RegionFilterTypeID int;
declare @RegionIDUpdate int, @RegionFilterIDUpdate int, @RegionFilterTypeIDUpdate int;
declare @ScheduleLogIDUsed int = 0, @OrderDetailIDInCopy INT,@PackageVehicleID INT,@PackageVehicleIDInCopy INT;
declare @Priority int, @LogDate datetime, @HourOfTheDay int, @PositionInTheHour INT,@TotalLength INT,@ContractHeaderID INT,@OrderSpotID INT
-- Assigning ISCI on Inventory level
declare copyplans cursor local fast_forward read_only for
select RegionID, CopyHeaderID, SchedulerLogID from #copyPlans order by [Priority] desc, SchedulerID, LogDate, HourOfTheDay, PositionInTheHour;
open copyplans;
fetch next from copyplans into @RegionID, @OrderCopyHeaderID, @SchedulerLogID;
while @@FETCH_STATUS = 0
BEGIN
declare inventories cursor local fast_forward read_only for
select i.ContractDetailID, i.SchedulerOrderSpotID, od.ID as OrderDetailID, rs.RegionID as StationRegionID, rs.RegionFilterID, rs.RegionFilterTypeID,sos.PackageVehicleID
from #InventoryTable i
join SchedulerOrderSpot sos on i.SchedulerOrderSpotID = sos.ID
join ContractDetail cd on i.ContractDetailID = cd.ID
JOIN dbo.OrderSpot AS os ON os.id = sos.OrderSpotID
JOIN dbo.OrderDetailWeek AS odw ON odw.id = os.OrderDetailWeekID
JOIN dbo.OrderDetail AS od ON od.id = odw.OrderDetailID
left join RegionStation rs on cd.StationID = rs.StationID and rs.RegionID = @RegionID
where sos.SchedulerLogID = @SchedulerLogID
and i.OrderCopyMediaID is NULL
option (recompile);
open inventories;
fetch next from inventories into @ContractDetailID, @SchedulerOrderSpotID, @OrderDetailID, @StationRegionID, @RegionFilterID, @RegionFilterTypeID,@PackageVehicleID;
while @@FETCH_STATUS = 0
BEGIN
Set @OrderDetailIDInCopy = (select OrderDetailID from OrderCopyHeader where ID = @OrderCopyHeaderID);
SET @PackageVehicleIDInCopy=(select PackageVehicleID from OrderCopyHeader where ID = @OrderCopyHeaderID);
-- process regional copy
if @StationRegionID is not null and @RegionID is not null and @RegionID = @StationRegionID and @PackageVehicleIDInCopy is not null and @PackageVehicleID = @PackageVehicleIDInCopy
begin
set @RegionIDUpdate = @RegionID;
set @RegionFilterIDUpdate = @RegionFilterID;
set @RegionFilterTypeIDUpdate = @RegionFilterTypeID;
--print 'regional';
END
ELSE if @StationRegionID is not null and @RegionID is not null and @RegionID = @StationRegionID and @OrderDetailIDInCopy is not null and @OrderDetailID = @OrderDetailIDInCopy
begin
set @RegionIDUpdate = @RegionID;
set @RegionFilterIDUpdate = @RegionFilterID;
set @RegionFilterTypeIDUpdate = @RegionFilterTypeID;
--print 'regional';
END
else if @StationRegionID is not null and @RegionID is not null and @RegionID = @StationRegionID and @OrderDetailIDInCopy is NOT NULL AND @PackageVehicleIDInCopy IS NULL
begin
set @RegionIDUpdate = @RegionID;
set @RegionFilterIDUpdate = @RegionFilterID;
set @RegionFilterTypeIDUpdate = @RegionFilterTypeID;
END
else if @StationRegionID is not null and @RegionID is not null and @RegionID = @StationRegionID and @OrderDetailIDInCopy is null
begin
set @RegionIDUpdate = @RegionID;
set @RegionFilterIDUpdate = @RegionFilterID;
set @RegionFilterTypeIDUpdate = @RegionFilterTypeID;
end
-- process national copy
else if @RegionID is null and @PackageVehicleIDInCopy is not null and @PackageVehicleID = @PackageVehicleIDInCopy
begin
set @RegionIDUpdate = null;
set @RegionFilterIDUpdate = null;
set @RegionFilterTypeIDUpdate = null;
--print 'national'
end
else if @RegionID is null and @OrderDetailIDInCopy is not null and @OrderDetailID = @OrderDetailIDInCopy
begin
set @RegionIDUpdate = null;
set @RegionFilterIDUpdate = null;
set @RegionFilterTypeIDUpdate = null;
--print 'national'
end
-- whole order copy
else if @RegionID is null and @OrderDetailIDInCopy is null
begin
set @RegionIDUpdate = null;
set @RegionFilterIDUpdate = null;
set @RegionFilterTypeIDUpdate = null;
--print 'whole order'
end
else
begin
--print 'unknown';
goto INNER_FETCH;
end
select top 1 @OrderCopyMediaID = OrderCopyMediaID, @OrderCopyDetailID = ID
from OrderCopyDetail
where OrderCopyHeaderID = @OrderCopyHeaderID
order by RotationCount asc, ID ASC
option (recompile);
update #InventoryTable
set OrderCopyHeaderID = @OrderCopyHeaderID,
OrderCopyMediaID = @OrderCopyMediaID,
RegionID = @RegionIDUpdate,
RegionFilterID = @RegionFilterIDUpdate,
RegionFilterTypeID = @RegionFilterTypeIDUpdate
where ContractDetailID = @ContractDetailID and SchedulerOrderSpotID = @SchedulerOrderSpotID;
update OrderCopyDetail set RotationCount = isnull(RotationCount, 0) + 1
where ID = @OrderCopyDetailID
option (recompile);
INNER_FETCH:
fetch next from inventories into @ContractDetailID, @SchedulerOrderSpotID, @OrderDetailID, @StationRegionID, @RegionFilterID, @RegionFilterTypeID,@PackageVehicleID;
END
close inventories;
deallocate inventories;
fetch next from copyplans into @RegionID, @OrderCopyHeaderID, @SchedulerLogID;
END;
close copyplans;
deallocate copyplans;
END
I learn from the footprints of giants......
September 13, 2016 at 3:54 pm
Dude! This goes well beyond the scope of a free forum.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 13, 2016 at 8:55 pm
+1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 13, 2016 at 9:49 pm
If you'd like some help, take a look at the link in Drew's signature to see how to post so that you have the best chance of getting an answer.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Basically, post create table scripts, insert statements with usable test data and the desired output based on the test data. Folks around here love a challenge but they don't love building test harnesses out of thin air, especially when they don't have a clue what to use for test data... Help us help you... 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply