[Home] [Help]
PACKAGE BODY: APPS.PON_ADD_UTIL_PKG
Source
1 PACKAGE BODY PON_ADD_UTIL_PKG as
2 /*$Header: PONADDUTB.pls 120.0 2005/06/01 14:09:45 appldev noship $ */
3
4 -- Given location id, retrieve the address information
5
6 PROCEDURE retrieve_hr_address (
7 p_location_id IN NUMBER
8 , x_address OUT NOCOPY hrRefAddressCurTyp
9 , x_status OUT NOCOPY VARCHAR2
10 , x_exception_msg OUT NOCOPY VARCHAR2
11 )
12 IS
13
14 BEGIN
15
16 OPEN x_address FOR
17
18 SELECT
19 hl.location_code
20 , hl.address_line_1
21 , hl.address_line_2
22 , hl.town_or_city
23 , hl.region_2
24 , hl.region_3
25 , hl.postal_code
26 , hl.postal_code
27 , hl.country
28 , hl.region_1
29 , hl.bill_to_site_flag
30 , hl.ship_to_site_flag
31 , hl.receiving_site_flag
32
33 FROM hr_locations hl
34
35 WHERE hl.location_id = p_location_id;
36
37 x_status :='S';
38 x_exception_msg :=NULL;
39
40 EXCEPTION
41
42 WHEN OTHERS THEN
43 x_status :='U';
44 x_exception_msg := 'no such location id';
45 --dbms_output.put_line('Other failure -- '||x_exception_msg);
46
47 RAISE;
48
49 END retrieve_hr_address;
50
51 PROCEDURE retrieve_tca_address (
52 p_location_id IN NUMBER
53 , x_address OUT NOCOPY tcaRefAddressCurTyp
54 , x_status OUT NOCOPY VARCHAR2
55 , x_exception_msg OUT NOCOPY VARCHAR2
56 )
57
58 IS
59 l_exception_msg varchar2(100);
60 l_bill_to hz_party_site_uses.SITE_USE_TYPE%TYPE;
61 l_ship_to hz_party_site_uses.SITE_USE_TYPE%TYPE;
62 l_mail_to hz_party_site_uses.SITE_USE_TYPE%TYPE;
63
64 BEGIN
65
66 l_bill_to := 'BILL_TO';
67 l_ship_to := 'SHIP_TO';
68 l_mail_to := 'GENERAL_MAIL_TO';
69
70 OPEN x_address FOR
71
72 SELECT
73 hps.party_site_name
74 , hl.ADDRESS1
75 , hl.ADDRESS2
76 , hl.CITY
77 , hl.STATE
78 , hl.PROVINCE
79 , hl.POSTAL_PLUS4_CODE
80 , hl.POSTAL_CODE
81 , hl.COUNTRY
82 , hl.county
83 , hl.overseas_address_flag
84 , DECODE (hpsu_bill_to.site_use_type, l_bill_to,'Y','N')
85 , DECODE (hpsu_ship_to.site_use_type, l_ship_to,'Y','N')
86 , DECODE (hpsu_mail_to.site_use_type, l_mail_to,'Y','N')
87
88 FROM hz_locations hl
89 , hz_party_sites hps
90 , hz_party_site_uses hpsu_ship_to
91 , hz_party_site_uses hpsu_bill_to
92 , hz_party_site_uses hpsu_mail_to
93
94 WHERE hl.location_id = p_location_id
95 AND hps.location_id = hl.location_id
96 AND hpsu_ship_to.party_site_id(+) = hps.party_site_id
97 AND hpsu_ship_to.site_use_type(+) = l_ship_to
98 AND hpsu_bill_to.party_site_id(+) = hps.party_site_id
99 AND hpsu_bill_to.site_use_type(+) = l_bill_to
100 AND hpsu_mail_to.party_site_id(+) = hps.party_site_id
101 AND hpsu_mail_to.site_use_type(+) = l_mail_to
102
103 ORDER by hps.party_site_name;
104
105 X_STATUS :='S';
106 x_exception_msg :=NULL;
107 EXCEPTION
108
109 WHEN OTHERS THEN
110 --dbms_output.put_line('Other failure -- '||x_exception_msg);
111 X_STATUS :='U';
112 RAISE;
113
114 END retrieve_tca_address;
115
116 PROCEDURE retrieve_hr_location_code (
117 p_location_id IN NUMBER
118 , p_language IN VARCHAR2
119 , x_address_name OUT NOCOPY VARCHAR2
120 )
121
122 IS
123
124 BEGIN
125
126 SELECT location_code
127 INTO x_address_name
128 FROM hr_locations_all_tl
129 WHERE location_id = p_location_id
130 AND language = p_language;
131
132 END retrieve_hr_location_code;
133
134 --we do not need store functionality at this time
135 --PROCEDURE store_hr_address()
136 --PROCEDURE store_tca_address()
137
138 END PON_ADD_UTIL_PKG;