Sp_PurchasePrice (procedure)

• Type: PROCEDURE
• Deterministic: NO
• SQL data access: CONTAINS SQL

Parameters

#	Mode	Name	Type
1	IN	tStartDate	datetime
2	IN	tEndDate	datetime
3	IN	sLoginId	varchar(100)
4	IN	sBrId	varchar(100)
5	IN	sSuId	varchar(100)
6	OUT	sReturn	varchar(1000)
7	OUT	sCode	int

Body

Body is not pre-cached. To inspect: mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE PROCEDURESpPurchasePrice'._

Narrative

Business context: 采购管理 → 采购分析报表 → 采购主材比例浮动报表 (form 101251240115016204641682560) — paper-material price trend pivot. Returns one row per (sMaterialsId, sParentId) with one dynamically-added column per purchase-order date in the window, holding that day's dMaterialsPrice. Used to watch paper-price fluctuations.

What it does: Builds temp T of viw_purpurchaseorder rows joined to elematerials/sismaterialsclassify, filtered to bCheck=1 AND sMaterialsType='paper' AND tCreateDate ∈ (tStartDate, tEndDate). Distincts (sMaterialsId, sParentId) into temp DT. Then opens nested cursors: outer over materials, inner over the per-date price rows; for each date not yet seen, dynamic ALTER TABLE DT ADD COLUMN 日期MMDD numeric(18,6), then UPDATE DT SET 日期MMDD = price WHERE …. Finally SELECT * FROM DT returns the wide pivot.

Invocation: Bound as the data-source for form 101251240115016204641682560 (采购主材比例浮动报表) under 采购管理 → 采购分析报表 via gdsconfigformmaster.sSqlStr. Loaded when the user opens the report. Install script ships at script/标版/30100101/Sp_PurchasePrice.sql.

Note: dynamic-column pivoting in MySQL is fragile; the proc fails silently if DT is concurrently used or if a date column already exists with a different type. Charting siblings: Sp_PurchasePriceChar, Sp_PurchasePriceChart.