Wednesday, 18 April 2012

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(+)

No comments:

Post a Comment