Oracle Apps: May 2015

Monday, 18 May 2015

OZF Provision Transfer (Oracle Trade Management)

Declare

lr_adjustment_rec ozf_fund_utilized_pub.adjustment_rec_type;
lc_return_status VARCHAR2 (1);
lc_record_exist VARCHAR2 (1);
lc_error_flag VARCHAR2 (1) := 'N';
lc_adjustment_type VARCHAR2 (80);
lc_error_msg VARCHAR2 (200);
lc_err_locations VARCHAR2 (200);
lc_msg_data VARCHAR2 (32767);
lc_errbuf VARCHAR2 (32767);
ln_claim_type_id NUMBER;
ln_qp_list_header_id NUMBER;
ln_cr_ccid NUMBER;
ln_dr_ccid NUMBER;
ln_fund_id NUMBER;
ln_msg_count NUMBER;
ln_util_id NUMBER;
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;
ln_budget_amt NUMBER;
ln_cust_id NUMBER;
ln_num_of_months NUMBER;
ln_loop_count NUMBER;
ln_tot_budg_amt NUMBER;
ln_prov_amt NUMBER;
ld_fund_date DATE;
ld_start_date DATE;
ld_end_date DATE;
n_request_id NUMBER := 0;

CURSOR lcur_fund_data(p_fund_number number)
IS

SELECT v.fund_id, v.fund_number,b.cust_account_id,
o.qp_list_header_id,
adjustment_type_id old_adjustment_type_id, b.attribute4
FROM ozf_funds_all_vl v,
ozf_funds_utilized_all_b b,
ozf_offers o
WHERE 1 = 1
AND b.fund_id = v.fund_id
AND v.attribute3 = o.offer_code
AND v.fund_number = p_fund_number
AND b.attribute4 = 'PROVISION'
AND rownum = 1;

BEGIN
BEGIN
mo_global.set_policy_context (UPPER ('s'), 22);
END;

FOR i IN lcur_fund_data (p_fund_id)
LOOP
EXIT WHEN lcur_fund_data%NOTFOUND;

FND_GLOBAL.apps_initialize (user_id => 8605,
resp_id => 22371,
resp_appl_id => 682);

lr_adjustment_rec.adjustment_type := 'STANDARD';
lr_adjustment_rec.adjustment_type_id := 21;
lr_adjustment_rec.fund_id := i.fund_id;
lr_adjustment_rec.fund_number := i.fund_number;
lr_adjustment_rec.adjustment_date := Sysdate;
lr_adjustment_rec.gl_date := Sysdate;
lr_adjustment_rec.activity_type := 'OFFR';
lr_adjustment_rec.activity_id := i.qp_list_header_id;
lr_adjustment_rec.plan_currency_code :='INR';
lr_adjustment_rec.org_id := 22;
lr_adjustment_rec.skip_acct_gen_flag := 'F';
lr_adjustment_rec.amount := p_amount;
lr_adjustment_rec.customer_type := 'CUSTOMER';
lr_adjustment_rec.attribute4 := 'PROVISION';
lr_adjustment_rec.cust_id := i.cust_account_id;
ozf_fund_utilized_pub.create_fund_adjustment (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_adj_rec => lr_adjustment_rec,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_utilization_id => ln_util_id);

FND_FILE.PUT_LINE(FND_FILE.LOG,'lc_return_status ' || lc_return_status);

IF lc_return_status <> 'S'
THEN
IF ln_msg_count <= 1
THEN
lc_errbuf := NULL;
lc_errbuf := lc_errbuf || lc_msg_data;
lc_errbuf:=substr(lc_errbuf,1,150);
FND_FILE.PUT_LINE(FND_FILE.LOG,'error message - '||lc_errbuf);

ELSE
lc_errbuf := NULL;
FOR ln_i IN 1 .. ln_msg_count
LOOP

lc_errbuf :=
lc_errbuf || ' ' || fnd_msg_pub.get (ln_i, 'F');

lc_errbuf:=Substr(lc_errbuf,1,150);
FND_FILE.PUT_LINE(FND_FILE.LOG,'error message - '|| lc_errbuf);

