eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JAI_CMN_RGM_SETTLEMENT_PKG dependencies on JAI_RGM_STL_BALANCES

Line 157: as the jai_rgm_Stl_balances are not updated after distribution

153: 1k 3K
154:
155: In the first run of credit balance loop 2k will be setoff against 2.5k
156: In the second run of credit balance loop 1k will be setoff against 2.5k only.
157: as the jai_rgm_Stl_balances are not updated after distribution
158:
159: fix:
160:
161: added the pl/sql table logic in the transfer_balance procude to distribute the

Line 609: PROCEDURE transfer_balance( pn_settlement_id IN jai_rgm_stl_balances.settlement_id%TYPE,

605:
606: end insert_into_vat_register;
607:
608:
609: PROCEDURE transfer_balance( pn_settlement_id IN jai_rgm_stl_balances.settlement_id%TYPE,
610: pv_process_flag OUT NOCOPY VARCHAR2,
611: pv_process_message OUT NOCOPY VARCHAR2,
612: pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%TYPE DEFAULT NULL)--Added by Qiong for reverse charge settlement
613:

Line 615: CURSOR c_debit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS

611: pv_process_message OUT NOCOPY VARCHAR2,
612: pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%TYPE DEFAULT NULL)--Added by Qiong for reverse charge settlement
613:
614: IS
615: CURSOR c_debit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
616: SELECT NVL(debit_balance,0) - NVL(credit_balance,0) debit_balance,
617: party_id,
618: location_id,
619: service_type_code , /* added by ssawant for bug 5879769 */

Line 622: FROM jai_rgm_stl_balances

618: location_id,
619: service_type_code , /* added by ssawant for bug 5879769 */
620: party_type,
621: rowid
622: FROM jai_rgm_stl_balances
623: WHERE settlement_id = pn_settlement_id
624: AND tax_type = lv_tax_type
625: AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
626: -- ORDER BY 1 desc; 12706846

Line 629: CURSOR c_credit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS

625: AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
626: -- ORDER BY 1 desc; 12706846
627: ORDER BY 1;
628:
629: CURSOR c_credit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
630: SELECT NVL(credit_balance,0) - NVL(debit_balance,0) credit_balance,
631: party_id,
632: location_id,
633: service_type_code ,/* added by ssawant for bug 5879769 */

Line 636: FROM jai_rgm_stl_balances

632: location_id,
633: service_type_code ,/* added by ssawant for bug 5879769 */
634: party_type,
635: rowid
636: FROM jai_rgm_stl_balances
637: WHERE settlement_id = pn_settlement_id
638: AND tax_type = lv_tax_type
639: AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
640: ORDER BY 1 desc;

Line 642: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,

638: AND tax_type = lv_tax_type
639: AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
640: ORDER BY 1 desc;
641:
642: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
643: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
644: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
645: lv_party_type jai_rgm_stl_balances.party_type%TYPE
646: ) IS

Line 643: ln_party_id jai_rgm_stl_balances.party_id%TYPE,

639: AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
640: ORDER BY 1 desc;
641:
642: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
643: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
644: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
645: lv_party_type jai_rgm_stl_balances.party_type%TYPE
646: ) IS
647: SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid

Line 644: ln_location_id jai_rgm_stl_balances.location_id%TYPE,

640: ORDER BY 1 desc;
641:
642: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
643: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
644: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
645: lv_party_type jai_rgm_stl_balances.party_type%TYPE
646: ) IS
647: SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid
648: FROM jai_rgm_trx_records

Line 645: lv_party_type jai_rgm_stl_balances.party_type%TYPE

641:
642: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
643: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
644: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
645: lv_party_type jai_rgm_stl_balances.party_type%TYPE
646: ) IS
647: SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid
648: FROM jai_rgm_trx_records
649: WHERE tax_type = lv_tax_type

Line 662: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,

658: OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
659: )
660: ORDER BY 1 desc;
661:
662: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
663: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
664: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
665: lv_party_type jai_rgm_stl_balances.party_type%TYPE
666: ) IS

Line 663: ln_party_id jai_rgm_stl_balances.party_id%TYPE,

