|
avijit111@gmail.com
Yak Posting Veteran
55 Posts |
 Posted - 2009-10-09 : 09:27:46
|
| In the below code i would like to map the vendor_name from the (pur_company_vendor_master)bracket table. please help me how to map those to table.Can u show me please how to map because i am not getting any similar data.please help me out.....thankyou.... --exec PO_perday_authorize '','',,'','' --grant all on PO_perday_authorize to public ***********************************************************/ alter proc PO_perday_authorize @company_no glcompany, @location_no gllocn, @lang_id int, @cdate datetime, @pr_cata varchar(20) as begin set nocount on if (@pr_cata = 'General') begin select @pr_cata = 'GEN' end if (@pr_cata = 'Service') begin select @pr_cata = 'SER' end if (@pr_cata = 'All') Begin select @pr_cata = '%' end insert into ft_PO_perday_authorize (company_no, location_no, po_no, cdate, dept, authorized_by) select a.company_code, a.locn_no, a.po_no, convert(varchar(11), a.authorise_date,103), a.where_used, a.authorise_user_name from pur_po_header a where a.po_status = 'O' and a.authorise_date = @cdate and a.company_code = @company_no and a.locn_no = @location_no and a.po_category like @pr_cata update ft_PO_perday_authorize set dept_desc = a.param_value from pur_comp_parameter_detail a(nolock), ft_PO_perday_authorize b(nolock) where a.company_code = b.company_no and a.param_class = 'USRDFL' and a.param_cat = 'POM' and a.param_type = 'WUSD' and a.param_code = b.dept -- update ft_PO_perday_authorize -- set requester_name = rtrim(ISNULL(H04_First_Name,''))+' '+rtrim(ISNULL(H04_Middle_Name,''))+' '+rtrim(ISNULL(H04_Last_Name,'')) -- from common..h04_namemaster a(nolock), ft_PO_perday_authorize b(nolock) -- where a.C03_Organisation_code = @company_no -- and a.H01_emp_num = b.requester select company_no, location_no, po_no, cdate current_dt, dept_desc department, authorized_by from ft_PO_perday_authorize (nolock) order by dept_desc, po_no delete from ft_PO_perday_authorizeset nocount onend/******************************************/ CREATE TABLE [dbo].[pur_company_vendor_master] ( [company_code] [GLCOMPANY] NOT NULL , [vendor_code] [cmn_cust_ven_code] NOT NULL , [vendor_name] [CMN_NAME] NOT NULL , [vendor_add1] [CMN_DESCRIPTION] NULL , [vendor_add2] [CMN_DESCRIPTION] NULL , [vendor_add3] [CMN_DESCRIPTION] NULL , [city] [CMN_DESCRIPTION] NULL , [state] [CMN_DESCRIPTION] NULL , [country] [CMN_DESCRIPTION] NULL , [zip_code] [CMN_DESCRIPTION] NULL , [vendor_type] [PURFLAG] NOT NULL , [vendor_category] [PURFLAG] NULL , [vendor_class] [PURSDESC] NULL , [lang_id] [PURFLAG] NOT NULL , [tax_group] [PURSDESC] NULL , [customer_flag] [PURFLAG] NOT NULL , [customer_code] [cmn_cust_ven_code] NULL , [vendor_status] [PURFLAG] NOT NULL , [approved_flag] [PURFLAG] NOT NULL , [phone_no] [PURPHONE] NULL , [telex_no] [CMN_NAME] NULL , [fax_no] [PURPHONE] NULL , [payment_mode] [PURSDESC] NULL , [pay_term_no] [purdoc] NULL , [reason] [CMN_DESCRIPTION] NULL , [freeze_date] [datetime] NULL , [currency_code] [GLCURRCODE] NULL , [parent_vendor] [cmn_cust_ven_code] NULL , [create_locn_no] [GLLOCN] NOT NULL , [created_date] [datetime] NOT NULL , [user_id] [cmn_userid] NOT NULL , [modified_date] [datetime] NULL , [modified_user_id] [cmn_userid] NULL , [timestamp] [timestamp] NOT NULL , [ch_id] [VAT_ID] NULL , [ec_id] [VAT_ID] NULL , [po_allowed] [PURFLAG] NULL , [mo_allowed] [PURFLAG] NULL , [check_allowed] [PURFLAG] NULL , [vendor_representative] [CMN_NAME] NULL , [country_code] [GLCNTCODE] NULL , [invoice_subtrantype] [PURSDESC] NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[pur_po_header] ( [company_code] [GLCOMPANY] NOT NULL , [locn_no] [GLLOCN] NOT NULL , [po_no] [cmn_tranno] NOT NULL , [amend_no] [cmn_slno] NOT NULL , [amend_date] [datetime] NULL , [amend_reference] [CMN_DESCRIPTION] NULL , [amend_desc] [CMN_DESCRIPTION] NULL , [ack_no] [PURSDESC] NULL , [ack_date] [datetime] NULL , [ack_remarks] [CMN_DESCRIPTION] NULL , [po_date] [datetime] NOT NULL , [po_type] [PURPOTYPE] NOT NULL , [po_category] [PURPOTYPE] NOT NULL , [po_mode] [PURPOTYPE] NOT NULL , [buyer_code] [purdoc] NULL , [module_id] [cmn_slno] NOT NULL , [po_status] [PURFLAG] NOT NULL , [onetime_vendor_flag] [PURFLAG] NOT NULL , [po_close_date] [datetime] NULL , [vendor_code] [cmn_cust_ven_code] NOT NULL , [payto_vendor] [cmn_cust_ven_code] NOT NULL , [pay_term_no] [purdoc] NULL , [advance_payable] [cmn_Pamt] NOT NULL , [advance_paid] [cmn_Pamt] NOT NULL , [schedule_flag] [PURFLAG] NOT NULL , [delivery_date] [datetime] NULL , [shipment_flag] [PURFLAG] NOT NULL , [gi_flag] [PURFLAG] NOT NULL , [quality_indicator] [PURFLAG] NOT NULL , [base_curr_value] [cmn_Pamt] NOT NULL , [po_value] [cmn_Pamt] NOT NULL , [po_addnl_value] [cmn_Pamt] NOT NULL , [po_total_value] [cmn_Pamt] NOT NULL , [tr_currency_code] [GLCURRCODE] NOT NULL , [tr_exchange_rate] [cmn_Pexch_rate] NOT NULL , [quot_no] [cmn_tranno] NULL , [reason] [CMN_DESCRIPTION] NULL , [payment_mode] [PURSDESC] NULL , [ins_liability] [PURFLAG] NOT NULL , [ins_term] [CMN_DESCRIPTION] NULL , [ins_amount] [cmn_Pamt] NOT NULL , [valid_from] [datetime] NULL , [valid_to] [datetime] NULL , [allocated_amount] [cmn_Pamt] NOT NULL , [remaining_amount] [cmn_Pamt] NOT NULL , [lc_applicable_flag] [PURFLAG] NOT NULL , [last_nego_date] [datetime] NULL , [last_ship_date] [datetime] NULL , [trans_ship_flag] [PURFLAG] NOT NULL , [part_ship_flag] [PURFLAG] NOT NULL , [authorise_user_name] [cmn_username] NULL , [authorise_date] [datetime] NULL , [prev_po_status] [PURFLAG] NULL , [ap_locn_no] [GLLOCN] NULL , [fob_point] [PURSDESC] NULL , [where_used] [PURSDESC] NULL , [payment_flag] [PURFLAG] NOT NULL , [freeze_date] [datetime] NULL , [inco_terms] [PURSDESC] NULL , [fs_fund_number] [FSFUNDNO] NULL , [asset_locn] [GLLOCN] NULL , [asset_id] [FAASSET] NULL , [cc_allocate_flag] [PURFLAG] NOT NULL , [remarks] [CMN_DESCRIPTION] NULL , [create_locn_no] [GLLOCN] NOT NULL , [created_date] [datetime] NOT NULL , [user_id] [cmn_userid] NOT NULL , [modified_date] [datetime] NULL , [modified_user_id] [cmn_userid] NULL , [timestamp] [timestamp] NOT NULL , [vat_desirable] [PURFLAG] NULL , [vat_inclusive] [PURFLAG] NULL , [vat_category] [VAT_CATEGORY_CODE] NULL , [vat_class] [VAT_CLASS_CODE] NULL ) ON [PRIMARY]GO |
|
hi to all.......,