ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F45116C306511365E0530100007FE422Y select null action_, null status_, null message_, null request_id_, null modified_columns_, to_char(null) row_id, to_char(null) request_id, :p_upload_mode upload_mode, to_char(null) source_code, :p_replace_forecast replace_forecast, -- mp.organization_code, mfds.forecast_set, mfds.forecast_designator forecast, mfds.description forecast_description, msiv.concatenated_segments item, msiv.description item_description, msiv.primary_uom_code uom, msiv.planner_code planner, mfd.forecast_date forecast_date, mfd.rate_end_date forecast_end_date, mfd.original_forecast_quantity original_quantity, mfd.current_forecast_quantity quantity, mfd.comments, xxen_util.meaning(mfd.bucket_type,'MRP_BUCKET_TYPE',700) bucket_type, to_char(null) workday_control, mfd.confidence_percentage, nvl( (select ppa.segment1 project_number from pa_projects_all ppa where ppa.project_id = mfd.project_id), (select psm.project_number from pjm_seiban_numbers psm where psm.project_id = mfd.project_id) ) project, (select pt.task_number from pa_tasks pt where pt.task_id = mfd.task_id) task, (select wl.line_code from wip_lines wl where wl.line_id = mfd.line_id and wl.organization_id = mfd.organization_id) wip_line_code, mfd.attribute_category, mfd.attribute1, mfd.attribute2, mfd.attribute3, mfd.attribute4, mfd.attribute5, mfd.attribute6, mfd.attribute7, mfd.attribute8, mfd.attribute9, mfd.attribute10, mfd.attribute11, mfd.attribute12, mfd.attribute13, mfd.attribute14, mfd.attribute15, mfd.transaction_id from mtl_parameters mp, mrp_forecast_designators mfds, mrp_forecast_items_v mfiv, mrp_forecast_dates mfd, mtl_system_items_vl msiv where 1=1 and mfds.forecast_set = nvl(:p_forecast_set,mfds.forecast_set) and mfds.organization_id = mp.organization_id and mfds.organization_id = mfiv.organization_id and mfds.forecast_designator = mfiv.forecast_designator and mfiv.organization_id = mfd.organization_id and mfiv.forecast_designator = mfd.forecast_designator and mfiv.inventory_item_id = mfd.inventory_item_id and mfiv.organization_id = msiv.organization_id and mfiv.inventory_item_id = msiv.inventory_item_id U (:Upload_Mode not like '%' || xxen_upload.action_update || '%' or :Forecast_Set is not null or :Forecast_Name is not null) A xxen_mrp_upload.upload_mrp_forecast_entry xxen_mrp_upload.do_forecast_import US MRP Item Forecast Upload MRP Item Forecast Upload ================================ - Create New Item Forecast Entries. - Update Existing Item Forecast Entries. - Delete Existing Item Forecast Entries (Delete by setting the Forecast Quantity = 0). Prerequisites/Restrictions ========================== - Forecast Designators must be defined. - Item Forecasts can only be uploaded to a Forecast, not a Forecast Set. Upload Modes ============ Create ------ Opens an empty spreadsheet where the user can enter new Forecast Entries against any accessible Forecast. Use the Organization Code, Forecast Set, Forecast Name parameters to restrict the Organizations, Forecast Sets, and Forecasts LOVs in the spreadsheet. Create and Update ----------------- Create new Forecast Entries and/or Modify Existing Forecast Entries. Allows the user to - download existing Forecast Entries for the Forecasts meeting the entered selection criteria. - modify (add/update/delete) the Forecast Entries within the downloaded Forecasts. - create new Forecast Entries in any accessible Forecast. This mode allows the creation of Forecast Entries against Forecasts not included in the download. This mode can be used for downloading Forecast Entries from an existing Forecast and copying them to a different Forecast. Update ------ Modify (Add/Update/Delete) Forecast Entries against the downloaded Forecasts only. Allows the user to - download existing Forecast Entries for the Forecasts meeting the entered selection criteria. - modify (add/update/delete) the Forecast Entries within the downloaded Forecasts only. This mode does not allow the creation of Forecast Entries against Forecasts not included in the download. Parameters that control the upload behaviour: ====================================== Delete Existing Forecast ----------------------- Select ‘DELETE all existing forecast entries and replace’ if this upload will replace all existing entries in the forecast. In this case all existing forecast entries are deleted before the new forecast entries are loaded. Select ‘KEEP existing forecast entries and update’ if this upload is creating new entries to add to the forecast and/or updating/deleting specific forecast entries. In this case the existing forecast entries are retained. The option to delete the existing forecast is only available when the upload is run in the Create upload mode. Default Bucket Type --------------------------- This is the default bucket type used for the forecast entries if not specified in the upload spreadsheet. It will default from the selected forecast name, selected forecast set or will default to Days if a Forecast Set or Forecast Name is not specified. This can be overridden in the Upload spreadsheet for individual forecast entries. Default Workday Control --------------------------------- This parameter determines the default behaviour for handling a non-workday forecast date or forecast end date. The options are Reject the forecast entry, shift the date backwards to the previous working bucket date, or shift the date forward to the next working bucket date. This can be overridden in the Upload spreadsheet for individual forecast entries. A Forecast Set and/or Forecast Name must be entered in Update Mode Enginatics Upload 1=1 :p_forecast_set :p_replace_forecast :p_upload_mode 1 -30 :p_source_code Char Blitz US Upload Source Code 2 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV custom Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, 'Create new Forecast Entries in any accessible Forecast' description from dual union all select xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_update) value, 'Download and add/update/delete Forecast Entries in the selected Forecasts only' description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, 'Download Forecast Entries from selected Forecasts. Add/update/delete the existing Forecast Entries and/or Create new Forecast Entries in any accessible Forecast' description from dual order by 1 C Y US Upload Mode 3 15 :p_replace_forecast LOV custom Y select x.id, x.value, x.description from (select 'Y' id, 'DELETE all existing forecast entries and replace' value, 'The existing forecast is deleted and replaced by this upload' description from dual union select 'N' id, 'KEEP existing forecast entries and update' value, 'The existing forecast is retained and updated by this upload' description from dual ) x where :$flex$.upload_mode = xxen_upload.action_create order by x.value N US Delete Existing Forecast DELETE - this upload will delete all existing entries in the forecast and replace the forecast with this upload. KEEP - this upload will keep the existing forecast entries and update the forecast with this upload. 4 20 1=1 :p_bucket_type=:p_bucket_type LOV MRP Bucket Type EE0BAB77101DB1A6E0530100007F6311 Y select flvv.meaning value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'MRP_BUCKET_TYPE' and flvv.view_application_id = 700 and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate) and nvl(flvv.end_date_active,sysdate) order by flvv.meaning select coalesce( (select xxen_util.meaning(mfds.bucket_type,'MRP_BUCKET_TYPE',700) from mrp_forecast_designators mfds where mfds.forecast_designator = :$flex$.forecast_name and (:$flex$.organization_code is null or mfds.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y')) and rownum <= 1 ), (select xxen_util.meaning(mfds.bucket_type,'MRP_BUCKET_TYPE',700) from mrp_forecast_designators mfds where mfds.forecast_designator = :$flex$.forecast_set and (:$flex$.organization_code is null or mfds.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y')) and rownum <= 1 ), 'Days' ) from dual Y US Default Bucket Type This is the default bucket type used for the forecast entries. It will default from the selected forecast name, selected forecast set or will default to Days if a Forecast Set or Forecast Name is not specified. 5 30 1=1 :p_workday_control=:p_workday_control LOV MRP Workday Control F45CD86816892A61E0530100007FC72D Y select flvv.meaning value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'MRP_WORKDAY_CONTROL_TYPE' and flvv.view_application_id = 700 and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate) and nvl(flvv.end_date_active,sysdate) order by flvv.meaning xxen_util.meaning(1,'MRP_WORKDAY_CONTROL_TYPE',700) Y US Default Workday Control The default behaviour for handling a non-workday forecast date or forecast end date. The options are Reject the forecast entry, shift the date backwards to the previous working bucket date, or shift the date forward to the next working bucket date. 6 50 1=1 mp.organization_code=:p_organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') F Code de l'organisation US Organization Code 7 60 :p_forecast_set LOV custom Y select distinct mfds.forecast_designator value, mfds.description from mrp_forecast_designators mfds where mfds.forecast_set is null and trunc(sysdate) <= nvl(mfds.disable_date,sysdate) and (:$flex$.organization_code is null or mfds.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y')) and mfds.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by value US Forecast Set 8 70 1=1 mfds.forecast_designator=:p_forecast_designator LOV custom Y select distinct mfds.forecast_designator value, mfds.description from mrp_forecast_designators mfds where mfds.forecast_set is not null and trunc(sysdate) <= nvl(mfds.disable_date,sysdate) and (:$flex$.forecast_set is null or mfds.forecast_set = :$flex$.forecast_set) and (:$flex$.organization_code is null or mfds.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y')) and mfds.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by value F Prévision US Forecast Name 9 80 1=1 msiv.planner_code=:p_planner_code LOV INV Planner Code (all) A5D770D0ECB301D5E0530100007F19A3 Y select mp.planner_code value, mp.description from mtl_planners mp where nvl(mp.disable_date,sysdate+1)>=sysdate and (:$flex$.organization_code is null or mp.organization_id in (select mp0.organization_id from mtl_parameters mp0 where xxen_util.contains(:$flex$.organization_code,mp0.organization_code)='Y')) order by mp.planner_code F Planificateur US Planner 10 90 1=1 msiv.concatenated_segments=:p_item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments F Article US Item 11 100 1=1 mfd.project_id in ( select ppa.project_id from ( select ppa.segment1 project, ppa.project_id, ppa.org_id from pa_projects_all ppa union all select psm.project_number project, psm.project_id, psm.operating_unit org_id from pjm_seiban_numbers psm ) ppa where ppa.project = :p_project ) LOV custom Y Y select ppa.project_number value, ppa.project_type||': '||ppa.name description from ( select ppa.segment1 project_number, ppa.name, ppa.project_type, ppa.org_id, ppa.creation_date, ppa.project_id from pa_projects_all ppa union all select psm.project_number, psm.project_name, 'PJM Seiban' project_type, psm.operating_unit, psm.creation_date, psm.project_id from pjm_seiban_numbers psm ) ppa where ppa.project_id in ( select mfd.project_id from mrp_forecast_designators mfds, mrp_forecast_dates mfd where mfds.organization_id = mfd.organization_id and mfds.forecast_designator = mfd.forecast_designator and mfds.forecast_set = :$flex$.forecast_set and (:$flex$.forecast_name is null or xxen_util.contains(:$flex$.forecast_name,mfds.forecast_designator) = 'Y') and (:$flex$.organization_code is null or mfds.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y')) and mfds.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mfd.project_id is not null ) order by ppa.creation_date desc, ppa.project_number desc F Projet US Project 12 110 1=1 mfd.forecast_date>=:forecast_date_from Date F Date prévisionnelle du US Forecast Date From 13 120 1=1 mfd.forecast_date<:p_forecast_date_to+1 Date F Date prévisionnelle jusqu'au US Forecast Date To FF955D3E007F33DE90A5299D4402584A Create Opens an empty spreadsheet where the user can enter new Forecast Entries against any accessible Forecast. Use the Organization Code, Forecast Set, Forecast Name parameters to restict the Organizations, Forecast Sets, and Forecasts LOVs in the spreadsheet SYSADMIN 12 BUCKET_TYPE 18 COMMENTS 3 FORECAST 9 FORECAST_DATE 4 FORECAST_DESCRIPTION 10 FORECAST_END_DATE 2 FORECAST_SET 5 ITEM 6 ITEM_DESCRIPTION 1 ORGANIZATION_CODE 8 PLANNER 15 PROJECT 11 QUANTITY 16 TASK 7 UOM 17 WIP_LINE_CODE 13 WORKDAY_CONTROL Organization Code Upload Mode C S Site Forecast Date From Forecast Date To Item Planner Project 4AE05B191EC5B5F139809B8F7503445F Create and Update Create new Forecast Entries and/or Modify Existing Forecast Entries Allows the user to - download existing Forecaset Entries for the Forecasts meeting the entered selection criteria - modfy (add/update/delete) the Forecast Entries within the downloaded Forecasts. - create new Forecast Entries in any accessible Forecast This template allows the creation of Forecast Entries against Forecasts not included in the download This template can be used for downloading Forecast Entries from an existing Forecast and copying them to a different Forecast. SYSADMIN 13 BUCKET_TYPE 18 COMMENTS 3 FORECAST 9 FORECAST_DATE 4 FORECAST_DESCRIPTION 10 FORECAST_END_DATE 2 FORECAST_SET 5 ITEM 6 ITEM_DESCRIPTION 1 ORGANIZATION_CODE 11 ORIGINAL_QUANTITY Y 8 PLANNER 15 PROJECT 12 QUANTITY 16 TASK 7 UOM 17 WIP_LINE_CODE 14 WORKDAY_CONTROL Upload Mode C,U S Site 47DB1BD07847B34F333956FF483F8A38 Default SYSADMIN 13 BUCKET_TYPE 18 COMMENTS 3 FORECAST 9 FORECAST_DATE 4 FORECAST_DESCRIPTION 10 FORECAST_END_DATE 2 FORECAST_SET 5 ITEM 6 ITEM_DESCRIPTION 1 ORGANIZATION_CODE 11 ORIGINAL_QUANTITY Y 8 PLANNER 15 PROJECT 12 QUANTITY 16 TASK 7 UOM 17 WIP_LINE_CODE 14 WORKDAY_CONTROL S Site EEDCDE6FD1D5BC47DE9FB276D6DA4750 Update Modify (Add/Update/Delete) Forecast Entries against the downloaded Forecasts only. Allows the user to - download existing Forecaset Entries for the Forecasts meeting the entered selection criteria - modfy (add/update/delete) the Forecast Entries within the downloaded Forecasts only. This template does not allow the creation of Forecast Entries against Forecasts not included in the download SYSADMIN 13 BUCKET_TYPE 18 COMMENTS 3 FORECAST 9 FORECAST_DATE 4 FORECAST_DESCRIPTION 10 FORECAST_END_DATE 2 FORECAST_SET 5 ITEM 6 ITEM_DESCRIPTION 1 ORGANIZATION_CODE 11 ORIGINAL_QUANTITY Y 8 PLANNER 15 PROJECT 12 QUANTITY 16 TASK 7 UOM 17 WIP_LINE_CODE 14 WORKDAY_CONTROL Upload Mode U S Site SYSADMIN EEDCDE6FD1D5BC47DE9FB276D6DA4750 ENGINATICS 4AE05B191EC5B5F139809B8F7503445F 47DB1BD07847B34F333956FF483F8A38 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 ROW_ID Char varchar2 Y Y 7 REQUEST_ID Char varchar2 Y Y 8 UPLOAD_MODE Char varchar2 2000 :$flex$.upload_mode Y Y 9 SOURCE_CODE Char varchar2 :$flex$.upload_source_code Y Y 10 REPLACE_FORECAST Char varchar2 2000 :$flex$.delete_existing_forecast Y Y 11 ORGANIZATION_CODE LOV custom varchar2 3 Y select ood.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where ood.organization_id = mp.organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.upload_mode not in (xxen_upload.action_meaning(xxen_upload.action_create),xxen_upload.action_meaning(xxen_upload.action_update)) or mp.organization_id in (select mp2.organization_id from mtl_parameters mp2, mrp_forecast_designators mfds2 where mp2.organization_id = mfds2.organization_id and mfds2.forecast_set = nvl(:$flex$.forecast_set,mfds2.forecast_set) and (:$flex$.forecast_name is null or xxen_util.contains(:$flex$.forecast_name,mfds2.forecast_designator) = 'Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp2.organization_code) = 'Y') ) ) order by value Y Y 12 FORECAST_SET LOV custom varchar2 10 Y select mfds.forecast_designator value, mfds.description description from mtl_parameters mp, mrp_forecast_designators mfds where mp.organization_code = :$column$.organization_code and mfds.organization_id = mp.organization_id and trunc(sysdate) < nvl(mfds.disable_date,sysdate) and mfds.forecast_set is null and (:$flex$.upload_mode not in (xxen_upload.action_meaning(xxen_upload.action_create),xxen_upload.action_meaning(xxen_upload.action_update)) or (mfds.organization_id,mfds.forecast_designator) in (select mfds2.organization_id,mfds2.forecast_set from mrp_forecast_designators mfds2 where mfds2.forecast_set = nvl(:$flex$.forecast_set,mfds2.forecast_set) and (:$flex$.forecast_name is null or xxen_util.contains(:$flex$.forecast_name,mfds2.forecast_designator) = 'Y') ) ) and (:$column$.forecast is null or mfds.forecast_designator = (select mfds2.forecast_set from mrp_forecast_designators mfds2 where mfds2.organization_id = mp.organization_id and mfds2.forecast_designator = :$column$.forecast) ) order by value Y 13 FORECAST LOV custom varchar2 10 Y select mfds.forecast_designator value, mfds.description description from mtl_parameters mp, mrp_forecast_designators mfds where mp.organization_code = :$column$.organization_code and mfds.organization_id = mp.organization_id and trunc(sysdate) < nvl(mfds.disable_date,sysdate) and mfds.forecast_set is not null and (:$flex$.upload_mode not in (xxen_upload.action_meaning(xxen_upload.action_create),xxen_upload.action_meaning(xxen_upload.action_update)) or (mfds.organization_id,mfds.forecast_designator) in (select mfds2.organization_id,mfds2.forecast_designator from mrp_forecast_designators mfds2 where mfds2.forecast_set = nvl(:$flex$.forecast_set,mfds2.forecast_set) and (:$flex$.forecast_name is null or xxen_util.contains(:$flex$.forecast_name,mfds2.forecast_designator) = 'Y') ) ) and mfds.forecast_set = nvl(:$column$.forecast_set,mfds.forecast_set) order by value Y Y 14 FORECAST_DESCRIPTION LOV custom varchar2 50 Y select mfds.description value, null description from mtl_parameters mp, mrp_forecast_designators mfds where mp.organization_code = :$column$.organization_code and mfds.organization_id = mp.organization_id and mfds.forecast_designator = :$column$.forecast select mfds.description from mtl_parameters mp, mrp_forecast_designators mfds where mp.organization_code = :$column$.organization_code and mfds.organization_id = mp.organization_id and mfds.forecast_designator = :$column$.forecast Y 15 ITEM LOV custom varchar2 40 Y Y select msiv.concatenated_segments value, msiv.description description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.effectivity_control != 2 and not (msiv.mrp_planning_code = 6 and msiv.bom_item_type = 4) -- not planned standard item order by value Y Y 16 ITEM_DESCRIPTION LOV custom varchar2 240 Y select msiv.description value, null description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item select msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item Y 17 UOM LOV custom varchar2 3 Y select msiv.primary_uom_code value, null description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item select msiv.primary_uom_code from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item Y 18 PLANNER LOV custom varchar2 10 Y select msiv.planner_code value, null description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item select msiv.planner_code from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code = :$column$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item Y 19 FORECAST_DATE Date date Y 20 FORECAST_END_DATE Date date 21 ORIGINAL_QUANTITY Number number 22 QUANTITY Number number Y 23 COMMENTS Char varchar2 240 24 BUCKET_TYPE LOV custom varchar2 Y select flvv.meaning value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'MRP_BUCKET_TYPE' and flvv.view_application_id = 700 and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate-1) and nvl(flvv.end_date_active,sysdate+1) order by flvv.meaning :$flex$.default_bucket_type 25 WORKDAY_CONTROL LOV custom varchar2 Y select flvv.meaning value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'MRP_WORKDAY_CONTROL_TYPE' and flvv.view_application_id = 700 and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate-1) and nvl(flvv.end_date_active,sysdate+1) order by flvv.meaning :$flex$.default_workday_control 26 CONFIDENCE_PERCENTAGE Number number 100 Y 27 PROJECT LOV custom varchar2 25 Y select ppa.project_number value, ppa.project_type||': '||ppa.name description from org_organization_definitions ood, ( select ppa.segment1 project_number, ppa.name, ppa.project_type, ppa.org_id, ppa.creation_date, ppa.project_id from pa_projects_all ppa union all select psm.project_number, psm.project_name, 'PJM Seiban' project_type, psm.operating_unit, psm.creation_date, psm.project_id from pjm_seiban_numbers psm ) ppa where ood.organization_code = :$column$.organization_code and ppa.org_id = ood.operating_unit order by ppa.creation_date desc, ppa.project_number desc 28 TASK LOV custom varchar2 25 Y select pt.task_number value, pt.description description from org_organization_definitions ood, ( select ppa.segment1 project_number, ppa.name, ppa.project_type, ppa.org_id, ppa.creation_date, ppa.project_id from pa_projects_all ppa union all select psm.project_number, psm.project_name, 'PJM Seiban' project_type, psm.operating_unit, psm.creation_date, psm.project_id from pjm_seiban_numbers psm ) ppa, pa_tasks pt where ood.organization_code = :$column$.organization_code and ppa.org_id = ood.operating_unit and ppa.project_number = :$column$.project and pt.project_id = ppa.project_id order by pt.task_number 29 WIP_LINE_CODE Char varchar2 10 30 ATTRIBUTE_CATEGORY Char varchar2 30 31 ATTRIBUTE1 Char varchar2 150 32 ATTRIBUTE2 Char varchar2 150 33 ATTRIBUTE3 Char varchar2 150 34 ATTRIBUTE4 Char varchar2 150 35 ATTRIBUTE5 Char varchar2 150 36 ATTRIBUTE6 Char varchar2 150 37 ATTRIBUTE7 Char varchar2 150 38 ATTRIBUTE8 Char varchar2 150 39 ATTRIBUTE9 Char varchar2 150 40 ATTRIBUTE10 Char varchar2 150 41 ATTRIBUTE11 Char varchar2 150 42 ATTRIBUTE12 Char varchar2 150 43 ATTRIBUTE13 Char varchar2 150 44 ATTRIBUTE14 Char varchar2 150 45 ATTRIBUTE15 Char varchar2 150 46 TRANSACTION_ID Number number Y Y U 1 action_ in Char ACTION_CODE_ U 3 p_transaction_id in Number TRANSACTION_ID U 4 p_organization_code in Char ORGANIZATION_CODE U 6 p_forecast_designator in Char FORECAST U 8 p_item in Char ITEM U 12 p_forecast_date in Date FORECAST_DATE U 13 p_forecast_end_date in Date FORECAST_END_DATE U 14 p_quantity in Number QUANTITY U 15 p_comments in Char COMMENTS U 16 p_bucket_type in Char BUCKET_TYPE U 17 p_workday_control in Char WORKDAY_CONTROL U 18 p_confidence_percentage in Number CONFIDENCE_PERCENTAGE U 19 p_source_code in Char SOURCE_CODE U 20 p_project in Char PROJECT U 21 p_task in Char TASK U 22 p_wip_line_code in Char WIP_LINE_CODE U 23 p_attribute_category in Char ATTRIBUTE_CATEGORY U 24 p_attribute1 in Char ATTRIBUTE1 U 25 p_attribute2 in Char ATTRIBUTE2 U 26 p_attribute3 in Char ATTRIBUTE3 U 27 p_attribute4 in Char ATTRIBUTE4 U 28 p_attribute5 in Char ATTRIBUTE5 U 29 p_attribute6 in Char ATTRIBUTE6 U 30 p_attribute7 in Char ATTRIBUTE7 U 31 p_attribute8 in Char ATTRIBUTE8 U 32 p_attribute9 in Char ATTRIBUTE9 U 33 p_attribute10 in Char ATTRIBUTE10 U 34 p_attribute11 in Char ATTRIBUTE11 U 35 p_attribute12 in Char ATTRIBUTE12 U 36 p_attribute13 in Char ATTRIBUTE13 U 37 p_attribute14 in Char ATTRIBUTE14 U 38 p_attribute15 in Char ATTRIBUTE15 U 39 p_row_id in/out Char ROW_ID U 40 request_id_ out Number REQUEST_ID_ U 41 status_ out Char STATUS_CODE_ U 42 message_ out Char MESSAGE_ P 1 p_upload_mode in Char UPLOAD_MODE P 2 p_replace_forecast in Char REPLACE_FORECAST P 3 p_request_id in Number REQUEST_ID P 4 request_id_ out Number REQUEST_ID_ P 5 status_ out Char STATUS_CODE_ P 6 message_ out Char MESSAGE_ Order By forecast, item, forecast_date asc Success SQL select xu.action_, xxen_upload.status_meaning(xu.pstatus) status_, case when xu.pstatus = xxen_upload.status_success and mfd.transaction_id is null then 'Forecast Entry Deleted' else xu.pmsg end message_, xu.request_id_, xu.row_id, xu.request_id, xu.modified_columns_, xu.upload_mode, xu.source_code, xu.replace_forecast, -- decode(xu.pstatus,xxen_upload.status_error,xu.organization_code,nvl(mp.organization_code,xu.organization_code)) organization_code, decode(xu.pstatus,xxen_upload.status_error,xu.forecast_set,nvl(mfds.forecast_set,xu.forecast_set)) forecast_set, decode(xu.pstatus,xxen_upload.status_error,xu.forecast,nvl(mfds.forecast_designator,xu.forecast)) forecast, decode(xu.pstatus,xxen_upload.status_error,xu.forecast_description,nvl(mfds.description,xu.forecast_description)) forecast_description, decode(xu.pstatus,xxen_upload.status_error,xu.item,nvl(msiv.concatenated_segments,xu.item)) item, decode(xu.pstatus,xxen_upload.status_error,xu.item_description,nvl(msiv.description,xu.item_description)) item_description, decode(xu.pstatus,xxen_upload.status_error,xu.uom,nvl(msiv.primary_uom_code,xu.uom)) uom, decode(xu.pstatus,xxen_upload.status_error,xu.planner,nvl(msiv.planner_code,xu.planner)) planner, decode(xu.pstatus,xxen_upload.status_error,xu.forecast_date,nvl(mfd.forecast_date,xu.forecast_date)) forecast_date, decode(xu.pstatus,xxen_upload.status_error,xu.forecast_end_date,nvl(mfd.rate_end_date,xu.forecast_end_date)) forecast_end_date, decode(xu.pstatus,xxen_upload.status_error,to_number(xu.original_quantity),nvl(mfd.original_forecast_quantity,to_number(xu.original_quantity))) original_quantity, decode(xu.pstatus,xxen_upload.status_error,to_number(xu.quantity),nvl(mfd.current_forecast_quantity,to_number(xu.quantity))) quantity, decode(xu.pstatus,xxen_upload.status_error,xu.comments,nvl(mfd.comments,xu.comments)) comments, decode(xu.pstatus,xxen_upload.status_error,xu.bucket_type,nvl(xxen_util.meaning(mfd.bucket_type,'MRP_BUCKET_TYPE',700),xu.bucket_type)) bucket_type, xu.workday_control, decode(xu.pstatus,xxen_upload.status_error,to_number(xu.confidence_percentage),nvl(mfd.confidence_percentage,to_number(xu.confidence_percentage))) confidence_percentage, decode(xu.pstatus,xxen_upload.status_error,xu.project, coalesce( (select ppa.segment1 project_number from pa_projects_all ppa where ppa.project_id = mfd.project_id), (select psm.project_number from pjm_seiban_numbers psm where psm.project_id = mfd.project_id), xu.project ) ) project, decode(xu.pstatus,xxen_upload.status_error,xu.task,nvl((select pt.task_number from pa_tasks pt where pt.task_id = mfd.task_id),xu.task)) task, decode(xu.pstatus,xxen_upload.status_error,xu.wip_line_code,nvl((select wl.line_code from wip_lines wl where wl.line_id = mfd.line_id and wl.organization_id = mfd.organization_id),xu.wip_line_code)) wip_line_code, decode(xu.pstatus,xxen_upload.status_error,xu.attribute_category,nvl(mfd.attribute_category,xu.attribute_category)) attribute_category, decode(xu.pstatus,xxen_upload.status_error,xu.attribute1 ,nvl(mfd.attribute1 ,xu.attribute1 )) attribute1, decode(xu.pstatus,xxen_upload.status_error,xu.attribute2 ,nvl(mfd.attribute2 ,xu.attribute2 )) attribute2, decode(xu.pstatus,xxen_upload.status_error,xu.attribute3 ,nvl(mfd.attribute3 ,xu.attribute3 )) attribute3, decode(xu.pstatus,xxen_upload.status_error,xu.attribute4 ,nvl(mfd.attribute4 ,xu.attribute4 )) attribute4, decode(xu.pstatus,xxen_upload.status_error,xu.attribute5 ,nvl(mfd.attribute5 ,xu.attribute5 )) attribute5, decode(xu.pstatus,xxen_upload.status_error,xu.attribute6 ,nvl(mfd.attribute6 ,xu.attribute6 )) attribute6, decode(xu.pstatus,xxen_upload.status_error,xu.attribute7 ,nvl(mfd.attribute7 ,xu.attribute7 )) attribute7, decode(xu.pstatus,xxen_upload.status_error,xu.attribute8 ,nvl(mfd.attribute8 ,xu.attribute8 )) attribute8, decode(xu.pstatus,xxen_upload.status_error,xu.attribute9 ,nvl(mfd.attribute9 ,xu.attribute9 )) attribute9, decode(xu.pstatus,xxen_upload.status_error,xu.attribute10,nvl(mfd.attribute10,xu.attribute10)) attribute10, decode(xu.pstatus,xxen_upload.status_error,xu.attribute11,nvl(mfd.attribute11,xu.attribute11)) attribute11, decode(xu.pstatus,xxen_upload.status_error,xu.attribute12,nvl(mfd.attribute12,xu.attribute12)) attribute12, decode(xu.pstatus,xxen_upload.status_error,xu.attribute13,nvl(mfd.attribute13,xu.attribute13)) attribute13, decode(xu.pstatus,xxen_upload.status_error,xu.attribute14,nvl(mfd.attribute14,xu.attribute14)) attribute14, decode(xu.pstatus,xxen_upload.status_error,xu.attribute15,nvl(mfd.attribute15,xu.attribute15)) attribute15, decode(xu.pstatus,xxen_upload.status_error,to_number(xu.transaction_id),nvl(mfd.transaction_id,to_number(xu.transaction_id))) transaction_id from mtl_parameters mp, mrp_forecast_designators mfds, mrp_forecast_items_v mfiv, mrp_forecast_dates mfd, mtl_system_items_vl msiv, (select decode(xu.status_code_,xxen_upload.status_error,xu.status_code_,xxen_upload.status_valid,xxen_upload.status_error,xxen_mrp_upload.get_status_(xu.request_id_,xu.row_id)) pstatus, decode(xu.status_code_,xxen_upload.status_error,xu.message_,xxen_upload.status_valid,'Post Processing Failed',xxen_mrp_upload.get_message_(xu.request_id_,xu.row_id)) pmsg, xu.* from xxen_mrp_item_forecast_1745_u xu ) xu where mfd.request_id (+) = to_number(xu.request_id_) and mfd.source_line_id (+) = to_number(xu.row_id) and mfiv.organization_id (+) = mfd.organization_id and mfiv.forecast_designator (+) = mfd.forecast_designator and mfiv.inventory_item_id (+) = mfd.inventory_item_id and mfds.organization_id (+) = mfiv.organization_id and mfds.forecast_designator (+) = mfiv.forecast_designator and mp.organization_id (+) = mfds.organization_id and msiv.organization_id (+) = mfiv.organization_id and msiv.inventory_item_id (+) = mfiv.inventory_item_id