15/24
Sample PL/SQL Package
Example from Oracle iProcurement
CREATE OR REPLACE PACKAGE BODY icx_ecc_util_pvt AS
/* $Header: ICXECCOP.pls 120.0.1 2019/02/28 10:13:01 noship $ */
/* Utility Package for populating ECC metadata. */
FUNCTION get_descriptors_sql(
p_category IN NUMBER,
p_load_type IN VARCHAR2,
p_languages IN VARCHAR2,
p_ds_last_success_run IN TIMESTAMP)
RETURN CLOB
IS
l_sql_text CLOB;
v_for_lang_pivot_clause varchar2(400) := FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_PIVOT_FOR_LANG_CL(p_languages);
l_desc_sql_text VARCHAR2(30000) := ' select * from ICX_CAT_ECC_' || p_category || '_VL';
l_pivot_pre_sql varchar2(2000) := 'select * from ( ';
l_pivot_post_sql1 VARCHAR2(2000) := ' )
PIVOT ( count(1) as IS_TRANSLATION_AVAILABLE ';
l_pivot_post_sql2 VARCHAR2(2000) := ' ';
l_pivot_post_sql3 VARCHAR2(2000) := 'FOR LANGUAGE IN ('||v_for_lang_pivot_clause||'))' ;
l_last_update_condition varchar2(1000) := ' WHERE ECC_LAST_UPDATE_DATE >= to_date(to_char(to_timestamp('||''''||p_ds_last_success_run||''''||'),''DD-MON-YY HH24.MI.SS''),''DD-MON-YY HH24.MI.SS'')';
l_trans_attributes dbms_sql.varchar2_table;
cursor c_trans_attributes is
SELECT Decode( UPPER(regexp_replace(KEY,'[-*# ]','_') ), 'SIZE', 'SIZE_', UPPER(regexp_replace(KEY,'[-*# ]','_') ) ) ATTRIBUTE_NAME
FROM ICX_CAT_ATTRIBUTES_TL
WHERE stored_in_table ='PO_ATTRIBUTE_VALUES_TLP'
AND STORED_IN_COLUMN IS NOT NULL
AND ATTRIBUTE_ID>28
AND LANGUAGE='US'
AND RT_CATEGORY_ID=p_category;
BEGIN
OPEN c_trans_attributes;
FETCH c_trans_attributes BULK COLLECT INTO l_trans_attributes;
CLOSE c_trans_attributes;
/*
IF(l_trans_attributes.COUNT = 0) THEN
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_desc_sql_text;
ELSE
l_sql_text := l_desc_sql_text || l_last_update_condition;
END IF;
RETURN l_sql_text;
END IF;
*/
FOR i IN 1..l_trans_attributes.count
LOOP
l_pivot_post_sql2 := l_pivot_post_sql2 || ', MAX( ' || l_trans_attributes(i) || ' ) as ' || l_trans_attributes(i) ;
END LOOP;
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_pivot_pre_sql || l_desc_sql_text || l_pivot_post_sql1 || l_pivot_post_sql2 || l_pivot_post_sql3;
ELSE
l_sql_text := l_pivot_pre_sql || l_desc_sql_text || l_last_update_condition || l_pivot_post_sql1 || l_pivot_post_sql2 || l_pivot_post_sql3;
END IF;
RETURN l_sql_text;
END get_descriptors_sql;
/*
** GET_ECC_DATA_LOAD_INFO - Retrieves the data load details for the given data set key
**
** IN parameters
** p_dataset_id - data set key
** p_load_type - Indicating Full or incremental load (F/I/Custom).For custom there are no data load rules defined
** p_ds_last_success_run - Returns the last successful etl run for the data set key
** OUT parameters
** x_ecc_ds_meta_rec - ecc_ds_meta_rec
** x_return_status - return status
*/
------------------------------------------------------------------------
PROCEDURE get_ecc_data_load_info(
p_dataset_key IN VARCHAR2,
p_load_type IN VARCHAR2,
p_ds_last_success_run IN TIMESTAMP,
p_languages IN VARCHAR2,
p_addl_params IN ecc_sec_field_values,
x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
x_return_status OUT NOCOPY VARCHAR2 )
IS
query_det_arr ecc_query_det_arr_type := ecc_query_det_arr_type(NULL);
l_apps_schema_name varchar2(25) := 'APPS';
CURSOR c_category_view
IS
SELECT DISTINCT RT_CATEGORY_ID FROM ICX_CAT_CATEGORIES_TL
WHERE LANGUAGE='US'
AND EXISTS
(SELECT 1 FROM ALL_VIEWS
WHERE VIEW_NAME = 'ICX_CAT_ECC_'||RT_CATEGORY_ID||'_VL'
AND owner=l_apps_schema_name);
l_categories dbms_sql.number_table;
l_category NUMBER;
l_sql_index number := 1;
BEGIN
select oracle_username
into l_apps_schema_name
from fnd_oracle_userid
where read_only_flag = 'U';
IF ( p_load_type = 'INCREMENTAL_LOAD' ) THEN
query_det_arr(l_sql_index) := ecc_query_det_rec(get_sql_text(p_dataset_key,p_load_type,g_icx_ecc_del_op, 'ITEMS', p_ds_last_success_run,p_languages),g_icx_ecc_del_op);
query_det_arr.extend ();
l_sql_index := l_sql_index +1;
END IF;
query_det_arr(l_sql_index) := ecc_query_det_rec(get_sql_text(p_dataset_key,p_load_type,g_icx_ecc_ins_op, 'ITEMS', p_ds_last_success_run,p_languages),g_icx_ecc_ins_op);
query_det_arr.extend ();
l_sql_index := l_sql_index +1;
query_det_arr(l_sql_index) := ecc_query_det_rec(get_sql_text(p_dataset_key,p_load_type,g_icx_ecc_upd_op, 'HIERARCHY', p_ds_last_success_run,p_languages),g_icx_ecc_upd_op);
query_det_arr.extend ();
l_sql_index := l_sql_index +1;
query_det_arr(l_sql_index) := ecc_query_det_rec(get_sql_text(p_dataset_key,p_load_type,g_icx_ecc_upd_op, 'ZONESB', p_ds_last_success_run,p_languages),g_icx_ecc_upd_op);
query_det_arr.extend ();
l_sql_index := l_sql_index +1;
query_det_arr(l_sql_index) := ecc_query_det_rec(get_sql_text(p_dataset_key,p_load_type,g_icx_ecc_upd_op, 'ZONESP', p_ds_last_success_run,p_languages),g_icx_ecc_upd_op);
query_det_arr.extend ();
l_sql_index := l_sql_index +1;
query_det_arr(l_sql_index) := ecc_query_det_rec(get_sql_text(p_dataset_key,p_load_type,g_icx_ecc_upd_op, 'ZONESI', p_ds_last_success_run,p_languages),g_icx_ecc_upd_op);
OPEN c_category_view;
FETCH c_category_view BULK COLLECT INTO l_categories;
CLOSE c_category_view;
FOR i IN 1..l_categories.count
LOOP
query_det_arr.extend ();
l_sql_index := l_sql_index +1;
query_det_arr(l_sql_index) := ecc_query_det_rec(get_descriptors_sql(l_categories(i), p_load_type, p_languages, p_ds_last_success_run ),g_icx_ecc_upd_op);
END LOOP;
x_ecc_ds_meta_rec := ecc_ds_meta_rec(p_dataset_key,query_det_arr);
x_return_status := 'S';
END get_ecc_data_load_info;
FUNCTION get_sql_text(
p_dataset_key IN VARCHAR2,
p_load_type IN VARCHAR2,
p_operation IN VARCHAR2,
p_data_type IN VARCHAR2,
p_ds_last_success_run IN TIMESTAMP,
p_languages IN VARCHAR2)
RETURN CLOB
IS
l_sql_text CLOB;
v_for_lang_pivot_clause varchar2(400) := FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_PIVOT_FOR_LANG_CL(p_languages);
l_last_update_condition varchar2(1000) := ' WHERE ECC_LAST_UPDATE_DATE >= to_date(to_char(to_timestamp('||''''||p_ds_last_success_run||''''||'),''DD-MON-YY HH24.MI.SS''),''DD-MON-YY HH24.MI.SS'') ';
l_pivot_pre_sql VARCHAR2(100) := 'SELECT * FROM ( ';
l_pivot_post_sql VARCHAR2(2000) := ' )
PIVOT (
MAX(SHOPPING_CATEGORY) AS SHOPPING_CATEGORY,
MAX(DESCRIPTION) AS DESCRIPTION,
MAX(UNIT_OF_MEASURE) AS UNIT_OF_MEASURE,
MAX(COMMENTS) AS COMMENTS,
MAX(LONG_DESCRIPTION) AS LONG_DESCRIPTION,
MAX(ITEM_SOURCE_TEXT) AS ITEM_SOURCE_TEXT,
MAX(CONTENT_TYPE_FILTER) AS CONTENT_TYPE_FILTER,
MAX(DISPLAY_PRICE) AS DISPLAY_PRICE,
MAX(PRICE_BREAK) AS PRICE_BREAK,
MAX(ITEM_RATING_FILTER) AS ITEM_RATING_FILTER,
MAX(SUPPLIER_RATING_FILTER) AS SUPPLIER_RATING_FILTER,
MAX(PUNCHOUT_MORE_DETAILS) AS PUNCHOUT_MORE_DETAILS,
MAX(CONTENT_TYPE_RESULTS) AS CONTENT_TYPE_RESULTS,
MAX(SHOPPING_CATEGORY_1) AS SHOPPING_CATEGORY_1,
MAX(SHOPPING_CATEGORY_2) AS SHOPPING_CATEGORY_2,
MAX(SHOPPING_CATEGORY_3) AS SHOPPING_CATEGORY_3,
MAX(KEYWORDS) AS KEYWORDS,
MAX(SOURCE) AS SOURCE,
COUNT(1) AS IS_TRANSLATION_AVAILABLE
FOR LANGUAGE IN ('||v_for_lang_pivot_clause||'))' ;
l_items_sql_text VARCHAR2(30000) :=
'SELECT ECC_SPEC_ID,
THUMBNAIL_IMAGE ,
SHOPPING_CATEGORY ,
SUPPLIER ,
SUPPLIER_SITE ,
SUPPLIER_PART_NUM ,
SUPPLIER_PART_AUXID ,
INTERNAL_ITEM_NUM ,
SOURCE ,
MANUFACTURER ,
MANUFACTURER_PART_NUM ,
PURCHASING_CATEGORY ,
DESCRIPTION ,
ITEM_REVISION ,
UNIT_OF_MEASURE ,
PRICE ,
CURRENCY ,
FUNCTIONAL_PRICE ,
FUNCTIONAL_CURRENCY ,
AVAILABILITY ,
LEAD_TIME ,
UNSPSC ,
ALIAS ,
COMMENTS ,
LONG_DESCRIPTION ,
ATTACHMENT_URL ,
SUPPLIER_URL ,
MANUFACTURER_URL ,
ORG_ID ,
LANGUAGE ,
ITEM_SOURCE_TEXT ,
CONTENT_TYPE_FILTER ,
dbms_lob.substr(ATTACHMENT, 4000, 1) ATTACHMENT ,
ZONE_ID ,
DISPLAY_PRICE ,
CONTENT_ID ,
CONTENT_URL ,
KEYWORDS ,
CONTENT_TYPE ,
HIDE_SMARTFORM_ATTRS ,
HIDE_INFOCONTENT_ATTRS ,
PRICE_BREAK ,
HIDE_PRICE_BREAK ,
ITEM_RATING_FILTER ,
SUPPLIER_RATING_FILTER ,
HIDE_ADDTOCART ,
PUNCHOUT_MORE_DETAILS ,
OPEN_DESCRIPTION_IN_NEW_TAB ,
ITEM_RATING ,
SUPPLIER_RATING ,
HIDE_CONTENT_TYPE ,
CONTENT_TYPE_RESULTS ,
SHOPPING_CATEGORY_1,
SHOPPING_CATEGORY_2,
SHOPPING_CATEGORY_3,
ZONESB,
ZONESP,
ZONESI,
PREFERRED_SUPPLIER_ICON,
ECO_FRIENDLY_SUPPLIER_ICON,
GREEN_SUPPLIER_ICON,
OFF_CONTRACT_ITEM_ICON,
OVER_PRICED_ICON
FROM ICX_CAT_ECC_ITEMS_V ' ;
l_hierarchy_sql_text_pre VARCHAR2(30000) := 'SELECT * FROM(
SELECT
ecc_spec_id,
shopping_category,
CASE
WHEN shopping_category_3 IS NOT NULL THEN shopping_category_3
WHEN shopping_category_2 IS NOT NULL THEN shopping_category_2
ELSE ''Others'' END AS shopping_category_1 ,
CASE
WHEN shopping_category_3 IS NOT NULL AND shopping_category_2 IS NOT NULL THEN shopping_category_2
WHEN shopping_category_2 IS NOT NULL AND shopping_category_1 IS NOT NULL THEN shopping_category_1
ELSE shopping_category_1
END AS shopping_category_2,
CASE
WHEN shopping_category_3 IS NOT NULL AND shopping_category_2 IS NOT NULL AND shopping_category_1 IS NOT NULL THEN shopping_category_1
ELSE null
END AS shopping_category_3,
LANGUAGE
FROM icx_ecc_category_hierarchy ';
l_hierarchy_sql_text_post VARCHAR2(30000) := ' ) PIVOT(
MAX(SHOPPING_CATEGORY) AS SHOPPING_CATEGORY,
MAX(SHOPPING_CATEGORY_1) AS SHOPPING_CATEGORY_1,
MAX(SHOPPING_CATEGORY_2) AS SHOPPING_CATEGORY_2,
MAX(SHOPPING_CATEGORY_3) AS SHOPPING_CATEGORY_3
FOR LANGUAGE IN ('||v_for_lang_pivot_clause||'))' ;
l_zonesb_sql_text VARCHAR2(30000) := 'SELECT
ecc_spec_id,
zonesb
from icx_cat_ecc_zones_b';
l_zonesp_sql_text VARCHAR2(30000) := 'SELECT
ecc_spec_id,
zonesp
from icx_cat_ecc_zones_p';
l_zonesi_sql_text VARCHAR2(30000) := 'SELECT
ecc_spec_id,
zonesi
from icx_cat_ecc_zones_i';
BEGIN
IF ( p_operation = g_icx_ecc_del_op ) THEN
l_sql_text := 'SELECT recordkey ECC_SPEC_ID
FROM icx_cat_endeca_item_attributes
WHERE attributekey LIKE ''##DELETERECORD##''' ;
return l_sql_text;
END IF;
IF ( p_data_type = 'ITEMS' ) THEN
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_pivot_pre_sql || l_items_sql_text || l_pivot_post_sql;
ELSE
l_sql_text := l_pivot_pre_sql || l_items_sql_text || l_last_update_condition || l_pivot_post_sql;
END IF;
ELSIF( p_data_type = 'HIERARCHY' ) THEN
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_hierarchy_sql_text_pre || l_hierarchy_sql_text_post ;
ELSE
l_sql_text := l_hierarchy_sql_text_pre || l_last_update_condition || l_hierarchy_sql_text_post ;
END IF;
ELSIF( p_data_type = 'ZONESB' ) THEN
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_zonesb_sql_text;
ELSE
l_sql_text := l_zonesb_sql_text || l_last_update_condition ;
END IF;
ELSIF( p_data_type = 'ZONESP' ) THEN
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_zonesp_sql_text;
ELSE
l_sql_text := l_zonesp_sql_text || l_last_update_condition ;
END IF;
ELSIF( p_data_type = 'ZONESI' ) THEN
IF ( p_load_type = 'FULL_LOAD' ) THEN
l_sql_text := l_zonesi_sql_text;
ELSE
l_sql_text := l_zonesi_sql_text || l_last_update_condition ;
END IF;
END IF;
RETURN l_sql_text;
END get_sql_text;
PROCEDURE create_ecc_desc_view(
p_category_id IN NUMBER )
IS
l_view_sql VARCHAR2(20000);
l_category_id NUMBER := p_category_id;
l_attr_list VARCHAR2(2000) := '';
l_attr_val_list VARCHAR2(2000) := '';
l_attribute_name VARCHAR2(100) := '';
CURSOR c_base_attrs
IS
SELECT UPPER(regexp_replace(KEY,'[-*# ]','_') ),
DECODE(icon_indicator,1,'decode(PAV.'
|| stored_in_column
|| ' , ''Y'', ''true'', ''false'') ','PAV.'
|| stored_in_column) value
FROM icx_cat_attributes_tl
WHERE rt_category_id = l_category_id
AND stored_in_table = 'PO_ATTRIBUTE_VALUES'
AND stored_in_column IS NOT NULL
AND attribute_id > 28
AND language = 'US';
CURSOR c_base_attrs_tl
IS
SELECT UPPER(regexp_replace(KEY,'[-*# ]','_') ),
'PAV_TLP'
|| '.'
|| stored_in_column value
FROM icx_cat_attributes_tl
WHERE rt_category_id = l_category_id
AND stored_in_table = 'PO_ATTRIBUTE_VALUES_TLP'
AND stored_in_column IS NOT NULL
AND attribute_id > 28
AND language = 'US';
l_attrs_tbl dbms_sql.varchar2_table;
l_attr_vals_tbl dbms_sql.varchar2_table;
BEGIN
l_view_sql := 'create or replace view ICX_CAT_ECC_' || l_category_id || '_VL (';
l_view_sql := l_view_sql || 'ECC_SPEC_ID,';
l_view_sql := l_view_sql || 'LANGUAGE, ';
l_view_sql := l_view_sql || 'ECC_LAST_UPDATE_DATE ';
OPEN c_base_attrs;
FETCH c_base_attrs BULK COLLECT INTO l_attrs_tbl,l_attr_vals_tbl;
FOR i IN 1..l_attrs_tbl.count
LOOP
l_attribute_name := l_attrs_tbl(i);
IF ( l_attribute_name = 'SIZE' ) THEN
l_attribute_name := l_attribute_name || '_';
END IF;
l_attr_list := l_attr_list || ' , ' || l_attribute_name;
l_attr_val_list := l_attr_val_list || ' , ' || l_attr_vals_tbl(i);
END LOOP;
CLOSE c_base_attrs;
OPEN c_base_attrs_tl;
FETCH c_base_attrs_tl BULK COLLECT INTO l_attrs_tbl,l_attr_vals_tbl;
FOR i IN 1..l_attrs_tbl.count
LOOP
l_attribute_name := l_attrs_tbl(i);
IF ( l_attribute_name = 'SIZE' ) THEN
l_attribute_name := l_attribute_name || '_';
END IF;
l_attr_list := l_attr_list || ' , ' || l_attribute_name;
l_attr_val_list := l_attr_val_list || ' , ' || l_attr_vals_tbl(i);
END LOOP;
CLOSE c_base_attrs_tl;
l_view_sql := l_view_sql || l_attr_list || ') AS ';
l_view_sql := l_view_sql || 'select items.inventory_item_id || ''#'' || items.po_line_id || ''#''
|| items.req_template_name || ''#'' ||items.req_template_line_num || ''#'' || items.org_id ECC_SPEC_ID,' ;
l_view_sql := l_view_sql || ' items.LANGUAGE, ';
l_view_sql := l_view_sql || ' GREATEST(items.last_update_date, NVL(PAV.LAST_UPDATE_DATE, items.last_update_date), NVL(items.last_update_date, PAV_TLP.LAST_UPDATE_DATE)) ECC_LAST_UPDATE_DATE ';
l_view_sql := l_view_sql || l_attr_val_list;
l_view_sql := l_view_sql || ' FROM ICX_CAT_ITEMS_CTX_HDRS_TLP ITEMS, ';
l_view_sql := l_view_sql || ' PO_ATTRIBUTE_VALUES PAV, ' || ' PO_ATTRIBUTE_VALUES_TLP PAV_TLP ';
l_view_sql := l_view_sql || ' WHERE items.inventory_item_id = PAV.inventory_item_id(+) ';
l_view_sql := l_view_sql || ' AND items.po_line_id = PAV.po_line_id(+) ';
l_view_sql := l_view_sql || ' AND items.req_template_name = PAV.req_template_name(+) ';
l_view_sql := l_view_sql || ' AND items.req_template_line_num = PAV.req_template_line_num(+) ';
l_view_sql := l_view_sql || ' AND items.org_id = PAV.org_id(+) ';
l_view_sql := l_view_sql || ' AND items.inventory_item_id = PAV_TLP.inventory_item_id(+) ';
l_view_sql := l_view_sql || ' AND items.po_line_id = PAV_TLP.po_line_id(+) ';
l_view_sql := l_view_sql || ' AND items.req_template_name = PAV_TLP.req_template_name(+) ';
l_view_sql := l_view_sql || ' AND items.req_template_line_num = PAV_TLP.req_template_line_num(+) ';
l_view_sql := l_view_sql || ' AND items.org_id = PAV_TLP.org_id(+) ';
l_view_sql := l_view_sql || ' AND items.language = PAV_TLP.language(+) ';
IF ( l_category_id <> 0 ) THEN
l_view_sql := l_view_sql || ' AND items.ip_category_id = ';
l_view_sql := l_view_sql || l_category_id;
END IF;
-- dbms_output.put_line(substr(l_view_sql, 0, 200));
-- dbms_output.put_line(substr(l_view_sql, 201, 200));
-- dbms_output.put_line(substr(l_view_sql, 401, 200));
-- dbms_output.put_line(substr(l_view_sql, 601, 200));
-- dbms_output.put_line(substr(l_view_sql, 801, 200));
EXECUTE IMMEDIATE l_view_sql;
END create_ecc_desc_view;
PROCEDURE create_ecc_desc_views
IS
l_cat_id_list dbms_sql.varchar2_table;
CURSOR c_categories
IS
SELECT DISTINCT rt_category_id
FROM icx_cat_attributes_tl
WHERE EXISTS
(SELECT 1 FROM icx_cat_items_ctx_hdrs_tlp WHERE ip_category_id=rt_category_id
UNION
SELECT 1 FROM icx_cat_punchout_items WHERE ip_category_id=rt_category_id
)
OR rt_category_id=0;
BEGIN
icx_ecc_util_pvt.drop_ecc_desc_views;
OPEN c_categories;
FETCH c_categories BULK COLLECT INTO l_cat_id_list;
CLOSE c_categories;
FOR i IN 1..l_cat_id_list.count
LOOP
icx_ecc_util_pvt.create_ecc_desc_view(l_cat_id_list(i) );
END LOOP;
END create_ecc_desc_views;
PROCEDURE drop_ecc_desc_views
IS
l_apps_schema_name varchar2(25) := 'APPS';
CURSOR c_ecc_views
IS
SELECT DISTINCT view_name
FROM all_views
WHERE view_name LIKE 'ICX_CAT_ECC%VL'
AND owner=l_apps_schema_name;
l_view_list dbms_sql.varchar2_table;
BEGIN
select oracle_username
into l_apps_schema_name
from fnd_oracle_userid
where read_only_flag = 'U';
OPEN c_ecc_views;
FETCH c_ecc_views BULK COLLECT INTO l_view_list;
CLOSE c_ecc_views;
FOR i IN 1..l_view_list.count
LOOP
EXECUTE IMMEDIATE 'drop view ' || l_view_list(i);
END LOOP;
END drop_ecc_desc_views;
PROCEDURE get_desc_metadata_load_info(
p_dataset_key IN VARCHAR2,
p_dataset_attrs IN ecc_sec_field_values DEFAULT NULL,
p_languages IN VARCHAR2 ,
x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_apps_schema_name varchar2(25);
l_desc_sql_text VARCHAR2(4000) ;
l_prec_sql_text VARCHAR2(4000) ;
l_prec_cat1_sql_text VARCHAR2(4000) := ' SELECT ' || '''SHOPPING_CATEGORY_1'' TRIGGER_INSTANCE_ATTRIBUTE, '
|| '''SHOPPING_CATEGORY_2'' TARGET_INSTANCE_ATTRIBUTE, ' || '''*'' TRIGGER_ATTR_VALUE ,' || '''Y'' ENABLED_FLAG '
|| ' FROM dual ';
l_prec_cat2_sql_text VARCHAR2(4000) := ' SELECT ' || '''SHOPPING_CATEGORY_2'' TRIGGER_INSTANCE_ATTRIBUTE, '
|| '''SHOPPING_CATEGORY_3'' TARGET_INSTANCE_ATTRIBUTE, ' || '''*'' TRIGGER_ATTR_VALUE ,' || '''Y'' ENABLED_FLAG '
|| ' FROM dual ';
l_prec_cat3_sql_text VARCHAR2(4000) := ' SELECT ' || '''SHOPPING_CATEGORY_3'' TRIGGER_INSTANCE_ATTRIBUTE, '
|| '''SHOPPING_CATEGORY'' TARGET_INSTANCE_ATTRIBUTE, ' || '''*'' TRIGGER_ATTR_VALUE ,' || '''Y'' ENABLED_FLAG '
|| ' FROM dual ';
query_det_arr ecc_query_det_arr_type := ecc_query_det_arr_type(NULL);
BEGIN
begin
select oracle_username
into l_apps_schema_name
from fnd_oracle_userid
where read_only_flag = 'U';
exception
when others then
l_apps_schema_name := 'APPS';
end;
l_desc_sql_text := 'SELECT distinct Decode(UPPER(regexp_replace(key,''[-*# ]'',''_'') ), ''SIZE'', ''SIZE_'', UPPER(regexp_replace(key,''[-*# ]'',''_'') )) NAME,
ATTRIBUTE_NAME DISPLAY_NAME,
Decode(SubStr(stored_in_column,1,3) , ''NUM'', ''NUMBER'', ''VARCHAR2'') TYPE,
NULL SCALE,
NULL PRECISION,
decode(rt_category_id, 0, ''Y'', ''N'') SHOW_IN_GUDIED_DISCOVERY,
''REFINEMENTS'' GROUP_KEY,
Decode(stored_in_TABLE , ''PO_ATTRIBUTE_VALUES_TLP'', ''Y'', ''N'') TRANSLATABLE,
LANGUAGE
FROM icx_cat_attributes_tl
WHERE stored_in_table IN (''PO_ATTRIBUTE_VALUES'', ''PO_ATTRIBUTE_VALUES_TLP'')
AND STORED_IN_COLUMN IS NOT NULL
AND KEY NOT IN (''PREFERRED_SUPPLIER_ICON'', ''ECO_FRIENDLY_SUPPLIER_ICON'', ''GREEN_SUPPLIER_ICON'', ''OFF_CONTRACT_ITEM_ICON'', ''OVER_PRICED_ICON'')
AND ATTRIBUTE_ID>28
AND LANGUAGE IN ( select COLUMN_VALUE from TABLE(FND_ECC_UTIL_MLS_PVT.GET_LANG_FOR_METADATA(''' || p_languages || ''')))';
l_prec_sql_text := ' SELECT ' || '''SHOPPING_CATEGORY_1'' TRIGGER_INSTANCE_ATTRIBUTE, '
|| ' UPPER(SubStrB(icx_ecc_util_pvt.makeNCName(ita.KEY), 1,30) ) TARGET_INSTANCE_ATTRIBUTE, '
|| '''*'' TRIGGER_ATTR_VALUE ,' || '''Y'' ENABLED_FLAG '
|| ' FROM icx_cat_attributes_tl ita, ' || 'fnd_languages lang '
|| ' WHERE lang.language_code= ita.LANGUAGE ' || ' AND lang.installed_flag =''B'''
|| ' AND STORED_IN_TABLE IN (''PO_ATTRIBUTE_VALUES'',''PO_ATTRIBUTE_VALUES_TLP'') '
|| ' AND STORED_IN_COLUMN like ''%ATTRIBUTE%'''
|| ' AND ita.rt_category_id <> 0 '
|| ' AND upper(icx_ecc_util_pvt.makeNCName(ita.KEY)) in '
|| ' (select column_name FROM all_tab_columns where table_name like ''ICX_CAT_ECC%VL'' and owner = ''' ||l_apps_schema_name || '''' || ' )'
|| ' GROUP BY ita.KEY';
icx_ecc_util_pvt.create_ecc_desc_views;
icx_endeca_util_pkg.seed_icon_descriptors;
query_det_arr(1) := ecc_query_det_rec(l_desc_sql_text,icx_ecc_util_pvt.g_icx_exc_att_op);
query_det_arr.extend ();
query_det_arr(2) := ecc_query_det_rec(l_prec_sql_text,icx_ecc_util_pvt.g_icx_exc_pre_op);
query_det_arr.extend ();
query_det_arr(3) := ecc_query_det_rec(l_prec_cat1_sql_text,icx_ecc_util_pvt.g_icx_exc_pre_op);
query_det_arr.extend ();
query_det_arr(4) := ecc_query_det_rec(l_prec_cat2_sql_text,icx_ecc_util_pvt.g_icx_exc_pre_op);
query_det_arr.extend ();
query_det_arr(5) := ecc_query_det_rec(l_prec_cat3_sql_text,icx_ecc_util_pvt.g_icx_exc_pre_op);
x_ecc_ds_meta_rec := ecc_ds_meta_rec(p_dataset_key,query_det_arr);
x_return_status := 'S';
END get_desc_metadata_load_info;
FUNCTION makencname(
p_attribute_name IN VARCHAR2 )
RETURN VARCHAR2
IS
l_attribute_name VARCHAR2(450);
BEGIN
l_attribute_name := p_attribute_name;
l_attribute_name := REPLACE(l_attribute_name,' ','_');
l_attribute_name := REPLACE(l_attribute_name,'/','_');
l_attribute_name := REPLACE(l_attribute_name,'#','_');
l_attribute_name := REPLACE(l_attribute_name,',','_');
IF ( upper(l_attribute_name) = 'SIZE' ) THEN
l_attribute_name := l_attribute_name || '_';
END IF;
RETURN l_attribute_name;
END makencname;
PROCEDURE SUBMIT_ECC_DATA_LOAD(
ERRBUF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY VARCHAR2 ,
p_system_name IN VARCHAR2,
p_load_type IN VARCHAR2,
p_languages IN VARCHAR2,
p_log_level IN VARCHAR2,
p_trace_enabled IN VARCHAR2
) IS
l_app_short_name varchar2(10) := 'icx';
l_request_id number;
l_req_data varchar2(10);
l_req_data1 varchar2(10);
l_load_type varchar2(200);
BEGIN
l_req_data := fnd_conc_global.request_data;
SELECT decode(p_load_type, 'FULL_LOAD', 'METADATA_LOAD', p_load_type) INTO l_load_type FROM dual;
IF l_req_data ='END' THEN
fnd_file.put_line(FND_FILE.OUTPUT,'Executed the sub request: ICX ECC Data Load for load type : ' || p_load_type);
fnd_file.put_line(FND_FILE.LOG,'Executed the sub request: ICX ECC Data Load for load type : ' || p_load_type);
RETURN;
END IF;
IF (l_req_data ='END-META' and p_load_type = 'FULL_LOAD') THEN
fnd_file.put_line(FND_FILE.OUTPUT,'Executed the sub request: ICX ECC Data Load for load type : ' || l_load_type);
fnd_file.put_line(FND_FILE.LOG,'Executed the sub request: ICX ECC Data Load for load type : ' || l_load_type);
l_request_id := fnd_request.submit_request(
application => 'FND',
program => 'ECCRUNDL',
description => 'ECC - Run Data Load',
start_time => sysdate,
argument1 => p_system_name,
argument2 => l_app_short_name,
argument3 => null,
argument4 => p_load_type,
argument5 => p_languages,
argument6 => p_trace_enabled,
argument7 => p_log_level,
sub_request => true);
fnd_file.put_line(FND_FILE.OUTPUT,'ECC Load for load type : ' || p_load_type || ' Job Request ID:'||l_request_id);
fnd_file.put_line(FND_FILE.LOG,'ECC Load for load type : ' || p_load_type || ' Job Request ID:'||l_request_id);
IF l_request_id = 0 THEN
--
-- If request submission failed, exit with error.
--
ERRBUF := fnd_message.get;
RETCODE := 2;
ELSE
--
-- Here we set the globals to put the program into the
-- PAUSED status on exit, and to save the state in
-- request_data.
--
fnd_conc_global.set_req_globals(conc_status => 'PAUSED',request_data => 'END');
ERRBUF := 'Sub-Request submitted!';
RETCODE := 0 ;
END IF;
RETURN;
END IF;
-- Submit l_load_type which can be either METADATA_LOAD or INCREMENTAL_LOAD.
l_request_id := fnd_request.submit_request(
application => 'FND',
program => 'ECCRUNDL',
description => 'ECC - Run Data Load',
start_time => sysdate,
argument1 => p_system_name,
argument2 => l_app_short_name,
argument3 => null,
argument4 => l_load_type,
argument5 => p_languages,
argument6 => p_trace_enabled,
argument7 => p_log_level,
sub_request => true);
fnd_file.put_line(FND_FILE.OUTPUT,'ECC Load for load type : ' || l_load_type || ' Job Request ID:'||l_request_id);
fnd_file.put_line(FND_FILE.LOG,'ECC Load for load type : ' || l_load_type || ' Job Request ID:'||l_request_id);
IF l_request_id = 0 THEN
--
-- If request submission failed, exit with error.
--
ERRBUF := fnd_message.get;
RETCODE := 2;
ELSE
--
-- Here we set the globals to put the program into the
-- PAUSED status on exit, and to save the state in
-- request_data.
--
IF(l_load_type = 'METADATA_LOAD' AND p_load_type='FULL_LOAD') THEN
l_req_data1 := 'END-META';
ELSE
l_req_data1 := 'END';
END IF;
fnd_conc_global.set_req_globals(conc_status => 'PAUSED',request_data => l_req_data1);
ERRBUF := 'Sub-Request submitted!';
RETCODE := 0 ;
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RETCODE := 1;
fnd_file.put_line(FND_FILE.OUTPUT,'OTHERS exception while submitting : ICX ECC Data Load' || sqlerrm);
fnd_file.put_line(FND_FILE.LOG,'OTHERS exception while submitting : ICX ECC Data Load' || sqlerrm);
END SUBMIT_ECC_DATA_LOAD;
END icx_ecc_util_pvt;
/
--show errors;
COMMIT;
EXIT;
Example from Order Management
CREATE OR REPLACE PACKAGE BODY APPS.OE_ECC_UTIL_PVT
AS
/* $Header: OEXUEXMP.pls 120.0.1 2019/07/22 06:14:03 noship $ */
/* Procedure GET_ECC_DATA_LOAD_INFO is the main procedure which is called during both full load and
** incremental load, for both ont-lines as well as ont-headers data sets.
**
** GET_ECC_DATA_LOAD_INFO - Retrieves the data load details for the given data set key
**
** IN parameters
** p_dataset_id - dataset key
** p_load_type - Indicating Full or incremental load (F/I/Custom).For custom there are no data load rules defined
** p_ds_last_success_run - Returns the last successful etl run for the dataset key
** OUT parameters
** x_ecc_ds_meta_rec - ecc_ds_meta_rec
** x_return_status - return status
*/
---------------------------------------------------------------------------------------------------
PROCEDURE GET_ECC_DATA_LOAD_INFO (
p_dataset_key IN VARCHAR2,
p_load_type IN VARCHAR2,
p_ds_last_success_run IN DATE,
p_languages IN VARCHAR2,
P_ADDL_PARAMS IN ECC_SEC_FIELD_VALUES DEFAULT NULL,
x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_masstrans_d_sql_text CLOB;
l_masstrans_i_sql_text CLOB;
l_ont_header_full_text VARCHAR2 (30000);
l_ont_header_hold_text VARCHAR2 (2500);
l_base_language VARCHAR2 (20);
l_line_del_sql_text VARCHAR2 (256)
:= 'select line_id ecc_spec_id from oe_ecc_deletion_map where line_id is not null';
l_hdr_del_sql_text VARCHAR2 (256)
:= 'select distinct header_id ecc_spec_id from oe_ecc_deletion_map where line_id is null';
v_for_lang_pivot_clause VARCHAR2 (400)
:= FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_PIVOT_FOR_LANG_CL (
p_languages);
l_ont_line_hold_text VARCHAR2 (4000)
:= 'SELECT * FROM
(SELECT DISTINCT oegt.line_id ecc_spec_id,
oegt.total_hold_name,
oegt.header_hold_name,
oegt.line_hold_name,
oegt.GRAPH_ALERT_NAME,
oegt.alert_text,
oegt.language_code
FROM oe_ecc_global_temp oegt
) pivot (max (GRAPH_ALERT_NAME) as GRAPH_ALERT_NAME,
max(alert_text) as alert_text
for language_code in ('
|| v_for_lang_pivot_clause
|| '))';
l_ont_full_sql_text VARCHAR2 (8000)
:= 'SELECT * FROM (SELECT
ECC_SPEC_ID,ECC_LAST_UPDATE_DATE,ORDER_NUMBER,LINE_NUMBER,LINE_ID,HEADER_ID,PRODUCT,LINE_QUANTITY,UNIT_SELLING_PRICE,
ORDER_UOM,PRODUCT_DESCRIPTION,LINE_TYPE,PRICE_LIST,PRICE_LIST_ID,ORDER_DATE,SHIP_FROM_ORG_ID,WAREHOUSE,SOURCE_TYPE,
SHIPMENT_METHOD,CARRIER,SHIPMENT_PRIORITY,SHIPPING_INSTRUCTIONS,PACKING_INSTRUCTIONS,FREIGHT_TERMS,SALESPERSON,CUSTOMER,
CUSTOMER_NUMBER,SHIP_TO_CUSTOMER_NAME,SHIP_TO_CUSTOMER_NUMBER,SHIP_TO_CONTACT,SHIP_TO_CONTACT_ID,SHIP_TO_ADDRESS,SHIP_TO_ORG_ID,
BILL_TO_CUSTOMER_NAME,BILL_TO_CUSTOMER_NUMBER,BILL_TO_CONTACT,BILL_TO_CONTACT_ID,BILL_TO_ADDRESS,BILL_TO_ORG_ID,EXPECTED_DELAY,
REQUEST_DATE,REQUEST_DATE_TYPE,SCHEDULE_SHIP_DATE,ACTUAL_SHIPMENT_DATE,SCHEDULE_ARRIVAL_DATE,ACTUAL_ARRIVAL_DATE,
LATEST_ACCEPTABLE_DATE,FULFILLMENT_DATE,FLOW_STATUS_CODE,LINE_STATUS,ITEM_TYPE_CODE,ORG_ID,OPERATING_UNIT,LINE_CATEGORY_CODE,
FULFILLED_FLAG,LINE_TRAN_AMOUNT,OPEN_FLAG,TXN_CURRENCY,TXN_CURRENCY_CODE,FUNC_CURRENCY_CODE,FUNC_CURRENCY,LINE_AMOUNT,
SHIP_TO_SITE,SHIP_TO_LOCATION,SHIP_TO_STATE,SHIP_TO_COUNTRY,BILL_TO_SITE,BILL_TO_CITY,BILL_TO_STATE,BILL_TO_COUNTRY,
LINE_LAST_UPDATE_DATE,PRIMARY_UOM,INVENTORY_ITEM_ID,SALES_CHANNEL,ORDER_TYPE,STATUS,SHIPPED_QUANTITY,FULFILLED_QUANTITY,
INVOICED_QUANTITY,SCHEDULE_STATUS,PROMISE_DATE,CUSTOMER_PO,PAYMENT_TERM,AGREEMENT_NAME,SALES_AGREEMENT_NUMBER,SALES_AGREEMENT_LINE_NUMBER,
SUBINVENTORY,PICK_STATUS,TRANSACTIONAL_ORDER_TOTAL,ORDER_TOTAL,RESERVED_QUANTITY,PARTY_ID,PREDICTED_RETURN_REASON_CODE,
ANOMALIES,LINE_HOLD_NAME,HEADER_HOLD_NAME,TOTAL_HOLD_NAME,ALERT_FLAG,ALERT_COUNT,ALERT_TEXT,GRAPH_ALERT_NAME,DELAY_FLAG,DELAY_COUNT,
CONVERSION_RATE,CONVERSION_TYPE_CODE,RESERVED_QTY,BOOKED_FLAG,ORDER_ALERT_COUNT,ALERT_TYPE,HDR_ALERT_COUNT,
LANGUAGE_CODE,ALERT_TYPE_CODE,TABLE_ALERT_FLAG,HDR_LAST_UPDATE_DATE,ON_ALERT,IS_OPEN,IS_BOOKED,
LINE_QUANTITY_1,SHIPPED_QUANTITY_1,CURRENCY,CSR_USER_NAME
FROM
OE_ECC_GLOBAL_TEMP )
PIVOT (
max(freight_terms) as freight_terms,
max(product_description) as product_description,
max(price_list) as price_list,
max(line_type) as line_type,
max(payment_term) as payment_term,
max(ship_to_contact) as ship_to_contact,
max(bill_to_contact) as bill_to_contact,
max(line_status) as line_status,
max(operating_unit) as operating_unit,
max(sales_channel) as sales_channel,
max(SHIPMENT_METHOD) as SHIPMENT_METHOD,
max(on_alert) as on_alert,
max(is_open) as is_open,
max(is_booked) as is_booked,
max(order_type) as order_type,
max(agreement_name) as agreement_name,
max(SOURCE_TYPE) as SOURCE_TYPE,
max(TXN_CURRENCY) as TXN_CURRENCY,
max(func_currency) as func_currency,
max(BILL_TO_COUNTRY) as BILL_TO_COUNTRY,
max(ship_to_country) as ship_to_country,
max(STATUS) as STATUS,
max(GRAPH_ALERT_NAME) as GRAPH_ALERT_NAME,
max(alert_text) as alert_text ,
max(warehouse) as warehouse,
max(alert_type) as alert_type
for language_code in ('
|| v_for_lang_pivot_clause
|| '))';
query_det_arr ecc_query_det_arr_type
:= ecc_query_det_arr_type (NULL);
CURSOR lines IS SELECT * FROM OE_ECC_GLOBAL_TEMP;
l_current_header_id NUMBER := 0;
l_current_total NUMBER := 0;
l_func_total NUMBER := 0;
l_hold_cnt NUMBER := 0;
l_anam_cnt NUMBER := 0;
l_reserved_qty2 NUMBER := 0;
l_return_status VARCHAR2 (200);
l_currency_multiple VARCHAR2 (200);
l_exists VARCHAR2 (1);
l_hdr_total_sql_text VARCHAR2 (2500);
l_ont_line_dff_text VARCHAR2 (2500);
l_ont_header_dff_text VARCHAR2 (2500);
l_alert_anomaly VARCHAR2 (200);
l_alert_delay VARCHAR2 (200);
l_alert_expected_delay VARCHAR2 (200);
l_alert_hold VARCHAR2 (200);
l_alert_predictive_alert VARCHAR2 (200);
l_alert_pred_return_reason VARCHAR2 (200);
l_alert_return_prediction VARCHAR2 (200);
l_operating_unit VARCHAR2 (100);
l_yes_label_meaning VARCHAR2 (20);
l_no_label_meaning VARCHAR2 (20);
l_hdr_hold_cnt NUMBER;
lines_to_process SYS_REFCURSOR;
l_ECC_BULK_LOAD_SIZE NUMBER
:= NVL (fnd_profile.Value_wnps ('OM_ECC_BULK_LOAD_SIZE'), 10000);
l_alert_type_tbl alert_type_tbl;
l_yes_no_tbl alert_type_tbl;
l_ecc_tbl ecc_temp_tbl_type;
l_full_ecc_tbl ecc_temp_tbl_type;
CURSOR alert_types IS
SELECT meaning, language, lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'ONT_ALERT_TYPE'
ORDER BY lookup_code;
CURSOR yes_no IS
SELECT meaning, language, lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'YES_NO'
AND view_application_id = 660
AND enabled_flag = 'Y'
ORDER BY lookup_code;
CURSOR request_date_type_cur IS
SELECT meaning, lookup_code
FROM oe_lookups
WHERE lookup_type = 'REQUEST_DATE_TYPE' AND enabled_flag = 'Y';
TYPE request_date_type_tbl IS TABLE OF request_date_type_cur%ROWTYPE
INDEX BY BINARY_INTEGER;
l_request_date_type_tbl request_date_type_tbl;
BEGIN
-- insert_debug('Inside GET_ECC_DATA_LOAD_INFO procedure');
-- set_db_trace('enable');
-- insert_debug('l_ECC_BULK_LOAD_SIZE:'||l_ECC_BULK_LOAD_SIZE);
-- insert_debug('p_dataset_key:'||p_dataset_key);
-- insert_debug('p_load_type:'||p_load_type);
-- insert_debug('p_ds_last_success_run:'||TO_CHAR(p_ds_last_success_run,'DD-MON-YYYY HH24:MI:SS'));
-- insert_debug('After truncating oe_ecc_global_temp');
-- first delete all the records from oe_ecc_deletion_map that are already processed.
-- delete from oe_ecc_deletion_map WHERE processed = 'Y'; commented. trying truncation instead.
-- get the meanings of alert types by opening the cursor
OPEN alert_types;
FETCH alert_types BULK COLLECT INTO l_alert_type_tbl;
CLOSE alert_types;
OPEN yes_no;
FETCH yes_no BULK COLLECT INTO l_yes_no_tbl;
CLOSE yes_no;
OPEN request_date_type_cur;
FETCH request_date_type_cur BULK COLLECT INTO l_request_date_type_tbl;
CLOSE request_date_type_cur;
IF p_dataset_key = 'ont-lines'
THEN
-- if data set is ont-lines, first truncate all the necessary temp tables.
trunc_temp_tables (p_load_type);
IF (p_load_type = 'FULL_LOAD')
THEN -- full load of ont-lines
OPEN lines_to_process FOR
SELECT *
FROM (-- Subquery Refactoring
WITH
requested_languages
AS
( SELECT REGEXP_SUBSTR (
p_languages,
'[^,]+',
1,
LEVEL) AS LANGUAGE_CODE
FROM DUAL
CONNECT BY REGEXP_SUBSTR (p_languages,
'[^,]+',
1,
LEVEL)
IS NOT NULL)
-- Actual select query
SELECT a.*
FROM oe_ecc_order_lines_v a,
requested_languages lang
WHERE a.open_flag = 'Y'
AND a.language_code IN
(lang.LANGUAGE_CODE)
AND org_id IS NOT NULL -- added to avoid count mismatch
UNION ALL
SELECT /*+ index(OE_ECC_ORDER_LINES_V.l OE_ORDER_LINES1_ENDECA_N1) */
a.*
FROM oe_ecc_order_lines_v a,
requested_languages lang
WHERE a.line_LAST_UPDATE_DATE >=
SYSDATE
- NVL (
FND_PROFILE.VALUE_WNPS (
'OM_ECC_FULL_LOAD_DAYS'),
90)
AND a.open_flag = 'N'
AND a.language_code IN
(lang.LANGUAGE_CODE)
AND a.org_id IS NOT NULL -- added to avoid count mismatch
)
ORDER BY header_id;
ELSIF (p_load_type = 'INCREMENTAL_LOAD')
THEN --incremental load of ont-lines
OPEN lines_to_process FOR
--SELECT /*+ index(oeol.l OE_ORDER_LINES1_ENDECA_N1) leading(oeol.l oeol.h) cardinality(oeol.l 10) */ *
--FROM oe_ecc_order_lines_v oeol
--WHERE ( ecc_last_update_date >= p_ds_last_success_run ) AND language_code in (
-- select regexp_substr(p_languages,'[^,]+', 1, level) AS LANGUAGE_CODE from dual
-- connect by regexp_substr(p_languages, '[^,]+', 1, level) is not null)
-- Subquery Refactoring
WITH
requested_languages
AS
( SELECT REGEXP_SUBSTR (
p_languages,
'[^,]+',
1,
LEVEL) AS LANGUAGE_CODE
FROM DUAL
CONNECT BY REGEXP_SUBSTR (p_languages,
'[^,]+',
1,
LEVEL)
IS NOT NULL)
-- Actual select query
SELECT /*+ index(oeol.h OE_ORDER_LINES6_ENDECA_N1) leading(oeol.h oeol.l) cardinality(oeol.l 10) */
oeol.*
FROM oe_ecc_order_lines_v oeol,
requested_languages lang
WHERE (hdr_last_update_date >= p_ds_last_success_run)
AND oeol.language_code IN (lang.LANGUAGE_CODE)
UNION
SELECT /*+ index(oeol.l OE_ORDER_LINES1_ENDECA_N1) leading(oeol.l oeol.h) cardinality(oeol.l 10) */
oeol.*
FROM oe_ecc_order_lines_v oeol,
requested_languages lang
WHERE (line_last_update_date >=
p_ds_last_success_run)
AND oeol.language_code IN (lang.LANGUAGE_CODE)
UNION
SELECT /*+ leading(oeol.tta oeol.l oeol.h) cardinality(oeol.l 10) */
oeol.*
FROM oe_ecc_order_lines_v oeol,
requested_languages lang
WHERE (line_type_last_update_date >=
p_ds_last_success_run)
AND oeol.language_code IN (lang.LANGUAGE_CODE)
UNION
SELECT /*+ index(h OE_ORDER_LINES4_ENDECA_N1) */
a.*
FROM oe_ecc_order_lines_v a,
oe_ordeR_holds_all h,
requested_languages lang
WHERE a.line_id = h.line_id
AND h.last_update_date >= p_ds_last_success_run
AND a.language_code IN (lang.LANGUAGE_CODE)
UNION
SELECT /*+ index(h OE_ORDER_LINES4_ENDECA_N1) */
a.*
FROM oe_ecc_order_lines_v a,
oe_ordeR_holds_all h,
requested_languages lang
WHERE a.header_id = h.header_id
AND h.last_update_date >= p_ds_last_success_run
AND a.language_code IN (lang.LANGUAGE_CODE)
UNION
SELECT /*+ index(p OE_PREDICTIONS_N2) */
a.*
FROM oe_ecc_order_lines_v a,
oe_predictions p,
requested_languages lang
WHERE a.line_id = p.line_id
AND p.last_update_date >= p_ds_last_success_run
AND a.language_code IN (lang.LANGUAGE_CODE);
END IF; -- load type is incremental load
LOOP -- fetch lines_to_process
FETCH lines_to_process
BULK COLLECT INTO l_ecc_tbl
LIMIT l_ECC_BULK_LOAD_SIZE;
EXIT WHEN l_ecc_tbl.COUNT = 0;
--insert_debug('l_ecc_tbl.COUNT is:'||l_ecc_tbl.COUNT);
FOR i IN l_ecc_tbl.FIRST .. l_ecc_tbl.LAST
LOOP
l_alert_anomaly := NULL;
l_alert_delay := NULL;
l_alert_expected_delay := NULL;
l_alert_hold := NULL;
l_alert_predictive_alert := NULL;
l_alert_pred_return_reason := NULL;
l_alert_return_prediction := NULL;
l_operating_unit := NULL;
l_yes_label_meaning := NULL;
l_no_label_meaning := NULL;
FOR j IN l_alert_type_tbl.FIRST .. l_alert_type_tbl.LAST
LOOP
IF l_alert_type_tbl (j).language =
l_ecc_tbl (i).language_code
THEN
IF l_alert_type_tbl (j).lookup_code = 'ANOMALY'
THEN
l_alert_anomaly :=
l_alert_type_tbl (j).meaning;
ELSIF l_alert_type_tbl (j).lookup_code = 'DELAY'
THEN
l_alert_delay := l_alert_type_tbl (j).meaning;
ELSIF l_alert_type_tbl (j).lookup_code =
'EXP_DELAY'
THEN
l_alert_expected_delay :=
l_alert_type_tbl (j).meaning;
ELSIF l_alert_type_tbl (j).lookup_code = 'HOLD'
THEN
l_alert_hold := l_alert_type_tbl (j).meaning;
ELSIF l_alert_type_tbl (j).lookup_code =
'PRED_ALERT'
THEN
l_alert_predictive_alert :=
l_alert_type_tbl (j).meaning;
ELSIF l_alert_type_tbl (j).lookup_code =
'PRE_RET_REASON'
THEN
l_alert_pred_return_reason :=
l_alert_type_tbl (j).meaning;
ELSIF l_alert_type_tbl (j).lookup_code =
'RETURN_PRED'
THEN
l_alert_return_prediction :=
l_alert_type_tbl (j).meaning;
END IF;
END IF;
END LOOP;
FOR j IN l_yes_no_tbl.FIRST .. l_yes_no_tbl.LAST
LOOP
IF l_yes_no_tbl (j).language =
l_ecc_tbl (i).language_code
THEN
IF l_yes_no_tbl (j).lookup_code = 'Y'
THEN
l_yes_label_meaning :=
l_yes_no_tbl (j).meaning;
ELSIF l_yes_no_tbl (j).lookup_code = 'N'
THEN
l_no_label_meaning :=
l_yes_no_tbl (j).meaning;
END IF;
END IF;
END LOOP;
-- convert line total into functional currency
IF l_ecc_tbl (i).FUNC_CURRENCY_CODE IS NULL
THEN
l_ecc_tbl (i).FUNC_CURRENCY_CODE :=
oe_upgrade_misc.get_sob_currency (
l_ecc_tbl (i).org_id);
IF l_ecc_tbl (i).FUNC_CURRENCY_CODE IS NOT NULL
THEN
SELECT cur.NAME
INTO l_ecc_tbl (i).FUNC_CURRENCY
FROM fnd_currencies_tl cur
WHERE cur.currency_code =
l_ecc_tbl (i).FUNC_CURRENCY_CODE
AND cur.LANGUAGE =
l_ecc_tbl (i).LANGUAGE_CODE; --'US';
END IF;
END IF;
IF l_ecc_tbl (i).flow_status_code IS NOT NULL
THEN
l_ecc_tbl (i).line_status :=
oe_ecc_util_pvt.Get_Line_Status (
l_ecc_tbl (i).line_id,
l_ecc_tbl (i).flow_status_code,
l_ecc_tbl (i).language_code);
END IF;
IF l_ecc_tbl (i).FUNC_CURRENCY IS NULL
AND l_currency_multiple IS NULL
THEN
BEGIN
SELECT MESSAGE_TEXT
INTO l_currency_multiple
FROM fnd_new_messages
WHERE message_name =
'ONT_ENDECA_MULTIPLE_CURRENCIES';
EXCEPTION
WHEN OTHERS
THEN
l_currency_multiple := NULL;
END;
l_ecc_tbl (i).FUNC_CURRENCY := l_currency_multiple;
ELSIF l_ecc_tbl (i).FUNC_CURRENCY IS NULL
AND l_currency_multiple IS NOT NULL
THEN
l_ecc_tbl (i).FUNC_CURRENCY := l_currency_multiple;
END IF;
IF NVL (l_ecc_tbl (i).TXN_CURRENCY_CODE, 'abc') <>
NVL (l_ecc_tbl (i).FUNC_CURRENCY_CODE, 'abc')
THEN
OE_UPGRADE_MISC.CONVERT_CURRENCY (
l_ecc_tbl (i).line_tran_amount,
l_ecc_tbl (i).TXN_CURRENCY_CODE,
l_ecc_tbl (i).FUNC_CURRENCY_CODE,
l_ecc_tbl (i).order_date,
NULL,
'Corporate',
l_return_status,
l_ecc_tbl (i).line_amount);
ELSE
l_ecc_tbl (i).line_amount :=
l_ecc_tbl (i).line_tran_amount;
END IF;
FOR j IN l_request_date_type_tbl.FIRST ..
l_request_date_type_tbl.LAST
LOOP
IF l_ecc_tbl (i).request_date_type =
l_request_date_type_tbl (j).lookup_code
THEN
l_ecc_tbl (i).request_date_type :=
l_request_date_type_tbl (j).meaning;
END IF;
END LOOP;
-- insert_debug('l_ecc_tbl(i).line_amount:'||l_ecc_tbl(i).line_amount);
-- insert_debug('l_current_header_id:'||l_current_header_id);
-- insert_debug('l_ecc_tbl(i).header_id:'||l_ecc_tbl(i).header_id);
-- insert_debug('l_ecc_tbl(i).order_total:'||l_func_total);
-- insert_debug('l_ecc_tbl(i).transactional_order_total:'||l_ecc_tbl(i).transactional_order_total);
-- derive total holds
BEGIN
SELECT LISTAGG (holds_tbl.HOLD_NAME, '|')
WITHIN GROUP (ORDER BY holds_tbl.line_id) total_hold_name
INTO l_ecc_tbl (i).total_hold_name
FROM ( SELECT /*+ QB_NAME(HOLD_APPLY_INFO_SELECT2) */
LISTAGG (hdf.name, '|')
WITHIN GROUP (ORDER BY l.line_id)
HOLD_NAME,
l.line_id
line_id
FROM oe_order_lines_all l,
oe_order_holds_all h,
oe_hold_sources_all hsr,
oe_hold_definitions hdf,
oe_lookups oel
WHERE h.header_id = l.header_id
AND l.line_category_code = 'ORDER'
AND h.released_flag = 'N'
AND h.hold_source_id =
hsr.hold_source_id
AND hdf.hold_id = hsr.hold_id
AND oel.lookup_type = 'HOLD_TYPE'
AND oel.lookup_code = hdf.type_code
AND l.header_id =
l_ecc_tbl (i).header_id
AND ( h.line_id IS NULL
OR h.line_id =
l_ecc_tbl (i).line_id)
GROUP BY l.line_id) holds_tbl
WHERE line_id = l_ecc_tbl (i).line_id
GROUP BY line_id;
--insert_debug('After total holds query:'||l_ecc_tbl(i).total_hold_name);
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('in total holds exception');
l_ecc_tbl (i).total_hold_name := NULL;
END;
-- derive header holds
BEGIN
SELECT LISTAGG (holds_tbl.HOLD_NAME, '|')
WITHIN GROUP (ORDER BY holds_tbl.line_id) header_hold_name
INTO l_ecc_tbl (i).header_hold_name
FROM ( SELECT LISTAGG (hdf.name, '|')
WITHIN GROUP (ORDER BY lin.line_id)
HOLD_NAME,
lin.line_id
line_id
FROM oe_order_headers_all hdr,
oe_order_lines_all lin,
oe_order_holds_all oha,
oe_hold_sources_all hsr,
oe_hold_definitions hdf,
oe_lookups oel
WHERE hdr.open_flag = 'Y'
AND oha.hold_source_id =
hsr.hold_source_id
AND hsr.hold_id = hdf.hold_id
AND oha.header_id = hdr.header_id
AND oha.released_flag = 'N'
AND hdr.header_id = lin.header_id
AND lin.line_category_code = 'ORDER'
AND oha.line_id IS NULL
AND oel.lookup_type = 'HOLD_TYPE'
AND oel.lookup_code = hdf.type_code
AND hdr.header_id =
l_ecc_tbl (i).header_id
GROUP BY lin.line_id) holds_tbl
WHERE line_id = l_ecc_tbl (i).line_id
GROUP BY line_id;
--insert_debug('in header holds :'||l_ecc_tbl(i).header_hold_name);
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('in header holds exception');
l_ecc_tbl (i).header_hold_name := NULL;
END;
-- derive line holds
BEGIN
SELECT /*+ QB_NAME(HOLD_APPLY_INFO_SELECT2) */
LISTAGG (hdf.name, '|')
WITHIN GROUP (ORDER BY l.line_id) HOLD_NAME
INTO l_ecc_tbl (i).line_hold_name
FROM oe_order_lines_all l,
oe_order_holds_all h,
oe_hold_sources_all hsr,
oe_hold_definitions hdf,
oe_lookups oel
WHERE h.header_id = l.header_id
AND l.line_id = h.line_id
AND l.line_category_code = 'ORDER'
AND h.released_flag = 'N'
AND h.hold_source_id = hsr.hold_source_id
AND hdf.hold_id = hsr.hold_id
AND oel.lookup_type = 'HOLD_TYPE'
AND oel.lookup_code = hdf.type_code
AND l.line_id = l_ecc_tbl (i).line_id
GROUP BY l.line_id;
--insert_debug('in line holds:'||l_ecc_tbl(i).line_hold_name);
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('in line holds exception');
l_ecc_tbl (i).line_hold_name := NULL;
END;
-- derive line level anomalies
BEGIN
SELECT LISTAGG (ol.meaning, '|')
WITHIN GROUP (ORDER BY op.line_id) anomalies
INTO l_ecc_tbl (i).anomalies
FROM fnd_lookup_values ol, oe_predictions op
WHERE op.anomaly_code = ol.lookup_code
AND ol.lookup_type = 'ONT_ANOMALY_CODES'
AND ol.language = l_ecc_tbl (i).LANGUAGE_CODE --'US'//saadepu
AND op.line_id = l_ecc_tbl (i).line_id;
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('in Anomalies exception');
l_ecc_tbl (i).anomalies := NULL;
END;
-- derive return predictions
BEGIN
SELECT ol.meaning --PREDICTED_RETURN_REASON_CODE
INTO l_ecc_tbl (i).PREDICTED_RETURN_REASON_CODE
FROM fnd_lookup_values ol, oe_predictions op
WHERE op.predicted_return_reason_code =
ol.lookup_code
AND ol.lookup_type = 'ONT_PRED_RETURN_REASON'
AND ol.language = l_ecc_tbl (i).LANGUAGE_CODE --'US'//saadepu
AND op.line_id = l_ecc_tbl (i).line_id;
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('in PREDICTED_RETURN_REASON_CODE exception');
l_ecc_tbl (i).PREDICTED_RETURN_REASON_CODE :=
NULL;
END;
--insert_debug('l_ecc_tbl(i).LINE_ID:'||l_ecc_tbl(i).LINE_ID);
--insert_debug('l_ecc_tbl(i).SHIP_FROM_ORG_ID:'||l_ecc_tbl(i).SHIP_FROM_ORG_ID);
OE_LINE_UTIL.Get_Reserved_Quantities (
p_header_id => l_ecc_tbl (i).header_id,
p_line_id => l_ecc_tbl (i).LINE_ID,
p_org_id => l_ecc_tbl (i).SHIP_FROM_ORG_ID,
x_reserved_quantity => l_ecc_tbl (i).reserved_qty,
x_reserved_quantity2 => l_reserved_qty2);
--insert_debug('l_ecc_tbl(i).reserved_qty:'||l_ecc_tbl(i).reserved_qty);
--insert_debug('l_ecc_tbl(i).ANOMALIES:'||l_ecc_tbl(i).ANOMALIES);
--insert_debug('l_ecc_tbl(i).PREDICTED_RETURN_REASON_CODE:'||l_ecc_tbl(i).PREDICTED_RETURN_REASON_CODE);
--insert_debug('l_ecc_tbl(i).total_hold_name:'||l_ecc_tbl(i).total_hold_name);
--insert_debug('l_ecc_tbl(i).header_hold_name:'||l_ecc_tbl(i).header_hold_name);
--insert_debug('l_ecc_tbl(i).line_hold_name:'||l_ecc_tbl(i).line_hold_name);
IF l_ecc_tbl (i).expected_delay <= 0
THEN -- negative expected delay essentially means no delay
l_ecc_tbl (i).expected_delay := NULL;
END IF;
IF ( l_ecc_tbl (i).ANOMALIES IS NOT NULL
OR l_ecc_tbl (i).PREDICTED_RETURN_REASON_CODE
IS NOT NULL
OR l_ecc_tbl (i).total_hold_name IS NOT NULL
OR ( l_ecc_tbl (i).expected_delay IS NOT NULL
AND l_ecc_tbl (i).expected_delay > 0
AND NVL (l_ecc_tbl (i).shipped_quantity, 0) =
0))
AND l_ecc_tbl (i).open_flag = 'Y'
THEN
l_ecc_tbl (i).alert_flag := 'Y';
l_ecc_tbl (i).table_alert_flag := 'ecc_warning';
ELSE
l_ecc_tbl (i).alert_flag := 'N';
l_ecc_tbl (i).table_alert_flag := 'ecc_blank';
END IF;
--insert_debug('l_ecc_tbl(i).alert_flag:'||l_ecc_tbl(i).alert_flag);
l_ecc_tbl (i).alert_count := 0;
IF l_ecc_tbl (i).anomalies IS NOT NULL
THEN
l_anam_cnt := 0;
SELECT REGEXP_COUNT (l_ecc_tbl (i).anomalies, ',')
+ 1
INTO l_anam_cnt
FROM DUAL;
l_ecc_tbl (i).alert_count :=
l_ecc_tbl (i).alert_count + l_anam_cnt;
l_ecc_tbl (i).alert_type :=
l_ecc_tbl (i).alert_type
|| l_alert_predictive_alert; --l_alert_type_tbl(5).meaning; --'Predictive Alerts';
l_ecc_tbl (i).alert_type_code := 'PA'; --'Predictive Alerts';
l_ecc_tbl (i).alert_text := l_ecc_tbl (i).anomalies; --(Anomaly:Quantity Anomaly)
l_ecc_tbl (i).graph_alert_name := l_alert_anomaly; --l_alert_type_tbl(1).meaning; --l_ecc_tbl(i).anomalies;
END IF;
IF l_ecc_tbl (i).PREDICTED_RETURN_REASON_CODE IS NOT NULL
THEN
l_ecc_tbl (i).alert_count :=
l_ecc_tbl (i).alert_count + 1;
IF l_ecc_tbl (i).alert_type IS NOT NULL
THEN
l_ecc_tbl (i).alert_type :=
l_ecc_tbl (i).alert_type
|| '|'
|| l_alert_predictive_alert; --l_alert_type_tbl(5).meaning; --Predictive Alerts
l_ecc_tbl (i).alert_type_code := 'PA'; --Predictive Alerts
ELSE
l_ecc_tbl (i).alert_type :=
l_alert_predictive_alert; --l_alert_type_tbl(5).meaning;
l_ecc_tbl (i).alert_type_code := 'PA';
END IF; --l_ecc_tbl(i).alert_type IS NOT NULL
IF l_ecc_tbl (i).alert_text IS NOT NULL
THEN
l_ecc_tbl (i).alert_text :=
l_ecc_tbl (i).alert_text
|| '|'
|| l_alert_pred_return_reason --l_alert_type_tbl(6).meaning
|| ': '
|| l_ecc_tbl (i).PREDICTED_RETURN_REASON_CODE;
ELSE
l_ecc_tbl (i).alert_text :=
l_alert_pred_return_reason --l_alert_type_tbl(6).meaning
|| ': '
|| l_ecc_tbl (i).PREDICTED_RETURN_REASON_CODE;
END IF; --l_ecc_tbl(i).alert_text IS NOT NULL
IF l_ecc_tbl (i).graph_alert_name IS NOT NULL
THEN
l_ecc_tbl (i).graph_alert_name :=
l_ecc_tbl (i).graph_alert_name
|| '|'
|| l_alert_return_prediction; --l_alert_type_tbl(7).meaning; --Return Prediction
ELSE
l_ecc_tbl (i).graph_alert_name :=
l_alert_return_prediction; --l_alert_type_tbl(7).meaning; --return prediction--l_ecc_tbl(i).PREDICTED_RETURN_REASON_CODE;
END IF;
END IF; --l_ecc_tbl(i).PREDICTED_RETURN_REASON_CODE IS NOT NULL
/*hdr_alert_count stores the number of holds at header level*/
IF l_ecc_tbl (i).header_hold_name IS NOT NULL
THEN
l_hdr_hold_cnt := 0;
SELECT REGEXP_COUNT (
l_ecc_tbl (i).header_hold_name,
'\|')
+ 1
INTO l_hdr_hold_cnt
FROM DUAL;
l_ecc_tbl (i).hdr_alert_count :=
NVL (l_ecc_tbl (i).hdr_alert_count, 0)
+ l_hdr_hold_cnt;
--insert_debug('After hdr_alert_count check l_hdr_hold_cnt is:'||l_hdr_hold_cnt);
--insert_debug('After hdr_alert_count check count is:'||l_ecc_tbl(i).hdr_alert_count);
END IF; -- l_ecc_tbl(i).header_hold_name IS NOT NULL
IF l_ecc_tbl (i).line_hold_name IS NOT NULL
THEN
l_hold_cnt := 0;
SELECT REGEXP_COUNT (l_ecc_tbl (i).line_hold_name,
'\|')
+ 1
INTO l_hold_cnt
FROM DUAL;
--insert_debug('After line_alert_count check l_hold_cnt is:'||l_hold_cnt);
l_ecc_tbl (i).alert_count :=
l_ecc_tbl (i).alert_count + l_hold_cnt;
--insert_debug('After line_alert_count check count is:'||l_ecc_tbl(i).alert_count);
IF l_ecc_tbl (i).alert_text IS NOT NULL
THEN
l_ecc_tbl (i).alert_text :=
l_ecc_tbl (i).alert_text
|| '|'
|| l_alert_hold --l_alert_type_tbl(4).meaning
|| ': '
|| l_ecc_tbl (i).line_hold_name;
ELSE
l_ecc_tbl (i).alert_text :=
l_alert_hold --l_alert_type_tbl(4).meaning
|| ': '
|| l_ecc_tbl (i).line_hold_name;
END IF; --l_ecc_tbl(i).alert_text IS NOT NULL
END IF; -- l_ecc_tbl(i).line_hold_name IS NOT NULL
IF l_ecc_tbl (i).total_hold_name IS NOT NULL
THEN
IF l_ecc_tbl (i).alert_type IS NOT NULL
THEN
l_ecc_tbl (i).alert_type :=
l_ecc_tbl (i).alert_type
|| '|'
|| l_alert_hold; --l_alert_type_tbl(4).meaning;
ELSE
l_ecc_tbl (i).alert_type := l_alert_hold; --l_alert_type_tbl(4).meaning;
END IF; -- l_ecc_tbl(i).alert_type IS NOT NULL
END IF;
IF l_ecc_tbl (i).total_hold_name IS NOT NULL
THEN
IF l_ecc_tbl (i).graph_alert_name IS NOT NULL
THEN
l_ecc_tbl (i).graph_alert_name :=
l_ecc_tbl (i).graph_alert_name
|| '|'
|| l_ecc_tbl (i).total_hold_name;
ELSE
l_ecc_tbl (i).graph_alert_name :=
l_ecc_tbl (i).total_hold_name;
END IF;
END IF;
IF l_ecc_tbl (i).expected_delay IS NOT NULL
AND l_ecc_tbl (i).expected_delay > 0
AND NVL (l_ecc_tbl (i).shipped_quantity, 0) = 0
THEN
l_ecc_tbl (i).alert_count :=
l_ecc_tbl (i).alert_count + 1;
IF l_ecc_tbl (i).alert_type IS NOT NULL
THEN
l_ecc_tbl (i).alert_type :=
l_ecc_tbl (i).alert_type
|| '|'
|| l_alert_expected_delay; --l_alert_type_tbl(3).meaning;
ELSE
l_ecc_tbl (i).alert_type :=
l_alert_expected_delay; --l_alert_type_tbl(3).meaning;
END IF; -- l_ecc_tbl(i).alert_type IS NOT NULL
IF l_ecc_tbl (i).alert_text IS NOT NULL
THEN
l_ecc_tbl (i).alert_text :=
l_ecc_tbl (i).alert_text
|| '|'
|| l_alert_expected_delay; --l_alert_type_tbl(3).meaning;
ELSE
l_ecc_tbl (i).alert_text :=
l_alert_expected_delay; --l_alert_type_tbl(3).meaning;
END IF; -- l_ecc_tbl(i).alert_text IS NOT NULL.
IF l_ecc_tbl (i).graph_alert_name IS NOT NULL
THEN
l_ecc_tbl (i).graph_alert_name :=
l_ecc_tbl (i).graph_alert_name
|| '|'
|| l_alert_expected_delay; --l_alert_type_tbl(3).meaning;
ELSE
l_ecc_tbl (i).graph_alert_name :=
l_alert_expected_delay; --l_alert_type_tbl(3).meaning;
END IF;
END IF; -- end if for expected delay is not null condition
--insert_debug('l_ecc_tbl(i).alert_count:'||l_ecc_tbl(i).alert_count);
--insert_debug('l_ecc_tbl(i).expected_delay:'||l_ecc_tbl(i).expected_delay);
IF ( l_ecc_tbl (i).expected_delay IS NOT NULL
AND l_ecc_tbl (i).expected_delay > 0
AND NVL (l_ecc_tbl (i).shipped_quantity, 0) = 0)
THEN
l_ecc_tbl (i).delay_flag := 'Y';
l_ecc_tbl (i).delay_count := 0;
IF ( l_ecc_tbl (i).expected_delay IS NOT NULL
AND l_ecc_tbl (i).expected_delay > 0)
THEN
l_ecc_tbl (i).delay_count :=
l_ecc_tbl (i).delay_count + 1;
END IF;
END IF;
IF l_ecc_tbl (i).csr_user_id IS NOT NULL
THEN
BEGIN
SELECT p.full_name
INTO l_ecc_tbl (i).CSR_USER_NAME
FROM per_all_people_f p, fnd_user u
WHERE p.person_id = u.employee_id
AND TRUNC (SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
AND u.user_id = l_ecc_tbl (i).csr_user_id;
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('exception while fetching CSR_USER_NAME ');
l_ecc_tbl (i).CSR_USER_NAME := NULL;
END;
END IF;
--insert_debug('l_ecc_tbl(i).delay_flag:'||l_ecc_tbl(i).delay_flag );
--insert_debug('l_ecc_tbl(i).delay_count:'||l_ecc_tbl(i).delay_count);
IF (p_load_type = 'INCREMENTAL_LOAD')
THEN -- incremental load of ont-lines
-- first check if record already exists. if record already exists, just update the alert count and total
-- for that record.
/*BEGIN
SELECT 'Y'
INTO l_exists
FROM oe_ecc_header_totals
WHERE line_id=l_ecc_tbl(i).line_id;*/
UPDATE oe_ecc_header_totals
SET header_id = l_ecc_tbl (i).header_id,
line_id = l_ecc_tbl (i).line_id,
alert_count = l_ecc_tbl (i).alert_count,
hdr_alert_count =
l_ecc_tbl (i).hdr_alert_count,
tran_amount = l_ecc_tbl (i).line_tran_amount,
func_amount = l_ecc_tbl (i).line_amount,
language_code = l_ecc_tbl (i).language_code
WHERE line_id = l_ecc_tbl (i).line_id
AND language_code =
l_ecc_tbl (i).language_code;
IF SQL%ROWCOUNT = 0
THEN
INSERT INTO oe_ecc_header_totals (
HEADER_ID,
LINE_ID,
ALERT_COUNT,
HDR_ALERT_COUNT,
tran_amount,
func_amount,
language_code
)
VALUES (l_ecc_tbl (i).header_id,
l_ecc_tbl (i).line_id,
l_ecc_tbl (i).alert_count,
l_ecc_tbl (i).hdr_alert_count,
l_ecc_tbl (i).line_tran_amount,
l_ecc_tbl (i).line_amount,
l_ecc_tbl (i).language_code
);
END IF;
DELETE FROM oe_ecc_header_totals
WHERE line_id IN (SELECT line_id
FROM oe_ecc_deletion_map
WHERE line_id IS NOT NULL);
/*EXCEPTION
WHEN NO_DATA_FOUND THEN
-- line does not exist already.Looks like line got created.So insert into oe_ecc_header_totals
END ;*/
-- also mark the header into oe_ecc_headers table, for headers data set to pick the records and process.
IF l_current_header_id <> l_ecc_tbl (i).header_id
THEN
-- added the IF condition to minimize the number of inserts into temp table.
INSERT INTO oe_ecc_headers (HEADER_ID)
VALUES (l_ecc_tbl (i).header_id);
l_current_header_id := l_ecc_tbl (i).header_id;
END IF;
END IF;
-- set the lookup based values for open, booked and alert flags
IF NVL (l_ecc_tbl (i).alert_flag, NULL) = 'Y'
THEN
l_ecc_tbl (i).on_alert := l_yes_label_meaning; --l_yes_no_tbl(2).meaning;
ELSE
l_ecc_tbl (i).on_alert := l_no_label_meaning; --l_yes_no_tbl(1).meaning;
END IF;
IF NVL (l_ecc_tbl (i).open_flag, NULL) = 'Y'
THEN
l_ecc_tbl (i).is_open := l_yes_label_meaning; --l_yes_no_tbl(2).meaning;
ELSE
l_ecc_tbl (i).is_open := l_no_label_meaning; --l_yes_no_tbl(1).meaning;
END IF;
IF NVL (l_ecc_tbl (i).booked_flag, NULL) = 'Y'
THEN
l_ecc_tbl (i).is_booked := l_yes_label_meaning; --l_yes_no_tbl(2).meaning;
ELSE
l_ecc_tbl (i).is_booked := l_no_label_meaning; --l_yes_no_tbl(1).meaning;
END IF;
END LOOP; -- inner loop for processing records bulk collected.
FORALL idx IN INDICES OF l_ecc_tbl
INSERT INTO oe_ecc_global_temp
VALUES l_ecc_tbl (idx);
COMMIT;
/*if it is full load, insert all the record totals into oe_ecc_header_totals table */
IF p_load_type = 'FULL_LOAD'
THEN -- full load of ont-lines
l_full_ecc_tbl := l_ecc_tbl;
FOR j IN l_full_ecc_tbl.FIRST .. l_full_ecc_tbl.LAST
LOOP
IF l_full_ecc_tbl (j).open_flag = 'N'
THEN
l_full_ecc_tbl (j).line_amount := 0;
END IF;
END LOOP;
FORALL idx IN INDICES OF l_ecc_tbl
INSERT INTO oe_ecc_header_totals
VALUES (l_full_ecc_tbl (idx).header_id,
l_full_ecc_tbl (idx).line_id,
l_full_ecc_tbl (idx).alert_count,
l_full_ecc_tbl (idx).hdr_alert_count,
l_full_ecc_tbl (idx).line_tran_amount,
l_full_ecc_tbl (idx).line_amount,
l_full_ecc_tbl (idx).language_code
);
DELETE FROM oe_ecc_header_totals
WHERE line_id IN (SELECT line_id
FROM oe_ecc_deletion_map
WHERE line_id IS NOT NULL);
END IF;
COMMIT;
--insert_debug('After inserting into temp table');
l_ecc_tbl.DELETE ();
--insert_debug('After deleting data from plsql table. Count is:'||l_ecc_tbl.count);
END LOOP; -- outer loop for bulk collect.
SELECT LANGUAGE_CODE
INTO l_base_language
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B';
--update order totals at one go
UPDATE oe_ecc_global_temp oegt
SET (oegt.transactional_order_total, oegt.order_total) =
(SELECT SUM (oeh1.tran_amount), SUM (oeh1.func_amount)
FROM oe_ecc_header_totals oeh1
WHERE oegt.header_id = oeh1.header_id
AND oeh1.language_code = l_base_language
);
query_det_arr.EXTEND (1);
query_det_arr (1) :=
ecc_query_det_rec (l_ont_full_sql_text, G_ONT_ECC_UPS_OP);
IF (p_load_type = 'INCREMENTAL_LOAD')
THEN
query_det_arr.EXTEND (2);
query_det_arr (2) :=
ecc_query_det_rec (l_line_del_sql_text, G_ONT_ECC_DEL_OP);
-- query_det_arr.extend(3);
-- query_det_arr(3) := ecc_query_det_rec(l_ont_line_hold_text,G_ONT_ECC_RPL_OP);
END IF;
x_ecc_ds_meta_rec :=
ecc_ds_meta_rec (p_dataset_key, query_det_arr);
x_return_status := 'S';
ELSIF p_dataset_key = 'ont-headers'
THEN
IF p_load_type = 'FULL_LOAD'
THEN -- full load of ont-headers
--insert_debug('Full load for headers data set');
l_ont_header_full_text :=
' SELECT * FROM (
SELECT order_number,customer,customer_number,ship_to_location, order_date, status, order_alert_count,
order_alert_flag,ecc_spec_id,header_id,header_hold_name,customer_po,request_date,order_type,
org_id,order_currency,language_code,
(SELECT p.full_name FROM per_all_people_f p, fnd_user u WHERE p.person_id = u.employee_id AND
trunc(sysdate) between p.effective_start_date and p.effective_end_date
AND u.user_id = csr_user_id) csr_user_name
FROM
(
SELECT oegt.order_number,
oegt.customer,
oegt.customer_number,
ship_su.LOCATION ship_to_location,
oegt.order_date,
oegt.status,
SUM (oegt.alert_count) + MAX(nvl(oegt.hdr_alert_count,0)) order_alert_count,
DECODE (SUM (oegt.alert_count) + MAX(nvl(oegt.hdr_alert_count,0)),0,''ecc_blank'',''ecc_warning'') order_alert_flag,
oegt.header_id ecc_spec_id,
oegt.header_id,
oegt.header_hold_name,
oh.cust_po_number customer_po,
oh.request_date ,
oegt.order_type,
oegt.org_id,
oegt.func_currency order_currency,
oh.csr_user_id,
oegt.language_code language_code
FROM
oe_ecc_global_temp oegt,
oe_order_headers_all oh,
hz_cust_site_uses_all ship_su
WHERE
oh.header_id =oegt.header_id AND
oh.ship_to_org_id = ship_su.site_use_id(+) AND
oegt.org_id IS NOT NULL
GROUP BY
oegt.header_id,
oegt.order_number,
oegt.customer,
oegt.customer_number,
oegt.order_date,
oegt.status,
ship_su.LOCATION,
oegt.header_hold_name,
oh.cust_po_number,
oh.request_date ,
oegt.hdr_alert_count,
oegt.order_type,
oegt.org_id,
oegt.func_currency,
oh.csr_user_id,
oegt.language_code)
)
PIVOT (
max(STATUS) as STATUS,
max(ORDER_CURRENCY) as ORDER_CURRENCY,
max(ORDER_TYPE) as ORDER_TYPE
for language_code in ('
|| v_for_lang_pivot_clause
|| '))';
l_hdr_total_sql_text :=
'select
oh.header_id ecc_spec_id,
oe_ecc_util_pvt.get_order_total(oh.header_id,
oh.transactional_curr_code,
oh.org_id,
fnd_profile.Value(''OM_ECC_DISPLAY_CURRENCY''),
oh.ordered_date,
null,
''Corporate''
) order_total
from
oe_order_headers_all oh
where
oh.header_id in
(select distinct header_id from oe_ecc_global_temp)';
query_det_arr.EXTEND (1);
query_det_arr (1) :=
ecc_query_det_rec (l_ont_header_full_text,
G_ONT_ECC_UPS_OP);
query_det_arr.EXTEND (2);
query_det_arr (2) :=
ecc_query_det_rec (l_hdr_total_sql_text,
G_ONT_ECC_UPS_OP);
x_ecc_ds_meta_rec :=
ecc_ds_meta_rec (p_dataset_key, query_det_arr);
x_return_status := 'S';
ELSIF (p_load_type = 'INCREMENTAL_LOAD')
THEN --incremental load of ont-headers
l_ont_header_full_text :=
'SELECT * FROM (
SELECT order_number,customer,customer_number,ship_to_location, order_date, status, order_alert_count,
order_alert_flag,ecc_spec_id,header_id,header_hold_name,customer_po,request_date,order_type,
org_id,order_currency,language_code,
(SELECT p.full_name FROM per_all_people_f p, fnd_user u WHERE p.person_id = u.employee_id AND
trunc(sysdate) between p.effective_start_date and p.effective_end_date
AND u.user_id = csr_user_id) csr_user_name
FROM
(
SELECT oegt.order_number ,
oegt.customer,
oegt.customer_number,
ship_su.LOCATION ship_to_location,
oegt.order_date, oegt.status,
SUM (oeht.alert_count) + MAX(NVL(oeht.hdr_alert_count,0)) order_alert_count,
DECODE (SUM (oegt.alert_count) + MAX(NVL(oeht.hdr_alert_count,0)),0,''ecc_blank'',''ecc_warning'') order_alert_flag,
oegt.header_id ecc_spec_id,
oegt.header_id,
oegt.header_hold_name,
oh.cust_po_number customer_po,
oh.request_date,
oegt.order_type,
oegt.org_id,
oegt.func_currency order_currency,
oh.csr_user_id,
oegt.language_code
FROM
oe_ecc_global_temp oegt,
oe_ecc_header_totals oeht,
oe_order_headers_all oh,
hz_cust_site_uses_all ship_su
WHERE
oegt.line_id =oeht.line_id AND
oegt.header_id =oh.header_id AND
oeht.header_id =oh.header_id AND
oh.ship_to_org_id = ship_su.site_use_id(+) AND
oegt.header_id IN
(SELECT header_id FROM oe_ecc_headers
UNION
SELECT DISTINCT header_id FROM oe_ecc_deletion_map )
GROUP BY
oegt.order_number ,
oegt.customer,
oegt.customer_number,
ship_su.LOCATION,
oegt.order_date,
oegt.status,
oegt.header_id,
oegt.header_hold_name,
oh.cust_po_number,
oh.request_date,
oeht.hdr_alert_count,
oegt.order_type,
oegt.org_id,
oegt.func_currency,
oh.csr_user_id,
oegt.language_code)
)
pivot(
max(status) as status,
max(order_type) as order_type,
max(order_currency) as order_currency
for language_code in ('
|| v_for_lang_pivot_clause
|| '))';
l_hdr_total_sql_text :=
'select oh.header_id ecc_spec_id,
oe_ecc_util_pvt.get_order_total(oh.header_id,
oh.transactional_curr_code,
oh.org_id,
fnd_profile.Value(''OM_ECC_DISPLAY_CURRENCY''),
oh.ordered_date,
null,
''Corporate''
) order_total
from
oe_order_headers_all oh
where
oh.header_id in (select header_id from oe_ecc_headers
UNION
SELECT DISTINCT header_id FROM oe_ecc_deletion_map)';
l_ont_header_hold_text :=
'SELECT DISTINCT oegt.header_id ecc_spec_id, oegt.header_hold_name FROM oe_ecc_global_temp oegt';
query_det_arr.EXTEND (1);
query_det_arr (1) :=
ecc_query_det_rec (l_ont_header_full_text,
G_ONT_ECC_UPS_OP);
query_det_arr.EXTEND (2);
query_det_arr (2) :=
ecc_query_det_rec (l_hdr_total_sql_text,
G_ONT_ECC_UPS_OP);
query_det_arr.EXTEND (3);
query_det_arr (3) :=
ecc_query_det_rec (l_hdr_del_sql_text, G_ONT_ECC_DEL_OP);
query_det_arr.EXTEND (4);
query_det_arr (4) :=
ecc_query_det_rec (l_ont_header_hold_text,
G_ONT_ECC_RPL_OP);
x_ecc_ds_meta_rec :=
ecc_ds_meta_rec (p_dataset_key, query_det_arr);
UPDATE oe_ecc_deletion_map
SET processed = 'Y';
--trunc_temp_tables('END_LOAD');
x_return_status := 'S';
END IF; -- load type = full load
END IF; -- data set is ont-lines or ont-headers
-- set_db_trace('disable');
END GET_ECC_DATA_LOAD_INFO;
PROCEDURE OM_ECC_DATALOAD (ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
SYSTEMNAME IN VARCHAR2,
LOADTYPE IN VARCHAR2,
LANGUAGES IN VARCHAR2,
TRACEENABLED IN VARCHAR2,
LOGLEVEL IN VARCHAR2)
IS
l_req_data VARCHAR2 (1000);
l_request_id NUMBER;
lc_phase VARCHAR2 (50);
lc_status VARCHAR2 (50);
lc_dev_phase VARCHAR2 (50);
lc_dev_status VARCHAR2 (50);
lc_message VARCHAR2 (50);
l_req_return_status BOOLEAN;
BEGIN
l_req_data := fnd_conc_global.request_data;
IF l_req_data = 'END'
THEN
fnd_file.put_line (FND_FILE.OUTPUT,
'Executed the sub request: OM ECC Data Load');
fnd_file.put_line (FND_FILE.LOG,
'Executed the sub request: OM ECC Data Load');
RETURN;
END IF;
fnd_file.put_line (
FND_FILE.LOG,
'Submitting request for ont for ont-lines and loadType: '
|| loadType);
l_request_id :=
FND_REQUEST.SUBMIT_REQUEST (
application => 'FND',
program => 'ECCRUNDL',
description => 'ECC - Run Data Load',
start_time => SYSDATE,
ARGUMENT1 => systemName,
ARGUMENT2 => 'ont',
ARGUMENT3 => 'ont-lines',
ARGUMENT4 => loadType,
ARGUMENT5 => LANGUAGES,
ARGUMENT6 => TRACEENABLED,
ARGUMENT7 => LOGLEVEL,
sub_request => FALSE);
COMMIT;
IF l_request_id > 0
THEN
fnd_file.put_line (FND_FILE.LOG,
'Submitted request id: ' || l_request_id);
fnd_file.put_line (FND_FILE.LOG,
'Waitting for submitted request complete+');
l_req_return_status :=
fnd_concurrent.wait_for_request (
request_id => l_request_id,
interval => 3,
max_wait => 0,
phase => lc_phase,
STATUS => lc_STATUS,
dev_phase => lc_dev_phase,
dev_status => lc_dev_status,
MESSAGE => lc_message);
fnd_file.put_line (FND_FILE.LOG,
'Waitting for submitted request complete-');
fnd_file.put_line (FND_FILE.LOG,
' lc_dev_phase:' || lc_dev_phase);
fnd_file.put_line (FND_FILE.LOG,
' lc_dev_status:' || lc_dev_status);
END IF;
IF UPPER (lc_dev_phase) = 'COMPLETE'
THEN
IF UPPER (lc_dev_status) = 'NORMAL'
OR UPPER (lc_dev_status) = 'WARNING'
THEN
fnd_file.put_line (
FND_FILE.LOG,
'Submitting request -> ECCRUNDL: ECC - Run Data Load for ont-headers');
BEGIN
l_request_id :=
FND_REQUEST.SUBMIT_REQUEST (
application => 'FND',
program => 'ECCRUNDL',
description => 'ECC - Run Data Load',
start_time => SYSDATE,
ARGUMENT1 => systemName,
ARGUMENT2 => 'ont',
ARGUMENT3 => 'ont-headers',
ARGUMENT4 => loadType,
ARGUMENT5 => LANGUAGES,
ARGUMENT6 => TRACEENABLED,
ARGUMENT7 => LOGLEVEL,
sub_request => FALSE);
COMMIT;
fnd_file.put_line (
FND_FILE.OUTPUT,
'ECC Load Job Request ID:' || l_request_id);
fnd_file.put_line (
FND_FILE.LOG,
'ECC Load Job Request ID:' || l_request_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
FND_FILE.LOG,
'OTHERS exception while submitting 2: '
|| SQLERRM);
END;
END IF;
IF l_request_id > 0
THEN
fnd_file.put_line (FND_FILE.LOG,
'Submitted request id: ' || l_request_id);
fnd_file.put_line (
FND_FILE.LOG,
'Waitting for submitted request complete+');
l_req_return_status :=
fnd_concurrent.wait_for_request (
request_id => l_request_id,
interval => 3,
max_wait => 0,
phase => lc_phase,
STATUS => lc_STATUS,
dev_phase => lc_dev_phase,
dev_status => lc_dev_status,
MESSAGE => lc_message);
fnd_file.put_line (
FND_FILE.LOG,
'Waitting for submitted request complete-');
fnd_file.put_line (FND_FILE.LOG,
' lc_dev_phase:' || lc_dev_phase);
fnd_file.put_line (FND_FILE.LOG,
' lc_dev_status:' || lc_dev_status);
END IF;
END IF;
--fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'END');
IF lc_dev_phase = 'COMPLETE'
THEN
IF lc_dev_status = 'WARNING'
THEN
RETCODE := 1;
ELSIF lc_dev_status = 'ERROR'
THEN
RETCODE := 2;
ELSE
RETCODE := 0;
END IF;
END IF;
ERRBUF := 'Sub-Request submitted!';
EXCEPTION
WHEN OTHERS
THEN
RETCODE := 1;
fnd_file.put_line (
FND_FILE.OUTPUT,
'OTHERS exception while submitting : WSH ECC Data Load'
|| SQLERRM);
fnd_file.put_line (
FND_FILE.LOG,
'OTHERS exception while submitting : WSH ECC Data Load'
|| SQLERRM);
END OM_ECC_DATALOAD;
PROCEDURE get_desc_metadata_load_info (
p_dataset_key IN VARCHAR2,
p_dataset_attrs IN ecc_sec_field_values DEFAULT NULL,
p_languages IN VARCHAR2,
x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
x_return_status OUT NOCOPY VARCHAR2)
IS
--l_ecc_dff_segments varchar2(40) := fnd_profile.value('OM: ECC DFF SEGMENTS');
query_det_arr ecc_query_det_arr_type
:= ecc_query_det_arr_type (NULL);
l_view_stmt VARCHAR2 (4000);
l_column_prefix VARCHAR2 (2500);
--l_dff_query_tbl FND_ECC_DFF_UTIL.dff_query_tbl;
--vrow FND_ECC_DFF_UTIL.dff_query_rec;
BEGIN
--insert_debug('Inside get_desc_metadata_load_info' || p_dataset_key || ' - ' || p_languages);
/*
vrow.flexfield_name := 'OE_HEADER_ATTRIBUTES';
vrow.app_id := 660;
vrow.column_prefix := 'ORDER_';
vrow.attribute_metadata := FND_ECC_DFF_UTIL.FND_ECC_DFF_ATTRIBUTES;
vrow.precedence_metadata := FND_ECC_DFF_UTIL.FND_ECC_DFF_PRECEDENCE;
vrow.context_metadata := FND_ECC_DFF_UTIL.FND_ECC_DFF_CONTEXT;
l_dff_query_tbl(l_dff_query_tbl.count) :=vrow;
vrow.flexfield_name := 'OE_LINE_ATTRIBUTES';
vrow.app_id := 660;
vrow.column_prefix := '';
vrow.attribute_metadata := FND_ECC_DFF_UTIL.FND_ECC_DFF_ATTRIBUTES;
vrow.precedence_metadata := FND_ECC_DFF_UTIL.FND_ECC_DFF_PRECEDENCE;
vrow.context_metadata := FND_ECC_DFF_UTIL.FND_ECC_DFF_CONTEXT;
l_dff_query_tbl(l_dff_query_tbl.count) :=vrow;
FND_ECC_DFF_UTIL.GET_ECC_DFF_INFO(p_dataset_key,p_languages, l_dff_query_tbl, x_ecc_ds_meta_rec ,x_return_status);*/
x_return_status := 'S';
END get_desc_metadata_load_info;
/* This function is copied from OE_LINE_STATUS_PUB.Get_Line_Status
* Modified to add language as input parameter
*/
FUNCTION Get_Line_Status (p_line_id IN NUMBER,
p_flow_status_code IN VARCHAR2,
p_language_code IN VARCHAR2)
RETURN VARCHAR2
IS
l_flow_status_code VARCHAR2 (80);
l_flow_meaning VARCHAR2 (80);
released_count NUMBER;
total_count NUMBER;
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
l_language_code VARCHAR2 (10);
BEGIN
l_flow_status_code := p_flow_status_code;
l_language_code := p_language_code;
IF p_flow_status_code IS NULL
THEN
SELECT flow_status_code
INTO l_flow_status_code
FROM oe_order_lines
WHERE line_id = p_line_id;
END IF;
IF l_flow_status_code <> 'AWAITING_SHIPPING'
AND l_flow_status_code <> 'PRODUCTION_COMPLETE'
AND l_flow_status_code <> 'PICKED'
AND l_flow_status_code <> 'PICKED_PARTIAL'
AND l_flow_status_code <> 'PO_RECEIVED'
AND l_flow_status_code <> 'SUPPLY_PARTIAL'
THEN
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = l_flow_status_code
AND LANGUAGE = l_language_code
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group (
lv.lookup_type,
lv.view_application_id);
ELSE
SELECT SUM (DECODE (released_status, 'Y', 1, 'C', 1, 0)),
SUM (1)
INTO released_count, total_count
FROM wsh_delivery_details
WHERE source_line_id = p_line_id
AND source_code = 'OE'
AND released_status <> 'D';
IF released_count = total_count
THEN
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED'
AND LANGUAGE = l_language_code
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group (
lv.lookup_type,
lv.view_application_id);
ELSIF released_count < total_count AND released_count <> 0
THEN
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED_PARTIAL'
AND LANGUAGE = l_language_code
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group (
lv.lookup_type,
lv.view_application_id);
ELSE
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = l_flow_status_code
AND LANGUAGE = l_language_code
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group (
lv.lookup_type,
lv.view_application_id);
END IF;
END IF;
IF l_debug_level > 0
THEN
fnd_file.put_line (FND_FILE.LOG,
'Exiting Get_Line_Status:' || l_flow_meaning);
END IF;
RETURN l_flow_meaning;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN TOO_MANY_ROWS
THEN
NULL;
WHEN OTHERS
THEN
NULL;
END Get_Line_Status;
PROCEDURE set_db_trace (p_action VARCHAR2)
IS
BEGIN
--insert_debug('Inside enable_db_trace procedure');
IF p_action = 'enable'
THEN
--insert_debug('before enabling trace');
EXECUTE IMMEDIATE 'ALTER SYSTEM SET max_dump_file_size = unlimited';
EXECUTE IMMEDIATE 'ALTER SYSTEM SET timed_statistics = true';
EXECUTE IMMEDIATE 'ALTER session set tracefile_identifier= ''suneela_ecc''';
EXECUTE IMMEDIATE 'ALTER session set events ''10046 trace name context forever, level 8''';
EXECUTE IMMEDIATE 'ALTER session set statistics_level = ''ALL''';
--insert_debug('after enabling trace');
ELSIF p_action = 'disable'
THEN
--insert_debug('before disabling trace');
DBMS_SESSION.reset_package;
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context off''';
--insert_debug('after disabling trace');
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--insert_debug(sqlerrm);
END set_db_trace;
PROCEDURE get_accessible_orgs (
p_user_id IN NUMBER,
p_resp_id IN NUMBER,
x_accessible_outab OUT NOCOPY icx_tbl_varchar240)
IS
l_ou_tab mo_global.OrgIdTab;
l_tab icx_tbl_varchar240;
l_count NUMBER := 0;
l_application_id NUMBER := NULL;
BEGIN
BEGIN
SELECT application_id
INTO l_application_id
FROM fnd_responsibility
WHERE responsibility_id = p_resp_id;
EXCEPTION
WHEN OTHERS
THEN
l_application_id := 660;
END;
Fnd_Global.Apps_Initialize (p_user_id, p_resp_id, l_application_id);
mo_global.init ('ONT');
l_tab := icx_tbl_varchar240 ();
SELECT TO_CHAR (organization_id)
BULK COLLECT INTO l_tab
FROM mo_glob_org_access_tmp;
x_accessible_outab := l_tab;
END get_accessible_orgs;
/*Function get_order_total is used to calculate the order total during full load and incremental load. The order
total value is used in ont-headers data set. */
FUNCTION get_order_total (p_header_id NUMBER,
p_header_currency_code VARCHAR2,
p_header_org_id NUMBER,
p_func_currency_code VARCHAR2,
p_order_date DATE,
p_conversion_rate NUMBER,
p_conversion_type_code VARCHAR2)
RETURN NUMBER
IS
l_total_transactional_amt NUMBER;
l_order_total NUMBER;
l_return_status VARCHAR2 (100);
l_func_currency_code VARCHAR2 (100);
BEGIN
-- first select the amount in ordered currency.
--insert_debug('Inside get_order_total');
BEGIN
SELECT SUM (
ROUND (
NVL (l.ordered_quantity * l.unit_selling_price, 0),
2))
INTO l_total_transactional_amt
FROM oe_order_lines_all l
WHERE l.header_id = p_header_id;
--insert_debug('l_total_transactional_amt is :'||l_total_transactional_amt);
EXCEPTION
WHEN OTHERS
THEN
l_total_transactional_amt := 0;
END;
-- convert the ordered amount into currency based on the value of profile option "ECC display currency"
--insert_debug('p_func_currency_code:'||p_func_currency_code);
BEGIN
IF p_func_currency_code IS NOT NULL
THEN
l_func_currency_code := p_func_currency_code;
ELSE
l_func_currency_code :=
oe_upgrade_misc.get_sob_currency (p_header_org_id);
--insert_debug('l_func_currency_code:'||l_func_currency_code);
END IF;
--insert_debug('l_func_currency_code:'||l_func_currency_code);
IF NVL (l_func_currency_code, 'abc') <> p_header_currency_code
THEN
OE_UPGRADE_MISC.CONVERT_CURRENCY (l_total_transactional_amt,
p_header_currency_code,
l_func_currency_code,
p_order_date,
NULL,
'Corporate',
l_return_status,
l_order_total);
--insert_debug('l_order_total is:'||l_order_total);
ELSE -- currency is same. No need to convert.
l_order_total := l_total_transactional_amt;
--insert_debug('in else block l_order_total is:'||l_order_total);
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
--insert_debug('before returning l_order_total is:'||l_order_total);
RETURN l_order_total;
END get_order_total;
/* Procedure trunc_temp_tables is used to truncate the tables before full load and incremental load.
OE_ECC_HEADER_TOTALS table needs to be truncated only before full load. However, the tables OE_ECC_GLOBAL_TEMP
and OE_ECC_HEADERS need to be truncated before every run, both full load and incremental load of ont-lines
data set only. temp tables are not truncated during ont-headers run. Becuase ont-headers data set works on
data populated into temp tables via loads on ont-lines data sets.*/
PROCEDURE trunc_temp_tables (p_load_type IN VARCHAR2)
IS
l_api_success BOOLEAN := FALSE;
l_status VARCHAR2 (128);
l_industry VARCHAR2 (128);
l_schema VARCHAR2 (128);
l_stmt VARCHAR2 (256);
BEGIN
l_api_success :=
fnd_installation.get_app_info (
application_short_name => 'ONT',
status => l_status,
industry => l_industry,
oracle_schema => l_schema);
IF l_api_success
THEN
NULL;
--insert_debug('api is success');
ELSE
NULL;
--insert_debug('api is failure');
END IF;
--l_stmt := 'truncate table test_debug';
--EXECUTE IMMEDIATE l_stmt;
--insert_debug('inside trunc_temp_tables');
IF l_api_success
THEN --if api_success
l_stmt :=
'TRUNCATE TABLE ' || l_schema || '.' || 'OE_ECC_GLOBAL_TEMP';
--insert_debug('before truncating OE_ECC_GLOBAL_TEMP');
EXECUTE IMMEDIATE l_stmt;
l_stmt :=
'TRUNCATE TABLE ' || l_schema || '.' || 'OE_ECC_HEADERS';
--insert_debug('before truncating OE_ECC_HEADERS');
EXECUTE IMMEDIATE l_stmt;
IF p_load_type = 'FULL_LOAD'
THEN
l_stmt :=
'TRUNCATE TABLE '
|| l_schema
|| '.'
|| 'OE_ECC_HEADER_TOTALS';
--insert_debug('before truncating OE_ECC_HEADER_TOTALS');
EXECUTE IMMEDIATE l_stmt;
l_stmt :=
'TRUNCATE TABLE '
|| l_schema
|| '.'
|| 'OE_ECC_DELETION_MAP';
EXECUTE IMMEDIATE l_stmt;
END IF; -- load type is full load.
END IF; --if api_success
--insert_debug('before truncating OE_ECC_DELETION_MAP');
--insert_debug('l_stmt:'||l_stmt);
DELETE FROM oe_ecc_deletion_map
WHERE processed = 'Y';
--insert_debug('After truncating');
END trunc_temp_tables;
/* ECC V3 ER start */
FUNCTION get_csr_details (p_sold_to_org_id NUMBER,
p_inventory_item_id NUMBER)
RETURN NUMBER
IS
l_csr_user VARCHAR2 (200);
l_csr_user_id NUMBER := NULL;
BEGIN
IF p_sold_to_org_id IS NOT NULL AND p_inventory_item_id IS NOT NULL
THEN
--insert_debug('both ct and product are present.try to derive csr');
fnd_file.put_line (
FND_FILE.LOG,
'both ct and product are present.try to derive csr');
BEGIN
SELECT csr_user_id
INTO l_csr_user_id
FROM ( SELECT *
FROM oe_csr_assignment_rules
WHERE sold_to_org_id = p_sold_to_org_id
AND ( item_category IN
(SELECT DISTINCT
miv.category_concat_segs
FROM mtl_item_categories_v miv
WHERE miv.inventory_item_id =
p_inventory_item_id)
OR item_category IS NULL)
ORDER BY sold_to_org_id, item_category) a
WHERE ROWNUM = 1;
RETURN l_csr_user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--insert_debug('CSR rule does not exist. Return null');
fnd_file.put_line (
FND_FILE.LOG,
'CSR rule does not exist. Return null');
RETURN NULL;
END;
ELSIF p_inventory_item_id IS NULL
THEN
--insert_debug('Item details are not present.Return null');
fnd_file.put_line (FND_FILE.LOG,
'Item details are not present.Return null');
RETURN NULL;
ELSIF p_sold_to_org_id IS NULL
THEN
--insert_debug('customer is not yet entered on the order. Cannot assign CSR');
fnd_file.put_line (
FND_FILE.LOG,
'customer is not yet entered on the order. Cannot assign CSR');
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--insert_debug('others exception:'||sqlerrm);
fnd_file.put_line (FND_FILE.LOG, 'others exception:' || SQLERRM);
RETURN NULL;
END;
/* Procedure reassign_csr is called when Assign Customer Servie Rep CP is invoked.
**
**
** reassign_csr - Reassigns the customer service rep based on assignment rules for the given inputs.
**
** IN parameters
** CUSTOMER - sold to org id of the customer
** CUSTSERVICEREP - user_id of the customer service rep
** OVERRIDE - boolean value for override existing csr
** OUT parameters
** ERRBUF - error
** RETCODE - return status
**
** 1) prepare sql statement based on given parameters
** 2) open cursor with prepared sql statement
** 3) If override is yes, update the existing csr to null
** 4) Iterate over the cursor and update the csr
*/
PROCEDURE assign_csr (ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
CUSTOMER NUMBER,
CUSTSERVICEREP NUMBER,
OVERRIDE VARCHAR2)
IS
l_sold_to_org_id NUMBER;
l_customer_service_rep NUMBER;
l_min_line_id NUMBER;
l_inventory_item_id NUMBER;
l_csr_user_id NUMBER;
order_header_id NUMBER;
l_override VARCHAR2 (10);
orders_to_process SYS_REFCURSOR;
l_error BOOLEAN := FALSE;
l_headers_all header_csr_tbl;
l_ECC_BULK_LOAD_SIZE NUMBER
:= NVL (fnd_profile.Value_wnps ('OM_ECC_BULK_LOAD_SIZE'), 1000);
l_query_string VARCHAR2 (1000)
:= 'select order_number,header_id,sold_to_org_id from oe_order_headers where NVL(transaction_phase_code, ''F'') = ''F'' and open_flag =''Y''';
BEGIN
l_sold_to_org_id := CUSTOMER;
l_customer_service_rep := CUSTSERVICEREP;
l_override := OVERRIDE;
mo_global.init ('ONT');
mo_global.set_policy_context ('M', NULL);
--insert_debug('l_sold_to_org_id :'||l_sold_to_org_id ||'- l_customer_service_rep :'||l_customer_service_rep|| ' - l_override :'||l_override);
fnd_file.put_line (
FND_FILE.LOG,
'l_sold_to_org_id :'
|| l_sold_to_org_id
|| '- l_customer_service_rep :'
|| l_customer_service_rep
|| ' - l_override :'
|| l_override);
IF l_customer_service_rep IS NOT NULL
THEN
IF l_override = 'N'
THEN
l_query_string := '';
fnd_file.put_line (
FND_FILE.OUTPUT,
fnd_message.get_string ('ONT', 'ONT_ECC_OVERRIDE_NO'));
fnd_file.put_line (
FND_FILE.LOG,
fnd_message.get_string ('ONT', 'ONT_ECC_OVERRIDE_NO'));
l_error := TRUE;
RETCODE := FND_API.G_RET_STS_ERROR;
l_error := TRUE;
ELSE
l_query_string :=
l_query_string
|| ' and csr_user_id = '
|| l_customer_service_rep; -- csr +override
IF l_sold_to_org_id IS NOT NULL
THEN
l_query_string :=
l_query_string
|| ' and sold_to_org_id ='
|| l_sold_to_org_id; -- csr+override+cust
END IF;
END IF;
ELSIF l_sold_to_org_id IS NOT NULL
THEN
l_query_string :=
l_query_string
|| ' and sold_to_org_id ='
|| l_sold_to_org_id
|| ' '; -- cust+override
IF l_override = 'N'
THEN
l_query_string :=
l_query_string || ' and csr_user_id is null'; -- only cust
END IF;
ELSIF l_override = 'N'
THEN
l_query_string := l_query_string || ' and csr_user_id is null'; -- none
END IF;
--insert_debug('l_query_string :'||l_query_string);
fnd_file.put_line (FND_FILE.LOG,
'l_query_string :' || l_query_string);
IF l_error <> TRUE
THEN
OPEN orders_to_process FOR l_query_string;
LOOP
FETCH orders_to_process
BULK COLLECT INTO l_headers_all
LIMIT l_ECC_BULK_LOAD_SIZE;
EXIT WHEN l_headers_all.COUNT = 0;
--insert_debug('l_headers_all.COUNT is:'||l_headers_all.COUNT);
fnd_file.put_line (
FND_FILE.LOG,
'l_headers_all.COUNT is:' || l_headers_all.COUNT);
/* Audit issue - should not update last_update_date for all the records.
IF l_override = 'Y' THEN
FORALL indx IN 1 .. l_headers_all.COUNT
update oe_order_headers_all
set csr_user_id = null,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
where header_id = l_headers_all(indx).header_id;
END IF;
*/
FOR i IN l_headers_all.FIRST .. l_headers_all.LAST
LOOP
l_inventory_item_id := 0;
l_sold_to_org_id := l_headers_all (i).sold_to_org_id;
--insert_debug('order_header_id :'||l_headers_all(i).header_id);
BEGIN
SELECT l.inventory_item_id
INTO l_inventory_item_id
FROM oe_order_lines_all l,
(SELECT MIN (line_id) min_line_id
FROM oe_order_lines_all
WHERE header_id =
l_headers_all (i).header_id) l_min
WHERE l.line_id = l_min.min_line_id;
EXCEPTION
WHEN OTHERS
THEN
l_inventory_item_id := -1;
--insert_debug(' sql error for order : '|| l_headers_all(i).order_number);
fnd_file.put_line (
FND_FILE.LOG,
' sql error for order : '
|| l_headers_all (i).order_number);
END;
--insert_debug('inventory_item_id :'||l_inventory_item_id ||'l_sold_to_org_id:'||l_sold_to_org_id);
fnd_file.put_line (
FND_FILE.LOG,
'inventory_item_id :'
|| l_inventory_item_id
|| ' - l_sold_to_org_id:'
|| l_sold_to_org_id
|| ' - order_header_id :'
|| l_headers_all (i).header_id);
IF l_inventory_item_id > 0
THEN
l_csr_user_id :=
oe_ecc_util_pvt.get_csr_details (
l_sold_to_org_id,
l_inventory_item_id);
--insert_debug('updating l_csr_user_id :'||l_csr_user_id ||' for order number :'||l_headers_all(i).order_number);
-- should not update if l_csr_user_id same as on the order
BEGIN
UPDATE oe_order_headers_all
SET csr_user_id = l_csr_user_id,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE header_id =
l_headers_all (i).header_id
AND NVL (csr_user_id, 0) <>
NVL (l_csr_user_id, 0);
EXCEPTION
WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION
THEN
--insert_debug('others exception:'||sqlerrm);
fnd_file.put_line (
FND_FILE.LOG,
'RECORD_LOCK_EXCEPTION exception:'
|| SQLERRM);
fnd_file.put_line (
FND_FILE.LOG,
'RECORD_LOCK_EXCEPTION exception: error occured for '
|| l_headers_all (i).header_id
|| '--'
|| l_headers_all (i).order_number);
WHEN OTHERS
THEN
--insert_debug('others exception:'||sqlerrm);
fnd_file.put_line (
FND_FILE.LOG,
'others exception:' || SQLERRM);
fnd_file.put_line (
FND_FILE.LOG,
'others exception: error occured for '
|| l_headers_all (i).header_id
|| '--'
|| l_headers_all (i).order_number);
END;
ELSIF l_override = 'Y'
THEN
-- update csr to null only if all lines are deleted and csr it is not null
BEGIN
UPDATE oe_order_headers_all
SET csr_user_id = NULL,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE header_id =
l_headers_all (i).header_id
AND csr_user_id IS NOT NULL;
EXCEPTION
WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION
THEN
--insert_debug('others exception:'||sqlerrm);
fnd_file.put_line (
FND_FILE.LOG,
'RECORD_LOCK_EXCEPTION exception:'
|| SQLERRM);
fnd_file.put_line (
FND_FILE.LOG,
'RECORD_LOCK_EXCEPTION exception: error occured for '
|| l_headers_all (i).header_id
|| '--'
|| l_headers_all (i).order_number);
WHEN OTHERS
THEN
--insert_debug('others exception:'||sqlerrm);
fnd_file.put_line (
FND_FILE.LOG,
'others exception:' || SQLERRM);
fnd_file.put_line (
FND_FILE.LOG,
'others exception: error occured for '
|| l_headers_all (i).header_id
|| '--'
|| l_headers_all (i).order_number);
END;
END IF;
END LOOP;
COMMIT;
END LOOP;
CLOSE orders_to_process;
RETCODE := FND_API.G_RET_STS_SUCCESS;
-- COMMIT; -- should we commit after every update in inner loop.
fnd_file.put_line (FND_FILE.OUTPUT,
'Program completed successfully');
fnd_file.put_line (FND_FILE.LOG,
'Program completed successfully');
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
FND_FILE.OUTPUT,
'OTHERS exception while submitting : ' || SQLERRM);
fnd_file.put_line (
FND_FILE.LOG,
'OTHERS exception while submitting : ' || SQLERRM);
RETCODE := FND_API.G_RET_STS_ERROR;
END assign_csr;
/* ECC V3 ER end */
END OE_ECC_UTIL_PVT;
/