APPS.OE_DEALS_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

SELECT  WPA.activity_name INTO l_activity
		FROM WF_ITEM_ACTIVITY_STATUSES WIAS
			, WF_PROCESS_ACTIVITIES WPA
		WHERE WIAS.item_type 	= p_item_type
		  AND WIAS.item_key 	= p_item_key
		  AND WIAS.activity_status = 'NOTIFIED'
		  AND WPA.instance_id 	   = WIAS.process_activity;

Line: 32

        select TRANSACTION_PHASE_CODE  into l_TRANSACTION_PHASE_CODE  FROM
		OE_ORDER_HEADERS_ALL where header_id = p_header_id;

Line: 41

select ROOT_ACTIVITY INTO l_root_activity from wf_items where
   item_type=l_item_type and item_key=to_char(p_header_id);

Line: 59

p_deleted_options_tbl  OE_Order_PUB.request_tbl_type;

Line: 60

p_updated_options_tbl  OE_Order_PUB.request_tbl_type;

Line: 71

cursor config_lines is  select  line_id, Config_header_id, Config_rev_nbr, Configuration_id
FROM OE_ORDER_LINES_ALL where header_id = p_header_id  AND
line_id= top_model_line_id  and open_flag='Y' and nvl(cancelled_flag,'N') ='N';

Line: 93

         p_deleted_options_tbl => p_deleted_options_tbl,
      	 p_updated_options_tbl => p_updated_options_tbl,
         p_validate_flag       => 'Y',
       	 p_complete_flag       => 'N',
         x_valid_config        => l_valid_config,
       	 x_complete_config     => l_complete_config,
         x_return_status       => l_return_status);

Line: 262

	If Deal is Approved, complete with Result=Y, Update Order status to PRICING APPROVED
	If Deal is NOT Approved OR NOT created, complete with Result=N, Update Order status to PENDING PRICING APPROVAL
*/

PROCEDURE COMPLIANCE_CHECK(
	    itemtype  in varchar2,
	    itemkey   in varchar2,
	    actid     in number,
	    funcmode  in varchar2,
	    resultout in out NOCOPY /* file.sql.39 change */ varchar2)
	IS
	l_header_id		NUMBER;

Line: 292

	l_updateable		VARCHAR2(1) := 'N';

Line: 303

		select
			ORDER_NUMBER , QUOTE_NUMBER, VERSION_NUMBER, ORDER_TYPE_ID , ORG_ID
		INTO
		  	L_ORDER_NUMBER , L_QUOTE_NUMBER, L_VERSION_NUMBER, L_ORDER_TYPE_ID , L_ORG_ID
		FROM
			oe_order_headers_all where header_id= l_header_id;

Line: 375

			UPDATE oe_order_headers_all      SET
		  	      flow_status_code = 'PRICING_APPROVED'
		  	WHERE header_id = l_header_id;

Line: 382

                        UPDATE OE_ORDER_LINES_ALL
                           SET CALCULATE_PRICE_FLAG = 'P'
                         WHERE header_id = l_header_id
                           AND open_flag='Y'
                           AND cancelled_flag='N';

Line: 424

DESC-	This procedure is called when user selects Action -CHECK_COMPLIANCE (p_accept = Y)
	OR Action-INVOKE_DEAL_WB (p_accept=N)
	Check if the WF is eligible for Pricing Approval, If NOT, throw error msg.
	If p-accept=y
		just complete the WF activity-Pricing eligible with result =Y
		The next WF activty COMPLIANCE_CHECK will create the DEAL.
	If p-accept=N
		Create the Deal
		Update the Order Status to PENDING_PRICING_APPROVAL ( NO longer DEAL_SUBMITTED)
		complete the WF activity-Pricing eligible with Result=N
*/

PROCEDURE Complete_Compliance_Eligible
			( p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE
			, p_header_id			IN 	NUMBER
			, p_accept			IN VARCHAR2
			, p_item_type 			IN VARCHAR2
			, x_return_status OUT NOCOPY VARCHAR2
			, x_msg_count OUT NOCOPY NUMBER
			, x_msg_data OUT NOCOPY VARCHAR2
			)  IS

	l_api_name              CONSTANT VARCHAR2(30) := 'COMPLETE_Compliance_eligible';

Line: 478

		SELECT 'Y'
		FROM WF_ITEM_ACTIVITY_STATUSES WIAS
			, WF_PROCESS_ACTIVITIES WPA
		WHERE WIAS.item_type 	= p_item_type
		  AND WIAS.item_key 	= l_itemkey
		  AND WIAS.activity_status = 'NOTIFIED'
		  AND WPA.activity_name    = 'PRICING_APPROVAL_ELIGIBLE'
		  AND WPA.instance_id 	   = WIAS.process_activity;

Line: 488

	       SELECT 'Y'
	       FROM WF_ITEM_ACTIVITY_STATUSES WIAS
			, WF_PROCESS_ACTIVITIES WPA
	       WHERE WIAS.item_type 	= p_item_type
	       AND WIAS.item_key 	= l_itemkey
	       AND WIAS.activity_status = 'ERROR'
	       AND WPA.activity_name 	= 'PRICING_APPROVAL_ELIGIBLE'
	       AND WPA.instance_id 	= WIAS.process_activity;

Line: 509

		SELECT order_source_id, orig_sys_document_ref, change_sequence, source_document_type_id, source_document_id,
		     ORDER_NUMBER , quote_number, VERSION_NUMBER, ORDER_TYPE_ID , ORG_ID
		INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence, l_source_document_type_id, l_source_document_id,
		     L_ORDER_NUMBER , l_quote_number, L_VERSION_NUMBER, L_ORDER_TYPE_ID , L_ORG_ID
		FROM OE_ORDER_HEADERS_ALL
	 	WHERE HEADER_ID = p_header_id;

Line: 566

		OE_Standard_WF.OEOH_SELECTOR
		   (p_itemtype => p_item_type
		   ,p_itemkey => l_itemkey
		   ,p_actid => 12345
		   ,p_funcmode => 'SET_CTX'
		   ,p_result => l_result
		   );

Line: 671

Procedure Update_OM_with_deal(
        source_id in number,
        source_ref_id in number,
        event in varchar2,
        x_return_status out NOCOPY varchar2,
        x_message_name out NOCOPY varchar2)

IS
l_header_id NUMBER;

Line: 704

	select TRANSACTION_PHASE_CODE , flow_status_code into l_TRANSACTION_PHASE_CODE , l_status FROM
		OE_ORDER_HEADERS_ALL where header_id = l_header_id;

Line: 729

	OE_STANDARD_WF.OEOH_SELECTOR(
		p_itemtype => l_item_type,
		p_itemkey => to_char(l_header_id),
		p_actid   => 12345,
		p_funcmode   => 'SET_CTX',
		p_result => l_result);

Line: 758

			UPDATE oe_order_headers_all      SET
				flow_status_code = 'PENDING_PRICING_APPROVAL'
				WHERE header_id = l_header_id;

Line: 774

		UPDATE oe_order_headers_all      SET
			flow_status_code = 'DEAL_APPROVED'
			WHERE header_id = l_header_id;

Line: 779

	--when deal is Accepted, change status to Pricing Approved, update order with deal,
	-- Progress WF, there can be 2 cases--direct accept 		--Pricing eligible->Pricing Approved
					    --Submit n Approve n accept --Submit Deal WB->Pricing Approved
	IF (event = 'ACCEPTED') THEN
		IF OE_DEALS_UTIL.Validate_Config(p_header_id =>l_header_id) then

			OE_DEALS_UTIL.Update_Order_with_Deal
  			( p_header_id			=> l_header_id
  			, p_item_type			=> l_item_type
  			, x_return_status		=> x_return_status
  			, x_msg_count			=> l_msg_count
  			, x_msg_data			=> l_msg_data);

Line: 793

				x_message_name := 'OE_DEAL_UPDATE_FAILED';

Line: 812

			UPDATE oe_order_headers_all      SET
				flow_status_code = l_new_status
				WHERE header_id = l_header_id;

Line: 851

DESC-	Calls the Process Order API to Update the Order
	Update the Flow status Code of the Header.
	Query the view QPR_INT_DEAL_V to get the new values for all lines in the Header.
	Build the Line adjustment Info using the Modifier value from the Profile.
	Check if the Modifier is already applied to the Line.
        If modifier is applied then Operation is Update Else operation is Create
	Call Process Order API
*/

PROCEDURE Call_Process_Order (
    p_header_id IN NUMBER
    ,x_return_status OUT  NOCOPY varchar2
) IS
	x_msg_count			NUMBER;

Line: 916

   cursor lines_cursor is select line_id, unit_selling_price from oe_order_lines_all where header_id=p_header_id
   and open_flag='Y' and cancelled_flag='N';

Line: 951

    select list_header_id INTO l_list_header_id from qp_list_lines where list_line_id=l_list_line_id;

Line: 955

    l_header_rec.operation  := OE_GLOBALS.G_OPR_UPDATE;

Line: 963

        l_sqlstmt :=' SELECT  UOM_CODE ,CURRENCY_CODE ,ORDERED_QTY ,PRICE ,PAYMENT_TERM_ID ,SHIP_METHOD_CODE ' ||
     		  ' FROM QPR_INT_DEAL_V' || l_db_link ||
		  ' WHERE  SOURCE = 660 AND STATUS= ' || '''ACCEPT'''  ||
		  ' AND CHANGED = ' || '''Y''' ||
		  ' AND SOURCE_REF_HEADER_ID =  :p_header_id ' ||
		  ' AND SOURCE_REF_LINE_ID = :l_line_id ' ;

Line: 983

	        oe_debug_pub.add(  'Exec immediate NO data found-Dont update this Line'  , 1 ) ;

Line: 996

      l_line_tbl(i).operation  := OE_GLOBALS.G_OPR_UPDATE;

Line: 1004

	select price_adjustment_id, operand into l_price_adj_id, l_adjusted_amount from oe_price_adjustments where
                 header_id=p_header_id AND
		 line_id =l_lines.line_id  AND
		 list_line_id =l_list_line_id and list_header_id = l_list_header_id
	 	 and applied_flag='Y' and rownum <2;

Line: 1024

      l_line_adj_tbl(i).updated_flag  := 'Y';

Line: 1031

   	l_line_adj_tbl(i).operation  := OE_GLOBALS.G_OPR_UPDATE;

Line: 1043

      l_line_tbl(i).operation  := OE_GLOBALS.G_OPR_UPDATE;

Line: 1175

NAME-Update_Order_with_Deal
Desc -Update the Order with the Approved Deal.
      Get the deal Info.
      If deal is NOT create or NOT approved,
	show message
      If deal is approved,
	call Process Order to Update the Order.
	Progress the WF
*/

PROCEDURE Update_Order_with_Deal
			( p_header_id			IN 	NUMBER
			, p_item_type IN VARCHAR2
			, x_return_status OUT NOCOPY VARCHAR2
			, x_msg_count OUT NOCOPY NUMBER
			, x_msg_data OUT NOCOPY VARCHAR2
			)  IS
	l_order_source_id           NUMBER;

Line: 1207

	    oe_debug_pub.add(  'ENTER OE_DEALS_UTIL.Update_Order_with_Deal' , 1 ) ;

Line: 1210

	SELECT order_source_id, orig_sys_document_ref, change_sequence, source_document_type_id, source_document_id
		INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence, l_source_document_type_id, l_source_document_id
		FROM OE_ORDER_HEADERS_ALL
	 	WHERE HEADER_ID = p_header_id;

Line: 1236

	  -- Approved Deal, Call  PO API update the Order
		Call_Process_Order (p_header_id => p_header_id
				    ,x_return_status  => x_return_status);

Line: 1249

		select   WPA.activity_name  INTO l_wf_activity
		  		FROM WF_ITEM_ACTIVITY_STATUSES WIAS
		  			, WF_PROCESS_ACTIVITIES WPA
		  		WHERE WIAS.item_type = p_item_type
		  		  AND WIAS.item_key = to_char(p_header_id)
		  		  AND WIAS.activity_status = 'NOTIFIED'
		  		  AND WPA.activity_name in ( 'SUBMITTED_DEAL_WB', 'PRICING_APPROVAL_ELIGIBLE')
  		  AND WPA.instance_id = WIAS.process_activity;

Line: 1295

			FND_MESSAGE.SET_NAME('ONT','OE_ORDER_DEAL_UPDATED');

Line: 1299

			FND_MESSAGE.SET_NAME('ONT','OE_DEAL_UPDATE_FAILED');

Line: 1327

					, 'Update_Order_with_Deal'
					);

Line: 1335

END Update_Order_with_Deal;