abirama62

r_monitoring_gto

Nov 17th, 2020
916
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION r_monitoring_goods_transfer_out(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader              REFCURSOR := 'refHeader';
  6.     pRefDetail              REFCURSOR := 'refDetail';
  7.    
  8.     pSessionId              ALIAS FOR $1;
  9.     pTenantId                   ALIAS FOR $2;
  10.     pUserId                     ALIAS FOR $3;
  11.     pRoleId                     ALIAS FOR $4;
  12.     pDatetime                   ALIAS FOR $5;
  13.     pOuId                           ALIAS FOR $6;
  14.     pWarehouseFromId    ALIAS FOR $7;
  15.     pDateFrom                   ALIAS FOR $8;
  16.     pDateTo                     ALIAS FOR $9;
  17.     pWarehouseList      ALIAS FOR $10;
  18.  
  19.     vFlagLost       character varying(1);
  20.     vFlagMissing    character varying(1);
  21.     vFlagRejected   character varying(1);
  22.     vFlagCorrection character varying(1);
  23.  
  24.     vEmptyValue                     character varying := '';
  25.     vEmptyStrip         character varying := '-';
  26.     vAll            character varying := 'ALL';
  27.  
  28.     vFilterWarehouseTo      character varying := '';
  29.     vFilterWarehouseFrom        character varying := '';
  30.     vFilterOu       character varying := '';
  31.     vWarehouseName      character varying := '';
  32.     vOuName         character varying := '';
  33.     vOuCode         character varying := '';
  34.  
  35.     vDocTypeIdtransferIn    bigint := 535;
  36.     vDocTypeIdtransferOut   bigint := 533;
  37.     vDocTypeIdGTIR                  bigint := 536;
  38.  
  39. BEGIN
  40.  
  41.     vFlagLost := 'L';
  42.     vFlagMissing := 'M';
  43.     vFlagRejected := 'R';
  44.     vFlagCorrection := 'C';
  45.  
  46.     vWarehouseName := vAll;
  47.     vOuName := vAll;
  48.     vOuCode := vEmptyStrip;
  49.  
  50.     IF (pOuId <> -99) THEN
  51.         vFilterOu := ' AND A.ou_from_id = '|| pOuId ;
  52.         vOuName := f_get_ou_code(pOuId);
  53.         vOuCode := f_get_ou_name(pOuId);
  54.     END IF;
  55.  
  56.     IF pWarehouseFromId <> -99 THEN
  57.         vFilterWarehouseFrom := 'AND A.warehouse_from_id = '|| pWarehouseFromId;
  58.         vWarehouseName := f_get_warehouse_name(pWarehouseFromId);
  59.     END IF;
  60.  
  61.     IF pWarehouseList <> vEmptyValue THEN
  62.         vFilterWarehouseTo := 'AND A.warehouse_to_id '|| pWarehouseList;
  63.     END IF;
  64.  
  65.  
  66.     DELETE FROM tt_monitoring_gto WHERE session_id = pSessionId;
  67.  
  68.     -- 1. insert data rgto, gto, pengiriman, finalisasi pengiriman, gti ke temp table
  69.     EXECUTE '
  70.         INSERT INTO tt_monitoring_gto(
  71.             session_id, tenant_id, ou_from, warehouse_from,
  72.             ou_to, warehouse_to,
  73.             rgto_id, rgto_date, rgto_no,
  74.             gto_id, gto_date, gto_no,   pengiriman_date, pengiriman_no,
  75.             finalisasi_pengiriman_date, finalisasi_pengiriman_no,
  76.             gti_id, gti_date, gti_no,
  77.             product_id, product_code, product_name, base_uom,
  78.             rgto_qty, gto_qty, gti_qty, diff_gto_gti_qty
  79.         )
  80.         SELECT $1, $2, f_get_ou_name(A.ou_from_id), f_get_warehouse_name(A.warehouse_from_id),
  81.         f_get_ou_name(A.ou_to_id), f_get_warehouse_name(A.warehouse_to_id),
  82.         COALESCE(D.inventory_id, -99), COALESCE(D.doc_date, $7), COALESCE(D.doc_no, $7),
  83.         A.inventory_id, A.doc_date, A.doc_no, COALESCE(F.doc_date, $7), COALESCE(F.doc_no, $7),
  84.         COALESCE(H.doc_date, $7), COALESCE(H.doc_no, $7),
  85.         COALESCE(C.inventory_id, -99), COALESCE(C.doc_date, $7), COALESCE(C.doc_no, $7),
  86.         X.product_id, X.product_code, X.product_name, f_get_uom_name(B.base_uom_id),
  87.         B.qty_request, B.qty_realization, COALESCE(Y.qty_realization, 0), B.qty_realization - COALESCE(Y.qty_realization, 0)
  88.         FROM in_inventory A -- ini gto
  89.         INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
  90.         INNER JOIN in_pengiriman_item E ON A.inventory_id = E.ref_id AND A.doc_type_id = E.ref_doc_type_id
  91.         INNER JOIN in_pengiriman F ON E.pengiriman_id = F.pengiriman_id
  92.         INNER JOIN m_product X ON B.product_id = X.product_id
  93.         LEFT JOIN in_inventory C ON A.inventory_id = C.ref_id AND A.doc_type_id = C.ref_doc_type_id AND C.doc_type_id = $8 -- ini gti
  94.         LEFT JOIN in_inventory_item Y ON Y.inventory_id = C.inventory_id AND B.product_id = Y.product_id -- item gti
  95.         LEFT JOIN in_inventory D ON D.inventory_id = A.ref_id AND D.doc_type_id = A.ref_doc_type_id -- ini rgto
  96.         LEFT JOIN in_finalisasi_pengiriman_item G ON A.inventory_id = G.ref_id AND A.doc_type_id = G.ref_doc_type_id
  97.         LEFT JOIN in_finalisasi_pengiriman H ON G.finalisasi_pengiriman_id = H.finalisasi_pengiriman_id
  98.         INNER JOIN t_user_role V ON V.user_id = $10 AND V.role_id = $11
  99.         INNER JOIN t_policy_ou W ON A.ou_from_id = W.ou_id AND V.policy_id = W.policy_id
  100.         INNER JOIN m_policy_warehouse Z ON A.warehouse_to_id = Z.warehouse_id AND V.user_role_id = Z.user_role_id
  101.         WHERE A.tenant_id = $2
  102.             '|| vFilterOu ||'
  103.             '|| vFilterWarehouseFrom ||'
  104.             AND A.doc_type_id = $9
  105.             AND A.doc_date BETWEEN $4 AND $5
  106.             '|| vFilterWarehouseTo ||'
  107.     'USING pSessionId, pTenantId, pOuId, pDateFrom, pDateTo, pWarehouseFromId, vEmptyStrip,
  108.             vDocTypeIdtransferIn, vDocTypeIdtransferOut, pUserId, pRoleId;
  109.  
  110.   -- 2. update data gtir ke temp table
  111.     UPDATE tt_monitoring_gto A
  112.         SET gtir_date = COALESCE(B.doc_date, vEmptyStrip), gtir_no = COALESCE(B.doc_no, vEmptyStrip),
  113.         missing_qty = CASE WHEN C.reason_receipt_code = vFlagMissing THEN
  114.                 CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
  115.                 ELSE 0 END,
  116.         rejected_qty = CASE WHEN C.reason_receipt_code = vFlagRejected THEN
  117.                 CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
  118.                 ELSE 0 END,
  119.         correction_qty = CASE WHEN C.reason_receipt_code = vFlagCorrection THEN
  120.                 CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
  121.                 ELSE 0 END,
  122.         lost_qty = CASE WHEN C.reason_receipt_code = vFlagLost THEN
  123.                 CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
  124.                 ELSE 0 END
  125.     FROM in_inventory B
  126.     INNER JOIN in_inventory_item_receipt C ON B.inventory_id = C.inventory_id
  127.     WHERE B.ref_id = A.gti_id
  128.         AND B.ref_doc_type_id = vDocTypeIdtransferIn
  129.         AND C.product_id = A.product_id
  130.         AND C.qty_realization <> 0;
  131.  
  132.   -- 3. update data hppa
  133.     WITH product_cogs AS (
  134.             SELECT D.tenant_id, D.ou_id, MAX(D.date_year_month) AS year_month_date, D.product_id, D.avg_price
  135.             FROM in_summary_monthly_cogs D
  136.             INNER JOIN m_ou_structure E ON E.ou_bu_id = D.ou_id
  137.             GROUP BY D.tenant_id, D.ou_id, D.product_id, D.avg_price)
  138.     UPDATE tt_monitoring_gto A
  139.         SET hppa_price = COALESCE(B.avg_price, 0)
  140.     FROM product_cogs B
  141.     INNER JOIN m_ou_structure C ON C.ou_bu_id = B.ou_id
  142.     WHERE A.session_id = pSessionId
  143.         AND B.tenant_id = A.tenant_id
  144.         AND B.product_id = A.product_id
  145.         AND B.year_month_date <= SUBSTRING(A.gto_date, 1, 6);
  146.  
  147.     Open pRefHeader FOR
  148.         SELECT f_get_user_fullname(pUserId) username, vOuCode AS ou_code, vOuName AS ou_name, pDatetime datetime,
  149.             pDateFrom date_from, pDateTo date_to, f_get_warehouse_code(pWarehouseFromId) warehouse_code, vWarehouseName AS warehouse_name;
  150.     RETURN NEXT pRefHeader;
  151.     Open pRefDetail FOR
  152.         SELECT ou_from, warehouse_from, ou_to, warehouse_to,
  153.             rgto_id, rgto_date, rgto_no,
  154.             gto_id, gto_date, gto_no,   pengiriman_date, pengiriman_no,
  155.             finalisasi_pengiriman_date, finalisasi_pengiriman_no,
  156.             gti_id, gti_date, gti_no,
  157.             product_id, product_code, product_name, base_uom, hppa_price,
  158.             gtir_date, gtir_no,
  159.             rgto_qty, gto_qty, gti_qty,
  160.             CASE WHEN diff_gto_gti_qty < 0 THEN diff_gto_gti_qty * -1 ELSE diff_gto_gti_qty END AS diff_gto_gti_qty,
  161.             gto_qty * hppa_price AS gto_amount, gti_qty * hppa_price AS gti_amount,
  162.             CASE WHEN diff_gto_gti_qty < 0 THEN (diff_gto_gti_qty * -1) * hppa_price
  163.                     ELSE diff_gto_gti_qty * hppa_price END AS diff_gto_gti_amount,
  164.             missing_qty, rejected_qty, correction_qty, lost_qty,
  165.             missing_qty * hppa_price AS missing_amount, rejected_qty * hppa_price AS rejected_amount,
  166.             correction_qty * hppa_price AS correction_amount, lost_qty * hppa_price AS lost_amount
  167.         FROM tt_monitoring_gto A
  168.         WHERE A.session_id = pSessionId
  169.         ORDER BY A.ou_from, A.gto_date, A.gto_no, warehouse_from;
  170.     RETURN NEXT pRefDetail;
  171.  
  172.     DELETE FROM tt_monitoring_gto WHERE session_id = pSessionId;
  173.    
  174. END;
  175. $BODY$
  176.   LANGUAGE plpgsql VOLATILE
  177.   COST 100
  178.   ROWS 1000;
  179.   /
RAW Paste Data