1 PACKAGE gms_pa_costing_pkg AUTHID CURRENT_USER AS
2 -- $Header: gmspafcs.pls 120.7.12020000.2 2012/10/16 14:00:33 navemish ship $
3
4 g_debug_context varchar2(1) := NULL;
5 g_packet_id number := to_number(null);
6 g_set_of_books_id number := to_number(null);
7 g_request_id number := to_number(null);
8 g_txn_source varchar2(30) := NULL;
9 g_error_stage varchar2(200);
10 g_batch_name varchar2(30) := null;
11 g_txn_xface_id number;
12
13 --=============================================================================
14 -- Procedure Fundscheck_CDL: Fundschecking procedure which is called from
15 -- Projects Costing processes.
16 --
17 -- Input Parameters :
18 -- Request ID of the calling Costing process.
19 --
20 -- Output Parameters :
21 -- return_status : 0 if successful, else a non-zero numeric value.
22 -- error_code : Error code of the failure
23 -- error_stage : Stage where error occured.
24 --=============================================================================
25
26 Procedure FundsCheck_CDL (p_request_id IN NUMBER,
27 p_return_status OUT NOCOPY NUMBER,
28 p_error_code OUT NOCOPY VARCHAR2,
29 p_error_stage OUT NOCOPY NUMBER);
30
31 --=============================================================================
32 -- Procedure : fundscheck_tieback
33 -- Purpose : Tieback status of CDL's that were Grants funds checked during
34 -- Costing.
35 --
36 -- Parameters and meaning.
37 -- -----------------------
38 -- p_request_id : Request_id of the costing process being run.
39 -- p_return_status : Return status is 0 for success and -1 for failure.
40 -- p_error_code : Error Code for the failure.
41 -- p_error_stage : Stage where the failure occured.
42 --=============================================================================
43 Procedure FundsCheck_TieBack (p_request_id IN NUMBER,
44 p_return_status OUT NOCOPY NUMBER,
45 p_error_code OUT NOCOPY VARCHAR2,
46 p_error_stage OUT NOCOPY NUMBER);
47
48 --=============================================================================
49 -- Procedure : Net_zero_adls
50 -- Purpose : Adls creation logic for the dummy additional exp created
51 -- to correct the accounting adjustments.
52 -- These are new zero transactions.
53 --
54 -- Parameters and meaning.
55 -- -----------------------
56 -- p_transaction_source : Transaction source for supplier cost interface.
57 -- p_batch : Batch name for transaction source.
58 -- p_status : return status .
59 -- P_xface_id : Transaction interface ID.
60 --=============================================================================
61 procedure Net_zero_adls( p_transaction_source IN VARCHAR2,
62 p_batch IN VARCHAR2,
63 P_xface_id IN NUMBER,
64 p_status IN OUT NOCOPY VARCHAR2 ) ;
65
66 --=============================================================================
67 -- Procedure : Fundscheck_Supplier_Cost
68 -- Purpose : Funds Check supplier invoice distributions being interfaced to
69 -- projects against an award budget.
70 --
71 -- Parameters and meaning.
72 -- -----------------------
73 -- p_transaction_source : Transaction source of the record being processed.
74 -- p_current_batch : Current interface batch being processed.
75 -- p_txn_interface_id : Transaction Identifier.
76 -- p_request_id : Request_id of the interface process.
77 -- p_status : Execution status of the procedure.
78 -- FND_API.G_RET_STS_SUCCESS for success and
79 -- FND_API.G_RET_STS_UNEXP_ERROR for unexpected error.
80 --=============================================================================
81 PROCEDURE Fundscheck_Supplier_Cost(p_transaction_source IN VARCHAR2,
82 p_txn_interface_id IN NUMBER ,
83 p_request_id IN NUMBER,
84 p_status IN OUT NOCOPY VARCHAR2);
85
86 --=============================================================================
87 -- Procedure : Tieback_Interface
88 -- Purpose : Tieback grants related transactions during interface of supplier
89 -- invoice to projects.
90 --
91 -- Parameters and meaning.
92 -- -----------------------
93 -- p_request_id : Request_id of the interface process.
94 -- p_status : Execution status of the procedure.
95 -- FND_API.G_RET_STS_SUCCESS for success and
96 -- FND_API.G_RET_STS_UNEXP_ERROR for unexpected error.
97 --=============================================================================
98 PROCEDURE Tieback_Interface(p_request_id IN NUMBER,
99 p_status IN OUT NOCOPY VARCHAR2
100 );
101
102 --
103 --=============================================================================
104 -- Function : grants_implemented
105 -- Purpose : Function checks if grants is implemented for the OU. If so,
106 -- return 'Y' else return 'N'
107 --=============================================================================
108 FUNCTION grants_implemented return VARCHAR2;
109 --
110 --
111 -- Variable declarations
112
113 TYPE tt_project_id is table of gms_bc_packets.project_id%TYPE;
114 TYPE tt_award_id is table of gms_bc_packets.award_id%TYPE;
115 TYPE tt_task_id is table of gms_bc_packets.task_id%TYPE;
116 TYPE tt_expenditure_type is table of gms_bc_packets.expenditure_type%TYPE;
117 TYPE tt_expenditure_item_date is table of gms_bc_packets.expenditure_item_date%TYPE;
118 TYPE tt_actual_flag is table of gms_bc_packets.actual_flag%TYPE;
119 TYPE tt_status_code is table of gms_bc_packets.status_code%TYPE;
120 TYPE tt_last_update_date is table of gms_bc_packets.last_update_date%TYPE;
121 TYPE tt_last_updated_by is table of gms_bc_packets.last_updated_by%TYPE;
122 TYPE tt_created_by is table of gms_bc_packets.created_by%TYPE;
123 TYPE tt_creation_date is table of gms_bc_packets.creation_date%TYPE;
124 TYPE tt_last_update_login is table of gms_bc_packets.last_update_login%TYPE;
125 TYPE tt_je_category_name is table of gms_bc_packets.je_category_name%TYPE;
126 TYPE tt_je_source_name is table of gms_bc_packets.je_source_name%TYPE;
127 TYPE tt_transfered_flag is table of gms_bc_packets.transfered_flag%TYPE;
128 TYPE tt_document_type is table of gms_bc_packets.document_type%TYPE;
129 TYPE tt_expenditure_organization_id is table of gms_bc_packets.expenditure_organization_id%TYPE;
130 TYPE tt_document_header_id is table of gms_bc_packets.document_header_id%TYPE;
131 TYPE tt_document_distribution_id is table of gms_bc_packets.document_distribution_id%TYPE;
132 TYPE tt_entered_dr is table of gms_bc_packets.entered_dr%TYPE;
133 TYPE tt_entered_cr is table of gms_bc_packets.entered_cr%TYPE;
134 TYPE tt_status_flag is table of gms_bc_packets.status_flag%TYPE;
135 TYPE tt_bc_packet_id is table of gms_bc_packets.bc_packet_id%TYPE;
136 TYPE tt_request_id is table of gms_bc_packets.request_id%TYPE;
137 TYPE tt_ind_compiled_set_id is table of gms_bc_packets.ind_compiled_set_id%TYPE;
138 TYPE tt_person_id is table of gms_bc_packets.person_id%TYPE;
139 TYPE tt_job_id is table of gms_bc_packets.job_id%TYPE;
140 TYPE tt_expenditure_category is table of gms_bc_packets.expenditure_category%TYPE;
141 TYPE tt_revenue_category is table of gms_bc_packets.revenue_category%TYPE;
142 TYPE tt_adjusted_document_header_id is table of gms_bc_packets.adjusted_document_header_id%TYPE;
143 TYPE tt_award_set_id is table of gms_bc_packets.award_set_id%TYPE;
144 TYPE tt_transaction_source is table of gms_bc_packets.transaction_source%TYPE;
145 TYPE tt_burdenable_raw_cost is table of gms_award_distributions.burdenable_raw_cost%TYPE;
146 TYPE tt_acct_raw_cost is table of pa_transaction_interface_all.acct_raw_cost%TYPE;
147 TYPE tt_line_type_lookup is table of ap_invoice_distributions_all.line_type_lookup_code%TYPE;
148 TYPE tt_invoice_type_lookup is table of ap_invoices_all.invoice_type_lookup_code%TYPE;
149
150 --REL12 : AP lines uptake enhancement : Added below plsql tables
151 TYPE tt_invoice_id is table of ap_invoices_all.invoice_id%TYPE;
152 TYPE tt_invoice_distribution_id is table of ap_invoice_distributions_all.invoice_distribution_id%TYPE;
153 TYPE tt_sys_ref4 IS table of pa_transaction_interface_all.cdl_system_reference4%TYPE;
154 TYPE tt_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
155 TYPE tt_txn_interface_id is table of pa_transaction_interface_all.txn_interface_id%TYPE;
156 Type tt_nz_adj_flag is table of pa_transaction_interface_all.net_zero_adjustment_flag%TYPE ;
157 --
158
159 cursor get_xface_cur is
160 select xface.interface_id, -- Bug 5389130
161 xface.txn_interface_id,
162 xface.transaction_source,
163 apinv.invoice_id,
164 apinv.invoice_distribution_id , -- REL12 : Ap lines uptake
165 xface.cdl_system_reference4 , -- REL12 : Ap lines uptake
166 apinv.project_id,
167 apinv.task_id,
168 adl.award_id,
169 adl.ind_compiled_set_id,
170 adl.burdenable_raw_cost,
171 adl.bud_task_id,
172 xface.expenditure_type,
173 xface.expenditure_item_date,
174 apinv.expenditure_organization_id,
175 apinv.line_type_lookup_code,
176 api.invoice_type_lookup_code,
177 xface.acct_raw_cost,
178 api.vendor_id,
179 pet.expenditure_category,
180 pet.revenue_category_code,
181 xface.adjusted_expenditure_item_id --REL12 : Ap lines uptake
182 , xface.net_zero_adjustment_flag
183 , adl.fc_status -- R12 AP lines uptake : Prepayment changes
184 from ap_invoice_distributions apinv,
185 pa_transaction_interface xface,
186 gms_award_distributions adl,
187 ap_invoices api,
188 pa_expenditure_types pet
189 where xface.transaction_source = G_txn_source
190 and xface.txn_interface_id = G_txn_xface_id
191 and xface.cdl_system_reference2 = apinv.invoice_id
192 and xface.cdl_system_reference5 = apinv.invoice_distribution_id --REL12 : Ap lines uptake
193 and apinv.invoice_id = adl.invoice_id
194 and apinv.distribution_line_number = adl.distribution_line_number
195 and apinv.invoice_id = api.invoice_id
196 and apinv.award_id = adl.award_set_id
197 and apinv.expenditure_type = pet.expenditure_type
198 and nvl(apinv.pa_addition_flag, 'X') <> 'T'
199 and adl.document_type = 'AP'
200 and adl.adl_status = 'A'
201 union /* BUG 14216205 : Added the union for SAT */
202 SELECT xface.interface_id,
203 xface.txn_interface_id,
204 XFACE.TRANSACTION_SOURCE,
205 APSAT.INVOICE_ID,
206 apsat.invoice_distribution_id ,
207 XFACE.CDL_SYSTEM_REFERENCE4 ,
208 APSAT.PROJECT_ID,
209 apsat.task_id,
210 adl.award_id,
211 adl.ind_compiled_set_id,
212 adl.burdenable_raw_cost,
213 adl.bud_task_id,
214 xface.expenditure_type,
215 XFACE.EXPENDITURE_ITEM_DATE,
216 APSAT.EXPENDITURE_ORGANIZATION_ID,
217 apsat.line_type_lookup_code,
218 api.invoice_type_lookup_code,
219 xface.acct_raw_cost,
220 api.vendor_id,
221 pet.expenditure_category,
222 pet.revenue_category_code,
223 xface.adjusted_expenditure_item_id ,
224 xface.net_zero_adjustment_flag ,
225 ADL.FC_STATUS
226 FROM AP_SELF_ASSESSED_TAX_DIST apsat,
227 pa_transaction_interface xface,
228 gms_award_distributions adl,
229 ap_invoices api,
230 PA_EXPENDITURE_TYPES PET
231 where xface.transaction_source = G_txn_source
232 and XFACE.TXN_INTERFACE_ID = G_TXN_XFACE_ID
233 and XFACE.CDL_SYSTEM_REFERENCE2 = APSAT.INVOICE_ID
234 and XFACE.CDL_SYSTEM_REFERENCE5 = APSAT.INVOICE_DISTRIBUTION_ID
235 and APSAT.INVOICE_ID = ADL.INVOICE_ID
236 and APSAT.DISTRIBUTION_LINE_NUMBER = ADL.DISTRIBUTION_LINE_NUMBER
237 and APSAT.INVOICE_ID = API.INVOICE_ID
238 and APSAT.AWARD_ID = ADL.AWARD_SET_ID
239 and APSAT.EXPENDITURE_TYPE = PET.EXPENDITURE_TYPE
240 AND NVL(apsat.pa_addition_flag, 'X') <> 'T'
241 and ADL.DOCUMENT_TYPE = 'AP'
242 AND adl.adl_status = 'A';
243
244 --REL12 : AP lines uptake enhancement
245 -- Cursor to fetch pa_transaction_interface_all records having same invoice_id ,invoice_distribution_id
246 -- and invoice_payment_id. These records correspond to adjusted/non adjusted expenditure items
247 -- associated with original invoice distribution.
248
249 cursor c_txn_details (p_sys_ref2 VARCHAR2,
250 p_sys_ref5 VARCHAR2,
251 p_sys_ref4 VARCHAR2,
252 p_interface_id NUMBER) IS
253 select xface.txn_interface_id,
254 xface.transaction_source,
255 to_number(xface.cdl_system_reference2),
256 to_number(xface.cdl_system_reference5),
257 xface.cdl_system_reference4,
258 xface.project_id,
259 xface.task_id,
260 adl.award_id,
261 adl.ind_compiled_set_id,
262 adl.burdenable_raw_cost,
263 adl.bud_task_id,
264 xface.expenditure_type,
265 xface.expenditure_item_date,
266 NVL(xface.override_to_organization_id,xface.org_id) expenditure_organization_id,
267 xface.acct_raw_cost,
268 pet.expenditure_category,
269 pet.revenue_category_code,
270 xface.adjusted_expenditure_item_id ,
271 xface.net_zero_adjustment_flag
272 from pa_transaction_interface xface,
273 gms_award_distributions adl,
274 pa_expenditure_types pet
275 where xface.transaction_source = G_txn_source
276 and xface.cdl_system_reference2 = p_sys_ref2
277 and xface.cdl_system_reference5 = p_sys_ref5
278 and (xface.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
279 and xface.interface_id = p_interface_id
280 and xface.TRANSACTION_STATUS_CODE = 'P'
281 and adl.expenditure_item_id = xface.adjusted_expenditure_item_id
282 and adl.adl_line_num = (SELECT max(adl1.adl_line_num)
283 FROM gms_award_distributions adl1
284 WHERE adl1.expenditure_item_id = adl.expenditure_item_id
285 AND adl1.award_id= adl.award_id
286 AND adl1.adl_status = 'A'
287 AND adl1.document_type = 'EXP')
288 and xface.expenditure_type = pet.expenditure_type
289 and adl.document_type = 'EXP'
290 ORDER BY adl.award_id,
291 xface.project_id,
292 xface.task_id;
293
294 END gms_pa_costing_pkg;
[Home] [Help]