[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(SUM(BASIC_ED)
,0)
INTO V_BASIC_ED
FROM
JAI_CMN_RG_I_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND PAYMENT_REGISTER in ( 'RG23A' , 'RG23C' )
AND TRUNC(CREATION_DATE) >= P_START_DATE
AND TRUNC(CREATION_DATE) <= P_END_DATE;
SELECT
ORGANIZATION_NAME
INTO V_ORGANIZATION_NAME
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
SELECT
EC_CODE
INTO V_EC_CODE
FROM
JAI_CMN_INVENTORY_ORGS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID;
SELECT
NVL(SUM(BASIC_ED)
,0)
INTO LN_BASIC_ED
FROM
JAI_CMN_RG_I_TRXS A,
JAI_INV_ITM_SETUPS C
WHERE A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID = C.ORGANIZATION_ID
AND ( C.ITEM_TARIFF = CETSH
OR A.INVENTORY_ITEM_ID = cf_cenvat_dutyformula.INVENTORY_ITEM_ID )
AND A.PRIMARY_UOM_CODE = UNITS
AND A.PAYMENT_REGISTER in ( 'RG23A' , 'RG23C' )
AND A.TRANSACTION_TYPE = 'I'
AND A.ISSUE_TYPE in ( 'OPE' , 'HU' , 'EWE' )
AND A.LOCATION_ID = P_LOCATION_ID
AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) >= P_START_DATE
AND TRUNC(A.CREATION_DATE) <= P_END_DATE
AND A.EXCISE_DUTY_RATE <> 0;
SELECT
SUM(NVL(CR_BASIC_ED
,0) + NVL(CR_OTHER_ED
,0))
FROM
JAI_CMN_RG_PLA_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND CREATION_DATE >= P_START_DATE
AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
,SYSDATE))
AND TRANSACTION_SOURCE_NUM = 91;
SELECT
SUM(NVL(CREDIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_TYPE = 2
AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , 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 CREATION_DATE >= P_START_DATE
AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
,SYSDATE))
AND TRANSACTION_SOURCE_NUM = 91 );
SELECT
SUM(DECODE(UPPER(A.PAYMENT_REGISTER)
,'RG23A'
,NVL(A.BASIC_ED
,0) + NVL(A.ADDITIONAL_ED
,0) + NVL(A.OTHER_ED
,0)
,'RG23C'
,NVL(A.BASIC_ED
,0) + NVL(A.ADDITIONAL_ED
,0) + NVL(A.OTHER_ED
,0))) CENVAT_DUTY_PAYABLE
INTO LN_CR_BASIC_ED
FROM
JAI_CMN_RG_I_TRXS A,
MTL_SYSTEM_ITEMS B,
JAI_INV_ITM_SETUPS C
WHERE A.TRANSACTION_TYPE in ( 'I' , 'IA' , 'IOI' , 'PI' )
AND 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 = P_LOCATION_ID
AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) >= P_START_DATE
AND TRUNC(A.CREATION_DATE) <= NVL(P_END_DATE
,SYSDATE);
SELECT
SUM(NVL(DEBIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , 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)) );
SELECT
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))
INTO LN_OPENING_BALANCE
FROM
JAI_CMN_RG_23AC_II_TRXS
WHERE LOCATION_ID = P_LOCATION_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID
AND CREATION_DATE < P_START_DATE;
SELECT
NVL(SUM(CREDIT)
,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 = 'A'
AND JRO.SOURCE_REGISTER = 'RG23A_P2'
AND JRO.TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
SELECT
NVL(SUM(CREDIT)
,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 = 'C'
AND JRO.SOURCE_REGISTER = 'RG23C_P2'
AND JRO.TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
SELECT
SUM(SERVICE_CREDIT),
SUM(EDU_CESS_CREDIT)
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 (+) = TAX_TYPE_SERVICE_EDU_CESS
AND ( NVL(TRUNC(JRTF1.CREATION_DATE)
,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
,TRUNC(JRTF1.CREATION_DATE)) )
AND ( NVL(P_END_DATE
,TRUNC(SYSDATE)) )
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 (+) = TAX_TYPE_SERVICE_EDU_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)) )
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 (+) = TAX_TYPE_SERVICE_EDU_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 ( NVL(P_START_DATE
,TRUNC(JRTR1.CREATION_DATE)) )
AND ( NVL(P_END_DATE
,TRUNC(SYSDATE)) ) );
SELECT
SUM(SERVICE_CREDIT),
SUM(EDU_CESS_CREDIT)
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 (+) = TAX_TYPE_SH_SERVICE_EDU_CESS
AND ( NVL(TRUNC(JRTF1.CREATION_DATE)
,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
,TRUNC(JRTF1.CREATION_DATE)) )
AND ( NVL(P_END_DATE
,TRUNC(SYSDATE)) )
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 (+) = TAX_TYPE_SH_SERVICE_EDU_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)) )
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 (+) = TAX_TYPE_SH_SERVICE_EDU_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 ( NVL(P_START_DATE
,TRUNC(JRTR1.CREATION_DATE)) )
AND ( NVL(P_END_DATE
,TRUNC(SYSDATE)) ) );
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) );
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) );
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) );
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) );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_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) );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_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) );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_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) );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_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) );
SELECT
NVL(SUM(CR_BASIC_ED)
,0)
INTO LN_TAX_DUTY_SRVCS
FROM
JAI_CMN_RG_PLA_TRXS
WHERE TRANSACTION_SOURCE_NUM = 151
AND ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND CREATION_DATE >= P_START_DATE
AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
,SYSDATE));
SELECT
NVL(SUM(OTHER_TAX_CREDIT)
,0)
INTO LN_CESS_TAX_DUTY_SRVCS
FROM
JAI_CMN_RG_PLA_TRXS
WHERE TRANSACTION_SOURCE_NUM = 151
AND 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));
SELECT
SUM(NVL(CREDIT
,0) - NVL(DEBIT
,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) < P_START_DATE )
AND TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
SELECT
CONCURRENT_PROGRAM_ID,
NVL(ENABLE_TRACE
,'N')
FROM
FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_REQUEST_ID;
SELECT
A.SID,
A.SERIAL#,
B.SPID
FROM
V$SESSION A,
V$PROCESS B
WHERE AUDSID = USERENV('SESSIONID')
AND A.PADDR = B.ADDR;
SELECT
NAME
FROM
V$DATABASE;
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AP'
AND TAX_TYPE = 'Service'
AND TRUNC(CREATION_DATE) < P_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(P_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE = 'Service'
AND TRUNC(CREATION_DATE) < P_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(P_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(P_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AP'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
AND TRUNC(CREATION_DATE) < P_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 = TAX_TYPE_SERVICE_EDU_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(CREDIT_AMOUNT)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
AND TRUNC(CREATION_DATE) < P_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 = TAX_TYPE_SERVICE_EDU_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(P_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 = TAX_TYPE_SERVICE_EDU_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(P_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 = TAX_TYPE_SERVICE_EDU_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(P_START_DATE
,TRUNC(SYSDATE)) );
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
INTO LN_QTY_MFTRD
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 = CF_1FORMULA.INVENTORY_ITEM_ID
OR NVL(ITEMS.ITEM_TARIFF
,'xyz') = NVL(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(UNITS
,'XYZ')
AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
,0)
,-999.95) = NVL(CF_1FORMULA.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);
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
INTO LN_QTY_CLRD
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 = CF_QTY_CLEAREDFORMULA.INVENTORY_ITEM_ID
OR NVL(ITEMS.ITEM_TARIFF
,'xyz') = NVL(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(CF_QTY_CLEAREDFORMULA.EXCISE_DUTY_RATE
,-999.95)
AND NVL(JRGI.PRIMARY_UOM_CODE
,'xyz') = NVL(UNITS
,'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(PLA_AMOUNT)
,0)
INTO LN_PLA_AMOUNT
FROM
JAI_CMN_RG_PLA_HDRS A
WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
AND A.LOCATION_ID = P_LOCATION_ID
AND TRUNC(A.TR6_DATE) >= P_START_DATE
AND TRUNC(A.TR6_DATE) <= P_END_DATE
AND A.ACK_RECVD_FLAG = 'Y';
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_I_TRXS JRGI,
JAI_INV_ITM_SETUPS ITEMS
WHERE JRGI.TRANSACTION_TYPE in ( 'I' , 'PI' , 'IA' , 'IOI' )
AND ( JRGI.INVENTORY_ITEM_ID = cf_duty_payableformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(UNITS
,'XYZ')
AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
,0)
,-999.95) = NVL(cf_duty_payableformula.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);
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 = cf_duty_payableformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(UNITS
,'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_I_TRXS JRGI,
JAI_INV_ITM_SETUPS ITEMS
WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(UNITS
,'XYZ')
AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
,0)
,-999.95) = NVL(cf_other_dutiesformula.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 JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
AND PAYMENT_REGISTER = 'PLA' )
AND SOURCE_TYPE = 2
AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , 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 = cf_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(UNITS
,'XYZ')
AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
,0)
,-999.95) = NVL(cf_other_dutiesformula.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 JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
AND PAYMENT_REGISTER IN ( 'RG23A' , 'RG23C' ) )
AND SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , 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_23AC_I_TRXS JRGI,
JAI_INV_ITM_SETUPS ITEMS
WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(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 JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
AND REGISTER_TYPE = 'PLA' )
AND SOURCE_TYPE = 2
AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , 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_23AC_I_TRXS JRGI,
JAI_INV_ITM_SETUPS ITEMS
WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(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 JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
AND REGISTER_TYPE IN ( 'A' , 'C' ) )
AND SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS );
SELECT
NVL(SUM(DEBIT)
,0)
INTO LN_EDU_CESS_EXCISE
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 ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
SELECT
MSI.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = cf_item_description.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = P_ORGANIZATION_ID;
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 not exists (
SELECT
1
FROM
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND ( JMSI.ITEM_CLASS = 'RMEX'
OR ( JMSI.ITEM_CLASS = 'RMIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
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 not exists (
SELECT
1
FROM
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND ( JMSI.ITEM_CLASS = 'RMEX'
OR ( JMSI.ITEM_CLASS = 'RMIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
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 not exists (
SELECT
1
FROM
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND ( JMSI.ITEM_CLASS = 'RMEX'
OR ( JMSI.ITEM_CLASS = 'RMIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
SELECT
ROUND(NVL(SUM(DECODE(REGISTER_TYPE
,'A'
,NVL(CR_BASIC_ED
,0) + NVL(CR_ADDITIONAL_ED
,0) + NVL(CR_OTHER_ED
,0)
,0))
,0)
,0) CREDIT_ON_IMPORT_INPUTS
FROM
JAI_CMN_RG_23AC_II_TRXS JIRP,
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.LOCATION_ID = P_LOCATION_ID
AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
AND JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
,SYSDATE))
AND ( ( JIRP.VENDOR_ID is not null
AND JIRP.VENDOR_SITE_ID is not null )
OR ( JIRP.CUSTOMER_ID is not null
AND JIRP.LOCATION_ID is not null ) )
AND ( JMSI.ITEM_CLASS = 'RMEX'
OR ( JMSI.ITEM_CLASS = 'RMIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) );
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 not exists (
SELECT
1
FROM
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND ( JMSI.ITEM_CLASS = 'RMEX'
OR ( JMSI.ITEM_CLASS = 'RMIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
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 not exists (
SELECT
1
FROM
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND ( JMSI.ITEM_CLASS = 'RMEX'
OR ( JMSI.ITEM_CLASS = 'RMIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
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 ( TAX_TYPE_CVD_EDU_CESS , 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_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 ( TAX_TYPE_CVD_EDU_CESS , 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_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 ( TAX_TYPE_CVD_EDU_CESS , 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 ( '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 ( TAX_TYPE_CVD_EDU_CESS , 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_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 ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
SELECT
SUBSTR(JIMSI.ITEM_TARIFF
,1
,15)
FROM
JAI_INV_ITM_SETUPS JIMSI
WHERE JIMSI.INVENTORY_ITEM_ID = cf_get_cetsh.INVENTORY_ITEM_ID
AND JIMSI.ORGANIZATION_ID = P_ORGANIZATION_ID;
SELECT
PVS.VENDOR_SITE_CODE,
PVS.VENDOR_ID,
PVS.ADDRESS_LINE1,
PVS.ADDRESS_LINE2,
PVS.ADDRESS_LINE3,
PVS.VENDOR_SITE_ID
FROM
PO_VENDOR_SITES_ALL PVS,
JAI_CMN_RG_23AC_II_TRXS JIRP
WHERE JIRP.VENDOR_ID = PVS.VENDOR_ID
AND JIRP.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND JIRP.VENDOR_ID || JIRP.VENDOR_SITE_ID NOT IN (
SELECT
JIPV.VENDOR_ID || JIPV.VENDOR_SITE_ID
FROM
JAI_CMN_VENDOR_SITES JIPV )
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));
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;
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';
SELECT
SUM(NVL(DEBIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , 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 );
SELECT
SUM(NVL(DEBIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , 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' );
SELECT
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
FROM
JAI_CMN_RG_23AC_II_TRXS JIRP,
JAI_INV_ITM_SETUPS JMSI
WHERE JIRP.LOCATION_ID = P_LOCATION_ID
AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
AND JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
,SYSDATE))
AND ( JMSI.ITEM_CLASS = 'CGEX'
OR ( JMSI.ITEM_CLASS = 'CGIN'
AND exists (
SELECT
1
FROM
JAI_RCV_LINES JTL,
JAI_RCV_LINE_TAXES JRTL
WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) );
SELECT
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))
INTO LN_OPENING_BALANCE
FROM
JAI_CMN_RG_PLA_TRXS
WHERE LOCATION_ID = P_LOCATION_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID
AND CREATION_DATE < P_START_DATE;
SELECT
NVL(SUM(PLA_AMOUNT)
,0)
INTO LN_PLA_AMOUNT
FROM
JAI_CMN_RG_PLA_HDRS A
WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
AND A.LOCATION_ID = P_LOCATION_ID
AND TRUNC(A.TR6_DATE) >= P_START_DATE
AND TRUNC(A.TR6_DATE) <= P_END_DATE
AND A.ACK_RECVD_FLAG = 'Y';
SELECT
ROUND(SUM(NVL(DR_ADDITIONAL_ED
,0))
,0) AED_CREDIT_UTILIZED
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));
SELECT
ROUND(SUM(NVL(CR_ADDITIONAL_ED
,0))
,0)
FROM
JAI_CMN_RG_PLA_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND CREATION_DATE >= P_START_DATE
AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
,SYSDATE))
AND TRANSACTION_SOURCE_NUM = 91;
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(CREDIT
,0) - NVL(DEBIT
,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) < P_START_DATE )
AND TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_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 ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
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 ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
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 ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_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 ( '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 ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
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 ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
SELECT
NVL(SUM(CREDIT)
,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 = 'C'
AND JRO.SOURCE_REGISTER = 'RG23C_P2'
AND JRO.TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
SELECT
NVL(SUM(CREDIT)
,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 = 'A'
AND JRO.SOURCE_REGISTER = 'RG23A_P2'
AND JRO.TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
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 ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AP'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
AND TRUNC(CREATION_DATE) < P_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 = TAX_TYPE_SH_SERVICE_EDU_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(CREDIT_AMOUNT)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
AND TRUNC(CREATION_DATE) < P_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 = TAX_TYPE_SH_SERVICE_EDU_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(P_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 = TAX_TYPE_SH_SERVICE_EDU_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(P_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 = TAX_TYPE_SH_SERVICE_EDU_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(P_START_DATE
,TRUNC(SYSDATE)) );
SELECT
SUM(NVL(DEBIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_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 );
SELECT
SUM(NVL(DEBIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_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' );
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_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) );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_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) );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_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) );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_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) );
SELECT
SUM(NVL(DEBIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_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)) );
SELECT
SUM(NVL(CREDIT
,0))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_TYPE = 2
AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_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 CREATION_DATE >= P_START_DATE
AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
,SYSDATE))
AND TRANSACTION_SOURCE_NUM = 91 );
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 = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(UNITS
,'XYZ')
AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
,0)
,-999.95) = NVL(cf_sh_other_dutiesformula.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 JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
AND PAYMENT_REGISTER = 'PLA' )
AND SOURCE_TYPE = 2
AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_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 = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(UNITS
,'XYZ')
AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
,0)
,-999.95) = NVL(cf_sh_other_dutiesformula.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 JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
AND PAYMENT_REGISTER IN ( 'RG23A' , 'RG23C' ) )
AND SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_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_23AC_I_TRXS JRGI,
JAI_INV_ITM_SETUPS ITEMS
WHERE ( JRGI.INVENTORY_ITEM_ID = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(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 JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
AND REGISTER_TYPE = 'PLA' )
AND SOURCE_TYPE = 2
AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_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_23AC_I_TRXS JRGI,
JAI_INV_ITM_SETUPS ITEMS
WHERE ( JRGI.INVENTORY_ITEM_ID = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
OR ITEMS.ITEM_TARIFF = 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(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 JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
AND REGISTER_TYPE IN ( 'A' , 'C' ) )
AND SOURCE_TYPE = 1
AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS );