1 PACKAGE BODY WIP_PENDING_TXNS AS
2 /* $Header: wippendb.pls 120.1 2005/06/21 13:55:44 appldev ship $ */
3
4 /*
5 Inputs: These identify which job or schedule you are interested in
6 P_WIP_ENTITY_ID
7 P_ORGANIZATION_ID
8 Outputs: All are set to WIP_CONSTANTS.YES or WIP_CONSTANTS.NO
9 P_RESOURCES_CHARGED
10 P_MATERIAL_ISSUED
11 P_MTL_TXNS_TEMP
12 P_UNCOSTED_MTL_TXNS
13 P_PO_REQ_LINES
14 P_PO_REQ_INTERFACE
15 P_MOVE_TXN_INTERFACE
16 P_COST_TXN_INTERFACE
17
18 */
19
20 PROCEDURE RETRIEVE(
21 P_WIP_ENTITY_ID IN NUMBER,
22 P_ORGANIZATION_ID IN NUMBER,
23 P_RESOURCES_CHARGED OUT NOCOPY NUMBER,
24 P_MATERIAL_ISSUED OUT NOCOPY NUMBER,
25 P_MTL_TXNS_TEMP OUT NOCOPY NUMBER,
26 P_UNCOSTED_MTL_TXNS OUT NOCOPY NUMBER,
27 P_PO_REQ_LINES OUT NOCOPY NUMBER,
28 P_PO_REQ_INTERFACE OUT NOCOPY NUMBER,
29 P_MOVE_TXN_INTERFACE OUT NOCOPY NUMBER,
30 P_COST_TXN_INTERFACE OUT NOCOPY NUMBER
31 ) IS
32 BEGIN
33
34 -- Check if resources have been charged
35
36 BEGIN
37
38 SELECT WIP_CONSTANTS.YES
39 INTO P_RESOURCES_CHARGED
40 FROM DUAL
41 WHERE EXISTS (SELECT 'X'
42 FROM WIP_OPERATION_RESOURCES
43 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
44 AND WIP_ENTITY_ID = P_WIP_ENTITY_ID
45 AND APPLIED_RESOURCE_UNITS <> 0);
46
47
48 EXCEPTION WHEN NO_DATA_FOUND THEN
49 P_RESOURCES_CHARGED := WIP_CONSTANTS.NO;
50 END;
51
52 -- Check if material has been issued
53
54 BEGIN
55
56 SELECT WIP_CONSTANTS.YES
57 INTO P_MATERIAL_ISSUED
58 FROM DUAL
59 WHERE EXISTS (SELECT 'X'
60 FROM WIP_REQUIREMENT_OPERATIONS
61 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
62 AND WIP_ENTITY_ID = P_WIP_ENTITY_ID
63 AND QUANTITY_ISSUED <> 0);
64
65 EXCEPTION WHEN NO_DATA_FOUND THEN
66 P_MATERIAL_ISSUED := WIP_CONSTANTS.NO;
67 END;
68
69 -- Check for MTL_MATERIAL_TRANSACTIONS_TEMP
70
71 BEGIN
72
73 SELECT WIP_CONSTANTS.YES
74 INTO P_MTL_TXNS_TEMP
75 FROM DUAL
76 WHERE EXISTS (SELECT 1
77 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
78 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
79 AND TRANSACTION_SOURCE_ID = P_WIP_ENTITY_ID
80 AND TRANSACTION_SOURCE_TYPE_ID = 5);
81
82 EXCEPTION WHEN NO_DATA_FOUND THEN
83 P_MTL_TXNS_TEMP := WIP_CONSTANTS.NO;
84 END;
85
86 -- Check for uncosted material transactions
87
88 BEGIN
89
90 SELECT WIP_CONSTANTS.YES
91 INTO P_UNCOSTED_MTL_TXNS
92 FROM DUAL
93 WHERE EXISTS (SELECT 1
94 FROM MTL_MATERIAL_TRANSACTIONS
95 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
96 AND TRANSACTION_SOURCE_ID = P_WIP_ENTITY_ID
97 AND COSTED_FLAG IN ('N','E')
98 AND TRANSACTION_SOURCE_TYPE_ID = 5);
99
100 EXCEPTION WHEN NO_DATA_FOUND THEN
101 P_UNCOSTED_MTL_TXNS := WIP_CONSTANTS.NO;
102 END;
103
104 -- Check for PO_REQUISITION_LINES
105
106 BEGIN
107
108 SELECT WIP_CONSTANTS.YES
109 INTO P_PO_REQ_LINES
110 FROM DUAL
111 WHERE EXISTS (SELECT 1
112 FROM PO_REQUISITION_LINES_ALL
113 WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
114 AND DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID);
115
116 EXCEPTION WHEN NO_DATA_FOUND THEN
117 P_PO_REQ_LINES := WIP_CONSTANTS.NO;
118 END;
119
120 -- Check for PO_REQUISITION_INTERFACE
121
122 BEGIN
123
124 SELECT WIP_CONSTANTS.YES
125 INTO P_PO_REQ_INTERFACE
126 FROM DUAL
127 WHERE EXISTS (SELECT 1
128 FROM PO_REQUISITIONS_INTERFACE_ALL
129 WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
130 AND DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID);
131
132 EXCEPTION WHEN NO_DATA_FOUND THEN
133 P_PO_REQ_INTERFACE := WIP_CONSTANTS.NO;
134 END;
135
136 -- Check for WIP_MOVE_TXN_INTERFACE
137
138 BEGIN
139
140 SELECT WIP_CONSTANTS.YES
141 INTO P_MOVE_TXN_INTERFACE
142 FROM DUAL
143 WHERE EXISTS (SELECT 1
144 FROM WIP_MOVE_TXN_INTERFACE
145 WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
146 AND ORGANIZATION_ID = P_ORGANIZATION_ID);
147
148 EXCEPTION WHEN NO_DATA_FOUND THEN
149 P_MOVE_TXN_INTERFACE := WIP_CONSTANTS.NO;
150 END;
151
152 -- Check for WIP_COST_TXN_INTERFACE
153
154 BEGIN
155
156 SELECT WIP_CONSTANTS.YES
157 INTO P_COST_TXN_INTERFACE
158 FROM DUAL
159 WHERE EXISTS (SELECT 1
160 FROM WIP_COST_TXN_INTERFACE
161 WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
162 AND ORGANIZATION_ID = P_ORGANIZATION_ID);
163
164 EXCEPTION WHEN NO_DATA_FOUND THEN
165 P_COST_TXN_INTERFACE := WIP_CONSTANTS.NO;
166 END;
167 END RETRIEVE;
168
169 END WIP_PENDING_TXNS;
[Home] [Help]