APPS.ZX_AR_FORMULA_MIG_PKG SQL Statements

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

Line: 57

	SELECT	vat.taxable_basis	,
		vat.tax_code		,
		vat.enabled_flag	,
		vat.start_date		,
		vat.end_date		,
		rate.tax		,
		rate.tax_regime_code	,
		rate.content_owner_id
	FROM	ar_vat_tax_all vat, zx_rates_b rate
	WHERE 	vat.taxable_basis IN ('AFTER_EPD', 'QUANTITY')
	AND 	vat.tax_class = 'O'
	AND	vat.tax_type <> 'TAX_GROUP'
	AND 	vat.vat_tax_id = NVL(rate.source_id, rate.tax_rate_id);

Line: 78

	SELECT 	tax_rel_upg.tax_group_id		,
		tax_rel_upg.tax_group_code	,
		tax_rel_upg.parent_tax_code	,
		tax_rel_upg.parent_precedence	,
		tax_rel_upg.parent_regime_code	,
		tax_rel_upg.child_tax_code	,
		tax_rel_upg.child_precedence	,
		tax_rel_upg.child_regime_code	,
		tax_rel_upg.child_taxable_basis	,
		tax_rel_upg.branch_flag		,
		tax_rel_upg.content_owner_id	,
		grp.enabled_flag		, -- for create_formula() and create_rules()
		grp.start_date			, -- for create_rules
		grp.end_date			, -- for create_rules
		grp.tax_condition_id		,
		grp.tax_exception_id		,
		grp.tax_group_id		,
		zx_rate.tax_regime_code		,
		zx_rate.tax
	FROM 	zx_tax_relations_t tax_rel_upg, ar_tax_group_codes_all grp, zx_rates_b zx_rate
	WHERE 	tax_rel_upg.tax_group_id NOT IN (SELECT	tax_group_id
				 		FROM  	zx_tax_relations_t
				 		WHERE 	TRUNC(child_precedence) <> child_precedence )
	AND	grp.tax_group_id = tax_rel_upg.tax_group_id
	AND 	grp.tax_code_id = NVL(zx_rate.source_id, zx_rate.tax_rate_id)
	AND 	grp.tax_code_id = tax_rel_upg.child_tax_code_id		--* new condition added
	ORDER BY tax_rel_upg.tax_group_id, child_precedence DESC;

Line: 110

	SELECT	tax_rel_upg.tax_group_id		,
		tax_rel_upg.tax_group_code	,
		tax_rel_upg.Parent_tax_code	,
		tax_rel_upg.Parent_precedence	,
		tax_rel_upg.Parent_regime_code	,
		tax_rel_upg.Child_tax_code	,
		tax_rel_upg.Child_precedence	,
		tax_rel_upg.Child_regime_code	,
		tax_rel_upg.Child_Taxable_basis	,
		tax_rel_upg.branch_flag		,
		tax_rel_upg.content_owner_id	,
		grp.enabled_flag		,
		grp.start_date			,
		grp.end_date			,
		grp.tax_condition_id		,
		grp.tax_exception_id		,
		grp.tax_group_id		,
		zx_rate.tax_regime_code		,
		zx_rate.tax
	FROM	zx_tax_relations_t tax_rel_upg, ar_tax_group_codes_all grp, zx_rates_b zx_rate
	WHERE 	tax_rel_upg.tax_group_id IN (SELECT tax_group_id
					 FROM  	zx_tax_relations_t
					 WHERE 	child_precedence > TRUNC(child_precedence) )
	AND	grp.tax_group_id = tax_rel_upg.tax_group_id
	AND 	grp.tax_code_id = NVL(zx_rate.source_id, zx_rate.tax_rate_id)
	AND 	grp.tax_code_id = tax_rel_upg.child_tax_code_id
	ORDER BY tax_rel_upg.tax_group_id, child_precedence DESC;

Line: 141

	SELECT	group_id		,
		tax_group_code		,
		Parent_tax_code		,
		Parent_precedence	,
		Parent_regime_code	,
		Child_tax_id		,
		Child_tax_code		,
		Child_precedence	,
		Child_regime_code	,
		Child_Taxable_basis	,
		branch			,
		content_owner_id
	FROM	zx_tax_relations_t
--	START WITH Parent_Regime_code IS NULL				--* changed crsr
	CONNECT BY PRIOR child_tax_code = parent_Tax_code
	ORDER BY group_id, child_tax_code, child_precedence DESC;

Line: 203

	arp_util_tax.debug('CASE 1: before select');

Line: 208

		SELECT 	condition_group_id, condition_group_code
		INTO	l_condition_grp_id, l_condition_grp_cd
		FROM 	zx_condition_groups_b
		WHERE 	condition_group_code = l_tax_code;

Line: 331

		SELECT DECODE( SIGN( LENGTH(l_tax_group_code||'_'||l_child_tax_code||'_TB') - 30),
				1,
				SUBSTRB(l_tax_group_code||'_'||l_child_tax_code,1,24) ||'_TB'||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_FORMULA_B_S'),
				l_tax_group_code||'_'||l_child_tax_code||'_TB')
		INTO	l_formula_code
		FROM 	DUAL;

Line: 387

			arp_util_tax.debug('case 3:bef select');

Line: 389

			SELECT  vat.tax_constraint_id
			INTO    l_tax_constraint_id
			FROM    ar_vat_tax_all vat
			WHERE   vat.vat_tax_id = l_tax_group_id;

Line: 394

			SELECT 	condition_group_id, condition_group_code
			INTO	l_condition_grp_id, l_condition_grp_cd
			FROM 	zx_condition_groups_b
			WHERE 	condition_group_code = l_tax_group_code|| decode(l_tax_constraint_id,NULL, '', '~'||l_tax_constraint_id);

Line: 525

			arp_util_tax.debug('case 4:before select of cond grp');

Line: 527

			SELECT  vat.tax_constraint_id
			INTO    l_tax_constraint_id
			FROM    ar_vat_tax_all vat
			WHERE   vat.vat_tax_id = l_tax_group_id;

Line: 532

			SELECT 	condition_group_id, condition_group_code
			INTO	l_condition_grp_id, l_condition_grp_cd
			FROM 	zx_condition_groups_b
			WHERE 	condition_group_code = l_tax_group_code|| decode(l_tax_constraint_id,NULL, '', '~'||l_tax_constraint_id);

Line: 551

			SELECT DECODE(SIGN(LENGTH(l_tax_group_code||'_'||l_child_tax_code||'_TB') - 30),
					1,
					SUBSTRB(l_tax_group_code||'_'||l_child_tax_code,1,24) ||'_TB'||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_FORMULA_B_S' ),
					l_tax_group_code||'_'||l_child_tax_code||'_TB')
			INTO	l_formula_code
			FROM 	DUAL;

Line: 612

			-- INSERT INTO zx_formula_b with taxable_basis_type = 'LINE_AMOUNT';

Line: 615

			SELECT DECODE(SIGN(LENGTH(l_tax_group_code||'_'||l_child_tax_code||'_TB') - 30),
					1,
					SUBSTRB(l_tax_group_code||'_'||l_child_tax_code,1,24) ||'_TB'||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_FORMULA_B_S' ),
					l_tax_group_code||'_'||l_child_tax_code||'_TB')
			INTO	l_formula_code
			FROM 	DUAL;

Line: 743

	-- Inserting values in table zx_formula_b

	arp_util_tax.debug('in create_formula() for : '||p_tax_code);

Line: 748

		INSERT INTO zx_formula_b_tmp (
				formula_type_code		,
				formula_code			,
				tax_regime_code			,
				tax				,
				effective_from			,
				effective_to			,
				enabled_flag			,
				taxable_basis_type_code		,
				record_type_code		,
				base_rate_modifier		,
				cash_discount_appl_flag		,
				volume_discount_appl_flag	,
				trading_discount_appl_flag	,
				transfer_charge_appl_flag	,
				transport_charge_appl_flag	,
				insurance_charge_appl_flag	,
				other_charge_appl_flag		,
				formula_id			,
				content_owner_id		,
				created_by			,
				creation_date			,
				last_updated_by			,
				last_update_date		,
				last_update_login		,
				request_id			,
				program_application_id		,
				program_id			,
				program_login_id		,
				object_version_number)
			SELECT
				'TAXABLE_BASIS'			,
				p_formula_code			, --tax_group_code||'_'||tax_code||'_TB'
				p_tax_regime_code		,
				p_tax_code				,
				p_start_date			,
				p_end_date			,
				p_enabled_flag			,
				DECODE(p_taxable_basis,'PRIOR_TAX','PRIOR_TAX','LINE_AMOUNT'),  --Bug Fix 5691957
				'MIGRATED'			,
				0				,-- bug6718736
				DECODE(p_taxable_basis, 'AFTER_EPD','Y','N'),
				p_discount_flag			,
				p_discount_flag			,
				p_charge_flag			,
				p_charge_flag			,
				p_charge_flag			,
				p_charge_flag			,
				zx_formula_b_s.NEXTVAL	l_formula_id,
				p_content_owner_id		,
				fnd_global.user_id		,
				SYSDATE				,
				fnd_global.user_id		,
				SYSDATE				,
				fnd_global.conc_login_id	,
				fnd_global.conc_request_id     	,--Request Id
				fnd_global.prog_appl_id        	,--Program Application ID
				fnd_global.conc_program_id    	,--Program Id
				fnd_global.conc_login_id        ,--Program Login ID
				1
			FROM	DUAL
			WHERE
			--Re-runnability
			NOT EXISTS (	SELECT 	1
					FROM 	zx_formula_b
					WHERE	SUBSTRB(formula_code,1,24) = SUBSTRB(p_formula_code,1,24)
					AND 	(effective_from BETWEEN
							p_start_date and nvl(p_end_date,SYSDATE)
							OR
						 NVL(effective_to,sysdate) BETWEEN
						      p_start_date and nvl(p_end_date,sysdate)
						)
					AND	enabled_flag   = 'Y'
						 );

Line: 829

	-- Inserting values in table zx_formula_tl
 	BEGIN

		 INSERT INTO zx_formula_tl (
			formula_id		,
			formula_name		,
			created_by		,
			creation_date		,
			last_updated_by		,
			last_update_date	,
			last_update_login	,
			language		,
			source_lang
			)
		  SELECT
			formula_id		,
			    CASE WHEN formula_code = UPPER(formula_code)
			     THEN    Initcap(formula_code)
			     ELSE
				     formula_code
			     END,
			fnd_global.user_id	,
			SYSDATE			,
			fnd_global.user_id	,
			SYSDATE			,
			fnd_global.conc_login_id,
			l.language_code		,
			userenv('LANG')
		  FROM 	fnd_languages l, zx_formula_b formula
		  WHERE	l.installed_flag IN ('I', 'B')
		  AND  	formula.record_type_code = 'MIGRATED'
		  AND 	formula.formula_code = p_formula_code
		  AND  	NOT EXISTS(SELECT	NULL
				   FROM 	zx_formula_tl t
				   WHERE 	t.formula_id = formula.formula_id
				   AND 		t.language = l.language_code);

Line: 873

	-- Inserting values in table zx_formula_details

	arp_util_tax.debug('l_formula_id:'||l_formula_id);

Line: 880

			  INSERT INTO zx_formula_details (
				formula_detail_id		,
				formula_id			,
				compounding_tax_regime_code	,
				compounding_tax			,
				compounding_type_code		,
				record_type_code 		,
				creation_date			,
				last_update_date		,
				created_by			,
				last_updated_by			,
				last_update_login		,
				request_id			,
				program_application_id		,
				program_id			,
				program_login_id		,
				object_version_number
				)
			SELECT
				zx_formula_details_s.NEXTVAL	,
				zx_formula_b_s.CURRVAL		,
				p_parent_regime_code		,
				p_parent_tax_code		,
				'ADD'				,
				'MIGRATED'			,
				SYSDATE				,
				SYSDATE				,
				fnd_global.user_id		,
				fnd_global.user_id		,
				fnd_global.conc_login_id	,
				fnd_global.conc_request_id     	,--Request Id
				fnd_global.prog_appl_id        	,--Program Application ID
				fnd_global.conc_program_id    	,--Program Id
				fnd_global.conc_login_id        ,--Program Login ID
				1

			FROM 	DUAL
			WHERE
			--Re-runnability
			NOT EXISTS 	(SELECT 1
					 FROM 	zx_formula_details, zx_formula_b
					 WHERE	zx_formula_details.formula_id = zx_formula_b.formula_id
					 AND	compounding_tax_regime_code = p_parent_regime_code
					 AND 	compounding_tax = p_parent_tax_code);

