Logic of this function:
**************************
From invoice system we will post the data to ERP system once invoice is processed this function will update the erp invoice number and process flag to invoice system table.
CREATE OR REPLACE FUNCTION ERP_TO_MOPS_INV_UPD RETURN NUMBER IS
/* Function for update the invoice number processed status back to invoice table */
FRETVAL NUMBER:=0;
BEGIN
FOR I IN (SELECT PISH_ID,PISH_INV_NO FROM PH_INVOICE_SMRY_HDR WHERE ERP_STGFLG='S') LOOP
FOR J IN(SELECT INVOICE_NO FROM XXMO_AR_INVOICE_ARC WHERE REFERENCE_NO=I.PISH_INV_NO AND REFERENCE_DOC_ID=I.PISH_ID AND NVL(MO_STATUS,'N')='N') LOOP
UPDATE PH_INVOICE_SMRY_HDR SET ERP_INVOICE_NO=J.INVOICE_NO,ERP_STGFLG='P' WHERE PISH_ID=I.PISH_ID AND PISH_INV_NO=I.PISH_INV_NO;
IF SQL%NOTFOUND THEN
FRETVAL:=1003;
END IF;
UPDATE XXMO_AR_INVOICE_ARC SET MO_STATUS='P' WHERE REFERENCE_DOC_ID=I.PISH_ID AND REFERENCE_NO=I.PISH_INV_NO;
IF SQL%NOTFOUND THEN
FRETVAL:=1004;
END IF;
END LOOP;
END LOOP;
RETURN FRETVAL;
exception when others then
FRETVAL:=-1;
END ERP_TO_MOPS_INV_UPD;
when i try to run like this values are not get updated. But when i run this code using anonymous block its working. Anyone please help me
declare
ret number:=0;
begin
ret:=ERP_TO_MOPS_INV_UPD;
DBMS_OUTPUT.PUT_LINE('RETURN VALUE'|| RET);
end;