Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Modified Fitra 2017 Des 06, Menampilkan informasi Induk RGTO dan GTO tanpa referensi
- -- Fitra 2017 Des 15, saat update qty GTO juga mengupdate qty RGTO = qty GTO
- /* Modified by Adrian, May 9, 2018, memisahkan RGTO berdasarkan:
- * 1. RGTO dengan PO dan blm GTO
- * 2. RGTO dengan PO dan sdh GTO
- * 3. RGTO tanpa PO dan blm GTO
- * 4. RGTO tanpa PO dan sdh GTO
- */
- CREATE OR REPLACE FUNCTION r_report_pemenuhan_stok(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying,
- character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pOuId ALIAS FOR $3;
- pCtgrProductId ALIAS FOR $4;
- pSubCtgrProductId ALIAS FOR $5;
- pStyleProduct ALIAS FOR $6;
- pProductCodeName ALIAS FOR $7;
- pPeriodFrom ALIAS FOR $8;
- pPeriodTo ALIAS FOR $9;
- pRgtoNo ALIAS FOR $10;
- pRgtoRemark ALIAS FOR $11;
- pPoNo ALIAS FOR $12; -- diisi string kosong karena di UI filter dihilangkan
- pPoRemark ALIAS FOR $13; -- diisi string kosong karena di UI filter dihilangkan
- pGtoNo ALIAS FOR $14;
- pGtoRemark ALIAS FOR $15;
- pGtiNo ALIAS FOR $16;
- pGtiRemark ALIAS FOR $17;
- pGtirNo ALIAS FOR $18;
- pGtirRemark ALIAS FOR $19;
- vFilterCtgrProduct character varying= '';
- vFilterSubCtgrProduct character varying= '';
- vFilterGolonganProduct character varying= '';
- vFilterProductCodeName character varying= '';
- vFilterRgtoNo character varying= '';
- vFilterRgtoRemark character varying= '';
- vFilterPoNo character varying= '';
- vFilterPoRemark character varying= '';
- vFilterPoNotExists boolean;
- vFilterGto character varying= '';
- vFilterGti character varying= '';
- vFilterGtir character varying= '';
- vFilterGtoForDataGto character varying= '';
- vFilterGtiForDataGto character varying= '';
- vFilterGtirForDataGto character varying= '';
- vPeriodFrom text;
- vPeriodYearMonth text;
- vOuBuId bigint;
- vStockOpnameActivityId bigint;
- vEmptyId bigint := -99;
- vEmptyString text := '';
- vZero bigint := 0;
- vFlgApproved text := 'R';
- vFlgRejected text := 'V';
- vFlgQtyMissing text := 'M';
- vFlgQtyRejected text := 'R';
- vFlgQtyCorrect text := 'C';
- vFlgQtyLost text := 'L';
- vDocTypeRgto bigint := 538;
- vDocTypeCorrQtyRgto bigint := 590;
- vDocTypePurchaseOrder bigint := 101;
- vDocTypeReceiveGoods bigint := 111;
- vDocTypeGoodsTransferOut bigint := 533;
- vDocTypeGoodsTransferIn bigint := 535;
- vDocTypeGoodsTransferInReceipt bigint := 536;
- vYes character varying := 'Y';
- vEmptyAmount numeric := 0;
- BEGIN
- DELETE FROM tt_pemenuhan_stok WHERE session_id = pSessionId;
- DELETE FROM tt_pemenuhan_stok_rgto WHERE session_id = pSessionId;
- SELECT (f_get_ou_bu_structure(pOuId)).ou_bu_id INTO vOuBuId;
- --A. filter terhadap ctgr product
- IF pCtgrProductId != vEmptyId THEN
- vFilterCtgrProduct = ' AND D.ctgr_product_id = '||pCtgrProductId ;
- END IF;
- --B. filter terhadap sub ctgr product
- IF pSubCtgrProductId != vEmptyId THEN
- vFilterSubCtgrProduct = ' AND D.sub_ctgr_product_id = '||pSubCtgrProductId;
- END IF;
- --C. filter terhadap golongan
- IF pStyleProduct != vEmptyString THEN
- vFilterGolonganProduct = ' AND E.style_product = '''||pStyleProduct||'''';
- END IF;
- --D. filter terhadap product code name
- IF pProductCodeName != vEmptyString THEN
- vFilterProductCodeName = ' AND (UPPER(D.product_code) LIKE UPPER('''||'%'||pProductCodeName||'%'||''') OR UPPER(D.product_name) LIKE UPPER('''||'%'||pProductCodeName||'%'||''')) ';
- END IF;
- --E. filter terhadap RGTO no dan remark
- IF pRgtoNo != vEmptyString THEN
- vFilterRgtoNo = ' AND (UPPER(B.doc_no) LIKE UPPER('''||'%'||pRgtoNo||'%'||''')) ';
- END IF;
- IF pRgtoRemark != vEmptyString THEN
- vFilterRgtoRemark = ' AND (UPPER(B.remark) LIKE UPPER('''||'%'||pRgtoRemark||'%'||''')) ';
- END IF;
- -- --F. filter terhadap PO no dan remark
- -- -- sementara dicomment karena filter PO di UI dihilangkan
- --
- -- IF pPoNo != vEmptyString THEN
- -- vFilterPoNo = ' AND (UPPER(I.doc_no) LIKE UPPER('''||'%'||pPoNo||'%'||''')) ';
- -- vFilterPoNotExists = false;
- -- END IF;
- --
- -- IF pPoRemark != vEmptyString THEN
- -- vFilterPoRemark = ' AND (UPPER(I.remark) LIKE UPPER('''||'%'||pPoRemark||'%'||''')) ';
- -- vFilterPoNotExists = false;
- -- END IF;
- --G. filter terhadap GTO no dan remark
- IF(pGtoNo != vEmptyString OR pGtoRemark != vEmptyString) THEN
- vFilterGto = ' AND EXISTS (
- SELECT 1
- FROM in_inventory BB
- WHERE B.inventory_id = BB.ref_id
- AND BB.doc_type_id = '|| vDocTypeGoodsTransferOut;
- IF(pGtoNo != vEmptyString) THEN
- vFilterGto = vFilterGto || ' AND (UPPER(BB.doc_no) LIKE UPPER('''||'%'||pGtoNo||'%'||''')) ';
- END IF;
- IF(pGtoRemark != vEmptyString) THEN
- vFilterGto = vFilterGto || ' AND (UPPER(BB.remark) LIKE UPPER('''||'%'||pGtoRemark||'%'||''')) ';
- END IF;
- vFilterGto = vFilterGto || ' ) ';
- END IF;
- --H. filter terhadap GTI no dan remark
- IF(pGtiNo != vEmptyString OR pGtiRemark != vEmptyString) THEN
- vFilterGti = ' AND EXISTS (
- SELECT 1
- FROM in_inventory BB
- JOIN in_inventory GTI ON BB.inventory_id = GTI.ref_id
- WHERE B.inventory_id = BB.ref_id
- AND BB.doc_type_id = '|| vDocTypeGoodsTransferOut ||'
- AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn;
- IF(pGtiNo != vEmptyString) THEN
- vFilterGti = vFilterGti || ' AND (UPPER(GTI.doc_no) LIKE UPPER('''||'%'||pGtiNo||'%'||''')) ';
- END IF;
- IF(pGtiRemark != vEmptyString) THEN
- vFilterGti = vFilterGti || ' AND (UPPER(GTI.remark) LIKE UPPER('''||'%'||pGtiRemark||'%'||''')) ';
- END IF;
- vFilterGti = vFilterGti || ' ) ';
- END IF;
- --I. filter terhadap GTIR no dan remark
- IF(pGtirNo != vEmptyString OR pGtirRemark != vEmptyString) THEN
- vFilterGtir = ' AND EXISTS (
- SELECT 1
- FROM in_inventory BB
- JOIN in_inventory GTI ON BB.inventory_id = GTI.ref_id
- JOIN in_inventory GTIR ON GTI.inventory_id = GTIR.ref_id
- WHERE B.inventory_id = BB.ref_id
- AND BB.doc_type_id = '|| vDocTypeGoodsTransferOut ||'
- AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn ||'
- AND GTIR.doc_type_id = '|| vDocTypeGoodsTransferInReceipt;
- IF(pGtirNo != vEmptyString) THEN
- vFilterGtir = vFilterGtir || ' AND (UPPER(GTIR.doc_no) LIKE UPPER('''||'%'||pGtirNo||'%'||''')) ';
- END IF;
- IF(pGtirRemark != vEmptyString) THEN
- vFilterGtir = vFilterGtir || ' AND (UPPER(GTIR.remark) LIKE UPPER('''||'%'||pGtirRemark||'%'||''')) ';
- END IF;
- vFilterGtir = vFilterGtir || ' ) ';
- END IF;
- RAISE NOTICE '%', vFilterGolonganProduct;
- RAISE NOTICE '%', vFilterProductCodeName;
- RAISE NOTICE '%', vFilterGto;
- RAISE NOTICE '%', pGtoNo;
- RAISE NOTICE '%', vFilterGti;
- RAISE NOTICE '%', pGtiNo;
- RAISE NOTICE '%', vFilterGtir;
- RAISE NOTICE '%', pGtirNo;
- RAISE NOTICE '%', vFilterPoNo;
- RAISE NOTICE '%', vFilterPoRemark;
- /* A. RGTO tanpa GTO */
- --1. Insert data RGTO ke tt untuk yang belum dibuat PO
- /* Modify by Henik (04-01-2018), untuk RGTO yg belum dibuat PO gr_qty nya langsung diset 0 */
- --Modified by Adrian, May 11, 2018, hanya ambil yang belum dibuat GTO
- IF( pPoNo = vEmptyString AND pPoRemark = vEmptyString ) THEN
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok(
- session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
- rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty, gr_qty, purch_price)
- SELECT $1, C.product_id, A.inventory_id, C.inventory_item_id, B.ou_to_id, B.doc_type_id,
- F.warehouse_name, G.warehouse_name, B.doc_no, B.doc_date, COALESCE(N.qty_request,C.qty_request, 0), $9 AS gr_qty, J.purch_price + J.tax_amount
- FROM in_balance_req_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id
- JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
- LEFT JOIN in_req_trf_out_po_balance_item I ON B.inventory_id = I.inventory_id AND C.inventory_item_id = I.inventory_item_id
- JOIN m_product D ON C.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg K ON E.product_id = K.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product J ON C.product_id = J.product_id
- AND J.ou_id = H.ou_bu_id
- AND $4 BETWEEN J.date_from AND J.date_to
- AND J.partner_id = K.supplier_id
- LEFT JOIN in_req_trf_out_correction_item N ON I.inventory_item_id = N.ref_item_id AND I.flg_correction = $8
- WHERE A.tenant_id = $2
- AND B.doc_type_id = $3
- AND B.doc_date BETWEEN $4 AND $5
- AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
- AND NOT EXISTS (
- SELECT 1
- FROM pu_po z
- JOIN pu_po_item Y ON Z.po_id = Y.po_id AND Y.product_id = C.product_id AND Y.ref_id = C.inventory_item_id
- JOIN pu_po_balance_item X ON Y.po_item_id = X.po_item_id
- WHERE Z.ref_id = A.inventory_id
- AND Z.ref_doc_type_id = $7
- )
- AND NOT EXISTS(
- SELECT 1
- FROM in_inventory Z
- INNER JOIN in_inventory_item Y ON Z.inventory_id = Y.inventory_id
- WHERE A.inventory_id = Z.ref_id
- AND C.inventory_item_id = Y.ref_id
- AND Z.doc_type_id = $10
- AND Z.ref_doc_type_id = B.doc_type_id
- AND Z.status_doc = $11
- )' ||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterRgtoNo ||
- vFilterRgtoRemark ||
- -- vFilterPoNo ||
- -- vFilterPoRemark ||
- vFilterGto ||
- vFilterGti ||
- vFilterGtir
- USING pSessionId, pTenantId, vDocTypeRgto, pPeriodFrom, pPeriodTo,
- pOuId, vDocTypeRgto, vYes, vEmptyAmount, vDocTypeGoodsTransferOut,
- vFlgApproved;
- END IF;
- --2. Insert data RGTO dan PO yang belum dibuat GTO
- /* Modify by Henik (04-01-2018), gr_qty diubah berdasarkan pu_po_balance_item.qty_int_rcv bukan dari in_req_trf_out_po_balance_item.qty_rcv_int
- * Alasannya, karna yg di in_req_trf_out_po_balance_item.qty_rcv_int nilainya sudah terakumulasi dari GR beberapa PO */
- --Modified by Adrian, May 11, 2018, hanya ambil yang belum dibuat GTO
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok(
- session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
- rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
- po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date,po_qty, gr_qty, purch_price)
- SELECT $1, C.product_id, A.inventory_id, C.inventory_item_id, B.ou_to_id, B.doc_type_id,
- F.warehouse_name, G.warehouse_name, B.doc_no, B.doc_date, COALESCE(O.qty_request,C.qty_request, 0) AS qty_request,
- I.po_id, J.po_item_id, I.doc_type_id, I.doc_no, I.doc_date, J.qty_int, K.qty_int_rcv, M.purch_price + M.tax_amount
- FROM in_balance_req_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id
- JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
- JOIN pu_po I ON A.inventory_id = I.ref_id AND I.ref_doc_type_id = $3
- JOIN pu_po_item J ON I.po_id = J.po_id AND C.inventory_item_id = J.ref_id
- JOIN pu_po_balance_item K ON J.po_item_id = K.po_item_id
- JOIN in_req_trf_out_po_balance_item L ON B.inventory_id = L.inventory_id AND C.inventory_item_id = L.inventory_item_id
- JOIN m_product D ON C.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg N ON E.product_id = N.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
- AND M.ou_id = H.ou_bu_id
- AND $4 BETWEEN M.date_from AND M.date_to
- AND M.partner_id = N.supplier_id
- LEFT JOIN in_req_trf_out_correction_item O ON L.inventory_item_id = O.ref_item_id AND L.flg_correction = $9
- WHERE A.tenant_id = $2
- AND B.doc_type_id = $3
- AND B.doc_date BETWEEN $4 AND $5
- AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
- AND I.doc_type_id = $7
- AND I.status_doc != $8
- AND NOT EXISTS(
- SELECT 1
- FROM in_inventory Z
- INNER JOIN in_inventory_item Y ON Z.inventory_id = Y.inventory_id
- WHERE A.inventory_id = Z.ref_id
- AND C.inventory_item_id = Y.ref_id
- AND Z.doc_type_id = $10
- AND Z.ref_doc_type_id = B.doc_type_id
- AND Z.status_doc = $11
- )'||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterRgtoNo ||
- vFilterRgtoRemark ||
- -- vFilterPoNo ||
- -- vFilterPoRemark ||
- vFilterGto ||
- vFilterGti ||
- vFilterGtir
- USING pSessionId, pTenantId, vDocTypeRgto, pPeriodFrom, pPeriodTo,
- pOuId, vDocTypePurchaseOrder, vFlgRejected, vYes, vDocTypeGoodsTransferOut,
- vFlgApproved;
- /* END OF A */
- /* B. GTO tanpa RGTO */
- --Insert Data GTO tanpa referensi jika tidak ada filter terhadap RGTO no dan remark
- IF (pRgtoNo = vEmptyString AND pRgtoRemark = vEmptyString) THEN
- -- Membuat Filter GTO
- IF(pGtoNo != vEmptyString) THEN
- vFilterGtoForDataGto = ' AND (UPPER(B.doc_no) LIKE UPPER('''||'%'||pGtoNo||'%'||''')) ';
- END IF;
- IF(pGtoRemark != vEmptyString) THEN
- vFilterGtoForDataGto = vFilterGtoForDataGto || ' AND (UPPER(B.remark) LIKE UPPER('''||'%'||pGtoRemark||'%'||''')) ';
- END IF;
- -- Membuat Filter GTI
- IF(pGtiNo != vEmptyString OR pGtiRemark != vEmptyString) THEN
- vFilterGtiForDataGto = ' AND EXISTS (
- SELECT 1
- FROM in_inventory GTI
- WHERE B.inventory_id = GTI.ref_id
- AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn;
- IF(pGtiNo != vEmptyString) THEN
- vFilterGtiForDataGto = vFilterGtiForDataGto || ' AND (UPPER(GTI.doc_no) LIKE UPPER('''||'%'||pGtiNo||'%'||''')) ';
- END IF;
- IF(pGtiRemark != vEmptyString) THEN
- vFilterGtiForDataGto = vFilterGtiForDataGto || ' AND (UPPER(GTI.remark) LIKE UPPER('''||'%'||pGtiRemark||'%'||''')) ';
- END IF;
- vFilterGtiForDataGto = vFilterGtiForDataGto || ' ) ';
- END IF;
- -- Membuat Filter GTIR
- IF(pGtirNo != vEmptyString OR pGtirRemark != vEmptyString) THEN
- vFilterGtirForDataGto = ' AND EXISTS (
- SELECT 1
- FROM in_inventory GTI
- JOIN in_inventory GTIR ON GTI.inventory_id = GTIR.ref_id
- WHERE B.inventory_id = GTI.ref_id
- AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn ||'
- AND GTIR.doc_type_id = '|| vDocTypeGoodsTransferInReceipt;
- IF(pGtirNo != vEmptyString) THEN
- vFilterGtirForDataGto = vFilterGtirForDataGto || ' AND (UPPER(GTIR.doc_no) LIKE UPPER('''||'%'||pGtirNo||'%'||''')) ';
- END IF;
- IF(pGtirRemark != vEmptyString) THEN
- vFilterGtirForDataGto = vFilterGtirForDataGto || ' AND (UPPER(GTIR.remark) LIKE UPPER('''||'%'||pGtirRemark||'%'||''')) ';
- END IF;
- vFilterGtirForDataGto = vFilterGtirForDataGto || ' ) ';
- END IF;
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok(
- session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
- rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
- po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date, gr_qty, purch_price,
- gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date, gto_rgto_date_diff,
- gto_qty)
- SELECT $1, C.product_id, $8, $8, B.ou_to_id, $8,
- F.warehouse_name, G.warehouse_name, $9, $9, 0 AS qty_request,
- $8, $8, $8, $9, $9, 0, M.purch_price + M.tax_amount,
- B.inventory_id, C.inventory_item_id, B.doc_type_id, B.doc_no, B.doc_date, 0,
- C.qty_realization
- FROM in_balance_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id
- JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
- JOIN m_product D ON C.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg N ON E.product_id = N.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
- AND M.ou_id = H.ou_bu_id
- AND $4 BETWEEN M.date_from AND M.date_to
- AND M.partner_id = N.supplier_id
- WHERE A.tenant_id = $2
- AND B.doc_type_id = $3
- AND B.doc_date BETWEEN $4 AND $5
- AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
- AND B.ref_id = $8 '||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterGtoForDataGto ||
- vFilterGtiForDataGto ||
- vFilterGtirForDataGto
- USING pSessionId, pTenantId, vDocTypeGoodsTransferOut, pPeriodFrom, pPeriodTo,
- pOuId, vYes,vEmptyId,vEmptyString;
- -- insert untuk GTI item baru dari GTO non reference
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok(
- session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
- rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
- po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date, gr_qty, purch_price,
- gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date, gto_rgto_date_diff,
- gto_qty, gti_id, gti_item_id, gti_doc_type_id, gti_doc_no, gti_doc_date, gti_qty)
- SELECT $1, C.product_id, $8, $8, B.ou_to_id, $8,
- F.warehouse_name, G.warehouse_name, $9, $9, 0 AS qty_request,
- $8, $8, $8, $9, $9, 0, M.purch_price + M.tax_amount,
- B.inventory_id, $8, B.doc_type_id, B.doc_no, B.doc_date, 0,
- C.qty_out, Z.inventory_id, C.inventory_item_id, Z.doc_type_id, Z.doc_no, Z.doc_date,
- C.qty_in
- FROM in_balance_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id
- JOIN in_inventory Z ON Z.ref_id = B.inventory_id AND Z.ref_doc_type_id = B.doc_type_id AND Z.doc_type_id = $10
- JOIN in_balance_transfer_in_item C ON Z.inventory_id = C.inventory_id
- JOIN m_product D ON C.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg N ON E.product_id = N.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
- AND M.ou_id = H.ou_bu_id
- AND $4 BETWEEN M.date_from AND M.date_to
- AND M.partner_id = N.supplier_id
- WHERE A.tenant_id = $2
- AND B.doc_type_id = $3
- AND B.doc_date BETWEEN $4 AND $5
- AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
- AND B.ref_id = $8
- AND C.ref_item_id = $8 '||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterGtoForDataGto ||
- vFilterGtiForDataGto ||
- vFilterGtirForDataGto
- USING pSessionId, pTenantId, vDocTypeGoodsTransferOut, pPeriodFrom, pPeriodTo,
- pOuId, vYes,vEmptyId,vEmptyString, vDocTypeGoodsTransferIn;
- END IF;
- /* END OF B */
- /* C. RGTO dengan GTO */
- --Added by Adrian, May 11, 2018
- --1. Insert data RGTO ke tt_rgto untuk yang belum dibuat PO dan sudah dibuat GTO
- IF( pPoNo = vEmptyString AND pPoRemark = vEmptyString ) THEN
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok_rgto(
- session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
- rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty, gr_qty, purch_price,
- gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date,
- gto_rgto_date_diff, gto_qty)
- SELECT $1, C.product_id, A.inventory_id, C.inventory_item_id, B.ou_to_id, B.doc_type_id,
- F.warehouse_name, G.warehouse_name, B.doc_no, B.doc_date, P.qty_realization, $9 AS gr_qty, J.purch_price + J.tax_amount,
- O.inventory_id, P.inventory_item_id, O.doc_type_id, O.doc_no, O.doc_date,
- (O.doc_date::date - B.doc_date::date)::numeric, P.qty_realization
- FROM in_balance_req_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id
- JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
- LEFT JOIN in_req_trf_out_po_balance_item I ON B.inventory_id = I.inventory_id AND C.inventory_item_id = I.inventory_item_id
- JOIN m_product D ON C.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg K ON E.product_id = K.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product J ON C.product_id = J.product_id
- AND J.ou_id = H.ou_bu_id
- AND $4 BETWEEN J.date_from AND J.date_to
- AND J.partner_id = K.supplier_id
- LEFT JOIN in_req_trf_out_correction_item N ON I.inventory_item_id = N.ref_item_id AND I.flg_correction = $8
- INNER JOIN in_inventory O ON A.inventory_id = O.ref_id AND O.doc_type_id = $10 AND O.ref_doc_type_id = B.doc_type_id AND O.status_doc = $11
- INNER JOIN in_inventory_item P ON O.inventory_id = P.inventory_id AND C.inventory_item_id = P.ref_id
- WHERE A.tenant_id = $2
- AND B.doc_type_id = $3
- AND B.doc_date BETWEEN $4 AND $5
- AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
- AND NOT EXISTS (
- SELECT 1
- FROM pu_po z
- JOIN pu_po_item Y ON Z.po_id = Y.po_id AND Y.product_id = C.product_id AND Y.ref_id = C.inventory_item_id
- JOIN pu_po_balance_item X ON Y.po_item_id = X.po_item_id
- WHERE Z.ref_id = A.inventory_id
- AND Z.ref_doc_type_id = $7
- )' ||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterRgtoNo ||
- vFilterRgtoRemark ||
- -- vFilterPoNo ||
- -- vFilterPoRemark ||
- vFilterGto ||
- vFilterGti ||
- vFilterGtir
- USING pSessionId, pTenantId, vDocTypeRgto, pPeriodFrom, pPeriodTo,
- pOuId, vDocTypeRgto, vYes, vEmptyAmount, vDocTypeGoodsTransferOut,
- vFlgApproved;
- END IF;
- --Added by Adrian, May 11, 2018
- --2. Insert data RGTO dan PO yang sudah dibuat GTO
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok_rgto(
- session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
- rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
- po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date,po_qty, gr_qty, purch_price,
- gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date,
- gto_rgto_date_diff, gto_qty)
- SELECT $1, C.product_id, A.inventory_id, C.inventory_item_id, B.ou_to_id, B.doc_type_id,
- F.warehouse_name, G.warehouse_name, B.doc_no, B.doc_date, Q.qty_realization AS qty_request,
- I.po_id, J.po_item_id, I.doc_type_id, I.doc_no, I.doc_date, J.qty_int, K.qty_int_rcv, M.purch_price + M.tax_amount,
- P.inventory_id, Q.inventory_item_id, P.doc_type_id, P.doc_no, P.doc_date,
- (P.doc_date::date - B.doc_date::date)::numeric, Q.qty_realization
- FROM in_balance_req_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id
- JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
- JOIN pu_po I ON A.inventory_id = I.ref_id AND I.ref_doc_type_id = $3
- JOIN pu_po_item J ON I.po_id = J.po_id AND C.inventory_item_id = J.ref_id
- JOIN pu_po_balance_item K ON J.po_item_id = K.po_item_id
- JOIN in_req_trf_out_po_balance_item L ON B.inventory_id = L.inventory_id AND C.inventory_item_id = L.inventory_item_id
- JOIN m_product D ON C.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg N ON E.product_id = N.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
- AND M.ou_id = H.ou_bu_id
- AND $4 BETWEEN M.date_from AND M.date_to
- AND M.partner_id = N.supplier_id
- LEFT JOIN in_req_trf_out_correction_item O ON L.inventory_item_id = O.ref_item_id AND L.flg_correction = $9
- INNER JOIN in_inventory P ON A.inventory_id = P.ref_id AND P.doc_type_id = $10 AND P.ref_doc_type_id = B.doc_type_id AND P.status_doc = $11
- INNER JOIN in_inventory_item Q ON P.inventory_id = Q.inventory_id AND C.inventory_item_id = Q.ref_id
- WHERE A.tenant_id = $2
- AND B.doc_type_id = $3
- AND B.doc_date BETWEEN $4 AND $5
- AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
- AND I.doc_type_id = $7
- AND I.status_doc != $8'||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterRgtoNo ||
- vFilterRgtoRemark ||
- -- vFilterPoNo ||
- -- vFilterPoRemark ||
- vFilterGto ||
- vFilterGti ||
- vFilterGtir
- USING pSessionId, pTenantId, vDocTypeRgto, pPeriodFrom, pPeriodTo,
- pOuId, vDocTypePurchaseOrder, vFlgRejected, vYes, vDocTypeGoodsTransferOut,
- vFlgApproved;
- --Added by Adrian, May 11, 2018
- -- 3. Insert dari tt_pemenuhan_stok_rgto ke tt_pemenuhan_stok
- INSERT INTO tt_pemenuhan_stok(
- session_id, product_id, purch_price, rgto_id, rgto_doc_type_id,
- rgto_item_id, rgto_ou_id, rgto_whs_from, rgto_whs_to, rgto_doc_no,
- rgto_doc_date, rgto_qty, rgto_qty_correction, po_id, po_doc_type_id,
- po_item_id, po_doc_no, po_doc_date, po_qty, gr_doc_date, gr_rgto_date_diff,
- gr_qty, gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date,
- gto_rgto_date_diff, gto_qty, gti_id, gti_item_id, gti_doc_type_id,
- gti_doc_no, gti_doc_date, gti_qty, gtir_id, gtir_doc_type_id,
- gtir_doc_no, gtir_doc_date, gtir_qty_missing, gtir_qty_rejected,
- gtir_qty_correct, gtir_qty_lost)
- SELECT session_id, product_id, purch_price, rgto_id, rgto_doc_type_id,
- rgto_item_id, rgto_ou_id, rgto_whs_from, rgto_whs_to, rgto_doc_no,
- rgto_doc_date, rgto_qty, rgto_qty_correction, po_id, po_doc_type_id,
- po_item_id, po_doc_no, po_doc_date, po_qty, gr_doc_date, gr_rgto_date_diff,
- gr_qty, gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date,
- gto_rgto_date_diff, gto_qty, gti_id, gti_item_id, gti_doc_type_id,
- gti_doc_no, gti_doc_date, gti_qty, gtir_id, gtir_doc_type_id,
- gtir_doc_no, gtir_doc_date, gtir_qty_missing, gtir_qty_rejected,
- gtir_qty_correct, gtir_qty_lost
- FROM tt_pemenuhan_stok_rgto
- WHERE session_id = pSessionId;
- /* END OF C */
- /* D. SEMUA */
- --1. update qty_correction
- UPDATE tt_pemenuhan_stok A
- SET rgto_qty_correction = C.qty_request_correction
- FROM in_inventory B, in_req_trf_out_correction_item C
- WHERE A.rgto_id = B.ref_id
- AND A.rgto_item_id = C.ref_item_id
- AND A.session_id = pSessionId
- AND B.doc_type_id = vDocTypeCorrQtyRgto
- AND B.ref_doc_type_id = A.rgto_doc_Type_id
- AND B.status_doc = vFlgApproved;
- --2. Update data GR
- WITH get_last_rg AS(
- SELECT A.po_id, MAX(C.doc_date) AS doc_date, C.ref_doc_type_id
- FROM tt_pemenuhan_stok A
- INNER JOIN pu_receive_goods C ON C.ref_id = A.po_id
- WHERE C.ref_doc_type_id = A.po_doc_Type_id
- AND A.session_id = pSessionId
- AND C.doc_type_id = vDocTypeReceiveGoods
- AND C.status_doC = vFlgApproved
- GROUP BY A.po_id,C.ref_doc_type_id
- )
- UPDATE tt_pemenuhan_stok A
- SET gr_doc_date = B.doc_date,
- gr_rgto_date_diff = ((B.doc_date)::date - A.rgto_doc_date::date)::numeric
- FROM get_last_rg B
- WHERE B.po_id = A.po_id
- AND B.ref_doc_type_id = A.po_doc_Type_id;
- --update RGTO yg kena GTO untuk item yg tidak ikut GTO (qty rgto = 0),
- UPDATE tt_pemenuhan_stok A
- SET rgto_qty = 0
- FROM in_inventory B
- WHERE A.rgto_id = B.ref_id
- AND B.ref_doc_type_id = vDocTypeRgto
- AND B.doc_type_id = vDocTypeGoodsTransferOut
- AND NOT EXISTS (select 1 FROM in_inventory_item C
- WHERE B.inventory_id = C.inventory_id AND
- A.rgto_item_id = C.ref_id)
- AND B.status_doc = vFlgApproved;
- --3.a Update data GTI
- UPDATE tt_pemenuhan_stok A
- SET gti_id = B.inventory_id,
- gti_item_id = C.inventory_item_id,
- gti_doc_type_id = B.doc_type_id,
- gti_doc_no = B.doc_no,
- gti_doc_date = B.doc_date,
- gti_qty = C.qty_realization
- FROM in_inventory B
- INNER JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
- WHERE A.gto_id = B.ref_id
- AND A.gto_item_id = C.ref_item_id
- AND A.session_id = pSessionId
- AND B.doc_type_id = vDocTypeGoodsTransferIn
- AND B.ref_doc_type_id = A.gto_doc_Type_id
- AND B.status_doc = vFlgApproved
- AND A.gto_item_id <> vEmptyId;
- --3.b insert data GTI untuk item yang dimasukan saat buat GTI
- EXECUTE '
- INSERT INTO tt_pemenuhan_stok(
- session_id, product_id, purch_price, rgto_id, rgto_doc_type_id,
- rgto_item_id, rgto_ou_id, rgto_whs_from, rgto_whs_to, rgto_doc_no,
- rgto_doc_date, rgto_qty, rgto_qty_correction,
- gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date,
- gto_rgto_date_diff, gto_qty, gti_id, gti_item_id, gti_doc_type_id,
- gti_doc_no, gti_doc_date, gti_qty)
- SELECT $1, N.product_id, J.purch_price + J.tax_amount, B.inventory_id, B.doc_type_id,
- $2, B.ou_to_id, F.warehouse_name, G.warehouse_name, B.doc_no,
- B.doc_date, $3, $3,
- L.inventory_id, $2, L.doc_type_id, L.doc_no, L.doc_date,
- (L.doc_date::date - B.doc_date::date)::numeric, N.qty_out, M.inventory_id, N.inventory_item_id, M.doc_type_id,
- M.doc_no, M.doc_date, N.qty_in
- FROM in_balance_req_transfer_out A
- JOIN in_inventory B ON A.inventory_id = B.inventory_id AND B.doc_type_id = $4
- JOIN in_inventory L ON B.inventory_id = L.ref_id AND L.doc_type_id = $5
- JOIN in_inventory M ON L.inventory_id = M.ref_id AND M.doc_type_id = $6
- JOIN in_balance_transfer_in_item N ON M.inventory_id = N.inventory_id
- JOIN m_product D ON N.product_id = D.product_id
- JOIN m_product_custom E ON D.product_id = E.product_id
- JOIN m_product_custom_for_dlg K ON E.product_id = K.product_id
- JOIN m_warehouse F ON B.warehouse_from_id = F.warehouse_id
- JOIN m_warehouse G ON B.warehouse_to_id = G.warehouse_id
- JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
- LEFT JOIN m_purch_price_product J ON D.product_id = J.product_id
- AND J.ou_id = H.ou_bu_id
- AND $7 BETWEEN J.date_from AND J.date_to
- AND J.partner_id = K.supplier_id
- WHERE A.tenant_id = $8
- AND B.doc_date BETWEEN $7 AND $9
- AND (H.ou_bu_id = $10 OR H.ou_branch_id = $10)
- AND N.ref_item_id = $2 ' ||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterGolonganProduct ||
- vFilterProductCodeName ||
- vFilterRgtoNo ||
- vFilterRgtoRemark ||
- -- vFilterPoNo ||
- -- vFilterPoRemark ||
- vFilterGto ||
- vFilterGti ||
- vFilterGtir
- USING pSessionId, vEmptyId, vZero, vDocTypeRgto, vDocTypeGoodsTransferOut, vDocTypeGoodsTransferIn, pPeriodFrom, pTenantId, pPeriodTo, pOuId;
- --7.a. Update data GTIR
- UPDATE tt_pemenuhan_stok A
- SET gtir_id = B.inventory_id,
- gtir_doc_type_id = B.doc_type_id,
- gtir_doc_no = B.doc_no,
- gtir_doc_date = B.doc_date
- FROM in_inventory B
- WHERE A.gti_id = B.ref_id
- AND A.session_id = pSessionId
- AND B.doc_type_id = vDocTypeGoodsTransferInReceipt
- AND B.ref_doc_type_id = A.gti_doc_Type_id
- AND B.status_doc = vFlgApproved
- AND EXISTS (
- SELECT 1
- FROM in_inventory_item_receipt C
- WHERE B.inventory_id = C.inventory_id
- AND A.gti_item_id = C.ref_item_id
- );
- --4.b. Update data GTIR qty_missing
- --Modified by Adrian, May 11, 2018, tambah filter ref_item_id
- UPDATE tt_pemenuhan_stok A
- SET gtir_qty_missing = qty_realization
- FROM in_inventory_item_receipt C
- WHERE A.gtir_id = C.inventory_id
- AND A.product_id = C.product_id
- AND A.session_id = pSessionId
- AND A.gti_item_id = C.ref_item_id
- AND C.reason_receipt_code = vFlgQtyMissing;
- --Modified by Adrian, May 11, 2018, tambah filter ref_item_id
- UPDATE tt_pemenuhan_stok A
- SET gtir_qty_rejected = qty_realization
- FROM in_inventory_item_receipt C
- WHERE A.gtir_id = C.inventory_id
- AND A.product_id = C.product_id
- AND A.session_id = pSessionId
- AND A.gti_item_id = C.ref_item_id
- AND C.reason_receipt_code = vFlgQtyRejected;
- --Modified by Adrian, May 11, 2018, tambah filter ref_item_id
- UPDATE tt_pemenuhan_stok A
- SET gtir_qty_correct = qty_realization
- FROM in_inventory_item_receipt C
- WHERE A.gtir_id = C.inventory_id
- AND A.product_id = C.product_id
- AND A.session_id = pSessionId
- AND A.gti_item_id = C.ref_item_id
- AND C.reason_receipt_code = vFlgQtyCorrect;
- --Modified by Adrian, May 11, 2018, tambah filter ref_item_id
- UPDATE tt_pemenuhan_stok A
- SET gtir_qty_lost = qty_realization
- FROM in_inventory_item_receipt C
- WHERE A.gtir_id = C.inventory_id
- AND A.product_id = C.product_id
- AND A.session_id = pSessionId
- AND A.gti_item_id = C.ref_item_id
- AND C.reason_receipt_code = vFlgQtyLost;
- Open pRefHeader FOR
- SELECT f_get_ou_name(vOuBuId) AS ou_bu_name, pPeriodFrom AS period_from, pPeriodTo AS period_to,
- pPeriodTo AS period_to, f_get_ou_name(pOuId) AS ou_name,
- CASE WHEN pCtgrProductId != vEmptyId THEN f_get_ctgr_product_code(pCtgrProductId)||' - '||f_get_ctgr_product_name(pCtgrProductId) ELSE 'ALL' END AS ctgr_product,
- CASE WHEN pSubCtgrProductId != vEmptyId THEN f_get_sub_ctgr_product_code(pSubCtgrProductId)||' - '||f_get_sub_ctgr_product_name(pSubCtgrProductId) ELSE 'ALL' END AS sub_ctgr_product,
- CASE WHEN pStyleProduct != vEmptyString THEN pStyleProduct ELSE ' - ' END AS style_product,
- CASE WHEN pProductCodeName != vEmptyString THEN pProductCodeName ELSE ' - ' END AS product_code_name,
- CASE WHEN pRgtoNo != vEmptyString THEN pRgtoNo ELSE ' - ' END AS rgto_no,
- CASE WHEN pRgtoRemark != vEmptyString THEN pRgtoRemark ELSE ' - ' END AS rgto_remark,
- CASE WHEN pPoNo != vEmptyString THEN pPoNo ELSE ' - ' END AS po_no,
- CASE WHEN pPoRemark != vEmptyString THEN pPoRemark ELSE ' - ' END AS po_remark,
- CASE WHEN pGtoNo != vEmptyString THEN pGtoNo ELSE ' - ' END AS gto_no,
- CASE WHEN pGtoRemark != vEmptyString THEN pGtoRemark ELSE ' - ' END AS gto_remark,
- CASE WHEN pGtiNo != vEmptyString THEN pGtiNo ELSE ' - ' END AS gti_no,
- CASE WHEN pGtiRemark != vEmptyString THEN pGtiRemark ELSE ' - ' END AS gti_remark,
- CASE WHEN pGtirNo != vEmptyString THEN pGtirNo ELSE ' - ' END AS gtir_no,
- CASE WHEN pGtirRemark != vEmptyString THEN pGtirRemark ELSE ' - ' END AS gtir_remark;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT A.product_id, B.product_code, B.product_name, C.ctgr_product_name, D.sub_ctgr_product_name,
- E.style_product, A.purch_price, f_get_ou_name(A.rgto_ou_id) AS rgto_ou_name, A.rgto_whs_from, A.rgto_whs_to,
- A.rgto_doc_no, A.rgto_doc_date, SUM(A.rgto_qty) AS rgto_qty, SUM(CASE WHEN A.rgto_qty_correction IS NOT NULL THEN A.rgto_qty_correction ELSE 0 END) AS rgto_qty_correction,
- A.po_doc_no, A.po_doc_date, SUM(A.po_qty) AS po_qty, A.gr_doc_date, A.gr_rgto_date_diff,
- SUM(CASE WHEN gr_doc_date != vEmptyString THEN A.gr_qty END) AS gr_qty,
- A.gto_doc_no, A.gto_doc_date, A.gto_rgto_date_diff, SUM(A.gto_qty) AS gto_qty, A.gti_doc_no, A.gti_doc_date, SUM(A.gti_qty) AS gti_qty, SUM(A.gto_qty - A.gti_qty) AS gto_gti_qty_diff,
- A.gtir_doc_no, A.gtir_doc_date, SUM(A.gtir_qty_missing) AS gtir_qty_missing, SUM(A.gtir_qty_rejected) AS gtir_qty_rejected, SUM(A.gtir_qty_correct) AS gtir_qty_correct, SUM(A.gtir_qty_lost) AS gtir_qty_lost,
- COALESCE(G.doc_no, vEmptyString) AS rgto_no_induk, COALESCE(G.doc_date, vEmptyString) AS rgto_date_induk
- FROM tt_pemenuhan_stok A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_ctgr_product C ON B.ctgr_product_id = C.ctgr_product_id
- INNER JOIN m_sub_ctgr_product D ON B.sub_ctgr_product_id = D.sub_ctgr_product_id
- INNER JOIN m_product_custom E ON B.product_id = E.product_id
- LEFT JOIN in_inventory F ON F.inventory_id = A.rgto_id
- LEFT JOIN in_inventory G ON G.inventory_id = F.ref_id AND G.doc_type_id = F.ref_doc_type_id AND G.doc_type_id = vDocTypeRgto
- WHERE A.session_id = pSessionId
- GROUP BY A.product_id, B.product_code, B.product_name, C.ctgr_product_name, D.sub_ctgr_product_name,
- E.style_product, A.purch_price, f_get_ou_name(A.rgto_ou_id), A.rgto_whs_from, A.rgto_whs_to,
- A.rgto_doc_no, A.rgto_doc_date,
- A.po_doc_no, A.po_doc_date, A.gr_doc_date, A.gr_rgto_date_diff,
- A.gto_doc_no, A.gto_doc_date, A.gto_rgto_date_diff, A.gti_doc_no, A.gti_doc_date,
- A.gtir_doc_no, A.gtir_doc_date,
- rgto_no_induk, rgto_date_induk
- ORDER BY A.rgto_doc_no, B.product_code;
- RETURN NEXT pRefDetail ;
- DELETE FROM tt_pemenuhan_stok WHERE session_id = pSessionId;
- DELETE FROM tt_pemenuhan_stok_rgto WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement