0

I have users who enter staff at the end of the day i have to run 2 scripts

which sets all the base master and detail data for each member over a 2 years.

Script 1. Generates the master data

MERGE INTO MD_TS_MAST d
USING (SELECT e.ENG_ID,
            a.DT 
       FROM MAN_ENGINEERS e
       CROSS JOIN ALL_DATES a) s 
ON (d.ENG_ID = s.ENG_ID AND
    d.MS_DATE = s.DT) 
WHEN NOT MATCHED THEN 
INSERT (ENG_ID, 
        MS_DATE) 
VALUES (s.ENG_ID, 
        s.DT); 

Script 2. Generates the detail data

INSERT INTO MD_TS_DETAIL 
        (MD_ID) 
SELECT MAST_ID 
FROM   MD_TS_MAST 
WHERE  MAST_ID NOT IN (SELECT MD_ID 
                   FROM   MD_TS_DETAIL 
                   WHERE  MD_ID IS NOT NULL); 

question is how can I create a button that when pressed will run the 2 scripts.

1 Answer 1

3

Create a PL/SQL page process that runs on submit with the source:

MERGE INTO MD_TS_MAST d
USING (SELECT e.ENG_ID,
            a.DT 
       FROM MAN_ENGINEERS e
       CROSS JOIN ALL_DATES a) s 
ON (d.ENG_ID = s.ENG_ID AND
    d.MS_DATE = s.DT) 
WHEN NOT MATCHED THEN 
INSERT (ENG_ID, 
        MS_DATE) 
VALUES (s.ENG_ID, 
        s.DT); 

INSERT INTO MD_TS_DETAIL 
        (MD_ID) 
SELECT MAST_ID 
FROM   MD_TS_MAST 
WHERE  MAST_ID NOT IN (SELECT MD_ID 
                   FROM   MD_TS_DETAIL 
                   WHERE  MD_ID IS NOT NULL); 

Then put a button on the page that submits the page.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.