CREATE OR REPLACE VIEW C_ORDER_HEADER_V
AS 
SELECT o.AD_Client_ID AS AD_Client_ID, o.AD_Org_ID AS AD_Org_ID, o.IsActive AS IsActive, 
    o.Created AS Created, o.CreatedBy AS CreatedBy, o.Updated AS Updated, o.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    o.C_Order_ID AS C_Order_ID, o.IsSOTrx AS IsSOTrx,
    o.DocumentNo AS DocumentNo, o.DocStatus AS DocStatus,
    o.C_DocType_ID AS C_DocType_ID,
    o.C_BPartner_ID AS C_BPartner_ID, bp.Value AS BPValue,
    bp.TaxID AS BPTaxID, bp.NAICS AS NAICS, bp.DUNS AS DUNS,
    oi.C_Location_ID AS Org_Location_ID, oi.TaxID AS TaxID,
    o.M_Warehouse_ID AS M_Warehouse_ID, wh.C_Location_ID AS Warehouse_Location_ID,
    dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
    o.SalesRep_ID AS SalesRep_ID, COALESCE(ubp.Name,u.Name) AS SalesRep_Name,
    o.DateOrdered AS DateOrdered, o.DatePromised AS DatePromised,
    bpg.Greeting AS BPGreeting,
    bp.Name AS Name, bp.Name2 AS Name2,
    bpcg.Greeting AS BPContactGreeting, bpc.Title AS Title, 
	coalesce(bpc.Phone, bpc.Phone2, bpl.Phone, bpl.Phone2, null) as Phone,
	nvl (bpc.Fax, bpl.Fax) as fax,
    NULLIF (bpc.Name,bp.Name) AS ContactName,
    bpl.C_Location_ID AS C_Location_ID, l.Postal || COALESCE(l.Postal_Add,N'') AS Postal,
    bp.ReferenceNo AS ReferenceNo,
    o.Bill_BPartner_ID AS Bill_BPartner_ID, o.Bill_Location_ID AS Bill_Location_ID, o.Bill_User_ID AS Bill_User_ID,
    bbp.Value AS Bill_BPValue, bbp.TaxID AS Bill_BPTaxID,
    bbp.Name AS Bill_Name, bbp.Name2 AS Bill_Name2,
    bbpc.Title AS Bill_Title, bbpc.Phone AS Bill_Phone,
    NULLIF (bbpc.Name,bbp.Name) AS Bill_ContactName,
    bbpl.C_Location_ID AS Bill_C_Location_ID,  o.Description AS Description,
    o.POReference AS POReference,
    o.C_Currency_ID AS C_Currency_ID,
    pt.Name AS PaymentTerm, pt.DocumentNote AS PaymentTermNote,
    o.C_Charge_ID AS C_Charge_ID, o.ChargeAmt AS ChargeAmt,
    o.TotalLines AS TotalLines,  o.GrandTotal AS GrandTotal, o.GrandTotal AS AmtInWords,
    o.M_PriceList_ID AS M_PriceList_ID,
    o.IsTaxIncluded AS IsTaxIncluded, o.Volume AS Volume, o.Weight AS Weight,
    o.C_Campaign_ID AS C_Campaign_ID, o.C_Project_ID AS C_Project_ID, o.C_Activity_ID AS C_Activity_ID,
    o.M_Shipper_ID AS M_Shipper_ID, o.DeliveryRule AS DeliveryRule, o.DeliveryViaRule AS DeliveryViaRule,
    o.PriorityRule AS PriorityRule, o.InvoiceRule AS InvoiceRule, 
    -- Japan Spec
    (select SUM(TAXAMT) from C_ordertax ot WHERE ot.C_order_ID=o.C_order_ID) AS TaxAmtTotal,
    case when o.IsDiscountPrinted='Y' THEN (select sum(ol.QTYORDERED*(ol.PRICELIST-ol.PRICEACTUAL)) 
                                             from C_ORDERLINE ol where ol.C_ORDER_ID=o.C_ORDER_ID) 
    	else 0 
    end as DiscountAmtTotal,
    us.Name as OrgLastName, us.FirstName as OrgFirstName, us.Fax as OrgFax, us.Phone as OrgPhone

FROM  C_Order o
    INNER JOIN C_DocType dt ON (o.C_DocType_ID=dt.C_DocType_ID)
    INNER JOIN M_Warehouse wh ON (o.M_Warehouse_ID=wh.M_Warehouse_ID)
    INNER JOIN C_PaymentTerm pt ON (o.C_PaymentTerm_ID=pt.C_PaymentTerm_ID)
    INNER JOIN C_BPartner bp ON (o.C_BPartner_ID=bp.C_BPartner_ID)
    LEFT OUTER JOIN C_Greeting bpg ON (bp.C_Greeting_ID=bpg.C_Greeting_ID)
    INNER JOIN C_BPartner_Location bpl ON (o.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
    INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)
    LEFT OUTER JOIN AD_User bpc ON (o.AD_User_ID=bpc.AD_User_ID)
    LEFT OUTER JOIN C_Greeting bpcg ON (bpc.C_Greeting_ID=bpcg.C_Greeting_ID)
    INNER JOIN AD_OrgInfo oi ON (o.AD_Org_ID=oi.AD_Org_ID)
    LEFT OUTER JOIN AD_User u ON (o.SalesRep_ID=u.AD_User_ID)
    LEFT OUTER JOIN C_BPartner ubp ON (u.C_BPartner_ID=ubp.C_BPartner_ID)
    INNER JOIN C_BPartner bbp ON (o.Bill_BPartner_ID=bbp.C_BPartner_ID)
    INNER JOIN C_BPartner_Location bbpl ON (o.Bill_Location_ID=bbpl.C_BPartner_Location_ID)
    LEFT OUTER JOIN AD_User bbpc ON (o.Bill_User_ID=bbpc.AD_User_ID)
    -- Japan Spec
    LEFT OUTER JOIN AD_USER us on (us.AD_USER_ID=oi.SUPERVISOR_ID)
/
