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