Home » SQL & PL/SQL » SQL & PL/SQL » URGENT:Problem with API call in stored procedure
URGENT:Problem with API call in stored procedure [message #302651] Tue, 26 February 2008 08:14 Go to next message
moumita_mitra
Messages: 1
Registered: May 2007
Junior Member
Hi ,

My requirement is to load Oracle Apps base Table via QP_PRICE_LIST_PUB.Process_Price_List API from Oracle Apps Interface Table.

As per technical guideline I have to call that API from a stored procedure.I have written following stored proc which will
1) first checks the existence of data in the four interface table
2) then call the API to load the data into the base table.
-------------------------------------------
/*----------PROCEDURE TO Upload the Price---------------------------------*/

CREATE OR REPLACE PROCEDURE Process_Upload_Pricelist (P_OUT_MESSG		OUT VARCHAR2,
				                      P_OUT_RETURN_STAT      OUT VARCHAR2)
				 
AS
Cursor Cur_List_Headers 
IS
Select list_header_id
,Name
,List_Type_Code
,Start_Date_Active
,End_Date_Active
FROM Qp_Interface_List_Headers;

Cursor Cur_List_Lines (CP_LIST_HEADER_ID		        NUMBER)                      
IS
Select list_header_id
,List_Line_Id
,Primary_Uom_Flag
,List_Line_Type_Code
,Price_Break_Type_Code
,Arithmetic_Operator
,Operand
,Price_By_Formula_Id
,Generate_Using_Formula_Id
FROM Qp_Interface_List_Lines
WHERE
list_header_id =CP_LIST_HEADER_ID;
 

Cursor Cur_Pricing_Attribs (CP_LIST_HEADER_ID		        NUMBER,
                            CP_LIST_LINE_ID	 		Number)  
IS
Select list_header_id
,List_Line_Id
,Product_Attribute_Context
,Product_Attribute
,Product_Attr_Value
,Product_Uom_Code
FROM Qp_Interface_Pricing_Attribs
WHERE
list_header_id =CP_LIST_HEADER_ID
AND List_Line_Id =CP_LIST_LINE_ID;

Cursor Cur_Qualifiers(CP_LIST_HEADER_ID		        NUMBER,
                      CP_LIST_LINE_ID	 		Number)  
IS
Select list_header_id
,List_Line_Id
,Qualifier_Precedence 
FROM Qp_Interface_Qualifiers
WHERE
list_header_id =CP_LIST_HEADER_ID
AND List_Line_Id =CP_LIST_LINE_ID;


				 
x_return_status varchar2(1)                := NULL;
x_msg_count number                         := 0;
x_msg_data varchar2(2000);
p_PRICE_LIST_rec                            QP_PRICE_LIST_PUB.Price_List_Rec_Type;
p_PRICE_LIST_LINE_tbl                       QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
p_PRICING_ATTR_tbl                          QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
x_PRICE_LIST_rec                            QP_PRICE_LIST_PUB.Price_List_Rec_Type;
x_PRICE_LIST_val_rec                        QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
x_PRICE_LIST_LINE_tbl                       QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
x_PRICE_LIST_LINE_val_tbl                   QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
x_QUALIFIERS_tbl                            QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
x_QUALIFIERS_val_tbl                        QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
x_PRICING_ATTR_tbl                          QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
x_PRICING_ATTR_val_tbl                      QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
x_version VARCHAR2(240);
qi_lh number:=1;
qi_ll number:=1;
qi_pa number:=1;
qi_q number:=1;
v_count NUMBER (20) := 0;

begin

