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.
58 lines
2.8 KiB
SQL
58 lines
2.8 KiB
SQL
SELECT CF$_SITE as "Plant",
|
|
CF$_YEAR as "Year",
|
|
CF$_PERIOD as "Month",
|
|
CF$_CUSTOMER as "Customer Number",
|
|
Customer_Group_API.Get_Description(CUST_ORD_CUSTOMER_API.Get_Cust_Grp(CF$_CUSTOMER)) "Cust Stat Group",
|
|
CUSTOMER_INFO_API.Get_Name(CF$_CUSTOMER) as "Customer Name",
|
|
CF$_SALES_PART_NO as "Part Number",
|
|
Sales_Part_API.Get_Catalog_Desc(CF$_SITE, CF$_SALES_PART_NO) as "Part Description",
|
|
CF$_BUDGETED_QUANTITY as "Budget Sales (qty)",
|
|
CF$_BUDGETED_SALES_REVENUE as "Budget Sales ($)",
|
|
|
|
(SELECT SUM(INVOICED_QTY)
|
|
from CUST_ORD_INVO_STAT stat
|
|
where EXTRACT(YEAR FROM TO_DATE(INVOICE_DATE)) = CF$_YEAR
|
|
AND EXTRACT(MONTH FROM TO_DATE(INVOICE_DATE)) = CF$_PERIOD
|
|
AND CUSTOMER_NO = CF$_CUSTOMER
|
|
AND PART_NO = CF$_SALES_PART_NO) "Invoiced Sales (Qty)",
|
|
|
|
(SELECT SUM(NET_AMOUNT)
|
|
from CUST_ORD_INVO_STAT stat
|
|
where EXTRACT(YEAR FROM TO_DATE(INVOICE_DATE)) = CF$_YEAR
|
|
AND EXTRACT(MONTH FROM TO_DATE(INVOICE_DATE)) = CF$_PERIOD
|
|
AND CUSTOMER_NO = CF$_CUSTOMER
|
|
AND PART_NO = CF$_SALES_PART_NO) "Invoiced Sales ($)",
|
|
|
|
(SELECT SUM(BUY_QTY_DUE)
|
|
from CUSTOMER_ORDER_JOIN stat
|
|
where EXTRACT(YEAR FROM TO_DATE(PLANNED_DELIVERY_DATE)) = CF$_YEAR
|
|
AND EXTRACT(MONTH FROM TO_DATE(PLANNED_DELIVERY_DATE)) =
|
|
CF$_PERIOD
|
|
AND CUSTOMER_NO = CF$_CUSTOMER
|
|
AND STATE NOT IN ('Cancelled', 'Invoiced/Closed')
|
|
AND PART_NO = CF$_SALES_PART_NO) "Open Orders (Qty)",
|
|
|
|
(SELECT SUM(Customer_Order_Line_API.Get_Base_Sale_Price_Total(ORDER_NO,
|
|
LINE_NO,
|
|
REL_NO,
|
|
LINE_ITEM_NO))
|
|
from CUSTOMER_ORDER_JOIN stat
|
|
where EXTRACT(YEAR FROM TO_DATE(PLANNED_DELIVERY_DATE)) = CF$_YEAR
|
|
AND EXTRACT(MONTH FROM TO_DATE(PLANNED_DELIVERY_DATE)) =
|
|
CF$_PERIOD
|
|
AND CUSTOMER_NO = CF$_CUSTOMER
|
|
AND STATE NOT IN ('Cancelled', 'Invoiced/Closed')
|
|
AND PART_NO = CF$_SALES_PART_NO) "Open Orders ($)"
|
|
|
|
FROM IPL_SALES_BUDGET_CLV sbc
|
|
WHERE to_date(CF$_PERIOD || '/' || '01' || '/' || CF$_YEAR, 'MM/DD/YYYY') between
|
|
TO_DATE('&Valid_From', 'MM/DD/YYYY') and
|
|
TO_DATE('&Valid_To', 'MM/DD/YYYY')
|
|
AND sbc.cf$_Site LIKE NVL('&COMPANY', '%')
|
|
AND CUST_ORD_CUSTOMER_API.Get_Cust_Grp(sbc.CF$_CUSTOMER) LIKE
|
|
NVL('&CUST_GRP', '%')
|
|
AND sbc.CF$_CUSTOMER LIKE nvl('&CUSTOMER_NO', '%')
|
|
AND sbc.CF$_SALES_PART_NO LIKE
|
|
NVL('&CATALOG_NO', '%')
|
|
|
|
-- Lov reference is removed due to limitations in IFS cloud |