widana

pu_submit_invoice_ap_memo_supplier

May 22nd, 2017
102
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION pu_submit_invoice_ap_memo_supplier(bigint, character varying, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pTenantId                   ALIAS FOR $1;
  6.     pSessionId                  ALIAS FOR $2;
  7.     pProcessNo                  ALIAS FOR $3;
  8.  
  9.     vProcessId                  bigint;
  10.     vInvoiceApMemoId            bigint;
  11.     vUserId                     bigint;
  12.     vMemoSupplierDocTypeId      bigint := 118;
  13.     vReceiveGoodsDocTypeId      bigint := 111;
  14.     vPurchaseOrderDocTypeId     bigint := 101;
  15.     vEmptyId                    bigint := -99;
  16.     vOuId                       bigint;
  17.     vJournalTrxId               bigint;
  18.     vReceiveGoodsId             bigint;
  19.     vPurchaseOrderId            bigint;
  20.    
  21.     vDatetime                   character varying(14);
  22.     vYes                        character varying := 'Y';
  23.     vNo                         character varying := 'N';
  24.     vInProgress                 character varying := 'I';
  25.     vStatusFinal                character varying := 'F';
  26.     vStatusRelease              character varying := 'R';
  27.     vRoundingModeNonTax         character varying(5);
  28.     vProductStatus              character varying := 'GOOD';
  29.     vProductCOA                 character varying := 'PRODUCT';
  30.     vSignCredit                 character varying := 'C';
  31.     vSignDebit                  character varying := 'D';
  32.     vTypeRate                   character varying := 'COM';
  33.     vActivityCOA                character varying := 'ACTIVITY';
  34.     vSystemCOA                  character varying := 'SYSTEM';
  35.     vEmptyValue                 character varying := '';
  36.     vDbVersion                  character varying(10);
  37.  
  38.     vDocJournal                 DOC_JOURNAL%ROWTYPE;
  39.     vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
  40.     vOuStructureJournalItem     OU_BU_STRUCTURE%ROWTYPE;
  41.     result                      RECORD;
  42.    
  43.     vSignCreditOrDebt           character varying := '';
  44.     vCoaDesc                    character varying := '';
  45.     vCoaCreditOrDebt            character varying := '';
  46.     vTotalAmount                numeric           := 0;
  47.    
  48. BEGIN
  49.  
  50.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  51.  
  52.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  53.    
  54.     SELECT A.process_message_id INTO vProcessId
  55.     FROM t_process_message A
  56.     WHERE A.tenant_id = pTenantId AND
  57.         A.process_name = 'pu_submit_invoice_ap_memo_supplier' AND
  58.         A.process_no = pProcessNo;
  59.        
  60.     SELECT CAST(A.process_parameter_value AS bigint) INTO vInvoiceApMemoId
  61.     FROM t_process_parameter A
  62.     WHERE A.process_message_id = vProcessId AND
  63.         A.process_parameter_key = 'invoiceApId';
  64.    
  65.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  66.     FROM t_process_parameter A
  67.     WHERE A.process_message_id = vProcessId AND
  68.         A.process_parameter_key = 'userId';
  69.  
  70.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  71.     FROM t_process_parameter A
  72.     WHERE A.process_message_id = vProcessId AND
  73.         A.process_parameter_key = 'datetime';
  74.  
  75.     UPDATE fi_invoice_ap
  76.         SET status_doc = vStatusRelease, version = version+1, update_datetime = vDatetime, update_user_id = vUserId
  77.     WHERE invoice_ap_id = vInvoiceApMemoId;
  78.  
  79.     SELECT total_amount INTO vTotalAmount FROM fi_invoice_ap WHERE invoice_ap_id = vInvoiceApMemoId;
  80.  
  81.     UPDATE pu_po_balance_invoice B
  82.         SET flg_invoice = vNo, version = B.version+1, update_datetime = vDatetime, update_user_id = vUserId
  83.     FROM fi_invoice_ap A
  84.     WHERE A.tenant_id = pTenantId
  85.         AND A.ref_id = B.ref_id
  86.         AND A.ref_doc_type_id = B.ref_doc_type_id
  87.         AND A.invoice_ap_id = vInvoiceApMemoId;
  88.  
  89.     UPDATE pu_po_balance_invoice_tax B
  90.         SET flg_invoice = vNo, version = B.version+1, update_datetime = vDatetime, update_user_id = vUserId
  91.     FROM fi_invoice_ap A
  92.     WHERE A.tenant_id = pTenantId
  93.         AND A.ref_id = B.ref_id
  94.         AND A.ref_doc_type_id = B.ref_doc_type_id
  95.         AND A.invoice_ap_id = vInvoiceApMemoId;
  96.        
  97.     INSERT INTO pu_po_balance_invoice
  98.             (tenant_id, ou_id, partner_id, po_id,
  99.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  100.             curr_code, price_po, item_amount, flg_invoice, invoice_id,
  101.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  102.     SELECT A.tenant_id, A.ou_id, A.partner_id, D.po_id,
  103.         A.doc_type_id, A.invoice_ap_id, A.doc_no, A.doc_date, B.invoice_ap_memo_item_id, B.qty, D.po_uom_id,
  104.         A.curr_code, B.total_amount_difference/E.qty_rcv_po,
  105.         B.total_amount_difference,
  106. --      f_get_amount_before_tax(B.total_amount_difference, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vMemoSupplierDocTypeId, D.curr_code), vRoundingModeNonTax),
  107.         vNo, vEmptyId,
  108.         0, vDatetime, vUserId, vDatetime, vUserId
  109.     FROM fi_invoice_ap A, fi_invoice_ap_memo_item B,pu_receive_goods_item E, pu_po_item D
  110.     WHERE A.tenant_id = 10
  111.         AND A.invoice_ap_id = B.invoice_ap_id
  112.         AND B.ref_item_id = E.receive_goods_item_id
  113.         AND D.po_item_id = E.ref_id
  114.         AND E.ref_doc_type_id = vPurchaseOrderDocTypeId
  115.         AND A.doc_type_id = vMemoSupplierDocTypeId
  116.         AND A.invoice_ap_id = vInvoiceApMemoId;
  117.  
  118.     INSERT INTO pu_po_balance_invoice_tax
  119.             (tenant_id, ou_id, partner_id, po_id,
  120.             ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  121.             tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  122.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  123.     SELECT A.tenant_id, A.ou_id, A.partner_id, D.po_id,
  124.             A.doc_type_id, A.invoice_ap_id, B.invoice_ap_memo_item_id, D.tax_id, E.flg_amount,
  125.             D.tax_percentage, D.curr_code,
  126. --          0,
  127.             f_get_amount_before_tax(B.total_amount_difference/*/C.qty_rcv_po*/, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vMemoSupplierDocTypeId, D.curr_code), vRoundingModeNonTax),
  128. --          0,
  129.             f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.total_amount_difference/*/C.qty_rcv_po*/, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vMemoSupplierDocTypeId, D.curr_code), vRoundingModeNonTax), D.tax_percentage),
  130.             vNo, vEmptyId,
  131.             0, vDatetime, vUserId, vDatetime, vUserId
  132.     FROM fi_invoice_ap A, fi_invoice_ap_memo_item B, pu_receive_goods_item C, pu_po_item D, m_tax E
  133.     WHERE A.tenant_id = 10
  134.         AND A.invoice_ap_id = B.invoice_ap_id
  135.         AND B.ref_item_id = C.receive_goods_item_id
  136.         AND C.ref_id = D.po_item_id
  137.         AND D.tax_id = E.tax_id
  138.         AND A.doc_type_id = vMemoSupplierDocTypeId
  139.         AND A.ref_doc_type_id = vReceiveGoodsDocTypeId
  140.         AND A.invoice_ap_id = vInvoiceApMemoId;
  141.  
  142.     IF vTotalAmount <= 0 THEN
  143.         vSignCreditOrDebt := 'C';
  144.         vCoaCreditOrDebt  := 'D';
  145.         vCoaDesc := 'HargaPokokPenjualan';
  146.     ELSE
  147.         vSignCreditOrDebt := 'D';
  148.         vCoaCreditOrDebt  := 'C';
  149.         vCoaDesc := 'HutangHarusDibayar';
  150.     END IF;
  151.  
  152.     SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
  153.     FROM fi_invoice_ap A
  154.     WHERE A.invoice_ap_id = vInvoiceApMemoId INTO result;
  155.  
  156. --  vInvoiceApMemoId := result.ref_id;
  157.     vOuStructure := result.ou;
  158.     vDocJournal := result.doc;      
  159.    
  160.     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)
  161.     FROM fi_invoice_ap A
  162.     WHERE A.invoice_ap_id = vInvoiceApMemoId;
  163.  
  164.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  165.  
  166.     INSERT INTO gl_journal_trx
  167.         (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  168.         ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  169.         ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  170.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  171.     SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.invoice_ap_id, A.doc_no, A.doc_date,
  172.         (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, B.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  173.         A.ref_doc_type_id, A.ref_id, A.doc_date, C.curr_code, vEmptyValue, 'D', 'DRAFT',
  174.         0, vDatetime, vUserId, vDatetime, vUserId
  175.     FROM fi_invoice_ap A , pu_receive_goods B , pu_po C
  176.     WHERE A.invoice_ap_id = vInvoiceApMemoId
  177.         AND A.doc_type_id = vMemoSupplierDocTypeId
  178.         AND A.ref_id = B.receive_goods_id
  179.         AND A.ref_doc_type_id = B.doc_type_id
  180.         AND B.ref_id = C.po_id
  181.         AND B.ref_doc_type_id = C.doc_type_id;
  182.  
  183.     INSERT INTO tt_journal_trx_item
  184.     (session_id, tenant_id, journal_trx_id, line_no,
  185.     ref_doc_type_id, ref_id,
  186.     partner_id, product_id, cashbank_id, ou_rc_id,
  187.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  188.     coa_id, curr_code, qty, uom_id,
  189.     amount, journal_date, type_rate,
  190.     numerator_rate, denominator_rate, journal_desc, remark)
  191.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  192.         A.doc_type_id, B.invoice_ap_memo_item_id,
  193.         A.partner_id, C.product_id, vEmptyId, D.ou_rc_id,
  194.         vEmptyId, vSignCreditOrDebt, vProductCOA, D.activity_gl_id,
  195.         D.product_coa_id, D.curr_code, C.qty_rcv_int, C.base_uom_id,
  196.         B.total_amount_difference,
  197.         --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),
  198.         A.doc_date, vTypeRate,
  199.         1, 1, 'PRODUCT_STOCK', vEmptyValue
  200.     FROM fi_invoice_ap A, fi_invoice_ap_memo_item B, pu_receive_goods_item C, pu_po_item D
  201.     WHERE A.invoice_ap_id = B.invoice_ap_id
  202.     AND B.ref_item_id = C.receive_goods_item_id
  203.     AND C.ref_id = D.po_item_id
  204.     AND A.invoice_ap_id = vInvoiceApMemoId
  205.     AND D.flg_stock = vYes;
  206.  
  207.     INSERT INTO tt_journal_trx_item
  208.     (session_id, tenant_id, journal_trx_id, line_no,
  209.     ref_doc_type_id, ref_id,
  210.     partner_id, product_id, cashbank_id, ou_rc_id,
  211.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  212.     coa_id, curr_code, qty, uom_id,
  213.     amount, journal_date, type_rate,
  214.     numerator_rate, denominator_rate, journal_desc, remark)
  215.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  216.         A.doc_type_id, B.invoice_ap_memo_item_id,
  217.         A.partner_id, C.product_id, vEmptyId, D.ou_rc_id,
  218.         vEmptyId, vSignCreditOrDebt, vProductCOA, D.activity_gl_id,
  219.         D.product_coa_id, D.curr_code, C.qty_rcv_int, C.base_uom_id,
  220.         B.total_amount_difference,
  221.         --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),
  222.         A.doc_date, vTypeRate,
  223.         1, 1, 'PRODUCT_NON_STOCK', vEmptyValue
  224.     FROM fi_invoice_ap A, fi_invoice_ap_memo_item B, pu_receive_goods_item C, pu_po_item D
  225.     WHERE A.invoice_ap_id = B.invoice_ap_id
  226.     AND B.ref_item_id = C.receive_goods_item_id
  227.     AND C.ref_id = D.po_item_id
  228.     AND A.invoice_ap_id = vInvoiceApMemoId
  229.     AND D.flg_stock = vNo;
  230.    
  231.     INSERT INTO gl_journal_trx_item
  232.         (tenant_id, journal_trx_id, line_no,
  233.         ref_doc_type_id, ref_id,
  234.         partner_id, product_id, cashbank_id, ou_rc_id,
  235.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  236.         coa_id, curr_code, qty, uom_id,
  237.         amount, journal_date, type_rate,
  238.         numerator_rate, denominator_rate, journal_desc, remark,
  239.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  240.         ou_branch_id, ou_sub_bu_id)
  241.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  242.         A.ref_doc_type_id, A.ref_id,
  243.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  244.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  245.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  246.         A.amount, A.journal_date, A.type_rate,
  247.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  248.         0, vDatetime, vUserId, vDatetime, vUserId,
  249.         (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
  250.     FROM tt_journal_trx_item A
  251.     WHERE A.session_id = pSessionId
  252.     AND A.journal_desc = 'PRODUCT_STOCK';
  253.  
  254.     INSERT INTO gl_journal_trx_item
  255.         (tenant_id, journal_trx_id, line_no,
  256.         ref_doc_type_id, ref_id,
  257.         partner_id, product_id, cashbank_id, ou_rc_id,
  258.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  259.         coa_id, curr_code, qty, uom_id,
  260.         amount, journal_date, type_rate,
  261.         numerator_rate, denominator_rate, journal_desc, remark,
  262.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  263.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  264.         A.ref_doc_type_id, A.ref_id,
  265.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  266.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  267.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  268.         A.amount, A.journal_date, A.type_rate,
  269.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  270.         0, vDatetime, vUserId, vDatetime, vUserId
  271.     FROM tt_journal_trx_item A
  272.     WHERE A.session_id = pSessionId
  273.     AND A.journal_desc = 'PRODUCT_NON_STOCK';
  274.    
  275.     INSERT INTO gl_journal_trx_mapping
  276.         (tenant_id, journal_trx_id, line_no,
  277.         ref_doc_type_id, ref_id,
  278.         partner_id, product_id, cashbank_id, ou_rc_id,
  279.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  280.         coa_id, curr_code, qty, uom_id,
  281.         amount, journal_date, type_rate,
  282.         numerator_rate, denominator_rate, journal_desc, remark,
  283.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  284.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  285.         vEmptyId, vEmptyId,    
  286.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  287.         vEmptyId, vCoaCreditOrDebt, vSystemCOA, vEmptyId,
  288.         f_get_system_coa_by_group_coa(A.tenant_id, vCoaDesc), A.curr_code, 0, vEmptyId,
  289.         SUM(A.amount), A.journal_date, A.type_rate,
  290.         1, 1, 'ACCR_AP', vEmptyValue,
  291.         0, vDatetime, vUserId, vDatetime, vUserId
  292.     FROM tt_journal_trx_item A
  293.     WHERE A.session_id = pSessionId
  294.     GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  295.    
  296.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  297.    
  298. END;
  299. $BODY$
  300.   LANGUAGE plpgsql VOLATILE
  301.   COST 100;
  302. /
Advertisement
Add Comment
Please, Sign In to add comment