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.

[Home] [Help]

APPS.JAI_CMN_RGM_SETTLEMENT_PKG dependencies on JAI_RGM_STL_BALANCES

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

318:
319: end insert_into_vat_register;
320:
321:
322: PROCEDURE transfer_balance( pn_settlement_id IN jai_rgm_stl_balances.settlement_id%TYPE,
323: pv_process_flag OUT NOCOPY VARCHAR2,
324: pv_process_message OUT NOCOPY VARCHAR2)
325: IS
326: CURSOR c_debit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS

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

322: PROCEDURE transfer_balance( pn_settlement_id IN jai_rgm_stl_balances.settlement_id%TYPE,
323: pv_process_flag OUT NOCOPY VARCHAR2,
324: pv_process_message OUT NOCOPY VARCHAR2)
325: IS
326: CURSOR c_debit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
327: SELECT NVL(debit_balance,0) - NVL(credit_balance,0) debit_balance,
328: party_id,
329: location_id,
330: service_type_code , /* added by ssawant for bug 5879769 */

Line 333: FROM jai_rgm_stl_balances

329: location_id,
330: service_type_code , /* added by ssawant for bug 5879769 */
331: party_type,
332: rowid
333: FROM jai_rgm_stl_balances
334: WHERE settlement_id = pn_settlement_id
335: AND tax_type = lv_tax_type
336: AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
337: ORDER BY 1 desc;

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

335: AND tax_type = lv_tax_type
336: AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
337: ORDER BY 1 desc;
338:
339: CURSOR c_credit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
340: SELECT NVL(credit_balance,0) - NVL(debit_balance,0) credit_balance,
341: party_id,
342: location_id,
343: service_type_code ,/* added by ssawant for bug 5879769 */

Line 346: FROM jai_rgm_stl_balances

342: location_id,
343: service_type_code ,/* added by ssawant for bug 5879769 */
344: party_type,
345: rowid
346: FROM jai_rgm_stl_balances
347: WHERE settlement_id = pn_settlement_id
348: AND tax_type = lv_tax_type
349: AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
350: ORDER BY 1 desc;

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

348: AND tax_type = lv_tax_type
349: AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
350: ORDER BY 1 desc;
351:
352: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
353: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
354: ln_location_id jai_rgm_stl_balances.location_id%TYPE,
355: lv_party_type jai_rgm_stl_balances.party_type%TYPE
356: ) IS

Line 353: ln_party_id jai_rgm_stl_balances.party_id%TYPE,

349: AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
350: ORDER BY 1 desc;
351:
352: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
353: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
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

Line 354: ln_location_id jai_rgm_stl_balances.location_id%TYPE,

350: ORDER BY 1 desc;
351:
352: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
353: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
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

Line 355: lv_party_type jai_rgm_stl_balances.party_type%TYPE

351:
352: CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
353: ln_party_id jai_rgm_stl_balances.party_id%TYPE,
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

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

364: AND NVL(settled_flag,'N') <> 'Y'
365: AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0
366: ORDER BY 1 desc;
367:
368: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
369: ln_party_id jai_rgm_stl_balances.party_id%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

Line 369: ln_party_id jai_rgm_stl_balances.party_id%TYPE,

365: AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0
366: ORDER BY 1 desc;
367:
368: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
369: ln_party_id jai_rgm_stl_balances.party_id%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

Line 370: ln_location_id jai_rgm_stl_balances.location_id%TYPE,

366: ORDER BY 1 desc;
367:
368: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
369: ln_party_id jai_rgm_stl_balances.party_id%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

Line 371: lv_party_type jai_rgm_stl_balances.party_type%TYPE

367:
368: CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
369: ln_party_id jai_rgm_stl_balances.party_id%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

Line 405: FROM jai_rgm_stl_balances

401:
402: /* added by ssawant for bug 6020629 */
403: CURSOR c_acct_balances IS
404: SELECT *
405: FROM jai_rgm_stl_balances
406: WHERE NVL(debit_balance,0) >= 0
407: AND NVL(credit_balance,0) >= 0
408: AND settlement_id = pn_settlement_id;
409:

Line 421: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

417: SELECT jstl.primary_registration_no,
418: jbal.party_type ,
419: jbal.party_id ,
420: jbal.location_id
421: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
422: WHERE jbal.settlement_id = jstl.settlement_id
423: AND jbal.settlement_id = pn_settlement_id;
424:
425:

Line 435: from jai_rgm_stl_balances a

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
436: where 2 = (select count(distinct jbal.settlement_date)
437: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
438: where jbal.settlement_id = jstl.settlement_id
439: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));

