Friday, January 29, 2010

Business event generation from backed

In the below, if you want you can provide additional informations by extending the v_parameter
declare
v_event_name VARCHAR2(100);
v_event_key VARCHAR2(100);
v_parameter wf_parameter_list_t:=wf_parameter_list_t();
begin
v_event_name:='oracle.apps.wsh.delivery.gen.shipconfirmed';
v_event_key:=51425;
wf_event.raise(p_event_name => v_event_name ,
p_event_key => v_event_key,
p_event_data => NULL,
p_parameters => v_parameter,
p_send_date => sysdate);
COMMIT;
end;

Friday, January 29, 2010 by Team search · 0

Basic Workflow Tables

WF_ACTIVITIES table stores the definition of an activity. Activities can
be processes, notifications, functions or folders. A process activity is a
modelled workflow process, which can be included as an activity in
other processes to represent a sub–process. A notification activity sends
a message to a performer. A functions activity performs an automated
function that is written as a PL/SQL stored procedure. A folder activity
is not part of a process, it provides a means of grouping activities.
WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.
The WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which the
attribute is used as well as the format of the attribute data.
WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time a
message is sent.

by Team search · 0

Detailed informations about basic GL tables

Below gives you the basic tables involved in GL

1- GL_SETS_OF_BOOKS

2- GL_IMPORT_REFERENCES

3- GL_DAILY_RATES

4- GL_JE_LINES

5- GL_PERIODS

6- GL_JE_HEADERS

7- GL_JE_BATCHES

8- GL_BALANCES

9- GL_CODE_COMBINATIONS

GL_SETS_OF_BOOKS

Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES

Stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.

For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES

Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.

For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.

In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table.

GL_JE_LINES

Stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_PERIODS

Stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows inthis table.

GL_JE_HEADERS

Stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_BATCHES

Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.

The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_BALANCES

Stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.

ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.

GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance. Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is notused.

GL_CODE_COMBINATIONS

Stores valid account combinations foreach Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting ordetail budgeting is allowed, and others. Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order. The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.

by Team search · 2

Basic AR Tables informatins related to Receipts

These are the main tables which holds Customer Payment information

  • AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
  • AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
  •  Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
  • AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
  • AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
  • AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
  • Each row includes the amount applied, status, and accounting flexfield information.
 

 
The ER Diagram for a customer Payment can be easily understood as:

 

 

 

by Team search · 0

Transactions Basic Table Structure

Invoice Information resides in the below tables,

1. The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions. One row exists for each invoice, debit memo, commitment, bill receivable, and credit memo that you create in Oracle Receivables. Invoices, debit memos, credit memos, bills receivable, and commitments are distinguished by their associated transaction types, which are stored in the RA_CUST_TRX_TYPES_ALL table.


2. The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. For example, an invoice can have one line for Product A and another line for Product B. Each line requires one row in this table.


3. The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry. The AMOUNT column is required even though this column is null allowed. Receivable uses this information to post the proper amounts to General Ledger.


4. The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table

5. The RA_CUST_TRX_TYPES_ALL table stores information about each transaction type that is used for invoices, commitments, bills receivable, and credit memos. Each row includes Auto Accounting information as well as standard defaults for the invoices that result.

6. AR_PAYMENT_SCHEDULES_ALL holds the payment schedules for the transactions


by Team search · 0

Thursday, January 28, 2010

Ar_receipt_api_pub.Apply

Applies a cash receipt to a particular installment of a debit item.
 The application can also be a cross currency application.
This api works only when you set the org_id using DBMS_APPLICATION.SET_CLIENT_INFO(&v_org_id)
But dont forget to set the session through FND_GLOBAL.APPS_INITIALISE because it will set the
necessary profile values defaulted to user

Another important thing to note is...
Incase if you want to apply against the invoice installment then we have to pass the parameter named
"P_INSTALLMENT" with the installment number
Remember Oralce provides another api named Ar_receipt_api_pub.Create_and_apply to create and apply simultaneously.
Let us have the detailed view of the mentioned api in another post


Script:
declare
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(240);
l_count number;
l_msg_data_out varchar2(240);
l_mesg varchar2(240);
p_count number;
begin
ar_receipt_api_pub.Apply
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_cash_receipt_id => '&cash_receipt_id',
p_customer_trx_id => '&customer_trx_id',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
dbms_output.put_line('Status ' || l_return_status);
dbms_output.put_line('Message count ' || l_msg_count);
if l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '||l_msg_data);
elsif l_msg_count > 1 Then
loop
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
if l_msg_data is NULL Then
exit;
end if;
dbms_output.put_line('Message' || p_count ||'.'||l_msg_data);
end loop;
end if;
end;

if the Status is 'E', check for the error message and debug log.

Thursday, January 28, 2010 by Team search · 1

Ar_receipt_api_pub.Create_and_apply

Creates a cash receipt and applies it to a specified installment of a debit item in one pass.
Application fails if the creation fails due to some reason.
This api works only when you set the org_id using DBMS_APPLICATION.SET_CLIENT_INFO(&v_org_id)
But dont forget to set the session through FND_GLOBAL.APPS_INITIALISE because it will set the
necessary profile values defaulted to user

Script

DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2(240);
l_mesg VARCHAR2(240);
p_count number;
BEGIN
-- Api start here
AR_RECEIPT_API_PUB.create_and_apply
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_amount => &amount,
p_receipt_number => '&receipt_number',
p_receipt_date => '&receipt_date',
p_gl_date => '&gl_date',
p_customer_number => '&customer_number',
p_location => '&location_value',
p_receipt_method_id => &receipt_method_id,
p_trx_number => '&trx_number',
p_cr_id => l_cash_receipt_id );
dbms_output.put_line('Message count ' || l_msg_count);
dbms_output.put_line('Cash Receipt ID ' || l_cash_receipt_id );
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '||l_msg_data);
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
if l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message' || p_count ||' ---'||l_msg_data);
END LOOP;
END IF;
END;
/

If the Status is 'E', check for the error message and debug log.

by Team search · 1

Ar_receipt_api_pub.Unapply

Unapplies the application of a particular installment of a debit item against the specified cash receipt.
This api works only when you set the org_id using DBMS_APPLICATION.SET_CLIENT_INFO(&v_org_id)
But dont forget to set the session through FND_GLOBAL.APPS_INITIALISE because it will set the
necessary profile values defaulted to user

Script:
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2(240);
l_mesg VARCHAR2(240);
p_count number;
BEGIN
-- Api Starts Here
AR_RECEIPT_API_PUB.unapply
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cash_receipt_id => &cash_receipt_id,
p_applied_payment_schedule_id => &applied_payment_schedule_id,
p_reversal_gl_date => '&reversal_gl_date'
);
dbms_output.put_line('Message count ' || l_msg_count);
dbms_output.put_line('Cash Receipt ID ' || l_cash_receipt_id );
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '||l_msg_data);
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
if l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message' || p_count ||' ---'||l_msg_data);
END LOOP;
END IF;
END;

by Team search · 0

Ar_receipt_api_pub.Create_cash

Creates a single cash receipt, as in the case of manually created cash receipts.
This api works only when you set the org_id using DBMS_APPLICATION.SET_CLIENT_INFO(&v_org_id)
But dont forget to set the session through FND_GLOBAL.APPS_INITIALISE because it will set the
necessary profile values defaulted to user
Script:

DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2(240);
l_mesg VARCHAR2(240);
p_count number;
BEGIN
AR_RECEIPT_API_PUB.create_cash
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => '&currency_code',
p_amount => &amount,
p_receipt_number => '&receipt_number',
p_receipt_date => '&receipt_date',
p_gl_date => '&gl_date',
p_customer_number => '&customer_number',
p_receipt_method_id => &receipt_method_id,
p_cr_id => l_cash_receipt_id );
dbms_output.put_line('Status ' || l_return_status);
dbms_output.put_line('Cash Receipt id ' || l_cash_receipt_id );
dbms_output.put_line('Message count ' || l_msg_count);
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '||l_msg_data);
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message' || p_count ||'.'||l_msg_data);
END LOOP;
END IF;
END;

If the Status is 'E', check for the error message and debug log.

by Team search · 0

Query to find the parameter information of the concurrent program

In the below query, we have to give the program short name to find the parameter info

SELECT B.ROWID ROW_ID           ,
  B.RUNTIME_PROPERTY_FUNCTION    ,
  B.APPLICATION_ID               ,
  B.DESCRIPTIVE_FLEXFIELD_NAME   ,
  B.DESCRIPTIVE_FLEX_CONTEXT_CODE,
  B.APPLICATION_COLUMN_NAME      ,
  B.END_USER_COLUMN_NAME         ,
  B.LAST_UPDATE_DATE             ,
  B.LAST_UPDATED_BY              ,
  B.CREATION_DATE                ,
  B.CREATED_BY                   ,
  B.LAST_UPDATE_LOGIN            ,
  B.COLUMN_SEQ_NUM               ,
  B.ENABLED_FLAG                 ,
  B.REQUIRED_FLAG                ,
  B.SECURITY_ENABLED_FLAG        ,
  B.DISPLAY_FLAG                 ,
  B.DISPLAY_SIZE                 ,
  B.MAXIMUM_DESCRIPTION_LEN      ,
  B.CONCATENATION_DESCRIPTION_LEN,
  B.FLEX_VALUE_SET_ID            ,
  B.RANGE_CODE                   ,
  B.DEFAULT_TYPE                 ,
  B.DEFAULT_VALUE                ,
  B.SRW_PARAM                    ,
  T.FORM_LEFT_PROMPT             ,
  T.FORM_ABOVE_PROMPT            ,
  T.DESCRIPTION
   FROM FND_DESCR_FLEX_COL_USAGE_TL T,
  FND_DESCR_FLEX_COLUMN_USAGES B
  WHERE B.APPLICATION_ID            = T.APPLICATION_ID
AND B.DESCRIPTIVE_FLEXFIELD_NAME    = T.DESCRIPTIVE_FLEXFIELD_NAME
AND B.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND B.APPLICATION_COLUMN_NAME       = T.APPLICATION_COLUMN_NAME
AND T.LANGUAGE                      = 'US'
and b.DESCRIPTIVE_FLEXFIELD_NAME like '&Program_short_name'  

by Team search · 0

Inventory On-hand quantity Interface

This interface lets you import the on hand inventory into Oracle.

Interface tables:

MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)

Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. 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

Validations:

Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Account  --- GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias --- MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule ---WIP_ENTITIES.WIP_ENTITY_ID
Sales Order --- MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.

MTL_TRANSACTION_LOTS_INTERFACE:

TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)

MTL_SERIAL_NUMBERS_INTERFACE:

TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER

by Team search · 0

Item import (Item conversion)

The Item Interface lets you import items into Oracle Inventory.

Pre-requisites:

Creating an Organization
Code Combinations
Templates
Defining Item Status Codes
Defining Item Types

Interface tables:

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
MTL_INTERFACE_ERRORS (View errors after import)

Concurrent Program:

Item import

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 any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.

Base Tables:

MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES

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
Load child record if no error found.

Some important columns that need to populated in the interface tables:

MTL_SYSTEM_ITEMS_INTERFACE:

PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)

MTL_ITEM_CATEGORIES_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER.
ORGANIZATION_ID or ORGANIZATION_CODE or both.
TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not possible through Item Import).
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
CATEGORY_ID or CATEGORY_NAME or both.
PROCESS_FLAG = 1
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)

MTL_ITEM_REVISIONS_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the ORGANIZATION_ID or ORGANIZATION_CODE or both)
 REVISION
CHANGE_NOTICE
ECN_INITIATION_DATE
IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER
EFFECTIVITY_DATE
ATTRIBUTE_CATEGORY
ATTRIBUTEn
REVISED_ITEM_SEQUENCE_ID
DESCRIPTION
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1

Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

by Team search · 0

Upload Lookup values

Below API can be used for uploading lookup values,

APPS_APPLMGR.FND_LOOKUP_VALUES_PKG.INSERT_ROW

(X_ROWID,
LOOKUP_TYPE,
0,
3,
LOOKUP_CODE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Y',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
LOOKUP_MEANING,
NULL,
SYSDATE,
0,
SYSDATE,
0,
0
);

by Team search · 0

Table Suffixes of EBS

Very common thing, people get confuse why such type of naming convention oracle have used. Here is note for some of them:

_ALL: Table holds all the information about different operating units. Multi-Org environment. You can also set the client info to specific operating unit to see the data specific to that operating unit only.

_TL: are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_B: these are the BASE tables.
They are very important and the data is stored in the table with all validations.
It is supposed that these tables will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.

_F: these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.

_V: tables are the views created on base tables

_VL: are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV (’LANG’).

_S: are sequences, used for finding new values for the primary key of a table.

_A: are Audit Shadow Tables

_AVN and _ACN: are Audit Shadow Views (when data was changed, and with what values







by Team search · 0

Query to get the Number of Seconds or Minutes or Hours between 2 given days:-


For the Seconds:-
-------------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For Minutes:-
---------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For the Hours:-
-----------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

This is the simple decode function made for all the above 3 requirements. I hope this will help you.





by Team search · 0

AR Data model Receipts 11i


by Team search · 0

AR Data Model 11i


by Team search · 0

How Inventory Passes Journals To General Ledger (GL)

Inventory Transactions transfer to GL normally done by Program called INCTGL.

Inventory to GLThe accounting information related to transactions in INV module is transferred to GL by running the ‘Transfer Transactions to GL’ concurrent program. This is part of ‘Period End Process' . However, you can run explicitly any time during an open period. When you run this process and in case if more than one period is open, all the transactions from the earliest open period to the date up to which the process is run (Transfer Date) are considered for transfer to GL.

When you run this program all the accounting information is inserted into gl_interface table. ‘Journal Import’ and ‘Post Journals’ processes in Oracle General Ledger can then be used to post the information in General Ledger.

The level of detail that is transferred to GL for the accounting information related to these transactions depends on the value chosen in the Organization Parameters Screen

Navigation: Oracle Inventory => Setup => Organization => Parameters

Each parameter has there own significance.

1. Organization Code : This is one of inventory organization, for which the gl transfer has to be done.
2. Date :A date in any open period.
3. Current Period
4. GL Description

The possible values for the ‘Transfer to GL’ field (in Organization Parameters Screen) are – Detail, Summary, None.

* When Summary is selected :The accounting information pertaining to the transactions is summarized and the summary entries are posted to GL. One of the advantage of running the process in 'Summary' mode over 'Detail' mode is minimising the posting times.
* When ‘Detail’ is selected :The detailed accounting entries are transferred to GL. In this case, the posting times are larger due to a larger number of records. The advantage of this mode is creation of detailed accounting records in GL for every transaction in Inventory/WIP.
* When none is selected: This case no transfer of accounting information to GL is done for this organization.
Running ‘Transfer to GL’ process
You can run Transfer Transactions to GL process can be run from Oracle Inventory
Accounting Close Cycle .. General Ledger Transfers
These are the tables which get affected By transfer Program.
* GL_INTERFACE : Based out of Program logic, new row inserted into this table. The connecting link are established by these columns.GROUP_ID, REFERENCE21, REFERENCE22,ACCOUNTING_DATE,
USER_JE_CATEGORY_NAME,GL_SL_LINK_TABLE
Action on this table :Insert
* MTL_TRANSACTION_ACCOUNTS : Mtl_transaction_accounts holds the accounting information for each material transaction in mtl_material_transactions.
Action on this table :Select/Update
* WIP_TRANSACTION_ACCOUNTS :This table stores the accounting information for each resource transaction in wip_transactions.
Action on this table :Select/Update
* MTL_PARAMETERS :This table maintains a set of defaults and controls like general ledger accounts; locator, lot, and serial controls; inter-organization options; costing method; etc. for each organization defined in Oracle Inventory.
Action on this table :Select
* ORG_GL_BATCHES :This table holds history rows for every batch of transactions that have been interfaced to the gl_interface table.
Action on this table :Insert/Delete/Update
* ORG_ACCT_PERIODS :This table holds the open and closed financial periods for organizations.
Action on this table :Select
* ORG_ORGANIZATION_DEFINITIONS : Defination of the Org is derived from this table
Action on this table :Select
* GL_SET_OF_BOOKS : Table holds the setof book data used for joining purpose.
Query for Subledger Transfer to GL
If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.

SELECT gjh.period_name "Period name"
,gjb.name "Batch name"
,gjjlv.header_name "Journal entry"
,gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
,mmt.subinventory_code "Subinventory"
,glcc3.segment4 "Costcenter"
,gjjlv.line_entered_dr "Entered debit"
,gjjlv.line_entered_cr "Entered credit"
,gjjlv.line_accounted_dr "Accounted debit"
,gjjlv.line_accounted_cr "Accounted credit"
,gjjlv.currency_code "Currency"
,mtt.transaction_type_name "Transaction type"
,TO_CHAR(mta.transaction_id)"Transaction_number"
,mta.transaction_date "Transaction_date"
,msi.segment1 "Reference"
FROM apps.gl_je_journal_lines_v gjjlv,
gl_je_lines gje,
mtl_transaction_accounts mta,
mtl_material_transactions mmt,
mtl_system_items_b msi,
gl_je_headers gjh,
gl_je_batches gjb,
apps.gl_code_combinations_kfv glcc,
apps.gl_code_combinations_kfv glcc2,
mtl_secondary_inventories msin,
mtl_transaction_types mtt,
MTL_SECONDARY_INVENTORIES cost,
gl_code_combinations glcc3
WHERE gjjlv.period_name BETWEEN 'NOV-2008' AND 'DEC-2008'
AND gje.code_combination_id = gje.code_combination_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gl_sl_link_table = 'MTA'
AND gjjlv.je_header_id = gje.je_header_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND gje.je_header_id = gjh.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND mmt.organization_id = msi.organization_id
AND mmt.organization_id = msin.organization_id
AND mmt.subinventory_code= msin.secondary_inventory_name
AND mta.gl_sl_link_id= gje.gl_sl_link_id
AND mta.reference_account = glcc.code_combination_id
AND msin.expense_account = glcc2.code_combination_id
AND mmt.transaction_id = mta.transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id
AND cost.organization_id(+) = mmt.organization_id
AND cost.secondary_inventory_name(+) = mmt.subinventory_code
AND glcc3.code_combination_id(+) = cost.expense_account

by Team search · 0

Journal Import

What is Journal Import?
A) Journal import is an interface used to bring journal entries from legacy systems and other modules into the General Ledger.(Specifically Journal Import gets entries from legacy data into the GL base tables.

The tables populated during journal Import are

GL_JE_BATCHES,

GL_JE_HEADERS,

GL_JE_LINES,

GL_IMPORT_REFERENCES


Read More -->

by Team search · 2

Query used to fetch locking tables

Select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID
','
S.SERIAL# Kill,
U1.NAME
'.'
substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5

Alter system kill session ''

by Team search · 0

Friday, January 22, 2010

Supplier and Bank Info in R12

The data model for storing Banks and Bank Account information has changed for this release of the
Oracle Applications Suite.

Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They
are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both
Bank to Branch and also from Branch to Bank.

The Bank Accounts themselves are now stored in the new Oracle Payments Application. All tables are
prefixed with the Application Short Name, IBY. The bank accounts themselves are stored in the
IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the
relevant Bank and Branch Parties in the HZ_PARTIES table.

Now, linking the Bank Account to the relevant Supplier is a bit more involved. The table
IBY_ACCOUNT_OWNERS can be used to identify the Supplier Party that the Bank Account belongs to.
This is done through linking together the following tables IBY_EXTERNAL_PAYEES_ALL and
IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table
IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the
bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument
Record links to an External Payee Record held in IBY_EXTERNAL_PAYEES_ALL using the
ext_pmt_party_id. It is the External Payee Record that links us to a Supplier Party ID (payee_party_id),
Supplier Party Site ID (party_site_id) and Supplier Site ID (supplier_site_id).

There is a record stored in the IBY_EXTERNAL_PAYEES_ALL table for every Supplier Site defined
and for the supplier itself (Bank Accounts can be defined at supplier level too). The
IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site/Supplier that the Bank Account has
been assigned to.

As an added complexity in R12, links to Suppliers are now created in the TCA. Suppliers have a Party
Record and Supplier Sites have Party Site Records. As part of this functionality shift, Suppliers and
their Sites have now moved to AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL (although the
unique keys are still called VENDOR_ID and VENDOR_SITE_ID respectively!!). The old PO tables
used in 11i and before are now created as views which link the Supplier Records to their related TCA records (i.e. PO_VENDORS links AP_SUPPLIERS with HZ_PARTIES and
PO_VENDOR_SITES_ALL links AP_SUPPLIER_SITES_ALL with HZ_PARTY_SITES).

Friday, January 22, 2010 by Team search · 1

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.