ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B1A6F966279C37D6E0530100007F3EB7Y 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 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 Enginatics &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 xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) 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