iDempiere ERP has a reliability limitation in the receiving process. If a lot number has been set for an order detail due to special reasons, the receiving document will automatically incorporate the lot number from the order. However, if the actual received lot number differs from the one on the order and the lot number field already has a number, it is impossible to create a new lot number.
To solve this issue, we have designed a process for users to clear the lot number and then create a new one when encountering this situation. However, there is a limitation; the document status cannot be already completed.
This solution enables users to overcome the limitation of automatic lot number incorporation and create new lot numbers when necessary. By implementing this process, users can ensure that their inventory management is accurate and up to date.
To use the process, users must first identify the mismatch between the lot number on the order and the one they received. They can then use the process to clear the existing lot number, after which they can create a new lot number that matches the actually received lot. By doing so, users can ensure that their inventory is accurate and that they can avoid potential errors or discrepancies.
In conclusion, the iDempiere Material Receipt process for overcoming lot number limitations allows users to maintain accurate inventory management and avoid potential errors. By clearing and creating new lot numbers when necessary, users can ensure that their inventory data is reliable and up to date.
CREATE OR REPLACE FUNCTION adempiere.mm_clearinoutlineasi_sp(
pinstance numeric)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_docstatus VARCHAR (2) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN
pinstance := 0;
END IF;
v_message :=' process start';
BEGIN
ResultStr := 'PInstanceNotFound';
UPDATE adempiere.ad_pinstance
SET created = SYSDATE,
isprocessing = 'Y',
reslut = 0
WHERE ad_pinstance_id = pinstance_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
v_message :='parameter ';
FOR p IN (
SELECT
pi.record_id,
pi.ad_client_id,
pi.ad_org_id,
pi.ad_user_id,
pp.parametername,
pp.p_string,
pp.p_number,
pp.p_date,
pp.p_string_TO,
pp.p_number_TO,
pp.p_date_tO
FROM
adempiere.ad_pinstance pi
LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
WHERE
pi.ad_pinstance_id = pinstance
ORDER BY
pp.SeqNo
)
LOOP
p_Record_ID := p.record_id;
p_User_ID := p.AD_User_id;
p_AD_Client_ID := p.AD_Client_ID;
END LOOP;
v_message :='主要程式開始';
select docstatus into v_docstatus from m_inout
where exists (select 1 from m_inoutline where m_inout.m_inout_id = m_inout_id and m_inoutline_id = p_Record_ID);
v_message :='單據已完成無法執行';
IF v_docstatus not in ('CO','CL') THEN
update m_inoutline set m_attributesetinstance_id = 0 where m_inoutline_id = p_Record_ID;
v_message :='主要程式結束';
END IF;
IF pinstance > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET
updated = now(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE
ad_pinstance_id = pinstance;
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;
END;
$BODY$;
ALTER FUNCTION adempiere.mm_clearinoutlineasi_sp(numeric)
OWNER TO adempiere;
GRANT EXECUTE ON FUNCTION adempiere.mm_clearinoutlineasi_sp(numeric) TO adempiere;