This article gives you a brief understanding and sample code snippet of how to import legacy timesheets into oracle projects.

Steps:

1. Populate the transaction interface table pa_transaction_interface_all.

2. Run the PRC: Transaction Import process for the Transaction Source/ batch name.

3. Run the Distribute Labor Costs process if the transactions are not costed.

Populating the Transaction Interface Table:

Below is a code snippet of how to populate the interface table:

NOTE:This code populates the interface table with the uncosted transactions, so there is no need of populating the cost columns.

If you are importing costed transactions, then you have to supply the denom_raw_cost, acct_raw_cost,burdened_cost, denom_currency_code and other burden cost columns if you are importing the burden amounts as well.

Also if you are importing the GL accounted transactions, then you have to provide the Debit and Credit CCIDs.

Procedure Populate_Interface_Table
Is
CURSOR Txn_Cur IS –Cursor for the transactions that belong to 2007 and beyond
SELECT ‘ABC’ transaction_source, /** Transaction Source should have been defined ***/
‘ABC-BATCH’ batch_name,
hrorg.NAME organization_name,
pa_utils.getweekending (expenditure_item_date) expenditure_ending_date,
expenditure_item_date,
pap.segment1 project_number,
pat.task_number,
‘Internal Labor’ Expenditure_Type,
txn.total_hours,
employee_role,
‘Hours From ABC’ expenditure_comment, ‘P’ transaction_status_code,
txn.txn_reference,
‘Y’ unmatched_negative_txn_flag,
‘N’ billable_flag,
pap.org_id,
txn.employee_number,
fnd_global.login_id created_by,
SYSDATE creation_date,
fnd_global.login_id modified_by,
SYSDATE modified_date
FROM ABC_Legacy_Timesheets txn,
pa_projects_all pap,
pa_tasks pat,
hr_all_organization_units hrorg
WHERE txn.project_id = TO_NUMBER (pap.pm_project_reference)
AND pap.project_id = pat.task_id
AND pat.pm_task_reference = txn.task_id
AND hrorg.organization_id = pap.carrying_out_organization_id
AND txn.valid_flag = ‘Y’
AND txn.converted_flag = ‘N’;

Begin

For txn_rec in txn_cur Loop

Insert into pa_transaction_interface
(
transaction_source ,
batch_name ,
expenditure_ending_date ,
organization_name ,
expenditure_item_date ,
project_number ,
task_number ,
expenditure_type ,
quantity ,
expenditure_comment ,
transaction_status_code ,
orig_transaction_reference ,
unmatched_negative_txn_flag ,
billable_flag ,
org_id ,
employee_number ,
attribute_category ,
attribute1 ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(
txn_rec.transaction_source,
txn_rec.batch_name,
txn_rec.expenditure_ending_date,
txn_rec.organization_name,
txn_rec.expenditure_item_date,
txn_rec.project_number,
txn_rec.task_number,
txn_rec.expenditure_type,
txn_rec.total_hours,
txn_rec.expenditure_comment,
txn_rec.transaction_status_code,
txn_rec.txn_reference,
txn_rec.unmatched_negative_txn_flag,
txn_rec.billable_flag,
txn_rec.org_id,
txn_rec.employee_number,
‘Global Data Elements’, /** Attribute category ***/
txn_rec.employee_role, /** this is attribute1 of the expenditure item **/
txn_rec.created_by,
txn_rec.creation_date,
txn_rec.modified_by,
txn_rec.modified_date
);

End Loop;

commit;

Exception
When OTHERS then
print_log(‘Exception in procedure : Populate_Interface_Table: ‘ || SUBSTR(SQLERRM,1,100));
RAISE;
END;

2. Run the PRC: Transaction Import Process , for the source ‘ABC’ and batch name ‘ABC-BATCH’.

3. Run the Distribute Labor Costs since these transactions are not costed.

Leave a Reply