I have an automated shipping software that reaches into our database and gets customer orders and processes them for UPS tags. The problem I am having is we have to manually break out line items. What I mean iss this, if there is multiples of the same thing on the order the UPS software shows a total wieght for all simular items as if it was one item.
What I am trying to do is to pre process the Order table into individuale lines so the automated feature of my UPS shipping software works.
The table looks something like this,
Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
-----------------------
1200, AB123, Some stuff, 1, 10, 10
1200, AB324, More Stuff, 3, 12, 36
1300, XY32, Junk, 2, 40, 80
What I need to turn it into is"
Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
-----------------------
1200, AB123, Some stuff, 1, 10, 10
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1300, XY32, Junk, 1, 40, 80
1300, XY32, Junk, 1, 40, 80
it would also be nice to add a column to the table to track the total number of packages for each order number, kind of a 1 of 3, 2 of 3 type thing, but that is much easyier than the breack out
any help woulf be greatly apreciated
KellyCan you post the DDL of the table? There's got to me more to it...
Because you do you get..
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
From this?
1200, AB324, More Stuff, 1, 12, 36
Doesn't make sense, unless there are other things on the row...
Script the table and post the DDL like..
CREATE TABLE myTable99 (Col1 int, ect...
GO
And sample Data like..
INSERT INTO myTable99 (Col1, col2, ect)
SELECT sample data UNION ALL
SELECT sample data UNION ALL
SELECT sample data UNION ALL
SELECT sample data
and it'll be easier for us to help..|||There is a lot of extra columns we do not even use as this is a canned ERP package this is from , we are a simple manufacturing company and do not require many of the features that this package provides, I have posted two create statements, first with only the columns that we use, and second with every thing.
the columns we are interested in are,
CREATE TABLE [OEORDLIN_SQL] (
[ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_seq_no] [smallint] NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_to_ship] [decimal](13, 4) NULL ,
[unit_price] [decimal](13, 6) NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[unit_weight] [decimal](13, 6) NULL ,
[tot_qty_ordered] [decimal](13, 4) NULL ,
[cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
The entire table is below,
CREATE TABLE [OEORDLIN_SQL] (
[ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_seq_no] [smallint] NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pick_seq] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cus_item_no] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_to_ship] [decimal](13, 4) NULL ,
[unit_price] [decimal](13, 6) NULL ,
[discount_pct] [decimal](5, 2) NULL ,
[request_dt] [int] NOT NULL ,
[qty_bkord] [decimal](13, 4) NULL ,
[qty_return_to_stk] [decimal](13, 4) NULL ,
[bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom_ratio] [decimal](9, 5) NULL ,
[unit_cost] [decimal](13, 6) NULL ,
[unit_weight] [decimal](13, 6) NULL ,
[comm_calc_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comm_pct_or_amt] [decimal](7, 2) NULL ,
[promise_dt] [int] NOT NULL ,
[tax_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stocked_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[controlled_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[select_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tot_qty_ordered] [decimal](13, 4) NULL ,
[tot_qty_shipped] [decimal](13, 4) NULL ,
[tax_fg_1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_fg_2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_fg_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[orig_price] [decimal](13, 6) NULL ,
[copy_to_bm_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[explode_kit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[allocate_dt] [int] NULL ,
[last_post_dt] [int] NULL ,
[post_to_inv_qty] [decimal](13, 4) NULL ,
[posted_to_inv] [decimal](13, 4) NULL ,
[tot_qty_posted] [decimal](13, 4) NULL ,
[qty_allocated] [decimal](13, 4) NULL ,
[components_alloc] [decimal](13, 4) NULL ,
[bin_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cost_meth] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ser_lot_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mult_ftr_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[line_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prod_cat] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_item_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reason_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[feature_return] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_inspection] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ship_from_stk] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mult_release] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[req_ship_dt] [int] NULL ,
[qty_from_stk] [decimal](13, 4) NULL ,
[user_def_fld_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[picked_dt] [int] NULL ,
[shipped_dt] [int] NULL ,
[billed_dt] [int] NULL ,
[update_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prc_cd_orig_price] [decimal](13, 6) NULL ,
[tax_sched] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_amt] [decimal](14, 2) NULL ,
[qty_bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_no] [smallint] NOT NULL ,
[mfg_method] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[forced_demand] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[conf_pick_dt] [int] NULL ,
[item_release_no] [int] NULL ,
[bin_ser_lot_comp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[offset_used_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ecs_space] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sfc_order_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_cost] [decimal](14, 2) NULL ,
[po_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rma_line_seq_no] [smallint] NULL ,
[vend_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler_0004] [char] (71) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO|||Here is an insert script with some data from our test database, I have only included the data we use, most of the rest is zeros or nulls,
Insert Into [OEORDLIN_SQL]
(ord_type,ord_no,line_seq_no,item_no,loc,item_desc _1,item_desc_2,qty_ordered,
qty_to_ship,unit_price,uom,unit_weight,tot_qty_ord ered,cus_no,A4GLIdentity)
Values (B,00000002,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,.0000,64.420000,EA,26.000000,3.0000, SCHFNE,18301)
(B,00000003,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,5.0000,.0000,128.840000, EA,46.000000,6.0000,PYEFAR,20247)
(O,00056941,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,1.0000,1.0000,46.160000,EA,26.000000,1.0000,M ASCAL,34818)
(O,00056941,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,1.0000,1.0000,92.320000,EA,49.000000,1.0000,M ASCAL,34819)
(O,00056941,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,5.0000,5.0000,88.220000,EA,49.000000,5.000 0,MASCAL,34820)
(O,00056942,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,RAIEGA,34821)
(O,00056943,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,87.720000,EA,46.000000,1.0000 ,CORVGA,34823)
(O,00056944,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,MAR6TX,34824)
(O,00056945,1,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,3.0000,3.0000,92.320000,EA,49.000000,3.0000,C TGFAL,34825)
(O,00056945,2,FPFF44,WH,Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CTGFAL,34826)
(O,00056946,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,WYLKAL,34827)
(O,00056946,2,FPHD8TT,WH,Disenfectant 8, Tea Tree Oil,1.0000,1.0000,12.000000,EA,.000000,1.0000,WYLK AL,34828)
(O,00056947,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,ZICBFL,34829)
(O,00056948,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,43.860000, EA,23.000000,1.0000,YATRAL,348300
(O,00056949,1,FPFF44,WH,Farrier's Formula 44 lb,NULL, 1.0000,1.0000,132.750000, EA,49.000000,1.0000,BERAIL,348310
(O,00056950,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,10.0000,10.0000,87.720000,EA,46.000000,10.0 000,MODFCA,348320
(O,00056951,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,2.0000,2.0000,46.160000,EA,26.000000,2.0000,S TAAOR,348330
(O,00056952,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,3.0000,87.720000,EA,46.000000,3.0000 ,EMCPNY,348340
(O,00056952,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,2.0000,2.0000,92.320000,EA,49.000000,2.0000,E MCPNY,348350
(O,00056952,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,4.0000,4.0000,88.220000,EA,49.000000,4.000 0,EMCPNY,34836)
(O,00056953,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,BARBNY,34837)
(O,00056955,1,FPFF44,HSP, Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CASAVT,34839)
(O,00056956,1,FPFF11CS,HSP, 2 PK 11 LB FARRIERS FORMULA, NULL, 2.0000,2.0000,46.160000,EA,26.000000,2.0000,HORNMA , 34840)
(O,00056956,2,FPFF22,HSP, Farrier's Formula 22 lb,NULL,5.0000,5.0000,46.160000,EA,25.000000,5.000 0,HORNMA,34841)
(O,00056956,3,FPFF44,HSP, Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,HORNMA,34842)
Go
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment