set -e
set +x

#Executes SVR parameter scripts Sets all the application specific properties
 . $SCRIPTS_PATH/svrparam.sh

  $ORACLE_HOME/bin/sqlplus -s ${SOLV_DB_USER}/${SOLV_DB_PASS}@${SOLV_DB_SERVER}<<EOF
        set echo on
        set termout on
        spool ${SVR_LOG_PATH}/solv_outbound_count_check.txt
         --das staging count
            SELECT COUNT(1)
                FROM das_security_stg2
                WHERE solv_ind='Y';

                --unique asset id count

           SELECT COUNT(1)
           FROM (SELECT DISTINCT sec_ssc_id
                 FROM holding_dynamic
                 WHERE as_of_dt = (SELECT TO_DATE(pkg_solv_common.fnc_get_valuation_dt,'yyyymmdd')
                                   FROM dual));


                --security outbound count
                SELECT COUNT(1)
                FROM v_security_outbound;
spool off
EOF

CONNECT '&1'/'&2'/@'&3'
spool '&4'/solv_outbound_count_check.log
set feedback off
set echo on
set pagesize 0
set linesize 100
set space 0
set serveroutput on
--das staging COUNT
SELECT COUNT(1)
FROM   das_security_stg2
WHERE  solv_ind = 'Y';

--unique asset id COUNT
SELECT COUNT(1)
FROM   (SELECT DISTINCT sec_ssc_id
        FROM   holding_dynamic
        WHERE  as_of_dt = (SELECT  TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
                           FROM   DUAL));

--security outbound COUNT
SELECT COUNT(1)
FROM   v_security_outbound;

--security dynamic COUNT
SELECT COUNT(1)
FROM   (SELECT DISTINCT sec.sec_ssc_id
        FROM   security_dynamic secdy,
               sol_security sec
        WHERE  secdy.security_ky  = sec.security_ky
               AND secdy.as_of_dt = (SELECT TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
                                     FROM   DUAL));

#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH

set -e

#Executes SVR parameter scripts Sets all the application specific properties
 . $SCRIPTS_PATH/svrparam.sh

$ORACLE_HOME/bin/sqlplus -s  /nolog @${SCRIPTS_PATH}/solv_outbound_veri_query.sql \
${SOLV_DB_USER} ${SOLV_DB_PASS} ${SOLV_DB_SERVER} ${SVR_LOG_PATH}


SUB="Solvency outbound file count of current month"
MESSAGE="${SVR_LOG_PATH}/solv_outbound_count_check.txt"
              /bin/mailx -s "$SUB" "$SOL2_SUPPORT_GRP_EMAIL" < "$MESSAGE"

SQL>         connect solvency/solvency@O03GPA0
Connected.
SQL> 		--das staging count
SQL> 		SELECT COUNT(1)
  2  		FROM   das_security_stg2
  3  		WHERE  solv_ind = 'Y';
!

SQL> 
SQL> 		--unique asset id count
SQL> 		SELECT COUNT(1)
  2  		FROM   (SELECT DISTINCT sec_ssc_id
  3  				FROM   holding_dynamic
  4  				WHERE  as_of_dt = (SELECT  TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
  5  						   FROM   DUAL));
!

--unique asset id count
SELECT COUNT(1)
FROM   (SELECT DISTINCT sec_ssc_id
FROM   holding_dynamic
WHERE  as_of_dt = (SELECT  TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
 FROM   DUAL));

---
0