[Home] [Help]
PACKAGE BODY: APPS.WIP_RES_USAGE_SUBSTITUTIONS
Source
1 Package Body WIP_RES_USAGE_SUBSTITUTIONS as
2 /* $Header: wiprustb.pls 115.6 2002/11/29 17:43:06 simishra ship $ */
3
4 Procedure Substitution_Res_Usages( p_group_id in number,
5 p_wip_entity_id in number,
6 p_organization_id in number,
7 x_err_code out nocopy varchar2,
8 x_err_msg out nocopy varchar2,
9 x_return_status out nocopy varchar2) IS
10
11 Cursor Usage_info (p_group_id number,
12 p_wip_entity_id number,
13 p_organization_id number) IS
14 SELECT operation_seq_num, resource_seq_num
15 FROM WIP_JOB_DTLS_INTERFACE
16 WHERE group_id = p_group_id
17 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
18 AND process_status = WIP_CONSTANTS.RUNNING
19 AND wip_entity_id = p_wip_entity_id
20 AND organization_id = p_organization_id
21 AND load_type = WIP_JOB_DETAILS.WIP_RES_USAGE
22 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD
23 ORDER BY operation_seq_num, resource_seq_num;
24
25
26 Begin
27
28 FOR cur_row in Usage_info(p_group_id, p_wip_entity_id,p_organization_id)Loop
29
30 Sub_Usage(p_group_id, p_wip_entity_id, p_organization_id,
31 cur_row.operation_seq_num, cur_row.resource_seq_num,
32 x_err_code, x_err_msg, x_return_status);
33
34 END LOOP;
35
36 END SUBSTITUTION_RES_USAGES;
37
38 Procedure Sub_Usage (p_group_id in number,
39 p_wip_entity_id in number,
40 p_organization_id in number,
41 p_operation_seq_num in number,
42 p_resource_seq_num in number,
43 x_err_code out nocopy varchar2,
44 x_err_msg out nocopy varchar2,
45 x_return_status out nocopy varchar2) IS
46
47 Cursor Usage_Update (p_group_id number, p_wip_entity_id number,
48 p_organization_id number, p_operation_seq_num number,
49 p_resource_seq_num number) IS
50
51 SELECT distinct wip_entity_id , organization_id, operation_seq_num,
52 resource_seq_num, start_date, completion_date, assigned_units,
53 last_update_date, last_updated_by, creation_date, created_by,
54 last_update_login, program_application_id, request_id, program_id,
55 program_update_date, substitution_type
56 FROM WIP_JOB_DTLS_INTERFACE
57 WHERE group_id = p_group_id
58 AND wip_entity_id = p_wip_entity_id
59 AND organization_id = p_organization_id
60 AND operation_seq_num = p_operation_seq_num
61 AND resource_seq_num = p_resource_seq_num
62 AND load_type = WIP_JOB_DETAILS.WIP_RES_USAGE
63 ORDER BY start_date;
64
65 l_start_date date;
66 l_end_date date;
67 tmp1 number;
68 x_statement varchar2(2000);
69
70 BEGIN
71
72 l_start_date := sysdate;
73 l_end_date := sysdate;
74 tmp1 := 0;
75 x_statement := NULL;
76
77 begin
78
79 IF p_group_id IS NULL OR p_organization_id IS NULL OR p_wip_entity_id IS NULL
80 OR p_operation_seq_num IS NULL OR p_resource_seq_num IS NULL THEN
81 x_err_code := SQLCODE;
82 x_err_msg := 'Error in wiprustb.pls: Primary key cannot be null!';
83 x_return_status := FND_API.G_RET_STS_ERROR;
84 return;
85 END IF;
86
87 /************************************************************************
88 CHECK THAT IF GROUP_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
89 OPERATION_SEQ_NUM AND RESOURCE_SEQ_NUM IS NULL
90 **********************************************************************/
91
92 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
93 WHERE wip_entity_id = p_wip_entity_id
94 AND organization_id = p_organization_id
95 AND operation_seq_num = p_operation_seq_num
96 AND resource_seq_num = p_resource_seq_num;
97
98 /**********************DELETE ALL EXISTING RECORDS BEFORE ADD *************/
99
100 FOR cur_update IN Usage_Update(p_group_id , p_wip_entity_id,
101 p_organization_id, p_operation_seq_num,
102 p_resource_seq_num ) LOOP
103
104 IF cur_update.substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
105
106 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
107 ( WIP_ENTITY_ID ,
108 ORGANIZATION_ID,
109 OPERATION_SEQ_NUM,
110 RESOURCE_SEQ_NUM,
111 START_DATE ,
112 COMPLETION_DATE,
113 ASSIGNED_UNITS ,
114 LAST_UPDATE_DATE,
115 LAST_UPDATED_BY,
116 CREATION_DATE,
117 CREATED_BY,
118 LAST_UPDATE_LOGIN,
119 REQUEST_ID,
120 PROGRAM_APPLICATION_ID,
121 PROGRAM_ID,
122 PROGRAM_UPDATE_DATE )
123 VALUES
124 ( cur_update.wip_entity_id,
125 cur_update.organization_id,
126 cur_update.operation_seq_num,
127 cur_update.resource_seq_num,
128 cur_update.start_date,
129 cur_update.completion_date,
130 cur_update.assigned_units,
131 cur_update.last_update_date,
132 cur_update.last_updated_by,
133 cur_update.creation_date,
134 cur_update.created_by,
135 cur_update.last_update_login,
136 cur_update.request_id,
137 cur_update.program_application_id,
138 cur_update.program_id,
139 cur_update.program_update_date);
140
141 END IF;
142
143 END LOOP;
144
145 exception
146 When others then
147 x_err_code := SQLCODE;
148 x_err_msg := 'Error in wiprudfb: '|| SQLERRM;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 return;
151 end;
152
153 END Sub_Usage;
154
155 END WIP_RES_USAGE_SUBSTITUTIONS;