[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_is_sourcing_installed
FROM fnd_application a,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND application_short_name LIKE 'PON';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
INSERT_IN_PON_FBO_POSTS(l_document_id,'FED_AWARD',l_request_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
l_query1 := '(SELECT xmlforest(
Max(Decode (ego.descriptive_flex_context_code,
''addresses'',phae.c_ext_attr10,
NULL)) "zip",
Max(Decode (ego.descriptive_flex_context_code,
''addresses'',phae.c_ext_attr5,
NULL )) "offadd",
Max(Decode(ego.descriptive_flex_context_code,
''SET_ASIDE_INFO'',plc.displayed_field,
NULL )) "setaside",
decode( ''' || p_notice_type || ''',
''FED_JA'',
Max(Decode(ego.descriptive_flex_context_code,
''AWD_CTGR'',po_fbo_mapping.get_fbo_stauth(phae.c_ext_attr1),
NULL )) ,
NULL ) "stauth",
Max(decode(ego.descriptive_flex_context_code,
''SUPPLIER_DTLS'',
(nvl(C_EXT_ATTR22,pv.vendor_name)
|| fnd_global.newline
|| phae.C_EXT_ATTR3),
NULL)) "awardee",
decode(''' || p_notice_type || ''',
''FED_AWARD'',
Max(decode(ego.descriptive_flex_context_code,
''SUPPLIER_DTLS'',
nvl(C_EXT_ATTR15,C_EXT_ATTR13) ,
NULL)),
NULL) "awardee_duns" ,
xmlforest(fnd_profile.Value(''PON_FBO_URL'') "url",
decode(fnd_profile.Value(''PON_FBO_URL''),
NULL,NULL,
''Government Agency URL'') "desc")
"link"
)
FROM po_headers_all_ext_b phae,
ego_fnd_dsc_flx_ctx_ext ego,
po_lookup_codes plc,
po_vendors pv
WHERE phae.po_header_id = pha.po_header_id
AND phae.draft_id = -1
AND pha.vendor_id = pv.vendor_id
AND phae.attr_group_id = ego.attr_group_id
AND ego.descriptive_flexfield_name = ''PO_HEADER_EXT_ATTRS''
AND (
(ego.descriptive_flex_context_code = ''addresses''
AND phae.c_ext_attr39 = ''ISSUING_OFFICE''
AND plc.lookup_code = pha.type_lookup_code
AND plc.lookup_type = ''PO TYPE'')
OR (ego.descriptive_flex_context_code = ''SET_ASIDE_INFO''
AND plc.lookup_type = ''PO_CLM_SET_ASIDE_TYPE''
AND plc.enabled_flag = ''Y''
AND Trunc(Nvl(plc.inactive_date, SYSDATE)) >= Trunc(SYSDATE)
AND plc.lookup_code = phae.c_ext_attr2 )
OR (ego.descriptive_flex_context_code = ''AWD_CTGR''
AND plc.lookup_type = ''PO_CLM_AUTH_OTR_FULL_OPEN_COMP''
AND plc.enabled_flag = ''Y''
AND Trunc(Nvl(plc.inactive_date, SYSDATE)) >= Trunc(SYSDATE)
AND plc.lookup_code = phae.c_ext_attr1)
OR (ego.descriptive_flex_context_code = ''SUPPLIER_DTLS''
AND plc.lookup_code = pha.type_lookup_code
AND plc.lookup_type = ''PO TYPE'')
)
)';
l_query3 := '(SELECT xmlforest(
pbv.full_name ||
decode(pbv.work_telephone,
NULL, NULL,
fnd_global.newline || pbv.work_telephone)
"contact",
xmlforest(pbv.email_address "address",
decode(pbv.email_address,
NULL, NULL,
''Government Agency contact email'') "desc")
"email"
)
FROM po_buyers_val_v pbv
WHERE pbv.employee_id=pha.clm_contract_officer)';
l_attach_query := '(SELECT xmlelement("files",
xmlagg(xmlelement("file",
xmlforest(fl.file_name "filename",
PO_FBO_PKG.ENCODE_BLOB
(fl.file_data) "filedata",
d.description "desc"))))
FROM fnd_documents_vl d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl,
fnd_document_categories_vl fdc
WHERE ad.pk1_value = To_char(pha.po_header_id)
AND ad.entity_name = ''PO_HEADERS''
AND d.document_id = ad.document_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND d.datatype_id = 6
AND af.function_name = ''PO_CLM_ATTACHMENTS''
AND d.media_id = fl.file_id
AND dcu.enabled_flag = ''Y''
AND fdc.category_id = dcu.category_id
AND fdc.name = ''JAToFBO'')';
l_xml_query := 'select xmlelement("' || l_soap_operation || '",
xmlelement("data",
xmlconcat(
xmlforest(' || l_query2 || ' ) , '
|| l_query1
|| ', ' || l_query3
||', decode( ''' || p_notice_type || ''',
''FED_JA'',' || l_attach_query
|| ', null) )))
from po_headers_all pha
where pha.po_header_id = ' || p_document_id;
SELECT Decode(Length(Trim(Translate(c_ext_attr4, ' +-.0123456789', ' '))),
NULL,
Substr(c_ext_attr4, 1, 2),
Substr(c_ext_attr4, 1, 1))
INTO l_class_cod
FROM (SELECT SUM(Decode(pla.matching_basis,
'AMOUNT',pla.amount,
(pla.quantity * pla.unit_price))) total_amount,
c_ext_attr4
FROM po_lines_all_ext_b ple,
po_lines_all pla,
ego_fnd_dsc_flx_ctx_ext ego
WHERE pla.po_header_id = p_document_id
AND pla.po_line_id = ple.po_line_id
AND ple.draft_id = -1
AND ple.attr_group_id = ego.attr_group_id
AND ego.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS'
AND ego.descriptive_flex_context_code = 'FED_CUST_DESG'
GROUP BY c_ext_attr4
ORDER BY total_amount DESC)
WHERE ROWNUM = 1;
SELECT Decode(Length(Trim(Translate(c_ext_attr1, ' +-.0123456789', ' '))),
NULL,
Substr(c_ext_attr1, 1, 2),
Substr(c_ext_attr1, 1, 1))
INTO l_class_cod
FROM po_headers_all_ext_b phae,
ego_fnd_dsc_flx_ctx_ext ego
WHERE phae.po_header_id = p_document_id
AND phae.draft_id = -1
AND phae.attr_group_id = ego.attr_group_id
AND ego.descriptive_flexfield_name = 'PO_HEADER_EXT_ATTRS'
AND ego.descriptive_flex_context_code = 'FSC_PSC'
AND ROWNUM =1;
SELECT c_ext_attr3
INTO l_naics
FROM (SELECT SUM(decode(pla.matching_basis,
'AMOUNT',pla.amount,
(pla.quantity * pla.unit_price))) total_amount,
c_ext_attr3
FROM po_lines_all_ext_b ple,
po_lines_all pla,
ego_fnd_dsc_flx_ctx_ext ego
WHERE pla.po_header_id = p_document_id
AND ple.draft_id = -1
AND pla.po_line_id = ple.po_line_id
AND ple.attr_group_id = ego.attr_group_id
AND ego.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS'
AND ego.descriptive_flex_context_code = 'FED_CUST_DESG'
GROUP BY c_ext_attr3
ORDER BY total_amount DESC)
WHERE ROWNUM = 1;
SELECT phae.C_EXT_ATTR4
INTO l_naics
FROM po_headers_all_ext_b phae,
ego_fnd_dsc_flx_ctx_ext ego,
po_lookup_codes plc
WHERE phae.po_header_id = p_document_id
AND phae.draft_id = -1
AND phae.attr_group_id = ego.attr_group_id
AND ego.descriptive_flexfield_name = 'PO_HEADER_EXT_ATTRS'
AND ego.descriptive_flex_context_code = 'SET_ASIDE_INFO'
AND plc.lookup_type = 'PO_CLM_NAICS'
AND plc.enabled_flag = 'Y'
AND Trunc(Nvl(plc.inactive_date, SYSDATE)) >= Trunc(SYSDATE)
AND plc.lookup_code = phae.C_EXT_ATTR4
AND ROWNUM =1;
PROCEDURE INSERT_IN_PON_FBO_POSTS(p_document_id IN NUMBER,
p_notice_type IN VARCHAR2,
p_request_id IN NUMBER
) IS
BEGIN
INSERT INTO pon_fbo_posts
(transaction_id,
document_id,
form_id,
form_code,
product_code,
cp_request_id,
xml_request_date)
VALUES(PON_FBO_POSTS_S.NEXTVAL,
p_document_id,
NULL,
p_notice_type,
'PO',
p_request_id,
SYSDATE);
END INSERT_IN_PON_FBO_POSTS;
SELECT 'Y'
INTO l_is_post_pending
FROM pon_fbo_posts
WHERE document_id = p_document_id
AND form_id = p_notice_type
AND Nvl(fbo_post_status, 'INPROGRESS') NOT IN ( 'SUCCESS', 'FAILED' )
AND pon_fbo_pkg.Get_request_internal_status(cp_request_id) <> 'ERROR'
AND Nvl2(p_request_id, cp_request_id, -9999) <> Nvl(p_request_id, -9998)
AND ROWNUM < 2;