The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trxnmid INTO trxnmid_out
FROM iby_trxn_summaries_all
WHERE (transactionid=ci_transactionid)
AND (status=ci_status)
AND ((trxntypeid=NVL(ci_trxntypeid,trxntypeid)) OR (trxntypeid is NULL))
ORDER BY creation_date DESC;
SELECT trxnmid INTO trxnmid_out
FROM iby_trxn_summaries_all
WHERE (transactionid=transactionid_in) AND (trxntypeid=trxntypeid_in);
SELECT iby_trxn_documentid_s.NEXTVAL INTO trxn_document_id_seq FROM dual;
INSERT INTO iby_trxn_documents (trxnmid,doctype,document,object_version_number,last_update_date,last_updated_by,creation_date,created_by,last_update_login,trxn_document_id, payment_instruction_id)
-- object version number is 0 as the document is empty
--
VALUES (trxnmid_in,doctype_in,empty_clob(),0,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,trxn_document_id_seq, NULL);
SELECT iby_trxn_documentid_s.NEXTVAL INTO trxn_document_id_seq FROM dual;
INSERT INTO iby_trxn_documents (trxnmid,doctype,document,object_version_number,
last_update_date,last_updated_by,creation_date,created_by,last_update_login,
trxn_document_id, payment_instruction_id)
-- object version number is 0 as the document is empty
--
-- work around for the unique key trxnmid/doctype
-- FZ 8/28/2005. This should not be a problem
-- for funds capture code as the doctype (100)
-- is for R12 disbursement only
VALUES (p_payment_instruction_id,p_doctype,p_doc,1,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,
trxn_document_id_seq, p_payment_instruction_id);
* instead of inserting a new row in the table.
*
* Used in online financing.
*/
PROCEDURE CreateOrUpdateDocument
(
trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
doctype_in IN iby_trxn_documents.doctype%TYPE,
docid_out OUT NOCOPY iby_trxn_documents.trxn_document_id%TYPE
)
IS
l_docid iby_trxn_documents.trxn_document_id%TYPE;
SELECT trxn_document_id
FROM iby_trxn_documents
WHERE trxnmid = ci_trxnmid
AND doctype = ci_doctype;
UPDATE iby_trxn_documents SET
document=empty_clob()
WHERE
trxnmid = trxnmid_in
AND doctype = doctype_in;
END CreateOrUpdateDocument;
/* Delete a document based on the master trxn id of a trxn. */
PROCEDURE DeleteDocument
(
trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
doctype_in IN iby_trxn_documents.doctype%TYPE
)
IS
BEGIN
DELETE FROM iby_trxn_documents WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in);
END DeleteDocument;
PROCEDURE DeleteDocument
(
transactionid_in IN iby_trxn_summaries_all.transactionid%TYPE,
trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
status_in IN iby_trxn_summaries_all.status%TYPE,
doctype_in IN iby_trxn_documents.doctype%TYPE
)
IS
l_mtrxnid iby_trxn_summaries_all.trxnmid%TYPE;
DeleteDocument(l_mtrxnid,doctype_in);
END DeleteDocument;
-- not read only so we update the last change information
--
UPDATE iby_trxn_documents
SET object_version_number=object_version_number+1, last_update_date=sysdate,last_updated_by=fnd_global.user_id,last_update_login=fnd_global.login_id
WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in);
SELECT document INTO document_out
FROM iby_trxn_documents
WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in) FOR UPDATE;
-- must select as for update it to be able to write to it
-- from JDBC
ELSE
SELECT document INTO document_out
FROM iby_trxn_documents
WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in);
SELECT document INTO document_out
FROM iby_trxn_documents
WHERE (payment_instruction_id=p_payment_instruction_id) AND (doctype=p_doctype) and rownum = 1
ORDER BY trxn_document_id asc;
-- not read only so we update the last change information
--
UPDATE iby_trxn_documents
SET object_version_number=object_version_number+1, last_update_date=sysdate,last_updated_by=fnd_global.user_id,last_update_login=fnd_global.login_id
WHERE (payment_instruction_id=p_payment_instruction_id) AND (doctype=p_doctype);
SELECT document INTO document_out
FROM iby_trxn_documents
WHERE (payment_instruction_id=p_payment_instruction_id) AND (doctype=p_doctype) FOR UPDATE;
-- must select as for update it to be able to write to it
-- from JDBC
*/
ELSE
OPEN l_doc_csr(p_payment_instruction_id, p_doctype);