[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,x_conv_rate_selected OUT NOCOPY VARCHAR2
,x_err_desc OUT NOCOPY VARCHAR2
,x_err_msg OUT NOCOPY VARCHAR2
,x_parameter_valid OUT NOCOPY BOOLEAN
) AS
l_primary_currency VARCHAR2(50);
x_conv_rate_selected := '1';
x_conv_rate_selected := '0';
IF to_number(x_conv_rate_selected) < 0 THEN
x_conv_rate_selected := 'NULL';
MESSAGE => l_primary_currency ||', '||l_conv_type ||', '|| x_conv_rate_selected);
SELECT current_date_id INTO x_curr_date FROM bis_system_date;
SELECT attribute_code FROM AK_REGION_ITEMS
WHERE REGION_CODE = pRegionName
AND (ATTRIBUTE3 LIKE pBilMeasureTxt OR ATTRIBUTE1 IN
(pGrandTotalTxt,pDrillTxt,pNaTxt))
AND NVL(ATTRIBUTE3, pNVTxt) NOT LIKE pWCTxt
ORDER BY DISPLAY_SEQUENCE;
temp_sql:= 'SELECT null viewby';
select trunc(max(period_to)) into x_snapshot_date
from bis_refresh_log
where object_name = 'BIL_BI_PIPELINE_F'
and status = 'SUCCESS'
and period_to <= l_as_of_date
and period_to >= l_check_date;
x_conv_rate_selected OUT NOCOPY VARCHAR2,
x_curr_page_time_id OUT NOCOPY NUMBER,
x_page_period_type OUT NOCOPY VARCHAR2,
x_parameter_valid OUT NOCOPY BOOLEAN,
x_period_type OUT NOCOPY VARCHAR2,
x_prev_page_time_id OUT NOCOPY NUMBER,
x_prior_as_of_date OUT NOCOPY DATE,
x_prodcat_id OUT NOCOPY VARCHAR2,
x_record_type_id OUT NOCOPY NUMBER,
x_resource_id OUT NOCOPY VARCHAR2,
x_sg_id OUT NOCOPY VARCHAR2,
x_parent_sg_id OUT NOCOPY NUMBER,
x_viewby OUT NOCOPY VARCHAR2)
AS
l_currency VARCHAR2(20);
l_conv_rate_selected VARCHAR2(100);
,x_conv_rate_selected => l_conv_rate_selected
,x_err_desc => l_err_desc
,x_err_msg => l_err_msg
,x_parameter_valid => l_parameter_valid);
x_conv_rate_selected := l_conv_rate_selected;
' p_conv_rate_selected : '||x_conv_rate_selected;
select parent_group_id
into l_parent_sls_grp_id
from jtf_rs_groups_denorm
where group_id = p_sales_grp_id
and immediate_parent_flag='Y'
and latest_relationship_flag='Y';
/* SELECT ENT_YEAR_ID
INTO x_curr_startprd_id
FROM fii_time_ent_year
WHERE x_curr_start_date BETWEEN start_date AND end_date;
SELECT ent_period_id
INTO x_curr_startprd_id
FROM fii_time_ent_period
WHERE x_curr_start_date BETWEEN start_date AND end_date;
SELECT ent_period_id
INTO x_prev_startprd_id
FROM fii_time_ent_period
WHERE x_prev_start_date BETWEEN start_date AND end_date;
SELECT ent_qtr_id
INTO x_curr_startprd_id
FROM fii_time_ent_qtr
WHERE x_curr_start_date BETWEEN start_date AND end_date;
SELECT ent_qtr_id
INTO x_prev_startprd_id
FROM fii_time_ent_qtr
WHERE x_prev_start_date BETWEEN start_date AND end_date;
SELECT DESCRIPTION || ' ('|| MEANING ||')'
INTO l_unassigned_value
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ITEM_CATG' AND
LOOKUP_CODE = '-1' AND
LANGUAGE = USERENV('LANG');
SELECT a.leaf_node_flag INTO leaf_node
FROM ENI_DENORM_HIERARCHIES A,
MTL_DEFAULT_CATEGORY_SETS B
WHERE B.FUNCTIONAL_AREA_ID = 11
AND A.OBJECT_TYPE = 'CATEGORY_SET'
AND A.OBJECT_ID = B.CATEGORY_SET_ID
AND A.DBI_FLAG = 'Y'
AND a.parent_id = p_prodcat_id
AND a.parent_id = a.child_id;
SELECT Meaning INTO l_cat_assign
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = p_lookuptype
AND LOOKUP_CODE = p_lookupcode
AND LANGUAGE = USERENV('LANG');
SELECT l_prev_date-w.start_date, w.sequence, p.year445_id
INTO l_timespan, l_sequence, l_year
FROM fii_time_week w, fii_time_p445 p
WHERE w.period445_id=p.period445_id
AND l_prev_date BETWEEN w.start_date AND w.end_date;
SELECT MAX(w.sequence)
INTO l_sequence
FROM fii_time_week w, fii_time_p445 p
WHERE w.period445_id=p.period445_id
AND p.year445_id=l_year;
SELECT w.week_id, w.start_date+l_timespan
INTO l_prior_prior_time_id, l_prior_prior_date
FROM fii_time_week w, fii_time_p445 p
WHERE w.period445_id=p.period445_id
AND w.sequence=l_sequence
AND p.year445_id=l_year;
SELECT p.end_date-l_prev_date, p.sequence, p.ent_year_id
INTO l_timespan, l_sequence, l_year
FROM fii_time_ent_period p
WHERE l_prev_date BETWEEN p.start_date AND p.end_date;
SELECT MAX(p.sequence)
INTO l_sequence
FROM fii_time_ent_period p
WHERE p.ent_year_id=l_year;
SELECT ent_period_id, GREATEST(p.start_date, p.end_date-l_timespan)
INTO l_prior_prior_time_id, l_prior_prior_date
FROM fii_time_ent_period p
WHERE p.sequence=l_sequence
AND p.ent_year_id=l_year;
SELECT end_date-l_prev_date, sequence, ent_year_id
INTO l_timespan, l_sequence, l_year
FROM fii_time_ent_qtr
WHERE l_prev_date BETWEEN start_date AND end_date;
SELECT MAX(sequence)
INTO l_sequence
FROM fii_time_ent_qtr
WHERE ent_year_id=l_year;
SELECT ent_qtr_id, GREATEST(start_date, end_date-l_timespan)
INTO l_prior_prior_time_id, l_prior_prior_date
FROM fii_time_ent_qtr
WHERE sequence=l_sequence
AND ent_year_id=l_year;
SELECT end_date-l_prev_date, sequence
INTO l_timespan, l_year
FROM fii_time_ent_year
WHERE l_prev_date BETWEEN start_date AND end_date;
SELECT ent_year_id, GREATEST(start_date, end_date-l_timespan)
INTO l_prior_prior_time_id, l_prior_prior_date
FROM fii_time_ent_year
WHERE sequence=l_year-1;
IF (l_resource_id IS NOT NULL) -- Some SalesRep is selected in the salesgroup LOV
THEN
l_drill_link := 'pFunctionName=BIL_BI_OPPTY_LINE_DETAIL_R&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&';
SELECT to_date(attribute1, 'dd/mm/yyyy'), MAX(to_date(attribute2, 'dd/mm/yyyy'))
INTO l_coll_st_date,l_coll_end_date
FROM bis_refresh_log a
WHERE
a.last_update_date = (
SELECT MAX(b.last_update_date) FROM bis_refresh_log b
WHERE b.object_name = 'BIL_BI_PIPEC_F'
AND status = 'SUCCESS'
)
AND a.object_name = 'BIL_BI_PIPEC_F'
AND status = 'SUCCESS'
group by to_date(attribute1, 'dd/mm/yyyy');
SELECT week_end_date, ent_period_end_date, ent_qtr_end_date, ent_year_end_date
INTO l_week_end_date, l_period_end_date, l_qtr_end_date, l_year_end_date
FROM FII_TIME_DAY
WHERE report_date = l_as_of_date;
SELECT to_date(attribute1, 'dd/mm/yyyy'), MAX(to_date(attribute2, 'dd/mm/yyyy'))
INTO l_coll_st_date,l_coll_end_date
FROM bis_refresh_log a
WHERE
a.last_update_date = (
SELECT MAX(b.last_update_date) FROM bis_refresh_log b
WHERE b.object_name = 'BIL_BI_PIPEC_F'
AND status = 'SUCCESS'
)
AND a.object_name = 'BIL_BI_PIPEC_F'
AND status = 'SUCCESS'
group by to_date(attribute1, 'dd/mm/yyyy');
SELECT MIN(date2) INTO l_tmp_date FROM bil_bi_rpt_tmp1;
SELECT MIN(date2) INTO l_tmp_date FROM bil_bi_rpt_tmp1;