PGSQL PROCEDUCE DEMO FOR LEARNING

-- DROP PROCEDURE cogs.cal_cogs_procedure(text);

CREATE OR REPLACE PROCEDURE cogs.cal_cogs_procedure(IN fl_only_refresh_no_result text DEFAULT 'Y'::text)
 LANGUAGE plpgsql
AS $procedure$
declare
	raw_row 								record;
	cal_row  					cogs."RAW_DATA_RESULT";
	temp_date                              Date;
	temp_nr								   int;
	sql_where                              text:='';

	begin

	    if fl_only_refresh_no_result = 'Y' then
	        sql_where = ' and cd_raw_data_result is null order by ctid asc limit 10000';
	    end if;
		for raw_row in execute 'select *,ctid from cogs."RAW_DATA" where 1 = 1' || sql_where
		loop
			begin
			cal_row = null;	
			temp_date = null;
			temp_nr = 0;
			raw_row.etd = replace(raw_row.etd,'''','');
			raw_row.item_no = replace(raw_row.item_no,'''','');
			raw_row.unit_selling_price = replace(raw_row.unit_selling_price,'''','');
			raw_row.data_type = replace(raw_row.data_type,'''','');
			raw_row.region_name = replace(raw_row.region_name,'''','');
			raw_row.currency_code = replace(raw_row.currency_code,'''','');
			raise notice '%',raw_row.ctid;
			raise notice '%','AO---';
			--region
			select region_define into cal_row.region  from cogs."REGION_DEFINE" where region = raw_row.region_name;

			--AO
			if raw_row.unit_selling_price is null or raw_row.unit_selling_price = '' then
				select d.ratio into cal_row.price_factor from cogs."FORECAST_DEFINE" d  where d.region = cal_row.region;
			else
				select rate_curr_usd into cal_row.price_factor  from cogs."EXCHANGE_RATE" where currency_type = raw_row.currency_code;
			end if;
			--
			raise notice '%','AP---';
			--AP
			if raw_row.unit_selling_price != '' AND raw_row.unit_selling_price::float > 0 then
				cal_row.sell_price = raw_row.unit_selling_price;
			else
				cal_row.sell_price = 0;
			end if;

			raise notice '%','AQ---';
			--AQ
			if cal_row.sell_price::float > 0 then
				cal_row.tp_aq = 0;
			else
				select stp_usd into cal_row.tp_aq  from cogs."PRICE_DEFINE" where price_list_name = raw_row.item_no;
			end if;
			if cal_row.tp_aq is null then
				cal_row.tp_aq = 0;
			end if;
			--

			raise notice '%','AR---';
			--AR
			if cal_row.tp_aq::float = 0  and cal_row.sell_price::float = 0 then
				cal_row.std_cost = raw_row.item_cost_usd;
			else
				cal_row.std_cost = 0;
			end if;
			--

			raise notice '%','AS---';
			--AS--
			if cal_row.sell_price::float = 0 and cal_row.tp_aq::float = 0 and cal_row.std_cost::float = 0 then
			  select price into cal_row.est_cost  from cogs."ESTIMATED_PRICE_DEFINE" where estimated_list = raw_row.item_no;
			else
				cal_row.est_cost = 0;
			end if;
			--

			raise notice '%','AT---';
			--AT--
			raise notice '%' ,cal_row.sell_price;
			raise notice '%' ,cal_row.price_factor;
			if cal_row.sell_price::float > 0 then
			 	cal_row.total = cal_row.price_factor::float * cal_row.sell_price::float * raw_row.schedule_qty::float;
			elsif cal_row.tp_aq::float > 0  then
				cal_row.total = cal_row.tp_aq::float * raw_row.schedule_qty::float;
			elsif cal_row.std_cost::float > 0  then
				cal_row.total = cal_row.std_cost::float * cal_row.price_factor::float * raw_row.schedule_qty::float;
			else
				cal_row.total = cal_row.est_cost::float * raw_row.schedule_qty::float;
			end if;
			--

			raise notice '%','AU---';
			--AU

			cal_row.tp_au = cal_row.total::float  /  raw_row.schedule_qty::float;
			--

			raise notice '%','AV---';
			--AV
			if raw_row.item_cost_usd::float > 0 then
			   cal_row.cogs = raw_row.schedule_qty::float * raw_row.item_cost_usd::float;
			else
			   cal_row.cogs = cal_row.tp_au::float * 0.8 * raw_row.schedule_qty::float;
			end if;
			--

			--invoice m
					--ship type use invoice date
			raise notice '%','invoice---1';
			if raw_row.data_type ilike 'ship' then
				--cal_row.invoice_month =  to_char(to_date(raw_row.invoice_date,'MM/DD/YYYY'),'YYYYMM');
				cal_row.invoice_month =  to_char(raw_row.invoice_date::timestamp,'YYYYMM');
			end if;

			raise notice '%','invoice---2';
			if raw_row.data_type ilike 'forecast' or raw_row.data_type ilike 'so' then
				raise notice '%','invoice---2.1';
				--have etd_date
				if raw_row.etd is not null and raw_row.etd != '' then
					if  TO_DATE(TO_CHAR(raw_row.etd::timestamp,'YYYYMM'),'YYYYMM') < TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYYMM'),'YYYYMM') then --ETD PASS DUE
						cal_row.invoice_month = to_char(CURRENT_DATE, 'YYYYMM');
					else
						cal_row.invoice_month = to_char(raw_row.etd::timestamp,'YYYYMM');
					end if;
				end if;
				raise notice '%','invoice---2.2';
				if raw_row.etd is null or raw_row.etd = '' then
					if raw_row.ready_date != '' and  raw_row.ready_date is not null then -- CRD
							temp_date = to_date(to_char(raw_row.ready_date::timestamp,'MM/DD/YYYY') , 'MM/DD/YYYY');
					elsif raw_row.re_promise_date != '' and raw_row.re_promise_date is not null then --Promised Date
							temp_date = to_date(to_char(raw_row.re_promise_date::timestamp,'MM/DD/YYYY') , 'MM/DD/YYYY');
					elsif raw_row.re_schedule_ship_date != '' and raw_row.re_schedule_ship_date is not null then --Schedule Date
							temp_date = to_date(to_char(raw_row.re_schedule_ship_date::timestamp,'MM/DD/YYYY') , 'MM/DD/YYYY');
					elsif raw_row.request_date_etd != '' and raw_row.request_date_etd is not null then  -- Request Date
							temp_date = to_date(to_char(raw_row.request_date_etd::timestamp,'MM/DD/YYYY') , 'MM/DD/YYYY');
					end if;
				end if;
				raise notice '%','invoice---2.3';
				if temp_date is not null then
					raise notice '%','invoice---2.3.1';
					if raw_row.data_type ilike 'forecast' then
						if raw_row.item_no ilike '%DG%' then
							select fcst_dg into temp_nr from cogs."SALES_M_DEFINE" s  where s.region = cal_row.region;
						else
							select fcst_non_dg into temp_nr from cogs."SALES_M_DEFINE" s where s.region = cal_row.region;
						end if;
					end if;
					raise notice '%','invoice---2.3.2';
					if raw_row.data_type ilike 'so' then
						if raw_row.item_no ilike '%DG%' then
							select so_dg into temp_nr from cogs."SALES_M_DEFINE" s  where s.region = cal_row.region;
						else
							select so_non_dg into temp_nr from cogs."SALES_M_DEFINE" s  where s.region = cal_row.region;
						end if;
					end if;
				end if;
				raise notice '%', temp_date;
				raise notice '%', temp_nr;
				if temp_date is not null then
						temp_date = temp_date + COALESCE(temp_nr,0) * interval '1 days';
						if temp_date < TO_DATE(TO_CHAR(CURRENT_DATE, 'MM/DD/YYYY'),'MM/DD/YYYY') then
							cal_row.invoice_month = to_char(CURRENT_DATE, 'YYYYMM');
						else
							cal_row.invoice_month = to_char(temp_date,'YYYYMM');
						end if;
				end if;


			end if;

			--
			EXCEPTION WHEN others then
				rollback;
				RAISE NOTICE 'An unexpected error occurred: %', SQLERRM;
				update cogs."RAW_DATA" set cd_raw_data_result  = null where ctid = raw_row.ctid ;
				delete from cogs."RAW_DATA_RESULT" where cd_raw_data_result = raw_row.cd_raw_data_result;
			end;
			--插入结果
			if cal_row.invoice_month is not null then
                cal_row.dt_record = now();
                cal_row.cd_raw_data_result = nextval('cogs."RAW_DATA_RESULT_cd_raw_data_result_seq"'::regclass);
                INSERT INTO cogs."RAW_DATA_RESULT" SELECT cal_row.*;
                update cogs."RAW_DATA" set cd_raw_data_result  = cal_row.cd_raw_data_result where ctid = raw_row.ctid ;
                delete from cogs."RAW_DATA_RESULT" where cd_raw_data_result = raw_row.cd_raw_data_result;
                commit ;
	       end if;



		end loop;

	END;
$procedure$
;