K3 Database Related

Account set Information
KDACCOUNT_GL in the KDACCTDB database.
SELECT * FROM T_AD_KDACCOUNT_GL version 14.0

K3 each table explains
SELECT * FROM T_tabledescription

Under SQL server2008
1. Get all database names:
SELECT Name from Master. sysdatabases ORDER by Name

2. Get all table names:
SELECT Name from DatabaseName. SysObjects Where xtype= ' U ' ORDER by Name
Xtype= ' U ': represents all user tables;
Xtype= ' S ': denotes all system tables;
3. Get all field names:
SELECT Name from syscolumns WHERE id=object_id (' TableName ')

in the Background database Icclasstype table, the field fid<0 is the old single, Fid>0 is the new single.
----------------system Settings------------------------
fstatus:0--has not been approved; 2--partial line closed; 3--closed ;
===============================================
Step One: Check the Ftableid of the business document from the T_tabledescription table first
Step two: Again according to Ftableid, from the T_fielddescription table check the field name of the business document, the field description
These two tables can be used to query all Kingdee business documents using the following:
SELECT * from t_tabledescription WHERE fdescription like '% inbound% '--system table
SELECT * from t_tabledescription WHERE ftablename like '%icstockbill% '-system table
SELECT * from T_tabledescription WHERE ftableid=17-system table
SELECT * from t_fielddescription WHERE fdescription like '% lot number% '--field table
SELECT * from t_fielddescription WHERE ffieldname like '%fbatchno% '--Field table
SELECT * from T_fielddescription WHERE ftableid=17--Field table

SELECT * from Icclasstype WHERE fname_chs like '% supply% '--use this table to basically find all the tables
SELECT * from Ictranstype-document type table, query the business document type Ftrantypeid, often and out of the inbound table with accurate query
===============================================
SELECT * FROM T_log--Log table
SELECT * from ictemplate--field Template table header
SELECT * from ictemplateentry--field template entry
SELECT * FROM Iiclisttemplate--order time thin template
SELECT * FROM t_userprofile-user Scenarios Table
------------------Multi-level audit------------------------
SELECT * FROM icclassmctemplate--Approval Flow Template table
SELECT * FROM Icclassmctableinfo--Approval flow template Schedule
SELECT * FROM Icclassmctemplatemap--document-Audit Flow template mapping table
SELECT * FROM ICClassMCStatus200000007--Document approval step table
SELECT * FROM ICClassMCRecord200000007--document audit record form
--delete document data, cause the audit process cannot be modified, prompt "document is being audited"
SELECT * from Icclassmctemplate WHERE fclasstypeid=200000008
DELETE from Icclassmctemplate WHERE fclasstypeid=200000008
DELETE from Icclassmctemplatemap WHERE fclasstypeid=200000008
DELETE from Icclassmctableinfo WHERE ftemplateid=87
-----------------Basic Data Settings---------------------
SELECT * from T_item--accounting for project base data, where Fitemclassid value
--Indicates 1-Customer 2-Department 3-Staff 4-product 5-Position 7-Unit 8-Suppliers
SELECT * from T_stock--warehouse
SELECT * FROM t_emp--staff table
SELECT * FROM T_department-Department
SELECT * from T_organization-Customer
SELECT * from T_measureunit-Unit of Measure
SELECT * FROM T_supplier--supplier table
SELECT * FROM T_submessage--auxiliary data table
SELECT * FROM Submestype--auxiliary data classification table
-----------------Item Setup--------------------------
SELECT * from T_icitem--material table
material attribute 1-outsourcing; 2-self-made (characteristic configuration); 3-outsourcing processing; 4-; 5-Virtual parts; 6-characteristic class; 7-Configuration class; 8-planning class; 9-assembly parts;
SELECT * from T_icitemcore-core table, associated with other tables via Fitemid
SELECT * FROM t_icitembase-basic data sheet, including rule type, unit, etc.
SELECT * FROM T_icitemmaterial-----------logistics data sheet, including cost valuation method, accounting ledger account
SELECT * from T_icitemplan-schedule table, including planning strategy and routing, etc.
SELECT * FROM t_icitemdesign-design data sheet, including net weight, gross weight, long width and high
SELECT * from T_icitemstandard-standard table with standard cost, hours, etc.
SELECT * FROM t_icitemquality--quality data sheet, inspection data
SELECT * FROM T_base_icitementrance--Import and export data sheet, English name, HS code, etc.
SELECT * FROM T_icitemcustom--All custom field tables for the item
------------------Supply Chain setup-----------------------
SELECT * FROM Icstockbill-All inbound single headers where: The Ftrantype value indicates the document type as follows: 1-Purchase Inbound 10-Other warehousing 21-sales out of stock 29-Other outbound 41-allocation order 100-Cost Adjustment sheet
SELECT * FROM Icstockbillentry---all out-of-stock single-table body heads are associated with Finterid
SELECT * FROM Ictransactiontype-out-of-storage report
SELECT * from Icinventory--Real warehouse storage table instant Inventory table
SELECT * FROM Poinventory--Virtual warehouse storage table
SELECT * from Icbal--Inventory balance table
SELECT * from Icinvbal--Warehouse Inventory balance table
SELECT * from icinvinitial--Inventory initial data Sheet
SELECT * FROM Seoutstock--Delivery notice
SELECT * FROM Icsale--Sales invoice Header select * from Icsaleentry--Sales Invoice Form Body SELECT * FROM vwicbill_43--Sales Invoice (view) SELECT * from SE Order--Sales order header SELECT * from Seorderentry--Sales Order table body surface header with Finterid associated SELECT * FROM Vwicbill_32--Sales order (view) SELECT * from POR FQ--Sales quotation header select * from Porfqentry--Sales quotation form Body SELECT * FROM vwicbill_35--sales quotation (view) SELECT * from Vwicbill_8--Sell out library (view)

SELECT a.*,isnull (b.ftable, ') as Ftable,isnull (E.ffieldname, ") as FieldName from T_billcoderule a
Left joins T_option E on A.fprojectid=e.fprojectid and A.fformatindex=e.fid
Left OUTER joins T_checkproject B on A.fbilltype=b.fbilltypeid and A.fprojectval=b.ffield
WHERE a.fbilltypeiid = ' Bayi ' ORDER by A.fclassindex
SELECT Fbilltypeid,fformatchar,fprojectval,fnummax from T_billcodeby
WHERE Fbilltypeid = ' Bayi ' and fformatchar= ' 1412 ' and fprojectval= ' yymm| '
UPDATE t_billcodeby SET fnummax=10 WHERE fbilltypeid = ' Bayi ' and fformatchar= ' 1410 ' and fprojectval= ' yymm| '
SELECT * FROM t_option-Document encoding format
SELECT * from T_billcodeby WHERE Fbilltypeid = ' 81 '--Sales order per issue number details
SELECT * from T_billcoderule WHERE fbilltypeid= ' 81 '--Encoding rules for sales order numbering
SELECT * from T_checkproject
SELECT * from Icpurbill--Purchase invoice Header
SELECT * from Icpurentry--Purchase invoice form Body
SELECT * from vwicbill_42--Purchase Invoice (view)
SELECT * from Poorder--Purchase order header
SELECT * from Poorderentry--Purchase order form body
SELECT * from vwicbill_26--Purchase order (view)
SELECT * from Porequest--Purchase requisition form header
SELECT * from Porequestentry--purchase requisition form the body surface head is associated with the Finterid
SELECT * from vwicbill_25--purchase requisition (view)
SELECT * from Vwicbill_1--Purchase warehousing (view)

SELECT * from T_rp_newreceivebill--Receipt form
SELECT * FROM t_rp_contact--accounts receivable and payable transactions table
SELECT * from T_rp_newcheckinfo--write-off log verification business detailed records. Frp= 1-receivable: FRP = 0-Coping
SELECT * FROM T_rp_checkdetail--verification details
SELECT * from T_rp_contactbal--current balance table frp= 1-receivable: FRP = 0-Coping
SELECT * FROM icitemmapping--Supply information Form
SELECT * FROM T_supplyentry--quote Form
-----------------Production Management---------------------------
SELECT * from ICMO--production task order
SELECT * from Ppbom--production feed sheet
SELECT * FROM Icsubcontract--Outsourcing order form Header
SELECT * from Icsubcontractentry--External order table body surface head with Finterid Association
-----------------Financial Accounting----------------------------
SELECT * from T_account-- chart of accounts
SELECT * from T_balance--account balance table
SELECT * FROM T_voucher--voucher table
SELECT * FROM T_vouchergroup--voucher word
SELECT * FROM T_voucherentry--Voucher Entry table
SELECT * FROM T_voucherexp--Voucher summary table
SELECT * from T_itempropdesc--Accounting Project schedule information Description Table
SELECT * from T_ITEMDETAILV--accounting Project use details Chart
SELECT * from T_itemdetail--accounting project usage details horizontal table
SELECT * from t_rpcontract WHERE fcontractno= ' 2014079 '--Contract receivable
SELECT * from T_rpcontractscheme WHERE fcontractid=1546--Collection plan schedule
SELECT fperiod,fyear from t_perioddate WHERE ' 2008-02-29 ' >=fstartdate and ' 2008-02-29 ' <=fenddate--year, account period
SELECT * FROM t_currency--Currency
SELECT a.faccountid,a.fnumber faccountnumber,a.fname faccountname,i.fitemclassid,--Account hanging Accounting Project
i.fnumber fitemclassnumber,i.fname fitemclassname,case ai. Fitemid WHEN-1 then 1 ELSE 0 END fbalchecked
From t_account a,t_itemclass i,t_itemdetailv ai
WHERE a.fdetailid = ai. Fdetailid and AI. Fitemclassid = I.fitemclassid and AI. Fitemid in ( -1,-2)
ORDER by A.faccountid, I.fnumber
--bos Document template information:
--Document overall attribute table
SELECT * from Icclasstype WHERE fname_chs like '% expense reimbursement sheet% '
--Document body attribute table
SELECT * from Icclasstypeentry WHERE fparentid=1000050
--Document field template Information table
SELECT * from Icclasstableinfo WHERE fclasstypeid=1000050

--Document conversion process template information:
--Document conversion process overall information table
SELECT * from Icclasslink
--the field corresponding relation table in the conversion process
SELECT * from Icclasslinkentry
--Defininion Relationship control field, close field, control field information table
SELECT * from Icclasslinkcommit

--document conversion process in the BOS Integration development tool:
--Document conversion Process name Information table
select * from Icclassworkflow
--document conversion process
select * from Icclassworkflowbill
--The connection information table between two documents in the document conversion process
select * from Icclassworkflowjoin

--approval process design:
--Approval flow Overall information table
select * from Icclassmctemplate
Span style= "FONT-SIZE:14PT; Font-family: Bold; " >--The process node of the approval flow, the transition rank, and the details table of its attributes
select * from Icclassmctableinfo

--Audit Record information table: icclassmcrecord+ Document type ID
-for example, if the expense claim ID is 1000050, then its Audit record table is ICClassMCRecord1000050
SELECT * from ICClassMCRecord1000050
--Audit Status information table: icclassmcstatus+ Document type ID
----For example, if the expense claim ID is 1000050, then its Audit record table is ICClassMCStatus1000050
SELECT * from ICClassMCStatus1000050

First level menu: T_subsystem
Level II Menu: T_dataflowsubsystem
Level three menu: T_dataflowsubfunc
Level four menu: T_dataflowdetailfunc

K3 Database Related