The Below code Describes The multiple Billing Schedule for contract that is created through Order Management.
Issue : When we create service Contract from Order management we cant use the Multiple billing schedule option of Service Contracts Module.
Solution :
Refrences :http://www.oraclebusinessapps.com/2008/07/26/r12-service-contract-from-order-management-part-iii/
1. Create a temp Table
------------------------------------------------------------------------------------------------------------
/*<Temprory Table to Store Order Details Service LIne >*/
CREATE TABLE XX_OKS_OM_TST_TBL
(
FROM_INTEGRATION VARCHAR2(300 BYTE),
TRANSACTION_TYPE VARCHAR2(100 BYTE),
TRANSACTION_DATE DATE,
ORDER_LINE_ID NUMBER,
OLD_INSTANCE_ID NUMBER,
NEW_INSTANCE_ID NUMBER,
CHR_ID NUMBER,
TOPLINE_ID NUMBER,
SUBLINE_ID NUMBER
);
/
------------------------------------------------------------------------------------------------------------
2.
CREATE OR REPLACE PACKAGE APPS.xx_alter_contract_from_om
IS
PROCEDURE prepare_contract (
p_from_integration IN VARCHAR2
, p_transaction_type IN VARCHAR2
, p_transaction_date IN DATE
, p_order_line_id IN NUMBER
, p_old_instance_id IN NUMBER
, p_new_instance_id IN NUMBER
, p_chr_id IN NUMBER
, p_topline_id IN NUMBER
, p_subline_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
END xx_alter_contract_from_om;
/
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.xx_alter_contract_from_om
IS
PROCEDURE prepare_contract (
p_from_integration IN VARCHAR2
, p_transaction_type IN VARCHAR2
, p_transaction_date IN DATE
, p_order_line_id IN NUMBER
, p_old_instance_id IN NUMBER
, p_new_instance_id IN NUMBER
, p_chr_id IN NUMBER
, p_topline_id IN NUMBER
, p_subline_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_bclv_rec_type oks_billcontline_pub.bclv_rec_type;
l_bslv_rec_type oks_billsubline_pub.bslv_rec_type;
x_bill_cont_status VARCHAR2 (1);
x_sub_line_status VARCHAR2 (1);
x_rgp_id NUMBER;
x_status VARCHAR2 (1);
l_top_months NUMBER;
l_sub_months NUMBER;
l_order_uom VARCHAR2 (10);
BEGIN
okc_context.set_okc_org_context (p_chr_id => p_chr_id);
fnd_global.apps_initialize (1005902, 21708, 515);
/*==========================================================================
|select order UOM. This logic is to create billing schedules with UOM month.|
|So create an order for the service item with MTH has UOM or create item |
|with UOM of MTH so that it defaults into order line. This UOM will used in |
|updating the stream headers later |
+==========================================================================*/
BEGIN
SELECT order_quantity_uom
INTO l_order_uom
FROM oe_order_lines
WHERE line_id = p_order_line_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/*==========================================================================
|Since our intention is to create multiple schedules compared to what OKS |
|Contract Order Capture Program creates, we need to select number of months |
|between start date and End Date. This logic gets harder if you do not |
|populate the same. It is not required to be populated because the start |
|date can be calculated based on warranty start date or fulfillment date |
+==========================================================================*/
BEGIN
SELECT CEIL (MONTHS_BETWEEN (end_date, start_date))
INTO l_top_months
FROM okc_k_lines_b
WHERE ID = p_topline_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT CEIL (MONTHS_BETWEEN (end_date, start_date))
INTO l_sub_months
FROM okc_k_lines_b
WHERE ID = p_subline_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT ID
INTO l_bslv_rec_type.ID
FROM oks_bill_sub_lines
WHERE cle_id = p_subline_id;
/*==========================================================================
|Now delete the billing tables. This table is populated to avoid billing for|
|these contracts. This needs to be deleted.Subline here. |
+==========================================================================*/
oks_billsubline_pub.delete_bill_subline_pub (p_api_version => 1.0
, p_init_msg_list => okc_api.g_false
, x_return_status => x_sub_line_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_bslv_rec => l_bslv_rec_type
);
x_return_status := x_sub_line_status;
DBMS_OUTPUT.put_line ('Return Status of deleting subline billing record :' || x_sub_line_status);
DBMS_OUTPUT.put_line ('msg count of deleting sub line billing record:' || x_msg_count);
IF x_sub_line_status = 'S'
THEN
/*==========================================================================
|Delete existing billing schedules as we have to recreate them. Blind delete|
|is OK as this schedule comes with completed date and never got billed before|
+==========================================================================*/
DELETE oks_level_elements
WHERE cle_id = p_subline_id;
/*============================================================================
|Update the number of level periods to the number of periods in months so that|
|billing can happen monthly.Update UOM too!! |
+==========================================================================*/
UPDATE oks_stream_levels_b
SET level_periods = l_sub_months
, uom_code = l_order_uom
, uom_per_period=1
WHERE cle_id = p_subline_id;
x_status := 'S';
--oks_bill_util_pub.refresh_bill_sch (p_cle_id => i.ID, x_rgp_id => x_rgp_id, x_status => x_status);
DBMS_OUTPUT.put_line ('Return Status of refreshing bill schedules is:' || x_status);
x_msg_data := NULL;
x_msg_count := NULL;
x_return_status := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT ID
INTO l_bclv_rec_type.ID
FROM oks_bill_cont_lines
WHERE cle_id = p_topline_id;
/*==========================================================================
|Now delete the billing tables. This table is populated to avoid billing for|
|these contracts. This needs to be deleted.Top Linehere. |
+==========================================================================*/
oks_billcontline_pub.delete_bill_cont_line (p_api_version => 1.0
, p_init_msg_list => okc_api.g_false
, x_return_status => x_bill_cont_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_bclv_rec => l_bclv_rec_type
);
x_return_status := x_bill_cont_status;
DBMS_OUTPUT.put_line ('Return Status of deleting contractline billing record:' || x_bill_cont_status);
DBMS_OUTPUT.put_line ('msg count of deleting contractline billing record:' || x_msg_count);
IF x_bill_cont_status = 'S'
THEN
/*==========================================================================
|Delete existing billing schedules as we have to recreate them. Blind delete|
|is OK as this schedule comes with completed date and never got billed before|
+==========================================================================*/
DELETE oks_level_elements
WHERE cle_id = p_topline_id;
/*============================================================================
|Update the number of level periods to the number of periods in months so that|
|billing can happen monthly.Update UOM too!! |
+==========================================================================*/
UPDATE oks_stream_levels_b
SET level_periods = l_top_months
, uom_code = l_order_uom
, uom_per_period=1
WHERE cle_id = p_topline_id;
x_status := 'S';
DBMS_OUTPUT.put_line ('Return Status of refreshing bill schedules is:' || x_status);
x_msg_data := NULL;
x_msg_count := NULL;
x_return_status := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF (x_bill_cont_status <> 'S' OR x_sub_line_status <> 'S' OR x_status <> 'S')
THEN
ROLLBACK;
END IF;
/*============================================================================
|Now create new billing schedules |
+==========================================================================*/
oks_bill_sch.create_bill_sch_cp (p_top_line_id => p_topline_id
, p_cp_line_id => p_subline_id
, p_cp_new => 'Y'
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ('Return Status of refreshing bill schedules is:' || x_return_status);
DBMS_OUTPUT.put_line ('msg count of refreshing bill sch:' || x_msg_count);
/*=================================================================================
|Finally update the related objects table so that this contract line can be billed!|
+==================================================================================*/
UPDATE okc_k_rel_objs
SET cle_id = -1
WHERE cle_id = p_subline_id;
END;
END xx_alter_contract_from_om;
/
------------------------------------------------------------------------------------------------------------
Issue : When we create service Contract from Order management we cant use the Multiple billing schedule option of Service Contracts Module.
Solution :
Refrences :http://www.oraclebusinessapps.com/2008/07/26/r12-service-contract-from-order-management-part-iii/
1. Create a temp Table
------------------------------------------------------------------------------------------------------------
/*<Temprory Table to Store Order Details Service LIne >*/
CREATE TABLE XX_OKS_OM_TST_TBL
(
FROM_INTEGRATION VARCHAR2(300 BYTE),
TRANSACTION_TYPE VARCHAR2(100 BYTE),
TRANSACTION_DATE DATE,
ORDER_LINE_ID NUMBER,
OLD_INSTANCE_ID NUMBER,
NEW_INSTANCE_ID NUMBER,
CHR_ID NUMBER,
TOPLINE_ID NUMBER,
SUBLINE_ID NUMBER
);
/
------------------------------------------------------------------------------------------------------------
2.
CREATE OR REPLACE PACKAGE APPS.xx_alter_contract_from_om
IS
PROCEDURE prepare_contract (
p_from_integration IN VARCHAR2
, p_transaction_type IN VARCHAR2
, p_transaction_date IN DATE
, p_order_line_id IN NUMBER
, p_old_instance_id IN NUMBER
, p_new_instance_id IN NUMBER
, p_chr_id IN NUMBER
, p_topline_id IN NUMBER
, p_subline_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
END xx_alter_contract_from_om;
/
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.xx_alter_contract_from_om
IS
PROCEDURE prepare_contract (
p_from_integration IN VARCHAR2
, p_transaction_type IN VARCHAR2
, p_transaction_date IN DATE
, p_order_line_id IN NUMBER
, p_old_instance_id IN NUMBER
, p_new_instance_id IN NUMBER
, p_chr_id IN NUMBER
, p_topline_id IN NUMBER
, p_subline_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_bclv_rec_type oks_billcontline_pub.bclv_rec_type;
l_bslv_rec_type oks_billsubline_pub.bslv_rec_type;
x_bill_cont_status VARCHAR2 (1);
x_sub_line_status VARCHAR2 (1);
x_rgp_id NUMBER;
x_status VARCHAR2 (1);
l_top_months NUMBER;
l_sub_months NUMBER;
l_order_uom VARCHAR2 (10);
BEGIN
okc_context.set_okc_org_context (p_chr_id => p_chr_id);
fnd_global.apps_initialize (1005902, 21708, 515);
/*==========================================================================
|select order UOM. This logic is to create billing schedules with UOM month.|
|So create an order for the service item with MTH has UOM or create item |
|with UOM of MTH so that it defaults into order line. This UOM will used in |
|updating the stream headers later |
+==========================================================================*/
BEGIN
SELECT order_quantity_uom
INTO l_order_uom
FROM oe_order_lines
WHERE line_id = p_order_line_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/*==========================================================================
|Since our intention is to create multiple schedules compared to what OKS |
|Contract Order Capture Program creates, we need to select number of months |
|between start date and End Date. This logic gets harder if you do not |
|populate the same. It is not required to be populated because the start |
|date can be calculated based on warranty start date or fulfillment date |
+==========================================================================*/
BEGIN
SELECT CEIL (MONTHS_BETWEEN (end_date, start_date))
INTO l_top_months
FROM okc_k_lines_b
WHERE ID = p_topline_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT CEIL (MONTHS_BETWEEN (end_date, start_date))
INTO l_sub_months
FROM okc_k_lines_b
WHERE ID = p_subline_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT ID
INTO l_bslv_rec_type.ID
FROM oks_bill_sub_lines
WHERE cle_id = p_subline_id;
/*==========================================================================
|Now delete the billing tables. This table is populated to avoid billing for|
|these contracts. This needs to be deleted.Subline here. |
+==========================================================================*/
oks_billsubline_pub.delete_bill_subline_pub (p_api_version => 1.0
, p_init_msg_list => okc_api.g_false
, x_return_status => x_sub_line_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_bslv_rec => l_bslv_rec_type
);
x_return_status := x_sub_line_status;
DBMS_OUTPUT.put_line ('Return Status of deleting subline billing record :' || x_sub_line_status);
DBMS_OUTPUT.put_line ('msg count of deleting sub line billing record:' || x_msg_count);
IF x_sub_line_status = 'S'
THEN
/*==========================================================================
|Delete existing billing schedules as we have to recreate them. Blind delete|
|is OK as this schedule comes with completed date and never got billed before|
+==========================================================================*/
DELETE oks_level_elements
WHERE cle_id = p_subline_id;
/*============================================================================
|Update the number of level periods to the number of periods in months so that|
|billing can happen monthly.Update UOM too!! |
+==========================================================================*/
UPDATE oks_stream_levels_b
SET level_periods = l_sub_months
, uom_code = l_order_uom
, uom_per_period=1
WHERE cle_id = p_subline_id;
x_status := 'S';
--oks_bill_util_pub.refresh_bill_sch (p_cle_id => i.ID, x_rgp_id => x_rgp_id, x_status => x_status);
DBMS_OUTPUT.put_line ('Return Status of refreshing bill schedules is:' || x_status);
x_msg_data := NULL;
x_msg_count := NULL;
x_return_status := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT ID
INTO l_bclv_rec_type.ID
FROM oks_bill_cont_lines
WHERE cle_id = p_topline_id;
/*==========================================================================
|Now delete the billing tables. This table is populated to avoid billing for|
|these contracts. This needs to be deleted.Top Linehere. |
+==========================================================================*/
oks_billcontline_pub.delete_bill_cont_line (p_api_version => 1.0
, p_init_msg_list => okc_api.g_false
, x_return_status => x_bill_cont_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_bclv_rec => l_bclv_rec_type
);
x_return_status := x_bill_cont_status;
DBMS_OUTPUT.put_line ('Return Status of deleting contractline billing record:' || x_bill_cont_status);
DBMS_OUTPUT.put_line ('msg count of deleting contractline billing record:' || x_msg_count);
IF x_bill_cont_status = 'S'
THEN
/*==========================================================================
|Delete existing billing schedules as we have to recreate them. Blind delete|
|is OK as this schedule comes with completed date and never got billed before|
+==========================================================================*/
DELETE oks_level_elements
WHERE cle_id = p_topline_id;
/*============================================================================
|Update the number of level periods to the number of periods in months so that|
|billing can happen monthly.Update UOM too!! |
+==========================================================================*/
UPDATE oks_stream_levels_b
SET level_periods = l_top_months
, uom_code = l_order_uom
, uom_per_period=1
WHERE cle_id = p_topline_id;
x_status := 'S';
DBMS_OUTPUT.put_line ('Return Status of refreshing bill schedules is:' || x_status);
x_msg_data := NULL;
x_msg_count := NULL;
x_return_status := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF (x_bill_cont_status <> 'S' OR x_sub_line_status <> 'S' OR x_status <> 'S')
THEN
ROLLBACK;
END IF;
/*============================================================================
|Now create new billing schedules |
+==========================================================================*/
oks_bill_sch.create_bill_sch_cp (p_top_line_id => p_topline_id
, p_cp_line_id => p_subline_id
, p_cp_new => 'Y'
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ('Return Status of refreshing bill schedules is:' || x_return_status);
DBMS_OUTPUT.put_line ('msg count of refreshing bill sch:' || x_msg_count);
/*=================================================================================
|Finally update the related objects table so that this contract line can be billed!|
+==================================================================================*/
UPDATE okc_k_rel_objs
SET cle_id = -1
WHERE cle_id = p_subline_id;
END;
END xx_alter_contract_from_om;
/
------------------------------------------------------------------------------------------------------------
