[Home] [Help]
PACKAGE BODY: APPS.AR_DUNNING_LETTERS_CUSTOM_PKG
Source
1 package body AR_DUNNING_LETTERS_CUSTOM_PKG as
2 /* $Header: ARPDLCSB.pls 120.2.12000000.2 2007/05/16 11:37:36 tthangav ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DUNNING_LETTER_ID in NUMBER,
6 X_PARAGRAPH_NUMBER in NUMBER,
7 X_PARAGRAPH_TEXT in VARCHAR2,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from AR_DUNNING_LETTERS_CUSTOM_TL
15 where DUNNING_LETTER_ID = X_DUNNING_LETTER_ID
16 and PARAGRAPH_NUMBER = X_PARAGRAPH_NUMBER
17 and LANGUAGE = userenv('LANG')
18 ;
19 begin
20 insert into AR_DUNNING_LETTERS_CUSTOM_TL (
21 DUNNING_LETTER_ID,
22 PARAGRAPH_NUMBER,
23 PARAGRAPH_TEXT,
24 LAST_UPDATE_DATE,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN,
29 LANGUAGE,
30 SOURCE_LANG
31 ) select
32 X_DUNNING_LETTER_ID,
33 X_PARAGRAPH_NUMBER,
34 X_PARAGRAPH_TEXT,
35 X_LAST_UPDATE_DATE,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN,
40 L.LANGUAGE_CODE,
41 userenv('LANG')
42 from FND_LANGUAGES L
43 where L.INSTALLED_FLAG in ('I', 'B')
44 and not exists
45 (select NULL
46 from AR_DUNNING_LETTERS_CUSTOM_TL T
47 where T.DUNNING_LETTER_ID = X_DUNNING_LETTER_ID
48 and T.PARAGRAPH_NUMBER = X_PARAGRAPH_NUMBER
49 and T.LANGUAGE = L.LANGUAGE_CODE);
50
51 open c;
52 fetch c into X_ROWID;
53 if (c%notfound) then
54 close c;
55 raise no_data_found;
56 end if;
57 close c;
58
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_DUNNING_LETTER_ID in NUMBER,
63 X_PARAGRAPH_NUMBER in NUMBER,
64 X_PARAGRAPH_TEXT in VARCHAR2
65 ) is
66 cursor c1 is select
67 PARAGRAPH_TEXT,
68 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
69 from AR_DUNNING_LETTERS_CUSTOM_TL
70 where DUNNING_LETTER_ID = X_DUNNING_LETTER_ID
71 and PARAGRAPH_NUMBER = X_PARAGRAPH_NUMBER
72 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
73 for update of DUNNING_LETTER_ID nowait;
74 begin
75 for tlinfo in c1 loop
76 if (tlinfo.BASELANG = 'Y') then
77 if ( ((tlinfo.PARAGRAPH_TEXT = X_PARAGRAPH_TEXT)
78 OR ((tlinfo.PARAGRAPH_TEXT is null) AND (X_PARAGRAPH_TEXT is null)))
79 ) then
80 null;
81 else
82 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
83 app_exception.raise_exception;
84 end if;
85 end if;
86 end loop;
87 return;
88 end LOCK_ROW;
89
90 procedure UPDATE_ROW (
91 X_DUNNING_LETTER_ID in NUMBER,
92 X_PARAGRAPH_NUMBER in NUMBER,
93 X_PARAGRAPH_TEXT in VARCHAR2,
94 X_LAST_UPDATE_DATE in DATE,
95 X_LAST_UPDATED_BY in NUMBER,
96 X_LAST_UPDATE_LOGIN in NUMBER
97 ) is
98 begin
99 update AR_DUNNING_LETTERS_CUSTOM_TL set
100 PARAGRAPH_TEXT = X_PARAGRAPH_TEXT,
101 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
102 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
103 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
104 SOURCE_LANG = userenv('LANG')
105 where DUNNING_LETTER_ID = X_DUNNING_LETTER_ID
106 and PARAGRAPH_NUMBER = X_PARAGRAPH_NUMBER
107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
108
109 if (sql%notfound) then
110 raise no_data_found;
111 end if;
112 end UPDATE_ROW;
113
114 procedure DELETE_ROW (
115 X_DUNNING_LETTER_ID in NUMBER,
116 X_PARAGRAPH_NUMBER in NUMBER
117 ) is
118 begin
119 delete from AR_DUNNING_LETTERS_CUSTOM_TL
120 where DUNNING_LETTER_ID = X_DUNNING_LETTER_ID
121 and PARAGRAPH_NUMBER = X_PARAGRAPH_NUMBER;
122
123 if (sql%notfound) then
124 raise no_data_found;
125 end if;
126
127 end DELETE_ROW;
128
129 procedure ADD_LANGUAGE
130 is
131 begin
132 update AR_DUNNING_LETTERS_CUSTOM_TL T set (
133 PARAGRAPH_TEXT
134 ) = (select
135 B.PARAGRAPH_TEXT
136 from AR_DUNNING_LETTERS_CUSTOM_TL B
137 where B.DUNNING_LETTER_ID = T.DUNNING_LETTER_ID
138 and B.PARAGRAPH_NUMBER = T.PARAGRAPH_NUMBER
139 and B.LANGUAGE = T.SOURCE_LANG)
140 where (
141 T.DUNNING_LETTER_ID,
142 T.PARAGRAPH_NUMBER,
143 T.LANGUAGE
144 ) in (select
145 SUBT.DUNNING_LETTER_ID,
146 SUBT.PARAGRAPH_NUMBER,
147 SUBT.LANGUAGE
148 from AR_DUNNING_LETTERS_CUSTOM_TL SUBB, AR_DUNNING_LETTERS_CUSTOM_TL SUBT
149 where SUBB.DUNNING_LETTER_ID = SUBT.DUNNING_LETTER_ID
150 and SUBB.PARAGRAPH_NUMBER = SUBT.PARAGRAPH_NUMBER
151 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
152 and (SUBB.PARAGRAPH_TEXT <> SUBT.PARAGRAPH_TEXT
153 or (SUBB.PARAGRAPH_TEXT is null and SUBT.PARAGRAPH_TEXT is not null)
154 or (SUBB.PARAGRAPH_TEXT is not null and SUBT.PARAGRAPH_TEXT is null)
155 ));
156
157 insert into AR_DUNNING_LETTERS_CUSTOM_TL (
158 DUNNING_LETTER_ID,
159 PARAGRAPH_NUMBER,
160 PARAGRAPH_TEXT,
161 LAST_UPDATE_DATE,
162 CREATION_DATE,
163 CREATED_BY,
164 LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN,
166 LANGUAGE,
167 SOURCE_LANG
168 ) select
169 B.DUNNING_LETTER_ID,
170 B.PARAGRAPH_NUMBER,
171 B.PARAGRAPH_TEXT,
172 B.LAST_UPDATE_DATE,
173 B.CREATION_DATE,
174 B.CREATED_BY,
175 B.LAST_UPDATED_BY,
176 B.LAST_UPDATE_LOGIN,
177 L.LANGUAGE_CODE,
178 B.SOURCE_LANG
179 from AR_DUNNING_LETTERS_CUSTOM_TL B, FND_LANGUAGES L
180 where L.INSTALLED_FLAG in ('I', 'B')
181 and B.LANGUAGE = userenv('LANG')
182 and not exists
183 (select NULL
184 from AR_DUNNING_LETTERS_CUSTOM_TL T
185 where T.DUNNING_LETTER_ID = B.DUNNING_LETTER_ID
186 and T.PARAGRAPH_NUMBER = B.PARAGRAPH_NUMBER
187 and T.LANGUAGE = L.LANGUAGE_CODE);
188 end ADD_LANGUAGE;
189
190 procedure LOAD_ROW (
191 X_DUNNING_LETTER_ID in NUMBER,
192 X_PARAGRAPH_NUMBER in NUMBER,
193 X_PARAGRAPH_TEXT in VARCHAR2,
194 X_OWNER in VARCHAR2) IS
195 begin
196
197 declare
198 user_id number := 0;
199 row_id varchar2(64);
200
201 begin
202
203 user_id := fnd_load_util.owner_id(X_OWNER);
204
205 AR_DUNNING_LETTERS_CUSTOM_PKG.UPDATE_ROW (
206 X_DUNNING_LETTER_ID => X_DUNNING_LETTER_ID,
207 X_PARAGRAPH_NUMBER => X_PARAGRAPH_NUMBER,
208 X_PARAGRAPH_TEXT => X_PARAGRAPH_TEXT,
209 X_LAST_UPDATE_DATE => sysdate,
210 X_LAST_UPDATED_BY => user_id,
211 X_LAST_UPDATE_LOGIN => 0);
212
213 exception
214 when NO_DATA_FOUND then
215 AR_DUNNING_LETTERS_CUSTOM_PKG.INSERT_ROW (
216 X_ROWID => row_id,
217 X_DUNNING_LETTER_ID => X_DUNNING_LETTER_ID,
218 X_PARAGRAPH_NUMBER => X_PARAGRAPH_NUMBER,
219 X_PARAGRAPH_TEXT => X_PARAGRAPH_TEXT,
220 X_CREATION_DATE => sysdate,
221 X_CREATED_BY => user_id,
222 X_LAST_UPDATE_DATE => sysdate,
223 X_LAST_UPDATED_BY => user_id,
224 X_LAST_UPDATE_LOGIN => 0);
225 end;
226 end LOAD_ROW;
227
228 procedure TRANSLATE_ROW (
229 X_DUNNING_LETTER_ID in NUMBER,
230 X_PARAGRAPH_NUMBER in NUMBER,
231 X_PARAGRAPH_TEXT in VARCHAR2,
232 X_OWNER in VARCHAR2) IS
233
234 begin
235
236 -- only update rows that have not been altered by user
237
238 update AR_DUNNING_LETTERS_CUSTOM_TL
239 set paragraph_text = X_PARAGRAPH_TEXT,
240 source_lang = userenv('LANG'),
241 last_update_date = sysdate,
242 last_updated_by = fnd_load_util.owner_id(X_OWNER),
243 last_update_login = 0
244 where dunning_letter_id = X_DUNNING_LETTER_ID
245 and paragraph_number = X_PARAGRAPH_NUMBER
246 and userenv('LANG') in (language, source_lang);
247
248 end TRANSLATE_ROW;
249
250 end AR_DUNNING_LETTERS_CUSTOM_PKG;