DECLARE /* ===================================== Variable Declaration ======================================*/ l_precision NUMBER := 0; l_extended_precision NUMBER := 0; l_retcode NUMBER := 0; vo_return_status VARCHAR2(1) := NULL; vo_msg_count NUMBER := 0; vo_msg_data VARCHAR2(2000); v_count NUMBER := 1; r_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type; r_price_list_rec_dum QP_PRICE_LIST_PUB.Price_List_Rec_Type; /*Dummy*/ r_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type; t_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type; t_price_list_line_tbl_dum QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type; /*Dummy*/ t_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type; t_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type; t_qualifiers_tbl_dum QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type; /*Dummy*/ t_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type; t_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type; t_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type; rr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type; rr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type; tr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type; tr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type; tr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type; tr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type; tr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type; tr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type; e_NUIF_VALUE_ERR1 EXCEPTION; e_NUIF_VALUE_ERR2 EXCEPTION; e_NUIF_VALUE_ERR EXCEPTION; e_CONCURRENT_ERR EXCEPTION; /*====================================== Who Columns INfo And Profile Value =======================================*/ g_user_id NUMBER; g_login_id NUMBER; g_request_id NUMBER; g_prog_appl_id NUMBER; g_prog_id NUMBER; g_precision_type VARCHAR2(240) := NULL; g_organization_id NUMBER := 0; /* Get Rounding Factor */ CURSOR rounding_factor_c IS SELECT fcv.precision * -1, fcv.extended_precision * -1 FROM gl_sets_of_books gsob, fnd_currencies_vl fcv, financials_system_parameters fsp WHERE gsob.set_of_books_id = fsp.set_of_books_id AND fcv.currency_code = gsob.currency_code; BEGIN g_user_id := FND_GLOBAL.USER_ID; g_login_id := FND_GLOBAL.LOGIN_ID; g_request_id := FND_GLOBAL.CONC_REQUEST_ID; g_prog_appl_id := FND_GLOBAL.PROG_APPL_ID; g_prog_id := FND_GLOBAL.CONC_PROGRAM_ID; g_precision_type := FND_PROFILE.VALUE('OE_UNIT_PRICE_PRECISION_TYPE'); g_organization_id := FND_PROFILE.VALUE('SO_ORGANIZATION_ID'); DBMS_OUTPUT.PUT_LINE('Inside 1st Begin'); /*===================================== Get Rounding Factor =====================================*/ OPEN rounding_factor_c; FETCH rounding_factor_c INTO l_precision, l_extended_precision; CLOSE rounding_factor_c; DBMS_OUTPUT.PUT_LINE('Rounding Cursor fetched'); BEGIN /*Insert header data into record type variable*/ r_price_list_rec.list_header_id := 13;--nuif_pli_rec1.price_list_id; r_price_list_rec.creation_date := SYSDATE; r_price_list_rec.created_by := g_user_id; r_price_list_rec.last_update_date := SYSDATE; r_price_list_rec.last_updated_by := g_user_id; r_price_list_rec.last_update_login := g_login_id; r_price_list_rec.program_application_id := g_prog_appl_id; r_price_list_rec.program_id := g_prog_id; r_price_list_rec.program_update_date := SYSDATE; r_price_list_rec.request_id := g_request_id; r_price_list_rec.name := 'Test Name';--nuif_pli_rec1.name; r_price_list_rec.currency_code := 'curr_code';--nuif_pli_rec1.currency_code; r_price_list_rec.rounding_factor := 1;--nuif_pli_rec1.rounding_factor; r_price_list_rec.ship_method_code := NULL; r_price_list_rec.freight_terms_code := NULL; r_price_list_rec.terms_id := NULL; r_price_list_rec.start_date_active := sysdate;--TRUNC(nuif_pli_rec1.start_date_active); r_price_list_rec.end_date_active := sysdate;--TRUNC(nuif_pli_rec1.end_date_active); r_price_list_rec.comments := 'comments';--nuif_pli_rec1.comments; r_price_list_rec.context := 'nuif_pli_rec1.context'; r_price_list_rec.attribute1 := 'nuif_pli_rec1.attribute1'; r_price_list_rec.attribute2 := 'nuif_pli_rec1.attribute2'; r_price_list_rec.attribute3 := 'nuif_pli_rec1.attribute3'; r_price_list_rec.attribute4 := 'nuif_pli_rec1.attribute4'; r_price_list_rec.attribute5 := 'nuif_pli_rec1.attribute5'; r_price_list_rec.attribute6 := 'nuif_pli_rec1.attribute6'; r_price_list_rec.attribute7 := 'nuif_pli_rec1.attribute7'; r_price_list_rec.attribute8 := 'nuif_pli_rec1.attribute8'; r_price_list_rec.attribute9 := 'nuif_pli_rec1.attribute9'; r_price_list_rec.attribute10 := 'nuif_pli_rec1.attribute10'; r_price_list_rec.attribute11 := 'nuif_pli_rec1.attribute11'; r_price_list_rec.attribute12 := 'nuif_pli_rec1.attribute12'; r_price_list_rec.attribute13 := 'nuif_pli_rec1.attribute13'; r_price_list_rec.attribute14 := 'nuif_pli_rec1.attribute14'; r_price_list_rec.attribute15 := 'nuif_pli_rec1.attribute15'; r_price_list_rec.description := 'nuif_pli_rec1.description'; r_price_list_rec.version_no := NULL; /*Insert header data into record type variable(End)*/ /*Insert qualifier data into table type variable (start)*/ select qp_qualifiers_s.nextval into t_qualifiers_tbl(v_count).qualifier_id from dual; t_qualifiers_tbl(v_count).creation_date := SYSDATE; t_qualifiers_tbl(v_count).created_by := g_user_id; t_qualifiers_tbl(v_count).last_update_date := SYSDATE; t_qualifiers_tbl(v_count).last_updated_by := g_user_id; t_qualifiers_tbl(v_count).last_update_login := g_login_id; select qp_qualifier_group_no_s.nextval into t_qualifiers_tbl(v_count).qualifier_grouping_no from dual; t_qualifiers_tbl(v_count).qualifier_context := 'modlist'; t_qualifiers_tbl(v_count).qualifier_attribute := 'qualifier_attribute4'; t_qualifiers_tbl(v_count).qualifier_attr_value := 13;--nuif_pli_rec1.price_list_id; t_qualifiers_tbl(v_count).comparison_operator_code := '='; t_qualifiers_tbl(v_count).excluder_flag := 'N'; t_qualifiers_tbl(v_count).qualifier_rule_id := NULL; t_qualifiers_tbl(v_count).start_date_active := SYSDATE;--TRUNC(nuif_pli_rec1.start_date_active); t_qualifiers_tbl(v_count).end_date_active := SYSDATE;--TRUNC(nuif_pli_rec1.end_date_active); t_qualifiers_tbl(v_count).list_header_id := 13;--nuif_pli_rec1.price_list_id; t_qualifiers_tbl(v_count).list_line_id := NULL; t_qualifiers_tbl(v_count).qualifier_datatype := NULL; t_qualifiers_tbl(v_count).qualifier_attr_value_to := NULL; t_qualifiers_tbl(v_count).context := NULL; /*Insert qualifier data into table type variable (end)*/ /*Call API for inserting header and Qualifier Data */ QP_PRICE_LIST_PUB.Process_Price_List ( p_api_version_number => 1 , p_init_msg_list => FND_API.G_FALSE , p_return_values => FND_API.G_FALSE , p_commit => FND_API.G_FALSE , x_return_status => vo_return_status , x_msg_count => vo_msg_count , x_msg_data => vo_msg_data , p_PRICE_LIST_rec => r_price_list_rec , p_PRICE_LIST_val_rec => r_price_list_val_rec , p_PRICE_LIST_LINE_tbl => t_price_list_line_tbl_dum , p_PRICE_LIST_LINE_val_tbl => t_price_list_line_val_tbl , p_QUALIFIERS_tbl => t_qualifiers_tbl , p_QUALIFIERS_val_tbl => t_qualifiers_val_tbl , p_PRICING_ATTR_tbl => t_pricing_attr_tbl , p_PRICING_ATTR_val_tbl => t_pricing_attr_val_tbl , x_PRICE_LIST_rec => rr_price_list_rec , x_PRICE_LIST_val_rec => rr_price_list_val_rec , x_PRICE_LIST_LINE_tbl => tr_price_list_line_tbl , x_PRICE_LIST_LINE_val_tbl=> tr_price_list_line_val_tbl , x_QUALIFIERS_tbl => tr_qualifiers_tbl , x_QUALIFIERS_val_tbl => tr_qualifiers_val_tbl , x_PRICING_ATTR_tbl => tr_pricing_attr_tbl , x_PRICING_ATTR_val_tbl => tr_pricing_attr_val_tbl ); DBMS_OUTPUT.PUT_LINE('API called Successfully'); DBMS_OUTPUT.PUT_LINE(vo_return_status); DBMS_OUTPUT.PUT_LINE(vo_msg_count); DBMS_OUTPUT.PUT_LINE(vo_msg_data); commit; if vo_return_status <> FND_API.G_RET_STS_SUCCESS THEN DBMS_OUTPUT.PUT_LINE('API UNSUCCESSFUL'); ELSIF vo_return_status = FND_API.G_RET_STS_SUCCESS THEN DBMS_OUTPUT.PUT_LINE('API SUCCESSFUL'); ELSE DBMS_OUTPUT.PUT_LINE('RETURN STATUS UNKNOWN'); END IF; v_count := v_count + 1; EXCEPTION WHEN e_NUIF_VALUE_ERR1 THEN DBMS_OUTPUT.PUT_LINE('e_NUIF_VALUE_ERR1'); COMMIT; WHEN e_CONCURRENT_ERR THEN DBMS_OUTPUT.PUT_LINE('e_CONCURRENT_ERR'); ROLLBACK; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OTHERS'); IF rounding_factor_c%ISOPEN THEN CLOSE rounding_factor_c; END IF; ROLLBACK; END; DBMS_OUTPUT.PUT_LINE('At the end'); END;