[Home] [Help]
PACKAGE BODY: APPS.GMP_EAM_DOWNTIME_PKG
Source
1 PACKAGE BODY gmp_eam_downtime_pkg AS
2 /* $Header: GMPASUNB.pls 120.4.12020000.1 2012/06/27 15:05:43 appldev ship $ */
3
4 PROCEDURE insert_man_unavail
5 (
6 errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER,
8 p_organization_id IN NUMBER,
9 p_include_unreleased IN NUMBER, /* 3467386 */
10 p_include_unfirmed IN NUMBER, /* 3467386 */
11 p_resources IN VARCHAR2 /* 3467386 */
12 ) IS
13
14 g_reason_code VARCHAR2(4);
15 l_user_id NUMBER;
16 l_resources VARCHAR2(16);
17 v_prod_id NUMBER;
18 i NUMBER;
19 new_prod_org_flag NUMBER;
20 prev_prod_org_id NUMBER;
21 NO_ROWS EXCEPTION;
22
23 /* B3467386 08-Mar-04 Namit Singhi. Cursor modified to have these new changes - Cursor
24 should be able to give rows when no plant code is entered and when
25 resources are enetered. */
26 /* B4905308, The eam View contains the active organizations in it and hence HR
27 table join is removed */
28
29 CURSOR Cur_eam_rsrc_unavail (c_organization_id NUMBER,
30 c_include_unreleased NUMBER,
31 c_include_unfirmed NUMBER,
32 c_resources VARCHAR2
33 ) IS
34 SELECT mp.organization_code ORGANIZATION_CODE,
35 mp.organization_id mtl_organization_id,
36 crd.resource_id resource_id,
37 gri.instance_id instance_id,
38 gri.instance_number instance_number,
39 eam.wip_entity_id wip_entity_id,
40 eam.op_seq op_seq,
41 eam.maint_org_id maint_org_id,
42 eam.workorder_number workorder_number,
43 eam.from_date from_date,
44 eam.to_date to_date,
45 eam.eqp_serial_number eqp_serial_number
46 FROM eam_workorder_downtime_v eam,
47 gmp_resource_instances gri,
48 cr_rsrc_dtl crd,
52 AND eam.prod_org_id = mp.organization_id
49 mtl_parameters mp
50 WHERE mp.organization_id = nvl(c_organization_id,mp.organization_id)
51 AND mp.process_enabled_flag = 'Y'
53 AND eam.equipment_item_id = gri.equipment_item_id
54 AND eam.eqp_serial_number = gri.eqp_serial_number
55 AND crd.resource_id = gri.resource_id
56 AND crd.organization_id = mp.organization_id
57 AND ((c_include_unreleased = 1
58 AND eam.status_type IN (1,3)) /* B3467386,Released and Unreleased */
59 OR ( c_include_unreleased <> 1
60 AND eam.status_type = 3 )) /* B3467386 Released Only */
61 AND ((c_include_unfirmed = 1
62 AND eam.firm_flag IN (1,2)) /* 3467386 - Firm and Unfirm */
63 OR (c_include_unfirmed <> 1
64 AND eam.firm_flag = 1)) /* B3467386 - Firm only */
65 AND eam.to_date >= sysdate
66 AND eam.to_date > eam.from_date /* Rows with no duration will not be selected */
67 AND crd.resources = nvl(c_resources,crd.resources)
68 AND crd.inactive_ind = 0
69 -- B8416225 Rajesh Patangya
70 AND crd.schedule_ind in (1,2) /* Schedule and Schedule to Instances */
71 AND crd.delete_mark = 0
72 ORDER BY mp.organization_id, eam.wip_entity_id, gri.instance_number;
73
74 BEGIN
75 g_reason_code := 'NONE';
76 l_resources := '';
77 v_prod_id := 0 ;
78 i := 0;
79 new_prod_org_flag := 0;
80 prev_prod_org_id := 0;
81
82 l_user_id := to_number(FND_PROFILE.VALUE('USER_ID')) ;
83
84 /* B3467386 08-Mar-04 Namit Singhi. Cursor paramters included */
85 FOR eam_unavail_dtl IN Cur_eam_rsrc_unavail(p_organization_id,
86 p_include_unreleased,
87 p_include_unfirmed,
88 p_resources)
89 LOOP
90
91 /* B3467386 08-Mar-04 Namit Singhi. The following IF condition makes sure
92 that Delete from gmp_rsrc_unavail_man happens only once for each Plant */
93
94 IF(prev_prod_org_id = eam_unavail_dtl.mtl_organization_id) THEN
95 new_prod_org_flag := 2; -- False
96 ELSE
97 new_prod_org_flag := 1; -- True. Execute delete statement.
98 END IF;
99
100
101 /* Delete from Resource Unavailability table to load a fresh
102 The following Delete method has been agreed, though it has some
103 Cost associated with it with respect to Performance issue
104 Cannot Blindly delete the Wip_entity_id's that got loaded and
105 had to use the following procedure, which uses sub queries. */
106
107 IF new_prod_org_flag = 1 THEN
108 DELETE
109 FROM gmp_rsrc_unavail_man gmp
110 WHERE EXISTS (
111 SELECT eam.wip_entity_id
112 FROM
113 eam_workorder_downtime_v eam,
114 gmp_resource_instances gri,
115 cr_rsrc_dtl crd
116 WHERE eam.equipment_item_id = gri.equipment_item_id
117 AND eam.eqp_serial_number = gri.eqp_serial_number
118 AND crd.resource_id = gri.resource_id
119 AND eam.firm_flag in (1,2)
120 AND eam.prod_org_id = eam_unavail_dtl.mtl_organization_id
121 AND crd.organization_id = eam_unavail_dtl.mtl_organization_id
122 AND eam.wip_entity_id = gmp.wip_entity_id
123 AND eam.to_date >= sysdate
124 AND eam.to_date > eam.from_date
125 )
126 AND gmp.to_date > sysdate ;
127 END IF;
128
129 prev_prod_org_id := eam_unavail_dtl.mtl_organization_id;
130
131 i := i + 1 ;
132 IF i > 500 THEN
133 COMMIT ;
134 i := 0 ;
135 END IF ;
136
137
138 INSERT INTO gmp_rsrc_unavail_man
139 (
140 resource_id,
141 from_date,
142 to_date,
143 reason_code,
144 creation_date,
145 created_by,
146 last_update_date,
147 last_updated_by,
148 last_update_login,
149 resource_units,
150 instance_id,
151 wip_entity_id,
152 maint_org_id,
153 op_seq
154 )
155 Values (
156 eam_unavail_dtl.resource_id,
157 eam_unavail_dtl.from_date,
158 eam_unavail_dtl.to_date,
159 G_reason_code,
160 sysdate,
161 l_user_id ,
162 sysdate,
163 l_user_id ,
164 l_user_id ,
165 to_number(NULL), /* instance_id is given */
166 eam_unavail_dtl.instance_id,
167 eam_unavail_dtl.wip_entity_id,
168 eam_unavail_dtl.maint_org_id,
169 eam_unavail_dtl.op_seq
170 );
171
172 FND_FILE.PUT_LINE(FND_FILE.LOG,'Wip Entity Ids Inserted - '||eam_unavail_dtl.ORGANIZATION_CODE||'-'||eam_unavail_dtl.wip_entity_id);
173 END LOOP;
174
175 IF i = 0 THEN
176 RAISE NO_ROWS;
177 END IF;
178
179 EXCEPTION
180 WHEN NO_ROWS THEN
181 errbuf := sqlerrm;
182 FND_MESSAGE.SET_NAME('GMA','SY_NO_ROWS_SELECTED');
183 FND_FILE.PUT_LINE ( FND_FILE.LOG,'-'||FND_MESSAGE.GET);
184 /* Setting the Concurrent Status to Warning instead of giving Error */
185 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
186 NULL;
187 END IF;
188 retcode := '3';
189 WHEN OTHERS THEN
190 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Failed - Error Occured '||sqlerrm);
191
192 END insert_man_unavail;
193
194 END gmp_eam_downtime_pkg;