[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(exceeded_amount)
into l_violation_total
from ap_pol_violations_all
where report_header_id = p_report_header_id;
select nvl(sum(least(viol1.exceeded_amount, viol2.exceeded_amount)), 0)
into l_extra_amount
from ap_pol_violations_all viol1, ap_pol_violations_all viol2
where viol1.report_header_id = viol2.report_header_id
and viol1.distribution_line_number = viol2.distribution_line_number
and viol1.violation_type = 'DAILY_LIMIT'
and viol2.violation_type = 'DAILY_SUM_LIMIT'
and viol1.report_header_id = p_report_header_id;
select gp.person_id
into l_award_manager_id
from gms_personnel gp,
per_assignments_f pa,
per_assignment_status_types past
where gp.award_id = p_award_id
and gp.award_role = C_AWARD_MANAGER_ROLE
AND gp.start_date_active = ( select max(gp2.start_date_active)
from gms_personnel gp2
where gp2.award_role = C_AWARD_MANAGER_ROLE
and gp2.award_id = p_award_id
and gp2.start_date_active <= trunc(sysdate)
)
AND gp.person_id = pa.person_id
AND pa.primary_flag='Y'
AND trunc(sysdate) between pa.effective_start_date and
nvl(pa.effective_end_date, trunc(sysdate))
AND pa.assignment_status_type_id= past.assignment_status_type_id
AND past.per_system_status = C_ACTIVE_STATUS
AND pa.assignment_type in ('E', 'C')
AND rownum =1;
select ppp.person_id
into l_project_manager_id
from pa_project_players ppp,
per_assignments_f pa,
per_assignment_status_types past
where ppp.project_id = p_project_id
and ppp.project_role_type = C_PROJECT_MANAGER_ROLE_TYPE
AND ppp.start_date_active = ( select max(pp2.start_date_active)
from pa_project_players pp2
where pp2.project_role_type = C_PROJECT_MANAGER_ROLE_TYPE
and pp2.project_id = p_project_id
and pp2.start_date_active <= trunc(sysdate)
)
AND ppp.person_id = pa.person_id
AND pa.primary_flag='Y'
AND trunc(sysdate) between pa.effective_start_date and
nvl(pa.effective_end_date, trunc(sysdate))
AND pa.assignment_status_type_id= past.assignment_status_type_id
AND past.per_system_status = C_ACTIVE_STATUS
AND pa.assignment_type in ('E', 'C')
AND rownum =1;
SELECT SUM(AMOUNT)
INTO l_line_item_total
FROM AP_EXPENSE_REPORT_LINES_ALL
WHERE REPORT_HEADER_ID = p_report_header_id
AND FLEX_CONCATENATED = (
SELECT FLEX_CONCATENATED
FROM AP_EXPENSE_REPORT_LINES_ALL
WHERE REPORT_HEADER_ID = p_report_header_id
AND DISTRIBUTION_LINE_NUMBER = p_line_number
)
AND itemization_parent_id <> -1;
SELECT GS.chart_of_accounts_id
INTO l_char_of_accounts_id
FROM ap_system_parameters_all S,
gl_sets_of_books GS,
ap_expense_report_headers_all erh
WHERE GS.set_of_books_id = S.set_of_books_id
AND S.org_id = erh.org_id
AND erh.report_header_id = p_report_header_id;
l_query_stmt := 'SELECT distinct HOIP.ORG_INFORMATION2 OWNER_ID
FROM GL_CODE_COMBINATIONS GLCC,
HR_ORGANIZATION_INFORMATION HOIP,
HR_ORGANIZATION_INFORMATION HOIC,
HR_ORGANIZATION_INFORMATION HOI,
PER_WORKFORCE_CURRENT_X PP,
AP_EXPENSE_REPORT_LINES_ALL LINES
WHERE ENABLED_FLAG = ''Y''
AND GLCC.' || l_segment_name || ' = :costCenter
AND LINES.REPORT_HEADER_ID = :reportHeaderId
AND CHART_OF_ACCOUNTS_ID = :charOfAccountsId
AND COMPANY_COST_CENTER_ORG_ID IS NOT NULL
AND HOI.ORG_INFORMATION_CONTEXT = ''CLASS''
AND HOI.ORG_INFORMATION1 = ''CC''
AND HOIC.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND UPPER(HOIC.ORG_INFORMATION_CONTEXT) = ''COMPANY COST CENTER''
AND GLCC.COMPANY_COST_CENTER_ORG_ID = HOIC.ORGANIZATION_ID
AND HOIC.ORGANIZATION_ID = HOIP.ORGANIZATION_ID
AND UPPER(HOIP.ORG_INFORMATION_CONTEXT) = ''ORGANIZATION NAME ALIAS''
AND PP.PERSON_ID = HOIP.ORG_INFORMATION2';
select ame_util.booleanAttributeTrue
into l_is_missing_shortpay
from ap_expense_report_headers aerh,
wf_items wf
where aerh.report_header_id = p_report_header_id
and aerh.shortpay_parent_id is not null
and wf.item_type = l_apexp
and wf.Item_key = to_char(aerh.report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
and wf.end_date is null
and wf.root_activity = l_no_receipts_shortpay_process
and rownum = 1;
select award_number
into l_award_number
from GMS_AWARDS_ALL
where award_id = p_award_id;
select total
into p_amount
from ap_expense_report_headers_all
where report_header_id = p_report_header_id;
select sum(amount)
into p_amount
from ap_expense_report_lines_all
where report_header_id = p_report_header_id
and distribution_line_number = p_item_id;
select nvl(sum(amount),0)
into p_amount
from ap_exp_report_dists_all
where report_header_id = p_report_header_id
and project_id = p_item_id;
select nvl(sum(amount),0)
into p_amount
from ap_exp_report_dists_all
where report_header_id = p_report_header_id
and award_id = p_item_id;
select nvl(sum(amount),0)
into p_amount
from ap_exp_report_dists_all
where report_header_id = p_report_header_id
and cost_center = p_item_id;
Inserts data into table OIE_AME_NOTIF_GT
*/
-------------------------------------------------------------------------
PROCEDURE InsertToAMENotifGT(
p_report_header_id IN NUMBER,
p_orig_system IN VARCHAR2,
p_orig_system_id IN NUMBER,
p_item_class IN VARCHAR2,
p_item_id IN VARCHAR2,
p_amount IN NUMBER,
p_project_number IN VARCHAR2,
p_project_name IN VARCHAR2,
p_award_number IN VARCHAR2) as
-------------------------------------------------------------------------
BEGIN
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start InsertToAMENotifGT');
insert into OIE_AME_NOTIF_GT(
report_header_id,
orig_system,
orig_system_id,
item_class,
item_id ,
amount,
project_number,
project_name,
award_number
)
values
(
p_report_header_id,
p_orig_system,
p_orig_system_id,
p_item_class,
p_item_id,
p_amount,
p_project_number,
p_project_name,
p_award_number
);
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'End InsertToAMENotifGT');
AP_WEB_DB_UTIL_PKG.RaiseException( 'InsertToAMENotifGT' );
END InsertToAMENotifGT;
Inserts only the data that is related to the p_approver_id.
Data is populated into OIE_AME_APPROVER_AMT_GT by the call InitOieAmeApproverAmtGT
*/
-------------------------------------------------------------------------
PROCEDURE InitOieAmeNotifGT( p_report_header_id IN NUMBER,
p_approver_id IN NUMBER,
p_display_instr OUT NOCOPY VARCHAR2) iS
-------------------------------------------------------------------------
l_debug_info VARCHAR2(200);
DELETE FROM OIE_AME_NOTIF_GT;
l_debug_info := 'Before call to InsertToAMENotifGT';
InsertToAMENotifGT(
p_report_header_id,
l_approversOut(i).orig_system,
l_approversOut(i).orig_system_id,
l_itemClassesOut(j),
l_itemIdsOut(j),
l_amount,
l_project_number,
l_project_name,
l_award_number
);
l_debug_info := 'Before call to InsertToAMENotifGT';
InsertToAMENotifGT(
p_report_header_id,
l_approversOut(i).orig_system,
l_approversOut(i).orig_system_id,
l_approversOut(i).item_class,
l_approversOut(i).item_id ,
l_amount,
l_project_number,
l_project_name,
l_award_number
);
select report_line_id, nvl(amount,0) amount, distribution_line_number, itemization_parent_id
from ap_expense_report_lines_all xl
where xl.report_header_id = p_report_header_id
and (xl.itemization_parent_id is null or xl.itemization_parent_id <> -1);
DELETE FROM OIE_AME_APPROVER_AMT_GT;
select 'Y'
into l_line_approver
from oie_ame_notif_gt gt
where gt.item_class = C_LINE_ITEM
and gt.item_id = i.distribution_line_number;
select nvl(sum(xd.amount),0)
into l_approver_amount
from ap_exp_report_dists_all xd,
oie_ame_notif_gt gt
where xd.report_header_id = p_report_header_id
and xd.report_line_id in (
select report_line_id
from ap_expense_report_lines_all
where report_header_id = p_report_header_id
and itemization_parent_id = i.report_line_id)
and ( (xd.cost_center = gt.item_id
and
gt.item_class = C_COST_CENTER)
or
(xd.project_id = gt.item_id
and
gt.item_class = C_PROJECT)
or
(xd.award_id = gt.item_id
and
gt.item_class = C_AWARD) );
select nvl(sum(xd.amount),0)
into l_approver_amount
from ap_exp_report_dists_all xd,
oie_ame_notif_gt gt
where xd.report_header_id = p_report_header_id
and xd.report_line_id = i.report_line_id
and ( (xd.cost_center = gt.item_id
and
gt.item_class = C_COST_CENTER)
or
(xd.project_id = gt.item_id
and
gt.item_class = C_PROJECT)
or
(xd.award_id = gt.item_id
and
gt.item_class = C_AWARD) );
insert into OIE_AME_APPROVER_AMT_GT(
report_header_id,
report_line_id,
approver_amount)
values (
p_report_header_id,
i.report_line_id,
nvl(l_approver_amount,0));