[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_aging_days age_days,
:i_from_item from_item,
:i_to_item to_item,
:i_from_vendor from_vendor,
:i_to_vendor to_vendor,
: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_aging_days,
i_from_item ,
i_to_item ,
i_from_vendor,
i_to_vendor,
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;
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 :l_age_days age_days,
gcc.concatenated_segments account,
decode(:l_age_days, 0, 0,
floor( ( sysdate - decode(:l_age_option,
1,
nvl(crs.last_receipt_date,crs.LAST_INVOICE_DIST_DATE),
greatest(nvl(last_receipt_date,LAST_INVOICE_DIST_DATE),
nvl(LAST_INVOICE_DIST_DATE, last_receipt_date))
)) / :l_age_div)*:l_age_days) bkt_start_date,
decode(:l_age_days, 0, 0,
ceil(( sysdate - decode(:l_age_option,
1,
nvl(crs.last_receipt_date,crs.LAST_INVOICE_DIST_DATE),
greatest(nvl(last_receipt_date,LAST_INVOICE_DIST_DATE),
nvl(LAST_INVOICE_DIST_DATE, last_receipt_date))
) ) / :l_age_div)*:l_age_days-1) bkt_end_date,
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_num,
crs.po_distribution_id po_distribution,
crs.po_balance po_balance,
crs.ap_balance ap_balance,
crs.write_off_balance wo_balance,
:l_currency l_currency,
(nvl(crs.po_balance,0) + nvl(crs.ap_balance,0)
+ nvl(crs.write_off_balance,0)) total_balance,
trunc(sysdate - decode(:l_age_option,
1,
nvl(crs.last_receipt_date,crs.LAST_INVOICE_DIST_DATE),
greatest(nvl(last_receipt_date,LAST_INVOICE_DIST_DATE), nvl(LAST_INVOICE_DIST_DATE, last_receipt_date))
)
) age_in_days,
decode(crs.inventory_item_id, null, null,
(select msi.concatenated_segments from
mtl_system_items_vl msi
where inventory_item_id = crs.inventory_item_id
and rownum <2)
) item,
decode(capr.write_off_id,
NULL, pol.UNIT_MEAS_LOOKUP_CODE,
NULL ) uom,
pov.vendor_name vendor,
pdt.displayed_field destination,
decode(capr.invoice_distribution_id,
NULL,
decode(capr.write_off_id,
NULL,
''PO'',
''WO''),
''AP'') transaction_source ,
crc.displayed_field transaction_type,
capr.transaction_date transaction_date,
apia.invoice_num invoice_number,
aida.invoice_line_number invoice_line,
capr.quantity quantity,
capr.amount amount,
capr.entered_amount entered_amount,
capr.currency_code currency_code,
capr.write_off_id write_off_id,
decode(capr.inventory_organization_id,
NULL,
NULL,
mp.organization_code) org,
rsh.receipt_num receipt_number
FROM cst_reconciliation_codes crc,
cst_ap_po_reconciliation capr,
ap_invoices_all apia,
ap_invoice_distributions_all aida,
mtl_parameters mp,
rcv_transactions rct,
rcv_shipment_headers rsh,
cst_reconciliation_summary crs,
po_distributions_all pod,
po_line_locations_all poll,
po_releases_all por,
po_lines_all pol,
po_headers_all poh,
po_vendors pov,
po_destination_types_all_v pdt,
gl_code_combinations_kfv gcc
WHERE crc.lookup_code = to_char(capr.transaction_type_code)
AND crc.lookup_type in ( ''RCV TRANSACTION TYPE'',
''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE'')
AND aida.invoice_distribution_id(+) = capr.invoice_distribution_id
AND apia.invoice_id(+) = aida.invoice_id
AND mp.organization_id(+) = capr.inventory_organization_id
AND rct.transaction_id(+) = capr.rcv_transaction_id
AND rsh.shipment_header_id(+) = rct.shipment_header_id
AND capr.po_distribution_id = crs.po_distribution_id
and crs.accrual_account_id = capr.accrual_account_id
AND pod.po_distribution_id = crs.po_distribution_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 pdt.lookup_code(+) = crs.destination_type_code
AND pov.vendor_id(+) = crs.vendor_id
AND crs.accrual_account_id = gcc.code_combination_id
AND crs.operating_unit_id = :l_current_org_id
AND capr.operating_unit_id = :l_current_org_id
AND (nvl(crs.po_balance,0) + nvl(crs.ap_balance,0)
+ nvl(crs.write_off_balance,0))
BETWEEN nvl(:i_from_amount,(nvl(crs.po_balance,0)
+ nvl(crs.ap_balance,0) + nvl(crs.write_off_balance,0)))
AND nvl(:i_to_amount,(nvl(crs.po_balance,0) +
nvl(crs.ap_balance,0) + nvl(crs.write_off_balance,0)))
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 ) )
AND (:l_items_null = ''Y''
OR (:l_items_null = ''N''
AND decode(crs.inventory_item_id, null, null,
(select msi.concatenated_segments
from mtl_system_items_vl msi
where inventory_item_id = crs.inventory_item_id
and rownum <2))
between nvl(:i_from_item, decode(crs.inventory_item_id, null,
null,
(select msi.concatenated_segments
from mtl_system_items_vl msi
where inventory_item_id = crs.inventory_item_id
and rownum <2)))
and nvl(:i_to_item ,decode(crs.inventory_item_id, null, null,
(select msi.concatenated_segments
from mtl_system_items_vl msi
where inventory_item_id = crs.inventory_item_id
and rownum <2)))
))
AND (:l_vendors_null = ''Y''
OR ( :l_vendors_null = ''N''
and pov.vendor_name between nvl( :i_from_vendor, pov.vendor_name )
and nvl( :i_to_vendor, pov.vendor_name )
)
)
ORDER BY decode( :i_sort_by ,
''ITEM'', item,
''AGE IN DAYS'', decode(sign(age_in_days),-1,
chr(0) || translate( to_char(abs(age_in_days), ''000000000999.999''),
''0123456789'', ''9876543210''), to_char(age_in_days , ''000000000999.999'' ) ),
''VENDOR'', pov.vendor_name,
''TOTAL BALANCE'', decode(sign(total_balance),-1,
chr(0) || translate( to_char(abs(total_balance), ''000000000999.999''),
''0123456789'', ''9876543210''),to_char(total_balance, ''000000000999.999'' ) ),
''PO NUMBER'', NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1)) '
USING l_age_days,
l_age_days,
l_age_option,
l_age_div,
l_age_days,
l_age_days,
l_age_option,
l_age_div,
l_age_days,
l_currency,
l_age_option,
l_current_org_id,
l_current_org_id,
i_from_amount,
i_to_amount,
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_items_null,
l_items_null,
i_from_item,
i_to_item,
l_vendors_null,
l_vendors_null,
i_from_vendor,
i_to_vendor,
i_sort_by;
OPEN l_ref_cur FOR SELECT l_count l_count FROM dual ;