[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
ATTR_GROUP_ID,
AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
MULTI_ROW
FROM EGO_FND_DSC_FLX_CTX_EXT AG, pon_auction_headers_all H,
PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
WHERE H.auction_header_id = p_auction_header_id
AND H.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
and POTU.attribute_group_id in (select distinct attr_group_id from PON_AUCTION_HEADERS_EXT_B
where AUCTION_HEADER_ID = H.auction_header_id and attr_group_id = POTU.attribute_group_id)
ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
SELECT EFDFCE.ATTR_ID ,
EFDFCE.APPLICATION_COLUMN_NAME ,
FCU.END_USER_COLUMN_NAME,
fcu.flex_value_set_id,
EFDFCE.data_type
FROM
EGO_FND_DF_COL_USGS_EXT EFDFCE
, FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
AND FCU.DISPLAY_FLAG <> 'H';
L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
|| '", XMLAgg(XMLForest(' ;
L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
XMLAgg(XMLForest(';
L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
|| '", XMLForest(' ;
L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
l_op := ' select XMLConcat(' || l_sql || ') from dual';
SELECT ag.attr_group_id,
efdfce.application_column_name
into l_attr_grp_id, l_col_name
FROM ego_fnd_dsc_flx_ctx_ext ag,
ego_fnd_df_col_usgs_ext efdfce,
fnd_descr_flex_column_usages fcu
WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
and fcu.application_column_name = efdfce.application_column_name
and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
and upper(ag.descriptive_flex_context_code) = upper('addresses')
and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
l_addr_sql := 'select ' || l_col_name || ' as address ' ;
SELECT ag.attr_group_id,
efdfce.application_column_name
into l_attr_grp_id, l_col_name
FROM ego_fnd_dsc_flx_ctx_ext ag,
ego_fnd_df_col_usgs_ext efdfce,
fnd_descr_flex_column_usages fcu
WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
and fcu.application_column_name = efdfce.application_column_name
and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
and upper(ag.descriptive_flex_context_code) = upper('addresses')
and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
ATTR_GROUP_ID,
AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
MULTI_ROW
FROM EGO_FND_DSC_FLX_CTX_EXT AG, pon_auction_item_prices_all L,
PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
WHERE L.auction_header_id = p_auction_header_id
AND L.LINE_NUMBER = p_line_id
AND L.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
and potu.attribute_group_id in (select distinct attr_group_id from PON_AUCTION_ITEM_PRICES_EXT_B
where AUCTION_HEADER_ID = L.auction_header_id and LINE_NUMBER=L.LINE_NUMBER and attr_group_id = potu.attribute_group_id)
ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
SELECT EFDFCE.ATTR_ID ,
EFDFCE.APPLICATION_COLUMN_NAME ,
FCU.END_USER_COLUMN_NAME ,
fcu.flex_value_set_id,
EFDFCE.data_type
FROM
EGO_FND_DF_COL_USGS_EXT EFDFCE
, FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
AND FCU.DISPLAY_FLAG <> 'H';
L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
|| '", XMLAgg(XMLForest(' ;
L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
XMLAgg(XMLForest(';
L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
|| '", XMLForest(' ;
L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
l_op := ' select XMLConcat(' || l_sql || ') from dual';
SELECT ag.attr_group_id,
efdfce.application_column_name
into l_attr_grp_id, l_col_name
FROM ego_fnd_dsc_flx_ctx_ext ag,
ego_fnd_df_col_usgs_ext efdfce,
fnd_descr_flex_column_usages fcu
WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
and fcu.application_column_name = efdfce.application_column_name
and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_PRICES_EXT_ATTRS'
and upper(ag.descriptive_flex_context_code) = upper('SHIP_INFO')
and upper(fcu.end_user_column_name) = upper('hiddShipAddXml');
l_addr_sql := 'select ' || l_col_name || ' as ship_address ' ;
select '' into clob_addr from dual;
SELECT (XMLAgg(XMLElement("ATTRIBUTE" , XMLAttributes(diff.label_message_code as "name",
diff.disp_data_type as "datatype",
diff.sub_entity_name as "sub_entity"),
XMLElement("orig_id", diff.orig_value),
XMLElement("mod_id", diff.mod_value),
XMLElement("orig_value", diff.orig_value_desc),
XMLElement("mod_value", diff.mod_value_desc) ))) finalXML
INTO l_finalXML
FROM
(
SELECT nvl(fnd_message.get_string('PON', c.label_message_code),d.column_name) label_message_code,
c.disp_data_type,
c.sub_entity_name,
d.orig_value,
d.mod_value,
decode(c.disp_data_type, 'N', nvl2(d.orig_value, d.orig_value, 0),
'M', nvl2(d.orig_value, d.orig_value, 0),
nvl(d.orig_value_desc, d.orig_value)) orig_value_desc,
decode(c.disp_data_type, 'N', nvl2(d.mod_value, d.mod_value, 0),
'M', nvl2(d.mod_value, d.mod_value, 0),
nvl(d.mod_value_desc, d.mod_value)) mod_value_desc
FROM pon_entity_differences d,
po_diff_config c
WHERE d.document_type = p_document_type
and d.entity_name = p_entity_name
and d.mod_doc_pk1_val = p_mod_pk1_value
and (p_mod_pk2_value IS NULL or mod_doc_pk2_val = p_mod_pk2_value)
and (p_mod_pk3_value IS NULL or mod_doc_pk3_val = p_mod_pk3_value)
and (p_mod_pk4_value IS NULL or mod_doc_pk4_val = p_mod_pk4_value)
and (p_mod_pk5_value IS NULL or mod_doc_pk5_val = p_mod_pk5_value)
and c.document_type = d.document_type
and c.entity_name = d.entity_name
and c.mod_doc_source_name = d.mod_doc_source_name
and c.column_name = d.column_name
and nvl(c.is_printable_flag, 'N') = 'Y'
and nvl(c.ignore_source_flag, 'N') = 'N'
and not exists(SELECT 1 from po_entity_rollup_gt
WHERE document_type = d.document_type
and entity_name = d.entity_name
and mod_doc_source_name = d.mod_doc_source_name
and column_name = d.column_name
and mod_doc_pk1_val = d.mod_doc_pk1_val
and nvl(mod_doc_pk2_val, '0') = nvl(d.mod_doc_pk2_val, '0')
and nvl(mod_doc_pk3_val, '0') = nvl(d.mod_doc_pk3_val, '0')
and nvl(mod_doc_pk4_val, '0') = nvl(d.mod_doc_pk4_val, '0')
and nvl(mod_doc_pk5_val, '0') = nvl(d.mod_doc_pk5_val, '0')
)
ORDER BY c.display_seq_number
) diff;
l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
SELECT d.mod_doc_source_name,
d.column_name,
nvl(fnd_message.get_string('PON', min(c.label_message_code)),d.column_name) label_message_code,
d.orig_value,
d.mod_value,
decode(min(c.disp_data_type), 'N', nvl2(d.orig_value, d.orig_value, 0),
'M', nvl2(d.orig_value, d.orig_value, 0),
nvl(min(d.orig_value_desc), d.orig_value)) orig_value_desc,
decode(min(c.disp_data_type), 'N', nvl2(d.mod_value, d.mod_value, 0),
'M', nvl2(d.mod_value, d.mod_value, 0),
nvl(min(d.mod_value_desc), d.mod_value)) mod_value_desc,
min(c.disp_data_type) disp_data_type,
min(c.sub_entity_name) sub_entity_name
FROM po_entity_rollup_gt d,
po_diff_config c
WHERE d.document_type = p_document_type
and d.entity_name = p_entity_name
and d.mod_doc_pk1_val = mod_pk1_value
and c.document_type = p_document_type
and c.entity_name = p_entity_name
and c.mod_doc_source_name = d.mod_doc_source_name
and c.column_name = d.column_name
GROUP BY d.mod_doc_source_name, d.column_name, d.orig_value, d.mod_value
ORDER BY min(c.display_seq_number);
SELECT (case when(fromline = toline) then fromline
else fromline || '-' || toline end ) line_display
FROM (
SELECT min(line) fromline, max(line) toline, 'A' orderseq
FROM (SELECT line_num_display line, line_num_display -row_number() over(order by to_number(line_num_display)) rn, group_line_id
FROM pon_auction_item_prices_all
WHERE group_line_id is null
and auction_header_id = mod_pk1_value
and line_number IN (select mod_doc_pk2_val FROM po_entity_rollup_gt d
WHERE d.document_type = p_document_type
and d.entity_name = p_entity_name
and d.mod_doc_pk1_val = mod_pk1_value
and mod_doc_source_name = c_mod_doc_source
and column_name = c_column_name
and nvl(orig_value, '*') = nvl(c_orig_value ,'*')
and nvl(mod_value, '*') = nvl(c_mod_value ,'*')
)
)
GROUP BY group_line_id, rn
UNION ALL
SELECT min(line) fromline, max(line) toline, null orderseq
FROM (SELECT mod.line_num_display line, mod.group_line_id, base.rank - row_number() over(partition by mod.group_line_id order by mod.line_num_display) rn
FROM pon_auction_item_prices_all mod,
(SELECT paip.line_num_display, rank() over(partition by paip.group_line_id order by paip.line_num_display) rank, paip.line_number
FROM pon_auction_item_prices_all paip, pon_auction_headers_all pah
WHERE paip.group_line_id is not null and pah.auction_header_id = mod_pk1_value
and paip.auction_header_id = pah.auction_header_id_prev_amend
) base
WHERE mod.line_number = base.line_number
and mod.group_line_id is not null
and mod.auction_header_id = mod_pk1_value
and mod.line_number IN (SELECT mod_doc_pk2_val FROM po_entity_rollup_gt d
WHERE d.document_type = p_document_type
and d.entity_name = p_entity_name
and d.mod_doc_pk1_val = mod_pk1_value
and mod_doc_source_name = c_mod_doc_source
and column_name = c_column_name
and nvl(orig_value, '*') = nvl(c_orig_value ,'*')
and nvl(mod_value, '*') = nvl(c_mod_value ,'*')
)
)
GROUP BY group_line_id, rn
ORDER By orderseq, fromline
);
SELECT XMLConcat(l_finalXML, XMLElement("ATTRIBUTE" , XMLAttributes(r_change.label_message_code as "name",
r_change.disp_data_type as "datatype",
r_change.sub_entity_name as "sub_entity"),
XMLElement("orig_id", r_change.orig_value),
XMLElement("mod_id", r_change.mod_value),
XMLElement("orig_value", r_change.orig_value_desc),
XMLElement("mod_value", r_change.mod_value_desc),
XMLElement("printable_pks", fnd_message.get_string('PON', 'PON_FED_AMEND_LINE_TITLE_MSG') || ' ' || rollup_pk )))
INTO l_finalXML from dual;
l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
SELECT DISTINCT entity_name, base_doc_source_name, mod_doc_source_name FROM po_diff_config WHERE document_type = p_document_type and nvl(rollup_eligibility_flag, 'N') = 'Y' and nvl(ignore_source_flag, 'N') = 'N';
SELECT DISTINCT base_doc_source_name, mod_doc_source_name FROM po_diff_config WHERE document_type = p_document_type and entity_name = p_entity_name and nvl(rollup_eligibility_flag, 'N') = 'Y' and nvl(ignore_source_flag, 'N') = 'N';
l_insert_stmt CLOB;
l_rfc_stmt := 'SELECT d.column_name, d.orig_value, d.mod_value from pon_entity_differences d, po_diff_config c
where ' || l_where_stmt ||
' and c.column_name = d.column_name
and nvl(c.rollup_eligibility_flag, ''N'') = ''Y''
and c.document_type = '''|| p_document_type ||'''
and c.entity_name = '''|| p_entity_name ||'''
and c.mod_doc_source_name = '''|| p_mod_doc_source ||'''
and nvl(c.ignore_source_flag, ''N'') = ''N''
group by '|| l_groupby_stmt ||' HAVING count(*) > 1 ';
l_insert_stmt := 'INSERT INTO po_entity_rollup_gt (
document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by)
SELECT document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by
from pon_entity_differences d
WHERE '|| l_where_stmt ||' and column_name = :1 and nvl(orig_value, ''*'') = nvl(:2, ''*'') and nvl(mod_value, ''*'') = nvl(:3, ''*'') ';
execute immediate l_insert_stmt USING l_column_name, l_orig_value, l_mod_value;