[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mtr.reason_name
INTO l_reason
FROM mtl_transaction_reasons mtr
WHERE mtr.reason_id = i_reason;
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_from_write_off_date from_write_date,
:i_to_write_off_date to_write_date,
:l_reason reason_name,
decode(:i_comments, ''N'', ''No'',
''Yes'') comments,
:i_from_amount min_amount,
:i_to_amount max_amount,
crs.displayed_field sort_option,
:i_bal_segment_from bal_seg_from,
:i_bal_segment_to bal_seg_to
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_write_off_date ,
i_to_write_off_date ,
l_reason,
i_comments ,
i_from_amount ,
i_to_amount ,
i_bal_segment_from,
i_bal_segment_to,
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;
select xla.currency_code
into l_currency
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;
OPEN l_ref_cur FOR 'SELECT gcc.concatenated_segments account,
cwo.write_off_id write_off_id,
cwo.write_off_amount write_off_amount,
cwo.transaction_date write_off_date,
(SELECT gcc2.concatenated_segments
FROM gl_code_combinations_kfv gcc2,
cst_write_offs cwo2
WHERE cwo2.offset_account_id =
gcc2.code_combination_id
AND cwo2.accrual_account_id =
cwo.accrual_account_id
AND cwo2.write_off_id =
cwo.write_off_id
AND cwo2.offset_account_id =
cwo.offset_account_id) offset_account,
decode ( cwo.transaction_type_code,
''REVERSE WRITE OFF'',
cwo.reversal_id,
NULL) reversal_id,
decode(cwo.inventory_item_id, null, null,
(select msi.concatenated_segments from
mtl_system_items_vl msi
where inventory_item_id = cwo.inventory_item_id
and rownum <2)
) item,
decode (cwod.write_off_transaction_id,
NULL,
decode(cwod.inventory_transaction_id,
NULL,
decode( cwod.invoice_distribution_id,
NULL,
pol.UNIT_MEAS_LOOKUP_CODE,
pol.UNIT_MEAS_LOOKUP_CODE),
mmt.TRANSACTION_UOM),
null) uom,
pdt.displayed_field destination,
pov.vendor_name vendor,
mtr.reason_name reason,
:l_currency l_currency,
decode( :i_comments,
''Y'',
cwo.comments,
NULL) comments,
poh.segment1 po_number,
por.release_num po_release,
pol.line_num po_line,
poll.shipment_num po_shipment,
pod.distribution_num po_distribution,
cwo.po_distribution_id po_distribution_id,
decode (cwod.write_off_transaction_id,
NULL,
decode(cwod.inventory_transaction_id,
NULL,
decode( cwod.invoice_distribution_id,
NULL,
''PO'',
''AP''),
''INV''),
''WO'') transaction_source,
decode( cwod.inventory_transaction_id,
NULL,
(SELECT crc2.displayed_field
FROM cst_reconciliation_codes crc2
WHERE to_char(crc2.lookup_code) =
to_char(cwod.transaction_type_code)
AND crc2.lookup_type IN
( ''RCV TRANSACTION TYPE'',
''ACCRUAL WRITE-OFF ACTION'',
''ACCRUAL TYPE'') ) ,
(SELECT mtt.transaction_type_name
FROM mtl_transaction_types mtt
WHERE to_char(mtt.transaction_type_id) =
to_char(cwod.transaction_type_code)
)) transaction_type,
cwod.transaction_date transaction_date,
cwod.quantity quantity,
decode ( cwo.transaction_type_code,
''REVERSE WRITE OFF'',
cwod.amount,
(-1*cwod.amount)) abs_amount,
cwod.amount amount,
cwod.entered_amount entered_amount,
cwod.currency_code currency_code,
apia.invoice_num invoice_number,
aida.distribution_line_number invoice_line,
rsh.receipt_num receipt_number,
cwod.inventory_transaction_id inventory_transaction_id,
cwod.write_off_transaction_id write_off_trans_id,
mp.organization_code org
FROM cst_write_offs cwo,
po_vendors pov,
mtl_transaction_reasons mtr,
po_headers_all poh,
po_lines_all pol,
po_releases_all por,
po_line_locations_all poll,
po_distributions_all pod,
cst_write_off_details cwod,
ap_invoices_all apia,
ap_invoice_distributions_all aida,
rcv_transactions rct,
rcv_shipment_headers rsh,
mtl_parameters mp,
po_destination_types_all_v pdt,
gl_code_combinations_kfv gcc,
mtl_material_transactions mmt
WHERE cwo.write_off_id = cwod.write_off_id
AND pov.vendor_id(+) = cwo.vendor_id
AND mtr.reason_id(+) = cwo.reason_id
AND pod.po_distribution_id(+) = cwo.po_distribution_id
AND poll.line_location_id(+) = pod.line_location_id
AND pol.po_line_id(+) = pod.po_line_id
AND por.po_release_id(+) = pod.po_release_id
AND poh.po_header_id(+) = pod.po_header_id
AND cwod.invoice_distribution_id = aida.invoice_distribution_id (+)
AND apia.invoice_id(+) = aida.invoice_distribution_id
AND cwod.rcv_transaction_id = rct.transaction_id(+)
AND rsh.shipment_header_id(+) = rct.shipment_header_id
AND pdt.lookup_code(+) = cwo.destination_type_code
AND cwod.inventory_organization_id = mp.organization_id(+)
and cwod.inventory_transaction_id = mmt.transaction_id (+)
AND cwo.accrual_account_id = gcc.code_combination_id
AND cwo.operating_unit_id = :l_current_org_id
AND cwod.operating_unit_id = :l_current_org_id
AND cwo.WRITE_OFF_AMOUNT
BETWEEN nvl(:i_from_amount,cwo.WRITE_OFF_AMOUNT)
AND nvl(:i_to_amount,cwo.WRITE_OFF_AMOUNT)
AND cwo.transaction_date
BETWEEN nvl( :i_from_write_off_date,cwo.transaction_date )
AND nvl(:i_to_write_off_date ,cwo.transaction_date )
AND nvl(:i_reason ,nvl(cwo.reason_id,-1)) = nvl(cwo.reason_id,-1)
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 ,
''REASON'',mtr.reason_name,
''AMOUNT'', decode(sign(write_off_amount),-1,
chr(0) || translate( to_char(abs(write_off_amount), ''000000000999.999''),
''0123456789'', ''9876543210''), to_char(write_off_amount, ''000000000999.999'' ) ),
''OFFSET ACCOUNT'', cwo.offset_account_id,
''DATE'', to_char(transaction_date, ''yyyymmddhh24miss'')) '
USING l_currency,
i_comments,
l_current_org_id,
l_current_org_id,
i_from_amount,
i_to_amount,
i_from_write_off_date,
i_to_write_off_date,
i_reason,
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 ;