[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
SUM(DECODE(TAX_TYPE
,'SERVICE_EDUCATION_CESS'
,TRXRECS.CREDIT_AMOUNT
,0)) SERVICE_CESS_AMOUNT,
SUM(DECODE(TAX_TYPE
,'Service'
,TRXRECS.CREDIT_AMOUNT
,0)) SERVICE_AMOUNT
FROM
JAI_RGM_TRX_RECORDS TRXRECS
WHERE TRXRECS.SOURCE in ( 'AP' , 'MANUAL' )
AND TRXRECS.SOURCE_TRX_TYPE = 'PAYMENT'
AND TRXRECS.REGIME_CODE = 'SERVICE'
AND TRUNC(TRXRECS.TRANSACTION_DATE) between PRM_FROMDT
AND PRM_TODT
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_MON_YEAR
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
INVC.INVOICE_NUM INVOICE_NUM,
INVC.INVOICE_DATE INVOICE_DATE
FROM
AP_INVOICE_PAYMENTS_ALL INVCPAY,
AP_INVOICES_ALL INVC,
PO_VENDOR_SITES_ALL VENDSITES,
PO_VENDORS VENDS
WHERE INVCPAY.INVOICE_ID = INVC.INVOICE_ID
AND INVC.VENDOR_ID = VENDSITES.VENDOR_ID
AND VENDSITES.VENDOR_ID = VENDS.VENDOR_ID
AND INVCPAY.CREATION_DATE BETWEEN PRM_FROMDT
AND PRM_TODT
AND VENDSITES.ORG_ID = NVL(PRM_ORG_ID
,VENDSITES.ORG_ID)
AND VENDS.VENDOR_TYPE_LOOKUP_CODE = 'Service Tax Authorities'
AND TO_CHAR(INVCPAY.CREATION_DATE
,'MON-YYYY') = LV_MON_YEAR
AND ROWNUM = 1;
SELECT
SUM(DECODE(TAX_TYPE
,'SERVICE_EDUCATION_CESS'
,TRXRECS.CREDIT_AMOUNT
,0)) SERVICE_CESS_ST_AMOUNT,
SUM(DECODE(TAX_TYPE
,'Service'
,TRXRECS.CREDIT_AMOUNT
,0)) SERVICE_ST_AMOUNT
FROM
JAI_RGM_TRX_RECORDS TRXRECS
WHERE ( TRXRECS.SOURCE in ( 'AP' , 'SERVICE_DISTRIBUTE_IN' )
OR TRXRECS.SOURCE in ( 'MANUAL' )
AND TRXRECS.SOURCE_TRX_TYPE in ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' ) )
AND TRXRECS.REGIME_CODE = 'SERVICE'
AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_MON_YEAR
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AP'
AND TAX_TYPE in ( 'Service' )
AND TRUNC(CREATION_DATE) < LV_DATE
AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
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 = PRM_RGM_PRIM_REGNO );
SELECT
SUM(CREDIT_AMOUNT)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(CREDIT_AMOUNT)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' )
AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE in ( 'Service' )
AND TRUNC(CREATION_DATE) < LV_DATE
AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
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 = PRM_RGM_PRIM_REGNO );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' )
AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' )
AND SOURCE_TRX_TYPE = 'PAYMENT'
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(TRXRECS.CREDIT_AMOUNT) SERVICE_CREDIT
FROM
JAI_RGM_TRX_RECORDS TRXRECS,
JAI_RGM_TRX_REFS TRXREFS,
AP_INVOICES_ALL APA,
JAI_CMN_VENDOR_SITES JPVS,
PO_VENDOR_SITES_ALL PVSA,
JAI_INV_ITM_SETUPS JMSI
WHERE TRXREFS.REFERENCE_ID = TRXRECS.REFERENCE_ID
AND APA.CANCELLED_DATE IS NULL
AND APA.CANCELLED_BY IS NULL
AND APA.CANCELLED_AMOUNT IS NULL
AND JPVS.VENDOR_ID = APA.VENDOR_ID
AND JPVS.VENDOR_SITE_ID = APA.VENDOR_SITE_ID
AND APA.VENDOR_ID = PVSA.VENDOR_ID
AND APA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID
AND APA.INVOICE_ID = TRXREFS.INVOICE_ID
AND TRXREFS.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND TRXREFS.ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND JMSI.ITEM_CLASS = CPV_ITEM_CLASS
AND TRXRECS.SOURCE in ( 'AP' )
AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRXRECS.REGIME_CODE = 'SERVICE'
AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,TRXRECS.SERVICE_TYPE_CODE)
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXREFS.TAX_TYPE = 'Service';
SELECT
SUM(JRTR.CREDIT_AMOUNT) SERVICE_CREDIT
FROM
JAI_RGM_TRX_RECORDS JRTR,
JAI_RGM_MANUAL_TRXS JMT,
PO_VENDOR_SITES_ALL PVSA
WHERE JRTR.SOURCE = 'MANUAL'
AND JRTR.REGIME_CODE = 'SERVICE'
AND JRTR.SOURCE_DOCUMENT_ID = JMT.TRANSACTION_NUMBER
AND PVSA.VENDOR_ID = JMT.PARTY_ID
AND PVSA.VENDOR_SITE_ID = JMT.PARTY_SITE_ID
AND JMT.ITEM_CLASS = CPV_ITEM_CLASS
AND JRTR.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(JRTR.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,JRTR.SERVICE_TYPE_CODE)
AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
,JRTR.ORGANIZATION_ID)
AND JRTR.TAX_TYPE = 'Service';
SELECT
SUM(JRTR.CREDIT_AMOUNT) SERVICE_CREDIT
FROM
JAI_RGM_TRX_RECORDS JRTR
WHERE JRTR.SOURCE = 'SERVICE_DISTRIBUTE_IN'
AND JRTR.REGIME_CODE = 'SERVICE'
AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(JRTR.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,JRTR.SERVICE_TYPE_CODE)
AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
,JRTR.ORGANIZATION_ID)
AND JRTR.TAX_TYPE = 'Service';
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS TRXREFS
WHERE SOURCE = 'AR'
AND TAX_TYPE in ( 'Service' , '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 = PRM_RGM_PRIM_REGNO )
AND ( NVL(TRUNC(CREATION_DATE)
,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
,SYSDATE) )
AND ( NVL(PRM_TODT
,SYSDATE) )
AND TO_CHAR(TRXREFS.CREATION_DATE
,'MON-YYYY') = LV_DATE
AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE;
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS TRXRECS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' , 'SERVICE_EDUCATION_CESS' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
,SYSDATE) )
AND ( NVL(PRM_TODT
,SYSDATE) )
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,TRXRECS.SERVICE_TYPE_CODE);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS TRXRECS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' , 'SERVICE_EDUCATION_CESS' )
AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
,SYSDATE) )
AND ( NVL(PRM_TODT
,SYSDATE) )
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,TRXRECS.SERVICE_TYPE_CODE);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS TRXRECS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'Service' , 'SERVICE_EDUCATION_CESS' )
AND SOURCE_TRX_TYPE = 'PAYMENT'
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
,SYSDATE) )
AND ( NVL(PRM_TODT
,SYSDATE) )
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,TRXRECS.SERVICE_TYPE_CODE);
SELECT
SUM(TRXRECS.CREDIT_AMOUNT) SERVICE_CREDIT
FROM
JAI_RGM_TRX_RECORDS TRXRECS,
JAI_RGM_TRX_REFS TRXREFS,
AP_INVOICES_ALL APA,
JAI_CMN_VENDOR_SITES JPVS,
PO_VENDOR_SITES_ALL PVSA,
JAI_INV_ITM_SETUPS JMSI
WHERE TRXREFS.REFERENCE_ID = TRXRECS.REFERENCE_ID
AND APA.CANCELLED_DATE IS NULL
AND APA.CANCELLED_BY IS NULL
AND APA.CANCELLED_AMOUNT IS NULL
AND JPVS.VENDOR_ID = APA.VENDOR_ID
AND JPVS.VENDOR_SITE_ID = APA.VENDOR_SITE_ID
AND APA.VENDOR_ID = PVSA.VENDOR_ID
AND APA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID
AND APA.INVOICE_ID = TRXREFS.INVOICE_ID
AND TRXREFS.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND TRXREFS.ITEM_ID IS NULL
AND TRXRECS.SOURCE in ( 'AP' )
AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRXRECS.REGIME_CODE = 'SERVICE'
AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,TRXRECS.SERVICE_TYPE_CODE)
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXREFS.TAX_TYPE = 'Service';
SELECT
SUM(JRTR.CREDIT_AMOUNT) SERVICE_CREDIT
FROM
JAI_RGM_TRX_RECORDS JRTR,
JAI_RGM_MANUAL_TRXS JMT,
PO_VENDOR_SITES_ALL PVSA
WHERE JRTR.SOURCE = 'MANUAL'
AND JRTR.REGIME_CODE = 'SERVICE'
AND JRTR.SOURCE_DOCUMENT_ID = JMT.TRANSACTION_NUMBER
AND PVSA.VENDOR_ID = JMT.PARTY_ID
AND PVSA.VENDOR_SITE_ID = JMT.PARTY_SITE_ID
AND JRTR.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(JRTR.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,JRTR.SERVICE_TYPE_CODE)
AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
,JRTR.ORGANIZATION_ID)
AND JRTR.TAX_TYPE = 'Service';
SELECT
SUM(JRTR.CREDIT_AMOUNT) TAX_AMOUNT
FROM
JAI_RGM_TRX_RECORDS JRTR
WHERE JRTR.SOURCE = 'SERVICE_DISTRIBUTE_IN'
AND JRTR.REGIME_CODE = 'SERVICE'
AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(JRTR.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,JRTR.SERVICE_TYPE_CODE)
AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
,JRTR.ORGANIZATION_ID);
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AP'
AND TAX_TYPE in ( 'SERVICE_EDUCATION_CESS' )
AND TRUNC(CREATION_DATE) < LV_DATE
AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
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 = PRM_RGM_PRIM_REGNO );
SELECT
SUM(CREDIT_AMOUNT)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(CREDIT_AMOUNT)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(RECOVERED_AMOUNT)
FROM
JAI_RGM_TRX_REFS
WHERE SOURCE = 'AR'
AND TAX_TYPE in ( 'SERVICE_EDUCATION_CESS' )
AND TRUNC(CREATION_DATE) < LV_DATE
AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
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 = PRM_RGM_PRIM_REGNO );
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0)
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'MANUAL'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
AND SOURCE_TRX_TYPE = 'PAYMENT'
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,TRUNC(SYSDATE)) ) < LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID)
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE);
SELECT
SUM(TRXRECS.CREDIT_AMOUNT) EDU_CESS_CREDIT
FROM
JAI_RGM_TRX_RECORDS TRXRECS,
JAI_RGM_TRX_REFS TRXREFS,
AP_INVOICES_ALL APA,
JAI_CMN_VENDOR_SITES JPVS,
PO_VENDOR_SITES_ALL PVSA,
JAI_INV_ITM_SETUPS JMSI
WHERE TRXREFS.REFERENCE_ID = TRXRECS.REFERENCE_ID
AND APA.CANCELLED_DATE IS NULL
AND APA.CANCELLED_BY IS NULL
AND APA.CANCELLED_AMOUNT IS NULL
AND JPVS.VENDOR_ID = APA.VENDOR_ID
AND JPVS.VENDOR_SITE_ID = APA.VENDOR_SITE_ID
AND APA.VENDOR_ID = PVSA.VENDOR_ID
AND APA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID
AND APA.INVOICE_ID = TRXREFS.INVOICE_ID
AND TRXREFS.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
AND TRXREFS.ITEM_ID = JMSI.INVENTORY_ITEM_ID
AND JMSI.ITEM_CLASS = CPV_ITEM_CLASS
AND TRXRECS.SOURCE in ( 'AP' )
AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRXRECS.REGIME_CODE = 'SERVICE'
AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(TRXRECS.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
,TRXRECS.ORGANIZATION_ID)
AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,TRXRECS.SERVICE_TYPE_CODE)
AND TRXREFS.TAX_TYPE = 'SERVICE_EDUCATION_CESS';
SELECT
SUM(JRTR.CREDIT_AMOUNT) EDU_CESS_CREDIT
FROM
JAI_RGM_TRX_RECORDS JRTR,
JAI_RGM_MANUAL_TRXS JMT,
PO_VENDOR_SITES_ALL PVSA
WHERE JRTR.SOURCE = 'MANUAL'
AND JRTR.REGIME_CODE = 'SERVICE'
AND JRTR.SOURCE_DOCUMENT_ID = JMT.TRANSACTION_NUMBER
AND PVSA.VENDOR_ID = JMT.PARTY_ID
AND PVSA.VENDOR_SITE_ID = JMT.PARTY_SITE_ID
AND JMT.ITEM_CLASS = CPV_ITEM_CLASS
AND JRTR.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
AND TRUNC(PRM_TODT)
AND TO_CHAR(JRTR.TRANSACTION_DATE
,'MON-YYYY') = LV_DATE
AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
,JRTR.ORGANIZATION_ID)
AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,JRTR.SERVICE_TYPE_CODE)
AND JRTR.TAX_TYPE = 'SERVICE_EDUCATION_CESS';
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0) DEBIT
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = 'Service'
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
,SYSDATE) )
AND ( NVL(PRM_TODT
,SYSDATE) )
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE)
AND TO_CHAR(CREATION_DATE
,'MON-YYYY') = LV_DATE
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID);
SELECT
NVL(SUM(DEBIT_AMOUNT)
,0) DEBIT
FROM
JAI_RGM_TRX_RECORDS
WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
AND REGIME_CODE = 'SERVICE'
AND TAX_TYPE = 'SERVICE_EDUCATION_CESS'
AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
AND ( NVL(TRUNC(CREATION_DATE)
,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
,SYSDATE) )
AND ( NVL(PRM_TODT
,SYSDATE) )
AND TO_CHAR(CREATION_DATE
,'MON-YYYY') = LV_DATE
AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
,SERVICE_TYPE_CODE)
AND ORGANIZATION_ID = NVL(PRM_ORG_ID
,ORGANIZATION_ID);
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
DESCRIPTION
INTO DESCRIPTION
FROM
JA_LOOKUPS
WHERE LOOKUP_TYPE = 'JAI_SERVICE_TYPE'
AND LOOKUP_CODE = SERVICE_TYPE_CODE;