Line: 937

			INSERT INTO zx_formula_details (
				formula_detail_id		,
				formula_id			,
				compounding_tax			,
				compounding_tax_regime_code	,
				compounding_type_code		,
				record_type_code 		,
				creation_date			,
				last_update_date		,
				created_by			,
				last_updated_by			,
				last_update_login		,
				request_id			,
				program_application_id		,
				program_id			,
				program_login_id		,
				object_version_number
				)
				SELECT zx_formula_details_s.NEXTVAL	,
					zx_formula_b_s.CURRVAL	,
					parent_tax_code			,
					parent_regime_code		,
					'ADD'			,
					'MIGRATED'		,
					SYSDATE			,
					SYSDATE			,
					fnd_global.user_id		,
					fnd_global.user_id		,
					fnd_global.conc_login_id	,
					fnd_global.conc_request_id     	,--Request Id
					fnd_global.prog_appl_id        	,--Program Application ID
					fnd_global.conc_program_id    	,--Program Id
					fnd_global.conc_login_id        ,--Program Login ID
					1
				FROM 	zx_tax_relations_t t
				WHERE 	t.child_regime_code = p_child_regime_code
				AND 	t.child_tax_code = p_child_tax_code
				AND	t.tax_group_id = p_group_id
				AND	NOT EXISTS 	(SELECT 1
							 FROM 	zx_formula_details, zx_formula_b
							 WHERE	zx_formula_details.formula_id = zx_formula_b.formula_id
							 AND	compounding_tax_regime_code = p_parent_regime_code
							 AND 	compounding_tax = p_parent_tax_code
                                                         AND
contains(zx_formula_b.formula_code, t.tax_group_code) > 0
							)
 and rownum = 1;

Line: 1028

	SELECT DECODE(SIGN(LENGTHB('O_TB_' || p_tax) - 30),
			1,
			SUBSTRB('O_TB_' || p_tax,1,24)||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_RULES_B_S'),
			'O_TB_' || p_tax)
	INTO l_tax_rule_code
	FROM DUAL;

Line: 1041

		INSERT INTO zx_rules_b_tmp
			(tax_rule_code		,
			 tax          		,
			 tax_regime_code	,
			 service_type_code	,
			 application_id   	,
			 recovery_type_code 	,
			 priority		,
			 system_default_flag	,
			 effective_from		,
			 effective_to		,
			 enabled_flag		,
			 record_type_code	,
			 det_factor_templ_code	,
			 content_owner_id	,
			 tax_rule_id		,
			 created_by		,
			 creation_date          	,
			 last_updated_by        	,
			 last_update_date       	,
			 last_update_login      	,
			 request_id             	,
			 program_application_id,
			 program_id             	,
			 program_login_id		,
			 object_version_number)

		SELECT
			l_tax_rule_code 	,
			p_tax			,
			p_tax_regime_code	,
			'DET_TAXABLE_BASIS'	,
			NULL			,
			NULL			,
			1			,
			'N'			,  -- Bug 4590290
			p_effective_from	,
			p_effective_to		,
			p_enabled_flag		,
			'MIGRATED'		,
			'STCC'	,
			p_content_owner_id	,
			zx_rules_b_s.NEXTVAL	,
			fnd_global.user_id	,
			SYSDATE			,
			fnd_global.user_id	,
			SYSDATE			,
			fnd_global.conc_login_id,
			fnd_global.conc_request_id     		,--Request Id
			fnd_global.prog_appl_id        		,--Program Application ID
			fnd_global.conc_program_id    		,--Program Id
			fnd_global.conc_login_id        	,--Program Login ID
			1
		FROM 	DUAL
		WHERE
		--Re-runnability

		NOT EXISTS	(SELECT 1
				 FROM 	zx_rules_B
				 WHERE	substrb(tax_rule_code,1,24)      = (SELECT DECODE(SIGN(LENGTHB('O_TB_' || p_tax) - 30),
														1,
														SUBSTRB('O_TB_' || p_tax,1,24),
														SUBSTRB('O_TB_' || p_tax,1,24))
											FROM DUAL)
				 AND content_owner_id = p_content_owner_id
				 AND tax_regime_code = p_tax_regime_code
				 AND (effective_from BETWEEN p_effective_from AND NVL(p_effective_to,SYSDATE)
					OR
					NVL(effective_to,SYSDATE) BETWEEN p_effective_from AND NVL(p_effective_to,SYSDATE)
					)
				 AND enabled_flag   = 'Y'
				);

