[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,
crs.displayed_field sort_option,
:i_from_date from_date,
:i_to_date to_date,
:i_from_item from_item,
:i_to_item to_item,
:i_from_amount from_amount,
:i_to_amount to_amount,
:i_bal_segment_from bal_seg_from,
:i_bal_segment_to bal_seg_to,
decode(:l_age_option,
1,
''Last Receipt Date'',
''Last Activity Date'') age_option
FROM cst_reconciliation_codes crs,
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
AND crs.lookup_type = ''SRS ACCRUAL ORDER BY''
AND crs.LOOKUP_CODE = :i_sort_by'
USING l_org_code,
l_org_name,
i_title,
i_from_date ,
i_to_date ,
i_from_item ,
i_to_item ,
i_from_amount,
i_to_amount,
i_bal_segment_from,
i_bal_segment_to,
l_age_option,
l_current_org_id,
i_sort_by;
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 gcc.concatenated_segments account,
decode(cmr.invoice_distribution_id,
NULL,
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'')),
(SELECT mtt.transaction_type_name
FROM mtl_transaction_types mtt
WHERE cmr.transaction_type_code =
to_char(mtt.transaction_type_id) )),
(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''))) transaction_type,
decode(cmr.invoice_distribution_id,
NULL,
''INV'',
''AP'') transaction_source,
cmr.transaction_date transaction_date,
cmr.quantity quantity,
cmr.amount amount,
cmr.entered_amount entered_amount,
cmr.currency_code currency_code,
apia.invoice_num invoice_number,
aida.invoice_line_number invoice_line,
NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1) po_number,--Changed as a part of CLM
por.release_num po_release,
nvl(POL.LINE_NUM_DISPLAY, to_char(POL.LINE_NUM)) po_line,--Changed as a part of CLM
poll.shipment_num po_shipment,
pod.distribution_num po_distribution,
cmr.po_distribution_id po_distribution_id,
cmr.inventory_transaction_id inventory_transaction_id,
decode(cmr.inventory_item_id, null, null,
(select msi.concatenated_segments from
mtl_system_items_vl msi
where inventory_item_id = cmr.inventory_item_id
and rownum <2)
) item,
decode(cmr.invoice_distribution_id,
NULL,
mmt.TRANSACTION_UOM,
pol.UNIT_MEAS_LOOKUP_CODE) uom,
trunc (decode (cmr.transaction_type_code,
''CONSIGNMENT'', decode(cmr.po_distribution_id,
null, null,
decode ( :l_age_option, 1,
(sysdate - nvl( (select max(cmr2.transaction_date)
from cst_misc_reconciliation cmr2
where cmr2.po_distribution_id= cmr.po_distribution_id
and cmr2.inventory_transaction_id is not null
and cmr2.transaction_type_code = ''CONSIGNMENT''
),
(select max(cmr2.transaction_date)
from cst_misc_reconciliation cmr2
where cmr2.po_distribution_id = cmr.po_distribution_id
and cmr2.inventory_transaction_id is null
and cmr2.transaction_type_code = ''CONSIGNMENT''
and cmr2.invoice_distribution_id is not null)
)),
(sysdate - greatest( nvl( (select max(cmr2.transaction_date)
from cst_misc_reconciliation cmr2
where cmr2.po_distribution_id= cmr.po_distribution_id
and cmr2.inventory_transaction_id is not null
and cmr2.transaction_type_code = ''CONSIGNMENT''),
(select max(cmr2.transaction_date)
from cst_misc_reconciliation cmr2
where cmr2.po_distribution_id = cmr.po_distribution_id
and cmr2.inventory_transaction_id is null
and cmr2.transaction_type_code = ''CONSIGNMENT''
and cmr2.invoice_distribution_id is not null)
),
NVL((select max(cmr2.transaction_date)
from cst_misc_reconciliation cmr2
where cmr2.po_distribution_id = cmr.po_distribution_id
and cmr2.inventory_transaction_id is null
and cmr2.transaction_type_code = ''CONSIGNMENT''
and cmr2.invoice_distribution_id is not null),
(select max(cmr2.transaction_date)
from cst_misc_reconciliation cmr2
where cmr2.po_distribution_id= cmr.po_distribution_id
and cmr2.inventory_transaction_id is not null
and cmr2.transaction_type_code = ''CONSIGNMENT'')
)
)) -- age option 2
) --po dist id not null, age option 1
), --po dist_id null
null) --txn_type_code not consignment
) age_in_days,
pov.vendor_name vendor,
mp.organization_code org
FROM cst_misc_reconciliation cmr,
ap_invoices_all apia,
ap_invoice_distributions_all aida,
po_vendors pov,
mtl_parameters mp,
gl_code_combinations_kfv gcc,
po_distributions_all pod,
po_line_locations_all poll,
po_releases_all por,
po_lines_all pol,
po_headers_all poh,
mtl_material_transactions mmt
WHERE cmr.invoice_distribution_id = aida.invoice_distribution_id(+)
AND aida.invoice_id = apia.invoice_id(+)
AND cmr.vendor_id = pov.vendor_id(+)
AND cmr.inventory_organization_id = mp.organization_id(+)
AND cmr.accrual_account_id = gcc.code_combination_id
AND pod.po_distribution_id(+) = cmr.po_distribution_id
and cmr.inventory_transaction_id = mmt.transaction_id (+)
AND poll.line_location_id(+) = pod.line_location_id
AND pod.po_release_id = por.po_release_id(+)
AND pol.po_line_id(+) = pod.po_line_id
AND poh.po_header_id(+) = pod.po_header_id
AND cmr.operating_unit_id = :l_current_org_id
AND cmr.transaction_date BETWEEN
nvl( :i_from_date ,cmr.transaction_date )
AND nvl(:i_to_date ,cmr.transaction_date)
AND cmr.amount BETWEEN nvl(:i_from_amount,cmr.amount)
AND nvl(:i_to_amount,cmr.amount)
AND (:l_items_null = ''Y''
OR (:l_items_null = ''N''
AND decode(cmr.inventory_item_id, null, null,
(select msi.concatenated_segments
from mtl_system_items_vl msi
where inventory_item_id = cmr.inventory_item_id
and rownum <2))
between nvl(:i_from_item, decode(cmr.inventory_item_id, null,
null,
(select msi.concatenated_segments
from mtl_system_items_vl msi
where inventory_item_id = cmr.inventory_item_id
and rownum <2)))
and nvl(:i_to_item ,decode(cmr.inventory_item_id, null, null,
(select msi.concatenated_segments
from mtl_system_items_vl msi
where inventory_item_id = cmr.inventory_item_id
and rownum <2)))
))
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 ) )
ORDER BY decode( :i_sort_by ,
''ITEM'', item,
''AMOUNT'', decode(sign(amount),-1,
chr(0) || translate( to_char(abs(amount), ''000000000999.999''),
''0123456789'', ''9876543210''), to_char(amount, ''000000000999.999'' ) ),
''DATE'', to_char(transaction_date, ''yyyymmddhh24miss'')) '
USING l_age_option,
l_current_org_id,
i_from_date,
i_to_date,
i_from_amount,
i_to_amount,
l_items_null,
l_items_null,
i_from_item,
i_to_item,
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,
i_sort_by;
OPEN l_ref_cur FOR SELECT l_count l_count FROM dual ;