Oracle Timecard (OTL) Automation

Recently i have seen many requests for OTL timecard automations in Oracle. One of the reasons is the financial downturn in the martket.

Many companies have announced furlough for employees- meaning the offices and manufacturing units will be shutdonw for a specified amount of time and it will be mandatory for employees to either take that time off from their vacation time, flex time, may be borrow it from next year or simply be unpaid for that time period. Whatever be the case, it saves company a lot of money that it was otherwise obligated to pay in vacation time or paid days. Now because its mandatory, it has to be in the system for the payroll to pick it up.

If the organization uses time entry system – like oracle – to enter vacation/flex time, and processes payroll then following needs to happen in order for a succesfull furlough automation.

- Employees time must be entered on the online timecard.

- The timecard must be approved by manager or Auto-approval process.

- This OTL timecard must be transferred to Batch Element Entries for Payroll.

To automate this process we will use HXC Timecard APIs provided by Oracle. These APIs help us in creating a timecard for a day, week, and also attach elements/ projects to the timecard. Also, the APIs submit the timecard with a workflow process type so it can either be picked for AUTO processing or Manual approval.

Before we go into the details, we have to see how a typical timecard is built. For the sake of simplicity, we will consider Monthly Paid (exempt) employees.

A timecard is a combination of DAYS. Each DAY will be one Row in a table. For Every day that you need to enter time (e.g. Saturday Sunday will not need a time entry being a holiday) you need a DETAIL type row also. And, for each DETAIL record that has to go into an element or project for costing or payroll purpose, we need an ATTRIBUTE also.

These are nothing but the TIME BUILDING BLOCKS. To make things simple, lets go and see these records:

select * from hxc_time_building_blocks

TYPE: MEASURE ( Number of hours to be put in that particular day) or RANGE (For period like week or day).
SCOPE: TIMECARD, DAY, DETAIL or APPLICATION_PERIOD

Now, if you see a typical timecard, this is how it looks (Use the hierarchical Query below):

TIMECARD (12/1/2008  - 12/7/2008) TYPE: RANGE
               |_  DAY (12/1/2008 – 12/1/2008) TYPE: RANGE
                                    |_  DETAIL (12/1/2008):  8 HRS   (may have attributes)  TYPE:MEASURE
               |_  DAY (12/2/2008 – 12/2/2008)
                                    |_  DETAIL (12/2/2008):  8 HRS                                                      
               |_  DAY (12/3/2008 – 12/3/2008)
                                    |_  DETAIL (12/3/2008):  8 HRS                                                      
               |_  DAY (12/4/2008 – 12/4/2008)
                                    |_  DETAIL (12/4/2008):  8 HRS                                                      
               |_  DAY (12/5/2008 – 12/5/2008)
                                    |_  DETAIL (12/5/2008):  8 HRS                                                      
               |_  DAY (12/6/2008 – 12/6/2008)
               |_  DAY (12/7/2008 – 12/7/2008)

 SELECT htbb.time_building_block_id, htbb.TYPE, htbb.measure, htbb.unit_of_measure, htbb.start_time, htbb.stop_time,htbb.parent_building_block_id, ‘N’ parent_is_new, htbb.SCOPE,htbb.object_version_number, htbb.approval_status,htbb.resource_id, htbb.resource_type, htbb.approval_style_id,htbb.date_from, htbb.date_to, htbb.comment_text,htbb.parent_building_block_ovn, ‘N’ NEW, ‘N’ changed,htbb.application_set_id, htbb.translation_display_key
FROM apps.hxc_time_building_blocks htbb START WITH ( htbb.time_building_block_id in (6848088) AND htbb.object_version_number in (1 ) )CONNECT BY PRIOR htbb.time_building_block_id =  htbb. parent_building_block_id AND PRIOR htbb.object_version_number = htbb.parent_building_block_ovn ORDER BY htbb.time_building_block_id  ASC

NOW Over to APIs….

 

 

 

 

 

 hxc_timestore_deposit.create_timecard_bb 
– Create a Timecard building block (only timecard, no days, or details)
– Here are the parameters it takes
(
   p_start_time => to_date(‘01-DEC-2008 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) ,
   p_stop_time =>  to_date(‘07-DEC-2008 23:59:59′,’DD-MON-YYYY HH24:MI:SS’),
   p_resource_id => emp.person_id ,
   p_comment_text => ‘Automated TimeCard: DEC08′,
   p_approval_style_id => 41, –This is your workflow approval style, default to AUTO APPROVE
   p_app_blocks => l_tbl_timecard_info,
   p_time_building_block_id => l_tc_bb_id –returns the id of  TC Building block
   );

hxc_timestore_deposit.create_day_bb
– Creates a DAY building block (only DAY no details)
– Here are the parameters it takes
(
   p_day => l_start_date,
   p_parent_building_block_id => l_tc_bb_id,
   p_comment_text => ‘Automated TimeCard: DEC08′,
   p_app_blocks => l_tbl_timecard_info,
   p_time_building_block_id => l_day_bb_id
   );

hxc_timestore_deposit.create_detail_bb
– Creates a DETAIL building block, in next step we have to attach the attribute to this DETAIL
– Here are the parameters it takes
(
   p_type => ‘MEASURE’,
   p_measure => 8, –Number of Hours
   p_parent_building_block_id => l_day_bb_id,
   p_comment_text => ‘Automated TimeCard: DEC08′,
   p_app_blocks => l_tbl_timecard_info,
   p_app_attributes => l_tbl_attributes_info,
   p_time_building_block_id => l_detail_bb_st_id
   );
   hxc_timestore_deposit.create_attribute (
   p_building_block_id=> l_detail_bb_st_id,
   p_attribute_name=> ‘Dummy Element Context’,
   p_attribute_value=> ‘ELEMENT – 60110′, –This is the Accrual PTO Element we want to update through this API.
   p_app_attributes=> l_tbl_attributes_info);

HXC_TIMESTORE_DEPOSIT.EXECUTE_DEPOSIT_PROCESS
–This is the Submission Call. This process will submit the timecard, days and details with attributes. Timecard will stay in SUBMITTED State until approved via Manual or AUTO Approve process.
(
   p_validate => FALSE,
   p_app_blocks => l_tbl_timecard_info,
   p_app_attributes => l_tbl_attributes_info,
   p_messages => l_tbl_messages,
   p_mode => ‘SUBMIT’,
   p_deposit_process => ‘OTL Deposit Process’,
   p_retrieval_process => ‘BEE Retrieval Process’,
   p_timecard_id => l_new_timecard_id,
   p_timecard_ovn => l_new_timecard_ovn
   );

 

Hope this article helped you in understanding the basics of the Time Entry APIs. Please note that this is  the initial and basic knowledge. You will need some more knowledge – like DELETE timecards API for rollback in case of any issues. UPDATE timecard APIs etc for a full fledge capability on OTL timecard automation.

Oracle Fixed Assets Useful Tables

Hello Friends , here is some of quite commonly used FA (Fixed Assets) tables and their usage. There are many other tables also in FA but here i am putting only few commonly used tables. for other table if needed we can dig furthur. Let go through below article and let me know if it useful.

1- FA_DEPRN_PERIODS

2- FA_DEPRN_SUMMARY

3- FA_ADDITIONS_B

4- FA_BOOKS

5- FA_CATEGORIES_B

6- FA_DEPRN_DETAIL

FA_DEPRN_PERIODS contains information about your depreciation periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book. PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when you opened and closed each book’s depreciation period. Each time you run the depreciation program, it closes the current period by setting PERIOD_CLOSE_DATE to the system date. It also opens the next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row. CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in FA_CALENDAR_PERIODS.

FA_DEPRN_SUMMARY contains depreciation information for your assets. Each time you run the depreciation program, it inserts one row into thistable for each asset. PERIOD_COUNTER is the period for which you ran the depreciation program. DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period. YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period. DEPRN_RESERVE is the total accumulated depreciation for this asset. DEPRN_SOURCE_CODE tells you what program created the row BOOKS Created by the Depreciation Books form, Quick Additions form, or the post mass additions program when you enter a new asset. DEPRN Created by the depreciation program when you run depreciation. ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset’s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.

For assets that use a diminishing value method, the ADJUSTED_COST is the beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset’s net book value at the time of the adjustment or revaluation. BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted rate to give you the flat rate for the fiscal year. The depreciation program uses this rate to calculate depreciation for an asset. This only applies to assets that use both a flat–rate depreciation method and bonus depreciation.

FA_ADDITIONS_B contains descriptive information to help you identify your assets. Oracle Assets does not use this table to calculate depreciation.When you add an asset, Oracle Assets inserts a row into this table and into FA_ASSET_HISTORY. When you change the asset information stored in this table, Oracle Assets updates it in this table. It also creates a new row in FA_ASSET_HISTORY. When you perform a unit retirement, Oracle Assets reduces the CURRENT_UNITS by the units retired. UNIT_ADJUSTMENT_FLAG is set to YES by the Additions form if you change the number of units for an asset. The Transfers form resets it to NO after you reassign the remaining units. FA_ADJUSTMENTS stores information that Oracle Assets needs to create journal entries for transactions. The posting program creates journal entries for regular depreciation expense from information in FA_DEPRN_DETAIL. Oracle Assets inserts a row in this table for the debit and credit sides of a financial transaction. All the rows for the same transaction have the same value in the TRANSACTION_HEADER_ID column. The SOURCE_TYPE_CODE column tells you which program created the adjustment:

- ADDITION Depreciation program

- ADJUSTMENT Expensed or Amortized Adjustment User Exit

- CIP ADDITION Depreciation program

- CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit

- CIP RETIREMENT Gain/loss program

- DEPRECIATION Depreciation program (Retroactive transactions andexpensed depreciation adjustments)

- RETIREMENT Gain/loss program

- RECLASS Reclassification user exit

- TRANSFER Transfers form

- TAX Reserve Adjustments form

- REVALUATION Mass revaluation program

The ADJUSTMENT_TYPE column tells you which type of account Oracle Assets adjusts. DEBIT_CREDIT_FLAG is DR if the amount is a debit and CR if the amount is a credit. ADJUSTMENT_AMOUNT is the amount debited or credited to the account. ANNUALIZED_ADJUSTMENT is the adjustment amount for a period times the number of periods in a fiscal year. The depreciation program uses it to calculate the depreciation adjustment for an asset when you perform multiple retroactive transactions on the asset. Oracle Assets calculates ADJUSTMENT_PER_PERIOD by dividing the ADJUSTMENT_AMOUNT for a retroactive transaction by the numberof periods between the period you entered the transaction and the period that it was effective. For current period transactions, this columnis zero. PERIOD_COUNTER_CREATED IS the period that you entered the adjustment into Oracle Assets. PERIOD_COUNTER_ADJUSTED is the period to which the adjustment applies. It is the same as PERIOD_COUNTER_CREATED, unless you enter a reserve adjustment, in which case PERIOD_COUNTER_ADJUSTED is the last period of the fiscal year to which the adjustment applies. CODE_COMBINATION_ID indicates the Accounting Flexfield combination Oracle Assets debits or credits for all transactions except reclassifications and intercompany transfers. This CODE_COMBINATION_ID is generated using the Account Generator, and the posting program does not perform any further processing.

 

FA_BOOKS contains the information that Oracle Assets needs to calculate depreciation. When you initially add an asset, Oracle Assets inserts one row into the table. This becomes the ”active” row for the asset. Whenever you use the Depreciation Books form to change the asset’s depreciation information, or if you retire or reinstate it, Oracle Assets inserts another row into the table, which then becomes the new ”active” row, and marks the previous row as obsolete.

At any point in time, there is only one ”active” row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a report for a prior accounting period, Oracle Assets selects the row that was active during that period. You can identify the active row for anasset in a book because it is the only one whose DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_OUT are NULL. When Oracle Assets terminates a row, the DATE_INEFFECTIVE and TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and TRANSACTION_HEADER_IN of the new row, respectively. This means that you can easily identify rows affected by the same transaction because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs.When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the DATE_EFFECTIVE is the system date. When you retire an asset, Oracle Assets inserts a new row to reduce the COST by the amount retired. When you reinstate an asset, Oracle Assets inserts a new row to increase the COST by the COST_RETIRED in the corresponding row in FA_RETIREMENTS.RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread depreciation over the remaining life of an asset after an amortization or revaluation. If you perform a revaluation or an amortized adjustment, Oracle Assets resets the Rate Adjustment Factor to prorate the remaining recoverable net book value over the remaining life. This fraction is calculated as [Recoverable Cost – what Depreciation Reserve would be]/Recoverable Cost. The depreciation program uses this value to adjust the depreciation rate for an asset.

FA_CATEGORIES_B stores information about your asset categories. This table provides default information when you add an asset. The depreciation program does not use this information to calculate depreciation.The Asset Categories form inserts one row in this table for each asset category you define. The Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment.

FA_DEPRN_DETAIL contains the depreciation amounts that the depreciation program charges to the depreciation expense account in each distribution line.

Oracle Assets uses this information to create depreciation expense journal entries for your general ledger.The depreciation program inserts one row per distribution line for an asset each time you run depreciation.

For example, if you assign an asset to two different cost centers, the depreciation program inserts two rows in this table for the asset. DEPRN_AMOUNT is the amount of depreciation expense calculated forthis distribution line.YTD_DEPRN is the year–to–date depreciation allocated to thisdistribution line.When you add an asset, Oracle Assets inserts a row into this table for the period before the current period. This row has the asset cost in the ADDITION_COST_TO_CLEAR column and a DEPRN_SOURCE_CODE of ’B’. This column is used for reporting on new assets. When you run depreciation, Oracle Assets transfers the cost to the COST column in the current period row, this row has a DEPRN_SOURCE_CODE of ’D’.

 

 

Thanks – Shivmohan Purohit

Data flow for Order-to-Cash cycle

Data flow for Order-to-Cash cycle  

Hello friends, here we are having one of the contribution from “Devendra Gulve” , very precise and useful explaination on O2C cycle. hope this be helpful to you.

For more details please visit http://functionalguy.blogspot.com

 

1. Order Entry 
This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.

• Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all
  • No record exists in any other table for this order till now.
• Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID.

2. Order Booking 
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.

• oe_order_headers_alL
• oe_order_lines_all
• wsh_delivery_details
• wsh_delivery_assignments

*In shipping transaction form order status remains “Ready to Release”.

At the same time, Demand interface program runs in background and insert into inventory tables mtl_demand.

3. Reservation 
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the mtl_demand and mtl_reservations table get updated.

4. Pick Release 
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from ‘Release Sales Order’ form or ‘Pick release SRS’ program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from ‘Shipping Transaction form. For this case Pick Release is done from ‘Release Sales Order’ form with Pick Confirm=NO. 
Once pick release is done these are the tables get affected:

• If step 3 is not done then MTL_RESERVATIONS gets updated now.
• wsh_new_deliveries
• wsh_delivery_assignments
• wsh_delivery_details
• MTL_TXN_REQUEST_HEADERS
• MTL_TXN_REQUEST_LINES
• Mtl_material_transactions_temp
• MTL_SERIAL_NUMBERS_TEMP
• MTL_SERIAL_NUMBERS

*In shipping transaction form order status remains “Released to Warehouse” and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5. Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes ‘Picked’ on Sales Order and ‘Staged/Pick Confirmed’ on Shipping Transaction Form.

• MTL_MATERIAL_TRANSACTIONS_TEMP
• oe_order_lines_all
• MTL_MATERIAL_TRANSACTIONS
• mtl_transaction_accounts
• wsh_delivery_details
• wsh_delivery_assignments
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP
• MTL_SERIAL_NUMBERS

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release 

6. Ship Confirm 
Here ship confirm interface program runs in background. Data removed from wsh_new_deliveries. 

The items on the delivery gets shipped to customer at this stage.

• oe_order_lines_all
• wsh_delivery_details
• WSH_SERIAL_NUMBERS
• mtl_transaction_interface
• mtl_material_TRANSACTIONS
• mtl_transaction_accounts
• mtl_demand, MTL_reservations
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP
• MTL_SERIAL_NUMBERS

7. Enter Invoice 

After shipping the order the order lines gets eligible to get transferred to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.

ra_interface_lines_all (interface table into which the data is transferred from order management) Then Auto-invoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in ra_customer_trx_all and ra_customer_trx_lines_all

8. Complete Line 
In this stage order line level table get updated with Flow status and open flag. 
oe_order_lines_all

9. Close Order 
This is last step of Order Processing. In this stage only oe_order_lines_all table get updated. These are the table get affected in this step. 
 
oe_order_lines_all

oe_order_HEADERS_all

 You will get the details of column values getting updated at different stages and table joins information at http://functionalguy.blogspot.com/2008/05/data-flow-for-order-to-cash-cycle.html

devendra gulve — Expert’s page – click here

special Thanks TO dEVENDRA gULVE ( http://functionalguy.blogspot.com/2008/05/data-flow-for-order-to-cash-cycle.html )

Thanks – Shivmohan Purohit