Hi @OrangeCloud,

The notes will be saved on fin_note_text_tab table and the relevant note_id for a specific customer record will be saved on identity_note_tab table.

Therefore you may use the following query,

SELECT a.*
FROM   fin_note_text_tab a
WHERE  a.note_id = (SELECT b.note_id
                    FROM   identity_note_tab b
                    WHERE  b.company    = '&company'
                    AND    b.identity   = '&identity'
                    AND    b.party_type = 'CUSTOMER')
;

Cheers !
Dhananjaya.

Thanks @dhlelk , I am looking for getting history log on these Notes.. identity_note_tab is not coming up with a note_id when i add/edit a note in the existing Notes list in an account..

Here is what my query and results looks like, Can someone help me fixing this so i can get Customer no with these Notes to make this report readable. 

SELECT h.time_stamp Date_Change,client_sys.Get_Key_Reference_Value(h.keys,'IDENTITY')Cust_No,h.username,hla.old_value,hla.new_value,h.history_type  
 from history_log_tab h, history_log_attribute_tab hla
where  h.TABLE_NAME in( 'IDENTITY_INVOICE_INFO_TAB','FIN_NOTE_TEXT_TAB','IDENTITY_NOTE_TAB')
and (keys like'%IDENTITY=90073356%'or keys like'NOTE_ID%')
and hla.log_id = h.log_id
order by h.log_id;

Here is what my query and results looks like, Can someone help me fixing this so i can get Customer no with these Notes to make this report readable. 

SELECT h.time_stamp Date_Change,client_sys.Get_Key_Reference_Value(h.keys,'IDENTITY')Cust_No,h.username,hla.old_value,hla.new_value,h.history_type  
 from history_log_tab h, history_log_attribute_tab hla
where  h.TABLE_NAME in( 'IDENTITY_INVOICE_INFO_TAB','FIN_NOTE_TEXT_TAB','IDENTITY_NOTE_TAB')
and (keys like'%IDENTITY=90073356%'or keys like'NOTE_ID%')
and hla.log_id = h.log_id
order by h.log_id;

 

Hi @OrangeCloud,

Can you try the below method?

Setup history log for FIN_NOTE_TEXT_TAB.

Execute the below SQL,

SELECT hl.time_stamp   date_change,
       (SELECT iii.identity cust_no
        FROM   identity_invoice_info_tab iii
        WHERE  rownum = 1
        AND    Identity_Note_API.Get_Note_Id('COMPANY' || CHR(31) || iii.company || CHR(30) || 'IDENTITY' || CHR(31) || iii.identity || CHR(30) || 'PARTY_TYPE_DB' || CHR(31) || iii.party_type || CHR(30)) = Client_SYS.Get_Key_Reference_Value(hl.keys, 'NOTE_ID')) cust_no,
       hl.username     username,
       hla.old_value   old_value,
       hla.new_value   new_value,
       hl.history_type history_type
FROM   history_log_tab           hl,
       history_log_attribute_tab hla
WHERE  hl.log_id       =   hla.log_id
AND    hl.module       =  'ENTERP'
AND    hl.lu_name      =  'FinNoteText'
AND    hl.table_name   =  'FIN_NOTE_TEXT_TAB'
AND    hl.history_type IN ('1', '2')
ORDER  BY hl.log_id
;

Please note that the above SQL is not optimized and it will only work for Insert and Update log types.

Cheers !
Dhananjaya.

Hi ,

Is it possible to enable the history logs for module wise in single screen , What are the performance impact? 

Thanks @dhlelk  appreciate your response.