Line 437: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

433: AND source_trx_type = 'Invoice Payment'
434: AND transaction_date = ( select max(settlement_date) + 1
435: from jai_rgm_stl_balances a
436: where 2 = (select count(distinct jbal.settlement_date)
437: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
438: where jbal.settlement_id = jstl.settlement_id
439: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));
440:
441:

Line 447: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

443: IS
444: --|| This cursor is used to retrieve the sum of credit and debit balances as on
445: --|| last settlement date for the given registration number
446: SELECT sum(jbal.credit_balance) credit_balance,sum(jbal.debit_balance) debit_balance
447: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
448: WHERE jbal.settlement_id = jstl.settlement_id
449: AND jstl.primary_registration_no = lp_regn_no
450: AND jbal.tax_type = lp_tax_type
451: AND jbal.party_id = lp_org_id

Line 453: from jai_rgm_stl_balances a

449: AND jstl.primary_registration_no = lp_regn_no
450: AND jbal.tax_type = lp_tax_type
451: AND jbal.party_id = lp_org_id
452: AND jstl.settlement_date = ( select max(settlement_date)
453: from jai_rgm_stl_balances a
454: where 2 = (select count(distinct jbal.settlement_date)
455: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
456: where jbal.settlement_id = jstl.settlement_id
457: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));

Line 455: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

451: AND jbal.party_id = lp_org_id
452: AND jstl.settlement_date = ( select max(settlement_date)
453: from jai_rgm_stl_balances a
454: where 2 = (select count(distinct jbal.settlement_date)
455: from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
456: where jbal.settlement_id = jstl.settlement_id
457: and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));
458:
459: */

Line 476: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

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
478: AND jstl.primary_registration_no = cp_regn_no
479: AND jbal.party_type = cp_org_type
480: AND jbal.party_id = cp_org_id

Line 496: FROM jai_rgm_stl_balances

492: || This cursor is used to retrieve the sum of credit and debit balances as on
493: || last settlement date for the given registration number,organization and location grouped at the tax type
494: */
495: SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
496: FROM jai_rgm_stl_balances
497: WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
498: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
499: WHERE jbal.settlement_id = jstl.settlement_id
500: AND jstl.primary_registration_no = cp_regn_no

Line 498: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl

494: */
495: SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
496: FROM jai_rgm_stl_balances
497: WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
498: FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
499: WHERE jbal.settlement_id = jstl.settlement_id
500: AND jstl.primary_registration_no = cp_regn_no
501: AND jbal.party_type = cp_org_type
502: AND jbal.party_id = cp_org_id

Line 525: ln_organization_id jai_rgm_stl_balances.party_id%TYPE;

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;
526: ln_location_id jai_rgm_stl_balances.location_id%TYPE;
527: lv_org_type jai_rgm_stl_balances.party_type%TYPE;
528: /*rchandan for bug#5642053 end*/
529:

Line 526: ln_location_id jai_rgm_stl_balances.location_id%TYPE;

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;
526: ln_location_id jai_rgm_stl_balances.location_id%TYPE;
527: lv_org_type jai_rgm_stl_balances.party_type%TYPE;
528: /*rchandan for bug#5642053 end*/
529:
530: BEGIN

Line 527: lv_org_type jai_rgm_stl_balances.party_type%TYPE;

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;
526: ln_location_id jai_rgm_stl_balances.location_id%TYPE;
527: lv_org_type jai_rgm_stl_balances.party_type%TYPE;
528: /*rchandan for bug#5642053 end*/
529:
530: BEGIN
531: /* */

Line 709: from jai_rgm_stl_balances a,

705:
706:
707:
708: FOR I in (select distinct b.regime_id, b.settlement_date, a.tax_type
709: from jai_rgm_stl_balances a,
710: jai_rgm_settlements b
711: where a.settlement_id = b.settlement_id
712: AND a.settlement_id = pn_settlement_id)
713: LOOP

Line 716: FROM jai_rgm_stl_balances

712: AND a.settlement_id = pn_settlement_id)
713: LOOP
714: SELECT count(*)
715: INTO ln_debit_cnt
716: FROM jai_rgm_stl_balances
717: WHERE settlement_id = pn_settlement_id
718: AND debit_balance >0;
719:
720:

Line 728: FROM jai_rgm_stl_balances

724: END IF;
725:
726: SELECT count(*)
727: INTO ln_credit_cnt
728: FROM jai_rgm_stl_balances
729: WHERE settlement_id = pn_settlement_id
730: AND credit_balance >0;
731:
732:

