1 PACKAGE BODY XTR_CLEAR_JOURNAL_PROCESS_P as
2 /* $Header: xtrcljnb.pls 120.3 2005/06/29 06:01:05 badiredd ship $ */
3 ----------------------------------------------------------------------------------------------------------------
4
5 /* ---------------------------------------------------------------------
6 | PUBLIC PROCEDURE |
7 | Clear_Journal_Process |
8 | |
9 | DESCRIPTION |
10 | This procedure merely accepts input parameters and performs |
11 | necessary data conversion then calls the main procedure |
12 | CLEAR_JOURNALS to clear journals created within the input date |
13 | range. |
14 | CALLED BY |
15 | Concurrent program submission manager and XTRACJNL. |
16 | PARAMETERS |
17 | p_company_code company code. (required) |
18 | p_start_date date. (required) |
19 | p_end_date date. (required) |
20 | HISTORY |
21 | 05/19/99 eklau Created |
22 --------------------------------------------------------------------- */
23
24
25 PROCEDURE Clear_Journal_Process
26 (errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY NUMBER,
28 p_company_code IN VARCHAR2,
29 p_batch_id_from IN NUMBER,
30 p_batch_id_to IN NUMBER) IS
31
32 p_curr_batch_id XTR_BATCHES.BATCH_ID%TYPE;
33 p_period_start DATE;
34 p_period_end DATE;
35 p_event_code XTR_BATCH_EVENTS.EVENT_CODE%TYPE :='JRNLGN';
36
37 CURSOR BATCH_SEQ is
38 Select batch_id
39 From XTR_BATCHES
40 Where company_code = p_company_code
41 and batch_id between p_batch_id_from and p_batch_id_to
42 Order by batch_id desc;
43
44 CURSOR BATCH_PERIOD is
45 Select period_start, period_end
46 From XTR_BATCHES
47 Where batch_id = p_curr_batch_id;
48
49 CURSOR CHK_TRANS_GL is -- Check if the current batch has been transferred
50 Select 1
51 From XTR_BATCHES
52 Where batch_id = p_curr_batch_id
53 and gl_group_id is not null;
54
55 CURSOR FIND_LATE_BATCH is
56 Select e.batch_id
57 From XTR_BATCHES B, XTR_BATCH_EVENTS E
58 Where b.batch_id = e.batch_id
59 and e.batch_id > p_curr_batch_id
60 and b.company_code = p_company_code
61 and e.event_code = p_event_code
62 and b.batch_type is null
63 order by e.batch_id asc;
64 l_late_batch_id XTR_BATCHES.BATCH_ID%TYPE;
65
66 /* CURSOR CHK_LATE_BATCH is
67 Select 1
68 From XTR_BATCHES
69 where batch_id = l_late_batch_id
70 and gl_group_id is not null; */
71
72 ex_late_batch EXCEPTION;
73 ex_trans_gl EXCEPTION;
74 l_temp NUMBER;
75 v_batch_type Xtr_Batches.Batch_Type%Type;
76
77 --
78 BEGIN
79 -- xtr_debug_pkg.enable_debug (null,null);
80 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
81 xtr_debug_pkg.debug('>>XTR_CLEAR_JOURNAL_PROCESS_P.Clear_Journal_Process');
82 END IF;
83
84 Open BATCH_SEQ;
85 Fetch BATCH_SEQ into p_curr_batch_id;
86 While BATCH_SEQ%FOUND LOOP -- Delete Batches.Start from greatest ID.
87
88 -- Raise exception if the deleted batch id has been transferred to GL
89 Open CHK_TRANS_GL;
90 Fetch CHK_TRANS_GL into l_temp;
91 If CHK_TRANS_GL%FOUND then
92 Close CHK_TRANS_GL;
93 Raise ex_trans_gl;
94 Else
95 Close CHK_TRANS_GL;
96 End if;
97
98 Select batch_type
99 Into v_batch_type
100 From Xtr_Batches
101 Where batch_id = p_curr_batch_id;
102
103 If nvl(v_batch_type, 'R') <> 'J' then
104 -- Raise exception if the deleted batch id which has later batch id existed.
105 Open FIND_LATE_BATCH;
106 Fetch FIND_LATE_BATCH into l_late_batch_id;
107 If FIND_LATE_BATCH%FOUND then
108 Close FIND_LATE_BATCH;
109 Raise ex_late_batch;
110 Else
111 Close FIND_LATE_BATCH;
112 End if;
113 End if;
114
115 G_company_code := p_company_code;
116 G_batch_id := p_curr_batch_id;
117 -- G_start_date := p_period_start;
118 -- G_end_date := p_period_end;
119
120 Clear_Journals(G_company_code,G_batch_id);
121
122 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
123 xtr_debug_pkg.debug('<<XTR_CLEAR_JOURNAL_PROCESS_P.Clear_Journal_Process');
124 END IF;
125
126 Fetch BATCH_SEQ into p_curr_batch_id;
127 END LOOP;
128 Close BATCH_SEQ;
129
130
131 EXCEPTION
132 when ex_trans_gl then
133 FND_MESSAGE.Set_Name('XTR', 'XTR_TRANS_GL');
134 FND_MESSAGE.Set_Token('BATCH', p_curr_batch_id);
135 APP_EXCEPTION.Raise_exception;
136 when ex_late_batch then
137 FND_MESSAGE.Set_Name('XTR', 'XTR_LATE_BATCH');
138 FND_MESSAGE.Set_Token('CUR_BATCH', p_curr_batch_id);
139 FND_MESSAGE.Set_Token('LATE_BATCH', l_late_batch_id);
140 APP_EXCEPTION.Raise_exception;
141
142 END Clear_Journal_Process;
143
144 -------------------------------------------------------------------------------------------------------------
145 /* ---------------------------------------------------------------------
146 | PUBLIC PROCEDURE |
147 | Clear_Journals |
148 | |
149 | DESCRIPTION |
150 | Procedure to delete non-transferred journal entries for the |
151 | specified company and date range and reset the 'journal_created'|
152 | flag in DDA to allow for re-generation of journals at a later |
153 | date. |
154 | CALLED BY |
155 | Clear_Journal_Process |
156 | PARAMETERS |
157 | in_company company code. (required) |
158 | in_start_date date. (required) |
159 | in_end_date date. (required) |
160 | HISTORY |
161 | 05/19/99 eklau Created. |
162 | 06/15/99 eklau Added logic to exclude clearing journal |
163 | lines with status = 'R' in column |
164 | JNL_REVERSAL_IND. These are reversal |
165 | entries for closed/cancelled deals which|
166 | no longer have associating DDA rows. |
167 --------------------------------------------------------------------- */
168
169 PROCEDURE CLEAR_JOURNALS
170 (in_company IN VARCHAR2,
171 in_batch_id IN NUMBER) is
172
173 Cursor GET_BATCH_TYPE is
174 Select batch_type
175 from xtr_batches
176 where batch_id = in_batch_id;
177
178 l_batch_type XTR_BATCHES.batch_type%TYPE := null;
179 p_event_code XTR_BATCH_EVENTS.EVENT_CODE%TYPE :='JRNLGN';
180 --
181 Begin
182 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
183 xtr_debug_pkg.debug('>>XTR_CLEAR_JOURNAL_PROCESS_P.Clear_Journals');
184 xtr_debug_pkg.debug('CLEAR_JOURNALS: ' || 'Current Batch = ' || to_char(in_batch_id));
185 END IF;
186
187 -- Turn BATCH_ID to null value once we delete journals.
188 Update XTR_DEAL_DATE_AMOUNTS
189 Set BATCH_ID = null
190 Where company_code = in_company
191 and batch_id = in_batch_id;
192
193 --Remove jnl lines in JOURNALS after successful reset DDA batch ID to null.
194
195 Delete from XTR_JOURNALS
196 Where company_code = in_company
197 and batch_id = in_batch_id;
198
199 -- Update XTR_BATCH_EVENTS, remove the journal_generate row
200 Delete from XTR_BATCH_EVENTS
201 Where batch_id = in_batch_id
202 and event_code = p_event_code;
203
204 -- Flex Journals.
205 -- If Non-Reval/Non-Accrual related batch, need to remove existence
206 -- of batch from XTR_BATCHES also.
207
208 Open GET_BATCH_TYPE;
209 Fetch GET_BATCH_TYPE into l_batch_type;
210 Close GET_BATCH_TYPE;
211
212 If (nvl(l_batch_type,'X') = 'J') then
213 Delete from XTR_BATCHES
214 Where batch_id = in_batch_id;
215 End If;
216
217 Commit;
218
219 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
220 xtr_debug_pkg.debug('<<XTR_CLEAR_JOURNAL_PROCESS_P.Clear_Journals');
221 END IF;
222
223 End CLEAR_JOURNALS;
224
225 --------------------------------------------------------------------------------------------------------------------------
226 END XTR_CLEAR_JOURNAL_PROCESS_P;
[Home] [Help]