PJB_CC_IC_IDENTIFICATION

Details

Schema: FUSION
Object owner: PJB
Object type: VIEW

Columns

Name
CONTRACT_ID PROVIDER_LE_ID PROVIDER_ORG_ID RECEIVER_PROJECT_ID PROJ_ELEMENT_ID

Name

CONTRACT_ID

PROVIDER_LE_ID

PROVIDER_ORG_ID

RECEIVER_PROJECT_ID

PROJ_ELEMENT_ID

Query

SQL_Statement
SELECT header.id contract_id, header.legal_entity_id PROVIDER_LE_ID, INT.provider_business_unit_id PROVIDER_ORG_ID, INT.receiver_project_id RECEIVER_PROJECT_ID , Element.PROJ_ELEMENT_ID PROJ_ELEMENT_ID FROM okc_k_headers_all_b header, okc_k_lines_b lines, pjb_cntrct_proj_links link, pjb_clin_internal_attributes Int, pjf_projects_all_b proj, pjf_proj_elements_b element WHERE Header.id = lines.dnz_chr_id AND lines.id = link.contract_line_id AND header.id = link.contract_id AND Link.Billing_type_code = 'IC' AND Link.Active_flag = 'Y' AND link.PROJECT_ID = Proj.Project_id AND Proj.Project_id = Element.PROJECT_ID AND Proj.Project_id = Int.receiver_project_id AND NVL(RCVR_SETUP_CODE,'IC_NON_GLOBAL' ) = 'IC_NON_GLOBAL' AND DECODE(PROJ.IC_CLIN_LINKED_CODE ,'T', Element.IC_CLIN_ELEMENT_ID, Element.project_id) = DECODE(PROJ.IC_CLIN_LINKED_CODE, 'T', Link.PROJ_ELEMENT_ID, Element.project_id ) AND DECODE(PROJ.IC_CLIN_LINKED_CODE, 'T', Element.LEAF_NODE_FLAG, 'Y') = 'Y' AND header.version_type in ('A','C') AND header.sts_code in ('ACTIVE','EXPIRED') AND header.major_version = Lines.major_version AND Lines.major_version = link.major_version AND Lines.major_version = Int.major_version UNION SELECT header.id contract_id, header.legal_entity_id PROVIDER_LE_ID, INT.provider_business_unit_id PROVIDER_ORG_ID, Proj.project_id RECEIVER_PROJECT_ID, Element.PROJ_ELEMENT_ID PROJ_ELEMENT_ID FROM okc_k_headers_all_b header, okc_k_lines_b lines, pjb_bill_plans_b plan, pjb_clin_internal_attributes Int, pjf_projects_all_b proj, pjf_proj_elements_b element WHERE Header.id = lines.dnz_chr_id and lines.bill_plan_id = plan.bill_plan_id and lines.id = int.contract_line_id and header.id = int.contract_id and int.RCVR_SETUP_CODE = 'IC_GLOBAL' and int.RCVR_BUSINESS_UNIT_ID = proj.ORG_ID AND Proj.Project_id = Element.PROJECT_ID AND Element.LEAF_NODE_FLAG = 'Y' and Proj.legal_entity_id = plan.BILL_TO_CUSTOMER_LE_ID AND (NOT EXISTS ( SELECT 1 FROM PJC_CC_PRJ_BU_RELATIONSHIPS cc WHERE cc.bu_id = int.PROVIDER_BUSINESS_UNIT_ID AND cc.EXCEPTION_FLAG ='E' AND cc.project_id = proj.PROJECT_ID ) OR EXISTS ( SELECT 1 FROM PJC_CC_PRJ_BU_RELATIONSHIPS cc WHERE cc.bu_id = int.PROVIDER_BUSINESS_UNIT_ID AND cc.EXCEPTION_FLAG ='I' AND cc.project_id = proj.PROJECT_ID) ) AND header.version_type in ('A','C') AND header.sts_code in ('ACTIVE','EXPIRED') AND header.major_version = Lines.major_version AND lines.major_version = plan.major_version AND Lines.major_version = Int.major_version

