[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: delete_po_notif
===========================================================================*/
PROCEDURE delete_po_notif (x_document_type_code IN VARCHAR2,
x_object_id IN NUMBER) IS
x_progress VARCHAR2(3) := '';
SELECT po_release_id
FROM po_releases
WHERE po_header_id = x_object_id;
delete_notif_by_id_type(x_object_id, x_document_type_code);
po_notifications_sv2.delete_from_po_notif (x_document_type_code,
x_object_id);
-- Delete notifications from fnd_notifications.
x_progress := '040';
delete_notif_by_id_type(x_release_id, 'RELEASE');
-- Delete notifications from po_notifications
x_progress := '050';
po_notifications_sv2.delete_from_po_notif ('RELEASE',
x_release_id);
dbms_output.put_line('exception occurred in delete_po_notif ');
PO_MESSAGE_S.SQL_ERROR('DELETE_PO_NOTIF', x_progress, sqlcode);
PROCEDURE NAME: delete_notif_by_id_type
===========================================================================*/
PROCEDURE delete_notif_by_id_type(x_object_id NUMBER,
x_doc_type VARCHAR2) IS
x_progress VARCHAR2(3) := '';
SELECT notification_id
FROM fnd_notifications
WHERE object_id = x_object_id
AND doc_type = x_doc_type; */
-- Call the procedure to delete notifications.
ntn.delete_notification(x_notification_id, x_return_code);
dbms_output.put_line('exception occurred in delete_notif_by_id_type');
PO_MESSAGE_S.SQL_ERROR('DELETE_NOTIF_BY_ID_TYPE', x_progress, sqlcode);
SELECT po_release_id
FROM po_releases
WHERE po_header_id = x_object_id
AND nvl(cancel_flag,'N') = 'N';
delete_po_notif(x_document_type_code, x_object_id);
-- Insert new notification for releases against
-- this blanket.
x_progress := '020';
-- Insert the same notification into the po_notifications
-- table to keep the two tables in sync.
po_notifications_sv2.insert_into_po_notif (
x_forward_release_to,
x_message_name,
'RELEASE',
x_release_id,
x_doc_creation_date,
x_start_date,
NULL);
-- Insert the same notification into the po_notification table
-- to keep the two tables in sync.
po_notifications_sv2.insert_into_po_notif (
x_employee_id,
x_message_name,
x_document_type_code,
x_object_id,
x_doc_creation_date,
x_start_date,
x_end_date_active);
-- If document is on hold, insert an 'ON_HOLD' notification.
-- If document is APPROVED, insert an 'ACCEPTANCE_PAST_DUE'
-- notification if acceptance is required.
-- For other document statuses, insert the following types
-- of notifications:
-- Document Status Message Name
-- --------------- -------------
-- REQUIRES REAPPROVAL REQUIRES_REAPPROVAL
-- REJECTED REJECTED_BY_APPROVER
-- PRE-APPROVED AWAITING_YOUR_APPROVAL
-- IN PROCESS AWAITING_YOUR_APPROVAL
-- NEVER APPROVED NEVER_APPROVED
-- RETURNED REJECTED_BY_PURCHASING
-- If no notification is required for this document, message_name
-- will be returned with NULL value.
BEGIN
IF x_document_type_code IN ('RELEASE', 'SCHEDULED') THEN
x_progress := '020';
SELECT DECODE(x_forward_to_id,
NULL, pr.agent_id,
x_forward_to_id),
pr.agent_id,
ph.comments,
ph.segment1,
pr.release_num,
pr.creation_date,
pr.agent_id,
he.full_name,
nvl(pr.authorization_status, 'INCOMPLETE'),
ph.currency_code,
DECODE(pr.acceptance_required_flag,
'Y', pr.acceptance_due_date, NULL),
NULL,
NULL,
DECODE(PR.HOLD_FLAG,
'Y', 'ON_HOLD',
DECODE(PR.AUTHORIZATION_STATUS,
'APPROVED', DECODE(pr.acceptance_required_flag,
'Y', 'ACCEPTANCE_PAST_DUE',
NULL),
'REQUIRES REAPPROVAL', 'REQUIRES_REAPPROVAL',
'REJECTED', 'REJECTED_BY_APPROVER',
'PRE-APPROVED', 'AWAITING_YOUR_APPROVAL',
'IN PROCESS', 'AWAITING_YOUR_APPROVAL',
'NEVER_APPROVED'))
INTO x_forward_to_id,
x_doc_owner_id,
x_attribute_array(DESCRIPTION),
x_attribute_array(SEGMENT1),
x_attribute_array(RELEASE_NUM),
x_doc_creation_date,
x_attribute_array(OWNER_ID),
x_attribute_array(DOC_OWNER),
x_attribute_array(AUTHORIZATION_STATUS),
x_currency_code,
x_acceptance_due_date,
x_expiration_date,
x_close_date,
x_message_name
FROM PO_RELEASES PR,
PO_HEADERS PH,
HR_EMPLOYEES HE
WHERE NVL(PR.CANCEL_FLAG,'N') = 'N'
AND pr.po_release_id = x_object_id
AND pr.po_header_id = ph.po_header_id
AND pr.agent_id = he.employee_id;
SELECT DECODE(x_forward_to_id,
NULL, ph.agent_id,
x_forward_to_id),
ph.agent_id,
ph.comments,
ph.segment1,
NULL,
ph.creation_date,
ph.agent_id,
he.full_name,
nvl(ph.authorization_status, 'INCOMPLETE'),
ph.currency_code,
DECODE(ph.acceptance_required_flag,
'Y', ph.acceptance_due_date, NULL),
DECODE(x_document_type_code,
'QUOTATION', ph.end_date,
NULL),
DECODE(x_document_type_code,
'RFQ', ph.rfq_close_date,
NULL),
DECODE(x_document_type_code,
'RFQ',DECODE(ph.STATUS_LOOKUP_CODE,
'I','REQUIRES_COMPLETION',
DECODE(x_end_date_active,
NULL,'AWAITING_REPLIES',
'NEAR_CLOSE')),
'QUOTATION',DECODE(ph.STATUS_LOOKUP_CODE,
'I','REQUIRES_COMPLETION',
DECODE(x_end_date_active,
NULL,'ACTIVE',
'NEAR_EXPIRATION')),
DECODE(ph.USER_HOLD_FLAG,
'Y', 'ON_HOLD',
DECODE(PH.AUTHORIZATION_STATUS,
'APPROVED', DECODE(ph.acceptance_required_flag,
'Y', 'ACCEPTANCE_PAST_DUE',
NULL),
'REQUIRES REAPPROVAL', 'REQUIRES_REAPPROVAL',
'REJECTED', 'REJECTED_BY_APPROVER',
'IN PROCESS', 'AWAITING_YOUR_APPROVAL',
'PRE-APPROVED', 'AWAITING_YOUR_APPROVAL',
'NEVER_APPROVED')))
INTO x_forward_to_id,
x_doc_owner_id,
x_attribute_array(DESCRIPTION),
x_attribute_array(SEGMENT1),
x_attribute_array(RELEASE_NUM),
x_doc_creation_date,
x_attribute_array(OWNER_ID),
x_attribute_array(DOC_OWNER),
x_attribute_array(AUTHORIZATION_STATUS),
x_currency_code,
x_acceptance_due_date,
x_expiration_date,
x_close_date,
x_message_name
FROM PO_HEADERS ph,
hr_employees he
WHERE NVL(ph.CANCEL_FLAG,'N') != 'Y'
AND NVL(ph.STATUS_LOOKUP_CODE, 'I') != 'C'
AND po_header_id = x_object_id
AND he.employee_id = ph.agent_id;
SELECT DECODE(x_forward_to_id,
NULL, prh.preparer_id,
x_forward_to_id),
prh.preparer_id,
prh.description,
prh.segment1,
NULL,
prh.creation_date,
prh.preparer_id,
he.full_name,
nvl(prh.authorization_status, 'INCOMPLETE'),
NULL,
NULL,
NULL,
DECODE(prh.authorization_status,
'REJECTED', 'REJECTED_BY_APPROVER',
'RETURNED', 'REJECTED_BY_PURCHASING',
'APPROVED', NULL,
'IN PROCESS', 'AWAITING_YOUR_APPROVAL',
'PRE-APPROVED', 'AWAITING_YOUR_APPROVAL',
'NEVER_APPROVED')
INTO x_forward_to_id,
x_doc_owner_id,
x_attribute_array(DESCRIPTION),
x_attribute_array(SEGMENT1),
x_attribute_array(RELEASE_NUM),
x_doc_creation_date,
x_attribute_array(OWNER_ID),
x_attribute_array(DOC_OWNER),
x_attribute_array(AUTHORIZATION_STATUS),
x_acceptance_due_date,
x_expiration_date,
x_close_date,
x_message_name
FROM po_requisition_headers prh,
hr_employees he
WHERE NVL(CANCEL_FLAG,'N') = 'N'
AND requisition_header_id = x_object_id
AND he.employee_id = prh.preparer_id;
dbms_output.put_line('Exception occurred when selecting from header table');
-- unapproved and the notification for the release is updated) and
-- for notifications that are transfered from po_notifications.
dbms_output.put_line('before select from po_action_history');
SELECT poa.employee_id,
poa.note
INTO x_from_id,
x_note
FROM po_action_history poa
WHERE poa.object_type_code = x_type
AND poa.object_id = x_object_id
AND poa.sequence_num = (SELECT max(sequence_num)
FROM po_action_history pv
WHERE pv.object_type_code = poa.object_type_code
AND pv.object_id = poa.object_id
AND pv.action_code IN ('FORWARD',
'SUBMIT', 'REJECT', 'RETURN','APPROVE'));
-- Bug 412292: For reqimport, insert into po_action_history
-- occurs after insert into po_notifications, so we cannot get
-- the forward from person from po_action_history.
x_from_id := x_doc_owner_id;
-- unapproved and the notification for the release is updated).
BEGIN
SELECT employee_id
INTO x_forward_to_id
FROM po_action_history
WHERE object_type_code = x_type
AND object_id = x_object_id
AND action_code IS NULL;
dbms_output.put_line('before select from po_lookup_codes');
SELECT displayed_field
INTO x_attribute_array(AUTHORIZATION_STATUS_DISP)
FROM po_lookup_codes
WHERE lookup_type = 'AUTHORIZATION STATUS'
AND lookup_code = x_attribute_array(AUTHORIZATION_STATUS);