IPLUPGRADE/REPORTS/OBHOISTLK/Sales_Report.sql

Select INV.Company,
       Company.Name,
       SS.Acct_Currency_Code as "Curr Company",
       INV.Contract "Site",
       INV.Catalog_No as "Part No",
       INV.Description,
       TO_CHAR(INV.Invoice_Date, 'YYYY-MM-DD') "Date Trans",
       TO_CHAR(INV.Invoice_Date, 'YYYY-MM') "YYYY-MM",
       Case
         when Inv.Price_Adjustment = 'TRUE' then
          0
         else
          Inv.Invoiced_Qty
       End "Invoiced Qty",
       INV.Sale_Um UoM,
       STOCK.Part_Product_Family "Product Family",
       STOCK.Part_Product_Code "Product Code",
       STOCK.Type_Designation "Type Designation",
       INV.Identity "Customer No",
       CUST.Name "Customer Name",
       CUST.Category "Customer Type",
       IPP.CF$_QTY_ON_HAND "Qty on Hand",
       IPP.Safety_Stock "Safety Stock",
       IPP.MIN_ORDER_QTY "Min Lot Size",
       IPP.CF$_MIN_SALES_QTY "Min Sales Qty",
       STOCK.CF$_HU_QTY "HU Qty",
       SS.Price_Source "Price Source",
       SS.Price_Source_Id "Price Source ID",
       customer_order.order_id "Order Type",
       INV.Order_No "Order No",
       INV.Line_No "Line No",
       INV.Release_No "Rel No",
       INV.Invoice_No "Invoice No",
       cust_order_inv_head_uiv_all.DELIVERY_ADDRESS_ID "Address ID",
       SHIP.Name "Ship Name",
       SHIP.Country "Ship Ctry",
       SHIP.State "Ship State",
       SHIP.City "Ship City",
       SS.Cf$_Main_Rep "Main Rep",
       SS.Market_Desc "Market Desc",
       SS.Region_Code "Region Code",
       SS.Country_Code "Ctry Code",
       Case
         when customer_order.District_Code is null then
          SS.District_Code
         else
          customer_order.District_Code
       end as "District Code",
       Case
         when customer_order.District_Code is null then
          SS.District_Desc
         else
          SALES_DISTRICT.Description
       end as "District Description",
       customer_order.Delivery_Terms "Delivery Terms",
       INV.Customer_Po_No "Customer PO NO",
       cust_order_inv_head_uiv_all.Shipment_Id "Shipment ID",
       shipment_cfv.Cf$_Freight_Charge "Ttl Freight Cost",
       STOCK.C_Resin_Type "Resin Type",
       STOCK.C_Resin_Weight "Resin Weight",
       Case
         when Inv.Price_Adjustment = 'TRUE' then
          0
         else
          (INV.Invoiced_Qty * STOCK.C_Resin_Weight * Sales_Part.CONV_FACTOR)
       End "Ttl Resin Weight",
       INV.Sale_Unit_Price "Sales Unit Price",
       INV.Currency_Code "Currency Code",
       INV.Net_Curr_Amount "Net Amount Curr",
       INV.Net_Dom_Amount "Net Amount Co",
       SS.Cost,
       Sales_Part.CONV_FACTOR "Factor UoM",
       COJ.Supply_Code "Supply Code",
       COJ.Supply_Site "Supply Site",
       X.CUSTOMER_PART_NO "Customer Part No",
       Color.ATTR_VALUE_ALPHA "Color",
       Pant.ATTR_VALUE_ALPHA "Pantone",
       IML.Component_Part "IML"

  from CUSTOMER_ORDER_INV_ITEM_JOIN INV
 inner join company
    on INV.Company = Company.Company
 inner join inventory_part_cfv STOCK
    on INV.Catalog_No = STOCK.Part_No
   and INV.Contract = STOCK.Contract
  left join cust_ord_cust1 CUST
    on INV.Identity = CUST.Customer_No
  left join cust_ord_invo_stat_cfv SS
    on INV.Invoice_id = SS.Invoice_id
   and INV.Item_id = SS.Item_id
  left join customer_order
    on INV.Order_No = customer_order.Order_No
  left join cust_order_inv_head_uiv_all
    on INV.Invoice_No = cust_order_inv_head_uiv_all.Invoice_No
   and INV.Series_ID = cust_order_inv_head_uiv_all.Series_Id
  left join shipment_cfv
    on cust_order_inv_head_uiv_all.shipment_Id = shipment_cfv.shipment_Id
  left join SALES_DISTRICT
    on customer_order.District_Code = SALES_DISTRICT.District_Code
 inner join SALES_PART
    on INV.Contract = SALES_PART.Contract
   and INV.Catalog_No = SALES_PART.Part_no
 inner join CUSTOMER_INFO_ADDRESS SHIP
    on INV.Identity = SHIP.Customer_Id
   and cust_order_inv_head_uiv_all.DELIVERY_ADDRESS_ID = SHIP.Address_Id
  left join CUSTOMER_ORDER_JOIN COJ
    on INV.Order_No = COJ.Order_No
   and INV.Line_No = COJ.Line_No
   and INV.Release_No = COJ.Rel_No
  left join (Select contract,
                    part_no,
                    CASE
                      WHEN substr(ATTR_VALUE_ALPHA, 1, 3) = 'DRY' THEN
                       'OFFFSET'
                      else
                       ATTR_VALUE_ALPHA
                    end Decoration
               from INVENTORY_PART_CHAR_ALL
              where CHARACTERISTIC_CODE = '00034') C34
    on INV.Contract = C34.Contract
   and INV.Catalog_No = C34.Part_No
 inner join INVENTORY_PART_PLANNING_cfv IPP
    on IPP.Contract = INV.Contract
   and IPP.Part_No = INV.Catalog_No
  left join SALES_PART_CROSS_REFERENCE X
    on X.Contract = INV.Contract
   and X.Catalog_No = INV.Catalog_No
   and X.CUSTOMER_NO = INV.Identity
  left join (Select contract, part_no, ATTR_VALUE_ALPHA
               from INVENTORY_PART_CHAR_ALL
              where CHARACTERISTIC_CODE in ('00025', '00028')) Color
    on Color.Contract = INV.Contract
   and Color.PArt_No = INV.Catalog_No
  left join (Select contract, part_no, ATTR_VALUE_ALPHA
               from INVENTORY_PART_CHAR_ALL
              where CHARACTERISTIC_CODE in ('00079')) Pant
    on Pant.Contract = INV.Contract
   and Pant.PArt_No = INV.Catalog_No
  left join (Select MS.Contract,
                    MS.Part_No,
                    LISTAGG(MS.COMPONENT_PART, ' + ') WITHIN GROUP(Order By MS.Component_Part) Component_Part
               From MANUF_STRUCTURE MS
              inner join Purchase_Part_Cfv PP
                 on MS.Contract = PP.Contract
                and MS.Component_Part = PP.Part_No
             
              Where PP.CF$_PURCHASE_PART_CAT_2 = 'RM07A'
                and MS.Alternative_No = '*'
                and MS.BOM_TYPE_DB = 'M'
                and MANUF_STRUCT_ALTERNATE_API.Get_State(MS.contract,
                                                         MS.part_no,
                                                         MS.eng_chg_level,
                                                         MS.bom_type,
                                                         MS.alternative_no) =
                    'Buildable'
                and MS.EFF_PHASE_OUT_DATE is null
             
              Group By MS.Contract, MS.Part_No) IML
    on IML.Contract = INV.Contract
   and IML.Part_No = INV.Catalog_No

 where INV.Series_ID <> 'PR'
   and INV.COMPANY LIKE NVL('&COMPANY', '%')
   and INV.CONTRACT LIKE NVL('%CONTRACT%', '%')
   and TO_DATE(TO_CHAR(INV.Invoice_Date, 'YYYY-MM-DD'), 'YYYY-MM-DD') between
       to_date('&BeginDate', 'YYYY-MM-DD') and
       to_date('&EndDate', 'YYYY-MM-DD')
   and INV.Identity like NVL('&Customer_No', '%')
   and STOCK.Type_Designation like NVL('&Type_Designation', '%')

-- Lov reference is removed due to limitations in IFS cloud