select distinct a.

*,
to_char(rownum) as sequence_number
from
(select
distinct base.*
---to_char(rownum) as sequence_number
from

SELECT c.customer_trx_id customer_trx_id_line,

null customer_trx_line_id,
null line_number,

c.line_type line_type,
AR_BPA_UTILS_PKG.fn_get_line_description(c.customer_trx_line_id)
line_description,
decode(c.line_type, 'TAX', null, nvl(c.quantity_invoiced, c.quantity_credited))
quantity,
uom.unit_of_measure unit_of_measure_name,
eitem.item_number item_number,
--null unit_of_measure_name,
c.unit_selling_price unit_price,

to_char(c.extended_amount,fnd_currency.get_format_mask(trx.invoice_currency_co
de,40))
extended_amount,
c.sales_order line_sales_order,
c.uom_code,
 -- null uom_code,
trx.trx_number line_trx_number,
decode(c.line_type, 'TAX', null, AR_INVOICE_SQL_FUNC_PUB.get_taxyn
( c.customer_trx_line_id ))
tax_exists_for_this_line_flag,
c.tax_rate as line_tax_rate,
--decode(c.line_type, 'TAX', v.tax_code, null) as line_tax_code,
null line_tax_code,
null printed_tax_name,
--decode(c.line_type, 'TAX', v.printed_tax_name, null) as
line_printed_tax_name,

(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
c.interface_line_attribute2 group_attribute2,
--- c.interface_line_attribute3,
null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
 c.interface_line_attribute9,
c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
----c.ATTRIBUTE7,
null ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
 c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
nvl((SELECT SUM(c.extended_amount) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
----- AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
---- and attribute7 = c.attribute7
and attribute7 !='3'
group by attribute1,interface_line_attribute2),0)rental,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
 AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='3'
group by attribute1,interface_line_attribute2),0)reserve,
(nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='15'
group by attribute1,interface_line_attribute2),0) -
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='13'
group by attribute1,interface_line_attribute2),0))Reserve_Adjustment,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='13'
group by
attribute1,interface_line_attribute2),0)Inventory_Holding_Expense,
 nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='12'
group by attribute1,interface_line_attribute2),0)Full_Yield,
(nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='13'
group by attribute1,interface_line_attribute2),0) +
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='8'
group by attribute1,interface_line_attribute2),0))Vehicle_Sale_Fee,
nvl((SELECT nvl(count(c.line_number),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='1'
 group by attribute1,interface_line_attribute2),0)Fuel_Transactions,
nvl((SELECT nvl(sum(nvl(c.quantity_invoiced, c.quantity_credited)),0)
FROM ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='1'
group by attribute1,interface_line_attribute2),0)Fuel_Gallons,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='1'
group by attribute1,interface_line_attribute2),0)Fuel_Cost,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='4'
group by attribute1,interface_line_attribute2),0)Maintenance_Non_PO,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
 and attribute7 in ('3','5')
group by attribute1,interface_line_attribute2),0)Maintenance_PO,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 in ('6','10')
group by attribute1,interface_line_attribute2),0)Accident_Repairs,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 ='7'
group by attribute1,interface_line_attribute2),0)Car_Rental,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
and attribute7 in ('5','10')
group by attribute1,interface_line_attribute2),0)Service_Fees,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
 -----and attribute7 = c.attribute7
and attribute7 ='8'
group by attribute1,interface_line_attribute2),0)Miscellaneous,
nvl((SELECT nvl(SUM(c.extended_amount),0) FROM
ra_customer_trx_lines_all WHERE customer_trx_id= c.customer_trx_id
---AND customer_trx_line_id=C.customer_trx_line_id
AND attribute1=c.attribute1
and interface_line_attribute2=c.interface_line_attribute2
-----and attribute7 = c.attribute7
----and attribute7 ='8'
group by attribute1,interface_line_attribute2),0)Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE

UNION ALL

SELECT c.customer_trx_id customer_trx_id_line,

