SQL & PL/SQL

Using a cursor and loop method records are inserted into the header and line tables. How do I code the IF statement prior to the INSert statement such that Insert if record does not exist else Update the record. If the record for insert fails in the line, the same record should be deleted from the header table as well and transaction should rollback. The Ora version used is 11.2.0.2.

CURSOR C1 IS
        SELECT H.*
        FROM   rex_head_extract h
        WHERE  TRUNC(h.create_date) BETWEEN v_begin_date AND v_end_date;   

 FOR I IN C1 (v_begin_date, v_end_date)
           LOOP
              v_record_count := v_record_count + 1;

              SELECT kcpos_tran_seq.nextval
              INTO   l_tran_seq_nbr
              FROM   dual;

--- Need to perform a check if record exists here prior to insert      
             INSERT INTO tran_head
                 (TRAN_SEQ_NBR,        ORG_NUMBER,
                  STORE_NUMBER,        TRAN_DATE,
                  TERMINAL_NUMBER,     TRAN_NUMBER,
                  TRAN_TIME,           BATCH_NUMBER)
              	VALUES (l_tran_seq_nbr, 1,
                      I.store_number, I.tran_date,
                      I.terminal_number, I.tran_number,
                      I.tran_time, I.batch_number);

 	INSERT INTO tran_line
                 (TRAN_SEQ_NBR, TRAN_LINE_NBR, ORG_NUMBER,
                  STORE_NUMBER,        TRAN_DATE,
                  TERMINAL_NUMBER,     TRAN_NUMBER,
                  TRAN_TIME,           TRAN_TYPE,
                  SUB_TRAN_TYPE)
      		SELECT  l_tran_seq_nbr, tran_line_nbr, 1,
                      store_number, tran_date,
                      terminal_number, tran_number,
                      tran_time, tran_type,
                      sub_tran_type
        	FROM   rex_line_extract
              	WHERE   rex_seq_nbr = I.rex_seq_nbr
              		AND     revision_number = I.revision_number
              		AND     store_number    = I.store_number
              		AND     tran_date       = I.tran_date
              		AND     terminal_number = I.terminal_number
              		AND     tran_number     = I.tran_number;
 	END LOOP;

Source DDL for rex_head_extract

CREATE TABLE REX_HEAD_EXTRACT
(
  REX_SEQ_NBR         NUMBER(20)            NOT NULL,
  LOAD_ID             NUMBER(10)            NOT NULL,
  REVISION_NUMBER         NUMBER(3)             NOT NULL,
  DAY                     NUMBER(3)             NOT NULL,
  RESA_TRAN_SEQ_NO        NUMBER(20)            NOT NULL,
  BUSINESS_DATE           DATE                  NOT NULL,
  TRAN_SEQ_NBR            NUMBER(12),
  ORG_NUMBER              NUMBER,
  STORE_NUMBER            NUMBER,
  TRAN_DATE               DATE,
  TERMINAL_NUMBER         NUMBER,
  TRAN_NUMBER             NUMBER,
  TRAN_TIME               DATE,
  BATCH_NUMBER            VARCHAR2(8 BYTE),
  BATCH_TYPE              VARCHAR2(4 BYTE),
  TRAN_TYPE               VARCHAR2(4 BYTE),
  SUB_TRAN_TYPE           VARCHAR2(4 BYTE),
  DISC_TOTAL              NUMBER(9,2)
)



CREATE INDEX REX_HEAD_EXTRACT_NDX ON REX_HEAD_EXTRACT
(STORE_NUMBER, TRUNC("TRAN_TIME"), TERMINAL_NUMBER, TRAN_NUMBER, REVISION_NUMBER);


CREATE INDEX REX_HEAD_EXTRACT_NDX1 ON REX_HEAD_EXTRACT
(STORE_NUMBER, TRAN_DATE, TERMINAL_NUMBER, TRAN_NUMBER, REVISION_NUMBER);


CREATE INDEX REX_HEAD_EXTRACT_NDX2 ON REX_HEAD_EXTRACT
(BCF_LOAD_ID);


