[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT credit_card_trx_id, amount
FROM AP_EXPENSE_REPORT_LINES
WHERE (REPORT_HEADER_ID = p_reportId)
AND (credit_card_trx_id is not null);
SELECT TO_CHAR(WEB_PARAMETER_ID), -- xtype
TO_CHAR(AMOUNT), -- amount
RECEIPT_MISSING_FLAG, -- receiptmissing
JUSTIFICATION, -- justification
EXPENSE_GROUP, -- group
-- chiho:1283146:
TO_CHAR(START_EXPENSE_DATE, l_date_format), -- xdate1
TO_CHAR(END_EXPENSE_DATE, l_date_format), -- xdate2
RECEIPT_CURRENCY_CODE, -- currency
TO_CHAR(RECEIPT_CONVERSION_RATE), -- rate array
TO_CHAR(DAILY_AMOUNT), -- damount
TO_CHAR(RECEIPT_CURRENCY_AMOUNT), -- recamount
AERL.MERCHANT_NAME,
AERL.MERCHANT_DOCUMENT_NUMBER,
AERL.MERCHANT_REFERENCE,
AERL.MERCHANT_TAX_REG_NUMBER,
AERL.MERCHANT_TAXPAYER_ID,
AERL.COUNTRY_OF_SUPPLY,
AERL.TAX_CODE_ID,
AERL.TAX_CODE_OVERRIDE_FLAG,
AERL.AMOUNT_INCLUDES_TAX_FLAG,
AERL.CREDIT_CARD_TRX_ID,
AERL.ATTRIBUTE1,
AERL.ATTRIBUTE2,
AERL.ATTRIBUTE3,
AERL.ATTRIBUTE4,
AERL.ATTRIBUTE5,
AERL.ATTRIBUTE6,
AERL.ATTRIBUTE7,
AERL.ATTRIBUTE8,
AERL.ATTRIBUTE9,
AERL.ATTRIBUTE10,
AERL.ATTRIBUTE11,
AERL.ATTRIBUTE12,
AERL.ATTRIBUTE13,
AERL.ATTRIBUTE14,
AERL.ATTRIBUTE15,
AMOUNT_INCLUDES_TAX_FLAG,
VAT_CODE,
TO_CHAR(AERL.PROJECT_ID),
AERL.PROJECT_NUMBER,
TO_CHAR(AERL.TASK_ID),
AERL.TASK_NUMBER,
EXPENDITURE_TYPE,
AERL.DISTRIBUTION_LINE_NUMBER
FROM AP_EXPENSE_REPORT_LINES AERL
WHERE REPORT_HEADER_ID = p_reportId
ORDER BY AERL.DISTRIBUTION_LINE_NUMBER;
SELECT XL.receipt_missing_flag,
-- Called from GenerateExpClobLines for rendering old notifications for
-- expenses created prior to 11.5.10, will not be used for reports submitted
-- in R12 hence decision was made not to tune these old queries.
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
nvl(XL.justification, '&' || 'nbsp'),
nvl(XP.web_friendly_prompt, XP.prompt),
PAP.segment1, --PAP.project_number,
nvl(PAT.task_number, '&' || 'nbsp'),
XL.credit_card_trx_id,
XL.distribution_line_number dist_num,
nvl(GMS.award_number, '&' || 'nbsp'),
av.displayed_field,
XL.merchant_name
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_lookup_codes LC,
PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
PA_TASKS PAT, -- AP_WEB_PA_PROJECTS_TASKS_V PAT, -- bug 1652647
GMS_AWARDS GMS,
AP_POL_VIOLATIONS_V AV
WHERE XL.report_header_id = p_report_header_id
AND XL.project_id is not null
AND XL.task_id is not null
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id = PAP.project_id
AND XL.project_id = PAT.project_id
AND XL.task_id = PAT.task_id
AND XL.award_id = GMS.award_id(+)
AND XL.report_header_id = AV.report_header_id(+)
AND XL.distribution_line_number = AV.distribution_line_number(+)
AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
UNION ALL
SELECT XL.receipt_missing_flag,
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
XL.justification,
nvl(XP.web_friendly_prompt, XP.prompt),
NULL,
NULL,
XL.credit_card_trx_id,
XL.distribution_line_number dist_num,
NULL,
av.displayed_field,
XL.merchant_name
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_lookup_codes LC,
AP_POL_VIOLATIONS_V AV
WHERE XL.report_header_id = p_report_header_id
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id is null
AND XL.task_id is null
AND XL.award_id is null
AND XL.report_header_id = AV.report_header_id(+)
AND XL.distribution_line_number = AV.distribution_line_number(+)
AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
ORDER BY dist_num;
SELECT XL.receipt_missing_flag,
-- Called from GenerateExpClobLines for rendering old notifications for
-- expenses created prior to 11.5.10, will not be used for reports submitted
-- in R12 hence decision was made not to tune these old queries.
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
nvl(XL.justification, '&' || 'nbsp'),
nvl(XP.web_friendly_prompt, XP.prompt),
PAP.segment1, --PAP.project_number,
nvl(PAT.task_number, '&' || 'nbsp'),
XL.credit_card_trx_id,
XL.distribution_line_number,
nvl(GMS.award_number, '&' || 'nbsp'),
av.displayed_field,
XL.merchant_name,
nvl(XL.flex_concatenated, XH.flex_concatenated),
XL.mileage_rate_adjusted_flag
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_expense_report_headers XH,
ap_credit_card_trxns CC,
ap_lookup_codes LC,
PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
PA_TASKS PAT, -- AP_WEB_PA_PROJECTS_TASKS_V PAT -- bug 1652647
GMS_AWARDS GMS,
AP_POL_VIOLATIONS_V AV
WHERE XL.report_header_id = p_report_header_id
AND XL.report_header_id = XH.report_header_id
AND XL.project_id is not null
AND XL.task_id is not null
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id = PAP.project_id
AND XL.project_id = PAT.project_id
AND XL.task_id = PAT.task_id
AND XL.credit_card_trx_id is not null
AND CC.trx_id = XL.credit_card_trx_id
AND (CC.category is null OR CC.category NOT IN ('PERSONAL','DEACTIVATED'))--not a personal expense
AND XL.web_parameter_id <> l_personalParameterId -- not a personal expense
AND XL.award_id = GMS.award_id(+)
AND XL.report_header_id = AV.report_header_id(+)
AND XL.distribution_line_number = AV.distribution_line_number(+)
AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
UNION ALL
SELECT XL.receipt_missing_flag,
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
XL.justification,
nvl(XP.web_friendly_prompt, XP.prompt),
NULL,
NULL,
XL.credit_card_trx_id,
XL.distribution_line_number,
NULL,
av.displayed_field,
XL.merchant_name,
nvl(XL.flex_concatenated, XH.flex_concatenated),
XL.mileage_rate_adjusted_flag
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_expense_report_headers XH,
ap_credit_card_trxns CC,
ap_lookup_codes LC,
AP_POL_VIOLATIONS_V AV
WHERE XL.report_header_id = p_report_header_id
AND XL.report_header_id = XH.report_header_id
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id is null
AND XL.task_id is null
AND XL.credit_card_trx_id is not null
AND CC.trx_id = XL.credit_card_trx_id
AND (CC.category is null OR CC.category NOT IN ('PERSONAL','DEACTIVATED'))--not a personal expense
AND XL.web_parameter_id <> l_personalParameterId -- not a personal expense
AND XL.award_id is NULL
AND XL.report_header_id = AV.report_header_id(+)
AND XL.distribution_line_number = AV.distribution_line_number(+)
AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
ORDER BY 14;
SELECT XL.receipt_missing_flag,
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
nvl(XL.justification, '&' || 'nbsp'),
nvl(XP.web_friendly_prompt, XP.prompt),
PAP.segment1, --PAP.project_number,
nvl(PAT.task_number, '&' || 'nbsp'),
XL.credit_card_trx_id,
XL.distribution_line_number,
nvl(GMS.award_number, '&' || 'nbsp'),
av.displayed_field,
XL.merchant_name,
nvl(XL.flex_concatenated, XH.flex_concatenated),
XL.mileage_rate_adjusted_flag
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_expense_report_headers XH,
ap_lookup_codes LC,
PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
PA_TASKS PAT, -- AP_WEB_PA_PROJECTS_TASKS_V PAT -- bug 1652647
GMS_AWARDS GMS,
AP_POL_VIOLATIONS_V AV
WHERE XL.report_header_id = p_report_header_id
AND XL.report_header_id = XH.report_header_id
AND XL.project_id is not null
AND XL.task_id is not null
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id = PAP.project_id
AND XL.project_id = PAT.project_id
AND XL.task_id = PAT.task_id
AND XL.credit_card_trx_id is null
AND XL.award_id = GMS.award_id(+)
AND XL.report_header_id = AV.report_header_id(+)
AND XL.distribution_line_number = AV.distribution_line_number(+)
AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
UNION ALL
SELECT XL.receipt_missing_flag,
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
XL.justification,
nvl(XP.web_friendly_prompt, XP.prompt),
NULL,
NULL,
XL.credit_card_trx_id,
XL.distribution_line_number,
NULL,
av.displayed_field,
XL.merchant_name,
nvl(XL.flex_concatenated, XH.flex_concatenated),
XL.mileage_rate_adjusted_flag
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_expense_report_headers XH,
ap_lookup_codes LC,
AP_POL_VIOLATIONS_V av
WHERE XL.report_header_id = p_report_header_id
AND XL.report_header_id = XH.report_header_id
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id is null
AND XL.task_id is null
AND XL.credit_card_trx_id is null
AND XL.award_id is null
AND XL.report_header_id = AV.report_header_id(+)
AND XL.distribution_line_number = AV.distribution_line_number(+)
AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
ORDER BY 14;
SELECT XL.receipt_missing_flag,
-- Called from GenerateExpClobLines for rendering old notifications for
-- expenses created prior to 11.5.10, will not be used for reports submitted
-- in R12 hence decision was made not to tune these old queries.
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
nvl(XL.justification, '&' || 'nbsp'),
nvl(XP.web_friendly_prompt, XP.prompt),
PAP.segment1, --PAP.project_number,
nvl(PAT.task_number, '&' || 'nbsp'),
XL.credit_card_trx_id,
XL.distribution_line_number,
XL.MERCHANT_NAME --Bug 2942773
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_credit_card_trxns CC,
ap_lookup_codes LC,
PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
PA_TASKS PAT -- AP_WEB_PA_PROJECTS_TASKS_V PAT -- bug 1652647
WHERE XL.report_header_id = p_report_header_id
AND XL.project_id is not null
AND XL.task_id is not null
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id = PAP.project_id
AND XL.project_id = PAT.project_id
AND XL.task_id = PAT.task_id
AND XL.credit_card_trx_id is not null
AND CC.trx_id = XL.credit_card_trx_id
AND (CC.category = 'PERSONAL' OR XL.web_parameter_id = l_personalParameterId)
UNION ALL
SELECT XL.receipt_missing_flag,
to_char(XL.start_expense_date),
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
LPAD(to_char(XL.daily_amount),9),
XL.receipt_currency_code,
LPAD(to_char(XL.receipt_conversion_rate),5),
LPAD(to_char(XL.receipt_currency_amount),10),
XL.amount,
XL.justification,
nvl(XP.web_friendly_prompt, XP.prompt),
NULL,
NULL,
XL.credit_card_trx_id,
XL.distribution_line_number,
XL.MERCHANT_NAME --Bug 2942773
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_credit_card_trxns CC,
ap_lookup_codes LC
WHERE XL.report_header_id = p_report_header_id
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id is null
AND XL.task_id is null
AND XL.credit_card_trx_id is not null
AND CC.trx_id = XL.credit_card_trx_id
AND (CC.category = 'PERSONAL' OR XL.web_parameter_id = l_personalParameterId)
ORDER BY distribution_line_number;
SELECT 'Y'
INTO l_temp
FROM ap_expense_report_lines aerl
WHERE aerl.report_header_id = p_report_header_id
AND aerl.adjustment_reason_code is not null
AND rownum = 1;
* Note the select statement needs to have the same number and type of columns in order for the reference cursor
* to work. Currently the queries match except for where statement. Not using dynamic SQL here because there are
* a lot of delimiters in the query and also the columns might be calculated differently for the cases in the
* future.
*/
/* jrautiai ADJ Fix Start */
IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
l_personalParameterId := fnd_api.G_MISS_NUM;
SELECT aerl.report_header_id,
aerl.start_expense_date,
aerl.amount,
aerl.submitted_amount,
(aerl.submitted_amount - aerl.amount) adjusted_amount,
aerl.web_parameter_id,
AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_disp,
aerl.justification,
aerl.adjustment_reason_code,
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_code_disp,
AP_WEB_POLICY_UTILS.get_lookup_description('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_description,
aerl.adjustment_reason,
DECODE(aerl.CREDIT_CARD_TRX_ID,
null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) credit_card_expense_disp,
DECODE(aerl.itemization_parent_id,
null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) itemized_expense_disp
FROM ap_expense_report_lines aerl
WHERE aerl.report_header_id in (select p_report_header_id from dual
union
select aerh1.report_header_id
from ap_expense_report_headers_all aerh1
where aerh1.SHORTPAY_PARENT_ID = p_report_header_id)
AND (itemization_parent_id is null OR itemization_parent_id = -1)
AND aerl.web_parameter_id <> l_roundingParameterId
AND aerl.amount <> NVL(aerl.submitted_amount,aerl.amount)
ORDER BY aerl.distribution_line_number;
SELECT aerl.report_header_id,
aerl.start_expense_date,
aerl.amount,
aerl.submitted_amount,
(aerl.submitted_amount - aerl.amount) adjusted_amount,
aerl.web_parameter_id,
AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_disp,
aerl.justification,
aerl.adjustment_reason_code,
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_code_disp,
AP_WEB_POLICY_UTILS.get_lookup_description('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_description,
aerl.adjustment_reason,
DECODE(aerl.CREDIT_CARD_TRX_ID,
null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) credit_card_expense_disp,
DECODE(aerl.itemization_parent_id,
null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) itemized_expense_disp
FROM ap_expense_report_lines aerl
WHERE aerl.report_header_id = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id = -1)
AND aerl.adjustment_reason_code is not null
AND aerl.web_parameter_id <> l_roundingParameterId
ORDER BY aerl.distribution_line_number;
SELECT aerl.report_header_id,
aerl.start_expense_date,
aerl.amount,
aerl.submitted_amount,
(aerl.submitted_amount - aerl.amount) adjusted_amount,
aerl.web_parameter_id,
AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_disp,
aerl.justification,
aerl.adjustment_reason_code,
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_code_disp,
AP_WEB_POLICY_UTILS.get_lookup_description('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_description,
aerl.adjustment_reason,
DECODE(aerl.CREDIT_CARD_TRX_ID,
null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) credit_card_expense_disp,
DECODE(aerl.itemization_parent_id,
null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) itemized_expense_disp
FROM ap_expense_report_lines aerl
WHERE aerl.report_header_id = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id = -1)
AND aerl.web_parameter_id <> l_roundingParameterId
ORDER BY aerl.distribution_line_number;
that selected columns from the same tables.
*/
--------------------------------------------------------------------------------
FUNCTION GetExpDistAcctCursor(p_exp_report_id IN expLines_headerID,
p_cursor OUT NOCOPY XpenseLineAcctCursor)
RETURN BOOLEAN IS
--------------------------------------------------------------------------------
BEGIN
OPEN p_cursor FOR
SELECT XL.distribution_line_number,
XD.report_distribution_id,
XL.start_expense_date,
XD.amount,
nvl(XP.web_friendly_prompt, XP.prompt) expense_type,
XL.credit_card_trx_id,
XD.project_id,
XD.task_id,
XD.award_id,
XL.expenditure_item_date,
XL.expenditure_type,
XL.pa_quantity,
XD.expenditure_organization_id,
XL.web_parameter_id,
XL.adjustment_reason,
XP.flex_concactenated,
XL.category_code,
XL.attribute_category,
XL.attribute1,
XL.attribute2,
XL.attribute3,
XL.attribute4,
XL.attribute5,
XL.attribute6,
XL.attribute7,
XL.attribute8,
XL.attribute9,
XL.attribute10,
XL.attribute11,
XL.attribute12,
XL.attribute13,
XL.attribute14,
XL.attribute15,
XD.cost_center,
XL.AP_VALIDATION_ERROR,
XL.Report_Line_id
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_exp_report_dists XD
WHERE XL.report_header_id = p_exp_report_id
AND XL.web_parameter_id = XP.parameter_id
AND XD.report_line_id(+) = XL.report_line_id
AND (XL.itemization_parent_id is null OR XL.itemization_parent_id <> -1)
ORDER BY XL.distribution_line_number;
SELECT sum(DECODE(aerl.credit_card_trx_id,null,0,aerl.amount)),
sum(DECODE(aerl.credit_card_trx_id, null, aerl.amount,0))
INTO p_no_receipts_ccard_amt, p_no_receipts_emp_amt
FROM ap_expense_report_lines aerl,
ap_expense_report_headers aerh
WHERE aerl.report_header_id = p_report_header_id
AND aerh.report_header_id = aerl.report_header_id
AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
AND nvl(aerl.receipt_required_flag, 'N') = 'Y'
AND nvl(aerl.receipt_verified_flag, 'N') = 'N'
AND nvl(aerl.policy_shortpay_flag, 'N') = 'N';
SELECT nvl(sum(erl.amount),0)
INTO p_personal_total
FROM ap_expense_report_lines erl,
ap_expense_report_headers aerh,
ap_credit_card_trxns cct
WHERE erl.report_header_id = p_report_header_id
AND aerh.report_header_id = erl.report_header_id
AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
AND nvl(erl.receipt_required_flag, 'N') = 'Y'
AND nvl(erl.receipt_verified_flag, 'N') = 'N'
AND nvl(erl.policy_shortpay_flag, 'N') = 'N'
AND erl.credit_card_trx_id = cct.trx_id
AND cct.category = 'PERSONAL';
SELECT sum(DECODE(credit_card_trx_id, null,amount,0)), sum(DECODE(credit_card_trx_id, null,0,amount))
INTO p_policy_emp_amt, p_policy_ccard_amt
FROM ap_expense_report_lines
WHERE report_header_id = P_report_header_id
AND nvl(policy_shortpay_flag, 'N') = 'Y'
AND (itemization_parent_id is null OR itemization_parent_id <> -1);
SELECT sum(amount)
INTO p_sum_missing_receipts
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND amount >= 0
AND receipt_missing_flag = 'Y'
AND (itemization_parent_id is null OR itemization_parent_id <> -1);
select sum(daily_amount *
LPAD(to_char((nvl(end_expense_date,start_expense_date) -
start_expense_date)+1),4)) violation_total
into p_sum_violations
from ap_expense_report_lines_all
where report_header_id = p_report_header_id
and amount >= 0
and receipt_missing_flag <> 'Y'
and distribution_line_number in (
select distinct (distribution_line_number)
from ap_pol_violations
where report_header_id = p_report_header_id);
SELECT nvl(sum(amount),0)
INTO p_personal_total
FROM ap_expense_report_lines erl
WHERE erl.report_header_id = p_report_header_id
AND erl.web_parameter_id = l_personalParameterId
AND (itemization_parent_id is null OR itemization_parent_id <> -1);
SELECT sum(DECODE(credit_card_trx_id, null,amount,0)),
sum(DECODE(credit_card_trx_id, null,0,amount))
INTO p_emp_amt, p_ccard_amt
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id <> -1);
SELECT count(*)
INTO p_num_req_receipts
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND nvl(receipt_required_flag, 'N') = 'Y';
SELECT count(*)
INTO p_num_req_receipt_not_verified
FROM ap_expense_report_lines aerl,
ap_expense_report_headers aerh
WHERE aerl.report_header_id = p_report_header_id
AND aerh.report_header_id = aerl.report_header_id
AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
AND nvl(aerl.receipt_required_flag, 'N') = 'Y'
AND nvl(aerl.receipt_missing_flag, 'N') = 'N'
AND nvl(aerl.receipt_verified_flag, 'N') = 'N';
SELECT count(*)
INTO p_count
FROM ap_expense_report_lines aerl,
ap_expense_report_headers aerh
WHERE aerl.report_header_id = p_report_header_id
AND aerh.report_header_id = aerl.report_header_id
AND (nvl(aerl.policy_shortpay_flag,'N') = 'Y'
OR ( nvl(aerl.receipt_required_flag, 'N') = 'Y'
AND nvl(aerl.receipt_verified_flag, 'N') = 'N'
AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
)
);
SELECT count(*)
INTO p_num_req_receipts
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND nvl(justification_required_flag, 'V') = 'Y'
AND amount >= 0;
SELECT count(*)
INTO p_count
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id;
SELECT count(*)
INTO p_crd_card_count
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND credit_card_trx_id IS NOT NULL;
SELECT count(*)
INTO p_personal_count
FROM ap_expense_report_lines XL,
ap_credit_card_trxns CC
WHERE XL.report_header_id = p_report_header_id
AND XL.credit_card_trx_id is not null
AND CC.trx_id = XL.credit_card_trx_id
AND (CC.category = 'PERSONAL' OR XL.web_parameter_id = l_personalParameterId);
SELECT 'Y' INTO l_context_enabled
FROM fnd_descr_flex_contexts_vl
WHERE application_id = l_flexfield.application_id
AND descriptive_flexfield_name = l_flexfield.flexfield_name
AND enabled_flag = 'Y'
AND global_flag = 'N'
AND descriptive_flex_context_code = p_xpense_lines.item_description;
INSERT INTO AP_EXPENSE_REPORT_LINES
(report_header_id,
code_combination_id,
web_parameter_id,
set_of_books_id,
amount,
item_description,
line_type_lookup_code,
currency_code,
receipt_missing_flag,
receipt_required_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
daily_amount,
receipt_currency_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
distribution_line_number,
amount_includes_tax_flag,
tax_code_id,
vat_code, -- Bug 1303470
tax_code_override_flag,
merchant_name,
merchant_document_number,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
project_id,
project_number,
task_id,
task_number,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
project_accounting_context,
company_prepaid_invoice_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
report_line_id,
org_id)
SELECT
p_xpense_lines.new_report_header_id,
p_xpense_lines.code_combination_id,
p_expLines.parameter_id,
p_xpense_lines.set_of_books_id,
p_expLines.amount,
p_xpense_lines.item_description,
p_xpense_lines.line_type_lookup_code,
p_xpense_lines.reimbursement_currency_code,
p_expLines.receipt_missing_flag,
p_xpense_lines.require_receipt_flag,
AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_expLines.justification),
p_expLines.group_value,
p_xpense_lines.date1_temp,
p_xpense_lines.date2_temp,
p_expLines.currency_code,
p_xpense_lines.rate,
p_expLines.daily_amount,
p_expLines.receipt_amount,
sysdate,
icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
sysdate,
icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
i,
p_expLines.amount_includes_tax,
p_expLines.taxId,
p_expLines.tax_code, -- Bug 1303470
p_expLines.taxOverrideFlag,
p_expLines.merchant,
p_expLines.merchantDoc,
p_expLines.taxReference,
p_expLines.taxRegNumber,
p_expLines.taxPayerId,
p_expLines.supplyCountry,
p_expLines.cCardTrxnId,
to_number(p_expLines.project_id),
p_expLines.project_number,
to_number(p_expLines.task_id),
p_expLines.task_number,
DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',
p_xpense_lines.expenditure_organization_id,NULL),
p_expLines.expenditure_type,
DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',NVL(p_xpense_lines.date2_temp, p_xpense_lines.date1_temp),NULL),
p_xpense_lines.IsReceiptProjectEnabled,
p_xpense_lines.company_prepaid_invoice_id, --company prepaid invoice id
DECODE(l_context_enabled,'Y',p_xpense_lines.item_description,NULL),
P_AttributeCol(1),
P_AttributeCol(2),
P_AttributeCol(3),
P_AttributeCol(4),
P_AttributeCol(5),
P_AttributeCol(6),
P_AttributeCol(7),
P_AttributeCol(8),
P_AttributeCol(9),
P_AttributeCol(10),
P_AttributeCol(11),
P_AttributeCol(12),
P_AttributeCol(13),
P_AttributeCol(14),
P_AttributeCol(15),
ap_expense_report_lines_s.nextval,
mo_global.get_current_org_id()
FROM ap_expense_report_params
WHERE parameter_id = p_expLines.parameter_id;
INSERT INTO AP_EXPENSE_REPORT_LINES
(report_header_id,
code_combination_id,
web_parameter_id,
set_of_books_id,
amount,
item_description,
line_type_lookup_code,
currency_code,
receipt_missing_flag,
receipt_required_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
daily_amount,
receipt_currency_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
distribution_line_number,
amount_includes_tax_flag,
tax_code_id,
tax_code_override_flag,
merchant_name,
merchant_document_number,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
project_id,
project_number,
task_id,
task_number,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
project_accounting_context,
company_prepaid_invoice_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
report_line_id,
org_id )
VALUES (
p_xpense_lines.new_report_header_id,
p_xpense_lines.code_combination_id,
p_expLines.parameter_id,
p_xpense_lines.set_of_books_id,
p_expLines.amount,
p_xpense_lines.item_description,
p_xpense_lines.line_type_lookup_code,
p_xpense_lines.reimbursement_currency_code,
p_expLines.receipt_missing_flag,
p_xpense_lines.require_receipt_flag,
AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_expLines.justification),
p_expLines.group_value,
p_xpense_lines.date1_temp,
p_xpense_lines.date2_temp,
p_expLines.currency_code,
p_xpense_lines.rate,
p_expLines.daily_amount,
p_expLines.receipt_amount,
sysdate,
icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
sysdate,
icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
i,
p_expLines.amount_includes_tax,
p_expLines.taxId,
p_expLines.taxOverrideFlag,
p_expLines.merchant,
p_expLines.merchantDoc,
p_expLines.taxReference,
p_expLines.taxRegNumber,
p_expLines.taxPayerId,
p_expLines.supplyCountry,
p_expLines.cCardTrxnId,
to_number(p_expLines.project_id),
p_expLines.project_number,
to_number(p_expLines.task_id),
p_expLines.task_number,
DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',
p_xpense_lines.expenditure_organization_id,NULL),
p_expLines.expenditure_type,
DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',NVL(p_xpense_lines.date2_temp, p_xpense_lines.date1_temp),NULL),
p_xpense_lines.IsReceiptProjectEnabled,
p_xpense_lines.company_prepaid_invoice_id, --company prepaid invoice id
DECODE(l_context_enabled,'Y',p_xpense_lines.item_description,NULL),
P_AttributeCol(1),
P_AttributeCol(2),
P_AttributeCol(3),
P_AttributeCol(4),
P_AttributeCol(5),
P_AttributeCol(6),
P_AttributeCol(7),
P_AttributeCol(8),
P_AttributeCol(9),
P_AttributeCol(10),
P_AttributeCol(11),
P_AttributeCol(12),
P_AttributeCol(13),
P_AttributeCol(14),
P_AttributeCol(15),
ap_expense_report_lines_s.nextval,
mo_global.get_current_org_id() );
SELECT REPORT_LINE_ID, ITEMIZATION_PARENT_ID
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_HEADER_ID = p_orig_expense_report_id
AND nvl(policy_shortpay_flag,'N') = 'Y'
AND nvl(itemization_parent_id,-1) = -1;/*Bug:6131435*/
SELECT REPORT_LINE_ID
FROM AP_EXPENSE_REPORT_LINES
WHERE itemization_parent_id = p_report_line_id
and report_header_id = p_orig_expense_report_id; -- Bug: 6705839, Performance Issue during shortpay
UPDATE AP_EXPENSE_REPORT_LINES_ALL
SET report_header_id = p_new_expense_report_id,
mileage_rate_adjusted_flag = C_Unchanged,
last_update_date = sysdate,
creation_date = sysdate
WHERE report_line_id = l_OrigReportLineID;
UPDATE AP_EXPENSE_REPORT_LINES_ALL
SET report_header_id = p_new_expense_report_id,
mileage_rate_adjusted_flag = C_Unchanged,
last_update_date = sysdate,
creation_date = sysdate
WHERE report_line_id = i.report_line_id;
EXCEPTION -- Block which encapsulates the delete and insert code
WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
AP_WEB_DB_UTIL_PKG.RaiseException('AddPolicyShortPaidExpLines','',
'AP_WEB_SAVESUB_DELETE_FAILED');
SELECT REPORT_LINE_ID, ITEMIZATION_PARENT_ID
FROM ap_expense_report_lines aerl1
WHERE aerl1.report_header_id = p_orig_expense_report_id
AND nvl(itemization_parent_id,-1) = -1 /*Bug# 6131435*/
/* jrautiai ADJ Fix
* We need to move all lines in the itemization when one of them is missing a receipt
*/
AND (
( aerl1.receipt_required_flag = 'Y'
AND nvl(aerl1.receipt_verified_flag,'N') = 'N'
AND nvl(aerl1.policy_shortpay_flag, 'N') = 'N'
)
OR
( EXISTS (SELECT aerl2.report_header_id
FROM ap_expense_report_lines aerl2
WHERE aerl2.report_header_id = aerl1.report_header_id
AND ((aerl1.credit_card_trx_id IS NULL AND aerl2.credit_card_trx_id is NULL AND aerl1.itemization_parent_id = -1 AND aerl2.itemization_parent_id = aerl1.report_line_id)
OR
(aerl1.credit_card_trx_id IS NOT NULL AND aerl2.credit_card_trx_id = aerl1.credit_card_trx_id)
)
AND aerl2.receipt_required_flag = 'Y'
AND nvl(aerl2.receipt_verified_flag,'N') = 'N'
AND nvl(aerl2.policy_shortpay_flag, 'N') = 'N'
)
)
);
SELECT REPORT_LINE_ID
FROM AP_EXPENSE_REPORT_LINES
WHERE itemization_parent_id = p_report_line_id
and report_header_id = p_orig_expense_report_id; -- Bug: 6705839, Performance Issue during shortpay
UPDATE AP_EXPENSE_REPORT_LINES_all
SET report_header_id = p_new_expense_report_id,
mileage_rate_adjusted_flag = C_Unchanged,
last_update_date = sysdate,
creation_date = sysdate
WHERE report_line_id = l_OrigReportLineID;
UPDATE AP_EXPENSE_REPORT_LINES_ALL
SET report_header_id = p_new_expense_report_id,
mileage_rate_adjusted_flag = C_Unchanged,
last_update_date = sysdate,
creation_date = sysdate
WHERE report_line_id = i.report_line_id;
EXCEPTION -- Block which encapsulates the delete and insert code
WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
AP_WEB_DB_UTIL_PKG.RaiseException('AddUnverifiedShortpaidLines','',
'AP_WEB_SAVESUB_DELETE_FAILED',
'V_ReportHeaderID = ' || p_orig_expense_report_id);
PROCEDURE InsertCCLine(
p_new_report_header_id IN expLines_headerID,
p_report_line_id IN NUMBER,
p_itemization_parent_id IN NUMBER,
p_new_report_line_id IN NUMBER,
p_distribution_line_number IN NUMBER) IS
l_clearning_ccid NUMBER;
l_last_updated_by AP_EXP_REPORT_DISTS.LAST_UPDATED_BY%TYPE;
SELECT DT.ORG_ID,
DT.SEQUENCE_NUM,
DT.LAST_UPDATED_BY,
DT.CREATED_BY,
DT.PREPARER_MODIFIED_FLAG,
DT.AMOUNT,
DT.COST_CENTER
FROM AP_EXP_REPORT_DISTS_ALL DT,
AP_EXPENSE_REPORT_LINES_ALL DL
WHERE DT.REPORT_LINE_ID = p_line_id
AND DT.REPORT_LINE_ID = DL.REPORT_LINE_ID
AND (DL.ITEMIZATION_PARENT_ID IS NULL
OR
DL.ITEMIZATION_PARENT_ID <> -1);
AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'Start InsertCCLine');
SELECT AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(credit_card_trx_id)
INTO l_clearning_ccid
FROM ap_expense_report_lines erl
WHERE report_line_id = p_report_line_id;
INSERT INTO ap_expense_report_lines
(report_header_id,
last_update_date,
last_updated_by,
code_combination_id,
item_description,
set_of_books_id,
amount,
currency_code,
exchange_rate_type,
exchange_rate,
exchange_date,
line_type_lookup_code,
last_update_login,
creation_date,
created_by,
stat_amount,
distribution_line_number,
reference_1,
reference_2,
awt_group_id,
org_id,
justification_required_flag,
receipt_required_flag,
receipt_verified_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
daily_amount,
receipt_currency_amount,
web_parameter_id,
adjustment_reason,
policy_shortpay_flag,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
company_prepaid_invoice_id,
location_id,
itemization_parent_id,
func_currency_amt,
ap_validation_error,
category_code, -- bug 3311471
flex_concatenated,
location,
adjustment_reason_code, -- jrautiai ADJ Fix
submitted_amount, -- jrautiai ADJ Fix
report_line_id, -- LLA
allocation_split_code --Bug#6870253
)
SELECT p_new_report_header_id,
sysdate,
last_updated_by,
l_clearning_ccid,
item_description,
set_of_books_id,
amount,
currency_code,
exchange_rate_type,
exchange_rate,
exchange_date,
'MISCELLANEOUS',
last_update_login,
sysdate,
created_by,
stat_amount,
p_distribution_line_number AS distribution_line_number,
reference_1,
reference_2,
awt_group_id,
erl.org_id,
justification_required_flag,
receipt_required_flag,
receipt_verified_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
daily_amount,
receipt_currency_amount,
web_parameter_id,
adjustment_reason,
policy_shortpay_flag,
merchant_document_number,
merchant_name,
erl.merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
company_prepaid_invoice_id,
location_id,
p_itemization_parent_id AS itemization_parent_id,
func_currency_amt,
ap_validation_error,
category_code, -- bug 3311471
flex_concatenated,
location,
adjustment_reason_code, -- jrautiai ADJ Fix
submitted_amount, -- jrautiai ADJ Fix
p_new_report_line_id,
allocation_split_code --Bug#6870253
FROM ap_expense_report_lines erl
WHERE report_line_id = p_report_line_id;
SELECT GL.SEGMENT1, GL.SEGMENT2,GL.SEGMENT3,GL.SEGMENT4, GL.SEGMENT5, GL.SEGMENT6,
GL.SEGMENT7, GL.SEGMENT8,GL.SEGMENT9,GL.SEGMENT10,GL.SEGMENT11, GL.SEGMENT12,
GL.SEGMENT13, GL.SEGMENT14, GL.SEGMENT15, GL.SEGMENT16, GL.SEGMENT17, GL.SEGMENT18,
GL.SEGMENT19, GL.SEGMENT20, GL.SEGMENT21, GL.SEGMENT22, GL.SEGMENT23, GL.SEGMENT24,
GL.SEGMENT25, GL.SEGMENT26, GL.SEGMENT27, GL.SEGMENT28, GL.SEGMENT29, GL.SEGMENT30
INTO l_segment1, l_segment2, l_segment3, l_segment4, l_segment5, l_segment6,
l_segment7, l_segment8, l_segment9, l_segment10, l_segment11, l_segment12,
l_segment13, l_segment14, l_segment15, l_segment16, l_segment17, l_segment18,
l_segment19, l_segment20, l_segment21, l_segment22, l_segment23, l_segment24,
l_segment25, l_segment26, l_segment27, l_segment28, l_segment29, l_segment30
FROM GL_CODE_COMBINATIONS GL
WHERE GL.code_combination_id(+) = l_clearning_ccid;
FETCH copy_dist into l_org_id, l_sequence_num, l_last_updated_by, l_created_by,
l_preparer_modified_flag, l_amount, l_cost_center;
INSERT INTO AP_EXP_REPORT_DISTS
(
report_header_id,
report_line_id,
report_distribution_id,
org_id,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
preparer_modified_flag,
amount,
project_id,
task_id,
award_id,
expenditure_organization_id,
cost_center
)
VALUES (
p_new_report_header_id,
p_new_report_line_id,
AP_EXP_REPORT_DISTS_S.NEXTVAL,
l_org_id,
l_sequence_num,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_created_by,
l_clearning_ccid,
l_segment1,
l_segment2,
l_segment3,
l_segment4,
l_segment5,
l_segment6,
l_segment7,
l_segment8,
l_segment9,
l_segment10,
l_segment11,
l_segment12,
l_segment13,
l_segment14,
l_segment15,
l_segment16,
l_segment17,
l_segment18,
l_segment19,
l_segment20,
l_segment21,
l_segment22,
l_segment23,
l_segment24,
l_segment25,
l_segment26,
l_segment27,
l_segment28,
l_segment29,
l_segment30,
l_preparer_modified_flag,
l_amount,
null,
null,
null,
null,
l_cost_center);
AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end InsertCCLine');
AP_WEB_DB_UTIL_PKG.RaiseException('InsertCCLine');
END InsertCCLine;
SELECT REPORT_LINE_ID
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_HEADER_ID = P_source_report_header_id
AND ITEMIZATION_PARENT_ID = p_source_parent_report_line_id;
SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
INTO l_NewReportLineID
FROM DUAL;
InsertCCLine(p_new_report_header_id => p_target_report_header_id,
p_report_line_id => i.report_line_id,
p_itemization_parent_id => p_target_parent_report_line_id,
p_new_report_line_id => l_NewReportLineID,
p_distribution_line_number => l_NewReportLineID);
SELECT report_line_id, itemization_parent_id, distribution_line_number
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND credit_card_trx_id IS NOT NULL
AND (itemization_parent_id is null
OR
itemization_parent_id = -1);
SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
INTO l_NewReportLineID
FROM DUAL;
InsertCCLine(p_new_report_header_id => p_new_report_id,
p_report_line_id => i.report_line_id,
p_itemization_parent_id => i.itemization_parent_id,
p_new_report_line_id => l_NewReportLineID,
p_distribution_line_number => i.distribution_line_number);
PROCEDURE DeleteAddonRates(P_ReportID IN NUMBER) IS
--------------------------------------------------------------------------------
l_temp OIE_ADDON_MILEAGE_RATES.ADDON_RATE_TYPE%type;
SELECT ADDON_RATE_TYPE
FROM OIE_ADDON_MILEAGE_RATES addon, AP_EXPENSE_REPORT_LINES el
WHERE (el.REPORT_HEADER_ID = P_ReportID AND
el.REPORT_LINE_ID = addon.REPORT_LINE_ID)
FOR UPDATE OF ADDON_RATE_TYPE NOWAIT;
DELETE OIE_ADDON_MILEAGE_RATES WHERE CURRENT OF addonRates;
AP_WEB_DB_UTIL_PKG.RaiseException('DeleteAddonRates');
END DeleteAddonRates;
PROCEDURE DeletePDMDailyBreakup(P_ReportID IN NUMBER) IS
--------------------------------------------------------------------------------
l_temp OIE_PDM_DAILY_BREAKUPS.PDM_DAILY_BREAKUP_ID%type;
SELECT PDM_DAILY_BREAKUP_ID
FROM OIE_PDM_DAILY_BREAKUPS db, AP_EXPENSE_REPORT_LINES el
WHERE (el.REPORT_HEADER_ID = P_ReportID AND
el.REPORT_LINE_ID = db.REPORT_LINE_ID)
FOR UPDATE OF PDM_DESTINATION_ID NOWAIT;
DELETE OIE_PDM_DAILY_BREAKUPS WHERE CURRENT OF dailyBreakup;
AP_WEB_DB_UTIL_PKG.RaiseException('DeletePDMDailyBreakup');
END DeletePDMDailyBreakup;
PROCEDURE DeletePDMDestination(P_ReportID IN NUMBER) IS
--------------------------------------------------------------------------------
l_temp OIE_PDM_DESTINATIONS.PDM_DESTINATION_ID%type;
SELECT PDM_DESTINATION_ID
FROM OIE_PDM_DESTINATIONS db, AP_EXPENSE_REPORT_LINES el
WHERE (el.REPORT_HEADER_ID = P_ReportID AND
el.REPORT_LINE_ID = db.REPORT_LINE_ID)
FOR UPDATE OF PDM_DESTINATION_ID NOWAIT;
DELETE OIE_PDM_DESTINATIONS WHERE CURRENT OF pdmDestination;
AP_WEB_DB_UTIL_PKG.RaiseException('DeletePDMDestination');
END DeletePDMDestination;
FUNCTION DeleteReportLines(P_ReportID IN expLines_headerID)
RETURN BOOLEAN IS
--------------------------------------------------------------------------------
l_TempReportHeaderID expLines_headerID;
l_curr_calling_sequence VARCHAR2(100) := 'DeleteReportLines';
SELECT REPORT_HEADER_ID, REPORT_LINE_ID
FROM AP_EXPENSE_REPORT_LINES
WHERE (REPORT_HEADER_ID = P_ReportID)
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
AP_WEB_DB_EXPDIST_PKG.DeleteReportDistributions(P_ReportID);
AP_WEB_DB_EXP_ATTENDEES_PKG.deleteAttendees(P_ReportID);
DeleteAddonRates(P_ReportID);
DeletePDMDailyBreakup(P_ReportID);
DeletePDMDestination(P_ReportID);
DELETE AP_EXPENSE_REPORT_LINES WHERE CURRENT OF ReportLines;
/* Delete attachments assocated with the line */
fnd_attached_documents2_pkg.delete_attachments(
X_entity_name => 'OIE_LINE_ATTACHMENTS',
X_pk1_value => l_TempReportLineID,
X_delete_document_flag => 'Y'
);
AP_WEB_DB_UTIL_PKG.RaiseException('DeleteReportLines');
END DeleteReportLines;
FUNCTION DeletePersonalLines(p_report_header_id IN expLines_headerID
) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
DELETE FROM ap_expense_report_lines
WHERE web_parameter_id = l_personalParameterId
AND report_header_id = p_report_header_id;
AP_WEB_DB_UTIL_PKG.RaiseException('DeletePersonalLines');
END DeletePersonalLines;
FUNCTION DeleteCreditReportLines(p_report_header_id IN expLines_headerID) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
BEGIN
DELETE FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND credit_card_trx_id IS NOT NULL;
AP_WEB_DB_UTIL_PKG.RaiseException('DeleteCreditReportLines');
END DeleteCreditReportLines;
UPDATE ap_expense_report_lines RL
SET awt_group_id = decode(p_sys_allow_awt_flag, 'Y',
decode(p_ven_allow_awt_flag, 'Y', p_ven_awt_group_id,
null), null),
justification_required_flag = (SELECT nvl(justification_required_flag,'V')
FROM ap_expense_report_params
WHERE parameter_id = RL.web_parameter_id)
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_lines
SET receipt_missing_flag = p_flag
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_lines
SET receipt_required_flag = p_required_flag
WHERE nvl(receipt_missing_flag, 'N') = 'Y'
AND report_header_id = p_report_header_id;
SELECT decode(erl.web_parameter_id,l_parameterId,-erl.amount,erl.amount),
cc.company_prepaid_invoice_id, cc.card_program_id,
decode(erl.web_parameter_id,l_parameterId, 'PERSONAL', 'BUSINESS'),
erl.org_id,
nvl(cc.transaction_date,sysdate),
erh.employee_id
FROM ap_expense_report_lines erl,
ap_credit_card_trxns cc,
ap_expense_report_headers erh
WHERE erl.report_header_id = erh.report_header_id
AND nvl(erl.itemization_parent_id,0) <> -1 /* Itemization Project */
AND cc.trx_id = erl.credit_card_trx_id -- is a credit card transaction
AND cc.payment_due_from_code in ('BOTH','COMPANY') -- Both Pay split project
AND erh.report_header_id = p_expReportHeaderId
AND erh.source = 'SelfService';
SELECT sum(erl.amount)
INTO l_prepaid_amt
FROM ap_expense_report_lines erl,
ap_credit_card_trxns cc
WHERE erl.report_header_id = p_expReportHeaderId
AND cc.trx_id = erl.credit_card_trx_id -- is a credit card transaction
AND (cc.category is null OR cc.category <> 'PERSONAL') -- not a personal expense
AND erl.web_parameter_id <> l_parameterId -- Not personal itemized line
AND cc.company_prepaid_invoice_id IS NOT null; -- company prepaid
l_debugInfo := 'Update the amount.';
select receipt_missing_flag
into p_missing_receipts_flag
from ap_expense_report_lines
where report_header_id = p_report_header_id
and receipt_missing_flag = 'Y'
and rownum = 1;
SELECT XL.start_expense_date,
XL.end_expense_date,
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
XL.start_expense_date)+1),4),
XL.distribution_line_number,
XP.company_policy_id,
XL.avg_mileage_rate,
XL.distance_unit_code,
nvl(XL.trip_distance,0),
nvl(XL.daily_distance,0),
XP.category_code,
XL.currency_code,
XL.amount,
XL.number_people,
XL.web_parameter_id,
XL.rate_per_passenger,
XL.attribute1,
XL.attribute2,
XL.attribute3,
XL.attribute4,
XL.attribute5,
XL.attribute6,
XL.attribute7,
XL.attribute8,
XL.attribute9,
XL.attribute10,
XL.attribute11,
XL.attribute12,
XL.attribute13,
XL.attribute14,
XL.attribute15,
XL.report_line_id
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_lookup_codes LC,
PA_PROJECTS_ALL PAP,
PA_TASKS PAT,
GMS_AWARDS GMS
WHERE XL.report_header_id = p_report_header_id
AND XL.project_id is not null
AND XL.task_id is not null
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id = PAP.project_id
AND XL.project_id = PAT.project_id
AND XL.task_id = PAT.task_id
AND XL.award_id = GMS.award_id(+)
UNION ALL
SELECT XL.start_expense_date,
XL.end_expense_date,
LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
XL.start_expense_date)+1),4),
XL.distribution_line_number,
XP.company_policy_id,
XL.avg_mileage_rate,
XL.distance_unit_code,
nvl(XL.trip_distance,0),
nvl(XL.daily_distance,0),
XP.category_code,
XL.currency_code,
XL.amount,
XL.number_people,
XL.web_parameter_id,
XL.rate_per_passenger,
XL.attribute1,
XL.attribute2,
XL.attribute3,
XL.attribute4,
XL.attribute5,
XL.attribute6,
XL.attribute7,
XL.attribute8,
XL.attribute9,
XL.attribute10,
XL.attribute11,
XL.attribute12,
XL.attribute13,
XL.attribute14,
XL.attribute15,
XL.report_line_id
FROM ap_expense_report_params XP,
ap_expense_report_lines XL,
ap_lookup_codes LC
WHERE XL.report_header_id = p_report_header_id
AND XL.web_parameter_id = XP.parameter_id
AND XL.line_type_lookup_code = LC.lookup_code
AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND XL.project_id is null
AND XL.task_id is null
AND XL.award_id is null
ORDER BY distribution_line_number;
PROCEDURE updateMileageExpLine(
p_avg_mileage_rate IN expLines_avg_mileage_rate,
p_report_header_id IN expLines_headerID,
p_distribution_line_number IN expLines_distLineNum,
p_new_dist_line_number IN expLines_distLineNum,
p_amount IN expLines_amount,
p_trip_distance IN expLines_trip_distance,
p_daily_distance IN NUMBER,
p_daily_amount IN expLines_dailyAmount,
p_receipt_currency_amount IN NUMBER,
p_status_code IN expLines_mrate_adj_flag
)IS
--------------------------------------------------------------------------------
l_debug_info VARCHAR2(200);
UPDATE ap_expense_report_lines
SET avg_mileage_rate = p_avg_mileage_rate,
amount = p_amount,
distribution_line_number = p_new_dist_line_number,
trip_distance = p_trip_distance,
daily_distance = p_daily_distance,
daily_amount = p_daily_amount,
receipt_currency_amount = p_receipt_currency_amount,
mileage_rate_adjusted_flag = p_status_code
WHERE report_header_id = p_report_header_id
AND distribution_line_number = p_distribution_line_number;
AP_WEB_DB_UTIL_PKG.RaiseException('updateMileageExpLine: no data found');
AP_WEB_DB_UTIL_PKG.RaiseException('updateMileageExpLine');
END updateMileageExpLine;
PROCEDURE updateExpenseMileageLines(
p_mileage_line_array IN Mileage_Line_Array,
p_bUpdatedHeader OUT NOCOPY BOOLEAN
) IS
--------------------------------------------------------------------------------
i NUMBER := 1;
p_bUpdatedHeader := FALSE;
-- Modified: update database
updateMileageExpLine(
p_avg_mileage_rate => p_mileage_line_array(i).avg_mileage_rate,
p_report_header_id => p_mileage_line_array(i).report_header_id,
p_distribution_line_number => p_mileage_line_array(i).orig_dist_line_number,
p_new_dist_line_number => p_mileage_line_array(i).new_dist_line_number,
p_amount => p_mileage_line_array(i).amount,
p_trip_distance => p_mileage_line_array(i).trip_distance,
p_daily_distance => p_mileage_line_array(i).daily_distance,
p_daily_amount => p_mileage_line_array(i).daily_amount,
p_receipt_currency_amount => p_mileage_line_array(i).receipt_currency_amount,
p_status_code => p_mileage_line_array(i).status);
p_bUpdatedHeader := TRUE;
AP_WEB_DB_UTIL_PKG.RaiseException('updateExpenseMileageLines');
END updateExpenseMileageLines;
SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
INTO l_report_line_id
FROM DUAL;
INSERT INTO ap_expense_report_lines
(REPORT_HEADER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_DATE,
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
STAT_AMOUNT,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
DISTRIBUTION_LINE_NUMBER,
REFERENCE_1,
REFERENCE_2,
AWT_GROUP_ID,
ORG_ID,
RECEIPT_VERIFIED_FLAG,
JUSTIFICATION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
RECEIPT_MISSING_FLAG,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
DAILY_AMOUNT,
RECEIPT_CURRENCY_AMOUNT,
WEB_PARAMETER_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
ADJUSTMENT_REASON,
POLICY_SHORTPAY_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
CREDIT_CARD_TRX_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
COMPANY_PREPAID_INVOICE_ID,
PROJECT_NUMBER,
TASK_NUMBER,
PA_INTERFACED_FLAG,
AWARD_NUMBER,
VEHICLE_CATEGORY_CODE,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
DAILY_DISTANCE,
AVG_MILEAGE_RATE,
DESTINATION_FROM,
DESTINATION_TO,
TRIP_DISTANCE,
DISTANCE_UNIT_CODE,
LICENSE_PLATE_NUMBER,
LOCATION_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
ATTENDEES,
NUMBER_ATTENDEES,
TICKET_CLASS_CODE,
TRAVEL_TYPE,
TICKET_NUMBER,
FLIGHT_NUMBER,
LOCATION_TO_ID,
ITEMIZATION_PARENT_ID,
FLEX_CONCATENATED,
MILEAGE_RATE_ADJUSTED_FLAG,
FUNC_CURRENCY_AMT,
CATEGORY_CODE,
ADJUSTMENT_REASON_CODE,
LOCATION,
AP_VALIDATION_ERROR,
REPORT_LINE_ID
)
SELECT
REPORT_HEADER_ID,
SYSDATE,
LAST_UPDATED_BY,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
p_new_amount,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_DATE,
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
LAST_UPDATE_LOGIN,
SYSDATE,
CREATED_BY,
STAT_AMOUNT,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
p_new_distribution_line_number,
REFERENCE_1,
REFERENCE_2,
AWT_GROUP_ID,
ORG_ID,
RECEIPT_VERIFIED_FLAG,
JUSTIFICATION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
RECEIPT_MISSING_FLAG,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
p_daily_amount,
p_receipt_currency_amount,
WEB_PARAMETER_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
ADJUSTMENT_REASON,
POLICY_SHORTPAY_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
CREDIT_CARD_TRX_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
COMPANY_PREPAID_INVOICE_ID,
PROJECT_NUMBER,
TASK_NUMBER,
PA_INTERFACED_FLAG,
AWARD_NUMBER,
VEHICLE_CATEGORY_CODE,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
p_new_daily_distance,
p_new_avg_mileage_rate,
DESTINATION_FROM,
DESTINATION_TO,
p_new_trip_distance,
DISTANCE_UNIT_CODE,
LICENSE_PLATE_NUMBER,
LOCATION_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
ATTENDEES,
NUMBER_ATTENDEES,
TICKET_CLASS_CODE,
TRAVEL_TYPE,
TICKET_NUMBER,
FLIGHT_NUMBER,
LOCATION_TO_ID,
ITEMIZATION_PARENT_ID,
FLEX_CONCATENATED,
AP_WEB_DB_EXPLINE_PKG.C_New,
FUNC_CURRENCY_AMT,
CATEGORY_CODE,
ADJUSTMENT_REASON_CODE,
LOCATION,
AP_VALIDATION_ERROR,
l_report_line_id
FROM ap_expense_report_lines
WHERE report_header_id = p_orig_expense_report_id
AND distribution_line_number = p_orig_dist_line_number;
INSERT INTO OIE_ADDON_MILEAGE_RATES(REPORT_LINE_ID,
ADDON_RATE_TYPE,
MILEAGE_RATE,
MILEAGE_AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
SELECT X_REPORT_LINE_ID,
ADDON_RATE_TYPE,
MILEAGE_RATE,
MILEAGE_AMOUNT,
SYSDATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
SYSDATE
FROM OIE_ADDON_MILEAGE_RATES
WHERE REPORT_LINE_ID = (SELECT REPORT_LINE_ID
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_HEADER_ID = P_ORIG_EXPENSE_REPORT_ID
AND DISTRIBUTION_LINE_NUMBER = P_ORIG_DIST_LINE_NUMBER
AND ROWNUM = 1);
'AP_WEB_SAVESUB_DELETE_FAILED');
SELECT CATEGORY_CODE INTO L_CATEGORY_CODE
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
INSERT INTO OIE_ADDON_MILEAGE_RATES(REPORT_LINE_ID,
ADDON_RATE_TYPE,
MILEAGE_RATE,
MILEAGE_AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
SELECT P_TO_REPORT_LINE_ID,
ADDON_RATE_TYPE,
MILEAGE_RATE,
MILEAGE_AMOUNT,
SYSDATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
SYSDATE
FROM OIE_ADDON_MILEAGE_RATES
WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
SELECT CATEGORY_CODE INTO L_CATEGORY_CODE
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
INSERT INTO OIE_PDM_DAILY_BREAKUPS(PDM_DAILY_BREAKUP_ID,
REPORT_LINE_ID,
START_DATE,
END_DATE,
AMOUNT,
NUMBER_OF_MEALS,
MEALS_AMOUNT,
BREAKFAST_FLAG,
LUNCH_FLAG,
DINNER_FLAG,
ACCOMMODATION_FLAG,
ACCOMMODATION_AMOUNT,
HOTEL_NAME,
NIGHT_RATE_TYPE,
NIGHT_RATE_AMOUNT,
PDM_RATE,
RATE_TYPE_CODE,
PDM_DESTINATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
SELECT OIE_PDM_DAILY_BREAKUPS_S.nextval,
p_to_report_line_id,
START_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
END_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
AMOUNT,
NUMBER_OF_MEALS,
MEALS_AMOUNT,
BREAKFAST_FLAG,
LUNCH_FLAG,
DINNER_FLAG,
ACCOMMODATION_FLAG,
ACCOMMODATION_AMOUNT,
HOTEL_NAME,
NIGHT_RATE_TYPE,
NIGHT_RATE_AMOUNT,
PDM_RATE,
RATE_TYPE_CODE,
PDM_DESTINATION_ID,
SYSDATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
SYSDATE
FROM OIE_PDM_DAILY_BREAKUPS
WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
SELECT CATEGORY_CODE INTO L_CATEGORY_CODE
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
INSERT INTO OIE_PDM_DESTINATIONS(PDM_DESTINATION_ID,
REPORT_LINE_ID,
START_DATE,
END_DATE,
LOCATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
SELECT OIE_PDM_DESTINATIONS_S.nextval,
p_to_report_line_id,
START_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
END_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
LOCATION_ID,
SYSDATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
SYSDATE
FROM OIE_PDM_DESTINATIONS
WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
SELECT report_line_id, receipt_currency_code, currency_code, (start_expense_date+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward), receipt_currency_amount
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_HEADER_ID = P_source_report_header_id
AND CREDIT_CARD_TRX_ID is null
AND ITEMIZATION_PARENT_ID = p_source_parent_report_line_id;
CURSOR update_new_parent_line_amt_c IS
SELECT ael.*
FROM ap_expense_report_lines ael
WHERE report_header_id = p_target_report_header_id
AND report_line_id = p_target_parent_report_line_id
FOR UPDATE OF report_header_id, report_line_id NOWAIT;
l_parent_line_rec update_new_parent_line_amt_c%ROWTYPE;
SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
INTO l_NewReportLineID
FROM DUAL;
insert into AP_EXPENSE_REPORT_LINES
(
REPORT_HEADER_ID,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
ITEMIZATION_PARENT_ID,
AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_DATE,
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
DISTRIBUTION_LINE_NUMBER,
ORG_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
DAILY_AMOUNT,
RECEIPT_CURRENCY_AMOUNT,
WEB_PARAMETER_ID,
AMOUNT_INCLUDES_TAX_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
PA_INTERFACED_FLAG,
PROJECT_NUMBER,
TASK_NUMBER,
AWARD_NUMBER,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
AVG_MILEAGE_RATE,
DESTINATION_FROM,
DESTINATION_TO,
TRIP_DISTANCE,
LOCATION_ID,
ATTENDEES,
TICKET_NUMBER,
FLIGHT_NUMBER,
LICENSE_PLATE_NUMBER,
NUMBER_ATTENDEES,
LOCATION_TO_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
TRAVEL_TYPE,
FLEX_CONCATENATED,
VEHICLE_CATEGORY_CODE,
DISTANCE_UNIT_CODE,
TICKET_CLASS_CODE,
DAILY_DISTANCE,
FUNC_CURRENCY_AMT,
LOCATION,
CATEGORY_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REPORT_LINE_ID
)
select
p_target_report_header_id AS REPORT_HEADER_ID,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
p_target_parent_report_line_id AS ITEMIZATION_PARENT_ID,
l_amount, -- Bug 6689280 (sodash)
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
(START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward) as EXCHANGE_DATE,
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
l_NewReportLineID,
ORG_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS START_EXPENSE_DATE, -- roll forward 7 days
END_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS END_EXPENSE_DATE, -- roll forward 7 days
RECEIPT_CURRENCY_CODE,
l_Receipt_Conversion_Rate, -- Bug 6689280 (sodash) reclculating the receipt conversion rate. Prior to it, It was getting set to null when the receipt currency and the reimbursement currency didn't match
DAILY_AMOUNT,
RECEIPT_CURRENCY_AMOUNT,
WEB_PARAMETER_ID,
AMOUNT_INCLUDES_TAX_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
PA_INTERFACED_FLAG,
PROJECT_NUMBER,
TASK_NUMBER,
AWARD_NUMBER,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
AVG_MILEAGE_RATE,
DESTINATION_FROM,
DESTINATION_TO,
TRIP_DISTANCE,
LOCATION_ID,
ATTENDEES,
TICKET_NUMBER,
FLIGHT_NUMBER,
LICENSE_PLATE_NUMBER,
NUMBER_ATTENDEES,
LOCATION_TO_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
TRAVEL_TYPE,
FLEX_CONCATENATED,
VEHICLE_CATEGORY_CODE,
DISTANCE_UNIT_CODE,
TICKET_CLASS_CODE,
DAILY_DISTANCE,
FUNC_CURRENCY_AMT,
LOCATION,
CATEGORY_CODE,
sysdate AS CREATION_DATE,
p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
p_user_id AS LAST_UPDATED_BY,
l_NewReportLineID
from AP_EXPENSE_REPORT_LINES
where REPORT_LINE_ID = l_TempReportLineID;
OPEN update_new_parent_line_amt_c;
FETCH update_new_parent_line_amt_c into l_parent_line_rec;
UPDATE ap_expense_report_lines
SET amount = l_lines_total
WHERE CURRENT OF update_new_parent_line_amt_c;
CLOSE update_new_parent_line_amt_c;
SELECT report_line_id, itemization_parent_id, receipt_currency_code, currency_code, (start_expense_date+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward), receipt_currency_amount
FROM AP_EXPENSE_REPORT_LINES
WHERE REPORT_HEADER_ID = P_source_report_header_id
AND CREDIT_CARD_TRX_ID is null
AND (ITEMIZATION_PARENT_ID is null
OR
ITEMIZATION_PARENT_ID = -1);
SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
INTO l_NewReportLineID
FROM DUAL;
insert into AP_EXPENSE_REPORT_LINES
(
REPORT_HEADER_ID,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
ITEMIZATION_PARENT_ID,
AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_DATE,
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
DISTRIBUTION_LINE_NUMBER,
ORG_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
DAILY_AMOUNT,
RECEIPT_CURRENCY_AMOUNT,
WEB_PARAMETER_ID,
AMOUNT_INCLUDES_TAX_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
PA_INTERFACED_FLAG,
PROJECT_NUMBER,
TASK_NUMBER,
AWARD_NUMBER,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
AVG_MILEAGE_RATE,
DESTINATION_FROM,
DESTINATION_TO,
TRIP_DISTANCE,
LOCATION_ID,
ATTENDEES,
TICKET_NUMBER,
FLIGHT_NUMBER,
LICENSE_PLATE_NUMBER,
NUMBER_ATTENDEES,
LOCATION_TO_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
TRAVEL_TYPE,
FLEX_CONCATENATED,
VEHICLE_CATEGORY_CODE,
DISTANCE_UNIT_CODE,
TICKET_CLASS_CODE,
DAILY_DISTANCE,
FUNC_CURRENCY_AMT,
LOCATION,
CATEGORY_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REPORT_LINE_ID
)
select
p_target_report_header_id AS REPORT_HEADER_ID,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
ITEMIZATION_PARENT_ID,
l_amount, -- Bug 6689280 (sodash) changed it because the earlier decode condition set the amount to null when the receipt currency and the reimbursement currency didn't match
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE, -- Bug 6689280 (sodash)
START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS EXCHANGE_DATE, -- bug 6689280 (sodash)
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
AP_WEB_DB_EXPLINE_PKG.C_InitialDistLineNumber+DISTRIBUTION_LINE_NUMBER,
ORG_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS START_EXPENSE_DATE, -- roll forward 7 days
END_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS END_EXPENSE_DATE, -- roll forward 7 days
RECEIPT_CURRENCY_CODE,
l_Receipt_Conversion_Rate, -- Bug 6689280 (sodash) reclculating the receipt conversion rate. Prior to it, It was getting set to null when the receipt currency and the reimbursement currency didn't match
DAILY_AMOUNT,
RECEIPT_CURRENCY_AMOUNT,
WEB_PARAMETER_ID,
AMOUNT_INCLUDES_TAX_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
PA_INTERFACED_FLAG,
PROJECT_NUMBER,
TASK_NUMBER,
AWARD_NUMBER,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
AVG_MILEAGE_RATE,
DESTINATION_FROM,
DESTINATION_TO,
TRIP_DISTANCE,
LOCATION_ID,
ATTENDEES,
TICKET_NUMBER,
FLIGHT_NUMBER,
LICENSE_PLATE_NUMBER,
NUMBER_ATTENDEES,
LOCATION_TO_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
TRAVEL_TYPE,
FLEX_CONCATENATED,
VEHICLE_CATEGORY_CODE,
DISTANCE_UNIT_CODE,
TICKET_CLASS_CODE,
DAILY_DISTANCE,
FUNC_CURRENCY_AMT,
LOCATION,
CATEGORY_CODE,
sysdate AS CREATION_DATE,
p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
p_user_id AS LAST_UPDATED_BY,
l_NewReportLineID
from AP_EXPENSE_REPORT_LINES
where REPORT_LINE_ID = l_TempReportLineID;
UPDATE ap_expense_report_lines
SET ap_validation_error = ''
WHERE report_header_id = p_report_header_id;
PROCEDURE UpdateAPValidationError(
p_report_header_id IN expLines_headerID,
p_dist_line_number IN expLines_distLineNum,
p_ap_validation_error IN expLines_APValidationError) IS
-------------------------------------------------------------------
BEGIN
AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'start UpdateAPValidationError');
UPDATE ap_expense_report_lines
SET ap_validation_error = substrb(ap_validation_error||p_ap_validation_error,1,240)
WHERE report_header_id = p_report_header_id
AND distribution_line_number = p_dist_line_number;
AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end UpdateAPValidationError');
AP_WEB_DB_UTIL_PKG.RaiseException('UpdateAPValidationError');
AP_WEB_DB_UTIL_PKG.RaiseException('UpdateAPValidationError');
END UpdateAPValidationError;
UPDATE ap_expense_report_lines
SET receipt_verified_flag = null,
policy_shortpay_flag = null,
adjustment_reason = null
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers
SET audit_code = null, -- Bug 4019412
report_submitted_date = null
WHERE report_header_id = p_report_header_id;
SELECT count(*)
INTO p_count
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND merchant_name IS NOT NULL
AND credit_card_trx_id IS NULL;
* Need the ability to insert a single row, this procedure inserts a row in the
* database, using the data provided in the record given as parameter.
*/
PROCEDURE InsertLine(expense_line_rec in AP_EXPENSE_REPORT_LINES_ALL%ROWTYPE) IS
l_debug_info varchar2(240);
l_debug_info := 'InsertLine';
INSERT INTO AP_EXPENSE_REPORT_LINES_ALL
(REPORT_HEADER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CODE_COMBINATION_ID,
ITEM_DESCRIPTION,
SET_OF_BOOKS_ID,
AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_DATE,
VAT_CODE,
LINE_TYPE_LOOKUP_CODE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
STAT_AMOUNT,
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
DISTRIBUTION_LINE_NUMBER,
REFERENCE_1,
REFERENCE_2,
AWT_GROUP_ID,
ORG_ID,
RECEIPT_VERIFIED_FLAG,
JUSTIFICATION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
RECEIPT_MISSING_FLAG,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
DAILY_AMOUNT,
RECEIPT_CURRENCY_AMOUNT,
WEB_PARAMETER_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
AMOUNT_INCLUDES_TAX_FLAG,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
ADJUSTMENT_REASON,
POLICY_SHORTPAY_FLAG,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
CREDIT_CARD_TRX_ID,
ALLOCATION_REASON,
ALLOCATION_SPLIT_CODE,
PROJECT_NAME,
TASK_NAME,
COMPANY_PREPAID_INVOICE_ID,
PA_INTERFACED_FLAG,
PROJECT_NUMBER,
TASK_NUMBER,
AWARD_NUMBER,
VEHICLE_CATEGORY_CODE,
VEHICLE_TYPE,
FUEL_TYPE,
NUMBER_PEOPLE,
DAILY_DISTANCE,
DISTANCE_UNIT_CODE,
AVG_MILEAGE_RATE,
DESTINATION_FROM,
DESTINATION_TO,
TRIP_DISTANCE,
LICENSE_PLATE_NUMBER,
MILEAGE_RATE_ADJUSTED_FLAG,
LOCATION_ID,
NUM_PDM_DAYS1,
NUM_PDM_DAYS2,
NUM_PDM_DAYS3,
PER_DIEM_RATE1,
PER_DIEM_RATE2,
PER_DIEM_RATE3,
DEDUCTION_ADDITION_AMT1,
DEDUCTION_ADDITION_AMT2,
DEDUCTION_ADDITION_AMT3,
NUM_FREE_BREAKFASTS1,
NUM_FREE_LUNCHES1,
NUM_FREE_DINNERS1,
NUM_FREE_ACCOMMODATIONS1,
NUM_FREE_BREAKFASTS2,
NUM_FREE_LUNCHES2,
NUM_FREE_DINNERS2,
NUM_FREE_ACCOMMODATIONS2,
NUM_FREE_BREAKFASTS3,
NUM_FREE_LUNCHES3,
NUM_FREE_DINNERS3,
NUM_FREE_ACCOMMODATIONS3,
ATTENDEES,
NUMBER_ATTENDEES,
TRAVEL_TYPE,
TICKET_CLASS_CODE,
TICKET_NUMBER,
FLIGHT_NUMBER,
LOCATION_TO_ID,
ITEMIZATION_PARENT_ID,
FLEX_CONCATENATED,
FUNC_CURRENCY_AMT,
LOCATION,
CATEGORY_CODE,
ADJUSTMENT_REASON_CODE,
AP_VALIDATION_ERROR,
SUBMITTED_AMOUNT,
REPORT_LINE_ID)
VALUES (
expense_line_rec.REPORT_HEADER_ID,
expense_line_rec.LAST_UPDATE_DATE,
expense_line_rec.LAST_UPDATED_BY,
expense_line_rec.CODE_COMBINATION_ID,
expense_line_rec.ITEM_DESCRIPTION,
expense_line_rec.SET_OF_BOOKS_ID,
expense_line_rec.AMOUNT,
expense_line_rec.ATTRIBUTE_CATEGORY,
expense_line_rec.ATTRIBUTE1,
expense_line_rec.ATTRIBUTE2,
expense_line_rec.ATTRIBUTE3,
expense_line_rec.ATTRIBUTE4,
expense_line_rec.ATTRIBUTE5,
expense_line_rec.ATTRIBUTE6,
expense_line_rec.ATTRIBUTE7,
expense_line_rec.ATTRIBUTE8,
expense_line_rec.ATTRIBUTE9,
expense_line_rec.ATTRIBUTE10,
expense_line_rec.ATTRIBUTE11,
expense_line_rec.ATTRIBUTE12,
expense_line_rec.ATTRIBUTE13,
expense_line_rec.ATTRIBUTE14,
expense_line_rec.ATTRIBUTE15,
expense_line_rec.CURRENCY_CODE,
expense_line_rec.EXCHANGE_RATE_TYPE,
expense_line_rec.EXCHANGE_RATE,
expense_line_rec.EXCHANGE_DATE,
expense_line_rec.VAT_CODE,
expense_line_rec.LINE_TYPE_LOOKUP_CODE,
expense_line_rec.LAST_UPDATE_LOGIN,
expense_line_rec.CREATION_DATE,
expense_line_rec.CREATED_BY,
expense_line_rec.STAT_AMOUNT,
expense_line_rec.PROJECT_ACCOUNTING_CONTEXT,
expense_line_rec.EXPENDITURE_TYPE,
expense_line_rec.EXPENDITURE_ITEM_DATE,
expense_line_rec.PA_QUANTITY,
expense_line_rec.DISTRIBUTION_LINE_NUMBER,
expense_line_rec.REFERENCE_1,
expense_line_rec.REFERENCE_2,
expense_line_rec.AWT_GROUP_ID,
expense_line_rec.ORG_ID,
expense_line_rec.RECEIPT_VERIFIED_FLAG,
expense_line_rec.JUSTIFICATION_REQUIRED_FLAG,
expense_line_rec.RECEIPT_REQUIRED_FLAG,
expense_line_rec.RECEIPT_MISSING_FLAG,
expense_line_rec.JUSTIFICATION,
expense_line_rec.EXPENSE_GROUP,
expense_line_rec.START_EXPENSE_DATE,
expense_line_rec.END_EXPENSE_DATE,
expense_line_rec.RECEIPT_CURRENCY_CODE,
expense_line_rec.RECEIPT_CONVERSION_RATE,
expense_line_rec.DAILY_AMOUNT,
expense_line_rec.RECEIPT_CURRENCY_AMOUNT,
expense_line_rec.WEB_PARAMETER_ID,
expense_line_rec.GLOBAL_ATTRIBUTE_CATEGORY,
expense_line_rec.GLOBAL_ATTRIBUTE1,
expense_line_rec.GLOBAL_ATTRIBUTE2,
expense_line_rec.GLOBAL_ATTRIBUTE3,
expense_line_rec.GLOBAL_ATTRIBUTE4,
expense_line_rec.GLOBAL_ATTRIBUTE5,
expense_line_rec.GLOBAL_ATTRIBUTE6,
expense_line_rec.GLOBAL_ATTRIBUTE7,
expense_line_rec.GLOBAL_ATTRIBUTE8,
expense_line_rec.GLOBAL_ATTRIBUTE9,
expense_line_rec.GLOBAL_ATTRIBUTE10,
expense_line_rec.AMOUNT_INCLUDES_TAX_FLAG,
expense_line_rec.GLOBAL_ATTRIBUTE11,
expense_line_rec.GLOBAL_ATTRIBUTE12,
expense_line_rec.GLOBAL_ATTRIBUTE13,
expense_line_rec.GLOBAL_ATTRIBUTE14,
expense_line_rec.GLOBAL_ATTRIBUTE15,
expense_line_rec.GLOBAL_ATTRIBUTE16,
expense_line_rec.GLOBAL_ATTRIBUTE17,
expense_line_rec.GLOBAL_ATTRIBUTE18,
expense_line_rec.GLOBAL_ATTRIBUTE19,
expense_line_rec.GLOBAL_ATTRIBUTE20,
expense_line_rec.ADJUSTMENT_REASON,
expense_line_rec.POLICY_SHORTPAY_FLAG,
expense_line_rec.MERCHANT_DOCUMENT_NUMBER,
expense_line_rec.MERCHANT_NAME,
expense_line_rec.MERCHANT_REFERENCE,
expense_line_rec.MERCHANT_TAX_REG_NUMBER,
expense_line_rec.MERCHANT_TAXPAYER_ID,
expense_line_rec.COUNTRY_OF_SUPPLY,
expense_line_rec.TAX_CODE_OVERRIDE_FLAG,
expense_line_rec.TAX_CODE_ID,
expense_line_rec.CREDIT_CARD_TRX_ID,
expense_line_rec.ALLOCATION_REASON,
expense_line_rec.ALLOCATION_SPLIT_CODE,
expense_line_rec.PROJECT_NAME,
expense_line_rec.TASK_NAME,
expense_line_rec.COMPANY_PREPAID_INVOICE_ID,
expense_line_rec.PA_INTERFACED_FLAG,
expense_line_rec.PROJECT_NUMBER,
expense_line_rec.TASK_NUMBER,
expense_line_rec.AWARD_NUMBER,
expense_line_rec.VEHICLE_CATEGORY_CODE,
expense_line_rec.VEHICLE_TYPE,
expense_line_rec.FUEL_TYPE,
expense_line_rec.NUMBER_PEOPLE,
expense_line_rec.DAILY_DISTANCE,
expense_line_rec.DISTANCE_UNIT_CODE,
expense_line_rec.AVG_MILEAGE_RATE,
expense_line_rec.DESTINATION_FROM,
expense_line_rec.DESTINATION_TO,
expense_line_rec.TRIP_DISTANCE,
expense_line_rec.LICENSE_PLATE_NUMBER,
expense_line_rec.MILEAGE_RATE_ADJUSTED_FLAG,
expense_line_rec.LOCATION_ID,
expense_line_rec.NUM_PDM_DAYS1,
expense_line_rec.NUM_PDM_DAYS2,
expense_line_rec.NUM_PDM_DAYS3,
expense_line_rec.PER_DIEM_RATE1,
expense_line_rec.PER_DIEM_RATE2,
expense_line_rec.PER_DIEM_RATE3,
expense_line_rec.DEDUCTION_ADDITION_AMT1,
expense_line_rec.DEDUCTION_ADDITION_AMT2,
expense_line_rec.DEDUCTION_ADDITION_AMT3,
expense_line_rec.NUM_FREE_BREAKFASTS1,
expense_line_rec.NUM_FREE_LUNCHES1,
expense_line_rec.NUM_FREE_DINNERS1,
expense_line_rec.NUM_FREE_ACCOMMODATIONS1,
expense_line_rec.NUM_FREE_BREAKFASTS2,
expense_line_rec.NUM_FREE_LUNCHES2,
expense_line_rec.NUM_FREE_DINNERS2,
expense_line_rec.NUM_FREE_ACCOMMODATIONS2,
expense_line_rec.NUM_FREE_BREAKFASTS3,
expense_line_rec.NUM_FREE_LUNCHES3,
expense_line_rec.NUM_FREE_DINNERS3,
expense_line_rec.NUM_FREE_ACCOMMODATIONS3,
expense_line_rec.ATTENDEES,
expense_line_rec.NUMBER_ATTENDEES,
expense_line_rec.TRAVEL_TYPE,
expense_line_rec.TICKET_CLASS_CODE,
expense_line_rec.TICKET_NUMBER,
expense_line_rec.FLIGHT_NUMBER,
expense_line_rec.LOCATION_TO_ID,
expense_line_rec.ITEMIZATION_PARENT_ID,
expense_line_rec.FLEX_CONCATENATED,
expense_line_rec.FUNC_CURRENCY_AMT,
expense_line_rec.LOCATION,
expense_line_rec.CATEGORY_CODE,
expense_line_rec.ADJUSTMENT_REASON_CODE,
expense_line_rec.AP_VALIDATION_ERROR,
expense_line_rec.SUBMITTED_AMOUNT,
ap_expense_report_lines_s.nextval);
AP_WEB_DB_UTIL_PKG.RaiseException('InsertLine','',
'AP_WEB_SAVESUB_DELETE_FAILED');
AP_WEB_DB_UTIL_PKG.RaiseException('InsertLine',
l_debug_Info);
END InsertLine;
SELECT sum(DECODE(credit_card_trx_id, null,amount,0)),
sum(DECODE(credit_card_trx_id,
null,0,
DECODE(web_parameter_id,
l_personalParameterId,ABS(amount),
amount
)
)
),
sum(DECODE(web_parameter_id,
l_personalParameterId,0,
AMOUNT))
INTO l_cash_amt, l_ccard_amt, l_total_amt
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id <> -1);
select decode(shortpay_parent_id, null, 'N','Y')
into p_shortpaid_flag
from ap_expense_report_headers
where report_header_id = p_report_header_id;
SELECT count(*)
INTO p_count
FROM ap_expense_report_lines
WHERE report_header_id = p_report_header_id
AND nvl(policy_shortpay_flag, 'N') = 'Y';
SELECT sum(amount), sum(nvl(submitted_amount,0))
FROM ap_expense_report_lines aerl
WHERE aerl.report_header_id = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id = -1)
AND aerl.credit_card_trx_id is null ;
SELECT report_header_id
FROM ap_expense_report_lines aerl
WHERE aerl.report_header_id = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id = -1)
AND aerl.credit_card_trx_id is not null
AND NVL(submitted_amount,amount) <> amount;
UPDATE ap_expense_report_lines
SET submitted_amount = amount
WHERE report_header_id = p_report_header_id;
* been updated, but the update fails for some reason here, the implication is
* that the employee will get a adjustment notification when the shortpaid report
* is audited. Even if the auditor did not adjust the report. */
null;
SELECT to_char(nvl(CC.TRANSACTION_DATE,SYSDATE)),
LPAD(to_char(CC.expensed_amount),9),
CC.billed_currency_code,
nvl(CC.Merchant_name1 , Merchant_name2)
FROM ap_expense_report_headers XH,
ap_credit_card_trxns CC,
ap_lookup_codes alc
WHERE XH.report_header_id = p_report_header_id
AND XH.report_header_id = CC.report_header_id
AND XH.total = 0
AND alc.lookup_type = 'PAYMENT_DUE_FROM'
AND alc.lookup_code = CC.payment_due_from_code
AND alc.lookup_code = 'BOTH'
AND NOT EXISTS(SELECT 1 from AP_EXPENSE_REPORT_LINES XL
WHERE XH.report_header_id = XL.report_header_id);
SELECT count(*)
INTO p_personal_count
FROM ap_expense_report_headers XH,
ap_credit_card_trxns CC,
ap_lookup_codes alc
WHERE XH.report_header_id = p_report_header_id
AND XH.report_header_id = CC.report_header_id
AND XH.total = 0
AND alc.lookup_type = 'PAYMENT_DUE_FROM'
AND alc.lookup_code = CC.payment_due_from_code
AND alc.lookup_code = 'BOTH'
AND NOT EXISTS(SELECT 1 from AP_EXPENSE_REPORT_LINES XL
WHERE XH.report_header_id = XL.report_header_id);
UPDATE ap_expense_report_lines aerl
SET aerl.adjustment_reason_code = '',
aerl.adjustment_reason = '',
aerl.policy_shortpay_flag = '' -- Bug 3683276
WHERE aerl.report_header_id = p_report_header_id;
SELECT 'Y'
INTO l_temp
FROM ap_pol_violations
WHERE report_header_id = p_report_header_id
and rownum = 1;
SELECT AERL.report_line_id,
AERL.code_combination_id,
AERL.amount
FROM AP_EXPENSE_REPORT_LINES AERL
WHERE REPORT_HEADER_ID = p_reportId;
UPDATE ap_expense_report_headers_all
SET maximum_amount_to_apply = null,
advance_invoice_to_apply = null,
apply_advances_default = null,
prepay_apply_flag = null,
prepay_num = null,
prepay_dist_num = null,
prepay_apply_amount = null,
prepay_gl_date = null,
advances_justification = null
WHERE report_header_id = p_report_header_id;
select 'Y' into l_cc_lines_exist
from ap_expense_report_lines_all
where report_header_id = p_report_header_id
and credit_card_trx_id is not null
and rownum = 1;