659: )
660: ORDER BY 1 desc;
661:
662: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
663: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
664: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
665: lv_party_type jai_rgm_stl_balances.party_type%TYPE
666: ) IS
667: SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid

Line 664: ln_location_id jai_rgm_stl_balances.location_id%TYPE,

660: ORDER BY 1 desc;
661:
662: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
663: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
664: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
665: lv_party_type jai_rgm_stl_balances.party_type%TYPE
666: ) IS
667: SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid
668: FROM jai_rgm_trx_records

Line 665: lv_party_type jai_rgm_stl_balances.party_type%TYPE

661:
662: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
663: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
664: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
665: lv_party_type jai_rgm_stl_balances.party_type%TYPE
666: ) IS
667: SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid
668: FROM jai_rgm_trx_records
669: WHERE tax_type = lv_tax_type

Line 703: FROM jai_rgm_stl_balances

699:
700: /* added by ssawant for bug 6020629 */
701: CURSOR c_acct_balances IS
702: SELECT *
703: FROM jai_rgm_stl_balances
704: WHERE NVL(debit_balance,0) >= 0
705: AND NVL(credit_balance,0) >= 0
706: AND settlement_id = pn_settlement_id;
707:

Line 719: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

715: SELECT jstl.primary_registration_no,
716: jbal.party_type ,
717: jbal.party_id ,
718: jbal.location_id
719: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
720: WHERE jbal.settlement_id = jstl.settlement_id
721: AND jbal.settlement_id = pn_settlement_id;
722:
723:

Line 733: from jai_rgm_stl_balances a

729: FROM jai_rgm_trx_records
730: WHERE regime_primary_regno = lp_regn_no
731: AND source_trx_type = 'Invoice Payment'
732: AND transaction_date = ( select max(settlement_date) + 1
733: from jai_rgm_stl_balances a
734: where 2 = (select count(distinct jbal.settlement_date)
735: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
736: where jbal.settlement_id = jstl.settlement_id
737: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));

Line 735: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

731: AND source_trx_type = 'Invoice Payment'
732: AND transaction_date = ( select max(settlement_date) + 1
733: from jai_rgm_stl_balances a
734: where 2 = (select count(distinct jbal.settlement_date)
735: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
736: where jbal.settlement_id = jstl.settlement_id
737: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));
738:
739:

Line 745: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

741: IS
742: --|| This cursor is used to retrieve the sum of credit and debit balances as on
743: --|| last settlement date for the given registration number
744: SELECT sum(jbal.credit_balance) credit_balance,sum(jbal.debit_balance) debit_balance
745: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
746: WHERE jbal.settlement_id = jstl.settlement_id
747: AND jstl.primary_registration_no = lp_regn_no
748: AND jbal.tax_type = lp_tax_type
749: AND jbal.party_id = lp_org_id

Line 751: from jai_rgm_stl_balances a

747: AND jstl.primary_registration_no = lp_regn_no
748: AND jbal.tax_type = lp_tax_type
749: AND jbal.party_id = lp_org_id
750: AND jstl.settlement_date = ( select max(settlement_date)
751: from jai_rgm_stl_balances a
752: where 2 = (select count(distinct jbal.settlement_date)
753: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
754: where jbal.settlement_id = jstl.settlement_id
755: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));

Line 753: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

749: AND jbal.party_id = lp_org_id
750: AND jstl.settlement_date = ( select max(settlement_date)
751: from jai_rgm_stl_balances a
752: where 2 = (select count(distinct jbal.settlement_date)
753: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
754: where jbal.settlement_id = jstl.settlement_id
755: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));
756:
757: */

Line 774: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

770: SELECT sum(credit_amount)
771: FROM jai_rgm_trx_records
772: WHERE source_trx_type = 'Invoice Payment'
773: AND settlement_id = ( SELECT MAX(jbal.settlement_id)
774: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
775: WHERE jbal.settlement_id = jstl.settlement_id
776: AND jstl.primary_registration_no = cp_regn_no
777: AND jbal.party_type = cp_org_type
778: AND jbal.party_id = cp_org_id

Line 794: FROM jai_rgm_stl_balances

790: || This cursor is used to retrieve the sum of credit and debit balances as on
791: || last settlement date for the given registration number,organization and location grouped at the tax type
792: */
793: SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
794: FROM jai_rgm_stl_balances
795: WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
796: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
797: WHERE jbal.settlement_id = jstl.settlement_id
798: AND jstl.primary_registration_no = cp_regn_no

Line 796: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

792: */
793: SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
794: FROM jai_rgm_stl_balances
795: WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
796: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
797: WHERE jbal.settlement_id = jstl.settlement_id
798: AND jstl.primary_registration_no = cp_regn_no
799: AND jbal.party_type = cp_org_type
800: AND jbal.party_id = cp_org_id

Line 818: FROM jai_rgm_stl_balances jbal,

814: WHERE org_tan_no = cp_regn_no
815: --AND source_document_type = 'Invoice Payment'
816: AND settlement_id =
817: (SELECT MAX(jbal.settlement_id)
818: FROM jai_rgm_stl_balances jbal,
819: jai_rgm_settlements jstl,
820: jai_rgm_definitions jrg
821: WHERE jbal.settlement_id = jstl.settlement_id
822: AND jrg.regime_id = jstl.regime_id

Line 831: CURSOR c_tcs_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,

827: --AND jbal.location_id = cp_location_id
828: AND jbal.settlement_id <> nvl(pn_settlement_id, -999))
829: ;
830:
831: CURSOR c_tcs_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
832: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
833: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
834: lv_party_type jai_rgm_stl_balances.party_type%TYPE
835: ) IS

Line 832: ln_party_id jai_rgm_stl_balances.party_id%TYPE,

828: AND jbal.settlement_id <> nvl(pn_settlement_id, -999))
829: ;
830:
831: CURSOR c_tcs_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
832: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
833: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
834: lv_party_type jai_rgm_stl_balances.party_type%TYPE
835: ) IS
836: SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0),0) credit_balance, organization_id party_id, rowid

Line 833: ln_location_id jai_rgm_stl_balances.location_id%TYPE,

829: ;
830:
831: CURSOR c_tcs_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
832: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
833: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
834: lv_party_type jai_rgm_stl_balances.party_type%TYPE
835: ) IS
836: SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0),0) credit_balance, organization_id party_id, rowid
837: FROM jai_rgm_refs_all

Line 834: lv_party_type jai_rgm_stl_balances.party_type%TYPE

830:
831: CURSOR c_tcs_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
832: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
833: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
834: lv_party_type jai_rgm_stl_balances.party_type%TYPE
835: ) IS
836: SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0),0) credit_balance, organization_id party_id, rowid
837: FROM jai_rgm_refs_all
838: WHERE organization_id = ln_party_id

Line 844: CURSOR c_tcs_dedit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,

840: AND settlement_id <= pn_settlement_id
841: ORDER BY 1 desc;
842:
843:
844: CURSOR c_tcs_dedit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
845: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
846: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
847: lv_party_type jai_rgm_stl_balances.party_type%TYPE
848: ) IS

Line 845: ln_party_id jai_rgm_stl_balances.party_id%TYPE,

841: ORDER BY 1 desc;
842:
843:
844: CURSOR c_tcs_dedit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
845: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
846: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
847: lv_party_type jai_rgm_stl_balances.party_type%TYPE
848: ) IS
849: SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, 0, 1),0) debit_balance, organization_id party_id, rowid

Line 846: ln_location_id jai_rgm_stl_balances.location_id%TYPE,

842:
843:
844: CURSOR c_tcs_dedit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
845: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
846: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
847: lv_party_type jai_rgm_stl_balances.party_type%TYPE
848: ) IS
849: SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, 0, 1),0) debit_balance, organization_id party_id, rowid
850: FROM jai_rgm_refs_all

Line 847: lv_party_type jai_rgm_stl_balances.party_type%TYPE

843:
844: CURSOR c_tcs_dedit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
845: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
846: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
847: lv_party_type jai_rgm_stl_balances.party_type%TYPE
848: ) IS
849: SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, 0, 1),0) debit_balance, organization_id party_id, rowid
850: FROM jai_rgm_refs_all
851: WHERE organization_id = ln_party_id

