VIEW: APPS.AR_INVOICE_LINES_V

Source

View Text - Preformatted

SELECT lines.customer_trx_id customer_trx_id, lines.customer_trx_line_id customer_trx_line_id, decode( lines2.line_number, null, lines.line_number, null) line_number, lines.line_type line_type, nvl(AR_INVOICE_SQL_FUNC_PUB.get_description( lines.customer_trx_line_id ), lines.description) item_description, nvl(lines.quantity_ordered, lines.quantity_invoiced) quantity_ordered, nvl(lines.quantity_invoiced, lines.quantity_credited) quantity, uom.unit_of_measure unit_of_measure_name, nvl(msi.global_Attribute1, m.global_attribute1) item_code, nvl(lines.unit_selling_price,0) unit_selling_price, lines.extended_amount extended_amount, lines.sales_order sales_order, lines.sales_order_date sales_order_date, lines.tax_rate tax_rate, lines.vat_tax_id vat_tax_id, vat.printed_tax_name printed_tax_name, lines.tax_exemption_id tax_exemption_id, decode(lines.tax_exemption_id, null, null, decode(lines.sales_tax_id, null,exempt.percent_exempt, null )) percent_exemption, decode(lines.tax_exemption_id, null, null, decode(lines.sales_tax_id, null,exempt.exemption_type, null )) exemption_type, lines.sales_tax_id location_rate_id, lines.tax_precedence tax_precedence, lines.link_to_cust_trx_line_id link_to_cust_trx_line_id, AR_INVOICE_SQL_FUNC_PUB.get_taxyn ( lines.customer_trx_line_id ) tax_exists_for_this_line_flag, lines.item_exception_rate_id, lines.memo_line_id, lines.autorule_duration_processed, lines.uom_code, lines.default_ussgl_transaction_code, lines.default_ussgl_trx_code_context, lines.autotax, lines.last_period_to_credit, lines.item_context, lines.tax_exempt_flag, lines.tax_exempt_number, lines.tax_exempt_reason_code, lines.tax_vendor_return_code, lines.location_segment_id, lines.movement_id, lines.org_id, lines.request_id, lines.program_application_id, lines.program_id, lines.program_update_date, lines.rule_start_date, lines.initial_customer_trx_line_id, lines.sales_order_source, lines.taxable_flag, lines.revenue_amount, lines.autorule_complete_flag, lines.last_update_date, lines.last_updated_by, lines.creation_date, lines.created_by, lines.last_update_login, lines.set_of_books_id, lines.reason_code, lines.inventory_item_id, lines.previous_customer_trx_id, lines.previous_customer_trx_line_id, lines.quantity_credited, lines.unit_standard_price, lines.sales_order_revision, lines.sales_order_line, lines.accounting_rule_id, lines.accounting_rule_duration, lines.global_attribute1, lines.global_attribute2, lines.global_attribute3, lines.global_attribute4, lines.global_attribute5, lines.global_attribute6, lines.global_attribute7, lines.global_attribute8, lines.global_attribute9, lines.global_attribute10, lines.global_attribute11, lines.global_attribute12, lines.global_attribute13, lines.global_attribute14, lines.global_attribute15, lines.global_attribute16, lines.global_attribute17, lines.global_attribute18, lines.global_attribute19, lines.global_attribute20, lines.global_attribute_category, lines.attribute_category, lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4, lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8, lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12, lines.attribute13, lines.attribute14, lines.attribute15, lines.interface_line_context, lines.interface_line_attribute1, lines.interface_line_attribute2, lines.interface_line_attribute3, lines.interface_line_attribute4, lines.interface_line_attribute5, lines.interface_line_attribute6, lines.interface_line_attribute7, lines.interface_line_attribute8, lines.interface_line_attribute9, lines.interface_line_attribute10, lines.interface_line_attribute11, lines.interface_line_attribute12, lines.interface_line_attribute13, lines.interface_line_attribute14, lines.interface_line_attribute15, lines.wh_update_date FROM mtl_units_of_measure uom, mtl_system_items msi, ra_tax_exemptions exempt, ar_vat_tax_vl vat, ar_memo_lines m, ra_customer_trx_lines lines2, ra_customer_trx_lines lines, ra_cust_trx_types types, ra_customer_trx trx WHERE lines.link_to_cust_trx_line_id = lines2.customer_trx_line_id(+) AND trx.customer_trx_id = lines.customer_trx_id AND trx.complete_flag = 'Y' AND trx.cust_trx_type_id = types.cust_trx_type_id AND types.default_printing_option = 'PRI' AND lines.uom_code = uom.uom_code(+) AND exempt.tax_exemption_id(+) = lines.tax_exemption_id AND vat.vat_tax_id(+) = lines.vat_tax_id AND lines.inventory_item_id = msi.inventory_item_id(+) AND lines.memo_line_id = m.memo_line_id(+) AND lines.org_id = msi.organization_id(+)

