Oracle Apps

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;
/

Saturday, 28 January 2012

Friday, 27 January 2012

MTL Inventory Item Accounts Update Program

Run Item Import Program with the generated Process id from the below program after program is completed successfull.

Get Field
-----

FUNCTION get_field(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_field_no IN NUMBER) RETURN VARCHAR2 IS
l_start NUMBER :=0;
l_end NUMBER :=0;
lc_delimiter VARCHAR2(10);
BEGIN
lc_delimiter := p_delimiter;
/*****-- Determine start position--*****/
IF p_field_no = 1 THEN
l_start :=0;
ELSE
l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
IF l_start = 0 THEN
RETURN NULL;
END IF;
END IF;
/*****-- Determine end position--*****/
l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
IF l_end = 0 THEN
l_end := LENGTH(p_line) + 1;
END IF;
/*****-- Extract the field data --*****/
IF (l_end - l_start) = 1 THEN
RETURN NULL;
ELSE
RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_field;
--------------------------------------------------------------------------------------------------------

PROCEDURE mtl_inv_acct_upload(Errbuf OUT VARCHAR2,Retcode OUT NUMBER,p_file_name IN VARCHAR2,P_LINE_NUM IN NUMBER)
IS
reqfiletype UTL_FILE.FILE_TYPE;
req_line VARCHAR2(3000);
req_delim VARCHAR2(1):=',';
rec_count NUMBER;
v_File_Name VARCHAR2(200):=P_File_Name;
v_date_processed DATE; -- (Q)
ln_user_id NUMBER:=FND_GLOBAL.USER_ID;
ln_resp_id NUMBER:=FND_GLOBAL.RESP_ID;
ln_resp_app_id NUMBER:=FND_GLOBAL.RESP_APPL_ID;
v_request_id NUMBER:=0;
ln_batch_id NUMBER;
Lc_Transaction_type VARCHAR2(500);
ln_line_no NUMBER;
ln_record_id NUMBER;

ln_last_updated_by NUMBER;
ln_conc_req_id NUMBER;
ln_login_id NUMBER;
lc_error_flag VARCHAR2(10);
lc_errors_all VARCHAR2(500);
ln_cnt NUMBER;
lc_error_message VARCHAR2(500);
ln_operating_org_id NUMBER;
lc_status VARCHAR2(50);
ln_org_id NUMBER;
lc_error_msg VARCHAR2(500);
ln_request_id NUMBER;
lc_item_no VARCHAR2(50);
ln_item_id NUMBER;
lc_item_desc VARCHAR2(500);
lc_whse_code VARCHAR2(10);
ln_whse_id NUMBER;
lc_acct_comb VARCHAR2(500);
lc_loc VARCHAR2(10);
ln_rule_id NUMBER;
ln_process_id NUMBER;
ln_ccid NUMBER;
lc_rev_concat_segments VARCHAR2(500);
ln_chart_of_account_id NUMBER;
lc_record_status VARCHAR2(20);
lc_item_sts_code VARCHAR2(20);
lc_item_type VARCHAR2(20);
lc_product_code VARCHAR2(20);
lc_lob VARCHAR2(20);

BEGIN
v_file_name:=p_file_name;
reqfiletype := UTL_FILE.FOPEN('MTL_INV_ACCT_UPD', v_File_Name, 'r');
ln_line_no :=0;

---Process id for import items interface
Begin
Select to_char(sysdate,'DDMMYYYYHH24MISS') into ln_process_id from dual;
End;

LOOP
ln_line_no := ln_line_no + 1;
BEGIN
UTL_FILE.GET_LINE(reqfiletype , req_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
IF ln_line_no > p_line_num THEN
---------------------------------------------------------------------------------------------
--- Mapping with csv fields
-----------------------------------------------------------------------------------------------
--Item Code
BEGIN
lc_item_no := TRIM(get_field(req_line,req_delim,1)); -- (A)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Number '||SQLERRM);
END;
-----------------------------------------------------------------------------------------------
--Item Description
BEGIN
lc_item_desc := TRIM(get_field(req_line,req_delim,2)); -- (B)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Description '||SQLERRM);
END;
-----------------------------------------------------------------
-- Whse Code
BEGIN
lc_whse_code := TRIM(get_field(req_line,req_delim,3)); -- (C)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Whse Code '||SQLERRM);
END;
-----------------------------------------------------------------
-- Account
BEGIN
lc_acct_comb := TRIM(get_field(req_line,req_delim,4)); -- (D)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Acct Comb '||SQLERRM);
END;
-----------------------------------------------------------------
---- Record Status
BEGIN
lc_record_status := TRIM(get_field(req_line,req_delim,5)); -- (E)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Start Period '||SQLERRM);
END;
----------------------------------------------------------------

BEGIN

ln_user_id := apps.fnd_profile.value('USER_ID');

IF ln_user_id IS NULL THEN
ln_user_id := -1;
END IF;
ln_last_updated_by := APPS.FND_GLOBAL.USER_ID;
-- To get the Conc Request ID
ln_conc_req_id := APPS.FND_GLOBAL.CONC_REQUEST_ID;
IF ln_conc_req_id IS NULL THEN
ln_conc_req_id := -1;
END IF;
-- To get the Login ID
ln_login_id := APPS.FND_GLOBAL.LOGIN_ID;
IF ln_login_id IS NULL THEN
ln_login_id := -1;
END IF;
-- To get the Set of Book ID
--ln_set_of_book_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
-- To get the Operating Uni ID
ln_operating_org_id := fnd_profile.VALUE ('ORG_ID');
-- Start to Process the Cursor Records

---Validations for Account update in MTL Item Master

/******************************************************/
-- Validation for organization code
/******************************************************/

Begin
SELECT organization_id into ln_whse_id
FROM mtl_parameters
WHERE organization_code = lc_whse_code;
Exception When Others Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Whse Code Either Does Not Exist or is inactive in Inventory Organization Master -'||lc_whse_code );
Retcode := 2;
Return;
End;

--
/******************************************************/
-- Validation for item code
/******************************************************/

Begin
Select inventory_item_id into ln_item_id
From mtl_system_items_b
Where organization_id = ln_whse_id
And segment1 = lc_item_no;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Code - '||ln_item_id||' either does not exist or is inactive in Inventory Item master for warehouse - '||lc_whse_code);
retcode:=2;
Return;
End;

/******************************************************/
-- Product code validation
/******************************************************/

Begin
Select flex_value into lc_product_code
From fnd_flex_values_vl
Where flex_value_set_id = 1005239
And Trim(substr(Description,instr(Description,'-',-1)+1)) = Trim(lc_item_no);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Product Code does not exist in key flexfield setup -'||lc_item_no);
retcode:=2;
Return;
End;

/******************************************************/
--Cross Validation checkinf for LOB and PRODUCT
/******************************************************/

Begin
Select rule_line_id into ln_rule_id
From fnd_flex_validation_rule_lines
Where flex_validation_rule_name like 'LOB & Product'
And substr(concatenated_segments_high,3,3) = Substr(lc_acct_comb,6,3)
And substr(concatenated_segments_high,instr(concatenated_segments_high,'.',-2)+1,5) = Substr(lc_acct_comb,25,5);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Cross validation rule does not exist for lob - '||Substr(lc_acct_comb,6,3)||' and product code - '||Substr(lc_acct_comb,25,5));
retcode:=2;
Return;
End;

/******************************************************/
-- Location Validation
/******************************************************/

Begin
Select substr(acctg_unit_no,6) into lc_loc
From gl_accu_mst gat,
gl_accu_map gap
Where gap.acctg_unit_id = gat.acctg_unit_id
And gap.whse_code = lc_whse_code;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in MAC account combination - '||lc_whse_code);
retcode:=2;
Return;
End;
--
If lc_loc <> Substr(lc_acct_comb,10,3) Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in account combination,Either it does not exist in MAC or wrong in sheet - '||lc_whse_code);
retcode:=2;
Return;
End If;

/******************************************************/
-- LOB validation
/******************************************************/

Begin
Select segment5 into lc_lob
From MTL_ITEM_CATEGORIES_V
Where CATEGORY_SET_ID = 1100000021
And inventory_item_id = ln_item_id
And organization_id = 23;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB does not exist in Item Category -'||ln_item_id);
retcode:=2;
Return;
End;
--
IF lc_lob <> Substr(lc_acct_comb,6,3) THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB Mismatch with LOB in Item Category of Inventory Item Master -'||lc_lob||' for item id -'||ln_item_id);
retcode:=2;
Return;
End If;

/******************************************************/
--Account combination creation using API
/******************************************************/

Begin

ln_ccid := fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => 50186,
validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY'),
concatenated_segments => lc_acct_comb);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Does Not Exist in Master -'||lc_acct_comb);

retcode:=2;
Return;
End;
--Cross validation failure check
If ln_ccid = 0 Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Cannot be created please check cross validation rules -'||lc_acct_comb);
retcode:=2;
Return;
End If;

-- End of Vlidations for sales Account creation in Inventory Item Master

IF ln_item_id > 0 Then
fnd_file.put_line(fnd_file.log,'In If Before Insert of interface -'||lc_item_no||' and warehouse - '||lc_whse_code);

INSERT INTO mtl_system_items_interface( inventory_item_id
, organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, segment1
, process_flag
, item_number
, transaction_type
, sales_account
, set_process_id
)
VALUES ( ln_item_id
, ln_whse_id
, SYSDATE
, ln_user_id
, SYSDATE
, ln_user_id
, ln_login_id
, lc_item_no
, 1
, lc_item_no
, 'UPDATE'
, ln_ccid
, ln_process_id
);

END IF;

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Data Not inserted into tables -'||ln_item_id);
RAISE_APPLICATION_ERROR(-20057,'Error While Insert into Stg Table '||SQLERRM);
END;
END IF;
-----------------------------------------------------------------
-- END IF;
END LOOP;
COMMIT;
Fnd_File.put_line(apps.fnd_file.LOG,'File Uploaded into interface table succussfully');
Fnd_File.put_line(apps.fnd_file.LOG,'Process id for this upload is - '||ln_process_id);

UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Operation: '||' File Not Exit in OTM budget Directory ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20051,'File Not Exit in MTL Inv Directory');
WHEN UTL_FILE.INVALID_PATH THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Path');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20052,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Mode');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20053,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid File Handle ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20054,'Invalid File Handle');
WHEN UTL_FILE.WRITE_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Write Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20055,'Invalid Write Error');
WHEN UTL_FILE.READ_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Read Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20056,'Read Error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20057,'Check the File Data Format '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
END mtl_inv_acct_upload;