[Home] [Help]
743: (SELECT hp.party_name
744: FROM hz_parties hp
745: WHERE hp.party_id = pcr.contact_party_id
746: )) contact_name
747: FROM pos_contact_requests pcr
748: WHERE pcr.contact_request_id = p_contact_request_id;
749:
750: l_rec l_cur%ROWTYPE;
751:
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 )
1809: l_process wf_process_activities.process_name%TYPE;
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;
2952:
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
3025:
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
3135:
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