ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDE7F79D2E0530100007F1FF2 AR Account Number Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name US 8E2FF36EDE8679D2E0530100007F1FF2 AR Customer Name Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name US 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）限制的操作单元。 8E2FF36EDEF179D2E0530100007F1FF2 INV Item Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Inventory items, restricted to the accessible inventory organizations 2450E07B1B9F64E9E0630100007FCFA7 INV Lot Number Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US D49619EA723A4EBAE0530100007FD3B2 select /*+ push_pred(wfc) push_pred(shipped_lots) push_pred(picked_lots) */ haou2.name selling_operating_unit, haou1.name shipping_operating_unit, ood.organization_code, -- delivery level info wnd.delivery_id delivery_note_id, wnd.name delivery_note_number, trunc(nvl(mmt.transaction_date,wnd.initial_pickup_date)) delivery_note_date, xxen_util.meaning(wnd.status_code,'DELIVERY_STATUS',665) delivery_status, trunc(mmt.transaction_date) actual_shipment_date, trunc(wnd.confirm_date) confirmed_date, wnd.confirmed_by, -- delivery detail info wdd.delivery_detail_id, xxen_util.meaning(wdd.released_status,'PICK_STATUS',665) release_status, trunc(wpb.creation_date) released_date, -- freight cost details wfc.freight_cost_currency, wfc.freight_cost_name, wfc.freight_cost_type, wfc.freight_cost, -- customer details hca.account_number customer_number, nvl(hca.account_name,hp.party_name) customer_name, hcsua.location customer_ship_to_site, hz_format_pub.format_address(hl.location_id,null,null,',') customer_ship_to_address, wdd.cust_po_number customer_po_number, wnd.delivery_id customer_delivery_code, wnd.name customer_delivery_name, -- delivery item details msiv.concatenated_segments item, wdd.item_description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type, wdd.requested_quantity, decode(wdd.released_status,'Y',nvl(wdd.picked_quantity,wdd.requested_quantity),NULL) picked_qty, wdd.shipped_quantity, wdd.delivered_quantity, wdd.cancelled_quantity, wdd.requested_quantity_uom uom, -- packing metrics wdd.net_weight, wdd.gross_weight, wdd.weight_uom_code weight_uom, wdd.volume, wdd.volume_uom_code volume_uom, wdd.unit_price, wdd.currency_code, -- shipment details wnd.waybill, wsh_util_core.derive_shipment_priority(wnd.delivery_id) shipment_priority, wsh_util_core.ship_method_to_freight(wnd.ship_method_code,wdd.organization_id) delivery_freight_code, xxen_util.meaning(wnd.ship_method_code,'SHIP_METHOD',3) delivery_shipping_method, xxen_util.meaning(wnd.freight_terms_code,'FREIGHT_TERMS',660) delivery_freight_terms, nvl(xxen_util.meaning(wnd.fob_code,'FOB',222),wnd.fob_code) delivery_fob, wsh_util_core.ship_method_to_freight(nvl(wdd.ship_method_code,wnd.ship_method_code),wdd.organization_id) delivery_line_freight_code, xxen_util.meaning(nvl(wdd.ship_method_code,wnd.ship_method_code),'SHIP_METHOD',3) delivery_line_shipping_method, xxen_util.meaning(nvl(wdd.freight_terms_code,wnd.freight_terms_code),'FREIGHT_TERMS',660) delivery_line_freight_terms, nvl(xxen_util.meaning(nvl(wdd.fob_code,wnd.fob_code),'FOB',222),wnd.fob_code) delivery_line_fob, wdd.tracking_number, wdd.customer_dock_code, -- container/lot details wdd.container_flag, xxen_util.meaning(wdd.container_type_code,'CONTAINER_TYPE',401) container_type, wdd.container_name, coalesce(wdd.lot_number,shipped_lots.lot_numbers,picked_lots.lot_numbers) lot_numbers, -- exceptions (select dbms_lob.substr(rtrim(xmlagg(xmlelement(name excptn,wev.description,',').extract('//text()') order by wev.description).GetClobVal(),','),4000,1) from (select distinct wev.delivery_detail_id, wev.description from wsh_exceptions_v wev where wev.status = 'OPEN' and wev.severity = 'ERROR' ) wev where wev.delivery_detail_id = wdd.delivery_detail_id ) error_exceptions, -- source references/invoice details wpb.name release_batch, coalesce((select mmt.pick_slip_number -- released to warehouse from mtl_material_transactions mmt where mmt.move_order_line_id = wdd.move_order_line_id and mmt.transaction_id = decode(nvl(wdd.transaction_id ,-99),-99,mmt.transaction_id,wdd.transaction_id) and mmt.transaction_source_type_id in (2,8) and nvl(mmt.transaction_quantity,0) < 0 --> to prevent duplicates, just take the -qty trx leg sub to staging. and mmt.pick_slip_number is not null and wdd.source_code = 'OE' and wdd.released_status != 'S' -- released to warehouse and rownum=1 ), (select mtrl.pick_slip_number from mtl_txn_request_lines mtrl where mtrl.line_id = wdd.move_order_line_id and mtrl.pick_slip_number is not null and wdd.source_code = 'OE' and wdd.released_status != 'S' -- released to warehouse and wdd.transaction_id is null and rownum=1 ), (select mmtt.pick_slip_number from mtl_material_transactions_temp mmtt where mmtt.move_order_line_id = wdd.move_order_line_id and nvl(mmtt.parent_line_id,0) = 0 and mmtt.pick_slip_number is not null and wdd.source_code = 'OE' and wdd.released_status = 'S' -- released to warehouse and rownum=1 ), (select mmttp.pick_slip_number from mtl_material_transactions_temp mmtt, mtl_material_transactions_temp mmttp where mmttp.transaction_temp_id = mmtt.parent_line_id and mmtt.parent_line_id != mmtt.transaction_temp_id and mmtt.move_order_line_id = wdd.move_order_line_id and nvl(mmtt.parent_line_id,0) = 0 and mmttp.pick_slip_number is not null and wdd.source_code = 'OE' and wdd.released_status = 'S' -- released to warehouse and rownum=1 ), (select mmtt.pick_slip_number from mtl_material_transactions_temp mmtt where mmtt.move_order_line_id = wdd.move_order_line_id and mmtt.parent_line_id = mmtt.transaction_temp_id and mmtt.pick_slip_number is not null and wdd.source_code = 'OE' and wdd.released_status = 'S' -- released to warehouse and rownum=1 ) ) pick_slip_number, (select mtrh.request_number from mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh where mtrl.header_id = mtrh.header_id and mtrl.line_id = wdd.move_order_line_id ) move_order_number, wdi.sequence_number pack_slip_number, xxen_util.meaning(wdd.source_code,'SOURCE_SYSTEM',665) source, wdd.source_header_type_name source_document_type, wdd.source_header_number source_document_number, wdd.source_line_number source_document_line, ooha.ordered_date source_document_date, rcta.trx_number invoice_number, rctla.line_number invoice_line_number, rcta.trx_date invoice_date, -- date info trunc(wdd.date_requested) requested_date, trunc(wdd.date_scheduled) scheduled_date, trunc(wdd.earliest_pickup_date) earliest_pickup_date, trunc(wdd.latest_pickup_date) latest_pickup_date, trunc(wdd.earliest_dropoff_date) earliest_dropoff_date, trunc(wdd.latest_dropoff_date) latest_dropoff_date, trunc(wnd.initial_pickup_date) delivery_initial_pickup_date, trunc(wnd.ultimate_dropoff_date) delivery_ultimate_dropoff_date, -- ship from/to locations wsh_util_core.get_location_description(nvl(wnd.initial_pickup_location_id,wdd.ship_from_location_id), case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) ship_from_location, wsh_util_core.get_location_description(nvl(wnd.ultimate_dropoff_location_id,wdd.ship_to_location_id), case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) ship_to_location, wsh_util_core.get_location_description(wdd.deliver_to_location_id, case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) deliver_to_location, wsh_util_core.get_location_description(nvl(wnd.intmed_ship_to_location_id,wdd.intmed_ship_to_location_id), case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) int_med_ship_to_location, wsh_util_core.get_location_description(wnd.fob_location_id, case :p_loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) fob_location, -- packing/shipping instructions wdd.packing_instructions, wdd.shipping_instructions from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd, wsh_picking_batches wpb, wsh_document_instances wdi, wsh_carriers wc, (select distinct x.delivery_detail_id, x.freight_cost_currency, x.freight_cost, listagg(x.freight_cost_name,',') within group (order by x.freight_cost_type_code,x.freight_cost_name) over (partition by x.delivery_detail_id,x.freight_cost_currency) freight_cost_name, listagg(x.freight_cost_type,',') within group (order by x.freight_cost_type_code,x.freight_cost_name) over (partition by x.delivery_detail_id,x.freight_cost_currency) freight_cost_type from (select wfc.delivery_detail_id, wfc.currency_code freight_cost_currency, wfct.name freight_cost_name, wfct.freight_cost_type_code, xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665) freight_cost_type, sum(nvl(wfc.total_amount,wfc.unit_amount * nvl(wfc.quantity,1))) over (partition by wfc.delivery_detail_id,wfc.currency_code) freight_cost, sum(lengthb(wfct.name)+1) over (partition by wfc.delivery_detail_id,wfc.currency_code order by wfct.freight_cost_type_code,wfct.name rows between unbounded preceding and current row) lengthb1, sum(lengthb(xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665))+1) over (partition by wfc.delivery_detail_id,wfc.currency_code order by wfct.freight_cost_type_code,wfct.name rows between unbounded preceding and current row) lengthb2 from wsh_freight_costs wfc, wsh_freight_cost_types wfct where wfc.freight_cost_type_id = wfct.freight_cost_type_id and nvl(wfc.charge_source_code, 'MANUAL') in ('PRICING_ENGINE' ,'MANUAL') ) x where (x.lengthb1 <= 4000 and x.lengthb2 <= 4000) ) wfc, mtl_system_items_vl msiv, mtl_material_transactions mmt, (select distinct x.picking_line_id, listagg(x.lot_number,',') within group (order by x.lot_number) over (partition by x.picking_line_id) lot_numbers from (select mmt.picking_line_id, mtln.lot_number, sum(lengthb(mtln.lot_number)+1) over (partition by mmt.picking_line_id order by mtln.lot_number rows between unbounded preceding and current row) lengthb from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln where 2=2 and mtln.transaction_id = mmt.transaction_id and mmt.transaction_source_type_id in (2,8) and mmt.transaction_quantity < 0 ) x where x.lengthb <= 4000 ) shipped_lots, (select distinct x.move_order_line_id, listagg(x.lot_number,',') within group (order by x.lot_number) over (partition by x.move_order_line_id) lot_numbers from (select mmt.move_order_line_id, mtln.lot_number, sum(lengthb(mtln.lot_number)+1) over (partition by mmt.move_order_line_id order by mtln.lot_number rows between unbounded preceding and current row) lengthb from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln where 2=2 and mtln.transaction_id = mmt.transaction_id and mmt.transaction_source_type_id in (2,8) and mmt.transaction_quantity < 0 ) x where x.lengthb <= 4000 ) picked_lots, oe_order_headers_all ooha, oe_order_lines_all oola, ra_customer_trx_lines_all rctla, ra_customer_trx_all rcta, org_organization_definitions ood, hr_all_organization_units haou1, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, hz_party_sites hps, hz_locations hl, hr_all_organization_units haou2 where 1=1 and nvl(wdd.line_direction,'O') in ('O','IO') and wdd.delivery_detail_id = wda.delivery_detail_id (+) and wda.delivery_id = wnd.delivery_id (+) and nvl(wnd.shipment_direction, 'O') in ('O','IO') and wnd.delivery_type (+) = 'STANDARD' and wdd.batch_id = wpb.batch_id(+) and wnd.delivery_id = wdi.entity_id(+) and wdi.entity_name(+) = 'WSH_NEW_DELIVERIES' and wdi.document_type(+) = 'PACK_TYPE' and wdd.carrier_id = wc.carrier_id(+) and wdd.organization_id = msiv.organization_id(+) and wdd.inventory_item_id = msiv.inventory_item_id(+) and wdd.delivery_detail_id = mmt.picking_line_id(+) and mmt.transaction_source_type_id (+) in (2,8) and mmt.transaction_quantity (+) < 0 and wdd.delivery_detail_id = wfc.delivery_detail_id (+) -- and nvl2(wdd.lot_number,-1,wdd.delivery_detail_id) = shipped_lots.picking_line_id (+) and nvl2(wdd.lot_number,-1,wdd.move_order_line_id) = picked_lots.move_order_line_id (+) -- and decode(wdd.source_code,'OE',wdd.source_header_id) = ooha.header_id(+) and decode(wdd.source_code,'OE',wdd.source_line_id) = oola.line_id(+) and rctla.interface_line_context(+) = 'ORDER ENTRY' and decode(wdd.source_code,'OE',wdd.source_header_number) = rctla.interface_line_attribute1(+) and decode(wdd.source_code,'OE',wdd.source_header_type_name) = rctla.interface_line_attribute2(+) and decode(wdd.source_code,'OE',to_char(wdd.source_line_id)) = rctla.interface_line_attribute6(+) and nvl(rctla.interface_line_attribute3,nvl(wnd.name,'??')) = nvl(wnd.name,'??') and nvl(rctla.interface_line_attribute11,'0') = '0' and rctla.customer_trx_id = rcta.customer_trx_id(+) -- and wdd.organization_id = ood.organization_id(+) and ood.operating_unit = haou1.organization_id(+) and wdd.customer_id = hca.cust_account_id(+) and hca.party_id = hp.party_id(+) and wdd.ship_to_site_use_id = hcsua.site_use_id(+) and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+) and hcasa.party_site_id = hps.party_site_id(+) and hps.location_id = hl.location_id(+) and hcasa.org_id = haou2.organization_id(+) -- and (wdd.organization_id is null or exists (select null from org_access_view oav where oav.organization_id = wdd.organization_id and oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) and ( haou1.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) or haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) or (haou1.organization_id is null and haou2.organization_id is null) ) order by selling_operating_unit, shipping_operating_unit, organization_code, delivery_note_date, delivery_note_id, delivery_detail_id Y (:Selling_Operating_Unit is not null or :Shipping_Operating_Unit is not null or :Organization_Code is not null) US WSH Shipping/Delivery Transactions This report provides details of Warehouse Shipping Transactions and Deliveries. To review details of deliveries only, set the Assigned to Delivery Parameter = 'Yes' To review details of shipping transaction not yet assigned to a delivery, set the Assigned to Delivery Parameter = 'No' Set the parameter to null to review all shipping transations regardless of delivery assignment status. Please specify a value for at least one of the following parameters: Selling Operating Unit, Shipping Operating Unit, Organization Code Enginatics 1=1 2=2 :p_loc_format 1 10 1=1 haou2.name=:p_selling_ou 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é opérationnelle de vente US Selling Operating Unit 2 20 1=1 haou1.name=:p_shipping_ou 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 F Unité d'exploitation d'expédition US Shipping Operating Unit 3 30 1=1 ood.organization_code=:p_organization_code LOV custom Y select mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.shipping_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$.shipping_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 mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') F Code de l'organisation US Organization Code 4 40 1=1 wnd.delivery_id is not null LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y F Affecté à la livraison US Assigned to Delivery 5 1=1 wnd.delivery_id is null N F Affecté à la livraison US Assigned to Delivery 6 50 1=1 wnd.status_code=xxen_util.lookup_code(:p_delivery_status,'DELIVERY_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='DELIVERY_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning F État de la livraison US Delivery Status 7 60 1=1 nvl(wnd.status_code,'OP')!=xxen_util.lookup_code(:p_delivery_status_excl,'DELIVERY_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='DELIVERY_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning Closed F Exclure l'état de la livraison US Exclude Delivery Status 8 70 1=1 wdd.released_status=xxen_util.lookup_code(:p_release_status,'PICK_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='PICK_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning F État de la libération US Release status 9 80 1=1 wdd.released_status!=xxen_util.lookup_code(:p_release_status_excl,'PICK_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='PICK_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning Cancelled F Exclure le statut de libération US Exclude Release Status 10 90 1=1 exists ( select null from wsh_exceptions_v wev where wev.delivery_detail_id = wdd.delivery_detail_id and status = 'OPEN' and severity = 'ERROR' ) LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y F Existence d'une exception US Exception Exists 11 1=1 not exists ( select null from wsh_exceptions_v wev where wev.delivery_detail_id = wdd.delivery_detail_id and status = 'OPEN' and severity = 'ERROR' ) N F Existence d'une exception US Exception Exists 12 100 1=1 hp.party_name=:p_party LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name F Nom du client US Customer Name 13 110 1=1 hca.account_number=:p_account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name F Numéro de client US Customer Number 14 120 1=1 wnd.name>=:p_delivery_from LOV custom select wnd.name value, null description from wsh_new_deliveries wnd where (:$flex$.organization_code is null or wnd.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y') ) and nvl(wnd.shipment_direction,'O') in ('O','IO') and wnd.delivery_type='STANDARD' order by wnd.name F Bon de livraison de US Delivery Note From 15 130 1=1 wnd.name<=:p_delivery_to LOV custom Y select wnd.name value, null description from wsh_new_deliveries wnd where (:$flex$.organization_code is null or wnd.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y') ) and nvl(wnd.shipment_direction,'O') in ('O','IO') and wnd.delivery_type='STANDARD' order by wnd.name $flex$.delivery_note_from F Note de livraison à US Delivery Note To 16 140 1=1 msiv.concatenated_segments=:p_item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 17 150 1=1 msiv.concatenated_segments>=:p_item_from LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments F Article de US Item From 18 160 1=1 msiv.concatenated_segments<=:p_item_to LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments :$flex$.item_from F Objet à US Item To 19 170 1=1 (wdd.lot_number = :p_lot_number or nvl(shipped_lots.lot_numbers,picked_lots.lot_numbers) is not null) LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number 20 2=2 mtln.lot_number = :p_lot_number US Lot Number 21 180 1=1 (wdd.lot_number >= :p_lot_number_from or nvl(shipped_lots.lot_numbers,picked_lots.lot_numbers) is not null) LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number From 22 2=2 mtln.lot_number >= :p_lot_number_from US Lot Number From 23 190 1=1 (wdd.lot_number <= :p_lot_number_to or nvl(shipped_lots.lot_numbers,picked_lots.lot_numbers) is not null) LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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 msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number To 24 2=2 mtln.lot_number <= :p_lot_number_to US Lot Number To 25 200 1=1 wdd.source_document_number>=:p_order_num_from LOV custom Y Y select to_char(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$.selling_operating_unit is null 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) ) or ooha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.selling_operating_unit,haouv.name)='Y') ) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') and exists (select null from wsh_delivery_details wdd where wdd.source_header_number = to_char(ooha.order_number) ) order by to_char(ooha.order_number) desc F Numéro de commande De US Order Number From 26 210 1=1 wdd.source_document_number<=:p_order_num_to LOV custom Y Y select to_char(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$.selling_operating_unit is null 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) ) or ooha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.selling_operating_unit,haouv.name)='Y') ) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') and exists (select null from wsh_delivery_details wdd where wdd.source_header_number = to_char(ooha.order_number) ) order by to_char(ooha.order_number) desc :$flex$.order_number_to F Numéro de commande à US Order Number To 27 220 1=1 wdd.date_scheduled>=:p_sched_date_from Date F Date prévue du US Scheduled Date From 28 230 1=1 wdd.date_scheduled<:p_sched_date_to+1 Date :$flex$.scheduled_date_from F Date prévue jusqu'au US Scheduled Date To 29 240 1=1 wpb.creation_date>=:p_release_date_from Date F Date de sortie de US Released Date From 30 250 1=1 wpb.creation_date<:p_release_date_to+1 Date :$flex$.released_date_from F Date de sortie au US Released Date To 31 260 1=1 mmt.transaction_date>=:p_ship_date_from Date F Date d'expédition réelle De US Actual Shipment Date From 32 270 1=1 mmt.transaction_date<:p_ship_date_to+1 Date :$flex$.actual_shipment_date_from F Date réelle d'expédition à US Actual Shipment Date To 33 280 1=1 rcta.trx_date>=:p_ar_trx_date_from Date F Date de facturation de US Invoiced Date From 34 290 1=1 rcta.trx_date<:p_ar_trx_date_to+1 Date :$flex$.invoiced_date_from F Date de facturation au US Invoiced Date To 35 300 1=1 wnd.initial_pickup_date>=:p_pickup_date_from Date F Date de prise en charge initiale du US Initial Pick Up Date From 36 310 1=1 wnd.initial_pickup_date>:p_pickup_date_to+1 Date :$flex$.initial_pick_up_date_from F Date de prise en charge initiale au US Initial Pick Up Date To 37 320 1=1 wnd.ultimate_dropoff_date>=:p_dropoff_date_from Date F Date de dépôt ultime De US Ultimate Drop Off Date From 38 330 1=1 wnd.ultimate_dropoff_date<:p_dropoff_date_to+1 Date :$flex$.ultimate_drop_off_date_from F Date de dépôt ultime à US Ultimate Drop Off Date To 39 340 :p_loc_format LOV custom Y select x.value, x.description from ( select 'City State Zip' value, 'City, State, Zip' description from dual union select 'City State Zip Country' value, 'City, State, Zip, Country' description from dual union select 'Code City' value,'Location Code, City' description from dual union select 'Code Address1 City' value,'Location Code, Address1, City' description from dual ) x order by x.value Code City US Location Format :$flex$.actual_shipment_date_from Actual Shipment Date From Y Actual Shipment Date To :$flex$.initial_pick_up_date_from Initial Pick Up Date From Y Initial Pick Up Date To :$flex$.invoiced_date_from Invoiced Date From Y Invoiced Date To :$flex$.item Item Lot Number :$flex$.item Item Lot Number From :$flex$.item Item Lot Number To :$flex$.item_from Item From Item To :$flex$.item_from Item From Lot Number :$flex$.item_from Item From Lot Number From :$flex$.item_from Item From Lot Number To :$flex$.item_to Item To Lot Number :$flex$.item_to Item To Lot Number From :$flex$.item_to Item To Lot Number To :$flex$.order_number_to Order Number To Order Number To :$flex$.organization_code Organization Code Delivery Note From :$flex$.organization_code Organization Code Delivery Note To :$flex$.organization_code Organization Code Item :$flex$.organization_code Organization Code Item From :$flex$.organization_code Organization Code Item To :$flex$.organization_code Organization Code Lot Number :$flex$.organization_code Organization Code Lot Number From :$flex$.organization_code Organization Code Lot Number To :$flex$.released_date_from Released Date From Y Released Date To :$flex$.scheduled_date_from Scheduled Date From Y Scheduled Date To :$flex$.selling_operating_unit Selling Operating Unit Order Number From :$flex$.selling_operating_unit Selling Operating Unit Order Number To :$flex$.shipping_operating_unit Shipping Operating Unit Organization Code :$flex$.ultimate_drop_off_date_from Ultimate Drop Off Date From Y Ultimate Drop Off Date To