END LOOP;
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Provision transfer is Successfull and utilization id is - '||ln_util_id);
END IF;

COMMIT;
END LOOP;

END;

Incentive Compensation rollback from calculate

Delete cn_trx_sales_lines_all
where processed_period_id = 2015001

Delete cn_trx_lines
where trx_id in (Select trx_id from cn_trx_all
where trunc(processed_date) between '01-APR-2015' and '30-APR-2015')

Delete cn_trx_all
where trunc(processed_date) between '01-APR-2015' and '30-APR-2015'

Delete cn_not_trx_all
where trunc(processed_date) between '01-APR-2015' and '30-APR-2015'

Delete cn_comm_lines_api_all
where trunc(processed_date) between '01-APR-2015' and '30-APR-2015'

DELETE FROM CN_COMMISSION_LINES_ALL
WHERE processed_date between '01-APR-2015' and '30-APR-2015'

DELETE FROM CN_COMMISSION_HEADERS_ALL
WHERE processed_date between '01-APR-2015' and '30-APR-2015'

Delete xx_jtf_rs_trxdata_stg
where trunc(invoice_processed_date) between '01-APR-2015' and '30-APR-2015'

delete xxatfl_jtf_rs_ar_trxdata_stg
where trunc(trx_date) between '01-APR-2015' and '30-APR-2015'

ATFL ONT Sale Order Backorder program without delivery

Declare

p_line_rows WSH_UTIL_CORE.ID_TAB_TYPE;
x_del_rows WSH_UTIL_CORE.ID_TAB_TYPE;
p_count NUMBER;
v_context VARCHAR2 (100);

x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);

CURSOR C1
IS
SELECT wdd.delivery_Detail_id,
ooh.order_source_id,
ooh.header_id,
NULL Line_id,
wdd.source_header_id,
sot.name,
wdd.source_line_id,
wdd.subinventory whse_code,
ooh.order_number,
ooh.attribute5 po_expire_date,
DECODE (wdd.released_status, 'Y', 'Staged/Pick Confirmed')
Shipping_Status
FROM oe_order_headers_all ooh,
oe_transaction_types_v sot,
wsh_delivery_details wdd
WHERE 1 = 1
AND ooh.header_id = wdd.source_header_id
AND sot.transaction_type_id = ooh.order_type_id
AND wdd.released_status = 'Y'
AND TRUNC (SYSDATE) - TO_DATE (ooh.booked_date) >= 3
AND ooh.flow_status_code = 'BOOKED'
AND NOT EXISTS
(SELECT 1
FROM wsh_Delivery_assignments
WHERE delivery_Detail_id =
wdd.delivery_detail_id
AND delivery_id IS NOT NULL);

BEGIN
BEGIN
FND_GLOBAL.apps_initialize (2438, 50118, 200);
END;
IF p_order_num is NULL THEN
FOR i IN c1
LOOP
EXIT WHEN C1%NOTFOUND;

FNd_File.put_line (
fnd_file.LOG,
'Delivery_detail_id is - ' || i.delivery_detail_id);

BEGIN
p_line_rows (1) := i.delivery_detail_id; -- Delivery_detail_id from WSH_DELIVERY_DETAILS

-- API Call for Auto Create Deliveries
WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES (
p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows);

IF x_return_status = 'S'
THEN
FNd_File.put_line (
fnd_file.LOG,
'Delivery Got Sucessfully created and assigned to delivery details '
|| x_del_rows (1));

wsh_reg_delivery_bkrdr (x_del_rows (1));
ELSE
FNd_File.put_line (fnd_file.LOG,
'Message count ' || x_msg_count);

IF x_msg_count = 1
THEN
FNd_File.put_line (fnd_file.LOG,
'x_msg_data ' || x_msg_data);
ELSIF x_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
x_msg_data :=
FND_MSG_PUB.Get (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);

IF x_msg_data IS NULL
THEN
EXIT;
END IF;

