-- 1700 sbl
set @sid='';
select @sid:=h.sid, d.nomor, h.nomor po from pb_trs_purchase_order_hdr h
inner join pb_trs_delivery_order_hdr d on h.sid = d.purchase_order_sid
inner join pb_trs_delivery_order_dtl dtld on d.sid = dtld.delivery_order_sid
where d.sid = '9daf23a0-f207-47d7-afb8-8d705bf58163';

select sid, purchase_order_sid, purchase_order_nomor, qty_sudah_terima, qty_final, supplier_kode from pb_trs_purchase_order_dtl
where purchase_order_sid = @sid
and qty_sudah_terima = 0
;
LOCK TABLES pb_trs_purchase_order_hdr WRITE;

set @nomor=0;
set @kodeBu= 1;
select @nomor :=max(nomor), max(nomor)from pb_trs_purchase_order_hdr
where bu_kode = @kodeBu
and periode_tahun = 2019
;
set @newNomor = right(@nomor, 5)+1;
set @genNomor = concat("001.001.1.1.0",@newNomor);

select  @genNomor from pb_trs_purchase_order_hdr
where sid = '751e0ff8-15b6-43d4-acaa-b8cc8a0d81ce';

-- insert hdr po baru berdasarkan po terakhir +1
insert into pb_trs_purchase_order_hdr (sid, created_date, nomor, tanggal, relasi_bu_sid, relasi_bu_kode, relasi_customer_sid, relasi_customer_kode, sellback_sid, sellback_nomor, ngs_sid, ngs_kode, bu_sid, bu_kode, is_ppn_exclude, kontrak_pembelian_sid, kontrak_pembelian_nomor, grn_nomor, grn_tanggal, catatan, nilai_rp, nilai_ppn_rp, ongkos_toling_rp, total_nilai_rp, nilai_diskon_rp, berat_kg, jalur_transaksi_sid, jalur_transaksi_kode, lokasi_gudang_sid, lokasi_gudang_kode, total_serah_rp, total_terima_rp, periode_bulan, periode_tahun, referensi_dokumen_nomor, referensi_dokumen_tanggal, freeze_approval_status_sid, freeze_approval_status, purchase_order_status_sid, purchase_order_status_kode, purchase_order_status_user_nama, purchase_order_status_date, is_non_tunai, cara_pembayaran_sid, cara_pembayaran_kode, total_nilai_pembayaran, user_add_edit_sid, user_add_edit_nama, user_add_edit_tanggal, freeze_approval_count, serah_terima_status_sid, serah_terima_status, serah_terima_count, serah_terima_status_ap_sid, serah_terima_status_ap, serah_terima_count_ap, is_closed, is_batal, is_hold, is_deleted, is_beli_lepas, is_sudah_ada_ap, relasi_supplier_sid, relasi_supplier_kode, relasi_supplier_bu_sid, relasi_supplier_bu_kode, is_non_kl, is_sudah_posting_bulanan, is_sudah_catat_order, is_tukaran, nomor_sj_supplier, tanggal_sj_supplier, kontrak_toling_sid, kontrak_toling_nomor, relasi_subcon_toling_sid, relasi_subcon_toling_kode, cara_pengiriman_barang_sid, cara_pengiriman_barang_kode, tipe_pembelian_sid, tipe_pembelian_kode, lokasi_pabrik_supplier_sid, lokasi_pabrik_supplier_kode, is_harus_pakai_do, is_ada_ppn, is_sudah_declare, is_sudah_proceed, declare_sid, print_count, print_date, ho_sid, ho_kode, proses_sid, is_ada_pph22, lama_top, dasar_dokumen_jatuh_tempo_sid, dasar_dokumen_jatuh_tempo_kode, ongkos_kawal_rp, pp2_sid, pp2_nomor, catatan_closed_po, is_sudah_penyelesaian_ap, expedisi_sid, expedisi_kode, sewa_expedisi_sid, sewa_expedisi_nomor, proceed_sid, nilai_pph_rp, dari_supplier_h_prod_nomor, truk_sid, truk_nomor)
VALUES
(
	UUID(), now(), @genNomor, '2019-05-21', NULL, NULL, NULL, NULL, NULL, NULL, '83a00b89-36d8-48bc-af0f-908774380j4m', 'S', '81878d9c-7f24-4dff-93fa-cab87a7811bd', '1', '0', 'ec089103-6706-4983-b65c-c7efe70fce0d', '000.2.00328', NULL, NULL, 'PO - KONTRAK KODE KATEGORI - OTOMATIS', '75198549.3852', '7519854.9228', '0.0000', '82943999.9400', NULL, '8100.0000', '8a40fb58-0212-11e6-b21b-c8600086eab3', '1', '43dabd03-43f8-4984-897b-3419a6792a17', '1', '0.0000', '0.0000', '5', '2019', '000.2.00328', '2019-05-18', '00100b89-36d8-48bc-af0f-908774300051', 'AV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '20922fc2-a68d-4590-b33f-e45e586f294b', 'ASTIARRAHMANIA', '2019-05-21', '1', '00100b89-36d8-48bc-af0f-908774300048', 'TR', '1', NULL, NULL, '0', '0', '0', '0', '0', '0', '0', '76b78a07-d1bc-4446-bd29-130d642c6c7f', 'HSI01', NULL, NULL, '1', '0', '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, '73af40cf-e921-4fec-a8e8-e56d269699bc', 'TB', '06ed509e-760e-11e5-ab17-c8600086eab3', '4', 'b671af11-60ac-4ab8-9deb-57c7dd66111d', 'TGR', '0', '1', '0', '0', NULL, '3', '2019-05-21', '277238c8-ff51-46b5-a11c-81df6cb00001', '0', NULL, '1', '1', '95be6248-69c4-11e6-b2a2-00235a7459de', '1', '0.0000', '3d89365e-fab3-488e-8941-74ae02d257f7', '001.2.01053', NULL, '0', NULL, NULL, NULL, NULL, NULL, '0.0000', NULL, 'fd0ef7df-2333-4d64-8f34-936d4eea0cbc', 'A 9101 ZM'
);

UNLOCK TABLES;
-- dapetin po yang baru diinsert
set @sidPO='';
set @nomorPo = '';
select @sidPO :=sid, @nomorPo :=nomor from pb_trs_purchase_order_hdr h
where nomor = @genNomor;
;

select @sidPO, @nomorPo from pb_trs_purchase_order_hdr
where sid = '751e0ff8-15b6-43d4-acaa-b8cc8a0d81ce'
;

-- belokin dtl nya
update pb_trs_purchase_order_dtl set purchase_order_sid = @sidPo, purchase_order_nomor=@nomorPo
where sid in (
	'0ff292e8-a8e5-49f0-aca2-110ff0d2d3d6',
	'69b80352-4648-4b8a-a375-ebaaf49ee12b',
	'87e4cd76-01c3-403d-ae7e-9fa7d45e5810',
	'fd8e5541-91e3-46c2-bbb8-90f93e2fb61f'
);
-- belokin DO hdrnya ke po yang baru
update pb_trs_delivery_order_hdr
set purchase_order_sid = @sidPo, purchase_order_nomor=@nomorPo
where sid = '9daf23a0-f207-47d7-afb8-8d705bf58163';

select * from pb_trs_delivery_order_hdr
where sid = '9daf23a0-f207-47d7-afb8-8d705bf58163';