Database Software

Hi,

Could anyone tell me what is the join condition between PO_REQUISITION_HEADERS_ALL and WF_NOTIFICATIONS?

I joined these two tables by WF_NOTIFICATIONS.ITEM_KEY = PO_REQUISITION_HEADERS_ALL .WF_ITEM_KEY.But I cannot found some approved requisitions item key in WF_NOTIFICATIONS table.

Please help.

I paste my query below:

SELECT PRH.ORG_ID ORG_ID,
WF.ITEM_KEY,
WF.FROM_ROLE,
PRH.WF_ITEM_KEY,
WF.RECIPIENT_ROLE APPROVERS_NAME,
TRUNC(NVL(PRH.APPROVED_DATE, '')) DATE_APPROVED,
WF.SUBJECT SUBJECT,
PRH.DESCRIPTION REQ_DESC,
PRH.SEGMENT1,
(SUM(NVL(PRL.AMOUNT, 0)) +
SUM(NVL(PRL.UNIT_PRICE, 0) * NVL(PRL.QUANTITY, 0))) REQ_AMT,
GL.SEGMENT1 || '.' || GL.SEGMENT2 || '.' || GL.SEGMENT3 || '.' ||
GL.SEGMENT4 || '.' || GL.SEGMENT5 || '.' || GL.SEGMENT6 || '.' ||
GL.SEGMENT7 || '.' || GL.SEGMENT8 CHARGE_ACCT
FROM APPS.WF_NOTIFICATIONS WF,
APPS.PO_REQUISITION_HEADERS_ALL PRH,
APPS.PO_REQUISITION_LINES_ALL PRL,
APPS.PO_REQ_DISTRIBUTIONS_ALL PRD,
APPS.GL_CODE_COMBINATIONS GL
WHERE WF.FROM_ROLE IN ('GHAWKINS', 'GHALVERSON', 'NCERNOTTA', 'DCOLLOPY', 'RHAY')
AND BEGIN_DATE > SYSDATE - 60
AND WF.MESSAGE_TYPE = 'REQAPPRV'
AND WF.ITEM_KEY = PRH.WF_ITEM_KEY --added
AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRD.CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID
GROUP BY PRH.ORG_ID,
WF.ITEM_KEY,
WF.FROM_ROLE,
PRH.WF_ITEM_KEY,
WF.BEGIN_DATE,
WF.SUBJECT,
WF.RECIPIENT_ROLE,
PRH.APPROVED_DATE,
PRH.DESCRIPTION,
PRH.SEGMENT1,
PRL.REQUISITION_HEADER_ID,
GL.SEGMENT1 || '.' || GL.SEGMENT2 || '.' || GL.SEGMENT3 || '.' ||
GL.SEGMENT4 || '.' || GL.SEGMENT5 || '.' || GL.SEGMENT6 || '.' ||
GL.SEGMENT7 || '.' || GL.SEGMENT8