[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
th_select varchar2(200) := '';
Line: 64
td_select varchar2(200) := '';
Line: 178
SELECT name
INTO tvalue
FROM wf_role_lov_vl
WHERE upper(display_name) = upper(p_from_role)
AND rownum = 1;
Line: 287
UpdateHeaderLines(p_report_header_id); --Bug 2777245
Line: 290
select nvl(AMT_DUE_CCARD_COMPANY,0)+nvl(AMT_DUE_EMPLOYEE,0)+nvl(MAXIMUM_AMOUNT_TO_APPLY,0)
into l_total
from ap_expense_report_headers_all
where report_header_id = p_report_header_id;
Line: 318
SELECT 'Y' INTO l_wf_exists
FROM wf_items
WHERE item_type = l_item_type
AND item_key = l_item_key;
Line: 367
SELECT org_id
INTO l_n_org_id
FROM ap_expense_report_headers_all
WHERE report_header_id = l_item_key;
Line: 461
l_debug_info := 'Update Withdraw Message';
Line: 775
SELECT count(*)
INTO l_violation_count
FROM ap_pol_violations
WHERE report_header_id = p_report_header_id
and distribution_line_number > 0;
Line: 898
UPDATE ap_expense_report_headers erh
SET workflow_approved_flag = 'S',
expense_status_code = null,
source = 'NonValidatedWebExpense'
WHERE report_header_id = p_report_header_id;
Line: 972
l_table_header := l_table_header || th_select || l_prompts(4) || td_end;
Line: 1117
l_table_row := l_table_row || td_select || to_char(l_line_num) || td_end;
Line: 1237
select count(*)
into l_num_lines
from ap_expense_report_lines
where report_header_id = l_report_header_id
and credit_card_trx_id is not null
and ap_validation_error is not null;
Line: 1253
select count(*)
into l_num_lines
from ap_expense_report_lines
where report_header_id = l_report_header_id
and credit_card_trx_id is null
and ap_validation_error is not null;
Line: 1642
SELECT fndl.meaning
INTO l_yes
FROM FND_LOOKUPS fndl
WHERE fndl.LOOKUP_TYPE = C_YES_NO
AND fndl.LOOKUP_CODE = C_Y;
Line: 1654
SELECT fndl.meaning
INTO l_no
FROM FND_LOOKUPS fndl
WHERE fndl.LOOKUP_TYPE = C_YES_NO
AND fndl.LOOKUP_CODE = C_N;
Line: 1669
select count(*)
into l_num_line_errors
from ap_expense_report_lines
where report_header_id = l_report_header_id
and ap_validation_error is not null;
Line: 1823
AP_WEB_DB_EXPDIST_PKG.updateAccountValues (
p_report_header_id => p_report_header_id,
p_report_line_id => p_ReportLineId,
p_report_distribution_id => p_report_distribution_id,
p_ccid => p_exp_dist_ccid);
Line: 2017
UPDATE ap_expense_report_headers erh
SET workflow_approved_flag = '',
source = 'NonValidatedWebExpense'
WHERE report_header_id = p_report_header_id;
Line: 2240
SELECT card_program_id, transaction_date
INTO l_card_program_id, l_transaction_date
FROM ap_credit_card_trxns
WHERE trx_id = V_CreditCardTrxID;
Line: 2303
/* Also update the segment values in the dist table to ensure the ccid
and the segment values are in sync. */
--------------------------------------------------------------
l_debug_info:='Synch Account Segments with CCID';
Line: 2307
-- updateAccountValues calls AddDistributionLine if dist does not
-- exist and then updates the ccid and segments based on l_exp_dist_ccid
--------------------------------------------------------------
AP_WEB_DB_EXPDIST_PKG.updateAccountValues (
p_report_header_id => p_report_header_id,
p_report_line_id => V_ReportLineId,
p_report_distribution_id => l_report_distribution_id,
p_ccid => l_exp_dist_ccid);
Line: 2331
SELECT card_program_id, transaction_date
INTO l_card_program_id, l_transaction_Date
FROM ap_credit_card_trxns
WHERE trx_id = V_CreditCardTrxID;
Line: 2472
/* Also update the segment values in the dist table to ensure the ccid
and the segment values are in sync. */
--------------------------------------------------------------
l_debug_info:='Synch Account Segments with CCID';
Line: 2476
-- updateAccountValues calls AddDistributionLine if dist does not
-- exist and then updates the ccid and segments based on l_exp_dist_ccid
--------------------------------------------------------------
AP_WEB_DB_EXPDIST_PKG.updateAccountValues (
p_report_header_id => p_report_header_id,
p_report_line_id => V_ReportLineId,
p_report_distribution_id => l_report_distribution_id,
p_ccid => l_exp_dist_ccid);
Line: 2498
AP_WEB_DB_EXPLINE_PKG.UpdateAPValidationError(
p_report_header_id => p_report_header_id,
p_dist_line_number => l_dist_line_number,
p_ap_validation_error => l_line_error_message);
Line: 2520
SELECT fndl.meaning
INTO l_yes
FROM FND_LOOKUPS fndl
WHERE fndl.LOOKUP_TYPE = C_YES_NO
AND fndl.LOOKUP_CODE = C_Y;
Line: 2531
SELECT fndl.meaning
INTO l_no
FROM FND_LOOKUPS fndl
WHERE fndl.LOOKUP_TYPE = C_YES_NO
AND fndl.LOOKUP_CODE = C_N;
Line: 2619
l_debug_info := 'Update ap_expense_report_headers';
Line: 2638
l_debug_info := 'Update ap_expense_report_lines';
Line: 2708
select HOME_URL
into l_url
from ICX_PARAMETERS;
Line: 2859
l_debug_info := 'Update the Expense Report as Manager Approved';
Line: 2866
l_debug_info := 'Update Receipts Status to Missing if Pending Resolution';
Line: 2868
update ap_expense_report_headers
set receipts_status = 'MISSING'
where report_header_id = l_report_header_id
and receipts_status = 'RESOLUTN';
Line: 3230
select wa.result_type
into l_resultType
from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WA, wf_items wi
where WPA.INSTANCE_ID = p_actid
and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
and WPA.ACTIVITY_NAME = WA.NAME
and wi.begin_date >= WA.BEGIN_DATE
and wi.begin_date < nvl(WA.END_DATE, wi.begin_date+1)
and wi.item_type = wa.item_type
and wi.item_key = p_item_key;
Line: 3285
SELECT nvl(workflow_approved_flag, 'N')
INTO l_ap_review_status
FROM ap_expense_report_headers
WHERE report_header_id = l_report_header_id;
Line: 3313
UPDATE WF_ITEM_ACTIVITY_STATUSES
SET begin_date = l_wakeup_time
WHERE item_type = p_item_type
AND item_key = p_item_key
AND process_activity = p_actid;
Line: 3410
UPDATE ap_expense_report_headers_all
SET expense_status_code = 'MGRAPPR'
WHERE report_header_id = l_report_header_id;
Line: 3431
UPDATE WF_ITEM_ACTIVITY_STATUSES
SET begin_date = l_wakeup_time
WHERE item_type = p_item_type
AND item_key = p_item_key
AND process_activity = p_actid;
Line: 3610
l_debug_info := 'Update the Headers table with the new Amt
Dues and Total columns';
Line: 3937
l_debug_info := 'Update the Expense Report as Approved, if the expense
report has only been manager approved then mark it as
approved automatic';
Line: 3962
UPDATE ap_expense_report_headers_all
SET expense_status_code = 'INVOICED',
last_update_date = SYSDATE
--last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
WHERE report_header_id = l_report_header_id;
Line: 3970
l_debug_info := 'Update the Credit Card Trxns associated with the Expense
Report as Approved';
Line: 4358
l_ExpenseRec.last_updated_by := NULL;
Line: 4365
l_ExpenseRec.last_update_login := NULL;
Line: 4385
SELECT receipts_status, receipts_received_date, image_receipts_status, image_receipts_received_date
INTO l_receipts_status, l_received_date, l_img_receipts_status, l_img_received_date
FROM ap_expense_report_headers WHERE report_header_id = p_orig_expense_report_id;
Line: 4406
IF (NOT AP_WEB_DB_EXPRPT_PKG.InsertReportHeaderLikeExisting(
p_orig_expense_report_id, l_ExpenseRec)) THEN
NULL;
Line: 4423
l_debug_info := 'Insert the lines that cannot be paid into
ap_expense_report_lines with new report_header_id';
Line: 4441
l_debug_info := 'Update the report header id in the violations table
with the new expense report id';
Line: 4450
l_debug_info := 'Insert the lines that cannot be paid into
ap_expense_report_lines with new report_header_id for
missing receipts shortpay';
Line: 4484
l_debug_info := 'Update the report header id in the violations table
with the new expense report id';
Line: 4931
l_debug_info := 'Delete the original expense report if everything
is getting shortpaid';
Line: 4934
IF (NOT AP_WEB_DB_EXPRPT_PKG.DeleteExpenseReport(l_report_header_id)) THEN
NULL;
Line: 4941
l_debug_info := 'Update the total of the original expense report
to not include the amount of the new expense report';
Line: 4982
PROCEDURE DeleteExpReportFromAPTables(p_report_header_id IN AP_WEB_DB_EXPRPT_PKG.expHdr_headerID) IS
l_debug_info VARCHAR2(200);
Line: 4990
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'start DeleteExpReportFromAPTables');
Line: 4999
l_debug_info := 'Update manager rejected/returned credit card transactions that
are deleted after a timeout';
Line: 5011
l_debug_info := 'Update shortpaid credit card transactions';
Line: 5018
l_debug_info := 'Delete the expense lines for the given expense report id';
Line: 5020
IF (NOT AP_WEB_DB_EXPLINE_PKG.DeleteReportLines(p_report_header_id)) THEN
NULL;
Line: 5025
l_debug_info := 'Delete the expense report header';
Line: 5027
IF (NOT AP_WEB_DB_EXPRPT_PKG.DeleteExpenseReport(p_report_header_id)) THEN
NULL;
Line: 5031
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'end DeleteExpReportFromAPTables');
Line: 5038
FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DeleteExpReportFromAPTables');
Line: 5042
END DeleteExpReportFromAPTables;
Line: 5326
l_debug_info := 'Update for Credit Card Integration';
Line: 5328
IF (NOT AP_WEB_DB_EXPLINE_PKG.DeletePersonalLines(p_report_header_id))
THEN
NULL;
Line: 5341
l_debug_info := 'Update for Credit Card Integration Bothpay';
Line: 5442
select end_date
into l_end_date
from wf_items
where item_type = l_itemtype
and item_key = l_childItemKey;
Line: 5630
update ap_expense_report_lines
set receipt_verified_flag = l_No
where report_header_id = l_report_header_id;
Line: 5662
PROCEDURE DeleteExpenseReport(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
----------------------------------------------------------------------
l_report_header_id AP_WEB_DB_EXPRPT_PKG.expHdr_headerID;
Line: 5672
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'start DeleteExpenseReport');
Line: 5683
DeleteExpReportFromAPTables(l_report_header_id);
Line: 5685
AP_WEB_DB_VIOLATIONS_PKG.deleteViolationEntry(l_report_header_id);
Line: 5691
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'end DeleteExpenseReport');
Line: 5695
Wf_Core.Context('AP_WEB_EXPENSE_WF', 'DeleteExpenseReport',
p_item_type, p_item_key, to_char(p_actid), l_debug_info);
Line: 5698
END DeleteExpenseReport;
Line: 5968
l_debug_info := 'Update all expense lines as receipt missing';
Line: 5976
l_debug_info := 'Update Receipts Status to Required if Pending Resolution';
Line: 5978
update ap_expense_report_headers
set receipts_status = 'IN_TRANSIT'
where report_header_id = l_report_header_id
and receipts_status = 'RESOLUTN';
Line: 5983
update ap_expense_report_headers
set image_receipts_status = 'IN_TRANSIT'
where report_header_id = l_report_header_id
and image_receipts_status = 'RESOLUTN';
Line: 5992
update ap_expense_report_lines
set receipt_missing_flag = l_No
where report_header_id = l_report_header_id;
Line: 6043
l_debug_info := 'Update all expense lines as receipt missing';
Line: 6242
l_XpenseRec.last_update_login:= NULL;
Line: 6243
l_XpenseRec.last_updated_by := NULL;
Line: 6258
select report_submitted_date
into l_report_submitted_date
from ap_expense_report_headers
where report_header_id = l_report_header_id;
Line: 6282
IF (NOT AP_WEB_DB_EXPRPT_PKG.InsertReportHeaderLikeExisting(
l_report_header_id, l_XpenseRec)) THEN
NULL;
Line: 6298
l_debug_info := 'Insert the lines for the credit card company into
ap_expense_report_lines with new report_header_id';
Line: 7801
l_debug_info := 'Update all expense lines as receipt missing';
Line: 8146
/* Bug 3566496: Need to update expense_current_approver_id since that
* is used to display the approver in the Track expenses page.
*/
UPDATE ap_expense_report_headers_all
SET expense_current_approver_id = p_manager_id,
approval_type = 'PER'
WHERE report_header_id = l_report_header_id;
Line: 9329
PROCEDURE DeleteShortPayExpReport(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
----------------------------------------------------------------------
l_report_header_id AP_WEB_DB_EXPRPT_PKG.expHdr_headerID;
Line: 9339
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'start DeleteShortPayExpReport');
Line: 9350
DeleteExpReportFromAPTables(l_report_header_id);
Line: 9356
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'end DeleteShortPayExpReport');
Line: 9360
Wf_Core.Context('AP_WEB_EXPENSE_WF', 'DeleteShortPayExpReport',
p_item_type, p_item_key, to_char(p_actid), l_debug_info);
Line: 9363
END DeleteShortPayExpReport;
Line: 10220
select nls_language
into l_orig_language
from fnd_languages
where language_code = l_orig_language_code;
Line: 10229
select note_language_code
into l_new_language_code
from ap_expense_params;
Line: 10242
select language_code
into l_new_language_code
from fnd_languages
where installed_flag in ('B');
Line: 10251
select nls_language
into l_new_language
from fnd_languages
where language_code = l_new_language_code;
Line: 10360
SELECT orig_system_id
FROM wf_roles
WHERE orig_system = 'PER'
AND name = l_TransferToName;
Line: 10410
select message_name
into l_message_name
from wf_notifications
where notification_id = l_notificationID;
Line: 10446
AME_API6.updateApprovalStatus(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
transactionTypeIn => p_item_type,
transactionIdIn => l_AmeMasterItemKey,
approverIn => l_forwarder,
notificationIn => l_notificationIn,
forwardeeIn => l_forwardee);
Line: 10466
select nls_language
into l_orig_language
from fnd_languages
where language_code = l_orig_language_code;
Line: 10475
select note_language_code
into l_new_language_code
from ap_expense_params;
Line: 10488
select language_code
into l_new_language_code
from fnd_languages
where installed_flag in ('B');
Line: 10497
select nls_language
into l_new_language
from fnd_languages
where language_code = l_new_language_code;
Line: 10521
SELECT NVL(MORE_INFO_ROLE, RECIPIENT_ROLE) INTO l_temp_user_name
FROM wf_notifications
WHERE notification_id = l_notificationID;
Line: 10525
SELECT user_id INTO l_temp_entered_by FROM fnd_user WHERE user_name = l_temp_user_name;
Line: 10676
l_forwardee.api_insertion := ame_util.apiAuthorityInsertion;
Line: 10681
AME_API.updateApprovalStatus(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
transactionIdIn => p_item_key,
approverIn => l_forwarder,
forwardeeIn => l_forwardee,
transactionTypeIn => p_item_type);
Line: 10716
l_forwarder.api_insertion <> ame_util.apiInsertion) then
l_forwardee.api_insertion := ame_util.apiAuthorityInsertion;
Line: 10719
l_forwardee.api_insertion := ame_util.apiInsertion;
Line: 10728
-- Bug 7155445(sodash) Used the AME API AME_API6.updateApprovalStatus so that the comments of one approver is visible to other approvers when the notification is transferred or forwarded
AME_API6.updateApprovalStatus(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
transactionTypeIn => p_item_type,
transactionIdIn => l_itemkey,
approverIn => l_forwarder,
notificationIn => l_notificationIn,
forwardeeIn => l_forwardee);
Line: 10737
/* AME_API2.updateApprovalStatus(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
transactionTypeIn => p_item_type,
transactionIdIn => l_itemkey,
approverIn => l_forwarder,
forwardeeIn => l_forwardee); */
Line: 10919
PROCEDURE UpdateApproverIdInHeaders(
p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
----------------------------------------------------------------------
l_debug_info VARCHAR2(1000);
Line: 10936
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'start UpdateApproverIdInHeaders');
Line: 10966
SELECT orig_system_id INTO l_approver_person_id
FROM wf_roles
WHERE orig_system = 'PER'
AND name = l_TransferToName;
Line: 10973
l_debug_info := 'Update Headers table with Person Id';
Line: 10976
UPDATE ap_expense_report_headers
SET expense_current_approver_id = l_approver_person_id
WHERE report_header_id = p_item_key;
Line: 10982
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'end UpdateApproverIdInHeaders');
Line: 10986
Wf_Core.Context('AP_WEB_EXPENSE_WF', 'UpdateApproverIdInHeaders',
p_item_type, p_item_key, to_char(0), l_debug_info);
Line: 10989
END UpdateApproverIdInHeaders;
Line: 11117
IF (NOT AP_WEB_DB_EXPLINE_PKG.DeletePersonalLines(l_report_header_id)) THEN
NULL;
Line: 11178
update ap_expense_report_lines
set receipt_verified_flag = l_No
where report_header_id = l_report_header_id;
Line: 11512
select report_submitted_date
into l_report_submitted_date
from ap_expense_report_headers
where report_header_id = l_report_header_id;
Line: 11517
SELECT employee_id
INTO l_employee_id
FROM ap_expense_report_headers_all
WHERE report_header_id = l_report_header_id;
Line: 11535
AP_WEB_DB_VIOLATIONS_PKG.deleteDupViolationEntry(l_report_header_id);
Line: 11544
UPDATE ap_expense_report_headers_all
SET expense_status_code = l_expense_status_code,
expense_current_approver_id = l_current_approver_id,
expense_last_status_date=sysdate,
source = nvl(l_source, source),
last_update_date = sysdate
--last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
WHERE report_header_id = l_report_header_id
and nvl(expense_status_code, 'NULL') <> 'PAID'; -- Bug: 13482337, 13870204
Line: 11679
select end_date
into l_end_date
from wf_items
where item_type = l_itemtype
and item_key = l_itemkey;
Line: 11738
l_debug_info := 'Update AME as if rejected';
Line: 11800
AP_WEB_DB_VIOLATIONS_PKG.deleteDupViolationEntry(p_rep_header_id);
Line: 11808
update ap_expense_report_headers
set report_submitted_date = null,
last_update_date = sysdate, -- Bug 8411814 (sodash) added to update the who columns when a report is withdrawn
expense_last_status_date = sysdate,
expense_status_code = AP_WEB_OA_ACTIVE_PKG.C_WITHDRAWN
where report_header_id = p_rep_header_id;
Line: 11823
update ap_expense_report_lines
set receipt_verified_flag = l_No
where report_header_id = p_rep_header_id;
Line: 11865
select nls_language
into l_orig_language
from fnd_languages
where language_code = l_orig_language_code;
Line: 11874
select note_language_code
into l_new_language_code
from ap_expense_params;
Line: 11887
select language_code
into l_new_language_code
from fnd_languages
where installed_flag in ('B');
Line: 11896
select nls_language
into l_new_language
from fnd_languages
where language_code = l_new_language_code;
Line: 11907
select message_text
into l_note
from fnd_new_messages
where application_id = 200
and message_name = l_message_name
and language_code = l_new_language_code;
Line: 12361
l_debug_info := 'Traverse selected lines';
Line: 12398
l_document := l_document || th_select || l_prompts(13) || td_end;
Line: 12524
l_document := l_document || td_select;
Line: 12532
l_document := l_document || td_select;
Line: 12703
end if; -- traverse selected lines
Line: 12997
SELECT count(*)
INTO l_modified_count
FROM ap_expense_report_lines xl
WHERE xl.report_header_id = l_report_header_id
AND xl.mileage_rate_adjusted_flag = AP_WEB_DB_EXPLINE_PKG.C_Modified;
Line: 13010
SELECT count(*)
INTO l_new_count
FROM ap_expense_report_lines xl
WHERE xl.report_header_id = l_report_header_id
AND (xl.mileage_rate_adjusted_flag = AP_WEB_DB_EXPLINE_PKG.C_New
OR xl.mileage_rate_adjusted_flag = AP_WEB_DB_EXPLINE_PKG.C_Split);
Line: 13091
SELECT XL.VEHICLE_CATEGORY_CODE,
XL.VEHICLE_TYPE,
XL.FUEL_TYPE,
nvl(XL.TRIP_DISTANCE,0),
XL.DISTANCE_UNIT_CODE,
XL.CURRENCY_CODE,
XL.START_EXPENSE_DATE,
XL.END_EXPENSE_DATE,
XL.WEB_PARAMETER_ID,
XP.COMPANY_POLICY_ID,
SH.CURRENCY_PREFERENCE
INTO l_vehicle_category_code,
l_vehicle_type,
l_fuel_type,
l_trip_distance,
l_distance_unit_code,
l_currency_code,
l_start_expense_date,
l_end_expense_date,
l_web_parameter_id,
l_policy_id,
l_currency_preference
FROM ap_expense_report_lines XL,
AP_EXPENSE_REPORT_HEADERS XH,
AP_EXPENSE_REPORT_PARAMS XP,
AP_POL_HEADERS SH
WHERE XH.report_header_id = p_report_header_id
AND XH.report_header_id = XL.report_header_id
AND XL.distribution_line_number = p_distribution_line_number
AND (XP.WEB_ENABLED_FLAG = 'Y'
OR XH.EXPENSE_REPORT_ID = XP.EXPENSE_REPORT_ID)
AND XL.web_parameter_id = XP.parameter_id
AND XP.company_policy_id = SH.policy_id;
Line: 13128
SELECT AH.DISTANCE_UOM,
AH.DISTANCE_THRESHOLDS_FLAG,
AH.EMPLOYEE_ROLE_FLAG
INTO l_distance_uom,
l_distance_thresholds_flag,
l_employee_role_flag
FROM AP_POL_HEADERS AH
WHERE AH.POLICY_ID = l_policy_id;
Line: 13194
SELECT DISTANCE_FIELD
INTO l_ou_distance_field
FROM AP_POL_CAT_OPTIONS
WHERE category_code = 'MILEAGE';
Line: 13242
PROCEDURE updateCumulativeMileage(
p_cumulative_mileage IN AP_WEB_EMPLOYEE_INFO.NUMERIC_VALUE%TYPE,
p_period_id IN AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE,
p_employee_id IN AP_WEB_EMPLOYEE_INFO.EMPLOYEE_ID%TYPE) IS
-------------------------------------------------------------------------------
l_count NUMBER := 0;
Line: 13250
SELECT count(*)
INTO l_count
FROM ap_web_employee_info_all
WHERE value_type = 'CUM_REIMB_DISTANCE'
AND period_id = p_period_id
AND employee_id = p_employee_id;
Line: 13258
UPDATE ap_web_employee_info_all
SET numeric_value = p_cumulative_mileage,
last_update_date = sysdate,
last_updated_by = p_employee_id
WHERE value_type = 'CUM_REIMB_DISTANCE'
AND period_id = p_period_id
AND employee_id = p_employee_id;
Line: 13266
INSERT INTO ap_web_employee_info_all
(EMPLOYEE_ID,
VALUE_TYPE,
NUMERIC_VALUE,
PERIOD_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ORG_ID)
VALUES(p_employee_id,
'CUM_REIMB_DISTANCE',
p_cumulative_mileage,
p_period_id,
sysdate,
p_employee_id,
sysdate,
p_employee_id,
mo_global.get_current_org_id() );
Line: 13289
AP_WEB_DB_UTIL_PKG.RaiseException('updateCumulativeMileage');
Line: 13291
END updateCumulativeMileage;
Line: 13300
select nvl(sum(mileage_rate),0)
into l_addon_rate_sum
from oie_addon_mileage_rates
where report_line_id = (select report_line_id
from ap_expense_report_lines
where report_header_id = p_report_header_id
and distribution_line_number = p_dist_line_number
and rownum = 1);
Line: 13476
PROCEDURE updateNewDistNumber(
p_index IN NUMBER,
p_last_index IN NUMBER,
p_added_total IN NUMBER,
p_mileage_line_array IN OUT NOCOPY AP_WEB_DB_EXPLINE_PKG.Mileage_Line_Array)IS
-------------------------------------------------------------------------------
l_debug_info VARCHAR2(200);
Line: 13504
AP_WEB_DB_UTIL_PKG.RaiseException('updateNewDistNumber');
Line: 13506
END updateNewDistNumber;
Line: 13528
l_updated_trip_dist NUMBER;
Line: 13530
l_insert_index NUMBER := p_mileage_line_array_count + 1;
Line: 13591
SELECT DISTANCE_FIELD
INTO l_ou_distance_field
FROM AP_POL_CAT_OPTIONS
WHERE category_code = 'MILEAGE';
Line: 13679
SELECT max(distribution_line_number) + 1
INTO l_next_dist_number
FROM AP_EXPENSE_REPORT_LINES
WHERE report_header_id = p_mileage_line_array(p_ml_index).report_header_id;
Line: 13688
l_updated_trip_dist := l_range_high - l_range_low;
Line: 13690
l_updated_trip_dist := l_over_threshold_distance;
Line: 13693
l_daily_distance := l_updated_trip_dist / p_mileage_line_array(p_ml_index).number_of_days;
Line: 13706
addToMileageArray(l_insert_index,
l_next_dist_number,
round(l_updated_trip_dist),
round(l_daily_distance),
l_new_rate,
p_mileage_line_array(p_ml_index).report_header_id,
p_ml_index,
p_mileage_line_array);
Line: 13715
p_mileage_line_array_count := l_insert_index ;
Line: 13731
updateNewDistNumber. -Akita */
updateNewDistNumber(p_ml_index + 1,
l_sl_array_count,
l_added_total,
p_mileage_line_array);
Line: 13789
l_bHeaderUpdated BOOLEAN := FALSE;
Line: 13814
cursor getAddonRates(l_report_line_id NUMBER) is select addon_rate_type
from oie_addon_mileage_rates
where report_line_id = l_report_line_id;
Line: 14033
-- update reimbursable amount (Rate)
l_new_rate := getRate(
p_sh_distance_uom => l_sh_distance_uom,
p_sh_currency_code => l_sh_currency_code,
p_mileage_line => l_mileage_line_array(i),
p_schedule_line => l_schedule_line_array(1));
Line: 14042
updateExpenseMileageLines.
*/
l_mileage_line_array(i).amount :=
AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(
l_new_rate * l_mileage_line_array(i).trip_distance,
l_mileage_line_array(i).reimbursement_currency_code);
Line: 14081
updateCumulativeMileage(l_cumulative_mileage,
l_period_id,
l_employee_id);
Line: 14088
SELECT CALCULATE_AMOUNT_FLAG
INTO l_client_extension_enabled
FROM ap_expense_report_params
WHERE parameter_id = l_mileage_line_array(i).web_parameter_id;
Line: 14169
l_temp_array.delete; -- bug 5358186
Line: 14177
AP_WEB_DB_EXPLINE_PKG.updateExpenseMileageLines(l_mileage_line_array, l_bHeaderUpdated);
Line: 14180
l_debug_info := 'update display_total item attribute if ap_expense_report_headers has been updated';
Line: 14182
IF (l_bHeaderUpdated = TRUE) THEN
IF (NOT AP_WEB_DB_EXPRPT_PKG.UpdateHeaderTotal(l_report_header_id)) THEN
NULL;
Line: 14239
l_debug_info := 'Select from ap_pol_violations table';
Line: 14242
SELECT count(*)
INTO l_violations_count
FROM ap_pol_violations
WHERE report_header_id = l_report_header_id
and distribution_line_number > 0;
Line: 14390
select audit_code
from ap_expense_report_headers
where report_header_id = p_report_header_id;
Line: 14395
select rs.assign_auditor_stage_code, rs.audit_all,
rs.paperless_audit_cc_only_flag,
rs.paperless_audit_violation_flag,
rs.paperless_audit_pdm_only_flag,
aerh.workflow_approved_flag
from ap_expense_report_headers aerh,
ap_aud_rule_sets rs,
ap_aud_rule_assignments_all rsa
where aerh.report_header_id = p_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'RULE'
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
Line: 14412
select rs.recpt_assign_stage_code,
rs.AUD_IMG_RECEIPT_REQUIRED,
rs.AUD_PAPER_RECEIPT_REQUIRED
from ap_expense_report_headers aerh,
ap_aud_rule_sets rs,
ap_aud_rule_assignments_all rsa
where aerh.report_header_id = p_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'RULE'
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
Line: 14453
SELECT auditor_id INTO l_auditor_id FROM ap_aud_queues WHERE expense_report_id = l_report_header_id;
Line: 14499
select nvl(image_receipts_status,'NOT_REQUIRED') ,
nvl(receipts_status, 'NOT_REQUIRED') into l_image_receipts_status, l_receipts_status from
ap_expense_report_headers_all where report_header_id = l_report_header_id;
Line: 14524
select nvl(image_receipts_status,'NOT_REQUIRED') ,
nvl(receipts_status, 'NOT_REQUIRED') into l_image_receipts_status, l_receipts_status from
ap_expense_report_headers_all where report_header_id = l_report_header_id;
Line: 14546
select nvl(image_receipts_status,'NOT_REQUIRED') ,
nvl(receipts_status, 'NOT_REQUIRED') into l_image_receipts_status, l_receipts_status from
ap_expense_report_headers_all where report_header_id = l_report_header_id;
Line: 14742
SELECT user_name INTO l_auditor_name
FROM fnd_user
WHERE user_id=(SELECT last_audited_by
FROM ap_expense_report_headers_all
WHERE report_header_id=p_item_key);
Line: 15001
SELECT WORKFLOW_APPROVED_FLAG
INTO l_workflow_approved_flag
FROM AP_EXPENSE_REPORT_HEADERS
WHERE REPORT_HEADER_ID = l_report_header_id;
Line: 15049
select audit_code, workflow_approved_flag
from ap_expense_report_headers
where report_header_id = p_report_header_id;
Line: 15070
l_debug_info := 'Retrieve UPDATE_MANAGER_APPROVED Activity Attribute';
Line: 15075
'UPDATE_MANAGER_APPROVED');
Line: 15092
l_debug_info := 'Update the Expense Report as Mgr Approved so that it can be auditable';
Line: 15252
select nls_language
into l_orig_language
from fnd_languages
where language_code = l_orig_language_code;
Line: 15261
select note_language_code
into l_new_language_code
from ap_expense_params;
Line: 15274
select language_code
into l_new_language_code
from fnd_languages
where installed_flag in ('B');
Line: 15283
select nls_language
into l_new_language
from fnd_languages
where language_code = l_new_language_code;
Line: 15304
SELECT approval_type, workflow_approved_flag INTO l_approval_type, l_workflow_flag
FROM AP_EXPENSE_REPORT_HEADERS_ALL
WHERE report_header_id = l_report_header_id;
Line: 15325
SELECT recipient_role INTO l_user_name
FROM wf_notifications
WHERE message_type = 'APEXP'
AND message_name = 'OIE_REQ_EXPENSE_REPORT_APPRVL'
AND ( item_key = p_item_key
OR p_item_key = SubStr(context,7,length(p_item_key)) )
AND status <> 'CANCELED';
Line: 15365
SELECT recipient_role INTO l_user_name
FROM wf_notifications
WHERE message_type = 'APEXP'
AND message_name = 'OIE_REQUEST_EMPLOYEE_APPROVAL'
AND ( item_key = p_item_key
OR p_item_key = SubStr(context,7,length(p_item_key)) )
AND status <> 'CANCELED';
Line: 15394
select message_text
into l_note_prefix
from fnd_new_messages
where application_id = 200
and message_name = l_message_name
and language_code = l_new_language_code;
Line: 15518
SELECT
nvl(pj.approval_authority, 0)
INTO p_jobLevel
FROM
per_jobs pj,
per_all_assignments_f pa
WHERE
pj.job_id = pa.job_id
AND pa.person_id = p_personId
AND pa.primary_flag = 'Y'
AND pa.assignment_type in ('E' , 'C') --Support Contingent Workres
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(sysdate) between pa.effective_start_date and pa.effective_end_date;
Line: 15785
UPDATE AP_EXPENSE_REPORT_HEADERS
SET EXPENSE_STATUS_CODE = 'PAID'
WHERE REPORT_HEADER_ID = p_report_header_id;
Line: 15798
Bug 2777245: Update expense report header and lines data after submission
through self-service and just before workflow kicks off.
Note: Customer can use this procedure to manipualte the values in
Header and Lines table.
*/
----------------------------------------------------------------------------
Procedure UpdateHeaderLines(p_report_header_id IN Number)
IS
----------------------------------------------------------------------------
BEGIN
UPDATE AP_EXPENSE_REPORT_HEADERS
SET DESCRIPTION= AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(description)
WHERE REPORT_HEADER_ID = p_report_header_id;
Line: 15816
UPDATE AP_EXPENSE_REPORT_LINES
SET JUSTIFICATION = AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(justification),
SUBMITTED_AMOUNT = AMOUNT
WHERE REPORT_HEADER_ID = p_report_header_id;
Line: 15825
UPDATE ap_expense_report_lines_all aerl
SET expenditure_type =
(SELECT pa_expenditure_type
FROM ap_expense_report_params_all
WHERE parameter_id = aerl.web_parameter_id
),
expenditure_item_date = NVL(end_expense_date, start_expense_date)
WHERE report_header_id = p_report_header_id
AND expenditure_type IS NULL
AND expenditure_item_date IS NULL
AND EXISTS
(SELECT project_id
FROM ap_exp_report_dists_all
WHERE report_line_id = aerl.report_line_id
AND project_id IS NOT NULL
);
Line: 15846
END UpdateHeaderLines;
Line: 15907
UPDATE ap_expense_report_headers erh
SET workflow_approved_flag = 'S',
expense_status_code = null,
source = 'NonValidatedWebExpense'
WHERE report_header_id = p_report_header_id;
Line: 15960
select aerh.report_header_id,
fnd.employee_id,
aerh.employee_id,
aerh.invoice_num,
aerh.default_currency_code,
aerh.flex_concatenated,
aerh.description,
aerh.override_approver_id,
aerh.week_end_date,
aerh.workflow_approved_flag
into l_report_header_id,
l_preparer_id,
l_employee_id,
l_document_number,
l_reimb_curr,
l_cost_center,
l_purpose,
l_approver_id,
l_week_end_date,
l_workflow_flag
from ap_expense_report_headers aerh,
fnd_user fnd
where aerh.report_header_id = l_report_header_id
and fnd.user_id = aerh.created_by;
Line: 15985
select sum(aerl.amount),
sum(decode(sign(aerl.amount),-1,0,aerl.amount))
into l_total,
l_new_total
from ap_expense_report_lines aerl
where aerl.report_header_id = l_report_header_id
and (itemization_parent_id is null OR itemization_parent_id <> -1);
Line: 16039
select message_type, item_key
into l_item_type, l_temp_item_key
from wf_notifications
where notification_id = p_ntf_id
and rownum = 1;
Line: 16050
select 'Y'
into l_access_granted
from wf_item_activity_statuses
where item_type = l_item_type
and ((item_key = to_char(p_item_key)) or (item_key like to_char(p_item_key) || '-%'))
and notification_id = p_ntf_id
and rownum = 1;
Line: 16058
select 'Y'
into l_access_granted
from wf_item_activity_statuses_h
where item_type = l_item_type
and ((item_key = to_char(p_item_key)) or (item_key like to_char(p_item_key) || '-%'))
and notification_id = p_ntf_id
and rownum = 1;
Line: 16070
select item_key
into l_item_key
from wf_item_activity_statuses
where item_type = l_item_type
and notification_id = p_ntf_id
and rownum = 1;
Line: 16077
select item_key
into l_item_key
from wf_item_activity_statuses_h
where item_type = l_item_type
and notification_id = p_ntf_id
and rownum = 1;
Line: 16085
select 'Y'
into l_access_granted
from dual
where
(p_item_key = WF_ENGINE.GetItemAttrNumber(l_item_type, l_item_key, 'EXPENSE_REPORT_ID')
or
p_item_key = WF_ENGINE.GetItemAttrNumber(l_item_type, l_item_key, 'HOLDING_EXPENSE_REPORT_ID'))
and rownum = 1;
Line: 16097
select 'Y'
into l_access_granted
from wf_item_activity_statuses
where item_type = l_item_type
and ((item_key like '%'||to_char(p_item_key)||'%') or (item_key = l_temp_item_key))
and notification_id = p_ntf_id
and rownum = 1;
Line: 16105
select 'Y'
into l_access_granted
from wf_item_activity_statuses_h
where item_type = l_item_type
and ((item_key like '%'||to_char(p_item_key)||'%') or (item_key = l_temp_item_key))
and notification_id = p_ntf_id
and rownum = 1;
Line: 16126
select 'Y'
into l_access_granted
from wf_notifications wfn ,
WF_USER_ROLES wur
where wur.user_name = p_user_name
and wfn.notification_id = p_ntf_id
and ( wfn.recipient_role = wur.role_name
OR
( wfn.more_info_role is not null and wfn.more_info_role = wur.role_name )
OR
( wfn.from_role is not null and wfn.from_role = wur.role_name ) )
and rownum = 1;
Line: 16249
select end_date
into l_end_date
from wf_notifications
where message_type = 'APEXP'
and message_name = 'OIE_AME_EXPENSE_REPORT_APPRVL'
and item_key = l_child_item_key;
Line: 16820
We get the approver response and update the attribute 'AME Approver Response'
with approver response, which would be used in AME Approval Process'.
Update AME of approver response.
*/
----------------------------------------------------------------------
PROCEDURE AMEPropagateApprovalResult(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
----------------------------------------------------------------------
l_debug_info varchar2(200);
Line: 16962
l_debug_info := 'Call AME_API2.updateApprovalStatus ';
Line: 16971
AME_API2.updateApprovalStatus(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
transactionTypeIn => p_item_type,
transactionIdIn => l_AmeMasterItemKey,
approverIn => l_approverIn);
Line: 16976
/*AME_API2.updateApprovalStatus2(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
transactionIdIn => l_AmeMasterItemKey,
approvalStatusIn => l_approvalStatusIn,
approverNameIn => l_approver_name,
transactionTypeIn => 'APEXP');
Line: 17191
UPDATE ap_expense_report_headers_all
SET expense_current_approver_id = C_AME_MULTIPLE_CURR_APPROVER
WHERE report_header_id = l_report_header_id;
Line: 17195
UPDATE ap_expense_report_headers_all
SET expense_current_approver_id = l_approversOut(1).orig_system_id,
approval_type = l_approversOut(1).orig_system
WHERE report_header_id = l_report_header_id;
Line: 17217
FUNCTION IsExpAccountsUpdated(p_report_line_id IN NUMBER)
RETURN VARCHAR2 IS
-----------------------------------------------------------------------
l_ExpAccountsUpdated VARCHAR2(10);
Line: 17223
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'start IsExpAccountsUpdated');
Line: 17225
select decode(nvl(preparer_modified_flag, 'N'), 'Y', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), null)
into l_ExpAccountsUpdated
from ap_exp_report_dists
where report_line_id = p_report_line_id
and rownum = 1;
Line: 17232
AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'end IsExpAccountsUpdated');
Line: 17234
RETURN l_ExpAccountsUpdated;
Line: 17238
RETURN l_ExpAccountsUpdated;
Line: 17239
END IsExpAccountsUpdated;
Line: 17249
select context into l_context
from wf_notifications
where notification_id = p_notification_id;
Line: 17326
select nvl(rs.image_req_appr_flag,'X'),
nvl(aerh.image_receipts_status, 'NOT_REQUIRED') into l_req_mgr_appr,l_img_recpt_status
from ap_expense_report_headers aerh,
ap_aud_rule_sets rs,
ap_aud_rule_assignments_all rsa
where aerh.report_header_id = l_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'RECEIPT'
and TRUNC(nvl(aerh.report_submitted_date,SYSDATE))
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
Line: 17341
update ap_expense_report_headers_all
set expense_status_code = 'PENDING_IMAGE_SUBMISSION'
where report_header_id = l_report_header_id;
Line: 17387
select nvl(rs.req_reappr_flag,'X')
into l_re_appr_flag
from ap_expense_report_headers aerh,
ap_aud_rule_sets rs,
ap_aud_rule_assignments_all rsa
where aerh.report_header_id = l_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'RECEIPT'
and TRUNC(nvl(aerh.report_submitted_date,SYSDATE))
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
Line: 17437
UPDATE ap_expense_report_headers
SET image_receipts_status = l_image_receipts_status,
last_update_date = sysdate,
last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
WHERE report_header_id = l_report_header_id;
Line: 17473
UPDATE ap_expense_report_headers
SET receipts_status = l_orig_receipts_status,
last_update_date = sysdate,
last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
WHERE report_header_id = l_report_header_id;
Line: 17486
PROCEDURE UpdateExpenseStatusCode(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
----------------------------------------------------------------------
l_report_header_id AP_WEB_DB_EXPRPT_PKG.expHdr_headerID;
Line: 17509
UPDATE ap_expense_report_headers
SET expense_status_code = l_expense_status_code,
last_update_date = sysdate,
last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
WHERE report_header_id = l_report_header_id;
Line: 17519
END UpdateExpenseStatusCode;
Line: 17540
SELECT count(*) into l_line_count FROM ap_expense_report_lines
WHERE report_header_id = l_report_header_id;
Line: 17588
SELECT missing_img_just
INTO l_missing_image_just
FROM ap_expense_report_headers
WHERE report_header_id = p_report_header_id;
Line: 17606
SELECT report_header_id INTO l_report_header_id
FROM ap_expense_report_headers where bothpay_parent_id = p_report_header_id;