1 PACKAGE BODY JAI_AP_IA_TRIGGER_PKG AS
2 /* $Header: jai_ap_ia_t.plb 120.15 2011/12/15 10:20:34 wenzhou 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 /* Updated By Wenqiong Zhou for Bug #1168411 BOE enhancement Start*/
97 IF nvl(pr_old.payment_status_flag,'N') <> 'Y' AND nvl(pr_new.payment_status_flag,'N') = 'Y' AND pr_new.invoice_num like 'BOE%' THEN
98 UPDATE JAI_CMN_BOE_HDRS hdr
99 SET hdr.status = 'AVAILABLE'
100 ,last_update_date = SYSDATE
101 ,last_update_login = fnd_global.login_id
102 ,last_updated_by = fnd_global.user_id
103 WHERE hdr.boe_id = to_number(pr_new.reference_key1);
104 END IF;
105 /* Updated By Wenqiong Zhou for Bug #1168411 BOE enhancement End*/
106 end if; /* updating */
107
108
109 exception
110 when others then
111 --raise_application_error(-20000, 'Error - trigger ja_in_ap_aia_after_trg on ap_invoices_all : ' || sqlerrm);
112 /* Added an exception block by Ramananda for bug#4570303 */
113 Pv_return_code := jai_constants.unexpected_error;
114 Pv_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARI_T7 ' ||
115 'Error on ap_invoices_all : ' || substr(sqlerrm,1,1800);
116
117 END ARUID_T1 ;
118
119 /*
120 REM +======================================================================+
121 REM NAME BRIUD_T1
122 REM
123 REM DESCRIPTION Called from trigger JAI_AP_IA_BRIUD_T1
124 REM
125 REM NOTES Refers to old trigger JAI_AP_IA_BRIUD_T1
126 REM
127 REM +======================================================================+
128 */
129 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
130 lb_result boolean;
131 ln_req_id number;
132 lv_process_flag varchar2(20);
133 lv_process_message varchar2(1100);
134 ln_org_id ap_invoices_all.org_id%type;
135 ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
136
137
138 ln_regime_id NUMBER;
139 lv_regime_code VARCHAR2(30) := jai_constants.service_regime;
140
141 ln_organization_id NUMBER;
142 ln_location_id NUMBER;
143 ln_charge_account_id NUMBER;
144 ld_transaction_date DATE := pr_new.invoice_date;
145 ld_gl_date DATE := pr_new.gl_date;
146 lv_called_from VARCHAR2(80) := 'AP_ACCOUNTING';
147 lv_currency_code VARCHAR2(30) := pr_new.payment_currency_code;
148 ld_curr_conv_date DATE := pr_new.exchange_date;
149 lv_curr_conv_type VARCHAR2(30) := pr_new.exchange_rate_type;
150 ln_curr_conv_rate NUMBER := pr_new.exchange_rate;
151
152 lv_invoice_type VARCHAR2(30) := pr_new.invoice_type_lookup_code;
153 ln_invoice_id NUMBER := pr_new.invoice_id;
154 ld_st_accrual_date DATE;
155 lv_match_type VARCHAR2(50);
156 ln_item_line_num NUMBER;
157
158 ---------------------------------------------------------------------------
159 --Add by Xiao for POT, reg bug#12397015 on 26-Apr-2011, end
160
161 /*changes for bug 5662741 removed for bug 6493858/6411412*/
162
163 BEGIN
164 pv_return_code := jai_constants.successful ;
165 /*------------------------------------------------------------------------------------------
166 FILENAME: jai_ap_ia_t.sql
167
168 CHANGE HISTORY:
169 S.No Date Author and Details
170
171 1. 25/03/2004 Aparajita. Bug # 4088186. TDS Clean up. Version#115.0
172
173 This is the only trigger introduced for all the before event
174 on the table on which this is based.
175
176 2. 14/04/2005 4284505 ssumaith - file version 115.1
177
178 Code added for service tax support for 3rd party taxes in a receipt.
179 In this trigger code has been added to populate the invoice id into
180 the jai_Rcv_tp_invoices table based on the invoice_num , vendor and
181 vendor site.
182
183
184 This patch creates dependency by addition of a new table - jai_rcv_tp_inv_Details
185 and addition of new column (invoice_id) in the table jai_Rcv_tp_invoices table.
186
187 A new procedure has been added to the package - jai_rcv_third_party_pkg which does
188 the actual invoice id update , and hence this package should go along with this trigger
189
190 2. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
191 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
192 Changed AP Lookup code from 'RECEIPT' to 'INDIA TAX INVOICE'
193
194 3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
195 DB Entity as required for CASE COMPLAINCE. Version 116.2
196
197 4. 13-Jun-2005 File Version: 116.3
198 Ramananda for bug#4428980. Removal of SQL LITERALs is done
199
200 5. 26-Apr-2011 Xiao for bug#12397015,
201 Solution change for Service Tax Point of Taxation.
202 6. 09-Jun-2011 Qiong.liu for bug#12621302,
203 BOE tax and remaining amount should be 0 if BOE cancelled.
204
205 Dependency:
206 ----------
207 Sl No. Bug Dependent on
208 Bug/Patch set Details
209 -------------------------------------------------------------------------------------------------
210 1. 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, which was created thru bug
211 4033992.
212 ja_in_util_pkg_s.sql 115.0
213 ja_in_util_pkg_b.sql 115.0
214
215
216
217
218 ------------------------------------------------------------------------------------------ */
219 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
220 ln_org_id := pr_new.org_id;
221 ln_set_of_books_id := pr_new.set_of_books_id;
222 elsif pv_action = jai_constants.deleting then
223 ln_org_id := pr_old.org_id;
224 ln_set_of_books_id := pr_old.set_of_books_id;
225 end if;
226
227 --if
228 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIA_BEFORE_TRG',
229 -- p_org_id => ln_org_id,
230 -- p_set_of_books_id => ln_set_of_books_id )
231 -- =
232 -- FALSE
233 -- then
234 /* India Localization funtionality is not required */
235 -- return;
236 -- end if;
237
238
239 /*
240 || Cancellation Functionality
241 */
242 if pv_action = jai_constants.updating then
243
244 if pr_old.cancelled_date is null and pr_new.cancelled_date is not null then
245
246 if pr_new.source <> 'INDIA TDS' then /* 'TDS' then --Ramanand for bug#4388958 */
247
248 /* TDs functionality on TDS invoice is not required. */
249
250 /*changes for bug 5662741 removed for bug 6493858/6411412*/
251 /*the logic is moved to jai_ap_ida_t.plb*/
252
253 lb_result := fnd_request.set_mode(true);
254
255 ln_req_id :=
256 Fnd_Request.submit_request
257 (
258 'JA',
259 'JAINAPIC',
260 'Cancel TDS invoices',
261 '',
262 false,
263 pr_new.invoice_id
264 );
265
266 end if; /* pr_new.source <> 'TDS' then */
267
268 /* Updated By Wenqiong Zhou for Bug #1168411 BOE enhancement Start*/
269 IF pr_new.invoice_num like 'BOE%' THEN
270 --Add by qiong.liu for bug12621302 09-JUN-2011 Begin
271 ----------------------------------------------------
272 jai_boe_general_pkg.cancelled_boe(pn_boe_id => to_number(pr_new.reference_key1) );
273 ----------------------------------------------------
274 --Add by qiong.liu for bug12621302 09-JUN-2011 End
275 --Commented by qiong.liu for bug12621302 09-JUN-2011 Begin
276 ----------------------------------------------------------
277 /* UPDATE JAI_CMN_BOE_HDRS hdr
278 SET hdr.status = 'CANCELLED'
279 ,last_update_date = SYSDATE
280 ,last_update_login = fnd_global.login_id
281 ,last_updated_by = fnd_global.user_id
282 WHERE hdr.boe_id = to_number(pr_new.reference_key1);
283 */
284 ----------------------------------------------------------
285 --Commented by qiong.liu for bug12621302 09-JUN-2011 End
286
287 END IF; /*IF pr_new.source = 'BOE' THEN */
288 /* Updated By Wenqiong Zhou for Bug #1168411 BOE enhancement End*/
289
290 end if; /* if pr_old.cancelled_date is null and pr_new.cancelled_date is not null then */
291
292 end if; /* if pv_action = jai_constants.updating than */
293
294 /* Update invoice Ids in TDS tables for TDS invoices */
295 if pv_action = jai_constants.inserting and pr_new.source = 'INDIA TDS' then /* 'TDS' then --Ramanand for bug#4388958 */
296
297 jai_ap_tds_generation_pkg.populate_tds_invoice_id
298 (
299 p_invoice_id => pr_new.invoice_id,
300 p_invoice_num => pr_new.invoice_num,
301 p_vendor_id => pr_new.vendor_id,
302 p_vendor_site_id => pr_new.vendor_site_id,
303 p_process_flag => lv_process_flag,
304 p_process_message => lv_process_message
305 );
306
307 if nvl(lv_process_flag, 'N') = 'E' then
308 /* raise_application_error(-20001,
309 '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 :=
310 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message ; return ;
311 end if;
312
313 end if; /* Update invoice Ids in TDS tables for TDS invoices */
314
315 /* Update invoice Ids in Third Party tables for Third Party invoices */
316 if pv_action = jai_constants.inserting and pr_new.source = 'INDIA TAX INVOICE' then /* 'RECEIPT' then --Ramanand for bug#4388958 */
317
318 jai_rcv_third_party_pkg.populate_tp_invoice_id
319 (
320 p_invoice_id => pr_new.invoice_id,
321 p_invoice_num => pr_new.invoice_num,
322 p_vendor_id => pr_new.vendor_id,
323 p_vendor_site_id => pr_new.vendor_site_id,
324 p_process_flag => lv_process_flag,
325 p_process_message => lv_process_message
326 );
327 if nvl(lv_process_flag, 'N') = jai_constants.unexpected_error then
328 /* raise_application_error(-20002,
329 '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 :=
330 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message ; return ;
331 end if;
332 end if;
333
334 /*
335 || Update invoice Ids in Third Party tables for Third Party invoices
336 */
337
338 /* Deleting */
339 if pv_action = jai_constants.deleting then
340
341 jai_ap_tds_tax_defaultation.process_delete
342 (
343 p_invoice_id => pr_old.invoice_id,
344 p_process_flag => lv_process_flag,
345 P_process_message => lv_process_message
346 );
347
348 if nvl(lv_process_flag, 'N') = 'E' then
349 /* raise_application_error(-20002,
350 '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 :=
351 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
352 end if;
353
354 end if; /* Deleting */
355
356
357 exception
358 when others then
359 --raise_application_error(-20003, 'Error - trigger ja_in_ap_aia_before_trg on ap_invoices_all : ' || sqlerrm);
360 /* Added an exception block by Ramananda for bug#4570303 */
361 Pv_return_code := jai_constants.unexpected_error;
362 Pv_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARI_T7 ' ||
363 'Error on ap_invoices_all : ' || substr(sqlerrm,1,1800);
364
365 END BRIUD_T1 ;
366
367 END JAI_AP_IA_TRIGGER_PKG ;
[Home] [Help]