c.customer_trx_line_id customer_trx_line_id,
to_char(decode( c2.line_number, null, c.line_number, null)) line_number,
c.line_type line_type,
AR_BPA_UTILS_PKG.fn_get_line_description(c.customer_trx_line_id)
line_description,
decode(c.line_type, 'TAX', null, nvl(c.quantity_invoiced, c.quantity_credited))
quantity,
uom.unit_of_measure unit_of_measure_name,
eitem.item_number item_number,
--null unit_of_measure_name,
c.unit_selling_price unit_price,

to_char(c.extended_amount,fnd_currency.get_format_mask(trx.invoice_currency_co
de,40))
extended_amount,
c.sales_order line_sales_order,
c.uom_code,
 -- null uom_code,
trx.trx_number line_trx_number,
decode(c.line_type, 'TAX', null, AR_INVOICE_SQL_FUNC_PUB.get_taxyn
( c.customer_trx_line_id ))
tax_exists_for_this_line_flag,
c.tax_rate as line_tax_rate,
--decode(c.line_type, 'TAX', v.tax_code, null) as line_tax_code,
null line_tax_code,
null printed_tax_name,
--decode(c.line_type, 'TAX', v.printed_tax_name, null) as
line_printed_tax_name,

(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
c.interface_line_attribute2 group_attribute2,
c.interface_line_attribute3,
---null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
 c.interface_line_attribute9,
c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
null rental,
null reserve,
null Reserve_Adjustment,
null Inventory_Holding_Expense,
null Full_Yield,
null Vehicle_Sale_Fee,
null Fuel_Transactions,
null Fuel_Gallons,
null Fuel_Cost,
null Maintenance_Non_PO,
null Maintenance_PO,
null Accident_Repairs,
 null Car_Rental,
null Service_Fees,
null Miscellaneous,
null Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] not in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE

/*order by
decode( c.line_number,
null, decode( c.line_type,
'LINE', c.line_number*10000+0,
'TAX', c.line_number*10000+8000,
100000000000),
decode( c2.line_type,
'LINE', c2.line_number*10000+0,
'TAX', c2.line_number*10000+8000,
c2.line_number*10000+9000) +
decode( c.line_type,
'LINE', 0, 'CB', 0,
'TAX', 8000,
'FREIGHT', 9000) +
c.line_number )*/
) base)a

(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
 c.interface_line_attribute2 group_attribute2,
c.interface_line_attribute3,
---null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
c.interface_line_attribute9,
c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
null rental,
null reserve,
null Reserve_Adjustment,
null Inventory_Holding_Expense,
 null Full_Yield,
null Vehicle_Sale_Fee,
null Fuel_Transactions,
null Fuel_Gallons,
null Fuel_Cost,
null Maintenance_Non_PO,
null Maintenance_PO,
null Accident_Repairs,
null Car_Rental,
null Service_Fees,
null Miscellaneous,
null Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] not in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE

/*order by
decode( c.line_number,
null, decode( c.line_type,
'LINE', c.line_number*10000+0,
'TAX', c.line_number*10000+8000,
100000000000),
decode( c2.line_type,
'LINE', c2.line_number*10000+0,
'TAX', c2.line_number*10000+8000,
c2.line_number*10000+9000) +
decode( c.line_type,
'LINE', 0, 'CB', 0,
'TAX', 8000,
'FREIGHT', 9000) +
c.line_number )*/
) base)a
(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
 WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
c.interface_line_attribute2 group_attribute2,
c.interface_line_attribute3,
---null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
c.interface_line_attribute9,
c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
 c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
 c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
null rental,
null reserve,
null Reserve_Adjustment,
null Inventory_Holding_Expense,
null Full_Yield,
null Vehicle_Sale_Fee,
null Fuel_Transactions,
null Fuel_Gallons,
null Fuel_Cost,
null Maintenance_Non_PO,
null Maintenance_PO,
null Accident_Repairs,
null Car_Rental,
null Service_Fees,
null Miscellaneous,
null Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] not in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE

/*order by
decode( c.line_number,
null, decode( c.line_type,
'LINE', c.line_number*10000+0,
'TAX', c.line_number*10000+8000,
100000000000),
decode( c2.line_type,
'LINE', c2.line_number*10000+0,
'TAX', c2.line_number*10000+8000,
c2.line_number*10000+9000) +
decode( c.line_type,
'LINE', 0, 'CB', 0,
'TAX', 8000,
 'FREIGHT', 9000) +
c.line_number )*/
) base)a
(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
c.interface_line_attribute2 group_attribute2,
c.interface_line_attribute3,
---null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
c.interface_line_attribute9,
c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
 to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
 c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
null rental,
null reserve,
null Reserve_Adjustment,
null Inventory_Holding_Expense,
null Full_Yield,
null Vehicle_Sale_Fee,
null Fuel_Transactions,
null Fuel_Gallons,
null Fuel_Cost,
null Maintenance_Non_PO,
null Maintenance_PO,
null Accident_Repairs,
null Car_Rental,
null Service_Fees,
null Miscellaneous,
null Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
 ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] not in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE

/*order by
decode( c.line_number,
null, decode( c.line_type,
'LINE', c.line_number*10000+0,
'TAX', c.line_number*10000+8000,
100000000000),
 decode( c2.line_type,
'LINE', c2.line_number*10000+0,
'TAX', c2.line_number*10000+8000,
c2.line_number*10000+9000) +
decode( c.line_type,
'LINE', 0, 'CB', 0,
'TAX', 8000,
'FREIGHT', 9000) +
c.line_number )*/
) base)a
(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
c.interface_line_attribute2 group_attribute2,
c.interface_line_attribute3,
---null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
c.interface_line_attribute9,
 c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
null rental,
null reserve,
null Reserve_Adjustment,
null Inventory_Holding_Expense,
null Full_Yield,
null Vehicle_Sale_Fee,
null Fuel_Transactions,
null Fuel_Gallons,
null Fuel_Cost,
null Maintenance_Non_PO,
null Maintenance_PO,
null Accident_Repairs,
null Car_Rental,
 null Service_Fees,
null Miscellaneous,
null Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] not in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE
/*order by
decode( c.line_number,
null, decode( c.line_type,
'LINE', c.line_number*10000+0,
'TAX', c.line_number*10000+8000,
100000000000),
decode( c2.line_type,
'LINE', c2.line_number*10000+0,
'TAX', c2.line_number*10000+8000,
c2.line_number*10000+9000) +
decode( c.line_type,
'LINE', 0, 'CB', 0,
'TAX', 8000,
'FREIGHT', 9000) +
c.line_number )*/
) base)a
(SELECT party_site.PARTY_SITE_NAME
FROM hz_party_sites party_site,
hz_cust_site_uses_all acct_uses ,
hz_cust_acct_sites_all acct_site
WHERE party_site.party_site_id = acct_site.party_site_id
and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id
and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug:
18705899

c.interface_line_attribute1,
c.interface_line_attribute2,
c.interface_line_attribute2 group_attribute2,
c.interface_line_attribute3,
 ---null interface_line_attribute3,
c.interface_line_attribute4,
c.interface_line_attribute5,
c.interface_line_attribute6,
c.interface_line_attribute7,
c.interface_line_attribute8,
c.interface_line_attribute9,
c.interface_line_attribute10,
c.interface_line_attribute11,
c.interface_line_attribute12,
c.interface_line_attribute13,
c.interface_line_attribute14,
c.interface_line_attribute15,
to_char(c.unit_selling_price) unformatted_unit_price,
to_char(nvl(c.extended_amount,0)) unformatted_extended_amount,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE_NUMBER5,
nvl(c.ATTRIBUTE_NUMBER1,0)ATTRIBUTE_NUMBER1,
nvl(c.ATTRIBUTE_NUMBER2,0)ATTRIBUTE_NUMBER2,
nvl(c.ATTRIBUTE_NUMBER3,0)ATTRIBUTE_NUMBER3,
nvl(c.ATTRIBUTE_NUMBER4,0)ATTRIBUTE_NUMBER4,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.SET_OF_BOOKS_ID,
c.REASON_CODE,
c.QUANTITY_ORDERED,
c.QUANTITY_CREDITED,
c.UNIT_STANDARD_PRICE,
c.SALES_ORDER_LINE,
to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE,
c.ACCOUNTING_RULE_DURATION,
c.ATTRIBUTE_CATEGORY,
to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE,
to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date,
to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date,
c.INTERFACE_LINE_CONTEXT,
c.SALES_ORDER_SOURCE,
c.REVENUE_AMOUNT,
c.DEFAULT_USSGL_TRANSACTION_CODE,
c.DEFAULT_USSGL_TRX_CODE_CONTEXT,
c.LAST_PERIOD_TO_CREDIT,
c.ITEM_CONTEXT,
c.TAX_EXEMPT_FLAG,
c.TAX_EXEMPT_NUMBER,
c.TAX_EXEMPT_REASON_CODE,
c.TAX_VENDOR_RETURN_CODE,
c.GLOBAL_ATTRIBUTE_CATEGORY,
c.GROSS_UNIT_SELLING_PRICE,
c.GROSS_EXTENDED_AMOUNT,
c.EXTENDED_ACCTD_AMOUNT,
c.MRC_EXTENDED_ACCTD_AMOUNT,
c.ORG_ID line_org_id,
c.GLOBAL_ATTRIBUTE1,
c.GLOBAL_ATTRIBUTE2,
c.GLOBAL_ATTRIBUTE3,
c.GLOBAL_ATTRIBUTE4,
c.GLOBAL_ATTRIBUTE5,
c.GLOBAL_ATTRIBUTE6,
c.GLOBAL_ATTRIBUTE7,
c.GLOBAL_ATTRIBUTE8,
c.GLOBAL_ATTRIBUTE9,
c.GLOBAL_ATTRIBUTE10,
c.GLOBAL_ATTRIBUTE11,
c.GLOBAL_ATTRIBUTE12,
c.GLOBAL_ATTRIBUTE13,
c.GLOBAL_ATTRIBUTE14,
c.GLOBAL_ATTRIBUTE15,
c.GLOBAL_ATTRIBUTE16,
c.GLOBAL_ATTRIBUTE17,
c.GLOBAL_ATTRIBUTE18,
c.GLOBAL_ATTRIBUTE19,
c.GLOBAL_ATTRIBUTE20,
null rental,
null reserve,
null Reserve_Adjustment,
null Inventory_Holding_Expense,
null Full_Yield,
null Vehicle_Sale_Fee,
 null Fuel_Transactions,
null Fuel_Gallons,
null Fuel_Cost,
null Maintenance_Non_PO,
null Maintenance_PO,
null Accident_Repairs,
null Car_Rental,
null Service_Fees,
null Miscellaneous,
null Detail_Line_Total
from ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
inv_units_of_measure_vl uom,
ra_customer_trx_all trx,
ra_cust_trx_types_all types,
egp_system_items_vl eitem,
ar_system_parameters_all sysp
--ar_vat_tax_all_vl v
where c.customer_trx_id = :customer_trx_id
AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
and c.uom_code = uom.uom_code(+)
and c.inventory_item_id = eitem.inventory_item_id (+)
AND [Link] not in ('WLS Rental','WLS Disposal','WLS MAP')
and c.org_id = sysp.org_id
and ( (c.inventory_item_id is not null
 and sysp.item_validation_org_id = eitem.organization_id)
or c.inventory_item_id is null)
-----and c.attribute1='130386'
--and c.vat_tax_id = v.vat_tax_id(+)
--and c.org_id = v.org_id(+)
--and c.line_type in ('LINE', 'TAX', 'CB')
&WHERE_LINE_TYPE

/*order by
decode( c.line_number,
null, decode( c.line_type,
'LINE', c.line_number*10000+0,
'TAX', c.line_number*10000+8000,
100000000000),
decode( c2.line_type,
'LINE', c2.line_number*10000+0,
'TAX', c2.line_number*10000+8000,
c2.line_number*10000+9000) +
decode( c.line_type,
'LINE', 0, 'CB', 0,
'TAX', 8000,
'FREIGHT', 9000) +
c.line_number )*/
) base)a