[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
and approverList(i).api_insertion = 'N'
and approverList(i).group_or_chain_id < 3 ) then
currentFirstApprover := approverList(i) ;
SELECT position_id into tmpApprover.orig_system_id FROM PER_ALL_ASSIGNMENTS_F pa
WHERE pa.person_id = pPersonId 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);
UPDATE po_requisition_headers_all
SET first_position_id = tmpApprover.orig_system_id, first_approver_id = pPersonId
WHERE requisition_header_id = pReqHeaderId;
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Inserted the first_position_id and first_approver_id columns.' );
tmpApprover.api_insertion := ame_util.apiAuthorityInsertion;
SELECT name into tmpApprover.name FROM wf_roles
WHERE orig_system = tmpApprover.orig_system and orig_system_id = tmpApprover.orig_system_id and rownum = 1;
procedure insert_approver( pReqHeaderId IN NUMBER,
pPersonId IN NUMBER,
pAuthority IN VARCHAR2,
pApproverCategory IN VARCHAR2,
pPosition IN NUMBER,
pApproverNumber IN NUMBER,
pInsertionType IN VARCHAR2,
pApproverName IN VARCHAR2,
pApprovalListStr OUT NOCOPY VARCHAR2,
pApprovalListCount OUT NOCOPY NUMBER,
pQuoteChar OUT NOCOPY VARCHAR2,
pFieldDelimiter OUT NOCOPY VARCHAR2
) IS
l_api_name varchar2(50):= 'insert_approver';
insertOrder ame_util.insertionRecord2;
E_NO_AVAILABLE_INSERTION EXCEPTION;
availableInsertionList ame_util.insertionsTable2;
l_insertion_type varchar2(1); -- To store insertion type
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering insert_approver...');
tmpApprover.api_insertion := ame_util.apiInsertion;
Second condition will be used to avoid this code when we are inserting first approver */
IF ( hasHiddenApprovers = true AND pApproverName IS NOT NULL ) THEN
if g_fnd_debug = 'Y' then
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Found Repeated/Skipped Approvers!' );
l_insertion_type := approverList(l_approver_position).api_insertion; -- Set insertion type which will be used to identify adhoc approver
/* Condition 1.1: If we are inserting after an approver who belongs to a group
Condition 1.2: If we are inserting after an approver who is either adhoc or belongs to COA
Condition 2.1: If we are inserting approver before an approver who belongs to a group
Condition 2.2: If we are inserting before an approver who is either adhoc or belongs to COA
In each case we have to take care of approvers who are repeated or deleted (suppressed)
And if there are approvers like this then either increment or decrement positionId depending upon whether
we are inserting after or before */
IF pInsertionType = 'A' THEN
l_counter := l_approver_position +1;
Checking it for 'after' case and not 'before' because insertion before adhoc is only possible if adhoc is inserted at
the left end of chain. And in that case action_type_id is enough to locate exact position of insertion
Requirement of these extra statuses have beed explained earlier. Not included condition check of 'approvedStatus'
for 'before' case, because we dont provide option to insert before approver who have approved */
IF l_insertion_type <> ame_util.apiInsertion THEN
if l_group_or_chain_id > 1 then
while( l_counter <= approverList.count AND approverList(l_counter).group_or_chain_id = l_group_or_chain_id
AND approverList(l_counter).approval_status IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus, ame_util.rejectedByRepeatedStatus,
ame_util.approvedStatus) ) LOOP
l_counter := l_counter + 1;
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.getAvailableInsertions()..');
ame_api3.getAvailableInsertions( applicationIdIn => applicationId,
transactionIdIn => pReqHeaderId,
positionIn => absolutePosition,
transactionTypeIn => ameTransactionType,
availableInsertionsOut => availableInsertionList
);
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.getAvailableInsertions()..');
FOR i IN 1 .. availableInsertionList.COUNT LOOP
IF availableInsertionList(i).order_type IN
(ame_util.absoluteOrder,ame_util.afterApprover, ame_util.beforeApprover) AND
availableInsertionList(i).api_insertion = tmpApprover.api_insertion AND
availableInsertionList(i).authority = tmpApprover.authority THEN
insertOrder := availableInsertionList(i);
tmpApprover.item_class := insertOrder.item_class;
tmpApprover.item_id := insertOrder.item_id;
tmpApprover.action_type_id := insertOrder.action_type_id;
tmpApprover.group_or_chain_id := insertOrder.group_or_chain_id;
tmpApprover.api_insertion := insertOrder.api_insertion;
tmpApprover.authority := insertOrder.authority;
SELECT name into tmpApprover.name FROM wf_roles
WHERE orig_system = tmpApprover.orig_system and orig_system_id = tmpApprover.orig_system_id and rownum = 1;
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the available position also to insert..');
FOR i IN 1 .. availableInsertionList.COUNT LOOP
/* We can insert after an approver if we have order_type in absoluteOrder,afterApprover or beforeApprover
And for insertion before an approver we should have order_type as beforeApprover */
IF ((pInsertionType = 'A' and availableInsertionList(i).order_type IN (ame_util.afterApprover, ame_util.beforeApprover))
OR (pInsertionType = 'B' and availableInsertionList(i).order_type = ame_util.beforeApprover)) AND
availableInsertionList(i).api_insertion = tmpApprover.api_insertion AND
availableInsertionList(i).authority = tmpApprover.authority THEN
insertOrder := availableInsertionList(i);
tmpApprover.item_class := insertOrder.item_class;
tmpApprover.item_id := insertOrder.item_id;
tmpApprover.action_type_id := insertOrder.action_type_id;
tmpApprover.group_or_chain_id := insertOrder.group_or_chain_id;
tmpApprover.api_insertion := insertOrder.api_insertion;
tmpApprover.authority := insertOrder.authority;
SELECT name into tmpApprover.name FROM wf_roles
WHERE orig_system = tmpApprover.orig_system and orig_system_id = tmpApprover.orig_system_id and rownum = 1;
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the available position also to insert..');
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.insertApprover()..');
ame_api3.insertApprover( applicationIdIn => applicationId,
transactionIdIn => pReqHeaderId,
approverIn => tmpApprover,
positionIn => absolutePosition,
insertionIn => insertOrder,
transactionTypeIn => ameTransactionType
);
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.insertApprover()..');
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving insert_approver...');
raise E_NO_AVAILABLE_INSERTION;
when E_NO_AVAILABLE_INSERTION then
pApprovalListCount := 0;
pApprovalListStr := 'EXCEPTION-E_NO_AVAILABLE_INSERTION';
l_api_name || '.E_NO_AVAILABLE_INSERTION', 'No available insertion order');
procedure delete_approver( pReqHeaderId IN NUMBER,
pPersonId IN NUMBER,
pOrigSystem IN VARCHAR2,
pOrigSystemId IN NUMBER,
pRecordName IN VARCHAR2,
pAuthority IN VARCHAR2,
pApprovalListStr OUT NOCOPY VARCHAR2,
pApprovalListCount OUT NOCOPY NUMBER,
pQuoteChar OUT NOCOPY VARCHAR2,
pFieldDelimiter OUT NOCOPY VARCHAR2
) IS
l_api_name varchar2(50):= 'delete_approver';
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering delete_approver...');
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the approver to be deleted also in the list...');
SELECT FIRST_POSITION_ID
INTO l_first_position_id
FROM po_requisition_headers_all
WHERE requisition_header_id = pReqHeaderId;
UPDATE po_requisition_headers_all
SET first_position_id = NULL, first_approver_id = NULL
WHERE requisition_header_id = pReqHeaderId;
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving delete_approver...');
SELECT wf_item_type, wf_item_key
INTO l_itemtype, l_itemkey
FROM po_requisition_headers_all
WHERE requisition_header_id = pReqHeaderId;
select authorization_status, preparer_id
into authorizationStatus, preparerId
from po_requisition_headers
where requisition_header_id = pReqHeaderId;
UPDATE po_requisition_headers_all
SET first_position_id = NULL, first_approver_id = NULL
WHERE requisition_header_id = pReqHeaderId;
UPDATE po_requisition_headers_all
SET first_position_id = NULL, first_approver_id = NULL
WHERE requisition_header_id = pReqHeaderId;
SELECT DISTINCT wf_item_type, wf_item_key
INTO l_itemtype, l_itemkey
FROM po_change_requests
WHERE document_header_id= pReqHeaderId AND
document_type = 'REQ' AND
action_type IN ('MODIFICATION', 'CANCELLATION') AND
creation_date = (select max(creation_date)
from PO_CHANGE_REQUESTS
where DOCUMENT_HEADER_ID = pReqHeaderId) AND
request_status NOT IN ('ACCEPTED', 'REJECTED');
SELECT wf_item_type, wf_item_key
INTO l_itemtype, l_itemkey
FROM po_requisition_headers_all
WHERE requisition_header_id = pReqHeaderId;
select authorization_status
into authorizationStatus
from po_requisition_headers_all
where requisition_header_id = pReqHeaderId;
SELECT COUNT(1)
INTO changeRequestExist
FROM
PO_CHANGE_REQUESTS pcr
WHERE
pcr.document_header_id = pReqHeaderId AND
pcr.document_type = 'REQ' AND
pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
pcr.approval_required_flag = 'Y' AND
pcr.request_status NOT IN ('ACCEPTED', 'REJECTED');
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
(approvers(i).api_insertion = ame_util.oamGenerated or
approvers(i).api_insertion = ame_util.apiAuthorityInsertion) and
(approvers(i).approval_status is null or
approvers(i).approval_status = ame_util.nullStatus)) then
l_return_val := 'N';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
approvers(i).api_insertion = ame_util.oamGenerated and
(approvers(i).approval_status is null or
approvers(i).approval_status = ame_util.nullStatus)) then
if approvers(i).orig_system = ame_util.posOrigSystem then
if g_fnd_debug = 'Y' then
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'First record is Position Hierarchy action type...');
SELECT person_id into xPersonId FROM (
SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = approvers(i).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;
PROCEDURE can_delete_oam_approvers( pReqHeaderId IN NUMBER,
xResult OUT NOCOPY VARCHAR2) IS
l_api_name varchar2(50):= 'can_delete_oam_approvers';
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering can_delete_oam_approvers...');
FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving can_delete_oam_approvers...');
END can_delete_oam_approvers;
SELECT COUNT(1)
INTO changeRequestExist
FROM
PO_CHANGE_REQUESTS pcr
WHERE
pcr.document_header_id = pReqHeaderId AND
pcr.document_type = 'REQ' AND
pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
pcr.request_status NOT IN ('ACCEPTED', 'REJECTED');
SELECT org_id
INTO orgId
FROM po_requisition_headers_all
WHERE requisition_header_id = pReqHeaderId;
SELECT type_lookup_code, org_id
INTO lookupCode, orgId
FROM po_requisition_headers_all
WHERE requisition_header_id = pReqHeaderId;
SELECT ame_transaction_type
INTO x_ame_txn_type
FROM po_document_types_all_b
WHERE document_type_code = p_doc_type
and document_subtype = p_doc_subtype
and org_id = p_org_id;
select PREPARER_ID
into l_preparer_id
from po_requisition_headers_all
where reqHeaderId = requisition_header_id;
SELECT POSITION_ID
INTO l_preparer_pos_id
FROM PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = l_preparer_id
and primary_flag = 'Y'
and assignment_type in ('E','C')
and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE)
and NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
and rownum = 1;
SELECT orig_system, orig_system_id into l_orig_system, l_orig_system_id FROM wf_roles where name = approversTableIn(i).name and rownum = 1;
SELECT orig_system_id, display_name, description into l_person_id, l_full_name, l_job_or_position
FROM wf_roles where name = approversTableIn(i).name and rownum = 1;
SELECT position_id, job_id INTO l_position_id, l_job_id
FROM per_all_assignments_f
WHERE person_id = l_person_id
and primary_flag = 'Y' and assignment_type in ('E','C')
and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
and rownum = 1;
SELECT name INTO l_job_or_position FROM per_all_positions WHERE position_id = l_position_id;
SELECT name INTO l_job_or_position FROM per_jobs WHERE job_id = l_job_id;
marshalField(approversTableIn(i).api_insertion, quoteChar, fieldDelimiter);
select authorization_status, wf_item_type, wf_item_key
into x_approval_status, l_itemtype, l_itemkey
from po_requisition_headers_all
where requisition_header_id = p_req_header_id;
SELECT max(change_request_group_id) INTO l_change_request_group_id
FROM po_change_requests
WHERE document_header_id = p_req_header_id
AND document_type = 'REQ';
SELECT wf_item_type, wf_item_key,
decode( request_status, 'ACCEPTED', 'APPROVED',
'MGR_APP', 'APPROVED',
'REJECTED','REJECTED',
'IN PROCESS' )
INTO l_itemtype, l_itemkey, x_approval_status
FROM po_change_requests
WHERE document_header_id = p_req_header_id
AND change_request_group_id = l_change_request_group_id
AND document_type = 'REQ'
AND action_type <> 'DERIVED'
AND rownum = 1;
SELECT distinct meaning into x_approval_status
FROM fnd_lookup_values_vl
WHERE lookup_code = x_approval_status and lookup_type = 'AUTHORIZATION STATUS';
SELECT email_address INTO x_email FROM per_all_people_f
WHERE person_id = p_approver_id
AND TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
AND rownum = 1;
SELECT position_id, job_id INTO l_position_id, l_job_id
FROM per_all_assignments_f
WHERE person_id = p_approver_id
and primary_flag = 'Y' and assignment_type in ('E','C')
and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
and rownum = 1;
SELECT name INTO x_title FROM per_all_positions WHERE position_id = l_position_id;
SELECT name INTO x_title FROM per_jobs WHERE job_id = l_job_id;
select PREPARER_ID,
first_position_id,
first_approver_id,
org_id,
AUTHORIZATION_STATUS,
CHANGE_PENDING_FLAG
into l_preparer_id,
l_first_position_id,
l_first_approver_id,
l_org_id,
l_authorizationStatus,
l_change_pending_flag
from po_requisition_headers_all
where pReqHeaderId = requisition_header_id;
select DISTINCT nvl(APPROVAL_REQUIRED_FLAG, 'N')
into l_approval_reqd_flag
from PO_CHANGE_REQUESTS
where DOCUMENT_HEADER_ID = pReqHeaderId
and action_type IN ('MODIFICATION', 'CANCELLATION')
and creation_date = (select max(creation_date)
from PO_CHANGE_REQUESTS
where DOCUMENT_HEADER_ID = pReqHeaderId);
SELECT type_lookup_code
INTO p_doc_subtype
FROM po_requisition_headers_all
WHERE requisition_header_id = pReqHeaderId;
SELECT full_name
INTO l_full_name
FROM per_all_people_f
WHERE person_id = l_first_approver_id
AND TRUNC(sysdate) between effective_start_date and effective_end_date;
SELECT person_id, full_name into l_person_id,l_full_name FROM (
SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = l_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 employee_id into l_person_id
FROM fnd_user
WHERE user_id = l_orig_system_id
and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
SELECT position_id, job_id INTO l_position_id, l_job_id
FROM per_all_assignments_f
WHERE person_id = l_person_id
and primary_flag = 'Y' and assignment_type in ('E','C')
and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
and rownum = 1;
SELECT name INTO l_job_or_position FROM per_all_positions WHERE position_id = l_position_id;
SELECT name INTO l_job_or_position FROM per_jobs WHERE job_id = l_job_id;
marshalField(approverList(i).api_insertion, quoteChar, fieldDelimiter);
select PREPARER_ID
into l_preparer_id
from po_requisition_headers_all
where pReqHeaderId = requisition_header_id;
select first_position_id, first_approver_id
into l_first_position_id, l_first_approver_id
from po_requisition_headers_all
where p_reqHeaderId = requisition_header_id;
SELECT full_name
INTO x_fullName
FROM per_all_people_f
WHERE person_id = l_first_approver_id
AND trunc(sysdate) between effective_start_date and effective_end_date;
SELECT person_id, full_name
into x_personId, x_fullName
FROM (
SELECT person.person_id, person.full_name
FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = p_origSystemId
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 employee_id
into x_personId
FROM fnd_user
WHERE user_id = p_origSystemId
and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);