RA_CUSTOMER_TRX_LINES_RM

Details

Schema: FUSION
Object owner: VRM
Object type: VIEW

Columns

Name
CUSTOMER_TRX_LINE_ID OKS_SUB_LINE_ID OKS_BILLING_ID CUSTOMER_TRX_ID DOCUMENT_TYPE_CODE LINE_TYPE LINE_NUMBER INVENTORY_ITEM_ID MEMO_LINE_SEQ_ID MEMO_LINE_NAME UOM_CODE QUANTITY_ORDERED QUANTITY_INVOICED UNIT_SELLING_PRICE UNIT_STANDARD_PRICE EXTENDED_AMOUNT SHIP_TO_SITE_USE_ID CREATION_DATE LAST_UPDATE_DATE DELIVERED_FLAG DELIVERY_STATUS DELIVERY_DATE ACCOUNTING_RULE_ID RULE_START_DATE RULE_END_DATE PREVIOUS_CUSTOMER_TRX_LINE_ID ACCOUNTING_RULE_DURATION ORG_ID WAREHOUSE_ID SHIP_DATE_ACTUAL CREDIT_METHOD_FOR_RULES LAST_PERIOD_TO_CREDIT TERM_ID ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE_CATEGORY INTERFACE_LINE_CONTEXT INTERFACE_LINE_ATTRIBUTE1 INTERFACE_LINE_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE8 INTERFACE_LINE_ATTRIBUTE9 INTERFACE_LINE_ATTRIBUTE10 INTERFACE_LINE_ATTRIBUTE11 INTERFACE_LINE_ATTRIBUTE12 INTERFACE_LINE_ATTRIBUTE13 INTERFACE_LINE_ATTRIBUTE14 INTERFACE_LINE_ATTRIBUTE15 REFERENCE_DOCUMENT_TYPE_ID REFERENCE_DOC_LINE_ID_INT_1 REFERENCE_DOC_LINE_ID_INT_2 REFERENCE_DOC_LINE_ID_INT_3 REFERENCE_DOC_LINE_ID_INT_4 REFERENCE_DOC_LINE_ID_INT_5 REFERENCE_DOC_LINE_ID_CHAR_1 REFERENCE_DOC_LINE_ID_CHAR_2 REFERENCE_DOC_LINE_ID_CHAR_3 REFERENCE_DOC_LINE_ID_CHAR_4 REFERENCE_DOC_LINE_ID_CHAR_5

Name

CUSTOMER_TRX_LINE_ID

OKS_SUB_LINE_ID

OKS_BILLING_ID

CUSTOMER_TRX_ID

DOCUMENT_TYPE_CODE

LINE_TYPE

LINE_NUMBER

INVENTORY_ITEM_ID

MEMO_LINE_SEQ_ID

MEMO_LINE_NAME

UOM_CODE

QUANTITY_ORDERED

QUANTITY_INVOICED

UNIT_SELLING_PRICE

UNIT_STANDARD_PRICE

EXTENDED_AMOUNT

SHIP_TO_SITE_USE_ID

CREATION_DATE

LAST_UPDATE_DATE

DELIVERED_FLAG

DELIVERY_STATUS

DELIVERY_DATE

ACCOUNTING_RULE_ID

RULE_START_DATE

RULE_END_DATE

PREVIOUS_CUSTOMER_TRX_LINE_ID

ACCOUNTING_RULE_DURATION

ORG_ID

WAREHOUSE_ID

SHIP_DATE_ACTUAL

CREDIT_METHOD_FOR_RULES

LAST_PERIOD_TO_CREDIT

TERM_ID

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE_CATEGORY

INTERFACE_LINE_CONTEXT

INTERFACE_LINE_ATTRIBUTE1

INTERFACE_LINE_ATTRIBUTE2

INTERFACE_LINE_ATTRIBUTE3

INTERFACE_LINE_ATTRIBUTE4

INTERFACE_LINE_ATTRIBUTE5

INTERFACE_LINE_ATTRIBUTE6

INTERFACE_LINE_ATTRIBUTE7

INTERFACE_LINE_ATTRIBUTE8

INTERFACE_LINE_ATTRIBUTE9

INTERFACE_LINE_ATTRIBUTE10

INTERFACE_LINE_ATTRIBUTE11

