[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_CANCEL_BORDERO
Source
1 PACKAGE BODY JL_BR_AR_CANCEL_BORDERO AS
2 /*$Header: jlbrrcab.pls 120.10 2011/07/01 19:42:20 abuissa ship $*/
3
4 PROCEDURE cancel_bordero (
5 param_select_control IN number,
6 param_bordero_id IN number,
7 param_bordero_status IN varchar2,
8 param_select_account_id IN number,
9 param_option IN varchar2,
10 param_date IN date,
11 param_exit OUT NOCOPY number)
12 IS
13 var_document_status VARCHAR2(30);
14 var_selection_status VARCHAR2(30);
15 var_bordero_action VARCHAR2(1);
16 var_status VARCHAR2(30);
17 var_previous_status VARCHAR2(30);
18 var_bordero_status VARCHAR2(30);
19 var_bordero_status_chk VARCHAR2(30);
20 var_selection_control_chk NUMBER;
21 flag NUMBER(1);
22 var_cancel_date DATE;
23 var_payment_schedule NUMBER(15);
24 var_document_id NUMBER(38);
25 var_gl_date DATE;
26 var_occurrence_id NUMBER(38);
27 var_gl_date_cancel DATE;
28 var_flag_post_gl VARCHAR2(1);
29 var_bordero_type VARCHAR2(30);
30 var_event_type_code VARCHAR2(30); -- SLA Uptake - Bug#4301543
31 var_occurrence_type VARCHAR2(30); -- SLA Uptake - Bug#4301543
32 var_occurrence_code NUMBER(2); -- SLA Uptake - Bug#4301543
33 var_cancel_event_id NUMBER; -- SLA Uptake - Bug#4301543
34 var_cash_receipt_id NUMBER(15);
35 l_return_status VARCHAR2(30);
36 l_msg_count NUMBER(15);
37 l_msg_data VARCHAR2(100);
38 var_event_id NUMBER; -- bug 10349927
39
40 /* Cursor c1 - This cursor is used to check the bordero status */
41 /* of all borderos with the same select account id */
42 /* and selection control id */
43 /* Cursor c3 - This cursor is used to change the document status */
44 /* previous_doc_status, and cancellation date */
45 /* of all documents from the same bordero that is */
46 /* being canceled, since this document was not */
47 /* canceled yet. */
48 /* Cursor c4 - This cursor is used to remittance occurrences from */
49 /* all documents of one bordero. */
50
51 CURSOR check1 IS
52 SELECT bordero_status,
53 bordero_type
54 FROM jl_br_ar_borderos
55 WHERE bordero_id = param_bordero_id
56 AND bordero_status in ('SELECTED', 'FORMATTED')
57 FOR UPDATE NOWAIT;
58
59 CURSOR check2 IS
60 SELECT selection_control_id
61 FROM jl_br_ar_select_controls
62 WHERE selection_control_id = param_select_control
63 FOR UPDATE NOWAIT;
64
65 CURSOR c1 IS
66 SELECT bordero_status
67 FROM jl_br_ar_borderos
68 WHERE selection_control_id = param_select_control
69 AND select_account_id = param_select_account_id;
70 CURSOR c3 IS
71 SELECT document_status,
72 payment_schedule_id,
73 document_id
74 FROM jl_br_ar_collection_docs
75 WHERE bordero_id = param_bordero_id
76 AND document_status <> 'CANCELED';
77 CURSOR c4 IS
78 SELECT gl_date,
79 occurrence_id,
80 gl_cancel_date,
81 flag_post_gl,
82 bank_occurrence_type, -- SLA Uptake - Bug#4301543
83 bank_occurrence_code, -- SLA Uptake - Bug#4301543
84 event_id -- bug 10349927
85 FROM jl_br_ar_occurrence_docs
86 WHERE document_id = var_document_id
87 AND bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
88 AND occurrence_status <> 'CANCELED';
89 BEGIN
90 flag := 0;
91 var_selection_status := 'CANCELED';
92 param_exit := 0;
93
94 OPEN check1;
95 LOOP
96 FETCH check1 INTO var_bordero_status_chk,var_bordero_type;
97 EXIT WHEN check1%NOTFOUND;
98
99 IF param_option = 'Reverse Bordero' AND
100 var_bordero_status_chk <> 'FORMATTED' THEN
101 param_exit := -1;
102 EXIT;
103 END IF;
104
105 OPEN check2;
106 LOOP
107 FETCH check2 INTO var_selection_control_chk;
108 EXIT WHEN check2%NOTFOUND;
109
110 /* Reverse Bordero status to SELECTED */
111 IF param_option = 'Reverse Bordero' THEN
112 var_document_status := 'SELECTED';
113 var_bordero_action := 'Y';
114 var_status := 'SELECTED';
115 var_cancel_date := NULL;
116 /* Change Bordero status to CANCELED */
117 ELSIF param_option = 'Cancel Bordero' THEN
118 var_document_status := 'CANCELED';
119 var_bordero_action := NULL;
120 var_status := 'CANCELED';
121 var_cancel_date := sysdate;
122 END IF;
123
124 UPDATE jl_br_ar_borderos SET
125 bordero_status = var_status,
126 cancellation_date = var_cancel_date
127 WHERE bordero_id = param_bordero_id;
128 --commit;
129
130 OPEN c3;
131 LOOP
132 FETCH c3 INTO var_previous_status, var_payment_schedule,var_document_id;
133 EXIT WHEN c3%NOTFOUND;
134
135 IF var_bordero_type = 'FACTORING' THEN
136
137 Ar_receipt_api_pub.reverse(
138 p_api_version => 1.0,
139 p_init_msg_list => FND_API.G_FALSE,
140 x_return_status => l_return_status,
141 x_msg_count => l_msg_count,
142 x_msg_data => l_msg_data,
143 p_cash_receipt_id => var_cash_receipt_id,
144 p_reversal_category_code => 'REV',
145 p_reversal_category_name => 'Reverse Payment',
146 p_reversal_gl_date => sysdate,
147 p_reversal_date => sysdate,
148 p_called_from => 'Factoring'
149 );
150
151 END IF;
152
153 UPDATE jl_br_ar_collection_docs SET
154 previous_doc_status = var_previous_status,
155 document_status = var_document_status,
156 cancellation_date = var_cancel_date
157 WHERE bordero_id = param_bordero_id
158 AND payment_schedule_id = var_payment_schedule
159 AND document_id = var_document_id;
160
161 IF var_bordero_action = 'Y' THEN
162 UPDATE AR_PAYMENT_SCHEDULES SET
163 global_attribute12 = var_document_status, --bug 12606008
164 global_attribute11 = 'N',
165 global_attribute9 = 'MANUAL_RECEIPT' --bug 8971675
166 WHERE payment_schedule_id = var_payment_schedule;
167 ELSE
168 UPDATE AR_PAYMENT_SCHEDULES SET
169 global_attribute12 = var_document_status, --bug 12606008
170 global_attribute13 = NULL, --bug 12636807
171 global_attribute14 = NULL, --bug 12636807
172 selected_for_receipt_batch_id = NULL,
173 global_attribute11 = 'N',
174 global_attribute9 = 'MANUAL_RECEIPT' --bug 8971675
175 WHERE payment_schedule_id = var_payment_schedule;
176 END IF;
177
178 IF param_bordero_status = 'FORMATTED' THEN
179 OPEN c4;
180 LOOP
181 FETCH c4 INTO var_gl_date,
182 var_occurrence_id,
183 var_gl_date_cancel,
184 var_flag_post_gl,
185 var_occurrence_type, -- SLA Uptake - Bug#4301543
186 var_occurrence_code, -- SLA Uptake - Bug#4301543
187 var_event_id; -- bug 10349927
188
189 EXIT WHEN c4%NOTFOUND;
190 var_gl_date_cancel := param_date;
191 IF var_flag_post_gl = 'Y' THEN
192 var_flag_post_gl := 'N';
193 var_gl_date := param_date;
194 END IF;
195
196 -- SLA Uptake - Bug#4301543
197 if var_bordero_type = 'FACTORING' then
198 var_event_type_code := 'CANCEL_FACT_DOC';
199 else
200 var_event_type_code := 'CANCEL_COLL_DOC';
201 end if;
202
203 IF var_event_id IS NOT NULL THEN -- bug 10349927
204 -- Below call is to create cancellation event. So, Occurreces for which accounting is not happened yet can be ignored
205
206 JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists (
207 p_event_type_code => var_event_type_code ,
208 p_event_date => NVL(var_cancel_date,SYSDATE) ,
209 p_document_id => var_document_id ,
210 p_gl_date => var_gl_date ,
211 p_occurrence_id => var_occurrence_id ,
212 p_bank_occurrence_type => var_occurrence_type ,
213 p_bank_occurrence_code => var_occurrence_code ,
214 p_std_occurrence_code => 'REMITTANCE' ,
215 p_bordero_type => var_bordero_type ,
216 p_endorsement_amt => NULL ,
217 p_bank_charges_amt => NULL ,
218 p_factoring_charges_amt => NULL ,
219 p_event_id => var_cancel_event_id
220 );
221 END IF; -- bug 10349927
222 -- End SLA Uptake - Bug#4301543
223
224 UPDATE jl_br_ar_occurrence_docs SET
225 occurrence_status= 'CANCELED',
226 gl_date = var_gl_date,
227 gl_cancel_date = var_gl_date_cancel,
228 flag_post_gl = var_flag_post_gl,
229 cancel_event_id = var_cancel_event_id -- SLA Uptake - Bug#4301543
230 WHERE document_id = var_document_id
231 AND occurrence_id = var_occurrence_id;
232 --commit;
233 END LOOP;
234 CLOSE c4;
235 END IF;
236 --commit;
237 END LOOP;
238 CLOSE c3;
239
240 var_cancel_date := sysdate;
241
242 OPEN c1;
243 LOOP
244 FETCH c1 INTO var_bordero_status;
245 EXIT WHEN c1%NOTFOUND;
246
247 IF var_bordero_status <> 'CANCELED' THEN
248 IF var_bordero_status <> 'SELECTED' THEN
249 flag := 2;
250 ELSE
251 flag := 1;
252 var_selection_status := 'SELECTED';
253 var_cancel_date := NULL;
254 END IF;
255 END IF;
256 END LOOP;
257 CLOSE c1;
258
259 IF flag = 0 OR flag = 1 THEN
260 UPDATE jl_br_ar_select_accounts SET
261 cancellation_date = var_cancel_date
262 WHERE selection_control_id = param_select_control
263 AND select_account_id = param_select_account_id;
264
265 UPDATE jl_br_ar_select_controls SET
266 selection_status = var_selection_status,
267 cancellation_date = var_cancel_date
268 WHERE selection_control_id = param_select_control;
269 --commit;
270 END IF;
271
272 param_exit := 1;
273
274 END LOOP;
275 CLOSE check2;
276
277 END LOOP;
278 CLOSE check1;
279
280 commit;
281
282 END cancel_bordero;
283
284 END JL_BR_AR_CANCEL_BORDERO;