Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM (
- SELECT '' AS No
- ,ri.vendor_source_id AS 'Source'
- ,pc.vessel_name AS 'Shipment'
- ,ri.container_no AS 'Container'
- ,ri.receiving_no AS 'SPG No'
- ,ri.created_date AS 'SPG Date'
- ,pc.invoice_no AS 'Invoice No'
- ,pc.po_no AS 'PO No'
- ,pc.part_id AS 'Part No'
- ,p.job_no AS 'Job No'
- ,p.name AS 'Part Name'
- ,pc.quantity AS 'Qty CWS'
- ,ri.receiving_status_id AS 'Status'
- ,pc.gr_no AS 'SAP Mat Doc'
- ,pc.sync_date AS 'Posting Date'
- ,sy.doc_no AS 'Doc Header'
- ,'101' AS 'T_Code'
- ,'D104' AS 'Plant'
- ,'D103' AS 'Sloc'
- FROM receivingitemsap pc
- INNER JOIN ReceivingItem ri ON pc.receiving_item_id = ri.id
- INNER JOIN Part p ON pc.part_id = p.id
- INNER JOIN SyncLog sy ON pc.id_document_no = sy.record_id
- WHERE sy.doc_no LIKE '%K%'
- UNION ALL
- SELECT '' AS No,
- ri.vendor_source_id
- ,pc.shipment AS shipment
- ,ri.container_no
- ,ri.receiving_no AS spg_no
- ,ri.created_date AS 'SPG Date'
- ,pc.invoice
- ,pc.po_no
- ,p.id
- ,p.job_no
- ,p.name
- ,pc.quantity
- ,ri.receiving_status_id
- ,NULL AS sap_mat_doc
- ,sy.send_date AS posting_date
- ,sy.doc_no AS doc_header
- ,'101' AS t_code
- ,'D104' AS plant
- ,'DSET' AS sloc
- FROM receivingplan pc
- INNER JOIN ReceivingItem ri ON pc.order_no = ri.order_no
- INNER JOIN Part p ON pc.part_no = p.id
- INNER JOIN SyncLog sy ON ri.id = sy.record_id
- WHERE sy.doc_no LIKE '%N%'
- ) AS tmp
- WHERE 1=1
- AND (COALESCE(@SOURCE, '') = '' AND 1 = 1 OR tmp.[SOURCE] = @SOURCE)
- AND (COALESCE(@Shipment, '') = '' AND 1 = 1 OR tmp.[Shipment] LIKE '%' + @Shipment + '%')
- AND (COALESCE(@Container, '') = '' AND 1 = 1 OR tmp.[Container] LIKE '%' + @Container + '%')
- AND (COALESCE(@SPGNo, '') = '' AND 1 = 1 OR tmp.[SPG No] LIKE '%' + @SPGNo + '%')
- AND (COALESCE(tmp.[SPG DATE],'1975-01-01') BETWEEN COALESCE(@DateFromSPG, '1975-01-01') AND COALESCE(@DateToSPG, '9999-12-31'))
- AND (COALESCE(@InvoiceNo, '') = '' AND 1 = 1 OR tmp.[Invoice No] LIKE '%' + @InvoiceNo + '%')
- AND (COALESCE(@PoNo, '') = '' AND 1 = 1 OR tmp.[PO No] LIKE '%'+ @PoNo + '%')
- AND (COALESCE(@PartNo, '') = '' AND 1 = 1 OR tmp.[Part No] LIKE '%' + @PartNo + '%')
- AND (COALESCE(@JobNo, '') = '' AND 1 = 1 OR tmp.[Job No] LIKE '%' + @JobNo + '%')
- AND (COALESCE(@PartName, '') = '' AND 1 = 1 OR tmp.[Part Name] LIKE '%' + COALESCE(@PartName, tmp.[Part Name]) + '%')
- AND (tmp.[Qty CWS] BETWEEN COALESCE(@QtyCWSFrom, tmp.[Qty CWS]) AND COALESCE(@QtyCWSTo, tmp.[Qty CWS]))
- AND (COALESCE(@STATUS, '') = '' AND 1 = 1 OR tmp.[STATUS] = @STATUS)
- AND (COALESCE(@StatusMatDoc, '') = '' AND 1 = 1 OR tmp.[SAP Mat Doc] LIKE '%' + @StatusMatDoc + '%')
- AND (COALESCE(tmp.[Posting DATE],'1975-01-01') BETWEEN COALESCE(@DateFromPosting, '1975-01-01') + ' 00:00' AND COALESCE(@DateToPosting, '9999-12-31') + ' 23:59')
- AND (COALESCE(@DocHeader, '') = '' AND 1 = 1 OR tmp.[Doc Header] LIKE '%' + @DocHeader + '%')
- AND (COALESCE(@T_Code, '') = '' AND 1 = 1 OR tmp.[T_Code] = @T_Code)
- AND (COALESCE(@Plant, '') = '' AND 1 = 1 OR tmp.[Plant] = @Plant)
- AND (COALESCE(@Sloc, '') = '' AND 1 = 1 OR tmp.[Sloc] = @Sloc)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement