APPS.JA_JAIN23D_XMLP_PKG SQL Statements

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

Line: 8

      SELECT
        SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' '
	|| F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
              ,1
              ,255)
      FROM
        HZ_PARTIES E,
        HZ_CUST_ACCOUNTS HZCA,
        JAI_CMN_CUS_ADDRESSES F,
        HZ_LOCATIONS G,
        HZ_PARTY_SITES HZPS,
        HZ_CUST_ACCT_SITES_ALL HZCAS,
        HZ_CUST_SITE_USES_ALL H
      WHERE E.PARTY_ID = HZCA.PARTY_ID
        AND HZCA.CUST_ACCOUNT_ID = F.CUSTOMER_ID
        AND F.ADDRESS_ID = H.CUST_ACCT_SITE_ID
        AND G.LOCATION_ID = HZPS.LOCATION_ID
        AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
        AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
        AND F.CUSTOMER_ID = P_CUSTOMER_ID
        AND H.CUST_ACCT_SITE_ID = P_ADDRESS_ID;

Line: 30

      SELECT
        PARTY_NAME CUSTOMER_NAME
      FROM
        HZ_PARTIES HZP,
        HZ_CUST_ACCOUNTS HZCA
      WHERE HZP.PARTY_ID = HZCA.PARTY_ID
        AND HZCA.CUST_ACCOUNT_ID = P_CUSTOMER_ID;

Line: 38

      SELECT
        SUBSTR(B.VENDOR_NAME || ' ' || C.ADDRESS_LINE1 || ' ' || ADDRESS_LINE2 || ' ' || ADDRESS_LINE3 || ' ' || D.EXCISE_DUTY_RANGE || ' ' || D.EXCISE_DUTY_DIVISION || ' ' || D.EXCISE_DUTY_COMM
              ,1
              ,255)
      FROM
        JAI_CMN_RG_23D_TRXS A,
        PO_VENDORS B,
        PO_VENDOR_SITES_ALL C,
        JAI_CMN_VENDOR_SITES D
      WHERE A.VENDOR_ID = B.VENDOR_ID
        AND A.VENDOR_ID = C.VENDOR_ID
        AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
        AND A.VENDOR_ID = D.VENDOR_ID
        AND A.VENDOR_SITE_ID = D.VENDOR_SITE_ID
        AND C.VENDOR_SITE_ID = D.VENDOR_SITE_ID
        AND A.VENDOR_ID = P_VENDOR_ID
        AND A.VENDOR_SITE_ID = P_VENDOR_SITE_ID;

Line: 56

      SELECT
        SUBSTR(A.NAME || ' ' || ((SUBSTR(ADDRESS_LINE_1
                     ,1
                     ,30)) || (SUBSTR(ADDRESS_LINE_2
                     ,1
                     ,20)) || (SUBSTR(ADDRESS_LINE_3
                     ,1
                     ,10)))
              ,1
              ,255)
      FROM
        HR_ALL_ORGANIZATION_UNITS A,
        HR_LOCATIONS B
      WHERE A.ORGANIZATION_ID = B.INVENTORY_ORGANIZATION_ID
        AND A.ORGANIZATION_ID = ABS(P_CUSTOMER_ID)
        AND B.LOCATION_ID = ABS(P_SHIP_TO_SITE_ID);

Line: 73

      SELECT
        RCV.SHIPMENT_LINE_ID,
        RCV.CUSTOMER_ID,
        RCV.CUSTOMER_SITE_ID
      FROM
        RCV_TRANSACTIONS RCV,
        JAI_CMN_RG_23D_TRXS D
      WHERE RCV.TRANSACTION_ID = D.RECEIPT_REF
        AND D.REGISTER_ID = P_REGISTER_ID;

Line: 83

      SELECT
        CUSTOMER_ID,
        CUSTOMER_SITE_ID
      FROM
        RCV_TRANSACTIONS
      WHERE SHIPMENT_LINE_ID = P_SHIPMENT_LINE_ID
        AND TRANSACTION_TYPE = CP_TRANSACTION_TYPE;

