 Highlight Changed data [message #186805] |
Wed, 09 August 2006 12:17  |
ghamm2070
Messages: 8
Registered: March 2006
Location: Denver
|
Junior Member |
|
|
Hello experts
I'm building a report in 6i 8.1.7 database. I have a requirement to highlight/bold any field on the report that changed from the previous instance of the report. Luckily the tables I access have a pointer back to the previous row of data in the table (last_r_id) so I can get at the data I need for the compare.
This report is already very complicated and I'd like to avoid complicating things further by taking the wrong approach.
Has anyone ever had a need to do something like this? If you have please give me a brief description of what you did:
created another query with the previous data and compared with a format trigger, Unioned the old and new data and compared with a format trigger etc.
Any input you can provide will be a big help.
Regards
|
|
|
|
| Re: Highlight Changed data [message #208448 is a reply to message #186805] |
Sun, 10 December 2006 23:13  |
Mike_23
Messages: 11
Registered: December 2006
Location: Australia
|
Junior Member |
|
|
ghamm2070,
I'm guessing that you are referring to Intergraphs Marian application database? If so, I did a similar thing for a PO report....Let me know if you want details.
Michael.
|
|
|
|
|
|
| Re: Highlight Changed data [message #208656 is a reply to message #186805] |
Mon, 11 December 2006 16:16 |
Mike_23
Messages: 11
Registered: December 2006
Location: Australia
|
Junior Member |
|
|
You wont believe it but I have actually already done one for the Req also (just forgot that I had!!) Mine, however, only really is used for qty changes.
---------------------------------------
What I did was created a function and called it something like RLI_Change and placed it within the RLI SQL group. Then I used the following code to look for changes:
function CF_RLI_ChangeFormula return Char is
retval varchar2(12);
begin
SELECT rli_id
INTO retval
FROM m_sys.m_req_line_items rli, m_sys.m_reqs req, m_sys.m_commodity_codes cc,
m_sys.m_commodity_code_nls ccn,
m_sys.m_commodity_code_layouts ccl,
m_sys.m_idents i,
m_sys.m_ident_nls idn
WHERE req.r_id = rli.r_id
AND rli.ident = i.ident(+)
AND i.ident = idn.ident(+)
AND idn.nls_id(+) = 1
AND i.commodity_id = cc.commodity_id(+)
AND cc.commodity_id = ccn.commodity_id(+)
AND ccn.nls_id(+) = 1
AND ccl.commodity_id = i.commodity_id
AND req.r_supp > 0
AND ((rli.last_rli_id IS NULL) OR (TRIM(TO_CHAR(rli.total_release_qty-rli.last_total_release_qty, '9999999990.99')) <> '0.00')
OR DECODE(cc.commodity_code,'', idn.description, REPLACE(to_char(ccl.layout_long),chr(10),' ')) !=
(
SELECT DECODE(cc.commodity_code,'', idn.description, REPLACE(to_char(ccl.layout_long),chr(10),' '))
FROM m_sys.m_req_line_items rli1,
m_sys.m_reqs req1,
m_sys.m_commodity_codes cc,
m_sys.m_commodity_code_nls ccn,
m_sys.m_commodity_code_layouts ccl,
m_sys.m_idents i,
m_sys.m_ident_nls idn
WHERE req1.r_id = rli1.r_id
AND rli1.ident = i.ident(+)
AND i.ident = idn.ident(+)
AND idn.nls_id(+) = 1
AND i.commodity_id = cc.commodity_id(+)
AND cc.commodity_id = ccn.commodity_id(+)
AND ccn.nls_id(+) = 1
AND ccl.commodity_id = i.commodity_id
AND req1.r_code = :WP_REQ_ID
AND req1.r_supp = (:WP_SUPP - 1)
AND req1.proj_id = USER
AND ((actual_ind = DECODE(:WP_RTYPE, 'Requisition', 'N', 'Y')) OR (actual_ind = DECODE(:WP_RTYPE, 'Requisition', 'Y', 'N')))
AND actual_ind = DECODE(:WP_RTYPE, 'For Purchase', 'Y', 'N')
AND RLI1.RLI_ID = :RLI_ID
))
AND RLI.RLI_ID = :RLI_ID
AND rownum < 2;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN null;
end;
----------------------------------
Then on the required fields, I created a format trigger with this code:
function F_Prev_QtyFormatTrigger return boolean is
begin
IF (:rli_id = :CF_RLI_CHANGE) AND :PREV_QTY > '0.00' THEN
srw.set_text_color('red');
srw.set_font_face('Verdana');
srw.set_font_size(6);
srw.set_font_weight(srw.bold_weight);
srw.set_font_style(srw.plain_style);
RETURN (TRUE);
ELSE
IF :PREV_QTY > '0.00' THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END IF;
end;
----------------------
Hope it helps ya!! It would actually be good to keep in contact because like you I'm constantly creating reports for the Marian app and dont know of many other people who do this. 
Also, be careful of creating forms for Marian because I heard that they may cause problems when doing future upgrades!!
|
|
|
|
|
|
| Re: Highlight Changed data [message #208941 is a reply to message #208896] |
Tue, 12 December 2006 12:00 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I believe Gary and Ken will be thankful to you for revealing their business e-mail addresses to spammers.
You could have sent them (if necessary) directly to Mike via Private Message or any other way, but leaving them here ... huh, I'd say it is a BAD idea.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
