Ledger Nano X - The secure hardware wallet
SHARE
TWEET

Untitled

tercnem Apr 2nd, 2020 2,570 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_migrate_data_trx_pos_to_debt_note_ar(character varying, bigint, character varying, bigint, bigint, character varying, character varying, numeric, bigint)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pSessionId          alias for $1;
  6.     pTenantId           alias for $2;
  7.     pProcessNo          alias for $3;
  8.     pOuId               alias for $4;
  9.     pUserId             alias for $5;
  10.     pDatetime           alias for $6;
  11.     pFlgPkp             alias for $7;
  12.     pCountBelumDiBayar  alias for $8;
  13.     pTrxPosId           alias for $9;
  14.  
  15.     vStatusDoc          character varying;
  16.     vDocTypeDebtNoteAr  bigint;
  17.     vEmptyValue         bigint;
  18.     vSpasiChar          character varying;
  19.     vYes                character varying;
  20.     vNo                 character varying;
  21.     vCurrency           character varying;
  22.     vNol                numeric;
  23.     vWorkFlowStatus     character varying;
  24.     vCoaId              bigint;
  25.     vProcessName        character varying;
  26.     vCountMinutes       integer;
  27.     vParamKey           character varying;
  28.     vStatusDocDraft     character varying;
  29.     vWorkFlowStatusDraft character varying;
  30.     vEmptyString        character varying;
  31.     vDocTypeDPBalance   bigint;
  32.     vFlowArDebtNoteId   bigint;
  33.     vSchemeDebtNoteAr   character varying;
  34.     vInvoiceArId        bigint;
  35.     vInvoiceArBalanceId bigint;
  36.     vNettAmountWithoutNonTax bigint;
  37.     vMaxLineNo          bigint;
  38. BEGIN
  39.     vStatusDoc := 'I';
  40.     vDocTypeDebtNoteAr := 241;
  41.     vDocTypeDPBalance := 252;
  42.     vEmptyValue := -99;
  43.     vSpasiChar := ' ';
  44.     vYes := 'Y';
  45.     vNo := 'N';
  46.     vCurrency := 'IDR';
  47.     vNol := 0;
  48.     vWorkFlowStatus := 'APPROVED';
  49.     vProcessName := 'fi_submit_invoice_ar';
  50.     vCountMinutes := 1;
  51.     vParamKey := 'invoiceArId';
  52.     vStatusDocDraft := 'D';
  53.     vWorkFlowStatusDraft := 'DRAFT';
  54.     vEmptyString := '';
  55.     vSchemeDebtNoteAr = 'DF01';
  56.    
  57.        
  58.     -- hanya dijalankan jika ada kurang bayar(BELUM LUNAS PEMBAYARANNYA ), kalau sudah lunas tidak tulis debtnoteAR
  59.     IF pCountBelumDiBayar > 0 THEN
  60.        
  61.         /* get coa id*/
  62.         vCoaId := (SELECT f_get_system_coa_by_group_coa(pTenantId, 'PendapatanPenjualan'));
  63.        
  64.         SELECT nextval('fi_invoice_ar_seq') INTO vInvoiceArId;
  65.         SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;
  66.                
  67.         IF pFlgPkp = vYes THEN
  68.             /* PKP */
  69.             /*
  70.              * insert ke tabel finance invoice ar
  71.              * dengan status_doc = D dan workflow_status = DRAFT
  72.              *
  73.              * mod by Didit, 29 Nov 2016
  74.              * Debt Note AR langsung approved
  75.              * ubah perhitungan tax
  76.              */
  77.             INSERT INTO fi_invoice_ar(
  78.                 invoice_ar_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
  79.                 partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
  80.                 due_date, curr_code, total_tax_base_amount, total_amount, tax_amount,
  81.                 remark, status_doc, workflow_status, version, create_datetime,
  82.                 create_user_id, update_datetime, update_user_id)
  83.             SELECT vInvoiceArId, A.tenant_id, vDocTypeDebtNoteAr, A.doc_no, A.doc_date, c.ou_parent_id,
  84.                 D.partner_id, A.doc_no, A.doc_date, A.doc_type_id, A.trx_pos_id,
  85.                 B.payment_due_date, vCurrency, vNol, vNol, vNol,
  86.                 A.remark, vStatusDoc, vWorkFlowStatus, vNol, pDatetime,
  87.                 pUserId, pDatetime, pUserId
  88.             FROM i_trx_pos A
  89.             INNER JOIN i_trx_pos_ext E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
  90.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  91.             INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
  92.             INNER JOIN m_partner D ON E.partner_id = D.partner_id AND E.tenant_id = D.tenant_id
  93.             WHERE A.tenant_id = pTenantId
  94.                 AND A.process_no = pProcessNo
  95.                 AND A.ou_id = pOuId
  96.                 AND A.trx_pos_id = pTrxPosId
  97.                 AND B.under_payment_amount > 0
  98.             ORDER BY doc_date;
  99.            
  100.             SELECT COALESCE(A.nett_amount - SUM(CASE WHEN B.tax_id = -99 AND B.tax_amount = 0 THEN B.base_amount ELSE 0 END), A.nett_amount) INTO vNettAmountWithoutNonTax
  101.             FROM i_trx_pos A
  102.             INNER JOIN i_trx_pos_termin_payment E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
  103.             INNER JOIN i_trx_pos_tax B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  104.             WHERE A.tenant_id = pTenantId
  105.                 AND A.process_no = pProcessNo
  106.                 AND A.ou_id = pOuId
  107.                 AND A.trx_pos_id = pTrxPosId
  108.             GROUP BY A.nett_amount;
  109.        
  110.             -- insert ke tabel fi_invoice_ar_tax jika ou_bu_id punya flg_pkp = Y
  111.             INSERT INTO fi_invoice_ar_tax(
  112.                 tenant_id, invoice_ar_id, tax_id, flg_amount,
  113.                 tax_percentage, base_amount,
  114.                 tax_amount, tax_no, tax_date, tax_curr_code,
  115.                 gov_tax_amount, remark, version, create_datetime, create_user_id,
  116.                 update_datetime, update_user_id)
  117.             SELECT A.tenant_id, C.invoice_ar_id, D.tax_id, D.flg_amount,
  118.                 -- D.percentage, E.under_payment_amount, B.tax_amount, vEmptyString, A.doc_date, vCurrency,
  119.                 -- rev by Ping An 17 Nov 2015, base_amount ambil dari nilai total amount di POS, bukan ambil nilai kurang bayarnya saja
  120.                 -- D.percentage, A.total_amount, B.tax_amount, vEmptyString, A.doc_date, vCurrency,
  121.                 -- set base amountnya ambil dari nett amount di pos(tanpa rounding) - tax
  122.                 D.percentage, SUM(vNettAmountWithoutNonTax - f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, B.flg_amount, B.tax_percentage)),
  123.                 SUM(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, B.flg_amount, B.tax_percentage)), vEmptyString, A.doc_date, vCurrency,
  124.                 SUM(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, B.flg_amount, B.tax_percentage)), A.remark, vNol, pDatetime, pUserId,
  125.                 pDatetime, pUserId
  126.             FROM i_trx_pos A
  127.             INNER JOIN i_trx_pos_termin_payment E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
  128.             INNER JOIN i_trx_pos_tax B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  129.             INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
  130.             INNER JOIN m_tax D ON D.tax_id = B.tax_id
  131.             WHERE A.tenant_id = pTenantId
  132.                 AND A.process_no = pProcessNo
  133.                 AND A.ou_id = pOuId
  134.                 AND A.trx_pos_id = pTrxPosId
  135.                 AND B.tax_id <> -99
  136.                 AND B.tax_amount <> 0
  137.             GROUP BY A.tenant_id, C.invoice_ar_id, D.tax_id, D.flg_amount, D.percentage, A.doc_date, A.remark;
  138.                
  139.                    
  140.             /* insert fi_invoice_ar_cost non tax */
  141.             INSERT INTO fi_invoice_ar_cost(
  142.                 tenant_id, invoice_ar_id, line_no, activity_gl_id,
  143.                 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
  144.                 remark, version, create_datetime, create_user_id, update_datetime,
  145.                 update_user_id, segment_id)
  146.             SELECT A.tenant_id, C.invoice_ar_id, ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
  147.                 vEmptyValue, vCurrency, F.base_amount, -99, vNol, vNol,
  148.                 A.remark, vNol, pDatetime, pUserId, pDatetime,
  149.                 pUserId, vEmptyValue
  150.             FROM i_trx_pos A
  151.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  152.             INNER JOIN i_trx_pos_tax F ON A.tenant_id = F.tenant_id AND A.process_no = F.process_no AND A.trx_pos_id = F.trx_pos_id
  153.             INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
  154.             INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
  155.             WHERE A.tenant_id = pTenantId
  156.                 AND A.process_no = pProcessNo
  157.                 AND A.ou_id = pOuId
  158.                 AND A.trx_pos_id = pTrxPosId
  159.                 AND F.tax_id = -99
  160.                 AND F.tax_amount = 0;
  161.            
  162.             SELECT COALESCE(MAX(line_no), 0) INTO vMaxLineNo
  163.             FROM fi_invoice_ar_cost
  164.             WHERE invoice_ar_id = vInvoiceArId;
  165.                
  166.             /* insert fi_invoice_ar_cost with tax */
  167.             INSERT INTO fi_invoice_ar_cost(
  168.                 tenant_id, invoice_ar_id, line_no, activity_gl_id,
  169.                 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
  170.                 remark, version, create_datetime, create_user_id, update_datetime,
  171.                 update_user_id, segment_id)
  172.             SELECT A.tenant_id, C.invoice_ar_id, vMaxLineNo + ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
  173.                 vEmptyValue, vCurrency, vNettAmountWithoutNonTax - COALESCE(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, F.flg_amount, F.tax_percentage), vNol), COALESCE(F.tax_id, vEmptyValue), COALESCE(F.tax_percentage, vNol), COALESCE(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, F.flg_amount, F.tax_percentage), vNol),
  174.                 A.remark, vNol, pDatetime, pUserId, pDatetime,
  175.                 pUserId, vEmptyValue
  176.             FROM i_trx_pos A
  177.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  178.             INNER JOIN i_trx_pos_tax F ON A.tenant_id = F.tenant_id AND A.process_no = F.process_no AND A.trx_pos_id = F.trx_pos_id
  179.             INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
  180.             LEFT JOIN m_tax D ON F.tax_id = D.tax_id
  181.             INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
  182.             WHERE A.tenant_id = pTenantId
  183.                 AND A.process_no = pProcessNo
  184.                 AND A.ou_id = pOuId
  185.                 AND A.trx_pos_id = pTrxPosId
  186.                 AND F.tax_id <> -99
  187.                 AND F.tax_amount <> 0;
  188.            
  189.             SELECT COALESCE(MAX(line_no), 0) INTO vMaxLineNo
  190.             FROM fi_invoice_ar_cost
  191.             WHERE invoice_ar_id = vInvoiceArId;
  192.                
  193.             /* untuk PKP ambil dan tulis juga nilai roundingnya jika ada */
  194.             /* insert rounding tanpa tax */
  195.             INSERT INTO fi_invoice_ar_cost(
  196.                 tenant_id, invoice_ar_id, line_no, activity_gl_id,
  197.                 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
  198.                 remark, version, create_datetime, create_user_id, update_datetime,
  199.                 update_user_id, segment_id)
  200.             SELECT A.tenant_id, C.invoice_ar_id, vMaxLineNo + ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
  201.                 vEmptyValue, vCurrency, A.rounding_amount, vEmptyValue, vNol, vNol, -- tanpa tax
  202.                 A.remark, vNol, pDatetime, pUserId, pDatetime,
  203.                 pUserId, vEmptyValue
  204.             FROM i_trx_pos A
  205.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  206.             INNER JOIN i_trx_pos_tax F ON A.tenant_id = F.tenant_id AND A.process_no = F.process_no AND A.trx_pos_id = F.trx_pos_id
  207.             INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
  208.             LEFT JOIN m_tax D ON F.tax_id = D.tax_id
  209.             INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
  210.             WHERE A.tenant_id = pTenantId
  211.                 AND A.process_no = pProcessNo
  212.                 AND A.ou_id = pOuId
  213.                 AND A.trx_pos_id = pTrxPosId
  214.                 AND A.rounding_amount > 0;
  215.                
  216.             /*
  217.              * insert fi_invoice_ar_balance
  218.              * untuk saldo DP partner corporate yg bayar sebagian
  219.              * untuk yang PKP Saldo DP, flg_payment = N dan payment_amount = 0
  220.              */
  221.             INSERT INTO fi_invoice_ar_balance(
  222.                     invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
  223.                     doc_no, doc_date, ext_doc_no, ext_doc_date, ref_doc_type_id,
  224.                     ref_id, partner_id, due_date, curr_code, amount, remark, payment_amount,
  225.                     flg_payment, version, create_datetime, create_user_id, update_datetime,
  226.                     update_user_id)
  227.             SELECT vInvoiceArBalanceId, A.tenant_id, c.ou_parent_id, vDocTypeDPBalance, F.invoice_ar_id,
  228.                 A.doc_no, A.doc_date, vSpasiChar, vSpasiChar, A.doc_type_id,
  229.                 A.trx_pos_id, E.partner_id,
  230.                 -- rev by Ping An 16 Nov 2015, due_date ambil dari docdatenya POS karena DP sudah kejadian dan sudah approved  B.payment_due_date,
  231.                 A.doc_date,
  232.                 A.curr_code, -1 * A.total_payment, vSpasiChar, vNol,
  233.                 vNo, vNol, pDatetime, pUserId, pDatetime,
  234.                 pUserId
  235.             FROM i_trx_pos A
  236.             INNER JOIN i_trx_pos_ext E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
  237.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  238.             INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
  239.             INNER JOIN fi_invoice_ar F ON A.doc_no = F.doc_no AND A.doc_date = F.doc_date AND A.tenant_id = F.tenant_id AND F.doc_type_id = vDocTypeDebtNoteAr
  240.             WHERE A.tenant_id = pTenantId
  241.                 AND A.process_no = pProcessNo
  242.                 AND A.ou_id = pOuId
  243.                 AND A.trx_pos_id = pTrxPosId
  244.                 AND B.payment_amount > 0;
  245.                 -- B.payment_amount > 0 untuk cek apakah ada yang dibayar          
  246.                 -- F.invoice_ar_id : unique index -> fi_invoice_ar_balance adalah tenant_id, doc_type_id, dan invoice_ar_id.
  247.                 -- jadi tidak dipantek -99, krn pasti akan kena error unique constraint
  248.         ELSE
  249.             /* NON PKP */
  250.             /*
  251.              * insert ke tabel finance invoice ar
  252.              * dengan status_doc = I dan workflow_status = APPROVED
  253.              *
  254.              */
  255.             INSERT INTO fi_invoice_ar(
  256.                 invoice_ar_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
  257.                 partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
  258.                 due_date, curr_code, total_tax_base_amount, total_amount, tax_amount,
  259.                 remark, status_doc, workflow_status, version, create_datetime,
  260.                 create_user_id, update_datetime, update_user_id)
  261.             SELECT vInvoiceArId, A.tenant_id, vDocTypeDebtNoteAr, A.doc_no, A.doc_date, C.ou_parent_id,
  262.                 D.partner_id, A.doc_no, A.doc_date, vEmptyValue, vEmptyValue,
  263.                 B.payment_due_date, vCurrency, vNol, vNol, vNol,
  264.                 A.remark, vStatusDoc, vWorkFlowStatus, vNol, pDatetime,
  265.                 pUserId, pDatetime, pUserId
  266.             FROM i_trx_pos A
  267.             INNER JOIN i_trx_pos_ext E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
  268.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  269.             INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
  270.             INNER JOIN m_partner D ON E.partner_id = D.partner_id AND E.tenant_id = D.tenant_id
  271.             WHERE A.tenant_id = pTenantId
  272.                 AND A.process_no = pProcessNo
  273.                 AND A.ou_id = pOuId
  274.                 AND A.trx_pos_id = pTrxPosId
  275.                 AND B.under_payment_amount > 0
  276.             ORDER BY doc_date;
  277.        
  278.                
  279.             /* insert fi_invoice_ar_cost */
  280.             INSERT INTO fi_invoice_ar_cost(
  281.                 tenant_id, invoice_ar_id, line_no, activity_gl_id,
  282.                 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
  283.                 remark, version, create_datetime, create_user_id, update_datetime,
  284.                 update_user_id, segment_id)
  285.             SELECT A.tenant_id, C.invoice_ar_id, ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
  286.                 vEmptyValue, vCurrency, B.under_payment_amount, vEmptyValue, vNol, vNol,
  287.                 A.remark, vNol, pDatetime, pUserId, pDatetime,
  288.                 pUserId, vEmptyValue
  289.             FROM i_trx_pos A
  290.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  291.             INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
  292.             --INNER JOIN m_tax D ON A.tenant_id = D.tenant_id
  293.             INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
  294.             WHERE A.tenant_id = pTenantId
  295.                 AND A.process_no = pProcessNo
  296.                 AND A.ou_id = pOuId
  297.                 AND A.trx_pos_id = pTrxPosId;
  298.            
  299.             /*
  300.              * insert fi_invoice_ar_balance
  301.              * untuk saldo DP partner corporate yg bayar sebagian
  302.              * untuk yang Non PKP Saldo DP, flg_payment = Y dan payment_amount = amount
  303.              */
  304.             INSERT INTO fi_invoice_ar_balance(
  305.                     invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
  306.                     doc_no, doc_date, ext_doc_no, ext_doc_date, ref_doc_type_id,
  307.                     ref_id, partner_id, due_date, curr_code, amount, remark, payment_amount,
  308.                     flg_payment, version, create_datetime, create_user_id, update_datetime,
  309.                     update_user_id)
  310.             SELECT vInvoiceArBalanceId, A.tenant_id, c.ou_parent_id, vDocTypeDPBalance, F.invoice_ar_id,
  311.                 A.doc_no, A.doc_date, vSpasiChar, vSpasiChar, A.doc_type_id,
  312.                 A.trx_pos_id, E.partner_id,
  313.                 -- rev by Ping An 16 Nov 2015, due_date ambil dari docdatenya POS karena DP sudah kejadian dan sudah approved  B.payment_due_date,
  314.                 A.doc_date,
  315.                 A.curr_code, -1 * A.total_payment, vSpasiChar, -1 * A.total_payment,
  316.                 vYes, vNol, pDatetime, pUserId, pDatetime,
  317.                 pUserId
  318.             FROM i_trx_pos A
  319.             INNER JOIN i_trx_pos_ext E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
  320.             INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  321.             INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
  322.             INNER JOIN fi_invoice_ar F ON A.doc_no = F.doc_no AND A.doc_date = F.doc_date AND A.tenant_id = F.tenant_id AND F.doc_type_id = vDocTypeDebtNoteAr
  323.             WHERE A.tenant_id = pTenantId
  324.                 AND A.process_no = pProcessNo
  325.                 AND A.ou_id = pOuId
  326.                 AND A.trx_pos_id = pTrxPosId
  327.                 AND B.payment_amount > 0;
  328.                 -- B.payment_amount > 0 untuk cek apakah ada yang dibayar          
  329.                 -- F.invoice_ar_id : unique index -> fi_invoice_ar_balance adalah tenant_id, doc_type_id, dan invoice_ar_id.
  330.                 -- jadi tidak dipantek -99, krn pasti akan kena error unique constraint
  331.        
  332.         END IF;
  333.        
  334.         /* generate_process_message_for_submit_doc */
  335.         /**
  336.          * mod by Didit, 29 Nov 2016
  337.          * Debt Note AR langsung approved, tidak pedulikan flag PKP
  338.          */
  339.         --IF pFlgPkp = vNo THEN
  340.        
  341.         PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName, B.invoice_ar_id ||'_'||B.doc_no,
  342.                 pDatetime, vParamKey, B.invoice_ar_id::character varying, pUserId)
  343.         FROM i_trx_pos A
  344.         INNER JOIN fi_invoice_ar B ON A.tenant_id = B.tenant_id AND A.doc_no = B.doc_no AND A.doc_date = B.doc_date AND B.doc_type_id = vDocTypeDebtNoteAr
  345.         WHERE A.tenant_id = pTenantId
  346.             AND A.process_no = pProcessNo
  347.             AND A.ou_id = pOuId
  348.             AND A.trx_pos_id = pTrxPosId;
  349.        
  350.         -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
  351.         SELECT awe_flow_id INTO vFlowArDebtNoteId
  352.         FROM awe_flow
  353.         WHERE scheme = vSchemeDebtNoteAr AND
  354.             flg_validate = 'Y' AND
  355.             active = 'Y';
  356.        
  357.          -- Generate data awe_currdoc_status
  358.         INSERT INTO awe_currdoc_status(
  359.             req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
  360.             remark, current_user_id, current_role_id, flg_user_role, label,
  361.             data, flow_id, create_datetime, create_user_id, create_role_id,
  362.             update_datetime, update_user_id, update_role_id, version)
  363.         SELECT A.invoice_ar_id||'_'||A.doc_no, A.tenant_id, vSchemeDebtNoteAr, A.invoice_ar_id, A.doc_no, A.doc_date, vWorkFlowStatus,
  364.             A.remark, A.create_user_id, vEmptyValue, 'R', 'AR DEBT NOTE'||A.doc_no,
  365.             '{}', vFlowArDebtNoteId, pDatetime, pUserId, vEmptyValue,
  366.             pDatetime, pUserId, vEmptyValue, 0
  367.         FROM fi_invoice_ar A
  368.         WHERE A.invoice_ar_id = vInvoiceArId
  369.             AND A.status_doc = 'R';
  370.        
  371.         --END IF;
  372.        
  373.         /**
  374.          * mod By Didit, 1 Des 2016
  375.          * untuk alokasi otomatis saldo DP ke debt note ar
  376.          */
  377.         IF EXISTS(SELECT 1 FROM fi_invoice_ar_balance WHERE invoice_ar_balance_id = vInvoiceArBalanceId) THEN
  378.             PERFORM f_automatic_credit_alloc_ar(pSessionId, pTenantId, vInvoiceArId, vInvoiceArBalanceId, pUserId, pDatetime);
  379.         END IF;
  380.        
  381.     END IF;
  382.    
  383. END
  384. $BODY$
  385.   LANGUAGE plpgsql VOLATILE
  386.   COST 100;
  387.   /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top