SCM4

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