Week 3 - Assignment 4 - For me it worked perfectly. - PL/SQL Program Units

Try this code bellow. For me it worked perfectly. ---- TRIGGER DELETE_FACULTY_TRG ----CREATE OR REPLACE TRIGGER delete_faculty_trgBEFORE DELETE ON ad_faculty_detailsBEGIN   IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '09:00' AND '18:00'        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN    RAISE_APPLICATION_ERROR (-20205, 'Faculty records cannot be deleted during the business hours of 9AM and 6PM.');  END IF;END; ---- INVOKE ADD FACULTY ---------- Uncomment and execute the below query anytime after 18:00 and before 09:00 (Monday to Friday) or on a Saturday or Sunday, to test this trigger.DELETE FROM ad_faculty_details WHERE job_id = 'FA_ST';

Related

AR Statement R03B500X - As of Date

Hi all, At April month end I started my normal month end activities on C+2 as C+1 was a public holiday in Ireland (month end closure was the 30th of April). I normally run intercompany AR statements on C+1 but for April month end I ran the statements on C+2. I specified an "As Of Date" of 1st May 2016 when I ran the statements so that transactions up to and including the 1st May only would be included in the statements, but when I checked the PDF Output, the report include invoices from the 2nd of May. Could you please advise me how to avoid this issue in the future or why the report took invoices of the 2nd of May too? Thanks a lot! Agustina

Re: Forte Express Question

