1 PACKAGE BODY JAI_AP_IA_TRIGGER_PKG AS
2 /* $Header: jai_ap_ia_t.plb 120.3.12010000.3 2008/10/14 13:00:02 bgowrava ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARUID_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_AP_IA_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_AP_IA_ARIUD_T2
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARUID_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15 BEGIN
16 pv_return_code := jai_constants.successful ;
17 /*------------------------------------------------------------------------------------------
18 FILENAME: ja_in_ap_aia_after_trg.sql
19
20 CHANGE HISTORY:
21 S.No Date Author and Details
22
23 1. 22/11/2004 Aparajita, created for bug # 3924692. Version # 115.0
24
25 This is the common after row level trigger for all events, that is
26 insert, update and delete.
27
28 Introduced the call to centralized packaged procedure,
29 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
30
31 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
32 DB Entity as required for CASE COMPLAINCE. Version 116.1
33
34 3. 13-Jun-2005 File Version: 116.2
35 Ramananda for bug#4428980. Removal of SQL LITERALs is done
36
37 4. 01-FEB-2007 CSahoo for BUG#5631784, Version 120.1
38 Forward Porting of ii1 BUG#4742259(TCS solution)
39 When an invoice generated during the TCS settlement is paid we update
40 the invoice_id column in the jai_rgm_settlements table with the
41 corresponding invoice_id.
42 Update of invoice_id in jai_rgm_settlements is made when payment_status_flag is
43 Y. Previously it was checking if amount_paid is more than invoice_amount.
44 This would not work if there is any discount.
45 The if condition to update invoice_id in jai_rgm_settlements was modified from
46 IF nvl(:new.payment_status_flag,'N') <> 'Y' AND nvl(:new.payment_status_flag,'N') = 'Y' to
47 IF nvl(:old.payment_status_flag,'N') <> 'Y' AND nvl(:new.payment_status_flag,'N') = 'Y'
48 5 29/03/2007 bduvarag for bug#5662741,File version 120.2
49 Forward porting the changes done in 11i bug#5638769
50
51 6 15-OCT-2007 Bug 6493858 File version 120.0.12000000.3
52 Removed changes done for bug 5662741 and moved the logic to jai_ap_ida_t.plb.
53
54
55 Dependency:
56 ----------
57
58 Sl No. Bug Dependent on
59 Bug/Patch set Details
60 -------------------------------------------------------------------------------------------------
61 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
62 4033992.
63 ja_in_util_pkg_s.sql 115.0
64 ja_in_util_pkg_b.sql 115.0
65 ------------------------------------------------------------------------------------------ */
66
67 --if
68 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIA_AFTER_TRG',
69 -- p_org_id => pr_new.org_id,
70 -- p_set_of_books_id => pr_new.set_of_books_id )
71 -- =
72 -- FALSE
73 --then
74 /* India Localization funtionality is not required */
75 -- return;
76 --end if;
77
78 if pv_action = jai_constants.updating then
79
80 -- Bug 7114863. Added by Lakshmi Gopalsami
81 -- Removed the reference to jai_ap_tolerance_pkg.check_tolerance_hold
82
83 /*BUG#5631784, Added by CSahoo*/
84 IF nvl(pr_old.payment_status_flag,'N') <> 'Y' AND nvl(pr_new.payment_status_flag,'N') = 'Y' AND pr_new.invoice_num like 'TCS%' THEN
85
86 UPDATE jai_rgm_settlements
87 SET invoice_id = pr_new.invoice_id,
88 last_updated_by = fnd_global.user_id,
89 last_update_date = sysdate
90 WHERE system_invoice_no = pr_new.invoice_num
91 AND tax_authority_id = pr_new.vendor_id
92 AND tax_authority_site_id = pr_new.vendor_site_id;
93
94 END IF;/*5631784 end*/
95
96 end if; /* updating */
97
98
99 exception
100 when others then
101 --raise_application_error(-20000, 'Error - trigger ja_in_ap_aia_after_trg on ap_invoices_all : ' || sqlerrm);
102 /* Added an exception block by Ramananda for bug#4570303 */
103 Pv_return_code := jai_constants.unexpected_error;
104 Pv_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARI_T7 ' ||
105 'Error on ap_invoices_all : ' || substr(sqlerrm,1,1800);
106
107 END ARUID_T1 ;
108
109 /*
110 REM +======================================================================+
111 REM NAME BRIUD_T1
112 REM
113 REM DESCRIPTION Called from trigger JAI_AP_IA_BRIUD_T1
114 REM
115 REM NOTES Refers to old trigger JAI_AP_IA_BRIUD_T1
116 REM
117 REM +======================================================================+
118 */
119 PROCEDURE BRIUD_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
120 lb_result boolean;
121 ln_req_id number;
122 lv_process_flag varchar2(20);
123 lv_process_message varchar2(1100);
124 ln_org_id ap_invoices_all.org_id%type;
125 ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
126
127 /*changes for bug 5662741 removed for bug 6493858/6411412*/
128
129 /*START, Bgowrava for Bug#5638773 */
130 CURSOR c_tds_invoice_id(cp_invoice_id NUMBER)
131 IS
132 SELECT invoice_to_tds_authority_id invoice_id, invoice_to_tds_authority_num invoice_num
133 FROM jai_ap_tds_thhold_trxs
134 WHERE invoice_id = cp_invoice_id;
135
136 r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
137 lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
138
139 FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
140 RETURN VARCHAR2
141 IS
142 PRAGMA AUTONOMOUS_TRANSACTION;
143
144 CURSOR c_payment_status(cp_invoice_id NUMBER)
145 IS
146 SELECT payment_status_flag
147 FROM ap_invoices_all
148 WHERE invoice_id = cp_invoice_id;
149
150 r_payment_status c_payment_status%ROWTYPE;
151 BEGIN
152 OPEN c_payment_status(p_invoice_id);
153 FETCH c_payment_status INTO r_payment_status;
154 CLOSE c_payment_status;
155
156 RETURN r_payment_status.payment_status_flag;
157 END get_invoice_payment_status;
158
159 /*END, Bgowrava for Bug#5638773 */
160
161 BEGIN
162 pv_return_code := jai_constants.successful ;
163 /*------------------------------------------------------------------------------------------
164 FILENAME: jai_ap_ia_t.sql
165
166 CHANGE HISTORY:
167 S.No Date Author and Details
168
169 1. 25/03/2004 Aparajita. Bug # 4088186. TDS Clean up. Version#115.0
170
171 This is the only trigger introduced for all the before event
172 on the table on which this is based.
173
174 2. 14/04/2005 4284505 ssumaith - file version 115.1
175
176 Code added for service tax support for 3rd party taxes in a receipt.
177 In this trigger code has been added to populate the invoice id into
178 the jai_Rcv_tp_invoices table based on the invoice_num , vendor and
179 vendor site.
180
181
182 This patch creates dependency by addition of a new table - jai_rcv_tp_inv_Details
183 and addition of new column (invoice_id) in the table jai_Rcv_tp_invoices table.
184
185 A new procedure has been added to the package - jai_rcv_third_party_pkg which does
186 the actual invoice id update , and hence this package should go along with this trigger
187
188 2. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
189 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
190 Changed AP Lookup code from 'RECEIPT' to 'INDIA TAX INVOICE'
191
192 3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
193 DB Entity as required for CASE COMPLAINCE. Version 116.2
194
195 4. 13-Jun-2005 File Version: 116.3
196 Ramananda for bug#4428980. Removal of SQL LITERALs is done
197
198 5. 14/10/2008 Bgowrava for Bug#5638773, file version 120.0.12000000.7, 120.3.12010000.3
199 Issue - Base AP invoice shouldn't be allowed to be cancelled, if the corresponding
200 invoice to TDS Authority is already paid
201 Fix - 1) Added a new cursor - c_tds_invoice_id, to get the tds_invoice_id
202 2) Created an autonomous function to get the payment_status of an invoice
203 3) Added the code to if check the tds_invoice is already paid, then stop the
204 cancelling of base invoice by using - raise_application_error
205
206 Dependency:
207 ----------
208 Sl No. Bug Dependent on
209 Bug/Patch set Details
210 -------------------------------------------------------------------------------------------------
211 1. 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, which was created thru bug
212 4033992.
213 ja_in_util_pkg_s.sql 115.0
214 ja_in_util_pkg_b.sql 115.0
215
216
217
218
219 ------------------------------------------------------------------------------------------ */
220 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
221 ln_org_id := pr_new.org_id;
222 ln_set_of_books_id := pr_new.set_of_books_id;
223 elsif pv_action = jai_constants.deleting then
224 ln_org_id := pr_old.org_id;
225 ln_set_of_books_id := pr_old.set_of_books_id;
226 end if;
227
228 --if
229 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIA_BEFORE_TRG',
230 -- p_org_id => ln_org_id,
231 -- p_set_of_books_id => ln_set_of_books_id )
232 -- =
233 -- FALSE
234 -- then
235 /* India Localization funtionality is not required */
236 -- return;
237 -- end if;
238
239
240 /*
241 || Cancellation Functionality
242 */
243 if pv_action = jai_constants.updating then
244
245 if pr_old.cancelled_date is null and pr_new.cancelled_date is not null then
246
247 if pr_new.source <> 'INDIA TDS' then /* 'TDS' then --Ramanand for bug#4388958 */
248
249 /*START, Bgowrava for Bug#5638773 */
250 OPEN c_tds_invoice_id(pr_old.invoice_id);
251 FETCH c_tds_invoice_id INTO r_tds_invoice_id;
252 CLOSE c_tds_invoice_id;
253
254 IF r_tds_invoice_id.invoice_id IS NOT NULL THEN
255 lv_invoice_payment_status := get_invoice_payment_status(r_tds_invoice_id.invoice_id);
256 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
257 pv_return_code := jai_constants.expected_error ;
258 pv_return_message := 'Invoice to TDS Authority - '||r_tds_invoice_id.invoice_num||' is already paid. Current invoice can''t be cancelled';
259 END IF;
260 END IF;
261
262 /*END, Bgowrava for Bug#5638773 */
263
264 /* TDs functionality on TDS invoice is not required. */
265
266 /*changes for bug 5662741 removed for bug 6493858/6411412*/
267 /*the logic is moved to jai_ap_ida_t.plb*/
268
269 lb_result := fnd_request.set_mode(true);
270
271 ln_req_id :=
272 Fnd_Request.submit_request
273 (
274 'JA',
275 'JAINAPIC',
276 'Cancel TDS invoices',
277 '',
278 false,
279 pr_new.invoice_id
280 );
281
282 end if; /* pr_new.source <> 'TDS' then */
283
284 end if; /* if pr_old.cancelled_date is null and pr_new.cancelled_date is not null then */
285
286 end if; /* if pv_action = jai_constants.updating than */
287
288 /* Update invoice Ids in TDS tables for TDS invoices */
289 if pv_action = jai_constants.inserting and pr_new.source = 'INDIA TDS' then /* 'TDS' then --Ramanand for bug#4388958 */
290
291 jai_ap_tds_generation_pkg.populate_tds_invoice_id
292 (
293 p_invoice_id => pr_new.invoice_id,
294 p_invoice_num => pr_new.invoice_num,
295 p_vendor_id => pr_new.vendor_id,
296 p_vendor_site_id => pr_new.vendor_site_id,
297 p_process_flag => lv_process_flag,
298 p_process_message => lv_process_message
299 );
300
301 if nvl(lv_process_flag, 'N') = 'E' then
302 /* raise_application_error(-20001,
303 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
304 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message ; return ;
305 end if;
306
307 end if; /* Update invoice Ids in TDS tables for TDS invoices */
308
309 /* Update invoice Ids in Third Party tables for Third Party invoices */
310 if pv_action = jai_constants.inserting and pr_new.source = 'INDIA TAX INVOICE' then /* 'RECEIPT' then --Ramanand for bug#4388958 */
311
312 jai_rcv_third_party_pkg.populate_tp_invoice_id
313 (
314 p_invoice_id => pr_new.invoice_id,
315 p_invoice_num => pr_new.invoice_num,
316 p_vendor_id => pr_new.vendor_id,
317 p_vendor_site_id => pr_new.vendor_site_id,
318 p_process_flag => lv_process_flag,
319 p_process_message => lv_process_message
320 );
321 if nvl(lv_process_flag, 'N') = jai_constants.unexpected_error then
322 /* raise_application_error(-20002,
323 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
324 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message ; return ;
325 end if;
326 end if;
327
328 /*
329 || Update invoice Ids in Third Party tables for Third Party invoices
330 */
331
332 /* Deleting */
333 if pv_action = jai_constants.deleting then
334
335 jai_ap_tds_tax_defaultation.process_delete
336 (
337 p_invoice_id => pr_old.invoice_id,
338 p_process_flag => lv_process_flag,
339 P_process_message => lv_process_message
340 );
341
342 if nvl(lv_process_flag, 'N') = 'E' then
343 /* raise_application_error(-20002,
344 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
345 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
346 end if;
347
348 end if; /* Deleting */
349
350
351 exception
352 when others then
353 --raise_application_error(-20003, 'Error - trigger ja_in_ap_aia_before_trg on ap_invoices_all : ' || sqlerrm);
354 /* Added an exception block by Ramananda for bug#4570303 */
355 Pv_return_code := jai_constants.unexpected_error;
356 Pv_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARI_T7 ' ||
357 'Error on ap_invoices_all : ' || substr(sqlerrm,1,1800);
358
359 END BRIUD_T1 ;
360
361 END JAI_AP_IA_TRIGGER_PKG ;
[Home] [Help]