PLSQL Sample Logging Code

CREATE OR REPLACE
PROCEDURE APPS.xx_CUSTOM_METCHANT_LOG(
    P_REPORT_HEADER_ID  IN NUMBER,
    P_REPORT_LINE_INDEX IN NUMBER,
    P_EXPENSE_TYPE      IN VARCHAR2,
    P_MERCHANT          IN VARCHAR2,
    P_MESSAGE           IN VARCHAR2 )
IS
  BEGIN
  INSERT
  INTO apps.xx_EXP_REP_MERCHANT_LOG2
    (
      report_header_id,
      REPORT_LINE_INDEX,
      expense_type,
      merchant,
      MESSAGE
    )
    VALUES
    (
     P_REPORT_HEADER_ID 
    ,P_REPORT_LINE_INDEX
    ,P_EXPENSE_TYPE     
    ,P_MERCHANT         
    ,P_MESSAGE         
    );
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  NULL;
  END;
--END;


BEGIN
APPS.CUSTOM_METCHANT_LOG(12345,1,'airfare','other','test');
end;


select * from apps.xx_EXP_REP_MERCHANT_LOG2;



CREATE TABLE xx_logging_table(seq_no NUMBER(15), MESSAGE VARCHAR2(500));

CREATE SEQUENCE apps.xx_logging_table_s
      INCREMENT BY 1
      START WITH 1
      MAXVALUE   1000000
      NOCACHE
      CYCLE;


CREATE OR REPLACE PROCEDURE apps.xx_logging_proc (p_message VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO xx_logging_table
        VALUES (apps.xx_logging_table_s.NEXTVAL, p_message);

   COMMIT;
END xx_logging_proc;

commit;


DECLARE
BEGIN
   FOR i IN 1 .. 10
   LOOP
      apps.xx_logging_proc('VALUE' || '-' || i);
   END LOOP;
END;


select * from xx_logging_table;

truncate table xx_logging_table;


Logging in Personalization:

         fnd_log.string (fnd_log.LEVEL_STATEMENT,
                         'XX_ENFORCE_EMAILADDR',  -- module
                         v_message  --message to be inserted in fnd_log_messages
                                    );