AR_LL_LINES_GROUPS

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
CASH_RECEIPT_ID CUSTOMER_TRX_LINE_ID ALLOCATED_RECEIPT_AMOUNT AMOUNT TAX AMOUNT_APPLIED LAST_UPDATE_DATE LAST_UPDATED_BY LINE_DISCOUNT TAX_DISCOUNT CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN COMMENTS APPLY_TO ATTRIBUTE1 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE_CATEGORY REFERENCE4 REFERENCE5 GROUP_ID REFERENCE2 REFERENCE1 REFERENCE3 CREATED_BY_MODULE OBJECT_VERSION_NUMBER CUSTOMER_TRX_ID GROUP_NAME LINE_NUMBER LINE_BALANCE TAX_BALANCE

Name

CASH_RECEIPT_ID

CUSTOMER_TRX_LINE_ID

ALLOCATED_RECEIPT_AMOUNT

AMOUNT

TAX

AMOUNT_APPLIED

LAST_UPDATE_DATE

LAST_UPDATED_BY

LINE_DISCOUNT

TAX_DISCOUNT

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

COMMENTS

APPLY_TO

ATTRIBUTE1

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE_CATEGORY

REFERENCE4

REFERENCE5

GROUP_ID

REFERENCE2

REFERENCE1

REFERENCE3

CREATED_BY_MODULE

OBJECT_VERSION_NUMBER

CUSTOMER_TRX_ID

GROUP_NAME

LINE_NUMBER

LINE_BALANCE

TAX_BALANCE

Query

SQL_Statement
SELECT ll.CASH_RECEIPT_ID, ll.CUSTOMER_TRX_LINE_ID, ll.ALLOCATED_RECEIPT_AMOUNT, ll.AMOUNT, ll.TAX, nvl(ll.amount,0) + nvl(ll.tax,0) amount_applied, ll.LAST_UPDATE_DATE, ll.LAST_UPDATED_BY, ll.LINE_DISCOUNT, ll.TAX_DISCOUNT, ll.CREATION_DATE, ll.CREATED_BY, ll.LAST_UPDATE_LOGIN, ll.COMMENTS, to_char(ll.APPLY_TO) apply_to, ll.ATTRIBUTE1, ll.ATTRIBUTE10, ll.ATTRIBUTE11, ll.ATTRIBUTE12, ll.ATTRIBUTE13, ll.ATTRIBUTE14, ll.ATTRIBUTE15, ll.ATTRIBUTE2, ll.ATTRIBUTE3, ll.ATTRIBUTE4, ll.ATTRIBUTE5, ll.ATTRIBUTE6, ll.ATTRIBUTE7, ll.ATTRIBUTE8, ll.ATTRIBUTE9, ll.ATTRIBUTE_CATEGORY, ll.REFERENCE4, ll.REFERENCE5, to_char(ll.group_id) GROUP_ID, ll.REFERENCE2, ll.REFERENCE1, ll.REFERENCE3, ll.created_by_module, ll.object_version_number, line.customer_trx_id, line.source_data_key5 group_name, to_char(line.line_number) line_number, line.amount_due_remaining line_balance, tax.amount_due_remaining tax_balance from AR_ACTIVITY_DETAILS ll, ra_customer_trx_lines line, (select customer_trx_id, link_to_cust_trx_line_id, line_type, sum(nvl(amount_due_original,0)) amount_due_original, sum(nvl(amount_due_remaining,0)) amount_due_remaining from ra_customer_trx_lines /* Bug fix 5503852 / where line_type = 'TAX' group by customer_trx_id, link_to_cust_trx_line_id, line_type ) tax WHERE line.customer_trx_line_id = ll.customer_trx_line_id and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+) and line.customer_trx_id = tax.customer_trx_id(+) and line.line_type = 'LINE' and nvl(ll.source_table,'RA') = 'RA' UNION (select ll.CASH_RECEIPT_ID, 0 customer_Trx_line_id, sum(ll.ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT , sum(ll.AMOUNT) AMOUNT, sum(ll.TAX) TAX, sum(nvl(ll.amount,0) + nvl(ll.tax,0)) amount_applied, max(ll.LAST_UPDATE_DATE) LAST_UPDATE_DATE, max(ll.LAST_UPDATED_BY) LAST_UPDATED_BY, sum(ll.LINE_DISCOUNT) LINE_DISCOUNT, sum(ll.TAX_DISCOUNT) TAX_DISCOUNT, max(ll.CREATION_DATE) CREATION_DATE, max(ll.CREATED_BY) CREATED_BY, max(ll.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN, max(ll.COMMENTS) COMMENTS, nvl(line.source_data_key5,'Not grouped') apply_to , max(ll.ATTRIBUTE1) ATTRIBUTE1, max(ll.ATTRIBUTE10) ATTRIBUTE10, max(ll.ATTRIBUTE11) ATTRIBUTE11, max(ll.ATTRIBUTE12) ATTRIBUTE12, max(ll.ATTRIBUTE13) ATTRIBUTE13, max(ll.ATTRIBUTE14) ATTRIBUTE14, max(ll.ATTRIBUTE15) ATTRIBUTE15, max(ll.ATTRIBUTE2) ATTRIBUTE2, max(ll.ATTRIBUTE3) ATTRIBUTE3, max(ll.ATTRIBUTE4) ATTRIBUTE4, max(ll.ATTRIBUTE5) ATTRIBUTE5, max(ll.ATTRIBUTE6) ATTRIBUTE6, max(ll.ATTRIBUTE7) ATTRIBUTE7, max(ll.ATTRIBUTE8) ATTRIBUTE8, max(ll.ATTRIBUTE9) ATTRIBUTE9, max(ll.ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY, max(ll.REFERENCE4) REFERENCE4, max(ll.REFERENCE5) REFERENCE5, to_char(nvl(line.source_data_key4,0)) GROUP_ID , max(ll.REFERENCE2) REFERENCE2, max(ll.REFERENCE1) REFERENCE1, max(ll.REFERENCE3) REFERENCE3, max(ll.created_by_module) created_by_module, sum(ll.object_version_number) object_version_number, line.customer_trx_id, nvl(line.source_data_key5,'Not grouped') group_name, to_char(nvl(line.source_data_key4,0)) line_number, sum(line.amount_due_remaining) line_balance, sum(tax.amount_due_remaining) tax_balance from AR_ACTIVITY_DETAILS ll, ra_customer_trx_lines line, (select customer_trx_id, link_to_cust_trx_line_id, line_type, sum(nvl(amount_due_original,0)) amount_due_original, sum(nvl(amount_due_remaining,0)) amount_due_remaining from ra_customer_trx_lines / Bug fix 5503852 */ where line_type = 'TAX' group by customer_trx_id, link_to_cust_trx_line_id, line_type ) tax where line.customer_trx_line_id = ll.customer_trx_line_id and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+) and line.customer_trx_id = tax.customer_trx_id(+) and line.line_type = 'LINE' and nvl(ll.source_table,'RA') = 'RA' group by ll.cash_receipt_id, line.source_data_key4, line.customer_Trx_id, line.source_data_key5 )

SQL_Statement

SELECT ll.CASH_RECEIPT_ID, ll.CUSTOMER_TRX_LINE_ID, ll.ALLOCATED_RECEIPT_AMOUNT, ll.AMOUNT, ll.TAX, nvl(ll.amount,0) + nvl(ll.tax,0) amount_applied, ll.LAST_UPDATE_DATE, ll.LAST_UPDATED_BY, ll.LINE_DISCOUNT, ll.TAX_DISCOUNT, ll.CREATION_DATE, ll.CREATED_BY, ll.LAST_UPDATE_LOGIN, ll.COMMENTS, to_char(ll.APPLY_TO) apply_to, ll.ATTRIBUTE1, ll.ATTRIBUTE10, ll.ATTRIBUTE11, ll.ATTRIBUTE12, ll.ATTRIBUTE13, ll.ATTRIBUTE14, ll.ATTRIBUTE15, ll.ATTRIBUTE2, ll.ATTRIBUTE3, ll.ATTRIBUTE4, ll.ATTRIBUTE5, ll.ATTRIBUTE6, ll.ATTRIBUTE7, ll.ATTRIBUTE8, ll.ATTRIBUTE9, ll.ATTRIBUTE_CATEGORY, ll.REFERENCE4, ll.REFERENCE5, to_char(ll.group_id) GROUP_ID, ll.REFERENCE2, ll.REFERENCE1, ll.REFERENCE3, ll.created_by_module, ll.object_version_number, line.customer_trx_id, line.source_data_key5 group_name, to_char(line.line_number) line_number, line.amount_due_remaining line_balance, tax.amount_due_remaining tax_balance from AR_ACTIVITY_DETAILS ll, ra_customer_trx_lines line, (select customer_trx_id, link_to_cust_trx_line_id, line_type, sum(nvl(amount_due_original,0)) amount_due_original, sum(nvl(amount_due_remaining,0)) amount_due_remaining from ra_customer_trx_lines /* Bug fix 5503852 */ where line_type = 'TAX' group by customer_trx_id, link_to_cust_trx_line_id, line_type ) tax WHERE line.customer_trx_line_id = ll.customer_trx_line_id and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+) and line.customer_trx_id = tax.customer_trx_id(+) and line.line_type = 'LINE' and nvl(ll.source_table,'RA') = 'RA' UNION (select ll.CASH_RECEIPT_ID, 0 customer_Trx_line_id, sum(ll.ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT , sum(ll.AMOUNT) AMOUNT, sum(ll.TAX) TAX, sum(nvl(ll.amount,0) + nvl(ll.tax,0)) amount_applied, max(ll.LAST_UPDATE_DATE) LAST_UPDATE_DATE, max(ll.LAST_UPDATED_BY) LAST_UPDATED_BY, sum(ll.LINE_DISCOUNT) LINE_DISCOUNT, sum(ll.TAX_DISCOUNT) TAX_DISCOUNT, max(ll.CREATION_DATE) CREATION_DATE, max(ll.CREATED_BY) CREATED_BY, max(ll.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN, max(ll.COMMENTS) COMMENTS, nvl(line.source_data_key5,'Not grouped') apply_to , max(ll.ATTRIBUTE1) ATTRIBUTE1, max(ll.ATTRIBUTE10) ATTRIBUTE10, max(ll.ATTRIBUTE11) ATTRIBUTE11, max(ll.ATTRIBUTE12) ATTRIBUTE12, max(ll.ATTRIBUTE13) ATTRIBUTE13, max(ll.ATTRIBUTE14) ATTRIBUTE14, max(ll.ATTRIBUTE15) ATTRIBUTE15, max(ll.ATTRIBUTE2) ATTRIBUTE2, max(ll.ATTRIBUTE3) ATTRIBUTE3, max(ll.ATTRIBUTE4) ATTRIBUTE4, max(ll.ATTRIBUTE5) ATTRIBUTE5, max(ll.ATTRIBUTE6) ATTRIBUTE6, max(ll.ATTRIBUTE7) ATTRIBUTE7, max(ll.ATTRIBUTE8) ATTRIBUTE8, max(ll.ATTRIBUTE9) ATTRIBUTE9, max(ll.ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY, max(ll.REFERENCE4) REFERENCE4, max(ll.REFERENCE5) REFERENCE5, to_char(nvl(line.source_data_key4,0)) GROUP_ID , max(ll.REFERENCE2) REFERENCE2, max(ll.REFERENCE1) REFERENCE1, max(ll.REFERENCE3) REFERENCE3, max(ll.created_by_module) created_by_module, sum(ll.object_version_number) object_version_number, line.customer_trx_id, nvl(line.source_data_key5,'Not grouped') group_name, to_char(nvl(line.source_data_key4,0)) line_number, sum(line.amount_due_remaining) line_balance, sum(tax.amount_due_remaining) tax_balance from AR_ACTIVITY_DETAILS ll, ra_customer_trx_lines line, (select customer_trx_id, link_to_cust_trx_line_id, line_type, sum(nvl(amount_due_original,0)) amount_due_original, sum(nvl(amount_due_remaining,0)) amount_due_remaining from ra_customer_trx_lines /* Bug fix 5503852 */ where line_type = 'TAX' group by customer_trx_id, link_to_cust_trx_line_id, line_type ) tax where line.customer_trx_line_id = ll.customer_trx_line_id and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+) and line.customer_trx_id = tax.customer_trx_id(+) and line.line_type = 'LINE' and nvl(ll.source_table,'RA') = 'RA' group by ll.cash_receipt_id, line.source_data_key4, line.customer_Trx_id, line.source_data_key5 )