ERP 2.50:QA test plan 2.50/Modularity/Room material procedure
/********************PROCEDURE RMAT_ADD_MATERIAL***************/
CREATE OR REPLACE PROCEDURE RMAT_ADD_MATERIAL (pinstance_id IN VARCHAR2)
AS
-- Logistice
v_ResultStr VARCHAR2(2000):=;
v_Message VARCHAR2(2000):=;
v_Record_ID VARCHAR2(32);
-- Parameter
TYPE RECORD IS REF CURSOR;
Cur_Parameter RECORD;
-- Record Info
v_Bpartner VARCHAR2(32);
v_Client VARCHAR2(32);
v_Org VARCHAR2(32);
v_Createdby VARCHAR2(32);
v_Typematerial CHAR(1);
v_SeqNo NUMBER(10);
v_Rmat_Id VARCHAR2(32);
v_NoOfLines NUMBER(10);
-- Copy
Cur_Copymaterial RECORD;
BEGIN
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID) ;
v_ResultStr:='PInstanceNotFound';
AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'Y', NULL, NULL) ;
BEGIN --BODY
-- Get Parameters
v_ResultStr:='ReadingParameters';
FOR Cur_Parameter IN
(SELECT i.Record_ID,
p.ParameterName,
p.P_String,
p.P_Number,
p.P_Date
FROM AD_PINSTANCE i
LEFT JOIN AD_PINSTANCE_PARA p
ON i.AD_PInstance_ID=p.AD_PInstance_ID
WHERE i.AD_PInstance_ID=PInstance_ID
ORDER BY p.SeqNo
)
LOOP
v_Record_ID:=Cur_Parameter.Record_ID;
IF(Cur_Parameter.ParameterName='MaterialType') THEN
v_Typematerial:=Cur_Parameter.P_String;
DBMS_OUTPUT.PUT_LINE('MaterialType=' || v_Typematerial) ;
END IF;
END LOOP; -- Get Parameter
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || v_Record_ID) ;
v_ResultStr:='Readingscheduleroom';
SELECT AD_CLIENT_ID, AD_ORG_ID, CREATEDBY
INTO v_client, v_org, v_Createdby
FROM ROOMS_ROOMSCHEDULE
WHERE ROOMS_ROOMSCHEDULE_ID=v_Record_ID;
SELECT COALESCE(MAX(RMAT_REQUIRED.line), 10)
INTO v_SeqNo
FROM RMAT_REQUIRED
WHERE RMAT_REQUIRED.RMAT_REQUIRED_ID=v_Record_ID;
-- Copying records from RMAT_MATERIAL depending on Type material value
v_ResultStr:='Copying';
FOR Cur_Copymaterial IN
(SELECT RMAT_MATERIAL_ID
FROM RMAT_MATERIAL
WHERE RMAT_MATERIAL.MATERIALTYPE=v_Typematerial
AND RMAT_MATERIAL.AD_CLIENT_ID=v_Client;
)
LOOP
-- Get next no
SELECT GET_UUID() INTO v_Rmat_Id FROM DUAL;
-- Insert
INSERT
INTO RMAT_REQUIRED
( RMAT_REQUIRED_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, LINE, ROOMS_ROOMSCHEDULE_ID, MATERIALUSED_ID)
VALUES
(
v_Rmat_Id, v_client, v_org, 'Y',
now(), v_Createdby, now(), v_Createdby,
v_SeqNo, v_Record_ID, Cur_Copymaterial.RMAT_MATERIAL_ID
)
;
-- update translation
--
v_SeqNo:=v_SeqNo + 10;
v_NoOfLines:=v_NoOfLines + 1;
END LOOP;
v_Message:='@CopiedMaterial@=' || v_NoOfLines ;
--<<FINISH_PROCESS>>
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ;
AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 1, v_Message) ;
RETURN;
END; --BODY
EXCEPTION
WHEN OTHERS THEN
v_ResultStr:= '@ERROR=' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
RETURN;
END RMAT_ADD_MATERIAL
;
/