Writing customized reports for WAM
Custom reports can be added and integrated into Oracle Utilities Work and Asset Management (WAM) without changing any of the vendor code. This post explains how to add an Oracle custom report and integrate it into the application.
WAM uses a defined parameter form to call each report. The parameter form builds the ‘where clause’ and the ‘order by’ parameters and passes them to the report. The report then appends them to the SQL statement when running the report. If the report is called from a custom menu (defined in the Module Administration – Forms function), then the menu will pass a ‘where clause’ (consisting of the primary key equaling the current record) into the report. All of this functionality resides in the libraries srpt.pll (for the reports) and srpi.pll (for the form).
When the report is run, the form saves the parameters into the table sv_report_queue. This gives a log of what each user has ran, as well as the status of the report.
Setup of development environment
Before building customized reports for WAM, you need to install Oracle 10g Developer. The system is written using the latest verson, so, while other versions of the report developer may work, it is best to use the latest version.
1 – Install Latest version forms and reports (10 not 6)
2 – Create a directory for forms and reports development (j:\spl)
3 – Create a shortcut for the report developer and form developer.
4 – For each shortcut, change the ‘start in’ to the directory you created in #2.
5 – Copy all forms and reports you will work on, as well as srpt.pll, srpi.pll and rptmstr.fmb found on the CMMS server to the directory you created in #2.
It is very important that all your forms and reports development be done in one directory. If you use multiple directories, you will need to create a shortcut for the Forms and Reports developer for each one. You will also need to copy srpt.pll, spri.pll and rptmstr.fmb to the new directory. If you don’t, the libraries and other files will not attach correctly.
Developing reports with Oracle Reports
When developing a custom report, you can start with an existing seeded report, or another custom report. By doing that, you get all the parameter and other information needed to run the report. You can do this be copying the existing report to the new custom report.
Before starting to change the report, you first want to make sure that it will compile. Do this by going to file/connect and then connect to the CMMS database. Then go to Program/Compile/All to compile the report.
The first thing to change is to update the filename of the new report in two places inside the report developer. First, change the report ID parameter default value to the new name. Secondly, change the name in the top level of the navigator.
You then can start to customize the report. To change the SQL for the report go under ‘Data Model’ in the navigator. To change the layout, go under ‘Paper Layout’ to change it.
For the report to run from the form, you will need to make sure that each parameter in the parameter form is a valid column in your SQL statement in the report. If the where clause or order by are invalid, you get the generic error FRM-41214 ‘Unable to run report’. Please see the section under forms for more on the parameters.
You can run the report from your desktop to test your changes.
NOTE: It is very easy within Oracle Reports to invalidate the Report especially when doing the ‘Paper Layout’. If you do this, you may not be able to undo or recover the invalidated version. You will need to restore a backup. To deal with this, create a backup directory and make FREQUENT backups of the report you are working on. You can rename them name_v1.rdf, name_v2.rdf, etc. to keep multiple backups. I would recommend you backup at least every half hour if you are working on a report.
That being said, 10g is much more stable than report 6, which EBS uses.
If you do the steps above, the following about attaching the library will not apply.
If you get errors about SRPT.PLL missing when loading the report, then you will need to attach SRPT.PLL You do this by selecting ‘Attached Libraries’ and clicking on the green plus sign and choosing SRPT.PLL. Say ‘YES’ to the question about removing the path.
Compiling and uploading
When your report is complete, you will need to compile and export the report. To compile, select program – compile – all from the menu. To export your report, select file – export. Export the report as a .REP with the same name as the RDF. You will need to move the REP to the server in order for CMMS to run the report.
If the report does not run from the application, you will get the FRM-41214 error. To see the actual error being returned by the report, you will need to run it directly from the browser. The URL to use is:
http://SERVER:PORT/reports/rwservlet?report=D:\SYNERGEN\CMMST\forms\c_rpt000.rep&destype=cache&desformat=html&userid=student15/password@cmmst
Where c_rpt000.rep is your report and the userid information is your login and password to the database. This will return the actual error that caused the report not to run.
Parameter Forms
The parameter form is the screen that shows up when you run the report, and is what calls the report. The form uses the field names and data to build a ‘where clause’ and ‘order by clause’ and sends them to the report as parameters.
A single form file (FMX) can store multiple parameter forms. Each parameter screen is a separate canvas. You can use rptspl1.fmb (comment on this and I will send) as an example of a parameter form. You can either add parameter forms to rptspl1.fmb or create a new parameter form.
To create a new parameter canvas, do the following:
1 – Go to the data blocks section and create a new data block. Do not use the wizard.
2 – Ctrl-click and drag each of the items under the REPORT_TEMPLATE data block to the new data block. Make sure to subclass B_RUN_REPORT and copy the others.
3 – Add any parameters you need to the data block.
4 – Create a new window object. Name it the same as the form
5 – Create a new canvas using the wizard. Choose the correct data block. When you get to the list of fields, choose them all but the plant. Select the B_RUN_REPORT parameter and make sure that it is a push button and has a size.
6 – Arrange the parameter form so it looks good.
7 – Open the property pallet for the canvas and make sure the window is the same as created in #4.
8 – Save, compile the PL/SQL and compile the module.
Field naming conventions
The libraries use the field names on the form as column names on the form, and to identify the order by field. You need to name the fields in the data block and canvas as they are in the database. The field names can be prefixed to identify the operator to use. Below are the prefixes you can use
Prefix on field name
Operator
No prefix
=
OPER_
Drop down list of operators
ORDER_BY_TEXT
The Order by clause
LE_
<=
GE_
>=
EQ_
=
LIST1_
Like
For OPER_ the data is the operator, you need to have another field for the parameter. The OPER_ field type needs to be a pop list.
For Date fields (end in _DATE) the library will set it to get the whole days for LE_ and GE_. So, if you enter 9/1/2007 as a start date and 9/30/07 as the end date, the system will add the code: Date_col >= 9/1/2007 and Date_col < 10/1/07. This way, it gets the whole last day.
To test these, use the dummy report (c_rpt000.rdf) (comment and I will send to you) that does not process the parameters, but prints them on the first page of the report. This way you can make sure your parameters are correct.
You cannot run the form on your desktop; you will need to load it to the test server.
Reports Admin
Report ID: This is the report (RDF) Name.
Select Block: This is the Canvas name in the parameter form.
Location: This is the Parameter Form (FMX) Name. The LOV is in Code table 95.
Type: Does not seem to make any difference. Set it to Listing for now.
Group: This is used to help find things in the responsibility form. Choose the most appropriate.
Custom Menus and Reports
Reports can be put on custom menus and ran using the ‘Use Primekeys in where clause’. This skips the parameter form and runs the report using the information contained in the form that calls the report. In order to use the primekeys and bring up a parameter form, you would have to create a custom version of srpi.pll and use it with the form.
To make this work:
1 - Make sure that the from clause of the select statement of the report contains the table that is being viewed in the module.
2 – Register the report.
3 – Create a custom menu for the form. Go to ‘Module administration – Forms’. Find the form you want to add the menu to, click on it, click on custom menu. Add the menu name in the ‘menu title’ field. Enter a sequence number (use 10, 20, etc.). Enter the submenu name into ‘Submenu Title’. Click the ‘display’ box. Choose ‘Report’ for the ‘type’ and enter the report name for the Action. Set ‘Use Primekeys in where clause’. Save your work.
4 – Grant access to the custom menu for the responsibilities that are to run it. Goto Responsibilities, Select the responsibility, choose Custom Menu for the type and add the form to the list.
5 – Test the report. It is helpful to put the raw generated where clause in report to validate that the where clause is passed correctly.
Monday, November 30, 2009
Wednesday, November 18, 2009
Upgrade to 1.8.1.1
We just upgraded to 1.8.1.1 and found the following issues:
1 - Users cannot reset their passwords (We already have received a patch for this and are testing it.
2 - Mousetrap issue (cannot use mouse to change the focus of the cursor. This is resolved by uninstalling Jinitiator and allowing the system to re-install it.
3 - Advanced edit on the home page does not work unless a form is already open
4 - The help screen is broken (Oracle has a fix for this)
All in all, no major issues.
1 - Users cannot reset their passwords (We already have received a patch for this and are testing it.
2 - Mousetrap issue (cannot use mouse to change the focus of the cursor. This is resolved by uninstalling Jinitiator and allowing the system to re-install it.
3 - Advanced edit on the home page does not work unless a form is already open
4 - The help screen is broken (Oracle has a fix for this)
All in all, no major issues.
Friday, November 6, 2009
Code to move Work history to work orders
Here is the package to move all the history to work orders. This only will work for version 1.8.1.1
I used Toad SGA to extract these.
create or replace package xx_move_WO_from_WH
as
procedure move_wo(iPlant varchar2, iWork_order_number varchar2);
end;
/
create or replace package body xx_move_WO_from_WH
as
procedure move_wo(iPlant varchar2, iWork_order_number varchar2)
is
begin
INSERT INTO sa_work_order_task_cu
(plant, work_order_no, work_order_task_no,
accounting_treatment_type, usage_code, cu_no, cu_qty,
cu_estimate, unit_of_measure, cu_function, difficulty,
cu_actual_qty, standard_price_ind, discount_percent,
discount_amount, created_date, created_by, last_update_date,
last_update_user)
SELECT plant, work_order_no, work_order_task_no, accounting_treatment_type,
usage_code, cu_no, cu_qty, cu_estimate, unit_of_measure,
cu_function, difficulty, cu_actual_qty, standard_price_ind,
discount_percent, discount_amount, SYSDATE, USER, SYSDATE, USER
FROM sa_work_history_task_cu
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_attachment
(plant, work_order_no, work_order_task_no, attachment,
attachment_id, print_ind, attachment_revision_no)
SELECT plant, work_order_no, work_order_task_no, attachment, attachment_id,
print_ind, attachment_revision_no
FROM sa_work_history_attachment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order
(plant, work_order_no, work_class, work_type, asset_id,
asset_record_type, work_status, work_desc, asset_desc,
project_id, subproject_id, task_priority_default,
planned_total_amount, approved_amount, work_schedule_date,
work_entered_date, requestor_name, routing_list_id,
work_required_date, maintenance_manager, production_approver,
maintenance_approver, work_status_date, work_category,
benchmark_no, planner, pm_master_no, last_update_date,
safety_notes_ind, inspection_ind, lead_crew,
original_work_order_no, component_id, component_id_desc,
department, area, account_no, process_no, deficiency_tag,
building, LOCATION, POSITION, meter_units, meter_reading_last,
meter_reading_current, meter_ltd, vehicle_in_date,
vehicle_out_date, operator_id, shop, track_downtime_ind,
job_code, work_priority_total, work_priority_adjustment,
criticality, deliver_to_desc, work_form, meter_reading_date,
blanket_contract_no, sent_offsite_date, return_from_offsite_date,
blanket_revision_no, created_date, created_by, last_update_user,
signoff_by, signoff_date, actual_duration, actual_start_date,
actual_finish_date, finished_date, finished_by, closed_date,
closed_by, retain_type, retain_days, operational_reading_date,
asset_activity_log_no)
SELECT plant, work_order_no, work_class, work_type, asset_id,
asset_record_type, 'CLOSED', work_desc, asset_desc, project_id,
subproject_id, task_priority_default, planned_total_amount,
approved_amount, work_schedule_date, work_entered_date,
requestor_name, routing_list_id, work_required_date,
maintenance_manager, production_approver, maintenance_approver,
work_status_date, work_category, benchmark_no, planner,
pm_master_no, last_update_date, safety_notes_ind, inspection_ind,
lead_crew, original_work_order_no, component_id, component_id_desc,
department, area, account_no, process_no, deficiency_tag, building,
LOCATION, POSITION, meter_units, meter_reading_last,
meter_reading_current, meter_ltd, vehicle_in_date, vehicle_out_date,
operator_id, shop, track_downtime_ind, job_code,
work_priority_total, work_priority_adjustment, criticality,
deliver_to_desc, work_form, meter_reading_date, blanket_contract_no,
sent_offsite_date, return_from_offsite_date, blanket_revision_no,
created_date, created_by, last_update_user, signoff_by,
signoff_date, actual_duration, actual_start_date,
actual_finish_date, finished_date, finished_by, closed_date,
closed_by, retain_type, retain_days, operational_reading_date,
asset_activity_log_no
FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task
(plant, work_order_no, work_order_task_no, backlog_group,
task_status, task_status_date, task_desc, weekly_schedule_ind,
weekly_schedule_date, asset_record_type, process_no, asset_id,
downtime_ind, downtime_hours, assigned_to_name,
task_priority_total, task_priority_adjustment, task_priority,
criticality, account_no, specification_no,
specification_category, specification_type, asset_desc,
component_id_desc, daily_schedule_ind, daily_schedule_date, area,
department, bom_id, last_update_date, crew,
task_estimated_start_date, held_for_parts_ind, component_id,
estimated_duration_units, task_scheduled_shift,
task_required_date, estimated_duration, deliver_to_desc,
task_sequence_no, component_spec_no, component_spec_type,
component_spec_category, component_bom_id, work_request_no,
downtime_type, report_code1, report_code2, report_code3,
report_code4, report_code5, LOCATION, last_update_user, building,
POSITION, deficiency_tag, creation_date, work_code,
task_estimated_finish_date, job_code, created_by,
actual_duration, work_location, travel_time, work_trip,
actual_start_date, actual_finish_date, finished_date,
finished_by, type_of_work, type_of_work_units,
type_of_work_amount, comments, change_request_ind,
change_request_no, wbs, service_request_no, number_prefix,
street_number, number_suffix, street_name, street_direction,
cross_street, city, state_province, postal_code, offset,
direction, from_asset_record_type, to_asset_record_type,
from_asset_id, to_asset_id, point_id, iso_ind, environmental_ind,
run_to_failure_ind, health_ind, safety_critical_ind, task_phase,
planner, renewal_work_ind, street_number_char, shop, meter_units,
meter_ltd, meter_reading_current, job_reason_code,
job_action_code, vehicle_system_code, vehicle_assembly_code,
vehicle_component_code, asset_posted_date, asset_posted_ind,
mwm_field_order_no, mwm_order_type, sent_to_mwm_ind)
SELECT plant, work_order_no, work_order_task_no, backlog_group,
task_status, task_status_date, task_desc, weekly_schedule_ind,
weekly_schedule_date, asset_record_type, process_no, asset_id,
downtime_ind, downtime_hours, assigned_to_name, task_priority_total,
task_priority_adjustment, task_priority, criticality, account_no,
specification_no, specification_category, specification_type,
asset_desc, component_id_desc, daily_schedule_ind,
daily_schedule_date, area, department, bom_id, last_update_date,
crew, task_estimated_start_date, held_for_parts_ind, component_id,
estimated_duration_units, task_scheduled_shift, task_required_date,
estimated_duration, deliver_to_desc, task_sequence_no,
component_spec_no, component_spec_type, component_spec_category,
component_bom_id, work_request_no, downtime_type, report_code1,
report_code2, report_code3, report_code4, report_code5, LOCATION,
last_update_user, building, POSITION, deficiency_tag, creation_date,
work_code, task_estimated_finish_date, job_code, created_by,
actual_duration, work_location, travel_time, work_trip,
actual_start_date, actual_finish_date, finished_date, finished_by,
type_of_work, type_of_work_units, type_of_work_amount, comments,
change_request_ind, change_request_no, wbs, service_request_no,
number_prefix, street_number, number_suffix, street_name,
street_direction, cross_street, city, state_province, postal_code,
offset, direction, from_asset_record_type, to_asset_record_type,
from_asset_id, to_asset_id, point_id, NVL (iso_ind, 'N'),
NVL (environmental_ind, 'N'), NVL (run_to_failure_ind, 'N'),
NVL (health_ind, 'N'), NVL (safety_critical_ind, 'N'), task_phase,
planner, renewal_work_ind, street_number_char, shop, meter_units,
meter_ltd, meter_reading_current, job_reason_code, job_action_code,
vehicle_system_code, vehicle_assembly_code, vehicle_component_code,
asset_posted_date, asset_posted_ind, mwm_field_order_no,
mwm_order_type, NVL (sent_to_mwm_ind, 'N')
FROM sa_work_history_task
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_approval
(plant, work_order_no, approval_title, username, approval_status,
approval_status_date, approval_amount, approval_comments)
SELECT plant, work_order_no, approval_title, username, approval_status,
approval_status_date, approval_amount, approval_comments
FROM sa_work_history_approval
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_location_wrksht
(plant, work_order_no, work_order_task_no, cu_location,
accounting_treatment_type, usage_code, cu_no, cu_qty,
cu_actual_qty, cu_estimate, unit_of_measure, cu_function,
difficulty, standard_price_ind, discount_percent,
discount_amount, created_date, created_by, last_update_date,
last_update_user, cu_qty_calculated, no_of_conductors)
SELECT plant, work_order_no, work_order_task_no, cu_location,
accounting_treatment_type, usage_code, cu_no, cu_qty, cu_actual_qty,
cu_estimate, unit_of_measure, cu_function, difficulty,
standard_price_ind, discount_percent, discount_amount, SYSDATE,
USER, SYSDATE, USER, cu_qty_calculated, no_of_conductors
FROM sa_work_history_locat_wrksht
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_material
(plant, work_order_no, work_order_task_no, item_sequence_no,
item_status, item_status_date, last_update_date, vendor_contact,
vendor_code, blanket_contract_no, item_required_date, unit_price,
po_no, unit_of_issue, item_desc, internal_committed_amount,
internal_committed_quantity, external_committed_amount,
external_committed_quantity, revised_estimate_amount,
accrued_amount, original_estimate_amount, actual_amount,
actual_quantity, accrued_quantity, revised_estimate_quantity,
original_estimate_quantity, component_id, stock_type, stock_code,
po_item_type, issued_to_date_quantity, storeroom,
requisition_originator, cost_category, routing_list_id,
requisition_item, po_item, requisition_no, buyer, vendor_name,
deliver_to_department, expense_code, expense_category,
credit_card_purchase_ind, credit_card_holder_name, created_date,
created_by, last_update_user, reported_used_quantity)
SELECT plant, work_order_no, work_order_task_no, item_sequence_no,
item_status, item_status_date, last_update_date, vendor_contact,
vendor_code, blanket_contract_no, item_required_date, unit_price,
po_no, unit_of_issue, item_desc, internal_committed_amount,
internal_committed_quantity, external_committed_amount,
external_committed_quantity, revised_estimate_amount,
accrued_amount, original_estimate_amount, actual_amount,
actual_quantity, accrued_quantity, revised_estimate_quantity,
original_estimate_quantity, component_id, stock_type, stock_code,
po_item_type, issued_to_date_quantity, storeroom,
requisition_originator, cost_category, routing_list_id,
requisition_item, po_item, requisition_no, buyer, vendor_name,
deliver_to_department, expense_code, expense_category,
credit_card_purchase_ind, credit_card_holder_name, created_date,
created_by, last_update_user, reported_used_quantity
FROM sa_work_history_material
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_labor
(plant, work_order_no, work_order_task_no, labor_sequence_no,
craft, craftsmen_quantity, expense_code, premium_expense_code,
original_estimate_amount, revised_estimate_amount,
internal_committed_amount, external_committed_amount,
actual_amount, actual_premium_amount, original_estimate_hours,
revised_estimate_hours, internal_committed_hours,
external_committed_hours, actual_hours, actual_premium_hours,
last_update_date, last_update_user, burden_regular_amount,
burden_premium_amount, burden_regular_expense_code,
burden_premium_expense_code, labor_duration)
SELECT plant, work_order_no, work_order_task_no, labor_sequence_no, craft,
craftsmen_quantity, expense_code, premium_expense_code,
original_estimate_amount, revised_estimate_amount,
internal_committed_amount, external_committed_amount, actual_amount,
actual_premium_amount, original_estimate_hours,
revised_estimate_hours, internal_committed_hours,
external_committed_hours, actual_hours, actual_premium_hours,
last_update_date, last_update_user, burden_regular_amount,
burden_premium_amount, burden_regular_expense_code,
burden_premium_expense_code, labor_duration
FROM sa_work_history_labor
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_requirement
(plant, work_order_no, work_order_task_no, item_sequence_no,
requirement_type, requirement_quantity, requirement_units,
requirement_standard_price, requirement_duration,
requirement_total_amount, original_estimate_qty,
original_estimate_amount, revised_estimate_qty,
revised_estimate_amount, committed_qty, committed_amount,
actual_qty, actual_amount, expense_code, created_date,
created_by, last_update_date, last_update_user)
SELECT r.plant, r.work_order_no, r.work_order_task_no, r.item_sequence_no,
r.requirement_type, r.requirement_quantity, r.requirement_units,
r.requirement_standard_price, r.requirement_duration,
r.requirement_total_amount, r.original_estimate_qty,
r.original_estimate_amount, r.revised_estimate_qty,
r.revised_estimate_amount, r.committed_qty, r.committed_amount,
r.actual_qty, r.actual_amount, r.expense_code, SYSDATE, USER,
SYSDATE, USER
FROM sa_work_history_requirement r
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_cu_items
(plant, item_sequence_no, work_order_no, work_order_task_no,
accounting_treatment_type, item_type, item_id, storeroom,
item_qty, unit_of_measure, detail_sequence_no, item_duration,
unit_price, expense_code, premium_expense_code, created_date,
created_by, last_update_date, last_update_user, item_desc)
SELECT plant, item_sequence_no, work_order_no, work_order_task_no,
accounting_treatment_type, item_type, item_id, storeroom, item_qty,
unit_of_measure, detail_sequence_no, item_duration, unit_price,
expense_code, premium_expense_code, SYSDATE, USER, SYSDATE, USER,
item_desc
FROM sa_work_history_task_cu_itm
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_reg_acct_dist
(plant, work_order_no, work_order_task_no, regulatory_account_no,
regulatory_account_desc, calculated_percent_split,
override_percent_split, wip_account_no, regulatory_account_type,
direct_oh_percent_split, cu_estimate_total, created_date,
created_by, last_update_date, last_update_user)
SELECT plant, work_order_no, work_order_task_no, regulatory_account_no,
regulatory_account_desc, calculated_percent_split,
override_percent_split, wip_account_no, regulatory_account_type,
direct_oh_percent_split, cu_estimate_total, SYSDATE, USER, SYSDATE,
USER
FROM sa_work_history_reg_acct_dist
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_cu_bid
(plant, work_order_no, work_order_task_no, bid_date, cu_no,
contractor, bid_amount, material_ind, expense_code,
detail_sequence_no, labor_ind, created_date, created_by,
last_update_date, last_update_user, cu_function, difficulty)
SELECT plant, work_order_no, work_order_task_no, bid_date, cu_no,
contractor, bid_amount, material_ind, expense_code,
detail_sequence_no, labor_ind, SYSDATE, USER, SYSDATE, USER,
cu_function, difficulty
FROM sa_work_history_task_cu_bid
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_reg_acct_oh
(plant, work_order_no, work_order_task_no, overhead_class,
estimated_overhead_percent, applied_expense_category,
created_date, created_by, last_update_date, last_update_user)
SELECT plant, work_order_no, work_order_task_no, overhead_class,
estimated_overhead_percent, applied_expense_category, SYSDATE, USER,
SYSDATE, USER
FROM sa_work_history_reg_acct_oh
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_location
(plant, work_order_no, work_order_task_no, cu_location,
location_desc, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, created_date, created_by, last_update_date,
last_update_user, work_design_no, structure_type, SOURCE,
distance, distance_units, gis_gps_latitude, gis_gps_longitude,
overhead_ind, underground_ind)
SELECT plant, work_order_no, work_order_task_no, cu_location,
location_desc, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, SYSDATE, USER, SYSDATE, USER, work_design_no,
structure_type, SOURCE, distance, distance_units, gis_gps_latitude,
gis_gps_longitude, overhead_ind, underground_ind
FROM sa_work_history_location
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_fixed_asset
(plant, work_order_no, work_order_task_no, item_no, cu_no,
property_unit_no, cu_location, action, asset_record_type,
asset_id, action_date, regulatory_account_no,
regulatory_account_type, created_date, created_by,
last_update_date, last_update_user, asset_value, period_year,
apply_prior_year, contributed_ind, cu_function,
change_request_no, asset_change_seq_no, number_of_units)
SELECT a.plant, a.work_order_no, a.work_order_task_no, a.item_no, a.cu_no,
a.property_unit_no, a.cu_location, a.action, a.asset_record_type,
a.asset_id, a.action_date, a.regulatory_account_no,
a.regulatory_account_type, SYSDATE, USER, SYSDATE, USER,
a.asset_value, a.period_year, a.apply_prior_year, a.contributed_ind,
a.cu_function, a.change_request_no, a.asset_change_seq_no,
a.number_of_units
FROM sa_work_history_fixed_asset a
WHERE a.plant = iPlant
AND a.work_order_no = iWork_order_number
AND NVL (a.contributed_ind, 'N') = 'N';
INSERT INTO sa_work_order_adjustment
(plant, work_order_no, work_order_task_no, expense_code,
actual_amount, last_update_date)
SELECT plant, work_order_no, work_order_task_no, expense_code,
actual_amount, last_update_date
FROM sa_work_history_adjustment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order_material w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history_material r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no
AND r.item_sequence_no = w.item_sequence_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
UPDATE sa_work_order
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number)
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order_material w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history_material r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no
AND r.item_sequence_no = w.item_sequence_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
INSERT INTO sa_work_order_service_contract
(plant, work_order_no, work_order_task_no, labor_sequence_no,
service_contract_no, contract_revision_no, contract_item,
item_id, craftsmen_quantity, rate, rate_type, expense_code,
original_estimate_amount, revised_estimate_amount,
committed_amount, accrued_amount, actual_amount,
original_estimate_quantity, revised_estimate_quantity,
committed_quantity, accrued_quantity, actual_quantity,
last_update_date, last_update_user)
SELECT plant, work_order_no, work_order_task_no, labor_sequence_no,
service_contract_no, contract_revision_no, contract_item, item_id,
craftsmen_quantity, rate, rate_type, expense_code,
original_estimate_amount, revised_estimate_amount, committed_amount,
accrued_amount, actual_amount, original_estimate_quantity,
revised_estimate_quantity, committed_quantity, accrued_quantity,
actual_quantity, last_update_date, last_update_user
FROM sa_work_history_contract
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_asset_list
(work_order_no, work_order_task_no, sequence_no, plant,
asset_record_type, asset_id, component_id, percent_split,
total_amount, created_date, created_by, last_update_date,
last_update_user)
SELECT work_order_no, work_order_task_no, sequence_no, plant,
asset_record_type, asset_id, component_id, percent_split,
total_amount, created_date, created_by, last_update_date,
last_update_user
FROM sa_work_history_task_asset_lis
WHERE work_order_no = iWork_order_number AND plant = iPlant;
BEGIN
UPDATE sa_work_order_task w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history_task r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
END;
INSERT INTO sa_work_order_permit
(plant, work_order_no, work_order_task_no, permit_type, permit_no,
permit_acquired_date, last_update_date, created_date, created_by,
last_update_user, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, permit_template_id, asset_record_type, asset_id)
SELECT plant, work_order_no, work_order_task_no, permit_type, permit_no,
permit_acquired_date, last_update_date, created_date, created_by,
last_update_user, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, permit_template_id, asset_record_type, asset_id
FROM sa_work_history_permit
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_service_history_data
(plant, work_order_no, work_order_task_no, cu_location,
specification_type, specification_category)
SELECT plant, work_order_no, work_order_task_no, cu_location,
specification_type, specification_category
FROM sa_work_history_service_data
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_service_history_details
(plant, work_order_no, work_order_task_no, specification_type,
specification_category, attribute_sequence_no, attribute_value,
attribute_desc)
SELECT plant, work_order_no, work_order_task_no, specification_type,
specification_category, attribute_sequence_no, attribute_value,
attribute_desc
FROM sa_work_history_service_detail
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_material_attachment
(plant, work_order_no, work_order_task_no, item_sequence_no,
attachment, attachment_id, copy_to_purchase_order, print_ind,
last_update_date, last_update_user, created_date, created_by,
attachment_revision_no)
SELECT plant, work_order_no, work_order_task_no, item_sequence_no,
attachment, attachment_id, copy_to_purchase_order, print_ind,
SYSDATE, USER, SYSDATE, USER, attachment_revision_no
FROM sa_work_history_mtl_attch
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_failure
(plant, work_order_no, work_order_task_no, asset_id,
asset_record_type, component_id, failure_code, repair_code,
component_code, primary_ind, failure_mode, root_cause,
work_request_no, further_action, further_action_desc,
further_action_required_date, last_update_date, last_update_user,
created_date, created_by, sequence_no)
SELECT plant, work_order_no, work_order_task_no, asset_id,
asset_record_type, component_id, failure_code, repair_code,
component_code, primary_ind, failure_mode, root_cause,
work_request_no, further_action, further_action_desc,
further_action_required_date, last_update_date, last_update_user,
created_date, created_by, sequence_no
FROM sa_work_order_task_failure
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number)
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order_task w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history_task r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
UPDATE sa_work_order_permit w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history_permit r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
UPDATE sa_work_order_permit w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history_permit r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_cu
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_reg_acct_dist
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_labor
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_material
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_mtl_notes
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_permit
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_service_detail
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_locat_wrksht
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_failure
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_service_data
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_cu_bid
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_location
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_reg_acct_oh
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_cu_itm
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_notes
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_assig
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_requirement
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_fixed_asset
WHERE plant = iPlant AND work_order_no = iWork_order_number
AND NVL (contributed_ind, 'N') = 'N';
DELETE FROM sa_asset_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_crew_work_log
(plant, crew, work_order_no, work_order_task_no, backlog_group,
event_type, event_date, event_desc, reported_by,
last_update_date, last_update_user, created_date, created_by)
SELECT plant, crew, work_order_no, work_order_task_no, backlog_group,
event_type, event_date, event_desc, reported_by, last_update_date,
last_update_user, created_date, created_by
FROM sa_crew_history_log
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_approval
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_attachment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_notes
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_crew_history_log
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_adjustment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_asset_lis
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_contract
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task
WHERE plant = iPlant AND work_order_no = iWork_order_number;
commit;
end;
end;
I used Toad SGA to extract these.
create or replace package xx_move_WO_from_WH
as
procedure move_wo(iPlant varchar2, iWork_order_number varchar2);
end;
/
create or replace package body xx_move_WO_from_WH
as
procedure move_wo(iPlant varchar2, iWork_order_number varchar2)
is
begin
INSERT INTO sa_work_order_task_cu
(plant, work_order_no, work_order_task_no,
accounting_treatment_type, usage_code, cu_no, cu_qty,
cu_estimate, unit_of_measure, cu_function, difficulty,
cu_actual_qty, standard_price_ind, discount_percent,
discount_amount, created_date, created_by, last_update_date,
last_update_user)
SELECT plant, work_order_no, work_order_task_no, accounting_treatment_type,
usage_code, cu_no, cu_qty, cu_estimate, unit_of_measure,
cu_function, difficulty, cu_actual_qty, standard_price_ind,
discount_percent, discount_amount, SYSDATE, USER, SYSDATE, USER
FROM sa_work_history_task_cu
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_attachment
(plant, work_order_no, work_order_task_no, attachment,
attachment_id, print_ind, attachment_revision_no)
SELECT plant, work_order_no, work_order_task_no, attachment, attachment_id,
print_ind, attachment_revision_no
FROM sa_work_history_attachment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order
(plant, work_order_no, work_class, work_type, asset_id,
asset_record_type, work_status, work_desc, asset_desc,
project_id, subproject_id, task_priority_default,
planned_total_amount, approved_amount, work_schedule_date,
work_entered_date, requestor_name, routing_list_id,
work_required_date, maintenance_manager, production_approver,
maintenance_approver, work_status_date, work_category,
benchmark_no, planner, pm_master_no, last_update_date,
safety_notes_ind, inspection_ind, lead_crew,
original_work_order_no, component_id, component_id_desc,
department, area, account_no, process_no, deficiency_tag,
building, LOCATION, POSITION, meter_units, meter_reading_last,
meter_reading_current, meter_ltd, vehicle_in_date,
vehicle_out_date, operator_id, shop, track_downtime_ind,
job_code, work_priority_total, work_priority_adjustment,
criticality, deliver_to_desc, work_form, meter_reading_date,
blanket_contract_no, sent_offsite_date, return_from_offsite_date,
blanket_revision_no, created_date, created_by, last_update_user,
signoff_by, signoff_date, actual_duration, actual_start_date,
actual_finish_date, finished_date, finished_by, closed_date,
closed_by, retain_type, retain_days, operational_reading_date,
asset_activity_log_no)
SELECT plant, work_order_no, work_class, work_type, asset_id,
asset_record_type, 'CLOSED', work_desc, asset_desc, project_id,
subproject_id, task_priority_default, planned_total_amount,
approved_amount, work_schedule_date, work_entered_date,
requestor_name, routing_list_id, work_required_date,
maintenance_manager, production_approver, maintenance_approver,
work_status_date, work_category, benchmark_no, planner,
pm_master_no, last_update_date, safety_notes_ind, inspection_ind,
lead_crew, original_work_order_no, component_id, component_id_desc,
department, area, account_no, process_no, deficiency_tag, building,
LOCATION, POSITION, meter_units, meter_reading_last,
meter_reading_current, meter_ltd, vehicle_in_date, vehicle_out_date,
operator_id, shop, track_downtime_ind, job_code,
work_priority_total, work_priority_adjustment, criticality,
deliver_to_desc, work_form, meter_reading_date, blanket_contract_no,
sent_offsite_date, return_from_offsite_date, blanket_revision_no,
created_date, created_by, last_update_user, signoff_by,
signoff_date, actual_duration, actual_start_date,
actual_finish_date, finished_date, finished_by, closed_date,
closed_by, retain_type, retain_days, operational_reading_date,
asset_activity_log_no
FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task
(plant, work_order_no, work_order_task_no, backlog_group,
task_status, task_status_date, task_desc, weekly_schedule_ind,
weekly_schedule_date, asset_record_type, process_no, asset_id,
downtime_ind, downtime_hours, assigned_to_name,
task_priority_total, task_priority_adjustment, task_priority,
criticality, account_no, specification_no,
specification_category, specification_type, asset_desc,
component_id_desc, daily_schedule_ind, daily_schedule_date, area,
department, bom_id, last_update_date, crew,
task_estimated_start_date, held_for_parts_ind, component_id,
estimated_duration_units, task_scheduled_shift,
task_required_date, estimated_duration, deliver_to_desc,
task_sequence_no, component_spec_no, component_spec_type,
component_spec_category, component_bom_id, work_request_no,
downtime_type, report_code1, report_code2, report_code3,
report_code4, report_code5, LOCATION, last_update_user, building,
POSITION, deficiency_tag, creation_date, work_code,
task_estimated_finish_date, job_code, created_by,
actual_duration, work_location, travel_time, work_trip,
actual_start_date, actual_finish_date, finished_date,
finished_by, type_of_work, type_of_work_units,
type_of_work_amount, comments, change_request_ind,
change_request_no, wbs, service_request_no, number_prefix,
street_number, number_suffix, street_name, street_direction,
cross_street, city, state_province, postal_code, offset,
direction, from_asset_record_type, to_asset_record_type,
from_asset_id, to_asset_id, point_id, iso_ind, environmental_ind,
run_to_failure_ind, health_ind, safety_critical_ind, task_phase,
planner, renewal_work_ind, street_number_char, shop, meter_units,
meter_ltd, meter_reading_current, job_reason_code,
job_action_code, vehicle_system_code, vehicle_assembly_code,
vehicle_component_code, asset_posted_date, asset_posted_ind,
mwm_field_order_no, mwm_order_type, sent_to_mwm_ind)
SELECT plant, work_order_no, work_order_task_no, backlog_group,
task_status, task_status_date, task_desc, weekly_schedule_ind,
weekly_schedule_date, asset_record_type, process_no, asset_id,
downtime_ind, downtime_hours, assigned_to_name, task_priority_total,
task_priority_adjustment, task_priority, criticality, account_no,
specification_no, specification_category, specification_type,
asset_desc, component_id_desc, daily_schedule_ind,
daily_schedule_date, area, department, bom_id, last_update_date,
crew, task_estimated_start_date, held_for_parts_ind, component_id,
estimated_duration_units, task_scheduled_shift, task_required_date,
estimated_duration, deliver_to_desc, task_sequence_no,
component_spec_no, component_spec_type, component_spec_category,
component_bom_id, work_request_no, downtime_type, report_code1,
report_code2, report_code3, report_code4, report_code5, LOCATION,
last_update_user, building, POSITION, deficiency_tag, creation_date,
work_code, task_estimated_finish_date, job_code, created_by,
actual_duration, work_location, travel_time, work_trip,
actual_start_date, actual_finish_date, finished_date, finished_by,
type_of_work, type_of_work_units, type_of_work_amount, comments,
change_request_ind, change_request_no, wbs, service_request_no,
number_prefix, street_number, number_suffix, street_name,
street_direction, cross_street, city, state_province, postal_code,
offset, direction, from_asset_record_type, to_asset_record_type,
from_asset_id, to_asset_id, point_id, NVL (iso_ind, 'N'),
NVL (environmental_ind, 'N'), NVL (run_to_failure_ind, 'N'),
NVL (health_ind, 'N'), NVL (safety_critical_ind, 'N'), task_phase,
planner, renewal_work_ind, street_number_char, shop, meter_units,
meter_ltd, meter_reading_current, job_reason_code, job_action_code,
vehicle_system_code, vehicle_assembly_code, vehicle_component_code,
asset_posted_date, asset_posted_ind, mwm_field_order_no,
mwm_order_type, NVL (sent_to_mwm_ind, 'N')
FROM sa_work_history_task
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_approval
(plant, work_order_no, approval_title, username, approval_status,
approval_status_date, approval_amount, approval_comments)
SELECT plant, work_order_no, approval_title, username, approval_status,
approval_status_date, approval_amount, approval_comments
FROM sa_work_history_approval
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_location_wrksht
(plant, work_order_no, work_order_task_no, cu_location,
accounting_treatment_type, usage_code, cu_no, cu_qty,
cu_actual_qty, cu_estimate, unit_of_measure, cu_function,
difficulty, standard_price_ind, discount_percent,
discount_amount, created_date, created_by, last_update_date,
last_update_user, cu_qty_calculated, no_of_conductors)
SELECT plant, work_order_no, work_order_task_no, cu_location,
accounting_treatment_type, usage_code, cu_no, cu_qty, cu_actual_qty,
cu_estimate, unit_of_measure, cu_function, difficulty,
standard_price_ind, discount_percent, discount_amount, SYSDATE,
USER, SYSDATE, USER, cu_qty_calculated, no_of_conductors
FROM sa_work_history_locat_wrksht
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_material
(plant, work_order_no, work_order_task_no, item_sequence_no,
item_status, item_status_date, last_update_date, vendor_contact,
vendor_code, blanket_contract_no, item_required_date, unit_price,
po_no, unit_of_issue, item_desc, internal_committed_amount,
internal_committed_quantity, external_committed_amount,
external_committed_quantity, revised_estimate_amount,
accrued_amount, original_estimate_amount, actual_amount,
actual_quantity, accrued_quantity, revised_estimate_quantity,
original_estimate_quantity, component_id, stock_type, stock_code,
po_item_type, issued_to_date_quantity, storeroom,
requisition_originator, cost_category, routing_list_id,
requisition_item, po_item, requisition_no, buyer, vendor_name,
deliver_to_department, expense_code, expense_category,
credit_card_purchase_ind, credit_card_holder_name, created_date,
created_by, last_update_user, reported_used_quantity)
SELECT plant, work_order_no, work_order_task_no, item_sequence_no,
item_status, item_status_date, last_update_date, vendor_contact,
vendor_code, blanket_contract_no, item_required_date, unit_price,
po_no, unit_of_issue, item_desc, internal_committed_amount,
internal_committed_quantity, external_committed_amount,
external_committed_quantity, revised_estimate_amount,
accrued_amount, original_estimate_amount, actual_amount,
actual_quantity, accrued_quantity, revised_estimate_quantity,
original_estimate_quantity, component_id, stock_type, stock_code,
po_item_type, issued_to_date_quantity, storeroom,
requisition_originator, cost_category, routing_list_id,
requisition_item, po_item, requisition_no, buyer, vendor_name,
deliver_to_department, expense_code, expense_category,
credit_card_purchase_ind, credit_card_holder_name, created_date,
created_by, last_update_user, reported_used_quantity
FROM sa_work_history_material
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_labor
(plant, work_order_no, work_order_task_no, labor_sequence_no,
craft, craftsmen_quantity, expense_code, premium_expense_code,
original_estimate_amount, revised_estimate_amount,
internal_committed_amount, external_committed_amount,
actual_amount, actual_premium_amount, original_estimate_hours,
revised_estimate_hours, internal_committed_hours,
external_committed_hours, actual_hours, actual_premium_hours,
last_update_date, last_update_user, burden_regular_amount,
burden_premium_amount, burden_regular_expense_code,
burden_premium_expense_code, labor_duration)
SELECT plant, work_order_no, work_order_task_no, labor_sequence_no, craft,
craftsmen_quantity, expense_code, premium_expense_code,
original_estimate_amount, revised_estimate_amount,
internal_committed_amount, external_committed_amount, actual_amount,
actual_premium_amount, original_estimate_hours,
revised_estimate_hours, internal_committed_hours,
external_committed_hours, actual_hours, actual_premium_hours,
last_update_date, last_update_user, burden_regular_amount,
burden_premium_amount, burden_regular_expense_code,
burden_premium_expense_code, labor_duration
FROM sa_work_history_labor
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_requirement
(plant, work_order_no, work_order_task_no, item_sequence_no,
requirement_type, requirement_quantity, requirement_units,
requirement_standard_price, requirement_duration,
requirement_total_amount, original_estimate_qty,
original_estimate_amount, revised_estimate_qty,
revised_estimate_amount, committed_qty, committed_amount,
actual_qty, actual_amount, expense_code, created_date,
created_by, last_update_date, last_update_user)
SELECT r.plant, r.work_order_no, r.work_order_task_no, r.item_sequence_no,
r.requirement_type, r.requirement_quantity, r.requirement_units,
r.requirement_standard_price, r.requirement_duration,
r.requirement_total_amount, r.original_estimate_qty,
r.original_estimate_amount, r.revised_estimate_qty,
r.revised_estimate_amount, r.committed_qty, r.committed_amount,
r.actual_qty, r.actual_amount, r.expense_code, SYSDATE, USER,
SYSDATE, USER
FROM sa_work_history_requirement r
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_cu_items
(plant, item_sequence_no, work_order_no, work_order_task_no,
accounting_treatment_type, item_type, item_id, storeroom,
item_qty, unit_of_measure, detail_sequence_no, item_duration,
unit_price, expense_code, premium_expense_code, created_date,
created_by, last_update_date, last_update_user, item_desc)
SELECT plant, item_sequence_no, work_order_no, work_order_task_no,
accounting_treatment_type, item_type, item_id, storeroom, item_qty,
unit_of_measure, detail_sequence_no, item_duration, unit_price,
expense_code, premium_expense_code, SYSDATE, USER, SYSDATE, USER,
item_desc
FROM sa_work_history_task_cu_itm
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_reg_acct_dist
(plant, work_order_no, work_order_task_no, regulatory_account_no,
regulatory_account_desc, calculated_percent_split,
override_percent_split, wip_account_no, regulatory_account_type,
direct_oh_percent_split, cu_estimate_total, created_date,
created_by, last_update_date, last_update_user)
SELECT plant, work_order_no, work_order_task_no, regulatory_account_no,
regulatory_account_desc, calculated_percent_split,
override_percent_split, wip_account_no, regulatory_account_type,
direct_oh_percent_split, cu_estimate_total, SYSDATE, USER, SYSDATE,
USER
FROM sa_work_history_reg_acct_dist
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_cu_bid
(plant, work_order_no, work_order_task_no, bid_date, cu_no,
contractor, bid_amount, material_ind, expense_code,
detail_sequence_no, labor_ind, created_date, created_by,
last_update_date, last_update_user, cu_function, difficulty)
SELECT plant, work_order_no, work_order_task_no, bid_date, cu_no,
contractor, bid_amount, material_ind, expense_code,
detail_sequence_no, labor_ind, SYSDATE, USER, SYSDATE, USER,
cu_function, difficulty
FROM sa_work_history_task_cu_bid
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_reg_acct_oh
(plant, work_order_no, work_order_task_no, overhead_class,
estimated_overhead_percent, applied_expense_category,
created_date, created_by, last_update_date, last_update_user)
SELECT plant, work_order_no, work_order_task_no, overhead_class,
estimated_overhead_percent, applied_expense_category, SYSDATE, USER,
SYSDATE, USER
FROM sa_work_history_reg_acct_oh
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_location
(plant, work_order_no, work_order_task_no, cu_location,
location_desc, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, created_date, created_by, last_update_date,
last_update_user, work_design_no, structure_type, SOURCE,
distance, distance_units, gis_gps_latitude, gis_gps_longitude,
overhead_ind, underground_ind)
SELECT plant, work_order_no, work_order_task_no, cu_location,
location_desc, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, SYSDATE, USER, SYSDATE, USER, work_design_no,
structure_type, SOURCE, distance, distance_units, gis_gps_latitude,
gis_gps_longitude, overhead_ind, underground_ind
FROM sa_work_history_location
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_fixed_asset
(plant, work_order_no, work_order_task_no, item_no, cu_no,
property_unit_no, cu_location, action, asset_record_type,
asset_id, action_date, regulatory_account_no,
regulatory_account_type, created_date, created_by,
last_update_date, last_update_user, asset_value, period_year,
apply_prior_year, contributed_ind, cu_function,
change_request_no, asset_change_seq_no, number_of_units)
SELECT a.plant, a.work_order_no, a.work_order_task_no, a.item_no, a.cu_no,
a.property_unit_no, a.cu_location, a.action, a.asset_record_type,
a.asset_id, a.action_date, a.regulatory_account_no,
a.regulatory_account_type, SYSDATE, USER, SYSDATE, USER,
a.asset_value, a.period_year, a.apply_prior_year, a.contributed_ind,
a.cu_function, a.change_request_no, a.asset_change_seq_no,
a.number_of_units
FROM sa_work_history_fixed_asset a
WHERE a.plant = iPlant
AND a.work_order_no = iWork_order_number
AND NVL (a.contributed_ind, 'N') = 'N';
INSERT INTO sa_work_order_adjustment
(plant, work_order_no, work_order_task_no, expense_code,
actual_amount, last_update_date)
SELECT plant, work_order_no, work_order_task_no, expense_code,
actual_amount, last_update_date
FROM sa_work_history_adjustment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order_material w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history_material r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no
AND r.item_sequence_no = w.item_sequence_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
UPDATE sa_work_order
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number)
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order_material w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history_material r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no
AND r.item_sequence_no = w.item_sequence_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
INSERT INTO sa_work_order_service_contract
(plant, work_order_no, work_order_task_no, labor_sequence_no,
service_contract_no, contract_revision_no, contract_item,
item_id, craftsmen_quantity, rate, rate_type, expense_code,
original_estimate_amount, revised_estimate_amount,
committed_amount, accrued_amount, actual_amount,
original_estimate_quantity, revised_estimate_quantity,
committed_quantity, accrued_quantity, actual_quantity,
last_update_date, last_update_user)
SELECT plant, work_order_no, work_order_task_no, labor_sequence_no,
service_contract_no, contract_revision_no, contract_item, item_id,
craftsmen_quantity, rate, rate_type, expense_code,
original_estimate_amount, revised_estimate_amount, committed_amount,
accrued_amount, actual_amount, original_estimate_quantity,
revised_estimate_quantity, committed_quantity, accrued_quantity,
actual_quantity, last_update_date, last_update_user
FROM sa_work_history_contract
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_asset_list
(work_order_no, work_order_task_no, sequence_no, plant,
asset_record_type, asset_id, component_id, percent_split,
total_amount, created_date, created_by, last_update_date,
last_update_user)
SELECT work_order_no, work_order_task_no, sequence_no, plant,
asset_record_type, asset_id, component_id, percent_split,
total_amount, created_date, created_by, last_update_date,
last_update_user
FROM sa_work_history_task_asset_lis
WHERE work_order_no = iWork_order_number AND plant = iPlant;
BEGIN
UPDATE sa_work_order_task w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history_task r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
END;
INSERT INTO sa_work_order_permit
(plant, work_order_no, work_order_task_no, permit_type, permit_no,
permit_acquired_date, last_update_date, created_date, created_by,
last_update_user, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, permit_template_id, asset_record_type, asset_id)
SELECT plant, work_order_no, work_order_task_no, permit_type, permit_no,
permit_acquired_date, last_update_date, created_date, created_by,
last_update_user, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, permit_template_id, asset_record_type, asset_id
FROM sa_work_history_permit
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_service_history_data
(plant, work_order_no, work_order_task_no, cu_location,
specification_type, specification_category)
SELECT plant, work_order_no, work_order_task_no, cu_location,
specification_type, specification_category
FROM sa_work_history_service_data
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_service_history_details
(plant, work_order_no, work_order_task_no, specification_type,
specification_category, attribute_sequence_no, attribute_value,
attribute_desc)
SELECT plant, work_order_no, work_order_task_no, specification_type,
specification_category, attribute_sequence_no, attribute_value,
attribute_desc
FROM sa_work_history_service_detail
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_material_attachment
(plant, work_order_no, work_order_task_no, item_sequence_no,
attachment, attachment_id, copy_to_purchase_order, print_ind,
last_update_date, last_update_user, created_date, created_by,
attachment_revision_no)
SELECT plant, work_order_no, work_order_task_no, item_sequence_no,
attachment, attachment_id, copy_to_purchase_order, print_ind,
SYSDATE, USER, SYSDATE, USER, attachment_revision_no
FROM sa_work_history_mtl_attch
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_work_order_task_failure
(plant, work_order_no, work_order_task_no, asset_id,
asset_record_type, component_id, failure_code, repair_code,
component_code, primary_ind, failure_mode, root_cause,
work_request_no, further_action, further_action_desc,
further_action_required_date, last_update_date, last_update_user,
created_date, created_by, sequence_no)
SELECT plant, work_order_no, work_order_task_no, asset_id,
asset_record_type, component_id, failure_code, repair_code,
component_code, primary_ind, failure_mode, root_cause,
work_request_no, further_action, further_action_desc,
further_action_required_date, last_update_date, last_update_user,
created_date, created_by, sequence_no
FROM sa_work_order_task_failure
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number)
WHERE plant = iPlant AND work_order_no = iWork_order_number;
UPDATE sa_work_order_task w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history_task r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
UPDATE sa_work_order_permit w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1
FROM sa_work_history_permit r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
UPDATE sa_work_order_permit w
SET (attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10) =
(SELECT attribute1, attribute1, attribute1, attribute1, attribute1,
attribute1, attribute1, attribute1, attribute1, attribute1
FROM sa_work_history_permit r
WHERE r.plant = iPlant
AND r.work_order_no = iWork_order_number
AND r.work_order_task_no = w.work_order_task_no)
WHERE w.plant = iPlant AND w.work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_cu
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_reg_acct_dist
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_labor
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_material
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_mtl_notes
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_permit
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_service_detail
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_locat_wrksht
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_failure
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_service_data
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_cu_bid
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_location
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_reg_acct_oh
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_cu_itm
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_notes
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_assig
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_requirement
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_fixed_asset
WHERE plant = iPlant AND work_order_no = iWork_order_number
AND NVL (contributed_ind, 'N') = 'N';
DELETE FROM sa_asset_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number;
INSERT INTO sa_crew_work_log
(plant, crew, work_order_no, work_order_task_no, backlog_group,
event_type, event_date, event_desc, reported_by,
last_update_date, last_update_user, created_date, created_by)
SELECT plant, crew, work_order_no, work_order_task_no, backlog_group,
event_type, event_date, event_desc, reported_by, last_update_date,
last_update_user, created_date, created_by
FROM sa_crew_history_log
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_approval
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_attachment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_notes
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_crew_history_log
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_adjustment
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task_asset_lis
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_contract
WHERE plant = iPlant AND work_order_no = iWork_order_number;
DELETE FROM sa_work_history_task
WHERE plant = iPlant AND work_order_no = iWork_order_number;
commit;
end;
end;
Sunday, November 1, 2009
Work Order History
The WO aging business rule has two parts.
The first, Auto Close After, sets the number of days from when a work order is finished to when it is set to closed status. The second, Retain Days, sets the number of days from when a work order is set to closed status to when it is moved from the work order tables to the history tables.
We had these set to 180 and 90 days respectively.
The problem we are having is that we do a lot of Year to Date reporting, and the work orders are dropping off the report after nine months. Our reports are coded to only look at the Work Order tables, not the work history tables. So, as the work orders moved to history, the data disappeared from the reports.
I tried to re-code the reports to look at both work order and history tables, but the performance was unacceptable.
You can blank out the Retain Days field, so that the work orders will never move to history. This is what I would recommend.
For us, we are going to have to blank it out and then copy the data back into the Work order tables from work history using scripts.
The first, Auto Close After, sets the number of days from when a work order is finished to when it is set to closed status. The second, Retain Days, sets the number of days from when a work order is set to closed status to when it is moved from the work order tables to the history tables.
We had these set to 180 and 90 days respectively.
The problem we are having is that we do a lot of Year to Date reporting, and the work orders are dropping off the report after nine months. Our reports are coded to only look at the Work Order tables, not the work history tables. So, as the work orders moved to history, the data disappeared from the reports.
I tried to re-code the reports to look at both work order and history tables, but the performance was unacceptable.
You can blank out the Retain Days field, so that the work orders will never move to history. This is what I would recommend.
For us, we are going to have to blank it out and then copy the data back into the Work order tables from work history using scripts.
Subscribe to:
Posts (Atom)