This discussion is archived
2 Replies Latest reply: Nov 7, 2010 5:58 PM by 690175 RSS

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

690175 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points