APPS.QP_INT_LOADER_PUB SQL Statements

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

Line: 101

 SELECT
    qual.interface_action_code,
    qual.excluder_flag,
    qual.comparison_operator_code,
    qual.qualifier_context,
    qual.qualifier_attribute,
    qual.qualifier_attr_value,
    qual.qualifier_grouping_no
 FROM
    qp_interface_qualifiers qual
 WHERE
    qual.process_id = p_process_id;

Line: 115

 SELECT
    line.interface_action_code,
    line.list_header_id,
    line.list_line_id,
    line.list_line_type_code,
    line.automatic_flag,
    line.override_flag,
    line.modifier_level_code,
    line.operand,
    line.arithmetic_operator,
    line.product_precedence,
    line.comments,
    line.price_break_type_code,
    line.list_line_no,
    line.price_break_header_index
 FROM
    qp_interface_list_lines line
 WHERE
    line.process_id = p_process_id
 ORDER BY
    line.list_line_no;

Line: 139

 SELECT
    line.interface_action_code,
    line.list_header_id,
    line.list_line_id,
    line.list_line_type_code,
    line.automatic_flag,
    line.override_flag,
    line.modifier_level_code,
    line.operand,
    line.arithmetic_operator,
    line.product_precedence,
    line.pricing_group_sequence,
    line.pricing_phase_id,
    line.comments,
    line.price_break_type_code,
    line.list_line_no,
    line.charge_type_code,
    line.charge_subtype_code,
    line.price_break_header_index
 FROM
    qp_interface_list_lines line
 WHERE
    line.process_id = p_process_id
 ORDER BY
    line.list_line_no;

Line: 167

 SELECT
    pa.interface_action_code,
    pa.list_line_id,
    pa.excluder_flag,
    pa.product_attribute_context,
    pa.product_attribute,
    pa.product_attr_value,
    pa.product_uom_code,
    pa.product_attribute_datatype,
    pa.pricing_attribute_datatype,
    pa.pricing_attribute_context,
    pa.pricing_attribute,
    pa.pricing_attr_value_from,
    pa.pricing_attr_value_to,
    pa.attribute_grouping_no,
    pa.comparison_operator_code,
--    pa.price_list_line_index,
    pa.list_line_no
 FROM
    qp_interface_pricing_attribs pa
 WHERE
    pa.process_id = p_process_id;

Line: 192

 SELECT
    pa.interface_action_code,
    pa.list_line_id,
    pa.excluder_flag,
    pa.product_attribute_context,
    pa.product_attribute,
    pa.product_attr_value,
    pa.product_uom_code,
    pa.product_attribute_datatype,
    pa.pricing_attribute_datatype,
    pa.pricing_attribute_context,
    pa.pricing_attribute,
    pa.pricing_attr_value_from,
    pa.pricing_attr_value_to,
    pa.attribute_grouping_no,
    pa.comparison_operator_code,
--    pa.price_list_line_index,
    pa.list_line_no
 FROM
    qp_interface_pricing_attribs pa
 WHERE
    pa.process_id = p_process_id;

Line: 230

 SELECT process_type, list_type_code, interface_action_code, name, source_system_code
 INTO   l_process_type, l_list_type_code, l_interface_action_code, l_name, l_request_type_code
 FROM   qp_interface_list_headers
 WHERE  process_id = p_process_id;

Line: 254

 	SELECT count(1) INTO  l_count FROM qp_list_headers_tl qp_lhdr_tl where qp_lhdr_tl.name = l_name;

Line: 258

 		SELECT qp_lhdr_tl.list_header_id INTO l_list_header_id FROM qp_list_headers_tl qp_lhdr_tl WHERE qp_lhdr_tl.name = l_name AND qp_lhdr_tl.language='US' ;

Line: 260

 		delete from qp_pricing_attributes qp_prc_att where qp_prc_att.list_header_id  = l_list_header_id;

Line: 261

 		delete from qp_list_lines qp_ll where qp_ll.list_header_id  = l_list_header_id;

Line: 262

 		delete from qp_qualifiers qp_qual where qp_qual.list_header_id  = l_list_header_id;

Line: 263

 		delete from qp_list_headers_b qp_lhdr_b where qp_lhdr_b.list_header_id = l_list_header_id;

Line: 264

 		delete from qp_list_headers_tl qp_lhdr_tl where qp_lhdr_tl.list_header_id  = l_list_header_id;

Line: 267

 		update fte_lanes set fte_lanes.pricelist_view_flag = NULL WHERE fte_lanes.pricelist_id = l_list_header_id;

