[Home] [Help]
25: select party_id, party_name
26: from(
27: select p.party_id party_id, p.party_name party_name,
28: RANK() OVER (ORDER BY p.last_update_date desc ) rank
29: from hz_parties p, hz_dup_set_parties mp
30: where p.party_id = mp.dup_party_id
31: and mp.dup_set_id = p_dup_set_id
32: and p.status = cp_status
33: and nvl(mp.merge_flag,'Y') = 'Y'
40: select party_id, party_name
41: from(
42: select p.party_id party_id,p.party_name party_name,
43: RANK() OVER (ORDER BY p.creation_date desc, p.party_id desc ) rank
44: from hz_parties p, hz_dup_set_parties mp
45: where p.party_id = mp.dup_party_id
46: and mp.dup_set_id = p_dup_set_id
47: and p.status = cp_status
48: and nvl(mp.merge_flag,'Y') = 'Y'
54: select party_id, party_name
55: from(
56: select p.party_id party_id,p.party_name party_name,
57: RANK() OVER (ORDER BY p.creation_date, p.party_id) rank
58: from hz_parties p, hz_dup_set_parties mp
59: where p.party_id = mp.dup_party_id
60: and mp.dup_set_id = p_dup_set_id
61: and p.status = cp_status
62: and nvl(mp.merge_flag,'Y') = 'Y'
74: ca.party_id party_id, party.party_name party_name,
75: count(*) over (partition by ca.party_id) as cnt,
76: ca.last_update_date
77: from
78: HZ_CUST_ACCOUNTS CA,HZ_PARTIES PARTY, HZ_DUP_SETS DS,
79: HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
80: WHERE CA.PARTY_ID =DSP.DUP_PARTY_ID
81: AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
82: AND DS.DUP_SET_ID = DSP.DUP_SET_ID
97: SELECT
98: ps.party_id party_id, party.party_name party_name,
99: count(*) over (partition by ps.party_id) as cnt,
100: ps.last_update_date
101: from HZ_PARTY_SITES PS,HZ_PARTIES PARTY,
102: HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
103: WHERE PS.PARTY_ID =DSP.DUP_PARTY_ID
104: AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
105: AND DS.DUP_SET_ID = DSP.DUP_SET_ID
121: party.party_id party_id,
122: party.party_name party_name,
123: count(*) over (partition by party.party_id) as cnt,
124: party.last_update_date
125: from HZ_RELATIONSHIPS R,HZ_PARTIES PARTY, HZ_DUP_SETS DS,
126: HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
127: WHERE PARTY.PARTY_ID =DSP.DUP_PARTY_ID
128: AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
129: AND DS.DUP_SET_ID = DSP.DUP_SET_ID
140: select party_id, party_name
141: from(
142: select p.party_id party_id, p.party_name party_name,
143: RANK() OVER (ORDER BY p.certification_level, p.last_update_date desc ) rank
144: from hz_parties p, hz_dup_set_parties mp
145: where p.party_id = mp.dup_party_id
146: and mp.dup_set_id = p_dup_set_id
147: and p.status = cp_status
148: and nvl(mp.merge_flag,'Y') = 'Y'
150: where rank = 1 and rownum=1;
151:
152: cursor get_active_party_count_csr is
153: select count(*)
154: from hz_parties p, hz_dup_set_parties mp
155: where p.party_id = mp.dup_party_id
156: and mp.dup_set_id = p_dup_set_id
157: and p.status = 'A';
158:
163:
164: -- validate party_id and party_name combination.
165: cursor check_set_party_exist_csr(cp_party_id number, cp_party_name varchar2) is
166: select count(*)
167: from HZ_DUP_SET_PARTIES dsp, hz_parties p
168: where dsp.dup_set_id = p_dup_set_id
169: and dsp.dup_party_id = p.party_id
170: and dsp.dup_party_id = cp_party_id
171: and p.party_name = cp_party_name;
172:
173: -- Bug 4592273: only one active party in the set
174: cursor get_active_party_csr is
175: select p.party_id, p.party_name
176: from hz_parties p, hz_dup_set_parties mp
177: where p.party_id = mp.dup_party_id
178: and mp.dup_set_id = p_dup_set_id
179: and p.status = 'A'
180: and rownum = 1;
450: l_cand_type VARCHAR2(30);
451:
452: cursor get_winner_party_type(l_dset_id NUMBER) is
453: select party_type
454: from HZ_PARTIES a, HZ_DUP_SETS b
455: where a.party_id = b.winner_party_id
456: and b.dup_set_id = l_dset_id;
457:
458: cursor get_party_type(l_party_id NUMBER) is
456: and b.dup_set_id = l_dset_id;
457:
458: cursor get_party_type(l_party_id NUMBER) is
459: select party_type
460: from HZ_PARTIES
461: where party_id = l_party_id;
462:
463: BEGIN
464:
577: l_temp_type VARCHAR2(30);
578:
579: cursor get_party_type(l_party_id NUMBER) is
580: select party_type
581: from HZ_PARTIES
582: where party_id = l_party_id;
583:
584: BEGIN
585:
1873: BEGIN
1874:
1875: ---Per PM, take out Party Modeling for Certification Level
1876: /* OPEN c_cursor for 'select count(*) '||
1877: ' from HZ_PARTIES pa '||
1878: ' where pa.certification_level = 100 '||
1879: ' and pa.party_id IN ('||p_party_ids||')';
1880: FETCH c_cursor INTO l_count;
1881: if (l_count > 1) then
1887: l_count := 0;
1888: ---Party Modeling for relationship Hierarchy
1889: open c_cursor FOR ' select distinct parent_id,child_id,nvl(top_parent_flag,''N'') top_parent_flag,level_number,hierarchy_type,'||
1890: ' p1.party_number parent_reg_id,p2.party_number child_reg_id '||
1891: ' from hz_hierarchy_nodes h,hz_parties p1,hz_parties p2 '||
1892: ' where EXISTS (select 1 from hz_hierarchy_nodes h2 '||
1893: ' where h2.parent_id = h.parent_id '||
1894: ' and h2.hierarchy_type = h.hierarchy_type '||
1895: ' and nvl(h2.top_parent_flag,''N'') = ''Y'' '||
1988: x_msg_data OUT NOCOPY VARCHAR2 ) is
1989:
1990: cursor get_merge_parties_csr is
1991: select party_id
1992: from hz_merge_parties mp, hz_parties party
1993: where (party.party_id = mp.from_party_id or party.party_id = mp.to_party_id)
1994: and mp.batch_id = p_merge_batch_id;
1995:
1996: l_party_id number;
2015: IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' and l_dl_dss_prof <> 'F' THEN
2016:
2017: l_test_security := hz_dss_util_pub.test_instance(
2018: p_operation_code => 'UPDATE',
2019: p_db_object_name => 'HZ_PARTIES',
2020: p_instance_pk1_value => l_party_id,
2021: p_user_name => fnd_global.user_name,
2022: x_return_status => dss_return_status,
2023: x_msg_count => dss_msg_count,