VIEW: APPS.GMS_DRAFT_INV_LINE_DETAILS_V

Source

View Text - Preformatted

SELECT ei.ROWID, INT.expenditure_item_id, INT.adl_line_num, drf.project_id, drf.draft_invoice_num, drf.line_num, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), (adl.raw_cost * adl.distribution_value / 100) ), ei.project_currency_code, ei.project_rate_date, ei.project_rate_type, conv.user_conversion_type user_project_rate_type, ei.project_exchange_rate, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), (adl.raw_cost * adl.distribution_value / 100) ), ei.receipt_currency_code, ei.receipt_exchange_rate, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), (adl.raw_cost * adl.distribution_value / 100) ), DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), (adl.raw_cost * adl.distribution_value / 100) ), ei.denom_currency_code, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), (adl.raw_cost * adl.distribution_value / 100) ), DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', (adl.raw_cost * adl.distribution_value / 100 ), NULL ), (adl.raw_cost * adl.distribution_value / 100) ), ei.acct_currency_code, ei.acct_rate_date, ei.acct_rate_type, conv2.user_conversion_type user_acct_rate_type, ei.acct_exchange_rate, ei.acct_exchange_rounding_limit, INT.amount, TO_NUMBER (NULL), NULL, e.expenditure_id, ei.task_id, t.task_number, t.task_name, ei.expenditure_item_date, ei.expenditure_type, e.incurred_by_person_id, emp.full_name employee_name, emp.employee_number, ei.job_id, j.NAME job_name, NVL (ei.override_to_organization_id, e.incurred_by_organization_id), o1.NAME expenditure_organization_name, vend.vendor_id, vend.vendor_name, vend.segment1 vendor_number, NVL (emp.full_name, vend.vendor_name) employee_vendor_name, NVL (emp.employee_number, vend.segment1) employee_vendor_number, ei.non_labor_resource, ei.organization_id nlr_organization_id, o2.NAME nlr_organization_name, ei.system_linkage_function, sl.meaning, c.expenditure_comment, ei.transaction_source, ei.orig_transaction_reference, e.expenditure_group, e.expenditure_status_code, ei.quantity * (adl.distribution_value / 100) * DECODE (adl.line_num_reversed, NULL, 1, -1), adl.raw_cost * (adl.distribution_value / 100), et.unit_of_measure, l2.meaning unit_of_measure_m, adl.cost_distributed_flag, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', ei.raw_cost_rate, NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', ei.raw_cost_rate, NULL ), ei.raw_cost_rate ), ei.bill_rate, ei.bill_job_billing_title, ei.bill_employee_billing_title, ei.adjusted_expenditure_item_id, ei.net_zero_adjustment_flag, ei.transferred_from_exp_item_id, ei.converted_flag, ei.source_expenditure_item_id, drf.project_id, drf.event_task_id, drf.event_num, adl.billable_flag, adl.bill_hold_flag, adl.revenue_distributed_flag, ei.last_update_date, ei.last_updated_by, ei.last_update_login, tr.user_transaction_source, tr.allow_adjustments_flag, tr.costed_flag, tr.cost_burdened_flag, tr.gl_accounted_flag, ei.burden_sum_dest_run_id, cdl.burden_sum_rejection_code, l9.meaning burden_sum_rejection_m, p.project_status_code, cdl.system_reference2, 'FROM-EI', p.segment1, p.segment1, p.project_id, ei.created_by, ei.creation_date, ei.bill_trans_currency_code, ei.invproc_currency_code, ei.invproc_rate_type, ei.invproc_rate_date, ei.invproc_exchange_rate, ei.bill_amount, ei.projfunc_currency_code, gl_func.user_conversion_type projfunc_inv_rate_type, p.projfunc_cost_rate_date, ei.acct_exchange_rate, ei.projfunc_bill_amount, gl_func.user_conversion_type project_inv_rate_type, ei.project_rate_date, ei.acct_exchange_rate, ei.bill_amount, ei.acct_currency_code, gl_func.user_conversion_type funding_inv_rate_type, p.funding_rate_date, p.funding_exchange_rate, ei.bill_amount, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', ei.project_raw_cost, NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', ei.project_raw_cost, NULL ), ei.project_raw_cost ) project_raw_cost, ei.projfunc_cost_rate_date projfunc_cost_rate_date, gl_func.user_conversion_type projfunc_cost_rate_type, ei.projfunc_cost_exchange_rate projfunc_cost_exchange_rate, '', '', ei.denom_tp_currency_code, ei.acct_tp_rate_type, (SELECT NVL (user_conversion_type, ei.acct_tp_rate_type) FROM pa_conversion_types_v WHERE conversion_type = ei.acct_tp_rate_type), ei.acct_tp_rate_date, ei.acct_tp_exchange_rate, (SELECT NVL (user_conversion_type, ei.projfunc_cost_rate_type) FROM pa_conversion_types_v WHERE conversion_type = ei.projfunc_cost_rate_type), ei.org_id, ei.recvr_org_id, ei.cc_cross_charge_code, ei.cc_cross_charge_type, ei.cc_bl_distributed_code, ei.cc_ic_processed_code, ei.cc_rejection_code, ei.org_id, ei.work_type_id, pa_utils4.get_work_type_name (ei.work_type_id), ei.tp_amt_type_code, pl.meaning, '', TO_NUMBER (NULL), TO_CHAR (NULL), TO_CHAR (NULL), TO_DATE (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_CHAR (NULL), TO_CHAR (NULL), TO_DATE (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_CHAR (NULL), TO_DATE (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_CHAR (NULL), TO_NUMBER (NULL), dri.org_id, EI.DOCUMENT_HEADER_ID, EI.DOCUMENT_LINE_NUMBER, EI.DOCUMENT_DISTRIBUTION_ID, EI.DOCUMENT_TYPE, EI.DOCUMENT_DISTRIBUTION_TYPE, EI.DOCUMENT_PAYMENT_ID, TR.ALLOW_BURDEN_FLAG FROM gms_event_intersect INT, pa_draft_invoice_items drf, pa_draft_invoices dri, pa_expenditure_items_all ei, gms_award_distributions adl, pa_tasks t, pa_projects_all p, pa_expenditures_all e, pa_expenditure_types et, pa_expenditure_comments c, pa_system_linkages sl, pa_cost_distribution_lines_all cdl, per_jobs j, per_people_f emp, po_vendors vend, pa_transaction_sources tr, hr_all_organization_units_tl o1, hr_all_organization_units_tl o2, fnd_lookup_values l2, fnd_lookup_values l9, pa_conversion_types_v conv, pa_conversion_types_v conv2, gl_daily_conversion_types gl_func, pa_lookups pl WHERE dri.draft_invoice_num = drf.draft_invoice_num AND dri.project_id = drf.project_id AND NVL(dri.write_off_flag, 'N') <> 'Y' AND NVL(dri.cancel_credit_memo_flag, 'N') <> 'Y' AND NVL(dri.concession_flag,'N') <> 'Y' AND INT.award_project_id = drf.project_id AND INT.event_num = drf.event_num AND INT.event_type = 'INVOICE' AND ei.expenditure_item_id = INT.expenditure_item_id AND INT.expenditure_item_id = adl.expenditure_item_id AND INT.adl_line_num = adl.adl_line_num AND adl.document_type = 'EXP' AND adl.adl_status = 'A' AND t.task_id = ei.task_id AND p.project_id = t.project_id AND e.expenditure_id = ei.expenditure_id AND ei.expenditure_item_id = c.expenditure_item_id(+) AND ei.expenditure_type = et.expenditure_type AND sl.FUNCTION = ei.system_linkage_function AND cdl.expenditure_item_id = adl.expenditure_item_id AND cdl.line_num = adl.cdl_line_num AND ei.job_id = j.job_id(+) AND e.incurred_by_person_id = emp.person_id(+) AND emp.employee_number(+) IS NOT NULL AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+) AND EI.VENDOR_ID = vend.vendor_id(+) AND ei.transaction_source = tr.transaction_source(+) AND NVL (ei.override_to_organization_id, e.incurred_by_organization_id) = o1.organization_id AND o1.LANGUAGE = USERENV ('LANG') AND ei.organization_id = o2.organization_id(+) AND o2.LANGUAGE(+) = USERENV ('LANG') AND l2.lookup_type = 'UNIT' AND l2.lookup_code = et.unit_of_measure AND l2.view_application_id = 275 AND l2.LANGUAGE = USERENV ('LANG') AND l9.lookup_type(+) = 'BURDEN SUM REJECTION CODE' AND l9.lookup_code(+) = cdl.burden_sum_rejection_code AND l9.view_application_id(+) = 275 AND l9.LANGUAGE(+) = USERENV ('LANG') AND ei.acct_rate_type = conv.conversion_type(+) AND ei.project_rate_type = conv2.conversion_type(+) AND ei.projfunc_cost_rate_type = gl_func.conversion_type(+) AND pl.lookup_type = 'TRANSACTION_TYPE' AND pl.lookup_code = 'EXP_ITEM' UNION ALL SELECT ei.ROWID, bc.expenditure_item_id, bc.adl_line_num, drf.project_id, drf.draft_invoice_num, drf.line_num, TO_NUMBER (NULL) raw_cost, ei.project_currency_code, ei.project_rate_date, ei.project_rate_type, conv2.user_conversion_type user_project_rate_type, ei.project_exchange_rate, TO_NUMBER (NULL), NULL, TO_NUMBER (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), NULL, TO_NUMBER (NULL), TO_NUMBER (NULL), NULL, TO_DATE (NULL), NULL, NULL, TO_NUMBER (NULL), TO_NUMBER (NULL), bc.amount bill_amount, TO_NUMBER (NULL), NULL, e.expenditure_id, bc.actual_task_id, t.task_number, t.task_name, ei.expenditure_item_date, bc.burden_exp_type, e.incurred_by_person_id, emp.full_name employee_name, emp.employee_number, ei.job_id, j.NAME job_name, bc.expenditure_org_id, o.NAME, TO_NUMBER (NULL), NULL, NULL, NULL, NULL, NULL, TO_NUMBER (NULL), NULL, NULL, NULL, NULL, ei.expenditure_type, bc.burden_cost_code, e.expenditure_group, NULL, TO_NUMBER (NULL), bc.amount, NULL, NULL, NULL, TO_NUMBER (NULL), TO_NUMBER (NULL), NULL, NULL, TO_NUMBER (NULL), NULL, TO_NUMBER (NULL), NULL, TO_NUMBER (NULL), drf.project_id, drf.event_task_id, drf.event_num, NULL, NULL, NULL, ei.last_update_date, ei.last_updated_by, ei.last_update_login, ei.expenditure_type, NULL, NULL, NULL, NULL, TO_NUMBER (NULL), NULL, NULL, NULL, NULL, 'FROM-EI', p.segment1 project_number, p.NAME project_name, bc.actual_project_id, ei.created_by, ei.creation_date, ei.bill_trans_currency_code, ei.invproc_currency_code, ei.invproc_rate_type, ei.invproc_rate_date, ei.invproc_exchange_rate, ei.bill_amount, ei.projfunc_currency_code, gl_func.user_conversion_type projfunc_inv_rate_type, p.projfunc_cost_rate_date, ei.acct_exchange_rate, ei.projfunc_bill_amount, gl_func.user_conversion_type project_inv_rate_type, ei.project_rate_date, ei.acct_exchange_rate, ei.bill_amount, ei.acct_currency_code, gl_func.user_conversion_type funding_inv_rate_type, p.funding_rate_date, p.funding_exchange_rate, ei.bill_amount, DECODE (ei.system_linkage_function, 'ST', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', ei.project_raw_cost, NULL ), 'OT', DECODE (pa_security.view_labor_costs (drf.project_id), 'Y', ei.project_raw_cost, NULL ), ei.project_raw_cost ) project_raw_cost, ei.projfunc_cost_rate_date projfunc_cost_rate_date, gl_func.user_conversion_type projfunc_cost_rate_type, ei.projfunc_cost_exchange_rate projfunc_cost_exchange_rate, '', '', ei.denom_tp_currency_code, ei.acct_tp_rate_type, (SELECT NVL (user_conversion_type, ei.acct_tp_rate_type) FROM pa_conversion_types_v WHERE conversion_type = ei.acct_tp_rate_type), ei.acct_tp_rate_date, ei.acct_tp_exchange_rate, (SELECT NVL (user_conversion_type, ei.projfunc_cost_rate_type) FROM pa_conversion_types_v WHERE conversion_type = ei.projfunc_cost_rate_type), ei.org_id, ei.recvr_org_id, ei.cc_cross_charge_code, ei.cc_cross_charge_type, ei.cc_bl_distributed_code, ei.cc_ic_processed_code, ei.cc_rejection_code, ei.org_id, ei.work_type_id, pa_utils4.get_work_type_name (ei.work_type_id), ei.tp_amt_type_code, pl.meaning, '', TO_NUMBER (NULL), TO_CHAR (NULL), TO_CHAR (NULL), TO_DATE (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_CHAR (NULL), TO_CHAR (NULL), TO_DATE (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_CHAR (NULL), TO_DATE (NULL), TO_NUMBER (NULL), TO_NUMBER (NULL), TO_CHAR (NULL), TO_NUMBER (NULL), dri.org_id, EI.DOCUMENT_HEADER_ID, EI.DOCUMENT_LINE_NUMBER, EI.DOCUMENT_DISTRIBUTION_ID, EI.DOCUMENT_TYPE, EI.DOCUMENT_DISTRIBUTION_TYPE, EI.DOCUMENT_PAYMENT_ID, NULL FROM gms_burden_components bc, pa_draft_invoice_items drf, pa_draft_invoices dri, pa_projects_all p, pa_tasks t, pa_expenditure_items_all ei, gms_award_distributions adl, hr_organization_units o, pa_expenditures_all e, per_people_f emp, per_jobs j, pa_conversion_types_v conv2, gl_daily_conversion_types gl_func, pa_lookups pl WHERE dri.draft_invoice_num = drf.draft_invoice_num AND dri.project_id = drf.project_id AND NVL(dri.write_off_flag, 'N') <> 'Y' AND NVL(dri.cancel_credit_memo_flag, 'N') <> 'Y' AND NVL(dri.concession_flag,'N') <> 'Y' AND bc.award_project_id = dri.project_id AND bc.event_num = drf.event_num AND bc.event_type = 'INVOICE' AND ei.expenditure_item_id = bc.expenditure_item_id AND e.expenditure_id = ei.expenditure_id AND adl.expenditure_item_id = bc.expenditure_item_id AND adl.adl_line_num = bc.adl_line_num AND adl.document_type = 'EXP' AND adl.adl_status = 'A' AND t.task_id = bc.actual_task_id AND p.project_id = bc.actual_project_id AND o.organization_id = bc.expenditure_org_id AND ei.job_id = j.job_id(+) AND e.incurred_by_person_id = emp.person_id(+) AND emp.employee_number(+) IS NOT NULL AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+) AND ei.project_rate_type = conv2.conversion_type(+) AND ei.projfunc_cost_rate_type = gl_func.conversion_type(+) AND pl.lookup_type = 'TRANSACTION_TYPE' AND pl.lookup_code = 'EXP_ITEM'

View Text - HTML Formatted

SELECT EI.ROWID
, INT.EXPENDITURE_ITEM_ID
, INT.ADL_LINE_NUM
, DRF.PROJECT_ID
, DRF.DRAFT_INVOICE_NUM
, DRF.LINE_NUM
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100) )
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_RATE_TYPE
, CONV.USER_CONVERSION_TYPE USER_PROJECT_RATE_TYPE
, EI.PROJECT_EXCHANGE_RATE
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100) )
, EI.RECEIPT_CURRENCY_CODE
, EI.RECEIPT_EXCHANGE_RATE
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100) )
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100) )
, EI.DENOM_CURRENCY_CODE
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100) )
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100 )
, NULL )
, (ADL.RAW_COST * ADL.DISTRIBUTION_VALUE / 100) )
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RATE_DATE
, EI.ACCT_RATE_TYPE
, CONV2.USER_CONVERSION_TYPE USER_ACCT_RATE_TYPE
, EI.ACCT_EXCHANGE_RATE
, EI.ACCT_EXCHANGE_ROUNDING_LIMIT
, INT.AMOUNT
, TO_NUMBER (NULL)
, NULL
, E.EXPENDITURE_ID
, EI.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, EI.EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE
, E.INCURRED_BY_PERSON_ID
, EMP.FULL_NAME EMPLOYEE_NAME
, EMP.EMPLOYEE_NUMBER
, EI.JOB_ID
, J.NAME JOB_NAME
, NVL (EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID)
, O1.NAME EXPENDITURE_ORGANIZATION_NAME
, VEND.VENDOR_ID
, VEND.VENDOR_NAME
, VEND.SEGMENT1 VENDOR_NUMBER
, NVL (EMP.FULL_NAME
, VEND.VENDOR_NAME) EMPLOYEE_VENDOR_NAME
, NVL (EMP.EMPLOYEE_NUMBER
, VEND.SEGMENT1) EMPLOYEE_VENDOR_NUMBER
, EI.NON_LABOR_RESOURCE
, EI.ORGANIZATION_ID NLR_ORGANIZATION_ID
, O2.NAME NLR_ORGANIZATION_NAME
, EI.SYSTEM_LINKAGE_FUNCTION
, SL.MEANING
, C.EXPENDITURE_COMMENT
, EI.TRANSACTION_SOURCE
, EI.ORIG_TRANSACTION_REFERENCE
, E.EXPENDITURE_GROUP
, E.EXPENDITURE_STATUS_CODE
, EI.QUANTITY * (ADL.DISTRIBUTION_VALUE / 100) * DECODE (ADL.LINE_NUM_REVERSED
, NULL
, 1
, -1)
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE / 100)
, ET.UNIT_OF_MEASURE
, L2.MEANING UNIT_OF_MEASURE_M
, ADL.COST_DISTRIBUTED_FLAG
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, EI.RAW_COST_RATE
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, EI.RAW_COST_RATE
, NULL )
, EI.RAW_COST_RATE )
, EI.BILL_RATE
, EI.BILL_JOB_BILLING_TITLE
, EI.BILL_EMPLOYEE_BILLING_TITLE
, EI.ADJUSTED_EXPENDITURE_ITEM_ID
, EI.NET_ZERO_ADJUSTMENT_FLAG
, EI.TRANSFERRED_FROM_EXP_ITEM_ID
, EI.CONVERTED_FLAG
, EI.SOURCE_EXPENDITURE_ITEM_ID
, DRF.PROJECT_ID
, DRF.EVENT_TASK_ID
, DRF.EVENT_NUM
, ADL.BILLABLE_FLAG
, ADL.BILL_HOLD_FLAG
, ADL.REVENUE_DISTRIBUTED_FLAG
, EI.LAST_UPDATE_DATE
, EI.LAST_UPDATED_BY
, EI.LAST_UPDATE_LOGIN
, TR.USER_TRANSACTION_SOURCE
, TR.ALLOW_ADJUSTMENTS_FLAG
, TR.COSTED_FLAG
, TR.COST_BURDENED_FLAG
, TR.GL_ACCOUNTED_FLAG
, EI.BURDEN_SUM_DEST_RUN_ID
, CDL.BURDEN_SUM_REJECTION_CODE
, L9.MEANING BURDEN_SUM_REJECTION_M
, P.PROJECT_STATUS_CODE
, CDL.SYSTEM_REFERENCE2
, 'FROM-EI'
, P.SEGMENT1
, P.SEGMENT1
, P.PROJECT_ID
, EI.CREATED_BY
, EI.CREATION_DATE
, EI.BILL_TRANS_CURRENCY_CODE
, EI.INVPROC_CURRENCY_CODE
, EI.INVPROC_RATE_TYPE
, EI.INVPROC_RATE_DATE
, EI.INVPROC_EXCHANGE_RATE
, EI.BILL_AMOUNT
, EI.PROJFUNC_CURRENCY_CODE
, GL_FUNC.USER_CONVERSION_TYPE PROJFUNC_INV_RATE_TYPE
, P.PROJFUNC_COST_RATE_DATE
, EI.ACCT_EXCHANGE_RATE
, EI.PROJFUNC_BILL_AMOUNT
, GL_FUNC.USER_CONVERSION_TYPE PROJECT_INV_RATE_TYPE
, EI.PROJECT_RATE_DATE
, EI.ACCT_EXCHANGE_RATE
, EI.BILL_AMOUNT
, EI.ACCT_CURRENCY_CODE
, GL_FUNC.USER_CONVERSION_TYPE FUNDING_INV_RATE_TYPE
, P.FUNDING_RATE_DATE
, P.FUNDING_EXCHANGE_RATE
, EI.BILL_AMOUNT
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, EI.PROJECT_RAW_COST
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, EI.PROJECT_RAW_COST
, NULL )
, EI.PROJECT_RAW_COST ) PROJECT_RAW_COST
, EI.PROJFUNC_COST_RATE_DATE PROJFUNC_COST_RATE_DATE
, GL_FUNC.USER_CONVERSION_TYPE PROJFUNC_COST_RATE_TYPE
, EI.PROJFUNC_COST_EXCHANGE_RATE PROJFUNC_COST_EXCHANGE_RATE
, ''
, ''
, EI.DENOM_TP_CURRENCY_CODE
, EI.ACCT_TP_RATE_TYPE
, (SELECT NVL (USER_CONVERSION_TYPE
, EI.ACCT_TP_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.ACCT_TP_RATE_TYPE)
, EI.ACCT_TP_RATE_DATE
, EI.ACCT_TP_EXCHANGE_RATE
, (SELECT NVL (USER_CONVERSION_TYPE
, EI.PROJFUNC_COST_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.PROJFUNC_COST_RATE_TYPE)
, EI.ORG_ID
, EI.RECVR_ORG_ID
, EI.CC_CROSS_CHARGE_CODE
, EI.CC_CROSS_CHARGE_TYPE
, EI.CC_BL_DISTRIBUTED_CODE
, EI.CC_IC_PROCESSED_CODE
, EI.CC_REJECTION_CODE
, EI.ORG_ID
, EI.WORK_TYPE_ID
, PA_UTILS4.GET_WORK_TYPE_NAME (EI.WORK_TYPE_ID)
, EI.TP_AMT_TYPE_CODE
, PL.MEANING
, ''
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_CHAR (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_CHAR (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_NUMBER (NULL)
, DRI.ORG_ID
, EI.DOCUMENT_HEADER_ID
, EI.DOCUMENT_LINE_NUMBER
, EI.DOCUMENT_DISTRIBUTION_ID
, EI.DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE
, EI.DOCUMENT_PAYMENT_ID
, TR.ALLOW_BURDEN_FLAG
FROM GMS_EVENT_INTERSECT INT
, PA_DRAFT_INVOICE_ITEMS DRF
, PA_DRAFT_INVOICES DRI
, PA_EXPENDITURE_ITEMS_ALL EI
, GMS_AWARD_DISTRIBUTIONS ADL
, PA_TASKS T
, PA_PROJECTS_ALL P
, PA_EXPENDITURES_ALL E
, PA_EXPENDITURE_TYPES ET
, PA_EXPENDITURE_COMMENTS C
, PA_SYSTEM_LINKAGES SL
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, PER_JOBS J
, PER_PEOPLE_F EMP
, PO_VENDORS VEND
, PA_TRANSACTION_SOURCES TR
, HR_ALL_ORGANIZATION_UNITS_TL O1
, HR_ALL_ORGANIZATION_UNITS_TL O2
, FND_LOOKUP_VALUES L2
, FND_LOOKUP_VALUES L9
, PA_CONVERSION_TYPES_V CONV
, PA_CONVERSION_TYPES_V CONV2
, GL_DAILY_CONVERSION_TYPES GL_FUNC
, PA_LOOKUPS PL
WHERE DRI.DRAFT_INVOICE_NUM = DRF.DRAFT_INVOICE_NUM
AND DRI.PROJECT_ID = DRF.PROJECT_ID
AND NVL(DRI.WRITE_OFF_FLAG
, 'N') <> 'Y'
AND NVL(DRI.CANCEL_CREDIT_MEMO_FLAG
, 'N') <> 'Y'
AND NVL(DRI.CONCESSION_FLAG
, 'N') <> 'Y'
AND INT.AWARD_PROJECT_ID = DRF.PROJECT_ID
AND INT.EVENT_NUM = DRF.EVENT_NUM
AND INT.EVENT_TYPE = 'INVOICE'
AND EI.EXPENDITURE_ITEM_ID = INT.EXPENDITURE_ITEM_ID
AND INT.EXPENDITURE_ITEM_ID = ADL.EXPENDITURE_ITEM_ID
AND INT.ADL_LINE_NUM = ADL.ADL_LINE_NUM
AND ADL.DOCUMENT_TYPE = 'EXP'
AND ADL.ADL_STATUS = 'A'
AND T.TASK_ID = EI.TASK_ID
AND P.PROJECT_ID = T.PROJECT_ID
AND E.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND EI.EXPENDITURE_ITEM_ID = C.EXPENDITURE_ITEM_ID(+)
AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND SL.FUNCTION = EI.SYSTEM_LINKAGE_FUNCTION
AND CDL.EXPENDITURE_ITEM_ID = ADL.EXPENDITURE_ITEM_ID
AND CDL.LINE_NUM = ADL.CDL_LINE_NUM
AND EI.JOB_ID = J.JOB_ID(+)
AND E.INCURRED_BY_PERSON_ID = EMP.PERSON_ID(+)
AND EMP.EMPLOYEE_NUMBER(+) IS NOT NULL
AND TRUNC (SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+)
AND EMP.EFFECTIVE_END_DATE(+)
AND EI.VENDOR_ID = VEND.VENDOR_ID(+)
AND EI.TRANSACTION_SOURCE = TR.TRANSACTION_SOURCE(+)
AND NVL (EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID
AND O1.LANGUAGE = USERENV ('LANG')
AND EI.ORGANIZATION_ID = O2.ORGANIZATION_ID(+)
AND O2.LANGUAGE(+) = USERENV ('LANG')
AND L2.LOOKUP_TYPE = 'UNIT'
AND L2.LOOKUP_CODE = ET.UNIT_OF_MEASURE
AND L2.VIEW_APPLICATION_ID = 275
AND L2.LANGUAGE = USERENV ('LANG')
AND L9.LOOKUP_TYPE(+) = 'BURDEN SUM REJECTION CODE'
AND L9.LOOKUP_CODE(+) = CDL.BURDEN_SUM_REJECTION_CODE
AND L9.VIEW_APPLICATION_ID(+) = 275
AND L9.LANGUAGE(+) = USERENV ('LANG')
AND EI.ACCT_RATE_TYPE = CONV.CONVERSION_TYPE(+)
AND EI.PROJECT_RATE_TYPE = CONV2.CONVERSION_TYPE(+)
AND EI.PROJFUNC_COST_RATE_TYPE = GL_FUNC.CONVERSION_TYPE(+)
AND PL.LOOKUP_TYPE = 'TRANSACTION_TYPE'
AND PL.LOOKUP_CODE = 'EXP_ITEM' UNION ALL SELECT EI.ROWID
, BC.EXPENDITURE_ITEM_ID
, BC.ADL_LINE_NUM
, DRF.PROJECT_ID
, DRF.DRAFT_INVOICE_NUM
, DRF.LINE_NUM
, TO_NUMBER (NULL) RAW_COST
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_RATE_TYPE
, CONV2.USER_CONVERSION_TYPE USER_PROJECT_RATE_TYPE
, EI.PROJECT_EXCHANGE_RATE
, TO_NUMBER (NULL)
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, TO_DATE (NULL)
, NULL
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, BC.AMOUNT BILL_AMOUNT
, TO_NUMBER (NULL)
, NULL
, E.EXPENDITURE_ID
, BC.ACTUAL_TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, EI.EXPENDITURE_ITEM_DATE
, BC.BURDEN_EXP_TYPE
, E.INCURRED_BY_PERSON_ID
, EMP.FULL_NAME EMPLOYEE_NAME
, EMP.EMPLOYEE_NUMBER
, EI.JOB_ID
, J.NAME JOB_NAME
, BC.EXPENDITURE_ORG_ID
, O.NAME
, TO_NUMBER (NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, NULL
, NULL
, NULL
, NULL
, EI.EXPENDITURE_TYPE
, BC.BURDEN_COST_CODE
, E.EXPENDITURE_GROUP
, NULL
, TO_NUMBER (NULL)
, BC.AMOUNT
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, NULL
, TO_NUMBER (NULL)
, NULL
, TO_NUMBER (NULL)
, NULL
, TO_NUMBER (NULL)
, DRF.PROJECT_ID
, DRF.EVENT_TASK_ID
, DRF.EVENT_NUM
, NULL
, NULL
, NULL
, EI.LAST_UPDATE_DATE
, EI.LAST_UPDATED_BY
, EI.LAST_UPDATE_LOGIN
, EI.EXPENDITURE_TYPE
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, NULL
, NULL
, NULL
, NULL
, 'FROM-EI'
, P.SEGMENT1 PROJECT_NUMBER
, P.NAME PROJECT_NAME
, BC.ACTUAL_PROJECT_ID
, EI.CREATED_BY
, EI.CREATION_DATE
, EI.BILL_TRANS_CURRENCY_CODE
, EI.INVPROC_CURRENCY_CODE
, EI.INVPROC_RATE_TYPE
, EI.INVPROC_RATE_DATE
, EI.INVPROC_EXCHANGE_RATE
, EI.BILL_AMOUNT
, EI.PROJFUNC_CURRENCY_CODE
, GL_FUNC.USER_CONVERSION_TYPE PROJFUNC_INV_RATE_TYPE
, P.PROJFUNC_COST_RATE_DATE
, EI.ACCT_EXCHANGE_RATE
, EI.PROJFUNC_BILL_AMOUNT
, GL_FUNC.USER_CONVERSION_TYPE PROJECT_INV_RATE_TYPE
, EI.PROJECT_RATE_DATE
, EI.ACCT_EXCHANGE_RATE
, EI.BILL_AMOUNT
, EI.ACCT_CURRENCY_CODE
, GL_FUNC.USER_CONVERSION_TYPE FUNDING_INV_RATE_TYPE
, P.FUNDING_RATE_DATE
, P.FUNDING_EXCHANGE_RATE
, EI.BILL_AMOUNT
, DECODE (EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, EI.PROJECT_RAW_COST
, NULL )
, 'OT'
, DECODE (PA_SECURITY.VIEW_LABOR_COSTS (DRF.PROJECT_ID)
, 'Y'
, EI.PROJECT_RAW_COST
, NULL )
, EI.PROJECT_RAW_COST ) PROJECT_RAW_COST
, EI.PROJFUNC_COST_RATE_DATE PROJFUNC_COST_RATE_DATE
, GL_FUNC.USER_CONVERSION_TYPE PROJFUNC_COST_RATE_TYPE
, EI.PROJFUNC_COST_EXCHANGE_RATE PROJFUNC_COST_EXCHANGE_RATE
, ''
, ''
, EI.DENOM_TP_CURRENCY_CODE
, EI.ACCT_TP_RATE_TYPE
, (SELECT NVL (USER_CONVERSION_TYPE
, EI.ACCT_TP_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.ACCT_TP_RATE_TYPE)
, EI.ACCT_TP_RATE_DATE
, EI.ACCT_TP_EXCHANGE_RATE
, (SELECT NVL (USER_CONVERSION_TYPE
, EI.PROJFUNC_COST_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.PROJFUNC_COST_RATE_TYPE)
, EI.ORG_ID
, EI.RECVR_ORG_ID
, EI.CC_CROSS_CHARGE_CODE
, EI.CC_CROSS_CHARGE_TYPE
, EI.CC_BL_DISTRIBUTED_CODE
, EI.CC_IC_PROCESSED_CODE
, EI.CC_REJECTION_CODE
, EI.ORG_ID
, EI.WORK_TYPE_ID
, PA_UTILS4.GET_WORK_TYPE_NAME (EI.WORK_TYPE_ID)
, EI.TP_AMT_TYPE_CODE
, PL.MEANING
, ''
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_CHAR (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_CHAR (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, TO_NUMBER (NULL)
, DRI.ORG_ID
, EI.DOCUMENT_HEADER_ID
, EI.DOCUMENT_LINE_NUMBER
, EI.DOCUMENT_DISTRIBUTION_ID
, EI.DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE
, EI.DOCUMENT_PAYMENT_ID
, NULL
FROM GMS_BURDEN_COMPONENTS BC
, PA_DRAFT_INVOICE_ITEMS DRF
, PA_DRAFT_INVOICES DRI
, PA_PROJECTS_ALL P
, PA_TASKS T
, PA_EXPENDITURE_ITEMS_ALL EI
, GMS_AWARD_DISTRIBUTIONS ADL
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURES_ALL E
, PER_PEOPLE_F EMP
, PER_JOBS J
, PA_CONVERSION_TYPES_V CONV2
, GL_DAILY_CONVERSION_TYPES GL_FUNC
, PA_LOOKUPS PL
WHERE DRI.DRAFT_INVOICE_NUM = DRF.DRAFT_INVOICE_NUM
AND DRI.PROJECT_ID = DRF.PROJECT_ID
AND NVL(DRI.WRITE_OFF_FLAG
, 'N') <> 'Y'
AND NVL(DRI.CANCEL_CREDIT_MEMO_FLAG
, 'N') <> 'Y'
AND NVL(DRI.CONCESSION_FLAG
, 'N') <> 'Y'
AND BC.AWARD_PROJECT_ID = DRI.PROJECT_ID
AND BC.EVENT_NUM = DRF.EVENT_NUM
AND BC.EVENT_TYPE = 'INVOICE'
AND EI.EXPENDITURE_ITEM_ID = BC.EXPENDITURE_ITEM_ID
AND E.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND ADL.EXPENDITURE_ITEM_ID = BC.EXPENDITURE_ITEM_ID
AND ADL.ADL_LINE_NUM = BC.ADL_LINE_NUM
AND ADL.DOCUMENT_TYPE = 'EXP'
AND ADL.ADL_STATUS = 'A'
AND T.TASK_ID = BC.ACTUAL_TASK_ID
AND P.PROJECT_ID = BC.ACTUAL_PROJECT_ID
AND O.ORGANIZATION_ID = BC.EXPENDITURE_ORG_ID
AND EI.JOB_ID = J.JOB_ID(+)
AND E.INCURRED_BY_PERSON_ID = EMP.PERSON_ID(+)
AND EMP.EMPLOYEE_NUMBER(+) IS NOT NULL
AND TRUNC (SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+)
AND EMP.EFFECTIVE_END_DATE(+)
AND EI.PROJECT_RATE_TYPE = CONV2.CONVERSION_TYPE(+)
AND EI.PROJFUNC_COST_RATE_TYPE = GL_FUNC.CONVERSION_TYPE(+)
AND PL.LOOKUP_TYPE = 'TRANSACTION_TYPE'
AND PL.LOOKUP_CODE = 'EXP_ITEM'