[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'S' INTO bil_indicator FROM PO_ACCEPTANCES WHERE po_header_id = p_header_id AND draft_id = p_draft_id
AND accepting_party = 'S' AND accepted_flag = 'Y' AND signature_flag = 'Y';
SELECT 'B' INTO bil_indicator FROM PO_ACCEPTANCES WHERE po_header_id = p_header_id AND draft_id = p_draft_id
AND accepting_party = 'B' AND accepted_flag = 'Y' AND signature_flag = 'Y';
SELECT ICA.ATTRIBUTE_ID, ICA.RT_CATEGORY_ID, REPLACE(ICA.KEY, ' ', '_') KEY, ICA.STORED_IN_TABLE, ICA.STORED_IN_COLUMN
from icx_cat_agreement_attrs_v ICA where
(ICA.rt_category_id = p_categoryId
or (ICA.rt_category_id =0 and ICA.attribute_id> 1000)
or ICA.key in ('LONG_DESCRIPTION','SUPPLIER_URL', 'MANUFACTURER', 'MANUFACTURER_URL', 'MANUFACTURER_PART_NUM',
'ATTACHMENT_URL', 'UNSPSC', 'PICTURE', 'THUMBNAIL_IMAGE', 'LEAD_TIME', 'AVAILABILITY', 'COMMENTS', 'ALIAS'
)) and language = userenv('lang');
S_SQL := '(select XMLElement("PO_ATTRIBUTE_VALUES", XMLForest(' || REC.STORED_IN_COLUMN || ' as ' || REC.KEY;
SELECT ICA.ATTRIBUTE_ID, ICA.RT_CATEGORY_ID, REPLACE(ICA.KEY, ' ', '_') KEY, ICA.STORED_IN_TABLE, ICA.STORED_IN_COLUMN
from icx_cat_agreement_attrs_v ICA where
(ICA.rt_category_id = p_categoryId
or (ICA.rt_category_id =0 and ICA.attribute_id> 1000)
or ICA.key in ('LONG_DESCRIPTION','SUPPLIER_URL', 'MANUFACTURER', 'MANUFACTURER_URL', 'MANUFACTURER_PART_NUM',
'ATTACHMENT_URL', 'UNSPSC', 'PICTURE', 'THUMBNAIL_IMAGE', 'LEAD_TIME', 'AVAILABILITY', 'COMMENTS', 'ALIAS'
)) and language = userenv('lang');
S_SQL := '(select XMLElement("PO_ATTRIBUTE_VALUES", XMLForest(' || REC.STORED_IN_COLUMN || ' as ' || REC.KEY;
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_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.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.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.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;
SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
'SUPPLIER_DTLS', NULL, 'HIDD_ADDRS_XML', 'INTERNAL_VALUE') INTO v_clob_addr FROM dual;
SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
'SUPPLIER_DTLS', NULL, 'HIDD_CNTCT_XML', 'INTERNAL_VALUE') INTO v_clob_addr FROM dual;
Select eag.attr_group_id, eav.database_column INTO l_attr_grp_id, l_col_name
from ego_attrs_v eav, ego_attr_groups_v eag
where eav.attr_group_name = 'SHIP_INFO' and eav.attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS'
AND eav.ATTR_NAME = 'FOB' and eag.attr_group_name = 'SHIP_INFO' and
eag.attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS';
SELECT 1 INTO l_value
FROM PO_LINE_LOCATIONS_ALL
WHERE PO_HEADER_ID = pt_headerId
and rownum = 1;
l_sql_o := '(SELECT line_location_id from po_line_locations_all where PO_HEADER_ID =:1)';
SELECT 1 INTO l_value
FROM PO_LINE_LOCATIONS_draft_all
WHERE draft_id = draftId
and rownum = 1;
l_sql_o := '(SELECT pla.line_location_id from po_line_locations_draft_all pla,' ||
' po_drafts pd where pd.draft_id = :1 and pd.document_id = pla.po_header_id)';
l_sql_d := '(SELECT COUNT(NVL( ' || l_col_name || ' , ''N'')) '
|| 'FROM PO_LINE_LOCATIONS_All_ext_b WHERE line_location_id IN '
|| l_sql_o
|| ' and draft_id = :2 and attr_group_id =:3 AND '
|| ' DECODE( NVL(' || l_col_name || ', ''N''), ''OSP'', ''Origin'', ''OAL'', ''Origin'',
NVL(' || l_col_name || ', ''N'')) <> :4)';
select uda_template_id, draft_id
into l_uda_template_id,l_draft_id
from po_headers_merge_v
where po_header_id = headerId
and draft_id = nvl(draftId, -1);
select decode(EDWOSB_COUNT, 0, '''N''', '''Y''') EDWOSB,
decode(HUBZ_COUNT, 0, '''N''', '''Y''') HUBZ,
decode(WOSB_COUNT, 0, '''N''', '''Y''') WOSB,
decode(SDVO_COUNT, 0, '''N''', '''Y''') SDVO,
decode(WOMEN_COUNT, 0, '''N''', '''Y''') WO,
decode(SDB_COUNT, 0,
decode(SDBSBA_COUNT, 0, '''N''', '''Y'''),
'''Y''') SDB
into
p_fp_of347_EDWOSB,
p_fp_of347_hubzone,
p_fp_of347_WOSB,
p_fp_of347_SDVO,
p_fp_of347_women,
p_fp_of347_disadv
from
(
select pca.lookup_code
from POS_BUS_CLASS_ATTR pca, po_vendors supp, po_headers_merge_v hdr
where hdr.po_header_id = headerId
and hdr.draft_id = nvl(draftId, -1)
and hdr.vendor_id = supp.vendor_id
and pca.party_id = supp.party_id
AND ( pca.end_date_active is null or trunc(pca.end_date_active) > sysdate )
AND pca.status='A'
AND pca.class_status = 'APPROVED'
AND pca.classification_id not in
(
SELECT classification_id
FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
WHERE psm.party_id = pca.party_id
AND psm.mapping_id = pbcr.mapping_id
AND pbcr.request_status = 'PENDING'
AND pbcr.request_type in ( 'ADD', 'UPDATE' )
AND pbcr.classification_id is not null)
)
pivot ( count(lookup_code) as count
for lookup_code in ('EDWOSB' as EDWOSB,
'HUB_ZONE' as HUBZ ,
'WOMEN_OWNED_WOSB_PRGM' as WOSB,
'DISABLED_VETERAN_OWNED' as SDVO,
'WOMEN_OWNED' as WOMEN,
'27' as SDB, 'A4' as SDBSBA)) ;
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);
SELECT document_id INTO l_header_id FROM po_drafts WHERE draft_id = draftId;
SELECT line_location_id,
shipment_type
FROM PO_LINE_LOCATIONS_ALL
WHERE po_header_id = p_header_id;
SELECT rate,
po_distribution_id,
encumbered_amount
FROM PO_DISTRIBUTIONS_ALL
WHERE line_location_id = line_loc_id;
SELECT gr.attr_group_name,
t1.attribute_group_id
FROM po_uda_ag_template_usages t1,
ego_attr_groups_v gr
WHERE t1.template_id = p_template_id
AND t1.attribute_category = 'CLOSEOUT'
AND t1.attribute_group_id = gr.attr_group_id
AND gr.attr_group_type = 'PO_HEADER_EXT_ATTRS';
SELECT val.attr_name,
val.database_column
FROM po_uda_ag_template_usages t1,
ego_attr_groups_v gr,
ego_attrs_v val
WHERE t1.template_id = p_template_id
AND t1.attribute_category = 'CLOSEOUT'
AND t1.attribute_group_id = gr.attr_group_id
AND gr.attr_group_id = p_attr_group_id
AND gr.attr_group_name = val.attr_group_name
AND gr.attr_group_type = val.attr_group_type
AND gr.attr_group_type = 'PO_HEADER_EXT_ATTRS';
l_sql := l_sql || '(select XMLElement("' || l_attr_grp_name || '", XMLForest(';