Line 874: ln_organization_id jai_rgm_stl_balances.party_id%TYPE;

870: ln_invoice_amount jai_rgm_trx_records.credit_amount%type ; /* added by ssawant for bug 6020629 */
871: rec_balances cur_balances%ROWTYPE;/* added by ssawant for bug 6020629 */
872: lv_regn_no jai_rgm_settlements.primary_registration_no%type;
873: /*rchandan for bug#5642053 start*/
874: ln_organization_id jai_rgm_stl_balances.party_id%TYPE;
875: ln_location_id jai_rgm_stl_balances.location_id%TYPE;
876: lv_org_type jai_rgm_stl_balances.party_type%TYPE;
877: /*rchandan for bug#5642053 end*/
878: --12706846

Line 875: ln_location_id jai_rgm_stl_balances.location_id%TYPE;

871: rec_balances cur_balances%ROWTYPE;/* added by ssawant for bug 6020629 */
872: lv_regn_no jai_rgm_settlements.primary_registration_no%type;
873: /*rchandan for bug#5642053 start*/
874: ln_organization_id jai_rgm_stl_balances.party_id%TYPE;
875: ln_location_id jai_rgm_stl_balances.location_id%TYPE;
876: lv_org_type jai_rgm_stl_balances.party_type%TYPE;
877: /*rchandan for bug#5642053 end*/
878: --12706846
879: type crdt_transfer is table of NUMBER ;

Line 876: lv_org_type jai_rgm_stl_balances.party_type%TYPE;

872: lv_regn_no jai_rgm_settlements.primary_registration_no%type;
873: /*rchandan for bug#5642053 start*/
874: ln_organization_id jai_rgm_stl_balances.party_id%TYPE;
875: ln_location_id jai_rgm_stl_balances.location_id%TYPE;
876: lv_org_type jai_rgm_stl_balances.party_type%TYPE;
877: /*rchandan for bug#5642053 end*/
878: --12706846
879: type crdt_transfer is table of NUMBER ;
880: ln_crdt_transfer crdt_transfer :=crdt_transfer();--added crdt_transfer() for bug#12706846 / 12996230 on 12thsep

Line 1147: from jai_rgm_stl_balances a,

1143:
1144: /**Observation :11821537 ,settle in ,settle out repository update is not required as the service tax is by IO and for settlement
1145: organization and location are mandatory ,need to comment the code**/
1146: FOR I in (select distinct b.regime_id, b.settlement_date, a.tax_type
1147: from jai_rgm_stl_balances a,
1148: jai_rgm_settlements b
1149: where a.settlement_id = b.settlement_id
1150: AND a.settlement_id = pn_settlement_id)
1151: LOOP

Line 1154: FROM jai_rgm_stl_balances

1150: AND a.settlement_id = pn_settlement_id)
1151: LOOP
1152: SELECT count(*)
1153: INTO ln_debit_cnt
1154: FROM jai_rgm_stl_balances
1155: WHERE settlement_id = pn_settlement_id
1156: AND debit_balance >0;
1157:
1158:

Line 1166: FROM jai_rgm_stl_balances

1162: END IF;
1163:
1164: SELECT count(*)
1165: INTO ln_credit_cnt
1166: FROM jai_rgm_stl_balances
1167: WHERE settlement_id = pn_settlement_id
1168: AND credit_balance >0;
1169:
1170:

Line 1298: update jai_rgm_stl_balances

1294:
1295: /*IF lv_regime_code NOT IN (jai_constants.service_regime,jai_constants.vat_regime) THEN
1296: --added by vumaasha for bug 7606212
1297:
1298: update jai_rgm_stl_balances
1299: SET debit_balance = debit_balance - ln_transfer_amt
1300: WHERE rowid = cur_debit.rowid;
1301:
1302: update jai_rgm_stl_balances

Line 1302: update jai_rgm_stl_balances

1298: update jai_rgm_stl_balances
1299: SET debit_balance = debit_balance - ln_transfer_amt
1300: WHERE rowid = cur_debit.rowid;
1301:
1302: update jai_rgm_stl_balances
1303: SET credit_balance = credit_balance - ln_transfer_amt
1304: WHERE rowid = cur_credit.rowid;
1305:
1306: END IF;*/