Line 839: update jai_rgm_stl_balances

835:
836: END IF;
837:
838:
839: update jai_rgm_stl_balances
840: SET debit_balance = debit_balance - ln_transfer_amt
841: WHERE rowid = cur_debit.rowid;
842:
843: update jai_rgm_stl_balances

Line 843: update jai_rgm_stl_balances

839: update jai_rgm_stl_balances
840: SET debit_balance = debit_balance - ln_transfer_amt
841: WHERE rowid = cur_debit.rowid;
842:
843: update jai_rgm_stl_balances
844: SET credit_balance = credit_balance - ln_transfer_amt
845: WHERE rowid = cur_credit.rowid;
846:
847: EXIT WHEN ln_credit_balance = 0;

Line 857: from jai_rgm_stl_balances

853: END LOOP;
854:
855: --for each transaction
856: FOR I in (select *
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

Line 974: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

970: pn_vendor_id IN jai_rgm_settlements.tax_authority_id%TYPE,
971: pn_vendor_site_id IN jai_rgm_settlements.tax_authority_site_id%TYPE,
972: pn_calculated_amount IN jai_rgm_settlements.calculated_amount%TYPE,
973: pn_invoice_amount IN jai_rgm_settlements.payment_amount%TYPE,
974: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
975: pv_regsitration_no IN jai_rgm_settlements.primary_registration_no%TYPE,
976: pn_created_by IN ap_invoices_interface.created_by%TYPE,
977: pd_creation_date IN ap_invoices_interface.creation_date%TYPE,
978: pn_last_updated_by IN ap_invoices_interface.last_updated_by%TYPE,

Line 1021: FROM JAI_RGM_STL_BALANCES_V

1017:
1018: /* commented the below cursor by ssawant for bug 5879769
1019: CURSOR cur_distributions IS
1020: SELECT tax_type, debit, credit, NVL(debit,0) - NVL(credit,0) balance_amount
1021: FROM JAI_RGM_STL_BALANCES_V
1022: WHERE settlement_id = pn_settlement_id
1023: AND NVL(debit,0) - NVL(credit,0) > 0;
1024: */
1025: /*

Line 1037: FROM JAI_RGM_STL_BALANCES

1033: tax_type ,
1034: sum(debit_balance) debit_balance ,
1035: sum(credit_balance) credit_balance,
1036: NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1037: FROM JAI_RGM_STL_BALANCES
1038: WHERE settlement_id = pn_settlement_id
1039: GROUP BY party_id,location_id,service_type_code,tax_type
1040: HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
1041:

Line 1047: FROM JAI_RGM_STL_BALANCES

1043: CURSOR cur_distributions_VAT IS
1044: SELECT party_id,location_id,tax_type,
1045: sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1046: NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1047: FROM JAI_RGM_STL_BALANCES
1048: WHERE settlement_id = pn_settlement_id
1049: GROUP BY party_id,location_id,tax_type
1050: HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
1051:

Line 1056: FROM JAI_RGM_STL_BALANCES

1052: CURSOR cur_distributions_TCS IS /*Added By CSahoo BUG#5631784*/
1053: SELECT party_id,location_id,tax_type,
1054: sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1055: NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1056: FROM JAI_RGM_STL_BALANCES
1057: WHERE settlement_id = pn_settlement_id
1058: GROUP BY party_id,location_id,tax_type;
1059:
1060: CURSOR cur_tax_types(p_reg_type jai_rgm_registrations.registration_type%TYPE )IS --rchandan for bug#4428980

Line 1074: FROM jai_rgm_stl_balances

1070: WHERE regime_id = pn_regime_id;
1071:
1072: CURSOR cur_org_io IS
1073: SELECT party_id,location_id
1074: FROM jai_rgm_stl_balances
1075: WHERE settlement_id = pn_settlement_id
1076: GROUP BY party_id,location_id
1077: HAVING sum(debit_balance) - sum(credit_balance) > 0;
1078:

Line 1093: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;

1089: for_terms_id_rec for_terms_id%ROWTYPE;
1090: counter_tds_dm_v NUMBER;
1091: ln_tmp NUMBER;
1092: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1093: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;

Line 1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;

1090: counter_tds_dm_v NUMBER;
1091: ln_tmp NUMBER;
1092: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1093: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1098: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;

Line 1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;

1091: ln_tmp NUMBER;
1092: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1093: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1098: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1099: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;

Line 1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;

1092: ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1093: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1098: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1099: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1100: v_open_period gl_period_statuses.period_name%type;

Line 1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;

1093: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1098: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1099: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1100: v_open_period gl_period_statuses.period_name%type;
1101: v_open_gl_date date;

Line 1098: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;

1094: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1098: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1099: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1100: v_open_period gl_period_statuses.period_name%type;
1101: v_open_gl_date date;
1102: lv_inv_exists varchar2(1) := 'N' ; -- Bug 4929081

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

1560: pv_process_message := SUBSTR(SQLERRM,1,200);
1561: END create_invoice;
1562:
1563: FUNCTION get_last_settlement_date
1564: (pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1565: /* Bug 5096787. Added by Lakshmi Gopalsami */
1566: pn_regime_id IN jai_rgm_settlements.regime_id%TYPE DEFAULT NULL
1567: )
1568: RETURN DATE

Line 1573: FROM JAI_RGM_STL_BALANCES

1569: IS
1570: CURSOR c_last_settlement_date
1571: IS
1572: SELECT MAX(settlement_date)
1573: FROM JAI_RGM_STL_BALANCES
1574: WHERE party_id = pn_org_id;
1575:
1576: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
1577:

Line 1576: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;

1572: SELECT MAX(settlement_date)
1573: FROM JAI_RGM_STL_BALANCES
1574: WHERE party_id = pn_org_id;
1575:
1576: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
1577:
1578: BEGIN
1579: OPEN c_last_settlement_date;
1580: FETCH c_last_settlement_date INTO ld_last_settlement_date;

Line 1588: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

1584:
1585: END get_last_settlement_date;
1586:
1587: FUNCTION get_last_settlement_date(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1588: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1589: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE)
1590: RETURN DATE
1591: IS
1592: CURSOR c_last_settlement_date

Line 1589: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE)

