PJB_PROJ_CONT_CUST

Details

Schema: FUSION
Object owner: PJB
Object type: VIEW

Columns

Name
PROJECT_ID PARTY_NAME

Name

PROJECT_ID

PARTY_NAME

Query

SQL_Statement
select distinct PROJECT_ID,PARTY_NAME from ( SELECT PJF_PROJECTS_ALL_B.PROJECT_ID, HZ_PARTIES.PARTY_NAME FROM PJF_PROJECT_PARTIES, HZ_PARTIES, PJF_PROJECTS_ALL_B WHERE PJF_PROJECT_PARTIES.PROJECT_PARTY_TYPE = 'CO' AND PJF_PROJECT_PARTIES.RESOURCE_SOURCE_ID = HZ_PARTIES.PARTY_ID AND PJF_PROJECT_PARTIES.PROJECT_ID = PJF_PROJECTS_ALL_B.PROJECT_ID AND (PJF_PROJECTS_ALL_B.CLIN_LINKED_CODE IS NULL OR (PJF_PROJECTS_ALL_B.CLIN_LINKED_CODE IS NOT NULL AND NOT EXISTS (SELECT 'X' FROM PJB_CNTRCT_PROJ_LINKS,OKC_K_LINES_B WHERE PJB_CNTRCT_PROJ_LINKS.PROJECT_ID = PJF_PROJECTS_ALL_B.PROJECT_ID AND PJB_CNTRCT_PROJ_LINKS.ACTIVE_FLAG = 'Y' AND PJB_CNTRCT_PROJ_LINKS.MAJOR_VERSION = OKC_K_LINES_B.MAJOR_VERSION AND OKC_K_LINES_B.ID = PJB_CNTRCT_PROJ_LINKS.CONTRACT_LINE_ID AND OKC_K_LINES_B.VERSION_TYPE in ('A','C') AND OKC_K_LINES_B.STS_CODE in ('ACTIVE','EXPIRED','HOLD','CLOSED') ) ) ) UNION ALL SELECT DISTINCT PJF_PROJECTS_ALL_B.PROJECT_ID, HZ_PARTIES.PARTY_NAME FROM PJB_CNTRCT_PROJ_LINKS, PJB_BILL_PLANS_B, OKC_K_HEADERS_ALL_B, OKC_K_LINES_B, HZ_PARTIES, HZ_CUST_ACCOUNTS, PJF_PROJECTS_ALL_B WHERE PJF_PROJECTS_ALL_B.PROJECT_ID = PJB_CNTRCT_PROJ_LINKS.PROJECT_ID AND OKC_K_HEADERS_ALL_B.ID = PJB_CNTRCT_PROJ_LINKS.CONTRACT_ID AND OKC_K_HEADERS_ALL_B.MAJOR_VERSION = PJB_CNTRCT_PROJ_LINKS.MAJOR_VERSION AND OKC_K_HEADERS_ALL_B.TEMPLATE_YN = 'N' AND OKC_K_LINES_B.ID = PJB_CNTRCT_PROJ_LINKS.CONTRACT_LINE_ID AND OKC_K_LINES_B.VERSION_TYPE in ('A','C') AND OKC_K_LINES_B.STS_CODE in ('ACTIVE','EXPIRED','HOLD','CLOSED') AND OKC_K_LINES_B.MAJOR_VERSION = PJB_CNTRCT_PROJ_LINKS.MAJOR_VERSION AND PJB_BILL_PLANS_B.BILL_PLAN_ID = OKC_K_LINES_B.BILL_PLAN_ID AND PJB_BILL_PLANS_B.MAJOR_VERSION = OKC_K_LINES_B.MAJOR_VERSION AND PJB_CNTRCT_PROJ_LINKS.ACTIVE_FLAG = 'Y' AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = PJB_BILL_PLANS_B.BILL_TO_CUST_ACCT_ID AND HZ_CUST_ACCOUNTS.PARTY_ID = HZ_PARTIES.PARTY_ID AND PJF_PROJECTS_ALL_B.CLIN_LINKED_CODE IS NOT NULL )

SQL_Statement

select distinct PROJECT_ID,PARTY_NAME from (

SELECT

PJF_PROJECTS_ALL_B.PROJECT_ID,

HZ_PARTIES.PARTY_NAME

FROM

PJF_PROJECT_PARTIES,

HZ_PARTIES,

PJF_PROJECTS_ALL_B

WHERE

PJF_PROJECT_PARTIES.PROJECT_PARTY_TYPE = 'CO'

AND PJF_PROJECT_PARTIES.RESOURCE_SOURCE_ID = HZ_PARTIES.PARTY_ID

AND PJF_PROJECT_PARTIES.PROJECT_ID = PJF_PROJECTS_ALL_B.PROJECT_ID

AND (PJF_PROJECTS_ALL_B.CLIN_LINKED_CODE IS NULL OR (PJF_PROJECTS_ALL_B.CLIN_LINKED_CODE IS NOT NULL AND NOT EXISTS (SELECT 'X' FROM PJB_CNTRCT_PROJ_LINKS,OKC_K_LINES_B WHERE

PJB_CNTRCT_PROJ_LINKS.PROJECT_ID = PJF_PROJECTS_ALL_B.PROJECT_ID AND PJB_CNTRCT_PROJ_LINKS.ACTIVE_FLAG = 'Y' AND PJB_CNTRCT_PROJ_LINKS.MAJOR_VERSION = OKC_K_LINES_B.MAJOR_VERSION

AND OKC_K_LINES_B.ID = PJB_CNTRCT_PROJ_LINKS.CONTRACT_LINE_ID

AND OKC_K_LINES_B.VERSION_TYPE in ('A','C')

AND OKC_K_LINES_B.STS_CODE in ('ACTIVE','EXPIRED','HOLD','CLOSED')

) )

)

UNION ALL

SELECT DISTINCT

PJF_PROJECTS_ALL_B.PROJECT_ID,

HZ_PARTIES.PARTY_NAME

FROM

PJB_CNTRCT_PROJ_LINKS,

PJB_BILL_PLANS_B,

OKC_K_HEADERS_ALL_B,

OKC_K_LINES_B,

HZ_PARTIES,

HZ_CUST_ACCOUNTS,

PJF_PROJECTS_ALL_B

WHERE

PJF_PROJECTS_ALL_B.PROJECT_ID = PJB_CNTRCT_PROJ_LINKS.PROJECT_ID

AND OKC_K_HEADERS_ALL_B.ID = PJB_CNTRCT_PROJ_LINKS.CONTRACT_ID

AND OKC_K_HEADERS_ALL_B.MAJOR_VERSION = PJB_CNTRCT_PROJ_LINKS.MAJOR_VERSION

AND OKC_K_HEADERS_ALL_B.TEMPLATE_YN = 'N'

AND OKC_K_LINES_B.ID = PJB_CNTRCT_PROJ_LINKS.CONTRACT_LINE_ID

AND OKC_K_LINES_B.VERSION_TYPE in ('A','C')

AND OKC_K_LINES_B.STS_CODE in ('ACTIVE','EXPIRED','HOLD','CLOSED')

AND OKC_K_LINES_B.MAJOR_VERSION = PJB_CNTRCT_PROJ_LINKS.MAJOR_VERSION

AND PJB_BILL_PLANS_B.BILL_PLAN_ID = OKC_K_LINES_B.BILL_PLAN_ID

AND PJB_BILL_PLANS_B.MAJOR_VERSION = OKC_K_LINES_B.MAJOR_VERSION

AND PJB_CNTRCT_PROJ_LINKS.ACTIVE_FLAG = 'Y'

AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = PJB_BILL_PLANS_B.BILL_TO_CUST_ACCT_ID

AND HZ_CUST_ACCOUNTS.PARTY_ID = HZ_PARTIES.PARTY_ID

AND PJF_PROJECTS_ALL_B.CLIN_LINKED_CODE IS NOT NULL

)