[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_auth_action_history(
p_document_id IN NUMBER
, p_revision_num IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_draft_id IN NUMBER --CLM Apprvl
, p_action IN VARCHAR2
, p_employee_id IN NUMBER
, p_offline_code IN VARCHAR2
, p_approval_path_id IN NUMBER
, p_note IN VARCHAR2
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE update_auth_action_history(
p_document_id IN NUMBER
, p_revision_num IN NUMBER
, p_document_type IN VARCHAR2
, p_draft_id IN NUMBER --CLM Apprvl
, p_action IN VARCHAR2
, p_approval_path_id IN NUMBER
, p_note IN VARCHAR2
, p_user_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE update_doc_notifications(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_notify_action IN VARCHAR2
, p_notify_employee IN NUMBER
, p_doc_creation_date IN DATE
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
);
SELECT nvl(por.authorization_status, 'INCOMPLETE'),
nvl(por.closed_code, 'OPEN'),
nvl(por.frozen_flag, 'N'),
nvl(por.hold_flag, 'N')
FROM po_releases por
WHERE por.po_release_id = docid;
SELECT nvl(poh.authorization_status, 'INCOMPLETE'),
nvl(poh.closed_code, 'OPEN'),
nvl(poh.frozen_flag, 'N'),
nvl(poh.user_hold_flag, 'N')
FROM po_headers poh
WHERE poh.po_header_id = docid;
SELECT nvl(phm.status, 'DRAFT'),
nvl(phm.closed_code, 'OPEN'),
nvl(phm.frozen_flag, 'N'),
nvl(phm.user_hold_flag, 'N')
FROM po_headers_merge_v phm
WHERE phm.po_header_id = docid
and phm.draft_id= draftId;
SELECT nvl(prh.authorization_status, 'INCOMPLETE'),
nvl(prh.closed_code, 'OPEN')
FROM po_requisition_headers prh
WHERE prh.requisition_header_id = docid;
SELECT nvl(poll.closed_code, 'OPEN')
FROM po_line_locations poll
WHERE poll.line_location_id = shipid;
SELECT nvl(poll.closed_code, 'OPEN')
FROM po_line_locations_merge_v poll
WHERE poll.line_location_id = shipid
AND poll.draft_id = draftId;
SELECT nvl(pol.closed_code, 'OPEN')
FROM po_lines pol
WHERE pol.po_line_id = lineid;
SELECT nvl(pol.closed_code, 'OPEN')
FROM po_lines_merge_v pol
WHERE pol.po_line_id = lineid
AND pol.draft_id = draftId;
SELECT por.agent_id
INTO x_preparer_id
FROM po_releases_all por
WHERE por.po_release_id = p_document_id;
SELECT poh.agent_id
INTO x_preparer_id
FROM po_headers_merge_v poh
WHERE poh.po_header_id = p_document_id
AND poh.draft_id = p_draft_id;
SELECT poh.agent_id
INTO x_preparer_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_document_id;
SELECT porh.preparer_id
INTO x_preparer_id
FROM po_requisition_headers_all porh
WHERE porh.requisition_header_id = p_document_id;
SELECT hr.person_id, hr.full_name, hr.location_id
INTO x_employee_id, x_employee_name, x_location_id
FROM FND_USER fnd, PO_WORKFORCE_CURRENT_X hr --
WHERE fnd.user_id = p_user_id
AND fnd.employee_id = hr.person_id;
SELECT hr.location_code
INTO x_location_code
FROM HR_LOCATIONS hr,
FINANCIALS_SYSTEM_PARAMETERS fsp,
ORG_ORGANIZATION_DEFINITIONS ood
WHERE hr.location_id = x_location_id
AND hr.inventory_organization_id = ood.organization_id (+)
AND nvl(ood.set_of_books_id, fsp.set_of_books_id) = fsp.set_of_books_id;
SELECT 'X'
INTO l_temp_var
FROM po_agents poa
WHERE poa.agent_id = x_employee_id
AND SYSDATE between nvl(poa.start_date_active, SYSDATE - 1) and NVL(poa.end_date_active, SYSDATE + 1);
SELECT hr.person_id
INTO x_employee_id
FROM FND_USER fnd, PER_WORKFORCE_CURRENT_X hr --R12 CWK Enhancement
WHERE fnd.user_id = p_user_id
AND fnd.employee_id = hr.person_id;
SELECT porh.authorization_status, porh.creation_date, 0
INTO l_old_status, l_creation_date, l_revision_num
FROM PO_REQUISITION_HEADERS porh
WHERE porh.requisition_header_id = p_document_id;
SELECT NVL(poh.status, 'DRAFT'),
poh.creation_date,
poh.revision_num
INTO l_old_status,
l_creation_date,
l_revision_num
FROM PO_HEADERS_MERGE_V poh
WHERE poh.po_header_id = p_document_id
AND poh.draft_id = p_draft_id;
SELECT NVL(poh.authorization_status, PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE),
poh.creation_date,
poh.revision_num
INTO l_old_status,
l_creation_date,
l_revision_num
FROM PO_HEADERS poh
WHERE poh.po_header_id = p_document_id;
SELECT NVL(por.authorization_status, PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE),
por.creation_date,
por.revision_num
INTO l_old_status,
l_creation_date,
l_revision_num
FROM PO_RELEASES por
WHERE por.po_release_id = p_document_id; --
update_doc_auth_status(
p_document_id => p_document_id
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_draft_id => p_draft_id --CLM Apprvl
, p_new_status => p_new_status
, p_user_id => l_user_id
, p_login_id => l_login_id
, x_return_status => l_ret_sts
);
l_err_msg := 'update_doc_auth_status not successful';
UPDATE po_action_history poah
SET poah.action_code = p_action
, poah.action_date = SYSDATE
, poah.offline_code = NULL
, poah.employee_id = l_emp_id
, poah.note = DECODE(p_shipment_id, NULL,
DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
l_rollup_msg)
, poah.last_updated_by = p_user_id
, poah.last_update_date = SYSDATE
WHERE poah.object_id = p_document_id
AND poah.object_type_code = p_document_type
AND poah.object_sub_type_code = p_document_subtype
AND poah.action_code IS NULL;
UPDATE po_action_history poah
SET poah.sequence_num = poah.sequence_num + 1
WHERE poah.object_id = p_document_id
AND poah.object_type_code = p_document_type
AND poah.object_sub_type_code = p_document_subtype
AND poah.action_code IS NULL;
SELECT count(1)
INTO l_count_hist
FROM po_action_history poah
WHERE poah.object_id = p_document_id
AND poah.object_type_code = p_document_type
AND poah.object_sub_type_code = p_document_subtype
AND poah.action_code IS NOT NULL;
INSERT INTO po_action_history(
object_id
, object_type_code
, object_sub_type_code
, sequence_num
, last_update_date
, last_updated_by
, creation_date
, created_by
, action_code
, action_date
, employee_id
, note
, object_revision_num
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, approval_path_id
, offline_code
)
SELECT
poah.object_id
, poah.object_type_code
, poah.object_sub_type_code
, max(poah.sequence_num) + 1
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_action
, SYSDATE
, l_emp_id
, DECODE(p_shipment_id,
NULL, DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
l_rollup_msg)
, max(poah.object_revision_num)
, p_login_id
, 0
, 0
, 0
, ''
, 0
, ''
FROM po_action_history poah
WHERE poah.object_id = p_document_id
AND poah.object_type_code = p_document_type
AND poah.object_sub_type_code = p_document_subtype
AND poah.action_code IS NOT NULL
GROUP BY poah.object_id
, poah.object_type_code
, poah.object_sub_type_code
;
SELECT max(poh.revision_num)
INTO l_revision_num
FROM po_headers_all poh
WHERE poh.po_header_id = p_document_id;
INSERT INTO po_action_history(
object_id
, object_type_code
, object_sub_type_code
, sequence_num
, last_update_date
, last_updated_by
, creation_date
, created_by
, action_code
, action_date
, employee_id
, note
, object_revision_num
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, approval_path_id
, offline_code
) VALUES (
p_document_id
, p_document_type
, p_document_subtype
, 1 -- Bug 13579433, sequence_num starts at 1
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_action
, SYSDATE
, l_emp_id
, DECODE(p_shipment_id,
NULL, DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
l_rollup_msg)
, l_revision_num
, p_login_id
, 0
, 0
, 0
, ''
, 0
, ''
);
insert_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => 'SUBMIT'
, p_employee_id => p_employee_id
, p_offline_code => NULL
, p_approval_path_id => p_approval_path_id
, p_note => p_note
, p_user_id => p_user_id
, p_login_id => p_login_id
, x_return_status => l_ret_sts
);
insert_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => p_action
, p_employee_id => p_employee_id
, p_offline_code => NULL
, p_approval_path_id => p_approval_path_id
, p_note => p_note
, p_user_id => p_user_id
, p_login_id => p_login_id
, x_return_status => l_ret_sts
);
update_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => p_action
, p_approval_path_id => p_approval_path_id
, p_note => p_note
, p_user_id => p_user_id
, x_return_status => l_ret_sts
);
insert_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => p_action
, p_employee_id => p_employee_id
, p_offline_code => NULL
, p_approval_path_id => p_approval_path_id
, p_note => p_note
, p_user_id => p_user_id
, p_login_id => p_login_id
, x_return_status => l_ret_sts
);
insert_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => NULL
, p_employee_id => p_fwd_to_id
, p_offline_code => p_offline_code
, p_approval_path_id => NULL
, p_note => NULL
, p_user_id => p_user_id
, p_login_id => p_login_id
, x_return_status => l_ret_sts
);
update_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE
, p_approval_path_id => p_approval_path_id
, p_note => p_note
, p_user_id => p_user_id
, x_return_status => l_ret_sts
);
insert_auth_action_history(
p_document_id => p_document_id
, p_revision_num => p_revision_num
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_draft_id => p_draft_id --CLM Apprvl
, p_action => NULL
, p_employee_id => p_employee_id
, p_offline_code => NULL
, p_approval_path_id => NULL
, p_note => NULL
, p_user_id => p_user_id
, p_login_id => p_login_id
, x_return_status => l_ret_sts
);
PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Insert or update action history not successful');
PO_LOG.exc(d_module, d_progress, 'Insert or update action history not successful');
PROCEDURE update_doc_auth_status(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_draft_id IN NUMBER --CLM Apprvl
, p_new_status IN VARCHAR2
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_conterms_exist PO_HEADERS.conterms_exist_flag%TYPE;
d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_doc_auth_status';
UPDATE PO_REQUISITION_HEADERS porh
SET porh.authorization_status = p_new_status
, porh.approved_date = DECODE (p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
SYSDATE,null)
, porh.last_update_date = SYSDATE
, porh.last_updated_by = p_user_id
, porh.last_update_login = p_login_id
WHERE porh.requisition_header_id = p_document_id;
SELECT conformed_header_id INTO l_conformed_id
FROM po_requisition_headers_all
WHERE requisition_header_id = p_document_id;
PO_REQ_LINES_SV.update_reqs_in_pool_flag(
x_req_line_id => NULL
, x_req_header_id => p_document_id
, x_return_status => l_ret_sts
);
PO_REQ_LINES_SV.update_reqs_in_pool_flag(
x_req_line_id => NULL
, x_req_header_id => l_conformed_id
, x_return_status => l_ret_sts
);
l_err_msg := 'update_reqs_in_pool_flag not successful';
/* UPDATE PO_HEADERS_DRAFT_ALL poh
SET poh.authorization_status = p_new_status,
poh.approved_flag = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
poh.approved_date = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
poh.approved_date),
poh.last_update_date = SYSDATE,
poh.last_updated_by = p_user_id,
poh.last_update_login = p_login_id,
poh.pending_signature_flag = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
DECODE(poh.acceptance_required_flag, 'S', 'Y', 'N'),
poh.pending_signature_flag)
WHERE poh.po_header_id = p_document_id
AND poh.draft_id = p_draft_id;*/
UPDATE PO_DRAFTS
SET status = decode(p_new_status,'APPROVED','COMPLETED',p_new_status)
WHERE draft_id = p_draft_id;
UPDATE PO_HEADERS poh
SET poh.authorization_status = p_new_status,
poh.approved_flag = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
poh.approved_date = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE, poh.approved_date),
poh.clm_effective_date = DECODE(poh.clm_effective_date,
NULL,DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
poh.approved_date),
poh.clm_effective_date),
poh.last_update_date = SYSDATE,
poh.last_updated_by = p_user_id,
poh.last_update_login = p_login_id,
poh.pending_signature_flag = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
DECODE(poh.acceptance_required_flag, 'S', 'Y',
DECODE( poh.pending_signature_flag, 'E', 'E','N')),
poh.pending_signature_flag)
WHERE poh.po_header_id = p_document_id;
SELECT NVL(poh.pending_signature_flag, 'N'), NVL(poh.conterms_exist_flag, 'N')
INTO l_pending_signature, l_conterms_exist
FROM PO_HEADERS poh
WHERE po_header_id = p_document_id;
PO_CONTERMS_WF_PVT.UPDATE_CONTERMS_DATES(
p_po_header_id => p_document_id
, p_po_doc_type => p_document_type
, p_po_doc_subtype => p_document_subtype
, p_conterms_exist_flag => l_conterms_exist
, x_return_status => l_ret_sts
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
l_err_msg := 'update_conterms_dates not successful';
UPDATE PO_HEADERS poh
SET poh.authorization_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED
, poh.approved_flag = 'N'
, poh.last_update_date = SYSDATE
, poh.last_updated_by = p_user_id
, poh.last_update_login = p_login_id
WHERE poh.po_header_id = p_document_id;
SELECT clm_flag
INTO l_is_clm_document
FROM po_doc_style_headers psh,
po_headers poh
WHERE psh.style_id = poh.style_id
AND poh.po_header_id = p_document_id;
UPDATE po_line_ucas
SET undef_approved_date =
Decode (p_draft_id,
undef_draft_id, nvl(undef_approved_date, SYSDATE),
undef_approved_date),
def_approved_date =
Decode (p_draft_id,
def_draft_id, SYSDATE,
def_approved_date),
undef_clm_document_number =
Decode (p_draft_id,
def_draft_id, undef_clm_document_number,
-1, (SELECT clm_document_number
FROM po_headers_all poh
WHERE poh.po_header_id = p_document_id),
(SELECT modification_number
FROM po_drafts
WHERE document_id = p_document_id
AND draft_id = p_draft_id)),
last_update_date = SYSDATE,
last_update_login = p_login_id,
last_updated_by = p_user_id
WHERE po_header_id = p_document_id
AND (def_draft_id = p_draft_id OR undef_draft_id = p_draft_id);
UPDATE PO_LINE_LOCATIONS_ALL poll
SET poll.approved_flag = 'Y'
, poll.approved_date = SYSDATE
, poll.last_update_date = SYSDATE
, poll.last_updated_by = p_user_id
, poll.last_update_login = p_login_id
WHERE poll.po_header_id = p_document_id
AND poll.po_release_id IS NULL
AND NVL(poll.approved_flag, 'N') <> 'Y'
AND EXISTS ( SELECT 'PO Does not require signature'
FROM PO_HEADERS_ALL poh
WHERE poh.po_header_id = poll.po_header_id
AND NVL(poh.pending_signature_flag, 'N') <> 'Y');
l_err_msg := 'update IDV header and line for total qty / amt not successful';
UPDATE PO_LINE_LOCATIONS_ALL poll
SET poll.approved_flag = 'R'
, poll.last_update_date = SYSDATE
, poll.last_updated_by = p_user_id
, poll.last_update_login = p_login_id
WHERE poll.po_header_id = p_document_id
AND poll.po_release_id IS NULL
AND NVL(poll.approved_flag, 'N') = 'Y';
UPDATE PO_RELEASES por
SET por.authorization_status = p_new_status,
por.approved_flag = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
por.approved_date = DECODE(p_new_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
por.approved_date),
por.last_update_date = SYSDATE,
por.last_updated_by = p_user_id,
por.last_update_login = p_login_id
WHERE por.po_release_id = p_document_id;
UPDATE PO_LINE_LOCATIONS_ALL poll
SET poll.approved_flag = 'Y'
, poll.approved_date = SYSDATE
, poll.last_update_date = SYSDATE
, poll.last_updated_by = p_user_id
, poll.last_update_login = p_login_id
WHERE poll.po_release_id = p_document_id
AND NVL(poll.approved_flag, 'N') <> 'Y';
UPDATE PO_LINE_LOCATIONS_ALL poll
SET poll.approved_flag = 'R'
, poll.last_update_date = SYSDATE
, poll.last_updated_by = p_user_id
, poll.last_update_login = p_login_id
WHERE poll.po_release_id = p_document_id
AND NVL(poll.approved_flag, 'N') = 'Y';
END update_doc_auth_status;
PROCEDURE update_doc_notifications(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_notify_action IN VARCHAR2
, p_notify_employee IN NUMBER
, p_doc_creation_date IN DATE
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
d_progress NUMBER;
d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_doc_notifications';
DELETE FROM PO_NOTIFICATIONS pon
WHERE pon.object_type_lookup_code = DECODE(p_document_type,
'PO', p_document_subtype,
'PA', p_document_subtype,
p_document_type)
AND pon.object_id = p_document_id
AND pon.employee_id > -1;
INSERT INTO PO_NOTIFICATIONS(
employee_id
, object_type_lookup_code
, object_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, object_creation_date
, action_lookup_code
)
VALUES(
p_notify_employee
, DECODE(p_document_type,
'PO', p_document_subtype,
'PA', p_document_subtype,
p_document_type)
, p_document_id
, SYSDATE
, p_user_id
, p_login_id
, SYSDATE
, p_user_id
, SYSDATE
, p_notify_action
);
END update_doc_notifications;
PROCEDURE insert_auth_action_history(
p_document_id IN NUMBER
, p_revision_num IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_draft_id IN NUMBER --CLM Apprvl
, p_action IN VARCHAR2
, p_employee_id IN NUMBER
, p_offline_code IN VARCHAR2
, p_approval_path_id IN NUMBER
, p_note IN VARCHAR2
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_sequence_num PO_ACTION_HISTORY.sequence_num%TYPE;
d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.insert_auth_action_history';
SELECT max(poah.sequence_num) + 1
INTO l_sequence_num
FROM PO_ACTION_HISTORY poah
WHERE poah.object_type_code = p_document_type
AND poah.object_sub_type_code = 'MODIFICATION'
AND poah.object_id = p_draft_id;
SELECT max(poah.sequence_num) + 1
INTO l_sequence_num
FROM PO_ACTION_HISTORY poah
WHERE poah.object_type_code = p_document_type
AND poah.object_id = p_document_id;
INSERT INTO PO_ACTION_HISTORY
( object_id
, object_type_code
, object_sub_type_code
, sequence_num
, last_update_date
, last_updated_by
, creation_date
, created_by
, action_code
, action_date
, employee_id
, note
, object_revision_num
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, approval_path_id
, offline_code
)
VALUES
( DECODE(p_draft_id,null,p_document_id,-1,p_document_id,p_draft_id) --CLM Apprvl
, p_document_type
, DECODE(p_draft_id,null,p_document_subtype,-1,p_document_subtype,'MODIFICATION')
, l_sequence_num
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_action
, DECODE(p_action, '', to_date(NULL), SYSDATE)
, p_employee_id
, p_note
, p_revision_num
, p_login_id
, 0
, 0
, 0
, ''
, p_approval_path_id
, p_offline_code
);
END insert_auth_action_history;
PROCEDURE update_auth_action_history(
p_document_id IN NUMBER
, p_revision_num IN NUMBER
, p_document_type IN VARCHAR2
, p_draft_id IN NUMBER --CLM Apprvl
, p_action IN VARCHAR2
, p_approval_path_id IN NUMBER
, p_note IN VARCHAR2
, p_user_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
d_progress NUMBER;
d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_auth_action_history';
UPDATE PO_ACTION_HISTORY
SET action_code = p_action
, action_date = SYSDATE
, note = p_note
, last_updated_by = p_user_id
, last_update_date = SYSDATE
, object_revision_num = p_revision_num
, approval_path_id = p_approval_path_id
WHERE object_id = p_draft_id
AND object_type_code = p_document_type
AND action_code IS NULL;
UPDATE PO_ACTION_HISTORY
SET action_code = p_action
, action_date = SYSDATE
, note = p_note
, last_updated_by = p_user_id
, last_update_date = SYSDATE
, object_revision_num = p_revision_num
, approval_path_id = p_approval_path_id
WHERE object_id = p_document_id
AND object_type_code = p_document_type
AND action_code IS NULL;
END update_auth_action_history;
SELECT po_distribution_id FROM po_distributions_all
WHERE po_line_id = p_po_line_id;
SELECT po_header_id, from_line_id, nvl(from_header_id, contract_id), amount, quantity, matching_basis
INTO l_po_header_id, l_idv_line_id, l_idv_header_id, l_line_amount, l_line_quantity, l_line_type
FROM po_lines_merge_v
WHERE po_line_id = p_po_line_id
AND draft_id = p_draft_id;
SELECT clm_amount_released INTO l_clm_amt_released_hdr
FROM po_headers_all
WHERE po_header_id = l_idv_header_id;
SELECT clm_total_amount_ordered, clm_total_quantity_ordered INTO l_amt_ordered_idv_line, l_qty_ordered_idv_line
FROM po_lines_all
WHERE po_line_id = l_idv_line_id;
SELECT quantity, amount INTO l_mod_quantity, l_mod_amount
FROM po_lines_draft_all
WHERE po_line_id = p_po_line_id
AND draft_id = p_draft_id;
SELECT quantity_cancelled, amount_cancelled INTO l_dist_qty_cancel, l_dist_amt_cancel FROM po_distributions_all
WHERE po_distribution_id = c_get_dist_rec.po_distribution_id;
UPDATE po_headers_all SET CLM_AMOUNT_RELEASED = l_clm_amt_released_hdr
WHERE po_header_id = l_idv_header_id;
UPDATE po_lines_all SET clm_total_amount_ordered = l_amt_ordered_idv_line, clm_total_quantity_ordered = l_qty_ordered_idv_line
WHERE po_line_id = l_idv_line_id;
SELECT po_line_id FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
AND (from_header_id IS NOT NULL or contract_id is not null);