Advance Pricing Qualifier Upload

CREATE OR REPLACE PACKAGE BODY APPS.XX_EXC_DAP_DSL_ORDER_PKG IS
--
--
FUNCTION XX_DAP_DSL_ORDER_EXCLUDE1(p_order_number IN NUMBER )
RETURN CHAR IS
--
lc_order_type VARCHAR2(50);
--
CURSOR lcu_order_type (p_order_number NUMBER)
IS
SELECT 'Y'
FROM oe_order_headers_all oeh,
oe_transaction_types_tl otl,
fnd_lookup_values flv
WHERE oeh.order_number = p_order_number
AND oeh.order_type_id = otl.transaction_type_id
AND otl.LANGUAGE = USERENV('LANG')
AND flv.lookup_type = 'XXP4U_DAP_DSL_EXCLUSION_LIST'
AND flv.meaning = otl.NAME
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(flv.start_date_active, TRUNC(SYSDATE))
AND NVL(flv.end_date_active, TRUNC(SYSDATE));
BEGIN
--
OPEN lcu_order_type (p_order_number => p_order_number);
FETCH lcu_order_type INTO lc_order_type;
--
IF lcu_order_type%FOUND THEN
lc_order_type:='MATCH';
ELSE
lc_order_type:='NOMATCH';
END IF;
--
CLOSE lcu_order_type;
--
RETURN lc_order_type;
--
EXCEPTION
--
WHEN NO_DATA_FOUND THEN
--
RETURN 'NOMATCH';
--
WHEN OTHERS THEN
RETURN 'PLEASE CHECK THE SETUP-ERROR OCCURED';
END XX_DAP_DSL_ORDER_EXCLUDE1;
--
--
PROCEDURE XX_QUALIFIER_LOAD
(
p_qualifier_context IN VARCHAR2 ,
p_qualifier_attribute IN VARCHAR2 ,
p_comparison_operator_code IN VARCHAR2 ,
p_qualifier_attr_value IN VARCHAR2 ,
retcode OUT NUMBER ,
errbuff OUT VARCHAR2
)
AS
/* |
| Description: Procedure to update qualifiers for Existing Budgets
| This will be one time activity.
| This package also supports the Qualifier Attribute
| loading process.
+----------------------------------------------------------------------------*/
-- Local variables
ln_qual_cnt NUMBER := 1;
ln_msg_count NUMBER;
ln_error_location NUMBER;
lc_return_status VARCHAR2(100);
lc_msg_data VARCHAR2(4000);
lb_proceed BOOLEAN;
lt_qual_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_tbl ozf_offer_pvt.qualifiers_tbl_type;
ln_msg_index_out NUMBER(10);
ld_unique_header_line_cnt NUMBER(10);
lt_profile_value NUMBER ;
ln_count NUMBER:=0;
lc_header_value VARCHAR2(20):='N';
--
--------------------------------------------------
-------- Cursor for getting Budget Data --------
--------------------------------------------------
--
CURSOR LCU_ACT_BUDGET_NAME
IS
SELECT DISTINCT fund_name budget_name
from xxp4u.xxp4u_budget_upload_stg
where line_status='P' ;
--
CURSOR LCU_ACT_BUDGET_ID(p_budget_name VARCHAR2)
IS
SELECT list_header_id,NAME BUDGET_NUMBER ,description modifier_name
FROM qp_list_headers
WHERE ACTIVE_FLAG='Y'
AND UPPER(description)=UPPER(p_budget_name)
AND end_date_active IS NULL OR end_date_active >SYSDATE
GROUP BY list_header_id,NAME,description
ORDER BY NAME;
BEGIN
fnd_file.put_line (fnd_file.LOG,'Starting for Qualifier Update');
---------------------------------------
/* Block for capturing Profile values */
---------------------------------------
--lt_profile_value:=fnd_profile.VALUE('XXP4U_QUALIFIER_LOAD');
--
fnd_file.put_line (fnd_file.LOG,'Entering into loop for Updation');
--
FOR ACT_BUG IN LCU_ACT_BUDGET_NAME LOOP
FOR ACT_HDR_ID IN LCU_ACT_BUDGET_ID(ACT_BUG.budget_name) LOOP
BEGIN
/*--------------------------------------------------------------*/
-- Block to check wheather the Qualifier Exists for the Budget --
/*--------------------------------------------------------------*/
--
SELECT qq.list_header_id
INTO lc_header_value
FROM qp_qualifiers qq
WHERE qq.qualifier_attr_value = 'MATCH'--p_qualifier_attr_value
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE37'--p_qualifier_attribute
AND qq.list_header_id=ACT_HDR_ID.list_header_id
GROUP BY qq.list_header_id;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_header_value:='P';
--
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Problem in Section 11Y for header id'||ACT_HDR_ID.list_header_id);
--
END;
--
IF lc_header_value='P' THEN
--
fnd_file.put_line (fnd_file.LOG,'Calling the API...');
ln_count:=ln_count+1;
fnd_file.put_line (fnd_file.LOG,ACT_HDR_ID.BUDGET_NUMBER||
'- List_Header_id'||ACT_HDR_ID.list_header_id||
'- Budget Name'|| ACT_HDR_ID.modifier_name);
--
--For Attribute1
/* lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := p_qualifier_context; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := p_qualifier_attribute; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := p_qualifier_attr_value; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := p_comparison_operator_code;-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
*/
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := 'ORDER'; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE37'; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := 'MATCH'; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := 'NOT =';-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
-------------------------------------
/* Call The API to upload qualifier */
-------------------------------------
ozf_offer_pvt.process_market_qualifiers(
p_init_msg_list => fnd_api.g_true,
p_api_version => 1.0,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_qualifiers_tbl => lt_qual_tbl,
x_error_location => ln_error_location
);
COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,'For Budget Name :'||ACT_HDR_ID.modifier_name||'the qualifier already Exists');
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
fnd_file.put_line (fnd_file.LOG,'Problem in Setup'||SQLERRM);
fnd_file.put_line (fnd_file.LOG,'Successfully updated:'||ln_count||' Records');
END XX_QUALIFIER_LOAD;
--
PROCEDURE XX_QUALIFIER_1_LOAD
(
p_qualifier_context IN VARCHAR2 ,
p_qualifier_attribute IN VARCHAR2 ,
p_comparison_operator_code IN VARCHAR2 ,
p_qualifier_attr_value IN VARCHAR2 ,
retcode OUT NUMBER ,
errbuff OUT VARCHAR2
)
AS
/*
+-----------------------------------------------------------------------------
|
+-----------------------------------------------------------------------------
|
|
|
| Description: Procedure to update qualifiers for Existing Budgets
| This will be one time activity.
| This package also supports the Qualifier Attribute
| loading process.
+----------------------------------------------------------------------------*/
-- Local variables
ln_qual_cnt NUMBER := 1;
ln_msg_count NUMBER;
ln_error_location NUMBER;
lc_return_status VARCHAR2(100);
lc_msg_data VARCHAR2(4000);
lb_proceed BOOLEAN;
lt_qual_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_exc EXCEPTION;
ln_msg_index_out NUMBER(10);
ld_unique_header_line_cnt NUMBER(10);
lt_profile_value NUMBER;
ln_count NUMBER:=0;
CURSOR LCU_MODIFIER_ID
IS
SELECT list_header_id,NAME BUDGET_NUMBER ,description modifier_name
FROM qp_list_headers
WHERE ACTIVE_FLAG='Y'
--AND NAME IN('10023','10024','10025','10026','10027')
AND end_date_active IS NULL OR end_date_active >SYSDATE
GROUP BY list_header_id,NAME,description
ORDER BY NAME;

BEGIN
fnd_file.put_line (fnd_file.LOG,'Starting for Qualifier Update');
---------------------------------------
/* Block for capturing Profile values */
---------------------------------------
--lt_profile_value:=fnd_profile.VALUE('XXP4U_QUALIFIER_LOAD');
fnd_file.put_line (fnd_file.LOG,'Entering into loop for Updation');
FOR ACT_HDR_ID IN LCU_MODIFIER_ID LOOP
ln_count:=ln_count+1;
fnd_file.put_line (fnd_file.LOG,ACT_HDR_ID.BUDGET_NUMBER||
'- List_Header_id'||ACT_HDR_ID.list_header_id||
'- Budget Name'|| ACT_HDR_ID.modifier_name);
--For Attribute1
/*
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := p_qualifier_context; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := p_qualifier_attribute; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := p_qualifier_attr_value; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := p_comparison_operator_code;-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
*/
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := 'ORDER'; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE37'; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := 'MATCH'; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := 'NOT =';-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'

-------------------------------------
/* Call The API to upload qualifier */
-------------------------------------
ozf_offer_pvt.process_market_qualifiers(
p_init_msg_list => fnd_api.g_true,
p_api_version => 1.0,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_qualifiers_tbl => lt_qual_tbl,
x_error_location => ln_error_location
);
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG,'End for Qualifier Update');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Problem in Setup'||SQLERRM);
fnd_file.put_line (fnd_file.LOG,'Successfully updated:'||ln_count||' Records');
retcode := 2;
END XX_QUALIFIER_1_LOAD;
--
END XX_EXC_DAP_DSL_ORDER_PKG;
/