Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

left outer join

440433Mar 17 2005 — edited Mar 17 2005
I have this query that gives me an error, can someone help out.
SELECT RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE AS Month,
RA_CUSTOMER_TRX_ALL.TRX_NUMBER AS "Transaction",
RA_SALESREPS_ALL.NAME AS "Sales Rep",
RA_TERRITORIES.NAME AS "Sales Territory",
MTL_SYSTEM_ITEMS.SEGMENT1 AS "Inventory ID",
MTL_SYSTEM_ITEMS.DESCRIPTION AS "Inv Desc",
RA_CUSTOMERS.CUSTOMER_NAME AS Customerfull,
--'Individual' AS customer,
RA_CUSTOMERS.Sales_Channel_Code AS "Cust Channel",
RA_CUSTOMERS.CUSTOMER_CLASS_CODE AS "Cust Class",
RA_CUSTOMERS.CUSTOMER_CATEGORY_CODE AS "Cust Category",
RA_CUSTOMER_TRX_ALL.TRX_DATE AS TrxDate,
RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED AS QTY,
MTL_SYSTEM_ITEMS.segment1,
RA_CUSTOMER_TRX_LINES_ALL.REVENUE_AMOUNT/12,
ra_customer_trx_lines_all.revenue_amount AS Sales,
RA_ADDRESSES_ALL.CITY AS "Ship City",
RA_ADDRESSES_ALL.STATE AS "Ship State",
FND_TERRITORIES_VL.TERRITORY_SHORT_NAME AS "Ship Country",
RA_ADDRESSES_ALL.STATE || FND_TERRITORIES_VL.TERRITORY_SHORT_NAME AS StCountry,
MTL_SYSTEM_ITEMS.ATTRIBUTE3 AS "Type",
MTL_SYSTEM_ITEMS.ATTRIBUTE4 AS Delivery,
MTL_SYSTEM_ITEMS.ATTRIBUTE8 AS "Language",
MTL_SYSTEM_ITEMS.ATTRIBUTE5 AS Version,
MTL_SYSTEM_ITEMS.ATTRIBUTE7 AS Product,
MTL_SYSTEM_ITEMS.SEGMENT1 AS PRODUCTGROUP,
DECODE(sign(ra_customer_trx_lines_all.revenue_amount-0),-1,'Sale','Credit') AS SalesVCredits,
RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,
RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS,
RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE,
GL_CODE_COMBINATIONS.SEGMENT1 AS "GL COMPANY",
GL_CODE_COMBINATIONS.SEGMENT3 AS "GL ACCOUNT",
RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT
FROM (((((((((AR_RA_CUSTOMER_TRX_ALL LEFT JOIN AR_RA_CUSTOMER_TRX_LINES_ALL ON AR_RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = AR_RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID)
LEFT JOIN AR_RA_CUSTOMERS ON AR_RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID = AR_RA_CUSTOMERS.CUSTOMER_ID)
LEFT JOIN AR_RA_SALESREPS_ALL ON AR_RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID = AR_RA_SALESREPS_ALL.SALESREP_ID)
LEFT JOIN AR_RA_SALESREP_TERRITORIES ON AR_RA_SALESREPS_ALL.SALESREP_ID = AR_RA_SALESREP_TERRITORIES.SALESREP_ID)
LEFT JOIN INV_MTL_SYSTEM_ITEMS ON AR_RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID = INV_MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID)
LEFT JOIN (AR_RA_ADDRESSES_ALL RIGHT JOIN AR_RA_SITE_USES_ALL ON AR_RA_ADDRESSES_ALL.ADDRESS_ID = AR_RA_SITE_USES_ALL.ADDRESS_ID) ON AR_RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID = AR_RA_SITE_USES_ALL.SITE_USE_ID)
LEFT JOIN AR_RA_TERRITORIES ON AR_RA_SALESREP_TERRITORIES.TERRITORY_ID = AR_RA_TERRITORIES.TERRITORY_ID)
LEFT JOIN APPS_FND_TERRITORIES_VL ON AR_RA_ADDRESSES_ALL.COUNTRY = APPS_FND_TERRITORIES_VL.TERRITORY_CODE)
LEFT JOIN APPS_RA_CUST_TRX_LINE_GL_DIST_ALL ON AR_RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID = APPS_RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_LINE_ID)
LEFT JOIN APPS_GL_CODE_COMBINATIONS ON APPS_RA_CUST_TRX_LINE_GL_DIST_ALL.CODE_COMBINATION_ID = APPS_GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
WHERE (((RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '184613'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '20088'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22619'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22620'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22617'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22618'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22611'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22612'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22613'
And (RA_CUSTOMER_TRX_ALL.TRX_NUMBER) <> '22614')
AND ((RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS)='REV') AND
(((RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE) > '31-DEC-04') and
(RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE) < '02-MAR-05') AND
((GL_CODE_COMBINATIONS.SEGMENT1)='01' Or (GL_CODE_COMBINATIONS.SEGMENT1)='30' Or
(GL_CODE_COMBINATIONS.SEGMENT1)='S1' Or (GL_CODE_COMBINATIONS.SEGMENT1)='S2') AND
((GL_CODE_COMBINATIONS.SEGMENT3) in ( '401000', '405000')) AND
((RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID) <> 1281 And
(RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID) <> 5501) AND
((RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE) <> 'TAX'));
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 14 2005
Added on Mar 17 2005
5 comments
517 views