FNd_File.put_line (
fnd_file.LOG,
'Message' || p_count || '---' || x_msg_data);
END LOOP;
END IF;
END IF;
END;
END LOOP;
ELSE
IF p_order_num is not null and p_orgn_code is null then
retcode := 2;

FNd_File.put_line (
fnd_file.LOG,'Organization Code cannot be null if order number is provided');
Return;
ELSIF p_order_num is null and p_orgn_code is not null then
retcode := 2;
FNd_File.put_line (
fnd_file.LOG,'Order Number cannot be null if Organization code is provided');
Return;
END IF;

FOR j IN c2(p_order_num,p_orgn_code )
LOOP
EXIT WHEN C2%NOTFOUND;

FNd_File.put_line (
fnd_file.LOG,
'Delivery_detail_id is - ' || j.delivery_detail_id);

BEGIN
p_line_rows (1) := j.delivery_detail_id; -- Delivery_detail_id from WSH_DELIVERY_DETAILS

-- API Call for Auto Create Deliveries
WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES (
p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows);

IF x_return_status = 'S'
THEN
FNd_File.put_line (
fnd_file.LOG,
'Delivery Got Sucessfully created and assigned to delivery details '
|| x_del_rows (1));

wsh_reg_delivery_bkrdr (x_del_rows (1));
ELSE
FNd_File.put_line (fnd_file.LOG,
'Message count ' || x_msg_count);

IF x_msg_count = 1
THEN
FNd_File.put_line (fnd_file.LOG,
'x_msg_data ' || x_msg_data);
ELSIF x_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
x_msg_data :=
FND_MSG_PUB.Get (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);

IF x_msg_data IS NULL
THEN
EXIT;
END IF;

FNd_File.put_line (
fnd_file.LOG,
'Message' || p_count || '---' || x_msg_data);
END LOOP;
END IF;
END IF;
END;
END LOOP;
END IF;
END;

ONT Sale Order Backorder program with delivery

Declare

x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);

--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;

CURSOR lc_get_ship_data
IS
Select wdd.delivery_Detail_id,
ooh.order_source_id,
ooh.header_id,
NULL Line_id,
wdd.source_header_id,
sot.name,
wdd.source_line_id,
wdd.subinventory whse_code,
ooh.order_number,
ooh.attribute5 po_expire_date,
DECODE (wdd.released_status, 'Y', 'Staged/Pick Confirmed')
Shipping_Status,
wda.delivery_id
FROM oe_order_headers_all ooh,
oe_transaction_types_v sot,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE 1 = 1
AND ooh.header_id = wdd.source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND sot.transaction_type_id = ooh.order_type_id
AND wdd.released_status = 'Y';

ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;
ln_reason_id NUMBER;

BEGIN
/* Initialize return status*/
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
/* Call this procedure to initialize applications parameters. To determine
parameter values, refer to the Application Paramater Initialization section of
this chapter. */
--BEGIN
--fnd_file.put_line(fnd_file.output,'STATUS'||' '||'ORDER NUMBER'||' '||'DATE'||' '||' '||'WAREHOUSE'||' '||'CANCELLED QTY');
ln_user_id := APPS.FND_PROFILE.VALUE ('USER_ID');

IF ln_user_id IS NULL
THEN
ln_user_id := -1;
END IF;

ln_resp_id := APPS.FND_PROFILE.VALUE ('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE ('RESP_APPL_ID');

BEGIN
FND_GLOBAL.apps_initialize (ln_user_id, ln_resp_id, ln_resp_app_id);
END;
IF v_delivery_id is NULL THEN
FOR lc_ship_dt IN lc_get_ship_data
LOOP
--exit when lc_get_ship_data%notfound;

/* Values to be set for action code 3 are */
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := lc_ship_dt.delivery_id; --620600; -- The delivery that needs to be confirmed
p_delivery_name := lc_ship_dt.delivery_id; --'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
P_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
WSH_DELIVERIES_PUB.Delivery_Action (
p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);

--IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then -- Commented By Samba on 21-JAN-2013

IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, 'W'))
THEN -- Added By Samba on 21-JAN-2013
fnd_file.put_line (
fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| lc_ship_dt.order_number
|| ', and for whse - '
|| lc_ship_dt.whse_code);
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| x_return_status
|| ' for header id - '
|| lc_ship_dt.header_id);
ROLLBACK;

