[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: lock_row_for_status_update
===========================================================================*/
PROCEDURE lock_row_for_status_update (x_requisition_header_id IN NUMBER)
IS
CURSOR C IS
SELECT *
FROM po_requisition_headers
WHERE requisition_header_id = x_requisition_header_id
FOR UPDATE of requisition_header_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_STATUS_UPDATE', x_progress, sqlcode);
PROCEDURE NAME: update_reqs_header_status
===========================================================================*/
PROCEDURE update_reqs_header_status
(X_req_header_id IN NUMBER,
X_req_line_id IN NUMBER,
X_req_control_action IN VARCHAR2,
X_req_control_reason IN VARCHAR2,
X_req_action_history_code IN OUT NOCOPY VARCHAR2,
X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
X_progress VARCHAR2(3) := NULL;
SELECT COUNT(1),
nvl(sum(decode(PORL.line_location_id,NULL,0,1)),0)
INTO X_req_has_open_line, X_req_has_open_shipment
FROM PO_REQUISITION_LINES PORL
WHERE PORL.requisition_header_id = X_req_header_id
AND nvl(PORL.cancel_flag, 'N') IN ('N', 'I')
AND nvl(PORL.closed_code, 'OPEN') <> 'FINALLY CLOSED';
/* Requisition still has open lines. Do not update
** requisition header.
*/
X_authorization_status := NULL;
UPDATE PO_REQUISITION_HEADERS
SET authorization_status = nvl(X_authorization_status, authorization_status),
closed_code = nvl(X_closed_code, closed_code),
contractor_status = decode(X_authorization_status,'CANCELLED',null,
contractor_status), -- Bug 3495679
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate
WHERE requisition_header_id = X_req_header_id;
po_message_s.sql_error('update_reqs_header_status', X_progress, sqlcode);
po_message_s.sql_error('update_reqs_header_status', X_progress, sqlcode);
END update_reqs_header_status;
cursor c1 is SELECT 'Y'
FROM po_req_distributions
WHERE requisition_line_id
IN (SELECT requisition_line_id
FROM po_requisition_lines
WHERE requisition_header_id = X_req_hdr_id)
AND nvl(encumbered_flag,'N') <> 'N';
PROCEDURE NAME: val_req_delete()
===========================================================================*/
FUNCTION val_req_delete(X_req_hdr_id IN NUMBER)
return boolean is
X_allow_delete boolean;
X_allow_delete := FALSE;
X_allow_delete := TRUE;
return(X_allow_delete);
X_allow_delete := FALSE;
po_message_s.sql_error('val_req_delete', x_progress, sqlcode);
END val_req_delete;
PROCEDURE NAME: delete_children
===========================================================================*/
PROCEDURE delete_children(X_req_hdr_id IN NUMBER) IS
x_progress VARCHAR2(3) := NULL;
CURSOR S IS SELECT requisition_line_id
FROM po_requisition_lines
WHERE requisition_header_id = X_req_hdr_id;
DELETE FROM po_req_distributions
WHERE requisition_line_id = Srec.requisition_line_id;
fnd_attached_documents2_pkg.delete_attachments('REQ_LINE',
Srec.requisition_line_id,
'',
'',
'',
'',
'Y');
DELETE FROM po_requisition_lines
WHERE requisition_line_id = Srec.requisition_line_id;
po_message_s.sql_error('delete_children', x_progress, sqlcode);
END delete_children;
PROCEDURE NAME: delete_req
===========================================================================*/
PROCEDURE delete_req(X_req_hdr_id IN NUMBER) IS
x_progress VARCHAR2(3) := NULL;
x_allow_delete BOOLEAN;
SELECT type_lookup_code
INTO x_type_lookup_code
FROM po_requisition_headers
WHERE requisition_header_id = X_req_hdr_id;
/* Validate if the Document can be deleted */
x_allow_delete := val_req_delete(X_req_hdr_id);
/* If the Documnet can be deleted */
IF (x_allow_delete) THEN
/*
** Delete the notification.
**/
x_progress := '020';
/* hvadlamu : commnting out the delete and adding the WorkFlow call */
SELECT wf_item_type,wf_item_key
INTO x_item_type,x_item_key
FROM PO_REQUISITION_HEADERS
WHERE requisition_header_id = x_req_hdr_id;
po send notification items are deleted.
when trying to delete a requisition it could be that it was submitted to
approval workflow and was never approved and also po send notification
was also invoked for it,in which case we need to stop the approval
workflow as well as the reminder workflow */
po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
x_req_hdr_id);
/* Bug 2904413 Need to delete the action history also */
Delete po_action_history
Where OBJECT_TYPE_CODE = 'REQUISITION' and
OBJECT_SUB_TYPE_CODE = x_type_lookup_code and
OBJECT_ID = x_req_hdr_id;
/* po_notifications_sv1.delete_po_notif (x_type_lookup_code,
x_req_hdr_id); */
SELECT rowid
INTO x_rowid
FROM po_requisition_headers
WHERE requisition_header_id = X_req_hdr_id;
** Delete all the distributions and lines
** for this requisition header.
*/
x_progress := '040';
po_reqs_sv.delete_children(X_req_hdr_id);
** Delete the attachments.
*/
x_progress := '050';
fnd_attached_documents2_pkg.delete_attachments('REQ_HEADER',
x_req_hdr_id,
'',
'',
'',
'',
'Y');
** Delete the requisition header.
*/
x_progress := '060';
po_requisition_headers_pkg.delete_row(X_rowid);
po_message_s.sql_error('delete_req', x_progress, sqlcode);
END delete_req;
PROCEDURE NAME: insert_req()
===========================================================================*/
PROCEDURE insert_req(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,
X_amount NUMBER,
X_currency_code VARCHAR2,
p_org_id IN NUMBER default null --
) IS
x_progress VARCHAR2(3) := NULL;
po_requisition_headers_pkg.insert_row(X_Rowid,
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,
NULL, --
X_Government_Context,
X_Interface_Source_Code,
X_Interface_Source_Line_Id,
X_Closed_Code,
X_Manual,
p_org_id --
);
** DEBUG. Call the routine to insert
** notifications.
*/
x_progress := '020';
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) in the POST-FORMS-COMMIT trigger.
Therefore, remove the call below from here and moving it to procedure
PO_REQUISITION_HEADERS_PKG.get_real_segment1.
*/
IF X_Manual THEN
/*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); */
po_message_s.sql_error('insert_req', x_progress, sqlcode);
END insert_req;
PROCEDURE NAME: update_oe_flag
===========================================================================*/
PROCEDURE update_oe_flag(X_req_hdr_id IN NUMBER,
X_flag IN VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
UPDATE po_requisition_headers
SET transferred_to_oe_flag = X_flag
WHERE requisition_header_id = X_req_hdr_id;
po_message_s.sql_error('update_oe_flag', x_progress, sqlcode);
END update_oe_flag;