widana

f_generate_po_and_rg_supplier_pkp

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