Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table items (
- code varchar(255),
- integration_ref varchar(255),
- classification varchar(255),
- description varchar(255),
- uom varchar(255),
- itemtag varchar(255),
- usage_type varchar(255),
- original_price numeric(16,6),
- suite numeric(16,6),
- private numeric(16,6),
- semi_private numeric(16,6),
- ward numeric(16,6),
- opd numeric(16,6),
- charity numeric(16,6),
- mdrptag varchar(255),
- accounting numeric,
- admitting numeric,
- cathlab numeric,
- rehab numeric,
- ccu numeric,
- csr numeric,
- charityob numeric,
- ctscan numeric,
- cvor numeric,
- cvrr numeric,
- dr numeric,
- dental numeric,
- rehabmed numeric,
- diet numeric,
- endo numeric,
- echo numeric,
- eeg numeric,
- er numeric,
- eye numeric,
- hearing numeric,
- heart numeric,
- satheart numeric,
- hema numeric,
- hemo numeric,
- histo numeric,
- icu numeric,
- lab numeric,
- satlab numeric,
- mammo numeric,
- nicu numeric,
- newborn numeric,
- nucmed numeric,
- operating numeric,
- picu numeric,
- pmc numeric,
- pacu numeric,
- pulmo numeric,
- radio numeric,
- satxray numeric,
- diabetes numeric,
- radonco numeric,
- tele numeric,
- ultra numeric,
- uro numeric,
- wound numeric,
- charity_reader numeric,
- semi_private_reader numeric,
- private_reader numeric,
- suite_reader numeric,
- ward_reader numeric,
- opd_reader numeric
- );
- alter table items add ref_service_id bigint;
- insert into ref_service (id, active, classification) values (0, true, '');
- create or replace function pop_ref_service(classification text, description text, uom text, merxcode text, sapcode text, itemtag text, usage_type text, mdrptag text)
- returns void as $$
- declare
- max_id bigint;
- begin
- select max(id) into max_id from ref_service;
- insert into ref_service (id, classification, created_by, created_date, updated_date, active, barcode, code, description, brand_name, uom, item_type, usage_type, integration_ref, doh)
- values (case when max_id is null then 0 else max_id + 1 end, CASE WHEN classification = 'CONSIGNED' THEN 'MISCELLANEOUS' ELSE classification END, 'IMPORT_SCRIPT', current_date, current_date, true, '1', merxcode, description, case when classification = 'DRUG' then description else null end, uom, case when classification = 'SERVICE_EQUIPMENT' AND usage_type = 'PER_USE' then 'EQUIPMENT' when classification = 'SERVICE_EQUIPMENT' AND (usage_type is null OR usage_type = '') then 'SERVICE' else null end, usage_type, sapcode, case when mdrptag = 'TRUE' then true else null end);
- raise notice 'Inserted: %', merxcode;
- select max(id) into max_id from ref_service;
- update items set ref_service_id = max_id where code = merxcode;
- IF itemtag is not null AND itemtag != '' AND itemtag != '-' THEN
- insert into ref_service_tag (ref_service_id, ref_item_tag) values (max_id, itemtag);
- END IF;
- end;
- $$ language plpgsql;
- select pop_ref_service(classification, description, uom, code, integration_ref, itemtag, usage_type, mdrptag) from items;
- create or replace function pop_ref_price(
- item_id bigint,
- cost_p numeric,
- suite_p numeric,
- private_p numeric,
- semi_private_p numeric,
- ward_p numeric,
- charity_rate_p numeric,
- opd_p numeric,
- charity_reader numeric,
- semi_private_reader numeric,
- private_reader numeric,
- suite_reader numeric,
- ward_reader numeric,
- opd_reader numeric,
- item_class text
- ) returns void as $$
- declare
- max_id bigint;
- begin
- select max(id) into max_id from ref_price;
- insert into ref_price (id, created_by, created_date, updated_date, active, price, billing_category_id, effective_dt, original_price, version, ref_service_id, readers_fee)
- values
- (max_id + 1, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 20, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE opd_p END, 1, item_id, null),
- (max_id + 2, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 19, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE opd_p END, 1, item_id, null),
- (max_id + 3, 'IMPORT_SCRIPT', current_date, current_date, true, ward_p, 1, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE ward_p END, 1, item_id, ward_reader),
- (max_id + 4, 'IMPORT_SCRIPT', current_date, current_date, true, ward_p, 2, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE ward_p END, 1, item_id, ward_reader),
- (max_id + 5, 'IMPORT_SCRIPT', current_date, current_date, true, suite_p, 3, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE suite_p END, 1, item_id, suite_reader),
- (max_id + 6, 'IMPORT_SCRIPT', current_date, current_date, true, suite_p, 4, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE suite_p END, 1, item_id, suite_reader),
- (max_id + 7, 'IMPORT_SCRIPT', current_date, current_date, true, private_p, 5, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE private_p END, 1, item_id, private_reader),
- (max_id + 8, 'IMPORT_SCRIPT', current_date, current_date, true, private_p, 6, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE private_p END, 1, item_id, private_reader),
- (max_id + 9, 'IMPORT_SCRIPT', current_date, current_date, true, semi_private_p, 7, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE semi_private_p END, 1, item_id, semi_private_reader),
- (max_id + 10, 'IMPORT_SCRIPT', current_date, current_date, true, semi_private_p, 8, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE semi_private_p END, 1, item_id, semi_private_reader),
- (max_id + 11, 'IMPORT_SCRIPT', current_date, current_date, true, charity_rate_p, 9, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE charity_rate_p END, 1, item_id, charity_reader),
- (max_id + 12, 'IMPORT_SCRIPT', current_date, current_date, true, charity_rate_p, 10, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE charity_rate_p END, 1, item_id, charity_reader),
- (max_id + 13, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 11, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE opd_p END, 1, item_id, opd_reader),
- (max_id + 14, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 12, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
- cost_p ELSE opd_p END, 1, item_id, opd_reader);
- end;
- $$ language plpgsql;
- insert into ref_price (id, active) values (0, true);
- select pop_ref_price(ref_service_id, original_price, suite, private, semi_private, ward, charity, opd, charity_reader, semi_private_reader, private_reader, suite_reader, ward_reader, opd_reader, classification) from items;
- create or replace function pop_ref_service_inventory_item(ref_service_i numeric, department numeric) returns void as $$
- declare
- max_id numeric;
- count numeric;
- loc_code text;
- item_code text;
- begin
- select count(*) into count from ref_service_item where department_id = department AND ref_service_id = ref_service_i;
- IF count = 0 THEN
- select max(id) into max_id from ref_service_item;
- select code into loc_code from ref_department where id = department;
- select code into item_code from ref_service where id = ref_service_i;
- insert into ref_service_item (id, created_date, created_by, updated_date, updated_by, active, department_id, ref_service_id) values (max_id + 1, current_date, 'IMPORT_SCRIPT', current_date, 'IMPORT_SCRIPT', true, department, ref_service_i);
- raise notice 'Inserted itemcode:% department code:%', item_code, loc_code;
- END IF;
- end;
- $$ language plpgsql;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement