• Author / Uploaded
• ajay78_hi

• Categories
• Base de datos Oracle
• SQL
• Pl / Sql
• Bases de datos
• Software de gestión de datos

Citation preview

© 2009 Oracle Corporation – Proprietary and Confidential

1

Demantra Solutions And Value Chain Planning Live Advisor Webcast MarchDay, 14,Date, 20122004 time p.m. ET am 11:00 am ET / 9:00 MT / 8:00 am PT Teleconference Access:

VOICE STREAMING North America: xxxx ENABLED

International: xxxx Password: Advisor

North America: (866) 627-3315

International: +1-706-758-7972 Doc Id: 1148600.1 Conf Id: 49842549 or Oracle DEM

© 2012 Oracle Corporation – Proprietary and Confidential

Upcoming Community Webcasts: April 11, 2012: Using the Automatic Workload Repository (AWR) to Cool Demantra Hot Spots in Doc Id 800030.1 April 12, 2012: 12.1.3.6 Rapid Planning Enhancements in Doc Id 837233.1 For complete details on Demantra Solutions Webcast Events, please see Note 800030.1. For complete details on Value Chain Planning Webcast Events, please see Note 837233.1. For complete details on All Other upcoming Oracle Advisor Webcast Events, please Note 740966.1. Do you have any topic requests for future Advisor Webcast Events? Please email your suggestions to [email protected], subject: Topics of Interest.

2



Troubleshooting Demantra Forecast Export to ASCP Manuela Ghita Senior Technical Support Engineer

Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

© 2012 Oracle Corporation – Proprietary and Confidential

4



Troubleshooting Demantra Forecast Export to ASCP Manuela Ghita Senior Technical Support Engineer

AGENDA • Export Integration Interface • Workflow to export data • Check data • BIEO_% • msd_dp_scn_entries_denorm • msd_dp_ascp_scenarios_v

• Known issues

© 2012 Oracle Corporation – Proprietary and Confidential

6

Export Integration Interface

•The integration interface profiles define the data, levels and intermediary tables involved with the integrations. •Integration interfaces are define in Business Modeler > Tools > Integration Interface •If you want to export and use multiple sets of forecast entries then you should create as many data profiles and as many workflows and select those data profiles as demand schedules in the ASCP plan options.

© 2012 Oracle Corporation – Proprietary and Confidential

7

Data Profile Interface •Integration Type: Export •Export Date : Full - Incremental export data is not supported

© 2012 Oracle Corporation – Proprietary and Confidential

8

Predefined integration interfaces For Demantra Demand Management to EBS Integration : •Local Forecast •Global Zone Forecast •Local Forecast with Demand Class •Global Zone forecast, Demand Class For Configure to Order: •CTO Local Forecast •CTO Global Forecast For Demantra Sales and Operations Planning to EBS Integration: •Upload Consensus Forecast - Org, Week •Upload Consensus Forecast - Zone, Week •Those can be modified so you can generate forecast exports that best suit your organization

© 2012 Oracle Corporation – Proprietary and Confidential

9

If you want to export a local forecast , you need to add the Organization level

If you do NOT add Organization level into your Integration Interface, then the forecast will be exported as global forecast

© 2010 Oracle Corporation – Proprietary and Confidential

10

Data Profile for exporting data to ASCP

For Series: •Forecast Series - predefined Final Forecast series or any other series where the internal name should start with 'FCST_‘ •Demand Priority Series - The internal name should start with 'PRTY_‘ •Forecast Accuracy Series - The internal name should start with 'ACRY_‘ •Destination Key Series - The internal name should start with 'DKEY_‘

For levels : •Item or Product Family (if Item is chosen then the series Item Destination Key should also be selected for export) •Organization •Site or Account or Trading Partner Zone or Zone •Demand Class •S&OP Scenario

© 2010 Oracle Corporation – Proprietary and Confidential

11

Check the Time

Check if the period you want to export forecast is the one between From Date and Until Date

The dates should match your time buckets and you can find those dates in inputs table

© 2012 Oracle Corporation – Proprietary and Confidential

12

BIEO_% intermediate view

Existing ASCP and Oracle Demantra capabilities, accommodate forecasting by line of business (LOB).