1585: END get_last_settlement_date;
1586:
1587: FUNCTION get_last_settlement_date(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1588: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1589: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE)
1590: RETURN DATE
1591: IS
1592: CURSOR c_last_settlement_date
1593: IS

Line 1595: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl

1591: IS
1592: CURSOR c_last_settlement_date
1593: IS
1594: SELECT MAX(jbal.settlement_date)
1595: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
1596: WHERE jbal.settlement_id = jstl.settlement_id
1597: AND jstl.regime_id = pn_regime_id
1598: AND party_id = pn_org_id
1599: AND location_id = pn_location_id;

Line 1601: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;

1597: AND jstl.regime_id = pn_regime_id
1598: AND party_id = pn_org_id
1599: AND location_id = pn_location_id;
1600:
1601: ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
1602:
1603: BEGIN
1604: OPEN c_last_settlement_date;
1605: FETCH c_last_settlement_date INTO ld_last_settlement_date;

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

1609:
1610: END get_last_settlement_date;
1611:
1612:
1613: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1614: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1615: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1616: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
1617: )

Line 1614: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,

1610: END get_last_settlement_date;
1611:
1612:
1613: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1614: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1615: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1616: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
1617: )
1618: IS

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

1611:
1612:
1613: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1614: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1615: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1616: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
1617: )
1618: IS
1619:

Line 1616: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE

1612:
1613: PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1614: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1615: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1616: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
1617: )
1618: IS
1619:
1620: /*ssawant : comenting the below cursor and redefining it for bug 5662296*/

Line 1625: FROM JAI_RGM_STL_BALANCES

1621: /*
1622: CURSOR c_last_settlement_balance
1623: IS
1624: SELECT debit_balance, credit_balance
1625: FROM JAI_RGM_STL_BALANCES
1626: WHERE party_id = pn_org_id
1627: AND tax_type = pv_tax_type
1628: AND settlement_date = (SELECT MAX(settlement_date)
1629: FROM JAI_RGM_STL_BALANCES

Line 1629: FROM JAI_RGM_STL_BALANCES

1625: FROM JAI_RGM_STL_BALANCES
1626: WHERE party_id = pn_org_id
1627: AND tax_type = pv_tax_type
1628: AND settlement_date = (SELECT MAX(settlement_date)
1629: FROM JAI_RGM_STL_BALANCES
1630: WHERE party_id = pn_org_id
1631: AND tax_type = pv_tax_type);
1632:
1633: */

Line 1639: FROM JAI_RGM_STL_BALANCES

