Link GL X PAC X RI

Perguntas relacionadas a questões técnicas do Oracle EBS. Criação de Concorrentes, Value Sets, Alerts, Forms Personalizations, Configurações, etc
  

Poston Sun, 01 Nov 2009 11:44 pm

Good morning!

I have a note that went off the air and have her data in INV, GL and PAC, but I want to get in line with the REC_INVOICES of this note. I have the Transaction_id from item out of source. How could I get the invoice number and other data in RI?

Thanks for the help.
rafaelsb21
Location: RS

Poston Tue, 03 Nov 2009 7:28 am

Hello Rafael.

Man would be easier if you put your select for us we take a look, or try to help you get the laughs.

Well you can use these join the INV to arrive in REC_INVOICE_LINES table
Code: Select all
  AND RIL.ITEM_ID                  = MSI.INVENTORY_ITEM_ID(+)
   AND RIL.ORGANIZATION_ID          = MSI.ORGANIZATION_ID(+)
and also that join to get to REC_ENTRY_OPERATIONS
Code: Select all
MPA.ORGANIZATION_ID          = REO.ORGANIZATION_ID
so it would be + or-so
Code: 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(+)
hope to have help.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Tue, 03 Nov 2009 7:38 am

Douglas, I have a select wheel and brings me to initial the transaction_id from mtl_material_transactions. From this, select Rhoda below that brings other GL info, PAC and Inv. From it I get the notes or the RI Note:
Code: 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.AE_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.ae_header_id
                   ,cl.ae_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_ae_headers              ch
                ,apps.CST_AE_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.AE_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.AE_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.AE_HEADER_ID                     = cl.AE_HEADER_ID
            --  and ch.ae_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 help.
rafaelsb21
Location: RS

Poston Tue, 03 Nov 2009 10:04 am

Rafael with item_id Item you can get the number of NOTE and receipt number.

I made a simple example and just modify the select for your need: [
Code: Select all
SELECT DISTINCT REO.OPERATION_ID "NUMERO DO RI",
                RI.INVOICE_NUM   "NUMERO DA NOTA"
  FROM apps.REC_ENTRY_OPERATIONS REO,
       apps.REC_INVOICES         RI,
       apps.REC_INVOICE_LINES    RIL,
       apps.MTL_SYSTEM_ITEMS_B   MSI
WHERE 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(+)
   AND TRUNC(REO.GL_DATE) between TO_DATE('01-MAR-2009', 'DD-MON-RRRR') and
       TO_DATE('31-MAR-2009', 'DD-MON-RRRR')
   AND REO.REVERSION_FLAG is null
   AND MSI.INVENTORY_ITEM_ID = 22143
order by 1
anything put aí Abs.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Tue, 03 Nov 2009 10:13 am

Select yours helped me, I adapted it, but didn't want to use date. I included the date in it with the item id and brought the number of the note. You know there in the GL_JE_LINES, in the reference_1 field, aquivale to the rec_invoices operation_id?
rafaelsb21
Location: RS

Poston Tue, 03 Nov 2009 5:34 pm

Douglas, I include the MTL_MATERIAL_TRANSACTIONS in your select doing join with inventory_item_id of MTL_SYSTEM_ITEMS_B by the organization_id filtered out so REC_INVOICES, transaction_id from MTL_MATERIAL_TRANSACTION and inventory_item_id. I returned only one row, i.e. an invoice on REC_INVOICES. Not only validated with the note of origin to verify that it's really her.
rafaelsb21
Location: RS

Poston Wed, 04 Nov 2009 8:36 am

That your last select has a date. I didn't want to use a date, although my report parameter for the user. I did the test without the date and he brings other notes.
rafaelsb21
Location: RS

Poston Wed, 04 Nov 2009 10:05 am

Rafael, in my select has REO.GL _ sim date DATE Date GL in FISCAL OPERATIONS entry screen in RI.

The Paremetros and relevant you should discuss with the user the best parameter for the outcome of the report.

Abs.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Mon, 09 Nov 2009 3:27 pm

Hi Douglas, see if you can give me a hint: I am doing a report on transfer notes between affiliates but that are in transit, i.e. left source branch and has not yet arrived in the INV or LAUGH at the destination. There in the air, to identify the transfer notes, you would only by RA_CUSTOMER_TRX_ALL? You'vê done something?

Thanks for the help.
Rafael
rafaelsb21
Location: RS

Poston Mon, 09 Nov 2009 8:28 pm

Rafael, come on well I haven't done a report of transfer notes between branches, as far as I know this kind of dump and is made through an internal requisition, but relies heavily on each company.

Well I'll give a hint that usually follow before making a report at the EBS: 1st most important of all the Select main você must have a main or select at least one select to get most of the information the rest can be by another select via cursor.

2 the user must do a layout in excel with columns and all.

3 set the parametrros to filter the report.

4th if possible the user shall inform the information in Oracle EBS often they don't know aí we should turn.

Well now that your report this kind of sinister all NFF undergo RA_CUSTOMER_TRX_ALL depends on Status, also be clearer who you know can help you more.


Hugs.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Sun, 15 Nov 2009 2:04 am

Douglas, you'vê done something, from an internal request (request for transfer of goods) between branches, arrives in the source branch, passes on OM, through an order and arrives in the AIR for the issuance of the note. In the target branch, is given in the input RI of this note.

I'm trying to find that inner request correct that can call or OM or in the air. I did something or tell a table of this internal requisition?

Thanks for your help man, Rafael
rafaelsb21
Location: RS

Poston Sun, 22 Nov 2009 2:13 pm

Rafael Speaks.

I never type didsomething but found interesting I will try to give the path of stones for you BRO.

We can use the following query to find all internal requests that were created in the select below:
Code: 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 PO very good too.

http://www.virginia.edu/integratedsyste ... V5030U.htm hope this helps ...

When you put aí.


Falow.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Mon, 30 Nov 2009 9:35 am

Douglas, OK, your previous tips were very interesting! My report is nearly complete, however now I walked into a bark at the end.
There are two fields at the end of my layout that says: entry into traffic and exit in transit, i.e. the list of line note, I have to show the value in the PAC. You already linked a note from RI to PAC? In addition to the call rec_invoices in reference_1 operation_id from gl_je_lines, have any connection (if you'vê ever worked on this part).

Thanks for the help.

Rafael
rafaelsb21
Location: RS

Poston Tue, 01 Dec 2009 9:04 am

Hello Rafael.

I'vê never worked with PAC-Package Average Cost, but I don't think there's a direct relationship with the RI no friend.

I'll give you one more very important tip and that I always use.

With the password of the apps we can go in the examine of the screen and you choose on the block = system and field = last query.

With this we can see the last select run EBS screen comes relationship and all, very helpful in time to develop new reports.

Another tip for a report on core PAC in Competing Programs and check the select helps you, because there are many reports in the system core.

When you have finished your report sends for us OK.

Abs.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Sat, 20 Mar 2010 11:43 pm

Hi Douglas, OK man. ... about that report I made almost whole, but has two columns at the end of it, on balance the PAC that neither the business analyst can explain. I asked him to talk to the user to really know what he seeks to know the balance of PAC in this report. Remember, you gave me some pointers and INV RI in this report of goods in transit. He leaves a source branch and is transferred to a destination branch. The idea of the report is to list the goods haven't arrived yet at the destination.
Douglas, you know PAC? or INV?

Got msn? We can exchange ideas about EBS. My msn is rafaelsb21@hotmail.com hug, Rafael
rafaelsb21
Location: RS

Poston Sun, 21 Mar 2010 10:05 pm

Hello Rafael I know well the INV which his doubts? explain better what I or someone else to help you right here on the forum.

Abs.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Sun, 21 Mar 2010 10:28 pm

You have some select that connect the INV with the CAP? When it happens the lower amount in INV, the CAP should be a release of value.

Is there a manual of INV or PAC?

Thanks, Rafael
rafaelsb21
Location: RS

Poston Tue, 23 Mar 2010 4:06 pm

Send Me an email at douglasmad3@hotmail.com I will send you a manual.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.



Return to EBS Technical

Who is online

Users browsing this forum: No registered users and 0 guests