Format of Excel Sheet
619658Jan 25 2008 — edited Jan 25 2008Hi experts,
I am working on Account receivables in oracle applications.I am getting one problem . I run my query in Toad and I am saving output in .xls format.i got output in comma separted.like
cust_no|Cust_name|trx_no|trx_date|amount|open_bal|closed_bal|curr_bal
1 xxx 110 12/01/06 10000 50000 60000 10000
But i want output like this
Cust_no:1
Cust_name:xxx
open_bal:50000
Trx_no Trx_date Amount Curr_bal
110 12/01/06 10000 10000
closed_bal:60000.
I want output like this in excel sheet.
how to do This?Please give me solution.
I want this output by using query only to display in excel sheet.Dont use rdf and anything.
My code is:
SELECT
HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
,RCT.TRX_NUMBER TRX_NUMBER
,HP.PARTY_NAME PARTY_NAME
,DECODE(RCT.STATUS_TRX,'OP','OPEN','CLOSED') STATUS
,HL.ADDRESS1 || HL.ADDRESS2 || HL.ADDRESS3|| HL.PROVINCE || HL.CITY || HL.STATE ||HL.POSTAL_CODE || HL.COUNTRY ADDRESS
,HCSU.LOCATION LOCATION
,RCT.TRX_DATE TRX_DATE
,APS.DUE_DATE DUE_DATE
,RCTG.GL_DATE GL_DATE
,APS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
,APS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
,APS.AMOUNT_APPLIED AMOUNT_APPLIED
,DECODE(RCTT.TYPE,'DM','DEBIT MEMO','CM','CREDIT MEMO','DEP','DEPOSIT','GUAR','GURANTEE','INVOICE') TYPE
,RCTT.STATUS STATUS1
,RBS.NAME NAME
,RCT.CT_REFERENCE CT_REFERENCE
,F1(HCA.ACCOUNT_NUMBER,'01-OCT-2007') OPENING_BALANCE
,F2(HCA.ACCOUNT_NUMBER,RCT.TRX_NUMBER,'01-OCT-2007','04-NOV-2007') CURRENT_BAL
,F1(HCA.ACCOUNT_NUMBER,'01-OCT-2007') + F2(HCA.ACCOUNT_NUMBER,RCT.TRX_NUMBER,'01-OCT-2007','04-NOV-2007') CLOSED_BAL
,GCC.SEGMENT1 BC
FROM
RA_CUSTOMER_TRX_ALL RCT
,AR_PAYMENT_SCHEDULES_ALL APS
,RA_CUST_TRX_TYPES_ALL RCTT
,RA_CUST_TRX_LINE_GL_DIST_ALL RCTG
,RA_BATCH_SOURCES_ALL RBS
,HZ_PARTY_SITES HPS
,HZ_PARTIES HP
,HZ_CUST_ACCOUNTS HCA
,HZ_CUST_SITE_USES_ALL HCSU
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_LOCATIONS HL
,GL_CODE_COMBINATIONS GCC
WHERE
RCT.CUSTOMER_TRX_ID=APS.CUSTOMER_TRX_ID
AND RCTT.CUST_TRX_TYPE_ID=RCT.CUST_TRX_TYPE_ID
AND RCTG.CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID
AND RCT.BATCH_SOURCE_ID=RBS.BATCH_SOURCE_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HP.PARTY_ID=HPS.PARTY_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HCA.CUST_ACCOUNT_ID =HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND RCT.BILL_TO_SITE_USE_ID = HCSU.SITE_USE_ID
AND GCC.CODE_COMBINATION_ID = RCTG.CODE_COMBINATION_ID
AND HCSU.SITE_USE_CODE = 'BILL_TO'
AND RCTG.GL_DATE BETWEEN '01-OCT-2007' AND '04-NOV-2007'
AND RCT.TRX_NUMBER = '1107010103'
GROUP BY
HCA.ACCOUNT_NUMBER
,RCT.TRX_NUMBER
,HP.PARTY_NAME
,DECODE(RCT.STATUS_TRX,'OP','OPEN','CLOSED')
,HL.ADDRESS1 || HL.ADDRESS2 || HL.ADDRESS3|| HL.PROVINCE || HL.CITY || HL.STATE ||HL.POSTAL_CODE || HL.COUNTRY
,HCSU.LOCATION
,RCT.TRX_DATE
,APS.DUE_DATE
,RCTG.GL_DATE
,APS.AMOUNT_DUE_ORIGINAL
,APS.AMOUNT_DUE_REMAINING
,APS.AMOUNT_APPLIED
,DECODE(RCTT.TYPE,'DM','DEBIT MEMO','CM','CREDIT MEMO','DEP','DEPOSIT','GUAR','GURANTEE','INVOICE')
,RCTT.STATUS
,RBS.NAME
,RCT.CT_REFERENCE
,GCC.SEGMENT1