[Home] [Help]
230: --and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section, section_code) /*Commented by mmurtuza for bug 16020276*/
231: /*bduvarag for Bug#5647725*/
232: and not exists (
233: select '1'
234: from JAI_AP_TDS_INV_PAYMENTS
235: where check_id = aca.check_id
236: and vendor_id = jattt.vendor_id /*Added by nprashar for bug # 6195566*/
237: and invoice_id = aipa.invoice_id /*Added by nprashar for bug # 6195566*/
238: and tds_tax_id in /*bduvarag for Bug#5647725*/
297: --Removed the union code by Bgowrava for Bug#6129650
298: )
299: and not exists (
300: select '1'
301: from JAI_AP_TDS_INV_PAYMENTS
302: where invoice_id = aia.invoice_id
303: )
304: and not exists (
305: SELECT 1
427: --Removed the union code by Bgowrava for Bug#6129650
428: )
429: and not exists (
430: select '1'
431: from JAI_AP_TDS_INV_PAYMENTS jatip
432: where jatip.invoice_id = aia.invoice_id
433: and jatip.prepay_invoice_id = aida_prepayment.invoice_id ) --Added by nprashar for Bug # 6774129
434: having sum(aida.amount) <> 0 -- Added by nprashar for Bug # 6774129
435: group by
477: and aipa.invoice_id = pn_invoice_id;
478:
479: cursor c_get_total_tax_basis ( cp_invoice_id number) is /*Added by nprashar for Bug # 6774129*/
480: select sum(nvl(taxable_basis,0))
481: from jai_ap_tds_inv_payments
482: where invoice_id = cp_invoice_id;
483:
484: /* START, Bgowrava for Bug#6069891*/
485:
484: /* START, Bgowrava for Bug#6069891*/
485:
486: CURSOR c_tds_multiple_payments IS
487: SELECT jatp.*
488: FROM jai_ap_tds_inv_payments jatp
489: WHERE (jatp.invoice_id , jatp.taxable_basis,
490: jatp.tax_amount, jatp.tds_tax_id ) IN
491: (SELECT invoice_id, taxable_basis, tax_amount , tds_tax_id
492: FROM jai_ap_tds_inv_payments
488: FROM jai_ap_tds_inv_payments jatp
489: WHERE (jatp.invoice_id , jatp.taxable_basis,
490: jatp.tax_amount, jatp.tds_tax_id ) IN
491: (SELECT invoice_id, taxable_basis, tax_amount , tds_tax_id
492: FROM jai_ap_tds_inv_payments
493: GROUP BY invoice_id, taxable_basis, tax_amount , tds_tax_id
494: having count(*) > 1
495: )
496: AND jatp.check_id NOT IN /* Filter out all voided and stop initiated checks*/
562: if pv_regenerate_flag = lv_pv_regenerate_flag then
563:
564: /* Flush the check records that have been processed earlier but are not paid */
565: Fnd_File.put_line(Fnd_File.LOG, ' Flushing the data as regenration option is set to Yes');
566: delete JAI_AP_TDS_INV_PAYMENTS
567: where check_id in
568: (
569: select
570: aca.check_id check_id
638: if r_ap_invoices_all.cancelled_date is not null then
639: lv_parent_invoice_cancel_flag := 'Y';
640: end if;
641:
642: insert into JAI_AP_TDS_INV_PAYMENTS
643: (
644: tds_payment_id ,
645: check_id ,
646: check_amount ,
674: request_id
675: )
676: values
677: (
678: jai_ap_tds_inv_payments_s.nextval ,
679: cur_rec.check_id ,
680: cur_rec.check_amount ,
681: cur_rec.check_date ,
682: cur_rec.invoice_payment_id ,
821: close c_ap_invoices_all;
822: -- ended, Harshita for Bug 4643633
823:
824: << populate_old_invoice_details >>
825: insert into JAI_AP_TDS_INV_PAYMENTS
826: (
827: tds_payment_id ,
828: check_id ,
829: check_amount ,
858: request_id
859: )
860: values
861: (
862: jai_ap_tds_inv_payments_s.nextval ,
863: cur_rec.check_id ,
864: cur_rec.check_amount ,
865: cur_rec.check_date ,
866: cur_rec.invoice_payment_id ,
1042: ln_tax_amount := cur_rec.prepaid_amount;
1043: ln_taxable_basis := ln_inv_tax_basis - ln_taxable_basis;
1044: -- End for bug 6774129
1045:
1046: insert into JAI_AP_TDS_INV_PAYMENTS
1047: (
1048: tds_payment_id ,
1049: check_id ,
1050: check_amount ,
1080: request_id
1081: )
1082: values
1083: (
1084: jai_ap_tds_inv_payments_s.nextval ,
1085: r_get_payment_details.check_id ,
1086: r_get_payment_details.check_amount ,
1087: r_get_payment_details.check_date ,
1088: r_get_payment_details.invoice_payment_id ,
1120: -- bug 6774129. Added by Lakshmi Gopalsami
1121: -- Observation as part of QA.
1122: -- this will update the tax amount with the payment amount for
1123: -- all lines which has been paid by check.
1124: update jai_ap_tds_inv_payments
1125: set tax_amount = payment_amount
1126: where invoice_id = cur_rec.invoice_id
1127: and prepay_invoice_id is null
1128: and nvl(source,'ABC') <> 'Invoice paid by prepayment';
1141: r_get_tds_inv_details(tab_index).taxable_basis := c_get_multiple_payments.taxable_basis;
1142: r_get_tds_inv_details(tab_index).invoice_id := c_get_multiple_payments.invoice_id;
1143: tab_index := tab_index + 1;
1144: END IF ;
1145: UPDATE jai_ap_tds_inv_payments jatp
1146: SET jatp.taxable_basis = round(jatp.taxable_basis * jatp.payment_amount / jatp.tax_amount,2)
1147: WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
1148: UPDATE jai_ap_tds_inv_payments jatp
1149: SET jatp.tax_amount = jatp.payment_amount
1144: END IF ;
1145: UPDATE jai_ap_tds_inv_payments jatp
1146: SET jatp.taxable_basis = round(jatp.taxable_basis * jatp.payment_amount / jatp.tax_amount,2)
1147: WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
1148: UPDATE jai_ap_tds_inv_payments jatp
1149: SET jatp.tax_amount = jatp.payment_amount
1150: WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
1151: END LOOP ;
1152:
1152:
1153: /* Round the taxable basis correct if not rounded properly. */
1154: FOR ind IN 1..tab_index - 1
1155: LOOP
1156: UPDATE jai_ap_tds_inv_payments jatp
1157: SET jatp.taxable_basis = jatp.taxable_basis +
1158: ( r_get_tds_inv_details(ind).taxable_basis -
1159: (SELECT sum(jatp1.taxable_basis)
1160: FROM jai_ap_tds_inv_payments jatp1
1156: UPDATE jai_ap_tds_inv_payments jatp
1157: SET jatp.taxable_basis = jatp.taxable_basis +
1158: ( r_get_tds_inv_details(ind).taxable_basis -
1159: (SELECT sum(jatp1.taxable_basis)
1160: FROM jai_ap_tds_inv_payments jatp1
1161: WHERE jatp1.invoice_id = r_get_tds_inv_details(ind).invoice_id
1162: AND jatp1.check_id NOT IN
1163: (SELECT check_id
1164: FROM ap_checks_all ac
1172: END LOOP ;
1173: /* END, Bgowrava for Bug#6069891*/
1174:
1175: <
1176: Fnd_File.put_line(Fnd_File.LOG, 'No of records inserted into JAI_AP_TDS_INV_PAYMENTS : ' || to_char(ln_record_count));
1177: Fnd_File.put_line(Fnd_File.LOG, '** Successful End of procedure jai_ap_tds_processing_pkg.process_tds_payments **');
1178:
1179: return;
1180:
1222: pn_vendor_site_id number
1223: )
1224: is
1225: select distinct operating_unit_id operating_unit_id
1226: from jai_ap_tds_inv_payments
1227: where parent_invoice_id is not null
1228: and tds_tax_id is not null
1229: and tds_tax_rate is not null
1230: and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1254: pn_operating_unit_id number
1255: )
1256: is
1257: select distinct invoice_date invoice_date
1258: from jai_ap_tds_inv_payments
1259: where parent_invoice_id is not null
1260: and tds_tax_id is not null
1261: and tds_tax_rate is not null
1262: and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1306: --tds_tax_id,
1307: tds_section,
1308: tax_authority_id,
1309: parent_invoice_id
1310: from jai_ap_tds_inv_payments /*Added by nprashar for bug 6774129*/
1311: where parent_invoice_id is not null
1312: and tds_tax_id is not null
1313: and tds_tax_rate is not null
1314: and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1356: vendor_site_id,
1357: /*tds_tax_id, commented by nprashar for Bug : 6774129*/
1358: tds_section,
1359: tax_authority_id
1360: from jai_ap_tds_inv_payments
1361: where parent_invoice_id is not null
1362: and tds_tax_id is not null
1363: and tds_tax_rate is not null
1364: and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1387:
1388:
1389: cursor c_form16_cert_lines(cp_form16_hdr_id number) is
1390: select rowid row_id, parent_invoice_id, threshold_trx_id
1391: from jai_ap_tds_inv_payments
1392: where form16_hdr_id = cp_form16_hdr_id
1393: order by parent_invoice_id, invoice_id
1394: for update of certificate_line_num;
1395:
1407:
1408: /*Bug 10621438*/
1409: CURSOR c_get_cert_date(cp_f16_hdr_id number) IS
1410: SELECT max(invoice_date)
1411: from jai_ap_tds_inv_payments
1412: where form16_hdr_id = cp_f16_hdr_id;
1413:
1414: /*11067621*/
1415: cursor c_get_fin_year(p_fin_year number)
1542: close c_ja_in_tds_year_info;
1543:
1544: Fnd_File.put_line(Fnd_File.LOG, 'Updating ' || r_ja_in_tds_year_info.fin_year);
1545:
1546: update jai_ap_tds_inv_payments
1547: set fin_year = r_ja_in_tds_year_info.fin_year
1548: where parent_invoice_id is not null
1549: and tds_tax_id is not null
1550: and tds_tax_rate is not null
1593: open c_get_form16_hdr_id;
1594: fetch c_get_form16_hdr_id into ln_form16_hdr_id;
1595: close c_get_form16_hdr_id;
1596:
1597: update jai_ap_tds_inv_payments /*changed by nprashar for bug 6774129 */
1598: set form16_hdr_id = -1 * ln_form16_hdr_id
1599: , last_update_date = sysdate
1600: , last_update_login = ln_last_update_login
1601: where parent_invoice_id = cur_rec.parent_invoice_id
1650: open c_get_form16_hdr_id;
1651: fetch c_get_form16_hdr_id into ln_form16_hdr_id;
1652: close c_get_form16_hdr_id;
1653:
1654: update jai_ap_tds_inv_payments
1655: set form16_hdr_id = ln_form16_hdr_id
1656: , certificate_num = ln_certificate_num
1657: , last_update_date = sysdate
1658: , last_update_login = ln_last_update_login
1814: -- lv_prev_tds_event := lv_tds_event;
1815: /*bduvarag for Bug#5647725*/
1816: end if;
1817:
1818: update jai_ap_tds_inv_payments
1819: set certificate_line_num = ln_cert_line_num
1820: where current of c_form16_cert_lines;
1821:
1822: Fnd_File.put_line(Fnd_File.LOG, 'Line number / No of records for the line :'