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.
47 lines
2.1 KiB
SQL
47 lines
2.1 KiB
SQL
select A.contract as "Site",
|
|
A.order_no as "Order",
|
|
A.line_no as "Line No",
|
|
A.Rel_no as "Rel No",
|
|
A.line_item_no as "Line Item No",
|
|
A.SUPPLY_CODE as "Supply Code",
|
|
A.VENDOR_NO as "Supplier",
|
|
A.customer_no as "Customer",
|
|
A.customer_name as "Customer Name",
|
|
A.catalog_no as "Part",
|
|
A.catalog_desc as "Part Description",
|
|
B.PART_PRODUCT_FAMILY as "Product family code",
|
|
Inventory_Product_Family_API.Get_Description(B.PART_PRODUCT_FAMILY) as "Product Family Description",
|
|
B.PART_PRODUCT_CODE as "Product code",
|
|
Inventory_Product_Code_API.Get_Description(B.PART_PRODUCT_CODE) as "Product Code Description",
|
|
B.TYPE_DESIGNATION as "Type Designation",
|
|
CUSTOMER_ORDER_API.GET_CUSTOMER_PO_NO(A.ORDER_NO) as "Customer PO No",
|
|
A.order_state as "Order Status",
|
|
A.state as "Status",
|
|
TRUNC(A.WANTED_DELIVERY_DATE) as "Wanted Delivery Date",
|
|
TRUNC((A.DELIVERY_LEADTIME + sysdate) - 1) -
|
|
TRUNC(A.PROMISED_DELIVERY_DATE) as "Days Late",
|
|
A.buy_qty_due as "Sales Qty",
|
|
Customer_Order_Line_API.Get_Base_Sale_Price_Total(A.ORDER_NO,
|
|
A.LINE_NO,
|
|
A.REL_NO,
|
|
A.LINE_ITEM_NO) as "Value (curr)",
|
|
A.Currency_code as "Currency",
|
|
PERSON_INFO_API.Get_Name(CRM_CUST_INFO_API.Get_Main_Representative_Id(A.customer_no)) as "Main Rep",
|
|
SALES_DISTRICT_API.get_description(A.DISTRICT_CODE) as "Sales District"
|
|
|
|
from customer_order_join_cfv A
|
|
inner join inventory_part B
|
|
on A.catalog_no = B.part_no
|
|
and A.contract = B.contract
|
|
|
|
Where (A.objstate = 'Released' or A.objstate = 'Blocked' or
|
|
A.objstate = 'Reserved' or A.objstate = 'Picked' or
|
|
A.objstate = 'PartiallyDelivered')
|
|
|
|
AND A.CONTRACT LIKE '%&CONTRACT%'
|
|
AND CF$_COORDINATOR LIKE '%&AUTHORIZE_CODE%'
|
|
|
|
ORDER BY A.contract, A.promised_delivery_date
|
|
|
|
-- Lov reference is removed due to limitations in IFS cloud
|