SQL_Statement

SELECT header.id contract_id,

header.legal_entity_id PROVIDER_LE_ID,

INT.provider_business_unit_id PROVIDER_ORG_ID,

INT.receiver_project_id RECEIVER_PROJECT_ID ,

Element.PROJ_ELEMENT_ID PROJ_ELEMENT_ID

FROM okc_k_headers_all_b header,

okc_k_lines_b lines,

pjb_cntrct_proj_links link,

pjb_clin_internal_attributes Int,

pjf_projects_all_b proj,

pjf_proj_elements_b element

WHERE Header.id = lines.dnz_chr_id

AND lines.id = link.contract_line_id

AND header.id = link.contract_id

AND Link.Billing_type_code = 'IC'

AND Link.Active_flag = 'Y'

AND link.PROJECT_ID = Proj.Project_id

AND Proj.Project_id = Element.PROJECT_ID

AND Proj.Project_id = Int.receiver_project_id

AND NVL(RCVR_SETUP_CODE,'IC_NON_GLOBAL' ) = 'IC_NON_GLOBAL'

AND DECODE(PROJ.IC_CLIN_LINKED_CODE ,'T', Element.IC_CLIN_ELEMENT_ID, Element.project_id)

= DECODE(PROJ.IC_CLIN_LINKED_CODE, 'T', Link.PROJ_ELEMENT_ID, Element.project_id )

AND DECODE(PROJ.IC_CLIN_LINKED_CODE, 'T', Element.LEAF_NODE_FLAG, 'Y') = 'Y'

AND header.version_type in ('A','C')

AND header.sts_code in ('ACTIVE','EXPIRED')

AND header.major_version = Lines.major_version

AND Lines.major_version = link.major_version

AND Lines.major_version = Int.major_version

UNION

SELECT header.id contract_id,

header.legal_entity_id PROVIDER_LE_ID,

INT.provider_business_unit_id PROVIDER_ORG_ID,

Proj.project_id RECEIVER_PROJECT_ID,

Element.PROJ_ELEMENT_ID PROJ_ELEMENT_ID

FROM okc_k_headers_all_b header,

okc_k_lines_b lines,

pjb_bill_plans_b plan,

pjb_clin_internal_attributes Int,

pjf_projects_all_b proj,

pjf_proj_elements_b element

WHERE Header.id = lines.dnz_chr_id

and lines.bill_plan_id = plan.bill_plan_id

and lines.id = int.contract_line_id

and header.id = int.contract_id

and int.RCVR_SETUP_CODE = 'IC_GLOBAL'

and int.RCVR_BUSINESS_UNIT_ID = proj.ORG_ID

AND Proj.Project_id = Element.PROJECT_ID

AND Element.LEAF_NODE_FLAG = 'Y'

and Proj.legal_entity_id = plan.BILL_TO_CUSTOMER_LE_ID

AND (NOT EXISTS ( SELECT 1

FROM PJC_CC_PRJ_BU_RELATIONSHIPS cc

WHERE cc.bu_id = int.PROVIDER_BUSINESS_UNIT_ID

AND cc.EXCEPTION_FLAG ='E'

AND cc.project_id = proj.PROJECT_ID )

OR EXISTS ( SELECT 1 FROM PJC_CC_PRJ_BU_RELATIONSHIPS cc

WHERE cc.bu_id = int.PROVIDER_BUSINESS_UNIT_ID

AND cc.EXCEPTION_FLAG ='I'

AND cc.project_id = proj.PROJECT_ID)

)

AND header.version_type in ('A','C')

AND header.sts_code in ('ACTIVE','EXPIRED')

AND header.major_version = Lines.major_version

AND lines.major_version = plan.major_version

AND Lines.major_version = Int.major_version