4 REM +======================================================================+
1 PACKAGE BODY JAI_AR_GLDIST_TRIGGER_PKG AS
2 /* $Header: jai_ar_gldist_t.plb 120.3.12020000.4 2013/01/30 06:35:53 mbremkum ship $ */
3 /*
5 REM NAME BRI_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_AR_GLDIST_BRIUD_T1
8 REM
9 REM NOTES
10 REM
11 REM +======================================================================+
12 */
13 PROCEDURE BRI_T1 ( pr_old t_rec%type ,
14 pr_new t_rec%type ,
15 pv_action varchar2 ,
16 pv_return_code out nocopy varchar2 ,
17 pv_return_message out nocopy varchar2
18 ) IS
19 /********************************************************************************************************************************
20 Created By : brathod
21
22 Created Date: 11-Nov-2005
23
24 Bug : 4727534
25
26 Purpose : Stop the Ar Posting To GL (General Ledger Transfer Program) from posting if data for the invoice exists in JAI_AR_TRX_INS_LINES_T.
27 indicating that user did not/forgot to run the India Local Concurrent Program.
28 Here data is being posted from ra_cust_trx_line_gl_dist_all to gl_interface.
29
30 ********************************************************************************************************************************/
31
32 /*
33 || Should stop all invoices from posting because
34 || data still lies in ja_in_temp_line_insert
35 || This includes both Imported and Manual tramsactions
36 */
37 CURSOR cur_get_temp_row
38 IS
39 SELECT
40 1
41 FROM
42 JAI_AR_TRX_INS_LINES_T
43 WHERE
44 customer_trx_id = pr_new.customer_trx_id
45 AND error_flag <> 'D'; /* Modified by Ramananda for removal of SQL LITERALs */
46
47 ln_exists NUMBER;
48
49 BEGIN
50 pv_return_code := jai_constants.successful ;
51 OPEN cur_get_temp_row;
52 FETCH cur_get_temp_row INTO ln_exists;
53 IF cur_get_temp_row%FOUND THEN
54 /*
55 ||Data found in JAI_AR_TRX_INS_LINES_T , India Local concurrent has not been run for this invoice.
56 ||Stop the posting to gl_interface
57 */
58 CLOSE cur_get_temp_row;
59 /* raise_application_error (-20131,'IL Taxes found in JAI_AR_TRX_INS_LINES_T table. Please run the India Local Concurrent Program and then post the record into GL');*/
60 pv_return_code := jai_constants.expected_error ;
61 pv_return_message := 'IL Taxes found in JAI_AR_TRX_INS_LINES_T table. Please run the India Local Concurrent Program and then post the record into GL' ;
62 return ;
63 END IF ;
64 CLOSE cur_get_temp_row;
65 /* Added an exception block by Ramananda for bug#4570303 */
66 EXCEPTION
67 WHEN OTHERS THEN
71
68 Pv_return_code := jai_constants.unexpected_error;
69 Pv_return_message := 'Encountered an error in JAI_AR_GLDIST_TRIGGER_PKG.BRI_T1 ' || substr(sqlerrm,1,1900);
70 END BRI_T1 ;
72 /********************************************************************************************************************************
73 Created By : Eric Ma
74
75 Created Date: 30-MAY-2011
76
77 Bug : 12533434
78
79 Purpose : Change the Service Tax accouting from cash base to accrual base
80 Change the accouting from liability interime to liability
81 Compare the GL_date with effective data .
82 IF GL_date > effective data
83 THEN
84 accrual base (liability ACCOUNTING)
85 ELSE
86 cash base (liability interime )
87 END IF;
88
89 Bug 14121914 Incorrect CCID is picked if GL Date is derived based on Accounting Rule for AR Invoices
90 + Accrual Accounting to be used if GL date is NULL
91 + Update CCID of Tax Lines based on GL date of Revenue Line when Revenue Recognition program runs to
92 update the GL Date
93 ********************************************************************************************************************************/
94
95
96
97
98 PROCEDURE BRI_T2
99 ( pr_old t_rec%type
100 , pr_new t_rec%type
101 , pn_ccid out nocopy number /*Bug 14121914*/
102 , pv_action varchar2
103 , pv_return_code out nocopy varchar2
104 , pv_return_message out nocopy varchar2
105 )
106 IS
107 CURSOR cur_get_ar_tax_row
108 IS
109 SELECT
110 a.tax_id taxid ,
111 a.tax_rate ,
112 a.uom uom ,
113 a.tax_amount tax_amt ,
114 b.tax_type t_type ,
115 a.customer_trx_line_id line_id ,
116 a.tax_line_no tax_line_no
117 FROM
118 JAI_AR_TRX_TAX_LINES a ,
119 JAI_CMN_TAXES_ALL b
120 WHERE link_to_cust_trx_line_id = pr_new.customer_trx_line_id
121 AND a.tax_id = b.tax_id
122 AND NVL(b.inclusive_tax_flag,'N') = 'N'
123 ORDER BY tax_line_no;
124
125 /*Bug 14121914 - Fetch Tax Details based on customer_trx_line_id of the TAX Account Class*/
126 CURSOR cur_get_tax_row
127 IS
128 SELECT
129 a.tax_id taxid ,
130 a.tax_rate ,
131 a.uom uom ,
132 a.tax_amount tax_amt ,
133 b.tax_type t_type ,
134 a.customer_trx_line_id line_id ,
135 a.tax_line_no tax_line_no
136 FROM
137 JAI_AR_TRX_TAX_LINES a ,
138 JAI_CMN_TAXES_ALL b
139 WHERE customer_trx_line_id = pr_new.customer_trx_line_id
140 AND a.tax_id = b.tax_id
141 ORDER BY tax_line_no;
142
143 CURSOR cur_chk_rgm ( pv_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE )
144 IS
145 SELECT
146 regime_id ,
147 regime_code
148 FROM
149 jai_regime_tax_types_v jrttv
150 WHERE
151 upper(jrttv.tax_type) = upper(pv_tax_type);
152
153
154 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
155 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
156 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
160 where regime_id = p_regime_id
157 CURSOR cur_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
158 select to_date(attribute_value, 'DD/MM/YYYY')
159 from JAI_RGM_ORG_REGNS_V
161 and organization_id = p_organization_id
162 and location_id = p_location_id
163 AND attribute_code = 'EFF_DATE_ST_PT'
164 AND attribute_type_code = 'OTHERS'
165 AND registration_type = 'OTHERS'
166 AND (NOT EXISTS
167 (select '1'
168 from JAI_RGM_ORG_REGNS_V
169 where regime_id = p_regime_id
170 and attribute_code IN 'INV_ORG_CLASSIFICATION'
171 and attribute_value <> 'ORGANIZATION'
172 and organization_id = p_organization_id
173 and location_id = p_location_id)
174 OR
178 where regime_id = p_regime_id
175 NOT EXISTS
176 (select '1'
177 from JAI_RGM_ORG_REGNS_V
179 and attribute_code IN 'SERVICE TYPE'
180 and attribute_value <> 'OTHER'
181 and organization_id = p_organization_id
182 and location_id = p_location_id)
183 );
184
185 CURSOR c_get_org_loc
186 IS
187 SELECT organization_id, location_id
188 FROM jai_ar_trxs
189 WHERE customer_trx_id = pr_new.CUSTOMER_TRX_ID;
190
191 /*Bug 12805386 - End*/
192
193 CURSOR cur_get_ar_hdr_info
194 IS
195 SELECT organization_id, location_id
196 FROM JAI_AR_TRXS
197 WHERE Customer_Trx_ID = pr_new.CUSTOMER_TRX_ID;
198
199 /*Bug 12792354*/
200 CURSOR c_temp_record_exists
201 IS
202 SELECT 1
203 FROM JAI_AR_TRX_INS_LINES_T
204 WHERE customer_trx_id = pr_new.customer_trx_id;
205
206 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
207 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ;
208 ln_organization_id NUMBER;
209 ln_location_id NUMBER;
210 ln_ccid NUMBER;
211 ld_st_accrual_date DATE;
212 ln_exists NUMBER;
213 r_get_org_loc c_get_org_loc%ROWTYPE; /*Bug 12805386*/
214 ld_gl_date DATE; /*Bug 14121914*/
215
216 --Added by Wenqiong for Bug 14253668 begin
217 ----------------------------------------------
218 CURSOR check_trx_type (cp_customer_trx_line_id NUMBER)IS
219 SELECT trx_types.type
220 FROM
221 ra_customer_trx_lines_all trxl
222 , ra_customer_trx_all trx
223 , ra_cust_trx_types_all trx_types
224 WHERE trxl.customer_trx_id = trx.customer_trx_id
225 AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
226 AND trxl.customer_trx_line_id = cp_customer_trx_line_id;
227
228 lv_ar_trx_type ra_cust_trx_types_all.type%TYPE;
229 ----------------------------------------------
230 --Added by Wenqiong for Bug 14253668 end
231
232 /*Bug 14121914 - Fetch the GL Date of the Revenue Line*/
233 PROCEDURE get_rev_gl_date(p_gl_date OUT NOCOPY DATE)
234 IS
235 PRAGMA AUTONOMOUS_TRANSACTION;
236 CURSOR c_gl_date IS
237 SELECT gl_date
238 FROM ra_cust_trx_line_gl_dist_all
239 WHERE customer_trx_id = pr_new.customer_trx_id
240 AND account_class = 'REV';
241 ld_gl_date DATE;
242 BEGIN
243 OPEN c_gl_date;
244 FETCH c_gl_date INTO ld_gl_date;
245 CLOSE c_gl_date;
246 END;
247
248 BEGIN
249 OPEN cur_get_ar_hdr_info ;
250 FETCH cur_get_ar_hdr_info
251 INTO ln_organization_id
252 , ln_location_id;
253 CLOSE cur_get_ar_hdr_info;
254
255 /*Bug 12792354 - Check if data exists in JAI_AR_TRX_INS_LINES_T before proceeding to update CCID*/
256 OPEN c_temp_record_exists;
257 FETCH c_temp_record_exists INTO ln_exists;
258 IF c_temp_record_exists%FOUND THEN
259 IF pr_new.account_class = 'REV' THEN /*Bug 14121914*/
260 FOR TAX_TYPE_REC IN cur_get_ar_tax_row
261 LOOP
262 ln_regime_code := null;
263 ln_regime_id := null;
264 OPEN cur_chk_rgm ( pv_tax_type => tax_type_rec.t_type);
265 FETCH cur_chk_rgm
266 INTO ln_regime_id,ln_regime_code ;
267 CLOSE cur_chk_rgm ;
268
269 OPEN c_get_org_loc;
270 FETCH c_get_org_loc INTO r_get_org_loc;
271 CLOSE c_get_org_loc;
272
273 IF (ln_regime_code =jai_constants.service_regime)
274 THEN
275 OPEN cur_get_st_accrual_date(ln_regime_id, r_get_org_loc.organization_id, r_get_org_loc.location_id);
276 FETCH cur_get_st_accrual_date
277 INTO ld_st_accrual_date;
278 CLOSE cur_get_st_accrual_date;
279
280 --Added by Wenqiong for Bug 14253668 begin
281 -------------------------------------------
282 OPEN check_trx_type(pr_new.customer_trx_line_id);
283 FETCH check_trx_type INTO lv_ar_trx_type;
284 CLOSE check_trx_type;
285 -------------------------------------------
286 --Added by Wenqiong for Bug 14253668 end
287
288 /*Bug 14121914 - GL date can be NULL if Revenue Recognition is not run. Accrual Accounting to be used*/
289 IF (pr_new.gl_date>= ld_st_accrual_date OR pr_new.gl_date IS NULL)
290 THEN
291 IF lv_ar_trx_type <> jai_constants.ar_invoice_type_cm THEN
292 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
293 ( p_regime_id => ln_regime_id
294 , p_organization_type => jai_constants.service_tax_orgn_type
295 , p_organization_id => ln_organization_id
296 , p_location_id => ln_location_id
297 , p_tax_type => tax_type_rec.t_type
298 , p_account_name => jai_constants.liability
299 );
300 ELSE
301 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
302 ( p_regime_id => ln_regime_id
303 , p_organization_type => jai_constants.service_tax_orgn_type
304 , p_organization_id => ln_organization_id
305 , p_location_id => ln_location_id
306 , p_tax_type => tax_type_rec.t_type
307 , p_account_name => jai_constants.recovery
308 );
309 END IF;
310 ELSE
311 IF lv_ar_trx_type <> jai_constants.ar_invoice_type_cm THEN
312 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
318 , p_account_name => jai_constants.liability_interim
313 ( p_regime_id => ln_regime_id
314 , p_organization_type => jai_constants.service_tax_orgn_type
315 , p_organization_id => ln_organization_id
316 , p_location_id => ln_location_id
317 , p_tax_type => tax_type_rec.t_type
319 );
320 ELSE
321 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
322 ( p_regime_id => ln_regime_id
323 , p_organization_type => jai_constants.service_tax_orgn_type
324 , p_organization_id => ln_organization_id
325 , p_location_id => ln_location_id
326 , p_tax_type => tax_type_rec.t_type
327 , p_account_name => jai_constants.recovery_interim
328 );
329 END IF;
330 END IF;-- ld_st_accrual_date < pr_new.gl_date
331
332 UPDATE JAI_AR_TRX_INS_LINES_T
333 SET CODE_COMBINATION_ID = ln_ccid
334 WHERE customer_trx_id = pr_new.customer_trx_id
335 AND customer_trx_line_id = TAX_TYPE_REC.LINE_ID
336 AND link_to_cust_trx_line_id = pr_new.customer_trx_line_id;
337
338 /*Bug 12792354 - It is not required to error out if no records are updated*/
339 /*
340 IF SQL%ROWCOUNT <>1
341 THEN
342 pv_return_code := jai_constants.expected_error ;
343 pv_return_message := 'Error found in the JAI_AR_TRX_INS_LINES_T, please check the table' ;
344 END IF;
345 */
346 END IF; --(ln_regime_code =jai_constants.service_regime)
347 END LOOP;--TAX_TYPE_REC IN TAX_TYPE_CUR
348 END IF; /*Added IF pr_new.account_class = 'REV' THEN for Bug 14121914*/
349 END IF;
350 CLOSE c_temp_record_exists;
351
352 /*Bug 14121914 - Update CCID of the Tax Lines - Start*/
353 IF pr_new.account_class = 'TAX' THEN
354 FOR TAX_TYPE_REC IN cur_get_tax_row
355 LOOP
356 ln_regime_code := null;
357 ln_regime_id := null;
358 OPEN cur_chk_rgm ( pv_tax_type => tax_type_rec.t_type);
359 FETCH cur_chk_rgm
360 INTO ln_regime_id,ln_regime_code ;
361 CLOSE cur_chk_rgm ;
362
363 OPEN c_get_org_loc;
364 FETCH c_get_org_loc INTO r_get_org_loc;
365 CLOSE c_get_org_loc;
366
367 IF (ln_regime_code =jai_constants.service_regime)
368 THEN
369 OPEN cur_get_st_accrual_date(ln_regime_id, r_get_org_loc.organization_id, r_get_org_loc.location_id);
370 FETCH cur_get_st_accrual_date
371 INTO ld_st_accrual_date;
372 CLOSE cur_get_st_accrual_date;
373
374 --Added by Wenqiong for Bug 14253668 begin
375 -------------------------------------------
376 OPEN check_trx_type(pr_new.customer_trx_line_id);
377 FETCH check_trx_type INTO lv_ar_trx_type;
378 CLOSE check_trx_type;
379 -------------------------------------------
380 --Added by Wenqiong for Bug 14253668 end
381
382 get_rev_gl_date(ld_gl_date);
383
384 IF (ld_gl_date >= ld_st_accrual_date OR ld_gl_date IS NULL)
385 THEN
386 IF lv_ar_trx_type <> jai_constants.ar_invoice_type_cm THEN--Added by Wenqiong for Bug 14253668
387 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
388 ( p_regime_id => ln_regime_id
389 , p_organization_type => jai_constants.service_tax_orgn_type
390 , p_organization_id => ln_organization_id
391 , p_location_id => ln_location_id
392 , p_tax_type => tax_type_rec.t_type
393 , p_account_name => jai_constants.liability
394 );
395 --Added by Wenqiong for Bug 14253668 begin
396 ELSE
397 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
398 ( p_regime_id => ln_regime_id
399 , p_organization_type => jai_constants.service_tax_orgn_type
400 , p_organization_id => ln_organization_id
401 , p_location_id => ln_location_id
402 , p_tax_type => tax_type_rec.t_type
406 --Added by Wenqiong for Bug 14253668 end
403 , p_account_name => jai_constants.recovery
404 );
405 END IF;
407 ELSE
408 IF lv_ar_trx_type <> jai_constants.ar_invoice_type_cm THEN--Added by Wenqiong for Bug 14253668
409 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
410 ( p_regime_id => ln_regime_id
411 , p_organization_type => jai_constants.service_tax_orgn_type
412 , p_organization_id => ln_organization_id
413 , p_location_id => ln_location_id
414 , p_tax_type => tax_type_rec.t_type
415 , p_account_name => jai_constants.liability_interim
416 );
417 --Added by Wenqiong for Bug 14253668 begin
418 ELSE
419 ln_ccid := jai_cmn_rgm_recording_pkg.get_account
420 ( p_regime_id => ln_regime_id
421 , p_organization_type => jai_constants.service_tax_orgn_type
422 , p_organization_id => ln_organization_id
423 , p_location_id => ln_location_id
424 , p_tax_type => tax_type_rec.t_type
425 , p_account_name => jai_constants.recovery_interim
426 );
427 END IF;
428 --Added by Wenqiong for Bug 14253668 end
429 END IF;
430
431 pn_ccid := ln_ccid;
432
433 END IF; --(ln_regime_code =jai_constants.service_regime)
434 END LOOP;--TAX_TYPE_REC IN cur_get_tax_row
435 END IF;
436 /*Bug 14121914 - Update CCID of the Tax Lines - End*/
437
438 EXCEPTION
439 WHEN OTHERS
440 THEN
441 Pv_return_code := jai_constants.unexpected_error;
442 Pv_return_message := 'Encountered an error in JAI_AR_GLDIST_TRIGGER_PKG.BRI_T2 ' || substr(sqlerrm,1,1900);
443 END BRI_T2;
444 END JAI_AR_GLDIST_TRIGGER_PKG ;
[Home] [Help]