I tend to agree with Chris Kelly's earlier posting that
the problem is not with date, but rather with the other
fields. Forte takes care of date format that Oracle can
understand.
Oracle's will allow year entry of between -4713 and +4713.
Forte chose October 1582 as the zero date because it
represents the beginning of the modern calendar as we
know it, called the Gregorian Calendar.
Some interesting reading follows...
From http://www.sprinc.com/marktime.htmImagine that for an entire year you were twelve minutes
early for your appointments. This would be a good thing right?
Aren't we told to be early for our appointments -- that it is
courteous to be early and usually unacceptable to be late?
Well, if we agree on these things, then why on Earth would the
twelve minutes between the old Julian Calendar and the New Style
Gregorian Calendar be such a big deal? The answer lies in time
itself. You see, those twelve minutes accumulated over the years,
decades, and centuries so that by the 1500's the Julian Calendar
had raced ahead of the Sun by ten days!
In October, 1582 Pope Gregory XIII implemented the following
changes to the old Julian Calendar:
- New Year's day changed from March 25 to January 1.
- The calendar advanced by 10 days, making the day after Oct 4,
1582 into Oct 15, 1582.
- Updated and refined Leap Year rules:
1.Years evenly divisible by four are to be
considered Leap Years.
2.Century years (double 00) must be evenly divisible by
400 (2000 is a leap year).
3.The years 4000, 8000, and 12000 are considered to be
"common years" (not leap years).
4.Century years divisible by 900 will be leap years only
if the remainder is 200 or 600 (2000 is a leap year).
The changes above make the calendar accurate to 1 day in 44,000 years.
--------------------------------------
At 06:21 PM 3/31/97 -0500, you wrote:
Dan Elias wrote:
I am incurring the following error message when I go to insert a
record in a Forte Express application. I think the message relates to a
DATE field? Forte Express apparently reads the Oracle db and creates a
DATE field which include DATE&TIME, and Oracle cannot read this! At any
rate can you please contact me when you get a chance.
******** ERROR MESSAGE ********
=======================================================================
SYSTEM ERROR: Execute failed for dynamic SQL statement 3, error from
database
is:
ORA-01438: value larger than specified precision allows for this
column
Class: qqdb_ValueException with ReasonCode: DB_ER_CONVERSION
Detected at: qqdb_OracleVendorInfo::DoOexn
Last TOOL statement: method BusinessDBMgr.ExecuteSql
Error Time: Tue Mar 25 15:22:53
Oracle error: 1438, Server: devsrv1, Database: oratest, UserName:
prc
Database Statement: insert into MPRC(MPRC_SERVICE_TYPE,
MPRC_EFFECT_DATE_AGREED, MPRC_EFFECT_DATE_NEGOTIATED,
MPRC_EFFECT_DATE_REQUESTED, MPRC_DATE_RECEIVED,
MPRC_RAW_MATERIAL_DOLLARS,
MPRC_RAW_MATERIAL_TYPE, MPRC_RAW_MATERIAL, MPRC_INCREASE_TYPE,
MPRC_PCT_INCREASE_NEGOTIATED, MPRC_PCT_INCREASE_REQUESTED,
MPRC_TIME_RECORD_CHANGED, MPRC_DATE_RECORD_CHANGED,
MPRC_WHO_CHANGED_RECORD, MPRC_DANA_TYPE_SAVINGS,
MPRC_PROB_PROJECT_SUCCESS, MPRC_OTHER_COST, MPRC_TOOLING_COST,
MPRC_PROJECT_STATUS, MPRC_PROJECT_COMPLETED,
MPRC_MATERIAL_PRICE_VAR,
MPRC_ENGINEERING_HOURS, MPRC_PIRO_REJECTED_DATE,
MPRC_PIRO_APPROVED_DATE,
MPRC_PIRO_SUBMITTED_DATE, MPRC_IRR, MPRC_TRUCK_WEIGHT_CHANGE,
MPRC_PLANT_IMPACT_EXPLAIN, MPRC_PLANT_IMPACT,
MPRC_WARRANTY_IMPACT_EXPLAIN, MPRC_WARRANTY_IMPACT,
MPRC_QUALITY_IMPACT_EXPLAIN, MPRC_QUALITY_IMPACT,
MPRC_PIRO_NUMBER,
MPRC_YEAR, MPRC_PROJECT_DESCRIPTION, MPRC_INCREASE_DECREASE,
BUYR_BUYER_ID, CMTM_COMMODITY_TEAM, RESC_REASON_CODE,
SLOC_SUPPLIER_CODE,
MPRC_PROJECT_ID) values
(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17
,:p18,:p19,:p20,:p21,:p22,:p23,:p24,:p25,:p26,:p27,:p28,:p29,:p30,:p31,:p32,
:p33,:p34,:p35,:p36,:p37,:p38,:p39,:p40,:p41,:p42)
with input values: , 14-Oct-1582 19:00:00, 14-Oct-1582 19:00:00,
14-Oct-1582 19:00:00, 14-Oct-1582 19:00:00, 0, , , , 0, 0, , , , ,
0, 0,
0, , , , , , , , 2, 1, test P, 0, test W, 0, test Q, 0, 12345,
1997,
Desc., I, A, 20, I, 002100, 12300
Exception occurred (remotely) on partition
"prcMaterialPriceChangeAMWindows_CL0_Part", (partitionId =
C039E19E-6D81-11D0-A5BB-B3F4D289AA77:0x6db:0x1c, taskId =
[C039E19E-6D81-11D0-A5BB-B3F4D289AA77:0x6fb:0x2.12]) in
application "Forte
Runtime", pid 13255 on node devsrv1 in environment CentralEnv.Hi Dan,
You and I spoke briefly about this problem early last week, but now that
I see the actual error message printed out in your posting here, I think
I see where your difficulties are coming from. Take a look at the
actual date values that you are attempting to insert into your Oracle
table (about 10-15 lines up from the bottom of your error message
above). "14-Oct-1582 19:00:00." When you create a DateTimeData object
in Forte and do not assign a value to it, this is the value that it is
given by default. Pretty much all systems have some date that they
consider to be the beginning of time - apparently Forte chose
mid-October, 1582. My suspicion is that 1582 is before the dawn of
Oracle time, and thus Oracle rejects the attempted insert. I didn't
think of it when we last spoke, but I have seen this exact problem
before with Microsoft SQL Server - 1582 is too early a date for it to
recognize. Try going into SQL*Plus and doing an insert with the 14 Oct,
1582 date and see what happens.
Hope this helps!
Dale
Lee Wei wrote:
>
I tend to agree with Chris Kelly's earlier posting that
the problem is not with date, but rather with the other
fields. Forte takes care of date format that Oracle can
understand.
Oracle's will allow year entry of between -4713 and +4713.After testing a few things out today, I have to say I agree with you.
Oracle was perfectly happy with Oct 14, 1582 as a date, and even when I
got the format wrong, I never saw the "value larger than specified
precision" error that Dan Elias was seeing. Thus, it probably IS one of
the other fields causing the problem. My db experience has primarily
been Microsoft SQL Server, where 1582 is NOT a valid date.
Forte chose October 1582 as the zero date because it
represents the beginning of the modern calendar as we
know it, called the Gregorian Calendar.
Some interesting reading follows...And thanks for the interesting facts! I was vaguely aware of some of
it, but didn't know the exact date, for one thing.
Dale

Quartz - CronTrigger - Problem when executing a job monthly

