VIEW: APPS.PA_CC_BL_CUR_SEL_V

Source

View Text - Preformatted

SELECT rowidtochar(ei.rowid) exp_item_rowid, ei.expenditure_item_id expenditure_item_id, t.project_id project_id, decode(ei.cc_cross_charge_code, 'B', decode(adjusted_expenditure_item_id, NULL, 'Y', 'N'), 'N') calc_tp_flag, ei.adjusted_expenditure_item_id adjusted_exp_item_id, ei.system_linkage_function system_linkage_function, ei.task_id task_id, ei.expenditure_item_date expenditure_item_date, ei.net_zero_adjustment_flag net_zero_flag, ei.quantity quantity, ei.billable_flag billable_flag, ei.organization_id nl_resource_organization_id, nlorg.name nl_organization_name, ei.cc_prvdr_organization_id prvdr_organization_id, ei.cc_recvr_organization_id recvr_organization_id, ei.denom_tp_currency_code denom_tp_currency_code, ei.denom_currency_code denom_currency_code, ei.denom_transfer_price denom_transfer_price, ei.acct_currency_code acct_currency_code, ei.acct_tp_rate_type acct_tp_rate_type, ei.acct_tp_rate_date acct_tp_rate_date, ei.acct_tp_exchange_rate acct_tp_exchange_rate, ei.acct_transfer_price acct_transfer_price, ei.project_currency_code proj_currency_code, ei.cc_cross_charge_code cross_charge_code, ei.expenditure_type expenditure_type, ei.revenue_distributed_flag revenue_distributed_flag, ei.job_id job_id, exp.expenditure_group expenditure_group, exp.expenditure_ending_date expenditure_ending_date, pa_utils.get_pa_date(ei.expenditure_item_date, sysdate) pa_date, decode(ei.override_to_organization_id,null, exp.incurred_by_organization_id) expenditure_organization_id, exporg.name expenditure_organization_name, t.carrying_out_organization_id task_organization_id, t.task_number task_number, et.revenue_category_code revenue_Category, et.expenditure_category expenditure_category, ei.non_labor_resource non_labor_resource, projorg.organization_id proj_organization_id, projorg.name proj_organization_name, taskorg.name task_organization_name, t.service_type_code task_service_type, p.project_type project_type, p.segment1 project_number, prvorg.name prvdr_organization_name, rcvorg.name recvr_organization_name, p.org_id recvr_org_id, rcvou.name recvr_org_name, per.employee_number employee_number, per.person_id person_id, decode( sys.labor_non_labor_flag, 'Y', t.labor_tp_schedule_id, t.nl_tp_schedule_id) tp_schedule_id, decode( sys.labor_non_labor_flag, 'Y', t.labor_tp_fixed_date, t.nl_tp_fixed_date) tp_fixed_date, ei.tp_ind_compiled_set_id tp_ind_compiled_set_id, ei.tp_bill_rate tp_bill_rate, ei.tp_bill_markup_percentage tp_bill_markup_percentage, ei.tp_schedule_line_percentage tp_schedule_line_percentage, ei.tp_rule_percentage tp_rule_percentage, ei.cc_markup_base_code markup_calc_base_code, ei.tp_base_amount tp_base_amount, ei.denom_raw_cost denom_raw_cost, ei.denom_burdened_cost denom_burdened_cost, ei.raw_revenue raw_revenue, sys.labor_non_labor_flag labor_non_labor_flag, decode(fntrn1.enabled_flag, 'Y', fntrn1.function_transaction_code, 'ALL') fn_trn_code_borr, decode(fntrn1.enabled_flag, 'Y', decode(fntrn1.function_transaction_code, 'LAB', 1, 'USG', 2, 'ER', 3, 'SUP', 4, 'MIS', 5, 'INV', 6, 'WIP',7), 0 ) fn_trn_num_borr, decode( fntrn2.enabled_flag, 'Y', fntrn2.function_transaction_code, 'ALL') fn_trn_code_lent, decode(fntrn2.enabled_flag, 'Y', decode(fntrn2.function_transaction_code, 'LAB', 1, 'USG', 2, 'ER', 3, 'SUP', 4, 'MIS', 5, 'INV', 6, 'WIP',7), 0 ) fn_trn_num_lent, ei.org_id FROM pa_tasks t, pa_projects_all p, pa_expenditures_all exp, pa_expenditure_items_all ei, pa_expenditure_types et, pa_function_transactions fntrn1, pa_function_transactions fntrn2, pa_system_linkages sys, hr_all_organization_units_tl exporg, hr_all_organization_units_tl projorg, hr_all_organization_units_tl taskorg, hr_all_organization_units_tl prvorg, hr_all_organization_units_tl rcvorg, hr_all_organization_units_tl nlorg, hr_all_organization_units_tl rcvou, fnd_languages lang, pa_employees per WHERE exp.expenditure_status_code = 'APPROVED' AND exp.expenditure_id = ei.expenditure_id AND t.task_id = ei.task_id AND t.project_id = p.project_id AND ei. cc_bl_distributed_code = 'N' AND ei.cost_distributed_flag = 'Y' AND ei.system_linkage_function NOT IN ('BTC') AND et.expenditure_type = ei.expenditure_type AND ei.system_linkage_function = sys.function AND fntrn1.function_transaction_code = sys.cc_function_transaction_code AND fntrn1.function_code = 'CBR' AND fntrn2.function_code = 'CLN' AND fntrn2.function_transaction_code = sys.cc_function_transaction_code AND prvorg.organization_id = ei.cc_prvdr_organization_id AND rcvorg.organization_id = ei.cc_recvr_organization_id AND nlorg.organization_id(+) = ei.organization_id AND rcvou.organization_id = p.org_id AND exp.incurred_by_person_id= per.person_id(+) AND decode(ei.override_to_organization_id,null, exp.incurred_by_organization_id) = exporg.organization_id AND projorg.organization_id = p.carrying_out_organization_id AND taskorg.organization_id = t.carrying_out_organization_id AND exporg.language = lang.language_code AND projorg.language = lang.language_code AND taskorg.language = lang.language_code AND prvorg.language = lang.language_code AND rcvorg.language = lang.language_code AND decode(ei.organization_id, null, 'A', nlorg.language) = decode(ei.organization_id, null, 'A',lang.language_code) AND rcvou.language = lang.language_code AND lang.installed_flag = 'B' AND fntrn1.org_id = ei.org_id ORDER BY expenditure_item_id

