aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN

Definition

WITH details AS (
        SELECT i.folio_seq, i.folio_name, i.item_id,  
            i.charge_to_folio AS link_folio_id, link.folio_type AS link_folio_type,
           CASE link.folio_type 
                WHEN 'R' THEN 
                    (SELECT full_name FROM registration rg LEFT JOIN guest g ON g.id = rg.guest_id where rg.id = link.register_id) 
                WHEN 'B' THEN
                    (SELECT full_name FROM booking bk LEFT JOIN guest g ON g.id = bk.guest_id where bk.id = link.booking_id)  
                WHEN 'C' THEN 
                    (SELECT name FROM cash_sale cs where cs.folio_id = link.id)
            END AS link_folio_name, i.note, 
            (SELECT coalesce(array_agg(row_to_json(dep)), '{}')  
             FROM (SELECT f.dept_id AS id , d.name 
                   FROM folio_details F left join department D on D.id = F.dept_id
                   where F.folio_id = i.folio_id and F.folio_seq = i.folio_seq  
                  ) AS dep
            ) AS departments                  
        FROM folio_items i LEFT JOIN folio link ON link.id = i.charge_to_folio             
        WHERE i.folio_id  = $1 AND NOT i.closed   
    )
    SELECT row_to_json(J0) 
    FROM (
        SELECT f.folio_pattern_id, p.name AS folio_pattern_name, f.split_room_charge,
             (SELECT row_to_json(details) FROM details WHERE folio_seq = 1) AS folio1,
             (SELECT row_to_json(details) FROM details WHERE folio_seq = 2) AS folio2,
             (SELECT row_to_json(details) FROM details WHERE folio_seq = 3) AS folio3,
             (SELECT row_to_json(details) FROM details WHERE folio_seq = 4) AS folio4
        from folio F LEFT JOIN folio_pattern P ON p.id = f.folio_pattern_id 
        where (f.id = $1) 
        order by 1
    ) J0

sp_get_posting_folio_patterns_js

Description

Parameters

Definition