Hi everyone:
I've been looking around and trying to do it with some methods but I can't find the solution. The problem is: I have to give the user the option of selecting when execute the job. One option is executing it monthly and they can set the day of the month. Here is the problem, if they select a date after 28 I get a problem when february comes. What I want to do is that if they select the date to execute the job in the 31th for example, in the months that don't have 31 days, the job would be executed on the 30th of those months. That's no problem because I can set there a conditional clause and if date==31 set in the cron expression 'L' for the day of the month (execute the last day of the month), but it does not work for february, 'cause if they select the day 29th, it won't be executed in february.
I've guessed that the only solution for this is editing the cron once we are in the execution of the job, and if day of execution is greater than 28 and the current month is january, set the nextfiredtime to the last day of february but it doesn't work with
job.getTrigger().getNextFireTime().setTime(xxx);
or
job.getNextFireTime().setTime(xxx)
Do you know anything I can do to manage this problem? I would appreciate it very much
Thank you very muc for your attention
Kind regards 
You can use a cron trigger by using a cron expression for example: 0 0 15 ? * * Executes every day at 15 oclock. There is an spetial character "L" that means to execute the last Month/ Last Day, etc

DBI required to find the Work Schedule of an employee on duration calculation

Hi All,We have an requirement where in our test case/requirement is as follows, We have employee working in shifts in our organization and are as follows,     a. Employee working for 5 days a week with Saturday and Sunday as Weekly off.     b. Employee working for 6 days a week with Sunday as Weekly off.     c. Employee having 1st and 3rd week working on Saturday     d. Employee having 2nd and 4th week working on Saturday We have created he work schedules for all this on our Fusion HCM. However we wanted to include this in our Duration calculation formula as the calculation of weekly offs to included during the period when the employee records a absence. For example, a. Employee working for 5 days a week with Saturday and Sunday as Weekly offWhen an employee records a leave request on Friday and a consecutive absence on Monday, system should include Saturday and Sunday and Monday during the duration calculation on absence management. When an employee has not recorded absence on Friday and a single day of leave is raised by an employee on Monday, system should calculate the duration as one day only. Whenever there is a public holiday that falls between the same, we need to include this in the duration calculation inclusive of the weekly offs if they exists between.  b. Employee working for 6 days a week with Sunday as Weekly offWhen an employee records a leave request on Saturday and a consecutive absence on Monday, system should include Sunday and Monday during the duration calculation on absence management. When an employee has not recorded absence on Saturday and a single day of leave is raised by an employee on Monday, system should calculate the duration as one day only. Whenever there is a public holiday that falls between the same, we need to include this in the duration calculation inclusive of the weekly offs if they exists between. c. Employee having 1st and 3rd week working on SaturdayWhen an employee records a leave request on Friday and a consecutive absence on Monday for the 2nd week, system should include Saturday and Sunday during the duration calculation on absence management and when the leave record raised by an employee during the 1st week of Saturday and a consecutive leave of absence on Monday, system to include Sunday and Monday in the duration calculation. Whenever there is a public holiday that falls between the same, we need to include this in the duration calculation inclusive of the weekly offs if they exists between. d. Employee having 2nd and 4th week working on SaturdayWhen an employee records a leave request on Friday and a consecutive absence on Monday for the 1st week, system should include Saturday and Sunday during the duration calculation on absence management and when the leave record raised by an employee during the 2nd week on Saturday and a consecutive leave of absence on Monday, system to include Sunday and Monday in the duration calculation. Whenever there is a public holiday that falls between the same, we need to include this in the duration calculation inclusive of the weekly offs if they exists between.Can someone who has tried this help me out how can i understand and validated the previous leave application using the no of working days from employee work schedule assigned on Fusion HCM? Regards,Karthik NarayananR

EXTRACT

Hello, My Oracle version is Oracle 11g I am trying to understand EXTRACT function but have stumbled across an error when I run below two queries. 
select extract(YEAR FROM (sysdate) YEAR TO MONTH) from dual;
select extract(YEAR FROM (systimestamp) YEAR TO MONTH) from dual;
 ORA-30083: syntax error was found in interval value expression 30083. 00000 -  "syntax error was found in interval value expression" *Cause:    A syntax error was found during parsing an interval value            value expression. *Action:   Correct the syntax. Error at Line: 12 Column: 41 I am not sure why Oracle is throwing error when the value of sysdate and systimestamp is Date and DateTimestamp respectively as below 
select sysdate from dual;
select systimestamp from dual;
SYSDATE
---------
08-MAY-15
SYSTIMESTAMP                     
-----------------------------------
08-MAY-15 15.46.27.435000000 +01:00
 Explanation to understand the syntax issue is much appreciated. Thanks.
Hi, the syntax is wrong. You don't have to put YEAR TO MONTH after the FROM expression. select extract(YEAR FROM sysdate) from dual;  select extract(YEAR FROM systimestamp) from dual;

Categories

Resources