[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT base_currency_code, nvl((select exchange_rate_type from ap_pol_exrate_options where enabled = 'Y'), default_exchange_rate_type) exchange_rate_type
INTO P_BaseCurrencyCode, P_ExchangeRateType
FROM ap_system_parameters;
SELECT glps.start_date, glps.end_date
FROM ap_system_parameters SP,
gl_sets_of_books SOB,
gl_date_period_map map,
gl_periods glps
WHERE SOB.set_of_books_id = SP.set_of_books_id
AND MAP.period_set_name = SOB.period_set_name
AND MAP.period_type = P_PeriodType
AND MAP.accounting_date = to_date(P_Date, icx_sec.getID(icx_sec.PV_DATE_FORMAT))
AND GLPS.period_name = MAP.period_name
AND GLPS.period_set_name = SOB.period_set_name;
SELECT distinct supervisor_id
FROM per_all_assignments_f
WHERE person_id = P_PersonId
AND P_StartDate <= effective_end_date
AND P_EndDate >= effective_start_date
AND assignment_type in ('C','E');
SELECT employee_id
INTO L_SupervisorEmpId
FROM fnd_user
WHERE user_id = P_SupervisorId;
SELECT employee_id
INTO L_SupervisorEmpId
FROM fnd_user
WHERE user_id = P_SupervisorId;
SELECT sum(decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
))) as expense_amount
FROM ap_expense_report_lines_v aerl
WHERE aerl.employee_id = P_EmployeeId
AND aerl.week_end_date between P_StartDate and P_EndDate
AND nvl(aerl.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory);
SELECT SUM(line_amount) AS violation_line_amount
FROM
(
SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as line_amount
FROM ap_expense_report_lines_v aerl
WHERE EXISTS ( SELECT 'X'
FROM ap_pol_violations_all apv
WHERE apv.report_header_id = aerl.report_header_id
AND apv.distribution_line_number = aerl.distribution_line_number
AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
AND ( aerl.employee_id= P_EmployeeId or aerl.paid_on_behalf_employee_id = P_EmployeeId )
AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
);
SELECT sum(violation_amount) as violation_amount,
sum(number_of_violations) as number_of_violations
FROM
( SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
count(aerv.report_header_id) as number_of_violations
FROM ap_expense_report_violations_v aerv
WHERE ( (aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
UNION ALL
/* The below query is to fetch the daily sum limit violations for MEALS category */
SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.exceeded_amount
)) as violation_amount,
1 as number_of_violations
FROM ap_expense_report_headers_all aerh,
ap_pol_violations_all apv
WHERE aerh.report_header_id = apv.report_header_id
AND apv.distribution_line_number = -1
AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
AND aerh.source <> 'Both Pay'
AND NVL(aerh.expense_status_code,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
aerh.Workflow_approved_flag,
aerh.report_header_id,
'N'
)) IN ('MGRPAYAPPR','INVOICED','PAID',
'HOLD_PENDING_RECEIPTS','PEND_HOLDS_CLEARANCE','PARPAID')
AND ( (aerh.employee_id = P_EmployeeId) or (aerh.paid_on_behalf_employee_id = P_EmployeeId))
AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
AND aerh.week_end_date BETWEEN P_StartDate AND P_EndDate
UNION ALL
/* The below query is to bundle up RECEIPT_MISSING violations per line */
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
GL_CURRENCY_API.convert_amount_sql( aerv.currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
1 as number_of_violations
FROM ap_expense_report_violations_v aerv
WHERE ( (aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.week_end_date BETWEEN P_StartDate AND P_EndDate
group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
);
SELECT count(number_of_violations),
violation_type
from
(
SELECT 1 as number_of_violations,
alc.displayed_field as violation_type
FROM ap_lookup_codes alc,
ap_expense_report_violations_v aerv
WHERE alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
AND alc.lookup_code = aerv.violation_type
AND ( aerv.employee_id = P_EmployeeId OR aerv.paid_on_behalf_employee_id = P_EmployeeId)
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
UNION ALL
/* Count all the violations for Daily Sum Limit */
SELECT 1 as number_of_violations,
alc.displayed_field as violation_type
FROM ap_expense_report_headers_all aerh,
ap_pol_violations_all apv,
ap_lookup_codes alc
WHERE aerh.report_header_id = apv.report_header_id
AND apv.distribution_line_number = -1
AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
AND aerh.source <> 'Both Pay'
AND NVL(aerh.expense_status_code,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
aerh.Workflow_approved_flag,
aerh.report_header_id,
'N'
)) IN ('MGRPAYAPPR','INVOICED','PAID',
'HOLD_PENDING_RECEIPTS','PEND_HOLDS_CLEARANCE','PARPAID')
AND apv.violation_type = 'DAILY_SUM_LIMIT'
AND ( aerh.employee_id = P_EmployeeId OR aerh.paid_on_behalf_employee_id = P_EmployeeId)
AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
AND aerh.week_end_date between P_StartDate and P_EndDate
AND apv.violation_type = alc.lookup_code
AND alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
UNION ALL
/* Count all the violations for Receipts missing */
SELECT 1 as number_of_violations,
alc.displayed_field as violation_type
FROM ap_lookup_codes alc,
ap_expense_report_violations_v aerv
WHERE alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
AND alc.lookup_code = aerv.violation_type
AND ( aerv.employee_id = P_EmployeeId OR aerv.paid_on_behalf_employee_id = P_EmployeeId)
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type = 'RECEIPT_MISSING'
group by nvl(aerv.itemization_parent_id, aerv.report_line_id), alc.displayed_field
)
GROUP BY violation_type
ORDER BY 1 desc;
/* SELECT distinct
pap.full_name,
pap.person_id,
paa.supervisor_id,
greatest(paa.effective_start_date, P_StartDate) as Start_Date,
least(paa.effective_end_date, P_EndDate) as End_Date
FROM per_all_people_f pap,
per_all_assignments_f paa
WHERE pap.person_id = paa.person_id
AND pap.person_id = P_EmployeeId
AND P_StartDate < paa.effective_end_date
AND P_EndDate > paa.effective_start_date
AND (paa.assignment_type = 'E' OR paa.assignment_type = 'C')
AND P_StartDate < pap.effective_end_date
AND P_EndDate > pap.effective_start_date
UNION ALL
SELECT distinct
pap.full_name,
pap.person_id,
paa.supervisor_id,
greatest(paa.effective_start_date, P_StartDate) as Start_Date,
least(paa.effective_end_date, P_EndDate) as End_Date
FROM per_all_people_f pap,
per_all_assignments_f paa
WHERE pap.person_id = paa.person_id
AND paa.supervisor_id = P_EmployeeId
AND 'MANAGER' = P_QryType
AND P_StartDate < paa.effective_end_date
AND P_EndDate > paa.effective_start_date
AND (paa.assignment_type = 'E' OR paa.assignment_type = 'C')
AND P_StartDate < pap.effective_end_date
AND P_EndDate > pap.effective_start_date;
SELECT FULL_NAME,
PERSON_ID,
SUPERVISOR_ID,
GREATEST( P_StartDate, MIN( effective_start_date ) ) as Start_Date,
MAX_END_DATE as End_Date
FROM
( SELECT distinct
pap.full_name,
pap.person_id,
paa.supervisor_id,
pap.effective_start_date,
paa.effective_end_date,
( select least( P_EndDate , max(effective_end_date) )
from per_all_assignments_f
where person_id = pap.person_id
and supervisor_id = paa.supervisor_id
and ( assignment_type = 'E' OR assignment_type = 'C')
start with effective_start_date = paa.effective_start_date
connect by person_id = pap.person_id
and supervisor_id = paa.supervisor_id
AND ( assignment_type = 'E' OR assignment_type = 'C')
and prior effective_end_date = ( effective_start_date - 1 )
) max_end_date
FROM per_all_people_f pap,
per_all_assignments_f paa,
per_assignment_status_types past
WHERE pap.person_id = paa.person_id
AND ( pap.person_id = P_EmployeeId or ( P_QryType = 'MANAGER' AND paa.supervisor_id = P_EmployeeId ) )
AND P_StartDate <= paa.effective_end_date
AND P_EndDate >= paa.effective_start_date
AND (paa.assignment_type = 'E' OR paa.assignment_type = 'C')
AND P_StartDate <= pap.effective_end_date
AND P_EndDate >= pap.effective_start_date
AND past.assignment_status_type_id = paa.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK')
) V1
WHERE max_end_date is not null
GROUP BY FULL_NAME, PERSON_ID, SUPERVISOR_ID, MAX_END_DATE;
DELETE FROM AP_WEB_MANAGEMENT_REPORTS_GT;
INSERT INTO AP_WEB_MANAGEMENT_REPORTS_GT (
EMPLOYEE_ID,
SUPERVISOR_ID,
TOTAL_EXPENSES,
VIOLATION_LINE_AMOUNT,
VIOLATION_AMOUNT,
PERCENT_VIOLATION,
NUMBER_OF_VIOLATIONS,
MOST_VIOLATED_POLICY,
FULL_NAME,
EXPENSE_CATEGORY,
VIOLATION_TYPE,
PERIOD_TYPE,
PERIOD_DATE,
START_DATE_RANGE,
END_DATE_RANGE,
ROLLUP_TYPE,
REPORTING_CURRENCY_CODE
)
VALUES (
L_ByManagerTab( i ).employee_id,
L_ByManagerTab( i ).supervisor_id,
L_ByManagerTab( i ).total_expenses,
L_ByManagerTab( i ).violation_line_amount,
L_ByManagerTab( i ).violation_amount,
L_ByManagerTab( i ).percent_violation,
L_ByManagerTab( i ).number_of_violations,
L_ByManagerTab( i ).most_violated_policy,
L_ByManagerTab( i ).full_name,
P_ExpenseCategory,
P_ViolationType,
P_PeriodType,
P_Date,
L_ByManagerTab( i ).effective_start_date,
L_ByManagerTab( i ).effective_end_date,
P_QryType,
NVL(P_UserCurrencyCode, L_BaseCurrencyCode)
);
SELECT lookup_code,
displayed_field
FROM ap_lookup_codes
WHERE lookup_type = 'OIE_EXPENSE_CATEGORY'
AND lookup_code = decode (P_ExpenseCategory, 'ALL', lookup_code, P_ExpenseCategory);
SELECT sum(NVL(expense_amount,0)) as expense_amount
FROM
( SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as expense_amount
FROM ap_expense_report_lines_v aerl
WHERE ((aerl.employee_id= P_EmployeeId) or (aerl.paid_on_behalf_employee_id = P_EmployeeId))
AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
UNION ALL
SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as expense_amount
FROM per_all_assignments_f paf,
ap_expense_report_lines_v aerl
WHERE paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND (( aerl.employee_id = paf.person_id) or (aerl.paid_on_behalf_employee_id = paf.person_id))
AND aerl.week_end_date BETWEEN greatest(paf.effective_start_date,P_StartDate) AND least(paf.effective_end_date,P_EndDate)
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
AND 'MANAGER' = P_QryType
);
SELECT SUM(line_amount) AS violation_line_amount
FROM
(
SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as line_amount
FROM ap_expense_report_lines_v aerl
WHERE EXISTS ( SELECT 'X'
FROM ap_pol_violations_all apv
WHERE apv.report_header_id = aerl.report_header_id
AND apv.distribution_line_number = aerl.distribution_line_number
AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
AND ((aerl.employee_id= P_EmployeeId) or (aerl.paid_on_behalf_employee_id = P_EmployeeId))
AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
UNION ALL
SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as line_amount
FROM ap_expense_report_lines_v aerl,
per_all_assignments_f paf
WHERE EXISTS ( SELECT 'X'
FROM ap_pol_violations_all apv
WHERE apv.report_header_id = aerl.report_header_id
AND apv.distribution_line_number = aerl.distribution_line_number
AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
AND paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ((aerl.employee_id = paf.person_id) or (aerl.paid_on_behalf_employee_id = paf.person_id))
AND aerl.week_end_date BETWEEN greatest(paf.effective_start_date,P_StartDate) AND least(paf.effective_end_date,P_EndDate)
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
AND 'MANAGER' = P_QryType
);
SELECT sum(nvl(violation_amount,0)) as violation_amount,
sum(nvl(allowable_amount,0)) as allowable_amount,
sum(nvl(number_of_violations,0)) as number_of_violations
FROM
(
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM ap_expense_report_violations_v aerv
WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
UNION ALL
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM per_all_assignments_f paf,
ap_expense_report_violations_v aerv
WHERE paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
AND 'MANAGER' = P_QryType
AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
UNION ALL
/* The below two queries are to fetch the daily sum limit violations for MEALS category */
SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.exceeded_amount
)) as violation_amount,
decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.allowable_amount
)) as allowable_amount,
1 as number_of_violations
FROM ap_expense_report_headers_all aerh,
ap_pol_violations_all apv
WHERE aerh.report_header_id = apv.report_header_id
AND aerh.org_id = apv.org_id
AND apv.distribution_line_number = -1
AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
AND aerh.source <> 'Both Pay'
AND NVL(aerh.expense_status_code,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
aerh.Workflow_approved_flag,
aerh.report_header_id,
'N'
)) IN ('MGRPAYAPPR','INVOICED','PAID',
'HOLD_PENDING_RECEIPTS','PEND_HOLDS_CLEARANCE','PARPAID')
AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
AND (aerh.employee_id = P_EmployeeId OR aerh.paid_on_behalf_employee_id = P_EmployeeId)
AND aerh.week_end_date BETWEEN P_StartDate AND P_EndDate
UNION ALL
SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.exceeded_amount
)) as violation_amount,
decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.allowable_amount
)) as allowable_amount,
1 as number_of_violations
FROM ap_expense_report_headers_all aerh,
ap_pol_violations_all apv,
per_all_assignments_f paf
WHERE aerh.report_header_id = apv.report_header_id
AND aerh.org_id = apv.org_id
AND apv.distribution_line_number = -1
AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
AND aerh.source <> 'Both Pay'
AND NVL(aerh.expense_status_code,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.source,
aerh.workflow_approved_flag,
aerh.report_header_id,
'N'
)) IN ('MGRPAYAPPR','INVOICED','PAID',
'HOLD_PENDING_RECEIPTS','PEND_HOLDS_CLEARANCE','PARPAID')
AND paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ( aerh.employee_id = paf.person_id OR aerh.paid_on_behalf_employee_id = paf.person_id )
AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
AND aerh.week_end_date between P_StartDate and P_EndDate
AND 'MANAGER' = P_QryType
/* The below query is to bundle up RECEIPT_MISSING violations per line */
UNION ALL
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM ap_expense_report_violations_v aerv
WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
UNION ALL
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM per_all_assignments_f paf,
ap_expense_report_violations_v aerv
WHERE paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
AND 'MANAGER' = P_QryType
group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
);
DELETE FROM AP_WEB_MANAGEMENT_REPORTS_GT;
INSERT INTO AP_WEB_MANAGEMENT_REPORTS_GT (
EMPLOYEE_ID,
TOTAL_EXPENSES,
VIOLATION_LINE_AMOUNT,
VIOLATION_AMOUNT,
ALLOWABLE_AMOUNT,
PERCENT_VIOLATION,
PERCENT_ALLOWABLE,
NUMBER_OF_VIOLATIONS,
EXPENSE_CATEGORY,
EXPENSE_CATEGORY_DESC,
VIOLATION_TYPE,
PERIOD_TYPE,
PERIOD_DATE,
ROLLUP_TYPE,
REPORTING_CURRENCY_CODE
)
VALUES (
P_EmployeeId,
L_ByExpCatTab( i ).total_expenses,
L_ByExpCatTab( i ).violation_line_amount,
L_ByExpCatTab( i ).violation_amount,
L_ByExpCatTab( i ).allowable_amount,
L_ByExpCatTab( i ).percent_violation,
L_ByExpCatTab( i ).percent_allowable,
L_ByExpCatTab( i ).number_of_violations,
L_ByExpCatTab( i ).expense_category,
L_ByExpCatTab( i ).expense_category_desc,
P_ViolationType,
P_PeriodType,
P_Date,
P_QryType,
NVL(P_UserCurrencyCode, L_BaseCurrencyCode)
);
SELECT lookup_code,
displayed_field
FROM ap_lookup_codes
WHERE lookup_type = 'OIE_POL_VIOLATION_TYPES'
AND lookup_code = decode (P_ViolationType, 'ALL', lookup_code, P_ViolationType);
SELECT SUM(line_amount) AS violation_line_amount
FROM
(
SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as line_amount
FROM ap_expense_report_lines_v aerl
WHERE EXISTS ( SELECT 'X'
FROM ap_pol_violations_all apv
WHERE apv.report_header_id = aerl.report_header_id
AND apv.distribution_line_number = aerl.distribution_line_number
AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType))
AND ((aerl.employee_id= P_EmployeeId) or (aerl.paid_on_behalf_employee_id = P_EmployeeId))
AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
UNION ALL
SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerl.amount
)) as line_amount
FROM ap_expense_report_lines_v aerl,
per_all_assignments_f paf
WHERE EXISTS ( SELECT 'X'
FROM ap_pol_violations_all apv
WHERE apv.report_header_id = aerl.report_header_id
AND apv.distribution_line_number = aerl.distribution_line_number
AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
AND paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ((aerl.employee_id = paf.person_id) or (aerl.paid_on_behalf_employee_id = paf.person_id))
AND aerl.week_end_date BETWEEN greatest(paf.effective_start_date,P_StartDate) AND least(paf.effective_end_date,P_EndDate)
AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
AND 'MANAGER' = P_QryType
AND aerl.paid_on_behalf_employee_id IS NULL
);
SELECT sum(nvl(violation_amount,0)) as violation_amount,
sum(nvl(allowable_amount,0)) as allowable_amount,
sum(nvl(number_of_violations,0)) as number_of_violations
FROM
(
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM ap_expense_report_violations_v aerv
WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
UNION ALL
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM per_all_assignments_f paf,
ap_expense_report_violations_v aerv
WHERE paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
AND 'MANAGER' = P_QryType
AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
UNION ALL
/* The below two queries are to fetch the daily sum limit violations for MEALS category */
SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.exceeded_amount
)) as violation_amount,
decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.allowable_amount
)) as allowable_amount,
1 as number_of_violations
FROM ap_expense_report_headers_all aerh,
ap_pol_violations_all apv
WHERE aerh.report_header_id = apv.report_header_id
AND aerh.org_id = apv.org_id
AND apv.distribution_line_number = -1
AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
AND aerh.source <> 'Both Pay'
AND NVL(aerh.expense_status_code,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
aerh.Workflow_approved_flag,
aerh.report_header_id,
'N'
)) IN ('MGRPAYAPPR','INVOICED','PAID',
'HOLD_PENDING_RECEIPTS','PEND_HOLDS_CLEARANCE','PARPAID')
AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
AND (aerh.employee_id = P_EmployeeId OR aerh.paid_on_behalf_employee_id = P_EmployeeId)
AND aerh.week_end_date BETWEEN P_StartDate AND P_EndDate
UNION ALL
SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.exceeded_amount
)) as violation_amount,
decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
P_BaseCurrencyCode,
SYSDATE,
P_ExchangeRateType,
apv.allowable_amount
)) as allowable_amount,
1 as number_of_violations
FROM ap_expense_report_headers_all aerh,
ap_pol_violations_all apv,
per_all_assignments_f paf
WHERE aerh.report_header_id = apv.report_header_id
AND aerh.org_id = apv.org_id
AND apv.distribution_line_number = -1
AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
AND aerh.source <> 'Both Pay'
AND NVL(aerh.expense_status_code,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.source,
aerh.workflow_approved_flag,
aerh.report_header_id,
'N'
)) IN ('MGRPAYAPPR','INVOICED','PAID',
'HOLD_PENDING_RECEIPTS','PEND_HOLDS_CLEARANCE','PARPAID')
AND paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ( aerh.employee_id = paf.person_id OR aerh.paid_on_behalf_employee_id = paf.person_id )
AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
AND aerh.week_end_date between P_StartDate and P_EndDate
AND 'MANAGER' = P_QryType
/* The below query is to bundle up RECEIPT_MISSING violations per line */
UNION ALL
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM ap_expense_report_violations_v aerv
WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
AND aerv.week_end_date between P_StartDate and P_EndDate
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
UNION ALL
SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.violation_amount
))) as violation_amount,
sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
P_BaseCurrencyCode,
sysdate,
P_ExchangeRateType,
aerv.allowable_amount
))) as allowable_amount,
count(aerv.report_header_id) as number_of_violations
FROM per_all_assignments_f paf,
ap_expense_report_violations_v aerv
WHERE paf.supervisor_id = P_EmployeeId
AND paf.effective_start_date < P_EndDate
AND paf.effective_end_date > P_StartDate
AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
AND 'MANAGER' = P_QryType
group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
);
DELETE FROM AP_WEB_MANAGEMENT_REPORTS_GT;
INSERT INTO AP_WEB_MANAGEMENT_REPORTS_GT (
EMPLOYEE_ID,
VIOLATION_LINE_AMOUNT,
VIOLATION_AMOUNT,
ALLOWABLE_AMOUNT,
PERCENT_VIOLATION,
PERCENT_ALLOWABLE,
NUMBER_OF_VIOLATIONS,
EXPENSE_CATEGORY,
VIOLATION_TYPE,
VIOLATION_TYPE_DESC,
PERIOD_TYPE,
PERIOD_DATE,
ROLLUP_TYPE,
REPORTING_CURRENCY_CODE
)
VALUES (
P_EmployeeId,
L_ByViolTypeTab( i ).violation_line_amount,
L_ByViolTypeTab( i ).violation_amount,
L_ByViolTypeTab( i ).allowable_amount,
L_ByViolTypeTab( i ).percent_violation,
L_ByViolTypeTab( i ).percent_allowable,
L_ByViolTypeTab( i ).number_of_violations,
P_ExpenseCategory,
L_ByViolTypeTab( i ).violation_type,
L_ByViolTypeTab( i ).violation_type_desc,
P_PeriodType,
P_Date,
P_QryType,
NVL(P_UserCurrencyCode, L_BaseCurrencyCode)
);