[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LP_SELECT_EMP := 'emp.employee_id ';
LP_SELECT_EMP_NAME := 'emp.full_name ';
LP_SELECT_SUP := 'emp.supervisor_id ';
LP_SELECT_EMP := 'perf.person_id ';
LP_SELECT_EMP_NAME := 'perf.full_name ';
LP_SELECT_SUP := 'pera.supervisor_id ';
and perf.business_group_id+0 = (select business_group_id from financials_system_parameters fsp
where perf.business_group_id = fsp.business_group_id)
and perf.person_id = pera.person_id
and pera.assignment_status_type_id = peras.assignment_status_type_id
and peras.per_system_status in (''TERM_ASSIGN'', ''SUSP_ASSIGN'')
and pera.primary_flag = ''Y''
and pera.assignment_type in (''E'',''C'')
and perf.employee_number is not null
and trunc(sysdate) between perf.effective_start_date and perf.effective_end_date
and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date';
LP_EMP_MGR := ' and (ac.employee_id in (select distinct employee_id
from per_employees_x emp1 -- Bug 3176205: Changed HR view to include all employees(inactive also) and exclude contingent workers.
where emp1.supervisor_id = ' || P_EMPLOYEE || ')
OR ac.employee_id = ' || P_EMPLOYEE || ') ';
LP_EMP_MGR1 := ' and (ac.employee_id in (select distinct person_id
from per_assignments_f pera1
where pera1.supervisor_id = ' || P_EMPLOYEE || ' and pera1.assignment_type in (''E'',''C'') )
OR ac.employee_id = ' || P_EMPLOYEE || ') ';
SELECT
LY.MEANING,
LN.MEANING,
LA.DISPLAYED_FIELD,
LM.DISPLAYED_FIELD,
LP.DISPLAYED_FIELD,
LD.DISPLAYED_FIELD,
LR.DISPLAYED_FIELD
INTO CP_NLS_YES,CP_NLS_NO,CP_NLS_ALL,CP_NLS_DISPUTED,CP_NLS_MGR_UNAPPROVED,CP_NLS_AP_UNAPPROVED,CP_NLS_REJECTED
FROM
FND_LOOKUPS LY,
FND_LOOKUPS LN,
AP_LOOKUP_CODES LA,
AP_LOOKUP_CODES LM,
AP_LOOKUP_CODES LP,
AP_LOOKUP_CODES LD,
AP_LOOKUP_CODES LR
WHERE LY.LOOKUP_TYPE = 'YES_NO'
AND LY.LOOKUP_CODE = 'Y'
AND LN.LOOKUP_TYPE = 'YES_NO'
AND LN.LOOKUP_CODE = 'N'
AND LA.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
AND LA.LOOKUP_CODE = 'ALL'
AND LD.LOOKUP_TYPE = 'CC_STATUS'
AND LD.LOOKUP_CODE = 'DISPUTED'
AND LM.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND LM.LOOKUP_CODE = 'PENDMGR'
AND LP.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND LP.LOOKUP_CODE = 'MGRAPPR'
AND LR.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND LR.LOOKUP_CODE = 'REJECTED';
SELECT
NAME,
CHART_OF_ACCOUNTS_ID
INTO CP_COMPANY_NAME_HEADER,CP_CHART_OF_ACCOUNTS_ID
FROM
GL_SETS_OF_BOOKS G,
AP_SYSTEM_PARAMETERS A
WHERE G.SET_OF_BOOKS_ID = A.SET_OF_BOOKS_ID;
SELECT
ALC.DISPLAYED_FIELD
INTO V_DISPLAY_STATUS
FROM
AP_LOOKUP_CODES ALC
WHERE ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND ALC.LOOKUP_CODE = C_STATUS;
SELECT
ALC.DISPLAYED_FIELD
INTO V_DISPLAY_STATUS
FROM
AP_LOOKUP_CODES ALC
WHERE ALC.LOOKUP_TYPE = 'SSE_CCARD_TRXN_CATEGORY'
AND ALC.LOOKUP_CODE = C_STATUS;
SELECT
P.BASE_CURRENCY_CODE,
C.PRECISION,
C.MINIMUM_ACCOUNTABLE_UNIT,
C.DESCRIPTION
INTO C_BASE_CURRENCY_CODE,C_BASE_PRECISION,C_BASE_MIN_ACCT_UNIT,C_BASE_DESCRIPTION
FROM
AP_SYSTEM_PARAMETERS P,
FND_CURRENCIES_VL C
WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
SELECT
EMPLOYEE_ID,
SUM(BILLED_AMOUNT),
BILLED_CURRENCY_CODE
FROM
( SELECT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
AND ( NVL(CCT.EXPENSED_AMOUNT
,0) = 0
OR ( CCT.EXPENSED_AMOUNT <> 0
AND CCT.CATEGORY = 'PERSONAL'
AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
,CCT.TRX_ID) = 'TRUE' ) )
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' )
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND AC.EMPLOYEE_ID in (
SELECT
DISTINCT
EMPLOYEE_ID
FROM
PER_EMPLOYEES_X
WHERE SUPERVISOR_ID = P_EMPLOYEE )
AND P_INCLUDE_DIRECTS = 'Y'
UNION ALL
SELECT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
AND ( NVL(CCT.EXPENSED_AMOUNT
,0) = 0
OR ( CCT.EXPENSED_AMOUNT <> 0
AND CCT.CATEGORY = 'PERSONAL'
AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
,CCT.TRX_ID) = 'TRUE' ) )
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' )
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND AC.EMPLOYEE_ID = P_EMPLOYEE
AND P_INCLUDE_DIRECTS = 'N'
UNION ALL
SELECT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
AND ( NVL(CCT.EXPENSED_AMOUNT
,0) = 0
OR ( CCT.EXPENSED_AMOUNT <> 0
AND CCT.CATEGORY = 'PERSONAL'
AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
,CCT.TRX_ID) = 'TRUE' ) )
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' )
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND P_EMPLOYEE is null
UNION ALL
SELECT
DISTINCT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'PERSONAL' , 'DEACTIVATED' )
AND ERH.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
AND ERH.EMPLOYEE_ID = AC.EMPLOYEE_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND AC.EMPLOYEE_ID in (
SELECT
DISTINCT
EMPLOYEE_ID
FROM
PER_EMPLOYEES_X
WHERE SUPERVISOR_ID = P_EMPLOYEE )
AND P_INCLUDE_DIRECTS = 'Y'
UNION ALL
SELECT
DISTINCT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'PERSONAL' , 'DEACTIVATED' )
AND ERH.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
AND ERH.EMPLOYEE_ID = AC.EMPLOYEE_ID
AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND AC.EMPLOYEE_ID = P_EMPLOYEE
AND P_INCLUDE_DIRECTS = 'N'
UNION ALL
SELECT
DISTINCT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND P_EMPLOYEE is null )
GROUP BY
EMPLOYEE_ID,
BILLED_CURRENCY_CODE;
SELECT
CARD_PROGRAM_NAME
INTO V_CARD_PROGRAM_NAME
FROM
AP_CARD_PROGRAMS
WHERE CARD_PROGRAM_ID = P_CARD_PROGRAM_ID;
SELECT
DISTINCT
ERH.REPORT_HEADER_ID,
ERH.EXPENSE_CURRENT_APPROVER_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND ( ERH.SOURCE = 'WebExpense'
AND ERH.WORKFLOW_APPROVED_FLAG is null
OR ERH.WORKFLOW_APPROVED_FLAG = 'P' )
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND ERH.EMPLOYEE_ID in (
SELECT
DISTINCT
EMPLOYEE_ID
FROM
PER_EMPLOYEES_X
WHERE SUPERVISOR_ID = P_EMPLOYEE )
AND P_INCLUDE_DIRECTS = 'Y'
AND ERH.EXPENSE_CURRENT_APPROVER_ID is not null
GROUP BY
ERH.REPORT_HEADER_ID,
ERH.EXPENSE_CURRENT_APPROVER_ID
UNION ALL
SELECT
DISTINCT
ERH.REPORT_HEADER_ID,
ERH.EXPENSE_CURRENT_APPROVER_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
AND ( ERH.SOURCE = 'WebExpense'
AND ERH.WORKFLOW_APPROVED_FLAG is null
OR ERH.WORKFLOW_APPROVED_FLAG = 'P' )
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND ERH.EMPLOYEE_ID = P_EMPLOYEE
AND P_INCLUDE_DIRECTS = 'N'
AND ERH.EXPENSE_CURRENT_APPROVER_ID is not null
GROUP BY
ERH.REPORT_HEADER_ID,
ERH.EXPENSE_CURRENT_APPROVER_ID
UNION ALL
SELECT
DISTINCT
ERH.REPORT_HEADER_ID,
ERH.EXPENSE_CURRENT_APPROVER_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND NVL(CCT.CATEGORY
,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND ( ERH.SOURCE = 'WebExpense'
AND ERH.WORKFLOW_APPROVED_FLAG is null
OR ERH.WORKFLOW_APPROVED_FLAG = 'P' )
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND P_EMPLOYEE is null
AND ERH.EXPENSE_CURRENT_APPROVER_ID is not null
GROUP BY
ERH.REPORT_HEADER_ID,
ERH.EXPENSE_CURRENT_APPROVER_ID;
SELECT
AC.EMPLOYEE_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.EXPENSED_AMOUNT
,0) = 0
AND NVL(CCT.CATEGORY
,'BUSINESS') = 'DISPUTED'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1)
AND NVL(P_BILLED_END_DATE
,NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1)
AND CCT.BILLED_AMOUNT > P_MIN_AMOUNT
GROUP BY
EMPLOYEE_ID;
SELECT
CARD_PROGRAM_NAME
INTO CP_CARD_PROGRAM_NAME
FROM
AP_CARD_PROGRAMS_ALL CP
WHERE CP.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID;
SELECT
FULL_NAME
INTO L_SUPERVISOR_NAME
FROM
PER_EMPLOYEES_X
WHERE EMPLOYEE_ID = SUPERVISOR_ID1;
SELECT
FULL_NAME
INTO L_SUPERVISOR_NAME
FROM
PER_EMPLOYEES_X
WHERE EMPLOYEE_ID = AGE_SUPERVISOR_ID;
SELECT
FULL_NAME
INTO L_EMPLOYEE_NAME
FROM
PER_EMPLOYEES_X
WHERE EMPLOYEE_ID = AGE_EMPLOYEE_ID;
SELECT
FULL_NAME
INTO L_EMP_NAME
FROM
PER_EMPLOYEES_X
WHERE EMPLOYEE_ID = EMPLOYEE_ID1;
SELECT
EMPLOYEE_ID,
SUM(BILLED_AMOUNT),
BILLED_CURRENCY_CODE
FROM
( SELECT
DISTINCT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS AC,
AP_CARD_PROGRAMS CP,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') NOT IN ( 'DEACTIVATED' , 'MATCHED' , 'CREDIT' )
AND erl.credit_card_trx_id (+) = CCT.TRX_ID
AND erh.report_header_id (+) = ERL.REPORT_HEADER_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND NVL(AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID)
,'UNUSED') in ( 'UNUSED' , 'SAVED' , 'INPROGRESS' , 'EMPAPPR' , 'REJECTED' , 'RESOLUTN' , 'WITHDRAWN' , 'RETURNED' )
AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
,CCT.TRX_ID) = 'TRUE'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND ( AC.EMPLOYEE_ID in (
SELECT
DISTINCT
EMPLOYEE_ID
FROM
PER_EMPLOYEES_X EMP1
WHERE EMP1.SUPERVISOR_ID = P_EMPLOYEE )
OR AC.EMPLOYEE_ID = P_EMPLOYEE )
AND ( TRUNC(SYSDATE) - ( CCT.POSTED_DATE + DECODE(CCT.CATEGORY
,'DISPUTED'
,NVL(P_IN_GRACE_DAYS
,0)
,0) ) between P_IN_MIN_BUCKET
AND P_IN_MAX_BUCKET )
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND P_INCLUDE_DIRECTS = 'Y'
AND exists (
SELECT
1
FROM
AP_EXPENSE_REPORT_HEADERS_ALL ERH2
WHERE ERH2.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
OR ( CCT.REPORT_HEADER_ID is null
AND NVL(CCT.EXPENSED_AMOUNT
,0) = 0 )
AND ROWNUM = 1 )
UNION ALL
SELECT
DISTINCT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS AC,
AP_CARD_PROGRAMS CP,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') NOT IN ( 'DEACTIVATED' , 'MATCHED' , 'CREDIT' )
AND erl.credit_card_trx_id (+) = CCT.TRX_ID
AND NVL(AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID)
,'UNUSED') in ( 'UNUSED' , 'SAVED' , 'INPROGRESS' , 'EMPAPPR' , 'REJECTED' , 'RESOLUTN' , 'WITHDRAWN' , 'RETURNED' )
AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
,CCT.TRX_ID) = 'TRUE'
AND erh.report_header_id (+) = ERL.REPORT_HEADER_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND ( TRUNC(SYSDATE) - ( CCT.POSTED_DATE + DECODE(CCT.CATEGORY
,'DISPUTED'
,NVL(P_IN_GRACE_DAYS
,0)
,0) ) between P_IN_MIN_BUCKET
AND P_IN_MAX_BUCKET )
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND AC.EMPLOYEE_ID = P_EMPLOYEE
AND P_INCLUDE_DIRECTS = 'N'
AND exists (
SELECT
1
FROM
AP_EXPENSE_REPORT_HEADERS_ALL ERH2
WHERE ERH2.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
OR ( CCT.REPORT_HEADER_ID is null
AND NVL(CCT.EXPENSED_AMOUNT
,0) = 0 )
AND ROWNUM = 1 )
UNION ALL
SELECT
DISTINCT
AC.EMPLOYEE_ID,
CCT.BILLED_AMOUNT,
CCT.BILLED_CURRENCY_CODE,
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS AC,
AP_CARD_PROGRAMS CP,
AP_EXPENSE_REPORT_LINES ERL,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.VALIDATE_CODE = 'Y'
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') NOT IN ( 'DEACTIVATED' , 'MATCHED' , 'CREDIT' )
AND erl.credit_card_trx_id (+) = CCT.TRX_ID
AND erh.report_header_id (+) = ERL.REPORT_HEADER_ID
AND NVL(ERH.VOUCHNO
,0) = 0
AND NVL(AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID)
,'UNUSED') in ( 'UNUSED' , 'SAVED' , 'INPROGRESS' , 'EMPAPPR' , 'REJECTED' , 'RESOLUTN' , 'WITHDRAWN' , 'RETURNED' )
AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
,CCT.TRX_ID) = 'TRUE'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND ( TRUNC(SYSDATE) - ( CCT.POSTED_DATE + DECODE(CCT.CATEGORY
,'DISPUTED'
,NVL(P_IN_GRACE_DAYS
,0)
,0) ) between P_IN_MIN_BUCKET
AND P_IN_MAX_BUCKET )
AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
,NULL
,-999999999999
,P_MIN_AMOUNT)
AND P_EMPLOYEE is null
AND exists (
SELECT
1
FROM
AP_EXPENSE_REPORT_HEADERS_ALL ERH2
WHERE ERH2.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
OR ( CCT.REPORT_HEADER_ID is null
AND NVL(CCT.EXPENSED_AMOUNT
,0) = 0 )
AND ROWNUM = 1 ) )
GROUP BY
EMPLOYEE_ID,
BILLED_CURRENCY_CODE;
SELECT
CCT.CARD_PROGRAM_ID CARD_PROGRAM_ID,
AC.EMPLOYEE_ID INACT_EMPLOYEE_ID,
CCT.BILLED_CURRENCY_CODE BILLED_CURRENCY_CODE,
SUM(CCT.BILLED_AMOUNT) TOTAL_AMT_POSTED
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC
WHERE AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CCT.TRX_ID in (
SELECT
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_CARD_PROGRAMS_ALL CP,
PER_PEOPLE_F PERF,
PER_ASSIGNMENTS_F PERA,
PER_ASSIGNMENT_STATUS_TYPES PERAS
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
AND NVL(CCT.EXPENSED_AMOUNT
,0) = 0
AND CCT.VALIDATE_CODE = 'Y'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.EMPLOYEE_ID = PERF.PERSON_ID
AND PERF.PERSON_ID = PERA.PERSON_ID
AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
AND PERA.PRIMARY_FLAG = 'Y'
AND PERA.ASSIGNMENT_TYPE = 'E'
AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
AND PERA.EFFECTIVE_END_DATE
AND TRUNC(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE)) between NVL(P_CC_BILLED_START_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1))
AND NVL(P_CC_BILLED_END_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1))
AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL
AND CCT.REPORT_HEADER_ID IS NULL
UNION ALL
SELECT
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_CARD_PROGRAMS_ALL CP,
PER_PEOPLE_F PERF,
PER_ASSIGNMENTS_F PERA,
PER_ASSIGNMENT_STATUS_TYPES PERAS,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND CCT.VALIDATE_CODE = 'Y'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.EMPLOYEE_ID = PERF.PERSON_ID
AND PERF.PERSON_ID = PERA.PERSON_ID
AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
AND PERA.PRIMARY_FLAG = 'Y'
AND PERA.ASSIGNMENT_TYPE = 'E'
AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
AND PERA.EFFECTIVE_END_DATE
AND CCT.REPORT_HEADER_ID = erh.report_header_id (+)
AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
AND TRUNC(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE)) between NVL(P_CC_BILLED_START_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1))
AND NVL(P_CC_BILLED_END_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1))
AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL )
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
GROUP BY
CCT.CARD_PROGRAM_ID,
AC.EMPLOYEE_ID,
CCT.BILLED_CURRENCY_CODE;
CURSOR C_CCTRX_UPDATE(P_CARD_PROGRAM_ID IN NUMBER,P_INACT_EMPLOYEE_ID IN NUMBER,P_CC_BILLED_START_DATE IN DATE,P_CC_BILLED_END_DATE IN DATE) IS
SELECT
DISTINCT
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_CARD_PROGRAMS_ALL CP,
PER_PEOPLE_F PERF,
PER_ASSIGNMENTS_F PERA,
PER_ASSIGNMENT_STATUS_TYPES PERAS
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.PAYMENT_FLAG <> 'Y'
AND NVL(CCT.EXPENSED_AMOUNT
,0) = 0
AND CCT.VALIDATE_CODE = 'Y'
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.EMPLOYEE_ID = P_INACT_EMPLOYEE_ID
AND PERF.PERSON_ID = AC.EMPLOYEE_ID
AND PERA.PERSON_ID = PERF.PERSON_ID
AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
AND PERA.PRIMARY_FLAG = 'Y'
AND PERA.ASSIGNMENT_TYPE = 'E'
AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
AND PERA.EFFECTIVE_END_DATE
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_CC_BILLED_START_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1))
AND NVL(P_CC_BILLED_END_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1))
AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL
UNION
SELECT
DISTINCT
CCT.TRX_ID
FROM
AP_CREDIT_CARD_TRXNS CCT,
AP_CARDS_ALL AC,
AP_CARD_PROGRAMS_ALL CP,
PER_PEOPLE_F PERF,
PER_ASSIGNMENTS_F PERA,
PER_ASSIGNMENT_STATUS_TYPES PERAS,
AP_EXPENSE_REPORT_HEADERS ERH
WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
AND CCT.PAYMENT_FLAG <> 'Y'
AND CCT.EXPENSED_AMOUNT <> 0
AND CCT.VALIDATE_CODE = 'Y'
AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.CARD_ID = CCT.CARD_ID
AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
AND AC.EMPLOYEE_ID = P_INACT_EMPLOYEE_ID
AND PERF.PERSON_ID = AC.EMPLOYEE_ID
AND PERA.PERSON_ID = PERF.PERSON_ID
AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
AND PERA.PRIMARY_FLAG = 'Y'
AND PERA.ASSIGNMENT_TYPE = 'E'
AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
AND PERA.EFFECTIVE_END_DATE
AND CCT.REPORT_HEADER_ID = erh.report_header_id (+)
AND NVL(CCT.CATEGORY
,'BUSINESS') <> 'DEACTIVATED'
AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
,ERH.WORKFLOW_APPROVED_FLAG
,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
AND NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) between NVL(P_CC_BILLED_START_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) - 1))
AND NVL(P_CC_BILLED_END_DATE
,(NVL(CCT.BILLED_DATE
,CCT.POSTED_DATE) + 1))
AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL;
R_CCTRX_UPDATE C_CCTRX_UPDATE%ROWTYPE;
SELECT
TO_CHAR(AP_CCARD_NOTIFICATION_ID_S.NEXTVAL)
INTO L_WF_ITEM_KEY
FROM
SYS.DUAL;
OPEN C_CCTRX_UPDATE(R_PREPARER_NOTIFIED.CARD_PROGRAM_ID,R_PREPARER_NOTIFIED.INACT_EMPLOYEE_ID,L_CC_BILLED_START_DATE,L_CC_BILLED_END_DATE);
FETCH C_CCTRX_UPDATE
INTO R_CCTRX_UPDATE;
EXIT WHEN C_CCTRX_UPDATE%NOTFOUND;
UPDATE
AP_CREDIT_CARD_TRXNS
SET
INACTIVE_EMP_WF_ITEM_KEY = L_WF_ITEM_KEY
WHERE TRX_ID = R_CCTRX_UPDATE.TRX_ID;
CLOSE C_CCTRX_UPDATE;