1 PACKAGE BODY igs_fi_prc_1098t_data AS
2 /* $Header: IGSFI92B.pls 120.0 2005/09/09 19:03:52 appldev noship $ */
3 /************************************************************************
4 Created By : Umesh Udayaprakash
5 Date Created By : 1-May-2005
6 Purpose : Package Used in 1098t pdf file generation and EFT file generation
7 java concurrent program.
8
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 *************************************************************************/
13 PROCEDURE contactdetails ( p_n_party_site_id IN hz_party_sites.party_site_id%type,
14 p_n_relationship_id IN hz_relationships.relationship_id%type,
15 p_n_contact_point_id IN hz_contact_points.contact_point_id%type,
16 p_v_phone_country_code OUT NOCOPY hz_contact_points.phone_country_code%type,
17 p_v_phone_area_code OUT NOCOPY hz_contact_points.phone_area_code%type,
18 p_v_phone_number OUT NOCOPY hz_contact_points.phone_number%type,
19 p_v_phone_extension OUT NOCOPY hz_contact_points.phone_extension%type,
20 p_v_con_party_name OUT NOCOPY hz_parties.party_name%type,
21 p_v_party_name OUT NOCOPY hz_parties.party_name%type,
22 p_v_email_address OUT NOCOPY hz_parties.email_address%type,
23 p_v_country OUT NOCOPY hz_locations.country%type,
24 p_v_address1 OUT NOCOPY hz_locations.address1%type,
25 p_v_address2 OUT NOCOPY hz_locations.address2%type,
26 p_v_address3 OUT NOCOPY hz_locations.address3%type,
27 p_v_address4 OUT NOCOPY hz_locations.address4%type,
28 p_v_city OUT NOCOPY hz_locations.city%type,
29 p_v_postal_code OUT NOCOPY hz_locations.postal_code%type,
30 p_v_state OUT NOCOPY hz_locations.state%type,
31 p_v_province OUT NOCOPY hz_locations.province%type,
32 p_v_county OUT NOCOPY hz_locations.county%type
33 ) as
34 /************************************************************************
35 Created By : Umesh Udayaprakash
36 Date Created By : 1-May-2005
37 Purpose : Package Used in 1098t pdf file generation java concurrent program
38 Known limitations,enhancements,remarks:
39 Change History
40 Who When What
41 *************************************************************************/
42 CURSOR c_party_loc_id (c_n_party_site_id in hz_party_sites.party_site_id%TYPE ) IS
43 SELECT party_id ,location_id
44 FROM hz_party_sites WHERE PARTY_SITE_ID =c_n_PARTY_SITE_ID;
45 rec_party_loc_id c_party_loc_id%rowtype;
46
47
48 CURSOR c_contact_name (c_n_party_id in hz_parties.party_id%TYPE ) IS
49 SELECT party_name FROM
50 hz_parties
51 WHERE party_id = c_n_party_id;
52
53 CURSOR c_location_details (c_n_LOCATION_ID in HZ_LOCATIONS.LOCATION_ID%TYPE ) IS
54 SELECT country,address1,address2,address3,address4,city,
55 postal_code,state,province,county
56 FROM hz_locations
57 WHERE location_id = c_n_LOCATION_ID;
58
59 CURSOR c_name_mailid IS
60 SELECT hp_name.party_name, hp_email.email_address
61 FROM hz_parties hp_name, hz_relationships hr, hz_parties hp_email
62 WHERE hr.relationship_id = p_n_relationship_id
63 AND hr.directional_flag = 'B'
64 AND hp_name.party_id = hr.object_id
65 AND hr.party_id = hp_email.party_id;
66
67
68 CURSOR c_phone_details IS
69 SELECT phone_country_code, phone_area_code, phone_number,phone_extension
70 FROM hz_contact_points
71 WHERE contact_point_id = p_n_contact_point_id;
72 BEGIN
73
74 OPEN c_party_loc_id(p_n_party_site_id);
75 FETCH c_party_loc_id INTO rec_party_loc_id;
76
77 IF c_party_loc_id%FOUND THEN
78
79 OPEN c_contact_name(rec_party_loc_id.party_id);
80 FETCH c_contact_name INTO p_v_con_party_name;
81 CLOSE c_contact_name;
82
83 OPEN c_location_details(rec_party_loc_id.LOCATION_ID);
84 FETCH c_location_details INTO p_v_country,p_v_address1,p_v_address2,p_v_address3,p_v_address4,p_v_city,p_v_postal_code,p_v_state, p_v_province,p_v_county;
85 CLOSE c_location_details;
86 END IF;
87 CLOSE c_party_loc_id;
88 IF p_n_relationship_id is NOT NULL THEN
89 OPEN c_name_mailid;
90 FETCH c_name_mailid INTO p_v_party_name,p_v_email_address;
91 CLOSE c_name_mailid;
92 END IF;
93
94 IF p_n_contact_point_id is NOT NULL THEN
95 OPEN c_phone_details;
96 FETCH c_phone_details INTO p_v_phone_country_code,p_v_phone_area_code,p_v_phone_number,p_v_phone_extension;
97 CLOSE c_phone_details;
98 END IF;
99 END contactdetails;
100
101 END igs_fi_prc_1098t_data;
[Home] [Help]