SELECT order_number

,source_order_number
,source_order_id, change_version_number source_revision_number
,header_status_code
,source_line_id
source_line_id
,listagg (line_status_code
,'@') line_status_code
,sum(shipped_qty) shipped_qty
,sum(NVL(backordere_qty,0)) backordere_qty
,CASE WHEN split_from_fline>0 THEN
'Yes'
ELSE
'No'
END split_from_fline,
case when length(source_line_id)>4
then 'Y'
Else 'N' END NEW_LINE
FROM (
SELECT dha.order_number
,dha.source_order_number
,dha.source_order_id,dha.change_version_number
,DHA.HEADER_ID
-- ,dha.status_code header_status_code
,CASE WHEN dha.status_code = 'DOO_APPROVAL_PENDING' THEN dha.status_code
ELSE
CASE WHEN (SELECT COUNT(1)
FROM doo_messages_vl dmv
, doo_message_requests dmr
WHERE dmv.msg_entity_id1= dmr.req_entity_id1
AND dmr.header_id = dha.header_id
AND dmv.message_name = 'FOM_CMN_APPROVAL_REJECT_ERR'
AND dmr.REQUEST_FUNCTION = 'ORA_ORDER_APPROVALS'
)>0 THEN
'REJECTED'
WHEN (SELECT COUNT(1) FROM doo_hold_instances
WHERE 1=1
AND HOLD_RELEASE_REASON_CODE IS NULL
AND
(
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_LINES_V'
AND TRANSACTION_ENTITY_ID1 = dl.Line_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_HEADERS_V'
AND TRANSACTION_ENTITY_ID1 = dha.Header_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_FLINES_V'
AND TRANSACTION_ENTITY_ID1 = dfl.Fulfill_Line_id
)
))>0 THEN
'HOLD'
ELSE
dha.status_code
END
END header_status_code
,dl.display_line_number
,dl.source_line_id
,dl.source_line_number
,dfl.status_code line_status_code
,nvl(dfl.shipped_qty,0) shipped_qty
-- , (dfl.ordered_qty-nvl(dfl.shipped_qty,0))backordere_qty
/*,
(
SELECT dl.ordered_qty - sum (fl.shipped_qty) backordered_qty
FROM doo_fulfill_lines_all fl
START WITH fulfill_line_id = dfl.fulfill_line_id
CONNECT BY PRIOR split_from_fline_id = fulfill_line_id
GROUP BY dl.ordered_qty
) backordere_qty*/
, (
SELECT dfld.QUANTITY backordered_qty
FROM doo_fulfill_lines_all fl
,doo_fulfill_line_details dfld
WHERE 1=1
AND fl.fulfill_line_id = dfl.fulfill_line_id
AND fl.fulfill_line_id = dfld.fulfill_line_id
AND fl.split_from_fline_id IS NOT NULL
AND dfld.task_type = 'Shipment'
AND dfld.status IN ( 'BACKORDERED', 'PICKED' )
-- GROUP BY dl.ordered_qty
) backordere_qty
, (SELECT COUNT(1)
FROM doo_fulfill_lines_all dfla
WHERE dfla.header_id = dha.header_id
AND dfla.split_from_fline_id IS NOT NULL
) split_from_fline
FROM doo_headers_all dha
,doo_lines_all dl
,doo_fulfill_lines_all dfl
WHERE 1 = 1
AND dl.header_id = dha.header_id
AND dha.change_version_number =
(
SELECT max (change_version_number)
FROM doo_headers_all
WHERE order_number = dha.order_number
)
-- AND dha.status_code NOT IN ('DOO_REFERENCE','DOO_DRAFT')
AND (CASE WHEN dha.status_code = 'DOO_APPROVAL_PENDING' THEN dha.status_code
ELSE
(CASE WHEN (SELECT COUNT(1)
FROM doo_messages_vl dmv
, doo_message_requests dmr
WHERE dmv.msg_entity_id1= dmr.req_entity_id1
AND dmr.header_id = dha.header_id
AND dmv.message_name = 'FOM_CMN_APPROVAL_REJECT_ERR'
AND dmr.REQUEST_FUNCTION = 'ORA_ORDER_APPROVALS'
)>0 THEN
'REJECTED'
WHEN (SELECT COUNT(1) FROM doo_hold_instances
WHERE 1=1
AND HOLD_RELEASE_REASON_CODE IS NULL
AND
 (
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_LINES_V'
AND TRANSACTION_ENTITY_ID1 = dl.Line_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_HEADERS_V'
AND TRANSACTION_ENTITY_ID1 = dha.Header_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_FLINES_V'
AND TRANSACTION_ENTITY_ID1 = dfl.Fulfill_Line_id
)
))>0 THEN
'HOLD'
ELSE
dha.status_code
END)
END) NOT IN ('DOO_REFERENCE'--,'DOO_DRAFT'
)
AND dfl.line_id = dl.line_id
AND dfl.header_id = dl.header_id
AND dfl.fulfill_line_id = NVL( dfl.parent_fulfill_line_id,
dfl.fulfill_line_id )
--and dha.order_number='5000098'
AND dha.last_update_date > CAST (to_timestamp_tz (:p_start_date
,'YYYY-MM-
DD"T"HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC' AS date)
UNION
SELECT dha.order_number
,dha.source_order_number
,dha.source_order_id,dha.change_version_number
,DHA.HEADER_ID
-- ,dha.status_code header_status_code
,CASE WHEN dha.status_code = 'DOO_APPROVAL_PENDING' THEN dha.status_code
ELSE
CASE WHEN (SELECT COUNT(1)
FROM doo_messages_vl dmv
, doo_message_requests dmr
WHERE dmv.msg_entity_id1= dmr.req_entity_id1
AND dmr.header_id = dha.header_id
AND dmv.message_name = 'FOM_CMN_APPROVAL_REJECT_ERR'
AND dmr.REQUEST_FUNCTION = 'ORA_ORDER_APPROVALS'
)>0 THEN
'REJECTED'
WHEN (SELECT COUNT(1) FROM doo_hold_instances
WHERE 1=1
AND HOLD_RELEASE_REASON_CODE IS NULL
AND
(
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_LINES_V'
AND TRANSACTION_ENTITY_ID1 = dl.Line_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_HEADERS_V'
 AND TRANSACTION_ENTITY_ID1 = dha.Header_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_FLINES_V'
AND TRANSACTION_ENTITY_ID1 = dfl.Fulfill_Line_id
)
))>0 THEN
'HOLD'
ELSE
dha.status_code
END
END header_status_code
,dl.display_line_number
,dl.source_line_id
,dl.source_line_number
,dfl.status_code line_status_code
,nvl(dfl.shipped_qty,0) shipped_qty
-- , (dfl.ordered_qty-nvl(dfl.shipped_qty,0))backordere_qty
/* ,
NVL ((dfl.ordered_qty-nvl(dfl.shipped_qty,0)), (
SELECT dl.ordered_qty - sum (fl.shipped_qty) backordered_qty
FROM doo_fulfill_lines_all fl
START WITH fulfill_line_id = dfl.fulfill_line_id
CONNECT BY PRIOR split_from_fline_id = fulfill_line_id
GROUP BY dl.ordered_qty
)) backordere_qty*/
, (
SELECT dfld.QUANTITY backordered_qty
FROM doo_fulfill_lines_all fl
,doo_fulfill_line_details dfld
WHERE 1=1
AND fl.fulfill_line_id = dfl.fulfill_line_id
AND fl.fulfill_line_id = dfld.fulfill_line_id
AND fl.split_from_fline_id IS NOT NULL
AND dfld.task_type = 'Shipment'
AND dfld.status IN ( 'BACKORDERED', 'PICKED' )
-- GROUP BY dl.ordered_qty
) backordere_qty
, (SELECT COUNT(1)
FROM doo_fulfill_lines_all dfla
WHERE dfla.header_id = dha.header_id
AND dfla.split_from_fline_id IS NOT NULL
) split_from_fline
FROM doo_headers_all dha
,doo_lines_all dl
,doo_fulfill_lines_all dfl
WHERE 1 = 1
AND dl.header_id = dha.header_id
AND dha.change_version_number =
(
SELECT max (change_version_number)
FROM doo_headers_all
WHERE order_number = dha.order_number
)
-- AND dha.status_code NOT IN ('DOO_REFERENCE','DOO_DRAFT')
AND (CASE WHEN dha.status_code = 'DOO_APPROVAL_PENDING' THEN dha.status_code
ELSE
(CASE WHEN (SELECT COUNT(1)
 FROM doo_messages_vl dmv
, doo_message_requests dmr
WHERE dmv.msg_entity_id1= dmr.req_entity_id1
AND dmr.header_id = dha.header_id
AND dmv.message_name = 'FOM_CMN_APPROVAL_REJECT_ERR'
AND dmr.REQUEST_FUNCTION = 'ORA_ORDER_APPROVALS'
)>0 THEN
'REJECTED'
WHEN (SELECT COUNT(1) FROM doo_hold_instances
WHERE 1=1
AND HOLD_RELEASE_REASON_CODE IS NULL
AND
(
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_LINES_V'
AND TRANSACTION_ENTITY_ID1 = dl.Line_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_HEADERS_V'
AND TRANSACTION_ENTITY_ID1 = dha.Header_id
)
OR
(
TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_FLINES_V'
AND TRANSACTION_ENTITY_ID1 = dfl.Fulfill_Line_id
)
))>0 THEN
'HOLD'
ELSE
dha.status_code
END)
END) NOT IN ('DOO_REFERENCE'--,'DOO_DRAFT'
)
AND dfl.line_id = dl.line_id
AND dfl.header_id = dl.header_id
AND dfl.fulfill_line_id = NVL( dfl.parent_fulfill_line_id,
dfl.fulfill_line_id )
--and dha.order_number='5002132'
AND dfl.last_update_date >= CAST (to_timestamp_tz (:p_start_date
,'YYYY-MM-
DD"T"HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC' AS date)
) a
,doo_headers_eff_b eff
WHERE a.header_id = eff.header_id
AND context_code = 'Quote Information'
AND attribute_char1 IS NOT NULL
AND a.order_number like '5%'
GROUP BY order_number
,source_order_number
,source_order_id,source_line_id
,header_status_code,
change_version_number,
split_from_fline