[Home] [Help]
1068:
1069: FUNCTION get_segment_value(ccid NUMBER,segment_code VARCHAR2) RETURN VARCHAR2 AS
1070: /* This function is to return the value of segment for a code_combination_id.
1071: If segment_code is segment1, then the value of segment1 for the given code_combination_id(ccid) will be returned*/
1072: segment_value gl_code_combinations.segment1%TYPE;
1073: sqlstmt VARCHAR2(100);
1074: l_api_name CONSTANT VARCHAR2(30) :='GET_SEGMENT_VALUE';
1075: BEGIN
1076:
1079: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
1080: G_PKG_NAME||': '||l_api_name||'()+');
1081: END IF;
1082:
1083: sqlstmt := 'SELECT to_char('||segment_code||') FROM gl_code_combinations WHERE code_combination_id = :ccid';
1084:
1085: BEGIN
1086: EXECUTE IMMEDIATE sqlstmt INTO segment_value USING ccid;
1087: EXCEPTION
3250: ,gl_import_references glimp
3251: ,xla_ae_lines xll
3252: ,xla_ae_headers xlh
3253: ,xla_distribution_links xld
3254: ,gl_code_combinations glcc
3255: WHERE jh.ledger_id = '||g_ledger_id||'
3256: AND jh.je_source in (''Payables'',''Receivables'')
3257: AND jh.je_header_id = jl.je_header_id
3258: AND glimp.je_header_id = jh.je_header_id
3810: ||b.compiled_value_attributes
3811: ||fnd_global.newline, fnd_global.newline,1,'|| g_exclusion_qualifier_position||')+1, 1 )),''N'') <> ''Y'') V
3812: WHERE V.summary_flag= ''Y''
3813: OR exists (SELECT 1
3814: FROM gl_code_combinations glcc
3815: WHERE glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||
3816: ' AND glcc.summary_flag = ''N''
3817: AND ('''||l_exclusive_mode||'''=''Y'' OR
3818: ('''||l_exclusive_mode||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
4442: ','||g_last_updated_by|| ','''||g_last_update_date||''''||
4443: ','||g_last_update_login|| ' FROM fnd_flex_values_vl fv
4444: WHERE fv.flex_value_set_id = '||g_cost_center_value_set_id||
4445: ' AND EXISTS (SELECT 1
4446: FROM gl_code_combinations glcc
4447: WHERE glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||
4448: ' AND glcc.summary_flag = ''N''
4449: AND ('''||l_exclusive_mode||'''=''Y''
4450: OR ('''||l_exclusive_mode ||'''=''N'' AND glcc.'||g_bsv_segment||' in (select jg_info_v1 from jg_zz_vat_trx_gt)))
4708: ,0 )
4709: )
4710: ),0)),1,''D'',''C'') end_bal_type
4711: FROM gl_balances glb
4712: ,gl_code_combinations glcc
4713: WHERE period_name in('''||g_period_name||''' , '''||g_adjustment_period_name||''')
4714: AND glb.ledger_id = '||g_ledger_id||
4715: ' AND glb.currency_code = '''||g_currency_code||'''
4716: AND glb.code_combination_id= glcc.code_combination_id
4832: ,0 )
4833: )
4834: ),0)),1,''D'',''C'') end_bal_type
4835: FROM gl_balances glb
4836: ,gl_code_combinations glcc
4837: WHERE period_name in('''||g_period_name||''' , '''||g_adjustment_period_name||''')
4838: AND glb.ledger_id = '||g_ledger_id||
4839: ' AND glb.currency_code = '''||g_currency_code||'''
4840: AND glb.code_combination_id= glcc.code_combination_id
5372: ,NVL(SUM(jl.accounted_dr),0) accounted_dr
5373: ,NVL(SUM(jl.accounted_cr),0) accounted_cr
5374: FROM gl_je_headers jh
5375: ,gl_je_lines jl
5376: ,gl_code_combinations glcc
5377: WHERE jh.ledger_id = '||g_ledger_id||'
5378: AND jh.default_effective_date BETWEEN '''||g_start_date||''' AND '''||g_end_date||'''
5379: AND jh.je_header_id = jl.je_header_id
5380: AND jh.actual_flag = ''A''
5630: + (glb.PERIOD_NET_DR - glb.PERIOD_NET_CR)))),''99999999999999990D00'',''NLS_NUMERIC_CHARACTERS = '''',.'''''')) amount
5631: ,DECODE(SIGN(sum((glb.BEGIN_BALANCE_DR - glb.BEGIN_BALANCE_CR)
5632: + (glb.PERIOD_NET_DR - glb.PERIOD_NET_CR))),1,''D'',''C'') amount_flag
5633: FROM gl_balances glb
5634: ,gl_code_combinations glcc
5635: WHERE glb.period_name = '''||g_period_name||''''||
5636: ' AND glb.code_combination_id = glcc.code_combination_id
5637: AND glb.ledger_id = '||g_ledger_id||
5638: ' AND glcc.chart_of_accounts_id = '||g_chart_of_accounts_id||