1 PACKAGE BODY GMF_BATCH_VALIDATIONS AS
2 /* $Header: GMFBCHVB.pls 120.5 2011/09/28 15:40:28 pvkanetk noship $ */
3 --****************************************************************************************************
4 --* *
5 --* Oracle Process Manufacturing *
6 --* ============================ *
7 --* *
8 --* Package Body GMF_BATCH_VALIDATIONS *
9 --* --------------------------- *
10 --* Description: This package body contains validations called from Process Execution *
11 --* *
12 --* Author: OPM Development *
13 --* Date: 05-MAY-2011 *
14 --* *
15 --* History *
16 --* PARAG KANETKAR Created a new package Bug 12432083. *
17 --* *
18 --* Function check_cancel_batch Checks if a batch can be canceled. *
19 --* Returns BOOLEAN TRUE if batch can be canceled. *
20 --* FALSE if batch can not be canceled and should be closed *
21 --* Process Execution will call GMF_BATCH_VALIDATIONS.check_cancel_batch when attempting to *
22 --* cancel a batch. If this returns TRUE a batch would be canceled. If return is FALSE then *
23 --* Process execution will require the batch to be CLOSED and NOT CANCELED. All activity will be *
24 --* zeroed out when this code is called. *
25 --* Process Execution bug for related changes Bug 12418545. *
26 --* *
27 --* Pseudo Code for Function check_cancel_batch *
28 --* A) Check if transactions exist. If Transactions do not exist return TRUE *
29 --* B) If transactions Exist and are in different periods return FALSE. This would happen *
30 --* if batch is released and transacted in one period and is being canceled after the *
31 --* period in which batch is opened is closed in Inventory. *
32 --* If Transactions are in different periods WIP account may not get zeroed out for this *
33 --* batch. Bug 12418545. OPM Fin Bug 12432083. *
34 --* C) Transactions are all within one period for subsequent checks. *
35 --* Return TRUE. *
36 --****************************************************************************************************
37
38 FUNCTION check_cancel_batch(p_batch_id IN NUMBER)
39 RETURN Boolean
40 IS
41
42 CURSOR Min_max_trans_date IS
43 SELECT min(trans_date), max(trans_date) from
44 (SELECT t.transaction_date trans_date
45 FROM gme_batch_header h, mtl_material_transactions t
46 WHERE h.batch_id = p_batch_id
47 AND h.organization_id = t.organization_id
48 AND h.batch_id = t.transaction_source_id
49 AND t.transaction_source_type_id = 5
50 UNION
51 SELECT rt.trans_date
52 FROM gme_batch_header h, gme_resource_txns rt
53 WHERE h.batch_id = p_batch_id
54 AND h.batch_id = rt.doc_id
55 AND rt.completed_ind = 1
56 AND rt.doc_type = 'PROD') txn;
57
58 CURSOR Get_period_id (p_date DATE) IS
59 SELECT gps.period_id
60 FROM gmf_period_statuses gps, gmf_organization_definitions god, gmf_fiscal_policies gfp,
61 gme_batch_header gbh, gmf_calendar_assignments gca
62 WHERE gbh.batch_id = p_batch_id
63 AND gbh.organization_id = god.organization_id
64 AND god.legal_entity_id = gfp.legal_entity_id
65 AND gfp.delete_mark = 0
66 AND gfp.cost_type_id = gps.cost_type_id
67 AND gfp.cost_type_id = gca.cost_type_id
68 AND god.legal_entity_id = gca.legal_entity_id
69 AND gca.calendar_code = gps.calendar_code
70 AND gps.start_date <= p_date
71 AND gps.end_date >= p_date;
72
73 l_min_date DATE;
74 l_max_date DATE;
75 l_min_period_id NUMBER;
76 l_max_period_id NUMBER;
77 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
78 g_pkg_name VARCHAR2(32) := 'GMF_BATCH_VALIDATIONS';
79 l_api_name VARCHAR2(32) := 'check_cancel_batch';
80 err_num NUMBER;
81 err_msg VARCHAR2(100);
82
83 BEGIN
84
85 OPEN Min_max_trans_date;
86 FETCH Min_max_trans_date INTO l_min_date, l_max_date;
87
88 -- Bug 12952874 Cursor Min_max_trans_date always returns a record due to Min and Max function use.
89 -- Changed cursor NOTFOUND check to dates being NULL.
90
91 IF ((l_min_date IS NULL) OR (l_max_date IS NULL)) THEN
92 CLOSE Min_max_trans_date;
93 IF g_debug <= gme_debug.g_log_statement THEN
94 gme_debug.put_line ( g_pkg_name
95 || '.'
96 || l_api_name
97 || ':'
98 || 'No transactions exist for the batch');
99 END IF;
100 RETURN TRUE;
101 END IF;
102 CLOSE Min_max_trans_date;
103
104 OPEN Get_period_id(l_min_date);
105 FETCH Get_period_id INTO l_min_period_id;
106 IF (Get_period_id%NOTFOUND) THEN
107 CLOSE Get_period_id;
108 gme_debug.put_line ( g_pkg_name
109 || '.'
110 || l_api_name
111 || ':'
112 || 'Period not found for Min date '
113 || to_char(l_min_date,'MM/DD/RRRR HH24:MI:SS'));
114
115 GMF_LAYERS.log_message (
116 p_table_name => 'GMF_BATCH_VALIDATIONS',
117 p_procedure_name => 'check_cancel_batch',
118 p_parameters => p_batch_id,
119 p_message => 'Costing period Not found for Earliest Transaction Date '||to_char(l_min_date,'MM/DD/RRRR HH24:MI:SS'),
120 p_error_type => 'E');
121
122 RETURN FALSE;
123 END IF;
124 CLOSE Get_period_id;
125
126 OPEN Get_period_id(l_max_date);
127 FETCH Get_period_id INTO l_max_period_id;
128 IF (Get_period_id%NOTFOUND) THEN
129 CLOSE Get_period_id;
130 gme_debug.put_line ( g_pkg_name
131 || '.'
132 || l_api_name
133 || ':'
134 || 'Period not found for Max date '
135 || to_char(l_max_date,'MM/DD/RRRR HH24:MI:SS'));
136
137 GMF_LAYERS.log_message (
138 p_table_name => 'GMF_BATCH_VALIDATIONS',
139 p_procedure_name => 'check_cancel_batch',
140 p_parameters => p_batch_id,
141 p_message => 'Costing period Not found for Latest Transaction Date '||to_char(l_max_date,'MM/DD/RRRR HH24:MI:SS'),
142 p_error_type => 'E');
143
144 RETURN FALSE;
145 END IF;
146 CLOSE Get_period_id;
147
148 IF g_debug <= gme_debug.g_log_statement THEN
149 gme_debug.put_line ( g_pkg_name
150 || '.'
151 || l_api_name
152 || ':'
153 || 'l_min_period_id ' ||l_min_period_id
154 || 'l_max_period_id ' ||l_max_period_id);
155 END IF;
156
157 IF l_min_period_id <> l_max_period_id THEN
158
159 GMF_LAYERS.log_message (
160 p_table_name => 'GMF_BATCH_VALIDATIONS',
161 p_procedure_name => 'check_cancel_batch',
162 p_parameters => p_batch_id,
163 p_message => 'Transactions span costing periods. WIP Account may not be cleared out for this batch if it is Canceled.',
164 p_error_type => 'E');
165
166 RETURN FALSE;
167 ELSE
168 RETURN TRUE;
169 END IF;
170
171 EXCEPTION
172 WHEN OTHERS
173 THEN
174 err_num := SQLCODE;
175 err_msg := SUBSTRB(SQLERRM, 1, 100);
176 gme_debug.put_line ( g_pkg_name
177 || '.'
178 || l_api_name
179 || ':'
180 || 'Exception Error '||err_num
181 || ' Error Message '||err_msg );
182 RETURN FALSE;
183
184 END check_cancel_batch;
185 END GMF_BATCH_VALIDATIONS;
[Home] [Help]