1 PACKAGE BODY JAI_AP_IA_DTC_TRIGGER_PKG AS
2 /* $Header: jai_ap_ia_dtc_t.plb 120.0.12020000.2 2013/03/18 12:24:37 vkaranam noship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME BRIUD_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_AP_IA_DTC_BRIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_AP_IA_DTC_BRIUD_T1
11 REM
12 REM +======================================================================+
13 */
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_IA_DTC_TRIGGER_PKG';
21 GV_MODULE_PREFIX VARCHAR2 (100) := 'JAI.PLSQL.JAI_AP_IA_DTC_TRIGGER_PKG';
22 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
23 lb_result boolean;
24 ln_req_id number;
25 lv_process_flag varchar2(20);
26 lv_process_message varchar2(1100);
27 ln_org_id ap_invoices_all.org_id%type;
28 ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
29 lv_proc_name VARCHAR2 (100) := 'BRIUD_T1';
30 lv_open_period ap_invoice_distributions_all.period_name%type;
31 ld_accounting_date DATE;
32 ld_out_accounting_date DATE;
33 lv_count number := 0;
34 lv_payment varchar2(1) := 'N';
35
36
37 /*Get transaction records by invoice_id*/
38 cursor c_jai_ap_tds_thhold_trxs(p_invoice_id number) is
39 select threshold_trx_id,
40 threshold_grp_id,
41 threshold_hdr_id,
42 tax_category_id,
43 tds_section_code,
44 taxable_amount,
45 tax_amount,
46 invoice_to_tds_authority_id,
47 invoice_to_vendor_id,
48 tds_event,
49 calc_tax_amount
50 from jai_ap_tds_thhold_trxs
51 where invoice_id = p_invoice_id
52 and (tds_event = 'INVOICE VALIDATE' or
53 tds_event like 'THRESHOLD TRANSITION%' or
54 tds_event = 'SURCHARGE_CALCULATE')
55 and tds_rollbacked is NULL
56 order by threshold_trx_id;
57
58 --Get GL_date from distribution
59 CURSOR get_dist_gl_date(cp_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
60 SELECT accounting_date
61 FROM ap_invoice_distributions_all
62 WHERE invoice_id = cp_invoice_id
63 AND distribution_line_number = 1;
64 -- Only one distribution will be created for TDS invoices and so
65 -- hard coded the distribution line number to 1.
66
67 --If GL date in distribution is NULL, get from invoice header
68 CURSOR c_get_lines_acct_date(cp_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
69 SELECT accounting_date
70 FROM ap_invoice_lines_all
71 WHERE invoice_id = cp_invoice_id
72 AND line_number = 1;
73
74 cursor c_threshold_breach(p_invoice_id number) is
75 select jattt.invoice_id,
76 jattt.tds_event,
77 jattt.invoice_to_tds_authority_id
78 from jai_ap_tds_inv_taxes jatit, jai_ap_tds_thhold_trxs jattt
79 where jatit.invoice_id = p_invoice_id
80 and jatit.default_cum_threshold_stage = 'BEFORE THRESHOLD'
81 and jatit.threshold_grp_id = jattt.threshold_grp_id
82 and jattt.tds_event like 'THRESHOLD%'
83 and not exists (select '1'
84 from jai_ap_tds_thhold_trxs
85 where invoice_id = jatit.invoice_id); /*to ensure that no TDS is deducted*/
86
87 lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
88
89 FUNCTION get_invoice_org_id(p_invoice_id IN NUMBER)
90 RETURN NUMBER
91 IS
92 PRAGMA AUTONOMOUS_TRANSACTION;
93
94 cursor c_ap_invoices_all(p_invoice_id number) is
95 select org_id
96 from ap_invoices_all
97 where invoice_id = p_invoice_id;
98
99 r_ap_invoices_all c_ap_invoices_all%ROWTYPE;
100 BEGIN
101 OPEN c_ap_invoices_all(p_invoice_id);
102 FETCH c_ap_invoices_all INTO r_ap_invoices_all;
103 CLOSE c_ap_invoices_all;
104
105 RETURN r_ap_invoices_all.org_id;
106 END get_invoice_org_id;
107
108
109 FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
110 RETURN VARCHAR2
111 IS
112 PRAGMA AUTONOMOUS_TRANSACTION;
113
114 CURSOR c_payment_status(cp_invoice_id NUMBER)
115 IS
116 SELECT payment_status_flag
117 FROM ap_invoices_all
118 WHERE invoice_id = cp_invoice_id;
119
120 r_payment_status c_payment_status%ROWTYPE;
121 BEGIN
122 OPEN c_payment_status(p_invoice_id);
123 FETCH c_payment_status INTO r_payment_status;
124 CLOSE c_payment_status;
125
126 RETURN r_payment_status.payment_status_flag;
127 END get_invoice_payment_status;
128
129 BEGIN
130 pv_return_code := jai_constants.successful ;
131 /*------------------------------------------------------------------------------------------
132 FILENAME: jai_ap_ia_dtc_t.plb
133
134 CHANGE HISTORY:
135 S.No Date Author and Details
136
137 1. 25/03/2004 Chong. For DTC cancellation intergration
138 This is extracted from JAI_AP_IA_BRIUD_T1, only keep DTC logic.
139 For cancellation added below validations:
140 1). If any transaction's TDS authority invoice is already paid, stop cancellation.
141 2).Check all transaction's TDS invoice, if any GL_Date is not in open period, stop cancellation.
142 3). To check the invoices for which the TDS has not been deducted as threshold is not breached
143 and same threshold having crossed with another invoice and is paid.
144
145 Remove the call of DTC cancellation CP.
146 new DTC cancellation logic is invoked from AP workbench by JAI_AP_STND_TAX_PROCESS pakcage
147 Dependency:
148 ----------
149 Sl No. Bug Dependent on
150 Bug/Patch set Details
151 -------------------------------------------------------------------------------------------------
152 1. Call to JAI_CMN_UTILS_PKG.WRITE_FND_LOG, to write fnd log for future debug.
153 Call to ap_utilities_pkg.get_current_gl_date()
154 ------------------------------------------------------------------------------------------ */
155 /*
156 || Cancellation Functionality
157 */
158 if pv_action = jai_constants.updating then
159
160 if pr_old.cancelled_date is null and pr_new.cancelled_date is not null then
161 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, 'Cancelling invoice' || pr_old.invoice_id);
162
163 IF pr_new.source <> 'INDIA TDS' THEN /* 'TDS' then --Ramanand for bug#4388958 */
164 FOR cur_rec IN c_jai_ap_tds_thhold_trxs(pr_old.invoice_id) LOOP
165
166 /**Check payment status of TDS invoice to authority */
167 IF cur_rec.invoice_to_tds_authority_id IS NOT NULL THEN
168 lv_invoice_payment_status := get_invoice_payment_status(cur_rec.invoice_to_tds_authority_id);
169 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
170 pv_return_code := jai_constants.expected_error;
171 pv_return_message := 'Invoice to TDS Authority - '|| cur_rec.invoice_to_tds_authority_id ||' is already paid. Current invoice can''t be cancelled';
172 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message);
173 RETURN;
174 END IF;
175 END IF;
176
177 /**Check GL date of TDS invoice to authority */
178 ln_org_id := get_invoice_org_id(cur_rec.invoice_to_tds_authority_id);
179
180 OPEN get_dist_gl_date(cur_rec.invoice_to_tds_authority_id);
181 FETCH get_dist_gl_date
182 INTO ld_accounting_date;
183 CLOSE get_dist_gl_date;
184
185 /*Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
186 if (ld_accounting_date is NULL) then
187 OPEN c_get_lines_acct_date(cur_rec.invoice_to_tds_authority_id);
188 FETCH c_get_lines_acct_date
189 INTO ld_accounting_date;
190 CLOSE c_get_lines_acct_date;
191 end if;
192
193 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, ld_accounting_date);
194 --Check if the given date is in current open period ( for TDS authority)
195 lv_open_period := ap_utilities_pkg.get_current_gl_date(P_Date => ld_accounting_date,
196 P_Org_Id => ln_org_id);
197
198 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, lv_open_period);
199 if lv_open_period is null then
200
201 ap_utilities_pkg.get_open_gl_date(p_date => ld_accounting_date, /* In date */
202 p_period_name => lv_open_period, /* out Period */
203 p_gl_date => ld_out_accounting_date, /* out date */
204 P_Org_Id => ln_org_id);
205
206 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, lv_open_period);
207 if lv_open_period is null then
208 pv_return_code := jai_constants.expected_error;
209 pv_return_message := 'No open accounting Period after : ' || ld_accounting_date;
210 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message || ' invoice_id :' ||cur_rec.invoice_to_tds_authority_id);
211 return;
212 end if;
213 end if; /* lv_open_period is null */
214
215
216 /**Check GL date of TDS invoice to vendor */
217 ln_org_id := get_invoice_org_id(cur_rec.invoice_to_vendor_id);
218
219 OPEN get_dist_gl_date(cur_rec.invoice_to_vendor_id);
220 FETCH get_dist_gl_date
221 INTO ld_accounting_date;
222 CLOSE get_dist_gl_date;
223
224 /*Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
225 if (ld_accounting_date is NULL) then
226 OPEN c_get_lines_acct_date(cur_rec.invoice_to_vendor_id);
227 FETCH c_get_lines_acct_date
228 INTO ld_accounting_date;
229 CLOSE c_get_lines_acct_date;
230 end if;
231
232 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, ld_accounting_date);
233 --Check if the given date is in current open period ( for TDS authority)
234 lv_open_period := ap_utilities_pkg.get_current_gl_date(P_Date => ld_accounting_date,
235 P_Org_Id => ln_org_id);
236 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, lv_open_period);
237 if lv_open_period is null then
238
239 ap_utilities_pkg.get_open_gl_date(p_date => ld_accounting_date, /* In date */
240 p_period_name => lv_open_period, /* out Period */
241 p_gl_date => ld_out_accounting_date, /* out date */
242 P_Org_Id => ln_org_id);
243
244 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX ||'.'|| lv_proc_name, lv_open_period);
245 if lv_open_period is null then
246 pv_return_code := jai_constants.expected_error;
247 pv_return_message := 'No open accounting Period after : ' || ld_accounting_date;
248 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message || ' invoice_id :' ||cur_rec.invoice_to_vendor_id);
249 return;
250 end if;
251 end if; /* lv_open_period is null */
252 END LOOP; /*cur_rec IN c_jai_ap_tds_thhold_trxs(pr_old.invoice_id) LOOP*/
253
254 /**To check the invoices for which the TDS has not been deducted as threshold is not breached and same threshold having crossed with another invoice and is paid.*/
255 for r_threshold_breach in c_threshold_breach(pr_old.invoice_id) loop
256 if (r_threshold_breach.tds_event like 'THRESHOLD TRANSITION%') then
257 lv_count := lv_count + 1;
258
259 elsif (r_threshold_breach.tds_event like 'THRESHOLD ROLLBACK%') then
260 lv_count := lv_count - 1;
261 end if;
262 for tds_id in (select invoice_to_tds_authority_id
263 from jai_ap_tds_thhold_trxs
264 where invoice_id = r_threshold_breach.invoice_id) loop
265
266 if get_invoice_payment_status(tds_id.invoice_to_tds_authority_id) <> 'N' and
267 lv_payment = 'N' then
268 lv_payment := 'Y';
269 end if;
270 end loop;
271 end loop;
272 if ((mod(lv_count, 2) <> 0) and lv_payment = 'Y') then
273 pv_return_code := jai_constants.expected_error;
274 pv_return_message := 'Threshold transition TDS has already been paid for the attached threshold_grp_id. Cannot cancel the invoice';
275 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message);
276 end if;
277
278
279
280 end if; /* pr_new.source <> 'TDS' then */
281
282 end if; /* if pr_old.cancelled_date is null and pr_new.cancelled_date is not null then */
283
284 end if; /* if pv_action = jai_constants.updating than */
285
286 /* Update invoice Ids in TDS tables for TDS invoices */
287 if pv_action = jai_constants.inserting and pr_new.source = 'INDIA TDS' then /* 'TDS' then --Ramanand for bug#4388958 */
288
289 jai_ap_tds_generation_pkg.populate_tds_invoice_id
290 (
291 p_invoice_id => pr_new.invoice_id,
292 p_invoice_num => pr_new.invoice_num,
293 p_vendor_id => pr_new.vendor_id,
294 p_vendor_site_id => pr_new.vendor_site_id,
295 p_process_flag => lv_process_flag,
296 p_process_message => lv_process_message
297 );
298
299 if nvl(lv_process_flag, 'N') = 'E' then
300 /* raise_application_error(-20001,
301 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message); */
302 pv_return_code := jai_constants.expected_error ;
303 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message ; return ;
304 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message);
305 return ;
306 end if;
307
308 end if; /* Update invoice Ids in TDS tables for TDS invoices */
309
310 /* Update invoice Ids in Third Party tables for Third Party invoices */
311 if pv_action = jai_constants.inserting and pr_new.source = 'INDIA TAX INVOICE' then /* 'RECEIPT' then --Ramanand for bug#4388958 */
312
313 jai_rcv_third_party_pkg.populate_tp_invoice_id
314 (
315 p_invoice_id => pr_new.invoice_id,
316 p_invoice_num => pr_new.invoice_num,
317 p_vendor_id => pr_new.vendor_id,
318 p_vendor_site_id => pr_new.vendor_site_id,
319 p_process_flag => lv_process_flag,
320 p_process_message => lv_process_message
321 );
322 if nvl(lv_process_flag, 'N') = jai_constants.unexpected_error then
323 /* raise_application_error(-20002,
324 '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 :=
325 'Error - trigger ja_in_ap_aida_before_trg on ja_in_ap_aia_before_trg : ' || lv_process_message ; return ;
326 end if;
327 end if;
328
329 /*
330 || Update invoice Ids in Third Party tables for Third Party invoices
331 */
332
333 /* Deleting */
334 if pv_action = jai_constants.deleting then
335
336 jai_ap_tds_tax_defaultation.process_delete
337 (
338 p_invoice_id => pr_old.invoice_id,
339 p_process_flag => lv_process_flag,
340 P_process_message => lv_process_message
341 );
342
343 if nvl(lv_process_flag, 'N') = 'E' then
344 /* raise_application_error(-20002,
345 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message); */
346 pv_return_code := jai_constants.expected_error ;
347 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
348 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message);
349 return ;
350 end if;
351
352 end if; /* Deleting */
353
354
355 exception
356 when others then
357 --raise_application_error(-20003, 'Error - trigger ja_in_ap_aia_before_trg on ap_invoices_all : ' || sqlerrm);
358 /* Added an exception block by Ramananda for bug#4570303 */
359 Pv_return_code := jai_constants.unexpected_error;
360 Pv_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.BRIUD_T1 ' ||
361 'Error on ap_invoices_all : ' || substr(sqlerrm,1,1800);
362 JAI_CMN_UTILS_PKG.WRITE_FND_LOG(G_LEVEL_ERROR, GV_MODULE_PREFIX ||'.'|| lv_proc_name, pv_return_message);
363 END BRIUD_T1 ;
364
365 END JAI_AP_IA_DTC_TRIGGER_PKG ;
[Home] [Help]