oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT aerl.REPORT_HEADER_ID, aerl.LAST_UPDATE_DATE, aerl.LAST_UPDATED_BY, aerl.ITEM_DESCRIPTION, aerl.SET_OF_BOOKS_ID, aerl.AMOUNT, aerl.CURRENCY_CODE, aerl.VAT_CODE, aerl.LINE_TYPE_LOOKUP_CODE, aerl.LAST_UPDATE_LOGIN, aerl.CREATION_DATE, aerl.CREATED_BY, aerl.DISTRIBUTION_LINE_NUMBER, aerl.REFERENCE_1, aerl.REFERENCE_2, aerl.AWT_GROUP_ID, aerl.ORG_ID, aerl.RECEIPT_VERIFIED_FLAG, aerl.JUSTIFICATION_REQUIRED_FLAG, aerl.REPORT_LINE_ID, aerl.RECEIPT_REQUIRED_FLAG, aerl.RECEIPT_MISSING_FLAG, aerl.JUSTIFICATION, aerl.START_EXPENSE_DATE, aerl.END_EXPENSE_DATE, aerl.RECEIPT_CURRENCY_CODE, aerl.RECEIPT_CONVERSION_RATE, aerl.DAILY_AMOUNT, aerl.RECEIPT_CURRENCY_AMOUNT, aerl.WEB_PARAMETER_ID, aerl.AMOUNT_INCLUDES_TAX_FLAG, aerl.ADJUSTMENT_REASON, aerl.POLICY_SHORTPAY_FLAG, aerl.TAX_CODE_ID, aerl.MERCHANT_DOCUMENT_NUMBER, aerl.MERCHANT_NAME, aerl.MERCHANT_REFERENCE, aerl.MERCHANT_TAX_REG_NUMBER, aerl.MERCHANT_TAXPAYER_ID, aerl.CREDIT_CARD_TRX_ID, aerl.COMPANY_PREPAID_INVOICE_ID, aerl.NUMBER_PEOPLE, aerl.LOCATION_ID, aerl.ATTENDEES, aerl.NUMBER_ATTENDEES, aerl.LOCATION_TO_ID, aerl.ADJUSTMENT_REASON_CODE, aerl.EXPENDITURE_TYPE, aerl.itemization_parent_id, aerl.SUBMITTED_AMOUNT, aerl.allocation_split_code, decode(aerl.itemization_parent_id, -1, null, AP_WEB_AUDIT_UTILS.get_line_status(aerl.REPORT_HEADER_ID, aerl.DISTRIBUTION_LINE_NUMBER)) line_status, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_amount, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_rate(aerl.report_header_id, aerl.distribution_line_number)) allowable_exchange_rate, AP_WEB_AUDIT_UTILS.get_awt_group_info(aerl.AWT_GROUP_ID, 'name','VARCHAR2') awt_group_disp, aerl.AMOUNT orig_amount, aerl.RECEIPT_CONVERSION_RATE orig_receipt_conversion_rate, decode(aerl.itemization_parent_id, null, decode(AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id), 'Y', (select DECODE(trx.CATEGORY, 'BUSINESS',trx.transaction_amount, to_number(null)) from AP_CREDIT_CARD_TRXNS_all trx where trx.trx_id = aerl.credit_card_trx_id ) ), aerl.receipt_currency_amount) itemized_receipt_amount, DECODE(aerl.CREDIT_CARD_TRX_ID, null,'N', 'Y') cc_trx, AP_WEB_AUDIT_UTILS.get_expense_item_info(aerl.web_parameter_id,'category_code','VARCHAR2') category_code, AP_WEB_AUDIT_UTILS.get_concat_desc_flex(aerl.report_header_id, aerl.distribution_line_number) concat_desc_flex, DECODE(category_code, 'PER_DIEM', to_char(start_expense_date, 'HH24:MI:SS'), to_char(null)) start_time, DECODE(category_code, 'PER_DIEM', to_char(end_expense_date, 'HH24:MI:SS'), to_char(null)) end_time, DECODE(category_code, 'PER_DIEM', NVL(num_pdm_days1,0) + NVL(num_pdm_days2,0) + NVL(num_pdm_days3,0), NVL(end_expense_date - start_expense_date,0)+1) number_of_days, DECODE(category_code, 'PER_DIEM', AP_WEB_AUDIT_UTILS.get_average_pdm_rate(aerl.report_header_id, aerl.distribution_line_number), to_number(null)) avg_daily_rate, DECODE(category_code, 'PER_DIEM', NVL(deduction_addition_amt1,0) + NVL(deduction_addition_amt2,0) + NVL(deduction_addition_amt3,0), to_number(null)) deductions_and_additions, aerl.location location_expense_incurred, AP_WEB_POLICY_UTILS.get_location(location_to_id) destination_location, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_MILEAGE_RATE_ADJUSTED', aerl.MILEAGE_RATE_ADJUSTED_FLAG) mileage_rate_adjusted, AP_WEB_AUDIT_UTILS.get_allowable_daily_sum(aerl.report_header_id, aerl.distribution_line_number) allowable_daily_sum, AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id) personal_expense, AP_WEB_AUDIT_UTILS.is_rounding_line(aerl.web_parameter_id) rounding_line, AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_name, aerl.WEB_PARAMETER_ID orig_web_parameter_id, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_cc_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_cc_amount, DECODE(aerl.itemization_parent_id, null, 'N', -1, DECODE(aerl.credit_card_trx_id, null, 'N', AP_WEB_AUDIT_UTILS.has_default_cc_itemization(aerl.credit_card_trx_id)), 'N') provider_itemization, aerl.VAT_CODE vat_code_disp, decode(AP_WEB_AUDIT_UTILS.isAttendeeAvailable(aerl.report_line_id), 'Y', 'A', 'B') attendee_info, proj.segment1 project_number, proj.name project_name, task.task_number, task.task_name, award.award_number, award.award_short_name as award_name, hr.name as expend_org_name, 'Allocations' as allocation_switcher, 'CodeCombinationId' as accounting_switcher, 'Details' as detail_switcher, aerl.IMAGE_RECEIPT_REQUIRED_FLAG, aerl.RECEIPTS_ISSUE, 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 FROM AP_EXPENSE_REPORT_LINES_ALL aerl, ap_exp_report_dists_all ApExpReportDistEO, PA_PROJECTS_ALL proj, PA_TASKS task, GMS_AWARDS award, hr_organization_units hr WHERE ApExpReportDistEO.report_line_id(+) = aerl.report_line_id AND proj.project_id(+) = ApExpReportDistEO.project_id AND task.task_id(+) = ApExpReportDistEO.task_id AND award.award_id(+) = ApExpReportDistEO.award_id AND hr.organization_id(+) = ApExpReportDistEO.expenditure_organization_id AND allocation_split_code is null UNION ALL SELECT aerl.REPORT_HEADER_ID, aerl.LAST_UPDATE_DATE, aerl.LAST_UPDATED_BY, aerl.ITEM_DESCRIPTION, aerl.SET_OF_BOOKS_ID, aerl.AMOUNT, aerl.CURRENCY_CODE, aerl.VAT_CODE, aerl.LINE_TYPE_LOOKUP_CODE, aerl.LAST_UPDATE_LOGIN, aerl.CREATION_DATE, aerl.CREATED_BY, aerl.DISTRIBUTION_LINE_NUMBER, aerl.REFERENCE_1, aerl.REFERENCE_2, aerl.AWT_GROUP_ID, aerl.ORG_ID, aerl.RECEIPT_VERIFIED_FLAG, aerl.JUSTIFICATION_REQUIRED_FLAG, aerl.REPORT_LINE_ID, aerl.RECEIPT_REQUIRED_FLAG, aerl.RECEIPT_MISSING_FLAG, aerl.JUSTIFICATION, aerl.START_EXPENSE_DATE, aerl.END_EXPENSE_DATE, aerl.RECEIPT_CURRENCY_CODE, aerl.RECEIPT_CONVERSION_RATE, aerl.DAILY_AMOUNT, aerl.RECEIPT_CURRENCY_AMOUNT, aerl.WEB_PARAMETER_ID, aerl.AMOUNT_INCLUDES_TAX_FLAG, aerl.ADJUSTMENT_REASON, aerl.POLICY_SHORTPAY_FLAG, aerl.TAX_CODE_ID, aerl.MERCHANT_DOCUMENT_NUMBER, aerl.MERCHANT_NAME, aerl.MERCHANT_REFERENCE, aerl.MERCHANT_TAX_REG_NUMBER, aerl.MERCHANT_TAXPAYER_ID, aerl.CREDIT_CARD_TRX_ID, aerl.COMPANY_PREPAID_INVOICE_ID, aerl.NUMBER_PEOPLE, aerl.LOCATION_ID, aerl.ATTENDEES, aerl.NUMBER_ATTENDEES, aerl.LOCATION_TO_ID, aerl.ADJUSTMENT_REASON_CODE, aerl.EXPENDITURE_TYPE, aerl.itemization_parent_id, aerl.SUBMITTED_AMOUNT, aerl.allocation_split_code, decode(aerl.itemization_parent_id, -1, null, AP_WEB_AUDIT_UTILS.get_line_status(aerl.REPORT_HEADER_ID, aerl.DISTRIBUTION_LINE_NUMBER)) line_status, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_amount, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_rate(aerl.report_header_id, aerl.distribution_line_number)) allowable_exchange_rate, AP_WEB_AUDIT_UTILS.get_awt_group_info(aerl.AWT_GROUP_ID, 'name','VARCHAR2') awt_group_disp, aerl.AMOUNT orig_amount, aerl.RECEIPT_CONVERSION_RATE orig_receipt_conversion_rate, decode(aerl.itemization_parent_id, null, decode(AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id), 'Y', (select DECODE(trx.CATEGORY, 'BUSINESS',trx.transaction_amount, to_number(null)) from AP_CREDIT_CARD_TRXNS_all trx where trx.trx_id = aerl.credit_card_trx_id ) ), aerl.receipt_currency_amount) itemized_receipt_amount, DECODE(aerl.CREDIT_CARD_TRX_ID, null,'N', 'Y') cc_trx, AP_WEB_AUDIT_UTILS.get_expense_item_info(aerl.web_parameter_id,'category_code','VARCHAR2') category_code, AP_WEB_AUDIT_UTILS.get_concat_desc_flex(aerl.report_header_id, aerl.distribution_line_number) concat_desc_flex, DECODE(category_code, 'PER_DIEM', to_char(start_expense_date, 'HH24:MI:SS'), to_char(null)) start_time, DECODE(category_code, 'PER_DIEM', to_char(end_expense_date, 'HH24:MI:SS'), to_char(null)) end_time, DECODE(category_code, 'PER_DIEM', NVL(num_pdm_days1,0) + NVL(num_pdm_days2,0) + NVL(num_pdm_days3,0), NVL(end_expense_date - start_expense_date,0)+1) number_of_days, DECODE(category_code, 'PER_DIEM', AP_WEB_AUDIT_UTILS.get_average_pdm_rate(aerl.report_header_id, aerl.distribution_line_number), to_number(null)) avg_daily_rate, DECODE(category_code, 'PER_DIEM', NVL(deduction_addition_amt1,0) + NVL(deduction_addition_amt2,0) + NVL(deduction_addition_amt3,0), to_number(null)) deductions_and_additions, aerl.location location_expense_incurred, AP_WEB_POLICY_UTILS.get_location(location_to_id) destination_location, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_MILEAGE_RATE_ADJUSTED', aerl.MILEAGE_RATE_ADJUSTED_FLAG) mileage_rate_adjusted, AP_WEB_AUDIT_UTILS.get_allowable_daily_sum(aerl.report_header_id, aerl.distribution_line_number) allowable_daily_sum, AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id) personal_expense, AP_WEB_AUDIT_UTILS.is_rounding_line(aerl.web_parameter_id) rounding_line, AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_name, aerl.WEB_PARAMETER_ID orig_web_parameter_id, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_cc_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_cc_amount, DECODE(aerl.itemization_parent_id, null, 'N', -1, DECODE(aerl.credit_card_trx_id, null, 'N', AP_WEB_AUDIT_UTILS.has_default_cc_itemization(aerl.credit_card_trx_id)), 'N') provider_itemization, aerl.VAT_CODE vat_code_disp, decode(AP_WEB_AUDIT_UTILS.isAttendeeAvailable(aerl.report_line_id), 'Y', 'A', 'B') attendee_info, null, null, null, null, null, null, null, 'Allocations' as allocation_switcher, 'CodeCombinationId' as accounting_switcher, 'Details' as detail_switcher, aerl.IMAGE_RECEIPT_REQUIRED_FLAG, aerl.RECEIPTS_ISSUE, 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 FROM AP_EXPENSE_REPORT_LINES_ALL aerl WHERE allocation_split_code is not null) QRSLT WHERE :1 = REPORT_HEADER_ID AND (itemization_parent_id is null OR itemization_parent_id = -1) ORDER BY distribution_line_number