INTERFACE_LINE_ATTRIBUTE12

INTERFACE_LINE_ATTRIBUTE13

INTERFACE_LINE_ATTRIBUTE14

INTERFACE_LINE_ATTRIBUTE15

REFERENCE_DOCUMENT_TYPE_ID

REFERENCE_DOC_LINE_ID_INT_1

REFERENCE_DOC_LINE_ID_INT_2

REFERENCE_DOC_LINE_ID_INT_3

REFERENCE_DOC_LINE_ID_INT_4

REFERENCE_DOC_LINE_ID_INT_5

REFERENCE_DOC_LINE_ID_CHAR_1

REFERENCE_DOC_LINE_ID_CHAR_2

REFERENCE_DOC_LINE_ID_CHAR_3

REFERENCE_DOC_LINE_ID_CHAR_4

REFERENCE_DOC_LINE_ID_CHAR_5

Query

SQL_Statement
SELECT CTL.CUSTOMER_TRX_LINE_ID, -99 OKS_SUB_LINE_ID, -99 OKS_BILLING_ID, CTL.CUSTOMER_TRX_ID, 'TRANSACTIONS' DOCUMENT_TYPE_CODE, CTL.LINE_TYPE, CTL.LINE_NUMBER, CTL.INVENTORY_ITEM_ID, NVL2(CTL.INVENTORY_ITEM_ID,NULL,NVL(CTL.MEMO_LINE_SEQ_ID,'15')) MEMO_LINE_SEQ_ID, NVL(ML.NAME, DECODE(CTL.INVENTORY_ITEM_ID, NULL, CTL.DESCRIPTION, NULL)) MEMO_LINE_NAME, NVL(CTL.UOM_CODE, 'Ea') UOM_CODE, CTL.QUANTITY_ORDERED, DECODE(ct.trx_class,'CM', NVL(CTL.QUANTITY_CREDITED,-1) ,'ONACC', COALESCE(CTL.QUANTITY_INVOICED,CTL.QUANTITY_CREDITED,-1) , NVL(CTL.QUANTITY_INVOICED,1) ) QUANTITY_INVOICED, NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE, NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT)/DECODE(ct.trx_class,'CM', NVL(DECODE(CTL.QUANTITY_CREDITED,0,-1,CTL.QUANTITY_CREDITED),-1) , NVL(DECODE(CTL.QUANTITY_INVOICED,0,1,CTL.QUANTITY_INVOICED),1) ))) UNIT_SELLING_PRICE, CTL.UNIT_STANDARD_PRICE, NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT) EXTENDED_AMOUNT, CTL.SHIP_TO_SITE_USE_ID, CTL.CREATION_DATE, CTL.LAST_UPDATE_DATE, 'Y' DELIVERED_FLAG, 'FULFILLED' DELIVERY_STATUS, CT.TRX_DATE DELIVERY_DATE, CTL.ACCOUNTING_RULE_ID, CTL.RULE_START_DATE, CTL.RULE_END_DATE, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID, CTL.ACCOUNTING_RULE_DURATION, CTL.ORG_ID, NVL(CTL.WAREHOUSE_ID,DECODE(CTL.INVENTORY_ITEM_ID,null,null,(select item_validation_org_id from ar_system_parameters_all WHERE ORG_ID = CTL.ORG_ID))) WAREHOUSE_ID, CT.SHIP_DATE_ACTUAL, CT.CREDIT_METHOD_FOR_RULES, CTL.LAST_PERIOD_TO_CREDIT, CT.TERM_ID, CTL.ATTRIBUTE1, CTL.ATTRIBUTE2, CTL.ATTRIBUTE3, CTL.ATTRIBUTE4, CTL.ATTRIBUTE5, CTL.ATTRIBUTE6, CTL.ATTRIBUTE7, CTL.ATTRIBUTE8, CTL.ATTRIBUTE9, CTL.ATTRIBUTE10, CTL.ATTRIBUTE11, CTL.ATTRIBUTE12, CTL.ATTRIBUTE13, CTL.ATTRIBUTE14, CTL.ATTRIBUTE15, CTL.ATTRIBUTE_CATEGORY, CTL.INTERFACE_LINE_CONTEXT, CTL.INTERFACE_LINE_ATTRIBUTE1, CTL.INTERFACE_LINE_ATTRIBUTE2, CTL.INTERFACE_LINE_ATTRIBUTE3, CTL.INTERFACE_LINE_ATTRIBUTE4, CTL.INTERFACE_LINE_ATTRIBUTE5, CTL.INTERFACE_LINE_ATTRIBUTE6, CTL.INTERFACE_LINE_ATTRIBUTE7, CTL.INTERFACE_LINE_ATTRIBUTE8, CTL.INTERFACE_LINE_ATTRIBUTE9, CTL.INTERFACE_LINE_ATTRIBUTE10, CTL.INTERFACE_LINE_ATTRIBUTE11, CTL.INTERFACE_LINE_ATTRIBUTE12, CTL.INTERFACE_LINE_ATTRIBUTE13, CTL.INTERFACE_LINE_ATTRIBUTE14, CTL.INTERFACE_LINE_ATTRIBUTE15, NULL REFERENCE_DOCUMENT_TYPE_ID, NULL REFERENCE_DOC_LINE_ID_INT_1, NULL REFERENCE_DOC_LINE_ID_INT_2, NULL REFERENCE_DOC_LINE_ID_INT_3, NULL REFERENCE_DOC_LINE_ID_INT_4, NULL REFERENCE_DOC_LINE_ID_INT_5, NULL REFERENCE_DOC_LINE_ID_CHAR_1, NULL REFERENCE_DOC_LINE_ID_CHAR_2, NULL REFERENCE_DOC_LINE_ID_CHAR_3, NULL REFERENCE_DOC_LINE_ID_CHAR_4, NULL REFERENCE_DOC_LINE_ID_CHAR_5 FROM RA_CUSTOMER_TRX_LINES_ALL ctl, RA_CUSTOMER_TRX_ALL ct, RA_CUST_TRX_TYPES_ALL ctt, AR_MEMO_LINES_ALL_VL ml, RA_CUST_TRX_LINE_GL_DIST_ALL gld WHERE ctl.line_type = 'LINE' AND ctl.customer_trx_id = ct.customer_trx_id AND ct.complete_flag = 'Y' AND ct.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id AND ct.trx_class IN ('INV','CM','ONACC') AND ctl.previous_customer_trx_line_id IS NULL AND ctl.memo_line_seq_id = ml.memo_line_seq_id (+) AND gld.customer_trx_id = ct.customer_trx_id AND gld.account_class = 'REC' AND gld.account_set_flag = 'N' AND gld.posting_control_id <> -3 AND gld.latest_rec_flag = 'Y' UNION ALL SELECT CTL.CUSTOMER_TRX_LINE_ID, -99 OKS_SUB_LINE_ID, -99 OKS_BILLING_ID, CTL.CUSTOMER_TRX_ID, 'TRANSACTIONS' DOCUMENT_TYPE_CODE, CTL.LINE_TYPE, CTL.LINE_NUMBER, CTL.INVENTORY_ITEM_ID, NVL2(CTL.INVENTORY_ITEM_ID,NULL,NVL(CTL.MEMO_LINE_SEQ_ID,'15')), NVL(ML.NAME, DECODE(CTL.INVENTORY_ITEM_ID, NULL, CTL.DESCRIPTION, NULL)) MEMO_LINE_NAME, NVL(CTL.UOM_CODE, NVL(CTL_INV.UOM_CODE, 'Ea')) UOM_CODE, CTL.QUANTITY_ORDERED, NVL(CTL.QUANTITY_CREDITED, CTL.EXTENDED_AMOUNT/DECODE(NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE, CTL_INV.UNIT_SELLING_PRICE)),0,1,NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE, CTL_INV.UNIT_SELLING_PRICE)))) QUANTITY_INVOICED, NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE, DECODE(CTL.QUANTITY_CREDITED, NULL, CTL_INV.UNIT_SELLING_PRICE, NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT)/DECODE(CTL.QUANTITY_CREDITED,0,-1,CTL.QUANTITY_CREDITED)))) UNIT_SELLING_PRICE, CTL.UNIT_STANDARD_PRICE, NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT) EXTENDED_AMOUNT, CTL.SHIP_TO_SITE_USE_ID, CTL.CREATION_DATE, CTL.LAST_UPDATE_DATE, 'Y' DELIVERED_FLAG, 'FULFILLED' DELIVERY_STATUS, CT.TRX_DATE DELIVERY_DATE, CTL.ACCOUNTING_RULE_ID, CTL.RULE_START_DATE, CTL.RULE_END_DATE, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID, CTL.ACCOUNTING_RULE_DURATION, CTL.ORG_ID, NVL(CTL.WAREHOUSE_ID,DECODE(CTL.INVENTORY_ITEM_ID,null,null,(select item_validation_org_id from ar_system_parameters_all WHERE ORG_ID = CTL.ORG_ID))) WAREHOUSE_ID, CT.SHIP_DATE_ACTUAL, CT.CREDIT_METHOD_FOR_RULES, CTL.LAST_PERIOD_TO_CREDIT, CT.TERM_ID, CTL.ATTRIBUTE1, CTL.ATTRIBUTE2, CTL.ATTRIBUTE3, CTL.ATTRIBUTE4, CTL.ATTRIBUTE5, CTL.ATTRIBUTE6, CTL.ATTRIBUTE7, CTL.ATTRIBUTE8, CTL.ATTRIBUTE9, CTL.ATTRIBUTE10, CTL.ATTRIBUTE11, CTL.ATTRIBUTE12, CTL.ATTRIBUTE13, CTL.ATTRIBUTE14, CTL.ATTRIBUTE15, CTL.ATTRIBUTE_CATEGORY, CTL.INTERFACE_LINE_CONTEXT, CTL.INTERFACE_LINE_ATTRIBUTE1, CTL.INTERFACE_LINE_ATTRIBUTE2, CTL.INTERFACE_LINE_ATTRIBUTE3, CTL.INTERFACE_LINE_ATTRIBUTE4, CTL.INTERFACE_LINE_ATTRIBUTE5, CTL.INTERFACE_LINE_ATTRIBUTE6, CTL.INTERFACE_LINE_ATTRIBUTE7, CTL.INTERFACE_LINE_ATTRIBUTE8, CTL.INTERFACE_LINE_ATTRIBUTE9, CTL.INTERFACE_LINE_ATTRIBUTE10, CTL.INTERFACE_LINE_ATTRIBUTE11, CTL.INTERFACE_LINE_ATTRIBUTE12, CTL.INTERFACE_LINE_ATTRIBUTE13, CTL.INTERFACE_LINE_ATTRIBUTE14, CTL.INTERFACE_LINE_ATTRIBUTE15, null REFERENCE_DOCUMENT_TYPE_ID, null REFERENCE_DOC_LINE_ID_INT_1, null REFERENCE_DOC_LINE_ID_INT_2, null REFERENCE_DOC_LINE_ID_INT_3, null REFERENCE_DOC_LINE_ID_INT_4, NULL REFERENCE_DOC_LINE_ID_INT_5, null REFERENCE_DOC_LINE_ID_CHAR_1, null REFERENCE_DOC_LINE_ID_CHAR_2, null REFERENCE_DOC_LINE_ID_CHAR_3, null REFERENCE_DOC_LINE_ID_CHAR_4, null REFERENCE_DOC_LINE_ID_CHAR_5 FROM RA_CUSTOMER_TRX_LINES_ALL ctl, RA_CUSTOMER_TRX_ALL ct, RA_CUSTOMER_TRX_ALL ct_inv, RA_CUST_TRX_TYPES_ALL ctt, RA_CUSTOMER_TRX_LINES_ALL ctl_inv, AR_MEMO_LINES_ALL_VL ml, RA_CUST_TRX_LINE_GL_DIST_ALL gld WHERE ctl.line_type = 'LINE' AND ctl.customer_trx_id = ct.customer_trx_id AND ct.complete_flag = 'Y' AND ct.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id AND ct.trx_class = 'CM' AND ct_inv.rev_rec_application in ('VRM_BILLING_TRANSITION','VRM_BILLING') AND ct.previous_customer_trx_id = ct_inv.customer_trx_id AND ctl.previous_customer_trx_line_id = ctl_inv.customer_trx_line_id AND ctl.memo_line_seq_id = ml.memo_line_seq_id (+)AND gld.customer_trx_id = ct.customer_trx_id AND gld.account_class = 'REC' AND gld.account_set_flag = 'N' AND gld.posting_control_id <> -3 AND gld.latest_rec_flag = 'Y'

SQL_Statement

SELECT CTL.CUSTOMER_TRX_LINE_ID,

-99 OKS_SUB_LINE_ID,

-99 OKS_BILLING_ID,

CTL.CUSTOMER_TRX_ID,

'TRANSACTIONS' DOCUMENT_TYPE_CODE,

CTL.LINE_TYPE,

CTL.LINE_NUMBER,

CTL.INVENTORY_ITEM_ID,

NVL2(CTL.INVENTORY_ITEM_ID,NULL,NVL(CTL.MEMO_LINE_SEQ_ID,'15')) MEMO_LINE_SEQ_ID,

NVL(ML.NAME, DECODE(CTL.INVENTORY_ITEM_ID, NULL, CTL.DESCRIPTION, NULL)) MEMO_LINE_NAME,

NVL(CTL.UOM_CODE, 'Ea') UOM_CODE,

CTL.QUANTITY_ORDERED,

DECODE(ct.trx_class,'CM', NVL(CTL.QUANTITY_CREDITED,-1) ,'ONACC', COALESCE(CTL.QUANTITY_INVOICED,CTL.QUANTITY_CREDITED,-1) , NVL(CTL.QUANTITY_INVOICED,1) ) QUANTITY_INVOICED,

NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE,

NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT)/DECODE(ct.trx_class,'CM', NVL(DECODE(CTL.QUANTITY_CREDITED,0,-1,CTL.QUANTITY_CREDITED),-1) , NVL(DECODE(CTL.QUANTITY_INVOICED,0,1,CTL.QUANTITY_INVOICED),1) ))) UNIT_SELLING_PRICE,

CTL.UNIT_STANDARD_PRICE,

NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT) EXTENDED_AMOUNT,

CTL.SHIP_TO_SITE_USE_ID,

CTL.CREATION_DATE,

CTL.LAST_UPDATE_DATE,

'Y' DELIVERED_FLAG,

'FULFILLED' DELIVERY_STATUS,

CT.TRX_DATE DELIVERY_DATE,

CTL.ACCOUNTING_RULE_ID,

CTL.RULE_START_DATE,

CTL.RULE_END_DATE,

CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID,

CTL.ACCOUNTING_RULE_DURATION,

CTL.ORG_ID,

NVL(CTL.WAREHOUSE_ID,DECODE(CTL.INVENTORY_ITEM_ID,null,null,(select item_validation_org_id from ar_system_parameters_all WHERE ORG_ID = CTL.ORG_ID))) WAREHOUSE_ID,

CT.SHIP_DATE_ACTUAL,

CT.CREDIT_METHOD_FOR_RULES,

CTL.LAST_PERIOD_TO_CREDIT,

CT.TERM_ID,

CTL.ATTRIBUTE1,

CTL.ATTRIBUTE2,

CTL.ATTRIBUTE3,

CTL.ATTRIBUTE4,

CTL.ATTRIBUTE5,

CTL.ATTRIBUTE6,

CTL.ATTRIBUTE7,

CTL.ATTRIBUTE8,

CTL.ATTRIBUTE9,

CTL.ATTRIBUTE10,

CTL.ATTRIBUTE11,

CTL.ATTRIBUTE12,

CTL.ATTRIBUTE13,

CTL.ATTRIBUTE14,

CTL.ATTRIBUTE15,

CTL.ATTRIBUTE_CATEGORY,

CTL.INTERFACE_LINE_CONTEXT,

CTL.INTERFACE_LINE_ATTRIBUTE1,

CTL.INTERFACE_LINE_ATTRIBUTE2,

CTL.INTERFACE_LINE_ATTRIBUTE3,

CTL.INTERFACE_LINE_ATTRIBUTE4,

CTL.INTERFACE_LINE_ATTRIBUTE5,

CTL.INTERFACE_LINE_ATTRIBUTE6,

CTL.INTERFACE_LINE_ATTRIBUTE7,

CTL.INTERFACE_LINE_ATTRIBUTE8,

CTL.INTERFACE_LINE_ATTRIBUTE9,

CTL.INTERFACE_LINE_ATTRIBUTE10,

CTL.INTERFACE_LINE_ATTRIBUTE11,

CTL.INTERFACE_LINE_ATTRIBUTE12,

CTL.INTERFACE_LINE_ATTRIBUTE13,

CTL.INTERFACE_LINE_ATTRIBUTE14,

CTL.INTERFACE_LINE_ATTRIBUTE15,

NULL REFERENCE_DOCUMENT_TYPE_ID,

NULL REFERENCE_DOC_LINE_ID_INT_1,

NULL REFERENCE_DOC_LINE_ID_INT_2,

NULL REFERENCE_DOC_LINE_ID_INT_3,

NULL REFERENCE_DOC_LINE_ID_INT_4,

NULL REFERENCE_DOC_LINE_ID_INT_5,

NULL REFERENCE_DOC_LINE_ID_CHAR_1,

NULL REFERENCE_DOC_LINE_ID_CHAR_2,

NULL REFERENCE_DOC_LINE_ID_CHAR_3,

NULL REFERENCE_DOC_LINE_ID_CHAR_4,

NULL REFERENCE_DOC_LINE_ID_CHAR_5

FROM RA_CUSTOMER_TRX_LINES_ALL ctl,

RA_CUSTOMER_TRX_ALL ct,

RA_CUST_TRX_TYPES_ALL ctt,

AR_MEMO_LINES_ALL_VL ml,

RA_CUST_TRX_LINE_GL_DIST_ALL gld

WHERE ctl.line_type = 'LINE'

AND ctl.customer_trx_id = ct.customer_trx_id

AND ct.complete_flag = 'Y'

AND ct.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id

AND ct.trx_class IN ('INV','CM','ONACC')

AND ctl.previous_customer_trx_line_id IS NULL

AND ctl.memo_line_seq_id = ml.memo_line_seq_id (+)

AND gld.customer_trx_id = ct.customer_trx_id

AND gld.account_class = 'REC'

AND gld.account_set_flag = 'N'

AND gld.posting_control_id <> -3

AND gld.latest_rec_flag = 'Y'

UNION ALL

SELECT CTL.CUSTOMER_TRX_LINE_ID,

-99 OKS_SUB_LINE_ID,

-99 OKS_BILLING_ID,

CTL.CUSTOMER_TRX_ID,

'TRANSACTIONS' DOCUMENT_TYPE_CODE,

CTL.LINE_TYPE,

CTL.LINE_NUMBER,

CTL.INVENTORY_ITEM_ID,

NVL2(CTL.INVENTORY_ITEM_ID,NULL,NVL(CTL.MEMO_LINE_SEQ_ID,'15')),

NVL(ML.NAME, DECODE(CTL.INVENTORY_ITEM_ID, NULL, CTL.DESCRIPTION, NULL)) MEMO_LINE_NAME,

NVL(CTL.UOM_CODE, NVL(CTL_INV.UOM_CODE, 'Ea')) UOM_CODE,

CTL.QUANTITY_ORDERED,

NVL(CTL.QUANTITY_CREDITED, CTL.EXTENDED_AMOUNT/DECODE(NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE, CTL_INV.UNIT_SELLING_PRICE)),0,1,NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE, CTL_INV.UNIT_SELLING_PRICE)))) QUANTITY_INVOICED,

NVL(CTL.GROSS_UNIT_SELLING_PRICE, NVL(CTL.UNIT_SELLING_PRICE,

DECODE(CTL.QUANTITY_CREDITED, NULL, CTL_INV.UNIT_SELLING_PRICE, NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT)/DECODE(CTL.QUANTITY_CREDITED,0,-1,CTL.QUANTITY_CREDITED)))) UNIT_SELLING_PRICE,

CTL.UNIT_STANDARD_PRICE,

NVL(CTL.GROSS_EXTENDED_AMOUNT, CTL.EXTENDED_AMOUNT) EXTENDED_AMOUNT,

CTL.SHIP_TO_SITE_USE_ID,

CTL.CREATION_DATE,

CTL.LAST_UPDATE_DATE,

'Y' DELIVERED_FLAG,

'FULFILLED' DELIVERY_STATUS,

CT.TRX_DATE DELIVERY_DATE,

CTL.ACCOUNTING_RULE_ID,

CTL.RULE_START_DATE,

CTL.RULE_END_DATE,

CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID,

CTL.ACCOUNTING_RULE_DURATION,

CTL.ORG_ID,

NVL(CTL.WAREHOUSE_ID,DECODE(CTL.INVENTORY_ITEM_ID,null,null,(select item_validation_org_id from ar_system_parameters_all WHERE ORG_ID = CTL.ORG_ID))) WAREHOUSE_ID,

CT.SHIP_DATE_ACTUAL,

CT.CREDIT_METHOD_FOR_RULES,

CTL.LAST_PERIOD_TO_CREDIT,

CT.TERM_ID,

CTL.ATTRIBUTE1,

CTL.ATTRIBUTE2,

CTL.ATTRIBUTE3,

CTL.ATTRIBUTE4,

CTL.ATTRIBUTE5,

CTL.ATTRIBUTE6,

CTL.ATTRIBUTE7,

CTL.ATTRIBUTE8,

CTL.ATTRIBUTE9,

CTL.ATTRIBUTE10,

CTL.ATTRIBUTE11,

CTL.ATTRIBUTE12,

CTL.ATTRIBUTE13,

CTL.ATTRIBUTE14,

CTL.ATTRIBUTE15,

CTL.ATTRIBUTE_CATEGORY,

CTL.INTERFACE_LINE_CONTEXT,

CTL.INTERFACE_LINE_ATTRIBUTE1,

CTL.INTERFACE_LINE_ATTRIBUTE2,

CTL.INTERFACE_LINE_ATTRIBUTE3,

CTL.INTERFACE_LINE_ATTRIBUTE4,

CTL.INTERFACE_LINE_ATTRIBUTE5,

CTL.INTERFACE_LINE_ATTRIBUTE6,

CTL.INTERFACE_LINE_ATTRIBUTE7,

CTL.INTERFACE_LINE_ATTRIBUTE8,

CTL.INTERFACE_LINE_ATTRIBUTE9,

CTL.INTERFACE_LINE_ATTRIBUTE10,

CTL.INTERFACE_LINE_ATTRIBUTE11,

CTL.INTERFACE_LINE_ATTRIBUTE12,

CTL.INTERFACE_LINE_ATTRIBUTE13,

CTL.INTERFACE_LINE_ATTRIBUTE14,

CTL.INTERFACE_LINE_ATTRIBUTE15,

null REFERENCE_DOCUMENT_TYPE_ID,

null REFERENCE_DOC_LINE_ID_INT_1,

null REFERENCE_DOC_LINE_ID_INT_2,

null REFERENCE_DOC_LINE_ID_INT_3,

null REFERENCE_DOC_LINE_ID_INT_4,

NULL REFERENCE_DOC_LINE_ID_INT_5,

null REFERENCE_DOC_LINE_ID_CHAR_1,

null REFERENCE_DOC_LINE_ID_CHAR_2,

null REFERENCE_DOC_LINE_ID_CHAR_3,

null REFERENCE_DOC_LINE_ID_CHAR_4,

null REFERENCE_DOC_LINE_ID_CHAR_5

FROM RA_CUSTOMER_TRX_LINES_ALL ctl,

RA_CUSTOMER_TRX_ALL ct,

RA_CUSTOMER_TRX_ALL ct_inv,

RA_CUST_TRX_TYPES_ALL ctt,

RA_CUSTOMER_TRX_LINES_ALL ctl_inv,

AR_MEMO_LINES_ALL_VL ml,

RA_CUST_TRX_LINE_GL_DIST_ALL gld

WHERE ctl.line_type = 'LINE'

AND ctl.customer_trx_id = ct.customer_trx_id

AND ct.complete_flag = 'Y'

AND ct.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id

AND ct.trx_class = 'CM'

AND ct_inv.rev_rec_application in ('VRM_BILLING_TRANSITION','VRM_BILLING')

AND ct.previous_customer_trx_id = ct_inv.customer_trx_id

AND ctl.previous_customer_trx_line_id = ctl_inv.customer_trx_line_id

AND ctl.memo_line_seq_id = ml.memo_line_seq_id (+)AND gld.customer_trx_id = ct.customer_trx_id

AND gld.account_class = 'REC'

AND gld.account_set_flag = 'N'

AND gld.posting_control_id <> -3

AND gld.latest_rec_flag = 'Y'