Home » Applications » Oracle Fusion Apps & E-Business Suite » Journal lines R12(developer) (1)
Journal lines R12(developer) [message #396220] Sun, 05 April 2009 02:53 Go to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear all
I want to know the table which have the relation between the invoices in AP and the journals lines in GL.


-i make invoice on AP and create account final post .mean to go to the GL.
- i found the batch which created in GL
- when i do Line drilldawn and press view transaction the application show me the invoice .

so there is table in database have relation between invoices in AP and journals lines I want to know it

Alaa Fouad
Re: Journal lines R12(developer) [message #396223 is a reply to message #396220] Sun, 05 April 2009 04:21 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
gl_je_lines(je_header_id,je_line_num) -> gl_import_references(je_header_id,je_line_num)
gl_import_references(gl_sl_link_table,gl_sl_link_id) -> xla_ae_lines(gl_sl_link_table,gl_sl_link_id)
xla_ae_lines(applicaiton_id,ae_header_id) -> xla_ae_headers(application_id,ae_header_id)
xla_ae_headers(application_id,event_id) -> xla_events(application_id,event_id)
xla_events(application_id,entity_id) -> xla.xla_transaction_entities(application_id,entity_id)
xla.xla_transaction_entities.source_id_int_1 is equavalent to invoice_id or check_id. it depends on the entity_code.

Hope this helps.

By
Vamsi
Re: Journal lines R12(developer) [message #396243 is a reply to message #396223] Sun, 05 April 2009 11:53 Go to previous messageGo to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear friend

I want really to Thank you about your service

Alaa Fouad
Re: Journal lines R12(developer) [message #403447 is a reply to message #396220] Fri, 15 May 2009 12:59 Go to previous messageGo to next message
urwethe
Messages: 11
Registered: November 2008
Location: New Jersey
Junior Member
Hi,

I have the same problem. I am unable to find xla.xla_transaction_entities table. Is this a normal table? Could it be named something else?

Thanks in advance!

Bob Smile
Re: Journal lines R12(developer) [message #403449 is a reply to message #403447] Fri, 15 May 2009 13:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
I have the same problem.
What is it?
Are you on R12?
If so, I bet you can find xla.xla_transaction_entities.
Anyway, which user you have logged in? apps?

By
Vamsi
Re: Journal lines R12(developer) [message #403462 is a reply to message #403449] Fri, 15 May 2009 13:53 Go to previous messageGo to next message
urwethe
Messages: 11
Registered: November 2008
Location: New Jersey
Junior Member
Hi,

I am an idiot, just call me Forrest Gump!!!

I was looking for _entries NOT _entities !!

Have a good weekend and thanks for the quick reply!

Embarassed
Re: Journal lines R12(developer) [message #428613 is a reply to message #396220] Thu, 29 October 2009 02:40 Go to previous messageGo to next message
skjha
Messages: 3
Registered: October 2009
Junior Member
Hi
i make invoice on AP and create account final post.
It has populated every tables except xla_transaction_entities.
Re: Journal lines R12(developer) [message #428619 is a reply to message #428613] Thu, 29 October 2009 03:05 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Please read my previous update once again.

Further questions, first read the Forum Guide.

By
Vamsi

[Updated on: Thu, 29 October 2009 04:00]

Report message to a moderator

Re: Journal lines R12(developer) [message #428651 is a reply to message #428619] Thu, 29 October 2009 04:58 Go to previous messageGo to next message
skjha
Messages: 3
Registered: October 2009
Junior Member
After create accounting and final posting, once I execute this statement

select * from xla_transaction_entities
where CREATION_DATE>sysdate-1

No rows returned.


Shishir

Re: Journal lines R12(developer) [message #428652 is a reply to message #428619] Thu, 29 October 2009 05:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
vamsi kasina wrote on Thu, 29 October 2009 13:35
Please read my previous update once again.

Further questions, first read the Forum Guide.

By
Vamsi

Especially the point "Is count(*) giving 0 rows? (Or) Isn't SELECT giving desired output?" in Forum Guide.
If you don't care to read the updates fully, why should I pin point the answer?

By
Vamsi

[Updated on: Thu, 29 October 2009 05:02]

Report message to a moderator

Re: Journal lines R12(developer) [message #428654 is a reply to message #428651] Thu, 29 October 2009 05:20 Go to previous messageGo to next message
skjha
Messages: 3
Registered: October 2009
Junior Member
Thx Buddy,It is working now.

Shishir
icon7.gif  Re: Journal lines R12(developer) [message #437378 is a reply to message #428619] Mon, 04 January 2010 02:54 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
vamsi kasina wrote on Thu, 29 October 2009 11:05
Please read my previous update once again.

Further questions, first read the Forum Guide.

By
Vamsi

Hello Vamsi
We just implemented R12 and would appreciate incase if you could point me towards a query which would generate the following:
When our accountants click "View Transaction" button, corresponding entries are opened in the form and for the auditing purposes I am given a task to develop a custom report which will list all the transactions with details. Though I had gone through the tables relations you have provided earlier with the same post, not yet managed to write a proper SQL.

If you could please write a SQL and post it over here, as a beginner it would be a great help for me.

Thanks and regards,

raj
Re: Journal lines R12(developer) [message #438411 is a reply to message #437378] Fri, 08 January 2010 14:13 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
If you could please write a SQL and post it over here, as a beginner it would be a great help for me.
Write the code first and ask for help.

Do I need to search for you?
I thought that would be easy. Two hits in OTN.
One.
Two.

By
Vamsi
Re: Journal lines R12(developer) [message #438432 is a reply to message #438411] Fri, 08 January 2010 23:30 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
vamsi kasina wrote on Fri, 08 January 2010 23:13
Quote:
If you could please write a SQL and post it over here, as a beginner it would be a great help for me.
Write the code first and ask for help.

Do I need to search for you?
I thought that would be easy. Two hits in OTN.
One.
Two.

By
Vamsi

Hello Vamsi
I did write the code following the linking information I obtained from your previous posts. However after the third level I couldn't link anything, hence requested you to provide a sample code.
Thank you very much for the links, as a person with less than few months exposure to Oracle apps, at the same time building pressure to provide in-house solutions, certain times I am forced to look for ready made solutions.

Thank you very much once again Vamsi.

Regards
Re: Journal lines R12(developer) [message #438445 is a reply to message #438411] Sat, 09 January 2010 02:52 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
vamsi kasina wrote on Fri, 08 January 2010 23:13
Quote:
If you could please write a SQL and post it over here, as a beginner it would be a great help for me.
Write the code first and ask for help.

Do I need to search for you?
I thought that would be easy. Two hits in OTN.
One.
Two.

By
Vamsi

Hello Vamsi
A final question on this regard. I ran the script from OTN and it is fetching all relevant information. However please tell me which column in particular refers to a Sales Order or how I could hook up the query with OE_ORDER_HEADERS_ALL table.

Thank you in advance
Re: Journal lines R12(developer) [message #438448 is a reply to message #438445] Sat, 09 January 2010 03:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
I did write the code following the linking information I obtained from your previous posts. However after the third level I couldn't link anything, hence requested you to provide a sample code.
Post the code and point out where exactly you are stuck with respect you code.
Instead of explaining in text, better to post the code.

By
Vamsi
PS: Please don't use quote for all the replies, use it when needed and put the necessary portion in the quote. Use the Reply button instead.
Re: Journal lines R12(developer) [message #438451 is a reply to message #438448] Sat, 09 January 2010 03:50 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
Hello Vamsi
Following is the code I altered from my end. I needed to find out the corresponding Sale Order Numbers and Type of Sales along with the AR to GL drill down.
select 
b.name batch_name 
, b.description batch_description 
, b.running_total_accounted_dr batch_total_dr 
, b.running_total_accounted_cr batch_total_cr 
, b.status batch_status 
, b.default_effective_date effective_date 
, b.default_period_name batch_period_name 
, b.creation_date 
, u.user_name batch_created_by 
, h.je_category 
, h.je_source 
, h.period_name je_period_name 
, h.name journal_name 
, h.status journal_status 
, h.creation_date je_created_date 
, u1.user_name je_created_by 
, h.description je_description 
, h.running_total_accounted_dr je_total_dr 
, h.running_total_accounted_cr je_total_cr 
, l.je_line_num line_number 
, l.ledger_id 
, glcc.concatenated_segments Account 
, l.entered_dr 
, l.entered_cr 
, l.accounted_dr 
, l.accounted_cr 
, xlal.unrounded_accounted_dr XLA_unrounded_accounted_dr
, xlal.unrounded_accounted_cr XLA_unrounded_accounted_cr
, l.description 
, xlal.code_combination_id 
, xlal.accounting_class_code
, xlal.accounted_dr xlal_accounted_dr
, xlal.accounted_cr xlal_accounted_cr
, xlal.description xlal_description
, xlal.accounting_date xlal_accounting_date
, xlate.entity_code xlate_entity_code
, xlate.source_id_int_1 xlate_source_id_int_1
, xlate.source_id_int_2 xlate_source_id_int_2
, xlate.source_id_int_3 xlate_source_id_int_3
, xlate.security_id_int_1 xlate_security_id_int_1
, xlate.security_id_int_2 xlate_security_id_int_2
, xlate.transaction_number xlate_transaction_number
, xlae.event_id
, rcta.ct_reference
, rcta.interface_header_attribute2
from 
gl_je_batches b 
, gl_je_headers h 
, gl_je_lines l 
, fnd_user u 
, fnd_user u1 
, gl_code_combinations_kfv glcc 
, gl_import_references gir 
, xla_ae_lines xlal
, xla_ae_headers xlah
, xla_events xlae
, xla.xla_transaction_entities xlate
-- , rcv_transactions rcvt
,ra_customer_trx_all rcta
where 
b.created_by = u.user_id 
and h.created_by = u1.user_id 
and b.je_batch_id = h.je_batch_id 
and h.je_header_id = l.je_header_id 
and xlal.code_combination_id = glcc.code_combination_id 
and l.je_header_id = gir.je_header_id
and l.je_line_num = gir.je_line_num
and gir.gl_sl_link_table = xlal.gl_sl_link_table
and gir.gl_sl_link_id = xlal.gl_sl_link_id
and xlal.ae_header_id = xlah.ae_header_id
and xlah.event_id = xlae.event_id
and xlae.entity_id = xlate.entity_id
and xlae.application_id = xlate.application_id
and rcta.trx_number =  xlate.transaction_number
and xlate.security_id_int_1= rcta.org_id
and h.je_source = 'Receivables'
and h.period_name = '&period_name'
--and xlate.security_id_int_1 = '110'
order by rcta.ct_reference
--and rcta.application_id = '222'
--and xlal.description like 'Credit%'



Though the above code returns satisfactory results, I would like to know how far this code be expanded in a way that all the AP, AR, FA transaction numbers and related transaction names could be automated for the reporting purpose.
Ie, the task I am given is like following:
Parameters: Period Name (Dec-09)
Starting and Ending Account Numbers (start accout, end account) and whatever transactions posted to GL should appear with lines showing corresponding transaction references (ie, if the line belongs to AR, the sales order number and the sales type name and if it is AP the curresponding purchase order number etc)

Sorry about quoting the earlier posts.

[Updated on: Sat, 09 January 2010 04:48]

Report message to a moderator

Re: Journal lines R12(developer) [message #438468 is a reply to message #438451] Sat, 09 January 2010 06:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Why can't you use SLA standard reports?
Please crosscheck once whether Account Analysis Report / Journal Entries Report are useful in your case.

By
Vamsi
Re: Journal lines R12(developer) [message #438469 is a reply to message #438468] Sat, 09 January 2010 06:35 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
This is what Oracle also suggested. However you know how complex a standard report is.
I am pretty sure, within a short period of time we would develop few views and address this requirement.
Thank you very much Vamsi. My best regards and appreciate your time and efforts to answer the posts.

Thanks and regards,

raj
Re: Journal lines R12(developer) [message #475324 is a reply to message #396223] Tue, 14 September 2010 07:31 Go to previous messageGo to next message
thanigaimalai
Messages: 3
Registered: September 2010
Location: Chennai
Junior Member
gl_je_lines(je_header_id,je_line_num) -> gl_import_references(je_header_id,je_line_num)
gl_import_references(gl_sl_link_table,gl_sl_link_id) -> xla_ae_lines(gl_sl_link_table,gl_sl_link_id)
xla_ae_lines(applicaiton_id,ae_header_id) -> xla_ae_headers(application_id,ae_header_id)
xla_ae_headers(application_id,event_id) -> xla_events(application_id,event_id)
xla_events(application_id,entity_id) -> xla.xla_transaction_entities(application_id,entity_id)
xla.xla_transaction_entities.source_id_int_1 is equavalent to invoice_id or check_id. it depends on the entity_code.

Hi Vamsi...

please explain when xla.xla_transaction_entities.source_id_int_1 is equavalent to invoice_id ?? please suguest me the values of entity_code...
Re: Journal lines R12(developer) [message #475328 is a reply to message #475324] Tue, 14 September 2010 07:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Check this.
You will get the entity_code details and column details from the table xla_entity_id_mappings.

By
Vamsi
Re: Journal lines R12(developer) [message #475334 is a reply to message #475328] Tue, 14 September 2010 08:14 Go to previous messageGo to next message
thanigaimalai
Messages: 3
Registered: September 2010
Location: Chennai
Junior Member
1.gl_je_lines(je_header_id,je_line_num) -> gl_import_references(je_header_id,je_line_num)
2.gl_import_references(gl_sl_link_table,gl_sl_link_id) -> xla_ae_lines(gl_sl_link_table,gl_sl_link_id)
3.xla_ae_lines(applicaiton_id,ae_header_id) -> xla_ae_headers(application_id,ae_header_id)
4.xla_ae_headers(application_id,event_id) -> xla_events(application_id,event_id)
5.xla_events(application_id,entity_id) -> xla.xla_transaction_entities(application_id,entity_id)
6.xla.xla_transaction_entities.source_id_int_1 -> ap_check_all(check_id).
7.ap_check_all(check_id) + xla_events(event_id) ->
ap_invoices_payments_all (check_id,accounting_event_id).
8.ap_invoices_payments_all (invoice_id) -> ap_invoice_all (invoice_id) .

Will this works fine?? Am getting duplicate records gets fetched over the above linking...
Especially am getting duplication in line 7.

Please advise


[Updated on: Tue, 14 September 2010 08:28]

Report message to a moderator

Re: Journal lines R12(developer) [message #475390 is a reply to message #475334] Tue, 14 September 2010 15:01 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Did you read the following in the link?Quote:
xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id

For Ex:
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
I don't see the filters on application_id, entity_code and ledger_id while joining to ap_checks_all.

By
Vamsi
Re: Journal lines R12(developer) [message #475533 is a reply to message #475390] Wed, 15 September 2010 08:48 Go to previous messageGo to next message
thanigaimalai
Messages: 3
Registered: September 2010
Location: Chennai
Junior Member
I have multiple invocie in (AP_INVOICES_ALL) for a single payment in (AP_INVOICE_PAYMENTS_ALL). How to identify Invoice payments details for a single payment in a single check(AP_CHECK_ALL)??
Re: Journal lines R12(developer) [message #475570 is a reply to message #475533] Wed, 15 September 2010 15:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
I didn't get your issue. What is the issue here?
If the check has been paid by more than one invoices, then you will have more rows in ap_invoice_payments_all. That is expected.

By
Vamsi
icon5.gif  Re: Journal lines R12(developer) [message #524778 is a reply to message #475570] Mon, 26 September 2011 13:19 Go to previous messageGo to next message
venson
Messages: 7
Registered: April 2009
Location: India
Junior Member
Great thread. Great amount of info. However i couldnt get XLA.XLA_TRANSACTION_ENTITIES and RCV_TRANSACTIONS for Receipts created through Internal Requisition-->Internal Order route.

I mean TRANSACTION_ID from RCV_TRANSACTIONS of Receipts created through Internal Requisition-->Internal Order could not be found in SECURITY_ID_INT_1 of XLA.XLA_TRANSACTION_ENTITIES.!!

Any light on this would greatly help me.

Thanks,
Venson.
Supply Chain.
Re: Journal lines R12(developer) [message #524913 is a reply to message #524778] Tue, 27 September 2011 06:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
I mean TRANSACTION_ID from RCV_TRANSACTIONS of Receipts created through Internal Requisition-->Internal Order could not be found in SECURITY_ID_INT_1 of XLA.XLA_TRANSACTION_ENTITIES.!!
I doubt SECURITY_ID_INT_1 is a typo from your side.
I was talking about source_id_int_*
As I said in the link, you may need to refer xla_entity_id_mappings.

By
Vamsi
Re: Journal lines R12(developer) [message #560444 is a reply to message #524913] Thu, 12 July 2012 22:31 Go to previous messageGo to next message
kaka0070304
Messages: 1
Registered: July 2012
Location: 北京
Junior Member
Could be rcv_transactions related to xla_transaction_entities by rcv_transactions.transaction_id and xla_transaction_entities.SOURCE_ID_INT_1?
Re: Journal lines R12(developer) [message #560465 is a reply to message #560444] Fri, 13 July 2012 04:23 Go to previous message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
As the link above and the data in xla_entity_id_mappings the link for entity_code rcv_accounting_events is:
transcation_id -> source_id_int_1
accounting_evnet_id -> source_id_int_2
organization_id -> source_id_int_3

I think the entity_code and the table name are same as coincidence.
rcv_accounting_events.transaction_id could be same as rcv_transactions.transaction_id

By
Vamsi
Previous Topic: Create_update_trip API
Next Topic: Unable to cancel PO
Goto Forum:
  


Current Time: Thu Mar 28 06:34:01 CDT 2024