Expense reports will fail is the manager does not have approval authority for a particular cost center, or an appropriate approval level for the cost center of the employee submitting the expense report. The query to show the employee approval limits are as follows:
SELECT b.full_name, a.cost_center, a.org_id, c.NAME org_name, a.signing_limit
FROM ap_web_signing_limits_all a,
per_all_people_f b,
hr_organization_units c
WHERE a.employee_id = b.person_id
AND a.org_id = c.organization_id
AND b.effective_start_date = (SELECT MAX (effective_start_date)
FROM apps.per_all_people_f
WHERE person_id = b.person_id)
AND a.document_type = 'APEXP'
The navigation to check/update the approval limits for an expense report of an employee is as follows:
Payables Manager -> Employees -> Signing Limits
Monday, April 20, 2009
GL Setups
Defining Types of Calendar
Nav: Setup -> Financials -> Calendar -> Type
Accounting the Calendar
Nav: Setup -> Financials -> Calendar -> Accounting
Defining Currency
Nav: Setup -> Financials -> Currency -> Define
Define rate
Nav: Setup -> Financials -> Currency -> Rate -> Daily
Create a value set
Nav: Setup -> Financials -> Flexfields -> Validation -> Sets
Create a structure:
Nav: Setup -> Financials -> Flexfields -> key -> Segment
Define values:
Nav: Setup -> Financials -> Flexfeild -> Key -> Values
Creating a set of Books:
Nav: Setup -> Financials -> Books -> Define
Nav: Setup -> Financials -> Calendar -> Type
Accounting the Calendar
Nav: Setup -> Financials -> Calendar -> Accounting
Defining Currency
Nav: Setup -> Financials -> Currency -> Define
Define rate
Nav: Setup -> Financials -> Currency -> Rate -> Daily
Create a value set
Nav: Setup -> Financials -> Flexfields -> Validation -> Sets
Create a structure:
Nav: Setup -> Financials -> Flexfields -> key -> Segment
Define values:
Nav: Setup -> Financials -> Flexfeild -> Key -> Values
Creating a set of Books:
Nav: Setup -> Financials -> Books -> Define
Query to extract Employee Contact Information
SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type
Script to delete the concurrent program from Oracle
Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;
Person Types in Oracle HRMS
There are two tables (per_all_people_f,per_person_type_usages_f) where you can find the person_types for an employee. Developers usually gets misguided to which person_type_id we need to consider.
The answer is we should always need to use the table per_person_type_usages_f to retrieve the correct person_id. Though the person_type_id in the two tables appear to be same but they are different and that can perty much mislead us. Here are the reasons below:
1. The per_all_people_f.person_type_id holds the default user_person_type for a given system_person_type. It is not maintained to reflect the true user person type. For each system_person_type in each business group, there can only be one USER_PERSON_TYPE with a default_flag = 'Y'. However, an employee can have a user_person_type = Contractor, What will show in person_type_id of the two tables with a CONTRACTOR user_person_type will be as follows:
PER_ALL_PEOPLE_F : 2
PER_PERSON_TYPE_USAGES_F: 4
where 'Contractor' is the subgroup of the person type 'Employee' and 2 is the person_type_id for the employee and 4 is the person_type_id of contractor..
So, only the person_type_usages_f table will give us a true picture of user_person_type and should be used in ALL HR transactions. The per_all_people_f.person_type_id is for use by non HR applications, such as Purchasing which doesn't care about the various user flavors but the system_person_type of 'EMP'. When HR Development made the change to use per_person_type_usages_f table, they retain the person_type_id column in per_all_people_f table for compatibility with other applications. This scheme will enable other non-HR applications still work.
NOTE: When accessing per_person_type_usages_f table for a given person at a given time, there can be multiple rows returned because per_person_type_usages_f table will have a row for each person_type. When an employee is an 'EMP_APL', there'll be two rows returned: one for a system_person_type = 'EMP'with user_person_type = whatever, and another row for system_person_type = 'APL' and user_person_type = whatever. Any SQL statement or cursor needs to handle the multiple rows returned condition. Otherwise, one will get an error something like "Exact fetch returns more than 1 row".
The answer is we should always need to use the table per_person_type_usages_f to retrieve the correct person_id. Though the person_type_id in the two tables appear to be same but they are different and that can perty much mislead us. Here are the reasons below:
1. The per_all_people_f.person_type_id holds the default user_person_type for a given system_person_type. It is not maintained to reflect the true user person type. For each system_person_type in each business group, there can only be one USER_PERSON_TYPE with a default_flag = 'Y'. However, an employee can have a user_person_type = Contractor, What will show in person_type_id of the two tables with a CONTRACTOR user_person_type will be as follows:
PER_ALL_PEOPLE_F : 2
PER_PERSON_TYPE_USAGES_F: 4
where 'Contractor' is the subgroup of the person type 'Employee' and 2 is the person_type_id for the employee and 4 is the person_type_id of contractor..
So, only the person_type_usages_f table will give us a true picture of user_person_type and should be used in ALL HR transactions. The per_all_people_f.person_type_id is for use by non HR applications, such as Purchasing which doesn't care about the various user flavors but the system_person_type of 'EMP'. When HR Development made the change to use per_person_type_usages_f table, they retain the person_type_id column in per_all_people_f table for compatibility with other applications. This scheme will enable other non-HR applications still work.
NOTE: When accessing per_person_type_usages_f table for a given person at a given time, there can be multiple rows returned because per_person_type_usages_f table will have a row for each person_type. When an employee is an 'EMP_APL', there'll be two rows returned: one for a system_person_type = 'EMP'with user_person_type = whatever, and another row for system_person_type = 'APL' and user_person_type = whatever. Any SQL statement or cursor needs to handle the multiple rows returned condition. Otherwise, one will get an error something like "Exact fetch returns more than 1 row".
Sunday, April 19, 2009
Workflow Error
Error Name: WFNTF_ROLE
Error Message 3205: 'FND_RESP...' is not a valid role or user name.
Resolution: The reason for the error is due to the users not been assigned to the responsibility. The workflow notification was looking for all the users that are been assigned to the responsibility, but as there were no users been assigned the workflow was going in error status.
The query to find the roles that are been assigned to the users or not is as follows:
SELECT *
FROM wf_user_roles
WHERE UPPER (role_name) LIKE 'FND_RESP...'
SELECT c.user_name, c.start_date, c.end_date, b.responsibility_name
FROM fnd_user_resp_groups_all a,
fnd_responsibility_tl b,
fnd_user c,
fnd_application d
WHERE a.user_id = c.user_id
AND a.responsibility_id = b.responsibility_id
AND b.application_id = d.application_id
AND b.responsibility_id = :responsibility_id
For more information on 'Workflow Directory Service Views' please visit:
Oracle Workflow Administrator's -> Workflow Directory Service Views
Error Message 3205: 'FND_RESP...' is not a valid role or user name.
Resolution: The reason for the error is due to the users not been assigned to the responsibility. The workflow notification was looking for all the users that are been assigned to the responsibility, but as there were no users been assigned the workflow was going in error status.
The query to find the roles that are been assigned to the users or not is as follows:
SELECT *
FROM wf_user_roles
WHERE UPPER (role_name) LIKE 'FND_RESP...'
SELECT c.user_name, c.start_date, c.end_date, b.responsibility_name
FROM fnd_user_resp_groups_all a,
fnd_responsibility_tl b,
fnd_user c,
fnd_application d
WHERE a.user_id = c.user_id
AND a.responsibility_id = b.responsibility_id
AND b.application_id = d.application_id
AND b.responsibility_id = :responsibility_id
For more information on 'Workflow Directory Service Views' please visit:
Oracle Workflow Administrator's -> Workflow Directory Service Views
Tuesday, April 14, 2009
Query to retrieve active Alert information
SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code
AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code
AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'
Wednesday, March 25, 2009
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.
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.
Sunday, March 22, 2009
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;
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;
Thursday, March 5, 2009
Check out Jaago re! - One Billion Votes
Hi,
I want you to take a look at: Jaago re! - One Billion Votes
Wednesday, March 4, 2009
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.
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.
Tuesday, March 3, 2009
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;
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;
Wednesday, February 18, 2009
11i Oracle General Ledger Technical Document
Sets of Books and Charts of Accounts govern Oracle’s General Ledger. The set of books keeps what currency, chart of accounts structure and calendar that the book will use. The main tables for this are
GL_SETS_OF_BOOKS
FND_ID_FLEX_STRUCTURES
JOURNAL ENTRIES
Journal Entries can be added several ways. By upload from and external source, upload through ADI, transfer from the various modules, or manually keyed into the application from the form.
Manual Entry - The responsibility that the user is in for the entry determines the chart of accounts structure and set of books. The user will enter a Batch name and period (which will default to the current open period). The user will then enter a header record with a description, and a journal category chosen from the list of valid journal categories. The user may also override the currency defaulted from the set of books. This will cause the entry to be booked to that currency but it will translate to the functional currency of the set of books. To translate, it will use the data from the GL_DAILY_RATES table. The user will last enter the lines for the journal. The main tables needed for this are:
GL_CODE_COMBINATIONS
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_CATEGORIES
GL_DAILY_RATES (If needed)
Imported Journals - Journals can also come into the general ledger from the other financial modules or by an integration tool (either custom built of ADI). The path for all of these types of entries is the same. Data flows through the GL_INTERFACE where is groups the source into batches, headers and lines based on where the data originates. When the Journal Import process is submitted either manually as a concurrent process or by a transfer process from the other modules. A journal batch, header and lines are created in the base tables exactly like a fully manual journal entry. The processes that are submitted from the other modules to transfer data into the GL are
Payables Import to General Ledger
Create Journal Entries (Fixed Assets)
PRC: Interface Revenue to General Ledger (Projects)
Interface Receivables to General Ledger
Main GL tables
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_SOURCES
GL_JE_CATEGORIES
GL_SETS_OF_BOOKS
GL_CODE_COMBINATIONS
GL_PERIODS
GL_CURRENCIES
JOURNAL POSTING
When the user has completed a balance journal entry or the import process completes with no errors, the entries can be posted. The posting is a concurrent process submitted by the post form. When the entry is posted, the data is copied from the base tables into a temporary table called GL_POSTING_INTERIM. Here the program will use the information to group all lines with the same Currency, Code Combination, Set of Books, and Period to be used to update GL_BALANCES. The balances table will be updated with the debits and credits. The batches, headers and lines tables will also be updated for posting status. When the post is complete, the posting interim table is cleared.
Main tables
GL_POSTING_INTERIM
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_BALANCES
BALANCE TRANSLATIONS
Balances that require translation into one currency for reporting and/or consolidation purposes must run the translation process. This process requires that there be a period rate entered for set of books functional currency to the translation currency, i.e. EUR to USD. This rate is entered manually using the period rates form for each set of books. The user will then submit a process using the translation form to translate the balances to the new currency. The process will perform the following query to determine the balances available for translation.
SELECT DISTINCT CC.SEGMENT1
FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL
WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND GBAL.ACTUAL_FLAG = (user entered)
AND GBAL.SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND GBAL.PERIOD_NAME = (user entered)
AND GBAL.CURRENCY_CODE = (functional currency for the set of books)
AND GBAL.TEMPLATE_ID IS NULL
AND CC.DETAIL_POSTING_ALLOWED_FLAG = 'Y'
SELECT EARLIEST_EVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR
FROM GL_TRANSLATION_TRACKING
WHERE BAL_SEG_VALUE = :bal_seg_value
AND TARGET_CURRENCY = (user entered)
AND SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND AVERAGE_TRANSLATION_FLAG = 'N'
AND ACTUAL_FLAG = 'A'
The process will also retrieve the historical rates for equity accounts from the GL_HISTORICAL_RATES table for the prior period and insert new records based on a profile option set for the module (Year to Date or Period To Date).
The process will then insert into a temporary table all of the balances to be translated called the GL_TRANSLATION_INTERIM. From here the process will translate the balances and insert those records into the GL_XLATE_POSTING_INTERIM table. Last the data will be inserted into or updated in the GL_BALANCES table.
Main tables
GL_PERIOD_RATES
GL_HISTORICAL_RATES
GL_TRANSLATION_TRACKING
GL_TRANSALATION_INTERIM
GL_XLATE_POSTING_INTERIM
GL_BALANCES
GL_SETS_OF_BOOKS
FND_ID_FLEX_STRUCTURES
JOURNAL ENTRIES
Journal Entries can be added several ways. By upload from and external source, upload through ADI, transfer from the various modules, or manually keyed into the application from the form.
Manual Entry - The responsibility that the user is in for the entry determines the chart of accounts structure and set of books. The user will enter a Batch name and period (which will default to the current open period). The user will then enter a header record with a description, and a journal category chosen from the list of valid journal categories. The user may also override the currency defaulted from the set of books. This will cause the entry to be booked to that currency but it will translate to the functional currency of the set of books. To translate, it will use the data from the GL_DAILY_RATES table. The user will last enter the lines for the journal. The main tables needed for this are:
GL_CODE_COMBINATIONS
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_CATEGORIES
GL_DAILY_RATES (If needed)
Imported Journals - Journals can also come into the general ledger from the other financial modules or by an integration tool (either custom built of ADI). The path for all of these types of entries is the same. Data flows through the GL_INTERFACE where is groups the source into batches, headers and lines based on where the data originates. When the Journal Import process is submitted either manually as a concurrent process or by a transfer process from the other modules. A journal batch, header and lines are created in the base tables exactly like a fully manual journal entry. The processes that are submitted from the other modules to transfer data into the GL are
Payables Import to General Ledger
Create Journal Entries (Fixed Assets)
PRC: Interface Revenue to General Ledger (Projects)
Interface Receivables to General Ledger
Main GL tables
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_SOURCES
GL_JE_CATEGORIES
GL_SETS_OF_BOOKS
GL_CODE_COMBINATIONS
GL_PERIODS
GL_CURRENCIES
JOURNAL POSTING
When the user has completed a balance journal entry or the import process completes with no errors, the entries can be posted. The posting is a concurrent process submitted by the post form. When the entry is posted, the data is copied from the base tables into a temporary table called GL_POSTING_INTERIM. Here the program will use the information to group all lines with the same Currency, Code Combination, Set of Books, and Period to be used to update GL_BALANCES. The balances table will be updated with the debits and credits. The batches, headers and lines tables will also be updated for posting status. When the post is complete, the posting interim table is cleared.
Main tables
GL_POSTING_INTERIM
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_BALANCES
BALANCE TRANSLATIONS
Balances that require translation into one currency for reporting and/or consolidation purposes must run the translation process. This process requires that there be a period rate entered for set of books functional currency to the translation currency, i.e. EUR to USD. This rate is entered manually using the period rates form for each set of books. The user will then submit a process using the translation form to translate the balances to the new currency. The process will perform the following query to determine the balances available for translation.
SELECT DISTINCT CC.SEGMENT1
FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL
WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND GBAL.ACTUAL_FLAG = (user entered)
AND GBAL.SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND GBAL.PERIOD_NAME = (user entered)
AND GBAL.CURRENCY_CODE = (functional currency for the set of books)
AND GBAL.TEMPLATE_ID IS NULL
AND CC.DETAIL_POSTING_ALLOWED_FLAG = 'Y'
SELECT EARLIEST_EVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR
FROM GL_TRANSLATION_TRACKING
WHERE BAL_SEG_VALUE = :bal_seg_value
AND TARGET_CURRENCY = (user entered)
AND SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND AVERAGE_TRANSLATION_FLAG = 'N'
AND ACTUAL_FLAG = 'A'
The process will also retrieve the historical rates for equity accounts from the GL_HISTORICAL_RATES table for the prior period and insert new records based on a profile option set for the module (Year to Date or Period To Date).
The process will then insert into a temporary table all of the balances to be translated called the GL_TRANSLATION_INTERIM. From here the process will translate the balances and insert those records into the GL_XLATE_POSTING_INTERIM table. Last the data will be inserted into or updated in the GL_BALANCES table.
Main tables
GL_PERIOD_RATES
GL_HISTORICAL_RATES
GL_TRANSLATION_TRACKING
GL_TRANSALATION_INTERIM
GL_XLATE_POSTING_INTERIM
GL_BALANCES
11i Oracle Fixed Asset Technical Document
In Oracle Fixed Assets it is important to remember that most of the processes are c programs. All current asset records from the tables have an NULL DATE_INEFFECTIVE and/or NULL TRANSACTION_HEADER_ID_OUT. And the application is governed by books which point to a GL Set Of Books.
FA ADDITIONS
Additions can come into 2 ways. Directly adding into the system manually or by entry through the Mass Additions tables.
Manual Entry - User enters data through the form and the information is inserted into the following tables
FA_ADDITIONS
FA_BOOKS
FA_ASSET_HISTORY
FA_DISTRIBUTION_HISTORY
FA_TRANSACTION_HEADERS
FA_DEPRN_SUMMARY
FA_DEPRN_DETAIL
FA_TRANSFER_DETAILS
Mass Additions come into the Oracle Assets by a spreadsheet entry through ADI (Applications Desktop Integrator) or through a concurrent process called Create Mass Additions (This can be from Payables or from Projects depending on the nature of the business)
Data is inserted into the following tables
FA_MASS_ADDITIONS (asset details)
FA_MASSADD_DISTRIBUTIONS (invoice and invoice distribution details)
The data is reviewed and updated with Category, Location and Depreciation Expense account by the user. Upon completion, a concurrent process is submitted called Post Mass Additions. Any record marked as POST will be picked up and added to the tables listed above in the Manual Entry section.
FA GENERATE ACCOUNTS
This is a concurrent program that can be submitted both stand-alone or it will submit as part of the run depreciation process.
This process when submitted looks at the FA_DISTRIBUTION_ACCOUNTS table for every active asset distribution in the book that the process is running. If there is no account listed, this process creates a new account in this table.
DEPRECIATION - BEHIND THE SCENES
When you run depreciation, Oracle Assets processes each asset according to the transactions that you have performed on the asset since the last depreciation.
FADEPR uses the following tables:
FA_DEPRN_DETAIL
For each depreciable asset, Oracle Assets inserts one row per distribution line that was active at any time during the current period.
BOOK_TYPE_CODE
ASSET_ID
PERIOD_COUNTER
DISTRIBUTION_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
ADDITION_COST_TO_CLEAR
COST
DEPRN_ADJUSTMENT_AMOUNT
DEPRN_EXPENSE_JE_LINE_NUM
DEPRN_RESERVE_JE_LINE_NUM
REVAL_AMORT_JE_LINE_NUM
REVAL_RESERVE_JE_LINE_NUM
JE_HEADER_ID
REVAL_AMORTIZATION
REVAL_DEPRN_EXPENSE
REVAL_RESERVE YTD
FA_DEPRN_SUMMARY
Oracle Assets inserts one row per depreciable asset.
BOOK_TYPE_CODE
ASSET_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
DEPRN_SOURCE_CODE
ADJUSTED_COST
BONUS_RATE
LTD_PRODUCTION
PERIOD_COUNTER
FA_BOOK_CONTROLS
If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'C' (Completed) or 'E' (Error), the form submits the concurrent request and sets the DEPRN_STATUS = 'S' (Submitted). Oracle Assets now locks this row to prevent you from entering any transaction when depreciation is running. If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'R' (Running) or 'S' (Submitted), then Oracle Assets displays the errors message "CHECK_BOOK_STATUS" or "Failed to obtain lock on FA_BOOK_CONTROLS row for book."
Oracle Assets also checks if the depreciation request is for the current open period. If the LAST_PERIOD_COUNTER in the FA_BOOK_CONTROLS is that of the last period, the program proceeds. Oracle Assets updates the LAST_PERIOD_COUNTER, LAST_DEPRN_RUN_DATE, DEPRN_REQUEST_ID, DEPRN_STATUS, and CURRENT_FISCAL_YEAR for the book.
FA_DEPRN_PERIODS
Oracle Assets closes the row corresponding to the current period (by entering a PERIOD_CLOSE_DATE) and inserts a new row for the book and the new period.
FA_FISCAL_YEARS
If the new fiscal year has not been created, Oracle Assets automatically extends the fiscal year definition.
FA_ADJUSTMENTS
Retroactive transactions and expensed depreciation adjustments.
TRANSACTION_HEADER_ID
SOURCE_TYPE_CODE
ADJUSTMENT_TYPE
CODE_COMBINATION_ID
BOOK_TYPE_CODE
ASSET_ID
ADJUSTMENT_AMOUNT
DISTRIBUTION_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
ANNUALIZED_ADJUSTMENT
JE_HEADER_ID
JE_LINE_NUM
PERIOD_COUNTER
ADJUSTED PERIOD_COUNTER
ASSET_INVOICE_ID
FA_BOOKS
Look up information needed for depreciation calculation and check the following:
PERIOD_FULLY_RESERVED = NULL
PERIOD_FULLY_RETIRED = NULL
DEPRECIATE_FLAG = YES
ADJUSTMENT_REQUIRED_STATUS is not NONE or TFR (Prior Period Transfer)
DATE_INEFFECTIVE = NULL
BOOK_TYPE_CODE
ASSET_ID
DATE_PLACED_IN_SERVICE
DATE_EFFECTIVE
DEPRN_START_DATE
DEPRN_METHOD
LIFE_IN_MONTHS
RATE_ADJUSTMENT_FACTOR
ADJUSTED_COST
COST
ORIGINAL_COST
SALVAGE_VALUE
PRORATE_CONVENTION
PRORATE_DATE
DATE_INEFFECTIVE
TRANSACTION_HEADER_ID_IN
TRANSACTION_HEADER_ID_OUT
ITC_AMOUNT_ID
ITC_AMOUNT
RETIREMENT_ID
TAX_REQUEST_ID
ITC_BASIS BASIC_RATE
ADJUSTED_RATE BONUS_RULE
CEILING_NAME
RECOVERABLE_COST
LAST_UPDATE_LOGIN
ADJUSTED_CAPACITY
FULLY_RSVD_REVALS_COUNTER
PERIOD_COUNTER_CAPITALIZED
PERIOD_COUNTER_FULLY_RESERVED
PERIOD_COUNTER_FULLY_RETIRED PRODUCTION_CAPACITY REVAL_AMORTIZATION_BASIS
REVAL_CEILING UNIT_OF_MEASURE
PERIOD_COUNTER_LIFE_COMPLETE
FA_DISTRIBUTION_HISTORY
Look up active distributions.
FA_CEILINGS
Look up ceiling information.
FA_CALENDAR_PERIODS
Look up period information.
FA_CONVENTIONS
Look up prorate convention information.
THE BOTTOM LINE
~~~~~~~~~~~~~~~
Historical depreciation calculations (DEPRN_EXPENSE, ACCUMULATED_DEPRN) can be found in FA_DEPRN_DETAIL and FA_DEPRN_SUMMARY. The main difference between these tables is that FA_DEPRN_DETAIL shows depreciation information for each distribution line (i.e. each active row in FA_DISTRIBUTION_HISTORY), whereas FA_DEPRN_SUMMARY shows summary depreciation information for an asset.
NOTE: Because referential integrity is not maintained at the RDBMS level, the depreciation tables do not reference FA_DISTRIBUTION_HISTORY or FA_ADJUSTMENTS.
COMMON DEPRECIATION ERRORS
~~~~~~~~~~~~~~~~~~~~~~~~~~
APP-48260 Module CHECK_BOOK_STATUS ended with error.
Cause: The MASS_REQUEST_ID for this book is NOT NULL.
Resolution: Check to verify that all mass requests for this book have
completed normal. NULL the MASS_REQUEST_ID in the FA_BOOK_CONTROLS table.
APP-00988 ORA-1403 in fadccs.
Cause: fadccs failed due to ORA-01403 no data found.
APP-47670 Unable to validate depreciation periods.
Cause: You ran depreciation for the first time in your book. The problem
is that there is a missing row in FA_DEPRN_PERIODS. FADEPR expects a row in
FA_DEPRN_PERIODS for one period less than the active period.
Resolution: Insert the missing row in FA_DEPRN_PERIODS table. Contact
Oracle Support Services for the datafix script.
APP-47984 in fazccp and ORA-1403 in fazgtcp.
Cause: The depreciation calendar needs to go as far back as the oldest DPIS,
or if you have changed the calendar, there may be gaps or period overlaps.
Resolution: Correct the calendar.
APP-00988 ORACLE error 1403 in fadubc
APP-47649 Error: Unable to set depreciation status in FA_BOOK_CONTROLS table
APP-47640 Error: Unable to update FA_BOOK_CONTROLS table
Cause: You ran depreciation for a book with no assets and you are in the
last period of the fiscal year.
Resolution: You need to apply patch for BUG 605315 or higher.
APP-00988 Oracle error 1555 in faddep
Cause: faddep failed due to ORA-01555: snapshot too old (rollback segment
too small).
Resolution: Increase the rollback segment size.
APP-47191 and ORA-1403 in fazgtbc
Cause: Depreciation is submitted with number of parallel requests set to
more than 1 (FA:Number of Parallel Requests) and the BOOK_TYPE_CODE has a
space in between 2 words. Example: US CORPORATE
Resolution: This is BUG 456936. Apply Patch 605315 or higher. As a
workaround, you need to submit depreciation in single mode.
APP-00988 ORACLE error 1 in faenicp
Cause: faenicp failed due to ORA-00001:unique constraint
(FA.FA_CALENDAR_PERIODS_U3) violated.
Resolution:
1) Make sure that the version of faeofy.lpc in FADEPR is version 70.15
or Higher.
2) Check the calendar periods for gaps or overlap.
3) Check that the Fiscal Start and End Dates match the Start and End Dates
of the first and last periods of the fiscal year respectively.
4) Make sure that there are no gaps or overlap between Fiscal Year Start
and End Dates.
5) Be sure that all of the periods for a Fiscal Year have been created.
ORA-1 in fadaid/fadais
Common causes:
- Updating DEPRN_STATUS in FA_BOOK_CONTROLS to C (Completed).
- Partially committed or incomplete transfers, adjustments, or partial
retirements.
- Transfers that happened after depreciation has errored in a book.
- Orphaned adjustment records.
- Orphaned distributions.
Resolution: Run the provided ORA-1 diagnostic scripts. Contact Oracle
Support Services for any required datafix scripts.
ORA-1 DIAGNOSTIC SCRIPTS
========================
Script to check for adjustment rows with invalid distribution_id:
select aj.asset_id, aj.distribution_id
from fa.fa_distribution_history dh, fa.fa_adjustments aj
where aj.period_counter_created = &PCounter
and aj.book_type_code = '&BOOK'
and NOT exists (select dh.asset_id
from FA.fa_distribution_history dh
where dh.distribution_id = aj.distribution_id
and dh.asset_id = aj.asset_id);
Another script to check for adjustment rows with invalid distribution_id:
select aj.asset_id
from fa.fa_transaction_headers th,
fa.fa_distribution_history dh,
fa.fa_adjustments aj,
fa.fa_deprn_periods dp
where dp.book_type_code = '&BOOK'
and dp.period_close_date is null
and dp.period_counter = aj.period_counter_created
and dp.book_type_code = aj.book_type_code
and aj.transaction_header_id = th.transaction_header_id
and th.transaction_type_code = 'TRANSFER'
and aj.distribution_id = dh.distribution_id(+)
and dh.code_combination_id is null;
The best script to check for adjustment rows with invalid distribution_id:
select distinct aj.asset_id
from fa.fa_adjustments aj, fa.fa_deprn_detail dd
where aj.book_type_code = &book
and aj.distribution_id = dd.distribution_id(+)
and aj.period_counter_created = dd.period_counter(+)
and aj.book_type_code = dd.book_type_code(+)
and dd.deprn_amount is null
and exists (select 'fine' from fa.fa_deprn_detail dd2
where dd2.asset_id = aj.asset_id
and dd2.book_type_code = aj.book_type_code
and dd2.period_counter = aj.period_counter_created);
Script to check for invalid rows in FA_DISTRIBUTION_HISTORY:
select dh.asset_id
from fa.fa_distribution_history dh1,
fa.fa_distribution_history dh
where dh.transaction_header_id_out is not null
and dh.transaction_header_id_out = dh1.transaction_header_id_in(+)
and dh1.code_combination_id is null;
GENERAL INFORMATION
===================
DEPRECIATION CALENDAR
The depreciation calendar determines the number of accounting periods in
your fiscal year.
PRORATE CALENDAR
The prorate calendar determines what rate Oracle Assets uses to calculate annual depreciation by mapping each date to a prorate period, which corresponds to a set of rates in the rate table.
PERIOD CLOSE
Oracle Assets automatically closes the book's current period and opens the next when you run the depreciation program. You cannot have more than one open period for a given depreciation book.
YEAR-END PROCESSING
You can close the year independently in each depreciation book. The depreciation program automatically resets year-to-date amounts on a book the first time the depreciation program is run on that book in a fiscal year. Oracle Assets automatically creates the depreciation and prorate periods for your new year when you run depreciation for the last period of the previous fiscal year.
SUSPEND DEPRECIATION
You can suspend depreciation by un-checking Depreciate flag in the Books form. If you suspend depreciation of an asset when you add the asset, Oracle Assets expenses the missed depreciation in the period you start depreciating the asset.
For table and calculated methods, Oracle Assets calculates depreciation expense for the asset based on an asset life that includes the periods you did not depreciate it. If you suspend depreciation after an asset has started depreciating, Oracle Assets catches up the missed depreciation expense in the last period of life.
For flat-rate methods, Oracle Assets continues calculating depreciation expense for the asset based on the flat-rate. For flat-rate methods that use net book value, Oracle Assets uses the asset net book value at the beginning of the fiscal year in which you resume depreciation. The asset continues depreciating until it becomes fully reserved.
RECOVERABLE COST
For depreciation methods with a calculation basis of cost, Oracle Assets calculates depreciation using the recoverable cost. The recoverable cost is calculated as the lesser of either the cost less the salvage value less the investment tax credit basis reduction amount, or the cost ceiling.
Oracle Assets depreciates the asset until the accumulated depreciation equals the recoverable cost.
ADJUSTMENTS
The following are some examples of financial adjustments you can expense or amortize:
- Recoverable Cost Adjustments
- Depreciation Method Adjustments
- Life Adjustments
- Rate Adjustments
- Capacity Adjustments
PRIOR PERIOD TRANSACTIONS
- Prior Period Additions
If you enter an asset with a date placed in service before the current accounting period, Oracle Assets automatically calculates the missed depreciation and adjusts the accumulated depreciation on the next depreciation run. If you provide accumulated depreciation when you add the asset, Oracle Assets does not recalculate the accumulated depreciation. It accepts the amount you entered. For table and calculated methods, even if the entered accumulated depreciation differs from what Oracle Assets would have calculated, Oracle Assets does not depreciate the asset beyond the recoverable cost. If the accumulated depreciation is too low, Oracle Assets takes additional depreciation in the last period of the asset's life so that the asset becomes fully reserved. If the asset's accumulated depreciation is too high, Oracle Assets stops depreciating the asset when it becomes fully reserved, effectively shortening the asset life.
- Prior Period Transfers
If you backdate an asset transfer, Oracle Assets automatically reallocates depreciation expense by reversing some of the depreciation charged to the from account, and redistributing it proportionally to the to accounts. Retroactive transfers do not impact the total depreciation. You cannot backdate a transfer to a prior fiscal year.
- Prior Period Retirements / Reinstatements
If you backdate a retirement, Oracle Assets automatically adjusts the depreciation for the year by the appropriate amount, resulting in a one-time adjustment in depreciation expense for the period. Oracle Assets then computes the gain or loss using the resulting net book value. You cannot backdate a retirement to a previous fiscal year, nor can you reinstate a retirement performed in a previous fiscal year.
- Prior Period Amortized Adjustments
If you backdate an amortized adjustment, Oracle Assets automatically calculates depreciation from the retroactive amortization start date, and adds the retroactive depreciation to the current period.
- Negative Cost (Credit) Assets
You can enter a credit asset as an asset with a negative cost and Oracle Assets credits depreciation expense and debits accumulated depreciation each period for the life of the asset.
FA ADDITIONS
Additions can come into 2 ways. Directly adding into the system manually or by entry through the Mass Additions tables.
Manual Entry - User enters data through the form and the information is inserted into the following tables
FA_ADDITIONS
FA_BOOKS
FA_ASSET_HISTORY
FA_DISTRIBUTION_HISTORY
FA_TRANSACTION_HEADERS
FA_DEPRN_SUMMARY
FA_DEPRN_DETAIL
FA_TRANSFER_DETAILS
Mass Additions come into the Oracle Assets by a spreadsheet entry through ADI (Applications Desktop Integrator) or through a concurrent process called Create Mass Additions (This can be from Payables or from Projects depending on the nature of the business)
Data is inserted into the following tables
FA_MASS_ADDITIONS (asset details)
FA_MASSADD_DISTRIBUTIONS (invoice and invoice distribution details)
The data is reviewed and updated with Category, Location and Depreciation Expense account by the user. Upon completion, a concurrent process is submitted called Post Mass Additions. Any record marked as POST will be picked up and added to the tables listed above in the Manual Entry section.
FA GENERATE ACCOUNTS
This is a concurrent program that can be submitted both stand-alone or it will submit as part of the run depreciation process.
This process when submitted looks at the FA_DISTRIBUTION_ACCOUNTS table for every active asset distribution in the book that the process is running. If there is no account listed, this process creates a new account in this table.
DEPRECIATION - BEHIND THE SCENES
When you run depreciation, Oracle Assets processes each asset according to the transactions that you have performed on the asset since the last depreciation.
FADEPR uses the following tables:
FA_DEPRN_DETAIL
For each depreciable asset, Oracle Assets inserts one row per distribution line that was active at any time during the current period.
BOOK_TYPE_CODE
ASSET_ID
PERIOD_COUNTER
DISTRIBUTION_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
ADDITION_COST_TO_CLEAR
COST
DEPRN_ADJUSTMENT_AMOUNT
DEPRN_EXPENSE_JE_LINE_NUM
DEPRN_RESERVE_JE_LINE_NUM
REVAL_AMORT_JE_LINE_NUM
REVAL_RESERVE_JE_LINE_NUM
JE_HEADER_ID
REVAL_AMORTIZATION
REVAL_DEPRN_EXPENSE
REVAL_RESERVE YTD
FA_DEPRN_SUMMARY
Oracle Assets inserts one row per depreciable asset.
BOOK_TYPE_CODE
ASSET_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
DEPRN_SOURCE_CODE
ADJUSTED_COST
BONUS_RATE
LTD_PRODUCTION
PERIOD_COUNTER
FA_BOOK_CONTROLS
If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'C' (Completed) or 'E' (Error), the form submits the concurrent request and sets the DEPRN_STATUS = 'S' (Submitted). Oracle Assets now locks this row to prevent you from entering any transaction when depreciation is running. If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'R' (Running) or 'S' (Submitted), then Oracle Assets displays the errors message "CHECK_BOOK_STATUS" or "Failed to obtain lock on FA_BOOK_CONTROLS row for book."
Oracle Assets also checks if the depreciation request is for the current open period. If the LAST_PERIOD_COUNTER in the FA_BOOK_CONTROLS is that of the last period, the program proceeds. Oracle Assets updates the LAST_PERIOD_COUNTER, LAST_DEPRN_RUN_DATE, DEPRN_REQUEST_ID, DEPRN_STATUS, and CURRENT_FISCAL_YEAR for the book.
FA_DEPRN_PERIODS
Oracle Assets closes the row corresponding to the current period (by entering a PERIOD_CLOSE_DATE) and inserts a new row for the book and the new period.
FA_FISCAL_YEARS
If the new fiscal year has not been created, Oracle Assets automatically extends the fiscal year definition.
FA_ADJUSTMENTS
Retroactive transactions and expensed depreciation adjustments.
TRANSACTION_HEADER_ID
SOURCE_TYPE_CODE
ADJUSTMENT_TYPE
CODE_COMBINATION_ID
BOOK_TYPE_CODE
ASSET_ID
ADJUSTMENT_AMOUNT
DISTRIBUTION_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
ANNUALIZED_ADJUSTMENT
JE_HEADER_ID
JE_LINE_NUM
PERIOD_COUNTER
ADJUSTED PERIOD_COUNTER
ASSET_INVOICE_ID
FA_BOOKS
Look up information needed for depreciation calculation and check the following:
PERIOD_FULLY_RESERVED = NULL
PERIOD_FULLY_RETIRED = NULL
DEPRECIATE_FLAG = YES
ADJUSTMENT_REQUIRED_STATUS is not NONE or TFR (Prior Period Transfer)
DATE_INEFFECTIVE = NULL
BOOK_TYPE_CODE
ASSET_ID
DATE_PLACED_IN_SERVICE
DATE_EFFECTIVE
DEPRN_START_DATE
DEPRN_METHOD
LIFE_IN_MONTHS
RATE_ADJUSTMENT_FACTOR
ADJUSTED_COST
COST
ORIGINAL_COST
SALVAGE_VALUE
PRORATE_CONVENTION
PRORATE_DATE
DATE_INEFFECTIVE
TRANSACTION_HEADER_ID_IN
TRANSACTION_HEADER_ID_OUT
ITC_AMOUNT_ID
ITC_AMOUNT
RETIREMENT_ID
TAX_REQUEST_ID
ITC_BASIS BASIC_RATE
ADJUSTED_RATE BONUS_RULE
CEILING_NAME
RECOVERABLE_COST
LAST_UPDATE_LOGIN
ADJUSTED_CAPACITY
FULLY_RSVD_REVALS_COUNTER
PERIOD_COUNTER_CAPITALIZED
PERIOD_COUNTER_FULLY_RESERVED
PERIOD_COUNTER_FULLY_RETIRED PRODUCTION_CAPACITY REVAL_AMORTIZATION_BASIS
REVAL_CEILING UNIT_OF_MEASURE
PERIOD_COUNTER_LIFE_COMPLETE
FA_DISTRIBUTION_HISTORY
Look up active distributions.
FA_CEILINGS
Look up ceiling information.
FA_CALENDAR_PERIODS
Look up period information.
FA_CONVENTIONS
Look up prorate convention information.
THE BOTTOM LINE
~~~~~~~~~~~~~~~
Historical depreciation calculations (DEPRN_EXPENSE, ACCUMULATED_DEPRN) can be found in FA_DEPRN_DETAIL and FA_DEPRN_SUMMARY. The main difference between these tables is that FA_DEPRN_DETAIL shows depreciation information for each distribution line (i.e. each active row in FA_DISTRIBUTION_HISTORY), whereas FA_DEPRN_SUMMARY shows summary depreciation information for an asset.
NOTE: Because referential integrity is not maintained at the RDBMS level, the depreciation tables do not reference FA_DISTRIBUTION_HISTORY or FA_ADJUSTMENTS.
COMMON DEPRECIATION ERRORS
~~~~~~~~~~~~~~~~~~~~~~~~~~
APP-48260 Module CHECK_BOOK_STATUS ended with error.
Cause: The MASS_REQUEST_ID for this book is NOT NULL.
Resolution: Check to verify that all mass requests for this book have
completed normal. NULL the MASS_REQUEST_ID in the FA_BOOK_CONTROLS table.
APP-00988 ORA-1403 in fadccs.
Cause: fadccs failed due to ORA-01403 no data found.
APP-47670 Unable to validate depreciation periods.
Cause: You ran depreciation for the first time in your book. The problem
is that there is a missing row in FA_DEPRN_PERIODS. FADEPR expects a row in
FA_DEPRN_PERIODS for one period less than the active period.
Resolution: Insert the missing row in FA_DEPRN_PERIODS table. Contact
Oracle Support Services for the datafix script.
APP-47984 in fazccp and ORA-1403 in fazgtcp.
Cause: The depreciation calendar needs to go as far back as the oldest DPIS,
or if you have changed the calendar, there may be gaps or period overlaps.
Resolution: Correct the calendar.
APP-00988 ORACLE error 1403 in fadubc
APP-47649 Error: Unable to set depreciation status in FA_BOOK_CONTROLS table
APP-47640 Error: Unable to update FA_BOOK_CONTROLS table
Cause: You ran depreciation for a book with no assets and you are in the
last period of the fiscal year.
Resolution: You need to apply patch for BUG 605315 or higher.
APP-00988 Oracle error 1555 in faddep
Cause: faddep failed due to ORA-01555: snapshot too old (rollback segment
too small).
Resolution: Increase the rollback segment size.
APP-47191 and ORA-1403 in fazgtbc
Cause: Depreciation is submitted with number of parallel requests set to
more than 1 (FA:Number of Parallel Requests) and the BOOK_TYPE_CODE has a
space in between 2 words. Example: US CORPORATE
Resolution: This is BUG 456936. Apply Patch 605315 or higher. As a
workaround, you need to submit depreciation in single mode.
APP-00988 ORACLE error 1 in faenicp
Cause: faenicp failed due to ORA-00001:unique constraint
(FA.FA_CALENDAR_PERIODS_U3) violated.
Resolution:
1) Make sure that the version of faeofy.lpc in FADEPR is version 70.15
or Higher.
2) Check the calendar periods for gaps or overlap.
3) Check that the Fiscal Start and End Dates match the Start and End Dates
of the first and last periods of the fiscal year respectively.
4) Make sure that there are no gaps or overlap between Fiscal Year Start
and End Dates.
5) Be sure that all of the periods for a Fiscal Year have been created.
ORA-1 in fadaid/fadais
Common causes:
- Updating DEPRN_STATUS in FA_BOOK_CONTROLS to C (Completed).
- Partially committed or incomplete transfers, adjustments, or partial
retirements.
- Transfers that happened after depreciation has errored in a book.
- Orphaned adjustment records.
- Orphaned distributions.
Resolution: Run the provided ORA-1 diagnostic scripts. Contact Oracle
Support Services for any required datafix scripts.
ORA-1 DIAGNOSTIC SCRIPTS
========================
Script to check for adjustment rows with invalid distribution_id:
select aj.asset_id, aj.distribution_id
from fa.fa_distribution_history dh, fa.fa_adjustments aj
where aj.period_counter_created = &PCounter
and aj.book_type_code = '&BOOK'
and NOT exists (select dh.asset_id
from FA.fa_distribution_history dh
where dh.distribution_id = aj.distribution_id
and dh.asset_id = aj.asset_id);
Another script to check for adjustment rows with invalid distribution_id:
select aj.asset_id
from fa.fa_transaction_headers th,
fa.fa_distribution_history dh,
fa.fa_adjustments aj,
fa.fa_deprn_periods dp
where dp.book_type_code = '&BOOK'
and dp.period_close_date is null
and dp.period_counter = aj.period_counter_created
and dp.book_type_code = aj.book_type_code
and aj.transaction_header_id = th.transaction_header_id
and th.transaction_type_code = 'TRANSFER'
and aj.distribution_id = dh.distribution_id(+)
and dh.code_combination_id is null;
The best script to check for adjustment rows with invalid distribution_id:
select distinct aj.asset_id
from fa.fa_adjustments aj, fa.fa_deprn_detail dd
where aj.book_type_code = &book
and aj.distribution_id = dd.distribution_id(+)
and aj.period_counter_created = dd.period_counter(+)
and aj.book_type_code = dd.book_type_code(+)
and dd.deprn_amount is null
and exists (select 'fine' from fa.fa_deprn_detail dd2
where dd2.asset_id = aj.asset_id
and dd2.book_type_code = aj.book_type_code
and dd2.period_counter = aj.period_counter_created);
Script to check for invalid rows in FA_DISTRIBUTION_HISTORY:
select dh.asset_id
from fa.fa_distribution_history dh1,
fa.fa_distribution_history dh
where dh.transaction_header_id_out is not null
and dh.transaction_header_id_out = dh1.transaction_header_id_in(+)
and dh1.code_combination_id is null;
GENERAL INFORMATION
===================
DEPRECIATION CALENDAR
The depreciation calendar determines the number of accounting periods in
your fiscal year.
PRORATE CALENDAR
The prorate calendar determines what rate Oracle Assets uses to calculate annual depreciation by mapping each date to a prorate period, which corresponds to a set of rates in the rate table.
PERIOD CLOSE
Oracle Assets automatically closes the book's current period and opens the next when you run the depreciation program. You cannot have more than one open period for a given depreciation book.
YEAR-END PROCESSING
You can close the year independently in each depreciation book. The depreciation program automatically resets year-to-date amounts on a book the first time the depreciation program is run on that book in a fiscal year. Oracle Assets automatically creates the depreciation and prorate periods for your new year when you run depreciation for the last period of the previous fiscal year.
SUSPEND DEPRECIATION
You can suspend depreciation by un-checking Depreciate flag in the Books form. If you suspend depreciation of an asset when you add the asset, Oracle Assets expenses the missed depreciation in the period you start depreciating the asset.
For table and calculated methods, Oracle Assets calculates depreciation expense for the asset based on an asset life that includes the periods you did not depreciate it. If you suspend depreciation after an asset has started depreciating, Oracle Assets catches up the missed depreciation expense in the last period of life.
For flat-rate methods, Oracle Assets continues calculating depreciation expense for the asset based on the flat-rate. For flat-rate methods that use net book value, Oracle Assets uses the asset net book value at the beginning of the fiscal year in which you resume depreciation. The asset continues depreciating until it becomes fully reserved.
RECOVERABLE COST
For depreciation methods with a calculation basis of cost, Oracle Assets calculates depreciation using the recoverable cost. The recoverable cost is calculated as the lesser of either the cost less the salvage value less the investment tax credit basis reduction amount, or the cost ceiling.
Oracle Assets depreciates the asset until the accumulated depreciation equals the recoverable cost.
ADJUSTMENTS
The following are some examples of financial adjustments you can expense or amortize:
- Recoverable Cost Adjustments
- Depreciation Method Adjustments
- Life Adjustments
- Rate Adjustments
- Capacity Adjustments
PRIOR PERIOD TRANSACTIONS
- Prior Period Additions
If you enter an asset with a date placed in service before the current accounting period, Oracle Assets automatically calculates the missed depreciation and adjusts the accumulated depreciation on the next depreciation run. If you provide accumulated depreciation when you add the asset, Oracle Assets does not recalculate the accumulated depreciation. It accepts the amount you entered. For table and calculated methods, even if the entered accumulated depreciation differs from what Oracle Assets would have calculated, Oracle Assets does not depreciate the asset beyond the recoverable cost. If the accumulated depreciation is too low, Oracle Assets takes additional depreciation in the last period of the asset's life so that the asset becomes fully reserved. If the asset's accumulated depreciation is too high, Oracle Assets stops depreciating the asset when it becomes fully reserved, effectively shortening the asset life.
- Prior Period Transfers
If you backdate an asset transfer, Oracle Assets automatically reallocates depreciation expense by reversing some of the depreciation charged to the from account, and redistributing it proportionally to the to accounts. Retroactive transfers do not impact the total depreciation. You cannot backdate a transfer to a prior fiscal year.
- Prior Period Retirements / Reinstatements
If you backdate a retirement, Oracle Assets automatically adjusts the depreciation for the year by the appropriate amount, resulting in a one-time adjustment in depreciation expense for the period. Oracle Assets then computes the gain or loss using the resulting net book value. You cannot backdate a retirement to a previous fiscal year, nor can you reinstate a retirement performed in a previous fiscal year.
- Prior Period Amortized Adjustments
If you backdate an amortized adjustment, Oracle Assets automatically calculates depreciation from the retroactive amortization start date, and adds the retroactive depreciation to the current period.
- Negative Cost (Credit) Assets
You can enter a credit asset as an asset with a negative cost and Oracle Assets credits depreciation expense and debits accumulated depreciation each period for the life of the asset.
11i Oracle Purchasing Technical Document
Oracle Purchasing is multi-org sensitive. The complexity depends on the set up.
Requisition Entry
Requisitions can be entered manually by the user or from the Requisition Import process.
Manual entry – Users will enter the data using the Requisitions form. At the header level, the user will enter the requisition number (if the app is set up to accept manual numbering), a requisition type and a description. At the line level, the user will enter the line type (Goods, Service, Labor etc.), and item (not required), a category (required), a description of the item or category, the units and the amount, the requestor, inventory org and location. Vendor and Vendor Site are optional. The user can enter multiple lines. After the line is entered, the user will enter the distributions for each line. Each line must have at least one distribution, but can consist of multiple. The distribution will be the code combination and/or project information for the purchase. When the user saves, the PR Account Generator is called to validate the code combination or if customized build the code combination based on auto-accounting rules from Oracle Projects. When the entry is saved, records are created in the following tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Import Entry – In order to import requisitions, the source must be mapped to the fields needed in the requisition interface. Coding will need to be done to utilize this functionality. Data will import using a concurrent process called Requisition Import. The data is brought into Oracle Purchasing and creates the header, lines and distributions. The main tables are:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Requisition Approval
Requisitions are approved based on specific application set-ups. The main set ups consist of determining of Approval Groups that determine the dollar value by document and/or account combination. The approval group is then assigned to a job title set up in the Oracle HR system. The amount assigned to the title determines who can have final approval authority. The user will submit the requisition from approval. The workflow then calls a process to build the initial approval list based on employee/supervisor (or position hierarchy if the system is set up that way). The list will include all approvers necessary to approve the requisition total. If there is no person with a high enough authority to approve, the requisition goes back to an incomplete status and the users will determine where the gap is and correct the assignments. If the requisition is on the incorrect path, the approver may forward the requisition to the correct person. The workflow will then call a procedure to rebuild the approval list based upon the forward to person’s employee/supervisor relationship. The procedure ends the current approval list header and creates a new header and lines. The history and status of the requisition is kept in the action history, which is viewable from the requisition form or from the requisition notification. The main tables are:
PER_ALL_PEOPLE_F
PER_JOBS
PER_ALL_ASSIGNMENTS_F
GL_CODE_COMBINATIONS
PO_CONTROL_RULES
PO_CONTROL_GROUPS_ALL
PO_CONTROL_FUNCTIONS
PO_POSITION_CONTROLS_ALL
PO_ACTION_HISTORY
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES
Purchase Order Entry
Purchase orders can be created 2 ways, autocreating from an approved requisition or by manual entry into the from.
Autocreate – In the autocreate form, the user will enter a requisition or search criteria to pull multiple requisitions available to be turned into purchase orders. The query will produce the lines available and the user can select all or any combination to create the purchase order. Example, if there are 3 requisitions approved for one vendor, the user can create one purchase order with all of the requisition information. The main tables are
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Manual entry – The user enters into the Purchase Order form the same information as entry for the requisitions. The tables listed above are the main tables.
Requisition Entry
Requisitions can be entered manually by the user or from the Requisition Import process.
Manual entry – Users will enter the data using the Requisitions form. At the header level, the user will enter the requisition number (if the app is set up to accept manual numbering), a requisition type and a description. At the line level, the user will enter the line type (Goods, Service, Labor etc.), and item (not required), a category (required), a description of the item or category, the units and the amount, the requestor, inventory org and location. Vendor and Vendor Site are optional. The user can enter multiple lines. After the line is entered, the user will enter the distributions for each line. Each line must have at least one distribution, but can consist of multiple. The distribution will be the code combination and/or project information for the purchase. When the user saves, the PR Account Generator is called to validate the code combination or if customized build the code combination based on auto-accounting rules from Oracle Projects. When the entry is saved, records are created in the following tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Import Entry – In order to import requisitions, the source must be mapped to the fields needed in the requisition interface. Coding will need to be done to utilize this functionality. Data will import using a concurrent process called Requisition Import. The data is brought into Oracle Purchasing and creates the header, lines and distributions. The main tables are:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Requisition Approval
Requisitions are approved based on specific application set-ups. The main set ups consist of determining of Approval Groups that determine the dollar value by document and/or account combination. The approval group is then assigned to a job title set up in the Oracle HR system. The amount assigned to the title determines who can have final approval authority. The user will submit the requisition from approval. The workflow then calls a process to build the initial approval list based on employee/supervisor (or position hierarchy if the system is set up that way). The list will include all approvers necessary to approve the requisition total. If there is no person with a high enough authority to approve, the requisition goes back to an incomplete status and the users will determine where the gap is and correct the assignments. If the requisition is on the incorrect path, the approver may forward the requisition to the correct person. The workflow will then call a procedure to rebuild the approval list based upon the forward to person’s employee/supervisor relationship. The procedure ends the current approval list header and creates a new header and lines. The history and status of the requisition is kept in the action history, which is viewable from the requisition form or from the requisition notification. The main tables are:
PER_ALL_PEOPLE_F
PER_JOBS
PER_ALL_ASSIGNMENTS_F
GL_CODE_COMBINATIONS
PO_CONTROL_RULES
PO_CONTROL_GROUPS_ALL
PO_CONTROL_FUNCTIONS
PO_POSITION_CONTROLS_ALL
PO_ACTION_HISTORY
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES
Purchase Order Entry
Purchase orders can be created 2 ways, autocreating from an approved requisition or by manual entry into the from.
Autocreate – In the autocreate form, the user will enter a requisition or search criteria to pull multiple requisitions available to be turned into purchase orders. The query will produce the lines available and the user can select all or any combination to create the purchase order. Example, if there are 3 requisitions approved for one vendor, the user can create one purchase order with all of the requisition information. The main tables are
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Manual entry – The user enters into the Purchase Order form the same information as entry for the requisitions. The tables listed above are the main tables.
11i Oracle Payables Technical Information
Expense Report/Invoice Entry
Expense reports are entered using Web Expenses. This is multi-org sensitive, therefore each instance is set up with a template. The template is stored in AP_EXPENSE_REPORTS_ALL. The expense reports are submitted for approval and routed using workflow. When the expense report is approved, the payables department reviews and approved the expense report. Expense Report information is stored in the following tables
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL
The accounts payable department runs a concurrent process call “Payables Invoice Import”. This process turns approved expense reports into invoices. The process populates the following tables
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_AL.
These are the exact tables that are populated when an invoice is entered directly into the AP Invoice Entry form. Invoices must be approved and accounted. Approval can be done on individual invoices through the form or by running a concurrent process called “Payables Approval”. Once the invoice is approved, the accounting process can be run. This can also be done by through the AP Invoice Entry form or by running the concurrent process “Payables Accounting Process”. The process populates the following tables
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
These are the tables that are used to create the journal entries that will go to the General Ledger. This process is called “Payables Transfer to General Ledger”. The process takes all lines in the AP_AE tables (whose GL_TRANSFER_FLAG = N and are within the parameters set by the user submitting the program) and inserts them into the GL_INTERFACE table. If the user marks Journal Import to Y when submitting, the completion of the transfer to GL process will kick off the Journal Import. This will then create the data in the GL_JOURNAL_BATCHES, GL_JOURNAL_HEADERS, and
GL_JOURNAL_LINES tables.
Creating Payments
Creating payments is the same basic navigation as creating invoices. An Invoice must be approved (does not have to have the accounting run yet). The payment can be created in the Invoice Entry form after the invoice is approved. The payments can also be created in batches. From the Payment Batches form, the user will enter the criteria to build the payment batch. The user will then choose actions and build the batch. The data will be inserted into the following tables:
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
After the batch is built, formatted and confirmed, the user will need to create accounting and transfer to GL, as explained above.
Expense reports are entered using Web Expenses. This is multi-org sensitive, therefore each instance is set up with a template. The template is stored in AP_EXPENSE_REPORTS_ALL. The expense reports are submitted for approval and routed using workflow. When the expense report is approved, the payables department reviews and approved the expense report. Expense Report information is stored in the following tables
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL
The accounts payable department runs a concurrent process call “Payables Invoice Import”. This process turns approved expense reports into invoices. The process populates the following tables
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_AL.
These are the exact tables that are populated when an invoice is entered directly into the AP Invoice Entry form. Invoices must be approved and accounted. Approval can be done on individual invoices through the form or by running a concurrent process called “Payables Approval”. Once the invoice is approved, the accounting process can be run. This can also be done by through the AP Invoice Entry form or by running the concurrent process “Payables Accounting Process”. The process populates the following tables
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
These are the tables that are used to create the journal entries that will go to the General Ledger. This process is called “Payables Transfer to General Ledger”. The process takes all lines in the AP_AE tables (whose GL_TRANSFER_FLAG = N and are within the parameters set by the user submitting the program) and inserts them into the GL_INTERFACE table. If the user marks Journal Import to Y when submitting, the completion of the transfer to GL process will kick off the Journal Import. This will then create the data in the GL_JOURNAL_BATCHES, GL_JOURNAL_HEADERS, and
GL_JOURNAL_LINES tables.
Creating Payments
Creating payments is the same basic navigation as creating invoices. An Invoice must be approved (does not have to have the accounting run yet). The payment can be created in the Invoice Entry form after the invoice is approved. The payments can also be created in batches. From the Payment Batches form, the user will enter the criteria to build the payment batch. The user will then choose actions and build the batch. The data will be inserted into the following tables:
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
After the batch is built, formatted and confirmed, the user will need to create accounting and transfer to GL, as explained above.
Tuesday, February 17, 2009
How to remove page breaks from 6i reports
Navigate to System Administrator Responisbility and query for the report name in the concurrent define section...
System Administrator -> Concurrent -> Program -> Define
Use the Printer style as BACS and check the Style Required box.
Once the above steps are done save the changes and navigate to the concerned responsibility to run the report. The report output will not have page breaks anymore. Generally this style is used for creating output files through reports.
System Administrator -> Concurrent -> Program -> Define
Use the Printer style as BACS and check the Style Required box.
Once the above steps are done save the changes and navigate to the concerned responsibility to run the report. The report output will not have page breaks anymore. Generally this style is used for creating output files through reports.
Sunday, February 15, 2009
How to authorize an employee to enter expense reports for another employee
You can authorize an employee to enter expense reports for another employee. An employee who is assigned the responsibility to enter expense reports for another employee is an authorized delegate. There are 2 ways this could be done:
1. Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.
2. Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.
1. Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.
2. Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.
Thursday, February 5, 2009
How to make a Oracle form 'Query Only'
Follow the following steps inorder to make a form as Query only/Read Only.
1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..
Example: MODE="PROJECT" QUERY_ONLY=YES
1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..
Example: MODE="PROJECT" QUERY_ONLY=YES
Tuesday, February 3, 2009
ORA-29280: invalid directory path
ORA-29280: invalid directory path
Before moving furthur in this post, please check if the directory has full permissions (Read, Write, Execute) or not. The error will also occour when there are no permissions to the directory. Once you set full permissions to the directory, try to run the program and if it still errors with the 'invalid directory path' error then please proceed furthur below for some more solutions....
Cause: A corresponding directory object does not exist.
Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.
We can derieve the directory object parameter from the following sql query:
-------------------------------------
SELECT VALUE
FROM v$parameter
WHERE NAME = 'utl_file_dir';
-------------------------------------
We can also use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.
Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in the init.ora file. In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.
Steps to create a example directory entry:
sql> Connect apps/apps
sql> create directory CESDIR_COMMON as '/home/common';
sql> grant read on directory CESDIR_COMMON to public;
Before moving furthur in this post, please check if the directory has full permissions (Read, Write, Execute) or not. The error will also occour when there are no permissions to the directory. Once you set full permissions to the directory, try to run the program and if it still errors with the 'invalid directory path' error then please proceed furthur below for some more solutions....
Cause: A corresponding directory object does not exist.
Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.
We can derieve the directory object parameter from the following sql query:
-------------------------------------
SELECT VALUE
FROM v$parameter
WHERE NAME = 'utl_file_dir';
-------------------------------------
We can also use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.
Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in the init.ora file. In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.
Steps to create a example directory entry:
sql> Connect apps/apps
sql> create directory CESDIR_COMMON as '/home/common';
sql> grant read on directory CESDIR_COMMON to public;
Validating notification response
I received a requirement from one of my clients to force the user(approver) to enter the comments value before approving the notification..This notification contains 2 responses (1. Approve, 2. Reject), so when the approver rejects the notification we need to make the Comments field in the notification as mandatatory and the notification should not go to the next stage untill he/she enters the comments field before clicking the Reject button in the workflow notification.
Fix: To implement this we need to write a Post notification function which validates the response and checks whether there is a value included in the response attribute. This post notification function needs to be placed in the workflow package and we need to call it the pl/sql block of the notification.
The code in the Post notification function would looks like this.
--------------------------------------------------------------------
PROCEDURE set_reject_comment (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2
)
IS
v_response VARCHAR2 (2000);
l_user_comment VARCHAR2 (2000);
approval_comment EXCEPTION;
BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
v_response :=
wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
l_user_comment :=
wf_notification.getattrtext (wf_engine.context_nid,
'APPROVAL_COMMENT'
);
/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
BEGIN
IF ( funcmode = 'RESPOND'
AND v_response = 'REJECTED'
AND l_user_comment IS NULL
)
THEN
RAISE approval_comment;
END IF;
EXCEPTION
WHEN approval_comment
THEN
raise_application_error
(-20010,
'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
);
RAISE;
END;
COMMIT;
END set_reject_comment;
--------------------------------------------------------------------
Fix: To implement this we need to write a Post notification function which validates the response and checks whether there is a value included in the response attribute. This post notification function needs to be placed in the workflow package and we need to call it the pl/sql block of the notification.
The code in the Post notification function would looks like this.
--------------------------------------------------------------------
PROCEDURE set_reject_comment (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2
)
IS
v_response VARCHAR2 (2000);
l_user_comment VARCHAR2 (2000);
approval_comment EXCEPTION;
BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
v_response :=
wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
l_user_comment :=
wf_notification.getattrtext (wf_engine.context_nid,
'APPROVAL_COMMENT'
);
/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
BEGIN
IF ( funcmode = 'RESPOND'
AND v_response = 'REJECTED'
AND l_user_comment IS NULL
)
THEN
RAISE approval_comment;
END IF;
EXCEPTION
WHEN approval_comment
THEN
raise_application_error
(-20010,
'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
);
RAISE;
END;
COMMIT;
END set_reject_comment;
--------------------------------------------------------------------
Tuesday, January 6, 2009
Pivot queries
Pivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. Pivoting is a common technique, especially for reporting, and it has been possible to generate pivoted resultsets with SQL for many years and Oracle versions.
Lets explain this with an example:
1. The basic stucture of our table would be
2. We would like to see the output showing each of these 4 teams and their point tables for these 3 years.
3. Lets now see how to implement the requirement:
i) Here's the data
CREATE TABLE xx1 (Year1 NUMBER(4), team VARCHAR2(16), points NUMBER(3));
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India', 21);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India1', 28);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India2', 19);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India3', 10);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India', 18);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India1', 26);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India2', 2);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India3', 14);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India', 16);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India1', 27);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India2', 15);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India3', 10);
ii) Use "decode" to turn the year1 row into a column. Take a look at our "sparse" matrix.
SELECT team,
MAX (DECODE (year1, 2007, points, NULL)) Yr2007,
MAX (DECODE (year1, 2008, points, NULL)) Yr2008,
MAX (DECODE (year1, 2009, points, NULL)) Yr2009
FROM (SELECT year1, team, points FROM xx1)
GROUP BY team;
Lets explain this with an example:
1. The basic stucture of our table would be
2. We would like to see the output showing each of these 4 teams and their point tables for these 3 years.
3. Lets now see how to implement the requirement:
i) Here's the data
CREATE TABLE xx1 (Year1 NUMBER(4), team VARCHAR2(16), points NUMBER(3));
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India', 21);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India1', 28);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India2', 19);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India3', 10);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India', 18);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India1', 26);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India2', 2);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India3', 14);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India', 16);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India1', 27);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India2', 15);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India3', 10);
ii) Use "decode" to turn the year1 row into a column. Take a look at our "sparse" matrix.
SELECT team,
MAX (DECODE (year1, 2007, points, NULL)) Yr2007,
MAX (DECODE (year1, 2008, points, NULL)) Yr2008,
MAX (DECODE (year1, 2009, points, NULL)) Yr2009
FROM (SELECT year1, team, points FROM xx1)
GROUP BY team;
Monday, December 15, 2008
Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table
STATUS_CODE Column:
A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting
PHASE_CODE column
C - Completed
I - Inactive
P - Pending
R - Running
A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting
PHASE_CODE column
C - Completed
I - Inactive
P - Pending
R - Running
Monday, December 8, 2008
DateTrack History view
The name of the default DateTrack History view is the same as that of the base table, except that the suffix _F is replaced by _D. For example, if the base table is PER_ALL_PEOPLE_F, the application looks for a view called PER_ALL_PEOPLE_D.
Note: It is possible to define more than one History view for each datetracked table, so there might be examples where the History view name does not follow this naming convention.
When a view exists, the application reads the information about the entity name and column prompts from the DateTrack tables:
• DT_TITLE_PROMPTS_TL
• DT_DATE_PROMPTS_TL
• DT_COLUMN_PROMPTS_TL
If the column information is not available in the DT_COLUMN_PROMPTS_TL table, the information is obtained from the view definition. The DateTrack History code modifies the column names of the table or view before presenting them. Underscores are replaced by spaces and the first letter of each word appears in upper case.
Note: It is possible to define more than one History view for each datetracked table, so there might be examples where the History view name does not follow this naming convention.
When a view exists, the application reads the information about the entity name and column prompts from the DateTrack tables:
• DT_TITLE_PROMPTS_TL
• DT_DATE_PROMPTS_TL
• DT_COLUMN_PROMPTS_TL
If the column information is not available in the DT_COLUMN_PROMPTS_TL table, the information is obtained from the view definition. The DateTrack History code modifies the column names of the table or view before presenting them. Underscores are replaced by spaces and the first letter of each word appears in upper case.
Understanding the p_validate Control Parameter in HRMS API's
Every published API includes the p_validate control parameter. When this parameter is set to FALSE (the default value), the procedure executes all validation for that business function. If the operation is valid, the database rows/values are inserted or updated or deleted. Any non warning OUT parameters, warning OUT parameters and IN OUT parameters are all set with specific values.
When the p_validate parameter is set to TRUE, the API only checks that the operation is valid. It does so by issuing a savepoint at the start of the procedure and rolling back to that savepoint at the end. You do not have access to these internal savepoints. If the procedure is successful, without raising any validation errors, then non-warning OUT parameters are set to null, warning OUT parameters are set to a specific value, and IN OUT parameters are reset to their IN values.
In some cases we may want to write our own PL/SQL routines using the public API procedures as building blocks. This enables us to write routines specific to our business needs. For example, say that we have a business requirement to apply a DateTracked update to a row and then apply a DateTrack delete to the same row in the future. We could write an "update_and_future_del" procedure that calls two of the standard APIs.
When calling each standard API, p_validate must be set to false. If true is used the update procedure call is rolled back. So when the delete procedure is called, it is working on the non-updated version of the row. However when p_validate is set to false, the update is not rolled back. Thus, the delete call operates as if the user really wanted to apply the whole transaction.
If we want to be able to check that the update and delete operation is valid, you must issue your own savepoint and rollback commands. As the APIs do not issue any commits, there is no danger of part of the work being left in the database. It is the responsibility of the calling code to issue commits.
Note: You should not use our API procedure names for the savepoint names. An unexpected result may occur if you do not use different names.
When the p_validate parameter is set to TRUE, the API only checks that the operation is valid. It does so by issuing a savepoint at the start of the procedure and rolling back to that savepoint at the end. You do not have access to these internal savepoints. If the procedure is successful, without raising any validation errors, then non-warning OUT parameters are set to null, warning OUT parameters are set to a specific value, and IN OUT parameters are reset to their IN values.
In some cases we may want to write our own PL/SQL routines using the public API procedures as building blocks. This enables us to write routines specific to our business needs. For example, say that we have a business requirement to apply a DateTracked update to a row and then apply a DateTrack delete to the same row in the future. We could write an "update_and_future_del" procedure that calls two of the standard APIs.
When calling each standard API, p_validate must be set to false. If true is used the update procedure call is rolled back. So when the delete procedure is called, it is working on the non-updated version of the row. However when p_validate is set to false, the update is not rolled back. Thus, the delete call operates as if the user really wanted to apply the whole transaction.
If we want to be able to check that the update and delete operation is valid, you must issue your own savepoint and rollback commands. As the APIs do not issue any commits, there is no danger of part of the work being left in the database. It is the responsibility of the calling code to issue commits.
Note: You should not use our API procedure names for the savepoint names. An unexpected result may occur if you do not use different names.
Wednesday, November 19, 2008
SMSing using PL/SQL
1. Compile the below procedure on sql*plus
CREATE OR REPLACE PROCEDURE send_sms (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2
)
AS
mailcon UTL_SMTP.connection;
BEGIN
mailcon := UTL_SMTP.open_connection ('localhost');
UTL_SMTP.helo (mailcon, 'localhost');
UTL_SMTP.mail (mailcon, p_sender);
UTL_SMTP.rcpt (mailcon, p_recipient);
UTL_SMTP.DATA (mailcon,
'From: '
|| p_sender
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| UTL_TCP.crlf
|| 'Subject: SMS From Database'
|| UTL_TCP.crlf
|| p_message
);
UTL_SMTP.quit (mailcon);
END;
2. We need to execute the above procedure so as to send SMS. The systax is as follows:
exec SEND_SMS('arun@yahoo.com', '984812345@ideacellular.net','This is my first SMS');
Note: In place of the '984812345@ideacellular.net' you need to provide your mobile number. Also please refer to the below list of mobile operator and the area of the service.
Andhra Pradesh AirTel-----------Mobile No@airtelap.com
Andhra Pradesh Idea Cellular-----Mobile No@ideacellular.net
Chennai Skycell/Airtel------------Mobile No@airtelchennai.com
Chennai RPG Cellular-------------Mobile No@rpgmail.net
Delhi Airtel-----------------------Mobile No@airtelmail.com
Delhi Hutch-----------------------Mobile No@delhi.hutch.co.in
Gujarat Airtel---------------------Mobile No@airtelmail.com
Gujarat Idea Cellular--------------Mobile No@ideacellular.net
Gujarat Celforce/Fascel-----------Mobile No@celforce.com
Goa Airtel-------------------------Mobile No@airtelmail.com
Goa BPL Mobile-------------------Mobile No@bplmobile.com
Goa Idea Cellular------------------Mobile No@ideacellular.net
Haryana Airtel--------------------Mobile No@airtelmail.com
Haryana Escotel-------------------Mobile No@escotelmobile.com
Himachal Pradesh Airtel-----------Mobile No@airtelmail.com
Karnataka Airtel-------------------Mobile No@airtelkk.com
Kerala Airtel-----------------------Mobile No@airtelkerala.com
Kerala Escotel---------------------Mobile No@escotelmobile.com
Kerala BPL Mobile-----------------Mobile No@bplmobile.com
Kolkata Airtel----------------------Mobile No @airtelkol.com
Madhya Pradesh Airtel-------------Mobile No@airtelmail.com
Maharashtra Airtel-----------------Mobile No@airtelmail.com
Maharashtra BPL Mobile-----------Mobile No@bplmobile.com
Maharashtra Idea Cellular----------Mobile No@ideacellular.net
Mumbai Airtel----------------------Mobile No@airtelmail.com
Mumbai BPL Mobile----------------Mobile No@bplmobile.com
Punjab Airtel-----------------------Mobile No@airtelmail.com
Pondicherry BPL Mobile------------Mobile No @bplmobile.com
Tamil Nadu Airtel-------------------Mobile No@airtelmail.com
Tamil Nadu BPL Mobile-------------Mobile No@bplmobile.com
Tamil Nadu Aircel-------------------Mobile No@airsms.com
UP (West) Escotel-------------------Mobile No@escotelmobile.com
CREATE OR REPLACE PROCEDURE send_sms (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2
)
AS
mailcon UTL_SMTP.connection;
BEGIN
mailcon := UTL_SMTP.open_connection ('localhost');
UTL_SMTP.helo (mailcon, 'localhost');
UTL_SMTP.mail (mailcon, p_sender);
UTL_SMTP.rcpt (mailcon, p_recipient);
UTL_SMTP.DATA (mailcon,
'From: '
|| p_sender
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| UTL_TCP.crlf
|| 'Subject: SMS From Database'
|| UTL_TCP.crlf
|| p_message
);
UTL_SMTP.quit (mailcon);
END;
2. We need to execute the above procedure so as to send SMS. The systax is as follows:
exec SEND_SMS('arun@yahoo.com', '984812345@ideacellular.net','This is my first SMS');
Note: In place of the '984812345@ideacellular.net' you need to provide your mobile number. Also please refer to the below list of mobile operator and the area of the service.
Andhra Pradesh AirTel-----------Mobile No@airtelap.com
Andhra Pradesh Idea Cellular-----Mobile No@ideacellular.net
Chennai Skycell/Airtel------------Mobile No@airtelchennai.com
Chennai RPG Cellular-------------Mobile No@rpgmail.net
Delhi Airtel-----------------------Mobile No@airtelmail.com
Delhi Hutch-----------------------Mobile No@delhi.hutch.co.in
Gujarat Airtel---------------------Mobile No@airtelmail.com
Gujarat Idea Cellular--------------Mobile No@ideacellular.net
Gujarat Celforce/Fascel-----------Mobile No@celforce.com
Goa Airtel-------------------------Mobile No@airtelmail.com
Goa BPL Mobile-------------------Mobile No@bplmobile.com
Goa Idea Cellular------------------Mobile No@ideacellular.net
Haryana Airtel--------------------Mobile No@airtelmail.com
Haryana Escotel-------------------Mobile No@escotelmobile.com
Himachal Pradesh Airtel-----------Mobile No@airtelmail.com
Karnataka Airtel-------------------Mobile No@airtelkk.com
Kerala Airtel-----------------------Mobile No@airtelkerala.com
Kerala Escotel---------------------Mobile No@escotelmobile.com
Kerala BPL Mobile-----------------Mobile No@bplmobile.com
Kolkata Airtel----------------------Mobile No @airtelkol.com
Madhya Pradesh Airtel-------------Mobile No@airtelmail.com
Maharashtra Airtel-----------------Mobile No@airtelmail.com
Maharashtra BPL Mobile-----------Mobile No@bplmobile.com
Maharashtra Idea Cellular----------Mobile No@ideacellular.net
Mumbai Airtel----------------------Mobile No@airtelmail.com
Mumbai BPL Mobile----------------Mobile No@bplmobile.com
Punjab Airtel-----------------------Mobile No@airtelmail.com
Pondicherry BPL Mobile------------Mobile No @bplmobile.com
Tamil Nadu Airtel-------------------Mobile No@airtelmail.com
Tamil Nadu BPL Mobile-------------Mobile No@bplmobile.com
Tamil Nadu Aircel-------------------Mobile No@airsms.com
UP (West) Escotel-------------------Mobile No@escotelmobile.com
How to add comments on a table or a column.
If we want to give a comment on a table :
COMMENT ON TABLE
IS 'the comments should go here';
If we want to give a comment on a column :
COMMENT ON TABLE | COLUMN table.column
IS 'the comments should go here';
The entries of these comments on the table goes in the following tables:
For tables:
select * from all_tab_comments
where table_name ='table_name'
select * from user_tab_comments
where table_name = 'table_name'
For Columns:
select * from all_col_comments
where table_name = 'table_name'
select * from user_col_comments
where table_name = 'table_name'
COMMENT ON TABLE
IS 'the comments should go here';
If we want to give a comment on a column :
COMMENT ON TABLE
IS 'the comments should go here';
The entries of these comments on the table goes in the following tables:
For tables:
select * from all_tab_comments
where table_name ='table_name'
select * from user_tab_comments
where table_name = 'table_name'
For Columns:
select * from all_col_comments
where table_name = 'table_name'
select * from user_col_comments
where table_name = 'table_name'
Tuesday, November 18, 2008
How to Compile and port a Form (fmb) in Oracle
1. Port the attached form to unix directory to any top using FTP (Preferable we port the form to the AU_TOP)
NOTE: Copy .fmb files (form files) in Binary mode.
2. Connect to the telnet and change the directory to the top where we have ported the fmd.
Run the following commands in the above directory in Telnet:
a) FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US
b) f60gen module=formname.fmb userid=apps/apps output_file=formname.fmx module_type=form batch=no compile_all=special
example:
f60gen module=XXTRRBT.fmb userid=apps/apps output_file=XXTRRBT.fmx module_type=form batch=no compile_all=special
NOTE: Copy .fmb files (form files) in Binary mode.
2. Connect to the telnet and change the directory to the top where we have ported the fmd.
Run the following commands in the above directory in Telnet:
a) FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US
b) f60gen module=formname.fmb userid=apps/apps output_file=formname.fmx module_type=form batch=no compile_all=special
example:
f60gen module=XXTRRBT.fmb userid=apps/apps output_file=XXTRRBT.fmx module_type=form batch=no compile_all=special
Monday, November 17, 2008
Excellent information on all Oracle Application module topics
Please visit the following website to find excellent information on all Oracle Application Modules:
http://download-uk.oracle.com/docs/cd/A60725_05/html/comnls/us/index.htm
http://download-uk.oracle.com/docs/cd/A60725_05/html/comnls/us/index.htm
Script to exclude the control M(^M) from unix files
The simple way to replace the control M character in UNIX format is as follows.
1. dos2unix filename newfilename
or
2. Open the file which contains the control M character and type the following
:%s/Control v control M//g
1. dos2unix filename newfilename
or
2. Open the file which contains the control M character and type the following
:%s/Control v control M//g
Workflow Table Information
WF_ITEM_TYPES
The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE
WF_ITEM_ATTRIBUTES
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. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_ACTIVITIES
WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG
WF_ACTIVITY_ATTRIBUTES
The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_MESSAGES
WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES contains message attribute definitions.
WF_NOTIFICATIONS
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.
WF_NOTIFICATION_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.
WF_ITEMS
WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE
WF_ITEM_ACTIVITY_STATUSES
The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)
WF_ITEM_ACTIVITY_STATUSES_H
The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
WF_PROCESS_ACTIVITIES
WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_ACTIVITY_TRANSITIONS
The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL
WF_ACTIVITY_ATTR_VALUES
The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE
WF_ITEM_ATTRIBUTES
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. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_ACTIVITIES
WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG
WF_ACTIVITY_ATTRIBUTES
The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_MESSAGES
WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES contains message attribute definitions.
WF_NOTIFICATIONS
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.
WF_NOTIFICATION_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.
WF_ITEMS
WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE
WF_ITEM_ACTIVITY_STATUSES
The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)
WF_ITEM_ACTIVITY_STATUSES_H
The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
WF_PROCESS_ACTIVITIES
WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_ACTIVITY_TRANSITIONS
The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL
WF_ACTIVITY_ATTR_VALUES
The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
Sunday, November 16, 2008
Approved Supplier List (ASL)
Approved Supplier Lists in Oracle is the term used to describe a list of items and commodities that have approved sources from a list of suppliers.
All procurement organisations maintain lists that associate the items and services they buy with the companies who supply them, either formally or informally. Data stored in a controlled, global repository containing relevant details about each ship-from/ship-to/item relationship, is known as an Approved Supplier List (ASL).
Navigation for Approved Supplier List
PO Super User -> Supply base -> Approved Supplier List
Select the organization, then define the supplier for category or Item for which you want to define, and status to approved.
API's used to insert the Approved Supplier List's are as follows:
po_asl_ths.insert_row
po_asl_attributes_ths.insert_row
All procurement organisations maintain lists that associate the items and services they buy with the companies who supply them, either formally or informally. Data stored in a controlled, global repository containing relevant details about each ship-from/ship-to/item relationship, is known as an Approved Supplier List (ASL).
Navigation for Approved Supplier List
PO Super User -> Supply base -> Approved Supplier List
Select the organization, then define the supplier for category or Item for which you want to define, and status to approved.
API's used to insert the Approved Supplier List's are as follows:
po_asl_ths.insert_row
po_asl_attributes_ths.insert_row
Friday, November 14, 2008
Index on Clob datatype
CREATE INDEX ON () INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE');
The index creation includes a clause for setting the INDEXTYPE. I specified CTXSYS.CONTEXT as the type of index to create. CTXSYS is the Oracle Text schema, and this schema owns Oracle Text built-in features. The CTXAPP role granted to my user (TEXT_USER) provides permissions to use this index, but it must still be fully qualified with the schema name. CONTEXT is the type of index I am creating.
The index creation includes a clause for setting the INDEXTYPE. I specified CTXSYS.CONTEXT as the type of index to create. CTXSYS is the Oracle Text schema, and this schema owns Oracle Text built-in features. The CTXAPP role granted to my user (TEXT_USER) provides permissions to use this index, but it must still be fully qualified with the schema name. CONTEXT is the type of index I am creating.
Thursday, October 23, 2008
Overview of Inventory Transactions
1. Transferring material between subinventories.
2. Performing miscellaneous transactions (miscellaneous issues and receipts).
3. Transferring material between Organizations.
4. Tracking lots and serial numbers for an item (Assigning Lot Numbers, Assigning Serial Numbers).
5. Generating material shortage alerts and material shortage notifications.
6. Entering and maintaining movement statistics information.
7. Viewing Material Transactions.
8. Viewing material transaction accounting distributions.
9. Viewing Transaction Summaries for a range of dates.
10. Viewing Pending Transactions.
11. Viewing and Updating pending Transaction Interface Activity.
12. Purging Transaction History.
13. Viewing Serial Genealogy.
Transferring Material Between Subinventories:
1. You can transfer material within your current organization between subinventories, or between two locators within the same subinventory.
2. You can transfer from asset to expense subinventories, as well as from tracked to non–tracked subinventories. (Here Asset, Expense, Tracked and Non-Tracked are the types of subinventories)
3. If an item has a restricted list of subinventories (Item is sub inventory controlled.), you can only transfer material from and to subinventories in that list.
4. Oracle Inventory allows you to use user–defined transaction types when performing a subinventory transfer.
Steps for entering a subinventory transfer:
1. Navigate to the Subinventory Transfer window.
2. Enter the date and time of entry for the transaction.
3. Enter a transaction type for the subinventory transfer. This can either be a predefined system type or one you defined.
4. Optionally, enter the source of the transaction type ex: Inventory
5. Optionally, indicate if inventory information should be defaulted from the serial number.
6. Choose Transaction Lines in the Subinventory Transfer window. The Transaction Lines Detail folder window appears.
7. Enter an inventory item to transfer. If you choose to default inventory information from the serial number (step5), enter a serial number.
8. Optionally, enter the revision for the item. You must enter a value here if the item is under revision control.
9. Enter the subinventories from and to which to transfer material. Enter the same subinventory in the Sub and To Sub fields to transfer material between locators.
10. Optionally, enter the locators from and to which to transfer the item. You must enter a value here if you established locator control. You can enter a new value in the To Locator field only if you defined locator control as dynamic entry.
11. Optionally, enter a lot number for the item. If you want to enter multiple lot numbers, complete the remaining steps then choose the Lot/Serial button to display the Lot Entry window.
12. Enter a unit of measure. This can be the primary unit of measure (the default) or any valid alternate unit of measure. If you enter an alternate unit of measure, Oracle Inventory issues the quantity you specify in this unit of measure. Oracle Inventory also converts the quantity to the primary unit of measure so that it can correctly update the on–hand quantity.
13. Enter the quantity of the inventory item to transfer, based on the unit of measure you specified.
14. Optionally, enter a reason code for the transaction. For example, you can use reason codes to allow you to mark exceptional charges to support a quality data collection and reporting system.
15. Optionally, enter up to 240 characters of free text that describes the transaction. (Reference field).
To view quantity available and quantity on hand values:
Look at the following fields in the form.
Available: Displays the quantity available to transfer, based on the unit of measure you specified. The available quantity is the quantity on hand less all reservations for the item. This amount could include the amount you have reserved if you enter a transaction source that has reservations against it. The available quantity includes reservations against current transaction source. The available quantity is specific to the revision level, lot number, From subinventory, and From locator you specify for the transfer.
Reservations will always be against a Transaction Source (Inventory, Purchase Order etc.)
On hand: Displays the current on–hand quantity for the item, based on the unit of measure you specified. The on–hand quantity is specific to the revision, lot number, From subinventory, and From locator you specify for the transfer. On–hand includes quantities for pending transactions in the MTL–MATERIAL–TRANSACTIONS table.
Performing Miscellaneous Transactions
With a miscellaneous transaction you can issue material to or receive material from general ledger accounts in your current organization.
2. Performing miscellaneous transactions (miscellaneous issues and receipts).
3. Transferring material between Organizations.
4. Tracking lots and serial numbers for an item (Assigning Lot Numbers, Assigning Serial Numbers).
5. Generating material shortage alerts and material shortage notifications.
6. Entering and maintaining movement statistics information.
7. Viewing Material Transactions.
8. Viewing material transaction accounting distributions.
9. Viewing Transaction Summaries for a range of dates.
10. Viewing Pending Transactions.
11. Viewing and Updating pending Transaction Interface Activity.
12. Purging Transaction History.
13. Viewing Serial Genealogy.
Transferring Material Between Subinventories:
1. You can transfer material within your current organization between subinventories, or between two locators within the same subinventory.
2. You can transfer from asset to expense subinventories, as well as from tracked to non–tracked subinventories. (Here Asset, Expense, Tracked and Non-Tracked are the types of subinventories)
3. If an item has a restricted list of subinventories (Item is sub inventory controlled.), you can only transfer material from and to subinventories in that list.
4. Oracle Inventory allows you to use user–defined transaction types when performing a subinventory transfer.
Steps for entering a subinventory transfer:
1. Navigate to the Subinventory Transfer window.
2. Enter the date and time of entry for the transaction.
3. Enter a transaction type for the subinventory transfer. This can either be a predefined system type or one you defined.
4. Optionally, enter the source of the transaction type ex: Inventory
5. Optionally, indicate if inventory information should be defaulted from the serial number.
6. Choose Transaction Lines in the Subinventory Transfer window. The Transaction Lines Detail folder window appears.
7. Enter an inventory item to transfer. If you choose to default inventory information from the serial number (step5), enter a serial number.
8. Optionally, enter the revision for the item. You must enter a value here if the item is under revision control.
9. Enter the subinventories from and to which to transfer material. Enter the same subinventory in the Sub and To Sub fields to transfer material between locators.
10. Optionally, enter the locators from and to which to transfer the item. You must enter a value here if you established locator control. You can enter a new value in the To Locator field only if you defined locator control as dynamic entry.
11. Optionally, enter a lot number for the item. If you want to enter multiple lot numbers, complete the remaining steps then choose the Lot/Serial button to display the Lot Entry window.
12. Enter a unit of measure. This can be the primary unit of measure (the default) or any valid alternate unit of measure. If you enter an alternate unit of measure, Oracle Inventory issues the quantity you specify in this unit of measure. Oracle Inventory also converts the quantity to the primary unit of measure so that it can correctly update the on–hand quantity.
13. Enter the quantity of the inventory item to transfer, based on the unit of measure you specified.
14. Optionally, enter a reason code for the transaction. For example, you can use reason codes to allow you to mark exceptional charges to support a quality data collection and reporting system.
15. Optionally, enter up to 240 characters of free text that describes the transaction. (Reference field).
To view quantity available and quantity on hand values:
Look at the following fields in the form.
Available: Displays the quantity available to transfer, based on the unit of measure you specified. The available quantity is the quantity on hand less all reservations for the item. This amount could include the amount you have reserved if you enter a transaction source that has reservations against it. The available quantity includes reservations against current transaction source. The available quantity is specific to the revision level, lot number, From subinventory, and From locator you specify for the transfer.
Reservations will always be against a Transaction Source (Inventory, Purchase Order etc.)
On hand: Displays the current on–hand quantity for the item, based on the unit of measure you specified. The on–hand quantity is specific to the revision, lot number, From subinventory, and From locator you specify for the transfer. On–hand includes quantities for pending transactions in the MTL–MATERIAL–TRANSACTIONS table.
Performing Miscellaneous Transactions
With a miscellaneous transaction you can issue material to or receive material from general ledger accounts in your current organization.
Thursday, October 16, 2008
Chr(13) appended to column values while performing sqlload
Usually when we do the sqlload the '|' appends to the end of the string. This '|' is knows as Chr(13) and can be removed once the sqlload is done using the following update statement:
Update [table_name]
set [column_name] = trim(chr(13) from [column_name])
Update [table_name]
set [column_name] = trim(chr(13) from [column_name])
Deadlocks and how to break them?
What is a deadlock?
Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:
ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
Deadlocks in Oracle result in this error:
ORA-00060: deadlock detected while waiting for resource
If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.
How to Break them?
SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id
SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'
ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'
Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:
ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
Deadlocks in Oracle result in this error:
ORA-00060: deadlock detected while waiting for resource
If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.
How to Break them?
SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id
SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'
ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'
Wednesday, October 15, 2008
All about MRP Sourcing Rules API and Single record insertion script
Standard Validation
Oracle Master Scheduling/MRP validates all required columns in the Sourcing Rule/Bill of Distribution API. For specific information on the data implied by these columns, see your Oracle Master Scheduling/MRP Technical Reference Manual for details.
If you do not want to update a particular column in:
❏ MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
Do not enter NULL for its corresponding interface parameter unless the default in the PL/SQL specification is NULL. Either use one of the missing parameter constants defined in the FND_API package (G_MISS_...), or do not pass any value at all.
For all flag parameters, pass in a Boolean constant defined in FND_API (G_TRUE or G_FALSE). Each time the API is called, it will check the allocation percent for each receiving organization that belongs to the sourcing rule or bill of distribution. If the total allocation percent is 100, the plannning_active attribute is set to a value of 1. Otherwise the attribute is set to 2.
Creating Sourcing Rule API Entries
When you create a new sourcing rule the following item level validations:
■ sourcing_rule_name: must be defined in the MRP_SOURCING _RULES table.
■ sourcing_rule_type: must be a either (1) Sourcing Rule or (2) Bill of
Distribution.If the sourcing rule does not already exist in the system - the Status attribute is set to 1.
When you create a new sourcing rule, the following record level validations occur:
■ organization_id: must be a valid organization defined in ORG_ORGANIZATION_DEFINITIONS.
■ The organization_id attribute is associated with a valid organization, unless it is null.
When you create a new sourcing rule, the following object level validations occur:
■ At least one receiving organization record is created.
■ At least one shipping organization record is created.
If validation is successful, a record is inserted into the MRP_SOURCING_RULES table.
Procedure Parameter Descriptions for MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
The following content describes all parameters used by the public API MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE procedure. Additional information on these parameters follows.
1. p_api_version_number
Used to compare the incoming API call's version number with the current version number. An error is returned if the version numbers are incompatible.
2. p_init_msg_list
Requests that the API initialize the message list on your behalf. If the x_msg_count is greater than 1, then the list of messages must be retrieved using the call FND_MSG_PUB.GET. The values are:
■ p_msg_index => I
■ p_encoded => F
■ p_data => 1_message
■ p_msg_index_out => 1_msg_index_out
where 1_message and 1_msg_index_out are local variables of types Varchar2(2000 and Number respectively.
Default Value: FND_API.G_FALSE
3. p_return_values
Requests that the API send back the values on your behalf.
Default Value: FND_API.G_FALSE
4. p_commit
Requests that the API update information for you after it completes its function.
Default Value: FND_API.G_FALSE
5. x_return_status
Requests that the API return the status of the data for you after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
6. x_msg_count
Indicates number of error messages API has encountered.
7. x_msg_data
Displays error message text. If the x_msg_count is equal to 1, then this contains the actual message.
8. p_sourcing_rule_rec
The sourcing rule or bill of distribution record referenced by the API.
Default Value: G_MISS_SOURCING_RULE_REC
9. p_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
Default Value: G_MISS_SOURCING_RULE_VAL_REC
10. p_receiving_org_tbl
The receiving organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_RECEIVING_ORG_TBL
11. p_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule.
Default Value: G_MISS_RECEIVING_ORG_VAL_TBL
12. p_shipping_org_tbl
The shipping organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_SHIPPING_ORG_TBL
13. p_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule.
Default Value: G_MISS_SHIPPING_ORG_VAL_TBL
14. x_sourcing_rule_rec
Result of the API data after it completes its function for the sourcing rule/bill of distribution record.
15. x_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
16. x_receiving_org_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule/bill of distribution record.
17. x_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the rule.
18. x_shipping_org_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule/bill of distribution record.
19. x_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the rule.
Record Parameter Descriptions
SOURCING_RULE_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SOURCING_RULE_REC_TYPE record with some additional information.
1. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. description
Text describing the sourcing rule record type.
Default Value: FND_API.G_MISS_CHAR
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. organization_id
The identification number for the organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
11. planning_active
Rule is active when the sum of the allocation percentages equals 100.
Default Value: FND_API.G_MISS_NUM
12. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
14. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_name
Valid name of rule defined in the MRP_SOURCING _RULES table.
Default Value: FND_API.G_MISS_CHAR
17. sourcing_rule_type
Valid types must be one of the following values:
■ (1) Sourcing Rule
■ (2) Bill of Distribution
Default Value: FND_API.G_MISS_NUM
18. status
If any validation fails, the API will return error status to the calling module. The Sourcing Rule API processes the rows and reports the following values for every record. If the sourcing rule does not already exist in the system - the Status attribute is set to 1. Processing status of the sourcing rule, valid values are:
■ (1)
■ (2)
Default Value: FND_API.G_MISS_NUM
19. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
20. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
21. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
RECEIVING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the RECEIVING_ORG_REC_TYPE
record with additional information:
1. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. disable_date
Date the receipt organization is no longer effective.
Default Value: FND_API.G_MISS_DATE
7. effective_date
Beginning date the receipt organization becomes effective.
Default Value: FND_API.G_MISS_DATE
8. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
9. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
10. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
11. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
14. receipt_organization_id
Identifier of the organization that serves as the destination for the sourcing rule or bill of distribution.
Default Value: FND_API.G_MISS_NUM
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
17. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
18. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
19. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
SHIPPING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SHIPPING_ORG_REC_TYPE record with additional informatioin.
1. sr_source_id
Primary key in the sourcing rule or bill of distribution table.
Default Value: FND_API.G_MISS_NUM
2. allocation_percent
Percentage allocated to each source organization/supplier site destination.
Default Value: FND_API.G_MISS_NUM
3. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
4. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
5. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
6. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
11. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
13. rank
Rank of the sources, valid values are non-zero integers.
Default Value: FND_API.G_MISS_NUM
14. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
15. secondary_inventory
Currently not used.
16. ship_method
Method used when transporting material between source and destination.
Default Value: FND_API.G_MISS_CHAR
17. source_organization_id
Identifier of the source organization.
Default Value: FND_API.G_MISS_NUM
18. source_type
Indicator of the type of source. Valid values are:
■ Make
■ Transfer
■ Buy
Default Value: FND_API.G_MISS_NUM
19. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
20. vendor_id
Identifier of the vendor suppling the materials.
Default Value: FND_API.G_MISS_NUM
21. vendor_site_id
Identifier where the vendor’s materials are located.
Default Value: FND_API.G_MISS_NUM
22. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
23. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
24. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
25. receiving_org_index
Foreign key to the receipt organization PL/SQL table.
Default Value: FND_API.G_MISS_NUM
Single Record insertion script for MRP Sourcing Rules
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_err_count NUMBER := 0;
l_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
l_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
l_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
l_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
l_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
l_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
o_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
o_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
o_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
o_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
o_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
o_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
BEGIN
fnd_message.clear;
l_sourcing_rule_rec := MRP_SOURCING_RULE_PUB.G_MISS_SOURCING_RULE_REC;
l_sourcing_rule_rec.sourcing_rule_name := '1234'; --SR Name
l_sourcing_rule_rec.Organization_Id:=207;
l_sourcing_rule_rec.planning_active := 1; -- Active?
l_sourcing_rule_rec.status := 1; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := 1; -- 1:Sourcing Rule
l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.OPERATION=>'CREATE';
l_receiving_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_RECEIVING_ORG_TBL;
l_shipping_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_SHIPPING_ORG_TBL;
--l_receiving_org_tbl(1).Sr_Receipt_Id:=207;
l_receiving_org_tbl(1).effective_date := trunc(sysdate)+1;
l_receiving_org_tbl(1).disable_date := trunc(sysdate)+7;
l_receiving_org_tbl(1).receipt_organization_id:=207;
l_receiving_org_tbl(1).operation := 'CREATE'; -- Create or Update
--l_shipping_org_tbl(1).Sr_Source_Id:=228;
l_shipping_org_tbl(1).rank := 1;
l_shipping_org_tbl(1).allocation_percent := 100; -- Allocation 100
l_shipping_org_tbl(1).source_type := 3; -- BUY FROM
l_shipping_org_tbl(1).vendor_id := 21;
l_shipping_org_tbl(1).receiving_org_index := 1;
l_shipping_org_tbl(1).operation := 'CREATE';
dbms_output.put_line('before call');
dbms_output.put_line('Operation before call
'||l_sourcing_rule_rec.operation);
MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE(
p_api_version_number =>1.0
,p_init_msg_list =>fnd_api.g_true
,p_commit => fnd_api.g_true
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
,p_sourcing_rule_rec => l_sourcing_rule_rec
,p_sourcing_rule_val_rec => l_sourcing_rule_val_rec
,p_receiving_org_tbl => l_receiving_org_tbl
,p_receiving_org_val_tbl => l_receiving_org_val_tbl
,p_shipping_org_tbl => l_shipping_org_tbl
,p_shipping_org_val_tbl => l_shipping_org_val_tbl
,x_sourcing_rule_rec => o_sourcing_rule_rec
,x_sourcing_rule_val_rec => o_sourcing_rule_val_rec
,x_receiving_org_tbl => o_receiving_org_tbl
,x_receiving_org_val_tbl => o_receiving_org_val_tbl
,x_shipping_org_tbl => o_shipping_org_tbl
,x_shipping_org_val_tbl => o_shipping_org_val_tbl
);
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Success!');
else
dbms_output.put_line('count:'||l_msg_count);
IF l_msg_count > 0 THEN
FOR l_index IN 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(
p_msg_index => l_index,
p_encoded => FND_API.G_FALSE);
dbms_output.put_line(substr(l_msg_data,1,250));
END LOOP;
dbms_output.put_line('MSG:'||o_sourcing_rule_rec.return_status);
END IF;
dbms_output.put_line('Failure!');
end if;
END;
Single Record insertion script for MRP Sourcing Rule Assignments
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_org_cnt NUMBER;
l_vendor_cnt NUMBER;
l_org_class VARCHAR2 (3);
l_org_num NUMBER;
l_line_num NUMBER := 0;
l_err_count NUMBER := 0;
p NUMBER;
l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
BEGIN
fnd_message.CLEAR;
-- l_assignment_set_rec.assignment_set_id := 3530;
-- l_assignment_set_rec.assignment_set_name := 'Test_src1_set';
-- l_assignment_set_rec.operation := 'CREATE';
l_assignment_tbl (1).assignment_set_id := 1;
l_assignment_tbl (1).assignment_type := 6;
l_assignment_tbl (1).operation := 'CREATE';
l_assignment_tbl (1).organization_id := 107;
l_assignment_tbl (1).inventory_item_id := 37002;
l_assignment_tbl (1).sourcing_rule_id := 1014;
l_assignment_tbl (1).sourcing_rule_type := 1;
mrp_src_assignment_pub.process_assignment
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_assignment_set_rec => l_assignment_set_rec,
p_assignment_set_val_rec => l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl => l_assignment_val_tbl,
x_assignment_set_rec => o_assignment_set_rec,
x_assignment_set_val_rec => o_assignment_set_val_rec,
x_assignment_tbl => o_assignment_tbl,
x_assignment_val_tbl => o_assignment_val_tbl
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
ELSE
DBMS_OUTPUT.put_line ('count:' || l_msg_count);
IF l_msg_count > 0
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data, 1, 250));
END LOOP;
DBMS_OUTPUT.put_line ('MSG:' || o_assignment_set_rec.return_status);
END IF;
DBMS_OUTPUT.put_line ('Failure!');
END IF;
END;
/
Oracle Master Scheduling/MRP validates all required columns in the Sourcing Rule/Bill of Distribution API. For specific information on the data implied by these columns, see your Oracle Master Scheduling/MRP Technical Reference Manual for details.
If you do not want to update a particular column in:
❏ MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
Do not enter NULL for its corresponding interface parameter unless the default in the PL/SQL specification is NULL. Either use one of the missing parameter constants defined in the FND_API package (G_MISS_...), or do not pass any value at all.
For all flag parameters, pass in a Boolean constant defined in FND_API (G_TRUE or G_FALSE). Each time the API is called, it will check the allocation percent for each receiving organization that belongs to the sourcing rule or bill of distribution. If the total allocation percent is 100, the plannning_active attribute is set to a value of 1. Otherwise the attribute is set to 2.
Creating Sourcing Rule API Entries
When you create a new sourcing rule the following item level validations:
■ sourcing_rule_name: must be defined in the MRP_SOURCING _RULES table.
■ sourcing_rule_type: must be a either (1) Sourcing Rule or (2) Bill of
Distribution.If the sourcing rule does not already exist in the system - the Status attribute is set to 1.
When you create a new sourcing rule, the following record level validations occur:
■ organization_id: must be a valid organization defined in ORG_ORGANIZATION_DEFINITIONS.
■ The organization_id attribute is associated with a valid organization, unless it is null.
When you create a new sourcing rule, the following object level validations occur:
■ At least one receiving organization record is created.
■ At least one shipping organization record is created.
If validation is successful, a record is inserted into the MRP_SOURCING_RULES table.
Procedure Parameter Descriptions for MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
The following content describes all parameters used by the public API MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE procedure. Additional information on these parameters follows.
1. p_api_version_number
Used to compare the incoming API call's version number with the current version number. An error is returned if the version numbers are incompatible.
2. p_init_msg_list
Requests that the API initialize the message list on your behalf. If the x_msg_count is greater than 1, then the list of messages must be retrieved using the call FND_MSG_PUB.GET. The values are:
■ p_msg_index => I
■ p_encoded => F
■ p_data => 1_message
■ p_msg_index_out => 1_msg_index_out
where 1_message and 1_msg_index_out are local variables of types Varchar2(2000 and Number respectively.
Default Value: FND_API.G_FALSE
3. p_return_values
Requests that the API send back the values on your behalf.
Default Value: FND_API.G_FALSE
4. p_commit
Requests that the API update information for you after it completes its function.
Default Value: FND_API.G_FALSE
5. x_return_status
Requests that the API return the status of the data for you after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
6. x_msg_count
Indicates number of error messages API has encountered.
7. x_msg_data
Displays error message text. If the x_msg_count is equal to 1, then this contains the actual message.
8. p_sourcing_rule_rec
The sourcing rule or bill of distribution record referenced by the API.
Default Value: G_MISS_SOURCING_RULE_REC
9. p_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
Default Value: G_MISS_SOURCING_RULE_VAL_REC
10. p_receiving_org_tbl
The receiving organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_RECEIVING_ORG_TBL
11. p_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule.
Default Value: G_MISS_RECEIVING_ORG_VAL_TBL
12. p_shipping_org_tbl
The shipping organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_SHIPPING_ORG_TBL
13. p_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule.
Default Value: G_MISS_SHIPPING_ORG_VAL_TBL
14. x_sourcing_rule_rec
Result of the API data after it completes its function for the sourcing rule/bill of distribution record.
15. x_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
16. x_receiving_org_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule/bill of distribution record.
17. x_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the rule.
18. x_shipping_org_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule/bill of distribution record.
19. x_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the rule.
Record Parameter Descriptions
SOURCING_RULE_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SOURCING_RULE_REC_TYPE record with some additional information.
1. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. description
Text describing the sourcing rule record type.
Default Value: FND_API.G_MISS_CHAR
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. organization_id
The identification number for the organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
11. planning_active
Rule is active when the sum of the allocation percentages equals 100.
Default Value: FND_API.G_MISS_NUM
12. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
14. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_name
Valid name of rule defined in the MRP_SOURCING _RULES table.
Default Value: FND_API.G_MISS_CHAR
17. sourcing_rule_type
Valid types must be one of the following values:
■ (1) Sourcing Rule
■ (2) Bill of Distribution
Default Value: FND_API.G_MISS_NUM
18. status
If any validation fails, the API will return error status to the calling module. The Sourcing Rule API processes the rows and reports the following values for every record. If the sourcing rule does not already exist in the system - the Status attribute is set to 1. Processing status of the sourcing rule, valid values are:
■ (1)
■ (2)
Default Value: FND_API.G_MISS_NUM
19. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
20. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
21. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
RECEIVING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the RECEIVING_ORG_REC_TYPE
record with additional information:
1. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. disable_date
Date the receipt organization is no longer effective.
Default Value: FND_API.G_MISS_DATE
7. effective_date
Beginning date the receipt organization becomes effective.
Default Value: FND_API.G_MISS_DATE
8. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
9. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
10. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
11. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
14. receipt_organization_id
Identifier of the organization that serves as the destination for the sourcing rule or bill of distribution.
Default Value: FND_API.G_MISS_NUM
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
17. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
18. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
19. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
SHIPPING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SHIPPING_ORG_REC_TYPE record with additional informatioin.
1. sr_source_id
Primary key in the sourcing rule or bill of distribution table.
Default Value: FND_API.G_MISS_NUM
2. allocation_percent
Percentage allocated to each source organization/supplier site destination.
Default Value: FND_API.G_MISS_NUM
3. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
4. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
5. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
6. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
11. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
13. rank
Rank of the sources, valid values are non-zero integers.
Default Value: FND_API.G_MISS_NUM
14. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
15. secondary_inventory
Currently not used.
16. ship_method
Method used when transporting material between source and destination.
Default Value: FND_API.G_MISS_CHAR
17. source_organization_id
Identifier of the source organization.
Default Value: FND_API.G_MISS_NUM
18. source_type
Indicator of the type of source. Valid values are:
■ Make
■ Transfer
■ Buy
Default Value: FND_API.G_MISS_NUM
19. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
20. vendor_id
Identifier of the vendor suppling the materials.
Default Value: FND_API.G_MISS_NUM
21. vendor_site_id
Identifier where the vendor’s materials are located.
Default Value: FND_API.G_MISS_NUM
22. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
23. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
24. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
25. receiving_org_index
Foreign key to the receipt organization PL/SQL table.
Default Value: FND_API.G_MISS_NUM
Single Record insertion script for MRP Sourcing Rules
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_err_count NUMBER := 0;
l_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
l_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
l_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
l_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
l_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
l_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
o_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
o_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
o_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
o_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
o_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
o_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
BEGIN
fnd_message.clear;
l_sourcing_rule_rec := MRP_SOURCING_RULE_PUB.G_MISS_SOURCING_RULE_REC;
l_sourcing_rule_rec.sourcing_rule_name := '1234'; --SR Name
l_sourcing_rule_rec.Organization_Id:=207;
l_sourcing_rule_rec.planning_active := 1; -- Active?
l_sourcing_rule_rec.status := 1; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := 1; -- 1:Sourcing Rule
l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.OPERATION=>'CREATE';
l_receiving_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_RECEIVING_ORG_TBL;
l_shipping_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_SHIPPING_ORG_TBL;
--l_receiving_org_tbl(1).Sr_Receipt_Id:=207;
l_receiving_org_tbl(1).effective_date := trunc(sysdate)+1;
l_receiving_org_tbl(1).disable_date := trunc(sysdate)+7;
l_receiving_org_tbl(1).receipt_organization_id:=207;
l_receiving_org_tbl(1).operation := 'CREATE'; -- Create or Update
--l_shipping_org_tbl(1).Sr_Source_Id:=228;
l_shipping_org_tbl(1).rank := 1;
l_shipping_org_tbl(1).allocation_percent := 100; -- Allocation 100
l_shipping_org_tbl(1).source_type := 3; -- BUY FROM
l_shipping_org_tbl(1).vendor_id := 21;
l_shipping_org_tbl(1).receiving_org_index := 1;
l_shipping_org_tbl(1).operation := 'CREATE';
dbms_output.put_line('before call');
dbms_output.put_line('Operation before call
'||l_sourcing_rule_rec.operation);
MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE(
p_api_version_number =>1.0
,p_init_msg_list =>fnd_api.g_true
,p_commit => fnd_api.g_true
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
,p_sourcing_rule_rec => l_sourcing_rule_rec
,p_sourcing_rule_val_rec => l_sourcing_rule_val_rec
,p_receiving_org_tbl => l_receiving_org_tbl
,p_receiving_org_val_tbl => l_receiving_org_val_tbl
,p_shipping_org_tbl => l_shipping_org_tbl
,p_shipping_org_val_tbl => l_shipping_org_val_tbl
,x_sourcing_rule_rec => o_sourcing_rule_rec
,x_sourcing_rule_val_rec => o_sourcing_rule_val_rec
,x_receiving_org_tbl => o_receiving_org_tbl
,x_receiving_org_val_tbl => o_receiving_org_val_tbl
,x_shipping_org_tbl => o_shipping_org_tbl
,x_shipping_org_val_tbl => o_shipping_org_val_tbl
);
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Success!');
else
dbms_output.put_line('count:'||l_msg_count);
IF l_msg_count > 0 THEN
FOR l_index IN 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(
p_msg_index => l_index,
p_encoded => FND_API.G_FALSE);
dbms_output.put_line(substr(l_msg_data,1,250));
END LOOP;
dbms_output.put_line('MSG:'||o_sourcing_rule_rec.return_status);
END IF;
dbms_output.put_line('Failure!');
end if;
END;
Single Record insertion script for MRP Sourcing Rule Assignments
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_org_cnt NUMBER;
l_vendor_cnt NUMBER;
l_org_class VARCHAR2 (3);
l_org_num NUMBER;
l_line_num NUMBER := 0;
l_err_count NUMBER := 0;
p NUMBER;
l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
BEGIN
fnd_message.CLEAR;
-- l_assignment_set_rec.assignment_set_id := 3530;
-- l_assignment_set_rec.assignment_set_name := 'Test_src1_set';
-- l_assignment_set_rec.operation := 'CREATE';
l_assignment_tbl (1).assignment_set_id := 1;
l_assignment_tbl (1).assignment_type := 6;
l_assignment_tbl (1).operation := 'CREATE';
l_assignment_tbl (1).organization_id := 107;
l_assignment_tbl (1).inventory_item_id := 37002;
l_assignment_tbl (1).sourcing_rule_id := 1014;
l_assignment_tbl (1).sourcing_rule_type := 1;
mrp_src_assignment_pub.process_assignment
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_assignment_set_rec => l_assignment_set_rec,
p_assignment_set_val_rec => l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl => l_assignment_val_tbl,
x_assignment_set_rec => o_assignment_set_rec,
x_assignment_set_val_rec => o_assignment_set_val_rec,
x_assignment_tbl => o_assignment_tbl,
x_assignment_val_tbl => o_assignment_val_tbl
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
ELSE
DBMS_OUTPUT.put_line ('count:' || l_msg_count);
IF l_msg_count > 0
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data, 1, 250));
END LOOP;
DBMS_OUTPUT.put_line ('MSG:' || o_assignment_set_rec.return_status);
END IF;
DBMS_OUTPUT.put_line ('Failure!');
END IF;
END;
/
Subscribe to:
Posts (Atom)