[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION convert_to_ame_api_insertion(p_pon_api_insertion IN NUMBER) RETURN VARCHAR2;
FUNCTION convert_to_pon_api_insertion(p_ame_api_insertion IN VARCHAR2) RETURN NUMBER;
p_last_update_date OUT NOCOPY DATE,
p_error_message OUT NOCOPY VARCHAR2) IS
l_award_approval_status VARCHAR2(30);
l_last_update_date DATE;
SELECT award_approval_status
INTO l_award_approval_status
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
l_last_update_date := SYSDATE;
UPDATE pon_auction_headers_all
SET
award_appr_ame_trans_id = p_transaction_id,
award_appr_ame_txn_date = l_last_update_date,
last_updated_by = p_user_id,
last_update_date = l_last_update_date
WHERE auction_header_id = p_auction_header_id;
p_last_update_date := l_last_update_date;
UPDATE pon_auction_headers_all
SET
award_approval_status = 'REQUIRED',
award_appr_ame_trans_id = NULL,
award_appr_ame_trans_prev_id = NULL,
award_appr_ame_txn_date = NULL,
wf_award_approval_item_key = NULL,
last_updated_by = p_user_id,
last_update_date = SYSDATE
WHERE
auction_header_id = p_auction_header_id
AND NVL(award_approval_flag, 'Y') = 'Y';
p_last_update_date IN DATE,
p_lock_transaction IN BOOLEAN) IS
l_last_update_date DATE;
SELECT last_update_date, award_approval_status
INTO l_last_update_date, l_award_approval_status
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id
FOR UPDATE NOWAIT;
SELECT last_update_date, award_approval_status
INTO l_last_update_date, l_award_approval_status
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
IF NVL(l_last_update_date, SYSDATE) <> NVL(p_last_update_date, SYSDATE) THEN
RAISE transaction_modified;
SELECT MAX(sequence_num)
INTO l_action_sequence_number
FROM pon_action_history
WHERE
object_id = p_auction_header_id
AND object_type_code = AWARD_OBJECT_TYPE;
INSERT INTO pon_action_history(
object_id,
object_id2,
object_type_code,
sequence_num,
action_type,
action_date,
action_user_id,
action_note)
VALUES (
p_auction_header_id,
TO_NUMBER(p_transaction_id),
AWARD_OBJECT_TYPE,
l_action_sequence_number + 1,
p_action_type,
p_action_date,
p_user_id,
p_notes);
Updates an existing row in the PON_AUCTION_HISTORY table.
Only AWARD_APPROVAL_PENDING action entries can be updated.
*/
PROCEDURE update_action_history(p_auction_header_id IN NUMBER,
p_transaction_id IN VARCHAR2,
p_action_type IN VARCHAR2,
p_notes IN VARCHAR2,
p_action_date IN DATE,
p_user_id IN NUMBER) IS
BEGIN
UPDATE pon_action_history
SET
action_type = p_action_type,
action_date = p_action_date,
action_note = p_notes
WHERE
object_id = p_auction_header_id
AND object_id2 = TO_NUMBER(p_transaction_id)
AND action_user_id = p_user_id
AND action_type = 'AWARD_APPROVAL_PENDING'
AND object_type_code = AWARD_OBJECT_TYPE;
END update_action_history;
SELECT
auc.trading_partner_contact_name,
auc.trading_partner_name,
auc.award_appr_ame_trans_id,
auc.auction_title,
auc.document_number,
auc.open_bidding_date,
auc.close_bidding_date,
auc.award_by_date,
dt.doctype_group_name,
auc.view_by_date
INTO
l_trading_partner_contact_name,
l_trading_partner_name,
l_transaction_id,
l_auction_title,
l_document_number,
l_open_bidding_date,
l_close_bidding_date,
l_award_by_date,
l_doctype_group_name,
l_preview_date
FROM
pon_auction_headers_all auc,
pon_auc_doctypes dt
WHERE
dt.doctype_id= auc.doctype_id
AND auc.auction_header_id = p_auction_header_id;
SELECT user_id
INTO l_tp_contact_user_id
FROM fnd_user
WHERE user_name = l_tp_contact_user_name;
UPDATE pon_auction_headers_all
SET
award_appr_ame_trans_prev_id = l_transaction_id,
last_updated_by = l_tp_contact_user_id,
last_update_date = SYSDATE
WHERE auction_header_id = l_auction_header_id;
IF( l_next_approver.api_insertion = ame_util.apiAuthorityInsertion AND
l_source_type_out <> ame_util.forwardeeSource ) THEN
l_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
itemkey => itemkey,
aname => 'FIRST_AUTHORITY_APPROVER_ID');
SELECT person_id into l_person_id FROM (
SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = l_next_approver.orig_system_id and trunc(sysdate) between person.effective_start_date
and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
and asg.assignment_status_type_id not in (
SELECT assignment_status_type_id FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
) where rownum = 1;
SELECT user_id
INTO l_tp_contact_user_id
FROM fnd_user
WHERE user_name = l_tp_contact_user_name;
UPDATE pon_auction_headers_all
SET
award_approval_status = 'REJECTED',
last_updated_by = 0,
last_update_date = SYSDATE
WHERE auction_header_id = l_auction_header_id;
SELECT user_name
INTO l_oam_admin_user_name
FROM fnd_user
WHERE employee_id = l_admin_approver.orig_system_id;
UPDATE pon_auction_headers_all
SET
award_approval_status = 'REJECTED',
last_updated_by = 0,
last_update_date = SYSDATE
WHERE auction_header_id = l_auction_header_id;
UPDATE pon_auction_headers_all
SET
award_approval_status = 'APPROVED',
last_updated_by = 0,
last_update_date = SYSDATE
WHERE auction_header_id = l_auction_header_id;
IF( l_approver.api_insertion = ame_util.apiAuthorityInsertion AND
l_source_type_out <> ame_util.forwardeeSource ) THEN
l_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
itemkey => itemkey,
aname => 'FIRST_AUTHORITY_APPROVER_ID');
SELECT person_id into l_person_id FROM (
SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = l_approver.orig_system_id and trunc(sysdate) between person.effective_start_date
and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
and asg.assignment_status_type_id not in (
SELECT assignment_status_type_id FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
) where rownum = 1;
'Got the user details and going to update the action history record.');
update_action_history( l_auction_header_id,
l_transaction_id,
l_action_type,
l_note_to_buyer,
l_response_date,
l_approver_employee.user_id);
IF l_approver.api_insertion IN (ame_util.oamGenerated, ame_util.apiAuthorityInsertion) AND
l_approver.authority = ame_util.authorityApprover THEN
l_forwardee.api_insertion := ame_util.apiAuthorityInsertion;
l_forwardee.api_insertion := ame_util.apiInsertion;
select full_name into l_forwardee.display_name from per_all_people_f where person_id = l_forwardee.orig_system_id and TRUNC(sysdate) between effective_start_date and effective_end_date;
SELECT position_id into l_forwardee.orig_system_id FROM PER_ALL_ASSIGNMENTS_F pa
WHERE pa.person_id = l_forwardee.orig_system_id and pa.primary_flag = 'Y' and pa.assignment_type in ('E','C')
and pa.position_id is not null and pa.assignment_status_type_id not in (
select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
and TRUNC ( pa.effective_start_date )
<= TRUNC(SYSDATE) AND NVL(pa.effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE);
SELECT name into l_forwardee.name FROM wf_roles
WHERE orig_system = l_forwardee.orig_system and orig_system_id = l_forwardee.orig_system_id and rownum = 1;
'calling ame_api2.updateApprovalStatus on workflow item key ' || itemkey || fnd_global.newline ||
'with approver' || fnd_global.newline || format_ame_approver(l_approver) || fnd_global.newline ||
'and forwardee' || fnd_global.newline || format_ame_approver(l_forwardee));
ame_api2.updateApprovalStatus(applicationIdIn => APPLICATION_ID,
transactionIdIn => l_transaction_id,
approverIn => l_approver,
transactionTypeIn => AWARD_TRANSACTION_TYPE,
forwardeeIn => l_forwardee);
p_last_update_date IN DATE,
p_approver_list_string OUT NOCOPY VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2) IS
l_approver_list ame_util.approversTable2;
l_available_insertion_list ame_util.insertionsTable2;
l_insertion_order ame_util.insertionRecord2;
validate_transaction(p_auction_header_id, p_last_update_date, false);
log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.getAvailableInsertions at position ' || p_position);
ame_api3.getAvailableInsertions(applicationIdIn => APPLICATION_ID,
transactionIdIn => p_transaction_id,
positionIn => p_position,
transactionTypeIn => AWARD_TRANSACTION_TYPE,
availableInsertionsOut => l_available_insertion_list);
log_string(l_statement_log_level, l_current_log_level, l_module_name, 'calling ame_api3.getAvailableInsertions returns ' || fnd_global.newline || get_insertion_list_string(l_available_insertion_list));
FOR i IN 1 .. l_available_insertion_list.COUNT LOOP
IF l_available_insertion_list(i).order_type IN
(ame_util.absoluteOrder, ame_util.afterApprover, ame_util.beforeApprover) AND
l_available_insertion_list(i).api_insertion = l_approver.api_insertion AND
l_available_insertion_list(i).authority = l_approver.authority THEN
l_insertion_order := l_available_insertion_list(i);
l_approver.item_class := l_insertion_order.item_class;
l_approver.item_id := l_insertion_order.item_id;
l_approver.action_type_id := l_insertion_order.action_type_id;
l_approver.group_or_chain_id := l_insertion_order.group_or_chain_id;
l_approver.api_insertion := l_insertion_order.api_insertion;
l_approver.authority := l_insertion_order.authority;
SELECT name into l_approver.name FROM wf_roles
WHERE orig_system = l_approver.orig_system and orig_system_id = l_approver.orig_system_id and rownum = 1;
'calling ame_api3.insertApprover with approver insertion ' || fnd_global.newline || get_insertion_string(l_approver, l_insertion_order) || ' at position ' || p_position);
ame_api3.insertApprover( applicationIdIn => APPLICATION_ID,
transactionIdIn => p_transaction_id,
approverIn => l_approver,
positionIn => p_position,
insertionIn => l_insertion_order,
transactionTypeIn => AWARD_TRANSACTION_TYPE);
Deletes an approver from the approver list.
The resulting approver list is returned as a formatted string.
*/
PROCEDURE delete_approver(p_auction_header_id IN NUMBER,
p_transaction_id IN VARCHAR2,
p_approver_string IN VARCHAR2,
p_last_update_date IN DATE,
p_approver_list_string OUT NOCOPY VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2) IS
l_approver_list ame_util.approversTable2;
l_module_name VARCHAR2(80) := 'pon.plsql.PON_AWARD_APPROVAL_PKG.DELETE_APPROVER';
validate_transaction(p_auction_header_id, p_last_update_date, false);
log_string(l_statement_log_level, l_current_log_level, l_module_name, 'deleted from approver list for award transaction ' || p_auction_header_id || '-' || p_transaction_id);
END delete_approver;
p_last_update_date IN DATE,
p_approver_list_string OUT NOCOPY VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2) IS
l_approver_list ame_util.approversTable2;
insertion ame_util.insertionRecord2;
l_available_insertion_list ame_util.insertionsTable2;
validate_transaction(p_auction_header_id, p_last_update_date, false);
and l_approver_list(i).api_insertion = ame_util.oamGenerated) then
l_current_first_approver := l_approver_list(i) ;
select full_name into l_approver.display_name from per_all_people_f where person_id = l_approver.orig_system_id and TRUNC(sysdate) between effective_start_date and effective_end_date ;
SELECT position_id into l_approver.orig_system_id FROM PER_ALL_ASSIGNMENTS_F pa
WHERE pa.person_id = l_approver.orig_system_id and pa.primary_flag = 'Y' and pa.assignment_type in ('E','C')
and pa.position_id is not null and pa.assignment_status_type_id not in (
select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
and TRUNC ( pa.effective_start_date )
<= TRUNC(SYSDATE) AND NVL(pa.effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE);
l_approver.api_insertion := ame_util.apiAuthorityInsertion;
SELECT name into l_approver.name FROM wf_roles
WHERE orig_system = l_approver.orig_system and orig_system_id = l_approver.orig_system_id and rownum = 1;
p_last_update_date IN DATE,
p_approver_list_string OUT NOCOPY VARCHAR2,
p_can_delete_oam_approvers OUT NOCOPY VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2) IS
l_approver_list ame_util.approversTable2;
validate_transaction(p_auction_header_id, p_last_update_date, false);
p_can_delete_oam_approvers := 'Y';
p_can_delete_oam_approvers := 'N';
p_can_delete_oam_approvers := NULL;
p_last_update_date IN DATE,
p_note_to_approvers IN VARCHAR2,
p_reject_unawarded_responses IN VARCHAR2,
p_note_to_rejected_suppliers IN VARCHAR2,
p_has_items_flag IN VARCHAR2,
p_has_scoring_teams_flag IN VARCHAR2,
p_scoring_lock_tpc_id IN NUMBER,
p_first_authority_approver_id IN NUMBER,
p_error_message OUT NOCOPY VARCHAR2) IS
l_item_key VARCHAR2(240);
validate_transaction(p_auction_header_id, p_last_update_date, true);
UPDATE pon_auction_headers_all
SET
award_approval_status = 'INPROCESS',
wf_award_approval_item_key = l_item_key,
last_updated_by = p_user_id,
last_update_date = SYSDATE
WHERE auction_header_id = p_auction_header_id;
SELECT wf_award_approval_item_key
INTO l_item_key
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT notification_id
INTO l_notification_id
FROM wf_item_activity_statuses
WHERE
assigned_user = p_user_name
AND item_type = AWARD_APPROVAL_WF_ITEM_TYPE
AND item_key = l_item_key
AND activity_status = 'NOTIFIED';
((p_approver1.api_insertion IS NULL AND p_approver2.api_insertion IS NULL) OR
p_approver1.api_insertion = p_approver2.api_insertion) AND
((p_approver1.authority IS NULL AND p_approver2.authority IS NULL) OR
p_approver1.authority = p_approver2.authority), FALSE);
p_approver.api_insertion := convert_to_ame_api_insertion(parse_number_field(l_field_value));
p_approver.api_insertion := convert_to_ame_api_insertion(parse_number_field(l_field_value));
convert_to_pon_api_insertion(p_approver.api_insertion) || APPROVER_FIELD_DELIMITER ||
convert_to_pon_authority(p_approver.authority) || APPROVER_FIELD_DELIMITER ||
convert_to_pon_approval_status(p_approver.approval_status);
convert_to_pon_api_insertion(p_approver.api_insertion) || APPROVER_FIELD_DELIMITER ||
convert_to_pon_authority(p_approver.authority) || APPROVER_FIELD_DELIMITER ||
convert_to_pon_approval_status(p_approver.approval_status) || APPROVER_FIELD_DELIMITER ||
p_approver.action_type_id || APPROVER_FIELD_DELIMITER ||
p_approver.group_or_chain_id || APPROVER_FIELD_DELIMITER ||
p_approver.occurrence || APPROVER_FIELD_DELIMITER ||
p_approver.source || APPROVER_FIELD_DELIMITER ||
p_approver.item_class || APPROVER_FIELD_DELIMITER ||
p_approver.item_id || APPROVER_FIELD_DELIMITER ||
p_approver.item_class_order_number || APPROVER_FIELD_DELIMITER ||
p_approver.item_order_number || APPROVER_FIELD_DELIMITER ||
p_approver.sub_list_order_number || APPROVER_FIELD_DELIMITER ||
p_approver.action_type_order_number || APPROVER_FIELD_DELIMITER ||
p_approver.group_or_chain_order_number || APPROVER_FIELD_DELIMITER ||
p_approver.member_order_number || APPROVER_FIELD_DELIMITER ||
p_approver.approver_order_number;
Converts the specified PON_AWARD_APPROVAL_PKG api_insertion code to a corresponding ame_util api_insertion code.
*/
FUNCTION convert_to_ame_api_insertion(p_pon_api_insertion IN NUMBER) RETURN VARCHAR2 IS
BEGIN
IF p_pon_api_insertion = API_AUTHORITY_INSERTION THEN
RETURN ame_util.apiAuthorityInsertion;
ELSIF p_pon_api_insertion = API_INSERTION THEN
RETURN ame_util.apiInsertion;
ELSIF p_pon_api_insertion = OAM_GENERATED THEN
RETURN ame_util.oamGenerated;
END convert_to_ame_api_insertion;
Converts the specified ame_util api_insertion code to a corresponding PON_AWARD_APPROVAL_PKG api_insertion code.
*/
FUNCTION convert_to_pon_api_insertion(p_ame_api_insertion IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF p_ame_api_insertion = ame_util.apiAuthorityInsertion THEN
RETURN API_AUTHORITY_INSERTION;
ELSIF p_ame_api_insertion = ame_util.apiInsertion THEN
RETURN API_INSERTION;
ELSIF p_ame_api_insertion = ame_util.oamGenerated THEN
RETURN OAM_GENERATED;
END convert_to_pon_api_insertion;
SELECT NVL(persons.full_name, users.user_name)
INTO l_display_name
FROM
per_all_people_f persons,
fnd_user users
WHERE
users.employee_id = persons.person_id(+)
AND users.user_id = p_user_id
AND TRUNC(sysdate) between persons.effective_start_date and persons.effective_end_date;
SELECT NVL(persons.full_name, users.user_name)
INTO l_display_name
FROM
per_all_people_f persons,
fnd_user users
WHERE
users.employee_id = persons.person_id(+)
AND users.user_name = p_user_name
AND TRUNC(sysdate) between persons.effective_start_date and persons.effective_end_date;
SELECT full_name
INTO l_display_name
FROM per_all_people_f
WHERE person_id = p_person_id;
SELECT
users.user_id,
users.user_name,
--emp.person_id
users.employee_id
INTO
p_employee.user_id,
p_employee.user_name,
p_employee.person_id
FROM
--pon_employees_current_v emp,
fnd_user users
WHERE
-- emp.person_id = users.employee_id
users.user_id = p_user_id
AND users.start_date <= SYSDATE
AND NVL(users.end_date, SYSDATE) >= SYSDATE;
SELECT
users.user_id,
users.user_name,
--emp.person_id
users.employee_id
INTO
p_employee.user_id,
p_employee.user_name,
p_employee.person_id
FROM
-- pon_employees_current_v emp,
fnd_user users
WHERE
-- emp.person_id = users.employee_id
users.user_name = p_user_name
AND users.start_date <= SYSDATE
AND NVL(users.end_date, SYSDATE) >= SYSDATE;
SELECT
users.user_id,
users.user_name,
-- emp.person_id
users.employee_id
INTO
p_employee.user_id,
p_employee.user_name,
p_employee.person_id
FROM
-- pon_employees_current_v emp,
fnd_user users
WHERE
-- emp.person_id = users.employee_id
users.employee_id = p_person_id
AND users.start_date <= SYSDATE
AND NVL(users.end_date, SYSDATE) >= SYSDATE
AND rownum = 1;
Returns a string representation of an ame_util.insertionsTable.
*/
FUNCTION get_insertion_list_string(p_insertion_list IN ame_util.insertionsTable2) RETURN VARCHAR2 IS
l_insertion_list_string VARCHAR2(4000);
FOR i IN 1 .. p_insertion_list.COUNT LOOP
l_insertion_list_string :=
l_insertion_list_string ||
i || ' (' ||
get_api_insertion_string(p_insertion_list(i).api_insertion) || ', ' ||
get_authority_string(p_insertion_list(i).authority) || ', ' ||
get_order_type_string(p_insertion_list(i).order_type) || ', ' ||
get_parameter_string(p_insertion_list(i).parameter) || ')';
IF i < p_insertion_list.COUNT THEN
l_insertion_list_string := l_insertion_list_string || fnd_global.newline;
RETURN l_insertion_list_string;
END get_insertion_list_string;
FUNCTION get_insertion_string(p_approver IN ame_util.approverRecord2, p_order IN ame_util.insertionRecord2) RETURN VARCHAR2 IS
BEGIN
RETURN format_ame_approver(p_approver) || ' ' || '(' || get_order_type_string(p_order.order_type) || ', ' || get_parameter_string(p_order.parameter) || ')';
END get_insertion_string;
Returns a string representation of the value of an ame_util.approverRecord's api_insertion field.
*/
FUNCTION get_api_insertion_string(p_api_insertion IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_api_insertion = ame_util.oamGenerated THEN
RETURN 'oamGenerated';
ELSIF p_api_insertion = ame_util.apiAuthorityInsertion THEN
RETURN 'apiAuthorityInsertion';
ELSIF p_api_insertion = ame_util.apiInsertion THEN
RETURN 'apiInsertion';
END get_api_insertion_string;
Returns a string representation of the value of an ame_util.orderRecord's or ame_util.insertionRecord's order_type field.
*/
FUNCTION get_order_type_string(p_order_type IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_order_type = ame_util.absoluteOrder THEN
RETURN 'absoluteOrder';
Returns a string representation of the value of an ame_util.orderRecord's or ame_util.insertionRecord's parameter field.
*/
FUNCTION get_parameter_string(p_parameter IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_parameter IS NOT NULL THEN
RETURN p_parameter;