You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
67 lines
2.6 KiB
SQL
67 lines
2.6 KiB
SQL
select a.contract as Site,
|
|
a.order_no,
|
|
a.RELEASE_NO,
|
|
a.SEQUENCE_NO,
|
|
a.state as Status,
|
|
a.demand_code,
|
|
a.part_no,
|
|
Inventory_Part_API.Get_Description(a.Contract, a.Part_No) as Part_Description,
|
|
e.attr_value_alpha as Colour,
|
|
a.revised_start_date as Start_Date,
|
|
a.revised_due_date as End_Date,
|
|
Shop_Ord_Util_API.Order_Is_Tardy(a.ORDER_NO,
|
|
a.RELEASE_NO,
|
|
a.SEQUENCE_NO) as Tardy,
|
|
a.revised_qty_due as Order_Lot_Size,
|
|
a.qty_complete,
|
|
a.QTY_ON_ORDER as Pegged_Qty,
|
|
INVENTORY_PART_API.Get_Unit_Meas(a.Contract, a.PART_NO) as UoM,
|
|
c.OPERATION_NO,
|
|
c.OPERATION_DESCRIPTION,
|
|
c.WORK_CENTER_NO,
|
|
WORK_CENTER_API.Get_Description(c.contract, c.WORK_CENTER_NO) as Work_Centre_Description,
|
|
c.RESOURCE_ID,
|
|
d.TOOL_ID,
|
|
MANUF_TOOL_API.Get_Tool_Description(d.contract, d.TOOL_ID) as Tool_Description,
|
|
a.customer_no,
|
|
CUSTOMER_INFO_API.Get_Name(a.customer_no) as Customer_Name,
|
|
a.customer_order_no,
|
|
a.customer_line_no as Customer_Order_Line,
|
|
a.customer_rel_no as Customer_Order_Del_No,
|
|
b.BUY_QTY_DUE as Sales_Qty,
|
|
CUSTOMER_ORDER_API.Get_Customer_Po_No(a.customer_order_no) as Customer_PO,
|
|
b.authorize_code as Coordinator_ID,
|
|
ORDER_COORDINATOR_API.Get_Name(b.AUTHORIZE_CODE) as Coordinator_Name,
|
|
CUSTOMER_ORDER_API.Get_District_Code(a.customer_order_no) as District,
|
|
SALES_DISTRICT_API.Get_Description(CUSTOMER_ORDER_API.Get_District_Code(a.customer_order_no)) as District_Name,
|
|
a.SERIAL_BEGIN,
|
|
a.SERIAL_END,
|
|
Customer_Order_Line_Cfp.Get_Cf$_Max_Cap_Per_Pallet(a.part_no) as Qty_per_Pallet
|
|
|
|
from IFSAPP.shop_ord a
|
|
left outer join CUSTOMER_ORDER_JOIN b
|
|
on a.customer_order_no = b.order_no
|
|
and a.customer_line_no = b.line_no
|
|
and a.customer_rel_no = b.rel_no
|
|
join SHOP_ORDER_OPERATION c
|
|
on a.order_no = c.order_no
|
|
and a.release_no = c.release_no
|
|
and a.sequence_no = c.sequence_no
|
|
left outer join SHOP_ORDER_OPER_TOOL d
|
|
on a.order_no = d.order_no
|
|
and a.release_no = d.release_no
|
|
and a.sequence_no = d.sequence_no
|
|
left outer join SALES_PART_CHARACTERISTIC e
|
|
on a.part_no = e.catalog_no
|
|
and a.contract = e.contract
|
|
|
|
where a.objstate <> 'Closed'
|
|
and a.objstate <> 'Cancelled'
|
|
and (e.characteristic_code = '00028' or e.characteristic_code = '00005' or
|
|
e.characteristic_code = '00025')
|
|
and a.CONTRACT LIKE '%&CONTRACT%'
|
|
|
|
order by a.contract, c.RESOURCE_ID, a.revised_start_date
|
|
|
|
-- Lov reference is removed due to limitations in IFS cloud
|