[Home] [Help]
88: where invoice_distribution_id = p_prepay_dist_id;
89:
90: cursor c_prepay_tds_cal(p_invoice_id number) is --Xiao for Bug#6767347
91: select 1 from
92: jai_ap_tds_thhold_trxs where
93: invoice_id = p_invoice_id;
94:
95: --Addec by Jia for FP Bug#7431371, Begin
96: -------------------------------------------------------------------------------
429: AND INVOICE_ID = cn_invoice_id
430: )
431: AND NOT EXISTS (
432: SELECT '1'
433: FROM JAI_AP_TDS_THHOLD_TRXS JATTT
434: WHERE JATTT.INVOICE_ID = INVOICE_ID
435: )
436: );
437: begin
506: AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
507: AND JATIT.THRESHOLD_GRP_ID = $$P_THRESHOLD_GRP_ID$$
508: AND (
509: NOT EXISTS (SELECT 1
510: FROM JAI_AP_TDS_THHOLD_TRXS
511: WHERE INVOICE_ID = JATIT.INVOICE_ID
512: AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
513: )
514: OR
513: )
514: OR
515: EXISTS (
516: SELECT 1
517: FROM JAI_AP_TDS_THHOLD_TRXS TRX1
518: WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
519: AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
520: AND TRX1.TDS_ROLLBACKED = ''Y''
521: AND TRX1.THRESHOLD_TRX_ID IN
520: AND TRX1.TDS_ROLLBACKED = ''Y''
521: AND TRX1.THRESHOLD_TRX_ID IN
522: (
523: SELECT MAX(THRESHOLD_TRX_ID)
524: FROM JAI_AP_TDS_THHOLD_TRXS TRX2
525: WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
526: AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
527: )
528: )
542: AND
543: (
544: NOT EXISTS (
545: SELECT ''1''
546: FROM JAI_AP_TDS_THHOLD_TRXS JAITTT
547: WHERE JAITTT.INVOICE_ID = DIST.INVOICE_ID
548: )
549: or
550: exists
549: or
550: exists
551: (
552: SELECT 1
553: FROM JAI_AP_TDS_THHOLD_TRXS TRX3
554: WHERE TRX3.INVOICE_ID = DIST.INVOICE_ID
555: AND TRX3.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
556: AND TRX3.TDS_ROLLBACKED = ''Y''
557: AND TRX3.THRESHOLD_TRX_ID IN
556: AND TRX3.TDS_ROLLBACKED = ''Y''
557: AND TRX3.THRESHOLD_TRX_ID IN
558: (
559: SELECT MAX(THRESHOLD_TRX_ID)
560: FROM JAI_AP_TDS_THHOLD_TRXS TRX4
561: WHERE TRX4.INVOICE_ID = DIST.INVOICE_ID
562: AND TRX4.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
563: )
564: )
647: AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
648: AND JATIT.THRESHOLD_GRP_ID = $$P_THRESHOLD_GRP_ID$$
649: AND JATIT.INVOICE_DISTRIBUTION_ID = JATP.INVOICE_DISTRIBUTION_ID (+)
650: AND (NOT EXISTS ( SELECT 1
651: FROM JAI_AP_TDS_THHOLD_TRXS
652: WHERE INVOICE_ID = JATIT.INVOICE_ID
653: AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
654: )
655: OR
655: OR
656: EXISTS
657: (
658: SELECT 1
659: FROM JAI_AP_TDS_THHOLD_TRXS TRX1
660: WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
661: AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
662: AND TRX1.TDS_ROLLBACKED = ''Y''
663: AND TRX1.THRESHOLD_TRX_ID IN
662: AND TRX1.TDS_ROLLBACKED = ''Y''
663: AND TRX1.THRESHOLD_TRX_ID IN
664: (
665: SELECT MAX(THRESHOLD_TRX_ID)
666: FROM JAI_AP_TDS_THHOLD_TRXS TRX2
667: WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
668: AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
669:
670: )
680: AND INVOICE_ID = JATIT.INVOICE_ID
681: )
682: AND EXISTS (
683: SELECT ''1''
684: FROM JAI_AP_TDS_THHOLD_TRXS JAITTT
685: WHERE JAITTT.INVOICE_ID = DIST.INVOICE_ID)
686: )
687: AND NOT EXISTS(
688: SELECT 1
777: FROM AP_INVOICES_ALL
778: WHERE INVOICE_ID = AIDA.INVOICE_ID
779: AND INVOICE_TYPE_LOOKUP_CODE = ''PREPAYMENT'')
780: AND (NOT EXISTS (SELECT 1
781: FROM JAI_AP_TDS_THHOLD_TRXS
782: WHERE INVOICE_ID = JATIT.INVOICE_ID
783: AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
784: )
785: OR
785: OR
786: EXISTS
787: (
788: SELECT 1
789: FROM JAI_AP_TDS_THHOLD_TRXS TRX1
790: WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
791: AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
792: AND TRX1.TDS_ROLLBACKED = ''Y''
793: AND TRX1.THRESHOLD_TRX_ID IN
792: AND TRX1.TDS_ROLLBACKED = ''Y''
793: AND TRX1.THRESHOLD_TRX_ID IN
794: (
795: SELECT MAX(THRESHOLD_TRX_ID)
796: FROM JAI_AP_TDS_THHOLD_TRXS TRX2
797: WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
798: AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
799:
800: )
819: FROM AP_INVOICES_ALL
820: WHERE INVOICE_ID = JATIT.INVOICE_ID
821: AND INVOICE_TYPE_LOOKUP_CODE = ''PREPAYMENT'')
822: AND (NOT EXISTS (SELECT 1
823: FROM JAI_AP_TDS_THHOLD_TRXS
824: WHERE INVOICE_ID = JATIT.INVOICE_ID
825: AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
826: )
827: OR
827: OR
828: EXISTS
829: (
830: SELECT 1
831: FROM JAI_AP_TDS_THHOLD_TRXS TRX1
832: WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
833: AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
834: AND TRX1.TDS_ROLLBACKED = ''Y''
835: AND TRX1.THRESHOLD_TRX_ID IN
834: AND TRX1.TDS_ROLLBACKED = ''Y''
835: AND TRX1.THRESHOLD_TRX_ID IN
836: (
837: SELECT MAX(THRESHOLD_TRX_ID)
838: FROM JAI_AP_TDS_THHOLD_TRXS TRX2
839: WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
840: AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
841:
842: )
981: where invoice_distribution_id = p_prepay_dist_id;
982:
983: cursor c_prepay_tds_cal(p_invoice_id number) is --Xiao for Bug#6767347
984: select 1 from
985: jai_ap_tds_thhold_trxs where
986: invoice_id = p_invoice_id;
987:
988: --Addec by Jia for FP Bug#7431371, Begin
989: -------------------------------------------------------------------------------
2774: lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
2775: lv_confirm_pan_flag JAI_AP_TDS_VENDOR_HDRS.confirm_pan_flag%type; /*Bug 11896260*/
2776: ln_exchange_rate number;
2777: ln_fin_year JAI_AP_TDS_YEARS.fin_year%type;
2778: lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
2779:
2780: ln_no_of_tds_inv_generated number := 0;
2781: lb_result boolean;
2782: ln_req_id number;
4784: if( lv_prospective_flag = 'N' ) then --Added by Zhiwei
4785:
4786: --Added by Zhiwei for Bug#13359892 on 20111222 begin
4787: --------------------------------------------------------
4788: UPDATE JAI_AP_TDS_THHOLD_TRXS
4789: SET ROLLBACK_REVERSED ='Y'
4790: WHERE THRESHOLD_GRP_ID = ln_threshold_grp_id
4791: AND TDS_EVENT LIKE 'THRESHOLD ROLLBACK%';
4792: --------------------------------------------------------
5219: /*Commented by Chong for bug#13359892 20120927 start
5220: cursor get_sum_amount_trx
5221: is
5222: select sum(tax_amount)
5223: from JAI_AP_TDS_THHOLD_TRXS
5224: where threshold_grp_id = p_threshold_grp_id
5225: --Updated by Zhiwei Hou for Bug#13787158 on 20120305 begin
5226: ------------------------------------------------------------
5227: AND ( p_invoice_id is null
5237: --from ther transactions which in the salb with no surcharge tax code.
5238: CURSOR get_sum_amount_trx
5239: IS
5240: SELECT SUM(decode(jattt.TDS_EVENT ,'PREPAYMENT APPLICATION', -jattt.tax_amount, jattt.tax_amount))
5241: FROM JAI_AP_TDS_THHOLD_TRXS jattt
5242: WHERE jattt.threshold_grp_id = p_threshold_grp_id
5243: AND TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK,G_SURCHARGE_CALCULATE)
5244: AND jattt.tds_rollbacked IS NULL
5245: /*Commented out by Chong for bug#15976304 20121212 Start
5780: P_process_message => P_process_message,
5781: p_codepath => lv_codepath
5782: );
5783:
5784: UPDATE JAI_AP_TDS_THHOLD_TRXS
5785: SET ROLLBACK_REVERSED = 'Y'
5786: WHERE TDS_EVENT LIKE G_SURCHARGE_ROLLBACK
5787: AND ROLLBACK_REVERSED IS NULL
5788: AND THRESHOLD_GRP_ID = p_threshold_grp_id;
5910: from gl_sets_of_books
5911: where set_of_books_id = cp_set_of_books_id;
5912:
5913: cursor c_get_ja_in_ap_inv_id is
5914: select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
5915: from dual;
5916:
5917: cursor c_ap_payment_schedules_all(p_invoice_id number) is
5918: select payment_priority
6508: close c_ap_payment_schedules_all;
6509:
6510: /* Bug 5751783
6511: * Moved the assignment of ln_invoice_amount outside IF as this is used
6512: * in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
6513: * of the tds event.
6514: */
6515:
6516: Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
6649: END IF;
6650: -- End for bug 5722028.
6651:
6652:
6653: insert into jai_ap_tds_thhold_trxs
6654: (
6655: threshold_trx_id ,
6656: invoice_id ,
6657: invoice_line_number ,
6687: ,tds_section_code --Added by Chong for bug#16414088 20130313
6688: )
6689: values
6690: (
6691: jai_ap_tds_thhold_trxs_s.nextval ,
6692: pn_invoice_id ,
6693: pn_invoice_line_number ,
6694: pn_invoice_distribution_id ,
6695: pn_threshold_grp_id ,
6829: from gl_sets_of_books
6830: where set_of_books_id = cp_set_of_books_id;
6831:
6832: cursor c_get_ja_in_ap_inv_id is
6833: select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
6834: from dual;
6835:
6836: cursor c_ap_payment_schedules_all(p_invoice_id number) is
6837: select payment_priority
7674: close c_ap_payment_schedules_all;
7675:
7676: /* Bug 5751783
7677: * Moved the assignment of ln_invoice_amount outside IF as this is used
7678: * in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
7679: * of the tds event.
7680: */
7681:
7682: --Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
7852: -- End for bug 5722028.
7853:
7854:
7855:
7856: insert into jai_ap_tds_thhold_trxs
7857: (
7858: threshold_trx_id ,
7859: invoice_id ,
7860: invoice_line_number ,
7891: ,TDS_SECTION_CODE --Add by zhiwei hou on 20120111
7892: )
7893: values
7894: (
7895: jai_ap_tds_thhold_trxs_s.nextval ,
7896: pn_invoice_id ,
7897: pn_invoice_line_number ,
7898: pn_invoice_distribution_id ,
7899: pn_threshold_grp_id ,
8197: p_start_thhold_trx_id in number
8198: )
8199: is
8200:
8201: cursor c_jai_ap_tds_thhold_trxs
8202: (p_invoice_id number, p_start_thhold_trx_id number, p_vendor_id number, p_vendor_site_id number) is
8203: select invoice_to_tds_authority_id,
8204: invoice_to_vendor_id,
8205: invoice_to_tds_authority_num,
8203: select invoice_to_tds_authority_id,
8204: invoice_to_vendor_id,
8205: invoice_to_tds_authority_num,
8206: invoice_to_vendor_num
8207: from jai_ap_tds_thhold_trxs
8208: where threshold_trx_id >= p_start_thhold_trx_id
8209: and invoice_id = p_invoice_id
8210: and vendor_id = p_vendor_id
8211: and vendor_site_id = p_vendor_site_id;
8280: Fnd_File.put_line(Fnd_File.LOG, 'Before Loop ');
8281:
8282: /* Get all the tds invoices that have been created and call the base API to approve it */
8283: for cur_rec in
8284: c_jai_ap_tds_thhold_trxs(p_invoice_id , p_start_thhold_trx_id , p_vendor_id , p_vendor_site_id)
8285: loop
8286:
8287:
8288: /* Get the status of both the invoices and call approval API, if it is not already approved */
8490: ,threshold_grp_id
8491: ,threshold_hdr_id
8492: --------------------------------------------------
8493: --Added by Chong for bug#16414088 eTDS ER End
8494: from jai_ap_tds_thhold_trxs
8495: where invoice_to_tds_authority_num = p_invoice_num
8496: and tds_authority_vendor_id = p_vendor_id
8497: and tds_authority_vendor_site_id = p_vendor_site_id
8498: and invoice_to_tds_authority_id is null;
8499:
8500:
8501: cursor c_check_inv_to_vendor (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
8502: select threshold_trx_id
8503: from jai_ap_tds_thhold_trxs
8504: where invoice_to_vendor_num = p_invoice_num
8505: and vendor_id = p_vendor_id
8506: and vendor_site_id = p_vendor_site_id
8507: and invoice_to_vendor_id is null;
8506: and vendor_site_id = p_vendor_site_id
8507: and invoice_to_vendor_id is null;
8508:
8509: r_check_inv_to_tds c_check_inv_to_tds_authority%rowtype; --Added by Chong for bug#16414088 eTDS ER
8510: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%type;
8511: ln_invoice_id ap_invoices_all.invoice_id%type;
8512: l_api_name CONSTANT VARCHAR2(50) := 'populate_tds_invoice_id()';
8513:
8514: begin
8522: if r_check_inv_to_tds.threshold_trx_id is not null then --Added by Chong for bug#16414088 eTDS ER
8523:
8524: /* Invoice being created is the invoice to TDS authority */
8525:
8526: update jai_ap_tds_thhold_trxs
8527: set invoice_to_tds_authority_id = p_invoice_id
8528: where threshold_trx_id = r_check_inv_to_tds.threshold_trx_id --ln_threshold_trx_id --Updated by Chong for bug#16414088 eTDS ER
8529: ;
8530:
8566:
8567: if ln_threshold_trx_id is not null then
8568:
8569: /* Invoice being created is teh invoice to TDS authority */
8570: update jai_ap_tds_thhold_trxs
8571: set invoice_to_vendor_id = p_invoice_id
8572: where threshold_trx_id = ln_threshold_trx_id;
8573:
8574: end if; /* TDS invoice to vendor */
8620: begin
8621: jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
8622: fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
8623:
8624: insert into jai_ap_tds_thhold_trxs
8625: (
8626: threshold_trx_id ,
8627: invoice_id ,
8628: tds_event ,
8653: last_update_login
8654: )
8655: values
8656: (
8657: jai_ap_tds_thhold_trxs_s.nextval ,
8658: p_invoice_id ,
8659: p_tds_event ,
8660: p_tax_id ,
8661: p_tax_rate ,
9326: from jai_ap_tds_thhold_grps
9327: where threshold_grp_id = cn_threshold_grp_id;
9328: CURSOR c_get_tax_amount(cv_event VARCHAR2, cn_threshold_grp_id NUMBER) IS
9329: SELECT SUM(TAXABLE_AMOUNT), SUM(TAX_AMOUNT)
9330: FROM JAI_AP_TDS_THHOLD_TRXS
9331: WHERE TDS_EVENT LIKE cv_event
9332: AND TDS_ROLLBACKED IS NULL
9333: AND THRESHOLD_GRP_ID = cn_threshold_grp_id;
9334:
9337: ,SUM(tax_amount)
9338: FROM
9339: (SELECT jattt.TAXABLE_AMOUNT taxable_amount
9340: ,jattt.TAX_AMOUNT tax_amount
9341: FROM JAI_AP_TDS_THHOLD_TRXS jattt
9342: WHERE jattt.TDS_ROLLBACKED IS NULL
9343: AND jattt.TDS_EVENT NOT LIKE 'PREPAYMENT APPLICATION'
9344: AND jattt.TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK) --Added by Chong.Lei for bug#13359892 at 20121008
9345: AND jattt.THRESHOLD_GRP_ID = cn_threshold_grp_id
9351: )
9352: UNION ALL --Added 'ALL' by Chong.Lei for bug#13802244 at 20120308
9353: SELECT -SUM(TAXABLE_AMOUNT) taxable_amount,
9354: -SUM(TAX_AMOUNT) tax_amount
9355: FROM JAI_AP_TDS_THHOLD_TRXS
9356: WHERE TDS_ROLLBACKED IS NULL
9357: AND TDS_EVENT LIKE 'PREPAYMENT APPLICATION'
9358: AND THRESHOLD_GRP_ID = cn_threshold_grp_id
9359: );
9386: invoice_to_vendor_id,
9387: tds_event,
9388: calc_tax_amount,
9389: invoice_id
9390: FROM JAI_AP_TDS_THHOLD_TRXS
9391: WHERE TDS_ROLLBACKED IS NULL
9392: AND TDS_EVENT LIKE 'SURCHARGE_CALCULATE'
9393: AND THRESHOLD_GRP_ID = p_threshold_grp_id
9394: ORDER BY threshold_trx_id;
9423: ,aia.invoice_currency_code
9424: ,aia.exchange_rate
9425: ,aia.set_of_books_id
9426: ,aia.org_id
9427: FROM jai_ap_tds_thhold_trxs jattt
9428: ,ap_invoices_all aia
9429: WHERE aia.invoice_id = jattt.invoice_id
9430: AND jattt.threshold_grp_id = p_threshold_grp_id
9431: AND NVL(jattt.tds_rollbacked,'N') = 'Y'
9489: lv_process_flag varchar2(1);
9490: lv_process_message varchar2(250);
9491: lv_tds_invoice_flag varchar2(1);
9492: lv_tds_invoice_message varchar2(250);
9493: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
9494: ln_tax_id NUMBER;
9495:
9496: lv_out_message_name varchar2(240);
9497: ln_out_invoice_amount number;
9602: lv_process_message := p_process_message;
9603: goto exit_from_procedure;
9604: end if;
9605:
9606: update JAI_AP_TDS_THHOLD_TRXS jattt
9607: set jattt.TDS_ROLLBACKED = 'Y'
9608: where jattt.TDS_ROLLBACKED is NULL
9609: and TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK)
9610: and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
9734: );
9735: -----------------------------------------------------------------------
9736: --Added by Chong for surcharge rollback issue 2012/10/10 end
9737:
9738: update JAI_AP_TDS_THHOLD_TRXS jattt
9739: set jattt.TDS_ROLLBACKED = 'Y'
9740: where jattt.TDS_ROLLBACKED is NULL
9741: and TDS_EVENT LIKE G_SURCHARGE_CALCULATE
9742: and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
9762:
9763: ELSIF p_called_from = 'CANCEL' THEN
9764:
9765:
9766: --Fetch the Surcharge Invoice from JAI_AP_TDS_THHOLD_TRXS
9767: FOR cur_rec IN c_get_surcharge_trxs
9768: LOOP
9769: r_ap_invoices_all_au := NULL;
9770: r_ap_invoices_all_cm := NULL;
10095: <
10096: NULL;
10097: END LOOP;
10098:
10099: update JAI_AP_TDS_THHOLD_TRXS jattt
10100: set jattt.TDS_ROLLBACKED = 'Y'
10101: where jattt.TDS_ROLLBACKED is NULL
10102: and TDS_EVENT LIKE 'SURCHARGE_CALCULATE'
10103: and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
10116: --**Create RTN for transactions which in surcharge tax slab
10117:
10118: --Get surcharge taxable amount for surcharge RTN invoice
10119: SELECT SUM(jattt.TAXABLE_AMOUNT) INTO ln_taxable_amount
10120: FROM JAI_AP_TDS_THHOLD_TRXS jattt
10121: WHERE jattt.tds_event NOT in (G_SURCHARGE_CALCULATE)
10122: AND jattt.tds_rollbacked IS NULL
10123: AND jattt.threshold_grp_id = p_threshold_grp_id
10124: AND EXISTS(
10140: SELECT SUM(aila.AMOUNT) INTO ln_tax_amount
10141: FROM AP_INVOICE_LINES_ALL aila
10142: WHERE INVOICE_ID IN (
10143: SELECT NVL(jattt.invoice_to_tds_authority_id, jattt.invoice_to_vendor_id)
10144: FROM JAI_AP_TDS_THHOLD_TRXS jattt
10145: ,AP_INVOICES_ALL aia
10146: WHERE jattt.invoice_id = aia.invoice_id
10147: AND jattt.tds_event NOT in (G_SURCHARGE_CALCULATE)
10148: AND aia.cancelled_date IS NULL
10343: jcta.tax_id, --Added tax_id by Chong for bug#16414088 20130320
10344: jcta.section_type
10345: from jai_cmn_taxes_all jcta
10346: ,jai_ap_tds_inv_taxes jatit
10347: ,jai_ap_tds_thhold_trxs jattt
10348: where jatit.actual_tax_id = jcta.tax_id
10349: and jatit.threshold_trx_id = jattt.threshold_trx_id
10350: and jatit.threshold_grp_id = pn_threshold_grp_id
10351: and jatit.threshold_slab_id_single is null
10358: from gl_sets_of_books
10359: where set_of_books_id = cp_set_of_books_id;
10360:
10361: cursor c_get_ja_in_ap_inv_id is
10362: select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
10363: from dual;
10364:
10365: cursor c_ap_payment_schedules_all(p_invoice_id number) is
10366: select payment_priority
10442: cursor get_total_amt
10443: is
10444: select nvl(sum(decode(jattt.tds_event,'PREPAYMENT APPLICATION',-jatit.tax_amount,jatit.tax_amount)),0)
10445: from jai_ap_tds_inv_taxes jatit
10446: ,jai_ap_tds_thhold_trxs jattt
10447: where jatit.threshold_trx_id = jattt.threshold_trx_id
10448: and jatit.threshold_grp_id = pn_threshold_grp_id
10449: and jattt.tds_rollbacked is null
10450: and jatit.threshold_slab_id_single is null;
10454: cursor get_amt_for_tax_account(cn_tax_id jai_cmn_taxes_all.tax_id%type)--Change to tax_id by Chong for bug#16414088 20130320
10455: is
10456: select nvl(sum(decode(jattt.tds_event,'PREPAYMENT APPLICATION',-jatit.tax_amount,jatit.tax_amount)),0)
10457: from jai_ap_tds_inv_taxes jatit
10458: ,jai_ap_tds_thhold_trxs jattt
10459: ,jai_cmn_taxes_all jcta
10460: where jatit.threshold_trx_id = jattt.threshold_trx_id
10461: and jatit.actual_tax_id = jcta.tax_id
10462: and jatit.threshold_grp_id = pn_threshold_grp_id
10715: for rec_tax in (
10716: --select distinct jcta.tax_account_id
10717: select distinct jcta.tax_id --Change to tax_id by Chong for bug#16414088 20130320
10718: from jai_ap_tds_inv_taxes jatit
10719: ,jai_ap_tds_thhold_trxs jattt
10720: ,jai_cmn_taxes_all jcta
10721: where jattt.threshold_trx_id = jatit.threshold_trx_id
10722: and jatit.actual_tax_id = jcta.tax_id
10723: and jattt.tds_rollbacked is null
10964:
10965:
10966: /* Bug 5751783
10967: * Moved the assignment of ln_invoice_amount outside IF as this is used
10968: * in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
10969: * of the tds event.
10970: */
10971:
10972: --Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
11031:
11032: END IF;
11033: -- End for bug 5722028.
11034:
11035: insert into jai_ap_tds_thhold_trxs
11036: (
11037: threshold_trx_id ,
11038: invoice_id ,
11039: invoice_line_number ,
11068: TDS_SECTION_CODE
11069: )
11070: values
11071: (
11072: jai_ap_tds_thhold_trxs_s.nextval ,
11073: pn_invoice_id ,
11074: NULL ,
11075: NULL ,
11076: pn_threshold_grp_id ,