Expense report number postfixed with "-1"

When an user submitted an expense report, his expense report number was discarded and a new expense report was generated with "-1" postfixed. He came up with the following questions.

Question: Why does my original expense report have a "-1" after it?
Answer: The expense report was short-paid by Accounts Payable – probably for lack of a receipt. Please contact your AP department and submit receipts where required.

Question: Why do I have a new expense report number?
Answer: The original expense report included two or more items. At least one item was short-paid by Accounts Payable. The original expense report was partially paid. The short-paid amount was given a document ID with a “-1” suffix. A new expense report was created to capture the amount of the short payment. Ultimately, there will be two payments which are associated with two different expense report numbers.

Workflow Issue

Some time back we had a requirement to resolve the timecard workflows that were in error status at PROD. After some analysis we found that someone has recompiled the older version of the workflow at PROD and as the underlying workflow package was referring to some attributes which were missing in the older workflow the process was going in error status. We had a difficult task ahead..

As it was a weekend there were many timecards that were been submitted and went in error status and all these timecards have to be approved in another couple of days by there respective approving authority failing which these timecards would not be considered for accounting.. Initially to resolve the issue the only way we thought would be possible is to recompile the original workflow, cancel all the timecards manually at PROD and inform all the users with error timecards to resubmit again. Informing to all the users of this count was a hectic task.

We did some further investigations in the workflow developer’s guide and found that we can actually abort the existing process and resubmit the timecards with same item key without the need cancel and inform the users to resubmit the timecards. We tried this process initially at DEV instance and found that it really worked as we expected.

In short the process what we followed is as follows:
1. Recompiled the original workflow.
2. Created a temp table to store the important attribute values for the error workflows.
3. Performed the Abort, Purge, Create and Start process using standard oracle workflow packages. (Before Starting the new process we set the workflow attributes from the temp table.). The below is the code which we used to perform all the process mentioned in the step 3:

/************************************************************
1. Below code is used to retrieve the important attribute values from the workflow table for the error timecards and store them in a temp table.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT DISTINCT item_key
FROM wf_item_activity_statuses
WHERE activity_status = 'ERROR'
AND activity_result_code = '#EXCEPTION'
AND item_type = 'PATCARD'
AND error_stack LIKE '%CHECKPERSONCOSTINGS%';
BEGIN
FOR c1_rec IN c1
LOOP
INSERT INTO xx_wf_temp
(SELECT 'N', c1_rec.item_key,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'APPLICATION_ID'
AND item_type = 'PATCARD') application_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'APPROVER_COUNT'
AND item_type = 'PATCARD') approver_count,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'COMMENT_DOC'
AND item_type = 'PATCARD') comment_doc,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_NAME'
AND item_type = 'PATCARD') employee_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EXPENDITURE_ID'
AND item_type = 'PATCARD') expenditure_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'LINE_TABLE'
AND item_type = 'PATCARD') line_table,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_NAME'
AND item_type = 'PATCARD') manager_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'ORG_ID'
AND item_type = 'PATCARD') org_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_NAME'
AND item_type = 'PATCARD') preparer_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'RESPONSIBILITY_ID'
AND item_type = 'PATCARD') responsibility_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TIMECARD_DETAILS'
AND item_type = 'PATCARD') timecard_details,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TIMECARD_NUMBER'
AND item_type = 'PATCARD') timecard_number,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TOTAL_HOURS'
AND item_type = 'PATCARD') total_hours,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'USER_ID'
AND item_type = 'PATCARD') user_id,
(SELECT date_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'WEEK_END_DATE'
AND item_type = 'PATCARD') week_end_date,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_ID'
AND item_type = 'PATCARD') employee_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_ID'
AND item_type = 'PATCARD') manager_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_ID'
AND item_type = 'PATCARD') preparer_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_DISP_NAME'
AND item_type = 'PATCARD') employee_disp_name,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_DISP_NAME'
AND item_type = 'PATCARD') manager_disp_name,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_DISP_NAME'
AND item_type = 'PATCARD') preparer_disp_name
FROM DUAL);

COMMIT;
END LOOP;
END;

/************************************************************
2. Basically to delete the workflows in open status, we need to abort workflow process first so they will be eligible for purge process. The below code is used to perform the abort process on the workflow. Once the code is executed the workflow status would be set to Completed.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'N'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.abortprocess ('PATCARD', --Item Type
cur_rec.item_key, -- Item Key
'PA_TCARD_APPROVAL_PROCESS', -- Parent Process
'CANCEL' -- Process
);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'A'
WHERE status_stg = 'N' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
3. The below code is used to perform the purge process for the error workflow timecards from the oracle workflow base tables.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'A'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_purge.items ('PATCARD', cur_rec.item_key, SYSDATE, TRUE, TRUE);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'P'
WHERE status_stg = 'A' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
4. The below code is used to create the timecard again in Oracle with the same item key and with the same version of the workflow existing in Oracle now..
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'P'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.createprocess ('PATCARD',
cur_rec.item_key,
'PA_TCARD_APPROVAL_PROCESS',
NULL,
cur_rec.preparer_name
);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'C'
WHERE status_stg = 'P' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
5. In the below code we are setting the attributes from the temp table in which we have stored all the values at Step 1 and would be starting the workflow process.
/************************************************************

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'C'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'COMMENT_DOC',
avalue => cur_rec.comment_doc
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_NAME',
avalue => cur_rec.employee_name
);
wf_engine.setitemattrdocument (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'LINE_TABLE',
documentid => cur_rec.line_table
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_NAME',
avalue => cur_rec.manager_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_NAME',
avalue => cur_rec.preparer_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TIMECARD_DETAILS',
avalue => cur_rec.timecard_details
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TIMECARD_NUMBER',
avalue => cur_rec.timecard_number
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_DISP_NAME',
avalue => cur_rec.employee_disp_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_DISP_NAME',
avalue => cur_rec.manager_disp_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_DISP_NAME',
avalue => cur_rec.preparer_disp_name
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'APPLICATION_ID',
avalue => cur_rec.application_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'APPROVER_COUNT',
avalue => cur_rec.approver_count
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EXPENDITURE_ID',
avalue => cur_rec.expenditure_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'ORG_ID',
avalue => cur_rec.org_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'RESPONSIBILITY_ID',
avalue => cur_rec.responsibility_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TOTAL_HOURS',
avalue => cur_rec.total_hours
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'USER_ID',
avalue => cur_rec.user_id
);
wf_engine.setitemattrdate (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'WEEK_END_DATE',
avalue => cur_rec.week_end_date
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_ID',
avalue => cur_rec.employee_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_ID',
avalue => cur_rec.manager_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_ID',
avalue => cur_rec.preparer_id
);
wf_engine.startprocess ('PATCARD', cur_rec.item_key);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'S'
WHERE status_stg = 'C' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

Check out Jaago re! - One Billion Votes

Hi,

I want you to take a look at: Jaago re! - One Billion Votes 

Understanding and Using API User Hooks

You can get all the information of API User Hooks in the metalink with the Doc ID: 73170.1

Retrieve the correct person type in HRMS

Use the below standard oracle function to retrieve the correct person type for an employee.

SELECT hr_person_type_usage_info.get_user_person_type (SYSDATE, &person_id)
FROM DUAL

If you want to retrieve the latest person type then perform the instr and substr on the output of the above query.

Simple Script to load the HR Locations using API

DECLARE
l_location_id NUMBER;
l_object_version_number NUMBER;
BEGIN
apps.hr_location_api.create_location
(p_effective_date => SYSDATE,
p_location_code => 'XXXXX',
p_description => 'XXX Location',
p_address_line_1 => 'XXX Rd',
p_country => 'US',
p_postal_code => '12345',
p_telephone_number_1 => '1234567890',
p_town_or_city => 'XXX',
p_business_group_id => '0',
p_style => 'US_GLB',
p_location_id => l_location_id,
p_object_version_number => l_object_version_number
);
COMMIT;
END;