APEX

Hello:

I have the following situation. When I create an organization, a record is inserted into the following table, AGREEMENTS, and populates four fields.

I have an update form which selects the organization from the AGREEMENTS table so the user can populate the rest of the table. In addition, on this form, there is a drop-down box which allows the user to select the name of a legal document along with the version of the document in which the user needs to select. This select list is created via an LOV joining three tables. The item name for this select list is :P6_DOCUMENT.

The code for the LOV is:

SELECT S.DOC_NAME||' - '|| O.VERSION_NO AS DOC, O.ORG_DOC_CURR_KEY
FROM SUPPORTING_DOCS S,
     ORG_DOC_CURR_VER O,
     AGREEMENTS H
WHERE
    S.DOC_TYPE = 'HISA'
 AND S.SUPPORTING_DOC_KEY = O.SUPPORTING_DOC_KEY
 AND H.ORG_KEY_LE = O.ORG_KEY
 AND O.ORG_KEY=:P6_ORG_KEY_LE

When the user completes the form, the SUBMIT process is a PL/SQL block consisting of an UPDATE statement to update the AGREEMENTS table based on the selected organization and an INSERT statement to insert a record into the AGREEMENTS_DOC table to store the value stored in :P7_DOCUMENT.

Ok, now here is where my problem starts.

When I first bring up the form and I select the organization I want to update, I click the Search button to find the organization and I receive the following error message: ORA-01722 Invalid Number.

At this point all I'm doing is a basic search. There is no insert/update or anything going on. I'm not understanding why I would be receiving this error message.

The search is based on the database column ORG_KEY_LE whose datatype is NUMBER.

In my application, the item assigned to ORG_KEY_LE is P6_ORG_KEY_LE.

I have a PL/SQL block process created (On Load-Before Header) in the Page Rendering area of my page definition. The PL/SQL code that is written is:

BEGIN
IF :P6_SEARCH_ORG != '0' THEN
:P6_ORG_KEY_LE := :P6_SEARCH_ORG;
END IF;
END;

I then have an Item created, :P6_SEARCH_ORG, which is a Select List. In the LOV portion of the page for this item, I have the following:

select ORG_KEY_LE display_value, ORG_KEY_LE return_value 
from AGREEMENTS
order by 1

The reason for using this table is because this table contains the newly created organization which needs to be updated with the remaining columns of data.

I then have a Search button in the Button area which has the following settings:
Button Position: Region Template Position #CHANGE#.
Condition Type: Value of Item in Express 1 is NULL.
Expression 1: :P6_ORG_KEY_LE.

To troubleshoot this problem, I created two pages, one page to do the UPDATE and the second page to do the INSERT.
The SEARCH functionality in both pages are identical.

When I run my UPDATE page, which only involves updating the missing fields, the process works. I have my search box, I'm able to search for the organization, make my updates, and I'm good.

When I run my INSERT page, which involves inserting the record with the assigned document, I receive the error message after I click the SEARCH button. In order to INSERT the record into this table, I first need to SELECT the organization that was UPDATED in the AGREEMENTS table (using the UPDATE page described in above paragraph). When I select the organization, the user can then assign the appropriate legal document to the organization and insert the record into the AGREEMENTS_DOC table.

Can someone help me with this error message? I'm not sure why I am able to perform my SEARCH on a page with the UPDATE statement, not able to perform the SEARCH on the page with my INSERT statement, and not be able to perform the SEARCH on the page that combines the UPDATE and INSERT statements.

I did some more troubleshooting and I do believe my SUBMIT process which contains the INSERT statement is the issue. I created a fourth page which doesn't have a SUBMIT process. I brought up the form, searched for my organization and the information for that organization appeared. The problem is definately with my UPDATE/INSERT process.

The PL/SQL block for the Submit process is the following:

BEGIN
 update 
    MDD.HISA_AGREEMENTS
     set
       LAST_UPDATED_BY=V('APP_USER'),
       APPROVER_SALUTATION=:P6_APPROVER_SALUTATION,
       APPROVER_FIRST_NAME=:P6_APPROVER_FIRST_NAME,
       APPROVER_MIDDLE_INITIAL=:P6_APPROVER_MIDDLE_INITIAL,
       APPROVER_LAST_NAME=:P6_APPROVER_LAST_NAME,
       APPROVER_NAME_SUFFIX=:P6_APPROVER_NAME_SUFFIX,
       APPROVER_EMAIL_ADDR=:P6_APPROVER_EMAIL_ADDR,
       SPONSOR_EMAIL_ADDR=:P6_SPONSOR_EMAIL_ADDR,
       APPROVER_TITLE=:P6_APPROVER_TITLE
 where
      ORG_KEY_LE=:P6_ORG_KEY_LE
;

INSERT INTO
    HISA_AGREEMENT_DOCS
      (HISA_AGREEMENT_DOC_KEY,
       ORG_KEY_LE,
       APPLICATION_KEY,
       STATUS,
       STATUS_DATE,
       CREATED_BY,
       ORG_DOC_CURR_KEY)
 VALUES
      (HISA_AGREEMENT_DOC_KEY_SEQ.NEXTVAL,
       :P6_ORG_KEY_LE,
       :P6_APPLICATION_KEY,
       'C',
       SYSDATE,
       V('APP_USER'),
       :P6_DOCUMENT)
;


END;

There is something wrong with the above statement and I do not understand what it could be. Can someone help?

Thanks for the help.