[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT set_of_books_id
INTO l_sob_id
FROM financials_system_parameters;
SELECT TO_NUMBER(org_information2)
INTO l_legal_entity
FROM hr_organization_information
WHERE organization_id = MO_GLOBAL.GET_CURRENT_ORG_ID
AND org_information_context = 'Operating Unit Information';
SELECT INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE (gps.end_date,
l_legal_entity)
INTO l_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = l_application_id
AND gps.set_of_books_id = l_sob_id
AND gps.period_name = NVL(p_period_name,
(SELECT gp.period_name
FROM gl_periods gp,
gl_sets_of_books sob
WHERE sob.set_of_books_id = l_sob_id
AND sob.period_set_name = gp.period_set_name
AND sob.accounted_period_type = gp.period_type
AND gp.start_date <= TRUNC(SYSDATE)
AND gp.end_date >= TRUNC(SYSDATE))
);
SELECT COUNT('X')
INTO l_row_count
FROM CST_PER_END_ACCRUALS_TEMP
WHERE ROWNUM = 1;
'SELECT gsb.name company_name,
:p_title report_title,
SYSDATE report_date,
DECODE(:p_accrued_receipts,
''Y'', ''Yes'',
''N'', ''No'') accrued_receipt,
DECODE(:p_inc_online_accruals,
''Y'', ''Yes'',
''N'', ''No'') include_online_accruals,
DECODE(:p_inc_closed_pos,
''Y'', ''Yes'',
''N'', ''No'') include_closed_pos,
:p_category_from category_from,
:p_category_to category_to,
:p_min_accrual_amount minimum_accrual_amount,
:p_period_name period_name,
:p_vendor_from vendor_from,
:p_vendor_to vendor_to,
:l_order_by order_by,
:l_row_count row_count
FROM gl_sets_of_books gsb
WHERE gsb.set_of_books_id = :l_sob_id'
USING
p_title,
p_accrued_receipts,
p_inc_online_accruals,
p_inc_closed_pos,
p_category_from,
p_category_to,
p_min_accrual_amount,
p_period_name,
p_vendor_from,
p_vendor_to,
l_order_by,
l_row_count,
l_sob_id;
'SELECT poh.segment1 po_number,
porl.release_num po_release_number,
poh.po_header_id po_header_id,
pol.po_line_id po_line_id,
cpea.shipment_id po_shipment_id,
cpea.distribution_id po_distribution_id,
plt.line_type line_type,
pol.line_num line_num,
msi.concatenated_segments item_name,
mca.concatenated_segments category,
pol.item_description item_description,
pov.vendor_name vendor_name,
fnc2.currency_code accrual_currency_code,
poll.shipment_num shipment_number,
poll.unit_meas_lookup_code uom_code,
pod.distribution_num distribution_num,
cpea.quantity_received quantity_received,
cpea.quantity_billed quantity_billed,
cpea.accrual_quantity quantity_accrued,
ROUND(cpea.unit_price,
NVL(fnc2.precision, 2)) po_unit_price,
cpea.currency_code po_currency_code,
ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.unit_price * cpea.currency_conversion_rate,
(cpea.unit_price / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2))
func_unit_price,
gcc1.concatenated_segments charge_account,
gcc2.concatenated_segments accrual_account,
cpea.accrual_amount accrual_amount,
ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.accrual_amount * cpea.currency_conversion_rate,
(cpea.accrual_amount / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2))
func_accrual_amount
FROM cst_per_end_accruals_temp cpea,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
po_vendors pov,
po_line_types plt,
po_releases_all porl,
mtl_system_items_kfv msi,
fnd_currencies fnc1,
fnd_currencies fnc2,
mtl_categories_kfv mca,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_sets_of_books sob
WHERE pod.po_distribution_id = cpea.distribution_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id
AND pol.line_type_id = plt.line_type_id
AND porl.po_release_id (+) = poll.po_release_id
AND poh.vendor_id = pov.vendor_id
AND msi.inventory_item_id (+) = pol.item_id
AND (msi.organization_id IS NULL
OR
(msi.organization_id = poll.ship_to_organization_id AND msi.organization_id IS NOT NULL))
AND fnc1.currency_code = cpea.currency_code
AND fnc2.currency_code = sob.currency_code
AND cpea.category_id = mca.category_id
AND gcc1.code_combination_id = pod.code_combination_id
AND gcc2.code_combination_id = pod.accrual_account_id
AND sob.set_of_books_id = :l_sob_id
ORDER BY DECODE(:l_order_by,
''Category'', mca.concatenated_segments,
''Vendor'', pov.vendor_name),
poh.segment1,
pol.line_num,
poll.shipment_num,
pod.distribution_num'
USING l_sob_id, l_order_by
;