[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_FA_BOOKS_PKG
Source
1 PACKAGE BODY jl_zz_fa_books_pkg AS
2 /* $Header: jlzzffbb.pls 120.7 2006/03/06 22:34:38 svaze ship $ */
3
4
5 PROCEDURE get_dist_book IS
6 BEGIN
7 SELECT distribution_source_book
8 INTO jl_zz_fa_books_pkg.v_dist_book
9 FROM fa_book_controls
10 WHERE book_type_code = jl_zz_fa_books_pkg.v_book_type_code;
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN NULL;
13 END get_dist_book;
14
15 FUNCTION update_insert RETURN BOOLEAN IS
16 BEGIN
17
18 SELECT global_attribute1,
19 global_attribute2,
23 global_attribute6,
20 global_attribute3,
21 global_attribute4,
22 global_attribute5,
24 global_attribute7,
25 global_attribute8,
26 global_attribute9,
27 global_attribute10,
28 global_attribute11,
29 global_attribute12,
30 global_attribute13,
31 global_attribute14,
32 global_attribute15,
33 global_attribute16,
34 global_attribute17,
35 global_attribute18,
36 global_attribute19,
37 global_attribute20,
38 global_attribute_category
39 INTO jl_zz_fa_books_pkg.v_global_attribute1,
40 jl_zz_fa_books_pkg.v_global_attribute2,
41 jl_zz_fa_books_pkg.v_global_attribute3,
42 jl_zz_fa_books_pkg.v_global_attribute4,
43 jl_zz_fa_books_pkg.v_global_attribute5,
44 jl_zz_fa_books_pkg.v_global_attribute6,
45 jl_zz_fa_books_pkg.v_global_attribute7,
46 jl_zz_fa_books_pkg.v_global_attribute8,
47 jl_zz_fa_books_pkg.v_global_attribute9,
48 jl_zz_fa_books_pkg.v_global_attribute10,
49 jl_zz_fa_books_pkg.v_global_attribute11,
50 jl_zz_fa_books_pkg.v_global_attribute12,
51 jl_zz_fa_books_pkg.v_global_attribute13,
52 jl_zz_fa_books_pkg.v_global_attribute14,
53 jl_zz_fa_books_pkg.v_global_attribute15,
54 jl_zz_fa_books_pkg.v_global_attribute16,
55 jl_zz_fa_books_pkg.v_global_attribute17,
56 jl_zz_fa_books_pkg.v_global_attribute18,
57 jl_zz_fa_books_pkg.v_global_attribute19,
58 jl_zz_fa_books_pkg.v_global_attribute20,
59 jl_zz_fa_books_pkg.v_global_attribute_category
60 FROM fa_books
61 WHERE asset_id = jl_zz_fa_books_pkg.v_asset_id
62 AND book_type_code = jl_zz_fa_books_pkg.v_book_type_code
63 AND transaction_header_id_out = jl_zz_fa_books_pkg.v_transaction_header_id_in ;
64
65 RETURN TRUE;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 RETURN FALSE;
69 END update_insert;
70
71 FUNCTION insert_update RETURN BOOLEAN IS
72 BEGIN
73
74 SELECT global_attribute1,
75 global_attribute2,
76 global_attribute3,
77 global_attribute4,
78 global_attribute5,
79 global_attribute6,
80 global_attribute7,
81 global_attribute8,
82 global_attribute9,
83 global_attribute10,
84 global_attribute11,
85 global_attribute12,
86 global_attribute13,
87 global_attribute14,
88 global_attribute15,
89 global_attribute16,
90 global_attribute17,
91 global_attribute18,
92 global_attribute19,
93 global_attribute20,
94 global_attribute_category
95 INTO jl_zz_fa_books_pkg.v_global_attribute1,
96 jl_zz_fa_books_pkg.v_global_attribute2,
97 jl_zz_fa_books_pkg.v_global_attribute3,
98 jl_zz_fa_books_pkg.v_global_attribute4,
99 jl_zz_fa_books_pkg.v_global_attribute5,
100 jl_zz_fa_books_pkg.v_global_attribute6,
101 jl_zz_fa_books_pkg.v_global_attribute7,
102 jl_zz_fa_books_pkg.v_global_attribute8,
103 jl_zz_fa_books_pkg.v_global_attribute9,
104 jl_zz_fa_books_pkg.v_global_attribute10,
105 jl_zz_fa_books_pkg.v_global_attribute11,
106 jl_zz_fa_books_pkg.v_global_attribute12,
107 jl_zz_fa_books_pkg.v_global_attribute13,
108 jl_zz_fa_books_pkg.v_global_attribute14,
109 jl_zz_fa_books_pkg.v_global_attribute15,
110 jl_zz_fa_books_pkg.v_global_attribute16,
111 jl_zz_fa_books_pkg.v_global_attribute17,
112 jl_zz_fa_books_pkg.v_global_attribute18,
113 jl_zz_fa_books_pkg.v_global_attribute19,
114 jl_zz_fa_books_pkg.v_global_attribute20,
115 jl_zz_fa_books_pkg.v_global_attribute_category
116 FROM fa_books
117 WHERE asset_id = jl_zz_fa_books_pkg.v_asset_id
118 AND book_type_code = jl_zz_fa_books_pkg.v_book_type_code
119 AND date_ineffective IS NULL
120 AND transaction_header_id_in <> jl_zz_fa_books_pkg.v_transaction_header_id_in ;
121
122 RETURN TRUE;
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 RETURN FALSE;
126 END insert_update;
127
128 PROCEDURE update_row IS
129 l_country_code VARCHAR2(10);
130 BEGIN
131
132 --BUG 2876793. Quick Additions does not populate Global Attribute Category.
133 -- This code prevents fa_books.global_attribute_category to
134 -- be null, if the country is AR,CL,CO,MX and the
135 -- fa_books.global_attribute1 has a value.
136
137 IF jl_zz_fa_books_pkg.v_global_attribute_category is NULL THEN
138 --FND_PROFILE.GET('JGZZ_COUNTRY_CODE',l_country_code);
139 -------------------------------------------------------------------------
140 -- BUG 4650081. Profile for country is replaced by call to JG Shared pkg.
141 -------------------------------------------------------------------------
142 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY;
143
144 IF l_country_code in ('AR','CL','CO','MX') THEN
145 --BUG 4618564. Due standards we cannot an schema harcoded, but in this case JL is not part
146 -- of and schema and really is a false positive. However we will decompose the string
147 -- to avoid conflicts with the standards.
148 jl_zz_fa_books_pkg.v_global_attribute_category := 'J'||'L'||'.'||l_country_code||'.FAXASSET.FA_BOOKS';
149
150 IF jl_zz_fa_books_pkg.v_global_attribute1 is NULL THEN
151 jl_zz_fa_books_pkg.v_global_attribute1 := 'N';
152 END IF;
153 END IF;
154
155 END IF;
156
157 UPDATE fa_books
158 SET global_attribute1 = decode(global_attribute1,null,
159 jl_zz_fa_books_pkg.v_global_attribute1,global_attribute1),
160 global_attribute2 = decode(global_attribute2,null,
161 jl_zz_fa_books_pkg.v_global_attribute2,global_attribute2),
162 global_attribute3 = decode(global_attribute3,null,
163 jl_zz_fa_books_pkg.v_global_attribute3,global_attribute3),
164 global_attribute4 = decode(global_attribute4,null,
165 jl_zz_fa_books_pkg.v_global_attribute4,global_attribute4),
166 global_attribute5 = decode(global_attribute5,null,
167 jl_zz_fa_books_pkg.v_global_attribute5,global_attribute5),
168 global_attribute6 = decode(global_attribute6,null,
169 jl_zz_fa_books_pkg.v_global_attribute6,global_attribute6),
170 global_attribute7 = decode(global_attribute7,null,
171 jl_zz_fa_books_pkg.v_global_attribute7,global_attribute7),
172 global_attribute8 = decode(global_attribute8,null,
173 jl_zz_fa_books_pkg.v_global_attribute8,global_attribute8),
174 global_attribute9 = decode(global_attribute9,null,
175 jl_zz_fa_books_pkg.v_global_attribute9,global_attribute9),
176 global_attribute10 = decode(global_attribute10,null,
177 jl_zz_fa_books_pkg.v_global_attribute10,global_attribute10),
178 global_attribute11 = decode(global_attribute11,null,
179 jl_zz_fa_books_pkg.v_global_attribute11,global_attribute11),
180 global_attribute12 = decode(global_attribute12,null,
181 jl_zz_fa_books_pkg.v_global_attribute12,global_attribute12),
182 global_attribute13 = decode(global_attribute13,null,
183 jl_zz_fa_books_pkg.v_global_attribute13,global_attribute13),
184 global_attribute14 = decode(global_attribute14,null,
185 jl_zz_fa_books_pkg.v_global_attribute14,global_attribute14),
186 global_attribute15 = decode(global_attribute15,null,
187 jl_zz_fa_books_pkg.v_global_attribute15,global_attribute15),
188 global_attribute16 = decode(global_attribute16,null,
189 jl_zz_fa_books_pkg.v_global_attribute16,global_attribute16),
190 global_attribute17 = decode(global_attribute17,null,
191 jl_zz_fa_books_pkg.v_global_attribute17,global_attribute17),
192 global_attribute18 = decode(global_attribute18,null,
193 jl_zz_fa_books_pkg.v_global_attribute18,global_attribute18),
194 global_attribute19 = decode(global_attribute19,null,
195 jl_zz_fa_books_pkg.v_global_attribute19,global_attribute19),
196 global_attribute20 = decode(global_attribute20,null,
197 jl_zz_fa_books_pkg.v_global_attribute20,global_attribute20),
198 global_attribute_category =decode(global_attribute_category,null,
199 jl_zz_fa_books_pkg.v_global_attribute_category,global_attribute_category)
200 WHERE asset_id = jl_zz_fa_books_pkg.v_asset_id
201 AND book_type_code = jl_zz_fa_books_pkg.v_book_type_code
202 AND transaction_header_id_in = jl_zz_fa_books_pkg.v_transaction_header_id_in ;
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN NULL;
206 END update_row;
207
208 PROCEDURE get_flag_category IS
209 BEGIN
210 SELECT a.global_attribute1
211 INTO jl_zz_fa_books_pkg.v_global_attribute1
212 FROM fa_category_books a, fa_additions b
213 WHERE b.asset_id = jl_zz_fa_books_pkg.v_asset_id
214 AND a.book_type_code = jl_zz_fa_books_pkg.v_book_type_code
215 AND a.category_id = b.asset_category_id;
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN NULL;
218 END get_flag_category;
219
220 PROCEDURE get_ga_dist_book IS
221
222 BEGIN
223 SELECT
224 global_attribute2,
225 global_attribute3,
226 global_attribute4,
227 global_attribute5,
228 global_attribute6,
229 global_attribute7,
230 global_attribute8,
231 global_attribute9,
232 global_attribute10,
233 global_attribute11,
234 global_attribute12,
235 global_attribute13,
236 global_attribute14,
237 global_attribute15,
238 global_attribute16,
239 global_attribute17,
240 global_attribute18,
241 global_attribute19,
242 global_attribute20,
243 global_attribute_category
244 INTO
245 jl_zz_fa_books_pkg.v_global_attribute2,
246 jl_zz_fa_books_pkg.v_global_attribute3,
247 jl_zz_fa_books_pkg.v_global_attribute4,
248 jl_zz_fa_books_pkg.v_global_attribute5,
249 jl_zz_fa_books_pkg.v_global_attribute6,
250 jl_zz_fa_books_pkg.v_global_attribute7,
251 jl_zz_fa_books_pkg.v_global_attribute8,
252 jl_zz_fa_books_pkg.v_global_attribute9,
253 jl_zz_fa_books_pkg.v_global_attribute10,
254 jl_zz_fa_books_pkg.v_global_attribute11,
255 jl_zz_fa_books_pkg.v_global_attribute12,
256 jl_zz_fa_books_pkg.v_global_attribute13,
257 jl_zz_fa_books_pkg.v_global_attribute14,
258 jl_zz_fa_books_pkg.v_global_attribute15,
259 jl_zz_fa_books_pkg.v_global_attribute16,
260 jl_zz_fa_books_pkg.v_global_attribute17,
261 jl_zz_fa_books_pkg.v_global_attribute18,
262 jl_zz_fa_books_pkg.v_global_attribute19,
263 jl_zz_fa_books_pkg.v_global_attribute20,
264 jl_zz_fa_books_pkg.v_global_attribute_category
265 FROM fa_books
266 WHERE asset_id = jl_zz_fa_books_pkg.v_asset_id
267 AND book_type_code = jl_zz_fa_books_pkg.v_dist_book
268 AND date_ineffective IS NULL
269 AND transaction_header_id_in <> jl_zz_fa_books_pkg.v_transaction_header_id_in ;
270
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN NULL;
273 END get_ga_dist_book;
274
275 END jl_zz_fa_books_pkg;