View erp.COMPIERE.C_INVOICE_V

 Generated by
SchemaSpy
Implied relationships Related columns Constraint names Legend
Legend: SourceForge.net
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
Dashed lines show
implied relationships
< n > number of related tables
Please support this project 
Column Type Size Nulls Auto Default Children Parents
C_INVOICE_ID number 10  √  null
C_INVOICE C_INVOICE_ID Implied Constraint
AD_CLIENT_ID number 10  √  null
AD_CLIENTINFO AD_CLIENT_ID Implied Constraint
AD_ORG_ID number 10  √  null
AD_ORGINFO AD_ORG_ID Implied Constraint
ISACTIVE char 1  √  null
CREATED date 7  √  null
CREATEDBY number 10  √  null
UPDATED date 7  √  null
UPDATEDBY number 10  √  null
ISSOTRX char 1  √  null
DOCUMENTNO nvarchar2 60  √  null
DOCSTATUS char 2  √  null
DOCACTION char 2  √  null
PROCESSING char 1  √  null
PROCESSED char 1  √  null
C_DOCTYPE_ID number 10  √  null
C_DOCTYPE C_DOCTYPE_ID Implied Constraint
C_DOCTYPETARGET_ID number 10  √  null
C_ORDER_ID number 10  √  null
C_ORDER C_ORDER_ID Implied Constraint
DESCRIPTION nvarchar2 510  √  null
ISAPPROVED char 1  √  null
ISTRANSFERRED char 1  √  null
SALESREP_ID number 10  √  null
DATEINVOICED date 7  √  null
DATEPRINTED date 7  √  null
DATEACCT date 7  √  null
C_BPARTNER_ID number 10  √  null
C_BPARTNER C_BPARTNER_ID Implied Constraint
C_BPARTNER_LOCATION_ID number 10  √  null
C_BPARTNER_LOCATION C_BPARTNER_LOCATION_ID Implied Constraint
AD_USER_ID number 10  √  null
AD_USER AD_USER_ID Implied Constraint
POREFERENCE nvarchar2 40  √  null
DATEORDERED date 7  √  null
C_CURRENCY_ID number 10  √  null
C_CURRENCY C_CURRENCY_ID Implied Constraint
C_CONVERSIONTYPE_ID number 10  √  null
C_CONVERSIONTYPE C_CONVERSIONTYPE_ID Implied Constraint
PAYMENTRULE char 1  √  null
C_PAYMENTTERM_ID number 10  √  null
C_PAYMENTTERM C_PAYMENTTERM_ID Implied Constraint
C_CHARGE_ID number 10  √  null
C_CHARGE C_CHARGE_ID Implied Constraint
M_PRICELIST_ID number 10  √  null
M_PRICELIST M_PRICELIST_ID Implied Constraint
C_CAMPAIGN_ID number 10  √  null
C_CAMPAIGN C_CAMPAIGN_ID Implied Constraint
C_PROJECT_ID number 10  √  null
C_PROJECT C_PROJECT_ID Implied Constraint
C_ACTIVITY_ID number 10  √  null
C_ACTIVITY C_ACTIVITY_ID Implied Constraint
ISPRINTED char 1  √  null
ISDISCOUNTPRINTED char 1  √  null
ISPAID char 1  √  null
ISINDISPUTE char 1  √  null
ISPAYSCHEDULEVALID char 1  √  null
C_INVOICEPAYSCHEDULE_ID number 22  √  null
CHARGEAMT number 22  √  null
TOTALLINES number 22  √  null
GRANDTOTAL number 22  √  null
MULTIPLIER number 22  √  null
MULTIPLIERAP number 22  √  null
DOCBASETYPE char 3  √  null
View SQL:
SELECT i.C_Invoice_ID, i.AD_Client_ID, i.AD_Org_ID, i.IsActive, i.Created, i.CreatedBy, i.Updated, i.UpdatedBy, 
    i.IsSOTrx, i.DocumentNo, i.DocStatus, i.DocAction, i.Processing, i.Processed, i.C_DocType_ID, 
    i.C_DocTypeTarget_ID, i.C_Order_ID, i.Description, i.IsApproved, i.IsTransferred, 
    i.SalesRep_ID, i.DateInvoiced, i.DatePrinted, i.DateAcct, i.C_BPartner_ID, i.C_BPartner_Location_ID, 
    i.AD_User_ID, i.POReference, i.DateOrdered, i.C_Currency_ID, i.C_ConversionType_ID, i.PaymentRule, 
    i.C_PaymentTerm_ID, i.C_Charge_ID, i.M_PriceList_ID, i.C_Campaign_ID, i.C_Project_ID, 
    i.C_Activity_ID, i.IsPrinted, i.IsDiscountPrinted, i.IsPaid, i.IsInDispute,
    i.IsPayScheduleValid, null AS C_InvoicePaySchedule_ID,
    CASE WHEN charAt(d.DocBaseType,3)='C' THEN i.ChargeAmt*-1 ELSE i.ChargeAmt END AS ChargeAmt, 
    CASE WHEN charAt(d.DocBaseType,3)='C' THEN i.TotalLines*-1 ELSE i.TotalLines END AS TotalLines, 
    CASE WHEN charAt(d.DocBaseType,3)='C' THEN i.GrandTotal*-1 ELSE i.GrandTotal END AS GrandTotal, 
    CASE WHEN charAt(d.DocBaseType,3)='C' THEN -1 ELSE 1 END AS Multiplier,
    CASE WHEN charAt(d.DocBaseType,2)='P' THEN -1 ELSE 1 END AS MultiplierAP,
    d.DocBaseType
