[Home] [Help]
PACKAGE BODY: APPS.PN_DISTRIBUTION_ACCOUNT
Source
1 PACKAGE BODY pn_distribution_account AS
2 -- $Header: PNUPGACB.pls 120.2 2005/12/01 15:02:02 appldev ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : CREATE_ACCOUNTS
6 -- INVOKED FROM :
7 -- PURPOSE :
8 -- HISTORY :
9 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_terms,pn_leases,
10 -- pn_locations with _ALL table.
11 -- 25-OCT-05 Hareesha o ATG mandated changes for SQL literals using dbms_sql.
12 -------------------------------------------------------------------------------
13 PROCEDURE create_accounts (
14 errbuf OUT NOCOPY VARCHAR2 ,
15 retcode OUT NOCOPY VARCHAR2 ,
16 p_chart_of_accounts_id IN NUMBER ,
17 p_lease_class IN VARCHAR2 ,
18 p_lease_num_from IN VARCHAR2 ,
19 p_lease_num_to IN VARCHAR2 ,
20 p_locn_code_from IN VARCHAR2 ,
21 p_locn_code_to IN VARCHAR2 ,
22 p_rec_ccid IN NUMBER ,
23 p_accr_asset_ccid IN NUMBER ,
24 p_lia_ccid IN NUMBER ,
25 p_accr_liab_ccid IN NUMBER
26 ) IS
27
28 l_payment_term_id pn_payment_terms.payment_term_id%TYPE;
29 l_normalize pn_payment_terms.normalize%TYPE;
30 l_org_id NUMBER;
31 l_project_id NUMBER;
32 l_distribution_set_id NUMBER;
33 l_lease_num pn_leases.lease_num%TYPE;
34 l_lease_class_code pn_leases.lease_class_code%TYPE;
35 l_lia_rec_class pn_distributions.account_class%TYPE;
36 l_lia_rec_acc pn_distributions.account_id%TYPE;
37 l_accr_class pn_distributions.account_class%TYPE;
38 l_accr_acc pn_distributions.account_id%TYPE;
39 l_count NUMBER := 0;
40 l_total_count NUMBER := 0;
41 l_context VARCHAR2(2000);
42 /* v_where_clause VARCHAR2(2000):= NULL; */
43 l_primary_flag VARCHAR2(30) := 'Y';
44 l_accr_acc_exists VARCHAR2(30) := 'Y';
45 l_lia_rec_acc_exists VARCHAR2(30) := 'Y';
46 l_err_msg VARCHAR2(2000);
47 l_cursor INTEGER;
48 l_statement VARCHAR2(10000);
49 l_rows INTEGER;
50 l_count1 INTEGER;
51 l_lease_num_from VARCHAR2(30);
52 l_lease_num_to VARCHAR2(30);
53 l_locn_code_from VARCHAR2(90);
54 l_locn_code_to VARCHAR2(90);
55 x_primary_flag VARCHAR2(30);
56 x_lease_class VARCHAR2(30);
57
58
59 BEGIN
60
61 pnp_debug_pkg.log('pn_distribution_account.create_accounts (+)' );
62
63 fnd_message.set_name ('PN','PN_UPGAC_INP_PRM');
64 fnd_message.set_token ('LS_CLASS',p_lease_class);
65 fnd_message.set_token ('LS_FRM',p_lease_num_from);
66 fnd_message.set_token ('LS_TO',p_lease_num_to);
67 fnd_message.set_token ('LOC_FRM',p_locn_code_from);
68 fnd_message.set_token ('LOC_TO',p_locn_code_to);
69 fnd_message.set_token ('LIA_ACC',TO_CHAR(p_lia_ccid));
70 fnd_message.set_token ('LIB_ACC',TO_CHAR(p_accr_liab_ccid));
71 fnd_message.set_token ('REC_ACC',TO_CHAR(p_rec_ccid));
72 fnd_message.set_token ('ASS_ACC',TO_CHAR(p_accr_asset_ccid));
73 pnp_debug_pkg.put_log_msg(fnd_message.get);
74
75
76 IF (p_lease_class IS NULL AND
77 (p_rec_ccid IS NULL OR
78 p_accr_asset_ccid IS NULL OR
79 p_lia_ccid IS NULL OR
80 p_accr_liab_ccid IS NULL ))
81 OR ( p_lease_class = 'DIRECT' AND
82 (p_lia_ccid IS NULL OR
83 p_accr_liab_ccid IS NULL))
84 OR ( p_lease_class IN ('SUB_LEASE','THIRD_PARTY') AND
85 (p_rec_ccid IS NULL OR
86 p_accr_asset_ccid IS NULL)) THEN
87
88 fnd_message.set_name ('PN', 'PN_ALL_ACNT_DIST_MSG');
89 l_err_msg := fnd_message.get;
90 pnp_debug_pkg.put_log_msg(l_err_msg);
91 RETURN;
92 END IF;
93
94
95 l_context := 'forming where clause';
96
97 l_cursor := dbms_sql.open_cursor;
98 l_statement :=
99 'SELECT ppt.payment_term_id,
100 ppt.normalize,
101 pl.lease_num,
102 ppt.org_id,
103 ppt.distribution_set_id,
104 ppt.project_id,
105 pl.lease_class_code
106 FROM pn_payment_terms ppt,
107 pn_tenancies_all pt,
108 pn_locations_all pln,
109 pn_leases_all pl
110 WHERE ppt.lease_id = pl.lease_id
111 AND pt.lease_id (+) = pl.lease_id
112 AND pt.location_id = pln.location_id (+)
113 AND pt.primary_flag (+) = :x_primary_flag
114 AND SYSDATE between NVL(pt.occupancy_date,SYSDATE)
115 AND NVL(pt.expiration_date,
116 TO_DATE(''12/31/4712'',''mm/dd/yyyy''))
117 AND pl.lease_class_code = NVL(:x_lease_class,pl.lease_class_code)';
118
119
120 x_primary_flag := l_primary_flag;
121 x_lease_class := p_lease_class;
122
123 IF p_lease_num_from IS NOT NULL THEN
124 l_lease_num_from := p_lease_num_from;
125 l_statement :=
126 l_statement || ' AND lease_num >= :l_lease_num_from ';
127
128 END IF;
129
130 IF p_lease_num_to IS NOT NULL THEN
131 l_lease_num_to := p_lease_num_to;
132 l_statement :=
133 l_statement || ' AND lease_num <= :l_lease_num_to ';
134
135 END IF;
136
137 IF p_locn_code_from IS NOT NULL THEN
138 l_locn_code_from := p_locn_code_from;
139 l_statement :=
140 l_statement || ' AND location_code >= :l_locn_code_from ';
141
142 END IF;
143
144 IF p_locn_code_to IS NOT NULL THEN
145 l_locn_code_to := p_locn_code_to;
146 l_statement :=
147 l_statement || ' AND location_code <= :l_locn_code_to ';
148
149 END IF;
150
151 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
152
153 dbms_sql.bind_variable
154 (l_cursor,'x_primary_flag',x_primary_flag );
155 dbms_sql.bind_variable
156 (l_cursor,'x_lease_class',x_lease_class );
157
158 IF p_lease_num_from IS NOT NULL THEN
159 dbms_sql.bind_variable
160 (l_cursor,'l_lease_num_from',l_lease_num_from );
161 END IF;
162
163 IF p_lease_num_to IS NOT NULL THEN
164 dbms_sql.bind_variable
165 (l_cursor,'l_lease_num_to',l_lease_num_to );
166 END IF;
167
168 IF p_locn_code_from IS NOT NULL THEN
169 dbms_sql.bind_variable
170 (l_cursor,'l_locn_code_from',l_locn_code_from );
171 END IF;
172
173 IF p_locn_code_to IS NOT NULL THEN
174 dbms_sql.bind_variable
175 (l_cursor,'l_locn_code_to',l_locn_code_to );
176 END IF;
177
178 dbms_sql.define_column (l_cursor, 1,l_payment_term_id);
179 dbms_sql.define_column (l_cursor, 2,l_normalize,1);
180 dbms_sql.define_column (l_cursor, 3,l_lease_num,30);
181 dbms_sql.define_column (l_cursor, 4,l_org_id);
182 dbms_sql.define_column (l_cursor, 5,l_distribution_set_id);
183 dbms_sql.define_column (l_cursor, 6,l_project_id);
184 dbms_sql.define_column (l_cursor, 7,l_lease_class_code,30);
185
186 l_rows := dbms_sql.execute(l_cursor);
187
188 LOOP
189
190 l_count1 := dbms_sql.fetch_rows( l_cursor );
191
192 EXIT WHEN l_count1 <> 1;
193
194 dbms_sql.column_value (l_cursor, 1,l_payment_term_id);
195 dbms_sql.column_value (l_cursor, 2,l_normalize);
196 dbms_sql.column_value (l_cursor, 3,l_lease_num);
197 dbms_sql.column_value (l_cursor, 4,l_org_id);
198 dbms_sql.column_value (l_cursor, 5,l_distribution_set_id);
199 dbms_sql.column_value (l_cursor, 6,l_project_id);
200 dbms_sql.column_value (l_cursor, 7,l_lease_class_code);
201
202 pnp_debug_pkg.log('Processing ... ' );
203 pnp_debug_pkg.log('Lease Num :' || l_lease_num );
204 pnp_debug_pkg.log('Payment Term Id :' || l_payment_term_id );
205 pnp_debug_pkg.log('Lease Class :' || l_lease_class_code );
206
207 IF l_lease_class_code = 'DIRECT' AND l_project_id IS NULL AND l_distribution_set_id IS NULL
208 THEN
209
210 l_context := 'setting Liability A/Cs ';
211
212 l_lia_rec_class := 'LIA';
213 l_lia_rec_acc := p_lia_ccid;
214 l_accr_class := 'ACC';
215 l_accr_acc := p_accr_liab_ccid ;
216
217 ELSIF l_lease_class_code IN ('SUB_LEASE','THIRD_PARTY') THEN
218
219 l_context := 'setting Receivables A/Cs ';
220
221 l_lia_rec_class := 'REC';
222 l_lia_rec_acc := p_rec_ccid;
223 l_accr_class := 'UNEARN';
224 l_accr_acc := p_accr_asset_ccid;
225
226 END IF;
227 l_count := l_count + 1;
228
229 IF (l_lease_class_code = 'DIRECT'
230 AND l_project_id IS NULL AND l_distribution_set_id IS NULL) OR
231 (l_lease_class_code IN ('SUB_LEASE','THIRD_PARTY')) THEN
232
233 /* Create an Liability/Receivable a/c distribution */
234
235 pnp_debug_pkg.log('Creating Liability/Receivable A/C ... ' );
236
237 l_context := 'Creating Liability/Receivables A/C ';
238
239 savepoint create_accnts;
240
241 create_accnt_dist (p_payment_term_id => l_payment_term_id,
242 p_accnt_class => l_lia_rec_class,
243 p_accnt_ccid => l_lia_rec_acc,
244 p_percent => 100,
245 p_org_id => l_org_id,
246 p_accnt_exists => l_lia_rec_acc_exists);
247
248 /* Create an accrued asset/accrued liability a/c distribution */
249
250 pnp_debug_pkg.log('Creating Accrued Liability/ Accrued Asset A/C ... ' );
251
252 l_context := 'Creating Accrued Liability/ Accrued Receivables A/C ';
253
254 create_accnt_dist (p_payment_term_id => l_payment_term_id,
255 p_accnt_class => l_accr_class,
256 p_accnt_ccid => l_accr_acc,
257 p_percent => 100,
258 p_org_id => l_org_id,
259 p_accnt_exists => l_accr_acc_exists);
260
261 IF l_lia_rec_acc_exists = 'Y' AND l_accr_acc_exists = 'Y' THEN
262 l_count := l_count - 1;
263 END IF;
264
268
265 END IF;
266
267 IF l_count = 1000 THEN
269 l_context := 'Commiting for count of 100';
270 pnp_debug_pkg.log('commiting for count of 100 ... ' );
271 COMMIT;
272 l_total_count := l_total_count + l_count;
273 l_count := 0;
274
275 END IF;
276
277 END LOOP;
278
279 l_context := 'exiting from loop';
280
281 IF dbms_sql.is_open (l_cursor) THEN
282 dbms_sql.close_cursor (l_cursor);
283 END IF;
284
285 COMMIT;
286 fnd_message.set_name ('PN','PN_UPGAC_PROC');
287 fnd_message.set_token ('NUM', TO_CHAR(l_total_count));
288 pnp_debug_pkg.put_log_msg(fnd_message.get);
289
290 pnp_debug_pkg.log('pn_distribution_account.create_accounts (-)' );
291
292 EXCEPTION
293
294 WHEN OTHERS THEN
295 pnp_debug_pkg.log(SUBSTRB('Error IN create_accounts - ' || TO_CHAR(sqlcode) || ' - '|| l_context,1,244));
296 errbuf := SUBSTRB('Error - ' || TO_CHAR(sqlcode) || ' - '|| l_context,1,244);
297 Retcode := sqlcode;
298 ROLLBACK TO create_accnts;
299 RAISE;
300
301 END create_accounts;
302
303 -------------------------------------------------------------------------------
304 -- PROCDURE : CREATE_ACCNT_DIST
305 -- INVOKED FROM :
306 -- PURPOSE :
307 -- HISTORY :
308 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_distributions with _ALL.
309 -- 22-NOV-05 Hareesha o Replaced _all with secured synonyms/baseviews.
310 -------------------------------------------------------------------------------
311 PROCEDURE create_accnt_dist (
312 p_payment_term_id IN NUMBER ,
313 p_accnt_class IN VARCHAR2 ,
314 p_accnt_ccid IN NUMBER ,
315 p_percent IN NUMBER ,
316 p_org_id IN NUMBER ,
317 p_accnt_exists OUT NOCOPY VARCHAR2
318 ) IS
319
320 CURSOR chk_exists IS
321 SELECT'Y'
322 FROM dual
323 WHERE EXISTS (SELECT NULL
324 FROM pn_distributions_all
325 WHERE payment_term_id = p_payment_term_id
326 AND account_class = p_accnt_class);
327
328 l_exists VARCHAR2(30) := 'N';
329 l_context VARCHAR2(2000);
330 l_line_number pn_distributions.line_number%TYPE;
331
332 CURSOR org_cur IS
333 SELECT org_id FROM pn_payment_terms_all WHERE payment_term_ID = p_payment_term_id;
334
335 l_org_ID NUMBER;
336 BEGIN
337
338 pnp_debug_pkg.log('pn_distribution_account.create_accnt_dist (+)' );
339
340 l_context := 'Opening cursor';
341 OPEN chk_exists;
342
343 l_context := 'fetching cursor';
344 FETCH chk_exists INTO l_exists;
345
346 CLOSE chk_exists;
347
348 p_accnt_exists := l_exists;
349
350 IF l_exists = 'N' THEN
351
352 /* get the line NUMBER */
353
354 l_context := 'getting line NUMBER';
355 SELECT NVL(MAX (line_number),0) + 1
356 INTO l_line_number
357 FROM pn_distributions_all
358 WHERE payment_term_id = p_payment_term_id;
359
360 pnp_debug_pkg.log('Line Number : ' || TO_CHAR(l_line_number) );
361
362 l_context := 'inserting INTO dist.';
363
364 IF p_org_ID IS NULL THEN
365 FOR rec IN org_cur LOOP
366 l_org_ID := rec.org_id;
367 END LOOP;
368 ELSE
369 l_org_ID := p_org_id;
370 END IF;
371
372 INSERT INTO pn_distributions_all (distribution_id,
373 payment_term_id,
374 account_id,
375 account_class,
376 percentage,
377 line_number,
378 last_update_date,
379 last_update_login,
380 last_updated_by,
381 creation_date,
382 created_by,
383 org_id)
384 VALUES (pn_distributions_s.nextval,
385 p_payment_term_id,
386 p_accnt_ccid,
387 p_accnt_class,
388 p_percent,
389 l_line_number,
390 SYSDATE,
391 FND_GLOBAL.LOGIN_ID,
392 FND_GLOBAL.USER_ID,
393 SYSDATE,
394 FND_GLOBAL.USER_ID,
395 l_org_ID);
396
397 pnp_debug_pkg.log('Inserted INTO pn_distributions ...' );
398 pnp_debug_pkg.log('Payment Term Id : '|| TO_CHAR(p_payment_term_id) );
399 pnp_debug_pkg.log('Account Class : '|| p_accnt_class );
400 pnp_debug_pkg.log('Account Id : '|| TO_CHAR(p_accnt_ccid) );
401 pnp_debug_pkg.log('Percentage : '|| TO_CHAR(p_percent) );
402 pnp_debug_pkg.log('Org. Id : '|| TO_CHAR(p_org_id) );
403
404 END IF;
405
406 l_context := 'done inserting INTO dist.';
407 pnp_debug_pkg.log('pn_distribution_account.create_accnt_dist (-)' );
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 pnp_debug_pkg.log(SUBSTRB('Error IN create_accnt_dist - ' || TO_CHAR(sqlcode) || ' - '|| l_context,1,244));
415 END create_accnt_dist;
412 ROLLBACK;
413 RAISE;
414
416
417 END pn_distribution_account;