CREATE OR REPLACE VIEW C_INVOICE_HEADER_V
AS 
SELECT i.AD_Client_ID AS AD_Client_ID, i.AD_Org_ID AS AD_Org_ID, i.IsActive AS IsActive,
    i.Created AS Created, i.CreatedBy AS CreatedBy, i.Updated AS Updated, i.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    i.C_Invoice_ID AS C_Invoice_ID, i.IsSOTrx AS IsSOTrx,
    i.DocumentNo AS DocumentNo, i.DocStatus AS DocStatus, i.C_DocType_ID AS C_DocType_ID,
    i.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,
    dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
    i.C_Order_ID AS C_Order_ID, i.SalesRep_ID AS SalesRep_ID, COALESCE(ubp.Name,u.Name) AS SalesRep_Name,
    i.DateInvoiced AS DateInvoiced,  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,
	bpc.FirstName,
    bpl.C_Location_ID AS C_Location_ID, bp.ReferenceNo AS ReferenceNo,
    l.Postal || COALESCE(l.Postal_Add,N'') AS Postal,
    i.Description AS Description,  i.POReference AS POReference,
    i.DateOrdered AS DateOrdered,  i.C_Currency_ID AS C_Currency_ID,
	TO_NCHAR(PaymentTermDueDate(i.C_PaymentTerm_ID, DATEINVOICED), 'YYYY/MM/DD') AS PaymentTerm, 
    pt.DocumentNote AS PaymentTermNote,
    i.C_Charge_ID AS C_Charge_ID, i.ChargeAmt AS ChargeAmt,
    i.TotalLines AS TotalLines,  i.GrandTotal AS GrandTotal, i.GrandTotal AS AmtInWords,
    i.M_PriceList_ID AS M_PriceList_ID,  i.IsTaxIncluded AS IsTaxIncluded,
    i.C_Campaign_ID AS C_Campaign_ID,  i.C_Project_ID AS C_Project_ID,
    i.C_Activity_ID AS C_Activity_ID,  i.IsPaid AS IsPaid, 
	-- Japanese Special Report
	(select sum(TAXAMT) from C_INVOICETAX it where it.C_INVOICE_ID=i.C_INVOICE_ID) as TaxAmtTotal,
	CASE WHEN i.IsDiscountPrinted='Y' THEN (select sum(il.QTYINVOICED*(il.PRICELIST-il.PRICEACTUAL)) from C_INVOICELINE il where il.C_INVOICE_ID=i.C_INVOICE_ID) 
		ELSE 0
	END as DiscountAmtTotal,
	CASE WHEN i.IsSOTrx='Y' THEN bka.AccountNO
		ELSE bpbka.ACCOUNTNO 
	END  as AccountNO,
	CASE WHEN i.IsSOTrx='Y' THEN bka.BankAccountType 
		ELSE bpbka.BANKACCOUNTTYPE 
	END as BankAccountType, 
	CASE WHEN i.IsSOTrx='Y' THEN bka.BRANCHNAME
		ELSE bpbka.BRANCHNAME 
	END as BranchName,
	CASE WHEN i.IsSOTrx='Y' THEN bka.C_BANK_ID
		ELSE bpbka.C_BANK_ID 
	END as C_BANK_ID,
	CASE WHEN i.IsSOTrx='Y' THEN bka.A_NAME
		ELSE bpbka.A_NAME 
	END as AccountName,
	us.Name as OrgLastName, 
	us.FirstName as OrgFirstName, 
	us.Phone as OrgPhone,
	us.Fax as OrgFax

FROM  C_Invoice i
    INNER JOIN C_DocType dt ON (i.C_DocType_ID=dt.C_DocType_ID)
    INNER JOIN C_PaymentTerm pt ON (i.C_PaymentTerm_ID=pt.C_PaymentTerm_ID)
    INNER JOIN C_BPartner bp ON (i.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 (i.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 (i.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 (i.AD_Org_ID=oi.AD_Org_ID)
    LEFT OUTER JOIN AD_User u ON (i.SalesRep_ID=u.AD_User_ID)
    LEFT OUTER JOIN C_BPartner ubp ON (u.C_BPartner_ID=ubp.C_BPartner_ID)
	LEFT OUTER JOIN (SELECT obka.*, mbka.AD_ORG_ID as ORG_ID FROM C_BANKACCOUNT obka, 
		(SELECT MAX(C_BANKACCOUNT_ID) as C_BANKACCOUNT_ID, AD_CLIENT_ID, AD_ORG_ID FROM
			(SELECT BKA.C_BANKACCOUNT_ID, BKA.AD_CLIENT_ID, O.AD_ORG_ID
			FROM C_BANKACCOUNT BKA, AD_ORG O
			WHERE BKA.AD_CLIENT_ID = O.AD_CLIENT_ID
			AND BKA.AD_ORG_ID = 0
			AND BKA.ISACTIVE = 'Y'
			AND BKA.ISDEFAULT = 'Y'
			UNION
			SELECT BKA.C_BANKACCOUNT_ID, BKA.AD_CLIENT_ID, BKA.AD_ORG_ID
			FROM C_BANKACCOUNT BKA
			WHERE BKA.AD_ORG_ID<>0
			AND BKA.ISACTIVE = 'Y'
			AND BKA.ISDEFAULT = 'Y')
			GROUP BY AD_CLIENT_ID, AD_ORG_ID) mbka 
		WHERE obka.C_BANKACCOUNT_ID = mbka.C_BANKACCOUNT_ID ) bka on (i.AD_ORG_ID=bka.ORG_ID)
	LEFT OUTER JOIN C_BP_BANKACCOUNT bpbka on (bp.C_BPartner_ID=bpbka.C_BPARTNER_ID AND bpbka.ISACTIVE='Y')
	LEFT OUTER JOIN AD_USER us on (us.AD_USER_ID=oi.SUPERVISOR_ID)
/
