Most probably a production order has been updated so that the BXID (PmxID) in WOR1 has been changed, and therefore the PDC booking cannot find the related line. This issue is cause by a wrong usage of the add-on.
The customer must decide what to do:
Delete the PDC Booking lines:
begin transaction
delete from [@BXPPDCMATQTY] where code in (select Code from [@BXPPDCMATQTY] mat where not exists (select code from [@BXPPRODORDEROPER] oper where mat.u_bxpprooi=oper.code))
delete from [@BXPPDCPRODQTY] where code in (select Code from [@BXPPDCPRODQTY] prod where not exists (select code from [@BXPPRODORDEROPER] oper where prod.u_bxpprooi=oper.code))
delete from [@BXPPDCSERBATCH] where code in (select Code from [@BXPPDCSERBATCH] ser where not exists (select code from [@BXPPRODORDEROPER] oper where ser.u_bxpprooi=oper.code))
delete from [@BXPPDCBOOKING] where code in (select Code from [@BXPPDCBOOKING] book where book.U_BXPPrOOI is not null and not exists (select code from [@BXPPRODORDEROPER] oper where book.u_bxpprooi=oper.code))
delete from [@BXPPDCOPERPARAM] where code in (select Code from [@BXPPDCOPERPARAM] par where not exists (select code from [@BXPPRODORDEROPER] oper where par.u_bxpprooi=oper.code))
rollback
Replace rollback with commit to make the changes in the db.
Reassign the PDC bookings to the new BXID of the same operation line if it exists or assign it to a new operation.
These are the steps of this process:
1. Use the following Script to get the messages that contain the BXID codes:
DECLARE @CRLF varchar(2), @TAB varchar(1)
DECLARE @s varchar(8000)
DECLARE @sum1 int, @sum2 int
DECLARE @code nvarchar(100), @fkField nvarchar(100)
SET @TAB = CHAR(9)
SET @CRLF = CHAR(10)
SET @s = ''
select @sum1 = COUNT(distinct t2.Code)
from [@BXPPDCBOOKING] as t1, [@BXPPRODORDEROPER] as t2 where t1.U_BXPPrOOI is not null and t1.U_BXPPrOOI = t2.Code
select @sum2 = COUNT(distinct U_BXPPrOOI)
from [@BXPPDCBOOKING] where U_BXPPrOOI is not null
IF @sum1 <> @sum2
BEGIN
DECLARE curs CURSOR
FOR select t1.Code,t1.U_BXPPrOOI
from [@BXPPDCBOOKING] as t1 LEFT OUTER JOIN [@BXPPRODORDEROPER] as t2 on t1.U_BXPPrOOI = t2.Code
where t2.Code IS NULL and t1.U_BXPPrOOI IS NOT NULL
and t1.U_BXPPrOOI <> ''
OPEN curs
FETCH NEXT FROM curs INTO @code, @fkField
WHILE @@FETCH_STATUS = 0
BEGIN
IF @fkField IS NULL SET @fkField = '(NULL)'
IF @code IS NULL SET @code = '(NULL)'
SET @s = @s + 'E:[U_BXPPrOOI]=' + @fkField + ' for [Code]=' + @code + ' in [dbo].[@BXPPDCBOOKING] doesnt exist in [@BXPPRODORDEROPER]' + @CRLF
FETCH NEXT FROM curs INTO @code, @fkField
END
CLOSE curs
DEALLOCATE curs
END
RAISERROR (@s,0,1)
EXAMPLE RESULT:
E:[U_BXPPrOOI]=00037559 for [Code]=00038168 in [dbo].[@BXPPDCBOOKING] doesnt exist in [@BXPPRODORDEROPER]
E:[U_BXPPrOOI]=00037559 for [Code]=00038169 in [dbo].[@BXPPDCBOOKING] doesnt exist in [@BXPPRODORDEROPER]
E:[U_BXPPrOOI]=00037559 for [Code]=00038170 in [dbo].[@BXPPDCBOOKING] doesnt exist in [@BXPPRODORDEROPER]
E:[U_BXPPrOOI]=00037559 for [Code]=00038171 in [dbo].[@BXPPDCBOOKING] doesnt exist in [@BXPPRODORDEROPER]
2. Use U_BXPPrOOI value from the message to get the Production Order Number:
select U_BXPPrODN as ProdOrderNum from [@BXPPDCBOOKING] where U_BXPPrOOI = '00037559'
3. Select the operations from the Production Order:
select "WOR1"."ItemCode", "WOR1"."U_BXPBxID" from "WOR1" left join "OWOR" on "WOR1"."DocEntry" = "OWOR"."DocEntry" where "U_BXPRowTy" = 2 AND "DocNum" = '606'
In case you have multiple operations select the value from the U_BXPBxID column from that operation, to which you want to link the PDCBOOKING.
4. Update the BXPPrOOI value in the PDCBOOKING table and in the material and product movements tables, and set the new BXID value in those lines where you have the wrong, not linked PDCBOOKINGS:
update [@BXPPDCBOOKING] set U_BXPPrOOI = '00038174' where U_BXPPrOOI = '00037559'
update [@BXPPDCMATQTY] set U_BXPPrOOI = '00038174' where U_BXPPrOOI = '00037559'
update [@BXPPDCPRODQTY] set U_BXPPrOOI = '00038174' where U_BXPPrOOI = '00037559'
5. Run the SQL Script again and repeat the described process until you eliminate all foreign reference message.