Thanks to Phani for sharing this.
CREATE OR REPLACE PACKAGE xxcofi_category_con_ext_pkg AUTHID CURRENT_USER
AS
/* ----------------------------------------------------------------- */
/* Program Name : XXCOFI_CATEGORY_CON_EXT_PKG */
/* */
/* TYPE : PL/SQL Package Specs */
/* */
/* Input Parms : - N/A */
/* */
/* Output Parms : - */
/* */
/* */
/* AUTHOR : JPREDDY */
/* */
/* DATE : 08-MAR-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : */
/* */
/* CHANGE HISTORY */
/* ------------------------------------------------------------------*/
/* DATE AUTHOR VERSION REASON */
/* ------------------------------------------------------------------*/
/* 08-MAR-2010 JPREDDY 1.0 Initial creation */
/**********************************************************************
DESCRIPTION:
This package contains procedures to create the category extract file.
This procedure extract all the category information to
create the category extract file.
***********************************************************************/
g_currdate DATE;
g_start_time VARCHAR2(25);
g_end_time VARCHAR2(25);
g_user NUMBER;
g_request_id NUMBER;
g_conc_program fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE;
g_err_cnt NUMBER := 0;
g_upv_err_cnt NUMBER := 0;
g_program_name VARCHAR2(100);
g_err_cnt_item NUMBER := 0;
g_errcode NUMBER := 0;
g_curr_sequence NUMBER;
g_datetime DATE;
-- g_totav_count_rec NUMBER := 0;
g_count_records NUMBER := 0;
g_count_dis_rec NUMBER := 0;
PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT NUMBER
);
PROCEDURE category_table_extract ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
);
PROCEDURE generate_output;
PROCEDURE write(p_type IN VARCHAR2
, p_message IN VARCHAR2);
END xxcofi_category_con_ext_pkg;
/
CREATE OR REPLACE PACKAGE BODY apps.xxcofi_category_con_ext_pkg
IS
/* ----------------------------------------------------------------- */
/* Program Name : XXCOFI_CATEGORY_CON_EXT_PKG */
/* */
/* TYPE : PL/SQL Package Specs */
/* */
/* Input Parms : - */
/* */
/* Output Parms : - */
/* */
/* */
/* AUTHOR : JPREDDY */
/* */
/* DATE : 08-MAR-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : */
/* */
/* CHANGE HISTORY */
/* ------------------------------------------------------------------*/
/* DATE AUTHOR VERSION REASON */
/* ------------------------------------------------------------------*/
/* 08-MAR-2010 JPREDDY 1.0 Initial creation */
/**********************************************************************
DESCRIPTION:
This package contains procedures to create the category extract file to Message broker.
The Message Broker using the translation rules will transform the data
into XML strings and pass them to Sterling.
DESCRIPTION:
This package contains procedures to create the category extract file.
This procedure extract all the category information to
create the category extract file. This populates all the valid Records
in the custom table (xxcofi_replacement_cat_ext).
***********************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
IS
/*****************************************************************************
PURPOSE: This is the main procedure which calls other
procedures for extracting the data and then generating output.
*****************************************************************************/
v_file_directory VARCHAR2(100);
v_file_name VARCHAR2(30);
BEGIN
g_currdate := sysdate;
g_request_id := fnd_global.conc_request_id;
BEGIN
/* Gets current program name from base table(fnd_concurrent_programs_vl)
for use in procedures for generating output - generate_output */
SELECT user_concurrent_program_name
INTO g_program_name
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_id = fnd_global.conc_program_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write('L','NO DATA FOUND while fetching concurrent program name ');
WHEN OTHERS
THEN
write('L','Error while fetching concurrent program name ' || SQLCODE || SQLERRM);
write('L','Message01');
END;
BEGIN
SELECT fnd_profile.value('XXCOFIDATA_OUT')
INTO v_file_directory
FROM DUAL;
v_file_name := 'Category';
write('L',' Path with File Name : '||v_file_directory||'/'||v_file_name||'.'|| 'dat');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write('L','NO DATA FOUND while fetching Outbound Path');
WHEN OTHERS
THEN
write('L','Error while fetching concurrent program name ' || SQLCODE || SQLERRM);
write('L','Message02');
END;
-- First Time Process procedure.
category_table_extract (v_file_name, v_file_directory);
generate_output;
END main;
PROCEDURE insert_custom (p_category_id IN NUMBER
,p_gma IN NUMBER
,p_desc_gma IN VARCHAR2
,p_group1 IN NUMBER
,p_desc_group1 IN VARCHAR2
,p_dept IN NUMBER
,p_desc_dept IN VARCHAR2
,p_cat IN NUMBER
,p_desc_cat IN VARCHAR2
,p_du IN NUMBER
,p_desc_du IN VARCHAR2
,p_di IN NUMBER
,p_desc_di IN VARCHAR2
)
IS
/*********************************************************************/
/* Description: This Procedure would take the IN parameter values */
/* And insert them in the custom table. */
/*********************************************************************/
-- Variables
v_category_id NUMBER;
v_gma NUMBER;
v_group1 NUMBER;
v_dept NUMBER;
v_cat NUMBER;
v_desc_gma VARCHAR2(100);
v_desc_group1 VARCHAR2(100);
v_desc_dept VARCHAR2(100);
v_desc_cat VARCHAR2(100);
v_desc_du VARCHAR2(100);
v_desc_di VARCHAR2(100);
v_du NUMBER;
v_di NUMBER;
v_gma_col VARCHAR2(3) := 'GMA';
v_group1_col VARCHAR2(5) := 'GROUP';
v_dept_col VARCHAR2(4) := 'DEPT';
v_cat_col VARCHAR2(8) := 'CATEGORY';
v_du_col VARCHAR2(2) := 'DU';
v_di_col VARCHAR2(2) := 'DI';
v_extract_flag VARCHAR2(30) := 'Extract Pending';
v_bay_con VARCHAR2(20) := '/BAYMasterCatalog';
BEGIN
v_category_id := p_category_id;
v_gma := p_gma;
v_group1 := p_group1;
v_dept := p_dept;
v_cat := p_cat;
v_du := p_du;
v_di := p_di;
v_desc_gma := p_desc_gma;
v_desc_group1 := p_desc_group1;
v_desc_dept := p_desc_dept;
v_desc_cat := p_desc_cat;
v_desc_du := p_desc_du;
v_desc_di := p_desc_di;
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
1
,v_category_id
,v_gma
,v_bay_con||'/'||v_gma
,v_desc_gma||'-'||v_gma_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
2
,v_category_id
,v_group1
,v_bay_con||'/'||v_gma||'/'||v_group1
,v_desc_group1||'-'||v_group1_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
3
,v_category_id
,v_dept
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept
,v_desc_dept||'-'||v_dept_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
4
,v_category_id
,v_cat
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept||'/'||v_cat
,v_desc_cat||'-'||v_cat_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
5
,v_category_id
,v_du
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept||'/'||v_cat||'/'||v_du
,v_desc_du||'-'||v_du_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
6
,v_category_id
,v_di
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept||'/'||v_cat||'/'||v_du||'/'||v_di
,v_desc_di||'-'||v_di_col
,v_extract_flag
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
write('L','Error while INSERT data in custom table ' || SQLCODE || SQLERRM);
write('L','Error with Category ID: '||v_category_id);
ROLLBACK;
END insert_custom;
PROCEDURE created_data_file ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
)
IS
/**********************************************************************/
/* Description: This procedure would create the data file */
/* Query all the data from the custom table. */
/**********************************************************************/
-- Variables
v_file_handle UTL_FILE.FILE_TYPE;
v_file_directory VARCHAR2(100) := p_file_directory;
v_report_name VARCHAR2(70) := p_file_name;
v_buff_size NUMBER := 32000;
-- Other temporary Variable Initializations
v_rec_count NUMBER :=0;
-- Cursor to fetch the records from the Custom table.
CURSOR inc_info_cur
IS
SELECT xie.file_seq_num file_seq_num
,xie.category_id category_id
,NVL(xie.category_segment_value,' ') category_segment_value
,NVL(xie.category_path,' ') category_path
,NVL(xie.category_description,' ') category_description
FROM xxcofi.xxcofi_replacement_cat_ext xie
WHERE xie.extract_flag = 'Extract Pending'
ORDER BY xie.category_id, xie.file_seq_num ASC;
-- Variables for the Header Inform in the Out file.
-- Variables defined for the Header Info in Out file records.
col1e_desc VARCHAR2(20) := 'category_id';
col2e_desc VARCHAR2(30) := 'category_segment_value';
col3e_desc VARCHAR2(20) := 'category_path';
col4e_desc VARCHAR2(20) := 'category_description';
BEGIN
v_file_handle := UTL_FILE.FOPEN (v_file_directory, v_report_name ||'.'|| 'dat','w', v_buff_size);
BEGIN
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
col1e_desc
|| ','
|| col2e_desc
|| ','
|| col3e_desc
|| ','
|| col4e_desc
);
For inc_info_rec in inc_info_cur
loop
v_rec_count := v_rec_count+1;
BEGIN
UTL_FILE.put_line(v_file_handle,
rpad(inc_info_rec.category_id,20,' ')
||rpad(inc_info_rec.category_segment_value,4,' ')
||rpad(inc_info_rec.category_path,50,' ')
||rpad(inc_info_rec.category_description,100,' ')
);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
inc_info_rec.category_id
||','
||inc_info_rec.category_segment_value
||','
||inc_info_rec.category_path
||','
||inc_info_rec.category_description
);
UPDATE xxcofi.xxcofi_replacement_cat_ext
SET extract_flag= 'Extract Completed'
WHERE file_seq_num = inc_info_rec.file_seq_num
AND extract_flag = 'Extract Pending'
AND category_id = inc_info_rec.category_id;
EXCEPTION
WHEN OTHERS
THEN
write('L','Error while Writing Data in Data file ' || SQLCODE || SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG,
rpad(inc_info_rec.category_id,20,' ')
||rpad(inc_info_rec.category_segment_value,4,' ')
||rpad(inc_info_rec.category_path,50,' ')
||rpad(inc_info_rec.category_description,100,' ')
);
END;
END LOOP;
g_count_records := v_rec_count;
EXCEPTION
WHEN OTHERS
THEN
write('L', 'inc_info_rec loop terminated due to exception '||chr(10)||
SQLCODE||' : '|| SQLERRM);
g_errcode:=2;
END;
-- UTL_FILE.put_line(v_file_handle,'FTAIL'||LPAD(NVL(TO_CHAR(v_rec_count),'0'),10,'0'));
UTL_FILE.fflush(v_file_handle);
UTL_FILE.fclose(v_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - invalid path');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Mode');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Operation');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Filehandle');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.write_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Write Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Read Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Internal Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Other Error'
|| SQLCODE || SQLERRM);
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
END;
EXCEPTION
WHEN OTHERS
THEN
write('L', 'Program oh_extract terminated due to error '||chr(10)
|| SQLCODE|| ' : '|| SQLERRM);
g_errcode:=2;
RETURN;
END created_data_file;
PROCEDURE category_table_extract( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
)
IS
/************************************************************************/
/* Description: procedure for first time run. */
/* This will fetch all the Category information */
/* All the Records in the Custom table will be */
/* Inserted to process for the Extract file. */
/* */
/************************************************************************/
-- Cursor would fetch all the category Information.
CURSOR cur_current_cat
IS
SELECT distinct mc.category_id category_id
, mc.segment1 GMA
, (select ffvt.description
from fnd_flex_values_tl ffvt
,fnd_flex_values ffv
,fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_GMA'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment1) desc_gma
, mc.segment2 GROUP1
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV-GROUP'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment2) desc_group1
, mc.segment3 DEPT
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_Dept'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment3) desc_dept
, mc.segment4 CAT
, (select ffvt.description
from fnd_flex_values_tl ffvt
,fnd_flex_values ffv
,fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_Cat'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment4) desc_cat
, mc.segment5 DU
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_DU'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment5) desc_du
, mc.segment6 DI
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV-DI'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment6) desc_di
FROM mtl_categories_b mc
WHERE mc.structure_id=50234
AND mc.segment1 != 0
ORDER BY mc.category_id;
-- Parameter Variables
v_file_name VARCHAR2(20) := p_file_name;
v_file_directory VARCHAR2(150) := p_file_directory;
-- Variables
v_category_id NUMBER;
v_gma NUMBER;
v_group1 NUMBER;
v_dept NUMBER;
v_cat NUMBER;
v_desc_gma VARCHAR2(100);
v_desc_group1 VARCHAR2(100);
v_desc_dept VARCHAR2(100);
v_desc_cat VARCHAR2(100);
v_desc_du VARCHAR2(100);
v_desc_di VARCHAR2(100);
v_du NUMBER;
v_di NUMBER;
v_count_dis_rec NUMBER;
BEGIN
-- Item Fetch Cursor Open. Started.
-- Delete the records if there are any.
DELETE FROM xxcofi.xxcofi_replacement_cat_ext;
COMMIT;
v_count_dis_rec := 0;
FOR rec_current_cat IN cur_current_cat
LOOP
BEGIN
/* Category ID */
v_category_id := SUBSTR(rec_current_cat.category_id,1,20);
/* GMA Segment1 Value */
v_gma := SUBSTR(rec_current_cat.gma,1,32);
/* Group Segment2 Value */
v_group1 := SUBSTR(rec_current_cat.group1,1,32);
/* Group Segment2 Value */
v_dept := SUBSTR(rec_current_cat.dept,1,32);
/* Group Segment2 Value */
v_cat := SUBSTR(rec_current_cat.cat,1,32);
/* Description of the category in US language */
v_desc_gma := SUBSTR(rec_current_cat.desc_gma,1,100);
/* Description of the category in US language */
v_desc_group1 := SUBSTR(rec_current_cat.desc_group1,1,100);
/* Description of the category in US language */
v_desc_dept := SUBSTR(rec_current_cat.desc_dept,1,100);
/* Description of the category in US language */
v_desc_cat := SUBSTR(rec_current_cat.desc_cat,1,100);
/* Description of the category in US language */
v_desc_du := SUBSTR(rec_current_cat.desc_du,1,100);
/* Description of the category in US language */
v_desc_di := SUBSTR(rec_current_cat.desc_di,1,100);
/* Catagory Information DU */
v_du := SUBSTR(rec_current_cat.du,1,32);
/* Catagory Information DI */
v_di := SUBSTR(rec_current_cat.di,1,32);
insert_custom (v_category_id
,v_gma
,v_desc_gma
,v_group1
,v_desc_group1
,v_dept
,v_desc_dept
,v_cat
,v_desc_cat
,v_du
,v_desc_du
,v_di
,v_desc_di
);
v_count_dis_rec := v_count_dis_rec + 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
write('L','Error while fetching Category Information: ' || SQLCODE || SQLERRM);
write('L','For the Category ID: ' || rec_current_cat.category_id);
END;
END LOOP;
-- Item Fetch Cursor Open. Ended.
/* Calling the Procedure to create the Data file */
g_count_dis_rec := v_count_dis_rec;
created_data_file ( v_file_name
, v_file_directory
);
END category_table_extract;
/* procedure to send output to the fnd log */
PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.log, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END write;
/* procedure to write output to the log and output file */
PROCEDURE generate_output IS
BEGIN
--The following parameters will be displayed every time
--in log file
write('L', '.........1.........2.........3.........4.........5.........6.........7.........8');
write('L', '________________________________________________________________________________');
write('L','');
write('L','Request Name : '||g_program_name);
write('L','Request Id : '||g_request_id);
write('L','Date : '||TO_CHAR(SYSDATE,'MM/DD/YY'));
write('L','Number of Distinct Categories Extracted in Current Run : '||g_count_dis_rec);
write('L','Number of Record Extracted in Current Run : '||g_count_records);
EXCEPTION
WHEN OTHERS THEN
write('L','Error : '||SQLCODE||SQLERRM);
END generate_output;
END xxcofi_category_con_ext_pkg;
/
SHOW ERROR
/
EXIT
/
CREATE OR REPLACE PACKAGE xxcofi_category_con_ext_pkg AUTHID CURRENT_USER
AS
/* ----------------------------------------------------------------- */
/* Program Name : XXCOFI_CATEGORY_CON_EXT_PKG */
/* */
/* TYPE : PL/SQL Package Specs */
/* */
/* Input Parms : - N/A */
/* */
/* Output Parms : - */
/* */
/* */
/* AUTHOR : JPREDDY */
/* */
/* DATE : 08-MAR-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : */
/* */
/* CHANGE HISTORY */
/* ------------------------------------------------------------------*/
/* DATE AUTHOR VERSION REASON */
/* ------------------------------------------------------------------*/
/* 08-MAR-2010 JPREDDY 1.0 Initial creation */
/**********************************************************************
DESCRIPTION:
This package contains procedures to create the category extract file.
This procedure extract all the category information to
create the category extract file.
***********************************************************************/
g_currdate DATE;
g_start_time VARCHAR2(25);
g_end_time VARCHAR2(25);
g_user NUMBER;
g_request_id NUMBER;
g_conc_program fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE;
g_err_cnt NUMBER := 0;
g_upv_err_cnt NUMBER := 0;
g_program_name VARCHAR2(100);
g_err_cnt_item NUMBER := 0;
g_errcode NUMBER := 0;
g_curr_sequence NUMBER;
g_datetime DATE;
-- g_totav_count_rec NUMBER := 0;
g_count_records NUMBER := 0;
g_count_dis_rec NUMBER := 0;
PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT NUMBER
);
PROCEDURE category_table_extract ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
);
PROCEDURE generate_output;
PROCEDURE write(p_type IN VARCHAR2
, p_message IN VARCHAR2);
END xxcofi_category_con_ext_pkg;
/
CREATE OR REPLACE PACKAGE BODY apps.xxcofi_category_con_ext_pkg
IS
/* ----------------------------------------------------------------- */
/* Program Name : XXCOFI_CATEGORY_CON_EXT_PKG */
/* */
/* TYPE : PL/SQL Package Specs */
/* */
/* Input Parms : - */
/* */
/* Output Parms : - */
/* */
/* */
/* AUTHOR : JPREDDY */
/* */
/* DATE : 08-MAR-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : */
/* */
/* CHANGE HISTORY */
/* ------------------------------------------------------------------*/
/* DATE AUTHOR VERSION REASON */
/* ------------------------------------------------------------------*/
/* 08-MAR-2010 JPREDDY 1.0 Initial creation */
/**********************************************************************
DESCRIPTION:
This package contains procedures to create the category extract file to Message broker.
The Message Broker using the translation rules will transform the data
into XML strings and pass them to Sterling.
DESCRIPTION:
This package contains procedures to create the category extract file.
This procedure extract all the category information to
create the category extract file. This populates all the valid Records
in the custom table (xxcofi_replacement_cat_ext).
***********************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
IS
/*****************************************************************************
PURPOSE: This is the main procedure which calls other
procedures for extracting the data and then generating output.
*****************************************************************************/
v_file_directory VARCHAR2(100);
v_file_name VARCHAR2(30);
BEGIN
g_currdate := sysdate;
g_request_id := fnd_global.conc_request_id;
BEGIN
/* Gets current program name from base table(fnd_concurrent_programs_vl)
for use in procedures for generating output - generate_output */
SELECT user_concurrent_program_name
INTO g_program_name
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_id = fnd_global.conc_program_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write('L','NO DATA FOUND while fetching concurrent program name ');
WHEN OTHERS
THEN
write('L','Error while fetching concurrent program name ' || SQLCODE || SQLERRM);
write('L','Message01');
END;
BEGIN
SELECT fnd_profile.value('XXCOFIDATA_OUT')
INTO v_file_directory
FROM DUAL;
v_file_name := 'Category';
write('L',' Path with File Name : '||v_file_directory||'/'||v_file_name||'.'|| 'dat');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write('L','NO DATA FOUND while fetching Outbound Path');
WHEN OTHERS
THEN
write('L','Error while fetching concurrent program name ' || SQLCODE || SQLERRM);
write('L','Message02');
END;
-- First Time Process procedure.
category_table_extract (v_file_name, v_file_directory);
generate_output;
END main;
PROCEDURE insert_custom (p_category_id IN NUMBER
,p_gma IN NUMBER
,p_desc_gma IN VARCHAR2
,p_group1 IN NUMBER
,p_desc_group1 IN VARCHAR2
,p_dept IN NUMBER
,p_desc_dept IN VARCHAR2
,p_cat IN NUMBER
,p_desc_cat IN VARCHAR2
,p_du IN NUMBER
,p_desc_du IN VARCHAR2
,p_di IN NUMBER
,p_desc_di IN VARCHAR2
)
IS
/*********************************************************************/
/* Description: This Procedure would take the IN parameter values */
/* And insert them in the custom table. */
/*********************************************************************/
-- Variables
v_category_id NUMBER;
v_gma NUMBER;
v_group1 NUMBER;
v_dept NUMBER;
v_cat NUMBER;
v_desc_gma VARCHAR2(100);
v_desc_group1 VARCHAR2(100);
v_desc_dept VARCHAR2(100);
v_desc_cat VARCHAR2(100);
v_desc_du VARCHAR2(100);
v_desc_di VARCHAR2(100);
v_du NUMBER;
v_di NUMBER;
v_gma_col VARCHAR2(3) := 'GMA';
v_group1_col VARCHAR2(5) := 'GROUP';
v_dept_col VARCHAR2(4) := 'DEPT';
v_cat_col VARCHAR2(8) := 'CATEGORY';
v_du_col VARCHAR2(2) := 'DU';
v_di_col VARCHAR2(2) := 'DI';
v_extract_flag VARCHAR2(30) := 'Extract Pending';
v_bay_con VARCHAR2(20) := '/BAYMasterCatalog';
BEGIN
v_category_id := p_category_id;
v_gma := p_gma;
v_group1 := p_group1;
v_dept := p_dept;
v_cat := p_cat;
v_du := p_du;
v_di := p_di;
v_desc_gma := p_desc_gma;
v_desc_group1 := p_desc_group1;
v_desc_dept := p_desc_dept;
v_desc_cat := p_desc_cat;
v_desc_du := p_desc_du;
v_desc_di := p_desc_di;
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
1
,v_category_id
,v_gma
,v_bay_con||'/'||v_gma
,v_desc_gma||'-'||v_gma_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
2
,v_category_id
,v_group1
,v_bay_con||'/'||v_gma||'/'||v_group1
,v_desc_group1||'-'||v_group1_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
3
,v_category_id
,v_dept
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept
,v_desc_dept||'-'||v_dept_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
4
,v_category_id
,v_cat
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept||'/'||v_cat
,v_desc_cat||'-'||v_cat_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
5
,v_category_id
,v_du
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept||'/'||v_cat||'/'||v_du
,v_desc_du||'-'||v_du_col
,v_extract_flag
);
INSERT INTO xxcofi.xxcofi_replacement_cat_ext
(file_seq_num
,category_id
,category_segment_value
,category_path
,category_description
,extract_flag
)
VALUES
(
6
,v_category_id
,v_di
,v_bay_con||'/'||v_gma||'/'||v_group1||'/'||v_dept||'/'||v_cat||'/'||v_du||'/'||v_di
,v_desc_di||'-'||v_di_col
,v_extract_flag
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
write('L','Error while INSERT data in custom table ' || SQLCODE || SQLERRM);
write('L','Error with Category ID: '||v_category_id);
ROLLBACK;
END insert_custom;
PROCEDURE created_data_file ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
)
IS
/**********************************************************************/
/* Description: This procedure would create the data file */
/* Query all the data from the custom table. */
/**********************************************************************/
-- Variables
v_file_handle UTL_FILE.FILE_TYPE;
v_file_directory VARCHAR2(100) := p_file_directory;
v_report_name VARCHAR2(70) := p_file_name;
v_buff_size NUMBER := 32000;
-- Other temporary Variable Initializations
v_rec_count NUMBER :=0;
-- Cursor to fetch the records from the Custom table.
CURSOR inc_info_cur
IS
SELECT xie.file_seq_num file_seq_num
,xie.category_id category_id
,NVL(xie.category_segment_value,' ') category_segment_value
,NVL(xie.category_path,' ') category_path
,NVL(xie.category_description,' ') category_description
FROM xxcofi.xxcofi_replacement_cat_ext xie
WHERE xie.extract_flag = 'Extract Pending'
ORDER BY xie.category_id, xie.file_seq_num ASC;
-- Variables for the Header Inform in the Out file.
-- Variables defined for the Header Info in Out file records.
col1e_desc VARCHAR2(20) := 'category_id';
col2e_desc VARCHAR2(30) := 'category_segment_value';
col3e_desc VARCHAR2(20) := 'category_path';
col4e_desc VARCHAR2(20) := 'category_description';
BEGIN
v_file_handle := UTL_FILE.FOPEN (v_file_directory, v_report_name ||'.'|| 'dat','w', v_buff_size);
BEGIN
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
col1e_desc
|| ','
|| col2e_desc
|| ','
|| col3e_desc
|| ','
|| col4e_desc
);
For inc_info_rec in inc_info_cur
loop
v_rec_count := v_rec_count+1;
BEGIN
UTL_FILE.put_line(v_file_handle,
rpad(inc_info_rec.category_id,20,' ')
||rpad(inc_info_rec.category_segment_value,4,' ')
||rpad(inc_info_rec.category_path,50,' ')
||rpad(inc_info_rec.category_description,100,' ')
);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
inc_info_rec.category_id
||','
||inc_info_rec.category_segment_value
||','
||inc_info_rec.category_path
||','
||inc_info_rec.category_description
);
UPDATE xxcofi.xxcofi_replacement_cat_ext
SET extract_flag= 'Extract Completed'
WHERE file_seq_num = inc_info_rec.file_seq_num
AND extract_flag = 'Extract Pending'
AND category_id = inc_info_rec.category_id;
EXCEPTION
WHEN OTHERS
THEN
write('L','Error while Writing Data in Data file ' || SQLCODE || SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG,
rpad(inc_info_rec.category_id,20,' ')
||rpad(inc_info_rec.category_segment_value,4,' ')
||rpad(inc_info_rec.category_path,50,' ')
||rpad(inc_info_rec.category_description,100,' ')
);
END;
END LOOP;
g_count_records := v_rec_count;
EXCEPTION
WHEN OTHERS
THEN
write('L', 'inc_info_rec loop terminated due to exception '||chr(10)||
SQLCODE||' : '|| SQLERRM);
g_errcode:=2;
END;
-- UTL_FILE.put_line(v_file_handle,'FTAIL'||LPAD(NVL(TO_CHAR(v_rec_count),'0'),10,'0'));
UTL_FILE.fflush(v_file_handle);
UTL_FILE.fclose(v_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - invalid path');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Mode');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Operation');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Filehandle');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.write_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Write Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Read Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Internal Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Other Error'
|| SQLCODE || SQLERRM);
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;
END;
EXCEPTION
WHEN OTHERS
THEN
write('L', 'Program oh_extract terminated due to error '||chr(10)
|| SQLCODE|| ' : '|| SQLERRM);
g_errcode:=2;
RETURN;
END created_data_file;
PROCEDURE category_table_extract( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
)
IS
/************************************************************************/
/* Description: procedure for first time run. */
/* This will fetch all the Category information */
/* All the Records in the Custom table will be */
/* Inserted to process for the Extract file. */
/* */
/************************************************************************/
-- Cursor would fetch all the category Information.
CURSOR cur_current_cat
IS
SELECT distinct mc.category_id category_id
, mc.segment1 GMA
, (select ffvt.description
from fnd_flex_values_tl ffvt
,fnd_flex_values ffv
,fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_GMA'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment1) desc_gma
, mc.segment2 GROUP1
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV-GROUP'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment2) desc_group1
, mc.segment3 DEPT
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_Dept'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment3) desc_dept
, mc.segment4 CAT
, (select ffvt.description
from fnd_flex_values_tl ffvt
,fnd_flex_values ffv
,fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_Cat'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment4) desc_cat
, mc.segment5 DU
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV_DU'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment5) desc_du
, mc.segment6 DI
, (select ffvt.description
from fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name='XXCOFIINV-DI'
and ffvt.language='US'
and ffv.flex_value_set_id=ffvs.flex_value_set_id
and ffv.flex_value_id=ffvt.flex_value_id
and ffv.flex_value=segment6) desc_di
FROM mtl_categories_b mc
WHERE mc.structure_id=50234
AND mc.segment1 != 0
ORDER BY mc.category_id;
-- Parameter Variables
v_file_name VARCHAR2(20) := p_file_name;
v_file_directory VARCHAR2(150) := p_file_directory;
-- Variables
v_category_id NUMBER;
v_gma NUMBER;
v_group1 NUMBER;
v_dept NUMBER;
v_cat NUMBER;
v_desc_gma VARCHAR2(100);
v_desc_group1 VARCHAR2(100);
v_desc_dept VARCHAR2(100);
v_desc_cat VARCHAR2(100);
v_desc_du VARCHAR2(100);
v_desc_di VARCHAR2(100);
v_du NUMBER;
v_di NUMBER;
v_count_dis_rec NUMBER;
BEGIN
-- Item Fetch Cursor Open. Started.
-- Delete the records if there are any.
DELETE FROM xxcofi.xxcofi_replacement_cat_ext;
COMMIT;
v_count_dis_rec := 0;
FOR rec_current_cat IN cur_current_cat
LOOP
BEGIN
/* Category ID */
v_category_id := SUBSTR(rec_current_cat.category_id,1,20);
/* GMA Segment1 Value */
v_gma := SUBSTR(rec_current_cat.gma,1,32);
/* Group Segment2 Value */
v_group1 := SUBSTR(rec_current_cat.group1,1,32);
/* Group Segment2 Value */
v_dept := SUBSTR(rec_current_cat.dept,1,32);
/* Group Segment2 Value */
v_cat := SUBSTR(rec_current_cat.cat,1,32);
/* Description of the category in US language */
v_desc_gma := SUBSTR(rec_current_cat.desc_gma,1,100);
/* Description of the category in US language */
v_desc_group1 := SUBSTR(rec_current_cat.desc_group1,1,100);
/* Description of the category in US language */
v_desc_dept := SUBSTR(rec_current_cat.desc_dept,1,100);
/* Description of the category in US language */
v_desc_cat := SUBSTR(rec_current_cat.desc_cat,1,100);
/* Description of the category in US language */
v_desc_du := SUBSTR(rec_current_cat.desc_du,1,100);
/* Description of the category in US language */
v_desc_di := SUBSTR(rec_current_cat.desc_di,1,100);
/* Catagory Information DU */
v_du := SUBSTR(rec_current_cat.du,1,32);
/* Catagory Information DI */
v_di := SUBSTR(rec_current_cat.di,1,32);
insert_custom (v_category_id
,v_gma
,v_desc_gma
,v_group1
,v_desc_group1
,v_dept
,v_desc_dept
,v_cat
,v_desc_cat
,v_du
,v_desc_du
,v_di
,v_desc_di
);
v_count_dis_rec := v_count_dis_rec + 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
write('L','Error while fetching Category Information: ' || SQLCODE || SQLERRM);
write('L','For the Category ID: ' || rec_current_cat.category_id);
END;
END LOOP;
-- Item Fetch Cursor Open. Ended.
/* Calling the Procedure to create the Data file */
g_count_dis_rec := v_count_dis_rec;
created_data_file ( v_file_name
, v_file_directory
);
END category_table_extract;
/* procedure to send output to the fnd log */
PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.log, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END write;
/* procedure to write output to the log and output file */
PROCEDURE generate_output IS
BEGIN
--The following parameters will be displayed every time
--in log file
write('L', '.........1.........2.........3.........4.........5.........6.........7.........8');
write('L', '________________________________________________________________________________');
write('L','');
write('L','Request Name : '||g_program_name);
write('L','Request Id : '||g_request_id);
write('L','Date : '||TO_CHAR(SYSDATE,'MM/DD/YY'));
write('L','Number of Distinct Categories Extracted in Current Run : '||g_count_dis_rec);
write('L','Number of Record Extracted in Current Run : '||g_count_records);
EXCEPTION
WHEN OTHERS THEN
write('L','Error : '||SQLCODE||SQLERRM);
END generate_output;
END xxcofi_category_con_ext_pkg;
/
SHOW ERROR
/
EXIT
/
No comments:
Post a Comment