This discussion is archived
3 Replies Latest reply: Sep 21, 2011 11:21 PM by 889802 RSS

Service Contracts Billing Schedule Creation Error

717575 Newbie
Currently Being Moderated
Hi,

I have a requirement of creating service contracts from back-end for which I am using the oks_contracts_pub.create_contract_header, create_service_line, and create_bill_schedule. I am creating my contracts with active status and renewal type of EVN (Header level), FUL(Line Level). My contract is of type subscription. The contract header and line are creating fine but the Bill schedule API is throwing an UNEXPECTED ERROR. The billing schedules need to be created based on accounting rule which can be quarterly or monthly. I have included the bill schedule code (hard coded values) for review. Any help is greatly appreciated.

Also the version I am working on is R12

________________________________________________CODE___________________________________________________________________
CREATE OR REPLACE PROCEDURE BILLING_SCHEDULE AS

l_strm_level_tbl OKS_BILL_SCH.STREAMLVL_TBL;

v_bill_qtrs NUMBER := 0;

v_bill_mths NUMBER := 0;

v_bill_days NUMBER := 0;

v_net_amount NUMBER := 468;

v_acct_rule_name VARCHAR2(50) := 'QUARTERLY';

v_amt_per_day NUMBER;

x_error_locator VARCHAR2(2000);

v_start_date DATE := '04-MAY-2010';

v_end_date DATE := '25-MAY-2010';

x_return_status VARCHAr2(3);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

v_billing_sequence NUMBER;

x_chr_id NUMBER := 17000;

x_line_id NUMBER := 223248604345353294444923586786456728480;

g_day_uom VARCHAR2(10) := 'DAY';

g_month_uom VARCHAR2(10) := 'MTH';

g_quarter_uom VARCHAR2(10) := 'QTR';

v_amt NUMBER;

v_invoicing_rule_id NUMBER := -2;



BEGIN

--Create Billing Schedule based on accounting rule



okc_context.set_okc_org_context;

MO_GLOBAL.INIT('OKS');

MO_GLOBAL.SET_POLICY_CONTEXT('S', 83);

v_bill_qtrs := 0;

v_bill_days := 0;

v_bill_mths := 0;



SELECT NVL(v_net_amount, 0)/(v_end_date - v_start_date)

INTO v_amt_per_day

FROM dual;

dbms_output.put_line('Calculated amt per day ' || v_amt_per_day);



IF v_acct_rule_name LIKE '%QUARTERLY%' THEN

--Quarterly billing schedule

SELECT FLOOR(MONTHS_BETWEEN( v_end_date,

v_start_date)/3),

v_end_date - ADD_MONTHS(v_start_date, (FLOOR(MONTHS_BETWEEN(v_end_date, v_start_date)/3)*3))

INTO v_bill_qtrs,

v_bill_days

FROM dual;

dbms_output.put_line('Calculated the qtrs and days');



IF v_bill_qtrs > 0 THEN

SELECT (ADD_MONTHS(v_start_date, (FLOOR(MONTHS_BETWEEN(v_end_date, v_start_date)/3)*3)) - v_start_date)*v_amt_per_day

INTO v_amt

FROM dual;

dbms_output.put_line('Calculated qtr amt');



SELECT NVL(MAX(sequence_no), 0) + 1

INTO v_billing_sequence

FROM oks_stream_levels_b

WHERE dnz_chr_id = x_chr_id

AND cle_id = x_line_id;



--Populating the stream line variables

l_strm_level_tbl(v_billing_sequence).sequence_no := v_billing_sequence;

l_strm_level_tbl(v_billing_sequence).dnz_chr_id := x_chr_id;

-- l_strm_level_tbl(v_billing_sequence).id := x_line_id;

l_strm_level_tbl(v_billing_sequence).cle_id := x_line_id;

l_strm_level_tbl(v_billing_sequence).uom_code := g_quarter_uom;

l_strm_level_tbl(v_billing_sequence).uom_per_period := 1;

l_strm_level_tbl(v_billing_sequence).level_periods := v_bill_qtrs;

l_strm_level_tbl(v_billing_sequence).start_date := v_start_date;

l_strm_level_tbl(v_billing_sequence).end_date := v_end_date;

l_strm_level_tbl(v_billing_sequence).invoice_offset_days := NULL;

l_strm_level_tbl(v_billing_sequence).amount := v_amt/v_bill_qtrs;

l_strm_level_tbl(v_billing_sequence).level_amount := v_amt/v_bill_qtrs;

l_strm_level_tbl(v_billing_sequence).lines_detailed_yn := 'Y';

l_strm_level_tbl(v_billing_sequence).due_arr_yn := 'Y';



dbms_output.put_line('Before calling the Bill Sch API for QTR');

OKS_CONTRACTS_PUB.CREATE_BILL_SCHEDULE ( p_billing_sch=>'E',

p_strm_level_tbl=>l_strm_level_tbl,

p_invoice_rule_id=>v_invoicing_rule_id,

x_return_status=>x_return_status);

dbms_output.put_line('After calling the Bill Sch API for QTR');



IF x_return_status <> 'S' THEN

x_msg_count := 1;

dbms_output.put_line('Quarterly schedule creation error ' || x_msg_data);

---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKS_A');

-- RETURN;

END IF;



END IF;



IF v_bill_days > 0 THEN

SELECT NVL(MAX(sequence_no), 0) + 1

INTO v_billing_sequence

FROM oks_stream_levels_b

WHERE dnz_chr_id = x_chr_id

AND cle_id = x_line_id;

dbms_output.put_line('sequence '||v_billing_sequence);

--Populating the stream line variables

l_strm_level_tbl(v_billing_sequence).sequence_no := v_billing_sequence;

l_strm_level_tbl(v_billing_sequence).dnz_chr_id := x_chr_id;

--l_strm_level_tbl(v_billing_sequence).id := x_line_id;

l_strm_level_tbl(v_billing_sequence).cle_id := x_line_id;

l_strm_level_tbl(v_billing_sequence).uom_code := g_day_uom;

l_strm_level_tbl(v_billing_sequence).uom_per_period := v_bill_days;

l_strm_level_tbl(v_billing_sequence).level_periods := 1;

l_strm_level_tbl(v_billing_sequence).start_date := v_start_date;

l_strm_level_tbl(v_billing_sequence).end_date := v_end_date;

l_strm_level_tbl(v_billing_sequence).invoice_offset_days := NULL;

l_strm_level_tbl(v_billing_sequence).amount := v_net_amount;--v_amt_per_day*v_bill_days;

l_strm_level_tbl(v_billing_sequence).level_amount := v_net_amount;--v_amt_per_day*v_bill_days;

l_strm_level_tbl(v_billing_sequence).lines_detailed_yn := 'Y';

l_strm_level_tbl(v_billing_sequence).due_arr_yn := 'Y';



dbms_output.put_line('Before calling the Bill Sch API for QTR days');

OKS_CONTRACTS_PUB.CREATE_BILL_SCHEDULE ( p_billing_sch=>'E',

p_strm_level_tbl=>l_strm_level_tbl,

p_invoice_rule_id=>v_invoicing_rule_id,

x_return_status=>x_return_status);

dbms_output.put_line('After calling the Bill Sch API for QTR days ' || v_bill_days || ' ' || x_return_status);



IF x_return_status <> 'S' THEN

x_msg_count := 1;

dbms_output.put_line('Day schedule, for quarterly accounting rule, creation error ' || x_msg_data);

--DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKS_A');

--RETURN;

END IF;



END IF;

ELSE

--Monthly billing schedule

SELECT FLOOR(MONTHS_BETWEEN( v_end_date,

v_start_date)),

v_end_date - ADD_MONTHS(v_start_date, FLOOR(MONTHS_BETWEEN(v_end_date, v_start_date)))

INTO v_bill_mths,

v_bill_days

FROM dual;

dbms_output.put_line('Calculated the mths and days');



IF v_bill_mths > 0 THEN

SELECT (ADD_MONTHS(v_start_date,FLOOR(MONTHS_BETWEEN(v_end_date, v_start_date))) - v_start_date)*v_amt_per_day

INTO v_amt

FROM dual;

dbms_output.put_line('Calculated the mths amt');



SELECT NVL(MAX(sequence_no), 0) + 1

INTO v_billing_sequence

FROM oks_stream_levels_b

WHERE dnz_chr_id = x_chr_id

