from
http://srini-peoplesoft.blogspot.com/2012/05/find-message-catalogs-in-peoplecode-sql.html
Select MsgSet_HexCode, MsgNbr_HexCode,
Substr(MsgSet_HexCode, -2)|| SubStr(MsgSet_HexCode,1,2) MsgSet_HexSrch,
Substr(MsgNbr_HexCode, -2)|| SubStr(MsgNbr_HexCode,1,2) MsgNbr_HexSrch,
Nvl2(MsgSet_HexCode,'500000'||Substr(MsgSet_HexCode, -2)||
SubStr(MsgSet_HexCode,1,2)||Lpad('3', 30, '0'),Null)
Nvl2(MsgNbr_HexCode,'500000'||Substr(MsgNbr_HexCode, -2)||
SubStr(MsgNbr_HexCode,1,2)||Lpad('3', 30, '0'),Null)
Srch_Str
From
(
Select (
Select LPad(Replace(Max(Sys_connect_by_path(HEX, ' ')), ' ', ''), 4, '0')
HexCode
From (Select NUM,
Chr(Floor(Mod(:MsgSetNbr/POW, 16))
+ Decode(Floor(Floor(Mod(:MsgSetNbr/POW, 16))/10), 0, 48, 55)
) HEX
From (Select Rownum-1 NUM, Power(16, Rownum-1) POW From Dual Connect By Level
<= 8)
Where :MsgSetNbr/POW > 1)
Start With NUM = FLOOR(LOG(16,:MsgSetNbr)) Connect By Prior NUM = NUM + 1)
MsgSet_HexCode,
(
Select LPad(Replace(Max(Sys_connect_by_path(HEX, ' ')), ' ', ''), 4, '0')
HexCode
From (Select NUM,
Chr(Floor(Mod(:MsgNbr/POW, 16))
+ Decode(Floor(Floor(Mod(:MsgNbr/POW, 16))/10), 0, 48, 55)
) HEX
From (Select Rownum - 1 NUM, Power(16, Rownum-1) POW From Dual Connect By
Level <= 8)
Where :MsgNbr / POW > 1)
Start With NUM = FLOOR(LOG(16,:MsgNbr)) Connect By Prior NUM = NUM + 1)
MsgNbr_HexCode
From Dual
)
)
Select P.*
From PSPCMPROG P, Srch
Where DBMS_LOB.INSTR(PROGTXT, Srch_Str) > 0;