[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT substr(ec_code,1,15) FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
SELECT round(nvl(sum(nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)),0),0)
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
AND TRANSACTION_SOURCE_NUM = 91
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm ;
SELECT round(sum(nvl(debit,0)),0)
FROM JAI_CMN_RG_OTHERS
WHERE tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
AND source_type = 1
AND source_register_id in
( SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE location_id = P_Location_id
AND organization_id = p_Organization_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
);
SELECT round(SUM(nvl(credit,0)),0)
FROM JAI_CMN_RG_OTHERS
WHERE source_type=2
AND tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
AND TRANSACTION_SOURCE_NUM = 91
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
);
SELECT ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized,
to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') year_month
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE location_id = p_location_id
AND organization_id = p_organization_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
group by
to_char(creation_date, 'MM'),
to_char(creation_date, 'YYYY')
ORDER BY
to_char(creation_date, 'YYYY'),
to_char(creation_date, 'MM') ;
SELECT MSI.description
FROM mtl_system_items MSI
WHERE MSI.inventory_item_id = p_inventory_item_id
AND MSI.organization_id = p_organization_id;
SELECT sum( NVL(MANUFACTURED_LOOSE_QTY,0)+
NVL(FOR_HOME_USE_PAY_ED_QTY,0)+
NVL(FOR_EXPORT_PAY_ED_QTY,0)+
NVL(FOR_EXPORT_N_PAY_ED_QTY,0)+
NVL(TO_OTHER_FACTORY_N_PAY_ED_QTY,0)+
NVL(OTHER_PURPOSE_N_PAY_ED_QTY,0)+
NVL(OTHER_PURPOSE_PAY_ED_QTY,0)) QTY_MANUFACTURED
FROM JAI_CMN_RG_I_TRXS jrgi,
JAI_INV_ITM_SETUPS items
WHERE jrgi.transaction_type in ( 'R','PR','RA','IOR','CR')
AND (jrgi.inventory_item_id = p_inventory_item_id
OR nvl(items.item_tariff,'xyz') = nvl(p_cetsh,'xyz'))
AND items.inventory_item_id = jrgi.inventory_item_id
AND jrgi.organization_id = p_organization_id
AND items.organization_id = jrgi.organization_id
AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
AND jrgi.location_id = p_location_id
AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm ;
SELECT sum( NVL(MANUFACTURED_LOOSE_QTY,0)+
NVL(FOR_HOME_USE_PAY_ED_QTY,0)+
NVL(FOR_EXPORT_PAY_ED_QTY,0)+
NVL(FOR_EXPORT_N_PAY_ED_QTY,0)+
NVL(TO_OTHER_FACTORY_N_PAY_ED_QTY,0)+
NVL(OTHER_PURPOSE_N_PAY_ED_QTY,0)+
NVL(OTHER_PURPOSE_PAY_ED_QTY,0)) QTY_MANUFACTURED
FROM JAI_CMN_RG_I_TRXS jrgi,JAI_INV_ITM_SETUPS items
WHERE jrgi.transaction_type in ( 'I','IA','PI','IOI')
AND ( jrgi.inventory_item_id = p_inventory_item_id
OR nvl(items.item_tariff,'xyz') = nvl(p_cetsh,'xyz'))
AND items.inventory_item_id = jrgi.inventory_item_id
AND jrgi.organization_id = p_organization_id
AND items.organization_id = jrgi.organization_id
AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
AND nvl(jrgi.primary_uom_code,'xyz') = nvl(p_units,'xyz')
AND jrgi.location_id = p_location_id
AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm ;
SELECT round(sum( NVL(jrgi.basic_ed,0 ) + NVL(jrgi.additional_ed,0) + NVL(jrgi.other_ed,0) ),0) Duty_payable
FROM JAI_CMN_RG_I_TRXS jrgi,JAI_INV_ITM_SETUPS items
WHERE jrgi.transaction_type in ( 'I','PI','IA','IOI')
AND ( jrgi.inventory_item_id = p_inventory_item_id
OR items.item_tariff = p_cetsh)
AND items.inventory_item_id = jrgi.inventory_item_id
AND jrgi.organization_id = p_organization_id
AND items.organization_id = jrgi.organization_id
AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
AND jrgi.location_id = p_location_id
AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm ;
SELECT nvl(sum(debit),0) FROM JAI_CMN_RG_OTHERS
WHERE source_register_id IN(
SELECT register_id_part_ii
FROM JAI_CMN_RG_I_TRXS jrgi,JAI_INV_ITM_SETUPS items
WHERE ( jrgi.inventory_item_id = p_inventory_item_id
OR items.item_tariff = p_cetsh)
AND items.inventory_item_id = jrgi.inventory_item_id
AND jrgi.organization_id = p_organization_id
AND items.organization_id = jrgi.organization_id
AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
AND jrgi.location_id = p_location_id
AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm
AND jrgi.transaction_type in ( 'I','IA','PI','IOI')
AND payment_register = 'PLA')
AND source_type = 2
AND tax_type IN (jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess ) ;
SELECT nvl(sum(debit),0)FROM JAI_CMN_RG_OTHERS
WHERE source_register_id IN(
SELECT register_id_part_ii FROM JAI_CMN_RG_I_TRXS jrgi, JAI_INV_ITM_SETUPS items
WHERE ( jrgi.inventory_item_id = p_inventory_item_id
OR items.item_tariff = p_cetsh )
AND items.inventory_item_id = jrgi.inventory_item_id
AND jrgi.organization_id = p_organization_id
AND items.organization_id = jrgi.organization_id
AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
AND jrgi.location_id = p_location_id
AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm
AND jrgi.transaction_type in ( 'I','IA','PI','IOI')
AND payment_register IN ('RG23A','RG23C') )
AND source_type = 1
AND tax_type IN (jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess ) ;
SELECT
a.primary_uom_code units,
c.item_tariff cetsh ,
substr(c.item_tariff,1,8) cetsh_sub,
0 inventory_item_id,
round(excise_duty_rate,0) excise_duty_rate ,
to_char(a.creation_date, 'YYYY') || to_char(a.creation_date, 'MM') year_month,
sum( nvl(a.basic_ed,0 ) + nvl(a.additional_ed,0) ) duty_payable,
sum( nvl(a.other_ed,0)) other_duties,
a.organization_id -- added, Harshita for Bug 5637136
FROM
JAI_CMN_RG_I_TRXS a ,
mtl_system_items b ,
JAI_INV_ITM_SETUPS c
WHERE a.inventory_item_id = b.inventory_item_id
AND c.inventory_item_id = b.inventory_item_id
AND c.organization_id = b.organization_id
AND a.organization_id = b.organization_id
AND a.location_id = nvl(p_location_id, a.location_id)
AND a.organization_id = nvl(p_organization_id, a.organization_id)
AND trunc(a.creation_date) >= trunc(p_start_date )
AND trunc(a.creation_date) <= trunc(nvl(p_end_date,sysdate))
GROUP BY
c.item_tariff ,
a.primary_uom_code,
round(excise_duty_rate,0),
to_char(a.creation_date, 'MM'),
to_char(a.creation_date, 'YYYY'),
a.organization_id -- added, Harshita for Bug 5637136
HAVING sum( nvl(manufactured_loose_qty,0)+
nvl(for_home_use_pay_ed_qty,0)+
nvl(for_export_pay_ed_qty,0)+
nvl(for_export_n_pay_ed_qty,0)+
nvl(to_other_factory_n_pay_ed_qty,0)+
nvl(other_purpose_n_pay_ed_qty,0)+
nvl(other_purpose_pay_ed_qty,0)) <> 0
ORDER BY
to_char(a.creation_date, 'YYYY'),
to_char(a.creation_date, 'MM') ;
SELECT round(sum(nvl(debit,0)),0)
FROM JAI_CMN_RG_OTHERS
WHERE tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
AND source_type = 1
AND source_register_id in
( SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS jrgi
WHERE location_id = P_Location_id
AND organization_id = p_Organization_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
AND register_id not in
( select NVL(register_id_part_ii,0)
from JAI_CMN_RG_I_TRXS
where payment_register IN ( 'RG23A','RG23C' )
)
);
SELECT round(SUM(nvl(credit,0)),0)
FROM JAI_CMN_RG_OTHERS
WHERE source_type=2
AND tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
AND TRANSACTION_SOURCE_NUM = 91
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
AND register_id not in
( select NVL(register_id_part_ii,0)
from JAI_CMN_RG_I_TRXS
where payment_register = 'PLA'
)
);
select excise_uom_code
from jai_ar_excise_uom
where organization_id = cp_organization_id
and primary_uom_code = cp_primary_uom_code ;
SELECT sum( NVL(jrgi.basic_ed,0 ) + NVL(jrgi.other_ed,0) ) Duty_payable,
sum( NVL(jrgi.additional_ed,0)) aed_duty_payable
FROM JAI_CMN_RG_23AC_I_TRXS jrgi,
JAI_INV_ITM_SETUPS items
WHERE jrgi.transaction_type in ( 'RTV', 'I', 'IA', 'IOI', 'PI')
and ( jrgi.inventory_item_id = cp_inventory_item_id
OR items.item_tariff = cp_cetsh
)
and items.inventory_item_id = jrgi.inventory_item_id
and jrgi.organization_id = p_organization_id
and items.organization_id = jrgi.organization_id
and nvl(jrgi.primary_uom_code,'XYZ') = nvl(cp_primary_uom_code,'XYZ')
and jrgi.location_id = p_location_id
and trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date);
SELECT nvl(sum(debit),0)
FROM JAI_CMN_RG_OTHERS
WHERE source_register_id IN
( SELECT register_id_part_ii
FROM JAI_CMN_RG_23AC_I_TRXS jrgi,
JAI_INV_ITM_SETUPS items
WHERE ( items.item_tariff = cp_cetsh
)
and items.inventory_item_id = jrgi.inventory_item_id
and jrgi.organization_id = p_organization_id
and items.organization_id = jrgi.organization_id
and nvl(jrgi.primary_uom_code,'XYZ') = nvl(cp_primary_uom_code,'XYZ')
and jrgi.location_id = p_location_id
and trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
and jrgi.transaction_type in ( 'RTV', 'I', 'IA', 'IOI', 'PI')
and register_type = 'PLA')
AND source_type = 2
AND tax_type in ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS');
SELECT nvl(sum(debit),0)
FROM JAI_CMN_RG_OTHERS
WHERE source_register_id IN
( SELECT register_id_part_ii
FROM JAI_CMN_RG_23AC_I_TRXS jrgi,
JAI_INV_ITM_SETUPS items
WHERE items.item_tariff = cp_cetsh
and items.inventory_item_id = jrgi.inventory_item_id
and jrgi.organization_id = p_organization_id
and items.organization_id = jrgi.organization_id
and nvl(jrgi.primary_uom_code,'XYZ') = nvl(cp_primary_uom_code,'XYZ')
and jrgi.location_id = p_location_id
and trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
and jrgi.transaction_type in ( 'RTV', 'I', 'IA', 'IOI', 'PI')
and register_type IN ('A','C') )
AND source_type = 1
AND tax_type in ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS');
SELECT
a.primary_uom_code UNITS,
c.item_tariff CETSH ,
Substr(c.ITEM_TARIFF,1,15) CETSH_SUB,
0 inventory_item_id,
0 excise_duty_rate ,
to_char(a.creation_date, 'YYYY') || to_char(a.creation_date, 'MM') year_month,
sum( NVL(a.basic_ed,0 ) + NVL(a.additional_ed,0) ) Duty_payable,
sum( NVL(a.other_ed,0)) Other_duties,
a.organization_id
FROM
JAI_CMN_RG_23AC_I_TRXS A ,
mtl_system_items b ,
JAI_INV_ITM_SETUPS c
where a.inventory_item_id = b.inventory_item_id
and c.inventory_item_id = b.inventory_item_id
and c.organization_id = b.organization_id
and a.organization_id = b.organization_id
and a.location_id = nvl(P_Location_id, a.location_id)
and a.organization_id = nvl(p_Organization_id, a.organization_id)
and trunc(a.creation_date) >= trunc(p_start_date )
and trunc(a.creation_date) <= trunc(nvl(p_end_date,sysdate))
GROUP BY
c.item_tariff ,
a.primary_uom_code,
a.organization_id ,
to_char(a.creation_date, 'MM'),
to_char(a.creation_date, 'YYYY')
ORDER BY
to_char(a.creation_date, 'YYYY'),
to_char(a.creation_date, 'MM')
)
LOOP
lv_uqc := NULL;
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)
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE location_id = p_location_id
AND organization_id = p_organization_id
AND trunc(creation_date) < cp_start_date;
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
FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_VENDOR_SITES JIPV
WHERE location_id = p_location_id
AND organization_id = p_organization_id
AND JIRP.vendor_id = JIPV.vendor_id
AND JIRP.vendor_site_id = JIPV.vendor_site_id
AND (
JIPV.vendor_type IN ('Manufacturer', 'Importer')
OR JIPV.vendor_type IS NULL)
AND TRUNC(JIRP.creation_date) >= p_start_date
AND TRUNC(JIRP.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
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
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
WHERE HZCAS.cust_acct_site_id = HZCSU.cust_acct_site_id
AND JICA.address_id = HZCSU.cust_acct_site_id
AND HZCSU.site_use_id = JIRP.customer_site_id
AND JIRP.customer_id = JICA.customer_id
AND JIRP.location_id = p_location_id
AND JIRP.organization_id = p_organization_id
AND TRUNC(JIRP.creation_date) >= p_start_date
AND TRUNC(JIRP.creation_date) <= TRUNC(nvl(p_end_date,SYSDATE))
AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
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
FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_INVENTORY_ORGS JIHO
WHERE JIRP.location_id = p_location_id
AND JIRP.organization_id = p_organization_id
AND ABS(jirp.vendor_id) = jiho.organization_id
AND ABS(jirp.vendor_site_id) = jiho.location_id
AND JIHO.manufacturing = 'Y'
AND TRUNC(jirp.creation_date) >= p_start_date
AND TRUNC(jirp.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
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
FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_VENDOR_SITES JIPV
WHERE location_id = p_location_id
AND organization_id = p_organization_id
AND JIRP.vendor_id = JIPV.vendor_id
AND JIRP.vendor_site_id = JIPV.vendor_site_id
AND JIPV.vendor_type IN ('First Stage Dealer', 'Second Stage Dealer')
AND TRUNC(JIRP.creation_date) >= p_start_date
AND TRUNC(JIRP.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
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
FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_INVENTORY_ORGS JIHO
WHERE JIRP.location_id = p_location_id
AND JIRP.organization_id = p_organization_id
AND ABS(JIRP.vendor_id) = JIHO.organization_id
AND ABS(JIRP.vendor_site_id) = JIHO.location_id
AND JIHO.trading = 'Y'
AND TRUNC(JIRP.creation_date) >= p_start_date
AND TRUNC(JIRP.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
SELECT sum(nvl(jrg23_ii.DR_BASIC_ED,0) + nvl(jrg23_ii.DR_ADDITIONAL_ED,0) + nvl(jrg23_ii.DR_OTHER_ED,0))
FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i
WHERE jrg23_ii.organization_id = p_organization_id
AND jrg23_ii.location_id = p_location_id
AND trunc(jrg23_ii.creation_date) >= p_start_date
AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
AND jrg23_i.transaction_type = 'RTV'
AND jrg23_ii.organization_id = jrg23_i.organization_id
AND jrg23_ii.location_id = jrg23_i.location_id
AND jrg23_ii.register_id_part_i = jrg23_i.register_id
AND to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm ;
SELECT sum(nvl(jrg23_ii.DR_BASIC_ED,0) + nvl(jrg23_ii.DR_ADDITIONAL_ED,0) + nvl(jrg23_ii.DR_OTHER_ED,0))
FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i,JAI_INV_ITM_SETUPS jmsi
WHERE jrg23_ii.organization_id = jrg23_i.organization_id
AND jrg23_ii.location_id = jrg23_i.location_id
AND jrg23_ii.register_id_part_i = jrg23_i.register_id
AND jmsi.organization_id = jrg23_ii.organization_id
AND jmsi.item_class like 'CG%'
AND jmsi.inventory_item_id = jrg23_ii.inventory_item_id
AND jmsi.organization_id = p_organization_id
AND jrg23_ii.organization_id = p_organization_id
AND jrg23_ii.location_id = p_location_id
AND trunc(jrg23_ii.creation_date) >= p_start_date
AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
AND jrg23_i.transaction_type <> 'RTV'
AND to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm ;
SELECT round(sum(nvl(credit,0) - nvl(debit,0)),0)
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND source_register_id in (
SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE location_id = p_location_id
AND organization_id = p_organization_id
AND trunc(creation_date) < cp_start_date)
AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess);
SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_VENDOR_SITES JIPV
WHERE jro.source_register_id = RG23.register_id
AND RG23.vendor_id = JIPV.vendor_id
AND RG23.vendor_site_id = JIPV.vendor_site_id
AND ( JIPV.vendor_type IN ('Manufacturer', 'Importer')
OR JIPV.vendor_type IS NULL)
AND rg23.location_id = p_location_id
AND rg23.organization_id = p_organization_id
AND TRUNC(rg23.creation_date) >= p_start_date
AND TRUNC(rg23.creation_date) <= TRUNC(NVL(p_end_date,sysdate))
AND RG23.register_type = 'A'
AND JRO.source_register = 'RG23A_P2'
AND JRO.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
AND to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
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
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
and jica.address_id = hzcsu.cust_acct_site_id
and hzcsu.site_use_id = rg23.customer_site_id
and rg23.customer_id = jica.customer_id
and jro.source_register_id = rg23.register_id
and rg23.location_id = p_location_id
and rg23.organization_id = p_organization_id
and trunc(rg23.creation_date) >= p_start_date
and trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
and rg23.register_type = 'A'
and jro.source_register = 'RG23A_P2'
and jro.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_INVENTORY_ORGS JIHO
WHERE jro.source_register_id = RG23.register_id
and abs(rg23.vendor_id) = jiho.organization_id
and abs(rg23.vendor_site_id)= jiho.location_id
and jiho.manufacturing = 'Y'
and rg23.location_id = p_location_id
and rg23.organization_id = p_organization_id
and trunc(rg23.creation_date) >= p_start_date
and trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
and rg23.register_type = 'A'
and jro.source_register = 'RG23A_P2'
and jro.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_VENDOR_SITES JIPV
WHERE jro.source_register_id = RG23.register_id
and rg23.vendor_id = jipv.vendor_id(+)
and rg23.vendor_site_id = jipv.vendor_site_id(+)
AND JIPV.vendor_type IN ('First Stage Dealer', 'Second Stage Dealer')
and rg23.location_id = p_location_id
and rg23.organization_id = p_organization_id
and trunc(rg23.creation_date) >= p_start_date
and trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
and rg23.register_type = 'A'
and jro.source_register = 'RG23A_P2'
and jro.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_INVENTORY_ORGS JIHO
WHERE jro.source_register_id = RG23.register_id
AND ABS(RG23.vendor_id) = JIHO.organization_id
AND ABS(RG23.vendor_site_id)= JIHO.location_id
AND JIHO.trading = 'Y'
AND RG23.location_id = p_location_id
AND RG23.organization_id = p_organization_id
AND TRUNC(RG23.creation_date) >= p_start_date
AND TRUNC(RG23.creation_date) <= TRUNC(NVL(p_end_date,sysdate))
AND RG23.register_type = 'A'
AND JRO.source_register = 'RG23A_P2'
AND JRO.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
SELECT round(nvl(sum(credit),0),0) FROM JAI_CMN_RG_OTHERS jro,JAI_CMN_RG_23AC_II_TRXS rg23
WHERE jro.source_register_id = rg23.register_id
AND rg23.location_id = p_location_id
AND rg23.organization_id = p_organization_id
AND trunc(rg23.creation_date) >= p_start_date
AND trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
AND rg23.register_type = p_register_type
AND jro.source_register = p_source_register
AND jro.tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
SELECT nvl(sum(debit),0) FROM JAI_CMN_RG_OTHERS jro,JAI_CMN_RG_23AC_II_TRXS rg23
WHERE jro.source_register_id = rg23.register_id
AND rg23.location_id = p_location_id
AND rg23.organization_id = p_organization_id
AND trunc(rg23.creation_date) >= p_start_date
AND trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
AND rg23.register_type IN ('A','C')
AND jro.source_register in ('RG23A_P2','RG23C_P2')
AND jro.tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
SELECT sum(nvl(debit,0))
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
AND source_register_id in (
SELECT jrg23_ii.register_id FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i
WHERE jrg23_ii.organization_id = p_organization_id
AND jrg23_ii.location_id = p_location_id
AND trunc(jrg23_ii.creation_date) >= p_start_date
AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
AND jrg23_i.transaction_type = 'RTV'
AND jrg23_ii.organization_id = jrg23_i.organization_id
AND jrg23_ii.location_id = jrg23_i.location_id
AND jrg23_ii.register_id_part_i = jrg23_i.register_id
and to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm
);
SELECT sum(nvl(debit,0))
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
AND source_register_id in (
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
WHERE jrg23_ii.organization_id = jrg23_i.organization_id
AND jrg23_ii.location_id = jrg23_i.location_id
AND jrg23_ii.register_id_part_i = jrg23_i.register_id
AND jmsi.organization_id = jrg23_ii.organization_id
AND jmsi.item_class like 'CG%'
AND jmsi.inventory_item_id = jrg23_ii.inventory_item_id
AND jmsi.organization_id = p_organization_id
AND jrg23_ii.organization_id = p_organization_id
AND jrg23_ii.location_id = p_location_id
AND trunc(jrg23_ii.creation_date) >= p_start_date
AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
AND jrg23_i.transaction_type <> 'RTV'
and to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm
);
SELECT sum(recovered_amount)
FROM jai_rgm_trx_refs
WHERE source = 'AP'
AND tax_type = 'Service'
AND trunc(creation_date) < cp_start_date
AND organization_id in
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
);
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE source = 'SERVICE_DISTRIBUTE_IN'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT SUM(recovered_amount)
FROM jai_rgm_trx_refs
WHERE source = 'AR'
AND tax_type = 'Service'
AND trunc(creation_date) < cp_start_date
AND organization_id IN
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
);
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'SERVICE_DISTRIBUTE_OUT'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = ( 'Service' )
AND source_trx_type = 'PAYMENT'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT nvl(sum(service_credit),0),nvl(sum(edu_cess_credit),0)
FROM (
SELECT jrtf1.recovered_amount service_credit ,jrtf2.recovered_amount edu_cess_credit
FROM jai_rgm_trx_refs jrtf1 ,jai_rgm_trx_refs jrtf2
WHERE jrtf1.source = 'AP'
AND jrtf1.invoice_id = jrtf2.invoice_id(+)
AND jrtf1.tax_type = 'Service'
AND jrtf2.tax_type(+) = 'SERVICE_EDUCATION_CESS'
AND NVL(trunc(jrtf1.creation_date),trunc(SYSDATE)) BETWEEN p_start_date AND p_end_date
AND to_char(jrtf1.creation_date, 'YYYY') || to_char(jrtf1.creation_date, 'MM') = ln_yyyymm
AND jrtf1.organization_id IN
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
)
UNION ALL
SELECT jrtr1.credit_amount service_credit ,jrtr2.credit_amount edu_cess_credit
FROM jai_rgm_trx_records jrtr1,jai_rgm_trx_records jrtr2
WHERE jrtr1.source = 'SERVICE_DISTRIBUTE_IN'
AND jrtr1.regime_code = 'SERVICE'
AND jrtr1.tax_type = 'Service'
AND jrtr2.tax_type(+) = 'SERVICE_EDUCATION_CESS'
AND jrtr1.organization_id = jrtr2.organization_id(+)
AND jrtr1.source_document_id = jrtr2.source_document_id(+)
AND jrtr1.regime_primary_regno = p_registration_number
AND (NVL(trunc(jrtr1.creation_date),trunc(SYSDATE))) BETWEEN (NVL(p_start_date,trunc(jrtr1.creation_date))) AND (NVL(p_end_date,trunc(SYSDATE)))
AND to_char(jrtr1.creation_date, 'YYYY') || to_char(jrtr1.creation_date, 'MM') = ln_yyyymm
UNION ALL
SELECT jrtr1.credit_amount service_credit ,jrtr2.credit_amount edu_cess_credit
FROM jai_rgm_trx_records jrtr1,jai_rgm_trx_records jrtr2
WHERE jrtr1.source = 'MANUAL'
AND jrtr1.regime_code = 'SERVICE'
AND jrtr1.tax_type = 'Service'
AND jrtr2.tax_type(+) = 'SERVICE_EDUCATION_CESS'
AND jrtr1.source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
AND jrtr1.source_trx_type = jrtr2.source_trx_type(+)
AND jrtr1.organization_id = jrtr2.organization_id(+)
AND jrtr1.source_document_id = jrtr2.source_document_id(+)
AND jrtr1.regime_primary_regno = p_registration_number
AND (NVL(trunc(jrtr1.creation_date),trunc(SYSDATE))) BETWEEN p_start_date AND p_end_date
AND to_char(jrtr1.creation_date, 'YYYY') || to_char(jrtr1.creation_date, 'MM') = ln_yyyymm
)
;
SELECT SUM(recovered_amount) FROM jai_rgm_trx_refs
WHERE source = 'AR'
AND tax_type = 'Service'
AND organization_id IN
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
)
AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
WHERE source = 'SERVICE_DISTRIBUTE_OUT'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'Service'
AND source_trx_type = 'PAYMENT'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
SELECT sum(recovered_amount) FROM jai_rgm_trx_refs
WHERE source = 'AP'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND trunc(creation_date) < cp_start_date
AND organization_id in
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
);
SELECT sum(credit_amount) FROM jai_rgm_trx_records
WHERE source = 'SERVICE_DISTRIBUTE_IN'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT SUM(recovered_amount)
FROM jai_rgm_trx_refs
WHERE source = 'AR'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND trunc(creation_date) < cp_start_date
AND organization_id IN
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
);
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'SERVICE_DISTRIBUTE_OUT'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND source_trx_type = 'PAYMENT'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(trunc(creation_date),trunc(SYSDATE))) < (NVL(cp_start_date,trunc(sysdate)));
SELECT SUM(recovered_amount) FROM jai_rgm_trx_refs
WHERE source = 'AR'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND organization_id IN
(
SELECT DISTINCT organization_id
FROM jai_rgm_org_regns_v
WHERE regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'PRIMARY'
AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
AND attribute_value = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
)
AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm ;
SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
WHERE source = 'SERVICE_DISTRIBUTE_OUT'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
SELECT nvl(sum(debit_amount),0)
FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
and (nvl(trunc(creation_date),sysdate)) between (nvl(p_start_date,sysdate)) and (nvl(p_end_date,sysdate))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
WHERE source = 'MANUAL'
AND regime_code = 'SERVICE'
AND tax_type = 'SERVICE_EDUCATION_CESS'
AND source_trx_type = 'PAYMENT'
AND regime_primary_regno = p_registration_number
AND organization_id = nvl(p_operating_unit,organization_id)
and (nvl(trunc(creation_date),sysdate)) between (nvl(p_start_date,sysdate)) and (nvl(p_end_date,sysdate))
AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
;
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,
ROUND(SUM(DECODE(register_type, 'C', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)), 0) credit_availed_on_cap_goods,
ROUND(SUM(NVL(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)), 0) total_credit_availed,
ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized ,
to_char(creation_date, 'YYYY') year,
to_char(creation_date, 'MM') month
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE location_id = p_location_id
AND organization_id = p_organization_id
AND trunc(creation_date) >= p_start_date
AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
group by
to_char(creation_date, 'MM'),
to_char(creation_date, 'YYYY')
ORDER BY
to_char(creation_date, 'YYYY'),
to_char(creation_date, 'MM') ;
select to_char(p_start_date, 'YYYYMM') from dual ;
select
sum(nvl(basic_ed,0) + nvl(additional_ed,0) + nvl(other_ed,0)) total_value,
sum(nvl(quantity_received,0)) total_quantity,
msi.attribute4 item_tariff,
msi.primary_uom_code
from
JAI_CMN_RG_23AC_I_TRXS jrp,
mtl_system_items msi
where
jrp.organization_id = msi.organization_id
and jrp.inventory_item_id = msi.inventory_item_id
and jrp.location_id = p_location_id
AND jrp.organization_id = p_organization_id
AND trunc(jrp.creation_date) >= p_start_date
AND trunc(jrp.creation_date) <= trunc(nvl(p_end_date,sysdate))
group by
to_char(jrp.creation_date, 'MM'),
to_char(jrp.creation_date, 'YYYY'),
msi.attribute4 , -- group by Item Tariff Head
msi.primary_uom_code
ORDER BY
to_char(jrp.creation_date, 'YYYY'),
to_char(jrp.creation_date, 'MM') ;
select to_char(p_start_date, 'YYYYMM') from dual ;
select to_char(p_start_date, 'YYYYMM') from dual ;
select NVL(SUM(pla_amount),0)
from JAI_CMN_RG_PLA_HDRS a
where a.organization_id = p_organization_id
and a.location_id = p_location_id
and trunc(a.creation_date) >= p_start_date
and trunc(a.creation_date) <= p_end_date
and a.ACK_RECVD_FLAG = 'Y';