Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Thursday, September 29, 2016

How to enable tracing for WEBADI

It’s very difficult track, and sees what went wrong when working on Web ADI either it is custom layout or it is custom integrators.
1)      First below 3 profile options need to be set
BNE Server Log Filename              : BneLogger.log (make sure it doesn't exist and if it does, delete it)
BNE Server Log Level                      : TRACE
BNE Server Log Path                       : free to choose, enter your own path
If you want to debug, track development errors then you must set below
BNE Server Log Filename              : BneLogger.log (This value is default, don’t change)
BNE Server Log Level                      : Statement
BNE Server Log Path                       : Normally /tmp value exists, if you want to change you can change your own.
2)      Bounce/restart Apache
3)      Create your document
If you have already created menu, and separate function for the document open function, and create some records and try to upload till you replicate the issues.
4)      Now it view to log file, You can refer other post Easy way to   Oracle Web ADI View Log, clear log
5)      Once you have done your work, just change your profile value  “BNE Server Log Level : INFORMATION “ level to avoid the growth of the BNELogger.Log
References:
http://anilbejugam.blogspot.com/2014/05/oracle-web-adi.html

Monday, September 26, 2016

What is a database Trigger?

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

Syntax for Creating a Trigger

 CREATE [OR REPLACE ] TRIGGER trigger_name 
 {BEFORE | AFTER | INSTEAD OF } 
 {INSERT [OR] | UPDATE [OR] | DELETE} 
 [OF col_name] 
 ON table_name 
 [REFERENCING OLD AS o NEW AS n] 
 [FOR EACH ROW] 
 WHEN (condition)  
 BEGIN 
   --- sql statements  
 END; 

  • CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
  • [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
  • [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
  • WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.
1) Create the 'product' table and 'product_price_history' table
CREATE TABLE product_price_history 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) ); 

CREATE TABLE product 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) ); 
2) Create the price_history_trigger and execute it.
CREATE or REPLACE TRIGGER price_history_trigger 
BEFORE UPDATE OF unit_price 
ON product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES 
(:old.product_id, 
 :old.product_name, 
 :old.supplier_name, 
 :old.unit_price); 
END; 
/ 
3) Lets update the price of a product.
UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100
Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.
1) Row level trigger - An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger - An event is triggered for each sql statement executed. 

PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.
For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.
CREATE TABLE product
(Message varchar2(50), 
 Current_Date number(32)
);
Let's create a BEFORE and AFTER statement and row level triggers for the product table.
1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' before a sql update statement is executed, at the statement level.
CREATE or REPLACE TRIGGER Before_Update_Stat_product 
BEFORE 
UPDATE ON product 
Begin 
INSERT INTO product_check 
Values('Before update, statement level',sysdate); 
END; 
/ 
2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row is updated.
 CREATE or REPLACE TRIGGER Before_Upddate_Row_product 
 BEFORE 
 UPDATE ON product 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO product_check 
 Values('Before update row level',sysdate); 
 END; 
 / 
3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' after a sql update statement is executed, at the statement level.
 CREATE or REPLACE TRIGGER After_Update_Stat_product 
 AFTER 
 UPDATE ON product 
 BEGIN 
 INSERT INTO product_check 
 Values('After update, statement level', sysdate); 
 End; 
 / 
4) AFTER UPDATE, Row Level: This trigger will insert a record into the table 'product_check' after each row is updated.
 CREATE or REPLACE TRIGGER After_Update_Row_product 
 AFTER  
 insert On product 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO product_check 
 Values('After update, Row level',sysdate); 
 END; 
 / 
Now lets execute a update statement on table product.
 UPDATE PRODUCT SET unit_price = 800  
 WHERE product_id in (100,101); 
Lets check the data in 'product_check' table to see the order in which the trigger is fired.
 SELECT * FROM product_check; 
Output:
Mesage                                             Current_Date
------------------------------------------------------------
Before update, statement level          26-Nov-2008
Before update, row level                    26-Nov-2008
After update, Row level                     26-Nov-2008
Before update, row level                    26-Nov-2008
After update, Row level                     26-Nov-2008
After update, statement level            26-Nov-2008
The above result shows 'before update' and 'after update' row level events have occured twice, since two records were updated. But 'before update' and 'after update' statement level events are fired only once per sql statement.
The above rules apply similarly for INSERT and DELETE statements.

How To know Information about Triggers.

We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.
The below statement shows the structure of the view 'USER_TRIGGERS'
 DESC USER_TRIGGERS; 
NAME                              Type
--------------------------------------------------------
TRIGGER_NAME                 VARCHAR2(30)
TRIGGER_TYPE                  VARCHAR2(16)
TRIGGER_EVENT                VARCHAR2(75)
TABLE_OWNER                  VARCHAR2(30)
BASE_OBJECT_TYPE           VARCHAR2(16)
TABLE_NAME                     VARCHAR2(30)
COLUMN_NAME                  VARCHAR2(4000)
REFERENCING_NAMES        VARCHAR2(128)
WHEN_CLAUSE                  VARCHAR2(4000)
STATUS                            VARCHAR2(8)
DESCRIPTION                    VARCHAR2(4000)
ACTION_TYPE                   VARCHAR2(11)
TRIGGER_BODY                 LONG
This view stores information about header and body of the trigger.
SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product'; 
The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'.
You can drop a trigger using the following command.
DROP TRIGGER trigger_name;

CYCLIC CASCADING in a TRIGGER

This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situtation does not exist.
The below example shows how Trigger's can enter into cyclic cascading.
Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created.
1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.
In such a situation, when there is a row inserted in table 'abc', triggerA fires and will update table 'xyz'.
When the table 'xyz' is updated, triggerB fires and will insert a row in table 'abc'.
This cyclic situation continues and will enter into a infinite loop, which will crash the database.

References:
http://plsql-tutorial.com/plsql-triggers.htm
https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

Monday, September 19, 2016

Tables in Cash Management R12

CE_BANK_ACCOUNTS:

CE_BANK_ACCOUNTS contains Legal Entity Level bank account information. Each bank account must be affiliated with one bank branch.  

CE_BANK_ACCT_USES_ALL:

CE_BANK_ACCT_USES_ALL stores Operating Unit level bank account use information.

CE_GL_ACCOUNTS_CCID: 

CE_GL_ACCOUNTS_CCID stores information about code combination ids per your bank account uses

 CE_INTEREST_RATES:

This table stores interest rate information  

CE_INTEREST_SCHEDULES:

This table stores interest schedule information

 CE_BANK_ACCT_BALANCES:

This table stores the internal bank account balances. 

CE_PROJECTED_BALANCES:

This table stores the projected balances of internal bank accounts.

CE_CASHFLOWS:

Table for storing Cashflows

CE_PAYMENT_TRANSACTIONS: 

Table for storing Bank Account Transfer 


CE_PAYMENT_TEMPLATES:

Table for storing payment templates 


CE_TRXNS_SUBTYPE_CODES:

Table for storing transaction subtype codes 


CE_CONTACT_ASSIGNMENTS:

This table contains the information about which level  (bank, branch, account) the contact is assigned to.

CE_AP_PM_DOC_CATEGORIES: 

This table stores payment document categories and payment methods for bank account uses

CE_PAYMENT_DOCUMENTS: 

This table stores payment document information.

CE_SECURITY_PROFILES_GT:

CE.CE_SECURITY_PROFILES_GT is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data in the table is temporary. It has a data duration of SYS$SESSION. Data is removed at the end of this period.

CE_AVAILABLE_TRANSACTIONS_TMP: 

CE.CE_AVAILABLE_TRANSACTIONS_TMP is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data in the table is temporary. It has a data duration of SYS$SESSION. Data is removed at the end of this period.

CE_CHECKBOOKS: 

This table stores payment check book information.

CE_STATEMENT_RECONCILS_ALL: 

The CE_STATEMENT_RECONCILS_ALL table stores information about reconciliation history or audit trail. Each row represents an action performed against a statement line.


CE_ARCH_RECONCILIATIONS_ALL:

The CE_ARCH_RECONCILIATIONS_ALL table stores information about archived statement reconciliation details. A row in this table corresponds to an archived CE_STATEMENT_RECONCILES_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive.


CE_ARCH_HEADERS:

The CE_ARCH_HEADERS_ALL table stores archived statement header information. Each row in this table corresponds to an archived CE_STATEMENT_HEADERS_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive.

CE_ARCH_INTRA_HEADERS: 

The CE_ARCH_INTRA_HEADERS_ALL table stores archived intra-day statement header 
information. Each row in this table corresponds to an archived CE_INTRA_STMT_HEADERS_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive.

CE_ARCH_INTERFACE_HEADERS: 

The CE_ARCH_INTERFACE_HEADERS_ALL table stores archived statement interface information. Each row in this table corresponds to an archived CE_STATEMENT_HEADERS_INT_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive, or by the AutoReconciliation program once you enable your system options to automatically purge and archive statement interface tables.


CE_INTRA_STMT_HEADERS:

The CE_INTRA_STMT_HEADERS_ALL table stores intra-day bank statement header information. Each row in this table contains the statement date, statement number, bank account identifier, and other information about the intra-day statement.

CE_STATEMENT_HEADERS: 

The CE_STATEMENT_HEADERS_ALL table stores bank statements. Each row in this table contains the statement name, statement date, GL date, bank account identifier, and other information about the statement. This table corresponds to the Bank Statement window of the Bank Statements form.
Once you have marked your statement as complete, the STATEMENT_COMPLETE_FLAG is set to Y, and you can no longer modify or update the statement.
AUTO_LOADED_FLAG is set to Y when your statement is uploaded from the interface table using the Bank Statement Import program.


CE_CASHPOOLS:

This table stores header information about the cash pool 


CE_STATEMENT_LINES:

The CE_STATEMENT_LINES table stores information about bank statement lines. Each row in this table stores the statement header identifier, statement line number, associated transaction type, and transaction amount associated with the statement line. 
This table corresponds to the Bank Statement Lines window of the Bank Statements form.

Cash Management Reconciliation

1) Run the program- Bank Statement Loader to load data in following Open Interface tables

CE_STATEMENT_HEADERS_INT_ALL (Stores Information about bank statement Header details)
CE_STATEMENT_LINES_INTERFACE (Stores Information about bank statement Line details)

2) Run the program- Bank Statement Import to load data in following Base tables from Interface tables.

CE_STATEMENT_HEADERS
CE_STATEMENT_LINES

3) Error information is stored in following tables while running Bank Statement Import

CE_HEADER_INTERFACE_ERRORS
CE_LINE_INTERFACE_ERRORS

4) Run the program- AutoReconciliation which in turn calls AutoReconciliation Execution Report (CEXINERR)
(AutoReconciliation --> CE_AUTO_BANK_REC.statement --> CE_AUTO_BANK_MATCH.match_process)

5) Error information is stored in following table while running AutoReconciliation Program

CE_RECONCILIATION_ERRORS

6) Reconciliation history or audit trail is stored in table- CE_STATEMENT_RECONCILS_ALL. Each row represents an action performed against a statement line.

7) Transactions from all the views below are consolidated into CE_AVAILABLE_TRANSACTIONS_V for bank statement reconcillation

CE_101_TRANSACTIONS_V -- GL journal entry lines 

CE_185_TRANSACTIONS_V -- Treasury Transactions

CE_200_TRANSACTIONS_V -- AP payments

CE_222_TRANSACTIONS_V -- AR cash receipts 

CE_260_TRANSACTIONS_V -- Bank statement lines available for reconciling bank errors

CE_801_TRANSACTIONS_V -- Payroll payments

References:
http://www.oracleerp4u.com/2010/10/cash-management-reconciliation.html

Saturday, September 17, 2016

Oracle Serial Number

Here is a list of common serial number statuses and ids:

Status Codes (ID and Description)
---------------------
1 Defined but not used
3 Resides in stores
4 Issued out of stores
5 Resides in intransit
6 Pending status 
7 Resides in receiving
8 Resides in WIP 

You can get a full list of the codes with the following SQL:
SELECT
   lookup_type,
   lookup_code,
   meaning
FROM mfg_lookups
WHERE lookup_type = 'SERIAL_NUM_STATUS'
ORDER BY lookup_type, lookup_code;


Here is a list of common serial control code ids and there descriptions:

Control Codes (ID and Description)
---------------------
1 No serial number control
2 Predefined serial numbers
5 Dynamic entry at inventory receipt
6 Dynamic entry at sales order issue 
You can get a full list of the codes with the following SQL
SELECT
   lookup_type,
   lookup_code,
   meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_SERIAL_NUMBER'
ORDER BY lookup_type, lookup_code;


Here is a version of the query from R12.1 - The actual query is against the view RCV_TRX_INT_SERIALS_V but the SQL below shows the underlying tables: (Receiving Transaction Form)

SELECT rss.serial_num
FROM rcv_serials_supply rss, rcv_shipment_lines rsl
WHERE rss.shipment_line_id = rsl.shipment_line_id
AND upper(serial_num) LIKE '&yourserial')
AND rss.shipment_line_id = &yourshiplineid
AND (rss.lot_num IS NULL OR rss.lot_num = '&YourLot')
AND EXISTS
(SELECT 1
FROM mtl_serial_numbers msn
WHERE msn.serial_number = rss.serial_num
AND msn.current_status = 5
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1)
AND (msn.line_mark_id IS NULL
OR msn.line_mark_id = -1)
AND (msn.lot_line_mark_id IS NULL
OR msn.lot_line_mark_id = -1)
AND msn.inventory_item_id = rsl.item_id
)
ORDER BY rss.serial_num;

INV: Restrict Receipt of Serials?

Here is a query that might help you look for serials that were received again after being shipped. You might add additional logic to check for the existence of a customer install base:
a. This one gives the list of serials shipped then received back:
select mut.serial_number, mut.organization_id
from mtl_unit_transactions mut, mtl_material_transactions mmt
where ((mut.transaction_source_type_id = 11 -- inventory transaction
and mmt.transaction_type_id = 42) -- misc. receipt
or (mut.transaction_source_type_id = 3 -- account transaction
and mmt.transaction_type_id = 40) -- account receipt
or (mut.transaction_source_type_id = 6 -- account alias transaction
and mmt.transaction_type_id = 41)) -- account alias receipt
and mut.transaction_id = mmt.transaction_id
and exists
(select 1 from mtl_unit_transactions mut2
where mut2.transaction_id != mut.transaction_id
and mut2.transaction_date < mut.transaction_date
and mut2.transaction_source_type_id IN (2,8) -- sales/internal order
and mut.serial_number = mut2.serial_number
);

b. This gives a summary count by status and organization of serials shipped and received back:
select msn.current_status, msn.current_organization_id, count(*)
from mtl_serial_numbers msn
where msn.serial_number IN
(select mut.serial_number, mut.organization_id
from mtl_unit_transactions mut, mtl_material_transactions mmt
where ((mut.transaction_source_type_id = 11 -- inventory transaction
and mmt.transaction_type_id = 42) -- misc. receipt
or (mut.transaction_source_type_id = 3 -- account transaction
and mmt.transaction_type_id = 40) -- account receipt
or (mut.transaction_source_type_id = 6 -- account alias transaction
and mmt.transaction_type_id = 41)) -- account alias receipt
and mut.transaction_id = mmt.transaction_id
and exists
(select 1 from mtl_unit_transactions mut2
where mut2.transaction_id != mut.transaction_id
and mut2.transaction_date < mut.transaction_date
and mut2.transaction_source_type_id IN (2,8) -- sales/internal order
and mut.serial_number = mut2.serial_number
))
group by msn.current_status, msn.current_organization_id;


How to track a serial number across organizations
prompt
accept YourSerial DEFAULT 'JBP10000' prompt 'Please enter your serial (Default JBP10000): '
prompt
break on serial_number

select c.serial_number, a.transaction_id, a.transaction_type_id, a.transaction_quantity, 'No Lot'
from MTL_MATERIAL_TRANSACTIONS a, MTL_UNIT_TRANSACTIONS c
where a.TRANSACTION_ID = c.TRANSACTION_ID
  and c.serial_number like '&YourSerial'
UNION
select c.serial_number, a.transaction_id, a.transaction_type_id, a.transaction_quantity, b.lot_number
from MTL_MATERIAL_TRANSACTIONS a, MTL_TRANSACTION_LOT_NUMBERS b, MTL_UNIT_TRANSACTIONS c
where a.TRANSACTION_ID = b.TRANSACTION_ID
  and b.SERIAL_TRANSACTION_ID   = c.TRANSACTION_ID
  and c.serial_number like '&YourSerial'
/


How to enter serial number descriptions (or Descriptive Flexfields / DFF)?

You can enter descriptions for serial numbers using descriptive flexfields.
There are two that would be helpful: Serial Attributes or Serial numbers
Serial attributes apper in the onhand > serial number screen in the Serial Info tab with the column name of Attributes. This is similar to the serial entry screens that label the field attributes as well.  The Serial numbers descriptive flexfield is visible upon placing the cursor in the descriptive flexfield field usually labled with two brackets like [] also called mug-handles.

To maintain descriptive flexfields for serial numbers:
Navigate to Inventory > Setup > Flexfield > Descriptive > Segments
Query Serial Attributes or Serial numbers
You could then setup descriptions for the serials

Performance Issues with Serial Numbers
For example, one customer found this query to be especially slow working with serials in the Transact Move Order form.  They found the query by running a trace on the process using Help > Diagnostics > Trace.  Selecting one serial was taking over a minute as there were 3 million available serial numbers in MTL_SERIAL_NUMBERS. Here is the SQL that had bad performance:
SELECT MSN.SERIAL_NUMBER
FROM
MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT WHERE
  MSNT.TRANSACTION_TEMP_ID = DECODE(:B5 , 1, :B4 , :B3 ) AND
  MSN.CURRENT_ORGANIZATION_ID = :B2 AND MSN.INVENTORY_ITEM_ID = :B1 AND
  MSN.SERIAL_NUMBER BETWEEN MSNT.FM_SERIAL_NUMBER AND
  NVL(MSNT.TO_SERIAL_NUMBER, MSNT.FM_SERIAL_NUMBER) AND
  LENGTH(MSN.SERIAL_NUMBER) = LENGTH(MSNT.FM_SERIAL_NUMBER)

References:
http://sairamgoudmalla.blogspot.com/2015/03/oracle-serial-number.html