API tools faq
paste
Advertisement
aadddrr

r_new_purchasing_relation_20170926

aadddrr
Sep 26th, 2017
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 6.59 KB | None | 0 0
raw download clone embed print report
  1. /**
  2.  * Adrian, Sep 25, 2017
  3.  * Report New Purchasing Relation dibuat berdasarkan Report Purchasing Relation,
  4.  * namun dengan perbedaan
  5.  * 1. Dimunculkan sampai detail produk
  6.  * 2. Tampilkan semua PO dengan status apapun
  7.  */
  8.  
  9. CREATE OR REPLACE FUNCTION r_new_purchasing_relation(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, bigint, character varying, character varying, double precision)
  10.   RETURNS SETOF refcursor AS
  11. $BODY$
  12. DECLARE
  13.     pRefHeader          REFCURSOR := 'refHeader';
  14.     pRefDetail          REFCURSOR := 'refDetail';
  15.     pSessionId          ALIAS FOR $1;
  16.     pTenantId           ALIAS FOR $2;
  17.     pUserId             ALIAS FOR $3;
  18.     pRoleId             ALIAS FOR $4;
  19.     pDatetime           ALIAS FOR $5;
  20.     pOuId               ALIAS FOR $6;
  21.     pPoNo               ALIAS FOR $7;
  22.     pSoNo               ALIAS FOR $8;
  23.     pSupplierId         ALIAS FOR $9;
  24.     pCustomerId         ALIAS FOR $10;
  25.     pDateFrom           ALIAS FOR $11;
  26.     pDateTo             ALIAS FOR $12;
  27.     pKursValue          ALIAS FOR $13;
  28.    
  29.     vEmptyId            bigint := -99;
  30.     vEmptyString        character varying := '';
  31.     vComboTop           character varying := 'TERMOFPAYMENT';
  32.     vFilterPoNo         text := '';
  33.     vFilterSoNo         text := '';
  34.     vFilterSupplier     text := '';
  35.     vFilterCustomer     text := '';
  36.     vDateFormat         character varying :='yyyymmdd';
  37.     vDate               character varying :='dd Mon yyyy';
  38.     vStatusRelease      character varying := 'R';
  39.     vStatusFinal        character varying := 'F';
  40. BEGIN
  41.     IF (pPoNo <> vEmptyString) THEN
  42.         vFilterPoNo := ' AND A.doc_no = ''' || pPoNo || '''';
  43.     END IF;
  44.  
  45.     IF (pSoNo <> vEmptyString) THEN
  46.         vFilterSoNo := ' AND B.doc_no = ''' || pSoNo || '''';
  47.     END IF;
  48.    
  49.     IF (pSupplierId <> vEmptyId) THEN
  50.         vFilterSupplier := ' AND A.partner_id = ' || pSupplierId;
  51.     END IF;
  52.  
  53.     IF (pCustomerId <> vEmptyId) THEN
  54.         vFilterCustomer := ' AND B.partner_id = ' || pCustomerId;
  55.     END IF;
  56.    
  57.     DELETE FROM tr_pu_purchasing_relation_po_so WHERE session_id = pSessionId;
  58.     DELETE FROM tr_pu_purchasing_relation_detail WHERE session_id = pSessionId;
  59.    
  60.     /* insert into table tr_pu_purchasing_relation_po_so*/
  61.     EXECUTE '
  62.     INSERT INTO tr_pu_purchasing_relation_po_so(session_id, tenant_id, po_id, so_id)
  63.     SELECT $1, $2, A.po_id, B.so_id
  64.     FROM pu_po A
  65.     INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id =  B.so_id AND B.status_doc IN ($6, $7)
  66.     WHERE A.tenant_id = $2
  67.         AND A.ou_id = $5
  68.         AND B.doc_date BETWEEN $3 AND $4
  69.         AND A.status_doc IN ($6, $7) ' ||
  70.         vFilterPoNo ||
  71.         vFilterSoNo ||
  72.         vFilterSupplier ||
  73.         vFilterCustomer || '
  74.     ' USING pSessionId, pTenantId, pDateFrom, pDateTo, pOuId, vStatusRelease, vStatusFinal;
  75.    
  76.     /* insert into table tr_pu_purchasing_relation_detail*/
  77.     EXECUTE '
  78.     INSERT INTO tr_pu_new_purchasing_relation_detail(
  79.            session_id, tenant_id, po_id, po_no, po_date, supp_id, supp_name,
  80.            top_po, valuta_po, po_amount, paid_po,
  81.             po_item_id, po_product_id, po_product_code, po_product_name,
  82.             po_qty, po_uom_id, po_uom_name,
  83.             so_id, so_no, so_date,
  84.            cust_id, cust_name, valuta_so, so_amount, top_so, alloc_amount,
  85.             so_item_id, so_product_id, so_product_code, so_product_name,
  86.             so_qty, so_uom_id,  so_uom_name)
  87.    SELECT $1, $2, A.po_id, A.doc_no AS po_no, A.doc_date AS po_date, A.partner_id AS supp_id, C.partner_name AS supp_name, f_get_top_days(A.top_code) AS top_po,
  88.         A.curr_code AS valuta_po, G.nett_item_amount AS po_amount, fi_get_paid_invoice_po(A.po_id) AS paid_po,
  89.         G.po_item_id, G.product_id, f_get_product_code(G.product_id), f_get_product_name(G.product_id),
  90.         G.qty_po, G.base_uom_id, f_get_uom_name(G.base_uom_id),
  91.         B.so_id, B.doc_no AS so_no, B.doc_date AS so_date, B.partner_id AS cust_id, E.partner_name AS cust_name, B.curr_code AS valuta_so,
  92.         H.nett_item_amount AS so_amount, F.due_payment_days AS top_so, fi_get_allocation_so_amount(B.so_id) AS alloc_amount,
  93.         H.so_item_id, H.product_id, f_get_product_code(H.product_id), f_get_product_name(H.product_id),
  94.         H.qty_so, H.base_uom_id, f_get_uom_name(H.base_uom_id)
  95.     FROM pu_po A
  96.     INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id =  B.so_id AND B.status_doc IN ($7, $8)
  97.     INNER JOIN m_partner C ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
  98.     INNER JOIN t_combo_value D ON D.combo_id = $3 AND A.top_code = D.code
  99.     INNER JOIN m_partner E ON A.tenant_id = E.tenant_id AND B.partner_id = E.partner_id
  100.     INNER JOIN m_cust_payment F ON A.tenant_id = F.tenant_id  AND B.partner_id = F.partner_id
  101.     INNER JOIN pu_po_item G ON G.po_id = A.po_id
  102.     INNER JOIN sl_so_item H ON H.so_id = B.so_id AND H.so_item_id = G.ref_id
  103.     WHERE A.tenant_id = $2
  104.         AND A.ou_id = $6
  105.         AND B.doc_date BETWEEN $4 AND $5 ' ||
  106.         vFilterPoNo ||
  107.         vFilterSoNo ||
  108.         vFilterSupplier ||
  109.         vFilterCustomer || '
  110.     ' USING pSessionId, pTenantId, vComboTop, pDateFrom, pDateTo, pOuId, vStatusRelease, vStatusFinal;
  111.    
  112.     /* get data for header*/
  113.     Open pRefHeader FOR
  114.         SELECT CASE WHEN pPoNo = vEmptyString THEN ' ' ELSE pPoNo END AS po_no,
  115.             CASE WHEN pSoNo = vEmptyString THEN ' ' ELSE pSoNo END AS so_no,
  116.             CASE WHEN pSupplierId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pSupplierId) END AS supp_name,
  117.             CASE WHEN pCustomerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pCustomerId) END AS cust_name,
  118.             TO_CHAR(TO_DATE(pDateFrom,vDateFormat),vDate) AS date_from,
  119.             TO_CHAR(TO_DATE(pDateTo,vDateFormat),vDate) AS date_to, pKursValue AS kurs_value, pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name;
  120.     RETURN NEXT pRefHeader;
  121.    
  122.     /* get data for detail*/
  123.     Open pRefDetail FOR
  124.         SELECT A.po_no, TO_CHAR(TO_DATE(A.po_date,vDateFormat),vDate) AS po_date, A.supp_name, A.top_po, A.valuta_po, A.po_amount,
  125.             (A.po_amount - A.paid_po) AS outstanding_po,
  126.             A.po_product_id, A.po_product_code, A.po_product_name,
  127.             A.po_qty, A.po_uom_id, A.po_uom_name,
  128.             A.so_no, TO_CHAR(TO_DATE(A.so_date,vDateFormat),vDate) AS so_date,
  129.             A.cust_name, A.valuta_so, A.so_amount, A.top_so, A.alloc_amount,
  130.             A.so_item_id, A.so_product_id, A.so_product_code, A.so_product_name,
  131.             A.so_qty, A.so_uom_id, A.so_uom_name,
  132.             (A.valuta_so||','||A.so_no) AS grouping_detail,
  133.             CASE WHEN A.valuta_so = 'USD' THEN A.so_amount * pKursValue ELSE A.so_amount END AS so_value,
  134.             CASE WHEN A.valuta_po = 'USD' THEN A.po_amount * pKursValue ELSE A.po_amount END AS po_value
  135.         FROM tr_pu_new_purchasing_relation_detail A
  136.         WHERE A.session_id = pSessionId
  137.             AND A.tenant_id = pTenantId
  138.         ORDER BY A.valuta_so, A.so_no, A.so_product_code, A.valuta_po, A.po_no, A.po_product_code;
  139.     RETURN NEXT pRefDetail ;
  140.    
  141.     DELETE FROM tr_pu_purchasing_relation_po_so WHERE session_id = pSessionId;
  142.     DELETE FROM tr_pu_purchasing_relation_detail WHERE session_id = pSessionId;
  143. END;
  144. $BODY$
  145.   LANGUAGE plpgsql VOLATILE
  146.   COST 100
  147.   ROWS 1000;
  148. /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!