GL connection X X PAC RI

[rafaelsb21]

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.

[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

Select all

MPA.ORGANIZATION_ID          = REO.ORGANIZATION_ID

Therefore would be + or - thus

Select all

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

I hope to help.

[rafaelsb21]

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:

Select all

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;

Thanks for the initial aid.

[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.

[rafaelsb21]

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?

[rafaelsb21]

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.

[rafaelsb21]

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]

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.

[rafaelsb21]

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

[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.

[rafaelsb21]

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

[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:

Select all

 
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 

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.

[rafaelsb21]

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

[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.

[rafaelsb21]

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

[madmax]

Hello Rafael

I know the inv Which doubt? Explain better so I or someone else help you right here in the forum.

ABS.

[rafaelsb21]

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

[madmax]

Send me an e-mail at Douglasmad3@hotmail.com I'll send you a manual.