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

No comments:

Post a Comment