For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
How to user filepicker in VBCS? I want to use PO Attachment REST api to attach any file(s) attached on filepicker to SaaS for specific PO Header ID.
CREATE OR REPLACE TRIGGER "BI_LOAD_RATES" before insert on "LOAD_RATES" for each row DECLARE -- *missing* --variable declarations actv INTEGER; actv_dt DATE; end_dt DATE; rt_id NUMBER; shft VARCHAR2(2); prem VARCHAR2(2); lvl INTEGER; cursor c_shft is SELECT 'D' a from DUAL UNION SELECT 'N' FROM DUAL; -- *use alias for later reference* cursor c_prem is SELECT 'B' a from DUAL UNION SELECT 'P' FROM DUAL; -- *use alias for later reference* begin if :NEW."LOAD_RATE_ID" is null then -- maybe it's better to avoid double_quotes -- not clear select "SEQ_LOAD_RATE_ID".nextval into rt_id from dual; :NEW."LOAD_RATE_ID" := rt_id; end if; actv := :NEW.ACTIVE; actv_dt := :NEW.EFFECTIVE_DATE; end_dt := :NEW.END_DATE for shft in c_shft loop for prem in c_prem loop FOR lvl in 1..4 LOOP INSERT INTO LOAD_PAY(ID, LOAD_RATE_ID, PAY_AMT, EXP_LVL, SHIFT, PREMIUM, ACTIVE, ACTIVE_DT, END_DT) VALUES (SEQ_LOAD_PAY.nextval, rt_id, 0, lvl, shft.a, prem.a, actv, SYSDATE, actv_dt, end_dt); -- *use aliases* END LOOP; end loop; end loop; end; /
... INSERT INTO LOAD_PAY (ID, LOAD_RATE_ID, PAY_AMT, EXP_LVL, SHIFT, PREMIUM, ACTIVE, ACTIVE_DT, END_DT) WITH c_shft AS ( SELECT 'D' AS shft FROM dual UNION ALL SELECT 'N' AS shft FROM dual ) , c_prem AS ( SELECT 'B' AS prem FROM dual UNION ALL SELECT 'P' AS prem FROM dual ) , c_lvl AS ( SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 4 ) SELECT SEQ_LOAD_PAY.nextval, -- ID rt_id, -- LOAD_RATE_ID 0, -- PAY_AMT l.lvl, -- EXP_LVL s.shft, -- SHIFT p.prem, -- PREMIUM :NEW.active, -- ACTIVE SYSDATE, -- ACTIVE_DT :NEW.end_date -- END_DT FROM c_shft s CROSS JOIN c_prem p CROSS JOIN c_lvl l ;
CREATE OR REPLACE TRIGGER "BI_LOAD_RATES" before insert on "LOAD_RATES" ... if :NEW."LOAD_RATE_ID" is null then select "SEQ_LOAD_RATE_ID".nextval into rt_id from dual; :NEW."LOAD_RATE_ID" := rt_id; ...
CREATE OR REPLACE TRIGGER BI_LOAD_RATES before insert on LOAD_RATES for each row declare --variable declarations actv INTEGER; actv_dt DATE; end_dt DATE; rt_id NUMBER; lvl INTEGER; begin if :NEW.LOAD_RATE_ID is null then select SEQ_LOAD_RATE_ID.nextval into rt_id from dual; :NEW.LOAD_RATE_ID := rt_id; end if; actv := :NEW.ACTIVE; actv_dt := :NEW.EFFECTIVE_DATE; end_dt := :NEW.END_DATE; for shft in (SELECT 'D' s from DUAL UNION SELECT 'N' s FROM DUAL) loop for prem in (SELECT 'B' p from DUAL UNION SELECT 'P' p FROM DUAL) loop FOR lvl in 1..4 LOOP INSERT INTO LOAD_PAY(ID, LOAD_RATE_ID, PAY_AMT, EXP_LVL, SHIFT, PREMIUM, ACTIVE, ACTIVE_DT, END_DT) VALUES (SEQ_LOAD_PAY.nextval, rt_id, 0, lvl, shft.s, prem.p, actv, actv_dt, end_dt); END LOOP; end loop; end loop; end; /