[Home] [Help]
PACKAGE BODY: APPS.POR_LOAD_LOC_TEMP_TABLE
Source
1 PACKAGE BODY POR_LOAD_LOC_TEMP_TABLE as
2 /* $Header: PORLLCTB.pls 115.5 2001/07/12 11:41:50 pkm ship $ */
3
4 PROCEDURE insert_update_loc_temp_table (
5 x_location_code IN VARCHAR2,
6 x_business_grp_name IN VARCHAR2,
7 x_effective_date IN DATE,
8 x_description IN VARCHAR2,
9 x_address_style IN VARCHAR2,
10 x_address_line_1 IN VARCHAR2,
11 x_address_line_2 IN VARCHAR2,
12 x_address_line_3 IN VARCHAR2,
13 x_city IN VARCHAR2,
14 x_state IN VARCHAR2,
15 x_county IN VARCHAR2,
16 x_country IN VARCHAR2,
17 x_postal_code IN VARCHAR2,
18 x_telephone_number_1 IN VARCHAR2,
19 x_telephone_number_2 IN VARCHAR2,
20 x_shipToLocation IN VARCHAR2,
21 x_ship_to_flag IN VARCHAR2,
22 x_bill_to_flag IN VARCHAR2,
23 x_receiving_site IN VARCHAR2,
24 x_office_site_flag IN VARCHAR2,
25 x_inv_org IN VARCHAR2,
26 x_tax_name IN VARCHAR2)
27 IS
28 BEGIN
29
30
31 IF (get_location_exists(x_location_code)) THEN
32
33 UPDATE POR_LOCATION_LOADER_VALUES
34 SET
35 location_code = x_location_code,
36 business_group = x_business_grp_name,
37 effective_date = x_effective_date,
38 description = x_description,
39 address_style = x_address_style,
40 address_line_1 = x_address_line_1,
41 address_line_2 = x_address_line_2,
42 address_line_3 = x_address_line_3,
43 city = x_city,
44 state = x_state,
45 county = x_county,
46 postal_code = x_postal_code,
47 country = x_country,
48 telephone = x_telephone_number_1,
49 fax = x_telephone_number_2,
50 ship_to_location = x_shipToLocation,
51 ship_to_flag = x_ship_to_flag,
52 bill_to_flag = x_bill_to_flag,
53 receiving_to_flag = x_receiving_site,
54 office_site = x_office_site_flag,
55 inventory_Org = x_inv_org,
56 tax_name = x_tax_name,
57 loader_status = 'unloaded',
58 last_update_date = sysdate
59 WHERE
60 location_code = x_location_code;
61
62 ELSE
63
64 INSERT INTO POR_LOCATION_LOADER_VALUES (
65 location_code,
66 business_group,
67 effective_date,
68 description,
69 address_style,
70 address_line_1,
71 address_line_2,
72 address_line_3,
73 city,
74 state,
75 county,
76 postal_code,
77 country,
78 telephone,
79 fax,
80 ship_to_location,
81 ship_to_flag,
82 bill_to_flag,
83 receiving_to_flag,
84 office_site,
85 inventory_Org,
86 tax_name,
87 loader_status,
88 last_update_date,
89 last_updated_by,
90 creation_date,
91 created_by)
92 VALUES (
93 x_location_code,
94 x_business_grp_name,
95 x_effective_date,
96 x_description,
97 x_address_style,
98 x_address_line_1,
99 x_address_line_2,
100 x_address_line_3,
101 x_city,
102 x_state,
103 x_county,
104 x_postal_code,
105 x_country,
106 x_telephone_number_1,
107 x_telephone_number_2,
108 x_shipToLocation,
109 x_ship_to_flag,
110 x_bill_to_flag,
111 x_receiving_site,
112 x_office_site_flag,
113 x_inv_org,
114 x_tax_name,
115 'unloaded',
116 sysdate,
117 0,
118 sysdate,
119 0
120 );
121
122 END IF;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 RAISE;
127
128 commit;
129
130 END insert_update_loc_temp_table;
131
132 FUNCTION get_location_exists (p_location_code IN VARCHAR2) RETURN BOOLEAN IS
133 l_exists NUMBER;
134
135 BEGIN
136
137 SELECT 1 INTO l_exists FROM por_location_loader_values
138 WHERE location_code = p_location_code;
139
140 RETURN true;
141
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 RETURN false;
145
146 END get_location_exists;
147
148 END POR_LOAD_LOC_TEMP_TABLE;
149