[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.ADJUSTMENT_PERIOD_FLAG = 'N'
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 meaning
into l_order_by
FROM mfg_lookups
WHERE LOOKUP_TYPE = 'CST_ACR_REPORT_SORT'
AND LOOKUP_CODE = p_orderby;
SELECT meaning
INTO l_accrued_receipts
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = p_accrued_receipts;
SELECT meaning
INTO l_inc_online_accruals
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = p_inc_online_accruals;
SELECT meaning
INTO l_inc_closed_pos
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = p_inc_closed_pos;
'SELECT gsb.name company_name,
:p_title report_title,
SYSDATE report_date,
:l_accrued_receipts accrued_receipt,
:l_inc_online_accruals include_online_accruals,
:l_inc_closed_pos 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,
:l_qty_precision qty_precision
FROM gl_sets_of_books gsb
WHERE gsb.set_of_books_id = :l_sob_id'
USING
p_title,
l_accrued_receipts,
l_inc_online_accruals,
l_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_qty_precision,
l_sob_id;
'SELECT NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1) po_number,--Changed as a part of CLM
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,
nvl(POL.LINE_NUM_DISPLAY, to_char(POL.LINE_NUM)) line_num,--Changed as a part of CLM
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,
round(nvl(cpea.quantity_received, 0), :p_qty_precision) quantity_received,
round(nvl(cpea.quantity_billed, 0), :p_qty_precision) quantity_billed,
round(nvl(cpea.accrual_quantity, 0), :p_qty_precision) quantity_accrued,
ROUND(cpea.unit_price,
NVL(fnc2.extended_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.extended_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,
nvl(fnc2.extended_precision,2) PO_PRECISION,
nvl(fnc1.extended_precision,2) PO_FUNC_PRECISION,
nvl(fnc1.precision,2) ACCR_PRECISION
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(:p_orderby,
1, mca.concatenated_segments,
2, pov.vendor_name),
NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1),
nvl(POL.LINE_NUM_DISPLAY, to_char(POL.LINE_NUM)),
poll.shipment_num,
pod.distribution_num'
USING p_qty_precision, p_qty_precision, p_qty_precision, l_sob_id, p_orderby
;