Hello
I want to log history on the ‘Notes’ in Customer Master ‘Invoice’ Tab. I can’t seems to be successful. Any ideas how to get these Notes in History Log Please. IFS8
So far i logged history on tables, 'IDENTITY_INVOICE_INFO_TAB' ,'FIN_NOTE_TEXT_TAB','IDENTITY_NOTE_TAB'
IDENTITY_NOTE_TAB does not seems to work. any ideas how can i get customer number, and latest update on note from history_log_tab , history_log_attribute_tab tables
Best answer by dhlelk
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
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.
+15Hi
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
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;
+15Here 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
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.
+4Hi ,
Is it possible to enable the history logs for module wise in single screen , What are the performance impact?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.