[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
is_docupload_inserted VARCHAR2(1);
SELECT form_id INTO l_form_id
FROM PON_FORMS_SECTIONS
WHERE form_code = 'FED_DOC_UPLOAD'
AND STATUS='ACTIVE'
AND ROWNUM < 2
ORDER BY FORM_VERSION DESC;
SELECT auction_header_id_orig_amend INTO l_header_id_orig_amend
FROM pon_auction_headers_all WHERE auction_header_id = p_document_id;
record would have already been inserted into pon_fbo_posts.
For automatic posting during soliciation publishing,
we have to insert it now */
SELECT 'Y' INTO is_docupload_inserted
FROM pon_fbo_posts
WHERE cp_request_id = p_request_id;
insert_record(p_document_id,
l_form_id,
p_form_code,
p_request_id,
'PON');
INSERT_DOC_UPLOAD_RECORD(p_document_id,l_form_id,NULL,NULL,NULL);
LOG_MESSAGE(l_module,'Document Upload record inserted into '
|| 'pon_forms_instances and pon_form_field_values tables');
SELECT pf.form_code
INTO l_form_code
FROM PON_FORMS_SECTIONS pf
WHERE pf.form_id = l_form_id;
SELECT 'Y' INTO is_post_pending FROM pon_fbo_posts
WHERE document_id = p_document_id
AND form_id = l_form_id
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;
PROCEDURE UPDATE_CP_PROGRESS(p_document_id IN NUMBER,
p_form_id IN NUMBER,
p_form_code IN VARCHAR2,
p_product_code IN VARCHAR2 ,
p_request_id IN NUMBER,
p_status IN VARCHAR2)
AS
l_form_id NUMBER;
UPDATE pon_fbo_posts
SET FBO_POST_STATUS = 'INPROGRESS'
WHERE cp_request_id = p_request_id;
UPDATE pon_forms_instances
SET status = Decode(p_status,'SUCCESS','FBO_INPROCESS','ERROR','FBO_ERROR')
WHERE entity_pk1 = p_document_Id
AND form_id = p_form_id;
SELECT form_id INTO l_form_id
FROM pon_fbo_posts
WHERE cp_request_id = p_request_id;
UPDATE pon_forms_instances
SET status = Decode(p_status,'SUCCESS','FBO_INPROCESS','ERROR','FBO_ERROR')
WHERE entity_pk1 = p_document_Id
AND form_id = l_form_id;
END UPDATE_CP_PROGRESS;
SELECT form_name INTO l_form_name
FROM pon_forms_sections_tl
WHERE form_id = p_form_id
AND LANGUAGE = UserEnv('LANG');
SELECT 'S' INTO x_result
FROM pon_fbo_posts
WHERE document_id = p_auction_header_id
AND form_code = 'FED_PRESOL'
AND FBO_POST_STATUS = 'SUCCESS'
AND ROWNUM < 2;
SELECT Sum(Nvl(clm_amount,0)) INTO l_total_amount
FROM pon_auction_item_prices_all
WHERE auctioN_header_id = p_auction_header_id;
SELECT auction_header_id INTO l_auctioN_header_id
FROM pon_bid_headers
WHERE po_header_id = p_po_header_id;
SELECT 'S' INTO x_result
FROM pon_fbo_posts
WHERE document_id = L_auction_header_id
AND form_code = 'FED_DOC_UPLOAD'
AND FBO_POST_STATUS = 'SUCCESS'
AND ROWNUM < 2;
SELECT pah.document_number,pah.auctioN_title
INTO x_sol_number,x_sol_title
FROM
pon_auction_headers_all pah,
pon_bid_headers pbh
WHERE pbh.po_header_id = p_po_header_id
AND pbh.auction_header_id = pah.auction_header_id;
SELECT form_code INTO l_form_code
FROM pon_forms_sections
WHERE form_id = p_form_id;
select DOCUMENT_NUMBER,
To_Char(SYSDATE,'YYYYMMDD'),
TO_CHAR(CLOSE_BIDDING_DATE, 'YYYYMMDD'),
TO_CHAR(CLOSE_BIDDING_DATE + 15, 'YYYYMMDD')
into fed_solnbr,fed_date, fed_respdate,fed_archdate
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_ZIP_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_ZIP';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_CLASSCOD_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_CLASSCOD';
l_classcode_query := 'select ' || FED_CLASSCOD_COL ||
' from pon_form_field_values ' ||
' where entity_Pk1 = :doc_id and form_id = :form_id';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO fed_naics_col
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_NAICS';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_SUBJECT_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_SUBJECT';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_CONTACT_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_CONTACT';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_DESC_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_DESC';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_SETASIDE_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_SETASIDE';
l_setaside_code_query := 'select ' || FED_SETASIDE_COL ||
' from pon_form_field_values
where entity_Pk1 = :doc_id and form_id = :form_id';
SELECT DISPLAYED_FIELD INTO l_setaside_value FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_SET_ASIDE_TYPE'
AND lookup_code = l_setaside_code
AND ROWNUM < 2;
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_URL_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_URL';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_URL_DESC_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_URL_DESC';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_EMAIL_ADDRESS_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_EMAIL_ADDRESS';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_EMAIL_DESC_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_EMAIL_DESC';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_POPADDRESS_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_POPADDRESS';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_POPZIP_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_POPZIP';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_POPCOUNTRY_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_POPCOUNTRY';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_RECOVERYACT_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_RECOVERY_ACT';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_RESPONSEDATE_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_RESPONSEDATE';
arch_date_query := 'select to_char(' || FED_RESPONSEDATE_COL ||'+15,''YYYYMMDD'')
from pon_form_field_values
where entity_Pk1 = :doc_id and form_id = :form_id';
query := 'SELECT xmlelement("' || l_soap_operation || '",
xmlelement("data",
xmlelement("date",''' || fed_date || '''),
xmlelement("zip",' || fed_zip_col || '),
xmlelement("classcod",''' || l_classcode_charvalue || '''),
xmlelement("naics",' || fed_naics_col || '),
xmlelement("offadd",'''|| PON_FORMS_UTIL_PVT.get_uda_fed_offadd(p_auction_header_id) || '''),
xmlelement("subject",' || fed_subject_col || ' ),
xmlelement("solnbr",''' || fed_solnbr ||''' ),';
SELECT auction_header_id INTO l_amendment_id
FROM pon_auction_headers_all
WHERE auction_header_id_prev_amend =
(SELECT auction_header_id_prev_amend
FROM pon_auction_Headers_all
WHERE auction_header_Id = p_auction_header_id)
AND amendment_flag = 'Y'
AND amendment_Number > 0;
FOR temp IN (SELECT fl.file_name as filename,ENCODE_BLOB(fl.file_data) as filedata,
fl.file_name as description
FROM fnd_attached_documents fad,
fnd_documents_vl fdvl,
fnd_lobs fl,
fnd_document_categories doccat
WHERE fad.document_id = fdvl.document_id
and fdvl.category_id = doccat.category_id
and doccat.name = l_doccat_name
AND fad.entity_name = 'PON_AUCTION_HEADERS_ALL'
AND fad.pk1_value = Nvl(l_amendment_id,p_auction_header_id)
AND fl.file_id(+) = fdvl.media_id
AND fdvl.datatype_name = 'File') LOOP
files_temp_holder := '' || temp.filename || ' ';
select updatexml(x_xml,'//files',xmltype(files_clob))
INTO x_xml FROM dual ;
SELECT form_code INTO l_form_code
FROM pon_forms_sections
WHERE form_id = p_form_id;
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_PRNUMBER_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_PR_NUMBER';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO fed_PKGLABEL_col
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_PACKAGE_LABEL';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_PROJECTNUMBER_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_PROJECTNUMBER';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_NSNMMAC_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_NSNMMAC';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_PARTNUMBER_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_PARTNUMBER';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_NOMENCLATURE_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_NOMENCLATURE';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_EXPORTCONTROL_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_EXPORT_CONTROLLED';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_EXPLICITACCESS_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_EXPLICIT_ACCESS';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_CDAVAIL_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_IS_CD_AVAIL';
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_RELEASE_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_RELEASE';
query := 'SELECT xmlelement("' || l_soap_operation || '",
xmlelement("data",
xmlelement("date",''' || To_Char(SYSDATE,'YYYYMMDD') || '''),
xmlelement("on_fbo",''' || PON_FORMS_UTIL_PVT.GET_FED_ONFBO(p_auction_header_id) || '''),
xmlelement("pr_number",' || FED_PRNUMBER_COL || '),
xmlelement("package_label",' || fed_PKGLABEL_col || '),
xmlelement("project_number",'|| FED_PROJECTNUMBER_COL || '),
xmlelement("nsn_mmac",' || FED_NSNMMAC_COL || ' ),
xmlelement("part_number",' || FED_PARTNUMBER_COL ||' ),
xmlelement("nomenclature",' || FED_NOMENCLATURE_COL ||' ),
xmlelement("export_controlled",' || FED_EXPORTCONTROL_COL ||' ),
xmlelement("explicit_access",' || FED_EXPLICITACCESS_COL || '),
xmlelement("is_cd_avail",'|| FED_CDAVAIL_COL || '),
xmlelement("files",''''),
xmlelement("release",' || FED_RELEASE_COL || ')
))
from pon_form_field_values WHERE entity_Pk1 = :doc_id and form_id = :form_id';
SELECT auction_header_id INTO l_amendment_id
FROM pon_auction_headers_all
WHERE auction_header_id_prev_amend =
(SELECT auction_header_id_prev_amend
FROM pon_auction_Headers_all
WHERE auction_header_Id = p_auction_header_id)
AND amendment_flag = 'Y'
AND amendment_Number > 0;
FOR temp IN (SELECT fl.file_name as filename,ENCODE_BLOB(fl.file_data) as filedata,
fl.file_name as description
FROM fnd_attached_documents fad,
fnd_documents_vl fdvl,
fnd_lobs fl,
fnd_document_categories doccat
WHERE fad.document_id = fdvl.document_id
and fdvl.category_id = doccat.category_id
and doccat.name = 'ToFedBizOppsSecure'
AND fad.entity_name = 'PON_AUCTION_HEADERS_ALL'
AND fad.pk1_value = nvl(l_amendment_id,p_auction_header_id)
AND fl.file_id(+) = fdvl.media_id
AND fdvl.datatype_name = 'File') LOOP
files_temp_holder := '' || temp.filename || ' ';
select updatexml(x_xml,'//files',xmltype(files_clob))
INTO x_xml FROM dual ;
select DOCUMENT_NUMBER,TO_CHAR(CLOSE_BIDDING_DATE, 'YYYYMMDD')
into fed_solnbr,fed_respdate
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT MAPPING_FIELD_VALUE_COLUMN
INTO FED_NTYPE_COL
FROM pon_form_section_compiled
WHERE form_id = p_form_id
AND field_code = 'FED_NTYPE_DOCUPLOAD'; -- bug 13484000
l_ntype_query := 'select ' || FED_NTYPE_COL ||
' from pon_form_field_values ' ||
' where entity_Pk1 = :doc_id and form_id = :form_id';
query := 'SELECT xmlelement("' || l_soap_operation || '",
xmlelement("data",
xmlelement("date",''' || To_Char(SYSDATE,'YYYYMMDD')|| '''),
xmlelement("solnbr",''' || fed_solnbr ||''' ),
xmlelement("ntype",''' || l_ntype_val ||''' ),
xmlelement("uploadtype",''' || PON_FORMS_UTIL_PVT.get_fed_uploadtype(p_auction_header_id) || '''),
xmlelement("respdate",''' || fed_respdate ||''' ),
xmlelement("files",'''')
))
from dual';
SELECT fl.file_name, ENCODE_BLOB(fl.file_data)
INTO l_pdfname, l_pdfdata
FROM fnd_Lobs fl WHERE file_Id =
(SELECT file_id FROM pon_action_History
WHERE object_id = p_auction_header_id
AND ACTION_TYPE='STORE_CLM_PDF'
AND ROWNUM < 2 );
SELECT auction_header_id INTO l_amendment_id
FROM pon_auction_headers_all
WHERE auction_header_id_prev_amend =
(SELECT auction_header_id_prev_amend
FROM pon_auction_Headers_all
WHERE auction_header_Id = p_auction_header_id)
AND amendment_flag = 'Y'
AND amendment_Number > 0;
FOR temp IN ( SELECT fl.file_name as filename,ENCODE_BLOB(fl.file_data) as filedata,
fl.file_name as description
FROM fnd_attached_documents fad,
fnd_documents_vl fdvl,
fnd_lobs fl,
fnd_document_categories doccat
WHERE fad.document_id = fdvl.document_id
and fdvl.category_id = doccat.category_id
and doccat.name in ('ToFedBizOpps','Vendor')
AND fad.entity_name = 'PON_AUCTION_HEADERS_ALL'
AND fad.pk1_value = Nvl(l_amendment_id,p_auction_header_id)
AND fl.file_id(+) = fdvl.media_id
AND fdvl.datatype_name = 'File') LOOP
files_temp_holder := '' || temp.filename || ' ';
select updatexml(x_xml,'//files',xmltype(files_clob))
INTO x_xml FROM dual ;
SELECT form_id INTO l_form_id
FROM PON_FORMS_SECTIONS
WHERE form_code = 'FED_DOC_UPLOAD'
AND STATUS='ACTIVE'
AND ROWNUM < 2
ORDER BY FORM_VERSION DESC;
SELECT form_code INTO l_form_code
FROM pon_forms_sections
WHERE form_id = p_form_id;
UPDATE pon_fbo_posts
SET xml_data = p_xml
WHERE document_id = p_document_id
AND cp_request_id = p_request_id;
PROCEDURE UPDATE_FBO_POST_STATUS(p_document_id IN NUMBER,
p_form_id IN NUMBER,
p_form_code IN VARCHAR2,
p_product_code IN VARCHAR2,
p_status IN VARCHAR2,
p_message IN VARCHAR2,
x_result IN OUT NOCOPY VARCHAR2)
AS
l_txn_id NUMBER;
UPDATE pon_fbo_posts
SET fbo_post_status = p_status
WHERE document_id = p_document_id
AND form_id = p_form_Id
AND fbo_post_status IS NULL;
UPDATE pon_forms_instances
SET status = Decode(p_status,'SUCCESS','POSTED','FAILED_POSTED'),
fbo_date_sent = SYSDATE
WHERE entity_pk1 = p_document_Id
AND form_id = p_form_id;
SELECT transaction_id INTO l_txn_id
FROM pon_fbo_posts
WHERE document_id = p_document_id
AND form_id = p_form_Id
AND fbo_post_status IS NULL;
INSERT INTO pon_interface_errors(interface_type,
table_name,
batch_id,
error_message_name,
auction_header_id,
token1_name,
token1_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES('FedBizOpps',
'PON_FBO_POSTS',
l_txn_id,
p_message,
p_document_id,
'FORM_ID',
p_form_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
);
END UPDATE_FBO_POST_STATUS;
SELECT l_response_xml.extract('//success/text()').getStringVal(),
l_response_xml.extract('//item/text()').getStringVal()
INTO l_result,l_res_message
FROM dual;
UPDATE pon_fbo_posts
SET fbo_post_status = Decode(l_result,'true','SUCCESS','FAILED'),
fbo_post_date = SYSDATE
WHERE document_id = To_Number(l_document_id)
AND cp_request_id = To_Number(l_request_id);
UPDATE pon_forms_instances
SET status = Decode(l_result,'true','POSTED','FAILED_POSTED'),
fbo_date_sent = SYSDATE
WHERE entity_pk1 = To_Number(l_document_Id)
AND form_id = To_Number(l_form_id);
INSERT INTO pon_interface_errors(interface_type,
table_name,
batch_id,
request_id,
error_message_name,
token1_name,
token1_value,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES('FBOUPLOAD',
'PON_FBO_POSTS',
(select transaction_id from pon_fbo_posts where cp_request_id = To_Number(l_request_id)
AND ROWNUM < 2),
To_Number(l_request_id),
l_res_message,
'FORM_ID',
l_form_id,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id
);
SELECT document_Number INTO l_sol_number
FROM pon_auctioN_Headers_all
WHERE auctioN_header_id = p_auction_header_id;
SELECT fbo_date_sent INTO l_date_sent
FROM pon_forms_instances
WHERE entity_pk1 = To_Number(p_auction_header_id)
AND form_id = To_Number(p_form_id);
FOR rec IN (SELECT DISTINCT prh.preparer_id
FROM pon_backing_requisitions pbr,
po_requisition_headers_all prh
WHERE pbr.auction_header_id = p_auction_header_id
AND pbr.requisition_header_id = prh.requisition_header_id)
LOOP
PO_REQAPPROVAL_INIT1.get_user_name(rec.preparer_id, l_username, l_user_display_name);
PROCEDURE insert_record(p_document_id IN NUMBER,
p_form_id IN NUMBER,
p_form_code IN VARCHAR2,
p_request_id IN NUMBER,
p_product_code IN VARCHAR2
) IS
pragma AUTONOMOUS_TRANSACTION;
INSERT INTO pon_fbo_posts
(transaction_id,
document_id,
form_id,
form_code,
product_code,
cp_request_id,
xml_request_date,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES(PON_FBO_POSTS_S.NEXTVAL,
p_document_id,
p_form_id,
p_form_code,
p_product_code,
p_request_id,
SYSDATE,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id);
END insert_record;
PROCEDURE INSERT_DOC_UPLOAD_RECORD(p_document_id IN NUMBER,
p_form_id IN NUMBER,
p_status IN VARCHAR2,
p_user_id IN NUMBER,
p_user_login IN NUMBER)
AS
BEGIN
INSERT INTO pon_forms_instances(ENTITY_CODE,
ENTITY_PK1,
FORM_ID,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES('PON_AUCTION_HEADERS_ALL',
p_document_id,
p_form_id,
'DATA_ENTERED',
SYSDATE,
fnd_global.user_id ,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id);
INSERT INTO pon_form_field_values(FORM_FIELD_VALUE_ID,
FORM_ID,
OWNING_ENTITY_CODE,
ENTITY_PK1,
SECTION_ID,
PARENT_FIELD_VALUES_FK,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
Textcol1)
VALUES(pon_form_field_values_s.nextval,
p_form_id,
'PON_AUCTION_HEADERS_ALL',
p_document_id,
-1,
-1,
SYSDATE,
fnd_global.user_id ,
SYSDATE,
fnd_global.user_id ,
fnd_global.login_id,
'PRESOL'); -- bug 13484000
END INSERT_DOC_UPLOAD_RECORD;