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.
79 lines
4.4 KiB
Plaintext
79 lines
4.4 KiB
Plaintext
SELECT contract,
|
|
EXTRACT(YEAR FROM DATE_CREATED) Year,
|
|
EXTRACT(MONTH FROM DATE_CREATED) Period,
|
|
IFSAPP.CUSTOMER_ORDER_API.Get_Customer_No(SOURCE_REF1) "Customer",
|
|
IFSAPP.Cust_Ord_Customer_API.Get_Name(CUSTOMER_ORDER_API.Get_Customer_No(SOURCE_REF1)) "Customer Name",
|
|
PART_NO,
|
|
IFSAPP.Inventory_Part_API.Get_Description(CONTRACT, PART_NO) "Part Description",
|
|
IFSAPP.INVENTORY_PART_API.Get_Part_Product_Family(CONTRACT, PART_NO) "Product Family",
|
|
SUM(QUANTITY) as Quantity,
|
|
ifsapp.inventory_part_api.GET_UNIT_MEAS(CONTRACT ,PART_NO) AS Units,
|
|
TO_CHAR(SUM((Customer_Order_Line_API.Get_Base_Sale_Price_Total(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4) /
|
|
(Customer_Order_Line_API.Get_Buy_Qty_Due(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4)*Customer_Order_Line_API.Get_Conv_Factor(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4) )) * Quantity),'999,999,999,999,999.99','NLS_NUMERIC_CHARACTERS=",."') as "Sales $ MDT",
|
|
|
|
|
|
|
|
/* SUM((Customer_Order_API.Get_Total_Base_Price(source_ref1) /
|
|
Customer_Order_Line_API.Get_Buy_Qty_Due(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4)) *
|
|
(Customer_Order_Line_API.Get_Buy_Qty_Due(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4) - Quantity)) as MTD2,*/
|
|
|
|
COUNT(HANDLING_UNIT_ID) as Pallets
|
|
FROM INVENTORY_TRANSACTION_HIST2
|
|
WHERE TRANSACTION_CODE = 'OESHIP'
|
|
AND (TRUNC(DATE_CREATED) >= to_date('&VALID_FROM','MM/DD/YYYY') AND
|
|
TRUNC(DATE_CREATED) <= to_date('&VALID_TO','MM/DD/YYYY'))
|
|
AND CONTRACT LIKE NVL('&Company', '%')
|
|
AND PART_NO LIKE nvl('&PartNo', '%')
|
|
AND (IFSAPP.CUSTOMER_ORDER_API.Get_Customer_No(SOURCE_REF1) is null or
|
|
IFSAPP.CUSTOMER_ORDER_API.Get_Customer_No(SOURCE_REF1) LIKE
|
|
NVL('&CustomerNo', '%'))
|
|
AND (IFSAPP.INVENTORY_PART_API.Get_Part_Product_Family(CONTRACT, PART_NO) is null or
|
|
IFSAPP.INVENTORY_PART_API.Get_Part_Product_Family(CONTRACT, PART_NO) LIKE
|
|
NVL('ProductFamily', '%'))
|
|
|
|
GROUP BY PART_NO,
|
|
contract,
|
|
EXTRACT(YEAR FROM DATE_CREATED),
|
|
EXTRACT(MONTH FROM DATE_CREATED),
|
|
IFSAPP.CUSTOMER_ORDER_API.Get_Customer_No(SOURCE_REF1),
|
|
IFSAPP.Cust_Ord_Customer_API.Get_Name(CUSTOMER_ORDER_API.Get_Customer_No(SOURCE_REF1)),
|
|
IFSAPP.Inventory_Part_API.Get_Description(CONTRACT, PART_NO),
|
|
IFSAPP.INVENTORY_PART_API.Get_Part_Product_Family(CONTRACT,
|
|
PART_NO),
|
|
ifsapp.inventory_part_api.GET_UNIT_MEAS(CONTRACT ,PART_NO)
|
|
/*,
|
|
((Customer_Order_API.Get_Total_Base_Price(source_ref1) /
|
|
SUM(Customer_Order_Line_API.Get_Buy_Qty_Due(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4))) *
|
|
Quantity)*/
|
|
/* ((Customer_Order_API.Get_Total_Base_Price(source_ref1) /
|
|
Customer_Order_Line_API.Get_Buy_Qty_Due(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4)) *
|
|
(Customer_Order_Line_API.Get_Buy_Qty_Due(source_ref1,
|
|
source_ref2,
|
|
source_ref3,
|
|
source_ref4) -
|
|
Quantity))*/
|
|
ORDER BY EXTRACT(YEAR FROM DATE_CREATED),
|
|
EXTRACT(MONTH FROM DATE_CREATED),
|
|
PART_NO,
|
|
CONTRACT |