[Home] [Help]
844: IF c_interface_rec.country_code IS NOT NULL THEN
845: -- Firstly Close all the existing citizenship records for this person
846: FOR c_other_nat_rec IN c_other_nat LOOP
847: -- Close the citizenship record by end dating it
848: INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
849: interface_id,
850: status,
851: country_code,
852: date_recognized,
855: created_by,
856: creation_date,
857: last_updated_by,
858: last_update_date)
859: VALUES (igs_pe_citizen_int_s.NEXTVAL,
860: l_interface_id,
861: l_status,
862: c_other_nat_rec.country_code,
863: NVL(c_other_nat_rec.date_recognized,TRUNC(SYSDATE)) ,
876: IF c_pe_nat%NOTFOUND THEN
877: CLOSE c_pe_nat ;
878: -- If person is not already having citizenship for this country then
879: -- create a new nationality record
880: INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
881: interface_id,
882: status,
883: country_code,
884: date_recognized,
885: created_by,
886: creation_date,
887: last_updated_by,
888: last_update_date)
889: VALUES (igs_pe_citizen_int_s.NEXTVAL,
890: l_interface_id,
891: l_status,
892: l_oss_nation,
893: Trunc(SYSDATE) ,
902: -- start and end dates appropriately
903: IF Trunc(c_pe_nat_rec.date_recognized) <= Trunc(SYSDATE) THEN
904: -- If the latest record start date is before sysdate then update its end date to NULL
905: -- But if it is a future record then we cannot create a valid citizenship record ,hence log error
906: INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
907: interface_id,
908: status,
909: country_code,
910: date_recognized,
913: created_by,
914: creation_date,
915: last_updated_by,
916: last_update_date)
917: VALUES (igs_pe_citizen_int_s.NEXTVAL,
918: l_interface_id,
919: l_status,
920: c_pe_nat_rec.country_code,
921: NVL(c_pe_nat_rec.date_recognized,TRUNC(SYSDATE)) ,