We do create code combination using GL setup. We can use standard API to create code combination from backend .
We have oracle standard API fnd_flex_ext.get_ccid, which we can be use to get CCID from backend by passing the segments or concatenated segments. If there is a combination then this will return the value of that CCID. If there is no combination avialable then it will create CCID and return that value. If there is any error then it will return a value less than or equal to zero.
Note: Dynamic insert should be allowed for accounting flexfield, in order to create code combination from backend.
We can use the following Package to get it.
LOOP
/*We can simply use API as well, if we are inside a package. we can use such kind of stand alone package, if we want to call it from outside.*/
We have oracle standard API fnd_flex_ext.get_ccid, which we can be use to get CCID from backend by passing the segments or concatenated segments. If there is a combination then this will return the value of that CCID. If there is no combination avialable then it will create CCID and return that value. If there is any error then it will return a value less than or equal to zero.
Note: Dynamic insert should be allowed for accounting flexfield, in order to create code combination from backend.
We can use the following Package to get it.
CREATE OR REPLACE PACKAGE BODY APPS.xxran_ccid_pkg
AS
-- create CCID by passing segments
FUNCTION create_a_combination (
p_segment1 IN VARCHAR2,
p_segment2 IN VARCHAR2,
p_segment3 IN VARCHAR2,
p_segment4 IN VARCHAR2,
p_segment5 IN VARCHAR2,
p_segment6 IN VARCHAR2,
p_segment7 IN VARCHAR2,
p_segment8 IN VARCHAR2,
p_segment9 IN VARCHAR2,
p_flex_delimiter IN VARCHAR2,
p_chart_of_accounts_id IN NUMBER,
p_ccid OUT NUMBER
)
RETURN VARCHAR2
IS
ccid NUMBER := 0;
allsegments VARCHAR2 (256) := NULL;
flexerror VARCHAR2 (2560) := NULL;
BEGIN
--p_error_loc := 'Create CCID';
allsegments :=
p_segment1
|| p_flex_delimiter
|| p_segment2
|| p_flex_delimiter
|| p_segment3
|| p_flex_delimiter
|| p_segment4
|| p_flex_delimiter
|| p_segment5
|| p_flex_delimiter
|| p_segment6
|| p_flex_delimiter
|| p_segment7
|| p_flex_delimiter
|| p_segment8
|| p_flex_delimiter
|| p_segment9;
DBMS_OUTPUT.put_line ('Inside create_a_combination-' || allsegments);
ccid :=
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
validation_date => TO_CHAR
(SYSDATE,
fnd_flex_ext.DATE_FORMAT
),
concatenated_segments => allsegments
);
p_ccid := ccid;
IF ccid <= 0
THEN
flexerror := fnd_message.get;
END IF;
RETURN flexerror;
EXCEPTION
WHEN OTHERS
THEN
p_ccid := 0;
flexerror := SQLERRM || ' ' || fnd_message.get;
RETURN flexerror;
END create_a_combination;
-- main function
FUNCTION get_code_combination (
p_segment1 IN gl_code_combinations.segment1%TYPE,
p_segment2 IN gl_code_combinations.segment2%TYPE,
p_segment3 IN gl_code_combinations.segment3%TYPE,
p_segment4 IN gl_code_combinations.segment4%TYPE,
p_segment5 IN gl_code_combinations.segment5%TYPE,
p_segment6 IN gl_code_combinations.segment6%TYPE,
p_segment7 IN gl_code_combinations.segment7%TYPE,
p_segment8 IN gl_code_combinations.segment8%TYPE,
p_segment9 IN gl_code_combinations.segment9%TYPE,
p_set_of_books_name IN gl_ledgers.NAME%TYPE,
p_error_msg OUT VARCHAR2,
p_error_loc OUT VARCHAR2
)
RETURN gl_code_combinations.code_combination_id%TYPE
IS
CURSOR c_ccid_validate (
p_segment1 IN gl_code_combinations.segment1%TYPE,
p_segment2 IN gl_code_combinations.segment2%TYPE,
p_segment3 IN gl_code_combinations.segment3%TYPE,
p_segment4 IN gl_code_combinations.segment4%TYPE,
p_segment5 IN gl_code_combinations.segment5%TYPE,
p_segment6 IN gl_code_combinations.segment6%TYPE,
p_segment7 IN gl_code_combinations.segment7%TYPE,
p_segment8 IN gl_code_combinations.segment8%TYPE,
p_segment9 IN gl_code_combinations.segment9%TYPE
)
IS
SELECT code_combination_id
FROM gl_code_combinations
WHERE segment1 = p_segment1
AND segment2 = p_segment2
AND segment3 = p_segment3
AND segment4 = p_segment4
AND segment5 = p_segment5
AND segment6 = p_segment6
AND segment7 = p_segment7
AND segment8 = p_segment8
AND segment9 = p_segment9;
CURSOR c_flex_details
IS
SELECT id_flex_num
FROM fnd_id_flex_segments
WHERE id_flex_num =
(SELECT chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = (SELECT ledger_id
FROM gl_ledgers
WHERE NAME = p_set_of_books_name))
AND application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL')
AND enabled_flag = 'Y'
AND ROWNUM = 1;
l_flex_details c_flex_details%ROWTYPE;
l_flexerror VARCHAR2 (2560) := NULL;
l_acc_segment1 gl_code_combinations.segment1%TYPE;
l_acc_segment2 gl_code_combinations.segment2%TYPE;
l_acc_segment3 gl_code_combinations.segment3%TYPE;
l_acc_segment4 gl_code_combinations.segment4%TYPE;
l_acc_segment5 gl_code_combinations.segment5%TYPE;
l_acc_segment6 gl_code_combinations.segment6%TYPE;
l_acc_segment7 gl_code_combinations.segment7%TYPE;
l_acc_segment8 gl_code_combinations.segment8%TYPE;
l_acc_segment9 gl_code_combinations.segment9%TYPE;
l_ccid gl_code_combinations.code_combination_id%TYPE := NULL;
l_user_name fnd_user.user_name%TYPE
:= fnd_profile.VALUE ('USERNAME');
x_delimiter VARCHAR2 (10);
BEGIN
p_error_loc := 'Validate CCID';
FOR c_ccid_validate_rec IN c_ccid_validate (p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8,
p_segment9
)
l_ccid := c_ccid_validate_rec.code_combination_id;
RETURN (l_ccid);
END LOOP ;
IF l_ccid IS NULL
THEN
fnd_file.put_line (fnd_file.LOG,
'CCID Not Found in gl_code_combinations'
);
fnd_file.put_line (fnd_file.LOG,
'CCID Not Found..Creating New CCID...'
);
-------- Cursor for getting the SOB details---------------------------
OPEN c_flex_details;
FETCH c_flex_details
INTO l_flex_details;
CLOSE c_flex_details;
fnd_file.put_line (fnd_file.LOG,
'chart of account :-'
|| l_flex_details.id_flex_num
);
x_delimiter :=
fnd_flex_ext.get_delimiter
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_flex_details.id_flex_num
);
--Create CCID
l_flexerror :=
create_a_combination (p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8,
p_segment9,
x_delimiter,
l_flex_details.id_flex_num,
l_ccid
);
fnd_file.put_line (fnd_file.LOG, 'Newly Created CCID-' || l_ccid);
IF l_ccid <= 0
THEN
fnd_file.put_line (fnd_file.LOG, 'l_flexerror' || l_flexerror);
p_error_msg := l_flexerror;
RETURN (0);
ELSE
p_error_msg := NULL;
RETURN (l_ccid);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_flexerror := SQLERRM;
p_error_msg := l_flexerror;
RETURN 0;
WHEN OTHERS
THEN
l_flexerror := SQLERRM;
p_error_msg := l_flexerror;
RETURN 0;
END get_code_combination;
END xxran_ccid_pkg;
/*We can simply use API as well, if we are inside a package. we can use such kind of stand alone package, if we want to call it from outside.*/
No comments:
Post a Comment