[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ACCT.CATEGORY
FROM AP_CREDIT_CARD_TRXNS ACCT,
AP_EXPENSE_REPORT_LINES AERL
WHERE AERL.REPORT_HEADER_ID = P_ReportHeaderID
AND ACCT.TRX_ID = AERL.CREDIT_CARD_TRX_ID;
Select trx_id,
transaction_date,
folio_type, --shuh
merchant_name1,
merchant_city,
merchant_province_state,
billed_amount,
posted_currency_code,
transaction_amount,
cc.card_id,
nvl(cc.category, c_business)
FROM ap_credit_card_trxns cc,
ap_cards card
WHERE cc.validate_code = 'Y'
AND cc.payment_flag <> 'Y'
AND cc.billed_amount is not null
AND nvl(cc.expensed_amount,0) = 0
AND nvl(cc.category,'BUSINESS') <> 'DEACTIVATED'
AND cc.billed_currency_code = p_reimb_curr_code
AND cc.card_id = card.card_id
AND cc.card_program_id = card.card_program_id
AND card.employee_id = p_user_id
AND card.card_program_id = p_card_prog_id
AND card.card_id = p_card_id
AND cc.payment_due_from_code = p_paymentDueFrom
ORDER BY cc.transaction_date;
SELECT DISTINCT
cp.card_program_id,
cp.card_program_name,
card.card_id,
trxn.payment_due_from_code
FROM ap_card_programs cp,
ap_cards card,
ap_credit_card_trxns trxn
WHERE card.employee_id = p_user_id
AND card.card_program_id = cp.card_program_id
AND cp.card_type_lookup_code = p_card_type
AND trxn.CARD_PROGRAM_ID = card.CARD_PROGRAM_ID
AND trxn.CARD_ID = card.CARD_ID
UNION
SELECT cp.card_program_id,
cp.card_program_name,
card.card_id,
cp.payment_due_from_code
FROM ap_card_programs cp,
ap_cards card
WHERE card.employee_id = p_user_id
AND card.card_program_id = cp.card_program_id
AND cp.card_type_lookup_code = p_card_type;
SELECT DISTINCT
TRX_ID,
TRANSACTION_DATE, --3028505
BILLED_AMOUNT,
cc.CARD_ID THE_CARD_ID,
emp.full_name,
emp.person_id
FROM AP_CREDIT_CARD_TRXNS cc,
AP_CARDS card,
per_people_x emp
WHERE cc.CARD_PROGRAM_ID = p_card_prog_id
AND cc.VALIDATE_CODE = 'Y'
AND cc.payment_flag <> 'Y'
AND cc.COMPANY_PREPAID_INVOICE_ID IS NULL
AND cc.BILLED_AMOUNT IS NOT NULL
AND cc.CARD_ID = card.CARD_ID
AND (nvl(cc.billed_date, cc.posted_date) BETWEEN
nvl(p_start_date, nvl(cc.billed_date, cc.posted_date) - 1) AND
nvl(p_end_date, nvl(cc.billed_date, cc.posted_date) + 1)
)
AND cc.payment_due_from_code='COMPANY'
AND card.employee_id = emp.person_id
AND nvl(cc.category,'BUSINESS') <> 'DEACTIVATED'
ORDER BY
cc.transaction_date; --3028505
SELECT transaction_date, merchant_name1, billed_amount, billed_currency_code
FROM
ap_credit_card_trxns cct,
ap_cards_all ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount,0) = 0
and nvl(cct.category,c_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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and cct.billed_amount > p_minimumAmount
and ac.employee_id = p_employeeId
order by cct.transaction_date;
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
erh.invoice_num,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id), --2615448
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and cct.expensed_amount <> 0
and nvl(cct.category,c_business) NOT IN
( 'DISPUTED', 'PERSONAL' , 'MATCHED' ,'CREDIT','DEACTIVATED') -- 3234232 , --3307864
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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
and erh.report_header_id = cct.report_header_id --3130923
and ac.employee_id = erh.employee_id --3130923
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id) in
('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED') --2615448
and ac.employee_id = p_employeeId
and rownum < 41
UNION ALL
SELECT transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
null,
'UNUSED',
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount,0) = 0
and nvl(cct.category,c_business) NOT IN ('DISPUTED','MATCHED','CREDIT','DEACTIVATED') --Bug 3307864
and cct.report_header_id is NULL
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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
and ac.employee_id = p_employeeId
and rownum < 41
and p_chargeType = 'UNUSED'
UNION ALL
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
erh.invoice_num,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id), --2615448
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and cct.expensed_amount <> 0
and nvl(cct.category,c_business) = 'PERSONAL'
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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
and erh.report_header_id = cct.report_header_id
and ac.employee_id = erh.employee_id --3130923
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id) in
('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED') --2615448
and ac.employee_id = p_employeeId
and rownum < 41;
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
erh.invoice_num,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id), --2615448
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and cct.expensed_amount <> 0
and nvl(cct.category,c_business) NOT IN
('DISPUTED','CREDIT', 'MATCHED','PERSONAL','DEACTIVATED') --Bug 3307864
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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
and erh.report_header_id = cct.report_header_id --3130923
and ac.employee_id = erh.employee_id --3130923
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
erh.report_header_id) in
('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED') --2615448
and ac.employee_id = p_employeeId
UNION ALL
SELECT transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
null,
'UNUSED',
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount,0) = 0
and nvl(cct.category,c_business) NOT IN ( 'DISPUTED','CREDIT','MATCHED','DEACTIVATED') --Bug 3307864
and cct.report_header_id is NULL
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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
and ac.employee_id = p_employeeId
and p_chargeType = 'UNUSED'
UNION ALL
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
erh.invoice_num,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id), --2615448
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and cct.expensed_amount <> 0
and nvl(cct.category,c_business) = 'PERSONAL'
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_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
and erh.report_header_id = cct.report_header_id
and ac.employee_id = erh.employee_id --3130923
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
erh.report_header_id) in
('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED') --2615448
and ac.employee_id = p_employeeId;
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
cct.posted_currency_code, --3339380
erh.invoice_num,
NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id),'UNUSED'), --2615505
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.posted_date, --Notification Esc
cct.transaction_amount,--Notification Esc
AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state), --Notification Esc
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category,c_business) NOT IN ( 'DISPUTED','PERSONAL' ,'MATCHED','CREDIT','DEACTIVATED')
and cct.expensed_amount <> 0
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
and erh.report_header_id = cct.report_header_id
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
erh.report_header_id)
in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN')
and ac.employee_id = p_employeeId
and rownum < 41
--order by cct.transaction_date;
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
cct.posted_currency_code, --3339380
erh.invoice_num,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id),
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.posted_date, --Notification Esc
cct.transaction_amount,--Notification Esc
AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state), --Notification Esc
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category,c_business) = 'PERSONAL'
and cct.expensed_amount <> 0
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
and erh.report_header_id = cct.report_header_id
and NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
erh.report_header_id),'UNUSED')
in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN')
and ac.employee_id = p_employeeId
and rownum < 41
UNION ALL
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
cct.posted_currency_code, --3339380
null,
'UNUSED',
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.posted_date, --Notification Esc
cct.transaction_amount,--Notification Esc
AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state), --Notification Esc
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount,0) = 0
and nvl(cct.category,'BUSINESS') <> 'DEACTIVATED'
and cct.report_header_id is null
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
and ac.employee_id = p_employeeId
and rownum < 41;
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
erh.invoice_num,
NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id),'UNUSED'), --2615505
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category,c_business) NOT IN
('DISPUTED' , 'CREDIT' , 'MATCHED','PERSONAL','DEACTIVATED') --Bug 3307864
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
and erh.report_header_id = cct.report_header_id
and cct.expensed_amount <> 0
and erh.report_header_id = cct.report_header_id
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
erh.report_header_id)
in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN')
and ac.employee_id = p_employeeId
UNION ALL
SELECT DISTINCT transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
erh.invoice_num,
AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
(erh.source,erh.workflow_approved_flag,
erh.report_header_id),
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.trx_id
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category,c_business) = 'PERSONAL'
and cct.expensed_amount <> 0
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
and erh.report_header_id = cct.report_header_id
and NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
erh.report_header_id),'UNUSED')
in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN')
and ac.employee_id = p_employeeId
UNION ALL
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
null,
'UNUSED',
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.trx_id -- Bug 3241358
FROM
ap_credit_card_trxns cct,
ap_cards ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount,0) = 0
and nvl(cct.category,'BUSINESS') <> 'DEACTIVATED'
and cct.report_header_id is null
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
and ac.employee_id = p_employeeId;
SELECT card_program_currency_code
INTO p_curr_code
FROM ap_card_programs
WHERE card_program_id = p_card_prog_id;
SELECT category
INTO p_category
FROM ap_credit_card_trxns
WHERE trx_id = p_trx_id;
SELECT company_prepaid_invoice_id
INTO p_prepaid_invoice_id
FROM ap_credit_card_trxns
WHERE trx_id = p_trx_id;
select expensed_amount
into p_amt
from ap_credit_card_trxns
where trx_id = p_id;
SELECT VENDOR_ID,
VENDOR_SITE_ID,
CARD_PROGRAM_CURRENCY_CODE
INTO p_vendor_id,
p_vendor_site_id,
p_invoice_curr_code
FROM AP_CARD_PROGRAMS
WHERE CARD_PROGRAM_ID = p_card_prog_id;
SELECT distinct acp.vendor_id,
acp.vendor_site_id
INTO p_vendor_id,
p_vendor_site_id
FROM ap_credit_card_trxns cc,
ap_card_programs acp
WHERE cc.report_header_id = p_report_header_id
AND cc.card_program_id = acp.card_program_id;
select card_program_name
into p_cardProgramName
from ap_card_programs
where card_program_id = p_cardProgramID;
select card_program_id
into p_cardProgramID
from ap_card_programs
where card_program_name = p_cardProgramName;
select 'Y'
into p_companyHasCardProgram
from ap_card_programs
where card_type_lookup_code = 'TRAVEL';
SELECT 'Y'
INTO p_userHasCreditCard
FROM AP_CARD_PROGRAMS_all CP ,
AP_CARDS_all CARD ,
AP_LOOKUP_CODES ALC,
( SELECT CARD_PROGRAM_ID,CARD_ID, PAYMENT_DUE_FROM_CODE
FROM AP_CREDIT_CARD_TRXNS_all
WHERE VALIDATE_CODE = 'Y'
AND PAYMENT_FLAG <> 'Y'
AND BILLED_AMOUNT IS NOT NULL
AND NVL ( CATEGORY , 'BUSINESS' ) <> 'DEACTIVATED' ) TRXN
WHERE CARD.EMPLOYEE_ID = p_userId
AND CARD.CARD_PROGRAM_ID = CP.CARD_PROGRAM_ID
AND CP.CARD_TYPE_LOOKUP_CODE = 'TRAVEL'
AND ALC.LOOKUP_TYPE = 'PAYMENT_DUE_FROM'
AND ( ALC.LOOKUP_CODE = CP.PAYMENT_DUE_FROM_CODE
OR ALC.LOOKUP_CODE = TRXN.PAYMENT_DUE_FROM_CODE)
AND TRUNC ( SYSDATE ) BETWEEN TRUNC ( NVL (ALC.START_DATE_ACTIVE , SYSDATE ) )
AND TRUNC ( NVL ( ALC.INACTIVE_DATE , SYSDATE ) )
AND TRXN.CARD_PROGRAM_ID(+) = CARD.CARD_PROGRAM_ID
AND TRXN.CARD_ID(+) = CARD.CARD_ID
AND ROWNUM = 1;
UPDATE ap_credit_card_trxns
SET report_header_id = p_new_report_id
WHERE report_header_id = p_report_header_id;
UPDATE ap_credit_card_trxns
SET report_header_id = p_new_expense_report_id
WHERE trx_id IN (SELECT credit_card_trx_id
FROM ap_expense_report_lines
WHERE report_header_id = p_new_expense_report_id
AND nvl(policy_shortpay_flag,'N') = 'Y');
UPDATE ap_credit_card_trxns
SET report_header_id = p_new_expense_report_id
WHERE trx_id IN (SELECT credit_card_trx_id
FROM ap_expense_report_lines
WHERE report_header_id = p_new_expense_report_id
AND receipt_required_flag = 'Y'
AND nvl(receipt_verified_flag,'N') = 'N'
AND nvl(policy_shortpay_flag, 'N') = 'N');
FUNCTION UpdateExpensedAmount(
p_trxn_id IN ccTrxn_trxID,
p_report_id IN ccTrxn_headerID,
p_expensed_amount IN ccTrxn_expensedAmt
) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
BEGIN
UPDATE ap_credit_card_trxns
SET expensed_amount = p_expensed_amount,
report_header_id = p_report_id,
category = null
WHERE trx_id = p_trxn_id;
AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateExpensedAmount' );
END UpdateExpensedAmount;
UPDATE ap_credit_card_trxns cc
SET expensed_amount = 0,
report_header_id = null,
category = null
WHERE report_header_id = p_report_header_id;
UPDATE ap_credit_card_trxns
SET expense_status = p_status
where report_header_id = p_report_header_id;
UPDATE ap_credit_card_trxns
SET expensed_amount = 0,
report_header_id = NULL
WHERE report_header_id IN (SELECT report_header_id
FROM ap_expense_report_headers
WHERE report_header_id = p_report_header_id
AND workflow_approved_flag in
(AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_REJECTED,
AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_RETURNED,
--ER 1552747 - withdraw expense report
AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_WITHDRAW));
update ap_credit_card_trxns
set expensed_amount = 0,
report_header_id = null
where (report_header_id = p_reportID)
and (category = c_personal);
UPDATE ap_credit_card_trxns cc
SET expensed_amount = 0,
report_header_id = null
WHERE cc.report_header_id IN (SELECT report_header_id erh_headerID
FROM ap_expense_report_headers
WHERE report_header_id = p_report_header_id
AND workflow_approved_flag in
(AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_REJECTED,
AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_RETURNED,
--ER 1552747 - withdraw expense report
AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_WITHDRAW))
AND category = c_personal;
UPDATE ap_credit_card_trxns_all
SET company_prepaid_invoice_id = p_invoice_id
WHERE trx_id = p_card_trxn_id;
AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateInvoiceId' );
SELECT trx_id,
transaction_date,
folio_type, --shuh
merchant_name1,
merchant_city,
merchant_province_state,
billed_amount,
posted_currency_code,
transaction_amount,
cc.card_id,
nvl(cc.category, c_business),
cc.card_program_id
INTO p_trxn_info_rec.trxn_id, p_trxn_info_rec.trxn_date,
p_trxn_info_rec.folio_type, p_trxn_info_rec.merchant_name,
p_trxn_info_rec.merchant_city, p_trxn_info_rec.merchant_prov,
p_trxn_info_rec.billed_amount, p_trxn_info_rec.posted_curr_code,
p_trxn_info_rec.trxn_amount, p_trxn_info_rec.card_id,
p_trxn_info_rec.category, p_trxn_info_rec.card_prog_id
FROM ap_credit_card_trxns cc
WHERE trx_id = p_trxn_id;
FUNCTION UpdateCCardCategory(
p_trxn_id IN ccTrxn_trxID,
p_category IN ccTrxn_category
) RETURN BOOLEAN IS
------------------------------------------------------------------------------
BEGIN
UPDATE ap_credit_card_trxns
SET category = p_category
WHERE trx_id = p_trxn_id;
AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateCCardCategory' );
END UpdateCCardCategory;
SELECT expensed_amount
INTO p_exp_amount
FROM ap_credit_card_trxns
WHERE trx_id = p_trxn_id;
select count(distinct payment_due_from_code)
into paymentMethodCount
from ap_credit_card_trxns
where card_program_id = p_cardProgramID
and card_id = p_card_id;
SELECT DISTINCT payment_due_from_code
INTO p_paymentDueCode
FROM ap_credit_card_trxns trx
WHERE trx.trx_id = p_trxn_id;
select prevent_cash_cc_age_limit
into l_prevent_cash_cc_age_limit
from ap_expense_params;
select count(1)
into p_num_old
from ap_card_programs cp,
ap_cards card,
ap_credit_card_trxns trxns
where card.employee_id = p_employee_id
and card.card_program_id = cp.card_program_id
and cp.card_type_lookup_code = 'TRAVEL'
and trxns.card_program_id = card.card_program_id
and trxns.card_id = card.card_id
and trxns.validate_code = 'Y'
and trxns.payment_flag <> 'Y'
and trxns.billed_amount is not null
and trxns.report_header_id is null
and (nvl(trxns.category, 'BUSINESS') not in ('DISPUTED', 'CREDIT', 'MATCHED','DEACTIVATED'))
and sysdate - trxns.posted_date > l_prevent_cash_cc_age_limit;
select count(1)
into p_num_disputed
from ap_card_programs cp,
ap_cards card,
ap_credit_card_trxns trxns
where card.employee_id = p_employee_id
and card.card_program_id = cp.card_program_id
and cp.card_type_lookup_code = 'TRAVEL'
and trxns.card_program_id = card.card_program_id
and trxns.card_id = card.card_id
and trxns.validate_code = 'Y'
and trxns.payment_flag <> 'Y'
and trxns.billed_amount is not null
and trxns.report_header_id is null
and nvl(trxns.category,c_business) = 'DISPUTED';
select count(1)
into p_num_credits
from ap_card_programs cp,
ap_cards card,
ap_credit_card_trxns trxns
where card.employee_id = p_employee_id
and card.card_program_id = cp.card_program_id
and cp.card_type_lookup_code = 'TRAVEL'
and trxns.card_program_id = card.card_program_id
and trxns.card_id = card.card_id
and trxns.validate_code = 'Y'
and trxns.payment_flag <> 'Y'
and trxns.billed_amount < 0
and trxns.report_header_id is null
and nvl(trxns.category, 'BUSINESS') not in ('DISPUTED', 'CREDIT', 'MATCHED','DEACTIVATED');
SELECT h.employee_id
FROM per_employees_x h, ak_web_user_sec_attr_values a
WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
AND a.web_user_id = p_user_id
AND h.employee_id = a.number_value
AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)='Y'
UNION ALL
SELECT h.person_id employee_id
FROM per_cont_workers_current_x h, ak_web_user_sec_attr_values a
WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
AND a.web_user_id = p_user_id
AND h.person_id = a.number_value;
SELECT distinct transaction_date,
merchant_name1,
billed_amount,
billed_currency_code,
cct.posted_currency_code, --3339380
nvl(cct.billed_date, cct.posted_date) billed_date,
cct.posted_date, --Notification Esc
cct.transaction_amount,--Notification Esc
AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state) --Notification Esc
from
ap_credit_card_trxns cct,
ap_cards_all ac
where
cct.card_program_id = p_cardProgramId
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 trunc(sysdate) - (cct.posted_date+nvl(p_grace_days,0)) between p_min_bucket and p_max_bucket
and ac.employee_id = p_employeeId
and rownum < 41;
SELECT sum(amount)
INTO p_totalAmount
FROM
(
SELECT DISTINCT cct.trx_id, cct.billed_amount amount
FROM
ap_credit_card_trxns_all cct,
ap_cards_all ac,
ap_expense_report_headers_all erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(
erh.source,erh.workflow_approved_flag,
erh.report_header_id)
in
('EMPAPPR', 'RESOLUTN','RETURNED',
'REJECTED','SAVED','WITHDRAWN','UNUSED')
and erh.report_header_id = cct.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 ac.employee_id = p_employeeId
UNION ALL
SELECT DISTINCT cct.trx_id, cct.billed_amount amount
FROM
ap_credit_card_trxns_all cct,
ap_cards_all ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount , 0) =0
and nvl(cct.category,'BUSINESS') <> 'DEACTIVATED'
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and ac.employee_id = p_employeeId
);
SELECT count(1), sum(amount)
INTO p_total_outstanding,
p_total_amt_outstanding
FROM
( SELECT DISTINCT trx_id, cct.billed_amount amount
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category,c_business) NOT IN
('DISPUTED','CREDIT','MATCHED','DEACTIVATED')--Bug 3307864
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
and erh.report_header_id = cct.report_header_id
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,
erh.workflow_approved_flag,
erh.report_header_id) --2615505
in ('SAVED','UNUSED','REJECTED',
'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN')
and ac.employee_id = p_employeeId
UNION ALL
SELECT DISTINCT trx_id, cct.billed_amount amount
FROM
ap_credit_card_trxns cct,
ap_cards ac
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount , 0) = 0
and nvl(cct.category,c_business) NOT IN
('DISPUTED','CREDIT','MATCHED','DEACTIVATED')--Bug 3307864
and cct.report_header_id is null
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
and ac.employee_id = p_employeeId
);
SELECT count(1), sum(amount)
INTO p_total_dispute,
p_total_amt_dispute
FROM
( SELECT DISTINCT trx_id, cct.billed_amount amount
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category,c_business) = 'DISPUTED'
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and trunc(sysdate) - (cct.posted_date+nvl(p_grace_days,0))
between p_min_bucket and p_max_bucket
and erh.report_header_id(+) = cct.report_header_id
and NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,
erh.workflow_approved_flag,
erh.report_header_id),
'UNUSED') --2615505
in ('SAVED','UNUSED','REJECTED',
'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN')
and ac.employee_id = p_employeeId);
select nvl(min(posted_date), sysdate)
into l_oldest_posted_date
from ap_credit_card_trxns cct
where cct.card_program_id = p_cardProgramId
and cct.card_id = p_cardId
and cct.payment_due_from_code = p_paymentDueFromCode
and cct.billed_currency_code = p_reimb_curr_code
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category, c_business) not in (c_disputed, c_credit, c_matched, c_deactivated)
and (cct.report_header_id is null or cct.report_header_id = p_report_header_id)
and cct.billed_amount is not null;