BACKUP REPORT CLAIM CONSIGNMENT

CREATE OR REPLACE FUNCTION r_claim_consignment(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying, character varying)
 RETURNS SETOF refcursor AS
$BODY$
DECLARE
    vRefHeader          REFCURSOR := 'refHeader';
    vRefDetail          REFCURSOR := 'refDetail';

    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;

    pOu                 ALIAS FOR $6;
    pStatusDoc          ALIAS FOR $7;
    pDocNo              ALIAS FOR $8;
    pDateFrom           ALIAS FOR $9;
    pDateTo             ALIAS FOR $10;
    pCustomer           ALIAS FOR $11;

    vFilterStatusDoc    text;
    vFilterDocNo        text;
    vFilterCustomer     text;

    vEmptyString        character varying := ' ';
    vFlgInProgress      character varying := 'I';
    vStatusInProgress   character varying := 'In Progress';
    vFlgDraft           character varying := 'D';
    vStatusDraft        character varying := 'Draft';
    vFLgApprove         character varying := 'R';
    vStatusApprove      character varying := 'Approve';
    vDocTypeClaimConsignment bigint := 561;

BEGIN

    IF (pStatusDoc <> vEmptyString) THEN
        vFilterStatusDoc := ' AND UPPER(A.status_doc) LIKE UPPER(''%' || pStatusDoc || '%'') ';
    END IF;

    IF (pDocNo <> vEmptyString) THEN
        vFilterDocNo := ' AND UPPER(A.doc_no) LIKE UPPER(''%' || pDocNo || '%'') ';
    END IF;

    IF (pCustomer <> vEmptyString) THEN
        vFilterCustomer := ' AND ( UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER(''%' || pCustomer || '%'')
                                OR UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER(''%' || pCustomer || '%'') )';
    END IF;

    OPEN vRefHeader FOR
        SELECT f_get_ou_name(pOu) AS ou_name, pDateFrom AS date_from, pDateTo AS date_to, pDatetime AS datetime,
            f_get_username(pUserId) AS username,
            CASE WHEN pStatusDoc = vFlgInProgress THEN vStatusInProgress
                WHEN pStatusDoc = vFlgDraft THEN vStatusDraft
                WHEN pStatusDoc = vFLgApprove THEN vStatusApprove ELSE 'ALL' END AS status_doc,
            CASE WHEN pDocNo = vEmptyString THEN 'ALL' ELSE pDocNo END AS doc_no,
            CASE WHEN pCustomer = vEmptyString THEN 'ALL' ELSE pCustomer END AS customer_name;

    RETURN NEXT vRefHeader;


    OPEN vRefDetail FOR
    EXECUTE '
        SELECT A.doc_date AS claim_consignment_date, A.doc_no AS claim_consignment_no, C.doc_date AS po_date,
            C.doc_no AS po_no, D.doc_date AS rg_consignment_date, D.doc_no AS rg_consignment_no,
            f_get_partner_name(A.partner_id) AS partner_name, f_get_partner_code(A.partner_id) AS partner_code,
            f_get_product_name(B.product_id) AS product_name, f_get_product_code(B.product_id) AS product_code,
            SUM(B.qty_realization) AS qty, f_get_uom_name(B.base_uom_id) AS base_uom,
            CASE WHEN A.status_doc = '''|| vFlgInProgress ||''' THEN '''|| vStatusInProgress ||'''
                WHEN A.status_doc = '''|| vFlgDraft ||''' THEN '''|| vStatusDraft ||''' ELSE '''|| vStatusApprove ||''' END AS status_doc
        FROM in_inventory A
        INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
        INNER JOIN pu_po C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.po_id
        INNER JOIN pu_receive_goods_consignment D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.po_id
        WHERE A.tenant_id = $1 AND
            A.ou_from_id = $2 AND
            A.doc_type_id = $3 AND
            A.doc_date BETWEEN $4 AND $5 '
                || vFilterDocNo
                || vFilterCustomer
                || vFilterStatusDoc ||'
        GROUP BY A.doc_date, A.doc_no, C.doc_date, C.doc_no, D.doc_date, D.doc_no, A.partner_id, B.product_id, B.base_uom_id, A.status_doc
        ORDER BY A.doc_date'
    USING pTenantId, pOu, vDocTypeClaimConsignment, pDateFrom, pDateTo;

    RETURN NEXT vRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /