begin try
BEGIN tran
	--Get The id of the GLI Status from Carton Status
DECLARE @GLI  AS int
SET              @GLI =
                          (SELECT     cartonstatus_id
                            FROM          CartonStatus
                            WHERE      (cartonstatus_name = 'GLI'))
--Create a temp table to hold Temp cartons ids
CREATE table #tempPLs(id int identity, packlistid nvarchar(20))
--Fill the temptable with the pl ids that their status must be changed
insert into #tempPLs
--Find Packlists that received GLI in the last 24 hours ie one day
SELECT    PackList_id
FROM         PackList 
where  packlist.packlist_statusDate< DateADD(day,-1,Getdate())
 

---INSERT INTO NOTICES AND TASK ACTION
DECLARE @notice_id INT 

if not exists (select notice_Message  from notice where notice_Message='PL remains in GLI in the last 24hrs')
begin
INSERT INTO [nicita].[dbo].[Notice]
           ([notice_Message])
     VALUES
           ('PL remains in GLI in the last 24hrs')
set @notice_id=@@identity
insert into noticegroup (notice_id,groups_id)
values (@notice_id,2)--finance group
end
else --notice exists already so only get notice id
begin
set @notice_id=(select top 1 notice_id from notice where notice_Message='PL remains in GLI in the last 24hrs')
end
INSERT INTO  [NoticeInformation]
           ([information_details]
           ,[information_date]
           ,[information_done]
           ,[notice_id])
select 'GLI PL: ' +  packlistid 
,getdate()
,0
,@notice_id
from #tempPLs
 


INSERT INTO [TasksActionData]
           (information_id
           ,[Data]--PL
           ,[ActionId])
SELECT @@identity,PACKLISTID,4 --none 
from  #tempPLs


 COMMIT TRAN -- Transaction Success!