abirama62

get_data_progress_so_mf49

Aug 25th, 2021
1,757
117 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Cek progress SO
  2.  
  3. -- init table temp
  4. CREATE TABLE IF NOT EXISTS temp_sl_so_progress_data_20210825(
  5.     so_id bigint NOT NULL,
  6.     doc_no character varying(30),
  7.     doc_date character varying(8),
  8.     ou_id bigint,
  9.     partner_id bigint,
  10.     so_item_id bigint,
  11.     product_id bigint
  12. )WITH (
  13.   OIDS=FALSE
  14. );
  15.  
  16. -- table unutk view
  17. CREATE TABLE IF NOT EXISTS temp_sl_so_progress_show_data_20210825(
  18.     so_id bigint NOT NULL,
  19.     doc_no character varying(30),
  20.     doc_date character varying(8),
  21.     ou_id bigint,
  22.     partner_id bigint,
  23.     so_item_id bigint,
  24.     product_id bigint,
  25.     po_id bigint DEFAULT -99,
  26.     po_doc_no character varying(30) DEFAULT '',
  27.     po_doc_date character varying(8) DEFAULT '',
  28.     gr_id bigint DEFAULT -99,
  29.     gr_doc_no character varying(30) DEFAULT '',
  30.     gr_doc_date character varying(8) DEFAULT '',
  31.     do_id bigint DEFAULT -99,
  32.     do_doc_no character varying(30) DEFAULT '',
  33.     do_doc_date character varying(8) DEFAULT '',
  34.     invoice_id bigint DEFAULT -99,
  35.     invoice_doc_no character varying(30) DEFAULT '',
  36.     invoice_doc_date character varying(8) DEFAULT ''
  37. )WITH (
  38.   OIDS=FALSE
  39. );
  40.  
  41. -- insert data ke temp table
  42. INSERT INTO temp_sl_so_progress_data_20210825(so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id)
  43. SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, B.so_item_id, B.product_id
  44. FROM sl_so A
  45. INNER JOIN sl_so_item B ON A.so_id = B.so_id
  46. WHERE B.flg_tax_amount <> 'Y' AND
  47. A.doc_no IN ('MEL-SO21.08.0020',
  48. 'MEL-SO21.08.0055',
  49. 'MEL-SO21.08.0056',
  50. 'MEL-SO21.08.0076',
  51. 'MEL-SO21.08.0085',
  52. 'MEL-SO21.08.0100',
  53. 'MEL-SO21.08.0105',
  54. 'MEL-SO21.08.0128',
  55. 'MEL-SO21.08.0132',
  56. 'MEL-SO21.08.0142',
  57. 'MEL-SO21.08.0194',
  58. 'MEL-SO21.08.0240',
  59. 'MEL-SO21.08.0245',
  60. 'MEL-SO21.08.0264',
  61. 'MEL-SO21.08.0266',
  62. 'MEL-SO21.08.0288',
  63. 'MEL-SO21.08.0422',
  64. 'MEL-SO21.08.0424',
  65. 'MEL-SO21.08.0435',
  66. 'MEL-SO21.08.0440',
  67. 'MEL-SO21.08.0444',
  68. 'MEL-SO21.08.0453',
  69. 'MEL-SO21.08.0472',
  70. 'MEL-SO21.08.0473',
  71. 'MEL-SO21.08.0482',
  72. 'MEL-SO21.08.0491',
  73. 'MEL-SO21.08.0527',
  74. 'MEL-SO21.08.0557',
  75. 'MEL-SO21.08.0579',
  76. 'MEL-SO21.08.0626',
  77. 'MEL-SO21.08.0683',
  78. 'MEL-SO21.08.0719',
  79. 'MEL-SO21.08.0727',
  80. 'MEL-SO21.08.0728',
  81. 'MEL-SO21.08.0731',
  82. 'MEL-SO21.08.0740',
  83. 'MEL-SO21.08.0741',
  84. 'MEL-SO21.08.0755',
  85. 'MEL-SO21.08.0756',
  86. 'MEL-SO21.08.0782');
  87.  
  88.  
  89. -- get data progress_so
  90. -- data SO yang belum diapa2in
  91. INSERT INTO temp_sl_so_progress_show_data_20210825(
  92.     so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id
  93. )
  94. SELECT A.*
  95. FROM temp_sl_so_progress_data_20210825 A
  96. WHERE NOT EXISTS (select 1 FROM pu_po_item B where B.ref_id=A.so_item_id) AND
  97.     NOT EXISTS (select 1 FROM sl_do_item C where C.ref_id=A.so_item_id);
  98.        
  99.  
  100. -- data so po gr
  101. WITH data_so_po AS (
  102.     SELECT row_number() over (partition by A.doc_no ORDER BY A.doc_no ASC) as line_no,
  103.     A.so_id, A.doc_no, A.doc_date, A.partner_id, A.ou_id, A.so_item_id, A.product_id,
  104.     B.po_id, B.doc_no AS po_no, B.doc_date AS po_date
  105.     FROM temp_sl_so_progress_data_20210825 A
  106.     INNER JOIN pu_po B ON A.so_id = B.ref_id AND B.ref_doc_type_id = 301
  107.     INNER JOIN pu_po_item C ON B.po_id = C.po_id AND C.ref_id = A.so_item_id
  108. )
  109. INSERT INTO temp_sl_so_progress_show_data_20210825(
  110.     so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id,
  111.     po_id, po_doc_no, po_doc_date,
  112.     gr_id, gr_doc_no, gr_doc_date
  113. )
  114. SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, A.so_item_id, A.product_id,
  115.     A.po_id, A.po_no, A.po_date,
  116.     B.receive_goods_id, B.doc_no, B.doc_date
  117. FROM data_so_po A
  118. INNER JOIN pu_receive_goods B ON A.po_id = B.ref_id
  119. WHERE B.ref_doc_type_id = 101; --> docType PO
  120.  
  121. -- data so -> do
  122. UPDATE temp_sl_so_progress_show_data_20210825 A
  123.     SET do_id = C.do_id,
  124.     do_doc_no = C.doc_no,
  125.     do_doc_date = C.doc_date
  126. FROM sl_do C
  127. INNER JOIN sl_do_item D ON C.do_id = D.do_id
  128. WHERE A.so_id = C.ref_id AND C.ref_doc_type_id = 301
  129. AND D.ref_id = A.so_item_id;
  130.  
  131. -- data so -> do -> si
  132. UPDATE temp_sl_so_progress_show_data_20210825 A
  133.     SET invoice_id = B.invoice_id, invoice_doc_no = B.doc_no, invoice_doc_date = B.doc_date
  134. FROM sl_invoice B
  135. INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id AND C.ref_doc_type_id = 311
  136. WHERE B.ref_doc_type_id = 301
  137. AND A.do_id = C.ref_id
  138. AND A.so_id = B.ref_id;
  139.  
  140. -- Get data final
  141.     SELECT doc_no AS so_no, doc_date AS so_no, f_get_ou_code(ou_id) AS ou_code,
  142.         f_get_partner_name(partner_id) AS customer, f_get_product_code(product_id), f_get_product_name(product_id),
  143.         po_doc_no, po_doc_date, gr_doc_no, gr_doc_date, do_doc_no, do_doc_date, invoice_doc_no AS sales_invoice_no,
  144.         invoice_doc_date AS sales_invoice_date
  145.     -- SELECT *
  146.     FROM temp_sl_so_progress_show_data_20210825 ORDER BY so_id;
RAW Paste Data