Line: 1120

 	-- Inserting values in table zx_rules_tl
	BEGIN
		INSERT INTO zx_rules_tl (
			tax_rule_id		,
			tax_rule_name	,
			created_by		,
			creation_date	,
			last_updated_by	,
			last_update_date	,
			last_update_login	,
			language		,
			source_lang

			)
		SELECT
			tax_rule_id		,
			    CASE WHEN tax_rule_code = UPPER(tax_rule_code)
			     THEN    Initcap(tax_rule_code)
			     ELSE
				     tax_rule_code
			     END,
			fnd_global.user_id	,
			SYSDATE		,
			fnd_global.user_id	,
			SYSDATE		,
			fnd_global.conc_login_id,
			l.language_code	,
			userenv('LANG')
		FROM 	fnd_languages l, zx_rules_b rules
		WHERE	l.installed_flag IN ('I', 'B')
		AND  	rules.record_type_code = 'MIGRATED'
		AND 	rules.tax_rule_code =  l_tax_rule_code
		AND  	NOT EXISTS (SELECT	NULL
				    FROM 	zx_rules_tl t
				    WHERE 	t.tax_rule_id = rules.tax_rule_id
				    AND 	t.language = l.language_code);

Line: 1163

	 -- Inserting values in table zx_process_results

	BEGIN

		INSERT INTO 	zx_process_results
				(condition_group_code		,
				 priority			,
				 result_type_code		,
				 tax_status_code 		,
				 numeric_result  		,
				 alphanumeric_result		,
				 status_result      		,
				 rate_result        		,
				 legal_message_code 		,
				 min_tax_amt        		,
				 max_tax_amt        		,
				 min_taxable_basis  		,
				 max_taxable_basis  		,
				 min_tax_rate       		,
				 max_tax_rate       		,
				 enabled_flag           	,
				 allow_exemptions_flag  	,
				 allow_exceptions_flag  	,
				 record_type_code       	,
				 result_api             	,
				 result_id              	,
				 content_owner_id       	,
				 condition_group_id     	,
				 tax_rule_id            	,
				 condition_set_id		,
				 exception_set_id		,
				 created_by             	,
				 creation_date          	,
				 last_updated_by        	,
				 last_update_date       	,
				 last_update_login      	,
				 request_id             	,
				 program_application_id 	,
				 program_id             	,
				 program_login_id		,
				 object_version_number)
			SELECT
				p_condition_grp_cd		,
				1			,
				'CODE'			,
				NULL			,
				NULL			,
				p_alphanumeric_result	,-- STANDARD_TB_DISCOUNT,STANDARD_QUANTITY or TAX_GROUP_CODE||'_'||TAX_CODE||'_TB'
				NULL			,
				NULL			,
				NULL			,
				NULL			,
				NULL			,
				NULL			,
				NULL			,
				NULL			,
				NULL			,
				p_enabled_flag		, -- also used in insert of zx_rules_b
				'N'			,
				'N'			,
				'MIGRATED'		,
				NULL			,
				zx_process_results_s.NEXTVAL	,
				p_content_owner_id		, -- also used in insert of zx_rules_b also
				p_condition_group_id		,
				zx_rules_b_s.CURRVAL	, -- zx_rules_b.tax_rule_id(based on rule created above)
				p_tax_condition_id	, --condition set id
				p_tax_exception_id	, --exception set id
				fnd_global.user_id           	,
				SYSDATE      		,
				fnd_global.user_id           	,
				SYSDATE			,
				fnd_global.conc_login_id	,
				fnd_global.conc_request_id     	,--Request Id
				fnd_global.prog_appl_id        	,--Program Application ID
				fnd_global.conc_program_id    	,--Program Id
				fnd_global.conc_login_id        ,--Program Login ID
				1

			FROM	dual
			WHERE
			--Re-runnability
			NOT EXISTS 	(SELECT 1
					 FROM 	zx_process_results, zx_rules_b
					 WHERE	zx_process_results.tax_rule_id = zx_rules_b.tax_rule_id
					 AND	zx_process_results.content_owner_id = p_content_owner_id
					 AND 	zx_process_results.condition_group_code = p_condition_grp_cd
					 AND 	zx_process_results.alphanumeric_result  = p_alphanumeric_result
		       );