1 package body jai_ap_dtc_defaultation_pkg as
2 /* $Header: jai_ap_dtc_dflt.plb 120.29.12020000.3 2013/04/23 14:11:38 cholei noship $ */
3 --+============================================================================================================+
4 --| Copyright (c) 2011 Oracle Corporation
8 --| FILENAME |
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+============================================================================================================+
9 --| jai_ap_dtc_defaultation_pkg.pck |
10 --| |
11 --| DESCRIPTION |
12 --| Defaulte tax based on Natural Account to Section Code Mapping or |
13 --| Vendor Site Default Section Code |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE populate_localization_inv_tax |
17 --| |
18 --| HISTORY |
19 --| 05/Dec/2011 Zhiwei.Xin Created |
20 --| 21/Feb/2012 Zhiwei.Xin Modified for bug 13740866 |
21 --| Fixed Details: |
22 --| Added Order by clause for cursor c_thrd_details. |
23 --| 22/Feb/2012 Zhiwei.Xin Modified for bug 13741305 |
24 --| Fixed Details: |
25 --| Delete records whose tax_line_no is 0 before insert the same one. |
26 --| 23/Feb/2012 Zhiwei.Xin Modified for bug 13736616 |
27 --| Fixed Details: |
28 --| Use base_amount for foreign currency invoices. |
29 --| 27/Feb/2012 Zhiwei.Xin Modified for bug 13741805 |
30 --| Fixed Details: |
31 --| Added TDS_SECTION_CODE IS NOT NULL condition in c_get_disnt_sec_code cursor. |
32 --| 29/Feb/2012 Zhiwei.Xin Modified for bug 13731393 |
33 --| Fixed Details: |
34 --| Exclude inclusive service tax when tds_on_service_flag is set with 'N'. |
35 --| 01/MAR/2012 Zhiwei.Xin Modified for bug 13745243 |
36 --| Fixed Details: |
37 --| Even individual distribution amount did not reach slab amount, |
38 --| Default tax category when their sum amount reach the single slab amount. |
39 --| 08/MAR/2012 Zhiwei.Xin Modified for bug 13792748 |
40 --| Fixed Details: |
41 --| Default section code based on Defaultation Option if PAN is not available |
42 --| or PAN reference number (i.e. Confirm PAN Check box is not enabled) |
43 --| is present. |
44 --| While default tax category from Regime Option only. |
45 --| 17/09/2012 Chong Updated For bug#13802244 |
46 --| 1) When p_call_from= G_CALL_FROM_ROLLBACK, means it was invoked after threshold rollback. |
47 --| 2) When call from rollback, do not get cumulative amount by group effective_amount + |
48 --| current_invoice_amount. Just get current invoice amount to see it if fall into any slab.|
49 --| Do this update for both Nature accounting mapping and vendor site defaultation method. |
50 --| 3) Update cursor c_get_thr_slab_id_mul to get slab for multi rate |
51 --| a. Added cp_legal_entity_id, cp_tds_section_code parameter |
52 --| b. Added alias for table jai_ap_tds_thhold_types. Added table alias prefix to |
53 --| threshold_hdr_id,threshold_type_id. |
54 --| 02/05/2013 Chong Updated For bug#16274617 |
55 --| 1) Add slab ID as paramter for cursor c_thrd_details, make sure this cursor only loop |
56 --| the targert slab ID. not read and check others slabs in the same threshold setup. |
57 --| 2) Add NVL to ln_effective_amount, as if c_thrd_group still can return NULL. |
58 --| 3) Add effective date check of tax category for PAN NOTAVAILABLE. |
59 --| 4) Add accounting date as paramter in cursor c_check_taxes_end_date. |
63 --+============================================================================================================*/
60 --| 5) Add NVL for REVERSAL_FLAG in cursor c_get_inv_dist,as it's null in new added dist line |
61 --| 02/07/2013 Chong Updated For bug#16281516 |
62 --| Get default TDS category from OU level by new cursor c_get_ou_regime_value . |
64 G_CALL_FROM_ROLLBACK CONSTANT VARCHAR2(40) := 'PROCESS_THRESHOLD_ROLLBACK';
65
66 /*-------------------------------------------------------------------------------------------------------------------------------+
67 | Created By : zhiwei.xin |
68 | Creation Date : 05/Dec/2011 |
69 | Bug Number/ER Name : DTC |
70 | SubProgram Name : default_tds_from_setup |
71 | Type : PROCEDURE |
72 | Purpose : Defaulte tax based on Natural Account to Section Code Mapping or Vendor Site Default Section Code |
73 | TDD Reference : Section 14 |
74 | Assumptions : |
75 | Called From : JAI Tax Details form |
76 |--------------------------------------------------------------------------------------------------------------------------------|
77 | parameters IN/OUT Type Required Description and Purpose |
78 | ------------ -------- ------ ---------- ------------------------- |
79 | p_inv_id IN NUMBER yes invoice id |
80 | p_redefault_tax_category IN VARCHAR2 yes whether redefault tax category |
81 | p_redefault_section_code IN VARCHAR2 yes whether redefault section code |
82 | p_process_flag OUT VARCHAR2 yes return process result |
83 | p_process_message OUT VARCHAR2 yes return process message |
84 ---------------------------------------------------------------------------------------------------------------------------------*/
85 procedure default_tds_from_setup(p_inv_id IN number,
86 p_redefault_tax_category IN varchar2 default 'N',
87 p_redefault_section_code IN varchar2 default 'N',
88 p_process_flag OUT NOCOPY varchar2,
89 p_process_message OUT NOCOPY varchar2,
90 p_call_from IN varchar2 default 'VALIDATION' --Added by Chong for Bug#13802244 2012/09/17
91 )
92 is
93
94 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
95 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
96 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
97 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
98 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
99 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
100 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
101 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_DTC_TAX_DEFAULTATION';
102 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'JAI.PLSQL.JAI_AP_DTC_TAX_DEFAULTATION';
103 G_VENDOR_SITE_DEF_SEC_CODE CONSTANT VARCHAR2(40) := 'Vendor Site Default Section Code';
104 G_NTL_ACC_SEC_MAP CONSTANT VARCHAR2(40) := 'Natural Account - Section Code Mapping';
105 l_api_name VARCHAR2(40) := 'DEFAULT_TDS_FROM_SETUP';
106 LN_ARRAY_SIZE CONSTANT NUMBER := 1000;
107
108 -- cursor to get vendor type by vendor id
109 cursor c_get_vendor_info
110 is
111 SELECT aia.VENDOR_ID,
112 jatvh.TDS_VENDOR_TYPE_LOOKUP_CODE
113 FROM JAI_AP_TDS_VENDOR_HDRS jatvh,
114 AP_INVOICES_ALL aia
115 WHERE aia.VENDOR_ID = jatvh.VENDOR_ID
116 AND aia.INVOICE_ID = p_inv_id;
117
118 -- cursor to get information (Operating Unit ID, Legal Entity ID, Ledger ID)from ap invoices
119 cursor c_get_inv_info
120 is
121 SELECT ORG_ID,
122 LEGAL_ENTITY_ID,
123 SET_OF_BOOKS_ID,
124 INVOICE_TYPE_LOOKUP_CODE,
125 VENDOR_SITE_ID
126 FROM AP_INVOICES_ALL
127 WHERE INVOICE_ID = p_inv_id;
128
129 -- cursor to get pan number and tan number
130 cursor c_get_vendor_pan_tan(cp_vendor_id number , cp_vendor_site_id number) is
131 SELECT JATVH.PAN_NO PAN_NO,
132 JATOTV.ORG_TAN_NUM TAN_NO,
133 JATVH.CONFIRM_PAN_FLAG CONFIRM_PAN_FLAG -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012
134 FROM PO_VENDORS PV,
135 PO_VENDOR_SITES_ALL PVSA,
136 JAI_AP_TDS_VENDOR_HDRS JATVH,
137 JAI_AP_TDS_ORG_TAN_V JATOTV
138 WHERE PV.VENDOR_ID = PVSA.VENDOR_ID
142 AND PV.VENDOR_ID = cp_vendor_id
139 AND PVSA.VENDOR_ID = JATVH.VENDOR_ID
140 AND PVSA.VENDOR_SITE_ID = JATVH.VENDOR_SITE_ID
141 AND PVSA.ORG_ID = JATOTV.ORGANIZATION_ID
143 AND PVSA.VENDOR_SITE_ID = cp_vendor_site_id;
144
145 cursor c_get_regime_attr_value (cp_attr_code JAI_RGM_REGISTRATIONS.ATTRIBUTE_CODE%TYPE)
146 is
147 SELECT jrr.ATTRIBUTE_VALUE
148 FROM JAI_RGM_DEFINITIONS jrd,
149 JAI_RGM_REGISTRATIONS jrr
150 WHERE jrd.REGIME_ID = jrr.REGIME_ID
151 AND jrr.ATTRIBUTE_CODE = cp_attr_code
152 AND jrd.REGIME_CODE = 'TDS';
153 -- Added by Chong for bug#16281516 Start
154 -----------------------------------------------------
155 CURSOR c_get_ou_regime_value (cp_attr_code JAI_RGM_REGISTRATIONS.ATTRIBUTE_CODE%TYPE
156 ,cp_org_id NUMBER
157 )
158 IS
159 SELECT jrorv.ATTRIBUTE_VALUE
160 FROM JAI_RGM_ORG_REGNS_V jrorv
161 WHERE jrorv.ATTRIBUTE_CODE = cp_attr_code
162 AND jrorv.organization_id = cp_org_id
163 AND jrorv.REGIME_CODE = 'TDS';
164 -----------------------------------------------------
165 -- Added by Chong for bug#16281516 End
166
167 -- cursor to get max and min accounting date for distributions
168 cursor c_get_accounting_date
169 is
170 SELECT MAX(ACCOUNTING_DATE) MAX_DATE,
171 MIN(ACCOUNTING_DATE) MIN_DATE
172 FROM AP_INVOICE_DISTRIBUTIONS_ALL
173 WHERE INVOICE_ID = p_inv_id;
174
175 -- cursor to get period year from gl_periods
176 cursor c_get_period_year(cp_calendar_id number,
177 cp_accounting_date AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE%TYPE)
178 is
179 SELECT a.PERIOD_YEAR
180 FROM GL_PERIODS a, gl_period_sets b
181 WHERE a.PERIOD_SET_NAME = b.period_set_name
182 and b.period_set_id = cp_calendar_id
183 AND cp_accounting_date BETWEEN a.START_DATE AND a.END_DATE;
184
185 -- cursor to get distribution details
186 cursor c_get_inv_dist
187 is
188 SELECT INVOICE_ID,
189 INVOICE_LINE_NUMBER,
190 DISTRIBUTION_LINE_NUMBER,
191 INVOICE_DISTRIBUTION_ID,
192 DIST_CODE_COMBINATION_ID,
193 AMOUNT,
194 DESCRIPTION,
195 MATCH_STATUS_FLAG,
196 LINE_TYPE_LOOKUP_CODE,
197 ACCOUNTING_DATE
198 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
199 WHERE INVOICE_ID = p_inv_id
200 AND LINE_TYPE_LOOKUP_CODE NOT IN
201 ('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS')
202 AND NOT EXISTS (SELECT '1'
203 FROM AP_INVOICES_ALL
204 WHERE INVOICE_ID = p_inv_id
205 AND SOURCE = 'INDIA TDS')
206 UNION
207 SELECT INVOICE_ID,
208 INVOICE_LINE_NUMBER,
209 DISTRIBUTION_LINE_NUMBER,
210 INVOICE_DISTRIBUTION_ID,
211 DIST_CODE_COMBINATION_ID,
212 AMOUNT,
213 DESCRIPTION,
214 MATCH_STATUS_FLAG,
215 LINE_TYPE_LOOKUP_CODE,
216 ACCOUNTING_DATE
217 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
218 WHERE INVOICE_ID = p_inv_id
219 AND LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
220 AND NOT EXISTS
221 (SELECT '1'
222 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
223 WHERE JAMIT.INVOICE_DISTRIBUTION_ID =
224 AIDA.INVOICE_DISTRIBUTION_ID
225 AND JCT.TAX_ID = JAMIT.TAX_ID
226 AND UPPER(JCT.TAX_TYPE) IN
227 (JAI_CONSTANTS.TAX_TYPE_EXCISE,
228 JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL,
229 JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
230 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS,
231 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
232 JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
233 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
234 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS))
235 AND NOT EXISTS (SELECT '1'
236 FROM AP_INVOICES_ALL
237 WHERE INVOICE_ID = p_inv_id
238 AND SOURCE = 'INDIA TDS')
239 ORDER BY INVOICE_DISTRIBUTION_ID;
240
241 -- cursor to get scetion code
242 cursor c_get_section_code
243 is
244 SELECT LOOKUP_CODE SECTION_CODE
245 FROM JA_LOOKUPS
246 WHERE LOOKUP_TYPE = 'JAI_TDS_SECTION';
247
248 cursor c_sectionwise_cum_amt (cp_section_code VARCHAR2)
249 is
250 SELECT TDS_SECTION_CODE,
251 INVOICE_DISTRIBUTION_ID,
252 SUM(AMOUNT) OVER(ORDER BY TDS_SECTION_CODE, INVOICE_DISTRIBUTION_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE_AMT
253 FROM JAI_AP_TDS_DEFAULT
254 WHERE INVOICE_ID = p_inv_id
255 AND TDS_SECTION_CODE = cp_section_code
256 ORDER BY TDS_SECTION_CODE, INVOICE_DISTRIBUTION_ID;
257
258 -- cursor to get distinct section code
259 cursor c_get_disnt_sec_code
260 is
261 SELECT --DISTINCT --commented out by zhiwei.xin for bug 13745243 on 01-Mar-2012
262 TDS_SECTION_CODE,
263 SUM(AMOUNT) SEC_AMOUNT -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
264 FROM JAI_AP_TDS_DEFAULT
265 WHERE INVOICE_ID = p_inv_id
266 AND TDS_SECTION_CODE IS NOT NULL -- Added by zhiwei.xin for bug 13741805 on 27-Feb-2012.
267 GROUP BY TDS_SECTION_CODE; -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
268
269 -- Added by Chong for bug#15939571 on 20121213 Start
270 ----------------------------------------------
271 --As threshold transition
272 cursor c_get_grp_sec_act(cp_section_code VARCHAR2)
273 is
277 ,SUM(AMOUNT) SEC_AMOUNT
274 SELECT TDS_SECTION_CODE
275 ,NATURAL_ACCOUNT_VALUE
276 ,ACCOUNTING_DATE
278 FROM JAI_AP_TDS_DEFAULT
279 WHERE INVOICE_ID = p_inv_id
280 AND TDS_SECTION_CODE IS NOT NULL
281 AND TDS_SECTION_CODE = cp_section_code
282 GROUP BY TDS_SECTION_CODE
283 ,NATURAL_ACCOUNT_VALUE
284 ,ACCOUNTING_DATE;
285 ----------------------------------------------
286 -- Added by Chong for bug#15939571 on 20121213 End
287
288 -- cursor to fetch JAI_AP_TDS_TH_VSITE_V
289 cursor c_tds_th_vsite_v (cp_tds_vendor_tlc JAI_AP_TDS_VENDOR_HDRS.TDS_VENDOR_TYPE_LOOKUP_CODE%TYPE,
290 cp_vendor_id JAI_AP_TDS_VENDOR_HDRS.VENDOR_ID%TYPE,
291 cp_vendor_site_id PO_VENDOR_SITES_ALL.VENDOR_SITE_ID%TYPE,
292 cp_tds_sec_code JAI_AP_TDS_THHOLD_HDRS.SECTION_CODE%TYPE)
293 is
294 SELECT THRESHOLD_HDR_ID,
295 NVL(EXCEPTION_SETUP_FLAG,'N'),
296 NVL(MULTIPLE_RATE_SETUP,'N')
297 FROM JAI_AP_TDS_TH_VSITE_V
298 WHERE TDS_VENDOR_TYPE_LOOKUP_CODE = cp_tds_vendor_tlc
299 AND VENDOR_ID = cp_vendor_id
300 AND VENDOR_SITE_ID = cp_vendor_site_id
301 AND SECTION_CODE = cp_tds_sec_code;
302
303 -- cursor to fetch threshold group for calculating existing effective invoice amount
304 cursor c_thrd_group (cp_vendor_id number,
305 cp_pan_num varchar2,
306 cp_tan_num varchar2,
307 cp_fin_year number,
308 cp_sec_code varchar2)
309 is
310 SELECT (NVL(TOTAL_INVOICE_AMOUNT, 0) +
311 NVL(TOTAL_INVOICE_UNAPPLY_AMOUNT, 0) -
312 NVL(TOTAL_INVOICE_APPLY_AMOUNT, 0) -
313 NVL(TOTAL_INVOICE_CANCEL_AMOUNT, 0)) EFFECTIVE_AMOUNT
314 FROM JAI_AP_TDS_THHOLD_GRPS
315 WHERE VENDOR_ID = cp_vendor_id
316 AND VENDOR_PAN_NUM = cp_pan_num
317 AND ORG_TAN_NUM = cp_tan_num
318 AND SECTION_CODE = cp_sec_code
319 AND FIN_YEAR = cp_fin_year;
320
321 -- cursor to fetch threshold details
322 cursor c_thrd_details (cp_thrd_header_id number
323 ,cp_org_id number
324 ,cp_threshold_slab_id number --Added by Chong for bug#16274617
325 )
326 is
327 SELECT JATTTY.THRESHOLD_TYPE_ID,
328 JATTTY.FROM_DATE,
329 JATTTY.TO_DATE,
330 JATTTY.THRESHOLD_TYPE,
331 JATTTA.THRESHOLD_SLAB_ID,
332 JATTS.FROM_AMOUNT,
333 JATTS.TO_AMOUNT,
334 JATTTA.TAX_CATEGORY_ID,
335 JATTTA.OPERATING_UNIT_ID
336 ,JATTH.THRESHOLD_HDR_ID --Added by Chong for bug#13740866 20121213
337 FROM JAI_AP_TDS_THHOLD_HDRS JATTH,
338 JAI_AP_TDS_THHOLD_TYPES JATTTY,
339 JAI_AP_TDS_THHOLD_SLABS JATTS,
340 JAI_AP_TDS_THHOLD_TAXES JATTTA
341 WHERE JATTH.THRESHOLD_HDR_ID = cp_thrd_header_id
342 AND JATTH.THRESHOLD_HDR_ID = JATTTY.THRESHOLD_HDR_ID
343 AND JATTTY.THRESHOLD_TYPE_ID = JATTS.THRESHOLD_TYPE_ID
344 AND JATTS.THRESHOLD_SLAB_ID = JATTTA.THRESHOLD_SLAB_ID
345 AND JATTTA.OPERATING_UNIT_ID = cp_org_id
346 AND JATTS.THRESHOLD_SLAB_ID = cp_threshold_slab_id --Added by Chong for bug#16274617
347 ORDER BY JATTTY.THRESHOLD_TYPE DESC -- added by zhiwei.xin for bug 13740866 on 21-Feb-2012
348 ,NVL(JATTTY.DEFAULT_TYPE,'N') ASC --Added by Chong for bug#13740866 20121213
349 ;
350
351 -- cursor to fetch default section code from vendor additional information
352 cursor c_get_def_sec_code (cp_vendor_id number, cp_vendor_site_id number)
353 is
354 SELECT SECTION_CODE
355 FROM JAI_AP_TDS_VENDOR_HDRS
356 WHERE VENDOR_ID = cp_vendor_id
357 AND VENDOR_SITE_ID = cp_vendor_site_id;
358
359 -- Added by Chong for bug#15939571 on 20121213 Start
360 ----------------------------------------------
361 -- cursor to fetch default section code from vendor NULL site
362 cursor c_get_null_sec_code (cp_vendor_id number)
363 is
364 SELECT SECTION_CODE
365 FROM JAI_AP_TDS_VENDOR_HDRS
366 WHERE VENDOR_ID = cp_vendor_id
367 AND VENDOR_SITE_ID = 0;
368 ----------------------------------------------
369 -- Added by Chong for bug#15939571 on 20121213 End
370
371 -- cursor to get records from JAI_AP_TDS_DEFAULT
372 cursor c_get_ap_tds_def
373 is
374 SELECT jatd.*
375 FROM JAI_AP_TDS_DEFAULT jatd
376 WHERE jatd.INVOICE_ID = p_inv_id
377 --AND jatd.TDS_SECTION_CODE IS NOT NULL commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
378 AND NOT EXISTS ( SELECT 1
379 FROM JAI_AP_TDS_INV_TAXES jatit
380 WHERE jatit.INVOICE_ID = p_inv_id
381 AND jatit.INVOICE_DISTRIBUTION_ID = jatd.INVOICE_DISTRIBUTION_ID
382 AND NVL(jatit.TAX_CATEGORY_OVERRIDEN,'N') = 'Y');
383
384 -- cursor to get tax category from JAI_AP_TDS_DEFAULT
385 cursor c_get_tax_ctg_id (cp_dist_id number)
386 is
387 SELECT TAX_CATEGORY_ID
388 FROM JAI_AP_TDS_DEFAULT
389 WHERE INVOICE_ID = p_inv_id
390 AND INVOICE_DISTRIBUTION_ID = cp_dist_id;
391
392 -- cursor to Check if Service Tax exists for Standalone Invoice
393 cursor c_standalone_st_line (cp_invoice_line_number number)
394 IS
395 SELECT '1'
396 FROM JAI_CMN_DOCUMENT_TAXES JCDT, JAI_CMN_TAXES_ALL JCTA
397 WHERE JCDT.SOURCE_DOC_ID = p_inv_id
398 AND JCDT.SOURCE_DOC_LINE_ID = cp_invoice_line_number
399 AND JCDT.SOURCE_DOC_TYPE = 'STANDALONE_INVOICE'
400 AND JCDT.TAX_ID = JCTA.TAX_ID
404 FROM JAI_RGM_DEFINITIONS
401 AND JCTA.TAX_TYPE IN (SELECT ATTRIBUTE_CODE
402 FROM JAI_RGM_REGISTRATIONS
403 WHERE REGIME_ID = (SELECT REGIME_ID
405 WHERE REGIME_CODE =JAI_CONSTANTS.SERVICE_REGIME)
406 AND REGISTRATION_TYPE = 'TAX_TYPES');
407
408 -- cursor to Check if Service Tax exists for PO matched Invoice
409 cursor c_po_matched_st_line (cp_inv_dist_id number)
410 IS
411 SELECT '1'
412 FROM JAI_AP_MATCH_INV_TAXES JAMIT,
413 JAI_CMN_TAXES_ALL JCTA
414 WHERE JAMIT.INVOICE_ID = p_inv_id
415 AND JAMIT.INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
416 AND JAMIT.TAX_ID = JCTA.TAX_ID
417 AND JCTA.TAX_TYPE IN (SELECT ATTRIBUTE_CODE
418 FROM JAI_RGM_REGISTRATIONS
419 WHERE REGIME_ID = (SELECT REGIME_ID
420 FROM JAI_RGM_DEFINITIONS
421 WHERE REGIME_CODE =JAI_CONSTANTS.SERVICE_REGIME)
422 AND REGISTRATION_TYPE = 'TAX_TYPES');
423
424 -- cursor to get parent distribution id for po matched
425 cursor c_get_po_matched_dist_id (cp_inv_dist_id number)
426 is
427 SELECT PARENT_INVOICE_DISTRIBUTION_ID
428 FROM JAI_AP_MATCH_INV_TAXES
429 WHERE INVOICE_ID = p_inv_id
430 AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id;
431
432 -- cursor to get parent distribution id for standardalone
433 cursor c_get_standalone_dist_id
434 is
435 SELECT aida.INVOICE_DISTRIBUTION_ID
436 FROM AP_INVOICE_DISTRIBUTIONS_ALL aida,
437 JAI_CMN_DOCUMENT_TAXES jcdt
438 WHERE jcdt.SOURCE_DOC_ID = p_inv_id
439 AND aida.INVOICE_LINE_NUMBER = jcdt.SOURCE_DOC_PARENT_LINE_NO
440 ORDER BY aida.INVOICE_DISTRIBUTION_ID;
441
442 -- cursor to get tds on service flg
443 cursor c_get_tds_on_service(cp_section_code varchar2)
444 is
445 SELECT 'Y'
446 FROM ja_lookups
447 WHERE lookup_type = 'JAI_TDS_SECTION_SERVICE'
448 --AND lookup_code = cp_section_code; -- commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
449 AND UPPER(lookup_code) = UPPER(cp_section_code); -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
450
451 -- cursor to fetch datas from JAI_CMN_TAX_CTG_LINES
452 cursor c_get_tax_ctg_lines (cp_tax_ctg_id number)
453 is
454 SELECT *
455 FROM JAI_CMN_TAX_CTG_LINES
456 WHERE TAX_CATEGORY_ID = cp_tax_ctg_id
457 ORDER BY LINE_NO;
458
459 -- cursor to get tax code details
460 cursor c_get_tax_code_details(cp_tax_id number)
461 is
462 SELECT *
463 FROM JAI_CMN_TAXES_ALL
464 WHERE TAX_ID = cp_tax_id;
465
466 -- cursor to check if record exists in JAI_AP_TDS_INV_TAXES
467 cursor c_check_rec_exists(cp_inv_dist_id number, cp_line_no number)
468 is
469 SELECT 'Y'
470 FROM JAI_AP_TDS_INV_TAXES
471 WHERE INVOICE_ID = p_inv_id
472 AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
473 AND TAX_LINE_NO = cp_line_no;
474
475 -- cursor to check tax end date
476 cursor c_check_taxes_end_date(cp_tax_ctg_id number
477 ,cp_accounting_date date --Added by Chong for bug#16274617
478 )
479 is
480 SELECT '1'
481 FROM JAI_CMN_TAX_CTGS_ALL jctca,
482 JAI_CMN_TAX_CTG_LINES jctcl,
483 JAI_CMN_TAXES_ALL jcta
484 WHERE jctca.TAX_CATEGORY_ID = cp_tax_ctg_id
485 AND jctcl.TAX_CATEGORY_ID = jctca.TAX_CATEGORY_ID
486 AND jcta.TAX_ID = jctcl.TAX_ID
487 --AND (jcta.END_DATE is not null and sysdate > jcta.END_DATE ) --Commented by Chong for bug#16274617
488 --Added by Chong for bug#16274617 Start
489 ----------------------------------------------------------------------
490 AND (cp_accounting_date < jcta.start_date
491 OR (jcta.end_date IS NOT NULL AND cp_accounting_date > jcta.end_date
492 )
493 )
494 ----------------------------------------------------------------------
495 --Added by Chong for bug#16274617 End
496 ;
497
498 -- cursor to get code combination id
499 cursor c_get_dist_ccid (cp_inv_dist_id number)
500 is
501 SELECT DIST_CODE_COMBINATION_ID
502 FROM AP_INVOICE_DISTRIBUTIONS_ALL
503 WHERE INVOICE_ID = p_inv_id
504 AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id;
505
506 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
507 CURSOR c_std_inclu_taxes (cp_inv_line_num number)
508 IS
509 SELECT sum(JAMIT.tax_amt)
510 FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
511 WHERE
512 JAMIT.source_doc_id = p_inv_id
513 AND JAMIT.source_doc_parent_line_no = cp_inv_line_num
514 AND JCT.TAX_ID = JAMIT.TAX_ID
515 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
516 AND JCT.TAX_TYPE
517 IN (SELECT ATTRIBUTE_CODE
518 FROM JAI_RGM_REGISTRATIONS
519 WHERE REGIME_ID = (SELECT REGIME_ID
520 FROM JAI_RGM_DEFINITIONS
521 WHERE REGIME_CODE ='SERVICE')
522 AND REGISTRATION_TYPE = 'TAX_TYPES');
523
524 CURSOR c_match_inclu_taxes (cp_inv_dist_id number)
525 IS
526 SELECT SUM(JAMIT.tax_amount)
527 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
528 WHERE JAMIT.INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
529 AND JCT.TAX_ID = JAMIT.TAX_ID
530 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
531 AND JCT.TAX_TYPE
532 IN (SELECT ATTRIBUTE_CODE
533 FROM JAI_RGM_REGISTRATIONS
534 WHERE REGIME_ID = (SELECT REGIME_ID
535 FROM JAI_RGM_DEFINITIONS
539
536 WHERE REGIME_CODE ='SERVICE')
537 AND REGISTRATION_TYPE = 'TAX_TYPES');
538 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
540 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 begin
541
542 -- cursor to get threshold slab id
543 cursor c_get_thr_slab_id (cp_thr_hdr_id number,
544 cp_thr_type varchar2,
545 cp_amount number,
546 cp_max_accounting_date date,
547 cp_min_accounting_date date)
548 is
549 SELECT threshold_slab_id
550 FROM jai_ap_tds_thhold_slabs
551 WHERE threshold_hdr_id = cp_thr_hdr_id
552 AND threshold_type_id in
553 -- (SELECT threshold_type_id --Commented by Chong for bug#15939571 20121212
554 --Added by Chong for bug#15939571 20121212 Start
555 ------------------------------------------------
556 (SELECT FIRST_VALUE(threshold_type_id)
557 OVER (ORDER BY NVL(DEFAULT_TYPE,'N') DESC ,threshold_type_id ASC)
558 ------------------------------------------------
559 --Added by Chong for bug#15939571 20121212 End
560 FROM jai_ap_tds_thhold_types
561 WHERE threshold_hdr_id = cp_thr_hdr_id
562 AND threshold_type = cp_thr_type
563 AND cp_max_accounting_date between nvl(from_date,cp_max_accounting_date) and nvl(to_date, cp_max_accounting_date)
564 AND cp_min_accounting_date between nvl(from_date,cp_min_accounting_date) and nvl(to_date, cp_min_accounting_date))
565 AND cp_amount between NVL(from_amount, cp_amount) and NVL(to_amount, cp_amount)
566 ;
567
568 -- cursor to get threshold slab id for multi-rate
569 cursor c_get_thr_slab_id_mul( cp_threshold_hdr_id number,
570 cp_threshold_type varchar2,
571 cp_amount number,
572 cp_max_accounting_date date,
573 cp_min_accounting_date date
574 ,cp_legal_entity_id JAI_AP_TDS_THHOLD_ACCOUNT.LEGAL_ENTITY_ID%TYPE --Added cp_legal_entity_id by Chong for Bug#13802244 2012/09/21
575 ,cp_tds_section_code jai_ap_tds_default.TDS_SECTION_CODE%TYPE --Added cp_tds_section_code by Chong for Bug#13802244 2012/09/21
576 ,cp_natural_account_value jai_ap_tds_default.natural_account_value%TYPE --Added by Chong for Bug#15939571 2012/12/12
577 ,cp_accounting_date Date --Added by Chong for Bug#15939571 2012/12/12
578 )
579 is
580 select threshold_slab_id
581 from jai_ap_tds_thhold_slabs
582 where threshold_hdr_id = cp_threshold_hdr_id
583 and threshold_type_id in
584 ( select jattt.threshold_type_id
585 from jai_ap_tds_thhold_types jattt
586 where jattt.threshold_hdr_id = cp_threshold_hdr_id
587 and jattt.threshold_type = cp_threshold_type
588 /* Commented out by Chong for bug#15964390 20121213 Start
589 and cp_max_accounting_date between nvl(jattt.from_date,sysdate) and nvl(jattt.to_date, sysdate)
590 and cp_min_accounting_date between nvl(jattt.from_date,sysdate) and nvl(jattt.to_date, sysdate)
591 Commented out by Chong for bug#15964390 20121213 End */
592 --Added by Chong for bug#15964390 20121212 Start
593 ------------------------------------------------
594 and cp_max_accounting_date between nvl(jattt.from_date,cp_max_accounting_date) and nvl(jattt.to_date, cp_max_accounting_date)
595 and cp_min_accounting_date between nvl(jattt.from_date,cp_min_accounting_date) and nvl(jattt.to_date, cp_min_accounting_date)
596 ------------------------------------------------
597 --Added by Chong for bug#15964390 20121212 End
598 and exists (select '1'
599 --Added by Chong for bug#15939571 20121212 Start
600 ------------------------------------------------
601 from JAI_AP_TDS_THHOLD_ACCOUNT jatta
602 ,JAI_DTC_SCTN_ACCOUNT_MAPPING jdsam
603 where jatta.threshold_hdr_id = jattt.threshold_hdr_id
604 and jatta.threshold_type_id = jattt.threshold_type_id
605 and jatta.legal_entity_id = jdsam.legal_entity_id
606 and jatta.natural_account_value = jdsam.natural_account_value
607 and jatta.legal_entity_id = cp_legal_entity_id
608 and jdsam.dtc_section_code = cp_tds_section_code
609 and jatta.natural_account_value = cp_natural_account_value
610 and cp_accounting_date between nvl(jdsam.from_date,cp_accounting_date) and nvl(jdsam.to_date, cp_accounting_date)
611 ------------------------------------------------
612 --Added by Chong for bug#15939571 20121212 End
613 /* --Commented by Chong for bug#15939571 20121213 Start
614 from JAI_AP_TDS_THHOLD_ACCOUNT jatta
615 where jatta.threshold_hdr_id = jattt.threshold_hdr_id --Added alias (jattt) by Chong for Bug#13802244 2012/09/21
616 and jatta.threshold_type_id = jattt.threshold_type_id
617 and jatta.legal_entity_id = cp_legal_entity_id --Added cp_legal_entity_id by Chong for Bug#13802244 2012/09/21
618 and jatta.NATURAL_ACCOUNT_VALUE in (select distinct natural_account_value
622 and jatd.tds_section_code = cp_tds_section_code) --Added by Chong for Bug#13802244 2012/09/21
619 from jai_ap_tds_default jatd
620 where jatd.invoice_id = p_inv_id
621 --and jatd.tds_section_code = tds_section_code) --Commented by Chong for Bug#13802244 2012/09/21
623 --Commented by Chong for bug#15939571 20121213 End */
624 )
625 )
626 and cp_amount between nvl(from_amount,cp_amount) and nvl(to_amount, cp_amount);
627
628 -- cursor to fetch tax category id
629 cursor c_tax_category_id (cp_thr_slab_id number, cp_org_id number)
630 is
631 SELECT TAX_CATEGORY_ID
632 FROM JAI_AP_TDS_THHOLD_TAXES
633 WHERE THRESHOLD_SLAB_ID = cp_thr_slab_id
634 AND OPERATING_UNIT_ID = cp_org_id;
635
636 cursor c_sec_accounting_date (cp_sec_code varchar2)
637 is
638 SELECT MAX(ACCOUNTING_DATE) MAX_DATE,
639 MIN(ACCOUNTING_DATE) MIN_DATE
640 FROM JAI_AP_TDS_DEFAULT
641 WHERE INVOICE_ID = p_inv_id
642 AND TDS_SECTION_CODE = cp_sec_code;
643
644 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 end.
645
646 -- added by zhiwei.xin for solution change on 01-Mar-2012 begin
647
648 -- cursor to validate accounting date for every threshold type
649 cursor c_valid_accounting_date (cp_thr_hdr_id number, cp_max_date date, cp_min_date date)
650 is
651 SELECT 'Y'
652 FROM jai_ap_tds_thhold_types
653 WHERE threshold_hdr_id = cp_thr_hdr_id
654 AND cp_max_date between nvl(from_date, cp_max_date) and nvl(to_date, cp_max_date)
655 AND cp_min_date between nvl(from_date, cp_min_date) and nvl(to_date, cp_min_date);
656
657 -- cursor to get threshold header id
658 cursor c_get_thr_hdr_id (cp_vendor_tlc JAI_AP_TDS_VENDOR_HDRS.TDS_VENDOR_TYPE_LOOKUP_CODE%TYPE,
659 cp_sec_code varchar2)
660 is
661 SELECT threshold_hdr_id,
662 NVL(MULTIPLE_RATE_SETUP,'N')
663 FROM JAI_AP_TDS_TH_VSITE_V JATVV
664 WHERE tds_vendor_type_lookup_code = cp_vendor_tlc
665 AND section_code = cp_sec_code
666 AND section_type = 'TDS_SECTION'
667 AND exception_setup_flag = 'N'
668 AND rownum = 1;
669
670 -- added by zhiwei.xin for solution change on 01-Mar-2012 end.
671
672 -- type of collection
673 /*
674 TYPE AIDA_RECORD IS RECORD (
675 INVOICE_ID NUMBER,
676 INVOICE_LINE_NUMBER NUMBER,
677 DISTRIBUTION_LINE_NUMBER NUMBER,
678 INVOICE_DISTRIBUTION_ID NUMBER,
679 DIST_CODE_COMBINATION_ID NUMBER,
680 AMOUNT NUMBER,
681 DESCRIPTION VARCHAR2(240),
682 MATCH_STATUS_FLAG VARCHAR2(1),
683 LINE_TYPE_LOOKUP_CODE VARCHAR2(25),
684 ACCOUNTING_DATE DATE
685 );
686 TYPE AIDA_TYPE IS TABLE OF AIDA_RECORD;
687 Chong */
688
689 TYPE CUM_AMT_RECORD IS RECORD (
690 TDS_SECTION_CODE VARCHAR2(30),
691 INVOICE_DISTRIBUTION_ID NUMBER,
692 CUMULATIVE_AMT NUMBER
693 );
694 TYPE CUM_AMT_TYPE IS TABLE OF CUM_AMT_RECORD;
695 --Added by Chong for bug#15939571 20121210 Start
696 -----------------------------------------------------------------------------------------------------
697 --When get count > 0, means AP invoice containing matched invoice lines
698 cursor c_check_is_matched is
699 select count(line_number)
700 from ap_invoice_lines_all
701 where invoice_id = p_inv_id
702 and match_type <> 'NOT_MATCHED';
703
704 cursor c_check_effective_date(cp_legal_entity_id JAI_AP_TDS_THHOLD_ACCOUNT.LEGAL_ENTITY_ID%TYPE
705 ,cp_tds_section_code jai_ap_tds_default.TDS_SECTION_CODE%TYPE
706 ,cp_natural_account_value jai_ap_tds_default.natural_account_value%TYPE
707 ,cp_accounting_date DATE
708 ) is
709 select count(1)
710 from JAI_DTC_SCTN_ACCOUNT_MAPPING jdsam
711 where jdsam.legal_entity_id = cp_legal_entity_id
712 and jdsam.dtc_section_code = cp_tds_section_code
713 and jdsam.natural_account_value = cp_natural_account_value
714 and cp_accounting_date between nvl(jdsam.from_date,cp_accounting_date) and nvl(jdsam.to_date, cp_accounting_date)
715 ;
716
717
718
719 ln_cnt_matched_lines NUMBER := 0;
720 ln_cnt_mapped_section NUMBER := 0;
721 ln_cnt_effective_date NUMBER := 0;
722 lv_section_code VARCHAR2(50);
723 -----------------------------------------------------------------------------------------------------
724 --Added by Chong for bug#15939571 20121210 End
725
726 -- variables
727 ln_vendor_id JAI_AP_TDS_VENDOR_HDRS.VENDOR_ID%TYPE;
728 lv_vendor_type JAI_AP_TDS_VENDOR_HDRS.TDS_VENDOR_TYPE_LOOKUP_CODE%TYPE;
729 ln_vendor_site_id AP_INVOICES_ALL.VENDOR_SITE_ID%TYPE;
730 ln_org_id AP_INVOICES_ALL.ORG_ID%TYPE;
731 ln_legal_entity_id AP_INVOICES_ALL.LEGAL_ENTITY_ID%TYPE;
732 ln_ledger_id AP_INVOICES_ALL.SET_OF_BOOKS_ID%TYPE;
733 lv_inv_tlc AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
734 lv_segment_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE;
735 ld_max_date AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE%TYPE;
739 lv_tax_default_option VARCHAR2(40);
736 ld_min_date AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE%TYPE;
737 ln_max_period_year GL_PERIODS.PERIOD_YEAR%TYPE;
738 ln_min_period_year GL_PERIODS.PERIOD_YEAR%TYPE;
740 lv_tds_vendor_default_flag VARCHAR2(40) := NULL; --Added by Chong for bug#16367707
741 -- la_aida_tab AIDA_TYPE := AIDA_TYPE(); commented for test by ChongLei
742 la_cum_amt_tab CUM_AMT_TYPE := CUM_AMT_TYPE();
743 ln_thr_header_id JAI_AP_TDS_THHOLD_HDRS.THRESHOLD_HDR_ID%TYPE;
744 lv_exp_setup_flg JAI_AP_TDS_THHOLD_HDRS.EXCEPTION_SETUP_FLAG%TYPE;
745 lv_multi_rate_flg JAI_AP_TDS_THHOLD_HDRS.MULTIPLE_RATE_SETUP%TYPE;
746 ln_effective_amount NUMBER;
747 lv_pan_no VARCHAR2(30);
748 lv_tan_no VARCHAR2(30);
749 lv_default_section_code JAI_AP_TDS_VENDOR_HDRS.SECTION_CODE%TYPE;
750 ln_line_no NUMBER;
751 lr_tax_code_details JAI_CMN_TAXES_ALL%ROWTYPE;
752 lv_rec_exists VARCHAR2(1);
753 ln_tax_ctg_id NUMBER;
754 lv_end_date_check varchar2(1); -- all Taxes attached to the Tax category are not end dated before attaching the same. (1:exist, 0:not exist)
755 lv_end_date_check2 varchar2(1); -- for PAN not available, will check min date and max GL date to make sure tax category effective in the whole time range --Added by Chong for bug#16274617
756 lv_exist_flg varchar2(1); -- '1':exist, '0':not exist
757 ln_parent_dist_id number;
758 lv_tds_on_service_flg varchar2(1);
759 lv_gl_calendar_id number;
760 ln_parent_dist_ccid number;
761 ln_inclusive_tax_amount number; -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
762
763 ln_thr_slab_id_old number; -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
764 ln_thr_slab_id_new number; -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
765
766 lv_valid_flg VARCHAR2(1); -- added by zhiwei.xin for solution change on 01-Mar-2012
767
768 lv_confirm_flg VARCHAR2(1); -- 'Y':confirmed, 'N':unconfirmed added by zhiwei.xin for bug 13792748 on 08-Mar-2012
769
770 begin
771 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
772 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'p_inv_id: '||p_inv_id);
773 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'p_redefault_tax_category: '||p_redefault_tax_category);
774 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'p_redefault_section_code: '||p_redefault_section_code);
775 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'p_call_from: '||p_call_from);
776
777 -- clear tables
778 if p_redefault_section_code = 'Y' then
779 delete from JAI_AP_TDS_DEFAULT where INVOICE_ID = p_inv_id;
780 delete from JAI_AP_TDS_INV_TAXES where INVOICE_ID = p_inv_id and NVL(TAX_CATEGORY_OVERRIDEN,'N') = 'N'; --Added tax_category_overriden by Chong for bug#16248896
781 end if;
782
783 -- get vendor type
784 open c_get_vendor_info;
785 fetch c_get_vendor_info into ln_vendor_id, lv_vendor_type;
786 close c_get_vendor_info;
787
788 if lv_vendor_type is NULL then
789 p_process_flag := 'N';
790 p_process_message := 'TDS vendor type classification is not present for the vendor. ';
791 if G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL then
792 FND_LOG.STRING(G_LEVEL_STATEMENT,
793 G_MODULE_NAME || l_api_name ,
794 'TDS vendor type classification is not present for the vendor ' || ln_vendor_id);
795 end if;
796 return;
797 end if;
798
799 -- get Operating Unit ID, Legal Entity ID, Ledger ID and Invoice Type Lookup Code
800 open c_get_inv_info;
801 fetch c_get_inv_info into ln_org_id, ln_legal_entity_id, ln_ledger_id, lv_inv_tlc, ln_vendor_site_id;
802 close c_get_inv_info;
803
804 -- get natural account
805 lv_segment_name := JAI_AP_UTILS_PKG.GET_NATURAL_ACCOUNT(ln_ledger_id, ln_legal_entity_id);
806
807 -- get pan number
808 open c_get_vendor_pan_tan(ln_vendor_id, ln_vendor_site_id);
809 fetch c_get_vendor_pan_tan into lv_pan_no, lv_tan_no, lv_confirm_flg; -- added lv_confirm_flg by zhiwei.xin for bug 13792748 on 08-Mar-2012
810 close c_get_vendor_pan_tan;
811
812 -- commented out by zhiwei.xin for bug 13792748 on 08-Mar-2012 begin
813 /*if lv_pan_no = 'PANAPPLIED' OR lv_pan_no = 'PANNOTAVBL' OR lv_pan_no = 'PANINVALID' then
814
815 \* 1. derive tax category from regime attribute. *\
816 open c_get_regime_attr_value('TAX_CATEGORY');
817 fetch c_get_regime_attr_value into ln_tax_ctg_id;
818 close c_get_regime_attr_value;
819
820 \* 2. insert data into jai_ap_tds_inv_taxes. *\
821 for tax_ctg_lines_rec IN c_get_tax_ctg_lines(ln_tax_ctg_id)
822 loop
823 ln_line_no := tax_ctg_lines_rec.LINE_NO;
824 open c_get_tax_code_details(tax_ctg_lines_rec.TAX_ID);
825 fetch c_get_tax_code_details into lr_tax_code_details;
826 close c_get_tax_code_details;
827
828 INSERT INTO JAI_AP_TDS_INV_TAXES
829 (TDS_INV_TAX_ID,
830 INVOICE_ID,
831 AMOUNT,
832 VENDOR_ID,
833 VENDOR_SITE_ID,
834 SECTION_TYPE,
835 TAX_LINE_NO,
836 TAX_TYPE,
837 TAX_CATEGORY_ID,
838 PRECEDENCE_1,
839 PRECEDENCE_2,
840 PRECEDENCE_3,
841 PRECEDENCE_4,
842 PRECEDENCE_5,
843 PRECEDENCE_6,
847 PRECEDENCE_10,
844 PRECEDENCE_7,
845 PRECEDENCE_8,
846 PRECEDENCE_9,
848 ACTUAL_TAX_ID,
849 PROCESS_STATUS,
850 PROCESS_MESSAGE,
851 CREATED_BY,
852 CREATION_DATE,
853 LAST_UPDATED_BY,
854 LAST_UPDATE_DATE,
855 LAST_UPDATE_LOGIN)
856 VALUES
857 (JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
858 p_inv_id,
859 lr_tax_code_details.TAX_AMOUNT,
860 ln_vendor_id,
861 ln_vendor_site_id,
862 lr_tax_code_details.SECTION_TYPE,
863 tax_ctg_lines_rec.LINE_NO,
864 lr_tax_code_details.TAX_TYPE,
865 tax_ctg_lines_rec.TAX_CATEGORY_ID,
866 tax_ctg_lines_rec.PRECEDENCE_1,
867 tax_ctg_lines_rec.PRECEDENCE_2,
868 tax_ctg_lines_rec.PRECEDENCE_3,
869 tax_ctg_lines_rec.PRECEDENCE_4,
870 tax_ctg_lines_rec.PRECEDENCE_5,
871 tax_ctg_lines_rec.PRECEDENCE_6,
872 tax_ctg_lines_rec.PRECEDENCE_7,
873 tax_ctg_lines_rec.PRECEDENCE_8,
874 tax_ctg_lines_rec.PRECEDENCE_9,
875 tax_ctg_lines_rec.PRECEDENCE_10,
876 tax_ctg_lines_rec.TAX_ID,
877 'D',
878 NULL,
879 FND_GLOBAL.USER_ID,
880 SYSDATE,
881 FND_GLOBAL.USER_ID,
882 SYSDATE,
883 FND_GLOBAL.LOGIN_ID);
884
885 end loop; -- c_get_tax_ctg_lines
886 return;
887 end if;*/
888 -- commented out by zhiwei.xin for bug 13792748 on 08-Mar-2012 end.
889
890 -- get GL calendar name from DTC REGIME
891 open c_get_regime_attr_value('TDS_CALENDAR');
892 fetch c_get_regime_attr_value into lv_gl_calendar_id;
893 close c_get_regime_attr_value;
894
895 -- get max and min accounting date
896 /*open c_get_accounting_date;
897 fetch c_get_accounting_date into ld_max_date, ld_min_date;
898 close c_get_accounting_date;*/
899 p_get_accounting_date(p_inv_id, ld_max_date,ld_min_date);
900
901 -- verify if accounting date is in the range of gl calendar date
902 open c_get_period_year(lv_gl_calendar_id, ld_max_date);
903 fetch c_get_period_year into ln_max_period_year;
904 close c_get_period_year;
905
906 open c_get_period_year(lv_gl_calendar_id, ld_min_date);
907 fetch c_get_period_year into ln_min_period_year;
908 close c_get_period_year;
909
910 if ln_max_period_year is NULL or
911 ln_min_period_year is NULL or
912 ln_max_period_year <> ln_min_period_year then
913
914 p_process_flag := 'E';
915 p_process_message := 'TDS Period is not defined.';
916 if G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL then
917 FND_LOG.STRING(G_LEVEL_STATEMENT,
918 G_MODULE_NAME || l_api_name,
919 'TDS Period is not defined / Invoice Accounting Date can not span across Financial Years.');
920 end if;
921 return;
922 end if;
923
924 -- get tax default option
925 if lv_inv_tlc = 'PREPAYMENT' then
926 lv_tax_default_option := G_VENDOR_SITE_DEF_SEC_CODE;
927 else
928 /*Commented out by Chong for bug#15939571 20121210 Start
929 open c_get_regime_attr_value('TDS_DEFAULTATION');
930 fetch c_get_regime_attr_value into lv_tax_default_option;
931 close c_get_regime_attr_value;
932 *Commented out by Chong for bug#15939571 20121210 End */
933
934 /* Commented out by Chong for bug#16367707 Start
935 --Added by Chong for bug#15939571 20121210 Start
936 -----------------------------------------------------------------------------------------------------
937 ----Added by Chong for bug15939571 begin
938 --When get count > 0, means AP invoice containing matched invoice lines
939 open c_check_is_matched;
940 fetch c_check_is_matched into ln_cnt_matched_lines;
941 close c_check_is_matched;
942 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_cnt_matched_lines: ' || ln_cnt_matched_lines);
943
944 if ln_cnt_matched_lines > 0 then
945 --PO/receipt Match AP invoice
946 lv_tax_default_option := G_VENDOR_SITE_DEF_SEC_CODE;
947 else
948 --Standalone AP invoice
949 --Check if any Natrual account - section mapping appliable.
950 lv_tax_default_option := G_NTL_ACC_SEC_MAP;
951
952 end if;
953 -----------------------------------------------------------------------------------------------------
954 --Added by Chong for bug#15939571 20121210 End
955 Commented out by Chong for bug#16367707 End */ --For PO/Receipt Match case, do not default by vendor site default method
956
957 --Added by Chong for bug#16367707 Start
958 -------------------------------------------------------------------
959 open c_get_regime_attr_value('TDS_VENDOR_DEFAULT_SEC_FLAG');
960 fetch c_get_regime_attr_value into lv_tds_vendor_default_flag;
961 close c_get_regime_attr_value;
962 lv_tax_default_option := G_NTL_ACC_SEC_MAP;
963 -------------------------------------------------------------------
964 --Added by Chong for bug#16367707 End
965
966 end if;
967
968 /*
969 process by tax default option
970 */
971 /*Commented out by Chong for bug#15939571 20121210 Start
972 if lv_tax_default_option is NULL then
973 p_process_flag := 'E';
974 p_process_message := 'TDS Defaultation Option is not defined in Regime Setup.';
978 'TDS Defaultation Option not defined in Regime Setup.');
975 if G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL then
976 FND_LOG.STRING(G_LEVEL_STATEMENT,
977 G_MODULE_NAME || l_api_name,
979 end if;
980 return;
981 end if;
982 Commented out by Chong for bug#15939571 20121210 End */
983
984 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'lv_tax_default_option: '||lv_tax_default_option);
985
986 if lv_tax_default_option = G_NTL_ACC_SEC_MAP then
987
988 if p_redefault_section_code = 'Y' then
989
990 -- open c_get_inv_dist;
991 -- loop
992 la_aida_tab.DELETE;
993 -- fetch c_get_inv_dist BULK COLLECT into la_aida_tab LIMIT LN_ARRAY_SIZE;
994 p_get_inv_dist(p_inv_id);
995
996 FOR l_index IN 1 .. la_aida_tab.COUNT LOOP
997
998 INSERT INTO
999 JAI_AP_TDS_DEFAULT (
1000 INVOICE_ID,
1001 INVOICE_LINE_NUMBER,
1002 DISTRIBUTION_LINE_NUMBER,
1003 INVOICE_DISTRIBUTION_ID,
1004 DIST_CODE_COMBINATION_ID,
1005 AMOUNT,
1006 TDS_SECTION_CODE,
1007 NATURAL_ACCOUNT_VALUE,
1008 DESCRIPTION,
1009 MATCH_STATUS_FLAG,
1010 LINE_TYPE_LOOKUP_CODE,
1011 ACCOUNTING_DATE,
1012 CREATION_DATE,
1013 CREATED_BY,
1014 LAST_UPDATE_DATE,
1015 LAST_UPDATED_BY,
1016 LAST_UPDATE_LOGIN )
1017 VALUES (
1018 la_aida_tab(l_index).INVOICE_ID,
1019 la_aida_tab(l_index).INVOICE_LINE_NUMBER,
1020 la_aida_tab(l_index).DISTRIBUTION_LINE_NUMBER,
1021 la_aida_tab(l_index).INVOICE_DISTRIBUTION_ID,
1022 la_aida_tab(l_index).DIST_CODE_COMBINATION_ID,
1023 la_aida_tab(l_index).AMOUNT,
1024 JAI_AP_UTILS_PKG.GET_SECTION_CODE(la_aida_tab(l_index).DIST_CODE_COMBINATION_ID
1025 ,lv_segment_name
1026 ,ln_legal_entity_id
1027 ,la_aida_tab(l_index).ACCOUNTING_DATE), --Added accounting_date by Chong for bug15939571
1028 JAI_AP_UTILS_PKG.GET_NATURAL_ACCOUNT_VALUE(la_aida_tab(l_index).DIST_CODE_COMBINATION_ID, lv_segment_name),
1029 la_aida_tab(l_index).DESCRIPTION,
1030 NVL(la_aida_tab(l_index).MATCH_STATUS_FLAG,'N'), --Added NVL for bug16707946
1031 la_aida_tab(l_index).LINE_TYPE_LOOKUP_CODE,
1032 la_aida_tab(l_index).ACCOUNTING_DATE,
1033 SYSDATE,
1034 FND_GLOBAL.USER_ID,
1035 SYSDATE,
1036 FND_GLOBAL.USER_ID,
1037 FND_GLOBAL.LOGIN_ID);
1038
1039 -- EXIT WHEN c_get_inv_dist%NOTFOUND;
1040 end loop;
1041 -- close c_get_inv_dist;
1042
1043 --Added by Chong for bug#15939571 20121212 Start
1044 --------------------------------------------------
1045 if NVL(lv_tds_vendor_default_flag, 'N') = 'Y' then --Added by Chong for bug#16367707 check regime Vendor Default Section Code flag
1046 -- get vendor site default section code
1047 open c_get_def_sec_code(ln_vendor_id, ln_vendor_site_id);
1048 fetch c_get_def_sec_code into lv_default_section_code;
1049 close c_get_def_sec_code;
1050
1051 --If section code on site is not setup, get from null site
1052 if lv_default_section_code is NULL then
1053 open c_get_null_sec_code(ln_vendor_id);
1054 fetch c_get_null_sec_code into lv_default_section_code;
1055 close c_get_null_sec_code;
1056 end if;
1057
1058 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Default section from vendor site: '||lv_default_section_code);
1059 --If any line missed mapping to any section, default on vendor site default section.
1060 UPDATE JAI_AP_TDS_DEFAULT
1061 SET TDS_SECTION_CODE = lv_default_section_code
1062 WHERE INVOICE_ID = p_inv_id
1063 AND TDS_SECTION_CODE IS NULL;
1064 end if; --NVL(lv_tds_vendor_default_flag, 'N') = 'Y' --Added by Chong for bug#16367707
1065 --------------------------------------------------
1066 --Added by Chong for bug#15939571 20121212 End
1067
1068 for sec_rec in c_get_section_code
1069 loop
1070 open c_sectionwise_cum_amt (sec_rec.SECTION_CODE);
1071 loop
1072 la_cum_amt_tab.DELETE;
1073 fetch c_sectionwise_cum_amt BULK COLLECT INTO la_cum_amt_tab LIMIT LN_ARRAY_SIZE;
1074 forall ln_index IN 1 .. la_cum_amt_tab.COUNT
1075 UPDATE JAI_AP_TDS_DEFAULT
1076 SET CUMULATIVE_AMT = la_cum_amt_tab(ln_index).CUMULATIVE_AMT
1077 WHERE INVOICE_ID = p_inv_id
1078 AND INVOICE_DISTRIBUTION_ID = la_cum_amt_tab(ln_index).INVOICE_DISTRIBUTION_ID;
1079 EXIT WHEN c_sectionwise_cum_amt%NOTFOUND;
1080 end loop;
1081 close c_sectionwise_cum_amt;
1082 end loop;
1083 end if; -- p_redefault_section_code = 'Y'
1084
1085 if p_redefault_tax_category = 'Y' then
1086
1087 -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012 begin
1088
1089 if NVL(lv_confirm_flg, 'N') = 'N' OR lv_pan_no = 'PANAPPLIED' OR lv_pan_no = 'PANNOTAVBL' OR lv_pan_no = 'PANINVALID' then
1090
1091 /*Commented by Chong for bug#16281516 Start
1095 Commented by Chong for bug#16281516 End*/
1092 open c_get_regime_attr_value('TAX_CATEGORY');
1093 fetch c_get_regime_attr_value into ln_tax_ctg_id;
1094 close c_get_regime_attr_value;
1096 -- Added by Chong for bug#16281516 Start
1097 -----------------------------------------------------
1098 open c_get_ou_regime_value ('TAX_CATEGORY'
1099 ,ln_org_id
1100 );
1101 fetch c_get_ou_regime_value into ln_tax_ctg_id;
1102 close c_get_ou_regime_value ;
1103 -----------------------------------------------------
1104 -- Added by Chong for bug#16281516 End
1105
1106 --Added by Chong for bug#16274617 Start
1107 -----------------------------------------------------------------------------
1108 -- all Taxes attached to the Tax category are not end dated before attaching the same
1109 open c_check_taxes_end_date(ln_tax_ctg_id
1110 ,ld_min_date
1111 );
1112 fetch c_check_taxes_end_date into lv_end_date_check;
1113 close c_check_taxes_end_date;
1114
1115 open c_check_taxes_end_date(ln_tax_ctg_id
1116 ,ld_max_date
1117 );
1118 fetch c_check_taxes_end_date into lv_end_date_check2;
1119 close c_check_taxes_end_date;
1120
1121 if (NVL(lv_end_date_check, '0') = '1') or (NVL(lv_end_date_check2, '0') = '1')then
1122 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'NO PAN 1@Tax end date issue in tax category: '||ln_tax_ctg_id);
1123 return;
1124 end if;
1125 -----------------------------------------------------------------------------
1126 --Added by Chong for bug#16274617 End
1127
1128 UPDATE JAI_AP_TDS_DEFAULT
1129 SET TAX_CATEGORY_ID = ln_tax_ctg_id
1130 WHERE INVOICE_ID = p_inv_id;
1131 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'NO PAN 1@ln_tax_ctg_id: '||ln_tax_ctg_id);
1132 else
1133
1134 -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012 end.
1135
1136 for disnt_sec_code_rec IN c_get_disnt_sec_code
1137 loop
1138 ln_thr_header_id := NULL;
1139 lv_exp_setup_flg := NULL;
1140 lv_multi_rate_flg := NULL;
1141
1142 open c_tds_th_vsite_v (lv_vendor_type, ln_vendor_id, ln_vendor_site_id, disnt_sec_code_rec.TDS_SECTION_CODE);
1143 fetch c_tds_th_vsite_v into ln_thr_header_id, lv_exp_setup_flg, lv_multi_rate_flg;
1144 close c_tds_th_vsite_v;
1145
1146 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 begin
1147 ld_max_date := null;
1148 ld_min_date := null;
1149
1150 open c_sec_accounting_date (disnt_sec_code_rec.TDS_SECTION_CODE);
1151 fetch c_sec_accounting_date into ld_max_date, ld_min_date;
1152 close c_sec_accounting_date;
1153 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 end.
1154
1155 -- added by zhiwei.xin for solution change on 01-Mar-2012 begin
1156
1157 /* Verify if the Max and Minimum Accounting Date is between the Start and End Date */
1158 if ln_thr_header_id is not NULL then
1159 lv_valid_flg := null;
1160 open c_valid_accounting_date (ln_thr_header_id, ld_max_date, ld_min_date);
1161 fetch c_valid_accounting_date into lv_valid_flg;
1162 close c_valid_accounting_date;
1163
1164 if nvl(lv_valid_flg, 'N') = 'N' then
1165 ln_thr_header_id := null;
1166 open c_get_thr_hdr_id (lv_vendor_type, disnt_sec_code_rec.TDS_SECTION_CODE);
1167 fetch c_get_thr_hdr_id into ln_thr_header_id, lv_multi_rate_flg;
1168 close c_get_thr_hdr_id;
1169
1170 end if;
1171 end if;
1172
1173 -- added by zhiwei.xin for solution change on 01-Mar-2012 end.
1174
1175 if ln_thr_header_id is NULL then
1176 p_process_flag := 'N';
1177 p_process_message := 'Threshold Setup does not exist. Hence no Tax will be deducted/applicable';
1178 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1179 FND_LOG.STRING(G_LEVEL_STATEMENT,
1180 G_MODULE_NAME || l_api_name,
1181 'Threshold Setup does not exist for Vendor Type ' ||
1182 lv_vendor_type ||
1183 ' and Section Code ' ||
1184 disnt_sec_code_rec.TDS_SECTION_CODE);
1185 END IF;
1186 return;
1187 end if;
1188
1189 -- get effective amount
1190 --Added by Chong for Bug#13802244 2012/09/17 Start
1191 ----------------------------------------------------------------------
1192 IF p_call_from = G_CALL_FROM_ROLLBACK THEN
1193 -- when call from threshold rollback, should not get cumulative from threshold group
1194 ln_effective_amount := 0;
1195 ELSE
1196 ----------------------------------------------------------------------
1197 --Added by Chong for Bug#13802244 2012/09/17 End
1198 open c_thrd_group(ln_vendor_id, lv_pan_no, lv_tan_no, ln_max_period_year, disnt_sec_code_rec.TDS_SECTION_CODE);
1202
1199 fetch c_thrd_group into ln_effective_amount;
1200 close c_thrd_group;
1201 END IF; --p_call_from = G_CALL_FROM_ROLLBACK --Added by Chong for Bug#13802244 2012/09/17
1203 ln_effective_amount := NVL(ln_effective_amount,0); --Added by Chong for bug#16274617
1204
1205 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 begin
1206
1207 ln_thr_slab_id_old := null;
1208 ln_thr_slab_id_new := null;
1209
1210 /*Commented out by Chong for bug#15939571 20121213 Start if multi on ,should get old slab by multi also
1211 -- Get old slab should check multi rate on/off, by different cursor
1212 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount, ld_max_date, ld_min_date);
1213 fetch c_get_thr_slab_id into ln_thr_slab_id_old;
1214 close c_get_thr_slab_id;
1215
1216 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 end.
1217 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, '1@ln_thr_slab_id_old: '||ln_thr_slab_id_old);
1218 Commented out by Chong for bug#15939571 20121213 End*/
1219
1220 UPDATE JAI_AP_TDS_DEFAULT
1221 SET CUMULATIVE_AMT = CUMULATIVE_AMT + NVL(ln_effective_amount,0)
1222 WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1223 AND INVOICE_ID = p_inv_id;
1224
1225 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name,
1226 'Processing Section: '||disnt_sec_code_rec.TDS_SECTION_CODE || ' Header ID:' || ln_thr_header_id || ' Multi Rate:' || lv_multi_rate_flg);
1227
1228 if lv_multi_rate_flg = 'Y' then
1229 --Added by Chong for bug#15939571 20121213 Start
1230 ----------------------------------------------------------------------
1231 FOR get_grp_sec_act_rec IN c_get_grp_sec_act(disnt_sec_code_rec.TDS_SECTION_CODE)
1232 LOOP
1233
1234 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name,
1235 'Processing Natural Account: '||get_grp_sec_act_rec.natural_account_value || ' Ac Date:' || get_grp_sec_act_rec.accounting_date);
1236
1237 open c_check_effective_date(cp_legal_entity_id => ln_legal_entity_id
1238 ,cp_tds_section_code => disnt_sec_code_rec.tds_section_code
1239 ,cp_natural_account_value => get_grp_sec_act_rec.natural_account_value
1240 ,cp_accounting_date => get_grp_sec_act_rec.accounting_date
1241 );
1242 fetch c_check_effective_date into ln_cnt_effective_date;
1243 close c_check_effective_date;
1244
1245 if ln_cnt_effective_date > 0 then
1246 open c_get_thr_slab_id_mul (ln_thr_header_id
1247 ,'CUMULATIVE'
1248 ,ln_effective_amount
1249 ,ld_max_date
1250 ,ld_min_date
1251 ,ln_legal_entity_id
1252 ,disnt_sec_code_rec.tds_section_code
1253 ,get_grp_sec_act_rec.natural_account_value
1254 ,get_grp_sec_act_rec.accounting_date);
1255 fetch c_get_thr_slab_id_mul into ln_thr_slab_id_old;
1256 close c_get_thr_slab_id_mul;
1257 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on 1@ln_thr_slab_id_old: '||ln_thr_slab_id_old);
1258 ----------------------------------------------------------------------
1259 --Added by Chong for bug#15939571 20121213 End
1260
1261 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 begin
1262 open c_get_thr_slab_id_mul(ln_thr_header_id
1263 , 'CUMULATIVE'
1264 , disnt_sec_code_rec.SEC_AMOUNT + ln_effective_amount
1265 , ld_max_date
1266 , ld_min_date
1267 , ln_legal_entity_id
1268 , disnt_sec_code_rec.tds_section_code
1269 , get_grp_sec_act_rec.natural_account_value --Added by Chong for bug#15939571 20121213
1270 , get_grp_sec_act_rec.accounting_date --Added by Chong for bug#15939571 20121213
1271 );
1272 --Added ln_legal_entity_id ,disnt_sec_code_rec.tds_section_code by Chong for Bug#13802244 2012/09/21
1273 fetch c_get_thr_slab_id_mul into ln_thr_slab_id_new;
1274 close c_get_thr_slab_id_mul;
1275 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on 2@ln_thr_slab_id_new: '||ln_thr_slab_id_new);
1276
1277 if nvl(ln_thr_slab_id_new,0) = 0 then
1278 open c_get_thr_slab_id_mul(ln_thr_header_id
1279 , 'SINGLE'
1280 , disnt_sec_code_rec.SEC_AMOUNT
1281 , ld_max_date
1282 , ld_min_date
1283 , ln_legal_entity_id
1284 , disnt_sec_code_rec.tds_section_code
1288 --Added ln_legal_entity_id ,disnt_sec_code_rec.tds_section_code by Chong for Bug#13802244 2012/09/21
1285 , get_grp_sec_act_rec.natural_account_value --Added by Chong for bug#15939571 20121213
1286 , get_grp_sec_act_rec.accounting_date --Added by Chong for bug#15939571 20121213
1287 );
1289 fetch c_get_thr_slab_id_mul into ln_thr_slab_id_new;
1290 close c_get_thr_slab_id_mul;
1291 end if;
1292
1293 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 end.
1294 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on 3@ln_thr_slab_id_new: '||ln_thr_slab_id_new);
1295 for thrd_details_rec IN c_thrd_details(ln_thr_header_id
1296 ,ln_org_id
1297 ,ln_thr_slab_id_new --Added by Chong for bug#16274617
1298 )
1299 loop
1300 -- all Taxes attached to the Tax category are not end dated before attaching the same
1301 open c_check_taxes_end_date(thrd_details_rec.TAX_CATEGORY_ID
1302 ,get_grp_sec_act_rec.accounting_date --Added by Chong for bug#16274617
1303 );
1304 fetch c_check_taxes_end_date into lv_end_date_check;
1305 close c_check_taxes_end_date;
1306
1307 if (NVL(lv_end_date_check, '0') = '1') then
1308 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Mult on4@Tax end date issue in tax category: '||thrd_details_rec.TAX_CATEGORY_ID);
1309 return;
1310 end if;
1311
1312 UPDATE JAI_AP_TDS_DEFAULT
1313 SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
1314 --UPdated by Zhiwei Hou on 20120116 begin
1315 -----------------------------------------------------
1316 --WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
1317 WHERE
1318 (
1319 (thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
1320 OR
1321 (thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
1322 )
1323 ------------------------------------------------------
1324 --UPdated by Zhiwei Hou on 20120116 end
1325 AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND NVL(thrd_details_rec.TO_DATE,ACCOUNTING_DATE)
1326 AND INVOICE_ID = p_inv_id
1327 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
1328 --Added by Chong for bug#15939571 20121213 Start
1329 ----------------------------------------------------------------------
1330 -- We have to added check to effective date here, if not may update end dated records which should default by default type
1331 AND natural_account_value = get_grp_sec_act_rec.natural_account_value
1332 AND accounting_date = get_grp_sec_act_rec.accounting_date
1333 AND EXISTS (
1334 SELECT 1
1335 from JAI_AP_TDS_THHOLD_ACCOUNT jatta
1336 ,JAI_DTC_SCTN_ACCOUNT_MAPPING jdsam
1337 where jatta.threshold_hdr_id = thrd_details_rec.threshold_hdr_id
1338 and jatta.threshold_type_id = thrd_details_rec.threshold_type_id
1339 and jatta.legal_entity_id = jdsam.legal_entity_id
1340 and jatta.natural_account_value = jdsam.natural_account_value
1341 and jatta.legal_entity_id = ln_legal_entity_id
1342 and jdsam.dtc_section_code = disnt_sec_code_rec.TDS_SECTION_CODE
1343 and jatta.natural_account_value = get_grp_sec_act_rec.natural_account_value
1344 and JAI_AP_TDS_DEFAULT.accounting_date between nvl(jdsam.from_date,JAI_AP_TDS_DEFAULT.accounting_date)
1345 and nvl(jdsam.to_date, JAI_AP_TDS_DEFAULT.accounting_date)
1346 )
1347 ;
1348 ----------------------------------------------------------------------
1349 --Added by Chong for bug#15939571 20121213 End
1350 /*Commented out by Chong for bug#15939571 20121213 Start
1351 AND NATURAL_ACCOUNT_VALUE IN
1352 (SELECT NATURAL_ACCOUNT_VALUE
1353 FROM JAI_AP_TDS_THHOLD_ACCOUNT
1354 WHERE THRESHOLD_HDR_ID = ln_thr_header_id
1355 AND THRESHOLD_TYPE_ID = thrd_details_rec.THRESHOLD_TYPE_ID
1356 AND LEGAL_ENTITY_ID = ln_legal_entity_id);
1357 Commented out by Chong for bug#15939571 20121213 End */
1358 end loop;
1359 --Added by Chong for bug#15939571 20121213 Start
1360 ----------------------------------------------------------------------
1364 fetch c_tax_category_id into ln_tax_ctg_id;
1361 if nvl(ln_thr_slab_id_old, 0) <> nvl(ln_thr_slab_id_new,0) then
1362
1363 open c_tax_category_id(ln_thr_slab_id_new, ln_org_id);
1365 close c_tax_category_id;
1366
1367 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on 5@ln_tax_ctg_id: '||ln_tax_ctg_id);
1368
1369 UPDATE JAI_AP_TDS_DEFAULT
1370 SET TAX_CATEGORY_ID = ln_tax_ctg_id
1371 WHERE INVOICE_ID = p_inv_id
1372 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1373 AND ACCOUNTING_DATE = get_grp_sec_act_rec.accounting_date
1374 AND TAX_CATEGORY_ID IS NULL;
1375
1376 end if;
1377 else
1378 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount, ld_max_date, ld_min_date);
1379 fetch c_get_thr_slab_id into ln_thr_slab_id_old;
1380 close c_get_thr_slab_id;
1381 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on/eff off 1@ln_thr_slab_id_old: '||ln_thr_slab_id_old);
1382
1383 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount + disnt_sec_code_rec.SEC_AMOUNT, ld_max_date, ld_min_date);
1384 fetch c_get_thr_slab_id into ln_thr_slab_id_new;
1385 close c_get_thr_slab_id;
1386
1387 if (nvl(ln_thr_slab_id_new, '0') = '0') then
1388 open c_get_thr_slab_id (ln_thr_header_id, 'SINGLE', disnt_sec_code_rec.SEC_AMOUNT, ld_max_date, ld_min_date);
1389 fetch c_get_thr_slab_id into ln_thr_slab_id_new;
1390 close c_get_thr_slab_id;
1391 end if;
1392
1393 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on/eff off 2@ln_thr_slab_id_new: '||ln_thr_slab_id_new);
1394
1395 for thrd_details_rec IN c_thrd_details(ln_thr_header_id
1396 ,ln_org_id
1397 ,ln_thr_slab_id_new --Added by Chong for bug#16274617
1398 )
1399 loop
1400
1401 -- all Taxes attached to the Tax category are not end dated before attaching the same
1402 open c_check_taxes_end_date(thrd_details_rec.TAX_CATEGORY_ID
1403 ,get_grp_sec_act_rec.accounting_date --Added by Chong for bug#16274617
1404 );
1405 fetch c_check_taxes_end_date into lv_end_date_check;
1406 close c_check_taxes_end_date;
1407
1408 if (NVL(lv_end_date_check, '0') = '1') then
1409 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on/eff off 3@Tax end date issue in tax category: '||thrd_details_rec.TAX_CATEGORY_ID);
1410 return;
1411 end if;
1412 UPDATE JAI_AP_TDS_DEFAULT
1413 SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
1414 --UPdated by Zhiwei Hou on 20120116 begin
1415 -----------------------------------------------------
1416 --WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
1417 WHERE
1418 (
1419 (thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
1420 OR
1421 (thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
1422 )
1423 ------------------------------------------------------
1424 --UPdated by Zhiwei Hou on 20120116 end
1425 AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND NVL(thrd_details_rec.TO_DATE, ACCOUNTING_DATE)
1426 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
1427 AND NATURAL_ACCOUNT_VALUE = get_grp_sec_act_rec.natural_account_value
1428 AND accounting_date = get_grp_sec_act_rec.accounting_date
1429 AND INVOICE_ID = p_inv_id;
1430
1431 end loop;
1432
1433 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 begin
1434 if nvl(ln_thr_slab_id_old, 0) <> nvl(ln_thr_slab_id_new,0) then
1435
1436 open c_tax_category_id(ln_thr_slab_id_new, ln_org_id);
1437 fetch c_tax_category_id into ln_tax_ctg_id;
1438 close c_tax_category_id;
1439
1440 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi on/eff off 4@ln_tax_ctg_id: '||ln_tax_ctg_id);
1441
1442 UPDATE JAI_AP_TDS_DEFAULT
1443 SET TAX_CATEGORY_ID = ln_tax_ctg_id
1444 WHERE INVOICE_ID = p_inv_id
1445 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1446 AND accounting_date = get_grp_sec_act_rec.accounting_date
1447 AND TAX_CATEGORY_ID IS NULL;
1448
1452 end if;
1449 end if;
1450 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 end.
1451
1453 END LOOP; --c_get_grp_sec_act(disnt_sec_code_rec.TDS_SECTION_CODE)
1454 ----------------------------------------------------------------------
1455 --Added by Chong for bug#15939571 20121213 End
1456
1457 ELSE
1458 --Added by Chong for bug#15939571 20121213 Start
1459 ----------------------------------------------------------------------
1460 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount, ld_max_date, ld_min_date);
1461 fetch c_get_thr_slab_id into ln_thr_slab_id_old;
1462 close c_get_thr_slab_id;
1463 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi off 1@ln_thr_slab_id_old: '||ln_thr_slab_id_old);
1464 ----------------------------------------------------------------------
1465 --Added by Chong for bug#15939571 20121213 End
1466 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 begin
1467 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount + disnt_sec_code_rec.SEC_AMOUNT, ld_max_date, ld_min_date);
1468 fetch c_get_thr_slab_id into ln_thr_slab_id_new;
1469 close c_get_thr_slab_id;
1470
1471
1472 if (nvl(ln_thr_slab_id_new, '0') = '0') then
1473 open c_get_thr_slab_id (ln_thr_header_id, 'SINGLE', disnt_sec_code_rec.SEC_AMOUNT, ld_max_date, ld_min_date);
1474 fetch c_get_thr_slab_id into ln_thr_slab_id_new;
1475 close c_get_thr_slab_id;
1476 end if;
1477 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 end.
1478 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'multi off @2ln_thr_slab_id_new: '||ln_thr_slab_id_new);
1479
1480 for thrd_details_rec IN c_thrd_details(ln_thr_header_id
1481 ,ln_org_id
1482 ,ln_thr_slab_id_new --Added by Chong for bug#16274617
1483 )
1484 loop
1485
1486 -- all Taxes attached to the Tax category are not end dated before attaching the same
1487 open c_check_taxes_end_date(thrd_details_rec.TAX_CATEGORY_ID
1488 ,ld_min_date --Added by Chong for bug#16274617
1489 );
1490 fetch c_check_taxes_end_date into lv_end_date_check;
1491 close c_check_taxes_end_date;
1492 --Added by Chong for bug#16274617 Start
1493 -------------------------------------------------------------------
1494 open c_check_taxes_end_date(thrd_details_rec.TAX_CATEGORY_ID
1495 ,ld_max_date
1496 );
1497 fetch c_check_taxes_end_date into lv_end_date_check2;
1498 close c_check_taxes_end_date;
1499 if (NVL(lv_end_date_check, '0') = '1') or (NVL(lv_end_date_check2, '0') = '1') then
1500 -------------------------------------------------------------------
1501 --if (NVL(lv_end_date_check, '0') = '1') then --Commented by Chong for bug#16274617
1502 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Mult off3@Tax end date issue in tax category: '||thrd_details_rec.TAX_CATEGORY_ID);
1503 return;
1504 end if;
1505 UPDATE JAI_AP_TDS_DEFAULT
1506 SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
1507 --UPdated by Zhiwei Hou on 20120116 begin
1508 -----------------------------------------------------
1509 --WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
1510 WHERE
1511 (
1512 (thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
1513 OR
1514 (thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
1515 )
1516 ------------------------------------------------------
1517 --UPdated by Zhiwei Hou on 20120116 end
1518 AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND NVL(thrd_details_rec.TO_DATE, ACCOUNTING_DATE)
1519 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
1520 AND INVOICE_ID = p_inv_id;
1521
1522 end loop;
1523
1524 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 begin
1525 if nvl(ln_thr_slab_id_old, 0) <> nvl(ln_thr_slab_id_new,0) then
1526
1527 open c_tax_category_id(ln_thr_slab_id_new, ln_org_id);
1528 fetch c_tax_category_id into ln_tax_ctg_id;
1529 close c_tax_category_id;
1530
1531 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Mult off4@ln_tax_ctg_id: '||ln_tax_ctg_id);
1532
1533 UPDATE JAI_AP_TDS_DEFAULT
1534 SET TAX_CATEGORY_ID = ln_tax_ctg_id
1535 WHERE INVOICE_ID = p_inv_id
1536 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1537 AND TAX_CATEGORY_ID IS NULL;
1541 end if;
1538
1539 end if;
1540 -- added by zhiwei.xin for bug 13745243 on 01-MAR-2012 end.
1542
1543 UPDATE JAI_AP_TDS_DEFAULT
1544 SET CUMULATIVE_AMT = CUMULATIVE_AMT - NVL(ln_effective_amount,0)
1545 WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1546 AND INVOICE_ID = p_inv_id;
1547
1548 end loop; -- c_get_disnt_sec_code
1549
1550 end if; -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012.
1551
1552 end if; -- p_redefault_tax_category = 'Y'
1553
1554 elsif lv_tax_default_option = G_VENDOR_SITE_DEF_SEC_CODE then
1555
1556 if p_redefault_section_code = 'Y' then
1557
1558 -- get default section code
1559 open c_get_def_sec_code(ln_vendor_id, ln_vendor_site_id);
1560 fetch c_get_def_sec_code into lv_default_section_code;
1561 close c_get_def_sec_code;
1562
1563 --Added by Chong for bug#15939571 20121212 Start
1564 --------------------------------------------------
1565 --If section code on site is not setup, get from null site
1566 if lv_default_section_code is NULL then
1567 open c_get_null_sec_code(ln_vendor_id);
1568 fetch c_get_null_sec_code into lv_default_section_code;
1569 close c_get_null_sec_code;
1570 end if;
1571 --------------------------------------------------
1572 --Added by Chong for bug#15939571 20121212 End
1573
1574 -- open c_get_inv_dist;
1575 -- loop
1576 la_aida_tab.DELETE;
1577 -- fetch c_get_inv_dist BULK COLLECT into la_aida_tab LIMIT LN_ARRAY_SIZE;
1578 p_get_inv_dist(p_inv_id);
1579 for l_index IN 1 .. la_aida_tab.COUNT loop
1580
1581 INSERT INTO
1582 JAI_AP_TDS_DEFAULT (
1583 INVOICE_ID,
1584 INVOICE_LINE_NUMBER,
1585 DISTRIBUTION_LINE_NUMBER,
1586 INVOICE_DISTRIBUTION_ID,
1587 DIST_CODE_COMBINATION_ID,
1588 AMOUNT,
1589 TDS_SECTION_CODE,
1590 DESCRIPTION,
1591 MATCH_STATUS_FLAG,
1592 LINE_TYPE_LOOKUP_CODE,
1593 ACCOUNTING_DATE,
1594 CREATION_DATE,
1595 CREATED_BY,
1596 LAST_UPDATE_DATE,
1597 LAST_UPDATED_BY,
1598 LAST_UPDATE_LOGIN )
1599 VALUES (
1600 la_aida_tab(l_index).INVOICE_ID,
1601 la_aida_tab(l_index).INVOICE_LINE_NUMBER,
1602 la_aida_tab(l_index).DISTRIBUTION_LINE_NUMBER,
1603 la_aida_tab(l_index).INVOICE_DISTRIBUTION_ID,
1604 la_aida_tab(l_index).DIST_CODE_COMBINATION_ID,
1605 la_aida_tab(l_index).AMOUNT,
1606 lv_default_section_code,
1607 la_aida_tab(l_index).DESCRIPTION,
1608 NVL(la_aida_tab(l_index).MATCH_STATUS_FLAG,'N'), --Added NVL for bug16707946
1609 la_aida_tab(l_index).LINE_TYPE_LOOKUP_CODE,
1610 la_aida_tab(l_index).ACCOUNTING_DATE,
1611 SYSDATE,
1612 FND_GLOBAL.USER_ID,
1613 SYSDATE,
1614 FND_GLOBAL.USER_ID,
1615 FND_GLOBAL.LOGIN_ID);
1616
1617 -- EXIT WHEN c_get_inv_dist%NOTFOUND;
1618 end loop;
1619 -- close c_get_inv_dist;
1620
1621 for sec_rec in c_get_section_code
1622 loop
1623 open c_sectionwise_cum_amt (sec_rec.SECTION_CODE);
1624 loop
1625 la_cum_amt_tab.DELETE;
1626 fetch c_sectionwise_cum_amt BULK COLLECT INTO la_cum_amt_tab LIMIT LN_ARRAY_SIZE;
1627 forall ln_index IN 1 .. la_cum_amt_tab.COUNT
1628 UPDATE JAI_AP_TDS_DEFAULT
1629 SET CUMULATIVE_AMT = la_cum_amt_tab(ln_index).CUMULATIVE_AMT
1630 WHERE INVOICE_ID = p_inv_id
1631 AND INVOICE_DISTRIBUTION_ID = la_cum_amt_tab(ln_index).INVOICE_DISTRIBUTION_ID;
1632 exit when c_sectionwise_cum_amt%notfound;
1633 end loop;
1634 close c_sectionwise_cum_amt;
1635 end loop;
1636
1637 end if; -- p_redefault_section_code = 'Y'
1638
1639 if p_redefault_tax_category = 'Y' then
1640
1641 -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012 begin
1642
1643 if NVL(lv_confirm_flg, 'N') = 'N' OR lv_pan_no = 'PANAPPLIED' OR lv_pan_no = 'PANNOTAVBL' OR lv_pan_no = 'PANINVALID' then
1644
1645 /*Commented by Chong for bug#16281516 Start
1646 open c_get_regime_attr_value('TAX_CATEGORY');
1647 fetch c_get_regime_attr_value into ln_tax_ctg_id;
1648 close c_get_regime_attr_value;
1649 Commented by Chong for bug#16281516 End*/
1650 -- Added by Chong for bug#16281516 Start
1651 -----------------------------------------------------
1652 open c_get_ou_regime_value ('TAX_CATEGORY'
1653 ,ln_org_id
1654 );
1655 fetch c_get_ou_regime_value into ln_tax_ctg_id;
1656 close c_get_ou_regime_value ;
1657 -----------------------------------------------------
1658 -- Added by Chong for bug#16281516 End
1659
1660 --Added by Chong for bug#16274617 Start
1661 -----------------------------------------------------------------------------
1662 -- all Taxes attached to the Tax category are effective in the GL time range of invoice
1663 open c_check_taxes_end_date(ln_tax_ctg_id
1664 ,ld_min_date
1668 open c_check_taxes_end_date(ln_tax_ctg_id
1665 );
1666 fetch c_check_taxes_end_date into lv_end_date_check;
1667 close c_check_taxes_end_date;
1669 ,ld_max_date
1670 );
1671 fetch c_check_taxes_end_date into lv_end_date_check2;
1672 close c_check_taxes_end_date;
1673
1674 if (NVL(lv_end_date_check, '0') = '1') or (NVL(lv_end_date_check2, '0') = '1')then
1675 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'NO PAN 2@Tax end date issue in tax category: '||ln_tax_ctg_id);
1676 return;
1677 end if;
1678 -----------------------------------------------------------------------------
1679 --Added by Chong for bug#16274617 End
1680
1681 UPDATE JAI_AP_TDS_DEFAULT
1682 SET TAX_CATEGORY_ID = ln_tax_ctg_id
1683 WHERE INVOICE_ID = p_inv_id;
1684
1685 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'NO PAN 2@ln_tax_ctg_id: '||ln_tax_ctg_id);
1686 else
1687
1688 -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012 end.
1689
1690 for disnt_sec_code_rec IN c_get_disnt_sec_code
1691 loop
1692 ln_thr_header_id := NULL;
1693 lv_exp_setup_flg := NULL;
1694 lv_multi_rate_flg := NULL;
1695
1696 open c_tds_th_vsite_v (lv_vendor_type, ln_vendor_id, ln_vendor_site_id, disnt_sec_code_rec.TDS_SECTION_CODE);
1697 fetch c_tds_th_vsite_v into ln_thr_header_id, lv_exp_setup_flg, lv_multi_rate_flg;
1698 close c_tds_th_vsite_v;
1699
1700 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 begin
1701 ld_max_date := null;
1702 ld_min_date := null;
1703
1704 open c_sec_accounting_date (disnt_sec_code_rec.TDS_SECTION_CODE);
1705 fetch c_sec_accounting_date into ld_max_date, ld_min_date;
1706 close c_sec_accounting_date;
1707 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 end.
1708
1709 -- added by zhiwei.xin for solution change on 01-Mar-2012 begin
1710
1711 /* Verify if the Max and Minimum Accounting Date is between the Start and End Date */
1712 if ln_thr_header_id is not NULL then
1713 lv_valid_flg := null;
1714 open c_valid_accounting_date (ln_thr_header_id, ld_max_date, ld_min_date);
1715 fetch c_valid_accounting_date into lv_valid_flg;
1716 close c_valid_accounting_date;
1717
1718 if nvl(lv_valid_flg, 'N') = 'N' then
1719 ln_thr_header_id := null;
1720 open c_get_thr_hdr_id (lv_vendor_type, disnt_sec_code_rec.TDS_SECTION_CODE);
1721 fetch c_get_thr_hdr_id into ln_thr_header_id, lv_multi_rate_flg;
1722 close c_get_thr_hdr_id;
1723
1724 end if;
1725 end if;
1726
1727 -- added by zhiwei.xin for solution change on 01-Mar-2012 end.
1728
1729 if ln_thr_header_id is NULL then
1730 p_process_flag := 'N';
1731 p_process_message := 'Threshold Setup does not exist. Hence no Tax will be deducted/applicable';
1732 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1733 FND_LOG.STRING(G_LEVEL_STATEMENT,
1734 G_MODULE_NAME || l_api_name,
1735 'Threshold Setup does not exist for Vendor Type ' ||
1736 lv_vendor_type ||
1737 ' and Section Code ' ||
1738 disnt_sec_code_rec.TDS_SECTION_CODE);
1739 END IF;
1740 return;
1741 end if;
1742
1743 -- get effective amount
1744 --Added by Chong for Bug#13802244 2012/09/17 Start
1745 ----------------------------------------------------------------------
1746 IF p_call_from = G_CALL_FROM_ROLLBACK THEN
1747 -- when call from threshold rollback, should not get cumulative from threshold group
1748 ln_effective_amount := 0;
1749 ELSE
1750 ----------------------------------------------------------------------
1751 --Added by Chong for Bug#13802244 2012/09/17 End
1752 open c_thrd_group(ln_vendor_id, lv_pan_no, lv_tan_no, ln_max_period_year, disnt_sec_code_rec.TDS_SECTION_CODE);
1753 fetch c_thrd_group into ln_effective_amount;
1754 close c_thrd_group;
1755 END IF; --p_call_from = G_CALL_FROM_ROLLBACK --Added by Chong for Bug#13802244 2012/09/17
1756
1757 ln_effective_amount := NVL(ln_effective_amount,0); --Added by Chong for bug#16274617
1758
1759 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 begin
1760
1761 ln_thr_slab_id_old := null;
1762 ln_thr_slab_id_new := null;
1763
1764 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount, ld_max_date, ld_min_date);
1765 fetch c_get_thr_slab_id into ln_thr_slab_id_old;
1766 close c_get_thr_slab_id;
1767
1768 open c_get_thr_slab_id (ln_thr_header_id, 'CUMULATIVE', ln_effective_amount + disnt_sec_code_rec.SEC_AMOUNT, ld_max_date, ld_min_date);
1769 fetch c_get_thr_slab_id into ln_thr_slab_id_new;
1770 close c_get_thr_slab_id;
1771
1772 if (nvl(ln_thr_slab_id_new, '0') = '0') then
1773 open c_get_thr_slab_id (ln_thr_header_id, 'SINGLE', disnt_sec_code_rec.SEC_AMOUNT, ld_max_date, ld_min_date);
1774 fetch c_get_thr_slab_id into ln_thr_slab_id_new;
1778 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 end.
1775 close c_get_thr_slab_id;
1776 end if;
1777
1779
1780 UPDATE JAI_AP_TDS_DEFAULT
1781 SET CUMULATIVE_AMT = CUMULATIVE_AMT + NVL(ln_effective_amount,0)
1782 WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1783 AND INVOICE_ID = p_inv_id;
1784
1785 for thrd_details_rec IN c_thrd_details(ln_thr_header_id
1786 ,ln_org_id
1787 ,ln_thr_slab_id_new --Added by Chong for bug#16274617
1788 )
1789 loop
1790
1791 -- all Taxes attached to the Tax category are not end dated before attaching the same
1792 open c_check_taxes_end_date(thrd_details_rec.TAX_CATEGORY_ID
1793 ,ld_min_date --Added by Chong for bug#16274617
1794 );
1795 fetch c_check_taxes_end_date into lv_end_date_check;
1796 close c_check_taxes_end_date;
1797 --Added by Chong for bug#16274617 start
1798 --------------------------------------------------------------------
1799 open c_check_taxes_end_date(thrd_details_rec.TAX_CATEGORY_ID
1800 ,ld_max_date
1801 );
1802 fetch c_check_taxes_end_date into lv_end_date_check2;
1803 close c_check_taxes_end_date;
1804 if (NVL(lv_end_date_check, '0') = '1') or (NVL(lv_end_date_check2, '0') = '1') then
1805 --------------------------------------------------------------------
1806 --Added by Chong for bug#16274617 end
1807 --if (NVL(lv_end_date_check, '0') = '1') then --Comment by Chong for bug#16274617
1808 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Vendor site default @Tax end date issue in tax category: '||thrd_details_rec.TAX_CATEGORY_ID);
1809 return;
1810 end if;
1811
1812 UPDATE JAI_AP_TDS_DEFAULT
1813 SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
1814 --UPdated by Zhiwei Hou on 20120116 begin
1815 -----------------------------------------------------
1816 --WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
1817 WHERE
1818 (
1819 (thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
1820 OR
1821 (thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
1822 )
1823 ------------------------------------------------------
1824 --UPdated by Zhiwei Hou on 20120116 end
1825 AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND nvl(thrd_details_rec.TO_DATE,ACCOUNTING_DATE)
1826 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
1827 AND INVOICE_ID = p_inv_id;
1828 end loop;
1829
1830 UPDATE JAI_AP_TDS_DEFAULT
1831 SET CUMULATIVE_AMT = CUMULATIVE_AMT - NVL(ln_effective_amount,0)
1832 WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1833 AND INVOICE_ID = p_inv_id;
1834
1835 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 begin
1836
1837 if nvl(ln_thr_slab_id_old, 0) <> nvl(ln_thr_slab_id_new,0) then
1838
1839 open c_tax_category_id(ln_thr_slab_id_new, ln_org_id);
1840 fetch c_tax_category_id into ln_tax_ctg_id;
1841 close c_tax_category_id;
1842
1843 UPDATE JAI_AP_TDS_DEFAULT
1844 SET TAX_CATEGORY_ID = ln_tax_ctg_id
1845 WHERE INVOICE_ID = p_inv_id
1846 AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
1847 AND TAX_CATEGORY_ID IS NULL;
1848
1849 end if;
1850
1851 -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012 end.
1852
1853 end loop; -- c_get_disnt_sec_code
1854
1855 end if; -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012.
1856
1857 end if; -- p_redefault_tax_category = 'Y'
1858
1859 end if; -- lv_tax_default_option = G_NTL_ACC_SEC_MAP
1860
1861 if p_redefault_tax_category = 'Y' then
1862
1863 for ap_tds_def_rec IN c_get_ap_tds_def
1864 loop
1865
1866 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
1867 ln_inclusive_tax_amount := 0;
1868 lv_tds_on_service_flg := null;
1869 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
1870
1871 ln_tax_ctg_id := null; -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012
1872
1873 if ap_tds_def_rec.LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS' then
1874
1875 open c_po_matched_st_line (ap_tds_def_rec.INVOICE_DISTRIBUTION_ID);
1876 fetch c_po_matched_st_line into lv_exist_flg;
1877 close c_po_matched_st_line;
1878
1879 -- service tax exists on po matched invoice
1880 if NVL(lv_exist_flg,'0') = '1' then
1881
1885
1882 open c_get_po_matched_dist_id (ap_tds_def_rec.INVOICE_DISTRIBUTION_ID);
1883 fetch c_get_po_matched_dist_id into ln_parent_dist_id;
1884 close c_get_po_matched_dist_id;
1886 else
1887
1888 open c_standalone_st_line (ap_tds_def_rec.INVOICE_LINE_NUMBER);
1889 fetch c_standalone_st_line into lv_exist_flg;
1890 close c_standalone_st_line;
1891
1892 -- service tax exists on standardalone invoice
1893 if NVL(lv_exist_flg, '0') = '1' then
1894 /*open c_get_standalone_dist_id;
1895 fetch c_get_standalone_dist_id into ln_parent_dist_id;
1896 close c_get_standalone_dist_id;*/
1897 --p_get_standalone_dist_id(p_inv_id, ln_parent_dist_id); commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
1898 p_get_standalone_dist_id(p_inv_id, ap_tds_def_rec.INVOICE_LINE_NUMBER, ln_parent_dist_id); -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
1899 end if;
1900
1901 end if;
1902
1903 if ln_parent_dist_id is not NULL then
1904
1905 -- commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
1906 -- get code combination id of parent distribution
1907 /*open c_get_dist_ccid(ln_parent_dist_id);
1908 fetch c_get_dist_ccid into ln_parent_dist_ccid;
1909 close c_get_dist_ccid; */
1910 -- commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
1911
1912 -- fetch distribution accid
1913 p_get_dist_ccid(p_inv_id, ln_parent_dist_id, ln_parent_dist_ccid); -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
1914
1915 -- check tds on service flg
1916 open c_get_tds_on_service(JAI_AP_UTILS_PKG.GET_SECTION_CODE(ln_parent_dist_ccid, lv_segment_name, ln_legal_entity_id));
1917 fetch c_get_tds_on_service into lv_tds_on_service_flg;
1918 close c_get_tds_on_service;
1919 if NVL(lv_tds_on_service_flg, 'N') = 'Y' then
1920
1921 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
1922 update JAI_AP_TDS_DEFAULT
1923 set TDS_SECTION_CODE = JAI_AP_UTILS_PKG.GET_SECTION_CODE(ln_parent_dist_ccid, lv_segment_name, ln_legal_entity_id)
1924 where INVOICE_ID = p_inv_id
1925 and INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID;
1926 ap_tds_def_rec.TDS_SECTION_CODE := JAI_AP_UTILS_PKG.GET_SECTION_CODE(ln_parent_dist_ccid, lv_segment_name, ln_legal_entity_id);
1927 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
1928
1929 -- get tax category id
1930 open c_get_tax_ctg_id(ln_parent_dist_id);
1931 fetch c_get_tax_ctg_id into ln_tax_ctg_id;
1932 close c_get_tax_ctg_id;
1933 end if;
1934
1935 end if;
1936
1937 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
1938
1939 elsif ap_tds_def_rec.LINE_TYPE_LOOKUP_CODE = 'ITEM' and ap_tds_def_rec.TDS_SECTION_CODE is not null then -- added TDS_SECTION_CODE IS NOT NULL condition by zhiwei.xin for bug 13731393 on 29-Feb-2012
1940
1941 open c_get_tds_on_service(ap_tds_def_rec.TDS_SECTION_CODE);
1942 fetch c_get_tds_on_service into lv_tds_on_service_flg;
1943 close c_get_tds_on_service;
1944
1945 if nvl(lv_tds_on_service_flg, 'N') = 'N' then
1946 if lv_inv_tlc = 'ITEM_TO_RECEIPT' OR lv_inv_tlc = 'ITEM_TO_PO' then
1947
1948 OPEN c_match_inclu_taxes (ap_tds_def_rec.INVOICE_LINE_NUMBER);
1949 FETCH c_match_inclu_taxes INTO ln_inclusive_tax_amount;
1950 CLOSE c_match_inclu_taxes;
1951 else
1952 OPEN c_std_inclu_taxes (ap_tds_def_rec.INVOICE_DISTRIBUTION_ID);
1953 FETCH c_std_inclu_taxes INTO ln_inclusive_tax_amount;
1954 CLOSE c_std_inclu_taxes;
1955 end if;
1956
1957 if nvl(ln_inclusive_tax_amount, 0) <= 0 then
1958 ln_inclusive_tax_amount := 0;
1959 end if;
1960
1961 end if;
1962
1966
1963 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
1964
1965 end if;
1967 if ap_tds_def_rec.TDS_SECTION_CODE is not null then -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
1968
1969 ln_line_no := 0;
1970
1971 for tax_ctg_lines_rec IN c_get_tax_ctg_lines(NVL(ln_tax_ctg_id, ap_tds_def_rec.TAX_CATEGORY_ID))
1972 loop
1973 ln_line_no := tax_ctg_lines_rec.LINE_NO;
1974 open c_get_tax_code_details(tax_ctg_lines_rec.TAX_ID);
1975 fetch c_get_tax_code_details into lr_tax_code_details;
1979 open c_check_rec_exists(ap_tds_def_rec.INVOICE_DISTRIBUTION_ID, tax_ctg_lines_rec.LINE_NO);
1976 close c_get_tax_code_details;
1977
1978 -- check whether the record already exists
1980 fetch c_check_rec_exists into lv_rec_exists;
1981 close c_check_rec_exists;
1982
1983 -- 'Y' : exist, 'N' : not exist
1984 if NVL(lv_rec_exists, 'N') = 'Y' then
1985
1986 UPDATE JAI_AP_TDS_INV_TAXES
1987 SET SECTION_TYPE = lr_tax_code_details.SECTION_TYPE,
1988 TAX_LINE_NO = tax_ctg_lines_rec.LINE_NO,
1989 TAX_TYPE = lr_tax_code_details.TAX_TYPE,
1990 TAX_CATEGORY_ID = tax_ctg_lines_rec.TAX_CATEGORY_ID,
1991 VENDOR_ID = ln_vendor_id,
1992 VENDOR_SITE_ID = ln_vendor_site_id,
1993 PRECEDENCE_1 = tax_ctg_lines_rec.PRECEDENCE_1,
1994 PRECEDENCE_2 = tax_ctg_lines_rec.PRECEDENCE_2,
1995 PRECEDENCE_3 = tax_ctg_lines_rec.PRECEDENCE_3,
1996 PRECEDENCE_4 = tax_ctg_lines_rec.PRECEDENCE_4,
1997 PRECEDENCE_5 = tax_ctg_lines_rec.PRECEDENCE_5,
1998 PRECEDENCE_6 = tax_ctg_lines_rec.PRECEDENCE_6,
1999 PRECEDENCE_7 = tax_ctg_lines_rec.PRECEDENCE_7,
2000 PRECEDENCE_8 = tax_ctg_lines_rec.PRECEDENCE_8,
2001 PRECEDENCE_9 = tax_ctg_lines_rec.PRECEDENCE_9,
2002 PRECEDENCE_10 = tax_ctg_lines_rec.PRECEDENCE_10,
2003 ACTUAL_SECTION_CODE = ap_tds_def_rec.TDS_SECTION_CODE,
2004 ACTUAL_TAX_ID = tax_ctg_lines_rec.TAX_ID
2005 WHERE INVOICE_ID = ap_tds_def_rec.INVOICE_ID
2006 AND INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID
2007 AND TAX_LINE_NO = tax_ctg_lines_rec.LINE_NO;
2008
2009 else
2010
2011 INSERT INTO JAI_AP_TDS_INV_TAXES
2012 (TDS_INV_TAX_ID,
2013 INVOICE_ID,
2014 INVOICE_DISTRIBUTION_ID,
2015 DISTRIBUTION_LINE_NUMBER,
2016 AMOUNT,
2017 VENDOR_ID,
2018 VENDOR_SITE_ID,
2019 DIST_CODE_COMBINATION_ID,
2020 SECTION_TYPE,
2021 TAX_LINE_NO,
2022 TAX_TYPE,
2023 TAX_CATEGORY_ID,
2024 PRECEDENCE_1,
2025 PRECEDENCE_2,
2026 PRECEDENCE_3,
2027 PRECEDENCE_4,
2028 PRECEDENCE_5,
2029 PRECEDENCE_6,
2030 PRECEDENCE_7,
2031 PRECEDENCE_8,
2032 PRECEDENCE_9,
2033 PRECEDENCE_10,
2034 ACTUAL_SECTION_CODE,
2035 ACTUAL_TAX_ID,
2036 PROCESS_STATUS,
2037 PROCESS_MESSAGE,
2038 MATCH_STATUS_FLAG,
2039 CREATED_BY,
2040 CREATION_DATE,
2041 LAST_UPDATED_BY,
2042 LAST_UPDATE_DATE,
2043 LAST_UPDATE_LOGIN,
2044 INVOICE_LINE_NUMBER)
2045 VALUES
2046 (JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
2047 ap_tds_def_rec.INVOICE_ID,
2048 ap_tds_def_rec.INVOICE_DISTRIBUTION_ID,
2049 ap_tds_def_rec.DISTRIBUTION_LINE_NUMBER,
2050 --commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
2051 --decode(tax_ctg_lines_rec.LINE_NO, 1, ap_tds_def_rec.AMOUNT, 0),
2052
2053 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
2054 decode(tax_ctg_lines_rec.LINE_NO, 1,
2055 decode(nvl(ln_inclusive_tax_amount,0),0,
2056 ap_tds_def_rec.AMOUNT,
2057 ap_tds_def_rec.AMOUNT - ln_inclusive_tax_amount),
2058 0),
2059 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
2060 ln_vendor_id,
2061 ln_vendor_site_id,
2062 ap_tds_def_rec.DIST_CODE_COMBINATION_ID,
2063 lr_tax_code_details.SECTION_TYPE,
2064 tax_ctg_lines_rec.LINE_NO,
2065 lr_tax_code_details.TAX_TYPE,
2066 tax_ctg_lines_rec.TAX_CATEGORY_ID,
2067 tax_ctg_lines_rec.PRECEDENCE_1,
2068 tax_ctg_lines_rec.PRECEDENCE_2,
2069 tax_ctg_lines_rec.PRECEDENCE_3,
2070 tax_ctg_lines_rec.PRECEDENCE_4,
2071 tax_ctg_lines_rec.PRECEDENCE_5,
2072 tax_ctg_lines_rec.PRECEDENCE_6,
2073 tax_ctg_lines_rec.PRECEDENCE_7,
2074 tax_ctg_lines_rec.PRECEDENCE_8,
2075 tax_ctg_lines_rec.PRECEDENCE_9,
2076 tax_ctg_lines_rec.PRECEDENCE_10,
2077 ap_tds_def_rec.TDS_SECTION_CODE,
2078 tax_ctg_lines_rec.TAX_ID,
2079 'D',
2080 NULL,
2081 ap_tds_def_rec.MATCH_STATUS_FLAG,
2082 FND_GLOBAL.USER_ID,
2083 SYSDATE,
2084 FND_GLOBAL.USER_ID,
2088
2085 SYSDATE,
2086 FND_GLOBAL.LOGIN_ID,
2087 ap_tds_def_rec.INVOICE_LINE_NUMBER);
2089 end if;
2090
2091 end loop; -- c_get_tax_ctg_lines
2092
2093 DELETE FROM JAI_AP_TDS_INV_TAXES
2094 WHERE INVOICE_ID = p_inv_id
2095 AND INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID
2096 --Commented out by zhiwei.xin for bug 13741305 on 22-Feb-2012
2097 --AND TAX_LINE_NO > ln_line_no;
2098 AND (TAX_LINE_NO > ln_line_no OR TAX_LINE_NO = 0); -- Added by zhiwei.xin for bug 13741305 on 22-Feb-2012
2099
2100 if ln_tax_ctg_id is NULL AND
2101 ap_tds_def_rec.TAX_CATEGORY_ID is NULL AND
2102 ap_tds_def_rec.TDS_SECTION_CODE is not NULL then
2103
2104 INSERT INTO JAI_AP_TDS_INV_TAXES
2105 (TDS_INV_TAX_ID,
2106 INVOICE_ID,
2107 INVOICE_DISTRIBUTION_ID,
2108 DISTRIBUTION_LINE_NUMBER,
2109 AMOUNT,
2110 VENDOR_ID,
2111 VENDOR_SITE_ID,
2112 DIST_CODE_COMBINATION_ID,
2113 SECTION_TYPE,
2114 TAX_LINE_NO,
2115 ACTUAL_SECTION_CODE,
2116 PROCESS_STATUS,
2117 PROCESS_MESSAGE,
2118 MATCH_STATUS_FLAG,
2119 CREATED_BY,
2120 CREATION_DATE,
2121 LAST_UPDATED_BY,
2122 LAST_UPDATE_DATE,
2123 LAST_UPDATE_LOGIN,
2124 INVOICE_LINE_NUMBER)
2125 VALUES
2126 (JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
2127 ap_tds_def_rec.INVOICE_ID,
2128 ap_tds_def_rec.INVOICE_DISTRIBUTION_ID,
2129 ap_tds_def_rec.DISTRIBUTION_LINE_NUMBER,
2130 ap_tds_def_rec.AMOUNT,
2131 ln_vendor_id,
2132 ln_vendor_site_id,
2133 ap_tds_def_rec.DIST_CODE_COMBINATION_ID,
2134 'TDS_SECTION',
2135 0,
2136 ap_tds_def_rec.TDS_SECTION_CODE,
2137 'D',
2138 NULL,
2139 ap_tds_def_rec.MATCH_STATUS_FLAG,
2140 FND_GLOBAL.USER_ID,
2141 SYSDATE,
2142 FND_GLOBAL.USER_ID,
2143 SYSDATE,
2144 FND_GLOBAL.LOGIN_ID,
2145 ap_tds_def_rec.INVOICE_LINE_NUMBER);
2146
2147 end if;
2148 end if; -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
2149 end loop;
2150 end if; -- p_redefault_tax_category = 'Y'
2151 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
2152 exception
2153 when others then
2154 p_process_flag := 'E';
2155 P_process_message := 'Error from default_tds_from_setup :' || sqlerrm;
2156 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.exception', P_process_message);
2157 return;
2158
2159 end default_tds_from_setup;
2160
2161 -- procedure for test by Chong
2162 procedure p_get_inv_dist (cp_inv_id IN number)
2163 is
2164 --PRAGMA AUTONOMOUS_TRANSACTION; --commented by Cholei for ZX integration bug#14040855
2165 -- cursor to get distribution details
2166 /*Commented by Chong for bug#16367707 Start
2167 cursor c_get_inv_dist
2168 is
2169 SELECT INVOICE_ID,
2170 INVOICE_LINE_NUMBER,
2171 DISTRIBUTION_LINE_NUMBER,
2172 INVOICE_DISTRIBUTION_ID,
2173 DIST_CODE_COMBINATION_ID,
2174 --AMOUNT, -- commented out by zhiwei.xin for bug 13736616 on 23-Feb-2012
2175 NVL(BASE_AMOUNT, AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
2176 DESCRIPTION,
2177 MATCH_STATUS_FLAG,
2178 LINE_TYPE_LOOKUP_CODE,
2179 ACCOUNTING_DATE
2180 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
2181 WHERE INVOICE_ID = cp_inv_id
2182 AND LINE_TYPE_LOOKUP_CODE NOT IN
2183 ('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS')
2184 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
2185 AND AMOUNT > 0
2186 AND NVL(REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
2187 AND NOT EXISTS (SELECT 1
2188 FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2189 WHERE
2190 JAMIT.source_doc_id = aida.invoice_id
2191 AND JAMIT.source_doc_line_id = aida.invoice_line_number
2192 AND JCT.TAX_ID = JAMIT.TAX_ID
2193 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2194 AND JCT.TAX_TYPE
2195 IN (SELECT ATTRIBUTE_CODE
2196 FROM JAI_RGM_REGISTRATIONS
2197 WHERE REGIME_ID = (SELECT REGIME_ID
2198 FROM JAI_RGM_DEFINITIONS
2199 WHERE REGIME_CODE ='SERVICE')
2200 AND REGISTRATION_TYPE = 'TAX_TYPES')
2201 )
2202 AND NOT EXISTS (SELECT 1
2203 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2204 WHERE JAMIT.INVOICE_DISTRIBUTION_ID = aida.INVOICE_DISTRIBUTION_ID
2205 AND JCT.TAX_ID = JAMIT.TAX_ID
2206 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2207 AND JCT.TAX_TYPE
2208 IN (SELECT ATTRIBUTE_CODE
2212 WHERE REGIME_CODE ='SERVICE')
2209 FROM JAI_RGM_REGISTRATIONS
2210 WHERE REGIME_ID = (SELECT REGIME_ID
2211 FROM JAI_RGM_DEFINITIONS
2213 AND REGISTRATION_TYPE = 'TAX_TYPES')
2214 )
2215 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
2216 AND NOT EXISTS (SELECT '1'
2217 FROM AP_INVOICES_ALL
2218 WHERE INVOICE_ID = cp_inv_id
2219 AND SOURCE = 'INDIA TDS')
2220 UNION
2221 SELECT INVOICE_ID,
2222 INVOICE_LINE_NUMBER,
2223 DISTRIBUTION_LINE_NUMBER,
2224 INVOICE_DISTRIBUTION_ID,
2225 DIST_CODE_COMBINATION_ID,
2226 --AMOUNT, -- commented out by zhiwei.xin for bug 13736616 on 23-Feb-2012
2227 NVL(BASE_AMOUNT, AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
2228 DESCRIPTION,
2229 MATCH_STATUS_FLAG,
2230 LINE_TYPE_LOOKUP_CODE,
2231 ACCOUNTING_DATE
2232 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
2233 WHERE INVOICE_ID = cp_inv_id
2234 AND LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
2235 AND NOT EXISTS
2236 (SELECT '1'
2237 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2238 WHERE JAMIT.INVOICE_DISTRIBUTION_ID =
2239 AIDA.INVOICE_DISTRIBUTION_ID
2240 AND JCT.TAX_ID = JAMIT.TAX_ID
2241 AND JCT.TAX_TYPE IN
2242 (JAI_CONSTANTS.TAX_TYPE_EXCISE,
2243 JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL,
2244 JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
2245 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS,
2246 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
2247 JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
2248 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
2252 AND NVL(REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
2249 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS))
2250 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
2251 AND AMOUNT > 0
2253 AND NOT EXISTS (SELECT 1
2254 FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2255 WHERE
2256 JAMIT.source_doc_id = AIDA.invoice_id
2257 AND JAMIT.source_doc_line_id = AIDA.invoice_line_number
2258 AND JCT.TAX_ID = JAMIT.TAX_ID
2259 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2260 AND JCT.TAX_TYPE
2261 IN (SELECT ATTRIBUTE_CODE
2262 FROM JAI_RGM_REGISTRATIONS
2263 WHERE REGIME_ID = (SELECT REGIME_ID
2264 FROM JAI_RGM_DEFINITIONS
2265 WHERE REGIME_CODE ='SERVICE')
2266 AND REGISTRATION_TYPE = 'TAX_TYPES')
2267 )
2268 AND NOT EXISTS (SELECT 1
2269 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2270 WHERE JAMIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
2271 AND JCT.TAX_ID = JAMIT.TAX_ID
2272 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2273 AND JCT.TAX_TYPE
2274 IN (SELECT ATTRIBUTE_CODE
2275 FROM JAI_RGM_REGISTRATIONS
2276 WHERE REGIME_ID = (SELECT REGIME_ID
2277 FROM JAI_RGM_DEFINITIONS
2278 WHERE REGIME_CODE ='SERVICE')
2279 AND REGISTRATION_TYPE = 'TAX_TYPES')
2280 )
2281 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
2282 AND NOT EXISTS (SELECT '1'
2283 FROM AP_INVOICES_ALL
2284 WHERE INVOICE_ID = cp_inv_id
2285 AND SOURCE = 'INDIA TDS')
2286 ORDER BY INVOICE_DISTRIBUTION_ID;
2287 Commented by Chong for bug#16367707 End*/
2288 --Added by Chong for bug#16367707 Start
2289 -------------------------------------------------------------------------
2290 --Rewrite cursor g_get_inv_dist, add left outer join to po_distributions_all table.
2291 --+ 1). Update SQL to add alias for each table.
2292 --+ 2). When PO/Receipt Match invoice, get PO charge account in priority.
2293 cursor c_get_inv_dist
2294 is
2295 SELECT AIDA.INVOICE_ID,
2296 AIDA.INVOICE_LINE_NUMBER,
2297 AIDA.DISTRIBUTION_LINE_NUMBER,
2298 AIDA.INVOICE_DISTRIBUTION_ID,
2299 --AIDA.DIST_CODE_COMBINATION_ID, --Commented out by Chong for bug#16367707
2300 NVL(pda.code_combination_id, aida.dist_code_combination_id) DIST_CODE_COMBINATION_ID, --Added by Chong for bug#16367707 get PO charge account in priority
2301 NVL(AIDA.BASE_AMOUNT, AIDA.AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
2302 AIDA.DESCRIPTION,
2303 AIDA.MATCH_STATUS_FLAG,
2304 AIDA.LINE_TYPE_LOOKUP_CODE,
2305 AIDA.ACCOUNTING_DATE
2306 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
2307 ,PO_DISTRIBUTIONS_ALL PDA
2308 WHERE AIDA.INVOICE_ID = cp_inv_id
2309 AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+) --Added by Chong for bug#16367707
2310 AND AIDA.LINE_TYPE_LOOKUP_CODE NOT IN
2311 ('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS') -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
2312 AND AIDA.AMOUNT > 0
2313 AND NVL(AIDA.REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
2314 AND NOT EXISTS (SELECT 1
2315 FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2316 WHERE
2317 JAMIT.source_doc_id = aida.invoice_id
2318 AND JAMIT.source_doc_line_id = aida.invoice_line_number
2319 AND JCT.TAX_ID = JAMIT.TAX_ID
2320 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2321 AND JCT.TAX_TYPE
2322 IN (SELECT ATTRIBUTE_CODE
2323 FROM JAI_RGM_REGISTRATIONS
2324 WHERE REGIME_ID = (SELECT REGIME_ID
2325 FROM JAI_RGM_DEFINITIONS
2326 WHERE REGIME_CODE ='SERVICE')
2327 AND REGISTRATION_TYPE = 'TAX_TYPES')
2328 )
2329 AND NOT EXISTS (SELECT 1
2330 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2331 WHERE JAMIT.INVOICE_DISTRIBUTION_ID = aida.INVOICE_DISTRIBUTION_ID
2332 AND JCT.TAX_ID = JAMIT.TAX_ID
2333 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2334 AND JCT.TAX_TYPE
2335 IN (SELECT ATTRIBUTE_CODE
2336 FROM JAI_RGM_REGISTRATIONS
2337 WHERE REGIME_ID = (SELECT REGIME_ID
2338 FROM JAI_RGM_DEFINITIONS
2339 WHERE REGIME_CODE ='SERVICE')
2340 AND REGISTRATION_TYPE = 'TAX_TYPES')
2341 )
2342 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
2343 AND NOT EXISTS (SELECT '1'
2344 FROM AP_INVOICES_ALL
2345 WHERE INVOICE_ID = cp_inv_id
2346 AND SOURCE = 'INDIA TDS')
2347 UNION
2348 SELECT AIDA.INVOICE_ID,
2349 AIDA.INVOICE_LINE_NUMBER,
2350 AIDA.DISTRIBUTION_LINE_NUMBER,
2351 AIDA.INVOICE_DISTRIBUTION_ID,
2357 AIDA.LINE_TYPE_LOOKUP_CODE,
2352 --AIDA.DIST_CODE_COMBINATION_ID, --Commented out by Chong for bug#16367707
2353 NVL(pda.code_combination_id, aida.dist_code_combination_id) DIST_CODE_COMBINATION_ID, --Added by Chong for bug#16367707 get PO charge account in priority
2354 NVL(AIDA.BASE_AMOUNT, AIDA.AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
2355 AIDA.DESCRIPTION,
2356 AIDA.MATCH_STATUS_FLAG,
2358 AIDA.ACCOUNTING_DATE
2359 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
2360 ,PO_DISTRIBUTIONS_ALL PDA
2361 WHERE AIDA.INVOICE_ID = cp_inv_id
2362 AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+) --Added by Chong for bug#16367707
2363 AND AIDA.LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
2364 AND NOT EXISTS
2365 (SELECT '1'
2366 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2367 WHERE JAMIT.INVOICE_DISTRIBUTION_ID =
2368 AIDA.INVOICE_DISTRIBUTION_ID
2369 AND JCT.TAX_ID = JAMIT.TAX_ID
2370 AND JCT.TAX_TYPE IN
2371 (JAI_CONSTANTS.TAX_TYPE_EXCISE,
2372 JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL,
2373 JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
2374 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS,
2375 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
2376 JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
2377 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
2378 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS))
2379 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
2380 AND AIDA.AMOUNT > 0
2381 AND NVL(AIDA.REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
2382 AND NOT EXISTS (SELECT 1
2383 FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2384 WHERE
2385 JAMIT.source_doc_id = AIDA.invoice_id
2386 AND JAMIT.source_doc_line_id = AIDA.invoice_line_number
2387 AND JCT.TAX_ID = JAMIT.TAX_ID
2388 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2389 AND JCT.TAX_TYPE
2390 IN (SELECT ATTRIBUTE_CODE
2391 FROM JAI_RGM_REGISTRATIONS
2392 WHERE REGIME_ID = (SELECT REGIME_ID
2393 FROM JAI_RGM_DEFINITIONS
2394 WHERE REGIME_CODE ='SERVICE')
2395 AND REGISTRATION_TYPE = 'TAX_TYPES')
2396 )
2397 AND NOT EXISTS (SELECT 1
2398 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
2399 WHERE JAMIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
2400 AND JCT.TAX_ID = JAMIT.TAX_ID
2401 AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
2402 AND JCT.TAX_TYPE
2403 IN (SELECT ATTRIBUTE_CODE
2404 FROM JAI_RGM_REGISTRATIONS
2405 WHERE REGIME_ID = (SELECT REGIME_ID
2406 FROM JAI_RGM_DEFINITIONS
2407 WHERE REGIME_CODE ='SERVICE')
2408 AND REGISTRATION_TYPE = 'TAX_TYPES')
2409 )
2410 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
2411 AND NOT EXISTS (SELECT '1'
2412 FROM AP_INVOICES_ALL
2413 WHERE INVOICE_ID = cp_inv_id
2414 AND SOURCE = 'INDIA TDS')
2415 ORDER BY INVOICE_DISTRIBUTION_ID;
2416 -------------------------------------------------------------------------
2417 --Added by Chong for bug#16367707 End
2418 begin
2419 begin
2420 open c_get_inv_dist;
2421 loop
2422 la_aida_tab.DELETE;
2423 fetch c_get_inv_dist BULK COLLECT into la_aida_tab LIMIT 1000;
2424 --for test temporary, no more distributon record than 1000 please
2425
2426 EXIT WHEN c_get_inv_dist%NOTFOUND;
2427 end loop;
2428
2429 close c_get_inv_dist;
2430
2431 EXCEPTION
2432 WHEN NO_DATA_FOUND THEN
2433 NULL;
2434 end;
2435 end p_get_inv_dist;
2436
2437 -- procedure for test
2438 procedure p_get_standalone_dist_id (cp_inv_id IN number, cp_inv_line_number IN number, cp_inv_dist_id OUT NOCOPY number)
2439 is
2440 -- PRAGMA AUTONOMOUS_TRANSACTION; --commented by Cholei for ZX integration bug#14040855
2441 begin
2442 begin
2443 SELECT aida.INVOICE_DISTRIBUTION_ID INTO cp_inv_dist_id
2444 FROM AP_INVOICE_DISTRIBUTIONS_ALL aida,
2445 JAI_CMN_DOCUMENT_TAXES jcdt
2446 WHERE jcdt.SOURCE_DOC_ID = cp_inv_id
2450 ORDER BY aida.INVOICE_DISTRIBUTION_ID;
2447 AND jcdt.SOURCE_DOC_LINE_ID = cp_inv_line_number -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
2448 AND jcdt.SOURCE_DOC_ID = aida.INVOICE_ID
2449 AND aida.INVOICE_LINE_NUMBER = jcdt.SOURCE_DOC_PARENT_LINE_NO
2451 EXCEPTION
2452 WHEN NO_DATA_FOUND THEN
2453 cp_inv_dist_id := null;
2454 end;
2455 end p_get_standalone_dist_id;
2456
2457 -- procedure for test
2458 procedure p_get_accounting_date (cp_inv_id IN number, cp_max_date OUT NOCOPY date, cp_min_date OUT NOCOPY date)
2459 is
2460 -- PRAGMA AUTONOMOUS_TRANSACTION; --commented by Cholei for ZX integration bug#14040855
2461 begin
2462 begin
2463 SELECT MAX(ACCOUNTING_DATE) MAX_DATE,
2464 MIN(ACCOUNTING_DATE) MIN_DATE
2465 INTO cp_max_date, cp_min_date
2466 FROM AP_INVOICE_DISTRIBUTIONS_ALL
2467 WHERE INVOICE_ID = cp_inv_id;
2468 EXCEPTION
2469 WHEN NO_DATA_FOUND THEN
2470 cp_max_date := null;
2471 cp_min_date := null;
2472 end;
2473 end p_get_accounting_date;
2474
2475 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
2476 -- procedure to get distribution ccid
2477 procedure p_get_dist_ccid (cp_inv_id IN number, cp_inv_dist_id IN number, cp_parent_dist_ccid OUT NOCOPY number)
2478 is
2479 -- PRAGMA AUTONOMOUS_TRANSACTION; --commented by Cholei for ZX integration bug#14040855
2480 begin
2481 begin
2482 SELECT DIST_CODE_COMBINATION_ID into cp_parent_dist_ccid
2483 FROM AP_INVOICE_DISTRIBUTIONS_ALL
2484 WHERE INVOICE_ID = cp_inv_id
2485 AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id;
2486 EXCEPTION
2487 WHEN NO_DATA_FOUND THEN
2488 cp_parent_dist_ccid := null;
2489 end;
2490 end p_get_dist_ccid;
2491 -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
2492
2493 end jai_ap_dtc_defaultation_pkg;
[Home] [Help]