OZF_OFFER_PUB.process_modifiers

1 2 3 4 5 6 7 8 9

SET serveroutput on; DECLARE -- v_return_status VARCHAR2(1); --v_msg_count NUMBER; -- v_msg_data VARCHAR2(20000); l_user_id NUMBER; l_resp_id NUMBER; l_api_version constant number := 1.0; l_qp_list_header_id NUMBER := 0;

l_validation_level NUMBER := fnd_api.g_valid_level_none;
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);

l_error_location NUMBER := 0;

l_modifier_list_rec OZF_Offer_Pub.modifier_list_rec_type;
l_modifier_line_tbl OZF_Offer_Pub.modifier_line_tbl_type;
l_qualifier_tbl OZF_Offer_Pub.qualifiers_tbl_type;
l_qualifiers_tbl_out qp_qualifier_rules_pub.qualifiers_tbl_type;
l_budget_tbl OZF_Offer_Pub.budget_tbl_type;
l_act_product_tbl OZF_Offer_Pub.act_product_tbl_type;
l_discount_tbl OZF_Offer_Pub.discount_line_tbl_type;
l_excl_tbl OZF_Offer_Pub.excl_rec_tbl_type;
l_offer_tier_tbl OZF_Offer_Pub.offer_tier_tbl_type;
l_prod_tbl OZF_Offer_Pub.prod_rec_tbl_type;
l_na_qualifier_tbl OZF_Offer_Pub.na_qualifier_tbl_type;
l_prod_rec ozf_disc_line_pvt.ozf_prod_rec_type;

BEGIN

BEGIN
select responsibility_id
into l_resp_id
from fnd_responsibility_vl
where responsibility_key = ‘OZF_USER’;
EXCEPTION
WHEN OTHERS THEN
l_resp_id := NULL;
END;

dbms_output.put_line(‘l_resp_id ‘||l_resp_id);

FND_GLOBAL.apps_initialize (
user_id => 1234,
resp_id => l_resp_id, — Oracle trade management user
resp_appl_id => 682 — Oracle Trade management
);

dbms_output.put_line(‘resp_appl_id ‘);

FND_PROFILE.put (‘FND_AS_MSG_LEVEL_THRESHOLD’, FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
FND_MSG_PUB.initialize;

——accrual offer start —————————–

l_modifier_list_rec.OFFER_TYPE :=’LUMPSUM’;
l_modifier_list_rec.OBJECT_VERSION_NUMBER := 1;
l_modifier_list_rec.STATUS_CODE := ‘DRAFT’;
l_modifier_list_rec.STATUS_DATE := sysdate;
l_modifier_list_rec.ACTIVE_FLAG := ‘N’;
l_modifier_list_rec.MODIFIER_LEVEL_CODE := ‘LINE’;
l_modifier_list_rec.BUDGET_AMOUNT_TC := 1000;
l_modifier_list_rec.BUDGET_SOURCE_ID := 1234;
l_modifier_list_rec.reusable := ‘Y’;
l_modifier_list_rec.BUDGET_SOURCE_TYPE := ‘FUND’;
–l_modifier_list_rec.NA_RULE_HEADER_ID := 1234; –required for net-accrual offers
–l_modifier_list_rec.activity_media_id := 1234; –required for scandata/net-accrual offers
l_modifier_list_rec.ql_qualifier_id := 1234; –required for scandata offers
l_modifier_list_rec.ql_qualifier_type := ‘CUSTOMER’; –required for scandata offers
l_modifier_list_rec.lumpsum_amount := 1000;
l_modifier_list_rec.lumpsum_payment_type := ‘ACCRUE’;
l_modifier_list_rec.distribution_type := ‘AMT’;
l_modifier_list_rec.TRANSACTION_CURRENCY_CODE := ‘USD’;
l_modifier_list_rec.CURRENCY_CODE := ‘USD’;
l_modifier_list_rec.START_DATE_ACTIVE := trunc(sysdate);
l_modifier_list_rec.END_DATE_ACTIVE := trunc(sysdate+10);
l_modifier_list_rec.NAME := ‘Lumpsum Offer1’; –offer name
l_modifier_list_rec.DESCRIPTION := ‘Lumpsum Offer1 Desc’; –offer desc
l_modifier_list_rec.ASK_FOR_FLAG := ‘N’;
l_modifier_list_rec.confidential_flag := ‘N’;
l_modifier_list_rec.reusable := ‘N’;
l_modifier_list_rec.offer_operation := ‘CREATE’;
l_modifier_list_rec.modifier_operation := ‘CREATE’;
l_modifier_list_rec.custom_setup_id := 1234; — ams_custom_Setups_vl
l_modifier_list_rec.orig_org_id := 123;

l_act_product_tbl(1).operation := ‘CREATE’;
l_act_product_tbl(1).arc_act_product_used_by := ‘OFFR’;
l_act_product_tbl(1).enabled_flag := ‘Y’;
l_act_product_tbl(1).primary_product_flag := ‘Y’;
l_act_product_tbl(1).level_type_code := ‘PRODUCT’;
l_act_product_tbl(1).line_lumpsum_amount := 1000 ;
l_act_product_tbl(1).line_lumpsum_qty := 1000 ;
l_act_product_tbl(1).inventory_item_id := 123456 ;

OZF_OFFER_PUB.process_modifiers(
p_init_msg_list => FND_API.g_true
,p_api_version => l_api_version
,p_commit => FND_API.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_offer_type => ‘LUMPSUM’
,p_modifier_list_rec => l_modifier_list_rec
,p_modifier_line_tbl => l_modifier_line_tbl
,p_qualifier_tbl => l_qualifier_tbl
,p_budget_tbl => l_budget_tbl
,p_act_product_tbl => l_act_product_tbl
,p_discount_tbl => l_discount_tbl
,p_excl_tbl => l_excl_tbl
,p_offer_tier_tbl => l_offer_tier_tbl
,p_prod_tbl => l_prod_tbl
,p_na_qualifier_tbl => l_na_qualifier_tbl
,x_qp_list_header_id => l_qp_list_header_id
,x_error_location => l_error_location
);

dbms_output.put_line(‘create returns ‘ || l_return_status);
dbms_output.put_line(‘l_qp_list_header_id ‘ || l_qp_list_header_id);
dbms_output.put_line(‘l_msg_count ‘ || l_msg_count);

for i in 1..l_msg_count loop
dbms_output.put_line(substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => ‘F’), 1, 254));
end loop;
IF l_return_status = ‘S’ THEN
commit;
end if;
exception
when others then
dbms_output.put_line(‘l_user_id excep’||l_user_id);
rollback;
END ;

1	-- Lumpsum Offer Header

select * from ozf_offers where qp_list_header_id = 1234; –l_qp_list_header_id

— Lumpsum Offer Lines

select * from ams_act_products where arc_Act_product_used_by = ‘OFFR’ and act_product_used_by_id = 1234;