[Home] [Help]
699: l_enable_log VARCHAR2(1);
700: l_request_id NUMBER;
701: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
702: l_party_site_ovn hz_party_sites.object_version_number%TYPE;
703: l_location_ovn hz_locations.object_version_number%TYPE;
704: l_addr_process BOOLEAN := FALSE;
705:
706:
707: --1. Pick up all the records from the table IGS_AD_RELADDR_INT
767: l_last_update_date DATE;
768: l_site_use_last_update_date DATE;
769: l_profile_last_update_date DATE;
770: l_row_id VARCHAR2(30);
771: l_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
772: l_territory_short_name FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
773: l_Check VARCHAR2(30);
774: l_object_version_number NUMBER;
775:
782: WHERE party_site_id = p_party_site_id AND
783: site_use_type = p_site_use_code;
784: l_flag_check_status VARCHAR2(1);
785: site_c_rec site_c%ROWTYPE;
786: l_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
787: l_party_site_id HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
788: l_party_site_use_id HZ_PARTY_SITE_USES.PARTY_SITE_USE_ID%TYPE;
789: l_row_id VARCHAR2(30);
790: l_Status IGS_AD_RELATIONS_INT.STATUS%TYPE;
803: l_profile_last_update_date := NULL;
804:
805: l_location_id := addr_rec.location_id;
806: l_party_site_id := addr_rec.party_site_id;
807: -- cursor to fetch l_last_update_date from hz_locations for a given location_id
808: l_location_ovn := addr_rec.location_ovn;
809: l_party_site_ovn := addr_rec.party_site_ovn;
810:
811: IGS_PE_PERSON_ADDR_PKG.UPDATE_ROW(
1226: match_ind = '19'
1227: WHERE mi.interface_relations_id = p_interface_relations_id
1228: AND mi.status = '2'
1229: AND EXISTS ( SELECT 1
1230: FROM hz_locations pe, hz_party_sites ps
1231: WHERE ps.party_id = P_Rel_Person_Id
1232: AND ps.location_id = pe.location_id
1233: AND UPPER(pe.address1) = UPPER(mi.addr_line_1)
1234: AND UPPER(NVL(pe.address2,'NulL')) = UPPER(NVL(mi.addr_line_2,'NulL'))
1300: WHERE mi.interface_relations_id = p_interface_relations_id
1301: AND mi.match_ind IS NULL
1302: AND mi.status = '2'
1303: AND EXISTS (SELECT 1
1304: FROM hz_locations pe, hz_party_sites ps
1305: WHERE ps.party_id = P_Rel_Person_Id
1306: AND ps.location_id = pe.location_id
1307: AND UPPER(pe.address1) = UPPER(mi.addr_line_1)
1308: AND UPPER(NVL(pe.address2,'NulL')) = UPPER(NVL(mi.addr_line_2,'NulL'))