1. Tables and Views for Project Management
  2. PJB_XLA_INV_LINES_REF_V

PJB_XLA_INV_LINES_REF_V

Details

  • Schema: FUSION

  • Object owner: PJB

  • Object type: VIEW

Columns

Name

INVOICE_LINE_ID

INVOICE_LINE_NUM

INVOICE_ID

INVOICE_NUM

INVOICE_LINE_CONTRACT_ID

INVOICE_LINE_BILL_PLAN_ID

INVOICE_LINE_BILL_METHOD_ID

CREDITED_INVOICE_ID

CREDITED_INVOICE_LINE_ID

PAYMENT_TERM_ID

INV_LINE_ATTRIBUTE_CATEGORY

INVOICE_LINE_ATTRIBUTE1

INVOICE_LINE_ATTRIBUTE2

INVOICE_LINE_ATTRIBUTE3

INVOICE_LINE_ATTRIBUTE4

INVOICE_LINE_ATTRIBUTE5

INVOICE_LINE_ATTRIBUTE6

INVOICE_LINE_ATTRIBUTE7

INVOICE_LINE_ATTRIBUTE8

INVOICE_LINE_ATTRIBUTE9

INVOICE_LINE_ATTRIBUTE10

INVOICE_LINE_ATTRIBUTE11

INVOICE_LINE_ATTRIBUTE12

INVOICE_LINE_ATTRIBUTE13

INVOICE_LINE_ATTRIBUTE14

INVOICE_LINE_ATTRIBUTE15

CONTRACT_LINE_ID

CONTRACT_LINE

CONTRACT_LINE_NUMBER

CONT_LINE_ATTRIBUTE_CATEGORY

CONTRACT_LINE_ATTRIBUTE1

CONTRACT_LINE_ATTRIBUTE2

CONTRACT_LINE_ATTRIBUTE3

CONTRACT_LINE_ATTRIBUTE4

CONTRACT_LINE_ATTRIBUTE5

CONTRACT_LINE_ATTRIBUTE6

CONTRACT_LINE_ATTRIBUTE7

CONTRACT_LINE_ATTRIBUTE8

CONTRACT_LINE_ATTRIBUTE9

CONTRACT_LINE_ATTRIBUTE10

CONTRACT_LINE_ATTRIBUTE11

CONTRACT_LINE_ATTRIBUTE12

CONTRACT_LINE_ATTRIBUTE13

CONTRACT_LINE_ATTRIBUTE14

CONTRACT_LINE_ATTRIBUTE15

CONTRACT_LINE_ATTRIBUTE16

CONTRACT_LINE_ATTRIBUTE17

CONTRACT_LINE_ATTRIBUTE18

CONTRACT_LINE_ATTRIBUTE19

CONTRACT_LINE_ATTRIBUTE20

CONTRACT_LINE_ATTRIBUTE21

CONTRACT_LINE_ATTRIBUTE22

CONTRACT_LINE_ATTRIBUTE23

CONTRACT_LINE_ATTRIBUTE24

CONTRACT_LINE_ATTRIBUTE25

CONTRACT_LINE_ATTRIBUTE26

CONTRACT_LINE_ATTRIBUTE27

CONTRACT_LINE_ATTRIBUTE28

CONTRACT_LINE_ATTRIBUTE29

CONTRACT_LINE_ATTRIBUTE30

CON_LINE_CUST_PO_NUMBER

Query

SQL_Statement

SELECT

InvLines.INVOICE_LINE_ID INVOICE_LINE_ID,

InvLines.INVOICE_LINE_NUM INVOICE_LINE_NUM,

InvLines.INVOICE_ID INVOICE_ID,

InvLines.INVOICE_NUM INVOICE_NUM ,

InvLines.CONTRACT_ID INVOICE_LINE_CONTRACT_ID,

InvLines.BILL_PLAN_ID INVOICE_LINE_BILL_PLAN_ID,

BillPlan.BILL_METHOD_ID INVOICE_LINE_BILL_METHOD_ID,

InvLines.CREDITED_INVOICE_ID CREDITED_INVOICE_ID,

InvLines.CREDITED_INVOICE_LINE_ID CREDITED_INVOICE_LINE_ID,

