How to join the ideation management with Items table

User_1EN5M

Hi everyone,

I have a customer requirement to create a report that includes the following details:

Idea Number / Relationship
Idea Requestor
Item details

I’m trying to determine the correct joint key between the ACN_IDEA_VL table for the ideation details and the EGP_SYSTEM_ITEMS_B_V table for the item details.

Could anyone guide me on how to link these two tables?

Thanks

Sumanth V -Oracle

@User_1EN5M - Based on the table details provided, it appears that you are using Fusion SCM. Kindly confirm. Thanks.

User_1EN5M

Yes I'm using Fusion SCM

Gianni Ceresa

Hi,

Are you using FDI? If yes, which one between ERP, SCM, HCM, CX? Just to know where to move the question.

If not, your question being about data of a Fusion application (I guess), you should ask in the forum looking after that application. Publisher is the technical tool where you want to write your query, but doesn't own the source data you have a question about. The forum covering the Fusion application is at https://community.oracle.com/customerconnect .

edit: as you said (while typing this one) that it is SCM: if you are using FDI SCM say it, otherwise look in the CCC forum for the many SCM categories and ask there your data question.

User_1EN5M

Hi Gianni,

I'm using FDI SCM module.

Gianni Ceresa

Moved to the FDI SCM category

Jayant Ajayrao Deshmukh

To join the ACN_IDEA_VL table (for ideation) and EGP_SYSTEM_ITEMS_B_V (for item details), you need an intermediary table that links ideas and items, often based on IDEA_ID and INVENTORY_ITEM_ID. Check for relationship tables such as ACN_IDEA_RELATIONSHIPS or similar.

A potential query could look like this:

sqlCopy codeSELECT a.idea_number, a.idea_requestor, b.segment1 as item_number, b.descriptionFROM ACN_IDEA_VL aJOIN RELATIONSHIP_TABLE r ON a.idea_id = r.idea_idJOIN EGP_SYSTEM_ITEMS_B_V b ON r.item_id = b.inventory_item_id;

Jayant Ajayrao Deshmukh

To join the ACN_IDEA_VL (ideation management) and EGP_SYSTEM_ITEMS_B_V (items), use a common key like ITEM_ID or an intermediate table that links ideas to items. Typically, you would look for a field in ACN_IDEA_VL that relates to item details, like PRODUCT_ID or ITEM_ID, and join it to inventory_item_id in EGP_SYSTEM_ITEMS_B_V.

Example join:

sqlCopy codeSELECT a.idea_number, a.idea_requestor, e.item_name FROM ACN_IDEA_VL aJOIN EGP_SYSTEM_ITEMS_B_V e ON a.item_id = e.inventory_item_id;