View Text - HTML Formatted

SELECT LINES.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, LINES.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
, DECODE( LINES2.LINE_NUMBER
, NULL
, LINES.LINE_NUMBER
, NULL) LINE_NUMBER
, LINES.LINE_TYPE LINE_TYPE
, NVL(AR_INVOICE_SQL_FUNC_PUB.GET_DESCRIPTION( LINES.CUSTOMER_TRX_LINE_ID )
, LINES.DESCRIPTION) ITEM_DESCRIPTION
, NVL(LINES.QUANTITY_ORDERED
, LINES.QUANTITY_INVOICED) QUANTITY_ORDERED
, NVL(LINES.QUANTITY_INVOICED
, LINES.QUANTITY_CREDITED) QUANTITY
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE_NAME
, NVL(MSI.GLOBAL_ATTRIBUTE1
, M.GLOBAL_ATTRIBUTE1) ITEM_CODE
, NVL(LINES.UNIT_SELLING_PRICE
, 0) UNIT_SELLING_PRICE
, LINES.EXTENDED_AMOUNT EXTENDED_AMOUNT
, LINES.SALES_ORDER SALES_ORDER
, LINES.SALES_ORDER_DATE SALES_ORDER_DATE
, LINES.TAX_RATE TAX_RATE
, LINES.VAT_TAX_ID VAT_TAX_ID
, VAT.PRINTED_TAX_NAME PRINTED_TAX_NAME
, LINES.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
, DECODE(LINES.TAX_EXEMPTION_ID
, NULL
, NULL
, DECODE(LINES.SALES_TAX_ID
, NULL
, EXEMPT.PERCENT_EXEMPT
, NULL )) PERCENT_EXEMPTION
, DECODE(LINES.TAX_EXEMPTION_ID
, NULL
, NULL
, DECODE(LINES.SALES_TAX_ID
, NULL
, EXEMPT.EXEMPTION_TYPE
, NULL )) EXEMPTION_TYPE
, LINES.SALES_TAX_ID LOCATION_RATE_ID
, LINES.TAX_PRECEDENCE TAX_PRECEDENCE
, LINES.LINK_TO_CUST_TRX_LINE_ID LINK_TO_CUST_TRX_LINE_ID
, AR_INVOICE_SQL_FUNC_PUB.GET_TAXYN ( LINES.CUSTOMER_TRX_LINE_ID ) TAX_EXISTS_FOR_THIS_LINE_FLAG
, LINES.ITEM_EXCEPTION_RATE_ID
, LINES.MEMO_LINE_ID
, LINES.AUTORULE_DURATION_PROCESSED
, LINES.UOM_CODE
, LINES.DEFAULT_USSGL_TRANSACTION_CODE
, LINES.DEFAULT_USSGL_TRX_CODE_CONTEXT
, LINES.AUTOTAX
, LINES.LAST_PERIOD_TO_CREDIT
, LINES.ITEM_CONTEXT
, LINES.TAX_EXEMPT_FLAG
, LINES.TAX_EXEMPT_NUMBER
, LINES.TAX_EXEMPT_REASON_CODE
, LINES.TAX_VENDOR_RETURN_CODE
, LINES.LOCATION_SEGMENT_ID
, LINES.MOVEMENT_ID
, LINES.ORG_ID
, LINES.REQUEST_ID
, LINES.PROGRAM_APPLICATION_ID
, LINES.PROGRAM_ID
, LINES.PROGRAM_UPDATE_DATE
, LINES.RULE_START_DATE
, LINES.INITIAL_CUSTOMER_TRX_LINE_ID
, LINES.SALES_ORDER_SOURCE
, LINES.TAXABLE_FLAG
, LINES.REVENUE_AMOUNT
, LINES.AUTORULE_COMPLETE_FLAG
, LINES.LAST_UPDATE_DATE
, LINES.LAST_UPDATED_BY
, LINES.CREATION_DATE
, LINES.CREATED_BY
, LINES.LAST_UPDATE_LOGIN
, LINES.SET_OF_BOOKS_ID
, LINES.REASON_CODE
, LINES.INVENTORY_ITEM_ID
, LINES.PREVIOUS_CUSTOMER_TRX_ID
, LINES.PREVIOUS_CUSTOMER_TRX_LINE_ID
, LINES.QUANTITY_CREDITED
, LINES.UNIT_STANDARD_PRICE
, LINES.SALES_ORDER_REVISION
, LINES.SALES_ORDER_LINE
, LINES.ACCOUNTING_RULE_ID
, LINES.ACCOUNTING_RULE_DURATION
, LINES.GLOBAL_ATTRIBUTE1
, LINES.GLOBAL_ATTRIBUTE2
, LINES.GLOBAL_ATTRIBUTE3
, LINES.GLOBAL_ATTRIBUTE4
, LINES.GLOBAL_ATTRIBUTE5
, LINES.GLOBAL_ATTRIBUTE6
, LINES.GLOBAL_ATTRIBUTE7
, LINES.GLOBAL_ATTRIBUTE8
, LINES.GLOBAL_ATTRIBUTE9
, LINES.GLOBAL_ATTRIBUTE10
, LINES.GLOBAL_ATTRIBUTE11
, LINES.GLOBAL_ATTRIBUTE12
, LINES.GLOBAL_ATTRIBUTE13
, LINES.GLOBAL_ATTRIBUTE14
, LINES.GLOBAL_ATTRIBUTE15
, LINES.GLOBAL_ATTRIBUTE16
, LINES.GLOBAL_ATTRIBUTE17
, LINES.GLOBAL_ATTRIBUTE18
, LINES.GLOBAL_ATTRIBUTE19
, LINES.GLOBAL_ATTRIBUTE20
, LINES.GLOBAL_ATTRIBUTE_CATEGORY
, LINES.ATTRIBUTE_CATEGORY
, LINES.ATTRIBUTE1
, LINES.ATTRIBUTE2
, LINES.ATTRIBUTE3
, LINES.ATTRIBUTE4
, LINES.ATTRIBUTE5
, LINES.ATTRIBUTE6
, LINES.ATTRIBUTE7
, LINES.ATTRIBUTE8
, LINES.ATTRIBUTE9
, LINES.ATTRIBUTE10
, LINES.ATTRIBUTE11
, LINES.ATTRIBUTE12
, LINES.ATTRIBUTE13
, LINES.ATTRIBUTE14
, LINES.ATTRIBUTE15
, LINES.INTERFACE_LINE_CONTEXT
, LINES.INTERFACE_LINE_ATTRIBUTE1
, LINES.INTERFACE_LINE_ATTRIBUTE2
, LINES.INTERFACE_LINE_ATTRIBUTE3
, LINES.INTERFACE_LINE_ATTRIBUTE4
, LINES.INTERFACE_LINE_ATTRIBUTE5
, LINES.INTERFACE_LINE_ATTRIBUTE6
, LINES.INTERFACE_LINE_ATTRIBUTE7
, LINES.INTERFACE_LINE_ATTRIBUTE8
, LINES.INTERFACE_LINE_ATTRIBUTE9
, LINES.INTERFACE_LINE_ATTRIBUTE10
, LINES.INTERFACE_LINE_ATTRIBUTE11
, LINES.INTERFACE_LINE_ATTRIBUTE12
, LINES.INTERFACE_LINE_ATTRIBUTE13
, LINES.INTERFACE_LINE_ATTRIBUTE14
, LINES.INTERFACE_LINE_ATTRIBUTE15
, LINES.WH_UPDATE_DATE
FROM MTL_UNITS_OF_MEASURE UOM
, MTL_SYSTEM_ITEMS MSI
, RA_TAX_EXEMPTIONS EXEMPT
, AR_VAT_TAX_VL VAT
, AR_MEMO_LINES M
, RA_CUSTOMER_TRX_LINES LINES2
, RA_CUSTOMER_TRX_LINES LINES
, RA_CUST_TRX_TYPES TYPES
, RA_CUSTOMER_TRX TRX
WHERE LINES.LINK_TO_CUST_TRX_LINE_ID = LINES2.CUSTOMER_TRX_LINE_ID(+)
AND TRX.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID
AND TRX.COMPLETE_FLAG = 'Y'
AND TRX.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
AND TYPES.DEFAULT_PRINTING_OPTION = 'PRI'
AND LINES.UOM_CODE = UOM.UOM_CODE(+)
AND EXEMPT.TAX_EXEMPTION_ID(+) = LINES.TAX_EXEMPTION_ID
AND VAT.VAT_TAX_ID(+) = LINES.VAT_TAX_ID
AND LINES.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND LINES.MEMO_LINE_ID = M.MEMO_LINE_ID(+)
AND LINES.ORG_ID = MSI.ORGANIZATION_ID(+)