----Stage Table
CREATE SEQUENCE DETAILS_STAGING_SQ ;
CREATE TABLE DETAILS_STAGING (
ID NUMBER (10,0) DEFAULT DETAILS_STAGING_SQ.NEXTVAL NOT NULL ENABLE,
KEY_ID NUMBER (10,0) NOT NULL ENABLE,
ID_NUM NUMBER (10,0) NOT NULL ENABLE,
DESCRIPTION VARCHAR2 (255 CHAR) NOT NULL ENABLE,
L7_L8 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_0_TO_3 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_4_TO_7 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_7_TO_10 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
REGION VARCHAR2 (50) NOT NULL ENABLE,
CARD_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
PROD_LIVE DATE NOT NULL ENABLE,
JIRA_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_NAME VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_ID VARCHAR2 (50 CHAR) NOT NULL ENABLE,
EPIC_ID varchar2 (50 char) not null enable,
SUB_TASK_IDS varchar2 (255 char) not null enable,
IS_ACTIVE NUMBER (1,0) NOT NULL ENABLE,
CREATED_BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED_BY VARCHAR2 (50 CHAR),
MODIFIED_ON TIMESTAMP (6),
CONSTRAINT PK_ELM_JIRA_EXTRACT_DETAILS_STAGING PRIMARY KEY(ID)
);
INSERT INTO DETAILS_STAGING VALUES(1,A11,1009,'GTS','STAT','0. AB TASK','6. CD TASK','9. AD TASK','QUATAR','ACTIVE',TO_DATE('2021/09/30'),'IN DEV');
---Main TABLE
CREATE SEQUENCE DETAILS_STAGING_MAIN_SQ ;
CREATE TABLE DETAILS_STAGING_MAIN (
ID NUMBER (10,0) DEFAULT DETAILS_STAGING_MAIN_SQ.NEXTVAL NOT NULL ENABLE,
KEY_ID NUMBER (10,0) NOT NULL ENABLE,
ID_NUM NUMBER (10,0) NOT NULL ENABLE,
DESCRIPTION VARCHAR2 (255 CHAR) NOT NULL ENABLE,
L7_L8 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_CODE VARCHAR2 (255 CHAR) NOT NULL ENABLE,
REGION VARCHAR2 (50) NOT NULL ENABLE,
CARD_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
PROD_LIVE DATE NOT NULL ENABLE,
JIRA_STATUS_CODE VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_NAME VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_ID VARCHAR2 (50 CHAR) NOT NULL ENABLE,
EPIC_ID varchar2 (50 char) not null enable,
SUB_TASK_IDS varchar2 (255 char) not null enable,
IS_ACTIVE NUMBER (1,0) NOT NULL ENABLE,
CREATED_BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED_BY VARCHAR2 (50 CHAR),
MODIFIED_ON TIMESTAMP (6),
CONSTRAINT PK_DETAILS_STAGING_MAIN PRIMARY KEY(ID),
CONSTRAINT FK_DETAILS_STAGING_MAIN FOREIGN KEY(STAGE_CODE) REFERENCES (TASK_ID),
CONSTRAINT FK_DETAILS_STAGING_MAIN FOREIGN KEY(JIRA_STATUS_CODE) REFERENCES (STATUS_ID)
);
---Master TABLE----
CREATE SEQUENCE TASK_REF_SQ ;
CREATE TABLE TASK_REF (
TASK_ID NUMBER (10,0) DEFAULT TASK_REF_SQ.NEXTVAL NOT NULL ENABLE,
TASK_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
COMPLETED_ON DATE NOT NULL ENABLE,
SUMMARY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
IS_ACTIVE NUMBER (1, 0) NOT NULL ENABLE,
CREATED_BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED_BY VARCHAR2 (50 CHAR),
MODIFIED_ON TIMESTAMP (6),
CONSTRAINT PK_TASK_REF PRIMARY KEY (TASK_ID)
);
INSERT INTO TASK_REF VALUES(1,'0.A TASK');
INSERT INTO TASK_REF VALUES(2,'1.B TASK');
INSERT INTO TASK_REF VALUES(3,'2.C TASK');
INSERT INTO TASK_REF VALUES(4,'3.D TASK');
INSERT INTO TASK_REF VALUES(5,'4.F TASK');
INSERT INTO TASK_REF VALUES(6,'5.CD TASK');
INSERT INTO TASK_REF VALUES(7,'6.G TASK');
INSERT INTO TASK_REF VALUES(8,'7.H TASK');
INSERT INTO TASK_REF VALUES(9,'8.I TASK');
INSERT INTO TASK_REF VALUES(10,'9.AD TASK');
INSERT INTO TASK_REF VALUES(11,'10.J TASK');
CREATE SEQUENCE JIRA_STATUS_REF_SQ;
CREATE TABLE JIRA_STATUS_REF
STATUS_ID NUMBER (10,0) DEFAULT ELM_JIRA_STATUS_REF_SQ.NEXTVAL NOT NULL ENABLE,
JIRA_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
IS ACTIVE NUMBER (1,0) NOT NULL ENABLE,
CREATED BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED BY VARCHAR2 (50 CHAR),
MODIFIED ON TIMESTAMP (6)
CONSTRAINT PK_JIRA_STATUS_REF PRIMARY KEY (STATUS_ID)
);
INSERT INTO JIRA_STATUS_REF VALUES(1,'IN DEV');
INSERT INTO JIRA_STATUS_REF VALUES(2,'NOT STARTED');
INSERT INTO JIRA_STATUS_REF VALUES(3,'STARTED');
REQUIREMENT ------------
-- I want to create one stored procedure to load the data from stage table DETAILS_STAGING to main table DETAILS_STAGING_MAIN
for common columns and getting the master data values.
-- When the SP is called first validaton will be done and next data load will be done from DETAILS_STAGING to DETAILS_STAGING_MAIN for only valid records.
--To get the STAGE_CODE and JIRA_STATUS_CODE of main table DETAILS_STAGING_MAIN.
We will select NVL(NVL(STAGE_0_TO_3, STAGE_4_TO_7 , STAGE_7_TO_10) from DETAILS_STAGING. This will give value in single column..then the value has to be joined with
master table TASK_REF and fetch the STAGE_CODE from master table TASK_REF.
Same have to do with JIRA_STATUS_CODE from JIRA_STATUS_REF.