Query for Supplier and Supplier Contact – Contact Directory

Below is the link between Supplier/Vendor to contact.

SELECT CON.party_name CONTACT, SUP.vendor_name SUPPLIER, ADDR.party_site_name ADDRESS, CON.party_id,
HR.end_date, SUP.vendor_id, SUP_CON.*
FROM hz_parties CON, hz_relationships HR, ap_suppliers SUP, hz_party_sites ADDR, ap_supplier_contacts SUP_CON
WHERE CON.party_id = HR.subject_id
AND HR.subject_type = 'PERSON'
AND HR.object_type = 'ORGANIZATION'
AND HR.relationship_code = 'CONTACT_OF'
AND HR.object_id = SUP.party_id
AND SUP_CON.per_party_id = CON.party_id
AND SUP_CON.org_party_site_id = ADDR.party_site_id
--AND SUP.vendor_id = 2240145

Workflow Validation while Rejecting the Notification

Requirement : To validate whether comments has been entered while rejecting. While Approving the notification the Validation is not Required.

Solution Approach : To write a Procedure and to call in Workflow Notification

There should be comments field in Workflow notification which will have attribute WF_NOTE. Here based on the value that we get we will Validate.

PROCEDURE Reason_not_null_validate (
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
x_resultout OUT NOCOPY VARCHAR2
)
–IS

IS
v_response VARCHAR2 (2000);
l_user_comment VARCHAR2 (2000);
approval_comment EXCEPTION;
BEGIN
IF ( p_funcmode = ‘RESPOND’) then
v_response :=
wf_notification.getattrtext (wf_engine.context_nid, ‘RESULT’);

l_user_comment :=
wf_notification.getattrtext (wf_engine.context_nid,’WF_NOTE’);

IF ( v_response = ‘REJECTED’AND l_user_comment IS NULL) THEN
RAISE approval_comment;
elsif(v_response = ‘REJECTED’AND l_user_comment IS not NULL) then
x_resultout :=’REJECTED’;
end if;
elsif(p_funcmode = ‘RESPOND’AND v_response = ‘APPROVED’)then
x_resultout :=’APPROVED’;
END IF;

EXCEPTION
WHEN approval_comment
THEN
raise_application_error
(-20010,
‘Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.’
);
COMMIT;
END;