APPS.JA_JAINPLA_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 79

      SELECT
        NVL(CLOSING_BALANCE
           ,0)
      FROM
        JAI_CMN_RG_OTHERS
      WHERE SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND TAX_TYPE = P_TAX_TYPE
        AND SOURCE_REGISTER_ID = (
        SELECT
          MAX(A.REGISTER_ID)
        FROM
          JAI_CMN_RG_PLA_TRXS A,
          JAI_CMN_RG_OTHERS B
        WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID
          AND B.SOURCE_REGISTER = CP_SOURCE_REGISTER
          AND B.TAX_TYPE = P_TAX_TYPE
          AND A.TRANSACTION_DATE = (
          SELECT
            MAX(C.TRANSACTION_DATE)
          FROM
            JAI_CMN_RG_PLA_TRXS C,
            JAI_CMN_RG_OTHERS D
          WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID
            AND D.SOURCE_REGISTER = CP_SOURCE_REGISTER
            AND D.TAX_TYPE = P_TAX_TYPE
            AND TRUNC(C.TRANSACTION_DATE) < TRUNC(P_TRN_FROM_DATE)
            AND C.ORGANIZATION_ID = P_ORG_ID
            AND C.LOCATION_ID = P_LOC_ID ) );

Line: 116

        SELECT
          SUM(NVL(CR_BASIC_ED
                 ,0)) - SUM(NVL(DR_BASIC_ED
                 ,0)),
          SUM(NVL(CR_ADDITIONAL_ED
                 ,0)) - SUM(NVL(DR_ADDITIONAL_ED
                 ,0)),
          SUM(NVL(CR_OTHER_ED
                 ,0)) - SUM(NVL(DR_OTHER_ED
                 ,0))
        INTO P_BED_OPEN_BAL,P_AED_OPEN_BAL,P_SED_OPEN_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID
          AND TRUNC(CREATION_DATE) <= TRUNC(P_TRN_FROM_DATE) - 1;

Line: 274

      SELECT
        B.ITEM_TARIFF
      FROM
        JAI_INV_ITM_SETUPS B
      WHERE B.INVENTORY_ITEM_ID = CF_CETFORMULA.INVENTORY_ITEM_ID
        AND B.ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 300

      SELECT
        A.EC_CODE
      FROM
        JAI_CMN_CUS_ADDRESSES A,
        HZ_CUST_SITE_USES_ALL C
      WHERE ( A.ADDRESS_ID = C.CUST_ACCT_SITE_ID
      OR ( A.ADDRESS_ID is NULL
        AND C.CUST_ACCT_SITE_ID is NULL ) )
        AND A.CUSTOMER_ID = VENDOR_ID
        AND C.SITE_USE_ID = VENDOR_SITE_ID;

Line: 311

      SELECT
        EC_CODE
      FROM
        JAI_CMN_VENDOR_SITES
      WHERE VENDOR_ID = CF_1FORMULA0032.VENDOR_ID
        AND ROWNUM = 1
        AND VENDOR_SITE_ID = CF_1FORMULA0032.VENDOR_SITE_ID;

Line: 319

      SELECT
        A.EC_CODE
      FROM
        JAI_CMN_INVENTORY_ORGS A
      WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
        AND ROWNUM = 1
        AND A.LOCATION_ID = P_LOCATION_ID;

Line: 377

      SELECT
        OPENING_BALANCE
      FROM
        JAI_CMN_RG_PLA_TRXS
      WHERE TRUNC(CREATION_DATE) >= TRUNC(FRM_DT)
        AND REGISTER_ID = (
        SELECT
          MIN(REGISTER_ID)
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) >= TRUNC(FRM_DT) );

Line: 389

      SELECT
        OPENING_BALANCE
      FROM
        JAI_CMN_RG_PLA_TRXS
      WHERE REGISTER_ID = (
        SELECT
          MIN(REGISTER_ID)
        FROM
          JAI_CMN_RG_PLA_TRXS );

Line: 419

        SELECT
          NVL(SUM(CR_BASIC_ED)
             ,0) - NVL(SUM(DR_BASIC_ED)
             ,0)
        INTO BASIC_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
          AND ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID;

Line: 445

        SELECT
          NVL(SUM(CR_ADDITIONAL_ED)
             ,0) - NVL(SUM(DR_ADDITIONAL_ED)
             ,0)
        INTO ADDITIONAL_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
          AND ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID;

Line: 472

        SELECT
          NVL(SUM(CR_OTHER_ED)
             ,0) - NVL(SUM(DR_OTHER_ED)
             ,0)
        INTO OTHER_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
          AND ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID;

