[Home] [Help]
1269: and hpua.status_flag = 'A'
1270: and hpua.party_usage_code = 'SUPPLIER_CONTACT'
1271: and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate)
1272: and hp.party_id not in ( select pcr.contact_party_id
1273: from pos_contact_requests pcr, pos_supplier_mappings psm
1274: where pcr.request_status='PENDING'
1275: and psm.mapping_id = pcr.mapping_id
1276: and psm.PARTY_ID = apsupp.party_id
1277: and pcr.contact_party_id is not null )
1810:
1811: CURSOR l_cur IS
1812: SELECT email_address, first_name, last_name
1813: FROM pos_contact_requests
1814: WHERE mapping_id IN (SELECT mapping_id FROM pos_supplier_mappings WHERE supplier_reg_id = p_supplier_reg_id)
1815: AND do_not_delete = 'Y';
1816:
1817: l_rec l_cur%ROWTYPE;
1818: l_receiver wf_roles.name%TYPE;
2953: CURSOR l_cur IS
2954: SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
2955: FROM pos_supplier_registrations psr,
2956: pos_contact_requests pcr,
2957: pos_supplier_mappings psm
2958: WHERE psr.supplier_reg_id = psm.supplier_reg_id
2959: AND psr.supplier_reg_id = p_supplier_reg_id
2960: AND pcr.mapping_id = psm.mapping_id
2961: AND pcr.do_not_delete = 'Y';
3026: CURSOR l_cur IS
3027: SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address, psr.supplier_name
3028: FROM pos_supplier_registrations psr,
3029: pos_contact_requests pcr,
3030: pos_supplier_mappings psm
3031: WHERE psr.supplier_reg_id = psm.supplier_reg_id
3032: AND psr.supplier_reg_id = p_supplier_reg_id
3033: AND pcr.mapping_id = psm.mapping_id
3034: AND pcr.do_not_delete = 'Y';
3136: CURSOR l_cur IS
3137: SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
3138: FROM pos_supplier_registrations psr,
3139: pos_contact_requests pcr,
3140: pos_supplier_mappings psm
3141: WHERE psr.supplier_reg_id = psm.supplier_reg_id
3142: AND psr.supplier_reg_id = p_supplier_reg_id
3143: AND pcr.mapping_id = psm.mapping_id
3144: AND pcr.do_not_delete = 'Y';