- CREATE OR REPLACE FUNCTION xi_set_sales_invoice_delivered(bigint, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- pRefPayloadData REFCURSOR := 'refPayloadData';
- vOuCode character varying;
- vSoId bigint;
- vParameterCode character varying:='API.UPDATE.PI.NON.BE.URL';
- vUrlApi character varying;
- vParameterCodeApiKey character varying:='API.KEY.FOR.API.KEMITRAAN';
- vApiKey character varying;
- vApiName character varying:='Update Pi Non BE';
- vProcessId bigint;
- vPengirimanId bigint;
- vDoId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- BEGIN
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'xi_set_sales_invoice_delivered' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vPengirimanId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'pengirimanId';
- 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';
- SELECT f_get_value_system_config_by_param_code(pTenantId, vParameterCode) INTO vUrlApi;
- SELECT f_get_value_system_config_by_param_code(pTenantId, vParameterCodeApiKey) INTO vApiKey;
- Open pRefPayloadData FOR
- SELECT row_to_json(A)::character varying
- FROM(
- SELECT pSessionId AS session_uuid, vUrlApi AS url, vApiName AS api_name, vApiKey AS api_key, B.tenant_code AS tenant_code,
- A.update_datetime AS datetime, f_get_username(A.update_user_id) AS username, vUserId AS user_id,
- (
- SELECT array_to_json(array_agg(row_to_json(A)))
- FROM (
- SELECT f_get_ou_code(A.ou_id) AS ou_code,
- A.doc_no AS doc_no, A.doc_date AS doc_date,
- B.doc_no AS so_doc_no, B.doc_date AS so_doc_date, A.due_date AS due_date
- FROM sl_invoice_temp A
- JOIN sl_so B ON A.ref_id = B.so_id AND A.tenant_id = B.tenant_id AND B.ref_doc_type_id = 370
- JOIN sl_do C ON B.so_id = C.ref_id AND B.doc_type_id = C.ref_doc_type_id
- JOIN in_pengiriman_item D ON C.do_id = D.ref_id AND C.doc_type_id = D.ref_doc_type_id
- WHERE A.status_doc <> 'V'
- AND A.tenant_id = pTenantId
- AND D.pengiriman_id = vPengirimanId
- ) A
- ) AS pi_list
- FROM in_pengiriman A
- JOIN t_tenant B ON A.tenant_id = B.tenant_id
- WHERE A.pengiriman_id = vPengirimanId
- AND A.tenant_id = pTenantId
- ) A;
- RETURN NEXT pRefPayloadData;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /