Hi All,
I hope someone from this group can help me on this. I am really struggling on the solution and not able to getting close to it.
On day-1 all three tables look like below:
On day -2 my PARTY_DOC has more data (below):
I am using Oracle 19C and below are the tables, sequence and functions that ill be using…
create sequence s_doc_key start with 1;
create or replace function DocKeyNext return varchar2 is
begin
return 'D-KEY-' || s_doc_key.nextval;
end;
CREATE TABLE PARTY_DOC
(
PARTY_KEY VARCHAR2(255 CHAR) NOT NULL ENABLE,
DOCUMENT_NUMBER VARCHAR2(50 CHAR),
DOCUMENT_TYPE_CD VARCHAR2(50 CHAR)
);
CREATE TABLE DOCUMENT
(
DOCUMENT_KEY VARCHAR2(50 CHAR) NOT NULL ENABLE,
DOCUMENT_NUMBER VARCHAR2(50 CHAR),
DOCUMENT_TYPE_CD VARCHAR2(50 CHAR),
EXPIRATION_DATE TIMESTAMP (6),
CONSTRAINT PK_DOCUMENT PRIMARY KEY (DOCUMENT_KEY)
);
CREATE TABLE DOCUMENT_RELATION
(
DOCUMENT_KEY VARCHAR2(50 CHAR) NOT NULL ENABLE,
PARTY_KEY VARCHAR2(255 CHAR) NOT NULL ENABLE,
CONSTRAINT PK_EDR PRIMARY KEY (DOCUMENT_KEY, PARTY_KEY)
);
insert into PARTY_DOC values ('P_51474', '35380387', 'EMPLOYER_IN');
insert into PARTY_DOC values ('P_51474','12345678','SSN');
insert into PARTY_DOC values ('P_51474','98765432','DRIVER_LICENSE');
insert into PARTY_DOC values ('P_43294','571142901','TIN');
insert into PARTY_DOC values ('P_43294','387945090','OTHER');
insert into PARTY_DOC values ('P_92169','777777777','TIN');
insert into PARTY_DOC values ('P_92169','531724788','PASSPORT');
insert into PARTY_DOC values ('P_92169','399526772','EMPLOYER_IN');
insert into PARTY_DOC values ('P_10253','643056091','SSN');
insert into PARTY_DOC values ('P_96752','398401225','DRIVER_LICENSE');
insert into DOCUMENT values (DocKeyNext,'35380387','EMPLOYER_IN','');
insert into DOCUMENT values (DocKeyNext,'387945090','OTHER','');
insert into DOCUMENT values (DocKeyNext,'398401225','DRIVER_LICENSE','');
insert into DOCUMENT values (DocKeyNext,'777777777','TIN','');
insert into DOCUMENT_RELATION values ('D-KEY-1','P_51474');
insert into DOCUMENT_RELATION values ('D-KEY-2','P_43294');
insert into DOCUMENT_RELATION values ('D-KEY-3','P_96752');
insert into DOCUMENT_RELATION values ('D-KEY-4','P_92169');
Here is what I need to do…
-
I must first check that if, PARTY\_DOC.DOC\_TYPE\_CD = ‘EMPLOYER\_IN’, ‘SSN’, or ‘ITIN’, there is not already a record with this PARTY\_KEY, and one of ‘EMPLOYER\_IN’, ‘SSN’, or ‘ITIN’, as the DOCUMENT\_TYPE\_CD on the DOCUMENT table, where DOCUMENT.EXPIRATION\_DATE is null.
If there is, then don’t add this DOCUMENT_NUMBER to the DOCUMENT table or PARTY_KEY to the DOCUMENT_RELATION table – skip it. At any time for the ‘null’ EXPIRATION_DATE, DOCUMENT table can only have either one of ‘EMPLOYER_IN’, ‘SSN’, or ‘ITIN’, DOCUMENT_TYPE_CD plus any other DOCUMENT_TYPE_CD (PASSPORT , DRIVER_LICENSE , OTHER, etc).
For example:
PARTY_KEY (P_51474) already has a record in DOCUMENT table for DOCUMENT_TYPE_CD: EMPLOYER_IN and EXPIRATION_DATE is null. So, it should not add document_number 12345678 doc_type_cd: SSN in DOCUMENT table and PARTY_KEY: P_51474 in the DOCUMENT_RELATION table. However, it should add DOCUMENT_NUMBER#98765432, DOCUMENT_TYPE_CD# DRIVER_LICENSE in the DOCUMENT table with a new D-KEY-5 and a new entry in DOCUMENT_RELATION as D-KEY-5 and PARTY_KEY# P_51474.
Thank you in advance!