Line: 268

 		update fte_lanes set fte_lanes.pricelist_name = NULL WHERE fte_lanes.pricelist_id = l_list_header_id;

Line: 269

 		update fte_lanes set fte_lanes.pricelist_id = NULL WHERE fte_lanes.pricelist_id = l_list_header_id;

Line: 275

			select qp_ll.list_line_id INTO l_list_line_id from qp_list_lines qp_ll where qp_ll.list_header_id = l_list_header_id;

Line: 276

			delete from qp_rltd_modifiers where qp_rltd_modifiers.from_rltd_modifier_id = l_list_line_id;

Line: 281

 		Insert_Job_Status  (p_process_id, -1, -1, -1, 1, -1, x_status, l_process_type, l_name, SYSDATE, SYSDATE);

Line: 292

  	SELECT
    		lhdr.list_header_id,
    		lhdr.name,
    		lhdr.description,
    		lhdr.interface_action_code,
    		lhdr.list_type_code,
    		lhdr.currency_code,
    		TO_DATE(lhdr.start_date_active,'YYYYMMDD HH24MISS'),
    		TO_DATE(lhdr.end_date_active,'YYYYMMDD HH24MISS'),
    		lhdr.automatic_flag
 	INTO
    		gpr_price_list_rec.list_header_id,
    		gpr_price_list_rec.name,
    		gpr_price_list_rec.description,
    		l_operation,
    		gpr_price_list_rec.list_type_code,
    		gpr_price_list_rec.currency_code,
    		gpr_price_list_rec.start_date_active,
    		gpr_price_list_rec.end_date_active,
    		gpr_price_list_rec.automatic_flag
 	FROM
    		qp_interface_list_headers lhdr
 	WHERE
    		lhdr.process_id = p_process_id
 	AND
    		rownum < 2;

Line: 322

 	SELECT
    		lhdr.list_header_id,
    		lhdr.name,
    		lhdr.description,
    		lhdr.interface_action_code,
    		lhdr.list_type_code,
    		lhdr.currency_code,
    		TO_DATE(lhdr.start_date_active,'YYYYMMDD HH24MISS'),
    		TO_DATE(lhdr.end_date_active,'YYYYMMDD HH24MISS'),
    		lhdr.automatic_flag
 	INTO
    		gpr_modifier_list_rec.list_header_id,
    		gpr_modifier_list_rec.name,
    		gpr_modifier_list_rec.description,
    		l_operation,
    		gpr_modifier_list_rec.list_type_code,
    		gpr_modifier_list_rec.currency_code,
    		gpr_modifier_list_rec.start_date_active,
    		gpr_modifier_list_rec.end_date_active,
    		gpr_modifier_list_rec.automatic_flag
 	FROM
    		qp_interface_list_headers lhdr
 	WHERE
    		lhdr.process_id = p_process_id
 	AND
    		rownum < 2;

Line: 351

 	SELECT
    		lhdr.list_header_id,
    		lhdr.name,
    		lhdr.description,
    		lhdr.interface_action_code,
    		lhdr.list_type_code,
    		lhdr.currency_code,
    		lhdr.start_date_active,
    		lhdr.end_date_active,
    		lhdr.automatic_flag
 	INTO
    		gpr_price_list_rec.list_header_id,
    		gpr_price_list_rec.name,
    		gpr_price_list_rec.description,
    		l_operation,
    		gpr_price_list_rec.list_type_code,
    		gpr_price_list_rec.currency_code,
    		gpr_price_list_rec.start_date_active,
    		gpr_price_list_rec.end_date_active,
    		gpr_price_list_rec.automatic_flag
 	FROM
    		qp_interface_list_headers lhdr
 	WHERE
    		lhdr.process_id = p_process_id
 	AND
    		rownum < 2;

Line: 381

	SELECT
    		lhdr.list_header_id,
    		lhdr.name,
    		lhdr.description,
    		lhdr.interface_action_code,
    		lhdr.list_type_code,
    		lhdr.currency_code,
    		lhdr.start_date_active,
    		lhdr.end_date_active,
   		lhdr.automatic_flag

 	INTO
    		gpr_modifier_list_rec.list_header_id,
    		gpr_modifier_list_rec.name,
    		gpr_modifier_list_rec.description,
    		l_operation,
    		gpr_modifier_list_rec.list_type_code,
    		gpr_modifier_list_rec.currency_code,
    		gpr_modifier_list_rec.start_date_active,
    		gpr_modifier_list_rec.end_date_active,
    		gpr_modifier_list_rec.automatic_flag
 	FROM
    		qp_interface_list_headers lhdr
 	WHERE
    		lhdr.process_id = p_process_id
 	AND
    		rownum < 2;

