SELECT mi.sr_inventory_item_id, mi.plan_id, mi.sr_instance_id,
mi.organization_id,
TO_CHAR (SUM (md.using_requirement_quantity) / 3) AS rate
FROM msc.msc_demands@xxbetsytobilly md INNER JOIN msc.msc_system_items@xxbetsytobilly mi
ON mi.organization_id IN
(1704, 3574, 4507, 4902, 6721, 7324, 8074, 8109, 8196)
AND md.inventory_item_id = mi.inventory_item_id
AND md.organization_id = mi.organization_id
AND md.plan_id = mi.plan_id
AND md.sr_instance_id = mi.sr_instance_id
WHERE md.origination_type IN (1, 2, 22, 24, 3, 5, 6, 7, 9, 29, 30)
AND md.using_assembly_demand_date BETWEEN TRUNC
(ADD_MONTHS (SYSTIMESTAMP,
3
)
)
AND TRUNC
( ADD_MONTHS
(SYSTIMESTAMP,
6
)
- 1
)
GROUP BY mi.sr_inventory_item_id,
mi.plan_id,
mi.sr_instance_id,
mi.organization_id
COALESCE(LRATE.RATE,0.0) is what is stored in USAGE_RATE column and the logic is as below:
SELECT RATE=CONVERT(FLOAT,BRATE.RATE),
ORGANIZATION_ID=CONVERT(INT,BRATE.ORGANIZATION_ID),
SR_INVENTORY_ITEM_ID=CONVERT(INT,BRATE.SR_INVENTORY_ITEM_ID)
FROM OPENQUERY(BILLYVALVES,'') BRATE
INNER JOIN dbo.ProgramControlParms PCP1
ON 'BillyConnection' = PCP1.ProgramControlName AND CAST(BRATE.SR_INSTANCE_ID AS VARCHAR)=PCP1.PARM2VALUE
INNER JOIN DBO.ProgramControlParms PCP
ON 'ASCPPLAN' + CAST(BRATE.organization_id AS VARCHAR) = PCP.ProgramControlName AND CAST(BRATE.PLAN_ID AS VARCHAR) = PCP.PARM3VALUE) LRATE