Query to get Account description of GL Code Combination in Oracle APPS

SELECT

    gcc.concatenated_segments                                                                              account,

    gl_flexfields_pkg.get_concat_description(gcc.chart_of_accounts_id, gcc.code_combination_id) account_description

FROM

    gl_code_combinations_kfv gcc

WHERE

    gcc.code_combination_id = 111111

API to find out Oracle GL Account Code Combination ID for given Segments (fnd_flex_ext.get_combination_id)

PROCEDURE get_costing_segments (
      p_segment1           IN   VARCHAR2
    , p_segment2           IN   VARCHAR2
    , p_segment3           IN   VARCHAR2
    , p_segment4           IN   VARCHAR2
    , p_segment5           IN   VARCHAR2
    , p_def_code_comb_id   OUT   NUMBER
, p_message    OUT   VARCHAR2
   )
AS
l_application_short_name      VARCHAR2 (240);
    l_key_flex_code               VARCHAR2 (240);
    l_structure_num               NUMBER;
    l_validation_date             DATE;
    n_segments                    NUMBER;
    segments                      apps.fnd_flex_ext.segmentarray;
    l_combination_id              NUMBER;
    l_data_set                    NUMBER;
    l_return                      BOOLEAN;
    l_message                     VARCHAR2 (1000);

BEGIN
l_application_short_name := 'SQLGL';
l_key_flex_code := 'GL#';

SELECT id_flex_num
    INTO l_structure_num
    FROM apps.fnd_id_flex_structures
WHERE id_flex_code = 'GL#'
    AND id_flex_structure_code = 'SPWX_CHART_OF_ACCOUNTS';

l_validation_date := SYSDATE;
n_segments := 6;
segments (1) := p_segment1;
segments (2) := p_segment2;
segments (3) := p_segment3;
segments (4) := p_segment4;
segments (5) := p_segment5;
segments (6) := '000';
l_data_set := NULL;

l_return :=
     fnd_flex_ext.get_combination_id
                      (application_short_name      => l_application_short_name
                     , key_flex_code               => l_key_flex_code
                     , structure_number            => l_structure_num
                     , validation_date             => l_validation_date
                     , n_segments                  => n_segments
                     , segments                    => segments
                     , combination_id              => l_combination_id
                     , data_set                    => l_data_set
                      );

l_message := fnd_flex_ext.GET_MESSAGE;

IF l_return
THEN
debug_msg ('l_Return = TRUE');
debug_msg ( 'COMBINATION_ID = ' || l_combination_id);
p_def_code_comb_id := l_combination_id;
p_message := NULL;
   
ELSE

debug_msg ( 'Error: ' || l_message);
p_def_code_comb_id := NULL;
p_message := l_message;

END IF;

EXCEPTION WHEN OTHERS THEN
debug_msg ( 'Error: ' || SQLERRM);
p_def_code_comb_id := NULL;
l_message := l_message||'-'||SQLERRM;   
END get_costing_segments;

Query to find batchname and perioddname from invoice

SELECT gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE --gjh.period_name IN ('OCT-2008','NOV-2008')
 glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
and rcta.trx_number ='13079077'

GL Period Delete query

delete from gl_period_statuses where start_date >= '31-JAN-2014' and closing_status in ('F','N');

delete from gl_periods where period_set_name ='Global' and start_date >= '31-JAN-2014'
and period_type ='21' ;

delete from pa_periods_all where org_id in (111,222)

and start_date >= '31-JAN-2014' and status='N';

GL Tables

GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS
GL_BALANCES

GL_PERIODS

GL_JESOURCES

GL_SET_OF_BOOKS
GL_IMPORT_REFERENCES
GL_BUDGETS
GL_BUDGET_ENTITIES
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_VERSIONS
GL_INTERFACE

GL_QUERY

select  jh.je_source,
jh.je_batch_id "Batch Number",
jb.name "Batch Name",
jb.creation_date "Batch Date",
jb.description "Batch Comment",
jl.je_line_num "Line Number",
gcc.segment2 "Account Number",
gcc.segment1 "Balancing Segment",
gcc.segment3 "Cost Center",
fds.type "Doc Type",
substr(jb.default_period_name, instr(jb.default_period_name,'-')+1,length(jb.default_period_name)) "Fiscal Year",
jb.posted_date "Posting Date",
jb.default_effective_date "Effective Date",
jb.default_period_name "Effective Period",
jl.accounted_dr,
jl.accounted_cr,
jh.currency_code "Currency Indicator",
jh.currency_conversion_rate ,
jh.description "Header Description",
jl.description "Line Description",
jh.accrual_rev_status "Reverse Indicator",
jh.accrual_rev_effective_date "Reverse Date",
jh.accrual_rev_period_name,
jh.created_by,
jb.creation_date,
jh.last_update_date
from gl_je_headers jh,
gl_je_batches jb,
gl_je_lines jl,
gl_code_combinations gcc,
fnd_document_sequences fds
where jh.je_batch_id = jb.je_batch_id
and jh.je_header_id = jl.je_header_id
and jl.code_combination_id = gcc.code_combination_id
and fds.doc_sequence_id =jh.doc_sequence_id
AND to_date(TO_CHAR(jb.creation_date,'dd-mon-rrrr') ,'dd-mon-rrrr') BETWEEN to_date('01-Jan-2012','dd-mon-rrrr') AND to_date('31-Dec-2012','dd-mon-rrrr');
--and jb.name ='E14 Purchasing 18290241: A 161476'
--and jl.je_line_num =1;