| Procedure [message #8234] |
Tue, 05 August 2003 07:46 |
marcin
Messages: 2
Registered: August 2003
|
Junior Member |
|
|
More information... about my problem
Here are the Three tables i'm working with ..... tblSchedules, tblActivities, tblMasterUnit
I need DateStart * txtTime to get I need date from tblSchedules
The formula is : (DateStart) * ((txtTime) * 7)) to get a new date
Thats going to be a new date for tblActivities.
SSLINKID.TBLSCHEDULES = PTMMASTERID.TBLMASTERUNIT
LLINKID.TBLACTIVITIES = PTNMASTERUNIT
After I pass the new date I from the tblschedules to tblActivities it will become dateAssigned ... txtTask, dateAssigned, txtArea, chkSaturday, txtJobPosition, actComments, intPriority, slLinkID from tblschedules to tblactivities.
I need that procedure to run automaticaly once a week .....
desc tblSchedules;
Name Null? Type
------------------------------- -------- ----
INTCOUNTER NOT NULL NUMBER(11)
SLLINKID NUMBER(11)
TXTITEM NOT NULL VARCHAR2(25)
DATESTART NOT NULL DATE
TXTTIME NOT NULL NUMBER(5)
TXTTASK NOT NULL VARCHAR2(255)
SCHEDCOMMENTS VARCHAR2(100)
CHKSATURDAY NUMBER(5)
TXTASSIGNEDTO VARCHAR2(50)
INTPRIORITY NUMBER(11)
desc tblActivities;
Name Null? Type
------------------------------- -------- ----
INTCOUNTER NOT NULL NUMBER(11)
LLINKID NUMBER(11)
TXTITEM VARCHAR2(25)
TXTAREA VARCHAR2(20)
TXTTASK VARCHAR2(255)
TXTDONE NUMBER(1)
TXTEMPLOYEE VARCHAR2(20)
ACTCOMMENTS VARCHAR2(255)
DATEDONE DATE
TXTINIT VARCHAR2(10)
DATECHANGED DATE
DATEASSIGNED DATE
TXTPRINTPROC NUMBER(1)
TXTJOBPOSITION VARCHAR2(50)
DATTIMEDOWN DATE
DATTIMEREPAIRED DATE
TXTMAINTTYPE VARCHAR2(50)
INTJOBNBR NUMBER(11)
CHKSATURDAY NUMBER(11)
INTPRIORITY NUMBER(11)
CHKFIXDATE NUMBER(1)
desc tblMasterUnit;
Name Null? Type
------------------------------- -------- ----
PTMMASTERID NOT NULL NUMBER(11)
PTMMASTERUNIT NOT NULL VARCHAR2(150)
TXTSUPPLIER VARCHAR2(150)
PTMMASTERMODEL VARCHAR2(150)
PTMMASTERMODELD VARCHAR2(150)
PTMMASTERREF VARCHAR2(150)
PTMMASTERDESC VARCHAR2(150)
PTMMASTERSN VARCHAR2(150)
PTMMASTERNOTES VARCHAR2(200)
PTMMASTERAREA VARCHAR2(50)
PTMMASTERTYPE VARCHAR2(50)
PTMPICTUREFILE VARCHAR2(100)
In tblSchedules DateStart is the last date a job was done. TxtTime has a number example 1 or 52 .... the number represents frequency. The number represents weeks. For example : dateStart is 25-JULY-2003 and txtTime has a value of 2 .... First I need to multiply txtTime by 2 * 7 to get 14 days and then add the 14 days to 25-JULY-2003 so the new date for the task will be 08-AUG-2003 ... And it should do that automaticaly.
Here is my procedure that I created with triggers
procedure 1
CREATE OR REPLACE PROCEDURE testingDate
AS
BEGIN
INSERT INTO tblActivities (txtTask, dateAssigned, txtArea, chkSaturday, txtJobPosition, actComments, intPriority, lLinkID)
Select b.txtTask, (b.dateStart + (b.txtTime * 7)), a.ptmMasterUnit, b.chkSaturday, b.txtAssignedTo, b.schedComments, b.intPriority, a.ptmMasterID
From tblMasterUnit A,
tblSchedules B,
tblActivities C
Where A.PTMMASTERID= B.SLLINKID AND C.LLINKID = B.SLLINKID;
END testingDate;
/
Tigger
CREATE OR REPLACE TRIGGER TS_2051_1_TBLACTIVITIES
BEFORE INSERT
ON tblActivities
FOR EACH ROW
BEGIN
SELECT primary.S_2051_1_TBLACTIVITIES.nextval
INTO :new.intCounter
FROM dual;
END;
Does anyone have any suggestions or ideas ???
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
