Good Morning!
I have a note that came out of the air and I have her data in the INV, in the GL and PAC, but I want to reach the line of the rec_invoices of this note. I have the transaction_id of the item that came out of the origin. How could it reach the note number and the other data in RI?
Thanks for the help.
GL connection X X PAC RI
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Good morning Rafael.
Face would be easier if you put your SELECT for us to take a look, but or try to help you get RI.
Well you can use these JOIN from the INV to reach the table REC_INVOICE_LINES
and also this join to reach the REC_ENTRY_OPERATIONS
Therefore would be + or - thus
I hope to help.
Face would be easier if you put your SELECT for us to take a look, but or try to help you get RI.
Well you can use these JOIN from the INV to reach the table REC_INVOICE_LINES
and also this join to reach the REC_ENTRY_OPERATIONS
MPA.ORGANIZATION_ID = REO.ORGANIZATION_ID
select colunas
from
FROM apps.REC_ENTRY_OPERATIONS REO
, apps.MTL_PARAMETERS_ALL_V MPA
, apps.REC_INVOICES RI
, apps.REC_INVOICE_LINES RIL
, apps.MTL_SYSTEM_ITEMS_B MSI
WHERE MPA.ORGANIZATION_ID = REO.ORGANIZATION_ID
AND REO.ORGANIZATION_ID = RI.ORGANIZATION_ID
AND REO.OPERATION_ID = RI.OPERATION_ID
AND REO.LOCATION_ID = RI.LOCATION_ID
AND RI.INVOICE_ID = RIL.INVOICE_ID
AND RIL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND RIL.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Douglas, I have an initial select that runs and brings me the transaction_id from mtl_material_transactions. From this, I squeeze the select below that brings me other information from GL, PAC and INV. From it I want to seek the notes or the ree note:
Thanks for the initial aid.
SELECT gh.PERIOD_NAME
,gcc.segment1 || '.'|| gcc.segment2 || '.'|| gcc.segment3 || '.'|| gcc.segment4 || '.'||
gcc.segment5 || '.'|| gcc.segment6 || '.'|| gcc.segment7 || '.'|| gcc.segment8 Conta
,ch.ORGANIZATION_ID
,Decode ( wdj2.WIP_ENTITY_NAME, NULL, wdj.WIP_ENTITY_NAME, wdj2.WIP_ENTITY_NAME ) Nro_OP
,gl.ACCOUNTED_DR DR_GL
,gl.ACCOUNTED_CR CR_GL
,cl.ACCOUNTED_DR DR_PAC
,cl.ACCOUNTED_CR CR_PAC
,gh.NAME
,gh.DESCRIPTION DESC_A
,gl.DESCRIPTION DESC_B
,ch.DESCRIPTION DESC_C
,cl.DESCRIPTION DESC_D
,gh.EXTERNAL_REFERENCE
,gh.JE_CATEGORY
,gh.JE_SOURCE
,ch.aí_CATEGORY
,ch.ACCT_EVENT_SOURCE_TABLE
,mmt.TRANSACTION_TYPE_ID
,mtt.TRANSACTION_TYPE_NAME
,mmt.TRANSACTION_SOURCE_TYPE_ID
,mst.TRANSACTION_SOURCE_TYPE_NAME
,mmt.TRANSACTION_SOURCE_ID
,gh.JE_BATCH_ID
,GH.JE_HEADER_ID
,gl.JE_LINE_NUM
,gl.GL_SL_LINK_ID
,cl.aí_header_id
,cl.aí_line_id
-- ,cl.source_id
-- ,mmt.TRANSACTION_SOURCE_ID
,mmt.transaction_id
,cl.wip_entity_id
,gh.STATUS
,gh.actual_flag
,ch.COST_GROUP_ID
,ch.COST_TYPE_ID
-- ,ch.PERIOD_NAME
,ch.ACCOUNTING_DATE
,ch.ACCOUNTING_ERROR_CODE
,ch.GL_TRANSFER_FLAG
,ch.GL_REVERSAL_FLAG
-- ,wdj.WIP_ENTITY_NAME
,wdj.STATUS_TYPE_DISP
,wdj.creation_date
,wdj.DATE_COMPLETED
,wdj.DATE_CLOSED
-- ,wdj2.WIP_ENTITY_NAME
-- ,wdj2.STATUS_TYPE_DISP
-- ,wdj2.creation_date
-- ,wdj2.DATE_COMPLETED
,wdj2.DATE_CLOSED Data_Fech
--,cl.*
FROM apps.cst_aí_headers ch
,apps.CST_aí_LINES cl
,apps.gl_code_combinations gcc
,apps.MTL_MATERIAL_TRANSACTIONS mmt
,apps.wip_discrete_jobs_v wdj
,apps.wip_discrete_jobs_v wdj2
,apps.gl_je_headers gh
,apps.gl_je_lines gl
-- ,apps.po_headers_all pha
,apps.MTL_TXN_SOURCE_TYPES mst
,apps.MTL_TRANSACTION_TYPES mtt
WHERE
---ch.organization_id = 99
---and ch.aí_HEADER_ID = 20705694
---and ch.creation_date > '01-AUG-2009'
---and ch.ACCOUNTING_DATE >= '01-AUG-2009'
---and ch.ACCOUNTING_DATE < '01-SEP-2009'
--- ch.aí_HEADER_ID between 20700000 and 20709000 ---IN (20705694, 20705732)
---
wdj.organization_id (+) = mmt.organization_id
and wdj.WIP_ENTITY_ID (+) = mmt.TRANSACTION_SOURCE_ID
and mtt.TRANSACTION_TYPE_ID (+) = mmt.TRANSACTION_TYPE_ID
and mst.TRANSACTION_SOURCE_TYPE_ID (+) = mmt.TRANSACTION_SOURCE_TYPE_ID
AND mmt.TRANSACTION_ID (+) = cl.source_id
AND wdj2.organization_id (+) = cl.reference2 ----ch.organization_id
AND wdj2.WIP_ENTITY_ID (+) = cl.wip_entity_id
and ch.aí_HEADER_ID = cl.aí_HEADER_ID
-- and ch.aí_header_id (+) = pha.PO_HEADER_ID
and cl.code_combination_id (+) = gl.code_combination_id
and cl.GL_SL_LINK_ID (+) = gl.GL_SL_LINK_ID
AND gh.actual_flag = 'A'
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = gcc.code_combination_id
and mst.TRANSACTION_SOURCE_TYPE_NAME = 'Internal order'
-- AND gcc.segment2 = '2008'
AND gcc.segment3 = '105311'
and mmt.TRANSACTION_ID = 560167968;
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Rafael with item_id of the item You can seek the note number and the receipt number.
I made a simple example and only modify the SELECT for your need:
99]]
Anything posts aí
ABS.
I made a simple example and only modify the SELECT for your need:
99]]
Anything posts aí
ABS.
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
This select helped me, I adapted it, but I did not want to use date. I would include the date on it with the item ID and brought the RI note number. Do you know that there for GL_JE_LINES, in the REFERENCE_1 field, Aquava to the OPERATION_ID of REC_INVOOGES?
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Douglas, I included MTL_Material_TransActions in this select doing join with the MTL_SYSTEM_ITEMS_B inventory_item_id. Then filtered by the rec_invoices organization_id, the transaction_id of MTL_Material_Transaction and Inventory_Item_ID. He returned only a line, that is, an invoice that appears on REC_INVOOGES. I just did not validate the source note to check if it's really.
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
In that last SELECT has date. I did not want to use a date, although it is parameter of my report for the user. I did the test without the date and it brings other notes.
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Rafael,
In my SELECT has DATA SIM REO.GL_DATE DATA GL that is on the input screen of fiscal operations in RI.
The paremetros and relevant you must discuss with the user what the best parameter for your report result.
ABS.
In my SELECT has DATA SIM REO.GL_DATE DATA GL that is on the input screen of fiscal operations in RI.
The paremetros and relevant you must discuss with the user what the best parameter for your report result.
ABS.
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Hi Douglas, see if you can give me a tip: I'm making a report of transfer notes between branches but that are in transit, that is, it left the origin branch and has not yet arrived in the inv or laughed at destiny. There in the air, to identify the transfer notes, would you only go by ra_customer_trx_all? Have you ever done something like that?
Thanks for help.
Rafael
Thanks for help.
Rafael
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Rafael,
Come on well I have not yet made a report of transfer notes between branches, so I know this type of transfer and made through an internal request, but it depends a lot on each company huh.
Well I will give a tip that custums to follow before making a report in EBS:
1st The most important of all Select You must have a main SELECT or at least one SELECT that brings most information the rest can be by another SELECT via cursor.
2nd User must make an Excel layout with columns and everything.
3º Define the parameterrings to filter the reproduction.
4º If possible the user must inform where the information in Oracle EBS are often they do not know and we should turn around.
well now this your report is half sinister all nff pass by ra_customer_trx_all depends on the status too, it is clearer who knows we can help you more.
Hugs.
Come on well I have not yet made a report of transfer notes between branches, so I know this type of transfer and made through an internal request, but it depends a lot on each company huh.
Well I will give a tip that custums to follow before making a report in EBS:
1st The most important of all Select You must have a main SELECT or at least one SELECT that brings most information the rest can be by another SELECT via cursor.
2nd User must make an Excel layout with columns and everything.
3º Define the parameterrings to filter the reproduction.
4º If possible the user must inform where the information in Oracle EBS are often they do not know and we should turn around.
well now this your report is half sinister all nff pass by ra_customer_trx_all depends on the status too, it is clearer who knows we can help you more.
Hugs.
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Douglas, you already did something like, from an internal request (goods transfer request) between affiliates, arrives at the origin branch, passes on the OM, through a request and arrives in the air to issue the note. At the destination affiliate, it is given in the RI of this note.
I'm trying to find this right internal requisition you can call or on or in the air. Have you ever done something like that or could you tell a table of this internal request?
Thanks for the help,
Rafael
I'm trying to find this right internal requisition you can call or on or in the air. Have you ever done something like that or could you tell a table of this internal request?
Thanks for the help,
Rafael
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Talks Rafael.
I do not believe the type but I found it interesting I will try to give the path of the stones to you mano.
We can use the following query to find all internal requisitions that were created in the select below:
There is a Very good site that explains the procedure of internal requisitions in the very good po as well.
http://www.virginia.edu/integratedsyste ... V5030U.htm
I hope to have help ..
When you get aí.
Falow.
I do not believe the type but I found it interesting I will try to give the path of the stones to you mano.
We can use the following query to find all internal requisitions that were created in the select below:
SELECT rqha.segment1 "Requisition Number"
, rqla.line_num "Line Number"
, rqla.requisition_header_id "Requisition Header ID"
, rqla.requisition_line_id "Requisition Line ID"
, rqla.item_id "Inventory item ID"
, rqla.unit_meas_lookup_code "Unit Of Measure"
, rqla.unit_price "Unit Price"
, rqla.quantity "Quantity"
, rqla.quantity_cancelled "Quantity Cancelled"
, rqla.quantity_delivered "Quantity Delivered"
, rqla.cancel_flag "Cancelled"
, rqla.source_type_code "Source Type"
, rqla.source_organization_id "Source Organization ID"
, rqla.destination_organization_id "Destination Organization ID"
, rqha.transferred_to_oe_flag "Transferred to OE Flag"
FROM po_requisition_lines_all rqla
, po_requisition_headers_all rqha
WHERE rqla.requisition_header_id = rqha.requisition_header_id
AND rqla.source_type_code = 'INVENTORY'
AND rqla.source_organization_id IS NOT NULL
AND NOT EXISTS ( SELECT 'existing internal order'
FROM oe_order_lines_all line
WHERE line.source_document_line_id =
rqla.requisition_line_id
AND line.source_document_type_id = 10)
ORDER BY rqha.requisition_header_id
, rqla.line_num
http://www.virginia.edu/integratedsyste ... V5030U.htm
I hope to have help ..
When you get aí.
Falow.
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Douglas, okay, your previous tips were interesting! My report is almost completed, but now I walked in a bark at the end of it.
There are two fields at the end of my layout that says: input in transit and output in transit, that is, when listing the note line, I have to show the value in the PAC. Have you ever linked a ri note to the PAC? In addition to the OPERATION_ID of the REC_INVOOKS CONNECT IN GL_JE_LINES REFERENCE_1, there is any more connection (if you have already worked in this part).
Thanks for help.
Rafael
There are two fields at the end of my layout that says: input in transit and output in transit, that is, when listing the note line, I have to show the value in the PAC. Have you ever linked a ri note to the PAC? In addition to the OPERATION_ID of the REC_INVOOKS CONNECT IN GL_JE_LINES REFERENCE_1, there is any more connection (if you have already worked in this part).
Thanks for help.
Rafael
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Hi Rafael.
I'vê never worked with Pac - Average Cost package, but I do not think there's no direct relationship with RI not friend.
I will give you another very important tip and that I always use.
with the password of the apps we can go in the screen examine and you choose in the block = System and field = Last Query.
With this we managed to see the last Select run from the EBS screen comes relationship and everything, it helps enough when developing new reports.
Another tip look for a Core Report on PAC in competing programs and check the SELECT helps you because there are many core reports on the system.
When finishing your report sends to us OK.
ABS.
I'vê never worked with Pac - Average Cost package, but I do not think there's no direct relationship with RI not friend.
I will give you another very important tip and that I always use.
with the password of the apps we can go in the screen examine and you choose in the block = System and field = Last Query.
With this we managed to see the last Select run from the EBS screen comes relationship and everything, it helps enough when developing new reports.
Another tip look for a Core Report on PAC in competing programs and check the SELECT helps you because there are many core reports on the system.
When finishing your report sends to us OK.
ABS.
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Hi Douglas, all right expensive ....
About that report I did almost all, but there are two columns at the end of it, on the balance of the CAP that neither the business analyst can explain. I asked him to talk to the user to really know what he seeks to know with the Balance of the PAC in this report. Remember, you gave me some tips from RI and INV in this report of goods in traffic. It leaves a source branch and is transferred to a destination branch. The idea of ??the report is listing the goods that have not yet reached the destination.
Douglas, do you know Pac? or involved
Has msn face? We can exchange ideas about EBS. My msn is rafaelsb21@hotmail.com
embrace,
Rafael
About that report I did almost all, but there are two columns at the end of it, on the balance of the CAP that neither the business analyst can explain. I asked him to talk to the user to really know what he seeks to know with the Balance of the PAC in this report. Remember, you gave me some tips from RI and INV in this report of goods in traffic. It leaves a source branch and is transferred to a destination branch. The idea of ??the report is listing the goods that have not yet reached the destination.
Douglas, do you know Pac? or involved
Has msn face? We can exchange ideas about EBS. My msn is rafaelsb21@hotmail.com
embrace,
Rafael
-
- Rank: Estagiário Sênior
- Posts: 10
- Joined: Sun, 01 Nov 2009 11:28 pm
- Location: RS
Do you have any Select that calls the inv with the PAC? When the amount of the quantity in the INV occurs, in the PAC a release of the value should occur.
Do you have any inverts manuals?
Valeu,
Rafael
Do you have any inverts manuals?
Valeu,
Rafael
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Send me an e-mail at Douglasmad3@hotmail.com I'll send you a manual.
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest