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  |
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  |
 |
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 #428652 is a reply to message #428619] |
Thu, 29 October 2009 05:00 |
|
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
vamsi kasina wrote on Thu, 29 October 2009 13:35Please 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 #438432 is a reply to message #438411] |
Fri, 08 January 2010 23:30 |
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
|
Junior Member |
|
|
vamsi kasina wrote on Fri, 08 January 2010 23:13Quote: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 |
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
|
Junior Member |
|
|
vamsi kasina wrote on Fri, 08 January 2010 23:13Quote: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 #438451 is a reply to message #438448] |
Sat, 09 January 2010 03:50 |
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 #438469 is a reply to message #438468] |
Sat, 09 January 2010 06:35 |
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 |
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 #475334 is a reply to message #475328] |
Tue, 14 September 2010 08:14 |
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 #475533 is a reply to message #475390] |
Wed, 15 September 2010 08:48 |
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 #560465 is a reply to message #560444] |
Fri, 13 July 2012 04:23 |
|
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
|
|
|
|
Goto Forum:
Current Time: Wed Dec 25 07:51:42 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
