1 PACKAGE BODY ast_note_package AS
2 /* $Header: astnoteb.pls 120.1 2005/06/01 03:35:27 appldev $ */
3 -- Start of Comments
4 -- Package name : ast_note_package
5 -- Purpose : Function to provide object details in AST_NOTE_CONTEXTS_V view
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 procedure note_context_info (
11 p_sql_statement IN varchar2,
12 p_object_info IN OUT NOCOPY /* file.sql.39 change */ varchar2,
13 p_object_id IN number) is
14 BEGIN
15 EXECUTE IMMEDIATE p_sql_statement INTO p_object_info USING p_object_id;
16 END note_context_info;
17
18 function read_clob (
19 p_clob CLOB)
20 return VARCHAR2 is
21 amount BINARY_INTEGER := 32000;
22 clob_size INTEGER;
23 buffer VARCHAR2(32000);
24 BEGIN
25 if p_clob is null then
26 return null;
27 else
28 clob_size := dbms_lob.getlength(p_clob);
29 if clob_size < amount then
30 amount := clob_size;
31 end if;
32 if clob_size = 0 then
33 return null;
34 end if;
35 dbms_lob.read(p_clob, amount, 1, buffer);
36 if amount > 0 then
37 return buffer;
38 else
39 return null;
40 end if;
41 end if;
42 END read_clob;
43
44 function read_clob (
45 p_note_id NUMBER)
46 return VARCHAR2 is
47 amount BINARY_INTEGER := 32000;
48 clob_size INTEGER;
49 buffer VARCHAR2(32000);
50
51 p_clob CLOB;
52 cursor c_clob (p_note_id NUMBER) is
53 select notes_detail
54 from jtf_notes_tl
55 where jtf_note_id = p_note_id
56 and language = userenv('LANG');
57
58 BEGIN
59 open c_clob(p_note_id);
60 fetch c_clob into p_clob;
61 close c_clob;
62
63 if p_clob is null then
64 return null;
65 else
66 clob_size := dbms_lob.getlength(p_clob);
67 if clob_size < amount then
68 amount := clob_size;
69 end if;
70 if clob_size = 0 then
71 return null;
72 end if;
73 dbms_lob.read(p_clob, amount, 1, buffer);
74 if amount > 0 then
75 return buffer;
76 else
77 return null;
78 end if;
79 end if;
80 END read_clob;
81
82 function party_type_info (
83 p_object_id NUMBER)
84 return VARCHAR2 is
85 l_party_type_name VARCHAR2(2000);
86 cursor C_party_type_name (p_object_id NUMBER) is
87 select a.meaning
88 from ar_lookups a, hz_parties p
89 where p.party_id = p_object_id
90 and a.lookup_code = p.party_type
91 and a.lookup_type = 'PARTY_TYPE';
92
93 BEGIN
94 l_party_type_name := 'Party';
95
96 if p_object_id is not null then
97 open C_party_type_name (p_object_id);
98 fetch C_party_type_name into l_party_type_name;
99 close C_party_type_name;
100 end if;
101
102 return l_party_type_name;
103
104 END party_type_info;
105
106 function note_context_info (
107 p_select_id VARCHAR2,
108 p_select_name VARCHAR2,
109 p_select_details VARCHAR2,
110 p_from_table VARCHAR2,
111 p_where_clause VARCHAR2,
112 p_object_id NUMBER)
113 return VARCHAR2 is
114 l_sql_statement VARCHAR2(2000);
115 l_object_info VARCHAR2(2000);
116 BEGIN
117 l_sql_statement := null;
118 l_object_info := null;
119
120 if p_from_table is not null and p_select_id is not null and p_object_id is not null then
121 if p_select_name is not null then
122 l_sql_statement := 'SELECT ' || p_select_name || ' ';
123 end if;
124 if p_select_details is not null then
125 if l_sql_statement is not null then
126 l_sql_statement := l_sql_statement || ' || '' - '' || ';
127 else
128 l_sql_statement := 'SELECT ';
129 end if;
130 l_sql_statement := l_sql_statement || p_select_details || ' ';
131 end if;
132 if l_sql_statement is not null then
133 l_sql_statement := l_sql_statement || 'FROM ' || p_from_table || ' ';
134 l_sql_statement := l_sql_statement || 'WHERE ' || p_select_id || ' = :p_object_id ';
135 if p_where_clause is not null then
136 l_sql_statement := l_sql_statement || 'AND ' || p_where_clause;
137 end if;
138 end if;
139 end if;
140
141 if l_sql_statement is not null then
142 begin
143 EXECUTE IMMEDIATE l_sql_statement INTO l_object_info USING p_object_id;
144 exception
145 when others then l_object_info := null;
146 end;
147 end if;
148
149 return l_object_info;
150
151 END note_context_info;
152 END ast_note_package;
[Home] [Help]