ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A8576D437D9C52DEE0530100007F5406 MSC Buyer Name Y select distinct msi.buyer_name value, null description from msc_apps_instances@A2M_DBLINK mai, msc_plans@A2M_DBLINK mp, msc_plan_organizations@A2M_DBLINK mpo, msc_system_items@A2M_DBLINK msi where mai.instance_code = :$flex$.planning_instance and mp.sr_instance_id = mai.instance_id and mp.compile_designator = :$flex$.plan and mpo.sr_instance_id = mp.sr_instance_id and mpo.plan_id = mp.plan_id and (:$flex$.organization is null or xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) and msi.sr_instance_id = mpo.sr_instance_id and msi.plan_id = mpo.plan_id and msi.organization_id = mpo.organization_id and msi.buyer_name is not null order by lower(msi.buyer_name) AR إرجاع قائمة بأسماء المشتري ASCP D Gibt die Liste der ASCP-Käufernamen zurück E Devuelve la lista de nombres de compradores de ASCP F Renvoie la liste des noms d'acheteurs de l'ASCP I Restituisce l'elenco dei nomi degli acquirenti ASCP JA ASCPバイヤー名のリストを返します KO ASCP 구매자 이름 목록을 반환합니다. PTB Retorna a lista de Nomes de Compradores ASCP RU Возвращает список ASCP имен покупателей S Returnerar listan över ASCP-köparnamn TR ASCP Alıcı Adlarının listesini verir US Returns the list of ASCP Buyer Names ZHS 返回ASCP买方名称列表 A85770BE6AD052E2E0530100007F2871 MSC Category Set Y select mcs.category_set_name value, null description from msc_apps_instances@A2M_DBLINK mai, msc_category_sets@A2M_DBLINK mcs where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mcs.sr_instance_id and exists (select null from msc_item_categories@A2M_DBLINK mic where mic.sr_instance_id = mcs.sr_instance_id and mic.category_set_id = mcs.category_set_id ) order by lower(mcs.category_set_name) AR تُرجع قائمة مجموعات فئات ASCP D Liefert die Liste der ASCP-Kategoriesätze E Devuelve la lista de conjuntos de categorías ASCP F Retourne la liste des ensembles de catégories ASCP I Restituisce l'elenco dei set di categorie ASCP JA ASCPカテゴリセットのリストを返します KO ASCP 범주 세트 목록을 반환합니다. PTB Devolve a lista dos conjuntos de categorias ASCP RU Возвращает список наборов категорий ASCP S Returnerar listan över ASCP-kategorisatser TR ASCP Kategori Kümelerinin listesini verir US Returns the list of ASCP Category Sets ZHS 返回ASCP类别集的列表 A85776F7DF3D52E0E0530100007F6064 MSC Category within a specified Category Set Y Y select distinct mic.category_name value, null description from msc_apps_instances@A2M_DBLINK mai, msc_category_sets@A2M_DBLINK mcs, msc_item_categories@A2M_DBLINK mic where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mcs.sr_instance_id and mcs.category_set_name = :$flex$.category_set and mcs.sr_instance_id = mcs.sr_instance_id and mcs.category_set_id = mcs.category_set_id order by lower(mic.category_name) AR تُرجع قائمة فئات ASCP ضمن مجموعة فئات محددة D Liefert die Liste der ASCP-Kategorien innerhalb eines angegebenen Kategorie-Sets E Devuelve la lista de categorías ASCP dentro de un conjunto de categorías especificado F Renvoie la liste des catégories ASCP dans un ensemble de catégories spécifié I Restituisce l'elenco delle categorie ASCP all'interno di un set di categorie specificato JA 指定されたカテゴリセット内のASCPカテゴリのリストを返します。 KO 지정된 범주 집합 내의 ASCP 범주 목록을 반환합니다. PTB Retorna a lista de categorias ASCP dentro de um conjunto de categorias especificado RU Возвращает список категорий ASCP в пределах указанного набора категорий. S Returnerar listan över ASCP-kategorier inom en angiven kategorisats TR Belirli bir Kategori Kümesi içindeki ASCP Kategorilerinin listesini verir US Returns the list of ASCP Categories within a specified Category Set ZHS 返回指定类别集中的ASCP类别列表。 A8A20CEB0602162EE0530100007FDE80 MSC Make or Buy Y select flv.meaning value, null description from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MTL_PLANNING_MAKE_BUY' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code in (1,2) order by value AR اصنع أو اشترِ LOV من Lookup MTL_PLANNING_MAKE_BUY D Make oder Buy LOV aus Lookup MTL_PLANNING_MAKE_BUY E Hacer o comprar LOV desde la búsqueda MTL_PLANNING_MAKE_BUY F Faire ou acheter LOV à partir de Lookup MTL_PLANNING_MAKE_BUY I Fare o comprare LOV dalla ricerca MTL_PLANNING_MAKE_BUY JA ルックアップMTL_PLANNING_MAKE_BUYからLOVを作るか買うか KO MTL_PLANNING_MAKE_BUY 룩업에서 LOV 생성 또는 구매 PTB Faça ou Compre AMOR na Lookup MTL_PLANNING_MAKE_BUY RU Make or Buy LOV из Lookup MTL_PLANNING_MAKE_BUY S Skapa eller köp LOV från Lookup MTL_PLANNING_MAKE_BUY TR Lookup MTL_PLANNING_MAKE_BUY ile Değer Oluşturun veya Satın Alın US Make or Buy LOV from Lookup MTL_PLANNING_MAKE_BUY ZHS 从查找MTL_PLANNING_MAKE_BUY中制作或购买LOV。 A8452450392C6F2FE0530100007F6DF4 MSC Plan Name Y select mp.compile_designator value ,null description from msc_plans@A2M_DBLINK mp, msc_apps_instances@A2M_DBLINK mai where mp.plan_id > 0 and mp.sr_instance_id = mai.instance_id and mai.instance_code =:$flex$.planning_instance order by lower(mp.compile_designator) AR إرجاع قائمة بأسماء خطة ASCP D Gibt die Liste der ASCP-Plan-Namen zurück E Devuelve la lista de nombres de planes ASCP F Retourne la liste des noms de plans ASCP I Restituisce l'elenco dei nomi dei piani ASCP JA ASCPプラン名のリストを返します KO ASCP 계획 명 목록을 반환합니다. PTB Retorna a lista de nomes dos planos ASCP RU Возвращает список названий планов ASCP S Returnerar listan över ASCP-plannamn TR ASCP Plan Adlarının listesini verir US Returns the list of ASCP Plan Names ZHS 返回ASCP计划名称的列表 EB8355FCD86A7EB8E0530100007F0B5B MSC Plan Order Upload Action Y select 'S' id, 'Select for Release' value, 'Select for Release' description from dual where nvl(:$flex$.upload_mode,'Select for Release') = 'Select for Release' union select 'R' id, 'Release the Order' value, 'Release the Order' description from dual where nvl(:$flex$.upload_mode,'Release the Order') = 'Release the Order' order by value US 347EE4CCAF055ECBE0630100007FAF97 MSC Plan Order Upload Actions Y select flv.meaning value, null description from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MRP_ACTIONS' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code in ('2','3','4') order by value US MSC Plan Order Upload - Actionable Actions 347DEAC4335143D2E0630100007FE943 MSC Plan Order Upload Order Types Y select flv.meaning value, null description from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MRP_ORDER_TYPE' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code in ('1','2','3','5','13') order by value US MSC Plan Order Upload - Actionable Order Types (Order Types that can Released, Rescheduled, Cancelled) 01F9429D6FE213DFE0630100007FA6FE MSC Plan Organization Code Y select mpo.organization_code value , null description from msc_plan_organizations@A2M_DBLINK mpo, msc_plans@A2M_DBLINK mp , msc_apps_instances@A2M_DBLINK mai where mp.sr_instance_id = mpo.sr_instance_id and mp.plan_id = mpo.plan_id and mpo.sr_instance_id = mai.instance_id and mp.compile_designator = nvl(:$flex$.plan,mp.compile_designator) and mai.instance_code =:$flex$.planning_instance and ( exists (select null from msc_selected_orgs_v@A2M_DBLINK msov where msov.responsibility_id = fnd_global.resp_id and msov.organization_id = mpo.organization_id) or exists (select null from org_access_view@A2M_DBLINK oav where oav.responsibility_id = fnd_global.resp_id and oav.resp_application_id = fnd_global.resp_appl_id and oav.organization_id = mpo.organization_id) ) order by lower(mpo.organization_code) US Returns the list of Organization Codes within the specified ASCP Plan A844FAB91C4F7309E0530100007F496B MSC Planned Item Y Y select msi.item_name value, min(msi.description) description from msc_system_items@A2M_DBLINK msi, msc_plans@A2M_DBLINK mp, msc_apps_instances@A2M_DBLINK mai where msi.sr_instance_id = mai.instance_id and msi.plan_id = mp.plan_id and mp.sr_instance_id = mai.instance_id and mp.compile_designator = nvl(:$flex$.plan,mp.compile_designator) and mai.instance_code =:$flex$.planning_instance group by msi.item_name order by lower(msi.item_name) AR تُرجع قائمة عناصر ASCP المخططة D Liefert die Liste der ASCP Planned Items E Devuelve la lista de elementos planificados de ASCP F Renvoie la liste des articles prévus par l'ASCP I Restituisce l'elenco degli elementi pianificati ASCP JA ASCP 予定項目のリストを返します KO ASCP 계획 품목 목록을 반환합니다. PTB Retorna a lista de itens planejados da ASCP RU Возвращает список запланированных элементов ППКП S Returnerar listan över ASCP-planerade objekt TR ASCP Planlanan Öğelerin listesini döndürür US Returns the list of ASCP Planned Items ZHS 返回ASCP计划项目的列表 A85765FE708251D8E0530100007FCD1D MSC Planner Code Y select distinct mpl.planner_code value, null description from msc_apps_instances@A2M_DBLINK mai, msc_plans@A2M_DBLINK mp, msc_plan_organizations@A2M_DBLINK mpo, msc_planners@A2M_DBLINK mpl where mai.instance_code = :$flex$.planning_instance and mp.sr_instance_id = mai.instance_id and mp.compile_designator = :$flex$.plan and mpo.sr_instance_id = mp.sr_instance_id and mpo.plan_id = mp.plan_id and (:$flex$.organization is null or xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) and mpl.sr_instance_id = mpo.sr_instance_id and mpl.organization_id = mpo.organization_id order by lower(mpl.planner_code) AR تُرجع قائمة رموز مخطط ASCP D Gibt die Liste der ASCP Planner Codes zurück E Devuelve la lista de los códigos de planificación ASCP F Renvoie la liste des codes de planificateurs de l'ASCP I Restituisce l'elenco dei codici ASCP Planner JA ASCPプランナーコードのリストを返します。 KO ASCP 계획자 코드 목록을 반환합니다. PTB Retorna a lista de Códigos de Planejamento ASCP RU Возвращает список кодов Планировщика ASCP S Returnerar listan över ASCP-planeringskoder TR ASCP Planlayıcı Kodlarının listesini döndürür US Returns the list of ASCP Planner Codes ZHS 返回ASCP规划师代码的列表。 A8454EC5D1EB6FE9E0530100007F0874 MSC Planning Instance Y select instance_code value ,null description from ( select instance_code from msc_apps_instances union select instance_code from mrp_ap_apps_instances_all ) order by instance_code AR تُرجع قائمة مثيلات تخطيط ASCP D Gibt die Liste der ASCP-Planungsinstanzen zurück E Devuelve la lista de instancias de planificación ASCP F Renvoie la liste des instances de planification de l'ASCP I Restituisce l'elenco delle istanze di pianificazione ASCP JA ASCPプランニングインスタンスのリストを返します KO ASCP 계획 인스턴스 목록을 반환합니다. PTB Retorna a lista de Instâncias de Planejamento ASCP RU Возвращает список случаев планирования ASCP S Returnerar listan över ASCP-planeringsinstanser TR ASCP Planlama Örneklerinin listesini verir US Returns the list of ASCP Planning Instances ZHS 返回ASCP规划实例的列表 347DEAC4335043D2E0630100007FE943 MSC Projects Y Y select distinct mp.project_number value, null description from msc_apps_instances@A2M_DBLINK mai, msc_projects@A2M_DBLINK mp where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mp.sr_instance_id and mp.plan_id = -1 and ( :$flex$.organization is null or mp.organization_id in (select mpo.organization_id from msc_apps_instances@A2M_DBLINK mai2, msc_plans@A2M_DBLINK mpl, msc_plan_organizations@A2M_DBLINK mpo where mai2.instance_code = :$flex$.planning_instance and mai2.instance_id = mpl.sr_instance_id and mpl.compile_designator = nvl(:$flex$.plan,mpl.compile_designator) and mpl.sr_instance_id = mpo.sr_instance_id and mpl.plan_id = mpo.plan_id and xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) ) order by mp.project_number US 347EE4CCAF065ECBE0630100007FAF97 MSC Tasks Y Y select distinct mpt.task_number value, mp.project_number description from msc_apps_instances@A2M_DBLINK mai, msc_projects@A2M_DBLINK mp, msc_project_tasks@A2M_DBLINK mpt where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mp.sr_instance_id and mp.plan_id = -1 and mp.project_number = :$flex$.project and mp.sr_instance_id = mpt.sr_instance_id and mp.plan_id = mpt.plan_id and mp.organization_id = mpt.organization_id and mp.project_id = mpt.project_id and ( :$flex$.organization is null or mp.organization_id in (select mpo.organization_id from msc_apps_instances@A2M_DBLINK mai2, msc_plans@A2M_DBLINK mpl, msc_plan_organizations@A2M_DBLINK mpo where mai2.instance_code = :$flex$.planning_instance and mai2.instance_id = mpl.sr_instance_id and mpl.compile_designator = nvl(:$flex$.plan,mpl.compile_designator) and mpl.sr_instance_id = mpo.sr_instance_id and mpl.plan_id = mpo.plan_id and xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) ) order by mpt.task_number US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US E300B0C64320782AE0530100007F7DAA with q_selected_items as (select /*+ materialize */distinct msi.sr_instance_id, msi.plan_id, msi.organization_id, msi.inventory_item_id from msc_system_items&a2m_dblink msi, msc_item_categories&a2m_dblink mic where msi.sr_instance_id = mic.sr_instance_id and msi.organization_id = mic.organization_id and msi.inventory_item_id = mic.inventory_item_id and mic.category_set_id = nvl(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where mcs.sr_instance_id = msi.sr_instance_id and mcs.default_flag = 1)) and 1=1 ), q_sub_components as (select distinct mbc.sr_instance_id, xxen_msc.get_plan_id&a2m_dblink(:p_instance_code,:p_plan_name) plan_id, mbc.organization_id, mbc.inventory_item_id from msc_boms&a2m_dblink mb, msc_bom_components&a2m_dblink mbc where mb.alternate_bom_designator is null and mb.sr_instance_id = mbc.sr_instance_id and mb.plan_id = mbc.plan_id and mb.organization_id = mbc.organization_id and mb.bill_sequence_id = mbc.bill_sequence_id connect by nocycle prior mbc.sr_instance_id = mb.sr_instance_id and prior mbc.plan_id = mb.plan_id and prior mbc.organization_id = mb.organization_id and prior mbc.inventory_item_id = mb.assembly_item_id start with (mb.sr_instance_id,mb.plan_id,mb.organization_id,mb.assembly_item_id) in (select msi.sr_instance_id, -1, msi.organization_id, msi.inventory_item_id from q_selected_items msi where nvl(:p_explode_boms,decode(nvl(:p_called_from_schub,'N'),'Y',decode(nvl(xxen_api.user_preference('BOM_EXPLOSION_DEFAULT','XXEN_SCHUB'),'expanded'),'expanded','Y',null),null)) = 'Y' ) ), q_all_items as (select msi.sr_instance_id, msi.plan_id, msi.organization_id, msi.inventory_item_id from q_selected_items msi union select msi.sr_instance_id, msi.plan_id, msi.organization_id, msi.inventory_item_id from q_sub_components msi ) -- -- Main Query select /*+ driving_site(mov) */ mov.*, -- custom attributes from lookup XXEN_MSC_CUSTOM_ATTRIBUTES &lp_custom_attributes -- item dff attributes &lp_item_dff_cols '.' last_col_flag from ( select case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_upload.action_meaning&a2m_dblink(xxen_upload.action_update&a2m_dblink) else null end action_, case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_upload.status_meaning&a2m_dblink(xxen_upload.status_new&a2m_dblink) else null end status_, case when :p_autopopulate_release_status is not null and :p_upload_mode is not null then xxen_util.description&a2m_dblink('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) else null end message_, null request_id_, null modified_columns_, nvl(:p_instance_code,mai.instance_code) instance, nvl(:p_plan_name,mp.compile_designator) plan, mpo.organization_code organization, decode(mov.project_id, null, null, msc_get_name.project&a2m_dblink(mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) project_number, decode(mov.task_id, null, null, msc_get_name.task&a2m_dblink(mov.task_id, mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) task_number, msi.planner_code planner, msi.buyer_name buyer_name, -- msc_get_name.lookup_meaning&a2m_dblink('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) make_buy, msi.item_name item, msi.description item_description, nvl(:p_category_set_name,(select mcs.category_set_name from msc_category_sets&a2m_dblink mcs where mcs.sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) category_set, mic.category_name category, (select distinct max(mss.safety_stock_quantity) keep (dense_rank last order by mss.period_start_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock from msc_safety_stocks&a2m_dblink mss where mss.sr_instance_id = mov.sr_instance_id and mss.plan_id = mov.plan_id and mss.organization_id = mov.organization_id and mss.inventory_item_id = mov.inventory_item_id and mss.period_start_date <= sysdate ) safety_stock, -- xxen_util.meaning&a2m_dblink('SUPPLY','MSC_QUESTION_TYPE',3) supply_demand, decode(mp.plan_type , 8,decode(mov.order_type , 1, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type) , 2, decode(mov.source_organization_id, null, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type), msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',53)) ,51, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type) , msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type)) ) ,msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type)) ) order_type, msc_get_name.supply_order_number&a2m_dblink(mov.order_type ,mov.order_number ,mov.plan_id ,mov.sr_instance_id ,mov.transaction_id ,mov.disposition_id) order_number, trunc(mov.need_by_date) need_by_date, trunc(mov.promised_date) promise_date, trunc(mov.old_schedule_date) old_due_date, trunc(mov.new_schedule_date) suggested_due_date, nvl(mov.daily_rate,mov.new_order_quantity) quantity, msc_get_name.action&a2m_dblink('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id) action, -- nvl(decode(mov.implement_as,null,null,msc_get_name.lookup_meaning&a2m_dblink('MRP_WORKBENCH_IMPLEMENT_AS',mov.implement_as)) ,xxen_util.meaning&a2m_dblink(xxen_msc_rel_plan_api.implement_as&a2m_dblink ( mov.sr_instance_id , mp.plan_type , 'MSC_SUPPLIES' , mov.order_type , mov.organization_id , msi.purchasing_enabled_flag , msi.planning_make_buy_code , msi.build_in_wip_flag , msi.repetitive_type , mov.source_sr_instance_id , mov.source_organization_id , mov.source_supplier_id , null -- dest_inst_id , null -- dest_org_id ),'MRP_WORKBENCH_IMPLEMENT_AS',700) ) implement_as, nvl(mov.implement_date, xxen_msc_rel_plan_api.implement_date&a2m_dblink ( mov.sr_instance_id , mov.plan_id , mp.plan_type , mov.organization_id , 'MSC_SUPPLIES' , mov.transaction_id , mov.order_type , -- implement_as nvl(mov.implement_as ,xxen_msc_rel_plan_api.implement_as&a2m_dblink ( mov.sr_instance_id , mp.plan_type , 'MSC_SUPPLIES' , mov.order_type , mov.organization_id , msi.purchasing_enabled_flag , msi.planning_make_buy_code , msi.build_in_wip_flag , msi.repetitive_type , mov.source_sr_instance_id , mov.source_organization_id , mov.source_supplier_id , null -- dest_inst_id , null -- dest_org_id )) , mov.new_schedule_date , mov.last_unit_completion_date , mov.firm_date , mov.new_ship_date , null -- dmd_satisfied_date )) implement_date, nvl(nvl(mov.implement_daily_rate,mov.implement_quantity), xxen_msc_rel_plan_api.implement_qty_rate&a2m_dblink ( mov.sr_instance_id , mov.plan_id , 'MSC_SUPPLIES' , mov.transaction_id , mov.order_type , mov.disposition_status_type , mov.original_quantity , nvl(mov.daily_rate,mov.new_order_quantity) , mov.firm_quantity , mov.quantity_in_process , mov.implemented_quantity )) implement_quantity, case when :p_autopopulate_release_status is not null then :p_upload_mode else null end update_release_status, -- case when mov.release_status = 1 then 'Selected' when mov.reschedule_flag = 1 and nvl(mov.implement_daily_rate,mov.implement_quantity) = 0 then 'Cancelled' when mov.reschedule_flag = 1 then 'Recheduled' when mov.implemented_quantity is not null then 'Released' else null end released_status, mov.implemented_quantity implemented_quantity, -- msc_get_name.get_order_comments&a2m_dblink(mov.plan_id, 'SUPPLY', mov.transaction_id) comments, decode(mov.firm_planned_type, 1, 'Y',null) firm, trunc(mov.firm_date) firm_date, mov.firm_quantity firm_quantity, -- msc_get_name.org_code&a2m_dblink(mov.source_organization_id,mov.source_sr_instance_id) source_organization, msc_get_name.supplier&a2m_dblink(mov.supplier_id) supplier, msc_get_name.supplier_site&a2m_dblink(mov.supplier_site_id) supplier_site, -- mov.reschedule_days reschedule_days, decode(mp.plan_type, 5, to_number(null), mov.schedule_priority) order_priority, -- 'SUPPLY' supply_demand_code, mov.transaction_id, mov.sr_instance_id, mov.plan_id, mov.organization_id, mov.inventory_item_id, msi.sr_inventory_item_id, mic.category_set_id category_set_id, nvl(mov.release_status,2) release_status from msc_supplies&a2m_dblink mov, msc_apps_instances&a2m_dblink mai, msc_plans&a2m_dblink mp, msc_plan_organizations&a2m_dblink mpo, msc_system_items&a2m_dblink msi, msc_item_categories&a2m_dblink mic where 2=2 and mov.sr_instance_id = mai.instance_id and mov.plan_id = mp.plan_id and mov.sr_instance_id = mpo.sr_instance_id and mov.plan_id = mpo.plan_id and mov.organization_id = mpo.organization_id and mov.sr_instance_id = msi.sr_instance_id and mov.plan_id = msi.plan_id and mov.organization_id = msi.organization_id and mov.inventory_item_id = msi.inventory_item_id and mov.sr_instance_id = mic.sr_instance_id and mov.inventory_item_id = mic.inventory_item_id and mov.organization_id = mic.organization_id and mic.category_set_id = nvl(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) and (mov.sr_instance_id,mov.plan_id,mov.organization_id,mov.inventory_item_id) in (select qai.sr_instance_id, qai.plan_id, qai.organization_id, qai.inventory_item_id from q_all_items qai ) and nvl(nvl(mov.daily_rate,mov.new_order_quantity),0) != 0 and nvl(mov.implemented_quantity,0) = 0 and ( xxen_util.lookup_code&a2m_dblink(msc_get_name.action&a2m_dblink('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id) ,'MRP_ACTIONS',700) in (2,3,4) -- Reschedule In, Reschedule Out, Release or mov.release_status = 1 ) and (nvl(:p_called_from_schub,'N') = 'N' or mov.order_type = 5) and nvl('&m2a_dblink','?') = nvl('&m2a_dblink','?') and nvl(:p_show_item_dff,'?') = nvl(:p_show_item_dff,'?') ) mov where 3=3 Y U XXEN_MSC A xxen_msc_upload.update_plan_order AR MSC Plan Order Upload MSC Plan Order Upload D MSC Plan Order Upload MSC Plan Order Upload F MSC Plan Order Upload MSC Plan Order Upload I MSC Plan Order Upload MSC Plan Order Upload US MSC Plan Order Upload Report: MSC Plan Orders Upload Description: Upload to action Plan Order recommendations. This upload can be used to either select for release or release Plan Order Recommendations from ASCP planning instances. Currently supported recommendations that can be actioned by this upload are: - Planned Order Releases - Reschedule In/Out recommendations Additionally, it allows the user to amend the recommended Implement Date and/or Quantity. In the generated Excel the user can amend the following columns: - Implement Date - Implement Quantity - Update Release Status Update Release Status can be either - Select for Release - Release the Order This is determined by the Report Parameter: Upload Mode For plan orders not yet selected for release, to amend the implement date and/or implement quantity, the Update Release Status column must also be specified as 'Select for Release' or 'Release the Order' against the Plan Order. If a plan order is already selected for release, then the user can amend the implement date and/or quantity in the generated excel without specifying a value in the Update Release Status column. The report parameter Upload mode determines the allowable action to be taken against the plan order recommendations in the upload ('Select for Release' or 'Release the Order') The report parameter Auto Populate Release Status if set to Yes, will automatically populate the 'Update Release Status' column and set the status to pending validation against all downloaded plan order recommendations in the generated excel. In this scenario, the generated excel can be saved and uploaded without the user needing to manually review and select the specific plan orders to be updated in the generated excel Enginatics R12 only Upload &a2m_dblink &lp_custom_attributes &lp_item_dff_cols &m2a_dblink 1=1 2=2 3=3 :p_autopopulate_release_status :p_called_from_schub :p_called_from_upload :p_category_set_name :p_explode_boms :p_instance_code :p_plan_name :p_show_item_dff :p_upload_mode 1 -100 1=1 msi.sr_instance_id = :p_instance_id Number select xxen_msc.get_instance_id(:$flex$.Planning_Instance) from dual US Instance ID 2 2=2 mov.sr_instance_id = :p_instance_id US Instance ID 3 -90 1=1 msi.plan_id = :p_plan_id Number select xxen_msc.get_plan_id(:$flex$.Planning_Instance,:$flex$.Plan) from dual US Plan Id 4 2=2 mov.plan_id = :p_plan_id US Plan Id 5 -80 :p_category_set_id Number select xxen_msc.get_category_set_id(:$flex$.Planning_Instance,:$flex$.Category_Set) from dual US Category Set ID 6 -20 2=2 mov.task_number = :p_task LOV MSC Tasks 347EE4CCAF065ECBE0630100007FAF97 Y Y select distinct mpt.task_number value, mp.project_number description from msc_apps_instances@A2M_DBLINK mai, msc_projects@A2M_DBLINK mp, msc_project_tasks@A2M_DBLINK mpt where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mp.sr_instance_id and mp.plan_id = -1 and mp.project_number = :$flex$.project and mp.sr_instance_id = mpt.sr_instance_id and mp.plan_id = mpt.plan_id and mp.organization_id = mpt.organization_id and mp.project_id = mpt.project_id and ( :$flex$.organization is null or mp.organization_id in (select mpo.organization_id from msc_apps_instances@A2M_DBLINK mai2, msc_plans@A2M_DBLINK mpl, msc_plan_organizations@A2M_DBLINK mpo where mai2.instance_code = :$flex$.planning_instance and mai2.instance_id = mpl.sr_instance_id and mpl.compile_designator = nvl(:$flex$.plan,mpl.compile_designator) and mpl.sr_instance_id = mpo.sr_instance_id and mpl.plan_id = mpo.plan_id and xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) ) order by mpt.task_number US Task 7 -10 1=1 (msi.sr_instance_id,msi.plan_id,msi.organization_id,msi.inventory_item_id) in ( select number1 instance_id, number2 plan_id, number3 organization_id, number4 inventory_item_id from msc_form_query&a2m_dblink mfq ) Char US instance_id|plan_id|organization_id|inventory_item_id 8 10 :p_upload_mode LOV custom Y select 'Select for Release' value, 'Select the Order for Release' description from dual union select 'Release the Order' value, 'Select and Automatically Release the Order' description from dual select 'Select for Release' from dual Y US Upload Mode 9 20 :p_autopopulate_release_status LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Auto Populate Release Status 10 100 :p_instance_code LOV MSC Planning Instance A8454EC5D1EB6FE9E0530100007F0874 Y select instance_code value ,null description from ( select instance_code from msc_apps_instances union select instance_code from mrp_ap_apps_instances_all ) order by instance_code Y US Planning Instance 11 &a2m_dblink select mai.a2m_dblink from ( select nvl2(maaia.a2m_dblink,'@'||maaia.a2m_dblink,null) a2m_dblink from mrp_ap_apps_instances_all maaia where maaia.instance_code = :p_instance_code ) mai US Planning Instance 12 &lp_custom_attributes select replace(replace(replace(replace(replace(flvv.description, '<INSTID>', 'x.sr_instance_id'), '<PLANID>', 'x.plan_id'), '<SD>' , 'substr(x.supply_demand_code,1,1)'), '<WHSID>' , 'x.organization_id'), '<TRXID>' , 'x.transaction_id' ) || ' ' || substrb(rtrim(ltrim(rtrim(ltrim(translate(flvv.meaning,'A.+-/\=?#,;:''" ','A______________'))),'_'),'_'),1,xxen_report.max_column_length) || ',' from fnd_lookup_values_vl flvv where flvv.lookup_type = 'XXEN_MSC_CUSTOM_ATTRIBUTES' and flvv.view_application_id = 0 and flvv.enabled_flag = 'Y' and trunc(sysdate) between nvl(flvv.start_date_active,trunc(sysdate)) and nvl(flvv.end_date_active,trunc(sysdate)) order by flvv.meaning US Planning Instance 13 &lp_item_dff_cols select xxen_msc.get_item_dff_lexicals(mai.instance_id,'mov.sr_inventory_item_id','mov.organization_id') from ( select maaia.instance_id from mrp_ap_apps_instances_all maaia where maaia.instance_code = :p_instance_code union select mai.instance_id from msc_apps_instances mai where mai.instance_code = :p_instance_code and not exists (select null from mrp_ap_apps_instances_all maaia where maaia.instance_code = :p_instance_code) ) mai where :p_show_item_dff is not null US Planning Instance 14 &m2a_dblink select mai.m2a_dblink from ( select nvl2(mai.m2a_dblink,'@'||mai.m2a_dblink,null) m2a_dblink from msc_apps_instances mai where mai.instance_code = :p_instance_code ) mai US Planning Instance 15 110 :p_plan_name LOV MSC Plan Name A8452450392C6F2FE0530100007F6DF4 Y select mp.compile_designator value ,null description from msc_plans@A2M_DBLINK mp, msc_apps_instances@A2M_DBLINK mai where mp.plan_id > 0 and mp.sr_instance_id = mai.instance_id and mai.instance_code =:$flex$.planning_instance order by lower(mp.compile_designator) Y US Plan 16 120 1=1 msi.organization_id in (select mpo.organization_id from msc_plan_organizations&a2m_dblink mpo where mpo.organization_code=:p_organization_code and mpo.sr_instance_id = :p_instance_id and mpo.plan_id = :p_plan_id) LOV MSC Plan Organization Code 01F9429D6FE213DFE0630100007FA6FE Y select mpo.organization_code value , null description from msc_plan_organizations@A2M_DBLINK mpo, msc_plans@A2M_DBLINK mp , msc_apps_instances@A2M_DBLINK mai where mp.sr_instance_id = mpo.sr_instance_id and mp.plan_id = mpo.plan_id and mpo.sr_instance_id = mai.instance_id and mp.compile_designator = nvl(:$flex$.plan,mp.compile_designator) and mai.instance_code =:$flex$.planning_instance and ( exists (select null from msc_selected_orgs_v@A2M_DBLINK msov where msov.responsibility_id = fnd_global.resp_id and msov.organization_id = mpo.organization_id) or exists (select null from org_access_view@A2M_DBLINK oav where oav.responsibility_id = fnd_global.resp_id and oav.resp_application_id = fnd_global.resp_appl_id and oav.organization_id = mpo.organization_id) ) order by lower(mpo.organization_code) US Organization 17 130 2=2 mov.source_organization_id in (select mpo.organization_id from msc_plan_organizations&a2m_dblink mpo where mpo.organization_code=:p_source_organization_code and mpo.sr_instance_id = :p_instance_id and mpo.plan_id = :p_plan_id) LOV MSC Plan Organization Code 01F9429D6FE213DFE0630100007FA6FE Y select mpo.organization_code value , null description from msc_plan_organizations@A2M_DBLINK mpo, msc_plans@A2M_DBLINK mp , msc_apps_instances@A2M_DBLINK mai where mp.sr_instance_id = mpo.sr_instance_id and mp.plan_id = mpo.plan_id and mpo.sr_instance_id = mai.instance_id and mp.compile_designator = nvl(:$flex$.plan,mp.compile_designator) and mai.instance_code =:$flex$.planning_instance and ( exists (select null from msc_selected_orgs_v@A2M_DBLINK msov where msov.responsibility_id = fnd_global.resp_id and msov.organization_id = mpo.organization_id) or exists (select null from org_access_view@A2M_DBLINK oav where oav.responsibility_id = fnd_global.resp_id and oav.resp_application_id = fnd_global.resp_appl_id and oav.organization_id = mpo.organization_id) ) order by lower(mpo.organization_code) US Source Organiziation 18 140 :p_category_set_name LOV MSC Category Set A85770BE6AD052E2E0530100007F2871 Y select mcs.category_set_name value, null description from msc_apps_instances@A2M_DBLINK mai, msc_category_sets@A2M_DBLINK mcs where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mcs.sr_instance_id and exists (select null from msc_item_categories@A2M_DBLINK mic where mic.sr_instance_id = mcs.sr_instance_id and mic.category_set_id = mcs.category_set_id ) order by lower(mcs.category_set_name) select xxen_msc.get_default_category_set(:$flex$.planning_instance) from dual Y US Category Set 19 150 1=1 mic.category_name = :p_category LOV MSC Category within a specified Category Set A85776F7DF3D52E0E0530100007F6064 Y Y select distinct mic.category_name value, null description from msc_apps_instances@A2M_DBLINK mai, msc_category_sets@A2M_DBLINK mcs, msc_item_categories@A2M_DBLINK mic where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mcs.sr_instance_id and mcs.category_set_name = :$flex$.category_set and mcs.sr_instance_id = mcs.sr_instance_id and mcs.category_set_id = mcs.category_set_id order by lower(mic.category_name) US Category 20 2=2 mic.category_name = :p_category US Category 21 160 1=1 msi.item_name=:p_item LOV MSC Planned Item A844FAB91C4F7309E0530100007F496B Y Y select msi.item_name value, min(msi.description) description from msc_system_items@A2M_DBLINK msi, msc_plans@A2M_DBLINK mp, msc_apps_instances@A2M_DBLINK mai where msi.sr_instance_id = mai.instance_id and msi.plan_id = mp.plan_id and mp.sr_instance_id = mai.instance_id and mp.compile_designator = nvl(:$flex$.plan,mp.compile_designator) and mai.instance_code =:$flex$.planning_instance group by msi.item_name order by lower(msi.item_name) US Item 22 165 :p_explode_boms LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Explode Assemblies 23 170 2=2 mov.project_number = :p_project LOV MSC Projects 347DEAC4335043D2E0630100007FE943 Y Y select distinct mp.project_number value, null description from msc_apps_instances@A2M_DBLINK mai, msc_projects@A2M_DBLINK mp where mai.instance_code = :$flex$.planning_instance and mai.instance_id = mp.sr_instance_id and mp.plan_id = -1 and ( :$flex$.organization is null or mp.organization_id in (select mpo.organization_id from msc_apps_instances@A2M_DBLINK mai2, msc_plans@A2M_DBLINK mpl, msc_plan_organizations@A2M_DBLINK mpo where mai2.instance_code = :$flex$.planning_instance and mai2.instance_id = mpl.sr_instance_id and mpl.compile_designator = nvl(:$flex$.plan,mpl.compile_designator) and mpl.sr_instance_id = mpo.sr_instance_id and mpl.plan_id = mpo.plan_id and xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) ) order by mp.project_number US Project 24 180 2=2 msi.planner_code = :p_planner LOV MSC Planner Code A85765FE708251D8E0530100007FCD1D Y select distinct mpl.planner_code value, null description from msc_apps_instances@A2M_DBLINK mai, msc_plans@A2M_DBLINK mp, msc_plan_organizations@A2M_DBLINK mpo, msc_planners@A2M_DBLINK mpl where mai.instance_code = :$flex$.planning_instance and mp.sr_instance_id = mai.instance_id and mp.compile_designator = :$flex$.plan and mpo.sr_instance_id = mp.sr_instance_id and mpo.plan_id = mp.plan_id and (:$flex$.organization is null or xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) and mpl.sr_instance_id = mpo.sr_instance_id and mpl.organization_id = mpo.organization_id order by lower(mpl.planner_code) US Planner 25 190 2=2 msi.buyer_name = :p_buyer LOV MSC Buyer Name A8576D437D9C52DEE0530100007F5406 Y select distinct msi.buyer_name value, null description from msc_apps_instances@A2M_DBLINK mai, msc_plans@A2M_DBLINK mp, msc_plan_organizations@A2M_DBLINK mpo, msc_system_items@A2M_DBLINK msi where mai.instance_code = :$flex$.planning_instance and mp.sr_instance_id = mai.instance_id and mp.compile_designator = :$flex$.plan and mpo.sr_instance_id = mp.sr_instance_id and mpo.plan_id = mp.plan_id and (:$flex$.organization is null or xxen_util.contains(:$flex$.organization,mpo.organization_code) = 'Y' ) and msi.sr_instance_id = mpo.sr_instance_id and msi.plan_id = mpo.plan_id and msi.organization_id = mpo.organization_id and msi.buyer_name is not null order by lower(msi.buyer_name) US Buyer 26 200 2=2 msc_get_name.lookup_meaning&a2m_dblink('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) = :p_makeorbuy LOV MSC Make or Buy A8A20CEB0602162EE0530100007FDE80 Y select flv.meaning value, null description from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MTL_PLANNING_MAKE_BUY' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code in (1,2) order by value US Make/Buy 27 210 2=2 mov.order_type=xxen_util.lookup_code&a2m_dblink(:order_type,'MRP_ORDER_TYPE',700) LOV MSC Plan Order Upload Order Types 347DEAC4335143D2E0630100007FE943 Y select flv.meaning value, null description from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MRP_ORDER_TYPE' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code in ('1','2','3','5','13') order by value select flv.meaning from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MRP_ORDER_TYPE' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code = '5' US Order Type 28 220 3=3 mov.action = :p_action LOV MSC Plan Order Upload Actions 347EE4CCAF055ECBE0630100007FAF97 Y select flv.meaning value, null description from fnd_lookup_values@A2M_DBLINK flv where flv.lookup_type='MRP_ACTIONS' and flv.view_application_id = 700 and flv.security_group_id = 0 and flv.enabled_flag = 'Y' and sysdate between nvl(flv.start_date_active,sysdate) and nvl(flv.end_date_active,sysdate) and flv.language = nvl((select fl.language_code from fnd_languages@A2M_DBLINK fl where fl.installed_flag in ('B','I') and language_code = userenv('lang')),fnd_global.base_language@A2M_DBLINK) and flv.lookup_code in ('2','3','4') order by value US Action 29 230 2=2 mov.release_status = decode(:p_release_status,'Y',1,2) LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description US Selected for Release 30 240 2=2 mov.new_schedule_date >= :p_sugg_due_date_fr Date US Suggested Due From 31 250 2=2 mov.new_schedule_date<:p_sugg_due_date_to+1 Date US Suggested Due To 32 280 :p_show_item_dff LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Item Descriptive Attributes :$flex$.Category_Set Category Set Category Set ID :$flex$.Plan Plan Plan Id :$flex$.Planning_Instance Planning Instance Category Set ID :$flex$.Planning_Instance Planning Instance Instance ID :$flex$.Planning_Instance Planning Instance Plan Id :$flex$.category_set Category Set Category :$flex$.organization Organization Buyer :$flex$.organization Organization Planner :$flex$.organization Organization Project :$flex$.organization Organization Task :$flex$.plan Plan Buyer :$flex$.plan Plan Item :$flex$.plan Plan Organization :$flex$.plan Plan Planner :$flex$.plan Plan Project :$flex$.plan Plan Source Organiziation :$flex$.plan Plan Task :$flex$.planning_instance Planning Instance Buyer :$flex$.planning_instance Planning Instance Category :$flex$.planning_instance Planning Instance Category Set :$flex$.planning_instance Planning Instance Item :$flex$.planning_instance Planning Instance Organization :$flex$.planning_instance Planning Instance Plan :$flex$.planning_instance Planning Instance Planner :$flex$.planning_instance Planning Instance Project :$flex$.planning_instance Planning Instance Source Organiziation :$flex$.planning_instance Planning Instance Task :$flex$.project Project Task Plan Order Recommendations Y SYSADMIN 21 ACTION 7 BUYER_NAME 11 CATEGORY CATEGORY_SET 28 COMMENTS 29 FIRM 30 FIRM_DATE 31 FIRM_QUANTITY 27 IMPLEMENTED_QUANTITY 22 IMPLEMENT_AS 23 IMPLEMENT_DATE 24 IMPLEMENT_QUANTITY 1 INSTANCE 9 ITEM 10 ITEM_DESCRIPTION 8 MAKE_BUY 16 NEED_BY_DATE 18 OLD_DUE_DATE 15 ORDER_NUMBER 36 ORDER_PRIORITY 14 ORDER_TYPE 3 ORGANIZATION 2 PLAN 6 PLANNER 4 PROJECT_NUMBER 17 PROMISE_DATE 20 QUANTITY 26 RELEASED_STATUS 35 RESCHEDULE_DAYS 12 SAFETY_STOCK 32 SOURCE_ORGANIZATION 19 SUGGESTED_DUE_DATE 33 SUPPLIER 34 SUPPLIER_SITE 13 SUPPLY_DEMAND 5 TASK_NUMBER 37 TRANSACTION_ID 25 UPDATE_RELEASE_STATUS S Site Plan Order Recommendations - Automatic Release Y Plan Order Recommendations are pre-loaded ready for Automatic Release SYSADMIN 21 ACTION 7 BUYER_NAME 11 CATEGORY CATEGORY_SET 28 COMMENTS 29 FIRM 30 FIRM_DATE 31 FIRM_QUANTITY 27 IMPLEMENTED_QUANTITY 22 IMPLEMENT_AS 23 IMPLEMENT_DATE 24 IMPLEMENT_QUANTITY 1 INSTANCE 9 ITEM 10 ITEM_DESCRIPTION 8 MAKE_BUY 16 NEED_BY_DATE 18 OLD_DUE_DATE 15 ORDER_NUMBER 36 ORDER_PRIORITY 14 ORDER_TYPE 3 ORGANIZATION 2 PLAN 6 PLANNER 4 PROJECT_NUMBER 17 PROMISE_DATE 20 QUANTITY 26 RELEASED_STATUS 35 RESCHEDULE_DAYS 12 SAFETY_STOCK 32 SOURCE_ORGANIZATION 19 SUGGESTED_DUE_DATE 33 SUPPLIER 34 SUPPLIER_SITE 13 SUPPLY_DEMAND 5 TASK_NUMBER 37 TRANSACTION_ID 25 UPDATE_RELEASE_STATUS Planning Instance TST Upload Mode Release the Order S Site ANONYMOUS Plan Order Recommendations SYSADMIN Plan Order Recommendations - Automatic Release Plan Order Recommendations 1 ACTION_ Char varchar2 Y 2 STATUS_ Char varchar2 Y 3 MESSAGE_ Char varchar2 Y 4 REQUEST_ID_ Char varchar2 Y Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 INSTANCE Char varchar2 Y 7 PLAN Char varchar2 Y 8 ORGANIZATION Char varchar2 Y 9 PROJECT_NUMBER Char varchar2 Y 10 TASK_NUMBER Char varchar2 Y 11 PLANNER Char varchar2 Y 12 BUYER_NAME Char varchar2 Y 13 MAKE_BUY Char varchar2 Y 14 ITEM Char varchar2 Y 15 ITEM_DESCRIPTION Char varchar2 Y 16 CATEGORY_SET Char varchar2 Y 17 CATEGORY Char varchar2 Y 18 SAFETY_STOCK Number number Y 19 SUPPLY_DEMAND Char varchar2 Y 20 ORDER_TYPE Char varchar2 Y 21 ORDER_NUMBER Char varchar2 Y 22 NEED_BY_DATE Date date Y 23 PROMISE_DATE Date date Y 24 OLD_DUE_DATE Date date Y 25 SUGGESTED_DUE_DATE Date date Y 26 QUANTITY Number number Y 27 ACTION Char varchar2 Y 28 IMPLEMENT_AS Char varchar2 Y 29 IMPLEMENT_DATE Date date 30 IMPLEMENT_QUANTITY Number number 31 UPDATE_RELEASE_STATUS LOV varchar2 MSC Plan Order Upload Action EB8355FCD86A7EB8E0530100007F0B5B Y select 'S' id, 'Select for Release' value, 'Select for Release' description from dual where nvl(:$flex$.upload_mode,'Select for Release') = 'Select for Release' union select 'R' id, 'Release the Order' value, 'Release the Order' description from dual where nvl(:$flex$.upload_mode,'Release the Order') = 'Release the Order' order by value select x.id, x.value from ( select 'S' id,'Select for Release' value from dual union select 'R' id,'Release the Order' value from dual ) x Y 32 RELEASED_STATUS Char varchar2 Y 33 IMPLEMENTED_QUANTITY Number number Y 34 COMMENTS Char varchar2 Y 35 FIRM Char varchar2 Y 36 FIRM_DATE Date date Y 37 FIRM_QUANTITY Number number Y 38 SOURCE_ORGANIZATION Char varchar2 Y 39 SUPPLIER Char varchar2 Y 40 SUPPLIER_SITE Char varchar2 Y 41 RESCHEDULE_DAYS Number number Y 42 ORDER_PRIORITY Number number Y 43 SUPPLY_DEMAND_CODE Char varchar2 Y Y 44 TRANSACTION_ID Number number Y 45 SR_INSTANCE_ID Number number Y Y 46 PLAN_ID Number number Y Y Y 47 ORGANIZATION_ID Number number Y Y 48 INVENTORY_ITEM_ID Number number Y Y 49 SR_INVENTORY_ITEM_ID Number number Y Y 50 CATEGORY_SET_ID Number number Y Y 51 RELEASE_STATUS Number number Y Y 52 LAST_COL_FLAG Char varchar2 Y Y U 1 action_ in Char ACTION_CODE_ U 2 p_instance in Char INSTANCE U 3 p_plan in Char PLAN U 4 p_sr_instance_id in Number SR_INSTANCE_ID U 5 p_plan_id in Number PLAN_ID U 6 p_organization_id in Number ORGANIZATION_ID U 7 p_transaction_id in Number TRANSACTION_ID U 8 p_identifier in Char ORDER_NUMBER U 9 p_supply_or_demand in Char SUPPLY_DEMAND_CODE U 10 p_implement_date in Date IMPLEMENT_DATE U 11 p_implement_quantity in Number IMPLEMENT_QUANTITY U 12 p_implement_as_text in Char IMPLEMENT_AS U 13 p_update_status_flag in Char UPDATE_RELEASE_STATUS U 14 p_comment1 in Char U 15 p_comment2 in Char U 16 p_comment3 in Char U 17 p_comment4 in Char U 18 p_old_release_status in Number RELEASE_STATUS U 19 request_id_ out Number REQUEST_ID_ U 20 status_ out Char STATUS_CODE_ U 21 message_ out Char MESSAGE_ Error SQL select xu.action_, xu.status_, xu.message_ message_, xu.request_id_ request_id_, xu.modified_columns_, xu.instance, xu.plan, xu.organization, xu.project_number, xu.task_number, xu.planner, xu.buyer_name, xu.make_buy, xu.item, xu.item_description, xu.category_set, xu.category, xu.safety_stock, xu.supply_demand, xu.order_type, xu.order_number, xu.need_by_date, xu.promise_date, xu.old_due_date, xu.suggested_due_date, xu.quantity quantity, xu.action, xu.implement_as, xu.implement_date, xu.implement_quantity, xu.update_release_status, xu.released_status, xu.implemented_quantity, xu.comments, xu.firm, xu.firm_date, xu.firm_quantity, xu.source_organization, xu.supplier, xu.supplier_site, xu.reschedule_days, xu.order_priority, xu.supply_demand_code, xu.transaction_id, xu.sr_instance_id, xu.plan_id, xu.organization_id, xu.inventory_item_id, xu.sr_inventory_item_id, xu.category_set_id, xu.release_status, -- custom attributes from lookup XXEN_MSC_CUSTOM_ATTRIBUTES &lp_custom_attributes -- item dff attributes &lp_item_dff_cols xu.last_col_flag from xxen_msc_plan_order_upl_3507_u xu where xu.status_code_=xxen_upload.status_error Order By instance, plan, organization, planner, item, suggested_due_date, order_type, order_number Success SQL select mov.*, -- custom attributes from lookup XXEN_MSC_CUSTOM_ATTRIBUTES &lp_custom_attributes -- item dff attributes &lp_item_dff_cols '.' last_col_flag from ( select /*+ driving_site(mov) */ xxen_upload.action_meaning&a2m_dblink(xxen_upload.action_update&a2m_dblink) action_, xxen_upload.status_meaning&a2m_dblink(xxen_upload.status_success&a2m_dblink) status_, null message_, null request_id_, null modified_columns_, mai.instance_code instance, mp.compile_designator plan, mpo.organization_code organization, decode(mov.project_id, null, null, msc_get_name.project&a2m_dblink(mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) project_number, decode(mov.task_id, null, null, msc_get_name.task&a2m_dblink(mov.task_id, mov.project_id, mov.organization_id, mov.plan_id, mov.sr_instance_id)) task_number, msi.planner_code planner, msi.buyer_name buyer_name, msc_get_name.lookup_meaning&a2m_dblink('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) make_buy, msi.item_name item, msi.description item_description, nvl(:p_category_set_name,(select mcs.category_set_name from msc_category_sets&a2m_dblink mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) category_set, mic.category_name category, (select distinct max(mss.safety_stock_quantity) keep (dense_rank last order by mss.period_start_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock from msc_safety_stocks&a2m_dblink mss where mss.sr_instance_id = mov.sr_instance_id and mss.plan_id = mov.plan_id and mss.organization_id = mov.organization_id and mss.inventory_item_id = mov.inventory_item_id and mss.period_start_date <= sysdate ) safety_stock, xxen_util.meaning&a2m_dblink('SUPPLY','MSC_QUESTION_TYPE',3) supply_demand, decode(mp.plan_type , 8,decode(mov.order_type , 1, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type) , 2, decode(mov.source_organization_id, null, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type), msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',53)) ,51, msc_get_name.lookup_meaning&a2m_dblink('SRP_CHANGED_ORDER_TYPE',mov.order_type) , msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type)) ) ,msc_get_name.lookup_meaning&a2m_dblink('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type)) ) order_type, msc_get_name.supply_order_number&a2m_dblink(mov.order_type ,mov.order_number ,mov.plan_id ,mov.sr_instance_id ,mov.transaction_id ,mov.disposition_id) order_number, trunc(mov.need_by_date) need_by_date, trunc(mov.promised_date) promise_date, trunc(mov.old_schedule_date) old_due_date, trunc(mov.new_schedule_date) suggested_due_date, nvl(mov.daily_rate,mov.new_order_quantity) quantity, msc_get_name.action&a2m_dblink('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id) action, decode(mov.implement_as,null,null,msc_get_name.lookup_meaning&a2m_dblink('MRP_WORKBENCH_IMPLEMENT_AS',mov.implement_as)) implement_as, mov.implement_date implement_date, nvl(mov.implement_daily_rate,mov.implement_quantity) implement_quantity, null update_release_status, case when mov.release_status = 1 then 'Selected' when mov.reschedule_flag = 1 and nvl(mov.implement_daily_rate,mov.implement_quantity) = 0 then 'Cancelled' when mov.reschedule_flag = 1 then 'Recheduled' when mov.implemented_quantity is not null then 'Released' else null end released_status, mov.implemented_quantity implemented_quantity, msc_get_name.get_order_comments&a2m_dblink(mov.plan_id, 'SUPPLY', mov.transaction_id) comments, decode(mov.firm_planned_type, 1, 'Y',null) firm, trunc(mov.firm_date) firm_date, mov.firm_quantity firm_quantity, msc_get_name.org_code&a2m_dblink(mov.source_organization_id,mov.source_sr_instance_id) source_organization, msc_get_name.supplier&a2m_dblink(mov.supplier_id) supplier, msc_get_name.supplier_site&a2m_dblink(mov.supplier_site_id) supplier_site, mov.reschedule_days reschedule_days, decode(mp.plan_type, 5, to_number(null), mov.schedule_priority) order_priority, 'SUPPLY' supply_demand_code, mov.transaction_id, mov.sr_instance_id, mov.plan_id, mov.organization_id, mov.inventory_item_id, msi.sr_inventory_item_id, mic.category_set_id category_set_id, mov.release_status from msc_supplies&a2m_dblink mov, msc_apps_instances&a2m_dblink mai, msc_plans&a2m_dblink mp, msc_plan_organizations&a2m_dblink mpo, msc_system_items&a2m_dblink msi, msc_item_categories&a2m_dblink mic where mov.sr_instance_id = mai.instance_id and mov.plan_id = mp.plan_id and mov.sr_instance_id = mpo.sr_instance_id and mov.plan_id = mpo.plan_id and mov.organization_id = mpo.organization_id and mov.sr_instance_id = msi.sr_instance_id and mov.plan_id = msi.plan_id and mov.organization_id = msi.organization_id and mov.inventory_item_id = msi.inventory_item_id and mov.sr_instance_id = mic.sr_instance_id and mov.inventory_item_id = mic.inventory_item_id and mov.organization_id = mic.organization_id and mic.category_set_id = nvl(xxen_msc.get_category_set_id&a2m_dblink(:p_instance_code,:p_category_set_name),(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) and (mov.sr_instance_id, mov.plan_id, mov.transaction_id) in (select xu.sr_instance_id, xu.plan_id, xu.transaction_id from xxen_msc_plan_order_upl_3507_u xu where xu.status_code_ = xxen_upload.status_success) ) mov LOV query :$flex$.upload_mode Upload Mode|:p_upload_mode| UPDATE_RELEASE_STATUS