1 PACKAGE gms_pa_costing_pkg AS
2 -- $Header: gmspafcs.pls 120.7 2006/07/28 12:42:12 cmishra noship $
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
202 --REL12 : AP lines uptake enhancement
203 -- Cursor to fetch pa_transaction_interface_all records having same invoice_id ,invoice_distribution_id
204 -- and invoice_payment_id. These records correspond to adjusted/non adjusted expenditure items
205 -- associated with original invoice distribution.
206
207 cursor c_txn_details (p_sys_ref2 VARCHAR2,
208 p_sys_ref5 VARCHAR2,
209 p_sys_ref4 VARCHAR2,
210 p_interface_id NUMBER) IS
211 select xface.txn_interface_id,
212 xface.transaction_source,
213 to_number(xface.cdl_system_reference2),
214 to_number(xface.cdl_system_reference5),
215 xface.cdl_system_reference4,
216 xface.project_id,
217 xface.task_id,
218 adl.award_id,
219 adl.ind_compiled_set_id,
220 adl.burdenable_raw_cost,
221 adl.bud_task_id,
222 xface.expenditure_type,
223 xface.expenditure_item_date,
224 NVL(xface.override_to_organization_id,xface.org_id) expenditure_organization_id,
225 xface.acct_raw_cost,
226 pet.expenditure_category,
227 pet.revenue_category_code,
228 xface.adjusted_expenditure_item_id ,
229 xface.net_zero_adjustment_flag
230 from pa_transaction_interface xface,
231 gms_award_distributions adl,
232 pa_expenditure_types pet
233 where xface.transaction_source = G_txn_source
234 and xface.cdl_system_reference2 = p_sys_ref2
235 and xface.cdl_system_reference5 = p_sys_ref5
236 and (xface.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
237 and xface.interface_id = p_interface_id
238 and xface.TRANSACTION_STATUS_CODE = 'P'
239 and adl.expenditure_item_id = xface.adjusted_expenditure_item_id
240 and adl.adl_line_num = (SELECT max(adl1.adl_line_num)
241 FROM gms_award_distributions adl1
242 WHERE adl1.expenditure_item_id = adl.expenditure_item_id
243 AND adl1.award_id= adl.award_id
244 AND adl1.adl_status = 'A'
245 AND adl1.document_type = 'EXP')
246 and xface.expenditure_type = pet.expenditure_type
247 and adl.document_type = 'EXP'
248 ORDER BY adl.award_id,
249 xface.project_id,
250 xface.task_id;
251
252 END gms_pa_costing_pkg;
[Home] [Help]