INV_PICKSLIPPICKS_BY_REQUEST

Details

Schema: FUSION
Object owner: INV
Object type: VIEW

Columns

Name
NUMBERCONFIRMEDPICKSLIPS NUMBEROPENPICKSLIPS NUMBERCONFIRMEDPICKS NUMBEROPENPICKS REQUEST_NUMBER ROWORTOTAL

Name

NUMBERCONFIRMEDPICKSLIPS

NUMBEROPENPICKSLIPS

NUMBERCONFIRMEDPICKS

NUMBEROPENPICKS

REQUEST_NUMBER

ROWORTOTAL

Query

SQL_Statement
with pkslps as( select mmtt.pick_slip_number, 0 as isconfirmed, mmtt.move_order_header_id, itrh.request_number from inv_material_txns_temp mmtt, inv_txn_request_lines itrl, inv_txn_request_headers itrh, inv_pick_slip_numbers ipsn where mmtt.move_order_line_id = itrl.line_id and itrl.header_id = itrh.header_id and itrh.move_order_type = 3 and ipsn.pick_slip_number = mmtt.pick_slip_number union all select mmt.pick_slip_number, 1 as isconfirmed, itrl.header_id move_order_header_id, itrh.request_number from inv_material_txns mmt, inv_txn_request_lines itrl, inv_txn_request_headers itrh, inv_pick_slip_numbers ipsn where mmt.move_order_line_id = itrl.line_id and itrl.header_id = itrh.header_id and itrh.move_order_type = 3 and mmt.transaction_quantity > 0 and ipsn.pick_slip_number = mmt.pick_slip_number ), pkslpgrp as ( select decode(sum(decode(isconfirmed, 0, 1, 0)), 0, 0, 1) pickslipstatus, sum(decode(isconfirmed, 1, 1, 0)) as numberconfirmedpicks, sum(decode(isconfirmed, 0, 1, 0)) as numberopenpicks, move_order_header_id, request_number FROM pkslps group by pick_slip_number, move_order_header_id, request_number) select sum(decode(pickslipstatus, 0, 1, 0)) as numberconfirmedpickslips, sum(decode(pickslipstatus, 0, 0, 1)) as numberopenpickslips, sum(numberconfirmedpicks) as numberconfirmedpicks, sum(numberopenpicks) as numberopenpicks, REQUEST_NUMBER, 'R' as rowortotal FROM ( select * from pkslpgrp) group by request_number

SQL_Statement

with pkslps as( select mmtt.pick_slip_number,

0 as isconfirmed, mmtt.move_order_header_id,

itrh.request_number

from inv_material_txns_temp mmtt, inv_txn_request_lines itrl,

inv_txn_request_headers itrh, inv_pick_slip_numbers ipsn

where mmtt.move_order_line_id = itrl.line_id

and itrl.header_id = itrh.header_id

and itrh.move_order_type = 3

and ipsn.pick_slip_number = mmtt.pick_slip_number

union all

select mmt.pick_slip_number,

1 as isconfirmed, itrl.header_id move_order_header_id,

itrh.request_number

from inv_material_txns mmt, inv_txn_request_lines itrl,

inv_txn_request_headers itrh, inv_pick_slip_numbers ipsn

where mmt.move_order_line_id = itrl.line_id

and itrl.header_id = itrh.header_id

and itrh.move_order_type = 3

and mmt.transaction_quantity > 0

and ipsn.pick_slip_number = mmt.pick_slip_number ),

pkslpgrp as (

select decode(sum(decode(isconfirmed, 0, 1, 0)), 0, 0, 1) pickslipstatus,

sum(decode(isconfirmed, 1, 1, 0)) as numberconfirmedpicks,

sum(decode(isconfirmed, 0, 1, 0)) as numberopenpicks,

move_order_header_id,

request_number

FROM pkslps

group by pick_slip_number, move_order_header_id, request_number)

select sum(decode(pickslipstatus, 0, 1, 0)) as numberconfirmedpickslips,

sum(decode(pickslipstatus, 0, 0, 1)) as numberopenpickslips,

sum(numberconfirmedpicks) as numberconfirmedpicks,

sum(numberopenpicks) as numberopenpicks,

REQUEST_NUMBER,

'R' as rowortotal

FROM

(

select * from pkslpgrp)

group by request_number