View Text - HTML Formatted

SELECT ROWIDTOCHAR(EI.ROWID) EXP_ITEM_ROWID
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, T.PROJECT_ID PROJECT_ID
, DECODE(EI.CC_CROSS_CHARGE_CODE
, 'B'
, DECODE(ADJUSTED_EXPENDITURE_ITEM_ID
, NULL
, 'Y'
, 'N')
, 'N') CALC_TP_FLAG
, EI.ADJUSTED_EXPENDITURE_ITEM_ID ADJUSTED_EXP_ITEM_ID
, EI.SYSTEM_LINKAGE_FUNCTION SYSTEM_LINKAGE_FUNCTION
, EI.TASK_ID TASK_ID
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, EI.NET_ZERO_ADJUSTMENT_FLAG NET_ZERO_FLAG
, EI.QUANTITY QUANTITY
, EI.BILLABLE_FLAG BILLABLE_FLAG
, EI.ORGANIZATION_ID NL_RESOURCE_ORGANIZATION_ID
, NLORG.NAME NL_ORGANIZATION_NAME
, EI.CC_PRVDR_ORGANIZATION_ID PRVDR_ORGANIZATION_ID
, EI.CC_RECVR_ORGANIZATION_ID RECVR_ORGANIZATION_ID
, EI.DENOM_TP_CURRENCY_CODE DENOM_TP_CURRENCY_CODE
, EI.DENOM_CURRENCY_CODE DENOM_CURRENCY_CODE
, EI.DENOM_TRANSFER_PRICE DENOM_TRANSFER_PRICE
, EI.ACCT_CURRENCY_CODE ACCT_CURRENCY_CODE
, EI.ACCT_TP_RATE_TYPE ACCT_TP_RATE_TYPE
, EI.ACCT_TP_RATE_DATE ACCT_TP_RATE_DATE
, EI.ACCT_TP_EXCHANGE_RATE ACCT_TP_EXCHANGE_RATE
, EI.ACCT_TRANSFER_PRICE ACCT_TRANSFER_PRICE
, EI.PROJECT_CURRENCY_CODE PROJ_CURRENCY_CODE
, EI.CC_CROSS_CHARGE_CODE CROSS_CHARGE_CODE
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, EI.REVENUE_DISTRIBUTED_FLAG REVENUE_DISTRIBUTED_FLAG
, EI.JOB_ID JOB_ID
, EXP.EXPENDITURE_GROUP EXPENDITURE_GROUP
, EXP.EXPENDITURE_ENDING_DATE EXPENDITURE_ENDING_DATE
, PA_UTILS.GET_PA_DATE(EI.EXPENDITURE_ITEM_DATE
, SYSDATE) PA_DATE
, DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, EXPORG.NAME EXPENDITURE_ORGANIZATION_NAME
, T.CARRYING_OUT_ORGANIZATION_ID TASK_ORGANIZATION_ID
, T.TASK_NUMBER TASK_NUMBER
, ET.REVENUE_CATEGORY_CODE REVENUE_CATEGORY
, ET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY
, EI.NON_LABOR_RESOURCE NON_LABOR_RESOURCE
, PROJORG.ORGANIZATION_ID PROJ_ORGANIZATION_ID
, PROJORG.NAME PROJ_ORGANIZATION_NAME
, TASKORG.NAME TASK_ORGANIZATION_NAME
, T.SERVICE_TYPE_CODE TASK_SERVICE_TYPE
, P.PROJECT_TYPE PROJECT_TYPE
, P.SEGMENT1 PROJECT_NUMBER
, PRVORG.NAME PRVDR_ORGANIZATION_NAME
, RCVORG.NAME RECVR_ORGANIZATION_NAME
, P.ORG_ID RECVR_ORG_ID
, RCVOU.NAME RECVR_ORG_NAME
, PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PER.PERSON_ID PERSON_ID
, DECODE( SYS.LABOR_NON_LABOR_FLAG
, 'Y'
, T.LABOR_TP_SCHEDULE_ID
, T.NL_TP_SCHEDULE_ID) TP_SCHEDULE_ID
, DECODE( SYS.LABOR_NON_LABOR_FLAG
, 'Y'
, T.LABOR_TP_FIXED_DATE
, T.NL_TP_FIXED_DATE) TP_FIXED_DATE
, EI.TP_IND_COMPILED_SET_ID TP_IND_COMPILED_SET_ID
, EI.TP_BILL_RATE TP_BILL_RATE
, EI.TP_BILL_MARKUP_PERCENTAGE TP_BILL_MARKUP_PERCENTAGE
, EI.TP_SCHEDULE_LINE_PERCENTAGE TP_SCHEDULE_LINE_PERCENTAGE
, EI.TP_RULE_PERCENTAGE TP_RULE_PERCENTAGE
, EI.CC_MARKUP_BASE_CODE MARKUP_CALC_BASE_CODE
, EI.TP_BASE_AMOUNT TP_BASE_AMOUNT
, EI.DENOM_RAW_COST DENOM_RAW_COST
, EI.DENOM_BURDENED_COST DENOM_BURDENED_COST
, EI.RAW_REVENUE RAW_REVENUE
, SYS.LABOR_NON_LABOR_FLAG LABOR_NON_LABOR_FLAG
, DECODE(FNTRN1.ENABLED_FLAG
, 'Y'
, FNTRN1.FUNCTION_TRANSACTION_CODE
, 'ALL') FN_TRN_CODE_BORR
, DECODE(FNTRN1.ENABLED_FLAG
, 'Y'
, DECODE(FNTRN1.FUNCTION_TRANSACTION_CODE
, 'LAB'
, 1
, 'USG'
, 2
, 'ER'
, 3
, 'SUP'
, 4
, 'MIS'
, 5
, 'INV'
, 6
, 'WIP'
, 7)
, 0 ) FN_TRN_NUM_BORR
, DECODE( FNTRN2.ENABLED_FLAG
, 'Y'
, FNTRN2.FUNCTION_TRANSACTION_CODE
, 'ALL') FN_TRN_CODE_LENT
, DECODE(FNTRN2.ENABLED_FLAG
, 'Y'
, DECODE(FNTRN2.FUNCTION_TRANSACTION_CODE
, 'LAB'
, 1
, 'USG'
, 2
, 'ER'
, 3
, 'SUP'
, 4
, 'MIS'
, 5
, 'INV'
, 6
, 'WIP'
, 7)
, 0 ) FN_TRN_NUM_LENT
, EI.ORG_ID
FROM PA_TASKS T
, PA_PROJECTS_ALL P
, PA_EXPENDITURES_ALL EXP
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURE_TYPES ET
, PA_FUNCTION_TRANSACTIONS FNTRN1
, PA_FUNCTION_TRANSACTIONS FNTRN2
, PA_SYSTEM_LINKAGES SYS
, HR_ALL_ORGANIZATION_UNITS_TL EXPORG
, HR_ALL_ORGANIZATION_UNITS_TL PROJORG
, HR_ALL_ORGANIZATION_UNITS_TL TASKORG
, HR_ALL_ORGANIZATION_UNITS_TL PRVORG
, HR_ALL_ORGANIZATION_UNITS_TL RCVORG
, HR_ALL_ORGANIZATION_UNITS_TL NLORG
, HR_ALL_ORGANIZATION_UNITS_TL RCVOU
, FND_LANGUAGES LANG
, PA_EMPLOYEES PER
WHERE EXP.EXPENDITURE_STATUS_CODE = 'APPROVED'
AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND T.TASK_ID = EI.TASK_ID
AND T.PROJECT_ID = P.PROJECT_ID
AND EI. CC_BL_DISTRIBUTED_CODE = 'N'
AND EI.COST_DISTRIBUTED_FLAG = 'Y'
AND EI.SYSTEM_LINKAGE_FUNCTION NOT IN ('BTC')
AND ET.EXPENDITURE_TYPE = EI.EXPENDITURE_TYPE
AND EI.SYSTEM_LINKAGE_FUNCTION = SYS.FUNCTION
AND FNTRN1.FUNCTION_TRANSACTION_CODE = SYS.CC_FUNCTION_TRANSACTION_CODE
AND FNTRN1.FUNCTION_CODE = 'CBR'
AND FNTRN2.FUNCTION_CODE = 'CLN'
AND FNTRN2.FUNCTION_TRANSACTION_CODE = SYS.CC_FUNCTION_TRANSACTION_CODE
AND PRVORG.ORGANIZATION_ID = EI.CC_PRVDR_ORGANIZATION_ID
AND RCVORG.ORGANIZATION_ID = EI.CC_RECVR_ORGANIZATION_ID
AND NLORG.ORGANIZATION_ID(+) = EI.ORGANIZATION_ID
AND RCVOU.ORGANIZATION_ID = P.ORG_ID
AND EXP.INCURRED_BY_PERSON_ID= PER.PERSON_ID(+)
AND DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID) = EXPORG.ORGANIZATION_ID
AND PROJORG.ORGANIZATION_ID = P.CARRYING_OUT_ORGANIZATION_ID
AND TASKORG.ORGANIZATION_ID = T.CARRYING_OUT_ORGANIZATION_ID
AND EXPORG.LANGUAGE = LANG.LANGUAGE_CODE
AND PROJORG.LANGUAGE = LANG.LANGUAGE_CODE
AND TASKORG.LANGUAGE = LANG.LANGUAGE_CODE
AND PRVORG.LANGUAGE = LANG.LANGUAGE_CODE
AND RCVORG.LANGUAGE = LANG.LANGUAGE_CODE
AND DECODE(EI.ORGANIZATION_ID
, NULL
, 'A'
, NLORG.LANGUAGE) = DECODE(EI.ORGANIZATION_ID
, NULL
, 'A'
, LANG.LANGUAGE_CODE)
AND RCVOU.LANGUAGE = LANG.LANGUAGE_CODE
AND LANG.INSTALLED_FLAG = 'B'
AND FNTRN1.ORG_ID = EI.ORG_ID ORDER BY EXPENDITURE_ITEM_ID