Line: 752

 	-- since only pricing_attr_value_from was inserted at the xml gateway level for price breaks,
 	-- therefore we need to calculate and insert the pricing_attr_value_to here when context is VOLUME
 	length := i-1;

Line: 852

 	-- since only pricing_attr_value_from was inserted at the xml gateway level,
 	-- therefore we need to calculate and insert the pricing_attr_value_to here
 	length := i-1;

Line: 956

	 Insert_Job_Status  (p_process_id, 1, 0, 1, 0, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 983

	 rollback;	-- do not insert to qp tables

Line: 989

	 Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 992

	 Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY:'||l_name, l_job_start_date, l_job_completion_date);

Line: 1008

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1009

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1019

	 	-- (JOB_ID, LINE_NUMBER, FIELD_NAME, CREATION_DATE, ERROR_MESSAGE, LAST_UPDATE_DATE)
	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1023

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1034

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1035

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1040

		l_err_buffer := 'SYNCIND cannot be NULL. Please specify SYNCIND data as C for create or D for delete. ';

Line: 1045

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1046

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1051

		l_err_buffer := 'SYNCIND must be C or D. Please specify SYNCIND data as C for create or D for delete. ';

Line: 1056

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1057

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1065

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1066

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1075

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1076

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1081

		l_err_buffer := 'Price list to be deleted does not exist. Please correct data for PRICELSTID. ';

Line: 1085

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1086

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1093

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1094

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1107

	 	Insert_Err_Msg (p_process_id, 1, l_name, SYSDATE, l_err_buffer, SYSDATE);

Line: 1108

	 	Insert_Job_Status  (p_process_id, 1, 1, 1, 1, l_party_id, l_job_status, 'TARI', 'GATEWAY: '||l_name, l_job_start_date, l_job_completion_date);

Line: 1127

	PROCEDURE Insert_Err_Msg
	(
		p_job_id		IN	NUMBER,
		p_line_num		IN	NUMBER,
		p_field_name		IN 	VARCHAR2,
		p_creation_date		IN 	DATE,
		p_err_msg		IN 	VARCHAR2,
		p_last_update_date	IN 	DATE
	)
	IS
	BEGIN
		INSERT INTO FTE_JOB_ERRORS (JOB_ID, LINE_NUMBER, FIELD_NAME, CREATION_DATE, ERROR_MESSAGE, LAST_UPDATE_DATE)
	 	VALUES (p_job_id, p_line_num, p_field_name, p_creation_date, p_err_msg, p_last_update_date);

Line: 1142

	END Insert_Err_Msg;

Line: 1144

	PROCEDURE Insert_Job_Status
	(
		p_job_id		IN	NUMBER,
		p_lines_processed	IN	NUMBER,
		p_lines_failed		IN 	NUMBER,
		p_lines_submitted	IN 	NUMBER,
		p_total_error_number	IN 	NUMBER,
		p_supplier_id		IN 	NUMBER,
		p_job_status		IN	VARCHAR2,
		p_job_type		IN	VARCHAR2,
		p_file_name		IN	VARCHAR2,
		p_start_date		IN 	DATE,
		p_completion_date	IN	DATE
	)
	IS
	BEGIN
		-- job status 0 = completed with success, 1 = completed with error
		INSERT INTO FTE_BATCH_JOBS (JOB_ID, LINES_PROCESSED, LINES_FAILED, LINES_SUBMITTED, TOTAL_ERROR_NUMBER, SUPPLIER_ID, JOB_STATUS, JOB_TYPE, FILENAME, START_DATE, COMPLETION_DATE)
	 	VALUES (p_job_id, p_lines_processed, p_lines_failed, p_lines_submitted, p_total_error_number, p_supplier_id, p_job_status, p_job_type, p_file_name, p_start_date, p_completion_date);

Line: 1164

	END Insert_Job_Status;

Line: 1268

 		SELECT q.qualifier_attr_value
 		INTO l_party_name
 		FROM qp_interface_qualifiers q
 		WHERE q.process_id = p_process_id AND q.qualifier_context = 'PARTY';

Line: 1274

 		SELECT hz_parties.party_id
 		INTO x_party_id
 		FROM hz_parties
 		WHERE hz_parties.party_name = l_party_name;

Line: 1300

		SELECT count(1) into l_count from qp_list_headers_tl where name = p_prclst_name;