Order Import Interface (Sales Order Conversion)
Interface tables:
· OE_HEADERS_IFACE_ALL
· OE_LINES_IFACE_ALL
· OE_ACTIONS_IFACE_ALL
· OE_ORDER_SOURCES
· OE_CUSTOMER_INFO_IFACE_ALL
· OE_PRICE_ADJS_IFACE_ALL
· OE_PRICE_ATTS_IFACE_ALL
Base tables:
· OE_ORDER_HEADERS_ALL
· OE_ORDER_LINES_ALL
Pricing tables
· QP_PRICING_ATTRIBUTES
During import of orders, shipping tables are not populated.
If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL
Base Tables: HZ_PARTIES HZ_LOCATIONS
Orders can be categorized based on their status:
1. Entered orders
2. Booked orders
3. Closed orders
Concurrent Program: Order Import
Validations:
· Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
· Check for sales_rep_id. Should exist for a booked order.
· Ordered_date should exist. -------- header level
· Delivery_lead_time should exist. ----------- line level
· Earliest_acceptable_date should exist.
· Freight_terms should exist
Order Import API OE_ORDER_PUB.GET_ORDER, PROCESS_ORDER
Concurrent programs will in turn call APIs.
2 APIs are called during order import process.
· OE_CNCL_ORDER_IMPORT_PVT (cancelled orders)
· ORDER_IMPORT_PRIVATE
Procedure: import_order()
Item import (Item conversion)
Always import master and child records.
Interface tables:
· MTL_SYSTEM_ITEMS_INTERFACE
· MTL_ITEM_REVISIONS_INTERFACE( If importing revisions, populate)
· MTL_ITEM_CATEGORIES_INTERFACE(If importing categories, populate)
· MTL_INTERFACE_ERRORS
Item import can be run in create mode or update mode.
Running the Item Open Interface In Create Mode:
Populate the mtl_system_items_interface with the following minimum required columns when creating new items:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org id.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n)
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface is required if you are populating revision history data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both.
Running the Item Open Interface In Update Mode:
To update existing item(s), set TRANSACTION_TYPE = 'UPDATE'.
For best performance, use inventory_item_id when updating items.
Functionality
Every attribute updateable from the Item form is updateable through the interface, and all required validations are performed to enforce:
· Item Attribute Interdependencies
· Master - Child Attribute Dependencies
· Status Controlled Attributes
· Templates can be applied to existing Items. For best results, use template_id and template_name.
· The Item status can be changed for existing Items, and the proper attributes are Defaulted / Set accordingly. The Status change is recorded in
MTL_PENDING_ITEM_STATUS.
· To populate material costs from IOI: Populate the LIST_PRICE_PER_UNIT column with a value while importing items and you will see your material cost for your item in MTL_SYSTEM_ITEMS after running the Item Import process. (CREATE transaction_type only)
· When launching items into the Master Item Org, the Child records are copied into MTL_SYSTEM_ITEMS_INTERFACE for validation, and are identified with transaction_type of 'AUTO_CHILD'. These records are deleted if the parameter 'Delete Processed Rows' has been passed as 'Yes', and remain for diagnostic purposes if the parameter is passed as 'No'. When the defining attribute for a Functional area is enabled, the proper default category set and category is assigned to the Item.
· Master Items were loaded before child records in MTL_SYSTEM_ITEMS.
Not Supported Issues
=========================================
· Item Costs cannot be UPDATED (using "UPDATE" transaction_type) through the interface.
· New Item revisions cannot be added to existing Items.
· Current functionality does not support updates to a PTO MODEL ITEM through
the IOI update feature. See notes: 1076412.6 and 2121870.6 Updating Item Attributes to NULL The method to update these columns to NULL is to use the following values:
1. for Numeric fields: insert -999999
2. for Character fields: insert '!'
3. for Date fields: the above list does not include any updateable date fields.
Importing Master and Child Records
==================================
The user procedures are as follows :
1. Populate the item interface tables (mtl_system_items_interface). This step is necessary if you are creating items and categories in the same run. For importing item category assignments for already existing items, you do not need to populate item interface table.
2. Populate the item categories interface table (mtl_item_categories_interface).
The user needs to populate the following mandatory columns in item categories interface table:
A. Either inventory_item_id or item_number. When item and category are being imported together, then user can only specify the item_number, since item id will be generated by the import process.
B. Either organization_id or organization_code or both.
C. The transaction_type column should be 'CREATE'. We do not support 'UPDATE' or 'DELETE' for item category assignment.
D. Either category_set_id or category_set_name or both.
E. Either category_id or category_name or both.
F. Process_flag column as 1.
G. Populate the set_process_id column. The item and category interface records should have the same set_process_id, if you are importing item and category assignment together.
3. After populating the item and category interface tables, launch the Item Import process from the applications. In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have have any value. Through the import process, we can only create item category assignment(s).
Updation or Deletion of item category assignment is not supported.
4. Once the concurrent process completes, check the mtl_interface_errors table for any error(s) during the item and category import. Correct those error conditions in the interface tables and run the item import again. If the process_flag is 7, that means the item category interface records were successfully imported.
Revisions
==============================
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface table is required if you are populating revision data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported, plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both. For historical item revision data, do NOT populate the REVISION column in the mtl_system_items_interface table. This column is used only if the current revision of the item is being imported.
Populate these columns in the mtl_item_revisions_interface table:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org ID.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = (Must match the item_number in mtl_system_items_interface table.)
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
Run the IOI process. Navigate --> Inventory: Items: Import Items
There are 6 parameters to enter to begin the process:
1. Specify one or all organizations.
2. Validate items, yes or no.
3. Process items, yes or no.
4. Delete processed rows, yes or no.
5. Process set (null for all)
6. Create or update items (1 for create, 2 for update)
Note: If you are importing Master and Child records, insert them into the mtl_system_items_interface and mtl_item_revisions_interface tables, and run them at the same time by setting the 'All organizations' parameter to 'Yes'. If you do not do this, then the Child revision records will not be imported.
Error Checking:
======================================
When importing multiple revisions, if one record for an item fails validation, all revisions for that item fail. Resolve failed rows by checking the mtl_interface_errors table.
SELECT table_name, column_name, error_message, message_name
FROM mtl_interface_errors;
Base tables:
§ MTL_SYSTEM_ITEMS_B
§ MTL_ITEM_REVISIONS_B
§ MTL_CATEGORIES_B
§ MTL_CATEGORY_SETS_B
§ MTL_ITEM_STATUS
§ MTL_ITEM_TEMPLATES
Concurrent program: Item Import
Validations: check for valid item type.
Check for valid part_id/segment of the source table.
Validate part_id/segment1 for master org.
Validate and translate template id of the source table.
Check for valid template id. (attributes are already set for items, default attributes for
that template, i.e., purchasable, stockable, etc)
Check for valid item status.
Validate primary uom of the source table.
Validate attribute values.
Validate other UOMs of the source table.
Check for unique item type. Discard the item, if part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.
Load master records and category records only if all validations are passed.
Load child record if no error found.
Interface Tables Base Tables
MTL_SYSTEM_ITEMS_INTERFACE MTL_SYSTEM_ITEMS
MTL_TRANSACTIONS_INTERFACE
MTL_ITEM_REVISION_INTERFACE MTL_ITEM_REVISIONS
MTL_DEMAND_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE MTL_ITEM_CATEGORIES
MTL_CROSS_REFERENCES_INTERFACE MTL_CROSS_REFERENCES
On-hand quantity
Interface tables: MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG to manipulate the records in the table. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base tables: MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS MTL_SERIAL_NUMBERS
Concurrent program:
Validations: validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Customer conversion
Interface tables: RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
Base tables: HZ_PARTIES
HZ_CONTACTS
HZ_PROFILES
HZ_LOCATIONS
Base tables for RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL
Uses TCA APIs.
Concurrent program: Customer Interface
Validations: Check if legacy values fetched are valid.
; Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
Customer API
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2. Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3. Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
4. Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
Base table:
· HZ_PARTIES
· HZ_PARTY_SITES
· HZ_LOCATIONS
· HZ_CUST_ACCOUNTS
· HZ_CUST_SITE_USES_ALL
· HZ_CUST_ACCT_SITES_ALL
· HZ_PARTY_SITE_USES
Validations: Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
Auto Invoice interface
Interface tables: RA_INTERFACE_LINES_ALL
Base tables:
RA_CUSTOMER_TRX_ALL
RA_BATCHES
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
AR_RECEIVABLES_APPLICATIONS
AR_ADJUSTMENTS
AR_CASH_RECEIPTS
RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program: Auto invoice master program
Validations: check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity.
Validate if the amount includes tax flag.
Receipt API
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY
To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.
Base tables: AR_CASH_RECEIPTS
Validations: check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.
Lockbox interface
Interface tables: AR_PAYMENTS_INTERFACE_ALL (Import data from bank file )
Base tables: AR_INTERIM_CASH_RECEIPTS_ALL AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)
Related Tables: AR_BANK_ACCOUNTS_ALL AR_RECEIPT_METHODS
AR_TRANSMISSIONS_ALL HZ_CUST_ACCOUNTS HZ_CUST_SITE_USES_ALL AR_CASH_RECEIPTS
(POST QUICK CASH -- applies the receipts and updates customer balances)
Concurrent program: nav-> receivables->interfaces->lockbox
Validations: check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).
AP invoice interface
Interface tables: AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE
Base tables: AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info
Concurrent program: Payables Open Interface Import
Validations: check for valid vendor
Check for valid vendor site code.
Check if record already exists in payables interface table.
Vendor conversion/interface
No interface tables
Base tables: PO_VENDORS PO_VENDOR_SITES_ALL
No concurrent program as data is directly populated into base tables.
Validations: check if a vendor already exists with the same name as the TIMSS customer
mail name.
Check if the proper site code and id exists based on the site code from TIMSS.
Check for uppercase value of the vendor name existed in Oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.
Purchasing:
Interface Tables Base Tables
PO_HEADERS_INTERFACE PO_HEADERS_ALL
PO_LINES_INTERFACE PO_LINES_ALL
PO_REQUISITIONS_INTERFACE_ALL PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DIST_INTERFACE_ALL PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_INTERFACE PO_DISTRIBUTIONS_ALL
PO_RESCHEDULE_INTERFACE PO_REQUISITION_LINES_ALL
Requisition import
Interface tables: PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
Basetables: PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Concurrent program: REQUISITION IMPORT
Validations: check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.
PO Receipts Interface
Interface tables:
· RCV_HEADERS_INTERFACE
· RCV_TRANSACTIONS_INTERFACE
Base tables:
· RCV_SHIPMENT_HEADERS
· RCV_SHIPMENT_LINES
· RCV_TRANSACTIONS
Concurrent program: RECEIVING OPEN INTERFACE
Error messages: 1. Run RECEIVING INTERFACE ERRORS REPORT
2. Look in PO_INTERFACE_ERRORS
Query to check interface errors: PO_INTERFACE_ERRORS .inteface_transaction_id =
RCV_HEADERS_INTERFACE.header_interface_id and processing_status_code in (‘error’ ,’print’)
Validations:
GL interface
Interface tables: GL_INTERFACE
Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES
Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES
Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Validations for the staging table:
Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.
GL budget interface
Interface tables: GL_BUDGET_INTERFACE
Base tables:
GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES
Concurrent program: Budget Upload
Validations: Check Account combination is valid or not. You check this in GL_CODE_COMBINATIONS table.
GL daily conversion rates
Interface tables: GL_DAILY_RATES_INTERFACE
Base tables:
· GL_DAILY_RATES
· GL_DAILY_CONVERSION_TYPES
Concurrent Program: Program - Daily Rates Import and Calculation
Fixed Assets Conversion
Interface tables: fa_mass_additions
Base tables:
· FA_ADDITIONS
· FA_BOOKS_BOOK_CONTROLS_V (It's view). fa_additions_tl .........
Concurrent Program: Program -
Fixed Assets Manager-> Mass Additions -> Post Mass Additions
Fixed Assets Manager-> Mass Additions -> Delete Mass Additions
Fixed Assets Manager-> Tax -> Initial Mass Copy
Fixed Assets Manager-> Tax -> period mass copy
Trace a Concurrent Request And Generate TKPROF File
October 5, 2008 · 1 Comment
Hello Friends, As in Oracle Application, there always need to tune programs , procedure and it is always difficult to make a perfect program, here i am trying to put a “How to” on tkprof and surely like to know if you find it useful.
How to Trace a Concurrent Request And Generate TKPROF File
Enable Tracing For The Concurrent Manager Program
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
2. Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program. The SQL prompts the user for the request id
SELECT ’Request id: ’||request_id , ‘Trace id: ’||oracle_Process_id, ‘Trace Flag: ’||req.enable_trace, ‘Trace Name: ‘||dest.value||’/'||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’, ‘Prog. Name: ’||prog.user_concurrent_program_name, ‘File Name: ’||execname.execution_file_name|| execname.subroutine_name , ‘Status : ’||decode(phase_code,’R',’Running’) ||’-'||decode(status_code,’R',’Normal’), ‘SID Serial: ’||ses.sid||’,'|| ses.serial#, ‘Module : ’||ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+) and dest.name=’user_dump_dest’ and dbnm.name=’db_name’ and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;
3. TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.
$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no
Where: raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the sql statements
sort: his provides the sort criteria in which all sql statements will be sorted. This will bring the bad sql at the top of the outputfile.
sys=no:Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries
$ tkprof sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10
Thanks - Shivmohan Purohit
转:http://henryren.itpub.net/post/15063/413360
前几天做了一个把文件上传到form的输入域中的程序,这里把步骤简单的写一下,附上关键的源代码.以供参考.
1.创建一个临时表: XXX_TEMP_FILES
建表语句是:
CREATE TABLE XXX_TEMP_FILES (
FILE_ID NUMBER,
SEQ NUMBER,
TEXT VARCHAR2(2000),
CREATION_DATE DATE)
创建这个表的目的就是要把数据从FND_LOBS这个表的file_data这个字段的数据,通过程序,因为这个表的这个数据是lobs类型的,所以整个文件都放在这个字段里,我们利用这个临时表,把数据文件拆成一行一个纪录.然后每行在根据分个符查分.
2. 创建上传包: XXX_FILE_IO
XXX_FILE_IO 包含三个子程序:
INS_TEMP_FILES:从fnd_lobs 表中取上传的数据,把它按行来拆分,并插入到: XXX_TEMP_FILES 表中
SEL_TEMP_FILES:从XXX_TEMP_FILES 中把数据去出
DEL_TEMP_FILES:数据已经上传到Form的界面中,需要删除文件.
主要语句就是:
PROCEDURE INS_TEMP_FILES(p_file_id IN NUMBER) IS
w_integer INTEGER;
w_blob BLOB;
w_raw RAW(10);
w_buff VARCHAR2(30000);
w_line VARCHAR2(30000);
w_len INTEGER;
eofsw BOOLEAN := FALSE;
offset INTEGER;
w_b_len NUMBER := 0;
w_num NUMBER;
w_seq NUMBER := 0;
BEGIN
SELECT file_data INTO w_blob FROM fnd_lobs WHERE file_id = p_file_id;
offset := 1;
LOOP
EXIT WHEN eofsw;
w_seq := w_seq + 1;
w_raw := utl_raw.cast_to_raw(chr(10));
w_num := dbms_lob.instr(w_blob, w_raw, offset, 1);
w_len := w_num - w_b_len;
w_b_len := w_num;
IF w_num = 0
THEN
w_len := 20000;
eofsw := TRUE;
END IF;
BEGIN
DBMS_LOB.READ(w_blob, w_len, offset, w_buff);
EXCEPTION
WHEN no_data_found THEN
EXIT;
WHEN OTHERS THEN
RAISE;
END;
w_line := utl_raw.cast_to_varchar2(w_buff);
SELECT REPLACE(w_line, chr(10), NULL) INTO w_line FROM dual;
SELECT REPLACE(w_line, chr(13), NULL) INTO w_line FROM dual;
INSERT INTO XXX_TEMP_FILES
(FILE_ID, SEQ, TEXT, CREATION_DATE)
VALUES
(p_file_id, w_seq, w_line, SYSDATE);
offset := offset + w_len;
END LOOP;
END;
3. 在Form文件中的Program Unit中健一个包.XXX_UPLOAD
这个文件最好做成PLL,和其他客制化的有用的程序一起打包,上传到form这是一个系统比较有用的通用的程序.
主要是利用FND_GFM这个通用上传的工具,把文件传到,FND_LOBS中去,然后第二步建好的的包,进行数据拆分:
p_file_id := NULL;
access_id := FND_GFM.AUTHORIZE(NULL);
FND_PROFILE.GET('APPS_WEB_AGENT', l_server_url);
l_url := rtrim(l_server_url, '/') ||
'/fnd_file_upload.displayGFMform?access_id=' ||
to_char(access_id) ||
chr(38) ||
'l_server_url=' ||
l_server_url;
if (l_url is NULL) then
raise form_trigger_failure;
return NULL;
end if;
FND_UTILITIES.OPEN_URL(l_url);
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 = 1 then
p_file_id := FND_GFM.GET_FILE_ID(access_id);
XXX_FILE_IO.INS_TEMP_FILES(p_file_id);
-- pcm_dbms_lob.UPOPEN(p_file_id);
else
return NULL;
end if;
return p_file_id;
1. 在Form文件中的Program Unit中健一个程序如: UPLOAD_XXX_XXX(X根据实际需要转换)
UPLOAD_XXX_XXX 这个文件的需要在实际的应用中作改动,根绝实际的要导入的字段修改程序.主要功能就是从XXX_TEMP_FILES把数据读出,然后根据分割符来,把每个数据对应到form的域中.
查看全文比如有一个外围支持系统，用户需要在外围系统登录之后点个link就可以登录到Oracle ERP系统中，那么我们需要先把外围系统的用户创建在Oracle ERP中，并且分配职责给他。
DECLARE
a BOOLEAN;
BEGIN
-------------------------------------------------------------
--Change password
-------------------------------------------------------------
/*
a := apps.fnd_user_pkg.changepassword('SIMON','oracle');
IF NOT a THEN
--RAISE_APPLICATION_ERROR(-20002, 'Password not updated');
dbms_output.put_line('Password not updated');
ELSE
dbms_output.put_line('***Password updated');
COMMIT;
END IF;
*/
-------------------------------------------------------------
--Create User
-------------------------------------------------------------
fnd_user_pkg.CreateUser('SIMON',
'SEED',
'PW$234567'
);
-------------------------------------------------------------
--Add Resp
--select * from fnd_responsibility fr where fr.responsibility_id =
--select * from fnd_responsibility_tl frt where upper(frt.responsibility_name) like '%%'
--select * from fnd_application fa where fa.application_id =
--select * from fnd_security_groups fs
-------------------------------------------------------------
fnd_user_pkg.AddResp ('SIMON',
'SYSADMIN',
'SYSTEM_ADMINISTRATOR',
'STANDARD',
'Test API',
sysdate,
null
);
Commit;
End;
--使用下面代码可以在Link中直接登录Oracle ERP系统：
iSupport Login
http://host:port/OA_HTML/jtfavald.jsp"
method="post" name="Login" id="Login">
查看全文
WEB_SERVER_PREFIX := FND_WEB_CONFIG.GFM_AGENT; MIME_TYPE := NVL (FND_PROFILE.VALUE ('FND_EXPORT_MIME_TYPE'), 'text/tab-separated-values' ); DB_FILE := FND_GFM.FILE_CREATE (CONTENT_TYPE => MIME_TYPE, PROGRAM_NAME => 'export'); V_RECORD_NUM := :SYSTEM.CURSOR_RECORD; GO_BLOCK ('PO_RULE'); FIRST_RECORD LOOP EXIT WHEN SYSTEM.LAST_QUERY = TRUE ; OUTSTRING := 输入你要的数据 FND_GFM.FILE_WRITE_LINE (DB_FILE, OUTPUT_STRING); NEXT_RECORD; END LOOP; GO_RECORD (V_RECORD_NUM); DB_FILE := FND_GFM.FILE_CLOSE (DB_FILE); URL := FND_GFM.CONSTRUCT_DOWNLOAD_URL (WEB_SERVER_PREFIX, DB_FILE, TRUE); FND_UTILITIES.OPEN_URL (URL); 这样就搞定了，我刚开始想的太复杂了。因为我要使用Excle的模版，认为模版要在程序中调用，所以走了很多的弯路。 TO Mypeking : 你随便打开一个form只要有数据列的，你选择文件--〉导出就可以存入excel了。
Oracle标准功能就用这一句代码：app_export.event('EXPORT');
自己写最多再在前面加个:go_block('xxx');
就够了。
--By: Anil Passi
--When Jun-2001
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
BEGIN
--Note, can be executed only when you have apps password.
-- Call the procedure to Creaet FND User
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'oracle'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'appstechnical.blogspot.com'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => 30 /*Change this id by running below SQL*/
/*
SELECT person_id
,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('full_name') || '%'
GROUP BY person_id
,full_name
*/
,x_email_address => 'appstechnical.blogspot@gmail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
END;
法一、重启库
库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。
法二、Metalink给出的一个方法
修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
法三、我常用的一个方法,具体内容如下：
1、 使用如下语句a查看一下认谁在用临时段
SELECT username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
2、 那些正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空间回缩一下
SQL>Alter tablespace TEMP coalesce;
法四、使用诊断事件的一种方法,也是被我认为是“杀手锏”的一种方法
1、 确定TEMP表空间的ts#
SQL>select ts#, name from sys.ts$ ;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
4 TOOLS
5 INDX
6 DRSYS
2、 执行清理操作
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;
说明：
temp表空间的TS# 为 3*, So TS#+ 1= 4
其它：
1、 出现如上问题的原因我认为可能是由于大的排序超出了TEMP表空间的空间允许范围引起的。也可能包含着其它的异常的因素。
2、 观注TEMP等这些空间的状态是Dba日常职责之一，我们可以通过Toad、Object Browser等这些工具办到，也可以用如下的语句：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC
2.create temporary tablespace TEMP2 TEMPFILE @#/home2/oracle/oradata/sysmon/temp02.dbf@# SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --创建中转临时表空间
3.alter database default temporary tablespace temp2; --改变缺省临时表空间 为刚刚创建的新临时表空间temp2
4.drop tablespace temp including contents and datafiles;--删除原来临时表空间
5.create temporary tablespace TEMP TEMPFILE @#/home2/oracle/oradata/sysmon/temp01.dbf@# SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新创建临时表空间
6.alter database default temporary tablespace temp; --重置缺省临时表空间为新建的temp表空间
7.drop tablespace temp2 including contents and datafiles;--删除中转用临时表空间
8.alter user roll temporary tablespace temp; --重新指定用户表空间为重建的临时表空间
SELECT B.FILE_ID 文件ID号
,B.TABLESPACE_NAME 表空间名
,B.FILE_NAME 文件名
,B.BYTES / 1024 / 1024 "空间(M)"
,(B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 "已使用(M)"
,SUM(NVL(A.BYTES, 0)) / 1024 / 1024 "剩余空间(M)"
,SUM(NVL(A.BYTES, 0)) / (B.BYTES) * 100 剩余百分比
FROM DBA_FREE_SPACE A
,DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID
AND B.TABLESPACE_NAME = 'POD' --GTD
GROUP BY B.TABLESPACE_NAME
,B.FILE_ID
,B.FILE_NAME
,B.BYTES
ORDER BY B.FILE_ID;
SELECT d.status "Status"
,d.tablespace_name "Name"
,a.file_name
,d.contents "Type"
,d.extent_management "Extent Management"
,to_char(nvl(a.bytes / 1024 / 1024,
0),
'99,999,990.900') "Size (M)"
,nvl(t.bytes,
0) / 1024 / 1024 || '/' ||
nvl(a.bytes / 1024 / 1024,
0) "Used (M)"
,to_char(nvl(t.bytes / a.bytes * 100,
0),
'990.00') "Used %"
FROM sys.dba_tablespaces d
,(SELECT tablespace_name
,file_name
,SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name
,file_name) a
,(SELECT tablespace_name
,SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.contents LIKE 'TEMPORARY'SELECT d.status "Status"
,d.tablespace_name "Name"
,a.file_name
,d.contents "Type"
,d.extent_management "Extent Management"
,to_char(nvl(a.bytes / 1024 / 1024,
0),
'99,999,990.900') "Size (M)"
,nvl(t.bytes,
0) / 1024 / 1024 || '/' ||
nvl(a.bytes / 1024 / 1024,
0) "Used (M)"
,to_char(nvl(t.bytes / a.bytes * 100,
0),
'990.00') "Used %"
FROM sys.dba_tablespaces d
,(SELECT tablespace_name
,file_name
,SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name
,file_name) a
,(SELECT tablespace_name
,SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
/*ALTER DATABASE DATAFILE '/oracle/gttest/gttestdata/pod01.dbf' RESIZE 550M;*/
一、模板制作：YSFOLDER.FMB
打开TEMPLATE.FMB，如果打开的是APPSTAND.FMB，以下步骤省略：
1、FORM级触发器：
1、 在WHEN-NEW-FORM-INSTANCE后面添加以下代码：
--定义文件夹数据块
app_folder.define_folder_block(
object_name => ' DETAILBLOCK _FOLDER' ,
folder_block_name => 'DETAILBLOCK',
prompt_block_name => 'DETAILBLOCK_PROMPT',
folder_canvas_name => 'DETAILBLOCK_STACKED',
folder_window_name => 'BLOCKNAME',
disabled_functions => '');
--以上参数均为VARCHAR2,长度不得超过20个字符
2、 增加FORM级触发器：FOLDER_ACTION， PL/SQL代码如下：
app_folder.event(:global.folder_action);
3、 增加FORM级触发器：FOLDER_RETURN_ACTION， PL/SQL代码如下：
null;
--
-- action code is in :global.folder_action
-- affected field is in :global.folder_field
--
2、Attached Libraries：
添加文件：APPFLDR.PLL
来源：ERP安装目录下，AU目录下查找该类文件所在的目录
3、Object Groups
继承一个对象组：STANDARD_FOLDER
做法：复制APPSTAND.FMB中的同名对象，粘贴到模板对象组中，选择“Refence”（继承），路径方式选择“Remove path”（不要路经）。
结果：将会把对象组中的所有对象映射添加到相应的对象节点中。
二、模板例子的制作
1、 修改WHEN-NEW-FORM-INSTANCE中文件夹数据块定义语句中的参数，确定对象名称（一般为form对象名称）、文件夹数据块名称、标题块名称、堆叠画布名称、窗口名称。
2、 定义窗口（windows），win_main：继承属性WINDOW_NORMAL
3、 定义堆叠画布（stacked canvas-views）, cv_detail_stacked：继承属性类CANVAS_STACKED
4、 定义文件夹数据块（folder block），blk_detail：继承属性类BLOCK
（1） 触发器：
· KEY-EXEQRY：
app_folder.event('KEY-EXEQRY');
· KEY-NEXT-ITEM
app_folder.event('KEY-NEXT-ITEM');
· KEY-PREV-ITEM
app_folder.event('KEY-PREV-ITEM');
· POST-BLOCK
app_folder.event('POST-BLOCK');
· PRE-BLOCK
app_folder.event('PRE-BLOCK');
· WHEN-NEW-BLOCK-INSTANCE
App_folder.event('WHEN-NEW-BLOCK-INSTANCE');
· KEY_CLRBLK
App_folder.event(' KEY_CLRBLK ');
· KEY_ENTQRY
App_folder.event(' KEY_ENTQRY ');
· KEY_NXTREC
App_folder.event(' KEY_NXTREC ');
· KEY_PRVREC
App_folder.event(' KEY_PRVREC ');
· PRE_QUERY
App_folder.event(' PRE_QUERY ');
· WHEN-NEW-RECORD-INSTANCE
App_folder.event('WHEN-NEW-RECORD-INSTANCE');
（2） 定义数据项，可以是表项，也可以是非基表项，确定哪些项需要放在堆叠画布上，注意只有在堆叠画布上的项才能使用文件夹功能
（3） 注意：数据项的类型不能是：Display Item、List Item等；
可以是：Text Item、Check Box等；
如果打开folder时出现错误：“Set_Item_Property没有这样的属性”，说明erp标准程序中用到的属性在个别项中不存在，出现几次就有几个缺乏这个属性的Item；是否拥有这个属性，是由Item的类型确定的，因此有些Item类型不能用于folder功能。
（4） 确定各个项在画布中的位置
5、 定义标题数据块（prompt block）, blk_detail_prompt：继承属性类BLOCK_DIALOG
便捷的做法：先复制文件夹数据块blk_detail，改名称，再删去新块下的全部触发器，然后除去不需要的项，修改剩下所有项的属性继承FOLDER_PROMPT_MULTRIROW，最后将各个项的default value属性值写入对应的中文或英文标题
（1） 触发器：无
（2） 标题项，来自文件夹数据块blk_detail，属性继承自FOLDER_PROMPT_MULTIROW，default value属性值写入相应的中英文标题
标题项必须和参与folder功能的数据项保持一一对应，名称必须一致
（3） 文件夹功能项（6个）：
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92289/viewspace-1012938/，如需转载，请注明出处，否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
转载于:http://blog.itpub.net/92289/viewspace-1012938/
1903
到【灌水乐园】发言
