[Home] [Help]
1: PACKAGE BODY PO_REQUISITION_HEADERS_PKG as
2: /* $Header: POXRIH1B.pls 120.5 2006/04/27 15:26:16 bao noship $ */
3:
4:
5: PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
55: X_Closed_Code VARCHAR2,
56: X_Manual BOOLEAN,
57: p_org_id IN NUMBER DEFAULT NULL --
58: ) IS
59: CURSOR C IS SELECT rowid FROM PO_REQUISITION_HEADERS
60: WHERE requisition_header_id = X_Requisition_Header_Id;
61:
62:
63: CURSOR S IS SELECT po_requisition_headers_s.nextval FROM sys.dual;
59: CURSOR C IS SELECT rowid FROM PO_REQUISITION_HEADERS
60: WHERE requisition_header_id = X_Requisition_Header_Id;
61:
62:
63: CURSOR S IS SELECT po_requisition_headers_s.nextval FROM sys.dual;
64:
65: /* Ben: bug#465696 Locking the po_unique_identifier_control table at this
66: point of the form commit cycle is causing the performance problem.
67: It may take 5 to 10 seconds to commit a PO with many lines, shipments
66: point of the form commit cycle is causing the performance problem.
67: It may take 5 to 10 seconds to commit a PO with many lines, shipments
68: and distributions.
69: The solution is to insert a bogus value into the SEGMENT1 column
70: of po_requisition_headers ( the negative of po_requisition_header)
71: then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
72: trigger on the form, update the po_requisition_headers table with
73: the real SEGMENT1 value from the po_unique_identifier_control table.
74: The advantage of this approach is that the
68: and distributions.
69: The solution is to insert a bogus value into the SEGMENT1 column
70: of po_requisition_headers ( the negative of po_requisition_header)
71: then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
72: trigger on the form, update the po_requisition_headers table with
73: the real SEGMENT1 value from the po_unique_identifier_control table.
74: The advantage of this approach is that the
75: po_unique_identifier_control will be locked for only a short period
76: of time.
77: THEREFORE, taking the S1 cursor out of the logic here.
78:
79: CURSOR S1 IS SELECT to_char(current_max_unique_identifier + 1)
80: FROM po_unique_identifier_control
81: WHERE table_name = 'PO_REQUISITION_HEADERS'
82: FOR UPDATE OF current_max_unique_identifier;
83:
84: */
85:
110:
111: END IF;
112: x_progress := '020';
113:
114: po_requisition_headers_pkg.check_unique (X_rowid, X_segment1);
115:
116: x_progress := '030';
117:
118: INSERT INTO PO_REQUISITION_HEADERS(
114: po_requisition_headers_pkg.check_unique (X_rowid, X_segment1);
115:
116: x_progress := '030';
117:
118: INSERT INTO PO_REQUISITION_HEADERS(
119: requisition_header_id,
120: preparer_id,
121: last_update_date,
122: last_updated_by,
247: END Insert_Row;
248:
249: PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
250: BEGIN
251: DELETE FROM PO_REQUISITION_HEADERS
252: WHERE rowid = X_Rowid;
253:
254: if (SQL%NOTFOUND) then
255: Raise NO_DATA_FOUND;
269: beginning of the form commit cycle is causing the performance problem.
270: It may take 5 to 10 seconds to commit a PO with many lines, shipments
271: and distributions.
272: The solution is to insert a bogus value into the SEGMENT1 column
273: of po_requisition_headers ( the negative of po_requisition_header)
274: during the ON-INSERT trigger on the PO_HEADERS,
275: then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
276: trigger on the form, update the po_requisition_headers table with
277: the real SEGMENT1 value from the po_unique_identifier_control table.
272: The solution is to insert a bogus value into the SEGMENT1 column
273: of po_requisition_headers ( the negative of po_requisition_header)
274: during the ON-INSERT trigger on the PO_HEADERS,
275: then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
276: trigger on the form, update the po_requisition_headers table with
277: the real SEGMENT1 value from the po_unique_identifier_control table.
278: The advantage of this approach is that the
279: po_unique_identifier_control will be locked for only a short period
280: of time.
288: -- bug5176308 START
289: -- Call API to get the po number
290: x_segment1 :=
291: PO_CORE_SV1.default_po_unique_identifier
292: ( x_table_name => 'PO_REQUISITION_HEADERS'
293: );
294: -- bug5176308 END
295:
296:
293: );
294: -- bug5176308 END
295:
296:
297: UPDATE po_requisition_headers set segment1=x_segment1
298: where requisition_header_id=x_requisition_header_id;
299:
300: /* bug# 465696 8/5/97. The previous fix to this performance problem introduced
301: a problem with the notifications (the bogus value used temporarily as the
334: SELECT 1 INTO dummy
335: FROM DUAL
336: WHERE NOT EXISTS
337: ( SELECT 1
338: FROM po_requisition_headers
339: WHERE Segment1 = X_Segment1
340: AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID)))
341: AND NOT EXISTS
342: ( SELECT 1
431:
432:
433: /* End Bug3406460 */
434:
435: END PO_REQUISITION_HEADERS_PKG;