1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
| SELECT ooh.order_number "commande n"
,hp.party_name "nom dentiste"
,msi.segment1 "code article"
,upper(mut.subinventory_code) "stock"
,decode(mut.locator_id,-1,null,mil.segment1) "emplacement"
,to_char(ooh.ordered_date,'DD/MM/YYYY') "ordered date"
,to_char(ool.schedule_ship_date,'DD/MM/YYYY') "schedule ship date"
,to_char(ool.promise_date,'DD/MM/YYYY') "promise date"
,trunc(sysdate - ooh.ordered_date) "nombre jours depuis reception"
,ooh.flow_status_code "statut commande"
,upper(flv_released.meaning) "statut expedition"
,ooh.attribute7 "blocage commande"
,upper(ooh.attribute10) "statut ss patient"
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
fnd_lookup_values flv_released,
oe_transaction_types_tl ott,
mtl_system_items msi,
mtl_unit_transactions mut,
mtl_item_locations mil,
hz_cust_site_uses_all hcsus,
hz_cust_acct_sites_all hcass,
hz_cust_accounts hcas,
hz_parties hp
WHERE ooh.header_id = ool.header_id
AND ooh.order_type_id = ott.transaction_type_id
AND wdd.source_line_id = ool.line_id
AND wdd.released_status = flv_released.lookup_code
AND flv_released.lookup_type = 'PICK_STATUS'
AND flv_released.language = 'US'
AND ool.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = 120
AND ott.language = 'F'
AND ooh.order_type_id = 3435
AND ooh.org_id = 119
AND msi.segment1 IN ('200000','200001','200002') -- ORTHÈSE
AND mut.serial_number = to_char(ooh.order_number)
AND mut.locator_id = mil.inventory_location_id
AND mil.disable_date IS NULL
AND ooh.ship_to_org_id = hcsus.site_use_id
AND hcsus.cust_acct_site_id = hcass.cust_acct_site_id
AND hcass.cust_account_id = hcas.cust_account_id
AND hcas.party_id = hp.party_id
AND ooh.cancelled_flag = 'N'
AND mut.transaction_id = (SELECT max(mut2.transaction_id)
FROM mtl_unit_transactions mut2
WHERE mut2.serial_number = mut.serial_number)
AND ool.flow_status_code NOT IN ('CANCELLED','CLOSED')
AND ((mut.subinventory_code = 'PRODUCTION' and schedule_ship_date <= (sysdate-1))
OR (mut.subinventory_code = 'LOGISTIQUE')
OR (ool.promise_date <= (sysdate - 1)) )
AND (mil.inventory_location_id != -1 OR mil.inventory_location_id = -1 and mil.organization_id = 105)
--and ooh.order_number = '10013651'
ORDER BY schedule_ship_date; |
Partager