SQL to obtain Fulfillment Lines on the Original Orders where the Copied/Return Order is known in Oracle Cloud Fusion R13
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
No comments:
Post a Comment