widana

f_generate_po_and_rg_supplier_pkp_perubahan

Jan 25th, 2017
160
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_generate_po_and_rg_supplier_pkp_perubahan(bigint, bigint, character varying, character varying, character varying, character varying, bigint, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pTenantId               ALIAS FOR $1;
  6.     pOuId               ALIAS FOR $2;
  7.     pSessionId              ALIAS FOR $3;
  8.     pStartDate              ALIAS FOR $4;
  9.     pEndDate                ALIAS FOR $5;
  10.     pPeriodProcess          ALIAS FOR $6;
  11.     pUserId                 ALIAS FOR $7;
  12.     pDatetime               ALIAS FOR $8;
  13.  
  14.     vEmptyId            BIGINT := -99;
  15.     vEmptyString        CHARACTER VARYING := '';
  16.     vEmptyNumeric       NUMERIC := 0;
  17.     vReleaseDoc         CHARACTER VARYING := 'R';
  18.     vStock              CHARACTER VARYING := 'Y';
  19.     vFilterPartner      TEXT := '';
  20.     vDocTypeDo          BIGINT := 311;
  21.     vDocTypeDoReceipt   BIGINT := 526;
  22.     vDocTypeReturnNote  BIGINT := 502;
  23.     vDocTypeGrKonsinyaiManual  BIGINT := 114;
  24.     vYes                CHARACTER VARYING := 'Y';
  25.    
  26.     vWarehouseId            bigint;
  27.     vPurchaserId            bigint;
  28.     vRoundingModeNonTax     character varying(5);
  29.     vCurrCode               character varying := ' ';
  30.     vProductStatus              character varying := ' ';
  31.     vTopCode                character varying := ' ';
  32.    
  33.     vStatusDraft            character varying := 'D';
  34.     vStatusRelease          character varying := 'R';
  35.     vStatusFinal            character varying := 'F';  
  36.     vFlagYes                character varying := 'Y';
  37.     vFlagNo                 character varying := 'N';
  38.     vSignDebit              character varying := 'D';
  39.     vSignCredit             character varying := 'C';
  40.     vActivityCOA            character varying := 'ACTIVITY';
  41.     vProductCOA             character varying := 'PRODUCT';
  42.     vSystemCOA              character varying := 'SYSTEM';
  43.     vTypeRate               character varying := 'COM';
  44.    
  45.     vDocTypeIdPo                    bigint := 101;
  46.     vDocTypeIdRg                    bigint := 111;
  47.     vDocTypeIdPoCommSheet           bigint := 106;
  48.     vDocTypeIdGrKonsinyasiManual    bigint := 114;
  49.  
  50.     vDocNoDefaultPo             character varying := ' ';
  51.     vDocNoDefaultRg             character varying := ' ';
  52.     vDocNoDefaultPoReturn   character varying;
  53.     vDocNoDefaultRgReturn   character varying;
  54.    
  55.     vPrefixDocNoVatNormal   character varying := '010';
  56.     vWorkflowStatusApproved character varying(10) := 'APPROVED';
  57.    
  58.     vRemarkPo               character varying := 'PO Auto Generated';
  59.     vRemarkRg               character varying := 'Receive Goods Auto Generated';
  60.     vEmptyValue             character varying := ' ';
  61.     vValReleasedTrx         bigint;
  62.     vValMagento             bigint;
  63.    
  64.     vDocJournal             DOC_JOURNAL%ROWTYPE;
  65.     vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
  66.     result                  RECORD;
  67.    
  68.     vYearMonth              character varying(7);
  69.     vProcessName            character varying := 'pu_create_doc_buy_from_consignment_sold';
  70.    
  71.     vStatusGenerate         character varying := 'S';
  72.     vTaxPercentage          numeric;
  73.     vTaxId                  bigint;
  74. BEGIN
  75.    
  76.     DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
  77.     DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
  78.     DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
  79.     DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
  80.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  81.                  
  82.     -------------------------------------------------------------------------------------------------
  83.     -- Ambil nilai-nilai untuk variabel-variabel tertentu
  84.     -------------------------------------------------------------------------------------------------
  85.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'WHS.RECEIVE') INTO vWarehouseId;
  86.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'PURCHASE.OFFICER.ID') INTO vPurchaserId;
  87.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  88.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vCurrCode;
  89.    
  90.     SELECT product_status_code INTO vProductStatus FROM m_product_status WHERE flg_buy = 'Y'; -- product status
  91.     SELECT prop_key INTO vTopCode FROM t_combo_value WHERE combo_id = 'TOPTYPE' AND code = 'TOP'; -- top
  92.    
  93.     SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId AND tax_code = 'PPn';
  94.    
  95.     SELECT concat('PO/', pPeriodProcess, '/') INTO vDocNoDefaultPo;
  96.     SELECT concat('GR/', pPeriodProcess, '/') INTO vDocNoDefaultRg;
  97.     SELECT concat('POR/', pPeriodProcess, '/') INTO vDocNoDefaultPoReturn;
  98.     SELECT concat('GRR/', pPeriodProcess, '/') INTO vDocNoDefaultRgReturn;
  99.    
  100.     SELECT f_get_ou_bu_structure(pOuId) AS ou, f_get_document_journal(vDocTypeIdRg) AS doc INTO result;
  101.     vDocJournal := result.doc;
  102.     vOuStructure := result.ou;
  103.    
  104.     SELECT val_unsubmit_trx_generate_po_rg_for_cland(pTenantId, pSessionId, pOuId, pPeriodProcess, pStartDate, pEndDate, pDatetime, pUserId, vProcessName) INTO vValReleasedTrx;
  105.     SELECT val_check_error_magento_trx_for_generate_po_rg_for_cland(pTenantId, pSessionId, pOuId, pDatetime, pUserId, vProcessName, pStartDate, pEndDate) INTO vValMagento;
  106.    
  107.     IF vValReleasedTrx = 0 THEN
  108.    
  109.         IF vValMagento = 0 THEN
  110.             /*
  111.              * insert data into table tr_report_sales_supplier for DO from in_log_product_balance_stock
  112.              */
  113.             EXECUTE '
  114.             INSERT INTO tr_report_sales_supplier(
  115.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  116.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  117.                    product_name, product_style, color, psize, normal_price, sold_price,
  118.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  119.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  120.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  121.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  122.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  123.             FROM in_log_product_balance_stock A
  124.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  125.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  126.             INNER JOIN sl_do D ON A.ref_id = D.do_id
  127.             INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
  128.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  129.             WHERE A.tenant_id = $2
  130.                 AND A.doc_date BETWEEN $5 AND $6
  131.                 AND A.ou_id = $3
  132.                 AND B.flg_buy_konsinyasi = $7
  133.                 AND A.doc_type_id = $8 ' ||
  134.                 vFilterPartner || '
  135.                 AND F.ref_doc_type_id = -99 AND F.ref_id = -99
  136.             GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  137.             F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
  138.             ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo;
  139.  
  140.             /*
  141.              * insert data into table tr_report_sales_supplier for DO from in_log_product_consignment_balance_stock
  142.              */
  143.             EXECUTE '
  144.             INSERT INTO tr_report_sales_supplier(
  145.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  146.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  147.                    product_name, product_style, color, psize, normal_price, sold_price,
  148.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  149.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  150.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  151.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  152.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  153.             FROM in_log_product_consignment_balance_stock A
  154.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  155.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  156.             INNER JOIN sl_do D ON A.ref_id = D.do_id
  157.             INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
  158.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  159.             WHERE A.tenant_id = $2
  160.                 AND A.doc_date BETWEEN $5 AND $6
  161.                 AND A.ou_id = $3
  162.                 AND B.flg_buy_konsinyasi = $7
  163.                 AND A.doc_type_id = $8 ' ||
  164.                 vFilterPartner || '
  165.                 AND F.ref_doc_type_id = -99 AND F.ref_id = -99
  166.             GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  167.             F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
  168.             ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo;
  169.  
  170.             ----------------------------------------------------------------------------------------------
  171.             /* perubahan mengenai gr konsinyasi manual di function f_generate_po_and_rg_supplier_pkp yang
  172.              * lama tidak memproses generate po and rg mengenai GR konsinyasi manual */
  173.             ----------------------------------------------------------------------------------------------
  174.             EXECUTE '
  175.             INSERT INTO tr_report_sales_supplier(
  176.                 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  177.                 doc_date, payment_method, product_id, product_code, supplier_product_code,
  178.                 product_name, product_style, color, psize, normal_price, sold_price,
  179.                 discount, margin_supp, sold_price_after_margin, qty_so)
  180.             SELECT $1,$2,$3,D.supplier_id,A.so_item_id,f_get_doc_desc($8),B.doc_no,
  181.                 B.doc_date,$4,C.product_id,f_get_product_code(C.product_id),D.supplier_product_code,
  182.                 f_get_product_name(C.product_id),E.style_product, E.color, E.size,
  183.                 A.normal_price_correction, A.sold_price_used,
  184.                 A.discount, A.margin_supp_correction, A.sold_price_after_margin, SUM(C.qty_rcv_po)
  185.             FROM pu_po_balance_item_consignment_sold A
  186.             INNER JOIN pu_receive_goods B ON A.receive_goods_id = B.receive_goods_id
  187.             INNER JOIN pu_receive_goods_item C ON A.receive_goods_item_id = A.receive_goods_item_id
  188.                     AND B.receive_goods_id = C.receive_goods_id
  189.             INNER JOIN m_product_consignment_supp_info D ON C.product_id = D.product_id
  190.             INNER JOIN m_product_custom E ON C.product_id = E.product_id
  191.             WHERE B.tenant_id = $2
  192.                 AND B.doc_date BETWEEN $5 AND $6
  193.                 AND E.flg_buy_konsinyasi = $7
  194.                 AND A.from_manual = $7
  195.                 AND B.doc_type_id = $8 '|| vFilterPartner || '
  196.                 AND A.ref_id = -99 AND A.ref_doc_type_id = -99
  197.             GROUP BY D.supplier_id, A.so_item_id, B.doc_type_id, B.doc_no, B.doc_date, C.product_id,
  198.                  D.supplier_product_code, E.style_product, E.color, E.size,
  199.                  A.normal_price_correction, A.sold_price_used, A.discount, A.margin_supp_correction, A.sold_price_after_margin'
  200.             USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeGrKonsinyaiManual;
  201.            
  202.             EXECUTE '
  203.                 INSERT INTO tr_report_sales_supplier(
  204.                        session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  205.                        doc_date, payment_method, product_id, product_code, supplier_product_code,
  206.                        product_name, product_style, color, psize, normal_price, sold_price,
  207.                        discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  208.                 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(Z.doc_type_id), Z.doc_no,
  209.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  210.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  211.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  212.                 FROM in_log_product_consignment_balance_stock A
  213.                 INNER JOIN m_product_custom B ON A.product_id = B.product_id
  214.                 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  215.                 INNER JOIN sl_do D ON A.ref_id = D.do_id
  216.                 INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
  217.                 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  218.                 INNER JOIN pu_receive_goods Z ON F.receive_goods_id = Z.receive_goods_id
  219.                 INNER JOIN pu_receive_goods_item Y ON Z.receive_goods_id = Y.receive_goods_id
  220.                         AND F.receive_goods_item_id = Y.receive_goods_item_id
  221.                         AND E.product_id = Y.product_id
  222.                 WHERE A.tenant_id = $2
  223.                     AND A.doc_date BETWEEN BETWEEN $5 AND $6
  224.                     AND A.ou_id = $3
  225.                     AND B.flg_buy_konsinyasi = $7
  226.                     AND A.doc_type_id = $8
  227.                     AND F.ref_doc_type_id = -99 AND F.ref_id = -99
  228.                 GROUP BY C.supplier_id, E.ref_id, Z.doc_type_id, Z.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  229.                 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id        
  230.                 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo;
  231.            
  232.             /*
  233.              * insert data into table tr_report_sales_supplier for Return Note from in_log_product_balance_stock
  234.              */
  235.             EXECUTE '
  236.             INSERT INTO tr_report_sales_supplier(
  237.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  238.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  239.                    product_name, product_style, color, psize, normal_price, sold_price,
  240.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  241.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  242.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  243.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  244.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  245.             FROM in_log_product_balance_stock A
  246.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  247.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  248.             INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
  249.             INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
  250.             INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
  251.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  252.             WHERE A.tenant_id = $2
  253.                 AND A.doc_date BETWEEN $5 AND $6
  254.                 AND A.ou_id = $3
  255.                 AND B.flg_buy_konsinyasi = $7
  256.                 AND A.doc_type_id IN ($9) ' ||
  257.                 vFilterPartner || '
  258.             GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  259.             F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
  260.             ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
  261.            
  262.             /*
  263.              * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_balance_stock
  264.              */
  265.             EXECUTE '
  266.             INSERT INTO tr_report_sales_supplier(
  267.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  268.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  269.                    product_name, product_style, color, psize, normal_price, sold_price,
  270.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  271.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  272.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  273.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  274.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  275.             FROM in_log_product_balance_stock A
  276.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  277.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  278.             INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
  279.             INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
  280.             INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
  281.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  282.             WHERE A.tenant_id = $2
  283.                 AND A.doc_date BETWEEN $5 AND $6
  284.                 AND A.ou_id = $3
  285.                 AND B.flg_buy_konsinyasi = $7
  286.                 AND A.doc_type_id IN ($8) ' ||
  287.                 vFilterPartner || '
  288.             GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  289.             F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
  290.             ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
  291.            
  292.             /*
  293.              * insert data into table tr_report_sales_supplier for Return Note from in_log_product_consignment_balance_stock
  294.              */
  295.             EXECUTE '
  296.             INSERT INTO tr_report_sales_supplier(
  297.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  298.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  299.                    product_name, product_style, color, psize, normal_price, sold_price,
  300.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  301.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  302.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  303.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  304.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  305.             FROM in_log_product_consignment_balance_stock A
  306.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  307.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  308.             INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
  309.             INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
  310.             INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
  311.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  312.             WHERE A.tenant_id = $2
  313.                 AND A.doc_date BETWEEN $5 AND $6
  314.                 AND A.ou_id = $3
  315.                 AND B.flg_buy_konsinyasi = $7
  316.                 AND A.doc_type_id IN ($9) ' ||
  317.                 vFilterPartner || '
  318.             GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  319.             F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
  320.             ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
  321.            
  322.             /*
  323.              * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_consignment_balance_stock
  324.              */
  325.             EXECUTE '
  326.             INSERT INTO tr_report_sales_supplier(
  327.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  328.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  329.                    product_name, product_style, color, psize, normal_price, sold_price,
  330.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  331.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  332.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  333.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  334.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  335.             FROM in_log_product_consignment_balance_stock A
  336.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  337.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  338.             INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
  339.             INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
  340.             INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
  341.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  342.             WHERE A.tenant_id = $2
  343.                 AND A.doc_date BETWEEN $5 AND $6
  344.                 AND A.ou_id = $3
  345.                 AND B.flg_buy_konsinyasi = $7
  346.                 AND A.doc_type_id IN ($8) ' ||
  347.                 vFilterPartner || '
  348.             GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
  349.             F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
  350.             ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
  351.  
  352.             -- hanya yang supplier PKP saja, delete semua data partner yang non PKP
  353.             DELETE FROM tr_report_sales_supplier A
  354.             WHERE NOT EXISTS (
  355.                 SELECT 1 FROM m_partner_npwp B
  356.                 WHERE A.partner_id = B.partner_id
  357.                 AND B.flg_pkp = 'Y'
  358.             );
  359.            
  360.            
  361.             -------------------------------------------------------------------------------------------------
  362.             -- Buat summary penjualan & return untuk persiapan offset
  363.             -------------------------------------------------------------------------------------------------
  364.                    
  365.             INSERT INTO tt_summary_doc_sales_item_penjualan_by_period
  366.                 (session_id, supplier_id, record_type,
  367.                 product_id, product_balance_id,
  368.                 qty, gross_price_po, tax_percentage)
  369.             SELECT pSessionId, A.partner_id, 'SALES',
  370.                    A.product_id, A.product_balance_id,
  371.                    SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
  372.             FROM tr_report_sales_supplier A
  373.             INNER JOIN m_document B ON A.transaction_type = B.doc_desc
  374.             WHERE A.session_id = pSessionId
  375.             AND B.doc_type_id IN (vDocTypeDo)
  376.             GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
  377.                    
  378.             RAISE NOTICE '4';
  379.            
  380.             INSERT INTO tt_summary_doc_return_item_penjualan_by_period
  381.                 (session_id, supplier_id, record_type,
  382.                 product_id, product_balance_id,
  383.                 qty, gross_price_po, tax_percentage)
  384.             SELECT pSessionId, A.partner_id, 'RETURN',
  385.                    A.product_id, A.product_balance_id,
  386.                    SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
  387.             FROM tr_report_sales_supplier A
  388.             INNER JOIN m_document B ON A.transaction_type = B.doc_desc
  389.             WHERE A.session_id = pSessionId
  390.             AND B.doc_type_id IN (vDocTypeReturnNote, vDocTypeDoReceipt)
  391.             GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
  392.                    
  393.             RAISE NOTICE '5';
  394.            
  395.             WITH summary_item_sales AS
  396.             (
  397.                 SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
  398.                 FROM tt_summary_doc_sales_item_penjualan_by_period A
  399.                 WHERE A.session_id = pSessionId
  400.                 GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
  401.             ), summary_item_return AS
  402.             (
  403.                 SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
  404.                 FROM tt_summary_doc_return_item_penjualan_by_period A
  405.                 WHERE A.session_id = pSessionId
  406.                 GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
  407.             )
  408.             UPDATE tt_summary_doc_return_item_penjualan_by_period A
  409.             SET record_type = 'SALES'
  410.             WHERE EXISTS (
  411.                 SELECT 1 FROM summary_item_sales B
  412.                 INNER JOIN summary_item_return C ON B.session_id = C.session_id AND B.product_id = C.product_id AND B.supplier_id = C.supplier_id AND B.tax_percentage = C.tax_percentage
  413.                 WHERE A.session_id = B.session_id
  414.                 AND A.supplier_id = B.supplier_id
  415.                 AND A.tax_percentage = B.tax_percentage
  416.                 AND A.product_id = B.product_id
  417.                 AND C.item_amount <= B.item_amount
  418.             ) AND A.session_id = pSessionId;
  419.            
  420.             INSERT INTO tt_summary_item_penjualan_by_period
  421.                 (session_id, supplier_id,  
  422.                 product_id, product_balance_id,
  423.                 qty, gross_price_po)
  424.             SELECT pSessionId, A.supplier_id,
  425.                    A.product_id, A.product_balance_id,
  426.                    A.qty, A.gross_price_po
  427.             FROM tt_summary_doc_sales_item_penjualan_by_period A
  428.             WHERE A.session_id = pSessionId;
  429.            
  430.             RAISE NOTICE '7';
  431.            
  432.             INSERT INTO tt_summary_item_penjualan_by_period
  433.                 (session_id, supplier_id,  
  434.                 product_id, product_balance_id,
  435.                 qty, gross_price_po)
  436.             SELECT pSessionId, A.supplier_id,
  437.                    A.product_id, A.product_balance_id,
  438.                    A.qty, A.gross_price_po
  439.             FROM tt_summary_doc_return_item_penjualan_by_period A
  440.             WHERE A.session_id = pSessionId
  441.             AND A.record_type = 'SALES';
  442.            
  443.             RAISE NOTICE '8';
  444.            
  445.             INSERT INTO tt_summary_item_return_by_period
  446.                 (session_id, supplier_id,  
  447.                 product_id, product_balance_id,
  448.                 qty, gross_price_po, tax_percentage)
  449.             SELECT pSessionId, A.supplier_id,
  450.                    A.product_id, A.product_balance_id,
  451.                    A.qty, A.gross_price_po, A.tax_percentage
  452.             FROM tt_summary_doc_return_item_penjualan_by_period A
  453.             WHERE A.session_id = pSessionId
  454.             AND A.record_type = 'RETURN';
  455.            
  456.             INSERT INTO tt_monthly_supplier_po_rg(
  457.                 session_id, supplier_id,  
  458.                 po_id, po_no, po_date,
  459.                 receive_goods_id, receive_goods_no, receive_goods_date
  460.             )
  461.             SELECT pSessionId, A.supplier_id,
  462.                    nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
  463.                    nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
  464.             FROM tt_summary_item_penjualan_by_period A
  465.             WHERE A.session_id = pSessionId
  466.             GROUP BY A.supplier_id;
  467.            
  468.             INSERT INTO tt_monthly_supplier_po_rg_return(
  469.                 session_id, supplier_id,
  470.                 po_id, po_no, po_date,
  471.                 receive_goods_id, receive_goods_no, receive_goods_date
  472.             )
  473.             SELECT pSessionId, A.supplier_id,
  474.                    nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
  475.                    nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
  476.             FROM tt_summary_item_return_by_period A
  477.             WHERE A.session_id = pSessionId
  478.             GROUP BY A.supplier_id;
  479.            
  480.             UPDATE tt_monthly_supplier_po_rg A
  481.             SET po_no = vDocNoDefaultPo||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
  482.                 receive_goods_no = vDocNoDefaultRg||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
  483.             WHERE A.session_id = pSessionId;
  484.            
  485.             UPDATE tt_monthly_supplier_po_rg_return A
  486.             SET po_no = vDocNoDefaultPoReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
  487.                 receive_goods_no = vDocNoDefaultRgReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
  488.             WHERE A.session_id = pSessionId;
  489.            
  490.            
  491.            
  492.             -- UPDATE po_date berdasarkan data penjualan minimum
  493.             WITH summary_item_penjualan AS (
  494.                 SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
  495.                 WHERE A.session_id = pSessionId
  496.                 GROUP BY A.session_id, A.partner_id
  497.             )
  498.             UPDATE tt_monthly_supplier_po_rg A
  499.             SET po_date = B.doc_date, receive_goods_date = B.doc_date
  500.             FROM summary_item_penjualan B
  501.             WHERE B.session_id = A.session_id
  502.             AND A.session_id = pSessionId
  503.             AND B.partner_id = A.supplier_id;
  504.            
  505.             WITH summary_item_penjualan AS (
  506.                 SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
  507.                 WHERE A.session_id = pSessionId
  508.                 GROUP BY A.session_id, A.partner_id
  509.             )
  510.             UPDATE tt_monthly_supplier_po_rg_return A
  511.             SET po_date = B.doc_date, receive_goods_date = B.doc_date
  512.             FROM summary_item_penjualan B
  513.             WHERE B.session_id = A.session_id
  514.             AND A.session_id = pSessionId
  515.             AND B.partner_id = A.supplier_id;
  516.            
  517.            
  518.             -- UPDATE jika po_datenya NULL
  519.             UPDATE tt_monthly_supplier_po_rg A
  520.             SET po_date = pStartDate, receive_goods_date = pStartDate
  521.             WHERE A.session_id = pSessionId
  522.             AND po_date IS NULL;
  523.            
  524.             UPDATE tt_monthly_supplier_po_rg_return A
  525.             SET po_date = pStartDate, receive_goods_date = pStartDate
  526.             WHERE A.session_id = pSessionId
  527.             AND po_date IS NULL;
  528.            
  529.            
  530.            
  531.             UPDATE pu_po_balance_item_consignment_sold A
  532.             SET flg_process = vFlagYes, ref_doc_type_id = vDocTypeIdRg, ref_id = receive_goods_id,  
  533.                 version = A.version + 1, update_user_id = pUserId, update_datetime = pDatetime
  534.             FROM tt_monthly_supplier_po_rg B
  535.             WHERE B.session_id = pSessionId
  536.             AND B.supplier_id = A.supplier_id
  537.             AND A.ref_doc_type_id = -99 AND A.ref_id = -99
  538.             AND EXISTS (
  539.                 SELECT 1
  540.                 FROM tr_report_sales_supplier C
  541.                 WHERE C.session_id = pSessionId
  542.                 AND C.so_item_id = A.so_item_id
  543.                 AND C.tenant_id = A.tenant_id
  544.                 AND C.partner_id = A.supplier_id
  545.             );
  546.            
  547.             -------------------------------------------------------------------------------------------------
  548.             -- Buat data PO
  549.             -------------------------------------------------------------------------------------------------
  550.             INSERT INTO pu_po
  551.                     (po_id, tenant_id, doc_type_id, doc_no,
  552.                     doc_date, ou_id, ext_doc_no, ext_doc_date,
  553.                     ref_doc_type_id, ref_id, remark,
  554.                     partner_id, purchaser_id, warehouse_id,
  555.                     flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
  556.                     top_code, status_doc, workflow_status,
  557.                     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  558.             SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
  559.                 A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
  560.                 vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
  561.                 A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
  562.                 vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
  563.                 vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
  564.                 0, pDatetime, pUserId, pDatetime, pUserId
  565.             FROM tt_monthly_supplier_po_rg A
  566.             WHERE A.session_id = pSessionId
  567.             GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
  568.            
  569.             INSERT INTO pu_po
  570.                     (po_id, tenant_id, doc_type_id, doc_no,
  571.                     doc_date, ou_id, ext_doc_no, ext_doc_date,
  572.                     ref_doc_type_id, ref_id, remark,
  573.                     partner_id, purchaser_id, warehouse_id,
  574.                     flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
  575.                     top_code, status_doc, workflow_status,
  576.                     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  577.             SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
  578.                 A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
  579.                 vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
  580.                 A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
  581.                 vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
  582.                 vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
  583.                 0, pDatetime, pUserId, pDatetime, pUserId
  584.             FROM tt_monthly_supplier_po_rg_return A
  585.             WHERE A.session_id = pSessionId
  586.             GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
  587.            
  588.             RAISE NOTICE '7';
  589.            
  590.             INSERT INTO pu_po_item
  591.                 (tenant_id, po_id, line_no,
  592.                 ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
  593.                 curr_code, gross_price_po,
  594.                 flg_tax_amount, tax_id, tax_percentage,
  595.                 tax_price,
  596.                 nett_price_po,
  597.                 qty_po, po_uom_id, qty_int, base_uom_id,
  598.                 discount_percentage, discount_amount, gross_item_amount,
  599.                 nett_item_amount, tax_amount,
  600.                 activity_gl_id, product_coa_id, ou_rc_id, eta,
  601.                 tolerance_rcv_qty, remark, version,
  602.                 create_datetime, create_user_id, update_datetime, update_user_id,
  603.                 segment_id, eta_day, flg_indent)
  604.             SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
  605.                    vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
  606.                    vCurrCode, A.gross_price_po,
  607.                    vFlagYes, vTaxId, vTaxPercentage,
  608.                    f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
  609.                    f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
  610.                    SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
  611.                    0, 0, 0,
  612.                    0, 0,
  613.                    vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
  614.                    0, vEmptyValue, 0,
  615.                    pDatetime, pUserId, pDatetime, pUserId,
  616.                    vEmptyId, vEmptyValue, vFlagNo
  617.             FROM tt_summary_item_penjualan_by_period A
  618.             INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  619.             WHERE A.session_id = pSessionId
  620.             GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
  621.            
  622.             INSERT INTO pu_po_item
  623.                 (tenant_id, po_id, line_no,
  624.                 ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
  625.                 curr_code, gross_price_po,
  626.                 flg_tax_amount, tax_id, tax_percentage,
  627.                 tax_price,
  628.                 nett_price_po,
  629.                 qty_po, po_uom_id, qty_int, base_uom_id,
  630.                 discount_percentage, discount_amount, gross_item_amount,
  631.                 nett_item_amount, tax_amount,
  632.                 activity_gl_id, product_coa_id, ou_rc_id, eta,
  633.                 tolerance_rcv_qty, remark, version,
  634.                 create_datetime, create_user_id, update_datetime, update_user_id,
  635.                 segment_id, eta_day, flg_indent)
  636.             SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
  637.                    vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
  638.                    vCurrCode, A.gross_price_po,
  639.                    vFlagYes, vTaxId, vTaxPercentage,
  640.                    f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
  641.                    f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
  642.                    SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
  643.                    0, 0, 0,
  644.                    0, 0,
  645.                    vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
  646.                    0, vEmptyValue, 0,
  647.                    pDatetime, pUserId, pDatetime, pUserId,
  648.                    vEmptyId, vEmptyValue, vFlagNo
  649.             FROM tt_summary_item_return_by_period A
  650.             INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  651.             WHERE A.session_id = pSessionId
  652.             GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
  653.                
  654.             RAISE NOTICE '8';
  655.            
  656.             UPDATE pu_po_item A
  657.             SET gross_item_amount = A.gross_price_po * A.qty_po,
  658.                 nett_item_amount = A.nett_price_po * A.qty_po,
  659.                 tax_amount = A.tax_price * A.qty_po
  660.             WHERE EXISTS (
  661.                 SELECT 1 FROM tt_monthly_supplier_po_rg B
  662.                 WHERE B.session_id = pSessionId
  663.                 AND B.po_id = A.po_id
  664.             );
  665.            
  666.             UPDATE pu_po_item A
  667.             SET gross_item_amount = A.gross_price_po * A.qty_po,
  668.                 nett_item_amount = A.nett_price_po * A.qty_po,
  669.                 tax_amount = A.tax_price * A.qty_po
  670.             WHERE EXISTS (
  671.                 SELECT 1 FROM tt_monthly_supplier_po_rg_return B
  672.                 WHERE B.session_id = pSessionId
  673.                 AND B.po_id = A.po_id
  674.             );
  675.            
  676.             RAISE NOTICE '9';
  677.            
  678.             INSERT INTO pu_po_balance_item
  679.             (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
  680.              qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
  681.              tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
  682.             SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
  683.                     A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
  684.                     A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
  685.             FROM pu_po_item A, pu_po B
  686.             WHERE A.po_id = B.po_id
  687.             AND EXISTS (
  688.                 SELECT 1 FROM tt_monthly_supplier_po_rg C
  689.                 WHERE C.session_id = pSessionId
  690.                 AND C.po_id = A.po_id
  691.             );
  692.            
  693.             INSERT INTO pu_po_balance_item
  694.             (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
  695.              qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
  696.              tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
  697.             SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
  698.                     A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
  699.                     A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
  700.             FROM pu_po_item A, pu_po B
  701.             WHERE A.po_id = B.po_id
  702.             AND EXISTS (
  703.                 SELECT 1 FROM tt_monthly_supplier_po_rg_return C
  704.                 WHERE C.session_id = pSessionId
  705.                 AND C.po_id = A.po_id
  706.             );
  707.            
  708.             RAISE NOTICE '10';
  709.              
  710.             INSERT INTO pu_log_po_balance_item
  711.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  712.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  713.             version, create_datetime, create_user_id, update_datetime, update_user_id)
  714.             SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
  715.                 A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
  716.                 0, pDatetime, pUserId, pDatetime, pUserId
  717.             FROM pu_po_item A, pu_po B
  718.             WHERE A.po_id = B.po_id
  719.             AND EXISTS(
  720.                 SELECT 1 FROM tt_monthly_supplier_po_rg C
  721.                 WHERE C.session_id = pSessionId
  722.                 AND C.po_id = A.po_id
  723.             );
  724.            
  725.             INSERT INTO pu_log_po_balance_item
  726.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  727.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  728.             version, create_datetime, create_user_id, update_datetime, update_user_id)
  729.             SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
  730.                 A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
  731.                 0, pDatetime, pUserId, pDatetime, pUserId
  732.             FROM pu_po_item A, pu_po B
  733.             WHERE A.po_id = B.po_id
  734.             AND EXISTS(
  735.                 SELECT 1 FROM tt_monthly_supplier_po_rg_return C
  736.                 WHERE C.session_id = pSessionId
  737.                 AND C.po_id = A.po_id
  738.             );
  739.            
  740.             RAISE NOTICE '11';
  741.            
  742.             -------------------------------------------------------------------------------------------------
  743.             -- Buat data Receive Goods
  744.             -------------------------------------------------------------------------------------------------
  745.            
  746.             INSERT INTO pu_receive_goods
  747.                 (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
  748.                 ou_id, ext_doc_no, ext_doc_date,
  749.                 ref_doc_type_id, ref_id,
  750.                 remark, partner_id, warehouse_id, status_doc, workflow_status,
  751.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  752.             SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
  753.                 pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
  754.                 vDocTypeIdPo, A.po_id,
  755.                 vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
  756.                 vStatusRelease, vWorkflowStatusApproved,
  757.                 0, pDatetime, pUserId, pDatetime, pUserId
  758.             FROM tt_monthly_supplier_po_rg A
  759.             WHERE A.session_id = pSessionId
  760.             GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
  761.            
  762.             -- menambahkan untuk generate po and rg, RGnya harus tercatat di pu_po_rg_generated
  763.             INSERT INTO pu_po_rg_generated (doc_type_id, ref_id, doc_no, partner_id,
  764.                 create_datetime, create_user_id,
  765.                 update_datetime, update_user_id,
  766.                 version)
  767.             SELECT vDocTypeIdRg, A.receive_goods_id,  A.receive_goods_no, A.supplier_id,
  768.                 pDatetime, pUserId,
  769.                 pDatetime, pUserId,
  770.                 0
  771.             FROM tt_monthly_supplier_po_rg A
  772.             WHERE A.session_id = pSessionId
  773.             GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;     
  774.            
  775.             INSERT INTO pu_receive_goods
  776.                 (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
  777.                 ou_id, ext_doc_no, ext_doc_date,
  778.                 ref_doc_type_id, ref_id,
  779.                 remark, partner_id, warehouse_id, status_doc, workflow_status,
  780.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  781.             SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
  782.                 pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
  783.                 vDocTypeIdPo, A.po_id,
  784.                 vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
  785.                 vStatusRelease, vWorkflowStatusApproved,
  786.                 0, pDatetime, pUserId, pDatetime, pUserId
  787.             FROM tt_monthly_supplier_po_rg_return A
  788.             WHERE A.session_id = pSessionId
  789.             GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
  790.            
  791.            
  792.             RAISE NOTICE '12';
  793.                
  794.             INSERT INTO pu_receive_goods_item
  795.                 (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
  796.                  product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
  797.                  remark,
  798.                  version, create_datetime, create_user_id, update_datetime, update_user_id)
  799.             SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
  800.                    C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
  801.                    vEmptyValue,
  802.                    0, pDatetime, pUserId, pDatetime, pUserId
  803.             FROM tt_monthly_supplier_po_rg A
  804.             INNER JOIN pu_po B ON A.po_id = B.po_id
  805.             INNER JOIN pu_po_item C ON B.po_id = C.po_id
  806.             WHERE A.session_id = pSessionId;
  807.            
  808.             INSERT INTO pu_receive_goods_item
  809.                 (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
  810.                  product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
  811.                  remark,
  812.                  version, create_datetime, create_user_id, update_datetime, update_user_id)
  813.             SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
  814.                    C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
  815.                    vEmptyValue,
  816.                    0, pDatetime, pUserId, pDatetime, pUserId
  817.             FROM tt_monthly_supplier_po_rg_return A
  818.             INNER JOIN pu_po B ON A.po_id = B.po_id
  819.             INNER JOIN pu_po_item C ON B.po_id = C.po_id
  820.             WHERE A.session_id = pSessionId;
  821.            
  822.             RAISE NOTICE '13';
  823.            
  824.             INSERT INTO pu_log_po_balance_item
  825.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  826.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  827.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  828.             SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
  829.                 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
  830.                 0, pDatetime, pUserId, pDatetime, pUserId
  831.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  832.             WHERE A.receive_goods_id = B.receive_goods_id AND
  833.                   B.ref_id = C.po_item_id AND EXISTS (
  834.                         SELECT 1 FROM tt_monthly_supplier_po_rg D
  835.                         WHERE D.receive_goods_id = A.receive_goods_id
  836.                         AND D.session_id = pSessionId
  837.                   );
  838.                  
  839.             INSERT INTO pu_log_po_balance_item
  840.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  841.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  842.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  843.             SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
  844.                 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
  845.                 0, pDatetime, pUserId, pDatetime, pUserId
  846.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  847.             WHERE A.receive_goods_id = B.receive_goods_id AND
  848.                   B.ref_id = C.po_item_id AND EXISTS (
  849.                         SELECT 1 FROM tt_monthly_supplier_po_rg_return D
  850.                         WHERE D.receive_goods_id = A.receive_goods_id
  851.                         AND D.session_id = pSessionId
  852.                   );
  853.            
  854.             RAISE NOTICE '14';
  855.                  
  856.             INSERT INTO pu_po_balance_invoice
  857.             (tenant_id, ou_id, partner_id, po_id,
  858.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  859.             curr_code, price_po, item_amount, flg_invoice, invoice_id,
  860.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  861.             SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  862.                 A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
  863.                 C.curr_code, C.nett_price_po,
  864.                 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
  865.                 vFlagNo, vEmptyId,
  866.                 0, pDatetime, pUserId, pDatetime, pUserId
  867.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  868.             WHERE A.receive_goods_id = B.receive_goods_id AND
  869.                   B.ref_id = C.po_item_id AND EXISTS (
  870.                         SELECT 1 FROM tt_monthly_supplier_po_rg D
  871.                         WHERE D.receive_goods_id = A.receive_goods_id
  872.                         AND D.session_id = pSessionId
  873.                   );
  874.                  
  875.             INSERT INTO pu_po_balance_invoice
  876.             (tenant_id, ou_id, partner_id, po_id,
  877.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  878.             curr_code, price_po, item_amount, flg_invoice, invoice_id,
  879.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  880.             SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  881.                 A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
  882.                 C.curr_code, C.nett_price_po,
  883.                 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
  884.                 vFlagNo, vEmptyId,
  885.                 0, pDatetime, pUserId, pDatetime, pUserId
  886.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  887.             WHERE A.receive_goods_id = B.receive_goods_id AND
  888.                   B.ref_id = C.po_item_id AND EXISTS (
  889.                         SELECT 1 FROM tt_monthly_supplier_po_rg_return D
  890.                         WHERE D.receive_goods_id = A.receive_goods_id
  891.                         AND D.session_id = pSessionId
  892.                   );
  893.                  
  894.             INSERT INTO pu_po_balance_invoice_tax
  895.         (tenant_id, ou_id, partner_id, po_id,
  896.         ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  897.         tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  898.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  899.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  900.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
  901.             C.tax_percentage, C.curr_code,
  902.             f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
  903.             f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
  904.             vFlagNo, vEmptyId,
  905.             0, pDatetime, pUserId, pDatetime, pUserId
  906.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
  907.         WHERE A.receive_goods_id = B.receive_goods_id AND
  908.               B.ref_id = C.po_item_id AND
  909.               C.tax_id = D.tax_id AND EXISTS (
  910.                     SELECT 1 FROM tt_monthly_supplier_po_rg D
  911.                     WHERE D.receive_goods_id = A.receive_goods_id
  912.                     AND D.session_id = pSessionId
  913.               );
  914.              
  915.               RAISE NOTICE '32';
  916.              
  917.         INSERT INTO pu_po_balance_invoice_tax
  918.         (tenant_id, ou_id, partner_id, po_id,
  919.         ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  920.         tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  921.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  922.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  923.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
  924.             C.tax_percentage, C.curr_code,
  925.             f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
  926.             f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
  927.             vFlagNo, vEmptyId,
  928.             0, pDatetime, pUserId, pDatetime, pUserId
  929.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
  930.         WHERE A.receive_goods_id = B.receive_goods_id AND
  931.               B.ref_id = C.po_item_id AND
  932.               C.tax_id = D.tax_id AND EXISTS (
  933.                     SELECT 1 FROM tt_monthly_supplier_po_rg_return D
  934.                     WHERE D.receive_goods_id = A.receive_goods_id
  935.                     AND D.session_id = pSessionId
  936.               );
  937.              
  938.               RAISE NOTICE '33';
  939.                  
  940.             -------------------------------------------------------------------------------------------------
  941.             -- Buat data log product balance stock
  942.             -------------------------------------------------------------------------------------------------      
  943.                  
  944.             INSERT INTO in_log_product_balance_stock
  945.             (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  946.              product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  947.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  948.             SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
  949.                    A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(A.qty),
  950.                    0, pDatetime, pUserId, pDatetime, pUserId
  951.             FROM tt_summary_item_penjualan_by_period A
  952.             INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  953.             WHERE A.session_id = pSessionId
  954.             GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
  955.            
  956.             INSERT INTO in_log_product_balance_stock
  957.             (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  958.              product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  959.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  960.             SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
  961.                    A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(-1*A.qty),
  962.                    0, pDatetime, pUserId, pDatetime, pUserId
  963.             FROM tt_summary_item_penjualan_by_period A
  964.             INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  965.             WHERE A.session_id = pSessionId
  966.             GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
  967.            
  968.             -------------------------------------------------------------------------------------------------
  969.             -- Buat data jurnal penerimaan barang
  970.             -------------------------------------------------------------------------------------------------
  971.            
  972.             PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
  973.             FROM pu_receive_goods A
  974.             WHERE EXISTS (
  975.                 SELECT 1 FROM tt_monthly_supplier_po_rg E
  976.                 WHERE E.session_id = pSessionId
  977.                 AND A.receive_goods_id = E.receive_goods_id
  978.             );
  979.            
  980.             RAISE NOTICE '18';
  981.            
  982.             INSERT INTO tt_generate_po_rg_for_journal
  983.                 (session_id, receive_goods_id, journal_trx_id)
  984.             SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
  985.             FROM tt_monthly_supplier_po_rg A
  986.             WHERE A.session_id = pSessionId
  987.             GROUP BY A.receive_goods_id;
  988.            
  989.             INSERT INTO tt_generate_po_rg_for_journal
  990.                 (session_id, receive_goods_id, journal_trx_id)
  991.             SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
  992.             FROM tt_monthly_supplier_po_rg_return A
  993.             WHERE A.session_id = pSessionId
  994.             GROUP BY A.receive_goods_id;
  995.            
  996.             RAISE NOTICE '19';
  997.            
  998.             INSERT INTO gl_journal_trx
  999.             (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  1000.             ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  1001.             ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  1002.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1003.             SELECT C.journal_trx_id, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
  1004.                 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  1005.                 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
  1006.                 0, pDatetime, pUserId, pDatetime, pUserId
  1007.             FROM pu_receive_goods A, pu_po B, tt_generate_po_rg_for_journal C
  1008.             WHERE A.ref_doc_type_id = B.doc_type_id
  1009.                 AND A.ref_id = B.po_id
  1010.                 AND C.session_id = pSessionId
  1011.                 AND C.receive_goods_id = A.receive_goods_id;
  1012.            
  1013.             RAISE NOTICE '20';
  1014.                
  1015.             INSERT INTO tt_journal_trx_item
  1016.             (session_id, tenant_id, journal_trx_id, line_no,
  1017.             ref_doc_type_id, ref_id,
  1018.             partner_id, product_id, cashbank_id, ou_rc_id,
  1019.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1020.             coa_id, curr_code, qty, uom_id,
  1021.             amount, journal_date, type_rate,
  1022.             numerator_rate, denominator_rate, journal_desc, remark)
  1023.             SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
  1024.                 A.doc_type_id, B.receive_goods_item_id,
  1025.                 A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
  1026.                 vEmptyId, CASE WHEN f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax) > 0 THEN vSignDebit ELSE vSignCredit END, vProductCOA, C.activity_gl_id,
  1027.                 C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
  1028.                 ABS(f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax)),
  1029.                 A.doc_date, vTypeRate,
  1030.                 1, 1, 'PRODUCT_STOCK', B.remark
  1031.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, tt_generate_po_rg_for_journal D
  1032.             WHERE A.receive_goods_id = B.receive_goods_id AND
  1033.                 A.receive_goods_id = D.receive_goods_id AND
  1034.                 B.ref_id = C.po_item_id AND
  1035.                 D.session_id = pSessionId;
  1036.                
  1037.             RAISE NOTICE '21';
  1038.            
  1039.             INSERT INTO gl_journal_trx_item
  1040.             (tenant_id, journal_trx_id, line_no,
  1041.             ref_doc_type_id, ref_id,
  1042.             partner_id, product_id, cashbank_id, ou_rc_id,
  1043.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1044.             coa_id, curr_code, qty, uom_id,
  1045.             amount, journal_date, type_rate,
  1046.             numerator_rate, denominator_rate, journal_desc, remark,
  1047.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1048.             SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  1049.                 A.ref_doc_type_id, A.ref_id,
  1050.                 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  1051.                 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  1052.                 A.coa_id, A.curr_code, A.qty, A.uom_id,
  1053.                 abs(A.amount), A.journal_date, A.type_rate,
  1054.                 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  1055.                 0, pDatetime, pUserId, pDatetime, pUserId
  1056.             FROM tt_journal_trx_item A
  1057.             WHERE A.session_id = pSessionId;
  1058.            
  1059.             RAISE NOTICE '22';
  1060.            
  1061.             -- UNTUK CREDIT
  1062.             INSERT INTO gl_journal_trx_mapping
  1063.             (tenant_id, journal_trx_id, line_no,
  1064.             ref_doc_type_id, ref_id,
  1065.             partner_id, product_id, cashbank_id, ou_rc_id,
  1066.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1067.             coa_id, curr_code, qty, uom_id,
  1068.             amount, journal_date, type_rate,
  1069.             numerator_rate, denominator_rate, journal_desc, remark,
  1070.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1071.             SELECT A.tenant_id, A.journal_trx_id, 1,
  1072.                 vEmptyId, vEmptyId,    
  1073.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  1074.                 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  1075.                 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  1076.                 abs(SUM(A.amount)), A.journal_date, A.type_rate,
  1077.                 1, 1, 'ACCR_AP', vEmptyValue,
  1078.                 0, pDatetime, pUserId, pDatetime, pUserId
  1079.             FROM tt_journal_trx_item A
  1080.             WHERE A.session_id = pSessionId
  1081.             AND A.sign_journal = vSignDebit
  1082.             GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  1083.            
  1084.             -- UNTUK DEBIT
  1085.             INSERT INTO gl_journal_trx_mapping
  1086.             (tenant_id, journal_trx_id, line_no,
  1087.             ref_doc_type_id, ref_id,
  1088.             partner_id, product_id, cashbank_id, ou_rc_id,
  1089.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1090.             coa_id, curr_code, qty, uom_id,
  1091.             amount, journal_date, type_rate,
  1092.             numerator_rate, denominator_rate, journal_desc, remark,
  1093.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1094.             SELECT A.tenant_id, A.journal_trx_id, 2,
  1095.                 vEmptyId, vEmptyId,    
  1096.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  1097.                 vEmptyId,vSignDebit, vSystemCOA, vEmptyId,
  1098.                 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  1099.                 abs(SUM(A.amount)), A.journal_date, A.type_rate,
  1100.                 1, 1, 'ACCR_AP', vEmptyValue,
  1101.                 0, pDatetime, pUserId, pDatetime, pUserId
  1102.             FROM tt_journal_trx_item A
  1103.             WHERE A.session_id = pSessionId
  1104.             AND A.sign_journal = vSignCredit
  1105.             GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  1106.            
  1107.             -- insert ke pu_po_generated_po_and_rg
  1108.             INSERT INTO pu_po_generated_po_and_rg (year_month, po_id, po_doc_no, po_doc_date, receive_goods_id, receive_goods_doc_no, receive_goods_doc_date)
  1109.             SELECT pPeriodProcess, A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date
  1110.             FROM tt_monthly_supplier_po_rg A
  1111.             WHERE A.session_id = pSessionId
  1112.             GROUP BY A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date;
  1113.            
  1114.             vStatusGenerate := 'S';
  1115.            
  1116.         ELSE
  1117.            
  1118.             vStatusGenerate := 'E';
  1119.             RAISE NOTICE 'TERKENA VALIDASI ERROR DOKUMEN PENJUALAN DARI MAGENTO, CEK HALAMAN REPROCESS MAGENTO ORDER';
  1120.        
  1121.         END IF;
  1122.    
  1123.     ELSE
  1124.         vStatusGenerate := 'E';
  1125.         RAISE NOTICE 'TERKENA VALIDASI DOKUMEN TRANSAKSI YANG BELUM DI APPROVED';
  1126.        
  1127.     END IF;
  1128.  
  1129.     DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
  1130.     DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
  1131.     DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
  1132.     DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
  1133.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  1134.    
  1135. END;
  1136. $BODY$
  1137.   LANGUAGE plpgsql VOLATILE
  1138.   COST 100;
  1139. /
Advertisement
Add Comment
Please, Sign In to add comment