Query to get Intercompany Transaction Details

SELECT FTB.BATCH_NUMBER,

            FTH.TRX_NUMBER,

            FIOP.INTERCO_ORG_NAME Provider,

            FTTL.TRX_TYPE_NAME,

            FTB.BATCH_DATE,

            FTB.GL_DATE "Accounting Date",

            FTB.DESCRIPTION,

            FTB.EXCHANGE_RATE_TYPE,

            PROVIDER_ACCOUNT.GL_STRING PROVIDER_ACCOUNT,

            PROVIDER_ACCOUNT.AMOUNT_DR PROVIDER_debit,

            PROVIDER_ACCOUNT.AMOUNT_CR PROVIDER_credit,

            PROVIDER_ACCOUNT.DESCRIPTION provider_description,

            FIO.INTERCO_ORG_NAME Receiver,

            XEP.name Receiver_LE,

            RECEIVER_ACCOUNT.GL_STRING receiver_account,

            RECEIVER_ACCOUNT.AMOUNT_DR receiver_debit,

            RECEIVER_ACCOUNT.AMOUNT_CR receiver_credit,

            RECEIVER_ACCOUNT.DESCRIPTION receiver_description,

            FTH.AR_INVOICE_NUMBER "Document ID"

FROM FUN_TRX_HEADERS FTH,

            FUN_INTERCO_ORGANIZATIONS FIO,

            XLE_ENTITY_PROFILES XEP,

            FUN_TRX_BATCHES FTB,

            FUN_INTERCO_ORGANIZATIONS FIOP,

            FUN_TRX_TYPES_TL FTTL,

            (

                        SELECT FDL.TRX_ID,

                                    FDL.DIST_NUMBER,

                                    FDL.AMOUNT_DR,

                                    FDL.AMOUNT_CR,

                                    FDL.DESCRIPTION,

                                    GCC.CODE_COMBINATION_ID,

                                    GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 || '-' || GCC.SEGMENT5 || '-' || GCC.SEGMENT6 || '-' || GCC.SEGMENT7 || '-' || GCC.SEGMENT8 GL_STRING

                        FROM FUN_DIST_LINES FDL,

                             GL_CODE_COMBINATIONS GCC

                        WHERE FDL.CCID = GCC.CODE_COMBINATION_ID

                                    AND FDL.PARTY_TYPE_FLAG = 'I'

                                    AND AMOUNT_CR IS NOT NULL

                        ) PROVIDER_ACCOUNT,

            (

                        SELECT FDL.TRX_ID,

                                    FDL.DIST_NUMBER,

                                    FDL.AMOUNT_DR,

                                    FDL.AMOUNT_CR,

                                    FDL.DESCRIPTION,

                                    GCC.CODE_COMBINATION_ID,

                                    GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 || '-' || GCC.SEGMENT5 || '-' || GCC.SEGMENT6 || '-' || GCC.SEGMENT7 || '-' || GCC.SEGMENT8 GL_STRING

                        FROM FUN_DIST_LINES FDL,

                             GL_CODE_COMBINATIONS GCC

                        WHERE FDL.CCID = GCC.CODE_COMBINATION_ID

                                    AND FDL.PARTY_TYPE_FLAG = 'R'

                                    AND AMOUNT_DR IS NOT NULL

                        ) RECEIVER_ACCOUNT

WHERE 1 = 1

            AND BATCH_NUMBER = :P_BATCH

            AND TRX_NUMBER = :P_TRX_NO

            AND FTH.RECIPIENT_ID = FIO.INTERCO_ORG_ID

            AND FTH.TO_LE_ID = XEP.Legal_entity_id

            AND FTB.BATCH_ID = FTH.BATCH_ID

            AND FTB.INITIATOR_ID = FIOP.INTERCO_ORG_ID

            AND FTB.TRX_TYPE_ID = FTTL.TRX_TYPE_ID

            AND FTTL.LANGUAGE = USERENV('LANG')

            AND FTH.TRX_ID = PROVIDER_ACCOUNT.TRX_ID

            AND FTH.TRX_ID = RECEIVER_ACCOUNT.TRX_ID

            AND PROVIDER_ACCOUNT.DIST_NUMBER = RECEIVER_ACCOUNT.DIST_NUMBER

            AND PROVIDER_ACCOUNT.TRX_ID = RECEIVER_ACCOUNT.TRX_ID