[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 = 'PO_HEADER_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 = 'PO_HEADER_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,
PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
WHERE 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 po_headers_all_ext_b
where po_header_id = p_headerId
and draft_id = p_draft_id and uda_template_id = potu.template_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.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,
PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
WHERE 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 po_lines_all_ext_b
where po_line_id = p_po_line_id
and draft_id = p_draft_Id
and attr_group_id = potu.attribute_group_id and uda_template_id = potu.template_id
and pk1_value IS NULL)
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.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,
PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
WHERE 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 po_line_locations_all_ext_b
where line_location_id = p_line_location_id
and draft_id = p_draft_Id and uda_template_id = potu.template_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 = 'PO_SHIPMENTS_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;
l_sql_stmt := 'select XMLAgg(XMLElement("ATTRIBUTE" , XMLATTRIBUTES( nvl(fnd_message.get_string(''PO'', c.label_message_code), d.column_name) as "name",
c.disp_data_type as "datatype",
c.sub_entity_name as "sub_entity" ),
XMLELement("orig_id", d.orig_value),
XMLELement("mod_id", d.mod_value),
XMLELement("orig_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)) ),
XMLELement("mod_value", 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)) ))
ORDER BY c.display_seq_number)
FROM (SELECT 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
FROM po_entity_differences WHERE '|| l_where_stmt || '
MINUS
SELECT 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
FROM po_entity_rollup_gt WHERE '|| l_where_stmt || '
) d,
po_diff_config c
WHERE 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
and nvl(c.is_printable_flag, ''N'') = ''Y''
and nvl(c.ignore_source_flag,''N'') = ''N'' ';
l_rollup_stmt := 'SELECT d.mod_doc_source_name,
d.column_name,
nvl(fnd_message.get_string(''PO'', min(c.label_message_code)), d.column_name),
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)),
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)),
min(c.disp_data_type),
min(c.sub_entity_name)
FROM po_entity_rollup_gt d,
po_diff_config c
WHERE ' || l_where_stmt
|| ' 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 ' || l_groupby_stmt ||
' ORDER BY min(c.display_seq_number)';
l_linecont_stmt := 'SELECT min(line) fromline, max(line) toline, count(*) count, ''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 po_lines_draft_all
WHERE group_line_id is null
and po_header_id = ' || mod_pk1_value || '
and draft_id = ' || mod_pk2_value || '
and po_line_id in (select mod_doc_pk3_val FROM po_entity_rollup_gt d
WHERE ' || l_where_stmt || ' and mod_doc_source_name = :1 and column_name = :2
and nvl(orig_value, 1) = nvl(:3 ,1) and nvl(mod_value, 1) = nvl(:4 ,1) ))
GROUP BY group_line_id, rn
UNION ALL
SELECT min(line) fromline, max(line) toline, count(*) count, 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 po_lines_draft_all mod,
(select line_num_display, rank() over(partition by group_line_id order by line_num_display) rank, po_line_id from po_lines_all
where group_line_id is not null and po_header_id = ' || mod_pk1_value || ') base
WHERE mod.po_line_id = base.po_line_id
and mod.group_line_id is not null
and mod.po_header_id = ' || mod_pk1_value || '
and mod.draft_id = ' || mod_pk2_value || '
and mod.po_line_id in (select mod_doc_pk3_val FROM po_entity_rollup_gt d
WHERE ' || l_where_stmt || ' and mod_doc_source_name = :5 and column_name = :6
and nvl(orig_value, 1) = nvl(:7 ,1) and nvl(mod_value, 1) = nvl(:8 ,1) ))
GROUP BY group_line_id, rn
order by orderseq, fromline';
l_sql_stmt := '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
''' || l_datatype || ''' as "datatype",
''' || l_subentity || ''' as "sub_entity"),
XMLELement("orig_id", ''' || l_origvalue ||'''),
XMLELement("mod_id", ''' || l_modvalue ||'''),
XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
XMLELement("mod_value", ''' || l_modvalue_desc || '''),
XMLELement("printable_pks", ''' || l_print_string ||'''))
FROM dual) ';
l_sql_stmt := l_sql_stmt || ',' || '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
''' || l_datatype || ''' as "datatype",
''' || l_subentity || ''' as "sub_entity"),
XMLELement("orig_id", ''' || l_origvalue ||'''),
XMLELement("mod_id", ''' || l_modvalue ||'''),
XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
XMLELement("mod_value", ''' || l_modvalue_desc || '''),
XMLELement("printable_pks", ''' || l_print_string ||'''))
FROM dual) ';
l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
l_rollup_stmt := 'SELECT d.mod_doc_source_name,
d.column_name,
nvl(fnd_message.get_string(''PO'', min(c.label_message_code)), d.column_name),
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)),
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)),
min(c.disp_data_type),
min(c.sub_entity_name)
FROM po_entity_rollup_gt d,
po_diff_config c
WHERE ' || l_where_stmt
|| ' 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 ' || l_groupby_stmt ||
' ORDER BY min(c.display_seq_number)';
l_shipno_stmt := 'SELECT pll.shipment_num
FROM po_entity_rollup_gt d, po_line_locations_all pll
WHERE ' || l_where_stmt
|| ' and mod_doc_source_name = :1
and column_name = :2
and nvl(orig_value, 1) = nvl(:3 ,1)
and nvl(mod_value, 1) = nvl(:4 ,1)
and d.mod_doc_pk4_val = pll.line_location_id
GROUP BY pll.shipment_num
ORDER BY pll.shipment_num';
l_linecont_stmt := 'SELECT min(line) fromline, max(line) toline, count(*) count, ''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 po_lines_draft_all
WHERE group_line_id is null
and po_header_id = ' || mod_pk1_value || '
and draft_id = ' || mod_pk2_value || '
and po_line_id in ( SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll
WHERE ' || l_where_stmt || ' and mod_doc_source_name = :1
and column_name = :2 and nvl(orig_value, 1) = nvl(:3 ,1)
and nvl(mod_value, 1) = nvl(:4 ,1) and d.mod_doc_pk4_val = pll.line_location_id and pll.shipment_num = :5 ) )
GROUP BY group_line_id, rn
UNION ALL
SELECT min(line) fromline, max(line) toline, count(*) count, 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 po_lines_draft_all mod,
(select line_num_display, rank() over(partition by group_line_id order by line_num_display) rank, po_line_id from po_lines_all
where group_line_id is not null and po_header_id = ' || mod_pk1_value || ') base
WHERE mod.po_line_id = base.po_line_id
and mod.group_line_id is not null
and mod.po_header_id = ' || mod_pk1_value || '
and mod.draft_id = ' || mod_pk2_value || '
and mod.po_line_id in (SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll
WHERE ' || l_where_stmt || ' and mod_doc_source_name = :6
and column_name = :7 and nvl(orig_value, 1) = nvl(:8 ,1)
and nvl(mod_value, 1) = nvl(:9 ,1) and d.mod_doc_pk4_val = pll.line_location_id and pll.shipment_num = :10 ))
GROUP BY group_line_id, rn
order by orderseq, fromline';
l_sql_stmt := '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
''' || l_datatype || ''' as "datatype",
''' || l_subentity || ''' as "sub_entity"),
XMLELement("orig_id", ''' || l_origvalue ||'''),
XMLELement("mod_id", ''' || l_modvalue ||'''),
XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
XMLELement("mod_value", ''' || l_modvalue_desc || '''),
XMLELement("printable_pks", ''' || l_print_string ||'''))
FROM dual) ';
l_sql_stmt := l_sql_stmt || ',' || '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
''' || l_datatype || ''' as "datatype",
''' || l_subentity || ''' as "sub_entity"),
XMLELement("orig_id", ''' || l_origvalue ||'''),
XMLELement("mod_id", ''' || l_modvalue ||'''),
XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
XMLELement("mod_value", ''' || l_modvalue_desc || '''),
XMLELement("printable_pks", ''' || l_print_string ||'''))
FROM dual) ';
l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
l_rollup_stmt := 'SELECT d.mod_doc_source_name,
d.column_name,
nvl(fnd_message.get_string(''PO'', min(c.label_message_code)), d.column_name),
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)),
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)),
min(c.disp_data_type),
min(c.sub_entity_name)
FROM po_entity_rollup_gt d,
po_diff_config c
WHERE ' || l_where_stmt
|| ' 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 ' || l_groupby_stmt ||
' ORDER BY min(c.display_seq_number)';
l_shipno_stmt := 'SELECT pll.shipment_num,
pd.distribution_num
FROM po_entity_rollup_gt d,
po_line_locations_all pll,
po_distributions_all pd
WHERE ' || l_where_stmt
|| ' and mod_doc_source_name = :1
and column_name = :2
and nvl(orig_value, 1) = nvl(:3 ,1)
and nvl(mod_value, 1) = nvl(:4 ,1)
and d.mod_doc_pk4_val = pll.line_location_id
and d.mod_doc_pk5_val = pd.po_distribution_id
GROUP BY pll.shipment_num,
pd.distribution_num
ORDER BY pll.shipment_num,
pd.distribution_num ';
l_linecont_stmt := 'SELECT min(line) fromline, max(line) toline, count(*) count, ''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 po_lines_draft_all
WHERE group_line_id is null
and po_header_id = ' || mod_pk1_value || '
and draft_id = ' || mod_pk2_value || '
and po_line_id in ( SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll, po_distributions_all pd
WHERE ' || l_where_stmt || ' and mod_doc_source_name = :1 and column_name = :2
and nvl(orig_value, 1) = nvl(:3 ,1) and nvl(mod_value, 1) = nvl(:4 ,1)
and d.mod_doc_pk4_val = pll.line_location_id and d.mod_doc_pk5_val = pd.po_distribution_id
and pll.shipment_num = :5 and pd.distribution_num = :6 ))
GROUP BY group_line_id, rn
UNION ALL
SELECT min(line) fromline, max(line) toline, count(*) count, 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 po_lines_draft_all mod,
(select line_num_display, rank() over(partition by group_line_id order by line_num_display) rank, po_line_id from po_lines_all
where group_line_id is not null and po_header_id = ' || mod_pk1_value || ') base
WHERE mod.po_line_id = base.po_line_id
and mod.group_line_id is not null
and mod.po_header_id = ' || mod_pk1_value || '
and mod.draft_id = ' || mod_pk2_value || '
and mod.po_line_id in ( SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll, po_distributions_all pd
WHERE ' || l_where_stmt || ' and mod_doc_source_name = :7 and column_name = :8
and nvl(orig_value, 1) = nvl(:9 ,1) and nvl(mod_value, 1) = nvl(:10 ,1)
and d.mod_doc_pk4_val = pll.line_location_id and d.mod_doc_pk5_val = pd.po_distribution_id
and pll.shipment_num = :11 and pd.distribution_num = :12 ))
GROUP BY group_line_id, rn
order by orderseq, fromline';
l_sql_stmt := '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
''' || l_datatype || ''' as "datatype",
''' || l_subentity || ''' as "sub_entity"),
XMLELement("orig_id", ''' || l_origvalue ||'''),
XMLELement("mod_id", ''' || l_modvalue ||'''),
XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
XMLELement("mod_value", ''' || l_modvalue_desc || '''),
XMLELement("printable_pks", ''' || l_print_string ||'''))
FROM dual) ';
l_sql_stmt := l_sql_stmt || ',' || '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
''' || l_datatype || ''' as "datatype",
''' || l_subentity || ''' as "sub_entity"),
XMLELement("orig_id", ''' || l_origvalue ||'''),
XMLELement("mod_id", ''' || l_modvalue ||'''),
XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
XMLELement("mod_value", ''' || l_modvalue_desc || '''),
XMLELement("printable_pks", ''' || l_print_string ||'''))
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 po_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 po_entity_differences d
WHERE '|| l_where_stmt ||' and column_name = :1 and nvl(orig_value, 1) = nvl(:2, 1) and nvl(mod_value, 1) = nvl(:3, 1) ';
execute immediate l_insert_stmt USING l_column_name, l_orig_value, l_mod_value;
SELECT XMLAgg(XMLElement("REF_PR_NUM", segment1))
INTO l_req_nums
FROM (SELECT distinct h.segment1
FROM po_distributions_merge_v pod,
po_req_distributions_all prd,
po_requisition_headers_all h,
po_requisition_lines_all l
WHERE pod.po_header_id = p_header_id
and pod.po_line_id = p_line_id
and pod.draft_id = p_draft_id
and pod.req_distribution_id = prd.distribution_id
and prd.requisition_line_id = l.requisition_line_id
and l.requisition_header_id = h.requisition_header_id);
SELECT XMLAgg(XMLElement("REF_PR_NUM", requisition_number))
INTO l_req_nums
FROM (SELECT distinct requisition_number
FROM pon_backing_requisitions
WHERE auction_header_id = p_auction_header_id
and line_number = p_auction_line_number);
SELECT XMLAgg(XMLElement("ACRN", acrn))
INTO l_acrns
FROM (SELECT distinct pod.acrn
FROM po_distributions_merge_v pod
WHERE pod.po_header_id = p_header_id
and pod.po_line_id = p_line_id
and pod.draft_id = p_draft_id);