© 2010 Oracle Corporation – Proprietary and Confidential

13

Workflow to export data There are predefined upload workflows. For Demantra Demand Management to EBS Integration •EBS Upload Local Forecast •EBS Upload Global Zone Forecast •EBS Upload Local Forecast, Demand Class •EBS Upload Global Zone Forecast, Demand Class For Configure to Order: •CTO Upload Local Forecast - Org, Week •CTO Upload Global Forecast - Zone, Week

For Demantra Sales and Operations Planning to EBS Integration: •Upload Consensus Forecast-Org,Week •Upload Consensus Forecast-Zone,Week We can create our own workflow to export the forecast.

© 2010 Oracle Corporation – Proprietary and Confidential

14

EBS Upload Local Forecast

It should include 2 main steps: •Transfer step •Store procedure step

© 2010 Oracle Corporation – Proprietary and Confidential

15

Transfer step

•export data from demantra internal tables to BIEO_% view •Type : Export •ensure you are selecting the right Integration Interface •for multiple profiles for the same integration interface, choose Data and select the right profile •check collaborator.log and integration.log files for any error messages •check db_exception_log table for errors

© 2010 Oracle Corporation – Proprietary and Confidential

16

Store Procedure Step

•UPLOAD_FORECAST – for old demantra versions •UPLOAD_FORECAST_WITH_APP_ID – calls UPLOAD_FORECAST •UPLOAD_PLNG_PCTG_WITH_APP_ID calls UPLOAD_PLANNING_PERCENTAGES •UPLOAD_CTO_FCST_WITH_APP_ID calls UPLOAD_TOTAL_DEMAND

The parameter of the store procedure should match the APPLICATION_ID value from transfer_query table for your integration interface. In this case is 'TRANSFER_QUERY:353‘: select APPLICATION_ID from transfer_query where QUERY_NAME = 'Local Forecast';

© 2010 Oracle Corporation – Proprietary and Confidential

17

Store Procedure step

If the workflow is failing at the Store Procedure step: • check collaborator.log file for any error message • check INTEG_STATUS messages

• you may troubleshoot it by manually executing manually the procedure : begin apps.msd_dem_upload_forecast.upload_forecast_with_app_id('TRANSFER_QUERY:353'); end;

© 2010 Oracle Corporation – Proprietary and Confidential

18

Check data

•run the workflow •ensure that this workflow is executed successfully •check BIEO_% view (BIEO_Local_Fcst for our case) •check msd_dp_scn_entries_denorm table •check that msd_dp_ascp_scenarios_v is having the forecast plan name.

© 2010 Oracle Corporation – Proprietary and Confidential

19

Check BIEO_% view

•BIEO_% view must exist •BIEO_% view must have data • Forecast should not be null

© 2010 Oracle Corporation – Proprietary and Confidential

20

Check BIEO_% view

•if the BIEO_% view is not getting populated with the right values •Review your integration interface definition. Maybe you do not have forecast to export for the time and filters you have specified on the integration interface •Create a worksheet with the same levels, time, series, filters as you have in the integration interface and ensure you have data

© 2010 Oracle Corporation – Proprietary and Confidential

21

Check BIEO_% view If not all members are exported to the BIEO_% view •export views use FROM_DATE and UNTIL_DATE columns of mdp_matrix table to determine the correct population to include in their queries Check if FROM_DATE and UNTIL_DATE columns from mdp_matrix are out of sync with sales_data records by running the following sql (it should not return any results unless you have this issue) SELECT ITEM_ID,LOCATION_ID FROM ( SELECT ITEM_ID,LOCATION_ID,FROM_DATE,UNTIL_DATE FROM MDP_MATRIX UNION SELECT ITEM_ID,LOCATION_ID, MIN(SALES_DATE), MAX(SALES_DATE) FROM SALES_DATA GROUP BY ITEM_ID,LOCATION_ID) GROUP BY ITEM_ID,LOCATION_ID HAVING COUNT(*) > 1 ORDER BY ITEM_ID,LOCATION_ID;

Please run the following to synch up these columns: UPDATE MDP_MATRIX SET FROM_DATE = NULL, UNTIL_DATE = NULL; COMMIT; EXEC UPDATE_MDP_MATRIX_DATES;

© 2010 Oracle Corporation – Proprietary and Confidential

22

Check msd_dp_scn_entries_denorm table

•demand_plan_id for all demantra forecasts is hardcoded to 5555555. •scenario_id is calculated as 5555555 + the id the data profile used for export . In our case: 5555555 + 353 = 5555908

© 2010 Oracle Corporation – Proprietary and Confidential

23

Check msd_dp_scn_entries_denorm table If msd_dp_scn_entries_denorm table is not populated for the demantra plan, it could be a mismatch for Item or Organization code between demantra and EBS or Profile option MSC: Organization containing generic BOM for forecast explosion was not populated with a value in the source instance and you may need to reload your data For an item that the forecast was loaded into BIEO_% view but was not loaded into msd_dp_scn_entries_denorm table • item must exist on source instance Inventory responsibility> Items > Master Item •Planning Method should not be “Not planned” mrp_planning_code 6 from msc_system_items •Forecast Control item attribute should not be “None“ ato_forecast_control 3 • inventory_item_id from msc_system_items should match the column dkey_item from the BIEO_% view . This is taken from ebs_item_dest_key field of t_ep_item table

© 2010 Oracle Corporation – Proprietary and Confidential

24

Check msd_dp_ascp_scenarios_v view •if demantra plan does not show in this view, then it will not be seen in the ASCP plan names/options form .

msd_dp_ascp_scenarios_v (view definition for demand_plan_id = 5555555) SELECT 5555555 demand_plan_id, SUBSTR(tl.name, 1, 30) demand_plan_name, tq.id + 5555555 scenario_id, SUBSTR(tq.query_name, 1, 30) scenario_name, -23453 organization_id, msd_dem_upload_forecast.get_sr_instance_id_for_profile(tq.id) sr_instance_id, msd_dem_upload_forecast.get_error_type(tq.id) error_type, 'Y' consume_flag, msd_dem_upload_forecast.is_global_scenario (tq.id) global_scenario_flag, '1' last_revision FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq WHERE tl.id = tq.transfer_id AND tq.integration_type 1 AND tq.export_type = 1 AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1;

© 2010 Oracle Corporation – Proprietary and Confidential

25

Check msd_dp_ascp_scenarios_v view •usually, this is fixed by running the concurrent program 'Update Synonyms‘

Demand Management System Administrator Responsibility -> Other -> Requests -> Submit a New Request -> Single Request

© 2010 Oracle Corporation – Proprietary and Confidential

26

Update Synonyms

The "Update Synonyms" concurrent program has a hidden parameter pSchemaName •If the user has multiple demantra schemas then user can enable hidden parameter to visibility. System Administrator Responsibility -> Concurrent -> Program -> Define •If the user has only one Demantra schema then the user can leave the parameter to be hidden. But, if the user has multiple demantra schemas then user can enable hidden parameter to visibility

© 2010 Oracle Corporation – Proprietary and Confidential

27

Known issues

Data is populated into msd_dp_scn_entries_denorm and demantra plan shows up in msd_dp_ascp_scenarios_v but when login to ASCP instance, under Advance Supply Chain Planner responsibility, look for the plan options Query for the plan and under organizations, choose the demand schedule and LOV has no value. •Local Forecast will be displayed in "Demand Schedules" under Organizations but not in "Global Demand Schedules". It is local, when global_scenario_flag from msd_dp_ascp_scenarios_v is N. •Global Forecast will be displayed in "Global Demand Schedules" but not in "Demand Schedules" under Organizations.

© 2010 Oracle Corporation – Proprietary and Confidential

28

Local forecast

sql used in plan options for form for demand schedules for local schedules select scn.scenario_name, scn.demand_plan_name||':'||scn.scenario_name, lu.meaning, 7 designator_type, scn.scenario_id input_schedule_id, scn.error_type error_type , null ship_to from mfg_lookups lu, msd_dp_ascp_scenarios_v scn where lu.lookup_type = 'MSC_DESIGNATOR_TYPE_SHORT' and lu.lookup_code = 7 and scn.global_scenario_flag ='N' and scn.scenario_name :mrp_plans.compile_designator and ( scn.sr_instance_id = -23453 or scn.sr_instance_id = :mrp_plan_orgs.sr_instance_id ) and scn.last_revision is not null ; :mrp_plans.compile_designator is a variable from the form. You can use help - diagnostics - examine to see the exact value.

© 2010 Oracle Corporation – Proprietary and Confidential

29

Global forecast

sql used in plan options for form for demand schedules for global schedules select scn.scenario_name designator, scn.demand_plan_name||':'||scn.scenario_name description, lu.meaning , 7 designator_type, scn.scenario_id input_schedule_id, scn.sr_instance_id from mfg_lookups lu, msd_dp_ascp_scenarios_v scn where lu.lookup_type = 'MSC_DESIGNATOR_TYPE_SHORT' and lu.lookup_code = 7 and scn.global_scenario_flag = 'Y' and scn.scenario_name :mrp_plans.compile_designator and scn.last_revision is not null ORDER BY 1,2;.

© 2010 Oracle Corporation – Proprietary and Confidential

30

ERRORS •Error: msd_dem_upload_forecast.upload_forecast_with_app_id - Export Data Profile Application ID is null Solution:you have not provided any parameter to the store procedure step of the workflow. You should provide the application_id •Error description 'Database internal error:ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - ORA20003: Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find data profile ORA-01403: no data found‘ •Troubleshoot : •you had provided wrong parameter to the store procedure step of the workflow. You should provide the application_id • Log into your database as your demantra user and execute: select query_name FROM TRANSFER_QUERY WHERE application_id = ; It should return 1 records , for your integration interface. If not, then check again the application_id for your integration interface: select APPLICATION_ID from transfer_query where QUERY_NAME = ; Then pass the right parameter to your store procedure step. •Log into your database as apps user and execute: select query_name FROM .TRANSFER_QUERY WHERE application_id = ; Replace with your demantra schema name. If it does not return any row, but it does when you are logged in as your demantra user, you may either have demantra schema on a different database then the apps (that is not supported) or you are missing some privileges. Please ensure you had run Update Synonyms •For a predefined integration interface: select APPLICATION_ID from transfer_query where QUERY_NAME = 'Global Zone Forecast'; APPLICATION_ID --------------------------------------------------------------B7C2E4FD6B7B32E7E040FE0A76A44C65 Cause: Rebuilding data model (as opposed to update model) will delete some objects which will cause issues like the ORA20015 error Solution: Restore from a backup created before Rebuilding the Data Model

© 2010 Oracle Corporation – Proprietary and Confidential

31

ERRORS While trying to upload Global Zone Forecast an error occurs. Error description:'Database internal error:ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - ORA-06550: line 1, column 15‘ Collaborator.log file shows: [WFProcess_7] FATAL workflow.general: ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast_with_app_id ORA-06550: line 1, column 15: PLS-00904: insufficient privilege to access object DL_LOG_STATUS ORA-06550: line 1, column 8: PL/SQL: Statement i ORA-06512: at "APPS.MSD_DEM_UPLOAD_FORECAST", line 1276 ORA-06512: at line 1 java.sql.SQLException: ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast_with_app_id ORA-06550: line 1, column 15: PLS-00904: insufficient privilege to access object ORADEM.DL_LOG_STATUS ORA-06550: line 1, column 8: PL/SQL: Statement i ORA-06512: at "APPS.MSD_DEM_UPLOAD_FORECAST", line 1276 ORA-06512: at line 1

Cause: •There is an issue with privileges. 'APPS' user is missing execute privilege on the procedure 'DL_LOG_STATUS‘ : Solution: • Run the following from demantra schema : GRANT EXECUTE ON DL_LOG_STATUS TO APPS; • Run again the workflow and check if it completes without error.

© 2010 Oracle Corporation – Proprietary and Confidential

32

ERRORS

While running ’EBS Upload Global Zone Forecast’ workflow, the following error shows up: Process ID: 781628 Schema ID: 635 Schema name: ’’EBS Upload Global Zone Forecast’’ Step ID: ’’PopulateDenormTable’’ Step name: ’’PopulateDenormTable’’ Error description ’’Database internal error:ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast - ORA-20011: Error: msd_dem_upload_forecast.upload_forecast - Unable to get sr_instance_’’ Cause Profile option MSC: Organization containing generic BOM for forecast explosion was not populated with a value in the source instance. SELECT fnd_profile.value('MSC_ORG_FOR_BOM_EXPLOSION') FROM dual; return NULL Solution: •Set profile option MSC: Organization containing generic BOM for forecast explosion with a value in the source instance. It should be set to the organization containing your Master Items or the organization setup for validations of BOM‘s •Rerun ASCP Standard Collection to pickup the profile change.

© 2010 Oracle Corporation – Proprietary and Confidential

33

ERRORS

•Error: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find schema name Solution: •you have not set up MSD_DEM: Schema profile name •MSD_DEM: Schema profile name was wrongly set up. Connect as apps user and execute: select OWNER from all_objects where object_name = 'SALES_DATA'; • your demantra schema reside on a different database then apps schema.

•Process ID: 2562 Schema ID: 1153 Schema name: ''Fcst Exp ASCP'' Step ID: ''PopulateDenormTable'' Step name: ''PopulateDenormTable'' Error description ''Database internal error: ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast - ORA-20005: Error: : msd_dem_upload_forecast.upload_forecast - Incremental export type is not supported''. Solution: Log in to Business Modeler > Tools > Integration Interface > Data Profile Naming page for option "Export Data" use only "FULL“

34

ERRORS •Running the Workflow EBS Upload Local Forecast or Upload Consensus Forecast-Org,Week is failing at the populate Denorm Table step. In the Collaborator Workbench this was the error that was displayed. Error description : Database internal error:ORA-6550: line 1, column 36: Following is the error in the collaborator.log – PLS-302: component 'UPLOAD_FORECAST_WITH_APP_ID' must be declared Cause: Demantra was upgraded to 7.3 and now the workflow were calling UPLOAD_FORECAST_WITH_APP_ID while previously were calling UPLOAD_FORECAST Solution : apply latest EBS-Demantra Integration Patch •CTO UPLOAD GLOBAL FORECAST - DEMAND CLASS, WEEK NOT PUBLISHING DATA Navigation -> Demand Maangement System Administrator -> Workflow Manager -> CTO Upload Global Forecast - Demand Class, Week We can also see the status of workflow in INTEG_STATUS Table and its showing loaded data. We can see view generated based on integration profile shows all data but when workflow calls "apps.msd_dem_upload_forecast.UPLOAD_FORECAST_WITH_APP_ID", its not publishing any data in DENORM Table. Cause: 'MSC: Organization containing generic BOM for forecast explosion' profile must be set to some value and then run Standard collections. Solution: set up profiles 1) MSD_DEM: Source Instance for Global Forecast 2) MSC: Organization containing generic BOM for forecast explosion

35

•

To ask a question on the phone line, select *1 on your phone.

•

To ask a question online, use the Q&A area at the top.

•

Your question will be read aloud in the order received. Question can also be asked on the My Oracle Support Communities

•

© 2012 Oracle Corporation – Proprietary and Confidential

36

Visit My Oracle Communities Collaborate with a large network of your industry peers, support professionals, and Oracle experts to exchange information, ask questions & get answers. Find out how your peers are using Oracle technologies and services to better meet their support and business needs. •

Exchange Knowledge

•

Resolve Issues

•

Gain Expertise

Visit the My Oracle Support Community now!! 1.

Log into My Oracle Support.

2.

Select the Community tab.

3.

Select the Enter Here button.

4.

Select the Community Name link under the E-Business Suite section of the My Communities Menu on the left side of the window.

© 2012 Oracle Corporation – Proprietary and Confidential

37

Demantra Solutions Advisor Webcast Calendar and Archive (Doc Id 800030.1)

© 2012 Oracle Corporation – Proprietary and Confidential

38

Value Chain Planning Advisor Webcast Calendar and Archive (Doc Id 837233.1)

© 2012 Oracle Corporation – Proprietary and Confidential

39

THANK YOU

© 2012 Oracle Corporation – Proprietary and Confidential

40