11i - GL Journal Inquiry Drilldown Details to Projects encounters slow performance (Doc ID 1507796.1)

Last updated on AUGUST 01, 2016

Applies to:

Oracle Project Costing - Version 11.5.10.2 and later
Information in this document applies to any platform.
*** Checked for relevance 16-Oct-2014 ***

Symptoms

The GL Drilldown to Projects Journals has slow performance.  The most expensive query is:


SELECT TRX_CLASS_NAME,TRX_TYPE_NAME,TRX_NUMBER_DISPLAYED,TRX_DATE,COMMENTS,
  DOC_SEQUENCE_NAME,DOC_SEQUENCE_VALUE,ACCT_LINE_TYPE_NAME,CURRENCY_CODE,
  ENTERED_DR,ENTERED_CR,ACCOUNTED_DR,ACCOUNTED_CR,TAXABLE_ENTERED_DR,
  TAXABLE_ENTERED_CR,TAXABLE_ACCOUNTED_DR,TAXABLE_ACCOUNTED_CR,
  CURRENCY_CONVERSION_DATE,CURRENCY_USER_CONVERSION_TYPE,
  CURRENCY_CONVERSION_RATE,THIRD_PARTY_NAME,THIRD_PARTY_NUMBER,
  THIRD_PARTY_SUB_NAME,ACCOUNTING_DATE,GL_TRANSFER_STATUS_NAME,
  TRANSFER_SYSTEM_NAME,GL_TRANSFER_FROM_TO_NAME,ACCOUNTING_COMPLETE_NAME,
  ACCOUNTING_COMPLETE,AE_LINE_REFERENCE,TAX_CODE,ACCOUNTING_RULE_NAME,
  TAX_EXEMPT_NUMBER,TRX_LINE_TYPE_NAME,TRX_LINE_NUMBER,TRX_DETAIL_LINE_NUMBER,
  TRX_QUANTITY,SALES_ORDER_NUMBER,SALESREP_NAME,TAX_RATE,UNIT_SELLING_PRICE,
  TRX_UOM,TRX_SOURCE_NAME,ASSET_NUMBER,ASSET_DESCRIPTION,ASSET_BOOK_TYPE_CODE,
  ACCOUNTING_EVENT_NUMBER,ACCOUNTING_EVENT_TYPE,ACCOUNTING_EVENT_TYPE_NAME,
  ACCOUNTING_LINE_NUMBER,AEH_ACCOUNTING_ERROR_NAME,AEL_ACCOUNTING_ERROR_NAME,
  USER_JE_CATEGORY_NAME,TRANSFER_STATUS_DETAIL_NAME,APPLICATION_DATE,
  APPLIED_TO_TRX_HDR_CURRENCY,APPLIED_TO_TRX_HDR_DATE,APPLIED_TO_TRX_HDR_ID,
  APPLIED_TO_TRX_HDR_NUMBER_C,APPLIED_TO_TRX_HDR_NUMBER_DISP,
  APPLIED_TO_TRX_HDR_TABLE,APPLIED_TO_TRX_LINE_NUMBER,
  APPLIED_TO_TRX_LINE_TYPE,APPLIED_TO_TRX_LINE_TYPE_NAME,AR_ACTIVITY_NAME,
  AR_ADJUSTMENT_CREATION_TYPE,AR_ADJUSTMENT_TYPE,BANK_ACCOUNT_NAME,
  BANK_STATEMENT_DOC_SEQ_ID,BANK_STATEMENT_DOC_SEQ_NAME,
  BANK_STATEMENT_DOC_SEQ_VALUE,BANK_STATEMENT_LINE_NUMBER,
  BANK_STATEMENT_NUMBER,BOM_DEPARTMENT_CODE,BOM_DEPARTMENT_NAME,
  CHARGEBACK_NUMBER,COST_ELEMENT_ID,COST_ELEMENT_NAME,DISTRIBUTION_SET_NAME,
  GL_BATCH_ID,INVENTORY_ITEM_LOCATOR_ID,INVENTORY_ITEM_LOCATOR_NAME,
  INVENTORY_ITEM_REVISION,ITEM_DESCRIPTION,INVENTORY_ORGANIZATION_CODE,
  INVENTORY_ORGANIZATION_ID,COST_TYPE_ID,COST_TYPE_NAME,MFG_OPERATION_SEQ_NUM,
  PAYMENT_CLEARED_DATE,PAYMENT_DATE,PAYMENT_DEPOSIT_DATE,PAYMENT_NUMBER,
  PAYMENT_RECON_CURRENCY,PO_LINE_NUM,PO_ORDER_NUMBER,PO_ORDER_RELEASE_NUM,
  PRICE_OVERRIDE,PO_ORDER_SHIPMENT_NUM,PO_ORDER_DISTRIBUTION_NUM,
  PO_ORDER_TYPE,RCV_RECEIPT_NUM,RCV_SHIPMENT_HEADER_ID,REVERSAL_COMMENTS,
  REVERSAL_DATE,SUBINVENTORY,TRX_HDR_CURRENCY,TRX_REASON_NAME,
  TRX_SOURCE_TYPE_ID,TRX_SOURCE_TYPE_NAME,UNIT_COST,UNIT_PRICE,WIP_ASSEMBLY,
  WIP_BASIS,WIP_FLOW_SCHEDULE_NUMBER,WIP_JOB_ID,WIP_JOB_NAME,WIP_LINE_CODE,
  WIP_LINE_ID,WIP_RESOURCE_ID,WIP_RESOURCE_NAME,WIP_RESOURCE_SEQ_NUM,
  PA_EMPLOYEE_NUM,PA_EMPLOYEE_NAME,PA_SUPPLIER_NUM,PA_SUPPLIER_NAME,
  PA_PROJECT_NUM,PA_PROJECT_NAME,PA_TASK_NUM,PA_TASK_NAME,PA_EI_EV_ORG,
  PA_NL_RESOURCE_ORG,PA_NL_RESOURCE,PA_EI_EV_TYPE,PA_EVENT_NUM,PA_QUANTITY,
  PA_UOM,PA_PERIOD_DATE,PA_GL_TRANSFER_DATE,PA_CUSTOMER_NUMBER,
  PA_CUSTOMER_NAME,PA_AGREEMENT_NUM,PA_ACCRUE_THRU_DATE,PA_DRI_NUMBER,
  PA_TRANS_ID,PA_LINE_NUMBER,PRVDR_ORGANIZATION_NAME,RECVR_ORGANIZATION_NAME,
  PRVDR_OU_NAME,RECVR_OU_NAME,CC_TYPE_NAME,CC_PROC_METHOD_NAME,APPLICATION_ID,
  JE_HEADER_ID,JE_LINE_NUM,ASSET_ID,ASSET_CATEGORY_ID,ASSET_KEY_CCID,
  CODE_COMBINATION_ID,INVENTORY_ITEM_ID,SET_OF_BOOKS_ID,ORG_ID,TRX_CLASS,
  TRX_TYPE_N,TRX_TYPE_C,TRX_NUMBER_N,TRX_NUMBER_C,DOC_SEQUENCE_ID,
  TRX_HDR_TABLE,TRX_HDR_ID,ACCT_LINE_TYPE,CURRENCY_CONVERSION_TYPE,
  THIRD_PARTY_TYPE,THIRD_PARTY_ID,THIRD_PARTY_SUB_ID,GL_TRANSFER_STATUS,
  SOURCE_TABLE,SOURCE_ID,AEL_TABLE,AEL_ID,AEH_ACCOUNTING_ERROR_CODE,
  AEL_ACCOUNTING_ERROR_CODE,JE_CATEGORY,TRX_LINE_TYPE,TRX_SOURCE_ID,
  LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
  REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
  PA_PROJECT_ID,PA_VIEW_LABOR_COST
