CREATE OR REPLACE FUNCTION generateGoodReceipt(p_product character varying, p_storagebin character varying, p_documentno character varying, p_linecount numeric) RETURNS numeric AS $BODY$ DECLARE v_MInOut_Id VARCHAR(32); v_m_inoutline_id VARCHAR(32); v_adClient_Id VARCHAR(32); v_adOrg_Id VARCHAR(32); v_cDocType_Id VARCHAR(32); v_c_bpartner_id VARCHAR(32); v_c_bpartner_location_id VARCHAR(32); v_m_warehouse_id VARCHAR(32); v_m_locator_id VARCHAR(32); v_m_product_id VARCHAR(32); v_c_uom_id VARCHAR(32); v_m_attributeset_id VARCHAR(32); v_m_attributesetinstance_id VARCHAR(32); v_serno VARCHAR(20); v_lineno numeric; v_linecount numeric := 1; v_count numeric; v_mod numeric; BEGIN --M inout Id v_MInOut_Id := (select get_uuid()); --Org Id, Client Id select ad_org_id, ad_client_id into v_adOrg_Id, v_adClient_Id from ad_org where value = 'F&B US East Coast'; --DOC type select C_DOCTYPE_ID into v_cDocType_Id from C_DOCTYPE where name='MM Receipt'; --bpartner select c_bpartner_id into v_c_bpartner_id from c_bpartner where name = 'phone supplier A'; if(v_c_bpartner_id is null or v_c_bpartner_id = '') then raise exception '%', 'Not find business partner which name is: phone supplier A'; end if; --bpartner location select c_bpartner_location_id into v_c_bpartner_location_id from c_bpartner_location where c_bpartner_id = v_c_bpartner_id limit 1; --warehouse select m_warehouse_id into v_m_warehouse_id from m_locator where value=p_storagebin;--"In Transit" if(v_m_warehouse_id is null or v_m_warehouse_id = '') then raise exception '%', 'Not find storage bin which value is: ' || p_storagebin; end if; --"Goods Receipt Header" raise notice '%', 'Starting to create minout header, id: ' || v_MInOut_Id; INSERT INTO m_inout( m_inout_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, issotrx, documentno, docaction, docstatus, posted, processing, processed, c_doctype_id, description, c_order_id, dateordered, isprinted, movementtype, movementdate, dateacct, c_bpartner_id, c_bpartner_location_id, m_warehouse_id, poreference, deliveryrule, freightcostrule, freightamt, deliveryviarule, m_shipper_id, c_charge_id, chargeamt, priorityrule, dateprinted, c_invoice_id, createfrom, generateto, ad_user_id, salesrep_id, nopackages, pickdate, shipdate, trackingno, ad_orgtrx_id, c_project_id, c_campaign_id, c_activity_id, user1_id, user2_id, updatelines, islogistic, generatelines, calculate_freight, delivery_location_id, m_freightcategory_id, freight_currency_id, rm_receipt_pickedit, rm_shipment_pickedit, m_condition_goods_id, a_asset_id, c_costcenter_id, process_goods_java ) VALUES (v_MInOut_Id, v_adClient_Id, v_adOrg_Id, 'Y', now(), '0', now(), '0', 'N', p_documentno, 'CO', 'DR', 'N', 'N', 'N', v_cDocType_Id, null, null, now(), 'N', 'V+', now(), now(), v_c_bpartner_id, v_c_bpartner_location_id, v_m_warehouse_id, null, 'A', 'I', 0, 'P', null, null, 0, '5', null, null, 'N', 'N', null, null, null, null, null, null, null, null, null, null, null, null, 'N', 'N', 'N', null, null, null, null, null, null, null, null, null, 'CO' ); raise notice '%', 'Finish minout header.'; --"Goods Receipt Lines" --UOM select c_uom_id into v_c_uom_id from c_uom where name = 'Unit'; --product select m_product_id into v_m_product_id from m_product where value=p_product; if(v_m_product_id is null or v_m_product_id = '') then raise exception '%', 'Not find product which value is: ' || p_product; end if; --locator select m_locator_id into v_m_locator_id from m_locator where value =p_storagebin; --IMEI attribute set select m_attributeset_id into v_m_attributeset_id from m_attributeset where name ='IMEI'; raise notice '%', 'Starting to create minout lines.'; for v_linecount in 1..p_linecount loop v_mod :=v_linecount%1000; --M inout line Id v_m_inoutline_id := (select get_uuid()); SELECT COALESCE(MAX(Line),0)+10 into v_lineno FROM M_InOutLine WHERE M_InOut_ID=v_MInOut_Id; --Create attributesetinstance --attribute set instance id v_m_attributesetinstance_id := (select get_uuid()); --serno v_serno := to_char(substring(p_documentno, 1, 2)) || v_linecount; if(v_mod =0) then --raise notice '%', 'Line NO is - ' || TO_CHAR(v_linecount, '9'); --raise notice '%', 'Goods Receipt line - ' || TO_CHAR(v_lineno); raise notice '%', 'Created Serial Number - ' || v_serno; end if; INSERT INTO m_attributesetinstance( m_attributesetinstance_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, m_attributeset_id, serno, lot, guaranteedate, description, m_lot_id, islocked, lock_description) VALUES (v_m_attributesetinstance_id, v_adClient_Id, v_adOrg_Id, 'Y', now(), '0', now(), '0', v_m_attributeset_id, v_serno, null, null, '#'||v_serno, null, 'N', null); INSERT INTO m_inoutline( m_inoutline_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, line, description, m_inout_id, c_orderline_id, m_locator_id, m_product_id, c_uom_id, movementqty, isinvoiced, m_attributesetinstance_id, isdescription, quantityorder, m_product_uom_id, m_condition_goods_id, canceled_inoutline_id, a_asset_id, manage_prereservation, user1_id, user2_id, c_project_id, c_costcenter_id, c_bpartner_id, explode, bom_parent_id) VALUES (v_m_inoutline_id, v_adClient_Id, v_adOrg_Id, 'Y', now(), '0', now(), '0', v_lineno, null, v_MInOut_Id, null, v_m_locator_id, v_m_product_id, v_c_uom_id, 1, 'N', v_m_attributesetinstance_id, 'N', null, null, null, null, null, 'N', null, null, null, null, v_c_bpartner_id, 'N', null); --calculate line no v_count:=v_count+1; end loop; raise notice '%', 'Finished.'; return v_count; END ; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION generateGoodReceipt(character varying, character varying, character varying, numeric) OWNER TO tad; --Execute query select generateGoodReceipt('Galaxy S5 Whilte', 'Central Warehouse', 'A0100001', 4) from dual;