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.
45 lines
2.5 KiB
Plaintext
45 lines
2.5 KiB
Plaintext
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( '&CustomerStatisticsGroup' , '%')
|
|
AND sbc.CF$_CUSTOMER LIKE nvl ('&CustomerNo' , '%')
|
|
AND sbc.CF$_SALES_PART_NO LIKE NVL ('&SalesPartNo' , '%') |