[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_party_site_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_CREATED_BY NUMBER,
p_CREATION_DATE DATE,
p_LAST_UPDATE_LOGIN NUMBER,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER) IS
CURSOR c_from_ps_loc IS
SELECT party_site_id, location_id FROM HZ_PARTY_SITES
WHERE party_id = p_from_party_id
AND nvl(status, 'A') = 'A'
AND actual_content_source <> 'DNB';
SELECT party_site_id FROM HZ_PARTY_SITES
WHERE party_id = p_to_party_id
AND location_id = cp_loc_id
AND nvl(status, 'A') = 'A';
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
l_ps_id,
l_dup_ps_id,
'Y',
p_created_by,
p_creation_Date,
p_last_update_login,
p_last_update_date,
p_last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
l_ps_id,
l_ps_id,
'N',
p_created_by,
p_creation_Date,
p_last_update_login,
p_last_update_date,
p_last_updated_by);
END insert_party_site_details;
PROCEDURE insert_party_reln_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_CREATED_BY IN NUMBER,
p_CREATION_DATE IN DATE,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_LAST_UPDATE_DATE IN DATE,
p_LAST_UPDATED_BY IN NUMBER
) IS
CURSOR c_from_reln(l_batch_id NUMBER) IS
SELECT relationship_id, subject_id, object_id,
relationship_code, actual_content_source, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
FROM HZ_RELATIONSHIPS r
WHERE (subject_id = p_from_party_id
OR object_id = p_from_party_id)
AND nvl(status, 'A') IN ('A','I')
AND directional_flag = 'F'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND actual_content_source <> 'DNB';
SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
FROM HZ_RELATIONSHIPS
WHERE ( (subject_id = cp_subj_id AND object_id = cp_obj_id)
OR (p_self_rel ='Y' and ( (subject_id = cp_subj_id AND object_id = cp_subj_id) -- (in case of P1-Supplier-P1 ,P2-Supplier-P2 and Merge P1 into P2)
OR (object_id = cp_subj_id AND subject_id = cp_obj_id) -- (in case of P1-Supplier-P1(Forward) , P2-Customer-P1(Forward) and Merge P1 into P2)
OR (subject_id = cp_subj_id AND object_id = p_from_party_id ) -- (in case of P1-Supplier-P2 ,P2-Supplier-P1 and Merge P1 into P2)
)
) --Bug No: 4609894
)
AND relationship_code = cp_party_rel_code
--OR exists (select 1 from hz_relationship_types where relationship_type = cp_party_relationship_type
--and forward_code=backward_code))
AND ((start_date between from_start_date and from_end_date)
or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
or(start_datefrom_end_date))
AND nvl(status, 'A') IN ('A','I')
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND actual_content_source <> 'DNB';
select 'Y' from hz_relationships where relationship_id=rel_id
and (subject_id IN (p_from_party_id,p_to_party_id))
and (object_id IN (p_from_party_id,p_to_party_id))
AND directional_flag='F';
SELECT relationship_id
FROM HZ_RELATIONSHIPS
WHERE object_id = p_to_party_id
AND subject_id = cp_subj_id
AND relationship_code = cp_party_relationship_type
AND directional_flag = 'F'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND nvl(status, 'A') = 'A';
SELECT batch_id INTO l_batch_id
FROM HZ_MERGE_PARTIES
WHERE batch_party_id = p_batch_party_id;
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_RELATIONSHIPS',
l_pr_id,
l_dup_pr_id,
'Y',
p_created_by,
p_creation_Date,
p_last_update_login,
p_last_update_date,
p_last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_RELATIONSHIPS',
l_pr_id,
l_pr_id,
'N',
p_created_by,
p_creation_Date,
p_last_update_login,
p_last_update_date,
p_last_updated_by);
END insert_party_reln_details;
SELECT nvl(ps.party_site_name, ps.party_site_number) || '(Address: ' ||
l.address1 || ',' ||l.address2||','||l.address3||','||l.address4||','|| l.city ||
',' || l.county || ','|| l.state || ', ' || l.province || ','||l.postal_code || ',' || l.country||')'
FROM HZ_PARTY_SITES ps, HZ_LOCATIONS l
WHERE ps.party_site_id = p_party_site_id
AND ps.location_id = l.location_id;
SELECT '"'||p1.party_name || '"->"' || p2.party_name||'"'
FROM HZ_RELATIONSHIPS pr, HZ_PARTIES p1, --4500011
HZ_PARTIES p2
WHERE p1.party_id = pr.object_id
AND p2.party_id = pr.subject_id
AND pr.relationship_id = p_party_reln_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.directional_flag = 'F';
SELECT p1.party_name
FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc, --4500011
HZ_PARTIES p1
WHERE p1.party_id = pr.subject_id
AND oc.party_relationship_id = pr.relationship_id
AND oc.org_contact_id = p_org_contact_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.directional_flag = 'F';
SELECT org_contact_id
FROM hz_org_contacts
WHERE party_relationship_id = p_party_relationship_id;
SELECT party_id
FROM hz_relationships --4500011
WHERE relationship_id = p_party_relationship_id
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';