FOR j IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line (fnd_file.LOG,
'Error Message is=> ' || l_msg_data);
END LOOP;
END IF;
END LOOP;
Else
FOR i IN c2(v_delivery_id)
LOOP
--exit when lc_get_ship_data%notfound;

/* Values to be set for action code 3 are */
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := i.delivery_id; --620600; -- The delivery that needs to be confirmed
p_delivery_name := i.delivery_id; --'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
P_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
WSH_DELIVERIES_PUB.Delivery_Action (
p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);

--IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then -- Commented By Samba on 21-JAN-2013

IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, 'W'))
THEN -- Added By Samba on 21-JAN-2013
fnd_file.put_line (
fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| i.order_number
|| ', and for whse - '
|| i.whse_code);
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| l_msg_data
|| ' for header id - '
|| i.header_id);
fnd_file.put_line (
fnd_file.LOG,'msg count is - '||x_msg_count);

FOR j IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line (fnd_file.LOG,
'Error Message is=> ' || l_msg_data);
END LOOP;
ROLLBACK;
END IF;
END LOOP;
END IF;
END;

Agreement Creation API in R12

DECLARE

out_return_status varchar2(1) := NULL;
out_msg_count number := 0;
out_msg_data varchar2(2000);

in_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type;
in_Agreement_val_rec OE_Pricing_Cont_PUB.Agreement_Val_Rec_Type;

in_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
in_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;

in_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
in_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;

in_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
in_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;

out_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type;
out_Agreement_val_rec OE_Pricing_Cont_PUB.Agreement_Val_Rec_Type;

out_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
out_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;

out_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
out_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;

out_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
out_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;

K number := 1;
j number := 1;

begin

--dbms_output.put_line('Creating an Agreement record');

-- Creating an Agreement record

--- Agreement creation rec

in_Agreement_rec.name :='Testing_123_ABC';
in_Agreement_rec.creation_date :=sysdate;
in_Agreement_rec.created_by := 1738;
in_Agreement_rec.last_update_date := sysdate;
in_Agreement_rec.last_updated_by := 1738;
in_Agreement_rec.agreement_type_code := 'STANDARD';
in_Agreement_rec.agreement_num := '2001- XMP2';
in_Agreement_rec.sold_to_org_id := 3801;
in_Agreement_rec.revision := '1';
in_Agreement_rec.revision_date := sysdate;
in_Agreement_rec.term_id := 1000;
in_Agreement_rec.OVERRIDE_IRULE_FLAG := 'Y';
in_Agreement_rec.OVERRIDE_ARULE_FLAG := 'Y';
in_Agreement_rec.agreement_id := FND_API.G_MISS_NUM;
in_Agreement_rec.operation := QP_GLOBALS.G_OPR_CREATE;

/* set the list_header_id to g_miss_num */

----- Price List creaation rec

in_price_list_rec.list_header_id := FND_API.G_MISS_NUM;
in_price_list_rec.name := 'Testing_123_ABC';
in_price_list_rec.list_type_code := 'AGR';
in_price_list_rec.description := 'Testing_123_ABC';
in_price_list_rec.currency_code := 'INR';
--in_price_list_rec.QUALIFICATION_IND:=22;
in_price_list_rec.operation := QP_GLOBALS.G_OPR_CREATE;

------ List Line creation rec

in_price_list_line_tbl(1).list_line_id := FND_API.G_MISS_NUM;
in_price_list_line_tbl(1).list_line_type_code := 'PLL';
in_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
in_price_list_line_tbl(1).operand := 200;
in_price_list_line_tbl(1).arithmetic_operator := 'UNIT_PRICE';
in_price_list_line_tbl(1).REVISION_REASON_CODE:=100;
in_price_list_line_tbl(1).QUALIFICATION_IND:=22;
-- in_price_list_line_tbl(K).COMPARISON_OPERATOR_CODE:='-';

---- Price Attribute creation recc

-----1st record

--
in_pricing_attr_tbl(1).pricing_attribute_id := FND_API.G_MISS_NUM;
in_pricing_attr_tbl(1).list_line_id := FND_API.G_MISS_NUM;
in_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE_CONTEXT := 'ITEM';
in_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
in_pricing_attr_tbl(1).PRODUCT_ATTR_VALUE := '100487';
in_pricing_attr_tbl(1).PRODUCT_UOM_CODE := 'EA';
in_pricing_attr_tbl(1).EXCLUDER_FLAG := 'N';
in_pricing_attr_tbl(1).ATTRIBUTE_GROUPING_NO := 1;
in_pricing_attr_tbl(1).comparison_operator_code := '=';
in_pricing_attr_tbl(1).PRICE_LIST_LINE_INDEX := 1;
in_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;

---- 2nd price attribute record
in_pricing_attr_tbl(2).pricing_attribute_id := FND_API.G_MISS_NUM;
in_pricing_attr_tbl(2).list_line_id := FND_API.G_MISS_NUM;
in_pricing_attr_tbl(2).PRODUCT_ATTRIBUTE_CONTEXT := 'ITEM';
in_pricing_attr_tbl(2).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
in_pricing_attr_tbl(2).PRODUCT_ATTR_VALUE := '100487';
in_pricing_attr_tbl(2).PRODUCT_UOM_CODE := 'EA';
in_pricing_attr_tbl(2).ATTRIBUTE_GROUPING_NO := 1;
in_pricing_attr_tbl(2).PRICE_LIST_LINE_INDEX := 1;
in_pricing_attr_tbl(2).pricing_attribute_context := 'PRICING ATTRIBUTE';
in_pricing_attr_tbl(2).pricing_attribute := 'PRICING_ATTRIBUTE19';
in_pricing_attr_tbl(2).PRICING_ATTRIBUTE_DATATYPE:='C';
in_pricing_attr_tbl(2).pricing_attr_value_from := 200;
in_pricing_attr_tbl(2).comparison_operator_code := '=';
in_pricing_attr_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
in_pricing_attr_tbl(2).QUALIFICATION_IND:=22;

oe_debug_pub.add('Calling the process Agreements API');

OE_Pricing_Cont_PUB.Process_Agreement
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => out_return_status
, x_msg_count => out_msg_count
, x_msg_data => out_msg_data
, p_Agreement_rec => in_Agreement_rec
, p_Price_LHeader_rec => in_price_list_rec
, p_Price_LLine_tbl => in_price_list_line_tbl
, p_Pricing_Attr_tbl => in_pricing_attr_tbl
, x_Agreement_rec => out_Agreement_rec
, x_Agreement_val_rec => out_Agreement_val_rec
, x_Price_LHeader_rec => out_price_list_rec
, x_Price_LHeader_val_rec => out_price_list_val_rec
, x_Price_LLine_tbl => out_price_list_line_tbl
, x_Price_LLine_val_tbl => out_price_list_line_val_tbl
, x_Pricing_Attr_tbl => out_pricing_attr_tbl
, x_Pricing_Attr_val_tbl => out_pricing_attr_val_tbl
);

dbms_output.put_line ('Status '||out_return_status);

IF out_return_status <> FND_API.G_RET_STS_SUCCESS THEN

RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

END IF;

oe_debug_pub.add('after process agreement ');

EXCEPTION

WHEN FND_API.G_EXC_ERROR THEN

out_return_status := FND_API.G_RET_STS_ERROR;
rollback;

-- Get message count and data

--dbms_output.put_line('err msg 1 is : ' || out_msg_data);

WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN

out_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
rollback;
--dbms_output.put_line(' msg count 2 is : ' || out_msg_count);

for k in 1 .. out_msg_count loop
out_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
);
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || out_msg_data);
null;
end loop;

WHEN OTHERS THEN

out_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
rollback;

-- Get message count and data
--dbms_output.put_line('err msg 3 is : ' || out_msg_data);

for k in 1 .. out_msg_count loop
out_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
);
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || out_msg_data);
null;
end loop;

end;
/