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