Oracle Community | 929077 | 4 years ago
  1. 0

    VO Extension Error

    Oracle Community | 4 years ago | 929077
    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT aerl.REPORT_HEADER_ID, aerl.LAST_UPDATE_DATE, aerl.LAST_UPDATED_BY, aerl.ITEM_DESCRIPTION, aerl.SET_OF_BOOKS_ID, aerl.AMOUNT, aerl.CURRENCY_CODE, aerl.VAT_CODE, aerl.LINE_TYPE_LOOKUP_CODE, aerl.LAST_UPDATE_LOGIN, aerl.CREATION_DATE, aerl.CREATED_BY, aerl.DISTRIBUTION_LINE_NUMBER, aerl.REFERENCE_1, aerl.REFERENCE_2, aerl.AWT_GROUP_ID, aerl.ORG_ID, aerl.RECEIPT_VERIFIED_FLAG, aerl.JUSTIFICATION_REQUIRED_FLAG, aerl.REPORT_LINE_ID, aerl.RECEIPT_REQUIRED_FLAG, aerl.RECEIPT_MISSING_FLAG, aerl.JUSTIFICATION, aerl.START_EXPENSE_DATE, aerl.END_EXPENSE_DATE, aerl.RECEIPT_CURRENCY_CODE, aerl.RECEIPT_CONVERSION_RATE, aerl.DAILY_AMOUNT, aerl.RECEIPT_CURRENCY_AMOUNT, aerl.WEB_PARAMETER_ID, aerl.AMOUNT_INCLUDES_TAX_FLAG, aerl.ADJUSTMENT_REASON, aerl.POLICY_SHORTPAY_FLAG, aerl.TAX_CODE_ID, aerl.MERCHANT_DOCUMENT_NUMBER, aerl.MERCHANT_NAME, aerl.MERCHANT_REFERENCE, aerl.MERCHANT_TAX_REG_NUMBER, aerl.MERCHANT_TAXPAYER_ID, aerl.CREDIT_CARD_TRX_ID, aerl.COMPANY_PREPAID_INVOICE_ID, aerl.NUMBER_PEOPLE, aerl.LOCATION_ID, aerl.ATTENDEES, aerl.NUMBER_ATTENDEES, aerl.LOCATION_TO_ID, aerl.ADJUSTMENT_REASON_CODE, aerl.EXPENDITURE_TYPE, aerl.itemization_parent_id, aerl.SUBMITTED_AMOUNT, aerl.allocation_split_code, decode(aerl.itemization_parent_id, -1, null, AP_WEB_AUDIT_UTILS.get_line_status(aerl.REPORT_HEADER_ID, aerl.DISTRIBUTION_LINE_NUMBER)) line_status, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_amount, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_rate(aerl.report_header_id, aerl.distribution_line_number)) allowable_exchange_rate, AP_WEB_AUDIT_UTILS.get_awt_group_info(aerl.AWT_GROUP_ID, 'name','VARCHAR2') awt_group_disp, aerl.AMOUNT orig_amount, aerl.RECEIPT_CONVERSION_RATE orig_receipt_conversion_rate, decode(aerl.itemization_parent_id, null, decode(AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id), 'Y', (select DECODE(trx.CATEGORY, 'BUSINESS',trx.transaction_amount, to_number(null)) from AP_CREDIT_CARD_TRXNS_all trx where trx.trx_id = aerl.credit_card_trx_id ) ), aerl.receipt_currency_amount) itemized_receipt_amount, DECODE(aerl.CREDIT_CARD_TRX_ID, null,'N', 'Y') cc_trx, AP_WEB_AUDIT_UTILS.get_expense_item_info(aerl.web_parameter_id,'category_code','VARCHAR2') category_code, AP_WEB_AUDIT_UTILS.get_concat_desc_flex(aerl.report_header_id, aerl.distribution_line_number) concat_desc_flex, DECODE(category_code, 'PER_DIEM', to_char(start_expense_date, 'HH24:MI:SS'), to_char(null)) start_time, DECODE(category_code, 'PER_DIEM', to_char(end_expense_date, 'HH24:MI:SS'), to_char(null)) end_time, DECODE(category_code, 'PER_DIEM', NVL(num_pdm_days1,0) + NVL(num_pdm_days2,0) + NVL(num_pdm_days3,0), NVL(end_expense_date - start_expense_date,0)+1) number_of_days, DECODE(category_code, 'PER_DIEM', AP_WEB_AUDIT_UTILS.get_average_pdm_rate(aerl.report_header_id, aerl.distribution_line_number), to_number(null)) avg_daily_rate, DECODE(category_code, 'PER_DIEM', NVL(deduction_addition_amt1,0) + NVL(deduction_addition_amt2,0) + NVL(deduction_addition_amt3,0), to_number(null)) deductions_and_additions, aerl.location location_expense_incurred, AP_WEB_POLICY_UTILS.get_location(location_to_id) destination_location, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_MILEAGE_RATE_ADJUSTED', aerl.MILEAGE_RATE_ADJUSTED_FLAG) mileage_rate_adjusted, AP_WEB_AUDIT_UTILS.get_allowable_daily_sum(aerl.report_header_id, aerl.distribution_line_number) allowable_daily_sum, AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id) personal_expense, AP_WEB_AUDIT_UTILS.is_rounding_line(aerl.web_parameter_id) rounding_line, AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_name, aerl.WEB_PARAMETER_ID orig_web_parameter_id, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_cc_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_cc_amount, DECODE(aerl.itemization_parent_id, null, 'N', -1, DECODE(aerl.credit_card_trx_id, null, 'N', AP_WEB_AUDIT_UTILS.has_default_cc_itemization(aerl.credit_card_trx_id)), 'N') provider_itemization, aerl.VAT_CODE vat_code_disp, decode(AP_WEB_AUDIT_UTILS.isAttendeeAvailable(aerl.report_line_id), 'Y', 'A', 'B') attendee_info, proj.segment1 project_number, proj.name project_name, task.task_number, task.task_name, award.award_number, award.award_short_name as award_name, hr.name as expend_org_name, 'Allocations' as allocation_switcher, 'CodeCombinationId' as accounting_switcher, 'Details' as detail_switcher, aerl.IMAGE_RECEIPT_REQUIRED_FLAG, aerl.RECEIPTS_ISSUE, 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 FROM AP_EXPENSE_REPORT_LINES_ALL aerl, ap_exp_report_dists_all ApExpReportDistEO, PA_PROJECTS_ALL proj, PA_TASKS task, GMS_AWARDS award, hr_organization_units hr WHERE ApExpReportDistEO.report_line_id(+) = aerl.report_line_id AND proj.project_id(+) = ApExpReportDistEO.project_id AND task.task_id(+) = ApExpReportDistEO.task_id AND award.award_id(+) = ApExpReportDistEO.award_id AND hr.organization_id(+) = ApExpReportDistEO.expenditure_organization_id AND allocation_split_code is null UNION ALL SELECT aerl.REPORT_HEADER_ID, aerl.LAST_UPDATE_DATE, aerl.LAST_UPDATED_BY, aerl.ITEM_DESCRIPTION, aerl.SET_OF_BOOKS_ID, aerl.AMOUNT, aerl.CURRENCY_CODE, aerl.VAT_CODE, aerl.LINE_TYPE_LOOKUP_CODE, aerl.LAST_UPDATE_LOGIN, aerl.CREATION_DATE, aerl.CREATED_BY, aerl.DISTRIBUTION_LINE_NUMBER, aerl.REFERENCE_1, aerl.REFERENCE_2, aerl.AWT_GROUP_ID, aerl.ORG_ID, aerl.RECEIPT_VERIFIED_FLAG, aerl.JUSTIFICATION_REQUIRED_FLAG, aerl.REPORT_LINE_ID, aerl.RECEIPT_REQUIRED_FLAG, aerl.RECEIPT_MISSING_FLAG, aerl.JUSTIFICATION, aerl.START_EXPENSE_DATE, aerl.END_EXPENSE_DATE, aerl.RECEIPT_CURRENCY_CODE, aerl.RECEIPT_CONVERSION_RATE, aerl.DAILY_AMOUNT, aerl.RECEIPT_CURRENCY_AMOUNT, aerl.WEB_PARAMETER_ID, aerl.AMOUNT_INCLUDES_TAX_FLAG, aerl.ADJUSTMENT_REASON, aerl.POLICY_SHORTPAY_FLAG, aerl.TAX_CODE_ID, aerl.MERCHANT_DOCUMENT_NUMBER, aerl.MERCHANT_NAME, aerl.MERCHANT_REFERENCE, aerl.MERCHANT_TAX_REG_NUMBER, aerl.MERCHANT_TAXPAYER_ID, aerl.CREDIT_CARD_TRX_ID, aerl.COMPANY_PREPAID_INVOICE_ID, aerl.NUMBER_PEOPLE, aerl.LOCATION_ID, aerl.ATTENDEES, aerl.NUMBER_ATTENDEES, aerl.LOCATION_TO_ID, aerl.ADJUSTMENT_REASON_CODE, aerl.EXPENDITURE_TYPE, aerl.itemization_parent_id, aerl.SUBMITTED_AMOUNT, aerl.allocation_split_code, decode(aerl.itemization_parent_id, -1, null, AP_WEB_AUDIT_UTILS.get_line_status(aerl.REPORT_HEADER_ID, aerl.DISTRIBUTION_LINE_NUMBER)) line_status, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_amount, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_rate(aerl.report_header_id, aerl.distribution_line_number)) allowable_exchange_rate, AP_WEB_AUDIT_UTILS.get_awt_group_info(aerl.AWT_GROUP_ID, 'name','VARCHAR2') awt_group_disp, aerl.AMOUNT orig_amount, aerl.RECEIPT_CONVERSION_RATE orig_receipt_conversion_rate, decode(aerl.itemization_parent_id, null, decode(AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id), 'Y', (select DECODE(trx.CATEGORY, 'BUSINESS',trx.transaction_amount, to_number(null)) from AP_CREDIT_CARD_TRXNS_all trx where trx.trx_id = aerl.credit_card_trx_id ) ), aerl.receipt_currency_amount) itemized_receipt_amount, DECODE(aerl.CREDIT_CARD_TRX_ID, null,'N', 'Y') cc_trx, AP_WEB_AUDIT_UTILS.get_expense_item_info(aerl.web_parameter_id,'category_code','VARCHAR2') category_code, AP_WEB_AUDIT_UTILS.get_concat_desc_flex(aerl.report_header_id, aerl.distribution_line_number) concat_desc_flex, DECODE(category_code, 'PER_DIEM', to_char(start_expense_date, 'HH24:MI:SS'), to_char(null)) start_time, DECODE(category_code, 'PER_DIEM', to_char(end_expense_date, 'HH24:MI:SS'), to_char(null)) end_time, DECODE(category_code, 'PER_DIEM', NVL(num_pdm_days1,0) + NVL(num_pdm_days2,0) + NVL(num_pdm_days3,0), NVL(end_expense_date - start_expense_date,0)+1) number_of_days, DECODE(category_code, 'PER_DIEM', AP_WEB_AUDIT_UTILS.get_average_pdm_rate(aerl.report_header_id, aerl.distribution_line_number), to_number(null)) avg_daily_rate, DECODE(category_code, 'PER_DIEM', NVL(deduction_addition_amt1,0) + NVL(deduction_addition_amt2,0) + NVL(deduction_addition_amt3,0), to_number(null)) deductions_and_additions, aerl.location location_expense_incurred, AP_WEB_POLICY_UTILS.get_location(location_to_id) destination_location, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_MILEAGE_RATE_ADJUSTED', aerl.MILEAGE_RATE_ADJUSTED_FLAG) mileage_rate_adjusted, AP_WEB_AUDIT_UTILS.get_allowable_daily_sum(aerl.report_header_id, aerl.distribution_line_number) allowable_daily_sum, AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id) personal_expense, AP_WEB_AUDIT_UTILS.is_rounding_line(aerl.web_parameter_id) rounding_line, AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_name, aerl.WEB_PARAMETER_ID orig_web_parameter_id, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_cc_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_cc_amount, DECODE(aerl.itemization_parent_id, null, 'N', -1, DECODE(aerl.credit_card_trx_id, null, 'N', AP_WEB_AUDIT_UTILS.has_default_cc_itemization(aerl.credit_card_trx_id)), 'N') provider_itemization, aerl.VAT_CODE vat_code_disp, decode(AP_WEB_AUDIT_UTILS.isAttendeeAvailable(aerl.report_line_id), 'Y', 'A', 'B') attendee_info, null, null, null, null, null, null, null, 'Allocations' as allocation_switcher, 'CodeCombinationId' as accounting_switcher, 'Details' as detail_switcher, aerl.IMAGE_RECEIPT_REQUIRED_FLAG, aerl.RECEIPTS_ISSUE, 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 FROM AP_EXPENSE_REPORT_LINES_ALL aerl WHERE allocation_split_code is not null) QRSLT WHERE :1 = REPORT_HEADER_ID AND (itemization_parent_id is null OR itemization_parent_id = -1) ORDER BY distribution_line_number
  2. 0

    iExpense - Error Searching for Expense Report

    Oracle Community | 3 years ago | LMartin
    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT aerh.REPORT_HEADER_ID, aerh.EMPLOYEE_ID, aerh.WEEK_END_DATE, aerh.CREATION_DATE, aerh.CREATED_BY, aerh.LAST_UPDATE_DATE, aerh.LAST_UPDATED_BY, aerh.VOUCHNO, aerh.TOTAL, aerh.VENDOR_ID, aerh.VENDOR_SITE_ID, aerh.REFERENCE_1, aerh.REFERENCE_2, aerh.INVOICE_NUM, aerh.EXPENSE_REPORT_ID, aerh.SET_OF_BOOKS_ID, aerh.SOURCE, aerh.EMPLOYEE_CCID, aerh.DESCRIPTION, aerh.REJECT_CODE, aerh.HOLD_LOOKUP_CODE, aerh.DEFAULT_CURRENCY_CODE, aerh.LAST_UPDATE_LOGIN, aerh.VOUCHER_NUM, aerh.AWT_GROUP_ID, aerh.ORG_ID, aerh.WORKFLOW_APPROVED_FLAG, aerh.CORE_WF_STATUS_FLAG, aerh.BOTHPAY_PARENT_ID, aerh.SHORTPAY_PARENT_ID, aerh.PAID_ON_BEHALF_EMPLOYEE_ID, aerh.AMT_DUE_CCARD_COMPANY, aerh.AMT_DUE_EMPLOYEE, aerh.DEFAULT_RECEIPT_CURRENCY_CODE, aerh.MULTIPLE_CURRENCIES_FLAG, aerh.REPORT_FILING_NUMBER, aerh.RECEIPTS_RECEIVED_DATE, aerh.AUDIT_CODE, aerh.REPORT_SUBMITTED_DATE, aerh.LAST_AUDITED_BY, aerh.RETURN_REASON_CODE, aerh.RETURN_INSTRUCTION, wf.full_name employee_name, decode(wf.current_npw_flag, 'Y', wf.npw_number, wf.employee_number) employee_number, decode(AERH.WORKFLOW_APPROVED_FLAG, 'Y','Y', 'A','Y', 'M','Y', 'N') mgmt_reviewed_flag, decode(AERH.WORKFLOW_APPROVED_FLAG, 'Y','Y', 'A','Y', 'P','Y', DECODE(aerh.AUDIT_CODE, 'AUTO_APPROVE', 'Y', 'N')) ap_reviewed_flag, AP_WEB_AUDIT_UTILS.get_audit_reason(aerh.REPORT_HEADER_ID) AUDIT_REASON, ou.name ORG_NAME, ap_web_expense_form.get_num_total_violations(aerh.report_header_id) num_total_violations, ap_web_expense_form.get_num_violation_lines(aerh.report_header_id) num_violation_lines, DECODE(AI.Payment_status_flag,'Y', 'Y', 'N') status_paid, DECODE(AI.INVOICE_NUM,null, 'N', 'Y') status_invoice_created, DECODE(AI.Payment_status_flag,'P', 'Y', 'N') status_partially_paid, AP_WEB_POLICY_UTILS.get_lookup_meaning('EXPENSE REPORT STATUS', decode( AI.cancelled_date, NULL, aerh.expense_status_code, 'CANCELLED' )) report_status, decode( AI.cancelled_date, NULL, aerh.expense_status_code, 'CANCELLED' ) report_status_code, AP_WEB_AUDIT_UTILS.get_flex_structure_code(aerh.org_id) key_flex_structure_code, DECODE(aerh.AUDIT_CODE,'AUTO_APPROVE', 'Y', DECODE(aerh.workflow_approved_flag,'A','Y','N')) auto_approved_flag, wf.email_address email_address, q.auditor_id orig_auditor_id, AP_WEB_AUDIT_UTILS.get_auditor_name(q.auditor_id) auditor_name, q.auditor_id auditor_id, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_TYPES',NVL(aerh.AUDIT_CODE,'AUDIT')) audit_type, aerh.RECEIPTS_RECEIVED_DATE ORIG_RECEIPTS_RECEIVED_DATE, AP_WEB_AUDIT_UTILS.get_audit_rule_info(aerh.org_id,'AUDIT_LIST','receipt_delay_days','NUMBER') receipt_delay_days, aerh.REPORT_SUBMITTED_DATE + to_number(AP_WEB_AUDIT_UTILS.get_audit_rule_info(aerh.org_id,'AUDIT_LIST','receipt_delay_days','NUMBER')) receipt_delay_date, NVL(aerh.AUDIT_CODE,'AUDIT') audit_code_disp, decode(aerh.AUDIT_CODE, 'PAPERLESS_AUDIT', DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'N','Y', 'M','Y', NULL,'Y', 'N'), 'RECEIPT_BASED', DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'N','Y', 'M','Y', NULL,'Y', 'N'), 'N') requires_paperless_audit, AP_WEB_AUDIT_UTILS.is_employee_active(aerh.employee_id, aerh.org_id) employee_active, AP_WEB_AUDIT_UTILS.get_user_name(aerh.LAST_AUDITED_BY) last_audited_by_name, AP_WEB_AUDIT_UTILS.get_user_name(aerh.LAST_UPDATED_BY) last_updated_by_name, ap_web_audit_utils.get_lookup_description('OIE_AUD_RETURN_REASONS', aerh.return_reason_code) return_reason, ep.note_language_code, last_auditor.user_name last_auditor_user_name, DECODE(usr.employee_id, aerh.employee_id, 'Y', 'N') auditor_own_report, AP_WEB_POLICY_UTILS.get_lookup_meaning('RECEIPT_STATUS', aerh.RECEIPTS_STATUS) receipt_package_status, DECODE(aerh.HOLDING_REPORT_HEADER_ID, null, 'N','Y') hold_status, aerh.RECEIPTS_STATUS, aerh.attribute_category, aerh.attribute1, aerh.attribute2, aerh.attribute3, aerh.attribute4, aerh.attribute5, aerh.attribute6, aerh.attribute7, aerh.attribute8, aerh.attribute9, aerh.attribute10, aerh.attribute11, aerh.attribute12, aerh.attribute13, aerh.attribute14, aerh.attribute15, aerh.expense_status_code, aerh.expense_last_status_date, aerh.holding_report_header_id, decode(aerh.source,'Both Pay',pv.vendor_name, decode(aerh.source,'CREDIT CARD',pv.vendor_name,null)) CREDIT_CARD_PROVIDER_NAME, aerh.prepay_num, aerh.advances_justification, aerh.MAXIMUM_AMOUNT_TO_APPLY, aerh.ADVANCE_INVOICE_TO_APPLY, aerh.PREPAY_GL_DATE, aerh.PREPAY_DIST_NUM, aerh.PAYMENT_CURRENCY_CODE, aerh.FLEX_CONCATENATED, aerh.apply_advances_default, aerh.image_receipts_status, aerh.image_receipts_received_date, aerh.MISSING_IMG_JUST, DECODE(q.auditor_id, null, 'N', 'Y') auditor_assigned FROM AP_EXPENSE_REPORT_HEADERS aerh, AP_INVOICES_ALL AI, HR_ORGANIZATION_UNITS ou, AP_AUD_QUEUES q, FND_USER usr, AP_EXPENSE_PARAMS_ALL ep, PER_PEOPLE_F wf, FND_USER last_auditor, PO_VENDORS PV WHERE AI.INVOICE_ID(+) = AERH.VOUCHNO and ou.organization_id = aerh.org_id and q.expense_report_id(+) = aerh.report_header_id and aerh.source in ('NonValidatedWebExpense', 'WebExpense', 'SelfService', 'CREDIT CARD', 'Both Pay') and (aerh.Source <> 'NonValidatedWebExpense' OR aerh.Workflow_approved_flag IS NULL OR aerh.Workflow_approved_flag in ('R','W','T')) and usr.user_id(+) = fnd_global.user_id and ep.org_id(+) = aerh.org_id and wf.person_id = aerh.employee_id and trunc(sysdate) between wf.effective_start_date and wf.effective_end_date and last_auditor.user_id(+) = aerh.LAST_AUDITED_BY and aerh.vendor_id = pv.vendor_id(+)) QRSLT WHERE ((UPPER(INVOICE_NUM) = :1 AND (INVOICE_NUM like :2 OR INVOICE_NUM like :3 OR INVOICE_NUM like :4 OR INVOICE_NUM like :5)))
  3. 0

    Need help to solve the NULL Pointer Exception in a newly extended View Object

    Oracle Community | 2 years ago | sjain26
    oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.NullPointerException, msg=null
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    OAF Personalization exception

    Oracle Community | 2 months ago | 2906075
    oracle.apps.fnd.framework.OAException: oracle.jbo.AttributeLoadException: JBO-27021: Failed to load CustomDatum value at index 113 with java object of type oracle.jbo.domain.Date due to java.sql.SQLException.
  6. 0

    oracle.jbo.SQLStmtException:JBO-27122:SQLerror during statement preparation

    Oracle Community | 6 years ago | 837397
    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT OPMTL.ORG_PAYMENT_METHOD_NAME , OPM.ORG_PAYMENT_METHOD_ID , OPM.CURRENCY_CODE , OPM.DEFINED_BALANCE_ID , PTTL.PAYMENT_TYPE_NAME , PT.TERRITORY_CODE , PT.CATEGORY , PT.PRE_VALIDATION_REQUIRED , OPM.EFFECTIVE_END_DATE , OPM.EFFECTIVE_START_DATE , USG.PAYROLL_ID , OPM.BUSINESS_GROUP_ID , PEA.ID_FLEX_NUM , FC.NAME CURRENCY_NAME, PAY_PPMV4_SS.GET_PPM_COUNTRY(OPM.ORG_PAYMENT_METHOD_ID,OPM.BUSINESS_GROUP_ID + 0) PPM_COUNTRY_CODE, PAY_PPMV4_SS.GET_PPM_COUNTRY(OPM.ORG_PAYMENT_METHOD_ID,OPM.BUSINESS_GROUP_ID + 0,'Y') TERRITORY_SHORT_NAME, PAY_PPMV4_SS.GET_BANK_FLEXCODE(OPM.ORG_PAYMENT_METHOD_ID,OPM.BUSINESS_GROUP_ID + 0) BANK_FLEX_CODE FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL, PAY_PAYMENT_TYPES_TL PTTL, PAY_ORG_PAYMENT_METHODS_F OPM, PAY_ORG_PAY_METHOD_USAGES_F USG, PAY_PAYMENT_TYPES PT, PAY_EXTERNAL_ACCOUNTS PEA, FND_SESSIONS FF , FND_CURRENCIES_VL FC WHERE PT.PAYMENT_TYPE_ID = PTTL.PAYMENT_TYPE_ID AND PTTL.LANGUAGE = USERENV('LANG') AND OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID AND OPMTL.LANGUAGE = USERENV('LANG') AND FF.SESSION_ID = USERENV('SESSIONID') AND FF.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND FF.EFFECTIVE_DATE BETWEEN USG.EFFECTIVE_START_DATE AND USG.EFFECTIVE_END_DATE AND USG.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID AND OPM.PAYMENT_TYPE_ID = PT.PAYMENT_TYPE_ID AND OPM.CURRENCY_CODE = FC.CURRENCY_CODE(+) AND OPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID (+) AND USG.PAYROLL_ID = :1 AND OPM.BUSINESS_GROUP_ID + 0 = :2 AND :3 BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND OPM.DEFINED_BALANCE_ID IS NOT NULL) QRSLT WHERE (ORG_PAYMENT_METHOD_ID IN (837,977,133,279,274,417,297,226,324,271,183,390,396,429,217,144)

    2 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. oracle.apps.fnd.framework.OAException

      oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT aerl.REPORT_HEADER_ID, aerl.LAST_UPDATE_DATE, aerl.LAST_UPDATED_BY, aerl.ITEM_DESCRIPTION, aerl.SET_OF_BOOKS_ID, aerl.AMOUNT, aerl.CURRENCY_CODE, aerl.VAT_CODE, aerl.LINE_TYPE_LOOKUP_CODE, aerl.LAST_UPDATE_LOGIN, aerl.CREATION_DATE, aerl.CREATED_BY, aerl.DISTRIBUTION_LINE_NUMBER, aerl.REFERENCE_1, aerl.REFERENCE_2, aerl.AWT_GROUP_ID, aerl.ORG_ID, aerl.RECEIPT_VERIFIED_FLAG, aerl.JUSTIFICATION_REQUIRED_FLAG, aerl.REPORT_LINE_ID, aerl.RECEIPT_REQUIRED_FLAG, aerl.RECEIPT_MISSING_FLAG, aerl.JUSTIFICATION, aerl.START_EXPENSE_DATE, aerl.END_EXPENSE_DATE, aerl.RECEIPT_CURRENCY_CODE, aerl.RECEIPT_CONVERSION_RATE, aerl.DAILY_AMOUNT, aerl.RECEIPT_CURRENCY_AMOUNT, aerl.WEB_PARAMETER_ID, aerl.AMOUNT_INCLUDES_TAX_FLAG, aerl.ADJUSTMENT_REASON, aerl.POLICY_SHORTPAY_FLAG, aerl.TAX_CODE_ID, aerl.MERCHANT_DOCUMENT_NUMBER, aerl.MERCHANT_NAME, aerl.MERCHANT_REFERENCE, aerl.MERCHANT_TAX_REG_NUMBER, aerl.MERCHANT_TAXPAYER_ID, aerl.CREDIT_CARD_TRX_ID, aerl.COMPANY_PREPAID_INVOICE_ID, aerl.NUMBER_PEOPLE, aerl.LOCATION_ID, aerl.ATTENDEES, aerl.NUMBER_ATTENDEES, aerl.LOCATION_TO_ID, aerl.ADJUSTMENT_REASON_CODE, aerl.EXPENDITURE_TYPE, aerl.itemization_parent_id, aerl.SUBMITTED_AMOUNT, aerl.allocation_split_code, decode(aerl.itemization_parent_id, -1, null, AP_WEB_AUDIT_UTILS.get_line_status(aerl.REPORT_HEADER_ID, aerl.DISTRIBUTION_LINE_NUMBER)) line_status, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_amount, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_rate(aerl.report_header_id, aerl.distribution_line_number)) allowable_exchange_rate, AP_WEB_AUDIT_UTILS.get_awt_group_info(aerl.AWT_GROUP_ID, 'name','VARCHAR2') awt_group_disp, aerl.AMOUNT orig_amount, aerl.RECEIPT_CONVERSION_RATE orig_receipt_conversion_rate, decode(aerl.itemization_parent_id, null, decode(AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id), 'Y', (select DECODE(trx.CATEGORY, 'BUSINESS',trx.transaction_amount, to_number(null)) from AP_CREDIT_CARD_TRXNS_all trx where trx.trx_id = aerl.credit_card_trx_id ) ), aerl.receipt_currency_amount) itemized_receipt_amount, DECODE(aerl.CREDIT_CARD_TRX_ID, null,'N', 'Y') cc_trx, AP_WEB_AUDIT_UTILS.get_expense_item_info(aerl.web_parameter_id,'category_code','VARCHAR2') category_code, AP_WEB_AUDIT_UTILS.get_concat_desc_flex(aerl.report_header_id, aerl.distribution_line_number) concat_desc_flex, DECODE(category_code, 'PER_DIEM', to_char(start_expense_date, 'HH24:MI:SS'), to_char(null)) start_time, DECODE(category_code, 'PER_DIEM', to_char(end_expense_date, 'HH24:MI:SS'), to_char(null)) end_time, DECODE(category_code, 'PER_DIEM', NVL(num_pdm_days1,0) + NVL(num_pdm_days2,0) + NVL(num_pdm_days3,0), NVL(end_expense_date - start_expense_date,0)+1) number_of_days, DECODE(category_code, 'PER_DIEM', AP_WEB_AUDIT_UTILS.get_average_pdm_rate(aerl.report_header_id, aerl.distribution_line_number), to_number(null)) avg_daily_rate, DECODE(category_code, 'PER_DIEM', NVL(deduction_addition_amt1,0) + NVL(deduction_addition_amt2,0) + NVL(deduction_addition_amt3,0), to_number(null)) deductions_and_additions, aerl.location location_expense_incurred, AP_WEB_POLICY_UTILS.get_location(location_to_id) destination_location, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_MILEAGE_RATE_ADJUSTED', aerl.MILEAGE_RATE_ADJUSTED_FLAG) mileage_rate_adjusted, AP_WEB_AUDIT_UTILS.get_allowable_daily_sum(aerl.report_header_id, aerl.distribution_line_number) allowable_daily_sum, AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id) personal_expense, AP_WEB_AUDIT_UTILS.is_rounding_line(aerl.web_parameter_id) rounding_line, AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_name, aerl.WEB_PARAMETER_ID orig_web_parameter_id, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_cc_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_cc_amount, DECODE(aerl.itemization_parent_id, null, 'N', -1, DECODE(aerl.credit_card_trx_id, null, 'N', AP_WEB_AUDIT_UTILS.has_default_cc_itemization(aerl.credit_card_trx_id)), 'N') provider_itemization, aerl.VAT_CODE vat_code_disp, decode(AP_WEB_AUDIT_UTILS.isAttendeeAvailable(aerl.report_line_id), 'Y', 'A', 'B') attendee_info, proj.segment1 project_number, proj.name project_name, task.task_number, task.task_name, award.award_number, award.award_short_name as award_name, hr.name as expend_org_name, 'Allocations' as allocation_switcher, 'CodeCombinationId' as accounting_switcher, 'Details' as detail_switcher, aerl.IMAGE_RECEIPT_REQUIRED_FLAG, aerl.RECEIPTS_ISSUE, 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 FROM AP_EXPENSE_REPORT_LINES_ALL aerl, ap_exp_report_dists_all ApExpReportDistEO, PA_PROJECTS_ALL proj, PA_TASKS task, GMS_AWARDS award, hr_organization_units hr WHERE ApExpReportDistEO.report_line_id(+) = aerl.report_line_id AND proj.project_id(+) = ApExpReportDistEO.project_id AND task.task_id(+) = ApExpReportDistEO.task_id AND award.award_id(+) = ApExpReportDistEO.award_id AND hr.organization_id(+) = ApExpReportDistEO.expenditure_organization_id AND allocation_split_code is null UNION ALL SELECT aerl.REPORT_HEADER_ID, aerl.LAST_UPDATE_DATE, aerl.LAST_UPDATED_BY, aerl.ITEM_DESCRIPTION, aerl.SET_OF_BOOKS_ID, aerl.AMOUNT, aerl.CURRENCY_CODE, aerl.VAT_CODE, aerl.LINE_TYPE_LOOKUP_CODE, aerl.LAST_UPDATE_LOGIN, aerl.CREATION_DATE, aerl.CREATED_BY, aerl.DISTRIBUTION_LINE_NUMBER, aerl.REFERENCE_1, aerl.REFERENCE_2, aerl.AWT_GROUP_ID, aerl.ORG_ID, aerl.RECEIPT_VERIFIED_FLAG, aerl.JUSTIFICATION_REQUIRED_FLAG, aerl.REPORT_LINE_ID, aerl.RECEIPT_REQUIRED_FLAG, aerl.RECEIPT_MISSING_FLAG, aerl.JUSTIFICATION, aerl.START_EXPENSE_DATE, aerl.END_EXPENSE_DATE, aerl.RECEIPT_CURRENCY_CODE, aerl.RECEIPT_CONVERSION_RATE, aerl.DAILY_AMOUNT, aerl.RECEIPT_CURRENCY_AMOUNT, aerl.WEB_PARAMETER_ID, aerl.AMOUNT_INCLUDES_TAX_FLAG, aerl.ADJUSTMENT_REASON, aerl.POLICY_SHORTPAY_FLAG, aerl.TAX_CODE_ID, aerl.MERCHANT_DOCUMENT_NUMBER, aerl.MERCHANT_NAME, aerl.MERCHANT_REFERENCE, aerl.MERCHANT_TAX_REG_NUMBER, aerl.MERCHANT_TAXPAYER_ID, aerl.CREDIT_CARD_TRX_ID, aerl.COMPANY_PREPAID_INVOICE_ID, aerl.NUMBER_PEOPLE, aerl.LOCATION_ID, aerl.ATTENDEES, aerl.NUMBER_ATTENDEES, aerl.LOCATION_TO_ID, aerl.ADJUSTMENT_REASON_CODE, aerl.EXPENDITURE_TYPE, aerl.itemization_parent_id, aerl.SUBMITTED_AMOUNT, aerl.allocation_split_code, decode(aerl.itemization_parent_id, -1, null, AP_WEB_AUDIT_UTILS.get_line_status(aerl.REPORT_HEADER_ID, aerl.DISTRIBUTION_LINE_NUMBER)) line_status, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_amount, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_rate(aerl.report_header_id, aerl.distribution_line_number)) allowable_exchange_rate, AP_WEB_AUDIT_UTILS.get_awt_group_info(aerl.AWT_GROUP_ID, 'name','VARCHAR2') awt_group_disp, aerl.AMOUNT orig_amount, aerl.RECEIPT_CONVERSION_RATE orig_receipt_conversion_rate, decode(aerl.itemization_parent_id, null, decode(AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id), 'Y', (select DECODE(trx.CATEGORY, 'BUSINESS',trx.transaction_amount, to_number(null)) from AP_CREDIT_CARD_TRXNS_all trx where trx.trx_id = aerl.credit_card_trx_id ) ), aerl.receipt_currency_amount) itemized_receipt_amount, DECODE(aerl.CREDIT_CARD_TRX_ID, null,'N', 'Y') cc_trx, AP_WEB_AUDIT_UTILS.get_expense_item_info(aerl.web_parameter_id,'category_code','VARCHAR2') category_code, AP_WEB_AUDIT_UTILS.get_concat_desc_flex(aerl.report_header_id, aerl.distribution_line_number) concat_desc_flex, DECODE(category_code, 'PER_DIEM', to_char(start_expense_date, 'HH24:MI:SS'), to_char(null)) start_time, DECODE(category_code, 'PER_DIEM', to_char(end_expense_date, 'HH24:MI:SS'), to_char(null)) end_time, DECODE(category_code, 'PER_DIEM', NVL(num_pdm_days1,0) + NVL(num_pdm_days2,0) + NVL(num_pdm_days3,0), NVL(end_expense_date - start_expense_date,0)+1) number_of_days, DECODE(category_code, 'PER_DIEM', AP_WEB_AUDIT_UTILS.get_average_pdm_rate(aerl.report_header_id, aerl.distribution_line_number), to_number(null)) avg_daily_rate, DECODE(category_code, 'PER_DIEM', NVL(deduction_addition_amt1,0) + NVL(deduction_addition_amt2,0) + NVL(deduction_addition_amt3,0), to_number(null)) deductions_and_additions, aerl.location location_expense_incurred, AP_WEB_POLICY_UTILS.get_location(location_to_id) destination_location, AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_MILEAGE_RATE_ADJUSTED', aerl.MILEAGE_RATE_ADJUSTED_FLAG) mileage_rate_adjusted, AP_WEB_AUDIT_UTILS.get_allowable_daily_sum(aerl.report_header_id, aerl.distribution_line_number) allowable_daily_sum, AP_WEB_AUDIT_UTILS.is_personal_expense(aerl.web_parameter_id) personal_expense, AP_WEB_AUDIT_UTILS.is_rounding_line(aerl.web_parameter_id) rounding_line, AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_name, aerl.WEB_PARAMETER_ID orig_web_parameter_id, decode(aerl.itemization_parent_id, -1, to_number(null), AP_WEB_AUDIT_UTILS.get_allowable_cc_amount(aerl.report_header_id, aerl.distribution_line_number)) allowable_cc_amount, DECODE(aerl.itemization_parent_id, null, 'N', -1, DECODE(aerl.credit_card_trx_id, null, 'N', AP_WEB_AUDIT_UTILS.has_default_cc_itemization(aerl.credit_card_trx_id)), 'N') provider_itemization, aerl.VAT_CODE vat_code_disp, decode(AP_WEB_AUDIT_UTILS.isAttendeeAvailable(aerl.report_line_id), 'Y', 'A', 'B') attendee_info, null, null, null, null, null, null, null, 'Allocations' as allocation_switcher, 'CodeCombinationId' as accounting_switcher, 'Details' as detail_switcher, aerl.IMAGE_RECEIPT_REQUIRED_FLAG, aerl.RECEIPTS_ISSUE, 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 FROM AP_EXPENSE_REPORT_LINES_ALL aerl WHERE allocation_split_code is not null) QRSLT WHERE :1 = REPORT_HEADER_ID AND (itemization_parent_id is null OR itemization_parent_id = -1) ORDER BY distribution_line_number

      at oracle.apps.fnd.framework.OAException.wrapperException()
    2. oracle.apps.fnd
      OAApplicationModuleImpl.invokeMethod
      1. oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
      2. oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
      3. oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
      4. oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
      5. oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
      6. oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:762)
      6 frames
    3. oracle.apps.ap
      AuditPageCO.processRequest
      1. oracle.apps.ap.oie.audit.webui.AuditPageCO.processRequest(AuditPageCO.java:156)
      1 frame
    4. oracle.apps.fnd
      OAPageBean.preparePage
      1. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:600)
      2. oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
      3. oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1182)
      4. oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
      5. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968)
      6. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935)
      7. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659)
      8. oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
      9. oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
      10. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:968)
      11. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:935)
      12. oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:659)
      13. oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
      14. oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
      15. oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2607)
      16. oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1940)
      17. oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
      18. oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
      18 frames