[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_stmt VARCHAR2(3000);
l_insert_stmt := ' INSERT INTO ASO_BI_RPT_TMP1(ASO_ATTRIBUTE1,VIEWBY,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5
,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,ASO_URL1) ';
EXECUTE IMMEDIATE l_insert_stmt || p_query
USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
,p_fdcp_date , p_fdpp_date
,p_sg_id_num
,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date , p_priorasof_date , p_record_type_id
,p_sg_id_num
,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
,p_fdcp_date_j , p_fdpp_date_j
,p_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmt || p_query
USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
,p_fdcp_date , p_fdpp_date
,p_sg_id_num , p_sr_id_num
,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date , p_priorasof_date , p_record_type_id
,p_sg_id_num , p_sr_id_num
,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
,p_fdcp_date_j , p_fdpp_date_j
,p_sg_id_num , p_sr_id_num;
EXECUTE IMMEDIATE l_insert_stmt || p_query
USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
,p_fdcp_date , p_fdpp_date
,p_sg_id_num
,p_product_id
,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date , p_priorasof_date , p_record_type_id
,p_sg_id_num
,p_product_id
,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
,p_fdcp_date_j , p_fdpp_date_j
,p_product_id
,p_sg_id_num ;
EXECUTE IMMEDIATE l_insert_stmt || p_query
USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
,p_fdcp_date , p_fdpp_date
,p_sg_id_num , p_sr_id_num
,p_product_id
,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
,p_asof_date , p_priorasof_date , p_record_type_id
,p_sg_id_num , p_sr_id_num
,p_product_id
,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
,p_fdcp_date_j , p_fdpp_date_j
,p_product_id
,p_sg_id_num , p_sr_id_num;
l_query := ' SELECT PCD.ID
,PCD.Value
,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
,DECODE(PCD.leaf_node_flag,''Y''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
FROM (
SELECT Inn0.Category_id '|| l_summ ||
' FROM
(SELECT Sumry.Category_id '|| l_main_clause0 ||
'FROM ASO_BI_QLIN_PC_MV Sumry
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause0 || l_res_clause ||
'AND Sumry.Top_node_flag = ''Y''
AND Sumry.Category_flag = ''Y'' ';
(SELECT Sumry.Category_id '|| l_main_clause1 ||
'FROM ASO_BI_QLIN_PC_MV Sumry
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause1 || l_res_clause ||
'AND Sumry.Top_node_flag = ''Y''
AND Sumry.Category_flag = ''Y'')';
SELECT Sumry.Category_id '|| l_main_clause2 ||
'FROM ASO_BI_QLIN_PC_MV Sumry
WHERE Sumry.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
AND Sumry.Period_Type_Id = 1
AND Sumry.Top_node_flag = ''Y''
AND Sumry.Category_flag = ''Y'' '|| l_res_clause;
INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1)
SELECT PCD.Imm_child_id
FROM ENI_DENORM_HIERARCHIES PCD
,MTL_DEFAULT_CATEGORY_SETS MDFT
WHERE PCD.Parent_id = p_product_cat
AND PCD.Imm_child_id = PCD.Child_id
AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
AND MDFT.Functional_area_id = 11
AND MDFT.Category_set_id = PCD.Object_id
AND PCD.Object_type = 'CATEGORY_SET'
AND PCD.Dbi_flag = 'Y';
l_query := 'SELECT PCD.Id
,PCD.Value
,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
,DECODE(PCD.Leaf_node_flag,''Y''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
FROM
(SELECT Inn0.Category_id '|| l_summ ||
' FROM
(SELECT /*+ Ordered */ Sumry.Category_id '|| l_main_clause0 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause0 || l_res_clause ||
'AND Sumry.Category_id = TMP.ASO_VALUE1
AND Sumry.Category_flag = ''Y'' ';
(SELECT /*+ Ordered */ Sumry.Category_id '|| l_main_clause1 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause1 || l_res_clause ||
'AND Sumry.Category_id = TMP.ASO_VALUE1
AND Sumry.Category_flag = ''Y'')';
SELECT /*+ Leading(TMP) */ Sumry.Category_id '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV Sumry
,ASO_BI_RPT_TMP2 TMP
WHERE Sumry.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
AND Sumry.Period_Type_Id = 1
AND Sumry.Category_id = TMP.ASO_VALUE1
AND Sumry.Category_flag = ''Y'' '|| l_res_clause;
l_query := ' SELECT PCD.Id
,PCD.Value
,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
,DECODE(PCD.Leaf_node_flag,''Y''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
FROM
(SELECT Inn0.Category_id '|| l_summ ||
' FROM
(SELECT Sumry.Category_id '|| l_main_clause0 ||
' FROM ASO_BI_QLIN_PC_MV Sumry
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause0 || l_res_clause ||
' AND Sumry.Master_id = :p_product_id
AND Sumry.Category_flag = ''N'' ';
(SELECT Sumry.Category_id '|| l_main_clause1 ||
' FROM ASO_BI_QLIN_PC_MV Sumry
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause1 || l_res_clause ||
' AND Sumry.Master_id = :p_product_id
AND Sumry.Category_flag = ''N'')';
SELECT Sumry.Category_id '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV Sumry
WHERE Sumry.Time_id IN (:p_fdcp_date_j,:p_fdpp_date_j)
AND Sumry.Period_Type_Id = 1
AND Sumry.Master_id = :p_product_id
AND Sumry.Category_flag = ''N'' '|| l_res_clause;
INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1,ASO_VALUE2)
SELECT PCD.Child_id,PCD.Imm_child_id
FROM ENI_DENORM_HIERARCHIES PCD
,MTL_DEFAULT_CATEGORY_SETS MDFT
WHERE PCD.Parent_id = p_product_cat
AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
AND MDFT.Functional_area_id = 11
AND MDFT.Category_set_id = PCD.Object_id
AND PCD.Object_type = 'CATEGORY_SET'
AND PCD.Dbi_flag = 'Y';
l_query := ' SELECT PCD.Id
,PCD.Value
,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
,DECODE(PCD.Leaf_node_flag,''Y''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
FROM
(SELECT Inn0.Id '|| l_summ ||
' FROM
(SELECT /*+ Ordered */ TMP.ASO_VALUE2 Id '|| l_main_clause0 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause0 || l_res_clause ||
' AND SUMRY.Category_id = TMP.ASO_VALUE1
AND SUMRY.Master_id = :p_product_id
AND SUMRY.Category_flag = ''N''';
(SELECT /*+ Ordered */ TMP.ASO_VALUE2 Id '|| l_main_clause1 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause1 || l_res_clause ||
' AND SUMRY.Category_id = TMP.ASO_VALUE1
AND SUMRY.Master_id = :p_product_id
AND SUMRY.Category_flag = ''N'')';
SELECT /*+ Leading(TMP) */ TMP.ASO_VALUE2 '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,ASO_BI_RPT_TMP2 TMP
WHERE SUMRY.Time_id IN (:p_fdcp_date_j,:p_fdpp_date_j)
AND SUMRY.Period_Type_Id = 1
AND SUMRY.Category_id = TMP.ASO_VALUE1
AND SUMRY.Master_id = :p_product_id
AND SUMRY.Category_flag = ''N'' '|| l_res_clause ;
l_query := ' SELECT PCD.Id,PCD.Value,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
FROM
(SELECT Inn0.Master_id '|| l_summ ||
' FROM
(SELECT Sumry.Master_id '|| l_main_clause0 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause0 || l_res_clause ||
' AND SUMRY.Category_flag = ''N'' ';
(SELECT Sumry.Master_id '|| l_main_clause1 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause1 || l_res_clause ||
' AND SUMRY.Category_flag = ''N'')';
SELECT Sumry.Master_id '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
WHERE SUMRY.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
AND SUMRY.Period_Type_Id = 1
AND SUMRY.Category_flag = ''N'' '|| l_res_clause ;
INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1)
SELECT PCD.Child_id
FROM ENI_DENORM_HIERARCHIES PCD
,MTL_DEFAULT_CATEGORY_SETS MDFT
WHERE PCD.Parent_id = p_product_cat
AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
AND MDFT.Functional_area_id = 11
AND MDFT.Category_set_id = PCD.Object_id
AND PCD.Object_type = 'CATEGORY_SET'
AND PCD.Dbi_flag = 'Y';
l_query := ' SELECT PCD.ID,PCD.VALUE,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
FROM
(SELECT Inn0.Master_id '|| l_summ ||
' FROM
(SELECT /*+ Ordered */ SUMRY.Master_Id '|| l_main_clause0 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause0 || l_res_clause ||
' AND SUMRY.Category_flag = ''N''
AND SUMRY.Category_id = TMP.ASO_VALUE1 ';
(SELECT /*+ Ordered */ SUMRY.Master_Id '|| l_main_clause1 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause1 || l_res_clause ||
' AND SUMRY.Category_flag = ''N''
AND SUMRY.Category_id = TMP.ASO_VALUE1)';
SELECT /*+ Leading(TMP) */ SUMRY.Master_Id '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,ASO_BI_RPT_TMP2 TMP
WHERE SUMRY.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
AND SUMRY.Period_Type_Id = 1
AND SUMRY.Category_flag = ''N''
AND SUMRY.Category_id = TMP.ASO_VALUE1 '|| l_res_clause ;
l_query := ' SELECT PCD.Id,PCD.Value,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
FROM
(SELECT Inn0.Master_id '|| l_summ ||
' FROM
(SELECT Sumry.Master_id '|| l_main_clause0 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause0 || l_res_clause ||
' AND SUMRY.Category_flag = ''N''
AND SUMRY.Master_id = :p_product_id ';
(SELECT Sumry.Master_id '|| l_main_clause1 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,FII_TIME_RPT_STRUCT_V CAL
WHERE '|| l_time_clause1 || l_res_clause ||
' AND SUMRY.Category_flag = ''N''
AND SUMRY.Master_id = :p_product_id )';
SELECT Sumry.Master_id '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
WHERE SUMRY.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
AND SUMRY.Period_Type_Id = 1
AND SUMRY.Category_flag = ''N''
AND SUMRY.Master_id = :p_product_id '|| l_res_clause ;
INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1)
SELECT PCD.Child_id
FROM ENI_DENORM_HIERARCHIES PCD
,MTL_DEFAULT_CATEGORY_SETS MDFT
WHERE PCD.Parent_id = p_product_cat
AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
AND MDFT.Functional_area_id = 11
AND MDFT.Category_set_id = PCD.Object_id
AND PCD.Object_type = 'CATEGORY_SET'
AND PCD.Dbi_flag = 'Y';
l_query := ' SELECT PCD.Id,PCD.Value,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
FROM
(SELECT Inn0.Master_id '|| l_summ ||
' FROM
(SELECT /*+ Ordered */ Sumry.Master_id '|| l_main_clause0 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause0 || l_res_clause ||
' AND SUMRY.Category_flag = ''N''
AND SUMRY.Master_id = :p_product_id
AND SUMRY.Category_id = TMP.ASO_VALUE1 ';
(SELECT /*+ Ordered */ Sumry.Master_id '|| l_main_clause1 ||
' FROM FII_TIME_RPT_STRUCT_V CAL
,ASO_BI_RPT_TMP2 TMP
,ASO_BI_QLIN_PC_MV SUMRY
WHERE '|| l_time_clause1 || l_res_clause ||
' AND SUMRY.Category_flag = ''N''
AND SUMRY.Master_id = :p_product_id
AND SUMRY.Category_id = TMP.ASO_VALUE1) ';
SELECT /*+ Leading(TMP) */ Sumry.Master_id '|| l_main_clause2 ||
' FROM ASO_BI_QLIN_PC_MV SUMRY
,ASO_BI_RPT_TMP2 TMP
WHERE SUMRY.Time_id IN (:p_fdcp_date_j,:p_fdcp_date_j)
AND SUMRY.Period_Type_Id = 1
AND SUMRY.Category_flag = ''N''
AND SUMRY.Master_id = :l_product_id
AND SUMRY.Category_id = TMP.ASO_VALUE1 '|| l_res_clause ;