[Home] [Help]
PACKAGE BODY: APPS.PO_ACCEPTANCES_INS_PVT
Source
1 PACKAGE BODY PO_ACCEPTANCES_INS_PVT AS
2 /* $Header: POXVIACB.pls 120.4 2011/12/27 08:43:58 pamandav ship $*/
3
4 -- Bug 2850566
5 -- Modified the parameters of the procedure to accomodate new columns
6 -- and defaulted the parameters to Null
7
8 -- CLM Apprvl, added a new parameter p_draft_id for modification documents
9 PROCEDURE insert_row
10 (
11 x_rowid IN OUT NOCOPY ROWID,
12 x_acceptance_id IN OUT NOCOPY NUMBER,
13 x_last_update_date IN OUT NOCOPY DATE,
14 x_last_updated_by IN OUT NOCOPY NUMBER,
15 x_last_update_login IN OUT NOCOPY NUMBER,
16 p_creation_date IN DATE DEFAULT NULL,
17 p_created_by IN NUMBER DEFAULT NULL,
18 p_po_header_id IN NUMBER DEFAULT NULL,
19 p_draft_id IN NUMBER DEFAULT NULL, -- CLM Apprvl
20 p_po_release_id IN NUMBER DEFAULT NULL,
21 p_action IN VARCHAR2 DEFAULT NULL,
22 p_action_date IN DATE DEFAULT NULL,
23 p_employee_id IN NUMBER DEFAULT NULL,
24 p_revision_num IN NUMBER DEFAULT NULL,
25 p_accepted_flag IN VARCHAR2 DEFAULT NULL,
26 p_acceptance_lookup_code IN VARCHAR2 DEFAULT NULL,
27 p_note IN CLOB DEFAULT NULL,
28 p_accepting_party IN VARCHAR2 DEFAULT NULL,
29 p_signature_flag IN VARCHAR2 DEFAULT NULL,
30 p_erecord_id IN NUMBER DEFAULT NULL,
31 p_role IN VARCHAR2 DEFAULT NULL,
32 p_attribute_category IN VARCHAR2 DEFAULT NULL,
33 p_attribute1 IN VARCHAR2 DEFAULT NULL,
34 p_attribute2 IN VARCHAR2 DEFAULT NULL,
35 p_attribute3 IN VARCHAR2 DEFAULT NULL,
36 p_attribute4 IN VARCHAR2 DEFAULT NULL,
37 p_attribute5 IN VARCHAR2 DEFAULT NULL,
38 p_attribute6 IN VARCHAR2 DEFAULT NULL,
39 p_attribute7 IN VARCHAR2 DEFAULT NULL,
40 p_attribute8 IN VARCHAR2 DEFAULT NULL,
41 p_attribute9 IN VARCHAR2 DEFAULT NULL,
42 p_attribute10 IN VARCHAR2 DEFAULT NULL,
43 p_attribute11 IN VARCHAR2 DEFAULT NULL,
44 p_attribute12 IN VARCHAR2 DEFAULT NULL,
45 p_attribute13 IN VARCHAR2 DEFAULT NULL,
46 p_attribute14 IN VARCHAR2 DEFAULT NULL,
47 p_attribute15 IN VARCHAR2 DEFAULT NULL,
48 p_request_id IN NUMBER DEFAULT NULL,
49 p_program_application_id IN NUMBER DEFAULT NULL,
50 p_program_id IN NUMBER DEFAULT NULL,
51 p_program_update_date IN DATE DEFAULT NULL,
52 p_po_line_location_id IN NUMBER DEFAULT NULL,
53 p_vendor_contact_id IN NUMBER DEFAULT NULL, -- CLM Apprvl signature process
54 p_vendor_contact_name IN VARCHAR2 DEFAULT NULL, -- CLM Apprvl signature process
55 p_vendor_contact_title IN VARCHAR2 DEFAULT NULL, -- CLM Apprvl signature process
56 p_vendor_signed_date IN DATE DEFAULT NULL -- CLM Apprvl signature process
57 )
58 IS
59
60 l_id NUMBER;
61
62 -- Bug 2850566 RBAIRRAJ
63 -- SQL What :selects the rowid from the acceptances table
64 -- SQL Why :To set the output parameter x_rowid
65 -- SQL Join :Acceptance_id
66 CURSOR c_rowid IS
67 SELECT rowid
68 FROM PO_ACCEPTANCES
69 WHERE acceptance_id = l_id;
70
71 -- Bug 2850566 RBAIRRAJ
72 -- SQL What :selects the next avaiable sequence number from PO_ACCEPTANCES_S sequence
73 -- SQL Why :To insert it as Acceptance_Id in the PO_ACCEPTANCES table
74 CURSOR c_next_id IS
75 SELECT PO_ACCEPTANCES_S.nextval
76 FROM SYS.DUAL;
77
78 BEGIN
79
80 IF (x_acceptance_id IS NULL) THEN
81 SELECT PO_ACCEPTANCES_S.nextval
82 INTO l_id
83 FROM SYS.DUAL;
84 ELSE
85 l_id := x_acceptance_id;
86 END IF;
87
88 -- Bug 2850566
89 -- Added this If condition as iSP needs this for carry over acknowledgements
90 IF (x_last_updated_by IS NULL) THEN
91 x_last_updated_by := fnd_global.user_id;
92 END IF;
93
94 x_last_update_date := SYSDATE;
95 if (x_last_update_login is null) then
96 x_last_update_login := fnd_global.login_id;
97 end if;
98
99 -- Bug 2850566 RBAIRRAJ
100 -- SQL What :Inserts a record into PO_ACCEPTANCES table
101 -- SQL Why :This acts as a rowhandler for the PO_ACCEPTANCES table
102 INSERT INTO PO_ACCEPTANCES(
103 ACCEPTANCE_ID,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN,
107 CREATION_DATE,
108 CREATED_BY,
109 PO_HEADER_ID,
110 DRAFT_ID, -- CLM Apprvl
111 PO_RELEASE_ID,
112 ACTION,
113 ACTION_DATE,
114 EMPLOYEE_ID,
115 REVISION_NUM,
116 ACCEPTED_FLAG,
117 ACCEPTANCE_LOOKUP_CODE,
118 NOTE,
119 ACCEPTING_PARTY,
120 SIGNATURE_FLAG,
121 ERECORD_ID,
122 ROLE,
123 ATTRIBUTE_CATEGORY,
124 ATTRIBUTE1,
125 ATTRIBUTE2,
126 ATTRIBUTE3,
127 ATTRIBUTE4,
128 ATTRIBUTE5,
129 ATTRIBUTE6,
130 ATTRIBUTE7,
131 ATTRIBUTE8,
132 ATTRIBUTE9,
133 ATTRIBUTE10,
134 ATTRIBUTE11,
135 ATTRIBUTE12,
136 ATTRIBUTE13,
137 ATTRIBUTE14,
138 ATTRIBUTE15,
139 PO_LINE_LOCATION_ID,
140 REQUEST_ID,
141 PROGRAM_APPLICATION_ID,
142 PROGRAM_ID,
143 PROGRAM_UPDATE_DATE,
144 VENDOR_CONTACT_ID, -- CLM Apprvl signature process
145 VENDOR_CONTACT_NAME, -- CLM Apprvl signature process
146 VENDOR_CONTACT_TITLE, -- CLM Apprvl signature process
147 VENDOR_SIGNED_DATE -- CLM Apprvl signature process
148 )
149 VALUES (
150 l_id,
151 x_last_update_date,
152 x_last_updated_by,
153 x_last_update_login,
154 p_creation_date,
155 p_created_by,
156 p_po_header_id,
157 p_draft_id, -- CLM Apprvl
158 p_po_release_id,
159 p_action,
160 p_action_date,
161 p_employee_id,
162 p_revision_num,
163 p_accepted_flag,
164 p_acceptance_lookup_code,
165 p_note,
166 p_accepting_party,
167 p_signature_flag,
168 p_erecord_id,
169 p_role,
170 p_attribute_category,
171 p_attribute1,
172 p_attribute2,
173 p_attribute3,
174 p_attribute4,
175 p_attribute5,
176 p_attribute6,
177 p_attribute7,
178 p_attribute8,
179 p_attribute9,
180 p_attribute10,
181 p_attribute11,
182 p_attribute12,
183 p_attribute13,
184 p_attribute14,
185 p_attribute15,
186 p_po_line_location_id,
187 p_request_id,
188 p_program_application_id,
189 p_program_id,
190 p_program_update_date,
191 p_vendor_contact_id , -- CLM Apprvl signature process
192 p_vendor_contact_name, -- CLM Apprvl signature process
193 p_vendor_contact_title, -- CLM Apprvl signature process
194 p_vendor_signed_date -- CLM Apprvl signature process
195 );
196
197 x_acceptance_id := l_id;
198
199 OPEN c_rowid;
200 FETCH c_rowid INTO x_rowid;
201 if (c_rowid%NOTFOUND) then
202 CLOSE c_rowid;
203 Raise NO_DATA_FOUND;
204 end if;
205 CLOSE c_rowid;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 RAISE;
210 END insert_row;
211
212 END PO_ACCEPTANCES_INS_PVT;