CREATE INDEX REX_HEAD_EXTRACT_NDX3 ON REX_HEAD_EXTRACT
(REX_SEQ_NBR, REVISION_NUMBER, STORE_NUMBER, TRAN_DATE, TERMINAL_NUMBER, 
TRAN_NUMBER);


CREATE UNIQUE INDEX XPK_REX_HEAD_EXTRACT_1 ON REX_HEAD_EXTRACT
(BCF_REX_SEQ_NBR, TRAN_DATE);



ALTER TABLE REX_HEAD_EXTRACT ADD (
  CONSTRAINT XPKREX_HEAD_EXTRACT_1
  PRIMARY KEY
  (REX_SEQ_NBR, TRAN_DATE)
  USING INDEX XPK_REX_HEAD_EXTRACT_1);

DDL - source for REX_LINE_EXTRACT

CREATE TABLE REX_LINE_EXTRACT
(
  REX_SEQ_NBR      NUMBER(20)               NOT NULL,
  REVISION_NUMBER      NUMBER                   NOT NULL,
  DAY                  NUMBER(3)                NOT NULL,
  RESA_TRAN_SEQ_NO     NUMBER(20)               NOT NULL,
  BUSINESS_DATE        DATE                     NOT NULL,
  TRAN_SEQ_NBR         NUMBER(12),
  TRAN_LINE_NBR        NUMBER                   NOT NULL,
  ORG_NUMBER           NUMBER,
  STORE_NUMBER         NUMBER,
  TRAN_DATE            DATE                     NOT NULL,
  TERMINAL_NUMBER      NUMBER,
  TRAN_NUMBER          NUMBER,
  TRAN_TIME            DATE,
  TRAN_TYPE            VARCHAR2(4 BYTE),
  SUB_TRAN_TYPE        VARCHAR2(4 BYTE))


CREATE INDEX REX_LINE_EXTRACT_NDX ON REX_LINE_EXTRACT
(STORE_NUMBER, TRAN_DATE, TERMINAL_NUMBER, TRAN_NUMBER, REVISION_NUMBER);


CREATE INDEX REX_LINE_EXTRACT_NDX2 ON REX_LINE_EXTRACT
(REX_SEQ_NBR, REVISION_NUMBER, STORE_NUMBER, TRAN_DATE, TERMINAL_NUMBER, 
TRAN_NUMBER)

Target table DDL - Tran_Head

CREATE TABLE TRAN_HEAD
(
  TRAN_SEQ_NBR            NUMBER(12),
  ORG_NUMBER              NUMBER,
  STORE_NUMBER            NUMBER,
  TRAN_DATE               DATE,
  TERMINAL_NUMBER         NUMBER,
  TRAN_NUMBER             NUMBER,
  TRAN_TIME               DATE,
  BATCH_NUMBER            VARCHAR2(8 BYTE),
  BATCH_TYPE              VARCHAR2(4 BYTE),
  TRAN_TYPE               VARCHAR2(4 BYTE),
  SUB_TRAN_TYPE           VARCHAR2(4 BYTE));

ALTER TABLE TRAN_HEADER ADD (
  CONSTRAINT XPKKCPOS_TRAN_HEADER_1
  PRIMARY KEY
  (TRAN_SEQ_NBR, TRAN_DATE)
  USING INDEX LOCAL)

Target table DDL - Tran Line

CREATE TABLE TRAN_LINE
(
  TRAN_SEQ_NBR         NUMBER(12),
  TRAN_LINE_NBR        NUMBER,
  ORG_NUMBER           NUMBER,
  STORE_NUMBER         NUMBER,
  TRAN_DATE            DATE,
  TERMINAL_NUMBER      NUMBER,
  TRAN_NUMBER          NUMBER,
  TRAN_TIME            DATE,
  TRAN_TYPE            VARCHAR2(4 BYTE));

ALTER TABLE TRAN_LINE ADD (
  CONSTRAINT XPK_TRAN_LINE
  PRIMARY KEY
  (TRAN_SEQ_NBR, TRAN_LINE_NBR, TRAN_DATE)
  USING INDEX LOCAL);