I'm working on an Oracle APEX application where I need to implement a confirmation dialog when closing a risk. The process should work as follows:
When the user clicks the "Close Risk" button, I want to check if the risk has a "High" impact status in the RMA_USER_EVALUATIONS table in the column IMPACT_ACTUAL where there's a coincidence between P2_ID and the ID_RISK Column value.
If the impact is "High", I want to show an error message saying "Cannot close this risk because it has a High impact value. The countermeasures were not successful." If the impact is not "High", I want to show a confirmation dialog asking "Are you sure you want to close this risk?"
If the user confirms, I want to proceed with closing the risk (which involves updating a status in the database).
I'm using a Dynamic Action on the "Close Risk" button, with a PL/SQL action to check the risk status, and JavaScript to handle the server response and show the appropriate messages.
However, I'm encountering the following issues:
I'm getting a "parsererror" when trying to parse the JSON response from the server. The P2_ID item (which contains the risk ID) seems to be undefined when the JavaScript code runs. Here's my current PL/SQL code:
DECLARE
l_count NUMBER;
l_message VARCHAR2(4000);
BEGIN
apex_json.open_object;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM RMA_USER_EVALUATIONS
WHERE ID_RISK = :P2_ID
AND (IMPACT_ACTUAL = 'High' OR IMPACT_FORECAST = 'High');
IF l_count > 0 THEN
l_message := 'Cannot close this risk because it has a High impact value. The countermeasures were not successful.';
apex_json.write('status', 'ERROR');
ELSE
l_message := 'Risk can be closed.';
apex_json.write('status', 'SUCCESS');
END IF;
apex_json.write('message', l_message);
EXCEPTION
WHEN OTHERS THEN
apex_json.write('status', 'ERROR');
apex_json.write('message', 'An unexpected error occurred: ' || SQLERRM);
END;
apex_json.close_object;
END;
And here's my JavaScript code:
console.log('P2_ID value:', $v('P2_ID'));
apex.server.process(
'CLOSE_RISK',
{x01: $v('P2_ID')},
{
dataType: 'json',
success: function(data) {
console.log('Server response:', data);
if (data.status === 'ERROR') {
apex.message.showErrors({
type: 'error',
location: 'page',
message: data.message
});
} else if (data.status === 'SUCCESS') {
apex.message.confirm("Are you sure you want to close this risk?", function(okPressed) {
if (okPressed) {
console.log('Closing risk...');
apex.message.showPageSuccess('Risk closed successfully');
}
});
} else {
apex.message.showErrors({
type: 'error',
location: 'page',
message: 'Unexpected response from server.'
});
}
},
error: function(jqXHR, textStatus, errorThrown) {
console.error('AJAX error:', textStatus, errorThrown);
apex.message.showErrors({
type: 'error',
location: 'page',
message: 'An error occurred while processing your request: ' + textStatus
});
}
}
);
And to identify a Risk as closed I have this process PL/SQL
BEGIN
UPDATE RMA_RISK_INCIDENTS
SET IS_CLOSED = 'Y'
WHERE ID = :P2_ID;
END;
So for example more visual if in my RMA_USER_EVALUATIONS table I have something like this: | ID_RISK | SUBTYPE | QUESTION | ANSWER | COMMENTS | ID | SUPPLIER_ID | IMPACT | ACTUAL_ANSWER | FORECAST_ANSWER | IMPACT_ACTUAL | IMPACT_FORECAST | COMMENT_WHEN_CLOSED | |---------|----------------------------|--------------------------------------------------------------------------------------------------|--------|--------------|-----|-------------|--------|---------------|-----------------|---------------|-----------------|---------------------| | 541 | Tropical Storm / Hurricane | How exposed is the supplier's location to the risk of tropical storms or hurricanes? | Yes | TEST5 | 241 | 22463011 | Medium | Yes | No | High | Low | | | 541 | Tropical Storm / Hurricane | What is the potential impact of a tropical storm or hurricane on the supplier's operations? | No | TEST | 242 | 22463011 | Medium | Yes | No | High | Low | | | 541 | Tropical Storm / Hurricane | How well-prepared is the supplier to respond to and recover from such natural disasters? | Yes | TEST | 243 | 22463011 | Medium | Yes | No | High | Low | | | 541 | Tropical Storm / Hurricane | How exposed is the supplier's location to the risk of tropical storms or hurricanes? | Yes | test26 | 101 | 10811510 | High | Yes | | High | Low | | | 541 | Tropical Storm / Hurricane | How exposed is the supplier's location to the risk of tropical storms or hurricanes? | No | coment125355 | 76 | 10627811 | Low | Yes | | High | Low | | | 541 | Tropical Storm / Hurricane | What is the potential impact of a tropical storm or hurricane on the supplier's operations? | No | Coment235235 | 79 | 10627811 | Low | Yes | | High | Low | | | 541 | Tropical Storm / Hurricane | How well-prepared is the supplier to respond to and recover from such natural disasters? | No | Coment335145 | 82 | 10627811 | Low | Yes | | High | Low | | | 541 | Tropical Storm / Hurricane | What is the potential impact of a tropical storm or hurricane on the supplier's operations? | Yes | test36 | 102 | 10811510 | High | Yes | | High | Low | | | 541 | Tropical Storm / Hurricane | How well-prepared is the supplier to respond to and recover from such natural disasters? | Yes | test46 | 103 | 10811510 | High | Yes | | High | Low | |
and the P2_ID is 541 as you can see the IMPACT_ACTUAL has High Values so it should return a message 'Cannot close this risk because it has a High impact value. The countermeasures were not successful'. When clicking the butto, and if there's no High value in IMPACT_ACTUAL the message should be 'Are you sure you want to close this risk' and ask the user for enter a message that should be printed in all the COMMENT_WHEN_CLOSED column of the table because this comment is for all the questions. Can anyone help me identify what I'm doing wrong and how to correctly implement this functionality in APEX?
This requirement looks pretty simple:
With this, no dynamic action is needed and no callback process is needed. The only difference is that the page is submitted. Would this be a possibility ?