设置block的where条件并执行查询代码,OracleEBS代码库,德仔网

代码语言

代码分类

【OracleEBS】设置block的where条件并执行查询

作者:Dezai.CN / 发布于2013/4/8/ 2023


	declare
	 where_str varchar2(500);
	begin
	 set_item_property('HEADER.REPORTING',enabled,PROPERTY_TRUE);
	 set_block_property('WST_AR_SALES_EVALUATION_T',query_allowed,PROPERTY_TRUE);
	 set_block_property('WST_AR_SALES_EVALUATION_T',insert_allowed,PROPERTY_false);
	 :OPERATION_TYPE:='FIND';
	 :QUERY_TYPE:='A';
	 --where_str:='Status <> ''C''';
	 where_str:='Status <> ''C'' and Loading_Date>=TO_DATE('''||:HEADER.LOAD_DATE_FROM || ''',''dd/mon/yy'') and Loading_Date<= to_Date(''' ||:HEADER.LOAD_DATE_TO||''',''dd/mon/yy'')';
	 if :HEADER.INVOICE_DATE_FROM is not null then
	 where_str:=where_str || ' and Invoice_Date >=TO_DATE('''|| :HEADER.INVOICE_DATE_FROM ||''',''dd/mon/yy'')';
	 end if;
	 if :HEADER.INVOICE_DATE_TO is not null then
	 where_str:=where_str || ' and Invoice_Date <=TO_DATE('''|| :HEADER.INVOICE_DATE_TO ||''',''dd/mon/yy'')';
	 end if;
	 if :HEADER.CURRENCY_CODE is not null then
	 where_str:=where_str || ' and Curr_Code= ''' || :HEADER.CURRENCY_CODE||'''';
	 end if;
	 if :HEADER.COMPANY1 is not null then
	 where_str:=where_str || ' and Company= ''' || :HEADER.COMPANY1||'''';
	 end if;
	 --fnd_message.debug(:HEADER.COMPANY1);
	 where_str:=where_str; --|| ' order by WS_Item_No,Sales_Order,Line';
	 --fnd_message.debug(where_str);
	
	 go_block('WST_AR_SALES_EVALUATION_T');
	 clear_block(no_commit);
	 go_block('WST_AR_SALES_EVALUATION_T');
	 set_item_property('WST_AR_SALES_EVALUATION_T.CONFIRMED',enabled,PROPERTY_FALSE);
	 set_item_property('WST_AR_SALES_EVALUATION_T.CANCELED',enabled,PROPERTY_TRUE);
	 set_item_property('WST_AR_SALES_EVALUATION_T.CANCELED',update_allowed,PROPERTY_TRUE);
	 set_block_property('WST_AR_SALES_EVALUATION_T',default_where,where_str);
	 execute_query;
	 if :WST_AR_SALES_EVALUATION_T.EVALUATION_LINE_ID is null then
	 :WST_AR_SALES_EVALUATION_T.CONFIRMED:='N';
	 set_item_property('WST_AR_SALES_EVALUATION_T.CANCELED',enabled,PROPERTY_false);
	 end if;
	 --set_block_property('WST_AR_SALES_EVALUATION_T',query_allowed,PROPERTY_FALSE);
	
	EXCEPTION
	 WHEN OTHERS THEN
	 fnd_message.debug('Query Error!');
	 RAISE FORM_TRIGGER_FAILURE;
	end;

试试其它关键字

　设置block的where条件　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)