ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 8E2FF36EDEB279D2E0530100007F1FF2 INV Organization Code Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code AR جميع أكواد تنظيم المخزون النشط باستثناء المؤسسات الرئيسية ، مقيدة بعرض الوصول إلى مؤسسة المستخدم الحالي D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen, beschränkt auf die Org-Zugriffsansicht des aktuellen Benutzers E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras, restringidos a la vista de acceso a la organización del usuario actual F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses, limités à la vue d'accès à l'organisation de l'utilisateur actuel I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master, limitati alla vista di accesso all'org dell'utente corrente JA すべてのアクティブなインベントリの組織コードは、マスター組織を除いて、現在のユーザーの組織のアクセスビューに制限されます。 KO 현재 사용자의 조직 액세스보기로 제한되는 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre, restritos à visão de acesso org do usuário atual RU Все активные инвентаризационные организационные коды, исключая основные организации, ограниченные текущим представлением доступа пользователя к оргструктуре. S Alla aktiva lagerorganisationskoder exklusive huvudorganisationer, begränsade till den aktuella användarens organisationsåtkomstvy TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları, mevcut kullanıcının organizasyon erişim görünümü ile sınırlıdır US All active inventory organization codes excluding master organizations, restricted to the current user's org access view ZHS 所有活动的清单组织代码，不包括主组织，仅限于当前用户的组织访问视图。 8E2FF36EDEE779D2E0530100007F1FF2 ONT Order Number Y Y select ooha.order_number value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and ooha.org_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 ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc Fixed bug in operating unit access restriction when the default operating unit is set to something else US 3542C27D23EA0E62E0638A00890AD2C8Y with perpetual_qry_1 as ( select x.* from ( select /*+ leading(haou,crcml,oola,ooha,cce,mta) use_nl(haou,crcml, oola, ooha) index(crcml cst_rev_cogs_match_lines_n2) */ crcml.operating_unit_id, haou.name operating_unit, gl.name ledger, gl.currency_code currency, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id, crcml.inventory_item_id, crcml.unit_cost, crcml.unit_material_cost, crcml.unit_moh_cost, crcml.unit_op_cost, crcml.unit_resource_cost, crcml.unit_overhead_cost, sum(decode(mta.accounting_line_type, 35, mta.base_transaction_value,0)) cogs_balance, last_value(sum(decode(mta.accounting_line_type, 35, mta.base_transaction_value,0))) -- CST_ACCOUNTING_LINE_TYPE 35 = Cost of Goods Sold over ( partition by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id order by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id ) total_cogs_balance, sum(decode(mta.accounting_line_type, 36, mta.base_transaction_value,0)) def_cogs_balance, last_value(sum(decode(mta.accounting_line_type, 36, mta.base_transaction_value,0))) -- CST_ACCOUNTING_LINE_TYPE 36 = Deferred Cost of Goods Sold over( partition by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id order by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id ) total_def_cogs_balance from cst_revenue_cogs_match_lines crcml, cst_cogs_events cce, oe_order_lines_all oola, oe_order_headers_all ooha, mtl_transaction_accounts mta, gl_ledgers gl, hr_all_organization_units haou where 1=1 and crcml.ledger_id = :p_ledger_id and crcml.pac_cost_type_id is null and trunc(cce.event_date) <= :p_gps_end_dt and trunc(cce.event_date) >= :p_gps_start_dt and cce.cogs_om_line_id = crcml.cogs_om_line_id and gl.ledger_id = crcml.ledger_id and oola.header_id = ooha.header_id and oola.line_id = crcml.cogs_om_line_id and mta.transaction_id (+) = cce.mmt_transaction_id and haou.organization_id (+) = crcml.operating_unit_id group by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id, crcml.inventory_item_id, crcml.unit_cost, crcml.unit_material_cost, crcml.unit_moh_cost, crcml.unit_op_cost, crcml.unit_resource_cost, crcml.unit_overhead_cost ) x ), perpetual_qry_2 as ( select /*+ index(rctla ra_customer_trx_lines_n9) leading (pq1, rctla) use_nl(pq1, rctla) */ pq1.operating_unit_id, pq1.operating_unit, pq1.ledger, pq1.currency ledger_currency, pq1.order_number order_number, pq1.booked_date order_date, substrb(hp.party_name,1,50) customer_name, pq1.transactional_curr_code order_currency, pq1.line_number sales_order_line, pq1.sales_order_issue_date, pq1.revenue_om_line_id sales_order_line_id, pq1.item_type_code item_type_code, msiv.concatenated_segments item, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type, msiv.inventory_item_id, mp.organization_code, mp.organization_id, rctla.line_number invoice_line, rctla.unit_selling_price, rctla.customer_trx_line_id, rctla.customer_trx_id, sum(cce.event_quantity) total_line_quantity, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_cost) / sum(cce.event_quantity))item_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_material_cost) / sum(cce.event_quantity))material_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_moh_cost) / sum(cce.event_quantity))material_oh_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_op_cost) / sum(cce.event_quantity))osp_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_resource_cost) / sum(cce.event_quantity))resource_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_overhead_cost) / sum(cce.event_quantity))overhead_cost, pq1.uom, pq1.cogs_balance earned_cogs, pq1.total_cogs_balance total_earned_cogs, pq1.def_cogs_balance deferred_cogs, pq1.total_def_cogs_balance total_deferred_cogs, gcck_cogs.concatenated_segments cogs_account, gcck_dcogs.concatenated_segments deferred_cogs_account, pq1.cogs_om_line_id cogs_om_line_id, gcck_cogs.code_combination_id cogs_account_ccid, gcck_cogs.chart_of_accounts_id, cce.event_date gl_date from perpetual_qry_1 pq1, cst_revenue_cogs_match_lines crcml, mtl_system_items_vl msiv, mtl_parameters mp, gl_code_combinations_kfv gcck_cogs, gl_code_combinations_kfv gcck_dcogs, ra_customer_trx_lines_all rctla, hz_cust_accounts hca, hz_parties hp, cst_cogs_events cce where msiv.inventory_item_id = pq1.inventory_item_id and msiv.organization_id = pq1.organization_id and mp.organization_id = pq1.organization_id and gcck_cogs.code_combination_id = crcml.cogs_acct_id and gcck_dcogs.code_combination_id = pq1.deferred_cogs_acct_id and rctla.line_type (+) = 'LINE' and rctla.interface_line_context (+) = 'ORDER ENTRY' and rctla.interface_line_attribute6 (+) = to_char(pq1.revenue_om_line_id) and rctla.sales_order (+) = to_char(pq1.order_number) and pq1.sold_to_org_id = hca.cust_account_id (+)and hca.party_id = hp.party_id and cce.cogs_om_line_id = pq1.cogs_om_line_id and cce.event_type in (1,2) and trunc(cce.event_date) <= :p_gps_end_dt and trunc(cce.event_date) >= :p_gps_start_dt and crcml.cogs_om_line_id = pq1.cogs_om_line_id and crcml.pac_cost_type_id is null and ( :p_all_lines = 'Y' or pq1.def_cogs_balance not between -1*:p_amt_tolerance and :p_amt_tolerance) group by pq1.operating_unit_id, pq1.operating_unit, pq1.ledger, pq1.currency, pq1.order_number, pq1.booked_date, substrb(hp.party_name,1,50), pq1.transactional_curr_code, pq1.line_number, pq1.sales_order_issue_date, pq1.revenue_om_line_id, pq1.item_type_code, pq1.uom, msiv.concatenated_segments, msiv.description, msiv.item_type, msiv.inventory_item_id, mp.organization_code, mp.organization_id, rctla.line_number, rctla.unit_selling_price, rctla.customer_trx_line_id, rctla.customer_trx_id, pq1.cogs_balance, pq1.total_cogs_balance, pq1.def_cogs_balance, pq1.total_def_cogs_balance, gcck_cogs.concatenated_segments, gcck_dcogs.concatenated_segments, pq1.cogs_om_line_id, gcck_cogs.code_combination_id, gcck_cogs.chart_of_accounts_id, cce.event_date ), perpetual_qry as ( select pq2.operating_unit_id, pq2.operating_unit, pq2.ledger, pq2.ledger_currency, pq2.order_number, pq2.order_date, pq2.customer_name, pq2.order_currency, pq2.sales_order_line, pq2.sales_order_issue_date, pq2.sales_order_line_id, pq2.item_type_code, pq2.item, pq2.item_description, pq2.user_item_type, pq2.inventory_item_id, pq2.organization_code, pq2.organization_id, pq2.invoice_line, pq2.unit_selling_price, pq2.customer_trx_line_id, pq2.customer_trx_id, pq2.total_line_quantity, pq2.item_cost, pq2.material_cost, pq2.material_oh_cost, pq2.osp_cost, pq2.resource_cost, pq2.overhead_cost, pq2.uom, pq2.earned_cogs, case when pq2.item_type_code = 'INCLUDED' then (select sum(pq2_1.earned_cogs) from perpetual_qry_2 pq2_1 where pq2_1.operating_unit_id = pq2.operating_unit_id and pq2_1.order_number = pq2.order_number and pq2_1.sales_order_line = pq2.sales_order_line and pq2_1.sales_order_line_id = pq2.sales_order_line_id and pq2_1.item_type_code = 'INCLUDED' and pq2_1.customer_trx_line_id = pq2.customer_trx_line_id and pq2_1.customer_trx_id = pq2.customer_trx_id ) else pq2.total_earned_cogs end total_earned_cogs, pq2.deferred_cogs, pq2.total_deferred_cogs, pq2.cogs_account, pq2.deferred_cogs_account, pq2.cogs_om_line_id, pq2.cogs_account_ccid, pq2.chart_of_accounts_id, pq2.gl_date from perpetual_qry_2 pq2 ), perpetual_lines_qry as ( select /*+ leading(pq2,rctla) index(rctla ra_customer_trx_lines_n9)*/ rctla.interface_line_attribute1, rctla.interface_line_attribute6, rcta.trx_number, rctla.line_number, rctla.customer_trx_id, row_number() over (partition by rctla.sales_order,rctla.interface_line_attribute6,rctla.interface_line_context,rctla.line_type order by rctla.customer_trx_id) rctla_rank from ra_customer_trx_lines_all rctla, ra_customer_trx_all rcta, perpetual_qry pq where rctla.interface_line_context='ORDER ENTRY' and rctla.line_type ='LINE' and rctla.customer_trx_id = rcta.customer_trx_id and rctla.sales_order = to_char(pq.order_number) and rctla.interface_line_attribute6 = to_char(pq.sales_order_line_id) ) -- -- Main Query Starts Here -- select x.ledger, x.operating_unit, x.order_number, x.order_date, x.customer, x.order_currency, x.order_line, x.order_quantity, x.uom, x.sales_order_issue_date, x.invoice_number, x.quantity_invoiced, x.invoice_line, x.item, x.item_description, &category_columns x.user_item_type, x.organization_code, (select mp.organization_code from mtl_parameters mp,oe_order_lines_all oola where oola.line_id=x.cogs_om_line_id and oola.ship_from_org_id=mp.organization_id)ship_from_warehouse, x.ledger_currency, :p_period_name period, x.unit_selling_price, x.item_cost, x.material_cost, x.material_oh_cost, x.osp_cost, x.resource_cost, x.overhead_cost, &lp_cost_type_col x.earned_revenue, x.unearned_revenue, x.unbilled_revenue, case when x.earned_revenue is not null and x.unearned_revenue is not null and x.unbilled_revenue is not null then x.earned_revenue + x.unearned_revenue + x.unbilled_revenue end total_revenue, case when x.earned_revenue is not null and x.unearned_revenue is not null and x.unbilled_revenue is not null then case when x.earned_revenue + x.unearned_revenue + x.unbilled_revenue = 0 then 100 else x.earned_revenue/(x.earned_revenue + x.unearned_revenue + x.unbilled_revenue)*100 end end revenue_percent, x.earned_cogs, x.deferred_cogs, x.earned_cogs+x.deferred_cogs total_cogs, (select actual_shipment_date from oe_order_lines_all where line_id = x.sales_order_line_id) so_line_closed_date, x.gl_date, case when x.earned_cogs is not null and x.deferred_cogs is not null then case when x.earned_cogs + x.deferred_cogs = 0 then 100 else x.earned_cogs/(x.earned_cogs + x.deferred_cogs)*100 end end cogs_percent, x.cogs_account, x.deferred_cogs_account, case when x.revenue_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('seg','SQLGL','GL#',x.chart_of_accounts_id,NULL,x.revenue_ccid,'ALL','Y','VALUE') end revenue_account_segments, case when x.unearn_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('seg','SQLGL','GL#',x.chart_of_accounts_id,NULL,x.unearn_ccid,'ALL','Y','VALUE') end unearned_account_segments, case when x.unbill_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('seg','SQLGL','GL#',x.chart_of_accounts_id,NULL,x.unbill_ccid,'ALL','Y','VALUE') end unbilled_account_segments, &lp_cogs_account_segments &lp_rev_account_segments x.cogs_om_line_id from ( select /*+ leading(pq) no_merge(rctlgda) index(rctlgda ra_cust_trx_line_gl_dist_n1)*/ pq.ledger, pq.ledger_currency, pq.operating_unit, pq.order_number, pq.order_date, pq.customer_name customer, pq.order_currency, pq.sales_order_line order_line, decode(rcta.trx_number,min(plq.trx_number),decode(pq.invoice_line,min(plq.min_inv_line),pq.total_line_quantity)) order_quantity, pq.uom, pq.sales_order_issue_date, rcta.trx_number invoice_number, rctla.quantity_invoiced, pq.invoice_line, pq.unit_selling_price, pq.item_cost, pq.material_cost, pq.material_oh_cost, pq.osp_cost, pq.resource_cost, pq.overhead_cost, pq.item, pq.item_description, pq.user_item_type, pq.inventory_item_id, pq.organization_code, pq.organization_id, round(sum(decode(rctlgda.account_class,'UNEARN',0,'UNBILL',0, rctlgda.acctd_amount))*decode(pq.earned_cogs, pq.total_earned_cogs,1,pq.earned_cogs/decode(pq.total_earned_cogs,0,1,pq.total_earned_cogs)),2) earned_revenue, round(sum(decode(rctlgda.account_class,'REV', 0,'UNBILL',0, rctlgda.acctd_amount))*decode(pq.deferred_cogs, pq.total_deferred_cogs,1,pq.deferred_cogs/decode(pq.total_deferred_cogs,0,1,pq.total_deferred_cogs)),2) unearned_revenue, round(sum(decode(rctlgda.account_class,'REV', 0,'UNEARN',0, rctlgda.acctd_amount))*decode(pq.deferred_cogs, pq.total_deferred_cogs,1,pq.deferred_cogs/decode(pq.total_deferred_cogs,0,1,pq.total_deferred_cogs)),2) unbilled_revenue, decode(rcta.trx_number,min(plq.trx_number),decode(pq.invoice_line,min(plq.min_inv_line),pq.earned_cogs)) earned_cogs, decode(rcta.trx_number,min(plq.trx_number),decode(pq.invoice_line,min(plq.min_inv_line),pq.deferred_cogs)) deferred_cogs, pq.cogs_account, pq.deferred_cogs_account, min(decode(rctlgda.account_class,'REV',rctlgda.code_combination_id)) revenue_ccid, min(decode(rctlgda.account_class,'UNEARN',rctlgda.code_combination_id)) unearn_ccid, min(decode(rctlgda.account_class,'UNBILL',rctlgda.code_combination_id)) unbill_ccid, plq.interface_line_attribute6, pq.sales_order_line_id, pq.cogs_om_line_id, pq.cogs_account_ccid, pq.chart_of_accounts_id, rctlgda.gl_date from perpetual_qry pq, ra_cust_trx_line_gl_dist_all rctlgda, ra_customer_trx_lines_all rctla, ra_customer_trx_all rcta, ( select plq.interface_line_attribute1, plq.interface_line_attribute6, plq.trx_number, min(plq.line_number) min_inv_line from perpetual_lines_qry plq where plq.rctla_rank=1 group by plq.interface_line_attribute1, plq.interface_line_attribute6, plq.trx_number ) plq where (pq.customer_trx_line_id = rctla.customer_trx_line_id or pq.customer_trx_line_id = rctla.previous_customer_trx_line_id) and pq.customer_trx_id = rcta.customer_trx_id and rctlgda.customer_trx_line_id = rctla.customer_trx_line_id and rctlgda.account_set_flag = 'N' and rctlgda.account_class in ('REV', 'UNEARN', 'UNBILL') and trunc(rctlgda.gl_date) <= trunc(nvl(:p_gps_end_dt,rctlgda.gl_date)) and trunc(rctlgda.gl_date) >= trunc(nvl(:p_gps_start_dt,rctlgda.gl_date)) and to_char(pq.sales_order_line_id)=rctla.interface_line_attribute6 and to_char(pq.sales_order_line_id)=plq.interface_line_attribute6 group by pq.ledger, pq.ledger_currency, pq.operating_unit, pq.order_number, pq.order_date, pq.customer_name, pq.order_currency, pq.sales_order_line, pq.sales_order_issue_date, rcta.trx_number, rctla.quantity_invoiced, pq.invoice_line, pq.unit_selling_price, pq.item_cost, pq.material_cost, pq.material_oh_cost, pq.osp_cost, pq.resource_cost, pq.overhead_cost, pq.item, pq.item_description, pq.user_item_type, pq.inventory_item_id, pq.organization_code, pq.organization_id, pq.total_line_quantity, pq.uom, pq.earned_cogs, pq.total_earned_cogs, pq.deferred_cogs, pq.total_deferred_cogs, pq.cogs_account, pq.deferred_cogs_account, plq.interface_line_attribute6, pq.sales_order_line_id, pq.cogs_om_line_id, pq.cogs_account_ccid, pq.chart_of_accounts_id, rctlgda.gl_date having :p_all_lines = 'Y' or (decode(sum(rctla.revenue_amount),0,1,round(sum(decode(rctlgda.account_class,'UNEARN',0,'UNBILL',0, rctlgda.amount))/(sum(rctla.revenue_amount)/count(rctlgda.cust_trx_line_gl_dist_id)),3)) <> decode(pq.earned_cogs,0,decode(pq.deferred_cogs,0,1,0),round((pq.earned_cogs/decode(pq.deferred_cogs+pq.earned_cogs,0,1,pq.deferred_cogs+pq.earned_cogs)),3)) and pq.deferred_cogs not between -1*:p_amt_tolerance and :p_amt_tolerance ) union select pq.ledger, pq.ledger_currency, pq.operating_unit, pq.order_number, pq.order_date, pq.customer_name customer, pq.order_currency, pq.sales_order_line order_line, pq.total_line_quantity order_quantity, pq.uom, pq.sales_order_issue_date, null invoice_number, null quantity_invoiced, null invoice_line, null unit_selling_price, pq.item_cost, pq.material_cost, pq.material_oh_cost, pq.osp_cost, pq.resource_cost, pq.overhead_cost, pq.item, pq.item_description, pq.user_item_type, pq.inventory_item_id, pq.organization_code, pq.organization_id, null earned_revenue, null unearned_revenue, null unbilled_revenue, pq.earned_cogs, pq.deferred_cogs, pq.cogs_account, pq.deferred_cogs_account, null revenue_ccid, null unearn_ccid, null unbill_ccid, null interface_line_attribute6, pq.sales_order_line_id, pq.cogs_om_line_id, pq.cogs_account_ccid, pq.chart_of_accounts_id, pq.gl_date from perpetual_qry pq where (pq.customer_trx_line_id is null or pq.customer_trx_id is null) and (:p_all_lines = 'Y' or pq.deferred_cogs not between -1*:p_amt_tolerance and :p_amt_tolerance) union select pq.ledger, pq.ledger_currency, pq.operating_unit, pq.order_number, pq.order_date, pq.customer_name customer, pq.order_currency, pq.sales_order_line order_line, pq.total_line_quantity order_quantity, pq.uom, pq.sales_order_issue_date, null invoice_number, null quantity_invoiced, null invoice_line, null unit_selling_price, pq.item_cost, pq.material_cost, pq.material_oh_cost, pq.osp_cost, pq.resource_cost, pq.overhead_cost, pq.item, pq.item_description, pq.user_item_type, pq.inventory_item_id, pq.organization_code, pq.organization_id, null earned_revenue, null unearned_revenue, null unbilled_revenue, pq.earned_cogs, pq.deferred_cogs, pq.cogs_account, pq.deferred_cogs_account, null revenue_ccid, null unearn_ccid, null unbill_ccid, null interface_line_attribute6, pq.sales_order_line_id, pq.cogs_om_line_id, pq.cogs_account_ccid, pq.chart_of_accounts_id, pq.gl_date from perpetual_qry pq where pq.customer_trx_line_id is not null and pq.customer_trx_id is not null and not exists ( select /*+ use_concat no_unnest leading(rctla) use_nl(rctla,rctlgda) */ null from ra_customer_trx_lines_all rctla, ra_cust_trx_line_gl_dist_all rctlgda where (pq.customer_trx_line_id = rctla.customer_trx_line_id or pq.customer_trx_line_id = rctla.previous_customer_trx_line_id) and rctla.customer_trx_line_id = rctlgda.customer_trx_line_id and rctlgda.account_set_flag = 'N' and rctlgda.account_class in ('REV', 'UNEARN', 'UNBILL') and trunc(rctlgda.gl_date) <= trunc(nvl(:p_gps_end_dt,rctlgda.gl_date)) and trunc(rctlgda.gl_date) >= trunc(nvl(:p_gps_start_dt,rctlgda.gl_date)) ) and (:p_all_lines = 'Y' or pq.deferred_cogs not between -1*:p_amt_tolerance and :p_amt_tolerance) ) x where 3=3 and :p_ledger=:p_ledger and :p_cost_method=1 and nvl(:p_cost_type,'?')=nvl(:p_cost_type,'?') order by x.order_number, x.order_line, nvl2(order_quantity,1,2) #,##0.00;[Red]-#,##0.00 353C2C89549413B7E0638B00890AE15B :Period is not null or :Order_Number is not null US AUR:CST COGS Revenue Matching Imported from Concurrent Program Application: Bills of Material Source: COGS Revenue Matching Report Short Name: CSTRCMRX The COGS/Revenue Matching Report displays earned and unearned (deferred) revenue, and cost of goods sold amounts for sales orders issues specified in the report's run-time parameters. The report displays shipped sales order and associated sales order lines and shows the accounts where the earned and deferred COGS were charged. The report is based on the Revenue and COGS Matching functionality delivered in Oracle EBS R12. Please refer to the following documentation regarding this functionality: - Oracle Cost Management User Guide, Section: Revenue and COGS Matching - MOS Document 1060202.1 Understanding COGS and DCOGS Recognition Accounting Revenue / COGS Recognition Process Flow ======================================= When you ship confirm one or more order lines in Oracle Order Management and then run the applicable Cost Management cost and accounting processes, the cost of goods sold associated with the sales order line is immediately debited to a Deferred COGS account pending the invoicing and recognition of the sales order revenue in Oracle Receivables. When Oracle Receivables recognizes all or part of the sales revenue associated with a sales order line, you run a cost process that calculates the percentage of total billed revenue recognized. Oracle Inventory then creates a cost recognition transaction that adjusts the Deferred COGS and regular COGS amount for the order line. The proportion of total shipment cost that is recognized as COGS will always match the proportion of total billable quantity that is recognized as revenue. Revenue / COGS Recognition Concurrent Processes ================================================ It is recommended the Revenue and COGS concurrent processes be run in the following order: Run the AR Concurrent Processes first: - Autoinvoice Master Program. Run autoinvoice to generate the invoice transactions. - Revenue Recognition. Run the Revenue Recognition Master Program to generate the AR revenue recognition Then the COGS Concurrent Processes: - Record Order Management Transactions The Record Order Management Transactions concurrent process picks up and costs all uncosted sales order issue and RMA return transactions and creates a record for each new order line in the costing COGS recognition matching table. This process is not for Perpetual Discrete Costing (Standard, Average, FIFO). In Discrete Costing, the cost processor selects and costs the uncosted sales order issues and inserts them into the COGS matching table - Collect Revenue Recognition Information The Collect Revenue Recognition Information concurrent process calls an Oracle Receivables API to retrieve the latest revenue recognition percentage of all invoiced sales order lines in Oracle receivables for a specific ledger and with activity dates within a user-specified date range. This process must be run before the Generate COGS recognition Event concurrent process. - Generate COGS Recognition Events The Generate COGS Recognition Events concurrent request compares the COGS recognition percentage for each sales order line and accounting period combination to the current earned revenue percentage. When the compared percentages are different, the process raises a COGS recognition event and creates a COGS recognition transaction in Oracle Inventory that adjusts the ratio of earned and deferred COGS to match that of earned and deferred revenue. You must run this process after completion of the Collect Revenue Recognition Information concurrent process. Please specify a Period and/or Order Number &category_columns &lp_cogs_account_segments &lp_cost_type_col &lp_rev_account_segments 1=1 3=3 :p_all_lines :p_amt_tolerance :p_cost_method :p_cost_type :p_gps_end_dt :p_gps_start_dt :p_ledger :p_ledger_id :p_period_name 1 -40 :p_gps_start_dt Date select gps.start_date + 1/86400 from gl_period_statuses gps where gps.application_id = 101 and gps.set_of_books_id = :$flex$.ledger_id and gps.period_name = :$flex$.period US GL Period Start Date 2 -30 :p_gps_end_dt Date select gps.end_date + (1-1/86400) from gl_period_statuses gps where gps.application_id = 101 and gps.set_of_books_id = :$flex$.ledger_id and gps.period_name = :$flex$.period F Date de fin de la période GL US GL Period End Date 3 -20 :p_cost_method Number 1 Y US p_cost_method 4 -10 :p_ledger_id Number select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger Y F ID du grand livre US Ledger Id 5 &lp_cogs_account_segments select 'fnd_flex_xml_publisher_apis.process_kff_combination_1(''seg'',''SQLGL'',''GL#'',x.chart_of_accounts_id,NULL,x.cogs_account_ccid,' || fidsv.segment_num || ',''Y'',''VALUE'') "COGS: ' || substrb(fidsv.form_left_prompt,1,xxen_report.max_column_length-6) || '",' || chr(10) || 'fnd_flex_xml_publisher_apis.process_kff_combination_1(''seg'',''SQLGL'',''GL#'',x.chart_of_accounts_id,NULL,x.cogs_account_ccid,' || fidsv.segment_num || ',''Y'',''DESCRIPTION'') "COGS: ' || substrb(fidsv.form_left_prompt,1,xxen_report.max_column_length-11) || ' Desc",' from gl_ledgers gl, fnd_id_flex_segments_vl fidsv where gl.ledger_id = :p_ledger_id and fidsv.application_id = 101 and fidsv.id_flex_code = 'GL#' and fidsv.id_flex_num = gl.chart_of_accounts_id and fidsv.enabled_flag = 'Y' and fidsv.display_flag = 'Y' order by fidsv.segment_num US Ledger Id 6 &lp_rev_account_segments select 'case when x.revenue_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1(''seg'',''SQLGL'',''GL#'',x.chart_of_accounts_id,NULL,x.revenue_ccid,' || fidsv.segment_num || ',''Y'',''VALUE'') end "REV: ' || substrb(fidsv.form_left_prompt,1,xxen_report.max_column_length-6) || '",' || chr(10) || 'case when x.revenue_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1(''seg'',''SQLGL'',''GL#'',x.chart_of_accounts_id,NULL,x.revenue_ccid,' || fidsv.segment_num || ',''Y'',''DESCRIPTION'') end "REV: ' || substrb(fidsv.form_left_prompt,1,xxen_report.max_column_length-11) || ' Desc",' from gl_ledgers gl, fnd_id_flex_segments_vl fidsv where gl.ledger_id = :p_ledger_id and fidsv.application_id = 101 and fidsv.id_flex_code = 'GL#' and fidsv.id_flex_num = gl.chart_of_accounts_id and fidsv.enabled_flag = 'Y' and fidsv.display_flag = 'Y' order by fidsv.segment_num US Ledger Id 7 10 :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 coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) Y D GL_SRS_LEDGER_ID_UNVALIDATED E GL_SRS_LEDGER_ID_UNVALIDATED F Grand livre I GL_SRS_LEDGER_ID_UNVALIDATED US Ledger ZHS GL_SRS_LEDGER_ID_UNVALIDATED 8 20 1=1 haou.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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) F Unité d'exploitation US Operating Unit 9 30 :p_period_name LOV custom Y select period_name value, null description from gl_period_statuses where closing_status in ('O','C','P') and ledger_id=:$flex$.ledger_id and application_id=101 order by period_year desc,period_num desc select value,description from (select period_name value, null description from gl_period_statuses where closing_status in ('O','C','P') and ledger_id=:$flex$.ledger_id and application_id=101 order by period_year desc,period_num desc ) where rownum=1 Y D Periode E Período F Période I Periodo US Period ZHS 期间 10 40 1=1 ooha.order_number=:p_order_number LOV ONT Order Number 8E2FF36EDEE779D2E0530100007F1FF2 Y Y select ooha.order_number value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and ooha.org_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 ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc US Order Number 11 50 1=1 crcml.sales_order_issue_date>=:p_sales_order_date_low Date D Von Entnahmedatum Kundenauftrag E De Fecha de Emisión de Pedido de Venta F Date d'émission de la commande de vente de I Data emissione ordine di vendita - Da US Sales Order Issue Date From ZHS 销售订单发放日期自 12 60 1=1 crcml.sales_order_issue_date<:p_sales_order_date_high+1 Date D Bis Entnahmedatum Kundenauftrag E A Fecha de Emisión de Pedido de Venta F Date d'émission de la commande de vente au I Data emissione ordine di vendita - A US Sales Order Issue Date To ZHS 销售订单发放日期至 13 70 1=1 oola.ship_from_org_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:ship_from_warehouse) LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US Ship From Warehouse 14 80 3=3 x.organization_code= :org_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US Organization 15 90 :p_all_lines LOV Oracle CST_SRS_SYS_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by meaning Y Y D Abgeglichene Positionen anzeigen E Mostrar Líneas Confrontadas F Afficher les lignes appariées I Visualizza linee corrispondenti US Display Matched Lines ZHS 显示匹配行 16 100 :p_amt_tolerance Number 0 Y D Toleranzbetrag Toleranzbetrag E Importe de Tolerancia Importe de Tolerancia F Montant de la tolérance Montant de tolérance I Importo tolleranza Importo di tolleranza US Tolerance Amount Tolerance Amount ZHS 允差额 允差额 17 110 :p_cost_type LOV custom Y select distinct cct.cost_type value, cct.description description from cst_cost_types cct where nvl(cct.disable_date,sysdate+1)>sysdate and ( (cct.cost_type_id not in (1,2,5,6)) or exists ( select null from mtl_parameters mp, org_organization_definitions ood where mp.organization_id = ood.organization_id and ( (:$flex$.ledger is not null and :$flex$.operating_unit is null and :$flex$.organization_code is null and ood.set_of_books_id in (select gsob.set_of_books_id from gl_sets_of_books gsob where gsob.name = :$flex$.ledger) ) or (:$flex$.operating_unit is not null and :$flex$.organization_code is null and ood.operating_unit in (select hou.organization_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name) = 'Y') ) or (:$flex$.organization_code is not null and xxen_util.contains(:$flex$.organization_code,ood.organization_code) = 'Y' ) or (:$flex$.ledger is null and :$flex$.operating_unit is null and :$flex$.organization_code is null and ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') ) ) and mp.primary_cost_method = cct.cost_type_id and mp.organization_id = nvl(cct.organization_id,mp.organization_id) ) ) order by cct.cost_type US Display Cost Type Cost Type 18 &lp_cost_type_col select '( select cic.item_cost from mtl_parameters mp, cst_item_costs cic, cst_cost_types cct where mp.organization_id = x.organization_id and cic.organization_id = mp.cost_organization_id and cic.inventory_item_id = x.inventory_item_id and cic.cost_type_id = cct.cost_type_id and cic.organization_id = nvl(cct.organization_id,cic.organization_id) and cct.cost_type = :p_cost_type ) "' || :p_cost_type || ' Cost",' from dual US Display Cost Type 19 120 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') 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=1)) US Category Set 1 20 130 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') 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=2)) US Category Set 2 21 140 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') 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