[Home] [Help]
354: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
355: lv_party_type jai_rgm_stl_balances.party_type%TYPE
356: ) IS
357: SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid
358: FROM jai_rgm_trx_records
359: WHERE tax_type = lv_tax_type
360: AND organization_id = ln_party_id
361: AND nvl(location_id,-999) = nvl(ln_location_id,-999)
362: AND organization_type = lv_party_type
370: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
371: lv_party_type jai_rgm_stl_balances.party_type%TYPE
372: ) IS
373: SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid
374: FROM jai_rgm_trx_records
375: WHERE tax_type = lv_tax_type
376: AND organization_id = ln_party_id
377: AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
378: AND organization_type = lv_party_type
427: IS
428: -- || This cursor is used to get the total invoice amount paid
429: -- || when the last settlement was made
430: SELECT sum(credit_amount)
431: FROM jai_rgm_trx_records
432: WHERE regime_primary_regno = lp_regn_no
433: AND source_trx_type = 'Invoice Payment'
434: AND transaction_date = ( select max(settlement_date) + 1
435: from jai_rgm_stl_balances a
469: || This cursor is used to get the total invoice amount paid
470: || when the last settlement was made
471: */
472: SELECT sum(credit_amount)
473: FROM jai_rgm_trx_records
474: WHERE source_trx_type = 'Invoice Payment'
475: AND settlement_id = ( SELECT MAX(jbal.settlement_id)
476: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
477: WHERE jbal.settlement_id = jstl.settlement_id
515: lv_regime_code JAI_RGM_DEFINITIONS.regime_code%type;/* Ravi*/
516: lv_statement NUMBER;
517: ln_dist_dtl_id NUMBER;
518: ln_acct_amount NUMBER; /* added by ssawant for bug 6020629 */
519: ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type; /* added by ssawant for bug 6020629 */
520: lv_organization_type VARCHAR2(10); /* added by ssawant for bug 6020629 */
521: ln_invoice_amount jai_rgm_trx_records.credit_amount%type ; /* added by ssawant for bug 6020629 */
522: rec_balances cur_balances%ROWTYPE;/* added by ssawant for bug 6020629 */
523: lv_regn_no jai_rgm_settlements.primary_registration_no%type;
517: ln_dist_dtl_id NUMBER;
518: ln_acct_amount NUMBER; /* added by ssawant for bug 6020629 */
519: ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type; /* added by ssawant for bug 6020629 */
520: lv_organization_type VARCHAR2(10); /* added by ssawant for bug 6020629 */
521: ln_invoice_amount jai_rgm_trx_records.credit_amount%type ; /* added by ssawant for bug 6020629 */
522: rec_balances cur_balances%ROWTYPE;/* added by ssawant for bug 6020629 */
523: lv_regn_no jai_rgm_settlements.primary_registration_no%type;
524: /*rchandan for bug#5642053 start*/
525: ln_organization_id jai_rgm_stl_balances.party_id%TYPE;
857: from jai_rgm_stl_balances
858: where settlement_id = pn_settlement_id)
859: LOOP
860: IF NVL(i.debit_balance,0) = NVL(i.credit_balance,0) THEN
861: UPDATE jai_rgm_trx_records
862: SET settled_flag = 'Y',
863: settled_amount = NULL
864: WHERE tax_type = i.tax_type
865: AND organization_id = i.party_id
869: ELSE
870:
871: SELECT count(*)
872: INTO ln_debit_cnt
873: FROM jai_rgm_trx_records
874: WHERE tax_type = i.tax_type
875: AND organization_id = i.party_id
876: AND nvl(location_id,-999) = nvl(i.location_id,-999)
877: AND organization_type = i.party_type
885: END IF;
886:
887: SELECT count(*)
888: INTO ln_credit_cnt
889: FROM jai_rgm_trx_records
890: WHERE tax_type = i.tax_type
891: AND organization_id = i.party_id
892: AND nvl(location_id,-999) = nvl(i.location_id,-999)
893: AND organization_type = i.party_type
911: ln_transfer_amt := ln_credit_balance;
912: ln_credit_balance := 0;
913: END IF;
914:
915: UPDATE jai_rgm_trx_records
916: SET settled_amount = NVL(settled_amount,0) - ln_transfer_amt,
917: settled_flag = 'P'
918: WHERE rowid = cur_debit.rowid;
919:
916: SET settled_amount = NVL(settled_amount,0) - ln_transfer_amt,
917: settled_flag = 'P'
918: WHERE rowid = cur_debit.rowid;
919:
920: UPDATE jai_rgm_trx_records
921: SET settled_amount = NVL(settled_amount,0) + ln_transfer_amt,
922: settled_flag = 'P'
923: WHERE rowid = cur_credit.rowid;
924:
929: END IF;
930:
931: <
932:
933: UPDATE jai_rgm_trx_records
934: SET settled_flag = 'Y',
935: settled_amount = debit_amount*-1
936: WHERE settlement_id <= pn_settlement_id
937: AND organization_id = i.party_id
940: AND tax_type = i.tax_type
941: AND debit_amount > 0
942: AND debit_amount = settled_amount*-1;
943:
944: UPDATE jai_rgm_trx_records
945: SET settled_flag = 'Y',
946: settled_amount = credit_amount
947: WHERE settlement_id <= pn_settlement_id
948: AND organization_id = i.party_id
1774: AND rownum = 1; */
1775:
1776: rec_settlement cur_settlement%ROWTYPE;
1777:
1778: ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
1779:
1780: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1781: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1782: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1790: lv_regime cur_regime_code%rowtype ;/* 4245365*/
1791: org_io_rec cur_org_io%ROWTYPE;
1792: ln_credit_amount NUMBER;
1793: ln_debit_amount NUMBER;
1794: ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type;
1795:
1796: BEGIN
1797:
1798: pv_process_flag := 'SS';
1843: IF pv_process_flag <> 'SS' THEN
1844: goto MAIN_EXIT;
1845: END IF;
1846:
1847: UPDATE jai_rgm_trx_records
1848: SET settlement_id = pn_settlement_id
1849: WHERE repository_id = ln_repository_id;
1850:
1851: ELSIF lv_regime.regime_code = 'VAT' THEN
1896: IF pv_process_flag <> 'SS' THEN
1897: goto MAIN_EXIT;
1898: END IF;
1899:
1900: UPDATE jai_rgm_trx_records
1901: SET settlement_id = pn_settlement_id
1902: WHERE repository_id = ln_repository_id;
1903: END LOOP;
1904: