[Home] [Help]
PACKAGE BODY: APPS.JAI_TRX_REPO_EXTRACT_PKG
Source
1 package body jai_trx_repo_extract_pkg as
2 /* $Header: jai_trx_repo_ext.plb 120.19.12010000.2 2008/08/05 07:21:10 nprashar ship $ */
3 /*------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY
5 ------------------------------------------------------------------------------------------------------------
6 Sl.No. Date Developer BugNo Version Remarks
7 ------------------------------------------------------------------------------------------------------------
8 1. 22-Dec-2005 brathod 5694855 115.1 Created the initial version
9
10 2. 05-Feb-2007 rchandan 5694855 115.9 Added a cursor c_get_rma_line_srvtyp to
11 fetch the service type from RMA tables.
12 This cursor is used if the line_category_code = 'RETURN'
13
14 3. 06-Jul-2007 brathod 6012570 120.4 Enhanced the logic to suppor PROJECT DRAFT INVOICES
15 changes are marked with bug number 5876390 or 6012570
16 R12 Fwd Porting Bug: 6012570
17
18 4. 23-Aug-2007 Bgowrava 6012570 120.6 modified the c_get_pa_details cursor query to select from the tables PA_DRAFT_INVOICES_ALL,
19 PA_PROJECTS_ALL instead of pa_draft_invoices_v. This was done to improve the performance
20 of the query
21
22 5. 24-Sep-2007 vkantamn 6083978 120.8 The org_id for the po has been changed to fetch from the
23 PO table.
24 Also New transaction source 'RECEIVING' has been added,
25 and the org_id has been picked from the
26 ja_in_rcv_transactions for the above invoices.
27 6. 04-Oct-2007 CSahoo 6457710 120.9 Added a ELSIF block related to projects in the procedure extract_rgm_trxs.
28
29 7. 10-Oct-2007 CSahoo 6457710 120.10 Modified the follwing cursors in get_document_details procedure
30 c_get_po_line_loc_srvtyp
31 c_get_so_line_srvtyp
32 c_get_rma_line_srvtyp
33 c_get_pa_inv_line_tax
34 c_get_ra_line_srvtyp
35
36 Added the cess and sh cess tax types in the AND clause.
37 8. 27-Sep-2007 Bill.Shi 120.11 Add the logic for AP standalone invoice.
38
39 9. 25-Feb-2008 rchandan 6843479 120.12 Issue : The PO Matched to Receipt transactions are not shown in the
40 'Service tax Repository Review' form after running the India Service Tax Processing' conc program.
41 Fix : This above issue has been fixed by adding a new elsif condition for the 'RECIVING'
42 in the procedure 'extract_rgm_trxs'.
43 This is forward port of bug#6323157
44
45 10. 08-Apr-2008 Lion 6977917 120.17 Issue: The standalone inovice transactions are not shown in the
46 'Service tax Repository Review' form after running the India Service Tax Processing' conc program.
47 Fix : This above issue has been fixed by adding a new elsif condition for the 'standalone'
48 in the procedure 'extract_rgm_trxs'.
49 11. 22-Apr-2008 Lion 6991108 120.18 Issue: SERVICE TYPE IN SERVICE TAX REPOSITORY IS BLANK
50 Fix: Modify cursor l_get_si_tax_amt_csr in Procedure get_document_details to get the right value.
51
52 12. 19-May-2008 Changes by nprashar for bug #6636517 , added a NVL clause in join condition of cursor c_get_po_details.
53
54 13 . 4-Aug-2008 Changes by nprashar for bug 7172723.
55 Issue : India ST Processing concurrent should consider Third Party Invoices and
56 update India Service Tax Credit register report
57 Fix : Modifed following procedure to use receipt information for Third party
58 invoices which do not have reference to PO
59 1 - get_doc_from_reference - Added logic for third party invoices which do not have references to PO
60 Modified cursor - c_get_refs_rec
61 Added cursor - c_get_source_type,c_get_line_number,c_get_doc_details,c_get_ra_line_srvtyp
62 --------------------------------------------------------------------------------------------------------------*/
63
64 /*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
65
66 /** Package level variables used in debug package*/
67 lv_object_name jai_cmn_debug_contexts.log_context%type default 'JAI_TRX_REPO_EXTRACT_PKG';
68 lv_member_name jai_cmn_debug_contexts.log_context%type;
69 lv_context jai_cmn_debug_contexts.log_context%type;
70
71 --
72 -- Global variables used throught the package
73 --
74 lv_user_id fnd_user.user_id%type default fnd_global.user_id;
75 lv_login_id fnd_logins.login_id%type default fnd_global.login_id;
76
77 function get_settled_service_type
78 ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
79 , p_document_id jai_trx_repo_extract_gt.document_id%type
80 , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
81 )
82 return varchar2;
83
84 procedure set_debug_context
85 is
86
87 begin
88 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
89 end set_debug_context;
90
91 /*------------------------------------------------------------------------------------------------------------*/
92 procedure extract_rgm_trxs
93 ( p_regime_code jai_rgm_trx_records.regime_code%type
94 , p_organization_id jai_rgm_trx_records.organization_id%type default null
95 , p_location_id jai_rgm_trx_records.location_id%type default null
96 , p_from_trx_date date default null
97 , p_to_trx_date date default null
98 , p_source jai_rgm_trx_records.source%type default null
99 , p_query_settled_flag varchar2 default 'N'
100 , p_query_only_null_srvtype varchar2 default 'N'
101 , p_process_message OUT NOCOPY varchar
102 , p_process_flag OUT NOCOPY varchar2
103 )
104 as
105 cursor c_get_repo_recs
106 is
107 select (recs.repository_id) repository_id
108 , nvl(refs.reference_id, recs.reference_id) reference_id
109 , refs.invoice_id
110 , refs.item_line_id
111 , recs.source
112 , recs.service_type_code
113 , nvl(recs.organization_id, recs.inv_organization_id) organization_id
114 , recs.location_id
115 , (nvl(trx_credit_amount,0) + nvl(trx_debit_amount,0)) repository_tax_amt
116 , recs.organization_type
117 , recs.source_document_id
118 from jai_rgm_trx_refs refs
119 , jai_rgm_trx_records recs
120 where recs.reference_id = refs.reference_id (+)
121 and ( p_organization_id is null
122 or (recs.organization_id = p_organization_id)
123 )
124 and (p_location_id is null or recs.location_id = p_location_id )
125 and trunc(transaction_date) between nvl (p_from_trx_date, trunc(transaction_date)) and nvl (p_to_trx_date, trunc(transaction_date))
126 and recs.regime_code = p_regime_code
127 and ( (p_query_settled_flag = 'N' and (recs.settlement_id is null))
128 or (p_query_settled_flag = jai_constants.yes)
129 )
130 and ( (p_query_only_null_srvtype = 'Y' and (recs.service_type_code is null))
131 or (p_query_only_null_srvtype = 'N')
132 )
133 and (p_source is null or p_source = recs.source )
134 and recs.organization_type = 'IO'
135 and recs.source in ('AP'
136 ,'AR'
137 ,'MANUAL'
138 ,'SERVICE_DISTRIBUTE_OUT'
139 --,'SERVICE_DISTRIBUTE_IN'
140 );
141 cursor c_get_organization_name (cp_organization_id hr_organization_units.organization_id%type)
142 is
143 select name
144 from hr_organization_units
145 where organization_id = cp_organization_id;
146
147 cursor c_get_location_name (cp_location_id hr_locations_all.location_id%type)
148 is
149 select description
150 from hr_locations_all
151 where location_id = cp_location_id;
152
153
154
155 cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
156 ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
157 ,cp_line_id jai_rgm_trx_refs.line_id%type
158 )
159 is
160 select recs.service_type_code
161 from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
162 where recs.reference_id = refs.reference_id
163 and refs.invoice_id = cp_invoice_id
164 and refs.line_id = cp_line_id
165 and refs.source = cp_source
166 and recs.settlement_id is not null
167 and recs.service_type_code is not null
168 and rownum = 1;
169
170 cursor c_get_src_rec ( cp_transfer_id jai_rgm_dis_src_hdrs.transfer_id%type
171 , cp_party_type jai_rgm_dis_src_hdrs.party_type%type
172 , cp_party_id jai_rgm_dis_src_hdrs.party_id%type
173 )
174 is
175 select transfer_number
176 ,transaction_date
177 ,party_id
178 ,location_id
179 from jai_rgm_dis_src_hdrs
180 where party_type = cp_party_type
181 and party_id = cp_party_id
182 and transfer_id = cp_transfer_id;
183
184 cursor c_get_dest_rec( cp_transfer_id jai_rgm_dis_des_hdrs.transfer_id%type
185 , cp_party_type jai_rgm_dis_des_hdrs.destination_party_type%type
186 , cp_party_id jai_rgm_dis_des_hdrs.destination_party_id%type
187 )
188 is
189 select transfer_number
190 ,creation_date transaction_date
191 ,destination_party_id
192 ,location_id
193 from JAI_RGM_DIS_DES_HDRS
194 where destination_party_type = cp_party_type
195 and destination_party_id = cp_party_id
196 and transfer_id = cp_transfer_id;
197
198 r_src_rec c_get_src_rec%rowtype;
199 r_dest_rec c_get_dest_rec%rowtype;
200
201 cursor c_get_man_trx_rec (cp_trx_number jai_rgm_manual_trxs.transaction_number%type)
202 is
203 select party_type
204 , party_id
205 , transaction_date
206 , remarks
207 , invoice_number
208 from JAI_RGM_MANUAL_TRXS
209 where transaction_number = cp_trx_number;
210
211 r_man_trx_rec c_get_man_trx_rec%rowtype;
212
213 cursor c_get_vendor_name (cp_vendor_id po_vendors.vendor_id%type)
214 is
215 select vendor_name
216 from po_vendors
217 where vendor_id = cp_vendor_id;
218
219 cursor c_get_customer_name (cp_party_id po_vendors.vendor_id%type)
220 is
221 select hzp.party_name
222 from hz_cust_accounts hzca
223 ,hz_parties hzp
224 where hzca.cust_account_id = cp_party_id
225 and hzp.party_id = hzca.party_id;
226
227 cursor c_st_transprt_inv_details(cp_invoice_id ap_invoices_all.invoice_id%type)/* Changes by nprashar for bug 7172723*/
228 is
229 select
230 aia.invoice_num,
231 substr(aia.invoice_num,instr(aia.invoice_num,'/',1,1)+1,instr(aia.invoice_num,'/',1,2)-instr(aia.invoice_num,'/',1,1)-1) rcp_no,
232 pha.segment1 po_num,
233 aia.invoice_date
234 from
235 po_headers_all pha,
236 rcv_transactions rt,
237 rcv_shipment_headers rsh,
238 ap_invoices_all aia
239 where
240 rsh.receipt_num=substr(aia.invoice_num,instr(aia.invoice_num,'/',1,1)+1,instr(aia.invoice_num,'/',1,2)-instr(aia.invoice_num,'/',1,1)-1) AND
241 rsh.shipment_header_id=rt.shipment_header_id AND
242 rt.po_header_id=pha.po_header_id AND
243 pha.org_id=aia.org_id AND
244 aia.invoice_id=cp_invoice_id
245 and rownum=1;
246
247 cursor c_st_transprt_party_details(cp_invoice_id ap_invoices_all.invoice_id%type) /* Changes by nprashar for bug 7172723*/
248 IS
249 select pv.vendor_name,pv.vendor_id from
250 jai_rgm_trx_refs jrtr,
251 po_vendors pv
252 where invoice_id=cp_invoice_id
253 and pv.vendor_id=jrtr.party_id
254 and rownum=1;
255
256 lv_party_name hz_parties.party_name%type;
257
258 rec_st_transprt_inv_details c_st_transprt_inv_details%rowtype;/* Changes by nprashar , for bug 7172723 */
259 rec_st_transprt_party_details c_st_transprt_party_details%rowtype;/* Changes by nprashar , for bug bug 7172723 */
260 lv_service_type_code jai_rgm_trx_records.service_type_code%type;
261
262 lr_trx_repo_extract jai_trx_repo_extract_gt%rowtype;
263 lv_organization_name hr_organization_units.name%type;
264 lv_location_name hr_locations_all.description%type;
265 ln_reg_id number;
266
267 begin
268
269 lv_member_name := 'EXTRACT_RGM_TRXS';
270 set_debug_context;
271 p_process_flag := jai_constants.SUCCESSFUL;
272 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
273 , pn_reg_id => ln_reg_id
274 );
275
276 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Call Parameters:' || fnd_global.local_chr(10) ||
277 'p_regime_code =' ||p_regime_code || fnd_global.local_chr(10) ||
278 'p_organization_id=' ||p_organization_id || fnd_global.local_chr(10) ||
279 'p_location_id =' ||p_location_id || fnd_global.local_chr(10) ||
280 'p_from_trx_date =' ||p_from_trx_date || fnd_global.local_chr(10) ||
281 'p_to_trx_date =' ||p_to_trx_date || fnd_global.local_chr(10) ||
282 'p_query_settled_flag='||p_query_settled_flag
283 );
284
285 for r_repo_recs in c_get_repo_recs
286 loop
287
288 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Begin for r_repo_recs in c_get_repo_recs loop');
289 lr_trx_repo_extract := null;
290
291 jai_cmn_debug_contexts_pkg.print (ln_reg_id, '1. r_repo_recs.service_type_code ='||r_repo_recs.service_type_code );
292
293 jai_cmn_debug_contexts_pkg.print (ln_reg_id, '2. r_repo_recs.service_type_code ='||r_repo_recs.service_type_code || fnd_global.local_chr(10)
294 ||',r_repo_recs.source='||r_repo_recs.source );
295
296 if r_repo_recs.source in ('AP','AR') then
297
298 jai_trx_repo_extract_pkg.derrive_doc_from_ref
299 ( p_reference_source => r_repo_recs.source
300 , p_reference_invoice_id => r_repo_recs.invoice_id
301 , p_reference_item_line_id => r_repo_recs.item_line_id
302 , p_trx_repo_extract_rec => lr_trx_repo_extract
303 , p_process_message => p_process_message
304 , p_process_flag => p_process_flag
305 );
306 if p_process_flag <> jai_constants.SUCCESSFUL then
307 return;
308 end if;
309
310 /* { Changes by nprashar , For bug 7172723 */
311 if lr_trx_repo_extract.document_id is null then
312 /* if document_id is null here this is a Serv.tax invoice for transporters*/
313
314 open c_st_transprt_inv_details(r_repo_recs.invoice_id);
315 fetch c_st_transprt_inv_details into rec_st_transprt_inv_details;
316 close c_st_transprt_inv_details;
317
318 open c_st_transprt_party_details(r_repo_recs.invoice_id);
319 fetch c_st_transprt_party_details into rec_st_transprt_party_details;
320 close c_st_transprt_party_details;
321
322
323 lr_trx_repo_extract.transaction_source := 'Payables' ;
324 lr_trx_repo_extract.party_name := rec_st_transprt_party_details.vendor_name;
325 lr_trx_repo_extract.party_id := rec_st_transprt_party_details.vendor_id;
326 lr_trx_repo_extract.document_number := rec_st_transprt_inv_details.invoice_num;
327 lr_trx_repo_extract.document_date := rec_st_transprt_inv_details.invoice_date;
328 lr_trx_repo_extract.document_id := r_repo_recs.invoice_id;
329 lr_trx_repo_extract.document_line_desc :='Service Tax - for Transporters -'||'PO-Number: '||rec_st_transprt_inv_details.po_num;
330 lr_trx_repo_extract.organization_id := r_repo_recs.organization_id;
331 lr_trx_repo_extract.location_id := r_repo_recs.location_id;
332 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt;
333
334 end if;
335 /* Changes by nprashar , For bug bug 7172723 }*/
336
337 elsif r_repo_recs.source IN ('SERVICE_DISTRIBUTE_OUT') then
338
339 lr_trx_repo_extract.document_line_desc := 'Service Distribution Transaction' ;
340 lr_trx_repo_extract.transaction_source := r_repo_recs.source ;
341
342
343 if r_repo_recs.source = 'SERVICE_DISTRIBUTE_OUT' then
344 open c_get_src_rec ( cp_transfer_id => r_repo_recs.source_document_id
345 , cp_party_type => r_repo_recs.organization_type
346 , cp_party_id => r_repo_recs.organization_id
347 );
348 fetch c_get_src_rec into r_src_rec;
349 close c_get_src_rec ;
350
351 lr_trx_repo_extract.document_number := r_src_rec.transfer_number;
352 lr_trx_repo_extract.document_date := r_src_rec.transaction_date;
353 lr_trx_repo_extract.document_id := r_repo_recs.source_document_id;
354 lr_trx_repo_extract.organization_id := r_src_rec.party_id ;
355 lr_trx_repo_extract.location_id := r_src_rec.location_id ;
356 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt ;
357
358 end if; --> r_repo_recs.source = 'SERVICE_DISTRIBUTE_OUT'
359
360 elsif r_repo_recs.source = 'MANUAL' then
361
362 open c_get_man_trx_rec (cp_trx_number => r_repo_recs.source_document_id);
363 fetch c_get_man_trx_rec into r_man_trx_rec ;
364 close c_get_man_trx_rec ;
365
366 if r_man_trx_rec.party_type in ('VENDOR','AUTHORITY') then
367 open c_get_vendor_name (cp_vendor_id => r_man_trx_rec.party_id);
368 fetch c_get_vendor_name into lv_party_name;
369 close c_get_vendor_name ;
370 elsif r_man_trx_rec.party_type = 'CUSTOMER' then
371 open c_get_customer_name (cp_party_id => r_man_trx_rec.party_id);
372 fetch c_get_customer_name into lv_party_name;
373 close c_get_customer_name ;
374 end if;
375
376 lr_trx_repo_extract.transaction_source := r_repo_recs.source ;
377 lr_trx_repo_extract.party_name := lv_party_name;
378 lr_trx_repo_extract.document_number := r_repo_recs.source_document_id;
379 lr_trx_repo_extract.document_date := r_man_trx_rec.transaction_date;
380 lr_trx_repo_extract.document_id := r_repo_recs.source_document_id;
381 lr_trx_repo_extract.document_line_desc := nvl(r_man_trx_rec.remarks , 'Service Tax - Manual Transaction')
382 || rtrim('/'||r_man_trx_rec.invoice_number,'/');
383 lr_trx_repo_extract.organization_id := r_repo_recs.organization_id;
384 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt;
385
386 end if; --> r_repo_recs.source
387
388 lr_trx_repo_extract.transaction_repository_id := r_repo_recs.repository_id ;
389 lr_trx_repo_extract.transaction_reference_id := r_repo_recs.reference_id ;
390 lr_trx_repo_extract.repository_source := r_repo_recs.source ;
391 lr_trx_repo_extract.repository_invoice_id := r_repo_recs.invoice_id ;
392 lr_trx_repo_extract.repository_line_id := r_repo_recs.item_line_id ;
393 lr_trx_repo_extract.service_type_code := r_repo_recs.service_type_code ;
394
395 jai_cmn_debug_contexts_pkg.print
396 (ln_reg_id
397 , 'Before insert into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
398 'lr_trx_repo_extract.transaction_repository_id ='|| lr_trx_repo_extract.transaction_repository_id || fnd_global.local_chr(10) ||
399 'lr_trx_repo_extract.transaction_reference_id ='|| lr_trx_repo_extract.transaction_reference_id || fnd_global.local_chr(10) ||
400 'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
401 'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
402 'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
403 'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
404 'lr_trx_repo_extract.document_id ='|| lr_trx_repo_extract.document_id || fnd_global.local_chr(10) ||
405 'lr_trx_repo_extract.document_line_id ='|| lr_trx_repo_extract.document_line_id || fnd_global.local_chr(10) ||
406 'lr_trx_repo_extract.document_line_num ='|| lr_trx_repo_extract.document_line_num || fnd_global.local_chr(10) ||
407 'lr_trx_repo_extract.document_line_item ='|| lr_trx_repo_extract.document_line_item || fnd_global.local_chr(10) ||
408 'lr_trx_repo_extract.document_line_desc ='|| lr_trx_repo_extract.document_line_desc
409 );
410 jai_cmn_debug_contexts_pkg.print
411 ( ln_reg_id
412 ,'lr_trx_repo_extract.document_line_qty ='|| lr_trx_repo_extract.document_line_qty || fnd_global.local_chr(10) ||
413 'lr_trx_repo_extract.document_line_uom ='|| lr_trx_repo_extract.document_line_uom || fnd_global.local_chr(10) ||
414 'lr_trx_repo_extract.document_line_amt ='|| lr_trx_repo_extract.document_line_amt || fnd_global.local_chr(10) ||
415 'lr_trx_repo_extract.document_currency_code ='|| lr_trx_repo_extract.document_currency_code || fnd_global.local_chr(10) ||
416 'lr_trx_repo_extract.repository_tax_amt ='|| lr_trx_repo_extract.repository_tax_amt || fnd_global.local_chr(10) ||
417 'lr_trx_repo_extract.organization_name ='|| lr_trx_repo_extract.organization_name || fnd_global.local_chr(10) ||
418 'lr_trx_repo_extract.location_name ='|| lr_trx_repo_extract.location_name || fnd_global.local_chr(10) ||
419 'lr_trx_repo_extract.organization_id ='|| lr_trx_repo_extract.organization_id || fnd_global.local_chr(10) ||
420 'lr_trx_repo_extract.location_id ='|| lr_trx_repo_extract.location_id || fnd_global.local_chr(10) ||
421 'lr_trx_repo_extract.inventory_item_id ='|| lr_trx_repo_extract.inventory_item_id || fnd_global.local_chr(10) ||
422 'lr_trx_repo_extract.party_id ='|| lr_trx_repo_extract.party_id || fnd_global.local_chr(10) ||
423 'lr_trx_repo_extract.service_type_code ='|| lr_trx_repo_extract.service_type_code
424 );
425
426 insert into jai_trx_repo_extract_gt
427 (
428 transaction_repository_id
429 ,transaction_reference_id
430 ,transaction_source
431 ,party_name
432 ,document_number
433 ,document_date
434 ,document_id
435 ,document_line_id
436 ,document_line_num
437 ,document_line_item
438 ,document_line_desc
439 ,document_line_qty
440 ,document_line_uom
441 ,document_line_amt
442 ,document_currency_code
443 ,repository_tax_amt
444 ,organization_name
445 ,location_name
446 ,organization_id
447 ,location_id
448 ,inventory_item_id
449 ,party_id
450 ,service_type_code
451 ,repository_invoice_id
452 ,repository_line_id
453 ,repository_source
454 ,processed_flag
455 )
456 values
457 (
458 lr_trx_repo_extract.transaction_repository_id
459 ,lr_trx_repo_extract.transaction_reference_id
460 ,lr_trx_repo_extract.transaction_source
461 ,lr_trx_repo_extract.party_name
462 ,lr_trx_repo_extract.document_number
463 ,lr_trx_repo_extract.document_date
464 ,lr_trx_repo_extract.document_id
465 ,lr_trx_repo_extract.document_line_id
466 ,lr_trx_repo_extract.document_line_num
467 ,lr_trx_repo_extract.document_line_item
468 ,lr_trx_repo_extract.document_line_desc
469 ,lr_trx_repo_extract.document_line_qty
470 ,lr_trx_repo_extract.document_line_uom
471 ,lr_trx_repo_extract.document_line_amt
472 ,lr_trx_repo_extract.document_currency_code
473 ,lr_trx_repo_extract.repository_tax_amt
474 ,lr_trx_repo_extract.organization_name
475 ,lr_trx_repo_extract.location_name
476 ,lr_trx_repo_extract.organization_id
477 ,lr_trx_repo_extract.location_id
478 ,lr_trx_repo_extract.inventory_item_id
479 ,lr_trx_repo_extract.party_id
480 ,lr_trx_repo_extract.service_type_code
481 ,lr_trx_repo_extract.repository_invoice_id
482 ,lr_trx_repo_extract.repository_line_id
483 ,lr_trx_repo_extract.repository_source
484 ,null
485 );
486 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After insert into jai_trx_repo_extract_gt');
487 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'End of Loop -> for r_repo_recs in c_get_repo_recs');
488
489 end loop; --> r_repo_recs in c_get_repo_recs
490
491
492 --
493 -- Fetch distinct documents from the global temporary table populated above and get document details for each distinct document
494 -- and update temp table with document details
495 --
496 for r_docs in (select distinct transaction_source
497 , document_id
498 , document_line_id
499 from jai_trx_repo_extract_gt gt
500 where gt.repository_source in ('AP','AR')
501 )
502 loop
503
504 lr_trx_repo_extract := null;
505
506 if r_docs.transaction_source = 'ORDER MANAGEMENT' then
507
508 -- Only line_id it self is primary key for oe_order_lines_all so only line_id will do the job
509 jai_trx_repo_extract_pkg.get_document_details
510 ( p_document_id => ''
511 , p_document_line_id => r_docs.document_line_id
512 , p_document_source => r_docs.transaction_source
513 , p_called_from => 'JAINRPRW'
514 , p_process_message => p_process_message
515 , p_process_flag => p_process_flag
516 , p_trx_repo_extract => lr_trx_repo_extract
517 );
518
519 if p_process_flag <> jai_constants.SUCCESSFUL then
520 return;
521 end if;
522 /*added by csahoo for bug#6457710,start*/
523 elsif r_docs.transaction_source = 'PROJECTS' then
524
525 jai_trx_repo_extract_pkg.get_document_details
526 ( p_document_id => r_docs.document_id
527 , p_document_line_id => r_docs.document_line_id
528 , p_document_source => r_docs.transaction_source
529 , p_called_from => 'JAINRPRW'
530 , p_process_message => p_process_message
531 , p_process_flag => p_process_flag
532 , p_trx_repo_extract => lr_trx_repo_extract
533 );
534
535 if p_process_flag <> jai_constants.SUCCESSFUL then
536 return;
537 end if;
538 /*bug#6457710,end*/
539
540 elsif r_docs.transaction_source = 'RECEIVABLES' then
541
542 jai_trx_repo_extract_pkg.get_document_details
543 ( p_document_id => r_docs.document_id
544 , p_document_line_id => r_docs.document_line_id
545 , p_document_source => r_docs.transaction_source
546 , p_called_from => 'JAINRPRW'
547 , p_process_message => p_process_message
548 , p_process_flag => p_process_flag
549 , p_trx_repo_extract => lr_trx_repo_extract
550 );
551
552 if p_process_flag <> jai_constants.SUCCESSFUL then
553 return;
554 end if;
555
556 elsif r_docs.transaction_source = 'PURCHASING' then
557
558 jai_trx_repo_extract_pkg.get_document_details
559 ( p_document_id => r_docs.document_id
560 , p_document_line_id => r_docs.document_line_id
561 , p_document_source => r_docs.transaction_source
562 , p_called_from => 'JAINRPRW'
563 , p_process_message => p_process_message
564 , p_process_flag => p_process_flag
565 , p_trx_repo_extract => lr_trx_repo_extract
566 );
567
568 --Elsif added for Bug#6843479
569 elsif r_docs.transaction_source = 'RECEIVING' then
570
571 jai_trx_repo_extract_pkg.get_document_details
572 ( p_document_id => r_docs.document_id
573 , p_document_line_id => r_docs.document_line_id
574 , p_document_source => r_docs.transaction_source
575 , p_called_from => 'JAINRPRW'
576 , p_process_message => p_process_message
577 , p_process_flag => p_process_flag
578 , p_trx_repo_extract => lr_trx_repo_extract
579 );
580
581 --Till Here Bug#6843479
582 --Elsif added for Bug#6977917 on 2008/04/08
583 elsif r_docs.transaction_source = jai_constants.G_AP_STANDALONE_INVOICE then
584 jai_trx_repo_extract_pkg.get_document_details
585 ( p_document_id => r_docs.document_id
586 , p_document_line_id => r_docs.document_line_id
587 , p_document_source => r_docs.transaction_source
588 , p_called_from => 'JAINRPRW'
589 , p_process_message => p_process_message
590 , p_process_flag => p_process_flag
591 , p_trx_repo_extract => lr_trx_repo_extract
592 );
593 --Till Here Bug#6977917
594 end if;
595
596 --
597 -- For each document line check if repository has a settled record with a service type attached. If yes, then get the service type
598 -- of the settled line and default it to current document line and mark the record as non-updatable
599 --
600
601 lv_service_type_code := get_settled_service_type
602 ( p_transaction_source => r_docs.transaction_source
603 , p_document_id => lr_trx_repo_extract.document_id
604 , p_document_line_id => lr_trx_repo_extract.document_line_id
605 );
606 if lv_service_type_code is not null then
607
608 lr_trx_repo_extract.service_type_code := lv_service_type_code;
609 lr_trx_repo_extract.updatable_flag := jai_constants.NO;
610 lr_trx_repo_extract.processed_flag := jai_constants.NO;
611
612 end if;
613
614 lr_trx_repo_extract.document_line_id := lr_trx_repo_extract.document_line_num; --Added by Lion for bug#6911533
615 jai_cmn_debug_contexts_pkg.print
616 (ln_reg_id
617 , 'Before update into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
618 'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
619 'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
620 'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
621 'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
622 'lr_trx_repo_extract.document_id ='|| lr_trx_repo_extract.document_id || fnd_global.local_chr(10) ||
623 'lr_trx_repo_extract.document_line_id ='|| lr_trx_repo_extract.document_line_id || fnd_global.local_chr(10) ||
624 'lr_trx_repo_extract.document_line_num ='|| lr_trx_repo_extract.document_line_num || fnd_global.local_chr(10) ||
625 'lr_trx_repo_extract.document_line_item ='|| lr_trx_repo_extract.document_line_item || fnd_global.local_chr(10) ||
626 'lr_trx_repo_extract.document_line_desc ='|| lr_trx_repo_extract.document_line_desc
627 );
628 jai_cmn_debug_contexts_pkg.print
629 ( ln_reg_id
630 ,'lr_trx_repo_extract.document_line_qty ='|| lr_trx_repo_extract.document_line_qty || fnd_global.local_chr(10) ||
631 'lr_trx_repo_extract.document_line_uom ='|| lr_trx_repo_extract.document_line_uom || fnd_global.local_chr(10) ||
632 'lr_trx_repo_extract.document_line_amt ='|| lr_trx_repo_extract.document_line_amt || fnd_global.local_chr(10) ||
633 'lr_trx_repo_extract.document_currency_code ='|| lr_trx_repo_extract.document_currency_code || fnd_global.local_chr(10) ||
634 'lr_trx_repo_extract.repository_tax_amt ='|| lr_trx_repo_extract.repository_tax_amt || fnd_global.local_chr(10) ||
635 'lr_trx_repo_extract.organization_name ='|| lr_trx_repo_extract.organization_name || fnd_global.local_chr(10) ||
636 'lr_trx_repo_extract.location_name ='|| lr_trx_repo_extract.location_name || fnd_global.local_chr(10) ||
637 'lr_trx_repo_extract.organization_id ='|| lr_trx_repo_extract.organization_id || fnd_global.local_chr(10) ||
638 'lr_trx_repo_extract.location_id ='|| lr_trx_repo_extract.location_id || fnd_global.local_chr(10) ||
639 'lr_trx_repo_extract.inventory_item_id ='|| lr_trx_repo_extract.inventory_item_id || fnd_global.local_chr(10) ||
640 'lr_trx_repo_extract.party_id ='|| lr_trx_repo_extract.party_id || fnd_global.local_chr(10) ||
641 'lr_trx_repo_extract.service_type_code ='|| lr_trx_repo_extract.service_type_code || fnd_global.local_chr(10) ||
642 'lr_trx_repo_extract.updatable_flag ='|| lr_trx_repo_extract.updatable_flag || fnd_global.local_chr(10) ||
643 'lr_trx_repo_extract.processed_flag ='|| lr_trx_repo_extract.processed_flag
644 );
645
646 update jai_trx_repo_extract_gt
647 set transaction_source = lr_trx_repo_extract.transaction_source
648 , party_name = lr_trx_repo_extract.party_name
649 , document_number = lr_trx_repo_extract.document_number
650 , document_date = lr_trx_repo_extract.document_date
651 , document_id = lr_trx_repo_extract.document_id
652 , document_line_id = lr_trx_repo_extract.document_line_id
653 , document_line_num = lr_trx_repo_extract.document_line_num
654 , document_line_item = lr_trx_repo_extract.document_line_item
655 , document_line_desc = lr_trx_repo_extract.document_line_desc
656 , document_line_qty = lr_trx_repo_extract.document_line_qty
657 , document_line_uom = lr_trx_repo_extract.document_line_uom
658 , document_line_amt = lr_trx_repo_extract.document_line_amt
659 , repository_tax_amt = lr_trx_repo_extract.repository_tax_amt
660 , document_currency_code = lr_trx_repo_extract.document_currency_code
661 , inventory_item_id = lr_trx_repo_extract.inventory_item_id
662 , party_id = lr_trx_repo_extract.party_id
663 , organization_id = nvl(lr_trx_repo_extract.organization_id, organization_id)
664 , location_id = nvl(lr_trx_repo_extract.location_id,location_id)
665 , service_type_code = nvl(lr_trx_repo_extract.service_type_code, service_type_code)
666 , updatable_flag = lr_trx_repo_extract.updatable_flag
667 , processed_flag = lr_trx_repo_extract.processed_flag
668 where transaction_source = r_docs.transaction_source
669 and ( (r_docs.document_id is not null and document_id = r_docs.document_id)
670 or r_docs.document_id is null -- incase of order management it will be null
671 )
672 and document_line_id = r_docs.document_line_id;
673
674 jai_cmn_debug_contexts_pkg.print
675 ( ln_reg_id
676 , 'Number of rows updated ='||sql%rowcount
677 );
678 end loop;
679
680 --
681 -- Get organization name for each distinct organization
682 --
683
684 for r_org in (select distinct organization_id from jai_trx_repo_extract_gt where organization_id is not null)
685 loop
686
687 jai_cmn_debug_contexts_pkg.print
688 (ln_reg_id
689 , 'OPEN/FETCH/CLOSE c_get_organization_name, r_org.organization_id='||r_org.organization_id
690 );
691
692 open c_get_organization_name (cp_organization_id => r_org.organization_id);
693 fetch c_get_organization_name into lv_organization_name;
694 close c_get_organization_name ;
695
696 jai_cmn_debug_contexts_pkg.print
697 (ln_reg_id
698 ,'lv_organization_name='||lv_organization_name
699 );
700
701
702 update jai_trx_repo_extract_gt
703 set organization_name = lv_organization_name
704 where organization_id = r_org.organization_id;
705
706 end loop;
707
708 --
709 -- Get location name for each distinct location
710 --
711
712 for r_loc in (select distinct location_id from jai_trx_repo_extract_gt where location_id is not null )
713 loop
714 jai_cmn_debug_contexts_pkg.print
715 (ln_reg_id
716 ,'OPEN/FETCH/CLOSE c_get_location_name, r_loc.location_id='||r_loc.location_id
717 );
718
719 open c_get_location_name (cp_location_id => r_loc.location_id);
720 fetch c_get_location_name into lv_location_name;
721 close c_get_location_name ;
722
723 jai_cmn_debug_contexts_pkg.print
724 (ln_reg_id
725 ,'lv_location_name='||lv_location_name
726 );
727
728 update jai_trx_repo_extract_gt
729 set location_name = lv_location_name
730 where location_id = r_loc.location_id;
731
732 end loop;
733
734 /** Deregister procedure and return*/
735 <<deregister_and_return>>
736 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
737
738 exception
739 when others then
740 p_process_flag := jai_constants.unexpected_error;
741 p_process_message := lv_context||'->'||sqlerrm;
742 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
743 jai_cmn_debug_contexts_pkg.print_stack;
744 end extract_rgm_trxs;
745
746 /*------------------------------------------------------------------------------------------------------------*/
747
748
749 procedure get_document_details
750 (
751 p_document_id in number
752 , p_document_line_id in number
753 , p_document_source in varchar2
754 , p_called_from in varchar2 default null
755 , p_process_message out nocopy varchar2
756 , p_process_flag out nocopy varchar2
757 , p_trx_repo_extract in out nocopy jai_trx_repo_extract_gt%rowtype
758 )
759 as
760 ln_reg_id number;
761
762
763 /*Added parameters cp_header_id and cp_line_id in c_get_po_details by vkantamn for Bug #6083978*/
764 cursor c_get_po_details(cp_header_id number, cp_line_id number)
765 is
766 select pov.vendor_name party_name
767 ,poh.segment1 document_number
768 ,poh.creation_date document_date
769 ,poh.po_header_id document_id
770 ,pol.po_line_id document_line_id
771 ,pol.line_num document_line_num
772 ,msi.segment1 document_line_item
773 ,pol.item_description document_line_desc
774 ,pol.quantity document_line_qty
775 ,pol.unit_meas_lookup_code document_line_uom
776 ,(pol.unit_price * pol.quantity) document_line_amt
777 ,poh.currency_code document_currency_code
778 ,pol.item_id inventory_item_id
779 ,poh.vendor_id party_id
780 -- ,fsp.inventory_organization_id organization_id /* Commented by vkantamn for Bug#6083978 */
781 ,hl.inventory_organization_id organization_id /* Added by vkantamn for Bug#6083978 */
782 ,poll.ship_to_location_id location_id
783 from po_headers_all poh
784 , po_lines_all pol
785 , po_line_locations_all poll /*6843479*/
786 , mtl_system_items msi
787 , po_vendors pov
788 , hr_locations hl /* Added by vkantamn for Bug#6083978 */
789 -- , financials_system_parameters fsp /* Commented by vkantamn for Bug#6083978 */
790 where
791 --poh.po_header_id = p_document_id /* Commented by vkantamn for Bug#6083978 */
792 poh.po_header_id = cp_header_id /* Added by vkantamn for Bug#6083978 */
793 and pol.po_header_id = poh.po_header_id
794 -- and pol.po_line_id = p_document_line_id /* Commented by vkantamn for Bug#6083978 */
795 and pol.po_line_id = cp_line_id /* Added by vkantamn for Bug#6083978 */
796 and pol.po_line_id = poll.po_line_id
797 and poll.po_header_id = poh.po_header_id
798 and pol.item_id = msi.inventory_item_id (+)
799 --and nvl(msi.organization_id ,fsp.inventory_organization_id )= fsp.inventory_organization_id /* Commented by vkantamn for Bug#6026463 */
800 and nvl(poll.ship_to_location_id,poh.ship_to_location_id )= hl.location_id
801 /*Commented by nprashar for bug # 6636517 poh.ship_to_location_id = hl.location_id --Added by vkantamn for Bug#6083978 */
802 and pov.vendor_id = poh.vendor_id ;
803
804 cursor c_get_so_details
805 is
806 select hzp.party_name party_name
807 , oeh.order_number document_number
808 , oeh.ordered_date document_date
809 , oeh.header_id document_id
810 , oel.line_id document_line_id
811 , oel.line_number document_line_num
812 , msi.segment1 document_line_item
813 , substr(oel.user_item_description,1,240) document_line_desc
814 , oel.ordered_quantity document_line_qty
815 , oel.order_quantity_uom document_line_uom
816 , nvl(oel.unit_selling_price * oel.ordered_quantity,0) document_line_amt
817 , oeh.transactional_curr_code document_currency_code
818 , oel.inventory_item_id inventory_item_id
819 , oeh.sold_to_org_id party_id
820 , oel.ship_from_org_id organization_id
821 , oel.line_category_code line_category_code
822 from
823 oe_order_headers_all oeh
824 ,oe_order_lines_all oel
825 ,hz_parties hzp
826 ,hz_cust_accounts hzca
827 ,mtl_system_items msi
828 where (p_document_id is null or p_document_id = '' or oeh.header_id = p_document_id)
829 and oel.header_id = oeh.header_id
830 and oel.line_id = p_document_line_id
831 and oel.inventory_item_id = msi.inventory_item_id
832 and oel.ship_from_org_id = msi.organization_id
833 and hzca.cust_account_id = oel.sold_to_org_id
834 and hzca.party_id = hzp.party_id ;
835 -- Added by Bill Shi for standalone invoice on 2007/09/27
836 --------------------------------------------------------------------------
837 CURSOR l_get_si_details_csr IS
838 SELECT
839 pov.vendor_name party_name
840 , apa.invoice_num document_number
841 , apa.creation_date document_date
842 , apa.invoice_id document_id
843 , NULL document_line_id
844 , apla.line_number document_line_num
845 , NULL document_line_item
846 , NULL document_line_desc
847 , NULL document_line_qty
848 , NULL document_line_uom
849 , apla.amount document_line_amt
850 , jasl.currency_code document_currency_code
851 , NULL inventory_item_id
852 , apa.vendor_id party_id
853 , jasl.organization_id organization_id
854 , jasl.location_id location_id
855 FROM
856 ap_invoices_all apa
857 , ap_invoice_lines_all apla
858 , po_vendors pov
859 , jai_ap_invoice_lines jasl
860 WHERE apa.invoice_id = p_document_id
861 AND apa.invoice_id = apla.invoice_id
862 AND apla.line_number = p_document_line_id
863 AND jasl.invoice_id = apa.invoice_id
864 AND jasl.invoice_line_number = apla.line_number
865 AND pov.vendor_id = apa.vendor_id;
866 --------------------------------------------------------------------------
867
868 cursor c_get_ra_trx_details
869 is
870 select hzp.party_name
871 ,rct.trx_number document_number
872 ,rct.trx_date document_date
873 ,rct.customer_trx_id document_id
874 ,rctl.customer_trx_line_id document_line_id
875 ,rctl.line_number document_line_num
876 ,msi.segment1 document_line_item
877 ,rctl.description document_line_desc
878 ,rctl.quantity_invoiced document_line_qty
879 ,rctl.uom_code document_line_uom
880 ,rctl.extended_amount document_line_amt
881 ,rct.invoice_currency_code document_currency_code
882 ,rctl.inventory_item_id inventory_item_id
883 ,nvl(rct.sold_to_customer_id, rct.bill_to_customer_id) party_id
884 ,jrct.organization_id organization_id
885 ,jrct.location_id location_id
886 from ra_customer_trx_all rct
887 ,ra_customer_trx_lines_all rctl
888 ,jai_ar_trxs jrct
889 ,hz_parties hzp
890 ,hz_cust_accounts hzca
891 ,mtl_system_items msi
892 where rct.customer_trx_id = p_document_id
893 and jrct.customer_trx_id = rct.customer_trx_id
894 and rctl.customer_trx_id = rct.customer_trx_id
895 and rctl.customer_trx_line_id = p_document_line_id
896 and rctl.inventory_item_id = msi.inventory_item_id (+)
897 and nvl(msi.organization_id,jrct.organization_id) = jrct.organization_id
898 and hzca.cust_account_id = nvl(rct.sold_to_customer_id, rct.bill_to_customer_id)
899 and hzca.party_id = hzp.party_id;
900
901 -- Begin 5876390, 6012570
902 /*modified the below cusrsor query to select from the tables PA_DRAFT_INVOICES_ALL,
903 PA_PROJECTS_ALL instead of pa_draft_invoices_v.*/
904 cursor c_get_pa_details
905 is
906 select c.customer_name party_name,
907 p.segment1
908 ||'/'
909 ||padi.draft_invoice_num
910 document_number
911 , padi.creation_date document_date
912 , jpadi.draft_invoice_id document_id
913 , jpadil.draft_invoice_line_id document_line_id
914 , jpadil.line_num document_line_num
915 , null document_line_item
916 , substr(padil.text,1,240) document_line_desc
917 , null document_line_qty
918 , null document_line_uom
919 , jpadil.line_amt document_line_amt
920 , padi.inv_currency_code document_currency_code
921 , null inventory_item_id
922 , padi.ship_to_customer_id party_id
923 , jpadi.organization_id organization_id
924 , jpadi.location_id location_id
925 , jpadil.service_type_code service_type_code
926 from
927 PA_DRAFT_INVOICES_ALL padi,
928 PA_PROJECTS_ALL p
929 ,pa_draft_invoice_items padil
930 ,jai_pa_draft_invoices jpadi
931 ,jai_pa_draft_invoice_lines jpadil
932 ,PA_CUSTOMERS_V c
933 where jpadi.draft_invoice_id = p_document_id
934 and jpadil.draft_invoice_line_id = p_document_line_id
935 and jpadi.draft_invoice_id = jpadil.draft_invoice_id
936 and jpadi.project_id = padi.project_id
937 and jpadi.draft_invoice_num = padi.draft_invoice_num
938 and p.project_id=padi.project_id
939 and padi.ship_to_customer_id=c.customer_id;
940 -- End 5876390, 6012570
941
942
943 cursor c_get_po_line_loc_srvtyp (cp_po_line_id po_lines_all.po_line_id%type )
944 is
945 select service_type_code, sum(jpollt.tax_amount) service_tax_amount
946 from JAI_PO_LINE_LOCATIONS jpoll
947 ,jai_po_taxes jpollt
948 where jpoll.po_line_id = cp_po_line_id
949 and jpollt.line_location_id = jpoll.line_location_id
950 /*added the cess and sh cess tax types for bug#6457710*/
951 and jpollt.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
952 --and jpollt.tax_type = 'Service'
953 group by service_type_code;
954
955 cursor c_get_ra_line_srvtyp (cp_customer_trx_line_id jai_ar_trx_lines.customer_trx_line_id%type)
956 is
957 select service_type_code, sum(jrcttl.tax_amount) service_tax_amount
958 from JAI_AR_TRX_LINES jrctl
959 ,JAI_AR_TRX_TAX_LINES jrcttl
960 ,jai_cmn_taxes_all jtc
961 where jrctl.customer_trx_line_id = cp_customer_trx_line_id
962 and jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
963 and jtc.tax_id = jrcttl.tax_id
964 /*added the cess and sh cess tax types for bug#6457710*/
965 and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
966 --and jtc.tax_type = 'Service'
967 group by service_type_code;
968
969 cursor c_get_so_line_srvtyp (cp_line_id JAI_OM_OE_SO_LINES.line_id%type ) is
970 select service_type_code, sum(jstl.tax_amount) service_tax_amount
971 from JAI_OM_OE_SO_LINES jsl
972 , JAI_OM_OE_SO_TAXES jstl
973 , jai_cmn_taxes_all jtc
974 where jsl.line_id = cp_line_id
975 and jsl.line_id = jstl.line_id
976 and jstl.tax_id = jtc.tax_id
977 /*added the cess and sh cess tax types for bug#6457710*/
978 and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
979 --and jtc.tax_type = 'Service'
980 group by service_type_code;
981
982 /*The following cursor added by rchandan for RMA */
983
984 cursor c_get_rma_line_srvtyp (cp_line_id jai_om_oe_so_lines.line_id%type )
985 is
986 select service_type_code, sum(jrtl.tax_amount) service_tax_amount
987 from JAI_OM_OE_RMA_LINES jrl
988 , JAI_OM_OE_RMA_TAXES jrtl
989 , JAI_CMN_TAXES_ALL jtc
990 where jrl.rma_line_id = cp_line_id
991 and jrl.rma_line_id = jrtl.rma_line_id
992 and jrtl.tax_id = jtc.tax_id
993 /*added the cess and sh cess tax types for bug#6457710*/
994 and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
995 --and jtc.tax_type = 'Service'
996 group by service_type_code;
997
998 -- Bug 5876390, 6012570
999 cursor c_get_pa_inv_line_tax (cp_line_id jai_cmn_document_taxes.source_doc_line_id%type )
1000 is
1001 select sum(tax_amt) service_tax_amount
1002 from jai_cmn_document_taxes jcdt
1003 , jai_cmn_taxes_all jtc
1004 where jcdt.source_doc_line_id = cp_line_id
1005 and jcdt.source_doc_type = jai_constants.PA_DRAFT_INVOICE
1006 and jcdt.tax_id = jtc.tax_id
1007 /*added the cess and sh cess tax types for bug#6457710*/
1008 and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
1009 --and jtc.tax_type = 'Service';
1010
1011 --Deleted by Lion for bug#6911533 on 2008/04/22
1012 /*
1013 -- Added by Bill Shi for standalone invoice on 2007/09/27
1014 -- Get the service tax amount in AP standalone invoice
1015 --------------------------------------------------------------------------
1016 CURSOR l_get_si_tax_amt_csr
1017 ( lp_line_id jai_cmn_document_taxes.source_doc_line_id%TYPE) IS
1018 SELECT
1019 jasl.service_type_code
1020 , SUM (jcdt.tax_amt)
1021 FROM
1022 jai_ap_invoice_lines jasl
1023 , jai_cmn_document_taxes jcdt
1024 , jai_cmn_taxes_all jcta
1025 WHERE jasl.invoice_id = jcdt.source_doc_id
1026 AND jasl.invoice_line_number = jcdt.source_doc_line_id
1027 AND jcdt.source_doc_line_id = lp_line_id
1028 AND jcdt.source_doc_id = p_document_id
1029 AND jcta.tax_id = jcdt.tax_id
1030 AND jcta.tax_type = jai_constants.tax_type_service
1031 GROUP BY jasl.service_type_code;
1032 --------------------------------------------------------------------------
1033 */
1034
1035 --Added by Lion for bug#6911533 on 2008/04/22
1036 --------------------------------------------------------------------------
1037 CURSOR l_get_si_tax_amt_csr
1038 ( lp_line_id jai_cmn_document_taxes.source_doc_line_id%TYPE)
1039 IS
1040 SELECT
1041 SUM (jcdt.tax_amt)
1042 FROM
1043 jai_ap_invoice_lines jasl
1044 , jai_cmn_document_taxes jcdt
1045 , jai_cmn_taxes_all jcta
1046 WHERE jasl.invoice_id = p_document_id
1047 AND jasl.parent_invoice_line_number = lp_line_id
1048 AND jasl.invoice_id = jcdt.source_doc_id
1049 AND jasl.invoice_line_number = jcdt.source_doc_line_id
1050 AND jcdt.tax_id =jcta.tax_id
1051 AND jcta.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
1052
1053 GROUP BY jasl.service_type_code;
1054
1055 CURSOR l_get_si_service_type_csr
1056 ( lp_line_id jai_cmn_document_taxes.source_doc_line_id%TYPE) IS
1057 SELECT
1058 jasl.service_type_code
1059 FROM
1060 jai_ap_invoice_lines jasl
1061 WHERE invoice_id = p_document_id
1062 AND invoice_line_number = lp_line_id
1063 ;
1064 --------------------------------------------------------------------------
1065
1066 /*Started addition by vkantamn for Bug#6083978*/
1067 cursor c_get_rcv_details
1068 is
1069 select rcv.organization_id organization_id
1070 ,rcv.location_id location_id
1071 ,rsl.po_header_id po_header_id
1072 ,rsl.po_line_id po_line_id
1073 from jai_rcv_transactions rcv,
1074 rcv_shipment_lines rsl
1075 where
1076 rcv.shipment_header_id = rsl.shipment_header_id
1077 and rcv.shipment_line_id = rsl.shipment_line_id
1078 and rcv.shipment_header_id = p_document_id
1079 and rcv.shipment_line_id = p_document_line_id
1080 and rcv.transaction_type = 'RECEIVE';
1081
1082 v_organization_id number;
1083 v_location_id number;
1084 v_po_header_id number;
1085 v_po_line_id number;
1086
1087 /*End of addition by vkantamn for Bug#6083978*/
1088
1089 r_po_details c_get_po_details%rowtype;
1090 r_so_details c_get_so_details%rowtype;
1091 r_ra_trx_details c_get_ra_trx_details%rowtype;
1092 r_pa_details c_get_pa_details%rowtype; -- Bug 5876390, 6012570
1093
1094 -- Add by Bill Shi for standalone ap invoice on 2007/9/27
1095 --------------------------------------------------------------------------
1096 l_si_details_rec l_get_si_details_csr%ROWTYPE;
1097 --------------------------------------------------------------------------
1098
1099 lv_service_type jai_rgm_trx_records.service_type_code%type;
1100
1101 begin
1102
1103 lv_member_name := 'GET_DOCUMENT_DETAILS';
1104 set_debug_context;
1105 p_process_flag := jai_constants.SUCCESSFUL;
1106 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
1107 , pn_reg_id => ln_reg_id
1108 );
1109
1110
1111 jai_cmn_debug_contexts_pkg.print ( ln_reg_id
1112 ,'Call Parameters:' ||fnd_global.local_chr(10)
1113 ||'p_document_id =' ||p_document_id ||fnd_global.local_chr(10)
1114 ||'p_document_line_id =' ||p_document_line_id ||fnd_global.local_chr(10)
1115 ||'p_document_source =' ||p_document_source ||fnd_global.local_chr(10)
1116 );
1117
1118 if p_document_line_id is null or p_document_source is null then
1119 p_process_message := 'Document references cannot be null, cannot continue to derive the document details';
1120 p_process_flag := jai_constants.EXPECTED_ERROR;
1121 return;
1122 end if;
1123
1124 --
1125 -- Check for source and based on the source deligate control to respective procedure to fetch the details
1126 --
1127 if p_document_source = 'PURCHASING' then
1128 --open c_get_po_details;
1129 /*Added parameters to cursor by vkantamn for Bug#6083978 */
1130 open c_get_po_details(p_document_id,p_document_line_id);
1131 fetch c_get_po_details into r_po_details;
1132 close c_get_po_details;
1133
1134 if r_po_details.document_id is null then
1135 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1136 ,'Purchase Order does not exists for po_header_id ='||p_document_id ||' and po_line_id='|| p_document_line_id
1137 );
1138 end if;
1139
1140 p_trx_repo_extract.transaction_source := 'PURCHASING' ;
1141 p_trx_repo_extract.party_name := r_po_details.party_name ;
1142 p_trx_repo_extract.document_number := r_po_details.document_number ;
1143 p_trx_repo_extract.document_date := r_po_details.document_date ;
1144 p_trx_repo_extract.document_id := r_po_details.document_id ;
1145 p_trx_repo_extract.document_line_id := r_po_details.document_line_id ;
1146 p_trx_repo_extract.document_line_num := r_po_details.document_line_num ;
1147 p_trx_repo_extract.document_line_item := r_po_details.document_line_item ;
1148 p_trx_repo_extract.document_line_desc := r_po_details.document_line_desc ;
1149 p_trx_repo_extract.document_line_qty := r_po_details.document_line_qty ;
1150 p_trx_repo_extract.document_line_uom := r_po_details.document_line_uom ;
1151 p_trx_repo_extract.document_line_amt := r_po_details.document_line_amt ;
1152 p_trx_repo_extract.document_currency_code := r_po_details.document_currency_code ;
1153 p_trx_repo_extract.inventory_item_id := r_po_details.inventory_item_id ;
1154 p_trx_repo_extract.party_id := r_po_details.party_id ;
1155 p_trx_repo_extract.organization_id := r_po_details.organization_id ;
1156 p_trx_repo_extract.location_id := r_po_details.location_id ;
1157
1158 open c_get_po_line_loc_srvtyp (cp_po_line_id => p_document_line_id) ;
1159 fetch c_get_po_line_loc_srvtyp into lv_service_type
1160 ,p_trx_repo_extract.repository_tax_amt;
1161 close c_get_po_line_loc_srvtyp;
1162
1163 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
1164 -- if called from Repository Review UI then do not default service type from document
1165 p_trx_repo_extract.service_type_code := lv_service_type;
1166 end if;
1167
1168
1169 end if;
1170
1171 /*Started addition by vkantamn for Bug#6083978*/
1172
1173 IF p_document_source = 'RECEIVING' THEN
1174
1175 open c_get_rcv_details;
1176 fetch c_get_rcv_details into v_organization_id,v_location_id,v_po_header_id,v_po_line_id;
1177 close c_get_rcv_details;
1178
1179 open c_get_po_details(v_po_header_id,v_po_line_id);
1180 fetch c_get_po_details into r_po_details;
1181 close c_get_po_details;
1182
1183 if r_po_details.document_id is null then
1184 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1185 ,'Purchase Order does not exists for po_header_id ='||p_document_id ||' and po_line_id='|| p_document_line_id
1186 );
1187 end if;
1188
1189 p_trx_repo_extract.transaction_source := 'RECEIVING' ;
1190 p_trx_repo_extract.party_name := r_po_details.party_name ;
1191 p_trx_repo_extract.document_number := r_po_details.document_number ;
1192 p_trx_repo_extract.document_date := r_po_details.document_date ;
1193 p_trx_repo_extract.document_id := r_po_details.document_id ;
1194 p_trx_repo_extract.document_line_id := r_po_details.document_line_id ;
1195 p_trx_repo_extract.document_line_num := r_po_details.document_line_num ;
1196 p_trx_repo_extract.document_line_item := r_po_details.document_line_item ;
1197 p_trx_repo_extract.document_line_desc := r_po_details.document_line_desc ;
1198 p_trx_repo_extract.document_line_qty := r_po_details.document_line_qty ;
1199 p_trx_repo_extract.document_line_uom := r_po_details.document_line_uom ;
1200 p_trx_repo_extract.document_line_amt := r_po_details.document_line_amt ;
1201 p_trx_repo_extract.document_currency_code := r_po_details.document_currency_code ;
1202 p_trx_repo_extract.inventory_item_id := r_po_details.inventory_item_id ;
1203 p_trx_repo_extract.party_id := r_po_details.party_id ;
1204 p_trx_repo_extract.organization_id := v_organization_id ;
1205
1206 if v_location_id is null then
1207 p_trx_repo_extract.location_id := r_po_details.location_id;
1208 else
1209 p_trx_repo_extract.location_id := v_location_id;
1210 end if;
1211
1212 open c_get_po_line_loc_srvtyp (cp_po_line_id => v_po_line_id) ;
1213 fetch c_get_po_line_loc_srvtyp into lv_service_type,p_trx_repo_extract.repository_tax_amt;
1214 close c_get_po_line_loc_srvtyp;
1215
1216 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
1217 -- if called from Repository Review UI then do not default service type from document
1218 p_trx_repo_extract.service_type_code := lv_service_type;
1219 end if;
1220
1221 END IF;
1222
1223 /*Addition done by vkantamn for Bug#6083978*/
1224
1225 if p_document_source = 'ORDER MANAGEMENT' then
1226
1227 open c_get_so_details;
1228 fetch c_get_so_details into r_so_details;
1229 close c_get_so_details;
1230
1231 if r_so_details.document_id is null then
1232 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1233 ,'Sales Order does not exists for header_id='||p_document_id ||' and line_id ='||p_document_line_id
1234 );
1235 end if;
1236
1237 p_trx_repo_extract.transaction_source := 'ORDER MANAGEMENT' ;
1238 p_trx_repo_extract.party_name := r_so_details.party_name ;
1239 p_trx_repo_extract.document_number := r_so_details.document_number ;
1240 p_trx_repo_extract.document_date := r_so_details.document_date ;
1241 p_trx_repo_extract.document_id := r_so_details.document_id ;
1242 p_trx_repo_extract.document_line_id := r_so_details.document_line_id ;
1243 p_trx_repo_extract.document_line_num := r_so_details.document_line_num ;
1244 p_trx_repo_extract.document_line_item := r_so_details.document_line_item ;
1245 p_trx_repo_extract.document_line_desc := r_so_details.document_line_desc ;
1246 p_trx_repo_extract.document_line_qty := r_so_details.document_line_qty ;
1247 p_trx_repo_extract.document_line_uom := r_so_details.document_line_uom ;
1248 p_trx_repo_extract.document_line_amt := r_so_details.document_line_amt ;
1249 p_trx_repo_extract.document_currency_code := r_so_details.document_currency_code ;
1250 p_trx_repo_extract.inventory_item_id := r_so_details.inventory_item_id ;
1251 p_trx_repo_extract.party_id := r_so_details.party_id ;
1252 p_trx_repo_extract.organization_id := r_so_details.organization_id ;
1253 --
1254 -- For sales order location will be derrived from invoice because an order can be a bill only or it can be a normal shipped order
1255 -- In order to derrive location complex logic needs to be implemented to check order type and based on that derive the location
1256 -- However here we already have a reference of invoice so it is better to derrive it from invoice only
1257 -- As an enahcement to this API a logic can be added here to derrive the location using order reference only
1258
1259 IF r_so_details.line_category_code = 'ORDER' THEN
1260
1261 open c_get_so_line_srvtyp (cp_line_id => p_document_line_id);
1262 fetch c_get_so_line_srvtyp into lv_service_type
1263 ,p_trx_repo_extract.repository_tax_amt;
1264 close c_get_so_line_srvtyp ;
1265
1266 ELSIF r_so_details.line_category_code = 'RETURN' THEN
1267
1268 open c_get_rma_line_srvtyp(cp_line_id => p_document_line_id);
1269 fetch c_get_rma_line_srvtyp into lv_service_type
1270 ,p_trx_repo_extract.repository_tax_amt;
1271 close c_get_rma_line_srvtyp ;
1272
1273 END IF;
1274
1275 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
1276 -- if called from Repository Review UI then do not default service type from document
1277 p_trx_repo_extract.service_type_code := lv_service_type;
1278 end if;
1279
1280
1281 end if;
1282
1283 -- Begin 5876390, 6012570
1284 if p_document_source = 'PROJECTS' then -- Projects Invoice
1285
1286 open c_get_pa_details;
1287 fetch c_get_pa_details into r_pa_details;
1288 close c_get_pa_details;
1289
1290 if r_pa_details.document_id is null then
1291 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1292 ,'Project Draft Invoice does not exists for p_document_id ='||p_document_id ||' and p_document_line_id='||p_document_line_id
1293 );
1294 end if;
1295
1296 p_trx_repo_extract.transaction_source := 'PROJECTS' ;
1297 p_trx_repo_extract.party_name := r_pa_details.party_name ;
1298 p_trx_repo_extract.document_number := r_pa_details.document_number ;
1299 p_trx_repo_extract.document_date := r_pa_details.document_date ;
1300 p_trx_repo_extract.document_id := r_pa_details.document_id ;
1301 p_trx_repo_extract.document_line_id := r_pa_details.document_line_id ;
1302 p_trx_repo_extract.document_line_num := r_pa_details.document_line_num ;
1303 p_trx_repo_extract.document_line_item := r_pa_details.document_line_item ;
1304 p_trx_repo_extract.document_line_desc := r_pa_details.document_line_desc ;
1305 p_trx_repo_extract.document_line_qty := r_pa_details.document_line_qty ;
1306 p_trx_repo_extract.document_line_uom := r_pa_details.document_line_uom ;
1307 p_trx_repo_extract.document_line_amt := r_pa_details.document_line_amt ;
1308 p_trx_repo_extract.document_currency_code := r_pa_details.document_currency_code ;
1309 p_trx_repo_extract.inventory_item_id := r_pa_details.inventory_item_id ;
1310 p_trx_repo_extract.party_id := r_pa_details.party_id ;
1311 p_trx_repo_extract.organization_id := r_pa_details.organization_id ;
1312 p_trx_repo_extract.location_id := r_pa_details.location_id ;
1313 lv_service_type := r_pa_details.service_type_code ;
1314
1315 open c_get_pa_inv_line_tax (cp_line_id => r_pa_details.document_line_id);
1316 fetch c_get_pa_inv_line_tax into p_trx_repo_extract.repository_tax_amt;
1317 close c_get_pa_inv_line_tax ;
1318
1319 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
1320 -- if called from Repository Review UI then do not default service type from document
1321 p_trx_repo_extract.service_type_code := lv_service_type;
1322 end if;
1323
1324
1325 end if;
1326 -- End 5876390, 6012570
1327
1328 if p_document_source = 'RECEIVABLES' then
1329
1330 open c_get_ra_trx_details;
1331 fetch c_get_ra_trx_details into r_ra_trx_details;
1332 close c_get_ra_trx_details;
1333
1334 if r_ra_trx_details.document_id is null then
1335 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1336 ,'AR Transaction does not exists for ra_customer_trx_id='||p_document_id ||' and customer_trx_line_id='||p_document_line_id
1337 );
1338 end if;
1339 p_trx_repo_extract.transaction_source := 'RECEIVABLES' ;
1340 p_trx_repo_extract.party_name := r_ra_trx_details.party_name ;
1341 p_trx_repo_extract.document_number := r_ra_trx_details.document_number ;
1342 p_trx_repo_extract.document_date := r_ra_trx_details.document_date ;
1343 p_trx_repo_extract.document_id := r_ra_trx_details.document_id ;
1344 p_trx_repo_extract.document_line_id := r_ra_trx_details.document_line_id ;
1345 p_trx_repo_extract.document_line_num := r_ra_trx_details.document_line_num ;
1346 p_trx_repo_extract.document_line_item := r_ra_trx_details.document_line_item ;
1347 p_trx_repo_extract.document_line_desc := r_ra_trx_details.document_line_desc ;
1348 p_trx_repo_extract.document_line_qty := r_ra_trx_details.document_line_qty ;
1349 p_trx_repo_extract.document_line_uom := r_ra_trx_details.document_line_uom ;
1350 p_trx_repo_extract.document_line_amt := r_ra_trx_details.document_line_amt ;
1351 p_trx_repo_extract.document_currency_code := r_ra_trx_details.document_currency_code ;
1352 p_trx_repo_extract.inventory_item_id := r_ra_trx_details.inventory_item_id ;
1353 p_trx_repo_extract.party_id := r_ra_trx_details.party_id ;
1354 p_trx_repo_extract.organization_id := r_ra_trx_details.organization_id ;
1355 p_trx_repo_extract.location_id := r_ra_trx_details.location_id ;
1356
1357 open c_get_ra_line_srvtyp (cp_customer_trx_line_id => p_document_line_id) ;
1358 fetch c_get_ra_line_srvtyp into lv_service_type
1359 ,p_trx_repo_extract.repository_tax_amt;
1360 close c_get_ra_line_srvtyp ;
1361
1362 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
1363 -- if called from Repository Review UI then do not default service type from document
1364 p_trx_repo_extract.service_type_code := lv_service_type;
1365 end if;
1366
1367
1368 end if;
1369 -- Added by Bill Shi for standalone invoice on 2007/09/27
1370 --------------------------------------------------------------------------
1371 IF (p_document_source = jai_constants.G_AP_STANDALONE_INVOICE)
1372 THEN
1373 OPEN l_get_si_details_csr;
1374 FETCH l_get_si_details_csr INTO l_si_details_rec;
1375 CLOSE l_get_si_details_csr;
1376
1377 IF r_ra_trx_details.document_id IS NULL
1378 THEN
1379 jai_cmn_debug_contexts_pkg.print
1380 ( ln_reg_id,'AP Transaction does not exists for invoice_id='||
1381 p_document_id ||' and line_number='||p_document_line_id
1382 );
1383 END IF;
1384
1385 p_trx_repo_extract.transaction_source :=
1386 jai_constants.G_AP_STANDALONE_INVOICE;
1387 p_trx_repo_extract.party_name :=
1388 l_si_details_rec.party_name ;
1389 p_trx_repo_extract.document_number :=
1390 l_si_details_rec.document_number;
1391 p_trx_repo_extract.document_date :=
1392 l_si_details_rec.document_date ;
1393 p_trx_repo_extract.document_id :=
1394 l_si_details_rec.document_id ;
1395 p_trx_repo_extract.document_line_id :=
1396 l_si_details_rec.document_line_id ;
1397 p_trx_repo_extract.document_line_num :=
1398 l_si_details_rec.document_line_num ;
1399 p_trx_repo_extract.document_line_item :=
1400 l_si_details_rec.document_line_item;
1401 p_trx_repo_extract.document_line_desc :=
1402 l_si_details_rec.document_line_desc;
1403 p_trx_repo_extract.document_line_qty :=
1404 l_si_details_rec.document_line_qty ;
1405 p_trx_repo_extract.document_line_uom :=
1406 l_si_details_rec.document_line_uom ;
1407 p_trx_repo_extract.document_line_amt :=
1408 l_si_details_rec.document_line_amt ;
1409 p_trx_repo_extract.document_currency_code :=
1410 l_si_details_rec.document_currency_code ;
1411 p_trx_repo_extract.inventory_item_id :=
1412 l_si_details_rec.inventory_item_id ;
1413 p_trx_repo_extract.party_id :=
1414 l_si_details_rec.party_id ;
1415 p_trx_repo_extract.organization_id :=
1416 l_si_details_rec.organization_id ;
1417 p_trx_repo_extract.location_id :=
1418 l_si_details_rec.location_id ;
1419 -- Deleted by Lion for bug#6911533 on 2008/04/22
1420 /*
1421 OPEN l_get_si_tax_amt_csr(lp_line_id => p_document_line_id);
1422 FETCH l_get_si_tax_amt_csr
1423 INTO
1424 lv_service_type
1425 , p_trx_repo_extract.repository_tax_amt;
1426 CLOSE l_get_si_tax_amt_csr;
1427 */
1428 --Added by Lion for bug#6911533 on 2008/04/22
1429 ---------------------------------------------------------------------------
1430 OPEN l_get_si_tax_amt_csr(lp_line_id => p_document_line_id);
1431 FETCH l_get_si_tax_amt_csr
1432 INTO
1433 p_trx_repo_extract.repository_tax_amt;
1434 CLOSE l_get_si_tax_amt_csr;
1435
1436 OPEN l_get_si_service_type_csr(lp_line_id => p_document_line_id);
1437 FETCH l_get_si_service_type_csr
1438 INTO
1439 lv_service_type;
1440 CLOSE l_get_si_service_type_csr;
1441 ---------------------------------------------------------------------------
1442
1443 IF (nvl(p_called_from,'$#$') NOT IN ('JAINRPRW'))
1444 THEN
1445 --if called from Repository Review UI
1446 --then do not default service type from document
1447 p_trx_repo_extract.service_type_code := lv_service_type;
1448 END IF; -- (nvl(p_called_from,'$#$') NOT IN ('JAINRPRW'))
1449
1450 END IF; -- (p_document_source = jai_constants.AP_STANDALONE_INVOICE)
1451 --------------------------------------------------------------------------
1452 /** Deregister procedure and return*/
1453 <<deregister_and_return>>
1454 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
1455
1456 exception
1457 when others then
1458 p_process_flag := jai_constants.unexpected_error;
1459 p_process_message := lv_context||'->'||sqlerrm;
1460 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1461 jai_cmn_debug_contexts_pkg.print_stack;
1462 end get_document_details ;
1463
1464 /*------------------------------------------------------------------------------------------------------------*/
1465 -- Overloading the GET_DOC_FROM_REFERENCE to return only organization_id, location_id and service_type_code for a given document reference
1466 procedure get_doc_from_reference
1467 ( p_reference_id in number
1468 , p_organization_id out nocopy number
1469 , p_location_id out nocopy number
1470 , p_service_type_code out nocopy varchar2
1471 , p_process_flag out nocopy varchar2
1472 , p_process_message out nocopy varchar2
1473 )
1474 is
1475 lr_trx_repo_ext_rec jai_trx_repo_extract_gt%rowtype;
1476 begin
1477
1478 -- Delegate the call to actual procedure that derrived the document details from refernece
1479 get_doc_from_reference
1480 ( p_reference_id => p_reference_id
1481 , p_trx_repo_extract_rec => lr_trx_repo_ext_rec
1482 , p_process_flag => p_process_flag
1483 , p_process_message => p_process_message
1484 );
1485 if p_process_flag = jai_constants.SUCCESSFUL then
1486 p_organization_id := lr_trx_repo_ext_rec.organization_id;
1487 p_location_id := lr_trx_repo_ext_rec.location_id;
1488 p_service_type_code := lr_trx_repo_ext_rec.service_type_code;
1489 end if;
1490
1491
1492 end get_doc_from_reference ;
1493
1494 /*------------------------------------------------------------------------------------------------------------*/
1495 procedure get_doc_from_reference
1496 ( p_reference_id in number
1497 , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
1498 , p_process_flag out nocopy varchar2
1499 , p_process_message out nocopy varchar2
1500 )
1501 is
1502
1503 cursor c_get_refs_rec
1504 is
1505 select reference_id
1506 , source
1507 , invoice_id
1508 , item_line_id
1509 , line_id /*Added by nprashar for bug # 7172723*/
1510 from jai_rgm_trx_refs refs
1511 where refs.reference_id = p_reference_id;
1512
1513 /*Addition of code by nprashar for bug # 7172723*/
1514 cursor c_get_source_type
1515 is
1516 select source
1517 from ap_invoices_all aia
1518 where aia.invoice_id in
1519 (select invoice_id
1520 from jai_rgm_Trx_refs refs
1521 where refs.reference_id = p_reference_id);
1522
1523 cursor c_get_line_number(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_line_id jai_rgm_trx_refs.line_id%type)
1524 is
1525 select inv_dist_id,
1526 line_num
1527 from
1528 (select
1529 INVOICE_DISTRIBUTION_ID inv_dist_id,
1530 row_number() over(ORDER BY INVOICE_DISTRIBUTION_ID) line_num
1531 from ap_invoice_distributions_all
1532 where INVOICE_ID=p_invoice_id
1533 )
1534 where inv_dist_id=p_line_id;
1535
1536 cursor c_get_doc_details(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_row_number number)
1537 is
1538 SELECT shipment_header_id,
1539 shipment_line_id,
1540 receipt_num,
1541 creation_date,
1542 qty_received,
1543 tax_amount,
1544 organization_id,
1545 inventory_item_id,
1546 uom_code,
1547 location_id,
1548 vendor_id,
1549 vendor_site_id
1550 FROM
1551 (SELECT jrt.shipment_header_id shipment_header_id,
1552 jrt.shipment_line_id shipment_line_id,
1553 jrt.receipt_num receipt_num,
1554 jrt.creation_date creation_date,
1555 jrt.qty_received qty_received,
1556 jrtxl.tax_amount tax_amount,
1557 jrt.organization_id organization_id,
1558 jrt.inventory_item_id inventory_item_id,
1559 jirt.uom_code uom_code,
1560 jirt.location_id location_id,
1561 jrti.vendor_id vendor_id,
1562 jrti.vendor_site_id vendor_site_id,
1563 row_number() over(
1564 ORDER BY jrtxl.shipment_line_id,jrtxl.tax_line_no) rn
1565 FROM jai_rcv_lines jrt,
1566 rcv_transactions jirt,
1567 jai_rcv_tp_invoices jrti,
1568 jai_rcv_line_taxes jrtxl -- join to ja_in_receipt_tax_lines added by vumaasha for 6856213
1569 WHERE jrt.shipment_header_id = jrti.shipment_header_id
1570 AND jrti.invoice_id = p_invoice_id
1571 AND jrti.shipment_header_id = jirt.shipment_header_id
1572 AND jirt.transaction_type = 'RECEIVE'
1573 AND jirt.shipment_line_id = jrt.shipment_line_id
1574 AND jrtxl.shipment_header_id = jirt.shipment_header_id
1575 AND jrtxl.shipment_header_id = jrti.shipment_header_id
1576 AND jirt.shipment_line_id = jrtxl.shipment_line_id)
1577 WHERE rn =p_row_number ;
1578
1579 cursor c_get_ra_line_srvtyp(p_vendor_id jai_rcv_tp_invoices.VENDOR_ID%type,p_vendor_site_id jai_rcv_tp_invoices.VENDOR_SITE_ID%type)
1580 is
1581 select service_type_code
1582 from
1583 jai_cmn_vendor_sites
1584 where vendor_id= p_vendor_id
1585 and vendor_site_id=p_vendor_site_id;
1586
1587 lr_service_type jai_rgm_trx_records.service_type_code%type;
1588 lr_called_from varchar2(20) default null;
1589
1590 lr_line_number number;
1591 lr_inv_dist_id ap_invoice_distributions_all.INVOICE_DISTRIBUTION_ID%type;
1592
1593 /*Addition Ends for bug 7172723*/
1594
1595 lr_refs_rec c_get_refs_rec%rowtype;
1596
1597 ln_doc_id number;
1598 ln_doc_line_id number;
1599 lv_trx_src jai_rgm_trx_records.source%type;
1600 lr_source_type ap_invoices_all.source%type;
1601 lr_doc_details c_get_doc_details%rowtype;
1602
1603
1604 begin
1605
1606 /*
1607 p_trx_repo_extract_rec.service_type_code := '105-E';
1608 p_trx_repo_extract_rec.organization_id := 2832 ;
1609 p_trx_repo_extract_rec.location_id := 10023 ;
1610 */
1611 open c_get_refs_rec;
1612 fetch c_get_refs_rec into lr_refs_rec;
1613 close c_get_refs_rec;
1614
1615 if lr_refs_rec.reference_id is null then
1616 p_process_flag := jai_constants.EXPECTED_ERROR;
1617 p_process_message := 'Invalid reference id. Unable to location a repository reference for P_REFERENCE_ID='||p_reference_id;
1618 return;
1619 end if;
1620
1621
1622 if lr_refs_rec.source is null
1623 or lr_refs_rec.invoice_id is null
1624 or lr_refs_rec.item_line_id is null
1625 then
1626 p_process_flag := jai_constants.EXPECTED_ERROR;
1627 p_process_message := 'Unable to find transaction references in the repository. Source='|| lr_refs_rec.source
1628 ||', InvoiceID='|| lr_refs_rec.invoice_id
1629 ||', ItemLineID=' ||lr_refs_rec.item_line_id ;
1630 return;
1631 end if;
1632
1633 /* For Bug# 7172723 */
1634 open c_get_source_type; /* To get the source type */
1635 fetch c_get_source_type into lr_source_type;
1636 close c_get_source_type;
1637 /* if source = Recipt ,for 3rd party invoices */
1638 if NVL(lr_source_type,'$$$') = 'INDIA TAX INVOICE' THEN /*Added by nprashar for bug # 7172723*/
1639 /* open cursor c_get_line_number for invoice_id to fetch the exact delivery location number when multiple receipts lines are there.*/
1640 lr_line_number :=1;
1641 open c_get_line_number(lr_refs_rec.invoice_id,lr_refs_rec.line_id);
1642 fetch c_get_line_number into lr_inv_dist_id,lr_line_number;
1643 close c_get_line_number;
1644 /* get information from receipt */
1645
1646 open c_get_doc_details(lr_refs_rec.invoice_id,lr_line_number);
1647 fetch c_get_doc_details into lr_doc_details;
1648 close c_get_doc_details;
1649
1650 p_trx_repo_extract_rec.transaction_source := 'PURCHASING' ;
1651 -- p_trx_repo_extract.party_name := lr_doc_details.party_name ;
1652 p_trx_repo_extract_rec.document_number := lr_doc_details.receipt_num ;
1653 p_trx_repo_extract_rec.document_date := lr_doc_details.creation_date ;
1654 --p_trx_repo_extract.document_id := lr_doc_details.document_id ;
1655 --p_trx_repo_extract.document_line_id := lr_doc_details.document_line_id ;
1656 -- p_trx_repo_extract.document_line_num := lr_doc_details.document_line_num ;
1657 -- p_trx_repo_extract.document_line_item := lr_doc_details.document_line_item ;
1658 -- p_trx_repo_extract.document_line_desc := lr_doc_details.document_line_desc ;
1659 p_trx_repo_extract_rec.document_line_qty := lr_doc_details.qty_received ;
1660 p_trx_repo_extract_rec.document_line_uom := lr_doc_details.uom_code ;
1661 p_trx_repo_extract_rec.document_line_amt := lr_doc_details.tax_amount ;
1662 -- p_trx_repo_extract.document_currency_code := lr_doc_details.document_currency_code ;
1663 p_trx_repo_extract_rec.inventory_item_id := lr_doc_details.inventory_item_id ;
1664 p_trx_repo_extract_rec.party_id := lr_doc_details.vendor_id ;
1665 p_trx_repo_extract_rec.organization_id := lr_doc_details.organization_id ;
1666 p_trx_repo_extract_rec.location_id := lr_doc_details.location_id ;
1667
1668 --get service type from vendor addition information
1669 open c_get_ra_line_srvtyp(lr_doc_details.vendor_id,lr_doc_details.vendor_site_id);
1670 fetch c_get_ra_line_srvtyp into lr_service_type;
1671 close c_get_ra_line_srvtyp;
1672 -- if no service type for vendor and vendor site then get the service type for vendor null site
1673 if lr_service_type is null then
1674 open c_get_ra_line_srvtyp(lr_doc_details.vendor_id,'0');
1675 fetch c_get_ra_line_srvtyp into lr_service_type;
1676 close c_get_ra_line_srvtyp;
1677 end if;
1678 if nvl(lr_called_from,'$#$') not in ('JAINRPRW') then
1679 -- if called from Repository Review UI then do not default service type from document
1680 p_trx_repo_extract_rec.service_type_code := lr_service_type;
1681 end if;
1682
1683 p_process_flag := jai_constants.SUCCESSFUL;
1684 ELSE
1685
1686 jai_trx_repo_extract_pkg.derrive_doc_from_ref
1687 ( p_reference_source => lr_refs_rec.source
1688 , p_reference_invoice_id => lr_refs_rec.invoice_id
1689 , p_reference_item_line_id => lr_refs_rec.item_line_id
1690 , p_trx_repo_extract_rec => p_trx_repo_extract_rec
1691 , p_process_message => p_process_message
1692 , p_process_flag => p_process_flag
1693 ) ;
1694 ln_doc_id := p_trx_repo_extract_rec.document_id;
1695 ln_doc_line_id := p_trx_repo_extract_rec.document_line_id;
1696 lv_trx_src := p_trx_repo_extract_rec.transaction_source;
1697
1698
1699 if p_process_flag <> jai_constants.SUCCESSFUL then
1700 return;
1701 end if;
1702
1703 jai_trx_repo_extract_pkg.get_document_details
1704 ( p_document_id => ln_doc_id
1705 , p_document_line_id => ln_doc_line_id
1706 , p_document_source => lv_trx_src
1707 , p_process_message => p_process_message
1708 , p_process_flag => p_process_flag
1709 , p_trx_repo_extract => p_trx_repo_extract_rec
1710 );
1711 if p_process_flag <> jai_constants.SUCCESSFUL then
1712 return;
1713 end if;
1714
1715 ENd IF;
1716
1717 end get_doc_from_reference;
1718
1719 /*------------------------------------------------------------------------------------------------------------*/
1720 procedure update_service_type ( p_process_flag out nocopy varchar2
1721 , p_process_message out nocopy varchar2
1722 )
1723 is
1724
1725 cursor c_get_recs_to_update
1726 is
1727 select *
1728 from jai_trx_repo_extract_gt
1729 where processed_flag = jai_constants.NO;
1730
1731 ln_reg_id number;
1732
1733 begin
1734
1735 lv_member_name := 'UPDATE_SERVICE_TYPE';
1736 set_debug_context;
1737
1738 /* Initialize the process variables */
1739 p_process_flag := jai_constants.SUCCESSFUL;
1740
1741 jai_cmn_debug_contexts_pkg.register (lv_context, ln_reg_id);
1742 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Begin loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
1743 for rec in c_get_recs_to_update
1744 loop
1745 -- For each record in temporary table which is not yet processed
1746 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1747 ,'rec.transaction_source='||rec.transaction_source || fnd_global.local_chr(10) ||
1748 'rec.document_id='||rec.document_id || fnd_global.local_chr(10) ||
1749 'rec.document_line_id='||rec.document_line_id || fnd_global.local_chr(10) ||
1750 'rec.service_type_code='||rec.service_type_code
1751 );
1752 --
1753 -- Update the transaction tables with the service type code based on source value
1754 --
1755 if rec.transaction_source in ('PURCHASING','RECEIVING') then
1756
1757 update JAI_PO_LINE_LOCATIONS set service_type_code = rec.service_type_code
1758 , last_update_date = sysdate
1759 , last_updated_by = lv_user_id
1760 , last_update_login = lv_login_id
1761 where po_header_id = rec.document_id
1762 and po_line_id = rec.document_line_id;
1763
1764 elsif rec.transaction_source = 'ORDER MANAGEMENT' then
1765
1766 update JAI_OM_OE_SO_LINES set service_type_code = rec.service_type_code
1767 , last_update_date = sysdate
1768 , last_updated_by = lv_user_id
1769 , last_update_login = lv_login_id
1770 where header_id = rec.document_id
1771 and line_id = rec.document_line_id;
1772
1773 elsif rec.transaction_source = 'RECEIVABLES' then
1774
1775 update JAI_AR_TRX_LINES set service_type_code = rec.service_type_code
1776 , last_update_date = sysdate
1777 , last_updated_by = lv_user_id
1778 , last_update_login = lv_login_id
1779 where customer_trx_id = rec.document_id
1780 and customer_trx_line_id = rec.document_line_id;
1781
1782 elsif rec.transaction_source = 'MANUAL' then
1783
1784 update jai_rgm_manual_trxs
1785 set service_type_code = rec.service_type_code
1786 , last_update_date = sysdate
1787 , last_updated_by = lv_user_id
1788 where transaction_number = rec.document_id;
1789
1790 elsif rec.transaction_source in ('SERVICE_DISTRIBUTE_OUT') then
1791
1792 -- Update Source of the distribution
1793 update jai_rgm_dis_src_hdrs
1794 set service_type_code = rec.service_type_code
1795 , last_update_date = sysdate
1796 , last_updated_by = lv_user_id
1797 , last_update_login = lv_login_id
1798 where transfer_id = rec.document_id;
1799
1800 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1801 ,'No of rows updated in trx table='||sql%rowcount
1802 );
1803 --Added by Lion for bug#6911533 on 2008/04/22
1804 -------------------------------------------------------------------------
1805 elsif rec.transaction_source = jai_constants.G_AP_STANDALONE_INVOICE then
1806
1807 update JAI_AP_INVOICE_LINES set service_type_code = rec.service_type_code
1808 , last_update_date = sysdate
1809 , last_updated_by = lv_user_id
1810 , last_update_login = lv_login_id
1811 where invoice_id = rec.document_id
1812 and invoice_line_number = rec.DOCUMENT_LINE_NUM;
1813 ------------------------------------------------------------------------
1814 --
1815 -- Update service type code for SERVICE_DISTRIBUTE_IN type of trx with the same service type
1816 -- code as give in the source (SERVICE_DISTRIBUTE_OUT) --
1817
1818 update jai_rgm_trx_records
1819 set service_type_code = rec.service_type_code
1820 , last_update_date = sysdate
1821 , last_updated_by = lv_user_id
1822 , last_update_login = lv_login_id
1823 where source_document_id = rec.document_id
1824 and source = 'SERVICE_DISTRIBUTE_IN';
1825
1826 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1827 ,'No of rows updated in repository table for source SERVICE_DISTRIBUTE_IN='||sql%rowcount
1828 );
1829
1830 -- Begin 5876390, 6012570
1831 elsif rec.transaction_source = 'PROJECTS' then
1832
1833 update jai_pa_draft_invoice_lines
1834 set service_type_code = rec.service_type_code
1835 , last_update_date = sysdate
1836 , last_updated_by = lv_user_id
1837 , last_update_login = lv_login_id
1838 where draft_invoice_id = rec.document_id
1839 and draft_invoice_line_id = rec.document_line_id;
1840
1841 -- End bug 5876390, 6012570
1842
1843 end if;
1844
1845 -- Bug 5876390, 6012570
1846
1847 if rec.transaction_source in ('PROJECTS','ORDER MANAGEMENT') then
1848 -- In case of project and order invoices the invoice also should be updated
1849 update jai_ar_trx_lines
1850 set service_type_code = rec.service_type_code
1851 , last_update_date = sysdate
1852 , last_updated_by = lv_user_id
1853 , last_update_login = lv_login_id
1854 where customer_trx_id = rec.repository_invoice_id
1855 and customer_trx_line_id = rec.repository_line_id;
1856
1857 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1858 ,'Rows updated in ja_in_ra_customer_trx_lines='||sql%rowcount
1859 );
1860 end if;
1861
1862 -- End Bug 5876390, 6012570
1863
1864 if rec.transaction_source not in ('SERVICE_DISTRIBUTE_OUT') then
1865 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1866 ,'No of rows updated in trx table='||sql%rowcount
1867 );
1868 end if;
1869
1870 --
1871 -- Update repository records with service type code
1872 --
1873
1874 update jai_rgm_trx_records
1875 set service_type_code = rec.service_type_code
1876 , last_update_date = sysdate
1877 , last_updated_by = lv_user_id
1878 where repository_id = rec.transaction_repository_id;
1879
1880 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1881 ,'No of rows updated in jai_rgm_trx_records table='||sql%rowcount
1882 );
1883 --
1884 -- Mark record in global temp table as PROCESSED
1885 --
1886 update jai_trx_repo_extract_gt
1887 set processed_flag = 'Y'
1888 where transaction_repository_id = rec.transaction_repository_id;
1889
1890 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1891 ,'No of rows updated in jai_trx_repo_extract_gt table='||sql%rowcount
1892 );
1893
1894 end loop;
1895 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'End loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
1896
1897 /** Deregister procedure and return*/
1898 <<deregister_and_return>>
1899 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
1900
1901 exception
1902 when others then
1903 p_process_flag := jai_constants.unexpected_error;
1904 p_process_message := lv_context||'->'||sqlerrm;
1905 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1906 jai_cmn_debug_contexts_pkg.print_stack;
1907
1908 end update_service_type;
1909
1910 /*------------------------------------------------------------------------------------------------------------*/
1911
1912 procedure derrive_doc_from_ref
1913 ( p_reference_source in jai_rgm_trx_refs.source%type
1914 , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
1915 , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
1916 , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
1917 , p_process_message out nocopy varchar2
1918 , p_process_flag out nocopy varchar2
1919 )
1920 is
1921 lv_created_from ra_customer_trx_all.created_from%type ;
1922
1923 cursor c_chk_ar_inv_attr (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%type
1924 ,cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type
1925 )
1926 is
1927 select rct.created_from
1928 , rct.interface_header_context
1929 , rct.interface_header_attribute1 -- holds order number if context is ORDER_ENTRY
1930 , rctl.interface_line_attribute6 -- holds order line id if context is ORDER_ENTRY
1931 , rctl.interface_line_attribute1 -- 5876390, 6012570, holds PROJECT_NUMBER if context is PROJECTS INVOICES
1932 , rctl.interface_line_attribute2 -- 5876390, 6012570, holds DRAFT_INVOICE_NUM if context is PROJECTS_INOVICES
1933 from ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
1934 where rct.customer_trx_id = cp_customer_trx_id
1935 and rct.customer_trx_id = rctl.customer_trx_id
1936 and rctl.customer_trx_line_id = cp_customer_trx_line_id;
1937
1938 lr_inv_attr c_chk_ar_inv_attr%rowtype;
1939
1940 cursor c_get_po_reference (cp_invoice_id ap_invoice_distributions_all.invoice_id%type
1941 ,cp_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type
1942 )
1943 is
1944 select pod.po_header_id
1945 ,pod.po_line_id
1946 ,apd.rcv_transaction_id /*Added by vkantamn for Bug#6083978*/
1947 from po_distributions_all pod
1948 ,ap_invoice_distributions_all apd
1949 where pod.po_distribution_id = apd.po_distribution_id
1950 and apd.invoice_id = cp_invoice_id
1951 and apd.invoice_distribution_id = cp_distribution_id;
1952
1953 -- Added by Bill Shi for standalone invoice on 2007/09/27
1954 --------------------------------------------------------------------------
1955 CURSOR l_get_si_reference_csr
1956 ( lp_invoice_id jai_ap_invoice_lines.invoice_id%TYPE
1957 , lp_invoice_line_number jai_ap_invoice_lines.invoice_line_number%TYPE
1958 ) IS
1959 SELECT
1960 jail.invoice_id
1961 , jail.invoice_line_number
1962 FROM
1963 jai_ap_invoice_lines jail
1964 , ap_invoice_distributions_all aida
1965 WHERE jail.invoice_id = aida.invoice_id
1966 AND jail.invoice_line_number = aida.invoice_line_number
1967 AND jail.invoice_id = lp_invoice_id
1968 AND aida.invoice_line_number = lp_invoice_line_number; -- Modified by Lion for bug#6977917
1969 --------------------------------------------------------------------------
1970 cursor c_get_loc_from_invoice (cp_customer_trx_id jai_ar_trxs.customer_trx_id%type)
1971 is
1972 select location_id
1973 from jai_ar_trxs
1974 where customer_trx_id = cp_customer_trx_id ;
1975
1976 -- Begin 5876390, 6012570
1977 cursor c_get_jai_pa_details (cp_project_number pa_projects_all.segment1%type
1978 ,cp_draft_inv_num jai_pa_draft_invoice_lines.draft_invoice_num%type
1979 ,cp_line_num jai_pa_draft_invoice_lines.line_num%type
1980 )
1981 is
1982 select draft_invoice_id
1983 ,draft_invoice_line_id
1984 from jai_pa_draft_invoice_lines jpdil
1985 ,pa_projects_all ppa
1986 where ppa.segment1 = cp_project_number
1987 and ppa.project_id = jpdil.project_id
1988 and jpdil.draft_invoice_num = cp_draft_inv_num
1989 and jpdil.line_num = cp_line_num;
1990
1991 ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type;
1992 ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type;
1993
1994 -- End 5876390, 6012570
1995
1996 /* Cursor Added by vkantamn for Bug#6083978 */
1997 cursor c_rcv_trans(cp_rcv_trans_id number)
1998 is
1999 select shipment_header_id,shipment_line_id
2000 from rcv_transactions
2001 where transaction_id = cp_rcv_trans_id;
2002
2003 /* Addition done by vkantamn for Bug#6083978 */
2004
2005 lr_po_reference c_get_po_reference%rowtype;
2006 -- Added by Bill Shi for standalone invoice on 2007/09/27
2007 --------------------------------------------------------------------------
2008 l_si_reference_rec l_get_si_reference_csr%ROWTYPE;
2009 --------------------------------------------------------------------------
2010 ln_reg_id number;
2011
2012 begin
2013
2014 lv_member_name := 'DERRIVE_DOC_FROM_REF';
2015 set_debug_context;
2016
2017 jai_cmn_debug_contexts_pkg.register ( lv_context, ln_reg_id );
2018
2019 if p_reference_source = 'AR' then
2020
2021 lv_created_from := null;
2022 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'OPEN/FETCH/CLOSE c_chk_ar_inv_attr, p_reference_invoice_id='||p_reference_invoice_id);
2023
2024 --
2025 -- Get the invoice attributes to make a decision regarding from where to fetch document details.
2026 -- If the invoice is an imported one then document is Sales Order other wise AR Invoice details will be fetched
2027 --
2028 open c_chk_ar_inv_attr (cp_customer_trx_id => p_reference_invoice_id
2029 , cp_customer_trx_line_id => p_reference_item_line_id
2030 );
2031 fetch c_chk_ar_inv_attr into lr_inv_attr;
2032 close c_chk_ar_inv_attr;
2033
2034 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'lr_inv_attr.created_from='||lr_inv_attr.created_from
2035 ||',lr_inv_attr.interface_header_context='||lr_inv_attr.interface_header_context
2036 ||',lr_inv_attr.interface_header_attribute1(order number)='||lr_inv_attr.interface_header_attribute1
2037 ||',lr_inv_attr.interface_line_attribute6(oe_line_id)='||lr_inv_attr.interface_line_attribute6
2038 ) ;
2039
2040 if lr_inv_attr.interface_header_context = 'ORDER ENTRY' then -- Invoice imported from Sales order
2041
2042 p_trx_repo_extract_rec.transaction_source := 'ORDER MANAGEMENT';
2043 p_trx_repo_extract_rec.document_id := null;
2044 p_trx_repo_extract_rec.document_line_id := lr_inv_attr.interface_line_attribute6;
2045
2046
2047 --
2048 -- The API get_document_details will not give location in case it is a Sales Order (Refer the API comments for details)
2049 -- So, derrive it from invoice reference
2050 --
2051 open c_get_loc_from_invoice (cp_customer_trx_id => p_reference_invoice_id);
2052 fetch c_get_loc_from_invoice into p_trx_repo_extract_rec.location_id;
2053 close c_get_loc_from_invoice;
2054
2055 -- Begin 5876390, 6012570
2056 elsif JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lr_inv_attr.interface_header_context) then -- Invoice imported from Projects
2057
2058 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'OPEN/FETCH/CLOSE c_get_jai_pa_details ' ||fnd_global.local_chr(10)
2059 ||'cp_project_number='||lr_inv_attr.interface_line_attribute1 ||fnd_global.local_chr(10)
2060 ||'cp_draft_inv_num='||lr_inv_attr.interface_line_attribute2 ||fnd_global.local_chr(10)
2061 ||'cp_line_num='||lr_inv_attr.interface_line_attribute6 ||fnd_global.local_chr (10)
2062 );
2063 open c_get_jai_pa_details ( cp_project_number => lr_inv_attr.interface_line_attribute1
2064 , cp_draft_inv_num => lr_inv_attr.interface_line_attribute2
2065 , cp_line_num => lr_inv_attr.interface_line_attribute6
2066 );
2067 fetch c_get_jai_pa_details into ln_draft_invoice_id
2068 ,ln_draft_invoice_line_id;
2069 close c_get_jai_pa_details;
2070
2071
2072 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_draft_invoice_id='||ln_draft_invoice_id
2073 ||'ln_draft_invoice_line_id='||ln_draft_invoice_line_id
2074 );
2075 p_trx_repo_extract_rec.transaction_source := 'PROJECTS';
2076 p_trx_repo_extract_rec.document_id := ln_draft_invoice_id;
2077 p_trx_repo_extract_rec.document_line_id := ln_draft_invoice_line_id;
2078
2079 --End 5876390, 6012570
2080
2081 elsif lr_inv_attr.interface_header_context is null then -- Manual AR Transactions
2082
2083 p_trx_repo_extract_rec.transaction_source := 'RECEIVABLES';
2084 p_trx_repo_extract_rec.document_id := p_reference_invoice_id;
2085 p_trx_repo_extract_rec.document_line_id := p_reference_item_line_id;
2086
2087 end if; --> lr_inv_attr.created
2088
2089 elsif p_reference_source = 'AP' then
2090
2091 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2092 , 'OPEN/FETCH/CLOSE c_get_po_reference'||fnd_global.local_chr(10)
2093 ||'p_reference_invoice_id ='||p_reference_invoice_id
2094 ||'p_reference_item_line_id ='||p_reference_item_line_id
2095 );
2096
2097 open c_get_po_reference (cp_invoice_id => p_reference_invoice_id
2098 ,cp_distribution_id => p_reference_item_line_id
2099 );
2100 fetch c_get_po_reference into lr_po_reference;
2101 -- close c_get_po_reference ; --Deleted by Lion Li for bug#6977917 on 2008/04/08
2102 -- Added by Bill Shi for standalone invoice on 2007/09/27
2103 ------------------------------------------------------------------------
2104 IF (c_get_po_reference%FOUND)
2105 THEN
2106 ------------------------------------------------------------------------
2107
2108 jai_cmn_debug_contexts_pkg.print(ln_reg_id
2109 ,'lr_po_reference.po_header_id =' ||lr_po_reference.po_header_id || fnd_global.local_chr(10) ||
2110 'lr_po_reference.po_line_id =' ||lr_po_reference.po_line_id
2111 );
2112
2113 IF lr_po_reference.rcv_transaction_id is NULL THEN /*If Condition added by vkantamn for Bug#6083978 */
2114 p_trx_repo_extract_rec.transaction_source := 'PURCHASING';
2115 p_trx_repo_extract_rec.document_id := lr_po_reference.po_header_id;
2116 p_trx_repo_extract_rec.document_line_id := lr_po_reference.po_line_id;
2117 /* Else Part added by vkantamn for Bug#6083978 */
2118 ELSE
2119 p_trx_repo_extract_rec.transaction_source := 'RECEIVING';
2120 open c_rcv_trans(lr_po_reference.rcv_transaction_id);
2121 fetch c_rcv_trans into p_trx_repo_extract_rec.document_id,p_trx_repo_extract_rec.document_line_id;
2122 close c_rcv_trans;
2123 END IF;
2124 /* Addition done by vkantamn for Bug#6083978 */
2125 -- Added by Bill Shi for standalone invoice on 2007/09/27
2126 ------------------------------------------------------------------------
2127 ELSE
2128 OPEN l_get_si_reference_csr (
2129 lp_invoice_id => p_reference_invoice_id
2130 , lp_invoice_line_number => p_reference_item_line_id );
2131 FETCH l_get_si_reference_csr INTO l_si_reference_rec;
2132
2133 p_trx_repo_extract_rec.transaction_source := jai_constants.G_AP_STANDALONE_INVOICE;
2134 p_trx_repo_extract_rec.document_id := l_si_reference_rec.invoice_id;
2135 p_trx_repo_extract_rec.document_line_id :=
2136 l_si_reference_rec.invoice_line_number;
2137
2138 CLOSE l_get_si_reference_csr;
2139
2140 END IF; -- (c_get_po_reference%FOUND)
2141 close c_get_po_reference ; --Added by Lion Li for bug#6977917 on 2008/04/08
2142 ------------------------------------------------------------------------
2143 end if;
2144
2145 /** Deregister procedure and return*/
2146 <<deregister_and_return>>
2147 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
2148
2149 exception
2150 when others then
2151 p_process_flag := jai_constants.unexpected_error;
2152 p_process_message := lv_context||'->'||sqlerrm;
2153 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
2154 jai_cmn_debug_contexts_pkg.print_stack;
2155
2156 end derrive_doc_from_ref;
2157
2158 /*------------------------------------------------------------------------------------------------------------*/
2159 --
2160 -- A wraper function on top of the procedure get_doc_from_reference to return only service type code
2161 -- This functions are used by Serivce Tax reports for printing service type code
2162 --
2163 function get_service_type_from_ref (p_reference_id in jai_rgm_trx_refs.reference_id%type
2164 )
2165
2166 return varchar2
2167 is
2168 lv_organization_id number;
2169 lv_location_id number;
2170 lv_service_type_code jai_rgm_trx_records.service_type_code%type;
2171 lv_process_flag varchar2 (2);
2172 lv_process_message varchar2 (2000);
2173
2174 begin
2175 jai_trx_repo_extract_pkg.get_doc_from_reference
2176 ( p_reference_id => p_reference_id
2177 , p_organization_id => lv_organization_id
2178 , p_location_id => lv_location_id
2179 , p_service_type_code => lv_service_type_code
2180 , p_process_flag => lv_process_flag
2181 , p_process_message => lv_process_message
2182 );
2183 if lv_process_flag = jai_constants.SUCCESSFUL then
2184 return lv_service_type_code;
2185 else
2186 return null;
2187 end if;
2188 end get_service_type_from_ref;
2189 /*------------------------------------------------------------------------------------------------------------*/
2190
2191 function get_settled_service_type
2192 ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
2193 , p_document_id jai_trx_repo_extract_gt.document_id%type
2194 , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
2195 )
2196 return varchar2 is
2197
2198 lv_service_type jai_rgm_trx_records.service_type_code%type;
2199
2200 cursor c_get_so_settled_srvtyp
2201 is
2202 select recs.service_type_code
2203 from jai_rgm_trx_records recs
2204 ,jai_rgm_trx_refs refs
2205 ,ra_customer_trx_lines_all ractl
2206 where ractl.interface_line_attribute6 = p_document_line_id
2207 and ractl.interface_line_context = 'ORDER ENTRY'
2208 and ractl.line_type = 'LINE'
2209 and ractl.customer_trx_line_id = refs.item_line_id
2210 and refs.reference_id = recs.reference_id
2211 and recs.settlement_id is not null
2212 and recs.service_type_code is not null
2213 and recs.regime_code = 'SERVICE'
2214 and recs.source = 'AR';
2215
2216 cursor c_get_ar_settled_srvtyp
2217 is
2218 select recs.service_type_code
2219 from jai_rgm_trx_records recs
2220 ,jai_rgm_trx_refs refs
2221 where refs.item_line_id = p_document_line_id
2222 and recs.reference_id = refs.reference_id
2223 and recs.settlement_id is not null
2224 and recs.service_type_code is not null
2225 and recs.regime_code = 'SERVICE'
2226 and recs.source = 'AR';
2227
2228 cursor c_get_po_settled_srvtyp
2229 is
2230 select recs.service_type_code
2231 from jai_rgm_trx_records recs
2232 ,jai_rgm_trx_refs refs
2233 ,po_distributions_all pod
2234 ,ap_invoice_distributions_all apd
2235 where pod.po_line_id = p_document_line_id
2236 and pod.po_distribution_id = apd.po_distribution_id
2237 and apd.invoice_distribution_id = refs.item_line_id
2238 and recs.reference_id = refs.reference_id
2239 and recs.settlement_id is not null
2240 and recs.service_type_code is not null
2241 and recs.regime_code = 'SERVICE'
2242 and recs.source = 'AP';
2243 --Added by Lion for bug#6911533 on 2008/04/22
2244 ------------------------------------------------------------------
2245 cursor c_get_aps_settled_srvtyp
2246 is
2247 select recs.service_type_code
2248 from jai_rgm_trx_records recs
2249 ,jai_rgm_trx_refs refs
2250 where refs.invoice_id = p_document_id
2251 and refs.item_line_id = p_document_line_id
2252 and recs.reference_id = refs.reference_id
2253 and recs.settlement_id is not null
2254 and recs.service_type_code is not null
2255 and recs.regime_code = 'SERVICE'
2256 and recs.source = 'AP';
2257 ------------------------------------------------------------------
2258
2259 begin
2260
2261 if p_transaction_source = 'ORDER MANAGEMENT' then
2262
2263 open c_get_so_settled_srvtyp;
2264 fetch c_get_so_settled_srvtyp into lv_service_type;
2265 close c_get_so_settled_srvtyp ;
2266
2267 elsif p_transaction_source = 'RECEIVABLES' then
2268
2269 open c_get_ar_settled_srvtyp;
2270 fetch c_get_ar_settled_srvtyp into lv_service_type;
2271 close c_get_ar_settled_srvtyp ;
2272
2273 elsif p_transaction_source = 'PURCHASING' then
2274
2275 open c_get_po_settled_srvtyp;
2276 fetch c_get_po_settled_srvtyp into lv_service_type;
2277 close c_get_po_settled_srvtyp ;
2278
2279 --Added by Lion for bug#6911533 on 2008/04/22
2280 ------------------------------------------------------------------------
2281 elsif p_transaction_source = jai_constants.G_AP_STANDALONE_INVOICE then
2282
2283 open c_get_aps_settled_srvtyp;
2284 fetch c_get_aps_settled_srvtyp into lv_service_type;
2285 close c_get_aps_settled_srvtyp ;
2286 -----------------------------------------------------------------------
2287 end if;
2288
2289 return lv_service_type;
2290
2291 end get_settled_service_type;
2292
2293
2294
2295
2296 end jai_trx_repo_extract_pkg;