[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mp.organization_code
into l_org_code
from mtl_parameters mp
where mp.organization_id = l_current_org_id;
select hr.NAME
into l_org_name
from HR_ALL_ORGANIZATION_UNITS hr
where hr.ORGANIZATION_ID = l_current_org_id;
OPEN l_ref_cur FOR 'SELECT :l_org_code org_code,
:l_org_name org_name,
xla.NAME ledger_name,
xla.currency_code CUR_CODE,
:i_title TITLE_NAME,
:i_bal_segment_from from_seg,
:i_bal_segment_to to_seg
FROM XLA_GL_LEDGERS_V xla,
HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.ORGANIZATION_ID = :l_current_org_id
and hoi.ORG_INFORMATION_CONTEXT = ''Operating Unit Information''
and xla.LEDGER_ID = hoi.ORG_INFORMATION3 '
USING l_org_code,
l_org_name,
i_title,
i_bal_segment_from,
i_bal_segment_to,
l_current_org_id;
SELECT fav.application_column_name
INTO l_bal_segment
FROM gl_sets_of_books gl,
fnd_segment_attribute_values fav,
hr_organization_information hr
WHERE hr.org_information_context = 'Operating Unit Information'
AND hr.organization_id = l_current_org_id
AND to_number(hr.org_information3) = gl.set_of_books_id
AND fav.segment_attribute_type = 'GL_BALANCING'
AND fav.attribute_value = 'Y'
AND fav.application_id = 101
AND fav.id_flex_code = 'GL#'
AND id_flex_num = gl.chart_of_accounts_id;
OPEN l_ref_cur FOR ' SELECT account,
transaction_type,
amount_written_off,
outstanding_balance
FROM
(SELECT gcc.concatenated_segments account,
''AP-PO'' transaction_type,
SUM(crs.write_off_balance) amount_written_off,
SUM(crs.ap_balance +
crs.po_balance +
crs.write_off_balance) outstanding_balance
FROM cst_reconciliation_summary crs,
gl_code_combinations_kfv gcc
WHERE crs.accrual_account_id = gcc.code_combination_id
AND crs.operating_unit_id = :l_current_org_id
AND (( :l_account_range = 0 )
OR ( :l_account_range = 1 AND
gcc.' || l_bal_segment || ' >= :i_bal_segment_from)
OR ( :l_account_range = 2 AND
gcc.' || l_bal_segment || ' <= :i_bal_segment_to)
OR ( :l_account_range = 3 AND
gcc.' || l_bal_segment || ' BETWEEN :i_bal_segment_from
AND :i_bal_segment_to ) )
GROUP BY crs.accrual_account_id,
gcc.concatenated_segments
UNION
SELECT gcc.concatenated_segments account,
decode( cmr.transaction_type_code, ''CONSIGNMENT'',
(SELECT crc.displayed_field
FROM cst_reconciliation_codes crc
WHERE crc.lookup_code =
cmr.transaction_type_code
AND crc.lookup_type IN
( ''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE'' ) ) ,
decode (min(nvl(INVENTORY_TRANSACTION_ID,-1)), -1,
(SELECT crc.displayed_field
FROM cst_reconciliation_codes crc
WHERE crc.lookup_code = cmr.transaction_type_code
AND crc.lookup_type IN
( ''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE'' ) ),
(SELECT mtt.transaction_type_name
FROM mtl_transaction_types mtt
WHERE cmr.transaction_type_code =
to_char(mtt.transaction_type_id) ))) transaction_type,
(select nvl(sum (cwo.write_off_amount) ,0)
from cst_write_offs cwo ,
cst_write_off_details cwod
where cwo.accrual_account_id =
cmr.accrual_account_id
and cmr.TRANSACTION_TYPE_CODE =
cwod.TRANSACTION_TYPE_CODE
and cwod.write_off_id =
cwo.write_off_id) amount_written_off,
SUM(cmr.amount) outstanding_balance
FROM gl_code_combinations_kfv gcc,
cst_misc_reconciliation cmr
WHERE cmr.accrual_account_id = gcc.code_combination_id
AND cmr.operating_unit_id = :l_current_org_id
AND (( :l_account_range = 0 )
OR ( :l_account_range = 1 AND
gcc.' || l_bal_segment || ' >= :i_bal_segment_from)
OR ( :l_account_range = 2 AND
gcc.' || l_bal_segment || ' <= :i_bal_segment_to)
OR ( :l_account_range = 3 AND
gcc.' || l_bal_segment || ' BETWEEN :i_bal_segment_from
AND :i_bal_segment_to ) )
GROUP BY cmr.accrual_account_id,
gcc.concatenated_segments,
cmr.transaction_type_code )'
USING l_current_org_id,
l_account_range,
l_account_range,
i_bal_segment_from,
l_account_range,
i_bal_segment_to,
l_account_range,
i_bal_segment_from,
i_bal_segment_to,
l_current_org_id,
l_account_range,
l_account_range,
i_bal_segment_from,
l_account_range,
i_bal_segment_to,
l_account_range,
i_bal_segment_from,
i_bal_segment_to ;
OPEN l_ref_cur FOR SELECT l_count l_count FROM dual ;