HRMS Benefits query to fetch the dependent of the beneficiaries

SELECT DISTINCT emp_papf.full_name employee_full_name,
emp_papf.national_identifier employee_ssn,
ppf.full_name dependent_name,
ppf.national_identifier dependent_ssn
FROM ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp,
per_contact_relationships pcr,
per_all_people_f ppf,
per_all_people_f emp_papf,
ben_per_in_ler pil
WHERE epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pdp.dpnt_person_id = pcr.contact_person_id
AND pcr.contact_person_id = ppf.person_id
AND pcr.person_id = emp_papf.person_id
AND pcr.personal_flag = 'Y'
AND epe.prtt_enrt_rslt_id IS NOT NULL
AND epe.per_in_ler_id = pil.per_in_ler_id
AND pcr.person_id = pil.person_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN pdp.effective_start_date
AND pdp.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd')
BETWEEN NVL (pcr.date_start,
TO_DATE (sysdate, 'rrrr/mm/dd'))
AND NVL (pcr.date_end, TO_DATE (sysdate, 'rrrr/mm/dd'))
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd')
BETWEEN emp_papf.effective_start_date
AND emp_papf.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND pdp.cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pdp.per_in_ler_id = pil.per_in_ler_id

HRMS Benefits query to fetch the beneficiaries

