Using Forms [64 Bit] Version 12.2.1.4.0 (Production)
How can I ensure the order of INSERT operations when COMMIT_FORM is executed?
I have a form that contains multiple data blocks based on different tables (PAYEE, RECIPIENT, VOUCHER, DETAILS, etc.). The problem I am facing is that VOUCHER and DETAILS tables have a one-to-many relationship, but the DETAILS block INSERT statement occurs before the VOUCHER block INSERT statement, so the DETAILS INSERT fails because the VOUCHER primary key exists in the form, but not on the database. I was under the impression that the order of the data blocks determines the order of execution, but the VOUCHER block is listed before the DETAILS block in Oracle Forms Builder.
VOUCHER PRE-INSERT Trigger
MESSAGE('Entering VOUCHER PRE-INSERT');
PAUSE;
:VOUCHER.ADDRESS_LINE1 := :PAYEE_MASTER.ADDRESS_LINE1;
:VOUCHER.ADDRESS_LINE2 := :PAYEE_MASTER.ADDRESS_LINE2;
:VOUCHER.CITY := :PAYEE_MASTER.CITY;
:VOUCHER.STATE := :PAYEE_MASTER.STATE;
:VOUCHER.COUNTRY := :PAYEE_MASTER.COUNTRY;
:VOUCHER.ZIP_CODE := :PAYEE_MASTER.ZIP_CODE;
:VOUCHER.MAIL_CODE := :PAYEE_MASTER.MAIL_CODE;
SET_RECORD_PROPERTY (:SYSTEM.TRIGGER_RECORD,'VOUCHER', STATUS, INSERT_STATUS);
IF :ACCOUNTING.ACTG_TMPL_ID IS NULL THEN
Message ('Accounting Template ID is required');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :VOUCHER.ZIP_CODE IS NULL THEN
Message ('ZIP Code is required');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :VOUCHER.RECIPIENT_MASTER_REC IS NULL THEN
MESSAGE ('A RECIPIENT IS REQUIRED');
RAISE FORM_TRIGGER_FAILURE;
END IF;
DECLARE
V_NUMBER NUMBER(10):= 0;
BEGIN
SELECT COUNT(VOUCHER_NUMBER)
INTO V_NUMBER
FROM MSAPAY.VOUCHER;
--WHERE PAYMENT_TYPE = '01';
IF V_NUMBER > 0 THEN
SELECT (MAX(VOUCHER_NUMBER) + 1)
INTO :VOUCHER.VOUCHER_NUMBER
FROM MSAPAY.VOUCHER;
--WHERE PAYMENT_TYPE = '01';
ELSE
:VOUCHER.VOUCHER_NUMBER := 1;
END IF;
END;
:VOUCHER.Created_By := USER;
:VOUCHER.Created_On := SYSDATE;
SELECT PAY_VOUCHER_SEQ.NEXTVAL
INTO :VOUCHER.VOUCHER_REC
FROM DUAL;
MESSAGE('VOUCHER.VOUCHER_REC = ' || :VOUCHER.VOUCHER_REC);
PAUSE;
DETAILS PRE-INSERT Trigger
MESSAGE('Entering DETAILS PRE-INSERT');
PAUSE;
IF :DETAILS.SERVICE_DATE IS NULL THEN
MESSAGE ('SERVICE DATE MUST BE ENTERED');
MESSAGE (' ');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :DETAILS.CHARGE_AMOUNT IS NULL THEN
MESSAGE('CHARGE AMOUNT MUST BE ENTERED');
MESSAGE(' ');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :ACCOUNTING.ACTG_TMPL_ID IS NULL THEN
Message ('Accounting Template ID is required');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :PAYEE_MASTER.ZIP_CODE IS NULL THEN
Message ('ZIP Code is required');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF (:DETAILS.MEDICAID_SCREEN > .01 AND :DETAILS.ALLOWABLE_AMOUNT > 0) THEN
MESSAGE('ALLOWABLE AMOUNT AND MEDICAID SCREEN AMOUNT CANNOT BE USED AT THE SAME TIME');
MESSAGE(' ');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :DETAILS.PRESCRIPTION_NUMBER IS NULL AND :DETAILS.OTHER_SERVICE IS NULL AND
:DETAILS.PROCEDURE_CODE IS NULL THEN
MESSAGE('PRESCRIPTION NUMBER, PROCEDURE CODE OR OTHER SERVICE MUST BE FILLED IN ');
MESSAGE(' ');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :ACCOUNTING.voucher_acct_REC IS NOT NULL THEN
IF :ACCOUNTING.TOTAL <> :DETAILS.VOUCHER_NET_PAYMENT THEN
MESSAGE('ACCOUNTING TOTAL IS NOT EQUAL TO VOUCHER NET PAYMENT TOTAL. PLEASE CHECK.');
MESSAGE(' ');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
IF :VOUCHER.VOUCHER_REC IS NULL THEN
DECLARE
V_NUMBER NUMBER(10):= 0;
BEGIN
SELECT COUNT(VOUCHER_NUMBER)
INTO V_NUMBER
FROM MSAPAY.VOUCHER;
--WHERE PAYMENT_TYPE = '01';
IF V_NUMBER > 0 THEN
SELECT (MAX(VOUCHER_NUMBER) + 1)
INTO :VOUCHER.VOUCHER_NUMBER
FROM MSAPAY.VOUCHER;
--WHERE PAYMENT_TYPE = '01';
ELSE
:VOUCHER.VOUCHER_NUMBER := 1;
END IF;
END;
:VOUCHER.Created_By := USER;
:VOUCHER.Created_On := SYSDATE;
SELECT MSAPAY.PAY_VOUCHER_SEQ.NEXTVAL
INTO :VOUCHER.VOUCHER_REC
FROM DUAL;
END IF;
:DETAILS.VOUCHER_REC := :VOUCHER.VOUCHER_REC;
MESSAGE('DETAILS.VOUCHER_REC = ' || :DETAILS.VOUCHER_REC);
DECLARE
V_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(DETAILS_REC)
INTO V_COUNT
FROM MSAPAY.DETAILS
WHERE VOUCHER_REC = :DETAILS.VOUCHER_REC;
IF V_COUNT >= 50 THEN
MESSAGE ('EACH VOUCHER CAN HAVE ONLY 50 DETAIL RECORDS');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
Declare
alert_id ALERT := Find_Alert('OVER_21_DENTAL');
alert_display NUMBER;
BEGIN
IF :PAYEE_MASTER.PROVIDER_TYPE IN ('12','74')
AND :DETAILS.SERVICE_DATE > TO_DATE('09302003','MMDDYYYY') THEN
IF :RECIPIENT_MASTER.DATE_OF_BIRTH IS NULL THEN
Set_Alert_Property(alert_id,ALERT_MESSAGE_TEXT,'Did you make sure this person is under 21 for dental services? Continue processing?');
alert_display := Show_Alert(alert_id);
IF alert_display = ALERT_BUTTON2 THEN
MESSAGE ('Cancel this Manual Payment');
RAISE Form_Trigger_Failure;
END IF;
ELSIF :RECIPIENT_MASTER.DATE_OF_BIRTH < (SYSDATE - 7670) THEN -- checking for 21 years old
Set_Alert_Property(alert_id,ALERT_MESSAGE_TEXT,'Are you sure this client has met criteria for payment over 21? Continue processing?');
alert_display := Show_Alert(alert_id);
IF alert_display = ALERT_BUTTON2 THEN
MESSAGE ('Cancel this Manual Payment');
RAISE Form_Trigger_Failure;
END IF;
END IF;
END IF;
END;
:DETAILS.Created_By := USER;
:DETAILS.Created_On := SYSDATE;
SELECT PAY_DETAILS_SEQ.NEXTVAL
INTO :DETAILS.DETAILS_REC
FROM DUAL;
VOUCHER/DETAILS SAVE Button WHEN-BUTTON-PRESSED Trigger (same code)
DECLARE
alert_address_id NUMBER;
alert_country NUMBER;
BEGIN
IF LENGTH(TRIM(:PAYEE_MASTER.COUNTRY)) = 0 OR :PAYEE_MASTER.COUNTRY IS NULL THEN
alert_country := SHOW_ALERT('COUNTRY_ALERT');
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF LENGTH(TRIM(:PAYEE_MASTER.MAIL_CODE)) = 0 OR :PAYEE_MASTER.MAIL_CODE IS NULL THEN
alert_address_id := SHOW_ALERT('ADDRESS_ID_ALERT');
END IF;
END;
GO_BLOCK('VOUCHER');
DO_KEY('COMMIT_FORM');