widana

f_generate_po_and_rg_supplier_pkp_perubahan

Jan 26th, 2017
152
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.             /*
  203.              * insert data into table tr_report_sales_supplier for Return Note from in_log_product_balance_stock
  204.              */
  205.             EXECUTE '
  206.             INSERT INTO tr_report_sales_supplier(
  207.                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
  208.                    doc_date, payment_method, product_id, product_code, supplier_product_code,
  209.                    product_name, product_style, color, psize, normal_price, sold_price,
  210.                    discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
  211.             SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
  212.                    A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
  213.                    f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
  214.                    F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
  215.             FROM in_log_product_balance_stock A
  216.             INNER JOIN m_product_custom B ON A.product_id = B.product_id
  217.             INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
  218.             INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
  219.             INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
  220.             INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
  221.             INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
  222.             WHERE A.tenant_id = $2
  223.                 AND A.doc_date BETWEEN $5 AND $6
  224.                 AND A.ou_id = $3
  225.                 AND B.flg_buy_konsinyasi = $7
  226.                 AND A.doc_type_id IN ($9) ' ||
  227.                 vFilterPartner || '
  228.             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,
  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, vDocTypeDoReceipt, vDocTypeReturnNote;
  231.            
  232.             /*
  233.              * insert data into table tr_report_sales_supplier for DO Receipt 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_do_receipt D ON A.ref_id = D.do_receipt_id
  249.             INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
  250.             INNER JOIN sl_do_item E ON G.ref_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 ($8) ' ||
  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 Return Note from in_log_product_consignment_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_consignment_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_inventory D ON A.ref_id = D.inventory_id
  279.             INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
  280.             INNER JOIN sl_do_item E ON G.ref_item_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 ($9) ' ||
  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 DO Receipt 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_do_receipt D ON A.ref_id = D.do_receipt_id
  309.             INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
  310.             INNER JOIN sl_do_item E ON G.ref_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 ($8) ' ||
  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.             -- hanya yang supplier PKP saja, delete semua data partner yang non PKP
  323.             DELETE FROM tr_report_sales_supplier A
  324.             WHERE NOT EXISTS (
  325.                 SELECT 1 FROM m_partner_npwp B
  326.                 WHERE A.partner_id = B.partner_id
  327.                 AND B.flg_pkp = 'Y'
  328.             );
  329.            
  330.            
  331.             -------------------------------------------------------------------------------------------------
  332.             -- Buat summary penjualan & return untuk persiapan offset
  333.             -------------------------------------------------------------------------------------------------
  334.                    
  335.             INSERT INTO tt_summary_doc_sales_item_penjualan_by_period
  336.                 (session_id, supplier_id, record_type,
  337.                 product_id, product_balance_id,
  338.                 qty, gross_price_po, tax_percentage)
  339.             SELECT pSessionId, A.partner_id, 'SALES',
  340.                    A.product_id, A.product_balance_id,
  341.                    SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
  342.             FROM tr_report_sales_supplier A
  343.             INNER JOIN m_document B ON A.transaction_type = B.doc_desc
  344.             WHERE A.session_id = pSessionId
  345.             AND B.doc_type_id IN (vDocTypeDo)
  346.             GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
  347.                    
  348.             RAISE NOTICE '4';
  349.            
  350.             INSERT INTO tt_summary_doc_return_item_penjualan_by_period
  351.                 (session_id, supplier_id, record_type,
  352.                 product_id, product_balance_id,
  353.                 qty, gross_price_po, tax_percentage)
  354.             SELECT pSessionId, A.partner_id, 'RETURN',
  355.                    A.product_id, A.product_balance_id,
  356.                    SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
  357.             FROM tr_report_sales_supplier A
  358.             INNER JOIN m_document B ON A.transaction_type = B.doc_desc
  359.             WHERE A.session_id = pSessionId
  360.             AND B.doc_type_id IN (vDocTypeReturnNote, vDocTypeDoReceipt)
  361.             GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
  362.                    
  363.             RAISE NOTICE '5';
  364.            
  365.             WITH summary_item_sales AS
  366.             (
  367.                 SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
  368.                 FROM tt_summary_doc_sales_item_penjualan_by_period A
  369.                 WHERE A.session_id = pSessionId
  370.                 GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
  371.             ), summary_item_return AS
  372.             (
  373.                 SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
  374.                 FROM tt_summary_doc_return_item_penjualan_by_period A
  375.                 WHERE A.session_id = pSessionId
  376.                 GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
  377.             )
  378.             UPDATE tt_summary_doc_return_item_penjualan_by_period A
  379.             SET record_type = 'SALES'
  380.             WHERE EXISTS (
  381.                 SELECT 1 FROM summary_item_sales B
  382.                 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
  383.                 WHERE A.session_id = B.session_id
  384.                 AND A.supplier_id = B.supplier_id
  385.                 AND A.tax_percentage = B.tax_percentage
  386.                 AND A.product_id = B.product_id
  387.                 AND C.item_amount <= B.item_amount
  388.             ) AND A.session_id = pSessionId;
  389.            
  390.             INSERT INTO tt_summary_item_penjualan_by_period
  391.                 (session_id, supplier_id,  
  392.                 product_id, product_balance_id,
  393.                 qty, gross_price_po)
  394.             SELECT pSessionId, A.supplier_id,
  395.                    A.product_id, A.product_balance_id,
  396.                    A.qty, A.gross_price_po
  397.             FROM tt_summary_doc_sales_item_penjualan_by_period A
  398.             WHERE A.session_id = pSessionId;
  399.            
  400.             RAISE NOTICE '7';
  401.            
  402.             INSERT INTO tt_summary_item_penjualan_by_period
  403.                 (session_id, supplier_id,  
  404.                 product_id, product_balance_id,
  405.                 qty, gross_price_po)
  406.             SELECT pSessionId, A.supplier_id,
  407.                    A.product_id, A.product_balance_id,
  408.                    A.qty, A.gross_price_po
  409.             FROM tt_summary_doc_return_item_penjualan_by_period A
  410.             WHERE A.session_id = pSessionId
  411.             AND A.record_type = 'SALES';
  412.            
  413.             RAISE NOTICE '8';
  414.            
  415.             INSERT INTO tt_summary_item_return_by_period
  416.                 (session_id, supplier_id,  
  417.                 product_id, product_balance_id,
  418.                 qty, gross_price_po, tax_percentage)
  419.             SELECT pSessionId, A.supplier_id,
  420.                    A.product_id, A.product_balance_id,
  421.                    A.qty, A.gross_price_po, A.tax_percentage
  422.             FROM tt_summary_doc_return_item_penjualan_by_period A
  423.             WHERE A.session_id = pSessionId
  424.             AND A.record_type = 'RETURN';
  425.            
  426.             INSERT INTO tt_monthly_supplier_po_rg(
  427.                 session_id, supplier_id,  
  428.                 po_id, po_no, po_date,
  429.                 receive_goods_id, receive_goods_no, receive_goods_date
  430.             )
  431.             SELECT pSessionId, A.supplier_id,
  432.                    nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
  433.                    nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
  434.             FROM tt_summary_item_penjualan_by_period A
  435.             WHERE A.session_id = pSessionId
  436.             GROUP BY A.supplier_id;
  437.            
  438.             INSERT INTO tt_monthly_supplier_po_rg_return(
  439.                 session_id, supplier_id,
  440.                 po_id, po_no, po_date,
  441.                 receive_goods_id, receive_goods_no, receive_goods_date
  442.             )
  443.             SELECT pSessionId, A.supplier_id,
  444.                    nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
  445.                    nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
  446.             FROM tt_summary_item_return_by_period A
  447.             WHERE A.session_id = pSessionId
  448.             GROUP BY A.supplier_id;
  449.            
  450.             UPDATE tt_monthly_supplier_po_rg A
  451.             SET po_no = vDocNoDefaultPo||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
  452.                 receive_goods_no = vDocNoDefaultRg||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
  453.             WHERE A.session_id = pSessionId;
  454.            
  455.             UPDATE tt_monthly_supplier_po_rg_return A
  456.             SET po_no = vDocNoDefaultPoReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
  457.                 receive_goods_no = vDocNoDefaultRgReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
  458.             WHERE A.session_id = pSessionId;
  459.            
  460.            
  461.            
  462.             -- UPDATE po_date berdasarkan data penjualan minimum
  463.             WITH summary_item_penjualan AS (
  464.                 SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
  465.                 WHERE A.session_id = pSessionId
  466.                 GROUP BY A.session_id, A.partner_id
  467.             )
  468.             UPDATE tt_monthly_supplier_po_rg A
  469.             SET po_date = B.doc_date, receive_goods_date = B.doc_date
  470.             FROM summary_item_penjualan B
  471.             WHERE B.session_id = A.session_id
  472.             AND A.session_id = pSessionId
  473.             AND B.partner_id = A.supplier_id;
  474.            
  475.             WITH summary_item_penjualan AS (
  476.                 SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
  477.                 WHERE A.session_id = pSessionId
  478.                 GROUP BY A.session_id, A.partner_id
  479.             )
  480.             UPDATE tt_monthly_supplier_po_rg_return A
  481.             SET po_date = B.doc_date, receive_goods_date = B.doc_date
  482.             FROM summary_item_penjualan B
  483.             WHERE B.session_id = A.session_id
  484.             AND A.session_id = pSessionId
  485.             AND B.partner_id = A.supplier_id;
  486.            
  487.            
  488.             -- UPDATE jika po_datenya NULL
  489.             UPDATE tt_monthly_supplier_po_rg A
  490.             SET po_date = pStartDate, receive_goods_date = pStartDate
  491.             WHERE A.session_id = pSessionId
  492.             AND po_date IS NULL;
  493.            
  494.             UPDATE tt_monthly_supplier_po_rg_return A
  495.             SET po_date = pStartDate, receive_goods_date = pStartDate
  496.             WHERE A.session_id = pSessionId
  497.             AND po_date IS NULL;
  498.            
  499.            
  500.            
  501.             UPDATE pu_po_balance_item_consignment_sold A
  502.             SET flg_process = vFlagYes, ref_doc_type_id = vDocTypeIdRg, ref_id = -99,  
  503.                 version = A.version + 1, update_user_id = pUserId, update_datetime = pDatetime
  504.             FROM tt_monthly_supplier_po_rg B
  505.             WHERE B.session_id = pSessionId
  506.             AND B.supplier_id = A.supplier_id
  507.             AND A.ref_doc_type_id = -99 AND A.ref_id = -99
  508.             AND EXISTS (
  509.                 SELECT 1
  510.                 FROM tr_report_sales_supplier C
  511.                 WHERE C.session_id = pSessionId
  512.                 AND C.so_item_id = A.so_item_id
  513.                 AND C.tenant_id = A.tenant_id
  514.                 AND C.partner_id = A.supplier_id
  515.             );
  516.            
  517.             -------------------------------------------------------------------------------------------------
  518.             -- Buat data PO
  519.             -------------------------------------------------------------------------------------------------
  520.             INSERT INTO pu_po
  521.                     (po_id, tenant_id, doc_type_id, doc_no,
  522.                     doc_date, ou_id, ext_doc_no, ext_doc_date,
  523.                     ref_doc_type_id, ref_id, remark,
  524.                     partner_id, purchaser_id, warehouse_id,
  525.                     flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
  526.                     top_code, status_doc, workflow_status,
  527.                     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  528.             SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
  529.                 A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
  530.                 vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
  531.                 A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
  532.                 vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
  533.                 vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
  534.                 0, pDatetime, pUserId, pDatetime, pUserId
  535.             FROM tt_monthly_supplier_po_rg A
  536.             WHERE A.session_id = pSessionId
  537.             GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
  538.            
  539.             INSERT INTO pu_po
  540.                     (po_id, tenant_id, doc_type_id, doc_no,
  541.                     doc_date, ou_id, ext_doc_no, ext_doc_date,
  542.                     ref_doc_type_id, ref_id, remark,
  543.                     partner_id, purchaser_id, warehouse_id,
  544.                     flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
  545.                     top_code, status_doc, workflow_status,
  546.                     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  547.             SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
  548.                 A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
  549.                 vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
  550.                 A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
  551.                 vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
  552.                 vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
  553.                 0, pDatetime, pUserId, pDatetime, pUserId
  554.             FROM tt_monthly_supplier_po_rg_return A
  555.             WHERE A.session_id = pSessionId
  556.             GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
  557.            
  558.             RAISE NOTICE '7';
  559.            
  560.             INSERT INTO pu_po_item
  561.                 (tenant_id, po_id, line_no,
  562.                 ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
  563.                 curr_code, gross_price_po,
  564.                 flg_tax_amount, tax_id, tax_percentage,
  565.                 tax_price,
  566.                 nett_price_po,
  567.                 qty_po, po_uom_id, qty_int, base_uom_id,
  568.                 discount_percentage, discount_amount, gross_item_amount,
  569.                 nett_item_amount, tax_amount,
  570.                 activity_gl_id, product_coa_id, ou_rc_id, eta,
  571.                 tolerance_rcv_qty, remark, version,
  572.                 create_datetime, create_user_id, update_datetime, update_user_id,
  573.                 segment_id, eta_day, flg_indent)
  574.             SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
  575.                    vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
  576.                    vCurrCode, A.gross_price_po,
  577.                    vFlagYes, vTaxId, vTaxPercentage,
  578.                    f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
  579.                    f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
  580.                    SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
  581.                    0, 0, 0,
  582.                    0, 0,
  583.                    vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
  584.                    0, vEmptyValue, 0,
  585.                    pDatetime, pUserId, pDatetime, pUserId,
  586.                    vEmptyId, vEmptyValue, vFlagNo
  587.             FROM tt_summary_item_penjualan_by_period A
  588.             INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  589.             WHERE A.session_id = pSessionId
  590.             GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
  591.            
  592.             INSERT INTO pu_po_item
  593.                 (tenant_id, po_id, line_no,
  594.                 ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
  595.                 curr_code, gross_price_po,
  596.                 flg_tax_amount, tax_id, tax_percentage,
  597.                 tax_price,
  598.                 nett_price_po,
  599.                 qty_po, po_uom_id, qty_int, base_uom_id,
  600.                 discount_percentage, discount_amount, gross_item_amount,
  601.                 nett_item_amount, tax_amount,
  602.                 activity_gl_id, product_coa_id, ou_rc_id, eta,
  603.                 tolerance_rcv_qty, remark, version,
  604.                 create_datetime, create_user_id, update_datetime, update_user_id,
  605.                 segment_id, eta_day, flg_indent)
  606.             SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
  607.                    vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
  608.                    vCurrCode, A.gross_price_po,
  609.                    vFlagYes, vTaxId, vTaxPercentage,
  610.                    f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
  611.                    f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
  612.                    SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
  613.                    0, 0, 0,
  614.                    0, 0,
  615.                    vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
  616.                    0, vEmptyValue, 0,
  617.                    pDatetime, pUserId, pDatetime, pUserId,
  618.                    vEmptyId, vEmptyValue, vFlagNo
  619.             FROM tt_summary_item_return_by_period A
  620.             INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  621.             WHERE A.session_id = pSessionId
  622.             GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
  623.  
  624.             RAISE NOTICE '8';
  625.            
  626.             UPDATE pu_po_item A
  627.             SET gross_item_amount = A.gross_price_po * A.qty_po,
  628.                 nett_item_amount = A.nett_price_po * A.qty_po,
  629.                 tax_amount = A.tax_price * A.qty_po
  630.             WHERE EXISTS (
  631.                 SELECT 1 FROM tt_monthly_supplier_po_rg B
  632.                 WHERE B.session_id = pSessionId
  633.                 AND B.po_id = A.po_id
  634.             );
  635.            
  636.             UPDATE pu_po_item A
  637.             SET gross_item_amount = A.gross_price_po * A.qty_po,
  638.                 nett_item_amount = A.nett_price_po * A.qty_po,
  639.                 tax_amount = A.tax_price * A.qty_po
  640.             WHERE EXISTS (
  641.                 SELECT 1 FROM tt_monthly_supplier_po_rg_return B
  642.                 WHERE B.session_id = pSessionId
  643.                 AND B.po_id = A.po_id
  644.             );
  645.            
  646.             RAISE NOTICE '9';
  647.            
  648.             INSERT INTO pu_po_balance_item
  649.             (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
  650.              qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
  651.              tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
  652.             SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
  653.                     A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
  654.                     A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
  655.             FROM pu_po_item A, pu_po B
  656.             WHERE A.po_id = B.po_id
  657.             AND EXISTS (
  658.                 SELECT 1 FROM tt_monthly_supplier_po_rg C
  659.                 WHERE C.session_id = pSessionId
  660.                 AND C.po_id = A.po_id
  661.             );
  662.            
  663.             INSERT INTO pu_po_balance_item
  664.             (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
  665.              qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
  666.              tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
  667.             SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
  668.                     A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
  669.                     A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
  670.             FROM pu_po_item A, pu_po B
  671.             WHERE A.po_id = B.po_id
  672.             AND EXISTS (
  673.                 SELECT 1 FROM tt_monthly_supplier_po_rg_return C
  674.                 WHERE C.session_id = pSessionId
  675.                 AND C.po_id = A.po_id
  676.             );
  677.            
  678.             RAISE NOTICE '10';
  679.              
  680.             INSERT INTO pu_log_po_balance_item
  681.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  682.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  683.             version, create_datetime, create_user_id, update_datetime, update_user_id)
  684.             SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
  685.                 A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
  686.                 0, pDatetime, pUserId, pDatetime, pUserId
  687.             FROM pu_po_item A, pu_po B
  688.             WHERE A.po_id = B.po_id
  689.             AND EXISTS(
  690.                 SELECT 1 FROM tt_monthly_supplier_po_rg C
  691.                 WHERE C.session_id = pSessionId
  692.                 AND C.po_id = A.po_id
  693.             );
  694.            
  695.             INSERT INTO pu_log_po_balance_item
  696.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  697.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  698.             version, create_datetime, create_user_id, update_datetime, update_user_id)
  699.             SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
  700.                 A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
  701.                 0, pDatetime, pUserId, pDatetime, pUserId
  702.             FROM pu_po_item A, pu_po B
  703.             WHERE A.po_id = B.po_id
  704.             AND EXISTS(
  705.                 SELECT 1 FROM tt_monthly_supplier_po_rg_return C
  706.                 WHERE C.session_id = pSessionId
  707.                 AND C.po_id = A.po_id
  708.             );
  709.            
  710.             RAISE NOTICE '11';
  711.            
  712.             -------------------------------------------------------------------------------------------------
  713.             -- Buat data Receive Goods
  714.             -------------------------------------------------------------------------------------------------
  715.            
  716.             INSERT INTO pu_receive_goods
  717.                 (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
  718.                 ou_id, ext_doc_no, ext_doc_date,
  719.                 ref_doc_type_id, ref_id,
  720.                 remark, partner_id, warehouse_id, status_doc, workflow_status,
  721.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  722.             SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
  723.                 pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
  724.                 vDocTypeIdPo, A.po_id,
  725.                 vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
  726.                 vStatusRelease, vWorkflowStatusApproved,
  727.                 0, pDatetime, pUserId, pDatetime, pUserId
  728.             FROM tt_monthly_supplier_po_rg A
  729.             WHERE A.session_id = pSessionId
  730.             GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
  731.            
  732.             -- menambahkan untuk generate po and rg, RGnya harus tercatat di pu_po_rg_generated
  733.             INSERT INTO pu_po_rg_generated (doc_type_id, ref_id, doc_no, partner_id,
  734.                 create_datetime, create_user_id,
  735.                 update_datetime, update_user_id,
  736.                 version)
  737.             SELECT vDocTypeIdRg, A.receive_goods_id,  A.receive_goods_no, A.supplier_id,
  738.                 pDatetime, pUserId,
  739.                 pDatetime, pUserId,
  740.                 0
  741.             FROM tt_monthly_supplier_po_rg A
  742.             WHERE A.session_id = pSessionId
  743.             GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;     
  744.            
  745.             INSERT INTO pu_receive_goods
  746.                 (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
  747.                 ou_id, ext_doc_no, ext_doc_date,
  748.                 ref_doc_type_id, ref_id,
  749.                 remark, partner_id, warehouse_id, status_doc, workflow_status,
  750.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  751.             SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
  752.                 pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
  753.                 vDocTypeIdPo, A.po_id,
  754.                 vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
  755.                 vStatusRelease, vWorkflowStatusApproved,
  756.                 0, pDatetime, pUserId, pDatetime, pUserId
  757.             FROM tt_monthly_supplier_po_rg_return A
  758.             WHERE A.session_id = pSessionId
  759.             GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
  760.            
  761.            
  762.             RAISE NOTICE '12';
  763.                
  764.             INSERT INTO pu_receive_goods_item
  765.                 (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
  766.                  product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
  767.                  remark,
  768.                  version, create_datetime, create_user_id, update_datetime, update_user_id)
  769.             SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
  770.                    C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
  771.                    vEmptyValue,
  772.                    0, pDatetime, pUserId, pDatetime, pUserId
  773.             FROM tt_monthly_supplier_po_rg A
  774.             INNER JOIN pu_po B ON A.po_id = B.po_id
  775.             INNER JOIN pu_po_item C ON B.po_id = C.po_id
  776.             WHERE A.session_id = pSessionId;
  777.  
  778.            
  779.             INSERT INTO pu_receive_goods_item
  780.                 (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
  781.                  product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
  782.                  remark,
  783.                  version, create_datetime, create_user_id, update_datetime, update_user_id)
  784.             SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
  785.                    C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
  786.                    vEmptyValue,
  787.                    0, pDatetime, pUserId, pDatetime, pUserId
  788.             FROM tt_monthly_supplier_po_rg_return A
  789.             INNER JOIN pu_po B ON A.po_id = B.po_id
  790.             INNER JOIN pu_po_item C ON B.po_id = C.po_id
  791.             WHERE A.session_id = pSessionId;
  792.            
  793.             RAISE NOTICE '13';
  794.            
  795.             INSERT INTO pu_log_po_balance_item
  796.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  797.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  798.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  799.             SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
  800.                 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
  801.                 0, pDatetime, pUserId, pDatetime, pUserId
  802.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  803.             WHERE A.receive_goods_id = B.receive_goods_id AND
  804.                   B.ref_id = C.po_item_id AND EXISTS (
  805.                         SELECT 1 FROM tt_monthly_supplier_po_rg D
  806.                         WHERE D.receive_goods_id = A.receive_goods_id
  807.                         AND D.session_id = pSessionId
  808.                   );
  809.                  
  810.             INSERT INTO pu_log_po_balance_item
  811.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  812.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  813.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  814.             SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
  815.                 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
  816.                 0, pDatetime, pUserId, pDatetime, pUserId
  817.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  818.             WHERE A.receive_goods_id = B.receive_goods_id AND
  819.                   B.ref_id = C.po_item_id AND EXISTS (
  820.                         SELECT 1 FROM tt_monthly_supplier_po_rg_return D
  821.                         WHERE D.receive_goods_id = A.receive_goods_id
  822.                         AND D.session_id = pSessionId
  823.                   );
  824.            
  825.             RAISE NOTICE '14';
  826.                  
  827.             INSERT INTO pu_po_balance_invoice
  828.             (tenant_id, ou_id, partner_id, po_id,
  829.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  830.             curr_code, price_po, item_amount, flg_invoice, invoice_id,
  831.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  832.             SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  833.                 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,
  834.                 C.curr_code, C.nett_price_po,
  835.                 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),
  836.                 vFlagNo, vEmptyId,
  837.                 0, pDatetime, pUserId, pDatetime, pUserId
  838.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  839.             WHERE A.receive_goods_id = B.receive_goods_id AND
  840.                   B.ref_id = C.po_item_id AND EXISTS (
  841.                         SELECT 1 FROM tt_monthly_supplier_po_rg D
  842.                         WHERE D.receive_goods_id = A.receive_goods_id
  843.                         AND D.session_id = pSessionId
  844.                   );
  845.                  
  846.             INSERT INTO pu_po_balance_invoice
  847.             (tenant_id, ou_id, partner_id, po_id,
  848.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  849.             curr_code, price_po, item_amount, flg_invoice, invoice_id,
  850.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  851.             SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  852.                 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,
  853.                 C.curr_code, C.nett_price_po,
  854.                 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),
  855.                 vFlagNo, vEmptyId,
  856.                 0, pDatetime, pUserId, pDatetime, pUserId
  857.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  858.             WHERE A.receive_goods_id = B.receive_goods_id AND
  859.                   B.ref_id = C.po_item_id AND EXISTS (
  860.                         SELECT 1 FROM tt_monthly_supplier_po_rg_return D
  861.                         WHERE D.receive_goods_id = A.receive_goods_id
  862.                         AND D.session_id = pSessionId
  863.                   );
  864.                  
  865.             INSERT INTO pu_po_balance_invoice_tax
  866.         (tenant_id, ou_id, partner_id, po_id,
  867.         ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  868.         tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  869.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  870.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  871.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
  872.             C.tax_percentage, C.curr_code,
  873.             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),
  874.             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),
  875.             vFlagNo, vEmptyId,
  876.             0, pDatetime, pUserId, pDatetime, pUserId
  877.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
  878.         WHERE A.receive_goods_id = B.receive_goods_id AND
  879.               B.ref_id = C.po_item_id AND
  880.               C.tax_id = D.tax_id AND EXISTS (
  881.                     SELECT 1 FROM tt_monthly_supplier_po_rg D
  882.                     WHERE D.receive_goods_id = A.receive_goods_id
  883.                     AND D.session_id = pSessionId
  884.               );
  885.              
  886.               RAISE NOTICE '32';
  887.              
  888.         INSERT INTO pu_po_balance_invoice_tax
  889.         (tenant_id, ou_id, partner_id, po_id,
  890.         ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  891.         tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  892.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  893.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  894.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
  895.             C.tax_percentage, C.curr_code,
  896.             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),
  897.             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),
  898.             vFlagNo, vEmptyId,
  899.             0, pDatetime, pUserId, pDatetime, pUserId
  900.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
  901.         WHERE A.receive_goods_id = B.receive_goods_id AND
  902.               B.ref_id = C.po_item_id AND
  903.               C.tax_id = D.tax_id AND EXISTS (
  904.                     SELECT 1 FROM tt_monthly_supplier_po_rg_return D
  905.                     WHERE D.receive_goods_id = A.receive_goods_id
  906.                     AND D.session_id = pSessionId
  907.               );
  908.              
  909.               RAISE NOTICE '33';
  910.                  
  911.             -------------------------------------------------------------------------------------------------
  912.             -- Buat data log product balance stock
  913.             -------------------------------------------------------------------------------------------------      
  914.                  
  915.             INSERT INTO in_log_product_balance_stock
  916.             (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  917.              product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  918.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  919.             SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
  920.                    A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(A.qty),
  921.                    0, pDatetime, pUserId, pDatetime, pUserId
  922.             FROM tt_summary_item_penjualan_by_period A
  923.             INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  924.             WHERE A.session_id = pSessionId
  925.             GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
  926.            
  927.             INSERT INTO in_log_product_balance_stock
  928.             (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  929.              product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  930.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  931.             SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
  932.                    A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(-1*A.qty),
  933.                    0, pDatetime, pUserId, pDatetime, pUserId
  934.             FROM tt_summary_item_penjualan_by_period A
  935.             INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
  936.             WHERE A.session_id = pSessionId
  937.             GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
  938.            
  939.             -------------------------------------------------------------------------------------------------
  940.             -- Buat data jurnal penerimaan barang
  941.             -------------------------------------------------------------------------------------------------
  942.            
  943.             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)
  944.             FROM pu_receive_goods A
  945.             WHERE EXISTS (
  946.                 SELECT 1 FROM tt_monthly_supplier_po_rg E
  947.                 WHERE E.session_id = pSessionId
  948.                 AND A.receive_goods_id = E.receive_goods_id
  949.             );
  950.            
  951.             RAISE NOTICE '18';
  952.            
  953.             INSERT INTO tt_generate_po_rg_for_journal
  954.                 (session_id, receive_goods_id, journal_trx_id)
  955.             SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
  956.             FROM tt_monthly_supplier_po_rg A
  957.             WHERE A.session_id = pSessionId
  958.             GROUP BY A.receive_goods_id;
  959.            
  960.             INSERT INTO tt_generate_po_rg_for_journal
  961.                 (session_id, receive_goods_id, journal_trx_id)
  962.             SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
  963.             FROM tt_monthly_supplier_po_rg_return A
  964.             WHERE A.session_id = pSessionId
  965.             GROUP BY A.receive_goods_id;
  966.            
  967.             RAISE NOTICE '19';
  968.            
  969.             INSERT INTO gl_journal_trx
  970.             (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  971.             ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  972.             ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  973.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  974.             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,
  975.                 (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,
  976.                 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
  977.                 0, pDatetime, pUserId, pDatetime, pUserId
  978.             FROM pu_receive_goods A, pu_po B, tt_generate_po_rg_for_journal C
  979.             WHERE A.ref_doc_type_id = B.doc_type_id
  980.                 AND A.ref_id = B.po_id
  981.                 AND C.session_id = pSessionId
  982.                 AND C.receive_goods_id = A.receive_goods_id;
  983.            
  984.             RAISE NOTICE '20';
  985.                
  986.             INSERT INTO tt_journal_trx_item
  987.             (session_id, tenant_id, journal_trx_id, line_no,
  988.             ref_doc_type_id, ref_id,
  989.             partner_id, product_id, cashbank_id, ou_rc_id,
  990.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  991.             coa_id, curr_code, qty, uom_id,
  992.             amount, journal_date, type_rate,
  993.             numerator_rate, denominator_rate, journal_desc, remark)
  994.             SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
  995.                 A.doc_type_id, B.receive_goods_item_id,
  996.                 A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
  997.                 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,
  998.                 C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
  999.                 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)),
  1000.                 A.doc_date, vTypeRate,
  1001.                 1, 1, 'PRODUCT_STOCK', B.remark
  1002.             FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, tt_generate_po_rg_for_journal D
  1003.             WHERE A.receive_goods_id = B.receive_goods_id AND
  1004.                 A.receive_goods_id = D.receive_goods_id AND
  1005.                 B.ref_id = C.po_item_id AND
  1006.                 D.session_id = pSessionId;
  1007.                
  1008.             RAISE NOTICE '21';
  1009.            
  1010.             INSERT INTO gl_journal_trx_item
  1011.             (tenant_id, journal_trx_id, line_no,
  1012.             ref_doc_type_id, ref_id,
  1013.             partner_id, product_id, cashbank_id, ou_rc_id,
  1014.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1015.             coa_id, curr_code, qty, uom_id,
  1016.             amount, journal_date, type_rate,
  1017.             numerator_rate, denominator_rate, journal_desc, remark,
  1018.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1019.             SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  1020.                 A.ref_doc_type_id, A.ref_id,
  1021.                 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  1022.                 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  1023.                 A.coa_id, A.curr_code, A.qty, A.uom_id,
  1024.                 abs(A.amount), A.journal_date, A.type_rate,
  1025.                 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  1026.                 0, pDatetime, pUserId, pDatetime, pUserId
  1027.             FROM tt_journal_trx_item A
  1028.             WHERE A.session_id = pSessionId;
  1029.            
  1030.             RAISE NOTICE '22';
  1031.            
  1032.             -- UNTUK CREDIT
  1033.             INSERT INTO gl_journal_trx_mapping
  1034.             (tenant_id, journal_trx_id, line_no,
  1035.             ref_doc_type_id, ref_id,
  1036.             partner_id, product_id, cashbank_id, ou_rc_id,
  1037.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1038.             coa_id, curr_code, qty, uom_id,
  1039.             amount, journal_date, type_rate,
  1040.             numerator_rate, denominator_rate, journal_desc, remark,
  1041.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1042.             SELECT A.tenant_id, A.journal_trx_id, 1,
  1043.                 vEmptyId, vEmptyId,    
  1044.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  1045.                 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  1046.                 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  1047.                 abs(SUM(A.amount)), A.journal_date, A.type_rate,
  1048.                 1, 1, 'ACCR_AP', vEmptyValue,
  1049.                 0, pDatetime, pUserId, pDatetime, pUserId
  1050.             FROM tt_journal_trx_item A
  1051.             WHERE A.session_id = pSessionId
  1052.             AND A.sign_journal = vSignDebit
  1053.             GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  1054.            
  1055.             -- UNTUK DEBIT
  1056.             INSERT INTO gl_journal_trx_mapping
  1057.             (tenant_id, journal_trx_id, line_no,
  1058.             ref_doc_type_id, ref_id,
  1059.             partner_id, product_id, cashbank_id, ou_rc_id,
  1060.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1061.             coa_id, curr_code, qty, uom_id,
  1062.             amount, journal_date, type_rate,
  1063.             numerator_rate, denominator_rate, journal_desc, remark,
  1064.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1065.             SELECT A.tenant_id, A.journal_trx_id, 2,
  1066.                 vEmptyId, vEmptyId,    
  1067.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  1068.                 vEmptyId,vSignDebit, vSystemCOA, vEmptyId,
  1069.                 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  1070.                 abs(SUM(A.amount)), A.journal_date, A.type_rate,
  1071.                 1, 1, 'ACCR_AP', vEmptyValue,
  1072.                 0, pDatetime, pUserId, pDatetime, pUserId
  1073.             FROM tt_journal_trx_item A
  1074.             WHERE A.session_id = pSessionId
  1075.             AND A.sign_journal = vSignCredit
  1076.             GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  1077.            
  1078.             -- insert ke pu_po_generated_po_and_rg
  1079.             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)
  1080.             SELECT pPeriodProcess, A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date
  1081.             FROM tt_monthly_supplier_po_rg A
  1082.             WHERE A.session_id = pSessionId
  1083.             GROUP BY A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date;
  1084.                    
  1085.             vStatusGenerate := 'S';
  1086.            
  1087.         ELSE
  1088.            
  1089.             vStatusGenerate := 'E';
  1090.             RAISE NOTICE 'TERKENA VALIDASI ERROR DOKUMEN PENJUALAN DARI MAGENTO, CEK HALAMAN REPROCESS MAGENTO ORDER';
  1091.        
  1092.         END IF;
  1093.    
  1094.     ELSE
  1095.         vStatusGenerate := 'E';
  1096.         RAISE NOTICE 'TERKENA VALIDASI DOKUMEN TRANSAKSI YANG BELUM DI APPROVED';
  1097.        
  1098.     END IF;
  1099.  
  1100.     DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
  1101.     DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
  1102.     DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
  1103.     DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
  1104.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  1105.    
  1106. END;
  1107. $BODY$
  1108.   LANGUAGE plpgsql VOLATILE
  1109.   COST 100;
  1110. /
Advertisement
Add Comment
Please, Sign In to add comment