-- 1713 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.nomor = '001.001.1.01585'
and d.bu_kode = 1;

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
;
select * from pb_trs_purchase_order_hdr
where sid = @sid
and bu_kode = 1
;
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 = @sid;

-- 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-22', NULL, NULL, NULL, NULL, NULL, NULL, '83a00b89-36d8-48bc-af0f-908774380j4m', 'S', '81878d9c-7f24-4dff-93fa-cab87a7811bd', '1', '0', '2d8838b4-35bd-4f95-a98f-19d0f5a607c2', '000.2.00329', NULL, NULL, 'PO - KONTRAK KODE KATEGORI - OTOMATIS', '82395285.5700', '8239528.5300', '0.0000', '90881999.9400', NULL, '8910.0000', '8a40fb58-0212-11e6-b21b-c8600086eab3', '1', '43dabd03-43f8-4984-897b-3419a6792a17', '1', '0.0000', '0.0000', '5', '2019', '000.2.00329', '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-22', '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-22', '277238c8-ff51-46b5-a11c-81df6cb00001', '0', NULL, '1', '1', '95be6248-69c4-11e6-b2a2-00235a7459de', '1', '0.0000', '977e96a9-1ec2-4f2d-a1ec-424c9f8258f9', '001.2.01062', NULL, '0', NULL, NULL, NULL, NULL, NULL, '0.0000', NULL, 'f7015456-8151-4325-a593-bddc2fa01996', 'B 9207 SYK'
);

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 = @sidPO
;

-- belokin dtl nya
update pb_trs_purchase_order_dtl set purchase_order_sid = @sidPo, purchase_order_nomor=@nomorPo
where sid in (
	'17643724-627e-4fd4-bbb9-e04da699eb83',
	'5e4c2d13-809f-4d62-ab49-3a8bfb74a371',
	'b4c86c64-95f0-4189-82da-d36fb88dba4c'
);
-- belokin DO hdrnya ke po yang baru
update pb_trs_delivery_order_hdr
set purchase_order_sid = @sidPo, purchase_order_nomor=@nomorPo
where sid = @sidDo;