Order Information - Copied and Return Orders.sql

-- SQL to Obtain ALL copied/Returned orders where original order is known
SELECT
	orig_dha.source_order_number "Original Order"
  , Copied_dha.source_order_number "Copied Order"
  , dcr.HEADER_ID
  , dcr.LINE_ID
  , dcr.FULFILL_LINE_ID
  , dcr.DOC_REF_TYPE
  , dcr.DOC_ID
  , dcr.DOC_CONTEXT_ID
  , dcr.DOC_USER_KEY
  , dcr.DOC_SUBLINE_ID
FROM
	FUSION.DOO_DOCUMENT_REFERENCES dcr
  , fusion.doo_headers_All         orig_dha
  , fusion.doo_headers_All         copied_dha
WHERE
	dcr.DOC_REF_TYPE = 'ORIGINAL_SALES_ORDER'
	AND
	(
		TO_CHAR(dcr.doc_id)    = TO_CHAR(orig_dha.source_order_number)
		OR TO_CHAR(dcr.doc_id) = TO_CHAR(orig_dha.header_id)
	)
	AND orig_dha.source_order_number in('&ENTER_ORIGINAL_ORDER')
	AND copied_dha.header_id      = DCR.header_id
	AND copied_dha.submitted_flag = 'Y'
	AND orig_dha.submitted_flag   = 'Y'
order by
	Copied_dha.source_order_number
-- SQL to obtain Fulfillment Lines on the Original Orders where original order is known
SELECT
	dfla.source_order_number
  , dfla.LINE_TYPE_CODE
  , dfla.CATEGORY_CODE
  , dfla.*
FROM
	fusion.doo_fulfill_lines_all dfla
WHERE
	dfla.fulfill_line_id IN
	(
		SELECT
			dcr.FULFILL_LINE_ID
		FROM
			FUSION.DOO_DOCUMENT_REFERENCES dcr, fusion.doo_headers_All orig_dha , fusion.doo_headers_All copied_dha
		WHERE
			dcr.DOC_REF_TYPE = 'ORIGINAL_SALES_ORDER'
			AND
			(
				TO_CHAR(dcr.doc_id)    = TO_CHAR(orig_dha.source_order_number)
				OR TO_CHAR(dcr.doc_id) = TO_CHAR(orig_dha.header_id)
			)
			AND orig_dha.source_order_number in('&ENTER_ORIGINAL_ORDER')
			AND copied_dha.header_id      = DCR.header_id
			AND copied_dha.submitted_flag = 'Y'
			AND orig_dha.submitted_flag   = 'Y'
	)
-- SQL to Obtain Original order where Copied/Returned order is known
SELECT
	orig_dha.source_order_number "Original Order"
  , Copied_dha.source_order_number "Copied Order"
  , dcr.HEADER_ID
  , dcr.LINE_ID
  , dcr.FULFILL_LINE_ID
  , dcr.DOC_REF_TYPE
  , dcr.DOC_ID
  , dcr.DOC_CONTEXT_ID
  , dcr.DOC_USER_KEY
  , dcr.DOC_SUBLINE_ID
FROM
	FUSION.DOO_DOCUMENT_REFERENCES dcr
  , fusion.doo_headers_All         orig_dha
  , fusion.doo_headers_All         copied_dha
WHERE
	dcr.DOC_REF_TYPE = 'ORIGINAL_SALES_ORDER'
	AND
	(
		TO_CHAR(dcr.doc_id)    = TO_CHAR(orig_dha.source_order_number)
		OR TO_CHAR(dcr.doc_id) = TO_CHAR(orig_dha.header_id)
	)
	AND copied_dha.source_order_number in( '&ENTER_RETURN_ORDER')
	AND copied_dha.header_id      = DCR.header_id
	AND copied_dha.submitted_flag = 'Y'
	AND orig_dha.submitted_flag   = 'Y'
ORDER BY
	Copied_dha.source_order_number
-- SQL to obtain Fulfillment Lines on the Original Orders where the Copied/Return Order is known
SELECT
	dfla.source_order_number
  , dfla.LINE_TYPE_CODE
  , dfla.CATEGORY_CODE
  , dfla.*
FROM
	fusion.doo_fulfill_lines_all dfla
WHERE
	dfla.header_id IN
	(
		SELECT
			dfla.source_order_number, dfla.LINE_TYPE_CODE , dfla.CATEGORY_CODE , dfla.*
		FROM
			FUSION.DOO_DOCUMENT_REFERENCES dcr, fusion.doo_headers_All orig_dha , fusion.doo_headers_All copied_dha , fusion.doo_fulfill_lines_all dfla
		WHERE
			dcr.DOC_REF_TYPE = 'ORIGINAL_SALES_ORDER'
			AND
			(
				TO_CHAR(dcr.doc_id)    = TO_CHAR(orig_dha.source_order_number)
				OR TO_CHAR(dcr.doc_id) = TO_CHAR(orig_dha.header_id)
			)
			AND copied_dha.source_order_number in( '&ENTER_RETURN_ORDER')
			AND copied_dha.header_id      = DCR.header_id
			AND copied_dha.submitted_flag = 'Y'
			AND orig_dha.submitted_flag   = 'Y'
			AND orig_dha.header_id        = dfla.header_id
	)
-- SQL to show receiving transactions for the Return Order
SELECT
	ra_document_code
  , SHIPMENT_HEADER_ID
  , CUSTOMER_ID
  , RECEIPT_ADVICE_NUMBER
  , RA_DOCUMENT_CODE
  , RA_DOCUMENT_NUMBER
  , RA_DOO_SOURCE_SYSTEM_ID
  , HEADER_INTERFACE_ID
  , RA_ORIG_SYSTEM_REF
FROM
	fusion.rcv_shipment_headers
WHERE
	ra_document_code = 'RMA'
	AND ra_document_number in( '&ENTER_RETURN_ORDER')
-- SQL to show Shiping transactions for the Return Order
SELECT
	SHIPMENT_LINE_ID
  , QUANTITY_RECEIVED
  , QUANTITY_DELIVERED
  , ITEM_ID
  , SHIPMENT_LINE_STATUS_CODE
  , SOURCE_DOCUMENT_CODE
  , RA_DOO_HEADER_NUMBER
  , RA_ORIG_DOO_HEADER_NUMBER
FROM
	fusion.rcv_shipment_lines
WHERE
	shipment_header_id IN
	(
		SELECT
			SHIPMENT_HEADER_ID
		FROM
			fusion.rcv_shipment_headers
		WHERE
			ra_document_code = 'RMA'
			AND ra_document_number IN( '&ENTER_RETURN_ORDER')
	)