Query to derive AR Transaction details

Query to derive AR Transaction details:-

  SELECT sob.name sob_name,

         hou.name ou_name,

         l.customer_trx_line_id,

         t.customer_trx_id,

         l.line_number,

         l.line_type,

         l.reason_code,

         l.quantity_invoiced,

         l.inventory_item_id,

         l.description,

         l.uom_code,

         l.unit_selling_price,

         (d.amount) extended_amount,

         l.interface_line_context,

         l.interface_line_attribute1,

         l.interface_line_attribute2,

         l.interface_line_attribute3,

         l.interface_line_attribute4,

         l.interface_line_attribute5,

         l.interface_line_attribute6,

         l.interface_line_attribute7,

         l.interface_line_attribute8,

         l.interface_line_attribute9,

         gcc.segment1,

         gcc.segment2,

         gcc.segment3,

         gcc.segment4,

         gcc.segment5,

         gcc.segment6,

         gcc.segment7,

         gcc.segment8,

         d.account_class,

         d.gl_date,

         d.acctd_amount,

         d.attribute1,

         d.attribute2,

         d.attribute3,

         d.attribute4,

         sp.salesrep_number,

         s.revenue_percent_split,

         l.link_to_cust_trx_line_id

    FROM ra_customer_trx_all t,

         ar_payment_schedules_all p,

         gl_sets_of_books sob,

         hr_operating_units hou,

         ra_customer_trx_lines_all l,

         ra_cust_trx_line_gl_dist_all d,

         gl_code_combinations gcc,

         ra_cust_trx_line_salesreps_all s,

         jtf_rs_salesreps sp

   WHERE     t.customer_trx_id = p.customer_trx_id

         AND p.status = 'OP'

         AND t.set_of_books_id = sob.set_of_books_id

         AND t.org_id = hou.organization_id

         AND t.customer_trx_id = l.customer_trx_id

         AND d.customer_trx_id = l.customer_trx_id

         AND d.customer_trx_line_id = l.customer_trx_line_id

         AND d.code_combination_id = gcc.code_combination_id

         AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id

         AND s.customer_trx_id(+) = t.customer_trx_id

         AND s.salesrep_id = sp.salesrep_id(+)

         AND s.org_id = sp.org_id(+)

ORDER BY customer_trx_id,

         line_number,

         line_type,

         customer_trx_line_id;

How to derive code_combination_id(CCID) programatically using fnd_flex_ext.get_combination_id

/*-----------------------------------------------------------------------------*/

/*  Function: xx_get_ccid_fnc                                                         */

/* Combination_id output may be NULL if combination is invalid.        */

/* @param application_short_name Application Short Name             */

/* @param key_flex_code Key Flexfield Code                                  */

/* @param structure_number Structure number                             */

/* @param validation_date Validation date                                     */

/* @param n_segments Number of segments                                */

/* @param segments Segment array                                            */

/* @param combination_id Code Combination Id                           */

/* @param data_set Data set number                                          */

/* @return Returns TRUE if combination is valid, FALSE otherwise. */

/*---------------------------------------------------------------------------*/

FUNCTION xx_get_comb_id (p_coa_id        IN NUMBER,

                         p_company       IN VARCHAR2,

                         p_cost_center   IN VARCHAR2,

                         p_account       IN VARCHAR2,

                         p_interco       IN VARCHAR2,

                         p_future        IN VARCHAR2)

   RETURN NUMBER

IS

   l_appl_shrt_name    VARCHAR2 (80) := 'SQLGL';

   l_key_flx_cd        VARCHAR2 (80) := 'GL#';

   l_validation_date   DATE := SYSDATE;

   l_seg_size          NUMBER := 5;

   l_segments          fnd_flex_ext.segmentarray;

   l_combination_id    NUMBER;

   l_data_set          NUMBER := NULL;

   l_account_flag      BOOLEAN;

BEGIN

   l_segments (1) := p_company;

   l_segments (2) := p_cost_center;

   l_segments (3) := p_account;

   l_segments (4) := p_interco;

   l_segments (5) := p_future;

   l_account_flag :=   fnd_flex_ext.get_combination_id (

                                                                    application_short_name   => l_appl_shrt_name,

                                                                    key_flex_code                 => l_key_flx_cd,

                                                                    structure_number            => p_coa_id,

                                                                    validation_date                => l_validation_date,

                                                                    n_segments                    => l_seg_size,

                                                                    segments                        => segments,

                                                                    combination_id                => l_combination_id,

                                                                    data_set                         => l_data_set);

   DBMS_OUTPUT.put_line ('combination_id = ' || l_combination_id);

   IF l_account_flag

   THEN

      DBMS_OUTPUT.put_line ('l_account_flag = true');

   ELSE

      DBMS_OUTPUT.put_line ('l_account_flag = false');

   END IF;

   RETURN l_combination_id;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error in xx_get_comb_id=>' || SQLERRM);

      RETURN NULL;

END xx_get_comb_id;