Advertisement
hskartono

Untitled

Nov 30th, 2021
2,437
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.11 KB | None | 0 0
  1. SELECT * FROM (
  2.     SELECT '' AS No
  3.         ,ri.vendor_source_id            AS 'Source'
  4.         ,pc.vessel_name                 AS 'Shipment'
  5.         ,ri.container_no                AS 'Container'
  6.         ,ri.receiving_no                AS 'SPG No'
  7.         ,ri.created_date AS 'SPG Date'
  8.         ,pc.invoice_no                  AS 'Invoice No'
  9.         ,pc.po_no                       AS 'PO No'
  10.         ,pc.part_id                     AS 'Part No'
  11.         ,p.job_no                       AS 'Job No'
  12.         ,p.name                         AS 'Part Name'
  13.         ,pc.quantity                    AS 'Qty CWS'
  14.         ,ri.receiving_status_id         AS 'Status'
  15.         ,pc.gr_no                       AS 'SAP Mat Doc'
  16.         ,pc.sync_date AS 'Posting Date'
  17.         ,sy.doc_no                      AS 'Doc Header'
  18.         ,'101'                          AS 'T_Code'
  19.         ,'D104'                         AS 'Plant'
  20.         ,'D103'                         AS 'Sloc'
  21.     FROM receivingitemsap pc
  22.     INNER JOIN ReceivingItem ri ON pc.receiving_item_id = ri.id
  23.     INNER JOIN Part p ON pc.part_id = p.id
  24.     INNER JOIN SyncLog sy ON pc.id_document_no = sy.record_id
  25.     WHERE sy.doc_no LIKE '%K%'
  26.  
  27.     UNION ALL
  28.    
  29.     SELECT '' AS No,
  30.         ri.vendor_source_id
  31.         ,pc.shipment AS shipment
  32.         ,ri.container_no
  33.         ,ri.receiving_no AS spg_no
  34.         ,ri.created_date AS 'SPG Date'
  35.         ,pc.invoice
  36.         ,pc.po_no
  37.         ,p.id
  38.         ,p.job_no
  39.         ,p.name
  40.         ,pc.quantity
  41.         ,ri.receiving_status_id
  42.         ,NULL AS sap_mat_doc
  43.         ,sy.send_date AS posting_date
  44.         ,sy.doc_no AS doc_header
  45.         ,'101' AS t_code
  46.         ,'D104' AS plant
  47.         ,'DSET' AS sloc
  48.     FROM receivingplan pc
  49.     INNER JOIN ReceivingItem ri ON pc.order_no = ri.order_no
  50.     INNER JOIN Part p ON pc.part_no = p.id
  51.     INNER JOIN SyncLog sy ON ri.id = sy.record_id
  52.     WHERE sy.doc_no LIKE '%N%'
  53. ) AS tmp
  54. WHERE 1=1
  55. AND (COALESCE(@SOURCE, '') = '' AND 1 = 1 OR tmp.[SOURCE] = @SOURCE)
  56. AND (COALESCE(@Shipment, '') = '' AND 1 = 1 OR tmp.[Shipment] LIKE '%' + @Shipment +  '%')
  57. AND (COALESCE(@Container, '') = '' AND 1 = 1 OR tmp.[Container] LIKE '%' + @Container + '%')
  58. AND (COALESCE(@SPGNo, '') = '' AND 1 = 1 OR tmp.[SPG No] LIKE '%' + @SPGNo + '%')
  59. AND (COALESCE(tmp.[SPG DATE],'1975-01-01') BETWEEN COALESCE(@DateFromSPG, '1975-01-01') AND COALESCE(@DateToSPG, '9999-12-31'))
  60. AND (COALESCE(@InvoiceNo, '') = '' AND 1 = 1 OR tmp.[Invoice No] LIKE '%' + @InvoiceNo + '%')
  61. AND (COALESCE(@PoNo, '') = '' AND 1 = 1 OR tmp.[PO No] LIKE '%'+ @PoNo + '%')
  62. AND (COALESCE(@PartNo, '') = '' AND 1 = 1 OR tmp.[Part No] LIKE '%' + @PartNo + '%')
  63. AND (COALESCE(@JobNo, '') = '' AND 1 = 1 OR tmp.[Job No] LIKE '%' + @JobNo +  '%')
  64. AND (COALESCE(@PartName, '') = '' AND 1 = 1 OR tmp.[Part Name] LIKE '%' + COALESCE(@PartName, tmp.[Part Name]) + '%')
  65. AND  (tmp.[Qty CWS] BETWEEN COALESCE(@QtyCWSFrom, tmp.[Qty CWS]) AND COALESCE(@QtyCWSTo, tmp.[Qty CWS]))
  66. AND (COALESCE(@STATUS, '') = '' AND 1 = 1 OR tmp.[STATUS] = @STATUS)
  67. AND (COALESCE(@StatusMatDoc, '') = '' AND 1 = 1 OR tmp.[SAP Mat Doc] LIKE '%' + @StatusMatDoc +  '%')
  68. AND (COALESCE(tmp.[Posting DATE],'1975-01-01') BETWEEN COALESCE(@DateFromPosting, '1975-01-01') + ' 00:00' AND COALESCE(@DateToPosting, '9999-12-31') + ' 23:59')
  69. AND (COALESCE(@DocHeader, '') = '' AND 1 = 1 OR tmp.[Doc Header] LIKE '%' + @DocHeader + '%')
  70. AND (COALESCE(@T_Code, '') = '' AND 1 = 1 OR tmp.[T_Code] = @T_Code)
  71. AND (COALESCE(@Plant, '') = '' AND 1 = 1 OR tmp.[Plant] = @Plant)
  72. AND (COALESCE(@Sloc, '') = '' AND 1 = 1 OR tmp.[Sloc] = @Sloc)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement