[Home] [Help]
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
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
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
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)
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
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
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
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
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
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
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
2250: -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
2251: AND AMOUNT > 0
2252: AND NVL(REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
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
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
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
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
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
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
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