InvLines.PAYMENT_TERM_ID PAYMENT_TERM_ID,

InvLines.ATTRIBUTE_CATEGORY INV_LINE_ATTRIBUTE_CATEGORY,

InvLines.ATTRIBUTE1 INVOICE_LINE_ATTRIBUTE1,

InvLines.ATTRIBUTE2 INVOICE_LINE_ATTRIBUTE2,

InvLines.ATTRIBUTE3 INVOICE_LINE_ATTRIBUTE3,

InvLines.ATTRIBUTE4 INVOICE_LINE_ATTRIBUTE4,

InvLines.ATTRIBUTE5 INVOICE_LINE_ATTRIBUTE5,

InvLines.ATTRIBUTE6 INVOICE_LINE_ATTRIBUTE6,

InvLines.ATTRIBUTE7 INVOICE_LINE_ATTRIBUTE7,

InvLines.ATTRIBUTE8 INVOICE_LINE_ATTRIBUTE8,

InvLines.ATTRIBUTE9 INVOICE_LINE_ATTRIBUTE9,

InvLines.ATTRIBUTE10 INVOICE_LINE_ATTRIBUTE10,

InvLines.ATTRIBUTE11 INVOICE_LINE_ATTRIBUTE11,

InvLines.ATTRIBUTE12 INVOICE_LINE_ATTRIBUTE12,

InvLines.ATTRIBUTE13 INVOICE_LINE_ATTRIBUTE13,

InvLines.ATTRIBUTE14 INVOICE_LINE_ATTRIBUTE14,

InvLines.ATTRIBUTE15 INVOICE_LINE_ATTRIBUTE15,

ContLines.ID CONTRACT_LINE_ID,

ContLines.ID CONTRACT_LINE,

ContLines.LINE_NUMBER CONTRACT_LINE_NUMBER,

ContLines.ATTRIBUTE_CATEGORY CONT_LINE_ATTRIBUTE_CATEGORY,

ContLines.ATTRIBUTE1 CONTRACT_LINE_ATTRIBUTE1,

ContLines.ATTRIBUTE2 CONTRACT_LINE_ATTRIBUTE2,

ContLines.ATTRIBUTE3 CONTRACT_LINE_ATTRIBUTE3,

ContLines.ATTRIBUTE4 CONTRACT_LINE_ATTRIBUTE4,

ContLines.ATTRIBUTE5 CONTRACT_LINE_ATTRIBUTE5,

ContLines.ATTRIBUTE6 CONTRACT_LINE_ATTRIBUTE6,

ContLines.ATTRIBUTE7 CONTRACT_LINE_ATTRIBUTE7,

ContLines.ATTRIBUTE8 CONTRACT_LINE_ATTRIBUTE8,

ContLines.ATTRIBUTE9 CONTRACT_LINE_ATTRIBUTE9,

ContLines.ATTRIBUTE10 CONTRACT_LINE_ATTRIBUTE10,

ContLines.ATTRIBUTE11 CONTRACT_LINE_ATTRIBUTE11,

ContLines.ATTRIBUTE12 CONTRACT_LINE_ATTRIBUTE12,

ContLines.ATTRIBUTE13 CONTRACT_LINE_ATTRIBUTE13,

ContLines.ATTRIBUTE14 CONTRACT_LINE_ATTRIBUTE14,

ContLines.ATTRIBUTE15 CONTRACT_LINE_ATTRIBUTE15,

ContLines.ATTRIBUTE16 CONTRACT_LINE_ATTRIBUTE16,

ContLines.ATTRIBUTE17 CONTRACT_LINE_ATTRIBUTE17,

ContLines.ATTRIBUTE18 CONTRACT_LINE_ATTRIBUTE18,

ContLines.ATTRIBUTE19 CONTRACT_LINE_ATTRIBUTE19,

ContLines.ATTRIBUTE20 CONTRACT_LINE_ATTRIBUTE20,

ContLines.ATTRIBUTE21 CONTRACT_LINE_ATTRIBUTE21,

ContLines.ATTRIBUTE22 CONTRACT_LINE_ATTRIBUTE22,

