开发积累的常用代码.doc

获取当前窗口 GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAME)设置金额显示格式PROCEDURE FORMAT_PRICE(EVENT VARCHAR2) ISBEGIN IF (EVENT IN (WHEN-VALIDATE-ITEM,POST-QUERY) THENAPP_ITEM_PROPERTY.SET_PROPERTY(LINES.SUGGESTED_PRICE,FORMAT_MASK, FND_CURRENCY.GET_FORMAT_MASK(:ORDERS.CURRENCY_CODE,GET_ITEM_PROPERTY(LINES.SUGGESTED_PRICE,MAX_LENGTH); APP_ITEM_PROPERTY.SET_PROPERTY(LINES.TOTAL_PRICE,FORMAT_MASK, FND_CURRENCY.GET_FORMAT_MASK(:ORDERS.CURRENCY_CODE,GET_ITEM_PROPERTY(LINES.TOTAL_PRICE,MAX_LENGTH); ELSE FND_MESSAGE.DEBUG(Invalid event passed to lines.format_price |EVENT); END IF;END FORMAT_PRICE;获取帐户帐套，帐户CODE相关信息 /*= * PROCEDURE: pre_form *=*/ procedure pre_form is cursor sob_cur(sob_id number) is SELECT sob.set_of_books_id, sob.currency_code, sob.chart_of_accounts_id FROM gl_sets_of_books sob WHERE sob.set_of_books_id = sob_id ; l_coa_id number; l_sob_id number; l_currency_code varchar2(15); l_org_id number; l_order_num_mode varchar2(10); begin - Set of Books ID l_sob_id := fnd_profile.value(GL_SET_OF_BKS_ID); - org id l_org_id := fnd_profile.value(ORG_ID); if (l_org_id is null) then fnd_message.set_name(FND, PROFILES-CANNOT READ); fnd_message.set_token(OPTION, ORG_ID); fnd_message.error; raise form_trigger_failure; end if; open sob_cur(l_sob_id) ; fetch sob_cur into l_sob_id, l_currency_code,l_coa_id; close sob_cur; :parameter.currency_code := l_currency_code ; l_order_num_mode := fnd_profile.value(TRN_PO_ORDER_NUM_TYPE_036); - Stash values away for future reference - :_id := to_number(l_org_id); -:parameter.set_of_books_id := to_number(l_sob_id); :parameter.chart_of_accounts_id := to_number(l_coa_id); :parameter.order_number_mode := l_order_num_mode; - set amount mask set_amount_mask; end pre_form; procedure set_amount_mask is begin - - setup amount fields format mask - - set_item_property(HEADERS.TOTAL_AMOUNT,FORMAT_MASK, fnd_currency.get_format_mask( :parameter.currency_code, get_item_property(HEADERS.TOTAL_AMOUNT, MAX_LENGTH); set_item_property(LINES.LINE_AMOUNT,FORMAT_MASK, fnd_currency.get_format_mask( :parameter.currency_code, get_item_property(LINES.LINE_AMOUNT, MAX_LENGTH); end set_amount_mask; 动态提交请求APPS.FND_REQUEST.SUBMIT_REQUEST ( APPLICATION IN VARCHAR2 DEFAULT NULL, PROGRAM IN VARCHAR2 DEFAULT NULL, DESCRIPTION IN VARCHAR2 DEFAULT NULL, START_TIME IN VARCHAR2 DEFAULT NULL, SUB_REQUEST IN BOOLEAN DEFAULT FALSE, chr(0), , , , , , , , , ) RETURN NUMBER;状态判断get_block_property(headers,status):System.ModeGET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAMEGet_Block_Property( LINES_PROMPT, PREVIOUSBLOCK) GET_RECORD_PROPERTY(:SYSTEM.CURSOR_RECORD,SHOPPEDAYOVERTB_V,Status ); -:SYSTEM.RECORD_STATUS ;键弹性域定义和更新定义fnd_key_flex.define(BLOCK=Items,FIELD=EXPENSE_ACCID_DSP,APPL_SHORT_NAME=SQLGL,CODE=GL#,ID=EXPENSE_CCID,REQUIRED=Y,USEDBFLDS=N,updateable=,VALIDATE= FULL,VRULE= nSUMMARY_FLAGnInAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWEDnN,NUM=:PARAMETER.CHART_OF_ACCOUNTS_ID);Form中执行SQL语句sql1:= TRUNCATE TABLE cfnd_matrix_cells;forms_ddl(sql1); 更新procedure update_definition( block varchar2, field varchar2, enabled varchar2 default $FFLEX_DEFAULT$, validate varchar2 default $FFLEX_DEFAULT$, vdate varchar2 default $FFLEX_DEFAULT$, displayable varchar2 default $FFLEX_DEFAULT$, insertable varchar2 default $FFLEX_DEFAULT$, updateable varchar2 default $FFLEX_DEFAULT$, vrule varchar2 default $FFLEX_DEFAULT$, copy varchar2 default $FFLEX_DEFAULT$, derived varchar2 default $FFLEX_DEFAULT$, valatt varchar2 default $FFLEX_DEFAULT$, title varchar2 default $FFLEX_DEFAULT$, required varchar2 default $FFLEX_DEFAULT$, autopick varchar2 default $FFLEX_DEFAULT$, autocombpick varchar2 default $FFLEX_DEFAULT$, usedbflds varchar2 default $FFLEX_DEFAULT$, allownulls varchar2 default $FFLEX_DEFAULT$, data_set varchar2 default $FFLEX_DEFAULT$, column varchar2 default $FFLEX_DEFAULT$, where_clause varchar2 default $FFLEX_DEFAULT$, query_security varchar2 default $FFLEX_DEFAULT$, qbe_in varchar2 default $FFLEX_DEFAULT$, read_only varchar2 default $FFLEX_DEFAULT$, dinsert varchar2 default $FFLEX_DEFAULT$, longlist varchar2 default $FFLEX_DEFAULT$, no_combmsg varchar2 default $FFLEX_DEFAULT$, lock_flag varchar2 default $FFLEX_DEFAULT$, combqp_where varchar2 default $FFLEX_DEFAULT$, derive_always varchar2 default $FFLEX_DEFAULT$, help varchar2 default $FFLEX_DEFAULT$, default_mode varchar2 default $FFLEX_DEFAULT$, where_clause_msg VARCHAR2 DEFAULT $FFLEX_DEFAULT$)使用该方法更新相应的属性即可。Form中导入数据 DECLARE access_id NUMBER; l_server_url VARCHAR2(100); l_parameters VARCHAR2(100); button_choice INTEGER; l_file_id VARCHAR2(100); l_gfm_id INTEGER;BEGIN IF :file.import_type IS NULL OR :file.template_name IS NULL THEN fnd_message.set_string(请先输入完整数据.); fnd_message.show; RAISE form_trigger_failure; END IF; access_id := fnd_gfm.authorize(NULL); - BUG 2589587 FND File Upload form is not displayed consistently - in the correct language - now using fnd_function.execute to enforce - ICX security and NLS issues. fnd_profile.get(APPS_WEB_AGENT, l_server_url); l_parameters := access_id= | access_id | l_server_url= | l_server_url; fnd_function.EXECUTE(function_name = FND_FNDFLUPL, open_flag = Y, session_flag = Y, other_params = l_parameters); - Display a modal message for user to indicate file upload - is completed. fnd_message.set_name(FND, ATCHMT-FILE-UPLOAD-COMPLETE); button_choice := fnd_message.question(button1 = YES, button2 = NULL, button3 = NO, default_btn = 1, cancel_btn = 3, icon = question); IF (button_choice = 3) THEN NULL; ELSIF (button_choice = 1) THEN DELETE FROM cfnd_matrix_cells; COMMIT; l_file_id := ; copy(l_file_id, document_header.file_name_display); l_gfm_id := fnd_gfm.get_file_id(access_id); IF l_gfm_id IS NOT NULL THEN copy(to_char(l_gfm_id), document_header.media_id); SELECT decode(instr(file_name, /), 0, file_name, substr(file_name, instr(file_name, /) + 1) INTO l_file_id FROM fnd_lobs WHERE file_id = l_gfm_id; IF l_file_id IS NOT NULL THEN :file.filename := l_file_id; SELECT cfnd_matrix_cells_s1.NEXTVAL INTO :file.working_id FROM dual; cfnd_upl_pkg.extract_blob(l_gfm_id, :file.file_cs, chr(:file.delimiter), :file.working_id); go_item(CFND_MATRIX_CELLS.LINE_NO); clear_block(no_validate); execute_query; DELETE FROM fnd_lobs WHERE file_id = l_gfm_id; forms_ddl(commit); forms_ddl(commit); END IF; END IF; END IF;END;FORM中获取光标所在的TAB页面 1. 在Form级触发器中添加触发WHEN-TAB-PAGE-CHANGED 2. 在此触发器中写如下代码： DECLARE canvas_id VARCHAR2(30); -标签页ID BEGIN canvas_id := GET_CANVAS_PROPERTY(标签画布名, topmost_tab_page); IF canvas_id=标签页1 then go_block(块1);end if; IF canvas_id=标签页2 then go_block(块2);end if; IF canvas_id=标签页3 then go_block(块3);end if; execute_query; END;设置时间DECLARE timer_id Timer; one_minute NUMBER(5) := 60000; BEGIN timer_id := CREATE_TIMER(emp_timer, one_minute, REPEAT|NO_REPEAT); END;生成Editer框 DECLARE ed_id Editor; status BOOLEAN; BEGIN ed_id:=Find_Editor(edit_name); -由edit_name导航器定义 IF NOT Id_Null(ed_id) THEN Show_Editor(ed_id, NULL, :block_name.item_name, status); ELSE Message(Editor Happy_Edit_Window not found); RAISE Form_Trigger_Failure; END IF; END; 动态产生一个LOV框 DECLARE lv_id LOV; status BOOLEAN; BEGIN lv_id := Find_LOV(lov_name); -lov_name 由导航器定义 - IF Id_Null(lv_id) THEN - lv_id := Find_LOV(lov_name1); -lov_name1 由导航器定义 - END IF; status := Show_LOV(lv_id,10,20); END; 打开form上标准菜单 app_menu.set_prop(EDIT.SELECT_ALL, ENABLED, PROPERTY_ON);app_menu.set_prop(EDIT.DESELECT_ALL, ENABLED, PROPERTY_ON);在相应层次建立出发器即可；From 中Item代码格式Format A typical item handler looks like this:procedure ITEM_NAME(event VARCHAR2) ISIF (event = WHENVALIDATEITEM) THEN validate the itemELSIF (event = INIT) THEN initialize this dependent itemELSIF (event in (PRERECORD, POSTQUERY) THEN etc.ELSE fnd_message.debug(Invalid event passed to item_name: |EVENT);END IF;END ITEM_NAME;取关键性弹性域帐户描述的方法declare - Boolean parameters are translated from/to integers: - 0/1/null false/true/null result boolean;begin - Call the function result := fnd_flex_keyval.validate_ccid(appl_short_name = :appl_short_name,-SQLGLkey_flex_code = :key_flex_code,-GL#structure_number = :structure_number,-50228combination_id = :combination_id,-113773displayable = :displayable,-ALLdata_set = :data_set, vrule = :vrule, security = :security, -IGNORE get_columns = :get_columns, resp_appl_id = :resp_appl_id, -101 resp_id = :resp_id,-50481 user_id = :user_id, -11193 select_comb_from_view = :select_comb_from_view); dbms_output.put_line(fnd_flex_keyval.concatenated_descriptions); - Convert false/true/null to 0/1/nullend;组织访问权限的控制语句(11i) SELECT ict.ROWID row_id, anization_id, ict.rate, ict.base_type_code, flv.MEANING, ict.adjust_account_id, ict.description, ict.created_by, ict.creation_date, ict.last_updated_by, ict.last_update_date, ict.last_update_login, ood.ORGANIZATION_NAME ORGANIZATION_NAME FROM dpos_item_cost_rate ict, org_organization_definitions ood,org_access oa,fnd_lookup_values_vl flvWHERE anization_id = ood.ORGANIZATION_IDAND anization_id=ood.ORGANIZATION_IDAND oa.resp_application_id=fnd_profile.VALUE(RESP_APPL_ID)AND oa.responsibility_id = fnd_profile.VALUE(RESP_ID)循环访问所有记录DECLARE cur_blk VARCHAR2(40) := :System.Cursor_Block; bk_id Block;BEGIN bk_id := Find_Block(cur_blk); GO_BLOCK(SHOPPEDAYOVERTB_V); GO_RECORD(1); LOOP if get_block_property(bk_id,Update_Allowed)=TRUE then :SHOPPEDAYOVERTB_V.import_flag:=N; UPDATE SHOPPEDAYOVERTB SET import_flag =N where SHOPPEDAYOVERTB.ROWID=:SHOPPEDAYOVERTB_V.ROW_ID; end if; EXIT WHEN (NAME_IN(SYSTEM.LAST_RECORD) = TRUE); NEXT_RECORD; END LOOP; commit;END;在查询模式下使LOV有效 To enable LOVs in ENTERQUERY mode on an item, create an itemlevel KEYLISTVAL trigger as follows:Trigger: KEYLISTVALIF (:SYSTEM.MODE != ENTERQUERY) THEN LIST_VALUES;ELSE SHOW_LOV(query lov);END IF;关闭窗口代码段PROCEDURE close_window (wnd VARCHAR2) ISIF wnd = HEADER THEN Exit the formapp_window.close_first_window;ELSIF wnd = LINES THEN Close detail windows (Shipments)app_custom.close_window(SHIPMENTS); If cursor is in this window, move it to the HEADER blockIF (wnd = GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM,ITEM_CANVAS),WINDOW_NAME) THENGO_BLOCK(HEADER);END IF;ELSIF wnd = SHIPMENTS THEN If cursor is in this window, move it to the LINES blockIF (wnd = GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAME) THENGO_BLOCK(LINES);END IF;END IF; THIS CODE MUST REMAIN HERE. It ensures the cursor is not in the window that will be closed by moving it to the previous block.IF (wnd = GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAME) THENDO_KEY(PREVIOUS_BLOCK);END IF; Now actually close the designated windowHIDE_WINDOW(wnd);END close_window;从当前form转到请求提交页面 fnd_function.execute( function_name = FND_FNDRSRUN,open_flag = Y,session_flag = Y,other_params = DODT_REQ_ID=|TO_CHAR(l_request_id)|);在R12中实现多OU编程 A.首先最重要的是要在pre-form中初始化多OUBEGIN APP_STANDARD.EVENT(PRE-FORM);/必须在APP_STANDARD.EVENT()后执行MO_GLOBAL.init (INV);-参数可以使S单OU，M-多OU，或者已经注册过的应用简称END;B.初始化后获取OU的信息，在Pre-form中获取OU信息，或在块上When-Create-Record获取OU信息Pre-formDECLAREl_default_org_id number;l_default_ou_name varchar2(240);l_ou_count number;BEGIN.mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);:PARAMETER.mo_default_org_id) := l_default_org_id;:PARAMETER.mo_default_ou_name := l_default_ou_name;:PARAMETER.mo_ou_count := l_ou_count; .END;When-Create-RecordIF :parameter.mo_default_org_id is not null and :_id is null THEN:_id := :parameter.mo_default_org_id);:block.operating_unit := :parameter.mo_default_ou_name;END IF;C.在各个触发器实现多OU的支持的代码When-Create-Record Trigger of Operating Unit Field BlockIF (:parameter.mo_default_org_id IS NOT NULL ) THEN - Defaulting org_id from profile option :_id := :parameter.mo_default_org_id; :block.operating_unit := :parameter.mo_default_ou_name; - Set policy context mo_global.set_policy_context(S,:_id);ELSE mo_global.set_policy_context(M, null);END IF;IF : is not nullIF : nvl(:,-99) THEN - Get the cache for current orgEND IF;ELSE- Refresh the cache.END IF;When-Validate-Item Trigger of Operating Unit fieldIF (: IS NOT NULL ) THENIF : nvl(:,-99) THEN mo_global.set_policy_context(S, :_id); - Get the cache for the current orgEND IF;ELSE - :_id is nullmo_global.set_policy_context(M, null);- Refresh the cacheEND IF; When-New-Record-Instance Trigger of Operating Unit Field BlockIF (: IS NOT NULL ) THENIF : nvl(:,-99) THEN mo_global.set_policy_context(S, :_id); - Get the cache for the current orgEND IF;ELSE - :_id is null, so set the context to multiplemo_global.set_policy_context(M, null);- Refresh the cacheEND IF;Pre-Insert Trigger of Operating Unit Field BlockUse this trigger if the form allows the user to commi