PJC_CINT_TXN_DETAILS

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
PROJECT_ID PROJECT_NUM PROJECT_OWNING_ORG_ID CINT_RATE_SCH_ID PROJECT_START_DATE PROJECT_COMPLETION_DATE PROJECT_CINT_STOP_DATE TASK_ID TASK_NUMBER TASK_OWNING_ORG_ID TASK_START_DATE TASK_COMPLETION_DATE TASK_CINT_STOP_DATE CAPITAL_COST_TYPE_CODE BURDEN_METHOD TOTAL_BURDEN_FLAG EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_DATE AMOUNT LINE_TYPE LINE_NUM GL_DATE BILLABLE_FLAG ORG_ID CINT_RATE_NAME_ID CINT_GROUPING_METHOD TARGET_EXP_ORGANIZATION_ID RATE_MULTIPLIER CINT_CDL_STATUS PERIOD_NAME

Name

PROJECT_ID

PROJECT_NUM

PROJECT_OWNING_ORG_ID

CINT_RATE_SCH_ID

PROJECT_START_DATE

PROJECT_COMPLETION_DATE

PROJECT_CINT_STOP_DATE

TASK_ID

TASK_NUMBER

TASK_OWNING_ORG_ID

TASK_START_DATE

TASK_COMPLETION_DATE

TASK_CINT_STOP_DATE

CAPITAL_COST_TYPE_CODE

BURDEN_METHOD

TOTAL_BURDEN_FLAG

EXPENDITURE_ITEM_ID

EXPENDITURE_ITEM_DATE

AMOUNT

LINE_TYPE

LINE_NUM

GL_DATE

BILLABLE_FLAG

ORG_ID

CINT_RATE_NAME_ID

CINT_GROUPING_METHOD

TARGET_EXP_ORGANIZATION_ID

RATE_MULTIPLIER

CINT_CDL_STATUS

PERIOD_NAME

Query

