[Home] [Help]
115: is
116: /*Bug 5751783 - Start*/
117: cursor c_get_prepay_apply(cp_invoice_id number, cp_inv_dist_id number) is
118: select tds_threshold_trx_id_apply, count(1)
119: from jai_ap_tds_prepayments
120: where invoice_id = cp_invoice_id
121: and invoice_distribution_id_prepay = cp_inv_dist_id
122: group by tds_threshold_trx_id_apply;
123:
140: p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_prepayment', 'START'); /* 1 */
141: if p_prepay_amount < 0 then
142:
143: /* Event is APPLY of prepayment */
144: /* Comment out the jai_ap_tds_prepayments_pkg.allocate_prepayment with new procedure
145: jai_tds_prepayments_pkg.allocate_prepay_section. By Wenqiong for bug13359892 on Dec 26 2011.
146: jai_ap_tds_prepayments_pkg.allocate_prepayment
147: (
148: p_invoice_id => p_invoice_id ,
142:
143: /* Event is APPLY of prepayment */
144: /* Comment out the jai_ap_tds_prepayments_pkg.allocate_prepayment with new procedure
145: jai_tds_prepayments_pkg.allocate_prepay_section. By Wenqiong for bug13359892 on Dec 26 2011.
146: jai_ap_tds_prepayments_pkg.allocate_prepayment
147: (
148: p_invoice_id => p_invoice_id ,
149: p_invoice_distribution_id => p_invoice_distribution_id ,
150: p_prepay_amount => p_prepay_amount ,
156: if p_process_flag = 'E' then
157: goto exit_from_procedure;
158: end if;
159:
160: jai_ap_tds_prepayments_pkg.populate_section_tax
161: (
162: p_invoice_id => p_invoice_id ,
163: p_invoice_distribution_id => p_invoice_distribution_id ,
164: p_prepay_distribution_id => p_prepay_distribution_id ,
184: goto exit_from_procedure;
185: end if;
186:
187:
188: /* Comment out the jai_ap_tds_prepayments_pkg.process_tds_invoices with new procedure
189: jai_tds_prepayments_pkg.generate_rtn. By Wenqiong for bug13359892 on Dec 26 2011.
190:
191: jai_ap_tds_prepayments_pkg.process_tds_invoices
192: (
187:
188: /* Comment out the jai_ap_tds_prepayments_pkg.process_tds_invoices with new procedure
189: jai_tds_prepayments_pkg.generate_rtn. By Wenqiong for bug13359892 on Dec 26 2011.
190:
191: jai_ap_tds_prepayments_pkg.process_tds_invoices
192: (
193: p_event => p_event , --Added for Bug 8431516
194: p_invoice_id => p_invoice_id ,
195: p_invoice_distribution_id => p_invoice_distribution_id ,
348: sum( decode(wct_applicable_flag , 'Y', wct_application_amount, 0) ) wct_amount_orig,
349: sum( decode(essi_applicable_flag, 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) essi_taxable_basis,
350: sum( decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0) ) essi_amount,
351: sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount_orig
352: from jai_ap_tds_prepayments
353: where invoice_id = p_invoice_id
354: and invoice_distribution_id_prepay = p_invoice_distribution_id;
355:
356: /* bug 12965614. Added by Avanija
375: sum(decode(wct_applicable_flag , 'Y', wct_application_amount, 0)) wct_amount_orig,
376: sum(decode(essi_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) essi_taxable_basis,
377: sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount,
378: sum(decode(essi_applicable_flag , 'Y', essi_application_amount, 0)) essi_amount_orig
379: from jai_ap_tds_prepayments jatp
380: where invoice_id = p_invoice_id
381: and invoice_distribution_id_prepay = p_invoice_distribution_id
382: and tds_threshold_grp_id is not null
383: and nvl(unapply_flag, 'N') <> 'Y' -- Bug 6363056
392: tds_section_code_prepay section_code,
393: wct_threshold_trx_id_apply,
394: sum(decode(wct_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) wct_taxable_basis,
395: sum(decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0)) wct_amount
396: from jai_ap_tds_prepayments jatp
397: where invoice_id = p_invoice_id
398: and invoice_distribution_id_prepay = p_invoice_distribution_id
399: and wct_threshold_trx_id_apply is not NULL
400: and wct_threshold_trx_id_unapply IS NULL
408: tds_section_code_prepay section_code,
409: essi_threshold_trx_id_apply,
410: sum(decode(essi_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) essi_taxable_basis,
411: sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount
412: from jai_ap_tds_prepayments jatp
413: where invoice_id = p_invoice_id
414: and invoice_distribution_id_prepay = p_invoice_distribution_id
415: and essi_threshold_trx_id_apply is not NULL
416: and essi_threshold_trx_id_unapply IS NULL
425: where set_of_books_id = cp_set_of_books_id;
426:
427: cursor c_get_tds_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
428: select tds_tax_id_prepay
429: from jai_ap_tds_prepayments
430: where invoice_id = p_invoice_id
431: and invoice_distribution_id_prepay = p_prepay_distribution_id
432: and tds_tax_id_prepay is not null
433: and tds_applicable_flag = 'Y';
433: and tds_applicable_flag = 'Y';
434:
435: cursor c_get_wct_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
436: select wct_tax_id_prepay
437: from jai_ap_tds_prepayments
438: where invoice_id = p_invoice_id
439: and invoice_distribution_id_prepay = p_prepay_distribution_id
440: and wct_tax_id_prepay is not null
441: and wct_applicable_flag = 'Y';
441: and wct_applicable_flag = 'Y';
442:
443: cursor c_get_essi_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
444: select essi_tax_id_prepay
445: from jai_ap_tds_prepayments
446: where invoice_id = p_invoice_id
447: and invoice_distribution_id_prepay = p_prepay_distribution_id
448: and essi_tax_id_prepay is not null
449: and essi_applicable_flag = 'Y';
461: where threshold_trx_id = p_threshold_trx_id;
462: --Added by Wenqiong for bug13359892 begin
463: CURSOR c_get_tax_cat_section_code (p_invoice_distribution_id NUMBER) IS
464: SELECT tt.tax_category_id, tt.actual_section_code
465: FROM jai_ap_tds_prepayments tp, jai_ap_tds_inv_taxes tt
466: WHERE tp.invoice_distribution_id_prepay = p_invoice_distribution_id AND
467: tp.invoice_distribution_id = tt.invoice_distribution_id AND rownum = 1;
468: --Added by Wenqiong for bug13359892 end
469:
688: goto exit_from_procedure;
689: END IF;
690: END IF; /*if p_event = 'INSERT' then*/
691:
692: update jai_ap_tds_prepayments
693: set tds_threshold_trx_id_unapply = ln_threshold_trx_id_tds
694: where invoice_id = p_invoice_id
695: and invoice_distribution_id_prepay = p_parent_distribution_id
696: and tds_threshold_trx_id_apply is not null
702:
703: end if; /* r_tds_details_apply.tds_threshold_trx_id_apply is not null */
704:
705: /* update the unapply flag for invoice distribution */
706: update jai_ap_tds_prepayments
707: set unapply_flag = 'Y'
708: where invoice_id = p_invoice_id
709: and invoice_distribution_id_prepay = p_parent_distribution_id;
710:
869: if p_process_flag = 'E' then
870: goto exit_from_procedure;
871: end if;
872:
873: update jai_ap_tds_prepayments
874: set wct_threshold_trx_id_unapply = ln_threshold_trx_id_wct
875: where invoice_id = p_invoice_id
876: and invoice_distribution_id_prepay = p_parent_distribution_id
877: and wct_threshold_trx_id_apply is not null
961: if p_process_flag = 'E' then
962: goto exit_from_procedure;
963: end if;
964:
965: update jai_ap_tds_prepayments
966: set essi_threshold_trx_id_unapply = ln_threshold_trx_id_essi
967: where invoice_id = p_invoice_id
968: and invoice_distribution_id_prepay = p_parent_distribution_id
969: and essi_threshold_trx_id_apply is not null
982: end if;
983: /* Unapply ESSI */
984:
985: /* update the unapply flag for all */
986: update jai_ap_tds_prepayments
987: set unapply_flag = 'Y'
988: where invoice_id = p_invoice_id
989: and invoice_distribution_id_prepay = p_parent_distribution_id;
990:
1050: and amount > 0; --Added by bgowrava for bug#9214036
1051:
1052: cursor c_get_amount_already_applied(p_invoice_distribution_id number) is
1053: select sum(application_amount)
1054: from jai_ap_tds_prepayments
1055: where invoice_distribution_id = p_invoice_distribution_id
1056: and nvl(unapply_flag, 'N') <> 'Y';
1057:
1058: \*START, Added by bgowrava for bug#9214036*\
1110: ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);
1111:
1112: if ln_application_amount > 0 then
1113:
1114: \* Insert into jai_ap_tds_prepayments *\
1115: insert into jai_ap_tds_prepayments
1116: (
1117: tds_prepayment_id ,
1118: invoice_id ,
1111:
1112: if ln_application_amount > 0 then
1113:
1114: \* Insert into jai_ap_tds_prepayments *\
1115: insert into jai_ap_tds_prepayments
1116: (
1117: tds_prepayment_id ,
1118: invoice_id ,
1119: invoice_distribution_id_prepay ,
1126: last_update_login
1127: )
1128: values
1129: (
1130: jai_ap_tds_prepayments_s.nextval ,
1131: p_invoice_id ,
1132: p_invoice_distribution_id ,
1133: cur_si_distributions_rec.invoice_distribution_id ,
1134: ln_application_amount ,
1193: where invoice_id = p_invoice_id
1194: and invoice_distribution_id = p_invoice_distribution_id;
1195:
1196:
1197: cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1198: select tds_prepayment_id,
1199: invoice_distribution_id
1200: from jai_ap_tds_prepayments
1201: where invoice_id = p_invoice_id
1196:
1197: cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1198: select tds_prepayment_id,
1199: invoice_distribution_id
1200: from jai_ap_tds_prepayments
1201: where invoice_id = p_invoice_id
1202: and invoice_distribution_id_prepay = p_invoice_distribution_id;
1203:
1204:
1224:
1225: lv_applicable_flag varchar2(1);
1226: lv_is_si_validated_flag varchar2(1);
1227:
1228: lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1229: ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1230: ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1231: ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1225: lv_applicable_flag varchar2(1);
1226: lv_is_si_validated_flag varchar2(1);
1227:
1228: lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1229: ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1230: ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1231: ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233:
1226: lv_is_si_validated_flag varchar2(1);
1227:
1228: lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1229: ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1230: ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1231: ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233:
1234:
1227:
1228: lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1229: ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1230: ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1231: ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233:
1234:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1228: lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1229: ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1230: ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1231: ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233:
1234:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1231: ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233:
1234:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1232: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233:
1234:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240: ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1233:
1234:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240: ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1241: lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1234:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240: ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1241: lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1242:
1235: lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240: ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1241: lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1242:
1243: pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1236: ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240: ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1241: lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1242:
1243: pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1244:
1237: lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238: ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239: lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240: ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1241: lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1242:
1243: pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1244:
1245:
1304:
1305:
1306: \* Loop and get all the distribution is that has been been allocated for this prepayment and
1307: get the tax details that is applicable on the allocated line *\
1308: for cur_rec_pp_allocations in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id) loop
1309:
1310: for cur_rec in c_get_tax_details_si_inv_dist(p_invoice_id, cur_rec_pp_allocations.invoice_distribution_id) loop
1311: -- Bug 4754213. Added by Lakshmi Gopalsami
1312: if cur_rec.section_type = 'TDS_SECTION' then
1347:
1348: end loop; \* Cur rec *\
1349:
1350:
1351: \* Update jai_ap_tds_prepayments *\
1352: update jai_ap_tds_prepayments
1353: set application_basis = lv_application_basis ,
1354: tds_section_code_prepay = lv_tds_section_code_prepay ,
1355: tds_section_code_other = lv_tds_section_code_other ,
1348: end loop; \* Cur rec *\
1349:
1350:
1351: \* Update jai_ap_tds_prepayments *\
1352: update jai_ap_tds_prepayments
1353: set application_basis = lv_application_basis ,
1354: tds_section_code_prepay = lv_tds_section_code_prepay ,
1355: tds_section_code_other = lv_tds_section_code_other ,
1356: tds_tax_id_prepay = ln_tds_tax_id_prepay ,
1407: select currency_code
1408: from gl_sets_of_books
1409: where set_of_books_id = cp_set_of_books_id;
1410:
1411: cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1412: select tds_prepayment_id,
1413: application_amount,
1414: application_basis,
1415: \*
1429: *\
1430: tds_applicable_flag, tds_tax_id_other, tds_tax_id_prepay,
1431: wct_applicable_flag, wct_tax_id_other, wct_tax_id_prepay,
1432: essi_applicable_flag, essi_tax_id_other, essi_tax_id_prepay
1433: from jai_ap_tds_prepayments
1434: where invoice_id = p_invoice_id
1435: and invoice_distribution_id_prepay = p_invoice_distribution_id;
1436:
1437:
1478: sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
1479: sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
1480: sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
1481: sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
1482: from jai_ap_tds_prepayments
1483: where invoice_id = p_invoice_id
1484: and invoice_distribution_id_prepay = p_invoice_distribution_id;
1485:
1486: \* Bug 4522507. Added by Lakshmi Gopalsami *\
1494:
1495: \*Bug 6363056 - Replaced p_invoice_distribution_id with p_item_distribution_id*\
1496: cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_item_distribution_id number) is
1497: select sum(calc_tds_appln_amt) , sum(application_amount)
1498: from jai_ap_tds_prepayments jatp
1499: where invoice_id = p_invoice_id
1500: and invoice_distribution_id_prepay = p_invoice_distribution_id
1501: \*Bug 6363056. Added invoice_distribution_id condition also*\
1502: and invoice_distribution_id = p_item_distribution_id
1534:
1535: cursor c_get_tax_sec_det(p_invoice_id number, p_invoice_distribution_id number)
1536: is
1537: select tds_section_code_other, tds_tax_id_other, application_amount, invoice_distribution_id
1538: from jai_ap_tds_prepayments
1539: where invoice_id = p_invoice_id
1540: and invoice_distribution_id_prepay = p_invoice_distribution_id;
1541:
1542: ln_si_thgrp_id number;
1588: lb_result boolean;
1589: ln_req_id number;
1590: ln_pp_section_tax_id number;
1591: ln_threshold_grp_audit_id number;
1592: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1593: \* Bug 4522507. Added by Lakshmi Gopalsami *\
1594: ln_threshold_trx_id_prepay jai_ap_tds_inv_taxes.threshold_trx_id%type;
1595: ln_amt_tds_inv_generated_si number;
1596: --Added the below 6 variables by Sanjikum for Bug#5131075(4718907)
1615: ln_si_essi_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1616: ln_si_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1617: ln_pp_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1618: ln_parent_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1619: ln_tds_application_amt jai_ap_tds_prepayments.application_amount%TYPE ;
1620: r_pp_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
1621: r_si_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
1622: \*Bug 6363056 - End*\
1623: pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1650:
1651: ln_prepayment_amount := -1 * p_prepay_amount * ln_exchange_rate;
1652:
1653: \* update the tax amount for the prepayements *\
1654: for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
1655: loop
1656:
1657: if lv_application_basis is null then
1658: lv_application_basis := cur_rec.application_basis;
1692: END IF;
1693: -- End for bug 5722028
1694:
1695:
1696: update jai_ap_tds_prepayments
1697: set tds_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1698: \*Bug 5751783. Added the update for non-rounded value also*\
1699: calc_tds_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1700: where tds_prepayment_id = cur_rec.tds_prepayment_id;
1752: END IF;
1753: *\
1754: -- End for bug 5722028
1755:
1756: update jai_ap_tds_prepayments
1757: set wct_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1758: \*Bug 5751783. Added the update for non-rounded value also*\
1759: calc_wct_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1760: where tds_prepayment_id = cur_rec.tds_prepayment_id;
1810: END IF;
1811: *\
1812: -- End for bug 5722028
1813:
1814: update jai_ap_tds_prepayments
1815: set essi_application_amount = ln_tds_tmp_amt, --Bug 5722028
1816: \*Bug 5751783. Added the update for non-rounded value also*\
1817: calc_essi_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1818: where tds_prepayment_id = cur_rec.tds_prepayment_id;
1916: jatp.tds_prepayment_id tds_prepayment_id,
1917: jatp.application_amount tds_taxable_basis,
1918: jatp.invoice_distribution_id tax_dist
1919: FROM jai_ap_tds_thhold_trxs jattt,
1920: jai_ap_tds_prepayments jatp
1921: WHERE jattt.invoice_id = jatp.invoice_id
1922: AND jattt.tds_event = 'INVOICE VALIDATE'
1923: AND jatp.tds_applicable_flag ='Y'
1924: AND invoice_distribution_id_prepay = p_invoice_distribution_id
1986: END IF;
1987:
1988: -- Update each distribution with the threshold grp id as
1989: -- it may vary depending on the date and the group.
1990: update jai_ap_tds_prepayments
1991: set tds_threshold_grp_id = ln_threshold_grp_id
1992: where tds_prepayment_id = get_si_det.tds_prepayment_id; -- Bug 6363056
1993:
1994: \* TDS invoice was generated against the Prepayment,
2005: \*Bug 8431516 - Start*\
2006: ln_tot_tds_amt := ln_tot_tds_amt + ln_amt_tds_inv_generated_si;
2007: ln_tot_appln_amt := ln_tot_appln_amt + ln_tds_application_amt;
2008: if p_event = 'INSERT' then
2009: update jai_ap_tds_prepayments
2010: set tds_threshold_trx_id_apply = -999
2011: where tds_prepayment_id = get_si_det.tds_prepayment_id; --Bug 6031679
2012: end if;
2013: \*Bug 8431516 - End*\
2127: IF p_process_flag = 'E' THEN
2128: GOTO exit_from_procedure;
2129: END IF;
2130:
2131: \* Update jai_ap_tds_prepayments with threshold_trx_id_apply*\
2132: -- Update each distribution with the threshold grp id as
2133: -- it may vary depending on the date and the group.
2134: -- changed invoice_distribution_id_prepay to invoice_distribution_id.
2135: update jai_ap_tds_prepayments
2131: \* Update jai_ap_tds_prepayments with threshold_trx_id_apply*\
2132: -- Update each distribution with the threshold grp id as
2133: -- it may vary depending on the date and the group.
2134: -- changed invoice_distribution_id_prepay to invoice_distribution_id.
2135: update jai_ap_tds_prepayments
2136: set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
2137: where tds_threshold_trx_id_apply = -999
2138: and invoice_id = p_invoice_id
2139: and invoice_distribution_id_prepay = p_invoice_distribution_id; \*Bug 6363056*\
2221: if p_process_flag = 'E' then
2222: goto exit_from_procedure;
2223: end if;
2224:
2225: update jai_ap_tds_prepayments
2226: set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
2227: where invoice_id = p_invoice_id
2228: and invoice_distribution_id_prepay = p_invoice_distribution_id
2229: and wct_applicable_flag = 'Y';
2280: if p_process_flag = 'E' then
2281: goto exit_from_procedure;
2282: end if;
2283:
2284: update jai_ap_tds_prepayments
2285: set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
2286: where invoice_id = p_invoice_id
2287: and invoice_distribution_id_prepay = p_invoice_distribution_id
2288: and essi_applicable_flag = 'Y';
2596: | Type : PROCEDURE |
2597: | Purpose : Merge procedures allocate_prepayment and populate_section_tax into a single procedure |
2598: | TDD Reference : Section 16 |
2599: | Assumptions : |
2600: | Called From : jai_ap_tds_prepayments_pkg.process_prepayment |
2601: |--------------------------------------------------------------------------------------------------------------------------------|
2602: | parameters IN/OUT Type Required Description and Purpose |
2603: | ------------ -------- ------ ---------- ------------------------- |
2604: | p_invoice_id IN NUMBER yes invoice id |
2670: SECTION_TYPE = cn_section_type;
2671:
2672: CURSOR c_applied_amount(cn_invoice_distribution_id NUMBER) IS
2673: SELECT nvl(sum(application_amount),0)
2674: FROM JAI_AP_TDS_PREPAYMENTS
2675: WHERE invoice_distribution_id = cn_invoice_distribution_id
2676: AND nvl(unapply_flag, 'N') <> 'Y';
2677:
2678: cursor c_get_effective_available_amt(cn_invoice_id number, cn_invoice_line_num number) is
2789:
2790: jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Loop1 ln_application_amount:' || ln_application_amount);
2791:
2792: if ln_application_amount > 0 AND nvl(lv_tds_applicable,'N') = 'Y' then
2793: insert into jai_ap_tds_prepayments
2794: (
2795: tds_prepayment_id ,
2796: invoice_id ,
2797: invoice_distribution_id_prepay ,
2810: last_update_login
2811: )
2812: values
2813: (
2814: jai_ap_tds_prepayments_s.nextval ,
2815: p_invoice_id ,
2816: p_invoice_distribution_id ,
2817: cur_si_distributions_rec.invoice_distribution_id ,
2818: ln_application_amount ,
2884: END IF;
2885:
2886: jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Loop2 ln_application_amount:' || ln_application_amount);
2887: if ln_application_amount > 0 AND nvl(lv_tds_applicable,'N') = 'Y' then
2888: insert into jai_ap_tds_prepayments
2889: (
2890: tds_prepayment_id ,
2891: invoice_id ,
2892: invoice_distribution_id_prepay ,
2905: last_update_login
2906: )
2907: values
2908: (
2909: jai_ap_tds_prepayments_s.nextval ,
2910: p_invoice_id ,
2911: p_invoice_distribution_id ,
2912: cur_si_distributions_rec.invoice_distribution_id ,
2913: ln_application_amount ,
2950: | Type : PROCEDURE |
2951: | Purpose : Group by section code and generate RTN invoice |
2952: | TDD Reference : Section 16 |
2953: | Assumptions : |
2954: | Called From : jai_ap_tds_prepayments_pkg.process_prepayment |
2955: |--------------------------------------------------------------------------------------------------------------------------------|
2956: | parameters IN/OUT Type Required Description and Purpose |
2957: | ------------ -------- ------ ---------- ------------------------- |
2958: | p_event IN VARCHAR2 yes event
2992: select currency_code
2993: from gl_sets_of_books
2994: where set_of_books_id = cp_set_of_books_id;
2995:
2996: cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
2997: select tds_prepayment_id,
2998: invoice_distribution_id,
2999: application_amount,
3000: application_basis,
3002: tds_section_code_other,
3003: tds_applicable_flag,
3004: wct_applicable_flag,
3005: essi_applicable_flag
3006: from jai_ap_tds_prepayments
3007: where invoice_id = p_invoice_id
3008: and invoice_distribution_id_prepay = p_invoice_distribution_id;
3009:
3010: CURSOR c_jai_tds_tax(cn_invoice_distribution_id number, cv_section_code VARCHAR2, cv_section_type VARCHAR2) IS
3028: /*Update for adding exchange rate for bug13833254 */
3029: cursor c_get_tax_sec_det(p_invoice_id number, p_invoice_distribution_id number)
3030: is
3031: select tds_section_code_other, application_amount * nvl(p_exchange_rate,1) application_amount, invoice_distribution_id
3032: from jai_ap_tds_prepayments
3033: where invoice_id = p_invoice_id
3034: and invoice_distribution_id_prepay = p_invoice_distribution_id;
3035:
3036: cursor c_get_prepayment_thgroup(p_pre_pay_inv_id number, p_prepay_distribution_id number,cp_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
3049: sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
3050: sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
3051: sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
3052: sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
3053: from jai_ap_tds_prepayments
3054: where invoice_id = p_invoice_id
3055: and invoice_distribution_id_prepay = p_invoice_distribution_id;
3056:
3057: cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_item_distribution_id number) is
3055: and invoice_distribution_id_prepay = p_invoice_distribution_id;
3056:
3057: cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_item_distribution_id number) is
3058: select sum(calc_tds_appln_amt) , sum(application_amount)
3059: from jai_ap_tds_prepayments jatp
3060: where invoice_id = p_invoice_id
3061: and invoice_distribution_id_prepay = p_invoice_distribution_id
3062: and invoice_distribution_id = p_item_distribution_id
3063: and tds_applicable_flag = 'Y'
3069: );
3070:
3071: CURSOR c_get_section_code IS
3072: SELECT DISTINCT tt.actual_SECTION_CODE
3073: FROM JAI_AP_TDS_PREPAYMENTS tp, JAI_AP_TDS_INV_TAXES tt
3074: WHERE tp.invoice_distribution_id = tt.invoice_distribution_id
3075: AND tp.tds_section_code_other = tt.actual_section_code
3076: AND tp.invoice_distribution_id_prepay = p_invoice_distribution_id;
3077:
3088: select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
3089: -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3090: sum(tds_application_amount)* nvl(p_exchange_rate,1) tds_amount,
3091: sum(application_amount)* nvl(p_exchange_rate,1) amount
3092: from jai_ap_tds_prepayments jatp
3093: where jatp.invoice_id = p_invoice_id
3094: and jatp.tds_applicable_flag = 'Y'
3095: AND jatp.tds_section_code_other= cv_section_code
3096: AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
3108: select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
3109: -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3110: sum(wct_application_amount)* nvl(p_exchange_rate,1) wct_amount,
3111: sum(application_amount)* nvl(p_exchange_rate,1) amount
3112: from jai_ap_tds_prepayments jatp
3113: where jatp.invoice_id = p_invoice_id
3114: and jatp.wct_applicable_flag = 'Y'
3115: AND jatp.tds_section_code_other= cv_section_code
3116: AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
3127: select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
3128: -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3129: sum(essi_application_amount)* nvl(p_exchange_rate,1) essi_amount,
3130: sum(application_amount)* nvl(p_exchange_rate,1) amount
3131: from jai_ap_tds_prepayments jatp
3132: where jatp.invoice_id = p_invoice_id
3133: and jatp.essi_applicable_flag = 'Y'
3134: AND jatp.tds_section_code_other= cv_section_code
3135: AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
3205: ln_start_threshold_trx_id number;
3206: ln_prepayment_amount number;
3207: ln_pp_section_tax_id number;
3208: ln_threshold_grp_audit_id number;
3209: lv_application_basis jai_ap_tds_prepayments.application_basis%type;
3210: ln_threshold_trx_id_prepay jai_ap_tds_inv_taxes.threshold_trx_id%type;
3211: ln_amt_tds_inv_generated_si number;
3212: ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
3213: lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
3219:
3220: ln_tds_tmp_amt number;
3221: ln_parent_invoice_id NUMBER ;
3222: ln_pp_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
3223: ln_tds_application_amt jai_ap_tds_prepayments.application_amount%TYPE ;
3224: r_pp_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
3225: r_si_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
3226: pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE;
3227: ln_tot_tds_amt number := 0;
3260:
3261: ln_prepayment_amount := -1 * p_prepay_amount * ln_exchange_rate;
3262:
3263: /* update the tax amount for the prepayements */
3264: for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
3265: loop
3266:
3267: if lv_application_basis is null then
3268: lv_application_basis := cur_rec.application_basis;
3290:
3291: END IF;
3292: ln_tds_tmp_amt := cur_rec.application_amount * ln_tax_amount/ln_taxable_basis ;
3293:
3294: update jai_ap_tds_prepayments
3295: set tds_application_amount = ln_tds_tmp_amt,
3296: calc_tds_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
3297: where tds_prepayment_id = cur_rec.tds_prepayment_id;
3298:
3302: open c_get_prepayment_thgroup(pre_pay_inv_id,p_prepay_distribution_id,'TDS_SECTION');
3303: fetch c_get_prepayment_thgroup into ln_pp_thhold_grp_id, ln_prepay_tax_id, ln_threshold_trx_id_prepay;
3304: close c_get_prepayment_thgroup;
3305:
3306: update jai_ap_tds_prepayments
3307: set tds_threshold_grp_id = ln_pp_thhold_grp_id
3308: where tds_prepayment_id = cur_rec.tds_prepayment_id;
3309: -----------------------------------------------------------------------------------------------------
3310: --Added by Chong.Lei for bug#13802244 end
3330:
3331: END IF;
3332: ln_tds_tmp_amt := cur_rec.application_amount * ln_tax_amount/ln_taxable_basis ;
3333:
3334: update jai_ap_tds_prepayments
3335: set wct_application_amount = ln_tds_tmp_amt,
3336: calc_wct_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
3337: where tds_prepayment_id = cur_rec.tds_prepayment_id;
3338: END IF;
3356:
3357: END IF;
3358: ln_tds_tmp_amt := cur_rec.application_amount * ln_tax_amount/ln_taxable_basis ;
3359:
3360: update jai_ap_tds_prepayments
3361: set essi_application_amount = ln_tds_tmp_amt,
3362: calc_essi_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
3363: where tds_prepayment_id = cur_rec.tds_prepayment_id;
3364:
3481: jatp.tds_prepayment_id tds_prepayment_id,
3482: jatp.application_amount * nvl(p_exchange_rate,1) tds_taxable_basis,
3483: jatp.invoice_distribution_id tax_dist
3484: FROM jai_ap_tds_thhold_trxs jattt,
3485: jai_ap_tds_prepayments jatp
3486: WHERE jattt.invoice_id = jatp.invoice_id
3487: AND (jattt.tds_event = 'INVOICE VALIDATE'
3488: OR jattt.tds_event LIKE 'THRESHOLD TRANSITION(%' --Added threshold transition by Chong for issue120920-66 2012/10/09
3489: )
3561: */
3562:
3563: -- Update each distribution with the threshold grp id as
3564: -- it may vary depending on the date and the group.
3565: update jai_ap_tds_prepayments
3566: set tds_threshold_grp_id = ln_threshold_grp_id
3567: where tds_prepayment_id = get_si_det.tds_prepayment_id;
3568:
3569: /* TDS invoice was generated against the Prepayment,
3579:
3580: ln_tot_tds_amt := ln_tot_tds_amt + ln_amt_tds_inv_generated_si;
3581: ln_tot_appln_amt := ln_tot_appln_amt + ln_tds_application_amt;
3582: if p_event = 'INSERT' then
3583: update jai_ap_tds_prepayments
3584: set tds_threshold_trx_id_apply = -999
3585: where tds_prepayment_id = get_si_det.tds_prepayment_id;
3586: end if;
3587:
3719: IF p_process_flag = 'E' THEN
3720: GOTO exit_from_procedure;
3721: END IF;
3722:
3723: /* Update jai_ap_tds_prepayments with threshold_trx_id_apply*/
3724: -- Update each distribution with the threshold grp id as
3725: -- it may vary depending on the date and the group.
3726: -- changed invoice_distribution_id_prepay to invoice_distribution_id.
3727: IF lv_application_basis = 'STANDARD' THEN
3724: -- Update each distribution with the threshold grp id as
3725: -- it may vary depending on the date and the group.
3726: -- changed invoice_distribution_id_prepay to invoice_distribution_id.
3727: IF lv_application_basis = 'STANDARD' THEN
3728: update jai_ap_tds_prepayments tds_prepay
3729: set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
3730: where tds_threshold_trx_id_apply = -999
3731: and invoice_id = p_invoice_id
3732: and invoice_distribution_id_prepay = p_invoice_distribution_id
3735: tds_tax.actual_section_code = tds_prepay.tds_section_code_prepay --Added by Chong for issue120920-66 2012/10/09
3736: AND tds_tax.invoice_id = p_invoice_id
3737: AND tds_tax.tax_category_id = ln_tax_category_id);
3738: ELSE
3739: update jai_ap_tds_prepayments tds_prepay
3740: set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
3741: where tds_threshold_trx_id_apply = -999
3742: and invoice_id = p_invoice_id
3743: and invoice_distribution_id_prepay = p_invoice_distribution_id;
3911: goto exit_from_procedure;
3912: end if;
3913:
3914: IF lv_application_basis = 'STANDARD' THEN
3915: update jai_ap_tds_prepayments tds_prepay
3916: set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
3917: where invoice_id = p_invoice_id
3918: and invoice_distribution_id_prepay = p_invoice_distribution_id
3919: and wct_applicable_flag = 'Y'
3921: tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id
3922: AND tds_tax.invoice_id = p_invoice_id
3923: AND tds_tax.tax_category_id = ln_tax_category_id);
3924: ELSE
3925: update jai_ap_tds_prepayments tds_prepay
3926: set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
3927: where invoice_id = p_invoice_id
3928: and invoice_distribution_id_prepay = p_invoice_distribution_id
3929: and wct_applicable_flag = 'Y';
3992: goto exit_from_procedure;
3993: end if;
3994: IF lv_application_basis = 'STANDARD' THEN
3995:
3996: update jai_ap_tds_prepayments tds_prepay
3997: set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
3998: where invoice_id = p_invoice_id
3999: and invoice_distribution_id_prepay = p_invoice_distribution_id
4000: and essi_applicable_flag = 'Y'
4002: tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id
4003: AND tds_tax.invoice_id = p_invoice_id
4004: AND tds_tax.tax_category_id = ln_tax_category_id);
4005: ELSE
4006: update jai_ap_tds_prepayments tds_prepay
4007: set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
4008: where invoice_id = p_invoice_id
4009: and invoice_distribution_id_prepay = p_invoice_distribution_id
4010: and essi_applicable_flag = 'Y';