Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION pu_submit_retur_proforma(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vRpId bigint;
- vUserId bigint;
- vReturProformaDocTypeId bigint := 117;
- vEmptyId bigint := -99;
- vOutletId bigint;
- vOuWarehouseId bigint;
- vOuId bigint;
- vOuWarehouseIdForJournal bigint;
- vJournalTrxId bigint;
- vDatetime character varying(14);
- vYes character varying := 'Y';
- vNo character varying := 'N';
- vStatusRelease character varying := 'R';
- vRoundingModeNonTax character varying(5);
- vProductStatus character varying := 'GOOD';
- vProductCOA character varying := 'PRODUCT';
- vSignCredit character varying := 'C';
- vTypeRate character varying := 'COM';
- vActivityCOA character varying := 'ACTIVITY';
- vSystemCOA character varying := 'SYSTEM';
- vEmptyValue character varying := '';
- vDbVersion character varying(10);
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- BEGIN
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- vDbVersion = '1.0';
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'pu_submit_retur_proforma' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vRpId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'returProformaId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'userId';
- SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- UPDATE pu_retur_proforma
- SET status_doc = vStatusRelease, version = version+1, update_datetime = vDatetime, update_user_id = vUserId
- WHERE retur_proforma_id = vRpId;
- -- Serial Number referensi data dari pu_receive_goods_item
- INSERT INTO pu_retur_proforma_product
- (tenant_id, retur_proforma_item_id, line_no, product_id,
- serial_number, product_expired_date, product_year_made,
- lot_number, 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 A.tenant_id, A.retur_proforma_item_id, D.line_no, A.product_id,
- D.serial_number, D.product_expired_date, D.product_year_made,
- D.lot_number, D.qty_rcv_po, D.po_uom_id,D.qty_rcv_int, D.base_uom_id,D.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma_item A,pu_retur_proforma B, pu_receive_goods_item C, pu_receive_goods_product D
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND A.ref_id = C.receive_goods_item_id
- AND C.receive_goods_item_id = D.receive_goods_item_id
- AND A.retur_proforma_id = vRpId;
- 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, D.po_id, D.po_item_id, A.doc_type_id, A.retur_proforma_id, B.retur_proforma_item_id,
- B.qty_rcv_po * 1, B.po_uom_id, B.qty_rcv_int * 1, B.base_uom_id, B.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.ref_id = C.receive_goods_item_id
- AND C.ref_id = D.po_item_id
- AND A.retur_proforma_id = vRpId;
- 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, D.po_id,
- A.doc_type_id, A.retur_proforma_id, A.doc_no, A.doc_date, B.retur_proforma_item_id, B.qty_rcv_po, B.po_uom_id,
- D.curr_code, D.nett_price_po,
- f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
- vYes, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.ref_id = C.receive_goods_item_id
- AND C.ref_id = D.po_item_id
- AND A.retur_proforma_id = vRpId;
- 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.retur_proforma_id, B.retur_proforma_item_id, D.tax_id, E.flg_amount,
- D.tax_percentage, D.curr_code,
- f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
- f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax), D.tax_percentage),
- vYes, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D, m_tax E
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.ref_id = C.receive_goods_item_id
- AND C.ref_id = D.po_item_id
- AND D.tax_id = E.tax_id
- AND A.retur_proforma_id = vRpId;
- 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 A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_id, A.doc_no, A.doc_date, A.partner_id,
- C.product_id,E.product_balance_id,D.warehouse_id,vProductStatus, B.base_uom_id, SUM(B.qty_rcv_int),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A, pu_retur_proforma_item B, m_product C, m_warehouse_ou D, in_product_balance E
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.product_id = C.product_id
- AND A.warehouse_id = D.warehouse_id
- AND C.product_id = E.product_id
- AND A.retur_proforma_id = vRpId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_item_id, A.doc_no, A.doc_date, A.partner_id,
- C.product_id,E.product_balance_id,D.warehouse_id,B.base_uom_id;
- -- cek dulu warehouse ou nya
- SELECT B.ou_id INTO vOuWarehouseId
- FROM pu_retur_proforma A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- WHERE retur_proforma_id = vRpId;
- IF vOuWarehouseId IS NULL THEN
- vOuWarehouseId = vEmptyId;
- END IF;
- -- cek dulu apakah ou yang digunakan ini merupakan ou outlet
- IF EXISTS(SELECT 1 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId) THEN
- SELECT A.outlet_id INTO vOutletId
- FROM i_outlet A WHERE A.ou_id = vOuWarehouseId;
- END IF;
- IF vOutletId IS NULL THEN
- vOutletId = vEmptyId;
- END IF;
- -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
- IF vOutletId <> vEmptyId THEN
- vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
- INSERT INTO i_trx_data_log(
- tenant_id, ou_id, doc_date, db_version, type_data,
- mode_log, data_log, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT B.tenant_id, B.ou_id, B.doc_date, '1.0', 'in_log_product_balance_stock',
- 'U', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A
- INNER JOIN in_log_product_balance_stock B
- ON A.tenant_id = B.tenant_id
- AND A.doc_no = B.doc_no
- AND A.doc_date = B.doc_date
- AND A.doc_type_id = B.doc_type_id
- AND A.retur_proforma_id = B.ref_id
- AND A.warehouse_id = B.warehouse_id
- WHERE A.retur_proforma_id = vRpId;
- END IF;
- INSERT INTO in_log_product_price_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_id, A.doc_no, A.doc_date, A.partner_id,
- C.product_id,A.warehouse_id, D.product_balance_id, E.product_price_balance_id ,vProductStatus,B.base_uom_id,SUM(B.qty_rcv_int),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A, pu_retur_proforma_item B, m_product C, in_product_balance D , in_product_price_balance_stock E
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.product_id = C.product_id
- AND C.product_id = D.product_id
- AND D.product_balance_id = E.product_balance_id
- AND A.retur_proforma_id = vRpId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_id, A.doc_date, A.doc_no, A.partner_id,
- C.product_id,A.warehouse_id, D.product_balance_id,B.base_uom_id,E.product_price_balance_id;
- SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
- FROM pu_retur_proforma A
- WHERE A.retur_proforma_id = vRpId INTO result;
- -- vRpId := result.ref_id;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- /*
- * Membuat journal
- * Journal yang terbentuk
- * D dan ACCL_AP (yang mau di koreksi) --> Dokumen Dari receive Goods
- * C dan ACCL_AP (yang betul) --> Dokumen yg terbentuk Retur Proforma
- */
- 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', vDatetime, vUserId)
- FROM pu_retur_proforma A
- WHERE A.retur_proforma_id = vRpId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- 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 vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.retur_proforma_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, C.curr_code, A.remark, 'D', 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_retur_proforma A, pu_receive_goods B, pu_po C
- WHERE A.retur_proforma_id = vRpId
- AND B.ref_doc_type_id = C.doc_type_id
- AND A.ref_id = C.po_id
- AND A.ref_doc_type_id = B.doc_type_id
- AND A.ref_id = B.receive_goods_id;
- 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, vJournalTrxId, 1,
- A.doc_type_id, B.retur_proforma_item_id,
- A.partner_id, B.product_id, vEmptyId, D.ou_rc_id,
- vEmptyId, vSignCredit, vProductCOA, D.activity_gl_id,
- D.product_coa_id, D.curr_code, B.qty_rcv_int, B.base_uom_id,
- f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage,f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
- A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.ref_id = C.receive_goods_item_id
- AND C.ref_id = D.po_item_id
- AND A.retur_proforma_id = vRpId
- AND D.flg_stock = vYes;
- 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, vJournalTrxId, 1,
- A.doc_type_id, B.retur_proforma_id,
- A.partner_id, B.product_id, vEmptyId, D.ou_rc_id,
- D.segment_id, vSignCredit, vActivityCOA, D.activity_gl_id,
- E.coa_id, D.curr_code, B.qty_rcv_int, B.base_uom_id,
- f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
- A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_NON_STOCK', B.remark
- FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D, m_activity_gl E
- WHERE A.retur_proforma_id = B.retur_proforma_id
- AND B.ref_id = C.receive_goods_item_id
- AND C.ref_id = D.po_item_id
- AND A.retur_proforma_id = vRpId
- AND D.flg_stock = 'N'
- AND D.activity_gl_id = E.activity_gl_id;
- 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,
- ou_branch_id, ou_sub_bu_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,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId,
- (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.journal_desc = 'PRODUCT_STOCK';
- 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,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.journal_desc = 'PRODUCT_NON_STOCK';
- 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, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- 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,
- SUM(A.amount), A.journal_date, A.type_rate,
- 1, 1, 'ACCL_AP', vEmptyValue,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
- 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