Line: 91

      SELECT
        SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' ' ||
	F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
              ,1
              ,255)
      FROM
        HZ_PARTIES E,
        HZ_CUST_ACCOUNTS HZCA,
        JAI_CMN_CUS_ADDRESSES F,
        HZ_LOCATIONS G,
        HZ_PARTY_SITES HZPS,
        HZ_CUST_ACCT_SITES_ALL HZCAS,
        HZ_CUST_SITE_USES_ALL H
      WHERE HZCA.CUST_ACCOUNT_ID = P_CUSTOMER_ID
        AND E.PARTY_ID = HZCA.PARTY_ID
        AND H.SITE_USE_ID = P_CUSTOMER_SITE_ID
        AND F.CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID
        AND F.ADDRESS_ID = H.CUST_ACCT_SITE_ID
        AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
        AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
        AND G.LOCATION_ID = HZPS.LOCATION_ID;

Line: 113

      SELECT
        NVL(CUSTOMER_ID
           ,0),
        NVL(SHIP_TO_SITE_ID
           ,0),
        NVL(VENDOR_ID
           ,0),
        NVL(VENDOR_SITE_ID
           ,0)
      FROM
        JAI_CMN_RG_23D_TRXS
      WHERE REGISTER_ID = P_REGISTER_ID;

Line: 199

      SELECT
        SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' ' ||
	F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
              ,1
              ,255)
      FROM
        HZ_PARTIES E,
        HZ_CUST_ACCOUNTS HZCA,
        JAI_CMN_CUS_ADDRESSES F,
        HZ_LOCATIONS G,
        HZ_PARTY_SITES HZPS,
        HZ_CUST_ACCT_SITES_ALL HZCAS,
        HZ_CUST_SITE_USES_ALL H
      WHERE E.PARTY_ID = HZCA.PARTY_ID
        AND HZCA.CUST_ACCOUNT_ID = F.CUSTOMER_ID
        AND F.ADDRESS_ID = HZCAS.CUST_ACCT_SITE_ID
        AND G.LOCATION_ID = HZPS.PARTY_SITE_ID
        AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
        AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
        AND F.CUSTOMER_ID = P_CUSTOMER_ID
        AND H.CUST_ACCT_SITE_ID = P_ADDRESS_ID;

Line: 221

      SELECT
        SUBSTR(B.VENDOR_NAME || ' ' || C.ADDRESS_LINE1 || ' ' || ADDRESS_LINE2 || ' ' || ADDRESS_LINE3 || ' ' || D.EXCISE_DUTY_RANGE || ' ' || D.EXCISE_DUTY_DIVISION || ' ' || D.EXCISE_DUTY_COMM
              ,1
              ,255)
      FROM
        JAI_CMN_RG_23D_TRXS A,
        PO_VENDORS B,
        PO_VENDOR_SITES_ALL C,
        JAI_CMN_VENDOR_SITES D
      WHERE A.VENDOR_ID = B.VENDOR_ID
        AND A.VENDOR_ID = C.VENDOR_ID
        AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
        AND A.VENDOR_ID = D.VENDOR_ID
        AND A.VENDOR_SITE_ID = D.VENDOR_SITE_ID
        AND C.VENDOR_SITE_ID = D.VENDOR_SITE_ID
        AND A.VENDOR_ID = P_VENDOR_ID
        AND A.VENDOR_SITE_ID = P_VENDOR_SITE_ID;

Line: 239

      SELECT
        SUBSTR(A.NAME || ' ' || ((SUBSTR(ADDRESS_LINE_1
                     ,1
                     ,30)) || (SUBSTR(ADDRESS_LINE_2
                     ,1
                     ,20)) || (SUBSTR(ADDRESS_LINE_3
                     ,1
                     ,10)))
              ,1
              ,255)
      FROM
        HR_ALL_ORGANIZATION_UNITS A,
        HR_LOCATIONS B
      WHERE A.ORGANIZATION_ID = B.INVENTORY_ORGANIZATION_ID
        AND A.ORGANIZATION_ID = ABS(P_CUSTOMER_ID)
        AND B.LOCATION_ID = ABS(P_SHIP_TO_SITE_ID);

