Oracle Fusion: GL Child and Parent Account Query

SELECT

        FTN.PK1_START_VALUE  CHILD_ACCOUNT ,

        FTN.PARENT_PK1_VALUE PARENT_ACCOUNT,

        FVV.DESCRIPTION      PARENT_ACCOUNT_DESCRIPTION

FROM

        FND_TREE_NODE      FTN,

        FND_FLEX_VALUES_VL FVV

WHERE

        FTN.TREE_STRUCTURE_CODE ='GL_ACCT_FLEX'

AND     FTN.TREE_CODE           ='GDI_ACC_HIE'  -- Please Enter Your Organization Account Hierarchy Code

AND     FTN.PK1_START_VALUE     =GCC.SEGMENT4 --Child Account '511109'

AND     FTN.PARENT_PK1_VALUE    =FVV.FLEX_VALUE

AND     FTN.TREE_VERSION_ID IN

        (

                SELECT

                        TREE_VERSION_ID

                FROM

                        FND_TREE_VERSION_VL

                WHERE

                        TREE_STRUCTURE_CODE ='GL_ACCT_FLEX'

                AND     TREE_CODE           ='GDI_ACC_HIE'

                AND     STATUS              ='ACTIVE'

                AND     TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND     EFFECTIVE_END_DATE )

Oracle Fusion: Payables - AP Invoice URL Deep link Query

Requirement: User need to open AP Invoice Transaction from report output so need to provide a AP Invoice Deep link in the report output.

Solution:

 SELECT ('https://test.oraclecloud.com/fscmUI/faces/deeplink?objType=AP_VIEWINVOICE&action=VIEW&objKey=InvoiceId='

          || invoice_id)                                              --- URL

            url

from AP_INVOICES_ALL

Oracle Fusion : Inventory Item Manufacturer Name & Manufacturer Part Number SQL Query

Important Note: It is possible that Inventory Item has multiple Manufacturer and Part Number, With respect to your business need you can fetch any No. of Manufacturer details from below query using rec_order value.

select 

(SELECT manufacturer_name

          FROM (  SELECT hp.party_name manufacturer_name,

                         a.tp_item_number manfr_part_num,

                         a.tp_item_desc description,

                         eirb.inventory_item_id,

                         eirb.creation_date,

                         ROWNUM rec_order

                    FROM egp_trading_partner_items a,

                         hz_parties hp,

                         egp_item_relationships_b eirb

                   WHERE     a.tp_type = 'MANUFACTURER'

                         AND hp.party_id = a.trading_partner_id

                         AND a.tp_item_id = eirb.tp_item_id

                         AND eirb.item_relationship_type = 'MFG_PART_NUM'

                         AND eirb.inventory_item_id = esib.inventory_item_id ---300000001940364

                ORDER BY eirb.creation_date ASC)

         WHERE rec_order = 1)

          MANUFACTURER_NAME,

       (SELECT manfr_part_num

          FROM (  SELECT hp.party_name manufacturer_name,

                         a.tp_item_number manfr_part_num,

                         a.tp_item_desc description,

                         eirb.inventory_item_id,

                         eirb.creation_date,

                         ROWNUM rec_order

                    FROM egp_trading_partner_items a,

                         hz_parties hp,

                         egp_item_relationships_b eirb

                   WHERE     a.tp_type = 'MANUFACTURER'

                         AND hp.party_id = a.trading_partner_id

                         AND a.tp_item_id = eirb.tp_item_id

                         AND eirb.item_relationship_type = 'MFG_PART_NUM'

                         AND eirb.inventory_item_id = esib.inventory_item_id ---300000001940364

                ORDER BY eirb.creation_date ASC)

         WHERE rec_order = 1)

          MANUFACTURER_PART_NUM

from FROM egp_system_items_b esib