Code:
- Exec Sql
- Insert into GLOUTQ00 ( RqsText, CrtTime, CrtPgm, TPID )
- With r as (
- Select
- t1.OhOrdr,
- :KeyData.NumLines as NumLines,
- t3.OaLine, t3.OaProd, t3.OaPnam, t3.OaCnam, t3.OaType, t3.OaPric, t3.OaFrgh,
- t4.ImId# as ItemID,
- char( date( timestamp_format( char( t3.oaprmd ), 'YYYYMMDD' ) ), ISO ) as DateReq,
- 'A' as DateReqType,
- Case When t3.OaType = '8' then Dec( t3.OaAloc/100, 9, 2)
- when t3.OaType = '9' then Dec( t8.P2Sqmb, 9, 2)
- else Dec( (t3.OaAloc * t3.OaWide )/10000, 9, 2)
- end as SQM,
- t9.PdFnwt as finWeight,
- Dec( ( t3.OaAloc/100 ), 5, 2 ) as Length,
- case when ( t3.OaType = '8' or t3.OaType = '9') then 0
- else Dec( ( t3.OaWide / 100 ), 5, 2 ) end as Width,
- t1.OhPo# as CustRef,
- t4.Imid# as StockCutID
- From OrdrHdr t1
- Join GlEvLine00 t2 on t2.eventID = :EventID
- Join OrdrAllo t3 on t3.OaOrdr = t1.OhOrdr
- and t3.OaLine = t2.EventLine
- Join InvMast t4 on t4.ImProd = t3.OaProd
- and t4.ImRoll = t3.OaRoll
- and t4.ImFcut = t3.OaFcut
- and t4.ImGcut = t3.OaGcut
- Left Join MdSvch00 t7 on t7.ShOrdr = t1.OhOrdr
- Join IvProd00 t8 on t8.P2Prod = t3.OaProd
- Join PsProd00 t9 on t9.PdProd = t3.OaProd
- Where t1.OhOrdr = :OrderID
- )
- Select JSON_Object(
- 'msgType' value :MsgType,
- 'orderNumber' value Strip(r.OhOrdr, T),
- 'numLines' value r.NumLines,
- 'lines' value
- JSON_ArrayAgg(
- JSON_Object(
- 'lineNumber' value r.OaLine, 'productCode' value Strip(r.OaProd, T),
- 'productName' value Strip(r.OaPnam, T), 'colourName' value Strip(r.OaCnam, T),
- 'itemID' value Strip(r.ItemID, T), 'dateRequired' value r.DateReq,
- 'dateRequiredType' value r.DateReqType, 'weight' value Dec( r.SQM * r.FinWeight, 6, 2),
- 'length' value r.Length, 'width' value r.Width,
- 'customerRef' value Strip(r.CustRef, T),
- 'salesValue' value Dec( (r.SQM * r.OaPric) + r.OaFrgh, 9, 2 ),
- 'productType' value r.OaType, 'stockCutID' value Strip(r.StockCutID, T)
- absent on null
- )
- )
- absent on null
- ), Current_Timestamp, :PgmSts.Program, :ThirdPartyID
- From r
- Group by
- r.OhOrdr, r.NumLines
- With NC;
|
|