Add Items as available field in Invoice Line Item group report
I run many reports under Invoice Line Item with item no and prod cat. We have many prod cat so would like to also have prod cat group for consolidated totals.
Tim Burns - Macromatic Indust Controls Inc
shared this idea
AdminEdward M. Kwang - NETcellent System, Inc.
(CEO / Founder, NETcellent Systems, Inc.)
responded
in Elliott 8.6, we are moving toward using Report Desk to replace Export Processor. We can consider add Product Category as a group to UDR Invoice History by item Report.
-
This is to Netcellent's Developer.
Currently, UDR Invoice History By Item has a template with the following join:
SELECT * FROM CPINVHDR
left join cpinvlin on cpinvlin.inv_itm_inv_no = inv_no
left join arcusfil on arcusfil.cus_no = inv_customer_no
left join imitmfil on imitmfil.item_no = inv_itm_itm_no
left join imlocfil on imlocfil.loc_code = inv_mfg_location
[Where] cpinvlin.inv_itm_itm_no <> ' ' and inv_transit_sts <> 'Y' and inv_transit_sts <> 'R' and ((inv_type <> 'C' and inv_itm_qty_to_ship <> 0) or inv_type = 'C')
[UserWhere]We need to change this join with the followings:
1. Use CPINVLIN as the primary table and inner join to CPINVHDR.
2. Left Join IMCATFIL from ITEM_PROD_CAT, it could be argue left join from INV_ITEM_PROD_CAT. Both has its pros and cons. I think we need to play by ear.
3. Left Join IMUSRDEF.
4. Let Join ARSLMNFIL from the 1st Salesman in CPINVHDR
5. Left Join ARSHPFIL.The Report Filter should mostly based on CPINVLIN:
* INV_ITEM_ITEM_N0
* INV_ITEM_INV_DATE
* INV_ITEM_CUST_NO
* INV_ITEM_INV_NO
These are either the leading key segment in CPINVLIN except INV_ITEM_INV_DATE which is part of the key, but not the leading segment.
