OraFAQ Forum: SQL & PL/SQL » Using Procedure Update Date day -1

Home » SQL & PL/SQL » SQL & PL/SQL » Using Procedure Update Date day -1 (oracle sql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using Procedure Update Date day -1 [message #685317]

Tue, 07 December 2021 20:35

Rap19
Messages: 2
Registered: December 2021

Junior Member

The details I want, please help

Here I want to see the history of the last location..

example:

when I create a new contract with location SCI-SCG4 start date:25-NOV-21 and end date:25-NOV-26(5yrs from start date)
but a few months/years before the contract expires, SCI-SCG4 closes and moves to a new location then the input becomes:
SCI-SCG5 start date:07-DEC-21 and end date:25-NOV-26 (take from the contract that was previously inputted)

I want history from the previous location (SCI-SCG4) with start date : 25-NOV-2021(take it from the first contract input) and end date: 06-DEC-21(because the store closed this date)

create or replace PACKAGE BODY PKKDSCMSCNTRCTHHIST AS

procedure INS_HEADER_HIST(

PCNTRCTHHISTSDATE DATE,

PCNTRCTHHISTEDATE DATE,

PCNTRCTHHISTCRBY VARCHAR2,

PCNTRCTHHISTCOMP VARCHAR2,

PCNTRCTHHISTLNUM NUMBER,

PCNTRCTHHISTLASTLOC VARCHAR2,

POUTRSNCODE OUT NUMBER,

POUTRSNMSG OUT VARCHAR2) AS

BEGIN

-- TODO: Implementation required for procedure PKKDSCMSCNTRCTHHIST.INS_HEADER_HIST

INSERT INTO KDSCMSCNTRCTHHIST

(CNTRCTHHISTID, CNTRCTHHISTSDATE,CNTRCTHHISTEDATE,CNTRCTHHISTCDAT,CNTRCTHHISTMDAT, CNTRCTHHISTCRBY,CNTRCTHHISTMOBY,CNTRCTHHISTNMOD,CNTRCTHHISTCOMP,CNTRCTHHISTLNUM,CNTRCTHHISTLASTLOC)

VALUES

(KDSCMSCNTRCTHHIST_SEQ.NEXTVAL, PCNTRCTHHISTSDATE, PCNTRCTHHISTEDATE,CURRENT_DATE,CURRENT_DATE, PCNTRCTHHISTCRBY,PCNTRCTHHISTCRBY,0,PCNTRCTHHISTCOMP, PCNTRCTHHISTLNUM,PCNTRCTHHISTLASTLOC);

POUTRSNCODE := 1;

POUTRSNMSG := 'SUCCESS INSERTING';

EXCEPTION

WHEN OTHERS THEN

POUTRSNCODE := -99;

POUTRSNMSG := 'FAILED INSERT';

PKKDSCMSLOG.WRITELOG(SQLCODE, SUBSTR(SQLERRM, 1, 200), PCNTRCTHHISTCRBY, 'PCNTRCTHHISTCRBY.INS_HEADER_HIST', PCNTRCTHHISTCOMP);

NULL;

END INS_HEADER_HIST;

PROCEDURE UPD_HEADER_HIST(

PCNTRCTHHISTID IN NUMBER,

PCNTRCTHHISTSDATE IN DATE,

PCNTRCTHHISTEDATE IN DATE,

PCNTRCTHHISTCRBY VARCHAR2,

PCNTRCTHHISTCOMP VARCHAR2,

PCNTRCTHHISTLNUM NUMBER,

PCNTRCTHHISTLASTLOC VARCHAR2,

POUTRSNCODE OUT NUMBER,

POUTRSNMSG OUT VARCHAR2) AS

var TCNTRCTHHISTID NUMBER;

var locationChangeDate DATE;

BEGIN

TCNTRCTHHISTID :='141';

locationChangeDate := '07-DEC-21';

-- TODO: Implementation required for PROCEDURE PKKDSCMSCNTRCTH.UPD_DATA

UPDATE KDSCMSCNTRCTHHIST

SET CNTRCTHHISTEDATE = DATEADD(day, - 1, locationChangeDate)

WHERE CNTRCTHHISTID = TCNTRCTHHISTID

AND CNTRCTHHISTCOMP = PCNTRCTHHISTCOMP;

NULL;

END UPD_HEADER_HIST;

END PKKDSCMSCNTRCTHHIST;

Report message to a moderator

Re: Using Procedure Update Date day -1 [message #685319 is a reply to message #685317]

Wed, 08 December 2021 00:17

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 100 characters when you format.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Using Procedure Update Date day -1 [message #685327 is a reply to message #685319]

Thu, 09 December 2021 01:00

Rap19
Messages: 2
Registered: December 2021

Junior Member

CREATE TABLE "KDSCMSCNTRCTHHIST"

( "CNTRCTHHISTID" NUMBER(10,0),

"CNTRCTHHISTSDATE" DATE,

"CNTRCTHHISTEDATE" DATE,

"CNTRCTHHISTCDAT" DATE,

"CNTRCTHHISTMDAT" DATE,

"CNTRCTHHISTCRBY" VARCHAR2(20 BYTE),

"CNTRCTHHISTMOBY" VARCHAR2(20 BYTE),

"CNTRCTHHISTNMOD" NUMBER(12,0),

"CNTRCTHHISTCOMP" VARCHAR2(3 BYTE),

"CNTRCTHHISTLNUM" NUMBER(10,0),

"CNTRCTHHISTLASTLOC" VARCHAR2(30 BYTE)

)

INSERT INTO KDSCMSCNTRCTHHIST

(CNTRCTHHISTID, CNTRCTHHISTSDATE,CNTRCTHHISTEDATE,CNTRCTHHISTCDAT,CNTRCTHHISTMDAT, CNTRCTHHISTCRBY,CNTRCTHHISTMOBY,CNTRCTHHISTNMOD,CNTRCTHHISTCOMP,CNTRCTHHISTLNUM,CNTRCTHHISTLASTLOC)

VALUES

VALUES
(299,TO_DATE('2021-12-09','YYYY-MM-DD'),TO_DATE('2026-12-08','YYYY-MM-DD'),TO_DATE('2021-12-09','YYYY-MM-DD'),TO_DATE('2021-12-09','Y YYY-MM-DD'),'admin','admin',0,'NAV',172,'SCI-SCG4');

-------------------------------------------

the first input 1 line like this

(299,TO_DATE('2021-12-09','YYYY-MM-DD'),TO_DATE('2026-12-8','YYYY-MM-DD'),TO_DATE('2021-12-09' ,'YYYY-MM-DD'),TO_DATE('2021-12-09','YYYY-MM-DD'),'admin','admin',0,'NAV',172,'SCI-SCG4' )

the second input want to be like this

(299,TO_DATE('2021-12-09','YYYY-MM-DD'),TO_DATE('2021-12-10','YYYY-MM-DD'),TO_DATE('2021-12-09' ,'YYYY-MM-DD'),TO_DATE('2021-12-09','YYYY-MM-DD'),'admin','admin',0,'NAV',172,'SCI-SCG4' )

(300,TO_DATE('2021-12-11','YYYY-MM-DD'),TO_DATE('2026-12-08','YYYY-MM-DD'),TO_DATE('2021-12-09' ,'YYYY-MM-DD'),TO_DATE('2021-12-09','YYYY-MM-DD'),'admin','admin',0,'NAV',172,'SCI-SCG5' )

but the first line that updates CNTRCTHHISTEDATE to (TO_DATE('2021-12-10','YYYY-MM-DD')

and the second line which updates CNTRCTHHISTEDATE to (TO_DATE('2026-12-09','YYYY-MM-DD')

is this possible?

Report message to a moderator

Re: Using Procedure Update Date day -1 [message #685333 is a reply to message #685327]

Thu, 09 December 2021 09:00

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

Also being discussed on OTN

Report message to a moderator

Previous Topic:	Oracle Display parent, child, grandchild hierarchy
Next Topic:	XMLtype throwing an error ORA-00932

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 20 10:41:36 CDT 2023