[Home] [Help]
PACKAGE BODY: APPS.GMP_EAM_DOWNTIME_PKG
Source
1 PACKAGE BODY gmp_eam_downtime_pkg AS
2 /* $Header: GMPASUNB.pls 120.2 2006/02/24 13:35:51 rpatangy noship $ */
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,
49 mtl_parameters mp
50 WHERE mp.organization_id = nvl(c_organization_id,mp.organization_id)
51 AND mp.process_enabled_flag = 'Y'
52 AND eam.prod_org_id = mp.organization_id
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 AND crd.schedule_ind = 2 /* Scheduled to Instances only */
70 AND crd.delete_mark = 0
71 ORDER BY mp.organization_id, eam.wip_entity_id, gri.instance_number;
72
73 BEGIN
74 g_reason_code := 'NONE';
75 l_resources := '';
76 v_prod_id := 0 ;
77 i := 0;
78 new_prod_org_flag := 0;
79 prev_prod_org_id := 0;
80
81 l_user_id := to_number(FND_PROFILE.VALUE('USER_ID')) ;
82
83 /* B3467386 08-Mar-04 Namit Singhi. Cursor paramters included */
84 FOR eam_unavail_dtl IN Cur_eam_rsrc_unavail(p_organization_id,
85 p_include_unreleased,
86 p_include_unfirmed,
87 p_resources)
88 LOOP
89
90 /* B3467386 08-Mar-04 Namit Singhi. The following IF condition makes sure
91 that Delete from gmp_rsrc_unavail_man happens only once for each Plant */
92
93 IF(prev_prod_org_id = eam_unavail_dtl.mtl_organization_id) THEN
94 new_prod_org_flag := 2; -- False
95 ELSE
96 new_prod_org_flag := 1; -- True. Execute delete statement.
97 END IF;
98
99
100 /* Delete from Resource Unavailability table to load a fresh
101 The following Delete method has been agreed, though it has some
102 Cost associated with it with respect to Performance issue
103 Cannot Blindly delete the Wip_entity_id's that got loaded and
104 had to use the following procedure, which uses sub queries. */
105
106 IF new_prod_org_flag = 1 THEN
107 DELETE
108 FROM gmp_rsrc_unavail_man gmp
109 WHERE EXISTS (
110 SELECT eam.wip_entity_id
111 FROM
112 eam_workorder_downtime_v eam,
113 gmp_resource_instances gri,
114 cr_rsrc_dtl crd
115 WHERE eam.equipment_item_id = gri.equipment_item_id
116 AND eam.eqp_serial_number = gri.eqp_serial_number
117 AND crd.resource_id = gri.resource_id
118 AND eam.firm_flag in (1,2)
119 AND eam.prod_org_id = eam_unavail_dtl.mtl_organization_id
120 AND crd.organization_id = eam_unavail_dtl.mtl_organization_id
121 AND eam.wip_entity_id = gmp.wip_entity_id
122 AND eam.to_date >= sysdate
123 AND eam.to_date > eam.from_date
124 )
125 AND gmp.to_date > sysdate ;
126 END IF;
127
128 prev_prod_org_id := eam_unavail_dtl.mtl_organization_id;
129
130 i := i + 1 ;
131 IF i > 500 THEN
132 COMMIT ;
133 i := 0 ;
134 END IF ;
135
136
137 INSERT INTO gmp_rsrc_unavail_man
138 (
139 resource_id,
140 from_date,
141 to_date,
142 reason_code,
143 creation_date,
144 created_by,
145 last_update_date,
146 last_updated_by,
147 last_update_login,
148 resource_units,
149 instance_id,
150 wip_entity_id,
151 maint_org_id,
152 op_seq
153 )
154 Values (
155 eam_unavail_dtl.resource_id,
156 eam_unavail_dtl.from_date,
157 eam_unavail_dtl.to_date,
158 G_reason_code,
159 sysdate,
160 l_user_id ,
161 sysdate,
162 l_user_id ,
163 l_user_id ,
164 to_number(NULL), /* instance_id is given */
165 eam_unavail_dtl.instance_id,
166 eam_unavail_dtl.wip_entity_id,
167 eam_unavail_dtl.maint_org_id,
168 eam_unavail_dtl.op_seq
169 );
170
171 FND_FILE.PUT_LINE(FND_FILE.LOG,'Wip Entity Ids Inserted - '||eam_unavail_dtl.ORGANIZATION_CODE||'-'||eam_unavail_dtl.wip_entity_id);
172 END LOOP;
173
174 IF i = 0 THEN
175 RAISE NO_ROWS;
176 END IF;
177
178 EXCEPTION
179 WHEN NO_ROWS THEN
180 errbuf := sqlerrm;
181 FND_MESSAGE.SET_NAME('GMA','SY_NO_ROWS_SELECTED');
182 FND_FILE.PUT_LINE ( FND_FILE.LOG,'-'||FND_MESSAGE.GET);
183 /* Setting the Concurrent Status to Warning instead of giving Error */
184 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
185 NULL;
186 END IF;
187 retcode := '3';
188 WHEN OTHERS THEN
189 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Failed - Error Occured '||sqlerrm);
190
191 END insert_man_unavail;
192
193 END gmp_eam_downtime_pkg;