2 Replies Latest reply on Jun 27, 2013 12:57 PM by Jim Smith

    Stored Procedure with in param not working

    c80d7ab0-5f40-4c8c-9213-46a5ea5ca8c9


      I have  a stored procedure that works  now I need to use an in Parameter . when I made modification now the stored proc says  "Source does not have a runnable target." I can't seem to figure out what is wrong wit it.

      using SQL developer  version 1.5.5

      here is the  stored proc

       

      CREATE OR REPLACE

      PROCEDURE STATEMENT_DOMESTIC_TEST

      ( s_date IN statement.statementdate%Type

      ) AS

      BEGIN

      INSERT INTO STATEMENT_DATA

                        (   STATEMENTNUMBER,

                            STATEMENTTOTAL,

                            STATEMENTDATE,

                            BALANCE_FORWARD,

                            CUSTID,

                            CUSTNAME,

                            STATEMENTPURCHASES,

                            STATEMENTPAYMENTS,

                            NOMAILNOPRINT,

                            SOLDTOCOUNTRYNAME,

                            CREDITZEROFLAG,

                            SOLDTOCOUNTRYCODE)

                 

                  SELECT  STATEMENTNUMBER,

                          STATEMENTTOTAL,

                          STATEMENTDATE,

                          BALANCE_FORWARD,

                          CUSTID,

                          CUSTNAME,

                          STATEMENTPURCHASES,

                          STATEMENTPAYMENTS,

                          NOMAILNOPRINT,

                          SOLDTOCOUNTRYNAME,

                          CREDITZEROFLAG,

                          SOLDTOCOUNTRYCODE

                              FROM  EROCKS.PS_JWF_STATEMENT

                                    WHERE (statementdate = s_date)and (STATEMENTTOTAL >0)

                                          and (SOLDTOCOUNTRYNAME is null or SOLDTOCOUNTRYNAME='US')

                                          and (TRIM(NOMAILNOPRINT) is null and NOMAILNOPRINT!='99');

       

      update STATEMENT_DATA H

                          set

                            ( H.REMTO_ZIP,

                              H.REMTO_CITY,

                              H.REMTO_STATE,

                              H.REMTO_MAILNAME,

                              H.REMTO_ADDR1,

                              H.REMTO_ADDR2,

                              H.REMTO_ADDR3,

                              H.SOLDTO_CITY,

                              H.SOLDTO_STATE,

                              H.SOLDTO_ZIP,

                              H.SOLDTO_ADDR1,

                              H.SOLDTO_ADDR2,

                              H.SOLDTO_ADDR3,

                              H.PHONE_PREFIX,

                              H.PHONE_NUMBER,

                              H.COMPANY_NUMBER,

                              H.STATEMENTMISC_CREDIT1,

                              H.STATEMENTMISC_CREDIT2)=

                        (SELECT J.REMTO_ZIP,

                                J.REMTO_CITY,

                                J.REMTO_STATE,

                                J.REMTO_MAILNAME,

                                J.REMTO_ADDR1,

                                J.REMTO__ADDR2,

                                J.REMTO_ADDR3,

                                J.SOLDTO_CITY,

                                J.SOLDTO_STATE,

                                J.SOLDTO_ZIP,

                                J.SOLDTO_ADDR1,

                                J.SOLDTO_ADDR2,

                                J.SOLDTO_ADDR3,

                                J.PHONE_PREFIX,

                                J.PHONE_NUMBER,

                                J.COMPANY_NUMBER,

                                J.STATEMENTMISC_CREDIT1,

                                J.STATEMENTMISC_CREDIT2

                                FROM STATEMENT_HEADER J

                                    WHERE  H.STATEMENTNUMBER= J.STATEMENTNUMBER);

       

      INSERT INTO EROCKS.STATEMENT_DATA_DETAILS

                        ( INVOICENUMBER,

                        STATEMENTNUMBER,

                        INVOICEDATE,

                        DOC_TYPE,

                        INVOICETOTAL,

                        PURCHASES,

                        PAYMENTS,

                        MISC_CREDIT1,

                        MISC_CREDIT2,

                        BUNUMBER,

                        BUNUMBER_RU,

                        REFERENCE_NUMBER,

                        DESCRIPTION, seq_number)

                         

                          SELECT  H.INVOICENUMBER,

                                  H.STATEMENTNUMBER,

                                  H.INVOICEDATE,

                                  H.DOC_TYPE,

                                  H.INVOICETOTAL,

                                  H.PURCHASES,

                                  H.PAYMENTS,

                                  H.MISC_CREDIT1,

                                  H.MISC_CREDIT2,

                                  H.BUNUMBER,

                                  H.BUNUMBER_RU,

                                  H.REFERENCE_NUMBER,

                                  H.DESCRIPTION,

                                  EROCKS.SEQ_STATEMENT.NEXTVAL

                                 

                          FROM STATEMENT  H

                              WHERE H.STATEMENTNUMBER IN

                                  (SELECT STATEMENTNUMBER

                                      FROM STATEMENT_DOMESTIC);

                                   

      END STATEMENT_DOMESTIC_TEST;