CREATE PROCEDURE dbo.sp_JT_BS14_PO_Extract AS
INSERT INTO [temp - buffer sheet - PO] ( weeknum_key, MATERIAL, [bal to be Recvd], PLANT)

SELECT dbo.fnWeeknum_key(DatePart(yyyy,[PLAN_DLVRY_DT]),DatePart(ww,[PLAN_DLVRY_DT])) AS weeknum_key, [base - 7117 PO].MATERIAL, 

Sum(Case [FINAL_DELIVERY] 
WHEN 'X' THEN '0'
Else [PO_QTY_SIZE]-[RECEIVED_QTY]
End AS [bal to be Recvd]),
 [base - 7117 PO].PLANT
FROM [base - 7117 PO]

WHERE ((([base - 7117 PO].PURCH_GRP) In ('120','121')) AND 
(([base - 7117 PO].IB_DELIVERY_NO)='0') AND (([base - 7117 PO].SLS_ORG)='1000') AND 
(([base - 7117 PO].PO_TYPE) In ('ZP01','ZP02','ZP03','ZP04','ZP06','ZP09')) AND 
(([base - 7117 PO].ITEM_CAT)='0') AND 
(([base - 7117 PO].DELETION_FLG)<>'L')) OR ((([base - 7117 PO].PURCH_GRP) In ('130','131')) AND (([base - 7117 PO].IB_DELIVERY_NO)='0') AND 
(([base - 7117 PO].PO_TYPE) In ('ZP01','ZP02','ZP03','ZP04','ZP06','ZP09')) AND 
(([base - 7117 PO].ITEM_CAT)='0') AND (([base - 7117 PO].DELETION_FLG)<>'L'))

GROUP BY dbo.fnWeeknum_key(DatePart(yyyy,[PLAN_DLVRY_DT]),DatePart(ww,[PLAN_DLVRY_DT])), [base - 7117 PO].MATERIAL, [base - 7117 PO].PLANT
 
HAVING (((Sum(IIf([FINAL_DELIVERY]='X',0,[PO_QTY_SIZE]-[RECEIVED_QTY])))>0) AND (([base - 7117 PO].PLANT)='1051')) OR 
(((Sum(IIf([FINAL_DELIVERY]='X',0,[PO_QTY_SIZE]-[RECEIVED_QTY])))>0) AND (([base - 7117 PO].PLANT)='1060'));