Line 1324: from jai_rgm_stl_balances

1320:
1321: --for each transaction
1322: /**haven't touch the below code for budget 2011 phase 2,as the settled_amount will not be used anywhere*/
1323: FOR I in (select *
1324: from jai_rgm_stl_balances
1325: where settlement_id = pn_settlement_id)
1326: LOOP
1327: IF NVL(i.debit_balance,0) = NVL(i.credit_balance,0) THEN
1328: UPDATE jai_rgm_trx_records

Line 1499: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

1495: pn_vendor_id IN jai_rgm_settlements.tax_authority_id%TYPE,
1496: pn_vendor_site_id IN jai_rgm_settlements.tax_authority_site_id%TYPE,
1497: pn_calculated_amount IN jai_rgm_settlements.calculated_amount%TYPE,
1498: pn_invoice_amount IN jai_rgm_settlements.payment_amount%TYPE,
1499: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1500: pv_regsitration_no IN jai_rgm_settlements.primary_registration_no%TYPE,
1501: pn_created_by IN ap_invoices_interface.created_by%TYPE,
1502: pd_creation_date IN ap_invoices_interface.creation_date%TYPE,
1503: pn_last_updated_by IN ap_invoices_interface.last_updated_by%TYPE,

Line 1546: FROM JAI_RGM_STL_BALANCES_V

1542:
1543: /* commented the below cursor by ssawant for bug 5879769
1544: CURSOR cur_distributions IS
1545: SELECT tax_type, debit, credit, NVL(debit,0) - NVL(credit,0) balance_amount
1546: FROM JAI_RGM_STL_BALANCES_V
1547: WHERE settlement_id = pn_settlement_id
1548: AND NVL(debit,0) - NVL(credit,0) > 0;
1549: */
1550: /*

Line 1563: FROM JAI_RGM_STL_BALANCES

1559: sum(debit_balance) debit_balance ,
1560: sum(credit_balance) credit_balance,
1561: /**added nvl(credit_utilized for bugdget 2011 ,er#11821537*/
1562: NVL(sum(debit_balance),0) - NVL(sum(nvl(credit_utilized,credit_balance)),0) balance_amount
1563: FROM JAI_RGM_STL_BALANCES
1564: WHERE settlement_id = pn_settlement_id
1565: GROUP BY party_id,location_id,service_type_code,tax_type
1566: HAVING sum(debit_balance) - sum(nvl(credit_utilized,credit_balance)) > 0 ;/**added nvl(credit_utilized for bugdget 2011 ,er#11821537*/
1567:

Line 1573: FROM JAI_RGM_STL_BALANCES

1569: CURSOR cur_distributions_VAT IS
1570: SELECT party_id,location_id,tax_type,
1571: sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1572: NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1573: FROM JAI_RGM_STL_BALANCES
1574: WHERE settlement_id = pn_settlement_id
1575: GROUP BY party_id,location_id,tax_type
1576: HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
1577:

Line 1621: FROM JAI_RGM_STL_BALANCES

1617: CURSOR cur_distributions_TCS IS /*Added By CSahoo BUG#5631784*/
1618: SELECT party_id,location_id,tax_type,
1619: sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1620: NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1621: FROM JAI_RGM_STL_BALANCES
1622: WHERE settlement_id = pn_settlement_id
1623: GROUP BY party_id,location_id,tax_type;
1624:
1625: CURSOR cur_tax_types(p_reg_type jai_rgm_registrations.registration_type%TYPE )IS --rchandan for bug#4428980

Line 1639: FROM jai_rgm_stl_balances

1635: WHERE regime_id = pn_regime_id;
1636:
1637: CURSOR cur_org_io IS
1638: SELECT party_id,location_id
1639: FROM jai_rgm_stl_balances
1640: WHERE settlement_id = pn_settlement_id
1641: GROUP BY party_id,location_id
1642: HAVING sum(debit_balance) - sum(credit_balance) > 0;
1643:

