[Home] [Help]
1217: and hpua.status_flag = 'A'
1218: and hpua.party_usage_code = 'SUPPLIER_CONTACT'
1219: and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate)
1220: and hp.party_id not in ( select pcr.contact_party_id
1221: from pos_contact_requests pcr, pos_supplier_mappings psm
1222: where pcr.request_status='PENDING'
1223: and psm.mapping_id = pcr.mapping_id
1224: and psm.PARTY_ID = apsupp.party_id
1225: and pcr.contact_party_id is not null )
1742:
1743: CURSOR l_cur IS
1744: SELECT email_address, first_name, last_name
1745: FROM pos_contact_requests
1746: WHERE mapping_id IN (SELECT mapping_id FROM pos_supplier_mappings WHERE supplier_reg_id = p_supplier_reg_id)
1747: AND do_not_delete = 'Y';
1748:
1749: l_rec l_cur%ROWTYPE;
1750: l_receiver wf_roles.name%TYPE;
2885: CURSOR l_cur IS
2886: SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
2887: FROM pos_supplier_registrations psr,
2888: pos_contact_requests pcr,
2889: pos_supplier_mappings psm
2890: WHERE psr.supplier_reg_id = psm.supplier_reg_id
2891: AND psr.supplier_reg_id = p_supplier_reg_id
2892: AND pcr.mapping_id = psm.mapping_id
2893: AND pcr.do_not_delete = 'Y';
2958: CURSOR l_cur IS
2959: SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
2960: FROM pos_supplier_registrations psr,
2961: pos_contact_requests pcr,
2962: pos_supplier_mappings psm
2963: WHERE psr.supplier_reg_id = psm.supplier_reg_id
2964: AND psr.supplier_reg_id = p_supplier_reg_id
2965: AND pcr.mapping_id = psm.mapping_id
2966: AND pcr.do_not_delete = 'Y';