[Home] [Help]
119: END LOOP;
120: SAVEPOINT pop_parties;
121: BEGIN
122: FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
123: INSERT INTO HZ_SRCH_PARTIES (
124: TX2, TX36, TX41, TX45, TX59, PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID
125: ) VALUES (
126: H_TX2(I), H_TX36(I), H_TX41(I), H_TX45(I), H_TX59(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PARTY_ID(I), P_BATCH_ID, H_P_ROW_ID(I)
127: );
429: END LOOP;
430: SAVEPOINT pop_parties_int;
431: BEGIN
432: FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
433: INSERT INTO HZ_SRCH_PARTIES (
434: TX2, TX36, TX41, TX45, TX59, PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID , PARTY_ID
435: ) VALUES (
436: H_TX2(I), H_TX36(I), H_TX41(I), H_TX45(I), H_TX59(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), P_BATCH_ID, H_P_ROW_ID(I) , H_P_PARTY_ID(I)
437: );
924: decode(instrb(s2.TX45,s1.TX45),1,200,
925: 0
926: )
927: score , s1.party_osr party_osr, s1.party_os party_os
928: from HZ_SRCH_PARTIES s1, HZ_STAGED_PARTIES s2
929: where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
930: and nvl(s2.status,'A') = 'A'
931: and (
932: -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
987: group by f, t, party_osr, party_os
988: )
989: ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
990: where EXISTS (
991: SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_STAGED_PARTIES p2
992: WHERE p1.batch_id = p_batch_id and p1.party_osr = party_osr and p1.party_os = party_os
993: and p2.party_id = t
994: and
995: -- do an or between all the transformations of an attribute --
1335: decode(instrb(s2.TX45,s1.TX45),1,200,
1336: 0
1337: )
1338: score, s1.party_os fos, s2.party_os tos
1339: from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2
1340: where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1341: and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)
1342: and (
1343: -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1399: group by f, t, fos, tos
1400: )
1401: ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
1402: where EXISTS (
1403: SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_SRCH_PARTIES p2
1404: WHERE p1.batch_id = p_batch_id and p1.party_osr = f and p1.party_os = fos
1405: and p2.batch_id = p_batch_id and p2.party_osr = t and p2.party_os = tos
1406: and
1407: -- do an or between all the transformations of an attribute --