SQL_Statement
SELECT pp.project_id PROJECT_ID , pp.segment1 PROJECT_NUM , pp.carrying_out_organization_id PROJECT_OWNING_ORG_ID , pp.cint_rate_sch_id CINT_RATE_SCH_ID , pp.start_date PROJECT_START_DATE , pp.completion_date PROJECT_COMPLETION_DATE , pp.cint_stop_date PROJECT_CINT_STOP_DATE , pt.proj_element_id TASK_ID , pt.element_number TASK_NUMBER , pt.carrying_out_organization_id TASK_OWNING_ORG_ID , pt.start_date TASK_START_DATE , pt.completion_date TASK_COMPLETION_DATE , pt.cint_stop_date TASK_CINT_STOP_DATE , ppt.capital_cost_type_code CAPITAL_COST_TYPE_CODE , ppt.burden_amt_display_method BURDEN_METHOD , ppt.burden_cost_journal_flag TOTAL_BURDEN_FLAG , pei.expenditure_item_id EXPENDITURE_ITEM_ID , pei.expenditure_item_date EXPENDITURE_ITEM_DATE , DECODE (ppt.capital_cost_type_code , 'R', pcdl.projfunc_raw_cost , pcdl.projfunc_burdened_cost ) AMOUNT , pcdl.line_type LINE_TYPE , pcdl.line_num LINE_NUM , TRUNC(NVL(pcdl.recvr_gl_date, pcdl.prvdr_gl_date)) GL_DATE , pcdl.billable_flag BILLABLE_FLAG , pcdl.org_id ORG_ID , pcrn.rate_name_id CINT_RATE_NAME_ID , PJC_CLIENT_EXTN_CAP_INT.grouping_method ( gps.period_name , pp.project_id , pt.proj_element_id , pei.expenditure_item_id , pcdl.line_num , -1 , pet.expenditure_type_id , pec.expenditure_category_id , pei.attribute1 , pei.attribute2 , pei.attribute3 , pei.attribute4 , pei.attribute5 , pei.attribute6 , pei.attribute7 , pei.attribute8 , pei.attribute9 , pei.attribute10 , pei.attribute_category , pei.transaction_source_id , pcrn.rate_name_id ) CINT_GROUPING_METHOD , DECODE ( NVL(pcri.exp_org_source, 'TASK_OWNING_ORG') , 'PROJ_OWNING_ORG', pp.carrying_out_organization_id , 'TASK_OWNING_ORG', pt.carrying_out_organization_id , PJC_CLIENT_EXTN_CAP_INT.expenditure_org ( pei.expenditure_item_id , pcdl.line_num , pcrn.rate_name_id) ) TARGET_EXP_ORGANIZATION_ID , pjc_client_extn_cap_int.rate_multiplier ( pei.expenditure_item_id , pcdl.line_num , pcrn.rate_name_id ) RATE_MULTIPLIER , NVL((SELECT decode ( ppald.expenditure_item_id , NULL, 'OPEN' , 'CLOSED') FROM pjc_prj_asset_ln_dets ppald WHERE ppald.reversed_flag = 'N' AND ppald.line_num = pcdl.line_num AND rownum = 1 AND ppald.expenditure_item_id = pei.expenditure_item_id), 'OPEN') CINT_CDL_STATUS , gps.period_name PERIOD_NAME FROM pjc_cost_dist_lines_all pcdl , pjf_projects_b_v pp , pjf_project_types_b_v ppt , pjf_proj_elements_b_v pt , pjc_exp_items_all pei , pjf_exp_types_b_v pet , pjf_exp_categories_b_v pec , pjc_cint_rate_names pcrn , pjc_cint_rate_info_all pcri , gl_period_statuses gps , pjf_bu_impl_v imp WHERE pcdl.line_type in ('R', 'B') AND pcdl.capitalizable_flag = 'Y' AND pcdl.project_id = pp.project_id AND pp.project_type_id = ppt.project_type_id AND pcdl.line_type = DECODE ( ppt.capital_cost_type_code , 'R', 'R' , pcdl.line_type ) AND pcdl.task_id = pt.proj_element_id AND NVL(pt.cint_eligible_flag, 'Y') = 'Y' AND NVL(pt.RETIREMENT_COST_FLAG, 'N') = 'N' AND pcdl.expenditure_item_id = pei.expenditure_item_id AND pet.expenditure_type_id = pei.expenditure_type_id AND pet.expenditure_category_id = pec.expenditure_category_id AND pcrn.rate_name_id = pcri.rate_name_id(+) AND ( ( NVL(pcri.interest_calculation_method, 'SIMPLE') = 'SIMPLE' AND NOT EXISTS ( SELECT NULL FROM pjc_cint_rate_names pcrn1 WHERE pcrn1.rate_name_id = pcrn.rate_name_id AND pcrn1.expenditure_type_id = pei.expenditure_type_id ) ) OR ( NVL(pcri.interest_calculation_method, 'SIMPLE') <> 'SIMPLE' ) ) AND NOT EXISTS ( select null from pjc_cint_exp_typ_ecl_all excl where excl.rate_info_id = pcri.rate_info_id and excl.expenditure_type_id = pei.expenditure_type_id and nvl(pcri.org_id, -99) = nvl(pp.org_id, -99) ) AND ( ( NVL(pcri.threshold_amt_type, 'TOTAL_CIP') = 'TOTAL_CIP' ) OR ( NVL(pcri.threshold_amt_type, 'TOTAL_CIP') <> 'TOTAL_CIP' AND NOT EXISTS ( SELECT null FROM pjc_prj_asset_ln_dets ppald WHERE ppald.reversed_flag = 'N' AND ppald.line_num = pcdl.line_num AND ppald.expenditure_item_id = pei.expenditure_item_id ) ) ) AND gps.set_of_books_id = imp.primary_ledger_id AND gps.application_id = 101 AND pcri.org_id = pcdl.org_id AND imp.org_id = pcdl.org_id AND pcdl.prvdr_gl_period_name = gps.period_name

SQL_Statement

SELECT

pp.project_id PROJECT_ID

, pp.segment1 PROJECT_NUM

, pp.carrying_out_organization_id PROJECT_OWNING_ORG_ID

, pp.cint_rate_sch_id CINT_RATE_SCH_ID

, pp.start_date PROJECT_START_DATE

, pp.completion_date PROJECT_COMPLETION_DATE

, pp.cint_stop_date PROJECT_CINT_STOP_DATE

, pt.proj_element_id TASK_ID

, pt.element_number TASK_NUMBER

, pt.carrying_out_organization_id TASK_OWNING_ORG_ID

, pt.start_date TASK_START_DATE

, pt.completion_date TASK_COMPLETION_DATE

, pt.cint_stop_date TASK_CINT_STOP_DATE

, ppt.capital_cost_type_code CAPITAL_COST_TYPE_CODE

, ppt.burden_amt_display_method BURDEN_METHOD

, ppt.burden_cost_journal_flag TOTAL_BURDEN_FLAG

, pei.expenditure_item_id EXPENDITURE_ITEM_ID

, pei.expenditure_item_date EXPENDITURE_ITEM_DATE

, DECODE (ppt.capital_cost_type_code

, 'R', pcdl.projfunc_raw_cost

, pcdl.projfunc_burdened_cost

) AMOUNT

, pcdl.line_type LINE_TYPE

, pcdl.line_num LINE_NUM

, TRUNC(NVL(pcdl.recvr_gl_date, pcdl.prvdr_gl_date)) GL_DATE

, pcdl.billable_flag BILLABLE_FLAG

, pcdl.org_id ORG_ID

, pcrn.rate_name_id CINT_RATE_NAME_ID

, PJC_CLIENT_EXTN_CAP_INT.grouping_method (

gps.period_name

, pp.project_id

, pt.proj_element_id

, pei.expenditure_item_id

, pcdl.line_num

, -1

, pet.expenditure_type_id

, pec.expenditure_category_id

, pei.attribute1

, pei.attribute2

, pei.attribute3

, pei.attribute4

, pei.attribute5

, pei.attribute6

, pei.attribute7

, pei.attribute8

, pei.attribute9

, pei.attribute10

, pei.attribute_category

, pei.transaction_source_id

, pcrn.rate_name_id

) CINT_GROUPING_METHOD

, DECODE (

NVL(pcri.exp_org_source, 'TASK_OWNING_ORG')

, 'PROJ_OWNING_ORG', pp.carrying_out_organization_id

, 'TASK_OWNING_ORG', pt.carrying_out_organization_id

, PJC_CLIENT_EXTN_CAP_INT.expenditure_org (

pei.expenditure_item_id

, pcdl.line_num

, pcrn.rate_name_id)

) TARGET_EXP_ORGANIZATION_ID

, pjc_client_extn_cap_int.rate_multiplier (

pei.expenditure_item_id

, pcdl.line_num

, pcrn.rate_name_id

) RATE_MULTIPLIER

, NVL((SELECT decode ( ppald.expenditure_item_id

, NULL, 'OPEN'

, 'CLOSED')

FROM pjc_prj_asset_ln_dets ppald

WHERE ppald.reversed_flag = 'N'

AND ppald.line_num = pcdl.line_num

AND rownum = 1

AND ppald.expenditure_item_id = pei.expenditure_item_id), 'OPEN') CINT_CDL_STATUS

, gps.period_name PERIOD_NAME

FROM pjc_cost_dist_lines_all pcdl

, pjf_projects_b_v pp

, pjf_project_types_b_v ppt

, pjf_proj_elements_b_v pt

, pjc_exp_items_all pei

, pjf_exp_types_b_v pet

, pjf_exp_categories_b_v pec

, pjc_cint_rate_names pcrn

, pjc_cint_rate_info_all pcri

, gl_period_statuses gps

, pjf_bu_impl_v imp

WHERE pcdl.line_type in ('R', 'B')

AND pcdl.capitalizable_flag = 'Y'

AND pcdl.project_id = pp.project_id

AND pp.project_type_id = ppt.project_type_id

AND pcdl.line_type = DECODE (

ppt.capital_cost_type_code

, 'R', 'R'

, pcdl.line_type

)

AND pcdl.task_id = pt.proj_element_id

AND NVL(pt.cint_eligible_flag, 'Y') = 'Y'

AND NVL(pt.RETIREMENT_COST_FLAG, 'N') = 'N'

AND pcdl.expenditure_item_id = pei.expenditure_item_id

AND pet.expenditure_type_id = pei.expenditure_type_id

AND pet.expenditure_category_id = pec.expenditure_category_id

AND pcrn.rate_name_id = pcri.rate_name_id(+)

AND (

(

NVL(pcri.interest_calculation_method, 'SIMPLE') = 'SIMPLE'

AND NOT EXISTS ( SELECT NULL

FROM pjc_cint_rate_names pcrn1

WHERE pcrn1.rate_name_id = pcrn.rate_name_id

AND pcrn1.expenditure_type_id = pei.expenditure_type_id

)

OR

(

NVL(pcri.interest_calculation_method, 'SIMPLE') <> 'SIMPLE'

)

AND NOT EXISTS ( select null

from pjc_cint_exp_typ_ecl_all excl

where excl.rate_info_id = pcri.rate_info_id

and excl.expenditure_type_id = pei.expenditure_type_id

and nvl(pcri.org_id, -99) = nvl(pp.org_id, -99)

)

AND (

(

NVL(pcri.threshold_amt_type, 'TOTAL_CIP') = 'TOTAL_CIP'

)

OR

(

NVL(pcri.threshold_amt_type, 'TOTAL_CIP') <> 'TOTAL_CIP'

AND NOT EXISTS ( SELECT null

FROM pjc_prj_asset_ln_dets ppald

WHERE ppald.reversed_flag = 'N'

AND ppald.line_num = pcdl.line_num

AND ppald.expenditure_item_id = pei.expenditure_item_id

)

AND gps.set_of_books_id = imp.primary_ledger_id

AND gps.application_id = 101

AND pcri.org_id = pcdl.org_id

AND imp.org_id = pcdl.org_id

AND pcdl.prvdr_gl_period_name = gps.period_name