aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
address_type	text	OUT
customer_id	integer	OUT
customer_name	text	OUT
customer_address	text	OUT
customer_tax_id	text	OUT
customer_branch	text	OUT
customer_mobile	text	OUT
customer_phone	text	OUT
customer_email	text	OUT
country_code	text	OUT
buyer_type	text	OUT

Definition

declare
    is_group boolean;
    v_guest_id int;
    v_channel_id int;
    v_group_id int;
    v_folio_type char;
    v_register_id int;
    v_booking_id int;
begin
  select folio_type, register_id, booking_id
  from folio where id = $1
  into v_folio_type, v_register_id, v_booking_id;

 /* select r.channel_id, r.group_id, f.folio_type
  from folio f on f.register_id = r.id
  where r.id = $1
  into is_group, v_guest_id, v_channel_id, v_group_id, v_folio_type;*/

/*
Buyer Type
"CCPT"  "Foreigner"
"NIDN"  "Individual"
"OTHR"  "Not provider information"
"TXID"  "Corporation"*/
  v_guest_id := case v_folio_type
                    when 'R' then (select r.guest_id from registration r where r.id = v_register_id) -- Guest Folio
                    when 'B' then (select b.guest_id from booking b where b.id = v_booking_id) -- Deposit Folio
                end;

  if v_folio_type in ('R','B') then
        select g.full_name, trim(g.address1||coalesce(' '|| g.address2, '')), c.code2,
            g.doc_number, 'P', 10000000+g.id, g.emails[1], g.mobiles[1], g.telephones[1],
            case g.doc_type
                when 1 then 'CCPT'
                when 2 then 'NIDN'
                else 'OTHR'
            end
        from guest g left join country c on c.id = g.country_id
        where g.id = v_guest_id
        into customer_name, customer_address, country_code, customer_tax_id, address_type,
             customer_id, customer_email, customer_mobile, customer_phone, buyer_type;
  elsif v_folio_type = 'C' then
        select IIF(cs.company is not null, cs.company, coalesce(cn.company_name, cn.name)),
            case when cs.company is not null
               then trim(cs.address1||coalesce(' '|| cs.address2, ''))
               else trim(cn.contact_addr1||coalesce(' '|| cn.contact_addr2, ''))
            end,  
            IIF(cs.company is not null, c1.code2, c2.code2),
            IIF(cs.company is not null, cs.tax_id, cn.tax_id),
            IIF(cs.company is not null, cs.branch_no, cn.branch_no),
            IIF(IIF(cs.company is not null, cs.branch_no, cn.branch_no) ='0', 'H', 'B'),
            IIF(cs.company is not null, 30000000+cs.folio_id, 40000000+cn.id),
            IIF(cs.company is not null, null, cn.contact_email),
            IIF(cs.company is not null, null, cn.contact_mobile),
            IIF(cs.company is not null, null, cn.contact_telephone),
            IIF(cs.company is not null or cs.channel_id is not null, 'TXID', 'OTHR')
        from cash_sale cs
            left join channel cn on cn.id = cs.channel_id
            left join country c1 on c1.id = cs.country_id
            left join country c2 on c2.id = cn.contact_country_id
        where cs.folio_id = $1
        into customer_name, customer_address, country_code, customer_tax_id, customer_branch, address_type,
             customer_id, customer_email, customer_mobile, customer_phone, buyer_type;

  elsif v_folio_type =  'G' then -- Group Folio
        /*select coalesce(cn.company_name, cn.name), cn.contact_addr1, cn.contact_addr2,
           c.code2, cn.tax_id, cn.branch_no,
           iif(coalesce(cn.branch_no,'0') ='0', 'H', 'B'),
           40000000 + cn.id
        From channel cn left join country c on c.id = cn.contract_country_id
        where cn.id = v_channel_id
        into customer_name, customer_address1, customer_address2, country_code, customer_tax_id, customer_branch, addr_type, customer_id;

        if (customer_name is null) then
           select g.name, iif(:acctype = 'G', 30000000, 40000000)+grpno
           from group_info g where g.id = v_group_id
           into :name, :cus_id;*/
     else
  end if;
end

sp_get_vat_address

Parameters

Definition