ContLines.ATTRIBUTE23 CONTRACT_LINE_ATTRIBUTE23,

ContLines.ATTRIBUTE24 CONTRACT_LINE_ATTRIBUTE24,

ContLines.ATTRIBUTE25 CONTRACT_LINE_ATTRIBUTE25,

ContLines.ATTRIBUTE26 CONTRACT_LINE_ATTRIBUTE26,

ContLines.ATTRIBUTE27 CONTRACT_LINE_ATTRIBUTE27,

ContLines.ATTRIBUTE28 CONTRACT_LINE_ATTRIBUTE28,

ContLines.ATTRIBUTE29 CONTRACT_LINE_ATTRIBUTE29,

ContLines.ATTRIBUTE30 CONTRACT_LINE_ATTRIBUTE30,

ContLines.CUST_PO_NUMBER CON_LINE_CUST_PO_NUMBER

FROM

PJB_INVOICE_LINES InvLines,

PJB_BILL_PLANS_B BillPlan,

OKC_K_LINES_B ContLines

WHERE InvLines.CONTRACT_LINE_ID = ContLines.ID(+)

AND ContLines.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID(+)

AND ((ContLines.VERSION_TYPE IN ('A','C')

AND ContLines.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD')

AND BillPlan.MAJOR_VERSION = ContLines.MAJOR_VERSION

AND ContLines.ID = InvLines.CONTRACT_LINE_ID)

OR (ContLines.ID IS NULL))

UNION ALL

SELECT

InvLines.INVOICE_LINE_ID INVOICE_LINE_ID,

InvLines.INVOICE_LINE_NUM INVOICE_LINE_NUM,

InvLines.INVOICE_ID INVOICE_ID,

InvLines.INVOICE_NUM INVOICE_NUM ,

InvLines.CONTRACT_ID INVOICE_LINE_CONTRACT_ID,

InvLines.BILL_PLAN_ID INVOICE_LINE_BILL_PLAN_ID,

BillPlan.BILL_METHOD_ID INVOICE_LINE_BILL_METHOD_ID,

InvLines.CREDITED_INVOICE_ID CREDITED_INVOICE_ID,

InvLines.CREDITED_INVOICE_LINE_ID CREDITED_INVOICE_LINE_ID,

InvLines.PAYMENT_TERM_ID PAYMENT_TERM_ID,

InvLines.ATTRIBUTE_CATEGORY INV_LINE_ATTRIBUTE_CATEGORY,

InvLines.ATTRIBUTE1 INVOICE_LINE_ATTRIBUTE1,

InvLines.ATTRIBUTE2 INVOICE_LINE_ATTRIBUTE2,

InvLines.ATTRIBUTE3 INVOICE_LINE_ATTRIBUTE3,

InvLines.ATTRIBUTE4 INVOICE_LINE_ATTRIBUTE4,

InvLines.ATTRIBUTE5 INVOICE_LINE_ATTRIBUTE5,

InvLines.ATTRIBUTE6 INVOICE_LINE_ATTRIBUTE6,

InvLines.ATTRIBUTE7 INVOICE_LINE_ATTRIBUTE7,

InvLines.ATTRIBUTE8 INVOICE_LINE_ATTRIBUTE8,

InvLines.ATTRIBUTE9 INVOICE_LINE_ATTRIBUTE9,

InvLines.ATTRIBUTE10 INVOICE_LINE_ATTRIBUTE10,

InvLines.ATTRIBUTE11 INVOICE_LINE_ATTRIBUTE11,

InvLines.ATTRIBUTE12 INVOICE_LINE_ATTRIBUTE12,

InvLines.ATTRIBUTE13 INVOICE_LINE_ATTRIBUTE13,

InvLines.ATTRIBUTE14 INVOICE_LINE_ATTRIBUTE14,

InvLines.ATTRIBUTE15 INVOICE_LINE_ATTRIBUTE15,

ContLines.ID CONTRACT_LINE_ID,

ContLines.ID CONTRACT_LINE,

ContLines.LINE_NUMBER CONTRACT_LINE_NUMBER,

ContLines.ATTRIBUTE_CATEGORY CONT_LINE_ATTRIBUTE_CATEGORY,

ContLines.ATTRIBUTE1 CONTRACT_LINE_ATTRIBUTE1,

ContLines.ATTRIBUTE2 CONTRACT_LINE_ATTRIBUTE2,

ContLines.ATTRIBUTE3 CONTRACT_LINE_ATTRIBUTE3,

ContLines.ATTRIBUTE4 CONTRACT_LINE_ATTRIBUTE4,

ContLines.ATTRIBUTE5 CONTRACT_LINE_ATTRIBUTE5,

ContLines.ATTRIBUTE6 CONTRACT_LINE_ATTRIBUTE6,

ContLines.ATTRIBUTE7 CONTRACT_LINE_ATTRIBUTE7,

ContLines.ATTRIBUTE8 CONTRACT_LINE_ATTRIBUTE8,

ContLines.ATTRIBUTE9 CONTRACT_LINE_ATTRIBUTE9,

ContLines.ATTRIBUTE10 CONTRACT_LINE_ATTRIBUTE10,

ContLines.ATTRIBUTE11 CONTRACT_LINE_ATTRIBUTE11,

ContLines.ATTRIBUTE12 CONTRACT_LINE_ATTRIBUTE12,

ContLines.ATTRIBUTE13 CONTRACT_LINE_ATTRIBUTE13,

ContLines.ATTRIBUTE14 CONTRACT_LINE_ATTRIBUTE14,

ContLines.ATTRIBUTE15 CONTRACT_LINE_ATTRIBUTE15,

ContLines.ATTRIBUTE16 CONTRACT_LINE_ATTRIBUTE16,

ContLines.ATTRIBUTE17 CONTRACT_LINE_ATTRIBUTE17,

ContLines.ATTRIBUTE18 CONTRACT_LINE_ATTRIBUTE18,

ContLines.ATTRIBUTE19 CONTRACT_LINE_ATTRIBUTE19,

ContLines.ATTRIBUTE20 CONTRACT_LINE_ATTRIBUTE20,

ContLines.ATTRIBUTE21 CONTRACT_LINE_ATTRIBUTE21,

ContLines.ATTRIBUTE22 CONTRACT_LINE_ATTRIBUTE22,

ContLines.ATTRIBUTE23 CONTRACT_LINE_ATTRIBUTE23,

ContLines.ATTRIBUTE24 CONTRACT_LINE_ATTRIBUTE24,

ContLines.ATTRIBUTE25 CONTRACT_LINE_ATTRIBUTE25,

ContLines.ATTRIBUTE26 CONTRACT_LINE_ATTRIBUTE26,

ContLines.ATTRIBUTE27 CONTRACT_LINE_ATTRIBUTE27,

ContLines.ATTRIBUTE28 CONTRACT_LINE_ATTRIBUTE28,

ContLines.ATTRIBUTE29 CONTRACT_LINE_ATTRIBUTE29,

ContLines.ATTRIBUTE30 CONTRACT_LINE_ATTRIBUTE30,

ContLines.CUST_PO_NUMBER CON_LINE_CUST_PO_NUMBER

FROM

PJB_INVOICE_LINES InvLines,

PJB_BILL_PLANS_B BillPlan,

OKC_K_LINES_B ContLines,

OKC_K_HEADERS_ALL_B ContHead,

OKC_BU_IMPL_OPTIONS_ALL BU

WHERE InvLines.CONTRACT_LINE_ID = ContLines.ID(+)

AND ContLines.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID(+)

AND ContHead.Org_id = Bu.org_id

AND BU.attribute15 = 'INCLUDE_UNDER_AMENDMENT'

AND ContHead.Version_type = 'C'

AND ContHead.Sts_code = 'UNDER_AMENDMENT'

AND ContHead.under_amend_version_flag ='N'

AND ContLines.DNZ_CHR_ID = ContHead.ID

AND ContLines.VERSION_TYPE ='C'

AND ContLines.STS_CODE IN ( 'UNDER_AMENDMENT','CLOSED')

AND ( (ContLines.ID = InvLines.CONTRACT_LINE_ID

AND BillPlan.MAJOR_VERSION = ContLines.MAJOR_VERSION)

OR (ContLines.ID IS NULL ) )