[Home] [Help]
PACKAGE BODY: APPS.WIP_MATERIAL_PROCESSOR
Source
1 PACKAGE BODY wip_material_processor AS
2 /* $Header: wipmatpb.pls 115.8 2003/05/16 18:26:13 ccai ship $ */
3
4 FUNCTION wroUpdate(p_header_id IN NUMBER)
5 return boolean IS
6 l_wlcRec wip_lpn_completions%ROWTYPE;
7 BEGIN
8 SELECT *
9 INTO l_wlcRec
10 FROM wip_lpn_completions
11 WHERE header_id = p_header_id;
12
13 UPDATE wip_requirement_operations
14 SET quantity_issued = quantity_issued - ROUND(l_wlcRec.primary_quantity, 6),
15 quantity_allocated = greatest(0, quantity_allocated + ROUND(l_wlcRec.primary_quantity, 6)),
16 last_update_date = l_wlcRec.last_update_date,
17 last_updated_by = l_wlcRec.last_updated_by,
18 request_id = -1,
19 program_application_id = decode(l_wlcRec.program_application_id,
20 -1, program_application_id,
21 l_wlcRec.program_application_id),
22 program_update_date = nvl(l_wlcRec.program_update_date, program_update_date)
23 WHERE wip_entity_id = l_wlcRec.wip_entity_id
24 AND organization_id = l_wlcRec.organization_id
25 AND repetitive_schedule_id is null
26 AND operation_seq_num = l_wlcRec.operation_seq_num
27 AND inventory_item_id = l_wlcRec.inventory_item_id;
28
29 if(SQL%NOTFOUND) then
30 return FALSE;
31 else
32 return TRUE;
33 end if;
34 --header_id is PK of wip_lpn_completions, so TOO_MANY_ROWS will never occur
35
36 EXCEPTION
37 WHEN NO_DATA_FOUND then
38 return FALSE;
39 END wroUpdate;
40
41 FUNCTION wroInsert(p_header_id IN NUMBER)
42 return boolean IS
43 l_wlcRec wip_lpn_completions%ROWTYPE;
44 BEGIN
45 SELECT *
46 INTO l_wlcRec
47 FROM wip_lpn_completions
48 WHERE header_id = p_header_id;
49
50 INSERT INTO WIP_REQUIREMENT_OPERATIONS
51 (INVENTORY_ITEM_ID,
52 ORGANIZATION_ID,
53 WIP_ENTITY_ID,
54 OPERATION_SEQ_NUM,
55 REPETITIVE_SCHEDULE_ID,
56 CREATION_DATE,
57 CREATED_BY,
58 LAST_UPDATE_LOGIN,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 DEPARTMENT_ID,
62 DATE_REQUIRED,
63 REQUIRED_QUANTITY,
64 QUANTITY_ISSUED,
65 QUANTITY_PER_ASSEMBLY,
66 WIP_SUPPLY_TYPE,
67 MRP_NET_FLAG,
68 REQUEST_ID,
69 PROGRAM_APPLICATION_ID,
70 PROGRAM_ID,
71 PROGRAM_UPDATE_DATE,
72 SUPPLY_SUBINVENTORY,
73 SUPPLY_LOCATOR_ID,
74 MPS_DATE_REQUIRED,
75 MPS_REQUIRED_QUANTITY,
76 SEGMENT1,
77 SEGMENT2,
78 SEGMENT3,
79 SEGMENT4,
80 SEGMENT5,
81 SEGMENT6,
82 SEGMENT7,
83 SEGMENT8,
84 SEGMENT9,
85 SEGMENT10,
86 SEGMENT11,
87 SEGMENT12,
88 SEGMENT13,
89 SEGMENT14,
90 SEGMENT15,
91 SEGMENT16,
92 SEGMENT17,
93 SEGMENT18,
94 SEGMENT19,
95 SEGMENT20)
96 SELECT l_wlcRec.inventory_item_id,
97 l_wlcRec.organization_id,
98 l_wlcRec.wip_entity_id,
99 l_wlcRec.operation_seq_num,
100 NULL,
101 SYSDATE,
102 l_wlcRec.last_updated_by,
103 -1,
104 SYSDATE,
105 l_wlcRec.last_updated_by,
106 NULL,--l_wlcRec.dept_id...look in WIP_OPERATIONS table if you need this val
107 l_wlcRec.transaction_date,
108 0,
109 ROUND(l_wlcRec.primary_quantity, 6) * -1,
110 0,
111 wip_constants.PUSH, --WPUSH,--WIP_SUPPLY_TYPE, set to push???
112 wip_constants.SUPPLY_NET, --WYES, --MRP_NET_FLAG, set to yes???
113 to_number(NULL), --set request id to null?
114 DECODE(l_wlcRec.program_application_id, -1, NULL, l_wlcRec.program_application_id),
115 DECODE(l_wlcRec.program_id, -1, NULL, l_wlcRec.program_application_id),
116 l_wlcRec.program_update_date,
117 WIP_SUPPLY_SUBINVENTORY,
118 WIP_SUPPLY_LOCATOR_ID,
119 l_wlcRec.transaction_date,
120 0,
121 SEGMENT1,
122 SEGMENT2,
123 SEGMENT3,
124 SEGMENT4,
125 SEGMENT5,
126 SEGMENT6,
127 SEGMENT7,
128 SEGMENT8,
129 SEGMENT9,
130 SEGMENT10,
131 SEGMENT11,
132 SEGMENT12,
133 SEGMENT13,
134 SEGMENT14,
135 SEGMENT15,
136 SEGMENT16,
137 SEGMENT17,
138 SEGMENT18,
139 SEGMENT19,
140 SEGMENT20
141 FROM MTL_SYSTEM_ITEMS
142 WHERE ORGANIZATION_ID = l_wlcRec.organization_id
143 AND INVENTORY_ITEM_ID = l_wlcRec.inventory_item_id;
144
145 RETURN TRUE;
146
147
148 EXCEPTION
149 WHEN OTHERS then --invalid insertion into wip_requirement_operations or invalid header_id
150 return FALSE;
151 END wroInsert;
152
153
154 --the public procedure for this package. first try to update an existing requirement
155 --if it don't exist, insert a new one (push txn case)
156 --if for some reason that fails, return an error
157 PROCEDURE processItem(p_header_id IN NUMBER,
158 x_err_msg OUT NOCOPY VARCHAR2,
159 x_return_status OUT NOCOPY VARCHAR2) IS BEGIN
160
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162 if(not wroUpdate(p_header_id)) then
163 if(not wroInsert(p_header_id)) then
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 x_err_msg := fnd_message.get_string('WIP', 'TRANSACTION_FAILED') || ' ' || fnd_message.get_string('WIP', 'OPERATION_PROCESSING_ERROR');
166 end if;
167 end if;
168
169 exception
170 when others then
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 END processItem;
173 END wip_material_processor;