Home » SQL & PL/SQL » SQL & PL/SQL » Using Procedure Update Date day -1 (oracle sql)
Using Procedure Update Date day -1 [message #685317] Tue, 07 December 2021 20:35 Go to next message
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;
Re: Using Procedure Update Date day -1 [message #685319 is a reply to message #685317] Wed, 08 December 2021 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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.

Re: Using Procedure Update Date day -1 [message #685327 is a reply to message #685319] Thu, 09 December 2021 01:00 Go to previous messageGo to next message
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?

Re: Using Procedure Update Date day -1 [message #685333 is a reply to message #685327] Thu, 09 December 2021 09:00 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Also being discussed on OTN
Previous Topic: Oracle Display parent, child, grandchild hierarchy
Next Topic: XMLtype throwing an error ORA-00932
Goto Forum:
  


Current Time: Thu Mar 28 15:35:09 CDT 2024