FROM C_Invoice i
    INNER JOIN C_DocType d ON (i.C_DocType_ID=d.C_DocType_ID)
WHERE i.IsPayScheduleValid<>'Y'
UNION
SELECT i.C_Invoice_ID, i.AD_Client_ID, i.AD_Org_ID, i.IsActive, i.Created, i.CreatedBy, i.Updated, i.UpdatedBy, 
    i.IsSOTrx, i.DocumentNo, i.DocStatus, i.DocAction, i.Processing, i.Processed, i.C_DocType_ID, 
    i.C_DocTypeTarget_ID, i.C_Order_ID, i.Description, i.IsApproved, i.IsTransferred, 
    i.SalesRep_ID, i.DateInvoiced, i.DatePrinted, i.DateAcct, i.C_BPartner_ID, i.C_BPartner_Location_ID, 
    i.AD_User_ID, i.POReference, i.DateOrdered, i.C_Currency_ID, i.C_ConversionType_ID, i.PaymentRule, 
    i.C_PaymentTerm_ID, i.C_Charge_ID, i.M_PriceList_ID, i.C_Campaign_ID, i.C_Project_ID, 
    i.C_Activity_ID, i.IsPrinted, i.IsDiscountPrinted, i.IsPaid, i.IsInDispute,
    i.IsPayScheduleValid, ips.C_InvoicePaySchedule_ID,
    null AS ChargeAmt, 
    null AS TotalLines, 
    CASE WHEN charAt(d.DocBaseType,3)='C' THEN ips.DueAmt*-1 ELSE ips.DueAmt END AS GrandTotal, 
    CASE WHEN charAt(d.DocBaseType,3)='C' THEN -1 ELSE 1 END AS Multiplier,
    CASE WHEN charAt(d.DocBaseType,2)='P' THEN -1 ELSE 1 END AS MultiplierAP,
    d.DocBaseType
FROM C_Invoice i
    INNER JOIN C_DocType d ON (i.C_DocType_ID=d.C_DocType_ID)
    INNER JOIN C_InvoicePaySchedule ips ON (i.C_Invoice_ID=ips.C_Invoice_ID)
WHERE i.IsPayScheduleValid='Y'
    AND ips.IsValid='Y'


SchemaSpy was unable to generate a graphical representation of table relationships.
An appropriate version of dot must be in your path when generating these pages.
Requires dot version 2.2.1 or versions greater than 2.4 from www.graphviz.org.