ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E7CCB27F39541272E0530100007F62B4 BOM Resource Code (org code dependent) Y select br.resource_code value, br.description from bom_resources br where (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code US A486BD05719B2A82E053BB6B6358FC8E CST Cost Type (No ICP PII) Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') order by cct.cost_type US All cost types except ICP (intercompany profit) or PII (profit in inventory). D5CA808540E0FA18E05369FB090580A4 CST WIP Report Option Y select 'Open jobs' value, 'Report only open jobs' description from dual union all select 'All jobs' value, 'Report all jobs' description from dual union all select 'Closed jobs' value, 'Report only closed jobs' description from dual order by 1 US List of values to select All Jobs, Open Jobs or Closed Jobs. 8E2FF36EDEC879D2E0530100007F1FF2 GL Chart of Accounts (inv org related) Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name AR دليل الحسابات المتعلقة بمنظمات المخزون D Kontenplan für Bestandsorganisationen E Plan de cuentas relacionado con las organizaciones de inventario F Plan comptable relatif aux organismes d'inventaire I Piano dei conti relativo alle organizzazioni dell'inventario JA 棚卸し組織に関する勘定科目一覧表 KO 재고 조직과 관련된 계정과 목표 PTB Plano de contas relacionado às organizações de inventário RU План счетов организаций, занимающихся инвентаризацией S Kontoplan relaterade till lagerorganisationer TR Envanter organizasyonları ile ilgili hesap planı US Chart of accounts related to inventory organizations ZHS 与库存组织有关的会计科目表 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id 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) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US 91D022B15B12FFB9E053BB6B63587F0B INV Item (master, costing enabled only) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US All costing enabled items from item master org B42C590EA41DC91BE0530100007F4403 INV OSP Item Number (any OSP item from item master org) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id= ( select min(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.outside_operation_flag='Y' order by msiv.concatenated_segments AR أي عنصر معالجة خارجي من مؤسسة Item Master Org ، بما في ذلك تم تمكين احتساب التكاليف هو لا D Jede Fremdbearbeitungsposition aus Item Master Org, einschließlich Costing Enabled ist No E Cualquier elemento de procesamiento externo de Item Master Org, incluido el cálculo de costos habilitado es No F Tout élément de traitement externe provenant de l'Item Master Org, y compris le calcul des coûts activé, est non I Qualsiasi voce di elaborazione esterna da Item Master Org, incluso Costing Enabled è No JA 項目マスタ組織からの外部処理項目で、原価計算を有効にしているものはありません。 KO 원가 계산 사용을 포함하여 품목 마스터 조직의 외주 가공 품목은 아니오입니다. PTB Qualquer item de processamento externo da Item Master Org, incluindo o custo habilitado é Não RU Любой внешний элемент обработки из пункта Мастер Орг, включая включенную калькуляцию стоимости, нет. S Alla externa bearbetningsartiklar från artikel Master Org, inklusive kostnadsberäkning aktiverad, är nr TR Maliyet Oluşturma Etkin dahil olmak üzere Öğe Ana Org'dan herhangi bir dış işleme öğesi Hayır'dır US Any outside processing item from Item Master Org, including Costing Enabled is No ZHS 任何来自项目主机关的外部处理项目，包括成本计算启用，都是No。 8E2FF36EDF2279D2E0530100007F1FF2 INV Period Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name AR فترات دفتر الأستاذ العام السابقة للمخزون (تعتمد على دفتر الأستاذ و / أو معلمة رمز المؤسسة) للمؤسسات غير الرئيسية D Vergangene Bestands-GL-Perioden (abhängig von Ledger- und/oder Organisationscode-Parameter) für Nicht-Master-Organisationen E Períodos GL de inventario pasados (dependientes de los parámetros del libro mayor y/o del código de organización) para organizaciones no maestras F Périodes passées de GL d'inventaire (dépendant du grand livre et/ou du code d'organisation) pour les organisations non maîtresses I Periodi GL di inventario passati (dipendente dal parametro del ledger e/o del codice dell'organizzazione) per organizzazioni non master JA 非マスター組織の過去のインベントリGL期間（元帳および/または組織コードパラメータに依存する）。 KO 비 마스터 조직에 대한 과거 재고 GL 기간 (원장 및 / 또는 조직 코드 매개 변수에 따라 다름) PTB Períodos GL de inventário anteriores (ledger e/ou código de organização dependente do parâmetro) para organizações não-mestras RU Прошлые инвентаризационные GL-периоды (зависящие от бухгалтерской книги и/или параметра организационного кода) для неосновных организаций S Tidigare lager GL-perioder (storbok och / eller organisationskodparameter beroende) för icke-huvudorganisationer TR Ana olmayan kuruluşlar için geçmiş envanter GL dönemleri (genel muhasebe ve / veya kuruluş kodu parametresine bağlı) US Past inventory GL periods (ledger and/or organization code parameter dependent) for non master organizations ZHS 非主组织的过去库存GL期(分类账和/或组织代码参数而定) E7CCB27F39561272E0530100007F62B4 WIP Class Code (org dependent) Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US 8E2FF36EDEC379D2E0530100007F1FF2 WIP Job Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null 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 xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US 8E2FF36EDE9579D2E0530100007F1FF2 WIP Job Status Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US D2E7744C610F11F2E0530100007F3CF3 with wdj00 as ( select /*+ materialize */ wdj.wip_entity_id,min(wt.transaction_date) transaction_date from wip_discrete_jobs wdj, org_acct_periods oap, mtl_parameters mp, wip_accounting_classes wac, wip_transactions wt where wdj.class_code = wac.class_code and wdj.organization_id = wac.organization_id and wac.class_type in (1,3,5) and oap.organization_id = wdj.organization_id and mp.organization_id = wdj.organization_id and wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null and wt.transaction_date < oap.schedule_close_date + 1 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 2=2 -- p_org_code and 3=3 -- p_assembly_number and 4=4 -- p_period_name, p_wip_job, wip_status, p_wip_class_code group by wdj.wip_entity_id ), wdj0 as (select /*+ materialize */ wdj.wip_entity_id, wdj.organization_id, wdj.class_code, wdj.creation_date, wdj.scheduled_start_date, wdj.date_released, wdj.date_completed, wdj.date_closed, wdj.last_update_date, wdj.primary_item_id, wdj.lot_number, wdj.status_type, wdj.start_quantity, wdj.net_quantity, wdj.project_id, wdj.material_account, -- Revision for version 1.22 wdj.resource_account, wdj.outside_processing_account, -- End revision for version 1.22 wdj.material_overhead_account, wdj.overhead_account, wdj.quantity_completed, wdj.quantity_scrapped, oap.period_start_date, oap.schedule_close_date, oap.period_name, -- Revision for version 1.22 (case when wdj.date_closed >= oap.period_start_date and wdj.date_closed < oap.schedule_close_date + 1 then 'Variance' -- the job is open when wdj.date_closed is null and((wdj.creation_date < oap.schedule_close_date + 1 and not exists(select null from wip_transactions wt where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null)) or(wdj00.transaction_date<oap.schedule_close_date + 1)) then 'Valuation' -- the job is closed and ...the job was closed after the accounting period when wdj.date_closed is not null and wdj.date_closed >= oap.schedule_close_date + 1 then 'Valuation' end ) Report_Type, -- End revision for version 1.22 -- Revision for version 1.10 oap.acct_period_id, mp.primary_cost_method, mp.organization_code, wac.class_type from wip_discrete_jobs wdj, org_acct_periods oap, mtl_parameters mp, wip_accounting_classes wac, wdj00 where wdj.class_code = wac.class_code and wdj.organization_id = wac.organization_id and wac.class_type in (1,3,5) and wdj.wip_entity_id=wdj00.wip_entity_id(+) and oap.organization_id = wdj.organization_id and mp.organization_id = wdj.organization_id -- find jobs that were open or closed during or after the report period -- the job is open or opened before the period close date and ( (wdj.date_closed is null -- the job is open and((wdj.creation_date < oap.schedule_close_date + 1 and not exists (select null from wip_transactions wt where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null)) or(wdj00.transaction_date<oap.schedule_close_date + 1) ) and :p_report_option in ('Open jobs', 'All jobs') -- p_report_option ) or -- the job is closed and ...the job was closed after the accounting period (wdj.date_closed is not null and wdj.date_closed >= oap.schedule_close_date + 1 and((wdj.creation_date < oap.schedule_close_date + 1 and not exists (select null from wip_transactions wt where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null)) or(wdj00.transaction_date<oap.schedule_close_date + 1) ) and :p_report_option in ('Open jobs', 'All jobs') -- p_report_option ) or -- find jobs that were closed during the report period (wdj.date_closed >= oap.period_start_date and wdj.date_closed < oap.schedule_close_date + 1 and :p_report_option in ('Closed jobs', 'All jobs') -- p_report_option ) ) 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 2=2 -- p_org_code and 3=3 -- p_assembly_number and 4=4 -- p_period_name, p_wip_job, wip_status, p_wip_class_code ), wdj as (select wdjsum.wip_entity_id, wdjsum.organization_id, wdjsum.class_code, wdjsum.creation_date, wdjsum.scheduled_start_date, wdjsum.date_released, wdjsum.date_closed, wdjsum.date_completed, wdjsum.last_update_date, wdjsum.primary_item_id, wdjsum.lot_number, wdjsum.status_type, wdjsum.start_quantity, wdjsum.net_quantity, wdjsum.project_id, wdjsum.material_account, -- Revision for version 1.22 wdjsum.resource_account, wdjsum.outside_processing_account, wdjsum.material_overhead_account, wdjsum.overhead_account, -- End revision for version 1.22 wdjsum.period_start_date, wdjsum.schedule_close_date, wdjsum.period_name, -- Revision for version 1.22 wdjsum.report_type, -- Revision for version 1.10 wdjsum.acct_period_id, wdjsum.primary_cost_method, wdjsum.organization_code, wdjsum.class_type, sum (wdjsum.quantity_completed) quantity_completed, sum (wdjsum.quantity_scrapped) quantity_scrapped, -- Revision for version 1.1, if scrap is not financially recorded do not include in component requirements sum(decode(:p_include_scrap, 'N', 0, wdjsum.quantity_scrapped)) adj_quantity_scrapped from (select wdj0.* from wdj0 union all select wdj0.wip_entity_id, wdj0.organization_id, wdj0.class_code, wdj0.creation_date, wdj0.scheduled_start_date, wdj0.date_released, wdj0.date_completed, wdj0.date_closed, wdj0.last_update_date, wdj0.primary_item_id, wdj0.lot_number, wdj0.status_type, wdj0.start_quantity, wdj0.net_quantity, wdj0.project_id, wdj0.material_account, -- Revision for version 1.22 wdj0.resource_account, wdj0.outside_processing_account, wdj0.material_overhead_account, wdj0.overhead_account, -- End revision for version 1.22 decode(mmt.transaction_type_id, 90, 0, -- scrap assemblies from wip 91, 0, -- return assemblies scrapped from wip 44, -1 * mmt.primary_quantity, -- wip completion 17, mmt.primary_quantity -- wip completion return ) quantity_completed, decode(mmt.transaction_type_id, 90, mmt.primary_quantity, -- scrap assemblies from wip 91, -1 * mmt.primary_quantity, -- return assemblies scrapped from wip 44, 0, -- wip completion 17, 0 -- wip completion return ) quantity_scrapped, wdj0.period_start_date, wdj0.schedule_close_date, wdj0.period_name, -- Revision for version 1.22 wdj0.report_type, -- Revision for version 1.10 wdj0.acct_period_id, wdj0.primary_cost_method, wdj0.organization_code, wdj0.class_type from wdj0, mtl_material_transactions mmt where mmt.transaction_source_type_id = 5 and mmt.transaction_source_id = wdj0.wip_entity_id and mmt.transaction_date >= wdj0.schedule_close_date + 1 and wdj0.organization_id = mmt.organization_id ) wdjsum group by wdjsum.wip_entity_id, wdjsum.organization_id, wdjsum.class_code, wdjsum.creation_date, wdjsum.scheduled_start_date, wdjsum.date_released, wdjsum.date_completed, wdjsum.date_closed, wdjsum.last_update_date, wdjsum.primary_item_id, wdjsum.lot_number, wdjsum.status_type, wdjsum.start_quantity, wdjsum.net_quantity, wdjsum.project_id, wdjsum.material_account, -- Revision for version 1.22 wdjsum.resource_account, wdjsum.outside_processing_account, -- End revision for version 1.22 wdjsum.material_overhead_account, wdjsum.overhead_account, wdjsum.period_start_date, wdjsum.schedule_close_date, wdjsum.period_name, -- Revision for version 1.22 wdjsum.report_type, -- Revision for version 1.10 wdjsum.acct_period_id, wdjsum.primary_cost_method, wdjsum.organization_code, wdjsum.class_type ), -- Revision for version 1.22 wdj_assys as (select distinct wdj.primary_item_id, wdj.organization_id, wdj.primary_cost_method from wdj), wdj_wip_trxn as ( select max(wt.transaction_date) transaction_date, wt.transaction_type, wt.resource_id, wt.operation_seq_num, wt.resource_seq_num, wt.wip_entity_id from wip_transactions wt, wdj00 wdj where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null group by wt.resource_id, wt.operation_seq_num, wt.resource_seq_num, wt.wip_entity_id, wt.transaction_type ) ----------------main query starts here-------------- select res_sum.report_type Report_Type, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, res_sum.organization_code Org_Code, res_sum.period_name Period_Name, &segment_columns res_sum.class_code WIP_Class, ml1.meaning Class_Type, we.wip_entity_name WIP_Job, (select ppa.segment1 from pa_projects_all ppa where ppa.project_id = res_sum.project_id) Project_Number, ml2.meaning Job_Status, -- Revision for version 1.21 res_sum.creation_date Creation_Date, res_sum.scheduled_start_date Scheduled_Start_Date, -- End revision for version 1.21 res_sum.date_released Date_Released, res_sum.date_completed Date_Completed, res_sum.date_closed Date_Closed, res_sum.last_update_date Last_Update_Date, muomv.uom_code UOM_Code, msiv_fg.std_lot_size Item_Std_Lot_Size, -- Revision for version 1.22 cic.cost_type Lot_Size_Cost_Type, nvl(cic.lot_size, 1) Costing_Lot_Size, res_sum.start_quantity Start_Quantity, res_sum.quantity_completed Assembly_Quantity_Completed, res_sum.quantity_scrapped Assembly_Quantity_Scrapped, res_sum.fg_total_qty Total_Assembly_Quantity, msiv_fg.concatenated_segments Assembly, msiv_fg.description Assembly_Description, -- Revision for version 1.22 fl.meaning Rolled_Up, cic.last_rollup_date Last_Cost_Rollup, -- End revision for version 1.22 fcl.meaning Item_Type, misv.inventory_item_status_code Item_Status, ml4.meaning Make_Buy_Code, &category_columns -- Revision for version 1.22 res_sum.lot_number Lot_Number, msiv_osp.concatenated_segments Outside_Processing_Item, msiv_osp.description OSP_Description, (select poh.segment1 from po_headers_all poh where poh.po_header_id = res_sum.po_header_id) PO_Number, decode(res_sum.line_num, 0, null, res_sum.line_num) Line_Number, decode(res_sum.release_num, 0, null, res_sum.release_num) PO_Release, bd.department_code Department, res_sum.operation_seq_num Operation_Seq_Number, res_sum.operation_code, res_sum.resource_seq_num Resource_Seq_Number, res_sum.resource_code Resource_Code, -- Revision for version 1.22 res_sum.description Resource_Description, xxen_util.meaning(res_sum.transaction_type,'WIP_TRANSACTION_TYPE',700) transaction_type_name, -- Revision for version 1.25 ml5.meaning Auto_Charge, ml6.meaning Std_Rate, -- End revision for version 1.25 ml3.meaning Basis_Type, -- Revision for version 1.25 res_sum.po_currency_code PO_Currency_Code, -- Revision for version 1.17 decode(res_sum.line_num, 0, null, res_sum.po_unit_price) PO_Unit_Price, res_sum.cost_type Resource_Cost_Type, -- Revision for version 1.21 nvl(xxen_util.meaning(res_sum.cost_element_id ,'CST_COST_CODE_TYPE',700),'No Cost') cost_element_type, gl.currency_code Currency_Code, res_sum.resource_rate Resource_Rate, res_sum.res_unit_of_measure Resource_UOM_Code, res_sum.usage_rate_or_amount Quantity_Per_Assembly, res_sum.total_req_quantity Total_Required_Quantity, res_sum.applied_resource_units Total_Units_Applied, -- Revision for version 1.17 -- res_sum.qty_variance Quantity_Variance, round(res_sum.applied_resource_units - res_sum.total_req_quantity,6) Quantity_Variance, res_sum.std_resource_value Standard_Resource_Value, round(res_sum.applied_resource_value,2) Applied_Resource_Value, -- res_sum.res_efficiency_variance Resource_Efficiency_Variance, round(res_sum.applied_resource_value - res_sum.std_resource_value,2) Resource_Efficiency_Variance from mtl_system_items_vl msiv_fg, mtl_system_items_vl msiv_osp, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, bom_departments bd, wip_entities we, mfg_lookups ml1, -- WIP Class mfg_lookups ml2, -- WIP Status mfg_lookups ml3, -- Basis Type mfg_lookups ml4, -- Planning Make Buy -- Revision for version 1.25 mfg_lookups ml5, -- Autocharge Type mfg_lookups ml6, -- Standard Rate -- End revision for version 1.25 -- Revision for version 1.22 fnd_lookups fl, -- Rolled Up fnd_common_lookups fcl, -- Item Type gl_code_combinations gcc, -- wip job accounts hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units haou2, gl_ledgers gl, -- Revision for version 1.22 -- cst_item_costs cic, -- Get the assembly cost type, lot size and cost rollup status (select cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.lot_size, case when sum(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then 1 else 0 end) > 0 then 'Y' else 'N' end rolled_up, max(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then cicd.creation_date else null end) last_rollup_date from cst_item_costs cic, cst_item_cost_details cicd, cst_cost_types cct, -- Limit to assemblies on WIP jobs wdj_assys where cic.organization_id = cicd.organization_id (+) and cic.inventory_item_id = cicd.inventory_item_id (+) and cic.cost_type_id = cicd.cost_type_id (+) and cic.inventory_item_id = wdj_assys.primary_item_id and cic.organization_id = wdj_assys.organization_id and cct.cost_type_id = cic.cost_type_id -- Revision for version 1.22 and 1.23 and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = wdj_assys.primary_cost_method ), :p_cost_type ) group by cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.cost_type_id, cic.lot_size union all select cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.lot_size, case when sum(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then 1 else 0 end) > 0 then 'Y' else 'N' end rolled_up, max(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then cicd.creation_date else null end) last_rollup_date from cst_item_costs cic, cst_item_cost_details cicd, cst_cost_types cct, -- Limit to assemblies on WIP jobs wdj_assys where cic.cost_type_id = cicd.cost_type_id (+) and cic.inventory_item_id = cicd.inventory_item_id (+) and cic.organization_id = cicd.organization_id (+) and cic.inventory_item_id = wdj_assys.primary_item_id and cic.organization_id = wdj_assys.organization_id and cic.cost_type_id = wdj_assys.primary_cost_method -- this gets the Frozen Costs and cct.cost_type_id <> wdj_assys.primary_cost_method -- this avoids getting the Frozen costs twice -- Revision for version 1.22 and 1.23 and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = wdj_assys.primary_cost_method ), :p_cost_type ) -- ==================================== -- Find all the Frozen costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists ( select 'x' from cst_item_costs cic2 where cic2.organization_id = cic.organization_id and cic2.inventory_item_id = cic.inventory_item_id and cic2.cost_type_id = cct.cost_type_id) group by cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.cost_type_id, cic.lot_size ) cic, -- End revision for version 1.22 -- ======================================================== -- Get the WIP Resource Information in a three part union -- which is then condensed into a summary data set -- ======================================================== -- ======================================================== -- Section I Condense into a summary data set. -- ======================================================= (select res.report_type, res.period_name, res.organization_code, res.organization_id, res.primary_cost_method, res.account, res.class_code, res.class_type, res.wip_entity_id, res.project_id, res.status_type, res.primary_item_id, -- Revision for version 1.22 res.lot_number, -- Revision for version 1.21 res.creation_date, res.scheduled_start_date, -- End revision for version 1.21 res.date_released, res.date_completed, res.date_closed, res.last_update_date, res.start_quantity, res.quantity_completed, res.quantity_scrapped, res.fg_total_qty, max(res.po_header_id) po_header_id, max(res.line_num) line_num, max(res.release_num) release_num, max(res.purchase_item_id) purchase_item_id, min(res.transaction_type)transaction_type, res.department_id, -- Revision for version 1.22 1 level_num, res.operation_seq_num, -- Revision for version 1.25 res.autocharge_type, res.standard_rate_flag, res.po_currency_code, -- End revision for version 1.25 res.resource_seq_num, res.resource_code, -- Revision for version 1.22 res.description, res.basis_type, res.res_unit_of_measure, max(res.po_unit_price) po_unit_price, res.cost_type, res.cost_element_id, res.operation_code, res.resource_rate, sum(res.usage_rate_or_amount) usage_rate_or_amount, sum(res.total_req_quantity) total_req_quantity, sum(res.applied_resource_units) applied_resource_units, -- Revision for version 1.17 -- sum(res.qty_variance) qty_variance, -- Revision for version 1.17 sum(res.std_resource_value) std_resource_value, sum(res.applied_resource_value) applied_resource_value from -- ======================================================== -- Section II.A.OSP -- First get the OSP resource information with the related -- PO number and unit price information -- ======================================================= -- Revision for version 1.22 (select 'II.A' section, wdj.report_type, wdj.period_name, wdj.organization_code, wdj.organization_id, -- Revision for version 1.22 -- cct.cost_type primary_cost_type, wdj.primary_cost_method, -- End revision for version 1.22 --wdj.outside_processing_account account, br.cost_element_id, bso.operation_code, decode(br.cost_element_id, 1, wdj.material_account, 2, wdj.material_overhead_account, 3, wdj.resource_account, 4, wdj.outside_processing_account, 5, wdj.overhead_account, wdj.outside_processing_account )account, wdj.class_code, wdj.class_type, wdj.wip_entity_id, wdj.project_id, wdj.status_type, wdj.primary_item_id, -- Revision for version 1.22 wdj.lot_number, -- Revision for version 1.21 wdj.creation_date, wdj.scheduled_start_date, -- End revision for version 1.21 wdj.date_released, wdj.date_completed, wdj.date_closed, wdj.last_update_date, wdj.start_quantity, wdj.quantity_completed, wdj.quantity_scrapped, nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0) fg_total_qty, poh.po_header_id, pol.line_num, pr.release_num, br.purchase_item_id, wo.department_id, wo.operation_seq_num, wor.resource_seq_num, br.resource_code, -- Revision for version 1.22 br.description, wor.basis_type, -- Revision for version 1.25 wor.autocharge_type, wor.standard_rate_flag, wt.transaction_type, poh.currency_code po_currency_code, -- End revision for version 1.25 br.unit_of_measure res_unit_of_measure, nvl(pll.price_override, pol.unit_price) po_unit_price, crc.cost_type cost_type, nvl(crc.resource_rate,0) resource_rate, nvl(wor.usage_rate_or_amount,0) usage_rate_or_amount, -- Revision for version 1.22 -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close' -- use the completions plus scrap quantities unless for lot-based jobs nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) else -- else use the start quantity times the usage rate or amount decode(nvl(:p_use_completion_qtys,'N'), 'Y', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ), -- Revision for version 1.24 'N', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) ) end ,6),0) total_req_quantity, nvl(applied_resource_units,0) applied_resource_units, -- Revision for version 1.17 and 1.22 -- If the job status is "Complete" then use the completions plus -- scrap quantities else use the planned required quantities; and -- use the completions plus scrap quantities unless for lot-based jobs -- Get the total required quantity nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) else -- else use the start quantity times the usage rate or amount decode(nvl(:p_use_completion_qtys,'N'), 'Y', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ), -- Revision for version 1.24 'N', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) ) end ,6),0) -- total_req_quantity -- And multiply by the AvgRate or Frozen standard costs * nvl(crc.resource_rate,0) std_resource_value, -- End revision for version 1.17 nvl(wor.applied_resource_value,0) applied_resource_value from -- Revision for version 1.22 -- wip_accounting_classes wac, -- org_acct_periods oap -- mtl_parameters mp, -- cst_cost_types cct, wdj, -- End revision for version 1.22 wip_operations wo, wip_operation_resources wor, wip_repetitive_schedules wrs, bom_resources br, bom_standard_operations bso, wdj_wip_trxn wt, po_headers_all poh, po_lines_all pol, po_line_locations_all pll, po_releases_all pr, po_distributions_all pod, -- Revision for version 1.22 -- Get the Resource Cost Type, Cost Basis Type and Resource Rates (select crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate resource_rate from cst_resource_costs crc, cst_cost_types cct, mtl_parameters mp where crc.cost_type_id = decode(cct.cost_type_id, 2, mp.avg_rates_cost_type_id, -- Average Costing 5, mp.avg_rates_cost_type_id, -- FIFO Costing 6, mp.avg_rates_cost_type_id, -- LIFO Costing cct.cost_type_id) and crc.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 2=2 -- p_org_code and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = mp.primary_cost_method ), :p_cost_type ) group by crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate union all -- If missing from the above query, get the Frozen or AvgRates Resource Costs select crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate resource_rate from cst_resource_costs crc, cst_cost_types cct, mtl_parameters mp where crc.organization_id = mp.organization_id and crc.cost_type_id = decode(mp.primary_cost_method, 1, 1, -- Standard Costing, Frozen Cost Type 2, mp.avg_rates_cost_type_id, -- Average Costing 3, -99, -- Periodic Average 4, -99, -- Periodic Incremental LIFO 5, mp.avg_rates_cost_type_id, -- FIFO Costing 6, mp.avg_rates_cost_type_id -- LIFO Costing ) -- Don't get the Frozen or AvgRates resource costs twice and cct.cost_type_id <> decode(mp.primary_cost_method, 1, 1, -- Standard Costing, Frozen Cost Type 2, mp.avg_rates_cost_type_id, -- Average Costing 3, -99, -- Periodic Average 4, -99, -- Periodic Incremental LIFO 5, mp.avg_rates_cost_type_id, -- FIFO Costing 6, mp.avg_rates_cost_type_id -- LIFO Costing ) 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 2=2 -- p_org_code and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = mp.primary_cost_method ), :p_cost_type ) -- ==================================== -- Find all the resource costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists (select 'x' from cst_resource_costs crc2 where crc2.organization_id = crc.organization_id and crc2.resource_id = crc.resource_id and crc2.cost_type_id = case when mp.primary_cost_method = 1 then cct.cost_type_id when mp.primary_cost_method = 2 and cct.cost_type_id <> 2 then cct.cost_type_id when mp.primary_cost_method = 2 and cct.cost_type_id = 2 then mp.avg_rates_cost_type_id when mp.primary_cost_method = 3 then -99 when mp.primary_cost_method = 4 then -99 when mp.primary_cost_method = 5 and cct.cost_type_id <> 5 then cct.cost_type_id when mp.primary_cost_method = 5 and cct.cost_type_id = 5 then mp.avg_rates_cost_type_id when mp.primary_cost_method = 6 and cct.cost_type_id <> 6 then cct.cost_type_id when mp.primary_cost_method = 6 and cct.cost_type_id = 6 then mp.avg_rates_cost_type_id else cct.cost_type_id end ) group by crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate ) crc -- End revision for version 1.22 -- =========================================== -- WIP_Job Entity, Class and Period joins -- =========================================== where wo.wip_entity_id = wdj.wip_entity_id and wo.organization_id = wdj.organization_id and wo.wip_entity_id = wor.wip_entity_id and wo.organization_id = wor.organization_id and wo.operation_seq_num = wor.operation_seq_num and wor.resource_id = br.resource_id -- Revision for version 1.22 -- and cct.cost_type_id = wdj.primary_cost_method -- =========================================== -- PO Table Joins for version 1.8 -- =========================================== and poh.po_header_id = pod.po_header_id and pol.po_line_id = pod.po_line_id and pll.line_location_id = pod.line_location_id and pod.po_release_id = pr.po_release_id (+) and wor.wip_entity_id = pod.wip_entity_id and wor.resource_id = pod.bom_resource_id and wor.resource_seq_num = pod.wip_resource_seq_num and wor.operation_seq_num = pod.wip_operation_seq_num and wor.organization_id = pod.destination_organization_id -- =========================================== -- Cost Table Joins for version 1.22 -- =========================================== and wor.resource_id = crc.resource_id (+) and wor.organization_id = crc.organization_id (+) -- =========================================== -- Use the joins in wip_operation_resources_v as the same is used in Oracle forms -- =========================================== and wrs.organization_id(+)= wor.organization_id and wrs.wip_entity_id(+)= wor.wip_entity_id and wrs.repetitive_schedule_id(+)= wor.repetitive_schedule_id and wor.wip_entity_id=wt.wip_entity_id(+) and wor.resource_id=wt.resource_id(+) and wor.operation_seq_num=wt.operation_seq_num(+) and wor.resource_seq_num=wt.resource_seq_num(+) and case when wt.wip_entity_id=wor.wip_entity_id and wt.transaction_date<wdj.schedule_close_date+1 then 'Y' when not exists(select null from wip_transactions wt1 where wt1.wip_entity_id=wdj.wip_entity_id) then 'Y' end='Y' and bso.standard_operation_id(+)=wo.standard_operation_id and nvl(bso.operation_type, 1)=1 and bso.line_id is null and 8=8 -- p_resource_code union all -- ======================================================= -- Section II.B. Non-OSP -- Now get the non-OSP resource information -- ======================================================= -- Revision for version 1.22 select 'II.B' section, wdj.report_type, wdj.period_name, wdj.organization_code, wdj.organization_id, -- Revision for version 1.22 -- cct.cost_type primary_cost_type, wdj.primary_cost_method, -- End revision for version 1.22 --wdj.resource_account account, br.cost_element_id, bso.operation_code, decode(br.cost_element_id, 1, wdj.material_account, 2, wdj.material_overhead_account, 3, wdj.resource_account, 4, wdj.outside_processing_account, 5, wdj.overhead_account, wdj.resource_account )account, wdj.class_code, wdj.class_type, wdj.wip_entity_id, wdj.project_id, wdj.status_type, wdj.primary_item_id, -- Revision for version 1.22 wdj.lot_number, -- Revision for version 1.21 wdj.creation_date, wdj.scheduled_start_date, -- End revision for version 1.21 wdj.date_released, wdj.date_completed, wdj.date_closed, wdj.last_update_date, wdj.start_quantity, wdj.quantity_completed, wdj.quantity_scrapped, nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0) fg_total_qty, 0 purchase_item_id, 0 po_header_id, 0 line_num, 0 release_num, wo.department_id, wo.operation_seq_num, wor.resource_seq_num, br.resource_code, -- Revision for version 1.22 br.description, wor.basis_type, -- Revision for version 1.25 wor.autocharge_type, wor.standard_rate_flag, wt.transaction_type, null po_currency_code, -- End revision for version 1.25 br.unit_of_measure res_unit_of_measure, 0 po_unit_price, -- Revision for version 1.22 crc.cost_type, nvl(crc.resource_rate,0) resource_rate, -- End revision for version 1.22 nvl(wor.usage_rate_or_amount,0) usage_rate_or_amount, -- Revision for version 1.22 -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close' -- use the completions plus scrap quantities unless for lot-based jobs nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) else -- else use the start quantity times the usage rate or amount decode(nvl(:p_use_completion_qtys,'N'), 'Y', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ), -- Revision for version 1.24 'N', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) ) end ,6),0) total_req_quantity, -- End revision for version 1.22 nvl(applied_resource_units,0) applied_resource_units, -- Revision for version 1.17 and 1.22 -- If the job status is "Complete" then use the completions plus -- scrap quantities else use the planned required quantities; and -- use the completions plus scrap quantities unless for lot-based jobs -- Get the total required quantity nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) else -- else use the start quantity times the usage rate or amount decode(nvl(:p_use_completion_qtys,'N'), 'Y', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ), -- Revision for version 1.24 'N', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis -- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity * decode(wor.repetitive_schedule_id, null, wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)), wrs.daily_production_rate * wrs.processing_work_days ) ) ) end ,6),0) -- total_req_quantity -- And multiply by the AvgRate or Frozen standard costs * nvl(crc.resource_rate,0) std_resource_value, -- End revision for version 1.17 and 1.22 nvl(wor.applied_resource_value,0) applied_resource_value from -- Revision for version 1.22 -- wip_accounting_classes wac, -- org_acct_periods oap -- mtl_parameters mp, -- cst_cost_types cct, wdj, wip_operations wo, wip_operation_resources wor, wip_repetitive_schedules wrs, wdj_wip_trxn wt, bom_standard_operations bso, bom_resources br, -- Revision for version 1.22 -- Get the Resource Cost Type, Cost Basis Type and Resource Rates (select crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate resource_rate from cst_resource_costs crc, cst_cost_types cct, mtl_parameters mp where crc.cost_type_id = decode(cct.cost_type_id, 2, mp.avg_rates_cost_type_id, -- Average Costing 5, mp.avg_rates_cost_type_id, -- FIFO Costing 6, mp.avg_rates_cost_type_id, -- LIFO Costing cct.cost_type_id) and crc.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 2=2 -- p_org_code and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = mp.primary_cost_method ), :p_cost_type ) group by crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate union all -- If missing from the above query, get the Frozen or AvgRates Resource Costs select crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate resource_rate from cst_resource_costs crc, cst_cost_types cct, mtl_parameters mp where crc.organization_id = mp.organization_id and crc.cost_type_id = decode(mp.primary_cost_method, 1, 1, -- Standard Costing, Frozen Cost Type 2, mp.avg_rates_cost_type_id, -- Average Costing 3, -99, -- Periodic Average 4, -99, -- Periodic Incremental LIFO 5, mp.avg_rates_cost_type_id, -- FIFO Costing 6, mp.avg_rates_cost_type_id -- LIFO Costing ) -- Don't get the Frozen or AvgRates resource costs twice and cct.cost_type_id <> decode(mp.primary_cost_method, 1, 1, -- Standard Costing, Frozen Cost Type 2, mp.avg_rates_cost_type_id, -- Average Costing 3, -99, -- Periodic Average 4, -99, -- Periodic Incremental LIFO 5, mp.avg_rates_cost_type_id, -- FIFO Costing 6, mp.avg_rates_cost_type_id -- LIFO Costing ) 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 2=2 -- p_org_code and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = mp.primary_cost_method ), :p_cost_type ) -- ==================================== -- Find all the resource costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists (select 'x' from cst_resource_costs crc2 where crc2.organization_id = crc.organization_id and crc2.resource_id = crc.resource_id and crc2.cost_type_id = case when mp.primary_cost_method = 1 then cct.cost_type_id when mp.primary_cost_method = 2 and cct.cost_type_id <> 2 then cct.cost_type_id when mp.primary_cost_method = 2 and cct.cost_type_id = 2 then mp.avg_rates_cost_type_id when mp.primary_cost_method = 3 then -99 when mp.primary_cost_method = 4 then -99 when mp.primary_cost_method = 5 and cct.cost_type_id <> 5 then cct.cost_type_id when mp.primary_cost_method = 5 and cct.cost_type_id = 5 then mp.avg_rates_cost_type_id when mp.primary_cost_method = 6 and cct.cost_type_id <> 6 then cct.cost_type_id when mp.primary_cost_method = 6 and cct.cost_type_id = 6 then mp.avg_rates_cost_type_id else cct.cost_type_id end ) group by crc.resource_id, crc.organization_id, crc.last_update_date, crc.cost_type_id, cct.cost_type, crc.resource_rate ) crc -- End revision for version 1.22 -- =========================================== -- WIP_Job Entity, Class and Period joins -- =========================================== where wo.wip_entity_id = wdj.wip_entity_id and wo.organization_id = wdj.organization_id and wo.wip_entity_id = wor.wip_entity_id and wo.organization_id = wor.organization_id and wo.operation_seq_num = wor.operation_seq_num and br.resource_id = wor.resource_id -- Only select non-OSP resources and br.purchase_item_id is null -- Revision for version 1.22 -- and cct.cost_type_id = mp.primary_cost_method -- =========================================== -- Cost Table Joins for version 1.22 -- =========================================== and wor.resource_id = crc.resource_id (+) and wor.organization_id = crc.organization_id (+) -- =========================================== -- Use the joins in wip_operation_resources_v as the same is used in Oracle forms -- =========================================== and wrs.organization_id(+)= wor.organization_id and wrs.wip_entity_id(+)= wor.wip_entity_id and wrs.repetitive_schedule_id(+)= wor.repetitive_schedule_id and wor.wip_entity_id=wt.wip_entity_id(+) and wor.resource_id=wt.resource_id(+) and wor.operation_seq_num=wt.operation_seq_num(+) and wor.resource_seq_num=wt.resource_seq_num(+) and case when wt.wip_entity_id=wor.wip_entity_id and wt.transaction_date<wdj.schedule_close_date+1 then 'Y' when not exists(select null from wip_transactions wt1 where wt1.wip_entity_id=wdj.wip_entity_id) then 'Y' end='Y' and bso.standard_operation_id(+)=wo.standard_operation_id and nvl(bso.operation_type, 1)=1 and bso.line_id is null and 8=8 -- p_resource_code ) res group by res.report_type, res.period_name, res.organization_code, res.organization_id, res.primary_cost_method, res.cost_element_id, res.operation_code, res.account, res.class_code, res.class_type, res.wip_entity_id, res.project_id, res.status_type, res.primary_item_id, -- Revision for version 1.22 res.lot_number, -- Revision for version 1.21 res.creation_date, res.scheduled_start_date, -- End revision for version 1.21 res.date_released, res.date_completed, res.date_closed, res.last_update_date, res.start_quantity, res.quantity_completed, res.quantity_scrapped, res.fg_total_qty, res.department_id, -- Revision for version 1.22 1, -- level_num res.operation_seq_num, -- Revision for version 1.25 res.autocharge_type, res.standard_rate_flag, res.po_currency_code, -- End revision for version 1.25 res.resource_seq_num, res.resource_code, -- Revision for version 1.22 res.description, res.basis_type, res.res_unit_of_measure, -- Revision for version 1.22 res.cost_type, res.resource_rate ) res_sum -- =========================================== -- Account, cost and department joins -- =========================================== where we.wip_entity_id = res_sum.wip_entity_id and msiv_fg.organization_id = res_sum.organization_id and msiv_fg.inventory_item_id = res_sum.primary_item_id -- FG assembly item and msiv_osp.organization_id (+) = res_sum.organization_id and msiv_osp.inventory_item_id (+) = res_sum.purchase_item_id -- OSP item and muomv.uom_code = msiv_fg.primary_uom_code and misv.inventory_item_status_code = msiv_fg.inventory_item_status_code and bd.department_id = res_sum.department_id and cic.organization_id = res_sum.organization_id and cic.inventory_item_id = res_sum.primary_item_id -- Revision for version 1.22 -- and cic.cost_type_id = res_sum.primary_cost_method and gcc.code_combination_id (+) = res_sum.account -- =========================================== -- Lookup Codes -- =========================================== and ml1.lookup_type = 'WIP_CLASS_TYPE' and ml1.lookup_code = res_sum.class_type and ml2.lookup_type = 'WIP_JOB_STATUS' and ml2.lookup_code = res_sum.status_type and ml3.lookup_type = 'CST_BASIS' and ml3.lookup_code = res_sum.basis_type and ml4.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml4.lookup_code = msiv_fg.planning_make_buy_code -- Revision for version 1.25 and ml5.lookup_type (+) = 'BOM_AUTOCHARGE_TYPE' and ml5.lookup_code (+) = res_sum.autocharge_type and ml6.lookup_type = 'SYS_YES_NO' and ml6.lookup_code = res_sum.standard_rate_flag -- End revision for version 1.25 and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv_fg.item_type -- Revision for version 1.8 and fl.lookup_type = 'YES_NO' and fl.lookup_code = cic.rolled_up -- =========================================== -- Organization joins to the HR org model -- =========================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = res_sum.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and haou2.organization_id 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 1=1 -- p_osp_item, p_operating_unit, p_ledger -- -- order by Report Type, Ledger, Operating_Unit, Org_Code, Period_Name, Accounts, WIP_Class, WIP_Job, order by res_sum.report_type, nvl(gl.short_name, gl.name), haou2.name, -- Operating Unit res_sum.organization_code, &segment_columns_grp res_sum.class_code, we.wip_entity_name, msiv_osp.concatenated_segments, (select poh.segment1 from po_headers_all poh where poh.po_header_id = res_sum.po_header_id), -- PO Number decode(res_sum.line_num, 0, null, res_sum.line_num), -- Line Number decode(res_sum.release_num, 0, null, res_sum.release_num), -- PO Release res_sum.operation_seq_num, res_sum.resource_seq_num, res_sum.resource_code Added the cost element, fixed account to pick based on correct cost element. Fix for issue where resource Transactions from OCT-24 are appearing in SEP-24 report Operation Code column is added. Fix for issue where Job Close after accounting period but listed as Variance instead of Valuation Y #,##0.00000;[Red](#,##0.00000) US CAC WIP Resource Efficiency Report your resource efficiency variances for your open and closed WIP jobs. Resource efficiency measures the WIP routing requirements against the actual applied resources. This report replicates the Resource Section for the Oracle Discrete Job Value Report - Standard Costing. If you leave the Cost Type parameter blank the report uses either your Costing Method Cost Type (Standard) or your Costing Method "Avg Rates" Cost Type (Average, FIFO, LIFO) for your resource rates. If the WIP job is open the Report Type column displays "Valuation", as this WIP job and potential material usage variance is still in your WIP inventory balances. If the job has been closed during the reporting period, the Report Type column displays "Variance", as this WIP job was written off on a WIP Job Close Variance transaction during the reporting period. Closed, Pending Close, Cancelled, Complete and Complete No Charges WIP job statuses use the completion quantities. All other WIP jobs use the parameter "Use Completion Quantities" to determine if completion or planned quantities are used for requirements. If you choose Yes for including scrap, this report will automatically include the scrapped quantities as part of the resource quantity requirements. And this report automatically includes WIP jobs which were either open during the reported accounting period or if closed, were closed doing the reporting period. Parameters: ========= Report Option: You can choose to limit the report size with this parameter. The choices are: Open jobs, All jobs or Closed jobs. (mandatory) Period Name: Enter the Period_Name you wish to report for WIP Jobs (mandatory) Cost Type: Enter the resource rates cost type. If left blank, the report uses the Costing Method rates cost type. (optional) Include Scrap Quantities: Include scrap for quantity requirements. (mandatory) Include Unreleased Jobs: Include jobs which have not been released and are not started. (mandatory) Use Completion Quantities: For Released jobs, use the completion quantities for resource variance calculations else use the planned start quantities (mandatory). Category Set 1: Choose any item category to report. Does not limit what is reported. Category Set 2: Choose any item category to report. Does not limit what is reported. Class Code: Specific WIP class code to report (optional). Job Status: Specific WIP job status to report (optional). WIP Job: Specific WIP job number to report (optional). Resource Code: Specific resource code to report (optional). Outside Processing Item: Specific outside processing component to report (optional). Assembly Number: Specific assembly to report (optional). Organization Code: Specific inventory organization you wish to report (optional) Operating Unit: Specific operating unit you wish to report (optional) Ledger: Specific ledger you wish to report (optional) /* +=============================================================================+ -- | Copyright 2009 - 2021 Douglas Volz Consulting, Inc. | -- | All rights reserved. | -- | Permission to use this code is granted provided the original author is | -- | acknowledged | -- +=============================================================================+ -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 28 Jan 2010 Douglas Volz Initial Coding -- | 1.25 12 Dec 2021 Douglas Volz Added auto-charge, std rate and PO Currency Code columns. -- +=============================================================================+*/ Enginatics R12 only &category_columns &segment_columns &segment_columns_grp 1=1 2=2 3=3 4=4 8=8 :p_cost_type :p_include_scrap :p_report_option :p_use_completion_qtys 1 -10 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) Y US Chart of Accounts 2 &segment_columns_grp select 'gcc.'||lower(fifsv.application_column_name)||',' text from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num US Chart of Accounts 3 10 :p_report_option LOV CST WIP Report Option D5CA808540E0FA18E05369FB090580A4 Y select 'Open jobs' value, 'Report only open jobs' description from dual union all select 'All jobs' value, 'Report all jobs' description from dual union all select 'Closed jobs' value, 'Report only closed jobs' description from dual order by 1 All jobs Y US Report Option 4 20 4=4 oap.period_name = :p_period_name LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name select distinct oap.period_name value from org_acct_periods oap, (select max(oap.schedule_close_date) default_date from org_acct_periods oap, org_organization_definitions ood where ood.organization_id = oap.organization_id and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date and rownum = 1 Y US Period Name 5 30 :p_cost_type LOV CST Cost Type (No ICP PII) A486BD05719B2A82E053BB6B6358FC8E Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') order by cct.cost_type select cct.cost_type value, cct.description from cst_cost_types cct, mtl_parameters mp where nvl(cct.disable_date,sysdate + 1) > sysdate and cct.organization_id is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id) and (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or (fnd_profile.value('MFG_ORGANIZATION_ID') is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)) ) and rownum < 2 group by cct.cost_type, cct.description order by cct.cost_type US Cost Type 6 40 :p_include_scrap 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 Y Y US Include Scrap Quantities 7 50 4=4 wdj.status_type<> 1 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 N N Y US Include Unreleased Jobs 8 60 :p_use_completion_qtys 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 N Y US Use Completion Quantities 9 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'msiv_fg') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 10 80 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'msiv_fg') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 11 90 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'msiv_fg') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 12 100 2=2 mp.organization_code = :p_org_code LOV custom Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where 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) 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 order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 13 110 4=4 wdj.class_code = :p_class_code LOV WIP Class Code (org dependent) E7CCB27F39561272E0530100007F62B4 Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US Class Code 14 120 4=4 wdj.status_type=xxen_util.lookup_code(:p_job_status,'WIP_JOB_STATUS',700) LOV WIP Job Status 8E2FF36EDE9579D2E0530100007F1FF2 Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US Job Status 15 130 4=4 (wdj.organization_id, wdj.wip_entity_id) in (select we.organization_id, we.wip_entity_id from wip_entities we where we.wip_entity_name=:p_wip_job) LOV WIP Job 8E2FF36EDEC379D2E0530100007F1FF2 Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null 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 xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US WIP Job 16 140 4=4 br.resource_code=:resource_code LOV BOM Resource Code (org code dependent) E7CCB27F39541272E0530100007F62B4 Y select br.resource_code value, br.description from bom_resources br where (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code US Resource Code 17 150 1=1 msiv_osp.concatenated_segments = :p_osp_item LOV INV OSP Item Number (any OSP item from item master org) B42C590EA41DC91BE0530100007F4403 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id= ( select min(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.outside_operation_flag='Y' order by msiv.concatenated_segments US Outside Processing Item 18 160 3=3 (wdj.organization_id, wdj.primary_item_id) in (select msibk.organization_id, msibk.inventory_item_id from mtl_system_items_b_kfv msibk where msibk.concatenated_segments=:p_assembly_number) LOV INV Item (master, costing enabled only) 91D022B15B12FFB9E053BB6B63587F0B Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US Assembly Number 19 170 1=1 haou2.name = :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id 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) order by hou.name US Operating Unit 20 180 1=1 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Operating Unit :$flex$.ledger Ledger Organization Code :$flex$.ledger Ledger Period Name :$flex$.ledger Ledger WIP Job :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Class Code :$flex$.organization_code Organization Code Period Name :$flex$.organization_code Organization Code Resource Code :$flex$.organization_code Organization Code WIP Job