CREATE trigger [dbo].[tr_UpdateTicketsorderstat2]
on [dbo].[PO_ORD_LIN]
for insert, update, delete
as

set nocount on
update tl
set tl.ITEM_ORDER_STATUS = 'O',
tl.item_order_no=rl.po_no,
tl.LST_ITEM_STAT_DT = rh.ord_dat

from PO_ORD_LIN rl 
inner join PO_ORD_HDR rh
on rh.PO_NO=rl.PO_NO

inner join ps_tkt_lin tl
on rl.COMMNT_1 = tl.tkt_no
and rl.ITEM_NO=tl.ITEM_NO
and rl.ps_tkt_lin_seq_no=tl.seq_no
and rl.ps_tkt_lin_str_id=tl.str_id
and rl.ps_tkt_lin_sta_id=tl.sta_id

where not exists
(select item_no
from po_recvr_hist_lin pl
where pl.PO_NO=RL.PO_NO
AND pl.ITEM_NO=RL.ITEM_NO
AND pl.COMMNT_1=RL.COMMNT_1
and pl.ps_tkt_lin_seq_no=tl.seq_no
and pl.ps_tkt_lin_str_id=tl.str_id
and pl.ps_tkt_lin_sta_id=tl.sta_id)

update tl
set tl.ITEM_ORDER_STATUS = 'R',
tl.item_order_no=rl.po_no,
tl.lst_item_stat_dt=pl.recvr_dat

from PO_ORD_LIN rl 
inner join ps_tkt_lin tl
on rl.COMMNT_1 = tl.tkt_no
and rl.ITEM_NO=tl.ITEM_NO
and rl.ps_tkt_lin_seq_no=tl.seq_no
and rl.ps_tkt_lin_str_id=tl.str_id
and rl.ps_tkt_lin_sta_id=tl.sta_id

inner join po_recvr_hist_lin pl
on pl.PO_NO=RL.PO_NO
AND pl.ITEM_NO=RL.ITEM_NO
AND pl.COMMNT_1=RL.COMMNT_1
and pl.ps_tkt_lin_seq_no=tl.seq_no
and pl.ps_tkt_lin_str_id=tl.str_id
and pl.ps_tkt_lin_sta_id=tl.sta_id
and 
(select sum(qty_recvd)
from po_recvr_hist_lin pl
where pl.PO_NO=RL.PO_NO
AND pl.ITEM_NO=RL.ITEM_NO
AND pl.COMMNT_1=RL.COMMNT_1
and pl.ps_tkt_lin_seq_no=tl.seq_no
and pl.ps_tkt_lin_str_id=tl.str_id
and pl.ps_tkt_lin_sta_id=tl.sta_id
and pl.commnt_1 is not null
)>=tl.qty_sold

update tl
set tl.ITEM_ORDER_STATUS = 'B',
tl.item_order_no=rl.po_no,
tl.lst_item_stat_dt=pl.recvr_dat

from PO_ORD_LIN rl 

inner join ps_tkt_lin tl
on rl.COMMNT_1 = tl.tkt_no
and rl.ITEM_NO=tl.ITEM_NO
and rl.ps_tkt_lin_seq_no=tl.seq_no
and rl.ps_tkt_lin_str_id=tl.str_id
and rl.ps_tkt_lin_sta_id=tl.sta_id

inner join
po_recvr_hist_lin pl
on pl.PO_NO=RL.PO_NO
AND pl.ITEM_NO=RL.ITEM_NO
AND pl.COMMNT_1=RL.COMMNT_1
and pl.ps_tkt_lin_seq_no=tl.seq_no
and pl.ps_tkt_lin_str_id=tl.str_id
and pl.ps_tkt_lin_sta_id=tl.sta_id
and 
(select sum(qty_recvd)
from po_recvr_hist_lin pl
where pl.PO_NO=RL.PO_NO
AND pl.ITEM_NO=RL.ITEM_NO
AND pl.COMMNT_1=RL.COMMNT_1
and pl.ps_tkt_lin_seq_no=tl.seq_no
and pl.ps_tkt_lin_str_id=tl.str_id
and pl.ps_tkt_lin_sta_id=tl.sta_id
and pl.commnt_1 is not null
)<tl.qty_sold

set nocount off

CREATE trigger [dbo].[tr_UpdateItemShipDate]
on [dbo].[PS_TKT_LIN]
for insert, update
as
set nocount on

update tl
set tl.TKT_SHIP_DAT = th.SHIP_DAT,
tl.PROMPT_ALPHA_1 = ti.PROF_ALPHA_1,
tl.PROMPT_ALPHA_2 = ti.PROF_ALPHA_2
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID = tl.STA_ID
and i.TKT_NO = tl.TKT_NO
and i.SEQ_NO = tl.SEQ_NO
inner join IM_ITEM ti
on tl.ITEM_NO = ti.ITEM_NO 
join PS_TKT_HDR th
on tl.STR_ID = th.STR_ID
and tl.STA_ID = th.STA_ID
and tl.TKT_NO = th.TKT_NO


/* When TKT_SHIP_DAT is updated then update ITEM_SHIP_DATE
 in table PS_TKT_LIN under the following conditions:
a) If ITEM_SHIP_DAT is NULL then set it to TKT_SHIP_DAT
b) If ITEM_SHIP_DAT is NOT NULL then only update if it matches old TKT_SHIP_DAT
*/

if update(TKT_SHIP_DAT)
update tl set
tl.ITEM_SHIP_DAT=tl.TKT_SHIP_DAT
from PS_TKT_LIN tl

join deleted d
on tl.STR_ID=d.STR_ID
and tl.STA_ID=d.STA_ID
and tl.TKT_NO=d.TKT_NO
where
tl.TKT_SHIP_DAT is not null
and (d.TKT_SHIP_DAT=tl.ITEM_SHIP_DAT or tl.item_ship_dat is null)
set nocount off

UPDATE OL
SET OL.PS_TKT_LIN_SEQ_NO=I.SEQ_NO
FROM INSERTED I
JOIN PO_ORD_LIN OL
ON OL.COMMNT_1=I.TKT_NO
AND OL.ITEM_NO=I.ITEM_NO
JOIN DELETED D
ON I.PO_NO=D.PO_NO
AND I.ITEM_NO=D.ITEM_NO
AND I.SEQ_NO=D.SEQ_NO
AND D.PS_TKT_LIN_SEQ_NO=OL.PS_TKT_LIN_SEQ_NO