AR Invoice Query

AR Invoice Query

SELECT

RCTA.TRX_NUMBER INVOICE_NO,

HP.PARTY_NAME CUST_NAME,

HCA.ACCOUNT_NUMBER,

RCTA.TRX_DATE INVOICE_DATE,

--------SHIP TO

HP1.PARTY_NAME SHIP_CUST_NAME,

HCS.LOCATION SHIP_TO_LOC,

HL.ADDRESS1 SHIP_TO_ADDR1,

DECODE (HL.CITY,NULL, NULL,HL.CITY || ', ')||

DECODE (HL.STATE,NULL, HL.PROVINCE || ', ',HL.STATE || ', ')||

DECODE (HL.POSTAL_CODE, NULL, NULL, HL.POSTAL_CODE || ', ')||

DECODE (HL.COUNTRY, NULL, NULL, HL.COUNTRY) SHIP_TO_ADDR5,

----------BILL TO

HP.PARTY_NAME BILL_CUST_NAME,

HCS1.LOCATION BILL_TO_LOC,

HL1.ADDRESS1 BILL_TO_ADDR1,

DECODE (HL1.CITY,NULL, NULL, HL1.CITY || ', ')||

DECODE (HL1.STATE, NULL, HL1.PROVINCE || ', ',HL1.STATE || ', ')||

DECODE (HL1.POSTAL_CODE, NULL, NULL,HL1.POSTAL_CODE || ', ' ) ||

DECODE (HL1.COUNTRY, NULL, NULL, HL1.COUNTRY) BILL_TO_ADDR5,

----------REMIT TO

HP.PARTY_NAME REMIT_CUST_NAME,

HCS2.LOCATION REMIT_TO_LOC,

HL2.ADDRESS1 REMIT_TO_ADDR1,

DECODE (HL2.CITY,NULL, NULL, HL1.CITY || ', ')||

DECODE (HL2.STATE, NULL, HL1.PROVINCE || ', ',HL1.STATE || ', ')||

DECODE (HL2.POSTAL_CODE, NULL, NULL,HL1.POSTAL_CODE || ', ' ) ||

DECODE (HL2.COUNTRY, NULL, NULL, HL1.COUNTRY) REMIT_TO_ADDR5,

-------LINES

   RCTL.LINE_NUMBER,

   MSIB.SEGMENT1 ITEM_NAME,

   RCTL.UOM_CODE,

   RCTL.QUANTITY_INVOICED,

   RCTL.UNIT_SELLING_PRICE,

   RCTL.LINE_TYPE,

   (RCTL.QUANTITY_INVOICED*RCTL.UNIT_SELLING_PRICE)LINE_PRICE,

   DECODE(RCTL.QUANTITY_INVOICED*RCTL.UNIT_SELLING_PRICE,NULL,RCTL.EXTENDED_AMOUNT,

   RCTL.QUANTITY_INVOICED*RCTL.UNIT_SELLING_PRICE)TOTAMT

FROM

RA_CUSTOMER_TRX_ALL RCTA,

HZ_PARTIES HP,

HZ_CUST_ACCOUNTS HCA,

HZ_CUST_SITE_USES_ALL HCS,

HZ_CUST_ACCT_SITES_ALL HCAS,

HZ_LOCATIONS HL,

HZ_PARTY_SITES HPS,

--------------------

HZ_CUST_SITE_USES_ALL HCS1,

HZ_CUST_ACCT_SITES_ALL HCAS1,

HZ_LOCATIONS HL1,

HZ_PARTY_SITES HPS1,

---------------------------

RA_CUSTOMER_TRX_LINES_ALL RCTL,

MTL_SYSTEM_ITEMS_B MSIB,

HZ_CUST_SITE_USES_ALL HCS2,

HZ_CUST_ACCT_SITES_ALL HCAS2,

HZ_LOCATIONS HL2,

HZ_PARTY_SITES HPS2,

---------------------

HZ_PARTIES HP1,

HZ_CUST_ACCOUNTS HCA1,

-------------------------

HZ_PARTIES HP2,

HZ_CUST_ACCOUNTS HCA2

--------------------------

WHERE RCTA.TRX_NUMBER = '12024'

AND RCTA.SOLD_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID

AND HCA.PARTY_ID=HP.PARTY_ID

----------------------------------------------

AND RCTA.SHIP_TO_CUSTOMER_ID=HCA1.CUST_ACCOUNT_ID

AND HCA1.PARTY_ID=HP1.PARTY_ID

AND RCTA.SHIP_TO_SITE_USE_ID=HCS.SITE_USE_ID

AND HCS.CUST_ACCT_SITE_ID=HCAS.CUST_ACCT_SITE_ID

------------------------------------------------

AND RCTA.SHIP_TO_SITE_USE_ID=HCS.SITE_USE_ID

AND HCS.CUST_ACCT_SITE_ID=HCAS.CUST_ACCT_SITE_ID

AND HCAS.PARTY_SITE_ID=HPS.PARTY_SITE_ID

AND HPS.LOCATION_ID=HL.LOCATION_ID

---------------------------------

AND RCTA.BILL_TO_CUSTOMER_ID=HCA2.CUST_ACCOUNT_ID

AND HCA2.PARTY_ID=HP2.PARTY_ID

AND RCTA.BILL_TO_SITE_USE_ID=HCS1.SITE_USE_ID

AND HCS1.CUST_ACCT_SITE_ID=HCAS1.CUST_ACCT_SITE_ID

----------------------------------------------

AND RCTA.BILL_TO_SITE_USE_ID=HCS1.SITE_USE_ID

AND HCS1.CUST_ACCT_SITE_ID=HCAS1.CUST_ACCT_SITE_ID

AND HCAS1.PARTY_SITE_ID=HPS1.PARTY_SITE_ID

AND HPS1.LOCATION_ID=HL1.LOCATION_ID

----------------------------------------

AND RCTA.REMIT_TO_ADDRESS_ID=HCS2.SITE_USE_ID

AND HCS2.CUST_ACCT_SITE_ID=HCAS2.CUST_ACCT_SITE_ID

AND HCAS2.PARTY_SITE_ID=HPS2.PARTY_SITE_ID

AND HPS2.LOCATION_ID=HL2.LOCATION_ID

-----------------------------------------

AND RCTL.CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID

--------------------------------------

AND RCTL.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID(+)

AND RCTL.ORG_ID=MSIB.ORGANIZATION_ID(+)