Line: 256

      SELECT
        NVL(CUSTOMER_ID
           ,0),
        NVL(SHIP_TO_SITE_ID
           ,0),
        NVL(VENDOR_ID
           ,0),
        NVL(VENDOR_SITE_ID
           ,0)
      FROM
        JAI_CMN_RG_23D_TRXS
      WHERE REGISTER_ID = P_REGISTER_ID;

Line: 309

      SELECT
        SET_OF_BOOKS_ID
      FROM
        ORG_ORGANIZATION_DEFINITIONS
      WHERE ORGANIZATION_ID = INV_ORG_ID;

Line: 315

      SELECT
        NAME
      FROM
        GL_SETS_OF_BOOKS
      WHERE SET_OF_BOOKS_ID = SOB_ID;

Line: 336

      SELECT
        NAME
      FROM
        HR_ALL_ORGANIZATION_UNITS
      WHERE ORGANIZATION_ID = ORG_ID;

Line: 352

      SELECT
        DESCRIPTION
      FROM
        HR_LOCATIONS
      WHERE LOCATION_ID = LOC_ID;

Line: 368

      SELECT
        CONCATENATED_SEGMENTS
      FROM
        MTL_SYSTEM_ITEMS_KFV
      WHERE INVENTORY_ITEM_ID = ID;

Line: 387

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

Line: 395

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

Line: 405

      SELECT
        NVL(NAME
           ,'')
      FROM
        V$DATABASE;

Line: 458

      SELECT
        SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' ' ||
	F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
              ,1
              ,255)
      FROM
        HZ_PARTIES E,
        HZ_CUST_ACCOUNTS HZCA,
        JAI_CMN_CUS_ADDRESSES F,
        HZ_LOCATIONS G,
        HZ_PARTY_SITES HZPS,
        HZ_CUST_ACCT_SITES_ALL HZCAS,
        HZ_CUST_SITE_USES_ALL H
      WHERE E.PARTY_ID = HZCA.CUST_ACCOUNT_ID
        AND HZCA.CUST_ACCOUNT_ID = F.CUSTOMER_ID
        AND F.ADDRESS_ID = HZCAS.CUST_ACCT_SITE_ID
        AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
        AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
        AND G.LOCATION_ID = HZPS.LOCATION_ID
        AND F.CUSTOMER_ID = P_CUSTOMER_ID
        AND H.CUST_ACCT_SITE_ID = P_ADDRESS_ID;

Line: 480

      SELECT
        SUBSTR(A.NAME || ' ' || ((SUBSTR(ADDRESS_LINE_1
                     ,1
                     ,30)) || (SUBSTR(ADDRESS_LINE_2
                     ,1
                     ,20)) || (SUBSTR(ADDRESS_LINE_3
                     ,1
                     ,10)))
              ,1
              ,255)
      FROM
        HR_ALL_ORGANIZATION_UNITS A,
        HR_LOCATIONS B
      WHERE A.ORGANIZATION_ID = B.INVENTORY_ORGANIZATION_ID
        AND A.ORGANIZATION_ID = ABS(P_VENDOR_ID)
        AND B.LOCATION_ID = ABS(P_VENDOR_SITE_ID);

