[Home] [Help]
1582: WHERE tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
1583: AND source_type = 1
1584: AND source_register_id in
1585: ( SELECT register_id
1586: FROM JAI_CMN_RG_23AC_II_TRXS
1587: WHERE location_id = P_Location_id
1588: AND organization_id = p_Organization_id
1589: AND trunc(creation_date) >= p_start_date
1590: AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
1614: p_end_date IN DATE)
1615: IS
1616: SELECT ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized,
1617: to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') year_month
1618: FROM JAI_CMN_RG_23AC_II_TRXS
1619: WHERE location_id = p_location_id
1620: AND organization_id = p_organization_id
1621: AND trunc(creation_date) >= p_start_date
1622: AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
2004: WHERE tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
2005: AND source_type = 1
2006: AND source_register_id in
2007: ( SELECT register_id
2008: FROM JAI_CMN_RG_23AC_II_TRXS jrgi
2009: WHERE location_id = P_Location_id
2010: AND organization_id = p_Organization_id
2011: AND trunc(creation_date) >= p_start_date
2012: AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
2557: -- Cursor for cenvat opening balance
2558: CURSOR cur_opening_balance_cenvat(cp_start_date IN DATE )
2559: IS
2560: SELECT round(NVL(SUM(NVL(cr_basic_ed,0)+ NVL(cr_additional_ed,0) + NVL(cr_other_ed,0) - NVL(dr_basic_ed,0)- NVL(dr_additional_ed,0) - NVL(dr_other_ed,0)),0),0)
2561: FROM JAI_CMN_RG_23AC_II_TRXS
2562: WHERE location_id = p_location_id
2563: AND organization_id = p_organization_id
2564: AND trunc(creation_date) < cp_start_date;
2565:
2568: -- Cursors for Credit availed on Input on invoices issued by manufactureres
2569: CURSOR Cur_crdit_input_manf
2570: IS
2571: SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs_vend
2572: FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_VENDOR_SITES JIPV
2573: WHERE location_id = p_location_id
2574: AND organization_id = p_organization_id
2575: AND JIRP.vendor_id = JIPV.vendor_id
2576: AND JIRP.vendor_site_id = JIPV.vendor_site_id
2583:
2584: CURSOR Cur_crdit_input_cust
2585: IS
2586: SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs_cust
2587: FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_CUS_ADDRESSES JICA,hz_cust_acct_sites_all HZCAS,hz_cust_site_uses_all HZCSU
2588: WHERE HZCAS.cust_acct_site_id = HZCSU.cust_acct_site_id
2589: AND JICA.address_id = HZCSU.cust_acct_site_id
2590: AND HZCSU.site_use_id = JIRP.customer_site_id
2591: AND JIRP.customer_id = JICA.customer_id
2597:
2598: CURSOR Cur_crdit_input_manf_iso
2599: IS
2600: SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs
2601: FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_INVENTORY_ORGS JIHO
2602: WHERE JIRP.location_id = p_location_id
2603: AND JIRP.organization_id = p_organization_id
2604: AND ABS(jirp.vendor_id) = jiho.organization_id
2605: AND ABS(jirp.vendor_site_id) = jiho.location_id
2612:
2613: CURSOR Cur_crdit_input_stg
2614: IS
2615: SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs
2616: FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_VENDOR_SITES JIPV
2617: WHERE location_id = p_location_id
2618: AND organization_id = p_organization_id
2619: AND JIRP.vendor_id = JIPV.vendor_id
2620: AND JIRP.vendor_site_id = JIPV.vendor_site_id
2625:
2626: CURSOR Cur_crdit_input_stg_iso
2627: IS
2628: SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs
2629: FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_INVENTORY_ORGS JIHO
2630: WHERE JIRP.location_id = p_location_id
2631: AND JIRP.organization_id = p_organization_id
2632: AND ABS(JIRP.vendor_id) = JIHO.organization_id
2633: AND ABS(JIRP.vendor_site_id) = JIHO.location_id
2639: -- Cursors for Credit utilised (input/capital goods)
2640: CURSOR get_rtv_amount
2641: IS
2642: SELECT sum(nvl(jrg23_ii.DR_BASIC_ED,0) + nvl(jrg23_ii.DR_ADDITIONAL_ED,0) + nvl(jrg23_ii.DR_OTHER_ED,0))
2643: FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i
2644: WHERE jrg23_ii.organization_id = p_organization_id
2645: AND jrg23_ii.location_id = p_location_id
2646: AND trunc(jrg23_ii.creation_date) >= p_start_date
2647: AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
2653:
2654: CURSOR get_cgin_sales
2655: IS
2656: SELECT sum(nvl(jrg23_ii.DR_BASIC_ED,0) + nvl(jrg23_ii.DR_ADDITIONAL_ED,0) + nvl(jrg23_ii.DR_OTHER_ED,0))
2657: FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i,JAI_INV_ITM_SETUPS jmsi
2658: WHERE jrg23_ii.organization_id = jrg23_i.organization_id
2659: AND jrg23_ii.location_id = jrg23_i.location_id
2660: AND jrg23_ii.register_id_part_i = jrg23_i.register_id
2661: AND jmsi.organization_id = jrg23_ii.organization_id
2676: FROM JAI_CMN_RG_OTHERS
2677: WHERE source_type = 1
2678: AND source_register_id in (
2679: SELECT register_id
2680: FROM JAI_CMN_RG_23AC_II_TRXS
2681: WHERE location_id = p_location_id
2682: AND organization_id = p_organization_id
2683: AND trunc(creation_date) < cp_start_date)
2684: AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess);
2685:
2686: -- Cursors for Credit availed on Input on invoices issued by manufactureres (EDU CESS)
2687: CURSOR Cur_cess_excise_input_manf
2688: IS
2689: SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_VENDOR_SITES JIPV
2690: WHERE jro.source_register_id = RG23.register_id
2691: AND RG23.vendor_id = JIPV.vendor_id
2692: AND RG23.vendor_site_id = JIPV.vendor_site_id
2693: AND ( JIPV.vendor_type IN ('Manufacturer', 'Importer')
2702: AND to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2703:
2704: CURSOR Cur_cess_excise_input_cust
2705: IS
2706: SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_CUS_ADDRESSES JICA,hz_cust_acct_sites_all HZCAS,hz_cust_site_uses_all HZCSU
2707: WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
2708: and jica.address_id = hzcsu.cust_acct_site_id
2709: and hzcsu.site_use_id = rg23.customer_site_id
2710: and rg23.customer_id = jica.customer_id
2719: and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2720:
2721: CURSOR Cur_cess_excise_input_manf_iso
2722: IS
2723: SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_INVENTORY_ORGS JIHO
2724: WHERE jro.source_register_id = RG23.register_id
2725: and abs(rg23.vendor_id) = jiho.organization_id
2726: and abs(rg23.vendor_site_id)= jiho.location_id
2727: and jiho.manufacturing = 'Y'
2737:
2738: -- Cursor for Credit availed on Input on invoices issued by I or II stage dealers (EDU CESS)
2739: CURSOR Cur_cess_excise_input_stg
2740: IS
2741: SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_VENDOR_SITES JIPV
2742: WHERE jro.source_register_id = RG23.register_id
2743: and rg23.vendor_id = jipv.vendor_id(+)
2744: and rg23.vendor_site_id = jipv.vendor_site_id(+)
2745: AND JIPV.vendor_type IN ('First Stage Dealer', 'Second Stage Dealer')
2753: and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2754:
2755: CURSOR Cur_cess_excise_input_stg_iso
2756: IS
2757: SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_INVENTORY_ORGS JIHO
2758: WHERE jro.source_register_id = RG23.register_id
2759: AND ABS(RG23.vendor_id) = JIHO.organization_id
2760: AND ABS(RG23.vendor_site_id)= JIHO.location_id
2761: AND JIHO.trading = 'Y'
2771:
2772: -- Cursor for Credit on capital (EDU CESS) AND Total Credit (EDU CESS)
2773: CURSOR cur_edu_cess_cap(p_register_type IN VARCHAR2,p_source_register IN VARCHAR2)
2774: IS
2775: SELECT round(nvl(sum(credit),0),0) FROM JAI_CMN_RG_OTHERS jro,JAI_CMN_RG_23AC_II_TRXS rg23
2776: WHERE jro.source_register_id = rg23.register_id
2777: AND rg23.location_id = p_location_id
2778: AND rg23.organization_id = p_organization_id
2779: AND trunc(rg23.creation_date) >= p_start_date
2785:
2786: -- Cursor for Credit utilised(EDU CESS)
2787: CURSOR cur_edu_cess_excise
2788: IS
2789: SELECT nvl(sum(debit),0) FROM JAI_CMN_RG_OTHERS jro,JAI_CMN_RG_23AC_II_TRXS rg23
2790: WHERE jro.source_register_id = rg23.register_id
2791: AND rg23.location_id = p_location_id
2792: AND rg23.organization_id = p_organization_id
2793: AND trunc(rg23.creation_date) >= p_start_date
2803: FROM JAI_CMN_RG_OTHERS
2804: WHERE source_type = 1
2805: AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2806: AND source_register_id in (
2807: SELECT jrg23_ii.register_id FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i
2808: WHERE jrg23_ii.organization_id = p_organization_id
2809: AND jrg23_ii.location_id = p_location_id
2810: AND trunc(jrg23_ii.creation_date) >= p_start_date
2811: AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
2821: FROM JAI_CMN_RG_OTHERS
2822: WHERE source_type = 1
2823: AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2824: AND source_register_id in (
2825: SELECT jrg23_ii.register_id FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i,JAI_INV_ITM_SETUPS jmsi
2826: WHERE jrg23_ii.organization_id = jrg23_i.organization_id
2827: AND jrg23_ii.location_id = jrg23_i.location_id
2828: AND jrg23_ii.register_id_part_i = jrg23_i.register_id
2829: AND jmsi.organization_id = jrg23_ii.organization_id
3204: ROUND(SUM(NVL(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)), 0) total_credit_availed,
3205: ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized ,
3206: to_char(creation_date, 'YYYY') year,
3207: to_char(creation_date, 'MM') month
3208: FROM JAI_CMN_RG_23AC_II_TRXS
3209: WHERE location_id = p_location_id
3210: AND organization_id = p_organization_id
3211: AND trunc(creation_date) >= p_start_date
3212: AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))