查询EBS中的view

取EBS中的view， 会取不到值，

1. 如view： po_requisition_lines

直接在select * from po_requisition_lines 会取不到值。

我们看下这个view的定义

select text from user_views where lower(view_name)='po_requisition_lines';

：

SELECT REQUEST_ID , PROGRAM_APPLICATION_ID , PROGRAM_ID , PROGRAM_UPDATE_DATE , USSGL_TRANSACTION_CODE , GOVERNMENT_CONTEXT , CLOSED_REASON , CLOSED_DATE , TRANSACTION_REASON_CODE , QUANTITY_RECEIVED , SOURCE_REQ_LINE_ID , ORG_ID , CANCEL_REASON , CLOSED_CODE , AGENT_RETURN_NOTE , CHANGED_AFTER_RESEARCH_FLAG , VENDOR_ID , VENDOR_SITE_ID , VENDOR_CONTACT_ID , RESEARCH_AGENT_ID , ON_LINE_FLAG , WIP_ENTITY_ID , WIP_LINE_ID , WIP_REPETITIVE_SCHEDULE_ID , WIP_OPERATION_SEQ_NUM , WIP_RESOURCE_SEQ_NUM , ATTRIBUTE_CATEGORY , DESTINATION_CONTEXT , INVENTORY_SOURCE_CONTEXT , VENDOR_SOURCE_CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , BOM_RESOURCE_ID , PARENT_REQ_LINE_ID , JUSTIFICATION , NOTE_TO_AGENT , NOTE_TO_RECEIVER , PURCHASING_AGENT_ID , DOCUMENT_TYPE_CODE , BLANKET_PO_HEADER_ID , BLANKET_PO_LINE_NUM , CURRENCY_CODE , RATE_TYPE , RATE_DATE , RATE , CURRENCY_UNIT_PRICE , SUGGESTED_VENDOR_NAME , SUGGESTED_VENDOR_LOCATION , SUGGESTED_VENDOR_CONTACT , SUGGESTED_VENDOR_PHONE , SUGGESTED_VENDOR_PRODUCT_CODE , UN_NUMBER_ID , HAZARD_CLASS_ID , MUST_USE_SUGG_VENDOR_FLAG , REFERENCE_NUM , ON_RFQ_FLAG , URGENT_FLAG , CANCEL_FLAG , SOURCE_ORGANIZATION_ID , SOURCE_SUBINVENTORY , DESTINATION_TYPE_CODE , DESTINATION_ORGANIZATION_ID , DESTINATION_SUBINVENTORY , QUANTITY_CANCELLED , CANCEL_DATE , REQUISITION_LINE_ID , REQUISITION_HEADER_ID , LINE_NUM , LINE_TYPE_ID , CATEGORY_ID , ITEM_DESCRIPTION , UNIT_MEAS_LOOKUP_CODE , UNIT_PRICE , QUANTITY , DELIVER_TO_LOCATION_ID , TO_PERSON_ID , LAST_UPDATE_DATE , LAST_UPDATED_BY , SOURCE_TYPE_CODE , LAST_UPDATE_LOGIN , CREATION_DATE , CREATED_BY , ITEM_ID , ITEM_REVISION , QUANTITY_DELIVERED , SUGGESTED_BUYER_ID , ENCUMBERED_FLAG , RFQ_REQUIRED_FLAG , NEED_BY_DATE , LINE_LOCATION_ID , MODIFIED_BY_AGENT_FLAG , KANBAN_CARD_ID , CATALOG_TYPE , CATALOG_SOURCE , MANUFACTURER_ID , MANUFACTURER_NAME , MANUFACTURER_PART_NUMBER , REQUESTER_EMAIL , REQUESTER_FAX , REQUESTER_PHONE , UNSPSC_CODE , OTHER_CATEGORY_CODE , SUPPLIER_DUNS , TAX_STATUS_INDICATOR , PCARD_FLAG , NEW_SUPPLIER_FLAG , AUTO_RECEIVE_FLAG , TAX_USER_OVERRIDE_FLAG , TAX_CODE_ID , NOTE_TO_VENDOR , OKE_CONTRACT_HEADER_ID , OKE_CONTRACT_VERSION_ID ,ITEM_SOURCE_ID ,SUPPLIER_REF_NUMBER ,SECONDARY_UNIT_OF_MEASURE ,SECONDARY_QUANTITY ,PREFERRED_GRADE ,SECONDARY_QUANTITY_RECEIVED ,SECONDARY_QUANTITY_CANCELLED ,AUCTION_HEADER_ID ,AUCTION_DISPLAY_NUMBER ,AUCTION_LINE_NUMBER ,REQS_IN_POOL_FLAG ,VMI_FLAG ,BID_NUMBER ,BID_LINE_NUMBER , NONCAT_TEMPLATE_ID ,SUGGESTED_VENDOR_CONTACT_FAX ,SUGGESTED_VENDOR_CONTACT_EMAIL ,AMOUNT ,CURRENCY_AMOUNT ,JOB_ID ,JOB_LONG_DESCRIPTION ,CONTACT_INFORMATION ,CANDIDATE_SCREENING_REQD_FLAG ,SUGGESTED_SUPPLIER_FLAG ,CANDIDATE_FIRST_NAME ,CANDIDATE_LAST_NAME ,ASSIGNMENT_END_DATE ,OVERTIME_ALLOWED_FLAG ,LABOR_REQ_LINE_ID ,CONTRACTOR_STATUS ,CONTRACTOR_REQUISITION_FLAG , DROP_SHIP_FLAG ,ASSIGNMENT_START_DATE ,ORDER_TYPE_LOOKUP_CODE ,PURCHASE_BASIS ,MATCHING_BASIS ,NEGOTIATED_BY_PREPARER_FLAG ,SHIP_METHOD ,ESTIMATED_PICKUP_DATE ,SUPPLIER_NOTIFIED_FOR_CANCEL ,BASE_UNIT_PRICE FROM PO_REQUISITION_LINES_ALL WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

这个view的查询有带当前的OU，即org_id.

所以取值需要给设定一个OU：

BEGIN
  fnd_global.APPS_INITIALIZE

    (user_id => 1110 , -- 要取的profle对应的user

     resp_id => 50321, -- 要取的profle对应的职责

      resp_appl_id =>101 -- 要取的profle对应的application

        );
END;

之后就可以取到相应OU下的值。

2. 如view： FND_FLEX_VALUES_VL

有些情况，就算运行上面的fnd_global.APPS_INITIALIZE，还是取不到数据。

我们看下他的定义select text from user_views where view_name ='FND_FLEX_VALUES_VL';

SELECT B.ROWID ROW_ID, B.FLEX_VALUE_SET_ID, B.FLEX_VALUE_ID, B.FLEX_VALUE, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.CREATION_DATE, B.CREATED_BY, B.LAST_UPDATE_LOGIN, B.ENABLED_FLAG, B.SUMMARY_FLAG, B.START_DATE_ACTIVE, B.END_DATE_ACTIVE, B.PARENT_FLEX_VALUE_LOW, B.PARENT_FLEX_VALUE_HIGH, B.STRUCTURED_HIERARCHY_LEVEL, B.HIERARCHY_LEVEL, B.COMPILED_VALUE_ATTRIBUTES, B.VALUE_CATEGORY, B.ATTRIBUTE1, B.ATTRIBUTE2, B.ATTRIBUTE3, B.ATTRIBUTE4, B.ATTRIBUTE5, B.ATTRIBUTE6, B.ATTRIBUTE7, B.ATTRIBUTE8, B.ATTRIBUTE9, B.ATTRIBUTE10, B.ATTRIBUTE11, B.ATTRIBUTE12, B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, B.ATTRIBUTE16, B.ATTRIBUTE17, B.ATTRIBUTE18, B.ATTRIBUTE19, B.ATTRIBUTE20, B.ATTRIBUTE21, B.ATTRIBUTE22, B.ATTRIBUTE23, B.ATTRIBUTE24, B.ATTRIBUTE25, B.ATTRIBUTE26, B.ATTRIBUTE27, B.ATTRIBUTE28, B.ATTRIBUTE29, B.ATTRIBUTE30, B.ATTRIBUTE31, B.ATTRIBUTE32, B.ATTRIBUTE33, B.ATTRIBUTE34, B.ATTRIBUTE35, B.ATTRIBUTE36, B.ATTRIBUTE37, B.ATTRIBUTE38, B.ATTRIBUTE39, B.ATTRIBUTE40, B.ATTRIBUTE41, B.ATTRIBUTE42, B.ATTRIBUTE43, B.ATTRIBUTE44, B.ATTRIBUTE45, B.ATTRIBUTE46, B.ATTRIBUTE47, B.ATTRIBUTE48, B.ATTRIBUTE49, B.ATTRIBUTE50, T.FLEX_VALUE_MEANING, T.DESCRIPTION, B.ATTRIBUTE_SORT_ORDER from FND_FLEX_VALUES_TL T, FND_FLEX_VALUES B

 where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID and T.LANGUAGE = userenv('LANG')

select userenv('LANG') from dual

所以，如果'FND_FLEX_VALUES_VL'的值是在日语环境下录入（一般会保存日文JA和英文US两条记录）的，但是你的oracle client字符集是简体中文的话，就会取不到值。

client 的字符集需要和server的字符集一致。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8754571/viewspace-604360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8754571/viewspace-604360/