1 PACKAGE PO_HR_LOCATION AUTHID CURRENT_USER AS
2 /* $Header: POXPRPOS.pls 120.3 2007/02/15 20:27:55 dedelgad ship $ */
3
4 -- <R12 PO OTM Integration START>: Added overloaded version of get_address
5 /*******************************************************************
6 NAME: get_address
7
8 Description : This is a over loaded procedure to orginal procedure.
9 Created as apart of FPJ PO Communication enhancement.
10
11 Referenced by :
12 Parameters :
13
14 *******************************************************************/
15 PROCEDURE get_address
16 ( p_location_id IN NUMBER
17 , x_address_line_1 OUT NOCOPY VARCHAR2
18 , x_address_line_2 OUT NOCOPY VARCHAR2
19 , x_address_line_3 OUT NOCOPY VARCHAR2
20 , x_town_or_city OUT NOCOPY VARCHAR2
21 , x_state_or_province OUT NOCOPY VARCHAR2
22 , x_postal_code OUT NOCOPY VARCHAR2
23 , x_territory_short_name OUT NOCOPY VARCHAR2
24 , x_iso_territory_code OUT NOCOPY VARCHAR2
25 );
26 -- <R12 PO OTM Integration END>
27
28 PROCEDURE get_address
29 ( x_location_id IN Number,
30 Address_line_1 OUT NOCOPY Varchar2,
31 Address_line_2 OUT NOCOPY Varchar2,
32 Address_line_3 OUT NOCOPY Varchar2,
33 Territory_short_name OUT NOCOPY VArchar2,
34 Address_info OUT NOCOPY Varchar2 );
35
36 /*******************************************************************
37 NAME: get_address
38
39 Description : This is a over loaded procedure to orginal procedure.
40 Created as apart of FPJ PO Communication enhancement.
41
42 Referenced by :
43 parameters :
44
45 CHANGE History: Created MANRAM
46 bug:3463617 - Added new out variable x_address_line_4
47 --bug3438608 added the out variables x_town_or_city
48 --x_postal_code and x_state_or_province
49 *******************************************************************/
50 PROCEDURE get_address
51 ( p_location_id IN Number,
52 x_address_line_1 OUT NOCOPY Varchar2,
53 x_address_line_2 OUT NOCOPY Varchar2,
54 x_address_line_3 OUT NOCOPY Varchar2,
55 x_territory_short_name OUT NOCOPY VArchar2,
56 x_address_info OUT NOCOPY Varchar2,
57 x_location_name OUT NOCOPY Varchar2,
58 x_contact_phone OUT NOCOPY Varchar2,
59 x_contact_fax OUT NOCOPY Varchar2,
60 x_address_line_4 OUT NOCOPY Varchar2,
61 x_town_or_city OUT NOCOPY HR_LOCATIONS.town_or_city%type ,
62 x_postal_code OUT NOCOPY HR_LOCATIONS.postal_code%type,
63 x_state_or_province OUT NOCOPY varchar2 );
64
65 --Bug#3580225 Start --
66 /************************************************************************************
67 NAME: get_alladdress_lines
68
69 Description : This procedure has the same code as that of over loaded procedure of
70 get_address and the code to retrieve all the address details from
71 HR_LOCATIONS and HZ_LOCATIONS. The fields used in HR_LOCATIONS table is
72 retreived using fnd_dflex package and for HZ_LOCATIONS,
73 HZ_FORMAT_PUB.FORMAT_ADDRESS procedure is used.
74
75 Referenced by :
76 parameters :
77
78 CHANGE History: Created MANRAM
79
80 *******************************************************************/
81 PROCEDURE get_alladdress_lines
82 ( p_location_id IN Number,
83 x_address_line_1 OUT NOCOPY Varchar2,
84 x_address_line_2 OUT NOCOPY Varchar2,
85 x_address_line_3 OUT NOCOPY Varchar2,
86 x_territory_short_name OUT NOCOPY VArchar2,
87 x_address_info OUT NOCOPY Varchar2,
88 x_location_name OUT NOCOPY Varchar2,
89 x_contact_phone OUT NOCOPY Varchar2,
90 x_contact_fax OUT NOCOPY Varchar2,
91 x_address_line_4 OUT NOCOPY Varchar2,
92 x_town_or_city OUT NOCOPY HR_LOCATIONS.town_or_city%type ,
93 x_postal_code OUT NOCOPY HR_LOCATIONS.postal_code%type,
94 x_state_or_province OUT NOCOPY varchar2 );
95
96
97 /************************************************************************************
98 vchar_array is a type of VARRAY of size 20. This holds the data of type varchar2.
99 *************************************************************************************/
100
101 TYPE vchar_array IS VARRAY(20) OF VARCHAR2(240);
102
103 /************************************************************************************
104 address is a PL/SQL table of po_address_details_gt global temp table rowtype .
105 *************************************************************************************/
106
107 TYPE address IS TABLE OF po_address_details_gt%rowtype INDEX BY BINARY_INTEGER;
108
109
110 /************************************************************************************
111 NAME: populate_gt
112
113 Description : This procedure populates the global temp table from PL/SQL table.
114 This procedure is called from PO_COMMUNICATION_PVT.POXMLGEN function.
115
116 Referenced by :
117 parameters : None
118
119 CHANGE History: Created MANRAM
120
121 *******************************************************************/
122 procedure populate_gt ;
123
124
125
126 /*******************************************************************************
127 addr_prompt_query_rec is of type record with address style, address prompt list
128 and query as columns.
129 CHANGE History: 1) bug#3622675: PO_HR_LOCATION package is erroring out with
130 PLS-00507: a PLSQL Table may not contain a table or a record with composite
131 fields when compliled in 8i data base. Replaced the array list l_addr_prompts_array
132 with 20 varibales which holds the prompt names.
133 *******************************************************************************/
134 TYPE addr_prompt_query_rec IS RECORD(address_style hr_locations.style%type,
135 addr_label_1 varchar2(240),
136 addr_label_2 varchar2(240),
137 addr_label_3 varchar2(240),
138 addr_label_4 varchar2(240),
139 addr_label_5 varchar2(240),
140 addr_label_6 varchar2(240),
141 addr_label_7 varchar2(240),
142 addr_label_8 varchar2(240),
143 addr_label_9 varchar2(240),
144 addr_label_10 varchar2(240),
145 addr_label_11 varchar2(240),
146 addr_label_12 varchar2(240),
147 addr_label_13 varchar2(240),
148 addr_label_14 varchar2(240),
149 addr_label_15 varchar2(240),
150 addr_label_16 varchar2(240),
151 addr_label_17 varchar2(240),
152 addr_label_18 varchar2(240),
153 addr_label_19 varchar2(240),
154 addr_label_20 varchar2(240),
155 query varchar2(4000) );
156
157
158 /*********************************************************************************
159 This is a PL/SQL table of addr_prompt_query_rec record type.
160 This PL/SQL table is used to store the style code, address prompt List
161 and the select query that is framed after retreiving values from descriptive flex
162 fields. style code is unique field. If the style code for which values are to be
163 retrieved from descriptive flex fields is exists in this table then the values
164 in the PL/SQL table are used, other wise it will call fnd_dflex package.
165 *********************************************************************************/
166 TYPE addr_prompt_query IS TABLE OF addr_prompt_query_rec index by binary_integer;
167
168 --Bug#3580225 --
169 -- HTML Orders R12
170 FUNCTION get_formatted_address(p_location_id IN NUMBER)
171 RETURN VARCHAR2;
172
173 END PO_HR_LOCATION;
[Home] [Help]