ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B9847D20A0E4742FE0538931640A6379 AP Supplier Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US 8E2FF36EDEC579D2E0530100007F1FF2 GL Chart of Accounts (inv org restricted) 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)>=sysdate) and fifsv.id_flex_num in (select oav.chart_of_accounts_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by fifsv.id_flex_structure_name AR مخطط الحسابات المتعلق بمؤسسات المخزون المعينة لمسؤولية تسجيل الدخول الحالية D Kontenplan für Inventarorganisationen, die der aktuellen Anmeldeverantwortung zugeordnet sind E Plan de cuentas relacionado con las organizaciones de inventario asignadas a la responsabilidad de inicio de sesión actual F Plan comptable relatif aux organismes d'inventaire affectés à la responsabilité de la connexion actuelle I Piano dei conti relativo alle organizzazioni di inventario assegnate alla responsabilità di login corrente JA 現在のログイン責任者に割り当てられた在庫組織に関連する勘定科目一覧表 KO 현재 로그인 권한에 지정된 재고 조직과 관련된 계정과 목표 PTB Plano de contas relacionado às organizações de inventário atribuídas à responsabilidade de login atual RU План счетов, относящихся к инвентаризационным организациям, на которые возложена ответственность за текущий вход в систему S Kontokarta relaterade till lagerorganisationer som tilldelats det nuvarande inloggningsansvaret TR Mevcut oturum açma sorumluluğuna atanan envanter organizasyonlarıyla ilgili hesap planı US Chart of accounts related to inventory organizations assigned to the current login responsibility 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 8E2FF36EDEF379D2E0530100007F1FF2 INV Item (master) 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) ) order by msiv.concatenated_segments US Any item from item master org, including non costing enabled 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) 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 (:$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 AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 BA79D292B8FC0AD6E053BB6B63589012 select plla.ledger, plla.operating_unit, mp.organization_code org_code, aps.vendor_name supplier, hr.full_name buyer, msiv.concatenated_segments item_number, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, misv.inventory_item_status_code item_status, &category_columns xxen_util.meaning(pda.destination_type_code,'DESTINATION TYPE',201) po_destination_type, cic.resource_code osp_resource, pla.vendor_product_num supplier_item, round(msiv.list_price_per_unit,5) target_or_list_price, plla.pha_po_number po_number, pla.line_num po_line, coalesce( xxen_util.meaning(plla.closed_code,'DOCUMENT STATE',201), xxen_util.meaning(pla.closed_code,'DOCUMENT STATE',201), xxen_util.meaning(plla.pha_closed_code,'DOCUMENT STATE',201) ) po_line_status, ppa.segment1 project_number, ppa.name project_name, plla.creation_date creation_date, plla.promised_date promise_date, plla.need_by_date need_by_date, to_char(pra.release_num) po_release, pra.release_date release_date, (select max(ms.expected_delivery_date) from mtl_supply ms where pda.po_distribution_id=ms.po_distribution_id and ms.supply_type_code in ('PO','RECEIVING','SHIPMENT') and ms.destination_type_code in ('INVENTORY','SHOP FLOOR')) expected_receipt_date, xxen_util.meaning(decode(plla.inspection_required_flag,'Y','Y'),'YES_NO',0) inspection_required, muomv_po.uom_code po_uom, nvl(plla.gl_currency_code,plla.gl_currency_code) po_currency_code, nvl(plla.price_override,pla.unit_price) po_unit_price, decode(plla.match_option,'P',trunc(nvl(pda.rate_date,plla.creation_date)),:conversion_date) currency_rate_date, round(decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate),6) po_exchange_rate, plla.gl_currency_code, round(decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate) * nvl(plla.price_override,pla.unit_price),6) converted_po_unit_price, mucv.conversion_rate po_uom_conversion_rate, round(decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate) * nvl(plla.price_override,pla.unit_price) * mucv.conversion_rate,6) converted_po_at_primary_uom, muomv_msi.uom_code uom_code, round(nvl(cic.unburdened_cost,0),5) unburdened_unit_cost, round(decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate) * nvl(plla.price_override,pla.unit_price) - nvl(cic.unburdened_cost,0),5) unit_cost_difference, round((decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate) * nvl(plla.price_override,pla.unit_price) - nvl(cic.unburdened_cost,0)) * nvl(plla.quantity,0),2) extended_cost_difference, case when round(decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate * nvl(plla.price_override, pla.unit_price)) - nvl(cic.unburdened_cost,0),5) = 0 then 0 --difference = 0 when round(nvl(cic.unburdened_cost,0),5) = 0 then 100 --item cost = 0 when round(decode(plla.match_option,'P',nvl(pda.rate,1),gdr.conversion_rate) * nvl(plla.price_override, pla.unit_price),5) = 0 then -100 --PO unit price = 0 else round((decode(plla.match_option,'P', nvl(pda.rate,1),gdr.conversion_rate) * nvl(plla.price_override, pla.unit_price) - nvl(cic.unburdened_cost,0)) / xxen_util.zero_to_null(cic.unburdened_cost) * 100,2) --PO Price - item cost / item cost end percent_difference, (select cct.cost_type from cst_cost_types cct where mp.primary_cost_method=cct.cost_type_id) current_cost_type, round(cic.material_cost,5) material_cost, round(cic.material_overhead_cost,5) material_overhead_cost, round(cic.resource_cost,5) resource_cost, round(cic.outside_processing_cost,5) outside_processing_cost, round(cic.overhead_cost,5) overhead_cost, round(cic.item_cost,5) current_item_cost, (select round(cic.material_cost-cic.tl_material_overhead,5) from cst_item_costs cic where pla.item_id=cic.inventory_item_id and plla.ship_to_organization_id=cic.organization_id and cic.cost_type_id=:comparison_cost_type_id) comparison_material_cost, mmt.receipt_qty average_receipt_quantity, round(mmt.receipt_amount,2) average_receipt_amount, round(mmt.receipt_amount/xxen_util.zero_to_null(mmt.receipt_qty),5) average_receipt_cost, plla.quantity quantity_ordered, plla.quantity_received quantity_received, plla.quantity_billed quantity_invoiced, round(nvl(pda.rate,1) * plla.price_override * plla.quantity * mucv.conversion_rate,2) total_po_amount, &sla_columns &segment_columns pla.creation_date po_line_creation_date from po_lines_all pla, ( select gl.ledger_id, gl.name ledger, haouv.name operating_unit, gl.currency_code gl_currency_code, pha.currency_code pha_currency_code, pha.vendor_id pha_vendor_id, pha.agent_id pha_agent_id, pha.closed_code pha_closed_code, pha.segment1 pha_po_number, plla.* from po_line_locations_all plla, po_headers_all pha, org_organization_definitions ood, hr_all_organization_units_vl haouv, gl_ledgers gl where 1=1 and (nvl(fnd_profile.value('XXEN_REPORT_USE_LEDGER_SECURITY'),'N')='N' or ood.set_of_books_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 (nvl(fnd_profile.value('XXEN_REPORT_USE_OPERATING_UNIT_SECURITY'),'N')='N' or ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)) and plla.ship_to_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 pha.closed_date is null and plla.closed_date is null and plla.po_header_id=pha.po_header_id and plla.ship_to_organization_id=ood.organization_id and ood.operating_unit=haouv.organization_id and ood.set_of_books_id=gl.ledger_id ) plla, po_distributions_all pda, &sla_tables po_releases_all pra, mtl_parameters mp, ap_suppliers aps, mtl_system_items_vl msiv, mtl_item_status_vl misv, mtl_uom_conversions_view mucv, mtl_units_of_measure_vl muomv_po, mtl_units_of_measure_vl muomv_msi, wip_entities we, pa_projects_all ppa, hr_employees hr, (select gdr.* from gl_daily_rates gdr where gdr.conversion_date=:conversion_date and gdr.conversion_type=:conversion_type) gdr, --Set of currency rates to translate the PO Price into the GL Currency gl_code_combinations gcc1, gl_code_combinations gcc2, gl_code_combinations gcc3, ( --Item or resource cost select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, null resource_code, -999 resource_id, cic.material_cost, cic.material_overhead_cost, cic.resource_cost, cic.outside_processing_cost, cic.overhead_cost, cic.item_cost, cic.unburdened_cost from cst_item_costs cic union all select br.organization_id, br.purchase_item_id, crc.cost_type_id, br.resource_code, br.resource_id, 0 material_cost, 0 material_overhead_cost, 0 resource_cost, nvl(crc.resource_rate,0) outside_processing_cost, 0 overhead_cost, nvl(crc.resource_rate,0) item_cost, nvl(crc.resource_rate,0) unburdened_cost from bom_resources br, cst_resource_costs crc where br.cost_element_id=4 and br.resource_id=crc.resource_id ) cic, ( --Average PO receipt cost over a date range select distinct mmt.organization_id, mmt.inventory_item_id, sum(mmt.primary_quantity) over (partition by mmt.organization_id, mmt.inventory_item_id) receipt_qty, sum(mmt.transaction_cost*mmt.primary_quantity) over (partition by mmt.organization_id, mmt.inventory_item_id) receipt_amount from mtl_material_transactions mmt where mmt.transaction_date>=:transaction_from and mmt.transaction_date<:transaction_date_to+1 and mmt.transaction_source_type_id=1 and --purchase orders mmt.transaction_cost<>0 ) mmt where 2=2 and pla.po_line_id=plla.po_line_id and plla.line_location_id=pda.line_location_id and pda.destination_type_code in ('SHOP FLOOR','INVENTORY') and pla.closed_date is null and plla.po_release_id=pra.po_release_id(+) and plla.ship_to_organization_id=mp.organization_id and plla.pha_vendor_id=aps.vendor_id and pla.item_id=msiv.inventory_item_id and plla.ship_to_organization_id=msiv.organization_id and msiv.inventory_item_status_code=misv.inventory_item_status_code and pla.unit_meas_lookup_code=mucv.unit_of_measure and pla.item_id=mucv.inventory_item_id and plla.ship_to_organization_id=mucv.organization_id and mucv.uom_code=muomv_po.uom_code and msiv.primary_uom_code=muomv_msi.uom_code and pda.wip_entity_id=we.wip_entity_id(+) and pda.project_id=ppa.project_id(+) and plla.pha_agent_id=hr.employee_id(+) and plla.pha_currency_code=gdr.from_currency(+) and plla.gl_currency_code=gdr.to_currency(+) and &sla_join pda.variance_account_id=gcc2.code_combination_id(+) and pda.accrual_account_id=gcc3.code_combination_id(+) and plla.ship_to_organization_id=cic.organization_id and pla.item_id=cic.inventory_item_id and mp.primary_cost_method=cic.cost_type_id and nvl(pda.bom_resource_id,-999)=nvl(cic.resource_id,-999) and decode(msiv.inventory_asset_flag,'Y',msiv.organization_id)=mmt.organization_id(+) and decode(msiv.inventory_asset_flag,'Y',msiv.inventory_item_id)=mmt.inventory_item_id(+) Restructured for better performance and added SLA accounting. Y US CAC Open Purchase Orders Report to show open purchase orders and related information. This report will convert any foreign currency purchases into the currency of the general ledger (defaulted from the inventory organization for this session). Use the To and From Transaction Date parameters to create an average receipt cost and use the Comparison Cost Type parameter to show a comparison amounts from another cost type. Parameters: =========== Comparison Cost Type: enter the cost type for a comparison against the purchase order prices (optional). Defaulted from your Costing Method. Transaction Date From: starting transaction date for averaging the purchase order receipts (mandatory). Use these averages for comparing to the PO unit prices. Transaction Date To: ending transaction date for averaging the purchase order receipts (mandatory). Use these averages for comparing to the PO unit prices. Currency Conversion Date: enter the currency conversion date to use for converting foreign currency purchases into the currency of the general ledger (mandatory). Currency Conversion Type: enter the currency conversion type to use for converting foreign currency purchases into the currency of hhe general ledger (mandatory). Supplier Name: specific vendor or supplier you wish to report (optional). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2020 - 2023 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this -- | permission. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: open_po_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 09 Sep 2020 Douglas Volz Initial Coding based on Purchase Price -- | variance report, xxx_ppv_lot_rept.sql -- | 1.1 10 Sep 2020 Douglas Volz Added inspection flag. -- | 1.2 01 Dec 2020 Douglas Volz Added variance and charge accounts -- | 1.3 20 Dec 2020 Douglas Volz Added promise date, Need By Date, project, -- | Expected Receipt Date, Target Price (PO List Price), -- | Customer Name and difference columns. -- | And added Minimum Cost Difference parameter. -- | 1.4 03 Feb 2021 Douglas Volz Merged OSP with stock purchase orders. -- | 1.5 05 Feb 2021 Douglas Volz Added PO averages and item cost information. -- | 1.6 07 Jul 2022 Douglas Volz Add multi-language item status. -- | 1.7 28 Nov 2023 Andy Haack Remove tabs, add org access controls, fix for G/L Daily Rates, outer joins. -- | 1.8 15 Dec 2023 Douglas Volz Setting the comparison cost type as mandatory, to -- | prevent a multiple rows error for the comparison cost type. -- +=============================================================================+*/ Enginatics &category_columns &segment_columns &sla_columns &sla_join &sla_tables 1=1 2=2 :comparison_cost_type_id :conversion_date :conversion_type :transaction_date_to :transaction_from 1 -10 &segment_columns with x as ( select fifsv.application_column_name, fifsv.form_left_prompt 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 ) select 'gcc1.'||lower(x.application_column_name)||' "Charge '||substr(x.form_left_prompt,1,21)||'",' text from x union all select 'gcc2.'||lower(x.application_column_name)||' "IPV '||substr(x.form_left_prompt,1,21)||'",' text from x union all select 'gcc3.'||lower(x.application_column_name)||' "Accrual '||substr(x.form_left_prompt,1,21)||'",' text from x LOV GL Chart of Accounts (inv org restricted) 8E2FF36EDEC579D2E0530100007F1FF2 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)>=sysdate) and fifsv.id_flex_num in (select oav.chart_of_accounts_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by 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 in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and fifsv.id_flex_num in (select oav.chart_of_accounts_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 fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id = nvl(fnd_profile.value('MFG_ORGANIZATION_ID'),ood.organization_id)) US Chart of Accounts 2 10 :comparison_cost_type_id LOV Oracle CST_SRS_INV_COST_TYPE Y select cct.cost_type_id id, cct.cost_type value, cct.description description from cst_cost_types cct,mtl_parameters mp where nvl(cct.organization_id,fnd_profile.value('MFG_ORGANIZATION_ID'))= fnd_profile.value('MFG_ORGANIZATION_ID') and nvl(cct.disable_date,sysdate+1)>sysdate and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and ((cct.cost_type_id=mp.primary_cost_method) or (cct.cost_type_id) not in (1,2,5,6)) order by 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 Y US Comparison Cost Type 3 20 :transaction_from Date select max(oap.period_start_date) from org_acct_periods oap where oap.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_id<>mp.master_organization_id) Y US Transaction Date From 4 30 :transaction_date_to Date select max(oap.schedule_close_date) from org_acct_periods oap where oap.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_id<>mp.master_organization_id) Y US Transaction Date To 5 40 :conversion_date Date select oap.schedule_close_date value, oap.period_name description from apps.org_acct_periods oap where oap.acct_period_id = (select max(oap2.acct_period_id) from org_acct_periods oap2 where oap2.schedule_close_date = (select max(oap3.schedule_close_date) from org_acct_periods oap3, org_organization_definitions ood where ood.organization_id = oap3.organization_id and nvl(ood.disable_date,sysdate + 1) > sysdate ) ) Y US Currency Conversion Date 6 50 :conversion_type LOV custom Y select gdct.conversion_type id, gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 Corporate Y US Currency Conversion Type 7 60 2=2 aps.vendor_name=:supplier_name LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier Name 8 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') 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 9 80 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') 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 10 90 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') 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 11 95 &sla_tables ( select distinct xah.ledger_id, xah.gl_transfer_status_code, xdl.source_distribution_id_num_1, xdl.accounting_line_code, xal.code_combination_id, sum(nvl(xal.entered_dr,0)-nvl(xal.entered_cr,0)) over (partition by xah.ledger_id, xah.gl_transfer_status_code, xdl.source_distribution_id_num_1, xdl.accounting_line_code, xal.code_combination_id) entered_amount, sum(nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0)) over (partition by xah.ledger_id, xah.gl_transfer_status_code, xdl.source_distribution_id_num_1, xdl.accounting_line_code, xal.code_combination_id) accounted_amount from xla_distribution_links xdl, xla_ae_headers xah, xla_ae_lines xal where xdl.application_id=201 and xdl.source_distribution_type='PO_DISTRIBUTIONS_ALL' and xdl.application_id=xah.application_id(+) and xdl.ae_header_id=xah.ae_header_id(+) and xdl.application_id=xal.application_id(+) and xdl.ae_header_id=xal.ae_header_id(+) and xdl.ae_line_num=xal.ae_line_num(+) ) x, 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 Y US Show SLA 12 &sla_columns xxen_util.meaning(x.gl_transfer_status_code,'XLA_ACCT_TRANSFER',602) sla_gl_tfr_status, x.accounting_line_code accounting_line_code, nvl2(x.gl_transfer_status_code,x.entered_amount,null) sla_entered_amount, nvl2(x.gl_transfer_status_code,x.accounted_amount,null) sla_accounted_amount, Y US Show SLA 13 &sla_join pda.code_combination_id=gcc1.code_combination_id(+) and N US Show SLA 14 &sla_join plla.ledger_id=x.ledger_id(+) and pda.po_distribution_id=x.source_distribution_id_num_1(+) and nvl(pda.code_combination_id,x.code_combination_id)=gcc1.code_combination_id(+) and Y US Show SLA 15 100 2=2 msiv.concatenated_segments=:item_number LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 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) ) order by msiv.concatenated_segments US Item Number 16 110 2=2 mp.organization_code=:organization_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 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 (:$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 17 120 1=1 hou.name=: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 18 130 1=1 gl.name=: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$.operating_unit Operating Unit Organization Code :$flex$.operating_unit Operating Unit Supplier Name :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code Supplier Name 9C1B8BA36C570AB7D5A2659483C9B558 By Org by Destination Type Y ENGINATICS 54 AVERAGE_RECEIPT_AMOUNT 55 AVERAGE_RECEIPT_COST 53 AVERAGE_RECEIPT_QUANTITY 72 Accrual Account 70 Accrual Company 71 Accrual Department 74 Accrual Product 73 Accrual Sub-Account 5 BUYER 52 COMPARISON_MATERIAL_COST 39 CONVERTED_PO_AT_PRIMARY_UOM 37 CONVERTED_PO_UNIT_PRICE 24 CREATION_DATE 34 CURRENCY_RATE_DATE 45 CURRENT_COST_TYPE 51 CURRENT_ITEM_COST 62 Charge Account 60 Charge Company 61 Charge Department 64 Charge Product 63 Charge Sub-Account 13 Class 29 EXPECTED_RECEIPT_DATE 43 EXTENDED_COST_DIFFERENCE 12 Family 36 GL_CURRENCY_CODE 30 INSPECTION_REQUIRED 67 IPV Account 65 IPV Company 66 IPV Department 69 IPV Product 68 IPV Sub-Account 7 ITEM_DESCRIPTION 6 ITEM_NUMBER 9 ITEM_STATUS 8 ITEM_TYPE 14 Inv.Items Description 1 LEDGER 46 MATERIAL_COST 47 MATERIAL_OVERHEAD_COST 26 NEED_BY_DATE 2 OPERATING_UNIT 3 ORG_CODE 16 OSP_RESOURCE 49 OUTSIDE_PROCESSING_COST 50 OVERHEAD_COST 44 PERCENT_DIFFERENCE 32 PO_CURRENCY_CODE 15 PO_DESTINATION_TYPE 35 PO_EXCHANGE_RATE 20 PO_LINE 75 PO_LINE_CREATION_DATE 21 PO_LINE_STATUS 19 PO_NUMBER 27 PO_RELEASE 33 PO_UNIT_PRICE 31 PO_UOM 38 PO_UOM_CONVERSION_RATE 23 PROJECT_NAME 22 PROJECT_NUMBER 25 PROMISE_DATE 10 Product Cat 11 Product Description 58 QUANTITY_INVOICED 56 QUANTITY_ORDERED 57 QUANTITY_RECEIVED 28 RELEASE_DATE 48 RESOURCE_COST 4 SUPPLIER 17 SUPPLIER_ITEM 18 TARGET_OR_LIST_PRICE 59 TOTAL_PO_AMOUNT 41 UNBURDENED_UNIT_COST 42 UNIT_COST_DIFFERENCE 40 UOM_CODE EXTENDED_COST_DIFFERENCE VALUE 2 SUM LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 PO_DESTINATION_TYPE ROW 4 TOTAL_PO_AMOUNT VALUE 1 SUM S Site 9C1B8BA36C570AB7D5A2659483C9B558