Line 1658: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;

1654: for_terms_id_rec for_terms_id%ROWTYPE;
1655: counter_tds_dm_v NUMBER;
1656: ln_tmp NUMBER;
1657: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1658: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;

Line 1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;

1655: counter_tds_dm_v NUMBER;
1656: ln_tmp NUMBER;
1657: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1658: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1663: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;

Line 1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;

1656: ln_tmp NUMBER;
1657: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1658: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1663: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1664: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;

Line 1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;

1657: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1658: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1663: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1664: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1665: v_open_period gl_period_statuses.period_name%type;

Line 1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;

1658: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1663: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1664: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1665: v_open_period gl_period_statuses.period_name%type;
1666: v_open_gl_date date;

Line 1663: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;

1659: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1663: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1664: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1665: v_open_period gl_period_statuses.period_name%type;
1666: v_open_gl_date date;
1667: lv_inv_exists varchar2(1) := 'N' ; -- Bug 4929081

Line 2163: (pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

2159: pv_process_message := SUBSTR(SQLERRM,1,200);
2160: END create_invoice;
2161:
2162: FUNCTION get_last_settlement_date
2163: (pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2164: /* Bug 5096787. Added by Lakshmi Gopalsami */
2165: pn_regime_id IN jai_rgm_settlements.regime_id%TYPE DEFAULT NULL,
2166: pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%TYPE DEFAULT NULL--Added by Qiong for reverse charge settlement
2167: )

Line 2173: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl --bug 8974544

2169: IS
2170: CURSOR c_last_settlement_date
2171: IS
2172: SELECT MAX(jbal.settlement_date)
2173: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl --bug 8974544
2174: WHERE jbal.settlement_id = jstl.settlement_id --bug 8974544
2175: AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N')--Added by Qiong for reverse charge settlment
2176: AND jstl.regime_id = pn_regime_id
2177: and party_id = pn_org_id;

Line 2179: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;

2175: AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N')--Added by Qiong for reverse charge settlment
2176: AND jstl.regime_id = pn_regime_id
2177: and party_id = pn_org_id;
2178:
2179: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
2180:
2181: BEGIN
2182: OPEN c_last_settlement_date;
2183: FETCH c_last_settlement_date INTO ld_last_settlement_date;

Line 2191: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

2187:
2188: END get_last_settlement_date;
2189:
2190: FUNCTION get_last_settlement_date(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2191: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2192: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2193: pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%type DEFAULT NULL--Added by Qiong for reverse charge settle
2194: )
2195: RETURN DATE

Line 2192: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,

2188: END get_last_settlement_date;
2189:
2190: FUNCTION get_last_settlement_date(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2191: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2192: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2193: pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%type DEFAULT NULL--Added by Qiong for reverse charge settle
2194: )
2195: RETURN DATE
2196: IS

Line 2200: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl

2196: IS
2197: CURSOR c_last_settlement_date
2198: IS
2199: SELECT MAX(jbal.settlement_date)
2200: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
2201: WHERE jbal.settlement_id = jstl.settlement_id
2202: AND jstl.regime_id = pn_regime_id
2203: AND party_id = pn_org_id
2204: AND location_id = pn_location_id

Line 2207: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;

2203: AND party_id = pn_org_id
2204: AND location_id = pn_location_id
2205: AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N');--Added by Qiong for reverse charge settle
2206:
2207: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
2208:
2209: BEGIN
2210: OPEN c_last_settlement_date;
2211: FETCH c_last_settlement_date INTO ld_last_settlement_date;

Line 2219: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

2215:
2216: END get_last_settlement_date;
2217:
2218:
2219: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2220: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2221: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2222: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
2223: )

Line 2220: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,

2216: END get_last_settlement_date;
2217:
2218:
2219: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2220: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2221: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2222: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
2223: )
2224: IS

Line 2221: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,

2217:
2218:
2219: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2220: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2221: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2222: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
2223: )
2224: IS
2225:

Line 2222: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE

2218:
2219: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2220: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2221: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2222: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
2223: )
2224: IS
2225:
2226: /*ssawant : comenting the below cursor and redefining it for bug 5662296*/

Line 2231: FROM JAI_RGM_STL_BALANCES

2227: /*
2228: CURSOR c_last_settlement_balance
2229: IS
2230: SELECT debit_balance, credit_balance
2231: FROM JAI_RGM_STL_BALANCES
2232: WHERE party_id = pn_org_id
2233: AND tax_type = pv_tax_type
2234: AND settlement_date = (SELECT MAX(settlement_date)
2235: FROM JAI_RGM_STL_BALANCES

Line 2235: FROM JAI_RGM_STL_BALANCES

2231: FROM JAI_RGM_STL_BALANCES
2232: WHERE party_id = pn_org_id
2233: AND tax_type = pv_tax_type
2234: AND settlement_date = (SELECT MAX(settlement_date)
2235: FROM JAI_RGM_STL_BALANCES
2236: WHERE party_id = pn_org_id
2237: AND tax_type = pv_tax_type);
2238:
2239: */

Line 2245: FROM JAI_RGM_STL_BALANCES

2241: /*cursor added for bug 5662296*/
2242: CURSOR c_last_settlement_balance
2243: IS
2244: SELECT debit_balance, credit_balance
2245: FROM JAI_RGM_STL_BALANCES
2246: WHERE party_id = pn_org_id
2247: AND tax_type = pv_tax_type
2248: AND settlement_id = (SELECT MAX(settlement_id)
2249: FROM JAI_RGM_STL_BALANCES

Line 2249: FROM JAI_RGM_STL_BALANCES

2245: FROM JAI_RGM_STL_BALANCES
2246: WHERE party_id = pn_org_id
2247: AND tax_type = pv_tax_type
2248: AND settlement_id = (SELECT MAX(settlement_id)
2249: FROM JAI_RGM_STL_BALANCES
2250: WHERE party_id = pn_org_id
2251: AND tax_type = pv_tax_type);
2252:
2253: /* Added by Ramananda for bug#4407165 */

Line 2274: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

2270:
2271: END get_last_balance_amount;
2272:
2273: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2274: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,

Line 2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,

2271: END get_last_balance_amount;
2272:
2273: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2274: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
2279: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */

Line 2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,

2272:
2273: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2274: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
2279: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
2280: )

Line 2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,

2273: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2274: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
2279: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
2280: )
2281: IS

Line 2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,

2274: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
2279: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
2280: )
2281: IS
2282: CURSOR c_last_settlement_balance

Line 2279: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */

2275: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
2279: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
2280: )
2281: IS
2282: CURSOR c_last_settlement_balance
2283: IS

Line 2285: FROM JAI_RGM_STL_BALANCES

2281: IS
2282: CURSOR c_last_settlement_balance
2283: IS
2284: SELECT sum(debit_balance), sum(credit_balance) /* added sum by ssawant for bug 5879769 */
2285: FROM JAI_RGM_STL_BALANCES
2286: WHERE party_id = pn_org_id
2287: AND location_id = pn_location_id
2288: AND tax_type = pv_tax_type
2289: -- AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */

Line 2292: FROM JAI_RGM_STL_BALANCES jbal,

2288: AND tax_type = pv_tax_type
2289: -- AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
2290: AND nvl(service_type_code,'-999') = nvl(pv_service_type_code,nvl(service_type_code,'-999') ) /* added nvl for bug#13865856*/
2291: AND settlement_id = (SELECT MAX(jbal.settlement_id)
2292: FROM JAI_RGM_STL_BALANCES jbal,
2293: jai_rgm_settlements jstl
2294: WHERE jbal.settlement_id = jstl.settlement_id
2295: AND jstl.regime_id = pn_regime_id
2296: AND party_id = pn_org_id

Line 2342: FROM jai_rgm_stl_balances

2338: party_id ,
2339: party_type ,
2340: location_id ,
2341: service_type_code /* added by ssawant for bug 5879769 */
2342: FROM jai_rgm_stl_balances
2343: WHERE settlement_id = pn_settlement_id
2344: GROUP BY tax_type, party_type, party_id,location_id,service_type_code /* added by ssawant for bug 5879769 */
2345: HAVING NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) > 0;
2346:

Line 2374: FROM jai_rgm_stl_balances

2370: WHERE regime_id = pn_regime_id;
2371:
2372: CURSOR cur_org_io IS
2373: SELECT party_id,location_id
2374: FROM jai_rgm_stl_balances
2375: WHERE settlement_id = pn_settlement_id
2376: GROUP BY party_id,location_id
2377: HAVING sum(debit_balance) - sum(credit_balance) > 0;
2378:

Line 2388: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;

2384: rec_settlement cur_settlement%ROWTYPE;
2385:
2386: ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
2387:
2388: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
2389: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;

Line 2389: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;

2385:
2386: ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
2387:
2388: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
2389: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;

Line 2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;

2386: ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
2387:
2388: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
2389: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
2394: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;

Line 2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;

2387:
2388: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
2389: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
2394: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
2395: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;

Line 2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;

2389: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
2394: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
2395: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
2396:
2397: ln_discounted_amount NUMBER;

Line 2394: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;

2390: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
2394: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
2395: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
2396:
2397: ln_discounted_amount NUMBER;
2398: lv_regime cur_regime_code%rowtype ;/* 4245365*/

Line 2395: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;

2391: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
2394: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
2395: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
2396:
2397: ln_discounted_amount NUMBER;
2398: lv_regime cur_regime_code%rowtype ;/* 4245365*/
2399: org_io_rec cur_org_io%ROWTYPE;

Line 2693: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl

2689: IS
2690: CURSOR c_last_settlement_date
2691: IS
2692: SELECT MAX(jbal.settlement_date)
2693: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
2694: WHERE jbal.settlement_id = jstl.settlement_id
2695: AND jstl.regime_id = pn_regime_id
2696: AND jstl.primary_registration_no = pn_regn_no
2697: AND jbal.party_id = nvl(pn_organization_id,jbal.party_id)

Line 2705: jai_rgm_stl_balances jstl,

2701: CURSOR c_last_reg_level_Settlement IS
2702: select settlement_id from
2703: (
2704: select jstl.party_id,jstl.location_id,jstl.settlement_id from
2705: jai_rgm_stl_balances jstl,
2706: jai_rgm_Settlements jrs,
2707: JAI_RGM_DEFINITIONS jr
2708: WHERE jstl.settlement_id = jrs.settlement_id
2709: AND jrs.regime_id = jr.regime_id

Line 2717: CURSOR c_settlement_date(cp_settlement_id jai_rgm_stl_balances.settlement_id%TYPE) is

2713: )group by settlement_id
2714: having count(*) >1
2715: order by settlement_id desc;
2716:
2717: CURSOR c_settlement_date(cp_settlement_id jai_rgm_stl_balances.settlement_id%TYPE) is
2718: SELECT settlement_date FROM
2719: jai_rgm_stl_balances where settlement_id = cp_settlement_id
2720: and rownum=1;
2721:

Line 2719: jai_rgm_stl_balances where settlement_id = cp_settlement_id

2715: order by settlement_id desc;
2716:
2717: CURSOR c_settlement_date(cp_settlement_id jai_rgm_stl_balances.settlement_id%TYPE) is
2718: SELECT settlement_date FROM
2719: jai_rgm_stl_balances where settlement_id = cp_settlement_id
2720: and rownum=1;
2721:
2722: l_last_reg_settlement jai_rgm_stl_balances.settlement_id%TYPE;
2723: -----------------------------------------------------

Line 2722: l_last_reg_settlement jai_rgm_stl_balances.settlement_id%TYPE;

2718: SELECT settlement_date FROM
2719: jai_rgm_stl_balances where settlement_id = cp_settlement_id
2720: and rownum=1;
2721:
2722: l_last_reg_settlement jai_rgm_stl_balances.settlement_id%TYPE;
2723: -----------------------------------------------------
2724: --Added by Eric Ma for bug 8333082,8671217 on Dec-19-2009,end
2725:
2726: ld_settlement_date date;