April 18, 2013


FLEX FIELD

What is the difference between key flexfield and Descriptive flexfield?

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number, a cost centre etc
Desc Flex is used to just capture extra information.
Key Flexfields have qualifiers whereas Desc Flexfields do not.
Desc Flexfields can have context sensitive segments while Key flexfields cannot.
And one more differenct that KFF displays like text item but DFF displays like [ ] .

Which procedure should be called to enable a DFF in a form?
FND_DESCR_FLEX.DEFINE
(BLOCK                                 => 'BLOCK_NAME'
                ,FIELD                                    => 'FORM_FIELD_NAME'
               ,APPL_SHORT_NAME      => 'APP_NAME'
               ,DESC_FLEX_NAME         => 'DFF_NAME'
               );

In Brief,
We need to follow the following steps in your form:
a) Create a displayed field to hold your concatenated segment values (the [ ] field).
b) Create fields (normally hidden) for each of the flexfield columns in your table (the
      one used for your flex).
c) In the form-level WHEN-NEW-FORM-INSTANCE,
call FND_DESCR_FLEX.DEFINE,
setting BLOCK parameter to the name of the block containing your [ ] field,
the FIELD parameter to the name of your [ ] field,
the APPL_SHORT_NAME parameter to the short name of your application and the DESC_FLEX_NAME parameter to the name of your descriptive flex.

In the form-level WHEN-NEW-ITEM-INSTANCE,
WHEN-VALIDATE-ITEM,
PRE-INSERT,
PRE-UPDATE,
POST-QUERY and
PRE-QUERY triggers place a call to FND_FLEX.EVENT as follows:
         FND_FLEX.EVENT ( 'TRIGGER_NAME');
where TRIGGER_NAME is the name of the trigger,
e.g. in the PRE-QUERY trigger .... FND_FLEX.EVENT('PRE-QUERY');
Which procedure should be used to make the DFF read only at run time?
FND_DESCR_FLEX.UPDATE_DEFINITION()

What is the difference between flexfield qualifier and segment qualifier?
Flexfiled qualifier identifies segement in a flexfield and segment qualifier identifies value in a segment.
There are four types of flexfiled qualifier
1)     Balancing segment qualifier
2)     cost center
3)     natural account and
4)     intercompnay
segemtn qualifier :-
1)     allow budgeting
2)     allow posting
3)     account type
4)     contral account and
5)     reconciliation flag

What are the components of DFF (like structure, segment)?
Howmany Key flexfiels are there in Inventory?
What is Value Set?
Value Sets define and store the valid items of data, which may be entered into a field. Key Flexfields, Descriptive Flexfields and many standard fields use Value Sets. Oracle already comes with hundreds of Value Sets. We define additional Value Sets to support our own user-defined Key and Descriptive Flexfields (although we may use any existing standard Value Sets if they suit our purpose). In defining a new Value Set, we are defining the physical format of valid data, which can reside in that Value Set. Data in a Value Set can be of several types: 
  •  Independently loaded into a Value Set (through a standard form). 
  •  Resident in a table (to which we direct the Value Set definition). 
  •  There could be No Validation (any data can go into the field, but still subject to the formatting rules.) 
  •  Dependent on the value of data in a preceding Independent segment (loaded through a standard form). 
Ex: For Supplies & Accessories CC in ATP, we define GEMS_GPO_ASSIGN_SET value set for the assignment set associated with that OU.
Read More

April 12, 2013


  1. Enter Order in OM (Customer, Order type, Pricelist, shipping details, etc)
  2. Enter the Items details and sourcing details in Order Line
  3. Check for the availability and Book the Order
  4. Schedule and Reserve the Order
  5. Pick Release the Order (Reports here: Pick Selection List Generation, Pick Slip Report, Shipping Exception Report)
  6. Ship confirm the order in Shipping transaction form (Reports here: Bill Of Ladding, Packing Slip Report, Commercial Invoice, Vehicle Load Sheet Details, Interface Trip Stop)
  7. Line Status become Interfaced in Shipping Transaction form
  8. Sales Order Line status will be Shipped
  9. Run Workflow Background Concurrent request to close the workflow which in turn will close the order line.
  10. This will trigger Autoinvoice Master program (inturn it will run Autoinvoice Import Program)
  11. Then will trigger Prepayment Matching Program
  12. Invoice (Transaction#) can be found in Account Receivables
  13. After getting the Cash from customer, Create the Receipt.
  14. After creation of the receipt, match the receipt with the invoice transaction
  15. Once the receipt is matched, then the data can be transferred to General Ledger by running General ledger Transfer Program.
  16. Then Journal can be Imported in GL by Import Journals
  17. Then Journals can be posted to GL by Post Journals.

Read More

April 11, 2013


Order Import Interface (Sales Order Conversion)

Interface Tables

OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDERS_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL

Base Tables

OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
QP_PRICING_ATTRIBUTES

Concurrent Program:

Order Import

API

OE_ORDER_PUB.GET_ORDER and OE_ORDER_PUB.PROCESS_ORDER

Item Import

Interface Tables

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_INTERFACE_ERRORS

Concurrent program:

Item Import

Base Tables:

MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES

Inventory Onhand Quantity

Interface Tables

MTL_TRANSACTION_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

Concurrent Program:

Can be submitted in Transaction Manager through Interface Manager

API

INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS

Base Tables:

MTL_ONHAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS

Customer Conversions

Interface Tables

RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILE_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANK_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base Tables

RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL

Concurrent Program

Customer Interface

API

Trading Community Architecture uses the APIs to create the customers too
APIs are

Set the organization id

Exec dbms_application_info.set_client_info(<org_id>);

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

Create a physical location

HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

Create a party site

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

Create an account site

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

Create an account site use

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 Tables

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITE_USES

Auto Invoice Interface

Interface Tables

RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS

Base Tables

RA_BATCHES
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program

Auto invoice master program

Requisition Import

Interface Tables

PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

Base Tables:

PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent Program:

Requisition Import

Purchase Order Conversions

Interface Tables:

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERACE
PO_INTERCAE_ERRORS

Concurrent Interface Program:

Import Standard Purchase Orders

Base Tables:

PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Read More

Introduction:
DataLoad is powerful tool used by non-technical and technical users alike to load data and configuration in to ERP, CRM and operational systems such as Oracle E-Business Suite. These help pages are the primary source of information about DataLoad and how to use it. Follow the section links above and the menu links on the left to find more information about using DataLoad.
The latest version has many additional features upgraded to the earlier version
  • Sophisticated Load Control
  • Browser Control: Loads data into browser based applications too
  • Scripting: Now scripting language also given as feature to control the data using PHP codes (but you have to buy the professional version for this)
  • Data Validation: This feature will help people more who are going to deal with complex set of data and where validation is more important.
  • Header and Footer Grid: New Header and Footer grids enable one-off Macro load activities at the start and end of loads
  • Macro Recorder: Records and encodes keystrokes and mouse clicks, making it easier to create new Macro loads

Disadvantage:
  • Not interactive
  • During the Dataload process, you canot do anything in your machine
  • No log files generated
  • If system works slower than the mentioned delays then, it will not throw any error and dataload will continue. So there might be chance, dataload will be wrong or wrong data will be loaded in -wrong fields. So need to monitor the process.
Download the tool from

Click here for Direct Link to the exe file

·  Using DataLoad:


This is the main window, where you need to put all your data and have to mention the commands.
You can find the Command details for f=different group like, EBS R12, 11i, Siebel, SAP and others in this navigation
Edit à Commands

DataLoad Command
Application Action(s)
TAB
<Tab>
ENT
<Enter>
*UP
<Up Arrow>
*DN
<Down Arrow>
*LT
<Left Arrow>
*RT
<Right Arrow>
*SP
Save & Proceed
*FE
Field Editor
*PB
Previous Block
*NB
Next Block
*PF
Previous Field
*NF
Next Field
*PR
Previous Record
*NR
Next Record
*ER
Erase Record
*DR
Delete Record
*FR
First Record
*LR
Last Record
*SAVE
Save Record
*SB
Send a single space character
*ST
Select entire field text.
*SLN or *SL(N)
Pause for N seconds. Note 1
*BM
Block Menu
*AX
Alt + X where X is a single letter (A-Z). Note 2
*FI
Find +
*FA
Find All +
*QE
Query Enter +
*QR
Query Run +
*CL
Clear Field +
*IR
Insert record +
*CW(window)
Change to window window+
*ML(coordinates)
Position the mouse at coordinates and press the left button. ++
*MR(coordinates)
Position the mouse at coordinates and press the right button. ++
\^{f4}
Closing a window
*CW(window)
Make window the new target window for DataLoad.
*SLN or *SL(N)
Sleep for N seconds.
*ML(coordinates)
Position the mouse at coordinates and press the left button.
*MR(coordinates)
Position the mouse at coordinates and press the right button.
*DL(coordinates)
Position the mouse at coordinates and double click the left button.
PROMPT(message)
Prompt the user with message and wait for a response
BACKSPACE
{BACKSPACE}
DELETE
{DELETE}
UP ARROW
{UP}
DOWN ARROW
{DOWN}
LEFT ARROW
{LEFT}
RIGHT ARROW
{RIGHT}
END
{END}
ENTER
{ENTER}
TAB
{TAB}
ESC
{ESC}
HOME
{HOME}
PAGE DOWN
{PGDN}
PAGE UP
{PGUP}
INSERT
{INSERT}
Toggle Numlock1
{NUMLOCK}
Prnt Scrn2
{PRNTSCRN}
F1
{F1}
F2
{F2}
F3
{F3}
F4
{F4}
F5
{F5}
F6
{F6}
F7
{F7}
F8
{F8}
F9
{F9}
F10
{F10}
F11
{F11}
F12
{F12}
F13
{F13}
F14
{F14}
F15
{F15}
F16
{F16}

Special Combination Commands:
Key
Code
SHIFT
+

CTRL
^

ALT
%

Right Alt
&


Note: One important feature which we need to use as per the application speed is Delay. You might have realized, sometimes after entering data in Oracle application when you hit TAB or down key or up key as per your requirement, it takes bit time to process and then the cursor moves to the next field. Exactly the same thing we need to define in the dataload too. Because the dataload application will execute all command and data as mentioned in the data sheet without any time gap. So suppose your application need 0.5 second to process after hitting TAB but in dataload , its 0 second, then what will happen, data load will copy the data of the field and try to paste and take the data from the next field and try to execute in the window but as your application takes bit time to execute the commands, data canot be loaded properly. So to avoid this situation, dataload provides the option of giving manual delay times.
Here you can mention the delays.
Navigation is
Tools à Delays

Steps to Execute Dataload:

  1. Select the command group from the command group drop down
  2. Enter the data along with commands as per your need in the Main Load  data window
  3. Mention the Delays
  4. Select the window where you need to upload the data in the Window drop down
  5. Select the data from where to where you need to upload
  6. Then Hit the Dataload button or from tools select Start Dataload

We will demonstrate a simple use of this tool

Creating 3 locators in a an organization subinventory
Requirement to load data is
Locator, Description, Type, Status, Subinventory
Field Types in oracle are
Locator: Text validated in LOV
Description: Text
Type: Value from Drop down
Status:  Text Validated in LOV
Subinventory: Text Validated in LOV
Dataload data window

Follow the steps as mentioned above after preparation of the dataload


Set the delays
Then Hit Start Load button 

After data load

Now your data load is success.
Read More
Copyright © . Oracle Apps (SCM) . All Rights Reserved
Oracle Apps (SCM) is an online knowledge sharing blog which index online free tutorials, blogs and other sources, to get easier and accessible manner. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. All trademarks, trade names, service marks, copyrighted work, logos referenced herein belong to their respective owners/companies. If any of the posting is violating copyright and you want us to remove that content. Please contact Here