Line: 500

      FOR C_FETCH_ACK_DATE IN (SELECT
                                 ACK_RECVD_DATE
                               FROM
                                 JAI_CMN_RG_PLA_HDRS A,
                                 JAI_CMN_RG_PLA_TRXS B
                               WHERE A.PLA_RG_SR_NO = B.SLNO
                                 AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
                                 AND A.LOCATION_ID = B.LOCATION_ID
                                 AND B.REGISTER_ID = CF_ACK_RECD_DATEFORMULA.REGISTER_ID
                                 AND B.SLNO = CF_ACK_RECD_DATEFORMULA.SLNO) LOOP
        V_ACK_DATE := C_FETCH_ACK_DATE.ACK_RECVD_DATE;

Line: 525

      SELECT
        EC_CODE
      FROM
        JAI_CMN_INVENTORY_ORGS
      WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
        AND LOCATION_ID = P_LOCATION_ID;

Line: 532

      SELECT
        EC_CODE
      FROM
        JAI_CMN_INVENTORY_ORGS
      WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
        AND LOCATION_ID = 0;

Line: 557

      SELECT
        CONCURRENT_PROGRAM_ID,
        NVL(ENABLE_TRACE
           ,'N')
      FROM
        FND_CONCURRENT_REQUESTS
      WHERE REQUEST_ID = P_REQUEST_ID;

Line: 565

      SELECT
        A.SID,
        A.SERIAL#,
        B.SPID
      FROM
        V$SESSION A,
        V$PROCESS B
      WHERE AUDSID = USERENV('SESSIONID')
        AND A.PADDR = B.ADDR;

Line: 575

      SELECT
        NAME
      FROM
        V$DATABASE;

Line: 626

      SELECT
        CLOSING_BALANCE
      FROM
        JAI_CMN_RG_OTHERS
      WHERE SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND TAX_TYPE = CP_TAX_TYPE
        AND SOURCE_REGISTER_ID = (
        SELECT
          MAX(A.REGISTER_ID)
        FROM
          JAI_CMN_RG_PLA_TRXS A,
          JAI_CMN_RG_OTHERS B
        WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID
          AND B.SOURCE_REGISTER = CP_SOURCE_REGISTER
          AND B.TAX_TYPE = CP_TAX_TYPE
          AND A.TRANSACTION_DATE = (
          SELECT
            MAX(C.TRANSACTION_DATE)
          FROM
            JAI_CMN_RG_PLA_TRXS C,
            JAI_CMN_RG_OTHERS D
          WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID
            AND D.SOURCE_REGISTER = CP_SOURCE_REGISTER
            AND D.TAX_TYPE = CP_TAX_TYPE
            AND TRUNC(C.TRANSACTION_DATE) < TRUNC(P_TRN_FRM_DATE)
            AND C.ORGANIZATION_ID = P_ORG_ID
            AND C.LOCATION_ID = P_LOC_ID ) );

Line: 668

      SELECT
        NVL(SUM(CREDIT)
           ,0) - NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;

Line: 708

      SELECT
        NVL(SUM(CREDIT)
           ,0) - NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = 'PLA'
        AND SOURCE_TYPE = 2
        AND RG_OTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' );

Line: 733

      SELECT
        NVL(SUM(CREDIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;

Line: 742

      SELECT
        NVL(SUM(CREDIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = 'PLA'
        AND RG_OTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' );

Line: 769

      SELECT
        NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;

Line: 778

      SELECT
        NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = 'PLA'
        AND RG_OTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' );

Line: 806

      SELECT
        NVL(SUM(CREDIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;

Line: 815

      SELECT
        NVL(SUM(CREDIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = 'PLA'
        AND RG_OTH.TAX_TYPE IN ( 'CVD_SH_EDU_CESS' );

Line: 842

      SELECT
        NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;

Line: 851

      SELECT
        NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = 'PLA'
        AND RG_OTH.TAX_TYPE IN ( 'CVD_SH_EDU_CESS' );

Line: 879

      SELECT
        NVL(SUM(CREDIT)
           ,0) - NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;

Line: 889

      SELECT
        NVL(SUM(CREDIT)
           ,0) - NVL(SUM(DEBIT)
           ,0)
      FROM
        JAI_CMN_RG_OTHERS RG_OTH
      WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RG_OTH.SOURCE_REGISTER = 'PLA'
        AND RG_OTH.TAX_TYPE IN ( 'CVD_SH_EDU_CESS' );

Line: 929

      SELECT
        CLOSING_BALANCE
      FROM
        JAI_CMN_RG_OTHERS
      WHERE SOURCE_REGISTER = CP_SOURCE_REGISTER
        AND TAX_TYPE = CP_TAX_TYPE
        AND SOURCE_REGISTER_ID = (
        SELECT
          MAX(A.REGISTER_ID)
        FROM
          JAI_CMN_RG_PLA_TRXS A,
          JAI_CMN_RG_OTHERS B
        WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID
          AND B.SOURCE_REGISTER = CP_SOURCE_REGISTER
          AND B.TAX_TYPE = CP_TAX_TYPE
          AND A.TRANSACTION_DATE = (
          SELECT
            MAX(C.TRANSACTION_DATE)
          FROM
            JAI_CMN_RG_PLA_TRXS C,
            JAI_CMN_RG_OTHERS D
          WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID
            AND D.SOURCE_REGISTER = CP_SOURCE_REGISTER
            AND D.TAX_TYPE = CP_TAX_TYPE
            AND TRUNC(C.TRANSACTION_DATE) < TRUNC(P_TRN_FRM_DATE)
            AND C.ORGANIZATION_ID = P_ORG_ID
            AND C.LOCATION_ID = P_LOC_ID ) );

Line: 973

      SELECT
        REGISTER_ID
      FROM
        JAI_CMN_RG_ROUND_HDRS
      WHERE ROUNDING_ID = CP_ROUNDING_ID;

Line: 979

      SELECT
        - NVL(CR_BASIC_ED
           ,0) + NVL(DR_BASIC_ED
           ,0) BASIC_ED,
        - NVL(CR_ADDITIONAL_ED
           ,0) + NVL(DR_ADDITIONAL_ED
           ,0) ADDITIONAL_ED,
        - NVL(CR_OTHER_ED
           ,0) + NVL(DR_OTHER_ED
           ,0) OTHER_ED
      FROM
        JAI_CMN_RG_PLA_TRXS
      WHERE REGISTER_ID = CP_REGISTER_ID;

Line: 993

      SELECT
        SUM(NVL(DEBIT
               ,0) - NVL(CREDIT
               ,0))
      FROM
        JAI_CMN_RG_OTHERS
      WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
        AND SOURCE_TYPE = CP_SOURCE_TYPE
        AND TAX_TYPE = P_TAX_TYPE;

Line: 1083

      SELECT
        JCFY.FIN_YEAR
      FROM
        JAI_CMN_FIN_YEARS JCFY
      WHERE JCFY.ORGANIZATION_ID = CP_ORG_ID
        AND CP_FROM_DATE BETWEEN JCFY.FIN_YEAR_START_DATE
        AND JCFY.FIN_YEAR_END_DATE;

Line: 1091

      SELECT
        JCRPT.REGISTER_ID
      FROM
        JAI_CMN_RG_PLA_TRXS JCRPT
      WHERE JCRPT.LOCATION_ID = P_LOCATION_ID
        AND JCRPT.ORGANIZATION_ID = P_ORGANIZATION_ID
        AND JCRPT.SLNO = (
        SELECT
          MAX(JCRPT1.SLNO)
        FROM
          JAI_CMN_RG_PLA_TRXS JCRPT1
        WHERE JCRPT1.LOCATION_ID = P_LOCATION_ID
          AND JCRPT1.ORGANIZATION_ID = P_ORGANIZATION_ID
          AND JCRPT1.CREATION_DATE < P_TRN_FROM_DATE
          AND JCRPT1.FIN_YEAR = CP_FIN_YEAR
          AND EXISTS (
          SELECT
            1
          FROM
            JAI_CMN_RG_OTHERS JCRG
          WHERE JCRG.SOURCE_REGISTER_ID = JCRPT1.REGISTER_ID
            AND JCRG.SOURCE_TYPE = 2
            AND JCRG.SOURCE_REGISTER = 'PLA'
            AND JCRG.TAX_TYPE = CP_TAX_TYPE ) )
        AND JCRPT.FIN_YEAR = CP_FIN_YEAR;

Line: 1117

      SELECT
        CLOSING_BALANCE
      FROM
        JAI_CMN_RG_OTHERS
      WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
        AND TAX_TYPE = CP_TAX_TYPE
        AND SOURCE_REGISTER = 'PLA';

Line: 1140

        SELECT
          NVL(SUM(CR_BASIC_ED)
             ,0) - NVL(SUM(DR_BASIC_ED)
             ,0)
        INTO BASIC_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
          AND ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID;

Line: 1159

        SELECT
          NVL(SUM(CR_ADDITIONAL_ED)
             ,0) - NVL(SUM(DR_ADDITIONAL_ED)
             ,0)
        INTO ADDITIONAL_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
          AND ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID;

Line: 1178

        SELECT
          NVL(SUM(CR_OTHER_ED)
             ,0) - NVL(SUM(DR_OTHER_ED)
             ,0)
        INTO OTHER_BAL
        FROM
          JAI_CMN_RG_PLA_TRXS
        WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
          AND ORGANIZATION_ID = P_ORGANIZATION_ID
          AND LOCATION_ID = P_LOCATION_ID;