|
I have a report that was made by an external company in SQL. I would like to add two additional fields to their command file. The SQL is way way over my head.
I would like the fieldS
COMPONENT_INVENTORY.DIVISION
and
COMPONENT_INVENTORY.PARENT_COMP_MAN_ID
with an inner join to
COMPONENT_MANUFACTURE.COMP_MAN_ID
where COMPONENT_INVENTORY.PARENT_COMP_MAN_ID=COMPONENT_MANUFACTURE.COMP_MAN_ID
If anyone can offer assistance that would be great.
I have attached the complete SQL for the Command file for the report in question.
Thanks in advance for any help or insight.
CJB
SELECT *
FROM
(SELECT
null header,
ccmd.donor_id,
dd.draw_start_datetime,
dv.collection_location_id
||
CASE
WHEN dv.sublocation_id IS NULL
THEN NULL
ELSE '-'
END
|| dv.sublocation_id source,
dv.donor_group_id,
dd.actual_collection_type_cd,
dd.failure_cd,
CASE
WHEN ccmd.worksite_id='LEGACY'
THEN NULL
ELSE 'CM'
END inv,
CASE
WHEN ccmd.source_unit_no IS NULL
THEN ccmd.unit_no
ELSE ccmd.source_unit_no
END unit_no,
CASE
WHEN ccmd.source_unit_no IS NULL
THEN NULL
ELSE ccmd.unit_no
END pool_unit_no,
ccmd.comp_man_id,
ccmd.component_group,
ccmd.comp_product_code,
ccmd.worksite_id,
ccmd.comp_man_status_cd,
ccmd.expiration_datetime,
ccmd.quarantine_reason_cd,
ccmd.discard_reason_cd,
ccmd.discard_container_id,
NULL ship_to_facility_id,
NULL shipment_datetime,
NULL ret_from_facility_id,
NULL completed_datetime
FROM (WITH
table_var AS
(
SELECT
cm.donor_id,
cm.draw_id,
cm.comp_man_id,
cm.component_group,
cm.unit_no,
cm.comp_product_code,
cm.worksite_id,
cm.comp_man_status_cd,
cm.expiration_datetime,
cm.quarantine_reason_cd,
cm.discard_reason_cd,
cm.discard_container_id,
NULL source_unit_no
FROM
component_manufacture cm
WHERE
cm.donor_id IS NOT NULL
UNION ALL
SELECT
cma.donor_id,
cma.draw_id,
cl.comp_id comp_man_id,
cpl.component_group,
cpl.unit_no,
cpl.comp_product_code,
cpl.worksite_id,
cpl.comp_man_status_cd,
cpl.expiration_datetime,
cpl.quarantine_reason_cd,
cpl.discard_reason_cd,
cpl.discard_container_id,
cma.unit_no source_unit_no
FROM
component_lineage cl
INNER JOIN component_manufacture cma
ON
cl.parent_comp_id=cma.comp_man_id
INNER JOIN component_manufacture cpl
ON
cl.comp_id =cpl.comp_man_id
AND cpl.donor_id IS NULL
AND cma.donor_id IS NOT NULL
)
SELECT
tv.DONOR_ID,
tv.DRAW_ID,
tv.COMP_MAN_ID,
tv.COMPONENT_GROUP,
tv.UNIT_NO,
tv.COMP_PRODUCT_CODE,
tv.WORKSITE_ID,
tv.COMP_MAN_STATUS_CD,
tv.EXPIRATION_DATETIME,
tv.QUARANTINE_REASON_CD,
tv.DISCARD_REASON_CD,
tv.DISCARD_CONTAINER_ID,
tv.SOURCE_UNIT_NO
FROM
table_var tv
UNION ALL
SELECT
du.donor_id,
du.draw_id,
NULL comp_man_id,
NULL component_group,
du.unit_no,
NULL comp_product_code,
'LEGACY' worksite_id,
du.unit_status_cd,
NULL expiration_datetime,
du.quarantine_reason_cd,
du.discard_reason_cd,
du.discard_container_id,
NULL source_unit_no
FROM
donor_unit du
WHERE
NOT EXISTS
(
SELECT
tv1.*
FROM
table_var tv1
WHERE
tv1.donor_id =du.donor_id
AND tv1.draw_id=du.draw_id
AND tv1.unit_no=du.unit_no
)) ccmd
LEFT OUTER JOIN donor_draw dd
ON ccmd.draw_id= dd.draw_id
LEFT OUTER JOIN cd_failure cf
ON dd.failure_cd=cf.failure_cd
LEFT OUTER JOIN donor_visit dv
ON dd.visit_id =dv.visit_id
WHERE dd.donor_id ={?UNL_OR_DONOR}
AND ccmd.comp_man_status_cd IN ('C', 'IP', 'Q', 'D', 'PC')
UNION ALL
SELECT
null header,
ccmdd.donor_id,
dd.draw_start_datetime,
dv.collection_location_id
||
CASE
WHEN dv.sublocation_id IS NULL
THEN NULL
ELSE '-'
END
|| dv.sublocation_id source,
dv.donor_group_id,
dd.actual_collection_type_cd,
dd.failure_cd,
'CI' inv,
CASE
WHEN ccmdd.source_unit_no IS NULL
THEN ccmdd.unit_no
ELSE ccmdd.source_unit_no
END unit_no,
CASE
WHEN ccmdd.source_unit_no IS NULL
THEN NULL
ELSE ccmdd.unit_no
END pool_unit_no,
ci.compinv_id,
ci.component_group,
ci.comp_product_code,
ci.worksite_id,
ci.comp_inv_status_cd,
ci.expiration_datetime,
ci.quarantine_reason_cd,
ci.discard_reason_cd,
ci.discard_container_id,
CASE
WHEN ci.component_group IN ('RP','LP')
THEN (
SELECT distinct last_value (rs.ship_to_facility_id) over (order by rs.shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_facility
FROM rp_shipment_carton_comp rscc
INNER JOIN rp_shipment rs
ON rscc.rp_shipment_id=rs.rp_shipment_id
WHERE rscc.compinv_id =ci.compinv_id
)
ELSE (
SELECT distinct last_value(s.ship_to_facility_id) over (order by s.shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_facility
FROM shipment_item_component sic
INNER JOIN shipment s
ON sic.shipment_id =s.shipment_id
WHERE sic.compinv_id=ci.compinv_id
)
END ship_to_facility_id,
CASE
WHEN ci.component_group IN ('RP','LP')
THEN (
SELECT distinct last_value (to_char(rs.actual_shipment_datetime, 'MM/DD/YY')) over (order by rs.actual_shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_shipment_date
FROM rp_shipment_carton_comp rscc
INNER JOIN rp_shipment rs
ON rscc.rp_shipment_id=rs.rp_shipment_id
WHERE rscc.compinv_id =ci.compinv_id
)
ELSE (
SELECT distinct last_value(to_char(s.shipment_datetime, 'MM/DD/YY')) over (order by s.shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_shipment_date
FROM shipment_item_component sic
INNER JOIN shipment s
ON sic.shipment_id =s.shipment_id
WHERE sic.compinv_id=ci.compinv_id
)
END shipment_datetime,
(
SELECT distinct last_value(sr.ret_from_facility_id) over (order by sr.completed_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_facility
FROM shipment_return_component src
INNER JOIN shipment_return sr
ON src.shipment_return_id=sr.shipment_return_id
WHERE src.compinv_id =ci.compinv_id
) ret_from_facility_id,
(
SELECT distinct last_value(to_char(sr.completed_datetime, 'MM/DD/YY')) over (order by sr.completed_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_return
FROM shipment_return_component src
INNER JOIN shipment_return sr
ON src.shipment_return_id=sr.shipment_return_id
WHERE src.compinv_id =ci.compinv_id
) completed_datetime
FROM
(SELECT DISTINCT donor_id,
unit_no,
source_unit_no
FROM (WITH
table_var AS
(
SELECT
cm.donor_id,
cm.draw_id,
cm.comp_man_id,
cm.component_group,
cm.unit_no,
cm.comp_product_code,
cm.worksite_id,
cm.comp_man_status_cd,
cm.expiration_datetime,
cm.quarantine_reason_cd,
cm.discard_reason_cd,
cm.discard_container_id,
NULL source_unit_no
FROM
component_manufacture cm
WHERE
cm.donor_id IS NOT NULL
UNION ALL
SELECT
cma.donor_id,
cma.draw_id,
cl.comp_id comp_man_id,
cpl.component_group,
cpl.unit_no,
cpl.comp_product_code,
cpl.worksite_id,
cpl.comp_man_status_cd,
cpl.expiration_datetime,
cpl.quarantine_reason_cd,
cpl.discard_reason_cd,
cpl.discard_container_id,
cma.unit_no source_unit_no
FROM
component_lineage cl
INNER JOIN component_manufacture cma
ON
cl.parent_comp_id=cma.comp_man_id
INNER JOIN component_manufacture cpl
ON
cl.comp_id =cpl.comp_man_id
AND cpl.donor_id IS NULL
AND cma.donor_id IS NOT NULL
)
SELECT
tv.DONOR_ID,
tv.DRAW_ID,
tv.COMP_MAN_ID,
tv.COMPONENT_GROUP,
tv.UNIT_NO,
tv.COMP_PRODUCT_CODE,
tv.WORKSITE_ID,
tv.COMP_MAN_STATUS_CD,
tv.EXPIRATION_DATETIME,
tv.QUARANTINE_REASON_CD,
tv.DISCARD_REASON_CD,
tv.DISCARD_CONTAINER_ID,
tv.SOURCE_UNIT_NO
FROM
table_var tv
UNION ALL
SELECT
du.donor_id,
du.draw_id,
NULL comp_man_id,
NULL component_group,
du.unit_no,
NULL comp_product_code,
'LEGACY' worksite_id,
du.unit_status_cd,
NULL expiration_datetime,
du.quarantine_reason_cd,
du.discard_reason_cd,
du.discard_container_id,
NULL source_unit_no
FROM
donor_unit du
WHERE
NOT EXISTS
(
SELECT
tv1.*
FROM
table_var tv1
WHERE
tv1.donor_id =du.donor_id
AND tv1.draw_id=du.draw_id
AND tv1.unit_no=du.unit_no
))
WHERE donor_id={?UNL_OR_DONOR}
) ccmdd
INNER JOIN donor_unit du
ON du.donor_id=ccmdd.donor_id
AND du.unit_no=(
CASE
WHEN ccmdd.source_unit_no IS NULL
THEN ccmdd.unit_no
ELSE ccmdd.source_unit_no
END)
INNER JOIN donor_draw dd
ON du.draw_id= dd.draw_id
LEFT OUTER JOIN cd_failure cf
ON dd.failure_cd=cf.failure_cd
LEFT OUTER JOIN donor_visit dv
ON dd.visit_id=dv.visit_id
LEFT OUTER JOIN component_inventory ci
ON ccmdd.unit_no=ci.unit_no
WHERE ci.compinv_id IS NOT NULL
AND ci.comp_inv_status_cd NOT IN ('C','E')
)
ORDER BY draw_start_datetime,
unit_no,
inv DESC,
component_group
|
IP Logged