AND cle_id = x_line_id;



--Populating the stream line variables

l_strm_level_tbl(v_billing_sequence).sequence_no := v_billing_sequence;

l_strm_level_tbl(v_billing_sequence).dnz_chr_id := x_chr_id;

--l_strm_level_tbl(v_billing_sequence).id := x_line_id;

l_strm_level_tbl(v_billing_sequence).cle_id := x_line_id;

l_strm_level_tbl(v_billing_sequence).uom_code := g_month_uom;

l_strm_level_tbl(v_billing_sequence).uom_per_period := 1;

l_strm_level_tbl(v_billing_sequence).level_periods := v_bill_mths;

l_strm_level_tbl(v_billing_sequence).start_date := v_start_date;

l_strm_level_tbl(v_billing_sequence).end_date := v_end_date;

l_strm_level_tbl(v_billing_sequence).invoice_offset_days := NULL;

l_strm_level_tbl(v_billing_sequence).amount := v_amt/v_bill_mths;

l_strm_level_tbl(v_billing_sequence).level_amount := v_amt/v_bill_mths;

l_strm_level_tbl(v_billing_sequence).lines_detailed_yn := 'Y';

l_strm_level_tbl(v_billing_sequence).due_arr_yn := 'Y';



dbms_output.put_line('Before calling the Bill Sch API for MTHS');

OKS_CONTRACTS_PUB.CREATE_BILL_SCHEDULE ( p_billing_sch=>'E',

p_strm_level_tbl=>l_strm_level_tbl,

p_invoice_rule_id=>v_invoicing_rule_id,

x_return_status=>x_return_status);

dbms_output.put_line('After calling the Bill Sch API for MTHS');



IF x_return_status <> 'S' THEN

x_msg_count := 1;

dbms_output.put_line('Monthly schedule creation error ' || x_msg_data);

--DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKS_A');

--RETURN;

END IF;



END IF;



IF v_bill_days > 0 THEN

SELECT NVL(MAX(sequence_no), 0) + 1

INTO v_billing_sequence

FROM oks_stream_levels_b

WHERE dnz_chr_id = x_chr_id

AND cle_id = x_line_id;



--Populating the stream line variables

l_strm_level_tbl(v_billing_sequence).sequence_no := v_billing_sequence;

l_strm_level_tbl(v_billing_sequence).dnz_chr_id := x_chr_id;

--l_strm_level_tbl(v_billing_sequence).id := x_line_id;

l_strm_level_tbl(v_billing_sequence).cle_id := x_line_id;

l_strm_level_tbl(v_billing_sequence).uom_code := g_day_uom;

l_strm_level_tbl(v_billing_sequence).uom_per_period := v_bill_days;

l_strm_level_tbl(v_billing_sequence).level_periods := 1;

l_strm_level_tbl(v_billing_sequence).start_date := v_start_date;

l_strm_level_tbl(v_billing_sequence).end_date := v_end_date;

l_strm_level_tbl(v_billing_sequence).invoice_offset_days := NULL;

l_strm_level_tbl(v_billing_sequence).amount := v_amt_per_day*v_bill_days;

l_strm_level_tbl(v_billing_sequence).level_amount := v_amt_per_day*v_bill_days;

l_strm_level_tbl(v_billing_sequence).lines_detailed_yn := 'Y';

l_strm_level_tbl(v_billing_sequence).due_arr_yn := 'Y';



dbms_output.put_line('Before calling the Bill Sch API for MTH days');

OKS_CONTRACTS_PUB.CREATE_BILL_SCHEDULE ( p_billing_sch=>'E',

p_strm_level_tbl=>l_strm_level_tbl,

p_invoice_rule_id=>v_invoicing_rule_id,

x_return_status=>x_return_status);

dbms_output.put_line('After calling the Bill Sch API for MTH days');

IF x_return_status <> 'S' THEN

x_msg_count := 1;

dbms_output.put_line('Day schedule, for monthly accounting rule, creation error ' || x_msg_data);

--DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKS_A');

--RETURN;

END IF;



END IF;

END IF;

COMMIT;

EXCEPTION

when others then

dbms_output.put_line('Error ' || sqlerrm);

END;
_______________________________________________________________________________________________________________________________

Thanks,
Yash.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points