Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION f_generate_po_and_rg_supplier_pkp(bigint, bigint, character varying, character varying, character varying, character varying, bigint, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pOuId ALIAS FOR $2;
- pSessionId ALIAS FOR $3;
- pStartDate ALIAS FOR $4;
- pEndDate ALIAS FOR $5;
- pPeriodProcess ALIAS FOR $6;
- pUserId ALIAS FOR $7;
- pDatetime ALIAS FOR $8;
- vEmptyId BIGINT := -99;
- vEmptyString CHARACTER VARYING := '';
- vEmptyNumeric NUMERIC := 0;
- vReleaseDoc CHARACTER VARYING := 'R';
- vStock CHARACTER VARYING := 'Y';
- vFilterPartner TEXT := '';
- vDocTypeDo BIGINT := 311;
- vDocTypeDoReceipt BIGINT := 526;
- vDocTypeReturnNote BIGINT := 502;
- vYes CHARACTER VARYING := 'Y';
- vWarehouseId bigint;
- vPurchaserId bigint;
- vRoundingModeNonTax character varying(5);
- vCurrCode character varying := ' ';
- vProductStatus character varying := ' ';
- vTopCode character varying := ' ';
- vStatusDraft character varying := 'D';
- vStatusRelease character varying := 'R';
- vStatusFinal character varying := 'F';
- vFlagYes character varying := 'Y';
- vFlagNo character varying := 'N';
- vSignDebit character varying := 'D';
- vSignCredit character varying := 'C';
- vActivityCOA character varying := 'ACTIVITY';
- vProductCOA character varying := 'PRODUCT';
- vSystemCOA character varying := 'SYSTEM';
- vTypeRate character varying := 'COM';
- vDocTypeIdPo bigint := 101;
- vDocTypeIdRg bigint := 111;
- vDocTypeIdPoCommSheet bigint := 106;
- vDocNoDefaultPo character varying := ' ';
- vDocNoDefaultRg character varying := ' ';
- vDocNoDefaultPoReturn character varying;
- vDocNoDefaultRgReturn character varying;
- vPrefixDocNoVatNormal character varying := '010';
- vWorkflowStatusApproved character varying(10) := 'APPROVED';
- vRemarkPo character varying := 'PO Auto Generated';
- vRemarkRg character varying := 'Receive Goods Auto Generated';
- vEmptyValue character varying := ' ';
- vValReleasedTrx bigint;
- vValMagento bigint;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vYearMonth character varying(7);
- vProcessName character varying := 'pu_create_doc_buy_from_consignment_sold';
- vStatusGenerate character varying := 'S';
- vTaxPercentage numeric;
- vTaxId bigint;
- BEGIN
- DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
- DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
- DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
- DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- -------------------------------------------------------------------------------------------------
- -- Ambil nilai-nilai untuk variabel-variabel tertentu
- -------------------------------------------------------------------------------------------------
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'WHS.RECEIVE') INTO vWarehouseId;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'PURCHASE.OFFICER.ID') INTO vPurchaserId;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vCurrCode;
- SELECT product_status_code INTO vProductStatus FROM m_product_status WHERE flg_buy = 'Y'; -- product status
- SELECT prop_key INTO vTopCode FROM t_combo_value WHERE combo_id = 'TOPTYPE' AND code = 'TOP'; -- top
- SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId AND tax_code = 'PPn';
- SELECT concat('PO/', pPeriodProcess, '/') INTO vDocNoDefaultPo;
- SELECT concat('GR/', pPeriodProcess, '/') INTO vDocNoDefaultRg;
- SELECT concat('POR/', pPeriodProcess, '/') INTO vDocNoDefaultPoReturn;
- SELECT concat('GRR/', pPeriodProcess, '/') INTO vDocNoDefaultRgReturn;
- SELECT f_get_ou_bu_structure(pOuId) AS ou, f_get_document_journal(vDocTypeIdRg) AS doc INTO result;
- vDocJournal := result.doc;
- vOuStructure := result.ou;
- SELECT val_unsubmit_trx_generate_po_rg_for_cland(pTenantId, pSessionId, pOuId, pPeriodProcess, pStartDate, pEndDate, pDatetime, pUserId, vProcessName) INTO vValReleasedTrx;
- SELECT val_check_error_magento_trx_for_generate_po_rg_for_cland(pTenantId, pSessionId, pOuId, pDatetime, pUserId, vProcessName, pStartDate, pEndDate) INTO vValMagento;
- IF vValReleasedTrx = 0 THEN
- IF vValMagento = 0 THEN
- /*
- * insert data into table tr_report_sales_supplier for DO from in_log_product_balance_stock
- */
- EXECUTE '
- INSERT INTO tr_report_sales_supplier(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id = $8 ' ||
- vFilterPartner || '
- AND F.ref_doc_type_id = -99 AND F.ref_id = -99
- 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,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo;
- /*
- * insert data into table tr_report_sales_supplier for DO from in_log_product_consignment_balance_stock
- */
- EXECUTE '
- INSERT INTO tr_report_sales_supplier(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id = $8 ' ||
- vFilterPartner || '
- AND F.ref_doc_type_id = -99 AND F.ref_id = -99
- 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,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo;
- /*
- * insert data into table tr_report_sales_supplier for Return Note from in_log_product_balance_stock
- */
- EXECUTE '
- INSERT INTO tr_report_sales_supplier(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
- INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($9) ' ||
- vFilterPartner || '
- 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,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
- /*
- * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_balance_stock
- */
- EXECUTE '
- INSERT INTO tr_report_sales_supplier(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8) ' ||
- vFilterPartner || '
- 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,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
- /*
- * insert data into table tr_report_sales_supplier for Return Note from in_log_product_consignment_balance_stock
- */
- EXECUTE '
- INSERT INTO tr_report_sales_supplier(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
- INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($9) ' ||
- vFilterPartner || '
- 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,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
- /*
- * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_consignment_balance_stock
- */
- EXECUTE '
- INSERT INTO tr_report_sales_supplier(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8) ' ||
- vFilterPartner || '
- 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,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote;
- -- hanya yang supplier PKP saja, delete semua data partner yang non PKP
- DELETE FROM tr_report_sales_supplier A
- WHERE NOT EXISTS (
- SELECT 1 FROM m_partner_npwp B
- WHERE A.partner_id = B.partner_id
- AND B.flg_pkp = 'Y'
- );
- -------------------------------------------------------------------------------------------------
- -- Buat summary penjualan & return untuk persiapan offset
- -------------------------------------------------------------------------------------------------
- INSERT INTO tt_summary_doc_sales_item_penjualan_by_period
- (session_id, supplier_id, record_type,
- product_id, product_balance_id,
- qty, gross_price_po, tax_percentage)
- SELECT pSessionId, A.partner_id, 'SALES',
- A.product_id, A.product_balance_id,
- SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
- FROM tr_report_sales_supplier A
- INNER JOIN m_document B ON A.transaction_type = B.doc_desc
- WHERE A.session_id = pSessionId
- AND B.doc_type_id IN (vDocTypeDo)
- GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
- RAISE NOTICE '4';
- INSERT INTO tt_summary_doc_return_item_penjualan_by_period
- (session_id, supplier_id, record_type,
- product_id, product_balance_id,
- qty, gross_price_po, tax_percentage)
- SELECT pSessionId, A.partner_id, 'RETURN',
- A.product_id, A.product_balance_id,
- SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
- FROM tr_report_sales_supplier A
- INNER JOIN m_document B ON A.transaction_type = B.doc_desc
- WHERE A.session_id = pSessionId
- AND B.doc_type_id IN (vDocTypeReturnNote, vDocTypeDoReceipt)
- GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
- RAISE NOTICE '5';
- WITH summary_item_sales AS
- (
- SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
- FROM tt_summary_doc_sales_item_penjualan_by_period A
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
- ), summary_item_return AS
- (
- SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
- FROM tt_summary_doc_return_item_penjualan_by_period A
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
- )
- UPDATE tt_summary_doc_return_item_penjualan_by_period A
- SET record_type = 'SALES'
- WHERE EXISTS (
- SELECT 1 FROM summary_item_sales B
- 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
- WHERE A.session_id = B.session_id
- AND A.supplier_id = B.supplier_id
- AND A.tax_percentage = B.tax_percentage
- AND A.product_id = B.product_id
- AND C.item_amount <= B.item_amount
- ) AND A.session_id = pSessionId;
- INSERT INTO tt_summary_item_penjualan_by_period
- (session_id, supplier_id,
- product_id, product_balance_id,
- qty, gross_price_po)
- SELECT pSessionId, A.supplier_id,
- A.product_id, A.product_balance_id,
- A.qty, A.gross_price_po
- FROM tt_summary_doc_sales_item_penjualan_by_period A
- WHERE A.session_id = pSessionId;
- RAISE NOTICE '7';
- INSERT INTO tt_summary_item_penjualan_by_period
- (session_id, supplier_id,
- product_id, product_balance_id,
- qty, gross_price_po)
- SELECT pSessionId, A.supplier_id,
- A.product_id, A.product_balance_id,
- A.qty, A.gross_price_po
- FROM tt_summary_doc_return_item_penjualan_by_period A
- WHERE A.session_id = pSessionId
- AND A.record_type = 'SALES';
- RAISE NOTICE '8';
- INSERT INTO tt_summary_item_return_by_period
- (session_id, supplier_id,
- product_id, product_balance_id,
- qty, gross_price_po, tax_percentage)
- SELECT pSessionId, A.supplier_id,
- A.product_id, A.product_balance_id,
- A.qty, A.gross_price_po, A.tax_percentage
- FROM tt_summary_doc_return_item_penjualan_by_period A
- WHERE A.session_id = pSessionId
- AND A.record_type = 'RETURN';
- INSERT INTO tt_monthly_supplier_po_rg(
- session_id, supplier_id,
- po_id, po_no, po_date,
- receive_goods_id, receive_goods_no, receive_goods_date
- )
- SELECT pSessionId, A.supplier_id,
- nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
- nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
- FROM tt_summary_item_penjualan_by_period A
- WHERE A.session_id = pSessionId
- GROUP BY A.supplier_id;
- INSERT INTO tt_monthly_supplier_po_rg_return(
- session_id, supplier_id,
- po_id, po_no, po_date,
- receive_goods_id, receive_goods_no, receive_goods_date
- )
- SELECT pSessionId, A.supplier_id,
- nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
- nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
- FROM tt_summary_item_return_by_period A
- WHERE A.session_id = pSessionId
- GROUP BY A.supplier_id;
- UPDATE tt_monthly_supplier_po_rg A
- SET po_no = vDocNoDefaultPo||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
- receive_goods_no = vDocNoDefaultRg||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
- WHERE A.session_id = pSessionId;
- UPDATE tt_monthly_supplier_po_rg_return A
- SET po_no = vDocNoDefaultPoReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
- receive_goods_no = vDocNoDefaultRgReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
- WHERE A.session_id = pSessionId;
- -- UPDATE po_date berdasarkan data penjualan minimum
- WITH summary_item_penjualan AS (
- SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, A.partner_id
- )
- UPDATE tt_monthly_supplier_po_rg A
- SET po_date = B.doc_date, receive_goods_date = B.doc_date
- FROM summary_item_penjualan B
- WHERE B.session_id = A.session_id
- AND A.session_id = pSessionId
- AND B.partner_id = A.supplier_id;
- WITH summary_item_penjualan AS (
- SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, A.partner_id
- )
- UPDATE tt_monthly_supplier_po_rg_return A
- SET po_date = B.doc_date, receive_goods_date = B.doc_date
- FROM summary_item_penjualan B
- WHERE B.session_id = A.session_id
- AND A.session_id = pSessionId
- AND B.partner_id = A.supplier_id;
- -- UPDATE jika po_datenya NULL
- UPDATE tt_monthly_supplier_po_rg A
- SET po_date = pStartDate, receive_goods_date = pStartDate
- WHERE A.session_id = pSessionId
- AND po_date IS NULL;
- UPDATE tt_monthly_supplier_po_rg_return A
- SET po_date = pStartDate, receive_goods_date = pStartDate
- WHERE A.session_id = pSessionId
- AND po_date IS NULL;
- UPDATE pu_po_balance_item_consignment_sold A
- SET flg_process = vFlagYes, ref_doc_type_id = vDocTypeIdRg, ref_id = -99,
- version = A.version + 1, update_user_id = pUserId, update_datetime = pDatetime
- FROM tt_monthly_supplier_po_rg B
- WHERE B.session_id = pSessionId
- AND B.supplier_id = A.supplier_id
- AND A.ref_doc_type_id = -99 AND A.ref_id = -99
- AND EXISTS (
- SELECT 1
- FROM tr_report_sales_supplier C
- WHERE C.session_id = pSessionId
- AND C.so_item_id = A.so_item_id
- AND C.tenant_id = A.tenant_id
- AND C.partner_id = A.supplier_id
- );
- -------------------------------------------------------------------------------------------------
- -- Buat data PO
- -------------------------------------------------------------------------------------------------
- INSERT INTO pu_po
- (po_id, tenant_id, doc_type_id, doc_no,
- doc_date, ou_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, remark,
- partner_id, purchaser_id, warehouse_id,
- flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
- top_code, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
- A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
- vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
- A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
- vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
- vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_monthly_supplier_po_rg A
- WHERE A.session_id = pSessionId
- GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
- INSERT INTO pu_po
- (po_id, tenant_id, doc_type_id, doc_no,
- doc_date, ou_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, remark,
- partner_id, purchaser_id, warehouse_id,
- flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
- top_code, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
- A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
- vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
- A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
- vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
- vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_monthly_supplier_po_rg_return A
- WHERE A.session_id = pSessionId
- GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
- RAISE NOTICE '7';
- INSERT INTO pu_po_item
- (tenant_id, po_id, line_no,
- ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
- curr_code, gross_price_po,
- flg_tax_amount, tax_id, tax_percentage,
- tax_price,
- nett_price_po,
- qty_po, po_uom_id, qty_int, base_uom_id,
- discount_percentage, discount_amount, gross_item_amount,
- nett_item_amount, tax_amount,
- activity_gl_id, product_coa_id, ou_rc_id, eta,
- tolerance_rcv_qty, remark, version,
- create_datetime, create_user_id, update_datetime, update_user_id,
- segment_id, eta_day, flg_indent)
- SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
- vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
- vCurrCode, A.gross_price_po,
- vFlagYes, vTaxId, vTaxPercentage,
- f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
- f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
- SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
- 0, 0, 0,
- 0, 0,
- vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
- 0, vEmptyValue, 0,
- pDatetime, pUserId, pDatetime, pUserId,
- vEmptyId, vEmptyValue, vFlagNo
- FROM tt_summary_item_penjualan_by_period A
- INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
- WHERE A.session_id = pSessionId
- GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
- INSERT INTO pu_po_item
- (tenant_id, po_id, line_no,
- ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
- curr_code, gross_price_po,
- flg_tax_amount, tax_id, tax_percentage,
- tax_price,
- nett_price_po,
- qty_po, po_uom_id, qty_int, base_uom_id,
- discount_percentage, discount_amount, gross_item_amount,
- nett_item_amount, tax_amount,
- activity_gl_id, product_coa_id, ou_rc_id, eta,
- tolerance_rcv_qty, remark, version,
- create_datetime, create_user_id, update_datetime, update_user_id,
- segment_id, eta_day, flg_indent)
- SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
- vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
- vCurrCode, A.gross_price_po,
- vFlagYes, vTaxId, vTaxPercentage,
- f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
- f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
- SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
- 0, 0, 0,
- 0, 0,
- vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
- 0, vEmptyValue, 0,
- pDatetime, pUserId, pDatetime, pUserId,
- vEmptyId, vEmptyValue, vFlagNo
- FROM tt_summary_item_return_by_period A
- INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
- WHERE A.session_id = pSessionId
- GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
- RAISE NOTICE '8';
- UPDATE pu_po_item A
- SET gross_item_amount = A.gross_price_po * A.qty_po,
- nett_item_amount = A.nett_price_po * A.qty_po,
- tax_amount = A.tax_price * A.qty_po
- WHERE EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg B
- WHERE B.session_id = pSessionId
- AND B.po_id = A.po_id
- );
- UPDATE pu_po_item A
- SET gross_item_amount = A.gross_price_po * A.qty_po,
- nett_item_amount = A.nett_price_po * A.qty_po,
- tax_amount = A.tax_price * A.qty_po
- WHERE EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg_return B
- WHERE B.session_id = pSessionId
- AND B.po_id = A.po_id
- );
- RAISE NOTICE '9';
- INSERT INTO pu_po_balance_item
- (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
- qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
- tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
- A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
- A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A, pu_po B
- WHERE A.po_id = B.po_id
- AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg C
- WHERE C.session_id = pSessionId
- AND C.po_id = A.po_id
- );
- INSERT INTO pu_po_balance_item
- (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
- qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
- tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
- A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
- A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A, pu_po B
- WHERE A.po_id = B.po_id
- AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg_return C
- WHERE C.session_id = pSessionId
- AND C.po_id = A.po_id
- );
- RAISE NOTICE '10';
- INSERT INTO pu_log_po_balance_item
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
- A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A, pu_po B
- WHERE A.po_id = B.po_id
- AND EXISTS(
- SELECT 1 FROM tt_monthly_supplier_po_rg C
- WHERE C.session_id = pSessionId
- AND C.po_id = A.po_id
- );
- INSERT INTO pu_log_po_balance_item
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
- A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A, pu_po B
- WHERE A.po_id = B.po_id
- AND EXISTS(
- SELECT 1 FROM tt_monthly_supplier_po_rg_return C
- WHERE C.session_id = pSessionId
- AND C.po_id = A.po_id
- );
- RAISE NOTICE '11';
- -------------------------------------------------------------------------------------------------
- -- Buat data Receive Goods
- -------------------------------------------------------------------------------------------------
- INSERT INTO pu_receive_goods
- (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id,
- remark, partner_id, warehouse_id, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
- pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
- vDocTypeIdPo, A.po_id,
- vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
- vStatusRelease, vWorkflowStatusApproved,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_monthly_supplier_po_rg A
- WHERE A.session_id = pSessionId
- GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
- -- menambahkan untuk generate po and rg, RGnya harus tercatat di pu_po_rg_generated
- INSERT INTO pu_po_rg_generated (doc_type_id, ref_id, doc_no, partner_id,
- create_datetime, create_user_id,
- update_datetime, update_user_id,
- version)
- SELECT vDocTypeIdRg, A.receive_goods_id, A.receive_goods_no, A.supplier_id,
- pDatetime, pUserId,
- pDatetime, pUserId,
- 0
- FROM tt_monthly_supplier_po_rg A
- WHERE A.session_id = pSessionId
- GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
- INSERT INTO pu_receive_goods
- (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id,
- remark, partner_id, warehouse_id, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
- pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
- vDocTypeIdPo, A.po_id,
- vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
- vStatusRelease, vWorkflowStatusApproved,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_monthly_supplier_po_rg_return A
- WHERE A.session_id = pSessionId
- GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
- RAISE NOTICE '12';
- INSERT INTO pu_receive_goods_item
- (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
- product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
- remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
- C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
- vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_monthly_supplier_po_rg A
- INNER JOIN pu_po B ON A.po_id = B.po_id
- INNER JOIN pu_po_item C ON B.po_id = C.po_id
- WHERE A.session_id = pSessionId;
- INSERT INTO pu_receive_goods_item
- (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
- product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
- remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
- C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
- vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_monthly_supplier_po_rg_return A
- INNER JOIN pu_po B ON A.po_id = B.po_id
- INNER JOIN pu_po_item C ON B.po_id = C.po_id
- WHERE A.session_id = pSessionId;
- RAISE NOTICE '13';
- INSERT INTO pu_log_po_balance_item
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
- B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg D
- WHERE D.receive_goods_id = A.receive_goods_id
- AND D.session_id = pSessionId
- );
- INSERT INTO pu_log_po_balance_item
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
- B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg_return D
- WHERE D.receive_goods_id = A.receive_goods_id
- AND D.session_id = pSessionId
- );
- RAISE NOTICE '14';
- INSERT INTO pu_po_balance_invoice
- (tenant_id, ou_id, partner_id, po_id,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
- curr_code, price_po, item_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
- 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,
- C.curr_code, C.nett_price_po,
- 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),
- vFlagNo, vEmptyId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg D
- WHERE D.receive_goods_id = A.receive_goods_id
- AND D.session_id = pSessionId
- );
- INSERT INTO pu_po_balance_invoice
- (tenant_id, ou_id, partner_id, po_id,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
- curr_code, price_po, item_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
- 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,
- C.curr_code, C.nett_price_po,
- 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),
- vFlagNo, vEmptyId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg_return D
- WHERE D.receive_goods_id = A.receive_goods_id
- AND D.session_id = pSessionId
- );
- INSERT INTO pu_po_balance_invoice_tax
- (tenant_id, ou_id, partner_id, po_id,
- ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
- tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
- A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
- C.tax_percentage, C.curr_code,
- 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),
- 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),
- vFlagNo, vEmptyId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND
- C.tax_id = D.tax_id AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg D
- WHERE D.receive_goods_id = A.receive_goods_id
- AND D.session_id = pSessionId
- );
- RAISE NOTICE '32';
- INSERT INTO pu_po_balance_invoice_tax
- (tenant_id, ou_id, partner_id, po_id,
- ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
- tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
- A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
- C.tax_percentage, C.curr_code,
- 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),
- 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),
- vFlagNo, vEmptyId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND
- C.tax_id = D.tax_id AND EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg_return D
- WHERE D.receive_goods_id = A.receive_goods_id
- AND D.session_id = pSessionId
- );
- RAISE NOTICE '33';
- -------------------------------------------------------------------------------------------------
- -- Buat data log product balance stock
- -------------------------------------------------------------------------------------------------
- INSERT INTO in_log_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
- A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(A.qty),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_summary_item_penjualan_by_period A
- INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
- WHERE A.session_id = pSessionId
- GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
- INSERT INTO in_log_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
- A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(-1*A.qty),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_summary_item_penjualan_by_period A
- INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
- WHERE A.session_id = pSessionId
- GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
- -------------------------------------------------------------------------------------------------
- -- Buat data jurnal penerimaan barang
- -------------------------------------------------------------------------------------------------
- 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)
- FROM pu_receive_goods A
- WHERE EXISTS (
- SELECT 1 FROM tt_monthly_supplier_po_rg E
- WHERE E.session_id = pSessionId
- AND A.receive_goods_id = E.receive_goods_id
- );
- RAISE NOTICE '18';
- INSERT INTO tt_generate_po_rg_for_journal
- (session_id, receive_goods_id, journal_trx_id)
- SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
- FROM tt_monthly_supplier_po_rg A
- WHERE A.session_id = pSessionId
- GROUP BY A.receive_goods_id;
- INSERT INTO tt_generate_po_rg_for_journal
- (session_id, receive_goods_id, journal_trx_id)
- SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
- FROM tt_monthly_supplier_po_rg_return A
- WHERE A.session_id = pSessionId
- GROUP BY A.receive_goods_id;
- RAISE NOTICE '19';
- INSERT INTO gl_journal_trx
- (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- 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,
- (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,
- A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_po B, tt_generate_po_rg_for_journal C
- WHERE A.ref_doc_type_id = B.doc_type_id
- AND A.ref_id = B.po_id
- AND C.session_id = pSessionId
- AND C.receive_goods_id = A.receive_goods_id;
- RAISE NOTICE '20';
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
- A.doc_type_id, B.receive_goods_item_id,
- A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
- 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,
- C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
- 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)),
- A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, tt_generate_po_rg_for_journal D
- WHERE A.receive_goods_id = B.receive_goods_id AND
- A.receive_goods_id = D.receive_goods_id AND
- B.ref_id = C.po_item_id AND
- D.session_id = pSessionId;
- RAISE NOTICE '21';
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- abs(A.amount), A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId;
- RAISE NOTICE '22';
- -- UNTUK CREDIT
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
- abs(SUM(A.amount)), A.journal_date, A.type_rate,
- 1, 1, 'ACCR_AP', vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.sign_journal = vSignDebit
- GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
- -- UNTUK DEBIT
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, 2,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId,vSignDebit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
- abs(SUM(A.amount)), A.journal_date, A.type_rate,
- 1, 1, 'ACCR_AP', vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.sign_journal = vSignCredit
- GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
- -- insert ke pu_po_generated_po_and_rg
- 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)
- SELECT pPeriodProcess, A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date
- FROM tt_monthly_supplier_po_rg A
- WHERE A.session_id = pSessionId
- GROUP BY A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date;
- vStatusGenerate := 'S';
- ELSE
- vStatusGenerate := 'E';
- RAISE NOTICE 'TERKENA VALIDASI ERROR DOKUMEN PENJUALAN DARI MAGENTO, CEK HALAMAN REPROCESS MAGENTO ORDER';
- END IF;
- ELSE
- vStatusGenerate := 'E';
- RAISE NOTICE 'TERKENA VALIDASI DOKUMEN TRANSAKSI YANG BELUM DI APPROVED';
- END IF;
- DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
- DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
- DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
- DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
Advertisement
Add Comment
Please, Sign In to add comment