ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 91C259C62B0C4A0791E97F46FAE4D077Y select null action_, null status_, null message_, null modified_columns_, haouv.name operating_unit, ppa.segment1 project_number, ppa.name project_name, pt.task_number, pt.task_name, peia.expenditure_item_date, peia.expenditure_type, pet.expenditure_category, (select psl.meaning from pa_system_linkages psl where peia.system_linkage_function=psl.function) expenditure_type_class, (select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where pea.incurred_by_person_id=papf.person_id) employee_name, (select max(nvl(papf.employee_number,papf.npw_number)) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where pea.incurred_by_person_id=papf.person_id) employee_number, (select haouv2.name from hr_all_organization_units_vl haouv2 where nvl(peia.override_to_organization_id,pea.incurred_by_organization_id)=haouv2.organization_id) expenditure_organization, peia.non_labor_resource, peia.quantity, xxen_util.meaning(peia.unit_of_measure,'UNIT',275) unit_of_measure, peia.projfunc_currency_code project_functional_currency, peia.raw_cost, peia.burden_cost burdened_cost, xxen_util.yes(peia.billable_flag) billable, (select pts.user_transaction_source from pa_transaction_sources pts where peia.transaction_source=pts.transaction_source) transaction_source, peia.orig_transaction_reference original_transaction_reference, pea.expenditure_group expenditure_batch, (select pec.expenditure_comment from pa_expenditure_comments pec where peia.expenditure_item_id=pec.expenditure_item_id) expenditure_comment, null to_project_number, null to_project_name, null to_task_number, null to_task_name, peia.expenditure_item_id, peia.transferred_from_exp_item_id transferred_from_item_id, to_number(null) upload_row from hr_all_organization_units_vl haouv, pa_expenditure_items_all peia, pa_expenditures_all pea, pa_projects_all ppa, pa_tasks pt, pa_expenditure_types pet where 1=1 and :p_upload_mode like '%'||xxen_upload.action_update and haouv.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and haouv.organization_id=peia.org_id and nvl(peia.net_zero_adjustment_flag,'N')='N' and peia.adjusted_expenditure_item_id is null and peia.source_expenditure_item_id is null and peia.expenditure_id=pea.expenditure_id and peia.project_id=ppa.project_id and peia.task_id=pt.task_id and peia.expenditure_type=pet.expenditure_type Initial version. U A xxen_pa_upload.upload_expenditure_transfer US PA Expenditure Item Transfer Upload Transfers expenditure items to another project or task, as the Transfer action of the Expenditure Items window (Expenditure Inquiry) does, through pa_adjustments. Enter a To Task, and a To Project for a transfer to another project, on the rows to transfer. Each transferred item is reversed on its current task and recreated on the destination task, which takes its billable or capitalizable flag and transaction controls from there. The result shows the new item, with the original item in Transferred From Item Id. Rows without a To Task are not transferred. The download lists the items that are not reversed, reversing or related items. An item is rejected with Oracle's message where the window refuses the transfer, such as an unapproved item, an item already transferred or a date outside the destination project or task dates. Enginatics R12 only Upload 1=1 :p_upload_mode 1 10 :p_upload_mode LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 haouv.name=:p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 3 30 1=1 ppa.segment1=:project_number LOV custom select ppa.segment1 value, ppa.name description from hr_all_organization_units_vl haouv, pa_projects_all ppa where haouv.organization_id=ppa.org_id and nvl(ppa.template_flag,'N')='N' and haouv.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') order by value US Project 4 40 1=1 pt.task_number=:task_number LOV custom select pt.task_number value, pt.task_name description from pa_tasks pt, pa_projects_all ppa where xxen_util.contains(:$flex$.project,ppa.segment1)='Y' and ppa.project_id=pt.project_id order by pt.task_number US Task 5 50 1=1 peia.expenditure_item_date>=:expenditure_item_date_from Date US Expenditure Item Date From 6 60 1=1 peia.expenditure_item_date<:expenditure_item_date_to+1 Date US Expenditure Item Date To 7 70 1=1 peia.system_linkage_function=:expenditure_type_class LOV PA Expenditure Type Class 575A10692DD24F8CB449968D0BCDBC97 select psl.function id, psl.meaning value, null description from pa_system_linkages psl order by psl.meaning US Expenditure Type Class 8 80 1=1 pet.expenditure_category=:expenditure_category LOV custom select pec.expenditure_category value, pec.description from pa_expenditure_categories pec order by value US Expenditure Category 9 90 1=1 peia.expenditure_type=:expenditure_type LOV custom select pet.expenditure_type value, pet.description from pa_expenditure_types pet where (:$flex$.expenditure_category is null or xxen_util.contains(:$flex$.expenditure_category,pet.expenditure_category)='Y') order by value US Expenditure Type 10 100 1=1 pea.incurred_by_person_id=:employee_id LOV PA Employee 9E82A52DDBF04F079A5EC872CC2AF34D select pe.person_id id, pe.full_name value, pe.employee_number description from pa_employees pe where pe.active='*' order by pe.full_name US Employee 11 110 1=1 nvl(peia.override_to_organization_id,pea.incurred_by_organization_id)=:expenditure_organization_id LOV custom select haouv.organization_id id, haouv.name value, null description from hr_all_organization_units_vl haouv where haouv.organization_id in (select pao.organization_id from pa_all_organizations pao where pao.pa_org_use_type='EXPENDITURES') order by value US Expenditure Organization 12 120 1=1 peia.transaction_source=:transaction_source LOV custom select pts.transaction_source id, pts.user_transaction_source value, pts.description from pa_transaction_sources pts order by value US Transaction Source 13 130 1=1 pea.expenditure_group=:expenditure_batch LOV custom select pega.expenditure_group value, xxen_util.meaning(pega.expenditure_group_status_code,'EXPENDITURE GROUP STATUS',275) description from hr_all_organization_units_vl haouv, pa_expenditure_groups_all pega where haouv.organization_id=pega.org_id and haouv.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') order by pega.creation_date desc US Expenditure Batch FEA4884DA9F64C6DB5ADF2C8130290DD Default Expenditure items of the selection, with To Project and To Task to enter for the items to transfer. ENGINATICS 19 BILLABLE 18 BURDENED_COST 10 EMPLOYEE_NAME 11 EMPLOYEE_NUMBER 22 EXPENDITURE_BATCH 8 EXPENDITURE_CATEGORY 23 EXPENDITURE_COMMENT 6 EXPENDITURE_ITEM_DATE 28 EXPENDITURE_ITEM_ID 12 EXPENDITURE_ORGANIZATION 7 EXPENDITURE_TYPE 9 EXPENDITURE_TYPE_CLASS 13 NON_LABOR_RESOURCE 1 OPERATING_UNIT 21 ORIGINAL_TRANSACTION_REFERENCE 16 PROJECT_FUNCTIONAL_CURRENCY 3 PROJECT_NAME Y 2 PROJECT_NUMBER 14 QUANTITY 17 RAW_COST 5 TASK_NAME 4 TASK_NUMBER 25 TO_PROJECT_NAME 24 TO_PROJECT_NUMBER 27 TO_TASK_NAME 26 TO_TASK_NUMBER 20 TRANSACTION_SOURCE 29 TRANSFERRED_FROM_ITEM_ID 15 UNIT_OF_MEASURE Upload Mode C,U S Site 646DE97904324059A6C6A32F273841A2 Transfer by Item Id Empty sheet to transfer expenditure items by their item id. ENGINATICS 13 EMPLOYEE_NAME 11 EXPENDITURE_ITEM_DATE 1 EXPENDITURE_ITEM_ID 14 EXPENDITURE_ORGANIZATION 12 EXPENDITURE_TYPE 6 OPERATING_UNIT 8 PROJECT_NAME 7 PROJECT_NUMBER 15 QUANTITY 16 RAW_COST 10 TASK_NAME 9 TASK_NUMBER 3 TO_PROJECT_NAME 2 TO_PROJECT_NUMBER Y 5 TO_TASK_NAME 4 TO_TASK_NUMBER 17 TRANSFERRED_FROM_ITEM_ID Upload Mode C Y S Site Employee Expenditure Batch Expenditure Category Expenditure Item Date From Expenditure Item Date To Expenditure Organization Expenditure Type Expenditure Type Class Project Task Transaction Source FEA4884DA9F64C6DB5ADF2C8130290DD 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATING_UNIT Char varchar2 240 select haouv.name from pa_expenditure_items_all peia, hr_all_organization_units_vl haouv where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.org_id=haouv.organization_id Y 6 PROJECT_NUMBER Char varchar2 25 select ppa.segment1 from pa_expenditure_items_all peia, pa_projects_all ppa where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.project_id=ppa.project_id Y 7 PROJECT_NAME Char varchar2 30 select ppa.name from pa_expenditure_items_all peia, pa_projects_all ppa where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.project_id=ppa.project_id Y 8 TASK_NUMBER Char varchar2 25 select pt.task_number from pa_expenditure_items_all peia, pa_tasks pt where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.task_id=pt.task_id Y 9 TASK_NAME Char varchar2 20 select pt.task_name from pa_expenditure_items_all peia, pa_tasks pt where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.task_id=pt.task_id Y 10 EXPENDITURE_ITEM_DATE Date date select peia.expenditure_item_date from pa_expenditure_items_all peia where peia.expenditure_item_id=:$column$.expenditure_item_id Y 11 EXPENDITURE_TYPE Char varchar2 30 select peia.expenditure_type from pa_expenditure_items_all peia where peia.expenditure_item_id=:$column$.expenditure_item_id Y 12 EXPENDITURE_CATEGORY Char varchar2 30 select pet.expenditure_category from pa_expenditure_items_all peia, pa_expenditure_types pet where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.expenditure_type=pet.expenditure_type Y 13 EXPENDITURE_TYPE_CLASS Char varchar2 80 select psl.meaning from pa_expenditure_items_all peia, pa_system_linkages psl where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.system_linkage_function=psl.function Y 14 EMPLOYEE_NAME Char varchar2 240 select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from pa_expenditure_items_all peia, pa_expenditures_all pea, per_all_people_f papf where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.expenditure_id=pea.expenditure_id and pea.incurred_by_person_id=papf.person_id Y 15 EMPLOYEE_NUMBER Char varchar2 30 Y 16 EXPENDITURE_ORGANIZATION Char varchar2 240 select haouv.name from pa_expenditure_items_all peia, pa_expenditures_all pea, hr_all_organization_units_vl haouv where peia.expenditure_item_id=:$column$.expenditure_item_id and peia.expenditure_id=pea.expenditure_id and nvl(peia.override_to_organization_id,pea.incurred_by_organization_id)=haouv.organization_id Y 17 NON_LABOR_RESOURCE Char varchar2 20 Y 18 QUANTITY Number number select peia.quantity from pa_expenditure_items_all peia where peia.expenditure_item_id=:$column$.expenditure_item_id Y 19 UNIT_OF_MEASURE Char varchar2 80 Y 20 PROJECT_FUNCTIONAL_CURRENCY Char varchar2 15 Y 21 RAW_COST Number number select peia.raw_cost from pa_expenditure_items_all peia where peia.expenditure_item_id=:$column$.expenditure_item_id Y 22 BURDENED_COST Number number Y 23 BILLABLE Char varchar2 80 Billable flag of the item, or capitalizable flag on a capital project. A transferred item takes the flag from the transaction controls and the destination task. Y 24 TRANSACTION_SOURCE Char varchar2 80 Y 25 ORIGINAL_TRANSACTION_REFERENCE Char varchar2 240 Y 26 EXPENDITURE_BATCH Char varchar2 240 Y 27 EXPENDITURE_COMMENT Char varchar2 240 Y 28 TO_PROJECT_NUMBER LOV custom varchar2 25 select ppev.project_id id, ppev.project_number value, ppev.project_name description from pa_projects_expend_v ppev where ppev.expenditure_ou=(select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:$column$.operating_unit) order by value Project to transfer the item to. Leave it blank for a transfer to another task of the same project. 29 TO_PROJECT_NAME Char varchar2 30 select ppa.name from pa_projects_all ppa where ppa.segment1=:$column$.to_project_number 30 TO_TASK_NUMBER LOV custom varchar2 25 select ptev.task_id id, ptev.task_number value, ptev.task_name description from pa_tasks_expend_v ptev where ptev.project_number=nvl(:$column$.to_project_number,:$column$.project_number) order by ptev.wbs_sort_order Task to transfer the item to, of the To Project, or of the item's current project when the To Project is blank. 31 TO_TASK_NAME Char varchar2 20 select pt.task_name from pa_projects_all ppa, pa_tasks pt where ppa.segment1=nvl(:$column$.to_project_number,:$column$.project_number) and ppa.project_id=pt.project_id and pt.task_number=:$column$.to_task_number 32 EXPENDITURE_ITEM_ID Number number Item to transfer. After a transfer the result shows the new item on the destination task. Y Y 33 TRANSFERRED_FROM_ITEM_ID Number number Y 34 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 p_expenditure_item_id in Number EXPENDITURE_ITEM_ID U 5 p_to_project_id in Number Y TO_PROJECT_NUMBER U 6 p_to_project_name in Char Y TO_PROJECT_NAME U 7 p_to_task_id in Number Y TO_TASK_NUMBER U 8 p_to_task_name in Char Y TO_TASK_NAME U 9 p_expenditure_item_id_out out Number EXPENDITURE_ITEM_ID Error SQL Order By upload_row, project_number, task_number, expenditure_item_date, expenditure_item_id Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(haouv.name,xu.operating_unit) operating_unit, nvl(ppa.segment1,xu.project_number) project_number, nvl(ppa.name,xu.project_name) project_name, nvl(pt.task_number,xu.task_number) task_number, nvl(pt.task_name,xu.task_name) task_name, nvl(peia.expenditure_item_date,xu.expenditure_item_date) expenditure_item_date, nvl(peia.expenditure_type,xu.expenditure_type) expenditure_type, nvl(pet.expenditure_category,xu.expenditure_category) expenditure_category, nvl((select psl.meaning from pa_system_linkages psl where peia.system_linkage_function=psl.function),xu.expenditure_type_class) expenditure_type_class, nvl((select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where pea.incurred_by_person_id=papf.person_id),xu.employee_name) employee_name, nvl((select max(nvl(papf.employee_number,papf.npw_number)) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where pea.incurred_by_person_id=papf.person_id),xu.employee_number) employee_number, nvl((select haouv2.name from hr_all_organization_units_vl haouv2 where nvl(peia.override_to_organization_id,pea.incurred_by_organization_id)=haouv2.organization_id),xu.expenditure_organization) expenditure_organization, nvl(peia.non_labor_resource,xu.non_labor_resource) non_labor_resource, nvl(peia.quantity,xu.quantity) quantity, nvl(xxen_util.meaning(peia.unit_of_measure,'UNIT',275),xu.unit_of_measure) unit_of_measure, nvl(peia.projfunc_currency_code,xu.project_functional_currency) project_functional_currency, nvl(peia.raw_cost,xu.raw_cost) raw_cost, nvl(peia.burden_cost,xu.burdened_cost) burdened_cost, decode(peia.expenditure_item_id,null,xu.billable,xxen_util.yes(peia.billable_flag)) billable, nvl((select pts.user_transaction_source from pa_transaction_sources pts where peia.transaction_source=pts.transaction_source),xu.transaction_source) transaction_source, nvl(peia.orig_transaction_reference,xu.original_transaction_reference) original_transaction_reference, nvl(pea.expenditure_group,xu.expenditure_batch) expenditure_batch, nvl((select pec.expenditure_comment from pa_expenditure_comments pec where peia.expenditure_item_id=pec.expenditure_item_id),xu.expenditure_comment) expenditure_comment, xu.to_project_number, xu.to_project_name, xu.to_task_number, xu.to_task_name, nvl(peia.expenditure_item_id,xu.expenditure_item_id) expenditure_item_id, nvl(peia.transferred_from_exp_item_id,xu.transferred_from_item_id) transferred_from_item_id, xu.upload_row from xxen_pa_expenditure_ite_3196_u xu, pa_expenditure_items_all peia, pa_expenditures_all pea, pa_projects_all ppa, pa_tasks pt, pa_expenditure_types pet, hr_all_organization_units_vl haouv where xu.status_code_=xxen_upload.status_success and xu.expenditure_item_id=peia.expenditure_item_id(+) and peia.expenditure_id=pea.expenditure_id(+) and peia.project_id=ppa.project_id(+) and peia.task_id=pt.task_id(+) and peia.expenditure_type=pet.expenditure_type(+) and peia.org_id=haouv.organization_id(+)