CREATE OR REPLACE VIEW M_INOUT_HEADER_VT
AS 
SELECT io.AD_Client_ID AS AD_Client_ID, io.AD_Org_ID AS AD_Org_ID, io.IsActive AS IsActive,
    io.Created AS Created, io.CreatedBy AS CreatedBy, io.Updated AS Updated, io.UpdatedBy AS UpdatedBy,
    dt.AD_Language AS AD_Language,
    io.M_InOut_ID AS M_InOut_ID, io.IsSOTrx AS IsSOTrx,
    io.DocumentNo AS DocumentNo, io.DocStatus AS DocStatus,
    io.C_DocType_ID AS C_DocType_ID, io.SalesRep_ID AS SalesRep_ID,
    io.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,
    io.M_Warehouse_ID AS M_Warehouse_ID, wh.C_Location_ID AS Warehouse_Location_ID,
    dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
    io.C_Order_ID AS C_Order_ID, 
    io.MovementDate AS MovementDate, io.MovementType AS MovementType,
    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,
    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,  io.Description AS Description,
    io.POReference AS POReference,  io.DateOrdered AS DateOrdered,
    io.Volume AS Volume, io.Weight AS Weight,
    io.M_Shipper_ID AS M_Shipper_ID, io.DeliveryRule AS DeliveryRule,
    io.DeliveryViaRule AS DeliveryViaRule, io.PriorityRule AS PriorityRule,
	-- Japanese Special Report
	COALESCE(bpc.Fax, bpl.Fax, NULL) as Fax,
	NVL(o.DatePromised, LAST_DAY(SYSDATE)) as DatePromised,
	us.Name as OrgLastName, 
	us.FirstName as OrgFirstName, 
	us.Phone as OrgPhone,
	us.Fax as OrgFax

FROM  M_InOut io
    INNER JOIN C_DocType_Trl dt ON (io.C_DocType_ID=dt.C_DocType_ID)
    INNER JOIN C_BPartner bp ON (io.C_BPartner_ID=bp.C_BPartner_ID)
    LEFT OUTER JOIN C_Greeting_Trl bpg ON (bp.C_Greeting_ID=bpg.C_Greeting_ID AND dt.AD_Language=bpg.AD_Language)
    INNER JOIN C_BPartner_Location bpl ON (io.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 (io.AD_User_ID=bpc.AD_User_ID)
    LEFT OUTER JOIN C_Greeting_Trl bpcg ON (bpc.C_Greeting_ID=bpcg.C_Greeting_ID AND dt.AD_Language=bpcg.AD_Language)
    INNER JOIN AD_OrgInfo oi ON (io.AD_Org_ID=oi.AD_Org_ID)
    INNER JOIN M_Warehouse wh ON (io.M_Warehouse_ID=wh.M_Warehouse_ID)
	-- Japanese Special Report
	LEFT OUTER JOIN C_ORDER o ON (io.C_ORDER_ID=o.C_ORDER_ID and o.DateOrdered IS NOT NULL)
	LEFT OUTER JOIN AD_User us on (us.AD_USER_ID=oi.SUPERVISOR_ID)
/
