1 PACKAGE BODY ap_po_locn_association_pkg AS
2 /* $Header: appolocb.pls 120.3 2011/03/15 05:04:24 zrehman ship $ */
3 --
4 --
5 -- PROCEDURE
6 -- insert_row
7 --
8 -- DESCRIPTION
9 -- This procedure inserts rows into the table po_location_associations
10 --
11 PROCEDURE insert_row ( p_location_id IN NUMBER,
12 p_vendor_id IN NUMBER,
13 p_vendor_site_id IN NUMBER,
14 p_last_update_date IN DATE,
15 p_last_updated_by IN NUMBER,
16 p_last_update_login IN NUMBER,
17 p_creation_date IN DATE,
18 p_created_by IN NUMBER,
19 p_org_id IN NUMBER) IS /* MO Access Control */
20
21 l_po_location_exist VARCHAR2(2);
22
23 BEGIN
24
25 l_po_location_exist := 'N';
26
27 --Check if the location_id already exists in po_location_associations table.
28
29 BEGIN
30 select 'Y'
31 into l_po_location_exist
32 from po_location_associations pla
33 where pla.location_id = nvl(p_location_id,0);
34 EXCEPTION
35 when no_data_found then
36 l_po_location_exist := 'N';
37 END;
38
39 --If the parameter p_location_id is not null and it exists in
40 --po_location_associations table then update the table with the vendor_id,
41 --vendor_site_id, last_update_date, last_updated_by, last_update_login
42 --details. Else we insert the record with all the details above alongwith
43 --creation_date, created_by.
44
45 if (p_location_id is not null) then
46 if (l_po_location_exist = 'Y') then
47
48 UPDATE po_location_associations
49 SET vendor_id = p_vendor_id,
50 vendor_site_id = p_vendor_site_id,
51 last_update_date = p_last_update_date,
52 last_updated_by = p_last_updated_by,
53 last_update_login = p_last_update_login,
54 org_id = p_org_id --MO Access Control
55 WHERE location_id = p_location_id;
56
57 else
58
59 INSERT into po_location_associations(location_id,
60 vendor_id,
61 vendor_site_id,
62 last_update_date,
63 last_updated_by,
64 last_update_login,
65 creation_date,
66 created_by,
67 org_id) --MO Access Control
68 VALUES (p_location_id, p_vendor_id, p_vendor_site_id, p_last_update_date,
69 p_last_updated_by, p_last_update_login, p_creation_date, p_created_by,
70 p_org_id); --MO Access Control
71
72 end if;
73 end if;
74
75 END;
76 --
77 --
78 PROCEDURE update_row ( p_location_id IN NUMBER,
79 p_vendor_id IN NUMBER,
80 p_vendor_site_id IN NUMBER,
81 p_last_update_date IN DATE,
82 p_last_updated_by IN NUMBER,
83 p_last_update_login IN NUMBER,
84 p_creation_date IN DATE,
85 p_created_by IN NUMBER,
86 p_org_id IN NUMBER) IS --MO Access Control
87
88 l_po_location_exist VARCHAR2(2);
89 l_site_associated VARCHAR2(2);
90 l_location_id NUMBER := 0;
91
92 BEGIN
93 l_po_location_exist := 'N';
94 l_site_associated := 'N';
95
96 BEGIN
97 select 'Y', nvl(location_id,0)
98 into l_site_associated, l_location_id
99 from po_location_associations pla
100 where pla.vendor_site_id = p_vendor_site_id;
101
102 EXCEPTION
103 when no_data_found then
104 l_site_associated := 'N';
105 l_location_id := NULL;
106 END;
107
108 --if location_id has not been modified, then do nothing.
109
110 if((l_location_id = p_location_id) or
111 (l_location_id is null and p_location_id is null)) then
112 return;
113 end if;
114
115 -- if there is a vendor site, then null out the vendor_id and vendor_site_id
116
117 if (l_site_associated = 'Y') then
118 UPDATE po_location_associations
119 SET vendor_id = NULL,
120 vendor_site_id = NULL,
121 last_update_date = p_last_update_date,
122 last_updated_by = p_last_updated_by,
123 last_update_login = p_last_update_login,
124 org_id = p_org_id --MO Access Control
125 WHERE vendor_site_id = p_vendor_site_id;
126 end if;
127
128 if (p_location_id is not null) then
129 BEGIN
130 select 'Y'
131 into l_po_location_exist
132 from po_location_associations pla
133 where pla.location_id = p_location_id;
134
135 EXCEPTION
136 when no_data_found then
137 l_po_location_exist := 'N';
138 END;
139
140 -- if po_location exists in the po_location_associations table, then update vendor_id,
141 -- vendor_site_id and other details with the new values passed by the table handler.
142 -- Else insert a row with the new values.
143
144 if (l_po_location_exist = 'Y') then
145 UPDATE po_location_associations
146 SET vendor_id = p_vendor_id,
147 vendor_site_id = p_vendor_site_id,
148 last_update_date = p_last_update_date,
149 last_updated_by = p_last_updated_by,
150 last_update_login = p_last_update_login,
151 org_id = p_org_id --MO Access Control
152 WHERE location_id = p_location_id;
153 else
154 INSERT into po_location_associations ( location_id,
155 vendor_id,
156 vendor_site_id,
157 last_update_date,
158 last_updated_by,
159 last_update_login,
160 creation_date,
161 created_by ,
162 org_id) --MO Access Control
163 VALUES (p_location_id, p_vendor_id, p_vendor_site_id, p_last_update_date,
164 p_last_updated_by, p_last_update_login, p_creation_date, p_created_by,
165 p_org_id); --MO Access Control
166 end if;
167 end if;
168
169 END;
170 /*Bug 11724842 start */
171 PROCEDURE delete_row ( p_vendor_site_id IN NUMBER ) IS
172 BEGIN
173 delete from po_location_associations
174 where vendor_site_id = p_vendor_site_id;
175 END;
176 /*Bug 11724842 end */
177 --
178 END ap_po_locn_association_pkg;
[Home] [Help]