1 package body PN_NOTE_DETAILS_PKG As
2 /* $Header: PNTNOTDB.pls 120.1 2005/08/05 06:28:07 appldev ship $ */
3 -------------------------------------------------------------------
4 -- PROCEDURE : INSERT_ROW
5 -------------------------------------------------------------------
6 PROCEDURE INSERT_ROW
7 (
8 X_ROWID in out NOCOPY VARCHAR2
9 ,X_NOTE_DETAIL_ID in out NOCOPY NUMBER
10 ,X_NOTE_HEADER_ID in NUMBER
11 ,X_TEXT in VARCHAR2
12 ,X_CREATION_DATE in DATE
13 ,X_CREATED_BY in NUMBER
14 ,X_LAST_UPDATE_DATE in DATE
15 ,X_LAST_UPDATED_BY in NUMBER
16 ,X_LAST_UPDATE_LOGIN in NUMBER
17 )
18 IS
19 cursor C is
20 select ROWID
21 from PN_NOTE_DETAILS
22 where NOTE_DETAIL_ID = X_NOTE_DETAIL_ID
23 and LANGUAGE = userenv('LANG');
24 BEGIN
25 IF (X_NOTE_DETAIL_ID IS NULL) THEN
26 select PN_NOTE_DETAILS_S.nextval
27 into X_NOTE_DETAIL_ID
28 from dual;
29 END IF;
30
31 insert into PN_NOTE_DETAILS
32 (
33 LAST_UPDATED_BY,
34 LAST_UPDATE_DATE,
35 LAST_UPDATE_LOGIN,
36 CREATED_BY,
37 CREATION_DATE,
38 TEXT,
39 NOTE_DETAIL_ID,
40 NOTE_HEADER_ID,
41 LANGUAGE,
42 SOURCE_LANG
43 )
44 select
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATE_LOGIN,
48 X_CREATED_BY,
49 X_CREATION_DATE,
50 X_TEXT,
51 X_NOTE_DETAIL_ID,
52 X_NOTE_HEADER_ID,
53 L.LANGUAGE_CODE,
54 userenv('LANG')
55 from FND_LANGUAGES L
56 where L.INSTALLED_FLAG in ('I', 'B')
57 and not exists
58 (
59 select NULL
60 from PN_NOTE_DETAILS T
61 where T.NOTE_DETAIL_ID = X_NOTE_DETAIL_ID
62 and T.LANGUAGE = L.LANGUAGE_CODE
63 );
64
65 open c;
66 fetch c into X_ROWID;
67 if (c%notfound) then
68 close c;
69 raise no_data_found;
70 end if;
71 close c;
72
73 END INSERT_ROW;
74
75 -------------------------------------------------------------------
76 -- PROCEDURE : LOCK_ROW
77 -------------------------------------------------------------------
78 PROCEDURE LOCK_ROW
79 (
80 X_NOTE_DETAIL_ID in NUMBER
81 ,X_NOTE_HEADER_ID in NUMBER
82 ,X_TEXT in VARCHAR2
83 )
84 IS
85
86 CURSOR C1 IS
87 select *
88 from PN_NOTE_DETAILS
89 where NOTE_DETAIL_ID = X_NOTE_DETAIL_ID
90 and LANGUAGE = userenv('LANG')
91 for update of NOTE_DETAIL_ID nowait;
92
93 tlinfo c1%rowtype;
94
95 BEGIN
96 open c1;
97 fetch c1 into tlinfo;
98 if (c1%notfound) then
99 close c1;
100 return;
101 end if;
102 close c1;
103
104 if ( (tlinfo.TEXT = X_TEXT)
105 AND (tlinfo.NOTE_HEADER_ID = X_NOTE_HEADER_ID)
106 ) then
107 null;
108 else
109 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
110 app_exception.raise_exception;
111 end if;
112
113 return;
114 END LOCK_ROW;
115
116 -------------------------------------------------------------------
117 -- PROCEDURE : UPDATE_ROW
118 -------------------------------------------------------------------
119 PROCEDURE UPDATE_ROW
120 (
121 X_NOTE_DETAIL_ID in NUMBER
122 ,X_TEXT in VARCHAR2
123 ,X_LAST_UPDATE_DATE in DATE
124 ,X_LAST_UPDATED_BY in NUMBER
125 ,X_LAST_UPDATE_LOGIN in NUMBER
126 )
127 IS
128 BEGIN
129 update PN_NOTE_DETAILS
130 set TEXT = X_TEXT
131 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
132 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
133 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
134 ,SOURCE_LANG = userenv('LANG')
135 where NOTE_DETAIL_ID = X_NOTE_DETAIL_ID
136 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
137
138 if (sql%notfound) then
139 raise no_data_found;
140 end if;
141 END UPDATE_ROW;
142
143 -------------------------------------------------------------------
144 -- PROCEDURE : DELETE_ROW
145 -------------------------------------------------------------------
146 PROCEDURE DELETE_ROW
147 (
148 X_NOTE_DETAIL_ID in NUMBER
149 )
150 IS
151 BEGIN
152 delete from PN_NOTE_DETAILS
153 where NOTE_DETAIL_ID = X_NOTE_DETAIL_ID;
154
155 if (sql%notfound) then
156 raise no_data_found;
157 end if;
158
159 END DELETE_ROW;
160
161 -------------------------------------------------------------------
162 -- PROCEDURE : ADD_LANGUAGE
163 -------------------------------------------------------------------
164 PROCEDURE ADD_LANGUAGE
165 IS
166 BEGIN
167 update PN_NOTE_DETAILS T
168 set ( TEXT) = (
169 select B.TEXT
170 from PN_NOTE_DETAILS B
171 where B.NOTE_DETAIL_ID = T.NOTE_DETAIL_ID
172 and B.LANGUAGE = T.SOURCE_LANG
173 )
174 where ( T.NOTE_DETAIL_ID,
175 T.LANGUAGE
176 ) IN
177 (
178 select SUBT.NOTE_DETAIL_ID,
179 SUBT.LANGUAGE
180 from PN_NOTE_DETAILS SUBB,
181 PN_NOTE_DETAILS SUBT
182 where SUBB.NOTE_DETAIL_ID = SUBT.NOTE_DETAIL_ID
183 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
184 and (SUBB.TEXT <> SUBT.TEXT)
185 );
186
187 insert into PN_NOTE_DETAILS
188 (
189 LAST_UPDATED_BY,
190 LAST_UPDATE_DATE,
191 LAST_UPDATE_LOGIN,
192 CREATED_BY,
193 CREATION_DATE,
194 TEXT,
195 NOTE_DETAIL_ID,
196 NOTE_HEADER_ID,
197 LANGUAGE,
198 SOURCE_LANG
199 )
200 select B.LAST_UPDATED_BY,
201 B.LAST_UPDATE_DATE,
202 B.LAST_UPDATE_LOGIN,
203 B.CREATED_BY,
204 B.CREATION_DATE,
205 B.TEXT,
206 B.NOTE_DETAIL_ID,
207 B.NOTE_HEADER_ID,
208 L.LANGUAGE_CODE,
209 B.SOURCE_LANG
210 from PN_NOTE_DETAILS B,
211 FND_LANGUAGES L
212 where L.INSTALLED_FLAG in ('I', 'B')
213 and B.LANGUAGE = userenv('LANG')
214 and not exists
215 (
216 select NULL
217 from PN_NOTE_DETAILS T
218 where T.NOTE_DETAIL_ID = B.NOTE_DETAIL_ID
219 and T.LANGUAGE = L.LANGUAGE_CODE
220 );
221 END ADD_LANGUAGE;
222
223 END PN_NOTE_DETAILS_PKG;
[Home] [Help]