I am working on a report related to PO and PPM, for that I am using one condition that is identifying the Project Role Name this condition is working as expected for the Project is assigned for that PO , but for POs for which there is no PO that condition is restricting for getting records.
I am using this Query:
select distinct
PHA.SEGMENT1 as PO_NUMBER,
PLA.LINE_NUM AS PO_LINE_NUM,
PLA.ITEM_DESCRIPTION as po_line_description,
PPAM.RESOURCE_SOURCE_NAME AS Project_Owner,
PPAM.EMAIL_ADDRESS AS Project_Owner_Email,
PPEV.PROJECT_NAME,
PPEV.PROJECT_NUMBER,
PSV.VENDOR_NAME ,
PSV.SEGMENT1 AS VENDOR_NUMBER,
PPMFV.FULL_NAME AS BUYER,
PEA.EMAIL_ADDRESS AS Buyer_EMail,
PSCV.EMAIL_ADDRESS AS VENDOR_EMAIL_ADDRESS,
(PPAB.EXTERNAL_PROJECT_ID),
PPAM.PROJECT_ROLE_ID,
PRRTV.PROJECT_ROLE_ID as P1,
PRRTV.PROJECT_ROLE_NAME,
PRRTV.ROLE_ID
from
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PJF_PROJ_ALL_MEMBERS_V PPAM,
PJC_PROJECTS_EXPEND_V ppev,
POZ_SUPPLIERS_V PSV,
PER_PERSON_NAMES_F_V PPMFV,
PER_EMAIL_ADDRESSES PEA,
POZ_SUPPLIER_CONTACTS_V PSCV,
PJF_PROJECTS_ALL_B PPAB,
PJF_PROJ_ROLE_TYPES_V PRRTV,
POZ_SUPPLIER_SITES_V PSSV
where PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
and PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PDA.PJC_PROJECT_ID=PPAM.PROJECT_ID(+)
AND ppev.PROJECT_ID(+)=PPAM.PROJECT_ID
AND PLA.LINE_NUM=:P_PO_LINE_NUMBER
AND PHA.SEGMENT1=:P_PO_NUMBER
AND PSV.VENDOR_ID=PHA.VENDOR_ID
and PPMFV.PERSON_ID=PHA.AGENT_ID
and PPMFV.PERSON_ID=PEA.PERSON_ID
AND PSCV.VENDOR_ID(+)=PSV.VENDOR_ID
AND PEA.EMAIL_TYPE='W1'
AND PPEV.PROJECT_ID=PPAB.PROJECT_ID(+)
and PRRTV.PROJECT_ROLE_ID(+)=PPAM.PROJECT_ROLE_ID
and PRRTV.PROJECT_ROLE_NAME(+)='ABC Financial Project Manager' This condition is for Role Name.
and PSSV.VENDOR_ID=PSV.VENDOR_ID
and PSSV.PRIMARY_PAY_SITE_FLAG= 'Y'
AND PSV.VENDOR_ID=PHA.VENDOR_ID
AND TRUNC(SYSDATE) BETWEEN PPAM.START_DATE_ACTIVE(+) AND NVL(PPAM.END_DATE_ACTIVE(+), SYSDATE)