1635: /*cursor added for bug 5662296*/
1636: CURSOR c_last_settlement_balance
1637: IS
1638: SELECT debit_balance, credit_balance
1639: FROM JAI_RGM_STL_BALANCES
1640: WHERE party_id = pn_org_id
1641: AND tax_type = pv_tax_type
1642: AND settlement_id = (SELECT MAX(settlement_id)
1643: FROM JAI_RGM_STL_BALANCES

Line 1643: FROM JAI_RGM_STL_BALANCES

1639: FROM JAI_RGM_STL_BALANCES
1640: WHERE party_id = pn_org_id
1641: AND tax_type = pv_tax_type
1642: AND settlement_id = (SELECT MAX(settlement_id)
1643: FROM JAI_RGM_STL_BALANCES
1644: WHERE party_id = pn_org_id
1645: AND tax_type = pv_tax_type);
1646:
1647: /* Added by Ramananda for bug#4407165 */

Line 1668: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,

1664:
1665: END get_last_balance_amount;
1666:
1667: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1668: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,

Line 1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,

1665: END get_last_balance_amount;
1666:
1667: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1668: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
1673: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */

Line 1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,

1666:
1667: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1668: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
1673: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
1674: )

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

1667: PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1668: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
1673: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
1674: )
1675: IS

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

1668: pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
1673: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
1674: )
1675: IS
1676: CURSOR c_last_settlement_balance

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

1669: pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670: pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671: pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672: pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
1673: pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
1674: )
1675: IS
1676: CURSOR c_last_settlement_balance
1677: IS

Line 1679: FROM JAI_RGM_STL_BALANCES

1675: IS
1676: CURSOR c_last_settlement_balance
1677: IS
1678: SELECT sum(debit_balance), sum(credit_balance) /* added sum by ssawant for bug 5879769 */
1679: FROM JAI_RGM_STL_BALANCES
1680: WHERE party_id = pn_org_id
1681: AND location_id = pn_location_id
1682: AND tax_type = pv_tax_type
1683: AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */

Line 1685: FROM JAI_RGM_STL_BALANCES jbal,

1681: AND location_id = pn_location_id
1682: AND tax_type = pv_tax_type
1683: AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
1684: AND settlement_id = (SELECT MAX(jbal.settlement_id)
1685: FROM JAI_RGM_STL_BALANCES jbal,
1686: jai_rgm_settlements jstl
1687: WHERE jbal.settlement_id = jstl.settlement_id
1688: AND jstl.regime_id = pn_regime_id
1689: AND party_id = pn_org_id

Line 1734: FROM jai_rgm_stl_balances

1730: party_id ,
1731: party_type ,
1732: location_id ,
1733: service_type_code /* added by ssawant for bug 5879769 */
1734: FROM jai_rgm_stl_balances
1735: WHERE settlement_id = pn_settlement_id
1736: GROUP BY tax_type, party_type, party_id,location_id,service_type_code /* added by ssawant for bug 5879769 */
1737: HAVING NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) > 0;
1738:

Line 1766: FROM jai_rgm_stl_balances

1762: WHERE regime_id = pn_regime_id;
1763:
1764: CURSOR cur_org_io IS
1765: SELECT party_id,location_id
1766: FROM jai_rgm_stl_balances
1767: WHERE settlement_id = pn_settlement_id
1768: GROUP BY party_id,location_id
1769: HAVING sum(debit_balance) - sum(credit_balance) > 0;
1770:

Line 1780: lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;

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;
1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;

Line 1781: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;

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;
1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;

Line 1782: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;

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;
1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
1786: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;

Line 1783: ln_amount2 jai_rgm_stl_balances.debit_balance%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;
1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
1786: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1787: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;

Line 1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;

1781: lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1782: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
1786: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1787: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1788:
1789: ln_discounted_amount NUMBER;

Line 1786: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;

1782: ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
1786: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1787: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1788:
1789: ln_discounted_amount NUMBER;
1790: lv_regime cur_regime_code%rowtype ;/* 4245365*/

Line 1787: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;

1783: ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784: ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785: ln_org_id jai_rgm_stl_balances.party_id%TYPE;
1786: ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1787: lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1788:
1789: ln_discounted_amount NUMBER;
1790: lv_regime cur_regime_code%rowtype ;/* 4245365*/
1791: org_io_rec cur_org_io%ROWTYPE;

Line 2085: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl

2081: IS
2082: CURSOR c_last_settlement_date
2083: IS
2084: SELECT MAX(jbal.settlement_date)
2085: FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
2086: WHERE jbal.settlement_id = jstl.settlement_id
2087: AND jstl.regime_id = pn_regime_id
2088: AND jstl.primary_registration_no = pn_regn_no
2089: AND jbal.party_id = nvl(pn_organization_id,jbal.party_id)