FROM
 XLA_AEL_GL_V WHERE 1=2 UNION ALL SELECT TRX_CLASS_NAME ,TRX_TYPE_NAME ,
  TRX_NUMBER_DISPLAYED ,TRX_DATE ,COMMENTS ,DOC_SEQUENCE_NAME ,
  DOC_SEQUENCE_VALUE ,ACCT_LINE_TYPE_NAME ,CURRENCY_CODE ,ENTERED_DR ,
  ENTERED_CR ,ACCOUNTED_DR ,ACCOUNTED_CR ,TAXABLE_ENTERED_DR ,
  TAXABLE_ENTERED_CR ,TAXABLE_ACCOUNTED_DR ,TAXABLE_ACCOUNTED_CR ,
  CURRENCY_CONVERSION_DATE ,CURRENCY_USER_CONVERSION_TYPE ,
  CURRENCY_CONVERSION_RATE ,THIRD_PARTY_NAME ,THIRD_PARTY_NUMBER ,
  THIRD_PARTY_SUB_NAME ,ACCOUNTING_DATE ,GL_TRANSFER_STATUS_NAME ,
  TRANSFER_SYSTEM_NAME ,GL_TRANSFER_FROM_TO_NAME ,ACCOUNTING_COMPLETE_NAME ,
  ACCOUNTING_COMPLETE ,AE_LINE_REFERENCE ,TAX_CODE ,ACCOUNTING_RULE_NAME ,
  TAX_EXEMPT_NUMBER ,TRX_LINE_TYPE_NAME ,TRX_LINE_NUMBER ,
  TRX_DETAIL_LINE_NUMBER ,TRX_QUANTITY ,SALES_ORDER_NUMBER ,SALESREP_NAME ,
  TAX_RATE ,UNIT_SELLING_PRICE ,TRX_UOM ,TRX_SOURCE_NAME ,ASSET_NUMBER ,
  ASSET_DESCRIPTION ,ASSET_BOOK_TYPE_CODE ,ACCOUNTING_EVENT_NUMBER ,
  ACCOUNTING_EVENT_TYPE ,ACCOUNTING_EVENT_TYPE_NAME ,ACCOUNTING_LINE_NUMBER ,
  AEH_ACCOUNTING_ERROR_NAME ,AEL_ACCOUNTING_ERROR_NAME ,USER_JE_CATEGORY_NAME
  ,TRANSFER_STATUS_DETAIL_NAME ,APPLICATION_DATE ,APPLIED_TO_TRX_HDR_CURRENCY
  ,APPLIED_TO_TRX_HDR_DATE ,APPLIED_TO_TRX_HDR_ID ,
  APPLIED_TO_TRX_HDR_NUMBER_C ,APPLIED_TO_TRX_HDR_NUMBER_DISP ,
  APPLIED_TO_TRX_HDR_TABLE ,APPLIED_TO_TRX_LINE_NUMBER ,
  APPLIED_TO_TRX_LINE_TYPE ,APPLIED_TO_TRX_LINE_TYPE_NAME ,AR_ACTIVITY_NAME ,
  AR_ADJUSTMENT_CREATION_TYPE ,AR_ADJUSTMENT_TYPE ,BANK_ACCOUNT_NAME ,
  BANK_STATEMENT_DOC_SEQ_ID ,BANK_STATEMENT_DOC_SEQ_NAME ,
  BANK_STATEMENT_DOC_SEQ_VALUE ,BANK_STATEMENT_LINE_NUMBER ,
  BANK_STATEMENT_NUMBER ,BOM_DEPARTMENT_CODE ,BOM_DEPARTMENT_NAME ,
  CHARGEBACK_NUMBER ,COST_ELEMENT_ID ,COST_ELEMENT_NAME ,
  DISTRIBUTION_SET_NAME ,GL_BATCH_ID ,INVENTORY_ITEM_LOCATOR_ID ,
  INVENTORY_ITEM_LOCATOR_NAME ,INVENTORY_ITEM_REVISION ,ITEM_DESCRIPTION ,
  INVENTORY_ORGANIZATION_CODE ,INVENTORY_ORGANIZATION_ID ,COST_TYPE_ID ,
  COST_TYPE_NAME ,MFG_OPERATION_SEQ_NUM ,PAYMENT_CLEARED_DATE ,PAYMENT_DATE ,
  PAYMENT_DEPOSIT_DATE ,PAYMENT_NUMBER ,PAYMENT_RECON_CURRENCY ,PO_LINE_NUM ,
  PO_ORDER_NUMBER ,PO_ORDER_RELEASE_NUM ,PRICE_OVERRIDE ,
  PO_ORDER_SHIPMENT_NUM ,PO_ORDER_DISTRIBUTION_NUM ,PO_ORDER_TYPE ,
  RCV_RECEIPT_NUM ,RCV_SHIPMENT_HEADER_ID ,REVERSAL_COMMENTS ,REVERSAL_DATE ,
  SUBINVENTORY ,TRX_HDR_CURRENCY ,TRX_REASON_NAME ,TRX_SOURCE_TYPE_ID ,
  TRX_SOURCE_TYPE_NAME ,UNIT_COST ,UNIT_PRICE ,WIP_ASSEMBLY ,WIP_BASIS ,
  WIP_FLOW_SCHEDULE_NUMBER ,WIP_JOB_ID ,WIP_JOB_NAME ,WIP_LINE_CODE ,
  WIP_LINE_ID ,WIP_RESOURCE_ID ,WIP_RESOURCE_NAME ,WIP_RESOURCE_SEQ_NUM ,
  PA_EMPLOYEE_NUM ,PA_EMPLOYEE_NAME ,PA_SUPPLIER_NUM ,PA_SUPPLIER_NAME ,
  PA_PROJECT_NUM ,PA_PROJECT_NAME ,PA_TASK_NUM ,PA_TASK_NAME ,PA_EI_EV_ORG ,
  PA_NL_RESOURCE_ORG ,PA_NL_RESOURCE ,PA_EI_EV_TYPE ,PA_EVENT_NUM ,
  PA_QUANTITY ,PA_UOM ,PA_PERIOD_DATE ,PA_GL_TRANSFER_DATE ,
  PA_CUSTOMER_NUMBER ,PA_CUSTOMER_NAME ,PA_AGREEMENT_NUM ,PA_ACCRUE_THRU_DATE
  ,PA_DRI_NUMBER ,PA_TRANS_ID ,PA_LINE_NUMBER ,PRVDR_ORGANIZATION_NAME ,
  RECVR_ORGANIZATION_NAME ,PRVDR_OU_NAME ,RECVR_OU_NAME ,CC_TYPE_NAME ,
  CC_PROC_METHOD_NAME ,APPLICATION_ID ,JE_HEADER_ID ,JE_LINE_NUM ,ASSET_ID ,
  ASSET_CATEGORY_ID ,ASSET_KEY_CCID ,CODE_COMBINATION_ID ,INVENTORY_ITEM_ID ,
  SET_OF_BOOKS_ID ,ORG_ID ,TRX_CLASS ,TRX_TYPE_N ,TRX_TYPE_C ,TRX_NUMBER_N ,
  TRX_NUMBER_C ,DOC_SEQUENCE_ID ,TRX_HDR_TABLE ,TRX_HDR_ID ,ACCT_LINE_TYPE ,
  CURRENCY_CONVERSION_TYPE ,THIRD_PARTY_TYPE ,THIRD_PARTY_ID ,
  THIRD_PARTY_SUB_ID ,GL_TRANSFER_STATUS ,SOURCE_TABLE ,SOURCE_ID ,AEL_TABLE ,
  AEL_ID ,AEH_ACCOUNTING_ERROR_CODE ,AEL_ACCOUNTING_ERROR_CODE ,JE_CATEGORY ,
  TRX_LINE_TYPE ,TRX_SOURCE_ID ,LAST_UPDATE_DATE ,LAST_UPDATED_BY ,
  CREATION_DATE ,CREATED_BY ,LAST_UPDATE_LOGIN ,REQUEST_ID ,
  PROGRAM_APPLICATION_ID ,PROGRAM_ID ,PROGRAM_UPDATE_DATE ,PA_PROJECT_ID ,
  PA_VIEW_LABOR_COST FROM XLA_PA_EI_AEL_GL_V XLA_AEL_GL_V WHERE
  application_id = :1 AND je_header_id = :2 AND je_line_num = :3 AND -1=-1
  order by TRX_NUMBER_DISPLAYED, PA_LINE_NUMBER



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms