轻松学习 代码片段.docx

目录销售订单提交拟定：1Form中调用其它Form功能：1文件夹初始化：1表增删改锁API：2下拉列表，add_list_element：3资源-销售员-员工3OM项目验证组织:3动态视图查找sql语句4自动生成ccid5税码LOV约束条件invoice_date7销售订单的事务处理阶段8物料键弹性域9验证客户的收单方，收货方10提取销售订单的客户信息10输出日志11APP-PER-50022-HR：用户类型11循环记录12销售订单提交拟定：ELSIF name_in(parameter.ACTIONS) = SUBMIT_DRAFT THEN /* * * 客户化提交拟定 * */ cux_actions_submit_draft.submit_draft; -Added by jingyuan.wang 2015-01-16 -如果提交拟定后的状态是等待内部批准，则将这条报价单导入BPM系统 IF :order.flow_status_code = PENDING_INTERNAL_APPROVAL THEN cux_call_bpm_pkg.submit_request(p_bpm_type = BPM-007, p_source_id = :order.header_id, x_request_id = x_request_id); IF x_request_id 0 THEN IF NOT app_form.quietcommit THEN RAISE form_trigger_failure; END IF; fnd_message.set_name(FND, CONC-SUBMITTED REQUEST); fnd_message.set_token(REQUEST_ID, to_char(x_request_id); fnd_message.show; END IF; END IF; -End AddedForm中调用其它Form功能：BEGIN app_navigate.execute(CUXDISONHAND, Y, Y, P_ORG_ID = | :line.ship_from_org_id | | P_INVENTORY_ITEM_ID= | :line.inventory_item_id | );END;文件夹初始化：-WHEN-NEW-FORM-INSTANCE BEGIN fdrcsid($Header: CUXPAESTBILTYP.fmb 117.39 2014/12/10 22:52:55 pkm ship $); app_standard.event(WHEN-NEW-FORM-INSTANCE); END; app_folder.define_folder_block(CUXPAESTBILTYP, BILL_TYPE, BILL_TYPE_PROMPT, BILL_TYPE_STK, MAIN, , , BILL_TYPE_FIX); app_folder.event(INSTANTIATE); app_folder.define_folder_block(object_name = TEMPLATE_FOLDER_FD2, folder_block_name = PAY_OU_RELATION, prompt_block_name = PAY_OU_RELATION_PROMPT, folder_canvas_name = PAY_OU_RELATION_STK, folder_window_name = MAIN, disabled_functions = , tab_canvas_name = MAIN_TAB, fixed_canvas_name = PAY_OU_RELATION_FIX); app_folder.event(INSTANTIATE);表增删改锁API：BEGIN hand_public_util.table_handle_pkg(p_table_name = CUX_PA_ESTIMATE_BILL_TYPE, p_owner = CUX, p_primary_key = TYPE_ID);END;BEGIN hand_public_util.form_table_handle(p_block_name = BILL_TYPE, p_package_name = CUX_PA_ESTIMATE_BILL_TYPE_PKG, p_table_name = CUX_PA_ESTIMATE_BILL_TYPE, p_primary_key = TYPE_ID);END;下拉列表，add_list_element：-放在WHEN-NEW-FORM-INSTANCE trigger中即可PACKAGE BODY import_list IS PROCEDURE action_type IS CURSOR c_type IS SELECT flv.meaning, flv.lookup_code FROM fnd_lookup_values_vl flv WHERE flv.lookup_type = CUX_MRP_PURCHASE_RULE_TYPE; i NUMBER := 1; BEGIN clear_list(PURCHASE_RULE.TYPE); FOR r_type IN c_type LOOP BEGIN add_list_element(PURCHASE_RULE.TYPE, i, r_type.meaning, r_type.lookup_code); i := i + 1; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; END action_type;END;资源-销售员-员工SELECT ppf.full_name,res.resource_name FROM per_all_people_f ppf, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl res WHERE ppf.employee_number = jrs.salesrep_number AND res.resource_id = jrs.resource_id AND res.category = EMPLOYEE AND trunc(SYSDATE) BETWEEN nvl(jrs.start_date_active, trunc(SYSDATE) AND nvl(jrs.end_date_active, trunc(SYSDATE) AND jrs.salesrep_id = 100002157 - AND _id = 91; -OM项目验证组织:-物料值列表数据来源SELECT sp.parameter_value organization_id, _id, oe_sys_parameters_util.get_value(spd.value_set_id, sp.parameter_value) organization_name FROM oe_sys_parameters_all sp, oe_sys_parameter_def_vl spd WHERE sp.parameter_code = spd.parameter_code AND spd.parameter_code = MASTER_ORGANIZATION_ID;动态视图查找sql语句-第一步：找 sql id-方法一：根据绑定变量找到sql_idSELECT t.address, -000000010EDD5900 t.hash_value, -3003395808 t.sql_id, -5y6k08fth8br0 , -绑定变量的位置 t.datatype_string, -绑定变量的数据类型 t.value_string -绑定变量的值 FROM v$sql_bind_capture t WHERE trunc(t.last_captured) = trunc(SYSDATE) AND t.value_string LIKE %跨年%;-方法二：根据sid，或t.serial#，或audsid 找sql_idSELECT t.prev_sql_addr, -000000010EDD5900 t.prev_hash_value, -3003395808 t.prev_sql_id, -5y6k08fth8br0 t.sid, -639 t.serial#, -23309 t.audsid, -9582574 FROM v$session t WHERE t.audsid = 9582574;-第二步：-根据sql_id找到完整sql语句SELECT t.address, -000000010EDD5900 t.hash_value, -3003395808 t.sql_id, -5y6k08fth8br0 t.sql_text, t.sql_fulltext FROM v$sqlarea t WHERE t.sql_id = 5y6k08fth8br0;-根据sql_id找到sql语句片段SELECT t.address, -000000010EDD5900 t.hash_value, -3003395808 t.sql_id, -5y6k08fth8br0 t.sql_text FROM v$sqltext t WHERE t.sql_id = 5y6k08fth8br0 ORDER BY t.piece;自动生成ccidDECLARE l_bool BOOLEAN;BEGIN l_bool := fnd_flex_keyval.validate_segs(operation = CREATE_COMBINATION, appl_short_name = SQLGL, key_flex_code = GL#, structure_number = 50348, concat_segments = 10000.0.19990.0); IF l_bool THEN dbms_output.put_line(fnd_flex_bination_id); END IF;END;- Get Code Combination ID-FUNCTION get_code_combination_id(p_segment1 IN VARCHAR2, p_segment2 IN VARCHAR2, p_segment3 IN VARCHAR2, p_segment4 IN VARCHAR2, p_segment5 IN VARCHAR2, p_segment6 IN VARCHAR2, p_segment7 IN VARCHAR2) RETURN NUMBER IS l_concatenated_segments VARCHAR2(360); l_code_combination_id NUMBER;BEGIN l_concatenated_segments := p_segment1 | . | p_segment2 | . | p_segment3 | . | p_segment4 | . | p_segment5 | . | p_segment6 | . | p_segment7; l_code_combination_id := fnd_flex_ext.get_ccid(SQLGL, GL#, g_chart_of_accounts_id, to_char(SYSDATE, YYYY/MM/DD HH24:MI:SS), l_concatenated_segments); RETURN l_code_combination_id;END get_code_combination_id;税码LOV约束条件invoice_date-代码片段一SELECT tax_rate_code, tax_rate_name, tax_status_code, tax_jurisdiction_code, tax, tax_regime_code, percentage_rate /* bug 11907051 */ FROM zx_sco_rates WHERE tax_regime_code = :line_sum_folder.tax_regime_code AND tax = :line_sum_folder.tax AND tax_status_code = :line_sum_folder.tax_status_code AND tax_jurisdiction_code IS NULL AND active_flag = Y AND (tax_class IS NULL OR tax_class = INPUT) AND effective_from = :parameter.effective_date OR effective_to IS NULL)UNIONSELECT tax_rate_code, tax_rate_name, tax_status_code, tax_jurisdiction_code, tax, tax_regime_code, percentage_rate /* bug 11907051 */ FROM zx_sco_rates WHERE tax_regime_code = :line_sum_folder.tax_regime_code AND tax = :line_sum_folder.tax AND tax_status_code = :line_sum_folder.tax_status_code AND tax_jurisdiction_code = nvl(:line_sum_folder.tax_jurisdiction_code, tax_jurisdiction_code) AND active_flag = Y AND (tax_class IS NULL OR tax_class = INPUT) AND effective_from = :parameter.effective_date OR effective_to IS NULL);-:inv_sum_folder.related_doc_date:inv_sum_folder.adjusted_doc_date:inv_sum_folder.invoice_date-有效日期 = 相关日期调整日期发票日期（事务处理日期）END;-代码片段二 -验证发票日期在税码的有效期内SELECT COUNT(*) INTO l_null FROM zx_rates_b t WHERE t.tax_rate_code = CN_VAT_0 AND r_invoice.invoice_date BETWEEN t.effective_from AND nvl(t.effective_to, SYSDATE);IF l_null = 0 THEN l_flag := F; g_flag := F; log(r_invoice.invoice_date: | r_invoice.invoice_date); log(r_invoice.invoice_num | 发票日期在税码的有效期内);END IF;销售订单的事务处理阶段-事务处理阶段：如果是报价单，值为洽谈，negotiation。- 如果是销售订单，值为完成，finishment。-在order_summary块和order块的pre_query触发器中加入以下语句来限制查询结果： IF :parameter.startup_mode = SALES_ORDERS THEN set_block_property(ORDERS, default_where, TRANSACTION_PHASE_CODE= | | F | ); ELSIF :parameter.startup_mode = QUOTED_ORDERS THEN set_block_property(ORDERS, default_where, TRANSACTION_PHASE_CODE= | | N | ); END IF;物料键弹性域-库OEXOELIB - 包OE_OEXOEMOE_FOLDER -存储过程Initialize_Line_Folder_Dff-库OEXOEFRM - 包OE_OEXOEMOE_FLEX - 存储过程Define_Key_Flex_FieldsFND_KEY_FLEX.Define(Block = LINE, Field = Ordered_Item_Dsp, Id = Inventory_Item_Id, 相当于ccid Appl_Short_Name = INV, Code = MSTK, Num = To_Char(OE_CLIENT_GLOBALS.G_ITEM_ID_FLEX_NUM), Data_Set = :PARAMETER.OE_ORGANIZATION_ID, Validate = FULL, Required = N, Usedbflds = N, AllowNulls = Y, Insertable = ALL, Updateable = ALL, LONGLIST = Y, COLUMN=DESCRIPTION | name_in(PARAMETER.DESCRIPTION) | (80) | INTO LINE.ITEM_DESCRIPTION, SELECT_COMB_FROM_VIEW = MTL_SYSTEM_ITEMS_FVL, where_clause_msg= APPL=ONT;NAME=OE_INVALID_INTERNAL_ITEM, /* Bug 1764537 chhung BEGIN */ /* add a space in the front of and */ Where_Clause = customer_order_enabled_flag = Y | and bom_item_type in (1,4); /* Bug 1764537 chhung END */-WHEN-NEW-ITEM-INSTANCEOE_LINES.Ordered_Item(WHEN-NEW-ITEM-INSTANCE);OE_LINES_MAIN.Ordered_Item(event);fnd_key_flex.update_definition( BLOCK=LINE, FIELD=ORDERED_ITEM_DSP, ENABLED=N);验证客户的收单方，收货方-验证客户的收单方，收货方BEGIN SELECT cust_acct.cust_account_id sold_to_org_id, ship_su.site_use_id ship_to_org_id, bill_su.site_use_id invoice_to_org_id INTO r_importable_data.sold_to_org_id, r_importable_data.ship_to_org_id, r_importable_data.invoice_to_org_id FROM hz_parties party, hz_cust_accounts cust_acct, hz_cust_acct_sites_all ship_cas, hz_cust_site_uses_all ship_su, hz_cust_acct_sites_all bill_cas, hz_cust_site_uses_all bill_su WHERE party.party_id = cust_acct.party_id AND cust_acct.cust_account_id = ship_cas.cust_account_id AND cust_acct.cust_account_id = bill_cas.cust_account_id AND ship_cas.cust_acct_site_id = ship_su.cust_acct_site_id AND bill_cas.cust_acct_site_id = bill_su.cust_acct_site_id AND ship_su.site_use_code = SHIP_TO AND bill_su.site_use_code = BILL_TO AND party.party_name = r_importable_data.sold_to AND ship_su.location = r_importable_data.ship_to_location AND bill_su.location = r_importable_data.invoice_to_location;EXCEPTION WHEN OTHERS THEN cux_api.set_message(FND, FND_GENERIC_MESSAGE, MESSAGE, 查找客户，收单地点，收货地点出现异常： | SQLERRM); l_import_status := g_invalid_code;END;提取销售订单的客户信息SELECT party.party_name, -客户名称 custs.account_name, -账户名称 uses.location, -地点名称 h.order_number FROM oe_order_headers_all h, hz_parties party, -客户 hz_cust_accounts custs, -客户帐户 hz_cust_acct_sites_all sites, -客户地点 hz_cust_site_uses_all uses -客户使用地点 WHERE custs.party_id = party.party_id(+) AND custs.cust_account_id = sites.cust_account_id AND sites.cust_acct_site_id = uses.cust_acct_site_id AND uses.site_use_code = BILL_TO AND h.invoice_to_org_id = uses.site_use_id AND h.header_id = 73034 AND _id = 86;输出日志PROCEDURE log(p_msg IN VARCHAR2) ISBEGIN fnd_file.put_line(fnd_file.log, p_msg);END log;PROCEDURE output(p_msg IN VARCHAR2) ISBEGIN fnd_file.put_line(fnd_file.output, p_msg);END output;APP-PER-50022-HR：用户类型将系统标准HR相关职责下面的function移到客制化的职责下，运行该function的时候会报这个一个错误：APP-PER-50022:ORACLE人力资源管理系统无法检索用户类型配置文件选项的值.请确保为您的责任正确设置此值.解决方法：将进入系统配置文件，职责：客制化职责配置文件选项：HR:用户类型更改空值为HR用户循环记录-片段一BEGIN go_block(ORDERS_SUMMARY); first_record; LOOP IF :orders_summary.check_box = Y THEN cux_order_control.approve_check_record; END IF; EXIT WHEN :system.last_record = TRUE; next_record; END LOOP; first_record; -记录循环慎用oe_refresh.refresh_block(ORDERS_S