SELECT DISTINCT emp_papf.full_name employee_name,
emp_papf.national_identifier employee_ssn,
ppf.full_name beneficiary_name,
ppf.national_identifier beneficiary_ssn
FROM per_all_people_f ppf,
per_contact_relationships pcr,
ben_prtt_enrt_rslt_f pen,
ben_pl_bnf_f pbn,
per_all_people_f emp_papf
WHERE 1 = 1
AND pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pcr.personal_flag(+) = 'Y'
AND pbn.bnf_person_id = pcr.contact_person_id(+)
AND pbn.bnf_person_id = ppf.person_id(+)
AND emp_papf.person_id = pcr.person_id
AND TO_DATE (SYSDATE, 'rrrr/mm/dd') BETWEEN pbn.effective_start_date
AND pbn.effective_end_date
AND TO_DATE (SYSDATE, 'rrrr/mm/dd')
BETWEEN NVL (ppf.effective_start_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND NVL (ppf.effective_end_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND TO_DATE (SYSDATE, 'rrrr/mm/dd')
BETWEEN NVL (emp_papf.effective_start_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND NVL (emp_papf.effective_end_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND TO_DATE (SYSDATE, 'rrrr/mm/dd') BETWEEN pen.effective_start_date
AND pen.effective_end_date

FND_NEW_MESSAGES

The FND_NEW_MESSAGES table in the database stores all Oracle Applications messages for all languages. The below API is used to load new error messages in FND_NEW_MESSAGES table.

fnd_new_messages_pkg.load_row

Ex:

DECLARE
BEGIN
fnd_new_messages_pkg.load_row
(x_application_id => 800, -- Application id
x_message_name => 'INVALID_VALUES', -- error message name
x_message_number => NULL,
x_message_text => 'Cannot update values in CANCEL mode.', -- Message
x_description => 'Cannot update values in CANCEL mode.',
x_type => 'ERROR', -- message type
x_max_length => NULL,
x_category => NULL,
x_severity => NULL,
x_fnd_log_severity => NULL,
x_owner => NULL,
x_custom_mode => NULL,
x_last_update_date => SYSDATE
);

COMMIT;
END;
/

User Profiles

If you are looking for information on User profiles then i think you should navigate to the below link. This link provides very good information on user profiles.

User Profiles (Click Here)

API to Purge an person from Oracle HRMS

DECLARE
l_person_org_manager_warning VARCHAR2 (200);
BEGIN
hr_person_api.delete_person
(p_validate => FALSE,
p_effective_date => SYSDATE,
p_person_id => :person_id,
p_perform_predel_validation => FALSE,
p_person_org_manager_warning => l_person_org_manager_warning
);
COMMIT;
END;

Before purging the person from Oracle HRMS we need to make sure that the employee and fnd_user link is been deleted and also the person should not have an active payroll.

If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from 'Employee' to 'Applicant' and then use the above API again to purge the record.

How to upload customized Oracle Benefits Confirmation Page (BENSSCNF)

1. Navigate to the XML Publisher Administrator Responsibility > Templates.
2. Use the search engine to find the 'BENSSCNF' template. The seeded report should now display.
3. Click the "Duplicate" icon next to the seeded report.
4. Change the required Code field from Copy of BENSSCNF to BENSSCNF2. Please note that these codes are non-updateable, so we need to make sure to rename them while duplicating the template itself.
5. Change the required Name field to 'xx Custom Benefit Enrollment Confirmation' and click the Apply button.
6. Click the Add File button and upload the new or customized replacement file.
7. Go back to Templates and find the seeded report we have duplicated.
8. Click the link for the seeded template and click the Update button.
9. End date the seeded template.
10. Generate the report in the application. The generated report will reflect the updated RTF.

For any reason if we want to revert back to the Original template then we need to follow the below mentioned steps:

1. Go to XML Publisher Administrator Responsibility > Templates.
2. Use the search engine to find the template you wish to copy.
3. Click on the link for the copy previously created.
4. Click the Update button and enter an end date into the End Date field. (Use the creation date to completely remove the incorrect copy.)
5. Repeat this for every incorrect copy that may exist.

Setup to enable Effective Date Tracking popup in Oracle HRMS

This setup will turn on the switch that allows the effective date tracking in HRMS to pop up when someone navigates into Oracle employee record so that users do not forget to date track.

Navigation: Application Developer -> Profile -> System
Uncheck Site and check the Application
Application Field: Human Resources
Profile field : DateTrack:Reminder



Press the find button and then a screen will popup



In the Application field where we have the white background we need to give the value as ‘Always pop warning window’ and then save the form.

How to change the Address Style from US to Generic Addess Style in Oracle HRMS

Select any HRMS or Super HRMS Manager Responsibility (Example: US Super HRMS Manager)

1. Open the 'Define Taskflow' form. ( Security -> Task Flow Definitions )
2. Query the Taskflow that is used by the Person form function (For Example: 'US HR PERSON TASKFLOW').
3. Go to 'Node section – Name field' and delete the 'USADDRESS' node (This node is used by the US Address Style) and then add a new node 'ADDRESS' (This node is used by the international Address Style) with the same sequence number as the 'USADDRESS' node.

Save and Exit the “Define Taskflow” form.

Logout the current applications and re-login to check that the new Address form with international address style is launched instead of old US Address Style form when “Address” button is selected in People Window.

Single record insert script for Engineering Change Orders

DECLARE
p_eco_rec eng_eco_pub.eco_rec_type;
p_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type;
p_revised_item_tbl eng_eco_pub.revised_item_tbl_type;
p_rev_component_tbl bom_bo_pub.rev_component_tbl_type;
p_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type;
p_sub_component_tbl bom_bo_pub.sub_component_tbl_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_message_list error_handler.error_tbl_type;
x_eco_rec eng_eco_pub.eco_rec_type;
x_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type;
x_revised_item_tbl eng_eco_pub.revised_item_tbl_type;
x_rev_component_tbl bom_bo_pub.rev_component_tbl_type;
x_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type;
x_sub_component_tbl bom_bo_pub.sub_component_tbl_type;
x_rev_operation_tbl bom_rtg_pub.rev_operation_tbl_type;
x_rev_op_resource_tbl bom_rtg_pub.rev_op_resource_tbl_type;
x_rev_sub_resource_tbl bom_rtg_pub.rev_sub_resource_tbl_type;
x_change_line_tbl eng_eco_pub.change_line_tbl_type;
l_change_line_tbl eng_eco_pub.change_line_tbl_type;
error_msg error_handler.error_tbl_type;
BEGIN
p_eco_rec.eco_name := 'ECO009';
p_eco_rec.organization_code := 'V1';
p_eco_rec.change_name := 'A Test ECO History';
p_eco_rec.description := 'A Test ECO History';
p_eco_rec.status_name := 'Scheduled';
p_eco_rec.approval_status_name := 'Approved';
p_eco_rec.approval_date := SYSDATE;
p_eco_rec.approval_request_date := SYSDATE;
p_eco_rec.change_type_code := 'ECO';
p_eco_rec.transaction_type := 'CREATE';
p_eco_rec.plm_or_erp_change := 'ERP';

-----ECO REVISIONS
IF p_eco_revision_tbl.EXISTS (1) IS NOT NULL
THEN
p_eco_revision_tbl.DELETE;
END IF;

p_eco_revision_tbl (1).eco_name := 'ECO009';
p_eco_revision_tbl (1).organization_code := 'V1';
p_eco_revision_tbl (1).revision := 'A';
p_eco_revision_tbl (1).transaction_type := 'CREATE';

-----REVISED ITEMS
IF p_revised_item_tbl.EXISTS (1) IS NOT NULL
THEN
p_revised_item_tbl.DELETE;
END IF;

p_revised_item_tbl (1).eco_name := 'ECO009';
p_revised_item_tbl (1).organization_code := 'V1';
p_revised_item_tbl (1).revised_item_name := 'ASO0012';
p_revised_item_tbl (1).start_effective_date := SYSDATE;
p_revised_item_tbl (1).new_effective_date := SYSDATE + 2;
p_revised_item_tbl (1).transaction_type := 'CREATE';

/* ----history
l_change_line_tbl (1).eco_name := 'ECO002';
l_change_line_tbl (1).scheduled_date := SYSDATE;
l_change_line_tbl (1).assignee_name := '60805 Open';
l_change_line_tbl (1).transaction_type := 'CREATE';
p_revised_item_tbl (1).reschedule_comments := 'Testing revised items';
*/ ---COMPONENT------------
IF p_rev_component_tbl.EXISTS (1) IS NOT NULL
THEN
p_rev_component_tbl.DELETE;
END IF;

p_rev_component_tbl (1).eco_name := 'ECO009';
p_rev_component_tbl (1).organization_code := 'V1';
p_rev_component_tbl (1).revised_item_name := 'ASO0012';
p_rev_component_tbl (1).start_effective_date := SYSDATE;
p_rev_component_tbl (1).operation_sequence_number := 1;
p_rev_component_tbl (1).component_item_name := '060 1918';
p_rev_component_tbl (1).acd_type := 1;
p_rev_component_tbl (1).old_operation_sequence_number := 1;
p_rev_component_tbl (1).new_operation_sequence_number := 1;
p_rev_component_tbl (1).transaction_type := 'CREATE';

-----REFERENCE DESIGNATOR-------------
IF p_ref_designator_tbl.EXISTS (1) IS NOT NULL
THEN
p_ref_designator_tbl.DELETE;
END IF;

p_ref_designator_tbl (1).eco_name := 'ECO009';
p_ref_designator_tbl (1).organization_code := 'V1';
p_ref_designator_tbl (1).revised_item_name := 'ASO0012';
p_ref_designator_tbl (1).start_effective_date := SYSDATE;
p_ref_designator_tbl (1).operation_sequence_number := 1;
p_ref_designator_tbl (1).component_item_name := '060 1918';
p_ref_designator_tbl (1).reference_designator_name := 'ECO009_dsg';
p_ref_designator_tbl (1).acd_type := 1;
p_ref_designator_tbl (1).transaction_type := 'CREATE';
----- SUBSTITUTE COMPONENT ----------------
IF p_sub_component_tbl.EXISTS (1) IS NOT NULL
THEN
p_sub_component_tbl.DELETE;
END IF;

p_sub_component_tbl (1).eco_name := 'ECO009';
p_sub_component_tbl (1).organization_code := 'V1';
p_sub_component_tbl (1).revised_item_name := 'ASO0012';
p_sub_component_tbl (1).start_effective_date := SYSDATE;
p_sub_component_tbl (1).operation_sequence_number := 1;
p_sub_component_tbl (1).component_item_name := '060 1918';
p_sub_component_tbl (1).substitute_component_name := '10-016-0063';
p_sub_component_tbl (1).new_substitute_component_name := '10-016-0063';
p_sub_component_tbl (1).acd_type := 1;
p_sub_component_tbl (1).transaction_type := 'CREATE';
p_sub_component_tbl (1).row_identifier := NULL;
--fnd_client_info.set_org_context (204);

fnd_global.apps_initialize (1007941, 20567, 703);
DBMS_OUTPUT.put_line ('Before API');


eng_eco_pub.process_eco (p_api_version_number => 1.0,
p_init_msg_list => FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
p_bo_identifier => 'ECO',
p_eco_rec => p_eco_rec,
p_eco_revision_tbl => p_eco_revision_tbl,
p_change_line_tbl => l_change_line_tbl,
p_revised_item_tbl => p_revised_item_tbl,
p_rev_component_tbl => p_rev_component_tbl,
p_ref_designator_tbl => p_ref_designator_tbl,
p_sub_component_tbl => p_sub_component_tbl,
p_rev_operation_tbl => x_rev_operation_tbl,
p_rev_op_resource_tbl => x_rev_op_resource_tbl,
p_rev_sub_resource_tbl => x_rev_sub_resource_tbl,
x_eco_rec => x_eco_rec,
x_eco_revision_tbl => x_eco_revision_tbl,
x_change_line_tbl => x_change_line_tbl,
x_revised_item_tbl => x_revised_item_tbl,
x_rev_component_tbl => x_rev_component_tbl,
x_ref_designator_tbl => x_ref_designator_tbl,
x_sub_component_tbl => x_sub_component_tbl,
x_rev_operation_tbl => x_rev_operation_tbl,
x_rev_op_resource_tbl => x_rev_op_resource_tbl,
x_rev_sub_resource_tbl => x_rev_sub_resource_tbl,
p_debug => 'N',
p_output_dir => NULL,
p_debug_filename => 'ECO_BO_Debug.log'
);
DBMS_OUTPUT.put_line ('Return Status ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
error_handler.get_message_list (x_message_list);

FOR i IN 1 .. x_msg_count
LOOP
error_msg (i).MESSAGE_TEXT := x_message_list (i).MESSAGE_TEXT;
DBMS_OUTPUT.put_line (error_msg (i).MESSAGE_TEXT);
END LOOP;
END IF;

COMMIT;
END;
/

Base Tables and views involved:
ENG_ENGINEERING_CHANGES, ENG_CHANGE_ORDER_REVISIONS, ENG_REVISED_ITEMS, BOM_REFERENCE_DESIGNATORS, BOM_BILL_OF_MATERIALS, BOM_INVENTORY_COMPONENTS, BOM_SUBSTITUTE_COMPONENTS

Single Record script to load Customer Cross Reference

INSERT INTO mtl_ci_interface
(customer_id,
customer_item_number,
commodity_code,
item_definition_level, process_flag, process_mode,
inactive_flag, transaction_type, lock_flag,
last_updated_by, created_by, last_update_date,
creation_date
)
VALUES (1290,
'Test_customer_item2',
'Finished Good',
1, 1, 1,
'2', 'CREATE', 'N',
1007941, 1007941, SYSDATE,
SYSDATE
);

commit;

---After inserting customer items into the interface table
---run the Import program Import Customer Items
---Navigation to run the program: Inventory--> Reports --> All.
---Parameters: Abort On Error=N, Delete Record =Y
---After running import program verify the base table MTL_CUSTOMER_ITEMS

INSERT INTO mtl_ci_xrefs_interface
(customer_id,
customer_item_number,
preference_number,
inventory_item,
master_organization_id,
item_definition_level, process_flag, process_mode,
inactive_flag, transaction_type, lock_flag,
last_updated_by, created_by, last_update_date,
creation_date
)
VALUES (1290,
'Test_customer_item2',
1,
'MC31324',
204,
1, 1, 1,
'2', 'CREATE', 'N',
1007941,1007941, SYSDATE,
SYSDATE
);

commit;

---After inserting customer items cross references into the interface table
---run the Import program, Import Customer Item Cross References
---Navigation to run the program: Inventory--> Reports --> All.
---Parameters: Abort On Error=N, Delete Record =Y
---After running import program verify the base table MTL_CUSTOMER_ITEM_XREFS

Single insert script for PO Quotations

INSERT INTO po_headers_interface
(interface_header_id, batch_id, action, document_type_code,
document_subtype, vendor_id, vendor_site_id, org_id,
currency_code, rate, rate_type_code, agent_id,
bill_to_location_id, approval_status, approved_date, created_by,
creation_date, last_updated_by, last_update_date, comments,
attribute11, quote_warning_delay
)
VALUES (po_headers_interface_s.NEXTVAL, 5, 'ORIGINAL', 'QUOTATION',
'STANDARD', 8943, 4754, 204,
'USD', NULL, NULL, 2159,
204, 'APPROVED', SYSDATE, 1007941,
SYSDATE, 1007941, SYSDATE, 'test_po_for_status',
NULL, 10
);

COMMIT ;

INSERT INTO po_lines_interface
(interface_line_id, interface_header_id,
action, line_type, line_num, item_id, item,
item_description, uom_code,
quantity, unit_price, created_by, creation_date,
last_updated_by, last_update_date, line_attribute11
)
VALUES (po_lines_interface_s.NEXTVAL, po_headers_interface_s.CURRVAL,
'ORIGINAL', 'Goods', 1, 8063, '10-40W Oil',
'10-40W Car / Truck Motor Oil', 'QT',
2, 0.89, 1007941, SYSDATE,
1007941, SYSDATE, NULL
);

COMMIT ;

After inserting into interface tables, run the import program

Import Price Catalogs.
Navigation: Purchasing: Requests

Parameters:
1. Document Type = QUOTATION
2. Create or Update Items= NO
3. Create Sourcing Rules= NO
4. Approval Status= Approved
5. Batch Id=5

Base Tables
PO_HEADERS_ALL, PO_LINES_ALL

Mfg Cross References Single Record Script

DECLARE
l_rowid VARCHAR2 (30);
BEGIN
fnd_global.apps_initialize (1007941, 20567, 703);
mtl_mfg_part_numbers_pkg.insert_row
(l_rowid,
69, --mfg_xref_load_rec.manufacturer_id,
'Test Part', --mfg_xref_load_rec.ls_mfg_part_number,
173, --mfg_xref_load_rec.inventory_item_id,
SYSDATE,
1007941, --l_user_id,
SYSDATE,
1007941, --l_user_id,
1007941, --l_user_id,
204, --mfg_xref_load_rec.organization_id,
'',
'',
NULL, --mfg_xref_load_rec.ls_authorization_status,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
COMMIT;
END;

Base table involved: MTL_MFG_PART_NUMBERS

PO Blanket Releases Single Insert script

INSERT INTO po_requisitions_interface_all
(quantity, unit_price, authorization_status, unit_of_measure,
destination_organization_id, last_updated_by, last_update_date,
requisition_header_id, source_type_code, requisition_line_id,
need_by_date, org_id, amount, uom_code, document_type_code,
interface_source_code, destination_type_code, charge_account_id,
preparer_id, category_id, deliver_to_requestor_id,
deliver_to_location_id, item_id, batch_id, line_type_id,
currency_code, creation_date, requisition_type,
suggested_buyer_id, ---suggested_vendor_id,
autosource_doc_header_id,
autosource_doc_line_num, req_number_segment1,
req_dist_sequence_id
)
VALUES (10, 20, 'APPROVED', 'Each',
207, 1111, SYSDATE,
123492, 'VENDOR', 101198,
SYSDATE + 1, 204, NULL, 'Ea', 'RELEASE',
'test', 'EXPENSE', 13401,
15516, 1, 15516,
207, 10928, 100, 1,
'USD', SYSDATE, 'PURCHASE',
15516, 34333,
1, 'Req-106',
1
);

After inserting into interface tables, run the following import program with the parameters


Requisition Import.
Navigation: Purchasing: Requests

Parameters:
1. Import Source = test
(Select given interface_source_code value from LOV )

2. Import Batch ID = 100
(Give batch_id which u gave for the header)

3. Group By = Item+ (Default value)

4. Last Requisition Number = NULL

5. Multiple Distributions = No (Select YES only if u have multiple lines in distribution table)

6. Initiate Approval after ReqImport = YES (Select YES only if u want to auto approve the requisition)

Single Record Script to link 'BOM Resources' to 'BOM Departments'

DECLARE
l_rowid VARCHAR2 (2000);
BEGIN
bom_department_resources_pkg.insert_row (x_rowid => l_rowid,
x_department_id => 21773,
x_resource_id => 8270,
x_last_update_date => SYSDATE,
x_last_updated_by => 1007911,
x_creation_date => SYSDATE,
x_created_by => 1007911,
x_last_update_login => 3156870,
x_share_capacity_flag => 2,
x_share_from_dept_id => NULL,
x_capacity_units => 1,
x_resource_group_name => NULL,
x_available_24_hours_flag => 1,
x_ctp_flag => 2,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_exception_set_name => NULL,
x_atp_rule_id => NULL,
x_utilization => NULL,
x_efficiency => NULL,
x_schedule_to_instance => 2
);
COMMIT;
END;

Single Record Script to load 'BOM Resources'

DECLARE
l_rowid VARCHAR2 (2000);
l_resource_id NUMBER;
BEGIN
bom_resources_pkg.insert_row (x_rowid => l_rowid,
x_resource_id => l_resource_id,
x_resource_code => 'XXTEST123',
x_organization_id => 204,
x_last_update_date => SYSDATE,
x_last_updated_by => 1007911,
x_creation_date => SYSDATE,
x_created_by => 1007911,
x_last_update_login => 3156870,
x_description => NULL,
x_disable_date => NULL,
x_cost_element_id => 3,
x_purchase_item_id => 17817,
x_cost_code_type => 3,
x_functional_currency_flag => 2,
x_unit_of_measure => 'EA',
x_default_activity_id => 4,
x_resource_type => 2,
x_autocharge_type => 1,
x_standard_rate_flag => 2,
x_default_basis_type => 1,
x_absorption_account => 13735,
x_allow_costs_flag => 1,
x_rate_variance_account => NULL,
x_expenditure_type => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_request_id => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_program_update_date => NULL,
x_batchable => 1,
x_min_batch_capacity => NULL,
x_max_batch_capacity => NULL,
x_batch_capacity_uom => NULL,
x_batch_window => NULL,
x_batch_window_uom => NULL,
x_competence_id => NULL,
x_rating_level_id => NULL,
x_qualification_type_id => NULL,
x_billable_item_id => NULL,
x_supply_subinventory => NULL,
x_supply_locator_id => NULL
);
COMMIT;
END;

Single Record Script to load 'BOM Department'

DECLARE
l_rowid VARCHAR2 (2000);
l_department_id NUMBER;
BEGIN
bom_departments_pkg.insert_row (x_rowid => l_rowid,
x_department_id => l_department_id,
x_department_code => 'XXTEST',
x_organization_id => 204,
x_last_update_date => SYSDATE,
x_last_updated_by => 1007911,
x_creation_date => SYSDATE,
x_created_by => 1007911,
x_last_update_login => 3156870,
x_description => 'Test Department',
x_disable_date => NULL,
x_department_class_code => NULL,
x_pa_expenditure_org_id => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_location_id => NULL,
x_scrap_account => NULL,
x_est_absorption_account => NULL,
x_maint_cost_category => NULL
);
DBMS_OUTPUT.put_line (l_department_id);
COMMIT;
END;

Query to find the workflow override address in test instances

SELECT fsc.component_name, fsc.component_status, fsc.startup_mode,
fscpv.parameter_value
FROM fnd_svc_comp_params_tl fscpt,
fnd_svc_comp_param_vals fscpv,
fnd_svc_components fsc
WHERE fscpt.display_name = 'Test Address'
AND fscpt.parameter_id = fscpv.parameter_id
AND fscpv.component_id = fsc.component_id;

Query to fetch Error workflow details for 'HRSSA' workflow

The below query will show you the error workflow for a particular time period for the item type 'HRSSA'. You can also fetch the error for other workflows too, for that instead of 'HRSSA' you need to give the item type for which you want to fetch the data.

SELECT ias.begin_date, ias.item_key, ac.NAME activity,
ias.activity_result_code RESULT, ias.error_name error_name,
ias.error_message error_message
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities ac,
wf_activities ap,
wf_items i
WHERE ias.item_type = 'HRSSA'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'HRSSA'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < NVL (ac.end_date, i.begin_date + 1)
and trunc(i.begin_date) between to_date(:begin_date) and trunc(:end_date)
ORDER BY ias.begin_date DESC

Query to link a Responsibility to a Set of Books/Operating Unit in Oracle

--- Link to Set of Books
SELECT fr.responsibility_name, fpov.profile_option_value set_of_books_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr
WHERE fpo.user_profile_option_name = 'GL Set of Books Name'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id


--- Link to Operating Unit
SELECT fr.responsibility_name, fpov.profile_option_value orgid, NAME org_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr,
hr_operating_units hou
WHERE UPPER (fpo.user_profile_option_name) LIKE UPPER ('MO%OPERATIN%')
AND profile_option_name = 'ORG_ID'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id
AND hou.organization_id = fpov.profile_option_value

Query that shows all the repsonsibilities and what functions are attached to these responsibilities.

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;

How to close/respond to a FYI notification from PL/SQL

Closing/Responding to a FYI notification is perty straight forward. You need to just run the following API:

begin
wf_notification.CLOSE (nid => --Notification ID--,
responder => --Responder Name--
);
commit;
end;

Note: The Responder Name should be the same as the 'User Name' which the user uses to login the Oracle applications.

How to preserve leading zeros in a CSV file using Oracle

Some time back i was working on alerts which will extract the data from Oracle and send alert extracts to the users via email as attachments in 'CSV' format. But when i received the notifications i noticed that the leading zeros in the segment values of the code combinations were truncated.

After some research i found that when ever the Excel opens up a CSV file it defaults all cells to "General" formatting and removes the leading zeros on cell values thus converting them from strings to numbers.

The simple solution is to SELECT the value such that Excel will see it as a string and not a number. So we prefixed an equals sign and double quote on to the value and post fix a double quote. So

SELECT xx_column
FROM xx_table

becomes

SELECT '="'||xx_column||'"'
FROM xx_table

PL/Sql Script to assign responsibilities to Oracle Users

DECLARE
l_user_id NUMBER;

CURSOR cur_rec
IS
SELECT *
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER ('XXXXX');
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'XXXX';

FOR rec_cur IN cur_rec
LOOP
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => rec_cur.responsibility_id,
responsibility_application_id => rec_cur.application_id,
security_group_id => 0,
start_date => SYSDATE - 1,
end_date => NULL,
description => NULL
);
COMMIT;
END LOOP;
END;

Oracle Expense Report (Approval Limits)

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

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

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

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;

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".

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

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'

Expense report number postfixed with "-1"

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

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

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

Workflow Issue

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

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

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

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

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

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

COMMIT;
END LOOP;
END;

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

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

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

COMMIT;
END LOOP;
END;

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

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

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

COMMIT;
END LOOP;
END;

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

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

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

COMMIT;
END LOOP;
END;

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

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

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

COMMIT;
END LOOP;
END;

Check out Jaago re! - One Billion Votes

Hi,

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

Understanding and Using API User Hooks

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

Retrieve the correct person type in HRMS

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

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

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

Simple Script to load the HR Locations using API

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

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

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.

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.