1 package body PO_CHANGE_ORDER_TOLERANCES_PKG as
2 /* $Header: PO_CHANGE_ORDER_TOLERANCES_PKG.plb 120.2.12010000.2 2008/11/24 11:03:15 rojain ship $ */
3
4 ------------------------------------------------------------------------------
5 --Start of Comments
6 --Name: INSERT_ROW
7 --Pre-reqs:
8 -- None
9 --Modifies:
10 -- None
11 --Locks:
12 -- None
13 --Function:
14 -- 1. inserts a record into PO_CHANGE_ORDER_TOLERANCES_ALL table
15 --Parameters:
16 --IN:
17 -- X_CHANGE_ORDER_TYPE Change Order Type
18 -- X_TOLERANCE_NAME Tolerance name
19 -- X_ORG_ID Operating Unit Id
20 -- X_SEQUENCE_NUMBER Tolerance sequence number
21 -- X_MAXIMUM_INCREMENT maximum increment value
22 -- X_MAXIMUM_DECREMENT minimum increment value
23 -- X_ROUTING_FLAG approval routing flag
24 -- X_CREATION_DATE creation date (Standard Who Column)
25 -- X_CREATED_BY created date (Standard Who Column)
26 -- X_LAST_UPDATE_DATE last update date (Standard Who Column)
27 -- X_LAST_UPDATED_BY last updated by (Standard Who Column)
28 -- X_LAST_UPDATE_LOGIN last update login (Standard Who Column)
29 --OUT:
30 -- None
31 --End of Comment
32 -------------------------------------------------------------------------------
33 procedure INSERT_ROW (
34 X_CHANGE_ORDER_TYPE in VARCHAR2,
35 X_TOLERANCE_NAME in VARCHAR2,
36 X_ORG_ID in NUMBER,
37 X_SEQUENCE_NUMBER in NUMBER,
38 X_MAXIMUM_INCREMENT in NUMBER,
39 X_MAXIMUM_DECREMENT in NUMBER,
40 X_ROUTING_FLAG in VARCHAR2,
41 X_CREATION_DATE in DATE,
42 X_CREATED_BY in NUMBER,
43 X_LAST_UPDATE_DATE in DATE,
44 X_LAST_UPDATED_BY in NUMBER,
45 X_LAST_UPDATE_LOGIN in NUMBER)
46 is
47 x_tolerance_id number;
48 begin
49
50 select to_char(PO_CHANGE_ORDER_TOLERANCES_S.NEXTVAL)
51 into X_TOLERANCE_ID from sys.dual;
52
53 insert into PO_CHANGE_ORDER_TOLERANCES_ALL (
54 TOLERANCE_ID,
55 CHANGE_ORDER_TYPE,
56 TOLERANCE_NAME,
57 ORG_ID,
58 SEQUENCE_NUMBER,
59 MAXIMUM_INCREMENT,
60 MAXIMUM_DECREMENT,
61 ROUTING_FLAG,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_TOLERANCE_ID,
69 X_CHANGE_ORDER_TYPE,
70 X_TOLERANCE_NAME,
71 X_ORG_ID,
72 X_SEQUENCE_NUMBER,
73 X_MAXIMUM_INCREMENT,
74 X_MAXIMUM_DECREMENT,
75 X_ROUTING_FLAG,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN);
81
82 end INSERT_ROW;
83
84 ------------------------------------------------------------------------------
85 --Start of Comments
86 --Name: LOAD_ROW
87 --Pre-reqs:
88 -- None
89 --Modifies:
90 -- None
91 --Locks:
92 -- None
93 --Function:
94 -- 1. Load the row into PO_CHANGE_ORDER_TOLERANCES_ALL table
95 --Parameters:
96 --IN:
97 -- X_CHANGE_ORDER_TYPE Change Order Type
98 -- X_TOLERANCE_NAME Tolerance name
99 -- X_ORG_ID Operating Unit Id
100 -- X_SEQUENCE_NUMBER Tolerance sequence number
101 -- X_MAXIMUM_INCREMENT maximum increment value
102 -- X_MAXIMUM_DECREMENT minimum increment value
103 -- X_ROUTING_FLAG approval routing flag
104 --OUT:
105 -- None
106 --End of Comment
107 -------------------------------------------------------------------------------
108 procedure LOAD_ROW (
109 X_CHANGE_ORDER_TYPE in VARCHAR2,
110 X_TOLERANCE_NAME in VARCHAR2,
111 X_ORG_ID in NUMBER,
112 X_OWNER in VARCHAR2,
113 X_SEQUENCE_NUMBER in NUMBER,
114 X_LAST_UPDATE_DATE in DATE,
115 X_MAXIMUM_INCREMENT in NUMBER,
116 X_MAXIMUM_DECREMENT in NUMBER,
117 X_ROUTING_FLAG in VARCHAR2,
118 X_CUSTOM_MODE in VARCHAR2
119 ) IS
120
121 f_luby number; -- entity owner in file
122 f_ludate date; -- entity update date in file
123 db_luby number; -- entity owner in db
124 db_ludate date; -- entity update date in db
125
126 begin
127
128 f_luby := fnd_load_util.owner_id(X_OWNER);
129 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
130
131 -- bug3703523
132 -- for old see data we have last_updated_by as -1.
133 -- upload_test procedure will consider the record as being customized,
134 -- which is not the case here. Therefore we need to update
135 -- last_updated_by to 1 when it is -1 so that the record can be updated.
136
137 select DECODE(LAST_UPDATED_BY, -1, 1, LAST_UPDATED_BY), LAST_UPDATE_DATE
138 into db_luby, db_ludate
139 from PO_CHANGE_ORDER_TOLERANCES_ALL
140 where change_order_Type = X_CHANGE_ORDER_TYPE
141 and TOLERANCE_NAME = X_TOLERANCE_NAME
142 and ( (X_ORG_ID is null and org_id = -3113 )
143 or (X_ORG_ID is not null and org_id = X_ORG_ID));
144
145 -- Chanded the condition in such a way that only date is considered while updating owner is not updated
146 -- if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) then
147 if (fnd_load_util.upload_test(f_luby, f_ludate, f_luby, db_ludate, X_CUSTOM_MODE)) then
148
149 UPDATE PO_CHANGE_ORDER_TOLERANCES_ALL
150 SET MAXIMUM_INCREMENT = X_MAXIMUM_INCREMENT,
151 MAXIMUM_DECREMENT = X_MAXIMUM_DECREMENT,
152 ROUTING_FLAG = X_ROUTING_FLAG,
153 LAST_UPDATE_DATE = f_ludate ,
154 LAST_UPDATED_BY = f_luby,
155 LAST_UPDATE_LOGIN = 0
156 WHERE CHANGE_ORDER_TYPE = X_CHANGE_ORDER_TYPE
157 AND TOLERANCE_NAME = X_TOLERANCE_NAME
158 AND ( (X_ORG_ID IS NULL AND ORG_ID = -3113 )
159 OR (X_ORG_ID IS NOT NULL AND ORG_ID = X_ORG_ID));
160
161 end if;
162
163
164 exception
165 when NO_DATA_FOUND then
166 INSERT_ROW (
167 X_CHANGE_ORDER_TYPE,
168 X_TOLERANCE_NAME,
169 X_ORG_ID,
170 X_SEQUENCE_NUMBER,
171 X_MAXIMUM_INCREMENT,
172 X_MAXIMUM_DECREMENT,
173 X_ROUTING_FLAG,
174 f_ludate ,
175 f_luby ,
176 f_ludate ,
177 f_luby ,
178 0);
179 end LOAD_ROW;
180
181
182 end PO_CHANGE_ORDER_TOLERANCES_PKG;
[Home] [Help]