abirama62

simple_calc

May 14th, 2020
234
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION sl_automatic_create_sales_invoice_from_temp_sales_invoice(
  2.     character varying,
  3.     bigint,
  4.     bigint,
  5.     character varying,
  6.     bigint,
  7.     bigint)
  8.   RETURNS void AS
  9. $BODY$
  10. DECLARE
  11.     pSessionId              alias for $1;
  12.     pTenantId               alias for $2;
  13.     pUserId                 alias for $3;
  14.     pDatetime               alias for $4;
  15.     pSoId                   alias for $5;
  16.     pDoId                   alias for $6;
  17.  
  18.     vParamKey               character varying(30) := 'salesInvoiceId';
  19.     vProcessName            character varying(30) := 'sl_submit_sales_invoice';
  20.     vEmptyId                bigint := -99;
  21.     vEmptyString            character varying(1) :='';
  22.     vInvoiceId              bigint;
  23.     vDocTypeSalesInvoice    bigint:= 321;
  24.     vDocTypeSalesOrder      bigint:= 301;
  25.     vFlowInvoiceId          bigint;
  26.     vDueDate                bigint := 90;
  27.     vSchemeSalesInvoice     character varying:= 'FC01';
  28.     vFlagYes                character varying:= 'Y';
  29.     vWorkFlowStatusApproved character varying:= 'APPROVED';
  30.     vStatusDocApproved      character varying:= 'R';
  31.     vInvoiceTempId          bigint;
  32.     vReceiptDate                character varying(8);
  33.     vStatusRejectedAll  character varying:= 'DITOLAK_SEMUA';
  34.   vStatusFailOnDelivery character varying:='FAIL_ON_DELIVERY';
  35.    
  36. BEGIN
  37.    
  38.     SELECT NEXTVAL('sl_invoice_seq') INTO vInvoiceId;
  39.    
  40.     SELECT invoice_temp_id INTO vInvoiceTempId
  41.     FROM sl_invoice_temp
  42.     WHERE ref_id = pSoId
  43.     AND ref_doc_type_id = vDocTypeSalesOrder
  44.     AND status_doc = vStatusDocApproved;
  45.  
  46.     SELECT A.receipt_date INTO vReceiptDate
  47.     FROM in_finalisasi_pengiriman_item A
  48.     INNER JOIN sl_do B ON A.ref_id = B.do_id AND A.ref_doc_type_id = B.doc_type_id
  49.     WHERE A.receipt_status <> vStatusFailOnDelivery AND
  50.     A.receipt_status <> vStatusRejectedAll AND
  51.     B.do_id = pDoId
  52.     UNION
  53.     SELECT A.doc_date
  54.     FROM in_do_receipt A
  55.     INNER JOIN sl_do B ON A.ref_id = B.do_id AND A.ref_doc_type_id = B.doc_type_id
  56.     AND B.do_id = pDoId;
  57.  
  58.     SELECT TO_CHAR(to_date(vReceiptDate, 'YYYYMMDD') + B.term_of_payment * interval '1 days ', 'YYYYMMDD') INTO vDueDate
  59.     FROM sl_do A
  60.     INNER JOIN sl_so B ON A.ref_id = B.so_id
  61.     WHERE A.do_id = pDoId;
  62.    
  63.     --insert ke dalam sl invoice (UBBAH
  64.     INSERT INTO sl_invoice(
  65.             invoice_id,tenant_id, doc_type_id, doc_no, doc_date, ou_id,
  66.             ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
  67.             ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
  68.             advance_amount, tax_amount, add_amount, total_amount, status_doc,
  69.             workflow_status, version, create_datetime, create_user_id, update_datetime,
  70.             update_user_id, ref_inv_temp_id, discount_amount)
  71.     SELECT  vInvoiceId, pTenantId, vDocTypeSalesInvoice, A.inv_doc_no, A.inv_doc_date, A.ou_id,
  72.             A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
  73.             A.ou_legal_id, A.vDueDate, A.salesman_id, A.curr_code, ROUND(A.gross_amount,0),
  74.             A.advance_amount, ROUND(A.tax_amount,0), A.add_amount, ROUND(A.total_amount,0), A.status_doc,
  75.             A.workflow_status, A.version, pDatetime, pUserId, pDatetime,
  76.             pUserId, A.invoice_temp_id, A.discount_amount
  77.     FROM    sl_invoice_temp A
  78.     WHERE   A.invoice_temp_id = vInvoiceTempId;
  79.    
  80.     --insert ke dalam sl invoice item
  81.     INSERT INTO sl_invoice_item(
  82.             tenant_id, invoice_id, line_no, ref_doc_type_id,
  83.             ref_id, ref_item_id, ref_item_amount, remark, version, create_datetime,
  84.             create_user_id, update_datetime, update_user_id, do_receipt_item_id)
  85.     SELECT  pTenantId, vInvoiceId, ROW_NUMBER() OVER (PARTITION BY vInvoiceId), A.ref_doc_type_id,
  86.             A.ref_id, A.ref_item_id, ROUND(A.ref_item_amount,0), A.remark, A.version, pDatetime,
  87.             pUserId, pDatetime, pUserId, A.do_receipt_item_id
  88.     FROM    sl_invoice_temp_item A
  89.     WHERE   A.ref_id = pDoId
  90.     AND     A.invoice_temp_id = vInvoiceTempId;
  91.    
  92.     --insert ke dalam sl invoice tax
  93.     INSERT INTO sl_invoice_tax(
  94.             tenant_id, invoice_id, tax_id, flg_amount, tax_percentage,
  95.             base_amount, tax_amount, tax_no, tax_date, tax_curr_code, gov_tax_amount,
  96.             remark, version, create_datetime, create_user_id, update_datetime,
  97.             update_user_id, advance_amount, total_tax_disc_amount)
  98.     SELECT  pTenantId, vInvoiceId, A.tax_id, A.flg_amount, A.tax_percentage,
  99.             ROUND(A.base_amount,0), ROUND(A.tax_amount,0), A.tax_no, A.tax_date, A.tax_curr_code, ROUND(A.gov_tax_amount,0),
  100.             A.remark, A.version, pDatetime, pUserId, pDatetime,
  101.             pUserId, A.advance_amount, ROUND(A.total_tax_disc_amount,0)
  102.     FROM    sl_invoice_temp_tax A
  103.     WHERE   A.invoice_temp_id = vInvoiceTempId;
  104.    
  105.     --perform generate process message for submit doc sales invoice
  106.     --insert process message
  107.     PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName, vInvoiceId ||'_'||A.doc_no,
  108.                 pDatetime, vParamKey, vInvoiceId::character varying, pUserId)
  109.     FROM sl_invoice A
  110.     WHERE A.invoice_id = vInvoiceId;
  111.    
  112.     -- Mendapatkan default approval flow ID yang dipakai dari sysconfig untuk DO
  113.     SELECT awe_flow_id INTO vFlowInvoiceId
  114.     FROM awe_flow
  115.     WHERE scheme = vSchemeSalesInvoice
  116.     AND flg_validate = vFlagYes
  117.     AND active = vFlagYes
  118.     AND tenant_id = pTenantId;
  119.        
  120.     --insert awe_currdoc_status SI
  121.     INSERT INTO awe_currdoc_status(
  122.         req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
  123.         remark, current_user_id, current_role_id, flg_user_role, label,
  124.         data, flow_id, create_datetime, create_user_id, create_role_id,
  125.         update_datetime, update_user_id, update_role_id, version)
  126.     SELECT  vInvoiceId||'_'||A.doc_no, A.tenant_id, vSchemeSalesInvoice, vInvoiceId, A.doc_no, A.doc_date, vWorkFlowStatusApproved,
  127.             A.remark, A.create_user_id, vEmptyId, 'R', 'SALES INVOICE'||A.doc_no,
  128.             '{}', vFlowInvoiceId, pDatetime, pUserId, vEmptyId,
  129.             pDatetime, pUserId, vEmptyId, 0
  130.     FROM sl_invoice A
  131.     WHERE A.invoice_id = vInvoiceId;
  132.            
  133. END
  134. $BODY$
  135.   LANGUAGE plpgsql VOLATILE
  136.   COST 100;
  137.   /
RAW Paste Data