[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Requisition_Header_Id IN OUT NOCOPY NUMBER,
X_Preparer_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Segment1 IN OUT NOCOPY VARCHAR2,
X_Summary_Flag VARCHAR2,
X_Enabled_Flag VARCHAR2,
X_Segment2 VARCHAR2,
X_Segment3 VARCHAR2,
X_Segment4 VARCHAR2,
X_Segment5 VARCHAR2,
X_Start_Date_Active DATE,
X_End_Date_Active DATE,
X_Last_Update_Login NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Description VARCHAR2,
X_Authorization_Status VARCHAR2,
X_Note_To_Authorizer VARCHAR2,
X_Type_Lookup_Code VARCHAR2,
X_Transferred_To_Oe_Flag VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_On_Line_Flag VARCHAR2,
X_Preliminary_Research_Flag VARCHAR2,
X_Research_Complete_Flag VARCHAR2,
X_Preparer_Finished_Flag VARCHAR2,
X_Preparer_Finished_Date DATE,
X_Agent_Return_Flag VARCHAR2,
X_Agent_Return_Note VARCHAR2,
X_Cancel_Flag VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Government_Context VARCHAR2,
X_Interface_Source_Code VARCHAR2,
X_Interface_Source_Line_Id NUMBER,
X_Closed_Code VARCHAR2,
X_Manual BOOLEAN,
p_org_id IN NUMBER DEFAULT NULL --
) IS
CURSOR C IS SELECT rowid FROM PO_REQUISITION_HEADERS
WHERE requisition_header_id = X_Requisition_Header_Id;
CURSOR S IS SELECT po_requisition_headers_s.nextval FROM sys.dual;
The solution is to insert a bogus value into the SEGMENT1 column
of po_requisition_headers ( the negative of po_requisition_header)
then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
trigger on the form, update the po_requisition_headers table with
the real SEGMENT1 value from the po_unique_identifier_control table.
The advantage of this approach is that the
po_unique_identifier_control will be locked for only a short period
of time.
THEREFORE, taking the S1 cursor out of the logic here.
CURSOR S1 IS SELECT to_char(current_max_unique_identifier + 1)
FROM po_unique_identifier_control
WHERE table_name = 'PO_REQUISITION_HEADERS'
FOR UPDATE OF current_max_unique_identifier;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier
= current_max_unique_identifier + 1
WHERE CURRENT of S1;
INSERT INTO PO_REQUISITION_HEADERS(
requisition_header_id,
preparer_id,
last_update_date,
last_updated_by,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
description,
authorization_status,
note_to_authorizer,
type_lookup_code,
transferred_to_oe_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
on_line_flag,
preliminary_research_flag,
research_complete_flag,
preparer_finished_flag,
preparer_finished_date,
agent_return_flag,
agent_return_note,
cancel_flag,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
government_context,
interface_source_code,
interface_source_line_id,
closed_code,
Org_Id, --
tax_attribute_update_code --
) VALUES (
X_Requisition_Header_Id,
X_Preparer_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Segment1,
X_Summary_Flag,
X_Enabled_Flag,
X_Segment2,
X_Segment3,
X_Segment4,
X_Segment5,
X_Start_Date_Active,
X_End_Date_Active,
X_Last_Update_Login,
X_Creation_Date,
X_Created_By,
X_Description,
X_Authorization_Status,
X_Note_To_Authorizer,
X_Type_Lookup_Code,
X_Transferred_To_Oe_Flag,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_On_Line_Flag,
X_Preliminary_Research_Flag,
X_Research_Complete_Flag,
X_Preparer_Finished_Flag,
X_Preparer_Finished_Date,
X_Agent_Return_Flag,
X_Agent_Return_Note,
X_Cancel_Flag,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Government_Context,
X_Interface_Source_Code,
X_Interface_Source_Line_Id,
X_Closed_Code,
p_org_id, --
'CREATE' --
);
po_message_s.sql_error('INSERT_ROW',x_progress,sqlcode);
END Insert_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PO_REQUISITION_HEADERS
WHERE rowid = X_Rowid;
END Delete_Row;
The solution is to insert a bogus value into the SEGMENT1 column
of po_requisition_headers ( the negative of po_requisition_header)
during the ON-INSERT trigger on the PO_HEADERS,
then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
trigger on the form, update the po_requisition_headers table with
the real SEGMENT1 value from the po_unique_identifier_control table.
The advantage of this approach is that the
po_unique_identifier_control will be locked for only a short period
of time.
This procedure gets called from the POST_FORMS-COMMIT trigger
*/
BEGIN
-- bug5176308 START
-- Call API to get the po number
x_segment1 :=
PO_CORE_SV1.default_po_unique_identifier
( x_table_name => 'PO_REQUISITION_HEADERS'
);
UPDATE po_requisition_headers set segment1=x_segment1
where requisition_header_id=x_requisition_header_id;
document number was being inserted into the fnd_notifications table, since
the call below was made before we called the procedure to get the real
document number (segment1) .
Therefore, removed the call below from po_reqs_sv.insert_row and moved it to
here.
*/
/*hvadlamu : commenting out since notifications will be handled by workflow */
/*po_notifications_sv1.send_po_notif (x_type_lookup_code,
x_requisition_header_id,
x_currency_code,
null,
null,
null,
null,
null); */
SELECT 1 INTO dummy
FROM DUAL
WHERE NOT EXISTS
( SELECT 1
FROM po_requisition_headers
WHERE Segment1 = X_Segment1
AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID)))
AND NOT EXISTS
( SELECT 1
FROM po_history_requisitions phr
WHERE phr.segment1 = X_Segment1);
SELECT nvl(SUM(decode(quantity,
null,
amount,
(quantity * unit_price)
)
), 0)
into X_req_total
FROM po_requisition_lines
WHERE requisition_header_id = p_header_id and
nvl(cancel_flag,'N') <> 'Y' and -- Bug 554452 Ignore cancelled lines
nvl(MODIFIED_BY_AGENT_FLAG, 'N') = 'N' and -- Bug 574676
nvl(CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'; -- Bug 574676
SELECT nvl ( sum ( round ( decode ( order_type_lookup_code
, 'FIXED PRICE' , amount
, 'RATE' , amount
, quantity*unit_price
)
, l_precision
)
)
, 0
)
--
into X_req_total
FROM po_requisition_lines
WHERE requisition_header_id = p_header_id and
nvl(cancel_flag,'N') <> 'Y' and -- Bug 554452 Ignore cancelled lines
nvl(MODIFIED_BY_AGENT_FLAG, 'N') = 'N' and -- Bug 574676
nvl(CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'; -- Bug 574676