2 Replies Latest reply: Nov 7, 2010 7:58 PM by 690175 RSS

    How to pass 4 parameter to substr in host  concurrent program oracleapps

    690175
      Hi,
      Can anyone help me to solve my issue. i have written shell script which execute sql query. i dont know how to pass parameter from shell script i have given query below


      . /appstest/cmwprodappl/APPSORA.env
      sqlplus -s <<EOF
      apps/butterfly@TEST
      conn apps/butterfly@TEST
      spool off
      col spoolname new_value spoolname
      select 'sms_'||to_char(sysdate,'DDMMYYHH24MISS') spoolname from dual;
      spool /appstest/cmwprodappl/fnd/11.5.0/bin/&spoolname
      SELECT D.ACCOUNT_NUMBER cust_number,mobile_number,SUM(amount_due_remaining) ARREAR
      FROM AR_PAYMENT_SCHEDULES_ALL A,
      HZ_CUST_ACCOUNTS D , HZ_PARTIES C, cmw_bc_mobile_number b
      WHERE
      A.CUSTOMER_ID = D.CUST_ACCOUNT_ID
      AND C.PARTY_ID = D.PARTY_ID
      AND SUBSTR(d.account_number,1,15) = SUBSTR(b.customer_number,1,15)
      --AND SUBSTR(trx_number,1,6) < '2007'
      AND SUBSTR(D.ACCOUNT_NUMBER,1,2)=NVL('$5' ,SUBSTR(D.ACCOUNT_NUMBER,1,2))
      AND SUBSTR(D.ACCOUNT_NUMBER,3,3)=NVL('$6' ,SUBSTR(D.ACCOUNT_NUMBER,3,3))
      AND amount_due_remaining>0
      AND mobile_number IS NOT NULL
      GROUP BY D.ACCOUNT_NUMBER,mobile_number HAVING SUM(amount_due_remaining) BETWEEN NVL('$7',0) AND NVL('$8',100000000);
      spool off
      quit;
      EOF

      script execute sucessfully but it displays all the record it means it is not displaying based on the parameter $5 to $8 are my paramter