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 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）限制的操作单元。 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 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 所有活动的清单组织代码，不包括主组织，仅限于当前用户的组织访问视图。 B1A6F966279C37D6E0530100007F3EB7 with x as ( select hou.organization_id, gl.name ledger, hou.name operating_unit, fc.precision, nvl(fc.extended_precision,fc.precision) extended_precision, fspa.inventory_organization_id from hr_operating_units hou, gl_ledgers gl, fnd_currencies fc, financials_system_params_all fspa where 2=2 and hou.set_of_books_id=gl.ledger_id and gl.currency_code=fc.currency_code and hou.set_of_books_id=fspa.set_of_books_id and hou.organization_id=fspa.org_id ), xp as ( select x.ledger, x.operating_unit, x.precision, x.extended_precision, x.inventory_organization_id, rt.transaction_id, rt.organization_id, rt.transaction_date, rt.primary_unit_of_measure, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rt.po_distribution_id, rt.transaction_type, rt.currency_conversion_rate, rt.po_unit_price, rt.source_doc_quantity, rt.primary_quantity, rt.lcm_shipment_line_id, rsh.shipment_num, rsh.receipt_num, rt.destination_type_code rt_destination_type_code, pha.po_header_id, pha.org_id, pha.vendor_id, pha.agent_id, pha.type_lookup_code, pha.segment1, pha.currency_code, aps.vendor_name, pla.item_id, pla.category_id, pla.line_num, plla.line_location_id, plla.po_release_id, plla.lcm_flag, pda.po_distribution_id, pda.destination_type_code, pda.nonrecoverable_tax, pda.quantity_ordered from x, rcv_transactions rt, rcv_shipment_headers rsh, po_headers_all pha, ap_suppliers aps, po_lines_all pla, po_line_locations_all plla, po_distributions_all pda where 1=1 and rt.shipment_header_id=rsh.shipment_header_id and x.organization_id=pha.org_id and rt.po_header_id=pha.po_header_id and pha.vendor_id=aps.vendor_id and rt.po_line_id=pla.po_line_id and rt.po_line_location_id=plla.line_location_id and rt.po_distribution_id=pda.po_distribution_id ), papf as ( select papf.person_id, papf.full_name from per_all_people_f papf where (papf.employee_number is not null or papf.npw_number is not null) and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number ))='TRUE' and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id)=papf.business_group_id ) select 'Receiving PPV' type, xp.ledger, xp.operating_unit, mp.organization_code, mcv.category_concat_segs category, msiv.concatenated_segments item, msiv.description item_description, xp.vendor_name vendor, papf.full_name buyer, decode(xp.type_lookup_code, 'BLANKET', xp.segment1||' - '||pra.release_num, 'PLANNED', xp.segment1||' - '||pra.release_num, xp.segment1) po_number, xp.currency_code currency, xp.line_num line, xp.shipment_num shipment, xp.transaction_date receipt_date, xp.receipt_num receipt_number, round(mmt.primary_quantity,:p_qty_precision) quantity_received, xp.primary_unit_of_measure unit, round(nvl(mmt.transaction_cost,0)/nvl(mmt.currency_conversion_rate,1),xp.extended_precision) po_unit_price, round(nvl(mmt.transaction_cost,0),xp.extended_precision) po_functional_price, round(nvl(mmt.actual_cost,0),xp.extended_precision) std_unit_cost, round(nvl(mcacd1.actual_cost,0),xp.extended_precision) material_unit_cost, round(decode(mta.accounting_line_type,3,nvl(mcacd2.actual_cost,0),0),xp.extended_precision) material_overhead_unit_cost, po_poxrcppv_xmlp_pkg.c_price_varianceformula(round(nvl(mmt.transaction_cost,0),xp.extended_precision),round(nvl(mmt.actual_cost,0),xp.extended_precision),round(decode(mta.accounting_line_type,3,nvl(mcacd2.actual_cost,0),0),xp.extended_precision),round(mmt.primary_quantity,:p_qty_precision ),xp.precision) purchase_price_variance, mp.process_enabled_flag, xp.transaction_id rcv_transaction_id, xp.item_id from xp, po_releases_all pra, mtl_system_items_vl msiv, mtl_categories_v mcv, mtl_parameters mp, papf, mtl_material_transactions mmt, mtl_transaction_accounts mta, mtl_cst_actual_cost_details mcacd1, mtl_cst_actual_cost_details mcacd2 where 3=3 and xp.destination_type_code='INVENTORY' and xp.po_release_id=pra.po_release_id(+) and xp.item_id=msiv.inventory_item_id(+) and xp.inventory_organization_id=msiv.organization_id(+) and xp.organization_id=mp.organization_id and mp.process_enabled_flag='N' and xp.category_id=mcv.category_id(+) and xp.agent_id=papf.person_id and xp.transaction_id=mmt.rcv_transaction_id and xp.organization_id=mmt.organization_id and exists (select null from mtl_transaction_accounts mta1 where mmt.transaction_id=mta1.transaction_id and mta1.accounting_line_type=6) and mmt.transaction_id=mta.transaction_id (+) and mta.accounting_line_type(+)=3 and mmt.transaction_id=mcacd1.transaction_id(+) and mmt.transaction_id=mcacd2.transaction_id(+) and mmt.organization_id=mcacd1.organization_id(+) and mmt.organization_id=mcacd2.organization_id(+) and mcacd1.layer_id(+)=-1 and mcacd2.layer_id(+)=-1 and mcacd1.cost_element_id(+)=1 and mcacd2.cost_element_id(+)=2 and mcacd1.level_type(+)=1 and mcacd2.level_type(+)=1 and mmt.transaction_action_id=mcacd1.transaction_action_id(+) and mmt.transaction_action_id=mcacd2.transaction_action_id(+) union select 'Receiving PPV' type, xp.ledger, xp.operating_unit, mp.organization_code, mcv.category_concat_segs category, msiv.concatenated_segments item, msiv.description description, xp.vendor_name vendor, papf.full_name buyer, decode(xp.type_lookup_code, 'BLANKET', xp.segment1||' - '||pra.release_num, 'PLANNED', xp.segment1||' - '||pra.release_num, xp.segment1) po_number_release, xp.currency_code currency, xp.line_num line, xp.shipment_num shipment, xp.transaction_date receipt_date, xp.receipt_num receipt_number, round(decode(xp.transaction_type, 'RETURN TO RECEIVING', xp.primary_quantity * -1, xp.primary_quantity), :p_qty_precision) quantity_received, xp.primary_unit_of_measure unit, xp.po_unit_price*(xp.source_doc_quantity/xp.primary_quantity)+nvl(xp.nonrecoverable_tax,0)/decode(xp.quantity_ordered,0,1,xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity) unit_price, round(nvl(xp.currency_conversion_rate,1)*nvl(xp.po_unit_price*(xp.source_doc_quantity/xp.primary_quantity),0)+( (nvl(xp.nonrecoverable_tax,0)*nvl(xp.currency_conversion_rate,1))/decode(xp.quantity_ordered,0,1,xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity)),xp.extended_precision) po_functional_price, round(&p_select_wip,xp.extended_precision) std_unit_cost_f, 0 material_cost_f, 0 moh_absorbed_per_unit, po_poxrcppv_xmlp_pkg.c_price_varianceformula(round(nvl(xp.currency_conversion_rate,1)*nvl(xp.po_unit_price*(xp.source_doc_quantity/xp.primary_quantity),0) + nvl(xp.nonrecoverable_tax,0)*nvl(xp.currency_conversion_rate,1)/decode(xp.quantity_ordered,0,1,xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity),xp.extended_precision),round(&p_select_wip,xp.extended_precision ),0,round(decode(xp.transaction_type,'RETURN TO RECEIVING',xp.primary_quantity*-1,xp.primary_quantity),:p_qty_precision),xp.precision) c_price_variance, mp.process_enabled_flag, xp.transaction_id rct_id, xp.item_id from xp, po_releases_all pra, mtl_system_items_vl msiv, mtl_categories_v mcv, mtl_parameters mp, papf &p_from_wip where 3=3 and xp.destination_type_code='SHOP FLOOR' and xp.po_release_id=pra.po_release_id(+) and xp.item_id=msiv.inventory_item_id(+) and xp.inventory_organization_id=msiv.organization_id(+) and xp.organization_id=mp.organization_id and mp.process_enabled_flag='N' and xp.category_id=mcv.category_id(+) and xp.agent_id=papf.person_id &p_where_wip union all select 'Receiving PPV' type, xp.ledger, xp.operating_unit, mp.organization_code, mcv.category_concat_segs category, msiv.concatenated_segments item, msiv.description description, xp.vendor_name vendor, papf.full_name buyer, decode(xp.type_lookup_code, 'BLANKET', xp.segment1||' - '||pra.release_num, 'PLANNED', xp.segment1||' - '||pra.release_num, xp.segment1) po_number_release, xp.currency_code currency, xp.line_num line, xp.shipment_num shipment, xp.transaction_date receipt_date, xp.receipt_num receipt_number, round(decode(xp.transaction_type,'RETURN TO RECEIVING',xp.primary_quantity*-1,'RETURN TO VENDOR',xp.primary_quantity*-1,xp.primary_quantity),:p_qty_precision) quantity_received, xp.primary_unit_of_measure unit, xp.po_unit_price*(xp.source_doc_quantity/xp.primary_quantity ) + (nvl(xp.nonrecoverable_tax,0)/decode(xp.quantity_ordered,0,1, xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity)) unit_price, round(nvl(xp.currency_conversion_rate,1)*nvl(xp.po_unit_price* (xp.source_doc_quantity / xp.primary_quantity),0) + nvl(xp.nonrecoverable_tax,0)*nvl(xp.currency_conversion_rate,1)/decode(xp.quantity_ordered,0,1,xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity),xp.extended_precision) po_functional_price, po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(xp.item_id,decode(mp.process_enabled_flag,'Y',xp.organization_id,xp.inventory_organization_id),xp.transaction_date,mp.process_enabled_flag,0,xp.extended_precision) std_unit_cost_f, 0 material_cost_f, 0 moh_absorbed_per_unit, po_poxrcppv_xmlp_pkg.c_price_varianceformula(round(nvl(xp.currency_conversion_rate,1)*nvl(xp.po_unit_price*(xp.source_doc_quantity/xp.primary_quantity),0) + nvl(xp.nonrecoverable_tax,0)*nvl(xp.currency_conversion_rate,1)/decode(xp.quantity_ordered,0,1,xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity),xp.extended_precision), po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(xp.item_id, decode(mp.process_enabled_flag, 'Y', xp.organization_id, xp.inventory_organization_id), xp.transaction_date, mp.process_enabled_flag, 0, xp.extended_precision), 0, round(decode(xp.transaction_type,'RETURN TO RECEIVING', xp.primary_quantity * -1,xp.primary_quantity), :p_qty_precision), xp.precision) c_price_variance, mp.process_enabled_flag, xp.transaction_id rct_id, xp.item_id from xp, po_releases_all pra, mtl_system_items_vl msiv, mtl_categories_v mcv, mtl_parameters mp, papf where 3=3 and xp.destination_type_code in ('INVENTORY','SHOP FLOOR') and (nvl(xp.lcm_flag,'N')='N' or xp.lcm_flag='Y' and xp.lcm_shipment_line_id is null) and xp.rt_destination_type_code<>'RECEIVING' and xp.po_release_id=pra.po_release_id(+) and xp.item_id=msiv.inventory_item_id(+) and xp.inventory_organization_id=msiv.organization_id(+) and xp.organization_id=mp.organization_id and mp.process_enabled_flag='Y' and xp.category_id=mcv.category_id(+) and xp.agent_id=papf.person_id union all /* lcm-opm integration added below query bug 8642337, pmarada */ select distinct 'Receiving PPV' type, xp.ledger, xp.operating_unit, mp.organization_code, mcv.category_concat_segs category, msiv.concatenated_segments item, msiv.description description, xp.vendor_name vendor, papf.full_name buyer, decode(xp.type_lookup_code, 'BLANKET', xp.segment1||' - '||pra.release_num, 'PLANNED', xp.segment1||' - '||pra.release_num, xp.segment1) po_number_release, xp.currency_code currency, xp.line_num line, xp.shipment_num shipment, glat.transaction_date receipt_date, xp.receipt_num receipt_number, round(glat.primary_quantity,:p_qty_precision) quantity_received, glat.primary_uom_code unit, xp.po_unit_price*(xp.source_doc_quantity/xp.primary_quantity) + (nvl(xp.nonrecoverable_tax,0)/xp.quantity_ordered)*(xp.source_doc_quantity/xp.primary_quantity) unit_price, round(nvl(xp.currency_conversion_rate,1)*nvl(xp.po_unit_price*xp.source_doc_quantity/xp.primary_quantity,0) + nvl(xp.nonrecoverable_tax,0)*nvl(xp.currency_conversion_rate,1)/xp.quantity_ordered*(xp.source_doc_quantity/xp.primary_quantity),xp.extended_precision) po_functional_price, po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(xp.item_id, decode(mp.process_enabled_flag, 'Y', xp.organization_id, xp.inventory_organization_id), xp.transaction_date, mp.process_enabled_flag, 0 , xp.extended_precision) std_unit_cost_f, 0 material_cost_f, 0 moh_absorbed_per_unit, po_poxrcppv_xmlp_pkg.c_price_varianceformula( round(nvl(xp.currency_conversion_rate,1) * nvl(xp.po_unit_price* (xp.source_doc_quantity / xp.primary_quantity),0) + (( (nvl(xp.nonrecoverable_tax,0) * nvl(xp.currency_conversion_rate,1))/decode (xp.quantity_ordered,0,1,xp.quantity_ordered)) *(xp.source_doc_quantity/xp.primary_quantity)), xp.extended_precision), po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(xp.item_id, decode(mp.process_enabled_flag, 'Y', xp.organization_id, xp.inventory_organization_id), xp.transaction_date, mp.process_enabled_flag, 0, xp.extended_precision), 0, round(decode(xp.transaction_type,'RETURN TO RECEIVING', xp.primary_quantity * -1,xp.primary_quantity), :p_qty_precision), xp.precision) c_price_variance, mp.process_enabled_flag, xp.transaction_id rct_id, xp.item_id from xp, po_releases_all pra, mtl_system_items_vl msiv, mtl_categories_v mcv, mtl_parameters mp, papf, gmf_lc_adj_transactions glat where 3=3 and 4=4 and xp.destination_type_code='INVENTORY' and xp.lcm_flag='Y' and xp.rt_destination_type_code<>'RECEIVING' and xp.po_release_id=pra.po_release_id(+) and xp.item_id=msiv.inventory_item_id(+) and xp.inventory_organization_id=msiv.organization_id(+) and xp.transaction_id=glat.rcv_transaction_id and glat.event_type in (16,17) and glat.organization_id=mp.organization_id and mp.process_enabled_flag='Y' and xp.category_id=mcv.category_id(+) and xp.agent_id=papf.person_id union all select 'Ownership Transfer PPV' type, gl.name ledger, hou.name operating_unit, mp.organization_code, mcv.category_concat_segs category, msiv.concatenated_segments item, msiv.description description, aps.vendor_name vendor, papf.full_name buyer, pha.segment1 po_number_release, pha.currency_code currency, null line, null shipment, mmt.transaction_date receipt_date, null receipt_number, round(mmt.primary_quantity,:p_qty_precision) quantity_received, msiv.primary_unit_of_measure unit, round((nvl(mmt.transaction_cost,0)/nvl(mmt.currency_conversion_rate,1)),fc.extended_precision) unit_price, round(nvl(mmt.transaction_cost,0),fc.extended_precision) po_functional_price, round(nvl(mmt.actual_cost,0),fc.extended_precision) std_unit_cost_f, null material_cost_f, round(case when (select count(*) from mtl_transaction_accounts where transaction_id = mmt.transaction_id and accounting_line_type = 3) > 0 then nvl(mcacd2.actual_cost,0) else 0 end,fc.extended_precision) moh_absorbed_per_unit, po_poxrcppv_xmlp_pkg.c_price_variance1formula (round(nvl(mmt.transaction_cost,0), fc.extended_precision), round(nvl(mmt.actual_cost,0),fc.extended_precision), round(case when (select count(*) from mtl_transaction_accounts where transaction_id = mmt.transaction_id and accounting_line_type = 3) > 0 then nvl(mcacd2.actual_cost,0) else 0 end,fc.extended_precision), round(mmt.primary_quantity,:p_qty_precision ),fc.precision ) c_price_variance, mp.process_enabled_flag, null rct_id, mmt.inventory_item_id from gl_ledgers gl, (select fc.currency_code, fc.precision, nvl(fc.extended_precision,fc.precision) extended_precision from fnd_currencies fc) fc, hr_operating_units hou, po_headers_all pha, ap_suppliers aps, mtl_system_items_vl msiv, mtl_default_category_sets mdcs, mtl_item_categories mic, mtl_categories_v mcv, mtl_parameters mp, papf, mtl_material_transactions mmt, mtl_cst_actual_cost_details mcacd2 where 2=2 and 5=5 and gl.currency_code=fc.currency_code and gl.ledger_id=hou.set_of_books_id and hou.organization_id=pha.org_id and pha.vendor_id=aps.vendor_id and mmt.inventory_item_id=msiv.inventory_item_id and msiv.organization_id=(select fspa.inventory_organization_id from financials_system_params_all fspa where hou.set_of_books_id=fspa.set_of_books_id and pha.org_id=fspa.org_id) and mdcs.functional_area_id=2 and mdcs.category_set_id=mic.category_set_id and msiv.inventory_item_id=mic.inventory_item_id and msiv.organization_id=mic.organization_id and mic.category_id=mcv.category_id(+) and pha.agent_id=papf.person_id and pha.po_header_id=mmt.transaction_source_id and mmt.transaction_action_id=6 and exists (select null from mtl_transaction_accounts mta1 where mmt.transaction_id=mta1.transaction_id and mta1.accounting_line_type=6) and mmt.organization_id=mp.organization_id and mp.process_enabled_flag='N' and mmt.transaction_id=mcacd2.transaction_id(+) and mmt.organization_id=mcacd2.organization_id(+) and mcacd2.layer_id(+)=-1 and mcacd2.cost_element_id(+)=2 and mcacd2.level_type(+)=1 and mmt.transaction_action_id=mcacd2.transaction_action_id(+) Changes to adapt the SQL to 11g Database to avoid outer join error Y PO :Ledger is not null or :Operating_Unit is not null AR PO Purchase Price Variance Application: المشتريات Source: تقرير تباين سعر الشراء (بتنسيق XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG D PO Purchase Price Variance Application: Einkauf Source: Einkaufspreisabweichungen (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG E PO Purchase Price Variance Application: Purchasing Source: Informe Desviación en Precio de Compra (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG F PO Purchase Price Variance Application: Purchasing Source: Etat des écarts sur prix d'achat (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG I PO Purchase Price Variance Application: Purchasing Source: Rapporto Scostamento prezzo di acquisto (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG JA PO Purchase Price Variance Application: Purchasing Source: 購買価格差異レポート(XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG KO PO Purchase Price Variance Application: Purchasing Source: 구매 가격 차이 보고서(XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG PTB PO Purchase Price Variance Application: Compra Source: Relatório de Variação do Preço de Compra (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG RU PO Purchase Price Variance Application: Закупки Source: Отчет о расхождении закупочных цен (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG S PO Purchase Price Variance Application: Purchasing Source: Prisavvikelse för inköp - rapport (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG TR PO Purchase Price Variance Application: Satın Alma Yönetimi Source: Satın Alma Fiyat Sapması Raporu (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG US PO Purchase Price Variance Imported Oracle standard Purchase Price Variance report Source: Purchase Price Variance Report (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG Please enter Ledger or Operating Unit ZHS PO Purchase Price Variance Application: 采购管理系统 Source: 采购价格差异报表 (XML) Short Name: POXRCPPV_XML DB package: PO_POXRCPPV_XMLP_PKG BI Publisher Enginatics R12 only &p_from_wip &p_select_wip &p_where_wip 1=1 2=2 3=3 4=4 5=5 :p_qty_precision 1 -20 &p_from_wip , wip_transactions wt Char I select fpi.status from fnd_product_installations fpi where fpi.application_id=706 US WIP_INSTALLED 2 &p_from_wip null US WIP_INSTALLED 3 &p_select_wip wt.standard_resource_rate*decode(msiv.outside_operation_uom_type,'ASSEMBLY',decode(wt.usage_rate_or_amount,0,1,wt.usage_rate_or_amount),1) I US WIP_INSTALLED 4 &p_where_wip and xp.transaction_id=wt.rcv_transaction_id and wt.standard_rate_flag=1 and wt.transaction_type=3 and exists (select null from wip_transaction_accounts wta where wt.transaction_id=wta.transaction_id and wta.accounting_line_type=6) I US WIP_INSTALLED 5 10 2=2 gl.name=:ledger 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) 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) US Ledger 6 20 2=2 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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 7 30 3=3 mp.organization_code=:organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US Organization Code Organization 8 5=5 mp.organization_code=:organization_code US Organization Code 9 40 3=3 mcv.category_concat_segs=:p_category LOV custom Y select mcbk.concatenated_segments value, mct.description from mtl_categories_b_kfv mcbk, mtl_categories_tl mct where mcbk.structure_id=:$flex$.p_struct_num and mcbk.category_id=mct.category_id and mct.language=userenv('lang') order by mcbk.concatenated_segments US Category Categories From 10 50 1=1 rt.transaction_date>=:trans_date_from Date Y US Transaction Dates From Transaction Dates From 11 4=4 glat.transaction_date>=:trans_date_from US Transaction Dates From 12 5=5 mmt.transaction_date>=:trans_date_from US Transaction Dates From 13 60 1=1 rt.transaction_date<:trans_date_to+1 Date last_day(:$flex$.transaction_dates_from) Y US Transaction Dates To Transaction Dates To 14 4=4 glat.transaction_date<:trans_date_to+1 US Transaction Dates To 15 5=5 mmt.transaction_date<:trans_date_to+1 US Transaction Dates To 16 70 3=3 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 17 5=5 msiv.concatenated_segments=:item_number US Item 18 80 1=1 aps.vendor_name=:vendor_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 Vendor Name Vendors From 19 5=5 aps.vendor_name=:vendor_name US Vendor Name 20 90 :p_orderby LOV Oracle PO_SRS_POXRCPPV_SORT Y select lookup_code id, displayed_field value, null description from po_lookup_codes where lookup_type='SRS ORDER BY' and lookup_code in ('ITEM','CATEGORY','VENDOR', 'BUYER') order by displayed_field US Sort By Sort By 21 100 :p_qty_precision LOV Oracle INV_SRS_PRECISION Y select lookup_code id, lookup_code value, meaning description from mfg_lookups where lookup_type='INV_SRS_PRECISION' and enabled_flag='Y' order by lookup_code fnd_profile.value('REPORT_QUANTITY_PRECISION') Y US Dynamic Precision Option Quantity Precision :$flex$.ledger Ledger Operating Unit :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code :$flex$.operating_unit Operating Unit Vendor Name :$flex$.organization_code Organization Code Vendor Name :$flex$.transaction_dates_from Transaction Dates From Y Transaction Dates To