[Home] [Help]
SELECT customer_trx_id, customer_trx_line_id, line_number, line_type,
description, quantity, unit_of_measure_name, unit_price,
extended_amount, sales_order, uom_code, trx_number,
tax_exists_for_this_line_flag, line_tax_rate, tax_code,
printed_tax_name, 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,
unformatted_unit_price, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15, set_of_books_id, reason_code,
quantity_ordered, quantity_credited, unit_standard_price,
sales_order_line, sales_order_date, accounting_rule_duration,
attribute_category, rule_start_date, interface_line_context,
sales_order_source, revenue_amount, default_ussgl_transaction_code,
default_ussgl_trx_code_context, last_period_to_credit, item_context,
tax_exempt_flag, tax_exempt_number, tax_exempt_reason_code,
tax_vendor_return_code, global_attribute_category,
gross_unit_selling_price, gross_extended_amount,
extended_acctd_amount, mrc_extended_acctd_amount, org_id,
global_attribute1, global_attribute2, global_attribute3,
global_attribute4, global_attribute5, global_attribute6,
global_attribute7, global_attribute8, global_attribute9,
global_attribute10, global_attribute11, global_attribute12,
global_attribute13, global_attribute14, global_attribute15,
global_attribute16, global_attribute17, global_attribute18,
global_attribute19, global_attribute20, transaction_line_number,
transaction_currency_code, transaction_line_desc, transaction_date,
transaction_quantity, unit_selling_price,
unit_selling_price
|| transaction_currency_code code_unit_selling_price,
lines_extended_amount
|| transaction_currency_code code_lines_extended_amount,
tax_amount || transaction_currency_code code_tax_amount,
(lines_extended_amount + tax_amount)
|| transaction_currency_code code_line_gross_amount,
lines_extended_amount, bill_to_customer_id, inventory_item_id,
country_of_origin, customs_declaration_number, tax_amount, tax_rate,VAT_tax_amount, VAT_tax_rate,
excise_tax_amount, excise_tax_rate, exempt_tax_amount, exempt_tax_rate,tax_type_code,
item_name, item_description, item_uom_code, item_uom_name,tax_classif_code,
(lines_extended_amount + tax_amount) line_gross_amount,
NULL null_value
FROM (SELECT lines.customer_trx_id customer_trx_id,
lines.customer_trx_line_id customer_trx_line_id,
TO_CHAR (lines.line_number) line_number,
lines.line_type line_type,
NVL(ar_invoice_sql_func_pub.get_description(lines.customer_trx_line_id),lines.description) description,
TO_CHAR (NVL (lines.quantity_invoiced,lines.quantity_credited)) quantity,
uom.unit_of_measure unit_of_measure_name,
TO_CHAR(NVL (lines.unit_selling_price, 0)
,fnd_currency.get_format_mask(trx.invoice_currency_code,40)) unit_price,
TO_CHAR(lines.extended_amount,fnd_currency.get_format_mask
(trx.invoice_currency_code,40)) extended_amount,
lines.sales_order, lines.uom_code, trx.trx_number,
ar_invoice_sql_func_pub.get_taxyn(lines.customer_trx_line_id) tax_exists_for_this_line_flag,
ar_bpa_utils_pkg.fn_get_line_taxrate(lines.customer_trx_line_id)line_tax_rate,
ar_bpa_utils_pkg.fn_get_line_taxcode(lines.customer_trx_line_id)tax_code,
ar_bpa_utils_pkg.fn_get_line_taxname(lines.customer_trx_line_id)printed_tax_name,
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,
TO_CHAR(NVL (lines.unit_selling_price, 0)) unformatted_unit_price,
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.set_of_books_id, lines.reason_code,
lines.quantity_ordered, lines.quantity_credited,
lines.unit_standard_price, lines.sales_order_line,
lines.sales_order_date, lines.accounting_rule_duration,
lines.attribute_category, lines.rule_start_date,
lines.interface_line_context, lines.sales_order_source,
lines.revenue_amount,
lines.default_ussgl_transaction_code,
lines.default_ussgl_trx_code_context,
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.global_attribute_category,
lines.gross_unit_selling_price,
lines.gross_extended_amount, lines.extended_acctd_amount,
lines.mrc_extended_acctd_amount, lines.org_id,
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.line_number transaction_line_number,
trx.invoice_currency_code transaction_currency_code,
NVL(ar_invoice_sql_func_pub.get_description(lines.customer_trx_line_id),lines.description) transaction_line_desc,
trx.trx_date transaction_date,
TO_CHAR(NVL (lines.quantity_invoiced,lines.quantity_credited)) transaction_quantity,
TO_CHAR(NVL (lines.unit_selling_price, 0),fnd_currency.get_format_mask(trx.invoice_currency_code,40)) unit_selling_price,
TO_CHAR(lines.extended_amount,fnd_currency.get_format_mask(trx.invoice_currency_code,40)) lines_extended_amount,
trx.bill_to_customer_id bill_to_customer_id,
lines.inventory_item_id inventory_item_id,
lines.global_attribute1 country_of_origin,
lines.global_attribute2 customs_declaration_number,
SUM (rctl_tax.extended_amount) tax_amount,
SUM (rctl_tax.tax_rate) tax_rate,
SUM(DECODE(zx_tax.tax_type_code,'VAT',NVL(rctl_tax.extended_amount,0),0)) VAT_tax_amount,
SUM(DECODE(zx_tax.tax_type_code,'VAT',NVL(rctl_tax.tax_rate,0),0)) VAT_tax_rate,
SUM(DECODE(zx_tax.tax_type_code,'EXCISE',NVL(rctl_tax.extended_amount,0),0)) excise_tax_amount,
SUM(DECODE(zx_tax.tax_type_code,'EXCISE',NVL(rctl_tax.tax_rate,0),0)) excise_tax_rate,
SUM(DECODE(zx_tax.tax_type_code,'Exempt Vat',NVL(rctl_tax.extended_amount,0),0)) exempt_tax_amount,
SUM(DECODE(zx_tax.tax_type_code,'Exempt Vat',NVL(rctl_tax.tax_rate,0),0)) exempt_tax_rate,
zx_tax.tax_type_code tax_type_code,
LTRIM (RTRIM ( msi.segment1|| ' '
|| msi.segment2|| ' '
|| msi.segment3,' '),
' ') item_name,
NVL (msi.description, lines.description) item_description,
NVL (msi.primary_uom_code, lines.uom_code) item_uom_code,
NVL (msi.primary_unit_of_measure,uom.unit_of_measure) item_uom_name,
lines.tax_classification_code tax_classif_code
FROM mtl_units_of_measure uom,
ra_customer_trx_lines lines,
ra_customer_trx trx,
mtl_system_items msi,
ra_customer_trx_lines rctl_tax,
--ra_customer_trx_lines rctl_excise,
--ra_customer_trx_lines rctl_exempt,
zx_lines zx_tax
--zx_lines zx_excise,
--zx_lines zx_exempt
WHERE trx.customer_trx_id = lines.customer_trx_id
AND trx.complete_flag = 'Y'
AND lines.uom_code = uom.uom_code(+)
AND lines.line_type = 'LINE'
AND lines.inventory_item_id = msi.inventory_item_id(+)
AND lines.org_id = msi.organization_id(+)
AND lines.customer_trx_id = rctl_tax.customer_trx_id(+)
AND lines.customer_trx_line_id = rctl_tax.link_to_cust_trx_line_id(+)
AND rctl_tax.line_type(+) = 'TAX'
AND rctl_tax.tax_line_id = zx_tax.tax_line_id
--AND zx_tax.tax_type_code = 'VAT'
--AND lines.customer_trx_id = rctl_excise.customer_trx_id(+)
--AND lines.customer_trx_line_id = rctl_excise.link_to_cust_trx_line_id(+)
--AND rctl_excise.line_type(+) = 'TAX'
--AND rctl_excise.tax_line_id = zx_excise.tax_line_id
--AND zx_excise.tax_type_code = 'EXCISE'
--AND lines.customer_trx_id = rctl_exempt.customer_trx_id(+)
--AND lines.customer_trx_line_id = rctl_exempt.link_to_cust_trx_line_id(+)
--AND rctl_exempt.line_type(+) = 'TAX'
--AND rctl_exempt.tax_line_id = zx_exempt.tax_line_id
--AND zx_exempt.tax_type_code = 'Exempt Vat'
GROUP BY lines.customer_trx_id,
lines.customer_trx_line_id,
TO_CHAR (lines.line_number),
lines.line_type,
NVL(ar_invoice_sql_func_pub.get_description(lines.customer_trx_line_id),lines.description),
TO_CHAR (NVL (lines.quantity_invoiced,lines.quantity_credited)),
uom.unit_of_measure,
TO_CHAR(NVL (lines.unit_selling_price, 0),
fnd_currency.get_format_mask(trx.invoice_currency_code,40)),
TO_CHAR(lines.extended_amount,fnd_currency.get_format_mask
(trx.invoice_currency_code,40)),
lines.sales_order,
lines.uom_code,
trx.trx_number,
ar_invoice_sql_func_pub.get_taxyn(lines.customer_trx_line_id),
ar_bpa_utils_pkg.fn_get_line_taxrate(lines.customer_trx_line_id),
ar_bpa_utils_pkg.fn_get_line_taxcode(lines.customer_trx_line_id),
ar_bpa_utils_pkg.fn_get_line_taxname(lines.customer_trx_line_id),
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,
TO_CHAR (NVL (lines.unit_selling_price, 0)),
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.set_of_books_id,
lines.reason_code,
lines.quantity_ordered,
lines.quantity_credited,
lines.unit_standard_price,
lines.sales_order_line,
lines.sales_order_date,
lines.accounting_rule_duration,
lines.attribute_category,
lines.rule_start_date,
lines.interface_line_context,
lines.sales_order_source,
lines.revenue_amount,
lines.default_ussgl_transaction_code,
lines.default_ussgl_trx_code_context,
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.global_attribute_category,
lines.gross_unit_selling_price,
lines.gross_extended_amount,
lines.extended_acctd_amount,
lines.mrc_extended_acctd_amount,
lines.org_id,
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.line_number,
trx.invoice_currency_code,
NVL(ar_invoice_sql_func_pub.get_description(lines.customer_trx_line_id),lines.description),
trx.trx_date,
TO_CHAR (NVL (lines.quantity_invoiced,lines.quantity_credited)),
TO_CHAR(NVL (lines.unit_selling_price, 0),fnd_currency.get_format_mask
(trx.invoice_currency_code,40)),
TO_CHAR(lines.extended_amount,fnd_currency.get_format_mask
(trx.invoice_currency_code,40)),
trx.bill_to_customer_id,
lines.inventory_item_id,
lines.attribute12,
lines.attribute13,
zx_tax.tax_type_code,
LTRIM (RTRIM ( msi.segment1|| ' '
|| msi.segment2|| ' '
|| msi.segment3,' '),
' '),
NVL (msi.description, lines.description),
NVL (msi.primary_uom_code, lines.uom_code),
NVL (msi.primary_unit_of_measure, uom.unit_of_measure),
lines.tax_classification_code)
SELECT CUSTOMER_TRX_ID
, CUSTOMER_TRX_LINE_ID
, LINE_NUMBER
, LINE_TYPE
,
DESCRIPTION
, QUANTITY
, UNIT_OF_MEASURE_NAME
, UNIT_PRICE
,
EXTENDED_AMOUNT
, SALES_ORDER
, UOM_CODE
, TRX_NUMBER
,
TAX_EXISTS_FOR_THIS_LINE_FLAG
, LINE_TAX_RATE
, TAX_CODE
,
PRINTED_TAX_NAME
, 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
,
UNFORMATTED_UNIT_PRICE
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
,
ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
,
ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
,
ATTRIBUTE14
, ATTRIBUTE15
, SET_OF_BOOKS_ID
, REASON_CODE
,
QUANTITY_ORDERED
, QUANTITY_CREDITED
, UNIT_STANDARD_PRICE
,
SALES_ORDER_LINE
, SALES_ORDER_DATE
, ACCOUNTING_RULE_DURATION
,
ATTRIBUTE_CATEGORY
, RULE_START_DATE
, INTERFACE_LINE_CONTEXT
,
SALES_ORDER_SOURCE
, REVENUE_AMOUNT
, DEFAULT_USSGL_TRANSACTION_CODE
,
DEFAULT_USSGL_TRX_CODE_CONTEXT
, LAST_PERIOD_TO_CREDIT
, ITEM_CONTEXT
,
TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
,
TAX_VENDOR_RETURN_CODE
, GLOBAL_ATTRIBUTE_CATEGORY
,
GROSS_UNIT_SELLING_PRICE
, GROSS_EXTENDED_AMOUNT
,
EXTENDED_ACCTD_AMOUNT
, MRC_EXTENDED_ACCTD_AMOUNT
, ORG_ID
,
GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE3
,
GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
,
GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
,
GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
,
GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
,
GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
,
GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE20
, TRANSACTION_LINE_NUMBER
,
TRANSACTION_CURRENCY_CODE
, TRANSACTION_LINE_DESC
, TRANSACTION_DATE
,
TRANSACTION_QUANTITY
, UNIT_SELLING_PRICE
,
UNIT_SELLING_PRICE
|| TRANSACTION_CURRENCY_CODE CODE_UNIT_SELLING_PRICE
,
LINES_EXTENDED_AMOUNT
|| TRANSACTION_CURRENCY_CODE CODE_LINES_EXTENDED_AMOUNT
,
TAX_AMOUNT || TRANSACTION_CURRENCY_CODE CODE_TAX_AMOUNT
,
(LINES_EXTENDED_AMOUNT + TAX_AMOUNT)
|| TRANSACTION_CURRENCY_CODE CODE_LINE_GROSS_AMOUNT
,
LINES_EXTENDED_AMOUNT
, BILL_TO_CUSTOMER_ID
, INVENTORY_ITEM_ID
,
COUNTRY_OF_ORIGIN
, CUSTOMS_DECLARATION_NUMBER
, TAX_AMOUNT
, TAX_RATE
, VAT_TAX_AMOUNT
, VAT_TAX_RATE
,
EXCISE_TAX_AMOUNT
, EXCISE_TAX_RATE
, EXEMPT_TAX_AMOUNT
, EXEMPT_TAX_RATE
, TAX_TYPE_CODE
,
ITEM_NAME
, ITEM_DESCRIPTION
, ITEM_UOM_CODE
, ITEM_UOM_NAME
, TAX_CLASSIF_CODE
,
(LINES_EXTENDED_AMOUNT + TAX_AMOUNT) LINE_GROSS_AMOUNT
,
NULL NULL_VALUE
FROM (SELECT LINES.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
,
LINES.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
,
TO_CHAR (LINES.LINE_NUMBER) LINE_NUMBER
,
LINES.LINE_TYPE LINE_TYPE
,
NVL(AR_INVOICE_SQL_FUNC_PUB.GET_DESCRIPTION(LINES.CUSTOMER_TRX_LINE_ID)
, LINES.DESCRIPTION) DESCRIPTION
,
TO_CHAR (NVL (LINES.QUANTITY_INVOICED
, LINES.QUANTITY_CREDITED)) QUANTITY
,
UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE_NAME
,
TO_CHAR(NVL (LINES.UNIT_SELLING_PRICE
, 0)
, FND_CURRENCY.GET_FORMAT_MASK(TRX.INVOICE_CURRENCY_CODE
, 40)) UNIT_PRICE
,
TO_CHAR(LINES.EXTENDED_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK
(TRX.INVOICE_CURRENCY_CODE
, 40)) EXTENDED_AMOUNT
,
LINES.SALES_ORDER
, LINES.UOM_CODE
, TRX.TRX_NUMBER
,
AR_INVOICE_SQL_FUNC_PUB.GET_TAXYN(LINES.CUSTOMER_TRX_LINE_ID) TAX_EXISTS_FOR_THIS_LINE_FLAG
,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXRATE(LINES.CUSTOMER_TRX_LINE_ID)LINE_TAX_RATE
,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXCODE(LINES.CUSTOMER_TRX_LINE_ID)TAX_CODE
,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXNAME(LINES.CUSTOMER_TRX_LINE_ID)PRINTED_TAX_NAME
,
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
,
TO_CHAR(NVL (LINES.UNIT_SELLING_PRICE
, 0)) UNFORMATTED_UNIT_PRICE
,
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.SET_OF_BOOKS_ID
, LINES.REASON_CODE
,
LINES.QUANTITY_ORDERED
, LINES.QUANTITY_CREDITED
,
LINES.UNIT_STANDARD_PRICE
, LINES.SALES_ORDER_LINE
,
LINES.SALES_ORDER_DATE
, LINES.ACCOUNTING_RULE_DURATION
,
LINES.ATTRIBUTE_CATEGORY
, LINES.RULE_START_DATE
,
LINES.INTERFACE_LINE_CONTEXT
, LINES.SALES_ORDER_SOURCE
,
LINES.REVENUE_AMOUNT
,
LINES.DEFAULT_USSGL_TRANSACTION_CODE
,
LINES.DEFAULT_USSGL_TRX_CODE_CONTEXT
,
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.GLOBAL_ATTRIBUTE_CATEGORY
,
LINES.GROSS_UNIT_SELLING_PRICE
,
LINES.GROSS_EXTENDED_AMOUNT
, LINES.EXTENDED_ACCTD_AMOUNT
,
LINES.MRC_EXTENDED_ACCTD_AMOUNT
, LINES.ORG_ID
,
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.LINE_NUMBER TRANSACTION_LINE_NUMBER
,
TRX.INVOICE_CURRENCY_CODE TRANSACTION_CURRENCY_CODE
,
NVL(AR_INVOICE_SQL_FUNC_PUB.GET_DESCRIPTION(LINES.CUSTOMER_TRX_LINE_ID)
, LINES.DESCRIPTION) TRANSACTION_LINE_DESC
,
TRX.TRX_DATE TRANSACTION_DATE
,
TO_CHAR(NVL (LINES.QUANTITY_INVOICED
, LINES.QUANTITY_CREDITED)) TRANSACTION_QUANTITY
,
TO_CHAR(NVL (LINES.UNIT_SELLING_PRICE
, 0)
, FND_CURRENCY.GET_FORMAT_MASK(TRX.INVOICE_CURRENCY_CODE
, 40)) UNIT_SELLING_PRICE
,
TO_CHAR(LINES.EXTENDED_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(TRX.INVOICE_CURRENCY_CODE
, 40)) LINES_EXTENDED_AMOUNT
,
TRX.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
,
LINES.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
,
LINES.GLOBAL_ATTRIBUTE1 COUNTRY_OF_ORIGIN
,
LINES.GLOBAL_ATTRIBUTE2 CUSTOMS_DECLARATION_NUMBER
,
SUM (RCTL_TAX.EXTENDED_AMOUNT) TAX_AMOUNT
,
SUM (RCTL_TAX.TAX_RATE) TAX_RATE
,
SUM(DECODE(ZX_TAX.TAX_TYPE_CODE
, 'VAT'
, NVL(RCTL_TAX.EXTENDED_AMOUNT
, 0)
, 0)) VAT_TAX_AMOUNT
,
SUM(DECODE(ZX_TAX.TAX_TYPE_CODE
, 'VAT'
, NVL(RCTL_TAX.TAX_RATE
, 0)
, 0)) VAT_TAX_RATE
,
SUM(DECODE(ZX_TAX.TAX_TYPE_CODE
, 'EXCISE'
, NVL(RCTL_TAX.EXTENDED_AMOUNT
, 0)
, 0)) EXCISE_TAX_AMOUNT
,
SUM(DECODE(ZX_TAX.TAX_TYPE_CODE
, 'EXCISE'
, NVL(RCTL_TAX.TAX_RATE
, 0)
, 0)) EXCISE_TAX_RATE
,
SUM(DECODE(ZX_TAX.TAX_TYPE_CODE
, 'EXEMPT VAT'
, NVL(RCTL_TAX.EXTENDED_AMOUNT
, 0)
, 0)) EXEMPT_TAX_AMOUNT
,
SUM(DECODE(ZX_TAX.TAX_TYPE_CODE
, 'EXEMPT VAT'
, NVL(RCTL_TAX.TAX_RATE
, 0)
, 0)) EXEMPT_TAX_RATE
,
ZX_TAX.TAX_TYPE_CODE TAX_TYPE_CODE
,
LTRIM (RTRIM ( MSI.SEGMENT1|| ' '
|| MSI.SEGMENT2|| ' '
|| MSI.SEGMENT3
, ' ')
,
' ') ITEM_NAME
,
NVL (MSI.DESCRIPTION
, LINES.DESCRIPTION) ITEM_DESCRIPTION
,
NVL (MSI.PRIMARY_UOM_CODE
, LINES.UOM_CODE) ITEM_UOM_CODE
,
NVL (MSI.PRIMARY_UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE) ITEM_UOM_NAME
,
LINES.TAX_CLASSIFICATION_CODE TAX_CLASSIF_CODE
FROM MTL_UNITS_OF_MEASURE UOM
,
RA_CUSTOMER_TRX_LINES LINES
,
RA_CUSTOMER_TRX TRX
,
MTL_SYSTEM_ITEMS MSI
,
RA_CUSTOMER_TRX_LINES RCTL_TAX
,
--RA_CUSTOMER_TRX_LINES RCTL_EXCISE
,
--RA_CUSTOMER_TRX_LINES RCTL_EXEMPT
,
ZX_LINES ZX_TAX
--ZX_LINES ZX_EXCISE
,
--ZX_LINES ZX_EXEMPT
WHERE TRX.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID
AND TRX.COMPLETE_FLAG = 'Y'
AND LINES.UOM_CODE = UOM.UOM_CODE(+)
AND LINES.LINE_TYPE = 'LINE'
AND LINES.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND LINES.ORG_ID = MSI.ORGANIZATION_ID(+)
AND LINES.CUSTOMER_TRX_ID = RCTL_TAX.CUSTOMER_TRX_ID(+)
AND LINES.CUSTOMER_TRX_LINE_ID = RCTL_TAX.LINK_TO_CUST_TRX_LINE_ID(+)
AND RCTL_TAX.LINE_TYPE(+) = 'TAX'
AND RCTL_TAX.TAX_LINE_ID = ZX_TAX.TAX_LINE_ID
--AND ZX_TAX.TAX_TYPE_CODE = 'VAT'
--AND LINES.CUSTOMER_TRX_ID = RCTL_EXCISE.CUSTOMER_TRX_ID(+)
--AND LINES.CUSTOMER_TRX_LINE_ID = RCTL_EXCISE.LINK_TO_CUST_TRX_LINE_ID(+)
--AND RCTL_EXCISE.LINE_TYPE(+) = 'TAX'
--AND RCTL_EXCISE.TAX_LINE_ID = ZX_EXCISE.TAX_LINE_ID
--AND ZX_EXCISE.TAX_TYPE_CODE = 'EXCISE'
--AND LINES.CUSTOMER_TRX_ID = RCTL_EXEMPT.CUSTOMER_TRX_ID(+)
--AND LINES.CUSTOMER_TRX_LINE_ID = RCTL_EXEMPT.LINK_TO_CUST_TRX_LINE_ID(+)
--AND RCTL_EXEMPT.LINE_TYPE(+) = 'TAX'
--AND RCTL_EXEMPT.TAX_LINE_ID = ZX_EXEMPT.TAX_LINE_ID
--AND ZX_EXEMPT.TAX_TYPE_CODE = 'EXEMPT VAT'
GROUP BY LINES.CUSTOMER_TRX_ID
,
LINES.CUSTOMER_TRX_LINE_ID
,
TO_CHAR (LINES.LINE_NUMBER)
,
LINES.LINE_TYPE
,
NVL(AR_INVOICE_SQL_FUNC_PUB.GET_DESCRIPTION(LINES.CUSTOMER_TRX_LINE_ID)
, LINES.DESCRIPTION)
,
TO_CHAR (NVL (LINES.QUANTITY_INVOICED
, LINES.QUANTITY_CREDITED))
,
UOM.UNIT_OF_MEASURE
,
TO_CHAR(NVL (LINES.UNIT_SELLING_PRICE
, 0)
,
FND_CURRENCY.GET_FORMAT_MASK(TRX.INVOICE_CURRENCY_CODE
, 40))
,
TO_CHAR(LINES.EXTENDED_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK
(TRX.INVOICE_CURRENCY_CODE
, 40))
,
LINES.SALES_ORDER
,
LINES.UOM_CODE
,
TRX.TRX_NUMBER
,
AR_INVOICE_SQL_FUNC_PUB.GET_TAXYN(LINES.CUSTOMER_TRX_LINE_ID)
,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXRATE(LINES.CUSTOMER_TRX_LINE_ID)
,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXCODE(LINES.CUSTOMER_TRX_LINE_ID)
,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXNAME(LINES.CUSTOMER_TRX_LINE_ID)
,
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
,
TO_CHAR (NVL (LINES.UNIT_SELLING_PRICE
, 0))
,
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.SET_OF_BOOKS_ID
,
LINES.REASON_CODE
,
LINES.QUANTITY_ORDERED
,
LINES.QUANTITY_CREDITED
,
LINES.UNIT_STANDARD_PRICE
,
LINES.SALES_ORDER_LINE
,
LINES.SALES_ORDER_DATE
,
LINES.ACCOUNTING_RULE_DURATION
,
LINES.ATTRIBUTE_CATEGORY
,
LINES.RULE_START_DATE
,
LINES.INTERFACE_LINE_CONTEXT
,
LINES.SALES_ORDER_SOURCE
,
LINES.REVENUE_AMOUNT
,
LINES.DEFAULT_USSGL_TRANSACTION_CODE
,
LINES.DEFAULT_USSGL_TRX_CODE_CONTEXT
,
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.GLOBAL_ATTRIBUTE_CATEGORY
,
LINES.GROSS_UNIT_SELLING_PRICE
,
LINES.GROSS_EXTENDED_AMOUNT
,
LINES.EXTENDED_ACCTD_AMOUNT
,
LINES.MRC_EXTENDED_ACCTD_AMOUNT
,
LINES.ORG_ID
,
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.LINE_NUMBER
,
TRX.INVOICE_CURRENCY_CODE
,
NVL(AR_INVOICE_SQL_FUNC_PUB.GET_DESCRIPTION(LINES.CUSTOMER_TRX_LINE_ID)
, LINES.DESCRIPTION)
,
TRX.TRX_DATE
,
TO_CHAR (NVL (LINES.QUANTITY_INVOICED
, LINES.QUANTITY_CREDITED))
,
TO_CHAR(NVL (LINES.UNIT_SELLING_PRICE
, 0)
, FND_CURRENCY.GET_FORMAT_MASK
(TRX.INVOICE_CURRENCY_CODE
, 40))
,
TO_CHAR(LINES.EXTENDED_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK
(TRX.INVOICE_CURRENCY_CODE
, 40))
,
TRX.BILL_TO_CUSTOMER_ID
,
LINES.INVENTORY_ITEM_ID
,
LINES.ATTRIBUTE12
,
LINES.ATTRIBUTE13
,
ZX_TAX.TAX_TYPE_CODE
,
LTRIM (RTRIM ( MSI.SEGMENT1|| ' '
|| MSI.SEGMENT2|| ' '
|| MSI.SEGMENT3
, ' ')
,
' ')
,
NVL (MSI.DESCRIPTION
, LINES.DESCRIPTION)
,
NVL (MSI.PRIMARY_UOM_CODE
, LINES.UOM_CODE)
,
NVL (MSI.PRIMARY_UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE)
,
LINES.TAX_CLASSIFICATION_CODE)
|
|
|
|