APPS.XDP_OE_ORDER SQL Statements

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

Line: 6

		Procedure : Insert_OE_Order
		Purpose : Insert into XDP OE Order Header
	----------------------------------------*/
	-- API to Insert into XDP OE Order Header Table
	PROCEDURE Insert_OE_Order( P_OE_Order_Header IN XDP_TYPES.OE_ORDER_HEADER,
				P_OE_Order_Parameter_List IN XDP_TYPES.OE_ORDER_PARAMETER_LIST,
				Return_Code OUT NOCOPY NUMBER,
				Error_Description OUT NOCOPY VARCHAR2 ) IS


	lv_oe_order_header	XDP_TYPES.OE_ORDER_HEADER ;

Line: 51

		-- Insert into the XDP OE Order Header Table
		INSERT INTO XDP_OE_ORDER_HEADERS (
			ORDER_NUMBER,
			ORDER_VERSION,
			PROVISIONING_DATE,
			COMPLETION_DATE,
			ORDER_TYPE,
			ORDER_ACTION,
			ORDER_SOURCE,
			PRIORITY,
			STATUS,
			SDP_ORDER_ID,
			DUE_DATE,
			CUSTOMER_REQUIRED_DATE,
			CUSTOMER_NAME,
			CUSTOMER_ID,
			ORG_ID,
			SERVICE_PROVIDER_ID,
			TELEPHONE_NUMBER,
			RELATED_ORDER_ID,
			ORDER_COMMENT,
			SP_ORDER_NUMBER,
			SP_USERID,
			JEOPARDY_ENABLED_FLAG,
			ORDER_REF_NAME,
			ORDER_REF_VALUE
		)
		VALUES (
			UPPER(lv_oe_order_header.ORDER_NUMBER),
                        -- Order version made mandatory. 03/27/2001. skilaru
			NVL(lv_oe_order_header.ORDER_VERSION,'1'),
			lv_oe_order_header.PROVISIONING_DATE,
			lv_oe_order_header.COMPLETION_DATE,
			lv_oe_order_header.ORDER_TYPE,
			lv_oe_order_header.ORDER_ACTION,
			lv_oe_order_header.ORDER_SOURCE,
			lv_oe_order_header.PRIORITY,
			'SUBMITTED',
			lv_oe_order_header.SDP_ORDER_ID,
			lv_oe_order_header.DUE_DATE,
			lv_oe_order_header.CUSTOMER_REQUIRED_DATE,
			UPPER(lv_oe_order_header.CUSTOMER_NAME),
			lv_oe_order_header.CUSTOMER_ID,
			lv_oe_order_header.ORG_ID,
			lv_oe_order_header.SERVICE_PROVIDER_ID,
			lv_oe_order_header.TELEPHONE_NUMBER,
			lv_oe_order_header.RELATED_ORDER_ID,
			lv_oe_order_header.ORDER_COMMENT,
			lv_oe_order_header.SP_ORDER_NUMBER,
			lv_oe_order_header.SP_USERID,
			lv_oe_order_header.JEOPARDY_ENABLED_FLAG,
			lv_oe_order_header.ORDER_REF_NAME,
			lv_oe_order_header.ORDER_REF_VALUE
		) ;

Line: 111

			-- Insert any XDP OE Order Parameters.
			lv_param_count := lv_oe_order_parameter_list.FIRST ;

Line: 115

				INSERT INTO XDP_OE_ORDER_PARAMETERS (
					ORDER_NUMBER,
					ORDER_VERSION,
					PARAMETER_NAME,
					PARAMETER_VALUE
				)
				VALUES (
					UPPER(lv_oe_order_header.ORDER_NUMBER),
                                        -- Order version made mandatory. 03/27/2001. skilaru
					NVL(lv_oe_order_header.ORDER_VERSION,'1'),
					lv_oe_order_parameter_list( lv_param_count ).PARAMETER_NAME,
					lv_oe_order_parameter_list( lv_param_count ).PARAMETER_VALUE
				) ;

Line: 138

	END	Insert_OE_Order ;

Line: 153

			SELECT 'Y' INTO lv_exists_flag
			FROM DUAL
			WHERE EXISTS
				( SELECT 'x' FROM XDP_OE_ORDER_HEADERS
				WHERE ORDER_NUMBER =
						UPPER(P_Order_Number));

Line: 166

			SELECT 'Y' INTO lv_exists_flag
			FROM DUAL
			WHERE EXISTS
				( SELECT 'x' FROM XDP_OE_ORDER_HEADERS
				WHERE ORDER_NUMBER =
						UPPER( P_Order_Number) AND
				ORDER_VERSION = UPPER( p_Version));

Line: 188

		Procedure : Insert_OE_Order_Line
		Purpose : Insert into XDP OE Order Line and Line Details
	----------------------------------------*/
	-- API to Insert into XDP OE Order Line and Line Details Table
	PROCEDURE Insert_OE_Order_Line( P_OE_Order_Line IN XDP_TYPES.OE_ORDER_LINE,
				P_OE_Order_Line_Detail_List IN XDP_TYPES.OE_ORDER_LINE_DETAIL_LIST,
				Return_Code OUT NOCOPY NUMBER,
				Error_Description OUT NOCOPY VARCHAR2 ) IS

	lv_oe_order_line XDP_TYPES.OE_ORDER_LINE ;

Line: 243

		-- Insert the XDP Order Line
		INSERT INTO XDP_OE_ORDER_LINES (
			ORDER_NUMBER,
			ORDER_VERSION,
			LINE_NUMBER,
			LINE_ITEM_NAME,
			LINE_ITEM_VERSION,
			LINE_ITEM_ACTION,
			PROVISIONING_REQUIRED_FLAG,
			IS_WORKITEM_FLAG,
			LINE_ITEM_TYPE,
			STATUS,
			PROVISIONING_SEQUENCE,
			PRIORITY,
			PROVISIONING_DATE,
			DUE_DATE,
			CUSTOMER_REQUIRED_DATE,
			COMPLETION_DATE,
			BUNDLE_ID,
			BUNDLE_SEQUENCE,
			STARTING_NUMBER,
			ENDING_NUMBER,
			JEOPARDY_ENABLED_FLAG
		)
		VALUES (
			UPPER(lv_oe_order_line.ORDER_NUMBER),
                        -- Order version made mandatory. 03/27/2001. skilaru
			NVL(lv_oe_order_line.ORDER_VERSION,'1'),
			lv_oe_order_line.LINE_NUMBER,
			lv_oe_order_line.LINE_ITEM_NAME,
			lv_oe_order_line.LINE_ITEM_VERSION,
			lv_oe_order_line.LINE_ITEM_ACTION,
			lv_oe_order_line.PROVISIONING_REQUIRED_FLAG,
			lv_oe_order_line.IS_WORKITEM_FLAG,
			lv_oe_order_line.LINE_ITEM_TYPE,
			lv_oe_order_line.STATUS,
			lv_oe_order_line.PROVISIONING_SEQUENCE,
			lv_oe_order_line.PRIORITY,
			lv_oe_order_line.PROVISIONING_DATE,
			lv_oe_order_line.DUE_DATE,
			lv_oe_order_line.CUSTOMER_REQUIRED_DATE,
			lv_oe_order_line.COMPLETION_DATE,
			lv_oe_order_line.BUNDLE_ID,
			lv_oe_order_line.BUNDLE_SEQUENCE,
			lv_oe_order_line.STARTING_NUMBER,
			lv_oe_order_line.ENDING_NUMBER,
			lv_oe_order_line.JEOPARDY_ENABLED_FLAG
		) ;

Line: 297

			-- Insert any Line Details
			lv_detail_count := lv_oe_order_line_detail_list.FIRST ;

Line: 301

				INSERT INTO XDP_OE_ORDER_LINE_DETS (
					ORDER_NUMBER,
					ORDER_VERSION,
					LINE_NUMBER,
					PARAMETER_NAME,
					PARAMETER_VALUE,
					PARAMETER_REF_VALUE
				)
				VALUES (
					UPPER( lv_oe_order_line.ORDER_NUMBER),
                                        -- Order version made mandatory. 03/27/2001. skilaru
					NVL(lv_oe_order_line.ORDER_VERSION,'1'),
					lv_oe_order_line.LINE_NUMBER,
					lv_oe_order_line_detail_list( lv_detail_count ).PARAMETER_NAME,
					lv_oe_order_line_detail_list( lv_detail_count ).PARAMETER_VALUE,
				lv_oe_order_line_detail_list( lv_detail_count ).PARAMETER_REF_VALUE

				) ;

Line: 329

	END	Insert_OE_Order_Line ;

Line: 354

	SELECT PARAMETER_NAME, PARAMETER_VALUE
	FROM XDP_OE_ORDER_PARAMETERS
	WHERE ORDER_NUMBER = p_oe_order_number
	AND ORDER_VERSION = p_oe_order_version ;

Line: 370

	SELECT *
	FROM XDP_OE_ORDER_LINES
	WHERE ORDER_NUMBER = p_oe_order_number
	AND ORDER_VERSION = p_oe_order_version;

Line: 386

	select *
	from xdp_oe_order_line_dets
	where order_number = p_oe_order_number
	and order_version = p_oe_order_version;

Line: 409

		select order_number ,
			order_version ,
			provisioning_date,
			priority ,
			due_date ,
			customer_required_date ,
			order_type ,
			order_action ,
			order_source ,
			related_order_id,
			org_id ,
			customer_name ,
			customer_id  ,
			service_provider_id ,
			telephone_number,
			jeopardy_enabled_flag,
			order_ref_name,
			order_ref_value,
			sp_order_number,
			sp_userid
		into lv_header.order_number ,
			lv_header.order_version ,
			lv_header.provisioning_date,
			lv_header.priority ,
			lv_header.due_date ,
			lv_header.customer_required_date ,
			lv_header.order_type ,
			lv_header.order_action ,
			lv_header.order_source ,
			lv_header.related_order_id,
			lv_header.org_id ,
			lv_header.customer_name ,
			lv_header.customer_id  ,
			lv_header.service_provider_id ,
			lv_header.telephone_number,
			lv_header.jeopardy_enabled_flag,
			lv_header.order_ref_name,
			lv_header.order_ref_value,
			lv_header.sp_order_number,
			lv_header.sp_userid
		from xdp_oe_order_headers
		where order_number = p_oe_order_number
		and order_version IS NULL;

Line: 515

		select order_number ,
			order_version ,
			provisioning_date,
			priority ,
			due_date ,
			customer_required_date ,
			order_type ,
			order_action ,
			order_source ,
			related_order_id,
			org_id ,
			customer_name ,
			customer_id  ,
			service_provider_id ,
			telephone_number,
			jeopardy_enabled_flag,
			order_ref_name,
			order_ref_value,
			sp_order_number,
			sp_userid
		into lv_header.order_number ,
			lv_header.order_version ,
			lv_header.provisioning_date,
			lv_header.priority ,
			lv_header.due_date ,
			lv_header.customer_required_date ,
			lv_header.order_type ,
			lv_header.order_action ,
			lv_header.order_source ,
			lv_header.related_order_id,
			lv_header.org_id ,
			lv_header.customer_name ,
			lv_header.customer_id  ,
			lv_header.service_provider_id ,
			lv_header.telephone_number,
			lv_header.jeopardy_enabled_flag,
			lv_header.order_ref_name,
			lv_header.order_ref_value,
			lv_header.sp_order_number,
			lv_header.sp_userid
		from xdp_oe_order_headers
		where order_number = p_oe_order_number
		and order_version = p_oe_order_version;

Line: 628

	**      update table xdp_oe_order_headers with the value obtained from
	**      process_order() for the SDP_ORDER_ID column.
	*/

	l_sdp_order_id := sdp_order_id;

Line: 640

		update XDP_OE_ORDER_HEADERS
		set SDP_ORDER_ID = l_sdp_order_id, STATUS='SUBMITTED'
		where ORDER_NUMBER =  p_oe_order_number
		and   ORDER_VERSION is NULL;

Line: 647

		update XDP_OE_ORDER_HEADERS
		set SDP_ORDER_ID = l_sdp_order_id, STATUS='SUBMITTED'
		where ORDER_NUMBER =  p_oe_order_number
		and   ORDER_VERSION = p_oe_order_version;