Hello APEX Users,
I have an APEX form (Application Express 5.0.2.00.07) with dynamic actions that will be used for updating records in a designated table.
The issue I am running into is when I save the data into the table, the data is being saved correctly, but when I pull the data from the table, the form is populating the data into the wrong fields.
See example attachments.
The before_saving.jpg shows that I am placing a numerical number in Class Code and it saves into the field on the table correctly, but if I try and retrieve it in the form (after_saving.jpg), it shows in the highlighted space instead (Maximum Contract) instead of it's original field (Class Code).
Has anybody experienced these type of issues? We want to refrain from using an Interactive report which was the ideal way, but we feel it would be confusing to the user. Any assistance or help is greatly appreciated.
PL/SQL Block to retrieve records and place them into the fields.
declare
begin
select V.CONTRACT_NO as CONTRACT_NO,
V.VENDOR_ID as VENDOR_ID,
V.FISCAL_YEAR as FISCAL_YEAR,
V.TRANSACTION_CODE as TRANSACTION_CODE,
V.NUM_ID as NUM_ID,
V.VENDOR_NAME as VENDOR_NAME,
V.ADDRESS1 as ADDRESS1,
V.ADDRESS2 as ADDRESS2,
V.CITY as CITY,
V.STATE as STATE,
V.ZIP as ZIP,
V.VENDOR_CODE as VENDOR_CODE,
V.START_DATE as START_DATE,
V.END_DATE as END_DATE,
V.COMMENTS as COMMENTS,
DOC.RATE as RATE,
DOC.TIME as TIME,
DOC.AWARD_CODE as AWARD_CODE,
DOC.PUBLICATION_DATE as PUBLICATION_DATE,
DOC.REFERENCE as REFERENCE,
DOC.SUBCONTRACTOR_UTIL as SUBCONTRACTOR_UTIL,
DOC.SUBCONTRACTOR_DISC as SUBCONTRACTOR_DISC,
DOC.TRAVEL_EXPENSES as TRAVEL_EXPENSES,
DOC.ADVANCE_PAYMENT as ADVANCE_PAYMENT,
DOC.CONTRACT_ACTION as CONTRACT_ACTION,
DOC.CLASS_CODE as CLASS_CODE,
DOC.GOVERNOR_NO as GOVERNOR_NO,
DOC.MAXIMUM_CONTRACT as MAXIMUM_CONTRACT,
DOC.ANNUAL_CONTRACT as ANNUAL_CONTRACT,
DOC.APPROPRIATION as APPROPRIATION,
DOC.APPROPRIATION1 as APPROPRIATION1,
DOC.APPROPRIATION2 as APPROPRIATION2,
DOC.APPROPRIATION3 as APPROPRIATION3,
DOC.APPROPRIATION4 as APPROPRIATION4,
DOC.APPROPRIATION5 as APPROPRIATION5,
DOC.APPROPRIATION6 as APPROPRIATION6,
DOC.APPROPRIATION7 as APPROPRIATION7,
DOC.APPROPRIATION8 as APPROPRIATION8,
DOC.APPROPRIATION9 as APPROPRIATION9,
DOC.APPROPRIATION10 as APPROPRIATION10,
DOC.OBLIGATION as OBLIGATION,
DOC.OBLIGATION1 as OBLIGATION1,
DOC.OBLIGATION2 as OBLIGATION2,
DOC.OBLIGATION3 as OBLIGATION3,
DOC.OBLIGATION4 as OBLIGATION4,
DOC.OBLIGATION5 as OBLIGATION5,
DOC.OBLIGATION6 as OBLIGATION6,
DOC.OBLIGATION7 as OBLIGATION7,
DOC.OBLIGATION8 as OBLIGATION8,
DOC.OBLIGATION9 as OBLIGATION9,
DOC.OBLIGATION10 as OBLIGATION10,
V.MULTI_YEAR_START as MULTI_YEAR_START,
V.MULTI_YEAR_END as MULTI_YEAR_END,
DOC.MULTI_YEAR2 as MULTI_YEAR2,
DOC.MULTI_YEAR3 as MULTI_YEAR3,
DOC.MULTI_YEAR4 as MULTI_YEAR4,
DOC.MULTI_YEAR5 as MULTI_YEAR5,
DOC.MULTI_YEAR6 as MULTI_YEAR6,
DOC.MULTI_YEAR7 as MULTI_YEAR7,
DOC.AMOUNT as AMOUNT
into
:P3_CONTRACT_NO,
:P3_VENDOR_ID,
:P3_FISCAL_YEAR,
:P3_TRANSACTION_CODE,
:P3_NUM_ID,
:P3_VENDOR_NAME,
:P3_ADDRESS1,
:P3_ADDRESS2,
:P3_CITY,
:P3_STATE,
:P3_ZIP,
:P3_VENDOR_CODE,
:P3_START_DATE,
:P3_END_DATE,
:P3_COMMENTS,
:P3_RATE,
:P3_TIME,
:P3_AWARD_CODE,
:P3_PUBLICATION_DATE,
:P3_REFERENCE,
:P3_SUBCONTRACTOR_UTIL,
:P3_SUBCONTRACTOR_DISC,
:P3_TRAVEL_EXPENSES,
:P3_ADVANCE_PAYMENT,
:P3_CONTRACT_ACTION,
:P3_CLASS_CODE,
:P3_GOVERNOR_NO,
:P3_MAXIMUM_CONTRACT,
:P3_ANNUAL_CONTRACT,
:P3_APPROPRIATION,
:P3_APPROPRIATION1,
:P3_APPROPRIATION2,
:P3_APPROPRIATION3,
:P3_APPROPRIATION4,
:P3_APPROPRIATION5,
:P3_APPROPRIATION6,
:P3_APPROPRIATION7,
:P3_APPROPRIATION8,
:P3_APPROPRIATION9,
:P3_APPROPRIATION10,
:P3_OBLIGATION,
:P3_OBLIGATION1,
:P3_OBLIGATION2,
:P3_OBLIGATION3,
:P3_OBLIGATION4,
:P3_OBLIGATION5,
:P3_OBLIGATION6,
:P3_OBLIGATION7,
:P3_OBLIGATION8,
:P3_OBLIGATION9,
:P3_OBLIGATION10,
:P3_MULTI_YEAR_START,
:P3_MULTI_YEAR_END,
:P3_MULTI_YEAR2,
:P3_MULTI_YEAR3,
:P3_MULTI_YEAR4,
:P3_MULTI_YEAR5,
:P3_MULTI_YEAR6,
:P3_MULTI_YEAR7,
:P3_AMOUNT
from SIUPO_CONTRACTS_OBLIGATION_V V,
SIUPO_CONTRACTS_OBLIGATION_DOC DOC
WHERE V.CONTRACT_NO = DOC.CONTRACT_NO
AND V.CONTRACT_NO = :P3_SEARCH;
end;
PL/SQL Block when saving:
declare
begin
update SIUPO_CONTRACTS_OBLIGATION_DOC
Set CONTRACT_ACTION = :P3_CONTRACT_ACTION,
CLASS_CODE = :P3_CLASS_CODE,
GOVERNOR_NO = :P3_GOVERNOR_NO,
MAXIMUM_CONTRACT = :P3_MAXIMUM_CONTRACT,
ANNUAL_CONTRACT = :P3_ANNUAL_CONTRACT,
APPROPRIATION = :P3_APPROPRIATION,
OBLIGATION = :P3_OBLIGATION,
APPROPRIATION1 = :P3_APPROPRIATION1,
OBLIGATION1 = :P3_OBLIGATION1,
APPROPRIATION2 = :P3_APPROPRIATION2,
OBLIGATION2 = :P3_OBLIGATION2,
APPROPRIATION3 = :P3_APPROPRIATION3,
OBLIGATION3 = :P3_OBLIGATION3,
APPROPRIATION4 = :P3_APPROPRIATION4,
OBLIGATION4 = :P3_OBLIGATION4,
APPROPRIATION5 = :P3_APPROPRIATION5,
OBLIGATION5 = :P3_OBLIGATION5,
APPROPRIATION6 = :P3_APPROPRIATION6,
OBLIGATION6 = :P3_OBLIGATION6,
APPROPRIATION7 = :P3_APPROPRIATION7,
OBLIGATION7 = :P3_OBLIGATION7,
APPROPRIATION8 = :P3_APPROPRIATION8,
OBLIGATION8 = :P3_OBLIGATION8,
APPROPRIATION9 = :P3_APPROPRIATION9,
OBLIGATION9 = :P3_OBLIGATION9,
APPROPRIATION10 = :P3_APPROPRIATION10,
OBLIGATION10 = :P3_OBLIGATION10,
MULTI_YEAR2 = :P3_MULTI_YEAR2,
MULTI_YEAR3 = :P3_MULTI_YEAR3,
MULTI_YEAR4 = :P3_MULTI_YEAR4,
MULTI_YEAR5 = :P3_MULTI_YEAR5,
MULTI_YEAR6 = :P3_MULTI_YEAR6,
MULTI_YEAR7 = :P3_MULTI_YEAR7,
RATE = :P3_RATE,
TIME = :P3_TIME,
AWARD_CODE = :P3_AWARD_CODE,
TRAVEL_EXPENSES = :P3_TRAVEL_EXPENSE,
PUBLICATION_DATE = TO_DATE(:P3_PUBLICATION_DATE, 'DD-MON-YYYY'),
REFERENCE = :P3_REFERENCE,
ADVANCE_PAYMENT = :P3_ADVANCE_PAYMENT,
SUBCONTRACTOR_UTIL = :P3_SUBCONTRACTOR_UTIL,
SUBCONTRACTOR_DISC = :P3_SUBCONTRACTOR_DISC,
AMOUNT = :P3_AMOUNT
WHERE CONTRACT_NO = :P3_SEARCH;
IF sql%rowcount = 0 then
dbms_output.put_line ('no rows returned');
RAISE_APPLICATION_ERROR(-200001,'Check data entry',TRUE);
END IF;
end;
My question is does order matter when retrieving? I've tried to order the fields based on the table, but it doesn't seem to matter. As you can see, I'm only updating fields that the user can manipulate via the form. The fields that come from the View (V.field_name) cannot be updated.
Much appreciated,
Adam