DSXchange

how to do coding for this plsql code in Datastage

'CREATE OR REPLACE PROCEDURE "ASSIGN_PRODUCT_1"
( p_new_doc_id in integer,
p_pb_available in varchar2,
p_section_id in varchar2,
p_electronic_location varchar2,
p_org_id organisations.org_company_no%type)
is
-----------------------------------------------------------------------------------
v_format_id integer;
v_format_type_id v8user.format_types.format_type_id%TYPE;
v_pf_id v8user.product_families.pf_id%TYPE;
v_format_type_code v8user.format_types.format_type_code%TYPE;
v_true_location v8user.documents.doc_hfis_electronic_location%TYPE;
v_available v8user.product_breakdowns.pb_available%TYPE;
v_count integer;
begin
insert into trg_message values ('Enter assign 1');

select count(*) into v_count from product_sections where section_id = p_section_id;

if v_count = 0 then
raise_application_error(-20003, 'section id '||p_section_id||' does not exist');
else


select pf_pf_id into v_pf_id from product_sections
where section_id = p_section_id;

select ft_format_type_id into v_format_type_id from product_families
where pf_id = v_pf_id;

select count(*) into v_count from format_types
where format_type_id = v_format_type_id;

if v_count = 0 then
v_format_type_id := 17;
end if;
end if;

select format_type_code into v_format_type_code from
format_types where format_type_id = v_format_type_id;

select count(*) into v_count from formats where
doc_doc_id = p_new_doc_id and ft_format_type_id = v_format_type_id;

if v_count = 0 then
v_true_location := p_electronic_location;
/*if v_format_type_id = 17 then
if v_doc_location is not null
and v_doc_location != v_true_location
then v_true_location := v_doc_location;
end if; --req to use location from documents table
end if; -- format type id 17 identified */

select seq_next_format_id.nextval into v_format_id
from dual;
insert into trg_message values ('New format id '||v_format_id);

insert into formats
( format_id,
format_available,
doc_doc_id,
ft_format_type_id,
ft_format_type_code,
format_electronic_location,
format_created_on,
format_created_by)
values
( v_format_id,
'N',
p_new_doc_id,
v_format_type_id,
v_format_type_code,
v_true_location,
sysdate,
user);
else
select format_id into v_format_id from formats where
doc_doc_id = p_new_doc_id and ft_format_type_id = v_format_type_id;
end if;

SELECT count(*) INTO v_count FROM product_breakdowns
WHERE format_format_id = v_format_id
AND pb_available = 'Y';
IF v_count > 0 THEN
v_available := 'Y';
ELSE
v_available := 'N';
END IF;


if (p_org_id != 20406 and p_section_id = 'IOBS') or
(p_org_id != 30751 and p_section_id = 'IONS') --or

then
raise_application_error(-20003,'Invalid combination of standards organisation and section');
end if;

if (p_org_id != 20505 and p_section_id = 'OFPA') then
raise_application_error(-20003,'Cannot assign this document to the OFPA section. Only FPA documents can be assigned to the OFPA section.');
end if;

if (p_org_id = 20505 and p_section_id != 'OFPA' and v_pf_id in ('OO','IO','HI','MO') ) then
raise_application_error(-20003,'Cannot assign a FPA document to this section of OHSIS. FPA documents in OHSIS can only be assigned to the OFPA section.');
end if;


insert into trg_message values ('Call gofa assign. Format id = '||v_format_id);
if v_pf_id = 'OO' then
geog_priority_util.gofa_assign (p_new_doc_id, p_section_id, v_format_id, v_available);
else
select count(*) into v_count from product_breakdowns where
format_format_id = v_format_id and
section_section_id = p_section_id;

if v_count = 0 then
insert into product_breakdowns
( pb_id,
format_format_id,
section_section_id,
pb_available)
values
( seq_next_prodbreak_id.nextval,
v_format_id,
p_section_id,
v_available);

else
update product_breakdowns set pb_available = v_available where
format_format_id = v_format_id and
section_section_id = p_section_id;
end if;
end if;
end;

Thanks in advance

In Ray's words:

We at DSXchange are not in the business of supplying a complete solution (though a number of people here are in that business, but only for money).

How about you give some thought to how YOU might go about it (apart from requesting/demanding that someone else solves your problems)?

Now in my words:

Think about it, then use your mind and hands to code it in datastage. In case you have any problems post it here.

Hint: Procedure logic is not as complex as you think.

priyadarshikunal wrote:In Ray's words:

We at DSXchange are not in the business of supplying a complete solution (though a number of people here are in that business, but only for money).

How about you give some thought to how YOU might go about it (apart from requesting/demanding that someone else solves your problems)?

Now in my words:

Think about it, then use your mind and hands to code it in datastage. In case you have any problems post it here.

Hint: Procedure logic is not as complex as you think.

Thanks priyadarshikunal.
Sorry,I am not demanding anyone.I am requesting you to give me some idea to solve this.

Write out a specification in English, since not everyone reads PL/SQL code. This may also help you to determine what you want to achieve, by forcing you to focus your thoughts. Think about testing your algorithm; test values, expected and actual results.