VIEW: APPS.OE_RA_CUST_TRX_HDR_PERF_V

Source

View Text - Preformatted

SELECT RCT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , RCT.TRX_NUMBER TRX_NUMBER , RBS.NAME BATCH_SOURCE , RCTT.NAME TYPE , RCT.TRX_DATE TRX_DATE , OE_INVOICE_PUB.INVOICE_BALANCE(RCT.CUSTOMER_TRX_ID) BALANCE , SUM(RCTL.EXTENDED_AMOUNT) INVOICE_AMOUNT , RCT.CREATION_DATE , RCT.CREATED_BY , RCT.LAST_UPDATE_DATE , RCT.LAST_UPDATED_BY , RCT.LAST_UPDATE_LOGIN , RCT.ROWID ROW_ID , RCTL.INTERFACE_LINE_ATTRIBUTE1 ORDER_NUMBER , decode(RCTL.INTERFACE_LINE_CONTEXT, FND_PROFILE.value('ONT_SOURCE_CODE'), RCTL.INTERFACE_LINE_ATTRIBUTE2, 'INTERCOMPANY') ORDER_TYPE , RCTL.ORG_ID , decode(RCTL.INTERFACE_LINE_CONTEXT, FND_PROFILE.value('ONT_SOURCE_CODE'), '0', RCTL.interface_line_attribute9) interface_line_attribute9 FROM RA_CUSTOMER_TRX_ALL RCT , RA_BATCH_SOURCES_ALL RBS , RA_CUST_TRX_TYPES_ALL RCTT , RA_CUSTOMER_TRX_LINES RCTL WHERE RBS.BATCH_SOURCE_ID(+) = RCT.BATCH_SOURCE_ID AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCTL.INTERFACE_LINE_CONTEXT in (FND_PROFILE.value('ONT_SOURCE_CODE'), 'INTERCOMPANY') AND RCTL.LINE_TYPE <> 'TAX' AND RCTL.INTERFACE_LINE_ATTRIBUTE1 = OE_Invoice_PUB.get_order_number() AND decode(RCTL.INTERFACE_LINE_CONTEXT,FND_PROFILE.value('ONT_SOURCE_CODE'), RCTL.INTERFACE_LINE_ATTRIBUTE2, 'INTERCOMPANY') = OE_Invoice_PUB.get_order_type() AND RCTT.ORG_ID = RCT.ORG_ID AND RCTL.ORG_ID = RCT.ORG_ID AND RBS.ORG_ID(+) = RCT.ORG_ID GROUP BY RCT.CUSTOMER_TRX_ID , RCT.TRX_NUMBER , RBS.NAME , RCTT.NAME , RCT.TRX_DATE , RCT.CREATION_DATE , RCT.CREATED_BY , RCT.LAST_UPDATE_DATE , RCT.LAST_UPDATED_BY , RCT.LAST_UPDATE_LOGIN , RCT.ROWID , RCTL.INTERFACE_LINE_ATTRIBUTE1 , decode(RCTL.INTERFACE_LINE_CONTEXT, FND_PROFILE.value('ONT_SOURCE_CODE'), RCTL.INTERFACE_LINE_ATTRIBUTE2, 'INTERCOMPANY') , RCTL.ORG_ID ,decode(RCTL.INTERFACE_LINE_CONTEXT,FND_PROFILE.value('ONT_SOURCE_CODE'), '0', RCTL.interface_line_attribute9)

View Text - HTML Formatted

SELECT RCT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, RCT.TRX_NUMBER TRX_NUMBER
, RBS.NAME BATCH_SOURCE
, RCTT.NAME TYPE
, RCT.TRX_DATE TRX_DATE
, OE_INVOICE_PUB.INVOICE_BALANCE(RCT.CUSTOMER_TRX_ID) BALANCE
, SUM(RCTL.EXTENDED_AMOUNT) INVOICE_AMOUNT
, RCT.CREATION_DATE
, RCT.CREATED_BY
, RCT.LAST_UPDATE_DATE
, RCT.LAST_UPDATED_BY
, RCT.LAST_UPDATE_LOGIN
, RCT.ROWID ROW_ID
, RCTL.INTERFACE_LINE_ATTRIBUTE1 ORDER_NUMBER
, DECODE(RCTL.INTERFACE_LINE_CONTEXT
, FND_PROFILE.VALUE('ONT_SOURCE_CODE')
, RCTL.INTERFACE_LINE_ATTRIBUTE2
, 'INTERCOMPANY') ORDER_TYPE
, RCTL.ORG_ID
, DECODE(RCTL.INTERFACE_LINE_CONTEXT
, FND_PROFILE.VALUE('ONT_SOURCE_CODE')
, '0'
, RCTL.INTERFACE_LINE_ATTRIBUTE9) INTERFACE_LINE_ATTRIBUTE9
FROM RA_CUSTOMER_TRX_ALL RCT
, RA_BATCH_SOURCES_ALL RBS
, RA_CUST_TRX_TYPES_ALL RCTT
, RA_CUSTOMER_TRX_LINES RCTL
WHERE RBS.BATCH_SOURCE_ID(+) = RCT.BATCH_SOURCE_ID
AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.INTERFACE_LINE_CONTEXT IN (FND_PROFILE.VALUE('ONT_SOURCE_CODE')
, 'INTERCOMPANY')
AND RCTL.LINE_TYPE <> 'TAX'
AND RCTL.INTERFACE_LINE_ATTRIBUTE1 = OE_INVOICE_PUB.GET_ORDER_NUMBER()
AND DECODE(RCTL.INTERFACE_LINE_CONTEXT
, FND_PROFILE.VALUE('ONT_SOURCE_CODE')
, RCTL.INTERFACE_LINE_ATTRIBUTE2
, 'INTERCOMPANY') = OE_INVOICE_PUB.GET_ORDER_TYPE()
AND RCTT.ORG_ID = RCT.ORG_ID
AND RCTL.ORG_ID = RCT.ORG_ID
AND RBS.ORG_ID(+) = RCT.ORG_ID GROUP BY RCT.CUSTOMER_TRX_ID
, RCT.TRX_NUMBER
, RBS.NAME
, RCTT.NAME
, RCT.TRX_DATE
, RCT.CREATION_DATE
, RCT.CREATED_BY
, RCT.LAST_UPDATE_DATE
, RCT.LAST_UPDATED_BY
, RCT.LAST_UPDATE_LOGIN
, RCT.ROWID
, RCTL.INTERFACE_LINE_ATTRIBUTE1
, DECODE(RCTL.INTERFACE_LINE_CONTEXT
, FND_PROFILE.VALUE('ONT_SOURCE_CODE')
, RCTL.INTERFACE_LINE_ATTRIBUTE2
, 'INTERCOMPANY')
, RCTL.ORG_ID
, DECODE(RCTL.INTERFACE_LINE_CONTEXT
, FND_PROFILE.VALUE('ONT_SOURCE_CODE')
, '0'
, RCTL.INTERFACE_LINE_ATTRIBUTE9)