ITPub博客

Oracle已经提供了一个标准的Report \'Transaction historical summary\'来展示历史上的某一天item的Quantity,Value或者Balance输出结果：其中01-SEP-2012 Quantity表示2012年9月1日时，Item的数量。后边的Current Quantity是指现在系统中的库存量是多少。两个数字中间的Purchase Order，Account，Account Alias, Job or Schedule, Other是指从9月1日到当前，那些业务对数量发生了影响。另外有时候，我们想通过一个SQL Query来

Creating Move OrderDECLARE x_return_status VARCHAR2 (1); x_msg_data VARCHAR2 (4000); x_msg_count NUMBER; l_user_id NUMBER; l_resp_id NUMBER; l_appl_id NUMBER; l_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec; x_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec; l_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl; x_line_tbl inv_move_order_pub.trol

Using API inv_mo_backorder_pvt.backorder to back order move orderDECLARE CURSOR c_order_det IS SELECT dd.delivery_detail_id, dd.source_code, dd.source_line_id, dd.org_id, dd.source_header_id, dd.source_header_number, dd.source_line_number, NVL (dl.ship_method_code, dd.ship_method_code) ship_method_code, dd.inventory_item_id, (SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id = dd.inventory_it

In the Oracle E-Business Suite (EBS) Release 12 the data model of Suppliers has become much more complex. The base tables have changed (Suppliers, Sites, Bank Accounts, Contacts) and some of the fields have become obsolete.Here is a query to bring many of the Supplier attributes together, with focus on banks / bank accounts, payment methods, contacts, remittance delivery (email, notification method). Please post comments if you find any issues!Adjust the WHERE clause on the first WITH query to

Sample script for assigning one item to an organizationDECLARE g_user_id fnd_user.user_id%TYPE :=NULL; l_appl_id fnd_application.application_id%TYPE; l_resp_id fnd_responsibility_tl.responsibility_id%TYPE; l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2(2) := fnd_api.g_false; l_commit VARCHAR2(2) := FND_API.G_FALSE; x_message_list error_handler.error_tbl_type; x_retu

SELECT * FROM FND_FOLDERS WHERE object = \'INVTVTXN_RESULTS\';SELECT *FROM FND_FOLDER_COLUMNSWHERE folder_id in(SELECT folder_idFROM FND_FOLDERSWHERE object = \'INVTVTXN_RESULTS\');TablesFND_FOLDERS stores information about customized folders. Each row includes the folder identifier, the name of the folder, the object (which uniquely identifies the set of fields in the form), the screen width in inches, and the flag that determines whether the folder can be opened by users other than the folde

How to use script to get all Oracle EBS Concurrent Program/Report name and corresponding execution file name SELECT fcp.concurrent_program_name,fcpt.user_concurrent_program_name,fcp.EXECUTION_METHOD_CODE,flv.meaning FROM FND_CONCURRENT_PROGRAMS fcp,FND_CONCURRENT_PROGRAMS_TL fcpt,fnd_lookup_values flv WHERE fcp.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID AND flv.lookup_type = \'CP_EXECUTION_METHOD_CODE\' AND fcp.EXECUTION_METHOD_CODE = flv.lookup_code AND fcpt.LANGUAGE = \'

How to use script to get all Oracle EBS Form name and corresponding fmb file name SELECT fff.function_name, ff.form_name, ffft.user_function_name, fff.function_id, ff.form_id, fff.application_id FROM fnd_form ff, fnd_form_functions fff, fnd_form_functions_tl ffft WHERE ff.form_id = fff.form_id AND fff.function_id = ffft.function_id AND ffft.language = \'US\' AND fff.application_id = 401 --Inventory Module OR

查询所有ICX相关Profile的SQLset pages 100 set line 120 column "Option" format 99999999 column "Profile Name" format A35 column "Profile Value" format A55 spool support_profile_value.txt select a.PROFILE_OPTION_ID "Option" ,a.PROFILE_OPTION_NAME "Profile Name" ,b.PROFILE_OPTION_VALUE "Profile Value" from fnd_profile_options a ,fnd_profile_option_values b where a.PROFILE_OPTION_ID = b.PROFILE_OPTION_

1.如何查询EBS系统的版本信息 Query: select release_name from apps.fnd_product_groups;Table FND_PRODUCT_GROUPS FND_PRODUCT_GROUPS stores information about what release, language and type of product group (for example, Standard or Demonstration) you have installed at your site. FND_PRODUCT_GROUPS also stores supplementary information about the product group type in the ARGUMENT1 column. FND_PRODUCT_GROUPS is used mainly by AutoInstall. Oracle Application Object Library uses this

Compile alter package INV.INV_TRANSFER_ORDER_PVT compile; Check error if any show error; C:\Documents and Settings\ptian>sqlplus apps/password@instance SQL*Plus: Release 10.1.0.2.0 - Production on 星期一 7月 16 23:57:18 2012 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter package INV.IN

Call Inventory Transaction Manager set serveroutput on; declare --retmsg varchar2(1000); retmsg varchar2(240); retval number; trxhdrid number := \'&1\'; prnmsg varchar2(256); x_msg_data varchar2(256); x_msgcnt number; begin retval := INV_LPN_TRX_PUB.PROCESS_LPN_TRX( p_trx_hdr_id => trxhdrid, p_commit => fnd_api.g_false, x_proc_msg => retmsg, p_proc_mode => 1, p_process_trx => fnd_api.g_true, p_atomic => fnd_api.g_false); if (length(retmsg) > 200)

客户可以通过Help > Diagnostics > Custom Code > Personalize，来对Form做个性化的设置。如何通过脚本来查看Form做了哪些个性化 SELECT * FROM FND_FORM_CUSTOM_RULES WHERE form_name = \'INVTOMAI\';--Input your Form Name SELECT * FROM FND_FORM_CUSTOM_ACTIONS WHERE RULE_ID IN (SELECT ID FROM fnd_form_custom_rules WHERE form_name = \'INVTOMAI\') --Input your Form Name ORDER BY SEQUENCE; SELECT * FROM FND_FORM_CUSTOM_SCOPES WHERE RULE_ID IN (SELECT ID F

Oracle Apps Script to create a new User Login Account--CREATE a new user account DECLARE p_user_name VARCHAR2 (200) := \'PTIAN\'; p_owner VARCHAR2 (200) := NULL; p_unencrypted_password VARCHAR2 (200) := \'welcome\'; p_session_number NUMBER := USERENV (\'sessionid\'); p_start_date DATE := SYSDATE; p_end_date DATE := NULL; p_last_logon_date DATE

1.Using script to submit INV Manager to process MMTT/* * When prompted for user_id, resp_id and resp_appl_id, ask ct enter the correct values for * these variables. The values can be obtained by logging into Inventory * reponsibility and from menu Help->Diagnostics->Examine, select block as * $PROFILES and field as the 3 variables and note down the values of each. */ SET SEVEROUTPUT ON declare l_header_id number; p_userid NUMBER := &user_id; p_respid NUMBER := &resp_id;

Transaction Type = Transaction Source Type + Transaction Action eg.Move Order Issue = Move Order + Issue From Stores Below is the script to check Txn Type, Txn Source Type, Txn Action(Lookups) --Txn Type SELECT TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME,TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID FROM MTL_TRANSACTION_TYPES ORDER BY TRANSACTION_TYPE_ID; --Source Type SELECT TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_TYPE_NAME FROM MTL_TXN_SOURCE_TYPES ORDER BY T

Looking on how to check long running concurrent request in Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect the session information of each program.First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request. SELECT a.request_id ,a.oracle_process_id "SPID" ,frt.responsibility_name ,c.concurrent_program_name || \': \' || ctl.user_concurrent_p

知道Form执行文件的名字情况下，如何来查找对应的功能名称 SELECT a.form_name||\' : \'||nvl(b.description, b.user_form_name) FROM APPS.FND_FORM a, APPS.FND_FORM_TL b WHERE a.form_id = b.form_id AND a.form_name IN (\'INVTTMTX\');Output:INVTTMTX : Perform miscellaneous transactions 知道功能名称的情况下，如何来查找对应的fmx名称 SELECT a.form_name,b.description FROM APPS.FND_FORM a, APPS.FND_FORM_TL b WHERE a.form_id = b.form_id AND Upper(b.descript

系统版本信息装了哪些模块，以及版本信息select \'Current Application Release: \'||ver||\' (\'||bug||\')\' "Description" from ( select x.* from (select 1 seq, \'1939818\' bug,\'11.5.0\' pv,\'11.5.6\' ver from dual union select 2,\'2123967\',\'11.5.0\',\'11.5.7\' from dual union select 3,\'2293243\',\'11.5.0\',\'11.5.8\' from dual union select 4,\'2669606\',\'11.5.0\',\'11.5.9 MP\' from dual union select 5,\'3126422\',\'11.5.0\',\'11.5.9 Update1\' from dual union select 6,\'3171663\',\'11.5.0\',\'11.5.9 Up

How to Re-Compile All Invalid for Oracle EBS Package/Package Body set serveroutput on size 1000000 declare sql_statement varchar2(200); cursor_id number; ret_val number; CURSOR cur_invalid IS select object_type, owner, object_name from sys.dba_objects o where o.status = \'INVALID\' and o.object_type in (\'PACKAGE\', \'PACKAGE BODY\') AND object_name LIKE \'PO%\';--重新编译PO开头的Package begin dbms_output.put_line(chr(0)); dbms_output.put_line(\'Re-compilation of Invalid Objects\'); db