How to Create Code Combination ID by API

I was on an integration assignment between Home Grown Project System and Oracle Payable. A big gap which I faced there was how to maintain single source of Account Code Combinations. We had following options to manage this issue.
Built a View of GL_CODE_COMBINATIONS table and pass Combination and ID from Oracle to Custom System
Use API and create Code Combination
I used second way to handle this. Here is script which i used in that Assignment ( it works for me but you need to be careful as it’s not documented any where 🙂
—–Create Function
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID

L_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/

—–EXECUTE FUNCTION
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ‘10.2001.2211101.987872.001.0000’ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;

No approver found for Purchase Requisition XX

I was working on one client site for AME setup with Purchase Requisition. I finished setup for AME transaction Type “Purchase Requisition Approval”. When requester was submitting requisition system initially shows that it’s in process but after one minute requester was getting notification “No approver found for Purchase Requisition”.
I checked from AME Test Workbench functionality by using transaction ID system was generating approval list as per setup. In other words everything was setup properly but still after submission user was getting error while submitting from Core Purchasing Form.

If user was completing this purchase requisition by using iProcurement Checkout functionality request was going for approval to supervisor as per setup.

After 2 days of working, found out one silly problem .guess what????????

Download Solution

How to Create Company Cost Center By Using API

To implement Oracle Daily Business Intelligence it’s required to implement Company Cost Center at Organization definition level. Oracle Provided a API for this, in this document I will share the sample script to update company cost center by executing this script.

Link To Download File

How to Create Position Hierarchy by Using APIs

How to Create Position Hierarchy by Using APIs

Define Valid Grades for Position in Oracle HRMS

Define Valid Grades for Positions
Oracle HRMS lets you define Valid Grades for Positions. These definitions will be used to provide warning messages in the Assignment window when you enter Position and Grade information.
Navigate to Position Definitation Form
Query the position and click on the Valid Grades button
Enter and save the valid grades for each position. You can enter a single grade, or a set of grades.

Define Valid Grades for Positions By Using API
/* Formatted on 2010/01/17 08:29 (Formatter Plus v4.8.8) */
create table grade_raw_data(slno number,
position_name varchar2(1000),
grade varchar2(1000))

Upload Data into grade_raw_data table either by using SQLLDR or Some other tool

ALTER TABLE APPS_APPLMGR.GRADE_RAW_DATA
ADD (Status VARCHAR2(3 BYTE),
error_message varchar2(1000)
);

/* Formatted on 2010/01/17 08:40 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE xx_position_valid_grade
IS
CURSOR cur
IS
SELECT g.slno, g.position_name, g.grade, g.status, g.error_message
FROM grade_raw_data g
WHERE status = ‘E’ or Status is Null;

x_rowid VARCHAR2 (200);
x_valid_grade_id NUMBER;
x_business_group_id NUMBER;
x_grade_id NUMBER;
x_date_from DATE;
x_comments VARCHAR2 (200);
x_date_to DATE;
x_job_id NUMBER;
x_position_id NUMBER;
x_attribute_category VARCHAR2 (200);
x_attribute1 VARCHAR2 (200);
v_msg VARCHAR2 (1000) := NULL;
x_attribute2 VARCHAR2 (200);
x_attribute3 VARCHAR2 (200);
x_attribute4 VARCHAR2 (200);
x_attribute5 VARCHAR2 (200);
x_attribute6 VARCHAR2 (200);
x_attribute7 VARCHAR2 (200);
x_attribute8 VARCHAR2 (200);
x_attribute9 VARCHAR2 (200);
x_attribute10 VARCHAR2 (200);
x_attribute11 VARCHAR2 (200);
x_attribute12 VARCHAR2 (200);
x_attribute13 VARCHAR2 (200);
x_attribute14 VARCHAR2 (200);
x_attribute15 VARCHAR2 (200);
x_attribute16 VARCHAR2 (200);
x_attribute17 VARCHAR2 (200);
x_attribute18 VARCHAR2 (200);
x_attribute19 VARCHAR2 (200);
x_attribute20 VARCHAR2 (200);
x_end_of_time DATE;
x_pst1_date_end DATE;
x_pst1_date_effective DATE := TO_DATE (’03-01-2010′, ‘DD-MM-YYYY’);
BEGIN
FOR pos IN cur
LOOP
BEGIN
x_rowid := NULL;
x_valid_grade_id := dbms_random.random;
x_business_group_id := 0;

SELECT grade_id
INTO x_grade_id
FROM per_grades
WHERE UPPER (NAME) = UPPER (pos.grade);

x_date_from := ’03-jan-2010′;

SELECT position_id
INTO x_position_id
FROM hr_all_positions_f
WHERE UPPER (NAME) = UPPER (pos.position_name);
per_valid_grades_pkg2.insert_row (x_rowid,
x_valid_grade_id,
x_business_group_id,
x_grade_id,
x_date_from,
x_comments,
x_date_to,
x_job_id,
x_position_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_end_of_time,
x_pst1_date_end,
x_pst1_date_effective
);

UPDATE grade_raw_data p
SET p.status = ‘I’
WHERE slno = pos.slno;
x_valid_grade_id := NULL;
X_GRADE_ID := null;
x_position_id := null;
EXCEPTION
WHEN OTHERS
THEN
v_msg := SQLERRM;
UPDATE grade_raw_data p
SET p.status = ‘E’,
p.error_message = v_msg
WHERE slno = pos.slno;
COMMIT;
END;
END LOOP;
END;
/

Next Post will be about Position Hierarchy Upload by Using API .

Upload Lookup Values by Using Script

Upload Lookup Values By Using Script

Extract Employee and Supervisor Based on Position Hierarchy

This SQL will give you employee and Supervisor as per position hierarchy .

/* Formatted on 2009/10/19 15:01 (Formatter Plus v4.8.8) */
SELECT LPAD (‘ ‘, 5 * LEVEL) || has.NAME hierarchy, LEVEL,
hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = 81
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
start with pse.parent_position_id = –40979 Base Position Id (like CEO etc)
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 4