Line: 497

      SELECT
        SUBSTR(B.VENDOR_NAME || ' ' || C.ADDRESS_LINE1 || ' ' || ADDRESS_LINE2 || ' ' || ADDRESS_LINE3 || ' ' || D.EXCISE_DUTY_RANGE || ' ' || D.EXCISE_DUTY_DIVISION || ' ' || D.EXCISE_DUTY_COMM
              ,1
              ,255),
        D.VENDOR_TYPE
      FROM
        JAI_CMN_RG_23D_TRXS A,
        PO_VENDORS B,
        PO_VENDOR_SITES_ALL C,
        JAI_CMN_VENDOR_SITES D
      WHERE A.VENDOR_ID = B.VENDOR_ID
        AND A.VENDOR_ID = C.VENDOR_ID
        AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
        AND A.VENDOR_ID = D.VENDOR_ID
        AND A.VENDOR_SITE_ID = D.VENDOR_SITE_ID
        AND C.VENDOR_SITE_ID = D.VENDOR_SITE_ID
        AND A.VENDOR_ID = P_VENDOR_ID
        AND A.VENDOR_SITE_ID = P_VENDOR_SITE_ID;

Line: 516

      SELECT
        NVL(CUSTOMER_ID
           ,0),
        NVL(SHIP_TO_SITE_ID
           ,0),
        NVL(VENDOR_ID
           ,0),
        NVL(VENDOR_SITE_ID
           ,0)
      FROM
        JAI_CMN_RG_23D_TRXS
      WHERE REGISTER_ID = P_REGISTER_ID;

Line: 531

      SELECT
        DECODE(TRADING
              ,'N'
              ,'Manufacturer'
              ,'Dealer')
      FROM
        JAI_CMN_INVENTORY_ORGS
      WHERE ORGANIZATION_ID = ABS(P_VENDOR_ID)
        AND LOCATION_ID = ABS(P_VENDOR_SITE_ID);

Line: 599

      SELECT
        QTY_TO_ADJUST
      FROM
        JAI_CMN_RG_23D_TRXS
      WHERE REGISTER_ID = P_REGISTER_ID;

Line: 605

      SELECT
        SUM(QUANTITY_APPLIED)
      FROM
        JAI_CMN_MATCH_RECEIPTS
      WHERE RECEIPT_ID = P_REGISTER_ID;

Line: 611

      SELECT
        NVL(SUM(QTY_TO_ADJUST)
           ,0)
      FROM
        JAI_CMN_RG_23D_TRXS
      WHERE REGISTER_ID = P_REGISTER_ID;

Line: 618

      SELECT
        NVL(SUM(QUANTITY_APPLIED)
           ,0)
      FROM
        JAI_CMN_MATCH_RECEIPTS
      WHERE RECEIPT_ID IN (
        SELECT
          REGISTER_ID
        FROM
          JAI_CMN_RG_23D_TRXS A
        WHERE ( A.ORGANIZATION_ID , A.LOCATION_ID , A.INVENTORY_ITEM_ID , A.COMM_INVOICE_NO , A.FIN_YEAR ) IN (
          SELECT
            ORGANIZATION_ID,
            LOCATION_ID,
            INVENTORY_ITEM_ID,
            COMM_INVOICE_NO,
            FIN_YEAR
          FROM
            JAI_CMN_RG_23D_TRXS
          WHERE REGISTER_ID = P_REGISTER_ID )
          AND TRANSACTION_TYPE = CP_TRANSACTION_TYPE );

Line: 703

      SELECT
        SUM(DEBIT) TAX_VAL
      FROM
        JAI_CMN_RG_OTHERS RGOTH
      WHERE RGOTH.SOURCE_TYPE = 3
        AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RGOTH.TAX_TYPE in ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' );

Line: 734

      SELECT
        SUM(CREDIT) TAX_VAL
      FROM
        JAI_CMN_RG_OTHERS RGOTH
      WHERE RGOTH.SOURCE_TYPE = 3
        AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RGOTH.TAX_TYPE in ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' );

Line: 771

      SELECT
        SUM(DEBIT) TAX_VAL
      FROM
        JAI_CMN_RG_OTHERS RGOTH
      WHERE RGOTH.SOURCE_TYPE = 3
        AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RGOTH.TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );

Line: 802

      SELECT
        SUM(CREDIT) TAX_VAL
      FROM
        JAI_CMN_RG_OTHERS RGOTH
      WHERE RGOTH.SOURCE_TYPE = 3
        AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
        AND RGOTH.TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );