1 package body jai_trx_repo_extract_pkg as
2 /* $Header: jai_trx_repo_ext.plb 120.70.12020000.6 2013/01/30 16:00:06 nkodakan 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
38 8. 25-Feb-2008 rchandan 6841116 120.3.12000000.3 Issue : The PO Matched to Receipt transactions are not shown in the
39 'Service tax Repository Review' form after running the India Service Tax Processing' conc program.
40 Fix : This above issue has been fixed by adding a new elsif condition for the 'RECIVING'
41 in the procedure 'extract_rgm_trxs'.
42 This is forward port of bug#6323157
43
44 9. 3-march-2008 Changes by nprashar for bug # 6841116. Changes in procedure update_service_type.
45
46 10. 29-April-2008 Changes by nprashar for bug #6636517 , added a NVL clause in join condition of cursor c_get_po_details.
47
48 11. 31-july-2008 Changes by nprashar for bug 7172723.
49 Issue : India ST Processing concurrent should consider Third Party Invoices and
50 update India Service Tax Credit register report
51 Fix : Modifed following procedure to use receipt information for Third party
52 invoices which do not have reference to PO
53 1 - get_doc_from_reference - Added logic for third party invoices which do not have references to PO
54 Modified cursor - c_get_refs_rec
55 Added cursor - c_get_source_type,c_get_line_number,c_get_doc_details,c_get_ra_line_srvtyp
56 12. 03-Jul-2009 CSahoo for bug#8648359, File Version 120.3.12000000.8
57 Added an AND clause in the code in the procedure extract_rgm_trxs.
58 13. 14-Jul-2009 CSahoo for bug#8451703, File Version 120.3.12000000.9
59 Modified the IF clause in the procedure get_document_details.
60
61 14. 01-OCT-2009 JMEENA for bug#8943349
62 Issue: India Service Tax Processing Concurrent not processing Standalone Invoices
63 1. Modified procedure get_document_details and added cursor c_get_standalone_inv_details, c_get_standalone_org_loc
64 and c_get_standalone_inv_line_tax
65 2. Modified procedure derrive_doc_from_ref and added code for standalone invoice.
66 3. Modified procedure extract_rgm_trxs and added code to populate the table
67 jai_trx_repo_extract_gt for standalone invoice.
68
69 15. 08-Oct-2009 CSahoo for bug#8965721, File Version 120.19.12010000.7
70 Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
71 Fix: Modified the cursor c_get_doc_details. Replaced rcv_transactions by jai_rcv_transactions
72
73 16. 09-Dec-2009 CSahoo for bug#9192752, File Version 120.19.12010000.8
74 Issue: INTCUS:SERVCIE TAX PROCESSING LOG IS SHOWN ERROR MESSAGE
75 FIX: Modified the code in the procedure derrive_doc_from_ref. Moved the CLOSE cursor code
76 into the IF block.
77
78 17. 18-Dec-2009 Modifiey by Jia for FP Bug#6691866, File Version 120.19.12010000.10
79 Issue:
80 India - Service tax processor in landing in error. Error thrown is as below:
81 ORA-01652: unable to extend temp segment by 32 in tablespace MTEMP
82
83 This was a forward port issue of the R11i Bug#6652557.
84 In the query written for the cursor c_get_pa_details in procedure get_document_details,
85 four tables are used.But the join conditions in the where clause are only three which might be
86 leading to a cartesain join on the queries fetched.
87 FIX:
88 Modified cursor c_get_pa_details, included an inline-select statement for the field document_line_desc
89 and removed the Table pa_draft_invoice_items from the list to avoid the Cartesian join.
90
91 18. 18-Feb-2010 Bgowrava for bug#9385880, File Version 120.19.12010000.11
92 Issue: INDIA - SERVICE TAX PROCESSING IS COMPLETED WITH WARNING
93 Fix: Added a IF condition in the procedure get_doc_from_reference
94
95 19. 19-FEB-2010 JMEENA for bug#9298508
96 Modified procedure get_document_details and added cursor c_get_ra_tax_amt_applied and c_get_ra_line_amt_applied
97 to fetch the applied line and tax amount same is updated in temp table to show on service tax repository review form.
98 20. 29-oct-2010 vkaranam for bug#10085619
99 Modified procedure get_document_details,extract_rgm_trxs,get_doc_from_reference to handle the taxes attached to tp invoice.
100 20. 19-Jan-2011 Xiao Lv for bug#10634960
101 Issue: INDIA SERVICE TAX PROCESSING CONCURRENT DOES NOT PROCESS SERVICE FOR STANDALONE
102 Fixed: Modfiy Cursor c_get_standalone_org_loc, pick up loc/org from parent invoice line.
103 21. 31-Jan-2011 Bug 10434986
104 Description:
105 + JAI_TRX_REPO_EXTRACT_GT is populated with incorrect Document Line ID
106 (Line Number of Tax Line is inserted into document line id column instead of Item Line Number)
107 + Tax Amount and Line Amount are populated from jai_cmn_document_taxes (Tax Amount column)
108 Hence if partial payment is made, the same is not considered and the whole tax amount
109 is shown in the Service Tax Repository Review window
110 Fix:
111 + Inserted Line Number of Item into Document Line ID
112 + Fetched the Payment amount from ap_invoice_payments_all. Derived the Repository Amount
113 from JAI_RGM_TRX_RECORDS. Sibtracted the Tax Amount from Payment amount and derived the Line Amount
114 22. 14-Mar-2011 Bug 11821537
115 Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
116 Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
117 i.e. Accounting changes to Accrual basis from Cash Basis
118 Fix: Fetched the Line amount and Tax Amount shown in the Repository Review Window from
119 cursor c_get_ra_trx_details instead of Receivable Application Table as the entire liability
120 arises at the point of invoices instead of cash receipt/credit memo application
121
122 22. 20-may-2011 vkaranam for bug#12560704
123 Issue:Service tax processing concurrent is completing in warning
124 charge account not defined for tax type
125 Fix: modified c_get_rcv_details cursor in geT_document_details procedure.
126 23. 23-MAY-2011 Bug 11932841
127 Description: Tax and Line amount in repository displays the total amount for the entire
128 document in each line
129 Fix: Get the Tax and Line amount per Line for the AR Invoice
130 24. 24-May-2011 Xiao for POT change, reg bug#12533434.
131 Fixed: Compare the invoice accounting date with POT implementation date, so that it can process
132 transactions on cash basis as well as accrual basis.
133
134 25. 29-May-2011 Xiao for POT change, reg bug#12533434
135 Fixed: In cursor c_get_repo_amount, change the function as:
136 SELECT abs(SUM(NVL(jrec.TRX_DEBIT_AMOUNT,0))-SUM(NVL(jrec.TRX_CREDIT_AMOUNT,0)));
137 Cursor get_ar_gl_date_cur is changed to use link_to_cust_trx_line_id get accounting date
138 from 'REV' lines.
139
140 26. 06-Sep-2011 Bug 12902363
141 Description: PO Details are shown in Repository Review form for Receipt Matched Invoice
142 Fix: Fetched Transaction Number, Transaction Date, Quantity and Tax Details from the Receipt
143 27. 22-Aug-2011 Xiao for POT Phase III changes, reg bug#12895841
144 Fixed: Add logic for showing transactions for AP reversal, AP claim, AR reversal, AR claim.
145 (add by chong.lei pot code port)
146 35. 11-nov-2011 vkaranam for bug#13323182
147 Issue:Service tax is not getting processed on cash basis after the patch(12805386)
148 application.
149 fix:
150 + If Accounting Basis has to be CASH, the cursor that fetches POT date will
151 return NULL
152 + Hence when comparing the POT Date for any logic that needs to go by CASH,
153 we need to compare with NULL also.Added the OR ld_St_accrual_date is null for cash basis transaction.
154 36. 14-dec-2011 Qiong for Bug 13456083 - PRE POT INVOICE UPDATE REPOSITORY INCORRECT
155 37. 19-dec-2011 Qiong for Bug 13405553 - Update repository imcorrect for payables reversal claim source
156 38. 20-dec-2011 Qinglei for Bug 13405591 - Repository UI show line amount and tax amount as 0 after invoice cancellation
157 39. 27-Dec-2011 Qinglei for Bug 13531399 - For Pre-POT AR invoice, tax amount is null after receipt application
158 40. 04-Jan-2012 Qiong for bug#13535329 ST invoice number on ar trnx can't be updated to st repository
159 42. 05-Jan-2012 Qinglei for Bug 13556198 - Repository UI show doesn't show line amount for receipt application/unapplication
160 43. 12-Jan-2012 Qiong for bug#13555753 ST proceesing report and repository should use reversal trx date and gl date
161 44. 18-Jan-2012 Qiong for bug#13597785 - TRX DATE IN REPOSITORY FOR AR TRX FROM PB SHOULD BE TRX'S TRX DATE
162 45. 18-JAN-2012 vkaranam for bug#13375399
163 issue:service distribute in entries are not appearing the service tax repository review form.
164 fix:
165 changes are done in extract_Rgm_trxs,update_service_type procedures
166 to include service_distribute_in entries.
167 46. 23-Jan-2012 amandali for bug 13430127
168 Issues Fixed: +PO/Receipt matched invoices should have AP invoice reference in repository review form
169 Modified the procedure get_document_details by adding cursors c_get_ap_inv_payment,c_get_repo_amount
170 and commented code for PURCHASING and RECEIVING and added code for the same to get details from AP tables.
171 Also made the similar changes in derrive_doc_from_ref.
172 47. 03-Feb-2012 Qiong for bug13598199 Issues when update repository for AP CLAIM
173 48. 09-FEB-2012 Wenqiong for bug13462951 line amount not correct in service repository
174 49. 28-Feb-2012 Qinglei for bug#13741544
175 Issue Fixed: For Pre-POT AR transaction after receipt unapplication, repository show line amount and
176 tax amount as zero.
177
178 50. 29-JAN-13 Bug No : 14341945
179 Description : stx.servicecharge:service:foreign currency invoice not converted to inr in repos
180 FIX: chaged the col names trx_credit_amount,trx_debit_amount to credit_amount, debit_amount
181
182 */
183 -------------------------------------------------------------------------------------------------
184 -------------*/
185
186 /*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
187
188 /** Package level variables used in debug package*/
189 lv_object_name jai_cmn_debug_contexts.log_context%type default 'JAI_TRX_REPO_EXTRACT_PKG';
190 lv_member_name jai_cmn_debug_contexts.log_context%type;
191 lv_context jai_cmn_debug_contexts.log_context%type;
192
193 --
194 -- Global variables used throught the package
195 --
196 lv_user_id fnd_user.user_id%type default fnd_global.user_id;
197 lv_login_id fnd_logins.login_id%type default fnd_global.login_id;
198
199 function get_settled_service_type
200 ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
201 , p_document_id jai_trx_repo_extract_gt.document_id%type
202 , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
203 )
204 return varchar2;
205
206 procedure set_debug_context
207 is
208
209 begin
210 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
211 end set_debug_context;
212 --Add by chong.lei for POT code port begin
213 --Add by Xiao for POT Phase III change, reg bug#12895841, begin
214 FUNCTION get_gl_date( pv_source jai_trx_repo_extract_gt.transaction_source%TYPE,
215 pv_source_trx_type jai_rgm_trx_records.source_trx_type%TYPE,
216 pn_document_id jai_trx_repo_extract_gt.document_id%TYPE,
217 pn_source_document_id jai_rgm_trx_records.source_document_id%TYPE,
218 pn_organization_id NUMBER,
219 pn_location_id NUMBER,
220 pv_source_table_name jai_rgm_trx_records.source_table_name%TYPE default NULL,
221 pv_document_line_id VARCHAR2 default NULL) RETURN DATE IS
222 --Added by Qinglei 21-Dec-2011 for bug#13405591
223 CURSOR get_reversal_date_cur IS
224 SELECT date_of_reversal
225 FROM jai_st_invoice_reversal
226 WHERE invoice_id = pn_document_id
227 AND SOURCE = substr(pv_source, 1, 2);
228
229 CURSOR get_payment_date_cur IS
230 SELECT accounting_date
231 FROM ap_invoice_payments_all
232 WHERE invoice_payment_id = pn_source_document_id;
233
234 CURSOR get_receipt_date_cur IS
235 SELECT gl_date
236 FROM ar_receivable_applications_all
237 WHERE receivable_application_id = pn_source_document_id;
238
239 CURSOR get_dist_date_cur IS
240 SELECT accounting_date
241 FROM ap_invoice_distributions_all
242 WHERE invoice_distribution_id = pn_source_document_id;
243
244 CURSOR get_ar_dist_date_cur IS
245 SELECT gl_date
246 FROM ra_cust_trx_line_gl_dist_all
247 WHERE customer_trx_id = pn_document_id
248 AND account_class = 'REV';
249
250 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
251 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
252 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
253
254 CURSOR c_get_st_accrual_date(p_organization_id NUMBER, p_location_id NUMBER) IS
255 select to_date(attribute_value, 'DD/MM/YYYY')
256 from JAI_RGM_ORG_REGNS_V
257 where regime_id = (SELECT regime_id
258 FROM jai_rgm_definitions
259 where regime_code = jai_constants.service_regime
260 )
261 and organization_id = p_organization_id
262 and location_id = p_location_id
263 AND attribute_code = 'EFF_DATE_ST_PT'
264 AND attribute_type_code = 'OTHERS'
265 AND registration_type = 'OTHERS'
266 AND (NOT EXISTS
267 (select '1'
268 from JAI_RGM_ORG_REGNS_V
269 where regime_id = (SELECT regime_id
270 FROM jai_rgm_definitions
271 where regime_code = jai_constants.service_regime
272 )
273 and attribute_code IN 'INV_ORG_CLASSIFICATION'
274 and attribute_value <> 'ORGANIZATION'
275 and organization_id = p_organization_id
276 and location_id = p_location_id)
277 OR
278 NOT EXISTS
279 (select '1'
280 from JAI_RGM_ORG_REGNS_V
281 where regime_id = (SELECT regime_id
282 FROM jai_rgm_definitions
283 where regime_code = jai_constants.service_regime
284 )
285 and attribute_code IN 'SERVICE TYPE'
286 and attribute_value <> 'OTHER'
287 and organization_id = p_organization_id
288 and location_id = p_location_id)
289 );
290
291 --Added by Wenqiong begin
292 CURSOR get_cash_receipt_gl_date_cur IS
293 SELECT
294 GL_DATE
295 FROM jai_ar_cash_receipts_all
296 WHERE cash_receipt_id = pn_document_id;
297 --Added by Wenqiong end
298
299 --Added by Qiong for bug13555753 begin
300 --------------------------------------
301 CURSOR get_rev_receipt_gl_date_cur IS
302 SELECT acrh.gl_date gl_date
303 FROM ar_cash_receipts_all acra,
304 ar_cash_receipt_history_all acrh
305 WHERE acra.cash_receipt_id = pn_document_id
306 AND acrh.cash_receipt_id = acra.cash_receipt_id
307 AND acrh.status = 'REVERSED';
308 ---------------------------------------
309 --Added by Qiong for bug13555753 end
310
311 ld_gl_date DATE;
312 ld_pot_date DATE;
313
314 /*Added by Qinglei 21-Dec-2011 for bug#1340559 begin */
315 CURSOR c_get_invoice_discarded_flag
316 IS
317 SELECT nvl(discarded_flag,'N')
318 FROM ap_invoices_all aia,
319 ap_invoice_lines_all ail
320 WHERE aia.invoice_id = ail.invoice_id
321 AND aia.invoice_id = pn_document_id
322 AND ail.line_number = pv_document_line_id;
323
324 CURSOR get_discarded_dist_date_cur IS
325 SELECT accounting_date
326 FROM ap_invoice_distributions_all
327 WHERE invoice_id = pn_document_id
328 AND invoice_line_number = pv_document_line_id
329 AND nvl(reversal_flag,'N') = 'Y'
330 AND parent_reversal_id IS NOT NULL;
331
332 lv_discarded_flag ap_invoice_lines_all.discarded_flag%TYPE;
333 /*Added by Qinglei 21-Dec-2011 for bug#1340559 end */
334 --Added by Qiong for reverse charge bug#16001407 begin
335 CURSOR c_get_settle_payment_date
336 IS SELECT payment_date
337 FROM JAI_RGM_SETTLEMENTS
338 WHERE settlement_id = pn_source_document_id;
339 --Added by Qiong for reverse charge bug#16001407 end
340
341 BEGIN
342 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/
343 OPEN c_get_st_accrual_date(pn_organization_id, pn_location_id);
344 FETCH c_get_st_accrual_date INTO ld_pot_date;
345 CLOSE c_get_st_accrual_date;
346
347 IF pv_source = 'AP' THEN
348 if pv_source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL' then
349 OPEN get_dist_date_cur;
350 FETCH get_dist_date_cur INTO ld_gl_date;
351 CLOSE get_dist_date_cur;
352
353 --Added by Qinglei on 21-Dec-2011 for bug#13405591 begin
354 OPEN c_get_invoice_discarded_flag;
355 FETCH c_get_invoice_discarded_flag INTO lv_discarded_flag;
356 CLOSE c_get_invoice_discarded_flag;
357
358 IF lv_discarded_flag = 'Y' THEN
359 OPEN get_discarded_dist_date_cur;
360 FETCH get_discarded_dist_date_cur INTO ld_gl_date;
361 CLOSE get_discarded_dist_date_cur;
362 END IF;
363 --Added by Qinglei on 21-Dec-2011 for bug#13405591 end
364
365 --added or ld_pot_date is null for bug#13323182
366 --Commented by Qiong for bug#13456083 begin
367 -------------------------------------------------------
368 /*
369 IF ld_gl_date < ld_pot_date or ld_pot_date is null THEN
370 ld_gl_date := NULL;
371 END IF;
372 */
373 -------------------------------------------------------
374 --Commented by Qiong for bug#13456083 end
375 --Added by Qiong for bug#13456083 begin
376 ----------------------------------------------
377 elsif pv_source_table_name = 'AP_INVOICE_PAYMENTS_ALL' then
378 OPEN get_payment_date_cur;
379 FETCH get_payment_date_cur INTO ld_gl_date;
380 CLOSE get_payment_date_cur;
381 --Added by Qiong for reverse charge bug#16001407 begin
382 ELSIF pv_source_table_name = 'JAI_RGM_SETTLEMENTS' THEN
383 OPEN c_get_settle_payment_date;
384 FETCH c_get_settle_payment_date INTO ld_gl_date;
385 CLOSE c_get_settle_payment_date;
386 --Added by Qiong for reverse charge bug#16001407 end
387 end if;
388 -----------------------------------------------
389 --Added by Qiong for bug#13456083 end
390
391
392 ELSIF pv_source = 'AR' THEN
393 --Added by Wenqiong for advanced receipts Begin
394 IF pv_source_trx_type = jai_constants.trx_type_adv_rcpts THEN --changed by qiong for bug13555753
395 OPEN get_cash_receipt_gl_date_cur;
396 FETCH get_cash_receipt_gl_date_cur INTO ld_gl_date;
397 CLOSE get_cash_receipt_gl_date_cur;
398
399 --Added by Qiong for bug13555753 begin
400 --------------------------------------
401 ELSIF pv_source_trx_type = jai_constants.trx_type_adv_rvs THEN
402 --Modified by Qinglei for bug#13741544
403 OPEN get_rev_receipt_gl_date_cur;
404 FETCH get_rev_receipt_gl_date_cur INTO ld_gl_date;
405 CLOSE get_rev_receipt_gl_date_cur;
406 --------------------------------------
407 --Added by Qiong for bug13555753 end
408
409 ELSIF pv_source_trx_type IN (jai_constants.trx_type_adv_app,jai_constants.trx_type_adv_unapp) THEN
410 OPEN get_receipt_date_cur;
411 FETCH get_receipt_date_cur INTO ld_gl_date;
412 CLOSE get_receipt_date_cur;
413
414 ELSE--Added by Wenqiong for advanced receipts End
415
416 OPEN get_ar_dist_date_cur;
417 FETCH get_ar_dist_date_cur INTO ld_gl_date;
418 CLOSE get_ar_dist_date_cur;
419 --added or ld_pot_date is null for bug#13323182
420 --Commented by Qiong for bug#13456083
421 ------------------------------------------------------
422 /*
423 IF ld_gl_date < ld_pot_date or ld_pot_date is null THEN
424 ld_gl_date := NULL;
425 END IF;
426 */
427 -------------------------------------------------------
428 --Commented by Qiong for bug#13456083 end
429 END IF;
430
431 ELSIF pv_source = 'AP_CLAIM' THEN
432 OPEN get_payment_date_cur;
433 FETCH get_payment_date_cur INTO ld_gl_date;
434 CLOSE get_payment_date_cur;
435 ELSIF pv_source = 'AR_CLAIM' THEN
436 OPEN get_receipt_date_cur;
437 FETCH get_receipt_date_cur INTO ld_gl_date;
438 CLOSE get_receipt_date_cur;
439 ELSIF upper(substr(pv_source, 4)) = 'REVERSAL' THEN
440 OPEN get_reversal_date_cur;
441 FETCH get_reversal_date_cur INTO ld_gl_date;
442 CLOSE get_reversal_date_cur;
443 END IF;
444 RETURN ld_gl_date;
445
446 END get_gl_date;
447 --Add by Xiao for POT Phase III change, reg bug#12895841, end
448 --Add by chong.lei for POT code port end
449 /*------------------------------------------------------------------------------------------------------------*/
450 procedure extract_rgm_trxs
451 ( p_regime_code jai_rgm_trx_records.regime_code%type
452 , p_organization_id jai_rgm_trx_records.organization_id%type default null
453 , p_location_id jai_rgm_trx_records.location_id%type default null
454 , p_from_trx_date date default null
455 , p_to_trx_date date default null
456 , p_source jai_rgm_trx_records.source%type default null
457 , p_query_settled_flag varchar2 default 'N'
458 , p_query_only_null_srvtype varchar2 default 'N'
459 , p_process_message OUT NOCOPY varchar
460 , p_process_flag OUT NOCOPY varchar2
461 )
462 as
463 cursor c_get_repo_recs
464 is
465 select (recs.repository_id) repository_id
466 , nvl(refs.reference_id, recs.reference_id) reference_id
467 , refs.invoice_id
468 , refs.item_line_id
469 ,refs.line_id --added for bug#10085619 ,refers to the invoice dist id of the taxes
470 , recs.source
471 , recs.source_trx_type source_trx_type--Added by Wenqiong for Advanced Receipts
472 , recs.service_type_code
473 , nvl(recs.organization_id, recs.inv_organization_id) organization_id
474 , recs.location_id
475 , (nvl(credit_amount,0) + nvl(debit_amount,0)) repository_tax_amt -- modified for the bug 14341945
476 , recs.organization_type
477 , recs.source_document_id
478 , recs.invoice_no --Added by Qiong for Advanced Receipts
479 , recs.source_table_name --Added by Qiong for bug#13456083
480 from jai_rgm_trx_refs refs
481 , jai_rgm_trx_records recs
482 where recs.reference_id = refs.reference_id (+)
483 and ( p_organization_id is null
484 or (recs.organization_id = p_organization_id)
485 )
486 and (p_location_id is null or recs.location_id = p_location_id )
487 and trunc(transaction_date) between nvl (p_from_trx_date, trunc(transaction_date)) and nvl (p_to_trx_date, trunc(transaction_date))
488 and recs.regime_code = p_regime_code
489 and ( (p_query_settled_flag = 'N' and (recs.settlement_id is null))
490 or (p_query_settled_flag = jai_constants.yes)
491 )
492 and ( (p_query_only_null_srvtype = 'Y' and (recs.service_type_code is null))
493 or (p_query_only_null_srvtype = 'N')
494 )
495 and (p_source is null or p_source = recs.source )
496 and recs.organization_type = 'IO'
497 and recs.source in ('AP'
498 ,'AR'
499 ,'MANUAL'
500 ,'SERVICE_DISTRIBUTE_OUT'
501 ,'SERVICE_DISTRIBUTE_IN' /*added SERVICE_DISTRIBUTE_IN for bug#13375399*/
502 --Add by Chong.Lei for POT code port begin
503 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
504 , 'AP_REVERSAL'
505 , 'AP_CLAIM'
506 , 'AR_REVERSAL'
507 , 'AR_CLAIM'
508 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
509 --Add by Chong.Lei for POT code port end
510 );
511 --Added by Qinglei for bug#13405591 13-Dec-2011 begin
512 cursor c_get_repository_tax_amount(pv_source_trx_type VARCHAR2
513 ,pv_item_line_id VARCHAR2
514 ,pn_invoice_id NUMBER)
515 is
516 select
517 sum((nvl(credit_amount,0) + nvl(debit_amount,0))) repository_tax_amt -- modified for the bug 14341945
518 from jai_rgm_trx_records recs,
519 jai_rgm_trx_refs refs
520 where recs.reference_id = refs.reference_id (+)
521 and recs.SOURCE_TRX_TYPE = pv_source_trx_type
522 and refs.item_line_id = pv_item_line_id
523 and refs.invoice_id = pn_invoice_id;
524
525 --Added by Qiong.liu for bug#16001407 start
526 cursor c_get_repository_tax_amount_o(pv_source_trx_type VARCHAR2
527 ,pv_item_line_id VARCHAR2
528 ,pn_invoice_id NUMBER
529 )
530 is
531 select
532 sum((nvl(credit_amount,0) + nvl(debit_amount,0))) repository_tax_amt--Modified by Junjian for bug#14341945 on 2-Aug-2012
533 from jai_rgm_trx_records recs,
534 jai_rgm_trx_refs refs
535 where recs.reference_id = refs.reference_id (+)
536 and recs.SOURCE_TRX_TYPE = pv_source_trx_type
537 and refs.item_line_id = pv_item_line_id
538 and refs.invoice_id = pn_invoice_id
539 ;
540 --Added by Qiong.liu for bug#16001407 end
541
542 cursor c_get_invoice_discarded_flag(pn_invoice_id NUMBER
543 ,pv_invoice_line_num VARCHAR2)
544 IS
545 SELECT nvl(discarded_flag,'N')
546 FROM ap_invoices_all aia,
547 ap_invoice_lines_all ail
548 WHERE aia.invoice_id = ail.invoice_id
549 AND aia.invoice_id = pn_invoice_id
550 AND ail.line_number = pv_invoice_line_num;
551
552 lv_discarded_flag ap_invoice_lines_all.discarded_flag%TYPE;
553 --Added by Qinglei for bug#13405591 13-Dec-2011 end
554
555 cursor c_get_organization_name (cp_organization_id hr_organization_units.organization_id%type)
556 is
557 select name
558 from hr_organization_units
559 where organization_id = cp_organization_id;
560
561 cursor c_get_location_name (cp_location_id hr_locations_all.location_id%type)
562 is
563 select description
564 from hr_locations_all
565 where location_id = cp_location_id;
566
567
568
569 cursor c_get_settled_doc_service_typ (cp_source jai_rgm_trx_refs.source%type
570 ,cp_invoice_id jai_rgm_trx_refs.invoice_id%type
571 ,cp_line_id jai_rgm_trx_refs.line_id%type
572 )
573 is
574 select recs.service_type_code
575 from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
576 where recs.reference_id = refs.reference_id
577 and refs.invoice_id = cp_invoice_id
578 and refs.line_id = cp_line_id
579 and refs.source = cp_source
580 and recs.settlement_id is not null
581 and recs.service_type_code is not null
582 and rownum = 1;
583
584 cursor c_get_src_rec ( cp_transfer_id jai_rgm_dis_src_hdrs.transfer_id%type
585 , cp_party_type jai_rgm_dis_src_hdrs.party_type%type
586 , cp_party_id jai_rgm_dis_src_hdrs.party_id%type
587 )
588 is
589 select transfer_number
590 ,transaction_date
591 ,party_id
592 ,location_id
593 from jai_rgm_dis_src_hdrs
594 where party_type = cp_party_type
595 and party_id = cp_party_id
596 and transfer_id = cp_transfer_id;
597
598 cursor c_get_dest_rec( cp_transfer_id jai_rgm_dis_des_hdrs.transfer_id%type
599 , cp_party_type jai_rgm_dis_des_hdrs.destination_party_type%type
600 , cp_party_id jai_rgm_dis_des_hdrs.destination_party_id%type
601 )
602 is
603 select transfer_number
604 ,creation_date transaction_date
605 ,destination_party_id
606 ,location_id
607 from JAI_RGM_DIS_DES_HDRS
608 where destination_party_type = cp_party_type
609 and destination_party_id = cp_party_id
610 and transfer_id = cp_transfer_id;
611
612 r_src_rec c_get_src_rec%rowtype;
613 r_dest_rec c_get_dest_rec%rowtype;
614
615 cursor c_get_man_trx_rec (cp_trx_number jai_rgm_manual_trxs.transaction_number%type)
616 is
617 select party_type
618 , party_id
619 , transaction_date
620 , remarks
621 , invoice_number
622 from JAI_RGM_MANUAL_TRXS
623 where transaction_number = cp_trx_number;
624
625 r_man_trx_rec c_get_man_trx_rec%rowtype;
626
627 cursor c_get_vendor_name (cp_vendor_id po_vendors.vendor_id%type)
628 is
629 select vendor_name
630 from po_vendors
631 where vendor_id = cp_vendor_id;
632
633 cursor c_get_customer_name (cp_party_id po_vendors.vendor_id%type)
634 is
635 select hzp.party_name
636 from hz_cust_accounts hzca
637 ,hz_parties hzp
638 where hzca.cust_account_id = cp_party_id
639 and hzp.party_id = hzca.party_id;
640
641 cursor c_st_transprt_inv_details(cp_invoice_id ap_invoices_all.invoice_id%type)/* Changes by nprashar , Forward porting from bug 7172723*/
642 is
643 select
644 aia.invoice_num,
645 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,
646 pha.segment1 po_num,
647 aia.invoice_date
648 from
649 po_headers_all pha,
650 rcv_transactions rt,
651 rcv_shipment_headers rsh,
652 ap_invoices_all aia
653 where
654 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
655 rsh.shipment_header_id=rt.shipment_header_id AND
656 rt.po_header_id=pha.po_header_id AND
657 pha.org_id=aia.org_id AND
658 aia.invoice_id=cp_invoice_id
659 and rownum=1;
660
661 cursor c_st_transprt_party_details(cp_invoice_id ap_invoices_all.invoice_id%type) /* Changes by nprashar , Forward porting from bug 7172723*/
662 IS
663 select pv.vendor_name,pv.vendor_id from
664 jai_rgm_trx_refs jrtr,
665 po_vendors pv
666 where invoice_id=cp_invoice_id
667 and pv.vendor_id=jrtr.party_id
668 and rownum=1;
669
670 lv_party_name hz_parties.party_name%type;
671
672 rec_st_transprt_inv_details c_st_transprt_inv_details%rowtype;/* Changes by nprashar , Forward porting from bug 7172723 */
673 rec_st_transprt_party_details c_st_transprt_party_details%rowtype;/* Changes by nprashar , Forward porting from bug 7172723 */
674 lv_service_type_code jai_rgm_trx_records.service_type_code%type;
675
676 lr_trx_repo_extract jai_trx_repo_extract_gt%rowtype;
677 lv_organization_name hr_organization_units.name%type;
678 lv_location_name hr_locations_all.description%type;
679 ln_reg_id number;
680 --start additions for bug#10085619
681 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)
682 is
683 select inv_dist_id,
684 line_num
685 from
686 (select
687 INVOICE_DISTRIBUTION_ID inv_dist_id,
688 row_number() over(ORDER BY INVOICE_DISTRIBUTION_ID) line_num
689 from ap_invoice_distributions_all
690 where INVOICE_ID=p_invoice_id
691 )
692 where inv_dist_id=p_line_id;
693
694 lr_line_number number;
695 lr_inv_dist_id ap_invoice_distributions_all.INVOICE_DISTRIBUTION_ID%type;
696 --Add by Wenqiong for Advanced Receipt begin
697 -------------------------------------------------
698 ln_refer_invoice_id NUMBER;
699 ln_refer_item_line_id NUMBER;
700 --Add by Wenqiong for Advanced Receipt end
701 begin
702
703 lv_member_name := 'EXTRACT_RGM_TRXS';
704 set_debug_context;
705 p_process_flag := jai_constants.SUCCESSFUL;
706 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
707 , pn_reg_id => ln_reg_id
708 );
709
710 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Call Parameters:' || fnd_global.local_chr(10) ||
711 'p_regime_code =' ||p_regime_code || fnd_global.local_chr(10) ||
712 'p_organization_id=' ||p_organization_id || fnd_global.local_chr(10) ||
713 'p_location_id =' ||p_location_id || fnd_global.local_chr(10) ||
714 'p_from_trx_date =' ||p_from_trx_date || fnd_global.local_chr(10) ||
715 'p_to_trx_date =' ||p_to_trx_date || fnd_global.local_chr(10) ||
716 'p_query_settled_flag='||p_query_settled_flag
717 );
718
719 for r_repo_recs in c_get_repo_recs
720 loop
721
722 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Begin for r_repo_recs in c_get_repo_recs loop');
723 lr_trx_repo_extract := null;
724
725 jai_cmn_debug_contexts_pkg.print (ln_reg_id, '1. r_repo_recs.service_type_code ='||r_repo_recs.service_type_code );
726
727 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)
728 ||',r_repo_recs.source='||r_repo_recs.source );
729 lr_trx_repo_extract.gl_date := NULL;--Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011 --Added by Chong.Lei for POT code port
730
731 --if r_repo_recs.source in ('AP','AR') then --Commented by Chong.Lei for POT code port
732 --Added by Chong.Lei for POT code port begin
733 if r_repo_recs.source in ('AP','AR'
734 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
735 , 'AP_REVERSAL'
736 , 'AP_CLAIM'
737 , 'AR_REVERSAL'
738 , 'AR_CLAIM'
739 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
740 ) then
741 --Added by Chong.Lei for POT code port end
742 --Add by Wenqiong for Advanced Receipt begin
743 --------------------------------------------
744 IF r_repo_recs.source_trx_type IN
745 (jai_constants.trx_type_adv_app,jai_constants.trx_type_adv_unapp) THEN
746 ln_refer_invoice_id := r_repo_recs.source_document_id;
747 ln_refer_item_line_id := r_repo_recs.source_document_id;
748 ELSE
749 ln_refer_invoice_id := r_repo_recs.invoice_id;
750 ln_refer_item_line_id := r_repo_recs.item_line_id;
751 END IF;
752 --Comment by qiong for bug1353539 begin
753 ---------------------------------------------------------
754 /*
755 if r_repo_recs.source = 'AR' then
756 lr_trx_repo_extract.st_inv_number := r_repo_recs.invoice_no;--Add by Qiong For Advanced Receipts
757 end if;
758 */
759 ---------------------------------------------------------
760 --Comment by qiong for bug1353539 end
761
762 -----------------------------------------------
763 --Add by Wenqiong for Advanced Receipt end
764
765 jai_trx_repo_extract_pkg.derrive_doc_from_ref
766 ( p_reference_source => r_repo_recs.source
767 , p_source_trx_type => r_repo_recs.source_trx_type
768 , p_reference_invoice_id => ln_refer_invoice_id --Changed by Wenqiong for advanced receipt
769 , p_reference_item_line_id => ln_refer_item_line_id--Changed by Wenqiong for advanced receipt
770 , p_trx_repo_extract_rec => lr_trx_repo_extract
771 , p_process_message => p_process_message
772 , p_process_flag => p_process_flag
773 );
774 if p_process_flag <> jai_constants.SUCCESSFUL then
775 return;
776 end if;
777 --Added by qiong for bug1353539 begin
778 ---------------------------------------------------------
779 if r_repo_recs.source = 'AR' then
780 lr_trx_repo_extract.st_inv_number := r_repo_recs.invoice_no;
781 end if;
782 ---------------------------------------------------------
783 --Added by qiong for bug1353539 end
784
785 --Added by Chong.Lei for POT code port begin
786 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
787 /*Bug 12805386 - Added Organization ID and Location ID parameter as POT is modifiable at Inventory Organization level*/
788 lr_trx_repo_extract.gl_date := get_gl_date(r_repo_recs.source,
789 r_repo_recs.source_trx_type ,--Added by Wenqiong
790 r_repo_recs.invoice_id,
791 r_repo_recs.source_document_id,
792 r_repo_recs.organization_id,
793 r_repo_recs.location_id,
794 r_repo_recs.source_table_name,
795 r_repo_recs.item_line_id); --Added by Qiong for bug13456083
796 --Added by Qinglei for bug#13405591
797
798 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
799 --Added by Chong.Lei for POT code port end
800
801 /* { Changes by nprashar , Forward porting from bug 7172723 */
802 if lr_trx_repo_extract.document_id is null
803 --added the following and condition for bug#8648359
804 and lr_trx_repo_extract.transaction_source not in ('RECEIVABLES', 'PROJECTS', 'ORDER MANAGEMENT','STANDALONE_INVOICE') then
805 /* if document_id is null here this is a Serv.tax invoice for transporters*/
806
807 open c_st_transprt_inv_details(r_repo_recs.invoice_id);
808 fetch c_st_transprt_inv_details into rec_st_transprt_inv_details;
809 close c_st_transprt_inv_details;
810
811 open c_st_transprt_party_details(r_repo_recs.invoice_id);
812 fetch c_st_transprt_party_details into rec_st_transprt_party_details;
813 close c_st_transprt_party_details;
814
815
816 lr_trx_repo_extract.transaction_source := 'Payables' ;
817 lr_trx_repo_extract.party_name := rec_st_transprt_party_details.vendor_name;
818 lr_trx_repo_extract.party_id := rec_st_transprt_party_details.vendor_id;
819 lr_trx_repo_extract.document_number := rec_st_transprt_inv_details.invoice_num;
820 lr_trx_repo_extract.document_date := rec_st_transprt_inv_details.invoice_date;
821 lr_trx_repo_extract.document_id := r_repo_recs.invoice_id;
822 lr_trx_repo_extract.document_line_desc :='Service Tax - for Transporters -'||'PO-Number: '||rec_st_transprt_inv_details.po_num;
823 lr_trx_repo_extract.organization_id := r_repo_recs.organization_id;
824 lr_trx_repo_extract.location_id := r_repo_recs.location_id;
825 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt;
826
827 end if;
828 /* Changes by nprashar , Forward porting from bug 7172723 }*/
829
830 elsif r_repo_recs.source IN ('SERVICE_DISTRIBUTE_OUT') then
831
832 lr_trx_repo_extract.document_line_desc := 'Service Distribution Transaction' ;
833 lr_trx_repo_extract.transaction_source := r_repo_recs.source ;
834
835
836 if r_repo_recs.source = 'SERVICE_DISTRIBUTE_OUT' then
837 open c_get_src_rec ( cp_transfer_id => r_repo_recs.source_document_id
838 , cp_party_type => r_repo_recs.organization_type
839 , cp_party_id => r_repo_recs.organization_id
840 );
841 fetch c_get_src_rec into r_src_rec;
842 close c_get_src_rec ;
843
844 lr_trx_repo_extract.document_number := r_src_rec.transfer_number;
845 lr_trx_repo_extract.document_date := r_src_rec.transaction_date;
846 lr_trx_repo_extract.document_id := r_repo_recs.source_document_id;
847 lr_trx_repo_extract.organization_id := r_src_rec.party_id ;
848 lr_trx_repo_extract.location_id := r_src_rec.location_id ;
849 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt ;
850
851 end if; --> r_repo_recs.source = 'SERVICE_DISTRIBUTE_OUT'
852 --start additions for bug#13375399
853 elsif r_repo_recs.source IN ('SERVICE_DISTRIBUTE_IN') then
854
855 lr_trx_repo_extract.document_line_desc := 'Service Distribution Transaction' ;
856 lr_trx_repo_extract.transaction_source := r_repo_recs.source ;
857
858
859 if r_repo_recs.source = 'SERVICE_DISTRIBUTE_IN' then
860 open c_get_dest_rec ( cp_transfer_id => r_repo_recs.source_document_id
861 , cp_party_type => r_repo_recs.organization_type
862 , cp_party_id => r_repo_recs.organization_id
863 );
864 fetch c_get_dest_rec into r_dest_rec;
865 close c_get_dest_rec ;
866
867 lr_trx_repo_extract.document_number := r_dest_rec.transfer_number;
868 lr_trx_repo_extract.document_date := r_dest_rec.transaction_date;
869 lr_trx_repo_extract.document_id := r_repo_recs.source_document_id;
870 --Qiong changed it from r_dest_rec.party_id to r_dest_rec.destination_party_id
871 --Due to compiling error in version 120.73
872 lr_trx_repo_extract.organization_id := r_dest_rec.destination_party_id ;
873 lr_trx_repo_extract.location_id := r_dest_rec.location_id ;
874 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt ;
875
876 end if; --> r_repo_recs.source = 'SERVICE_DISTRIBUTE_IN'
877 --end additions for bug#13375399
878
879 elsif r_repo_recs.source = 'MANUAL' then
880
881 open c_get_man_trx_rec (cp_trx_number => r_repo_recs.source_document_id);
882 fetch c_get_man_trx_rec into r_man_trx_rec ;
883 close c_get_man_trx_rec ;
884
885 if r_man_trx_rec.party_type in ('VENDOR','AUTHORITY') then
886 open c_get_vendor_name (cp_vendor_id => r_man_trx_rec.party_id);
887 fetch c_get_vendor_name into lv_party_name;
888 close c_get_vendor_name ;
889 elsif r_man_trx_rec.party_type = 'CUSTOMER' then
890 open c_get_customer_name (cp_party_id => r_man_trx_rec.party_id);
891 fetch c_get_customer_name into lv_party_name;
892 close c_get_customer_name ;
893 end if;
894
895 lr_trx_repo_extract.transaction_source := r_repo_recs.source ;
896 lr_trx_repo_extract.party_name := lv_party_name;
897 lr_trx_repo_extract.document_number := r_repo_recs.source_document_id;
898 lr_trx_repo_extract.document_date := r_man_trx_rec.transaction_date;
899 lr_trx_repo_extract.document_id := r_repo_recs.source_document_id;
900 lr_trx_repo_extract.document_line_desc := nvl(r_man_trx_rec.remarks , 'Service Tax - Manual Transaction')
901 || rtrim('/'||r_man_trx_rec.invoice_number,'/');
902 lr_trx_repo_extract.organization_id := r_repo_recs.organization_id;
903 lr_trx_repo_extract.repository_tax_amt := r_repo_recs.repository_tax_amt;
904
905 end if; --> r_repo_recs.source
906
907 lr_trx_repo_extract.transaction_repository_id := r_repo_recs.repository_id ;
908 lr_trx_repo_extract.transaction_reference_id := r_repo_recs.reference_id ;
909 lr_trx_repo_extract.repository_source := r_repo_recs.source ;
910 lr_trx_repo_extract.repository_invoice_id := r_repo_recs.invoice_id ;
911 lr_trx_repo_extract.repository_line_id := r_repo_recs.item_line_id ;
912 lr_trx_repo_extract.service_type_code := r_repo_recs.service_type_code ;
913 lr_trx_repo_extract.source_trx_type := r_repo_recs.source_trx_type ;--Add by Wenqiong
914
915
916
917 --start additions for bug#10085619
918 --Added by Chong.Lei for POT code port begin
919 --if lr_trx_repo_extract.transaction_source ='STANDALONE_INVOICE'
920 --if lr_trx_repo_extract.transaction_source IN ('STANDALONE_INVOICE','AP_REVERSAL', 'AP_CLAIM')--Add by Xiao for POT Phase III, reg bug#12895841.
921 if lr_trx_repo_extract.transaction_source IN ('STANDALONE_INVOICE','RECEIVING','PURCHASING', 'AP_REVERSAL', 'AP_CLAIM')--Added by Qiong for reverse charge bug#16001407
922 then
923 /*
924 open c_get_line_number(r_repo_recs.invoice_id ,r_repo_recs.line_id);
925 fetch c_get_line_number into lr_inv_dist_id,lr_line_number;
926 close c_get_line_number;
927 */
928 /*10434986*/
929 lr_trx_repo_extract.document_line_id := r_repo_recs.item_line_id;--1102
930 lr_trx_repo_extract.repository_line_id := r_repo_recs.source_document_id;--Added by Qiong for reverse charge bug#16001407
931
932 end if;
933 --end additions for bug#10085619
934
935 --Added by Qiong for bug13405553 begin
936 if lr_trx_repo_extract.transaction_source ='AP_CLAIM'
937 then
938 lr_trx_repo_extract.document_line_id := r_repo_recs.item_line_id;--1102
939 lr_trx_repo_extract.repository_line_id := r_repo_recs.source_document_id;
940
941 end if;
942 --Added by Qiong for bug13405553 end
943
944 jai_cmn_debug_contexts_pkg.print
945 (ln_reg_id
946 , 'Before insert into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
947 'lr_trx_repo_extract.transaction_repository_id ='|| lr_trx_repo_extract.transaction_repository_id || fnd_global.local_chr(10) ||
948 'lr_trx_repo_extract.transaction_reference_id ='|| lr_trx_repo_extract.transaction_reference_id || fnd_global.local_chr(10) ||
949 'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
950 'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
951 'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
952 'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
953 'lr_trx_repo_extract.document_id ='|| lr_trx_repo_extract.document_id || fnd_global.local_chr(10) ||
954 'lr_trx_repo_extract.document_line_id ='|| lr_trx_repo_extract.document_line_id || fnd_global.local_chr(10) ||
955 'lr_trx_repo_extract.document_line_num ='|| lr_trx_repo_extract.document_line_num || fnd_global.local_chr(10) ||
956 'lr_trx_repo_extract.document_line_item ='|| lr_trx_repo_extract.document_line_item || fnd_global.local_chr(10) ||
957 'lr_trx_repo_extract.document_line_desc ='|| lr_trx_repo_extract.document_line_desc
958 );
959 jai_cmn_debug_contexts_pkg.print
960 ( ln_reg_id
961 ,'lr_trx_repo_extract.document_line_qty ='|| lr_trx_repo_extract.document_line_qty || fnd_global.local_chr(10) ||
962 'lr_trx_repo_extract.document_line_uom ='|| lr_trx_repo_extract.document_line_uom || fnd_global.local_chr(10) ||
963 'lr_trx_repo_extract.document_line_amt ='|| lr_trx_repo_extract.document_line_amt || fnd_global.local_chr(10) ||
964 'lr_trx_repo_extract.document_currency_code ='|| lr_trx_repo_extract.document_currency_code || fnd_global.local_chr(10) ||
965 'lr_trx_repo_extract.repository_tax_amt ='|| lr_trx_repo_extract.repository_tax_amt || fnd_global.local_chr(10) ||
966 'lr_trx_repo_extract.organization_name ='|| lr_trx_repo_extract.organization_name || fnd_global.local_chr(10) ||
967 'lr_trx_repo_extract.location_name ='|| lr_trx_repo_extract.location_name || fnd_global.local_chr(10) ||
968 'lr_trx_repo_extract.organization_id ='|| lr_trx_repo_extract.organization_id || fnd_global.local_chr(10) ||
969 'lr_trx_repo_extract.location_id ='|| lr_trx_repo_extract.location_id || fnd_global.local_chr(10) ||
970 'lr_trx_repo_extract.inventory_item_id ='|| lr_trx_repo_extract.inventory_item_id || fnd_global.local_chr(10) ||
971 'lr_trx_repo_extract.party_id ='|| lr_trx_repo_extract.party_id || fnd_global.local_chr(10) ||
972 'lr_trx_repo_extract.service_type_code ='|| lr_trx_repo_extract.service_type_code
973 );
974 insert into jai_trx_repo_extract_gt
975 (
976 transaction_repository_id
977 ,transaction_reference_id
978 ,transaction_source
979 ,party_name
980 ,document_number
981 ,document_date
982 ,document_id
983 ,document_line_id
984 ,document_line_num
985 ,document_line_item
986 ,document_line_desc
987 ,document_line_qty
988 ,document_line_uom
989 ,document_line_amt
990 ,document_currency_code
991 ,repository_tax_amt
992 ,organization_name
993 ,location_name
994 ,organization_id
995 ,location_id
996 ,inventory_item_id
997 ,party_id
998 ,service_type_code
999 ,repository_invoice_id
1000 ,repository_line_id
1001 ,repository_source
1002 ,processed_flag
1003 ,gl_date --Add by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
1004 ,st_inv_number --Add by Qiong for Advanced Receipts
1005 ,source_trx_type --Add by Qiong for Advanced Receipts
1006 )
1007 values
1008 (
1009 lr_trx_repo_extract.transaction_repository_id
1010 ,lr_trx_repo_extract.transaction_reference_id
1011 ,lr_trx_repo_extract.transaction_source
1012 ,lr_trx_repo_extract.party_name
1013 ,lr_trx_repo_extract.document_number
1014 ,lr_trx_repo_extract.document_date
1015 ,lr_trx_repo_extract.document_id
1016 ,lr_trx_repo_extract.document_line_id
1017 ,lr_trx_repo_extract.document_line_num
1018 ,lr_trx_repo_extract.document_line_item
1019 ,lr_trx_repo_extract.document_line_desc
1020 ,lr_trx_repo_extract.document_line_qty
1021 ,lr_trx_repo_extract.document_line_uom
1022 ,lr_trx_repo_extract.document_line_amt
1023 ,lr_trx_repo_extract.document_currency_code
1024 ,lr_trx_repo_extract.repository_tax_amt
1025 ,lr_trx_repo_extract.organization_name
1026 ,lr_trx_repo_extract.location_name
1027 ,lr_trx_repo_extract.organization_id
1028 ,lr_trx_repo_extract.location_id
1029 ,lr_trx_repo_extract.inventory_item_id
1030 ,lr_trx_repo_extract.party_id
1031 ,lr_trx_repo_extract.service_type_code
1032 ,lr_trx_repo_extract.repository_invoice_id
1033 ,lr_trx_repo_extract.repository_line_id
1034 ,lr_trx_repo_extract.repository_source
1035 ,NULL
1036 ,lr_trx_repo_extract.gl_date --Add by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
1037 ,lr_trx_repo_extract.st_inv_number --Add by Qiong for Advanced Receipts
1038 ,lr_trx_repo_extract.source_trx_type --Add by Qiong for Advanced Receipts
1039 );
1040 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After insert into jai_trx_repo_extract_gt');
1041 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'End of Loop -> for r_repo_recs in c_get_repo_recs');
1042
1043 end loop; --> r_repo_recs in c_get_repo_recs
1044
1045
1046 --
1047 -- Fetch distinct documents from the global temporary table populated above and get document details for each distinct document
1048 -- and update temp table with document details
1049 --
1050 for r_docs in (select distinct transaction_source
1051 , source_trx_type --Add by Qiong for Advanced Receipts
1052 , document_id
1053 , document_line_id
1054 , repository_line_id --Add by Qiong for bug#13405553
1055 , transaction_repository_id --Added by Qinglei on 28-Feb-2012 for bug#13741544
1056 from jai_trx_repo_extract_gt gt
1057 --Added by Chong.Lei for POT code port begin
1058 where gt.repository_source in ('AP','AR'
1059 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
1060 , 'AP_REVERSAL'
1061 , 'AP_CLAIM'
1062 , 'AR_REVERSAL'
1063 , 'AR_CLAIM'
1064 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
1065 )
1066 --Added by Chong.Lei for POT code port end
1067 )
1068 loop
1069
1070 lr_trx_repo_extract := null;
1071 if r_docs.transaction_source = 'ORDER MANAGEMENT' then
1072
1073 -- Only line_id it self is primary key for oe_order_lines_all so only line_id will do the job
1074 jai_trx_repo_extract_pkg.get_document_details
1075 ( p_document_id => ''
1076 , p_document_line_id => r_docs.document_line_id
1077 , p_document_source => r_docs.transaction_source
1078 , p_called_from => 'JAINRPRW'
1079 , p_process_message => p_process_message
1080 , p_process_flag => p_process_flag
1081 , p_trx_repo_extract => lr_trx_repo_extract
1082 );
1083
1084 if p_process_flag <> jai_constants.SUCCESSFUL then
1085 return;
1086 end if;
1087 /*added by csahoo for bug#6457710,start*/
1088 elsif r_docs.transaction_source = 'PROJECTS' then
1089
1090 jai_trx_repo_extract_pkg.get_document_details
1091 ( p_document_id => r_docs.document_id
1092 , p_document_line_id => r_docs.document_line_id
1093 , p_document_source => r_docs.transaction_source
1094 , p_called_from => 'JAINRPRW'
1095 , p_process_message => p_process_message
1096 , p_process_flag => p_process_flag
1097 , p_trx_repo_extract => lr_trx_repo_extract
1098 );
1099
1100 if p_process_flag <> jai_constants.SUCCESSFUL then
1101 return;
1102 end if;
1103 /*bug#6457710,end*/
1104
1105 --Added by Chong.Lei for POT code port begin
1106 --elsif r_docs.transaction_source = 'RECEIVABLES' THEN
1107 elsif r_docs.transaction_source IN ('RECEIVABLES', 'AR_REVERSAL', 'AR_CLAIM') THEN --Changed by Xiao for POT Phase III, reg bug#12895841.
1108 --Added by Chong.Lei for POT code port end
1109
1110 jai_trx_repo_extract_pkg.get_document_details
1111 ( p_document_id => r_docs.document_id
1112 , p_document_line_id => r_docs.document_line_id
1113 , p_document_source => r_docs.transaction_source
1114 , p_called_from => 'JAINRPRW'
1115 , p_source_trx_type => r_docs.source_trx_type--Add by Qiong.liu for Advanced Receipts
1116 , p_repository_id => r_docs.transaction_repository_id--Added by Qinglei for bug#13741544
1117 , p_process_message => p_process_message
1118 , p_process_flag => p_process_flag
1119 , p_trx_repo_extract => lr_trx_repo_extract
1120 );
1121
1122 if p_process_flag <> jai_constants.SUCCESSFUL then
1123 return;
1124 end if;
1125
1126 /* elsif r_docs.transaction_source = 'PURCHASING' then
1127
1128 jai_trx_repo_extract_pkg.get_document_details
1129 ( p_document_id => r_docs.document_id
1130 , p_document_line_id => r_docs.document_line_id
1131 , p_document_source => r_docs.transaction_source
1132 , p_called_from => 'JAINRPRW'
1133 , p_process_message => p_process_message
1134 , p_process_flag => p_process_flag
1135 , p_trx_repo_extract => lr_trx_repo_extract
1136 );
1137
1138 --Elsif added for Bug#6841116
1139 elsif r_docs.transaction_source = 'RECEIVING' then
1140
1141 jai_trx_repo_extract_pkg.get_document_details
1142 ( p_document_id => r_docs.document_id
1143 , p_document_line_id => r_docs.document_line_id
1144 , p_document_source => r_docs.transaction_source
1145 , p_called_from => 'JAINRPRW'
1146 , p_source_trx_type => r_docs.source_trx_type--Add by Qiong.liu for Advanced Receipts
1147 , p_process_message => p_process_message
1148 , p_process_flag => p_process_flag
1149 , p_trx_repo_extract => lr_trx_repo_extract
1150 ); */
1151 /* Commented the above code and added 'PURCHASING', 'RECEIVING' in the below elsif condition -Bug 13799495*/
1152 --Till Here Bug#6841116
1153 --Added by Chong.Lei for POT code port begin
1154 --elsif r_docs.transaction_source = 'STANDALONE_INVOICE' then --Added for bug#8943349
1155 elsif r_docs.transaction_source IN ('STANDALONE_INVOICE', 'AP_REVERSAL', 'AP_CLAIM', 'PURCHASING', 'RECEIVING') THEN --Changed by Xiao for POT Phase III, reg bug#12895841.
1156 --Added by Chong.Lei for POT code port end
1157
1158 --Added by Qiong for bug#13405553 begin
1159 ------------------------------------------
1160 if r_docs.transaction_source = 'AP_CLAIM' then
1161 jai_trx_repo_extract_pkg.get_document_details
1162 ( p_document_id => r_docs.document_id
1163 , p_document_line_id => r_docs.document_line_id
1164 , p_document_source => r_docs.transaction_source
1165 , p_called_from => 'JAINRPRW'
1166 , p_process_message => p_process_message
1167 , p_process_flag => p_process_flag
1168 , p_trx_repo_extract => lr_trx_repo_extract
1169 ,p_taxline_no => r_docs.repository_line_id
1170 );
1171 ------------------------------------------
1172 --Added by Qiong for bug#13405553 begin
1173 else
1174
1175 jai_trx_repo_extract_pkg.get_document_details
1176 ( p_document_id => r_docs.document_id
1177 , p_document_line_id => r_docs.document_line_id
1178 , p_document_source => r_docs.transaction_source
1179 , p_called_from => 'JAINRPRW'
1180 , p_process_message => p_process_message
1181 , p_process_flag => p_process_flag
1182 , p_trx_repo_extract => lr_trx_repo_extract
1183 ,p_taxline_no=> r_docs.document_line_id --10085619
1184 );
1185
1186 --Added by Qinglei for bug#13405591 13-Dec-2011 begin
1187 IF r_docs.source_trx_type IN (/*'INVOICE_ACCOUNTING','INVOICE_CANCELLATION' ,*/
1188 --Added by Qiong for reverse charge bug#16001407 begin
1189 'REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'/*,'INVOICE_SETTLEMENT'*/) THEN
1190 --Added by Qiong for reverse charge bug#16001407 end
1191 OPEN c_get_repository_tax_amount(pv_source_trx_type => r_docs.source_trx_type
1192 ,pv_item_line_id => r_docs.document_line_id
1193 ,pn_invoice_id => r_docs.document_id);
1194 FETCH c_get_repository_tax_amount INTO lr_trx_repo_extract.repository_tax_amt;
1195 CLOSE c_get_repository_tax_amount;
1196 --Added by Qiong for reverse charge bug#16001407 start
1197 elsif r_docs.source_trx_type IN ('INVOICE_ACCOUNTING','INVOICE_CANCELLATION' ,
1198 'INVOICE_SETTLEMENT') then
1199
1200 OPEN c_get_repository_tax_amount_o(pv_source_trx_type => r_docs.source_trx_type
1201 ,pv_item_line_id => r_docs.document_line_id
1202 ,pn_invoice_id => r_docs.document_id);
1203 FETCH c_get_repository_tax_amount_o INTO lr_trx_repo_extract.repository_tax_amt;
1204 CLOSE c_get_repository_tax_amount_o;
1205 --Added by Qiong for reverse charge bug#16001407 end
1206
1207 END IF;
1208 IF r_docs.source_trx_type IN ('INVOICE_CANCELLATION') THEN
1209 lr_trx_repo_extract.document_line_amt := -lr_trx_repo_extract.document_line_amt;
1210 END IF;
1211
1212 --Added by Qiong for reverse charge bug#16001407 start
1213 IF r_docs.source_trx_type IN ('INVOICE_SETTLEMENT') THEN
1214 lr_trx_repo_extract.document_date := get_gl_date(
1215 pv_source => jai_constants.source_ap,
1216 pv_source_trx_type => NULL,
1217 pn_document_id => null,
1218 pn_source_document_id => r_docs.repository_line_id,
1219 pn_organization_id => null,
1220 pn_location_id => null,
1221 pv_source_table_name => 'JAI_RGM_SETTLEMENTS'
1222 );
1223 END IF;
1224 --Added by Qiong for reverse charge bug#16001407 end
1225
1226 OPEN c_get_invoice_discarded_flag(pn_invoice_id => lr_trx_repo_extract.document_id
1227 ,pv_invoice_line_num => lr_trx_repo_extract.document_line_id);
1228 FETCH c_get_invoice_discarded_flag INTO lv_discarded_flag;
1229 CLOSE c_get_invoice_discarded_flag;
1230 IF lv_discarded_flag = 'Y' THEN
1231 lr_trx_repo_extract.document_line_amt := -lr_trx_repo_extract.document_line_amt;
1232 lr_trx_repo_extract.repository_tax_amt := -lr_trx_repo_extract.repository_tax_amt;
1233 END IF;
1234 --Added by Qinglei for bug#13405591 13-Dec-2011 end
1235
1236
1237 end if;--Added by Qiong for reverse charge bug#16001407
1238
1239 if p_process_flag <> jai_constants.SUCCESSFUL then
1240 return;
1241 end if;
1242 --End of bug#8943349
1243 end if;
1244
1245 --
1246 -- For each document line check if repository has a settled record with a service type attached. If yes, then get the service type
1247 -- of the settled line and default it to current document line and mark the record as non-updatable
1248 --
1249
1250 lv_service_type_code := get_settled_service_type
1251 ( p_transaction_source => r_docs.transaction_source
1252 , p_document_id => lr_trx_repo_extract.document_id
1253 , p_document_line_id => lr_trx_repo_extract.document_line_id
1254 );
1255 if lv_service_type_code is not null then
1256
1257 lr_trx_repo_extract.service_type_code := lv_service_type_code;
1258 lr_trx_repo_extract.updatable_flag := jai_constants.NO;
1259 lr_trx_repo_extract.processed_flag := jai_constants.NO;
1260
1261 end if;
1262
1263
1264 jai_cmn_debug_contexts_pkg.print
1265 (ln_reg_id
1266 , 'Before update into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
1267 'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
1268 'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
1269 'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
1270 'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
1271 'lr_trx_repo_extract.document_id ='|| lr_trx_repo_extract.document_id || fnd_global.local_chr(10) ||
1272 'lr_trx_repo_extract.document_line_id ='|| lr_trx_repo_extract.document_line_id || fnd_global.local_chr(10) ||
1273 'lr_trx_repo_extract.document_line_num ='|| lr_trx_repo_extract.document_line_num || fnd_global.local_chr(10) ||
1274 'lr_trx_repo_extract.document_line_item ='|| lr_trx_repo_extract.document_line_item || fnd_global.local_chr(10) ||
1275 'lr_trx_repo_extract.document_line_desc ='|| lr_trx_repo_extract.document_line_desc
1276 );
1277 jai_cmn_debug_contexts_pkg.print
1278 ( ln_reg_id
1279 ,'lr_trx_repo_extract.document_line_qty ='|| lr_trx_repo_extract.document_line_qty || fnd_global.local_chr(10) ||
1280 'lr_trx_repo_extract.document_line_uom ='|| lr_trx_repo_extract.document_line_uom || fnd_global.local_chr(10) ||
1281 'lr_trx_repo_extract.document_line_amt ='|| lr_trx_repo_extract.document_line_amt || fnd_global.local_chr(10) ||
1282 'lr_trx_repo_extract.document_currency_code ='|| lr_trx_repo_extract.document_currency_code || fnd_global.local_chr(10) ||
1283 'lr_trx_repo_extract.repository_tax_amt ='|| lr_trx_repo_extract.repository_tax_amt || fnd_global.local_chr(10) ||
1284 'lr_trx_repo_extract.organization_name ='|| lr_trx_repo_extract.organization_name || fnd_global.local_chr(10) ||
1285 'lr_trx_repo_extract.location_name ='|| lr_trx_repo_extract.location_name || fnd_global.local_chr(10) ||
1286 'lr_trx_repo_extract.organization_id ='|| lr_trx_repo_extract.organization_id || fnd_global.local_chr(10) ||
1287 'lr_trx_repo_extract.location_id ='|| lr_trx_repo_extract.location_id || fnd_global.local_chr(10) ||
1288 'lr_trx_repo_extract.inventory_item_id ='|| lr_trx_repo_extract.inventory_item_id || fnd_global.local_chr(10) ||
1289 'lr_trx_repo_extract.party_id ='|| lr_trx_repo_extract.party_id || fnd_global.local_chr(10) ||
1290 'lr_trx_repo_extract.service_type_code ='|| lr_trx_repo_extract.service_type_code || fnd_global.local_chr(10) ||
1291 'lr_trx_repo_extract.updatable_flag ='|| lr_trx_repo_extract.updatable_flag || fnd_global.local_chr(10) ||
1292 'lr_trx_repo_extract.processed_flag ='|| lr_trx_repo_extract.processed_flag
1293 );
1294
1295 update jai_trx_repo_extract_gt
1296 set transaction_source = lr_trx_repo_extract.transaction_source
1297 , party_name = lr_trx_repo_extract.party_name
1298 , document_number = lr_trx_repo_extract.document_number
1299 , document_date = lr_trx_repo_extract.document_date
1300 , document_id = lr_trx_repo_extract.document_id
1301 , document_line_id = lr_trx_repo_extract.document_line_id
1302 , document_line_num = lr_trx_repo_extract.document_line_num
1303 , document_line_item = lr_trx_repo_extract.document_line_item
1304 , document_line_desc = lr_trx_repo_extract.document_line_desc
1305 , document_line_qty = lr_trx_repo_extract.document_line_qty
1306 , document_line_uom = lr_trx_repo_extract.document_line_uom
1307 , document_line_amt = lr_trx_repo_extract.document_line_amt
1308 , repository_tax_amt = lr_trx_repo_extract.repository_tax_amt
1309 , document_currency_code = lr_trx_repo_extract.document_currency_code
1310 , inventory_item_id = lr_trx_repo_extract.inventory_item_id
1311 , party_id = lr_trx_repo_extract.party_id
1312 , organization_id = nvl(lr_trx_repo_extract.organization_id, organization_id)
1313 , location_id = nvl(lr_trx_repo_extract.location_id,location_id)
1314 , service_type_code = nvl(lr_trx_repo_extract.service_type_code, service_type_code)
1315 , updatable_flag = lr_trx_repo_extract.updatable_flag
1316 , processed_flag = lr_trx_repo_extract.processed_flag
1317 where transaction_source = r_docs.transaction_source
1318 and ( (r_docs.document_id is not null and document_id = r_docs.document_id)
1319 or r_docs.document_id is null -- incase of order management it will be null
1320 )
1321 and source_trx_type = NVL( r_docs.source_trx_type, source_trx_type) --Add by Qiong for advanced receipts bug13361952
1322 and repository_line_id = NVL( r_docs.repository_line_id,repository_line_id) --Add by Qiong for bug#13405553
1323 and document_line_id = r_docs.document_line_id;
1324
1325 jai_cmn_debug_contexts_pkg.print
1326 ( ln_reg_id
1327 , 'Number of rows updated ='||sql%rowcount
1328 );
1329 end loop;
1330
1331 --
1332 -- Get organization name for each distinct organization
1333 --
1334
1335 for r_org in (select distinct organization_id from jai_trx_repo_extract_gt where organization_id is not null)
1336 loop
1337
1338 jai_cmn_debug_contexts_pkg.print
1339 (ln_reg_id
1340 , 'OPEN/FETCH/CLOSE c_get_organization_name, r_org.organization_id='||r_org.organization_id
1341 );
1342
1343 open c_get_organization_name (cp_organization_id => r_org.organization_id);
1344 fetch c_get_organization_name into lv_organization_name;
1345 close c_get_organization_name ;
1346
1347 jai_cmn_debug_contexts_pkg.print
1348 (ln_reg_id
1349 ,'lv_organization_name='||lv_organization_name
1350 );
1351
1352
1353 update jai_trx_repo_extract_gt
1354 set organization_name = lv_organization_name
1355 where organization_id = r_org.organization_id;
1356
1357 end loop;
1358
1359 --
1360 -- Get location name for each distinct location
1361 --
1362
1363 for r_loc in (select distinct location_id from jai_trx_repo_extract_gt where location_id is not null )
1364 loop
1365 jai_cmn_debug_contexts_pkg.print
1366 (ln_reg_id
1367 ,'OPEN/FETCH/CLOSE c_get_location_name, r_loc.location_id='||r_loc.location_id
1368 );
1369
1370 open c_get_location_name (cp_location_id => r_loc.location_id);
1371 fetch c_get_location_name into lv_location_name;
1372 close c_get_location_name ;
1373
1374 jai_cmn_debug_contexts_pkg.print
1375 (ln_reg_id
1376 ,'lv_location_name='||lv_location_name
1377 );
1378
1379 update jai_trx_repo_extract_gt
1380 set location_name = lv_location_name
1381 where location_id = r_loc.location_id;
1382
1383 end loop;
1384
1385 /** Deregister procedure and return*/
1386 <<deregister_and_return>>
1387 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
1388
1389 exception
1390 when others then
1391 p_process_flag := jai_constants.unexpected_error;
1392 p_process_message := lv_context||'->'||sqlerrm;
1393 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1394 jai_cmn_debug_contexts_pkg.print_stack;
1395 end extract_rgm_trxs;
1396
1397 /*------------------------------------------------------------------------------------------------------------*/
1398
1399
1400 procedure get_document_details
1401 (
1402 p_document_id in number
1403 , p_document_line_id in number
1404 , p_document_source in varchar2
1405 , p_called_from in varchar2 default null
1406 , p_source_trx_type in varchar2 default NULL
1407 , p_repository_id in varchar2 default NULL
1408 , p_process_message out nocopy varchar2
1409 , p_process_flag out nocopy varchar2
1410 , p_trx_repo_extract in out nocopy jai_trx_repo_extract_gt%rowtype
1411 --added p_taxline_no for bug #10085619
1412 , p_taxline_no in number default null
1413
1414 )
1415 as
1416 ln_reg_id number;
1417 ln_converted_rate NUMBER;
1418
1419 /*Added parameters cp_header_id and cp_line_id in c_get_po_details by vkantamn for Bug #6083978*/
1420 cursor c_get_po_details(cp_header_id number, cp_line_id number)
1421 is
1422 select pov.vendor_name party_name
1423 ,poh.segment1 document_number
1424 ,poh.creation_date document_date
1425 ,poh.po_header_id document_id
1426 ,pol.po_line_id document_line_id
1427 ,pol.line_num document_line_num
1428 ,msi.segment1 document_line_item
1429 ,pol.item_description document_line_desc
1430 ,pol.quantity document_line_qty
1431 ,pol.unit_meas_lookup_code document_line_uom
1432 ,(pol.unit_price * pol.quantity) document_line_amt
1433 ,poh.currency_code document_currency_code
1434 ,pol.item_id inventory_item_id
1435 ,poh.vendor_id party_id
1436 -- ,fsp.inventory_organization_id organization_id /* Commented by vkantamn for Bug#6083978 */
1437 ,hl.inventory_organization_id organization_id /* Added by vkantamn for Bug#6083978 */
1438 ,poll.ship_to_location_id location_id
1439 from po_headers_all poh
1440 , po_lines_all pol
1441 , po_line_locations_all poll /*6841116*/
1442 , mtl_system_items msi
1443 , po_vendors pov
1444 , hr_locations hl /* Added by vkantamn for Bug#6083978 */
1445 -- , financials_system_parameters fsp /* Commented by vkantamn for Bug#6083978 */
1446 where
1447 --poh.po_header_id = p_document_id /* Commented by vkantamn for Bug#6083978 */
1448 poh.po_header_id = cp_header_id /* Added by vkantamn for Bug#6083978 */
1449 and pol.po_header_id = poh.po_header_id
1450 -- and pol.po_line_id = p_document_line_id /* Commented by vkantamn for Bug#6083978 */
1451 and pol.po_line_id = cp_line_id /* Added by vkantamn for Bug#6083978 */
1452 and pol.po_line_id = poll.po_line_id
1453 and poll.po_header_id = poh.po_header_id
1454 and pol.item_id = msi.inventory_item_id (+)
1455 --and nvl(msi.organization_id ,fsp.inventory_organization_id )= fsp.inventory_organization_id /* Commented by vkantamn for Bug#6026463 */
1456 and nvl(poll.ship_to_location_id,poh.ship_to_location_id )= hl.location_id /*Commented by nprashar for bug # 6636517 and poh.ship_to_location_id = hl.location_id */ /* Added by vkantamn for Bug#6083978 */
1457 and pov.vendor_id = poh.vendor_id ;
1458
1459 /*Bug 12902363 - Start*/
1460 cursor c_get_receipt_details(cp_transaction_id number)
1461 is
1462 select pov.vendor_name party_name
1463 ,rsh.receipt_num document_number
1464 ,rta.transaction_date document_date
1465 ,rsh.shipment_header_id document_id
1466 ,rsl.shipment_line_id document_line_id
1467 ,rsl.line_num document_line_num
1468 ,msi.segment1 document_line_item
1469 ,rsl.item_description document_line_desc
1470 ,rsl.quantity_received document_line_qty
1471 ,rsl.unit_of_measure document_line_uom
1472 ,(pla.unit_price * rsl.quantity_received) document_line_amt
1473 ,rsh.currency_code document_currency_code
1474 ,rsl.item_id inventory_item_id
1475 ,rsh.vendor_id party_id
1476 ,hl.inventory_organization_id organization_id
1477 ,rsl.ship_to_location_id location_id
1478 from rcv_transactions rta
1479 , rcv_shipment_headers rsh
1480 , rcv_shipment_lines rsl
1481 , po_lines_all pla
1482 , mtl_system_items msi
1483 , po_vendors pov
1484 , hr_locations_all hl
1485 where
1486 rta.transaction_id = cp_transaction_id
1487 and rta.po_line_id = pla.po_line_id
1488 and rta.shipment_header_id = rsh.shipment_header_id
1489 and rsh.shipment_header_id = rsl.shipment_header_id
1490 and rsh.vendor_id = pov.vendor_id
1491 and rsl.item_id = msi.inventory_item_id
1492 and rsl.to_organization_id = msi.organization_id
1493 and nvl(rsl.ship_to_location_id,rsh.ship_to_location_id )= hl.location_id;
1494 /*Bug 12902363 - End*/
1495
1496 cursor c_get_so_details
1497 is
1498 select hzp.party_name party_name
1499 , oeh.order_number document_number
1500 , oeh.ordered_date document_date
1501 , oeh.header_id document_id
1502 , oel.line_id document_line_id
1503 , oel.line_number document_line_num
1504 , msi.segment1 document_line_item
1505 , substr(oel.user_item_description,1,240) document_line_desc
1506 , oel.ordered_quantity document_line_qty
1507 , oel.order_quantity_uom document_line_uom
1508 , nvl(oel.unit_selling_price * oel.ordered_quantity,0) document_line_amt
1509 , oeh.transactional_curr_code document_currency_code
1510 , oel.inventory_item_id inventory_item_id
1511 , oeh.sold_to_org_id party_id
1512 , oel.ship_from_org_id organization_id
1513 , oel.line_category_code line_category_code
1514 from
1515 oe_order_headers_all oeh
1516 ,oe_order_lines_all oel
1517 ,hz_parties hzp
1518 ,hz_cust_accounts hzca
1519 ,mtl_system_items msi
1520 where (p_document_id is null or p_document_id = '' or oeh.header_id = p_document_id)
1521 and oel.header_id = oeh.header_id
1522 and oel.line_id = p_document_line_id
1523 and oel.inventory_item_id = msi.inventory_item_id
1524 and oel.ship_from_org_id = msi.organization_id
1525 and hzca.cust_account_id = oel.sold_to_org_id
1526 and hzca.party_id = hzp.party_id ;
1527
1528 cursor c_get_ra_trx_details
1529 is
1530 select hzp.party_name
1531 ,rct.trx_number document_number
1532 ,rct.trx_date document_date
1533 ,rct.customer_trx_id document_id
1534 ,rctl.customer_trx_line_id document_line_id
1535 ,rctl.line_number document_line_num
1536 ,msi.segment1 document_line_item
1537 ,rctl.description document_line_desc
1538 ,rctl.quantity_invoiced document_line_qty
1539 ,rctl.uom_code document_line_uom
1540 ,rctl.extended_amount document_line_amt
1541 ,rct.invoice_currency_code document_currency_code
1542 ,rctl.inventory_item_id inventory_item_id
1543 ,nvl(rct.sold_to_customer_id, rct.bill_to_customer_id) party_id
1544 ,jrct.organization_id organization_id
1545 ,jrct.location_id location_id
1546 from ra_customer_trx_all rct
1547 ,ra_customer_trx_lines_all rctl
1548 ,jai_ar_trxs jrct
1549 ,hz_parties hzp
1550 ,hz_cust_accounts hzca
1551 ,mtl_system_items msi
1552 where rct.customer_trx_id = p_document_id
1553 and jrct.customer_trx_id = rct.customer_trx_id
1554 and rctl.customer_trx_id = rct.customer_trx_id
1555 and rctl.customer_trx_line_id = p_document_line_id
1556 and rctl.inventory_item_id = msi.inventory_item_id (+)
1557 and nvl(msi.organization_id,jrct.organization_id) = jrct.organization_id
1558 and hzca.cust_account_id = nvl(rct.sold_to_customer_id, rct.bill_to_customer_id)
1559 and hzca.party_id = hzp.party_id;
1560
1561 -- Begin 5876390, 6012570
1562 /*modified the below cusrsor query to select from the tables PA_DRAFT_INVOICES_ALL,
1563 PA_PROJECTS_ALL instead of pa_draft_invoices_v.*/
1564 cursor c_get_pa_details
1565 is
1566 select c.customer_name party_name,
1567 p.segment1
1568 ||'/'
1569 ||padi.draft_invoice_num
1570 document_number
1571 , padi.creation_date document_date
1572 , jpadi.draft_invoice_id document_id
1573 , jpadil.draft_invoice_line_id document_line_id
1574 , jpadil.line_num document_line_num
1575 , null document_line_item
1576 -- Modified by Jia for FP Bug#6691866, Begin
1577 -------------------------------------------------------------------------------------------------
1578 --, substr(padil.text,1,240) document_line_desc -- Comment by Jia for FP Bug#6691866
1579 , (select substr(padil.text,1,240) from pa_draft_invoice_items padil
1580 where padil.draft_invoice_num = jpadi.draft_invoice_num
1581 and padil.project_id =jpadi.project_id
1582 and padil.line_num = jpadil.line_num ) document_line_desc -- Added by Jia for FP Bug#6691866
1583 -------------------------------------------------------------------------------------------------
1584 -- Modified by Jia for FP Bug#6691866, End
1585 , null document_line_qty
1586 , null document_line_uom
1587 , jpadil.line_amt document_line_amt
1588 , padi.inv_currency_code document_currency_code
1589 , null inventory_item_id
1590 , padi.ship_to_customer_id party_id
1591 , jpadi.organization_id organization_id
1592 , jpadi.location_id location_id
1593 , jpadil.service_type_code service_type_code
1594 from
1595 PA_DRAFT_INVOICES_ALL padi,
1596 PA_PROJECTS_ALL p
1597 -- ,pa_draft_invoice_items padil Removed by Jia for FP Bug#6691866
1598 ,jai_pa_draft_invoices jpadi
1599 ,jai_pa_draft_invoice_lines jpadil
1600 ,PA_CUSTOMERS_V c
1601 where jpadi.draft_invoice_id = p_document_id
1602 and jpadil.draft_invoice_line_id = p_document_line_id
1603 and jpadi.draft_invoice_id = jpadil.draft_invoice_id
1604 and jpadi.project_id = padi.project_id
1605 and jpadi.draft_invoice_num = padi.draft_invoice_num
1606 and p.project_id=padi.project_id
1607 and padi.ship_to_customer_id=c.customer_id;
1608 -- End 5876390, 6012570
1609
1610
1611 cursor c_get_po_line_loc_srvtyp (cp_po_line_id po_lines_all.po_line_id%type )
1612 is
1613 select service_type_code, sum(jpollt.tax_amount) service_tax_amount
1614 from JAI_PO_LINE_LOCATIONS jpoll
1615 ,jai_po_taxes jpollt
1616 where jpoll.po_line_id = cp_po_line_id
1617 and jpollt.line_location_id = jpoll.line_location_id
1618 /*added the cess and sh cess tax types for bug#6457710*/
1619 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)
1620 --and jpollt.tax_type = 'Service'
1621 group by service_type_code;
1622
1623 /*Bug 12902363 - Start*/
1624 cursor c_get_rcv_line_loc_srvtyp (cp_po_line_id po_lines_all.po_line_id%type )
1625 is
1626 select service_type_code, sum(jrlt.tax_amount) service_tax_amount
1627 from JAI_PO_LINE_LOCATIONS jpoll
1628 ,jai_rcv_line_taxes jrlt
1629 ,rcv_transactions rt
1630 where jpoll.po_line_id = cp_po_line_id
1631 and rt.po_line_id = cp_po_line_id
1632 and rt.transaction_type = 'DELIVER'
1633 and rt.shipment_header_id = jrlt.shipment_header_id
1634 and rt.shipment_line_id = jrlt.shipment_line_id
1635 and jrlt.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
1636 group by service_type_code;
1637 /*Bug 12902363 - End*/
1638
1639 --cursor c_get_ra_line_srvtyp (cp_customer_trx_line_id jai_ar_trx_lines.customer_trx_line_id%type) --commented by Chong.Lei for POT code port
1640 --Added by Chong.Lei for POT code port begin
1641 cursor c_get_ra_line_srvtyp (cp_customer_trx_line_id jai_ar_trx_lines.customer_trx_line_id%TYPE
1642 , pv_document_source VARCHAR2 --Add by Xiao for POT Phase III, reg bug#12895841.
1643 --Added by Chong.Lei for POT code port end
1644 )
1645 is
1646 -- select service_type_code, sum(jrcttl.tax_amount) service_tax_amount --commented by Chong.Lei for POT code port
1647 --Added by Chong.Lei for POT code port begin
1648 select jrctl.service_type_code,
1649 --sum(jrcttl.tax_amount) service_tax_amount
1650 abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0))) service_tax_amount--Add by Xiao for POT Phase III, reg bug#12895841.
1651 --Added by Chong.Lei for POT code port end
1652 from JAI_AR_TRX_LINES jrctl
1653 ,JAI_AR_TRX_TAX_LINES jrcttl
1654 ,jai_cmn_taxes_all jtc
1655 --Added by Chong.Lei for POT code port begin
1656 , jai_rgm_trx_records jrec --Add by Xiao for POT Phase III, reg bug#12895841.
1657 , jai_rgm_trx_refs jref --Add by Xiao for POT Phase III, reg bug#12895841.
1658 --Added by Chong.Lei for POT code port end
1659 where jrctl.customer_trx_line_id = cp_customer_trx_line_id
1660 and jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
1661 and jtc.tax_id = jrcttl.tax_id
1662 /*added the cess and sh cess tax types for bug#6457710*/
1663 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)
1664 --and jtc.tax_type = 'Service'
1665 -- group by service_type_code; --Commented by Chong.Lei for POT code port
1666 --Added by Chong.Lei for POT code port begin
1667 --Add pv_document_source by Xiao for POT Phase III, reg bug#12895841, begin
1668 AND jrec.source_document_id = jrcttl.customer_trx_line_id
1669 AND jref.invoice_id = p_document_id
1670 AND jref.item_line_id = cp_customer_trx_line_id
1671 AND jrec.reference_id = jref.reference_id
1672 AND jrec.source = decode(pv_document_source, 'RECEIVABLES', 'AR',pv_document_source)
1673 --Add pv_document_source by Xiao for POT Phase III, reg bug#12895841, end
1674 --Added by Chong.Lei for POT code port end
1675
1676 group by jrctl.service_type_code;
1677
1678
1679 --Added below cursor for bug#9298508 by JMEENA
1680 CURSOR c_get_ra_tax_amt_applied(cp_customer_trx_id NUMBER, cp_cust_trx_line_id NUMBER) IS
1681 SELECT Sum(Nvl(jrec.DEBIT_AMOUNT,0))+Sum(Nvl(jrec.CREDIT_AMOUNT,0)) -- modified for the bug 14341945
1682 FROM jai_rgm_trx_refs jref, jai_rgm_trx_records jrec
1683 WHERE jref.invoice_id= cp_customer_trx_id
1684 AND jref.item_line_id = cp_cust_trx_line_id
1685 AND jrec.source_trx_type = p_source_trx_type--Added by Qinglei on 28-Feb-2012 for bug#13741544
1686 AND jrec.reference_id=jref.reference_id
1687 AND jrec.TAX_TYPE IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
1688 /*Bug 11932841 - Added cp_cust_trx_line_id*/
1689 CURSOR c_get_ra_line_amt_applied (cp_customer_trx_id NUMBER, cp_cust_trx_line_id NUMBER) IS
1690 SELECT Sum(Nvl(line_applied,0)) * max(((SELECT line_amount
1691 FROM jai_ar_trx_lines
1692 WHERE customer_trx_id = cp_customer_trx_id
1693 AND customer_trx_line_id = cp_cust_trx_line_id)/
1694 (SELECT line_amount
1695 FROM jai_ar_trxs
1696 WHERE customer_trx_id = cp_customer_trx_id
1697 )))
1698 FROM AR_RECEIVABLE_APPLICATIONS_ALL araa,jai_rgm_trx_records jrtr
1699 WHERE araa.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
1700 /*Added by Qinglei for bug#13741544 begin*/
1701 AND araa.receivable_application_id = jrtr.source_document_id
1702 AND jrtr.repository_id = p_repository_id
1703 /*Added by Qinglei for bug#13741544 end*/
1704 AND araa.status= jai_constants.ar_status_app;
1705 --End bug#9298508
1706
1707 cursor c_get_so_line_srvtyp (cp_line_id JAI_OM_OE_SO_LINES.line_id%type ) is
1708 select service_type_code, sum(jstl.tax_amount) service_tax_amount
1709 from JAI_OM_OE_SO_LINES jsl
1710 , JAI_OM_OE_SO_TAXES jstl
1711 , jai_cmn_taxes_all jtc
1712 where jsl.line_id = cp_line_id
1713 and jsl.line_id = jstl.line_id
1714 and jstl.tax_id = jtc.tax_id
1715 AND nvl(jtc.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
1716 /*added the cess and sh cess tax types for bug#6457710*/
1717 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)
1718 --and jtc.tax_type = 'Service'
1719 group by service_type_code;
1720
1721 /*The following cursor added by rchandan for RMA */
1722
1723 cursor c_get_rma_line_srvtyp (cp_line_id jai_om_oe_so_lines.line_id%type )
1724 is
1725 select service_type_code, sum(jrtl.tax_amount) service_tax_amount
1726 from JAI_OM_OE_RMA_LINES jrl
1727 , JAI_OM_OE_RMA_TAXES jrtl
1728 , JAI_CMN_TAXES_ALL jtc
1729 where jrl.rma_line_id = cp_line_id
1730 and jrl.rma_line_id = jrtl.rma_line_id
1731 and jrtl.tax_id = jtc.tax_id
1732 AND nvl(jtc.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
1733 /*added the cess and sh cess tax types for bug#6457710*/
1734 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)
1735 --and jtc.tax_type = 'Service'
1736 group by service_type_code;
1737
1738 -- Bug 5876390, 6012570
1739 cursor c_get_pa_inv_line_tax (cp_line_id jai_cmn_document_taxes.source_doc_line_id%type )
1740 is
1741 select sum(tax_amt) service_tax_amount
1742 from jai_cmn_document_taxes jcdt
1743 , jai_cmn_taxes_all jtc
1744 where jcdt.source_doc_line_id = cp_line_id
1745 and jcdt.source_doc_type = jai_constants.PA_DRAFT_INVOICE
1746 and jcdt.tax_id = jtc.tax_id
1747 AND nvl(jtc.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
1748 /*added the cess and sh cess tax types for bug#6457710*/
1749 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);
1750 --and jtc.tax_type = 'Service';
1751
1752 /*Started addition by vkantamn for Bug#6083978*/
1753 cursor c_get_rcv_details
1754 is
1755 select rcv.organization_id organization_id
1756 ,rcv.deliver_to_location_id location_id --12560704
1757 ,rsl.po_header_id po_header_id
1758 ,rsl.po_line_id po_line_id
1759 ,rcv.transaction_id /*Bug 12902363*/
1760 from
1761 --jai_rcv_transactions rcv, 12560704
1762 rcv_transactions rcv ,
1763 rcv_shipment_lines rsl
1764 where
1765 rcv.shipment_header_id = rsl.shipment_header_id
1766 and rcv.shipment_line_id = rsl.shipment_line_id
1767 and rcv.shipment_header_id = p_document_id
1768 and rcv.shipment_line_id = p_document_line_id
1769 and rcv.transaction_type = 'RECEIVE';
1770 --Start bug#8943349 by JMEENA
1771 CURSOR c_get_standalone_inv_details IS
1772 select pov.vendor_name party_name
1773 ,apa.invoice_num document_number
1774 ,apa.creation_date document_date
1775 ,apa.invoice_id document_id
1776 ,apla.line_number document_line_id /*10434986*/
1777 ,apla.line_number document_line_num
1778 ,NULL document_line_item
1779 ,NULL document_line_desc
1780 ,NULL document_line_qty
1781 ,NULL document_line_uom
1782 ,decode(nvl(apla.amount,0),0,apla.original_amount,apla.amount) document_line_amt
1783 /*Modified by Qinglei 13-Dec-2011 Bug#13405591*/
1784 ,jasl.currency_code document_currency_code
1785 ,NULL inventory_item_id
1786 ,apa.vendor_id party_id
1787 ,jasl.organization_id organization_id
1788 ,jasl.location_id location_id
1789 from ap_invoices_all apa
1790 , ap_invoice_lines_all apla
1791 , po_vendors pov
1792 , jai_ap_invoice_lines jasl
1793 where
1794 apa.invoice_id = p_document_id
1795 and apa.invoice_id = apla.invoice_id
1796 and apla.line_number = p_document_line_id
1797 and jasl.invoice_id = apa.invoice_id
1798 and jasl.invoice_line_number = apla.line_number
1799 and pov.vendor_id = apa.vendor_id ;
1800
1801 --Added by Qiong for bug#13405553 begin
1802 --------------------------------------------
1803 CURSOR c_get_claim_amount (pv_document_source VARCHAR2) --Add pv_document_source by Xiao for POT Phase III reg bug#12895841. --Modified by Chong.Lei for POT code port
1804 IS
1805 SELECT abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0))) --Modified by Qiong for reverse charge bug#16001407
1806 FROM jai_rgm_trx_refs jref,
1807 jai_rgm_trx_records jrec
1808 WHERE jref.invoice_id = p_document_id
1809 AND jref.item_line_id = p_document_line_id
1810 AND jrec.source_document_id = p_taxline_no
1811 AND jrec.reference_id = jref.reference_id
1812 AND jrec.source = decode(pv_document_source, 'STANDALONE_INVOICE', 'AP',pv_document_source)
1813 AND jrec.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
1814
1815
1816 -------------------------------------------
1817 --Added by Qiong for bug#13405553 end
1818
1819 CURSOR c_get_standalone_org_loc IS
1820 SELECT organization_id, location_id
1821 FROM JAI_AP_INVOICE_LINES
1822 where invoice_id = p_document_id
1823 --Add by Xiao for fixing bug#10634960, begin
1824 --------------------------------------------------------------------
1825 AND invoice_line_number = (SELECT parent_invoice_line_number
1826 FROM JAI_AP_INVOICE_LINES jail
1827 WHERE jail.invoice_line_number = p_document_line_id
1828 AND jail.invoice_id = p_document_id)
1829 --------------------------------------------------------------------
1830 --Add by Xiao for fixing bug#10634960, end
1831 and PARENT_INVOICE_LINE_NUMBER is NULL;
1832
1833 CURSOR c_get_standalone_inv_line_tax (cp_invoice_id jai_cmn_document_taxes.source_doc_id%type, cp_line_id jai_cmn_document_taxes.source_doc_line_id%type) IS
1834 Select service_type_code,sum(jcdt.tax_amt)
1835 from jai_ap_invoice_lines jasl,
1836 jai_cmn_document_taxes jcdt,
1837 jai_cmn_taxes_all jcta
1838 where source_doc_line_id = cp_line_id
1839 AND jcdt.source_doc_id = cp_invoice_id
1840 AND jasl.invoice_id = jcdt.source_doc_id
1841 and jasl.invoice_line_number = jcdt.source_doc_line_id
1842 and jcta.tax_id = jcdt.tax_id
1843 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)
1844 GROUP BY service_type_code;
1845 /*10434986 - Start*/
1846 CURSOR c_get_standalone_inv_payment
1847 IS
1848 -- SELECT sum(amount) --Commented by Chong.Lei for POT code port
1849 -- Added by Chong.Lei for POT code port begin
1850 SELECT nvl(sum(amount), 0)--Add nvl by Xiao for POT Phase III, reg bug#12895841.
1851 -- Added by Chong.Lei for POT code port end
1852 FROM ap_invoice_payments_all
1853 WHERE invoice_id = p_document_id;
1854
1855 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 Begin*/
1856 CURSOR c_get_inv_prepay
1857 IS
1858 SELECT abs(nvl(SUM(aila.amount),0))
1859 FROM ap_invoice_lines_all aila
1860 WHERE aila.invoice_id = p_document_id
1861 AND aila.line_type_lookup_code = 'PREPAY';
1862
1863 CURSOR c_total_ap_amount IS
1864 select invoice_amount
1865 from ap_invoices_all
1866 where invoice_id = p_document_id;
1867
1868 CURSOR c_get_open_amount IS
1869 SELECT open_amt, original_line_amt
1870 FROM jai_st_invoice_reversal
1871 WHERE invoice_id = p_document_id
1872 AND line_num = p_document_line_id;
1873
1874 CURSOR c_get_claim_paid_amount IS
1875 SELECT SUM(aip.amount)
1876 FROM ap_invoice_payments_all aip
1877 WHERE EXISTS (SELECT 1 FROM jai_rgm_trx_refs jref,
1878 jai_rgm_trx_records jrec
1879 WHERE jref.invoice_id = p_document_id
1880 AND jref.item_line_id = p_document_line_id
1881 AND jrec.reference_id = jref.reference_id
1882 AND jrec.source = 'AP_CLAIM'
1883 AND jrec.source_document_id = p_taxline_no
1884 AND aip.invoice_id = jref.invoice_id
1885 AND aip.invoice_payment_id = jrec.source_document_id);
1886
1887 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 End*/
1888 CURSOR c_get_repo_amount (pv_document_source VARCHAR2) --Add pv_document_source by Xiao for POT Phase III reg bug#12895841. --Modified by Chong.Lei for POT code port
1889 IS
1890 SELECT abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0)))--add abs, change '+' to '-' by Xiao for POT, reg bug#12533434
1891 --Modified by Qiong for reverse charge bug#16001407
1892 FROM jai_rgm_trx_refs jref,
1893 jai_rgm_trx_records jrec
1894 WHERE jref.invoice_id = p_document_id
1895 AND jref.item_line_id = p_document_line_id --Add by Xiao fro POT Phase III, reg bug#12895841. --Modified by Chong.Lei for POT code port
1896 AND jrec.reference_id = jref.reference_id
1897 AND jrec.source = decode(pv_document_source, 'STANDALONE_INVOICE', 'AP','RECEIVING','AP','PURCHASING','AP',pv_document_source)--Add pv_document_source by Xiao for POT Phase III, reg bug#12895841.
1898 /* Added receiving and purchasing source conditions in above decode for bug 13430127 */
1899 AND jrec.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
1900
1901 CURSOR c_get_service_type
1902 IS
1903 SELECT service_type_code
1904 FROM jai_ap_invoice_lines
1905 WHERE invoice_id = p_document_id
1906 AND invoice_line_number = p_document_line_id;
1907 /*Added by Wenqiong for Advanced Receipts Begin*/
1908 cursor c_get_rcpt_trx_details
1909 is
1910 select hzp.party_name
1911 ,acra.receipt_number document_number
1912 ,acra.receipt_date document_date
1913 ,acra.cash_receipt_id document_id
1914 ,acra.amount document_line_amt
1915 ,jacr.exchange_rate exchange_rate
1916 ,jacr.currency_code document_currency_code
1917 ,jacr.customer_id party_id
1918 ,jacr.organization_id organization_id
1919 ,jacr.location_id location_id
1920 ,jacr.service_type_code service_type_code
1921 ,acra.reversal_date reversal_date --Added by Qiong for bug13555753
1922 from ar_cash_receipts_all acra,
1923 jai_ar_cash_receipts_all jacr,
1924 hz_parties hzp,
1925 hz_cust_accounts hca
1926 WHERE acra.cash_receipt_id = jacr.cash_receipt_id
1927 AND jacr.document_type = 'Service'
1928 AND hca.cust_account_id = jacr.customer_id
1929 AND hzp.party_id = hca.party_id
1930 AND acra.cash_receipt_id = p_document_id;
1931
1932 cursor c_get_rcpt_app_trx_details
1933 is
1934 select hzp.party_name
1935 ,acra.receipt_number document_number
1936 ,araa.apply_date document_date
1937 ,araa.receivable_application_id document_id
1938 ,araa.acctd_amount_applied_from document_line_amt
1939 --Modified by Qinglei on 05-Jan-2012 for bug#13556198
1940 ,araa.applied_customer_trx_id applied_customer_trx_id
1941 ,araa.applied_customer_trx_line_id applied_customer_trx_line_id
1942 ,jacr.currency_code document_currency_code
1943 ,jacr.customer_id party_id
1944 ,jacr.organization_id organization_id
1945 ,jacr.location_id location_id
1946 ,jacr.service_type_code service_type_code
1947 /*Added by Qinglei on 05-Jan-2012 for bug#13556198 begin*/
1948 ,acra.currency_code receipt_currency_code
1949 ,acra.exchange_date receipt_exchange_date
1950 ,acra.exchange_rate receipt_exchange_rate
1951 ,acra.exchange_rate_type receipt_exchange_rate_type
1952 ,acra.set_of_books_id
1953 /*Added by Qinglei on 05-Jan-2012 for bug#13556198 end*/
1954 from ar_receivable_applications_all araa,
1955 ar_cash_receipts_all acra,
1956 jai_ar_cash_receipts_all jacr,
1957 hz_parties hzp,
1958 hz_cust_accounts hca
1959 WHERE araa.cash_receipt_id = acra.cash_receipt_id
1960 AND acra.cash_receipt_id = jacr.cash_receipt_id
1961 AND jacr.document_type = 'Service'
1962 AND hca.cust_account_id = jacr.customer_id
1963 AND hzp.party_id = hca.party_id
1964 AND araa.receivable_application_id = p_document_id;
1965
1966 CURSOR get_rcpt_amt_cur IS
1967 SELECT SUM(nvl(jrec.trx_debit_amount,0)) + SUM(nvl(jrec.trx_credit_amount,0))
1968 FROM jai_rgm_trx_refs jref,
1969 jai_rgm_trx_records jrec
1970 WHERE jrec.source_document_id = p_document_id
1971 AND jref.reference_id = jrec.reference_id
1972 AND jrec.source_table_name = 'AR_CASH_RECEIPTS_ALL'
1973 AND jrec.source = 'AR'
1974 AND jrec.source_trx_type = p_source_trx_type
1975 AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
1976
1977 CURSOR get_rcpt_app_amt_cur IS
1978 SELECT SUM(nvl(jrec.trx_debit_amount,0)) + SUM(nvl(jrec.trx_credit_amount,0))
1979 FROM jai_rgm_trx_refs jref,
1980 jai_rgm_trx_records jrec
1981 WHERE jrec.source_document_id = p_document_id
1982 AND jref.reference_id = jrec.reference_id
1983 AND jrec.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
1984 AND jrec.source = 'AR'
1985 AND jrec.source_trx_type = p_source_trx_type
1986 AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
1987
1988 CURSOR c_get_document_line_num( p_document_id number,p_document_line_id number) IS
1989 SELECT rctl.line_number
1990 FROM ra_customer_trx_all rct
1991 ,ra_customer_trx_lines_all rctl
1992 WHERE rct.customer_trx_id = p_document_id
1993 and rctl.customer_trx_id = rct.customer_trx_id
1994 and rctl.customer_trx_line_id = p_document_line_id;
1995
1996 r_rcpt_trx_details c_get_rcpt_trx_details%ROWTYPE;
1997 r_rcpt_app_trx_details c_get_rcpt_app_trx_details%ROWTYPE;
1998 ln_document_line_num ra_customer_trx_lines_all.line_number%TYPE;
1999 /*Added by Wenqiong for Advanced Receipts End*/
2000
2001 ----Added by Qiong for bug13597785 Begin
2002 -----------------------------------------------
2003 cursor get_pa_trx_date is
2004 SELECT rct.trx_date document_date
2005 FROM jai_trx_repo_extract_gt gt,
2006 ra_customer_trx_all rct
2007 WHERE gt.document_id = p_document_id
2008 AND gt.document_line_id = p_document_line_id
2009 AND gt.transaction_source = p_document_source
2010 AND gt.repository_invoice_id = rct.customer_trx_id;
2011
2012 l_document_date date;
2013 ---------------------------------------------------
2014 --Added by Qiong for bug13597785 end
2015 ln_payment_amount NUMBER := 0;
2016 ln_prepay_amount NUMBER := 0;--Added by Wenqiong for bug13462951 on Feb 09, 2012
2017 ln_total_inv_amount NUMBER := 0;--Added by Wenqiong for bug13462951 on Feb 09, 2012
2018 ln_open_amt NUMBER := 0;--Added by Wenqiong for bug13462951 on Feb 09, 2012
2019 ln_total_original_amt NUMBER := 0;--Added by Wenqiong for bug13462951 on Feb 09, 2012
2020 ln_repository_tax_amount NUMBER := 0;
2021 /*10434986 - End*/
2022
2023 r_ap_details c_get_standalone_inv_details%rowtype;
2024 r_standalone_org_loc c_get_standalone_org_loc%rowtype;
2025 --End of bug#8943349 by JMEENA
2026
2027 v_organization_id number;
2028 v_location_id number;
2029 v_po_header_id number;
2030 v_po_line_id number;
2031 v_transaction_id number; /*Bug 12902363*/
2032
2033 /*End of addition by vkantamn for Bug#6083978*/
2034
2035 r_po_details c_get_po_details%rowtype;
2036 r_so_details c_get_so_details%rowtype;
2037 r_ra_trx_details c_get_ra_trx_details%rowtype;
2038 r_pa_details c_get_pa_details%rowtype; -- Bug 5876390, 6012570
2039 r_rcv_details c_get_receipt_details%rowtype; /*Bug 12902363*/
2040
2041 lv_service_type jai_rgm_trx_records.service_type_code%type;
2042
2043 --Add by Xiao for POT change, reg bug#12533434 on 18-May-2011, begin
2044 --------------------------------------------------------------------------
2045 --Commented by Chong.Lei for POT code port begin
2046 /*
2047 CURSOR c_get_st_accrual_date IS
2048 SELECT to_date(jrr.attribute_value, 'DD/MM/YYYY')
2049 FROM jai_rgm_registrations jrr
2050 , jai_rgm_definitions jrd
2051 WHERE jrr.regime_id = jrd.regime_id
2052 AND jrd.regime_code = jai_constants.service_regime
2053 AND jrr.attribute_code = 'EFF_DATE_ST_PT'
2054 AND jrr.attribute_type_code = 'OTHERS'
2055 AND jrr.registration_type = 'OTHERS';
2056 */
2057 --Commented by Chong.Lei for POT code port end
2058 -- Added by Chong.Lei for POT code port begin
2059 ------------------------------------------------------------------------------------
2060 --Add by Xiao for POT Phase III changes, reg bug#12895841.
2061 CURSOR get_claim_amt_cur IS
2062 SELECT SUM(nvl(jrec.debit_amount,0)) + SUM(nvl(jrec.credit_amount,0)) --Modified by Qiong for reverse charge bug#16001407
2063 FROM jai_rgm_trx_refs jref,
2064 jai_rgm_trx_records jrec
2065 WHERE jref.invoice_id = p_document_id
2066 AND jref.reference_id = jrec.reference_id
2067 AND jref.item_line_id = p_document_line_id
2068 --AND jrec.source_document_id = p_trx_repo_extract.document_line_num
2069 AND jrec.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
2070 AND jrec.source = 'AR_CLAIM'
2071 AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
2072 /* Bug 13430127 */
2073 CURSOR c_get_line_item(p_organization_id number,p_inventory_item_id number) IS
2074 select msl.segment1
2075 FROM mtl_system_items msl
2076 WHERE msl.inventory_item_id = p_inventory_item_id
2077 and msl.organization_id = p_organization_id;
2078 /* end -bug 13430127 */
2079 CURSOR c_get_dist_details IS
2080 SELECT aila.line_number, aila.match_type
2081 FROM ap_invoice_distributions_all aida,
2082 ap_invoice_lines_all aila
2083 WHERE aida.invoice_id = p_document_id
2084 AND aida.invoice_distribution_id = p_document_line_id
2085 AND aida.invoice_line_number = aila.line_number
2086 AND aila.invoice_id = p_document_id;
2087
2088 CURSOR get_po_matched_org_cur(pn_line_number NUMBER) IS
2089 SELECT po.ship_to_organization_id, po.ship_to_location_id
2090 FROM po_line_locations_all po,ap_invoice_lines_all ap
2091 WHERE po.line_location_id = ap.po_line_location_id
2092 AND ap.invoice_id = p_document_id
2093 AND ap.line_number = pn_line_number;
2094
2095 CURSOR get_rcpt_matched_org_cur(pn_line_number NUMBER) IS
2096 SELECT rcv.organization_id, rcv.location_id
2097 FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
2098 WHERE ap.rcv_transaction_id = rcv.transaction_id
2099 AND ap.invoice_id = p_document_id
2100 AND ap.line_number = pn_line_number;
2101
2102 CURSOR get_ap_inv_details IS
2103 SELECT pov.vendor_name party_name
2104 ,apa.invoice_num document_number
2105 ,apa.invoice_date document_date /* modified creation_date to invoice_date -13430127 */
2106 ,apa.invoice_id document_id
2107 ,aida.invoice_line_number document_line_id
2108 ,aida.invoice_line_number document_line_num
2109 ,NULL document_line_item
2110 ,NULL document_line_desc
2111 ,aida.quantity_invoiced document_line_qty --,NULL/*Modified for Bug 13430127 */
2112 ,aida.matched_uom_lookup_code document_line_uom --,NULL/*Modified for Bug 13430127 */
2113 ,decode(nvl(apla.amount,0),0,apla.original_amount,apla.amount) document_line_amt -- ,apla.amount /* Modified for Bug 13430127 */
2114 ,apa.payment_currency_code document_currency_code
2115 ,apla.inventory_item_id inventory_item_id
2116 ,apa.vendor_id party_id
2117 ,NULL organization_id
2118 ,NULL location_id
2119 from ap_invoices_all apa
2120 , ap_invoice_lines_all apla
2121 , po_vendors pov
2122 , ap_invoice_distributions_all aida
2123 where
2124 apa.invoice_id = p_document_id
2125 AND aida.invoice_id = p_document_id
2126 AND aida.invoice_distribution_id = p_document_line_id
2127 AND apa.invoice_id = apla.invoice_id
2128 AND apla.line_number = aida.invoice_line_number
2129 AND pov.vendor_id = apa.vendor_id ;
2130
2131 lr_dist_detail ap_invoice_distributions_all%ROWTYPE;
2132 ln_organization_id NUMBER;
2133 ln_location_id NUMBER;
2134 ln_line_number NUMBER;
2135 lv_match_type VARCHAR2(30);
2136 --Add by Xiao for POT Phase III changes, reg bug#12895841.
2137
2138 --Add by Xiao for POT change, reg bug#12533434 on 18-May-2011, begin
2139 --------------------------------------------------------------------------
2140 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
2141 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
2142 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
2143
2144 CURSOR c_get_st_accrual_date(p_organization_id NUMBER, p_location_id NUMBER) IS
2145 select to_date(attribute_value, 'DD/MM/YYYY')
2146 from JAI_RGM_ORG_REGNS_V
2147 where regime_id = (SELECT regime_id
2148 FROM jai_rgm_definitions
2149 where regime_code = jai_constants.service_regime
2150 )
2151 and organization_id = p_organization_id
2152 and location_id = p_location_id
2153 AND attribute_code = 'EFF_DATE_ST_PT'
2154 AND attribute_type_code = 'OTHERS'
2155 AND registration_type = 'OTHERS'
2156 AND (NOT EXISTS
2157 (select '1'
2158 from JAI_RGM_ORG_REGNS_V
2159 where regime_id = (SELECT regime_id
2160 FROM jai_rgm_definitions
2161 where regime_code = jai_constants.service_regime
2162 )
2163 and attribute_code IN 'INV_ORG_CLASSIFICATION'
2164 and attribute_value <> 'ORGANIZATION'
2165 and organization_id = p_organization_id
2166 and location_id = p_location_id)
2167 OR
2168 NOT EXISTS
2169 (select '1'
2170 from JAI_RGM_ORG_REGNS_V
2171 where regime_id = (SELECT regime_id
2172 FROM jai_rgm_definitions
2173 where regime_code = jai_constants.service_regime
2174 )
2175 and attribute_code IN 'SERVICE TYPE'
2176 and attribute_value <> 'OTHER'
2177 and organization_id = p_organization_id
2178 and location_id = p_location_id)
2179 );
2180 ------------------------------------------------------------------------------------
2181 -- Added by Chong.Lei for POT code port end
2182
2183 CURSOR get_ap_gl_date_cur(cp_line_num NUMBER) IS
2184 SELECT accounting_date
2185 FROM ap_invoice_distributions_all
2186 WHERE invoice_id = p_document_id
2187 AND invoice_line_number = cp_line_num;
2188
2189 CURSOR get_ar_gl_date_cur IS
2190 SELECT rda.gl_date
2191 FROM ra_cust_trx_line_gl_dist_all rda,
2192 ra_customer_trx_lines_all rla
2193 WHERE rda.customer_trx_id = p_document_id
2194 AND rda.account_class = 'REV'
2195 AND rda.customer_trx_line_id = rla.customer_trx_line_id
2196 --Modified by Qinglei 27-Dec-2011 for bug#13531399
2197 AND rla.customer_trx_line_id = p_document_line_id
2198 AND rla.customer_trx_id = p_document_id;
2199
2200 ld_st_accrual_date DATE;
2201 ld_gl_date DATE;
2202 --------------------------------------------------------------------------
2203 --Add by Xiao for POT change, reg bug#12533434 on 18-May-2011, end
2204
2205 begin
2206 --Add by Xiao for POT change, reg bug#12533434 on 18-May-2011, begin
2207 --------------------------------------------------------------------------
2208 --OPEN c_get_st_accrual_date; --Comment by Chong.Lei for POT code port
2209 OPEN c_get_st_accrual_date(r_ra_trx_details.organization_id, r_ra_trx_details.location_id); --add by Chong.Lei for POT code port
2210 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2211 CLOSE c_get_st_accrual_date;
2212
2213
2214 --------------------------------------------------------------------------
2215 --Add by Xiao for POT change, reg bug#12533434 on 18-May-2011, end
2216
2217 lv_member_name := 'GET_DOCUMENT_DETAILS';
2218 set_debug_context;
2219 p_process_flag := jai_constants.SUCCESSFUL;
2220 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
2221 , pn_reg_id => ln_reg_id
2222 );
2223
2224 jai_cmn_debug_contexts_pkg.print ( ln_reg_id
2225 ,'Call Parameters:' ||fnd_global.local_chr(10)
2226 ||'p_document_id =' ||p_document_id ||fnd_global.local_chr(10)
2227 ||'p_document_line_id =' ||p_document_line_id ||fnd_global.local_chr(10)
2228 ||'p_document_source =' ||p_document_source ||fnd_global.local_chr(10)
2229 );
2230
2231 fnd_file.put_line(fnd_file.log,' Call Parameters for get_document_details procedure :' ||fnd_global.local_chr(10)
2232 ||'p_document_id =' ||p_document_id ||fnd_global.local_chr(10)
2233 ||'p_document_line_id =' ||p_document_line_id ||fnd_global.local_chr(10)
2234 ||'p_document_source =' ||p_document_source ||fnd_global.local_chr(10)
2235 ); --12560704
2236
2237 if p_document_line_id is null or p_document_source is null then
2238 fnd_file.put_line(fnd_file.log,'Document references cannot be null, cannot continue to derive the document details'); --12560704
2239 p_process_message := 'Document references cannot be null, cannot continue to derive the document details';
2240 p_process_flag := jai_constants.EXPECTED_ERROR;
2241 return;
2242 end if;
2243 /* Commented the below PURCHASING and RECEIVING code for bug 13430127 */
2244 --
2245 -- Check for source and based on the source deligate control to respective procedure to fetch the details
2246 --
2247 /* if p_document_source = 'PURCHASING' then
2248 --open c_get_po_details;
2249 --Added parameters to cursor by vkantamn for Bug#6083978
2250 open c_get_po_details(p_document_id,p_document_line_id);
2251 fetch c_get_po_details into r_po_details;
2252 close c_get_po_details;
2253
2254 if r_po_details.document_id is null then
2255 fnd_file.put_line(fnd_file.log,'Purchase Order does not exists for po_header_id ='||p_document_id ||' and po_line_id='|| p_document_line_id); --12560704
2256 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2257 ,'Purchase Order does not exists for po_header_id ='||p_document_id ||' and po_line_id='|| p_document_line_id
2258 );
2259 end if;
2260
2261 fnd_file.put_line(fnd_file.log,'Purchase transaction details are r_po_details.document_id '||r_po_details.document_id||fnd_global.local_chr(10)
2262 ||'r_po_details.document_line_id =' ||r_po_details.document_line_id ||fnd_global.local_chr(10)
2263 ||'r_po_details.document_line_amt =' ||r_po_details.document_line_amt ||fnd_global.local_chr(10)
2264 ||'r_po_details.organization_id =' ||r_po_details.organization_id ||fnd_global.local_chr(10)
2265 ||'r_po_details.location_id =' ||r_po_details.location_id ||fnd_global.local_chr(10)); --12560704
2266
2267
2268 p_trx_repo_extract.transaction_source := 'PURCHASING' ;
2269 p_trx_repo_extract.party_name := r_po_details.party_name ;
2270 p_trx_repo_extract.document_number := r_po_details.document_number ;
2271 p_trx_repo_extract.document_date := r_po_details.document_date ;
2272 p_trx_repo_extract.document_id := r_po_details.document_id ;
2273 p_trx_repo_extract.document_line_id := r_po_details.document_line_id ;
2274 p_trx_repo_extract.document_line_num := r_po_details.document_line_num ;
2275 p_trx_repo_extract.document_line_item := r_po_details.document_line_item ;
2276 p_trx_repo_extract.document_line_desc := r_po_details.document_line_desc ;
2277 p_trx_repo_extract.document_line_qty := r_po_details.document_line_qty ;
2278 p_trx_repo_extract.document_line_uom := r_po_details.document_line_uom ;
2279 p_trx_repo_extract.document_line_amt := r_po_details.document_line_amt ;
2280 p_trx_repo_extract.document_currency_code := r_po_details.document_currency_code ;
2281 p_trx_repo_extract.inventory_item_id := r_po_details.inventory_item_id ;
2282 p_trx_repo_extract.party_id := r_po_details.party_id ;
2283 p_trx_repo_extract.organization_id := r_po_details.organization_id ;
2284 p_trx_repo_extract.location_id := r_po_details.location_id ;
2285
2286 open c_get_po_line_loc_srvtyp (cp_po_line_id => p_document_line_id) ;
2287 fetch c_get_po_line_loc_srvtyp into lv_service_type
2288 ,p_trx_repo_extract.repository_tax_amt;
2289 close c_get_po_line_loc_srvtyp;
2290 fnd_file.put_line(fnd_file.log,'Purchase transaction details p_trx_repo_extract.repository_tax_amt '||p_trx_repo_extract.repository_tax_amt);--12560704
2291 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
2292 -- if called from Repository Review UI then do not default service type from document
2293 p_trx_repo_extract.service_type_code := lv_service_type;
2294 end if;
2295
2296
2297 end if;
2298
2299 --Started addition by vkantamn for Bug#6083978
2300 --Bug 12902363 - Start
2301 --Fetch Document Details from Receipt instead of PO if Document Source is Receiving
2302 IF p_document_source = 'RECEIVING' THEN
2303
2304 open c_get_rcv_details;
2305 fetch c_get_rcv_details into v_organization_id,v_location_id,v_po_header_id,v_po_line_id,v_transaction_id;
2306 close c_get_rcv_details;
2307 fnd_file.put_line(fnd_file.log,'c_get_rcv_details details for po receipt v_organization_id= '||v_organization_id||fnd_global.local_chr(10)
2308 || ' v_location_id = '||v_location_id|| fnd_global.local_chr(10) || ' v_po_header_id = '||v_po_header_id||' v_po_line_id --> '||v_po_line_id ); --12560704
2309
2310 open c_get_receipt_details(v_transaction_id);
2311 fetch c_get_receipt_details into r_rcv_details;
2312 close c_get_receipt_details;
2313
2314 if r_rcv_details.document_id is null then
2315 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2316 ,'Receipt does not exists for transaction_id ='||v_transaction_id
2317 );
2318 end if;
2319
2320 p_trx_repo_extract.transaction_source := 'RECEIVING' ;
2321 p_trx_repo_extract.party_name := r_rcv_details.party_name ;
2322 p_trx_repo_extract.document_number := r_rcv_details.document_number ;
2323 p_trx_repo_extract.document_date := r_rcv_details.document_date ;
2324 p_trx_repo_extract.document_id := r_rcv_details.document_id ;
2325 p_trx_repo_extract.document_line_id := r_rcv_details.document_line_id ;
2326 p_trx_repo_extract.document_line_num := r_rcv_details.document_line_num ;
2327 p_trx_repo_extract.document_line_item := r_rcv_details.document_line_item ;
2328 p_trx_repo_extract.document_line_desc := r_rcv_details.document_line_desc ;
2329 p_trx_repo_extract.document_line_qty := r_rcv_details.document_line_qty ;
2330 p_trx_repo_extract.document_line_uom := r_rcv_details.document_line_uom ;
2331 p_trx_repo_extract.document_line_amt := r_rcv_details.document_line_amt ;
2332 p_trx_repo_extract.document_currency_code := r_rcv_details.document_currency_code ;
2333 p_trx_repo_extract.inventory_item_id := r_rcv_details.inventory_item_id ;
2334 p_trx_repo_extract.party_id := r_rcv_details.party_id ;
2335 p_trx_repo_extract.organization_id := v_organization_id ;
2336 --modified the IF clause for bug#8451703
2337 if nvl(v_location_id,0) = 0 then
2338 p_trx_repo_extract.location_id := r_rcv_details.location_id;
2339 else
2340 p_trx_repo_extract.location_id := v_location_id;
2341 end if;
2342 fnd_file.put_line(fnd_file.log,'Receiving transaction details are r_rcv_details.document_id '||r_rcv_details.document_id||fnd_global.local_chr(10)
2343 ||'r_rcv_details.document_line_id =' ||r_rcv_details.document_line_id ||fnd_global.local_chr(10)
2344 ||'r_rcv_details.document_line_amt =' ||r_rcv_details.document_line_amt ||fnd_global.local_chr(10)
2345 ||'r_rcv_details.organization_id =' ||r_rcv_details.organization_id ||fnd_global.local_chr(10)
2346 ||'r_rcv_details.location_id =' ||r_rcv_details.location_id ||fnd_global.local_chr(10));--12560704
2347
2348 open c_get_rcv_line_loc_srvtyp (cp_po_line_id => v_po_line_id) ;
2349 fetch c_get_rcv_line_loc_srvtyp into lv_service_type,p_trx_repo_extract.repository_tax_amt;
2350 close c_get_rcv_line_loc_srvtyp;
2351
2352 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
2353 -- if called from Repository Review UI then do not default service type from document
2354 p_trx_repo_extract.service_type_code := lv_service_type;
2355 end if;
2356
2357 END IF; */
2358 /*Bug 12902363 - End*/
2359 /*Addition done by vkantamn for Bug#6083978*/
2360
2361 if p_document_source = 'ORDER MANAGEMENT' then
2362
2363 open c_get_so_details;
2364 fetch c_get_so_details into r_so_details;
2365 close c_get_so_details;
2366
2367 if r_so_details.document_id is null then
2368 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2369 ,'Sales Order does not exists for header_id='||p_document_id ||' and line_id ='||p_document_line_id
2370 );
2371 end if;
2372
2373 p_trx_repo_extract.transaction_source := 'ORDER MANAGEMENT' ;
2374 p_trx_repo_extract.party_name := r_so_details.party_name ;
2375 p_trx_repo_extract.document_number := r_so_details.document_number ;
2376 p_trx_repo_extract.document_date := r_so_details.document_date ;
2377 p_trx_repo_extract.document_id := r_so_details.document_id ;
2378 p_trx_repo_extract.document_line_id := r_so_details.document_line_id ;
2379 p_trx_repo_extract.document_line_num := r_so_details.document_line_num ;
2380 p_trx_repo_extract.document_line_item := r_so_details.document_line_item ;
2381 p_trx_repo_extract.document_line_desc := r_so_details.document_line_desc ;
2382 p_trx_repo_extract.document_line_qty := r_so_details.document_line_qty ;
2383 p_trx_repo_extract.document_line_uom := r_so_details.document_line_uom ;
2384 p_trx_repo_extract.document_line_amt := r_so_details.document_line_amt ;
2385 p_trx_repo_extract.document_currency_code := r_so_details.document_currency_code ;
2386 p_trx_repo_extract.inventory_item_id := r_so_details.inventory_item_id ;
2387 p_trx_repo_extract.party_id := r_so_details.party_id ;
2388 p_trx_repo_extract.organization_id := r_so_details.organization_id ;
2389 --
2390 -- 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
2391 -- In order to derrive location complex logic needs to be implemented to check order type and based on that derive the location
2392 -- However here we already have a reference of invoice so it is better to derrive it from invoice only
2393 -- As an enahcement to this API a logic can be added here to derrive the location using order reference only
2394
2395 IF r_so_details.line_category_code = 'ORDER' THEN
2396
2397 open c_get_so_line_srvtyp (cp_line_id => p_document_line_id);
2398 fetch c_get_so_line_srvtyp into lv_service_type
2399 ,p_trx_repo_extract.repository_tax_amt;
2400 close c_get_so_line_srvtyp ;
2401
2402 ELSIF r_so_details.line_category_code = 'RETURN' THEN
2403
2404 open c_get_rma_line_srvtyp(cp_line_id => p_document_line_id);
2405 fetch c_get_rma_line_srvtyp into lv_service_type
2406 ,p_trx_repo_extract.repository_tax_amt;
2407 close c_get_rma_line_srvtyp ;
2408
2409 END IF;
2410
2411 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
2412 -- if called from Repository Review UI then do not default service type from document
2413 p_trx_repo_extract.service_type_code := lv_service_type;
2414 end if;
2415
2416
2417 end if;
2418
2419 -- Begin 5876390, 6012570
2420 if p_document_source = 'PROJECTS' then -- Projects Invoice
2421
2422 open c_get_pa_details;
2423 fetch c_get_pa_details into r_pa_details;
2424 close c_get_pa_details;
2425
2426 if r_pa_details.document_id is null then
2427 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2428 ,'Project Draft Invoice does not exists for p_document_id ='||p_document_id ||' and p_document_line_id='||p_document_line_id
2429 );
2430 end if;
2431 --Added by Qiong for bug13597785 begin
2432 -------------------------------------------
2433 open get_pa_trx_date;
2434 fetch get_pa_trx_date into l_document_date;
2435 close get_pa_trx_date;
2436 -------------------------------------------
2437 --Added by Qiong for bug13597785 end
2438
2439 p_trx_repo_extract.transaction_source := 'PROJECTS' ;
2440 p_trx_repo_extract.party_name := r_pa_details.party_name ;
2441 p_trx_repo_extract.document_number := r_pa_details.document_number ;
2442 --p_trx_repo_extract.document_date := r_pa_details.document_date ;
2443 p_trx_repo_extract.document_date := l_document_date ;
2444 --Changed document date by qiong for bug13597785
2445 p_trx_repo_extract.document_id := r_pa_details.document_id ;
2446 p_trx_repo_extract.document_line_id := r_pa_details.document_line_id ;
2447 p_trx_repo_extract.document_line_num := r_pa_details.document_line_num ;
2448 p_trx_repo_extract.document_line_item := r_pa_details.document_line_item ;
2449 p_trx_repo_extract.document_line_desc := r_pa_details.document_line_desc ;
2450 p_trx_repo_extract.document_line_qty := r_pa_details.document_line_qty ;
2451 p_trx_repo_extract.document_line_uom := r_pa_details.document_line_uom ;
2452 p_trx_repo_extract.document_line_amt := r_pa_details.document_line_amt ;
2453 p_trx_repo_extract.document_currency_code := r_pa_details.document_currency_code ;
2454 p_trx_repo_extract.inventory_item_id := r_pa_details.inventory_item_id ;
2455 p_trx_repo_extract.party_id := r_pa_details.party_id ;
2456 p_trx_repo_extract.organization_id := r_pa_details.organization_id ;
2457 p_trx_repo_extract.location_id := r_pa_details.location_id ;
2458 lv_service_type := r_pa_details.service_type_code ;
2459
2460 open c_get_pa_inv_line_tax (cp_line_id => r_pa_details.document_line_id);
2461 fetch c_get_pa_inv_line_tax into p_trx_repo_extract.repository_tax_amt;
2462 close c_get_pa_inv_line_tax ;
2463
2464 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
2465 -- if called from Repository Review UI then do not default service type from document
2466 p_trx_repo_extract.service_type_code := lv_service_type;
2467 end if;
2468
2469
2470 end if;
2471 -- End 5876390, 6012570
2472
2473 -- if p_document_source = 'RECEIVABLES' then --Comment by Chong.Lei for POT code port
2474 if p_document_source IN ( 'RECEIVABLES', 'AR_REVERSAL', 'AR_CLAIM' ) THEN --Modified by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
2475
2476 /*Added by Wenqiong for Advanced Receipts Begin*/
2477 -----------------------------------------------------
2478 IF p_source_trx_type IN (jai_constants.trx_type_adv_rcpts,jai_constants.trx_type_adv_rvs) THEN
2479 --Modified by Qinglei on 28-Feb-2012 for bug#13741544
2480 OPEN c_get_rcpt_trx_details;
2481 FETCH c_get_rcpt_trx_details INTO r_rcpt_trx_details;
2482 CLOSE c_get_rcpt_trx_details;
2483
2484 --Added by Qiong for bug13555753 begin
2485 ------------------------------------------------------------
2486 IF p_source_trx_type = jai_constants.trx_type_adv_rcpts THEN
2487 p_trx_repo_extract.document_date := r_rcpt_trx_details.document_date ;
2488 ELSIF p_source_trx_type = jai_constants.trx_type_adv_rvs THEN
2489 --Modified by Qinglei for bug#13741544
2490 p_trx_repo_extract.document_date := r_rcpt_trx_details.reversal_date ;
2491 END IF;
2492 ------------------------------------------------------------
2493 --Added by Qiong for bug13555753 end
2494
2495
2496 p_trx_repo_extract.transaction_source := p_document_source;--'RECEIVABLES';
2497 p_trx_repo_extract.party_name := r_rcpt_trx_details.party_name ;
2498 p_trx_repo_extract.document_number := r_rcpt_trx_details.document_number ;
2499 --p_trx_repo_extract.document_date := r_rcpt_trx_details.document_date ;commented by Qiong for bug13555753
2500 p_trx_repo_extract.document_id := r_rcpt_trx_details.document_id ;
2501 p_trx_repo_extract.document_line_id := r_rcpt_trx_details.document_id ;
2502 p_trx_repo_extract.document_line_num := 1 ;
2503 p_trx_repo_extract.document_line_amt := r_rcpt_trx_details.document_line_amt ;
2504 p_trx_repo_extract.document_currency_code := r_rcpt_trx_details.document_currency_code ;
2505 p_trx_repo_extract.party_id := r_rcpt_trx_details.party_id ;
2506 p_trx_repo_extract.organization_id := r_rcpt_trx_details.organization_id ;
2507 p_trx_repo_extract.location_id := r_rcpt_trx_details.location_id ;
2508 p_trx_repo_extract.service_type_code := r_rcpt_trx_details.service_type_code ;
2509
2510
2511 OPEN get_rcpt_amt_cur;
2512 FETCH get_rcpt_amt_cur INTO p_trx_repo_extract.repository_tax_amt;
2513 CLOSE get_rcpt_amt_cur;
2514
2515 IF p_source_trx_type = jai_constants.trx_type_adv_rvs THEN
2516 --Modified by Qinglei for bug#13741544
2517 p_trx_repo_extract.document_line_amt := -abs(p_trx_repo_extract.document_line_amt);
2518 END IF;
2519 ELSIF p_source_trx_type IN (jai_constants.trx_type_adv_app,jai_constants.trx_type_adv_unapp) THEN
2520 OPEN c_get_rcpt_app_trx_details;
2521 FETCH c_get_rcpt_app_trx_details INTO r_rcpt_app_trx_details;
2522 CLOSE c_get_rcpt_app_trx_details;
2523
2524 /*Modified by Qinglei on 05-Jan-2011 for bug#13556198 begin*/
2525 ln_converted_rate := jai_cmn_utils_pkg.currency_conversion(r_rcpt_app_trx_details.set_of_books_id,
2526 r_rcpt_app_trx_details.receipt_currency_code,
2527 r_rcpt_app_trx_details.receipt_exchange_date,
2528 r_rcpt_app_trx_details.receipt_exchange_rate_type,
2529 r_rcpt_app_trx_details.receipt_exchange_rate);
2530 /*Modified by Qinglei on 05-Jan-2011 for bug#13556198 end*/
2531 p_trx_repo_extract.transaction_source := p_document_source;--'RECEIVABLES';
2532 p_trx_repo_extract.party_name := r_rcpt_app_trx_details.party_name ;
2533 p_trx_repo_extract.document_number := r_rcpt_app_trx_details.document_number ;
2534 p_trx_repo_extract.document_date := r_rcpt_app_trx_details.document_date ;
2535 p_trx_repo_extract.document_id := r_rcpt_app_trx_details.document_id ;
2536 p_trx_repo_extract.document_line_id := r_rcpt_app_trx_details.document_id ;
2537 p_trx_repo_extract.document_line_num := NULL ;
2538 p_trx_repo_extract.document_line_amt := r_rcpt_app_trx_details.document_line_amt/NVL(ln_converted_rate,1);
2539 --Modified by Qinglei on 05-Jan-2011 for bug#13556198
2540 p_trx_repo_extract.document_currency_code := r_rcpt_app_trx_details.document_currency_code ;
2541 p_trx_repo_extract.party_id := r_rcpt_app_trx_details.party_id ;
2542 p_trx_repo_extract.organization_id := r_rcpt_app_trx_details.organization_id ;
2543 p_trx_repo_extract.location_id := r_rcpt_app_trx_details.location_id ;
2544 p_trx_repo_extract.service_type_code := r_rcpt_app_trx_details.service_type_code ;
2545
2546
2547 OPEN get_rcpt_app_amt_cur;
2548 FETCH get_rcpt_app_amt_cur INTO p_trx_repo_extract.repository_tax_amt;
2549 CLOSE get_rcpt_app_amt_cur;
2550 IF p_source_trx_type = jai_constants.trx_type_adv_app THEN
2551 p_trx_repo_extract.document_line_amt := -abs(p_trx_repo_extract.document_line_amt);
2552 ELSE
2553 p_trx_repo_extract.document_line_amt := abs(p_trx_repo_extract.document_line_amt);
2554 END IF;
2555
2556 -----------------------------------------------------
2557 /*Added by Wenqiong for Advanced Receipts End*/
2558 ELSE
2559 open c_get_ra_trx_details;
2560 fetch c_get_ra_trx_details into r_ra_trx_details;
2561 close c_get_ra_trx_details;
2562
2563 if r_ra_trx_details.document_id is null then
2564 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2565 ,'AR Transaction does not exists for ra_customer_trx_id='||p_document_id ||' and customer_trx_line_id='||p_document_line_id
2566 );
2567 end if;
2568 -- p_trx_repo_extract.transaction_source := 'RECEIVABLES' ; --Comment by Chong.Lei for POT code port
2569 p_trx_repo_extract.transaction_source := p_document_source;--'RECEIVABLES';--Modified by Xiao for POT III, reg bug#12895841. --Added by Chong.Lei for POT code port
2570 p_trx_repo_extract.party_name := r_ra_trx_details.party_name ;
2571 p_trx_repo_extract.document_number := r_ra_trx_details.document_number ;
2572 p_trx_repo_extract.document_date := r_ra_trx_details.document_date ;
2573 p_trx_repo_extract.document_id := r_ra_trx_details.document_id ;
2574 p_trx_repo_extract.document_line_id := r_ra_trx_details.document_line_id ;
2575 p_trx_repo_extract.document_line_num := r_ra_trx_details.document_line_num ;
2576 p_trx_repo_extract.document_line_item := r_ra_trx_details.document_line_item ;
2577 p_trx_repo_extract.document_line_desc := r_ra_trx_details.document_line_desc ;
2578 p_trx_repo_extract.document_line_qty := r_ra_trx_details.document_line_qty ;
2579 p_trx_repo_extract.document_line_uom := r_ra_trx_details.document_line_uom ;
2580 p_trx_repo_extract.document_line_amt := r_ra_trx_details.document_line_amt ; /*Bug 11821537 - Uncommented this line*/
2581 p_trx_repo_extract.document_currency_code := r_ra_trx_details.document_currency_code ;
2582 p_trx_repo_extract.inventory_item_id := r_ra_trx_details.inventory_item_id ;
2583 p_trx_repo_extract.party_id := r_ra_trx_details.party_id ;
2584 p_trx_repo_extract.organization_id := r_ra_trx_details.organization_id ;
2585 p_trx_repo_extract.location_id := r_ra_trx_details.location_id ;
2586
2587 -- open c_get_ra_line_srvtyp (cp_customer_trx_line_id => p_document_line_id) ; --Comment by Chong.Lei for POT code port
2588 -- Added by Chong.Lei for POT code port begin
2589 open c_get_ra_line_srvtyp (cp_customer_trx_line_id => p_document_line_id,
2590 pv_document_source => p_document_source--Add by Xiao for POT Phase III, reg bug#12895841.
2591 ) ;
2592 -- Added by Chong.Lei for POT code port end
2593 fetch c_get_ra_line_srvtyp into lv_service_type
2594 ,p_trx_repo_extract.repository_tax_amt;
2595 close c_get_ra_line_srvtyp ;
2596 -- Added by Chong.Lei for POT code port begin
2597 ------------------------------------------------------------------------------------
2598 --Add by Xiao for POT Phase III, reg bug#12895841.
2599 IF p_document_source = 'AR_REVERSAL' THEN
2600 p_trx_repo_extract.repository_tax_amt := -p_trx_repo_extract.repository_tax_amt;
2601 END IF;
2602
2603
2604 IF p_document_source = 'AR_CLAIM' THEN
2605 OPEN get_claim_amt_cur;
2606 fetch get_claim_amt_cur INTO p_trx_repo_extract.repository_tax_amt;
2607 CLOSE get_claim_amt_cur;
2608
2609 END IF;
2610
2611 --Add by Xiao for POT Phase III, reg bug#12895841.
2612 ------------------------------------------------------------------------------------
2613 -- Added by Chong.Lei for POT code port end
2614
2615 /*Bug 11821537 - Line Amount can be fetched from r_ra_trx_details itself as partial payment is not an issue
2616 as liability arises during Invoice creation and not during payment*/
2617 /*Bug 11932841 - Pass Document Line ID to get Tax and Line amount per line*/
2618 /*Following code needs to be uncommented for Cash Basis processing prior to Service Tax Point of Taxation*/
2619 --Added for bug#9298508 by JMEENA
2620 --Add by Xiao for POT changes, reg bug#12533434, begin
2621 -----------------------------------------------------------------------
2622 OPEN get_ar_gl_date_cur;
2623 FETCH get_ar_gl_date_cur INTO ld_gl_date;
2624 CLOSE get_ar_gl_date_cur;
2625
2626 -- Added by Chong.Lei for POT code port begin
2627 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/
2628 OPEN c_get_st_accrual_date(r_ra_trx_details.organization_id, r_ra_trx_details.location_id);
2629 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2630 CLOSE c_get_st_accrual_date;
2631 -- Added by Chong.Lei for POT code port end
2632
2633 IF ld_gl_date < ld_st_accrual_date OR ld_st_accrual_date IS NULL THEN
2634 --ADDED OR ld_st_accrual_date IS NULL THEN for bug#13323182
2635 -----------------------------------------------------------------------
2636 --Add by Xiao for POT changes, reg bug#12533434, end
2637
2638 OPEN c_get_ra_tax_amt_applied (p_document_id, p_document_line_id);
2639 FETCH c_get_ra_tax_amt_applied INTO p_trx_repo_extract.repository_tax_amt;
2640 CLOSE c_get_ra_tax_amt_applied;
2641
2642 OPEN c_get_ra_line_amt_applied (p_document_id, p_document_line_id);
2643 FETCH c_get_ra_line_amt_applied INTO p_trx_repo_extract.document_line_amt;
2644 CLOSE c_get_ra_line_amt_applied;
2645 END IF;
2646
2647 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
2648 -- if called from Repository Review UI then do not default service type from document
2649 p_trx_repo_extract.service_type_code := lv_service_type;
2650 end if;
2651
2652 END IF;
2653 end if;--END 'RECEIVABLES', 'AR_REVERSAL', 'AR_CLAIM'
2654
2655 /* Below code is added to process the STANDALONE INVOICE
2656 Bug#8943349 by JMEENA
2657 */
2658 /* Added 'RECEIVING','PURCHASING' for bug 13430127 */
2659 --if p_document_source = 'STANDALONE_INVOICE' THEN --Comment by Chong.Lei for POT code port
2660 if p_document_source IN ( 'STANDALONE_INVOICE', 'AP_REVERSAL', 'AP_CLAIM', 'RECEIVING','PURCHASING' ) THEN --Modified by Xiao for POT III, reg bug#12895841. --added by Chong.Lei for POT code port
2661
2662 OPEN c_get_standalone_inv_details;
2663 FETCH c_get_standalone_inv_details INTO r_ap_details;
2664 CLOSE c_get_standalone_inv_details;
2665 -- fnd_file.put_line(fnd_file.log,'Standalone AP invoice details are ln_repository_tax_amount '||ln_repository_tax_amount);--12560704 --Comment by Chong.Lei for POT code port
2666 -- fnd_file.put_line(fnd_file.log,'Standalone AP invoice details are ln_payment_amount '||ln_payment_amount);--12560704 --Comment by Chong.Lei for POT code port
2667 -- Added by Chong.Lei for POT code port begin
2668 --Add by Xiao for POT Phase III, reg bug#12895841
2669 IF r_ap_details.document_number IS NULL THEN
2670 OPEN c_get_dist_details;
2671 FETCH c_get_dist_details INTO ln_line_number, lv_match_type;
2672 CLOSE c_get_dist_details;
2673
2674 OPEN get_ap_inv_details;
2675 FETCH get_ap_inv_details INTO r_ap_details;
2676 CLOSE get_ap_inv_details;
2677
2678 IF lv_match_type = 'ITEM_TO_PO' THEN
2679 OPEN get_po_matched_org_cur(ln_line_number);
2680 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
2681 CLOSE get_po_matched_org_cur;
2682 ELSE
2683 OPEN get_rcpt_matched_org_cur(ln_line_number);
2684 FETCH get_rcpt_matched_org_cur INTO ln_organization_id,ln_location_id;
2685 CLOSE get_rcpt_matched_org_cur;
2686 IF ln_organization_id IS NULL OR ln_location_id IS NULL THEN
2687 OPEN get_po_matched_org_cur(ln_line_number);
2688 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
2689 CLOSE get_po_matched_org_cur;
2690 END IF;
2691 END IF;
2692
2693 r_ap_details.organization_id := ln_organization_id;
2694 r_ap_details.location_id := ln_location_id;
2695 END IF;
2696 --Add by Xiao for POT Phase III, reg bug#12895841
2697 -- Added by Chong.Lei for POT code port end
2698
2699 p_trx_repo_extract.transaction_source := p_document_source;--'STANDALONE_INVOICE';--Modified by Xiao for POT III, reg bug#12895841. --added by Chong.Lei for POT code port
2700 p_trx_repo_extract.party_name := r_ap_details.party_name ;
2701 p_trx_repo_extract.document_number := r_ap_details.document_number ;
2702 p_trx_repo_extract.document_date := r_ap_details.document_date ;
2703 p_trx_repo_extract.document_id := r_ap_details.document_id ;
2704 p_trx_repo_extract.document_line_id := r_ap_details.document_line_id ;
2705 p_trx_repo_extract.document_line_num := r_ap_details.document_line_num ;
2706 p_trx_repo_extract.document_line_item := r_ap_details.document_line_item ;
2707 p_trx_repo_extract.document_line_desc := r_ap_details.document_line_desc ;
2708 p_trx_repo_extract.document_line_qty := r_ap_details.document_line_qty ;
2709 p_trx_repo_extract.document_line_uom := r_ap_details.document_line_uom ;
2710 -- p_trx_repo_extract.document_line_amt := r_ap_details.document_line_amt ; --Xiao for POT change, reg bug#12533434 --Comment by Chong.Lei for POT code port
2711 -- p_trx_repo_extract.document_currency_code := r_ap_details.document_currency_code ; --Comment by Chong.Lei for POT code port
2712 -- Added by Chong.Lei for POT code port begin
2713 p_trx_repo_extract.document_line_amt := r_ap_details.document_line_amt ;
2714 -- modified below for the bug 14341945
2715 p_trx_repo_extract.document_currency_code := r_ap_details.document_currency_code ;--Xiao for POT change, reg bug#12533434, reg bug#12895841
2716 -- Added by Chong.Lei for POT code port end
2717 p_trx_repo_extract.inventory_item_id := r_ap_details.inventory_item_id ;
2718 p_trx_repo_extract.party_id := r_ap_details.party_id ;
2719 /*
2720 OPEN c_get_standalone_org_loc;
2721 FETCH c_get_standalone_org_loc INTO r_standalone_org_loc;
2722 CLOSE c_get_standalone_org_loc;
2723
2724 p_trx_repo_extract.organization_id := r_standalone_org_loc.organization_id ;
2725 p_trx_repo_extract.location_id := r_standalone_org_loc.location_id ;
2726 */
2727 /*10434986 - Start*/
2728 p_trx_repo_extract.organization_id := r_ap_details.organization_id ;
2729 p_trx_repo_extract.location_id := r_ap_details.location_id ;
2730
2731 OPEN c_get_standalone_inv_payment;
2732 FETCH c_get_standalone_inv_payment INTO ln_payment_amount;
2733 CLOSE c_get_standalone_inv_payment;
2734 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 Begin*/
2735 OPEN c_get_inv_prepay;
2736 FETCH c_get_inv_prepay INTO ln_prepay_amount;
2737 CLOSE c_get_inv_prepay;
2738
2739 OPEN c_total_ap_amount;
2740 FETCH c_total_ap_amount INTO ln_total_inv_amount;
2741 CLOSE c_total_ap_amount;
2742
2743 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 End*/
2744 --Added by Qiong for bug#13405553 BEGIN
2745 ---------------------------------------------
2746 IF p_document_source = 'AP_CLAIM' then
2747 OPEN c_get_claim_amount(p_document_source);
2748 FETCH c_get_claim_amount INTO ln_repository_tax_amount;
2749 CLOSE c_get_claim_amount;
2750 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 Begin*/
2751 OPEN c_get_claim_paid_amount;
2752 FETCH c_get_claim_paid_amount INTO ln_payment_amount;
2753 CLOSE c_get_claim_paid_amount;
2754
2755 p_trx_repo_extract.document_line_amt :=round(r_ap_details.document_line_amt * ln_payment_amount / ln_total_inv_amount,2);
2756 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 End*/
2757 p_trx_repo_extract.inventory_item_id := p_taxline_no;--Added by Qiong for bug13598199
2758 -----------------------------------------------------
2759 --Added by Qiong for bug#13405553 End
2760 ELSE
2761 OPEN c_get_repo_amount(p_document_source);--Add p_document_source by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
2762 FETCH c_get_repo_amount INTO ln_repository_tax_amount;
2763 CLOSE c_get_repo_amount;
2764 END IF;
2765
2766 -- Added by Chong.Lei for POT code port begin
2767 --Add by Xiao for POT Phase III, reg bug#12895841.
2768 IF p_document_source = 'AP_REVERSAL' THEN
2769 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 Begin*/
2770 OPEN c_get_open_amount;
2771 FETCH c_get_open_amount INTO ln_open_amt,ln_total_original_amt;
2772 CLOSE c_get_open_amount;
2773 IF nvl(ln_total_original_amt,0) <> 0 THEN
2774 ln_open_amt := nvl(ln_open_amt,0)/nvl(ln_total_original_amt,0);
2775 p_trx_repo_extract.document_line_amt := round(p_trx_repo_extract.document_line_amt * ln_open_amt,2);
2776 END IF;
2777 /* Added by Wenqiong for bug13462951 on Feb 09, 2012 End*/
2778 ln_repository_tax_amount := -ln_repository_tax_amount;
2779 END IF;
2780 --Add by Xiao for POT Phase III, reg bug#12895841.
2781 -- Added by Chong.Lei for POT code port end
2782 OPEN c_get_service_type;
2783 FETCH c_get_service_type INTO lv_service_type;
2784 CLOSE c_get_service_type;
2785
2786 --Add by Xiao for POT changes, reg bug#12533434, begin
2787 -----------------------------------------------------------------------
2788 OPEN get_ap_gl_date_cur(p_trx_repo_extract.document_line_num);-- Updated by Wenqiong for bug13462951 on Feb 09, 2012, add a parameter line num
2789 FETCH get_ap_gl_date_cur INTO ld_gl_date;
2790 CLOSE get_ap_gl_date_cur;
2791
2792 -- Added by Chong.Lei for POT code port begin
2793 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/
2794 OPEN c_get_st_accrual_date(r_ap_details.organization_id, r_ap_details.location_id);
2795 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2796 CLOSE c_get_st_accrual_date;
2797 -- Added by Chong.Lei for POT code port end
2798
2799
2800 IF ld_gl_date < ld_st_accrual_date OR ld_st_accrual_date IS NULL THEN
2801 --ADDED OR ld_st_accrual_date IS NULL THEN for bug#13323182
2802 -----------------------------------------------------------------------
2803 --Add by Xiao for POT changes, reg bug#12533434, end
2804 --Added by Wenqiong for 13462951 on Feb 09, 2012 begin
2805 ln_payment_amount := ln_prepay_amount + ln_payment_amount;
2806 p_trx_repo_extract.document_line_amt := (r_ap_details.document_line_amt) * ln_payment_amount / ln_total_inv_amount;
2807 --p_trx_repo_extract.document_line_amt := ln_payment_amount - ln_repository_tax_amount; comment out by Wenqiong on Feb 09, 2012
2808 --Added by Wenqiong for 13462951 on Feb 09, 2012 end
2809 END IF;
2810
2811 p_trx_repo_extract.repository_tax_amt := ln_repository_tax_amount;
2812 fnd_file.put_line(fnd_file.log,'Standalone AP invoice details p_trx_repo_extract.repository_tax_amt '||p_trx_repo_extract.repository_tax_amt);--12560704
2813
2814 fnd_file.put_line(fnd_file.log,'Standalone AP invoice details are r_ap_details.document_id '||r_ap_details.document_id||fnd_global.local_chr(10)
2815 ||'r_ap_details.document_line_id =' ||r_ap_details.document_line_id ||fnd_global.local_chr(10)
2816 ||'r_ap_details.document_line_num =' ||r_ap_details.document_line_num ||fnd_global.local_chr(10)
2817 ||'r_ap_details.document_line_qty =' ||r_ap_details.document_line_qty ||fnd_global.local_chr(10)
2818 ||'p_trx_repo_extract.document_line_amt =' ||p_trx_repo_extract.document_line_amt ||fnd_global.local_chr(10)
2819 -- comment by Chong.Lei for POT code port start
2820 --||'r_ap_details.organization_id =' ||r_ap_details.organization_id ||fnd_global.local_chr(10)
2821 --||'r_ap_details.location_id =' ||r_ap_details.location_id ||fnd_global.local_chr(10)
2822 );--12560704
2823 -- comment by Chong.Lei for POT code port end
2824 /*10434986 - End*/
2825 /*
2826 OPEN c_get_standalone_inv_line_tax (r_ap_details.document_id, nvl(p_taxline_no,r_ap_details.document_line_num));
2827 --added nvl(p_taxline_no, for bug#10085619,such that the tax amount of the particualr tax line will be fetched
2828 --and the same will be updated in repository
2829 FETCH c_get_standalone_inv_line_tax INTO lv_service_type,p_trx_repo_extract.repository_tax_amt;
2830 CLOSE c_get_standalone_inv_line_tax;
2831 */
2832
2833 if nvl(p_called_from,'$#$') not in ('JAINRPRW') then
2834 p_trx_repo_extract.service_type_code := lv_service_type;
2835 end if;
2836 END IF;
2837 --End of bug#8943349
2838 /** Deregister procedure and return*/
2839 <<deregister_and_return>>
2840 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
2841
2842 exception
2843 when others then
2844 p_process_flag := jai_constants.unexpected_error;
2845 p_process_message := lv_context||'->'||sqlerrm;
2846 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
2847 jai_cmn_debug_contexts_pkg.print_stack;
2848 end get_document_details ;
2849
2850 /*------------------------------------------------------------------------------------------------------------*/
2851 -- Overloading the GET_DOC_FROM_REFERENCE to return only organization_id, location_id and service_type_code for a given document reference
2852 procedure get_doc_from_reference
2853 ( p_reference_id in number
2854 , p_source_trx_type in varchar2 default NULL
2855 , p_application_id in number default NULL
2856 , p_organization_id out nocopy number
2857 , p_location_id out nocopy number
2858 , p_service_type_code out nocopy varchar2
2859 , p_process_flag out nocopy varchar2
2860 , p_process_message out nocopy varchar2
2861 )
2862 is
2863 lr_trx_repo_ext_rec jai_trx_repo_extract_gt%rowtype;
2864 begin
2865
2866 -- Delegate the call to actual procedure that derrived the document details from refernece
2867 get_doc_from_reference
2868 ( p_reference_id => p_reference_id
2869 , p_source_trx_type => p_source_trx_type
2870 , p_application_id => p_application_id
2871 , p_trx_repo_extract_rec => lr_trx_repo_ext_rec
2872 , p_process_flag => p_process_flag
2873 , p_process_message => p_process_message
2874 );
2875 if p_process_flag = jai_constants.SUCCESSFUL then
2876 p_organization_id := lr_trx_repo_ext_rec.organization_id;
2877 p_location_id := lr_trx_repo_ext_rec.location_id;
2878 p_service_type_code := lr_trx_repo_ext_rec.service_type_code;
2879 end if;
2880
2881
2882 end get_doc_from_reference ;
2883
2884 /*------------------------------------------------------------------------------------------------------------*/
2885 procedure get_doc_from_reference
2886 ( p_reference_id in number
2887 , p_source_trx_type in varchar2 default NULL
2888 , p_application_id in number default NULL
2889 , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
2890 , p_process_flag out nocopy varchar2
2891 , p_process_message out nocopy varchar2
2892 )
2893 is
2894
2895 cursor c_get_refs_rec
2896 is
2897 select reference_id
2898 , source
2899 , invoice_id
2900 , item_line_id
2901 , line_id /*Added by nprashar for bug # 7172723*/
2902 from jai_rgm_trx_refs refs
2903 where refs.reference_id = p_reference_id;
2904
2905 /*Addition of code by nprashar for bug # 7172723*/
2906 cursor c_get_source_type
2907 is
2908 select source
2909 from ap_invoices_all aia
2910 where aia.invoice_id in
2911 (select invoice_id
2912 from jai_rgm_Trx_refs refs
2913 where refs.reference_id = p_reference_id);
2914
2915 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)
2916 is
2917 select inv_dist_id,
2918 line_num
2919 from
2920 (select
2921 INVOICE_DISTRIBUTION_ID inv_dist_id,
2922 row_number() over(ORDER BY INVOICE_DISTRIBUTION_ID) line_num
2923 from ap_invoice_distributions_all
2924 where INVOICE_ID=p_invoice_id
2925 )
2926 where inv_dist_id=p_line_id;
2927
2928 lr_line_number number;
2929 lr_inv_dist_id ap_invoice_distributions_all.INVOICE_DISTRIBUTION_ID%type;
2930
2931 cursor c_get_doc_details(p_invoice_id jai_rgm_trx_refs.invoice_id%type,p_row_number number)
2932 is
2933 SELECT shipment_header_id,
2934 shipment_line_id,
2935 receipt_num,
2936 creation_date,
2937 qty_received,
2938 tax_amount,
2939 organization_id,
2940 inventory_item_id,
2941 uom_code,
2942 location_id,
2943 vendor_id,
2944 vendor_site_id
2945 FROM
2946 (SELECT jrt.shipment_header_id shipment_header_id,
2947 jrt.shipment_line_id shipment_line_id,
2948 jrt.receipt_num receipt_num,
2949 jrt.creation_date creation_date,
2950 jrt.qty_received qty_received,
2951 jrtxl.tax_amount tax_amount,
2952 jrt.organization_id organization_id,
2953 jrt.inventory_item_id inventory_item_id,
2954 jirt.uom_code uom_code,
2955 jirt.location_id location_id,
2956 jrti.vendor_id vendor_id,
2957 jrti.vendor_site_id vendor_site_id,
2958 row_number() over(
2959 ORDER BY jrtxl.shipment_line_id,jrtxl.tax_line_no) rn
2960 FROM jai_rcv_lines jrt,
2961 jai_rcv_transactions jirt,/* modified by vumaasha for bug 8965721 */
2962 jai_rcv_tp_invoices jrti,
2963 jai_rcv_line_taxes jrtxl -- join to ja_in_receipt_tax_lines added by vumaasha for 6856213
2964 WHERE jrt.shipment_header_id = jrti.shipment_header_id
2965 AND jrti.invoice_id = p_invoice_id
2966 AND jrti.shipment_header_id = jirt.shipment_header_id
2967 AND jirt.transaction_type = 'RECEIVE'
2968 AND jirt.shipment_line_id = jrt.shipment_line_id
2969 AND jrtxl.shipment_header_id = jirt.shipment_header_id
2970 AND jrtxl.shipment_header_id = jrti.shipment_header_id
2971 AND jirt.shipment_line_id = jrtxl.shipment_line_id)
2972 WHERE rn =p_row_number ;
2973
2974 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)
2975 is
2976 select service_type_code
2977 from
2978 jai_cmn_vendor_sites
2979 where vendor_id= p_vendor_id
2980 and vendor_site_id=p_vendor_site_id;
2981
2982 lr_service_type jai_rgm_trx_records.service_type_code%type;
2983 lr_called_from varchar2(20) default null;
2984
2985 /*Addition Ends for bug 7172723*/
2986
2987 lr_refs_rec c_get_refs_rec%rowtype;
2988 ln_doc_id number;
2989 ln_doc_line_id number;
2990 lv_trx_src jai_rgm_trx_records.source%type;
2991 lr_source_type ap_invoices_all.source%type;
2992 lr_doc_details c_get_doc_details%rowtype;
2993
2994 begin
2995
2996 /*
2997 p_trx_repo_extract_rec.service_type_code := '105-E';
2998 p_trx_repo_extract_rec.organization_id := 2832 ;
2999 p_trx_repo_extract_rec.location_id := 10023 ;
3000 */
3001 open c_get_refs_rec;
3002 fetch c_get_refs_rec into lr_refs_rec;
3003 close c_get_refs_rec;
3004
3005 if lr_refs_rec.reference_id is null then
3006 p_process_flag := jai_constants.EXPECTED_ERROR;
3007 p_process_message := 'Invalid reference id. Unable to location a repository reference for P_REFERENCE_ID='||p_reference_id;
3008 return;
3009 end if;
3010
3011 if lr_refs_rec.source is null
3012 or lr_refs_rec.invoice_id is null
3013 or lr_refs_rec.item_line_id is null
3014 then
3015 p_process_flag := jai_constants.EXPECTED_ERROR;
3016 p_process_message := 'Unable to find transaction references in the repository. Source='|| lr_refs_rec.source
3017 ||', InvoiceID='|| lr_refs_rec.invoice_id
3018 ||', ItemLineID=' ||lr_refs_rec.item_line_id ;
3019 return;
3020 end if;
3021
3022 /* For Bug# 7172723 */
3023 IF lr_refs_rec.source = 'AP' THEN --Added by Bgowrava For bug#9385880
3024 open c_get_source_type; /* To get the source type */
3025 fetch c_get_source_type into lr_source_type;
3026 close c_get_source_type;
3027 END IF; --Added by Bgowrava For bug#9385880
3028 /* if source = Recipt ,for 3rd party invoices */
3029 /*commenting the below code for bug#10085619,as get_document_details will fetch the details of purchasing also
3030 if NVL(lr_source_type,'$$$') = 'INDIA TAX INVOICE' THEN
3031 Added by nprashar for bug # 7172723*
3032 -- open cursor c_get_line_number for invoice_id to fetch the exact delivery location number when multiple receipts lines are there.*
3033 lr_line_number :=1;
3034 open c_get_line_number(lr_refs_rec.invoice_id,lr_refs_rec.line_id);
3035 fetch c_get_line_number into lr_inv_dist_id,lr_line_number;
3036 close c_get_line_number;
3037 -- /* get information from receipt
3038
3039 open c_get_doc_details(lr_refs_rec.invoice_id,lr_line_number);
3040 fetch c_get_doc_details into lr_doc_details;
3041 close c_get_doc_details;
3042
3043 p_trx_repo_extract_rec.transaction_source := 'PURCHASING' ;
3044 -- p_trx_repo_extract.party_name := lr_doc_details.party_name ;
3045 p_trx_repo_extract_rec.document_number := lr_doc_details.receipt_num ;
3046 p_trx_repo_extract_rec.document_date := lr_doc_details.creation_date ;
3047 --p_trx_repo_extract.document_id := lr_doc_details.document_id ;
3048 --p_trx_repo_extract.document_line_id := lr_doc_details.document_line_id ;
3049 -- p_trx_repo_extract.document_line_num := lr_doc_details.document_line_num ;
3050 -- p_trx_repo_extract.document_line_item := lr_doc_details.document_line_item ;
3051 -- p_trx_repo_extract.document_line_desc := lr_doc_details.document_line_desc ;
3052 p_trx_repo_extract_rec.document_line_qty := lr_doc_details.qty_received ;
3053 p_trx_repo_extract_rec.document_line_uom := lr_doc_details.uom_code ;
3054 p_trx_repo_extract_rec.document_line_amt := lr_doc_details.tax_amount ;
3055 -- p_trx_repo_extract.document_currency_code := lr_doc_details.document_currency_code ;
3056 p_trx_repo_extract_rec.inventory_item_id := lr_doc_details.inventory_item_id ;
3057 p_trx_repo_extract_rec.party_id := lr_doc_details.vendor_id ;
3058 p_trx_repo_extract_rec.organization_id := lr_doc_details.organization_id ;
3059 p_trx_repo_extract_rec.location_id := lr_doc_details.location_id ;
3060
3061 --get service type from vendor addition information
3062 open c_get_ra_line_srvtyp(lr_doc_details.vendor_id,lr_doc_details.vendor_site_id);
3063 fetch c_get_ra_line_srvtyp into lr_service_type;
3064 close c_get_ra_line_srvtyp;
3065 -- if no service type for vendor and vendor site then get the service type for vendor null site
3066 if lr_service_type is null then
3067 open c_get_ra_line_srvtyp(lr_doc_details.vendor_id,'0');
3068 fetch c_get_ra_line_srvtyp into lr_service_type;
3069 close c_get_ra_line_srvtyp;
3070 end if;
3071 if nvl(lr_called_from,'$#$') not in ('JAINRPRW') then
3072 -- if called from Repository Review UI then do not default service type from document
3073 p_trx_repo_extract_rec.service_type_code := lr_service_type;
3074 end if;
3075
3076 p_process_flag := jai_constants.SUCCESSFUL;
3077 ELSE
3078 *//*end commenting for bug#10085619*/
3079 --Add by Qiong for Advanced Receipts bug#13361952 Begin
3080 --------------------------------------------------------
3081 IF lr_refs_rec.source = 'AR' AND p_source_trx_type in (jai_constants.trx_type_adv_app,jai_constants.trx_type_adv_unapp)
3082 THEN
3083
3084 jai_trx_repo_extract_pkg.derrive_doc_from_ref
3085 ( p_reference_source => lr_refs_rec.source
3086 , p_source_trx_type => p_source_trx_type
3087 , p_reference_invoice_id => p_application_id
3088 , p_reference_item_line_id => lr_refs_rec.item_line_id
3089 , p_trx_repo_extract_rec => p_trx_repo_extract_rec
3090 , p_process_message => p_process_message
3091 , p_process_flag => p_process_flag
3092 ) ;
3093 ELSE
3094 -------------------------------------------------------
3095 --Add by Qiong for Advanced Receipts bug#13361952 End
3096 jai_trx_repo_extract_pkg.derrive_doc_from_ref
3097 ( p_reference_source => lr_refs_rec.source
3098 , p_reference_invoice_id => lr_refs_rec.invoice_id
3099 , p_reference_item_line_id => lr_refs_rec.item_line_id
3100 , p_trx_repo_extract_rec => p_trx_repo_extract_rec
3101 , p_process_message => p_process_message
3102 , p_process_flag => p_process_flag
3103 ) ;
3104 END IF;
3105 ln_doc_id := p_trx_repo_extract_rec.document_id;
3106 ln_doc_line_id := p_trx_repo_extract_rec.document_line_id;
3107 lv_trx_src := p_trx_repo_extract_rec.transaction_source;
3108 if lv_trx_src='STANDALONE_INVOICE'
3109 then
3110 fnd_file.put_line(fnd_file.log,'lr_refs_rec.invoice_id '|| lr_refs_rec.invoice_id ||' lr_refs_rec.line_id) '||lr_refs_rec.line_id );
3111 --lr_line_number refers to the tax line number in apila in the standlone invoice
3112 open c_get_line_number(lr_refs_rec.invoice_id,lr_refs_rec.line_id);
3113 fetch c_get_line_number into lr_inv_dist_id,lr_line_number;
3114 close c_get_line_number;
3115
3116 fnd_file.put_line(fnd_file.log,'lr_inv_dist_id '|| lr_inv_dist_id ||' lr_line_number '||lr_line_number );
3117
3118 end if;
3119 -- end bug#10085619
3120
3121 if p_process_flag <> jai_constants.SUCCESSFUL then
3122 return;
3123 end if;
3124 jai_trx_repo_extract_pkg.get_document_details
3125 ( p_document_id => ln_doc_id
3126 , p_document_line_id => ln_doc_line_id
3127 , p_document_source => lv_trx_src
3128 , p_source_trx_type => p_source_trx_type
3129 , p_process_message => p_process_message
3130 , p_process_flag => p_process_flag
3131 , p_trx_repo_extract => p_trx_repo_extract_rec
3132 ---added p_taxline_no parameter for bug#10085619
3133 , p_taxline_no => lr_line_number
3134 );
3135 if p_process_flag <> jai_constants.SUCCESSFUL then
3136 return;
3137 end if;
3138
3139 --END IF;
3140
3141 end get_doc_from_reference;
3142
3143 /*------------------------------------------------------------------------------------------------------------*/
3144 procedure update_service_type ( p_process_flag out nocopy varchar2
3145 , p_process_message out nocopy varchar2
3146 )
3147 is
3148
3149 cursor c_get_recs_to_update
3150 is
3151 select *
3152 from jai_trx_repo_extract_gt
3153 where processed_flag = jai_constants.NO;
3154
3155 ln_reg_id number;
3156
3157 begin
3158
3159 lv_member_name := 'UPDATE_SERVICE_TYPE';
3160 set_debug_context;
3161
3162 /* Initialize the process variables */
3163 p_process_flag := jai_constants.SUCCESSFUL;
3164
3165 jai_cmn_debug_contexts_pkg.register (lv_context, ln_reg_id);
3166 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Begin loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
3167 for rec in c_get_recs_to_update
3168 loop
3169 -- For each record in temporary table which is not yet processed
3170 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3171 ,'rec.transaction_source='||rec.transaction_source || fnd_global.local_chr(10) ||
3172 'rec.document_id='||rec.document_id || fnd_global.local_chr(10) ||
3173 'rec.document_line_id='||rec.document_line_id || fnd_global.local_chr(10) ||
3174 'rec.service_type_code='||rec.service_type_code
3175 );
3176 --
3177 -- Update the transaction tables with the service type code based on source value
3178 --
3179 if rec.transaction_source in ('PURCHASING', 'RECEIVING') /*Updated by nprashar for bug # 6841116*/ then
3180
3181 update JAI_PO_LINE_LOCATIONS set service_type_code = rec.service_type_code
3182 , last_update_date = sysdate
3183 , last_updated_by = lv_user_id
3184 , last_update_login = lv_login_id
3185 where po_header_id = rec.document_id
3186 and po_line_id = rec.document_line_id;
3187
3188 elsif rec.transaction_source = 'ORDER MANAGEMENT' then
3189
3190 update JAI_OM_OE_SO_LINES set service_type_code = rec.service_type_code
3191 , last_update_date = sysdate
3192 , last_updated_by = lv_user_id
3193 , last_update_login = lv_login_id
3194 where header_id = rec.document_id
3195 and line_id = rec.document_line_id;
3196
3197 elsif rec.transaction_source = 'RECEIVABLES' then
3198
3199 update JAI_AR_TRX_LINES set service_type_code = rec.service_type_code
3200 , last_update_date = sysdate
3201 , last_updated_by = lv_user_id
3202 , last_update_login = lv_login_id
3203 where customer_trx_id = rec.document_id
3204 and customer_trx_line_id = rec.document_line_id;
3205
3206 elsif rec.transaction_source = 'MANUAL' then
3207
3208 update jai_rgm_manual_trxs
3209 set service_type_code = rec.service_type_code
3210 , last_update_date = sysdate
3211 , last_updated_by = lv_user_id
3212 where transaction_number = rec.document_id;
3213
3214 --added service_distribute-in for bug#13375399
3215 elsif rec.transaction_source in ('SERVICE_DISTRIBUTE_OUT','SERVICE_DISTRIBUTE_IN') then
3216
3217 -- Update Source of the distribution
3218 update jai_rgm_dis_src_hdrs
3219 set service_type_code = rec.service_type_code
3220 , last_update_date = sysdate
3221 , last_updated_by = lv_user_id
3222 , last_update_login = lv_login_id
3223 where transfer_id = rec.document_id;
3224
3225 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3226 ,'No of rows updated in trx table='||sql%rowcount
3227 );
3228
3229 --
3230 -- Update service type code for SERVICE_DISTRIBUTE_IN type of trx with the same service type
3231 -- code as give in the source (SERVICE_DISTRIBUTE_OUT) --
3232
3233 update jai_rgm_trx_records
3234 set service_type_code = rec.service_type_code
3235 , last_update_date = sysdate
3236 , last_updated_by = lv_user_id
3237 , last_update_login = lv_login_id
3238 where source_document_id = rec.document_id
3239 and source = 'SERVICE_DISTRIBUTE_IN';
3240
3241 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3242 ,'No of rows updated in repository table for source SERVICE_DISTRIBUTE_IN='||sql%rowcount
3243 );
3244
3245 -- Begin 5876390, 6012570
3246 elsif rec.transaction_source = 'PROJECTS' then
3247
3248 update jai_pa_draft_invoice_lines
3249 set service_type_code = rec.service_type_code
3250 , last_update_date = sysdate
3251 , last_updated_by = lv_user_id
3252 , last_update_login = lv_login_id
3253 where draft_invoice_id = rec.document_id
3254 and draft_invoice_line_id = rec.document_line_id;
3255
3256 -- End bug 5876390, 6012570
3257
3258 end if;
3259
3260 -- Bug 5876390, 6012570
3261
3262 if rec.transaction_source in ('PROJECTS','ORDER MANAGEMENT') then
3263 -- In case of project and order invoices the invoice also should be updated
3264 update jai_ar_trx_lines
3265 set service_type_code = rec.service_type_code
3266 , last_update_date = sysdate
3267 , last_updated_by = lv_user_id
3268 , last_update_login = lv_login_id
3269 where customer_trx_id = rec.repository_invoice_id
3270 and customer_trx_line_id = rec.repository_line_id;
3271
3272 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3273 ,'Rows updated in ja_in_ra_customer_trx_lines='||sql%rowcount
3274 );
3275 end if;
3276
3277 -- End Bug 5876390, 6012570
3278
3279 if rec.transaction_source not in ('SERVICE_DISTRIBUTE_OUT') then
3280 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3281 ,'No of rows updated in trx table='||sql%rowcount
3282 );
3283 end if;
3284
3285 --
3286 -- Update repository records with service type code
3287 --
3288
3289 update jai_rgm_trx_records
3290 set service_type_code = rec.service_type_code
3291 , last_update_date = sysdate
3292 , last_updated_by = lv_user_id
3293 where repository_id = rec.transaction_repository_id;
3294
3295 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3296 ,'No of rows updated in jai_rgm_trx_records table='||sql%rowcount
3297 );
3298 --
3299 -- Mark record in global temp table as PROCESSED
3300 --
3301 update jai_trx_repo_extract_gt
3302 set processed_flag = 'Y'
3303 where transaction_repository_id = rec.transaction_repository_id;
3304
3305 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3306 ,'No of rows updated in jai_trx_repo_extract_gt table='||sql%rowcount
3307 );
3308
3309 end loop;
3310 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'End loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
3311
3312 /** Deregister procedure and return*/
3313 <<deregister_and_return>>
3314 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
3315
3316 exception
3317 when others then
3318 p_process_flag := jai_constants.unexpected_error;
3319 p_process_message := lv_context||'->'||sqlerrm;
3320 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
3321 jai_cmn_debug_contexts_pkg.print_stack;
3322
3323 end update_service_type;
3324
3325 /*------------------------------------------------------------------------------------------------------------*/
3326
3327 procedure derrive_doc_from_ref
3328 ( p_reference_source in jai_rgm_trx_refs.source%type
3329 , p_source_trx_type in jai_rgm_trx_records.source_trx_type%type default NULL
3330 , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
3331 , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
3332 , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
3333 , p_process_message out nocopy varchar2
3334 , p_process_flag out nocopy varchar2
3335 )
3336 is
3337 lv_created_from ra_customer_trx_all.created_from%type ;
3338
3339 cursor c_chk_ar_inv_attr (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%type
3340 ,cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type
3341 )
3342 is
3343 select rct.created_from
3344 , rct.interface_header_context
3345 , rct.interface_header_attribute1 -- holds order number if context is ORDER_ENTRY
3346 , rctl.interface_line_attribute6 -- holds order line id if context is ORDER_ENTRY
3347 , rctl.interface_line_attribute1 -- 5876390, 6012570, holds PROJECT_NUMBER if context is PROJECTS INVOICES
3348 , rctl.interface_line_attribute2 -- 5876390, 6012570, holds DRAFT_INVOICE_NUM if context is PROJECTS_INOVICES
3349 , rctl.global_attribute_category ----Added by zhiwei for Bug#12604133
3350 from ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
3351 where rct.customer_trx_id = cp_customer_trx_id
3352 and rct.customer_trx_id = rctl.customer_trx_id
3353 and rctl.customer_trx_line_id = cp_customer_trx_line_id;
3354
3355 lr_inv_attr c_chk_ar_inv_attr%rowtype;
3356
3357 cursor c_get_po_reference (cp_invoice_id ap_invoice_distributions_all.invoice_id%type
3358 ,cp_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type
3359 )
3360 is
3361 select pod.po_header_id
3362 ,pod.po_line_id
3363 ,apd.rcv_transaction_id /*Added by vkantamn for Bug#6083978*/
3364 from po_distributions_all pod
3365 ,ap_invoice_distributions_all apd
3366 where pod.po_distribution_id = apd.po_distribution_id
3367 and apd.invoice_id = cp_invoice_id
3368 and apd.invoice_distribution_id = cp_distribution_id;
3369
3370 cursor c_get_loc_from_invoice (cp_customer_trx_id jai_ar_trxs.customer_trx_id%type)
3371 is
3372 select location_id
3373 from jai_ar_trxs
3374 where customer_trx_id = cp_customer_trx_id ;
3375
3376 -- Begin 5876390, 6012570
3377 cursor c_get_jai_pa_details (cp_project_number pa_projects_all.segment1%type
3378 ,cp_draft_inv_num jai_pa_draft_invoice_lines.draft_invoice_num%type
3379 ,cp_line_num jai_pa_draft_invoice_lines.line_num%type
3380 )
3381 is
3382 select draft_invoice_id
3383 ,draft_invoice_line_id
3384 from jai_pa_draft_invoice_lines jpdil
3385 ,pa_projects_all ppa
3386 where ppa.segment1 = cp_project_number
3387 and ppa.project_id = jpdil.project_id
3388 and jpdil.draft_invoice_num = cp_draft_inv_num
3389 and jpdil.line_num = cp_line_num;
3390
3391 ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type;
3392 ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type;
3393
3394 -- End 5876390, 6012570
3395
3396 /* Cursor Added by vkantamn for Bug#6083978 */
3397 cursor c_rcv_trans(cp_rcv_trans_id number)
3398 is
3399 select shipment_header_id,shipment_line_id
3400 from rcv_transactions
3401 where transaction_id = cp_rcv_trans_id;
3402
3403 /* Addition done by vkantamn for Bug#6083978 */
3404
3405 lr_po_reference c_get_po_reference%rowtype;
3406 ln_reg_id number;
3407
3408 begin
3409
3410 lv_member_name := 'DERRIVE_DOC_FROM_REF';
3411 set_debug_context;
3412
3413 jai_cmn_debug_contexts_pkg.register ( lv_context, ln_reg_id );
3414
3415 if p_reference_source = 'AR' then
3416 /*Add by Wenqiong for Advanced Receipts Begin*/
3417 -----------------------------------------------
3418 IF p_source_trx_type in (jai_constants.trx_type_adv_rcpts,jai_constants.trx_type_rct_rvs, jai_constants.trx_type_adv_app,jai_constants.trx_type_adv_unapp) --means it's advanced receipts
3419 THEN
3420 p_trx_repo_extract_rec.transaction_source := 'RECEIVABLES';
3421 p_trx_repo_extract_rec.document_id := p_reference_invoice_id;
3422 p_trx_repo_extract_rec.document_line_id := p_reference_item_line_id;
3423 ELSE
3424 -----------------------------------------------
3425 /*Add by Wenqiong for Advanced Receipts End*/
3426
3427 lv_created_from := null;
3428 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);
3429
3430 --
3431 -- Get the invoice attributes to make a decision regarding from where to fetch document details.
3432 -- If the invoice is an imported one then document is Sales Order other wise AR Invoice details will be fetched
3433 --
3434 open c_chk_ar_inv_attr (cp_customer_trx_id => p_reference_invoice_id
3435 , cp_customer_trx_line_id => p_reference_item_line_id
3436 );
3437 fetch c_chk_ar_inv_attr into lr_inv_attr;
3438 close c_chk_ar_inv_attr;
3439
3440 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'lr_inv_attr.created_from='||lr_inv_attr.created_from
3441 ||',lr_inv_attr.interface_header_context='||lr_inv_attr.interface_header_context
3442 ||',lr_inv_attr.interface_header_attribute1(order number)='||lr_inv_attr.interface_header_attribute1
3443 ||',lr_inv_attr.interface_line_attribute6(oe_line_id)='||lr_inv_attr.interface_line_attribute6
3444 ) ;
3445
3446 if lr_inv_attr.interface_header_context = 'ORDER ENTRY' then -- Invoice imported from Sales order
3447
3448 p_trx_repo_extract_rec.transaction_source := 'ORDER MANAGEMENT';
3449 p_trx_repo_extract_rec.document_id := null;
3450 p_trx_repo_extract_rec.document_line_id := lr_inv_attr.interface_line_attribute6;
3451
3452
3453 --
3454 -- The API get_document_details will not give location in case it is a Sales Order (Refer the API comments for details)
3455 -- So, derrive it from invoice reference
3456 --
3457 open c_get_loc_from_invoice (cp_customer_trx_id => p_reference_invoice_id);
3458 fetch c_get_loc_from_invoice into p_trx_repo_extract_rec.location_id;
3459 close c_get_loc_from_invoice;
3460
3461 -- Begin 5876390, 6012570
3462 elsif JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lr_inv_attr.interface_header_context) then -- Invoice imported from Projects
3463
3464 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'OPEN/FETCH/CLOSE c_get_jai_pa_details ' ||fnd_global.local_chr(10)
3465 ||'cp_project_number='||lr_inv_attr.interface_line_attribute1 ||fnd_global.local_chr(10)
3466 ||'cp_draft_inv_num='||lr_inv_attr.interface_line_attribute2 ||fnd_global.local_chr(10)
3467 ||'cp_line_num='||lr_inv_attr.interface_line_attribute6 ||fnd_global.local_chr (10)
3468 );
3469 open c_get_jai_pa_details ( cp_project_number => lr_inv_attr.interface_line_attribute1
3470 , cp_draft_inv_num => lr_inv_attr.interface_line_attribute2
3471 , cp_line_num => lr_inv_attr.interface_line_attribute6
3472 );
3473 fetch c_get_jai_pa_details into ln_draft_invoice_id
3474 ,ln_draft_invoice_line_id;
3475 close c_get_jai_pa_details;
3476
3477
3478 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_draft_invoice_id='||ln_draft_invoice_id
3479 ||'ln_draft_invoice_line_id='||ln_draft_invoice_line_id
3480 );
3481 p_trx_repo_extract_rec.transaction_source := 'PROJECTS';
3482 p_trx_repo_extract_rec.document_id := ln_draft_invoice_id;
3483 p_trx_repo_extract_rec.document_line_id := ln_draft_invoice_line_id;
3484
3485 --End 5876390, 6012570
3486
3487 elsif lr_inv_attr.interface_header_context is null
3488 or nvl(lr_inv_attr.global_attribute_category,'##') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT' --Added by zhiwei for Bug#12604133 on 20110704
3489 then -- Manual AR Transactions
3490
3491 p_trx_repo_extract_rec.transaction_source := 'RECEIVABLES';
3492 p_trx_repo_extract_rec.document_id := p_reference_invoice_id;
3493 p_trx_repo_extract_rec.document_line_id := p_reference_item_line_id;
3494
3495
3496 end if; --> lr_inv_attr.created
3497 END IF;
3498 elsif p_reference_source = 'AP' then
3499 jai_cmn_debug_contexts_pkg.print (ln_reg_id
3500 , 'OPEN/FETCH/CLOSE c_get_po_reference'||fnd_global.local_chr(10)
3501 ||'p_reference_invoice_id ='||p_reference_invoice_id
3502 ||'p_reference_item_line_id ='||p_reference_item_line_id
3503 );
3504
3505 open c_get_po_reference (cp_invoice_id => p_reference_invoice_id
3506 ,cp_distribution_id => p_reference_item_line_id
3507 );
3508 fetch c_get_po_reference into lr_po_reference;
3509
3510 jai_cmn_debug_contexts_pkg.print(ln_reg_id
3511 ,'lr_po_reference.po_header_id =' ||lr_po_reference.po_header_id || fnd_global.local_chr(10) ||
3512 'lr_po_reference.po_line_id =' ||lr_po_reference.po_line_id
3513 );
3514 if c_get_po_reference%FOUND THEN --If condition added by JMEENA for bug#8943349
3515 IF lr_po_reference.rcv_transaction_id is NULL THEN /*If Condition added by vkantamn for Bug#6083978 */
3516 p_trx_repo_extract_rec.transaction_source := 'PURCHASING';
3517 /* p_trx_repo_extract_rec.document_id := lr_po_reference.po_header_id;
3518 p_trx_repo_extract_rec.document_line_id := lr_po_reference.po_line_id;*/ /* Commented for bug -13430127 */
3519 /* Else Part added by vkantamn for Bug#6083978 */
3520 ELSE
3521 p_trx_repo_extract_rec.transaction_source := 'RECEIVING';
3522 /*open c_rcv_trans(lr_po_reference.rcv_transaction_id);
3523 fetch c_rcv_trans into p_trx_repo_extract_rec.document_id,p_trx_repo_extract_rec.document_line_id;
3524 close c_rcv_trans;*/ /* Commented for bug -13430127 */
3525 END IF;
3526 /* Added the below 2 lines for bug 13430127 */
3527 p_trx_repo_extract_rec.document_id := p_reference_invoice_id;
3528 p_trx_repo_extract_rec.document_line_id :=p_reference_item_line_id;
3529 ELSE -- Else part for bug#8943349
3530
3531 p_trx_repo_extract_rec.transaction_source := 'STANDALONE_INVOICE' ;
3532 p_trx_repo_extract_rec.document_id := p_reference_invoice_id;
3533 p_trx_repo_extract_rec.document_line_id :=p_reference_item_line_id;
3534 /* Addition done by vkantamn for Bug#6083978 */
3535 END IF;
3536 close c_get_po_reference; --moved this code here for bug#9192752
3537
3538 -- Added by Chong.Lei for POT code port begin
3539 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
3540 ----------------------------------------------------------------------------------------------
3541 ELSIF p_reference_source IN ('AP_REVERSAL','AP_CLAIM', 'AR_REVERSAL', 'AR_CLAIM') THEN
3542 p_trx_repo_extract_rec.transaction_source := p_reference_source;
3543 p_trx_repo_extract_rec.document_id := p_reference_invoice_id;
3544 p_trx_repo_extract_rec.document_line_id :=p_reference_item_line_id;
3545 ----------------------------------------------------------------------------------------------
3546 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
3547 -- Added by Chong.Lei for POT code port end
3548
3549 end if; -- End if of bug#8943349
3550
3551 /** Deregister procedure and return*/
3552 <<deregister_and_return>>
3553 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
3554
3555 exception
3556 when others then
3557 p_process_flag := jai_constants.unexpected_error;
3558 p_process_message := lv_context||'->'||sqlerrm;
3559 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
3560 jai_cmn_debug_contexts_pkg.print_stack;
3561
3562 end derrive_doc_from_ref;
3563
3564 /*------------------------------------------------------------------------------------------------------------*/
3565 --
3566 -- A wraper function on top of the procedure get_doc_from_reference to return only service type code
3567 -- This functions are used by Serivce Tax reports for printing service type code
3568 --
3569 function get_service_type_from_ref (p_reference_id in jai_rgm_trx_refs.reference_id%type
3570 )
3571
3572 return varchar2
3573 is
3574 lv_organization_id number;
3575 lv_location_id number;
3576 lv_service_type_code jai_rgm_trx_records.service_type_code%type;
3577 lv_process_flag varchar2 (2);
3578 lv_process_message varchar2 (2000);
3579
3580 begin
3581 jai_trx_repo_extract_pkg.get_doc_from_reference
3582 ( p_reference_id => p_reference_id
3583 , p_organization_id => lv_organization_id
3584 , p_location_id => lv_location_id
3585 , p_service_type_code => lv_service_type_code
3586 , p_process_flag => lv_process_flag
3587 , p_process_message => lv_process_message
3588 );
3589 if lv_process_flag = jai_constants.SUCCESSFUL then
3590 return lv_service_type_code;
3591 else
3592 return null;
3593 end if;
3594 end get_service_type_from_ref;
3595 /*------------------------------------------------------------------------------------------------------------*/
3596
3597 function get_settled_service_type
3598 ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
3599 , p_document_id jai_trx_repo_extract_gt.document_id%type
3600 , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
3601 )
3602 return varchar2 is
3603
3604 lv_service_type jai_rgm_trx_records.service_type_code%type;
3605
3606 cursor c_get_so_settled_srvtyp
3607 is
3608 select recs.service_type_code
3609 from jai_rgm_trx_records recs
3610 ,jai_rgm_trx_refs refs
3611 ,ra_customer_trx_lines_all ractl
3612 where ractl.interface_line_attribute6 = p_document_line_id
3613 and ractl.interface_line_context = 'ORDER ENTRY'
3614 and ractl.line_type = 'LINE'
3615 and ractl.customer_trx_line_id = refs.item_line_id
3616 and refs.reference_id = recs.reference_id
3617 and recs.settlement_id is not null
3618 and recs.service_type_code is not null
3619 and recs.regime_code = 'SERVICE'
3620 and recs.source = 'AR';
3621
3622 cursor c_get_ar_settled_srvtyp
3623 is
3624 select recs.service_type_code
3625 from jai_rgm_trx_records recs
3626 ,jai_rgm_trx_refs refs
3627 where refs.item_line_id = p_document_line_id
3628 and recs.reference_id = refs.reference_id
3629 and recs.settlement_id is not null
3630 and recs.service_type_code is not null
3631 and recs.regime_code = 'SERVICE'
3632 and recs.source = 'AR';
3633
3634 cursor c_get_po_settled_srvtyp
3635 is
3636 select recs.service_type_code
3637 from jai_rgm_trx_records recs
3638 ,jai_rgm_trx_refs refs
3639 ,po_distributions_all pod
3640 ,ap_invoice_distributions_all apd
3641 where pod.po_line_id = p_document_line_id
3642 and pod.po_distribution_id = apd.po_distribution_id
3643 and apd.invoice_distribution_id = refs.item_line_id
3644 and recs.reference_id = refs.reference_id
3645 and recs.settlement_id is not null
3646 and recs.service_type_code is not null
3647 and recs.regime_code = 'SERVICE'
3648 and recs.source = 'AP';
3649
3650 begin
3651
3652 if p_transaction_source = 'ORDER MANAGEMENT' then
3653
3654 open c_get_so_settled_srvtyp;
3655 fetch c_get_so_settled_srvtyp into lv_service_type;
3656 close c_get_so_settled_srvtyp ;
3657
3658 elsif p_transaction_source = 'RECEIVABLES' then
3659
3660 open c_get_ar_settled_srvtyp;
3661 fetch c_get_ar_settled_srvtyp into lv_service_type;
3662 close c_get_ar_settled_srvtyp ;
3663
3664 elsif p_transaction_source = 'PURCHASING' then
3665
3666 open c_get_po_settled_srvtyp;
3667 fetch c_get_po_settled_srvtyp into lv_service_type;
3668 close c_get_po_settled_srvtyp ;
3669
3670 end if;
3671
3672 return lv_service_type;
3673
3674 end get_settled_service_type;
3675
3676
3677
3678 end jai_trx_repo_extract_pkg;
[Home] [Help]