ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y 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 US 8E2FF36EDF0779D2E0530100007F1FF2 PER Employee Y Y select ppx.full_name value, (select fu.user_name from fnd_user fu where ppx.person_id=fu.employee_id and rownum=1) description from per_people_x ppx, per_assignments_x pax where ppx.employee_number is not null and ppx.current_employee_flag='Y' and ppx.person_id=pax.person_id and pax.primary_flag='Y' and pax.assignment_type='E' order by ppx.full_name US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US FF40E81F28EA4EC7BB8FFBFB4B4ED225 select null action_, null status_, null message_, null modified_columns_, row_number() over (order by x.employee, x.period_starting, x.date_worked, x.detail_bb_id) upload_row, x.employee, x.employee_number, x.approval_status, x.transferred, x.period_starting, x.date_worked, x.project, x.task, x.expenditure_type, x.hours_type, x.hours, x.line_comment, x.submit_timecard, x.delete_line, x.timecard_bb_id, x.detail_bb_id from ( select papf.full_name employee, papf.employee_number, xxen_util.meaning(hts.approval_status,'HXC_APPROVAL_STATUS',3) approval_status, xxen_util.yes(case when exists (select null from hxc_transaction_details htd, hxc_transactions ht where htd.time_building_block_id=htbb_det.time_building_block_id and htd.time_building_block_ovn=htbb_det.object_version_number and htd.status='SUCCESS' and htd.transaction_id=ht.transaction_id and ht.type='RETRIEVAL' and ht.status='SUCCESS') then 'Y' end) transferred, trunc(hts.start_time) period_starting, trunc(htbb_day.start_time) date_worked, ppa.segment1 project, pt.task_number task, decode(hta.attribute_category,'PROJECTS',hta.attribute3) expenditure_type, petf.element_name hours_type, htbb_det.measure hours, htbb_det.comment_text line_comment, null submit_timecard, null delete_line, hts.timecard_id timecard_bb_id, htbb_det.time_building_block_id detail_bb_id from hxc_timecard_summary hts, hxc_time_building_blocks htbb_day, hxc_time_building_blocks htbb_det, hxc_time_attribute_usages htau, hxc_time_attributes hta, per_all_people_f papf, pa_projects_all ppa, pa_tasks pt, pay_element_types_f petf where :p_upload_mode like '%'||xxen_upload.action_update and hts.resource_id=papf.person_id and hts.start_time between papf.effective_start_date and papf.effective_end_date and htbb_day.parent_building_block_id=hts.timecard_id and htbb_day.parent_building_block_ovn=hts.timecard_ovn and htbb_day.scope='DAY' and htbb_day.date_to=to_date('4712-12-31','YYYY-MM-DD') and htbb_det.parent_building_block_id=htbb_day.time_building_block_id and htbb_det.parent_building_block_ovn=htbb_day.object_version_number and htbb_det.scope='DETAIL' and htbb_det.date_to=to_date('4712-12-31','YYYY-MM-DD') and htau.time_building_block_id=htbb_det.time_building_block_id and htau.time_building_block_ovn=htbb_det.object_version_number and htau.time_attribute_id=hta.time_attribute_id and (hta.attribute_category='PROJECTS' or hta.attribute_category like 'ELEMENT - %') and ppa.project_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute1)) and pt.task_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute2)) and petf.element_type_id(+)=case when hta.attribute_category like 'ELEMENT - %' then to_number(substr(hta.attribute_category,11)) end and htbb_day.start_time between petf.effective_start_date(+) and petf.effective_end_date(+) ) x where 1=1 Initial version: OTL Timecard Upload (create, update, submit and delete OTL timecards with project and payroll element lines) via the public TimeStore deposit API hxc_timestore_deposit. Y U A xxen_otl_upload.upload_timecard US OTL Timecard Upload OTL Timecard Upload =================== Create, update and delete Oracle Time and Labor (OTL / HXC) timecards via the public TimeStore deposit API hxc_timestore_deposit, as an Excel-based alternative to self-service time entry. Row model ========= One spreadsheet row per timecard line and day: Employee, Date, classification and Hours. Rows are grouped into timecards by employee and the OTL timecard period (e.g. the weekly period from the employee's OTL preferences) containing the Date. Rows of the same employee and period must be contiguous and are deposited together as one timecard. The Period Starting column fills automatically once Employee and Date are entered. Classification ============== Each line is classified either for Projects (Project, Task and Expenditure Type - straight time) or for Payroll (Hours Type = payroll element). The deposit derives the employee's application set, approval style and security context from the OTL preferences, exactly like self-service time entry. Run the upload under a responsibility whose operating unit context matches the projects being charged (e.g. the OTL or Projects responsibility the employees use) - the deposit validates the project, task, expenditure type and expenditure organization under the session's operating unit, exactly like self-service time entry. Save and submit =============== Leave Submit blank to save the timecard in Working status. Set Submit to Yes on any row of a timecard to submit it for approval (the OTL approval workflow starts, e.g. HR supervisor approval). Updating a timecard that is already Submitted or Approved re-submits it for approval automatically. Updates and deletes =================== Downloaded lines carry hidden building block ids and are updated through the OTL deposit API, which creates a new timecard version and re-routes approval. Set Delete Line to Yes to remove a line; deleting all lines of a timecard deletes the whole timecard. Timecards already transferred to a recipient application (Projects, Payroll/BEE or Purchasing) are protected and cannot be modified by the upload. Upload modes ============ Create - empty template for new timecards. Create, Update - downloads existing timecards (project and payroll element lines) for review, correction, resubmission or deletion. Enginatics Upload 1=1 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y 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 x.employee=:p_employee LOV PER Employee 8E2FF36EDF0779D2E0530100007F1FF2 Y Y select ppx.full_name value, (select fu.user_name from fnd_user fu where ppx.person_id=fu.employee_id and rownum=1) description from per_people_x ppx, per_assignments_x pax where ppx.employee_number is not null and ppx.current_employee_flag='Y' and ppx.person_id=pax.person_id and pax.primary_flag='Y' and pax.assignment_type='E' order by ppx.full_name US Employee 3 30 1=1 x.period_starting>=:p_period_start_date_from Date US Period Start Date From 4 40 1=1 x.period_starting<:p_period_start_date_to+1 Date last_day(:$flex$.period_start_date_from) US Period Start Date To 5 50 1=1 x.approval_status=:p_approval_status LOV custom Y select flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='HXC_APPROVAL_STATUS' and flvv.view_application_id=3 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by 1 US Approval Status :$flex$.period_start_date_from Period Start Date From Y Period Start Date To 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 UPLOAD_ROW Number number Y 6 EMPLOYEE LOV custom varchar2 Y Y select ppx.person_id id, ppx.full_name value, ppx.employee_number description from per_people_x ppx where ppx.current_employee_flag='Y' order by ppx.full_name Y 7 EMPLOYEE_NUMBER Char varchar2 select min(ppx.employee_number) from per_people_x ppx where ppx.full_name=:$column$.employee having count(distinct ppx.employee_number)=1 Fills automatically from the Employee. Only needed to identify the person uniquely when two employees share the same full name. Y 8 APPROVAL_STATUS Char varchar2 Current approval status of the downloaded timecard (Working, Submitted, Approved, Rejected, Error). Blank for new timecards. Y 9 TRANSFERRED Char varchar2 Yes when the timecard line has already been transferred to a recipient application (Projects, Payroll/BEE or Purchasing). Transferred timecards cannot be modified by the upload. Y 10 PERIOD_STARTING Date date select xxen_otl_upload.period_start(:$column$.employee,:$column$.employee_number,:$column$.date_worked) from dual First day of the OTL timecard period containing the Date, from the employee's OTL recurring period preference. Fills automatically once Employee and Date are entered. Rows of the same employee and period are deposited together as one timecard. Y 11 DATE_WORKED Date date Y 12 PROJECT LOV custom varchar2 Y select ppev.project_id id, ppev.project_number value, ppev.project_name || nvl2(ppev.project_description,' (' || ppev.project_description || ')',null) description from pa_projects_expend_v ppev where :$column$.date_worked between nvl(ppev.start_date,:$column$.date_worked) and nvl(ppev.completion_date,:$column$.date_worked) order by value, description Project for a Projects time entry. Enter either Project, Task and Expenditure Type or an Hours Type, not both. 13 TASK LOV custom varchar2 Y select ptev.task_id id, ptev.task_number value, ptev.task_name description from pa_projects_expend_v ppev, pa_tasks_expend_v ptev where ppev.project_number=:$column$.project and ppev.project_id=ptev.project_id and ppev.project_ou=ptev.org_id and :$column$.date_worked between nvl(ptev.start_date,:$column$.date_worked) and nvl(ptev.completion_date,:$column$.date_worked) order by value, description 14 EXPENDITURE_TYPE LOV custom varchar2 Y select petsl.expenditure_type value, pet.description || ' (' || pet.expenditure_category || ') (' || pet.unit_of_measure || ')' description from pa_expend_typ_sys_links petsl, pa_expenditure_types pet where petsl.system_linkage_function='ST' and petsl.expenditure_type=pet.expenditure_type and :$column$.date_worked between trunc(greatest(petsl.start_date_active,pet.start_date_active)) and nvl(trunc(least(petsl.end_date_active,pet.end_date_active)),:$column$.date_worked) order by value Straight time expenditure type for a Projects time entry. 15 HOURS_TYPE LOV custom varchar2 Y select petf.element_type_id id, petf.element_name value, petf.description from pay_element_types_f petf where :$column$.date_worked between petf.effective_start_date and petf.effective_end_date order by value Payroll element for a payroll time entry (e.g. Regular Hours, Overtime). Enter either Project, Task and Expenditure Type or an Hours Type, not both. 16 HOURS Number number Y 17 LINE_COMMENT Char varchar2 18 SUBMIT_TIMECARD LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Set to Yes on any row of a timecard to submit it for approval. Leave blank to save the timecard in Working status. A timecard that is already Submitted or Approved is re-submitted automatically when updated. 19 DELETE_LINE LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Set to Yes to delete this downloaded timecard line. Deleting all lines of a timecard deletes the whole timecard. 20 TIMECARD_BB_ID Number number Y 21 DETAIL_BB_ID Number number Y Error SQL Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.upload_row, nvl(papf.full_name,xu.employee) employee, nvl(papf.employee_number,xu.employee_number) employee_number, nvl(xxen_util.meaning(hts.approval_status,'HXC_APPROVAL_STATUS',3),xu.approval_status) approval_status, nvl(xxen_util.yes(case when exists (select null from hxc_transaction_details htd, hxc_transactions ht where htd.time_building_block_id=htbb_det.time_building_block_id and htd.time_building_block_ovn=htbb_det.object_version_number and htd.status='SUCCESS' and htd.transaction_id=ht.transaction_id and ht.type='RETRIEVAL' and ht.status='SUCCESS') then 'Y' end),xu.transferred) transferred, nvl(trunc(hts.start_time),xu.period_starting) period_starting, nvl(trunc(htbb_day.start_time),xu.date_worked) date_worked, nvl(ppa.segment1,xu.project) project, nvl(pt.task_number,xu.task) task, nvl(decode(hta.attribute_category,'PROJECTS',hta.attribute3),xu.expenditure_type) expenditure_type, nvl(petf.element_name,xu.hours_type) hours_type, nvl(htbb_det.measure,xu.hours) hours, nvl(htbb_det.comment_text,xu.line_comment) line_comment, xu.submit_timecard, xu.delete_line, nvl(hts.timecard_id,xu.timecard_bb_id) timecard_bb_id, nvl(htbb_det.time_building_block_id,xu.detail_bb_id) detail_bb_id from xxen_otl_timecard_uploa_9018_u xu, hxc_timecard_summary hts, per_all_people_f papf, hxc_time_building_blocks htbb_det, hxc_time_building_blocks htbb_day, ( select htau.time_building_block_id, htau.time_building_block_ovn, hta.attribute_category, hta.attribute1, hta.attribute2, hta.attribute3 from hxc_time_attribute_usages htau, hxc_time_attributes hta where htau.time_attribute_id=hta.time_attribute_id and (hta.attribute_category='PROJECTS' or hta.attribute_category like 'ELEMENT - %') ) hta, pa_projects_all ppa, pa_tasks pt, pay_element_types_f petf where xu.status_code_=xxen_upload.status_success and hts.timecard_id(+)=xu.timecard_bb_id and papf.person_id(+)=hts.resource_id and hts.start_time between papf.effective_start_date(+) and papf.effective_end_date(+) and htbb_det.time_building_block_id(+)=xu.detail_bb_id and htbb_det.scope(+)='DETAIL' and htbb_det.date_to(+)=to_date('4712-12-31','YYYY-MM-DD') and htbb_day.time_building_block_id(+)=htbb_det.parent_building_block_id and htbb_day.scope(+)='DAY' and htbb_day.date_to(+)=to_date('4712-12-31','YYYY-MM-DD') and hta.time_building_block_id(+)=htbb_det.time_building_block_id and hta.time_building_block_ovn(+)=htbb_det.object_version_number and ppa.project_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute1)) and pt.task_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute2)) and petf.element_type_id(+)=case when hta.attribute_category like 'ELEMENT - %' then to_number(substr(hta.attribute_category,11)) end and htbb_day.start_time between petf.effective_start_date(+) and petf.effective_end_date(+) Default value :$column$.employee EMPLOYEE EMPLOYEE_NUMBER LOV query :$column$.date_worked DATE_WORKED EXPENDITURE_TYPE LOV query :$column$.date_worked DATE_WORKED HOURS_TYPE Default value :$column$.date_worked DATE_WORKED PERIOD_STARTING Default value :$column$.employee EMPLOYEE PERIOD_STARTING Default value :$column$.employee_number EMPLOYEE_NUMBER PERIOD_STARTING LOV query :$column$.date_worked DATE_WORKED PROJECT LOV query :$column$.date_worked DATE_WORKED TASK LOV query :$column$.project PROJECT TASK