-- The statments below help the user in turning debug on
oe_debug_pub.SetDebugLevel(10);
oe_debug_pub.Initialize;
oe_debug_pub.debug_on;
-- Passing Information to the Pricing Engine
-- Setting up the control record variables
 For qi_lh in Cur_List_Headers
 LOOP
    For qi_ll in Cur_List_Lines (qi_lh.list_header_id)
    LOOP
	  For qi_pa in Cur_Pricing_Attribs (qi_ll.list_header_id,qi_ll.List_Line_Id)
      LOOP
        For qi_q in Cur_Qualifiers (qi_pa.list_header_id,qi_pa.List_Line_Id)
        LOOP 
		  v_count := v_count + 1;
         ---- For Price header-----------------
          /* Setting up QP_PRICE_LIST_PUB.Price_List_Rec_Type parameters */
          p_PRICE_LIST_rec.list_header_id := qi_lh.list_header_id; -- Primary Key (required)
          p_PRICE_LIST_rec.name := qi_lh.Name; --Mandatory
          p_PRICE_LIST_rec.list_type_code := qi_lh.List_Type_Code; -- Required (Y/N)
          p_PRICE_LIST_rec.start_date_active := qi_lh.Start_Date_Active; --required
          p_PRICE_LIST_rec.end_date_active := qi_lh.End_Date_Active;
          --p_PRICE_LIST_rec.currency_code := 'USD'; -- Mandatory
          p_PRICE_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE; --Mandatory (QP_GLOBALS.G_OPR_CREATE[create price list] OR QP_GLOBALS.G_OPR_UPDATE [updates price list])         
                                                                
          /* Setting up QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type parameters */
          p_PRICE_LIST_LINE_tbl(v_count).list_line_id := qi_ll.List_Line_Id; -- Line Identifier (required)
          p_PRICE_LIST_LINE_tbl(v_count).list_line_type_code := qi_ll.List_Line_Type_Code;
          p_PRICE_LIST_LINE_tbl(v_count).operation := QP_GLOBALS.G_OPR_CREATE; --(QP_GLOBALS.G_OPR_CREATE[create price list] OR QP_GLOBALS.G_OPR_UPDATE [updates price list])         
          p_PRICE_LIST_LINE_tbl(v_count).operand := qi_ll.Operand; --operand stores the price of the item (required)
          p_PRICE_LIST_LINE_tbl(v_count).arithmetic_operator := qi_ll.Arithmetic_Operator;
          p_PRICE_LIST_LINE_tbl(v_count).price_break_type_code  := qi_ll.Price_Break_Type_Code;
          p_PRICE_LIST_LINE_tbl(v_count).Primary_Uom_Flag  := qi_ll.Primary_Uom_Flag;
          p_PRICE_LIST_LINE_tbl(v_count).Price_By_Formula_Id := qi_ll.Price_By_Formula_Id; -- Not required (NULL)
          p_PRICE_LIST_LINE_tbl(v_count).Generate_Using_Formula_Id := qi_ll.Generate_Using_Formula_Id; -- Not required (NULL)
          
          /* Setting up QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type parameters */          
          --p_PRICING_ATTR_tbl(v_count).pricing_attribute_id := FND_API.G_MISS_NUM;
          p_PRICING_ATTR_tbl(v_count).list_line_id := qi_pa.List_Line_Id;
          p_PRICING_ATTR_tbl(v_count).PRODUCT_ATTRIBUTE_CONTEXT := qi_pa.Product_Attribute_Context ; --Mandatory (Item Constant)
          p_PRICING_ATTR_tbl(v_count).PRODUCT_ATTRIBUTE := qi_pa.Product_Attribute; --Mandatory (Item Number Constant)
          p_PRICING_ATTR_tbl(v_count).PRODUCT_ATTR_VALUE := qi_pa.Product_Attr_Value; --Mandatory (inventory_item_id)
          p_PRICING_ATTR_tbl(v_count).PRODUCT_UOM_CODE := qi_pa.Product_Uom_Code; --Mandatory (Unit of measure for which the price list line is defined)
          p_PRICING_ATTR_tbl(v_count).EXCLUDER_FLAG := 'N';
          --p_PRICING_ATTR_tbl(v_count).ATTRIBUTE_GROUPING_NO := 1;
          p_PRICING_ATTR_tbl(v_count).PRICE_LIST_LINE_INDEX := v_count;
          --p_PRICING_ATTR_tbl(v_count).operation := QP_GLOBALS.G_OPR_CREATE; --Mandatory
          --p_PRICING_ATTR_tbl(v_count).comparison_operator_code := QP_GLOBALS.G_OPR_CREATE; --Mandatory
          
          /***********************Calling API**********************/
          oe_msg_pub.INITIALIZE;
          -- Actual Call to the Pricing Engine
          QP_PRICE_LIST_PUB.Process_Price_List
          ( 1 --p_api_version_number
           , FND_API.G_FALSE --p_init_msg_list
           , FND_API.G_FALSE --p_return_values
           , FND_API.G_FALSE --p_commit
           , x_return_status
           , x_msg_count
           , x_msg_data
           , p_PRICE_LIST_rec
           , p_PRICE_LIST_LINE_tbl
           , p_PRICING_ATTR_tbl
           , x_PRICE_LIST_rec
           , x_PRICE_LIST_val_rec
           , x_PRICE_LIST_LINE_tbl
           , x_PRICE_LIST_LINE_val_tbl
           , x_QUALIFIERS_tbl
           , x_QUALIFIERS_val_tbl
           , x_PRICING_ATTR_tbl
           , x_PRICING_ATTR_val_tbl
          );
          dbms_output.put_line(x_return_status);
            IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
               RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
            END IF;
          /*EXCEPTION
                WHEN FND_API.G_EXC_ERROR 
                THEN x_return_status := FND_API.G_RET_STS_ERROR;
                Rollback;
                WHEN FND_API.G_EXC_UNEXPECTED_ERROR 
                THEN x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
                for k in 1 .. x_msg_count loop
                     x_msg_data := oe_msg_pub.get( p_msg_index => k,
                     p_encoded => 'F'
                    );
                    null;
                end loop;
                Rollback;
                WHEN OTHERS 
                THEN x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
                Rollback;     
          P_OUT_RETURN_STAT:=x_return_status;
          P_OUT_MESSG:=x_msg_data; */             
        END LOOP;               
      END LOOP;       
    END LOOP;          
 END LOOP; 
 EXCEPTION
      WHEN FND_API.G_EXC_ERROR 
      THEN x_return_status := FND_API.G_RET_STS_ERROR;
      Rollback;
      WHEN FND_API.G_EXC_UNEXPECTED_ERROR 
      THEN x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
      for k in 1 .. x_msg_count loop
      x_msg_data := oe_msg_pub.get( p_msg_index => k,
      p_encoded => 'F'
      );
      null;
      end loop;
      Rollback;
      WHEN OTHERS 
      THEN x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
      Rollback;     
      P_OUT_RETURN_STAT:=x_return_status;
      P_OUT_MESSG:=x_msg_data; 
 COMMIT;         
END Process_Upload_Pricelist;
/

-------------------------------------------------------

It is showing the error message while compiling
"PLS-00306: wrong number or types of arguments in call to 'PROCESS_PRICE_LIST'"

Please help me inthis regard...

Thanks
Moumita

[mod-edit] color removed, code tags added. Note: Using the word urgent has the inverse effect. Please pay for support if it's so urgent.

[Updated on: Tue, 26 February 2008 08:21] by Moderator

Report message to a moderator

Re: URGENT:Problem with API call in stored procedure [message #302653 is a reply to message #302651] Tue, 26 February 2008 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00306: wrong number or types of arguments in call to "string"
Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.
Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.


Regards
Michel

[Updated on: Tue, 26 February 2008 08:21]

Report message to a moderator

Re: URGENT:Problem with API call in stored procedure [message #302654 is a reply to message #302651] Tue, 26 February 2008 08:21 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I didn't / don't want go through the entire code.
Did you cross check the datatypes and number of parameters?
Does it the same problem as in the post?

By
Vamsi
Re: URGENT:Problem with API call in stored procedure [message #302718 is a reply to message #302651] Tue, 26 February 2008 16:30 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Try this:

create procedure bad_call (a number,b date,c varchar2) is
begin
   null;
end;
/
show errors

exec bad_call
exec bad_call(1)
exec bad_call(1,sysdate)
exec bad_call(1,sysdate,'a')


exec bad_call(sysdate,sysdate,sysdate)


If after executing this code you still do not understand the error you are getting, you should think about doing something else besides Oracle development. After trying this you should look at the procedure call you are making. Line up the specification with your parameters going in. Either you got parameters out of order so that you are doing something like passing a date into number parameter, or you forgot one or more required parameters altogether.

If you can't find your mis-match with a desk check (does anyone desk check anymore?) then rewrite the call using named notation. That will force you to account for each parameter in the specification of this procedure.


          QP_PRICE_LIST_PUB.Process_Price_List
          ( 1 --p_api_version_number
           , FND_API.G_FALSE --p_init_msg_list
           , FND_API.G_FALSE --p_return_values
           , FND_API.G_FALSE --p_commit
           , x_return_status
           , x_msg_count
           , x_msg_data
           , p_PRICE_LIST_rec
           , p_PRICE_LIST_LINE_tbl
           , p_PRICING_ATTR_tbl
           , x_PRICE_LIST_rec
           , x_PRICE_LIST_val_rec
           , x_PRICE_LIST_LINE_tbl
           , x_PRICE_LIST_LINE_val_tbl
           , x_QUALIFIERS_tbl
           , x_QUALIFIERS_val_tbl
           , x_PRICING_ATTR_tbl
           , x_PRICING_ATTR_val_tbl
          );

Good luck, Kevin
Previous Topic: PL/SQL combine two columns in one variable
Next Topic: Full Outer Joins (and subqueries)
Goto Forum:
  


Current Time: Sun Dec 04 00:18:57 CST 2016

Total time taken to generate the page: 0.10223 seconds