Obtain Requisition Numbers created for the Drop Ship Order
SELECT dfla.source_order_number ,
dfla.source_line_number ,
dfla.fulfill_line_number ,
dfla.status_code ,
dla.line_number ,
dla.display_line_number ,
ddr.DOC_USER_KEY "Requisition Number" ,
ddr.DOC_LINE_USER_KEY "Requisition Line Number" ,
ddr.doc_ref_type ,
ddr.doc_id ,
TO_CHAR(ddr.creation_date,'dd-mon-yyyy hh24:mi:ss') cr_dt,
TO_CHAR(ddr.last_update_date,'dd-mon-yyyy hh24:mi:ss') upd_dt
FROM fusion.doo_document_references ddr ,
fusion.doo_headers_all dha ,
fusion.doo_lines_all dla ,
fusion.doo_fulfill_lines_all dfla
WHERE dha.header_id = dfla.header_id
AND dha.header_id = ddr.header_id
AND dfla.fulfill_line_id = ddr.fulfill_line_id
AND submitted_flag = 'Y'
AND dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
AND ddr.DOC_REF_TYPE = 'DROPSHIP_REQ_REFERENCE'
AND dfla.line_id = dla.line_id
ORDER BY dla.display_line_number ASC