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
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  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 | 5 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