R12 GL Code Combination and Description and GL Queries

GL Code Combination and Description Query:

select
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5
 ACCOUNT,
A1.DESCRIPTION||'-'||A2.DESCRIPTION ||'-'|| A3.DESCRIPTION||'-'||A4.DESCRIPTION ||'-'|| A5.DESCRIPTION  DECSCRIPTION
from
fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
fnd_flex_values_vl A4,
fnd_flex_values_vl A5,
gl_code_combinations gcc
WHERE
a1.flex_value=gcc.segment1
And a1.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT1' )
And a2.flex_value=gcc.segment2
And a2.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT2' )
And a3.flex_value=gcc.segment3
And a3.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT3' )                        
And a4.flex_value=gcc.segment4
And a4.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT4' )
And a5.flex_value=gcc.segment5
And a5.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
                            from fnd_id_flex_segments
                           WHERE application_id = 101 AND id_flex_num='50576'
                           AND id_flex_code = 'GL#' and enabled_flag = 'Y'
                           and application_column_name='SEGMENT5' );

-------Account Combination and Description --------

select gcc.CONCATENATED_SEGMENTS,
GL_FLEXFIELDS_PKG.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations_kfv gcc
WHERE gcc.CHART_OF_ACCOUNTS_ID=50576;


------Account Segment and Description----------------

SELECT gl_flexfields_pkg.get_description_sql
       (
        chart_of_accounts_id,--- chart of account id
        1,----- Position of segment
        segment1 ---- Segment value
        )
FROM gl_code_combinations;

------- GL Account Balances-------------------
select
glc.segment5,
ffv.description,
sum((begin_balance_dr) - (begin_balance_cr)) as opening_amount,
sum(period_net_dr_beq) as debit,
sum(period_net_cr_beq) as credit,
--sum(period_net_dr) as Net_Debit,
--sum(period_net_cr) as Net_Credit,
sum((begin_balance_dr) - (begin_balance_cr)) + sum(period_net_dr) - sum(period_net_cr) as closing_amount
from gl_balances gb, gl_code_combinations glc, APPS.FND_FLEX_VALUES_VL FFV
where gb.ledger_id = 2364 --ledger name for US
and gb.period_name = 'Oct-15'
and gb.code_combination_id = glc.code_combination_id
and glc.segment5 = ffv.flex_value
and gb.actual_flag = 'A'
and gb.currency_code = 'USD'
group by glc.segment5, ffv.description
order by segment5, sum((begin_balance_dr) - (begin_balance_cr)) + sum(period_net_dr) - sum(period_net_cr) desc;

----Query to fetch GL data and drill down to payables source data like invoice , po and payment details-----

SELECT 

distinct

gll.Name "LEDGER NAME",

gjh.je_source "JOURNAL SOURCE",

gjh.je_category "JOURNAL CATEGORY",

gjh.period_name "GL PERIOD",

gjh.Name "JOURNAL NAME",

gjh.CURRENCY_CODE,

gjl.description "JOURNAL DESCRIPTION",

gjh.default_effective_date "EFFECTIVE GL DATE",

DECODE (gjh.actual_flag, 'A', 'Actual','B', 'Budget','E', 

'Encumbrance') Balance_Type,

DECODE (gjl.status,  'P', 'Posted',  'U', 'Unposted',  gjl.status) Batch_Status,

gjh.posted_date,

gcc.segment1 COMPANY,

gcc.segment2 BUSINESS_UNIT,

gcc.segment3 PROFIT_CENTER,

gcc.segment4 DEPARTMENT,

gcc.segment5 ACCOUNT,

gcc.segment6 INTERCOMPANY,

gcc.segment7 FUTURE_1,

gcc.segment8 FUTURE_2,

nvl(gjh.running_total_accounted_dr,0) JE_TOTAL_DR,

nvl(gjh.running_total_accounted_cr,0) JE_TOTAL_CR, 

gjh.JE_HEADER_ID,

gjl.JE_LINE_NUM,

nvl(gjl.ENTERED_DR,0) "JOURNAL DEBIT AMOUNT",

nvl(gjl.ENTERED_CR,0) "JOURNAL CREDIT AMOUNT",

XAL.accounting_class_code "ACCOUNTING CLASS",

nvl(XAL.ACCOUNTED_DR,0) "ACCOUNTED DEBIT",

nvl(XAL.ACCOUNTED_CR,0) "ACCOUNTED CREDIT",

xte.transaction_number AP_Invoice_Number,

TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') Invoice_date,

poh.SEGMENT1 PO_NUMBER,

apl.line_number,

apl.Amount Invoice_Amount,

apl.unit_price,

apl.quantity_invoiced,

apl.line_type_lookup_code,

aca.check_number,

aip.amount Payment_Amount,

nvl(aip.DISCOUNT_TAKEN,0)  Payment_Discount_Taken,

aca.check_date,

aca.Amount Total_Check_Amount,

aca.payment_method_code,

apl.description,

xte.ENTITY_CODE,

pv.vendor_name Vendor_name,

pv.segment1 Vendor_number,

gjh.default_effective_date gl_date

FROM 

apps.gl_je_headers gjh,

apps.gl_je_lines gjl,

apps.gl_je_batches gjb,

apps.gl_import_references gir,

apps.gl_ledgers gll,

xla.xla_ae_lines xal,

xla.xla_ae_headers xah,

xla.xla_events xe,

xla.xla_transaction_entities xte,

gl_code_combinations gcc,

apps.ap_suppliers pv,

gl_period_statuses ps,

apps.AP_INVOICE_LINES_ALL apl,

apps.PO_HEADERS_ALL poh,

apps.AP_CHECKS_ALL aca,

apps.AP_INVOICE_PAYMENTS_ALL aip

WHERE     

gll.ledger_id in (2364) ------(2403, 2383,2364)

AND gll.ledger_id=gjh.ledger_id

AND gjh.je_source = 'Payables'

AND gjh.je_header_id = gjl.je_header_id

AND gjh.actual_flag = 'A'

AND gjh.status = 'P'

AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

AND (gjl.accounted_dr != 0 OR gjl.accounted_cr != 0)

AND gjl.je_header_id = gir.je_header_id

AND gjl.je_line_num = gir.je_line_num

AND gir.gl_sl_link_id = xal.gl_sl_link_id

AND gir.gl_sl_link_table = xal.gl_sl_link_table

AND xal.ae_header_id = xah.ae_header_id

AND xal.application_id = xah.application_id

AND xah.event_id = xe.event_id

AND xah.application_id = xe.application_id

AND xe.entity_id = xte.entity_id

AND xe.application_id = xte.application_id

AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)

AND xal.party_id = pv.vendor_id(+)

AND gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

AND ps.application_id = 101

AND xte.SOURCE_ID_INT_1=apl.INVOICE_ID

AND apl.INVOICE_ID=aip.INVOICE_ID

AND gjl.period_name = ps.period_name

AND apl.po_header_id=poh.po_header_id

AND aca.CHECK_ID = aip.CHECK_ID

AND gjh.je_header_id=3129318

AND ps.ledger_id = gjh.LEDGER_ID

AND ps.period_Name=:P_Period_Name

AND gcc.segment5=:P_AP_Account

order by gjh.JE_HEADER_ID,gjl.JE_LINE_NUM, INVOICE_DATE, PO_NUMBER, LINE_NUMBER;